USP_DATALIST_PAYMENT_BYPAYMENTMETHOD

CREATE procedure dbo.USP_DATALIST_PAYMENT_BYPAYMENTMETHOD
(
    @DATETYPE tinyint = null
    ,@FROMDATE datetime = null
    ,@TODATE datetime = null
    ,@CURRENTAPPUSERID uniqueidentifier = null
    ,@METHOD1NAME nvarchar(25) = null output
    ,@METHOD2NAME nvarchar(25) = null output
    ,@METHOD3NAME nvarchar(25) = null output
    ,@METHOD4NAME nvarchar(25) = null output
    ,@METHOD5NAME nvarchar(25) = null output
)
as
begin
    declare @INSERTSTARTDATE bit = 0;
    declare @INSERTENDDATE bit = 0;

    if @FROMDATE is not null and @TODATE is not null and cast(@FROMDATE as date) = cast(@TODATE as date)
    begin
        set @FROMDATE = DATEADD(day, -1, @FROMDATE)
        set @TODATE = DATEADD(day, 1, @TODATE)
    end
    if @FROMDATE is not null
    begin
        set @FROMDATE = dbo.UFN_DATE_GETEARLIESTTIME(@FROMDATE);
        set @INSERTSTARTDATE = 1
    end
    if @TODATE is not null
    begin
        set @TODATE = dbo.UFN_DATE_GETLATESTTIME(@TODATE);
        set @INSERTENDDATE = 1
    end

    declare @Data TABLE (
        [DATE] date
        ,METHOD1AMOUNT money
        ,METHOD2AMOUNT money
        ,METHOD3AMOUNT money
        ,METHOD4AMOUNT money
        ,METHOD5AMOUNT money)

    declare @tmp_paymentmethod table 
        (PAYMENTID uniqueidentifier,
         PAYMENTMETHOD varchar(max),
         PAYMENTMETHODCODE tinyint,
         CREDITTYPE varchar(max),
         AMOUNT money,
         [DATE] date)

    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 0, 0, 0, @FROMDATE, @TODATE, @CURRENTAPPUSERID, null, null, null, 1, 1, 1, 1, 1;

    -- REFUNDS

    insert @tmp_paymentmethod 
    select distinct CP.ID [PAYMENTID]
        ,ISNULL(CP.PAYMENTMETHOD, 'None')
        ,isnull(CP.PAYMENTMETHODCODE, 9)
        ,case CP.PAYMENTMETHODCODE  
            when 2 then '<Card type not specified>'
            when 10 then ISNULL(OP.DESCRIPTION, '<Other payment subtype not specified>') END
        ,CP.AMOUNT * total.AMOUNT / CPTOTAL.AMOUNT
        ,t.TransactionDate
    from @tmp_TotalRevenue t
    inner join dbo.CREDITPAYMENT CP on CP.CREDITID = t.TransactionId
    left join dbo.OTHERPAYMENTMETHODCODE OP on OP.ID = CP.OTHERPAYMENTMETHODCODEID
    inner join (select TransactionId as ID, SUM(Amount) [AMOUNT] from @tmp_TotalRevenue group by TransactionId) total on total.ID = CP.CREDITID
    inner join (select SUM(AMOUNT) as [AMOUNT], CREDITID from dbo.CREDITPAYMENT group by CREDITID) CPTOTAL on CPTOTAL.CREDITID = cp.CREDITID
    where t.TransactionTypeCode = 23 and t.RevenueTypeCode <> 7

    -- SALES ORDERS 

    insert @tmp_paymentmethod 
    select ISNULL(pm.id, S.ID) PAYMENTID, isnull(pm.PAYMENTMETHOD,'None'), isnull(pm.PAYMENTMETHODCODE,9), 
    case pm.PAYMENTMETHODCODE  
    when 2 then 
        ISNULL((select ct.DESCRIPTION from CREDITTYPECODE ct 
                inner join dbo.CREDITCARDPAYMENTMETHODDETAIL pmd on pmd.CREDITTYPECODEID = ct.ID 
                where pm.ID = pmd.ID ),'<Card type not specified>')
    when 5 then
        ISNULL((select DESCRIPTION from PROPERTYSUBTYPECODE sc 
                inner join PROPERTYDETAIL pd on sc.ID = pd.PROPERTYSUBTYPECODEID 
                where pd.ID = pm.ID), '<Property subtype not specified>')
    when 6 then
        ISNULL((select DESCRIPTION from GIFTINKINDSUBTYPECODE gc
                inner join GIFTINKINDPAYMENTMETHODDETAIL gpm on gc.ID = gpm.GIFTINKINDSUBTYPECODEID 
                where gpm.ID = pm.id), '<Gift-in-kind subtype not specified>')
    when 10 then 
        ISNULL((select DESCRIPTION from OTHERPAYMENTMETHODCODE opm 
                inner join OTHERPAYMENTMETHODDETAIL opd on opm.ID = opd.OTHERPAYMENTMETHODCODEID 
                where opd.ID = pm.ID), '<Other payment subtype not specified>')
    else null end CREDITTYPE, 
    ISNULL(ftli.BASEAMOUNT, s.AMOUNT) AMOUNT
    ,S.TRANSACTIONDATE
    from SALESORDER s
    left join SALESORDERPAYMENT so on so.SALESORDERID= s.ID 
    left join FINANCIALTRANSACTIONLINEITEM ftli on ftli.FINANCIALTRANSACTIONID = so.PAYMENTID 
    left join REVENUEPAYMENTMETHOD pm on so.PAYMENTID = pm.REVENUEID 
    where s.ID in (Select TransactionId from @tmp_TotalRevenue where TransactionTypeCode = 5)

    -- PLEDGE RECORDS

    insert @tmp_paymentmethod 
    select pm.id PAYMENTID, isnull(pm.PAYMENTMETHOD,'None'), isnull(pm.PAYMENTMETHODCODE,9), 
    case pm.PAYMENTMETHODCODE  
    when 2 then 
        ISNULL((select ct.DESCRIPTION from dbo.CREDITTYPECODE ct 
                        inner join dbo.CREDITCARD cc on ct.ID = cc.CREDITTYPECODEID 
                        inner join dbo.REVENUESCHEDULE rs on rs.CREDITCARDID = cc.ID 
                        where rs.ID = pm.revenueid),'<Card type not specified>')    
    when 5 then
        ISNULL((select DESCRIPTION from PROPERTYSUBTYPECODE sc 
                inner join PROPERTYDETAIL pd on sc.ID = pd.PROPERTYSUBTYPECODEID 
                where pd.ID = pm.ID), '<Property subtype not specified>')
    when 6 then
        ISNULL((select DESCRIPTION from GIFTINKINDSUBTYPECODE gc
                inner join GIFTINKINDPAYMENTMETHODDETAIL gpm on gc.ID = gpm.GIFTINKINDSUBTYPECODEID 
                where gpm.ID = pm.id), '<Gift-in-kind subtype not specified>')
    when 10 then 
        ISNULL((select DESCRIPTION from OTHERPAYMENTMETHODCODE opm 
                inner join OTHERPAYMENTMETHODDETAIL opd on opm.ID = opd.OTHERPAYMENTMETHODCODEID 
                where opd.ID = pm.ID), '<Other payment subtype not specified>')
    else null end CREDITTYPE, 
    t.Amount
    ,t.TransactionDate
    from @tmp_TotalRevenue t
    left join REVENUEPAYMENTMETHOD pm on pm.REVENUEID = t.TransactionId
    where TransactionTypeCode = 1 -- Pledge


    -- REVENUE RECORDS

    insert @tmp_paymentmethod 
    select pm.id PAYMENTID, isnull(pm.PAYMENTMETHOD,'None'), isnull(pm.PAYMENTMETHODCODE,9), 
    case pm.PAYMENTMETHODCODE  
    when 2 then 
        ISNULL((select ct.DESCRIPTION from CREDITTYPECODE ct 
                    inner join dbo.CREDITCARDPAYMENTMETHODDETAIL pmd on pmd.CREDITTYPECODEID = ct.ID 
                    where pm.ID = pmd.ID ),'<Card type not specified>')    
    when 5 then
        ISNULL((select DESCRIPTION from PROPERTYSUBTYPECODE sc 
                inner join PROPERTYDETAIL pd on sc.ID = pd.PROPERTYSUBTYPECODEID 
                where pd.ID = pm.ID), '<Property subtype not specified>')
    when 6 then
        ISNULL((select DESCRIPTION from GIFTINKINDSUBTYPECODE gc
                inner join GIFTINKINDPAYMENTMETHODDETAIL gpm on gc.ID = gpm.GIFTINKINDSUBTYPECODEID 
                where gpm.ID = pm.id), '<Gift-in-kind subtype not specified>')
    when 10 then 
        ISNULL((select DESCRIPTION from OTHERPAYMENTMETHODCODE opm 
                inner join OTHERPAYMENTMETHODDETAIL opd on opm.ID = opd.OTHERPAYMENTMETHODCODEID 
                where opd.ID = pm.ID), '<Other payment subtype not specified>')
    else null end CREDITTYPE, 
    t.Amount AMOUNT
    ,t.TransactionDate
    from @tmp_TotalRevenue t
    left join REVENUEPAYMENTMETHOD pm on pm.REVENUEID = t.TransactionId
    where TransactionTypeCode not in (23,1,5)

    declare @topAmounts table (PAYMENTMETHODCODE int)
    insert into @topAmounts
    select top 4 PAYMENTMETHODCODE
    from @tmp_paymentmethod 
    group by PAYMENTMETHODCODE order by SUM(AMOUNT) desc;

    declare @temp table(ID int, [DATE] date, AMOUNT money, PAYMENTMETHOD nvarchar(25))
    insert into @temp
    select DENSE_RANK() over (order by ISNULL(convert(integer, TOPAMOUNTS.PAYMENTMETHODCODE), 999999)) [PAYMENTMETHODCODE]
        ,[DATE]
        ,SUM(AMOUNT) [AMOUNT]
        ,case when TOPAMOUNTS.PAYMENTMETHODCODE IS null then 'All others' else PM.PAYMENTMETHOD end PAYMENTMETHOD
    from @tmp_paymentmethod PM
    left join (select PAYMENTMETHODCODE from @topAmounts) TOPAMOUNTS on TOPAMOUNTS.PAYMENTMETHODCODE = PM.PAYMENTMETHODCODE
    group by ISNULL(convert(integer, TOPAMOUNTS.PAYMENTMETHODCODE), 999999), [DATE], case when TOPAMOUNTS.PAYMENTMETHODCODE IS null then 'All others' else PM.PAYMENTMETHOD end;

    insert into @Data
    select  pvt.[DATE]
        , pvt.[1] [METHOD1AMOUNT]
        , pvt.[2] [METHOD2AMOUNT]
        , pvt.[3] [METHOD3AMOUNT]
        , pvt.[4] [METHOD4AMOUNT]
        , pvt.[5] [METHOD5AMOUNT]
    from (
        select ID, [DATE], AMOUNT from @temp) T
    pivot (
        SUM(AMOUNT) for ID in ([1], [2], [3], [4], [5])
    ) pvt

    select top 1 @METHOD1NAME = T.PAYMENTMETHOD from @temp T where T.ID = 1
    select top 1 @METHOD2NAME = T.PAYMENTMETHOD from @temp T where T.ID = 2
    select top 1 @METHOD3NAME = T.PAYMENTMETHOD from @temp T where T.ID = 3
    select top 1 @METHOD4NAME = T.PAYMENTMETHOD from @temp T where T.ID = 4
    select top 1 @METHOD5NAME = T.PAYMENTMETHOD from @temp T where T.ID = 5

    update @Data set
        METHOD1AMOUNT = case when @METHOD1NAME is not null then isnull(METHOD1AMOUNT, 0) end
        ,METHOD2AMOUNT = case when @METHOD2NAME is not null then isnull(METHOD2AMOUNT, 0) end
        ,METHOD3AMOUNT = case when @METHOD3NAME is not null then isnull(METHOD3AMOUNT, 0) end
        ,METHOD4AMOUNT = case when @METHOD4NAME is not null then isnull(METHOD4AMOUNT, 0) end
        ,METHOD5AMOUNT = case when @METHOD5NAME is not null then isnull(METHOD5AMOUNT, 0) end;

    if @INSERTENDDATE = 1
        if not exists(select 1 from @Data where [DATE] = convert(varchar, @TODATE, 111))
            insert into @Data([DATE]
                , METHOD1AMOUNT
                , METHOD2AMOUNT
                , METHOD3AMOUNT
                , METHOD4AMOUNT
                , METHOD5AMOUNT)
            values (convert(varchar, @TODATE, 111)
                ,case when @METHOD1NAME is not null then 0 else null end
                ,case when @METHOD2NAME is not null then 0 else null end
                ,case when @METHOD3NAME is not null then 0 else null end
                ,case when @METHOD4NAME is not null then 0 else null end
                ,case when @METHOD5NAME is not null then 0 else null end);
    if @INSERTSTARTDATE = 1
        if not exists(select 1 from @Data where [DATE] = @FROMDATE)
            insert into @Data([DATE]
                , METHOD1AMOUNT
                , METHOD2AMOUNT
                , METHOD3AMOUNT
                , METHOD4AMOUNT
                , METHOD5AMOUNT)
            values (@FROMDATE
                ,case when @METHOD1NAME is not null then 0 else null end
                ,case when @METHOD2NAME is not null then 0 else null end
                ,case when @METHOD3NAME is not null then 0 else null end
                ,case when @METHOD4NAME is not null then 0 else null end
                ,case when @METHOD5NAME is not null then 0 else null end);


    if @INSERTSTARTDATE = 1 and @INSERTENDDATE = 1
    begin
        with DateRange([DATE]) as
        (
            select @FROMDATE [DATE]
            union all
            select DATEADD(day, 1, [DATE]) [DATE]
            from DateRange
            where [DATE] < convert(varchar, @TODATE, 111)
        )
        insert into @Data([DATE]
                , METHOD1AMOUNT
                , METHOD2AMOUNT
                , METHOD3AMOUNT
                , METHOD4AMOUNT
                , METHOD5AMOUNT)
            select T.[DATE]
                ,case when @METHOD1NAME is not null then 0 else null end
                ,case when @METHOD2NAME is not null then 0 else null end
                ,case when @METHOD3NAME is not null then 0 else null end
                ,case when @METHOD4NAME is not null then 0 else null end
                ,case when @METHOD5NAME is not null then 0 else null end
            from DateRange T
            where T.[DATE] not in (select [DATE] from @Data)
            OPTION (MAXRECURSION 7000);
    end

    select * from @Data
    order by [DATE] asc;
end