SET NOCOUNT ON 
SET QUOTED_IDENTIFIER ON	-- needed for V10
DECLARE @ErrorTag	NVARCHAR(20) = '###ERROR###'
DECLARE @WarningTag	NVARCHAR(20) = '###WARNING###'
DECLARE @AdviceTag	NVARCHAR(20) = '###ADVICE###'
DECLARE @HeaderTag	NVARCHAR(20) = '###HEADER###'
DECLARE @DataTag	NVARCHAR(20) = '###DATA###'
DECLARE @DescTag 	NVARCHAR(20) = '###DESCRIPTION###'
DECLARE @Output		NVARCHAR(MAX) = ''
DECLARE @colSep		NVARCHAR(20) = ':,:'
DECLARE @ScriptName	NVARCHAR(255) = 'Verifying Smart Client Group Rules for upgrade: '

DECLARE @errorCode		INTEGER  = 0
DECLARE @badSCGCount	INTEGER  = 0
DECLARE @retCode		INTEGER  = 0


PRINT @DescTag + ' Verifying whether Smart Client Group Rules can be upgraded '

IF OBJECT_ID('tempdb.dbo.#RuleTree') IS NOT NULL
	DROP TABLE #RuleTree

CREATE TABLE #RuleTree (
	id			INT IDENTITY(1,1),
	scgRuleId	INT,
	groupId		INT,
	groupName	NVARCHAR(255),
	masterOp	INT,
	[group]		INT,
	number		INT,
	filterID	INT,
	propID		INT,
	propType	INT,
	secValue	VARCHAR(MAX),
	value		VARCHAR(MAX),
	op			INT,
	funcName	VARCHAR(256),
	ruleXML		XML,
	newRuleXML	XML,
	level		INT,
	PRIMARY KEY (scgRuleId, [group], number, id)
)

INSERT INTO #RuleTree(scgRuleId, groupId, groupName, masterOp, [group], number, filterID, propID, propType, secValue, value, op, funcName, ruleXML, level)
	SELECT
		sr.id,
		sr.scgId,
		cg.name,
		r.value('../../../../@op', 'int') masterOp,
		gc.value('for $a in . return 1+count($a/../*[. << $a])','int') [group],
		rc.value('for $a in . return 1+count($a/../*[. << $a])','int') number,
		r.value('@filterID', 'int') filterID,
		r.value('@propID', 'int') propID,
		r.value('@propType', 'int') propType,
		r.value('@secValue', 'varchar(max)') secValue,
		CASE
			WHEN r.value('@propType', 'int') = 6 THEN CAST(CAST(r.value('@value', 'varchar(max)') AS XML) AS VARCHAR(MAX))	-- remove XML Header
			ELSE r.value('@value', 'varchar(max)') 
		END value,
		r.value('../../@op', 'int') op,
		p.funcName,
		CAST(sr.ruleXML AS NVARCHAR(MAX)),
		4
	FROM
		App_SCGRule sr
		CROSS APPLY sr.ruleXML.nodes('/scgRule/rules/rule/rules/rule') t(r)
		LEFT OUTER JOIN App_SCGProperty p WITH(READUNCOMMITTED) ON		-- propID < 10000	CS Supplied SCG Function
			p.id = r.value('@propID', 'int')
		LEFT OUTER JOIN APP_ClientGroup cg ON
			cg.id = sr.scgId
		CROSS APPLY r.nodes('..') rc(rc)			-- rule count / number
		CROSS APPLY r.nodes('../../..') gc(gc)		-- rule group count / number
	WHERE
		p.id IS NULL
	UNION
	SELECT
		sr.id,
		sr.scgId,
		cg.name,
		r.value('../../@op', 'int') masterOp,
		1 [group],	-- only one grouping
		rc.value('for $a in . return 1+count($a/../*[. << $a])','int') number,
		r.value('@filterID', 'int') filterID,
		r.value('@propID', 'int') propID,
		r.value('@propType', 'int') propType,
		r.value('@secValue', 'varchar(max)') secValue,
		CASE
			WHEN r.value('@propType', 'int') = 6 THEN CAST(CAST(r.value('@value', 'varchar(max)') AS XML) AS VARCHAR(MAX))	-- remove XML Header
			ELSE r.value('@value', 'varchar(max)') 
		END value,
		r.value('../../@op', 'int') op,		-- same as masterOp
		p.funcName,
		CAST(sr.ruleXML AS NVARCHAR(MAX)),
		2
	FROM
		App_SCGRule sr
		CROSS APPLY sr.ruleXML.nodes('/scgRule/rules/rule') t(r)
		LEFT OUTER JOIN App_SCGProperty p WITH(READUNCOMMITTED) ON		-- propID < 10000	CS Supplied SCG Function
			p.id = r.value('@propID', 'int')
		LEFT OUTER JOIN APP_ClientGroup cg ON
			cg.id = sr.scgId
		CROSS APPLY r.nodes('..') rc(rc)			-- rule count / number
	WHERE
		r.exist('rules/rule') = 0
		AND p.id IS NULL

SELECT @errorCode   = @@ERROR, @badSCGCount = @@ROWCOUNT
IF  @errorCode != 0 GOTO CX_EXIT 

--No problem SCG found
IF @badSCGCount = 0 
BEGIN 
	SET @retCode = 0
	PRINT 'All configured Smart Client Group Rules are ugpradable.'
	SET @errorCode = 0

	GOTO CX_EXIT
END 

--Problem SCGs found:
SET @retCode = 2
PRINT @ErrorTag + ' Found one or more Smart Client Group Rules that might encounter problems during the upgraded.'

--Print Header
PRINT @HeaderTag + ' SCG Rule Id' + @colSep + 'Client Group Id' + @colSep + 'Client Group Name' + @colSep + 'Invalid SCG Property Id' + ':'
PRINT '---------------------------------------------------------------------------------------------------------'

DECLARE @srId INT
DECLARE @grpId INT
DECLARE @grpName NVARCHAR(255)
DECLARE @spId INT
    
DECLARE Cur CURSOR STATIC FOR 
SELECT scgRuleId, groupId, groupName, propID from #RuleTree

OPEN Cur 
FETCH NEXT FROM  Cur INTO  @srId, @grpId, @grpName, @spId

WHILE @@FETCH_STATUS = 0 
BEGIN 
    PRINT @DataTag + ' ' + CAST(@srId AS VARCHAR(12)) + @colSep + CAST(@grpId AS VARCHAR(12))  + @colSep + @grpName + @colSep +CAST(@spId AS VARCHAR(12))

	FETCH NEXT FROM  Cur INTO  @srId, @grpId, @grpName, @spId
END 

CLOSE       Cur 
DEALLOCATE  Cur 

SET @errorCode = 1
--Print Advice
PRINT @AdviceTag + ' Before the upgrade, you must correct the Smart Client Group Rules.'

CX_EXIT: 

IF OBJECT_ID('tempdb.dbo.#RuleTree') IS NOT NULL
	DROP TABLE #RuleTree

IF @errorCode != 0
    SELECT 2 exitCode
ELSE
    SELECT @retCode exitCode

RETURN