USP_SALESORDER_DISTRIBUTEAPPLIEDDISCOUNTS

CREATE procedure dbo.USP_SALESORDER_DISTRIBUTEAPPLIEDDISCOUNTS (
    @SALESORDERID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTDATE datetime = null
)
as begin
    set nocount on;

    if (select ITEMDISCOUNTSCALCULATED from dbo.SALESORDER where ID = @SALESORDERID) = 1
        return;

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

    if @CURRENTDATE is null
        set @CURRENTDATE = getdate();

    -- First, we need a set of ID's representing individual pieces of merchandise. They only exist so far as aggregates, but we need them individualized before we can calculate.

    declare @MERCHANDISEUNITS table (SALESORDERITEMID uniqueidentifier, MERCHANDISEUNITID uniqueidentifier, PRICE money, ROWNUM int);

    insert into @MERCHANDISEUNITS
    select
        SALESORDERITEM.ID,
        newid(),
        SALESORDERITEM.PRICE,
        NUMBERS.NUM
    from dbo.SALESORDERITEM
    inner join dbo.NUMBERS on NUMBERS.NUM < SALESORDERITEM.QUANTITY
    where SALESORDERITEM.SALESORDERID = @SALESORDERID
        and SALESORDERITEM.TYPECODE = 14;

    -- This table will store all results of discount applications; in the end, we add them up per item and store to the TICKET table and SALESORDERITEMMERCHANDISEUNIT table.

    declare @PRORATEDITEMAMOUNTS table (SALESORDERITEMID uniqueidentifier, ITEMID uniqueidentifier, ORIGINALAMOUNT money, DISCOUNTEDAMOUNT money, ISITEMLEVEL bit, TYPECODE tinyint);

    -- Step 1: Item-level discounts.

    -- If any item discount has not been applied to all individual items represented by the SALESORDERITEM, then we have to do more work to ensure we only distribute across a subset of the item records.

    if exists (
        select 1
        from dbo.SALESORDERITEMITEMDISCOUNT ITEMDISCOUNT
        inner join dbo.SALESORDERITEM on ITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
        where SALESORDERITEM.SALESORDERID = @SALESORDERID
            and SALESORDERITEM.QUANTITY <> ITEMDISCOUNT.NUMBEROFDISCOUNTEDITEMS
    )
    begin
        -- Difficult case: some discount is not applied to all items.


        -- These CTE's match the tickets with which specific discount applies to them, enabling us to

        -- crank them through UFN_SPLITS_PRORATEAMOUNTS in a single non-iterative statement.

        with TICKETS_CTE as (
            select
                SALESORDERITEM.ID SALESORDERITEMID,
                TICKET.ID TICKETID,
                row_number() over (partition by SALESORDERITEM.ID order by TICKET.ID) ROWNUM
            from dbo.SALESORDERITEM
            inner join dbo.TICKET on TICKET.SALESORDERITEMTICKETID = SALESORDERITEM.ID
            where SALESORDERITEM.SALESORDERID = @SALESORDERID
                and SALESORDERITEM.TYPECODE = 0
        ),
        MERCHANDISE_CTE as (
            select
                SALESORDERITEM.ID SALESORDERITEMID,
                MERCHANDISE.MERCHANDISEUNITID,
                MERCHANDISE.ROWNUM
            from dbo.SALESORDERITEM
            inner join @MERCHANDISEUNITS MERCHANDISE on MERCHANDISE.SALESORDERITEMID = SALESORDERITEM.ID
        ),
        DISCOUNTS_CTE as (
            select
                ITEMDISCOUNT.SALESORDERITEMID SALESORDERITEMID,
                ITEMDISCOUNT.ID ITEMDISCOUNTID,
                ITEMDISCOUNT.AMOUNT TOTALAMOUNTDISCOUNTED,
                ITEMDISCOUNT.NUMBEROFDISCOUNTEDITEMS,
                row_number() over (partition by SALESORDERITEMID order by NUMBEROFDISCOUNTEDITEMS desc) ROWNUM
            from dbo.SALESORDERITEMITEMDISCOUNT ITEMDISCOUNT
            inner join dbo.NUMBERS on NUMBERS.NUM < ITEMDISCOUNT.NUMBEROFDISCOUNTEDITEMS
            where
                (select SALESORDERID from dbo.SALESORDERITEM where SALESORDERITEM.ID = ITEMDISCOUNT.SALESORDERITEMID) = @SALESORDERID
        )
        insert into @PRORATEDITEMAMOUNTS
        select SALESORDERITEM.ID, PRORATEDAMOUNTS.ID, SALESORDERITEM.PRICE, PRORATEDAMOUNTS.AMOUNT, 1, 0
        from dbo.SALESORDERITEM
        left join dbo.SALESORDERITEMITEMDISCOUNT ITEMDISCOUNT on ITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
        outer apply dbo.UFN_SPLITS_PRORATEAMOUNTS(
            SALESORDERITEM.PRICE * ITEMDISCOUNT.NUMBEROFDISCOUNTEDITEMS,
            ITEMDISCOUNT.AMOUNT,
            2,
            (
                select TICKETS_CTE.TICKETID ID, SALESORDERITEM.PRICE AMOUNT
                from TICKETS_CTE
                left join DISCOUNTS_CTE on TICKETS_CTE.SALESORDERITEMID = DISCOUNTS_CTE.SALESORDERITEMID and TICKETS_CTE.ROWNUM = DISCOUNTS_CTE.ROWNUM
                where DISCOUNTS_CTE.ITEMDISCOUNTID = ITEMDISCOUNT.ID
                for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64
            )
        ) PRORATEDAMOUNTS
        where SALESORDERITEM.SALESORDERID = @SALESORDERID
            and SALESORDERITEM.TYPECODE = 0

        union all

        select SALESORDERITEM.ID, PRORATEDAMOUNTS.ID, SALESORDERITEM.PRICE, PRORATEDAMOUNTS.AMOUNT, 1, 14
        from dbo.SALESORDERITEM
        left join dbo.SALESORDERITEMITEMDISCOUNT ITEMDISCOUNT on ITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
        outer apply dbo.UFN_SPLITS_PRORATEAMOUNTS(
            SALESORDERITEM.PRICE * ITEMDISCOUNT.NUMBEROFDISCOUNTEDITEMS,
            ITEMDISCOUNT.AMOUNT,
            2,
            (
                select MERCHANDISE_CTE.MERCHANDISEUNITID ID, SALESORDERITEM.PRICE AMOUNT
                from MERCHANDISE_CTE
                left join DISCOUNTS_CTE on MERCHANDISE_CTE.SALESORDERITEMID = DISCOUNTS_CTE.SALESORDERITEMID and MERCHANDISE_CTE.ROWNUM = DISCOUNTS_CTE.ROWNUM
                where DISCOUNTS_CTE.ITEMDISCOUNTID = ITEMDISCOUNT.ID
                for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64
            )
        ) PRORATEDAMOUNTS
        where SALESORDERITEM.SALESORDERID = @SALESORDERID
            and SALESORDERITEM.TYPECODE = 14;

    end
    else begin
        -- Easy case: there is at most one item discount per SALESORDERITEM, and it applies to all tickets represented by the item.


        -- Tickets

        insert into @PRORATEDITEMAMOUNTS
        select
            SALESORDERITEM.ID,
            PRORATEDAMOUNTS.ID,
            SALESORDERITEM.PRICE,
            PRORATEDAMOUNTS.AMOUNT,
            1,
            0
        from dbo.SALESORDERITEM
        inner join dbo.SALESORDERITEMITEMDISCOUNT on SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
        outer apply dbo.UFN_SPLITS_PRORATEAMOUNTS(
            SALESORDERITEM.QUANTITY * SALESORDERITEM.PRICE,
            SALESORDERITEMITEMDISCOUNT.AMOUNT,
            2,
            (
                select T.ID, T.PRICE as AMOUNT
                from dbo.TICKET T
                where T.SALESORDERITEMTICKETID = SALESORDERITEM.ID
                for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64
            )
        ) PRORATEDAMOUNTS
        where SALESORDERITEM.SALESORDERID = @SALESORDERID
            and SALESORDERITEM.TYPECODE = 0;

        -- Merchandise

        insert into @PRORATEDITEMAMOUNTS
        select
            SALESORDERITEM.ID,
            PRORATEDAMOUNTS.ID,
            SALESORDERITEM.PRICE,
            PRORATEDAMOUNTS.AMOUNT,
            1,
            14
        from dbo.SALESORDERITEM
        inner join dbo.SALESORDERITEMITEMDISCOUNT on SALESORDERITEMITEMDISCOUNT.SALESORDERITEMID = SALESORDERITEM.ID
        outer apply dbo.UFN_SPLITS_PRORATEAMOUNTS(
            SALESORDERITEM.QUANTITY * SALESORDERITEM.PRICE,
            SALESORDERITEMITEMDISCOUNT.AMOUNT,
            2,
            (
                select MERCHANDISE.MERCHANDISEUNITID ID, MERCHANDISE.PRICE as AMOUNT
                from @MERCHANDISEUNITS MERCHANDISE
                where MERCHANDISE.SALESORDERITEMID = SALESORDERITEM.ID
                for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64
            )
        ) PRORATEDAMOUNTS
        where SALESORDERITEM.SALESORDERID = @SALESORDERID
            and SALESORDERITEM.TYPECODE = 14;
    end;

    -- Step 2: Order-level discounts (including Adjustable)

    with ITEMDISCOUNTS_CTE as (
        select
            SALESORDERITEMID,
            sum(DISCOUNTEDAMOUNT) as AMOUNT
        from @PRORATEDITEMAMOUNTS
        group by SALESORDERITEMID
    )
    insert into @PRORATEDITEMAMOUNTS
    select
        SALESORDERITEM.ID,
        PRORATEDAMOUNTS.ID,
        SALESORDERITEM.PRICE,
        PRORATEDAMOUNTS.AMOUNT,
        0,
        0
    from dbo.SALESORDERITEM
    inner join dbo.SALESORDERITEMORDERDISCOUNTDETAIL ORDERDISCOUNTPERITEM on ORDERDISCOUNTPERITEM.SALESORDERITEMID = SALESORDERITEM.ID
    left join ITEMDISCOUNTS_CTE DISCOUNTSALREADYDISTRIBUTED on DISCOUNTSALREADYDISTRIBUTED.SALESORDERITEMID = SALESORDERITEM.ID
    cross apply dbo.UFN_SPLITS_PRORATEAMOUNTS(
        SALESORDERITEM.QUANTITY * SALESORDERITEM.PRICE - coalesce(DISCOUNTSALREADYDISTRIBUTED.AMOUNT, 0),
        ORDERDISCOUNTPERITEM.AMOUNT,
        2,
        (
            select
                T.ID,
                (T.PRICE - coalesce(DISCOUNTAMOUNTS.DISCOUNTEDAMOUNT, 0)) as AMOUNT
            from dbo.TICKET T
            left join @PRORATEDITEMAMOUNTS DISCOUNTAMOUNTS on DISCOUNTAMOUNTS.ITEMID = T.ID
            where T.SALESORDERITEMTICKETID = SALESORDERITEM.ID
            for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64
        )
    ) PRORATEDAMOUNTS
    where SALESORDERITEM.SALESORDERID = @SALESORDERID
        and SALESORDERITEM.TYPECODE = 0

    union all

    -- Merchandise

    select
        SALESORDERITEM.ID,
        PRORATEDAMOUNTS.ID,
        SALESORDERITEM.PRICE,
        PRORATEDAMOUNTS.AMOUNT,
        0,
        14
    from dbo.SALESORDERITEM
    inner join dbo.SALESORDERITEMORDERDISCOUNTDETAIL ORDERDISCOUNTPERITEM on ORDERDISCOUNTPERITEM.SALESORDERITEMID = SALESORDERITEM.ID
    left join ITEMDISCOUNTS_CTE DISCOUNTSALREADYDISTRIBUTED on DISCOUNTSALREADYDISTRIBUTED.SALESORDERITEMID = SALESORDERITEM.ID
    cross apply dbo.UFN_SPLITS_PRORATEAMOUNTS(
        SALESORDERITEM.QUANTITY * SALESORDERITEM.PRICE - coalesce(DISCOUNTSALREADYDISTRIBUTED.AMOUNT, 0),
        ORDERDISCOUNTPERITEM.AMOUNT,
        2,
        (
            select
                MERCHANDISE.MERCHANDISEUNITID ID,
                (SALESORDERITEM.PRICE - coalesce(DISCOUNTAMOUNTS.DISCOUNTEDAMOUNT, 0)) as AMOUNT
            from @MERCHANDISEUNITS MERCHANDISE
            left join @PRORATEDITEMAMOUNTS DISCOUNTAMOUNTS on DISCOUNTAMOUNTS.ITEMID = MERCHANDISE.MERCHANDISEUNITID
            where MERCHANDISE.SALESORDERITEMID = SALESORDERITEM.ID
            for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64
        )
    ) PRORATEDAMOUNTS
    where SALESORDERITEM.SALESORDERID = @SALESORDERID
        and SALESORDERITEM.TYPECODE = 14;


    -- Store calculations directly on the TICKET record

    update dbo.TICKET set
        AMOUNTPAID = TICKET.PRICE - coalesce(TICKETDISCOUNTS.ITEMLEVELDISCOUNTEDAMOUNT + TICKETDISCOUNTS.ORDERLEVELDISCOUNTEDAMOUNT, 0),
        ITEMLEVELDISCOUNTSAPPLIED = coalesce(TICKETDISCOUNTS.ITEMLEVELDISCOUNTEDAMOUNT, 0),
        ORDERLEVELDISCOUNTSAPPLIED = coalesce(TICKETDISCOUNTS.ORDERLEVELDISCOUNTEDAMOUNT, 0),
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    from dbo.TICKET
    inner join dbo.SALESORDERITEM on SALESORDERITEM.ID = TICKET.SALESORDERITEMTICKETID
    left join (
        select
            ITEMID,
            sum(case when ISITEMLEVEL = 1 then DISCOUNTEDAMOUNT else 0 end) ITEMLEVELDISCOUNTEDAMOUNT,
            sum(case when ISITEMLEVEL = 0 then DISCOUNTEDAMOUNT else 0 end) ORDERLEVELDISCOUNTEDAMOUNT
        from @PRORATEDITEMAMOUNTS
        where TYPECODE = 0
        group by ITEMID
    ) TICKETDISCOUNTS on TICKET.ID = TICKETDISCOUNTS.ITEMID
    where SALESORDERITEM.SALESORDERID = @SALESORDERID;


    insert into dbo.SALESORDERITEMMERCHANDISEUNIT
        (ID, SALESORDERITEMMERCHANDISEID, AMOUNTPAID, ITEMLEVELDISCOUNTSAPPLIED, ORDERLEVELDISCOUNTSAPPLIED, REFUNDEDAMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select
        MERCHANDISE.MERCHANDISEUNITID,
        MERCHANDISE.SALESORDERITEMID,
        MERCHANDISE.PRICE - coalesce(APPLIEDDISCOUNTS.ITEMLEVELDISCOUNTEDAMOUNT, 0) - coalesce(APPLIEDDISCOUNTS.ORDERLEVELDISCOUNTEDAMOUNT, 0),
        coalesce(APPLIEDDISCOUNTS.ITEMLEVELDISCOUNTEDAMOUNT, 0),
        coalesce(APPLIEDDISCOUNTS.ORDERLEVELDISCOUNTEDAMOUNT, 0),
        0,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from @MERCHANDISEUNITS MERCHANDISE
    left join (
        select
            ITEMID,
            coalesce(sum(case when ISITEMLEVEL = 1 then DISCOUNTEDAMOUNT else 0 end), 0) ITEMLEVELDISCOUNTEDAMOUNT,
            coalesce(sum(case when ISITEMLEVEL = 0 then DISCOUNTEDAMOUNT else 0 end), 0) ORDERLEVELDISCOUNTEDAMOUNT
        from @PRORATEDITEMAMOUNTS
        where TYPECODE = 14
        group by ITEMID
    ) APPLIEDDISCOUNTS on MERCHANDISE.MERCHANDISEUNITID = APPLIEDDISCOUNTS.ITEMID;

    -- Mark the order as having its discounts distributed to prevent recalculating them later (which could calculate differently and cause data corruption).

    update dbo.SALESORDER
    set
        ITEMDISCOUNTSCALCULATED = 1,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    where ID = @SALESORDERID;
end