USP_MEMBERSHIPPROGRAM_GETREVENUETREND

        CREATE procedure dbo.USP_MEMBERSHIPPROGRAM_GETREVENUETREND
        (
            @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;

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

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


            select
                TRANSACTIONDATE as TRANSACTIONDATE,
                sum(TOTAL) as TOTAL,
                PROGRAMNAME as PROGRAMNAME
            from (
                    select
                        REVENUESPLITS.DATE as TRANSACTIONDATE,
                        REVENUESPLITS.AMOUNTINCURRENCY - coalesce(REFUND.CREDITTOTAL, 0) as TOTAL,
                        MEMBERSHIPPROGRAMSPLITS.PROGRAMNAME as PROGRAMNAME
                    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 
                    (
                        select
                            MA.REVENUESPLITID as REVENUESPLITID,
                            MP.NAME as PROGRAMNAME
                        from dbo.MEMBERSHIPTRANSACTION MT
                        inner join dbo.MEMBERSHIPADDON MA
                            on MA.MEMBERSHIPTRANSACTIONID = MT.ID
                        inner join dbo.MEMBERSHIPLEVEL ML
                            on ML.ID = MT.MEMBERSHIPLEVELID
                        inner join @TOPPROGRAMS TOPPROGRAMS
                            on ML.MEMBERSHIPPROGRAMID = TOPPROGRAMS.ID
                        inner join dbo.MEMBERSHIPPROGRAM MP
                            on MP.ID = TOPPROGRAMS.ID
                        where
                            MT.TRANSACTIONDATE between @FROMDATE and @TODATE

                        union all

                        select
                            MT.REVENUESPLITID as REVNEUESPLITID,
                            MP.NAME as PROGRAMNAME
                        from dbo.MEMBERSHIPTRANSACTION MT
                        inner join dbo.MEMBERSHIPLEVEL ML
                            on ML.ID = MT.MEMBERSHIPLEVELID
                        inner join @TOPPROGRAMS TOPPROGRAMS
                            on ML.MEMBERSHIPPROGRAMID = TOPPROGRAMS.ID
                        inner join dbo.MEMBERSHIPPROGRAM MP
                            on MP.ID = TOPPROGRAMS.ID
                        where
                            MT.TRANSACTIONDATE between @FROMDATE and @TODATE                        
                    ) as MEMBERSHIPPROGRAMSPLITS
                        on MEMBERSHIPPROGRAMSPLITS.REVENUESPLITID = REVENUESPLITS.ID
                    where 
                        REVENUESPLITS.TYPECODE in (2,18)

                union all

                -- Membership promotions

                select
                    SALESORDER.TRANSACTIONDATE TRANSACTIONDATE,
                    -SALESORDERITEMMEMBERSHIPITEMPROMOTION.ORGANIZATIONAMOUNT TOTAL,
                    MEMBERSHIPPROGRAM.NAME PROGRAMNAME
                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.MEMBERSHIPPROGRAM
                    on SALESORDERITEMMEMBERSHIP.MEMBERSHIPPROGRAMID = MEMBERSHIPPROGRAM.ID
                where SALESORDER.TRANSACTIONDATE between @FROMDATE and @TODATE
                and exists(select 1 from @TOPPROGRAMS where ID = MEMBERSHIPPROGRAM.ID)
            ) as RAWMEMBERSHIPREVENUE
            group by TRANSACTIONDATE, PROGRAMNAME
            order by PROGRAMNAME desc



        end