Print ''
Print '----------------------------------------------------'
Print ' #Doc.........: Cleans all Data O.Tye = NP'
print ' #Doc.........: please look at marks #ToDo:'
Print ' FirstRevision: V2.2.1   29.11.2013 J.Fes'
Print ' CurrRevision.: '
Print ' Autor........: J.Feuerstein'
Print ' '
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),
        @DelDataOfSiteId nvarchar(32),
        @DelDataOfSiteId_ObjectFromSiteID nvarchar(32),
        @DelDataOfSiteId_ObjectOwningSiteID nvarchar(32),
        @SiteID_NEW nvarchar(32),
        @DoCleanData bit,
        @PrintEvery int

-- #ToDo: Please define SiteID to delte data from JobMgr DB
set @DelDataOfSiteId='#SiteID#'
set @DelDataOfSiteId='%'
-- #ToDo: Setup: print del ervery n'times
set @PrintEvery=10
-- #ToDo: Setup: @DoCleanData Action
--  if @DoCleanData = 1 then Data is deleted
--  if @DoCleanData = 0 then only Print is done
set @DoCleanData = 1

select @DeleteN=count(*)
from dbo.Objects_tbl
  WHERE dbo.Objects_tbl.ObjectClass IN ('IR','I')
   and dbo.Objects_tbl.ObjectUgMaster_Type = 'NP'
   and dbo.Objects_tbl.ObjectFromSiteID like @DelDataOfSiteId

print('.')
Print '-------------------------------------------------------------------------'
print('CleanUp in DB....: ' + '#DB_N#')
print('Items/Ref NP.....: [' + @DelDataOfSiteId + ']')
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 dbo.Objects_tbl
  WHERE dbo.Objects_tbl.ObjectClass IN ('IR','I')
   and dbo.Objects_tbl.ObjectUgMaster_Type = 'NP'
   and dbo.Objects_tbl.ObjectFromSiteID like @DelDataOfSiteId



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, @DelDataOfSiteId_ObjectFromSiteID, @DelDataOfSiteId_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:[' + @DelDataOfSiteId_ObjectFromSiteID + ':' + @ObjectClass + ':' + @ObjectCliName + ']')
    end

    if @DoCleanData = 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
