USP_DATAFORMTEMPLATE_VIEW_CREDITDONATIONVIEW

create procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CREDITDONATIONVIEW
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @ITEMS xml = null output,
    @TOTAL money = null output,
    @PAYMENTS xml = null output,
    @OTHERPAYMENTMETHODS xml = null output,
    @TRANSACTIONID uniqueidentifier = null output,
    @TIMESTAMP bigint = null output,
    @CONSTITUENTID uniqueidentifier = null output,
    @CONSTITUENTNAME nvarchar(154) = null output
)
as
    set nocount on;

    set @DATALOADED = 0;

    set @TRANSACTIONID = @ID;

    select
        @OTHERPAYMENTMETHODS =
            (select 
                [ID], 
            [DESCRIPTION] 
            from dbo.[OTHERPAYMENTMETHODCODE]
    for xml raw ('ITEM'), type, elements, root('OTHERPAYMENTMETHODS'), BINARY BASE64)

    select
        @TOTAL = sum(coalesce(REVENUESPLIT.AMOUNT, 0))
    from
        dbo.REVENUESPLIT
    where
        REVENUESPLIT.ID = @ID;

    declare @TOTALREFUND money;

    select
        @TOTALREFUND = sum(coalesce([CREDITITEM].[TOTAL], 0))
    from 
        dbo.[CREDITITEM]
    where 
        [CREDITITEM].[REVENUESPLITID] = @ID

    set @TOTAL = @TOTAL - coalesce(@TOTALREFUND, 0)

    select
        @ITEMS = 
            (select 
                null as [SALESORDERITEMID], 
                0 as [INCLUDE],
                0 as [QUANTITYREFUNDING],
                1 as [QUANTITY],
                (select NAME from dbo.DESIGNATION where ID = [REVENUESPLIT].[DESIGNATIONID]) as [DESCRIPTION],
                @TOTAL as [PRICE],
                0 as [FEES],
                @TOTAL as [TOTAL],
                0 as [REFUNDINGTOTAL],
                0 as [DISCOUNTS],
                0 as [DISCOUNTED],
                2 as [TYPECODE],
                null as [PROGRAMID],
                0 as [GROUPTYPECODE],
                null as [GROUPID]
            from dbo.[REVENUE]
            inner join dbo.[REVENUESPLIT] on [REVENUE].[ID] = [REVENUESPLIT].[REVENUEID]
            where [REVENUESPLIT].[ID] = @ID
        for xml raw ('ITEM'), type, elements, root('ITEMS'), BINARY BASE64);

        with PAYMENTS_CTE as (

        --payments made on an order
        select 
                0 as INCLUDE,
                case
                    when [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 10 then
                        dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION([OTHERPAYMENTMETHODDETAIL].[OTHERPAYMENTMETHODCODEID])
                    else
                        [REVENUEPAYMENTMETHOD].[PAYMENTMETHOD] + 
                        coalesce(', ' + dbo.UFN_CREDITTYPECODE_GETDESCRIPTION([CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID]) + ' #' + [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER], '') +
                        coalesce(' #' + [CHECKPAYMENTMETHODDETAIL].[CHECKNUMBER], '')
                end [DESCRIPTION],
                null as [DISPLAYSOURCE],
                [SALESORDER].[SEQUENCEID] as [SOURCENUMBER],
                0 as [SOURCETYPE],
                case
                    when [SALESORDERPAYMENT].[AMOUNT] < [SALESORDERITEM].[TOTAL] then
                        [SALESORDERPAYMENT].[AMOUNT]
                    else    
                        [SALESORDERITEM].[TOTAL]
                end [REFUNDAMOUNT],
                [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] as [PAYMENTTYPECODE],
                [REVENUE].[ID] as [REVENUEID],
                null as [REVENUESPLITID],
                [CREDITCARDPAYMENTMETHODDETAIL].[TRANSACTIONID],
                sum(coalesce([CREDITITEM].[TOTAL], 0)) as [REFUNDS],
                [SALESORDERITEM].[ID] as [PAYMENTGROUPID],
                [SALESORDERITEM].[PRICE] as [PAYMENTGROUPMAXAMOUNT]
            from dbo.[SALESORDERITEMEVENTREGISTRATION]
            inner join dbo.[SALESORDERITEM] on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
            inner join dbo.[SALESORDER] on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
            inner join dbo.[SALESORDERPAYMENT] on [SALESORDERITEM].[SALESORDERID] = [SALESORDERPAYMENT].[SALESORDERID]
            inner join dbo.[REVENUE] on [SALESORDERPAYMENT].[PAYMENTID] = [REVENUE].[ID]
            inner join dbo.[REVENUESPLIT] on [REVENUE].[ID] = [REVENUESPLIT].[REVENUEID]
            inner join dbo.[REVENUEPAYMENTMETHOD] on [REVENUE].[ID] = [REVENUEPAYMENTMETHOD].[REVENUEID]
            left join dbo.[CREDITCARDPAYMENTMETHODDETAIL] on [CREDITCARDPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
            left join dbo.[OTHERPAYMENTMETHODDETAIL] on [OTHERPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
            left join dbo.[CHECKPAYMENTMETHODDETAIL] on [CHECKPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
            left join dbo.[CREDITITEM] on [CREDITITEM].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
            where [REVENUESPLIT].[ID] = @ID
            group by 
                [REVENUE].[ID],
                [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE],
                [OTHERPAYMENTMETHODDETAIL].[OTHERPAYMENTMETHODCODEID],
                [REVENUEPAYMENTMETHOD].[PAYMENTMETHOD],
                [CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID],
                [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER],
                [CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID],
                [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER],
                [CHECKPAYMENTMETHODDETAIL].[CHECKNUMBER],
                [SALESORDER].[SEQUENCEID],
                [SALESORDERPAYMENT].[AMOUNT],
                [SALESORDERITEM].[TOTAL],
                [CREDITCARDPAYMENTMETHODDETAIL].[TRANSACTIONID],
                [SALESORDERITEM].[ID],
                [SALESORDERITEM].[PRICE]

            union all
            --payments made outside an order
            select 
                0 as INCLUDE,
                case
                    when [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 10 then
                        dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION([OTHERPAYMENTMETHODDETAIL].[OTHERPAYMENTMETHODCODEID])
                    else
                        [REVENUEPAYMENTMETHOD].[PAYMENTMETHOD] + 
                        coalesce(', ' + dbo.UFN_CREDITTYPECODE_GETDESCRIPTION([CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID]) + ' #' + [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER], '') +
                        coalesce(' #' + [CHECKPAYMENTMETHODDETAIL].[CHECKNUMBER], '')
                end [DESCRIPTION],
                null as [DISPLAYSOURCE],
                [REVENUE].[SEQUENCEID] as [SOURCENUMBER],
                2 as [SOURCETYPE],
                case 
                    when [REVENUE].[BATCHNUMBER] is not null then
                        [REVENUESPLIT].[AMOUNT]
                    else
                        [REVENUEPAYMENTMETHOD].[AMOUNT]
                end [REFUNDAMOUNT],
                [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] as [PAYMENTTYPECODE],
                null as [REVENUEID],
                [REVENUESPLIT].[ID] as [REVENUESPLITID],
                [CREDITCARDPAYMENTMETHODDETAIL].[TRANSACTIONID],
                sum(coalesce([CREDITPAYMENT].[AMOUNT], 0)) as [REFUNDS],
                [REVENUESPLIT].[ID] as [PAYMENTGROUPID],
                case
                    when [REVENUE].[BATCHNUMBER] is not null then
                        [REVENUESPLIT].[AMOUNT]
                    else
                        [REVENUEPAYMENTMETHOD].[AMOUNT]
                end [PAYMENTGROUPMAXAMOUNT]
            from dbo.[REVENUE]
            inner join dbo.[REVENUESPLIT] on [REVENUE].[ID] = [REVENUESPLIT].[REVENUEID]
            inner join dbo.[REVENUEPAYMENTMETHOD] on [REVENUE].[ID] = [REVENUEPAYMENTMETHOD].[REVENUEID]
            left join dbo.[CREDITCARDPAYMENTMETHODDETAIL] on [CREDITCARDPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
            left join dbo.[OTHERPAYMENTMETHODDETAIL] on [OTHERPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
            left join dbo.[CHECKPAYMENTMETHODDETAIL] on [CHECKPAYMENTMETHODDETAIL].[ID] = [REVENUEPAYMENTMETHOD].[ID]
            left join dbo.[CREDITPAYMENT] on [CREDITPAYMENT].[REVENUESPLITID] = [REVENUESPLIT].[ID]
            where [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] <> 9 and
            [REVENUESPLIT].[ID] = @ID
            group by 
                    [REVENUESPLIT].[ID],
                    [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE],
                    [OTHERPAYMENTMETHODDETAIL].[OTHERPAYMENTMETHODCODEID],
                    [REVENUEPAYMENTMETHOD].[PAYMENTMETHOD],
                    [CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID],
                    [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER],
                    [CHECKPAYMENTMETHODDETAIL].[CHECKNUMBER],
                    [REVENUE].[SEQUENCEID],
                    [REVENUEPAYMENTMETHOD].[AMOUNT],
                    [CREDITCARDPAYMENTMETHODDETAIL].[TRANSACTIONID],
                    [REVENUE].[BATCHNUMBER],
                    [REVENUESPLIT].[AMOUNT])

    select @PAYMENTS = 
        (select
            [INCLUDE],
            [DESCRIPTION],
            '$' + cast(cast([REFUNDAMOUNT] as money) as nvarchar(20)) + ' ' + [DESCRIPTION] as [DISPLAYDESCRIPTION],
            [DISPLAYSOURCE],
            [SOURCENUMBER],
            [SOURCETYPE],
            [REFUNDAMOUNT] - coalesce([REFUNDS], 0) as [AMOUNTPAID],
            [PAYMENTTYPECODE],
            [REVENUEID],
            [REVENUESPLITID],
            [TRANSACTIONID],
            [PAYMENTGROUPID],
            [PAYMENTGROUPMAXAMOUNT]
        from PAYMENTS_CTE PARAMETERS
        where [REFUNDAMOUNT] - coalesce([REFUNDS], 0) > 0
        order by [SOURCETYPE], [SOURCENUMBER]
        for xml raw ('ITEM'), type, elements, root('PAYMENTS'), BINARY BASE64)


    select 
        @TIMESTAMP = [TSLONG]
    from 
        dbo.[REVENUESPLIT]
    where 
        [ID] = @TRANSACTIONID;

    select
        @CONSTITUENTID = REVENUE.CONSTITUENTID,
        @CONSTITUENTNAME = dbo.UFN_CONSTITUENT_BUILDNAME(REVENUE.CONSTITUENTID)
    from
        dbo.REVENUE
    inner join
        dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID
    where
        REVENUESPLIT.ID = @ID;

    set @DATALOADED = 1;

    return 0;