use [#DB_N#]
GO
Print ''
Print '-------------------------------------------------------------------------'
Print ' Script for JobMgr:'
Print ' Autor........: J.Feuerstein'
Print ' Description..: Deletes Process Data and Resets Process Status having ERROR'
Print '                Selection Criterias are: ProcessResultCode <> 0'
Print '                Selection Criterias are: SiteId'
Print '                Selection Criterias are: ProcStateColXX'
Print '                Selection Criterias are: ProcessStartTime'
Print ' #New.........: 27.02.2019/J.Fes'
Print '-------------------------------------------------------------------------'


-- GO

DECLARE @DSId  uniqueidentifier,
        @PResId  uniqueidentifier,
        @ActionDoneCounter INT,
        @ActionToDoN INT,
        @PrintEvery INT,
        @DoAction bit,
        @DoPrint bit


DECLARE @ExecuteSQLstr        nvarchar(max),
        @SiteId               nvarchar(max),
        @ProcStateCol         nvarchar(max),
        @ProcStartTime        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=10
 -- #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#'
 -- set @SiteId='TC10'

 -- #ToDo: set Values: ProcStateCol01..20 oder ProcStateColRf
 set @ProcStateCol='ProcStateColXX'
 -- set @ProcStateCol='ProcStateCol01'

 -- #ToDo: set Values: ProcessStartTime in Format '2019-02-22 22:00:00'
 set @ProcStartTime='20YY-MM-DD HH:MI:SC'
 -- set @ProcStartTime='2000-02-27 22:00:00'

 -- #ToDo: Set Value you like to Use Default is 'N'
 set @ProcStateValueRestTo='N'
 -- set @ProcStateValueRestTo='RUN.F'

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


set @ExecuteSQLstr = +
'SELECT DS.Id AS DSId,' +
' DS.ObjectFromSiteID as ObjectFromSiteID,' +
' DS.ObjectClass as ObjectClass,' +
' DS.ObjectCliName as ObjectCliName,' +
' PRes.Id AS PResId' +
' into ##zzzzztemp_tbl_DelProcessData' +
' FROM   Objects_tbl AS DS INNER JOIN' +
'        JobControl_ObjectsProcessData_tbl AS PRes ON DS.Id = PRes.Objects_tbl_ID' +
' WHERE  (DS.ObjectFromSiteID = ' + '''' + @SiteId + ''')' +
-- '    AND (DS.ObjectClass = N''DS'')' +
-- '    AND (DS.ObjectUgMaster_Type = N''AP'')' +
'    AND (PRes.ProcessProcStateCol = ' + '''' + @ProcStateCol + ''')' +
'    AND (PRes.ProcessResultCode <> N''0'')' +
'    AND (PRes.ProcessTimeStart >= CONVERT(DATETIME,' + '''' + @ProcStartTime  + '''' + ', 102))'


 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 + ']'
Print ' Reset Status of Process Group: ' + @ProcStateCol
Print ' Reset Status to value........: ' + @ProcStateValueRestTo
print(' Set Object PStatus Yes/No....: ' + CONVERT(nvarchar(64),@ActionToDoN) + ']')
Print '-------------------------------------------------------------------------'

-- Cursor fuer alle zu durchlaufenden Objekte erstellen
declare tblCursor cursor fast_forward for
select DSId, ObjectFromSiteID, ObjectClass, ObjectCliName, PResId
  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 @DSId, @ObjectFromSiteID, @ObjectClass, @ObjectCliName, @PResId
    -- 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
     -- #Doc: delete process data by exact ID .ID = @PResId
     DELETE FROM dbo.JobControl_ObjectsProcessData_tbl
       WHERE JobControl_ObjectsProcessData_tbl.ID = @PResId

        -- #Doc: deletes all process data from @ProcStateCol
        -- DELETE FROM dbo.JobControl_ObjectsProcessData_tbl
        --   WHERE JobControl_ObjectsProcessData_tbl.Objects_tbl_ID = @DSId
        --         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),@DSId) + ''''
      -- #Doc: excute Update Status
      execute sp_executesql @ExecuteSQLstr
    END
  END


-- #Step: Close Cursor
close tblCursor
deallocate tblCursor

Print ''
Print '-------------------------------------------------------------------------'
Print ' Script executed'
Print '-------------------------------------------------------------------------'
Print ' to restructure PRes please execute now SQL Script:'
Print ' \01-BinServer\Resources\JobMgrSQL\DbMaintain_1Default\JobMgr_02_ProcessData_LostProcess_PStatus_AStatus_CleanUp.sql'
Print '-------------------------------------------------------------------------'
GO
