USE [ARANDA]
GO
/****** Object:  StoredProcedure [dbo].[PRC_ASDK_HIST_FILES]    Script Date: 07/17/2018 12:39:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER  PROCEDURE [dbo].[PRC_ASDK_HIST_FILES] (
	@i_item_id						INT				= NULL,
	@i_precase_id					INT				= NULL,
	@i_operation					CHAR(1),
	@i_option						CHAR(1)			= NULL,
	@i_hifi_row_type				INT				= NULL,
	@i_hifi_author					INT				= NULL,
	@i_hifi_file_name				NVARCHAR(4000)	= NULL,
	@i_hifi_file_size				INT				= NULL,
	@i_hifi_attach					CHAR(1)			= NULL,
	@i_hifi_application_id		INT				= NULL,
	@i_hifi_id						INT				= NULL,
	@i_hifi_full_path				INT				= 0
) AS

DECLARE
	@v_hifi_id						INT,
	@v_hifi_file_name				NVARCHAR(4000),
	@v_hifi_file_size				INT,
	@v_hifi_created_date			DATETIME,
	@v_hifi_author					INT,
	@v_hifi_attach					INT,
	@v_hifi_row_type				INT,
	@v_date_close					DATETIME,
	@v_date_open					DATETIME,
	@v_count							INT,
	@o_err_cod						INT,
	@o_err_msg						VARCHAR(255);
 
BEGIN

	IF LEN(@i_hifi_file_name) > 0
	BEGIN
		SET @v_hifi_file_name = LTRIM(RTRIM(@i_hifi_file_name))
	END

	/* Registra historico de enrutamiento o escalamiento */
	IF @i_operation = 'I'
	BEGIN

		IF @i_option = 'A'
		BEGIN

			/* Aplica logica para incidents */
			IF @i_hifi_row_type = 1
			BEGIN

				SELECT @v_date_open = inci_opened_date,
					@v_date_close = inci_colsed_date
				FROM dbo.ASDK_INCIDENT
				WHERE inci_id = @i_item_id;

			END;

			/* Aplica logica para problemas */
			IF @i_hifi_row_type = 2
			BEGIN

				SELECT @v_date_open = prob_opened_date,
					@v_date_close = prob_colsed_date
				FROM dbo.ASDK_PROBLEM
				WHERE prob_id = @i_item_id;

			END;

			/* Aplica logica para cambios*/
			IF @i_hifi_row_type = 3
			BEGIN

				SELECT @v_date_open = chan_opened_date,
					@v_date_close = chan_colsed_date
				FROM dbo.ASDK_CHANGE
				WHERE chan_id = @i_item_id;

			END;

			/* Aplica logica para llamadas de servicio */
			IF @i_hifi_row_type = 4
			BEGIN

				SELECT @v_date_open = serv_opened_date,
					@v_date_close = serv_colsed_date
				FROM dbo.ASDK_SERVICE_CALL
				WHERE serv_id = @i_item_id;

			END;

			SET @v_hifi_created_date = @v_date_open;

			EXECUTE ASC_SEQUENCE_ADM
				@i_table = 'ASDK_HIST_FILES',
				@o_nextvalue = @v_hifi_id OUTPUT,
				@o_err_cod = @o_err_cod OUTPUT, 
				@o_err_msg = @o_err_msg OUTPUT;

			INSERT INTO ASDK_HIST_FILES (
				hifi_id,
				hifi_application_id,
				hifi_item_id,
				hifi_row_type,
				hifi_file_name,
				hifi_file_size,
				hifi_created,
				hifi_author,
				hifi_attach,
				hifi_full_path
			) VALUES (
				@v_hifi_id,
				@i_hifi_application_id,
				@i_item_id,
				@i_hifi_row_type,
				@v_hifi_file_name,
				@i_hifi_file_size,
				@v_hifi_created_date,
				@i_hifi_author,
				@i_hifi_attach,
				@i_hifi_full_path
			);

		END;
		ELSE
		BEGIN

			EXECUTE ASC_SEQUENCE_ADM
				@i_table = 'ASDK_HIST_FILES',
				@o_nextvalue = @v_hifi_id OUTPUT,
				@o_err_cod = @o_err_cod OUTPUT,
				@o_err_msg = @o_err_msg OUTPUT;

			INSERT INTO ASDK_HIST_FILES (
				hifi_id,
				hifi_application_id,
				hifi_item_id,
				hifi_row_type,
				hifi_file_name,
				hifi_file_size,
				hifi_created,
				hifi_author,
				hifi_attach,
				hifi_full_path
			) VALUES (
				@v_hifi_id,
				@i_hifi_application_id,
				@i_item_id,
				@i_hifi_row_type,
				@v_hifi_file_name,
				@i_hifi_file_size,
				GETDATE(),
				@i_hifi_author,
				@i_hifi_attach,
				@i_hifi_full_path
			);

		END;		
	END;

	/* Copia el historico de archivos de un precaso a otro item */
	IF @i_operation = 'A'
	BEGIN

		DECLARE cur_hist_files CURSOR LOCAL STATIC FOR
		SELECT hifi_file_name, 
			hifi_file_size,
			hifi_created,
			hifi_author,
			hifi_attach
		FROM ASDK_HIST_FILES
		WHERE hifi_id IN (
			SELECT MAX(hifi_id)
			FROM ASDK_HIST_FILES
			WHERE	hifi_row_type = 10
			AND hifi_item_id = @i_precase_id
			AND hifi_application_id = 13
			GROUP BY hifi_file_name
		) AND hifi_attach = 1;

		OPEN cur_hist_files;

		FETCH NEXT FROM cur_hist_files INTO
			@v_hifi_file_name,
			@v_hifi_file_size,
			@v_hifi_created_date,
			@v_hifi_author,
			@v_hifi_attach;

		WHILE @@FETCH_STATUS = 0
		BEGIN

			SET @v_hifi_id = NULL;

			EXECUTE ASC_SEQUENCE_ADM
				@i_table = 'ASDK_HIST_FILES',
				@o_nextvalue = @v_hifi_id OUTPUT,
				@o_err_cod = @o_err_cod OUTPUT,
				@o_err_msg = @o_err_msg OUTPUT;

			INSERT INTO ASDK_HIST_FILES (
				hifi_id,
				hifi_application_id,
				hifi_item_id,
				hifi_row_type,
				hifi_file_name,
				hifi_file_size,
				hifi_created,
				hifi_author,
				hifi_attach
			) VALUES (
				@v_hifi_id,
				13,
				@i_item_id,
				@i_hifi_row_type,
				@v_hifi_file_name,
				@v_hifi_file_size,
				@v_hifi_created_date,
				@v_hifi_author,
				@v_hifi_attach
			);

			FETCH NEXT FROM cur_hist_files INTO
				@v_hifi_file_name,
				@v_hifi_file_size,
				@v_hifi_created_date,
				@v_hifi_author,
				@v_hifi_attach;

		END;

	END;
	/*Copia adjuntos de un caso a otro caso*/
	IF @i_operation = 'B'
	BEGIN
		SET @v_count = 0;

		DECLARE cur_hist_files_copy CURSOR LOCAL STATIC FOR
		SELECT hifi_file_name, 
			hifi_file_size,
			hifi_created,
			hifi_author,
			hifi_attach,
			hifi_row_type
		FROM ASDK_HIST_FILES
		WHERE hifi_id IN (
			SELECT MAX(hifi_id)
			FROM ASDK_HIST_FILES
			WHERE	hifi_item_id = @i_precase_id
			AND hifi_application_id = 13
			GROUP BY hifi_file_name
		) AND hifi_attach = 1;

		OPEN cur_hist_files_copy;

		FETCH NEXT FROM cur_hist_files_copy INTO
			@v_hifi_file_name,
			@v_hifi_file_size,
			@v_hifi_created_date,
			@v_hifi_author,
			@v_hifi_attach,
			@v_hifi_row_type;

		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF @v_count = 0
			BEGIN
				INSERT INTO	ASDK_ATTACHED_FILES_MAPPINGS(	origin_id,
																		origin_type,
																		destiny_id,		
																		destiny_type
														)VALUES(	@i_precase_id,
																	@v_hifi_row_type,
																	@i_item_id,
																	@i_hifi_row_type
														);	
				SET @v_count = 1;
			END;

			SET @v_hifi_id = NULL;

			EXECUTE ASC_SEQUENCE_ADM
				@i_table = 'ASDK_HIST_FILES',
				@o_nextvalue = @v_hifi_id OUTPUT,
				@o_err_cod = @o_err_cod OUTPUT,
				@o_err_msg = @o_err_msg OUTPUT;

			INSERT INTO ASDK_HIST_FILES (
				hifi_id,
				hifi_application_id,
				hifi_item_id,
				hifi_row_type,
				hifi_file_name,
				hifi_file_size,
				hifi_created,
				hifi_author,
				hifi_attach
			) VALUES (
				@v_hifi_id,
				13,
				@i_item_id,
				@i_hifi_row_type,
				@v_hifi_file_name,
				@v_hifi_file_size,
				@v_hifi_created_date,
				@v_hifi_author,
				@v_hifi_attach
			);

			FETCH NEXT FROM cur_hist_files_copy INTO
				@v_hifi_file_name,
				@v_hifi_file_size,
				@v_hifi_created_date,
				@v_hifi_author,
				@v_hifi_attach,
				@v_hifi_row_type;

		END;

		CLOSE cur_hist_files_copy;

	END;
	/* Registra historico de enrutamiento o escalamiento */
	IF @i_operation = 'U'
	BEGIN

		UPDATE ASDK_HIST_FILES
		SET hifi_attach = @i_hifi_attach
		WHERE hifi_item_id = @i_item_id
		AND hifi_row_type = @i_hifi_row_type
		AND hifi_file_name = @v_hifi_file_name
		AND hifi_application_id = @i_hifi_application_id;

	END;

	/* Consulta el historico de un item */
	IF @i_operation = 'S'
	BEGIN

		IF @i_option = 'A'
		BEGIN

			-- CONSULTA TODOS LOS HISTORICOS DE UN ELEMENTO
			SELECT hifi_id,
				hifi_application_id,
				hifi_item_id,
				hifi_row_type,
				hifi_file_name,
				hifi_file_size,
				hifi_created,
				hifi_author,
				hifi_attach,
				hifi_full_path,
				hifi_guid
			FROM ASDK_HIST_FILES
			WHERE hifi_file_name = @i_hifi_file_name
			AND hifi_application_id = @i_hifi_application_id
			AND hifi_item_id = @i_item_id
			AND hifi_row_type = @i_hifi_row_type;

		END;

		IF @i_option = 'B'
		BEGIN

			-- Consulta informacion de un elemento
			SELECT hifi_id,
				hifi_item_id,
				hifi_row_type,
				hifi_file_name,
				hifi_file_size,
				hifi_author,
				hifi_application_id,
				hifi_created,
				hifi_attach,
				hifi_full_path,
				hifi_guid
			FROM ASDK_HIST_FILES
			WHERE HIFI_ID = @i_hifi_id;

		END;

	END;
	
END;