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 @colSeparator			NVARCHAR(20) 
DECLARE @retCode				INT
DECLARE @errorCode				INT
DECLARE @openCurFlag			INT
DECLARE @openCSCurFlag			INT
DECLARE @openWebServerCurFlag	INT
DECLARE @clientErrCnt			INT
DECLARE @clientCSUpgCnt			INT
DECLARE @clientMAUpgCnt			INT
DECLARE @clientPkgImmUpgCnt		INT
DECLARE @clientId				INT
DECLARE @clientName				NVARCHAR(MAX)
DECLARE @clientOS				NVARCHAR(MAX)
DECLARE @machineTypeCS			INT = 1
DECLARE @machineTypeMA			INT = 0 

SET @ScriptName				= 'CommServe and MediaAgent operating system check: '
SET @ErrorTag				= '###ERROR###'
SET @WarningTag				= '###WARNING###'
SET @AdviceTag				= '###ADVICE###'
SET @HeaderTag				= '###HEADER###'
SET @DataTag				= '###DATA###'
SET @DescTag				= '###DESCRIPTION###'
SET @colSeparator			= ':,:'
SET @retCode				= 0
SET @errorCode				= 0
SET @openCurFlag			= 0
SET @openCSCurFlag			= 0
SET @openWebServerCurFlag	= 0
SET @clientCSUpgCnt			= 0
SET @clientMAUpgCnt			= 0
SET @clientPkgImmUpgCnt		= 0

PRINT @DescTag + ' Check for CommServe and MediaAgents with unsupported operating system'

CREATE TABLE #machinesWithWindowsOS (clientId INT, operatingSystemVersion VARCHAR(64), operatingSystemName VARCHAR(64), typeOfMachine INTEGER)

INSERT INTO #machinesWithWindowsOS
SELECT DISTINCT 
		C.id, 
		dbo.GetClientOSRelease(C.id, ''),
		CASE WHEN CHARINDEX(':-', dbo.scgGetClientOSName(C.id)) > 0 THEN SUBSTRING(dbo.scgGetClientOSName(C.id), 1, CHARINDEX(':-', dbo.scgGetClientOSName(C.id)) - 1) ELSE 'Unknown OS' END,
		CASE WHEN (C.id = 2 OR C.id IN (SELECT PMClientId from APP_VmToPMMap WHERE VMClientId = 2)) THEN @machineTypeCS ELSE @machineTypeMA END as typeOfMachine
FROM APP_Client C
JOIN simInstalledPackages SIP on C.id = SIP.ClientId
WHERE SIP.simPackageID = 51 OR (C.id = 2 OR C.id IN (SELECT PMClientId from APP_VmToPMMap WHERE VMClientId = 2))

CREATE TABLE #guestOsVersionTable (name varchar(255), versionNumber float)

INSERT INTO #guestOsVersionTable VALUES ('win2000AdvServGuest', 5)
INSERT INTO #guestOsVersionTable VALUES ('win2000ProGuest', 5)
INSERT INTO #guestOsVersionTable VALUES ('win2000ServGuest', 5)
INSERT INTO #guestOsVersionTable VALUES ('win31Guest', 3.1)
INSERT INTO #guestOsVersionTable VALUES ('win95Guest', 4)
INSERT INTO #guestOsVersionTable VALUES ('win98Guest', 4.1)
INSERT INTO #guestOsVersionTable VALUES ('windows7_64Guest', 6.1)
INSERT INTO #guestOsVersionTable VALUES ('windows7Guest', 6.1)
INSERT INTO #guestOsVersionTable VALUES ('windows7Server64Guest', 6.1)
INSERT INTO #guestOsVersionTable VALUES ('windowsHyperVGuest', 6)
INSERT INTO #guestOsVersionTable VALUES ('winLonghorn64Guest', 6)
INSERT INTO #guestOsVersionTable VALUES ('winLonghornGuest', 6)
INSERT INTO #guestOsVersionTable VALUES ('winMeGuest', 4.9)
INSERT INTO #guestOsVersionTable VALUES ('winNetBusinessGuest', 5.2)
INSERT INTO #guestOsVersionTable VALUES ('winNetDatacenter64Guest', 5.2)
INSERT INTO #guestOsVersionTable VALUES ('winNetDatacenterGuest', 5.2)
INSERT INTO #guestOsVersionTable VALUES ('winNetEnterprise64Guest', 5.2)
INSERT INTO #guestOsVersionTable VALUES ('winNetEnterpriseGuest', 5.2)
INSERT INTO #guestOsVersionTable VALUES ('winNetStandard64Guest', 5.2)
INSERT INTO #guestOsVersionTable VALUES ('winNetStandardGuest', 5.2)
INSERT INTO #guestOsVersionTable VALUES ('winNetWebGuest', 5.2)
INSERT INTO #guestOsVersionTable VALUES ('winNTGuest', 4)
INSERT INTO #guestOsVersionTable VALUES ('winVista64Guest', 6)
INSERT INTO #guestOsVersionTable VALUES ('winVistaGuest', 6)
INSERT INTO #guestOsVersionTable VALUES ('winXPHomeGuest', 5.1)
INSERT INTO #guestOsVersionTable VALUES ('winXPPro64Guest', 5.1)
INSERT INTO #guestOsVersionTable VALUES ('winXPProGuest', 5.1)
INSERT INTO #guestOsVersionTable VALUES ('windows8_64Guest', 6.2)
INSERT INTO #guestOsVersionTable VALUES ('windows8Guest', 6.2)
INSERT INTO #guestOsVersionTable VALUES ('windows8Server64Guest', 6.2)

UPDATE  #machinesWithWindowsOS
SET operatingSystemVersion = GOS.versionNumber
FROM #machinesWithWindowsOS MA
JOIN #guestOsVersionTable GOS ON MA.operatingSystemVersion = GOS.name
WHERE CHARINDEX('w', MA.operatingSystemVersion) = 1

DECLARE @supportedVersion FLOAT = 6.2

DELETE #machinesWithWindowsOS
FROM #machinesWithWindowsOS
WHERE operatingSystemVersion = 'New' OR operatingSystemVersion >= @supportedVersion

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

IF EXISTS (SELECT clientId FROM #machinesWithWindowsOS WHERE typeOfMachine = @machineTypeCS)
BEGIN

	DECLARE	csWarningCur CURSOR STATIC FOR 
	SELECT DISTINCT C.id, C.name, CS.operatingSystemName
	FROM #machinesWithWindowsOS CS
	JOIN APP_Client C ON CS.clientId = C.id
	WHERE typeOfMachine = @machineTypeCS
	ORDER BY C.id

	OPEN csWarningCur
	SET @openCSCurFlag = @openCSCurFlag + 1
	FETCH NEXT FROM csWarningCur INTO @clientId, @clientName, @clientOS
	
	SET	@errorCode = @@ERROR 
	IF	@errorCode != 0 GOTO CX_EXIT


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

		IF @clientCSUpgCnt = 1
		BEGIN
			IF @retCode = 0
				SET @retCode = 1
		
			PRINT @WarningTag + ' You cannot upgrade the CommServe because it has an unsupported operating system installed.'
			PRINT @AdviceTag + ' Before the upgrade, ensure it is running on a supported operating system. Refer to http://documentation.commvault.com/commvault/v11/article?p=system_requirements/commserve.htm for a list of supported operating systems.' + char(13)
			PRINT  @HeaderTag + ' Client Name' + @colSeparator + 'Operating System' 
		END
	
		PRINT @DataTag + @clientName + @colSeparator + @clientOS
	
		FETCH NEXT FROM csWarningCur INTO @clientId, @clientName, @clientOS
		SET	@errorCode = @@ERROR 
		IF	@errorCode != 0 GOTO CX_EXIT 
	END
END

DECLARE	clientWarningCur CURSOR STATIC FOR 
	SELECT DISTINCT C.id, C.name, MA.operatingSystemName
	FROM #machinesWithWindowsOS MA
	JOIN APP_Client C ON MA.clientId = C.id
	WHERE typeOfMachine = @machineTypeMA AND MA.clientId NOT IN (SELECT t_clientId FROM tmpClientWithWebServerClient)
	ORDER BY c.name

OPEN clientWarningCur
SET @openCurFlag = @openCurFlag + 1
FETCH NEXT FROM clientWarningCur INTO @clientId, @clientName, @clientOS

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

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

	IF @clientMAUpgCnt = 1
	BEGIN
		IF @retCode = 0
			SET @retCode = 1
	
		PRINT  @WarningTag + ' The following MediaAgents do not support upgrade with the current operating system installed.'
		PRINT  @AdviceTag + ' Before the upgrade, you must upgrade the operating system. Refer to http://documentation.commvault.com/commvault/v11/article?p=system_requirements/ma.htm for a list of supported operating systems.' + char(13)
		PRINT  @HeaderTag + ' Client Name' + @colSeparator + 'Operating System' 
	END
	
	PRINT @DataTag + @clientName + @colSeparator + @clientOS

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

DECLARE	clientWebServerWarningCur CURSOR STATIC FOR 
	SELECT DISTINCT C.id, C.name, MA.operatingSystemName
	FROM #machinesWithWindowsOS MA
	JOIN APP_Client C ON MA.clientId = C.id
	JOIN tmpClientWithWebServerClient WS ON MA.clientId = WS.t_clientId
	WHERE typeOfMachine = @machineTypeMA
	ORDER BY c.name

OPEN clientWebServerWarningCur
SET @openWebServerCurFlag = @openWebServerCurFlag + 1
FETCH NEXT FROM clientWebServerWarningCur INTO @clientId, @clientName, @clientOS

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 MediaAgents need to be upgraded immediately but do not support upgrade with the current operating system installed.'
		PRINT  @AdviceTag + ' Before the upgrade, you must upgrade the operating system. Refer to http://documentation.commvault.com/commvault/v11/article?p=system_requirements/ma.htm for a list of supported operating systems.' + char(13)
		PRINT  @HeaderTag + ' Client Name' + @colSeparator + 'Operating System' 
	END
	
	PRINT @DataTag + @clientName + @colSeparator + @clientOS

	FETCH NEXT FROM clientWebServerWarningCur INTO @clientId, @clientName, @clientOS
	SET	@errorCode = @@ERROR 
	IF	@errorCode != 0 GOTO CX_EXIT 
END

CX_EXIT:
DROP TABLE #guestOsVersionTable 
DROP TABLE #machinesWithWindowsOS 

IF @openCurFlag > 0
BEGIN
	CLOSE clientWarningCur
	DEALLOCATE clientWarningCur
END

IF @openCSCurFlag > 0
BEGIN
	CLOSE csWarningCur
	DEALLOCATE csWarningCur
END


IF @openWebServerCurFlag > 0
BEGIN
	CLOSE clientWebServerWarningCur
	DEALLOCATE clientWebServerWarningCur
END


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

RETURN