SET NOCOUNT ON 

DECLARE @ErrorTag NVARCHAR(20) 
SET			@ErrorTag = '###ERROR###'

DECLARE @WarningTag NVARCHAR(20) 
SET			@WarningTag = '###WARNING###'

DECLARE @AdviceTag NVARCHAR(20) 
SET			@AdviceTag = '###ADVICE###'

DECLARE @HeaderTag NVARCHAR(20) 
SET			@HeaderTag = '###HEADER###'

DECLARE @DataTag NVARCHAR(20) 
SET			@DataTag = '###DATA###'

DECLARE @DescTag NVARCHAR(20) 
SET	@DescTag = '###DESCRIPTION###'

DECLARE @colSeperator NVARCHAR(20) 
SET			@colSeperator = ':,:'

DECLARE @MANameCurFlag  INTEGER 
SET 		@MANameCurFlag  = 0 

DECLARE @MACount 		INTEGER 
SET			@MACount		= 0

DECLARE @errorCode  INTEGER 
SET			@errorCode	= 0

DECLARE @retCode  INTEGER 
SET			@retCode	= 0

DECLARE @HostId 	INTEGER 
DECLARE @MAName 	NVARCHAR(510) 
DECLARE @output		NVARCHAR(MAX)

DECLARE @MAWithOpticalLibaryConfigured TABLE
(
		HostId		INTEGER,
		MAName		NVARCHAR(510) 
		PRIMARY KEY (HostId)
)

PRINT @DescTag + ' Check for Media Agents with Optical Devices configured'

INSERT INTO @MAWithOpticalLibaryConfigured
SELECT DISTINCT AC.id, AC.name
FROM APP_Client AC WITH (NOLOCK), MMLibrary L WITH (NOLOCK), MMLibraryController LC WITH (NOLOCK)
WHERE AC.id = LC.ClientId
AND LC.LibraryId = L.LibraryId
AND L.LibraryId > 0
AND LibraryTypeId IN (2, 5, 7) --CV_LIB_OPTICAL, CV_LIB_STANDALONE_OPTICAL, CV_LIB_BLIND_OPTICAL


SELECT @errorCode	= @@ERROR, @MACount = @@ROWCOUNT
IF	@errorCode != 0 GOTO CX_EXIT 

--If no Media Agents found with Optical Devices configured
IF @MACount = 0 
BEGIN 
	SET @retCode = 0
	PRINT 'No MediaAgents have been found with any Optical Devices configured.'
	GOTO CX_EXIT
END 

--If Media Agent found with Optical Devices configured is CommServer then fail the Pre-Upgrade check
IF EXISTS (SELECT * FROM @MAWithOpticalLibaryConfigured WHERE HostId = 2)
BEGIN
	SET @retCode = 2
	SET @output = @ErrorTag + ' The CommServe cannot be upgrade because MediaAgents that use Optical Devices have been found. Optical devices are not supported since Version 10.'
END	
ELSE
--If Media Agent(s) found with Optical Devices configured is not CommServer then just give warning in the Pre-Upgrade check
BEGIN
	SET @retCode = 1
	SET @output = @WarningTag + ' Found one or more MediaAgents which still use Optical Devices. Optical devices are not supported since Version 10.'
END

--Print Error/Warning
SET @output = @output + CHAR(10) + 'The following (' + CAST(@MACount AS VARCHAR(10)) + ') MediaAgents are configured with Optical Devices.'
SET @output = @output + CHAR(10) + 'The upgrade will fail for the specified MediaAgents if this is not corrected.' 
SET @output = @output + CHAR(10)

PRINT @output

--Print Header
PRINT @HeaderTag + ' Media Agents'
PRINT '----------------------------'
	
DECLARE	MANameCur CURSOR STATIC FOR 
SELECT HostId, MAName
FROM @MAWithOpticalLibaryConfigured

SET	@errorCode	= @@ERROR 
IF	@errorCode != 0 GOTO CX_EXIT 

SET @MANameCurFlag = 1 
OPEN MANameCur 

SET	@errorCode	= @@ERROR 
IF	@errorCode != 0 GOTO CX_EXIT 

FETCH NEXT 
FROM  MANameCur 
INTO  @HostId, @MAName 

SET	@errorCode	= @@ERROR 
IF	@errorCode != 0 GOTO CX_EXIT 

WHILE @@FETCH_STATUS = 0 
BEGIN 
	PRINT @DataTag + ' ' + @MAName

	FETCH NEXT 
	FROM  MANameCur 
	INTO  @HostId, @MAName 

	SET	@errorCode	=	@@ERROR 
	IF	@errorCode != 0 GOTO CX_EXIT 
END 

--Print Advice
SET @output = CHAR(10) + @AdviceTag + ' Before the upgrade, you must deconfigure Optical Devices.'
SET @output = @output + CHAR(10)

PRINT @output

CX_EXIT: 

IF @MANameCurFlag = 1 
BEGIN 
	CLOSE	    MANameCur 
	DEALLOCATE	MANameCur 
END

IF @errorCode != 0
	SELECT 2
ELSE
	SELECT @retCode

RETURN