USP_MEMBERSHIPPROGRAM_GETREVENUETRENDBYLEVEL

        CREATE procedure dbo.USP_MEMBERSHIPPROGRAM_GETREVENUETRENDBYLEVEL
        (
            @PROGRAMID uniqueidentifier = null,
            @BREAKDOWNTYPE tinyint = null,
            @FROMDATE datetime = null,
            @TODATE datetime = null
        )
        as
        begin

            set @FROMDATE = dbo.UFN_DATE_GETEARLIESTTIME(@FROMDATE);
            set @TODATE = dbo.UFN_DATE_GETLATESTTIME(@TODATE);

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

            declare @SELECTEDCURRENCYID uniqueidentifier;

            set @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID;

            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 @BREAKDOWNTYPE = 0
            begin

                declare @LEVELPERFORMANCE table(ID uniqueidentifier, TOTAL int);
                insert into @LEVELPERFORMANCE
                select
                    MEMBERSHIPLEVELID,
                    count(*)
                from dbo.MEMBERSHIP
                where JOINDATE between @FROMDATE and @TODATE
                and MEMBERSHIP.MEMBERSHIPPROGRAMID = @PROGRAMID
                group by MEMBERSHIPLEVELID

                declare @TOPLEVELS table(ID uniqueidentifier)
                insert into @TOPLEVELS
                select top(6)
                    ID
                from @LEVELPERFORMANCE
                order by TOTAL desc

                select
                    TRANSACTIONDATE,
                    sum(TOTAL) as TOTAL,
                    LEVELNAME
                from (
                    select
                        REVENUESPLITS.DATE as TRANSACTIONDATE,
                        REVENUESPLITS.AMOUNTINCURRENCY - coalesce(REFUND.CREDITTOTAL, 0) as TOTAL,
                        MEMBERSHIPLEVEL.NAME as LEVELNAME
                    from dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(
                        @SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE
                    ) as REVENUESPLITS
                    left join dbo.UFN_CREDIT_GETSPLITCREDITAMOUNT_BULK() as REFUND
                        on REFUND.SOURCELINEITEMID = REVENUESPLITS.ID
                    inner join dbo.UFN_MEMBERSHIPTRANSACTIONSPLITS_BYPROGRAM(@PROGRAMID, @FROMDATE, @TODATE) MEMBERSHIPSPLITS
                        on MEMBERSHIPSPLITS.REVENUESPLITID = REVENUESPLITS.ID
                    inner join dbo.MEMBERSHIPLEVEL
                        on MEMBERSHIPLEVEL.ID = MEMBERSHIPSPLITS.LEVELID
                    inner join @TOPLEVELS T
                        on T.ID = MEMBERSHIPLEVEL.ID
                    where 
                        REVENUESPLITS.TYPECODE in (2,18)

                    union all

                    -- Membership promotions

                    select
                        SALESORDER.TRANSACTIONDATE TRANSACTIONDATE,
                        -SALESORDERITEMMEMBERSHIPITEMPROMOTION.ORGANIZATIONAMOUNT TOTAL,
                        MEMBERSHIPLEVEL.NAME LEVELNAME
                    from dbo.SALESORDER
                    inner join dbo.SALESORDERITEM
                        on SALESORDERITEM.SALESORDERID = SALESORDER.ID
                    inner join dbo.SALESORDERITEMMEMBERSHIP
                        on SALESORDERITEMMEMBERSHIP.ID = SALESORDERITEM.ID
                    inner join dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
                        on SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID = SALESORDERITEM.ID
                    inner join dbo.MEMBERSHIPLEVEL
                        on SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                    where SALESORDER.TRANSACTIONDATE between @FROMDATE and @TODATE
                    and MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @PROGRAMID
                    and exists(select 1 from @TOPLEVELS where ID = MEMBERSHIPLEVEL.ID)

                ) as RAWMEMBERSHIPREVENUE
                group by TRANSACTIONDATE, LEVELNAME
                order by LEVELNAME asc

            end
            else
            begin

                declare @TIERPERFORMANCE table(ID uniqueidentifier, TOTAL int);
                insert into @TIERPERFORMANCE
                select
                    TIERCODEID,
                    count(*)
                from dbo.MEMBERSHIP
                inner join dbo.MEMBERSHIPLEVEL
                    on MEMBERSHIPLEVEL.ID = MEMBERSHIP.MEMBERSHIPLEVELID
                where JOINDATE between @FROMDATE and @TODATE
                and MEMBERSHIP.MEMBERSHIPPROGRAMID = @PROGRAMID
                group by TIERCODEID

                declare @TOPTIERS table(ID uniqueidentifier)
                insert into @TOPTIERS
                select top(6)
                    ID
                from @TIERPERFORMANCE
                order by TOTAL desc

                select
                    TRANSACTIONDATE,
                    sum(TOTAL) as TOTAL,
                    TIERNAME as LEVELNAME
                from (
                    select
                        REVENUESPLITS.DATE as TRANSACTIONDATE,
                        REVENUESPLITS.AMOUNTINCURRENCY - coalesce(REFUND.CREDITTOTAL, 0) as TOTAL,
                        coalesce(TIERCODE.DESCRIPTION, 'No reporting group') as TIERNAME
                    from dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(
                        @SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE
                    ) as REVENUESPLITS
                    left join dbo.UFN_CREDIT_GETSPLITCREDITAMOUNT_BULK() as REFUND 
                        on REFUND.SOURCELINEITEMID = REVENUESPLITS.ID
                    inner join dbo.UFN_MEMBERSHIPTRANSACTIONSPLITS_BYPROGRAM(@PROGRAMID, @FROMDATE, @TODATE) MEMBERSHIPSPLITS
                        on MEMBERSHIPSPLITS.REVENUESPLITID = REVENUESPLITS.ID
                    left join dbo.TIERCODE
                        on TIERCODE.ID = MEMBERSHIPSPLITS.TIERCODEID
                    where 
                        REVENUESPLITS.TYPECODE in (2,18)
                        and (TIERCODE.ID is null or exists(select 1 from @TOPTIERS where ID = TIERCODE.ID))

                    union all

                    -- Membership promotions

                    select
                        SALESORDER.TRANSACTIONDATE TRANSACTIONDATE,
                        -SALESORDERITEMMEMBERSHIPITEMPROMOTION.ORGANIZATIONAMOUNT TOTAL,
                        coalesce(TIERCODE.[DESCRIPTION], 'no reporting group') TIERNAME
                    from dbo.SALESORDER
                    inner join dbo.SALESORDERITEM
                        on SALESORDERITEM.SALESORDERID = SALESORDER.ID
                    inner join dbo.SALESORDERITEMMEMBERSHIP
                        on SALESORDERITEMMEMBERSHIP.ID = SALESORDERITEM.ID
                    inner join dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
                        on SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID = SALESORDERITEM.ID
                    inner join dbo.MEMBERSHIPLEVEL
                        on SALESORDERITEMMEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
                    left join dbo.TIERCODE
                        on MEMBERSHIPLEVEL.TIERCODEID = TIERCODE.ID
                    where SALESORDER.TRANSACTIONDATE between @FROMDATE and @TODATE
                    and MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID = @PROGRAMID
                    and (TIERCODE.ID is null or exists(select 1 from @TOPTIERS where ID = TIERCODE.ID))

                ) as RAWMEMBERSHIPREVENUE
                group by TRANSACTIONDATE, TIERNAME
                order by TIERNAME asc

            end

        end