UFN_SMARTQUERY_POTENTIALGIFTAID

create function dbo.UFN_SMARTQUERY_POTENTIALGIFTAID
(
  @CONSTITUENTSELECTIONID uniqueidentifier,
  @STARTDATE datetime,
  @ENDDATE datetime,
  @CURRENTAPPUSERID uniqueidentifier = null,           
  @MAXROWS int
)
returns @T table 
(
  ID uniqueidentifier,
  DONOR nvarchar(154),
  DONATIONSRECEIVED money,
  POTENTIALBASETAXCLAIM money,
  POTENTIALTRANSITIONALTAXCLAIM money,
  POTENTIALTAXCLAIM money,
  DISPLAYCURRENCY uniqueidentifier
)
as
begin

  declare @SMARTQUERYCATALOGID uniqueidentifier = '2548b775-732f-42fa-8a61-3c70d957c7e1';
  declare @ISADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
  declare @MULTICURRENCYENABLED bit = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency');
  declare @CURRENCYID uniqueidentifier;

  if @MULTICURRENCYENABLED = 1
    set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);

  insert into @T
    select
      ID,
      DONOR,
      DONATIONSRECEIVED,
      POTENTIALBASETAXCLAIM,
      POTENTIALTRANSITIONALTAXCLAIM,
      POTENTIALBASETAXCLAIM + POTENTIALTRANSITIONALTAXCLAIM as POTENTIALTAXCLAIM,
      DISPLAYCURRENCY
    from
    -- Using derived table so POTENTIALTAXCLAIM can be calculated as the sum of POTENTIALBASETAXCLAIM and POTENTIALTRANSITIONALTAXCLAIM.

    -- The calculated column TAXCLAIMAMOUNT isn't used so UFN_REVENUESPLITGIFTAID_TRANSITIONALREFLIEFEXPIRED doesn't have 

    -- to be called twice for the same split.

    (
      select top (@MAXROWS)
        CONSTITUENT.ID as [ID],
        CONSTITUENT.NAME as [DONOR],
        case @MULTICURRENCYENABLED
          when 1 then sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID, @CURRENCYID))
          else sum(REVENUESPLIT.AMOUNT)
        end as [DONATIONSRECEIVED],
        case @MULTICURRENCYENABLED
          when 1 then sum(dbo.UFN_GIFTAID_GETBASETAXCLAIMAMOUNTINCURRENCY(REVENUESPLIT.ID, @CURRENCYID))
          else sum(REVENUESPLITGIFTAID.BASETAXCLAIMAMOUNT)
        end as [POTENTIALBASETAXCLAIM],
        case @MULTICURRENCYENABLED
          when 1 then sum(case when REVENUESPLITGIFTAID.TRANSITIONALTAXCLAIMAMOUNT <> 0 and dbo.UFN_REVENUESPLITGIFTAID_TRANSITIONALREFLIEFEXPIRED(REVENUESPLITGIFTAID.ID, REVENUESPLITGIFTAID.INCLUDETRANSITIONALAMOUNTCODE) = 0 then dbo.UFN_GIFTAID_GETTRANSITIONALTAXCLAIMAMOUNTINCURRENCY(REVENUESPLIT.ID, @CURRENCYID) else 0 end)
          else sum(case when REVENUESPLITGIFTAID.TRANSITIONALTAXCLAIMAMOUNT <> 0 and dbo.UFN_REVENUESPLITGIFTAID_TRANSITIONALREFLIEFEXPIRED(REVENUESPLITGIFTAID.ID, REVENUESPLITGIFTAID.INCLUDETRANSITIONALAMOUNTCODE) = 0 then REVENUESPLITGIFTAID.TRANSITIONALTAXCLAIMAMOUNT else 0 end)
        end as [POTENTIALTRANSITIONALTAXCLAIM],
        @CURRENCYID as DISPLAYCURRENCY
      from 
        dbo.CONSTITUENT 
        inner join dbo.REVENUE REVENUE on CONSTITUENT.ID = REVENUE.CONSTITUENTID
        left join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@CONSTITUENTSELECTIONID) as [IDSET] on [IDSET].ID = CONSTITUENT.ID
        left join dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID
        left join dbo.REVENUESPLITGIFTAID on REVENUESPLIT.ID = REVENUESPLITGIFTAID.ID
      where CONSTITUENT.ID not in 
        (select TD.CONSTITUENTID 
        from dbo.TAXDECLARATION TD
          inner join dbo.CHARITYCLAIMREFERENCENUMBER CCRN on CCRN.ID = TD.CHARITYCLAIMREFERENCENUMBERID
          left join dbo.CHARITYCLAIMREFERENCENUMBERSITE CCRNS on CCRNS.CHARITYCLAIMREFERENCENUMBERID = CCRN.ID
        where
          TD.PAYSTAXCODE <> 2
          --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

          and (REVENUE.DATE >= cast(TD.DECLARATIONSTARTS as date))
          --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

          and (REVENUE.DATE <= dateadd(ms, -003, dateadd(d, 1, cast(cast(TD.DECLARATIONENDS as date) as datetime))) or (TD.DECLARATIONENDS is null))
          and (CCRNS.SITEID in (select SITEID from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID))
          or
          ((((select count(*) from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID)) = 0) 
              or
              not exists(select * from dbo.CHARITYCLAIMREFERENCENUMBER CCRN2
                          inner join dbo.CHARITYCLAIMREFERENCENUMBERSITE CCRNS2 on CCRNS2.CHARITYCLAIMREFERENCENUMBERID = CCRN2.ID
                          where CCRNS2.SITEID in 
                            (select SITEID from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID))))
              and
              (CCRN.ID not in (select CHARITYCLAIMREFERENCENUMBERID from dbo.CHARITYCLAIMREFERENCENUMBERSITE))))
          )
          and CONSTITUENT.ISORGANIZATION = 0
          and CONSTITUENT.ISGROUP = 0
          and (REVENUESPLITGIFTAID.RULES_STATUS = 1 and REVENUESPLITGIFTAID.ATTRIBUTES_STATUS = 1)
          and (DECLINESGIFTAID = 0)
          and (ISCOVENANT = 0)
          and (ISSPONSORSHIP = 0)
          and (@STARTDATE is null or REVENUE.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE))
          and (@ENDDATE is null or REVENUE.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE))
          and (@CONSTITUENTSELECTIONID is null or not [IDSET].ID is null)
          and REVENUE.TRANSACTIONTYPECODE = 0
          and REVENUESPLIT.APPLICATIONCODE in (0,1,2,3,4,5)
          and (@ISADMIN = 1 or 
            (
              (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT(@CURRENTAPPUSERID, '2548b775-732f-42fa-8a61-3c70d957c7e1', CONSTITUENT.ID) = 1)
              and
              (dbo.UFN_SECURITY_APPUSER_GRANTED_SMARTQUERY_FORCONSTIT_BYSITE(@CURRENTAPPUSERID, '2548b775-732f-42fa-8a61-3c70d957c7e1', CONSTITUENT.ID) = 1)
            )
          )
        -- Apply site security for each revenue split

        and (@ISADMIN = 1 or
          exists
          (
            select    
              1 
            from 
              dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_SMARTQUERY as SECURITYVIEW
              left join dbo.SITEPERMISSION on SITEPERMISSION.APPUSERID = SECURITYVIEW.APPUSERID and SITEPERMISSION.SYSTEMROLEID = SECURITYVIEW.SYSTEMROLEID
            where 
              SECURITYVIEW.APPUSERID = @CURRENTAPPUSERID and  
              SECURITYVIEW.SMARTQUERYCATALOGID = @SMARTQUERYCATALOGID and  
              SECURITYVIEW.GRANTORDENY = 1 and                    
              (
                SECURITYVIEW.SITESECURITYMODE = 0
                or
                (SECURITYVIEW.SITESECURITYMODE = 1 and (select count(*) from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) where SITEID is null) <> 0)
                or
                SITEPERMISSION.SITEID in (select SITEID from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID))
              )
          )
        )
      group by CONSTITUENT.NAME, CONSTITUENT.ID
    ) as DATA
    order by DONATIONSRECEIVED desc;

  return;
end;