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 @errCnt		INT 
DECLARE @openCurFlag	INT
DECLARE @name		NVARCHAR(MAX)
DECLARE @name2		NVARCHAR(MAX)

SET @ScriptName		= 'SIM static data 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

PRINT @DescTag + ' Check for static SIM data'

-- For each static table, verify that all the rows in the table would be present in the new table.
-- 1) simBinarySet
DECLARE	simBinarySetCur CURSOR STATIC FOR 
SELECT DISTINCT name FROM simBinarySet WHERE id NOT IN (select id from simBinarySet_DBU)

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

SET @errCnt = 0
OPEN simBinarySetCur 
SET @openCurFlag = @openCurFlag + 1
FETCH NEXT FROM simBinarySetCur INTO @name

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

WHILE @@FETCH_STATUS = 0 
BEGIN
	IF @retCode = 0
		SET @retCode = 2

	SET @errCnt = @errCnt + 1

	IF @errCnt = 1
	BEGIN
		PRINT @ErrorTag + ' simBinarySet table contains the following records which are not supported in the new release'
		PRINT  @HeaderTag + 'Name'
	END

	PRINT @DataTag + @name

	FETCH NEXT FROM simBinarySetCur INTO @name
	SET	@errorCode = @@ERROR 
	IF	@errorCode != 0 GOTO CX_EXIT 
END

-- 2) simOEM
DECLARE	simOEMCur CURSOR STATIC FOR 
SELECT DISTINCT OEMName FROM simOEM WHERE OEMId NOT IN (select OEMId from simOEM_DBU)

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

SET @errCnt = 0
OPEN simOEMCur 
SET @openCurFlag = @openCurFlag + 1
FETCH NEXT FROM simOEMCur INTO @name

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

WHILE @@FETCH_STATUS = 0 
BEGIN
	IF @retCode = 0
		SET @retCode = 2

	SET @errCnt = @errCnt + 1

	IF @errCnt = 1
	BEGIN
		PRINT @ErrorTag + ' simOEM table contains the following records which are not supported in the new release'
		PRINT  @HeaderTag + 'OEM Name'
	END

	PRINT @DataTag + @name

	FETCH NEXT FROM simOEMCur INTO @name
	SET	@errorCode = @@ERROR 
	IF	@errorCode != 0 GOTO CX_EXIT 
END

-- 3) simMiniBrand
DECLARE	simMiniBrandCur CURSOR STATIC FOR 
SELECT DISTINCT name FROM simMiniBrand WHERE MiniBrandId NOT IN (select MiniBrandId from simMiniBrand_DBU)

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

SET @errCnt = 0
OPEN simMiniBrandCur 
SET @openCurFlag = @openCurFlag + 1
FETCH NEXT FROM simMiniBrandCur INTO @name

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

WHILE @@FETCH_STATUS = 0 
BEGIN
	IF @retCode = 0
		SET @retCode = 2

	SET @errCnt = @errCnt + 1

	IF @errCnt = 1
	BEGIN
		PRINT @ErrorTag + ' simMiniBrand table contains the following records which are not supported in the new release'
		PRINT  @HeaderTag + 'Mini Brand Name'
	END

	PRINT @DataTag + @name

	FETCH NEXT FROM simMiniBrandCur INTO @name
	SET	@errorCode = @@ERROR 
	IF	@errorCode != 0 GOTO CX_EXIT 
END

CX_EXIT:

IF @openCurFlag > 2
BEGIN
	CLOSE simMiniBrandCur
	DEALLOCATE simMiniBrandCur
END
IF @openCurFlag > 1
BEGIN
	CLOSE simOEMCur
	DEALLOCATE simOEMCur
END
IF @openCurFlag > 0
BEGIN
	CLOSE simBinarySetCur
	DEALLOCATE simBinarySetCur
END

IF @errorCode != 0
BEGIN
	PRINT 'The CommServe database contains deprecated binary, OS, or OEM type information. ' + @ScriptName + 'failed.'
	SELECT 2
END
ELSE
BEGIN
	IF @retCode = 0
		PRINT @ScriptName + 'succeeded.'
	SELECT @retCode
END

RETURN