UFN_FAFEVENT_GETREVENUECONSTITUENTS

create function dbo.UFN_FAFEVENT_GETREVENUECONSTITUENTS
(
    @EVENTID as uniqueidentifier,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @GIFTTYPECODE integer = 1 --1:Gift, 2:Pledge, 3:Recurring gift, 4:Pending donation    

)
returns TABLE
as
return
        with REVENUE_INSTALLMENTS_CTE
        as
        (        
    --DECLARE    @TRANSACTIONTYPECODE as integer = null

    --DECLARE @APPLICATIONCODE as integer = null


    --10/28/2010: For the logic on how to calculate all total, please contact DBA group


            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]
                ,case 
                    when REVENUEOFFLINEDONATION.ID IS NOT NULL                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 REVENUEOFFLINEDONATION.ID IS NOT NULL                then REVENUESPLIT.AMOUNT
                    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 REVENUEOFFLINEDONATION.ID IS NOT NULL                then REVENUESPLIT.AMOUNT
                    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]
                ,[EVENT].ID,[EVENTEXTENSION].PRIORYEAREVENTID
            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
            left    join dbo.REVENUEOFFLINEDONATION
            on        REVENUEOFFLINEDONATION.ID =  REVENUESPLIT.REVENUEID    
            LEFT    join dbo.[EVENTEXTENSION] (nolock)
            on        [EVENT].ID=[EVENTEXTENSION].EVENTID
            where    EVENT.ID = @EVENTID
            --AND REVENUESPLIT.APPLICATIONCODE = ISNULL(@APPLICATIONCODE,REVENUESPLIT.APPLICATIONCODE)

            --AND REVENUE.TRANSACTIONTYPECODE = ISNULL(@TRANSACTIONTYPECODE,REVENUE.TRANSACTIONTYPECODE)

            AND REVENUE.DATE BETWEEN ISNULL(@STARTDATE, '1/1/1900')  AND DATEADD(dd, 1, ISNULL(@ENDDATE, '12/30/2999'))

        )
        select * from EVENT_REVENUEDETAILS_CTE