USP_DATAFORMTEMPLATE_VIEW_CREDITEVENTREGISTRATIONVIEW

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CREDITEVENTREGISTRATIONVIEW
(
    @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

    --Error if the registration has been fully refunded already
    if (
        dbo.UFN_REGISTRANT_CALCULATETOTALPAID(@ID) <= 0 and
        exists(select 1 from dbo.[CREDITITEMEVENTREGISTRATION] where [REGISTRANTID] = @ID)
    )
    begin
        raiserror('The selected event registration has previously been refunded in full.', 13, 1);
        return 0;
    end

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

    select
        @TOTAL = sum(LI.BASEAMOUNT)
    from
        dbo.[REGISTRANT]
    inner join
        dbo.[EVENTREGISTRANTPAYMENT] on [EVENTREGISTRANTPAYMENT].[REGISTRANTID] = [REGISTRANT].[ID]
    inner join
        dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
    where
        [REGISTRANT].[ID] = @ID
        and LI.DELETEDON is null
        and LI.TYPECODE <> 1  -- Reversal

    declare @TOTALREFUND money;

    select @TOTALREFUND = isnull(sum((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS), 0)
    from dbo.FINANCIALTRANSACTIONLINEITEM as LI
    inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
    inner join dbo.[CREDITITEMEVENTREGISTRATION] on LI.[ID] = [CREDITITEMEVENTREGISTRATION].[ID]
    where [REGISTRANTID] = @ID;

    set @TOTAL -= @TOTALREFUND;

    set @ITEMS = (
        select 
            null as [SALESORDERITEMID], 
            0 as [INCLUDE],
            0 as [QUANTITYREFUNDING],
            1 as [QUANTITY],
            NF.NAME + ' - ' + [EVENT].[NAME]
            as [DESCRIPTION],
            @TOTAL as [PRICE],
            0 as [FEES],
            @TOTAL as [TOTAL],
            0 as [REFUNDINGTOTAL],
            0 as [DISCOUNTS],
            0 as [DISCOUNTED],
            1 as [TYPECODE],
            null as [PROGRAMID],
            1 as [GROUPTYPECODE],
            [REGISTRANT].[ID] as [GROUPID],
            case
                when exists(
                    select * 
                    from dbo.EVENTREGISTRANTPAYMENT
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
                    inner join dbo.REVENUESPLIT_EXT as EXT on EXT.ID = LI.ID
                    left outer join dbo.SALESORDERITEMEVENTREGISTRATION on SALESORDERITEMEVENTREGISTRATION.REGISTRANTID = EVENTREGISTRANTPAYMENT.REGISTRANTID
                    where 
                        EVENTREGISTRANTPAYMENT.REGISTRANTID = @ID 
                        and EXT.APPLICATIONCODE = 1  -- Event registration
                        and EXT.TYPECODE = 0  -- Gift
                        and SALESORDERITEMEVENTREGISTRATION.ID is null
                        and LI.DELETEDON is null
                        and LI.TYPECODE <> 1  -- Reversal
                ) 
                then 1
                else 0
            end as HASDESIGNATIONS
        from dbo.[REGISTRANT]
        inner join dbo.[EVENT] on [REGISTRANT].[EVENTID] = [EVENT].[ID]
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REGISTRANT.CONSTITUENTID) as NF
        where [REGISTRANT].[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],
            [FINANCIALTRANSACTION].[ID] as [REVENUEID],
            null as [REVENUESPLITID],
            [CREDITCARDPAYMENTMETHODDETAIL].[TRANSACTIONID],
            sum(coalesce((LI.QUANTITY * LI.UNITVALUE) - EXT.DISCOUNTS, 0)) as [REFUNDS],
            [SALESORDERITEM].[ID] as [PAYMENTGROUPID],
            [SALESORDERITEM].[PRICE] as [PAYMENTGROUPMAXAMOUNT],
            cast([FINANCIALTRANSACTION].[DATE] as datetime) as [PAYMENTDATE]
        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.[FINANCIALTRANSACTION] on [SALESORDERPAYMENT].[PAYMENTID] = [FINANCIALTRANSACTION].[ID]
        inner join dbo.[REVENUEPAYMENTMETHOD] on [FINANCIALTRANSACTION].[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 outer join dbo.CREDITITEM_EXT as EXT on EXT.SALESORDERITEMID = [SALESORDERITEM].[ID]
        left outer join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = EXT.ID
        where [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] = @ID
        group by 
            [FINANCIALTRANSACTION].[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],
            [FINANCIALTRANSACTION].[DATE]

        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],
            [FINANCIALTRANSACTION].[SEQUENCEGENERATORID] as [SOURCENUMBER],
            2 as [SOURCETYPE],
            case 
                when [REVENUE_EXT].[BATCHNUMBER] is not null then
                    [FINANCIALTRANSACTIONLINEITEM].[BASEAMOUNT]
                else
                    [REVENUEPAYMENTMETHOD].[AMOUNT]
            end [REFUNDAMOUNT],
            [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] as [PAYMENTTYPECODE],
            null as [REVENUEID],
            [FINANCIALTRANSACTIONLINEITEM].[ID] as [REVENUESPLITID],
            [CREDITCARDPAYMENTMETHODDETAIL].[TRANSACTIONID],
            sum(coalesce([CREDITPAYMENT].[AMOUNT], 0)) as [REFUNDS],
            [FINANCIALTRANSACTIONLINEITEM].[ID] as [PAYMENTGROUPID],
            case
                when [REVENUE_EXT].[BATCHNUMBER] is not null then
                    [FINANCIALTRANSACTIONLINEITEM].[BASEAMOUNT]
                else
                    [REVENUEPAYMENTMETHOD].[AMOUNT]
            end [PAYMENTGROUPMAXAMOUNT],
            cast([FINANCIALTRANSACTION].[DATE] as datetime) as [PAYMENTDATE]
        from dbo.[REGISTRANT]
        inner join dbo.[EVENTREGISTRANTPAYMENT] on [EVENTREGISTRANTPAYMENT].[REGISTRANTID] = [REGISTRANT].[ID]
        inner join dbo.[FINANCIALTRANSACTIONLINEITEM] on [EVENTREGISTRANTPAYMENT].[PAYMENTID] = [FINANCIALTRANSACTIONLINEITEM].[ID]
        inner join dbo.[FINANCIALTRANSACTION] on [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID] = [FINANCIALTRANSACTION].[ID]
        left join dbo.[REVENUE_EXT] on [FINANCIALTRANSACTION].[ID] = [REVENUE_EXT].[ID]
        inner join dbo.[REVENUEPAYMENTMETHOD] on [FINANCIALTRANSACTION].[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] = [FINANCIALTRANSACTIONLINEITEM].[ID]
        where [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] <> 9 and
        [REGISTRANT].ID = @ID
        group by 
                [FINANCIALTRANSACTIONLINEITEM].[ID],
                [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE],
                [OTHERPAYMENTMETHODDETAIL].[OTHERPAYMENTMETHODCODEID],
                [REVENUEPAYMENTMETHOD].[PAYMENTMETHOD],
                [CREDITCARDPAYMENTMETHODDETAIL].[CREDITTYPECODEID],
                [CREDITCARDPAYMENTMETHODDETAIL].[CREDITCARDPARTIALNUMBER],
                [CHECKPAYMENTMETHODDETAIL].[CHECKNUMBER],
                [FINANCIALTRANSACTION].[SEQUENCEGENERATORID],
                [REVENUEPAYMENTMETHOD].[AMOUNT],
                [CREDITCARDPAYMENTMETHODDETAIL].[TRANSACTIONID],
                [REVENUE_EXT].[BATCHNUMBER],
                [FINANCIALTRANSACTIONLINEITEM].[BASEAMOUNT],
                [FINANCIALTRANSACTION].[DATE]
    )
    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],
            [PAYMENTDATE]
        from PAYMENTS_CTE
        where [REFUNDAMOUNT] - coalesce([REFUNDS], 0) > 0
        order by [SOURCETYPE], [SOURCENUMBER]
        for xml raw ('ITEM'), type, elements, root('PAYMENTS'), BINARY BASE64
    );

    select
        @TIMESTAMP = REGISTRANT.TSLONG,
        @CONSTITUENTID = REGISTRANT.CONSTITUENTID,
        @CONSTITUENTNAME = dbo.UFN_CONSTITUENT_BUILDNAME(REGISTRANT.CONSTITUENTID)
    from
        dbo.REGISTRANT
    where
        REGISTRANT.ID = @ID;

    set @DATALOADED = 1;

    return 0;