USP_DATAFORMTEMPLATE_VIEW_CREDITEVENTREGISTRATIONVIEW2

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CREDITEVENTREGISTRATIONVIEW2
(
    @ID uniqueidentifier,  -- REGISTRANTID
    @DATALOADED bit = 0 output,
    @ITEMS xml = null output,
    @TOTAL money = null output,
    @PAYMENTS xml = null output,
    @OTHERPAYMENTMETHODS xml = null output,
    @TRANSACTIONID uniqueidentifier = null output,  -- same as @ID
    @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, 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.UNITVALUE), 0)
    from dbo.FINANCIALTRANSACTIONLINEITEM as LI
    inner join dbo.CREDITITEMEVENTREGISTRATION on LI.ID = CREDITITEMEVENTREGISTRATION.ID
    where REGISTRANTID = @ID;

    set @TOTAL -= @TOTALREFUND;

    set @ITEMS = (
        select
            NF.NAME + ' - ' + [EVENT].NAME as [DESCRIPTION],
            @TOTAL PRICE,
            @TOTAL AMOUNTPAID,
            1 TYPECODE,
            REGISTRANT.ID as EVENTREGISTRANTID,
            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
                    where
                        EVENTREGISTRANTPAYMENT.REGISTRANTID = @ID
                        and EXT.APPLICATIONCODE = 1  -- Event registration
                        and EXT.TYPECODE = 0  -- Gift
                        and LI.TYPECODE = 0  -- Standard
                        and LI.DELETEDON is null
                )
                then 1
                else 0
            end HASCONTRIBUTEDREVENUE
        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, root('ITEMS'), binary base64
    );

    with PAYMENTS_CTE as (
        --payments made on an order
        select
            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 join dbo.CREDITITEM_EXT as EXT on EXT.SALESORDERITEMID = SALESORDERITEM.ID
        left 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
            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
        inner join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
        left join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
        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
            0 as [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, root('PAYMENTS'), binary base64
    );

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

    return 0;