

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/rptUpdateSLA.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/rptUpdateSLA.sp,v $ $Id: rptUpdateSLA.sp,v 1.1.2.3.28.2 2021/07/15 19:39:13 junlu Exp $";
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='RptUpdateSLA')
	delete from GXDBVersions where aliasname = 'RptUpdateSLA'
GO
print '... Creating Procedure: RptUpdateSLA'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure RptUpdateSLA
-- Stored Procedure Inputs
  @i_date DATETIME,
  @i_csTZ NVARCHAR(255)
AS
SET NOCOUNT ON
--#define CV_COMPONENT_COMMCELL_SNAP_JOBS_QUALIFY_SLA 3303
DECLARE @SnapJobsQualifySLA INT = 0
SELECT	@SnapJobsQualifySLA = longVal
FROM	APP_ComponentProp WITH (NOLOCK)
WHERE	componentType = 1 AND componentId = 2 AND propertyTypeId = 3303 AND modified = 0
DECLARE	@dtBegin	DATETIME = dbo.LocalToUTCTime(@i_date, @i_csTZ)
DECLARE	@dtEnd		DATETIME = DATEADD(DAY, 1, @dtBegin)
DECLARE	@utBegin	INT = dbo.GetUnixTime(@dtBegin)
DECLARE	@utEnd		INT = dbo.GetUnixTime(@dtEnd)
CREATE TABLE #PreviousDays (dtBegin DATETIME, dtEnd DATETIME, utBegin INT, utEnd INT)
CREATE TABLE #PreviousDaysSLA (dtBegin DATETIME, clientId INT, appId INT, appTypeId INT,
		nCompletedJobs INT, nFailedJobs INT, nCompletedFullJobs INT, nFailedFullJobs INT, nCompletedSnapJobs INT, nCompletedFullSnapJobs INT)
CREATE TABLE #SnapJobs (jobId INT, commCellId INT)
CREATE TABLE #updatedSLA (date DATETIME, clientId INT, appId INT, fullJobsOnly INT, appTypeId INT, status INT, category INT, lastSeenTime INT)
INSERT	INTO #PreviousDays
SELECT	DISTINCT date, DATEADD(DAY, 1, date), 0, 0
FROM	RptSLA WITH (NOLOCK)
DECLARE @lastDate DATETIME = DATEADD(DAY, 1, @i_date)
DECLARE @prevDate DATETIME
DECLARE	@i INT = 0
WHILE @i < 30
BEGIN
	SET @prevDate = DATEADD(DAY, -1, @lastDate)
	SET @lastDate = (SELECT MAX(dtBegin) FROM #PreviousDays WHERE dtBegin <= @prevDate AND dtEnd > @prevDate)
	IF	@lastDate IS NULL
	BEGIN
		SET @lastDate = @prevDate
		INSERT INTO #PreviousDays
		SELECT @prevDate, DATEADD(DAY, 1, @prevDate), 0, 0
	END
	SET @i += 1
END
UPDATE	#PreviousDays
SET		utBegin = dbo.GetUnixTime(dbo.LocalToUTCTime(dtBegin, @i_csTZ)),
		utEnd   = dbo.GetUnixTime(dbo.LocalToUTCTime(dtEnd, @i_csTZ))
CREATE TABLE #LongRunJobs (
		dtBegin DATETIME, clientId INT, appId INT, appType INT, vsaInstance INT,
		jobId INT, commCellId INT, isSnapJob INT, isFullJob INT, status INT, failureErrorCode INT)
-- Use JMBkpStats_servStartDate_opType_servEndDate_idx
INSERT	INTO #LongRunJobs (dtBegin, jobId, commCellId)
SELECT	P.dtBegin, B.jobId, B.commCellId
FROM	JMBkpStats B WITH (NOLOCK)
		INNER JOIN #PreviousDays P ON B.servStartDate >= P.utBegin AND B.servStartDate < P.utEnd
WHERE	B.servStartDate < @utBegin AND B.servEndDate >= @utBegin
	AND B.opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
UPDATE	J SET appId = B.appId, appType = B.appType,
		vsaInstance = CASE WHEN B.opType IN (59, 65) AND B.jobIdActedOn <> B.jobId THEN 1 ELSE 0 END,
		isSnapJob = CASE WHEN B.opType IN (59, 65) AND B.jobIdActedOn <> B.jobId THEN 1 ELSE 0 END,
		isFullJob = CASE WHEN B.bkpLevel IN (1, 2, 4, 256, 1024, 32768) THEN 1 ELSE 0 END,
		status = CASE WHEN B.displayStatus = 14 THEN 1 ELSE B.displayStatus END,
		failureErrorCode = B.failureErrorCode
FROM	#LongRunJobs J
		INNER JOIN JMBkpStats B WITH (NOLOCK) ON J.jobId = B.jobId AND J.commCellId = B.commCellId
DELETE	#LongRunJobs WHERE failureErrorCode = 8
UPDATE	J SET status = 1
FROM	#LongRunJobs J
		INNER JOIN APP_iDAType I WITH (NOLOCK) ON J.appType = I.type AND I.isCWEjobValid = 1
WHERE	J.status = 3
UPDATE	J SET clientId = A.clientId,
vsaInstance = CASE WHEN A.appTypeId = 106 AND A.subclientStatus&0x00020 = 0 THEN A.instance ELSE 0 END
FROM	#LongRunJobs J
		INNER JOIN APP_Application A WITH (NOLOCK) ON J.appId = A.id
CREATE TABLE #VSA_AppIDs (appId INT, isIndexingV2 INT)
-- Get VSA subclient appId for both IndexingV2 and non-IndexingV2 VSA subclients
INSERT	INTO #VSA_AppIDs
SELECT	A.appId, ISNULL(CP.attrVal, 0)
FROM	(SELECT DISTINCT clientId, appId, vsaInstance FROM #LongRunJobs WHERE vsaInstance > 0) A
		INNER JOIN APP_InstanceProp IP WITH (NOLOCK) ON IP.componentNameId = A.vsaInstance
			AND IP.attrName = 'Virtual Server Instance Type' AND IP.attrVal <> '' AND IP.modified = 0
		LEFT OUTER JOIN APP_ClientProp CP WITH (NOLOCK) ON CP.componentNameId = A.clientId
			AND CP.attrName = 'IndexingV2_VSA' AND CP.modified = 0 AND CP.attrVal = '1'
CREATE TABLE #V1_VSAJobs (
		dtBegin DATETIME, clientId INT, appId INT, appType INT, vsaInstance INT,
		jobId INT, commCellId INT, isSnapJob INT, isFullJob INT, status INT)
INSERT	INTO #V1_VSAJobs
SELECT	J.dtBegin, V.clientId, J.appId, J.appType, J.vsaInstance, J.jobId, J.commCellId, J.isSnapJob, J.isFullJob,
		CASE WHEN V.status IN (0, 3) THEN 1 ELSE 2 END
FROM	#LongRunJobs J
		INNER JOIN #VSA_AppIDs VSA ON J.appId = VSA.appId AND VSA.isIndexingV2 = 0
		INNER JOIN JMQinetixUpdateStatus V WITH (NOLOCK) ON J.jobId = V.jobId AND J.commCellId = V.commCellId
-- Remove IndexingV2 parent VSA jobs and non-indexingV2 VSA jobs
DELETE J FROM #LongRunJobs J
	INNER JOIN #VSA_AppIDs VSA ON J.appId = VSA.appId
INSERT	INTO #PreviousDaysSLA
SELECT	dtBegin, clientId, appId, appType,
		SUM(CASE WHEN status = 1 AND isSnapJob = 0 THEN 1 ELSE 0 END) AS nCompletedJobs,
		SUM(CASE WHEN status <>1 THEN 1 ELSE 0 END) AS nFailedJobs,
		SUM(CASE WHEN status = 1 AND isFullJob = 1 AND isSnapJob = 0 THEN 1 ELSE 0 END) AS nCompletedFullJobs,
		SUM(CASE WHEN status <>1 AND isFullJob = 1 THEN 1 ELSE 0 END) AS nFailedFullJobs,
		SUM(CASE WHEN status = 1 AND isSnapJob = 1 THEN 1 ELSE 0 END) AS nCompletedSnapJobs,
		SUM(CASE WHEN status = 1 AND isFullJob = 1 AND isSnapJob = 1 THEN 1 ELSE 0 END) AS nCompletedFullSnapJobs
FROM	#LongRunJobs
GROUP BY dtBegin, clientId, appId, appType
INSERT	INTO #PreviousDaysSLA
SELECT	dtBegin, clientId, appId, appType,
		SUM(CASE WHEN status = 1 AND isSnapJob = 0 THEN 1 ELSE 0 END) AS nCompletedJobs,
		SUM(CASE WHEN status <>1 THEN 1 ELSE 0 END) AS nFailedJobs,
		SUM(CASE WHEN status = 1 AND isFullJob = 1 AND isSnapJob = 0 THEN 1 ELSE 0 END) AS nCompletedFullJobs,
		SUM(CASE WHEN status <>1 AND isFullJob = 1 THEN 1 ELSE 0 END) AS nFailedFullJobs,
		SUM(CASE WHEN status = 1 AND isSnapJob = 1 THEN 1 ELSE 0 END) AS nCompletedSnapJobs,
		SUM(CASE WHEN status = 1 AND isFullJob = 1 AND isSnapJob = 1 THEN 1 ELSE 0 END) AS nCompletedFullSnapJobs
FROM	#V1_VSAJobs
GROUP BY dtBegin, clientId, appId, appType
DROP TABLE #LongRunJobs
DROP TABLE #V1_VSAJobs
DROP TABLE #VSA_AppIDs
INSERT	INTO #updatedSLA
SELECT	dtBegin, clientId, appId, 0, appTypeId, 1, 1, 0 FROM #PreviousDaysSLA WHERE nCompletedJobs > 0
UNION ALL
SELECT	dtBegin, clientId, appId, 0, appTypeId, 12,12,0 FROM #PreviousDaysSLA WHERE nCompletedSnapJobs > 0
UNION ALL
SELECT	dtBegin, clientId, appId, 0, appTypeId, 2, 2, 0 FROM #PreviousDaysSLA WHERE nCompletedJobs = 0 AND nCompletedSnapJobs = 0 AND nFailedJobs > 0
UNION ALL
SELECT	dtBegin, clientId, appId, 1, appTypeId, 1, 1, 0 FROM #PreviousDaysSLA WHERE nCompletedFullJobs > 0
UNION ALL
SELECT	dtBegin, clientId, appId, 1, appTypeId, 12,12,0 FROM #PreviousDaysSLA WHERE nCompletedFullSnapJobs > 0
UNION ALL
SELECT	dtBegin, clientId, appId, 1, appTypeId, 2, 2, 0 FROM #PreviousDaysSLA WHERE nCompletedFullJobs = 0 AND nCompletedFullSnapJobs = 0 AND nFailedFullJobs > 0
DELETE FROM #PreviousDaysSLA
-- Forever running jobs with optype 116 (STATELESS_BACKUP)
INSERT	INTO #PreviousDaysSLA
SELECT	P.dtBegin, A.clientId, A.id, A.appTypeId, 1, 0, 1, 0, 0, 0
FROM	APP_Application A WITH (NOLOCK)
		INNER JOIN JMBkpJobInfo B WITH (NOLOCK) ON A.id = B.applicationId
		INNER JOIN JMJobInfo J WITH (NOLOCK) ON B.jobId = J.jobId AND B.commcellId = J.commCellId AND J.opType = 116 AND J.lastUpdateFromClient > 0
		INNER JOIN #PreviousDays P ON J.lastUpdateFromClient >= P.utBegin AND J.lastUpdateFromClient < P.utEnd
DELETE	T FROM #updatedSLA T INNER JOIN #PreviousDaysSLA S
		ON T.date = S.dtBegin AND T.clientId = S.clientId AND T.appId = S.appId AND T.appTypeId = S.appTypeId
DELETE	T FROM #PreviousDaysSLA T INNER JOIN RptSLA S WITH (NOLOCK)
		ON T.dtBegin = S.date AND T.clientId = S.clientId AND T.appId = S.appId AND T.appTypeId = S.appTypeId
INSERT	INTO #updatedSLA
SELECT	dtBegin, clientId, appId, 0, appTypeId, 1, 1, 0 FROM #PreviousDaysSLA WHERE nCompletedJobs > 0
UNION ALL
SELECT	dtBegin, clientId, appId, 1, appTypeId, 1, 1, 0 FROM #PreviousDaysSLA WHERE nCompletedFullJobs > 0
IF @SnapJobsQualifySLA = 0
BEGIN
	INSERT	INTO #SnapJobs
	SELECT	jobId, commCellId
	FROM	JMJobDataStats JDS WITH (NOLOCK)
		INNER JOIN archGroupCopy AGC WITH (NOLOCK)
		 ON JDS.archGrpCopyId = AGC.id AND JDS.opType IN (13, 61)
			AND (JDS.opType = 61 AND AGC.isSnapCopy = 0 OR
				 JDS.opType = 13 AND AGC.isSnapCopy = 1 AND AGC.isMirrorCopy = 0
				 AND AGC.id NOT IN (SELECT defaultSnapCopy FROM archGroup WITH (NOLOCK) WHERE id = AGC.archGroupId))
			AND JDS.copiedTime >= @utBegin
	GROUP BY jobId, commCellId
	HAVING MIN(status) = 100 AND MAX(status) = 100
	IF EXISTS (SELECT * FROM #SnapJobs)
	BEGIN
		-- JMJobDataStats.jobId of IndexingV2 VM is child jobId
		UPDATE	T SET jobId = JL.parentJobId
		FROM	#SnapJobs T
				INNER JOIN JMJobDataLink JL WITH (NOLOCK) ON T.jobId = JL.childJobId AND JL.linkType = 7
		WHERE	T.jobId <> JL.parentJobId
		DELETE FROM #PreviousDaysSLA
		INSERT	INTO #PreviousDaysSLA
		SELECT	P.dtBegin, A.clientId, B.appId, B.appType,
				COUNT(*), 0, SUM(CASE WHEN bkpLevel IN (1, 1024, 32768) THEN 1 ELSE 0 END), 0, 0, 0
		FROM	JMBkpStats B WITH (NOLOCK)
				INNER JOIN APP_Application A WITH (NOLOCK) ON B.appId= A.id
				INNER JOIN #SnapJobs J ON B.jobId = J.jobId AND B.commCellId = J.commCellId
				INNER JOIN #PreviousDays P ON servStartDate >= P.utBegin AND servStartDate < P.utEnd
		WHERE	appType <> 106 AND bkpLevel IN (1, 2, 4, 256, 1024, 32768)
			AND opType IN (59, 65) AND failureErrorCode <> 8
		GROUP BY P.dtBegin, A.clientId, B.appId, B.appType
		INSERT	INTO #updatedSLA
		SELECT	dtBegin, clientId, appId, 0, appTypeId, 1, 1, 0 FROM #PreviousDaysSLA WHERE nCompletedJobs > 0
		UNION ALL
		SELECT	dtBegin, clientId, appId, 0, appTypeId, 2, 2, 0 FROM #PreviousDaysSLA WHERE nCompletedJobs = 0 AND nFailedJobs > 0
		UNION ALL
		SELECT	dtBegin, clientId, appId, 1, appTypeId, 1, 1, 0 FROM #PreviousDaysSLA WHERE nCompletedFullJobs > 0
		UNION ALL
		SELECT	dtBegin, clientId, appId, 1, appTypeId, 2, 2, 0 FROM #PreviousDaysSLA WHERE nCompletedFullJobs = 0 AND nFailedFullJobs > 0
		DELETE FROM #PreviousDaysSLA
		INSERT	INTO #PreviousDaysSLA
		SELECT	P.dtBegin, V.clientId, B.appId, B.appType,
				COUNT(*), 0, SUM(CASE WHEN bkpLevel IN (1, 1024, 32768) THEN 1 ELSE 0 END), 0, 0, 0
		FROM	JMBkpStats B WITH (NOLOCK)
				INNER JOIN #SnapJobs J ON B.jobId = J.jobId AND B.commCellId = J.commCellId
				INNER JOIN #PreviousDays P ON servStartDate >= P.utBegin AND servStartDate < P.utEnd
				INNER JOIN JMQinetixUpdateStatus V WITH (NOLOCK) ON B.jobId = V.jobId AND B.commCellId = V.commCellId AND V.status IN (0, 3)
		WHERE	appType = 106 AND bkpLevel IN (1, 2, 4, 256, 1024, 32768) AND B.displayStatus IN (1, 3, 14)
			AND opType IN (59, 65) AND failureErrorCode <> 8
		GROUP BY P.dtBegin, V.clientId, B.appId, B.appType
		INSERT	INTO #updatedSLA
		SELECT	dtBegin, clientId, appId, 0, appTypeId, 1, 1, 0 FROM #PreviousDaysSLA WHERE nCompletedJobs > 0
		UNION ALL
		SELECT	dtBegin, clientId, appId, 0, appTypeId, 2, 2, 0 FROM #PreviousDaysSLA WHERE nCompletedJobs = 0 AND nFailedJobs > 0
		UNION ALL
		SELECT	dtBegin, clientId, appId, 1, appTypeId, 1, 1, 0 FROM #PreviousDaysSLA WHERE nCompletedFullJobs > 0
		UNION ALL
		SELECT	dtBegin, clientId, appId, 1, appTypeId, 2, 2, 0 FROM #PreviousDaysSLA WHERE nCompletedFullJobs = 0 AND nFailedFullJobs > 0
	END
END
DELETE T FROM #updatedSLA T INNER JOIN RptSLA S
	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
WHERE  S.status = 1 OR S.category = 12 AND T.category <> 1 OR S.category = 2 AND T.category NOT IN (1, 12)
DELETE T FROM #updatedSLA T
	INNER JOIN (SELECT date, clientId, appId, fullJobsOnly, appTypeId FROM #updatedSLA WHERE category = 1) S
	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
WHERE  T.category <> 1
MERGE RptSLA AS S
USING (SELECT DISTINCT * FROM #updatedSLA) 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);
DROP TABLE #SnapJobs
DROP TABLE #PreviousDays
DROP TABLE #PreviousDaysSLA
DROP TABLE #updatedSLA
SET NOCOUNT OFF
GO

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

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

insert into GXDBVersions values(2, 'rptUpdateSLA',  'v1.1.2.3.28.2', 'rptUpdateSLA', 'v1.1.2.3.28.2')
GO

