--- Please follow the below comments to insert SQL statements.

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------

SET NOCOUNT ON

BEGIN TRY

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

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



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

--------- Insert your SQL statements here
DECLARE @isPrivateRunning varchar(256)='';

IF EXISTS(SELECT name FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'CommservSurveyRunning')
BEGIN
	SELECT @isPrivateRunning = value from GXGlobalParam WITH (NOLOCK)  where name ='CommservSurveyRunning'
END
 
DECLARE @TIME_STAMP_NAME NVARCHAR(128) = 'CommservSurveyBackupOffendersCollectionTime'

IF (@isPrivateRunning = 'Diagnostics and Usage')	--Public
BEGIN
	SET @TIME_STAMP_NAME = 'CommservSurveyPublicErrorStatsCollectionTime'
	
END
DECLARE @csTimeZone    NVARCHAR(1024) = ''
SELECT  @csTimeZone = dbo.GetClientTimeZone(2)

IF ISNULL(@csTimeZone, '') = ''
BEGIN
    SELECT @csTimeZone = timeZone FROM APP_CommCell WITH (NOLOCK) WHERE id = 2
    SELECT @csTimeZone = TimeZoneStdName FROM SchedTimeZone WITH (NOLOCK)
    WHERE  TimeZoneName = SUBSTRING(@csTimeZone, CHARINDEX(':', @csTimeZone, CHARINDEX(':', @csTimeZone, 0) + 1) + 1, 255)
END

DECLARE @CurrentTime	INT = dbo.GetUnixTime(GETUTCDATE())

DECLARE @CurrCollectionTime INT = @CurrentTime
DECLARE @LastCollectionTime INT = 0

IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
BEGIN
	DECLARE @lastcollectionTimeSTR VARCHAR(20) = ''
	EXEC GetSurveyParamValue @TIME_STAMP_NAME, @lastcollectionTimeSTR OUTPUT
	SELECT @LastCollectionTime = CAST(@lastcollectionTimeSTR AS INT) 
END
ELSE
BEGIN
	SELECT	@LastCollectionTime = CAST(CAST(value AS VARCHAR(20)) AS INT) 
	FROM	GXGlobalParam WITH (NOLOCK)
	WHERE	name = @TIME_STAMP_NAME
END

IF	@LastCollectionTime = 0
BEGIN
	DECLARE	@LastCollection DATETIME = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
	SET	@LastCollection	= dbo.ClientLocalToUTCTime(@LastCollection, 2)
	DECLARE @CSFirstJobTime INT
	SELECT @CSFirstJobTime = MIN(servEndDate) FROM JMBkpStats B WITH (NOLOCK)	
	IF (@CSFirstJobTime < dbo.GetUnixTime(@LastCollection))
		SET @LastCollectionTime = dbo.GetUnixTime(@LastCollection) 
	ELSE
		SET @LastCollectionTime = @CSFirstJobTime
END

-- Check if any special group to consider for data collection.
-- Get the list of client group ids if exists
DECLARE @groupStr varchar(256);
DECLARE @groupStrName Nvarchar(MAX)='';

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

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

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 WITH (NOLOCK) 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 WITH (NOLOCK) join @includeGroups on id=groupID)
	SET @specialGroupExists =1


IF object_id('tempdb.dbo.#BackupOffendingJobs') is not null DROP TABLE #BackupOffendingJobs
	CREATE TABLE #BackupOffendingJobs (Client INT, Subclient INT, Agent INT, JobId INT, Status INT, FailureReason NVARCHAR(MAX), ErrorCode NVARCHAR(MAX))

IF object_id('tempdb.dbo.#BackupOffenders') is not null DROP TABLE #BackupOffenders
	CREATE TABLE #BackupOffenders (Client INT, Subclient INT, Agent INT, JobCount INT, Status INT, FailureReason NVARCHAR(MAX), ErrorCode NVARCHAR(MAX))

IF object_id('tempdb.dbo.#topBackupOffendersErrorCode') is not null DROP TABLE #topBackupOffendersErrorCode
	CREATE TABLE #topBackupOffendersErrorCode (Client INT, Subclient INT, Agent INT, JobCount INT, Status INT, FailureReason NVARCHAR(MAX), ErrorCode NVARCHAR(MAX))

IF object_id('tempdb.dbo.#topBackupOffenders') is not null DROP TABLE #topBackupOffenders
	CREATE TABLE #topBackupOffenders (Client INT, Subclient INT, Agent INT, JobCount INT, Status INT, FailureReason NVARCHAR(MAX), ErrorCode NVARCHAR(MAX))

IF object_id('tempdb.dbo.#OlderOffenders') is not null DROP TABLE #OlderOffenders
CREATE TABLE #OlderOffenders (Client INT, Subclient INT, Agent INT, JobCount INT, Status INT, CurrentMonth DATE, FailureReason NVARCHAR(MAX), ErrorCode NVARCHAR(MAX))

DECLARE @CurrentMonth DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

IF  (@specialGroupExists =1 )
BEGIN                
	INSERT INTO #BackupOffendingJobs
	SELECT	C.id AS 'Client',
    	    A.id AS 'Subclient',
			A.appTypeId AS 'Agent',
			jobId AS 'JobCount',
			B.status AS 'Status',
			CASE WHEN CHARINDEX(',', failureReason) = LEN(failureReason) THEN failureReason
				 ELSE RIGHT(failureReason, CHARINDEX(',', REVERSE(failureReason), 2)-1) END AS 'FailureReason',
	    	failureErrorCode
		FROM	JMBkpStats B WITH (NOLOCK)
			INNER JOIN APP_Application A WITH (NOLOCK)
				ON B.appId = A.id 
			INNER JOIN APP_Client C WITH (NOLOCK)
				ON C.id = A.clientId
			INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK)
				ON CGA.clientId = C.id
			INNER JOIN @includeGroups CG 
				ON CGA.clientGroupId = CG.groupId
			INNER JOIN APP_IDAType IT WITH (NOLOCK)
			    ON IT.type = A.appTypeId
	WHERE  servEndDate BETWEEN @LastCollectionTime AND @CurrCollectionTime AND 
	opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
	AND B.status in (2,4,9) AND failureErrorCode NOT In (8)
END
ELSE
BEGIN
	INSERT INTO #BackupOffendingJobs
	SELECT	C.id AS 'Client',
    	    A.id AS 'Subclient',
			A.appTypeId AS 'Agent',
			jobId AS 'JobCount',
			B.status AS 'Status',
			CASE WHEN CHARINDEX(',', failureReason) = LEN(failureReason) THEN failureReason
				 ELSE RIGHT(failureReason, CHARINDEX(',', REVERSE(failureReason), 2)-1) END AS 'FailureReason',
	    	failureErrorCode
		FROM	JMBkpStats B WITH (NOLOCK)
			INNER JOIN APP_Application A WITH (NOLOCK)
				ON B.appId = A.id 
			INNER JOIN APP_Client C WITH (NOLOCK)
				ON C.id = A.clientId
			INNER JOIN APP_ClientGroupAssoc CGA WITH (NOLOCK)
				ON CGA.clientId = C.id
			INNER JOIN APP_IDAType IT WITH (NOLOCK)
			    ON IT.type = A.appTypeId
	WHERE  servEndDate BETWEEN @LastCollectionTime AND @CurrCollectionTime AND 
	opType IN (4, 14, 18, 30, 43, 59, 65, 76, 87, 91, 94, 97, 98, 101)
	AND B.status in (2,4,9) AND failureErrorCode NOT In (8)
END

	UPDATE #BackupOffendingJobs
	SET     ErrorCode = CAST(C.SubsystemID AS NVARCHAR(12)) + ':' + CAST(C.MessageNum AS NVARCHAR(16)),
        	FailureReason = A.FailureReason
	FROM    #BackupOffendingJobs A
        	INNER JOIN JMFailureReasonMsg B WITH (NOLOCK) ON CAST(LEFT(A.FailureReason, LEN(A.FailureReason)-1) AS INT) = B.id
        	INNER JOIN EvLocaleMsgs C WITH (NOLOCK) ON B.messageId = C.MessageID AND C.LocaleID = 0
	WHERE  A.FailureReason <> ''
	
	INSERT INTO #BackupOffenders
	SELECT Client,Subclient, Agent, COUNT(JobId), Status, FailureReason, ErrorCode FROM #BackupOffendingJobs
	GROUP BY Client,Subclient, Agent, Status, FailureReason, ErrorCode
	
	;WITH TOP1 AS( SELECT Client,Subclient,AGENT,Status, FailureReason, ErrorCode ,JobCount,  ROW_NUMBER() over (Partition BY Subclient,Status  ORDER BY JobCount DESC ) AS ROWNO FROM  #BackupOffenders B  )
	INSERT INTO #topBackupOffendersErrorCode( Client,Subclient,AGENT,Status, FailureReason, ErrorCode ,JobCounT)
	SELECT  Client,Subclient,AGENT,Status, FailureReason, ErrorCode ,JobCounT FROM TOP1 WHERE ROWNO = 1 
	
	;WITH TOPN AS
	(
		SELECT   B.Client, B.subclient, B.Agent, SUM(B.JobCount) JobCount, B.Status,topB.FailureReason, topB.ErrorCode ,  ROW_NUMBER() over (Partition BY B.Status  ORDER BY B.Client,SUM(B.JobCount) DESC ) AS ROWNO
			FROM #BackupOffenders B
		INNER JOIN #topBackupOffendersErrorCode topB on 
		B.Status = topB.Status AND B.Subclient= topB.Subclient
		GROUP BY  B.Client, B.subclient, B.Agent, B.Status,topB.FailureReason, topB.ErrorCode
	)
	INSERT INTO #topBackupOffenders( Client,Subclient,AGENT,Status, FailureReason, ErrorCode ,JobCounT)
	SELECT  Client,Subclient,AGENT,Status, FailureReason, ErrorCode ,JobCounT FROM TOPN WHERE   ROWNO <= 10
	ORDER BY ROWNO
			
DECLARE @BackupOffenders XML =NULL
DECLARE @found INT = 0
IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
BEGIN
	DECLARE @BOXtemp NVARCHAR(MAX) = '';
	EXEC GetSurveyParamValue 'BackupOffendersxml', @BOXtemp OUTPUT
	IF @BOXtemp <> ''
	BEGIN
		SET @BackupOffenders = CAST(@BOXtemp AS XML) 
		SET @found = 1
	END
END
ELSE
BEGIN
	IF EXISTS(SELECT name FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'BackupOffendersxml')
	BEGIN
		SET @BackupOffenders = (SELECT TOP 1 CAST(value AS XML) from GXGlobalParam where name = 'BackupOffendersxml')
		SET @found = 1
	END
END

IF (@BackupOffenders IS NULL)
BEGIN
	SET @BackupOffenders = (
		SELECT Client AS '@Client',
		       Subclient AS '@Subclient', 
			   Agent AS '@Agent', 
			   JobCount AS '@JobCount',
			   @CurrentMonth AS '@Month', 
			   Status AS '@Status', 
			   REPLACE(LEFT(dbo.NormalizeForXML(dbo.JMGetLocalizedMessageFunc(0, FailureReason)), 512), '''','') AS '@FailureReason', 
			   ErrorCode AS '@ErrorCode'
	     FROM #topBackupOffenders
		 ORDER BY JobCount DESC
		 FOR XML PATH('BackupOffendersxml'))
END
ELSE
BEGIN
	INSERT INTO #OlderOffenders
	SELECT 
		csInfo.n.value('@Client', 'INT'),
		csInfo.n.value('@Subclient', 'INT'),
		csInfo.n.value('@Agent', 'INT'),
		csInfo.n.value('@JobCount', 'INT'),
		csInfo.n.value('@Status', 'INT'),
		csInfo.n.value('@Month', 'DATE'),
		csInfo.n.value('@FailureReason', 'NVARCHAR(MAX)'),
		csInfo.n.value('@ErrorCode', 'NVARCHAR(MAX)')
	FROM
	@BackupOffenders.nodes('BackupOffendersxml') AS csInfo(n)
	
	IF((SELECT TOP 1 CurrentMonth FROM #OlderOffenders) < @CurrentMonth)
	BEGIN
		Delete FROM #OlderOffenders
	END

	MERGE #OlderOffenders AS O
	USING #topBackupOffenders AS C
	ON O.Status = C.Status AND O.Subclient = C.Subclient 
	WHEN MATCHED
	THEN 
		UPDATE SET O.JobCount = O.JobCount + C.JobCount,
		           O.FailureReason = C.FailureReason,
				   O.ErrorCode = C.ErrorCode
	WHEN NOT MATCHED
	THEN
		INSERT (Client, Subclient, Agent, JobCount, CurrentMonth, Status, FailureReason, ErrorCode)
		VALUES (C.Client, C.Subclient, C.Agent, C.JobCount, @CurrentMonth, C.Status, C.FailureReason, C.ErrorCode);

	SET @BackupOffenders = (
		SELECT Client AS '@Client',
			   Subclient AS '@Subclient', 
			   Agent AS '@Agent', 
			   JobCount AS '@JobCount', 
			   CurrentMonth AS '@Month', 
			   Status AS '@Status', 
			   FailureReason AS '@FailureReason', 
			   ErrorCode AS '@ErrorCode' FROM (
					SELECT TOP 10 Client,
								Subclient, 
					  			Agent, 
					  			SUM(JobCount) AS JobCount, 
					  			CurrentMonth, 
								Status, 
					  			FailureReason, 
					  			ErrorCode
					FROM #OlderOffenders
					WHERE Status=2
					GROUP BY Client,Subclient, Agent, Status, CurrentMonth, FailureReason, ErrorCode
					ORDER BY JobCount DESC
					UNION
					SELECT TOP 10 Client,
					  			Subclient, 
					  			Agent, 
					  			SUM(JobCount) AS JobCount, 
					  			CurrentMonth, 
								Status, 
					  			FailureReason, 
								ErrorCode
					FROM #OlderOffenders
					WHERE Status=4
					GROUP BY Client,Subclient, Agent, Status, CurrentMonth, FailureReason, ErrorCode
					ORDER BY JobCount DESC
					UNION
					SELECT TOP 10 Client,
					  			Subclient, 
					  			Agent, 
								SUM(JobCount) AS JobCount, 
					  			CurrentMonth, 
					  			Status, 
					  			FailureReason, 
					  			ErrorCode 
					FROM #OlderOffenders
					WHERE Status=9
					GROUP BY Client,Subclient, Agent, Status, CurrentMonth, FailureReason, ErrorCode
					ORDER BY JobCount DESC ) A
					FOR XML PATH('BackupOffendersxml')
				)				
END
			
IF (@found = 0 AND OBJECT_ID('GetSurveyParamValue') IS NULL)
BEGIN
	IF COL_LENGTH('GXGlobalParam', 'created') IS NOT NULL
		EXEC('INSERT INTO GXGlobalParam (name, value, created, modified) SELECT ''BackupOffendersxml'', dbo.GetUnixTimeBig(getDate()), 0, 0')
	ELSE
		EXEC('INSERT INTO GXGlobalParam (name, value) SELECT ''BackupOffendersxml'', ''''')
END

IF OBJECT_ID('SetSurveyParamValue') IS NOT NULL
BEGIN
	DECLARE @ParamTemp NVARCHAR(MAX)
	SET @ParamTemp = ISNULL(CAST(@BackupOffenders AS NVARCHAR(MAX)),'')
	EXEC SetSurveyParamValue 'BackupOffendersxml', @ParamTemp
END
ELSE
BEGIN
	UPDATE GXGlobalParam
	SET value = ISNULL(CAST(@BackupOffenders AS NVARCHAR(MAX)),'')
	WHERE name = 'BackupOffendersxml'
END

SET @surveyXML = (SELECT
	@BackupOffenders
	FOR XML PATH('BackupClientOffenders'))

--select cast(@surveyXML as XML)
IF object_id('tempdb.dbo.#BackupOffendingJobs') is not null DROP TABLE #BackupOffendingJobs
IF object_id('tempdb.dbo.#BackupOffenders') is not null DROP TABLE #BackupOffenders
IF object_id('tempdb.dbo.#topBackupOffendersErrorCode') is not null DROP TABLE #topBackupOffendersErrorCode
IF object_id('tempdb.dbo.#topBackupOffenders') is not null DROP TABLE #topBackupOffenders
IF object_id('tempdb.dbo.#OlderOffenders') is not null DROP TABLE #OlderOffenders

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


IF NOT EXISTS (SELECT * FROM GXGlobalParam WHERE name = @TIME_STAMP_NAME) AND OBJECT_ID('GetSurveyParamValue') IS NULL
BEGIN
	IF EXISTS (SELECT * FROM APP_Client WITH(NOLOCK) WHERE id = 2 AND releaseId < 15)
		INSERT INTO GXGlobalParam (name, value) SELECT @TIME_STAMP_NAME, ''
	ELSE
		EXEC QS_SetKeyIntoGlobalParamTbl @TIME_STAMP_NAME, 'Y', ''
END

END TRY
BEGIN CATCH	 

	DECLARE @ErrorMessage NVARCHAR(4000);  
  SET @ErrorMessage = ERROR_MESSAGE();
  RAISERROR(@ErrorMessage,16,1);
RETURN

END CATCH

IF OBJECT_ID('SetSurveyParamValue') IS NOT NULL
BEGIN
	EXEC SetSurveyParamValue @TIME_STAMP_NAME, @CurrCollectionTime
END
ELSE
BEGIN
	UPDATE	GXGlobalParam SET value = CAST(@CurrCollectionTime AS VARCHAR(20))
	WHERE	name = @TIME_STAMP_NAME
END

SET NOCOUNT OFF

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