--- Please follow the below comments to insert SQL statements.

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON

DECLARE @LogDate AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @queryId AS INTEGER = 35
DECLARE @surveyXML NVARCHAR(MAX)

--------- END - GENERATED CODE ---------



--------- BEGIN SURVEY QUERY ---------


--------- Insert your SQL statements here 

-- Query to get DeDup Deleted Archive File Count
-- Get the deleted archive files from MMDeletedAF table per Dedupe store
DECLARE @nreleaseId INT = 1
SET @nreleaseId =(SELECT releaseId from APP_Client  where id =2)

DECLARE	@datePast7Days BIGINT 
select @datePast7Days=isnull(value,7) from MMConfigs(NOLOCK) where name like 'DA_CONFIG_DELETEDAF_EVENT_DAYS_TO_CONSIDER'
and value>=nmin and value <=nMax
SET	@datePast7Days = dbo.getUnixTime(DATEADD(day,-@datePast7Days,GETUTCDATE()))

IF(@nreleaseId = 14)
BEGIN
	SET @surveyXML = (
		SELECT 
			Store.SIDBStoreName AS '@SIDBStoreName', 
			ISNULL(T.dafcount,0) AS '@DeletedAFCount', 
			ISNULL(T2.dafcount7,0) AS '@OlderDeletedAFCount',
			(CASE WHEN Store.SealedTime > 0 THEN 'Sealed' ElSE 'Active' END) AS '@SealedOrNot', 
			CL.name AS '@DDBHost'
		FROM
			 APP_Client CL WITH (READUNCOMMITTED) 
			 INNER JOIN 
			 IdxSIDBStore Store WITH (READUNCOMMITTED) ON Store.ClientId = CL.id AND Store.Status = 0 AND (Store.flags & 2097152) = 0
			 LEFT OUTER JOIN
			 (
				SELECT 
					sidbstoreid, COUNT(*) AS dafcount
				FROM MMDeletedAF AS DAF WITH (READUNCOMMITTED) WHERE SIDBStoreId > 0
				GROUP BY SIDBStoreId
				--HAVING COUNT(*) > 20000
			  ) T ON Store.sidbstoreid = T.SIDBStoreId
			  LEFT OUTER JOIN
		     (
				SELECT sidbstoreid, COUNT(*) AS dafcount7
				FROM MMDeletedAF AS DAF WITH (READUNCOMMITTED) WHERE SIDBStoreId > 0
				AND  DAF.DeletedTime < @datePast7Days 
				GROUP BY SIDBStoreId
			  )T2 ON Store.sidbstoreid = T2.SIDBStoreId
			  
		FOR XML PATH ('DeDupDeletedAFCountXML') 
	)
END
ELSE
IF(@nreleaseId >= 15)
BEGIN
	DECLARE @tempResults table(StoreId int, StoreName varchar(256), dafCount bigint, olderDafCount bigint , sealStatus varchar(10), clientList nvarchar(max))

	INSERT INTO @tempResults
	SELECT 
		DISTINCT Store.SIDBStoreId, Store.SIDBStoreName, ISNULL(T.dafcount,0), ISNULL(T2.dafcount7,0), (CASE WHEN Store.SealedTime > 0 THEN 'Sealed' ElSE 'Active' END), 
		CL.name
	FROM
	IdxSIDBStore Store WITH (READUNCOMMITTED) 
	INNER JOIN IdxSIDBSubStore SubStore WITH (READUNCOMMITTED) ON Store.SIDBStoreId = SubStore.SIDBStoreId
	INNER JOIN App_Client CL WITH (READUNCOMMITTED) ON CL.id = SubStore.ClientId
	LEFT OUTER JOIN
	(
		  SELECT 
				 sidbstoreid, 
				 COUNT(*) AS dafcount
		  FROM MMDeletedAF AS DAF WITH (READUNCOMMITTED) WHERE SIDBStoreId > 0
		  GROUP BY SIDBStoreId
		--  HAVING COUNT(*) > 20000
	) T ON Store.sidbstoreid = T.SIDBStoreId
	LEFT OUTER JOIN  
	(SELECT 	
			sidbstoreid, COUNT(*) AS dafcount7
			FROM MMDeletedAF AS DAF WITH (READUNCOMMITTED) WHERE SIDBStoreId > 0
			AND DAF.DeletedTime < @datePast7Days 
			GROUP BY SIDBStoreId
	)T2 ON Store.sidbstoreid = T2.SIDBStoreId 
	
	WHERE Store.Status = 0
	AND (Store.flags & 2097152) = 0

	
	SET @surveyXML =
	(
		SELECT 
			T.StoreId AS '@SIDBStoreId',
			T.StoreName AS '@SIDBStoreName', 
			T.dafCount AS '@DeletedAFCount',
			T.olderDafCount AS '@OlderDeletedAFCount', 
			T.sealStatus AS '@SealedOrNot',  
			(SELECT STUFF((SELECT ', '+clientList FROM @tempResults T1 WHERE T1.StoreId = T.StoreId FOR XML PATH('')), 1, 2, ''))  AS '@DDBHost'
		FROM @tempResults T
		GROUP BY T.StoreId, T.StoreName, T.dafCount,  T.olderDafCount, T.sealStatus
		FOR XML PATH ('DeDupDeletedAFCountXML') 
	)
END

--------- END SURVEY QUERY ---------

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

DECLARE @EndTime AS BIGINT = dbo.GetUnixTime(GETUTCDATE())
SET @outputXML = 
(
	SELECT
		@queryId AS '@QueryId',
		@EndTime AS '@LogDate',
		(@EndTime - @LogDate) AS '@QueryRunningTime',
		@surveyXML
	FOR XML PATH('Rpt_CSSXMLDATA')
)

SET NOCOUNT OFF

--------- END - GENERATED CODE ---------