UFN_FAFEVENT_GETEVENTTOTAL_BY_CATEGORY

CREATE function [dbo].[UFN_FAFEVENT_GETEVENTTOTAL_BY_CATEGORY]
(
    @EVENTID uniqueidentifier,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @ISRETAINEDMODE tinyint = 0  --0: Total Revenue for current event, 1: Revenue retained for that current event.

)
returns @EventRevenueByCategory table 
(
     REVENUETYPECODE    tinyint 
    ,REVENUETYPE        nvarchar(50)
    ,TOTAL                money
    ,TOTALCOUNT            integer
)
as 
begin
    --10/28/2010: For the logic on how to calculate all total, please contact DBA group

    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([ORIGINAL amount]) AS [ORIGINALAMOUNT]
                ,SUM([APPLIED AMOUNT])    AS [APPLIEDAMOUNT]
                ,TRANSACTIONTYPE        AS [REVENUETYPE]
                ,COUNT(CONSTITUENTID)   AS [TOTALCOUNT]
        from    [dbo].[UFN_FAFEVENT_REVENUEDETAILS](@EVENTID,@STARTDATE,@ENDDATE,@ISRETAINEDMODE) 
        group by transactiontype
    )
     insert @EventRevenueByCategory 
    (
         [REVENUETYPECODE]    
        ,[REVENUETYPE]        
        ,[TOTAL]                
        ,[TOTALCOUNT]
    )
    select    
            case 
                when [REVENUETYPE] = 'Donation'                THEN 0
                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]
            ,[ORIGINALAMOUNT] as [TOTAL]
            ,[TOTALCOUNT]
    from    [EVENTTOTAL_CTE];

    return
end