Print ''
Print '-------------------------------------------------------------------------'
Print ' Table         : [JobMgr_05_CleanUpObjects_tbl]'
Print ' Creation Date : 23.07.2010 00:59'
Print ' LastMod Date  : 28.02.2011 22:53'
Print ' Autor         : J.Feuerstein'
Print ' using Database: #DB_N#'
Print '-------------------------------------------------------------------------'

use [#DB_N#]

-- set nocount on
--- @DB/57502100183P01/D1/specification/57502100183p01_dr01
if (object_id('zztmpClean1_tbl') is not null)  Drop table zztmpClean1_tbl
if (object_id('zztmpClean2_tbl') is not null)  Drop table zztmpClean2_tbl
if (object_id('zztmpClean3_tbl') is not null)  Drop table zztmpClean3_tbl
if (object_id('zztmpClean4_tbl') is not null)  Drop table zztmpClean4_tbl
if (object_id('zztmpClean5_tbl') is not null)  Drop table zztmpClean5_tbl
if (object_id('temp..#zztmpClean3_tbl') is not null) Drop table #zztmpClean3_tbl
if (object_id('temp..#zztmpClean3_tbl') is not null) Drop table #zztmpClean3_tbl

Print '-- Step: find Datasets mit verweisen auf mehrere IRs ---------'
select *
into zztmpClean1_tbl
from
(
SELECT
  Count(ObjectId) AS GroupCounter,
  dbo.Objects_Ir_Rel_tbl.ObjectId
FROM
  dbo.Objects_Ir_Rel_tbl
GROUP BY
  dbo.Objects_Ir_Rel_tbl.ObjectId
) As SelectSub
 where [GroupCounter]>1 ;

-- print ("##Debug: zztmpClean1_tbl ###############################################")
-- select * from zztmpClean1_tbl;
-- print ("##Debug:################################################################")


Print '-- Step: Repair DS -------------------------------------------------------'
select *
into zztmpClean2_tbl
from
(
SELECT
  dbo.Objects_Ir_Rel_tbl.Id AS Objects_Ir_Rel_tbl_Id,
  Ir.Id AS IrId,
  Ds.Id AS DsId,
  Ir.ObjectCliName AS IrCliName,
  Ds.ObjectCliName AS DsCliName,
  Ir.ObjectFromSiteID AS FromSiteID,
  Ir.ObjectPuid AS IrPuid,
  Ds.ObjectPuid AS DsPuid
FROM
  dbo.zztmpClean1_tbl
  INNER JOIN dbo.Objects_Ir_Rel_tbl ON (dbo.zztmpClean1_tbl.ObjectId = dbo.Objects_Ir_Rel_tbl.ObjectId)
  LEFT OUTER JOIN dbo.Objects_tbl Ir ON (dbo.Objects_Ir_Rel_tbl.IrId = Ir.Id)
  LEFT OUTER JOIN dbo.Objects_tbl Ds ON (dbo.Objects_Ir_Rel_tbl.ObjectId = Ds.Id)
)
As SelectSub
order BY FromSiteID ;

-- print ("##Debug: zztmpClean1_tbl ###############################################")
-- select * from zztmpClean2_tbl;
-- print ("##Debug:################################################################")

declare @Objects_Ir_Rel_tbl_Id uniqueidentifier,
        @IrId uniqueidentifier,
        @DsId uniqueidentifier,
        @IrPuid nvarchar(15),
        @DsPuid nvarchar(15),
        @IrCliName nvarchar(255),
        @DsCliName nvarchar(255),
        @FromSiteID nvarchar(32)

declare zztmpClean2_tbl_Cursor cursor for
        select Objects_Ir_Rel_tbl_Id, IrId, DsId, IrPuid, DsPuid, IrCliName, DsCliName, FromSiteID from zztmpClean2_tbl
open zztmpClean2_tbl_Cursor
 while 1=1
  begin
    fetch next from zztmpClean2_tbl_Cursor into @Objects_Ir_Rel_tbl_Id, @IrId, @DsId , @IrPuid, @DsPuid , @IrCliName, @DsCliName, @FromSiteID
    if (@@fetch_status <> 0) break

    -- Info an Datset hängen
    UPDATE
     dbo.Objects_tbl
    SET
     ObjectSelectByMarker = 'Import.Update'
    WHERE
     dbo.Objects_tbl.Id = @IrId

    if CharIndex(@IrCliName, @DsCliName) = 0
     begin
      -- print('@IrId......: [' + convert(varchar(64),@IrId) + ']')
      print('Cleanup:')
      print('@IrCliName.: [' + @FromSiteID + ":" + @IrCliName + ']')
      print('@DsCliName.: [' + @DsCliName + ']')
      -- @Objects_Ir_Rel_tbl_Id datensatz loeschen
      delete from Objects_Ir_Rel_tbl
       where Objects_Ir_Rel_tbl.Id = @Objects_Ir_Rel_tbl_Id
     end
  end
close zztmpClean2_tbl_Cursor
deallocate zztmpClean2_tbl_Cursor


--------------------------------------------------------------------------------
-- cleanup Objects_Ir_Rel_tbl eintraege basierend auf IRs die keinen verweis auf einen Dataset haben'
Print '-- cleanup Objects_Ir_Rel_tbl:[IR] --------------------------------------'
-- select * FROM dbo.Objects_Ir_Rel_tbl
DELETE FROM dbo.Objects_Ir_Rel_tbl
WHERE
  Objects_Ir_Rel_tbl.Id IN
(
SELECT
    dbo.Objects_Ir_Rel_tbl.Id
FROM
  dbo.Objects_Ir_Rel_tbl
  INNER JOIN dbo.Objects_tbl IR ON (dbo.Objects_Ir_Rel_tbl.IrId = IR.Id)
  LEFT OUTER JOIN dbo.Objects_tbl DS ON (dbo.Objects_Ir_Rel_tbl.ObjectId = DS.Id)
WHERE
  DS.Id IS NULL
)

--------------------------------------------------------------------------------
-- cleanup Objects_Ir_Rel_tbl eintraege basierend auf Ds fuer die keinen verweis auf einen IR mehr existiert'
Print '-- cleanup Objects_Ir_Rel_tbl:[DS] --------------------------------------'

select *
into zztmpClean3_tbl
from(
SELECT
  dbo.Objects_Ir_Rel_tbl.Id As Objects_Ir_Rel_tbl_Id,
  DS.Id AS DsId,
  IR.Id AS IrId,
  DS.ObjectCliName AS DsCliName,
  DS.ObjectFromSiteID AS FromSiteId
FROM
  dbo.Objects_tbl DS
  INNER JOIN dbo.Objects_Ir_Rel_tbl ON (DS.Id = dbo.Objects_Ir_Rel_tbl.ObjectId)
  LEFT OUTER JOIN dbo.Objects_tbl IR ON (dbo.Objects_Ir_Rel_tbl.IrId = IR.Id)
WHERE
  IR.Id IS NULL
) As SelectSub
order BY FromSiteID ;

-- CleanUp Datasets ---------------
-- SELECT * FROM dbo.Objects_tbl
Delete FROM dbo.Objects_tbl
WHERE
  dbo.Objects_tbl.Id IN (SELECT dbo.zztmpClean3_tbl.DsId FROM dbo.zztmpClean3_tbl);

-- CleanUp Objects_Ir_Rel_tbl -----
-- SELECT dbo.Objects_Ir_Rel_tbl.Id FROM dbo.Objects_Ir_Rel_tbl
Delete from dbo.Objects_Ir_Rel_tbl
WHERE
  dbo.Objects_Ir_Rel_tbl.Id IN (SELECT dbo.zztmpClean3_tbl.Objects_Ir_Rel_tbl_Id FROM dbo.zztmpClean3_tbl);
--------------------------------------------------------------------------------

Print ('-- Cleanup Dulicatet DS ------------------------------------------------')
select *
INTO zztmpClean4_tbl
from
(
SELECT
  Count(ObjectClass) AS [GroupCounter] ,
  dbo.Objects_tbl.ObjectCliName,
  dbo.Objects_tbl.ObjectFromSiteID ,
  dbo.Objects_tbl.ObjectClass
FROM
  dbo.Objects_tbl
GROUP BY
  dbo.Objects_tbl.ObjectCliName,
  dbo.Objects_tbl.ObjectFromSiteID,
  dbo.Objects_tbl.ObjectClass
) As SelectSub
 where [GroupCounter]>1 ;

-- print ("##Debug:#########################################")
-- print ("##Debug:")
-- print ("##Debug:")
-- select * from zztmpClean4_tbl;

-- step alle Duplicat Datensätze ermitten und durchnummerieren via rn = row_number()

SELECT
rn = row_number()
OVER (PARTITION BY zztmpClean4_tbl.ObjectCliName,
                   zztmpClean4_tbl.ObjectFromSiteID,
                   zztmpClean4_tbl.ObjectClass
                   ORDER BY zztmpClean4_tbl.ObjectClass)
, dbo.Objects_tbl.id As Id
, dbo.Objects_tbl.ObjectCliName
, dbo.Objects_tbl.ObjectFromSiteID
, dbo.Objects_tbl.ObjectClass
INTO zztmpClean5_tbl
FROM
  zztmpClean4_tbl,
  dbo.Objects_tbl
WHERE
  dbo.Objects_tbl.ObjectCliName = zztmpClean4_tbl.ObjectCliName AND
  dbo.Objects_tbl.ObjectFromSiteID = zztmpClean4_tbl.ObjectFromSiteID AND
  dbo.Objects_tbl.ObjectClass = zztmpClean4_tbl.ObjectClass ;


-- zeige was aufgeräumt wird
-- Print ''
-- Print 'View Clean Up Dublicates: ObjectPuid + ObjectFromSiteID + ObjectClass'
-- select rn, Id, ObjectCliName, ObjectFromSiteID, ObjectClass from zztmpClean5_tbl;

-- SELECT * FROM dbo.Objects_tbl
Delete FROM dbo.Objects_tbl
WHERE
  dbo.Objects_tbl.Id IN (SELECT dbo.zztmpClean5_tbl.Id FROM dbo.zztmpClean5_tbl where rn >1);


Print ('-- CleanUp Process Results ---------------------------------------------')
DELETE FROM dbo.JobControl_ObjectsProcessData_tbl
-- select * FROM dbo.JobControl_ObjectsProcessData_tbl
WHERE
dbo.JobControl_ObjectsProcessData_tbl.ID IN
(
SELECT
  dbo.JobControl_ObjectsProcessData_tbl.ID
FROM
  dbo.JobControl_ObjectsProcessData_tbl
  LEFT OUTER JOIN dbo.Objects_tbl ON (dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID = dbo.Objects_tbl.Id)
WHERE
  dbo.Objects_tbl.Id IS NULL
  )

goto EndeExit


EndeExit:
 Print '.'
 Print '------------------------------------------------------------------------'
 Print (' performed on Database: #DB_N#')
 Print '------------------------------------------------------------------------'

GO
