USP_MEMBERSHIPREVENUEREPORT_POPULATEREVENUEDATA

CREATE procedure dbo.USP_MEMBERSHIPREVENUEREPORT_POPULATEREVENUEDATA (
    @PROGRAMID uniqueidentifier,
    @SELECTEDCURRENCYID uniqueidentifier,
    @ORGANIZATIONCURRENCYID uniqueidentifier,
    @DECIMALDIGITS integer,
    @ROUNDINGTYPECODE tinyint,
    @STARTDATE datetime,
    @ENDDATE datetime
)
as
begin

if object_id('tempdb..#temp_membershiprevenuereportdata') is null
    return;

--REVENUE_CTE contains all transactions where REVENUE.DATE between @STARTDATE and @ENDDATE

--or for gifted transactions where MEMBERSHIPTRANSACTION.TRANSACTIONDATE between @STARTDATE and @ENDDATE

with REVENUE_CTE as (
    select
        case when (isnull([REVENUESPLITS].AMOUNTINCURRENCY,0) - isnull([REFUNDS].[AMOUNT],0)) < 0 then 0
        else (isnull([REVENUESPLITS].AMOUNTINCURRENCY,0) - isnull([REFUNDS].[AMOUNT],0))
        end as AMOUNTINCURRENCY,
        [REVENUESPLITS].TRANSACTIONTYPE,
        [REVENUESPLITS].TRANSACTIONTYPECODE,
        [REVENUESPLITS].REXT_TYPE,
        [REVENUESPLITS].REXT_TYPECODE,
        [REVENUESPLITS].APPLICATION,
        [REVENUESPLITS].APPLICATIONCODE,
        [REVENUESPLITS].REPORTBUCKETCODE,
        [REVENUESPLITS].REPORTBUCKET,
        [REVENUESPLITS].CHANNELCODEID,
        [REVENUESPLITS].ID,
        [REVENUESPLITS].CONSTITUENTID,
        [REVENUESPLITS].APPEALID,
        [REVENUESPLITS].DATE,
        [REVENUESPLITS].MEMBERSHIPTRANSACTIONID,
        [REVENUESPLITS].MEMBERSHIPLEVELID,
        [REVENUESPLITS].ACTIONCODE,
        [REVENUESPLITS].ACTION,
        [REVENUESPLITS].TRANSACTIONDATE,
    case when isnull([REFUNDS].[AMOUNT],0) - isnull([REVENUESPLITS].AMOUNTINCURRENCY,0) < 0 then 0
    else isnull([REFUNDS].[AMOUNT],0) - isnull([REVENUESPLITS].AMOUNTINCURRENCY,0)
        end  as LEFTOVERDISCOUNTANDREFUNDS
    from (
        select 
            REVENUEAMOUNT.AMOUNTINCURRENCY as AMOUNTINCURRENCY,
            REVENUEAMOUNT.TRANSACTIONTYPE,
            REVENUEAMOUNT.TRANSACTIONTYPECODE,
            REXT_TYPE = REVENUESPLIT_EXT.TYPE,
            REXT_TYPECODE = REVENUESPLIT_EXT.TYPECODE,
            REVENUEAMOUNT.APPLICATION,
            REVENUEAMOUNT.APPLICATIONCODE,
            REPORTBUCKETCODE = 
                case 
                    when REVENUEAMOUNT.APPLICATIONCODE in (5,19) and REVENUEAMOUNT.TRANSACTIONTYPECODE IN (0,5) and REVENUESPLIT_EXT.TYPECODE = 2 then 0
                    when (REVENUEAMOUNT.TRANSACTIONTYPECODE in (0,5) AND REVENUEAMOUNT.APPLICATIONCODE = 18) then 1
                    else 2
                end,
            REPORTBUCKET = 
                case 
                    when REVENUEAMOUNT.APPLICATIONCODE in (5,19) and REVENUEAMOUNT.TRANSACTIONTYPECODE IN (0,5) and REVENUESPLIT_EXT.TYPECODE = 2 then 'revenue'
                    when (REVENUEAMOUNT.TRANSACTIONTYPECODE in (0,5) AND REVENUEAMOUNT.APPLICATIONCODE = 18) then 'addon'
                    else 'gift'
                end,
            REVENUEAMOUNT.CHANNELCODEID,
            REVENUEAMOUNT.ID,
            REVENUEAMOUNT.CONSTITUENTID,
            REVENUEAMOUNT.APPEALID,
            REVENUEAMOUNT.DATE,
            --use the MEMBERSHIPTRANSACTION columns normally, but use the ADDON->MEMBERSHIPTRANSACTION columns for addons.

            coalesce(MEMBERSHIPTRANSACTION.ID, ADDON.MEMBERSHIPTRANSACTIONID) as MEMBERSHIPTRANSACTIONID,
            coalesce(MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID,ADDON.MEMBERSHIPLEVELID) AS MEMBERSHIPLEVELID,
            coalesce(MEMBERSHIPTRANSACTION.ACTIONCODE,ADDON.ACTIONCODE) AS ACTIONCODE,
            coalesce(MEMBERSHIPTRANSACTION.ACTION,ADDON.ACTION) AS ACTION,
            coalesce(MEMBERSHIPTRANSACTION.TRANSACTIONDATE, ADDON.TRANSACTIONDATE) AS TRANSACTIONDATE
        from dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as REVENUEAMOUNT
            inner join dbo.REVENUESPLIT_EXT on REVENUEAMOUNT.ID = REVENUESPLIT_EXT.ID
            left join dbo.MEMBERSHIPTRANSACTION on REVENUEAMOUNT.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
            left join (
                select
                    MEMBERSHIPADDON.REVENUESPLITID, 
                    MEMBERSHIP.MEMBERSHIPLEVELID, 
                    MEMBERSHIPTRANSACTION.ID, 
                    MEMBERSHIPTRANSACTION.ACTIONCODE, 
                    MEMBERSHIPTRANSACTION.ACTION,
                    MEMBERSHIPTRANSACTION.TRANSACTIONDATE,
                    MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID
                from dbo.MEMBERSHIPADDON
                    inner join dbo.MEMBERSHIP on MEMBERSHIPADDON.MEMBERSHIPID = MEMBERSHIP.ID
                    inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID = MEMBERSHIPTRANSACTION.ID
            ) as ADDON on ADDON.REVENUESPLITID = REVENUEAMOUNT.ID
        where
            (    
                isnull(MEMBERSHIPTRANSACTION.ID, ADDON.MEMBERSHIPTRANSACTIONID) is not null
                and REVENUEAMOUNT.APPLICATIONCODE in (5,18) 
                and TRANSACTIONTYPECODE not in (2,15)  --not the installment plans

                and isnull(MEMBERSHIPTRANSACTION.TRANSACTIONDATE, ADDON.TRANSACTIONDATE) between @STARTDATE and @ENDDATE
            )
    union all
        select 
            REVENUEAMOUNT.AMOUNTINCURRENCY as AMOUNTINCURRENCY,
            REVENUEAMOUNT.TRANSACTIONTYPE,
            REVENUEAMOUNT.TRANSACTIONTYPECODE,
            REXT_TYPE = REVENUESPLIT_EXT.TYPE,
            REXT_TYPECODE = REVENUESPLIT_EXT.TYPECODE,
            REVENUEAMOUNT.APPLICATION,
            REVENUEAMOUNT.APPLICATIONCODE,
            REPORTBUCKETCODE = 
                case 
                    when REVENUEAMOUNT.APPLICATIONCODE in (5,19) and REVENUEAMOUNT.TRANSACTIONTYPECODE IN (0,5) and REVENUESPLIT_EXT.TYPECODE = 2 then 0
                    when (REVENUEAMOUNT.TRANSACTIONTYPECODE in (0,5) AND REVENUEAMOUNT.APPLICATIONCODE = 18) then 1
                    else 2
                end,
            REPORTBUCKET = 
                case 
                    when REVENUEAMOUNT.APPLICATIONCODE in (5,19) and REVENUEAMOUNT.TRANSACTIONTYPECODE IN (0,5) and REVENUESPLIT_EXT.TYPECODE = 2 then 'revenue'
                    when (REVENUEAMOUNT.TRANSACTIONTYPECODE in (0,5) AND REVENUEAMOUNT.APPLICATIONCODE = 18) then 'addon'
                    else 'gift'
                end,
            REVENUEAMOUNT.CHANNELCODEID,
            REVENUEAMOUNT.ID,
            REVENUEAMOUNT.CONSTITUENTID,
            REVENUEAMOUNT.APPEALID,
            REVENUEAMOUNT.DATE,
            MEMBERSHIPTRANSACTION.ID,
            MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID,
            MEMBERSHIPTRANSACTION.ACTIONCODE,
            MEMBERSHIPTRANSACTION.ACTION,
            MEMBERSHIPTRANSACTION.TRANSACTIONDATE
        from dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as REVENUEAMOUNT
            inner join dbo.REVENUESPLIT_EXT on REVENUEAMOUNT.ID = REVENUESPLIT_EXT.ID
            left join dbo.MEMBERSHIPTRANSACTION on REVENUEAMOUNT.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
        where
            (
                (
                    MEMBERSHIPTRANSACTION.ID is null
                    and REVENUEAMOUNT.DATE between @STARTDATE and @ENDDATE
                )
                -- only get gifts

                and 
                (
                    REVENUEAMOUNT.TRANSACTIONTYPECODE = 7
                    or (REVENUEAMOUNT.TRANSACTIONTYPECODE = 5 and REVENUEAMOUNT.APPLICATIONCODE = 0)
                    or (
                        REVENUEAMOUNT.TRANSACTIONTYPECODE = 0
                        and REVENUEAMOUNT.APPLICATIONCODE in (0,2,3,4,6,7,8,12,13) 
                        and REVENUEAMOUNT.TYPECODE != 2 --don't include recurring gifts, recurring payments are included later

                    ) 
                    or (REVENUEAMOUNT.TRANSACTIONTYPECODE = 0 and REVENUEAMOUNT.APPLICATIONCODE = 1 and REVENUEAMOUNT.TYPECODE = 0)
                )
            )
        union all
        select distinct 
            PAYMENT_REVENUEAMOUNT.AMOUNTINCURRENCY as AMOUNTINCURRENCY,
            PAYMENT_REVENUEAMOUNT.TRANSACTIONTYPE,
            PAYMENT_REVENUEAMOUNT.TRANSACTIONTYPECODE,
            REXT_TYPE = REVENUESPLIT_EXT.TYPE,
            REXT_TYPECODE = REVENUESPLIT_EXT.TYPECODE,
            PAYMENT_REVENUEAMOUNT.APPLICATION,
            PAYMENT_REVENUEAMOUNT.APPLICATIONCODE,
            REPORTBUCKETCODE =
                case 
                    when PAYMENT_REVENUEAMOUNT.APPLICATIONCODE in (5,19) and PAYMENT_REVENUEAMOUNT.TRANSACTIONTYPECODE = 0 and REVENUESPLIT_EXT.TYPECODE = 2 then 0
                    when (PAYMENT_REVENUEAMOUNT.TRANSACTIONTYPECODE = 0 AND PAYMENT_REVENUEAMOUNT.TYPECODE = 18) then 1
                    else 2
                end,
            REPORTBUCKET = 
                case 
                    when PAYMENT_REVENUEAMOUNT.APPLICATIONCODE in (5,19) and PAYMENT_REVENUEAMOUNT.TRANSACTIONTYPECODE = 0 and REVENUESPLIT_EXT.TYPECODE = 2 then 'revenue'
                    when (PAYMENT_REVENUEAMOUNT.TRANSACTIONTYPECODE = 0 AND PAYMENT_REVENUEAMOUNT.TYPECODE = 18) then 'addon'
                    else 'gift'
                end,
            PAYMENT_REVENUEAMOUNT.CHANNELCODEID,
            PAYMENT_REVENUEAMOUNT.ID,
            PAYMENT_REVENUEAMOUNT.CONSTITUENTID,
            PAYMENT_REVENUEAMOUNT.APPEALID,
            PAYMENT_REVENUEAMOUNT.DATE,
            MEMBERSHIPTRANSACTION.ID as MEMBERSHIPTRANSACTIONID,
            MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID,
            MEMBERSHIPTRANSACTION.ACTIONCODE,
            MEMBERSHIPTRANSACTION.ACTION,
            MEMBERSHIPTRANSACTION.TRANSACTIONDATE
        from dbo.INSTALLMENTPAYMENT
            inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as PAYMENT_REVENUEAMOUNT
                on PAYMENT_REVENUEAMOUNT.ID = INSTALLMENTPAYMENT.PAYMENTID
            inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = PAYMENT_REVENUEAMOUNT.ID
            inner join dbo.REVENUE_EXT as PLEDGE_REVENUE on PLEDGE_REVENUE.ID = INSTALLMENTPAYMENT.PLEDGEID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on PLEDGE_REVENUE.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
        where 
            PAYMENT_REVENUEAMOUNT.DATE between @STARTDATE and @ENDDATE
            and PAYMENT_REVENUEAMOUNT.TRANSACTIONTYPECODE = 0 
            and REVENUESPLIT_EXT.TYPECODE IN (2, 18) 
            and PAYMENT_REVENUEAMOUNT.APPLICATIONCODE in (18,19)
        union all
        select distinct 
            PAYMENT_REVENUEAMOUNT.AMOUNTINCURRENCY as AMOUNTINCURRENCY,
            PAYMENT_REVENUEAMOUNT.TRANSACTIONTYPE,
            PAYMENT_REVENUEAMOUNT.TRANSACTIONTYPECODE,
            REXT_TYPE = REVENUESPLIT_EXT.TYPE,
            REXT_TYPECODE = REVENUESPLIT_EXT.TYPECODE,
            PAYMENT_REVENUEAMOUNT.APPLICATION,
            PAYMENT_REVENUEAMOUNT.APPLICATIONCODE,
            REPORTBUCKETCODE = 0,    --All records here are payment revenue.

            REPORTBUCKET = 'revenue',
            PAYMENT_REVENUEAMOUNT.CHANNELCODEID,
            PAYMENT_REVENUEAMOUNT.ID,
            PAYMENT_REVENUEAMOUNT.CONSTITUENTID,
            PAYMENT_REVENUEAMOUNT.APPEALID,
            PAYMENT_REVENUEAMOUNT.DATE,
            MEMBERSHIPTRANSACTION.ID as MEMBERSHIPTRANSACTIONID,
            MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID,
            MEMBERSHIPTRANSACTION.ACTIONCODE,
            MEMBERSHIPTRANSACTION.ACTION,
            MEMBERSHIPTRANSACTION.TRANSACTIONDATE
        from dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as PAYMENT_REVENUEAMOUNT
            inner join dbo.RECURRINGGIFTINSTALLMENTPAYMENT
                on PAYMENT_REVENUEAMOUNT.REVENUEID = RECURRINGGIFTINSTALLMENTPAYMENT.PAYMENTID
            inner join dbo.RECURRINGGIFTINSTALLMENT on RECURRINGGIFTINSTALLMENT.ID = RECURRINGGIFTINSTALLMENTPAYMENT.RECURRINGGIFTINSTALLMENTID
            inner join dbo.FINANCIALTRANSACTION PLEDGE_XACTION on PLEDGE_XACTION.ID = RECURRINGGIFTINSTALLMENT.REVENUEID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM PLEDGE_FTLI on PLEDGE_FTLI.FINANCIALTRANSACTIONID = PLEDGE_XACTION.ID
            inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = PAYMENT_REVENUEAMOUNT.ID
            inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = PLEDGE_FTLI.ID
        where 
            PAYMENT_REVENUEAMOUNT.DATE between @STARTDATE and @ENDDATE
            and PAYMENT_REVENUEAMOUNT.TRANSACTIONTYPECODE = 0 
            and REVENUESPLIT_EXT.TYPECODE = 2 
            and PAYMENT_REVENUEAMOUNT.APPLICATIONCODE = 3

    ) as [REVENUESPLITS]
    left join (
        select 
            sum(TOTAL) as AMOUNT,
            REVENUESPLITID
        from dbo.CREDITITEM
        group by REVENUESPLITID
    ) as [REFUNDS] on REVENUESPLITS.ID = REFUNDS.REVENUESPLITID
)

insert into tempdb.#temp_membershiprevenuereportdata (
    TIERCODEID,
    TIERSEQUENCE,
    TIERCODENAME,
    MEMBERSHIPLEVEL,
    MEMBERSHIPLEVELSEQUENCE,
    MEMBERSHIPLEVELNAME,
    REVENUEAMOUNT,
    TRANSACTIONTYPECODE,
    REVENUEAPPLICATIONCODE,
    REVENUECHANNEL,
    MEMBERSHIPTRANSACTIONTYPE,
    REPORTBUCKETCODE,
    REPORTBUCKET,
  LEFTOVERDISCOUNTANDREFUNDS
)
-- Get membership-related revenue

select
    TIERCODE.ID,
    TIERCODE.SEQUENCE,
    TIERCODE.DESCRIPTION,
    MEMBERSHIPLEVEL.ID,
    MEMBERSHIPLEVEL.SEQUENCE,
    MEMBERSHIPLEVEL.NAME,
    REVENUE_CTE.AMOUNTINCURRENCY as AMOUNT,
    REVENUE_CTE.TRANSACTIONTYPECODE,
    REVENUE_CTE.APPLICATIONCODE,
    REVENUE_CTE.CHANNELCODEID,
    REVENUE_CTE.ACTIONCODE,
    REVENUE_CTE.REPORTBUCKETCODE,
    REVENUE_CTE.REPORTBUCKET,
  REVENUE_CTE.LEFTOVERDISCOUNTANDREFUNDS
from REVENUE_CTE 
    inner join dbo.MEMBERSHIPLEVEL on REVENUE_CTE.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
    --left outer join REVENUE_CTE on REVENUE_CTE.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID

    left outer join dbo.TIERCODE on MEMBERSHIPLEVEL.TIERCODEID = TIERCODE.ID
where
    MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @PROGRAMID and
    MEMBERSHIPLEVEL.OBTAINLEVELCODE = 0 and
    REVENUE_CTE.REPORTBUCKETCODE in (0,1)

union all
-- Get non-membership gifts from same time period and same constituents

select
    TIERCODE.ID,
    TIERCODE.SEQUENCE,
    TIERCODE.DESCRIPTION,
    MEMBERSHIPLEVEL.ID,
    MEMBERSHIPLEVEL.SEQUENCE,
    MEMBERSHIPLEVEL.NAME,
    REVENUE_CTE.AMOUNTINCURRENCY as AMOUNT,
    REVENUE_CTE.TRANSACTIONTYPECODE,
    REVENUE_CTE.APPLICATIONCODE,
    null,
    null,
    REVENUE_CTE.REPORTBUCKETCODE,
    REVENUE_CTE.REPORTBUCKET,
    REVENUE_CTE.LEFTOVERDISCOUNTANDREFUNDS
from dbo.MEMBERSHIPLEVEL
    inner join dbo.MEMBERSHIP on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
    inner join dbo.MEMBER on MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
    inner join REVENUE_CTE on REVENUE_CTE.CONSTITUENTID = MEMBER.CONSTITUENTID
    left outer join dbo.TIERCODE on MEMBERSHIPLEVEL.TIERCODEID = TIERCODE.ID
where 
    MEMBERSHIP.STATUSCODE = 0 and
    MEMBER.ISDROPPED = 0 and
    MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @PROGRAMID and
    MEMBERSHIPLEVEL.OBTAINLEVELCODE = 0 and
    REVENUE_CTE.REPORTBUCKETCODE = 2;
end