-- Block DB Upgrade if CS has any 8.0 style firewall configuration files and warn if clients are still using 8.0 style firewall configuration files

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 @ScriptName NVARCHAR(MAX)
DECLARE @errorCode  INT
DECLARE @openCurFlag    INT
DECLARE @clientName NVARCHAR(MAX)
DECLARE @clientId INT
DECLARE @clientPlatform	INT
DECLARE @oldStyleClients INT
DECLARE @retCode INT
DECLARE @failUpgrade INT

SET @ScriptName     = 'Old style firewall configuration check: '
SET @errorCode      = 0
SET @openCurFlag    = 0
SET @retCode        = 0
SET @failUpgrade    = 0

PRINT @DescTag + ' Check if clients are using 8.0 style firewall configration files '

--Below query checks if there are any clients using old (8.0) style firewall configuration files.
--This causes the CommServe upgrade to fail if Commserver is using old style firewall configuration files.
--It just warns if a client is using a old style firewall configuration files

DECLARE oldStyleFWClients CURSOR STATIC FOR 
SELECT DISTINCT C.id, C.name, sum(P.platformType) as platformType
FROM App_Client C, APP_ClientProp prop, APP_Platform p
WHERE 
	prop.attrName='Using Old FW Config' AND
	prop.attrVal = '1' AND 
	prop.componentNameId = C.id AND
	C.id = P.clientId AND
	((C.status & 2) <> 2) -- CV_STATUS_UNINSTALLED (Ignore deconfigured clients)
GROUP BY C.id, C.name
ORDER BY platformType DESC

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

SET @openCurFlag = 1
SET @oldStyleClients = 0
OPEN oldStyleFWClients
FETCH NEXT FROM oldStyleFWClients INTO @clientId, @clientName, @clientPlatform

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

WHILE @@FETCH_STATUS = 0 
BEGIN
	IF (@clientPlatform & 3) > 0
    BEGIN
		if @failUpgrade = 0
		BEGIN
			PRINT  @ErrorTag + ' Detected Version 8 firewall configuration files (FwPeers.txt, FwHosts.txt and  FwPorts.txt) on the CommServe/MediaAgent computer.'
			PRINT  @AdviceTag + ' You must upgrade the firewall configuration to Version 10 firewall on the CommServe computer. After the upgrade, delete the Version 8 firewall configuration files and restart services on the CommServe/MediaAgent computer.'
			PRINT  @HeaderTag + ' Client Name'
			SET @failUpgrade = 1
		END
    END
    ELSE
    BEGIN
        IF @oldStyleClients = 0
        BEGIN
            PRINT  @WarningTag + ' Detected Version 8 firewall configuration files (FwPeers.txt, FwHosts.txt and  FwPorts.txt) on the following Clients. After the CommServe upgrade, communication with these clients might fail.'
            PRINT  @AdviceTag + ' You must upgrade the firewall configuration to Version 10 firewall on the client computers.'
            PRINT  @HeaderTag + ' Client Name'
            SET @oldStyleClients = 1
        END
    END 

    PRINT @DataTag + @clientName
    SET @retCode        = 1
    FETCH NEXT FROM oldStyleFWClients INTO @clientId, @clientName, @clientPlatform
    SET @errorCode = @@ERROR 
    IF  @errorCode != 0 GOTO CX_EXIT 
END

IF EXISTS( SELECT DISTINCT C.name FROM App_Client C, APP_Platform P WHERE releaseId = 14 AND ((C.status & 2) <> 2) AND C.id = P.clientId AND p.platformType = 2)
BEGIN
	PRINT  @WarningTag + ' Detected V9 Media Agent(s)'
	PRINT  @AdviceTag + ' Make sure V9 Media Agents are configured to use new style firewall configuration, . If there are old V8 style firewall configuration files, delete the files (FwHosts.txt, FwPeers.txt, FwPorts.txt ) and restart Commvault services.'
	SET @retCode        = 1
END

CX_EXIT:

IF @openCurFlag > 0 
BEGIN
    CLOSE oldStyleFWClients
    DEALLOCATE oldStyleFWClients
END

IF @errorCode != 0 OR @failUpgrade != 0 
BEGIN   
    IF @errorCode != 0
        PRINT @ScriptName + 'failed with SQL Error [' + CAST(@errorCode AS VARCHAR(20)) + ']'   
    SELECT 2
END
ELSE
BEGIN   
    PRINT @ScriptName + 'passed'
    SELECT @retCode
END

RETURN