UFN_CREDIT_GETSALESORDERITEMS

CREATE function dbo.UFN_CREDIT_GETSALESORDERITEMS
(
    @SALESORDERID uniqueidentifier
) returns @SALESORDERITEMS table
(
    SALESORDERITEMID uniqueidentifier,
    DESCRIPTION nvarchar(510),
    QUANTITY decimal(20,4),
    QUANTITYREFUNDING int,
    PRICE money,
    FEES money,
    TOTAL money,
    REFUNDTOTAL money,
    DISCOUNTS money,
    INCLUDE bit,
    DISCOUNTED bit,
    TYPECODE int,
    PROGRAMID uniqueidentifier,  --program id for tickets, merchandise department id for merch

    ISCOMBINATION bit,
    GROUPID uniqueidentifier,
    GROUPTYPECODE tinyint,
    ITEMID uniqueidentifier,
    REVENUESPLITID uniqueidentifier,
    HASCONTRIBUTEDREVENUE bit
)
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),
        QUANTITY decimal(20,4),
        QUANTITYREFUNDING int,
        PRICE money,
        FEES money,
        TOTAL money,
        REFUNDTOTAL money,
        DISCOUNTS money,
        INCLUDE bit,
        DISCOUNTED bit,
        TYPECODE int,
        PROGRAMID uniqueidentifier,
        ISCOMBINATION bit,
        GROUPID uniqueidentifier,
        GROUPTYPECODE tinyint,
        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,
        REGISTRANTID uniqueidentifier,
        /* end linking back fields */
        HASCONTRIBUTEDREVENUE bit
    )

    declare @CALCULATEDFIELDS table (
        [SALESORDERITEMID] uniqueidentifier,
        [QUANTITY] decimal(20,4),
        [FEES] money,
        [DISCOUNTS] money,
        [PROGRAMID] uniqueidentifier -- program id for tickets, merchandise department id for merch

    );

    insert into @CALCULATEDFIELDS
    select 
        [SALESORDERITEM].[ID],
        case
            when [SALESORDERITEM].[TYPECODE] = 0 then
                [SALESORDERITEM].[QUANTITY] - coalesce(
                    (
                        select count(*)
                        from dbo.[TICKET]
                        where [TICKET].[SALESORDERITEMTICKETID] = [SALESORDERITEM].[ID] 
                        and ([TICKET].[STATUSCODE] not in (0,1) or [TICKET].[APPLIEDTOMEMBERSHIP] = 1)
                    ), 0)
            else
                [SALESORDERITEM].[QUANTITY] - REFUNDTOTALS.QUANTITY
        end as [QUANTITY],
        FEESAMOUNT.AMOUNT - REFUNDTOTALS.FEES as [FEES],
        case SALESORDERITEM.TYPECODE
            when 1 then
                isnull(MEMBERSHIPPROMOAMOUNT.AMOUNT, 0) + SALESORDERITEMMEMBERSHIPITEMPROMOTIONAMOUNT.AMOUNT
            else (
                select coalesce(sum([AMOUNT]),0.0)
                from dbo.[SALESORDERITEMITEMDISCOUNT]
                where [SALESORDERITEMID] = [SALESORDERITEM].[ID]
            )
        end as [DISCOUNTS],
        case [SALESORDERITEM].[TYPECODE]
            when 0 then--tickets

                [SALESORDERITEMTICKET].[PROGRAMID]
            when 14 then --merchandise

                [SALESORDERITEMMERCHANDISE].[MERCHANDISEDEPARTMENTID]
            else
                null
        end [PROGRAMID]
    from dbo.[SALESORDERITEM]
    left join dbo.[SALESORDERITEMTICKET] 
        on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
    left join dbo.[SALESORDERITEMMERCHANDISE]
        on [SALESORDERITEM].[ID] = [SALESORDERITEMMERCHANDISE].[ID]
    outer apply (
        select isnull(sum(FEEITEM.TOTAL), 0) as AMOUNT
        from dbo.SALESORDERITEMFEE
        inner join dbo.SALESORDERITEM as FEEITEM on FEEITEM.ID = SALESORDERITEMFEE.ID
        where SALESORDERITEMFEE.SALESORDERITEMID = SALESORDERITEM.ID
    ) as FEESAMOUNT
    outer apply (
        select
            isnull(sum(LI.QUANTITY), 0) as QUANTITY,
            isnull(sum(EXT.FEES), 0) as FEES
        from dbo.FINANCIALTRANSACTIONLINEITEM as LI
        inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
        where EXT.SALESORDERITEMID = SALESORDERITEM.ID
    ) as REFUNDTOTALS
    outer apply (
        select
            case
                when MEMBERSHIPPROMOITEM.[PERCENT] > 0 then
                    SALESORDERITEM.TOTAL * MEMBERSHIPPROMOITEM.[PERCENT] * 0.01
                else
                    MEMBERSHIPPROMOITEM.PRICE
            end as 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

    declare @DISCOUNTEDPROGRAMS dbo.UDT_GENERICID;

    insert into @DISCOUNTEDPROGRAMS
    select distinct
        coalesce([SALESORDERITEMTICKET].[PROGRAMID], [SALESORDERITEMMERCHANDISE].[MERCHANDISEDEPARTMENTID])
    from dbo.[SALESORDERITEM]
    left join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEM].[ID]
    inner join @CALCULATEDFIELDS as [CALCULATEDFIELDS] on [CALCULATEDFIELDS].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
    left join dbo.[SALESORDERITEMMERCHANDISE] on [SALESORDERITEM].[ID] = [SALESORDERITEMMERCHANDISE].[ID]
    where [SALESORDERID] = @SALESORDERID
    and [CALCULATEDFIELDS].[DISCOUNTS] > 0
    and (SALESORDERITEMTICKET.PROGRAMID is not null or SALESORDERITEMMERCHANDISE.MERCHANDISEDEPARTMENTID is not null);

    --Select only valid items that belong to this order

    insert into @ITEMS
    select
        [SALESORDERITEM].[ID],
        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],
        [CALCULATEDFIELDS].[QUANTITY],
        0 as [QUANTITYREFUNDING],
        [SALESORDERITEM].[PRICE],
        [CALCULATEDFIELDS].[FEES] / [CALCULATEDFIELDS].[QUANTITY],
        ([CALCULATEDFIELDS].[QUANTITY] * [SALESORDERITEM].[PRICE]) + [CALCULATEDFIELDS].[FEES] - [CALCULATEDFIELDS].[DISCOUNTS] as [TOTAL],
        0 as [REFUNDTOTAL],
        [CALCULATEDFIELDS].[DISCOUNTS],
        0,
        case 
            when [CALCULATEDFIELDS].[PROGRAMID] in
                (select ID from @DISCOUNTEDPROGRAMS) or
                (select sum([AMOUNT]) from dbo.[SALESORDERITEMORDERDISCOUNTDETAIL] where[SALESORDERITEMORDERDISCOUNTDETAIL].[SALESORDERITEMID] = [SALESORDERITEM].[ID]) > 0
            then
                1
            when [SALESORDERITEM].[TYPECODE] = 1 and [CALCULATEDFIELDS].[DISCOUNTS] > 0
                then 1
            else
                0
        end [DISCOUNTED],
        [SALESORDERITEM].[TYPECODE],
        [CALCULATEDFIELDS].[PROGRAMID],
        0,
        case
            when [SALESORDERITEM].[TYPECODE] = 6
            then
                [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID]
            else
                null
        end [GROUPID],
        case 
            when [SALESORDERITEM].[TYPECODE] = 6
            then
                1
            else
                0
        end [GROUPTYPECODE],
        case
            when [SALESORDERITEM].[TYPECODE] = 16 then
                [SALESORDERITEMMEMBERSHIPADDON].[MEMBERSHIPADDONID]
            when [SALESORDERITEM].[TYPECODE] = 1 then
                [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPTRANSACTIONID]
            else
                null
        end [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,
        0 as HASCONTRIBUTEDREVENUE
    from dbo.[SALESORDERITEM]
    inner join dbo.SALESORDER
        on SALESORDER.ID = SALESORDERITEM.SALESORDERID
    inner join @CALCULATEDFIELDS as [CALCULATEDFIELDS] 
        on [SALESORDERITEM].[ID] = [CALCULATEDFIELDS].[SALESORDERITEMID]
    left outer join dbo.[SALESORDERITEMTICKET] 
        on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
    left outer join dbo.[SALESORDERITEMTICKETCOMBINATION] 
        on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
    left outer join dbo.[EVENT] 
        on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
    left outer join [dbo].[SALESORDERITEMFEE] 
        on [dbo].[SALESORDERITEMFEE].[ID] = [dbo].[SALESORDERITEM].[ID]
    left outer join dbo.[SALESORDERITEMEVENTREGISTRATION] 
        on [SALESORDERITEM].[ID] = [SALESORDERITEMEVENTREGISTRATION].[ID]
    left outer join dbo.[REGISTRANT]
        on [REGISTRANT].ID = SALESORDERITEMEVENTREGISTRATION.REGISTRANTID
    left outer join dbo.[SALESORDERITEMMEMBERSHIPADDON] 
        on [SALESORDERITEMMEMBERSHIPADDON].[ID] = [SALESORDERITEM].[ID]
    left outer join dbo.[MEMBERSHIPADDON] 
        on [MEMBERSHIPADDON].[ID] = [SALESORDERITEMMEMBERSHIPADDON].[MEMBERSHIPADDONID]
    left outer join dbo.SALESORDERITEMMEMBERSHIP
        on SALESORDERITEMMEMBERSHIP.ID = SALESORDERITEM.ID
    left outer join dbo.MEMBERSHIPTRANSACTION
        on SALESORDERITEMMEMBERSHIP.MEMBERSHIPTRANSACTIONID = MEMBERSHIPTRANSACTION.ID
    left outer join dbo.SALESORDERITEMDONATION
        on SALESORDERITEMDONATION.ID = SALESORDERITEM.ID
    left outer join dbo.SALESORDERITEMMERCHANDISE 
        on SALESORDERITEMMERCHANDISE.ID = SALESORDERITEM.ID
    where
        [SALESORDER].ID = @SALESORDERID
        and [SALESORDERITEMTICKETCOMBINATION].[ID] is null
        and (dbo.UFN_CREDIT_ISVALIDREFUNDTYPE([SALESORDERITEM].[TYPECODE], [SALESORDERITEMFEE].[APPLIESTOCODE]) = 1)
        and [CALCULATEDFIELDS].QUANTITY  > 0

    union all
    -- select combination items in the order

    select
        SALESORDERITEM.ID as [ID],
        [COMBINATION].[NAME] + ' - ' + [PRICETYPECODE].[DESCRIPTION] + ' - ' +
        stuff((select ';' + case
                                when [ITEMTICKET].PROGRAMID is not null then P.NAME 
                                else E.NAME + ' ' + convert(nvarchar(8),e.[STARTDATE], 1) + ' ' + dbo.UFN_HOURMINUTE_DISPLAYTIME(e.[STARTTIME])
                            end
                from dbo.[SALESORDERITEM] [ITEM]
                    inner join dbo.[SALESORDERITEMTICKET] [ITEMTICKET] on [ITEM].[ID] = [ITEMTICKET].[ID]
                    inner join dbo.[SALESORDERITEMTICKETCOMBINATION] [TICKETCOMBINATION] on [ITEMTICKET].[ID] = [TICKETCOMBINATION].[ID]
                    left join dbo.[PROGRAM] P on ([ITEMTICKET].[PROGRAMID] = P.[ID] and [ITEMTICKET].[PROGRAMID] is not null)
                    left join dbo.[EVENT] E on ([ITEMTICKET].[EVENTID] = E.[ID] and [ITEMTICKET].[EVENTID] is not null)
                where [TICKETCOMBINATION].TICKETCOMBINATIONID = [SALESORDERITEMTICKETCOMBINATION].TICKETCOMBINATIONID
                        and ITEMTICKET.PRICETYPECODEID = SALESORDERITEMTICKET.PRICETYPECODEID 
                order by 1 asc -- this guarantees that programs will be in the same order every time

                for xml path('')), 1, 1, '')
        as [DESCRIPTION],
        [CALCULATEDFIELDS].[QUANTITY] as [QUANTITY],
        0 as [QUANTITYREFUNDING],
        dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE([SALESORDERITEM].[ID]),
        (dbo.UFN_SALESORDER_GETTICKETCOMBINATIONFEES([SALESORDERITEM].[ID]) - dbo.UFN_SALESORDER_GETTICKETCOMBINATIONFEESREFUNDED([SALESORDERITEM].[ID])) / [CALCULATEDFIELDS].[QUANTITY],
        [CALCULATEDFIELDS].[QUANTITY] * dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE([SALESORDERITEM].[ID]) + dbo.UFN_SALESORDER_GETTICKETCOMBINATIONFEES([SALESORDERITEM].[ID]) - dbo.UFN_SALESORDER_GETTICKETCOMBINATIONFEESREFUNDED([SALESORDERITEM].[ID]),
        0 as [REFUNDTOTAL],
        0,
        0,
        -- combination only is discounted when there is order level discount

        case 
            when exists (select * from dbo.SALESORDERITEMORDERDISCOUNTDETAIL where SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID = SALESORDERITEM.ID) then
                1
            else
                0
        end [DISCOUNTED],
        [SALESORDERITEM].[TYPECODE],
        isnull(SALESORDERITEMTICKET.PROGRAMID, SALESORDERITEMTICKET.EVENTID),
        1,
        null,
        0,
        null as [ITEMID],
        null as [REVENUESPLITID],
        null as [MEMBERSHIPTRANSACTIONID],
        SALESORDERITEMTICKET.EVENTID,
        null as DESIGNATIONID,
        null as FEEID,
        null as MERCHANDISEPRODUCTINSTANCEID,
        null as REGISTRANTID,
        0 as HASCONTRIBUTEDREVENUE
    from dbo.[SALESORDERITEM]
    inner join @CALCULATEDFIELDS as [CALCULATEDFIELDS] on [SALESORDERITEM].[ID] = [CALCULATEDFIELDS].[SALESORDERITEMID]
    inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
    inner join dbo.[SALESORDERITEMTICKETCOMBINATION] on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
    inner join dbo.[COMBINATION] on [SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID] = [COMBINATION].[ID]
    inner join dbo.[PRICETYPECODE] on [SALESORDERITEMTICKET].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
    where
        [SALESORDERITEM].[SALESORDERID] = @SALESORDERID
        and [SALESORDERITEM].[QUANTITY] > 0
        and [SALESORDERITEMTICKETCOMBINATION].PROGRAMGROUPID in (
            -- Selecting top 1 is important here because we only want to match one program group

            -- for the refund form to work correctly.

            select top 1 ID
            from dbo.PROGRAMGROUP
            where PROGRAMGROUP.COMBINATIONID = [SALESORDERITEMTICKETCOMBINATION].COMBINATIONID
        )
        and [CALCULATEDFIELDS].QUANTITY  > 0

    /*    
        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 = REVENUESPLIT.ID
    from @ITEMS ITEMS
    inner join dbo.SALESORDERITEM
        on SALESORDERITEM.ID = ITEMS.SALESORDERITEMID
    inner join dbo.SALESORDER
        on SALESORDER.ID = SALESORDERITEM.SALESORDERID
    left outer join dbo.EVENTREGISTRANTPAYMENT
        on ITEMS.REGISTRANTID = EVENTREGISTRANTPAYMENT.REGISTRANTID
    left outer join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
        on REVENUESPLIT.FINANCIALTRANSACTIONID = SALESORDER.REVENUEID
    left outer join dbo.REVENUESPLIT_EXT
        on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
    left outer join dbo.REVENUESPLITORDER
        on REVENUESPLIT.ID = REVENUESPLITORDER.ID
    -- Membership addons and memberships have already had their revenuespilts 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 = REVENUESPLIT.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.APPLICATIONCODE = 0 --only donations

            and FTLI.TYPECODE = 0 --only standard ft line items

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

    insert into @SALESORDERITEMS
        select
            SALESORDERITEMID,
            DESCRIPTION,
            QUANTITY,
            QUANTITYREFUNDING,
            PRICE,
            FEES,
            TOTAL,
            REFUNDTOTAL,
            DISCOUNTS,
            INCLUDE,
            DISCOUNTED,
            TYPECODE,
            PROGRAMID,
            ISCOMBINATION,
            GROUPID,
            GROUPTYPECODE,
            ITEMID,
            REVENUESPLITID,
            HASCONTRIBUTEDREVENUE
        from @ITEMS
        order by
            case
                when TYPECODE = 16 then 1  --group add-ons with memberships

                else TYPECODE
            end,
            MEMBERSHIPTRANSACTIONID,
            case  --list memberships before add-ons

                when TYPECODE = 1 then 1
                when TYPECODE = 16 then 2
            end,
            PROGRAMID,
            DESCRIPTION;

    return;
end