

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

--:HIDE ON
-- ----------------------------------------------------------------------
--
--           Copyright (c) 1998  CommVault Systems, Inc.
--                  All rights reserved.
--
--
--        This is unpublished proprietary source code of CommVault
--        Systems, Inc.  The copyright notice above does not evidence
--        any actual or intended publication of such source code.
-- ----------------------------------------------------------------------*/
-- dataServer_h_rcsid[]="@(#)$Source: /cvs/cvsrepro/GX/vaultcx/Source/CommServer/Db/Sp/archFileCopyPreferred.sp,v $ $Id: archFileCopyPreferred.sp,v 1.15.200.5.20.1 2021/06/10 03:47:05 prasanthm Exp $";
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON


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

IF EXISTS (select * from GXDBVersions where aliasname='archFileCopyPreferred')
	delete from GXDBVersions where aliasname = 'archFileCopyPreferred'
GO
print '... Creating Procedure: archFileCopyPreferred'
GO
SET QUOTED_IDENTIFIER ON
SET NOCOUNT ON
GO
create procedure archFileCopyPreferred
  @i_archFileId integer,
  @i_commCellId integer,
  @i_includeAgedData integer,
  @i_preferMagnetic integer,
  @i_preferAccessible integer,
  @o_archCopyId integer OUTPUT,
  @o_AFC_isValid integer OUTPUT,
  @o_AFC_flags integer OUTPUT
AS
--This will turn off message: "xxx rows affected".
SET NOCOUNT ON
DECLARE @tblAFCVolume TABLE (
	archCopyId INT, copyPrec INT, isValid INT, flags INT, volumeId INT, isMagnetic INT)
DECLARE @tblMA_DP TABLE (
	mmErrorCode			integer,
mediaAgentName		varchar(1024),
	clientId			integer,
	nasClientId			integer,
	clientReleaseId     integer,
filerName			varchar(1024),
	hostId				integer,
	drivepoolId			integer,
	drivepoolType		integer,
	libraryId			integer,
	libraryTypeId		integer,
	libraryAttribute	integer,
libraryName			varchar(128),
libraryAliasName	varchar(128),
barCode				varchar(256),
	volumeId			integer,
	mediaLocation		integer,
	jobOpType			integer,
	failureType			integer)
DECLARE	@l_errorCode	INT
DECLARE	@l_volumeId		INT
DECLARE	@l_isMagnetic	INT
SET	@l_errorCode = 0
SET @l_volumeId = 0
DECLARE @IsCloudAndMagneticEqualPriority INT = 0
SELECT @IsCloudAndMagneticEqualPriority = ISNULL(CONVERT(INT,value),0) from GXGlobalParam WITH (NOLOCK) WHERE name = 'TreatCloudAsDiskForRestore'
INSERT	INTO @tblAFCVolume
SELECT	ACM.archCopyId, AGC.copy, AFC.isValid, AFC.flags,
		V.VolumeId, (case when (V.RecordingFormatId = 10001 AND (@IsCloudAndMagneticEqualPriority = 1 OR ISNULL(MP.MountPathTypeId, 0) <> 7 /*MOUNT_PATH_EXTERNAL_REMOTE_HOST*/)) then 1 else 0 end)
FROM	archFileCopy AFC WITH (NOLOCK), archChunkMapping ACM WITH (NOLOCK), archChunk AC WITH (NOLOCK), archGroupCopy AGC WITH (NOLOCK),
MMVolume V WITH (NOLOCK)
LEFT OUTER JOIN MMMountPath MP WITH(NOLOCK) ON V.MediaSideId = MP.MediaSideId
WHERE	AFC.archFileId = @i_archFileId AND AFC.commCellId = @i_commCellId
AND AFC.isValid > 0 AND AFC.physicalSize > 0 AND (@i_includeAgedData <> 0 OR (AFC.flags & 256) = 0)
	AND ACM.archFileId = AFC.archFileId AND ACM.commCellId = AFC.commCellId AND ACM.archCopyId = AFC.archCopyId AND ACM.chunkNumber = 1
	AND AC.id = ACM.archChunkId AND AC.commCellId = ACM.chunkCommCellId
	AND V.VolumeId = AC.volumeId
	AND AGC.id = ACM.archCopyId
	AND AGC.type <> 5 -- SELECT only none transitive copy files/chunks for restore, CVA_AGCOPYTYPE_TRANSITIVE = 5
-- If the file does not have any chunks then select the filecopy entry ordered by precedence
IF NOT EXISTS (SELECT * FROM @tblAFCVolume)
BEGIN
	SELECT	TOP 1 @o_archCopyId = AFC.archCopyId, @o_AFC_isValid = AFC.isValid, @o_AFC_flags = AFC.flags
	FROM	archFileCopy AFC, archGroupCopy AGC WITH (NOLOCK)
	WHERE	AFC.archFileId = @i_archFileId AND AFC.commCellId = @i_commCellId
AND AFC.isValid > 0 AND AFC.physicalSize = 0 AND (@i_includeAgedData <> 0 OR (AFC.flags & 256) = 0)
			AND AGC.id = AFC.archCopyId
			AND AGC.type <> 5 -- SELECT only none transitive copy files/chunks for restore, CVA_AGCOPYTYPE_TRANSITIVE = 5
	ORDER BY AGC.copy ASC
	RETURN @l_errorCode
END
IF @i_preferMagnetic = 0
	DECLARE curGetCopy CURSOR FOR
	SELECT	archCopyId, isValid, flags, volumeId, isMagnetic
	FROM	@tblAFCVolume
	ORDER BY copyPrec ASC
ELSE
	DECLARE curGetCopy CURSOR FOR
	SELECT	archCopyId, isValid, flags, volumeId, isMagnetic
	FROM	@tblAFCVolume
	ORDER BY isMagnetic DESC, copyPrec ASC
OPEN  curGetCopy
FETCH NEXT FROM curGetCopy
INTO  @o_archCopyId, @o_AFC_isValid, @o_AFC_flags, @l_volumeId, @l_isMagnetic
WHILE @@FETCH_STATUS = 0
BEGIN
	IF @i_preferAccessible = 0 AND @l_isMagnetic > 0
	BEGIN
		SET	@l_errorCode = 0
		GOTO DONE_WITH_CURSOR
	END
	INSERT	INTO @tblMA_DP
	EXEC	@l_errorCode = MMS2GetMAAndDPForVolume @l_volumeId, 0, 0, 0, 0, 0
IF @l_errorCode = 0
	BEGIN
		SET	@l_errorCode = 0
		IF EXISTS (
			SELECT *
			FROM	@tblMA_DP
			WHERE	volumeId = @l_volumeId AND failureType = 0 AND mediaLocation IN (1, 2) AND mmErrorCode = 0
			)
			GOTO DONE_WITH_CURSOR
	END
	SET @l_volumeId = 0
	FETCH NEXT FROM curGetCopy
	INTO  @o_archCopyId, @o_AFC_isValid, @o_AFC_flags, @l_volumeId, @l_isMagnetic
--	DELETE	FROM @tblMA_DP
END
DONE_WITH_CURSOR:
CLOSE curGetCopy
DEALLOCATE curGetCopy
IF @l_errorCode = 0 AND @l_volumeId = 0  -- if no eligible vol found then send based on below query. Otherwise send copy info where the vol is found.
BEGIN
	IF @i_preferMagnetic = 0
		SELECT	TOP 1 @o_archCopyId = V.archCopyId, @o_AFC_isValid = V.isValid, @o_AFC_flags = V.flags
		FROM	@tblAFCVolume V, @tblMA_DP M
		WHERE	V.volumeId = M.volumeId    AND mmErrorCode = 0
		ORDER BY M.mediaLocation ASC, V.copyPrec ASC
	ELSE
		SELECT	TOP 1 @o_archCopyId = V.archCopyId, @o_AFC_isValid = V.isValid, @o_AFC_flags = V.flags
		FROM	@tblAFCVolume V, @tblMA_DP M
		WHERE	V.volumeId = M.volumeId	   AND mmErrorCode = 0
		ORDER BY V.isMagnetic DESC, M.mediaLocation ASC, V.copyPrec ASC
END
ELSE
IF @l_errorCode <> 0
BEGIN
	IF @i_preferMagnetic = 0
		SELECT	TOP 1 @o_archCopyId = archCopyId, @o_AFC_isValid = isValid, @o_AFC_flags = flags
		FROM	@tblAFCVolume
		ORDER BY copyPrec ASC
	ELSE
		SELECT	TOP 1 @o_archCopyId = archCopyId, @o_AFC_isValid = isValid, @o_AFC_flags = flags
		FROM	@tblAFCVolume
		ORDER BY isMagnetic DESC, copyPrec ASC
END
RETURN @l_errorCode;
GO

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

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

insert into GXDBVersions values(2, 'archFileCopyPreferred',  'v1.15.200.5.20.1', 'archFileCopyPreferred', 'v1.15.200.5.20.1')
GO

