USP_CREDIT_ADDEVENTREGISTRATIONREFUND

CREATE procedure dbo.USP_CREDIT_ADDEVENTREGISTRATIONREFUND
(
    @ID uniqueidentifier = null output,
    @CHANGEAGENTID uniqueidentifier = null,
    @REGISTRANTID uniqueidentifier = null,
    @ITEMS xml = null,
    @REFUNDMETHODS xml = null,
    @COMMENT nvarchar(500) = '',
    @CREDITREASONCODEID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @TOTAL money = null,
    @TIMESTAMP bigint = null,
    @ADDRESSID uniqueidentifier = null
)
as
begin
    set nocount on;

    if @ID is null
        set @ID = newid();

    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    declare @CURRENTDATE datetime = getdate();
    declare @POSTSTATUSCODE tinyint;

    --Make sure nobody else has done a refund since the form was opened.

    if @TIMESTAMP <> coalesce((select TSLONG from dbo.REGISTRANT where ID = @REGISTRANTID), -1)
    begin
        if @REGISTRANTID is null or @REGISTRANTID = '00000000-0000-0000-0000-000000000000'
            raiserror('ERR_TRANSACTIONID_EVENTREGISTRATIONNOTSELECTED', 13, 1);
        else
            raiserror('ERR_CREDITITEMS_CONFLICTINGEVENTREGISTRATIONREFUND', 13, 1);

        return 1;
    end
    else begin
        update dbo.REGISTRANT
        set DATECHANGED = getdate(), CHANGEDBYID = @CHANGEAGENTID
        where ID = @REGISTRANTID;

        select @POSTSTATUSCODE = LI.POSTSTATUSCODE
        from dbo.EVENTREGISTRANTPAYMENT RP
        inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = RP.PAYMENTID
        where RP.REGISTRANTID = @REGISTRANTID;
    end

    -- Get refund items from input parameter @ITEMS

    declare @CREDITITEMS table(
        ID uniqueidentifier,
        SALESORDERITEMID uniqueidentifier,
        PRICE money,
        TYPECODE tinyint,
        [DESCRIPTION] nvarchar(255),
        AMOUNTTOREFUND money,
        EVENTREGISTRANTID uniqueidentifier,
        UNITVALUE money  -- this needs to reflect the original price, regardless of contributed revenue. Usually, it's the same as AMOUNTTOREFUND.

    );

    insert into @CREDITITEMS
    select
        newid(),
        case when T.item.value('(@SALESORDERITEMID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000'
                then null
            else
                T.item.value('(@SALESORDERITEMID)[1]','uniqueidentifier')
        end SALESORDERITEMID,
        T.item.value('(@PRICE)[1]','money') PRICE,
        T.item.value('(@TYPECODE)[1]','tinyint') TYPECODE,
        null [DESCRIPTION],
        T.item.value('(@AMOUNTTOREFUND)[1]','money') AMOUNTTOREFUND,
        T.item.value('(@EVENTREGISTRANTID)[1]','uniqueidentifier'),
        T.item.value('(@AMOUNTTOREFUND)[1]','money') UNITVALUE
    from @ITEMS.nodes('/ITEMS/ITEM') T(item)
    where T.item.value('(@INCLUDE)[1]','bit') = 1;


    /******** Validate refund item and payments *******/

    declare @REFUNDEDITEMTOTAL money = (select sum(AMOUNTTOREFUND) from @CREDITITEMS);

    -- Error if no items were selected

    if not exists (select 1 from @CREDITITEMS) begin
        raiserror('ERR_ITEMS_NONESELECTED', 13, 1);
        return 1;
    end

    -- Error if the amount refunding is less than zero for any of the items

    if exists (select 1 from @CREDITITEMS where AMOUNTTOREFUND < 0) begin
        raiserror('ERR_ITEMS_NEGATIVEAMOUNT', 13, 1);
        return 1;
    end

    -- Error if the payments do not add up to the refund total

    if coalesce((select sum(T.item.value('(@AMOUNTREFUNDING)[1]','money')) from @REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item)),0) <> @REFUNDEDITEMTOTAL begin
        raiserror('ERR_REFUNDMETHODS_DIFFERENTFROMTOTALAMOUNT', 13, 1);
        return 1;
    end

    -- Error if the refund amount is greater than the total available to refund

    if @REFUNDEDITEMTOTAL > @TOTAL begin
        raiserror('ERR_REFUNDAMOUNT_EXCEEDSLIMIT', 13, 1);
        return 1;
    end


    -- TODO: what is this, and do we need it.

    declare @PAYMENTGROUPID uniqueidentifier = null;
    select top(1)
        @PAYMENTGROUPID = PAYMENTGROUPID
    from (
        select
            T.item.value('(@AMOUNTREFUNDING)[1]','money') as AMOUNTPAID,
            T.item.value('(@PAYMENTGROUPID)[1]','uniqueidentifier') as PAYMENTGROUPID,
            T.item.value('(@PAYMENTGROUPMAXAMOUNT)[1]','money') as PAYMENTGROUPMAXAMOUNT
        from @REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item)
    ) PAYMENTS
    group by PAYMENTGROUPID, PAYMENTGROUPMAXAMOUNT
    having sum(AMOUNTPAID) > PAYMENTGROUPMAXAMOUNT;

    if (@PAYMENTGROUPID is not null) and (@PAYMENTGROUPID <> '00000000-0000-0000-0000-000000000000')
    begin
        declare @PAYMENTSLIST nvarchar(1000)
        declare @PAYMENTGROUPMAXAMOUNT nvarchar(25)

        select
            @PAYMENTSLIST  = dbo.UDA_BUILDLIST(T.item.value('(@DESCRIPTION)[1]','nvarchar(1000)'))
        from @REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item)
        where T.item.value('(@PAYMENTGROUPID)[1]','uniqueidentifier') = @PAYMENTGROUPID

        select top(1)
            @PAYMENTGROUPMAXAMOUNT = convert(nvarchar(25), T.item.value('(@PAYMENTGROUPMAXAMOUNT)[1]','money'))
        from @REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item)
        where T.item.value('(@PAYMENTGROUPID)[1]','uniqueidentifier') = @PAYMENTGROUPID

        set @PAYMENTSLIST = 'The original transaction includes multiple payment methods, so you cannot return the full transaction amount as one refund payment. You may return up to $' + @PAYMENTGROUPMAXAMOUNT + ' among the following payments: ' + @PAYMENTSLIST + '.'

        raiserror(@PAYMENTSLIST, 13, 1);
        return 1;
    end


    -- Find contributed revenue (only for sales ER's; currently, you cannot refund BO event registrations with contributed revenue).


    declare @CONTRIBUTEDREVENUE table (
        CONTRIBUTEDLINEITEMID uniqueidentifier,
        SOURCELINEITEMID uniqueidentifier,
        TYPECODE tinyint,
        [DESCRIPTION] nvarchar(200),
        ORIGINALAMOUNT money
    );

    -- Find contributed revenue.

    insert into @CONTRIBUTEDREVENUE
    select
        CONTRIBUTEDLI.ID CONTRIBUTEDLINEITEMID,
        CONTRIBUTEDLI.SOURCELINEITEMID,
        6 TYPECODE,
        CONTRIBUTEDLI.[DESCRIPTION],
        CONTRIBUTEDLI.BASEAMOUNT ORIGINALAMOUNT
    from @CREDITITEMS CREDITITEMS
    inner join dbo.EVENTREGISTRANTPAYMENT ERP on ERP.REGISTRANTID = CREDITITEMS.EVENTREGISTRANTID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM CONTRIBUTEDLI on CONTRIBUTEDLI.SOURCELINEITEMID = ERP.PAYMENTID  -- ERP.PAYMENTID is a line item on an Order transaction, in this case.

    inner join dbo.REVENUESPLIT_EXT CONTRIBUTEDLI_EXT on CONTRIBUTEDLI_EXT.ID = CONTRIBUTEDLI.ID
    where
        CONTRIBUTEDLI_EXT.TYPECODE = 0  -- Gift (eliminates the payment LI whose source is the earned LI)

        and CONTRIBUTEDLI_EXT.APPLICATIONCODE = 1  -- Application is "Event Registration" (eliminates the payment LI whose source is the contributed LI)

        and CONTRIBUTEDLI.TYPECODE = 0  -- Standard (eliminates adjustments)

        and not exists (  -- Don't include refunded contributed revenue.

            select 1
            from dbo.FINANCIALTRANSACTIONLINEITEM REFUNDLI
            inner join dbo.CREDITITEM_EXT REFUNDLI_EXT on REFUNDLI_EXT.ID = REFUNDLI.ID
            where REFUNDLI.SOURCELINEITEMID = CONTRIBUTEDLI.ID
        );

    if @@ROWCOUNT > 0
    begin
        -- Subtract contributed amount from revenue refund amount on items with partially contributed revenue.

        update @CREDITITEMS
        set AMOUNTTOREFUND = PRICE - coalesce((select sum(ORIGINALAMOUNT) from @CONTRIBUTEDREVENUE),0);

        -- At this point:

        -- -- 1. The UNITVALUE of a partially contributed credit item will reflect its original price.

        -- -- 2. The AMOUNTTOREFUND of a partially contributed credit item will be PRICE - CONTRIBUTEDREVENUE.

        -- Otherwise the refund GL will not be generated correctly.

    end


    declare @INS table
    (
        ID uniqueidentifier,
        CREDITID uniqueidentifier,
        SALESORDERITEMID uniqueidentifier,
        PRICE money,
        TYPECODE tinyint,
        [DESCRIPTION] nvarchar(700),
        GROUPID uniqueidentifier,
        GROUPTYPECODE tinyint,
        SOURCELINEITEMID uniqueidentifier,
        REFUNDAMOUNT money,
        UNITVALUE money
    );

    with CREDITPAYMENTS_CTE as (
        select
            T.item.value('(@AMOUNTREFUNDING)[1]','money') AMOUNT,
            case
                when T.item.value('(@REVENUEID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000'
                    then null
                else
                    T.item.value('(@REVENUEID)[1]','uniqueidentifier')
            end REVENUEID,
            case
                when T.item.value('(@REVENUESPLITID)[1]','uniqueidentifier') = '00000000-0000-0000-0000-000000000000'
                    then null
                else
                    T.item.value('(@REVENUESPLITID)[1]','uniqueidentifier')
            end REVENUESPLITID
        from @REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item)
    )
    insert into @INS
    select
        newid(), -- 2013-07-10 SW: account for multiple backoffice payment methods

        @ID,
        CREDITPAYMENTS.SALESORDERITEMID,
        coalesce(CREDITPAYMENTS.PAIDAMOUNT,0),  -- switch to refund amount

        6 as TYPECODE,
        dbo.UFN_CONSTITUENT_BUILDNAME(REGISTRANT.CONSTITUENTID) + ' - ' + EVENT.NAME,
        CREDITITEMS.EVENTREGISTRANTID GROUPID,  -- why this column is called GROUPID I cannot fathom

        1 GROUPTYPECODE,  -- why this column exists at all is beyond my feeble comprehension

        CREDITPAYMENTS.REVENUESPLITID,
        CREDITPAYMENTS.EARNEDAMOUNT,
        CREDITPAYMENTS.PAIDAMOUNT
    from @CREDITITEMS CREDITITEMS
    inner join dbo.REGISTRANT on CREDITITEMS.EVENTREGISTRANTID = REGISTRANT.ID
    inner join dbo.[EVENT] on REGISTRANT.EVENTID = [EVENT].ID
    outer apply (
        --Backoffice payments

        select
            null as SALESORDERITEMID,
            CREDITPAYMENT.AMOUNT EARNEDAMOUNT,  -- This would not be correct if we had contributed revenue, but we don't make refunds with backoffice contributed revenue.

            CREDITPAYMENT.AMOUNT PAIDAMOUNT,
            CREDITPAYMENT.REVENUESPLITID
        from CREDITPAYMENTS_CTE CREDITPAYMENT
        inner join dbo.EVENTREGISTRANTPAYMENT on CREDITPAYMENT.REVENUESPLITID = EVENTREGISTRANTPAYMENT.PAYMENTID
        where CREDITPAYMENT.REVENUESPLITID is not null

        union all

        --Order payments

        select
            SALESORDERITEM.ID as SALESORDERITEMID,
            PAYMENTSPLIT.BASEAMOUNT EARNEDAMOUNT,
            sum(CREDITPAYMENT.AMOUNT) PAIDAMOUNT,
            PAYMENTSPLIT.ID
        from CREDITPAYMENTS_CTE CREDITPAYMENT
        inner join dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.PAYMENTID = CREDITPAYMENT.REVENUEID
        inner join dbo.SALESORDER on SALESORDER.ID = SALESORDERPAYMENT.SALESORDERID
        inner join dbo.SALESORDERITEM on SALESORDERITEM.SALESORDERID = SALESORDERPAYMENT.SALESORDERID
        inner join dbo.SALESORDERITEMEVENTREGISTRATION on SALESORDERITEM.ID = SALESORDERITEMEVENTREGISTRATION.ID
        inner join dbo.REGISTRANT on SALESORDERITEMEVENTREGISTRATION.REGISTRANTID = REGISTRANT.ID
        left join dbo.EVENTREGISTRANTPAYMENT on EVENTREGISTRANTPAYMENT.REGISTRANTID = REGISTRANT.ID
        left join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTSPLIT on PAYMENTSPLIT.ID = EVENTREGISTRANTPAYMENT.PAYMENTID  -- I think this is actually an order split, but don't take my word for it.

        left join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = PAYMENTSPLIT.ID
        left join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = PAYMENTSPLIT.FINANCIALTRANSACTIONID
        where SALESORDERITEMEVENTREGISTRATION.REGISTRANTID = @REGISTRANTID
            and FINANCIALTRANSACTION.ID = SALESORDER.REVENUEID
            and REVENUESPLIT_EXT.APPLICATIONCODE = 1
            and REVENUESPLIT_EXT.TYPECODE = 1
        group by SALESORDERITEM.ID, PAYMENTSPLIT.ID, PAYMENTSPLIT.BASEAMOUNT
    ) CREDITPAYMENTS;

    -- Contributed revenue

    insert into @INS
    select
        newid(),
        @ID,
        null,
        ORIGINALAMOUNT,
        TYPECODE,
        [DESCRIPTION],
        null,
        0 GROUPTYPECODE,
        CONTRIBUTEDLINEITEMID,
        ORIGINALAMOUNT,
        ORIGINALAMOUNT
    from @CONTRIBUTEDREVENUE;


    begin try
        -- Bug #167166 - MDC

        -- Weird that the @TRANSACTIONDATE is being inserted into a datetimeoffset column. Always has offset 0.

        declare @TRANSACTIONDATE datetime = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEFROMUTC(getutcdate());

        declare @CURRENCYID uniqueidentifier = (select ID from dbo.CURRENCY where ISORGANIZATIONCURRENCY = 1);
        declare @PDACCOUNTSYSTEMID uniqueidentifier;
        declare @ALLOWGLDISTRIBUTIONS bit;

        select
            @PDACCOUNTSYSTEMID = ID,
            @ALLOWGLDISTRIBUTIONS = ALLOWGLDISTRIBUTIONS
        from
            dbo.UFN_PDACCOUNTSYSTEM_DEFAULTORSYSTEM();

        declare @CONSTITUENTID uniqueidentifier = (select REGISTRANT.CONSTITUENTID from dbo.REGISTRANT where ID = @REGISTRANTID);

        -- This seems shaky. If there are multiple sales order payments we're refunding, we set the SALESORDERID in CREDIT_EXT to be the first one we find.

        declare @EVENTREGISTRATIONREVENUEID uniqueidentifier = (
            select top 1 T.item.value('(@REVENUEID)[1]','uniqueidentifier')
            from @REFUNDMETHODS.nodes('/REFUNDMETHODS/ITEM') T(item)
        );

        declare @ORDERID uniqueidentifier;
        if @EVENTREGISTRATIONREVENUEID <> '00000000-0000-0000-0000-000000000000'
        begin
            select top 1 @ORDERID = SALESORDERPAYMENT.SALESORDERID
            from dbo.SALESORDERPAYMENT
            where PAYMENTID = @EVENTREGISTRATIONREVENUEID;
        end


        insert into dbo.FINANCIALTRANSACTION
        (
            ID,
            TRANSACTIONAMOUNT,
            BASEAMOUNT,
            ORGAMOUNT,
            TRANSACTIONCURRENCYID,
            TYPECODE,
            PDACCOUNTSYSTEMID,
            [DESCRIPTION],
            [DATE],
            POSTDATE,
            POSTSTATUSCODE,
            PARENTID,
            APPUSERID,
            CONSTITUENTID,
            DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID
        )
        values
        (
            @ID,
            @REFUNDEDITEMTOTAL,
            @REFUNDEDITEMTOTAL,
            @REFUNDEDITEMTOTAL,
            @CURRENCYID,
            23, --Refund

            @PDACCOUNTSYSTEMID,
            @COMMENT,
            @TRANSACTIONDATE,
            case when @ALLOWGLDISTRIBUTIONS = 1 and isnull(@POSTSTATUSCODE, 1) != 3 then cast(@TRANSACTIONDATE as date) else null end,
            case when @ALLOWGLDISTRIBUTIONS = 1 and isnull(@POSTSTATUSCODE, 1) != 3 then 1 else 3 end,
            (select REVENUEID from dbo.SALESORDER where ID = @ORDERID),
            @CURRENTAPPUSERID,
            @CONSTITUENTID,
            @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID
        );

        insert into dbo.CREDIT_EXT
        (
            ID,
            SALESORDERID,
            CREDITREASONCODEID,
            DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID
        )
        values
        (
            @ID,
            @ORDERID,
            case @CREDITREASONCODEID
                when '00000000-0000-0000-0000-000000000000' then null
                else @CREDITREASONCODEID
            end,
            @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID
        );

        exec dbo.USP_CREDIT_ADDCREDITPAYMENTS @ID, @REFUNDMETHODS, @CHANGEAGENTID, @CURRENTAPPUSERID, @ADDRESSID;

        insert into dbo.FINANCIALTRANSACTIONLINEITEM
        (
            ID,
            FINANCIALTRANSACTIONID,
            UNITVALUE,
            QUANTITY,
            [DESCRIPTION],
            TRANSACTIONAMOUNT,
            BASEAMOUNT,
            ORGAMOUNT,
            POSTDATE,
            POSTSTATUSCODE,
            TYPECODE,
            SOURCELINEITEMID,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        select
            I.ID,
            I.CREDITID,
            I.UNITVALUE,
            1 QUANTITY,
            I.[DESCRIPTION],
            I.REFUNDAMOUNT,
            I.REFUNDAMOUNT,
            I.REFUNDAMOUNT,
            FT.POSTDATE,
            FT.POSTSTATUSCODE,
            0 TYPECODE,
            I.SOURCELINEITEMID,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from @INS I
        inner join dbo.FINANCIALTRANSACTION FT on FT.ID = I.CREDITID;

        insert into dbo.CREDITITEM_EXT
        (
            ID,
            CREDITID,
            DISCOUNTS,
            FEES,
            GROUPID,
            GROUPTYPECODE,
            SALESORDERITEMID,
            TYPECODE,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        select
            ID,
            CREDITID,
            0 DISCOUNTS,
            0 FEES,
            GROUPID,
            GROUPTYPECODE,
            SALESORDERITEMID,
            TYPECODE,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from @INS;

        --Add credit event registration items

        -- TODO: verify, wrt bug notes from July

        insert into dbo.CREDITITEMEVENTREGISTRATION
        (
            ID,
            REGISTRANTID,
            EVENTNAME,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        select
            ID, -- 2013-07-10 SW: @INS.ID is not the same as @CREDITITEMS.ID

            GROUPID,
            [DESCRIPTION],
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        from @INS;


        if @ALLOWGLDISTRIBUTIONS = 1 and isnull(@POSTSTATUSCODE, 1) != 3 begin
            exec dbo.USP_REFUND_CREATEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CURRENTDATE;
        end

    end try
    begin catch
        exec dbo.USP_RAISE_ERROR
        return 1
    end catch

    return 0;
end