USP_DISCOUNT_UPDATEQUALIFYINGMERCHANDISE

CREATE procedure dbo.USP_DISCOUNT_UPDATEQUALIFYINGMERCHANDISE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTDATE datetime = null,
    @APPLIESTOMERCHANDISECODE tinyint,
    @QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS xml,
    @QUALIFYINGDISCOUNTMERCHANDISEITEMS xml
)        
as    
    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 = 4
    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 is null or @PREVIOUSAPPLICATIONCODE <> 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
                    inner join dbo.DISCOUNT on DISCOUNTGROUP.DISCOUNTID = DISCOUNT.ID
                    where DISCOUNTGROUPDETAIL.DISCOUNTGROUPDETAILAPPLICATIONCODE in (1, 2)
                        and DISCOUNT.ID = @ID
                        and DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 0
                )


            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(),
                0,
                @APPLIESTOMERCHANDISECODE, -- any item

                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CURRENTDATE, 
                @CURRENTDATE
            )    

        end
    end
    else if @APPLIESTOMERCHANDISECODE = 1
    begin


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

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

        -- 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 (4, 2)
                    and DISCOUNT.ID = @ID
                    and DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 0
            )        

        exec dbo.USP_DISCOUNT_QUALIFYINGMERCHANDISEDEPARTMENTS_UPDATEFROMXML @ID, @QUALIFYINGDISCOUNTMERCHANDISEDEPARTMENTS, @CHANGEAGENTID, @CURRENTDATE
    end
    else if @APPLIESTOMERCHANDISECODE = 2
    begin
        if (select count(T.qualifyingdiscountmerchandiseitems.value('(MERCHANDISEITEMID)[1]', 'uniqueidentifier'))
            from @QUALIFYINGDISCOUNTMERCHANDISEITEMS.nodes('/QUALIFYINGDISCOUNTMERCHANDISEITEMS/ITEM') T(qualifyingdiscountmerchandiseitems)) = 0
            raiserror('BBERR_ITEMSREQUIRED', 13, 1);        

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

        -- 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 (4, 1)
                    and DISCOUNT.ID = @ID
                    and DISCOUNTGROUPDETAIL.DISCOUNTEDITEM = 0
            )    

        exec dbo.USP_DISCOUNT_QUALIFYINGMERCHANDISEITEMS_UPDATEFROMXML @ID, @QUALIFYINGDISCOUNTMERCHANDISEITEMS, @CHANGEAGENTID, @CURRENTDATE

    end

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


return 0;