--USE commserv
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 @errorCode INTEGER

DECLARE @olderClientsCount INTEGER	= 0
DECLARE @warningClientsCount INTEGER	= 0
DECLARE @upgradeToRelId INTEGER		= 16
DECLARE	@clTbl TABLE ( t_ID INTEGER, t_name VARCHAR(256), t_status INT, t_flag INT)

PRINT @DescTag + ' Check for supported Client version'
DECLARE @output nvarchar(max)
SET @output = ''
SET @output = @ErrorTag + 'There are clients with older software versions (more than two versions old).' + char(13) 
			+ @AdviceTag + ' You must upgrade or de-configure the following clients' + char(13)
			-- + 'Following list may not be the complete list' + char(13)
			+ @HeaderTag + ' Client Name' + char(13) 
			+ '----------------------' + char(13)

INSERT INTO @clTbl
SELECT DISTINCT id,name,status,0
FROM APP_Client
WHERE ReleaseId < (@upgradeToRelId -2 ) AND id > 2

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

-- MR 169778: to skip v8 clients with OS: AIX 5.2, NetWare, Red Hat Linux 3.8, SuSE Linux 9.x, Ubuntu 7.x
DELETE @clTbl
FROM APP_Client C, simOperatingSystem O
WHERE C.id = t_ID AND C.ReleaseId = 13 AND C.simOperatingSystemId = O.ID AND 
	(O.type = 'Netware' OR O.type = 'UNIX' AND C.id IN (	SELECT componentNameId FROM APP_ClientProp P 
								WHERE P.attrName = 'SIM OS INFO' AND (P.AttrVal like 'AIX%5.2%' OR P.AttrVal like 'Red Hat %' OR P.AttrVal like 'SuSE %' OR P.AttrVal like 'Ubuntu %')))

-- MR161212: Warning\Advice for clients having either Windows 2000 or SQL 2000 V8 agent installed
--	t_flag: 1 = windows 2000; 2 = SQL 2000
UPDATE @clTbl
SET t_flag = 1
FROM APP_Client C, APP_ClientProp P
WHERE C.id = t_ID AND C.ReleaseId = 13 AND C.id = P.componentNameId AND P.attrName = 'SIM OS INFO' AND P.AttrVal like 'Windows 2000%'

UPDATE @clTbl
SET t_flag = 2
FROM APP_Client C, APP_Application A, APP_InstanceName I, APP_InstanceProp P
WHERE C.id = t_ID AND C.ReleaseId = 13 AND C.id = A.clientId AND (A.subclientStatus & 20) = 0 AND A.instance = I.id AND (I.status & 20) = 0 AND A.instance = P.componentNameId AND P.attrName = 'MSSQL Version' AND P.attrVal like '8%%'

DELETE @clTbl
WHERE (t_status & 0x2) > 0 AND t_flag = 0

-- Old Client Report
SELECT @olderClientsCount = count(t_id) FROM @clTbl WHERE (t_status & 0x2) = 0

SELECT @output = @output + @DataTag +  ' ' + t_name + char(13)  from @clTbl WHERE (t_status & 0x2) = 0

IF @olderClientsCount = 0 
BEGIN 
	PRINT 'There are no clients with older software versions (more than two versions old).'
END 
ELSE
BEGIN
	PRINT @output
END

-- deprecated windows 2000 or SQL2000
SELECT @warningClientsCount = count(t_id) FROM @clTbl where t_flag > 0
IF @warningClientsCount = 0
BEGIN 
	PRINT 'There are no v8 clients with Windows 2000 or SQL 2000 V8 agent installed.'
END 
ELSE
BEGIN
	SET @output = ''
	SET @output = @WarningTag + 'The following v8 clients having Windows 2000 or SQL 2000 V8 agent installed.' + char(13) 
			+ @AdviceTag + '  They must be upgraded to V9 SP14 + update 47028' + char(13)
			+ @HeaderTag + ' Client Name' + @colSeperator + 'Type' + char(13)
			+ '------------------------------------------------' + char(13)

	SELECT @output = @output + @DataTag +  ' ' + t_name + @colSeperator + (case t_flag when 1 then 'windows 2000' else 'SQL 2000' END) + char(13)  from @clTbl where t_flag > 0 Order by t_flag desc
	PRINT @output
END

CX_EXIT: 

IF @errorCode != 0 OR @olderClientsCount > 0
	SELECT 2
ELSE IF @warningClientsCount > 0
	SELECT 1
ELSE
	SELECT 0

	
RETURN