UFN_REVENUE_CALCPARTICIPANTRAISEDTOTAL

CREATE function [dbo].[UFN_REVENUE_CALCPARTICIPANTRAISEDTOTAL]
(
    @CONSTITUENTID    uniqueidentifier,
    @EVENTID uniqueidentifier
)
RETURNS TABLE  
AS  
RETURN  

    with TRANS_CTE as (
        select DONOR_CONSTITUENTID = case when TRANSACTIONTYPE = 'Event registration' then null else DONOR_CONSTITUENTID end,
            RECEIVEDAMOUNT = case when TRANSACTIONTYPE = 'Event registration' then 0 else [APPLIED AMOUNT] end, 
            REGAMOUNT = case when TRANSACTIONTYPE = 'Event registration' then [ORIGINAL AMOUNT] else 0 end, 
            UNPAIDPLEDGEAMOUNT = case when TRANSACTIONTYPE = 'Pledge' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
            UNPAIDRECURRINGAMOUNT = case when TRANSACTIONTYPE = 'Recurring gift' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
            MATCHINGGIFTCLAIMAMOUNT = case when TRANSACTIONTYPE = 'Matching gift' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,
            UNCONFIRMEDOFFLINEAMOUNT = case when TRANSACTIONTYPE = 'Offline Donation' then ([ORIGINAL AMOUNT] - [APPLIED AMOUNT]) else 0 end,

            GIFTCOUNT_DONATION = case when TRANSACTIONTYPE = 'Donation' then  1 else 0 end,

            GIFTCOUNT_UNPAIDPLEDGES_ON =                -- pledge header

                case when TRANSACTIONTYPECODE = 1 AND APPLICATIONCODE = 0 then 1 else 0 end,
            GIFTCOUNT_UNPAIDPLEDGES_OFF =               -- pledge header with at least one payment

                case when TRANSACTIONTYPECODE = 1 AND APPLICATIONCODE = 0 and exists(select 1 from INSTALLMENTSPLITPAYMENT where PLEDGEID = RP.REVENUEID) then 1 else 0 end,

            GIFTCOUNT_PENDINGMATCHINGGIFTS_ON =         -- matching gift header

                case when TRANSACTIONTYPECODE = 3 then 1 else 0 end,
            GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF =        -- matching gift payment

                case when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 7 then 1 else 0 end,

            GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON =         -- recurring gift header or number of payments, whichever is greater

                case when TRANSACTIONTYPECODE = 2 AND APPLICATIONCODE = 0 then
                    (select case when c > 1 then c else 1 end from
                        (select count(*) as c from dbo.RECURRINGGIFTINSTALLMENTPAYMENT where PAYMENTID = RP.REVENUEID) v1)
                else 0 end,
            GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF =         -- number of recurring payments

                case when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 3 then 1 else 0 end,

            GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON =      -- offline donation header (the pledge treatment)

                case when TRANSACTIONTYPECODE = 9 AND APPLICATIONCODE = 0 then 1 else 0 end,
            GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF =     -- offline donation header with at least one payment

                case when TRANSACTIONTYPECODE = 9 AND APPLICATIONCODE = 0 and exists(select 1 from INSTALLMENTSPLITPAYMENT where PLEDGEID = RP.REVENUEID) then 1 else 0 end
        from [dbo].[UFN_REVENUE_PARTICIPANT](@EVENTID, @CONSTITUENTID) RP
    )
    select 
        RECEIVEDAMOUNT = SUM(RECEIVEDAMOUNT), 
        REGAMOUNT = SUM(REGAMOUNT), 
        UNPAIDPLEDGEAMOUNT = SUM(UNPAIDPLEDGEAMOUNT),
        UNPAIDRECURRINGAMOUNT = SUM(UNPAIDRECURRINGAMOUNT),
        MATCHINGGIFTCLAIMAMOUNT = SUM(MATCHINGGIFTCLAIMAMOUNT),
        UNCONFIRMEDOFFLINEAMOUNT = SUM(UNCONFIRMEDOFFLINEAMOUNT),
        DONORCOUNT = COUNT(distinct DONOR_CONSTITUENTID),
        GIFTCOUNT_DONATION = SUM(GIFTCOUNT_DONATION),
        GIFTCOUNT_UNPAIDPLEDGES_ON = SUM(GIFTCOUNT_UNPAIDPLEDGES_ON),
        GIFTCOUNT_UNPAIDPLEDGES_OFF = SUM(GIFTCOUNT_UNPAIDPLEDGES_OFF),
        GIFTCOUNT_PENDINGMATCHINGGIFTS_ON = SUM(GIFTCOUNT_PENDINGMATCHINGGIFTS_ON),
        GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF = SUM(GIFTCOUNT_PENDINGMATCHINGGIFTS_OFF),
        GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON = SUM(GIFTCOUNT_UNPAIDRECURRINGGIFTS_ON),
        GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF = SUM(GIFTCOUNT_UNPAIDRECURRINGGIFTS_OFF),
        GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON = SUM(GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_ON),
        GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF = SUM(GIFTCOUNT_UNCONFIRMEDPARTICIPANTGIFTENTRY_OFF)
    from TRANS_CTE