Print ''
Print '-------------------------------------------------------------------------'
Print ' Script for JobMgr:'
Print ' Autor        : J.Feuerstein'
Print ' Description  : Moves P.Results between from PGroup to PGroup'
Print ' First Release: 25.10.2018/J.Fes'
Print '-------------------------------------------------------------------------'
GO
use [#DB_N#]
GO

DECLARE @Id  uniqueidentifier,
        @ActionDoneCounter INT,
        @ActionToDoN INT,
        @PrintEvery INT,
        @DoAction bit,
        @DoPrint bit


DECLARE @ExecuteSQLstr        nvarchar(max),
    --    @SiteId               nvarchar(max),
        @ProcStateColFrom     nvarchar(max),
        @ProcStateColTo       nvarchar(max),
        @zzzzztemp_tbl        nvarchar(max),
        @ProcessProcStateColValCur  nvarchar(max),
        @ProcessPacketName    nvarchar(max)

 -- #Doc: Reset Counter
 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: set Values: ProcStateCol01..20 or ProcStateColRf, ProcStateColMc
 set @ProcStateColFrom='ProcStateCol##'
 set @ProcStateColTo='ProcStateCol$$'


 set @zzzzztemp_tbl='##zzzzztemp_tbl_MovePGroups' +  @ProcStateColFrom + '_' + @ProcStateColTo

 IF (OBJECT_ID (@zzzzztemp_tbl, N'U') IS NOT NULL) exec ('drop table ' + @zzzzztemp_tbl)

 set @ExecuteSQLstr = +
'SELECT' +
'  ID,ProcessPacketName,ProcessProcStateCol' +
' INTO ##zzzzztemp_tbl_MovePGroups'   +
' FROM' +
'  JobControl_ObjectsProcessData_tbl' +
' WHERE' +
'  ProcessProcStateCol = ' + '''' + @ProcStateColFrom + ''''

print @ExecuteSQLstr
 execute sp_executesql @ExecuteSQLstr


-- #Doc: count to process data
select @ActionToDoN=count(*)
  from ##zzzzztemp_tbl_MovePGroups

print('.')
Print '-------------------------------------------------------------------------'
Print 'Process:'
Print ' Moves P.Results from P.Group.: ' + @ProcStateColFrom
Print ' Moves P.Results to.. P.Group.: ' + @ProcStateColTo
print(' need to Process..............: ' + CONVERT(nvarchar(64),@ActionToDoN) + ' (n)')
Print '-------------------------------------------------------------------------'

-- create Cursor for all datasets to process
declare tblCursor cursor fast_forward for
select Id, ProcessPacketName, ProcessProcStateCol
  from ##zzzzztemp_tbl_MovePGroups

set nocount on
open tblCursor
 -- process data
 while 1=1
  begin
    SET @ActionDoneCounter = @ActionDoneCounter + 1
    fetch next from tblCursor into @Id, @ProcessPacketName, @ProcessProcStateColValCur
    -- if (@ActionDoneCounter > 10) break
    -- exit on last element
    if (@@fetch_status <> 0) break

    set @DoPrint = 0
    if (@ActionDoneCounter = 1) or (@ActionDoneCounter = @ActionToDoN) or (@ActionDoneCounter % @PrintEvery = 0)
    begin
      set @DoPrint = 1
    end

    if @DoPrint = 1
    begin
      print('Update:[' + CONVERT(nvarchar(64),@ActionDoneCounter) + '/' + CONVERT(nvarchar(64),@ActionToDoN) + ':' + Str((100.00*@ActionDoneCounter/@ActionToDoN),6,2)  + '%]' +
            ' for ProcessPacketName:' + @ProcessPacketName + ' // PCol from:' + @ProcessProcStateColValCur + ' to: ' + @ProcStateColTo)
    end


    if @DoAction = 1
    BEGIN
      -- #Step: Move to @ProcStateColTo
      set @ExecuteSQLstr = 'Update JobControl_ObjectsProcessData_tbl' +
         ' set ProcessProcStateCol = ' + '''' + @ProcStateColTo + '''' +
         ' where ID =' + '''' + CONVERT(nvarchar(64),@Id) + ''''
      -- Print @ExecuteSQLstr
      execute sp_executesql @ExecuteSQLstr
    END
  END


-- #Step: Close Cursor
close tblCursor
deallocate tblCursor

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