-- This script checks whether DB is on 9.0 or 10.0 or 11.0 release
-- This script checks whether Service pack level on 9.0 is SP14 and higher
-- This script checks whether Service pack level on 10.0 is SP11 and higher

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 @CurRelDBVersionId INTEGER					--11.0
SET 	@CurRelDBVersionId = 16

DECLARE @LastRelDBVersionId INTEGER					--10.0
SET 	@LastRelDBVersionId = 15

DECLARE @PreLastRelDBVersionId INTEGER				--9.0
SET 	@PreLastRelDBVersionId = 14

DECLARE @SupportedSPLevelForLastRel INTEGER			--10.0
SET 	@SupportedSPLevelForLastRel = 11

DECLARE @SupportedSPLevelForPreLastRel INTEGER		--9.0
SET 	@SupportedSPLevelForPreLastRel = 14

DECLARE @CurrentDBVersionId INTEGER
SET 	@CurrentDBVersionId = 0

DECLARE @CurrentSPLevel INTEGER
SET 	@CurrentSPLevel = 0

DECLARE @CurrentDBVersion VARCHAR(128)
SET 	@CurrentDBVersion = 0

DECLARE @errorCode INTEGER 
SET		@errorCode = 0

PRINT @DescTag + ' Check for supported version and minimum Service Pack requirement for the CommServe computer'

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE id = OBJECT_ID(N'[dbo].[SimAllGalaxyRel]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
	SELECT TOP 1 @CurrentDBVersionId = id, @CurrentDBVersion = release FROM SimAllGalaxyRel ORDER BY id DESC
END
ELSE
BEGIN
	SET @errorCode = -1
	GOTO CX_EXIT
END

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

IF @CurrentDBVersionId != @PreLastRelDBVersionId AND @CurrentDBVersionId != @LastRelDBVersionId AND @CurrentDBVersionId != @CurRelDBVersionId
BEGIN
	SET @errorCode = -2
	GOTO CX_EXIT
END
	
SET	@errorCode	=	@@ERROR 
IF	@errorCode != 0 GOTO CX_EXIT 

IF EXISTS (SELECT * FROM SYSOBJECTS WHERE id = OBJECT_ID(N'[dbo].[simInstalledPackages]') and OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
	SELECT TOP 1 @CurrentSPLevel = HighestSP FROM simInstalledPackages WITH (NOLOCK) WHERE ClientId = 2 AND simPackageID = 801
END	
ELSE
BEGIN
	SET @errorCode = -4
	GOTO CX_EXIT
END

SET	@errorCode	=	@@ERROR 
IF	@errorCode != 0 GOTO CX_EXIT 
	
IF (@CurrentDBVersionId = @PreLastRelDBVersionId AND @CurrentSPLevel < @SupportedSPLevelForPreLastRel)	--Check SP level for version 9
BEGIN
	SET @errorCode = -5
	GOTO CX_EXIT
END

IF (@CurrentDBVersionId = @LastRelDBVersionId AND @CurrentSPLevel < @SupportedSPLevelForLastRel)		--Check SP level for Version 10
BEGIN
	SET @errorCode = -6
	GOTO CX_EXIT
END

CX_EXIT: 
	IF @errorCode != 0 
	BEGIN
		IF @errorCode = -1
		BEGIN
			PRINT @ErrorTag + ' Failed to check the software version. SimAllGalaxyRel table missing.'
		END
		ELSE IF @errorCode = -2
		BEGIN
			PRINT  @ErrorTag + ' Current version ' + @CurrentDBVersion + ' is not supported for upgrade to version 11.'
		END
		ELSE IF @errorCode = -3
		BEGIN
			PRINT  @ErrorTag + ' Failed to check current service pack information. PatchInfo table missing.'
		END
		ELSE IF @errorCode = -4
		BEGIN
			PRINT  @ErrorTag + ' Failed to check current service pack information. simInstalledPackages table missing.'		
		END		
		ELSE IF @errorCode = -5
		BEGIN
			PRINT  @ErrorTag + ' Current Service Pack level [SP' + CAST(@CurrentSPLevel AS VARCHAR(10)) + '] is not supported for upgrade to version 11.'
			PRINT  @AdviceTag + ' You must install Service Pack [SP' + CAST(@SupportedSPLevelForPreLastRel AS VARCHAR(10)) + '] before upgrading to version 11.'
		END

		ELSE IF @errorCode = -6
		BEGIN
			PRINT  @ErrorTag + ' Current Service Pack level [SP' + CAST(@CurrentSPLevel AS VARCHAR(10)) + '] is not supported for upgrade to version 11.'
			PRINT  @AdviceTag + ' You must install Service Pack [SP' + CAST(@SupportedSPLevelForLastRel AS VARCHAR(10)) + '] before upgrading to version 11.'
		END		
		ELSE
		BEGIN	
			PRINT  @ErrorTag + ' Failed to check the software version and service pack information.'
		END
			
		SELECT 2
	END
	ELSE
	BEGIN
		SELECT 0
	END
			
	RETURN