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 @clientErrCnt		INT
DECLARE @clientPkgImmUpgCnt	INT
DECLARE @clientId		INT
DECLARE @clientName		NVARCHAR(MAX)
DECLARE @packageName		NVARCHAR(MAX)
DECLARE @release		NVARCHAR(64)

SET @ScriptName			= 'Webserver/Webconsole 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 @clientPkgImmUpgCnt		= 0

PRINT @DescTag + ' Check for deprecated Webserver/Webconsole packages'

-- t_type: 1 - immediate upgarde; 2 - delay upgrade; 3 - deprecated; 4 - ImageLevel; 5 - DA
DECLARE @clientsWithWebserverPackage TABLE (t_clientId INT, t_packageId INT, t_client NVARCHAR(1024), t_package NVARCHAR(1024), t_release VARCHAR(64))

INSERT INTO @clientsWithWebserverPackage
SELECT T.*, R.release
FROM	tmpClientWithWebServerClient T, simAllGalaxyRel R, APP_CLient C 
WHERE	C.id = t_clientId AND C.releaseId = R.id

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

DECLARE	clientWarningCur CURSOR STATIC FOR 
	SELECT	DISTINCT t_client, ISNULL( (	SELECT '[' + T2.t_package + '] '
						FROM	@clientsWithWebserverPackage T2
						WHERE	T1.t_clientId = T2.t_clientId FOR XML PATH('')), ''), t_release
	FROM	@clientsWithWebserverPackage T1
	WHERE	t_clientId > 2
	ORDER BY t_client

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

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

WHILE @@FETCH_STATUS = 0
BEGIN
	SET @clientPkgImmUpgCnt = @clientPkgImmUpgCnt + 1

	IF @clientPkgImmUpgCnt = 1
	BEGIN
		IF @retCode = 0
			SET @retCode = 1
	
		PRINT  @WarningTag + ' The following clients have webserver or webclient installed and need to be upgraded immediately after CS upgrade'
		PRINT  @HeaderTag + ' Client Name' + @colSeperator + 'Release' + @colSeperator + 'Package Name'
	END
	

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

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

CX_EXIT:

IF @openCurFlag > 0
BEGIN
	CLOSE clientWarningCur
	DEALLOCATE clientWarningCur
END

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

DROP TABLE tmpClientWithWebServerClient

RETURN