USP_CONSTITUENT_REVENUEHISTORY_SUMMARY

            CREATE procedure dbo.USP_CONSTITUENT_REVENUEHISTORY_SUMMARY
            (
                @ID uniqueidentifier,
                @CURRENTAPPUSERID uniqueidentifier = null,
                @SECURITYFEATUREID uniqueidentifier = null,
                @INCLUDEGROUPMEMBERREVENUE bit = 0,
                @DATALOADED bit = 0 output,
                @TOTALREVENUE money = null output,
                @TOTALNUMBERTRANSACTIONS int = null output,
                @TOTALREVENUEWITHGIFTAID money = null output,
                @TOTALPAID money = null output,
                @TOTALPAYMENTS int = null output,
                @TOTALEXPECTED money = null output,
                @TOTALOPENCOMMITMENTS int = null output,
                @ISORGANIZATION bit = null output,
        @APPBASECURRENCY uniqueidentifier = null output
            ) 
            as
                set nocount on;

                set @DATALOADED = 0;

                declare @SECURITYFEATURETYPE tinyint;
                set @SECURITYFEATURETYPE = 1;
              declare @CURRENTDATE datetime = getdate(); -- Use today's date to get the exchange rate.


        declare @APPUSERBASEID uniqueidentifier;
        set @APPUSERBASEID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID)
        set @APPBASECURRENCY = @APPUSERBASEID

                declare @CONSTITUENTSTOINCLUDE table
                (
                    ID uniqueidentifier not null,
                    DATEFROM datetime,
                    DATETO datetime
                );

                insert into @CONSTITUENTSTOINCLUDE (ID)
                select @ID;

                if @INCLUDEGROUPMEMBERREVENUE = 1
                begin
                    insert into @CONSTITUENTSTOINCLUDE (ID, DATEFROM, DATETO)
                    select GROUPMEMBER.MEMBERID, GROUPMEMBERDATERANGE.DATEFROM, GROUPMEMBERDATERANGE.DATETO
                    from dbo.GROUPMEMBER
                    left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
                    where GROUPMEMBER.GROUPID = @ID
                end

                declare @REVENUETOINCLUDE table
                (
                    ID uniqueidentifier not null,
                    AMOUNT money not null,
                    TRANSACTIONTYPECODE tinyint not null
                );

                insert into @REVENUETOINCLUDE (ID, AMOUNT, TRANSACTIONTYPECODE)
                select
                    REVENUE.ID,
                    BASEAMOUNT,
                    TYPECODE
                from dbo.FINANCIALTRANSACTION REVENUE
                inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
                -- Even though the below semi-join on @CONSTITUENTSTOINCLUDE means this join filter isn't needed to get the correct results,

                -- it's still used since if just the semi-join is used, it results in a merge join with a scan on dbo.REVENUE

                inner join 
                (select distinct ID from @CONSTITUENTSTOINCLUDE) as POSSIBLECONSTITUENTS on REVENUE.CONSTITUENTID = POSSIBLECONSTITUENTS.ID
                where 
                    REVENUE.DELETEDON is null and
                    REVENUE.CONSTITUENTID in (    select ID from @CONSTITUENTSTOINCLUDE 
                                                where
                                                    (DATEFROM is null or REVENUE.DATE >= DATEFROM) and
                                                    (DATETO is null or REVENUE.DATE <= DATETO))
                    and    exists 
                    (
                        select top 1 RSSUB.ID from dbo.FINANCIALTRANSACTIONLINEITEM RSSUB
                        cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
                        where RSSUB.FINANCIALTRANSACTIONID = REVENUE.ID and RSSUB.DELETEDON is null
                        -- 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
                    )

                select @DATALOADED = 1,
                       -- Total Revenue = Unapplied payments + Commitment amounts - Pledge writeoffs

                       @TOTALREVENUE =  sum(case TRANSACTIONTYPECODE
                                             when 0 then AMOUNT-APPLIEDAMOUNT
                                             when -1 then -APPLIEDAMOUNT
                                             when 1 then AMOUNT
                                             when 4 then AMOUNT
                                             when 6 then AMOUNT
                                             when 7 then AMOUNT
                                             else 0
                                           end),
                       @TOTALREVENUEWITHGIFTAID = sum(GROSSAMOUNT),

                     @TOTALNUMBERTRANSACTIONS = count(*),
                       -- Total Paid = Payment amounts

                       @TOTALPAID = sum(case TRANSACTIONTYPECODE when 0 then AMOUNT else 0 end),

                       @TOTALPAYMENTS = count(case TRANSACTIONTYPECODE when 0 then 1 else null end),
                       -- Total Expected = Commitment amounts - Applied payments - Pledge writeoffs

                       @TOTALEXPECTED = sum(EXPECTEDAMOUNT),

                       -- Open Commitments = Commitments w/ a non-zero balance

                       @TOTALOPENCOMMITMENTS = count(case when EXPECTEDAMOUNT > 0 or RGSTATUS = 0 then 1 end)
                from (
                    -- all revenue transactions

                    select MYSITESREV_CTE.ID,
                           min(MYSITESREV_CTE.TRANSACTIONTYPECODE) TRANSACTIONTYPECODE,
                           min(dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(MYSITESREV_CTE.ID,@APPUSERBASEID)) AMOUNT,
                           sum(case MYSITESREV_CTE.TRANSACTIONTYPECODE 
                                 when 1 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNT_2(REVENUESPLIT.ID,@APPUSERBASEID)
                     when 2 then 0
                                 else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(REVENUESPLIT.ID, 1,@APPUSERBASEID)
                               end) GROSSAMOUNT,
                           sum(case when REVENUESPLIT_EXT.APPLICATIONCODE in(2,6,8) then dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(REVENUESPLIT.ID,@APPUSERBASEID) else 0 end) APPLIEDAMOUNT,
                           min(case when TRANSACTIONTYPECODE in(1,4,6) then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(MYSITESREV_CTE.ID,@APPUSERBASEID) else 0 end) EXPECTEDAMOUNT,
                           min(case MYSITESREV_CTE.TRANSACTIONTYPECODE when 2 then REVENUESCHEDULE.STATUSCODE else -1 end) RGSTATUS
                    from @REVENUETOINCLUDE as MYSITESREV_CTE
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.FINANCIALTRANSACTIONID = MYSITESREV_CTE.ID
                    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID and REVENUESPLIT.DELETEDON is null
                    left join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = MYSITESREV_CTE.ID
                    group by MYSITESREV_CTE.ID
                    union all
                    -- pledge writeoffs

                    select null, -1, 0, 0, sum(dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WRITEOFFSPLIT.ID,@APPUSERBASEID)), 0, -1
                    from @REVENUETOINCLUDE as MYSITESREV_CTE
                    inner join dbo.FINANCIALTRANSACTION WRITEOFF on WRITEOFF.PARENTID = MYSITESREV_CTE.ID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM WRITEOFFSPLIT on WRITEOFFSPLIT.FINANCIALTRANSACTIONID = WRITEOFF.ID
                    inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = WRITEOFFSPLIT.ID and WRITEOFFSPLIT.DELETEDON is null
                    where WRITEOFF.TYPECODE = 20 and WRITEOFF.DELETEDON is null
                    group by WRITEOFF.ID
                    union all
                    -- recurring gift writeoffs (for including in transaction count)

                    select null, -2, 0, 0, 0, 0, -1
                    from @REVENUETOINCLUDE as MYSITESREV_CTE
                    inner join dbo.RECURRINGGIFTWRITEOFF on RECURRINGGIFTWRITEOFF.REVENUEID = MYSITESREV_CTE.ID
                ) REV

                select @ISORGANIZATION = ISORGANIZATION 
                from dbo.CONSTITUENT 
                where ID = @ID;

                return 0;