USP_STEPUPDATEBATCH_UPDATE

CREATE procedure dbo.USP_STEPUPDATEBATCH_UPDATE
(
  @BATCHID uniqueidentifier,
  @FIELDSTOUPDATE xml ,
  @OFFSET smallint,
  @MOVECODE tinyint,
  @DATEVALUEUNITCODE tinyint,          
  @CURRENTAPPUSERID uniqueidentifier
)
as
begin
  set nocount on;

  declare @ID uniqueidentifier;
  declare @CURRENTDATE datetime;
  declare @CHANGEAGENTID uniqueidentifier;

  set @ID = NewID();

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

  set @CURRENTDATE = GetDate();

  declare @Temp table(ID uniqueidentifier PRIMARY KEY, TARGETDATE date, CONSTITUENTID uniqueidentifier, CONTACTMETHODCODEID uniqueidentifier, OBJECTIVE nvarchar(100), DATEUPDATED bit)

  insert into @Temp
  select
    BATCHSTEWARDSHIPPLANSTEPUPDATE.ID,
    BATCHSTEWARDSHIPPLANSTEPUPDATE.TARGETDATE,
    BATCHSTEWARDSHIPPLANSTEPUPDATE.CONSTITUENTID,
    BATCHSTEWARDSHIPPLANSTEPUPDATE.CONTACTMETHODCODEID,
    upper(rtrim(BATCHSTEWARDSHIPPLANSTEPUPDATE.OBJECTIVE)) as OBJECTIVE,
    0 as DATEUPDATED
  from dbo.BATCHSTEWARDSHIPPLANSTEPUPDATE
  where BATCHID = @BATCHID

  declare @REPLACEVALUES as int;
  declare @CURRENTTARGETDATEVALUE as date;
  declare @OVERWRITETARGETDATEVALUE as date;
  declare @CURRENTOBJECTIVEVALUE as nvarchar(100);
  declare @OVERWRITEOBJECTIVEVALUE as nvarchar(100);
  declare @CURRENTCONTACTMETHODVALUE as uniqueidentifier;
  declare @OVERWRITECONTACTMETHODVALUE as uniqueidentifier;
  declare @CURRENTASSIGNEDTOVALUE as uniqueidentifier;
  declare @OVERWRITEASSIGNEDTOVALUE as uniqueidentifier;

  declare FIELDSTOUPDATE cursor local fast_forward for 
  select
      T.c.value('(../../REPLACEVALUES)[1]','int') AS 'REPLACEVALUES',
      T.c.value('(CURRENTTARGETDATEVALUE)[1]','date') as currentTargetDate,
      T.c.value('(OVERWRITETARGETDATEVALUE)[1]','date') as overwriteTargetDate,
      T.c.value('(CURRENTOBJECTIVEVALUE)[1]','nvarchar(100)') as currentObjective,
      T.c.value('(OVERWRITEOBJECTIVEVALUE)[1]','nvarchar(100)') as overwriteObjective,
      T.c.value('(CURRENTCONTACTMETHODVALUE)[1]','uniqueidentifier') as currentContactMethod,
      T.c.value('(OVERWRITECONTACTMETHODVALUE)[1]','uniqueidentifier') as overwriteContactMethod,
      T.c.value('(CURRENTASSIGNEDTOVALUE)[1]','uniqueidentifier') as currentAssignedTo,
      T.c.value('(OVERWRITEASSIGNEDTOVALUE)[1]','uniqueidentifier') as overwriteAssignedTo
  from @FIELDSTOUPDATE.nodes('/FIELDSTOUPDATE/ITEM/ROWUPDATEMSG/ITEM') as T(c)

  begin try

    open FIELDSTOUPDATE
    fetch next from FIELDSTOUPDATE into @REPLACEVALUES, @CURRENTTARGETDATEVALUE, @OVERWRITETARGETDATEVALUE, @CURRENTOBJECTIVEVALUE, @OVERWRITEOBJECTIVEVALUE, 
                                        @CURRENTCONTACTMETHODVALUE, @OVERWRITECONTACTMETHODVALUE, @CURRENTASSIGNEDTOVALUE, @OVERWRITEASSIGNEDTOVALUE;

    while @@FETCH_STATUS = 0
    begin

        if @REPLACEVALUES = 0 --TARGETDATE

          begin
            update dbo.BATCHSTEWARDSHIPPLANSTEPUPDATE set
              BATCHSTEWARDSHIPPLANSTEPUPDATE.TARGETDATE = @OVERWRITETARGETDATEVALUE
            from dbo.BATCHSTEWARDSHIPPLANSTEPUPDATE
            inner join @Temp TEMP on TEMP.ID = BATCHSTEWARDSHIPPLANSTEPUPDATE.ID
            where 
              TEMP.TARGETDATE = @CURRENTTARGETDATEVALUE and
              BATCHSTEWARDSHIPPLANSTEPUPDATE.DATELOCKED = 0

            update @Temp set DATEUPDATED = 1 
            where TARGETDATE = @CURRENTTARGETDATEVALUE
          end
        else if @REPLACEVALUES = 1 --ASSIGNEDTO

          update dbo.BATCHSTEWARDSHIPPLANSTEPUPDATE set
            BATCHSTEWARDSHIPPLANSTEPUPDATE.CONSTITUENTID = @OVERWRITEASSIGNEDTOVALUE
          from dbo.BATCHSTEWARDSHIPPLANSTEPUPDATE
          inner join @Temp TEMP on TEMP.ID = BATCHSTEWARDSHIPPLANSTEPUPDATE.ID
          where 
            ISNULL(TEMP.CONSTITUENTID, '00000000-0000-0000-0000-000000000000') = ISNULL(@CURRENTASSIGNEDTOVALUE, '00000000-0000-0000-0000-000000000000')
        else if @REPLACEVALUES = 2 --CONTACTMETHOD

          update dbo.BATCHSTEWARDSHIPPLANSTEPUPDATE set
            BATCHSTEWARDSHIPPLANSTEPUPDATE.CONTACTMETHODCODEID = @OVERWRITECONTACTMETHODVALUE
          from dbo.BATCHSTEWARDSHIPPLANSTEPUPDATE
          inner join @Temp TEMP on TEMP.ID = BATCHSTEWARDSHIPPLANSTEPUPDATE.ID
          where 
            ISNULL(TEMP.CONTACTMETHODCODEID, '00000000-0000-0000-0000-000000000000') = ISNULL(@CURRENTCONTACTMETHODVALUE, '00000000-0000-0000-0000-000000000000')
        else if @REPLACEVALUES = 3 --OBJECTIVE

          update dbo.BATCHSTEWARDSHIPPLANSTEPUPDATE set
            BATCHSTEWARDSHIPPLANSTEPUPDATE.OBJECTIVE = @OVERWRITEOBJECTIVEVALUE
          from dbo.BATCHSTEWARDSHIPPLANSTEPUPDATE
          inner join @Temp TEMP on TEMP.ID = BATCHSTEWARDSHIPPLANSTEPUPDATE.ID
          where 
            TEMP.OBJECTIVE = upper(rtrim(@CURRENTOBJECTIVEVALUE))

      fetch next from FIELDSTOUPDATE into @REPLACEVALUES, @CURRENTTARGETDATEVALUE, @OVERWRITETARGETDATEVALUE, @CURRENTOBJECTIVEVALUE, @OVERWRITEOBJECTIVEVALUE, 
                                          @CURRENTCONTACTMETHODVALUE, @OVERWRITECONTACTMETHODVALUE, @CURRENTASSIGNEDTOVALUE, @OVERWRITEASSIGNEDTOVALUE;
    end
    close FIELDSTOUPDATE
    deallocate FIELDSTOUPDATE

    --set negative when moving backward

    if @MOVECODE = 1 
      set @OFFSET = -1 * @OFFSET

    --move target dates not updated by FIELDSTOUPDATE

    if @OFFSET <> 0
      update dbo.BATCHSTEWARDSHIPPLANSTEPUPDATE set
        BATCHSTEWARDSHIPPLANSTEPUPDATE.TARGETDATE = 
          case @DATEVALUEUNITCODE
            when 0 then DATEADD(d,@OFFSET,BATCHSTEWARDSHIPPLANSTEPUPDATE.TARGETDATE)
            when 1 then DATEADD(wk,@OFFSET,BATCHSTEWARDSHIPPLANSTEPUPDATE.TARGETDATE)
            when 2 then DATEADD(m,@OFFSET,BATCHSTEWARDSHIPPLANSTEPUPDATE.TARGETDATE)
            when 3 then DATEADD(yy,@OFFSET,BATCHSTEWARDSHIPPLANSTEPUPDATE.TARGETDATE)
          end
      from dbo.BATCHSTEWARDSHIPPLANSTEPUPDATE
      inner join @Temp TEMP on TEMP.ID = BATCHSTEWARDSHIPPLANSTEPUPDATE.ID and TEMP.DATEUPDATED = 0
      where 
        BATCHSTEWARDSHIPPLANSTEPUPDATE.BATCHID = @BATCHID and
        BATCHSTEWARDSHIPPLANSTEPUPDATE.DATELOCKED = 0
  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;
end