USP_DATAFORMTEMPLATE_VIEW_CREDITMEMBERSHIPVIEW2

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

    --Raise error if membership is not active
    if (select STATUSCODE from dbo.MEMBERSHIP where ID = @ID) <> 0
    begin
        raiserror('The membership selected is not active. Only active memberships may be refunded.', 13, 1);
    end

    if (select EXPIRATIONDATE from dbo.MEMBERSHIP where ID = @ID) < getdate()
    begin
        raiserror('The membership selected has lapsed. Lapsed memberships may not be refunded.', 13, 1);
    end

    declare @MEMBERSHIPTRANSACTIONID uniqueidentifier = (
        select top 1 ID
        from dbo.MEMBERSHIPTRANSACTION
        where MEMBERSHIPID = @ID
        order by DATEADDED desc
    );

    set @TRANSACTIONID = @MEMBERSHIPTRANSACTIONID;

    declare @ITEMSTABLE table(
        SALESORDERITEMID uniqueidentifier,
        [DESCRIPTION] nvarchar(500),
        PRICE money,
        DISCOUNTS money,
        AMOUNTPAID money,
        TYPECODE tinyint,
        SALESORDERID uniqueidentifier,
        ITEMID uniqueidentifier,
        REVENUESPLITID uniqueidentifier,
        HASCONTRIBUTEDREVENUE bit,
        PARENTITEMID uniqueidentifier  -- Each addon item will include the MEMBERSHIPTRANSACTIONID for the transaction being refunded.
    );

    declare @PAYMENTSTABLE table(
        [INCLUDE] bit,
        [DESCRIPTION] nvarchar(500),
        DISPLAYDESCRIPTION nvarchar(500),
        DISPLAYSOURCE nvarchar(100),
        SOURCENUMBER nvarchar(100),
        SOURCETYPE tinyint,
        REFUNDAMOUNT money,
        AMOUNTPAID money,
        PAYMENTTYPECODE tinyint,
        REVENUEID uniqueidentifier,
        REVENUESPLITID uniqueidentifier,
        TRANSACTIONID uniqueidentifier,
        PAYMENTDATE datetime
    );

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

    declare @SALESORDERID uniqueidentifier;
    select @SALESORDERID = SALESORDER.ID
    from dbo.SALESORDER
    inner join dbo.SALESORDERITEM on SALESORDER.ID = SALESORDERITEM.SALESORDERID
    inner join dbo.SALESORDERITEMMEMBERSHIP on SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIP.ID
    where SALESORDERITEMMEMBERSHIP.MEMBERSHIPTRANSACTIONID = @MEMBERSHIPTRANSACTIONID;

    --If the membership was paid for on an order
    if @SALESORDERID is not null
    begin
        insert into @ITEMSTABLE
        select
            SALESORDERITEM.ID SALESORDERITEMID,
            SALESORDERITEM.[DESCRIPTION],
            SALESORDERITEM.TOTAL PRICE,
            sum(coalesce(SALESORDERITEMMEMBERSHIPITEMPROMOTION.AMOUNT, 0)) DISCOUNTS,
            SALESORDERITEM.TOTAL - sum(coalesce(SALESORDERITEMMEMBERSHIPITEMPROMOTION.AMOUNT, 0)) AMOUNTPAID,
            SALESORDERITEM.TYPECODE TYPECODE,
            SALESORDERITEM.SALESORDERID,
            MEMBERSHIPTRANSACTION.ID ITEMID,
            MEMBERSHIPTRANSACTION.REVENUESPLITID,
            0 HASCONTRIBUTEDREVENUE,
            null PARENTITEMID
        from dbo.SALESORDERITEM
        inner join dbo.SALESORDERITEMMEMBERSHIP on SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIP.ID
        inner join dbo.MEMBERSHIPTRANSACTION on SALESORDERITEMMEMBERSHIP.MEMBERSHIPTRANSACTIONID = MEMBERSHIPTRANSACTION.ID
        left join dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION on SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID
        left join dbo.CREDITITEM_EXT REFUNDLINEITEM_EXT on REFUNDLINEITEM_EXT.SALESORDERITEMID = SALESORDERITEM.ID
        where SALESORDERITEM.SALESORDERID = @SALESORDERID
            and MEMBERSHIPTRANSACTION.ID = @MEMBERSHIPTRANSACTIONID
            and REFUNDLINEITEM_EXT.ID is null  -- Exclude membership that has already been refunded (only load add-ons).
        group by
            SALESORDERITEM.ID,
            SALESORDERITEM.[DESCRIPTION],
            SALESORDERITEM.TOTAL,
            SALESORDERITEM.TYPECODE,
            SALESORDERITEM.SALESORDERID,
            MEMBERSHIPTRANSACTION.ID,
            MEMBERSHIPTRANSACTION.REVENUESPLITID;
    end
    else begin
        --The membership was paid for outside of an order

        insert into @ITEMSTABLE
        select
            null as SALESORDERITEMID,
            isnull(dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID) + ' - '
                    + dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID) + ' ('
                    + dbo.UFN_MEMBERSHIPLEVELTERM_GETVALUE(MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELTERMID) + '): '
                , '')
                + MEMBERSHIPTRANSACTION.[ACTION]
            as [DESCRIPTION],
            FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT as PRICE,
            0 as DISCOUNTS,
            FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT as AMOUNTPAID,
            1 as TYPECODE,
            null as SALESORDERID,
            @TRANSACTIONID as ITEMID,
            MEMBERSHIPTRANSACTION.REVENUESPLITID,
            0 as HASCONTRIBUTEDREVENUE,
            null PARENTITEMID
        from dbo.MEMBERSHIP
        inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBERSHIPID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
        left join dbo.FINANCIALTRANSACTIONLINEITEM REFUNDLINEITEM on REFUNDLINEITEM.SOURCELINEITEMID = MEMBERSHIPTRANSACTION.REVENUESPLITID
        left join dbo.CREDITITEM_EXT REFUNDLINEITEM_EXT on REFUNDLINEITEM_EXT.ID = REFUNDLINEITEM.ID
        where MEMBERSHIP.ID = @ID
            and MEMBERSHIPTRANSACTION.ID = @MEMBERSHIPTRANSACTIONID
            and REFUNDLINEITEM_EXT.ID is null;  -- Exclude membership that has already been refunded (only load add-ons).
    end

    --find all the add-ons
    insert into @ITEMSTABLE
    select
        SALESORDERITEM.ID SALESORDERITEMID,
        ADDON.NAME,
        MEMBERSHIPADDON.ORGANIZATIONPURCHASEPRICE as PRICE,
        0 DISCOUNTS,
        MEMBERSHIPADDON.ORGANIZATIONPURCHASEPRICE as AMOUNTPAID,
        16,
        SALESORDERITEM.SALESORDERID,
        MEMBERSHIPADDON.ID ITEMID,
        MEMBERSHIPADDON.REVENUESPLITID,
        0 HASCONTRIBUTEDREVENUE,
        MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID as PARENTITEMID
    from dbo.MEMBERSHIPADDON
    inner join dbo.ADDON on ADDON.ID = MEMBERSHIPADDON.ADDONID
    left join dbo.SALESORDERITEMMEMBERSHIPADDON on SALESORDERITEMMEMBERSHIPADDON.MEMBERSHIPADDONID = MEMBERSHIPADDON.ID
    left join dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIPADDON.ID
    outer apply (
        select
            sum(QUANTITY) QUANTITY
        from dbo.FINANCIALTRANSACTIONLINEITEM
        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
        inner join dbo.CREDITITEM_EXT on CREDITITEM_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
        where
            FINANCIALTRANSACTION.TYPECODE = 23  -- refund
            and CREDITITEM_EXT.TYPECODE = 16  -- membership add-on
            and FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID = MEMBERSHIPADDON.REVENUESPLITID
    ) REFUNDEDADDONS
    cross apply (
        select NUM
        from dbo.NUMBERS
        where NUMBERS.NUM < MEMBERSHIPADDON.QUANTITY - isnull(REFUNDEDADDONS.QUANTITY, 0)  -- Make an entry for each remaining add-on unit
    ) NUMBERS
    where
        MEMBERSHIPADDON.MEMBERSHIPID = @ID
        and MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID = @MEMBERSHIPTRANSACTIONID;

    --Get the total of the membership and all the addons
    select @TOTAL = sum(AMOUNTPAID) from @ITEMSTABLE;

    --Update it if it has contributed revenue
    with CONTRIBUTEDREVENUE_CTE as
    (
        select
            case
                when sum(FTLI.TRANSACTIONAMOUNT) > 0 then 1
                else 0
            end HASCONTRIBUTEDREVENUE,
            I.REVENUESPLITID as ID
        from @ITEMSTABLE I
        inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.SOURCELINEITEMID = I.REVENUESPLITID
        inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FTLI.ID
        where REVENUESPLIT_EXT.TYPECODE = 0
            and REVENUESPLIT_EXT.APPLICATIONCODE = 0  -- only donations
            and FTLI.TYPECODE = 0  -- only standard ft line items
        group by I.REVENUESPLITID
    )
    update @ITEMSTABLE
    set HASCONTRIBUTEDREVENUE = CTE.HASCONTRIBUTEDREVENUE
    from CONTRIBUTEDREVENUE_CTE CTE
    where CTE.ID = REVENUESPLITID;

    set @ITEMS = (
        select
            SALESORDERITEMID,
            [DESCRIPTION],
            PRICE,
            DISCOUNTS,
            AMOUNTPAID,
            TYPECODE,
            ITEMID,
            REVENUESPLITID,
            HASCONTRIBUTEDREVENUE,
            PARENTITEMID
        from @ITEMSTABLE
        order by TYPECODE
        for xml raw ('ITEM'), type, root('ITEMS'), binary base64
    );

    --if it was paid for on an order, get the payments
    if @SALESORDERID is not null
    begin
        --get all the payments from sales orders
        insert into @PAYMENTSTABLE
        select
            PAYMENTS.[INCLUDE],
            PAYMENTS.[DESCRIPTION],
            case
                when PAYMENTS.AMOUNTPAID < ITEMTOTAL then
                    '$' + cast(PAYMENTS.AMOUNTPAID as nvarchar(20)) + ' ' + PAYMENTS.[DESCRIPTION]
                else
                    '$' + cast(ITEMTOTAL as nvarchar(20)) + ' ' + PAYMENTS.[DESCRIPTION]
            end DISPLAYDESCRIPTION,
            PAYMENTS.DISPLAYSOURCE,
            PAYMENTS.SOURCENUMBER,
            PAYMENTS.SOURCETYPE,
            PAYMENTS.REFUNDAMOUNT,
            case
                when PAYMENTS.AMOUNTPAID < ITEMTOTAL then
                    PAYMENTS.AMOUNTPAID
                else
                    ITEMTOTAL
            end AMOUNTPAID,
            PAYMENTS.PAYMENTTYPECODE,
            PAYMENTS.REVENUEID,
            PAYMENTS.REVENUESPLITID,
            PAYMENTS.TRANSACTIONID,
            PAYMENTS.PAYMENTDATE
        from (
            select
                0 [INCLUDE],
                case
                    when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 10 then
                        dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(OTHERPAYMENTMETHODCODEID)
                    else
                        REVENUEPAYMENTMETHOD.PAYMENTMETHOD
                        + coalesce(', ' + dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID) + ' #' + CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER, '')
                        + coalesce(' #' + CHECKPAYMENTMETHODDETAIL.CHECKNUMBER, '')
                end [DESCRIPTION],
                null DISPLAYSOURCE,
                SALESORDER.SEQUENCEID SOURCENUMBER,
                0 SOURCETYPE,
                0 REFUNDAMOUNT,
                FINANCIALTRANSACTION.BASEAMOUNT - coalesce(
                    (
                        select sum(coalesce(CREDITPAYMENT.AMOUNT, 0))
                        from dbo.CREDITPAYMENT
                        where REVENUEID = FINANCIALTRANSACTION.ID
                    ), 0
                ) AMOUNTPAID, -- (amount - refunds)
                REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE PAYMENTTYPECODE,
                1 AUTOINCLUDED,
                FINANCIALTRANSACTION.ID REVENUEID,
                null REVENUESPLITID,
                CREDITCARDPAYMENTMETHODDETAIL.TRANSACTIONID,
                sum(ITEMSTABLE.AMOUNTPAID) ITEMTOTAL,
                cast(FINANCIALTRANSACTION.[DATE] as datetime) as PAYMENTDATE
            from dbo.SALESORDER
            inner join dbo.SALESORDERPAYMENT on SALESORDERPAYMENT.SALESORDERID = SALESORDER.ID
            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 @ITEMSTABLE ITEMSTABLE on SALESORDER.ID = ITEMSTABLE.SALESORDERID
            where SALESORDER.ID = @SALESORDERID
            group by REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE, CREDITCARDPAYMENTMETHODDETAIL.TRANSACTIONID, FINANCIALTRANSACTION.ID, OTHERPAYMENTMETHODDETAIL.OTHERPAYMENTMETHODCODEID, REVENUEPAYMENTMETHOD.PAYMENTMETHOD, CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID, CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER, CHECKPAYMENTMETHODDETAIL.CHECKNUMBER, SALESORDER.SEQUENCEID, FINANCIALTRANSACTION.BASEAMOUNT, FINANCIALTRANSACTION.[DATE]
        ) PAYMENTS
        where PAYMENTS.AMOUNTPAID > 0;
    end
    else begin
        --it was paid for through the back office

        insert into @PAYMENTSTABLE
        select
            0 [INCLUDE],
            case
                when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 10 then
                    dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(OTHERPAYMENTMETHODCODEID)
                else
                    REVENUEPAYMENTMETHOD.PAYMENTMETHOD
                    + coalesce(', ' + dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID) + ' #' + CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER, '')
                    + coalesce(' #' + CHECKPAYMENTMETHODDETAIL.CHECKNUMBER, '')
            end [DESCRIPTION],
            '$' + cast(@TOTAL as nvarchar(20)) + ' ' + case
                when REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE = 10 then
                    dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(OTHERPAYMENTMETHODCODEID)
                else
                    REVENUEPAYMENTMETHOD.PAYMENTMETHOD
                    + coalesce(', ' + dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITCARDPAYMENTMETHODDETAIL.CREDITTYPECODEID) + ' #' + CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER, '')
                    + coalesce(' #' + CHECKPAYMENTMETHODDETAIL.CHECKNUMBER, '')
            end DISPLAYDESCRIPTION,
            '' DISPLAYSOURCE,
            case
                when REVENUE_EXT.BATCHNUMBER <> '' then REVENUE_EXT.BATCHNUMBER
                else convert(nvarchar(50), FINANCIALTRANSACTION.SEQUENCEGENERATORID)
            end SOURCENUMBER,
            case
                when REVENUE_EXT.BATCHNUMBER <> '' then 1
                else 2
            end SOURCETYPE, -- Payment (vs. Batch or Order)
            0 REFUNDAMOUNT,
            @TOTAL AMOUNTPAID,
            REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE PAYMENTTYPECODE,
            FINANCIALTRANSACTION.ID REVENUEID,
            null REVENUESPLITID,
            CREDITCARDPAYMENTMETHODDETAIL.TRANSACTIONID,
            cast(FINANCIALTRANSACTION.[DATE] as datetime) PAYMENTDATE
        from dbo.MEMBERSHIPTRANSACTION
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on MEMBERSHIPTRANSACTION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
        inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
        inner 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
        where MEMBERSHIPTRANSACTION.ID = @MEMBERSHIPTRANSACTIONID;
    end

    set @PAYMENTS = (
        select * from @PAYMENTSTABLE
        for xml raw ('ITEM'), type, root('PAYMENTS'), binary base64
    );

    select @TIMESTAMP = TSLONG
    from dbo.MEMBERSHIPTRANSACTION
    where ID = @TRANSACTIONID;

    select
        @CONSTITUENTID = MEMBER.CONSTITUENTID,
        @CONSTITUENTNAME = dbo.UFN_CONSTITUENT_BUILDNAME(MEMBER.CONSTITUENTID)
    from dbo.MEMBERSHIP
    inner join dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
    where MEMBERSHIP.ID = @ID
        and MEMBER.ISPRIMARY = 1;

    set @DATALOADED = 1;

    return 0;
end