--Name:- Unfinished Jobs
--Description:- Collect unfinished jobs. Reasons for a job not finishing -> failed, failed to start and killed.

SET NOCOUNT ON
SET	TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

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

DECLARE @startTime INT = dbo.GetUnixTime(DATEADD(DAY, -1, GETUTCDATE()))

IF object_id('tempdb.dbo.#JobDetails') is not null DROP TABLE #JobDetails 
CREATE TABLE #JobDetails (
		JobId INT, clientName NVARCHAR(255), idaName NVARCHAR(255), instance NVARCHAR(512), backupset NVARCHAR(128), 
		subclient NVARCHAR(128), data_sp NVARCHAR(144), BkpLevel NVARCHAR(256), jobstatus NVARCHAR(128),FailureMsg NVARCHAR(1024), FailureReason NVARCHAR(512),
		StartDate INT, EndDate INT
)

INSERT INTO #JobDetails
	SELECT
		B.jobId,
		ISNULL(C.name, 'N/A'),
		ISNULL(I.name, 'N/A'),
		ISNULL(INS.name, 'N/A'),
		ISNULL(BS.name, 'N/A'),
		ISNULL(A.subclientName, 'N/A'),
		ISNULL(AG.name, 'N/A'),
		CASE B.bkplevel
		WHEN 1 THEN 'Full'
		WHEN 2 THEN 'Incremental'
		WHEN 4 THEN 'Differential'
		WHEN 0x40 THEN 'SyntheticFull'
		WHEN 0x4000 THEN 'SyntheticFull'
		WHEN 0x100 THEN 'Transaction Log w. NoTruncate'
		WHEN 0x200 THEN 'ASR'
		WHEN 0x800 THEN 'Snapshot'
		WHEN 0x1000 THEN 'New Volume Creation'
		WHEN 0x2000 THEN 'Incremental Volume Update'
		WHEN 0x8000 THEN 'Online Full'
		WHEN 0x400 THEN 'Offline Full'
		WHEN 0x20000 THEN 'Analysis'
		WHEN 0x40000 THEN 'Discovery'
		ELSE 'Unknown'
		END,		
		
		CASE B.status
		WHEN 1 THEN 'Success'
		WHEN 2 THEN 'Failed'
		WHEN 3 THEN 'PartialSuccess'
		WHEN 4 THEN 'Killed'
		WHEN 5 THEN 'Stopped'
		WHEN 6 THEN 'Interrupted'
		WHEN 9 THEN 'Failed to Start'
		ELSE 'Unknown'
		END,
		CASE WHEN dbo.JMGetLocalizedMessageFunc(0, failureReason) = ''
	 	 	 THEN dbo.NormalizeForXML(dbo.JMGetLocalizedMessageFunc(0, dbo.GetJobFailureReason(B.jobId)))
			 ELSE dbo.NormalizeForXML(dbo.JMGetLocalizedMessageFunc(0, failureReason))
		END,
		failureReason,
		B.servStartDate,
		B.servEndDate
	FROM JMBkpStats B
		LEFT JOIN archGroup AG ON AG.id = (CASE WHEN B.dataArchGrpId > 1 THEN B.dataArchGrpId ELSE B.logArchGrpId END) AND AG.id > 1
		LEFT JOIN APP_Application A ON B.appId = A.id
		LEFT JOIN APP_Client C ON A.clientId = C.id
		LEFT JOIN APP_iDAType I ON A.appTypeId = I.type
		LEFT JOIN APP_InstanceName INS ON A.instance = INS.id
		LEFT JOIN APP_BackupSetName BS ON A.backupset = BS.id
	WHERE B.servEndDate > @startTime 
		AND B.opType IN (4, 14, 18, 30, 43, 59, 60, 65, 76, 87, 91, 94, 97, 98, 101) AND B.commCellId = 2
		AND B.status IN (2, 4, 9)

INSERT INTO #JobDetails
	SELECT
		B.jobId,
		ISNULL(C.name, 'N/A'),
		ISNULL(I.name, 'N/A'),
		ISNULL(INS.name, 'N/A'),
		ISNULL(BS.name, 'N/A'),
		ISNULL(A.subclientName, 'N/A'),
		ISNULL(AG.name, 'N/A'),
		CASE B.bkplevel
		WHEN 1 THEN 'Full'
		WHEN 2 THEN 'Incremental'
		WHEN 4 THEN 'Differential'
		WHEN 0x40 THEN 'SyntheticFull'
		WHEN 0x4000 THEN 'SyntheticFull'
		WHEN 0x100 THEN 'Transaction Log w. NoTruncate'
		WHEN 0x200 THEN 'ASR'
		WHEN 0x800 THEN 'Snapshot'
		WHEN 0x1000 THEN 'New Volume Creation'
		WHEN 0x2000 THEN 'Incremental Volume Update'
		WHEN 0x8000 THEN 'Online Full'
		WHEN 0x400 THEN 'Offline Full'
		WHEN 0x20000 THEN 'Analysis'
		WHEN 0x40000 THEN 'Discovery'
		ELSE 'Unknown'
		END,		
		
		CASE J.state
		WHEN 2 THEN 'Delayed'
		WHEN 3 THEN 'Delayed'
		WHEN 5 THEN 'Delayed'
		WHEN 6 THEN 'Running'
		WHEN 7 THEN 'Running'
		WHEN 8 THEN 'Running'
		WHEN 10 THEN 'Failed'
		WHEN 11 THEN 'Killed'
		WHEN 13 THEN 'Running'
		WHEN 14 THEN 'Delayed'
		WHEN 15 THEN 'Delayed'
		WHEN 16 THEN 'Delayed'
		WHEN 18 THEN 'Failed'
		ELSE 'Unknown'
		END,
		CASE WHEN dbo.JMGetLocalizedMessageFunc(0, J.failureReason) = ''
	 	 	 THEN dbo.NormalizeForXML(dbo.JMGetLocalizedMessageFunc(0, dbo.GetJobFailureReason(B.jobId)))
			 ELSE dbo.NormalizeForXML(dbo.JMGetLocalizedMessageFunc(0, J.failureReason))
		END,
		J.failureReason,
		J.jobStartTime,
		0
	FROM	JMJobInfo J
			INNER JOIN JMBkpJobInfo B ON J.jobId = B.jobId
			LEFT JOIN #JobDetails T ON J.jobId = T.jobId
			LEFT JOIN archGroup AG ON AG.id = (CASE WHEN B.dataPolicy > 1 THEN B.dataPolicy ELSE B.logPolicy END) AND AG.id > 1
			LEFT JOIN APP_Application A ON B.applicationId = A.id
			LEFT JOIN APP_Client C ON A.clientId = C.id
			LEFT JOIN APP_iDAType I ON A.appTypeId = I.type
			LEFT JOIN APP_InstanceName INS ON A.instance = INS.id
			LEFT JOIN APP_BackupSetName BS ON A.backupset = BS.id
	WHERE	T.jobId IS NULL AND J.state NOT IN (1,4,9,12,17,19) AND J.failureReason <> ''
	
SET @surveyXML =(
	SELECT	DISTINCT
		JobId AS 'unfinishedJob/@jobId',
		dbo.NormalizeForXML(clientName) AS 'unfinishedJob/@clientName',
		dbo.NormalizeForXML(idaName) AS 'unfinishedJob/@idataagent',
		dbo.NormalizeForXML(instance) AS 'unfinishedJob/@instance',
		dbo.NormalizeForXML(backupset) AS 'unfinishedJob/@backupset',
		dbo.NormalizeForXML(subclient) AS 'unfinishedJob/@subclient',
		dbo.NormalizeForXML(data_sp) AS 'unfinishedJob/@data_sp',
		BkpLevel AS 'unfinishedJob/@backuplevel',
		jobstatus AS 'unfinishedJob/@jobstatus',
		FailureMsg AS 'unfinishedJob/@jobfailedreason',
		(Select CAST(C.SubsystemID AS NVARCHAR(12)) + ':' + CAST(C.MessageNum AS NVARCHAR(16)) + ' '
		FROM	JMFailureReasonMsg B WITH (NOLOCK)
			INNER JOIN EvLocaleMsgs C WITH (NOLOCK) ON B.messageId = C.MessageID AND C.LocaleID = 0
			and J.FailureReason like '%' + cast (B.id as nvarchar(50)) +'%'
		where B.jobid= J.jobid and  J.FailureReason <> ''
		FOR XML PATH(''))  AS 'unfinishedJob/@errorCode',
		StartDate AS 'unfinishedJob/@startdateUTC',
		EndDate AS 'unfinishedJob/@enddateUTC'
	FROM #JobDetails J
	
	FOR XML PATH ('unfinishedJobsXML')
)
	
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 object_id('tempdb.dbo.#JobDetails') is not null DROP TABLE #JobDetails 

SET NOCOUNT OFF