

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/CloudDB/Sp/ParseJobDetails.sp] ---------- 

-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/CloudDB/Sp/ParseJobDetails.sp,v $ $Id: ParseJobDetails.sp,v 1.11.2.26.4.1 2021/06/28 21:05:20 junlu Exp $";
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


IF EXISTS (select * from sysobjects where name='ParseJobDetails')
BEGIN
	print '>>> Drop Stored Procedure: ParseJobDetails <<<'
	drop procedure ParseJobDetails
END
IF EXISTS (select * from GxQscripts where name='ParseJobDetails')
	delete from GxQscripts where name = 'ParseJobDetails'
GO

IF EXISTS (select * from GXDBVersions where aliasname='ParseJobDetails')
	delete from GXDBVersions where aliasname = 'ParseJobDetails'
GO
print '... Creating Procedure: ParseJobDetails'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure ParseJobDetails
  @QueryId INTEGER,
  @ResultId BIGINT,
  @CSUniqueId INTEGER,
  @LogDateUTC DATETIME,
  @LogDate DATETIME,
  @LogLevel INTEGER = 0
AS
SET NOCOUNT ON 
  DECLARE @StartDate DATETIME = GETUTCDATE() 
  DECLARE @TempDate  DATETIME 
  DECLARE @Duration  INTEGER = 0 
  DECLARE @SkipOnErr INTEGER = 1 
  DECLARE @ErrorCode INTEGER = 0 
  DECLARE @ErrorMsg  NVARCHAR(MAX) 
  DECLARE @LogString NVARCHAR(MAX) 
  IF @LogLevel > 1 
  BEGIN 
  	SELECT @LogString = 'Parsing result ' + CAST(@ResultId AS VARCHAR(20)) + ' (QueryId: ' + CAST(@QueryId AS VARCHAR(20)) + ', CSUniqueId: ' + CAST(@CSUniqueId AS VARCHAR(20)) + ') - started' 
  	INSERT INTO cf_SurveyLogger(Message) VALUES(@LogString) 
  END 
  SET @LogString = '' 
  BEGIN TRY 
  	BEGIN TRANSACTION ParseJobDetailsTran 
  	SELECT @ErrorCode = @@ERROR
--  QUERY SPECIFIC CODE BEGIN --
DECLARE @isPrivate 			INT = 0
SELECT	@isPrivate = ISNULL(CAST(Value AS INT), 0) FROM cf_SurveyConfig (NoLOCK) WHERE Name = 'isPrivate'
IF object_id('tempdb.dbo.#SurveySubclients') is not null DROP TABLE #SurveySubclients
IF object_id('tempdb.dbo.#SurveyJobStats') is not null DROP TABLE #SurveyJobStats
IF object_id('tempdb.dbo.#SurveyJobVMStats') is not null DROP TABLE #SurveyJobVMStats
IF object_id('tempdb.dbo.#SurveyJobDbSize') is not null DROP TABLE #SurveyJobDbSize
IF object_id('tempdb.dbo.#RunningJobStats') is not null DROP TABLE #RunningJobStats
IF object_id('tempdb.dbo.#tmpCompanyDailyJobs') IS NOT null DROP TABLE #tmpCompanyDailyJobs
IF object_id('tempdb.dbo.#tmpCompanyAggregateJobs') IS NOT null DROP TABLE #tmpCompanyAggregateJobs
IF object_id('tempdb.dbo.#tmpDailyJobs') IS NOT null DROP TABLE #tmpDailyJobs
IF object_id('tempdb.dbo.#tmpAggregateJobs') IS NOT null DROP TABLE #tmpAggregateJobs
CREATE TABLE #SurveySubclients (
	CommservUniqueId INT, AppId INT, AppTypeId INT, ClientName NVARCHAR(256), AgentName VARCHAR(256),
	InstanceName NVARCHAR(512), BackupsetName NVARCHAR(128), SubclientName NVARCHAR(128), ClientGroups NVARCHAR(MAX))
CREATE TABLE #SurveyJobStats (
	CommservUniqueId INT, JobId INT, AppId INT, OpType INT, BkpLevel INT, Status INT, StartDate INT, EndDate INT,
	ScanType INT, SizeChangePct INT, TransferTime INT, ProtectedObjects BIGINT, FailedObjects BIGINT, FailedFolders BIGINT,
	DataProtected BIGINT, DataCompressed BIGINT, DataTransferred BIGINT, DataWritten BIGINT,
	ErrorCode VARCHAR(32), FailureReason NVARCHAR(512), StoragePolicy NVARCHAR(144), StoragePolicyID INT, LogStoragePolicyID INT,
	SchedulePolicy NVARCHAR(256), ScheduleName NVARCHAR(256), Attempts INT,InitFrom INT,Phases INT,failureErrorCode	INT )
CREATE TABLE #SurveyJobVMStats (CommservUniqueId INT, JobId INT, vmClientId INT, vmBackupStart INT, vmBackupEnd INT, vmStatus INT,
	vmSize BIGINT, vmUsedSpace BIGINT, vmGuestSize BIGINT, vmIsGuestSizeValid INT, vmFailureReason NVARCHAR(1024), ParentJobId INT)
CREATE TABLE #SurveyJobDbSize (
	CommservUniqueId INT, JobId INT, JobEndDate INT, DbName NVARCHAR(1024), DbSize BIGINT)
CREATE TABLE #RunningJobStats (
	CommservUniqueId INT, JobId INT, AppId INT, OpType INT,	BkpLevel INT, StartDate INT, State INT,	InitFrom INT,created	 BIGINT, FailureReason NVARCHAR(MAX) )
CREATE TABLE #tmpCompanyDailyJobs(
	CommServUniqueId INT,
	JobEndDate DATETIME,
	ClientGroupId INT,
	TJobs INT,
	CJobs INT,
	CWEJobs INT,
	CWWJobs INT,
	FJobs INT,
	KJobs INT,
	SJobs INT,
	BackupSize BIGINT,
	DataWritten BIGINT,
	COJobs INT,
	FTSJobs INT
)
CREATE TABLE #tmpDailyJobs(
	CommServUniqueId INT,
	JobEndDate DATETIME,
	AppTypeId INT,
	TJobs INT,
	CJobs INT,
	CWEJobs INT,
	CWWJobs INT,
	FJobs INT,
	KJobs INT,
	SJobs INT,
	BackupSize BIGINT,
	DataWritten BIGINT,
	COJobs INT,
	FTSJobs INT
)
CREATE TABLE #tmpCompanyAggregateJobs
(
    CommServUniqueId INT,
    MonthStart DATETIME,
    ClientGroupId INT,
    TotalJobs INT,
    CompletedJobs INT,
    CWEJobs INT,
    CWWJobs INT,
    FailedJobs INT,
    KilledJobs INT,
    SkippedJobs INT,
    BackupSize BIGINT,
    DataWritten BIGINT,
    COJobs	INT,
	FTSJobs INT
)
CREATE TABLE #tmpAggregateJobs
(
    CommServUniqueId INT,
    MonthStart DATETIME,
    AppTypeId INT,
    TotalJobs INT,
    CompletedJobs INT,
    CWEJobs INT,
    CWWJobs INT,
    FailedJobs INT,
    KilledJobs INT,
    SkippedJobs INT,
    BackupSize BIGINT,
    DataWritten BIGINT,
    COJobs	INT,
	FTSJobs INT
)
DECLARE @LastDate DATETIME
DECLARE @LastTime BIGINT
DECLARE @LastCompanyDate BIGINT
DECLARE @LastCompanyDateTIME DATETIME
IF 	@ErrorCode = 0
BEGIN
	INSERT INTO #SurveySubclients (
		CommservUniqueId, AppId, AppTypeId, ClientName, AgentName, InstanceName, BackupsetName, SubclientName, ClientGroups)
	SELECT DISTINCT
		CommcellIdNameMap_ID,
		csInfo.n.value('@AppId', 'INT'),
		csInfo.n.value('@AppTypeId', 'INT'),
		csInfo.n.value('@ClientName', 'NVARCHAR(256)'),
		csInfo.n.value('@AgentName', 'VARCHAR(256)'),
		csInfo.n.value('@InstanceName', 'NVARCHAR(512)'),
		csInfo.n.value('@BackupsetName', 'NVARCHAR(128)'),
		csInfo.n.value('@SubclientName', 'NVARCHAR(128)'),
		csInfo.n.value('@ClientGroups', 'NVARCHAR(MAX)')
	FROM
		cf_CommservSurveyResults
			CROSS apply CollectedResults.nodes('/JobDetails/Subclients') AS csInfo(n)
	WHERE
		CommservSurveyResults_ID = @ResultId
	SELECT @ErrorCode = @@ERROR
END
IF 	@ErrorCode = 0
BEGIN
	INSERT INTO #SurveyJobStats (
		CommservUniqueId, JobId, AppId, OpType, BkpLevel, Status, StartDate, EndDate,
		ScanType, SizeChangePct, TransferTime, ProtectedObjects, FailedObjects, FailedFolders,
		DataProtected, DataCompressed, DataTransferred, DataWritten, ErrorCode, FailureReason,
		StoragePolicy, StoragePolicyID, LogStoragePolicyID, SchedulePolicy, ScheduleName, Attempts,InitFrom,Phases,failureErrorCode)
	SELECT DISTINCT
		CommcellIdNameMap_ID,
		csInfo.n.value('@JobId', 'INT'),
		csInfo.n.value('@AppId', 'INT'),
		csInfo.n.value('@OpType', 'INT'),
		csInfo.n.value('@BkpLevel', 'INT'),
		csInfo.n.value('@Status', 'INT'),
		csInfo.n.value('@StartDate', 'INT'),
		csInfo.n.value('@EndDate', 'INT'),
		csInfo.n.value('@ScanType', 'INT'),
		csInfo.n.value('@SizeChangePct', 'INT'),
		csInfo.n.value('@TransferTime', 'INT'),
		csInfo.n.value('@ProtectedObjects', 'BIGINT'),
		csInfo.n.value('@FailedObjects', 'BIGINT'),
		csInfo.n.value('@FailedFolders', 'BIGINT'),
		csInfo.n.value('@DataProtected', 'BIGINT'),
		csInfo.n.value('@DataCompressed', 'BIGINT'),
		csInfo.n.value('@DataTransferred', 'BIGINT'),
		csInfo.n.value('@DataWritten', 'BIGINT'),
		csInfo.n.value('@ErrorCode', 'VARCHAR(32)'),
		csInfo.n.value('@FailureReason', 'NVARCHAR(512)'),
		csInfo.n.value('@StoragePolicy', 'NVARCHAR(144)'),
        ISNULL(csInfo.n.value('@DataArchGroupId', 'INT'),0),
        ISNULL(csInfo.n.value('@LogArchGroupId', 'INT'),0),
		csInfo.n.value('@SchedulePolicy', 'NVARCHAR(256)'),
		csInfo.n.value('@ScheduleName', 'NVARCHAR(256)'),
		csInfo.n.value('@Attempts', 'INT'),
		ISNULL(csInfo.n.value('@InitFrom', 'INT'),0),
		ISNULL(csInfo.n.value('@Phases'  , 'INT'),0),
		ISNULL(csInfo.n.value('@failureErrorCode'  , 'INT'),0)
	FROM
		cf_CommservSurveyResults
			CROSS apply CollectedResults.nodes('/JobDetails/JobStats') AS csInfo(n)
	WHERE
		CommservSurveyResults_ID = @ResultId
	SELECT @ErrorCode = @@ERROR
END
IF 	@ErrorCode = 0
BEGIN
	INSERT INTO #SurveyJobVMStats (
		CommservUniqueId, JobId, vmClientId, vmBackupStart, vmBackupEnd, vmStatus,
		vmSize, vmUsedSpace, vmGuestSize, vmIsGuestSizeValid, vmFailureReason, ParentJobId)
	SELECT DISTINCT
		CommcellIdNameMap_ID,
		csInfo.n.value('@JobId', 'INT'),
		csInfo.n.value('@vmClientId', 'INT'),
		ISNULL(csInfo.n.value('@vmBackupStart', 'INT'), 0),
		ISNULL(csInfo.n.value('@vmBackupEnd', 'INT'), 0),
		ISNULL(csInfo.n.value('@vmStatus', 'INT'), 0),
		ISNULL(csInfo.n.value('@vmSize', 'BIGINT'), 0),
		ISNULL(csInfo.n.value('@vmUsedSpace', 'BIGINT'), 0),
		ISNULL(csInfo.n.value('@vmGuestSize', 'BIGINT'), 0),
		ISNULL(csInfo.n.value('@vmIsGuestSizeValid', 'INT'), 0),
		ISNULL(csInfo.n.value('@vmFailureReason', 'NVARCHAR(1024)'), ''),
		ISNULL(csInfo.n.value('@parentJobId', 'INT'), 0)
	FROM
		cf_CommservSurveyResults
		CROSS apply CollectedResults.nodes('/JobDetails/JobVMStats') AS csInfo(n)
	WHERE
		CommservSurveyResults_ID = @ResultId
	SELECT @ErrorCode = @@ERROR
END
IF 	@ErrorCode = 0
BEGIN
	INSERT INTO #SurveyJobDbSize (
		CommservUniqueId, JobId, JobEndDate, DbName, DbSize)
	SELECT DISTINCT
		CommcellIdNameMap_ID,
		csInfo.n.value('@JobId', 'INT'),
		csInfo.n.value('@JobEndDate', 'INT'),
		csInfo.n.value('@DbName', 'NVARCHAR(1024)'),
		csInfo.n.value('@DbSize', 'BIGINT')
	FROM
		cf_CommservSurveyResults
		CROSS apply CollectedResults.nodes('/JobDetails/JobDBSize') AS csInfo(n)
	WHERE
		CommservSurveyResults_ID = @ResultId
	SELECT @ErrorCode = @@ERROR
END
IF 	@ErrorCode = 0
BEGIN
	INSERT INTO #RunningJobStats (
		CommservUniqueId ,JobId ,AppId ,OpType ,BkpLevel ,StartDate ,State ,InitFrom,created,FailureReason)
	SELECT DISTINCT
		CommcellIdNameMap_ID,
		csInfo.n.value('@JobId', 'INT'),
		csInfo.n.value('@AppId', 'INT'),
		csInfo.n.value('@OpType', 'INT'),
		csInfo.n.value('@BkpLevel', 'INT'),
		csInfo.n.value('@StartDate', 'INT'),
		csInfo.n.value('@State', 'INT'),
		csInfo.n.value('@InitFrom', 'INT'),
		DBO.GETUNIXTIMEBIG(LogDateUTC),
		ISNULL(csInfo.n.value('@FailureReason', 'NVARCHAR(MAX)'), '')
	FROM
		cf_CommservSurveyResults
		CROSS apply CollectedResults.nodes('/JobDetails/RunningJobStats') AS csInfo(n)
	WHERE
		CommservSurveyResults_ID = @ResultId
	SELECT @ErrorCode = @@ERROR
END
IF 	@ErrorCode = 0
BEGIN
	UPDATE	cf_SurveySubclients
	SET		ClientName = B.ClientName, InstanceName = B.InstanceName, BackupsetName = B.BackupsetName,
			SubclientName = B.SubclientName, ClientGroups = B.ClientGroups
	FROM	cf_SurveySubclients A
			INNER JOIN #SurveySubclients B ON A.CommservUniqueId = B.CommservUniqueId AND A.AppId = B.AppId
	WHERE	A.ClientName <> B.ClientName OR A.InstanceName <> B.InstanceName OR A.BackupsetName <> B.BackupsetName
			OR A.SubclientName <> B.SubclientName OR A.ClientGroups <> B.ClientGroups
	SELECT @ErrorCode = @@ERROR
END
IF 	@ErrorCode = 0
BEGIN
	DELETE  #SurveySubclients
	FROM	cf_SurveySubclients S
	WHERE	#SurveySubclients.CommservUniqueId = S.CommservUniqueId AND #SurveySubclients.AppId = S.AppId
	SELECT @ErrorCode = @@ERROR
END
IF 	@ErrorCode = 0
BEGIN
	INSERT INTO cf_SurveySubclients (
			CommservUniqueId, AppId, AppTypeId, ClientName, AgentName, InstanceName, BackupsetName, SubclientName, ClientGroups)
	SELECT	CommservUniqueId, AppId, AppTypeId, ClientName, AgentName, InstanceName, BackupsetName, SubclientName, ClientGroups
	FROM	#SurveySubclients
	SELECT @ErrorCode = @@ERROR
END
IF 	@ErrorCode = 0
BEGIN
	DELETE  #SurveyJobStats
	FROM	cf_SurveyJobStats S
	WHERE	#SurveyJobStats.CommservUniqueId = S.CommservUniqueId AND #SurveyJobStats.JobId = S.JobId
	SELECT @ErrorCode = @@ERROR
END
IF 	@ErrorCode = 0
BEGIN
	INSERT INTO cf_SurveyJobStats (
			CommservUniqueId, JobId, AppId, OpType, BkpLevel, Status, StartDate, EndDate,
			ScanType, SizeChangePct, TransferTime, ProtectedObjects, FailedObjects, FailedFolders,
			DataProtected, DataCompressed, DataTransferred, DataWritten, ErrorCode, FailureReason,
			StoragePolicy, StoragePolicyID, LogStoragePolicyID, SchedulePolicy, ScheduleName, Attempts,InitFrom,Phases,failureErrorCode,DataStatus)
	SELECT	CommservUniqueId, JobId, AppId, OpType, BkpLevel, Status, StartDate, EndDate,
			ScanType, SizeChangePct, TransferTime, ProtectedObjects, FailedObjects, FailedFolders,
			DataProtected, DataCompressed, DataTransferred, DataWritten, ErrorCode, FailureReason,
			StoragePolicy, StoragePolicyID, LogStoragePolicyID, SchedulePolicy, ScheduleName, Attempts,InitFrom,Phases,failureErrorCode,0
	FROM	#SurveyJobStats
	SELECT @ErrorCode = @@ERROR
END
IF 	@ErrorCode = 0
BEGIN
	DELETE  #SurveyJobVMStats
	FROM	cf_SurveyJobVMStats S
	WHERE	#SurveyJobVMStats.CommservUniqueId = S.CommservUniqueId AND #SurveyJobVMStats.JobId = S.JobId AND #SurveyJobVMStats.vmClientId = S.vmClientId
	SELECT @ErrorCode = @@ERROR
END
IF 	@ErrorCode = 0
BEGIN
	INSERT INTO cf_SurveyJobVMStats (
			CommservUniqueId, JobId, vmClientId, vmBackupStart, vmBackupEnd, vmStatus,
			vmSize, vmUsedSpace, vmGuestSize, vmIsGuestSizeValid, vmFailureReason, ParentJobId)
	SELECT	CommservUniqueId, JobId, vmClientId, vmBackupStart, vmBackupEnd, vmStatus,
			vmSize, vmUsedSpace, vmGuestSize, vmIsGuestSizeValid, vmFailureReason, ParentJobId
	FROM	#SurveyJobVMStats
	SELECT @ErrorCode = @@ERROR
END
IF 	@ErrorCode = 0
BEGIN
	DELETE  #SurveyJobDbSize
	FROM	cf_SurveyJobDbSize S
	WHERE	#SurveyJobDbSize.CommservUniqueId = S.CommservUniqueId AND #SurveyJobDbSize.JobId = S.JobId
	SELECT @ErrorCode = @@ERROR
END
IF 	@ErrorCode = 0
BEGIN
	INSERT INTO cf_SurveyJobDbSize (
			CommservUniqueId, JobId, JobEndDate, DbName, DbSize)
	SELECT	CommservUniqueId, JobId, JobEndDate, DbName, DbSize
	FROM	#SurveyJobDbSize
	SELECT @ErrorCode = @@ERROR
END
IF 	@ErrorCode = 0
BEGIN
    DELETE CR
	FROM cf_SurveyRunningJobStats CR
	INNER JOIN #RunningJobStats R ON CR.CommservUniqueId = R.CommservUniqueId
	MERGE cf_SurveyRunningJobStats RS
	USING #RunningJobStats R
		ON RS.CommservUniqueId= R.CommservUniqueId AND R.JobId= RS.JobId AND R.AppId= RS.AppId AND R.JobId>0
	WHEN MATCHED THEN
		UPDATE SET RS.State = R.State
	WHEN NOT MATCHED  THEN
		INSERT (CommservUniqueId ,JobId ,AppId ,OpType ,BkpLevel ,StartDate ,State ,InitFrom,created, failureReason)
		VALUES (R.CommservUniqueId ,R.JobId ,R.AppId ,R.OpType ,R.BkpLevel ,R.StartDate ,R.State ,R.InitFrom,R.created, R.FailureReason);
	SELECT @ErrorCode = @@ERROR
END
--if private then only do
DECLARE @isactivityenabled		INT = 0
 SET @isactivityenabled =(SELECT CASE WHEN (csInfo.CommCellSurveyServices & 4) =4 THEN 1 ELSE 0 END  FROM cf_CommcellIdNameMap csinfo WITH (NOLOCK) WHERE @CSUniqueId =csinfo.ID  )
IF(@isPrivate <> 0 AND @isactivityenabled=1)
BEGIN
	IF 	@ErrorCode = 0
	BEGIN
		SET @LastDate = ISNULL((SELECT MAX(JobEndDate) FROM cf_SurveyIDADailyBkpJobStats WITH (NOLOCK) where CommServUniqueId = @CSUniqueId), '1970-01-01')
		SET	@LastTime = DATEDIFF(ss, '1970-01-01', @LastDate)
		SET @LastCompanyDate =  ISNULL((SELECT dbo.getUnixTimeBig(CAST(MAX(JobEndDate) AS DATE)) FROM cf_SurveyCompanyDailyBkpJobStats WITH (NOLOCK) where CommServUniqueId = @CSUniqueId),0)
		SELECT @ErrorCode = @@ERROR
	END
	IF (select [dbo].[isMSPViewEnabled]()) = 1
	BEGIN
		IF @ErrorCode = 0
		BEGIN
			IF object_id('tempdb.dbo.#CompanyClientGroup') IS NOT null DROP TABLE #CompanyClientGroup
				CREATE TABLE #CompanyClientGroup (CommServUniqueId INT, ClientGroupId INT)
			IF object_id('tempdb.dbo.#CompanySubclients') IS NOT null DROP TABLE #CompanySubclients
				CREATE TABLE #CompanySubclients (CommServUniqueId INT, ClientGroupId INT, AppId INT)
			INSERT INTO #CompanyClientGroup
			select CommServUniqueId, ClientGroupId from cf_SurveyTenants with (NOLOCK) where CommServUniqueId = @CSUniqueId
			INSERT INTO #CompanySubclients
			SELECT CG.CommServUniqueId, CG.ClientGroupId, AppID
			FROM #CompanyClientGroup CG
				INNER JOIN cf_SurveyClientGroupAssociation CGA WITH (NOLOCK) ON CG.CommServUniqueId = CGA.CommservUniqueId AND CG.ClientGroupId = CGA.ClientGroupId
				INNER JOIN cf_SurveySubclientAssociation S WITH (NOLOCK) ON S.CommservUniqueId = CG.CommServUniqueId AND S.ClientId = CGA.ClientId
			--If either we don't have any data in cf_SurveyCompanyDailyBkpJobStats for this Commcell or if we have a missing data for more than 5 days in cf_SurveyJobStats table in between. We might end up in a place where parser will always keep searching for those missing 5 days of data
			DECLARE @LastCompanyEpochTime BIGINT = dbo.getUnixTimeBig(DATEADD(DAY, 1, dbo.getDateTime(@LastCompanyDate)))
			DECLARE @LastCompanyEpochTimeWithFiveDays BIGINT = dbo.getUnixTimeBig(DATEADD(DAY, 6, dbo.getDateTime(@LastCompanyDate)))
			IF NOT EXISTS(SELECT top 1 jobid FROM cf_SurveyJobStats J WITH (NOLOCK)
										INNER JOIN #CompanySubclients S ON S.CommServUniqueId = J.CommservUniqueId AND S.AppId = J.AppId WHERE EndDate >= @LastCompanyEpochTime AND EndDate <= @LastCompanyEpochTimeWithFiveDays)
			BEGIN
				SET @LastCompanyDate = (SELECT MIN(EndDate) FROM cf_SurveyJobStats J WITH (NOLOCK)
																			INNER JOIN #CompanySubclients S ON S.CommServUniqueId = J.CommservUniqueId AND S.AppId = J.AppId WHERE J.EndDate >= @LastCompanyEpochTime)
			END
			SET @LastCompanyEpochTime = @LastCompanyDate
			SET @LastCompanyEpochTimeWithFiveDays = dbo.getUnixTimeBig(DATEADD(DAY, 6, dbo.getDateTime(@LastCompanyDate)))
			SET @LastCompanyDateTIME = dateadd(day, datediff(day, 0, dateadd(s, @LastCompanyDate, '1970-01-01')),0)
			SELECT @ErrorCode = @@ERROR
		END
		IF EXISTS(SELECT top 1 * FROM #CompanySubclients) AND @LastCompanyEpochTime is NOT NULL AND @LastCompanyEpochTimeWithFiveDays is NOT NULL
		BEGIN
			IF 	@ErrorCode = 0
			BEGIN
				INSERT INTO #tmpCompanyDailyJobs
				select  B.CommservUniqueId, CAST(dateadd(s, ENDDATE, '1970-01-01') AS DATE), S.ClientGroupId,
						SUM(CASE WHEN (B.status) IN (1,2,3,4,9,14,16) THEN 1 ELSE 0 END) as TJobs,
						SUM(CASE WHEN B.status = 1  THEN 1 ELSE 0 END) AS CJobs,
						SUM(CASE WHEN B.status = 3  THEN 1 ELSE 0 END) as CWEJobs,
						SUM(CASE WHEN B.status = 14 THEN 1 ELSE 0 END) as CWWJobs,
						SUM(CASE WHEN B.status = 2  THEN 1 ELSE 0 END) as FJobs,
						SUM(CASE WHEN B.status = 4 THEN 1 ELSE 0 END) as KJobs,
						SUM(CASE WHEN (B.Status = 9 AND B.failureErrorCode <> 8) THEN 1 ELSE 0 END) as SJobs,
						SUM(CASE WHEN B.status IN (1,3,14) THEN DataProtected ELSE 0 END) AS BackupSize,
						SUM(DataWritten) AS SizeOnMedia,
						SUM(CASE WHEN B.status = 16 THEN 1 ELSE 0 END) as COJobs,
						SUM(CASE WHEN B.Status = 9 AND B.failureErrorCode = 8 THEN 1 ELSE 0 END) AS FTSJobs
					FROM
						cf_SurveyJobStats B WITH (NOLOCK)
						INNER JOIN #CompanySubclients S ON B.CommservUniqueId = S.CommservUniqueId AND B.AppId = S.AppId
				WHERE   ENDDATE >= @LastCompanyEpochTime AND ENDDATE < @LastCompanyEpochTimeWithFiveDays
				GROUP BY B.CommservUniqueId, CAST(dateadd(s, ENDDATE, '1970-01-01') AS DATE), S.ClientGroupId
				IF object_id('tempdb.dbo.#CompanyClientGroup') IS NOT null DROP TABLE #CompanyClientGroup
				IF object_id('tempdb.dbo.#CompanySubclients') IS NOT null DROP TABLE #CompanySubclients
				SELECT @ErrorCode = @@ERROR
			END
		END
		IF 	@ErrorCode = 0
		BEGIN
			DELETE T
			FROM	#tmpCompanyDailyJobs T INNER JOIN cf_SurveyCompanyDailyBkpJobStats S
				ON T.CommServUniqueId = S.CommServUniqueId AND T.JobEndDate = S.JobEndDate AND T.ClientGroupId = S.ClientGroupId
			WHERE	T.JobEndDate = @LastCompanyDateTIME AND T.TJobs<=S.TotalJobs
			SELECT @ErrorCode = @@ERROR
		END
		IF 	@ErrorCode = 0
		BEGIN
			UPDATE	T
			SET		TJobs -= S.TotalJobs, CJobs -= S.CompletedJobs, CWEJobs -= S.CWEJobs, CWWJobs -= S.CWWJobs,
					FJobs -= S.FailedJobs, KJobs -= S.KilledJobs, SJobs -= S.SkippedJobs, COJobs -= S.CommittedJobs, BackupSize -= S.BackupSize, DataWritten -= S.DataWritten ,FTSJobs -= S.FailedToStartJobs
			FROM	#tmpCompanyDailyJobs T INNER JOIN cf_SurveyCompanyDailyBkpJobStats S
				ON T.CommServUniqueId = S.CommServUniqueId AND T.JobEndDate = S.JobEndDate AND T.ClientGroupId = S.ClientGroupId
			WHERE	T.JobEndDate = @LastCompanyDateTIME
			SELECT @ErrorCode = @@ERROR
		END
		IF 	@ErrorCode = 0
		BEGIN
			MERGE cf_SurveyCompanyDailyBkpJobStats AS T
				USING #tmpCompanyDailyJobs S
					ON T.CommServUniqueId = S.CommServUniqueId AND T.JobEndDate = S.JobEndDate AND T.ClientGroupId = S.ClientGroupId and S.JobEndDate>= @LastCompanyDateTIME
				WHEN MATCHED THEN
					UPDATE SET TotalJobs += S.TJobs, CompletedJobs += S.CJobs, CWEJobs += S.CWEJobs, CWWJobs += S.CWWJobs,
						FailedJobs += S.FJobs, KilledJobs += S.KJobs, SkippedJobs += S.SJobs, CommittedJobs += S.COJobs, BackupSize += S.BackupSize, DataWritten += S.DataWritten,
						FailedToStartJobs +=S.FTSJobs
				WHEN NOT MATCHED AND (S.ClientGroupId = 0 OR S.TJobs > 0) THEN
					INSERT (CommServUniqueId, JobEndDate, ClientGroupId, TotalJobs, CompletedJobs, CWEJobs, CWWJobs, FailedJobs, KilledJobs, SkippedJobs, BackupSize, DataWritten, CommittedJobs,FailedToStartJobs)
					VALUES (S.CommServUniqueId, S.JobEndDate, S.ClientGroupId, S.TJobs, S.CJobs, S.CWEJobs, S.CWWJobs, S.FJobs, S.KJobs, S.SJobs, S.BackupSize, S.DataWritten, S.COJobs,S.FTSJobs);
			SELECT @ErrorCode = @@ERROR
		END
		IF 	@ErrorCode = 0
		BEGIN
			INSERT INTO #tmpCompanyAggregateJobs
			SELECT  CommServUniqueId, DATEADD(MONTH, DATEDIFF(MONTH, 0, JobEndDate), 0), ClientGroupId,
					SUM(TJobs), SUM(CJobs), SUM(CWEJobs), SUM(CWWJobs), SUM(FJobs), SUM(KJobs), SUM(SJobs), SUM(BackupSize), SUM(DataWritten), SUM(COJobs),SUM(FTSJobs)
			FROM    #tmpCompanyDailyJobs
			GROUP BY CommServUniqueId, DATEADD(MONTH, DATEDIFF(MONTH, 0, JobEndDate), 0), ClientGroupId
			SELECT @ErrorCode = @@ERROR
		END
		IF 	@ErrorCode = 0
		BEGIN
			MERGE cf_SurveyCompanyMonthlyBkpJobStats AS T
			USING #tmpCompanyAggregateJobs S
				ON T.CommServUniqueId = S.CommServUniqueId AND T.MonthStart = S.MonthStart AND T.ClientGroupId = S.ClientGroupId
			WHEN MATCHED THEN
				UPDATE SET TotalJobs += S.TotalJobs, CompletedJobs += S.CompletedJobs, CWEJobs += S.CWEJobs, CWWJobs += S.CWWJobs,
					FailedJobs += S.FailedJobs, KilledJobs += S.KilledJobs, SkippedJobs += S.SkippedJobs, BackupSize += S.BackupSize, DataWritten += S.DataWritten, CommittedJobs += S.COJobs,
					FailedToStartJobs +=S.FTSJobs
			WHEN NOT MATCHED AND (S.ClientGroupId = 0 OR S.TotalJobs > 0) THEN
				INSERT (CommServUniqueId, MonthStart, ClientGroupId, TotalJobs, CompletedJobs, CWEJobs, CWWJobs, FailedJobs, KilledJobs, SkippedJobs, BackupSize, DataWritten, CommittedJobs,FailedToStartJobs)
				VALUES (S.CommServUniqueId, S.MonthStart, S.ClientGroupId, S.TotalJobs, S.CompletedJobs, S.CWEJobs, S.CWWJobs, S.FailedJobs, S.KilledJobs, S.SkippedJobs, S.BackupSize, S.DataWritten, S.COJobs,S.FTSJobs);
			SELECT @ErrorCode = @@ERROR
		END
	END
	IF 	@ErrorCode = 0
	BEGIN
		INSERT INTO #tmpDailyJobs
		select  B.CommservUniqueId, CAST(dateadd(s, ENDDATE, '1970-01-01') AS DATE), S.AppTypeId,
				SUM(CASE WHEN (B.status) IN (1,2,3,4,9,14,16) THEN 1 ELSE 0 END) as TJobs,
				SUM(CASE WHEN B.status = 1  THEN 1 ELSE 0 END) AS CJobs,
				SUM(CASE WHEN B.status = 3  THEN 1 ELSE 0 END) as CWEJobs,
				SUM(CASE WHEN B.status = 14 THEN 1 ELSE 0 END) as CWWJobs,
				SUM(CASE WHEN B.status = 2  THEN 1 ELSE 0 END) as FJobs,
				SUM(CASE WHEN B.status = 4 THEN 1 ELSE 0 END) as KJobs,
				SUM(CASE WHEN (B.Status = 9 AND B.failureErrorCode <> 8) THEN 1 ELSE 0 END) as SJobs,
				SUM(CASE WHEN B.status IN (1,3,14) THEN DataProtected ELSE 0 END) AS BackupSize,
				SUM(DataWritten) AS SizeOnMedia,
				SUM(CASE WHEN B.status = 16 THEN 1 ELSE 0 END) as COJobs,
				SUM(CASE WHEN B.Status = 9 AND B.failureErrorCode = 8 THEN 1 ELSE 0 END) AS FTSJobs
			FROM
				 cf_SurveyJobStats B WITH (NOLOCK)
				INNER JOIN cf_SurveySubclients S WITH (NOLOCK) ON B.CommservUniqueId = S.CommservUniqueId AND B.AppId = S.AppId and @CSUniqueId = B.CommservUniqueId
		WHERE   ENDDATE >= @LastTime
		GROUP BY B.CommservUniqueId, CAST(dateadd(s, ENDDATE, '1970-01-01') AS DATE), S.AppTypeId
		SELECT @ErrorCode = @@ERROR
	END
	IF 	@ErrorCode = 0
	BEGIN
		DELETE T
		FROM	#tmpDailyJobs T INNER JOIN cf_SurveyIDADailyBkpJobStats S
			ON T.CommServUniqueId = S.CommServUniqueId AND T.JobEndDate = S.JobEndDate AND T.AppTypeID = S.AppTypeID
		WHERE	T.JobEndDate = @LastDate AND T.TJobs<=S.TotalJobs
		SELECT @ErrorCode = @@ERROR
	END
	IF 	@ErrorCode = 0
	BEGIN
		UPDATE	T
		SET		TJobs -= S.TotalJobs, CJobs -= S.CompletedJobs, CWEJobs -= S.CWEJobs, CWWJobs -= S.CWWJobs,
				FJobs -= S.FailedJobs, KJobs -= S.KilledJobs, SJobs -= S.SkippedJobs, COJobs -= S.CommittedJobs, BackupSize -= S.BackupSize, DataWritten -= S.DataWritten ,FTSJobs -= S.FailedToStartJobs
		FROM	#tmpDailyJobs T INNER JOIN cf_SurveyIDADailyBkpJobStats S
			ON T.CommServUniqueId = S.CommServUniqueId AND T.JobEndDate = S.JobEndDate AND T.AppTypeID = S.AppTypeID
		WHERE	T.JobEndDate = @LastDate
		SELECT @ErrorCode = @@ERROR
	END
	IF 	@ErrorCode = 0
	BEGIN
		MERGE cf_SurveyIDADailyBkpJobStats AS T
			USING #tmpDailyJobs S
				ON T.CommServUniqueId = S.CommServUniqueId AND T.JobEndDate = S.JobEndDate AND T.AppTypeID = S.AppTypeID and S.JobEndDate>= @LastDate
			WHEN MATCHED THEN
				UPDATE SET TotalJobs += S.TJobs, CompletedJobs += S.CJobs, CWEJobs += S.CWEJobs, CWWJobs += S.CWWJobs,
					FailedJobs += S.FJobs, KilledJobs += S.KJobs, SkippedJobs += S.SJobs, CommittedJobs += S.COJobs, BackupSize += S.BackupSize, DataWritten += S.DataWritten,
					FailedToStartJobs +=S.FTSJobs
			WHEN NOT MATCHED AND (S.AppTypeID = 0 OR S.TJobs > 0) THEN
				INSERT (CommServUniqueId, JobEndDate, AppTypeId, TotalJobs, CompletedJobs, CWEJobs, CWWJobs, FailedJobs, KilledJobs, SkippedJobs, BackupSize, DataWritten, CommittedJobs,FailedToStartJobs)
				VALUES (S.CommServUniqueId, S.JobEndDate, S.AppTypeId, S.TJobs, S.CJobs, S.CWEJobs, S.CWWJobs, S.FJobs, S.KJobs, S.SJobs, S.BackupSize, S.DataWritten, S.COJobs,S.FTSJobs);
		SELECT @ErrorCode = @@ERROR
	END
	IF 	@ErrorCode = 0
	BEGIN
		INSERT INTO #tmpAggregateJobs
		SELECT  CommServUniqueId, DATEADD(MONTH, DATEDIFF(MONTH, 0, JobEndDate), 0), AppTypeId,
				SUM(TJobs), SUM(CJobs), SUM(CWEJobs), SUM(CWWJobs), SUM(FJobs), SUM(KJobs), SUM(SJobs), SUM(BackupSize), SUM(DataWritten), SUM(COJobs),SUM(FTSJobs)
		FROM    #tmpDailyJobs
		GROUP BY CommServUniqueId, DATEADD(MONTH, DATEDIFF(MONTH, 0, JobEndDate), 0), AppTypeId
		SELECT @ErrorCode = @@ERROR
	END
	IF 	@ErrorCode = 0
	BEGIN
		MERGE cf_SurveyIDAMonthlyBkpJobStats AS T
		USING #tmpAggregateJobs S
			ON T.CommServUniqueId = S.CommServUniqueId AND T.MonthStart = S.MonthStart AND T.AppTypeID = S.AppTypeID
		WHEN MATCHED THEN
			UPDATE SET TotalJobs += S.TotalJobs, CompletedJobs += S.CompletedJobs, CWEJobs += S.CWEJobs, CWWJobs += S.CWWJobs,
				FailedJobs += S.FailedJobs, KilledJobs += S.KilledJobs, SkippedJobs += S.SkippedJobs, BackupSize += S.BackupSize, DataWritten += S.DataWritten, CommittedJobs += S.COJobs,
				FailedToStartJobs +=S.FTSJobs
		WHEN NOT MATCHED AND (S.AppTypeID = 0 OR S.TotalJobs > 0) THEN
			INSERT (CommServUniqueId, MonthStart, AppTypeId, TotalJobs, CompletedJobs, CWEJobs, CWWJobs, FailedJobs, KilledJobs, SkippedJobs, BackupSize, DataWritten, CommittedJobs,FailedToStartJobs)
			VALUES (S.CommServUniqueId, S.MonthStart, S.AppTypeId, S.TotalJobs, S.CompletedJobs, S.CWEJobs, S.CWWJobs, S.FailedJobs, S.KilledJobs, S.SkippedJobs, S.BackupSize, S.DataWritten, S.COJobs,S.FTSJobs);
		SELECT @ErrorCode = @@ERROR
	END
END
--  QUERY SPECIFIC CODE END --
IF @ErrorCode = 0 
  	BEGIN 
  		DECLARE	@ResultFlagsTbl TABLE (ResultId BIGINT, Flags INT) 
  		DECLARE	@LatestParsedResultId BIGINT = 0 
  		SELECT	@LatestParsedResultId = CommservSurveyResults_ID 
  		FROM	cf_CommservSurveyResults 
  		WHERE	QueryId = @QueryId AND CommcellIdNameMap_ID = @CSUniqueId AND Flags = 1 
  		SELECT @ErrorCode = @@ERROR 
  		IF @ErrorCode = 0 
  		BEGIN 
  			IF @LatestParsedResultId = 0 
  				INSERT INTO @ResultFlagsTbl SELECT @ResultId, 1 
  			ELSE IF @LatestParsedResultId < @ResultId 
  				INSERT INTO @ResultFlagsTbl SELECT @ResultId, 1 UNION SELECT @LatestParsedResultId, 0 
  			ELSE 
  				INSERT INTO @ResultFlagsTbl SELECT @ResultId, 0 
  			SELECT @ErrorCode = @@ERROR 
  		END 
  		IF @ErrorCode = 0 
  		BEGIN 
  			UPDATE	R SET ShredStatus = 2, Flags = F.Flags, CollectedResults = NULL 
  			FROM	cf_CommservSurveyResults R 
  					INNER JOIN @ResultFlagsTbl F ON R.CommservSurveyResults_ID = F.ResultId 
  			SELECT @ErrorCode = @@ERROR 
  		END 
  	END 
  	IF @ErrorCode = 0 AND XACT_STATE() = 1 
  	BEGIN 
  		COMMIT TRANSACTION ParseJobDetailsTran 
  		SELECT @ErrorCode = @@ERROR 
  	END 
  	SET	@Duration = DATEDIFF(SS, @StartDate, GETUTCDATE()) 
  	IF @ErrorCode = 0 AND @LogLevel > 1 
  		SET @LogString += ' - Completed in ' + CAST(@Duration AS VARCHAR(10)) + ' (sec).' 
  	ELSE IF @ErrorCode <> 0 AND @SkipOnErr = 0 
  		SET @LogString += ' - Failed to update status after ' + CAST(@Duration AS VARCHAR(10)) + ' (sec). ERROR CODE: ' + CAST(@ErrorCode AS VARCHAR(20)) 
  	ELSE IF @ErrorCode <> 0 
  		SET @LogString += ' - Failed in ' + CAST(@Duration AS VARCHAR(10)) + ' (sec). ERROR CODE: ' + CAST(@ErrorCode AS VARCHAR(20)) 
  END TRY 
  BEGIN CATCH 
  	SELECT @ErrorCode = ERROR_NUMBER(), @ErrorMsg = ERROR_MESSAGE() 
  	SET	@Duration = DATEDIFF(SS, @StartDate, GETUTCDATE()) 
  	SET @LogString += ' - Exception in ' + CAST(@Duration AS VARCHAR(10)) + ' (sec). ERROR CODE: ' + CAST(@ErrorCode AS VARCHAR(20))+ ' ERROR MESSAGE: ' + ISNULL(@ErrorMsg, '') 
  END CATCH 
  IF @@TRANCOUNT > 0 
  BEGIN 
      SET @LogString += ' - Rollback uncommitted transaction after ' + CAST(@Duration AS VARCHAR(10)) + ' (sec).' 
      SET @TempDate = GETUTCDATE() 
      ROLLBACK TRANSACTION 
      SET	@Duration = DATEDIFF(SS, @TempDate, GETUTCDATE()) 
      SET @LogString += ' - Rollback completed in ' + CAST(@Duration AS VARCHAR(10)) + ' (sec).' 
  END 
  IF @ErrorCode <> 0 AND @SkipOnErr > 0 
      UPDATE cf_CommservSurveyResults SET ShredStatus = 3 WHERE CommservSurveyResults_ID = @ResultId 
  IF @LogString <> '' 
  BEGIN 
      SET @LogString = ('Parsing result ' + CAST(@ResultId AS VARCHAR(20)) + ' (QueryId: ' + CAST(@QueryId AS VARCHAR(20)) + ', CSUniqueId: ' + CAST(@CSUniqueId AS VARCHAR(20)) + ')' + @LogString) 
      INSERT INTO cf_SurveyLogger(Message) VALUES(@LogString) 
  END 
  SET NOCOUNT OFF
GO

IF EXISTS (select * from GxQscripts where name = 'ParseJobDetails')
	delete from GxQscripts where name = 'ParseJobDetails'
GO

IF EXISTS (select * from GXDBVersions where aliasname='ParseJobDetails')
	delete from GXDBVersions where aliasname = 'ParseJobDetails'
GO

insert into GXDBVersions values(2, 'ParseJobDetails',  'v1.11.2.26.4.1', 'ParseJobDetails', 'v1.11.2.26.4.1')
GO

