USP_DATAFORMTEMPLATE_EDIT_DISCOUNTGROUPDETAILQUALIFIEDMERCHANDISE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_DISCOUNTGROUPDETAILQUALIFIEDMERCHANDISE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @APPLIESTOMERCHANDISECODE tinyint,
    @DISCOUNTMERCHANDISEDEPARTMENTS xml,
    @DISCOUNTMERCHANDISEITEMS xml
)
as

    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    declare @contextCache varbinary(128);


    -- Clone the discount and only edit the new one, for historical salesorder calculations.
    declare @CLONEID uniqueidentifier;
    exec dbo.USP_DISCOUNT_CLONE @ID, @CLONEID output;

    -- Update all xml with new ID's so the originals do not get overwritten (and the changes lost).

    if @DISCOUNTMERCHANDISEDEPARTMENTS is not null
    begin
        set @DISCOUNTMERCHANDISEDEPARTMENTS = (
            select
                newid() as ID,
                newid() as DISCOUNTGROUPID,
                T.c.value('(MERCHANDISEDEPARTMENTID)[1]','uniqueidentifier') as MERCHANDISEDEPARTMENTID,
                T.c.value('(SEQUENCE)[1]','integer') as SEQUENCE
            from @DISCOUNTMERCHANDISEDEPARTMENTS.nodes('/DISCOUNTMERCHANDISEDEPARTMENTS/ITEM') T(c)
            for xml path('ITEM'),type,elements,root('DISCOUNTMERCHANDISEDEPARTMENTS'),BINARY BASE64
        )
    end

    if @DISCOUNTMERCHANDISEITEMS is not null
    begin
        set @DISCOUNTMERCHANDISEITEMS = (
            select
                newid() as ID,
                newid() as DISCOUNTGROUPID,
                T.c.value('(MERCHANDISEITEMID)[1]','uniqueidentifier') as MERCHANDISEITEMID,
                T.c.value('(SEQUENCE)[1]','integer') as SEQUENCE
            from @DISCOUNTMERCHANDISEITEMS.nodes('/DISCOUNTMERCHANDISEITEMS/ITEM') T(c)
            for xml path('ITEM'),type,elements,root('DISCOUNTMERCHANDISEITEMS'),BINARY BASE64
        )
    end


    if @APPLIESTOMERCHANDISECODE = 4 -- Any merchandise
    begin

        /* cache current context information */
        set @contextCache = CONTEXT_INFO();

        /* set CONTEXT_INFO to @CHANGEAGENTID */
        if not @CHANGEAGENTID is null
            set CONTEXT_INFO @CHANGEAGENTID

        if (select top 1 DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE
            from dbo.DISCOUNTGROUPDETAIL
            inner join dbo.DISCOUNTGROUP on DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
            where DISCOUNTGROUP.DISCOUNTID = @CLONEID
                and DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 0) <> 4
        begin
            -- get rid of all merchandisedepartments and merchandiseitems
            delete from dbo.DISCOUNTGROUP
            where DISCOUNTGROUP.ID in
                (
                    select DISCOUNTGROUP.ID
                    from dbo.DISCOUNTGROUPDETAIL
                    inner join dbo.DISCOUNTGROUP on DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
                    where DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE in (1, 2)
                        and DISCOUNTGROUP.DISCOUNTID = @CLONEID
                        and DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 0
                )

            declare @DISCOUNTGROUPID uniqueidentifier;
            set @DISCOUNTGROUPID = newid();

            -- insert new items
            insert into dbo.[DISCOUNTGROUP]
            (
                DISCOUNTID,
                ID,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            values
            (
                @CLONEID,
                @DISCOUNTGROUPID,
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            )

            insert into dbo.[DISCOUNTGROUPDETAIL]
            (
                DISCOUNTGROUPID,
                ID,
                DISCOUNTEDITEM,
                DISCOUNTGROUPDETAILAPPLICATIONCODE,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            values
            (
                @DISCOUNTGROUPID,
                newid(),
                0,
                @APPLIESTOMERCHANDISECODE, -- any item
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            )

        end
    end
    else if @APPLIESTOMERCHANDISECODE = 1 -- Merchandise department
    begin

        if (select count(T.discountmerchandisedepartments.value('(MERCHANDISEDEPARTMENTID)[1]', 'uniqueidentifier'))
            from @DISCOUNTMERCHANDISEDEPARTMENTS.nodes('/DISCOUNTMERCHANDISEDEPARTMENTS/ITEM') T(discountmerchandisedepartments)) = 0
            raiserror('BBERR_DEPARTMENTSREQUIRED', 13, 1);

        if exists
            (select count(MERCHANDISEDEPARTMENTID)
                from
                (
                    select T.discountmerchandisedepartments.value('(MERCHANDISEDEPARTMENTID)[1]', 'uniqueidentifier') MERCHANDISEDEPARTMENTID
                    from @DISCOUNTMERCHANDISEDEPARTMENTS.nodes('/DISCOUNTMERCHANDISEDEPARTMENTS/ITEM') T(discountmerchandisedepartments)
                ) Result
                group by MERCHANDISEDEPARTMENTID
                having count(*) > 1
            )
            raiserror('BBERR_DUPLICATEDEPARTMENTS', 13, 1);

        -- get rid of all qualifying discount groups, because the relevant existing ones won't be found for update.
        delete from dbo.DISCOUNTGROUP
        where DISCOUNTGROUP.ID in
            (
                select DISCOUNTGROUP.ID
                from dbo.DISCOUNTGROUPDETAIL
                inner join dbo.DISCOUNTGROUP on DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
                where DISCOUNTGROUP.DISCOUNTID = @CLONEID
                    and DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 0
            )

        exec dbo.USP_DISCOUNT_MERCHANDISEDEPARTMENTS_UPDATEFROMXML @CLONEID, @DISCOUNTMERCHANDISEDEPARTMENTS, @CHANGEAGENTID, @CURRENTDATE, 0
    end
    else if @APPLIESTOMERCHANDISECODE = 2 -- Merchandise items
    begin
        if (select count(T.discountmerchandiseitems.value('(MERCHANDISEITEMID)[1]', 'uniqueidentifier'))
            from @DISCOUNTMERCHANDISEITEMS.nodes('/DISCOUNTMERCHANDISEITEMS/ITEM') T(discountmerchandiseitems)) = 0
            raiserror('BBERR_ITEMSREQUIRED', 13, 1);

        if exists
            (select count(MERCHANDISEITEMID)
                from
                (
                    select T.discountmerchandiseitems.value('(MERCHANDISEITEMID)[1]', 'uniqueidentifier') MERCHANDISEITEMID
                    from @DISCOUNTMERCHANDISEITEMS.nodes('/DISCOUNTMERCHANDISEITEMS/ITEM') T(discountmerchandiseitems)
                ) Result
                group by MERCHANDISEITEMID
                having count(*) > 1
            )
            raiserror('BBERR_DUPLICATEITEMS', 13, 1);

        -- get rid of all qualifying discount groups, because the relevant existing ones won't be found for update.
        delete from dbo.DISCOUNTGROUP
        where DISCOUNTGROUP.ID in
            (
                select DISCOUNTGROUP.ID
                from dbo.DISCOUNTGROUPDETAIL
                inner join dbo.DISCOUNTGROUP on DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
                where DISCOUNTGROUP.DISCOUNTID = @CLONEID
                    and DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 0
            )

        exec dbo.USP_DISCOUNT_MERCHANDISEITEMS_UPDATEFROMXML @CLONEID, @DISCOUNTMERCHANDISEITEMS, @CHANGEAGENTID, @CURRENTDATE, 0

    end

    /* reset CONTEXT_INFO to previous value */
    if not @contextCache is null
        set CONTEXT_INFO @contextCache


    -- Update the DISCOUNT record to invalidate the cache for sales.
    update dbo.DISCOUNT
    set CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = getdate()
    where ID = @ID;

    return 0;