--@querytype 	CSV
--Name:- SLA Details

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

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

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

IF @csReleaseId < 15
BEGIN
	RETURN -- Use query 27
END

IF  @csReleaseId > 15 AND @isPrivateRunning = 'Metrics Reporting'
	AND EXISTS (SELECT * FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'Metrics Reporting - SLA for multiple days' AND CAST(value AS NVARCHAR(32)) = '1')
	SET @MultiDaySLA = 1

IF @MultiDaySLA = 1 OR OBJECT_ID('RptGetSLA', 'P') IS NULL OR NOT EXISTS (SELECT TOP 1 0 FROM RptSLAClient WHERE days = -1)
BEGIN
	RETURN -- Use query 27
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 @csTimeZone NVARCHAR(1024) = ''
SELECT	@csTimeZone = dbo.GetClientTimeZone(2)

IF ISNULL(@csTimeZone, '') = ''
BEGIN
	SELECT	@csTimeZone = timeZone FROM APP_CommCell WHERE id = 2

	SELECT	@csTimeZone = TimeZoneStdName FROM SchedTimeZone
	WHERE	TimeZoneName= SUBSTRING(@csTimeZone, CHARINDEX(':', @csTimeZone, CHARINDEX(':', @csTimeZone, 0) + 1) + 1, 255)
END

-- Get not-protected clients/subclients and the reason for not being protected

CREATE TABLE #SLA_Clients (clientId INT, clientType INT, status INT, category INT, slaDays INT)
CREATE TABLE #SLA_Details (clientId INT, appTypeId INT, appId INT, status INT, category INT, slaDays INT, 
		LastSuccTime INT, lastJobID INT, lastJobCommCellID INT, lastJobStartTime INT, lastJobStatus INT, lastJobOptype INT)

DECLARE	@lastTime INT = NULL
DECLARE @DateAsOf DATETIME
	
EXEC RptGetSLA 1, -1, 0, 0, @DateAsOf OUTPUT

SET	@lastTime = dbo.GetUnixTime(dbo.LocalToUTCTime(@DateAsOf, @csTimeZone))
	
IF @specialGroupExists = 1
BEGIN
	DELETE	S
	FROM	#SLA_Clients S
			LEFT OUTER JOIN (
				SELECT	DISTINCT CGA.clientId	
				FROM	APP_ClientGroupAssoc CGA WITH (NOLOCK) 
					INNER JOIN @includeGroups G ON CGA.clientGroupId = G.groupID
				) T ON S.clientId = T.clientId
	WHERE	T.clientId IS NULL
		
	DELETE	D
	FROM	#SLA_Details D
			LEFT OUTER JOIN #SLA_Clients C ON D.clientId = C.clientId
	WHERE	C.clientId IS NULL
END

DELETE	S
FROM	#SLA_Details S
		LEFT OUTER JOIN #SLA_Clients C ON S.clientId = C.clientId AND C.status = 2
WHERE	C.clientId IS NULL
	
DELETE	#SLA_Details WHERE status NOT IN (0, 2)

UPDATE	#SLA_Details
SET		LastSuccTime = T.LastSuccTime, LastJobID = T.LastJobID, lastJobCommCellID = T.CommCellID
FROM	#SLA_Details A INNER JOIN
		(SELECT	S.appId, J.commCellId, MAX(J.jobId) AS LastJobID, 
				MAX(CASE WHEN J.status IN (1,14) OR J.status = 3 AND I.isCWEjobValid = 1 THEN J.servStartDate ELSE 0 END) AS LastSuccTime
		FROM	#SLA_Details S 
			INNER JOIN JMBkpStats J WITH (NOLOCK)
					ON J.appId = S.appId AND S.appTypeId <> 106 AND J.servStartDate < @lastTime
					AND J.failureErrorCode <> 8
					AND J.bkpLevel IN (1, 2, 4, 256, 1024, 32768) 
					AND J.opType IN (4, 18, 30, 43, 59, 65, 76, 87, 91, 98, 101) 
			INNER JOIN APP_iDAType I WITH (NOLOCK) ON J.appType = I.type
		WHERE J.commCellId = 2
		GROUP BY S.appId, J.commCellId) T ON T.AppId = A.AppId

UPDATE	#SLA_Details
SET		LastSuccTime = CASE WHEN T.LastSuccTime > A.LastSuccTime THEN T.LastSuccTime ELSE A.LastSuccTime END, 
		LastJobID = T.LastJobID, lastJobCommCellID = T.CommCellID
FROM	#SLA_Details A INNER JOIN
		(SELECT	S.ClientId, J.commCellId, MAX(J.jobId) AS LastJobID, MAX(CASE WHEN V.status IN (0,3) THEN J.servStartDate ELSE 0 END) AS LastSuccTime
		FROM	#SLA_Details S
				INNER JOIN JMQinetixUpdateStatus V WITH (NOLOCK)
					ON V.clientId = S.ClientId AND S.appTypeId = 106 
				INNER JOIN JMBkpStats J WITH (NOLOCK)
					ON J.jobId = V.jobId AND J.commCellId = V.commCellId 
					AND J.servStartDate < @lastTime AND J.failureErrorCode <> 8
					AND J.bkpLevel IN (1, 2, 4, 256, 1024, 32768) 
					AND J.opType IN (4, 18, 30, 43, 59, 65, 76, 87, 91, 98, 101)
		WHERE J.commCellId = 2
		GROUP BY S.ClientId, J.commCellId) T ON T.ClientId = A.ClientId
WHERE	A.appTypeId = 106

UPDATE	#SLA_Details
SET		AppId = J.appId, lastJobStartTime = J.servStartDate, LastJobStatus = dbo.GetJobStatusForReport(J.status, 1), lastJobOptype = J.opType
FROM	#SLA_Details CJ
		INNER JOIN JMBkpStats J WITH (NOLOCK) ON J.jobId = CJ.LastJobID AND J.commCellId = CJ.lastJobCommCellID

-- Consider "Nothing to backup" check for File System subclients
UPDATE	F SET lastSuccTime = CAST(SP.attrVal AS INT) 
FROM	#SLA_Details F
	INNER JOIN APP_SubclientProp SP WITH (NOLOCK) ON F.appId = SP.componentNameId AND SP.attrName = 'Last Data Protected Time' 
		AND SP.cs_attrName = CHECKSUM(N'Last Data Protected Time') AND SP.modified = 0
WHERE	F.appTypeId <> 106

-- Forever running jobs with optype 116 (STATELESS_BACKUP)
IF EXISTS (SELECT * FROM JMJobInfo WITH (NOLOCK) WHERE opType = 116)
BEGIN
	UPDATE	T SET LastJobID = J.jobId, lastJobCommCellID = J.commCellId, 
		lastJobStartTime = CASE WHEN J.lastUpdateFromClient = 0 THEN J.jobStartTime ELSE J.lastUpdateFromClient END, 
		lastJobStatus = dbo.GetJobStatusForReport(J.state, 0), lastJobOptype = 116
	FROM	#SLA_Details T
		INNER JOIN JMBkpJobInfo B WITH (NOLOCK) ON T.AppId = B.applicationId
		INNER JOIN JMJobInfo J WITH (NOLOCK) ON B.jobId = J.jobId AND B.commcellId = J.commCellId AND J.opType = 116
	WHERE	T.lastJobStartTime = 0 OR J.lastUpdateFromClient > T.lastJobStartTime
END

IF EXISTS (SELECT clientId FROM #SLA_Details WHERE appTypeId = 106)
	UPDATE	T SET LastJobID = JL.childJobId
	FROM	#SLA_Details T
			INNER JOIN APP_Application A WITH (NOLOCK) ON T.clientId = A.clientId
			INNER JOIN JMJobDataLink JL WITH (NOLOCK) ON T.LastJobID = JL.parentJobId AND A.id = childAppid AND JL.linkType = 7
	WHERE	T.appTypeId = 106	

SELECT	
	-ISNULL(S.slaDays, 0) AS Days,
	S.ClientId AS ClientId,
	S.AppId AS AppId,
	dbo.WR_GetSLACategoryString(0, S.category) AS Reason,
	ISNULL(S.LastJobID, 0) AS LastBackupJobId,
	CASE WHEN ISNULL(S.LastJobStatus, 0) = 0 THEN 'N/A' 
	     WHEN @csReleaseId < 16 AND S.lastJobOptype IN (59, 65) AND S.LastJobStatus IN (1,2,3) THEN dbo.GetJobStatusString(0, S.LastJobStatus) + ' - No Backup Copy'
	     WHEN @csReleaseId >= 16 AND S.category = 12 AND S.LastJobStatus IN (1,2,3) THEN dbo.GetJobStatusString(0, S.LastJobStatus) + ' - No Backup Copy'
	     ELSE dbo.GetJobStatusString(0, S.LastJobStatus) END AS LastJobStatus,
	ISNULL(dbo.GetDateTime(S.lastJobStartTime), '1970-01-01') AS LastJobStartTime,
	REPLACE(ISNULL(dbo.GetClientOwners(S.ClientId), ''), ',', ' ') AS ClientOwners,
	(CASE WHEN S.lastSuccTime > 0 THEN dbo.GetDateTime(S.lastSuccTime) ELSE '1970-01-01' END) AS LastProtectedTime
FROM	#SLA_Details S
ORDER BY S.slaDays, S.ClientId, S.AppId

DROP TABLE #SLA_Clients
DROP TABLE #SLA_Details


--------- END SURVEY QUERY ---------
SET NOCOUNT OFF