Print ''
Print '-------------------------------------------------------------------------'
Print ' Script for JobMgr:'
Print ' Autor........: J.Feuerstein'
Print ' Description..: Renames From in JMDb from SiteID Old to SiteID New'
Print ' L.Upd........: 04.12.2015/J.Fes Edit Case Sensitive: @Id/@SiteID_New'
Print '-------------------------------------------------------------------------'
GO

use [#DB_N#]
GO

DECLARE @Id  uniqueidentifier,
        @ObjectCliName nvarchar(255),
        @SiteID_Old nvarchar(32),
        @SiteID_Old_ObjectFromSiteID nvarchar(32),
        @SiteID_Old_ObjectOwningSiteID nvarchar(32),
        @ObjectExportRecord_Old nvarchar(255),
        @ObjectExportRecord_NEW nvarchar(255),
        @SiteID_New nvarchar(32),
        @ActionDoneCounter INT,
        @ActionToDoN INT,
        @PrintEvery INT,
        @DoAction bit,
        @DoPrint bit

SET @ActionToDoN = 0
SET @ActionDoneCounter = 0
-- #ToDo: Setup: print del ervery n'times
set @PrintEvery=100
-- #ToDo: Setup: @DoAction Action
--  if @DoAction = 1 then Data is deleted
--  if @DoAction = 0 then only Print is done
set @DoAction=0

--  #ToDo: Setup: Old SiteId
set @SiteID_Old='#SiteID_Old#'
--  #ToDo: Setup: New SiteId
set @SiteID_New='#SiteID_New#'


-- #Doc: zaehlen der zu renamenden Objecte
select @ActionToDoN=count(*)
  from dbo.Objects_tbl
  WHERE (dbo.Objects_tbl.ObjectFromSiteID = @SiteID_Old
        or dbo.Objects_tbl.ObjectOwningSiteID = @SiteID_Old
        or dbo.Objects_tbl.ObjectExportRecord like ('%' + @SiteID_Old + '%'))
        -- and dbo.Objects_tbl.ObjectName in ('#ObjectName1#','#ObjectName1#')


print('.')
Print '-------------------------------------------------------------------------'
print('Rename in DB.....: ' + '#DB_N#')
print('from SiteID_Old..: [' + @SiteID_Old + ']')
print('to   SiteID_New..: [' + @SiteID_New + ']')
print('N to Rename......: [' + CONVERT(nvarchar(64),@ActionToDoN) + ']')
Print '-------------------------------------------------------------------------'

-- Curor fuer alle zu durchlaufenden Objekte erstellen
declare tblCursor cursor fast_forward for
select Id, ObjectCliName, ObjectFromSiteID, ObjectOwningSiteID, ObjectExportRecord
from dbo.Objects_tbl
  WHERE (dbo.Objects_tbl.ObjectFromSiteID = @SiteID_Old
        or dbo.Objects_tbl.ObjectOwningSiteID = @SiteID_Old
        or dbo.Objects_tbl.ObjectExportRecord like ('%' + @SiteID_Old + '%'))
        -- and dbo.Objects_tbl.ObjectName in ('#ObjectName1#','#ObjectName1#')

set nocount on
open tblCursor
 -- alle Objekte durchlaufen die durch den Cursor durchlaufen
 while 1=1
  begin

    fetch next from tblCursor into @Id, @ObjectCliName, @SiteID_Old_ObjectFromSiteID, @SiteID_Old_ObjectOwningSiteID, @ObjectExportRecord_Old
    -- if (@ActionDoneCounter >=10) break
    -- Ausstieg wenn letztes Element
    if (@@fetch_status <> 0) break

    SET @ActionDoneCounter = @ActionDoneCounter + 1
    if (@ActionDoneCounter = 1) or (@ActionDoneCounter = @ActionToDoN) or (@ActionDoneCounter % @PrintEvery = 0)
    begin
      set @DoPrint = 1
    end
    ELSE
    BEGIN
      set @DoPrint = 0
    END

    if @DoPrint = 1
    begin
      print('Rename:[' + CONVERT(nvarchar(64),@ActionDoneCounter) + '/' + CONVERT(nvarchar(64),@ActionToDoN) + ':' + Str((100.00*@ActionDoneCounter/@ActionToDoN),6,2)  + '%]' +
            ' Cli: [' + @SiteID_Old_ObjectFromSiteID + ':' + @ObjectCliName + ']' +
            ' F.SiteID: [' +  @SiteID_Old_ObjectFromSiteID + ']' +
            ' O.SiteID: [' +  @SiteID_Old_ObjectOwningSiteID + ']'
            )
    end

    -- #Step: Rename ObjectFromSiteID
    if upper(@SiteID_Old_ObjectFromSiteID) = upper(@SiteID_Old)
    begin
      if @DoPrint = 1
      begin
       print (' |-> Rename Case 1:' + ' F.SiteID: "' +  @SiteID_Old_ObjectFromSiteID + '" to: "' + @SiteID_New  + '"')
      end
      if @DoAction = 1
      begin
        UPDATE Objects_tbl SET ObjectFromSiteID = @SiteID_New  where Id=@Id
       end
    end

    -- #Step: Rename ObjectOwningSiteID
    if upper(@SiteID_Old_ObjectOwningSiteID) = upper(@SiteID_Old)
    begin
      if @DoPrint = 1
      begin
        print (' |-> Rename Case 2:' + ' O.SiteID: "' +  @SiteID_Old_ObjectOwningSiteID + '" to: "' + @SiteID_New + '"')
      end
      if @DoAction = 1
      begin
        UPDATE Objects_tbl SET ObjectOwningSiteID = @SiteID_New  where Id=@Id
      end
    end

    -- #New: 13.11.2014 J.Fes
    -- #Step: Rename ObjectExportRecord data
    if upper(charindex(@SiteID_Old,@ObjectExportRecord_Old)) > 0
    begin
      set @ObjectExportRecord_NEW=replace(@ObjectExportRecord_Old,@SiteID_Old,@SiteID_New )
      if @DoPrint = 1
      begin
        print (' |-> Rename Case 3:' + ' ExpoRec.: "' +  @ObjectExportRecord_Old + '" to: "' + @ObjectExportRecord_NEW + '"' )
      end
      if @DoAction = 1
      begin
        UPDATE Objects_tbl SET ObjectExportRecord = @ObjectExportRecord_NEW  where Id=@Id
      end
    end


--     -- #New...: 10.05.2013 07:19 J.Fes to update also ProcessOnSiteID for Process Results
--     -- #Change: 17.11.2015 13:24 J.Fes removed because  ProcessOnSiteID not existing anymore
--     if @DoAction = 1
--     begin
--       UPDATE JobControl_ObjectsProcessData_tbl
--          SET JobControl_ObjectsProcessData_tbl.ProcessOnSiteID=@SiteID_New
--          where JobControl_ObjectsProcessData_tbl.Objects_tbl_ID = @Id
--     end

  end

 -- #Doc: Close Cursor
close tblCursor
deallocate tblCursor

Print ''
Print '-------------------------------------------------------------------------'
Print ' Script executed'
Print '-------------------------------------------------------------------------'
GO
