UFN_REVENUE_GETEVENTTOTAL_BY_CATEGORY

CREATE function [dbo].[UFN_REVENUE_GETEVENTTOTAL_BY_CATEGORY]
(
    @EVENTID uniqueidentifier
)
returns @EventRevenueByCategory table 
(
     REVENUETYPECODE    tinyint 
    ,REVENUETYPE        nvarchar(50)
    ,TOTAL                money
)
as 
begin
    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;

    ;with [EVENTTOTAL_CTE]
    as    
    (
        select     sum(isnull([ORIGINAL amount],0)) as [ORIGINALAMOUNT]
                ,sum(isnull([APPLIED AMOUNT] ,0)) as [APPLIEDAMOUNT]
                ,TRANSACTIONTYPE                  as [REVENUETYPE]
        from    [dbo].[UFN_REVENUE_EVENTREVENUEDETAILS](@EVENTID)
        group by transactiontype
    )
     insert @EventRevenueByCategory 
    (
         [REVENUETYPECODE]    
        ,[REVENUETYPE]        
        ,[TOTAL]                
    )
    select    
            case 
                when [REVENUETYPE] = 'Donation'                then 0
                when [REVENUETYPE] = 'Offline Donation'     then 17
                when [REVENUETYPE] = 'Event registration'    then 1
                when [REVENUETYPE] = 'Pledge'                then 2
                when [REVENUETYPE] = 'Event sponsorship'    then 15
                when [REVENUETYPE] = 'Matching gift'        then 7
                when [REVENUETYPE] = 'Recurring gift'        then 3
            end as [REVENUETYPECODE]        
            ,[REVENUETYPE]
            ,case 
                when [REVENUETYPE] = 'Donation' THEN  [ORIGINALAMOUNT]
                when [REVENUETYPE] = 'Offline Donation' THEN 
                    case when @UNCONFIRMEDPARTICIPANTGIFTENTRY=1 then [ORIGINALAMOUNT] ELSE [APPLIEDAMOUNT] END
                when [REVENUETYPE] = 'Event registration'    then 
                    case when @ISREGISTRATIONREVENUE = 1 then [ORIGINALAMOUNT] else 0 end
                when [REVENUETYPE] = 'Pledge'                then 
                    case when @ISUNPAIDPLEDGES=1 then [ORIGINALAMOUNT] else [APPLIEDAMOUNT] end
                when [REVENUETYPE] = 'Event sponsorship'    then 
                    case when @ISSPONSORSHIPREVENUE = 0  then 0 else 
                        case when @ISUNPAIDSPONSORSHIPREVENUE=1 then  [ORIGINALAMOUNT] else [APPLIEDAMOUNT] end
                    end
                when [REVENUETYPE] = 'Matching gift'    then
                    case when @ISPENDINGMATCHINGGIFTS=1 then [ORIGINALAMOUNT] else [APPLIEDAMOUNT] end
                when [REVENUETYPE] = 'Recurring gift'    then 
                    case when @ISUNPAIDRECURRINGGIFTS=1 then [ORIGINALAMOUNT] else [APPLIEDAMOUNT] end
            end as [TOTAL]
    from    [EVENTTOTAL_CTE];

    return
end