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

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

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRY

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

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


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

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

-- Fallen Behind Backup Copies

DECLARE @nreleaseId INT =1
SET @nreleaseId =(SELECT releaseId from APP_Client WITH (NOLOCK) where id =2)

DECLARE @LastCollectionAttrName VARCHAR(256)
DECLARE @FirstCollectionAttrName VARCHAR(256)
DECLARE @isPrivateRunning varchar(256)='';
DECLARE @freequencyMode  NVARCHAR(MAX) ='0';
DECLARE @nstring NVARCHAR(MAX)

IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#MetricsInputParams'))
    SELECT @freequencyMode = 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 = 'Metrics Reporting') OR (@freequencyMode = '1'))
BEGIN
    SET @LastCollectionAttrName = 'CommservSurveyPrivateFallenBehindBackupCopiesCollectionTime'
	SET @FirstCollectionAttrName = 'CommservSurveyPrivateFallenBehindBackupCopiesFirstCollectionTime'
END
ELSE IF ((@isPrivateRunning = 'Metrics Direct Dip') OR (@freequencyMode = '3'))
BEGIN
    SET @LastCollectionAttrName = 'CommservSurveyDirectDipFallenBehindBackupCopiesCollectionTime'
	SET @FirstCollectionAttrName = 'CommservSurveyDirectDipFallenBehindBkpCopiesFirstCollectionTime'
END
ELSE
BEGIN
    SET @LastCollectionAttrName = 'CommservSurveyPublicFallenBehindBackupCopiesCollectionTime'
	SET @FirstCollectionAttrName = 'CommservSurveyPublicFallenBehindBackupCopiesFirstCollectionTime'
END


DECLARE @CurrCollectionDate BIGINT = dbo.getUnixTime(CAST(GETDATE() AS DATE))
DECLARE @PrevCollectionDate BIGINT
DECLARE @FirstCollectionDate BIGINT 

IF OBJECT_ID('GetSurveyParamValue') IS NOT NULL
BEGIN
	DECLARE @tempcollectionTimeSTR VARCHAR(256) = '';
	EXEC GetSurveyParamValue @LastCollectionAttrName, @tempcollectionTimeSTR OUTPUT
		SELECT @PrevCollectionDate = CAST(@tempcollectionTimeSTR AS BIGINT)
	EXEC GetSurveyParamValue @FirstCollectionAttrName, @tempcollectionTimeSTR OUTPUT
		SELECT @FirstCollectionDate = CAST(@tempcollectionTimeSTR AS BIGINT)
END
ELSE
BEGIN
	SELECT  @PrevCollectionDate = CAST(CAST(value AS VARCHAR(20)) AS BIGINT) from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName
	SELECT @FirstCollectionDate = CAST(CAST(value AS VARCHAR(20)) AS BIGINT) from GXGlobalParam WITH (NOLOCK) where name = @FirstCollectionAttrName
END

DECLARE @NoOfDays INT = 1 --Number of days for which results have to be collected during the first collection.
IF @PrevCollectionDate IS NULL
BEGIN
    SET @PrevCollectionDate = @CurrCollectionDate - (@NoOfDays * 24 * 60 * 60)
    SET @FirstCollectionDate = 0
END
ELSE
IF @FirstCollectionDate IS NULL
    SET @FirstCollectionDate = @PrevCollectionDate

IF OBJECT_ID('tempdb.dbo.#FallenBehindBackupCopyJobs') IS NOT NULL DROP TABLE #FallenBehindBackupCopyJobs
CREATE TABLE #FallenBehindBackupCopyJobs
(
	SnapBackupJobId INT,
	BackupCopyJobId INT,
	BkpCopyJobStatus INT,
	BkpCopyJobFailureReason NVARCHAR(MAX)
)

IF OBJECT_ID('tempdb.dbo.#FallenBehindBackupCopies') IS NOT NULL DROP TABLE #FallenBehindBackupCopies
CREATE TABLE #FallenBehindBackupCopies 
(
	JobId INT, 
	SubclientId INT,
	archGroupCopyId INT,
	JobEndDateUTC INT,
	isNewJob INT,
	BkpCopyJob INT,
	BkpCopyJobStatus INT,
	BkpCopyFailureReason NVARCHAR(MAX)
)

INSERT INTO #FallenBehindBackupCopies
SELECT 
	JMBS.jobid, 
	JMBS.appId,
	AGC.id,
	JMBS.servEndDate,
	CASE WHEN JMBS.servEndDate < @PrevCollectionDate THEN 0 ELSE 1 END,
	-1,
	-1,
	''
FROM JMJobSnapshotStats (NOLOCK) JMJSS   
	JOIN JMBkpStats (NOLOCK) JMBS ON JMJSS.jobid = JMBS.jobid
	JOIN APP_Application APP (NOLOCK) ON APP.id = JMBS.appId
	JOIN archGroup AG (NOLOCK) ON AG.id = JMJSS.archGrpId
	JOIN archMaterializeSnapshotProp AMSP (NOLOCK) ON AMSP.archGroupId = JMJSS.archGrpId
	JOIN archGroupCopy AGC (NOLOCK) ON (AMSP.sourceCopyId != 0 AND AGC.id = AMSP.sourceCopyId) 
		OR (AMSP.sourceCopyId = 0 AND AGC.id = AG.defaultSnapCopy)
WHERE JMJSS.disabled = 0 
	AND JMJSS.materializationStatus != 100 
	AND JMBS.servEndDate > @FirstCollectionDate

INSERT INTO #FallenBehindBackupCopyJobs
SELECT JS.jobId, 
	   MAX(JDL.childJobId), 
	   JDL.childJobStatus, 
	   CASE WHEN CHARINDEX(',', failureReason) = LEN(failureReason) THEN failureReason
             ELSE RIGHT(failureReason, CHARINDEX(',', REVERSE(failureReason), 2)-1) END
FROM JMJobStats JS WITH (NOLOCK)
	INNER JOIN #FallenBehindBackupCopies F WITH (NOLOCK) ON F.JobId = JS.jobId
	LEFT OUTER JOIN JMBkpJobInfo JBI WITH (NOLOCK) ON JS.jobId = JBI.jobId AND JS.commCellId = JBI.commcellId
    LEFT OUTER JOIN JMBkpStats JBS WITH (NOLOCK) ON JS.jobId = JBS.jobId AND JS.commCellId = JBS.commCellId
    INNER JOIN JMJobWF JWF WITH (NOLOCK) ON JS.jobId = JWF.processedJobId AND JS.commCellId = JWF.commcellId
    INNER JOIN JMJobDataLink JDL WITH (NOLOCK) ON JWF.jobId = JDL.parentJobId AND JDL.commCellId = JWF.commcellId
                                  AND JDL.linkType = 4 /*LINK_TYPE_SNAPTAPEWORKFLOW_UNIT*/ AND (JDL.childAppid = JBI.applicationId OR JDL.childAppid = JBS.appId)
GROUP BY JS.jobId, failureReason, JBS.displayStatus, JDL.childJobStatus

UPDATE F
SET BkpCopyJob = BackupCopyJobId,
	F.BkpCopyJobStatus = C.BkpCopyJobStatus,
	BkpCopyFailureReason = BkpCopyJobFailureReason
FROM #FallenBehindBackupCopies F
INNER JOIN #FallenBehindBackupCopyJobs C ON C.SnapBackupJobId = F.jobId

SET @surveyXML = (
	SELECT 
		SubclientId AS '@appid',
		archGroupCopyId AS '@copy',
		JobId AS '@id',
		JobEndDateUTC AS '@enddate',
		IsNewJob AS '@isnew',
		BkpCopyJob AS '@BkpCopyJobId',
		BkpCopyJobStatus AS '@BkpCopyJobStatus',
		BkpCopyFailureReason AS '@BkpCopyJobFailureReason'
	FROM #FallenBehindBackupCopies T1
	FOR XML PATH('Job'), ROOT('FallenBehindBackupCopies')
)
IF OBJECT_ID('tempdb.dbo.#FallenBehindBackupCopyJobs') IS NOT NULL DROP TABLE #FallenBehindBackupCopyJobs
IF OBJECT_ID('tempdb.dbo.#FallenBehindBackupCopies') IS NOT NULL DROP TABLE #FallenBehindBackupCopies


--------- END SURVEY QUERY ---------

--------- BEGIN - GENERATED CODE, PLEASE DO NOT MODIFY ---------
--SELECT CAST (@surveyXML AS XML)

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')
)
	 
END TRY
BEGIN CATCH	 
  DECLARE @ErrorMessage NVARCHAR(4000);  
  SET @ErrorMessage = ERROR_MESSAGE();
  RAISERROR(@ErrorMessage,16,1);
RETURN
END CATCH

--Update the last collection time
IF OBJECT_ID('SetSurveyParamValue') IS NOT NULL
BEGIN
	EXEC SetSurveyParamValue @LastCollectionAttrName, @CurrCollectionDate
	EXEC SetSurveyParamValue @FirstCollectionAttrName, @FirstCollectionDate
END
ELSE
BEGIN
	IF EXISTS(SELECT value from GXGlobalParam WITH (NOLOCK) where name = @LastCollectionAttrName)
	BEGIN
		UPDATE GXGlobalParam SET value = CAST(@CurrCollectionDate as NVARCHAR(MAX)) where name = @LastCollectionAttrName
		UPDATE GXGlobalParam SET value = CAST(@FirstCollectionDate as NVARCHAR(MAX)) where name = @FirstCollectionAttrName
	END
	ELSE
	BEGIN
		IF @nreleaseId >= 15
		BEGIN
			SET @nstring = N'insert into GXGlobalParam values(''' + @LastCollectionAttrName + ''', ''' + CAST(@CurrCollectionDate as NVARCHAR(MAX)) + ''', dbo.GetUnixTime(GetUTCDate()), 0)'
			EXEC sp_executesql @nstring

			SET @nstring = N'insert into GXGlobalParam values(''' + @FirstCollectionAttrName + ''', ''' + CAST(@FirstCollectionDate as NVARCHAR(MAX)) + ''', dbo.GetUnixTime(GetUTCDate()), 0)'
			EXEC sp_executesql @nstring
		END
		ELSE
		BEGIN
			insert into GXGlobalParam (name, value) values(@LastCollectionAttrName, CAST(@CurrCollectionDate as NVARCHAR(MAX)))
			insert into GXGlobalParam (name, value) values(@FirstCollectionAttrName, CAST(@FirstCollectionDate as NVARCHAR(MAX)))
		END
	END

	IF NOT EXISTS(SELECT value from GXGlobalParam WITH (NOLOCK) WHERE name = @FirstCollectionAttrName)
	BEGIN
		IF @nreleaseId >=15
		BEGIN
			SET @nstring = N'insert into GXGlobalParam values(''' + @FirstCollectionAttrName + ''', ''' + CAST(@FirstCollectionDate as NVARCHAR(MAX)) + ''', dbo.GetUnixTime(GetUTCDate()), 0)'
			EXEC sp_executesql @nstring
		END
		ELSE
		BEGIN
			insert into GXGlobalParam (name, value) values(@FirstCollectionAttrName, CAST(@FirstCollectionDate as NVARCHAR(MAX)))
		END
	END

END

SET NOCOUNT OFF

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