USP_DATALIST_MEMBERSHIPREVENUEREPORT

CREATE procedure dbo.USP_DATALIST_MEMBERSHIPREVENUEREPORT
(
    @PROGRAMID uniqueidentifier,
    @STARTDATE datetime,
    @ENDDATE datetime,
    @GROUPTYPE tinyint,
    @CURRENCYCODE tinyint
) as
set nocount on;

--GROUPTYPE Types

--  0:  Revenue Channel

--  1:  Membership Transaction type


--CURRENCYCODE types

--  0:  Base

--  1:  Organization


declare @ORGANIZATIONCURRENCYID uniqueidentifier;
set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

declare @SELECTEDCURRENCYID uniqueidentifier;
if coalesce(@CURRENCYCODE, 1) = 1
begin
    set @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID;
end
else
begin
    select @SELECTEDCURRENCYID = MEMBERSHIPPROGRAM.BASECURRENCYID
      from dbo.MEMBERSHIPPROGRAM
     where MEMBERSHIPPROGRAM.ID = @PROGRAMID;
end

declare @DECIMALDIGITS tinyint;
declare @ROUNDINGTYPECODE tinyint;
declare @CURRENCYISO nvarchar(6);
declare @CURRENCYSYMBOL nvarchar(10);
declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;

select
    @CURRENCYISO = CURRENCY.ISO4217,
    @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
    @CURRENCYSYMBOL = CURRENCY.CURRENCYSYMBOL,
    @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE,
    @CURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCY.SYMBOLDISPLAYSETTINGCODE
from
    dbo.CURRENCY
where
    CURRENCY.ID = @SELECTEDCURRENCYID;

if @GROUPTYPE not in (0,1)
    raiserror('Group by type must be specified', 13, 1);

create table tempdb.#temp_membershiprevenuereportdata (
    TIERCODEID uniqueidentifier,
    TIERSEQUENCE int,
    TIERCODENAME nvarchar(100) collate database_default,
    MEMBERSHIPLEVEL uniqueidentifier,
    MEMBERSHIPLEVELSEQUENCE int,
    MEMBERSHIPLEVELNAME nvarchar(100) collate database_default,
    REVENUEAMOUNT money,
    TRANSACTIONTYPECODE tinyint,
    REVENUECHANNEL uniqueidentifier,
    MEMBERSHIPTRANSACTIONTYPE tinyint,
    REVENUEAPPLICATIONCODE tinyint,
    REPORTBUCKETCODE tinyint,
    REPORTBUCKET varchar(10) collate database_default,
    LEFTOVERDISCOUNTANDREFUNDS money
);

--Populating temp table with revenue data

exec dbo.USP_MEMBERSHIPREVENUEREPORT_POPULATEREVENUEDATA
    @PROGRAMID,
    @SELECTEDCURRENCYID,
    @ORGANIZATIONCURRENCYID,
    @DECIMALDIGITS,
    @ROUNDINGTYPECODE,
    @STARTDATE,
    @ENDDATE;

-- Get the information for the report


if @GROUPTYPE = 0
begin

    with MEMBERSHIPCOUNT_CTE as (
        select
            MEMBERSHIPLEVELID,
            COUNT(MEMBERSHIPID) MEMBERSHIPCOUNT
        from dbo.UFN_MEMBERSHIPPROGRAM_MEMBERSHIPSASOF(@PROGRAMID, @ENDDATE)
        where ACTIONCODE <> 4
        group by MEMBERSHIPLEVELID
    )
    select
        isnull(MJ1.TIERCODENAME,'') as TIER,
        MJ1.MEMBERSHIPLEVELNAME as MEMBERSHIPLEVEL,
        dbo.UFN_CHANNELCODE_GETDESCRIPTION(MJ1.REVENUECHANNEL) as INNERGROUP,
        (
            select sum(REVENUEAMOUNT)
            from tempdb.#temp_membershiprevenuereportdata as MJ2
            where
                MJ2.MEMBERSHIPTRANSACTIONTYPE is not null and
                MJ2.REPORTBUCKETCODE = 0 and -- revenue

                MJ2.MEMBERSHIPLEVEL = MJ1.MEMBERSHIPLEVEL and
                (MJ2.REVENUECHANNEL = MJ1.REVENUECHANNEL or
                    (MJ2.REVENUECHANNEL is null and MJ1.REVENUECHANNEL is null))
        ) as TOTALREVENUE,
        avg(MEMBERSHIPCOUNT_CTE.MEMBERSHIPCOUNT) as NUMMEMBERS,
        (
            select sum(REVENUEAMOUNT) - (select sum(LEFTOVERDISCOUNTANDREFUNDS) from tempdb.#temp_membershiprevenuereportdata as MJ2
                                      where
                                              MJ2.MEMBERSHIPTRANSACTIONTYPE is not null and
                                              MJ2.REPORTBUCKETCODE = 0 and -- revenue

                                              MJ2.MEMBERSHIPLEVEL = MJ1.MEMBERSHIPLEVEL and
                                              (MJ2.REVENUECHANNEL = MJ1.REVENUECHANNEL or
                                                  (MJ2.REVENUECHANNEL is null and MJ1.REVENUECHANNEL is null)))
            from tempdb.#temp_membershiprevenuereportdata as MJ2
            where
                MJ2.MEMBERSHIPTRANSACTIONTYPE is null and
                MJ2.MEMBERSHIPLEVEL = MJ1.MEMBERSHIPLEVEL and
                MJ2.REPORTBUCKETCODE = 2    --gifts

        ) as ADDITIONALGIFTS,
        (
            select sum(REVENUEAMOUNT)
            from tempdb.#temp_membershiprevenuereportdata as MJ2
            where MJ2.REPORTBUCKETCODE = 1 and --add-ons

                MJ2.MEMBERSHIPLEVEL = MJ1.MEMBERSHIPLEVEL and
                (MJ2.REVENUECHANNEL = MJ1.REVENUECHANNEL or
                (MJ2.REVENUECHANNEL is null and MJ1.REVENUECHANNEL is null))
        ) as ADDONREVENUE,
        @CURRENCYSYMBOL as CURRENCYSYMBOL,
        @CURRENCYISO as ISOCURRENCYCODE,
        @DECIMALDIGITS as DECIMALDIGITS,
        @CURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
        @SELECTEDCURRENCYID as BASECURRENCYID,
        rank() over (partition by MJ1.MEMBERSHIPLEVEL order by MJ1.REVENUECHANNEL) ITEMSEQUENCE
    from tempdb.#temp_membershiprevenuereportdata as MJ1
    left join MEMBERSHIPCOUNT_CTE on MJ1.MEMBERSHIPLEVEL = MEMBERSHIPCOUNT_CTE.MEMBERSHIPLEVELID
    group by
        MJ1.TIERCODEID,
        MJ1.TIERSEQUENCE,
        MJ1.TIERCODENAME,
        MJ1.MEMBERSHIPLEVEL,
        MJ1.MEMBERSHIPLEVELSEQUENCE,
        MJ1.MEMBERSHIPLEVELNAME,
        MJ1.REVENUECHANNEL
    order by
        MJ1.TIERSEQUENCE,
        MJ1.MEMBERSHIPLEVELSEQUENCE,
        INNERGROUP
end
else if @GROUPTYPE = 1
begin
    with MEMBERSHIPCOUNT_CTE as (
        select
            MEMBERSHIPLEVELID,
            COUNT(MEMBERSHIPID) MEMBERSHIPCOUNT
        from dbo.UFN_MEMBERSHIPPROGRAM_MEMBERSHIPSASOF(@PROGRAMID, @ENDDATE)
        where ACTIONCODE <> 4
        group by MEMBERSHIPLEVELID
    )
    select
        isnull(MJ1.TIERCODENAME,'') as TIER,
        MJ1.MEMBERSHIPLEVELNAME as MEMBERSHIPLEVEL,
        dbo.UFN_MEMBERSHIPTRANSACTION_ACTIONCODE_GETDESCRIPTION(MJ1.MEMBERSHIPTRANSACTIONTYPE) as INNERGROUP,
        (
            select sum(REVENUEAMOUNT)
            from tempdb.#temp_membershiprevenuereportdata as MJ2
            where
                MJ2.MEMBERSHIPTRANSACTIONTYPE is not null and
                MJ2.REPORTBUCKETCODE = 0 and -- revenue

                MJ2.MEMBERSHIPLEVEL = MJ1.MEMBERSHIPLEVEL and
                MJ2.MEMBERSHIPTRANSACTIONTYPE = MJ1.MEMBERSHIPTRANSACTIONTYPE
        ) as TOTALREVENUE,
        avg(MEMBERSHIPCOUNT_CTE.MEMBERSHIPCOUNT) as NUMMEMBERS,
        (
            select sum(REVENUEAMOUNT) - (select sum(LEFTOVERDISCOUNTANDREFUNDS) from tempdb.#temp_membershiprevenuereportdata as MJ2
                                                                        where
                                                                            MJ2.MEMBERSHIPTRANSACTIONTYPE is not null and
                                                                            MJ2.REPORTBUCKETCODE = 0 and -- revenue

                                                                            MJ2.MEMBERSHIPLEVEL = MJ1.MEMBERSHIPLEVEL 
                                                                            )
            from tempdb.#temp_membershiprevenuereportdata as MJ2
            where
                MJ2.MEMBERSHIPTRANSACTIONTYPE is null and
                MJ2.MEMBERSHIPLEVEL = MJ1.MEMBERSHIPLEVEL and
                MJ2.REPORTBUCKETCODE = 2 -- gifts

        ) as ADDITIONALGIFTS,
        (
            select sum(REVENUEAMOUNT)
            from tempdb.#temp_membershiprevenuereportdata as MJ2
            where 
                MJ2.REPORTBUCKETCODE = 1 and -- add-ons

                MJ2.MEMBERSHIPLEVEL = MJ1.MEMBERSHIPLEVEL and
                MJ2.MEMBERSHIPTRANSACTIONTYPE = MJ1.MEMBERSHIPTRANSACTIONTYPE
        ) as ADDONREVENUE,
        @CURRENCYSYMBOL as CURRENCYSYMBOL,
        @CURRENCYISO as ISOCURRENCYCODE,
        @DECIMALDIGITS as DECIMALDIGITS,
        @CURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
        @SELECTEDCURRENCYID as BASECURRENCYID,
        rank() over (partition by MJ1.MEMBERSHIPLEVEL order by MJ1.MEMBERSHIPTRANSACTIONTYPE) ITEMSEQUENCE
    from tempdb.#temp_membershiprevenuereportdata as MJ1
    left join MEMBERSHIPCOUNT_CTE on MJ1.MEMBERSHIPLEVEL = MEMBERSHIPCOUNT_CTE.MEMBERSHIPLEVELID
    group by
        MJ1.TIERCODEID,
        MJ1.TIERSEQUENCE,
        MJ1.TIERCODENAME,
        MJ1.MEMBERSHIPLEVEL,
        MJ1.MEMBERSHIPLEVELSEQUENCE,
        MJ1.MEMBERSHIPLEVELNAME,
        MJ1.MEMBERSHIPTRANSACTIONTYPE
    order by
        MJ1.TIERSEQUENCE,
        MJ1.MEMBERSHIPLEVELSEQUENCE,
        INNERGROUP
end

drop table tempdb.#temp_membershiprevenuereportdata;

return 0;