

--  ------------  Generated from [/big2/11.0/Build80_SP24/cxunix/source/CommServer/Db/Sp/archGetValidAfilesForBkpJob.sp] ---------- 

-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/archGetValidAfilesForBkpJob.sp,v $ $Id: archGetValidAfilesForBkpJob.sp,v 1.11.2.25.8.2 2021/06/21 21:41:20 srajani Exp $";
--  +-----------------------------------------------------------------------+
--  |           Procedure : "archGetValidAfilesForBkpJob"
--  |   This Procedure is used to handle the different request for VM Allocation Policy
--  +-----------------------------------------------------------------------+
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='archGetValidAfilesForBkpJob')
	delete from GXDBVersions where aliasname = 'archGetValidAfilesForBkpJob'
GO
print '... Creating Procedure: archGetValidAfilesForBkpJob'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure archGetValidAfilesForBkpJob
-- Input arguments
  @i_bkpJobId integer,
  @i_commCellId integer,
  @i_snapBkp integer,
  @i_snapToTapeBkp integer,
  @i_opType integer
AS
  DECLARE @outResp XML
BEGIN
IF object_id('tempdb.dbo.#tempValidAfs') IS NOT NULL DROP TABLE #tempValidAfs
CREATE TABLE #tempValidAfs ( afileId int, volId int, clientId int)
DECLARE @validAfilesResp XML
DECLARE @indexCTime INT = 0
DECLARE @fileType INTEGER
-- auto scaling index job is snap backup
SET @i_snapBkp = CASE WHEN @i_opType = 150 THEN 1 ELSE @i_snapBkp END
SET @fileType = CASE WHEN @i_snapBkp = 1 THEN 6 ELSE 2 END
IF @i_opType = 150 /* AUTO_SCALING_INDEX_BACKUP */
BEGIN
	-- special handling for auto scaling index job
	SELECT @indexCTime = MAX(ctime) FROM archFile AF WITH (NOLOCK)
	WHERE AF.jobId = @i_bkpJobId and AF.commCellId = @i_commCellId and AF.isValid =1
	and AF.fileType IN(@fileType)
	-- insert ma
	INSERT  INTO #tempValidAfs
	SELECT DISTINCT NULL, NULL, J.attributeValueInt
	FROM JMJobOptions J(NOLOCK)
	WHERE J.jobId = @i_bkpJobId AND J.commcellId = @i_commCellId
AND J.attributeId = 95
	-- insert archive files
	INSERT INTO #tempValidAfs
	SELECT DISTINCT AF.id, NULL, NULL FROM archFile AF WITH (NOLOCK) WHERE AF.jobId = @i_bkpJobId AND AF.commCellId = @i_commCellId AND AF.isValid = 1
	AND NOT EXISTS (SELECT 1 FROM #tempValidAfs TAF WHERE AF.id = TAF.afileId)
END
ELSE
BEGIN
DECLARE @appTypeId INT = 0
SET @appTypeId = ISNULL((SELECT App.appTypeId FROM JMBkpJobInfo(NOLOCK) Bkp, App_Application(NOLOCK) App WHERE Bkp.jobId = @i_bkpJobId AND Bkp.commcellId = @i_commCellId AND App.id = Bkp.applicationId), 0)
-- If index is already backed up, save the index afile ctime.
-- For catalog snapshot job, pick job id from JMBkpJobInfo as job id passed as input is original snap job id. Index archfile is created with catalog snapshot job id.
DECLARE @currentJobID INT = 0
--This option is set for VSA and FS block level offline cataloging
DECLARE @catalogJobId INT = 0
select @catalogJobId = jo.attributeValueInt
	from JmJobOptions jo WITH (NOLOCK)
	JOIN JMAdminJobInfoTable aji WITH(NOLOCK) on aji.jobId = jo.jobId and aji.jobId = @i_bkpJobId
	where
	jo.jobId = @i_bkpJobId  AND jo.commCellId = @i_commCellId and jo.attributeValueInt>0 and jo.attributeId=92 --JMCATALOG_JOB_ID_FOR_BKP_JOB
-- if it is a offline catalog job for FS Block level, add MAs where catalog has been done
IF (@catalogJobId <> 0 AND (@appTypeId <> 106))
BEGIN
	DECLARE @tblCatalogMAs TABLE (MAHostId INT, Flag INT)
	INSERT  INTO @tblCatalogMAs
	SELECT  DISTINCT SNAPIMA.SnapIndexMAClientId, SNAPIMA.Flags
	FROM    SMSnapIndexMA SNAPIMA(NOLOCK)
	WHERE   SNAPIMA.JobID = @catalogJobID AND SNAPIMA.CommCellId = @i_commCellId
	INSERT  INTO #tempValidAfs
	SELECT  DISTINCT NULL, NULL, MAHostId FROM @tblCatalogMAs
END
SET @currentJobID =
CASE WHEN @i_snapBkp = 1
THEN ISNULL ((select T1.jobID from JMBkpJobInfo(NOLOCK) T1
			INNER JOIN JMJobInfo(NOLOCK) T2 ON T1.jobId = T2.jobId AND T1.commcellId = T2.commCellId
			where T1.jobIdActedOn = @i_bkpJobId and T1.commcellId = @i_commCellId AND T2.subOpType = 96 ),@i_bkpJobId) ELSE @i_bkpJobId END
SELECT @indexCTime = ctime FROM archFile AF WITH (NOLOCK)
	INNER JOIN ARCHFILECOPY AFC WITH (NOLOCK) ON AF.ID = AFC.archFileID AND AF.commCellId = AFC.commCellId
	WHERE AF.fileType IN(@fileType) AND AF.jobId = @currentJobID and AF.commCellId = @i_commCellId and AFC.isValid=1 AND AF.isValid =1 AND AFC.lastChunkNumber > 0
DECLARE @appId INT = 0
SELECT @appId = applicationId FROM JMBkpJobInfo WITH (NOLOCK) WHERE jobId = @i_bkpJobId and commcellId = @i_commCellId
IF @appId = 0
BEGIN
		SELECT @appId = appId FROM JMBkpStats WITH (NOLOCK) WHERE jobId = @i_bkpJobId and commCellId = @i_commCellId
END
IF @i_snapBkp = 1
BEGIN
    INSERT INTO #tempValidAfs
SELECT AF.id, NULL, NULL FROM archFile AF WITH (NOLOCK) WHERE AF.jobId = @i_bkpJobId AND AF.commCellId = @i_commCellId AND AF.isValid = 1 AND (AF.Flags & 2) = 2
AND (AF.Flags & 65536) = 0
    -- Add the MAs where catalog has been done
	DECLARE @tblSnapCatalogMAs TABLE (MAHostId INT, Flag INT)
	INSERT  INTO @tblSnapCatalogMAs
	SELECT  DISTINCT SNAPIMA.SnapIndexMAClientId, SNAPIMA.Flags
	FROM    SMSnapIndexMA SNAPIMA(NOLOCK)
	WHERE   SNAPIMA.JobId = @i_bkpJobId AND SNAPIMA.CommCellId = @i_commCellId
	INSERT  INTO #tempValidAfs
	SELECT  DISTINCT NULL, NULL, MAHostId FROM @tblSnapCatalogMAs
	UNION
	SELECT DISTINCT NULL, NULL, J.attributeValueInt
	FROM JMJobOptions J(NOLOCK)
	WHERE J.jobId = @currentJobID AND J.commcellId = @i_commCellId
	AND J.attributeId = 93 -- JM_DELAYED_CATALOG_TARGET_MA for MR:274355
END
ELSE
BEGIN
    DECLARE @lastHardIndexingFailureTime integer = 0
    SELECT  @lastHardIndexingFailureTime = intData -- timestamp
    FROM JMMisc WITH (NOLOCK)
    WHERE jobId = @i_bkpJobId AND commcellId = @i_commCellId
    AND itemType = 36 --JM_LAST_HARD_INDEXING_FAILURE
	DECLARE @idxAppType INT = 0
	DECLARE @errorCode INT = 0
	EXEC AppGetIndexingAppType @appTypeId, @appId, @idxAppType OUT, @errorCode OUT
IF @appTypeId IN (137) -- For Exchange OnePass we are not invalidating the ArchFiles on hard indexing failures and hence filtering based on the ArchFile creation time(Also note that Snapbackups are not supported for this agent)
	BEGIN
        INSERT INTO #tempValidAfs
                    SELECT DISTINCT AF.id, AC.volumeId, NULL/*MediaAgentId*/ FROM archFile AF WITH (NOLOCK) INNER JOIN archFileCopy AFC WITH (NOLOCK) ON AF.id = AFC.archFileId AND AF.commCellId = AFC.commCellId AND AF.cTime > @lastHardIndexingFailureTime
                    INNER JOIN archChunkMapping ACM WITH (NOLOCK) ON AFC.archFileID = ACM.archFileId AND AFC.commCellId = ACM.commCellId AND AFC.archCopyId = ACm.archCopyId AND ACM.chunkNumber > 0
                    INNER JOIN archChunk AC WITH (NOLOCK) ON ACM.archChunkId = AC.id AND ACM.chunkCommCellId = AC.commCellId
--                    INNER JOIN JMJobResourceHistory JMR WITH (NOLOCK) ON JMR.VolumeId = AC.volumeId AND JMR.jobId = AF.jobId AND JMR.commCellId = AF.commCellId AND JMR.ReservationTime > @lastHardIndexingFailureTime
--                    INNER JOIN App_Application AP WITH (NOLOCK) ON AF.appId = AP.id
                    WHERE AF.jobId = @i_bkpJobId  AND AF.commCellId = @i_commCellId AND AF.isValid = 1 AND AFC.lastChunkNumber > 0 AND AFC.isValid = 1
AND AF.Flags & 65536 = 0
	END
	ELSE
	BEGIN
		IF @catalogJobId>0
		BEGIN
	        INSERT INTO #tempValidAfs
                    SELECT DISTINCT AF.id, AC.volumeId, DBI.currentIdxServer
					FROM archFile AF WITH (NOLOCK)
					INNER JOIN archFileCopy AFC WITH (NOLOCK) ON AF.id = AFC.archFileId AND AF.commCellId = AFC.commCellId
                    INNER JOIN archChunkMapping ACM WITH (NOLOCK) ON AFC.archFileID = ACM.archFileId AND AFC.commCellId = ACM.commCellId AND AFC.archCopyId = ACm.archCopyId AND ACM.chunkNumber > 0
                    INNER JOIN archChunk AC WITH (NOLOCK) ON ACM.archChunkId = AC.id AND ACM.chunkCommCellId = AC.commCellId
                    INNER JOIN App_Application AP WITH (NOLOCK) ON AF.appId = AP.id
					INNER JOIN App_IndexDBInfo DBI WITH(NOLOCK) ON AP.backupSet=DBI.backupSetId AND DBI.idxDbEngineType=1 and DBI.isPrimary=1
                    WHERE AF.jobId = @i_bkpJobId  AND AF.commCellId = @i_commCellId AND AF.isValid = 1 AND AFC.lastChunkNumber > 0 AND AFC.isValid = 1
AND (((AF.Flags & 65536) = 0)OR (AP.appTypeId in (22,80) AND AF.fileType=1 ) OR (AP.appTypeId = 125 AND AF.fileType=4 ))
		END
		ELSE
		BEGIN
	        INSERT INTO #tempValidAfs
                    SELECT DISTINCT AF.id, AC.volumeId,JMR.ClientId FROM archFile AF WITH (NOLOCK) INNER JOIN archFileCopy AFC WITH (NOLOCK) ON AF.id = AFC.archFileId AND AF.commCellId = AFC.commCellId
                    INNER JOIN archChunkMapping ACM WITH (NOLOCK) ON AFC.archFileID = ACM.archFileId AND AFC.commCellId = ACM.commCellId AND AFC.archCopyId = ACm.archCopyId AND ACM.chunkNumber > 0
                    INNER JOIN archChunk AC WITH (NOLOCK) ON ACM.archChunkId = AC.id AND ACM.chunkCommCellId = AC.commCellId
                    INNER JOIN JMJobResourceHistory JMR WITH (NOLOCK) ON JMR.VolumeId = AC.volumeId AND JMR.jobId = AF.jobId AND JMR.commCellId = AF.commCellId AND JMR.ReservationTime > @lastHardIndexingFailureTime
                    INNER JOIN App_Application AP WITH (NOLOCK) ON AF.appId = AP.id
                    WHERE AF.jobId = @i_bkpJobId  AND AF.commCellId = @i_commCellId AND AF.isValid = 1 AND AFC.lastChunkNumber > 0 AND AFC.isValid = 1
AND (((AF.Flags & 65536) = 0)OR (AP.appTypeId in (22,80) AND AF.fileType=1 ) OR (AP.appTypeId = 125 AND AF.fileType=4 ))
IF @idxAppType = 200108 AND NOT EXISTS(SELECT * FROM #tempValidAfs)
			BEGIN
				--For VMs, instead of checking reservation tables, use the following information stored in CSDB
				--to find the MAs involved in the backup -> archFile --> archFileCopy (drive pool id) --> MMDrivePool (client id)
				INSERT INTO #tempValidAfs
				SELECT DISTINCT AF.id, AC.volumeId, MMDP.ClientId
				FROM archFile AF WITH (NOLOCK) JOIN archFileCopy AFC WITH (NOLOCK)
				ON AF.jobId = @i_bkpJobId  AND AF.commCellId = @i_commCellId AND AF.id = AFC.archFileId AND AF.commCellId = AFC.commCellId AND AF.isValid = 1
AND ((AF.Flags & 65536) = 0) AND AF.cTime > @lastHardIndexingFailureTime
				AND AFC.lastChunkNumber > 0 AND AFC.isValid = 1
				JOIN archChunkMapping ACM WITH (NOLOCK) ON ACM.archFileId = AF.id AND ACM.commCellId = AF.commCellId AND ACM.chunkNumber > 0 AND AFC.archCopyId = ACM.archCopyId
				JOIN archChunk AC WITH (NOLOCK) ON ACM.archChunkId = AC.id and ACM.commCellId = AC.commCellId
				JOIN MMDrivePool MMDP WITH (NOLOCK) ON AFC.drivePoolId = MMDP.DrivePoolId
			END
		END
	END
        IF @i_snapToTapeBkp  = 1
            INSERT INTO #tempValidAfs
                        SELECT DISTINCT AF.id, NULL, NULL FROM archFile AF WITH (NOLOCK) INNER JOIN archFileCopy AFC WITH (NOLOCK) ON AF.id = AFC.archFileId AND AF.commCellId = AFC.commCellId
                        INNER JOIN archGroupCopy agc WITH (NOLOCK) ON AFC.archCopyId = AGC.id AND AGC.isSnapCopy = 0
WHERE AF.jobId = @i_bkpJobId  AND AF.commCellId = @i_commCellId AND AF.isValid = 1 AND AFC.isValid = 1 AND (AF.Flags & 2) = 2
AND (AF.Flags & 65536) = 0
IF @appTypeId NOT IN (137, 78, 139) AND @idxAppType NOT IN (200108, 200118, 200127, 200128) --we do not create pseudo afiles for VSA apptype or any O365 app type
	BEGIN
			-- pick afiles with out any chunk as they could be created just for sending Pseudo Items, skip snap afiles
			INSERT INTO #tempValidAfs
            SELECT DISTINCT AF.id, NULL, NULL FROM archFile AF WITH (NOLOCK) WHERE AF.jobId = @i_bkpJobId AND AF.commCellId = @i_commCellId AND AF.isValid = 1
AND (AF.Flags & 65536) = 0 AND (AF.Flags & 2) = 0 AND NOT EXISTS (SELECT 1 FROM #tempValidAfs TAF WHERE AF.id = TAF.afileId)
	END
	-- pick MAs used just for sending pseudo items. Select MAs only reserved since last hard Indexing Failure as the job restarts from begining and all afiles before that
	-- becomes invalid.
	INSERT INTO #tempValidAfs
    SELECT  NULL, NULL, JMR.ClientId FROM   JMJobResourceHistory JMR WITH (NOLOCK)
    WHERE  JMR.JobID = @i_bkpJobId AND JMR.commCellId = @i_commCellId
    AND ReservationTime > @lastHardIndexingFailureTime
	AND NOT EXISTS (SELECT 1 FROM #tempValidAfs TAF WHERE JMR.ClientId = TAF.clientId)
END
END
END
DECLARE @previousJobId INT = 0
DECLARE @previousJobCommCellId INT = 0
declare @previousJobCataloged INT = 0
select TOP 1 @previousJobId=js.jobId, @previousJobCommCellId=js.commCellId from JMBkpStats js WITH(NOLOCK) where
	js.appid=@appId and js.jobId<@currentJobID and js.status in (1,3,14)
	order by js.servStartDate DESC
-- CVA_DATATYPE_FILE_INDEX == 9
if exists(select 1 from archFile af1 WITH(NOLOCK) where af1.jobId=@previousJobId and af1.commcellid=@previousJobCommCellId and af1.fileType=9 and af1.isValid=1)
BEGIN
	SET @previousJobCataloged=1
END
SET @outResp  = (
	 (
	 SELECT @indexCTime as '@indexAfileCtime', @previousJobCataloged as '@previousJobCataloged',
	 (
		SELECT  name as '@clientName' , net_hostname as '@hostName', cvdPort as '@cvdPort', evmgrcPort as '@evmgrcPort',
(CASE WHEN (MM.MmHostSoftState < 1 OR MmHostEnabled < 1 OR ((MM.Attribute & 16) = 16)) THEN 1 ELSE 0 END) as '@offline'
        from APP_Client CL(NOLOCK) INNER JOIN MMHost MM(NOLOCK) ON CL.id = MM.ClientID    WHERE CL.id IN
		(SELECT DISTINCT clientId FROM #tempValidAfs WHERE clientId IS NOT NULL)
		FOR XML PATH('reservedMas'),TYPE
	  ),
	  (
		SELECT DISTINCT afileId as '@val' FROM #tempValidAfs  WHERE afileId IS NOT NULL
		FOR XML PATH('validAfiles'),TYPE
	  )
FOR XML PATH('JobManager_ValidAFsAndMasForJob'), TYPE))
SELECT @outResp
IF object_id('tempdb.dbo.#tempValidAfs') IS NOT NULL DROP TABLE #tempValidAfs
GO

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

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

insert into GXDBVersions values(2, 'archGetValidAfilesForBkpJob',  'v1.11.2.25.8.2', 'archGetValidAfilesForBkpJob', 'v1.11.2.25.8.2')
GO

