UFN_CREDIT_GETSALESORDERITEMS2

CREATE function dbo.UFN_CREDIT_GETSALESORDERITEMS2
(
    @SALESORDERID uniqueidentifier
)
returns @SALESORDERITEMS table
(
    SALESORDERITEMID uniqueidentifier,
    [DESCRIPTION] nvarchar(510),
    PRICE money,
    DISCOUNTS money,
    AMOUNTPAID money,
    TYPECODE tinyint,
    PROGRAMID uniqueidentifier,  --program id for tickets, merchandise department id for merch

    ISCOMBINATION bit,
    EVENTREGISTRANTID uniqueidentifier,  -- special event registrant id (not for preregistered)

    ITEMID uniqueidentifier, -- represents TICKET.ID, MERCHANDISEUNIT.ID, SALESORDERITEMTICKETFEE.ID, MEMBERSHIPTRANSACTION.ID, or MEMBERSHIPADDON.ID.

    REVENUESPLITID uniqueidentifier,
    HASCONTRIBUTEDREVENUE bit,
    TICKETNUMBER bigint,
    SORTFIELD nvarchar(200),
    PARENTITEMID uniqueidentifier
)
begin

    -- Adding this table to save some performance tax later on when linking back to the revenue split for the item

    declare @ITEMS table
    (
        SALESORDERITEMID uniqueidentifier,
        [DESCRIPTION] nvarchar(510),
        PRICE money,
        DISCOUNTS money,
        AMOUNTPAID money,
        TYPECODE tinyint,
        PROGRAMID uniqueidentifier,
        ISCOMBINATION bit,
        ITEMID uniqueidentifier,
        REVENUESPLITID uniqueidentifier,
        MEMBERSHIPTRANSACTIONID uniqueidentifier,
        /*
            Fields used to link back to the order split for the item for
            the cases that we have to get it from REVENUESPLITORDER
        */
        EVENTID uniqueidentifier,
        DESIGNATIONID uniqueidentifier,
        FEEID uniqueidentifier,
        MERCHANDISEPRODUCTINSTANCEID uniqueidentifier,
        EVENTREGISTRANTID uniqueidentifier,
        /*
            end REVENUESPLITORDER link fields
        */
        HASCONTRIBUTEDREVENUE bit,
        APPLIESTOITEMSORTCRITERIA nvarchar(300), -- Used only in the final select to group item-level fees with the items they apply to.

        TICKETNUMBER bigint,
        PARENTITEMID uniqueidentifier  -- Each membership addon item will include the MEMBERSHIPTRANSACTIONID for the transaction being refunded (in case the order includes multiple memberships).

    );

    declare @CALCULATEDFIELDS table (
        SALESORDERITEMID uniqueidentifier,
        DISCOUNTS money,
        PROGRAMID uniqueidentifier, -- program id for tickets, merchandise department id for merch

        ITEMID uniqueidentifier,
        TICKETNUMBER bigint
    );

    -- We get order-level fees from this table variable, but not item-level. Really, we shouldn't be inserting the item-level fees.

    insert into @CALCULATEDFIELDS
    select
        SALESORDERITEM.ID,
        case SALESORDERITEM.TYPECODE
            when 0 then TICKET.ITEMLEVELDISCOUNTSAPPLIED + TICKET.ORDERLEVELDISCOUNTSAPPLIED
            when 1 then isnull(MEMBERSHIPPROMOAMOUNT.AMOUNT, 0) + SALESORDERITEMMEMBERSHIPITEMPROMOTIONAMOUNT.AMOUNT
            when 14 then MERCHUNIT.ITEMLEVELDISCOUNTSAPPLIED + MERCHUNIT.ORDERLEVELDISCOUNTSAPPLIED
            else 0
        end as DISCOUNTS,
        case SALESORDERITEM.TYPECODE
            when 0 then SALESORDERITEMTICKET.PROGRAMID  -- tickets

            when 14 then SALESORDERITEMMERCHANDISE.MERCHANDISEDEPARTMENTID  -- merchandise

            else null
        end PROGRAMID,
        case
            when SALESORDERITEM.TYPECODE = 0 then TICKET.ID
            when SALESORDERITEM.TYPECODE = 14 then MERCHUNIT.ID
            when SALESORDERITEM.TYPECODE = 1 then SALESORDERITEMMEMBERSHIP.MEMBERSHIPTRANSACTIONID
            when SALESORDERITEM.TYPECODE = 16 then SALESORDERITEMMEMBERSHIPADDON.MEMBERSHIPADDONID
            else null
        end ITEMID,
        TICKET.TICKETNUMBER
    from dbo.SALESORDERITEM
    left join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
    left join dbo.TICKET on TICKET.SALESORDERITEMTICKETID = SALESORDERITEMTICKET.ID
    left join dbo.SALESORDERITEMMERCHANDISE on SALESORDERITEM.ID = SALESORDERITEMMERCHANDISE.ID
    left join dbo.SALESORDERITEMMERCHANDISEUNIT MERCHUNIT on MERCHUNIT.SALESORDERITEMMERCHANDISEID = SALESORDERITEMMERCHANDISE.ID
    left join dbo.SALESORDERITEMMEMBERSHIP on SALESORDERITEMMEMBERSHIP.ID = SALESORDERITEM.ID
    left join dbo.SALESORDERITEMMEMBERSHIPADDON on SALESORDERITEMMEMBERSHIPADDON.ID = SALESORDERITEM.ID
    outer apply (
        select SALESORDERITEM.QUANTITY - coalesce((
                select sum(FTLI.QUANTITY)
                from dbo.FINANCIALTRANSACTIONLINEITEM FTLI
                inner join dbo.CREDITITEM_EXT EXT on EXT.ID = FTLI.ID
                where EXT.SALESORDERITEMID = SALESORDERITEM.ID
            ),0)
        as QUANTITY
    ) QUANTITYREMAINING
    left join dbo.NUMBERS on (SALESORDERITEMMEMBERSHIPADDON.ID is not null and NUMBERS.NUM < QUANTITYREMAINING.QUANTITY)
    outer apply (
        select
            case
                when MEMBERSHIPPROMOITEM.[PERCENT] > 0 then SALESORDERITEM.TOTAL * MEMBERSHIPPROMOITEM.[PERCENT] * 0.01
                else MEMBERSHIPPROMOITEM.PRICE
            end AMOUNT
        from dbo.SALESORDERITEM as MEMBERSHIPPROMOITEM
        inner join dbo.SALESORDERITEMMEMBERSHIPPROMO on SALESORDERITEMMEMBERSHIPPROMO.ID = SALESORDERITEM.ID
        where
            MEMBERSHIPPROMOITEM.SALESORDERID = @SALESORDERID
            and MEMBERSHIPPROMOITEM.PRICE > 0
            and SALESORDERITEMMEMBERSHIPPROMO.INUSE = 1
    ) as MEMBERSHIPPROMOAMOUNT
    outer apply (
        select isnull(sum(SALESORDERITEMMEMBERSHIPITEMPROMOTION.AMOUNT), 0) as AMOUNT
        from dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION
        where SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID = SALESORDERITEM.ID
    ) as SALESORDERITEMMEMBERSHIPITEMPROMOTIONAMOUNT
    where
        SALESORDERITEM.SALESORDERID = @SALESORDERID
        and QUANTITYREMAINING.QUANTITY > 0
        and (TICKET.ID is null or (TICKET.ISREFUNDED = 0 and TICKET.APPLIEDTOMEMBERSHIP = 0)) -- Don't include refunded, or applied tickets.

        and (MERCHUNIT.ID is null or MERCHUNIT.REFUNDEDAMOUNT = 0)  -- Don't include refunded merchandise items

        and SALESORDERITEM.TYPECODE <> 5;  -- Don't include discounts



    insert into @ITEMS
    select
        SALESORDERITEM.ID SALESORDERITEMID,
        -- Combo tickets have the same description as regular tickets, but with a prefix indicating the combo name and ticket grouping

        case
            when SALESORDERITEMTICKETCOMBINATION.ID is not null
                then COMBINATION.NAME + ' ' + cast(COMBOTICKETNUMBERS.TICKETGROUPNUMBER as nvarchar(3)) + ' - '
            else ''
        end + case
            when SALESORDERITEM.TYPECODE = 0 and SALESORDERITEMTICKET.EVENTID is not null
                then SALESORDERITEM.[DESCRIPTION] + ' - ' + convert(nvarchar(8),[EVENT].STARTDATE, 1) + ' ' + dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].STARTTIME)
            when SALESORDERITEM.TYPECODE = 6
                then dbo.UFN_CONSTITUENT_BUILDNAME(REGISTRANT.CONSTITUENTID) + ' - ' + SALESORDERITEM.[DESCRIPTION]
            else
                SALESORDERITEM.[DESCRIPTION]
        end [DESCRIPTION],
        SALESORDERITEM.PRICE,
        CALCULATEDFIELDS.DISCOUNTS,
        SALESORDERITEM.PRICE - CALCULATEDFIELDS.DISCOUNTS as AMOUNTPAID,
        SALESORDERITEM.TYPECODE,
        CALCULATEDFIELDS.PROGRAMID,
        case when COMBINATION.ID is not null then 1 else 0 end ISCOMBINATION,
        CALCULATEDFIELDS.ITEMID,
        -- We already have the addon/transaction, so grab the revenuesplitid now for memberships/addons

        case
            when SALESORDERITEM.TYPECODE = 16 then MEMBERSHIPADDON.REVENUESPLITID
            when SALESORDERITEM.TYPECODE = 1 then MEMBERSHIPTRANSACTION.REVENUESPLITID
            else null
        end REVENUESPLITID,
        case
            when SALESORDERITEM.TYPECODE = 16 then MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID
            else SALESORDERITEMMEMBERSHIP.MEMBERSHIPTRANSACTIONID
        end MEMBERSHIPTRANSACTIONID,
        SALESORDERITEMTICKET.EVENTID,
        SALESORDERITEMDONATION.DESIGNATIONID,
        SALESORDERITEMFEE.FEEID,
        SALESORDERITEMMERCHANDISE.MERCHANDISEPRODUCTINSTANCEID,
        SALESORDERITEMEVENTREGISTRATION.REGISTRANTID EVENTREGISTRANTID,
        0 HASCONTRIBUTEDREVENUE,
        null APPLIESTOITEMSORTCRITERIA,
        CALCULATEDFIELDS.TICKETNUMBER,
        MEMBERSHIPADDON.MEMBERSHIPTRANSACTIONID as PARENTITEMID
    from dbo.SALESORDERITEM
    inner join @CALCULATEDFIELDS as CALCULATEDFIELDS on SALESORDERITEM.ID = CALCULATEDFIELDS.SALESORDERITEMID
    left join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
    left join dbo.SALESORDERITEMTICKETCOMBINATION SALESORDERITEMTICKETCOMBINATION on SALESORDERITEMTICKET.ID = SALESORDERITEMTICKETCOMBINATION.ID
    left join dbo.COMBINATION on SALESORDERITEMTICKETCOMBINATION.COMBINATIONID = COMBINATION.ID
    left join dbo.UFN_SALESORDER_GETDETERMINISTICCOMBOTICKETNUMBERS(@SALESORDERID) as COMBOTICKETNUMBERS on COMBOTICKETNUMBERS.TICKETID = CALCULATEDFIELDS.ITEMID
    left join dbo.[EVENT] on SALESORDERITEMTICKET.EVENTID = [EVENT].ID
    left join dbo.SALESORDERITEMFEE on dbo.SALESORDERITEMFEE.ID = dbo.SALESORDERITEM.ID
    left join dbo.SALESORDERITEMEVENTREGISTRATION on SALESORDERITEM.ID = SALESORDERITEMEVENTREGISTRATION.ID
    left join dbo.REGISTRANT on REGISTRANT.ID = SALESORDERITEMEVENTREGISTRATION.REGISTRANTID
    left join dbo.SALESORDERITEMMEMBERSHIPADDON on SALESORDERITEMMEMBERSHIPADDON.ID = SALESORDERITEM.ID
    left join dbo.MEMBERSHIPADDON on MEMBERSHIPADDON.ID = SALESORDERITEMMEMBERSHIPADDON.MEMBERSHIPADDONID
    left join dbo.SALESORDERITEMMEMBERSHIP on SALESORDERITEMMEMBERSHIP.ID = SALESORDERITEM.ID
    left join dbo.MEMBERSHIPTRANSACTION on SALESORDERITEMMEMBERSHIP.MEMBERSHIPTRANSACTIONID = MEMBERSHIPTRANSACTION.ID
    left join dbo.SALESORDERITEMDONATION on SALESORDERITEMDONATION.ID = SALESORDERITEM.ID
    left join dbo.SALESORDERITEMMERCHANDISE on SALESORDERITEMMERCHANDISE.ID = SALESORDERITEM.ID
    where
        SALESORDERITEM.SALESORDERID = @SALESORDERID
        and (SALESORDERITEM.TYPECODE in (0, 1, 2, 6, 14, 16) or (SALESORDERITEM.TYPECODE = 3 and SALESORDERITEMFEE.APPLIESTOCODE = 0));

    insert into @ITEMS -- itemized item-level fees, which as of 10/2013 only apply to ticket items.

    select 
        ITEM.ID SALESORDERITEMID,
        ITEM.[DESCRIPTION],
        SALESORDERITEMTICKETFEE.AMOUNT PRICE,
        0 DISCOUNTS,
        SALESORDERITEMTICKETFEE.AMOUNT AMOUNTPAID,
        3 TYPECODE,
        null PROGRAMID,
        case when COMBOITEM.ID is null then 0 else 1 end ISCOMBINATION,  -- Mark the fees applied to combos as also combos (for ordering purposes only)

        SALESORDERITEMTICKETFEE.ID ITEMID,
        null REVENUESPLITID,
        null MEMBERSHIPTRANSACTIONID,
        null EVENTID,
        null DESIGNATIONID,
        FEEITEM.FEEID,
        null MERCHANDISEPRODUCTINSTANCEID,
        null EVENTREGISTRANTID,
        0 HASCONTRIBUTEDREVENUE,
        coalesce((select [DESCRIPTION] + cast(ITEMID as nvarchar(36)) from @ITEMS where ITEMID = SALESORDERITEMTICKETFEE.TICKETID),'Z') APPLIESTOITEMSORTCRITERIA,
        null TICKETNUMBER,
        null PARENTITEMID
    from dbo.SALESORDERITEMTICKETFEE
    inner join dbo.SALESORDERITEMFEE FEEITEM on FEEITEM.ID = SALESORDERITEMTICKETFEE.SALESORDERITEMFEEID
    inner join dbo.SALESORDERITEM ITEM on ITEM.ID = FEEITEM.ID
    left join dbo.SALESORDERITEMTICKETCOMBINATION COMBOITEM on COMBOITEM.ID = FEEITEM.SALESORDERITEMID
    where ITEM.SALESORDERID = @SALESORDERID
        and SALESORDERITEMTICKETFEE.REFUNDEDAMOUNT = 0; -- Under current design, if the fee has been partially refunded we won't show it.


    /*
        Update the revenuesplitid field in a separate step since $0 sales order items don't
        generate a revenue split if they are the only item of that category (ie only ticket
        for that program). Due to this behavior, the above query would require more complex
        logic to detect this case. The performance impact is less strenuous if the revenue
        split field is updated here for items that do have an associated split.
    */
    update ITEMS
    set
        REVENUESPLITID = FTLI.ID
    from @ITEMS ITEMS
    inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = ITEMS.SALESORDERITEMID
    inner join dbo.SALESORDER on SALESORDER.ID = SALESORDERITEM.SALESORDERID
    left join dbo.EVENTREGISTRANTPAYMENT on ITEMS.EVENTREGISTRANTID = EVENTREGISTRANTPAYMENT.REGISTRANTID
    left join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.FINANCIALTRANSACTIONID = SALESORDER.REVENUEID
    left join dbo.REVENUESPLIT_EXT on FTLI.ID = REVENUESPLIT_EXT.ID
    left join dbo.REVENUESPLITORDER on FTLI.ID = REVENUESPLITORDER.ID
    -- Membership addons and memberships have already had their revenuesplits populated

    where ITEMS.REVENUESPLITID is null
        and (
            -- Tickets

            SALESORDERITEM.TYPECODE <> 0
            or (
                REVENUESPLITORDER.PROGRAMID = ITEMS.PROGRAMID
                and (ITEMS.EVENTID is null or REVENUESPLITORDER.EVENTID = ITEMS.EVENTID)
            )
        )
        and (
            -- Donations

            SALESORDERITEM.TYPECODE <> 2 or REVENUESPLIT_EXT.DESIGNATIONID = ITEMS.DESIGNATIONID
        )
        and (
            -- Fees

            SALESORDERITEM.TYPECODE <> 3 or REVENUESPLITORDER.FEEID = ITEMS.FEEID
        )
        and (
            -- Event registration

            SALESORDERITEM.TYPECODE <> 6 or EVENTREGISTRANTPAYMENT.PAYMENTID = FTLI.ID
        )
        and (
            -- Merchandise

            SALESORDERITEM.TYPECODE <> 14 or REVENUESPLITORDER.MERCHANDISEPRODUCTINSTANCEID = ITEMS.MERCHANDISEPRODUCTINSTANCEID
        );

    --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 @ITEMS 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.TYPECODE = 0  -- Gift type

            and REVENUESPLIT_EXT.APPLICATIONCODE in (0,1)  -- Gift or Event Registration application (membership contributions apply to "Donation", ER contributions apply to "Event Registration")

            and FTLI.TYPECODE = 0  -- Standard line items (exclude reversals, etc.)

        group by I.REVENUESPLITID
    )
    update @ITEMS
    set HASCONTRIBUTEDREVENUE = CTE.HASCONTRIBUTEDREVENUE
    from CONTRIBUTEDREVENUE_CTE CTE where CTE.ID = REVENUESPLITID;

    insert into @SALESORDERITEMS
    select
        SALESORDERITEMID,
        [DESCRIPTION],
        PRICE,
        -DISCOUNTS,
        AMOUNTPAID,
        TYPECODE,
        PROGRAMID,
        ISCOMBINATION,
        EVENTREGISTRANTID,
        ITEMID,
        REVENUESPLITID,
        HASCONTRIBUTEDREVENUE,
        TICKETNUMBER,
        left(case
            when TYPECODE = 3 and ITEMID is not null then '00'  -- group item-level fees with tickets

            when TYPECODE = 3 and ITEMID is null then '99'  -- order-level fees come last

            when TYPECODE = 16 then '01'  -- group add-ons with memberships

            else right('0' + cast(TYPECODE as nvarchar(2)), 2)
        end + case
            when ISCOMBINATION = 1 and TYPECODE = 0 then left([DESCRIPTION], 40) + cast(ITEMID as nvarchar(36)) + '0'
            when ISCOMBINATION = 1 and TYPECODE = 3 then APPLIESTOITEMSORTCRITERIA + '1'
            else 'Z'
        end + case  -- Combo and regular ticket sort clauses have to be separate so they don't intermingle.

            when ISCOMBINATION = 0 and TYPECODE = 0 then left([DESCRIPTION], 40) + cast(ITEMID as nvarchar(36)) + '0'
            when ISCOMBINATION = 0 and TYPECODE = 3 and ITEMID is not null then APPLIESTOITEMSORTCRITERIA + '1'  -- group each item-level fee with the specific ticket it applies to

            else 'Z'
        end + case
            when MEMBERSHIPTRANSACTIONID is not null then cast(MEMBERSHIPTRANSACTIONID as nvarchar(36))
            else 'Z'
        end + case
            -- list memberships before add-ons

            when TYPECODE = 1 then '1'
            when TYPECODE = 16 then '2'
            else '0'
        end + left([DESCRIPTION], 40), 200) as SORTFIELD,
        PARENTITEMID
    from @ITEMS;

    return;
end