--@querytype 	CSV
--Name:- RecoverabilitySLA

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

-- This is used for Recoverability SLA and Copy Redundancy SLA for Health report.

DECLARE	@csReleaseId INT
SELECT	@csReleaseId = releaseId FROM APP_Client WHERE id = 2

IF @csReleaseId < 16
	RETURN
	
DECLARE @csHighestSP INT
SELECT 	@csHighestSP = MAX(CASE WHEN HighestSP>100 THEN HighestSP/100 ELSE HighestSP END) FROM simInstalledPackages WITH (NOLOCK) WHERE clientId = 2

IF @csReleaseId = 16 AND @csHighestSP < 7
	RETURN


DECLARE @groupStr varchar(256);
DECLARE @groupStrName Nvarchar(MAX)='';
DECLARE @isPrivateRunning varchar(256)='';
DECLARE @freequencyMode	 NVARCHAR(MAX) ='0';
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
BEGIN
    SELECT @freequencyMode = mode from #MetricsInputParams
END
IF EXISTS(SELECT name FROM GXGlobalParam WHERE name = 'CommservSurveyRunning')
BEGIN
	SELECT @isPrivateRunning = value from GXGlobalParam WITH (NOLOCK) where name ='CommservSurveyRunning'
END

IF ((@isPrivateRunning = 'Metrics Reporting') OR (@freequencyMode ='1'))
BEGIN
	SELECT @groupStr = value from GXGlobalParam where name = 'CommservSurveyPrivateSpecialClientGroup'
END
ELSE
BEGIN
	SELECT @groupStr = value from GXGlobalParam where name = 'CommservSurveySpecialClientGroup'
END

SET @groupStr = @groupStr + ',';
declare @includeGroups table(groupID int);
DECLARE @endIndex integer=0
DECLARE @startIndex integer=0
DECLARE @groupId integer        


-- Check if any special group to consider for data collection.
-- Get the list of client group ids if exists
SET @endIndex = CHARINDEX(',', @groupStr, @startIndex)
  
WHILE(@endIndex IS NOT NULL AND @endIndex <> 0)
BEGIN
	SET @groupId = CAST(SUBSTRING(@groupStr,@startIndex,@endIndex-@startIndex) AS INT)
	if(@groupId > 0)
	BEGIN
		insert into @includeGroups values(@groupId);
		DECLARE @tmpStr NVARCHAR(100);
		SELECT @tmpStr = NAME FROM APP_ClientGroup WHERE id = @groupId;
		IF(@tmpStr IS NOT NULL)
			SET @groupStrName = @groupStrName + ', ' + @tmpStr
	END
	SET @startIndex = @endIndex + 1
	SET @endIndex = CHARINDEX(',', @groupStr, @startIndex)
END

DECLARE @specialGroupExists INT  =0

IF EXISTS ( SELECT id FROM app_clientgroup join @includeGroups on id=groupID)
	SET @specialGroupExists = 1

DECLARE @now INT = dbo.GetUnixTime(GETUTCDATE())
DECLARE @OneMB BIGINT = 1024*1024

DECLARE	@CopyRedundancy INT = 1
DECLARE	@CopyBehindDays INT = 3
DECLARE	@RPOMinutes INT = 7*24*60
DECLARE	@RTOMinutes INT = 2*24*60
DECLARE	@TapeImportMinutes INT = 60
DECLARE	@RstBkpTimeRatio FLOAT = 1.10

SELECT	@CopyRedundancy = longVal
FROM	APP_ComponentProp
WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3304 AND modified = 0

SELECT	@CopyBehindDays = longVal
FROM	APP_ComponentProp
WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3305 AND modified = 0

SELECT	@RPOMinutes = longVal
FROM	APP_ComponentProp
WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3306 AND modified = 0

SELECT	@RTOMinutes = longVal
FROM	APP_ComponentProp
WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3307 AND modified = 0

CREATE	TABLE #ClientGroupParam (ClientGroupId INT, CopyRedundancy INT, CopyBehindDays INT, RPOMinutes INT, RTOMinutes INT)

INSERT	INTO #ClientGroupParam
SELECT	componentId, [3304], [3305], [3306], [3307]
FROM	(
		SELECT	componentId, propertyTypeId, longVal
		FROM	APP_ComponentProp
		WHERE	componentType = 8 AND propertyTypeId IN (3304, 3305, 3306, 3307) AND modified = 0
		) T PIVOT (MAX(longVal) FOR propertyTypeId IN ([3304], [3305], [3306], [3307])) P

CREATE TABLE #Client (clientId INT, slaCategory INT, slaDays INT, 
		copyRedundancy INT, CopyBehindDays INT, JobEndBefore INT, 
		RPOMinutes INT, RTOMinutes INT)
CREATE TABLE #Subclient (clientId INT, appTypeId INT, appId INT)
CREATE TABLE #SubclientStats (clientId INT, appTypeId INT, appId INT, fullCycleNum INT, archGroupId INT, 
		lastJobId INT, commCellId INT, lastBackupTime INT, opType INT, nAuxCopies INT, copyBehindSince INT, 
		maxAppSize BIGINT, tapeExported INT, rstThruputMbps FLOAT, restoreTime INT)
CREATE TABLE #ClientRecovery (clientId INT, lastBackupTime INT, appSize BIGINT, tapeExported INT, restoreTime INT)

IF EXISTS (SELECT * FROM RptSLAClient WHERE days = -1)
BEGIN
	INSERT	INTO #Client
	SELECT	DISTINCT ClientId, category, 0, @CopyRedundancy, @CopyBehindDays, 0, @RPOMinutes, @RTOMinutes
	FROM	RptSLAClient
	WHERE	days = -1 AND fullJobsOnly = 0 AND clientId > 1 AND status IN (1,2)
END
ELSE
BEGIN
	CREATE TABLE #ClientSLADays (clientId INT, clientGroupId INT, days INT)
	DECLARE	@ClientGroupSLADays TABLE (clientGroupId INT, slaDays INT)
	DECLARE @CS_SLADays			INT = 0

	SELECT	@CS_SLADays = longVal 
	FROM	APP_ComponentProp WITH (NOLOCK)
	WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3300 AND modified = 0

	IF @CS_SLADays <= 0 SET @CS_SLADays = 30

	INSERT	INTO @ClientGroupSLADays
	SELECT	componentId, longVal 
	FROM	APP_ComponentProp WITH (NOLOCK)
	WHERE	componentType = 8 AND propertyTypeId = 3300 AND modified = 0

	-- Only installed clients and active VMs are counted for SLA. All these clients and VMs should be shown as Met SLA or Missed SLA or Excluded.
	INSERT	INTO #Client
	SELECT	DISTINCT ClientId, category, @CS_SLADays, @CopyRedundancy, @CopyBehindDays, 0, @RPOMinutes, @RTOMinutes
	FROM	RptSLAClient
	WHERE	clientId > 1 AND status IN (1,2) AND days = @CS_SLADays

	IF EXISTS (SELECT * FROM @ClientGroupSLADays)
		UPDATE	S SET slaDays = CG.slaDays
		FROM	#Client S
				INNER JOIN APP_ClientGroupAssoc CGA ON S.clientId = CGA.clientId
				INNER JOIN @ClientGroupSLADays CG ON CGA.clientGroupId = CG.clientGroupId

	UPDATE	S SET slaCategory = C.category
	FROM	#Client S
			INNER JOIN RptSLAClient C ON S.clientId = C.clientId AND S.slaDays = C.days AND C.fullJobsOnly = 0

	DROP TABLE #ClientSLADays
END

DECLARE @AsOfDate DATETIME
DECLARE	@lastTime INT = NULL
SELECT	@lastTime = CAST(CAST(value AS NVARCHAR(32)) AS INT) FROM GxGlobalParam WITH (NOLOCK) WHERE name = 'SLA updated time'

IF @lastTime IS NULL
BEGIN
	SET @AsOfDate = GETDATE()
	SET @lastTime = dbo.GetUnixTime(@AsOfDate)
END
ELSE
	SET @AsOfDate = dbo.GetDateTime(@lastTime)

IF @specialGroupExists = 1
	DELETE T FROM #Client T
		INNER JOIN APP_ClientGroupAssoc CGA ON T.clientId = CGA.clientId
		LEFT OUTER JOIN @includeGroups G ON CGA.clientGroupId = G.groupID
	WHERE	CGA.clientGroupId IS NULL
	
DELETE T FROM #Client T
	INNER JOIN RptSLAClient S ON T.clientId = S.clientId AND S.status = 3 AND S.category <> 6
		
UPDATE	T SET CopyRedundancy = S.CopyRedundancy
FROM	#Client T 
		INNER JOIN 
		(SELECT	C.clientId, MIN(P.CopyRedundancy) AS CopyRedundancy
		FROM	#Client C INNER JOIN APP_ClientGroupAssoc A ON C.clientId = A.clientId
				INNER JOIN #ClientGroupParam P ON A.clientGroupId = P.ClientGroupId
		WHERE	P.CopyRedundancy IS NOT NULL
		GROUP BY C.clientId
		) S ON T.clientId = S.clientId

UPDATE	T SET CopyBehindDays = S.CopyBehindDays
FROM	#Client T 
		INNER JOIN (
		SELECT	C.clientId, MIN(P.CopyBehindDays) AS CopyBehindDays
		FROM	#Client C INNER JOIN APP_ClientGroupAssoc A ON C.clientId = A.clientId
				INNER JOIN #ClientGroupParam P ON A.clientGroupId = P.ClientGroupId
		WHERE	P.CopyBehindDays IS NOT NULL
		GROUP BY C.clientId
		) S ON T.clientId = S.clientId
		
UPDATE	T SET RPOMinutes = S.RPOMinutes
FROM	#Client T 
		INNER JOIN (
		SELECT	C.clientId, MIN(P.RPOMinutes) AS RPOMinutes
		FROM	#Client C INNER JOIN APP_ClientGroupAssoc A ON C.clientId = A.clientId
				INNER JOIN #ClientGroupParam P ON A.clientGroupId = P.ClientGroupId
		WHERE	P.RPOMinutes IS NOT NULL
		GROUP BY C.clientId
		) S ON T.clientId = S.clientId

UPDATE	T SET RTOMinutes = S.RTOMinutes
FROM	#Client T 
		INNER JOIN (
		SELECT	C.clientId, MIN(P.RTOMinutes) AS RTOMinutes
		FROM	#Client C INNER JOIN APP_ClientGroupAssoc A ON C.clientId = A.clientId
				INNER JOIN #ClientGroupParam P ON A.clientGroupId = P.ClientGroupId
		WHERE	P.RTOMinutes IS NOT NULL
		GROUP BY C.clientId
		) S ON T.clientId = S.clientId

DROP TABLE #ClientGroupParam

CREATE	TABLE #ClientParam (ClientId INT, CopyRedundancy INT, CopyBehindDays INT, RPOMinutes INT, RTOMinutes INT)

INSERT	INTO #ClientParam
SELECT	componentNameId, [CopyRedundancy], [CopyBehindDays], [RPO Minutes], [RTO Minutes]
FROM	(
		SELECT	componentNameId, attrName, CAST(attrVal AS INT) AS intVal
		FROM	APP_ClientProp
		WHERE	attrName IN ('SLA Copy Redundancy', 'SLA Copy Fallen Behind Days', 'RPO Minutes', 'RTO Minutes') AND modified = 0
		) T PIVOT (MAX(intVal) FOR attrName IN ([CopyRedundancy], [CopyBehindDays], [RPO Minutes], [RTO Minutes])) P

UPDATE	T SET CopyRedundancy = P.CopyRedundancy
FROM	#Client T 
		INNER JOIN #ClientParam P ON T.clientId = P.ClientId
WHERE	P.CopyRedundancy IS NOT NULL

UPDATE	T SET CopyBehindDays = P.CopyBehindDays
FROM	#Client T 
		INNER JOIN #ClientParam P ON T.clientId = P.ClientId
WHERE	P.CopyBehindDays IS NOT NULL

UPDATE	T SET RPOMinutes = P.RPOMinutes
FROM	#Client T 
		INNER JOIN #ClientParam P ON T.clientId = P.ClientId
WHERE	P.RPOMinutes IS NOT NULL

UPDATE	T SET RTOMinutes = P.RTOMinutes
FROM	#Client T 
		INNER JOIN #ClientParam P ON T.clientId = P.ClientId
WHERE	P.RTOMinutes IS NOT NULL

DROP TABLE #ClientParam

UPDATE #Client SET JobEndBefore = (@now - copyBehindDays*24*3600)


CREATE TABLE #TapeOnlySP (archGrpId INT)

INSERT	INTO #TapeOnlySP 
SELECT	AG.id
FROM	archGroup AG
		INNER JOIN archGroupCopy AGC ON AG.id = AGC.archGroupId AND AG.defaultSnapCopy = 0
		INNER JOIN MMDataPath DPT ON AGC.id = DPT.CopyId AND DPT.Flag&1 = 1
		INNER JOIN MMDrivePool DPL ON DPT.DrivePoolId = DPL.DrivePoolId AND DPL.DrivePoolType = 10001
GROUP BY AG.id
HAVING COUNT(DISTINCT DPT.DrivePoolId) = 0

INSERT	INTO #SubclientStats
SELECT	S.VMclientId, S.appType, S.appId, S.fullCycleNum, 0, 
		S.jobId, S.commCellId, S.servStartDate, S.opType, 0, 0, 0, 0, 0, 0
FROM	(
		SELECT	V.VMclientId, B.appId, B.appType, B.jobId, B.commCellId, B.servStartDate, B.opType, B.fullCycleNum, 
				ROW_NUMBER() OVER (PARTITION BY V.VMclientId ORDER BY B.servStartDate DESC) AS rowNo
		FROM	JMBkpStats B 
				INNER JOIN APP_Application A ON B.appId = A.id
				INNER JOIN APP_VMProp V ON B.jobId = V.jobId AND B.commCellId = V.commCellId
				INNER JOIN #Client C ON V.VMclientId = C.clientId
		WHERE	V.attrName = 'vmStatus' AND V.attrVal IN ('0', '3')
			AND A.subclientStatus&(2|4) = 0
			AND B.opType <> 60 AND B.fullCycleNum > 0
			AND B.bkpLevel NOT IN (64, 128, 16384) AND B.servStartDate < @lastTime
		) S
WHERE	S.rowNo = 1

INSERT	INTO #SubclientStats
SELECT	T.VMclientId, T.appType, T.appId, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
FROM	(
		SELECT	V.VMclientId, B.appId, B.appType, B.jobId, B.commCellId, B.servStartDate, B.opType, B.fullCycleNum, 
				ROW_NUMBER() OVER (PARTITION BY V.VMclientId ORDER BY B.servStartDate DESC) AS rowNo
		FROM	JMBkpStats B 
				INNER JOIN APP_Application A ON B.appId = A.id
				INNER JOIN APP_VMProp V ON B.jobId = V.jobId AND B.commCellId = V.commCellId
				INNER JOIN #Client C ON V.VMclientId = C.clientId
		WHERE	V.attrName = 'vmStatus'
			AND A.subclientStatus&(2|4) = 0
			AND B.opType <> 60 AND B.fullCycleNum > 0 AND B.servStartDate < @lastTime
		) T
		LEFT OUTER JOIN #SubclientStats S ON T.VMclientId = S.clientId AND T.appId = S.appId
WHERE	T.rowNo = 1 AND S.appId IS NULL

CREATE TABLE #LastFullCycleVSAJobs (jobId INT, commCellId INT)

INSERT	INTO #LastFullCycleVSAJobs
SELECT	DISTINCT B.jobId, B.commCellId
FROM	JMBkpStats B 
		INNER JOIN #SubclientStats S1 ON B.appId = S1.appId AND B.fullCycleNum = S1.fullCycleNum
WHERE	B.appType = 106 AND B.opType <> 60 AND B.fullCycleNum > 0 AND B.servStartDate < @lastTime
	AND B.bkpLevel IN (1, 2, 4, 64, 128) AND B.status IN (1, 3, 14)

UPDATE	#SubclientStats SET maxAppSize = T.appSize
FROM	#SubclientStats S
	INNER JOIN (
	SELECT	V.VMclientId, MAX(CAST(V.attrVal AS BIGINT)) AS appSize
	FROM	#LastFullCycleVSAJobs B 
			INNER JOIN APP_VMProp V ON B.jobId = V.jobId AND B.commCellId = V.commCellId
	WHERE	V.attrName = 'vmUsedSpace'
	GROUP BY V.VMclientId
	) T ON S.clientId = T.VMclientId

DROP TABLE #LastFullCycleVSAJobs


CREATE TABLE #VSAParams (VMClientId INT, CopyRedundancy INT, CopyBehindDays INT, RPOMinutes INT, RTOMinutes INT)

INSERT	INTO #VSAParams
SELECT	S.clientId, CopyRedundancy, CopyBehindDays, RPOMinutes, RTOMinutes
FROM	#SubclientStats S
		INNER JOIN APP_Application A ON S.appId = A.id
		INNER JOIN #Client CL ON A.clientId = CL.clientId

UPDATE	T 
SET		CopyRedundancy = CASE WHEN T.CopyRedundancy > 0 THEN T.CopyRedundancy ELSE ISNULL(V.CopyRedundancy, @CopyRedundancy) END, 
		CopyBehindDays = CASE WHEN T.CopyBehindDays > 0 THEN T.CopyBehindDays ELSE ISNULL(V.CopyBehindDays, @CopyBehindDays) END, 
		RPOMinutes = CASE WHEN T.RPOMinutes > 0 THEN T.RPOMinutes ELSE ISNULL(V.RPOMinutes, @RPOMinutes) END, 
		RTOMinutes = CASE WHEN T.RTOMinutes > 0 THEN T.RTOMinutes ELSE ISNULL(V.RTOMinutes, @RTOMinutes) END
FROM	#Client T 
		LEFT OUTER JOIN #VSAParams V ON T.clientId = V.VMClientId
WHERE	T.CopyRedundancy = 0 OR T.CopyBehindDays = 0 OR T.RPOMinutes = 0 OR T.RTOMinutes = 0

DROP TABLE #VSAParams


INSERT	INTO #Subclient
SELECT	A.clientId, A.appTypeId, A.id
FROM	APP_Application A
		INNER JOIN #Client C ON A.clientId = C.clientId
WHERE	subclientStatus&(2|4) = 0 AND (dataArchGrpID > 1 OR logArchGrpID > 1)
	AND appTypeId < 600 AND appTypeId <> 106
	AND A.appTypeId NOT IN (72, 85, 127, 84, 107, 121, 122, 136)
	AND (A.appTypeId NOT IN (24, 25, 40, 47, 48, 50, 65, 66, 67, 68, 73, 75, 76) OR A.subClientStatus&8 = 0)

	AND A.subclientStatus&(16|32) = 0

	AND A.id NOT IN (SELECT componentNameId FROM APP_subclientProp 
		WHERE attrName IN ('DDB Backup', 'Index SubClient', 'SILO Copy ID', 'Exclude From SLA') 
		AND cs_attrName IN (CHECKSUM(N'DDB Backup'), CHECKSUM(N'Index SubClient'), CHECKSUM(N'SILO Copy ID'), CHECKSUM(N'Exclude From SLA'))
		AND attrVal <> '0' AND modified = 0)

DELETE S FROM #Subclient S 
		INNER JOIN JMJobAction J WITH (NOLOCK)
		ON	J.opType = 4 AND J.action = 1
		AND J.clientId = S.ClientId AND J.appType IN (S.AppTypeId, 0)

-- instanceID column was added to JMJobAction table in V11 SP6
IF COL_LENGTH('JMJobAction', 'instanceID') IS NOT NULL
	EXEC sp_executesql N'DELETE S FROM #Subclient S INNER JOIN APP_Application A WITH (NOLOCK) ON S.appId = A.id INNER JOIN JMJobAction J WITH (NOLOCK) ON J.opType = 4 AND J.action = 1 AND J.instanceID = A.instance AND J.instanceID > 1'
		
DELETE S FROM #Subclient S 
		INNER JOIN JMJobAction J WITH (NOLOCK)
		ON	J.opType = 4 AND J.action = 1
		AND J.appId = S.AppId
		
DELETE S FROM #Subclient S 
	INNER JOIN (
		SELECT	componentNameId AS appId, CAST(attrVal AS INT) AS subclientPolicyAppId
		FROM	APP_SubClientProp
		WHERE	attrName = 'Associated subclient Policy' AND cs_attrName = CHECKSUM(N'Associated subclient Policy') AND modified = 0
	) T ON S.AppId = T.appId 
	INNER JOIN JMJobAction J ON J.opType = 4 AND J.action = 1 AND J.appId = T.subclientPolicyAppId

DELETE S FROM #Subclient S 
		INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK) ON S.clientId = CGA.clientId
		INNER JOIN JMJobAction J WITH (NOLOCK) ON CGA.clientGroupId = J.clientGroupId
			AND J.clientGroupId > 0 AND J.ClientId = 1 AND J.AppType = 0 AND J.AppId = 1
			AND J.opType = 4 AND J.action = 1

DECLARE @ExcludeCmdlineSubclients INT = 0

SELECT	@ExcludeCmdlineSubclients = longVal 
FROM	APP_ComponentProp WITH (NOLOCK)
WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3308 AND modified = 0

IF @ExcludeCmdlineSubclients = 1
	DELETE S FROM #Subclient S 
			INNER JOIN APP_Application A WITH (NOLOCK) ON S.appId = A.id 
	WHERE	A.AppTypeId IN (3,22,37,61,62,79,80,103,104,128,135) 
		AND A.subclientStatus & (64|4096) > 0

INSERT	INTO #SubclientStats
SELECT	T.clientId, T.appType, T.appId, T.fullCycleNum, 0, 
		T.jobId, T.commCellId, T.servStartDate, T.opType, 0, 0, 0, 0, 0, 0
FROM	(
		SELECT	S.clientId, B.appId, B.appType, B.jobId, B.commCellId, B.servStartDate, B.opType, B.fullCycleNum, 
				ROW_NUMBER() OVER (PARTITION BY B.appId ORDER BY B.servStartDate DESC) AS rowNo
		FROM	JMBkpStats B 
				INNER JOIN #Subclient S ON B.appId = S.appId
				INNER JOIN APP_iDAType I ON B.appType = I.type
		WHERE	(B.status IN (1,14) OR B.status = 3 AND I.isCWEjobValid = 1)
			AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
			AND B.bkpLevel NOT IN (64, 128, 16384) AND B.servStartDate < @lastTime
		) T
WHERE	T.rowNo = 1

INSERT	INTO #SubclientStats
SELECT	T.clientId, T.appTypeId, T.appId, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
FROM	#Subclient T
		LEFT OUTER JOIN #SubclientStats S ON T.clientId = S.clientId AND T.appId = S.appId
WHERE	S.appId IS NULL

DROP TABLE #Subclient

UPDATE	S SET archGroupId = CASE WHEN A.dataArchGrpID > 1 THEN A.dataArchGrpID ELSE logArchGrpID END
FROM	#SubclientStats S 
		INNER JOIN APP_Application A ON S.appId = A.id

CREATE TABLE #CopyRedundancy (appId INT, nAuxCopies INT)

INSERT	INTO #CopyRedundancy
SELECT	S.appId, COUNT(DISTINCT AGC.id)
FROM	#SubclientStats S
		INNER JOIN archGroup AG ON S.archGroupId = AG.id
		INNER JOIN archGroupCopy AGC ON AGC.archGroupId = AG.id
WHERE	AG.id > 1 AND AG.type = 1 AND AGC.type IN (1,2) AND AGC.isActive = 1 
	AND AGC.id NOT IN (AG.defaultCopy, AG.defaultSnapCopy) 
	AND (AGC.isSnapCopy = 0 OR AGC.isMirrorCopy = 0)
GROUP BY S.appId

UPDATE	S SET nAuxCopies = C.nAuxCopies 
FROM 	#SubclientStats  S
	INNER JOIN #CopyRedundancy C ON S.appId = C.appId

DROP TABLE #CopyRedundancy

CREATE	TABLE #OldestJobToCopy (appId INT, jobId INT)

INSERT	INTO #OldestJobToCopy
SELECT	appId, MIN(jobId)
FROM	JMJobDataStats
WHERE	status IN (101, 102, 103) AND disabled&(1|256) = 0 AND commCellId = 2
GROUP BY appId

UPDATE	S SET CopyBehindSince = B.servEndDate
FROM	#SubclientStats S
		INNER JOIN #Client CL ON S.clientId = CL.clientId
		INNER JOIN #OldestJobToCopy J ON S.appId = J.appId
		INNER JOIN JMBkpStats B ON J.jobId = B.jobId AND B.commCellId = 2
WHERE	B.servEndDate < CL.JobEndBefore

DROP TABLE #OldestJobToCopy


UPDATE	S SET maxAppSize = T.appSize
FROM	#SubclientStats S
		INNER JOIN (
		SELECT	B.appId, MAX(B.totalUncompBytes) AS appSize
		FROM	JMBkpStats B 
				INNER JOIN #SubclientStats S1 ON B.appId = S1.appId AND B.fullCycleNum >= S1.fullCycleNum
		WHERE	B.opType <> 60 AND B.fullCycleNum > 0
		GROUP BY B.appId
		) T ON S.appId = T.appId

UPDATE	S SET rstThruputMbps = (1.0*T.AppSize/T.WriteTime/@OneMB)/@RstBkpTimeRatio
FROM	#SubclientStats S
		INNER JOIN (
		SELECT	B.appId, SUM(B.totalUncompBytes) AS AppSize, SUM(CAST(B.totalWriteTime AS BIGINT)) AS WriteTime
		FROM	#SubclientStats S1
				INNER JOIN JMBkpStats B ON S1.appId = B.appId
		WHERE	B.opType NOT IN (59, 65) AND B.bkpLevel IN (1, 64, 128, 1024, 16384, 32768) 
			AND B.totalUncompBytes > 100*@OneMB AND B.totalWriteTime > 0
		GROUP BY B.appId
		) T ON S.appId = T.appId

-- Apply average throughput at app type or app group levels if not set
CREATE TABLE #RstThruputByClientAppType (clientId INT, appTypeId INT, duration BIGINT, appSizeMB FLOAT, thruputMbps FLOAT)
CREATE TABLE #RstThruputByAppType (appTypeId INT, duration BIGINT, appSizeMB FLOAT, thruputMbps FLOAT)
CREATE TABLE #RstThruputByApp (appName VARCHAR(256), thruputMbps FLOAT)
CREATE TABLE #RstThruputByAppGroup (appGroupName VARCHAR(256), thruputMbps FLOAT)

DECLARE @rstThruputGBPerHr FLOAT

INSERT	#RstThruputByClientAppType 
SELECT	clientId, appTypeId, duration, appSizeMB, (1.0*appSizeMB)/(1.0*duration)
FROM	(
		SELECT	clientId, appTypeId, 
				SUM(1.0*maxAppSize/@OneMB/rstThruputMbps) AS duration, 
				SUM(1.0*maxAppSize/@OneMB) AS appSizeMB
		FROM	#SubclientStats
		WHERE	maxAppSize > 100*@OneMB AND rstThruputMbps > 0
		GROUP BY clientId, appTypeId
		) S
WHERE	duration > 0

INSERT	#RstThruputByAppType 
SELECT	appTypeId, SUM(duration), SUM(appSizeMB), SUM(appSizeMB)/(1.0*SUM(duration))
FROM	#RstThruputByClientAppType
GROUP BY appTypeId

INSERT	INTO #RstThruputByApp
SELECT	ATG.app, SUM(T.appSizeMB)/(1.0*SUM(duration))
FROM	#RstThruputByAppType T
		INNER JOIN APP_AppTypeGroups ATG ON T.appTypeId = ATG.appTypeId
GROUP BY ATG.app

INSERT	INTO #RstThruputByAppGroup
SELECT	ATG.appGroup, SUM(T.appSizeMB)/(1.0*SUM(duration))
FROM	#RstThruputByAppType T
		INNER JOIN APP_AppTypeGroups ATG ON T.appTypeId = ATG.appTypeId
GROUP BY ATG.appGroup

SELECT	@rstThruputGBPerHr = SUM(appSizeMB)/(1.0*SUM(duration))
FROM	#RstThruputByAppType


UPDATE	#SubclientStats SET rstThruputMbps = T.thruputMbps
FROM	#SubclientStats S 
		INNER JOIN #RstThruputByClientAppType T ON S.clientId = T.clientId AND S.appTypeId = T.appTypeId
WHERE	opType NOT IN (59, 65) AND rstThruputMbps = 0

UPDATE	#SubclientStats SET rstThruputMbps = T.thruputMbps
FROM	#SubclientStats S 
		INNER JOIN #RstThruputByAppType T ON S.appTypeId = T.appTypeId
WHERE	opType NOT IN (59, 65) AND rstThruputMbps = 0

UPDATE	#SubclientStats SET rstThruputMbps = T.thruputMbps
FROM	#SubclientStats S 
		INNER JOIN APP_AppTypeGroups ATG ON S.appTypeId = ATG.appTypeId
		INNER JOIN #RstThruputByApp T ON ATG.app = T.appName
WHERE	opType NOT IN (59, 65) AND rstThruputMbps = 0

UPDATE	#SubclientStats SET rstThruputMbps = T.thruputMbps
FROM	#SubclientStats S 
		INNER JOIN APP_AppTypeGroups ATG ON S.appTypeId = ATG.appTypeId
		INNER JOIN #RstThruputByAppGroup T ON ATG.appGroup = T.appGroupName
WHERE	opType NOT IN (59, 65) AND rstThruputMbps = 0

UPDATE	#SubclientStats SET rstThruputMbps = @rstThruputGBPerHr
WHERE	opType NOT IN (59, 65) AND rstThruputMbps = 0

DROP TABLE #RstThruputByClientAppType
DROP TABLE #RstThruputByAppType
DROP TABLE #RstThruputByApp
DROP TABLE #RstThruputByAppGroup

-- If tape exported
CREATE TABLE #ChunksOnTape (appId INT, archChunkId INT, chunkCommCellId INT)

INSERT	INTO #ChunksOnTape
SELECT	DISTINCT S.appId, ACM.archChunkId, ACM.chunkCommCellId
FROM	#SubclientStats S
		INNER JOIN JMBkpStats B ON S.appId = B.appId AND S.fullCycleNum = B.fullCycleNum
		INNER JOIN #TapeOnlySP T ON B.dataArchGrpId = T.archGrpId OR B.logArchGrpId = T.archGrpId
		INNER JOIN archChunkMapping ACM ON B.jobId = ACM.jobId AND B.commCellId = ACM.commCellId
WHERE	ACM.flags&256 = 0 AND ACM.physicalSize > 0

UPDATE	S SET tapeExported = 1
FROM	#SubclientStats S 
		INNER JOIN (
		SELECT	T.appId, COUNT(V.MediaId) AS tapeExported
		FROM	#ChunksOnTape T
				INNER JOIN archChunk AC ON T.archChunkId = AC.id AND T.chunkCommCellId = AC.commCellId
				INNER JOIN MMVolume V ON AC.volumeId = V.VolumeId
				INNER JOIN MMMedia M ON V.MediaId = M.MediaId
		WHERE	M.MediaLocation >= 3
		GROUP BY T.appId
		HAVING COUNT(V.MediaId) > 0
		) T ON S.appId = T.appId
		
DROP TABLE #ChunksOnTape
DROP TABLE #TapeOnlySP


-- restoreTime from snap remains 0
UPDATE	#SubclientStats SET restoreTime = (1.0*maxAppSize)/@OneMB/rstThruputMbps
WHERE	opType NOT IN (59, 65) AND rstThruputMbps > 0

CREATE TABLE #DBAgentStats (clientId INT, appTypeId INT, instanceId INT, backupsetId INT, lastBackupTime INT, maxAppSize FLOAT, tapeExported INT, restoreTime INT)

-- For Informix, SyBase, Oracle, SAP for Oracle, Oracle for SAP DB, Oracle RAC and MySQL
INSERT	INTO #DBAgentStats
SELECT	T.clientId, T.appTypeId, T.instance, 0, T.lastBackupTime, T.maxAppSize, T.tapeExported, (1.0*T.maxAppSize)/@OneMB/T.rstThruputMbps
FROM	(SELECT	S.clientId, S.appTypeId, A.instance, S.lastBackupTime, S.maxAppSize, S.rstThruputMbps, S.tapeExported,
				ROW_NUMBER() OVER (PARTITION BY S.clientId, S.appTypeId, A.instance ORDER BY lastBackupTime DESC) AS RowNo
		FROM	#SubclientStats S
				INNER JOIN APP_Application A ON S.appId = A.id
		WHERE	opType NOT IN (59, 65) AND S.appTypeId IN (3,5,22,61,79,80,104,128)
		) T
WHERE	RowNo = 1

-- For DB2, UNIX DB2, DB2 DPF and SAP HANA
INSERT	INTO #DBAgentStats
SELECT	T.clientId, T.appTypeId, T.instance, T.backupSet, T.lastBackupTime, T.maxAppSize, T.tapeExported, (1.0*T.maxAppSize)/@OneMB/T.rstThruputMbps
FROM	(SELECT	S.clientId, S.appTypeId, A.instance, A.backupSet, S.lastBackupTime, S.maxAppSize, S.rstThruputMbps, S.tapeExported,
				ROW_NUMBER() OVER (PARTITION BY S.clientId, S.appTypeId, A.instance ORDER BY lastBackupTime DESC) AS RowNo
		FROM	#SubclientStats S
				INNER JOIN APP_Application A ON S.appId = A.id
		WHERE	opType NOT IN (59, 65) AND S.appTypeId IN (37,62,103,135)
		) T
WHERE	RowNo = 1

INSERT	INTO #ClientRecovery
SELECT	clientId, MIN(lastBackupTime), SUM(maxAppSize), MAX(tapeExported), SUM(restoreTime)
FROM	#SubclientStats
WHERE	appTypeId NOT IN (3,5,22,61,79,80,104,128,37,62,103,135) AND rstThruputMbps > 0
GROUP BY clientId

UPDATE	#ClientRecovery
SET		lastBackupTime = CASE WHEN T.lastBackupTime < S.lastBackupTime THEN T.lastBackupTime ELSE S.lastBackupTime END,
		appSize += S.appSize,
		restoreTime += S.restoreTime,
		tapeExported |= S.tapeExported
FROM	#ClientRecovery T
		INNER JOIN (
			SELECT	clientId, MIN(lastBackupTime) AS lastBackupTime, SUM(maxAppSize) AS appSize, 
					SUM(restoreTime) AS restoreTime, MAX(tapeExported) AS tapeExported
			FROM	#DBAgentStats
			GROUP BY clientId
		) S ON T.clientId = S.clientId

DELETE	#DBAgentStats
FROM	#DBAgentStats S INNER JOIN #ClientRecovery T ON T.clientId = S.clientId

INSERT	INTO #ClientRecovery
SELECT	clientId, MIN(lastBackupTime), SUM(maxAppSize), MAX(tapeExported), SUM(restoreTime)
FROM	#DBAgentStats
GROUP BY clientId

DROP TABLE #DBAgentStats

UPDATE	T SET restoreTime += @TapeImportMinutes
FROM	#ClientRecovery T
WHERE	tapeExported > 0


CREATE TABLE #ClientAuxCopy (clientId INT, nAuxCopies INT, CopyBehindSince INT)

INSERT	INTO #ClientAuxCopy
SELECT	clientId, MIN(nAuxCopies), -1
FROM 	#SubclientStats
WHERE	nAuxCopies >= 0 AND lastJobId > 0
GROUP BY clientId

UPDATE	C SET CopyBehindSince = S.CopyBehindSince
FROM	#ClientAuxCopy C
		INNER JOIN 
		(SELECT	clientId, MIN(CopyBehindSince) AS CopyBehindSince
		FROM 	#SubclientStats
		WHERE	nAuxCopies >= 0 AND lastJobId > 0 AND CopyBehindSince > 0
		GROUP BY clientId
		) S ON C.clientId = S.clientId


SELECT	CL.clientId AS [ClientId],
		ISNULL(S.appId, -1) AS [AppId],
		ISNULL(S.LastJobId, -1) AS [LastJobId],
		ISNULL(S.opType, -1) AS [OpType],
		CASE WHEN ISNULL(S.lastBackupTime, 0) > 0 THEN (SELECT dbo.UTCToCellLocalTime(DATEADD(s, S.lastBackupTime, '1970-01-01'), 2)) ELSE '1970-01-01' END AS [LastJobStart],
		ISNULL(S.nAuxCopies, -1) AS [AuxCopies],
		CASE WHEN ISNULL(S.CopyBehindSince, -1) > 0 THEN (@now-S.CopyBehindSince)/3600/24 ELSE -1 END AS [DaysBehind],
		ISNULL(S.maxAppSize, 0)/@OneMB AS [AppSizeMB],
		ISNULL(S.rstThruputMbps, 0) AS [RstThruputMbps],
		ISNULL(S.tapeExported, -1) AS [TapeExported],
		CL.copyRedundancy AS [CopyRedundancy], 
		ISNULL(A.nAuxCopies, -1) AS [MinRedundancy], 
		CL.CopyBehindDays AS [CopyBehindDays], 
		CASE WHEN ISNULL(A.CopyBehindSince, 0) > 0 THEN (@now-A.CopyBehindSince)/3600/24 ELSE -1 END AS [MaxBehindDays],
		CL.RPOMinutes AS [RPOMinutes],
		CASE WHEN ISNULL(C.lastBackupTime, 0) > 0 THEN (@now-C.lastBackupTime)/60 ELSE -1 END AS [RPAMinutes],
		CL.RTOMinutes AS [RTOMinutes],
		ISNULL(C.restoreTime, 0)/60 AS [RTAMinutes],
		ISNULL(C.appSize, 0)/@OneMB AS [RTASizeMB],
		CL.slaCategory AS [ClientSLACategory],
		@AsOfDate AS [AsOfDate]
FROM	#Client CL
		LEFT OUTER JOIN #SubclientStats S ON S.clientId = CL.clientId
		LEFT OUTER JOIN #ClientAuxCopy A ON S.clientId = A.clientId
		LEFT OUTER JOIN #ClientRecovery C ON S.clientId = C.clientId
ORDER BY [ClientId], [AppId]

DROP TABLE #SubclientStats
DROP TABLE #ClientAuxCopy
DROP TABLE #ClientRecovery
DROP TABLE #Client

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

SET NOCOUNT OFF