UFN_REVENUE_GETEVENTTOTAL

CREATE function dbo.UFN_REVENUE_GETEVENTTOTAL
(
@EVENTID uniqueidentifier
)
returns money
with execute as caller
as begin
--    return (select isnull(sum(TOTAL),0) from dbo.UFN_REVENUE_GETEVENTTOTAL_BY_CATEGORY(@EVENTID))


    declare @donationTotal money = 0,
        @registrationTotal money = 0,
        @sponsorshipTotal money = 0,
        @pledgedTotal money = 0,
        @pledgPaidTotal money = 0,
        @recurringTotal money = 0,
        @recurringPaidTotal money = 0,
        @offlineTotal money = 0,
        @offlinePaidTotal money = 0,
        @matchingGiftTotal money = 0,
        @matchingGiftPaidTotal money = 0

    select 
        @donationTotal= sum(case when FT.TYPECODE =0 and RS_EXT.APPLICATIONCODE = 0 then FTLI.TRANSACTIONAMOUNT else 0 end)
        , @registrationTotal = sum(case when FT.TYPECODE =0 and RS_EXT.APPLICATIONCODE = 1 then FTLI.TRANSACTIONAMOUNT else 0 end)
        , @sponsorshipTotal = sum(case when FT.TYPECODE =0 and RS_EXT.APPLICATIONCODE = 15 then FTLI.TRANSACTIONAMOUNT else 0 end) 
        , @pledgedTotal = sum(case when FT.TYPECODE =1 and RS_EXT.APPLICATIONCODE = 0 then FTLI.TRANSACTIONAMOUNT else 0 end)
        , @pledgPaidTotal = sum(case when FT.TYPECODE =0 and RS_EXT.APPLICATIONCODE = 2 then FTLI.TRANSACTIONAMOUNT else 0 end) 
        , @recurringTotal = sum(case when FT.TYPECODE =2 and RS_EXT.APPLICATIONCODE = 0 then FTLI.TRANSACTIONAMOUNT else 0 end)
        , @recurringPaidTotal = sum(case when FT.TYPECODE =0 and RS_EXT.APPLICATIONCODE = 3 then FTLI.TRANSACTIONAMOUNT else 0 end)
        , @offlineTotal = sum(case when FT.TYPECODE =9 and RS_EXT.APPLICATIONCODE = 0 then FTLI.TRANSACTIONAMOUNT else 0 end) 
        , @offlinePaidTotal = sum(case when FT.TYPECODE =0 and RS_EXT.APPLICATIONCODE = 17 then FTLI.TRANSACTIONAMOUNT else 0 end)
        , @matchingGiftTotal = sum(case when FT.TYPECODE =3/* and RS_EXT.APPLICATIONCODE = 2 */then FTLI.TRANSACTIONAMOUNT else 0 end)
        , @matchingGiftPaidTotal = sum(case when FT.TYPECODE =0 and RS_EXT.APPLICATIONCODE = 7 then FTLI.TRANSACTIONAMOUNT else 0 end)
    from dbo.FINANCIALTRANSACTION FT
        inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.FINANCIALTRANSACTIONID = FT.ID 
        inner join dbo.REVENUESPLIT_EXT RS_EXT on RS_EXT.ID = FTLI.ID 
        inner join dbo.REVENUE_EXT R_EXT on FT.ID = R_EXT.ID  
        inner join dbo.EVENT E on E.APPEALID = R_EXT.APPEALID 
    where E.ID = @EVENTID

    declare 
        @isUnpaidPledges bit = 0
        ,@isUnpaidRecurringGifts bit = 0
        ,@isSponsorshipRevenue bit = 0
        ,@isUnpaidSponsorshipRevenue bit = 0
        ,@isRegistrationRevenue bit = 0
        ,@isPendingMatchingGifts bit = 0
        ,@unconfirmedParticipantGiftEntry bit = 0;

    -- Get configuration settings

    select 
        @isUnpaidPledges                 = C.ISUNPAIDPLEDGES
        ,@isUnpaidRecurringGifts         = C.ISUNPAIDRECURRINGGIFTS
        ,@isSponsorshipRevenue             = C.ISSPONSORSHIPREVENUE
        ,@isUnpaidSponsorshipRevenue     = C.ISUNPAIDSPONSORSHIPREVENUE
        ,@isRegistrationRevenue         = C.ISREGISTRATIONREVENUE
        ,@isPendingMatchingGifts         = C.ISPENDINGMATCHINGGIFTS
        ,@unconfirmedParticipantGiftEntry = C.UNCONFIRMEDPARTICIPANTGIFTENTRY
    from    dbo.FAFEVENTDONATIONOPTIONSCONFIG C
    where    EVENTID = @EVENTID;

    return ( 
        @donationTotal
        + case when @isRegistrationRevenue = 1 then @registrationTotal else 0 end
        + case when @isSponsorshipRevenue = 1 then @sponsorshipTotal else 0 end
        + case when @unconfirmedParticipantGiftEntry = 1 then @offlineTotal else @offlinePaidTotal end
        + case when @isUnpaidPledges = 1 then @pledgedTotal else @pledgPaidTotal end
        + case when @isUnpaidRecurringGifts = 1 and @recurringTotal >  @recurringPaidTotal then @recurringTotal else @recurringPaidTotal end
        + case when @isPendingMatchingGifts = 1 then @matchingGiftTotal else @matchingGiftPaidTotal end
     )
end