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

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

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

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



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

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

DECLARE @LastNDays int = 30

-- Get Dedupe ratio per storage policy copy

DECLARE @copies XML
DECLARE @TimeRangeBegin datetime = DATEADD(DAY, -@LastNDays, GETDATE())
DECLARE @NoOfPrimaryCopies INT = 0
DECLARE @NoOfDeDupPrimaryCopies INT = 0
DECLARE @TotalAppSize FLOAT = 0
DECLARE @TotalsizeOnMedia FLOAT = 0
DECLARE @offlineDDBSize BIGINT  = 0

DECLARE @TmpStartDate	DATETIME
DECLARE @TmpEndDate	DATETIME
DECLARE @Today			DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

DECLARE @csTimeZone    NVARCHAR(1024) = ''
SELECT  @csTimeZone = dbo.GetClientTimeZone(2)

IF ISNULL(@csTimeZone, '') = ''
BEGIN
    SELECT @csTimeZone = timeZone FROM APP_CommCell WITH (NOLOCK) WHERE id = 2
    SELECT @csTimeZone = TimeZoneStdName FROM SchedTimeZone WITH (NOLOCK)
    WHERE  TimeZoneName = SUBSTRING(@csTimeZone, CHARINDEX(':', @csTimeZone, CHARINDEX(':', @csTimeZone, 0) + 1) + 1, 255)
END


SET	@TmpStartDate = dbo.LocalToUTCTime(@TimeRangeBegin, @csTimeZone)

DECLARE @TmpStartTime	INT = dbo.GetUnixTime(@TmpStartDate)

IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tmpSPCopies'))
	DROP TABLE #tmpSPCopies	

CREATE TABLE #tmpSPCopies(
	StoragePolicy NVARCHAR(256), 
	Copy NVARCHAR(256),
	DeDupe VARCHAR(4),
	AppSizeGB  DECIMAL(20, 2),
	SizeOnMediaGB DECIMAL(20, 2),
	offlineDDBSize DECIMAL(20,2),
	DedupeRatio DECIMAL(20, 2),
	Jobs INT,
	Clients INT)

CREATE TABLE #tblSPCopy (
	PolicyId INT, PolicyName NVARCHAR(512), CopyId INT, CopyName NVARCHAR(512), dedupeFlags INT)

INSERT	INTO #tblSPCopy
SELECT	AG.id AS PolicyId, AG.name AS PolicyName, AGC.id AS CopyId, AGC.name AS CopyName, AGC.dedupeFlags
				FROM	archGroup AG WITH (NOLOCK) INNER JOIN archGroupCopy AGC WITH (NOLOCK)
					 ON AGC.archGroupId = AG.id AND AGC.id = AG.defaultCopy
				WHERE	AG.type = 1 AND AG.id > 1

SELECT @NoOfPrimaryCopies = COUNT(CopyId) FROM #tblSPCopy
SELECT @NoOfDeDupPrimaryCopies = COUNT(CopyId) FROM #tblSPCopy WHERE ((dedupeFlags & 262144 /*$$(CVA_SIDB_STORE_ENABLED_FLAG)*/) > 0)

CREATE TABLE #tblSummary (
	CopyId INT, AppSize BIGINT, SizeOnMedia BIGINT, nJobs INT, nClients INT)

CREATE TABLE #offlineDDBs (SIDBStoreId INTEGER , CopyId INTEGER , TotalSize DECIMAL(20, 2))

INSERT INTO #offlineDDBs
SELECT MV.SIDBStoreId , 0 , SUM (CAST(PhysicalBytesMB AS BIGINT))
FROM  MMVolume MV WITH (NOLOCK)
INNER JOIN IdxSIDBStore ISS WITH (NOLOCK) ON MV.SIDBStoreId = ISS.SIDBStoreId
WHERE ISS.SealedTime > 0 AND (MV.Attributes & 512) = 0 AND MV.SiloStatus <> 3
GROUP BY MV.SIDBStoreId 

UPDATE T
SET CopyId = ACS.CopyId
FROM #offlineDDBs T INNER JOIN archCopySIDBStore ACS WITH (NOLOCK) ON ACS.SIDBStoreId = T.SIDBStoreId

INSERT	INTO #tblSummary
SELECT	C.CopyId, SUM(B.totalUncompBytes), 0, COUNT(DISTINCT B.jobId), COUNT(DISTINCT A.clientId) 	
FROM	#tblSPCopy C
	INNER JOIN JMJobDataStats D WITH (NOLOCK) 
		ON D.archGrpCopyId = C.CopyId AND D.dataType IN (1, 4) AND D.status = 100 AND (D.disabled & 256) = 0
	INNER JOIN JMBkpStats B WITH (NOLOCK) 
		ON D.jobId = B.jobId AND D.commCellId = B.commCellId 
		AND (C.PolicyId = B.dataArchGrpId AND D.dataType = 1 OR C.PolicyId = B.logArchGrpId AND B.dataBackedUp = 0 AND D.dataType = 4)
	INNER JOIN APP_Application A WITH (NOLOCK) ON A.id = B.appId
WHERE	B.servStartDate >= @TmpStartTime AND B.status IN (1, 3, 14) AND B.commCellId = 2 
		AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
		AND B.appType < 1000 AND B.appType NOT BETWEEN 600 AND 700
GROUP BY C.CopyId

UPDATE	#tblSummary SET SizeOnMedia = T.SizeOnMedia
FROM	(
		SELECT	C.CopyId, SUM(D.sizeOnMedia) AS sizeOnMedia
		FROM	#tblSPCopy C
			INNER JOIN JMJobDataStats D WITH (NOLOCK) 
				ON D.archGrpCopyId = C.CopyId AND D.dataType IN (1, 4) AND D.status = 100 AND (D.disabled & 256) = 0
			INNER JOIN JMBkpStats B WITH (NOLOCK) 
				ON D.jobId = B.jobId AND D.commCellId = B.commCellId 
		WHERE	B.servStartDate >= @TmpStartTime AND B.status IN (1, 3, 14) AND B.commCellId = 2 
				AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
				AND B.appType < 1000 AND B.appType NOT BETWEEN 600 AND 700
		GROUP BY C.CopyId
		) T
WHERE	#tblSummary.CopyId = T.CopyId

INSERT INTO #tmpSPCopies
SELECT	PolicyName, CopyName, (CASE WHEN ((SP.dedupeFlags & 262144 /*$$(CVA_SIDB_STORE_ENABLED_FLAG)*/) > 0) THEN 'Yes' ELSE 'No' END) AS Dedupe, 
	ROUND(ISNULL(AppSize/1024.0/1024.0/1024.0, 0), 2) AS AppSizeGB,
	ROUND(ISNULL(SizeOnMedia/1024.0/1024.0/1024.0, 0), 2) AS SizeOnMediaGB,
	ROUND(ISNULL((OfflineDDB.TotalSize)/1024.0, 0), 2) AS offlineDDBSize,
	ISNULL(CASE WHEN ((SP.dedupeFlags & 262144 /*$$(CVA_SIDB_STORE_ENABLED_FLAG)*/) = 0) OR AppSize = 0 OR AppSize < SizeOnMedia THEN 0
			ELSE ROUND(100.0*(AppSize - SizeOnMedia)/AppSize, 2) END, 0) AS DedupeRatio,
	ISNULL(nJobs, 0) AS Jobs, ISNULL(nClients, 0) AS Clients
FROM	#tblSPCopy SP
	LEFT OUTER JOIN #tblSummary S ON S.CopyId = SP.CopyId
	LEFT OUTER JOIN #offlineDDBs offlineDDB ON offlineDDB.CopyId = SP.CopyId
ORDER BY PolicyName, CopyName DESC

DROP TABLE #tblSPCopy
DROP TABLE #tblSummary

SELECT	@TotalAppSize = SUM(AppSizeGB), @TotalsizeOnMedia = SUM(SizeOnMediaGB) 
FROM	#tmpSPCopies

SELECT @offlineDDBSize = SUM(TotalSize) FROM #offlineDDBs
SET @copies = ( 
SELECT 
	dbo.NormalizeForXML(StoragePolicy) '@storagePolicy',
	dbo.NormalizeForXML(Copy) '@copy',
	SUM(AppSizeGB)  '@appSizeGB',
	SUM(SizeOnMediaGB) '@sizeOnMediaGB',
	SUM(offlineDDBSize) '@offlineDDBSize',
	CASE WHEN DeDupe = 'Yes'
	     THEN ISNULL(CASE WHEN SUM(AppSizeGB) = 0 OR SUM(AppSizeGB) < SUM(SizeOnMediaGB) THEN 0 ELSE ROUND(100.0*(SUM(AppSizeGB) - SUM(SizeOnMediaGB))/SUM(AppSizeGB), 2) END, 0) 
	     ELSE  -1  
	     END AS '@dedupeRatio',
	SUM(Jobs) '@jobs',
	SUM(Clients) '@clients'
FROM #tmpSPCopies 
GROUP BY StoragePolicy, Copy,DeDupe
FOR XML PATH ('Copy')
)

SET @surveyXML = (
	SELECT
		@LastNDays AS '@LastNDays',
		@NoOfPrimaryCopies AS '@PrimCopies',
		@NoOfDeDupPrimaryCopies AS '@DeDupPrimCopies',
		@TotalAppSize AS '@TotalAppSizeGB',
		@TotalsizeOnMedia AS '@TotalSizeOnMediaGB',
		@offlineDDBSize AS '@offlineDDBSize' ,
 		@copies
	FOR XML PATH ('DedupeRatioByCopy') 
	-- Replace <<RESULTS_NAME>> with specific name
)

IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#tmpSPCopies'))
	DROP TABLE #tmpSPCopies 




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