-- 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 = 208   -- TODOTDO

DECLARE @surveyXML NVARCHAR(MAX) = ''



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







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



--------------------------------------------------------

---  CommservSurveyQuery_208.sql   Ver 20170322_1328 ---

--------------------------------------------------------



--------- Insert your SQL statements here 

DECLARE @NumDaysBack AS INT = 3



--- CLEAN UP TEMPORARY TABLES UP FRONT PLEASE ! ----

IF object_id('tempdb.dbo.#JobDetails') is not null DROP TABLE #JobDetails 

IF object_id('tempdb.dbo.#RunningJobs') is not null DROP TABLE #RunningJobs 



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



DECLARE @isPrivateRunning varchar(256)='';

DECLARE @frequencyMode	 NVARCHAR(MAX) ='0';



IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))

    SELECT @frequencyMode = mode from #MetricsInputParams



IF EXISTS(SELECT name FROM GXGlobalParam WITH (NOLOCK) WHERE name = 'CommservSurveyRunning')

	SELECT @isPrivateRunning = value from GXGlobalParam WITH (NOLOCK)  where name ='CommservSurveyRunning'



IF (@isPrivateRunning = 'Diagnostics and Usage')	--Public

BEGIN

	DECLARE @isJobDetailsCollectionEnabled INT = 0

	SELECT	@isJobDetailsCollectionEnabled = CAST(CAST(value AS VARCHAR(20)) AS INT) FROM GXGlobalParam WITH (NOLOCK) WHERE	name = 'CommservSurveyPublicJobStatsEnabled'

	IF (@isJobDetailsCollectionEnabled <> 1) --No need to collect job details

	BEGIN

		DECLARE @relId INT

		SELECT @relId = MAX(id) FROM simAllGalaxyRel		

		IF (@relId <= 15) --V10 or below done have check for null value. So set outputxml with surveyxml as blank

		BEGIN

			SET @outputXML = 

			(

				SELECT

					@queryId AS '@QueryId',

					dbo.GetUnixTime(GETUTCDATE()) AS '@LogDate',

					0 AS '@QueryRunningTime',

					@surveyXML

				FOR XML PATH('Rpt_CSSXMLDATA')

			)			

		END

		RETURN

	END

END

	

DECLARE @TIME_STAMP_NAME varchar(80)



IF ((@frequencyMode = '1') or (@isPrivateRunning = 'Metrics Reporting'))

    SET @TIME_STAMP_NAME = 'CommservSurveyPrivateAdminJobStatsCollectionTime'

else IF ((@frequencyMode = '3') or (@isPrivateRunning = 'Metrics Direct Dip'))

    SET @TIME_STAMP_NAME = 'CommservSurveyDirectDIPAdminJobStatsCollectionTime'

ELSE

    SET @TIME_STAMP_NAME = 'CommservSurveyPublicAdminJobStatsCollectionTime'



IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL

BEGIN

	DECLARE @LastCollectionTimeSTR NVARCHAR(256) = ''

	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(DD, DATEDIFF(DD, 0, GETDATE())-@NumDaysBack, 0)

	SET	@LastCollection	= dbo.ClientLocalToUTCTime(@LastCollection, 2)

	DECLARE @CSFirstJobTime INT

	SELECT @CSFirstJobTime = MIN(servEnd) FROM JMAdminJobStatsTable B WITH (NOLOCK)	

	

	IF (@CSFirstJobTime < dbo.GetUnixTime(@LastCollection))

		SET @LastCollectionTime = dbo.GetUnixTime(@LastCollection) 

	ELSE

		SET @LastCollectionTime = @CSFirstJobTime

END



IF	@LastCollectionTime < (@CurrentTime - @NumDaysBack*24*3600)

	SET @CurrCollectionTime = (@LastCollectionTime + @NumDaysBack*24*3600)



DECLARE @releaseId INT

SELECT @releaseId = MAX(id) FROM simAllGalaxyRel

		

CREATE TABLE #JobDetails (

		JobId INT,

		AppId INT,

		AppTypeId INT,

		ArchGrpId INT,

		OpType INT,

		subOpType INT,

		BkpLevel INT,

		Status INT,

		StartDate INT,

		EndDate INT,

		DataProtected BIGINT,

		DataTransferred BIGINT,

		DataWritten BIGINT, 

		ErrorCode VARCHAR(32),

		FailureReason NVARCHAR(512),

		SubTaskId INT,

		Attempts INT,

		initFrom INT,

		NoOFPhases INT,

		failureErrorCode	INT,

		workflowName NVARCHAR(256)

)





CREATE TABLE #RunningJobs

(

	JobId INT,

	AppId INT,

	OpType INT,

	subOpType INT,

	BkpLevel INT,

	StartDate INT,

	State INT,

	InitFrom INT,

	FailureReason NVARCHAR(MAX)

	

)



IF @releaseId > 14 --above v9

BEGIN

	INSERT INTO #JobDetails

		SELECT	B.jobId,

			appId,

			1000,

			B.archGrpID,

			B.opType,

			JS.subOpType,

			bkpLevel= ER_bkpLevel,

			displayStatus=CASE dataStatus WHEN 0 THEN B.status ELSE dataStatus END,			

			servStartDate=servStart,

			servEndDate=servEnd,

			totalUncompBytes=totalSize,

			nwTransBytes,

			0,

			'',

			CASE WHEN CHARINDEX(',', failureReason) = LEN(failureReason) THEN failureReason

				ELSE RIGHT(failureReason, CHARINDEX(',', REVERSE(failureReason), 2)-1) END,

			subTaskId,

			numAttempts=0,

			B.initFrom,

			0,

			b.failureErrorCode,

			CASE WHEN B.opType in (90,39) THEN (Select name from WF_Definition where WorkflowId = B.workFlowId)

			ELSE '' end

	FROM	JMAdminJobStatsTable B 

	INNER JOIN JMJobStats JS WITH (NOLOCK) ON B.jobId = JS.jobId AND JS.commCellId = B.commCellId

	WHERE	servEnd BETWEEN @LastCollectionTime AND @CurrCollectionTime

END

ELSE

BEGIN

	INSERT INTO #JobDetails

		SELECT	B.jobId,

			appId,

			1000,

			B.archGrpID,

			B.opType,

			JS.subOpType,

			bkpLevel= ER_bkpLevel,

			displayStatus=CASE dataStatus WHEN 0 THEN B.status ELSE dataStatus END,			

			servStartDate=servStart,

			servEndDate=servEnd,

			totalUncompBytes=totalSize,

			nwTransBytes,

			0,

			'',

			CASE WHEN CHARINDEX(',', failureReason) = LEN(failureReason) THEN failureReason

				ELSE RIGHT(failureReason, CHARINDEX(',', REVERSE(failureReason), 2)-1) END,

			subTaskId,

			numAttempts=0,

			B.initFrom,

			0,

			b.failureErrorCode,

			''

			FROM	JMAdminJobStatsTable B

			INNER JOIN JMJobStats JS WITH (NOLOCK) ON B.jobId = JS.jobId AND JS.commCellId = B.commCellId

	WHERE	servEnd BETWEEN @LastCollectionTime AND @CurrCollectionTime

END



	INSERT INTO #RunningJobs

		SELECT	B.jobId,AppId=0,B.opType,B.subOpType,BkpLevel=J.ER_BkpLevel, B.jobStartTime,B.STATE,

		 B.initFrom, dbo.JMGetLocalizedMessageFunc(0, B.failureReason)

		FROM JMJobInfo B 

		    INNER JOIN JMAdminJobInfoTable J WITH (NOLOCK) ON J.jobId = B.jobId











UPDATE	#JobDetails

SET		ErrorCode = CAST(C.SubsystemID AS NVARCHAR(12)) + ':' + CAST(C.MessageNum AS NVARCHAR(16)),

		FailureReason = dbo.NormalizeForXML(LEFT(dbo.JMGetLocalizedMessageFunc(0, A.FailureReason), 512))

FROM	#JobDetails 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.Status <> 1 AND A.FailureReason <> ''



			 

update #JobDetails

SET		NoOFPhases = A.numOfPhases,

		Attempts = A.numOfAttempts

FROM	#JobDetails B

INNER JOIN (Select A.jobId, COUNT(DISTINCT A.phaseNum) AS numOfPhases,COUNT(*) AS numOfAttempts

				FROM JMAdminJobAttemptStatsTable A 	INNER JOIN #JobDetails B ON B.jobId = A.jobId AND 2= A.commCellId 

				GROUP BY A.jobId

			 ) A

ON A.jobId = B.JobId



			 



SET @surveyXML = (SELECT

	(SELECT	J.JobId AS '@JobId',

		J.Status AS '@Status',

		J.OpType AS '@OpType',

		J.subOpType AS '@SubOpType',

		J.BkpLevel AS '@BkpLevel',

		dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(J.StartDate), @csTimeZone)) AS '@StartDate',

		dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(J.EndDate), @csTimeZone)) AS '@EndDate',

		J.DataProtected AS '@DataProtected',

		J.ErrorCode AS '@ErrorCode',

		REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(J.FailureReason,

			',',' '), CHAR(10),';'), CHAR(13),';'), '''',' '), CHAR(145),' '), CHAR(146),' '), '<BR>','') AS '@FailureReason',

		dbo.NormalizeForXML(ISNULL(AG.name, 'N/A')) AS '@StoragePolicy',

		ISNULL(AG.id, '1') AS '@StoragePolicyID',

		T.taskName AS '@SchedulePolicy',

		ST.subTaskName AS '@ScheduleName',

		J.Attempts AS '@Attempts',

		J.initFrom AS '@InitFrom',

		J.NoOFPhases AS '@Phases',

		J.failureErrorCode AS '@failureErrorCode',

		J.workflowName AS '@workflowName'

	FROM	#JobDetails J

		LEFT OUTER JOIN archGroup AG WITH (NOLOCK) ON J.ArchGrpId = AG.id

		LEFT OUTER JOIN TM_SubTask ST WITH (NOLOCK) ON J.SubTaskId = ST.subTaskId

		LEFT OUTER JOIN TM_Task T WITH (NOLOCK) ON ST.taskId = T.taskId

	ORDER BY J.JobId

	FOR XML PATH('JobStats'), TYPE),



	(SELECT 	

          H.JobId as '@JobId',

          H.OpType       as   '@OpType',

		  H.subOpType  AS '@SubOpType',

          H.BkpLevel as '@BkpLevel',

          dbo.GetUnixTime(dbo.UTCToLocalTime(dbo.GetDateTime(H.StartDate), @csTimeZone)) as '@StartDate',

          H.State       as   '@State',

          H.InitFrom as '@InitFrom',

		  H.FailureReason as '@FailureReason'         

      FROM #RunningJobs H

      ORDER BY H.StartDate

      FOR XML PATH ('RunningJobStats'),TYPE)

FOR XML PATH ('JobDetails')

)



DROP TABLE #JobDetails

DROP TABLE #RunningJobs



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

)

--select cast (@surveyXML as XML)

-- UPDATE TIME STAMP TO GXGLOBALPARAM.  IF NOT ALREADY PRESET, MUST ACCOUNT

-- .. FOR OLDER VERSION OF GXGLOBALPARAM NOT HAVING AS MANY COLUMNS!

IF OBJECT_ID('SetSurveyParamValue') IS NOT NULL

BEGIN

	EXEC SetSurveyParamValue @TIME_STAMP_NAME, ''

END

ELSE

BEGIN

	DECLARE @DO_IT varchar(256)

	IF NOT EXISTS (SELECT * FROM GXGlobalParam WHERE name = @TIME_STAMP_NAME)

	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

		BEGIN

			SET @DO_IT = 'INSERT INTO GXGlobalParam (name, value, created, modified) VALUES('''

			 + @TIME_STAMP_NAME

			 + ''', '''','

			 + CONVERT(varchar(20), @logDate)                   -- Use Proper "create" date!

			 + ',0)'

			EXEC (@DO_IT)

		END

	END

END



IF object_id('tempdb.dbo.#JobDetails') is not null DROP TABLE #JobDetails 





	 

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