Print ''
Print '-------------------------------------------------------------------------'
Print ' #Doc.........: Cleanup !!all NonRef Data from JobMgr Database'
Print ' #New.........: 29.01.2024 A.Becker/J.Fes'
Print ' #Doc.........: Usage please Update #SiteId#'
Print ' Run ON DB....: #DB_N#'
Print '-------------------------------------------------------------------------'


USE [#DB_N#]

DECLARE @DeletedCounter INT
DECLARE @DeleteN INT
SET @DeleteN = 0
SET @DeletedCounter = 0

declare @Id  uniqueidentifier,
        @ObjectCliName nvarchar(255),
        @ObjectClass nvarchar(10),
        @CleanDataOfSiteId nvarchar(32),
        @CleanDataOfSiteId_ObjectFromSiteID nvarchar(32),
        @CleanDataOfSiteId_ObjectOwningSiteID nvarchar(32),
        @SiteID_NEW nvarchar(32),
        @DoDeleteData bit,
        @PrintEvery int

-- #ToDo: Please define SiteID to delte data from JobMgr DB
set @CleanDataOfSiteId='#SiteID#'

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

select @DeleteN=count(*)
FROM   Objects_tbl AS OBJ
WHERE  (OBJ.ObjectFromSiteID = @CleanDataOfSiteId)
        AND NOT EXISTS (SELECT 1 FROM Objects_Ir_Rel_tbl REL WHERE REL.ObjectId=OBJ.Id OR REL.IrId=OBJ.Id)


print('.')
Print '-------------------------------------------------------------------------'
print('CleanUp in DB....: ' + '#DB_N#')
print('CleanUp Classes..: Items/ItemRef/DS')
print('CleanUp SiteId...: [' + @CleanDataOfSiteId + ']')
print('N to process.....: [' + CONVERT(nvarchar(64),@DeleteN) + ']')
Print '-------------------------------------------------------------------------'


-- Curor fuer alle zu durchlaufenden Objekte erstellen
declare zztmpClean2_tbl_Cursor cursor fast_forward for
select Id, ObjectCliName, ObjectClass, ObjectFromSiteID, ObjectOwningSiteID
FROM   Objects_tbl AS OBJ
WHERE  (OBJ.ObjectFromSiteID = @CleanDataOfSiteId)
        AND NOT EXISTS (SELECT 1 FROM Objects_Ir_Rel_tbl REL WHERE REL.ObjectId=OBJ.Id OR REL.IrId=OBJ.Id)


set nocount on
open zztmpClean2_tbl_Cursor
 -- alle Objekte durchlaufen die durch den Cursor durchlaufen
 while 1=1
  begin
    SET @DeletedCounter = @DeletedCounter + 1
    fetch next from zztmpClean2_tbl_Cursor into @Id, @ObjectCliName, @ObjectClass, @CleanDataOfSiteId_ObjectFromSiteID, @CleanDataOfSiteId_ObjectOwningSiteID
    -- Ausstieg wenn nichts zu löschen ist
    if (@DeleteN =0)
    begin
      print('Result...........: No Data found to CleanUp')
      break
    end
    -- Ausstieg wenn letztes Element
    if (@@fetch_status <> 0) break

    -- #Debug: Austieg für test
    -- if (@DeletedCounter >=2) break
    -- print('DeleteMOd:[' + CONVERT(nvarchar(64),@DeletedCounter % 10))

    if (@DeletedCounter = 1) or (@DeletedCounter = @DeleteN) or (@DeletedCounter % @PrintEvery = 0)
    begin
       print('CleanUp:[' + CONVERT(nvarchar(64),@DeletedCounter) + '/' + CONVERT(nvarchar(64),@DeleteN) + ':' + Str((100.00*@DeletedCounter/@DeleteN),6,2)  + '%]' +
             ' Obj:[' + @CleanDataOfSiteId_ObjectFromSiteID + ':' + @ObjectClass + ':' + @ObjectCliName + ']')
    end

    if @DoDeleteData = 1
    begin

     -- Step: loeschen _ObjectsProcessData
     delete from dbo.JobControl_ObjectsProcessData_tbl WHERE dbo.JobControl_ObjectsProcessData_tbl.ID IN (
       SELECT
         dbo.JobControl_ObjectsProcessData_tbl.ID
       FROM
         dbo.Objects_tbl
         LEFT OUTER JOIN dbo.JobControl_ObjectsProcessData_tbl ON (dbo.Objects_tbl.Id = dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID)
       WHERE
         dbo.Objects_tbl.Id = @Id
     )

     -- Step: loeschen Objects_Ir_Rel_tbl
     delete from dbo.Objects_Ir_Rel_tbl WHERE dbo.Objects_Ir_Rel_tbl.ID IN (
       SELECT
         dbo.Objects_Ir_Rel_tbl.Id
       FROM
         dbo.Objects_tbl
         LEFT OUTER JOIN dbo.Objects_Ir_Rel_tbl ON (dbo.Objects_tbl.Id = dbo.Objects_Ir_Rel_tbl.ObjectId)
       WHERE
         dbo.Objects_tbl.Id = @Id
     )

     -- Step: loeschen des Objects selbst
     delete from dbo.Objects_tbl WHERE dbo.Objects_tbl.Id = @Id
    end
  end
close zztmpClean2_tbl_Cursor
deallocate zztmpClean2_tbl_Cursor




GO

Print ''
Print '----------------------------------------------------'
Print ' SQL Script Excetuted on: [#DB_N#]'
Print '----------------------------------------------------'
GO

