USP_DISCOUNT_UPDATEMERCHANDISE

CREATE procedure dbo.USP_DISCOUNT_UPDATEMERCHANDISE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTDATE datetime = null,
    @APPLIESTOMERCHANDISECODE tinyint,
    @DISCOUNTMERCHANDISEDEPARTMENTS xml,
    @DISCOUNTMERCHANDISEITEMS xml
)
as
begin
    set nocount on;

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

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

    declare @contextCache varbinary(128);

    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

        declare @PREVIOUSAPPLICATIONCODE tinyint;
        select top 1 
            @PREVIOUSAPPLICATIONCODE = DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE
        from dbo.DISCOUNTGROUPDETAIL
        inner join dbo.DISCOUNTGROUP on DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
        inner join dbo.DISCOUNT on DISCOUNTGROUP.DISCOUNTID = DISCOUNT.ID
        where DISCOUNT.ID = @ID
            and DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 1

        if @PREVIOUSAPPLICATIONCODE <> 0 or @PREVIOUSAPPLICATIONCODE is null
        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
                    inner join dbo.DISCOUNT on DISCOUNTGROUP.DISCOUNTID = DISCOUNT.ID
                    where DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE in (1, 2, 5, 6)
                        and DISCOUNT.ID = @ID
                        and DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 1
                )

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

            -- insert new items            

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

            insert into dbo.[DISCOUNTGROUPDETAIL]
            (
                DISCOUNTGROUPID, 
                ID,
                DISCOUNTEDITEM,
                DISCOUNTGROUPDETAILAPPLICATIONCODE,
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED
            )
            values
            (
                @DISCOUNTGROUPID, 
                newid(),
                1,
                @APPLIESTOMERCHANDISECODE, 
                @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);        

        delete from dbo.DISCOUNTGROUP
        where DISCOUNTGROUP.ID in
            (
                select DISCOUNTGROUP.ID
                from dbo.DISCOUNTGROUPDETAIL
                inner join dbo.DISCOUNTGROUP on DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
                inner join dbo.DISCOUNT on DISCOUNTGROUP.DISCOUNTID = DISCOUNT.ID
                where DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE in (0, 2)
                    and DISCOUNT.ID = @ID
                    and DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 1
            )        
        exec dbo.USP_DISCOUNT_MERCHANDISEDEPARTMENTS_UPDATEFROMXML @ID, @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);            

        delete from dbo.DISCOUNTGROUP
        where DISCOUNTGROUP.ID in
            (
                select DISCOUNTGROUP.ID
                from dbo.DISCOUNTGROUPDETAIL
                inner join dbo.DISCOUNTGROUP on DISCOUNTGROUP.ID = DISCOUNTGROUPDETAIL.DISCOUNTGROUPID
                inner join dbo.DISCOUNT on DISCOUNTGROUP.DISCOUNTID = DISCOUNT.ID
                where DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE in (0, 1)
                    and DISCOUNT.ID = @ID
                    and DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 1
            )    

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

    end

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

return 0;
end