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 @VSAAppType		INT
DECLARE @agentStatusToSkip	INT
DECLARE @CSInstCnt		INT
DECLARE @clientCnt		INT
DECLARE @clientId		INT
DECLARE @clientName		NVARCHAR(MAX)
DECLARE @instanceName		NVARCHAR(MAX)
DECLARE @release		NVARCHAR(64)

SET @ScriptName			= 'Deprecated VSA instance 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 @clientCnt			= 0
SET @VSAAppType			= 106
SET @agentStatusToSkip		= 22
SET @CSInstCnt			= 0

PRINT @DescTag + ' Check for deprecated instances for Virtual Server Agent (VSA)'

DECLARE @VSAInstance TABLE (t_clientId INT, t_client NVARCHAR(1024), t_instance NVARCHAR(1024), t_type INT, t_release VARCHAR(64))

--instance type: 1 = 'ESX instance'; 0 = 'XEN instance'

INSERT INTO @VSAInstance
SELECT	DISTINCT A.clientId, C.name, I.name, CASE attrVal WHEN '100' THEN 1 ELSE 0 END, R.release
FROM	APP_Application A	JOIN APP_InstanceName I ON A.instance = I.id AND (I.status & 16) = 0
				JOIN APP_InstanceProp P ON P.componentNameId = A.instance and P.attrName = 'Virtual Server Instance Type' and P.modified = 0
				JOIN APP_Client C	ON A.clientId = C.id
				JOIN simAllGalaxyRel R	ON C.releaseId = R.id
WHERE	A.appTypeId = @VSAAppType AND (A.subclientStatus & @agentStatusToSkip) = 0 AND P.attrVal IN ('100', '201')

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

DECLARE	clientsWithESXInstanceCur CURSOR STATIC FOR SELECT t_clientId, t_client, t_instance, t_release FROM @VSAInstance WHERE t_type = 1 ORDER BY t_clientId
SET @openCurFlag = 1
OPEN clientsWithESXInstanceCur 
FETCH NEXT FROM clientsWithESXInstanceCur INTO @clientId, @clientName, @instanceName, @release

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

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

		IF @CSInstCnt = 1
		BEGIN
			SET @retCode = 2
			PRINT @ErrorTag + ' The CommServe cannot be upgrade because it has the following deprecated ESX instances configured on VSA clients:'
			PRINT  @HeaderTag + 'Instance Name'
			PRINT  @AdviceTag + 'To proceed with the upgrade, ESX instances must be changed to vCenter.'
		END

		PRINT @DataTag + @instanceName
	END
	ELSE
	BEGIN
		SET @clientCnt = @clientCnt + 1

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

			PRINT  @WarningTag + ' The following VSA clients cannot be upgraded to the new software version because deprecated ESX instances have been found:'
			PRINT  @HeaderTag + ' Client Name' + @colSeperator + 'Release' + @colSeperator + 'Instance Name'
			PRINT  @AdviceTag + ' To proceed with the upgrade, ESX instances must be changed to vCenter. Otherwise, clients will continue to operate in backward compatibility mode.'
		END

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

	FETCH NEXT FROM clientsWithESXInstanceCur INTO @clientId, @clientName, @instanceName, @release
	SET	@errorCode = @@ERROR 
	IF	@errorCode != 0 GOTO CX_EXIT 
END

SET @CSInstCnt = 0
SET @clientCnt = 0
DECLARE	clientsWithXENInstanceCur CURSOR STATIC FOR SELECT t_clientId, t_client, t_instance, t_release FROM @VSAInstance WHERE t_type = 0 ORDER BY t_clientId
SET @openCurFlag = 2
OPEN clientsWithXENInstanceCur 
FETCH NEXT FROM clientsWithXENInstanceCur INTO @clientId, @clientName, @instanceName, @release

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

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

		IF @CSInstCnt = 1
		BEGIN
			SET @retCode = 2
			PRINT @ErrorTag + ' The CommServe cannot be upgrade because it has the following deprecated XEN instances configured on VSA clients:'
			PRINT  @HeaderTag + 'Instance Name'
			PRINT  @AdviceTag + 'To proceed with the upgrade, XEN instances must be deleted. Remember that after you delete the instances, all the backed up data will be lost. If you want to keep the backup data, install a new XEN agent, perform the required backup jobs, and then delete the XEN instance from the CommCell Console. '
		END

		PRINT @DataTag + @instanceName
	END
	ELSE
	BEGIN
		SET @clientCnt = @clientCnt + 1

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

			PRINT  @WarningTag + ' The following VSA clients cannot be upgraded to the new software version because XEN instances have been found:'
			PRINT  @HeaderTag + ' Client Name' + @colSeperator + 'Release' + @colSeperator + 'Instance Name'
			PRINT  @AdviceTag + 'The clients will continue to operate in backward compatibility mode.'
		END

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

	FETCH NEXT FROM clientsWithXENInstanceCur INTO @clientId, @clientName, @instanceName, @release
	SET	@errorCode = @@ERROR 
	IF	@errorCode != 0 GOTO CX_EXIT 
END


CX_EXIT:

IF @openCurFlag > 1
BEGIN
	CLOSE clientsWithXENInstanceCur
	DEALLOCATE clientsWithXENInstanceCur
END

IF @openCurFlag > 0
BEGIN
	CLOSE clientsWithESXInstanceCur 
	DEALLOCATE clientsWithESXInstanceCur 
END

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

RETURN