UFN_REVENUE_EVENT

CREATE function [dbo].[UFN_REVENUE_EVENT]
(
    @EVENTID as uniqueidentifier
)
returns TABLE
as
return
    with REVENUE_INSTALLMENTS_CTE
    as
    (
        select
              REVENUE.ID as REVENUEID
             ,REVENUE.TRANSACTIONTYPE
             ,REVENUE.AMOUNT
            ,case  
                 when NUMBEROFINSTALLMENTS = 0 then 
                    -- Recurring gift with no end date

                    case when ENDDATE is null then 1
                    else
                        -- Adding one 

                        case FREQUENCYCODE 
                            when 0 then -- Annually

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

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

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

                                 datediff(month, REVENUESCHEDULE.STARTDATE, REVENUESCHEDULE.ENDDATE)     
                            when 5 then    -- Single Installment

                                 1    
                            when 6 then -- Bimonthly

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

                                 datediff(month, REVENUESCHEDULE.STARTDATE, REVENUESCHEDULE.ENDDATE)*2     
                            when 8 then -- Biweekly

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

                                 datediff(week, REVENUESCHEDULE.STARTDATE, REVENUESCHEDULE.ENDDATE) 
                        end    + 1 -- Adding one because the start and enddate are inclusive

                    end             
                else isnull(NUMBEROFINSTALLMENTS,1)
            end as NUMBEROFINSTALLMENTS
        from    [dbo].[REVENUE] (nolock)
        left    join [dbo].[REVENUESCHEDULE] (nolock)
        on        REVENUE.ID = [REVENUESCHEDULE].ID
    )
    ,EVENT_REVENUEDETAILS_CTE
    as
    (
        select 
             REVENUE.CONSTITUENTID
            ,REVENUESPLIT.ID AS [REVENUESPLITID]
            ,TRANSACTIONTYPECODE
            ,REVENUESPLIT.APPLICATIONCODE
            ,REVENUE.[TRANSACTIONTYPE] as [RTRANSACTIONTYPE]
            ,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 REVENUESPLIT.AMOUNT
                when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 17    then 0                                                        
                when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 0    then REVENUESPLIT.AMOUNT
                when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 15    then REVENUESPLIT.AMOUNT
                when TRANSACTIONTYPECODE = 1 AND APPLICATIONCODE = 0    then REVENUESPLIT.AMOUNT
                when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 2    then 0
                when TRANSACTIONTYPECODE = 2 AND APPLICATIONCODE = 0    then REVENUESPLIT.AMOUNT
                when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 3    then 0
                when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 7    then 0 
                when TRANSACTIONTYPECODE = 3                            then REVENUESPLIT.AMOUNT
                when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 1    then REVENUESPLIT.AMOUNT
            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

                CASE when TRANSACTIONTYPECODE = 2 AND APPLICATIONCODE = 0 THEN NUMBEROFINSTALLMENTS ELSE 1 End 
                as [ORIGINAL AMOUNT]
            ,case                     
                when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 17    then REVENUESPLIT.AMOUNT
                when TRANSACTIONTYPECODE = 9 AND APPLICATIONCODE = 0    then 0
                when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 0    then REVENUESPLIT.AMOUNT
                when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 15    then REVENUESPLIT.AMOUNT
                when TRANSACTIONTYPECODE = 1 AND APPLICATIONCODE = 0    then 0
                when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 2    then REVENUESPLIT.AMOUNT
                when TRANSACTIONTYPECODE = 2 AND APPLICATIONCODE = 0    then 0
                when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 3    then REVENUESPLIT.AMOUNT
                when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 7    then REVENUESPLIT.AMOUNT                 
                when TRANSACTIONTYPECODE = 3                            then 0
                when TRANSACTIONTYPECODE = 0 AND APPLICATIONCODE = 1    then REVENUESPLIT.AMOUNT
            end  as [APPLIED AMOUNT]
        from    dbo.REVENUESPLIT (nolock)
        inner    join dbo.REVENUE (nolock)
        on        REVENUE.ID = REVENUESPLIT.REVENUEID
        left    join dbo.[EVENT] (nolock)
        on        REVENUE.APPEALID = [EVENT].APPEALID
        join    REVENUE_INSTALLMENTS_CTE
        on        REVENUE_INSTALLMENTS_CTE.REVENUEID = REVENUE.ID    
        where    EVENT.ID = @EVENTID
    )
    select    * 
    from    EVENT_REVENUEDETAILS_CTE