USP_SALESORDER_APPLYITEMDISCOUNTS_GROUPSIZEDISCOUNTS

CREATE procedure dbo.USP_SALESORDER_APPLYITEMDISCOUNTS_GROUPSIZEDISCOUNTS
(
    @SALESORDERID uniqueidentifier,
    @DISCOUNTOPTIONID uniqueidentifier,
    @SALESORDERAVAILABLEDISCOUNTS UDT_APPLYDISCOUNTS_SALESORDERAVAILABLEDISCOUNT readonly,
    @DISCOUNTORDERITEMS UDT_APPLYDISCOUNTS_DISCOUNTORDERITEM readonly,
    @CHANGEAGENTID uniqueidentifier,
    @SALESORDERITEMITEMDISCOUNTOPTION xml output,
    @DISCOUNTORDERITEMSXML_BYGROUP xml
)
as
    set nocount on;

    declare @GROUPOPTIONS table (
        [ID] uniqueidentifier,
        [SALESORDERITEMID] uniqueidentifier,
        [DISCOUNTID] uniqueidentifier,
        [DISCOUNTNAME] nvarchar(255),
        [DISCOUNTAMOUNT] money,
        [SALESORDERITEMAMOUNT] money,
        [APPLIESTOTICKETS] bit,
        [NUMBEROFDISCOUNTEDITEMS] integer
    );

    declare @GROUPDISCOUNTORDERITEMSTABLE table (
        ORDERITEMID uniqueidentifier,
        DISCOUNTID uniqueidentifier,
        DISCOUNTNAME nvarchar(100),
        MERCHANDISEPRODUCTID uniqueidentifier,
        QUANTITY decimal(5,2),
        UNITPRICE money,
        CALCULATIONTYPECODE tinyint
    )

    insert into @GROUPDISCOUNTORDERITEMSTABLE
    select
        DISCOUNTORDERITEMSXML.item.value('(@ORDERITEMID)[1]', 'uniqueidentifier'),
        DISCOUNTORDERITEMSXML.item.value('(@DISCOUNTID)[1]', 'uniqueidentifier'),
        DISCOUNTORDERITEMSXML.item.value('(@DISCOUNTNAME)[1]', 'nvarchar(100)'),
        DISCOUNTORDERITEMSXML.item.value('(@MERCHANDISEPRODUCTID)[1]', 'uniqueidentifier'),
        isnull(DISCOUNTORDERITEMSXML.item.value('(@QUANTITY)[1]', 'decimal(20,4)'), 0),
        DISCOUNTORDERITEMSXML.item.value('(@UNITPRICE)[1]', 'money'),
        DISCOUNTORDERITEMSXML.item.value('(@CALCULATIONTYPECODE)[1]', 'tinyint')
    from @DISCOUNTORDERITEMSXML_BYGROUP.nodes('/DISCOUNTORDERITEMSXML_BYGROUP/ITEM') DISCOUNTORDERITEMSXML(item);

    with GROUPED_CTE as
    (
        select
            DISCOUNTID,
            MERCHANDISEPRODUCTID,
            sum(QUANTITY) GROUPSIZE
        from @GROUPDISCOUNTORDERITEMSTABLE
        group by DISCOUNTID, MERCHANDISEPRODUCTID
    )
    insert into @GROUPOPTIONS
    (
        [ID],
        [SALESORDERITEMID],
        [DISCOUNTID],
        [DISCOUNTNAME],
        [DISCOUNTAMOUNT],
        [SALESORDERITEMAMOUNT],
        [APPLIESTOTICKETS],
        [NUMBEROFDISCOUNTEDITEMS]
    )
    select
        newid(),
        GROUPDISCOUNTORDERITEMSTABLE.ORDERITEMID,
        GROUPDISCOUNTORDERITEMSTABLE.DISCOUNTID,
        GROUPDISCOUNTORDERITEMSTABLE.DISCOUNTNAME,
        case [DISCOUNTORDERITEMS].[CALCULATIONTYPECODE]
            when 0 then
                [DISCOUNTORDERITEMS].[NUMBEROFDISCOUNTEDITEMS] * [GROUPSIZEDISCOUNT].[AMOUNT]
            when 1 then
                [DISCOUNTORDERITEMS].[NUMBEROFDISCOUNTEDITEMS] * ([GROUPSIZEDISCOUNT].[PERCENT] * 0.01) * [DISCOUNTORDERITEMS].[UNITPRICE]
            when 2 then
                case
                    when [GROUPSIZEDISCOUNT].[AMOUNT] >= [DISCOUNTORDERITEMS].[UNITPRICE] then
                        0
                    else
                        [DISCOUNTORDERITEMS].[NUMBEROFDISCOUNTEDITEMS] * ([DISCOUNTORDERITEMS].[UNITPRICE] - [GROUPSIZEDISCOUNT].[AMOUNT])
                end
        end [DISCOUNTAMOUNT],
        GROUPDISCOUNTORDERITEMSTABLE.UNITPRICE * GROUPDISCOUNTORDERITEMSTABLE.QUANTITY as [SALESORDERITEMAMOUNT],
        0,
        [DISCOUNTORDERITEMS].[NUMBEROFDISCOUNTEDITEMS]
    from @GROUPDISCOUNTORDERITEMSTABLE as GROUPDISCOUNTORDERITEMSTABLE
    cross apply (
        select 
            GROUPDISCOUNTORDERITEMSTABLE.QUANTITY as NUMBEROFDISCOUNTEDITEMS,
            GROUPDISCOUNTORDERITEMSTABLE.CALCULATIONTYPECODE,
            GROUPDISCOUNTORDERITEMSTABLE.UNITPRICE
    ) [DISCOUNTORDERITEMS]
    inner join GROUPED_CTE GROUPED on
        GROUPED.MERCHANDISEPRODUCTID = GROUPDISCOUNTORDERITEMSTABLE.MERCHANDISEPRODUCTID
    inner join [dbo].[GROUPSIZEDISCOUNT]
        on [GROUPSIZEDISCOUNT].[DISCOUNTID] = GROUPDISCOUNTORDERITEMSTABLE.DISCOUNTID
            and [GROUPSIZEDISCOUNT].[ID] in
            (select top(1) [ID]
            from [dbo].[GROUPSIZEDISCOUNT]
            where GROUPED.[GROUPSIZE] >= [GROUPSIZEDISCOUNT].[GROUPSIZE]
            and [GROUPSIZEDISCOUNT].[DISCOUNTID] = GROUPDISCOUNTORDERITEMSTABLE.DISCOUNTID
            order by [GROUPSIZEDISCOUNT].[GROUPSIZE] desc);

    with GROUPDISCOUNTORDERITEMS_CTE as
    (
        select
            [PROGRAMID],
            sum(coalesce([DOI].[QUANTITY], 0)) as [GROUPSIZE]
        from @DISCOUNTORDERITEMS [DOI]
        group by [PROGRAMID]
    )
    insert into @GROUPOPTIONS
    (
        [ID],
        [SALESORDERITEMID],
        [DISCOUNTID],
        [DISCOUNTNAME],
        [DISCOUNTAMOUNT],
        [SALESORDERITEMAMOUNT],
        [APPLIESTOTICKETS],
        [NUMBEROFDISCOUNTEDITEMS]
    )
    select
        newid(),
        [DISCOUNTORDERITEMS].[ORDERITEMID],
        [DISCOUNT].[ID],
        coalesce([DISCOUNT].[NAME],''),
        case [DISCOUNT].[CALCULATIONTYPECODE]
            when 0 then
                [DISCOUNTORDERITEMS].[QUANTITY] * [GROUPSIZEDISCOUNT].[AMOUNT]
            when 1 then
                [DISCOUNTORDERITEMS].[QUANTITY] * ([GROUPSIZEDISCOUNT].[PERCENT] * 0.01) * [DISCOUNTORDERITEMS].[UNITPRICE]
            when 2 then
                case
                    when [GROUPSIZEDISCOUNT].[AMOUNT] >= [DISCOUNTORDERITEMS].[UNITPRICE] then
                        0
                    else
                        [DISCOUNTORDERITEMS].[QUANTITY] * ([DISCOUNTORDERITEMS].[UNITPRICE] - [GROUPSIZEDISCOUNT].[AMOUNT])
                end
        end [DISCOUNTAMOUNT],
        [DISCOUNTORDERITEMS].[UNITPRICE] * [DISCOUNTORDERITEMS].[QUANTITY] as [SALESORDERITEMAMOUNT],
        1,
        [DISCOUNTORDERITEMS].[QUANTITY] as [NUMBEROFDISCOUNTEDITEMS]
    from
        @DISCOUNTORDERITEMS [DISCOUNTORDERITEMS]
    inner join [dbo].[DISCOUNTGROUPDETAILPROGRAM]
        on [DISCOUNTGROUPDETAILPROGRAM].[PROGRAMID] = [DISCOUNTORDERITEMS].[PROGRAMID]
    inner join [dbo].[DISCOUNTGROUPDETAIL]
        on [DISCOUNTGROUPDETAIL].[ID] = [DISCOUNTGROUPDETAILPROGRAM].[ID]
    inner join [dbo].[DISCOUNTGROUP]
        on [DISCOUNTGROUP].[ID] = [DISCOUNTGROUPDETAIL].[DISCOUNTGROUPID]
    inner join [dbo].[DISCOUNT]
        on [DISCOUNTGROUP].[DISCOUNTID] = [DISCOUNT].[ID]
    inner join @SALESORDERAVAILABLEDISCOUNTS [SOAD]
        on [SOAD].[DISCOUNTID] = [DISCOUNT].[ID]  -- all programs that are associated with this discount

    inner join [GROUPDISCOUNTORDERITEMS_CTE] as [GROUPDISCOUNTORDERITEMS]
        on [GROUPDISCOUNTORDERITEMS].[PROGRAMID] = [DISCOUNTORDERITEMS].[PROGRAMID]
    inner join [dbo].[GROUPSIZEDISCOUNT]
        on [GROUPSIZEDISCOUNT].[DISCOUNTID] = [DISCOUNT].[ID]
        and [GROUPSIZEDISCOUNT].[ID] in
            (select top(1) [ID]
                from [dbo].[GROUPSIZEDISCOUNT]
                where [GROUPDISCOUNTORDERITEMS].[GROUPSIZE] >= [GROUPSIZEDISCOUNT].[GROUPSIZE]
                and [GROUPSIZEDISCOUNT].[DISCOUNTID] = [DISCOUNT].[ID]
                order by [GROUPSIZEDISCOUNT].[GROUPSIZE] desc)
    where
        [DISCOUNT].[DISCOUNTTYPECODE] = 2;

    /*
        Bug 124701 - Two by quantity discounts show up for one item.
        Two quantity discounts are allowed if they are from different programs or merchandise.
        Getting the top best discount
    */
    with GROUPEDGROUPS_CTE as
    (
        select sum(DISCOUNTAMOUNT) DISCOUNTSUM,
            DISCOUNTID
        from @GROUPOPTIONS
        where APPLIESTOTICKETS = 1
        group by DISCOUNTID
    )
    /*
        Best quantity options for tickets and merchandise.
    */
    select @SALESORDERITEMITEMDISCOUNTOPTION = (
        select
            *
        from (
            select
                newid() as ID,
                [GROUPOPTIONS].[SALESORDERITEMID],
                [GROUPOPTIONS].[DISCOUNTID],
                [GROUPOPTIONS].[DISCOUNTNAME],
                convert(money, convert(decimal(17,2), 
                    case
                        when [GROUPOPTIONS].[DISCOUNTAMOUNT] > [GROUPOPTIONS].[SALESORDERITEMAMOUNT] then
                            [GROUPOPTIONS].[SALESORDERITEMAMOUNT]
                        else
                            [GROUPOPTIONS].[DISCOUNTAMOUNT]
                    end
                )) [DISCOUNTAMOUNT],
                @DISCOUNTOPTIONID as [OPTIONID],
                [GROUPOPTIONS].NUMBEROFDISCOUNTEDITEMS
            from @GROUPOPTIONS [GROUPOPTIONS]
            where
                [DISCOUNTID] in (
                    select top 1 DISCOUNTID
                    from GROUPEDGROUPS_CTE GROUPED
                    order by DISCOUNTSUM desc
                )

            union
            select
                newid() as ID,
                [GROUPOPTIONS].[SALESORDERITEMID],
                [GROUPOPTIONS].[DISCOUNTID],
                [GROUPOPTIONS].[DISCOUNTNAME],
                convert(money, convert(decimal(17,2),
                    case
                        when [GROUPOPTIONS].[DISCOUNTAMOUNT] > [GROUPOPTIONS].[SALESORDERITEMAMOUNT] then
                            [GROUPOPTIONS].[SALESORDERITEMAMOUNT]
                        else
                            [GROUPOPTIONS].[DISCOUNTAMOUNT]
                    end
                )) [DISCOUNTAMOUNT],
                @DISCOUNTOPTIONID as [OPTIONID],
                [GROUPOPTIONS].NUMBEROFDISCOUNTEDITEMS
            from @GROUPOPTIONS [GROUPOPTIONS]
            where
                [ID] in (
                    select top (1) [GOS].[ID]
                    from @GROUPOPTIONS [GOS]
                    where [GOS].[SALESORDERITEMID] = [GROUPOPTIONS].[SALESORDERITEMID] and
                        APPLIESTOTICKETS = 0
                    order by [GOS].[DISCOUNTAMOUNT] desc
                )
        ) GROUPS
        for xml raw ('ITEM'), type, root('SALESORDERITEMITEMDISCOUNTOPTION'), BINARY BASE64
    )

    return 0;