UFN_REVENUE_CONSTITUENTRECOGNIZEDAMOUNT

CREATE function [dbo].[UFN_REVENUE_CONSTITUENTRECOGNIZEDAMOUNT]
(

     @EVENTID                as uniqueidentifier 
    ,@CONSTITUENTID            as uniqueidentifier 
)
returns money
with execute as caller
as
begin

  /*
      Revenue amount raised by the current constituent
        in the current event.  Constituent in this 
    context can be individual, team, company or household.  
    Self recognition is excluded. 
  */

  -- 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 @AMOUNT as money = 0.0;

  ;with EVENTRECOGNIZEDREVENUE_CTE        
    as
    (
        select     
             REVENUERECOGNITION.CONSTITUENTID
            ,EVENT_REVENUE.TRANSACTIONTYPE as [REVENUETYPE]
            ,EVENT_REVENUE.[ORIGINAL AMOUNT] as ORIGINALAMOUNT
            ,EVENT_REVENUE.[APPLIED AMOUNT] as APPLIEDAMOUNT
      ,EVENT_REVENUE.REVENUESPLITID
        from    [dbo].[UFN_REVENUE_EVENT](@EVENTID) AS EVENT_REVENUE
        inner    join REVENUERECOGNITION
        on        REVENUERECOGNITION.CONSTITUENTID    <> EVENT_REVENUE.CONSTITUENTID
        and        REVENUERECOGNITION.REVENUESPLITID    = EVENT_REVENUE.REVENUESPLITID
    WHERE REVENUERECOGNITION.CONSTITUENTID = @CONSTITUENTID
    )
    ,[RAISEDAMOUNT_CTE]     -- Apply revenue rules

    as
    (
        select    
            case 
              when [REVENUETYPE] = 'Donation' THEN  [ORIGINALAMOUNT]
        when [REVENUETYPE] = 'Offline 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    [EVENTRECOGNIZEDREVENUE_CTE]
    )
    select @AMOUNT = SUM(ISNULL(TOTAL,0)) from [RAISEDAMOUNT_CTE];

    return(@AMOUNT);
end