UFN_REVENUE_GETPARTICIPANTGIVENTOTAL

create function [dbo].[UFN_REVENUE_GETPARTICIPANTGIVENTOTAL]
(
   @CONSTITUENTID  uniqueidentifier
  ,@EVENTID             uniqueidentifier
)
returns money
with execute as caller
as begin

      -- Configuration flags variables

      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                    = FAFEVENTDONATIONOPTIONSCONFIG.ISUNPAIDPLEDGES
        , @ISUNPAIDRECURRINGGIFTS               = FAFEVENTDONATIONOPTIONSCONFIG.ISUNPAIDRECURRINGGIFTS
        , @ISSPONSORSHIPREVENUE                 = FAFEVENTDONATIONOPTIONSCONFIG.ISSPONSORSHIPREVENUE
        , @ISUNPAIDSPONSORSHIPREVENUE           = FAFEVENTDONATIONOPTIONSCONFIG.ISUNPAIDSPONSORSHIPREVENUE
        , @ISREGISTRATIONREVENUE                = FAFEVENTDONATIONOPTIONSCONFIG.ISREGISTRATIONREVENUE
        , @ISPENDINGMATCHINGGIFTS               = FAFEVENTDONATIONOPTIONSCONFIG.ISPENDINGMATCHINGGIFTS
        , @UNCONFIRMEDPARTICIPANTGIFTENTRY = FAFEVENTDONATIONOPTIONSCONFIG.UNCONFIRMEDPARTICIPANTGIFTENTRY
      from  dbo.FAFEVENTDONATIONOPTIONSCONFIG 
      where FAFEVENTDONATIONOPTIONSCONFIG.EVENTID = @EVENTID;

      declare @TOTAL as money;

      ;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)
            where CONSTITUENTID = @CONSTITUENTID
            group by TRANSACTIONTYPE,CONSTITUENTID
      )
      ,[REGISTRANTRAISEDAMOUNT_CTE]
      as
      (
            select      
                  case 
                        when [REVENUETYPE] = 'Donation' THEN                        
                              case when @UNCONFIRMEDPARTICIPANTGIFTENTRY=1 then [ORIGINALAMOUNT] ELSE 0 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]
      )
      select @TOTAL = SUM(ISNULL(TOTAL,0)) from [REGISTRANTRAISEDAMOUNT_CTE]

      return
      (
            @TOTAL
      )
end