ALTER TABLE AVS_CLIENT ALTER COLUMN [clin_code] VARCHAR(128) NOT NULL


GO
DECLARE 
	@idServiceRC INT,
	@idServiceAVS INT;

SELECT @idServiceRC = serv_id
FROM AVS_SERVICE
WHERE serv_code = '1'


SELECT @idServiceAVS = serv_id
FROM AVS_SERVICE
WHERE serv_code = '100'


UPDATE AVS_SESSION
SET sess_serv_id = @idServiceRC
WHERE sess_type = 'Service' AND sess_serv_id = @idServiceAVS
GO


PRINT N'Dropping [dbo].[AVS_SESSION].[IND002_AVS_SESSION]...';
GO

IF EXISTS (
	SELECT	1
	FROM	sys.indexes 
	WHERE	name = 'IND001_AVS_SESSION' AND 
			object_id = OBJECT_ID('[dbo].[AVS_SESSION]')
)
DROP INDEX [IND001_AVS_SESSION]
    ON [dbo].[AVS_SESSION];

GO

PRINT N'Dropping [dbo].[AVS_SESSION].[IND002_AVS_SESSION]...';
GO

IF EXISTS (
	SELECT	1
	FROM	sys.indexes 
	WHERE	name = 'IND002_AVS_SESSION' AND 
			object_id = OBJECT_ID('[dbo].[AVS_SESSION]')
)
DROP INDEX [IND002_AVS_SESSION]
    ON [dbo].[AVS_SESSION];


GO
PRINT N'Dropping [dbo].[AVS_SESSION].[IND003_AVS_SESSION]...';


GO

IF EXISTS (
	SELECT	1
	FROM	sys.indexes 
	WHERE	name = 'IND003_AVS_SESSION' AND 
			object_id = OBJECT_ID('[dbo].[AVS_SESSION]')
)
DROP INDEX [IND003_AVS_SESSION]
    ON [dbo].[AVS_SESSION];


GO
PRINT N'Dropping [dbo].[AVS_SESSION].[IND004_AVS_SESSION]...';


GO
IF EXISTS (
	SELECT	1
	FROM	sys.indexes 
	WHERE	name = 'IND004_AVS_SESSION' AND 
			object_id = OBJECT_ID('[dbo].[AVS_SESSION]')
)
DROP INDEX [IND004_AVS_SESSION]
    ON [dbo].[AVS_SESSION];


GO
PRINT N'Creating [dbo].[AVS_SESSION].[IX01_AVS_SESSION]...';


GO
IF NOT EXISTS (
	SELECT	1
	FROM	sys.indexes 
	WHERE	name = 'IX01_AVS_SESSION' AND 
			object_id = OBJECT_ID('[dbo].[AVS_SESSION]')
)
CREATE NONCLUSTERED INDEX [IX01_AVS_SESSION]
    ON [dbo].[AVS_SESSION]([sess_guid] ASC, [sess_company] ASC)
    INCLUDE([sess_code], [sess_start_time], [sess_end_time], [sess_state], [sess_lice_id], [sess_serv_id], [sess_gate_id], [sess_type]);


GO
PRINT N'Creating [dbo].[AVS_SESSION].[IX02_AVS_SESSION]...';


GO
IF NOT EXISTS (
	SELECT	1
	FROM	sys.indexes 
	WHERE	name = 'IX02_AVS_SESSION' AND 
			object_id = OBJECT_ID('[dbo].[AVS_SESSION]')
)
CREATE NONCLUSTERED INDEX [IX02_AVS_SESSION]
    ON [dbo].[AVS_SESSION]([sess_state] ASC)
    INCLUDE([sess_id], [sess_company], [sess_gate_id], [sess_serv_id]);
GO


ALTER PROCEDURE [dbo].[PRC_AVS_AG_SESSION]
(
/*=======================================================================*/
/*  Archivo:            PRC_AVS_AG_SESSION.sql                           */
/*  Stored procedure:   PRC_AVS_AG_SESSION                               */
/*  Producto:           Aranda Virtual Support                           */
/*  Disenado por:       Aranda Software Corporation                      */
/*=======================================================================*/
/*                          IMPORTANTE                                   */
/*  ESTE PROGRAMA ES PROPIEDAD DE "ARANDA SOFTWARE CORPORATION".         */
/*  HA SIDO DISEÑADO BAJO EL AMBIENTE OPERATIVO DE ARANDA.               */
/*  DESARROLLADO POR "ARANDA SOFTWARE CORPORATION - COLOMBIA"            */
/*  SU USO NO AUTORIZADO QUEDA EXPRESAMENTE PROHIBIDO ASI COMO CUALQUIER */
/*  ALTERACION O AGREGADO HECHO POR ALGUNO DE SUS USUARIOS SIN EL DEBIDO */
/*  CONSENTIMIENTO POR ESCRITO DE LA GERENCIA GENERAL DE ARANDA O SU     */
/*  REPRESENTANTE.                                                       */
/*=======================================================================*/
/* OBJETIVO: Administrar y manipular los datos de                        */
/*           Sesiones de Agente                                          */
/* PARAMETROS DE ENTRADA:                                                */
/*			@i_sess_id : identificador de la sesión  			         */
/*			@i_sess_code : código de identificación       	             */
/*			@i_sess_start_time: fecha de inicio de la sesión             */
/*			@i_sess_end_time: fecha finalización                         */
/*			@i_sess_state: estado de la sesión						     */
/*			@i_sess_lice_id: Licencia asociada                           */
/*			@i_sess_serv_id: Servicio asociado                           */
/*			@i_agse_username: nombre de agente                           */
/*			@i_agse_xml_detail: inventario de agente                     */
/*			@i_agse_ip_address: dirección ip                             */
/*			@i_lice_client_id : identificador de la empresa              */
/*			@i_comp_prov_id : identificador del proveedor                */
/* PARAMETROS DE SALIDA:                                                 */
/*           @o_err_cod: Codigo de mensaje. Mayor a 0 si hay error       */
/*           @o_err_msg: Mensaje de error                                */
/* RETORNA:                                                              */
/*          @o_sess_id Identificador de la Sesión                        */
/*=======================================================================*/
	@i_operation        CHAR(1),
	@i_option           CHAR(1),
	
	@i_sess_id          BIGINT         = NULL,
	@i_sess_code        NVARCHAR(128)  = NULL,
	@i_sess_start_time  DATETIME       = NULL,
	@i_sess_end_time    DATETIME       = NULL,
	@i_sess_state       INT            = NULL,
	@i_sess_lice_id     BIGINT         = NULL,
	@i_sess_serv_id     BIGINT         = NULL,

	@i_agse_username    NVARCHAR(256)  = NULL,
	@i_agse_xml_detail  NVARCHAR(4000) = NULL,
	@i_agse_ip_address  NVARCHAR(128)  = NULL,
	@i_agse_version     NVARCHAR(32)   = NULL,
	@i_lice_client_id   BIGINT         = NULL,
	@i_comp_prov_id     BIGINT         = NULL,	
	@i_sess_guid		VARCHAR(150)   = NULL,
	@i_sess_company		VARCHAR(128)   = NULL,
	@i_sess_type		VARCHAR(50)	   = NULL,
	@i_sess_gate_id		BIGINT		   = NULL,

	@o_sess_id			BIGINT         = NULL OUTPUT
)
AS
BEGIN 
	/*========================================================*/
	/*              OPERACIONES DE CONSULTA                   */
	/*========================================================*/
	IF @i_operation = 'S'
	BEGIN

		/*========================================================*/
		/*      Opcion para listar las Sessiones de Agente        */
		/*========================================================*/
		IF @i_option ='A'
		BEGIN
			SELECT   A.agse_id, 
			         A.agse_username,
 				     A.agse_xml_detail,
			         A.agse_ip_address,
					 A.agse_version,
				     S.sess_code,
 			         S.sess_start_time,
			         S.sess_end_time,
				     S.sess_state,
			         S.sess_lice_id,
				     S.sess_serv_id,
					 S.sess_company,
					 S.sess_gate_id,
			         (SELECT serv_name 
				      FROM AVS_SERVICE AS E 
  				      WHERE (E.serv_id = S.sess_serv_id)) AS serv_name,
			         (SELECT lice_client_id
				      FROM  AVS_LICENSE AS L
				      WHERE L.lice_id = S.sess_lice_id) AS lice_client_id,
			         (SELECT C.clin_name
				      FROM AVS_LICENSE AS L 
			JOIN     AVS_CLIENT AS C ON C.clin_id = L.lice_client_id
			WHERE    L.lice_id = S.sess_lice_id) AS clin_name
			FROM     AVS_AG_SESSION AS A 
			JOIN     AVS_SESSION AS S ON S.sess_id = A.agse_id
			ORDER BY S.sess_end_time DESC
		END

		/*========================================================*/
		/*    Opcion una sesión de agente dado el ID              */
		/*========================================================*/
		IF @i_option ='B'
		BEGIN
			SELECT   A.agse_id, 
			         A.agse_username,
 				     A.agse_xml_detail,
			         A.agse_ip_address,
					 A.agse_version,
				     S.sess_code,
 			         S.sess_start_time,
			         S.sess_end_time,
				     S.sess_state,
			         S.sess_lice_id,
				     S.sess_serv_id,
					 S.sess_company,
					 S.sess_gate_id,			         
			         (SELECT serv_name 
				      FROM AVS_SERVICE AS E 
  				      WHERE (E.serv_id = S.sess_serv_id)) AS serv_name,
			         (SELECT lice_client_id
				      FROM  AVS_LICENSE AS L
				      WHERE L.lice_id = S.sess_lice_id) AS lice_client_id,
			         (SELECT C.clin_name
				      FROM AVS_LICENSE AS L 
			JOIN     AVS_CLIENT AS C ON C.clin_id = L.lice_client_id
			WHERE    L.lice_id = S.sess_lice_id) AS clin_name
			FROM     AVS_AG_SESSION AS A 
			JOIN     AVS_SESSION AS S ON S.sess_id = A.agse_id
            WHERE    S.sess_id = @i_sess_id
			ORDER BY S.sess_end_time DESC
		END

		/*========================================================*/
		/* Opcion una sesión de agente dado su ID y su cliente    */
		/*========================================================*/
		IF @i_option ='C'
		BEGIN
			SELECT   A.agse_id, 
			         A.agse_username,
 				     A.agse_xml_detail,
			         A.agse_ip_address,
					 A.agse_version,
				     S.sess_code,
 			         S.sess_start_time,
			         S.sess_end_time,
				     S.sess_state,
			         S.sess_lice_id,
				     S.sess_serv_id,
					 S.sess_company,
					 S.sess_gate_id,			         
			         (SELECT serv_name 
				      FROM AVS_SERVICE AS E 
  				      WHERE (E.serv_id = S.sess_serv_id)) AS serv_name,
			         (SELECT lice_client_id
				      FROM  AVS_LICENSE AS L
				      WHERE L.lice_id = S.sess_lice_id) AS lice_client_id,
			         (SELECT C.clin_name
				      FROM AVS_LICENSE AS L 
			JOIN     AVS_CLIENT AS C ON C.clin_id = L.lice_client_id
			AND      L.lice_client_id = @i_lice_client_id
			WHERE    L.lice_id = S.sess_lice_id) AS clin_name
			FROM     AVS_AG_SESSION AS A 
			JOIN     AVS_SESSION AS S ON S.sess_id = A.agse_id
            WHERE    S.sess_id = @i_sess_id
			ORDER BY S.sess_end_time DESC
		END

		/*========================================================*/
		/* Opcion una sesión de agente dado su ID y su proveedor  */
		/*========================================================*/
		IF @i_option ='D'
		BEGIN
			SELECT A.agse_id,
		           A.agse_username,
				   A.agse_xml_detail,
			       A.agse_ip_address,
				   A.agse_version,
				   S.sess_code,
				   S.sess_start_time,
				   S.sess_end_time, 
			       S.sess_state,
				   S.sess_lice_id,
				   S.sess_serv_id,
				   S.sess_company,
				   S.sess_gate_id,			         
			       (SELECT serv_name
				    FROM   AVS_SERVICE AS E
				    WHERE  serv_id = S.sess_serv_id) AS serv_name,
			       (SELECT lice_client_id
				    FROM   AVS_LICENSE AS L
				    WHERE  lice_id = S.sess_lice_id) AS lice_client_id,
			       (SELECT C.clin_name
				    FROM   AVS_LICENSE AS L
				    JOIN AVS_CLIENT AS C ON C.clin_id = L.lice_client_id
				    WHERE (L.lice_id = S.sess_lice_id)) AS clin_name
		    FROM   AVS_AG_SESSION AS A 
		    JOIN   AVS_SESSION AS S ON S.sess_id = A.agse_id 
		    JOIN   AVS_LICENSE AS L ON S.sess_lice_id = L.lice_id 
		    JOIN   AVS_CLIENT  AS C ON L.lice_client_id = C.clin_id 
		    JOIN   AVS_COMPANY AS O ON C.clin_id = O.comp_id 
		    AND    O.comp_prov_id = @i_comp_prov_id
            WHERE  S.sess_id = @i_sess_id
			ORDER BY S.sess_end_time DESC
		END

		/*========================================================*/
		/* Opcion lista sesiones de agente dado su cliente        */
		/*========================================================*/
		IF @i_option ='E'
		BEGIN
			SELECT   A.agse_id, 
			         A.agse_username,
 				     A.agse_xml_detail,
			         A.agse_ip_address,
					 A.agse_version,
				     S.sess_code,
 			         S.sess_start_time,
			         S.sess_end_time,
				     S.sess_state,
			         S.sess_lice_id,
				     S.sess_serv_id,
					 S.sess_company,
					 S.sess_gate_id,			         
			         E.serv_name,
			         L.lice_client_id,
			         C.clin_name
			FROM     AVS_AG_SESSION AS A 
			JOIN     AVS_SESSION AS S ON S.sess_id = A.agse_id
			JOIN	 AVS_SERVICE AS E ON E.serv_id = S.sess_serv_id  
			JOIN     AVS_LICENSE AS L ON L.lice_id = S.sess_lice_id
			JOIN     AVS_CLIENT AS C ON C.clin_id = L.lice_client_id 
			WHERE  L.lice_client_id = @i_lice_client_id				

			ORDER BY S.sess_end_time DESC
		END

		/*========================================================*/
		/* Opcion una sesión de agente dado su ID y su proveedor  */
		/*========================================================*/
		IF @i_option ='F'
		BEGIN
			SELECT A.agse_id,
		           A.agse_username,
				   A.agse_xml_detail,
			       A.agse_ip_address,
				   A.agse_version,
				   S.sess_code,
				   S.sess_start_time,
				   S.sess_end_time, 
			       S.sess_state,
				   S.sess_lice_id,
				   S.sess_serv_id,
				   S.sess_company,
				   S.sess_gate_id,			         
			       (SELECT serv_name
				    FROM   AVS_SERVICE AS E
				    WHERE  serv_id = S.sess_serv_id) AS serv_name,
			       (SELECT lice_client_id
				    FROM   AVS_LICENSE AS L
				    WHERE  lice_id = S.sess_lice_id) AS lice_client_id,
			       (SELECT C.clin_name
				    FROM   AVS_LICENSE AS L
				    JOIN AVS_CLIENT AS C ON C.clin_id = L.lice_client_id
				    WHERE (L.lice_id = S.sess_lice_id)) AS clin_name
		    FROM   AVS_AG_SESSION AS A 
		    JOIN   AVS_SESSION AS S ON S.sess_id = A.agse_id 
		    JOIN   AVS_LICENSE AS L ON S.sess_lice_id = L.lice_id 
		    JOIN   AVS_CLIENT  AS C ON L.lice_client_id = C.clin_id 
		    JOIN   AVS_COMPANY AS O ON C.clin_id = O.comp_id 
		    AND    O.comp_prov_id = @i_comp_prov_id
            ORDER BY S.sess_end_time DESC
		END
		
		/*========================================================*/
		/*     Opcion para obtener el codigo de la sesion en caso de existir*/
		/*========================================================*/		
		IF @i_option = 'G'
		BEGIN
			SELECT   A.agse_id, 
					 A.agse_username,
					 A.agse_xml_detail,
					 A.agse_ip_address,
					 A.agse_version,
					 S.sess_code,
					 S.sess_start_time,
					 S.sess_end_time,
					 S.sess_state,
					 S.sess_lice_id,
					 S.sess_serv_id,
					 S.sess_company,
					 S.sess_gate_id,			         
					 (SELECT serv_name 
				      FROM AVS_SERVICE AS E 
  				      WHERE (E.serv_id = S.sess_serv_id)) AS serv_name,
			         (SELECT lice_client_id
				      FROM  AVS_LICENSE AS L
				      WHERE L.lice_id = S.sess_lice_id) AS lice_client_id,
			         (SELECT C.clin_name
				      FROM AVS_LICENSE AS L 
			JOIN     AVS_CLIENT AS C ON C.clin_id = L.lice_client_id
			WHERE    L.lice_id = S.sess_lice_id) AS clin_name
			FROM     AVS_AG_SESSION AS A 
			JOIN     AVS_SESSION AS S ON S.sess_id = A.agse_id
			WHERE    S.sess_guid = @i_sess_guid
			AND		 S.sess_type = @i_sess_type
			AND		 S.sess_company = @i_sess_company
			ORDER BY S.sess_end_time DESC
		END
	END
	/*========================================================*/
	/*             OPERACIONES DE INSERCION                   */
	/*========================================================*/
	IF @i_operation = 'I'
	BEGIN
		/*========================================================*/
		/*         Opcion para el registro de Sesiones            */
		/*========================================================*/
		IF @i_option ='A'
		BEGIN
			EXECUTE PRC_AVS_SESSION
				@i_operation,
				@i_option,
				@i_sess_id,
				@i_sess_code,
				@i_sess_start_time,
				@i_sess_end_time,
				@i_sess_state,
				@i_sess_lice_id,
				@i_sess_serv_id,
				@i_sess_gate_id,
				@i_sess_guid,
				@i_sess_company,
				@i_sess_type,
				@o_sess_id output
			
			IF @o_sess_id IS NOT NULL	
			BEGIN
				INSERT INTO AVS_AG_SESSION
					(agse_id,       agse_username,    agse_xml_detail,    agse_ip_address, agse_version)
				VALUES
					(@o_sess_id, @i_agse_username, @i_agse_xml_detail, @i_agse_ip_address, @i_agse_version)
			END
		END
		
	END
	/*========================================================*/
	/*            OPERACIONES DE ACTUALIZACION                */
	/*========================================================*/
	IF @i_operation = 'U'
	BEGIN

		/*========================================================*/
		/*    Opcion para actualizar una Sesión                   */
		/*========================================================*/
		IF @i_option ='A'
		BEGIN
			EXECUTE PRC_AVS_SESSION
				@i_operation,
				@i_option,
				@i_sess_id,
				@i_sess_code,
				@i_sess_start_time,
				@i_sess_end_time,
				@i_sess_state,
				@i_sess_lice_id,
				@i_sess_serv_id,
				@i_sess_gate_id
		
			UPDATE AVS_AG_SESSION
			SET              
				agse_username      = @i_agse_username, 
				agse_xml_detail    = @i_agse_xml_detail, 
				agse_ip_address    = @i_agse_ip_address,
				agse_version	   = @i_agse_version
			WHERE agse_id = @i_sess_id
		END
	END
	/*========================================================*/
	/*             OPERACIONES DE ELIMINACION                 */
	/*========================================================*/
	IF @i_operation = 'D'
	BEGIN

	/*========================================================*/
	/*         Opcion para eliminar una sesión                */
	/*========================================================*/
		IF @i_option ='A'
		BEGIN
			DELETE FROM AVS_AG_SESSION 
			WHERE       agse_id = @i_sess_id
			
			EXECUTE PRC_AVS_SESSION
				@i_operation,
				@i_option,
				@i_sess_id,
				@i_sess_code,
				@i_sess_start_time,
				@i_sess_end_time,
				@i_sess_state,
				@i_sess_lice_id,
				@i_sess_serv_id,
				@i_sess_gate_id
		END
	END
END
GO

IF  EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MetadataInsert]') AND type in (N'P', N'PC'))
BEGIN
	DROP PROCEDURE [dbo].[MetadataInsert]
END
GO

CREATE PROCEDURE [dbo].[MetadataInsert]
	@swve_swcl_code	NVARCHAR(10),
	@swve_version	NVARCHAR(128),
	@swve_date		DATETIME	
AS
BEGIN	
	DECLARE @id BIGINT
	SELECT @id = [swcl_id] FROM [AVS_SW_CLIENT] WHERE [swcl_code] = @swve_swcl_code
	
	IF EXISTS (SELECT 1 FROM [AVS_SW_VERSION] WHERE [swve_swcl_id] = @id )
		UPDATE [AVS_SW_VERSION]
		SET 
		  [swve_version] = @swve_version,
		  [swve_date] = @swve_date
		WHERE [swve_swcl_id] = @id
		 
	ELSE
		INSERT INTO [AVS_SW_VERSION](
			[swve_version],
			[swve_date], 
			[swve_swcl_id]
		)VALUES(
			@swve_version,
			@swve_date,
			@id 
		)
		
END
GO

--METADATA
EXEC [dbo].[MetadataInsert] N'4', N'8.3.1', N'20200731'
GO

--DROP PROCEDURE [dbo].[MetadataInsert]
GO