

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/rptSaveSLA.sp] ---------- 

-- ----------------------------------------------------------------------
--
--           Copyright (c) 1998  CommVault Systems, Inc.
--                  All rights reserved.
--
--
--        This is unpublished proprietary source code of CommVault
--        Systems, Inc. The copyright notice above does not evidence
--        any actual or intended publication of such source code.
-- ----------------------------------------------------------------------*/
-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/rptSaveSLA.sp,v $ $Id: rptSaveSLA.sp,v 1.12.12.40.4.2 2021/07/15 19:39:11 junlu Exp $";
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='RptSaveSLA')
BEGIN
	print '>>> Drop Stored Procedure: RptSaveSLA <<<'
	drop procedure RptSaveSLA
END
IF EXISTS (select * from GxQscripts where name='RptSaveSLA')
	delete from GxQscripts where name = 'RptSaveSLA'
GO

IF EXISTS (select * from GXDBVersions where aliasname='RptSaveSLA')
	delete from GXDBVersions where aliasname = 'RptSaveSLA'
GO
print '... Creating Procedure: RptSaveSLA'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure RptSaveSLA
  @dummy integer
AS
  DECLARE @retVal integer;
SET NOCOUNT ON
SET	@retVal = 0
DECLARE @csTimeZoneName NVARCHAR(255) = ''
SELECT	@csTimeZoneName = dbo.GetClientTimeZone(2)
IF ISNULL(@csTimeZoneName, '') = ''
BEGIN
	SELECT  @csTimeZoneName = timeZone FROM APP_CommCell WITH (NOLOCK) WHERE id = 2
	SELECT	@csTimeZoneName = TimeZoneStdName
	FROM	SchedTimeZone WITH (NOLOCK)
	WHERE   TimeZoneName = SUBSTRING(@csTimeZoneName, CHARINDEX(':', @csTimeZoneName, CHARINDEX(':', @csTimeZoneName, 0) + 1) + 1, 255)
END
DECLARE @MaxSLADays INT = 30
SELECT	@MaxSLADays = longVal
FROM	APP_ComponentProp WITH (NOLOCK)
WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3300
	AND @MaxSLADays < longVal AND modified = 0
SELECT	@MaxSLADays = ISNULL(MAX(longVal), @MaxSLADays)
FROM	APP_ComponentProp WITH (NOLOCK)
WHERE	componentType = 8 AND propertyTypeId = 3300
	AND @MaxSLADays < longVal AND modified = 0
-- Only for EDC cells or exclude all EDC cells. No mixture. One capture time for all EDC cells.
DECLARE	@IgnoreEDCCommCells	INT = 0
DECLARE	@EDCDumpCaptureTime	INT = 0
DECLARE	@IsEDC				INT = 0
SELECT	@IgnoreEDCCommCells = CAST(value AS INT)
FROM	GXGlobalParam WITH (NOLOCK)
WHERE	name = 'IgnoreEDCCellsForSLA'
IF	@IgnoreEDCCommCells = 0
BEGIN
	SELECT	@EDCDumpCaptureTime = CAST(value AS INT)
	FROM	GXGlobalParam WITH (NOLOCK)
	WHERE	name = 'EDCDumpCaptureTime'
	IF	@EDCDumpCaptureTime > 0
		SET @IsEDC = 1
END
DECLARE @TblCommCell TABLE (id INT)
IF @IsEDC > 0
	INSERT INTO @TblCommCell SELECT id FROM APP_CommCell WHERE type >=100
ELSE
IF EXISTS (SELECT 1 FROM APP_CommCell WHERE id > 2)
    INSERT INTO @TblCommCell
    SELECT  DISTINCT origCCId
    FROM    APP_Application A WITH (NOLOCK)
            INNER JOIN (SELECT DISTINCT appId FROM JMBkpStats WITH (NOLOCK) WHERE commCellId = 2) S ON A.id = S.appId
ELSE
	INSERT INTO @TblCommCell SELECT 2
-- 1. Starting from v11, RptSLA and RptSLAClient tables are created in package
-- 2. Populate RptSLA table
CREATE TABLE #tempSLA
(
	date			datetime,
	clientId		int,
	appId			int,
	fullJobsOnly	int,
	appTypeId		int,
	status			int,
	category		int,
	lastSeenTime	int,
	slaDays			int,
	slaDaysLevel	varchar(32)
)
CREATE TABLE #tempSLAClient
(
	date			datetime,
	days			int,
	fullJobsOnly	int,
	clientId		int,
	isProtected		int,
	category		int,
	slaDays			int
)
CREATE TABLE #DailyJobStats (dtBegin DATETIME, clientId INT, appId INT, appTypeId INT,
		nCompletedJobs INT, nFailedJobs INT, nCompletedFullJobs INT, nFailedFullJobs INT, nCompletedSnapJobs INT, nCompletedFullSnapJobs INT, maxJobId INT)
CREATE TABLE #ActiveJobStats (dtBegin DATETIME, clientId INT, appId INT, appTypeId INT,
		nCompletedJobs INT, nFailedJobs INT, nCompletedFullJobs INT, nFailedFullJobs INT, nCompletedSnapJobs INT, nCompletedFullSnapJobs INT, maxJobId INT)
DECLARE	@lastDate	DATETIME = (SELECT MAX(date) FROM RptSLAClient)
DECLARE	@currDate	DATETIME = DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)
DECLARE	@fromHour	INT  = 8
SELECT	@fromHour = CAST(value AS INT)
FROM	GXGlobalParam WITH (NOLOCK)
WHERE	name = 'SLA time window hour'
IF @IsEDC > 0
BEGIN
	SET	@lastDate = (
		SELECT	MAX(date)
		FROM	RptSLAClient S
				INNER JOIN APP_Client CL ON S.clientId = CL.id
				INNER JOIN APP_CommCell CC ON CL.origCCId = CC.id AND CC.type >= 100)
	SET @currDate = DATEADD(DD, DATEDIFF(DD, 0, dbo.GetDateTime(@EDCDumpCaptureTime)), 0)
END
ELSE
IF DATEPART(HH, GETDATE()) < @fromHour
	SET @currDate = DATEADD(DD, -1, @currDate)
SET @currDate = DATEADD(HOUR, @fromHour, @currDate)
-- 30 day SLA needs RptSLA table entries for 30 days
IF	@lastDate IS NULL OR @IsEDC = 0 AND DATEDIFF(DAY, @lastDate, @currDate) > 30
	SET @lastDate = DATEADD(DAY, -30, @currDate)
ELSE
BEGIN
	SET @lastDate = DATEADD(DD, DATEDIFF(DD, 0, @lastDate), 0)
	SET @lastDate = DATEADD(HOUR, @fromHour, @lastDate)
END
DECLARE @DayInterval TABLE (csDateBegin DATETIME, csDateEnd DATETIME, dtBegin DATETIME, dtEnd DATETIME, utBegin INT, utEnd INT)
DECLARE	@dtBegin	DATETIME = DATEADD(DAY, -1, @lastDate)
DECLARE	@dtEnd		DATETIME = @lastDate
DECLARE	@utBegin	INT = 0
DECLARE	@utEnd		INT = 0
BEGIN TRY
	WHILE (@dtBegin <= @currDate)
	BEGIN
		INSERT INTO @DayInterval (csDateBegin, csDateEnd, dtBegin, dtEnd)
		SELECT @dtBegin, @dtEnd, dbo.LocalToUTCTime(@dtBegin, @csTimeZoneName), dbo.LocalToUTCTime(@dtEnd, @csTimeZoneName)
		SET @dtBegin = @dtEnd
		SET @dtEnd	 = DATEADD(DAY, 1, @dtBegin)
	END
	UPDATE	@DayInterval SET utBegin = dbo.GetUnixTime(dtBegin), utEnd = dbo.GetUnixTime(dtEnd)
	SELECT	@utBegin = MIN(utBegin), @utEnd = MAX(utEnd) FROM @DayInterval
	INSERT	INTO #DailyJobStats (dtBegin, clientId, appId, appTypeId, nCompletedJobs, nFailedJobs, nCompletedFullJobs, nFailedFullJobs, nCompletedSnapJobs, nCompletedFullSnapJobs)
	SELECT	csDateBegin, 0, appId, appType,
			SUM(CASE WHEN (displayStatus IN (1, 14) OR displayStatus = 3 AND isCWEjobValid = 1) AND (opType NOT IN (59, 65) OR B.jobIdActedOn = B.jobId) THEN 1 ELSE 0 END),
			SUM(CASE WHEN (displayStatus NOT IN (1, 3, 14) OR displayStatus = 3 AND isCWEjobValid = 0) THEN 1 ELSE 0 END),
			SUM(CASE WHEN (displayStatus IN (1, 14) OR displayStatus = 3 AND isCWEjobValid = 1) AND (opType NOT IN (59, 65) OR B.jobIdActedOn = B.jobId) AND bkpLevel IN (1, 1024, 32768) THEN 1 ELSE 0 END),
			SUM(CASE WHEN (displayStatus NOT IN (1, 3, 14) OR displayStatus = 3 AND isCWEjobValid = 0) AND bkpLevel IN (1, 1024, 32768) THEN 1 ELSE 0 END),
			SUM(CASE WHEN (displayStatus IN (1, 14) OR displayStatus = 3 AND isCWEjobValid = 1) AND opType IN (59, 65) THEN 1 ELSE 0 END),
			SUM(CASE WHEN (displayStatus IN (1, 14) OR displayStatus = 3 AND isCWEjobValid = 1) AND opType IN (59, 65) AND bkpLevel IN (1, 1024, 32768) THEN 1 ELSE 0 END)
	FROM	JMBkpStats B WITH (NOLOCK)
			INNER JOIN APP_iDAType I WITH (NOLOCK) ON B.appType = I.type AND B.appType <> 106
			INNER JOIN @TblCommCell CC ON B.commCellId = CC.id
			INNER JOIN @DayInterval D ON servStartDate >= utBegin AND servStartDate < utEnd
	WHERE	bkpLevel IN (1, 2, 4, 256, 1024, 32768)
		AND opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101) AND failureErrorCode <> 8
		AND servStartDate >= @utBegin AND servStartDate < @utEnd
	GROUP BY csDateBegin, appId, appType
	UPDATE	T SET clientId = A.clientId
	FROM	#DailyJobStats T INNER JOIN APP_Application A WITH (NOLOCK) ON T.appId = A.id
	-- For running VSA backup jobs that the individual VM jobs are complete.
	INSERT	INTO #ActiveJobStats (dtBegin, clientId, appId, appTypeId, nCompletedJobs, nFailedJobs, nCompletedFullJobs, nFailedFullJobs, nCompletedSnapJobs, nCompletedFullSnapJobs, maxJobId)
	SELECT	csDateBegin, clientId, B.applicationId, 106,
			SUM(CASE WHEN V.status IN (0, 3) AND (opType NOT IN (59, 65) OR B.jobIdActedOn = B.jobId) THEN 1 ELSE 0 END),
			SUM(CASE WHEN V.status NOT IN (0, 3) THEN 1 ELSE 0 END),
			SUM(CASE WHEN V.status IN (0, 3) AND (opType NOT IN (59, 65) OR B.jobIdActedOn = B.jobId) AND bkpLevel IN (1, 1024, 32768) THEN 1 ELSE 0 END),
			SUM(CASE WHEN V.status NOT IN (0, 3) AND bkpLevel IN (1, 1024, 32768) THEN 1 ELSE 0 END),
			SUM(CASE WHEN V.status IN (0, 3) AND opType IN (59, 65) THEN 1 ELSE 0 END),
			SUM(CASE WHEN V.status IN (0, 3) AND opType IN (59, 65) AND bkpLevel IN (1, 1024, 32768) THEN 1 ELSE 0 END),
			MAX(B.jobId)
	FROM	JMJobInfo J WITH (NOLOCK)
			INNER JOIN JMBkpJobInfo B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commcellId
			INNER JOIN JMQinetixUpdateStatus V WITH (NOLOCK) ON V.jobId = B.jobId AND V.commCellId = B.commCellId
			INNER JOIN @TblCommCell CC ON B.commCellId = CC.id
			INNER JOIN @DayInterval D ON J.jobStartTime >= utBegin AND J.jobStartTime < utEnd
	WHERE	B.bkpLevel IN (1, 2, 4, 256, 1024, 32768)
		AND J.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
		AND J.jobStartTime >= @utBegin AND J.jobStartTime < @utEnd
	GROUP BY csDateBegin, clientId, B.applicationId
	INSERT	INTO #DailyJobStats (dtBegin, clientId, appId, appTypeId, nCompletedJobs, nFailedJobs, nCompletedFullJobs, nFailedFullJobs, nCompletedSnapJobs, nCompletedFullSnapJobs, maxJobId)
	SELECT	csDateBegin, V.clientId, 0, 106,
			SUM(CASE WHEN B.displayStatus IN (1, 3, 14) AND V.status IN (0, 3) AND (opType NOT IN (59, 65) OR B.jobIdActedOn = B.jobId) THEN 1 ELSE 0 END),
			SUM(CASE WHEN (B.displayStatus NOT IN (1, 3, 14) OR V.status NOT IN (0, 3)) THEN 1 ELSE 0 END),
			SUM(CASE WHEN B.displayStatus IN (1, 3, 14) AND V.status IN (0, 3) AND (opType NOT IN (59, 65) OR B.jobIdActedOn = B.jobId) AND bkpLevel IN (1, 1024, 32768) THEN 1 ELSE 0 END),
			SUM(CASE WHEN (B.displayStatus NOT IN (1, 3, 14) OR V.status NOT IN (0, 3)) AND bkpLevel IN (1, 1024, 32768) THEN 1 ELSE 0 END),
			SUM(CASE WHEN B.displayStatus IN (1, 3, 14) AND V.status IN (0, 3) AND opType IN (59, 65) THEN 1 ELSE 0 END),
			SUM(CASE WHEN B.displayStatus IN (1, 3, 14) AND V.status IN (0, 3) AND opType IN (59, 65) AND bkpLevel IN (1, 1024, 32768) THEN 1 ELSE 0 END),
			MAX(B.jobId)
	FROM	JMBkpStats B WITH (NOLOCK)
			INNER JOIN JMQinetixUpdateStatus V WITH (NOLOCK) ON V.jobId = B.jobId AND V.commCellId = B.commCellId
			INNER JOIN @TblCommCell CC ON B.commCellId = CC.id
			INNER JOIN @DayInterval D ON servStartDate >= utBegin AND servStartDate < utEnd
	WHERE	B.bkpLevel IN (1, 2, 4, 256, 1024, 32768)
		AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101) AND B.failureErrorCode <> 8
		AND B.servStartDate >= @utBegin AND B.servStartDate < @utEnd
	GROUP BY csDateBegin, V.clientId
	UPDATE	T SET appId = B.appId
	FROM	#DailyJobStats T INNER JOIN JMBkpStats B WITH (NOLOCK) ON T.maxJobId = B.jobId
	WHERE	T.appTypeId = 106
	UPDATE	T SET nCompletedJobs += A.nCompletedJobs, nFailedJobs += A.nFailedJobs,
			nCompletedFullJobs += A.nCompletedFullJobs, nFailedFullJobs += A.nFailedFullJobs,
			nCompletedSnapJobs += A.nCompletedSnapJobs, nCompletedFullSnapJobs += A.nCompletedFullSnapJobs,
			maxJobId = A.maxJobId
	FROM	#DailyJobStats T INNER JOIN #ActiveJobStats A
		 ON T.dtBegin = A.dtBegin AND T.clientId = A.clientId AND T.appId = A.appId AND T.appTypeId = A.appTypeId
	INSERT	INTO #DailyJobStats (dtBegin, clientId, appId, appTypeId, nCompletedJobs, nFailedJobs, nCompletedFullJobs, nFailedFullJobs, nCompletedSnapJobs, nCompletedFullSnapJobs, maxJobId)
	SELECT	A.dtBegin, A.clientId, A.appId, A.appTypeId, A.nCompletedJobs, A.nFailedJobs, A.nCompletedFullJobs, A.nFailedFullJobs, A.nCompletedSnapJobs, A.nCompletedFullSnapJobs, A.maxJobId
	FROM	#ActiveJobStats A LEFT OUTER JOIN #DailyJobStats T
		 ON T.dtBegin = A.dtBegin AND T.clientId = A.clientId AND T.appId = A.appId AND T.appTypeId = A.appTypeId
	WHERE	T.clientId IS NULL
	INSERT	INTO #tempSLA (date, clientId, appId, fullJobsOnly, appTypeId, status, category, lastSeenTime)
	SELECT	dtBegin, clientId, appId, 0, appTypeId,
			CASE WHEN nCompletedJobs > 0 THEN 1 WHEN nCompletedSnapJobs > 0 THEN 12 WHEN nFailedJobs > 0 THEN 2 END,
			CASE WHEN nCompletedJobs > 0 THEN 1 WHEN nCompletedSnapJobs > 0 THEN 12 WHEN nFailedJobs > 0 THEN 2 END, 0
	FROM	#DailyJobStats
	WHERE	nCompletedJobs > 0 OR nCompletedSnapJobs > 0 OR nFailedJobs > 0
	INSERT	INTO #tempSLA (date, clientId, appId, fullJobsOnly, appTypeId, status, category, lastSeenTime)
	SELECT	dtBegin, clientId, appId, 1, appTypeId,
			CASE WHEN nCompletedFullJobs > 0 THEN 1 WHEN nCompletedFullSnapJobs > 0 THEN 12 WHEN nFailedFullJobs > 0 THEN 2 END,
			CASE WHEN nCompletedFullJobs > 0 THEN 1 WHEN nCompletedFullSnapJobs > 0 THEN 12 WHEN nFailedFullJobs > 0 THEN 2 END, 0
	FROM	#DailyJobStats
	WHERE	nCompletedFullJobs > 0 OR nCompletedFullSnapJobs > 0 OR nFailedFullJobs > 0
	DROP TABLE #DailyJobStats
	DROP TABLE #ActiveJobStats
	CREATE TABLE #LastCheckTime (appId INT, checkTime INT, isNothingBackup INT)
	-- Forever running jobs with optype 116 (STATELESS_BACKUP)
	INSERT	INTO #LastCheckTime
	SELECT	SP.componentNameId, CAST(SP.attrVal AS INT), 0
	FROM	JMJobInfo J WITH (NOLOCK)
			INNER JOIN JMBkpJobInfo B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commcellId AND J.opType = 116
			LEFT OUTER JOIN APP_SubclientProp SP WITH (NOLOCK) ON B.applicationId = SP.componentNameId
				AND SP.attrName = 'Last Data Protected Time' AND SP.cs_attrName = CHECKSUM(N'Last Data Protected Time')
				AND SP.modified = 0
	WHERE	J.lastUpdateFromClient BETWEEN @utBegin AND @utEnd OR ISNULL(CAST(SP.attrVal AS INT), 0) BETWEEN @utBegin AND @utEnd
	INSERT	INTO #LastCheckTime
	SELECT	SP.componentNameId, CAST(SP.attrVal AS INT), 1
	FROM	APP_SubclientProp SP WITH (NOLOCK)
			INNER JOIN (SELECT DISTINCT appId FROM RptSLASubclient WITH(NOLOCK) WHERE fullJobsOnly = 0 AND appTypeId <> 106) S ON SP.componentNameId = S.appId
			LEFT OUTER JOIN #LastCheckTime P ON SP.componentNameId = P.appId
	WHERE	SP.attrName = 'Nothing to backup' AND SP.cs_attrName = CHECKSUM(N'Nothing to backup')
		AND SP.modified = 0 AND CAST(SP.attrVal AS INT) BETWEEN @utBegin AND @utEnd
		AND P.appId IS NULL
	DELETE	P
	FROM	#LastCheckTime P
			INNER JOIN APP_SubclientProp SP WITH (NOLOCK) ON P.appId = SP.componentNameId
	WHERE	SP.attrName = 'Last Data Protected Time' AND SP.cs_attrName = CHECKSUM(N'Last Data Protected Time')
		AND SP.modified = 0 AND CAST(SP.attrVal AS INT) > P.checkTime
		AND P.isNothingBackup = 1
	UPDATE	S SET status = CASE WHEN P.isNothingBackup = 0 THEN 1 ELSE 13 END,
			category = CASE WHEN P.isNothingBackup = 0 THEN 1 WHEN S.category IN (3, 4) THEN 13 ELSE S.category END,
			lastSeenTime = P.checkTime
	FROM	#tempSLA S
			INNER JOIN #LastCheckTime P ON S.appId = P.appId
			INNER JOIN @DayInterval D ON P.checkTime BETWEEN D.utBegin AND D.utEnd
	WHERE	S.fullJobsOnly = 0 AND S.status NOT IN (1, 12) AND (P.isNothingBackup = 0 OR S.status <> 13)
		AND S.date BETWEEN D.csDateBegin AND D.csDateEnd
	INSERT	INTO #tempSLA (date, clientId, appId, fullJobsOnly, appTypeId, status, category, lastSeenTime)
	SELECT	D.csDateBegin, A.clientId, P.appId, 0, A.appTypeId,
			CASE WHEN P.isNothingBackup = 0 THEN 1 ELSE 13 END, CASE WHEN P.isNothingBackup = 0 THEN 1 ELSE 13 END, P.checkTime
	FROM	#LastCheckTime P
			INNER JOIN @DayInterval D ON P.checkTime BETWEEN D.utBegin AND D.utEnd
			INNER JOIN APP_Application A WITH (NOLOCK) ON P.appId = A.id
			LEFT OUTER JOIN #tempSLA T ON P.appId = T.appId AND T.fullJobsOnly = 0 AND T.date BETWEEN D.csDateBegin AND D.csDateEnd
	WHERE	T.appId IS NULL
	DROP TABLE #LastCheckTime
	DELETE	T FROM #tempSLA T INNER JOIN RptSLA S
			ON T.date = S.date AND T.clientId = S.clientId AND (T.appTypeId = 106 OR T.appId = S.appId) AND T.fullJobsOnly = S.fullJobsOnly AND T.appTypeId = S.appTypeId
	WHERE	S.category = 1 OR S.category IN (12, 13) AND T.category <> 1
	MERGE	RptSLA AS S
	USING	#tempSLA T
			ON T.date = S.date AND T.clientId = S.clientId AND T.appId = S.appId AND T.fullJobsOnly = S.fullJobsOnly AND T.appTypeId = S.appTypeId
		WHEN MATCHED THEN
			UPDATE SET status = T.status, category = T.category
		WHEN NOT MATCHED THEN
			INSERT (date, clientId, appId, fullJobsOnly, appTypeId, status, category, lastSeenTime)
			VALUES (T.date, T.clientId, T.appId, T.fullJobsOnly, T.appTypeId, T.status, T.category, T.lastSeenTime);
END TRY
BEGIN CATCH
	SET	@retVal = @@ERROR
	GOTO ERR_RETURN
END CATCH
-- Update RptSLA table for just completed jobs started in previous days
IF @IsEDC = 0
	EXEC RptUpdateSLA @lastDate, @csTimeZoneName
EXEC RptUpdateSLAExclusion @IsEDC
WHILE (@lastDate <= @currDate)
BEGIN
	DELETE #tempSLA
	DELETE #tempSLAClient
	EXEC RptOneDaySLA @lastDate, -1, 1
	SET	@retVal = @@ERROR
	IF	@retVal <> 0 GOTO ERR_RETURN
	IF NOT EXISTS (SELECT * FROM #tempSLAClient WHERE date = @lastDate)
		INSERT	INTO #tempSLAClient (date, days, fullJobsOnly, clientId, isProtected, category)
		SELECT	@lastDate, 0, 0, 0, 0, 0
		UNION
		SELECT	@lastDate, 0, 1, 0, 0, 0
  BEGIN TRAN
  	DELETE RptSLASubclient
	INSERT	INTO RptSLASubclient (date, clientId, appId, fullJobsOnly, appTypeId, status, category, lastSeenTime, slaDays, slaDaysLevel)
	SELECT	date, clientId, appId, fullJobsOnly, appTypeId,
			CASE WHEN category IN (1, 13) THEN 1 WHEN category IN (2, 3, 4, 9, 12) THEN 2 ELSE 3 END,
			category, lastSeenTime, slaDays, slaDaysLevel
	FROM	#tempSLA
	SET	@retVal = @@ERROR
	IF	@retVal <> 0
	BEGIN
		ROLLBACK TRAN
		GOTO ERR_RETURN
	END
  	DELETE RptSLAClient
	INSERT	INTO RptSLAClient (date, days, fullJobsOnly, clientId, status, category)
	SELECT	date, days, fullJobsOnly, clientId,
			MAX(CASE isProtected WHEN 0 THEN 2 ELSE isProtected END),
			CASE WHEN MIN(category%12) = 0 THEN 12 ELSE MIN(category%12) END
	FROM	#tempSLAClient
	GROUP BY date, days, fullJobsOnly, clientId
	IF EXISTS (SELECT 1 FROM #tempSLAClient WHERE days = -1 AND fullJobsOnly = 0 AND category IN (6, 10))
		INSERT	INTO RptSLAExclusion (clientId, clientGroupId, appTypeId, instanceId, backupsetId, appId, category, slaDays, planId)
		SELECT	T.clientId, 0, 0, 0, 0, 0, T.category, T.slaDays, 0
		FROM	#tempSLAClient T
				LEFT OUTER JOIN RptSLAExclusion E ON T.clientId = E.clientId AND E.appTypeId = 0
		WHERE	T.days = -1 AND T.fullJobsOnly = 0 AND T.category IN (6, 10)
			AND E.clientId IS NULL
	SET	@retVal = @@ERROR
	IF	@retVal <> 0
	BEGIN
		ROLLBACK TRAN
		GOTO ERR_RETURN
	END
	DELETE RptSLAHistory WHERE date >= @lastDate
	INSERT	INTO RptSLAHistory (date, days, fullJobsOnly, nClientsMet, nClientsMissed, nClientsExcluded)
	SELECT	date, days, fullJobsOnly,
		SUM(CASE WHEN isProtected = 1 THEN 1 ELSE 0 END),
		SUM(CASE WHEN isProtected = 0 AND category IN (2,3,4,9,12,13) THEN 1 ELSE 0 END),
		SUM(CASE WHEN isProtected = 0 AND category NOT IN (1,2,3,4,9,12,13) THEN 1 ELSE 0 END)
	FROM	#tempSLAClient
	GROUP BY date, days, fullJobsOnly
	SET	@retVal = @@ERROR
	IF	@retVal <> 0
	BEGIN
		ROLLBACK TRAN
		GOTO ERR_RETURN
	END
	DELETE RptClientGroupSLAHistory WHERE date >= @lastDate
	INSERT	INTO RptClientGroupSLAHistory (date, days, clientGroupId, nClientsMet, nClientsMissed, nClientsExcluded)
	SELECT	date, days, CGA.clientGroupId,
		SUM(CASE WHEN isProtected = 1 THEN 1 ELSE 0 END),
		SUM(CASE WHEN isProtected = 0 AND category IN (2,3,4,9,12,13) THEN 1 ELSE 0 END),
		SUM(CASE WHEN isProtected = 0 AND category NOT IN (1,2,3,4,9,12,13) THEN 1 ELSE 0 END)
	FROM	#tempSLAClient C
			INNER JOIN APP_ClientGroupAssoc CGA ON C.clientId = CGA.clientId
	WHERE	C.fullJobsOnly = 0
	GROUP BY date, days, CGA.clientGroupId
	SET	@retVal = @@ERROR
	IF	@retVal <> 0
	BEGIN
		ROLLBACK TRAN
		GOTO ERR_RETURN
	END
	DELETE RptClientGroupSLAHistory WHERE date >= @lastDate
	INSERT	INTO RptClientGroupSLAHistory (date, days, clientGroupId, nClientsMet, nClientsMissed, nClientsExcluded)
	SELECT	date, days, CGA.clientGroupId,
		SUM(CASE WHEN isProtected = 1 THEN 1 ELSE 0 END),
		SUM(CASE WHEN isProtected = 0 AND category IN (2,3,4,9,12,13) THEN 1 ELSE 0 END),
		SUM(CASE WHEN isProtected = 0 AND category NOT IN (1,2,3,4,9,12,13) THEN 1 ELSE 0 END)
	FROM	#tempSLAClient C
			INNER JOIN APP_ClientGroupAssoc CGA ON C.clientId = CGA.clientId
	WHERE	C.fullJobsOnly = 0
	GROUP BY date, days, CGA.clientGroupId
	SET	@retVal = @@ERROR
	IF	@retVal <> 0
	BEGIN
		ROLLBACK TRAN
		GOTO ERR_RETURN
	END
  COMMIT TRAN
	SET @lastDate = DATEADD(DAY, 1, @lastDate)
END
-- Populate RptRpoRtoSubclient, RptRpoRtoClient and RptRpoRtoSummary tables
EXEC RptSaveRpoRto 0
DELETE FROM GxGlobalParam WHERE name = 'SLA updated time'
INSERT INTO GxGlobalParam SELECT 'SLA updated time', dbo.GetUnixTime(GETDATE()), dbo.GetUnixTime(GETUTCDATE()), 0
ERR_RETURN:
DROP TABLE #tempSLA
DROP TABLE #tempSLAClient
-- 3. Prune old RptSLA and RptSLAClient entries
DECLARE	@agingDate	DATETIME
DECLARE	@SLATrendRetentionDays	INT = 84
SELECT	@SLATrendRetentionDays = CAST(value AS INT)
FROM	GXGlobalParam WITH (NOLOCK)
WHERE	name = 'SLA Trend Retention Days'
SET @lastDate  = (SELECT MAX(date) FROM RptSLAClient)
SET	@agingDate = DATEADD(DAY, -@MaxSLADays, @lastDate)
DELETE RptSLA WHERE date < @agingDate OR date < @lastDate AND category NOT IN (1,2,12,13)
SET	@agingDate = DATEADD(DAY, -@SLATrendRetentionDays, @currDate)
DELETE RptSLAHistory WHERE date < @agingDate
DELETE RptClientGroupSLAHistory WHERE date < @agingDate
SELECT	@retVal
SET NOCOUNT OFF
GO

IF EXISTS (select * from GxQscripts where name = 'rptSaveSLA')
	delete from GxQscripts where name = 'rptSaveSLA'
GO

IF EXISTS (select * from GXDBVersions where aliasname='rptSaveSLA')
	delete from GXDBVersions where aliasname = 'rptSaveSLA'
GO

insert into GXDBVersions values(2, 'rptSaveSLA',  'v1.12.12.40.4.2', 'rptSaveSLA', 'v1.12.12.40.4.2')
GO

