Print ''
Print '-------------------------------------------------------------------------'
Print ' Script for JobMgr:'
Print ' Autor        : J.Feuerstein'
Print ' Description  : Deletes Process Data of Site and Process'
Print '                Resets als Process Status of Object'
Print ' First Release: 30.10.2014/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),
        @ProcStateCol         nvarchar(max),
        @ProcStateValueRestTo nvarchar(max),
        @ObjectCliName nvarchar(255),
        @ObjectClass nvarchar(10),
        @ObjectFromSiteID nvarchar(32)

 -- #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: Insert your SiteId
 set @SiteId='#SiteId#'
 -- #ToDo: set Values: ProcStateCol01..20 oder ProcStateColRf
 set @ProcStateCol='ProcStateColXX'
 -- #ToDo: Set Value you like to Use Default is 'N'
 set @ProcStateValueRestTo='N'


 IF (OBJECT_ID ('##zzzzztemp_tbl_DelProcessData', N'U') IS NOT NULL) exec ('drop table ' + '##zzzzztemp_tbl_DelProcessData')

 set @ExecuteSQLstr = +
 'SELECT Id, ObjectFromSiteID, ObjectClass, ObjectCliName' +
 ' into ##zzzzztemp_tbl_DelProcessData'  +
 ' FROM dbo.Objects_tbl' +
 ' where ([' + @ProcStateCol + '] not like ' + '''X%''' + ' and [' + @ProcStateCol + '] <> ''' + @ProcStateValueRestTo + ''')' +
         ' and ObjectClass = ' + '''DS''' +
         ' and dbo.Objects_tbl.ObjectFromSiteID = ' + '''' + @SiteId + ''''

 print @ExecuteSQLstr
 execute sp_executesql @ExecuteSQLstr


-- #Doc: zaehlen der zu löschenden Result Daten
select @ActionToDoN=count(*)
  from ##zzzzztemp_tbl_DelProcessData


print('.')
Print '-------------------------------------------------------------------------'
Print 'Process:'
print ' Delete Processdata from Site.: [' + @SiteId + ']  from Process Group:[' + @ProcStateCol + ']'
Print ' Reset Status of Process Group: [' + @ProcStateCol + '] to:' + @ProcStateValueRestTo
print(' N to process.................: [' + CONVERT(nvarchar(64),@ActionToDoN) + ']')
Print '-------------------------------------------------------------------------'

-- Cursor fuer alle zu durchlaufenden Objekte erstellen
declare tblCursor cursor fast_forward for
select Id, ObjectFromSiteID, ObjectClass, ObjectCliName
  from ##zzzzztemp_tbl_DelProcessData

set nocount on
open tblCursor
 -- alle Objekte durchlaufen
 while 1=1
  begin
    SET @ActionDoneCounter = @ActionDoneCounter + 1
    fetch next from tblCursor into @Id, @ObjectFromSiteID, @ObjectClass, @ObjectCliName
    -- if (@ActionDoneCounter > 10) break
    -- Ausstieg wenn letztes 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('Del P.Res:[' + CONVERT(nvarchar(64),@ActionDoneCounter) + '/' + CONVERT(nvarchar(64),@ActionToDoN) + ':' + Str((100.00*@ActionDoneCounter/@ActionToDoN),6,2)  + '%]' +
            ' Of Object: [' + @ObjectFromSiteID + ':' + @ObjectClass + ':' +  @ObjectCliName + ']'
            )
    end


    if @DoAction = 1
    BEGIN
     -- loeschen der Process Daten
     DELETE FROM dbo.JobControl_ObjectsProcessData_tbl
       WHERE JobControl_ObjectsProcessData_tbl.Objects_tbl_ID = @Id
             and JobControl_ObjectsProcessData_tbl.ProcessProcStateCol = @ProcStateCol

     -- '#Step: Reset.: ProcessStatus Objects_tbl.' +  @ProcStateCol + ' to Value:[' + @ProcStateValueRestTo + ']'
      set @ExecuteSQLstr = 'UPDATE Objects_tbl' +
         ' set ' + @ProcStateCol + ' = ' + '''' + @ProcStateValueRestTo + '''' +
         ' where Objects_tbl.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
