UFN_CONSTITUENT_GIVINGHISTORY_2

CREATE function [dbo].[UFN_CONSTITUENT_GIVINGHISTORY_2]
(
    @CONSTITUENTID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
)
returns table
as
return(
    with CONSTITUENTREVENUE_CTE as
    (
        select
            CONSTITUENTID,
            ID,
            TRANSACTIONTYPECODE,
            TRANSACTIONTYPE,
            AMOUNT,
            DATE,
            DATEADDED,
      TRANSACTIONCURRENCYID,
      BASECURRENCYID,
      TRANSACTIONAMOUNT,
      ORGANIZATIONAMOUNT
        from dbo.REVENUE
        where
            CONSTITUENTID = @CONSTITUENTID
    ), REVHISTORY_CTE as
    (
        select
            R.CONSTITUENTID,
            R.ID REVENUEID,
            R.TRANSACTIONTYPECODE,
            R.TRANSACTIONTYPE,
            R.AMOUNT REVENUEAMOUNT,
            R.DATE,
            R.DATEADDED,
            RS.ID SPLITID,
            RS.APPLICATIONCODE,
            RS.DESIGNATIONID,
            RS.AMOUNT SPLITAMOUNT,
            coalesce(IWO.AMOUNT, 0) WRITEOFFAMOUNT,
      R.TRANSACTIONCURRENCYID,
      R.BASECURRENCYID,
      R.TRANSACTIONAMOUNT,
      R.ORGANIZATIONAMOUNT
        from
            CONSTITUENTREVENUE_CTE R 
        inner join
            dbo.REVENUESPLIT RS on RS.REVENUEID = R.ID
        left join 
            dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on PGR.REVENUEID = R.ID
        left join 
            dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
        left join
            (select
                INSTALLMENTSPLIT.PLEDGEID,
                INSTALLMENTSPLIT.DESIGNATIONID,
                sum(WO.AMOUNT) as AMOUNT
            from dbo.INSTALLMENTSPLIT
            inner join dbo.INSTALLMENTSPLITWRITEOFF WO on WO.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
            group by INSTALLMENTSPLIT.PLEDGEID, INSTALLMENTSPLIT.DESIGNATIONID) IWO
        on IWO.PLEDGEID = RS.REVENUEID and IWO.DESIGNATIONID = RS.DESIGNATIONID
    where 
        ((R.TRANSACTIONTYPECODE = 1) or --Pledge

         (R.TRANSACTIONTYPECODE = 7) or --Auction donation

        (R.TRANSACTIONTYPECODE = 4 and PG.VEHICLECODE in (0,1,2,5,6,7,8,9,11,12,13))) --Planned gifts other than lead trusts and bequests

    union all
        select
            R.CONSTITUENTID,
            R.ID REVENUEID,
            R.TRANSACTIONTYPECODE,
            R.TRANSACTIONTYPE,
            R.AMOUNT REVENUEAMOUNT,
            R.DATE,
            R.DATEADDED,
            RS.ID SPLITID,
            RS.APPLICATIONCODE,
            RS.DESIGNATIONID,
            RS.AMOUNT SPLITAMOUNT,
            0 WRITEOFFAMOUNT,
      R.TRANSACTIONCURRENCYID,
      R.BASECURRENCYID,
      R.TRANSACTIONAMOUNT,
      R.ORGANIZATIONAMOUNT
        from
            CONSTITUENTREVENUE_CTE R 
        inner join
            dbo.REVENUESPLIT RS on RS.REVENUEID = R.ID
        left join 
            dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.PAYMENTID = RS.ID
        left join
            dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PGR on ISP.PLEDGEID = PGR.REVENUEID
        left join
            dbo.PLANNEDGIFT PG on PG.ID = PGR.PLANNEDGIFTID
        where
            ((R.TRANSACTIONTYPECODE = 5 and RS.APPLICATIONCODE = 0) or -- Donations made on orders

            (R.TRANSACTIONTYPECODE = 0 and -- payments made on...

                (
                RS.APPLICATIONCODE in (0, 3, 7, 13, 12) -- ...donations, recurring gifts, matching gifts, donor challenge, auction purchase...

                 or
                (RS.APPLICATIONCODE = 6 and PG.VEHICLECODE in (3,4,10)) -- ...lead trusts and bequests

                 or
                (RS.APPLICATIONCODE = 1 and RS.TYPECODE = 0) -- gift revenue applied to event registration

                )
            ))
    )
    select 
        REVENUEID,
        TRANSACTIONTYPECODE,
        TRANSACTIONTYPE,
        REVENUEAMOUNT,
        [DATE],
        DATEADDED,
        SPLITID,
        APPLICATIONCODE,
        DESIGNATIONID,
        SPLITAMOUNT,
        WRITEOFFAMOUNT,
    TRANSACTIONCURRENCYID,
    BASECURRENCYID,
    TRANSACTIONAMOUNT,
    ORGANIZATIONAMOUNT
    from 
        REVHISTORY_CTE RH
    where 
        exists 
        (
            select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
            cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
            where RSSUB.REVENUEID = RH.REVENUEID
            -- Using a case statement since the standard site extension filters

            -- resulted in a poor plan

            and (case 
                    when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
                    when exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)) then 1
                    else 0 
                end) = 1
            and 
            (
                @SITEFILTERMODE = 0
                or 
                exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
            )
        )
)