Print ''
Print '----------------------------------------------------'
Print ' #Doc.........: Deletes Delete !!all Data!! of defined SiteId from JobMgr Database'
print ' #Doc.........: please use only cutomised:'
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 @ActionDoneCounter INT
DECLARE @ActionDoneN INT
SET @ActionDoneN = 0
SET @ActionDoneCounter = 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),
        @DoAction bit,
        @PrintEvery int

-- #ToDo: Please define SiteID to delte data from JobMgr DB
set @DelDataOfSiteId='#SiteID#'
-- #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 = 1

select @ActionDoneN=count(*)
from dbo.Objects_tbl
  WHERE dbo.Objects_tbl.ObjectFromSiteID = @DelDataOfSiteId

print('.')
Print '-------------------------------------------------------------------------'
print('Delete in DB.....: ' + '#DB_N#')
print('Data of Site.....: [' + @DelDataOfSiteId + ']')
print('N to Delete......: [' + CONVERT(nvarchar(64),@ActionDoneN) + ']')
Print '-------------------------------------------------------------------------'

-- Curor fuer alle zu durchlaufenden Objekte erstellen
declare tblCursor cursor fast_forward for
select Id, ObjectCliName, ObjectClass, ObjectFromSiteID, ObjectOwningSiteID
from dbo.Objects_tbl
  WHERE dbo.Objects_tbl.ObjectFromSiteID = @DelDataOfSiteId

set nocount on
open tblCursor
 -- alle Objekte durchlaufen die durch den Cursor durchlaufen
 while 1=1
  begin
    SET @ActionDoneCounter = @ActionDoneCounter + 1
    fetch next from tblCursor into @Id, @ObjectCliName, @ObjectClass, @DelDataOfSiteId_ObjectFromSiteID, @DelDataOfSiteId_ObjectOwningSiteID
    -- Ausstieg wenn nichts zu löschen ist
    if (@ActionDoneN =0)
    begin
      print('Result...........: No Data found to delete')
      break
    end
    -- Exit if last element is processed
    if (@@fetch_status <> 0) break
    -- #Debug: Austieg für test
    -- if (@ActionDoneCounter >=2) break
    -- print('DeleteMOd:[' + CONVERT(nvarchar(64),@ActionDoneCounter % 10))

    if (@ActionDoneCounter = 1) or (@ActionDoneCounter = @ActionDoneN) or (@ActionDoneCounter % @PrintEvery = 0)
    begin
       print('Delete:[' + CONVERT(nvarchar(64),@ActionDoneCounter) + '/' + CONVERT(nvarchar(64),@ActionDoneN) + ':' + Str((100.00*@ActionDoneCounter/@ActionDoneN),6,2)  + '%]' +
             ' Obj:[' + @DelDataOfSiteId_ObjectFromSiteID + ':' + @ObjectClass + ':' + @ObjectCliName + ']')
    end

    if @DoAction = 1
    begin
     -- loeschen der Daten ausfuehren

     -- Step: loeschen _SolidEdgeRefference Objects
     delete from SolidEdge_AssebmlyReferences_tbl Where ParentJMObjectTbl_ID  = @Id

     -- Step: loeschen _SolidEdge Objects
     delete from SolidEdge_ExternalData_tbl Where JobMgrObjectTbl_ID  = @Id

     -- 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 tblCursor
deallocate tblCursor




GO

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