USP_REPORT_REVENUEBYPAYMENTMETHODALTRU

create procedure dbo.USP_REPORT_REVENUEBYPAYMENTMETHODALTRU
(
    @REVENUETYPES xml = null,
    @PAYMENTMETHODS xml = null,
    @STARTDATE datetime = null,
    @ENDDATE datetime = null,
    @REPORTUSERID nvarchar(128) = null,
    @CURRENCYCODE tinyint = null,
    @ALTREPORTUSERID nvarchar(128) = null
)
as begin
    set nocount on;

    declare @SELECTEDCURRENCYID uniqueidentifier;

    declare @REVENUEPAYMENTMETHODTABLEID uniqueidentifier = '611E9794-28B4-4F03-A6A5-54BD92FB506B';
    declare @REVENUESPLITTABLEID uniqueidentifier = '274a03e5-066c-4f34-bfc2-fbb352fad140';
    --declare @REVENUESPLITTABLEID uniqueidentifier = '59E46B6A-F096-45A5-AA88-03C690F5D8ED';

    declare @CURRENTAPPUSERID uniqueidentifier = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
    declare @ORGANIZATIONCURRENCYID uniqueidentifier;
    declare @DECIMALDIGITS tinyint;
    declare @ROUNDINGTYPECODE tinyint;

    if @CURRENCYCODE = 3
    begin
        if dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID) is not null
        begin
            select @SELECTEDCURRENCYID = [CURRENCYSET].[BASECURRENCYID]
            from dbo.[CURRENCYSET]
            where
                [CURRENCYSET].[ID] = dbo.UFN_CURRENCYSET_GETAPPUSERCURRENCYSET(@CURRENTAPPUSERID);
        end
        else
        begin
            select @SELECTEDCURRENCYID = [CURRENCYSET].[BASECURRENCYID]
            from dbo.[CURRENCYSET]
            where
                [CURRENCYSET].[ID] = dbo.UFN_CURRENCYSET_GETAPPUSERDEFAULTCURRENCYSET();
        end
    end
    else  -- 2014/04/02 - Is there a begin/end missing here? I'm reducing the indentation of subsequent currency lines to better reflect the reality of execution.

        set @SELECTEDCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

    set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

    select @DECIMALDIGITS = [DECIMALDIGITS],
        @ROUNDINGTYPECODE = [ROUNDINGTYPECODE]
    from dbo.[CURRENCY]
    where ID = @SELECTEDCURRENCYID;

    create table #PAYMENTMETHODS_T
    (
        [PAYMENTMETHODCODE] tinyint,
        [PAYMENTMETHOD] nvarchar(100)
    );
    insert into #PAYMENTMETHODS_T([PAYMENTMETHODCODE], [PAYMENTMETHOD])
    select distinct
        [ENUMVALUES].[ENUMXML].value('@ID', 'tinyint'),
        [ENUMVALUES].[ENUMXML].value('@Translation', 'nvarchar(100)')
    from
        dbo.[TABLECATALOG]
    cross apply [TABLECATALOG].[TABLESPECXML].nodes
    (
        'declare namespace bbtable="bb_appfx_table";
        /*/bbtable:Fields/bbtable:EnumField[@Name="PAYMENTMETHODCODE"]/bbtable:EnumValues/bbtable:EnumValue'
    ) as [ENUMVALUES]([ENUMXML])
    where [TABLECATALOG].[ID] = @REVENUEPAYMENTMETHODTABLEID;


    /* Determine available payment methods: */
    create table #AVAILABLEPAYMENTMETHODS
    (
        [PAYMENTMETHODCODE] tinyint primary key,
        [PAYMENTMETHOD] nvarchar(100)
    );

    insert into #AVAILABLEPAYMENTMETHODS
    (
        [PAYMENTMETHODCODE],
        [PAYMENTMETHOD]
    )
    select
        [PAYMENTMETHODS_T].[PAYMENTMETHODCODE], [PAYMENTMETHODS_T].[PAYMENTMETHOD]
    from #PAYMENTMETHODS_T as [PAYMENTMETHODS_T]
    where [PAYMENTMETHODS_T].[PAYMENTMETHODCODE] in
    (
        select
            [PAYMENTMETHODS].[PAYMENTMETHOD].value('PAYMENTMETHOD[1]', 'tinyint')
        from
            @PAYMENTMETHODS.nodes('PAYMENTMETHODS/ITEM') as [PAYMENTMETHODS]([PAYMENTMETHOD])
    ) or @PAYMENTMETHODS is null;


    /* Determine available revenue types: */
    create table #AVAILABLEREVENUETYPES
    (
        [REVENUETYPECODE] tinyint primary key,
        [REVENUETYPE] nvarchar(100)
    );

    insert into #AVAILABLEREVENUETYPES
    select distinct
        [ENUMVALUES].[ENUMXML].value('@ID', 'tinyint'),
        [ENUMVALUES].[ENUMXML].value('@Translation', 'nvarchar(100)')
    from
        dbo.[TABLECATALOG]
    cross apply [TABLECATALOG].[TABLESPECXML].nodes
    (
        'declare namespace bbtable="bb_appfx_table";
        /*/bbtable:Fields/bbtable:EnumField[@Name="TYPECODE"]/bbtable:EnumValues/bbtable:EnumValue'
    ) as [ENUMVALUES]([ENUMXML])
    where [TABLECATALOG].[ID] = @REVENUESPLITTABLEID and
        ENUMVALUES.[ENUMXML].value('@ID', 'tinyint') in(
            select
                [REVENUETYPES].[REVENUETYPE].value('REVENUETYPE[1]', 'tinyint')
            from
                @REVENUETYPES.nodes('REVENUETYPES/ITEM') as [REVENUETYPES]([REVENUETYPE])
        ) or @REVENUETYPES is null


    --Add in the fake 'Group Sales Deposit' type since group sales revenue
    --points to tickets until the order is complete (and incomplete group sales
    --revenue is included)
    if exists
    (
        select 1
        from @REVENUETYPES.nodes('REVENUETYPES/ITEM') as [REVENUETYPES]([REVENUETYPE])
        where [REVENUETYPES].[REVENUETYPE].value('REVENUETYPE[1]', 'tinyint') = 255
    )
    begin
        insert into #AVAILABLEREVENUETYPES
        values (255, 'Group Sales Deposit')
    end;

    declare @ISOCURRENCYCODE nvarchar(3)
    declare @CURRENCYSYMBOL nvarchar(5)
    declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint
    declare @CURRENCYDECIMALDIGITS integer

    select
        @ISOCURRENCYCODE = CURRENCYPROPERTIES.ISO4217,
        @CURRENCYSYMBOL = CURRENCYPROPERTIES.CURRENCYSYMBOL,
        @CURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
        @CURRENCYDECIMALDIGITS = CURRENCYPROPERTIES.DECIMALDIGITS
    from dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES;


    /*    Select out the cartesian product of revenue types and payment methods; then join that with revenue: */
    /* Use a fake payment method table to turn auction donations from having a payment method of None to a payment method of Auction donation */


    create table #REVENUEDATA (
        ID uniqueidentifier,
        AMOUNT money,
        PAYMENTMETHODCODE tinyint,
        REVENUETYPECODE tinyint,
        TRANSACTIONID uniqueidentifier
    )

    create clustered index REVENUEDATA_IX1 on #REVENUEDATA (ID)

    insert into #REVENUEDATA
    select
        REVENUESPLIT.ID,
        0 as AMOUNT,
        REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
        case
            when SALESMETHODTYPECODE = 3 and (SALESORDER.STATUSCODE in (0, 2, 3, 4)) then  -- Group sales and (pending/tentative/confirmed/Finalized)

                255 --'Group Sales Deposit'

            else
                REVENUESPLIT.TYPECODE
        end as REVENUETYPECODE,
        REVENUE.ID as TRANSACTIONID
    from dbo.FINANCIALTRANSACTION as REVENUE
        inner join dbo.FINANCIALTRANSACTIONLINEITEM  on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = REVENUE.ID
        inner join dbo.REVENUESPLIT_EXT as REVENUESPLIT on REVENUESPLIT.ID = FINANCIALTRANSACTIONLINEITEM.ID
        inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
        left join dbo.SALESORDERPAYMENT on REVENUE.ID = SALESORDERPAYMENT.PAYMENTID
        left join dbo.SALESORDER on SALESORDERPAYMENT.SALESORDERID = SALESORDER.ID
    where
        (@STARTDATE is null or REVENUE.DATE >= @STARTDATE)
        and (@ENDDATE is null or REVENUE.DATE <= @ENDDATE)
        and REVENUE.TYPECODE in (0,7)  -- Payment, auction donation

        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null



    --refund splits

    --when the splits return null, this means that the revenue is missing

    --which is only the case for membership renewals since the transaction is

    --deleted

    insert into #REVENUEDATA
    select
        null,
        case when REVENUESPLIT_EXT.TYPECODE = 1 then
            -(LI.TRANSACTIONAMOUNT)
        else
            -((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS)
        end as AMOUNT,
        CREDITPAYMENT.PAYMENTMETHODCODE,
        REVENUESPLIT_EXT.TYPECODE,
        FT.ID
    from
        dbo.FINANCIALTRANSACTION as FT
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.FINANCIALTRANSACTIONID = FT.ID
    inner join
        dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
    inner join
        dbo.CREDITPAYMENT on CREDITPAYMENT.CREDITID = FT.ID
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as SOURCELINEITEM on SOURCELINEITEM.ID = LI.SOURCELINEITEMID
    inner join
        dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = SOURCELINEITEM.ID
    where
        (@STARTDATE is null or cast(FT.DATE as datetime) >= @STARTDATE)
        and (@ENDDATE is null or cast(FT.DATE as datetime) <= @ENDDATE)
        and FT.TYPECODE = 23  -- Refund

        and LI.DELETEDON is null
        and EXT.TYPECODE <> 255;  -- Unearned revenue



    update R
    set R.AMOUNT = REVENUESPLITINCURRENCY.AMOUNTINCURRENCY
    from #REVENUEDATA R
    inner join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as REVENUESPLITINCURRENCY on REVENUESPLITINCURRENCY.ID = R.ID
    where R.AMOUNT >= 0 and REVENUESPLITINCURRENCY.AMOUNTINCURRENCY is not null;


    select
        AVAILABLEREVENUETYPES.REVENUETYPE,
        AVAILABLEPAYMENTMETHODS.PAYMENTMETHOD,
        count(distinct REVENUEDATA.TRANSACTIONID) as NUMBEROFTRANSACTIONS,
        coalesce(sum(REVENUEDATA.AMOUNT), 0) as TOTALREVENUE,
        @ISOCURRENCYCODE ISOCURRENCYCODE,
        @CURRENCYSYMBOL CURRENCYSYMBOL,
        @CURRENCYSYMBOLDISPLAYSETTINGCODE CURRENCYSYMBOLDISPLAYSETTINGCODE,
        @CURRENCYDECIMALDIGITS DECIMALDIGITS
    from #AVAILABLEREVENUETYPES as AVAILABLEREVENUETYPES
    cross join #AVAILABLEPAYMENTMETHODS as AVAILABLEPAYMENTMETHODS
    left join #REVENUEDATA REVENUEDATA on REVENUEDATA.REVENUETYPECODE = AVAILABLEREVENUETYPES.REVENUETYPECODE and REVENUEDATA.PAYMENTMETHODCODE = AVAILABLEPAYMENTMETHODS.PAYMENTMETHODCODE
    where REVENUEDATA.ID is null
        or exists (
            select REPORTPERMISSIONS.HASPERMISSION
            from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUEDATA.ID) as REVENUESITES
            cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, '6e42c3f3-294c-4890-944c-763651b244d1', REVENUESITES.SITEID) as REPORTPERMISSIONS
        )
    group by AVAILABLEREVENUETYPES.REVENUETYPE, AVAILABLEPAYMENTMETHODS.PAYMENTMETHOD
    order by AVAILABLEREVENUETYPES.REVENUETYPE;


    drop table #REVENUEDATA;
    drop table #AVAILABLEREVENUETYPES;
    drop table #AVAILABLEPAYMENTMETHODS;
    drop table #PAYMENTMETHODS_T;
end