--- Please follow the below comments to insert SQL statements.
-- Dash copy fallen behind
--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON

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

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



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

--------- Insert your SQL statements here 
DECLARE @dashcopy XML = NULL

CREATE TABLE #OldestJobs (
	ArchGroupId INT, CopyId INT, ToBeCopiedJobCount INT, JobId INT, CommCellId INT, StartTime INT)
	
CREATE TABLE #AuxCopySummary (
	ArchGroupId INT, CopyId INT, SourceCopyId INT, AuxCopyScheduled INT, AuxCopyAlertGB INT, AuxCopyAlertDays INT, AuxCopyDelayedDays INT, AuxCopyBehindDate BIGINT,
	Last24HrCopiedAppSize BIGINT, Last24HrCopiedMediaSize BIGINT, FallenBehindJobCount INT, FallenBehindAppSize BIGINT,
	OldestToBeCopiedJob INT, OldestToBeCopiedJobCommCellId INT, OldestToBeCopiedJobStartTime INT,
	JobsToBeCopiedCount INT, TotalTobeCopiedJobApplicationSize FLOAT, EstTotalTobeCopiedJobMediaSize FLOAT,
	AvgAuxCopyJobThroughput FLOAT, EstCompletionDateForAllData DATETIME, CorruptedJobsCountOnSrcCopy INT)

CREATE TABLE #PartialJobs (jobId INT, commCellId INT, jobEndTime INT, copyId INT, afSize BIGINT, appSize BIGINT)

CREATE TABLE #DataLogJobs (jobId INT, commCellId INT, copyId INT, dataAndLog INT)

DECLARE @shouldCollectFromMMEntityProp INT = 0
IF EXISTS (SELECT top 1 EntityId FROM	MMEntityProp WITH (NOLOCK) WHERE	EntityType = 2 AND propertyName = 'AuxCopyAlertEnabled')
BEGIN
	SET @shouldCollectFromMMEntityProp = 1
END

-- Get All SP and SP Copy Names
INSERT INTO #AuxCopySummary
SELECT  AG.id, AGC.id, (CASE WHEN sourceCopyId = 0 THEN AG.defaultCopy ELSE AGC.sourceCopyId END), 0, 
        CASE WHEN @shouldCollectFromMMEntityProp = 1 THEN -1 ELSE (CASE WHEN AG.AuxCopyAlertGB = 0 THEN -1 ELSE AG.AuxCopyAlertGB END) END, 
        CASE WHEN @shouldCollectFromMMEntityProp = 1 THEN -1 ELSE (CASE WHEN AG.AuxCopyAlertDays = 0 THEN -1 ELSE AG.AuxCopyAlertDays END) END, 
		AGC.delayedDays, 
        0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, NULL, 0 
FROM    archGroup AG WITH (NOLOCK), archGroupCopy AGC WITH (NOLOCK)
WHERE   AG.id = AGC.archGroupId
        AND AGC.id <> AG.defaultCopy
        AND AGC.id <> AG.defaultSnapCopy
        AND AGC.isSnapCopy = 0  -- PICK ONLY NON SNAP COPIES
        AND AGC.type <> 5
        AND AGC.isActive = 1
        
IF @@ROWCOUNT = 0
BEGIN
    SET @dashcopy = NULL
    GOTO CX_EXIT    
END

UPDATE	C SET AuxCopyScheduled = 1
FROM	#AuxCopySummary C
		INNER JOIN (
		SELECT	DISTINCT AE.policyId, AE.copyId
		FROM	TM_AssocEntity AE WITH (NOLOCK) 
			INNER JOIN TM_SubTask ST WITH (NOLOCK) ON ST.taskId = AE.taskId AND ST.operationType = 4003 AND ST.deleted = 0 AND ST.uninstalled = 0
			INNER JOIN TM_Task T WITH (NOLOCK) ON ST.taskId = T.taskId AND T.deleted = 0 AND T.disabled = 0 AND T.uninstalled = 0 AND T.invalid = 0
		) S	ON (S.copyId = C.copyId OR S.policyId = C.ArchGroupId AND S.copyId = 0) 

-- The values of AuxCopyAlertGB property are actually in TB.
UPDATE	C SET AuxCopyAlertGB = T.longlongVal*1024
FROM	#AuxCopySummary C
		INNER JOIN (
		SELECT	EntityId, longlongVal
		FROM	MMEntityProp WITH (NOLOCK)
		WHERE	EntityType = 2 AND propertyName = 'AuxCopyAlertGB' AND longlongVal > 0
			AND EntityId IN (
				SELECT	EntityId
				FROM	MMEntityProp WITH (NOLOCK)
				WHERE	EntityType = 2 AND propertyName = 'AuxCopyAlertEnabled' AND intVal = 1)
		) T ON C.CopyId = T.EntityId

UPDATE	C SET AuxCopyAlertDays = T.intVal
FROM	#AuxCopySummary C
		INNER JOIN (
		SELECT	EntityId, intVal
		FROM	MMEntityProp WITH (NOLOCK)
		WHERE	EntityType = 2 AND propertyName = 'AuxCopyAlertDays' AND intVal > 0
			AND EntityId IN (
				SELECT	EntityId
				FROM	MMEntityProp WITH (NOLOCK)
				WHERE	EntityType = 2 AND propertyName = 'AuxCopyAlertEnabled' AND intVal = 1)
		) T ON C.CopyId = T.EntityId


UPDATE	#AuxCopySummary SET AuxCopyBehindDate = CASE 
			WHEN AuxCopyAlertDays >= DATEDIFF(DD, '1970-01-01', GETUTCDATE()) THEN 0 
			ELSE DATEDIFF(SS, '1970-01-01', DATEADD(DD, -(AuxCopyAlertDays+AuxCopyDelayedDays), GETUTCDATE())) END
WHERE	AuxCopyAlertDays > 0			

UPDATE	#AuxCopySummary SET FallenBehindJobCount += T.CopyBehindJobCount, FallenBehindAppSize += T.appSize
FROM	(
		SELECT	J.archGrpCopyId, COUNT(DISTINCT J.jobId) AS CopyBehindJobCount, SUM(B.totalUncompBytes) AS appSize
		FROM	JMBkpStats B WITH (NOLOCK)
				INNER JOIN JMJobDataStats J WITH (NOLOCK) ON B.jobId = J.jobId AND B.commCellId = J.commCellId
				INNER JOIN #AuxCopySummary C ON J.archGrpCopyId = C.CopyId
		WHERE	J.dataType IN (1, 4) AND (J.disabled & 257) = 0 AND J.status IN (101, 102, 103)
			AND B.servEndDate < C.AuxCopyBehindDate
		GROUP BY J.archGrpCopyId) T
WHERE	#AuxCopySummary.CopyId = T.archGrpCopyId

UPDATE	#AuxCopySummary SET FallenBehindJobCount += T.CopyBehindJobCount, FallenBehindAppSize += T.appSize
FROM	(
		SELECT	J.archGrpCopyId, COUNT(DISTINCT J.jobId) AS CopyBehindJobCount, SUM(A.totalBackupSize) AS appSize
		FROM	JMAdminJobStatsTable A WITH (NOLOCK)
				INNER JOIN JMJobDataStats J WITH (NOLOCK) ON A.jobId = J.jobId AND A.commCellId = J.commCellId
				INNER JOIN #AuxCopySummary C ON J.archGrpCopyId = C.CopyId
		WHERE	J.dataType IN (1, 4) AND (J.disabled & 257) = 0 AND J.status IN (101, 102, 103)
			AND A.servEnd < C.AuxCopyBehindDate
		GROUP BY J.archGrpCopyId) T
WHERE	#AuxCopySummary.CopyId = T.archGrpCopyId



-- Find the oldest job Id, CommCell Id and Start Time
INSERT INTO #OldestJobs
SELECT	J.archGrpId, J.archGrpCopyId, COUNT(DISTINCT jobId), MIN(J.jobId), J.commCellId, 0
FROM	JMJobDataStats J WITH (NOLOCK) INNER JOIN #AuxCopySummary C ON J.archGrpCopyId = C.CopyId
WHERE	(J.disabled & 257) = 0 AND J.status IN (101, 102, 103)
GROUP BY J.archGrpId, J.archGrpCopyId, J.commCellId

UPDATE	#OldestJobs SET StartTime = B.servStartDate
FROM	JMBkpStats B
WHERE	#OldestJobs.JobId = B.jobId AND #OldestJobs.CommCellId = B.commCellId

UPDATE	#OldestJobs SET StartTime = A.servStart
FROM	JMAdminJobStatsTable A
WHERE	#OldestJobs.JobId = A.jobId AND #OldestJobs.CommCellId = A.commCellId

UPDATE	#AuxCopySummary SET OldestToBeCopiedJobStartTime = S.StartTime, JobsToBeCopiedCount = S.ToBeCopiedJobCount
FROM	(SELECT	CopyId, MIN(StartTime) AS StartTime, SUM(ToBeCopiedJobCount) AS ToBeCopiedJobCount
		FROM	#OldestJobs
		GROUP BY archGroupId, CopyId) S
WHERE	#AuxCopySummary.CopyId = S.CopyId

UPDATE	#AuxCopySummary SET OldestToBeCopiedJob = J.JobId, OldestToBeCopiedJobCommCellId = J.CommCellId
FROM	#OldestJobs J
WHERE	#AuxCopySummary.CopyId = J.CopyId AND #AuxCopySummary.OldestToBeCopiedJobStartTime = J.StartTime

DROP TABLE #OldestJobs

UPDATE	#AuxCopySummary SET TotalTobeCopiedJobApplicationSize += T.appSize, EstTotalTobeCopiedJobMediaSize += T.appSize
FROM 
	(SELECT	J.archGrpCopyId, SUM(A.totalBackupSize) AS appSize
	FROM	JMJobDataStats J WITH (NOLOCK) 
		INNER JOIN #AuxCopySummary C 
		ON J.archGrpCopyId = C.CopyId AND (J.disabled & 257) = 0 AND J.status IN (101, 102, 103)
		INNER JOIN JMAdminJobStatsTable A WITH (NOLOCK) 
		ON J.jobId = A.jobId AND J.commCellId = A.commCellId AND J.dataType IN (1, 4)
	GROUP BY J.archGrpCopyId) T
WHERE	#AuxCopySummary.CopyId = T.archGrpCopyId

UPDATE	#AuxCopySummary SET TotalTobeCopiedJobApplicationSize += T.appSize, EstTotalTobeCopiedJobMediaSize += T.bkpSize
FROM 
	(SELECT	J.archGrpCopyId, SUM(B.totalUncompBytes) AS appSize, SUM(B.totalBackupSize) AS bkpSize
	FROM	JMJobDataStats J WITH (NOLOCK) 
		INNER JOIN #AuxCopySummary C 
		ON J.archGrpCopyId = C.CopyId AND (J.disabled & 257) = 0 AND J.status IN (101, 102, 103)
		INNER JOIN JMBkpStats B WITH (NOLOCK) 
		ON J.jobId = B.jobId AND J.commCellId = B.commCellId AND J.dataType IN (1, 4)
	GROUP BY J.archGrpCopyId) T
WHERE	#AuxCopySummary.CopyId = T.archGrpCopyId


-- To be copied data&log jobs
INSERT INTO #DataLogJobs
SELECT	J.jobId, J.commCellId, J.archGrpCopyId, 0
FROM	JMJobDataStats J WITH (NOLOCK) 
	INNER JOIN #AuxCopySummary C 
	ON J.archGrpCopyId = C.CopyId AND (J.disabled & 257) = 0 AND J.status IN (101, 102, 103) AND J.dataType = 4
	
UPDATE	#DataLogJobs SET dataAndLog = 1
FROM	#DataLogJobs LJ
	INNER JOIN JMJobDataStats J WITH (NOLOCK) 
	ON LJ.jobId = J.jobId AND LJ.commCellId = J.commCellId AND LJ.copyId = J.archGrpCopyId AND J.dataType = 1

-- Count appSize only once for data&log jobs
UPDATE	#AuxCopySummary SET FallenBehindAppSize -= T.appSize
FROM 
	(SELECT	J.copyId, SUM(B.totalUncompBytes) AS appSize
	FROM	#DataLogJobs J
		INNER JOIN JMBkpStats B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId AND dataAndLog = 1
		INNER JOIN #AuxCopySummary C ON J.CopyId = C.CopyId			
	WHERE	B.servEndDate < C.AuxCopyBehindDate
	GROUP BY J.copyId) T
WHERE	#AuxCopySummary.CopyId = T.copyId

UPDATE	#AuxCopySummary SET TotalTobeCopiedJobApplicationSize -= T.appSize
FROM 
	(SELECT	J.copyId, SUM(B.totalUncompBytes) AS appSize
	FROM	#DataLogJobs J
		INNER JOIN JMBkpStats B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId AND dataAndLog = 1
	GROUP BY J.copyId) T
WHERE	#AuxCopySummary.CopyId = T.copyId

DROP TABLE #DataLogJobs


-- Partially copiesd jobs
INSERT INTO #PartialJobs
SELECT	J.jobId, J.commCellId, 0, J.archGrpCopyId, SUM(AFC.physicalSize), 0
FROM	JMJobDataStats J WITH (NOLOCK) 
	INNER JOIN #AuxCopySummary C 
	ON J.archGrpCopyId = C.CopyId AND (J.disabled & 257) = 0 AND J.status IN (101, 102, 103)
	INNER JOIN archFile AF WITH (NOLOCK) 
	ON J.jobId = AF.jobId AND J.commCellId = AF.commCellId AND J.dataType = AF.fileType AND AF.isValid = 1
	INNER JOIN archFileCopy AFC WITH (NOLOCK) 
	ON AF.id = AFC.archFileId AND AF.commCellId = AFC.commCellId AND J.archGrpCopyId = AFC.archCopyId AND AFC.physicalSize > 0
GROUP BY J.jobId, J.commCellId, J.archGrpCopyId

UPDATE	#PartialJobs SET jobEndTime = B.servEndDate, appSize = B.totalUncompBytes*(1.0*afSize/B.totalBackupSize)
FROM	JMBkpStats B WITH (NOLOCK)
WHERE	#PartialJobs.jobId = B.jobId AND #PartialJobs.commCellId = B.commCellId AND B.totalBackupSize > 0

UPDATE	#PartialJobs SET jobEndTime = A.servEnd, appSize = afSize
FROM	JMAdminJobStatsTable A WITH (NOLOCK)
WHERE	#PartialJobs.jobId = A.jobId AND #PartialJobs.commCellId = A.commCellId AND A.totalBackupSize > 0

-- Subtract partially copied job size
UPDATE	#AuxCopySummary SET FallenBehindAppSize -= T.appSize
FROM 
	(SELECT	J.copyId, SUM(appSize) AS appSize
	FROM	#PartialJobs J
			INNER JOIN #AuxCopySummary C ON J.CopyId = C.CopyId
	WHERE	J.jobEndTime < C.AuxCopyBehindDate 
	GROUP BY J.copyId) T
WHERE	#AuxCopySummary.CopyId = T.copyId

UPDATE	#AuxCopySummary SET TotalTobeCopiedJobApplicationSize -= T.appSize, EstTotalTobeCopiedJobMediaSize -= T.afSize
FROM 
	(SELECT	copyId, SUM(appSize) AS appSize, SUM(afSize) AS afSize
	FROM	#PartialJobs
	GROUP BY copyId) T
WHERE	#AuxCopySummary.CopyId = T.copyId

DROP TABLE #PartialJobs

-- Get EstTotalTobeCopiedJobMediaSize
UPDATE	#AuxCopySummary 
SET FallenBehindAppSize = FallenBehindAppSize/1024/1024,
	TotalTobeCopiedJobApplicationSize = TotalTobeCopiedJobApplicationSize/1024.0/1024.0/1024.0,
	EstTotalTobeCopiedJobMediaSize = EstTotalTobeCopiedJobMediaSize/1024.0/1024.0/1024.0

CREATE TABLE #DedupeCopy (archGrpCopyId INT, activeStoreId INT, dedupeFlags INT, 
	dataRead BIGINT, dataWritten BIGINT, dedupRatio FLOAT, dedupSaving DECIMAL(10,2)
)

INSERT	INTO #DedupeCopy
SELECT	CopyId, 0, dedupeFlags, 0, 0, 0, 0
FROM	#AuxCopySummary AC
	INNER JOIN archGroupCopy AGC WITH (NOLOCK) ON AC.CopyId = AGC.id
WHERE	((AGC.dedupeFlags & 262144) > 0)

UPDATE	T
SET		activeStoreId = CS.SIDBStoreId
FROM	#DedupeCopy T INNER JOIN archCopySIDBStore CS ON T.archGrpCopyId = CS.CopyId
						INNER JOIN IdxSIDBStore I ON CS.SIDBStoreId = I.SIDBStoreId 
WHERE	I.SealedTime = 0

IF (COL_LENGTH('RptStorageUsage', 'allAppSize') IS NOT NULL)
BEGIN
	SET @sqlstr = N'
	DECLARE @MaxDate DATETIME = (SELECT MAX(startDT) FROM RptStorageUsage WITH (NOLOCK) WHERE type = 1)

	UPDATE	T SET dataRead = S.allAppSize, dataWritten = S.allMediaSize, dedupRatio = S.dedupRatio
	FROM	#DedupeCopy T
		INNER JOIN (
			SELECT	copyId, SUM(allAppSize) AS allAppSize, SUM(allMediaSize) AS allMediaSize, MAX(dedupRatio) AS dedupRatio
			FROM	RptStorageUsage WITH (NOLOCK)
			WHERE	startDT = @MaxDate AND type = 1
			GROUP BY copyId
			) S ON T.archGrpCopyId = S.copyId'

	EXEC sp_executesql @sqlstr
END
ELSE
IF OBJECT_ID('DedupCopySaving', 'P') IS NOT NULL
BEGIN
	EXEC DedupCopySaving 0
END
ELSE
BEGIN

	-- Total Application Size of fully copied jobs on each copies
	CREATE TABLE #CopyReadSize (archGrpCopyId INT, dataRead BIGINT)

	;WITH JobDataStats (jobId, commCellId, archGrpCopyId) AS
	(SELECT	J.jobId, J.commCellId, J.archGrpCopyId
			FROM	JMJobDataStats J WITH (NOLOCK)
					INNER JOIN #DedupeCopy C ON J.archGrpCopyId = C.archGrpCopyId
			WHERE	J.status <> 1000
			GROUP BY J.jobId, J.commCellId, J.archGrpCopyId
			HAVING MAX(J.status) = 100 AND MIN(J.status) = 100
	)
	INSERT	INTO #CopyReadSize
	SELECT	S.archGrpCopyId, SUM(J.totalUncompBytes)
	FROM	JMBkpStats J WITH (NOLOCK)
			INNER JOIN JobDataStats S ON J.jobId = S.jobId AND J.commCellId = S.commCellId
	GROUP BY S.archGrpCopyId
	UNION ALL
	SELECT	S.archGrpCopyId, SUM(J.totalBackupSize)
	FROM	JMAdminJobStatsTable J WITH (NOLOCK)
			INNER JOIN JobDataStats S ON J.jobId = S.jobId AND J.commCellId = S.commCellId
	GROUP BY S.archGrpCopyId

	-- Partially copied jobs
	CREATE TABLE #PartialJobSize (jobId INT, commCellId INT, archGrpCopyId INT, 
			totalAppSize BIGINT, totalBackupSize BIGINT, dataRead BIGINT)

	INSERT	INTO #PartialJobSize
	SELECT	J.jobId, J.commCellId, J.archGrpCopyId, 0, 0, 0
	FROM	JMJobDataStats J WITH (NOLOCK)
			INNER JOIN #DedupeCopy C ON J.archGrpCopyId = C.archGrpCopyId
	WHERE	J.status <> 1000
	GROUP BY J.jobId, J.commCellId, J.archGrpCopyId
	HAVING MAX(J.status) IN (102, 103) OR MIN(J.status) = 100 AND MAX(J.status) = 101

	UPDATE	#PartialJobSize
	SET		totalAppSize = J.totalUncompBytes, totalBackupSize = J.totalBackupSize
	FROM	JMBkpStats J WITH (NOLOCK)
	WHERE	#PartialJobSize.jobId = J.jobId AND #PartialJobSize.commCellId = J.commCellId

	UPDATE	#PartialJobSize
	SET		totalAppSize = J.totalBackupSize, totalBackupSize = J.totalBackupSize
	FROM	JMAdminJobStatsTable J WITH (NOLOCK)
	WHERE	#PartialJobSize.jobId = J.jobId AND #PartialJobSize.commCellId = J.commCellId

	UPDATE	#PartialJobSize
	SET	dataRead = CASE WHEN totalBackupSize > 0 THEN totalAppSize*(1000*T.dataCompressed/totalBackupSize)/1000 ELSE T.dataCompressed END
	FROM	(SELECT J.jobId, J.commCellId, J.archGrpCopyId, SUM(AFC.physicalSize) AS dataCompressed
			FROM	#PartialJobSize J
					INNER JOIN archFile AF WITH (NOLOCK)
					ON AF.jobId = J.jobId AND AF.commCellId = J.commCellId
					INNER JOIN archFileCopy AFC WITH (NOLOCK) 
					ON AFC.archFileId = AF.id AND AFC.commCellId = AF.commCellId AND AFC.archCopyId = J.archGrpCopyId
			GROUP BY J.jobId, J.commCellId, J.archGrpCopyId
			) T
	WHERE	#PartialJobSize.jobId = T.jobId AND #PartialJobSize.commCellId = T.commCellId AND #PartialJobSize.archGrpCopyId = T.archGrpCopyId

	INSERT	INTO #CopyReadSize
	SELECT	archGrpCopyId, SUM(dataRead) AS dataRead
	FROM	#PartialJobSize
	GROUP BY archGrpCopyId

	DROP TABLE #PartialJobSize

	-- Include all volumes except physically deleted volumes
	-- Volumes of deleted copies using GDSP are under GDSP host copy
	;WITH CopyWriteSize (activeStoreId, dataWritten) AS
	(	SELECT	ActiveStoreId, SUM(SS.PhysicalBytesMB)*1024*1024
		FROM	IdxSIDBStore S
				INNER JOIN (
					SELECT	SIDBStoreAliasName, MAX(SIDBStoreId) AS ActiveStoreId
					FROM	IdxSIDBStore WITH (NOLOCK)
					WHERE	SIDBStoreId > 0
					GROUP BY SIDBStoreAliasName
				) SN ON S.SIDBStoreAliasName = SN.SIDBStoreAliasName
				INNER JOIN (
					SELECT	SIDBStoreId, SUM(CAST(V.PhysicalBytesMB AS BIGINT)) AS PhysicalBytesMB
					FROM	MMVolume V WITH (NOLOCK)
					WHERE	SIDBStoreId > 0 AND SiloStatus <> 3 AND (Attributes & 512) = 0
					GROUP BY SIDBStoreId
				) SS ON S.SIDBStoreId = SS.SIDBStoreId
		GROUP BY ActiveStoreId
	)
	UPDATE	#DedupeCopy
	SET		dedupRatio = 1.0*W.dataWritten/R.dataRead
	FROM	(SELECT	DC.activeStoreId, SUM(RS.dataRead) AS dataRead
			FROM	#CopyReadSize RS
					INNER JOIN #DedupeCopy DC ON RS.archGrpCopyId = DC.archGrpCopyId
			GROUP BY DC.activeStoreId) R
			INNER JOIN CopyWriteSize W ON W.activeStoreId = R.activeStoreId
			INNER JOIN #DedupeCopy C ON C.activeStoreId = R.activeStoreId
	WHERE	R.dataRead > 0

	DROP TABLE #CopyReadSize
END
UPDATE	#AuxCopySummary SET EstTotalTobeCopiedJobMediaSize = TotalTobeCopiedJobApplicationSize*dedupRatio
FROM	#DedupeCopy T
WHERE	#AuxCopySummary.CopyId = T.archGrpCopyId AND dedupRatio > 0

CREATE	TABLE #Last24HrCopiedSize (CopyId INT, ArchFileSize BIGINT, AppSize BIGINT, MediaSize BIGINT)

DECLARE @queryStr NVARCHAR(MAX)
DECLARE @csRelId INT = (SELECT releaseId FROM APP_Client WHERE id = 2)

IF @csRelId < 15
BEGIN
	SET @queryStr = N'
	DECLARE	@24HrAgo INT = dbo.GetUnixTime(GETUTCDATE()) - 24*3600

	INSERT	INTO #Last24HrCopiedSize
	SELECT	ACM.archCopyId, SUM(ACM.physicalSize), SUM((1.0*ACM.physicalSize/J.totalBackupSize)*J.totalUncompBytes), 0
	FROM	archChunkMapping ACM WITH (NOLOCK)
			INNER JOIN archChunk AC WITH (NOLOCK) ON ACM.archChunkId = AC.id AND ACM.chunkCommCellId = AC.commCellId
			INNER JOIN archFile AF WITH (NOLOCK) ON ACM.archFileId = AF.id AND ACM.commCellId = AF.commCellId AND AF.isValid = 1
			INNER JOIN JMBkpStats J WITH (NOLOCK) ON AF.jobId = J.jobId AND AF.commCellId = J.commCellId
	WHERE	J.totalBackupSize > 0 AND AC.createTime > @24HrAgo
	GROUP BY ACM.archCopyId

	INSERT	INTO #Last24HrCopiedSize
	SELECT	ACM.archCopyId, SUM(ACM.physicalSize), SUM(ACM.physicalSize), 0
	FROM	archChunkMapping ACM WITH (NOLOCK)
			INNER JOIN archChunk AC WITH (NOLOCK) ON ACM.archChunkId = AC.id AND ACM.chunkCommCellId = AC.commCellId
			INNER JOIN archFile AF WITH (NOLOCK) ON ACM.archFileId = AF.id AND ACM.commCellId = AF.commCellId AND AF.isValid = 1
			INNER JOIN JMAdminJobStatsTable J WITH (NOLOCK) ON AF.jobId = J.jobId AND AF.commCellId = J.commCellId
	WHERE	J.totalBackupSize > 0 AND AC.createTime > @24HrAgo
	GROUP BY ACM.archCopyId'
END
ELSE
BEGIN
	SET @queryStr = N'
	DECLARE	@24HrAgo INT = dbo.GetUnixTime(GETUTCDATE()) - 24*3600

	INSERT	INTO #Last24HrCopiedSize
	SELECT	ACM.archCopyId, SUM(ACM.physicalSize), SUM((1.0*ACM.physicalSize/J.totalBackupSize)*J.totalUncompBytes), 0
	FROM	archChunkMapping ACM WITH (NOLOCK)
			INNER JOIN archChunk AC WITH (NOLOCK) ON ACM.archChunkId = AC.id AND ACM.chunkCommCellId = AC.commCellId
			INNER JOIN JMBkpStats J WITH (NOLOCK) ON ACM.jobId = J.jobId AND ACM.commCellId = J.commCellId
	WHERE	J.totalBackupSize > 0 AND AC.createTime > @24HrAgo
	GROUP BY ACM.archCopyId

	INSERT	INTO #Last24HrCopiedSize
	SELECT	ACM.archCopyId, SUM(ACM.physicalSize), SUM(ACM.physicalSize), 0
	FROM	archChunkMapping ACM WITH (NOLOCK)
			INNER JOIN archChunk AC WITH (NOLOCK) ON ACM.archChunkId = AC.id AND ACM.chunkCommCellId = AC.commCellId
			INNER JOIN JMAdminJobStatsTable J WITH (NOLOCK) ON ACM.jobId = J.jobId AND ACM.commCellId = J.commCellId
	WHERE	J.totalBackupSize > 0 AND AC.createTime > @24HrAgo
	GROUP BY ACM.archCopyId'
END
EXEC sp_executesql @queryStr

UPDATE	#Last24HrCopiedSize SET MediaSize = CASE WHEN ISNULL(T.dedupRatio, 1) < 1 THEN S.AppSize*T.dedupRatio ELSE S.ArchFileSize END
FROM	#Last24HrCopiedSize S
		LEFT OUTER JOIN #DedupeCopy T ON S.CopyId = T.archGrpCopyId AND T.dedupRatio > 0
		
UPDATE	#AuxCopySummary SET Last24HrCopiedAppSize = T.AppSize, Last24HrCopiedMediaSize = T.MediaSize
FROM	(SELECT CopyId, SUM(AppSize) AS AppSize, SUM(MediaSize) AS MediaSize
		FROM	#Last24HrCopiedSize
		GROUP BY CopyId) T
WHERE	#AuxCopySummary.CopyId = T.CopyId

DROP TABLE #Last24HrCopiedSize
DROP TABLE #DedupeCopy

-- Get average throughput from last 10 aux copy jobs
CREATE TABLE #LastAuxJobs (copyId INT, jobId INT, bytesCopied BIGINT, writeTime INT)	

CREATE TABLE #ActiveAuxJobs (copyId INT, jobId INT, bytesCopied BIGINT, writeTime INT, 
		startTime DATETIME, displayState VARCHAR(255), percentComplete INT, delayReason NVARCHAR(2048))	

INSERT INTO #LastAuxJobs
SELECT	T.archGrpCopyID, T.jobId, T.BytesXferred, T.writeTime
FROM
	(SELECT J.archGrpCopyID, J.jobId, J.BytesXferred, CAST((JA.totalWriteTime*(J.BytesXferred*1.0)/JA.EX_TotalSuccess) AS INT) AS writeTime,
			ROW_NUMBER() OVER (PARTITION BY J.archGrpCopyId ORDER BY J.jobId DESC) AS rowNo
	FROM   #AuxCopySummary A
		INNER JOIN JMAdminJobStatsTable JA WITH (NOLOCK) ON JA.archGrpID = A.ArchGroupId 
			AND (JA.archGrpCopyId = A.copyId OR JA.archGrpCopyId = 0) AND JA.opType IN (13, 104) AND JA.EX_TotalSuccess > 0 
		INNER JOIN JMDataCopiedStatsTable J WITH (NOLOCK) ON J.jobId = JA.jobId AND J.commCellId = JA.commCellId AND J.archGrpCopyID = A.copyId
	) T
WHERE	rowNo <= 10


INSERT #ActiveAuxJobs
SELECT J.archGrpCopyID, J.jobId, J.BytesXferred, CASE WHEN JA.ObjectsDone <= 0 THEN 0 ELSE (elapsedTime*(J.BytesXferred*1.0/JA.ObjectsDone)) END,
       dbo.UTCToClientLocalTime(dbo.GetDateTime(JA.jobStartTime), 2), dbo.GetJobStateName(JA.displayState), JA.percentcomplete, 
       dbo.NormalizeForXML(ISNULL(JA.DelayReason, ''))
FROM   #AuxCopySummary A
	INNER JOIN RunningAdminJobs JA WITH (NOLOCK) ON JA.archGrpID = A.ArchGroupId 
		AND (JA.archGrpCopyId = A.copyId OR JA.archGrpCopyId = 0) AND JA.opType IN (13, 104)
	INNER JOIN JMDataCopiedStatsTable J WITH (NOLOCK) ON J.jobId = JA.jobId AND J.commCellId = JA.commCellId AND J.archGrpCopyID = A.copyId

INSERT #LastAuxJobs
SELECT J.copyID, J.jobId, J.bytesCopied, J.writeTime
FROM   #ActiveAuxJobs J
	LEFT OUTER JOIN (SELECT DISTINCT copyId FROM #lastAuxJobs) LA ON J.copyId = LA.copyId
WHERE LA.copyId IS NULL

UPDATE	#AuxCopySummary
SET		AvgAuxCopyJobThroughput = (CASE WHEN TP.wTime > 0 THEN TP.BytesTransfer/TP.wTime ELSE 0 END)*3600.0/(1024*1024*1024)
FROM	#AuxCopySummary T INNER JOIN 
	(SELECT copyId, SUM(bytesCopied) BytesTransfer, SUM(writeTime) wTime
	FROM	#LastAuxJobs
	GROUP BY copyId
	) TP ON T.copyId = TP.copyId 

DROP TABLE #LastAuxJobs

DECLARE @currentTime DATETIME = GetUTCDate()

-- if this is zero then we calculate estimated completion time in terms of application size else in terms of media size.
IF @csRelId >= 15
BEGIN
  UPDATE	#AuxCopySummary
  SET		EstCompletionDateForAllData = (CASE WHEN TotalTobeCopiedJobApplicationSize = 0 THEN @currentTime 
			  ELSE DATEADD(SS, (CASE WHEN ((TotalTobeCopiedJobApplicationSize*(3600)/AvgAuxCopyJobThroughput) > 2147483647) THEN 2147483647 
								     ELSE (TotalTobeCopiedJobApplicationSize*(3600)/AvgAuxCopyJobThroughput) 
							    END), @currentTime) 
			  END)
  WHERE	AvgAuxCopyJobThroughput > 0
END
ELSE
BEGIN
  UPDATE	#AuxCopySummary
  SET		EstCompletionDateForAllData = (CASE WHEN EstTotalTobeCopiedJobMediaSize = 0 THEN @currentTime 
		  	ELSE DATEADD(SS, (CASE WHEN ((EstTotalTobeCopiedJobMediaSize*(3600)/AvgAuxCopyJobThroughput) > 2147483647) THEN 2147483647 
			  					   ELSE (EstTotalTobeCopiedJobMediaSize*(3600)/AvgAuxCopyJobThroughput) 
				  			  END), @currentTime) 
			  END)
  WHERE	AvgAuxCopyJobThroughput > 0
END

-- Get the corrupted jobs count on source copy
IF OBJECT_ID('tempdb.dbo.#CorruptedJobsCountOnSrcCopy') IS NOT NULL DROP TABLE #CorruptedJobsCountOnSrcCopy
CREATE TABLE #CorruptedJobsCountOnSrcCopy (CopyId INT, SrcCopyId INT, CorruptedJobsCount INT PRIMARY KEY(CopyId, SrcCopyId))
IF OBJECT_ID('tempdb.dbo.#CorruptedJobsOnSrcCopy') IS NOT NULL DROP TABLE #CorruptedJobsOnSrcCopy
CREATE TABLE #CorruptedJobsOnSrcCopy (JobId INT, CommCellId INT, CopyId INT, SrcCopyId INT PRIMARY KEY(JobId, CommCellId, CopyId, SrcCopyId))
IF @csRelId > 15
BEGIN
	SET @queryStr = N'
		INSERT INTO #CorruptedJobsOnSrcCopy
		SELECT	DISTINCT BackupJobId, CommCellId, DestCopyId, SrcCopyId
		FROM	ArchChunkToReplicate R WITH (READCOMMITTED)
		WHERE	(ErrorCode = 30017 OR ErrorCode = 30032 OR ErrorCode = 30074   OR ErrorCode = 1208 OR ErrorCode = 65103)
		UNION	
		SELECT	DISTINCT BackupJobId, CommCellId, DestCopyId, SrcCopyId
		FROM	ArchChunkToReplicateHistory R WITH (READCOMMITTED)
		WHERE	(ErrorCode = 30017 OR ErrorCode = 30032 OR ErrorCode = 30074   OR ErrorCode = 1208 OR ErrorCode = 65103)'

	EXEC sp_executesql @queryStr

	/*
	DELETE	CJ
	FROM	#CorruptedJobsOnSrcCopy CJ
			INNER JOIN JMJobDataStats JDS ON CJ.JobId = JDS.jobId AND CJ.CommCellId = JDS.commCellId AND CJ.CopyId = JDS.archGrpCopyId
	WHERE	(JDS.disabled & 257 /*CVA_DONOTCOPY_FLAG+CVA_AGED_DATA_FLAG*/) > 0
	*/

	DELETE	CJ
	FROM	#CorruptedJobsOnSrcCopy CJ
			INNER JOIN JMJobDataStats JDS ON CJ.JobId = JDS.jobId AND CJ.CommCellId = JDS.commCellId AND CJ.SrcCopyId = JDS.archGrpCopyId
	WHERE	(JDS.disabled & 257 /*CVA_DONOTCOPY_FLAG+CVA_AGED_DATA_FLAG*/) > 0

	INSERT INTO #CorruptedJobsCountOnSrcCopy
	SELECT CopyId, SrcCopyId, COUNT(JobId)
	FROM   #CorruptedJobsOnSrcCopy CJ
	GROUP BY CopyId, SrcCopyId
	UPDATE	S
	SET		CorruptedJobsCountOnSrcCopy = C.CorruptedJobsCount
	FROM	#AuxCopySummary S, #CorruptedJobsCountOnSrcCopy C
	WHERE	S.CopyId = C.CopyId
			AND S.SourceCopyId = C.SrcCopyId
END

IF OBJECT_ID('tempdb.dbo.#CorruptedJobsCountOnSrcCopy') IS NOT NULL DROP TABLE #CorruptedJobsCountOnSrcCopy
IF OBJECT_ID('tempdb.dbo.#CorruptedJobsOnSrcCopy') IS NOT NULL DROP TABLE #CorruptedJobsOnSrcCopy

SET @dashcopy = (
	SELECT	S.CopyId AS '@CopyId',
			1.0*Last24HrCopiedAppSize/1024/1024/1024 AS '@AppSizeGBCopiedIn24Hr', 
			1.0*Last24HrCopiedMediaSize/1024/1024/1024 AS '@MediaSizeGBCopiedIn24Hr',
			(CASE WHEN OldestToBeCopiedJobStartTime = 0 THEN 0
				  ELSE DATEDIFF( day, dbo.GetDateTime(OldestToBeCopiedJobStartTime), GetUTCDate()) 
			 END) AS '@FallenBehindDays',
			FallenBehindJobCount AS '@FallenBehindJobCount',
			FallenBehindAppSize  AS '@FallenBehindAppSizeMB',
			JobsToBeCopiedCount  AS '@JobsToCopyCount',
			OldestToBeCopiedJob  AS '@OldestJobId',
			(CASE WHEN OldestToBeCopiedJobStartTime = 0 THEN '' 
				  ELSE dbo.UTCToClientLocalTime(dbo.GetDateTime(OldestToBeCopiedJobStartTime), 2) 
			END) AS '@OldestJobStartTime',
			TotalTobeCopiedJobApplicationSize AS '@TotalToBeCopiedJobsApplicationSizeGB',
			EstTotalTobeCopiedJobMediaSize  AS '@EstTotalToBeCopiedJobsMediaSizeGB',
			(CASE WHEN (TotalTobeCopiedJobApplicationSize = 0 AND EstTotalTobeCopiedJobMediaSize = 0 ) THEN -1 
                                  WHEN EstCompletionDateForAllData IS NULL THEN -2 
                                  WHEN (EstCompletionDateForAllData > '2038/1/1') THEN -3 
                                  ELSE dbo.GetUnixTime(EstCompletionDateForAllData) 
                        END)  AS '@EstCompletionDateForAllData',

			AvgAuxCopyJobThroughput AS '@AvgAuxCopyJobThroughput',
			AuxCopyScheduled AS '@AuxCopyScheduled',
			AuxCopyAlertGB AS '@AuxCopyAlertGB', 
			AuxCopyAlertDays AS '@AuxCopyAlertDays',
			A.jobId AS '@ActiveJobId',
			A.startTime AS '@ActiveJobStartTime',
			A.displayState AS '@ActiveJobState',
			A.percentComplete AS '@ActiveJobProgress',
			A.delayReason AS '@ActiveJobDelayReason',
			S.CorruptedJobsCountOnSrcCopy AS '@CorruptedJobsCountOnSrcCopy',
			S.AuxCopyDelayedDays AS '@AuxCopyDelayedDays'
	FROM	#AuxCopySummary S 
		LEFT OUTER JOIN #ActiveAuxJobs A ON A.CopyId = S.CopyId
FOR XML PATH('DashCopy')
)

CX_EXIT:
IF OBJECT_ID('tempdb.dbo.#OldestJobs') IS NOT NULL DROP TABLE #OldestJobs
IF OBJECT_ID('tempdb.dbo.#DataLogJobs') IS NOT NULL DROP TABLE #DataLogJobs
IF OBJECT_ID('tempdb.dbo.#PartialJobs') IS NOT NULL DROP TABLE #PartialJobs
IF OBJECT_ID('tempdb.dbo.#DedupeCopy') IS NOT NULL DROP TABLE #DedupeCopy
IF OBJECT_ID('tempdb.dbo.#Last24HrCopiedSize') IS NOT NULL DROP TABLE #Last24HrCopiedSize
IF OBJECT_ID('tempdb.dbo.#LastAuxJobs') IS NOT NULL DROP TABLE #LastAuxJobs
IF OBJECT_ID('tempdb.dbo.#AuxCopySummary') IS NOT NULL DROP TABLE #AuxCopySummary
IF OBJECT_ID('tempdb.dbo.#ActiveAuxJobs') IS NOT NULL DROP TABLE #ActiveAuxJobs
IF OBJECT_ID('tempdb.dbo.#CorruptedJobsCountOnSrcCopy') IS NOT NULL DROP TABLE #CorruptedJobsCountOnSrcCopy
IF OBJECT_ID('tempdb.dbo.#CorruptedJobsOnSrcCopy') IS NOT NULL DROP TABLE #CorruptedJobsOnSrcCopy
    
SET @surveyXML = (
	SELECT
		@dashcopy
	FOR XML PATH ('FallenBehindCopy') 
	-- Replace <<RESULTS_NAME>> with specific name
)

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

--SELECT cast (@surveyXML as XML)

SET NOCOUNT OFF

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