UFN_REVENUE_REGISTRANTEVENTTOTAL_BYREVENUERULES

create function dbo.UFN_REVENUE_REGISTRANTEVENTTOTAL_BYREVENUERULES
(
    @EVENTID uniqueidentifier
)
returns money
as
begin
      declare 
            @ISUNPAIDPLEDGES                    bit = 0
          , @ISUNPAIDRECURRINGGIFTS                bit = 0
          , @ISREGISTRATIONREVENUE                bit = 0
          , @ISPENDINGMATCHINGGIFTS                bit = 0
          , @UNCONFIRMEDPARTICIPANTGIFTENTRY    bit = 0
      , @TOTAL money = null;

    -- Get configuration settings for the event

    select 
        @ISUNPAIDPLEDGES                 = C.ISUNPAIDPLEDGES
      , @ISUNPAIDRECURRINGGIFTS             = C.ISUNPAIDRECURRINGGIFTS
      , @ISREGISTRATIONREVENUE             = C.ISREGISTRATIONREVENUE
      , @ISPENDINGMATCHINGGIFTS             = C.ISPENDINGMATCHINGGIFTS
      , @UNCONFIRMEDPARTICIPANTGIFTENTRY = C.UNCONFIRMEDPARTICIPANTGIFTENTRY
    from    dbo.FAFEVENTDONATIONOPTIONSCONFIG C
    where    EVENTID = @EVENTID

    SELECT @TOTAL= (
      SUM(RECEIVEDAMOUNT)
      + CASE WHEN @ISUNPAIDPLEDGES=1 THEN ISNULL(SUM(UNPAIDPLEDGEAMOUNT), 0) ELSE 0 END
      + CASE WHEN @ISUNPAIDRECURRINGGIFTS=1 THEN ISNULL(SUM(UNPAIDRECURRINGAMOUNT), 0) ELSE 0 END
      + CASE WHEN @ISREGISTRATIONREVENUE=1 THEN ISNULL(SUM(REGAMOUNT), 0) ELSE 0 END
      + CASE WHEN @ISPENDINGMATCHINGGIFTS=1 THEN ISNULL(SUM(MATCHINGGIFTCLAIMAMOUNT), 0) ELSE 0 END
      + CASE WHEN @UNCONFIRMEDPARTICIPANTGIFTENTRY=1 THEN ISNULL(SUM(UNCONFIRMEDOFFLINEAMOUNT), 0) ELSE 0 END)
    FROM FAFRAISEDTOTAL FR
    --filter out revenue received by groups (general group donations)

    INNER JOIN REGISTRANT R on FR.CONSTITUENTID=R.CONSTITUENTID AND FR.EVENTID=R.EVENTID
    WHERE FR.EVENTID=@EVENTID

    return @TOTAL

end