-- Block DB Upgrade if there is any 9.0 client with service pack less than SP3

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

SET @ScriptName			= 'Deprecated client application 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 @CSAgentCnt			= 0
SET @clientCnt			= 0
SET @agentStatusToSkip		= 20

PRINT @DescTag + ' Check for deprecated application versions'

DECLARE @blockedAgentTbl	TABLE (t_clientId INT, t_client NVARCHAR(1024),  t_agent NVARCHAR(MAX), t_instance NVARCHAR(MAX), t_release VARCHAR(64))

INSERT INTO @blockedAgentTbl
SELECT	DISTINCT C.id, C.name, T.name, I.name, R.release
FROM	APP_Application A	JOIN APP_InstanceName I	ON A.instance = I.id AND (I.status & @agentStatusToSkip) = 0
				JOIN APP_Client C	ON A.clientId = C.id
				JOIN app_iDAType T	ON A.appTypeId = T.type
				JOIN simAllGalaxyRel R	ON C.releaseId = R.id
WHERE (A.subclientStatus & @agentStatusToSkip) = 0 AND 
	(A.appTypeId = 81 AND EXISTS (select * from APP_InstanceProp P where componentNameId = A.instance and attrName = 'MSSQL Version' AND attrVal like '8%' and P.modified = 0)
		OR
	 A.appTypeId = 5 AND EXISTS (select * from APP_InstanceProp P where componentNameId = A.instance and attrName = 'SYBASE version' AND attrVal like '%/12.5%' and P.modified = 0)
	 OR 
	 A.appTypeId IN (22, 80) AND EXISTS (select * from APP_InstanceProp P where componentNameId = A.instance and attrName = 'Oracle version' AND attrVal like '9%' and P.modified = 0)
	 OR
	 A.appTypeId IN (37, 62, 103) AND EXISTS (select * from APP_InstanceProp P where componentNameId = A.instance and attrName = 'DB2 Version' AND CAST(SUBSTRING(attrVal,4,4) AS INT) < 905 and P.modified = 0)
	 OR
	 A.appTypeId = 3 AND EXISTS (select * from APP_InstanceProp P where componentNameId = A.instance and attrName = 'Informix Version' AND CAST(SUBSTRING(attrVal,1,5) AS FLOAT) < 11.50 and P.modified = 0)
	 ) 


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

DECLARE	blockedAgentCur CURSOR STATIC FOR SELECT t_clientId, t_client, t_agent, t_instance, t_release FROM @blockedAgentTbl ORDER BY t_clientId, t_agent

SET @openCurFlag = 1
OPEN blockedAgentCur
FETCH NEXT FROM blockedAgentCur INTO @clientId, @clientName, @agentName, @instanceName, @release

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

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

		IF @CSAgentCnt = 1
		BEGIN
			SET @retCode = 2
			PRINT @ErrorTag + ' The CommServe cannot be upgraded because it has the following deprecated agents or instances configured:'
			PRINT  @AdviceTag + ' The upgrade can proceed only if deprecated instances are deleted from the CommCell Console. Be aware that all of the backed up data will be lost after the instances are deleted.'
			--PRINT  @AdviceTag + ' Please delete the deprecated instances from GUI and resume the upgrade. Be aware that all backed up data will be lost once the instance is deleted.'
			PRINT  @HeaderTag + ' Client Name' + @colSeperator + 'Agent Name' + @colSeperator  + 'Instance Name'
		END
	END
	ELSE
	BEGIN
		SET @clientCnt = @clientCnt + 1

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

			PRINT  @WarningTag + 'The following clients have deprecated agents or instances that cannot be upgraded to the new software version. '
			PRINT  @AdviceTag + ' The clients can continue to operate in backward compatibility mode.'
			PRINT  @HeaderTag + ' Client Name' + @colSeperator + 'Release' + @colSeperator + 'Agent Name' + @colSeperator  + 'Instance Name'
		END
	END

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

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

CX_EXIT:

IF @openCurFlag > 0
BEGIN
	CLOSE blockedAgentCur
	DEALLOCATE blockedAgentCur
END

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

RETURN