UFN_REVENUE_PARTICIPANT

CREATE function [dbo].[UFN_REVENUE_PARTICIPANT]
(
    @EVENTID as uniqueidentifier,
    @CONSTITUENTID as uniqueidentifier
)
returns TABLE
as
return
    with REVENUE_INSTALLMENTS_CTE as
    (
        select
            REVENUE.ID as REVENUEID
            ,REVENUE.TRANSACTIONTYPE
            ,REVENUE.TRANSACTIONTYPECODE
            ,REVENUE.CONSTITUENTID
            ,REVENUE.AMOUNT
            ,REVENUE.APPEALID

            ,case when NUMBEROFINSTALLMENTS = 0 then 
                case when ENDDATE is null then 1    -- Recurring gift with no end date

                else
                    case FREQUENCYCODE 
                        when 0 then datediff(month, REVENUESCHEDULE.STARTDATE, REVENUESCHEDULE.ENDDATE )/12         -- Annually

                        when 1 then (datediff(month, REVENUESCHEDULE.STARTDATE, REVENUESCHEDULE.ENDDATE )/12)*2     -- Semi-annually

                        when 2 then (datediff(month, REVENUESCHEDULE.STARTDATE, REVENUESCHEDULE.ENDDATE )/3)        -- Quarterly

                        when 3 then datediff(month, REVENUESCHEDULE.STARTDATE, REVENUESCHEDULE.ENDDATE)             -- Monthly

                        when 5 then 1                                                                                -- Single Installment

                        when 6 then datediff(month, REVENUESCHEDULE.STARTDATE, REVENUESCHEDULE.ENDDATE)/2           -- Bimonthly

                        when 7 then datediff(month, REVENUESCHEDULE.STARTDATE, REVENUESCHEDULE.ENDDATE)*2             -- Semi-Monthly

                        when 8 then datediff(week, REVENUESCHEDULE.STARTDATE, REVENUESCHEDULE.ENDDATE)    /2             -- Biweekly

                        when 9 then datediff(week, REVENUESCHEDULE.STARTDATE, REVENUESCHEDULE.ENDDATE)              -- Weekly

                    end    + 1 -- Adding one because the start and enddate are inclusive

                end             
             else isnull(NUMBEROFINSTALLMENTS,1)
             end as NUMBEROFINSTALLMENTS

        from [dbo].[REVENUE] (nolock)
        left outer join [dbo].[REVENUESCHEDULE] (nolock) on REVENUE.ID = [REVENUESCHEDULE].ID
    )
    select
         RI.REVENUEID
        ,RI.CONSTITUENTID as DONOR_CONSTITUENTID
        ,RS.ID AS [REVENUESPLITID]
        ,RI.TRANSACTIONTYPECODE
        ,RS.APPLICATIONCODE
        ,RI.[TRANSACTIONTYPE] as [RTRANSACTIONTYPE]
        ,RS.APPLICATION as RAPPLICATION
        ,case 
            when TRANSACTIONTYPECODE = 9 AND APPLICATIONCODE = 0    then 'Offline Donation'
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 17    then 'Offline Donation'
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 0    then 'Donation' 
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 15    then 'Event sponsorship' 
            when TRANSACTIONTYPECODE = 1 AND APPLICATIONCODE = 0    then 'Pledge' 
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 2    then 'Pledge' 
            when TRANSACTIONTYPECODE = 2 AND APPLICATIONCODE = 0    then 'Recurring gift'                 
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 3    then 'Recurring gift' 
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 7    then 'Matching gift' 
            when TRANSACTIONTYPECODE = 3                            then 'Matching gift' 
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 1    then 'Event registration'                
            end [TRANSACTIONTYPE]
        ,case 
            when TRANSACTIONTYPECODE = 9 AND APPLICATIONCODE = 0    then RS.AMOUNT
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 17    then 0                                                        
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 0    then RS.AMOUNT
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 15    then RS.AMOUNT
            when TRANSACTIONTYPECODE = 1 AND APPLICATIONCODE = 0    then RS.AMOUNT
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 2    then 0
            when TRANSACTIONTYPECODE = 2 AND APPLICATIONCODE = 0    then RS.AMOUNT
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 3    then 0
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 7    then 0 
            when TRANSACTIONTYPECODE = 3                            then RS.AMOUNT
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 1    then RS.AMOUNT
            end * CASE when TRANSACTIONTYPECODE = 2 AND APPLICATIONCODE = 0 THEN NUMBEROFINSTALLMENTS ELSE 1 End        -- Predicted amount based on installments is applied to recurring gifts only this allow us to select to see paid or unpaid amount for recurring gifts

            as [ORIGINAL AMOUNT]
        ,case                     
            when TRANSACTIONTYPECODE = 9 AND APPLICATIONCODE = 0    then 0
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 17    then RS.AMOUNT
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 0    then RS.AMOUNT
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 15    then RS.AMOUNT
            when TRANSACTIONTYPECODE = 1 AND APPLICATIONCODE = 0    then 0
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 2    then RS.AMOUNT
            when TRANSACTIONTYPECODE = 2 AND APPLICATIONCODE = 0    then 0
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 3    then RS.AMOUNT
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 7    then RS.AMOUNT                 
            when TRANSACTIONTYPECODE = 3                            then 0
            when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 1    then RS.AMOUNT
            end as [APPLIED AMOUNT]

    from dbo.REVENUESPLIT RS (nolock)
    inner join REVENUE_INSTALLMENTS_CTE RI on RI.REVENUEID = RS.REVENUEID    
    inner join dbo.[EVENT] (nolock) on RI.APPEALID = [EVENT].APPEALID
    WHERE EVENT.ID = @EVENTID AND RS.ID IN (
        SELECT REVENUESPLITID from REVENUERECOGNITION where CONSTITUENTID = @CONSTITUENTID)