SET NOCOUNT ON 

DECLARE @ScriptName		NVARCHAR(128)
DECLARE @ErrorTag		NVARCHAR(20) 
DECLARE @WarningTag		NVARCHAR(20) 
DECLARE @AdviceTag		NVARCHAR(20) 
DECLARE @HeaderTag		NVARCHAR(20) 
DECLARE @DataTag		NVARCHAR(20)
DECLARE @DescTag		NVARCHAR(20)
DECLARE @colSeperator		NVARCHAR(20) 
DECLARE @retCode		INT
DECLARE @errorCode		INT
DECLARE @openCurFlag		INT
DECLARE @agentStatusToSkip	INT
DECLARE @CSPkgCnt		INT
DECLARE @clientErrCnt		INT
DECLARE @clientPkgImmUpgCnt	INT
DECLARE @clientPkgDelayCnt	INT
DECLARE @clientPkgDepratedCnt	INT
DECLARE @clientImagePkgCnt	INT
DECLARE @client1TouchPkgCnt	INT
DECLARE @clientDAPkgCnt		INT
DECLARE @pkgType		INT
DECLARE @clientId		INT
DECLARE @clientName		NVARCHAR(MAX)
DECLARE @packageName		NVARCHAR(MAX)
DECLARE @release		NVARCHAR(64)
DECLARE @ArchAgentWarning	NVARCHAR(MAX)

SET @ScriptName			= 'Deprecated package check: '
SET @ErrorTag			= '###ERROR###'
SET @WarningTag			= '###WARNING###'
SET @AdviceTag			= '###ADVICE###'
SET @HeaderTag			= '###HEADER###'
SET @DataTag			= '###DATA###'
SET @DescTag			= '###DESCRIPTION###'
SET @colSeperator		= ':,:'
SET @retCode			= 0
SET @errorCode			= 0
SET @openCurFlag		= 0
SET @CSPkgCnt 			= 0
SET @clientErrCnt		= 0
SET @clientPkgImmUpgCnt		= 0
SET @clientPkgDelayCnt		= 0
SET @clientPkgDepratedCnt	= 0
SET @clientImagePkgCnt		= 0
SET @client1TouchPkgCnt		= 0
SET @clientDAPkgCnt		= 0
SET @agentStatusToSkip		= 20
SET @ArchAgentWarning		= 'After upgrading to version 11, you can use this agent to recover data, but you cannot use it to archive data. To archive data, use the appropriate OnePass agent. For more information about OnePass agents, refer to the Documentation site.'

PRINT @DescTag + ' Check for deprecated packages'

-- t_type: 1 - immediate upgrade; 2 - delay upgrade; 3 - soft deprecated; 4 - hard deprecated; 5 - DA;  6 - ImageLevel; 7 - 1TouchServer
DECLARE @clientsWithDeprecatedPackage	TABLE (t_clientId INT, t_packageId INT, t_client NVARCHAR(1024), t_package NVARCHAR(1024), t_type INT)
DECLARE @clientsNotupgradable		TABLE (t_id int, t_client NVARCHAR(1024), t_packages NVARCHAR(1024), t_type INT)
DECLARE @CSArchPkgs			TABLE (t_packageId INT, t_package NVARCHAR(1024), t_status INT)

INSERT INTO @clientsWithDeprecatedPackage
SELECT *, 1 FROM tmpClientWithWebServerClient
UNION
SELECT *, 2 FROM tmpClientWithDelayUpgradeAgents
UNION
SELECT t_clientId, t_packageId, t_client, t_package, 3 FROM tmpClientWithSoftDeprecatedPackages WHERE t_type = 'SIM_PKG_NOT_SUPPORT_UPGRADE'
UNION
SELECT t_clientId, t_packageId, t_client, t_package, 4 FROM tmpClientWithHardDeprecatedPackages 
UNION
SELECT t_clientId, t_packageId, t_client, t_package, 5 FROM tmpClientWithSoftDeprecatedPackages WHERE t_type = 'ClassicArchivingAgent'
UNION
SELECT t_clientId, t_packageId, t_client, t_package, 6 FROM tmpClientWithSoftDeprecatedPackages WHERE t_type = 'ImageLevelAgent'
UNION
SELECT t_clientId, t_packageId, t_client, t_package, 7 FROM tmpClientWithSoftDeprecatedPackages WHERE t_type = '1TouchServer'

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

-- Not allow CS upgrade if CS has active archiver agents
INSERT INTO @CSArchPkgs
SELECT t_packageId, t_package, A.status
FROM	@clientsWithDeprecatedPackage, APP_iDAName A, simPackageMap M
WHERE	t_clientId = 2 AND t_type = 5 AND t_clientId = A.clientID AND t_packageId = M.simPackageID AND A.appTypeId = M.appType

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

SET @packageName = ISNULL( (	SELECT '[' + t_package + '] '
				FROM	@CSArchPkgs
				WHERE	(t_status & 2) = 0 FOR XML PATH('')), '')
IF LEN(@packageName) > 0
BEGIN
	SET @retCode = 2
	PRINT @ErrorTag + 'Commserver has deprecated archiver package installed. To upgrade Commserver to version 11, you will need to release license for the deprecated package. ' + @ArchAgentWarning
	PRINT @HeaderTag + 'Package(s)'
	PRINT @DataTag + @packageName
END

SET @packageName = ISNULL( (	SELECT '[' + t_package + '] '
				FROM	@CSArchPkgs
				WHERE	(t_status & 2) > 0 FOR XML PATH('')), '')
IF LEN(@packageName) > 0
BEGIN
	PRINT @WarningTag + 'Commserver has deprecated archiver packages installed. ' + @ArchAgentWarning
	PRINT @HeaderTag + 'Package(s)'
	PRINT @DataTag + @packageName
END

-- Not allow CS upgrade if CS has clients with both immediate upgrade packages & soft deprecated/delay upgrade packages installed
INSERT INTO @clientsNotupgradable
SELECT	t_clientId, t_client, t_package, 1
FROM	@clientsWithDeprecatedPackage T1
WHERE	t_clientId > 2 AND t_type = 1 AND 
	EXISTS (select * from @clientsWithDeprecatedPackage T2 where T1.t_clientId = T2.t_clientId and T2.t_type IN (2, 3))

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

-- Not allow CS upgrade if CS has clients with hard deprecated packages
INSERT INTO @clientsNotupgradable
SELECT	t_clientId, t_client, t_package, 2
FROM	@clientsWithDeprecatedPackage
WHERE	t_clientId > 2 AND t_type = 4

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

UPDATE @clientsNotupgradable
SET	t_packages = ISNULL( (	SELECT '[' + t_package + '] '
				FROM	@clientsWithDeprecatedPackage
				WHERE	t_id = t_clientId and t_type IN (2, 3) FOR XML PATH('')), '')
WHERE t_type = 1

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

UPDATE @clientsNotupgradable
SET	t_packages = ISNULL( (	SELECT '[' + t_package + '] '
				FROM	@clientsWithDeprecatedPackage
				WHERE	t_id = t_clientId and t_type = 4 FOR XML PATH('')), '')
WHERE t_type = 2

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

-- Not allow CS upgrade if CS has deprecated or delay upgraded packages installed
INSERT INTO @clientsNotupgradable
SELECT	t_clientId, t_client, t_package, 0
FROM	@clientsWithDeprecatedPackage
WHERE	t_clientId = 2 AND t_type IN (2, 3, 4)

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

DECLARE	upgBolckedClientCur CURSOR STATIC FOR 
	SELECT DISTINCT t_id, t_client, t_packages, t_type, (select R.release from simAllGalaxyRel R, APP_CLient C where t_id = C.id AND C.releaseId = R.id)
	FROM @clientsNotupgradable ORDER BY t_type, t_client

OPEN upgBolckedClientCur
SET @openCurFlag = @openCurFlag + 1
FETCH NEXT FROM upgBolckedClientCur INTO @clientId, @clientName, @packageName, @pkgType, @release

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

WHILE @@FETCH_STATUS = 0
BEGIN
	IF @clientId = 2
	BEGIN
		SET @CSPkgCnt = @CSPkgCnt + 1

		IF @CSPkgCnt = 1
		BEGIN
			SET @retCode = 2
			PRINT @ErrorTag + ' The CommServe cannot be upgraded because it has deprecated packages or packages that are not yet available for upgrade.'
			PRINT @AdviceTag + ' Before the upgrade, you must uninstall the packages' + char(13)
			PRINT  @HeaderTag + 'Package(s)'
		END

		PRINT @DataTag + @packageName
	END
	ELSE IF @pkgType = 1
	BEGIN
		SET @clientErrCnt = @clientErrCnt + 1

		IF @clientErrCnt = 1
		BEGIN
			IF @retCode = 0
				SET @retCode = 2

			PRINT  @ErrorTag + ' The following clients which need to be upgraded immediately also have deprecated packages or packages that are not yet available for upgrade.'
			PRINT  @AdviceTag + ' Before the upgrade, you must uninstall the packages' + char(13)
			PRINT  @HeaderTag + ' Client Name' + @colSeperator + 'Release' + @colSeperator + 'Package(s)'
		END

		PRINT @DataTag + @clientName + @colSeperator + @release + @colSeperator + @packageName
	END
	ELSE IF @pkgType = 2
		BEGIN
		SET @clientPkgDepratedCnt = @clientPkgDepratedCnt + 1

		IF @clientPkgDepratedCnt = 1
		BEGIN
			IF @retCode = 0
				SET @retCode = 2
	
			PRINT  @ErrorTag + ' The following clients have deprecated packages installed and cannot be upgraded.'
			PRINT  @AdviceTag + ' Before the upgrade, you must uninstall the packages' + char(13)
			PRINT  @HeaderTag + ' Client Name' + @colSeperator + 'Release' + @colSeperator + 'Package(s)'
		END

		PRINT @DataTag + @clientName + @colSeperator + @release + @colSeperator + @packageName
	END

	FETCH NEXT FROM upgBolckedClientCur INTO @clientId, @clientName, @packageName, @pkgType, @release
	SET	@errorCode = @@ERROR 
	IF	@errorCode != 0 GOTO CX_EXIT 
END

SET @clientPkgDepratedCnt = 0
DECLARE	clientWarningCur CURSOR STATIC FOR 
	SELECT	DISTINCT t_client, ISNULL( (	SELECT '[' + T2.t_package + '] '
						FROM	@clientsWithDeprecatedPackage T2
						WHERE	T1.t_clientId = T2.t_clientId and T1.t_type = T2.t_type FOR XML PATH('')), ''), t_type,
		(select R.release from simAllGalaxyRel R, APP_CLient C where C.id = t_clientId AND C.releaseId = R.id)
	FROM	@clientsWithDeprecatedPackage T1
	WHERE	t_clientId > 2 AND t_clientId NOT IN (select t_id from @clientsNotupgradable)
	ORDER BY t_type, t_client

OPEN clientWarningCur
SET @openCurFlag = @openCurFlag + 1
FETCH NEXT FROM clientWarningCur INTO @clientName, @packageName, @pkgType, @release

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

WHILE @@FETCH_STATUS = 0
BEGIN		
	IF @pkgType = 2
	BEGIN
		SET @clientPkgDelayCnt = @clientPkgDelayCnt + 1

		IF @clientPkgDelayCnt = 1
		BEGIN
			IF @retCode = 0
				SET @retCode = 1
	
			PRINT  @WarningTag + ' The following clients have packages that are not yet available for upgrade at this time. Refer to the Documentation for details'
			PRINT  @HeaderTag + ' Client Name' + @colSeperator + 'Release' + @colSeperator + 'Package Name'
		END
	END
	ELSE IF @pkgType = 3
	BEGIN
		SET @clientPkgDepratedCnt = @clientPkgDepratedCnt + 1

		IF @clientPkgDepratedCnt = 1
		BEGIN
			IF @retCode = 0
				SET @retCode = 1
	
			PRINT  @WarningTag + ' The following clients have deprecated packages installed and cannot be upgraded'
			PRINT  @HeaderTag + ' Client Name' + @colSeperator + 'Release' + @colSeperator + 'Package Name'
		END
	END
	ELSE IF @pkgType = 5
	BEGIN
		SET @clientDAPkgCnt = @clientDAPkgCnt + 1

		IF @clientDAPkgCnt = 1
		BEGIN
			IF @retCode = 0
				SET @retCode = 1

			PRINT  @WarningTag + 'The following clients have deprecated packages installed. To upgrade this client to version 11, you will need to release license for the deprecated package. ' + @ArchAgentWarning
			PRINT  @HeaderTag + ' Client Name' + @colSeperator + 'Release' + @colSeperator + 'Package Name'
		END
	END
	ELSE IF @pkgType = 6
	BEGIN
		SET @clientImagePkgCnt = @clientImagePkgCnt + 1

		IF @clientImagePkgCnt = 1
		BEGIN
			IF @retCode = 0
				SET @retCode = 1
	
			PRINT  @WarningTag + ' The following clients have deprecated packages installed. After you upgrade to Version 11, you can use the deprecated agent to restore data, but not for backup operations. If you want to back up the data, configure the appropriate file system agent to perform block-level backups. For more information, refer to the <a href="https://documentation.commvault.com/commvault/v11/article?p=features/backup/block_level/c_bl_mgrtng_frm_img_lvl.htm">Documentation site</a>'
			PRINT  @HeaderTag + ' Client Name' + @colSeperator + 'Release' + @colSeperator + 'Package Name'
		END
	END
		ELSE IF @pkgType = 7
	BEGIN
		SET @client1TouchPkgCnt = @client1TouchPkgCnt + 1

		IF @client1TouchPkgCnt = 1
		BEGIN
			IF @retCode = 0
				SET @retCode = 1
	
			PRINT  @WarningTag + ' 1-Touch upgrades are not supported. In version 11, you do not require the 1-Touch Server to obtain the ISO file for recovery operations. You can download the ISO file from the Cloud Services website.'
			PRINT  @HeaderTag + ' Client Name' + @colSeperator + 'Release' + @colSeperator + 'Package Name'
		END
	END

	IF @pkgType <> 1
		PRINT @DataTag + @clientName + @colSeperator + @release + @colSeperator + @packageName

	FETCH NEXT FROM clientWarningCur INTO @clientName, @packageName, @pkgType, @release
	SET	@errorCode = @@ERROR 
	IF	@errorCode != 0 GOTO CX_EXIT 
END

CX_EXIT:

IF @openCurFlag > 1
BEGIN
	CLOSE clientWarningCur
	DEALLOCATE clientWarningCur
END
IF @openCurFlag > 0
BEGIN
	CLOSE upgBolckedClientCur
	DEALLOCATE upgBolckedClientCur
END

IF @errorCode != 0
BEGIN
	PRINT @ScriptName + 'failed'
	SELECT 2
END
ELSE
BEGIN
	IF @retCode = 0
		PRINT @ScriptName + 'passed'
	SELECT @retCode 
END

DROP TABLE tmpClientWithDelayUpgradeAgents
DROP TABLE tmpClientWithSoftDeprecatedPackages
DROP TABLE tmpClientWithHardDeprecatedPackages

RETURN