USE [ArandaDB8]
GO
/****** Object:  StoredProcedure [dbo].[PRC_ASDK_RULES_DATES]    Script Date: 02/15/2017 09:59:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[PRC_ASDK_RULES_DATES] AS
BEGIN
	DECLARE @v_rule_count INT,
		@v_case_id INT,
		@v_rule_string NVARCHAR(MAX),
		@cur_dates_rules CURSOR,
		@v_rule_string_inci NVARCHAR(MAX),
		@v_rule_string_prob NVARCHAR(MAX),
		@v_rule_string_chan NVARCHAR(MAX),
		@v_rule_string_serv NVARCHAR(MAX),
		@v_timestamp DATETIME,
		@v_rule_validate TINYINT,
		@v_current_date DATETIME,
		@v_case_type TINYINT,
		@v_sentence NVARCHAR(MAX);

	SET @v_rule_string_inci = dbo.FUN_ASDK_RULES_STRING(1, 2);
	SET @v_rule_string_prob = dbo.FUN_ASDK_RULES_STRING(2, 2);
	SET @v_rule_string_chan = dbo.FUN_ASDK_RULES_STRING(3, 2);
	SET @v_rule_string_serv = dbo.FUN_ASDK_RULES_STRING(15, 2);
	SET @v_case_type = 1;
	
	WHILE @v_case_type < 5
	BEGIN
		SET @v_current_date = GETDATE();

		SET @v_sentence = REPLACE(
		convert(varchar(max),
			'SET @cur_dates_rules = CURSOR LOCAL FAST_FORWARD FOR'
			+ '	SELECT * FROM ('
			+ '		SELECT {3}id,'
			+ '			{1} rules_string,'
			+ '			time_stamp'
			+ '			FROM {2}'
			+ '			WHERE {3}final_status = 1'
			+ '			AND {3}status_type = 0'
			+ '		)tmp WHERE tmp.rules_string <> '''''
			+ 'OPEN @cur_dates_rules;'),
			'{1}', CASE @v_case_type WHEN 1 THEN @v_rule_string_inci WHEN 2 THEN @v_rule_string_prob WHEN 3 THEN @v_rule_string_chan ELSE @v_rule_string_serv END
		);

		IF @v_sentence IS NOT NULL
		BEGIN
			SET @v_sentence = REPLACE(@v_sentence, '{2}', CASE @v_case_type WHEN 1 THEN 'V_ASDK_INCIDENT_TIME' WHEN 2 THEN 'V_ASDK_PROBLEM_TIME'
																			WHEN 3 THEN 'V_ASDK_CHANGE_TIME' ELSE 'V_ASDK_SERVICE_CALL_TIME' END);

			SET @v_sentence = REPLACE(@v_sentence, '{3}', CASE @v_case_type WHEN 1 THEN 'inci_' WHEN 2 THEN 'prob_'
																			WHEN 3 THEN 'chan_' ELSE 'serv_' END);

			EXEC SP_EXECUTESQL @v_sentence, N'@v_case_type INT, @cur_dates_rules CURSOR OUTPUT, @v_current_date DATETIME OUTPUT', @v_case_type, @cur_dates_rules OUTPUT, @v_current_date OUTPUT;

			FETCH NEXT FROM @cur_dates_rules INTO @v_case_id, @v_rule_string, @v_timestamp;

			WHILE @@FETCH_STATUS = 0
				BEGIN
					SET @v_rule_count = LEN(@v_rule_string) - LEN(REPLACE(@v_rule_string, ',', ''));
					SET @v_rule_string = CASE WHEN CHARINDEX(',', REVERSE(@v_rule_string), 1) = 1 THEN SUBSTRING(@v_rule_string, 1, LEN(@v_rule_string) - 1) END;

					SET @v_rule_string = REPLACE('SELECT @v_rule_validate = @v_rule_count - ISNULL(SUM(CASE WHEN idregla IN({Rules}) THEN 1 ELSE 0 END), 0)
											FROM ASDK_RULES_EXECUTED WHERE idcaso = @v_case_id', '{Rules}', @v_rule_string);
		
					EXEC SP_EXECUTESQL @v_rule_string, N'@v_case_id INT, @v_rule_count INT, @v_rule_validate INT OUTPUT', @v_case_id, @v_rule_count, @v_rule_validate OUTPUT;

					IF @@ROWCOUNT > 0 AND @v_rule_validate > 0
					BEGIN
						IF @v_case_type = 1
						BEGIN --To incident snapshot

							INSERT INTO ASDK_INCIDENT_RULES
							SELECT *
							FROM V_ASDK_INCIDENT_SNAPSHOT
							WHERE inci_id = @v_case_id
							AND inci_sdb_last_shot = @v_timestamp;

						END;
						ELSE IF @v_case_type = 2
						BEGIN --To problem snapshot

							INSERT INTO ASDK_PROBLEM_RULES
							SELECT *
							FROM V_ASDK_PROBLEM_SNAPSHOT
							WHERE prob_id = @v_case_id;

						END;
						ELSE IF @v_case_type = 3
						BEGIN --To change snapshot

							INSERT INTO ASDK_CHANGE_RULES
							SELECT *
							FROM V_ASDK_CHANGE_SNAPSHOT
							WHERE chan_id = @v_case_id;

						END
						ELSE IF @v_case_type = 4
						BEGIN --To service call snapshot

							INSERT INTO ASDK_SERVICE_CALL_RULES
							SELECT *
							FROM V_ASDK_SERVICE_CALL_SNAPSHOT
							WHERE serv_id = @v_case_id;

						END;
					END;

					FETCH NEXT FROM @cur_dates_rules INTO @v_case_id, @v_rule_string, @v_timestamp;

				END;

			CLOSE @cur_dates_rules;
			DEALLOCATE @cur_dates_rules;
		END;
		
		SET @v_case_type = @v_case_type + 1;
	END;
END;