CREATE OR ALTER PROCEDURE [MIG_ANZ_GENERATE_ENTRIES_IN_MIG_ANZ_COMPLEXITIES_Sp] 
AS BEGIN

	DELETE FROM MIG_ANZ_COMPLEXITIES

	DECLARE @SQLString NVARCHAR(MAX)
	DECLARE @COUNT INT

	DECLARE @COUNT_VAULTS INT
	DECLARE @COUNT_ROUTE INT
	DECLARE @COUNT_EO INT
	DECLARE @COUNT_MAPPING_RULES INT
	DECLARE @COUNT_MASK_RULES INT
	DECLARE @COUNT_SYNC_RULES INT
	DECLARE @COUNT_SEC_RULES INT
	DECLARE @COUNT_FILE_IMAGES INT
	DECLARE @COUNT_TEXT_DATA_MAPPING_RULES INT
	DECLARE @COUNT_TEXT_DATA_ITEM_ROUTING INT
	DECLARE @COUNT_TEXT_DATA_EO_EXT_CMNT INT
	DECLARE @COUNT_TEXT_DATA_EO_IMPACT_ANALYSIS INT
	DECLARE @COUNT_TEXT_DATA_EO_REMARKS INT
	DECLARE @COUNT_TEXT_DATA_EO_REASON_FOR_CHANGE INT
	DECLARE @COUNT_TEXT_DATA_EO_DUE_EVENTS INT
	DECLARE @COUNT_TEXT_DATA_RT_DUE_EVENTS INT
	DECLARE @COUNT_TEXT_DATA_EO_RT_TOOL_PRM INT
	DECLARE @COUNT_TEXT_DATA_RT_TOOL_PRM INT
	DECLARE @COUNT_ITEM INT
	DECLARE @COUNT_ITEM_PS_DEF INT
	DECLARE @COUNT_DOC INT
	DECLARE @COUNT_DOC_PS INT
	DECLARE @COUNT_FOLDER INT
	DECLARE @COUNT_FOLDER_PS INT
	DECLARE @COUNT_DATASET INT
	DECLARE @COUNT_DOC_DATASET INT
	DECLARE @COUNT_ITEM_DOC INT
	DECLARE @COUNT_FOLDER_ITEM INT
	DECLARE @COUNT_FOLDER_DOC INT
	DECLARE @COUNT_ITM_TRNS_HIST INT
	--DECLARE @COUNT_EO INT
	DECLARE @COUNT_EO_ROUTE_STEP INT
	DECLARE @COUNT_EO_ROUTE_LINE INT
	DECLARE @COUNT_EO_HISTORY INT

	DECLARE @COUNTER INT
	SET @COUNTER = 0

	INSERT INTO MIG_ANZ_COMPLEXITIES
	(
		[GROUP],
		[TABLE],
		[FIELD],
		[SQL_QUERY],
		[COMMENTS]
	)
	VALUES
	(
		'COMPLEX_TOPICS',
		'SITE',
		'',
		'SELECT @COUNT = COUNT(*) FROM SITE',
		'Multiple vaults means more set-up is needed and files need to be moved to all those vaults.'
	),
	(
		'COMPLEX_TOPICS',
		'ROUTE',
		'',
		'SELECT @COUNT = COUNT(*) FROM ROUTE',
		''
	),
	(
		'COMPLEX_TOPICS',
		'EO',
		'',
		'SELECT @COUNT = COUNT(*) FROM EO',
		'Business Process module has significant data-model changes. Prior to dumping, a pre-generation program ' +
			+ 'is executed which can take good amount of time depending on number of records.'
	),
	(
		'COMPLEX_TOPICS',
		'MAPPING_RULES',
		'',
		'SELECT @COUNT = COUNT(*) FROM MAPPING_RULES',
		'These contains field names in it; and as field names are changed in "pd", it requires user to correct the ' +
			+ 'field names appropriately. DCA Utility for PLM has provision to specify correct values.'
	),
	(
		'COMPLEX_TOPICS',
		'MASK_RULES',
		'',
		'SELECT @COUNT = COUNT(*) FROM MASK_RULES',
		'These contains field names in it; and as field names are changed in "pd", it requires user to correct the ' +
			+ 'field names appropriately. DCA Utility for PLM has provision to specify correct values.'
	),
	(
		'COMPLEX_TOPICS',
		'SYNC_RULES',
		'',
		'SELECT @COUNT = COUNT(*) FROM SYNC_RULES',
		'These contains field names in it; and as field names are changed in "pd", it requires user to correct the ' +
			+ 'field names appropriately. DCA Utility for PLM has provision to specify correct values.'
	),
	(
		'COMPLEX_TOPICS',
		'SEC_RULES',
		'',
		'SELECT @COUNT = COUNT(*) FROM SEC_RULES',
		'These contains field names in it; and as field names are changed in "pd", it requires user to correct the ' +
			+ 'field names appropriately. DCA Utility for PLM has provision to specify correct values.'
	),
	(
		'COMPLEX_TOPICS',
		'FILE_IMAGES',
		'',
		'SELECT @COUNT = COUNT(*) FROM FILE_IMAGES',
		'Thumbnails are dumped and loaded using separate process (not via DCA application). This gives an idea on how ' +
			+ 'much time it may take to load in target.'
	),
	(
		'COMPLEX_TOPICS',
		'ROUTE',
		'',
		'SELECT @COUNT = COUNT(*) FROM ROUTE',
		'Workflow Templates gives an idea on how many workflow templates a customer is using.'
	),
	(
		'TEXT_DATA',
		'MAPPING_RULES',
		'EXTRA_DATA',
		'SELECT @COUNT = COUNT(*) FROM MAPPING_RULES WHERE LEN(MAPPING_RULES.EXTRA_DATA) > 0',
		''
	),
	(
		'TEXT_DATA',
		'ITEM_ROUTING',
		'TEXT',
		'SELECT @COUNT = COUNT(*) FROM ITEM_ROUTING WHERE LEN(ITEM_ROUTING.TEXT) > 0',
		''
	),
	(
		'TEXT_DATA',
		'EO_EXT_CMNT',
		'TEXT_COMMENT',
		'SELECT @COUNT = COUNT(*) FROM EO_EXT_CMNT WHERE LEN(EO_EXT_CMNT.TEXT_COMMENT) > 0',
		''
	),
	(
		'TEXT_DATA',
		'EO',
		'IMPACT_ANALYSIS',
		'SELECT @COUNT = COUNT(*) FROM EO WHERE LEN(EO.IMPACT_ANALYSIS) > 0',
		''
	),
	(
		'TEXT_DATA',
		'EO',
		'REMARKS',
		'SELECT @COUNT = COUNT(*) FROM EO WHERE LEN(EO.REMARKS) > 0',
		''
	),
	(
		'TEXT_DATA',
		'EO',
		'REASON_FOR_CHANGE',
		'SELECT @COUNT = COUNT(*) FROM EO WHERE LEN(EO.REASON_FOR_CHANGE) > 0',
		''
	),
	(
		'TEXT_DATA',
		'EO_DUE_EVENTS',
		'MESSAGE',
		'SELECT @COUNT = COUNT(*) FROM EO_DUE_EVENTS WHERE LEN(EO_DUE_EVENTS.MESSAGE) > 0',
		''
	),
	(
		'TEXT_DATA',
		'RT_DUE_EVENTS',
		'MESSAGE',
		'SELECT @COUNT = COUNT(*) FROM RT_DUE_EVENTS WHERE LEN(RT_DUE_EVENTS.MESSAGE) > 0',
		''
	),
	(
		'TEXT_DATA',
		'EO_RT_TOOL_PRM',
		'PRM_VALUE',
		'SELECT @COUNT = COUNT(*) FROM EO_RT_TOOL_PRM WHERE TOOL_KEY = ''WkFl_Distribute'' AND LEN(PRM_VALUE) > 0',
		''
	),
	(
		'TEXT_DATA',
		'RT_TOOL_PRM',
		'PRM_VALUE',
		'SELECT @COUNT = COUNT(*) FROM RT_TOOL_PRM WHERE TOOL_KEY = ''WkFl_Distribute'' AND LEN(PRM_VALUE) > 0',
		''
	),
	(
		'MAIN_TABLES',
		'ITEM (OBJECT_TYPE = 0)',
		'',
		'SELECT @COUNT = COUNT(*) FROM ITEM WHERE OBJECT_TYPE = 0',
		'Records of type ITEM.'
	),
	(
		'MAIN_TABLES',
		'ITEM_PS_DEF (OBJECT_TYPE = 0)',
		'',
		'SELECT @COUNT = COUNT(*) ' +
			+ 'FROM ITEM_PS_DEF ' +
			+ 'LEFT JOIN ITEM ' +
			+ '  ON ITEM.ITEM_KEY = ITEM_PS_DEF.PARENT_ITEM ' +
			+ ' AND ITEM.REVISION = ITEM_PS_DEF.PARENT_REVISION ' +
			+ ' AND ITEM.OBJECT_TYPE = 0 ' +
			+ 'WHERE CHILD_ITEM IN (SELECT CI.ITEM_KEY FROM ITEM CI WHERE CI.OBJECT_TYPE = 0) ',
		'Records for ITEM object type.'
	),
	(
		'MAIN_TABLES',
		'DOC (OBJECT_TYPE = 8)',
		'',
		'SELECT @COUNT = COUNT(*) FROM DOC WHERE OBJECT_TYPE = 8',
		'Records of type Document.'
	),
	(
		'MAIN_TABLES',
		'DOC_PS',
		'',
		'SELECT @COUNT = COUNT(*) FROM DOC_PS',
		''
	),
	(
		'MAIN_TABLES',
		'FOLDER',
		'',
		'SELECT @COUNT = COUNT(*) FROM FOLDER',
		''
	),
	(
		'MAIN_TABLES',
		'FOLDER_PS',
		'',
		'SELECT @COUNT = COUNT(*) FROM FOLDER_PS',
		''
	),
	(
		'MAIN_TABLES',
		'DATASET',
		'',
		'SELECT @COUNT = COUNT(*) FROM DATASET',
		''
	),
	(
		'MAIN_TABLES',
		'ITEM_PS_DEF',
		'',
		'SELECT @COUNT = COUNT(*) FROM ITEM_PS_DEF',
		''
	),
	(
		'MAIN_TABLES',
		'DOC_DATASET',
		'',
		'SELECT @COUNT = COUNT(*) FROM DOC_DATASET',
		''
	),
	(
		'MAIN_TABLES',
		'ITEM_DOC',
		'',
		'SELECT @COUNT = COUNT(*) FROM ITEM_DOC',
		''
	),
	(
		'MAIN_TABLES',
		'FOLDER_ITEM',
		'',
		'SELECT @COUNT = COUNT(*) FROM FOLDER_ITEM',
		''
	),
	(
		'MAIN_TABLES',
		'FOLDER_DOC',
		'',
		'SELECT @COUNT = COUNT(*) FROM FOLDER_DOC',
		''
	),
	(
		'MAIN_TABLES',
		'ITM_TRNS_HIST',
		'',
		'SELECT @COUNT = COUNT(*) FROM ITM_TRNS_HIST',
		''
	),
	(
		'MAIN_TABLES',
		'EO_ROUTE_STEP',
		'',
		'SELECT @COUNT = COUNT(*) FROM EO_ROUTE_STEP',
		''
	),
	(
		'MAIN_TABLES',
		'EO_ROUTE_LINE',
		'',
		'SELECT @COUNT = COUNT(*) FROM EO_ROUTE_LINE',
		''
	),
	(
		'MAIN_TABLES',
		'EO_HISTORY',
		'',
		'SELECT @COUNT = COUNT(*) FROM EO_HISTORY',
		''
	),
	(
		'UNSUPPORTED_OBJECT_TYPES',
		'ITEM (OBJECT_TYPE <> 0)',
		'',
		'SELECT @COUNT = COUNT(*) FROM ITEM WHERE OBJECT_TYPE <> 0',
		'Records of type other than ITEM.'
	),
	(
		'UNSUPPORTED_OBJECT_TYPES',
		'ITEM_PS_DEF (OBJECT_TYPE <> 0)',
		'',
		'SELECT SUM(T.SUBTOTAL) ' +
			+ 'FROM ' +
			+ '( ' +
			+ '	SELECT COUNT(*) SUBTOTAL ' +
			+ '	FROM ITEM_PS_DEF ' +
			+ '	JOIN ITEM ' +
			+ '	ON ITEM.ITEM_KEY = ITEM_PS_DEF.PARENT_ITEM ' +
			+ '	AND ITEM.REVISION = ITEM_PS_DEF.PARENT_REVISION ' +
			+ '	AND ITEM.OBJECT_TYPE = 0 ' +
			+ '	WHERE CHILD_ITEM IN (SELECT CI.ITEM_KEY FROM ITEM CI WHERE CI.OBJECT_TYPE <> 0) ' +
			+ '	UNION ' +
			+ '	SELECT COUNT(*) SUBTOTAL ' +
			+ '	FROM ITEM_PS_DEF ' +
			+ '	JOIN ITEM ' +
			+ '	ON ITEM.ITEM_KEY = ITEM_PS_DEF.PARENT_ITEM ' +
			+ '	AND ITEM.REVISION = ITEM_PS_DEF.PARENT_REVISION ' +
			+ '	AND ITEM.OBJECT_TYPE <> 0 ' +
			+ '	WHERE CHILD_ITEM IN (SELECT CI.ITEM_KEY FROM ITEM CI WHERE CI.OBJECT_TYPE = 0) ' +
			+ ') T',
		'Records for type other than ITEM.'
	),
	(
		'UNSUPPORTED_OBJECT_TYPES',
		'DOC (OBJECT_TYPE <> 8)',
		'',
		'SELECT @COUNT = COUNT(*) FROM DOC WHERE OBJECT_TYPE <> 8',
		'Records of type other than Document.'
	)

	DECLARE @Itr_Group NVARCHAR(255)
	DECLARE @Itr_ID INT
	DECLARE @Itr_TABLE NVARCHAR(max)
	DECLARE @Itr_FIELD NVARCHAR(max)
	DECLARE @Itr_SqlQuery NVARCHAR(MAX)

	
	DECLARE @TableExists NVARCHAR(MAX)
	DECLARE @ColumnExists NVARCHAR(MAX)

	DECLARE RecordsCursor CURSOR  
	LOCAL FORWARD_ONLY FOR  
	SELECT 
		--SYS_PROPS.APP_NAME, 
		MIG_ANZ_COMPLEXITIES.[GROUP],
		MIG_ANZ_COMPLEXITIES.[ID],
		MIG_ANZ_COMPLEXITIES.[TABLE],
		MIG_ANZ_COMPLEXITIES.[FIELD],
		MIG_ANZ_COMPLEXITIES.[SQL_QUERY]
	FROM MIG_ANZ_COMPLEXITIES

	--Open cursor & iterate
	OPEN RecordsCursor  
	WHILE 1 = 1   
	BEGIN
		FETCH NEXT FROM RecordsCursor INTO  @Itr_Group, 
											@Itr_ID, 
											@Itr_TABLE,
											@Itr_FIELD,
											@Itr_SqlQuery
		IF @@FETCH_STATUS <> 0
			BREAK

		IF (CHARINDEX(' ', @Itr_TABLE, 1) > 0)
		BEGIN
			SET @Itr_TABLE = SUBSTRING(@Itr_TABLE, 1, CHARINDEX(' ', @Itr_TABLE, 1) - 1)
		END
		
		SET @TableExists = 0
		SET @ColumnExists = 0
		IF @Itr_TABLE <> '' 
			BEGIN
				SELECT @TableExists = 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @Itr_TABLE
				IF @TableExists <> 1
					CONTINUE
			END
		
		IF @Itr_TABLE <> '' AND @Itr_FIELD <> ''
			BEGIN
				SELECT @ColumnExists = 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @Itr_TABLE AND COLUMN_NAME = @Itr_FIELD
				IF @ColumnExists <> 1
					CONTINUE
			END

		SET @COUNT = 0
		SET @SQLString = @Itr_SqlQuery
		EXECUTE sp_executesql 
			@SQLString, 
			N'@COUNT INT OUTPUT', 
			@COUNT=@COUNT OUTPUT

		UPDATE MIG_ANZ_COMPLEXITIES 
		SET [COUNT] = @COUNT
		WHERE MIG_ANZ_COMPLEXITIES.[GROUP] = @Itr_Group
		  AND MIG_ANZ_COMPLEXITIES.[ID] = @Itr_ID

	END

	CLOSE RecordsCursor
		
	-------------------------------------------------------------------------------------------------
	--SET @SQLString = N'SELECT @COUNT = COUNT(*) FROM SITE'
	--EXECUTE sp_executesql @SQLString, N'@COUNT INT OUTPUT', @COUNT=@COUNT OUTPUT
	--SET @COUNT_VAULTS = @COUNT
	-----------------------------------------------------------------------------------------------

	--SELECT 
	--	MIG_ANZ_COMPLEXITIES.[GROUP] AS "X_GROUP",
	--	MIG_ANZ_COMPLEXITIES.[ID] AS "X_ID",
	--	MIG_ANZ_COMPLEXITIES.[TABLE] AS "X_TABLE",
	--	MIG_ANZ_COMPLEXITIES.[FIELD] AS "X_FIELD",
	--	MIG_ANZ_COMPLEXITIES.[SQL_QUERY] AS "SQL_QUERY",
	--	MIG_ANZ_COMPLEXITIES.[COUNT] AS "X_COUNT",
	--	MIG_ANZ_COMPLEXITIES.[COMMENTS] AS "COMMENTS"
	--FROM MIG_ANZ_COMPLEXITIES ORDER BY ID
END

-- exec MIG_GENERATE_ENTRIES_IN_MIG_ANZ_COMPLEXITIES_Sp
-- SELECT * FROM MIG_ANZ_COMPLEXITIES ORDER BY ID

