USP_REPORT_REVENUEBYPAYMENTMETHOD

create procedure dbo.USP_REPORT_REVENUEBYPAYMENTMETHOD
(
    @APPLICATIONTYPES 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 @CURRENTAPPUSERID uniqueidentifier = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
    declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
    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
    begin
        set @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID;
    end        

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

    -- Add a fake payment method to translate auction donation payment methods from 'None' to 'Auction donation'

    declare @PAYMENTMETHODS_T table    (
        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: */
    declare @AVAILABLEPAYMENTMETHODS table    (
        [PAYMENTMETHODCODE] tinyint,
        [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 application types: */
    declare @AVAILABLEAPPLICATIONTYPES table(
        [APPLICATIONTYPECODE] tinyint,
        [APPLICATIONTYPE] nvarchar(100)
    );

    insert into @AVAILABLEAPPLICATIONTYPES
    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="APPLICATIONCODE"]/bbtable:EnumValues/bbtable:EnumValue'
        ) as ENUMVALUES([ENUMXML])
    where TABLECATALOG.[ID] = @REVENUESPLITTABLEID 
        and ENUMVALUES.[ENUMXML].value('@ID', 'tinyint') in(
            select APPLICATIONTYPES.[APPLICATIONTYPE].value('APPLICATIONTYPE[1]', 'tinyint')
            from @APPLICATIONTYPES.nodes('APPLICATIONTYPES/ITEM') as APPLICATIONTYPES([APPLICATIONTYPE])
        ) 
        or @APPLICATIONTYPES is null;

    /*Change 'Auction donation' to 'Auction purchase' for usability purposes*/
    update @AVAILABLEAPPLICATIONTYPES
    set APPLICATIONTYPE = 'Auction purchase' 
    where APPLICATIONTYPECODE = 12;

    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;

    declare @ISSYSADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
    declare @CHECKSITES bit = 0;
    if exists(select top 1 1 from dbo.SITE)
        set @CHECKSITES = 1;

    if @CHECKSITES = 1 and @ISSYSADMIN = 1
        set @CHECKSITES = 0;

    declare @CHECKMULTICURRENCY bit = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION(N'Multicurrency');
    if @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID
        set @CHECKMULTICURRENCY = 0;

    declare @REVENUEDATA table (
        QUANTITY integer,
        AMOUNT money,
        APPLICATIONCODE tinyint,
        PAYMENTMETHODCODE tinyint
    );

    declare @USERREPORTSITE table(
        SITEID uniqueidentifier
    );
    insert into @USERREPORTSITE
    select 
        SITEID
    from 
        dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,'6e42c3f3-294c-4890-944c-763651b244d1', 21);

    if @CHECKMULTICURRENCY = 1
    begin
        if @CHECKSITES = 1
        begin
            /*    Select out the cartesian product of application 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 */
            insert into @REVENUEDATA
            select
                count(REVENUESPLITINCURRENCY.[ID]) as [QUANTITY],
                sum(REVENUESPLITINCURRENCY.AMOUNTINCURRENCY) as [AMOUNT],
                REVENUESPLITINCURRENCY.[APPLICATIONCODE],
                REVENUEPAYMENTMETHOD.[PAYMENTMETHODCODE]
            from 
                dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as REVENUESPLITINCURRENCY
                inner join dbo.UFN_SITEID_MAPFROM_REVENUESPLITID_BULK() as REVENUESITE on REVENUESITE.ID = REVENUESPLITINCURRENCY.ID
                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = REVENUESPLITINCURRENCY.REVENUEID
                inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUESPLITINCURRENCY.REVENUEID
            where
                (
                    (
                        REVENUESPLITINCURRENCY.[DATE] >= @STARTDATE 
                        and REVENUESPLITINCURRENCY.[DATE] <= @ENDDATE
                    ) 
                    or 
                    (
                        @STARTDATE is null 
                        and @ENDDATE is null
                    )
                ) 
                and REVENUESPLITINCURRENCY.TRANSACTIONTYPECODE in (0,7) -- Payment, auction donation

                and REVENUESPLITINCURRENCY.SPLITDELETEDON is null
                and not 
                (
                    FINANCIALTRANSACTION.BASEAMOUNT = 0 
                    and FINANCIALTRANSACTION.TRANSACTIONAMOUNT > 0
                ) 
                and 
                (
                    exists 
                    (
                        select 1
                        from 
                            @USERREPORTSITE USERREPORTSITE
                        where 
                            USERREPORTSITE.SITEID = REVENUESITE.SITEID
                            or 
                            (
                                USERREPORTSITE.SITEID is null 
                                and REVENUESITE.SITEID is null
                            )
                    )
                )
            group by 
                REVENUESPLITINCURRENCY.APPLICATIONCODE, 
                REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE;
        end
        else
        begin
            insert into @REVENUEDATA
            select
                count(REVENUESPLITINCURRENCY.[ID]) as [QUANTITY],
                sum(REVENUESPLITINCURRENCY.AMOUNTINCURRENCY) as [AMOUNT],
                REVENUESPLITINCURRENCY.[APPLICATIONCODE],
                REVENUEPAYMENTMETHOD.[PAYMENTMETHODCODE]
            from 
                dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as REVENUESPLITINCURRENCY
                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = REVENUESPLITINCURRENCY.REVENUEID
                inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUESPLITINCURRENCY.REVENUEID
            where
                (
                    (
                        REVENUESPLITINCURRENCY.[DATE] >= @STARTDATE 
                        and REVENUESPLITINCURRENCY.[DATE] <= @ENDDATE
                    ) 
                    or 
                    (
                        @STARTDATE is null 
                        and @ENDDATE is null
                    )
                ) 
                and REVENUESPLITINCURRENCY.TRANSACTIONTYPECODE in (0,7) -- Payment, auction donation

                and REVENUESPLITINCURRENCY.SPLITDELETEDON is null
                and not 
                (
                    FINANCIALTRANSACTION.BASEAMOUNT = 0 
                    and FINANCIALTRANSACTION.TRANSACTIONAMOUNT > 0
                ) 
            group by 
                REVENUESPLITINCURRENCY.APPLICATIONCODE, 
                REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE;
        end
    end
    else
    begin
        if @CHECKSITES = 1
        begin
            insert into @REVENUEDATA
            select
                count(LI.ID) as [QUANTITY],
                sum(LI.ORGAMOUNT) as [AMOUNT],
                RSE.[APPLICATIONCODE],
                REVENUEPAYMENTMETHOD.[PAYMENTMETHODCODE]
            from dbo.FINANCIALTRANSACTION FT
            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = FT.ID
            inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = LI.ID
            inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = FT.ID
            inner join dbo.UFN_SITEID_MAPFROM_REVENUESPLITID_BULK() as REVENUESITE on REVENUESITE.ID = LI.ID
            where
                LI.DELETEDON is null and LI.TYPECODE != 1
                and FT.TYPECODE in (0,7)
                and 
                (
                    (
                        FT.CALCULATEDDATE >= @STARTDATE 
                        and FT.CALCULATEDDATE <= @ENDDATE
                    ) 
                    or 
                    (
                        @STARTDATE is null 
                        and @ENDDATE is null
                    )
                )
                and 
                (
                    exists 
                    (
                        select 1
                        from 
                            @USERREPORTSITE USERREPORTSITE
                        where 
                            USERREPORTSITE.SITEID = REVENUESITE.SITEID
                            or 
                            (
                                USERREPORTSITE.SITEID is null 
                                and REVENUESITE.SITEID is null
                            )
                    )
                )
            group by 
                RSE.APPLICATIONCODE
                ,REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE;
        end
        else
        begin
            insert into @REVENUEDATA
            select
                count(LI.ID) as [QUANTITY],
                sum(LI.ORGAMOUNT) as [AMOUNT],
                RSE.[APPLICATIONCODE],
                REVENUEPAYMENTMETHOD.[PAYMENTMETHODCODE]
            from dbo.FINANCIALTRANSACTION FT
            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = FT.ID
            inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = LI.ID
            inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = FT.ID
            where
                LI.DELETEDON is null and LI.TYPECODE != 1
                and FT.TYPECODE in (0,7)
                and 
                (
                    (
                        FT.CALCULATEDDATE >= @STARTDATE 
                        and FT.CALCULATEDDATE <= @ENDDATE
                    ) 
                    or 
                    (
                        @STARTDATE is null 
                        and @ENDDATE is null
                    )
                )
            group by 
                RSE.APPLICATIONCODE
                ,REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE;
        end
    end

    select
        AVAILABLEAPPLICATIONTYPES.[APPLICATIONTYPE],
        AVAILABLEPAYMENTMETHODS.[PAYMENTMETHOD],
        coalesce(REVENUEDATA.[QUANTITY], 0) as [NUMBEROFTRANSACTIONS],
        coalesce(REVENUEDATA.[AMOUNT], 0) as [TOTALREVENUE],
        @ISOCURRENCYCODE [ISOCURRENCYCODE],
        @CURRENCYSYMBOL [CURRENCYSYMBOL],
        @CURRENCYSYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
        @CURRENCYDECIMALDIGITS [DECIMALDIGITS]
    from @AVAILABLEAPPLICATIONTYPES as AVAILABLEAPPLICATIONTYPES
        cross join @AVAILABLEPAYMENTMETHODS as [AVAILABLEPAYMENTMETHODS]
        left outer join @REVENUEDATA [REVENUEDATA] on REVENUEDATA.[APPLICATIONCODE] = AVAILABLEAPPLICATIONTYPES.[APPLICATIONTYPECODE] 
            and REVENUEDATA.[PAYMENTMETHODCODE] = AVAILABLEPAYMENTMETHODS.[PAYMENTMETHODCODE]
    order by 
        AVAILABLEAPPLICATIONTYPES.[APPLICATIONTYPE];
end