--- 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 = 30
DECLARE @surveyXML NVARCHAR(MAX)

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 @LastCollectionAttrName VARCHAR(256)


DECLARE @freequencyMode	 NVARCHAR(MAX) = '0'
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
	SELECT @freequencyMode = mode from #MetricsInputParams


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

IF ((@isPrivateRunning = 'Metrics Reporting') OR (@freequencyMode = '1'))
BEGIN    
    SET @LastCollectionAttrName = 'CommservSurveyPrivateTop10ErrorsCollectionDate'
END
else IF ((@isPrivateRunning = 'Metrics Direct Dip') OR (@freequencyMode = '3'))
BEGIN
    SET @LastCollectionAttrName = 'CommservSurveyDirectDipTop10ErrorsCollectionDate'
END
ELSE 
BEGIN
    SET @LastCollectionAttrName = 'CommservSurveyPublicTop10ErrorsCollectionDate'
END

DECLARE @nMonths INT 
DECLARE @lastCollectionDate DATETIME
IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
BEGIN
	DECLARE @lastCollectionDateSTR VARCHAR(256)='';
	EXEC GetSurveyParamValue @LastCollectionAttrName, @lastCollectionDateSTR OUTPUT 
	IF @lastCollectionDateSTR <> ''
	BEGIN
		SELECT @lastCollectionDate = CAST(@lastCollectionDateSTR AS DATE)
		IF DATEDIFF(MONTH, @lastCollectionDate, GETDATE()) <= 0
			SET @nMonths = 1
		ELSE
			SET @nMonths = 2
	END
	ELSE
		SET @nMonths = 2
END
ELSE
BEGIN
	IF EXISTS(SELECT value from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName)
	BEGIN
	
		SELECT @lastCollectionDate = CAST(CAST(value AS NVARCHAR(256)) AS DATE) from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName	

		IF DATEDIFF(MONTH, @lastCollectionDate, GETDATE()) <= 0
			SET @nMonths = 1
		ELSE
			SET @nMonths = 2
	END
	ELSE
	BEGIN
		SET @nMonths = 2
	END
END
DECLARE @nowTime INT = dbo.GetUnixTime(GetUTCDate())

DECLARE @oneHour INT = 3600
DECLARE @secondsInADay INT = 24 * @oneHour

DECLARE @nDays int = 1

-- This query is to get Top 10 Errors in last 1 day along with the number of jobs affected by each failure error code

DECLARE @topNErrors INT = 10
DECLARE @fromTime INT = @nowTime - (@nDays * @secondsInADay)
DECLARE @toTime INT = @nowTime 

IF OBJECT_ID('tempdb.dbo.#JobFailureReason') IS NOT NULL DROP TABLE #JobFailureReason
CREATE TABLE #JobFailureReason (jobId INT, commCellId INT, failureReason VARCHAR(256))

IF OBJECT_ID('tempdb.dbo.#JobErrorMsg') IS NOT NULL DROP TABLE #JobErrorMsg
CREATE TABLE #JobErrorMsg (messageId INT, jobId INT, failureId INT)

IF OBJECT_ID('tempdb.dbo.#DrillDownFailureReason') IS NOT NULL DROP TABLE #DrillDownFailureReason
CREATE TABLE  #DrillDownFailureReason(messageId INT, numberOfJobs INT, errorCode NVARCHAR(64), ErrorAdvice NVARCHAR(1024))

DECLARE @FailureReason TABLE (messageId INT, numberOfJobs INT, errorCode NVARCHAR(64), errorMsg NVARCHAR(1024))



DECLARE @Top10Errors XML
DECLARE @TopDrillDownJPR XML

DECLARE @STARTDATE  DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1 ,0)
DECLARE @ENDDATE    DATE 
DECLARE @i INT = 0

WHILE @i <= @nMonths
BEGIN	
	INSERT	INTO #JobFailureReason
	SELECT 	J.jobId, J.commCellId, J.failureReason
	FROM	JMJobInfo J WITH(NOLOCK)
			INNER JOIN JMBkpJobInfo B ON J.jobId = B.jobId
	WHERE	J.state NOT IN (1,4,9,12,17,19) AND J.failureReason <> ''

	INSERT	INTO #JobFailureReason
	SELECT 	jobId, commCellId, failureReason
	FROM	JMBkpStats WITH(NOLOCK)
	WHERE	failureReason <> ''
		AND servEndDate BETWEEN @fromTime AND @toTime
		AND opType IN (4, 14, 18, 30, 43, 59, 60, 65, 76, 87, 91, 94, 97, 98, 101) AND commCellId = 2
        AND status IN (2, 4, 9)
 
 
------------------------------------------------------------------------------------		
	INSERT	INTO #JobErrorMsg
	SELECT	M.messageId, M.jobId, MAX(M.id)
	FROM	#JobFailureReason J
			CROSS APPLY dbo.SplitIDString(J.failureReason) FR
			INNER JOIN JMFailureReasonMsg M ON J.jobId = M.jobId AND J.commCellId = M.commCellId AND M.id = CAST(FR._ID AS INT)
	GROUP BY M.messageId, M.jobId

	INSERT	INTO @FailureReason
	SELECT	TOP (@topNErrors) messageId, COUNT(*), '', ''
	FROM	#JobErrorMsg M
			INNER JOIN #JobFailureReason B ON M.jobId = B.jobId
	GROUP BY messageId 
	ORDER BY COUNT(*) DESC

	UPDATE  R SET errorCode = CAST(M.SubsystemID AS NVARCHAR(12)) + ':' + CAST(M.MessageNum AS NVARCHAR(16)), 
        errorMsg = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            M.Message ,'^', '...') 
            ,'...1', '...'),'...2', '...'),'...3', '...'),'...4', '...'),'...5', '...'),'...6', '...'),'...7', '...'),'...8', '...'),'...9', '...'),'...0', '...')
            , '%s', ''), '%a', ''), '%d', ''), '%ld', ''), '%lld', ''), '%x', ''), '%lx', ''),'[...]','...'),'...','[...]')
	FROM	@FailureReason R
			INNER JOIN EvLocaleMsgs M ON R.messageId = M.MessageID AND M.LocaleID = 0
	
	---below code is added to populate drill down failure information and saved into #DrillDownFailureReason ----------------
		--------- now the Advice will be like "Media[]: Library is disabled. Advice: Please check the library status on the Console." use below code to polish data
	INSERT INTO #DrillDownFailureReason(messageId, numberOfJobs, errorCode, ErrorAdvice)
	select MessageID,count(jobID), ErroCode,
			CASE WHEN charindex('Advice:',Advice) > 0 and charindex(']:',Advice) > 0 THEN substring(Advice,charindex(']:',Advice)+2,len(Advice)) 
		         WHEN charindex('Advice:',Advice) > 0 and charindex(']:',Advice) = 0  THEN substring(Advice,charindex('Advice:',Advice),len(Advice))
				 ELSE ErrorMsg
			END as ErrorAdvice
	FROM
	(SELECT distinct R.MessageID as MessageID,
	                 R.JobID as JobId,
					 F.errorCode as ErroCode,
					 dbo.JMGetLocalizedMessageFunc(0,cast(R.failureId as nvarchar(max))+',') as Advice,
					 F.errorMsg as ErrorMsg
	FROM #JobErrorMsg R
	INNER JOIN @FailureReason F
	ON R.messageID = F.messageID)T
	GROUP BY MessageID,ErroCode,
	CASE WHEN charindex('Advice:',Advice) > 0 and charindex(']:',Advice) > 0 THEN substring(Advice,charindex(']:',Advice)+2,len(Advice)) 
		 WHEN charindex('Advice:',Advice) > 0 and charindex(']:',Advice) = 0  THEN substring(Advice,charindex('Advice:',Advice),len(Advice))
		 ELSE ErrorMsg
	END
	-------for all list error code, using full error string --------------------------------------------------------------------------------
	UPDATE  D
	SET D.ErrorAdvice = dbo.JMGetLocalizedMessageFunc(0,cast(R.failureId as nvarchar(max))+',') 
	FROM #DrillDownFailureReason  D
	INNER JOIN #JobErrorMsg R
	ON D.messageId = R.messageID
	WHERE D.ErrorCode IN ('32:289','19:1335','72:151','18:106','82:158','91:139','91:113','18:146','94:13','32:487','32:392','19:1327','30:325')
	
--" Mount path is offline. Advice: Please check if the mount path is online and accessible.  The offline reason shown on mount path properties will indicate why it's unavailable. &lt;br&gt;Source: patchnext, Process: JobManager"--
	UPDATE #DrillDownFailureReason
	SET ErrorAdvice = CASE WHEN charindex('Source:',ErrorAdvice) > 0  then replace(substring(ErrorAdvice,0,charindex('Source:',ErrorAdvice)),'<br>','') 
	                       ELSE ErrorAdvice
						   END
	
------------------------------------------------------------------------------------------------------------------------------------
	

	IF @i = 0
	BEGIN
		SET @Top10Errors = (
			SELECT (
				SELECT
					errorCode AS '@errorCode',
					dbo.NormalizeForXML(errorMsg) AS '@errorDescription',
					numberOfJobs AS '@numberOfJobsAffected',
					messageId AS '@messageId'
				FROM @FailureReason
				ORDER BY numberOfJobs DESC
				FOR XML PATH ('failedErrorList'), TYPE),
				(SELECT
					errorCode AS '@errorCode',
					dbo.NormalizeForXML(ErrorAdvice) AS '@AdviceDescription',
					numberOfJobs AS '@numberOfJobsAffected',
					messageId AS '@messageId'
				FROM #DrillDownFailureReason
				ORDER BY numberOfJobs DESC
				FOR XML PATH ('failedErrorAdviceList'), TYPE)
			
			FOR XML PATH('JobManager_TopMostJobErrorsResp')			
			)

    END
	ELSE
	BEGIN	
		DECLARE @tempXML XML
		SET @tempXML = (
				SELECT
					@STARTDATE AS '@MonthStart',
					errorCode AS '@errorCode',
					dbo.NormalizeForXML(errorMsg) AS '@errorDescription',
					numberOfJobs AS '@numberOfJobsAffected'
				FROM @FailureReason
				ORDER BY numberOfJobs DESC
				FOR XML PATH ('MonthlyFailedErrorList')
				)
		IF @tempXML IS NOT NULL
			SET @Top10Errors.modify('insert sql:variable("@tempXML") into (JobManager_TopMostJobErrorsResp)[1]')
	END

	SET @ENDDATE = @STARTDATE
	SET @STARTDATE = DATEADD(MONTH, -1, @ENDDATE)

	SET @fromTime = dbo.GetUnixTime(dbo.LocalToUTCTime(CONVERT(DATETIME, DATEADD(DAY, 0, @STARTDATE)), @csTimeZone))
	SET @toTime = dbo.GetUnixTime(dbo.LocalToUTCTime(CONVERT(DATETIME, DATEADD(DAY, 0, @ENDDATE)), @csTimeZone))

	SET @i += 1
	
	DELETE FROM #JobFailureReason
	DELETE FROM #JobErrorMsg
	DELETE FROM @FailureReason
	DELETE FROM #DrillDownFailureReason
END

--Update the last collection time
DECLARE @nreleaseId INT =1
SET @nreleaseId =(SELECT releaseId from APP_Client WITH (NOLOCK) where id =2)



SET @surveyXML = cast((@Top10Errors) as nvarchar(max)) 
--------- 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',
		@nDays as '@LastNDays',
		@surveyXML
	FOR XML PATH('Rpt_CSSXMLDATA')
)
	 
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
	IF @LastCollectionDate IS NULL
		SELECT @LastCollectionDate = GETDATE()
	EXEC SetSurveyParamValue @LastCollectionAttrName, @LastCollectionDate
END
ELSE
BEGIN
	IF EXISTS(SELECT value from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName)
		UPDATE GXGlobalParam SET value = CAST(@LastCollectionDate as NVARCHAR(MAX)) where name = @LastCollectionAttrName
	ELSE
	BEGIN
		DECLARE @nstring NVARCHAR(MAX)
		IF(@nreleaseId >= 15 )
		BEGIN
			SET @nstring ='insert into GXGlobalParam values(''' + @LastCollectionAttrName + ''', ''' + CAST(GETDATE() as NVARCHAR(MAX)) + ''', dbo.GetUnixTime(GetUTCDate()), 0)'
			EXEC sp_executesql @nstring
		END
		ELSE IF(@nreleaseId = 14 )
		BEGIN
			insert into GXGlobalParam (name, value) values(@LastCollectionAttrName, CAST(GETDATE() as NVARCHAR(MAX)))
		END
	END
END

SET NOCOUNT OFF


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