USP_REPORT_TOTALREVENUE_PAYMENTMETHOD

create procedure [dbo].[USP_REPORT_TOTALREVENUE_PAYMENTMETHOD] (
    @INCLUDETAXES bit = null,    -- obsolete

    @INCLUDESECURITYDEPOSITS bit = null,
    @INCLUDEUNRECEIVEDREVENUE bit = null,
    @FROMDATE datetime = null,
    @TODATE datetime = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @APPUSERID uniqueidentifier = null,
    @APPUSERQUERYID uniqueidentifier = null,
    @SALESMETHODTYPECODE smallint = null,    --not used anymore

    @SOURCEADVANCESALES bit = null,
    @SOURCEDAILYSALES bit = null,
    @SOURCEGROUPSALES bit = null,
    @SOURCEONLINESALES bit = null,
    @SOURCEBACKOFFICE bit = null,
    @SHOWPAYMENTMETHOD bit = null)
as
    set nocount on;

    if isnull(@SHOWPAYMENTMETHOD, 0) = 0
        return;

    declare @USERISSYSADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

    declare @USERGRANTEDTRANSACTIONPAGE bit = @USERISSYSADMIN,
            @USERGRANTEDORDERPAGE bit = @USERISSYSADMIN,
            @USERGRANTEDREFUNDPAGE bit = @USERISSYSADMIN,
            @USERGRANTEDCONSTITUENTPAGE bit = @USERISSYSADMIN,
            @USERGRANTEDREVENUEAPPLICATIONPAGE bit = @USERISSYSADMIN,
            @USERGRANTEDREGISTRANTPAGE bit = @USERISSYSADMIN,
            @USERGRANTEDMEMBERSHIPPAGE bit = @USERISSYSADMIN;

    if @USERISSYSADMIN <> 1
    begin
        select
            @USERGRANTEDTRANSACTIONPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'D00E6C42-2434-4D85-8A04-2323CA6BB2E7'),
            @USERGRANTEDORDERPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, '9C4D9D19-BB7E-4656-9B78-7EE1930C009A'),
            @USERGRANTEDREFUNDPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'B2130DFA-51F6-4205-87E6-7D132573CB4D'),
            @USERGRANTEDCONSTITUENTPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, '0C836902-A398-47a0-91EB-8B66E434148E'),
            @USERGRANTEDREVENUEAPPLICATIONPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, '32C309D8-E111-4DFE-B777-69206707512F'),
            @USERGRANTEDREGISTRANTPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'D7A6E035-4F35-4784-BD57-B506CEAA8CE5'),
            @USERGRANTEDMEMBERSHIPPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'D56E385D-AF02-464D-AD07-8C2FBE88246D');
    end;

    declare @NONETEXT nchar(13) = N'None received';
    declare @SUBTYPENOTSPECIFIEDTEXT nchar(21) = N'Subtype not specified';
    declare @EXPECTEDORDERREVENUETEXT nchar(28) = N'Expected sales order revenue';
    declare @EXPECTEDPLEDGEREVENUETEXT nchar(23) = N'Expected pledge revenue';

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

    declare @TMP_PAYMENTMETHOD table (
        PAYMENTID uniqueidentifier,
        PAYMENTDATE datetime,
        PAYMENTMETHOD nvarchar(max),
        PAYMENTMETHODCODE tinyint,
        CREDITTYPE nvarchar(max),
        AMOUNT money,
        CONSTITUENTNAME nvarchar(max),
        CONSTITUENTLINK nvarchar(max),
        REFERENCE nvarchar(max),
        REFERENCELINK nvarchar(max)
    );

    declare @TMP_TOTALREVENUE table (
        TransactionID uniqueidentifier,
        SalesOrderId uniqueidentifier,
        TransactionDate datetime,
        Amount money,
        TransactionTypeCode int,
        TransactionType nvarchar(max),
        ApplicationCode int,
        RevenueTypeCode int,
        ConstituentName nvarchar(max),
        ConstituentLink nvarchar(max),
        ProgramName nvarchar(max),
        EventName nvarchar(max),
        TaxName nvarchar(max),
        FeeName nvarchar(max),
        DepartmentName nvarchar(max),
        MembershipName nvarchar(max),
        DesignationName nvarchar(max),
        LocationName nvarchar(max),
        ResourceName nvarchar(max),
        StaffResourceName nvarchar(max),
        SalesMethodTypeCode int,
        CreditItemID uniqueidentifier, -- why is this returned

        TransactionLink nvarchar(max),
        SortConstituentName nvarchar(max),
        PaymentMethod nvarchar(max),
        GroupType nvarchar(max),
        ArrivalDate date,
        ReservationName nvarchar(max),
        ReservationLink nvarchar(max),
        Reference nvarchar(max),
        ReferenceLink nvarchar(max),
        PledgeDate datetime
    );

    insert @TMP_TOTALREVENUE exec dbo.USP_REPORT_TOTALREVENUE 1, @INCLUDESECURITYDEPOSITS, @INCLUDEUNRECEIVEDREVENUE, @FROMDATE, @TODATE, @CURRENTAPPUSERID, @APPUSERID, @APPUSERQUERYID, @SALESMETHODTYPECODE, @SOURCEADVANCESALES, @SOURCEDAILYSALES, @SOURCEGROUPSALES, @SOURCEONLINESALES, @SOURCEBACKOFFICE;

    -- Refunds

    insert into @TMP_PAYMENTMETHOD
    select PAYMENTID, PAYMENTDATE, PAYMENTMETHOD, PAYMENTMETHODCODE, CREDITTYPE, AMOUNT, CONSTITUENTNAME, CONSTITUENTLINK, dbo.UDA_BUILDLIST(REFERENCE), REFERENCELINK
    from (
        select distinct
            CREDITPAYMENT.CREDITID as [PAYMENTID],
            cast(CREDITPAYMENT.CREDITPAYMENTDATEWITHTIMEOFFSET as datetime) PAYMENTDATE,
            CREDITPAYMENT.PAYMENTMETHOD,
            CREDITPAYMENT.PAYMENTMETHODCODE,
            case CREDITPAYMENT.PAYMENTMETHODCODE
                when 2 then isnull(CREDITTYPECODE.DESCRIPTION, @SUBTYPENOTSPECIFIEDTEXT)
                when 10 then isnull(OTHERPAYMENTMETHODCODE.DESCRIPTION, @SUBTYPENOTSPECIFIEDTEXT)
            end CREDITTYPE,
            0 - CREDITPAYMENT.AMOUNT AMOUNT,
            TMP_TOTALREVENUE.ConstituentName,
            TMP_TOTALREVENUE.ConstituentLink,
            TMP_TOTALREVENUE.Reference,
            TMP_TOTALREVENUE.ReferenceLink,
            REVENUEPAYMENTMETHOD.ID METHODID        -- prevent "distinct" from removing two credit payments with the same method and value

        from @TMP_TOTALREVENUE as TMP_TOTALREVENUE
        inner join dbo.CREDITPAYMENT on CREDITPAYMENT.CREDITID = TMP_TOTALREVENUE.TransactionID
        left join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = CREDITPAYMENT.REVENUESPLITID
        left join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = isnull(CREDITPAYMENT.REVENUEID, LI.FINANCIALTRANSACTIONID)
        left join dbo.CREDITCARDPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
        left join dbo.CREDITTYPECODE on CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID = CREDITTYPECODE.ID
        left join dbo.OTHERPAYMENTMETHODCODE on OTHERPAYMENTMETHODCODE.ID = CREDITPAYMENT.OTHERPAYMENTMETHODCODEID
        where TMP_TOTALREVENUE.TransactionTypeCode = 23
    ) REFUNDS
    group by PAYMENTID, PAYMENTDATE, PAYMENTMETHOD, PAYMENTMETHODCODE, CREDITTYPE, AMOUNT, CONSTITUENTNAME, CONSTITUENTLINK, REFERENCELINK, METHODID;

    -- Sales orders

    insert @TMP_PAYMENTMETHOD
    select
        SALESORDERPAYMENT.PAYMENTID,
        cast(SALESORDERPAYMENT.PAYMENTDATEWITHTIMEOFFSET as datetime),
        isnull(REVENUEPAYMENTMETHOD.PAYMENTMETHOD, @NONETEXT),
        isnull(REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,9),
        case isnull(REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,9)
            when 2 then isnull(CREDITTYPECODE.DESCRIPTION, @SUBTYPENOTSPECIFIEDTEXT)
            when 5 then isnull(PROPERTYSUBTYPECODE.DESCRIPTION, @SUBTYPENOTSPECIFIEDTEXT)
            when 6 then isnull(GIFTINKINDSUBTYPECODE.DESCRIPTION, @SUBTYPENOTSPECIFIEDTEXT)
            when 10 then isnull(OTHERPAYMENTMETHODCODE.DESCRIPTION, @SUBTYPENOTSPECIFIEDTEXT)
            when 9 then @EXPECTEDORDERREVENUETEXT
            else null
        end CREDITTYPE,
        isnull(FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT, SALESORDER.AMOUNT) as AMOUNT,
        CONSTITUENTINFO.ConstituentName,
        CONSTITUENTINFO.ConstituentLink,
        'Order-'+cast(SALESORDER.SEQUENCEID as nvarchar(36)) REFERENCE,
        case when @USERGRANTEDORDERPAGE = 1 then 'http://www.blackbaud.com/SALESORDERID?SALESORDERID=' + CONVERT(nvarchar(36), SALESORDERPAYMENT.SALESORDERID) else null end REFERENCELINK
    from dbo.SALESORDER
    left join dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.SALESORDERID = SALESORDER.ID
    left join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = SALESORDERPAYMENT.PAYMENTID
    left join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
    left join dbo.REVENUEPAYMENTMETHOD on SALESORDERPAYMENT.PAYMENTID = REVENUEPAYMENTMETHOD.REVENUEID
    left join dbo.CREDITCARDPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
    left join dbo.CREDITTYPECODE on CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID = CREDITTYPECODE.ID
    left join dbo.PROPERTYDETAIL on REVENUEPAYMENTMETHOD.ID = PROPERTYDETAIL.ID
    left join dbo.PROPERTYSUBTYPECODE on PROPERTYDETAIL.PROPERTYSUBTYPECODEID = PROPERTYSUBTYPECODE.ID
    left join dbo.GIFTINKINDPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = GIFTINKINDPAYMENTMETHODDETAIL.ID
    left join dbo.GIFTINKINDSUBTYPECODE on GIFTINKINDPAYMENTMETHODDETAIL.GIFTINKINDSUBTYPECODEID = GIFTINKINDSUBTYPECODE.ID
    left join dbo.OTHERPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = OTHERPAYMENTMETHODDETAIL.ID
    left join dbo.OTHERPAYMENTMETHODCODE on OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID = OTHERPAYMENTMETHODCODE.ID
    outer apply (
        select distinct ConstituentName, ConstituentLink
        from @TMP_TOTALREVENUE
        where SalesOrderId = SALESORDER.ID 
        and TRANSACTIONTYPECODE <> 23 -- Exclude refunds

    ) CONSTITUENTINFO
    where
        SALESORDER.ID in (
            select distinct SALESORDERID
            from @TMP_TOTALREVENUE
            where TransactionTypeCode = 5
                and SalesMethodTypeCode <> 3
        )
        and (REVENUESPLIT_EXT.APPLICATIONCODE <> 10 /*order*/ or FINANCIALTRANSACTIONLINEITEM.VISIBLE = 1)
        and FINANCIALTRANSACTIONLINEITEM.REVERSEDLINEITEMID is null
        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null;

    -- Pledge records

    insert @TMP_PAYMENTMETHOD
    select
        TMP_TOTALREVENUE.TransactionID PAYMENTID,
        TMP_TOTALREVENUE.TransactionDate PAYMENTDATE,
        @NONETEXT PAYMENTMETHOD,
        9 PAYMENTMETHODCODE,
        @EXPECTEDPLEDGEREVENUETEXT as CREDITTYPE,
        TMP_TOTALREVENUE.Amount AMOUNT,
        TMP_TOTALREVENUE.ConstituentName,
        TMP_TOTALREVENUE.ConstituentLink,
        TMP_TOTALREVENUE.Reference,
        TMP_TOTALREVENUE.ReferenceLink
    from @TMP_TOTALREVENUE as TMP_TOTALREVENUE
    where TMP_TOTALREVENUE.TransactionTypeCode = 1;

    -- Revenue records

    insert @TMP_PAYMENTMETHOD
    select
        TMP_TOTALREVENUE.TransactionID as PAYMENTID,
        TMP_TOTALREVENUE.TransactionDate as PAYMENTDATE,
        isnull(REVENUEPAYMENTMETHOD.PAYMENTMETHOD, @NONETEXT),
        isnull(REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,9),
        case isnull(REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,9)
            when 2 then isnull(CREDITTYPECODE.DESCRIPTION, @SUBTYPENOTSPECIFIEDTEXT)
            when 5 then isnull(PROPERTYSUBTYPECODE.DESCRIPTION, @SUBTYPENOTSPECIFIEDTEXT)
            when 6 then isnull(GIFTINKINDSUBTYPECODE.DESCRIPTION, @SUBTYPENOTSPECIFIEDTEXT)
            when 10 then isnull(OTHERPAYMENTMETHODCODE.DESCRIPTION, @SUBTYPENOTSPECIFIEDTEXT)
            else null
        end CREDITTYPE,
        TMP_TOTALREVENUE.Amount AMOUNT,
        TMP_TOTALREVENUE.ConstituentName,
        TMP_TOTALREVENUE.ConstituentLink,
        TMP_TOTALREVENUE.Reference,
        TMP_TOTALREVENUE.ReferenceLink
    from @TMP_TOTALREVENUE as TMP_TOTALREVENUE
    left join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = TMP_TOTALREVENUE.TransactionID
    left join dbo.CREDITCARDPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
    left join dbo.CREDITTYPECODE on CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID = CREDITTYPECODE.ID
    left join dbo.PROPERTYDETAIL on REVENUEPAYMENTMETHOD.ID = PROPERTYDETAIL.ID
    left join dbo.PROPERTYSUBTYPECODE on PROPERTYDETAIL.PROPERTYSUBTYPECODEID = PROPERTYSUBTYPECODE.ID
    left join dbo.GIFTINKINDPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = GIFTINKINDPAYMENTMETHODDETAIL.ID
    left join dbo.GIFTINKINDSUBTYPECODE on GIFTINKINDPAYMENTMETHODDETAIL.GIFTINKINDSUBTYPECODEID = GIFTINKINDSUBTYPECODE.ID
    left join dbo.OTHERPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = OTHERPAYMENTMETHODDETAIL.ID
    left join dbo.OTHERPAYMENTMETHODCODE on OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID = OTHERPAYMENTMETHODCODE.ID
    where TMP_TOTALREVENUE.TransactionTypeCode not in (1,5,23) -- Pledge, Order, Refund

        and isnull(TMP_TOTALREVENUE.SALESMETHODTYPECODE,0) != 3

    -- Group Sales (not including refunds)

    insert into @TMP_PAYMENTMETHOD
    select v1.TRANSACTIONID,
        cast(FINANCIALTRANSACTION.DATE as datetime),
        isnull(REVENUEPAYMENTMETHOD.PAYMENTMETHOD, @NONETEXT),
        isnull(REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,9),
        case REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE
            when 2 then isnull(CREDITTYPECODE.DESCRIPTION, @SUBTYPENOTSPECIFIEDTEXT)
            when 5 then isnull(PROPERTYSUBTYPECODE.DESCRIPTION, @SUBTYPENOTSPECIFIEDTEXT)
            when 6 then isnull(GIFTINKINDSUBTYPECODE.DESCRIPTION, @SUBTYPENOTSPECIFIEDTEXT)
            when 10 then isnull(OTHERPAYMENTMETHODCODE.DESCRIPTION, @SUBTYPENOTSPECIFIEDTEXT)
            else null
        end,
        isnull(SALESORDERPAYMENT.AMOUNT, FINANCIALTRANSACTION.BASEAMOUNT) as Amount,
        v1.ConstituentName,
        v1.ConstituentLink,
        v1.REFERENCE,
        v1.REFERENCELINK
    from
    (
        select distinct TRANSACTIONID, REFERENCE, REFERENCELINK, ConstituentName, ConstituentLink
        from @TMP_TOTALREVENUE
        where SALESMETHODTYPECODE = 3
            and TRANSACTIONTYPECODE <> 23 -- Exclude refunds

    ) v1
    inner join dbo.FINANCIALTRANSACTION on v1.TRANSACTIONID = FINANCIALTRANSACTION.ID
    left join dbo.SALESORDERPAYMENT on v1.TRANSACTIONID = SALESORDERPAYMENT.PAYMENTID
    left join dbo.REVENUEPAYMENTMETHOD on v1.TRANSACTIONID = REVENUEPAYMENTMETHOD.REVENUEID
    left join dbo.CREDITCARDPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = CREDITCARDPAYMENTMETHODDETAIL.ID
    left join dbo.CREDITTYPECODE on CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID = CREDITTYPECODE.ID
    left join dbo.PROPERTYDETAIL on REVENUEPAYMENTMETHOD.ID = PROPERTYDETAIL.ID
    left join dbo.PROPERTYSUBTYPECODE on PROPERTYDETAIL.PROPERTYSUBTYPECODEID = PROPERTYSUBTYPECODE.ID
    left join dbo.GIFTINKINDPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = GIFTINKINDPAYMENTMETHODDETAIL.ID
    left join dbo.GIFTINKINDSUBTYPECODE on GIFTINKINDPAYMENTMETHODDETAIL.GIFTINKINDSUBTYPECODEID = GIFTINKINDSUBTYPECODE.ID
    left join dbo.OTHERPAYMENTMETHODDETAIL on REVENUEPAYMENTMETHOD.ID = OTHERPAYMENTMETHODDETAIL.ID
    left join dbo.OTHERPAYMENTMETHODCODE on OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID = OTHERPAYMENTMETHODCODE.ID;

    with REFERENCE_CTE as (
        select distinct
            PAYMENTMETHODS.PAYMENTID,
            PAYMENTMETHODS.CONSTITUENTNAME,
            PAYMENTMETHODS.CONSTITUENTLINK,
            APPLICATIONS.APPLICATIONLIST,
            case when APPLICATIONS.APPCOUNT = 1 then PAYMENTMETHODS.REFERENCELINK else null end REFERENCELINK
        from @TMP_PAYMENTMETHOD PAYMENTMETHODS
        outer apply (
            select
                dbo.UDA_BUILDLIST(distinct PAYMENTMETHODS2.REFERENCE) APPLICATIONLIST,
                count(distinct isnull(PAYMENTMETHODS2.REFERENCELINK, N'')) APPCOUNT    --The reference link can be null, if user is not having permissions to view reference link. In that case, count will not return exact count and hence query will return wrong output. So make sure null isn't used.

            from @TMP_PAYMENTMETHOD PAYMENTMETHODS2
            where PAYMENTMETHODS2.PAYMENTID = PAYMENTMETHODS.PAYMENTID
            group by PAYMENTID
        ) APPLICATIONS
    )
    select distinct
        PAYMENTS.PAYMENTID,
        PAYMENTS.PAYMENTDATE,
        PAYMENTMETHODCODE,
        PAYMENTMETHOD,
        CREDITTYPE PAYMENTSUBMETHOD,
        APPLICATIONS.CONSTITUENTNAME,
        APPLICATIONS.CONSTITUENTLINK,
        sum(AMOUNT) AMOUNT,
        APPLICATIONS.APPLICATIONLIST REFERENCE,
        APPLICATIONS.REFERENCELINK,
        case
            when sum(AMOUNT) < 0 and @USERGRANTEDREFUNDPAGE = 1 then 'http://www.blackbaud.com/CREDITID?CREDITID=' + CONVERT(nvarchar(36), PAYMENTS.PAYMENTID)
            when sum(AMOUNT) > 0 and @USERGRANTEDTRANSACTIONPAGE = 1 then 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36), PAYMENTS.PAYMENTID)
            else null
        end PAYMENTLINK
    from @TMP_PAYMENTMETHOD PAYMENTS
    left join REFERENCE_CTE APPLICATIONS on APPLICATIONS.PAYMENTID = PAYMENTS.PAYMENTID
    group by
        PAYMENTS.PAYMENTID, PAYMENTS.PAYMENTDATE, PAYMENTS.PAYMENTMETHOD, PAYMENTS.PAYMENTMETHODCODE, PAYMENTS.CREDITTYPE, APPLICATIONS.CONSTITUENTNAME, APPLICATIONS.CONSTITUENTLINK, APPLICATIONS.APPLICATIONLIST, APPLICATIONS.REFERENCELINK
    order by
        PAYMENTMETHOD, CREDITTYPE;


    return 0;