Print ''
Print '---------------------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Procedures    : [Process Duplicated Items]'
Print ' Creation      : 06.09.2023/A.Becker+J.Fes'
Print '---------------------------------------------------------------------------------------'
use [#DB_N#]

Print ''
Print ' '
Print '---------------------------------------------------------------------------------------'
Print '--------------------- R E P O R T S - Process Duplicated Items ------------------------'
Print '---------------------------------------------------------------------------------------'
Print ''


--------------------------------------------------------------------------------
Print '-- [Report_912_ListDulicatedItems] --------------------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_912_ListDulicatedItems")
Drop Procedure Report_912_ListDulicatedItems
GO
CREATE PROCEDURE Report_912_ListDulicatedItems
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14) = NULL,
        @ObjectClass nvarchar(10) = NULL
       )


AS
begin


DECLARE @itemsWithDupes TABLE (Id UNIQUEIDENTIFIER PRIMARY KEY)

INSERT INTO @itemsWithDupes(Id)
SELECT ITEM.Id
FROM (
	SELECT ObjectFromSiteID, ObjectName
	FROM dbo.Objects_tbl ITEM
	WHERE ITEM.ObjectClass='I' and ITEM.ObjectFromSiteID = @ObjectFromSiteID
	GROUP BY ObjectFromSiteID, ObjectName
	HAVING COUNT(1)>1
) Q
JOIN dbo.Objects_tbl ITEM ON ITEM.ObjectClass='I' and ITEM.ObjectFromSiteID = @ObjectFromSiteID AND ITEM.ObjectFromSiteID=Q.ObjectFromSiteID AND ITEM.ObjectName=Q.ObjectName


-- COMPUTE: Get all items to delete by grouping on Site and ObjectName sorted by date desc only using group row indexes > 1
DECLARE @itemsWithDuplicateIndex TABLE (ItemId UNIQUEIDENTIFIER PRIMARY KEY,
                                        ObjectFromSiteID NVARCHAR(255), ObjectName NVARCHAR(255), DuplicateIndex INT,
                                        INDEX IX1 NONCLUSTERED(ObjectFromSiteID, ObjectName))
INSERT INTO @itemsWithDuplicateIndex (ItemId, ObjectFromSiteID, ObjectName, DUPLICATES.DuplicateIndex)
SELECT DUPLICATES.Id, ObjectFromSiteID, ObjectName, DUPLICATES.DuplicateIndex
FROM (SELECT ITEM.Id, ITEM.ObjectFromSiteID, ITEM.ObjectName
           , ROW_NUMBER() OVER (PARTITION BY ITEM.ObjectFromSiteID, ITEM.ObjectName ORDER BY ITEM.ObjectLastModDate DESC) AS DuplicateIndex
	  FROM dbo.Objects_tbl ITEM
	  WHERE EXISTS (SELECT 1 FROM @itemsWithDupes DUPES WHERE DUPES.Id=ITEM.Id)
	  --WHERE ITEM.ObjectClass='I' and ITEM.ObjectFromSiteID = @ObjectFromSiteID
) DUPLICATES
--WHERE DUPLICATES.DuplicateIndex > 1

-- selet / List to Delete
select ITEM.Id
     , CASE WHEN DUPES.DuplicateIndex>1 THEN 'DELETE' ELSE 'KEEP' END DelKeepStatus
     , ITEM.ObjectFromSiteID, ITEM.ObjectName, ITEM.ObjectLastModDate
from @itemsWithDuplicateIndex DUPES
join dbo.Objects_tbl ITEM ON ITEM.Id=DUPES.ItemId
--WHERE EXISTS (SELECT 1 FROM @itemsWithDuplicateIndex DUP WHERE DUP.ObjectFromSiteID=DUPES.ObjectFromSiteID AND DUP.ObjectName=DUPES.ObjectName AND DUP.DuplicateIndex>1)
ORDER BY ITEM.ObjectFromSiteID, ITEM.ObjectName, DUPES.DuplicateIndex
--WHERE ITEM.ObjectName like '%Debug%'
end
GO


Print '-- [Report_912_ListDulicatedItemsSetToAttr10] --------------------------------------'
If Exists (select Name from sysobjects where type = "P" and
           Name = "Report_912_ListDulicatedItemsSetToAttr10")
Drop Procedure Report_912_ListDulicatedItemsSetToAttr10
GO
CREATE PROCEDURE Report_912_ListDulicatedItemsSetToAttr10
       (
        @ObjectFromSiteID nvarchar(32) ,
        @ProcStateCol nvarchar(14),
        @ObjectClass nvarchar(10)
       )


AS
begin


  DECLARE @reportResult TABLE (Id UNIQUEIDENTIFIER, DelKeepStatus NVARCHAR(255), ObjectFromSiteID NVARCHAR(255), ObjectName NVARCHAR(255), ObjectLastModDate datetime)


  UPDATE Objects_tbl
  SET ObjectAttr10 = NULL
  FROM dbo.Objects_tbl
  Where
      ObjectFromSiteID = @ObjectFromSiteID
  AND ObjectAttr10 IN ('KEEP','DELETE')


  INSERT INTO @reportResult(Id, DelKeepStatus, ObjectFromSiteID, ObjectName, ObjectLastModDate)
  EXEC Report_912_ListDulicatedItems @ObjectFromSiteID

  UPDATE Objects2_tbl
  SET ObjectAttr10 = RES.DelKeepStatus
  --SELECT *
  FROM @reportResult RES
  JOIN dbo.Objects_tbl Objects2_tbl ON Objects2_tbl.Id=RES.Id

  SELECT @@rowcount [ItemData Affected]

end
GO


Print ''
Print '---------------------------------------------------------------------------------------'
Print ' R E P O R T S imported to #DB_N#'
Print '---------------------------------------------------------------------------------------'
GO

