USP_DATAFORMTEMPLATE_EDIT_DISCOUNTGROUPDETAILMERCHANDISE

CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_DISCOUNTGROUPDETAILMERCHANDISE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @APPLIESTOMERCHANDISECODE tinyint,
    @DISCOUNTMERCHANDISEDEPARTMENTS xml,
    @DISCOUNTMERCHANDISEITEMS xml,
    @MERCHANDISEAMOUNT money,
    @MERCHANDISEPERCENT decimal(5, 2)
)
as

    set nocount on;

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

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    declare @CALCULATIONTYPECODE tinyint;
    declare @DISCOUNTTYPECODE tinyint;
    select @CALCULATIONTYPECODE = CALCULATIONTYPECODE,
        @DISCOUNTTYPECODE = DISCOUNTTYPECODE
    from dbo.DISCOUNT
    where ID = @ID

    if @DISCOUNTTYPECODE <> 2
    begin
        if @CALCULATIONTYPECODE = 1 and @MERCHANDISEPERCENT <= 0
            raiserror('BBERR_INVALIDMERCHANDISEPERCENT', 13, 1)
        else if @CALCULATIONTYPECODE = 0 and @MERCHANDISEAMOUNT <= 0
            raiserror('BBERR_INVALIDMERCHANDISEAMOUNT', 13, 1)
    end

    declare @contextCache varbinary(128);

    -- Clone the discount and update only the new version.
    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.

    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


    update dbo.DISCOUNT
    set MERCHANDISEAMOUNT = @MERCHANDISEAMOUNT,
        MERCHANDISEPERCENT = @MERCHANDISEPERCENT,
        DATECHANGED = @CURRENTDATE,
        CHANGEDBYID = @CHANGEAGENTID
    where ID = @CLONEID

    if @APPLIESTOMERCHANDISECODE in (0, 5, 6)
    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 = 1) <> 0
        begin
            -- get rid of all discounted departments and items
            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 = 1
                )

            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(),
                1,
                @APPLIESTOMERCHANDISECODE, -- all discountable merchandise
                @CHANGEAGENTID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE
            )

        end
    end
    else if @APPLIESTOMERCHANDISECODE = 1
    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 discounted 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 = 1
            )

        exec dbo.USP_DISCOUNT_MERCHANDISEDEPARTMENTS_UPDATEFROMXML @CLONEID, @DISCOUNTMERCHANDISEDEPARTMENTS, @CHANGEAGENTID, @CURRENTDATE
    end
    else if @APPLIESTOMERCHANDISECODE = 2
    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 discounted 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 = 1
            )

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

    end

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

return 0;