/* Deployment script for BASE_EMPTY This code was generated by a tool. Changes to this file may cause incorrect behavior and will be lost if the code is regenerated. */ GO SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON; SET NUMERIC_ROUNDABORT OFF; GO /* --:setvar DatabaseName "BASE_EMPTY" --:setvar DefaultFilePrefix "BASE_EMPTY" --:setvar DefaultDataPath "D:\DATABASE\DESA\" --:setvar DefaultLogPath "E:\DATABASE_LOGS\DESA\" */ GO --:on error exit GO /* Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported. To re-enable the script after enabling SQLCMD mode, execute the following: SET NOEXEC OFF; */ --:setvar __IsSqlCmdEnabled "True" GO IF N'True' NOT LIKE N'True' BEGIN PRINT N'SQLCMD mode must be enabled to successfully execute this script.'; SET NOEXEC ON; END GO --USE [$(DatabaseName)]; GO /* Pre-Deployment Script Template -------------------------------------------------------------------------------------- This file contains SQL statements that will be executed before the build script. Use SQLCMD syntax to include a file in the pre-deployment script. Example: :r .\myfile.sql Use SQLCMD syntax to reference a variable in the pre-deployment script. Example: --:setvar TableName MyTable SELECT * FROM [$(TableName)] -------------------------------------------------------------------------------------- */ GO GO PRINT N'Creating [dbo].[AVS_AG_SESSION]...'; GO CREATE TABLE [dbo].[AVS_AG_SESSION] ( [agse_id] BIGINT NOT NULL, [agse_username] NVARCHAR (256) NOT NULL, [agse_xml_detail] NVARCHAR (4000) NOT NULL, [agse_ip_address] NVARCHAR (128) NULL, [agse_version] NVARCHAR (32) NULL, CONSTRAINT [PK_AVS_AG_SESSION] PRIMARY KEY CLUSTERED ([agse_id] ASC) ); GO PRINT N'Creating [dbo].[AVS_ARANDA]...'; GO CREATE TABLE [dbo].[AVS_ARANDA] ( [aran_id] BIGINT IDENTITY (1, 1) NOT NULL, [aran_key] NVARCHAR (MAX) NOT NULL, [aran_value] NVARCHAR (MAX) NOT NULL, CONSTRAINT [PK_AVS_SER] PRIMARY KEY CLUSTERED ([aran_id] ASC) ); GO PRINT N'Creating [dbo].[AVS_CD_KEY]...'; GO CREATE TABLE [dbo].[AVS_CD_KEY] ( [cdke_id] BIGINT IDENTITY (1, 1) NOT NULL, [cdke_value] NVARCHAR (1024) NOT NULL, [cdke_date] DATETIME NOT NULL, [cdke_user_key] BIGINT NOT NULL, CONSTRAINT [PK_AVS_CD_KEY] PRIMARY KEY CLUSTERED ([cdke_id] ASC) ); GO PRINT N'Creating [dbo].[AVS_CLIENT]...'; GO CREATE TABLE [dbo].[AVS_CLIENT] ( [clin_id] BIGINT IDENTITY (1, 1) NOT NULL, [clin_activate_date] DATETIME NOT NULL, [clin_is_activate] INT NOT NULL, [clin_contact] NVARCHAR (4000) NOT NULL, [clin_name] NVARCHAR (256) NOT NULL, [clin_code] VARCHAR (128) NOT NULL, CONSTRAINT [PK_AVS_CLIENT] PRIMARY KEY CLUSTERED ([clin_id] ASC) ); GO PRINT N'Creating [dbo].[AVS_CLIENT_GATEWAY]...'; GO CREATE TABLE [dbo].[AVS_CLIENT_GATEWAY] ( [clin_id] BIGINT NOT NULL, [gate_id] BIGINT NULL, CONSTRAINT [PK_AVS_CLIENT_GATEWAY] PRIMARY KEY CLUSTERED ([clin_id] ASC) ); GO PRINT N'Creating [dbo].[AVS_COMPANY]...'; GO CREATE TABLE [dbo].[AVS_COMPANY] ( [comp_id] BIGINT NOT NULL, [comp_prov_id] BIGINT NOT NULL, CONSTRAINT [PK_AVS_COMPANY] PRIMARY KEY CLUSTERED ([comp_id] ASC) ); GO PRINT N'Creating [dbo].[AVS_COMPANY_ADMIN]...'; GO CREATE TABLE [dbo].[AVS_COMPANY_ADMIN] ( [coad_id] BIGINT NOT NULL, [coad_comp_id] BIGINT NOT NULL, CONSTRAINT [PK_AVS_COMPANY_ADMIN] PRIMARY KEY CLUSTERED ([coad_id] ASC) ); GO PRINT N'Creating [dbo].[AVS_GATEWAY_BASE]...'; GO CREATE TABLE [dbo].[AVS_GATEWAY_BASE] ( [gate_id] BIGINT IDENTITY (1, 1) NOT NULL, [gate_server_port] INT NOT NULL, [gate_console_page_size] INT NOT NULL, [gate_agent_page_size] INT NOT NULL, [gate_service_page_size] INT NOT NULL, [gate_version] NVARCHAR (128) NOT NULL, [gate_last_change] DATETIME NOT NULL, [gate_last_state] INT NOT NULL, [gate_last_update] DATETIME NOT NULL, [gate_last_username] NVARCHAR (128) NOT NULL, [gate_audit_time] INT NOT NULL, [gate_wait_time] INT NOT NULL, [gate_enable_ssl] INT NOT NULL, [gate_cert_subject] NVARCHAR (512) NOT NULL, [gate_name] NVARCHAR (1024) NOT NULL, [gate_activate] NVARCHAR (1024) NULL, [gate_audit_url] NVARCHAR (2048) NULL, [gate_install_url] NVARCHAR (2048) NULL, [gate_host] NVARCHAR (2048) NULL, [gate_description] NVARCHAR (512) NULL, CONSTRAINT [PK_AVS_GATEWAY_BASE] PRIMARY KEY CLUSTERED ([gate_id] ASC), CONSTRAINT [UX_AVS_GATEWAY_BASE_description] UNIQUE NONCLUSTERED ([gate_description] ASC), CONSTRAINT [UX_AVS_GATEWAY_BASE_port] UNIQUE NONCLUSTERED ([gate_server_port] ASC) ); GO PRINT N'Creating [dbo].[AVS_LICENSE]...'; GO CREATE TABLE [dbo].[AVS_LICENSE] ( [lice_id] BIGINT IDENTITY (1, 1) NOT NULL, [lice_serial_number] NVARCHAR (1024) NOT NULL, [lice_serial_number_two] NVARCHAR (1024) NULL, [lice_serial_number_three] NVARCHAR (1024) NULL, [lice_type] INT NULL, [lice_descrition] NVARCHAR (4000) NULL, [lice_agent_quantity] INT NOT NULL, [lice_console_quantity] INT NOT NULL, [lice_vip_quantity] INT NOT NULL, [lice_start_date] DATETIME NOT NULL, [lice_end_date] DATETIME NOT NULL, [lice_is_master] INT NOT NULL, [lice_agent_available] INT NOT NULL, [lice_console_available] INT NOT NULL, [lice_vip_available] INT NOT NULL, [lice_client_id] BIGINT NOT NULL, [lice_serv_id] BIGINT NOT NULL, [lice_state] INT NOT NULL, CONSTRAINT [PK_AVS_LICENSE] PRIMARY KEY CLUSTERED ([lice_id] ASC) ); GO PRINT N'Creating [dbo].[AVS_LICENSE].[IND001_AVS_LICENSE]...'; GO CREATE NONCLUSTERED INDEX [IND001_AVS_LICENSE] ON [dbo].[AVS_LICENSE]([lice_client_id] ASC); GO PRINT N'Creating [dbo].[AVS_PROVIDER]...'; GO CREATE TABLE [dbo].[AVS_PROVIDER] ( [prov_id] BIGINT NOT NULL, [prov_companies_assigned] NVARCHAR (1024) NOT NULL, [prov_companies_available] NVARCHAR (1024) NOT NULL, [prov_wildcards] NVARCHAR (1024) NOT NULL, CONSTRAINT [PK_AVS_TENANT_1] PRIMARY KEY CLUSTERED ([prov_id] ASC) ); GO PRINT N'Creating [dbo].[AVS_PROVIDER_ADMIN]...'; GO CREATE TABLE [dbo].[AVS_PROVIDER_ADMIN] ( [prad_id] BIGINT NOT NULL, [prad_prov_id] BIGINT NOT NULL, CONSTRAINT [PK_AVS_TENANT_ADMIN] PRIMARY KEY CLUSTERED ([prad_id] ASC) ); GO PRINT N'Creating [dbo].[AVS_ROLE]...'; GO CREATE TABLE [dbo].[AVS_ROLE] ( [role_id] BIGINT NOT NULL, [role_name] NVARCHAR (128) NULL, [role_description] NVARCHAR (4000) NULL, CONSTRAINT [PK_AVS_ROLE] PRIMARY KEY CLUSTERED ([role_id] ASC) ); GO PRINT N'Creating [dbo].[AVS_SER_SESSION]...'; GO CREATE TABLE [dbo].[AVS_SER_SESSION] ( [sese_id] BIGINT NOT NULL, [sese_agse_id] BIGINT NOT NULL, [sese_spse_id] BIGINT NOT NULL, [sese_agent] NVARCHAR (128) NULL, [sese_console] NVARCHAR (128) NULL, [sese_has_video] INT NOT NULL, CONSTRAINT [PK_AVS_SER_SESSION] PRIMARY KEY CLUSTERED ([sese_id] ASC) ); GO PRINT N'Creating [dbo].[AVS_SER_SESSION].[IND_001_AVS_SER_SESSION]...'; GO CREATE NONCLUSTERED INDEX [IND_001_AVS_SER_SESSION] ON [dbo].[AVS_SER_SESSION]([sese_agse_id] ASC); GO PRINT N'Creating [dbo].[AVS_SER_SESSION].[IND_002_AVS_SER_SESSION]...'; GO CREATE NONCLUSTERED INDEX [IND_002_AVS_SER_SESSION] ON [dbo].[AVS_SER_SESSION]([sese_spse_id] ASC); GO PRINT N'Creating [dbo].[AVS_SERVICE]...'; GO CREATE TABLE [dbo].[AVS_SERVICE] ( [serv_id] BIGINT IDENTITY (1, 1) NOT NULL, [serv_name] NVARCHAR (128) NOT NULL, [serv_code] NVARCHAR (128) NOT NULL, [serv_description] NVARCHAR (4000) NOT NULL, [serv_is_activate] INT NOT NULL, [serv_is_master] INT NOT NULL, CONSTRAINT [PK_AVS_SERVICE] PRIMARY KEY CLUSTERED ([serv_id] ASC), CONSTRAINT [UX_AVS_SERVICE] UNIQUE NONCLUSTERED ([serv_code] ASC) ); GO PRINT N'Creating [dbo].[AVS_SESSION]...'; GO CREATE TABLE [dbo].[AVS_SESSION] ( [sess_id] BIGINT IDENTITY (1, 1) NOT NULL, [sess_code] NVARCHAR (128) NOT NULL, [sess_start_time] DATETIME NOT NULL, [sess_end_time] DATETIME NOT NULL, [sess_state] INT NOT NULL, [sess_lice_id] BIGINT NOT NULL, [sess_serv_id] BIGINT NOT NULL, [sess_gate_id] BIGINT NULL, [sess_type] VARCHAR (50) NULL, [sess_company] VARCHAR (128) NULL, [sess_guid] VARCHAR (150) NULL, CONSTRAINT [PK_AVS_SESSION] PRIMARY KEY CLUSTERED ([sess_id] ASC) ); GO PRINT N'Creating [dbo].[AVS_SESSION].[IX01_AVS_SESSION]...'; GO 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 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 PRINT N'Creating [dbo].[AVS_SPE_SESSION]...'; GO CREATE TABLE [dbo].[AVS_SPE_SESSION] ( [spse_id] BIGINT NOT NULL, [spse_spec_id] BIGINT NOT NULL, [spse_ip_address] NVARCHAR (128) NULL, [spse_version] NVARCHAR (32) NULL, CONSTRAINT [PK_AVS_SPE_SESSION] PRIMARY KEY CLUSTERED ([spse_id] ASC) ); GO PRINT N'Creating [dbo].[AVS_SPE_SESSION].[IND_001_AVS_SPE_SESSION]...'; GO CREATE NONCLUSTERED INDEX [IND_001_AVS_SPE_SESSION] ON [dbo].[AVS_SPE_SESSION]([spse_id] ASC); GO PRINT N'Creating [dbo].[AVS_SPE_SESSION].[IND_002_AVS_SPE_SESSION]...'; GO CREATE NONCLUSTERED INDEX [IND_002_AVS_SPE_SESSION] ON [dbo].[AVS_SPE_SESSION]([spse_spec_id] ASC); GO PRINT N'Creating [dbo].[AVS_SPECIALIST]...'; GO CREATE TABLE [dbo].[AVS_SPECIALIST] ( [spec_id] BIGINT NOT NULL, [spec_comp_id] BIGINT NOT NULL, [spec_is_vip] INT NOT NULL, [spec_is_audit] INT NOT NULL, CONSTRAINT [PK_AVS_SPECIALIST] PRIMARY KEY CLUSTERED ([spec_id] ASC) ); GO PRINT N'Creating [dbo].[AVS_SUBLICENSE]...'; GO CREATE TABLE [dbo].[AVS_SUBLICENSE] ( [subl_master] BIGINT NOT NULL, [subl_child] BIGINT NOT NULL, CONSTRAINT [PK_AVS_SUBLICENSE] PRIMARY KEY CLUSTERED ([subl_master] ASC, [subl_child] ASC) ); GO PRINT N'Creating [dbo].[AVS_SUBSERVICE]...'; GO CREATE TABLE [dbo].[AVS_SUBSERVICE] ( [subs_master] BIGINT NOT NULL, [subs_child] BIGINT NOT NULL, CONSTRAINT [PK_AVS_SUBSERVICE_1] PRIMARY KEY CLUSTERED ([subs_master] ASC, [subs_child] ASC) ); GO PRINT N'Creating [dbo].[AVS_SW_CLIENT]...'; GO CREATE TABLE [dbo].[AVS_SW_CLIENT] ( [swcl_id] BIGINT IDENTITY (1, 1) NOT NULL, [swcl_name] NVARCHAR (128) NOT NULL, [swcl_code] NVARCHAR (128) NOT NULL, [swcl_description] NVARCHAR (4000) NOT NULL, [swcl_base_path] NVARCHAR (1024) NULL, [swcl_comp_path] NVARCHAR (1024) NULL, [swcl_file_name] NVARCHAR (1024) NULL, CONSTRAINT [PK_AVS_SW_CLIENT] PRIMARY KEY CLUSTERED ([swcl_id] ASC) ); GO PRINT N'Creating [dbo].[AVS_SW_VERSION]...'; GO CREATE TABLE [dbo].[AVS_SW_VERSION] ( [swve_id] BIGINT IDENTITY (1, 1) NOT NULL, [swve_version] NVARCHAR (128) NOT NULL, [swve_date] DATETIME NOT NULL, [swve_swcl_id] BIGINT NOT NULL, CONSTRAINT [PK_AVS_SW_VERSION] PRIMARY KEY CLUSTERED ([swve_id] ASC) ); GO PRINT N'Creating [dbo].[AVS_USER]...'; GO CREATE TABLE [dbo].[AVS_USER] ( [user_key] BIGINT IDENTITY (1, 1) NOT NULL, [user_nick] NVARCHAR (128) NOT NULL, [user_password] NVARCHAR (128) NOT NULL, [user_registry_date] DATETIME NOT NULL, [user_mail] NVARCHAR (512) NOT NULL, [user_is_activate] INT NOT NULL, [user_role_id] BIGINT NOT NULL, [user_full_name] NVARCHAR (512) NOT NULL, [user_notify] INT NOT NULL, [user_last_login] DATETIME NOT NULL, [user_last_ip] NVARCHAR (128) NOT NULL, CONSTRAINT [PK_AVS_USER] PRIMARY KEY CLUSTERED ([user_key] ASC) ); GO PRINT N'Creating unnamed constraint on [dbo].[AVS_CLIENT]...'; GO ALTER TABLE [dbo].[AVS_CLIENT] ADD DEFAULT ((0)) FOR [clin_is_activate]; GO PRINT N'Creating unnamed constraint on [dbo].[AVS_LICENSE]...'; GO ALTER TABLE [dbo].[AVS_LICENSE] ADD DEFAULT ((0)) FOR [lice_state]; GO PRINT N'Creating unnamed constraint on [dbo].[AVS_SER_SESSION]...'; GO ALTER TABLE [dbo].[AVS_SER_SESSION] ADD DEFAULT ((0)) FOR [sese_has_video]; GO PRINT N'Creating unnamed constraint on [dbo].[AVS_SERVICE]...'; GO ALTER TABLE [dbo].[AVS_SERVICE] ADD DEFAULT ((0)) FOR [serv_is_activate]; GO PRINT N'Creating unnamed constraint on [dbo].[AVS_SERVICE]...'; GO ALTER TABLE [dbo].[AVS_SERVICE] ADD DEFAULT ((0)) FOR [serv_is_master]; GO PRINT N'Creating unnamed constraint on [dbo].[AVS_SPECIALIST]...'; GO ALTER TABLE [dbo].[AVS_SPECIALIST] ADD DEFAULT ((0)) FOR [spec_is_vip]; GO PRINT N'Creating unnamed constraint on [dbo].[AVS_SPECIALIST]...'; GO ALTER TABLE [dbo].[AVS_SPECIALIST] ADD DEFAULT ((0)) FOR [spec_is_audit]; GO PRINT N'Creating unnamed constraint on [dbo].[AVS_USER]...'; GO ALTER TABLE [dbo].[AVS_USER] ADD DEFAULT ((0)) FOR [user_is_activate]; GO PRINT N'Creating [dbo].[FK_AVS_AG_SESSION_AVS_SESSION]...'; GO ALTER TABLE [dbo].[AVS_AG_SESSION] WITH NOCHECK ADD CONSTRAINT [FK_AVS_AG_SESSION_AVS_SESSION] FOREIGN KEY ([agse_id]) REFERENCES [dbo].[AVS_SESSION] ([sess_id]); GO PRINT N'Creating [dbo].[FK_AVS_CD_KEY_AVS_CD_KEY]...'; GO ALTER TABLE [dbo].[AVS_CD_KEY] WITH NOCHECK ADD CONSTRAINT [FK_AVS_CD_KEY_AVS_CD_KEY] FOREIGN KEY ([cdke_user_key]) REFERENCES [dbo].[AVS_USER] ([user_key]); GO PRINT N'Creating [dbo].[FK_AVS_CLIENT_GATEWAY_AVS_CLIENT]...'; GO ALTER TABLE [dbo].[AVS_CLIENT_GATEWAY] WITH NOCHECK ADD CONSTRAINT [FK_AVS_CLIENT_GATEWAY_AVS_CLIENT] FOREIGN KEY ([clin_id]) REFERENCES [dbo].[AVS_CLIENT] ([clin_id]); GO PRINT N'Creating [dbo].[FK_AVS_CLIENT_GATEWAY_AVS_GATEWAY_BASE]...'; GO ALTER TABLE [dbo].[AVS_CLIENT_GATEWAY] WITH NOCHECK ADD CONSTRAINT [FK_AVS_CLIENT_GATEWAY_AVS_GATEWAY_BASE] FOREIGN KEY ([gate_id]) REFERENCES [dbo].[AVS_GATEWAY_BASE] ([gate_id]); GO PRINT N'Creating [dbo].[FK_AVS_COMPANY_AVS_CLIENT]...'; GO ALTER TABLE [dbo].[AVS_COMPANY] WITH NOCHECK ADD CONSTRAINT [FK_AVS_COMPANY_AVS_CLIENT] FOREIGN KEY ([comp_id]) REFERENCES [dbo].[AVS_CLIENT] ([clin_id]); GO PRINT N'Creating [dbo].[FK_AVS_COMPANY_AVS_TENANT]...'; GO ALTER TABLE [dbo].[AVS_COMPANY] WITH NOCHECK ADD CONSTRAINT [FK_AVS_COMPANY_AVS_TENANT] FOREIGN KEY ([comp_prov_id]) REFERENCES [dbo].[AVS_PROVIDER] ([prov_id]); GO PRINT N'Creating [dbo].[FK_AVS_COMPANY_ADMIN_AVS_COMPANY]...'; GO ALTER TABLE [dbo].[AVS_COMPANY_ADMIN] WITH NOCHECK ADD CONSTRAINT [FK_AVS_COMPANY_ADMIN_AVS_COMPANY] FOREIGN KEY ([coad_comp_id]) REFERENCES [dbo].[AVS_COMPANY] ([comp_id]); GO PRINT N'Creating [dbo].[FK_AVS_COMPANY_ADMIN_AVS_USER]...'; GO ALTER TABLE [dbo].[AVS_COMPANY_ADMIN] WITH NOCHECK ADD CONSTRAINT [FK_AVS_COMPANY_ADMIN_AVS_USER] FOREIGN KEY ([coad_id]) REFERENCES [dbo].[AVS_USER] ([user_key]); GO PRINT N'Creating [dbo].[FK_AVS_LICENSE_AVS_CLIENT]...'; GO ALTER TABLE [dbo].[AVS_LICENSE] WITH NOCHECK ADD CONSTRAINT [FK_AVS_LICENSE_AVS_CLIENT] FOREIGN KEY ([lice_client_id]) REFERENCES [dbo].[AVS_CLIENT] ([clin_id]); GO PRINT N'Creating [dbo].[FK_AVS_LICENSE_AVS_SERVICE]...'; GO ALTER TABLE [dbo].[AVS_LICENSE] WITH NOCHECK ADD CONSTRAINT [FK_AVS_LICENSE_AVS_SERVICE] FOREIGN KEY ([lice_serv_id]) REFERENCES [dbo].[AVS_SERVICE] ([serv_id]); GO PRINT N'Creating [dbo].[FK_AVS_PROVIDER_AVS_CLIENT]...'; GO ALTER TABLE [dbo].[AVS_PROVIDER] WITH NOCHECK ADD CONSTRAINT [FK_AVS_PROVIDER_AVS_CLIENT] FOREIGN KEY ([prov_id]) REFERENCES [dbo].[AVS_CLIENT] ([clin_id]); GO PRINT N'Creating [dbo].[FK_AVS_PROVIDER_ADMIN_AVS_USER]...'; GO ALTER TABLE [dbo].[AVS_PROVIDER_ADMIN] WITH NOCHECK ADD CONSTRAINT [FK_AVS_PROVIDER_ADMIN_AVS_USER] FOREIGN KEY ([prad_id]) REFERENCES [dbo].[AVS_USER] ([user_key]); GO PRINT N'Creating [dbo].[FK_AVS_TENANT_ADMIN_AVS_TENANT]...'; GO ALTER TABLE [dbo].[AVS_PROVIDER_ADMIN] WITH NOCHECK ADD CONSTRAINT [FK_AVS_TENANT_ADMIN_AVS_TENANT] FOREIGN KEY ([prad_prov_id]) REFERENCES [dbo].[AVS_PROVIDER] ([prov_id]); GO PRINT N'Creating [dbo].[FK_AVS_SER_SESSION_AVS_AG_SESSION]...'; GO ALTER TABLE [dbo].[AVS_SER_SESSION] WITH NOCHECK ADD CONSTRAINT [FK_AVS_SER_SESSION_AVS_AG_SESSION] FOREIGN KEY ([sese_agse_id]) REFERENCES [dbo].[AVS_AG_SESSION] ([agse_id]); GO PRINT N'Creating [dbo].[FK_AVS_SER_SESSION_AVS_SESSION]...'; GO ALTER TABLE [dbo].[AVS_SER_SESSION] WITH NOCHECK ADD CONSTRAINT [FK_AVS_SER_SESSION_AVS_SESSION] FOREIGN KEY ([sese_id]) REFERENCES [dbo].[AVS_SESSION] ([sess_id]); GO PRINT N'Creating [dbo].[FK_AVS_SER_SESSION_AVS_SPE_SESSION1]...'; GO ALTER TABLE [dbo].[AVS_SER_SESSION] WITH NOCHECK ADD CONSTRAINT [FK_AVS_SER_SESSION_AVS_SPE_SESSION1] FOREIGN KEY ([sese_spse_id]) REFERENCES [dbo].[AVS_SPE_SESSION] ([spse_id]); GO PRINT N'Creating [dbo].[FK_AVS_SESSION_GATEWAY]...'; GO ALTER TABLE [dbo].[AVS_SESSION] WITH NOCHECK ADD CONSTRAINT [FK_AVS_SESSION_GATEWAY] FOREIGN KEY ([sess_gate_id]) REFERENCES [dbo].[AVS_GATEWAY_BASE] ([gate_id]); GO PRINT N'Creating [dbo].[FK_AVS_SESSION_LICENSE]...'; GO ALTER TABLE [dbo].[AVS_SESSION] WITH NOCHECK ADD CONSTRAINT [FK_AVS_SESSION_LICENSE] FOREIGN KEY ([sess_lice_id]) REFERENCES [dbo].[AVS_LICENSE] ([lice_id]); GO PRINT N'Creating [dbo].[FK_AVS_SESSION_SERVICE]...'; GO ALTER TABLE [dbo].[AVS_SESSION] WITH NOCHECK ADD CONSTRAINT [FK_AVS_SESSION_SERVICE] FOREIGN KEY ([sess_serv_id]) REFERENCES [dbo].[AVS_SERVICE] ([serv_id]); GO PRINT N'Creating [dbo].[FK_AVS_SPE_SESSION_AVS_SESSION]...'; GO ALTER TABLE [dbo].[AVS_SPE_SESSION] WITH NOCHECK ADD CONSTRAINT [FK_AVS_SPE_SESSION_AVS_SESSION] FOREIGN KEY ([spse_id]) REFERENCES [dbo].[AVS_SESSION] ([sess_id]); GO PRINT N'Creating [dbo].[FK_AVS_SPE_SESSION_AVS_SPECIALIST]...'; GO ALTER TABLE [dbo].[AVS_SPE_SESSION] WITH NOCHECK ADD CONSTRAINT [FK_AVS_SPE_SESSION_AVS_SPECIALIST] FOREIGN KEY ([spse_spec_id]) REFERENCES [dbo].[AVS_SPECIALIST] ([spec_id]); GO PRINT N'Creating [dbo].[FK_AVS_SPECIALIST_AVS_COMPANY]...'; GO ALTER TABLE [dbo].[AVS_SPECIALIST] WITH NOCHECK ADD CONSTRAINT [FK_AVS_SPECIALIST_AVS_COMPANY] FOREIGN KEY ([spec_comp_id]) REFERENCES [dbo].[AVS_COMPANY] ([comp_id]); GO PRINT N'Creating [dbo].[FK_AVS_SPECIALIST_AVS_USER]...'; GO ALTER TABLE [dbo].[AVS_SPECIALIST] WITH NOCHECK ADD CONSTRAINT [FK_AVS_SPECIALIST_AVS_USER] FOREIGN KEY ([spec_id]) REFERENCES [dbo].[AVS_USER] ([user_key]); GO PRINT N'Creating [dbo].[FK_AVS_SUBLICENSE_AVS_LICENSE]...'; GO ALTER TABLE [dbo].[AVS_SUBLICENSE] WITH NOCHECK ADD CONSTRAINT [FK_AVS_SUBLICENSE_AVS_LICENSE] FOREIGN KEY ([subl_master]) REFERENCES [dbo].[AVS_LICENSE] ([lice_id]); GO PRINT N'Creating [dbo].[FK_AVS_SUBLICENSE_AVS_LICENSE1]...'; GO ALTER TABLE [dbo].[AVS_SUBLICENSE] WITH NOCHECK ADD CONSTRAINT [FK_AVS_SUBLICENSE_AVS_LICENSE1] FOREIGN KEY ([subl_child]) REFERENCES [dbo].[AVS_LICENSE] ([lice_id]); GO PRINT N'Creating [dbo].[FK_AVS_SUBSERVICE_AVS_SERVICE2]...'; GO ALTER TABLE [dbo].[AVS_SUBSERVICE] WITH NOCHECK ADD CONSTRAINT [FK_AVS_SUBSERVICE_AVS_SERVICE2] FOREIGN KEY ([subs_master]) REFERENCES [dbo].[AVS_SERVICE] ([serv_id]); GO PRINT N'Creating [dbo].[FK_AVS_SUBSERVICE_AVS_SERVICE3]...'; GO ALTER TABLE [dbo].[AVS_SUBSERVICE] WITH NOCHECK ADD CONSTRAINT [FK_AVS_SUBSERVICE_AVS_SERVICE3] FOREIGN KEY ([subs_child]) REFERENCES [dbo].[AVS_SERVICE] ([serv_id]); GO PRINT N'Creating [dbo].[FK_AVS_SW_VERSION_AVS_SW_CLIENT]...'; GO ALTER TABLE [dbo].[AVS_SW_VERSION] WITH NOCHECK ADD CONSTRAINT [FK_AVS_SW_VERSION_AVS_SW_CLIENT] FOREIGN KEY ([swve_swcl_id]) REFERENCES [dbo].[AVS_SW_CLIENT] ([swcl_id]); GO PRINT N'Creating [dbo].[FK_AVS_USER_AVS_ROLE]...'; GO ALTER TABLE [dbo].[AVS_USER] WITH NOCHECK ADD CONSTRAINT [FK_AVS_USER_AVS_ROLE] FOREIGN KEY ([user_role_id]) REFERENCES [dbo].[AVS_ROLE] ([role_id]); GO PRINT N'Creating [dbo].[PRC_AVS_ARANDA]...'; GO CREATE PROCEDURE [dbo].[PRC_AVS_ARANDA] ( /*=======================================================================*/ /* Archivo: PRC_AVS_Aranda.AVS.sql */ /* Stored procedure: PRC_AVS_ARANDA */ /* 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.AVS. */ /* 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 */ /* Clientes de Software soportados */ /* PARAMETROS DE ENTRADA: */ /* @i_aran_id : Identificador del parametro generado */ /* @i_aran_key : Llave del parametro generado */ /* @i_aran_value : Valor del parametro generado */ /* PARAMETROS DE SALIDA: */ /* @o_err_cod: Codigo de mensaje. Mayor a 0 si hay error */ /* @o_err_msg: Mensaje de error */ /* RETORNA: */ /* No hay retorno */ /*=======================================================================*/ @i_operation char(1), @i_option char(1), @i_aran_id bigint = null, @i_aran_key nvarchar(MAX) = null, @i_aran_value nvarchar(MAX) = null ) AS BEGIN /*========================================================*/ /* OPERACIONES DE CONSULTA */ /*========================================================*/ IF @i_operation = 'S' BEGIN /*========================================================*/ /* Opcion para listar todos los registros de parametro */ /*========================================================*/ IF @i_option ='A' BEGIN SELECT C.aran_id, C.aran_key, C.aran_value FROM AVS_ARANDA AS C END /*========================================================*/ /* Opcion para tener el Id del último parametro */ /*========================================================*/ IF @i_option ='B' BEGIN SELECT TOP 1 aran_id FROM AVS_ARANDA ORDER BY aran_id DESC END /*========================================================*/ /* Opcion revisar si existe el registro */ /*========================================================*/ IF @i_option ='C' BEGIN SELECT C.aran_id, C.aran_key, C.aran_value FROM AVS_ARANDA AS C WHERE C.aran_key = @i_aran_key END END /*========================================================*/ /* OPERACIONES DE INSERCION */ /*========================================================*/ IF @i_operation = 'I' BEGIN /*========================================================*/ /* Opcion para el registro de parametros */ /*========================================================*/ IF @i_option ='A' BEGIN IF NOT EXISTS( SELECT C.aran_id FROM AVS_ARANDA AS C WHERE C.aran_key = @i_aran_key) BEGIN INSERT INTO AVS_ARANDA (aran_key, aran_value) VALUES (@i_aran_key, @i_aran_value) END ELSE BEGIN UPDATE AVS_ARANDA SET aran_value = @i_aran_value WHERE aran_key = @i_aran_key END END END END GO PRINT N'Creating [dbo].[PRC_AVS_CD_KEY]...'; GO CREATE PROCEDURE [dbo].[PRC_AVS_CD_KEY] ( /*=======================================================================*/ /* Archivo: PRC_AVS_CD_KEY.sql */ /* Stored procedure: PRC_AVS_CD_KEY */ /* 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.AVS. */ /* 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 */ /* Clientes de Software soportados */ /* PARAMETROS DE ENTRADA: */ /* @i_cdke_id : Identificador del Cdkey generado */ /* @i_cdke_value : Valor del Cdkey generado */ /* @i_cdke_date : Fecha de Modificación del Cdkey */ /* @i_cdke_user_key : Identificador del Usuario que actualizó */ /* PARAMETROS DE SALIDA: */ /* @o_err_cod: Codigo de mensaje. Mayor a 0 si hay error */ /* @o_err_msg: Mensaje de error */ /* RETORNA: */ /* No hay retorno */ /*=======================================================================*/ @i_operation char(1), @i_option char(1), @i_cdke_id bigint = null, @i_cdke_value nvarchar(1024) = null, @i_cdke_date datetime = null, @i_cdke_user_key bigint = null ) AS BEGIN /*========================================================*/ /* OPERACIONES DE CONSULTA */ /*========================================================*/ IF @i_operation = 'S' BEGIN /*========================================================*/ /* Opcion para listar todos los registros de Cdkey */ /*========================================================*/ IF @i_option ='A' BEGIN SELECT C.cdke_id, C.cdke_value, C.cdke_date, C.cdke_user_key, (SELECT U.user_nick FROM AVS_USER AS U WHERE U.user_key = C.cdke_user_key) AS user_nick, (SELECT U.user_role_id FROM AVS_USER AS U WHERE U.user_key = C.cdke_user_key) AS user_role_id FROM AVS_CD_KEY AS C ORDER BY C.cdke_date DESC END /*========================================================*/ /* Opcion para tener el Id del último Cdkey */ /*========================================================*/ IF @i_option ='B' BEGIN SELECT TOP 1 cdke_id FROM AVS_CD_KEY ORDER BY cdke_id DESC END /*========================================================*/ /* Opcion tener el último Cdkey */ /*========================================================*/ IF @i_option ='C' BEGIN SELECT TOP 1 C.cdke_id, C.cdke_value, C.cdke_date, C.cdke_user_key, (SELECT U.user_nick FROM AVS_USER AS U WHERE U.user_key = C.cdke_user_key) AS user_nick, (SELECT U.user_role_id FROM AVS_USER AS U WHERE U.user_key = C.cdke_user_key) AS user_role_id FROM AVS_CD_KEY AS C ORDER BY C.cdke_date DESC END END /*========================================================*/ /* OPERACIONES DE INSERCION */ /*========================================================*/ IF @i_operation = 'I' BEGIN /*========================================================*/ /* Opcion para el registro de Cdkeys */ /*========================================================*/ IF @i_option ='A' BEGIN INSERT INTO AVS_CD_KEY (cdke_value, cdke_date, cdke_user_key) VALUES (@i_cdke_value, @i_cdke_date, @i_cdke_user_key) END END END GO PRINT N'Creating [dbo].[PRC_AVS_CLIENT]...'; GO CREATE PROCEDURE [dbo].[PRC_AVS_CLIENT] ( /*=======================================================================*/ /* Archivo: PRC_AVS_CLIENT.sql */ /* Stored procedure: PRC_AVS_CLIENT */ /* 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.AVS. */ /* 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 */ /* los Clientes */ /* PARAMETROS DE ENTRADA: */ /* @i_clin_id : identificador del cliente */ /* @i_clin_activate_date : fecha de activación */ /* @i_clin_is_activate: flag indica si está activo */ /* @i_clin_contact: información de contacto del cliente */ /* @i_clin_name: nombre del cliente */ /* @i_clin_code: código de identificación */ /* PARAMETROS DE SALIDA: */ /* @o_err_cod: Codigo de mensaje. Mayor a 0 si hay error */ /* @o_err_msg: Mensaje de error */ /* RETORNA: */ /* @o_bi_cli_id Identificador asignado al cliente */ /*=======================================================================*/ @i_operation char(1), @i_option char(1), @i_clin_id bigint, @i_clin_activate_date datetime, @i_clin_is_activate int, @i_clin_contact nvarchar(4000), @i_clin_name nvarchar(256), @i_clin_code nvarchar(128) ,@i_gate_id bigint = null ,@o_bi_cli_id bigint = null output ) AS BEGIN /*========================================================*/ /* OPERACIONES DE CONSULTA */ /*========================================================*/ IF @i_operation = 'S' BEGIN /*========================================================*/ /* Opcion para listar los atributos de Cliente */ /*========================================================*/ IF @i_option ='A' BEGIN SELECT clin_id, clin_activate_date, clin_is_activate, clin_contact, clin_name, clin_code FROM AVS_CLIENT END /*========================================================*/ /* Opcion para tener el Id del último Cliente */ /*========================================================*/ IF @i_option ='B' BEGIN SELECT TOP 1 clin_id FROM AVS_CLIENT ORDER BY clin_id DESC END END /*========================================================*/ /* OPERACIONES DE INSERCION */ /*========================================================*/ IF @i_operation = 'I' BEGIN /*========================================================*/ /* Opcion para el registro de Sesiones */ /*========================================================*/ IF @i_option ='A' BEGIN INSERT INTO AVS_CLIENT (clin_activate_date, clin_is_activate, clin_contact, clin_name, clin_code) VALUES (@i_clin_activate_date, @i_clin_is_activate, @i_clin_contact, @i_clin_name, @i_clin_code) -- Now return the sess_id of the newly inserted record SET @o_bi_cli_id = SCOPE_IDENTITY() INSERT INTO AVS_CLIENT_GATEWAY ( clin_id ,gate_id ) VALUES ( @o_bi_cli_id ,@i_gate_id ) END END /*========================================================*/ /* OPERACIONES DE ACTUALIZACION */ /*========================================================*/ IF @i_operation = 'U' BEGIN /*========================================================*/ /* Opcion para actualizar un Cliente */ /*========================================================*/ IF @i_option ='A' BEGIN UPDATE AVS_CLIENT SET clin_activate_date = @i_clin_activate_date, clin_is_activate = @i_clin_is_activate, clin_contact = @i_clin_contact, clin_name = @i_clin_name, clin_code = @i_clin_code WHERE clin_id = @i_clin_id; UPDATE AVS_CLIENT_GATEWAY SET gate_id = @i_gate_id WHERE clin_id = @i_clin_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_CLIENT_GATEWAY WHERE clin_id = @i_clin_id; DELETE FROM AVS_CLIENT WHERE clin_id = @i_clin_id; END END END GO PRINT N'Creating [dbo].[PRC_AVS_COMPANY]...'; GO CREATE PROCEDURE [dbo].[PRC_AVS_COMPANY] ( /*=======================================================================*/ /* Archivo: PRC_AVS_COMPANY.sql */ /* Stored procedure: PRC_AVS_COMPANY */ /* 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 */ /* las Empresas */ /* PARAMETROS DE ENTRADA: */ /* @i_clin_id : Identificador de la empresa */ /* @i_clin_activate_date : fecha de activación */ /* @i_clin_is_activate: flag indica si esta activo */ /* @i_clin_contact: información de contacto */ /* @i_clin_name: nombre de la Empresa */ /* @i_clin_code: código de identificación */ /* @i_comp_prov_id: Proveedor asociado */ /* PARAMETROS DE SALIDA: */ /* @o_err_cod: Codigo de mensaje. Mayor a 0 si hay error */ /* @o_err_msg: Mensaje de error */ /* RETORNA: */ /* @o_bi_cli_id Identificador asignado */ /*=======================================================================*/ @i_operation char(1), @i_option char(1), @i_clin_id bigint = null, @i_clin_activate_date datetime = null, @i_clin_is_activate int = null, @i_clin_contact nvarchar(4000) = null, @i_clin_name nvarchar(256) = null, @i_clin_code nvarchar(128) = null, @i_comp_prov_id bigint = null, @o_bi_cli_id bigint = null output, @i_gate_id bigint = null ) AS BEGIN /*========================================================*/ /* OPERACIONES DE CONSULTA */ /*========================================================*/ IF @i_operation = 'S' BEGIN /*========================================================*/ /* Opcion para listar los atributos de Empresas */ /*========================================================*/ IF @i_option ='A' BEGIN SELECT O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name, ISNULL(GB.gate_id,0) as gate_id, ISNULL(GB.gate_server_port,0) aS gate_server_port FROM AVS_COMPANY AS O JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id LEFT JOIN AVS_CLIENT_GATEWAY CG ON CG.clin_id = L.clin_id LEFT JOIN AVS_GATEWAY_BASE GB ON GB.gate_id = CG.gate_id END /*========================================================*/ /* Opcion busqueda Empresa por ID */ /*========================================================*/ IF @i_option ='B' BEGIN SELECT O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name, ISNULL(GB.gate_id,0) as gate_id, ISNULL(GB.gate_server_port,0) aS gate_server_port FROM AVS_COMPANY AS O JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id LEFT JOIN AVS_CLIENT_GATEWAY CG ON CG.clin_id = L.clin_id LEFT JOIN AVS_GATEWAY_BASE GB ON GB.gate_id = CG.gate_id WHERE O.comp_id = @i_clin_id END /*========================================================*/ /* Opcion busqueda Empresa por ID y ID de proveedor */ /*========================================================*/ IF @i_option ='C' BEGIN SELECT O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name, ISNULL(GB.gate_id,0) as gate_id, ISNULL(GB.gate_server_port,0) aS gate_server_port FROM AVS_COMPANY AS O JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id LEFT JOIN AVS_CLIENT_GATEWAY CG ON CG.clin_id = L.clin_id LEFT JOIN AVS_GATEWAY_BASE GB ON GB.gate_id = CG.gate_id WHERE O.comp_id = @i_clin_id AND O.comp_prov_id = @i_comp_prov_id END /*========================================================*/ /* Opcion busqueda Empresa por Company Code */ /*========================================================*/ IF @i_option ='D' BEGIN SELECT O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name, ISNULL(GB.gate_id,0) as gate_id, ISNULL(GB.gate_server_port,0) aS gate_server_port FROM AVS_COMPANY AS O JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id LEFT JOIN AVS_CLIENT_GATEWAY CG ON CG.clin_id = L.clin_id LEFT JOIN AVS_GATEWAY_BASE GB ON GB.gate_id = CG.gate_id where L.clin_code = @i_clin_code END /*========================================================*/ /* Opcion busqueda por ejemplo */ /*========================================================*/ IF @i_option ='E' BEGIN IF NOT @i_clin_is_activate IS NULL BEGIN IF NOT @i_comp_prov_id IS NULL BEGIN SELECT O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name, ISNULL(GB.gate_id,0) as gate_id, ISNULL(GB.gate_server_port,0) aS gate_server_port FROM AVS_COMPANY AS O JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id LEFT JOIN AVS_CLIENT_GATEWAY CG ON CG.clin_id = L.clin_id LEFT JOIN AVS_GATEWAY_BASE GB ON GB.gate_id = CG.gate_id WHERE L.clin_is_activate = @i_clin_is_activate AND O.comp_prov_id = @i_comp_prov_id END ELSE BEGIN SELECT O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name, ISNULL(GB.gate_id,0) as gate_id, ISNULL(GB.gate_server_port,0) aS gate_server_port FROM AVS_COMPANY AS O JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id LEFT JOIN AVS_CLIENT_GATEWAY CG ON CG.clin_id = L.clin_id LEFT JOIN AVS_GATEWAY_BASE GB ON GB.gate_id = CG.gate_id WHERE L.clin_is_activate = @i_clin_is_activate END END ELSE BEGIN IF NOT @i_comp_prov_id IS NULL BEGIN SELECT O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name, ISNULL(GB.gate_id,0) as gate_id, ISNULL(GB.gate_server_port,0) aS gate_server_port FROM AVS_COMPANY AS O JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id LEFT JOIN AVS_CLIENT_GATEWAY CG ON CG.clin_id = L.clin_id LEFT JOIN AVS_GATEWAY_BASE GB ON GB.gate_id = CG.gate_id WHERE O.comp_prov_id = @i_comp_prov_id END ELSE BEGIN SELECT O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name, ISNULL(GB.gate_id,0) as gate_id, ISNULL(GB.gate_server_port,0) aS gate_server_port FROM AVS_COMPANY AS O JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id LEFT JOIN AVS_CLIENT_GATEWAY CG ON CG.clin_id = L.clin_id LEFT JOIN AVS_GATEWAY_BASE GB ON GB.gate_id = CG.gate_id END END END /*========================================================*/ /* Opcion para listar los clientes activos del Sistema */ /*========================================================*/ IF @i_option ='F' BEGIN SELECT clin_id, clin_name FROM AVS_CLIENT WHERE clin_is_activate = 1 END END /*========================================================*/ /* OPERACIONES DE INSERCION */ /*========================================================*/ IF @i_operation = 'I' BEGIN /*========================================================*/ /* Opcion para el registro de Empresas */ /*========================================================*/ IF @i_option ='A' BEGIN EXECUTE PRC_AVS_CLIENT @i_operation, @i_option, @i_clin_id, @i_clin_activate_date, @i_clin_is_activate, @i_clin_contact, @i_clin_name, @i_clin_code, @i_gate_id, @o_bi_cli_id output INSERT INTO AVS_COMPANY (comp_id, comp_prov_id) VALUES (@o_bi_cli_id, @i_comp_prov_id) END END /*========================================================*/ /* OPERACIONES DE ACTUALIZACION */ /*========================================================*/ IF @i_operation = 'U' BEGIN /*========================================================*/ /* Opcion para actualizar una Empresa */ /*========================================================*/ IF @i_option ='A' BEGIN EXECUTE PRC_AVS_CLIENT @i_operation, @i_option, @i_clin_id, @i_clin_activate_date, @i_clin_is_activate, @i_clin_contact, @i_clin_name, @i_clin_code, @i_gate_id UPDATE AVS_COMPANY SET comp_prov_id = @i_comp_prov_id WHERE comp_id = @i_clin_id END END /*========================================================*/ /* OPERACIONES DE ELIMINACION */ /*========================================================*/ IF @i_operation = 'D' BEGIN /*========================================================*/ /* Opcion para eliminar una Empresa */ /*========================================================*/ IF @i_option ='A' BEGIN DELETE FROM AVS_COMPANY WHERE comp_id = @i_clin_id EXECUTE PRC_AVS_CLIENT @i_operation, @i_option, @i_clin_id, @i_clin_activate_date, @i_clin_is_activate, @i_clin_contact, @i_clin_name, @i_clin_code, @i_gate_id END END END GO PRINT N'Creating [dbo].[PRC_AVS_GATEWAY_BASE]...'; GO CREATE PROCEDURE [dbo].[PRC_AVS_GATEWAY_BASE] ( /*=======================================================================*/ /* Archivo: PRC_AVS_GATEWAY_BASE.sql */ /* Stored procedure: PRC_AVS_GATEWAY_BASE */ /* 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 */ /* configuración del Gateway */ /* PARAMETROS DE ENTRADA: */ /* @i_gate_server_port : puerto escucha del socket servidor */ /* @i_gate_admin_port : puerto escucha del administrador */ /* @i_gate_keep_alive: tiempo de keep alive de conexión */ /* @i_gate_console_page_size: tamaño paquete de consolas */ /* @i_gate_agent_page_size: tamaño de paquete de agentes */ /* @i_gate_service_page_size: tamaña de paquete de servicios */ /* @i_gate_version: versión del gateway */ /* @i_gate_last_change: ultimo cambio de estado del gateway */ /* @i_gate_last_state: fecha del último cambio de estado */ /* @i_gate_last_update: fecha de última actualización */ /* @i_gate_last_username: nombre de usuario último cambio */ /* @i_gate_audit_time: tiempo de captura de imagen de auditoria */ /* @i_gate_wait_time: tiempo de espera para comenzar servicio */ /* @i_gate_enable_ssl: flag que indica si está habilitado SSL */ /* @i_gate_cert_subject: CN del Certificado Digital */ /* @i_gate_name: Nombre de la instalación del Gateway */ /* @i_gate_activate: Activación de la instalación del Gateway */ /* @i_gate_audit_url: Url de auditoria */ /* @i_gate_install_url: Url de descarga de clientes SW */ /* @i_gate_host: Host donde corre el Gateway */ /* PARAMETROS DE SALIDA: */ /* @o_err_cod: Codigo de mensaje. Mayor a 0 si hay error */ /* @o_err_msg: Mensaje de error */ /* RETORNA: */ /* No hay retorno */ /*=======================================================================*/ @i_operation char(1), @i_option char(1), @i_gate_id bigint = null, @i_gate_server_port int = null, @i_gate_console_page_size int = null, @i_gate_agent_page_size int = null, @i_gate_service_page_size int = null, @i_gate_version nvarchar(128) = null, @i_gate_last_change datetime = null, @i_gate_last_state int = null, @i_gate_last_update datetime = null, @i_gate_last_username nvarchar(128) = null, @i_gate_audit_time int = null, @i_gate_wait_time int = null, @i_gate_enable_ssl int = null, @i_gate_cert_subject nvarchar(512) = null, @i_gate_name nvarchar(1024) = null, @i_gate_activate nvarchar(1024) = null, @i_gate_audit_url nvarchar(2048) = null, @i_gate_install_url nvarchar(2048) = null, @i_gate_host nvarchar(2048) = null, @i_gate_description nvarchar(2048) = null, @i_company_code nvarchar(512) = null ) AS declare @v_gate_id int; BEGIN /*========================================================*/ /* OPERACIONES DE CONSULTA */ /*========================================================*/ IF @i_operation = 'S' BEGIN /*========================================================*/ /* Opcion para listar los atributos del Gateway */ /*========================================================*/ IF @i_option ='A' BEGIN IF @i_gate_id IS NOT NULL BEGIN SELECT gate_id, gate_server_port, gate_console_page_size, gate_agent_page_size, gate_service_page_size, gate_version, gate_last_change, gate_last_state, gate_last_update, gate_last_username, gate_audit_time, gate_wait_time, gate_enable_ssl, gate_cert_subject, gate_name, gate_activate, gate_audit_url, gate_install_url, gate_host, gate_description FROM AVS_GATEWAY_BASE WHERE gate_id = @i_gate_id END ELSE BEGIN SELECT TOP 1 gate_id, gate_server_port, gate_console_page_size, gate_agent_page_size, gate_service_page_size, gate_version, gate_last_change, gate_last_state, gate_last_update, gate_last_username, gate_audit_time, gate_wait_time, gate_enable_ssl, gate_cert_subject, gate_name, gate_activate, gate_audit_url, gate_install_url, gate_host, gate_description FROM AVS_GATEWAY_BASE END END /*====================================================================*/ /* Opcion para listar todas las configuraciones del Gateway */ /*====================================================================*/ IF @i_option='B' BEGIN SELECT gate_id, gate_server_port, gate_console_page_size, gate_agent_page_size, gate_service_page_size, gate_version, gate_last_change, gate_last_state, gate_last_update, gate_last_username, gate_audit_time, gate_wait_time, gate_enable_ssl, gate_cert_subject, gate_name, gate_activate, gate_audit_url, gate_install_url, gate_host, gate_description FROM AVS_GATEWAY_BASE END /*====================================================================*/ /* Opcion para listar configuración del Gateway por nombre */ /*====================================================================*/ IF @i_option='C' BEGIN SELECT gate_id, gate_server_port, gate_console_page_size, gate_agent_page_size, gate_service_page_size, gate_version, gate_last_change, gate_last_state, gate_last_update, gate_last_username, gate_audit_time, gate_wait_time, gate_enable_ssl, gate_cert_subject, gate_name, gate_activate, gate_audit_url, gate_install_url, gate_host, gate_description FROM AVS_GATEWAY_BASE WHERE gate_description = @i_gate_description; END /*====================================================================*/ /* Opcion para listar configuración del Gateway por codigo empresa*/ /*====================================================================*/ IF @i_option='D' BEGIN SELECT AGB.gate_id, AGB.gate_server_port, AGB.gate_console_page_size, AGB.gate_agent_page_size, AGB.gate_service_page_size, AGB.gate_version, AGB.gate_last_change, AGB.gate_last_state, AGB.gate_last_update, AGB.gate_last_username, AGB.gate_audit_time, AGB.gate_wait_time, AGB.gate_enable_ssl, AGB.gate_cert_subject, AGB.gate_name, AGB.gate_activate, AGB.gate_audit_url, AGB.gate_install_url, AGB.gate_host, AGB.gate_description FROM AVS_CLIENT AC INNER JOIN AVS_CLIENT_GATEWAY ACG ON ACG.clin_id = AC.clin_id INNER JOIN AVS_GATEWAY_BASE AGB ON AGB.gate_id = ACG.gate_id WHERE AC.clin_code = @i_company_code END /*====================================================================*/ /* Opcion para listar configuración del Gateway por host y puerto */ /*====================================================================*/ IF @i_option='E' BEGIN IF @i_gate_id > 0 BEGIN SELECT gate_id, gate_server_port, gate_console_page_size, gate_agent_page_size, gate_service_page_size, gate_version, gate_last_change, gate_last_state, gate_last_update, gate_last_username, gate_audit_time, gate_wait_time, gate_enable_ssl, gate_cert_subject, gate_name, gate_activate, gate_audit_url, gate_install_url, gate_host, gate_description FROM AVS_GATEWAY_BASE WHERE gate_host = @i_gate_host AND gate_server_port = @i_gate_server_port AND gate_id <> @i_gate_id; END ELSE BEGIN SELECT gate_id, gate_server_port, gate_console_page_size, gate_agent_page_size, gate_service_page_size, gate_version, gate_last_change, gate_last_state, gate_last_update, gate_last_username, gate_audit_time, gate_wait_time, gate_enable_ssl, gate_cert_subject, gate_name, gate_activate, gate_audit_url, gate_install_url, gate_host, gate_description FROM AVS_GATEWAY_BASE WHERE gate_host = @i_gate_host AND gate_server_port = @i_gate_server_port; END; END END /*========================================================*/ /* OPERACIONES DE ACTUALIZACION */ /*========================================================*/ IF @i_operation = 'U' BEGIN /*========================================================*/ /* Opcion para actualizar de los atributos del Gateway */ /*========================================================*/ IF @i_option ='A' BEGIN UPDATE AVS_GATEWAY_BASE SET gate_server_port = @i_gate_server_port, gate_console_page_size = @i_gate_console_page_size, gate_agent_page_size = @i_gate_agent_page_size, gate_service_page_size = @i_gate_service_page_size, gate_last_update = @i_gate_last_update, gate_last_username = @i_gate_last_username, gate_audit_time = @i_gate_audit_time, gate_wait_time = @i_gate_wait_time, gate_enable_ssl = @i_gate_enable_ssl, gate_cert_subject = @i_gate_cert_subject, gate_host = @i_gate_host, gate_description =@i_gate_description WHERE gate_id = @i_gate_id END /*========================================================*/ /* Opcion para actualizar la fecha de cambio de Estado */ /*========================================================*/ IF @i_option ='B' BEGIN UPDATE AVS_GATEWAY_BASE SET gate_last_change = @i_gate_last_change, gate_last_state = @i_gate_last_state WHERE gate_id = @i_gate_id; END /*========================================================*/ /* Opcion para actualizar el nombre del Gateway */ /*========================================================*/ IF @i_option ='C' BEGIN UPDATE AVS_GATEWAY_BASE SET gate_name = @i_gate_name, gate_activate = @i_gate_activate; END /*========================================================*/ /* Opcion para actualizar la URL de Auditoria */ /*========================================================*/ IF @i_option ='D' BEGIN UPDATE AVS_GATEWAY_BASE SET gate_audit_url = @i_gate_audit_url, gate_install_url = @i_gate_install_url END END IF @i_operation = 'I' BEGIN /*========================================================*/ /* Opcion para insertar nuevos gateways */ /*========================================================*/ IF @i_option ='A' BEGIN DECLARE @VAR_GATE_NAME NVARCHAR(1024); DECLARE @VAR_GATE_ACTIVATE NVARCHAR(1024); DECLARE @VAR_GATE_AUDIT_URL NVARCHAR(1024); DECLARE @VAR_GATE_INSTALL_URL NVARCHAR(1024); SELECT TOP 1 @VAR_GATE_NAME = gate_name from AVS_GATEWAY_BASE; SELECT TOP 1 @VAR_GATE_ACTIVATE = gate_activate from AVS_GATEWAY_BASE; SELECT TOP 1 @VAR_GATE_AUDIT_URL = gate_audit_url from AVS_GATEWAY_BASE; SELECT TOP 1 @VAR_GATE_INSTALL_URL = gate_install_url from AVS_GATEWAY_BASE; INSERT INTO AVS_GATEWAY_BASE ( gate_server_port ,gate_console_page_size ,gate_agent_page_size ,gate_service_page_size ,gate_version ,gate_last_change ,gate_last_state ,gate_last_update ,gate_last_username ,gate_audit_time ,gate_wait_time ,gate_enable_ssl ,gate_cert_subject ,gate_name ,gate_activate ,gate_audit_url ,gate_install_url ,gate_host ,gate_description ) VALUES ( @i_gate_server_port ,5 ,5 ,5 ,'8.0' ,'2010-01-01' ,0 ,'2010-01-01' ,'' ,500 ,@i_gate_wait_time ,@i_gate_enable_ssl ,@i_gate_cert_subject ,@VAR_GATE_NAME ,@VAR_GATE_ACTIVATE ,@VAR_GATE_AUDIT_URL ,@VAR_GATE_INSTALL_URL ,@i_gate_host ,@i_gate_description ) END END IF @i_operation = 'D' BEGIN IF @i_option = 'A' BEGIN SELECT @v_gate_id = COUNT(GATE_ID) FROM AVS_GATEWAY_BASE; IF @v_gate_id > 1 BEGIN DELETE FROM AVS_GATEWAY_BASE WHERE gate_id = @i_gate_id; END; END; END; END GO PRINT N'Creating [dbo].[PRC_AVS_LICENSE]...'; GO CREATE PROCEDURE PRC_AVS_LICENSE ( /*=======================================================================*/ /* Archivo: PRC_AVS_LICENSE.sql */ /* Stored procedure: PRC_AVS_LICENSE */ /* 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 */ /* Licencias de Servicio */ /* PARAMETROS DE ENTRADA: */ /* @i_lice_id : identificador de licencia */ /* @i_lice_serial_number : número serial */ /* @i_lice_serial_number_two : número serial */ /* @i_lice_serial_number_three : número serial */ /* @i_lice_type : entero que representa el tipo de licencia */ /* @i_lice_client_id: cliente asociado */ /* @i_lice_serv_id: servicio asociado */ /* @i_lice_agent_quantity: cantidad de agentes */ /* @i_lice_console_quantity: cantidad de consolas */ /* @i_lice_vip_quantity: cantidad de consolas VIP */ /* @i_lice_descrition: descripción de la licencia */ /* @i_lice_end_date: fecha de vencimiento */ /* @i_lice_start_date: fecha de inicio */ /* @i_lice_is_available: Flag que indica si es Principal */ /* @i_lice_agent_available: cantidad de agentes disponibles */ /* @i_lice_console_available: cantidad de consolas disponibles */ /* @i_lice_vip_quantity: cantidad de consolas VIP disponibles */ /* @i_lice_state: estado de consumo de la licencia */ /* @i_serv_name: nombre de servicio */ /* @i_serv_code: código del servicio */ /* @i_serv_is_activate: flag indica si servicio activo */ /* @i_subl_child: identificador de sublicencia */ /* @i_subl_master: identificador de licencia principal */ /* PARAMETROS DE SALIDA: */ /* @o_err_cod: Codigo de mensaje. Mayor a 0 si hay error */ /* @o_err_msg: Mensaje de error */ /* RETORNA: */ /* No hay retorno */ /*=======================================================================*/ @i_operation char(1), @i_option char(1), @i_lice_id bigint = null, @i_lice_serial_number nvarchar(1024) = null, @i_lice_serial_number_two nvarchar(1024) = null, @i_lice_serial_number_three nvarchar(1024) = null, @i_lice_type int = null, @i_lice_client_id bigint = null, @i_lice_serv_id bigint = null, @i_lice_agent_quantity int = null, @i_lice_console_quantity int = null, @i_lice_vip_quantity int = null, @i_lice_descrition nvarchar(4000) = null, @i_lice_end_date datetime = null, @i_lice_start_date datetime = null, @i_lice_is_master int = null, @i_lice_agent_available int = null, @i_lice_console_available int = null, @i_lice_vip_available int = null, @i_lice_state int = null, @i_serv_name nvarchar(128) = null, @i_serv_code nvarchar(128) = null, @i_serv_is_activate int = null, @i_subl_child bigint = null, @i_subl_master bigint = null, @o_lice_id bigint = null output ) AS BEGIN /*========================================================*/ /* OPERACIONES DE CONSULTA */ /*========================================================*/ IF @i_operation = 'S' BEGIN /*========================================================*/ /* Opcion para listar las Licencias */ /*========================================================*/ IF @i_option ='A' BEGIN SELECT L.lice_id, L.lice_serial_number, L.lice_serial_number_two, L.lice_serial_number_three, L.lice_type, L.lice_client_id, L.lice_serv_id, L.lice_agent_quantity, L.lice_console_quantity, L.lice_vip_quantity, L.lice_descrition, L.lice_end_date, L.lice_start_date, L.lice_is_master, L.lice_agent_available, L.lice_console_available, L.lice_vip_available, L.lice_state, S.serv_name, S.serv_code, S.serv_description, S.serv_is_activate, (SELECT U.subl_master FROM AVS_SUBLICENSE AS U WHERE U.subl_child = L.lice_id) AS subl_master, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = L.lice_client_id) AS clin_name, (SELECT P.prov_id FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS I ON I.clin_id = P.prov_id JOIN AVS_COMPANY AS O ON P.prov_id = O.comp_prov_id WHERE O.comp_id = L.lice_client_id) AS prov_id, (SELECT I.clin_name FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS I ON I.clin_id = P.prov_id JOIN AVS_COMPANY AS O ON P.prov_id = O.comp_prov_id WHERE (O.comp_id = L.lice_client_id)) AS prov_name, (SELECT C.aran_value FROM AVS_ARANDA AS C WHERE C.aran_key = L.lice_serial_number) AS lice_value FROM AVS_LICENSE AS L JOIN AVS_SERVICE AS S ON L.lice_serv_id = S.serv_id END /*========================================================*/ /* Opcion Licencia por identificador */ /*========================================================*/ IF @i_option ='B' BEGIN SELECT L.lice_id, L.lice_serial_number, L.lice_serial_number_two, L.lice_serial_number_three, L.lice_type, L.lice_client_id, L.lice_serv_id, L.lice_agent_quantity, L.lice_console_quantity, L.lice_vip_quantity, L.lice_descrition, L.lice_end_date, L.lice_start_date, L.lice_is_master, L.lice_agent_available, L.lice_console_available, L.lice_vip_available, L.lice_state, S.serv_name, S.serv_code, S.serv_description, S.serv_is_activate, (SELECT U.subl_master FROM AVS_SUBLICENSE AS U WHERE U.subl_child = L.lice_id) AS subl_master, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = L.lice_client_id) AS clin_name, (SELECT P.prov_id FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS I ON I.clin_id = P.prov_id JOIN AVS_COMPANY AS O ON P.prov_id = O.comp_prov_id WHERE O.comp_id = L.lice_client_id) AS prov_id, (SELECT I.clin_name FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS I ON I.clin_id = P.prov_id JOIN AVS_COMPANY AS O ON P.prov_id = O.comp_prov_id WHERE (O.comp_id = L.lice_client_id)) AS prov_name, (SELECT C.aran_value FROM AVS_ARANDA AS C WHERE C.aran_key = L.lice_serial_number) AS lice_value FROM AVS_LICENSE AS L JOIN AVS_SERVICE AS S ON L.lice_serv_id = S.serv_id WHERE L.lice_id = @i_lice_id END /*========================================================*/ /* Opcion Licencia por serial */ /*========================================================*/ IF @i_option ='C' BEGIN SELECT L.lice_id, L.lice_serial_number, L.lice_serial_number_two, L.lice_serial_number_three, L.lice_type, L.lice_client_id, L.lice_serv_id, L.lice_agent_quantity, L.lice_console_quantity, L.lice_vip_quantity, L.lice_descrition, L.lice_end_date, L.lice_start_date, L.lice_is_master, L.lice_agent_available, L.lice_console_available, L.lice_vip_available, L.lice_state, S.serv_name, S.serv_code, S.serv_description, S.serv_is_activate, (SELECT U.subl_master FROM AVS_SUBLICENSE AS U WHERE U.subl_child = L.lice_id) AS subl_master, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = L.lice_client_id) AS clin_name, (SELECT P.prov_id FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS I ON I.clin_id = P.prov_id JOIN AVS_COMPANY AS O ON P.prov_id = O.comp_prov_id WHERE O.comp_id = L.lice_client_id) AS prov_id, (SELECT I.clin_name FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS I ON I.clin_id = P.prov_id JOIN AVS_COMPANY AS O ON P.prov_id = O.comp_prov_id WHERE (O.comp_id = L.lice_client_id)) AS prov_name, (SELECT C.aran_value FROM AVS_ARANDA AS C WHERE C.aran_key = L.lice_serial_number) AS lice_value FROM AVS_LICENSE AS L JOIN AVS_SERVICE AS S ON L.lice_serv_id = S.serv_id WHERE L.lice_serial_number = @i_lice_serial_number END /*===========================================================*/ /* Opcion Licencia por identificador de cliente y master/sub */ /*===========================================================*/ IF @i_option ='D' BEGIN IF NOT @i_lice_client_id IS NULL BEGIN SELECT L.lice_id, L.lice_serial_number, L.lice_serial_number_two, L.lice_serial_number_three, L.lice_type, L.lice_client_id, L.lice_serv_id, L.lice_agent_quantity, L.lice_console_quantity, L.lice_vip_quantity, L.lice_descrition, L.lice_end_date, L.lice_start_date, L.lice_is_master, L.lice_agent_available, L.lice_console_available, L.lice_vip_available, L.lice_state, S.serv_name, S.serv_code, S.serv_description, S.serv_is_activate, (SELECT U.subl_master FROM AVS_SUBLICENSE AS U WHERE U.subl_child = L.lice_id) AS subl_master, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = L.lice_client_id) AS clin_name, (SELECT P.prov_id FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS I ON I.clin_id = P.prov_id JOIN AVS_COMPANY AS O ON P.prov_id = O.comp_prov_id WHERE O.comp_id = L.lice_client_id) AS prov_id, (SELECT I.clin_name FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS I ON I.clin_id = P.prov_id JOIN AVS_COMPANY AS O ON P.prov_id = O.comp_prov_id WHERE (O.comp_id = L.lice_client_id)) AS prov_name, (SELECT C.aran_value FROM AVS_ARANDA AS C WHERE C.aran_key = L.lice_serial_number) AS lice_value FROM AVS_LICENSE AS L JOIN AVS_SERVICE AS S ON L.lice_serv_id = S.serv_id WHERE L.lice_client_id = @i_lice_client_id AND L.lice_is_master = @i_lice_is_master END ELSE BEGIN SELECT L.lice_id, L.lice_serial_number, L.lice_serial_number_two, L.lice_serial_number_three, L.lice_type, L.lice_client_id, L.lice_serv_id, L.lice_agent_quantity, L.lice_console_quantity, L.lice_vip_quantity, L.lice_descrition, L.lice_end_date, L.lice_start_date, L.lice_is_master, L.lice_agent_available, L.lice_console_available, L.lice_vip_available, L.lice_state, S.serv_name, S.serv_code, S.serv_description, S.serv_is_activate, (SELECT U.subl_master FROM AVS_SUBLICENSE AS U WHERE U.subl_child = L.lice_id) AS subl_master, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = L.lice_client_id) AS clin_name, (SELECT P.prov_id FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS I ON I.clin_id = P.prov_id JOIN AVS_COMPANY AS O ON P.prov_id = O.comp_prov_id WHERE O.comp_id = L.lice_client_id) AS prov_id, (SELECT I.clin_name FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS I ON I.clin_id = P.prov_id JOIN AVS_COMPANY AS O ON P.prov_id = O.comp_prov_id WHERE (O.comp_id = L.lice_client_id)) AS prov_name, (SELECT C.aran_value FROM AVS_ARANDA AS C WHERE C.aran_key = L.lice_serial_number) AS lice_value FROM AVS_LICENSE AS L JOIN AVS_SERVICE AS S ON L.lice_serv_id = S.serv_id WHERE L.lice_is_master = @i_lice_is_master END END /*============================================================*/ /* Opcion Licencia por identificador de servicio y master/sub */ /*============================================================*/ IF @i_option ='E' BEGIN IF NOT @i_lice_is_master IS NULL BEGIN SELECT L.lice_id, L.lice_serial_number, L.lice_serial_number_two, L.lice_serial_number_three, L.lice_type, L.lice_client_id, L.lice_serv_id, L.lice_agent_quantity, L.lice_console_quantity, L.lice_vip_quantity, L.lice_descrition, L.lice_end_date, L.lice_start_date, L.lice_is_master, L.lice_agent_available, L.lice_console_available, L.lice_vip_available, L.lice_state, S.serv_name, S.serv_code, S.serv_description, S.serv_is_activate, (SELECT U.subl_master FROM AVS_SUBLICENSE AS U WHERE U.subl_child = L.lice_id) AS subl_master, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = L.lice_client_id) AS clin_name, (SELECT P.prov_id FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS I ON I.clin_id = P.prov_id JOIN AVS_COMPANY AS O ON P.prov_id = O.comp_prov_id WHERE O.comp_id = L.lice_client_id) AS prov_id, (SELECT I.clin_name FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS I ON I.clin_id = P.prov_id JOIN AVS_COMPANY AS O ON P.prov_id = O.comp_prov_id WHERE (O.comp_id = L.lice_client_id)) AS prov_name, (SELECT C.aran_value FROM AVS_ARANDA AS C WHERE C.aran_key = L.lice_serial_number) AS lice_value FROM AVS_LICENSE AS L JOIN AVS_SERVICE AS S ON L.lice_serv_id = S.serv_id WHERE L.lice_serv_id = @i_lice_serv_id AND L.lice_is_master = @i_lice_is_master END ELSE IF NOT @i_lice_serv_id IS NULL BEGIN SELECT L.lice_id, L.lice_serial_number, L.lice_serial_number_two, L.lice_serial_number_three, L.lice_type, L.lice_client_id, L.lice_serv_id, L.lice_agent_quantity, L.lice_console_quantity, L.lice_vip_quantity, L.lice_descrition, L.lice_end_date, L.lice_start_date, L.lice_is_master, L.lice_agent_available, L.lice_console_available, L.lice_vip_available, L.lice_state, S.serv_name, S.serv_code, S.serv_description, S.serv_is_activate, (SELECT U.subl_master FROM AVS_SUBLICENSE AS U WHERE U.subl_child = L.lice_id) AS subl_master, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = L.lice_client_id) AS clin_name, (SELECT P.prov_id FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS I ON I.clin_id = P.prov_id JOIN AVS_COMPANY AS O ON P.prov_id = O.comp_prov_id WHERE O.comp_id = L.lice_client_id) AS prov_id, (SELECT I.clin_name FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS I ON I.clin_id = P.prov_id JOIN AVS_COMPANY AS O ON P.prov_id = O.comp_prov_id WHERE (O.comp_id = L.lice_client_id)) AS prov_name, (SELECT C.aran_value FROM AVS_ARANDA AS C WHERE C.aran_key = L.lice_serial_number) AS lice_value FROM AVS_LICENSE AS L JOIN AVS_SERVICE AS S ON L.lice_serv_id = S.serv_id WHERE L.lice_serv_id = @i_lice_serv_id END END /*============================================================*/ /* Opcion Licencia por identificador de maestra */ /*============================================================*/ IF @i_option ='F' BEGIN IF NOT @i_lice_is_master IS NULL BEGIN SELECT L.lice_id, L.lice_serial_number, L.lice_serial_number_two, L.lice_serial_number_three, L.lice_type, L.lice_client_id, L.lice_serv_id, L.lice_agent_quantity, L.lice_console_quantity, L.lice_vip_quantity, L.lice_descrition, L.lice_end_date, L.lice_start_date, L.lice_is_master, L.lice_agent_available, L.lice_console_available, L.lice_vip_available, L.lice_state, S.serv_name, S.serv_code, S.serv_description, S.serv_is_activate, (SELECT U.subl_master FROM AVS_SUBLICENSE AS U WHERE U.subl_child = L.lice_id) AS subl_master, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = L.lice_client_id) AS clin_name, (SELECT P.prov_id FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS I ON I.clin_id = P.prov_id JOIN AVS_COMPANY AS O ON P.prov_id = O.comp_prov_id WHERE O.comp_id = L.lice_client_id) AS prov_id, (SELECT I.clin_name FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS I ON I.clin_id = P.prov_id JOIN AVS_COMPANY AS O ON P.prov_id = O.comp_prov_id WHERE (O.comp_id = L.lice_client_id)) AS prov_name, (SELECT C.aran_value FROM AVS_ARANDA AS C WHERE C.aran_key = L.lice_serial_number) AS lice_value FROM AVS_LICENSE AS L JOIN AVS_SERVICE AS S ON L.lice_serv_id = S.serv_id JOIN AVS_SUBLICENSE AS U ON L.lice_id = U.subl_child AND U.subl_master = @i_subl_master WHERE L.lice_is_master = @i_lice_is_master END ELSE IF NOT @i_lice_serv_id IS NULL BEGIN SELECT L.lice_id, L.lice_serial_number, L.lice_serial_number_two, L.lice_serial_number_three, L.lice_type, L.lice_client_id, L.lice_serv_id, L.lice_agent_quantity, L.lice_console_quantity, L.lice_vip_quantity, L.lice_descrition, L.lice_end_date, L.lice_start_date, L.lice_is_master, L.lice_agent_available, L.lice_console_available, L.lice_vip_available, L.lice_state, S.serv_name, S.serv_code, S.serv_description, S.serv_is_activate, (SELECT U.subl_master FROM AVS_SUBLICENSE AS U WHERE U.subl_child = L.lice_id) AS subl_master, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = L.lice_client_id) AS clin_name, (SELECT P.prov_id FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS I ON I.clin_id = P.prov_id JOIN AVS_COMPANY AS O ON P.prov_id = O.comp_prov_id WHERE O.comp_id = L.lice_client_id) AS prov_id, (SELECT I.clin_name FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS I ON I.clin_id = P.prov_id JOIN AVS_COMPANY AS O ON P.prov_id = O.comp_prov_id WHERE (O.comp_id = L.lice_client_id)) AS prov_name, (SELECT C.aran_value FROM AVS_ARANDA AS C WHERE C.aran_key = L.lice_serial_number) AS lice_value FROM AVS_LICENSE AS L JOIN AVS_SERVICE AS S ON L.lice_serv_id = S.serv_id JOIN AVS_SUBLICENSE AS U ON L.lice_id = U.subl_child AND U.subl_master = @i_subl_master END END /*========================================================*/ /* Opcion Licencia para validación en Gateway */ /*========================================================*/ IF @i_option ='G' BEGIN SELECT L.lice_id, L.lice_serial_number, L.lice_serial_number_two, L.lice_serial_number_three, L.lice_type, L.lice_client_id, L.lice_serv_id, L.lice_agent_quantity, L.lice_console_quantity, L.lice_vip_quantity, L.lice_descrition, L.lice_end_date, L.lice_start_date, L.lice_is_master, L.lice_agent_available, L.lice_console_available, L.lice_vip_available, L.lice_state, S.serv_name, S.serv_code, S.serv_description, S.serv_is_activate, (SELECT U.subl_master FROM AVS_SUBLICENSE AS U WHERE U.subl_child = L.lice_id) AS subl_master, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = L.lice_client_id) AS clin_name, (SELECT P.prov_id FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS I ON I.clin_id = P.prov_id JOIN AVS_COMPANY AS O ON P.prov_id = O.comp_prov_id WHERE O.comp_id = L.lice_client_id) AS prov_id, (SELECT I.clin_name FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS I ON I.clin_id = P.prov_id JOIN AVS_COMPANY AS O ON P.prov_id = O.comp_prov_id WHERE (O.comp_id = L.lice_client_id)) AS prov_name, (SELECT C.aran_value FROM AVS_ARANDA AS C WHERE C.aran_key = L.lice_serial_number) AS lice_value FROM AVS_LICENSE AS L JOIN AVS_SERVICE AS S ON L.lice_serv_id = S.serv_id AND S.serv_code = @i_serv_code AND S.serv_is_activate = @i_serv_is_activate WHERE L.lice_client_id = @i_lice_client_id END END /*========================================================*/ /* OPERACIONES DE INSERCION */ /*========================================================*/ IF @i_operation = 'I' BEGIN /*========================================================*/ /* Opcion para el registro de Sesiones */ /*========================================================*/ IF @i_option ='A' BEGIN INSERT INTO AVS_LICENSE (lice_serial_number, lice_serial_number_two, lice_serial_number_three, lice_type, lice_client_id, lice_serv_id, lice_agent_quantity, lice_console_quantity, lice_vip_quantity, lice_descrition, lice_end_date, lice_start_date, lice_is_master, lice_agent_available, lice_console_available, lice_vip_available, lice_state) VALUES (@i_lice_serial_number, @i_lice_serial_number_two, @i_lice_serial_number_three, @i_lice_type, @i_lice_client_id, @i_lice_serv_id, @i_lice_agent_quantity, @i_lice_console_quantity, @i_lice_vip_quantity, @i_lice_descrition, @i_lice_end_date, @i_lice_start_date, @i_lice_is_master, @i_lice_agent_available, @i_lice_console_available, @i_lice_vip_available, @i_lice_state) -- Now return the lice_id of the newly inserted record SET @o_lice_id = SCOPE_IDENTITY() IF NOT @i_subl_master IS NULL BEGIN SET @i_subl_child = @o_lice_id INSERT INTO AVS_SUBLICENSE (subl_master, subl_child) VALUES (@i_subl_master, @i_subl_child) END END END /*========================================================*/ /* OPERACIONES DE ACTUALIZACION */ /*========================================================*/ IF @i_operation = 'U' BEGIN /*========================================================*/ /* Opcion para actualizar una Licencia */ /*========================================================*/ IF @i_option ='A' BEGIN UPDATE AVS_LICENSE SET lice_serial_number = @i_lice_serial_number, lice_serial_number_two = @i_lice_serial_number_two, lice_serial_number_three = @i_lice_serial_number_three, lice_type = @i_lice_type, lice_client_id = @i_lice_client_id, lice_serv_id = @i_lice_serv_id, lice_agent_quantity = @i_lice_agent_quantity, lice_console_quantity = @i_lice_console_quantity, lice_vip_quantity = @i_lice_vip_quantity, lice_descrition = @i_lice_descrition, lice_end_date = @i_lice_end_date, lice_start_date = @i_lice_start_date, lice_is_master = @i_lice_is_master, lice_agent_available = @i_lice_agent_available, lice_console_available = @i_lice_console_available, lice_vip_available = @i_lice_vip_available, lice_state = @i_lice_state WHERE lice_id = @i_lice_id DELETE FROM AVS_SUBLICENSE WHERE subl_child = @i_lice_id IF NOT @i_subl_master IS NULL BEGIN INSERT INTO AVS_SUBLICENSE (subl_master, subl_child) VALUES (@i_subl_master, @i_lice_id) END END END /*========================================================*/ /* OPERACIONES DE ELIMINACION */ /*========================================================*/ IF @i_operation = 'D' BEGIN /*========================================================*/ /* Opcion para eliminar una sesión */ /*========================================================*/ IF @i_option ='A' BEGIN DELETE FROM AVS_SUBLICENSE WHERE subl_child = @i_lice_id DELETE FROM AVS_LICENSE WHERE lice_id = @i_lice_id END END END GO PRINT N'Creating [dbo].[PRC_AVS_PROVIDER]...'; GO CREATE PROCEDURE PRC_AVS_PROVIDER ( /*=======================================================================*/ /* Archivo: PRC_AVS_PROVIDER.sql */ /* Stored procedure: PRC_AVS_PROVIDER */ /* 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 */ /* Proveedor de Servicios */ /* PARAMETROS DE ENTRADA: */ /* @i_clin_id : identificador del cliente */ /* @i_clin_activate_date : fecha de activación */ /* @i_clin_is_activate: flag indica si está activo */ /* @i_clin_contact: información de contacto del cliente */ /* @i_clin_name: nombre del cliente */ /* @i_clin_code: código de identificación */ /* @i_prov_companies_assigned: Empresas asignadas */ /* @i_prov_companies_available: Empresas registradas */ /* @i_prov_wildcards: Comodines */ /* PARAMETROS DE SALIDA: */ /* @o_err_cod: Codigo de mensaje. Mayor a 0 si hay error */ /* @o_err_msg: Mensaje de error */ /* RETORNA: */ /* @o_bi_cli_id Identificador asignado */ /*=======================================================================*/ @i_operation char(1), @i_option char(1), @i_clin_id bigint = null, @i_clin_activate_date datetime = null, @i_clin_is_activate int = null, @i_clin_contact nvarchar(4000) = null, @i_clin_name nvarchar(256) = null, @i_clin_code nvarchar(128) = null, @i_prov_companies_assigned nvarchar(1024) = null, @i_prov_companies_available nvarchar(1024) = null, @i_prov_wildcards nvarchar(1024) = null, @o_bi_cli_id bigint = null output ) AS BEGIN /*========================================================*/ /* OPERACIONES DE CONSULTA */ /*========================================================*/ IF @i_operation = 'S' BEGIN /*========================================================*/ /* Opcion para listar los atributos de Proveedor */ /*========================================================*/ IF @i_option ='A' BEGIN SELECT P.prov_id, P.prov_companies_assigned, P.prov_companies_available, P.prov_wildcards, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS L ON L.clin_id = P.prov_id END /*========================================================*/ /* Opcion Proveedores dado el estado */ /*========================================================*/ IF @i_option ='B' BEGIN SELECT P.prov_id, P.prov_companies_assigned, P.prov_companies_available, P.prov_wildcards, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS L ON L.clin_id = P.prov_id WHERE L.clin_is_activate = @i_clin_is_activate END /*========================================================*/ /* Opcion Proveedores dado el ID */ /*========================================================*/ IF @i_option ='C' BEGIN SELECT P.prov_id, P.prov_companies_assigned, P.prov_companies_available, P.prov_wildcards, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS L ON L.clin_id = P.prov_id WHERE P.prov_id = @i_clin_id END /*========================================================*/ /* Opcion Proveedores dado el Código */ /*========================================================*/ IF @i_option ='D' BEGIN SELECT P.prov_id, P.prov_companies_assigned, P.prov_companies_available, P.prov_wildcards, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code FROM AVS_PROVIDER AS P JOIN AVS_CLIENT AS L ON L.clin_id = P.prov_id WHERE L.clin_code = @i_clin_code END END /*========================================================*/ /* OPERACIONES DE INSERCION */ /*========================================================*/ IF @i_operation = 'I' BEGIN /*========================================================*/ /* Opcion para el registro de Proveedores */ /*========================================================*/ IF @i_option ='A' BEGIN EXECUTE PRC_AVS_CLIENT @i_operation, @i_option, @i_clin_id, @i_clin_activate_date, @i_clin_is_activate, @i_clin_contact, @i_clin_name, @i_clin_code, NULL, @o_bi_cli_id output INSERT INTO AVS_PROVIDER (prov_id, prov_companies_assigned, prov_companies_available, prov_wildcards) VALUES (@o_bi_cli_id, @i_prov_companies_assigned, @i_prov_companies_available, @i_prov_wildcards) END END /*========================================================*/ /* OPERACIONES DE ACTUALIZACION */ /*========================================================*/ IF @i_operation = 'U' BEGIN /*========================================================*/ /* Opcion para actualizar un Cliente */ /*========================================================*/ IF @i_option ='A' BEGIN EXECUTE PRC_AVS_CLIENT @i_operation, @i_option, @i_clin_id, @i_clin_activate_date, @i_clin_is_activate, @i_clin_contact, @i_clin_name, @i_clin_code UPDATE AVS_PROVIDER SET prov_companies_assigned = @i_prov_companies_assigned, prov_companies_available = @i_prov_companies_available, prov_wildcards = @i_prov_wildcards WHERE prov_id = @i_clin_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_PROVIDER WHERE prov_id = @i_clin_id EXECUTE PRC_AVS_CLIENT @i_operation, @i_option, @i_clin_id, @i_clin_activate_date, @i_clin_is_activate, @i_clin_contact, @i_clin_name, @i_clin_code END END END GO PRINT N'Creating [dbo].[PRC_AVS_ROLE]...'; GO CREATE PROCEDURE PRC_AVS_ROLE ( /*=======================================================================*/ /* Archivo: PRC_AVS_ROLE.sql */ /* Stored procedure: PRC_AVS_ROLE */ /* 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 */ /* Roles de Usuario */ /* PARAMETROS DE ENTRADA: */ /* @i_role_id :identificador del rol */ /* @i_role_name : nombre del rol */ /* @i_role_description: descripción del rol */ /* PARAMETROS DE SALIDA: */ /* @o_err_cod: Codigo de mensaje. Mayor a 0 si hay error */ /* @o_err_msg: Mensaje de error */ /* RETORNA: */ /* No hay retorno */ /*=======================================================================*/ @i_operation char(1), @i_option char(1), @i_role_id bigint = null, @i_role_name nvarchar(128) = null, @i_role_description nvarchar(4000) = null ) AS BEGIN /*========================================================*/ /* OPERACIONES DE CONSULTA */ /*========================================================*/ IF @i_operation = 'S' BEGIN /*========================================================*/ /* Opcion para listar los atributos de Rol */ /*========================================================*/ IF @i_option ='A' BEGIN SELECT role_id, role_name, role_description FROM AVS_ROLE END END /*========================================================*/ /* OPERACIONES DE INSERCION */ /*========================================================*/ IF @i_operation = 'I' BEGIN /*========================================================*/ /* Opcion para el registro de Administradores */ /*========================================================*/ IF @i_option ='A' BEGIN INSERT INTO AVS_ROLE (role_name, role_description) VALUES (@i_role_name, @i_role_description) END END /*========================================================*/ /* OPERACIONES DE ACTUALIZACION */ /*========================================================*/ IF @i_operation = 'U' BEGIN /*========================================================*/ /* Opcion para actualizar un Cliente */ /*========================================================*/ IF @i_option ='A' BEGIN UPDATE AVS_ROLE SET role_name = @i_role_name, role_description = @i_role_description WHERE role_id = @i_role_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_ROLE WHERE role_id = @i_role_id END END END GO PRINT N'Creating [dbo].[PRC_AVS_SERVICE]...'; GO CREATE PROCEDURE PRC_AVS_SERVICE ( /*=======================================================================*/ /* Archivo: PRC_AVS_SERVICE.sql */ /* Stored procedure: PRC_AVS_SERVICE */ /* 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 */ /* Servicios del Gateway */ /* PARAMETROS DE ENTRADA: */ /* @i_serv_id : identificador de servicio */ /* @i_serv_name : nombre del servicio */ /* @i_serv_code: código del servicio */ /* @i_serv_description: descripción */ /* @i_serv_is_activate: flag si esta activo */ /* @i_serv_is_master: flag si esta Servicio Principal */ /* @i_subs_child: identificador de subservicios */ /* @i_subs_master: identificador de servicio base */ /* PARAMETROS DE SALIDA: */ /* @o_err_cod: Codigo de mensaje. Mayor a 0 si hay error */ /* @o_err_msg: Mensaje de error */ /* RETORNA: */ /* No hay retorno */ /*=======================================================================*/ @i_operation char(1), @i_option char(1), @i_serv_id bigint = null, @i_serv_name nvarchar(128) = null, @i_serv_code nvarchar(128) = null, @i_serv_description nvarchar(4000) = null, @i_serv_is_activate int = null, @i_serv_is_master int = null, @i_subs_child bigint = null, @i_subs_master bigint = null ) AS BEGIN /*========================================================*/ /* OPERACIONES DE CONSULTA */ /*========================================================*/ IF @i_operation = 'S' BEGIN /*========================================================*/ /* Opcion para listar los atributos de Servicio */ /*========================================================*/ IF @i_option ='A' BEGIN SELECT S.serv_id, S.serv_name, S.serv_code, S.serv_description, S.serv_is_activate, S.serv_is_master FROM AVS_SERVICE AS S END /*========================================================*/ /* Opcion Servicio por ID */ /*========================================================*/ IF @i_option ='B' BEGIN SELECT S.serv_id, S.serv_name, S.serv_code, S.serv_description, S.serv_is_activate, S.serv_is_master FROM AVS_SERVICE AS S WHERE S.serv_id = @i_serv_id END /*========================================================*/ /* Opcion Servicio por ServiceCode */ /*========================================================*/ IF @i_option ='C' BEGIN SELECT S.serv_id, S.serv_name, S.serv_code, S.serv_description, S.serv_is_activate, S.serv_is_master FROM AVS_SERVICE AS S WHERE S.serv_code = @i_serv_code END /*========================================================*/ /* Opcion Servicio por ID del maestro */ /*========================================================*/ IF @i_option ='D' BEGIN SELECT S.serv_id, S.serv_name, S.serv_code, S.serv_description, S.serv_is_activate, S.serv_is_master FROM AVS_SERVICE AS S INNER JOIN AVS_SUBSERVICE AS U ON U.subs_child = S.serv_id AND U.subs_master = @i_subs_master END /*========================================================*/ /* Opcion Servicio por Estado */ /*========================================================*/ IF @i_option ='E' BEGIN SELECT S.serv_id, S.serv_name, S.serv_code, S.serv_description, S.serv_is_activate, S.serv_is_master FROM AVS_SERVICE AS S WHERE S.serv_is_activate = @i_serv_is_activate END /*========================================================*/ /* Opcion Servicio por Estado y Tipo */ /*========================================================*/ IF @i_option ='F' BEGIN SELECT S.serv_id, S.serv_name, S.serv_code, S.serv_description, S.serv_is_activate, S.serv_is_master FROM AVS_SERVICE AS S WHERE S.serv_is_activate = @i_serv_is_activate AND S.serv_is_master = @i_serv_is_master END /*========================================================*/ /* Opcion Servicio por Estado y Servicio Maestro */ /*========================================================*/ IF @i_option ='G' BEGIN SELECT S.serv_id, S.serv_name, S.serv_code, S.serv_description, S.serv_is_activate, S.serv_is_master FROM AVS_SERVICE AS S INNER JOIN AVS_SUBSERVICE AS U ON U.subs_child = S.serv_id AND U.subs_master = @i_subs_master WHERE S.serv_is_activate = @i_serv_is_activate END /*========================================================*/ /* Opcion Servicio por Estado y Servicio Maestro */ /*========================================================*/ IF @i_option ='H' BEGIN SELECT S.serv_id, S.serv_name, S.serv_code, S.serv_description, S.serv_is_activate, S.serv_is_master FROM AVS_SERVICE AS S WHERE S.serv_is_activate = @i_serv_is_activate AND S.serv_code = @i_serv_code END END /*========================================================*/ /* OPERACIONES DE INSERCION */ /*========================================================*/ IF @i_operation = 'I' BEGIN /*========================================================*/ /* Opcion para el registro de Servicios */ /*========================================================*/ IF @i_option ='A' BEGIN INSERT INTO AVS_SERVICE (serv_name, serv_code, serv_description, serv_is_activate, serv_is_master) VALUES (@i_serv_name, @i_serv_code, @i_serv_description, @i_serv_is_activate, @i_serv_is_master) END /*========================================================*/ /* Opcion para el registrar asociación de Subservicio */ /*========================================================*/ IF @i_option ='B' BEGIN INSERT INTO AVS_SUBSERVICE (subs_master, subs_child) VALUES (@i_subs_master, @i_subs_child) END END /*========================================================*/ /* OPERACIONES DE ACTUALIZACION */ /*========================================================*/ IF @i_operation = 'U' BEGIN /*========================================================*/ /* Opcion para actualizar un Servicio */ /*========================================================*/ IF @i_option ='A' BEGIN UPDATE AVS_SERVICE SET serv_name = @i_serv_name, serv_code = @i_serv_code, serv_description = @i_serv_description, serv_is_activate = @i_serv_is_activate, serv_is_master = @i_serv_is_master WHERE serv_id = @i_serv_id END /*========================================================*/ /* Opcion para actualizar un Servicio */ /*========================================================*/ IF @i_option ='B' BEGIN UPDATE AVS_SERVICE SET serv_is_activate = @i_serv_is_activate WHERE serv_id = @i_serv_id END END /*========================================================*/ /* OPERACIONES DE ELIMINACION */ /*========================================================*/ IF @i_operation = 'D' BEGIN /*========================================================*/ /* Opcion para eliminar un Servicio */ /*========================================================*/ IF @i_option ='A' BEGIN DELETE FROM AVS_SERVICE WHERE serv_id = @i_serv_id END /*========================================================*/ /* Opcion para eliminar un Subservicio */ /*========================================================*/ IF @i_option ='B' BEGIN DELETE FROM AVS_SUBSERVICE WHERE subs_child = @i_subs_child AND subs_master = @i_subs_master END END END GO PRINT N'Creating [dbo].[PRC_AVS_SESSION]...'; GO CREATE PROCEDURE [dbo].[PRC_AVS_SESSION] ( /*=======================================================================*/ /* Archivo: PRC_AVS_SESSION.sql */ /* Stored procedure: PRC_AVS_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.AVS. */ /* 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 */ /* Sesión de Gateway */ /* 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 */ /* 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 asignado */ /*=======================================================================*/ @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_sess_gate_id BIGINT = NULL, @i_sess_guid VARCHAR(150) = NULL, @i_sess_company VARCHAR(128) = NULL, @i_sess_type VARCHAR(50) = NULL, @o_sess_id BIGINT = NULL OUTPUT ) AS BEGIN /*========================================================*/ /* OPERACIONES DE CONSULTA */ /*========================================================*/ IF @i_operation = 'S' BEGIN /*========================================================*/ /* Opcion para listar los atributos de Sessión */ /*========================================================*/ IF @i_option ='A' BEGIN SELECT sess_id, sess_code, sess_start_time, sess_end_time, sess_state, sess_lice_id, sess_serv_id, sess_gate_id FROM AVS_SESSION END /*========================================================*/ /* Opcion para tener el Id de la ultima Sessión */ /*========================================================*/ IF @i_option ='B' BEGIN SELECT TOP 1 sess_id FROM AVS_SESSION ORDER BY sess_id DESC END END /*========================================================*/ /* OPERACIONES DE INSERCION */ /*========================================================*/ IF @i_operation = 'I' BEGIN /*========================================================*/ /* Opcion para el registro de Sesiones */ /*========================================================*/ IF @i_option ='A' BEGIN IF NOT EXISTS ( --No repetir session_code SELECT TOP(1) 1 FROM AVS_SESSION WHERE sess_code = @i_sess_code AND sess_type = @i_sess_type AND sess_company = @i_sess_company ) BEGIN INSERT INTO AVS_SESSION (sess_code, sess_start_time, sess_end_time, sess_state, sess_lice_id, sess_serv_id, sess_gate_id, sess_guid, sess_type, sess_company) VALUES (@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_type, @i_sess_company) -- Now return the sess_id of the newly inserted record SET @o_sess_id = SCOPE_IDENTITY() END END END /*========================================================*/ /* OPERACIONES DE ACTUALIZACION */ /*========================================================*/ IF @i_operation = 'U' BEGIN /*========================================================*/ /* Opcion para actualizar una Sesión */ /*========================================================*/ IF @i_option ='A' BEGIN UPDATE AVS_SESSION SET sess_code = @i_sess_code, sess_start_time = @i_sess_start_time, sess_end_time = @i_sess_end_time, sess_state = @i_sess_state, sess_lice_id = @i_sess_lice_id, sess_serv_id = @i_sess_serv_id, sess_gate_id = @i_sess_gate_id WHERE sess_id = @i_sess_id END /*========================================================*/ /* Opcion para cerrar todas las Sesiones Pendientes */ /*========================================================*/ IF @i_option ='B' BEGIN UPDATE AVS_SESSION SET sess_end_time = @i_sess_end_time, sess_state = 0 --Cerrado WHERE sess_state = 1 --Iniciado AND (sess_gate_id IS NULL OR sess_gate_id = @i_sess_gate_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_SESSION WHERE sess_id = @i_sess_id END END END GO PRINT N'Creating [dbo].[PRC_AVS_SPE_SESSION]...'; GO CREATE PROCEDURE [dbo].[PRC_AVS_SPE_SESSION] ( /*=======================================================================*/ /* Archivo: PRC_AVS_SPE_SESSION.sql */ /* Stored procedure: PRC_AVS_SPE_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 */ /* configuración del Gateway */ /* 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_spse_spec_id : especialista asociado */ /* @i_spse_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 asignado */ /*=======================================================================*/ @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_spse_spec_id BIGINT = NULL, @i_spse_ip_address NVARCHAR(128) = NULL, @i_spse_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 Especialista */ /*========================================================*/ IF @i_option ='A' BEGIN SELECT P.spse_id, P.spse_spec_id, P.spse_ip_address, P.spse_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 user_nick FROM AVS_USER AS U WHERE U.user_key = P.spse_spec_id) AS user_nick, (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_SPE_SESSION AS P JOIN AVS_SESSION AS S ON S.sess_id = P.spse_id ORDER BY S.sess_end_time DESC END /*========================================================*/ /* Opcion para filtrar listado por Cliente */ /*========================================================*/ IF @i_option ='B' BEGIN /* Filtro por Empresa */ IF NOT @i_lice_client_id IS NULL BEGIN IF NOT @i_sess_id IS NULL BEGIN SELECT P.spse_id, P.spse_spec_id, P.spse_ip_address, P.spse_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 user_nick FROM AVS_USER AS U WHERE U.user_key = P.spse_spec_id) AS user_nick, (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_SPE_SESSION AS P JOIN AVS_SESSION AS S ON S.sess_id = P.spse_id JOIN AVS_LICENSE AS L ON S.sess_lice_id = L.lice_id AND L.lice_client_id = @i_lice_client_id WHERE P.spse_id = @i_sess_id ORDER BY S.sess_end_time DESC END ELSE BEGIN SELECT P.spse_id, P.spse_spec_id, P.spse_ip_address, P.spse_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 user_nick FROM AVS_USER AS U WHERE U.user_key = P.spse_spec_id) AS user_nick, (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_SPE_SESSION AS P JOIN AVS_SESSION AS S ON S.sess_id = P.spse_id JOIN AVS_LICENSE AS L ON S.sess_lice_id = L.lice_id AND L.lice_client_id = @i_lice_client_id ORDER BY S.sess_end_time DESC END END /* Filtro por Proveedor */ ELSE IF NOT @i_comp_prov_id IS NULL BEGIN IF NOT @i_sess_id IS NULL BEGIN SELECT P.spse_id, P.spse_spec_id, P.spse_ip_address, P.spse_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 user_nick FROM AVS_USER AS U WHERE U.user_key = P.spse_spec_id) AS user_nick, (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_SPE_SESSION AS P JOIN AVS_SESSION AS S ON S.sess_id = P.spse_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 P.spse_id = @i_sess_id ORDER BY S.sess_end_time DESC END ELSE BEGIN SELECT P.spse_id, P.spse_spec_id, P.spse_ip_address, P.spse_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 user_nick FROM AVS_USER AS U WHERE U.user_key = P.spse_spec_id) AS user_nick, (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_SPE_SESSION AS P JOIN AVS_SESSION AS S ON S.sess_id = P.spse_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 END ELSE BEGIN SELECT P.spse_id, P.spse_spec_id, P.spse_ip_address, P.spse_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 user_nick FROM AVS_USER AS U WHERE U.user_key = P.spse_spec_id) AS user_nick, (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_SPE_SESSION AS P JOIN AVS_SESSION AS S ON S.sess_id = P.spse_id JOIN AVS_LICENSE AS L ON S.sess_lice_id = L.lice_id WHERE P.spse_id = @i_sess_id ORDER BY S.sess_end_time DESC END END /*==============================================================*/ /* Opcion para la sesión del especialista antes adjudicada */ /*==============================================================*/ IF @i_option = 'C' BEGIN SELECT P.spse_id, P.spse_spec_id, P.spse_ip_address, P.spse_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_company, S.sess_gate_id, (SELECT user_nick FROM AVS_USER AS U WHERE U.user_key = P.spse_spec_id) AS user_nick, (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_SPE_SESSION AS P JOIN AVS_SESSION AS S ON S.sess_id = P.spse_id JOIN AVS_LICENSE AS L ON S.sess_lice_id = L.lice_id WHERE P.spse_spec_id = @i_spse_spec_id AND S.sess_company = @i_sess_company AND S.sess_type = @i_sess_type 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 INSERT INTO AVS_SPE_SESSION (spse_id, spse_spec_id, spse_ip_address, spse_version) VALUES (@o_sess_id, @i_spse_spec_id, @i_spse_ip_address, @i_spse_version) 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_SPE_SESSION SET spse_spec_id = @i_spse_spec_id, spse_ip_address = @i_spse_ip_address, spse_version = @i_spse_version WHERE spse_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_SPE_SESSION WHERE spse_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 PRINT N'Creating [dbo].[PRC_AVS_SW_CLIENT]...'; GO CREATE PROCEDURE PRC_AVS_SW_CLIENT ( /*=======================================================================*/ /* Archivo: PRC_AVS_SW_CLIENT.sql */ /* Stored procedure: PRC_AVS_SW_CLIENT */ /* 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 */ /* Clientes de Software soportados */ /* PARAMETROS DE ENTRADA: */ /* @i_swcl_id :identificador del Cliente de Software */ /* @i_swcl_name : nombre del Cliente de Software */ /* @i_swcl_code : código del Cliente de Software */ /* @i_swve_version : Versión del Cliente SW */ /* @i_swve_date : Fecha de Modificación de la URL */ /* PARAMETROS DE SALIDA: */ /* @o_err_cod: Codigo de mensaje. Mayor a 0 si hay error */ /* @o_err_msg: Mensaje de error */ /* RETORNA: */ /* No hay retorno */ /*=======================================================================*/ @i_operation char(1), @i_option char(1), @i_swcl_id bigint = null, @i_swcl_name nvarchar(128) = null, @i_swcl_code nvarchar(128) = null, @i_swve_version nvarchar(128) = null, @i_swve_date datetime = null ) AS BEGIN /*========================================================*/ /* OPERACIONES DE CONSULTA */ /*========================================================*/ IF @i_operation = 'S' BEGIN /*========================================================*/ /* Opcion para listar los atributos de Clientes SW */ /*========================================================*/ IF @i_option ='A' BEGIN SELECT C.swcl_id, C.swcl_name, C.swcl_code, C.swcl_description, C.swcl_base_path, C.swcl_comp_path, C.swcl_file_name, (SELECT TOP 1 U.swve_version FROM AVS_SW_VERSION AS U WHERE U.swve_swcl_id = C.swcl_id ORDER BY U.swve_date DESC) AS swve_version, (SELECT TOP 1 U.swve_date FROM AVS_SW_VERSION AS U WHERE U.swve_swcl_id = C.swcl_id ORDER BY U.swve_date DESC) AS swve_date FROM AVS_SW_CLIENT AS C END /*========================================================*/ /* Opcion Cliente SW por ID */ /*========================================================*/ IF @i_option ='B' BEGIN SELECT C.swcl_id, C.swcl_name, C.swcl_code, C.swcl_description, C.swcl_base_path, C.swcl_comp_path, C.swcl_file_name, (SELECT TOP 1 U.swve_version FROM AVS_SW_VERSION AS U WHERE U.swve_swcl_id = C.swcl_id ORDER BY U.swve_date DESC) AS swve_version, (SELECT TOP 1 U.swve_date FROM AVS_SW_VERSION AS U WHERE U.swve_swcl_id = C.swcl_id ORDER BY U.swve_date DESC) AS swve_date FROM AVS_SW_CLIENT AS C WHERE C.swcl_id = @i_swcl_id END /*========================================================*/ /* Opcion Cliente SW por Codigo */ /*========================================================*/ IF @i_option ='C' BEGIN SELECT C.swcl_id, C.swcl_name, C.swcl_code, C.swcl_description, C.swcl_base_path, C.swcl_comp_path, C.swcl_file_name, (SELECT TOP 1 U.swve_version FROM AVS_SW_VERSION AS U WHERE U.swve_swcl_id = C.swcl_id ORDER BY U.swve_date DESC) AS swve_version, (SELECT TOP 1 U.swve_date FROM AVS_SW_VERSION AS U WHERE U.swve_swcl_id = C.swcl_id ORDER BY U.swve_date DESC) AS swve_date FROM AVS_SW_CLIENT AS C WHERE C.swcl_code = @i_swcl_code END END /*========================================================*/ /* OPERACIONES DE ACTUALIZACION */ /*========================================================*/ IF @i_operation = 'U' BEGIN /*========================================================*/ /* Opcion para actualizar un Cliente de Software */ /*========================================================*/ IF @i_option ='A' BEGIN INSERT INTO AVS_SW_VERSION (swve_version, swve_date, swve_swcl_id) VALUES (@i_swve_version, @i_swve_date, @i_swcl_id) END END END GO PRINT N'Creating [dbo].[PRC_AVS_USER]...'; GO CREATE PROCEDURE PRC_AVS_USER ( /*=======================================================================*/ /* Archivo: PRC_AVS_USER.sql */ /* Stored procedure: PRC_AVS_USER */ /* 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 */ /* usuarios del Gateway */ /* PARAMETROS DE ENTRADA: */ /* @i_user_id : identificador de usuario */ /* @i_user_nick : nombre de usuario */ /* @i_user_password: contraseña */ /* @i_user_registry_date: fecha de registro */ /* @i_user_mail: correo electrónico */ /* @i_user_is_activate: flag está activo */ /* @i_user_role_id: rol asignado */ /* @i_user_full_name: nombre completo */ /* @i_user_notify: flag recibe notificaciones */ /* @i_clin_code : código del cliente asociado */ /* @i_user_last_login: fecha última sesión */ /* @i_user_last_ip: dirección ip última sesión */ /* PARAMETROS DE SALIDA: */ /* @o_err_cod: Codigo de mensaje. Mayor a 0 si hay error */ /* @o_err_msg: Mensaje de error */ /* RETORNA: */ /* @o_user_id: identificador asignado */ /*=======================================================================*/ @i_operation char(1), @i_option char(1), @i_user_id bigint = null, @i_user_nick nvarchar(128) = null, @i_user_password nvarchar(128) = null, @i_user_registry_date datetime = null, @i_user_mail nvarchar(512) = null, @i_user_is_activate int = null, @i_user_role_id bigint = null, @i_user_full_name nvarchar(512) = null, @i_user_notify tinyint = null, @i_user_last_login datetime = null, @i_user_last_ip nvarchar(128) = null, @i_clin_code nvarchar(128) = null, @o_user_id bigint = null output ) AS BEGIN /*========================================================*/ /* OPERACIONES DE CONSULTA */ /*========================================================*/ IF @i_operation = 'S' BEGIN /*========================================================*/ /* Opcion para listar los atributos de Usuario */ /*========================================================*/ IF @i_option ='A' BEGIN SELECT user_key, user_nick, user_password, user_registry_date, user_mail, user_is_activate, user_role_id, user_full_name, user_notify, user_last_login, user_last_ip FROM AVS_USER END /*========================================================*/ /* Opcion Usuario por Id */ /*========================================================*/ IF @i_option ='B' BEGIN SELECT U.user_key, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name FROM AVS_USER AS U WHERE U.user_key = @i_user_id END /*========================================================*/ /* Opcion Usuario por nick y contraseña */ /*========================================================*/ IF @i_option ='C' BEGIN IF NOT @i_user_password IS NULL BEGIN SELECT U.user_key, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name FROM AVS_USER AS U WHERE U.user_nick = @i_user_nick AND U.user_password = @i_user_password END ELSE BEGIN SELECT U.user_key, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name FROM AVS_USER AS U WHERE U.user_nick = @i_user_nick END END /*========================================================*/ /* Opcion Usuario por rol */ /*========================================================*/ IF @i_option ='D' BEGIN SELECT U.user_key, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name FROM AVS_USER AS U WHERE U.user_role_id = @i_user_role_id END /*========================================================*/ /* Opcion busqueda por empresa para notificaciones */ /*========================================================*/ IF @i_option ='E' BEGIN IF NOT @i_clin_code IS NULL BEGIN /* ADMINISTRADORES DE GATEWAY */ SELECT user_full_name, user_mail FROM AVS_USER WHERE user_is_activate = 1 AND user_role_id = 1 AND user_notify = 1 UNION /* ADMINISTRADORES DE PROVEEDOR */ SELECT AVS_USER_2.user_full_name, AVS_USER_2.user_mail FROM AVS_USER AS AVS_USER_2 JOIN AVS_CLIENT JOIN AVS_COMPANY ON AVS_CLIENT.clin_id = AVS_COMPANY.comp_id JOIN AVS_PROVIDER_ADMIN ON AVS_COMPANY.comp_prov_id = AVS_PROVIDER_ADMIN.prad_prov_id ON AVS_USER_2.user_key = AVS_PROVIDER_ADMIN.prad_id WHERE AVS_CLIENT.clin_code = @i_clin_code AND AVS_USER_2.user_is_activate = 1 AND AVS_USER_2.user_role_id = 2 AND AVS_USER_2.user_notify = 1 UNION /* ADMINISTRADORES DE EMPRESA */ SELECT AVS_USER_1.user_full_name, AVS_USER_1.user_mail FROM AVS_COMPANY AS AVS_COMPANY_1 JOIN AVS_COMPANY_ADMIN ON AVS_COMPANY_1.comp_id = AVS_COMPANY_ADMIN.coad_comp_id JOIN AVS_USER AS AVS_USER_1 ON AVS_COMPANY_ADMIN.coad_id = AVS_USER_1.user_key JOIN AVS_CLIENT AS AVS_CLIENT_1 ON AVS_COMPANY_1.comp_id = AVS_CLIENT_1.clin_id WHERE AVS_CLIENT_1.clin_code = @i_clin_code AND AVS_USER_1.user_is_activate = 1 AND AVS_USER_1.user_role_id = 3 AND AVS_USER_1.user_notify = 1 END END END /*========================================================*/ /* OPERACIONES DE INSERCION */ /*========================================================*/ IF @i_operation = 'I' BEGIN /*========================================================*/ /* Opcion para el registro de Sesiones */ /*========================================================*/ IF @i_option ='A' BEGIN INSERT INTO AVS_USER (user_nick, user_password, user_registry_date, user_mail, user_is_activate, user_role_id, user_full_name, user_notify, user_last_login, user_last_ip) VALUES (@i_user_nick, @i_user_password, @i_user_registry_date, @i_user_mail, @i_user_is_activate, @i_user_role_id, @i_user_full_name, @i_user_notify, @i_user_last_login, @i_user_last_ip) -- Now return the user_key of the newly inserted record SET @o_user_id = SCOPE_IDENTITY() END END /*========================================================*/ /* OPERACIONES DE ACTUALIZACION */ /*========================================================*/ IF @i_operation = 'U' BEGIN /*========================================================*/ /* Opcion para actualizar un Usuario */ /*========================================================*/ IF @i_option ='A' BEGIN UPDATE AVS_USER SET user_nick = @i_user_nick, user_password = @i_user_password, user_registry_date = @i_user_registry_date, user_mail = @i_user_mail, user_is_activate = @i_user_is_activate, user_role_id = @i_user_role_id, user_full_name = @i_user_full_name, user_notify = @i_user_notify, user_last_login = @i_user_last_login, user_last_ip = @i_user_last_ip WHERE user_key = @i_user_id END END /*========================================================*/ /* OPERACIONES DE ELIMINACION */ /*========================================================*/ IF @i_operation = 'D' BEGIN /*========================================================*/ /* Opcion para eliminar un Usuario */ /*========================================================*/ IF @i_option ='A' BEGIN DELETE FROM AVS_USER WHERE user_key = @i_user_id END END END GO PRINT N'Creating [dbo].[PRC_AVS_AG_SESSION]...'; GO CREATE 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 PRINT N'Creating [dbo].[PRC_AVS_COMPANY_ADMIN]...'; GO CREATE PROCEDURE PRC_AVS_COMPANY_ADMIN ( /*=======================================================================*/ /* Archivo: PRC_AVS_COMPANY_ADMIN.sql */ /* Stored procedure: PRC_AVS_COMPANY_ADMIN */ /* 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 */ /* configuración del Gateway */ /* PARAMETROS DE ENTRADA: */ /* @i_user_id : identificador de usuario */ /* @i_user_nick : nombre de usuario */ /* @i_user_password: contraseña */ /* @i_user_registry_date: fecha de registro */ /* @i_user_mail: correo electrónico */ /* @i_user_is_activate: flag está activo */ /* @i_user_role_id: rol asignado */ /* @i_user_full_name: nombre completo */ /* @i_user_notify: flag recibe notificaciones */ /* @i_user_last_login: fecha última sesión */ /* @i_user_last_ip: dirección ip última sesión */ /* @i_coad_comp_id : Empresa asociada */ /* @i_comp_prov_id : Proveedor asociado */ /* PARAMETROS DE SALIDA: */ /* @o_err_cod: Codigo de mensaje. Mayor a 0 si hay error */ /* @o_err_msg: Mensaje de error */ /* RETORNA: */ /* No hay retorno */ /*=======================================================================*/ @i_operation char(1), @i_option char(1), @i_user_id bigint = NULL, @i_user_nick nvarchar(128) = NULL, @i_user_password nvarchar(128) = NULL, @i_user_registry_date datetime = NULL, @i_user_mail nvarchar(512) = NULL, @i_user_is_activate int = NULL, @i_user_role_id bigint = NULL, @i_user_full_name nvarchar(512) = NULL, @i_user_notify tinyint = NULL, @i_user_last_login datetime = NULL, @i_user_last_ip nvarchar(128) = null, @i_coad_comp_id bigint = NULL, @i_comp_prov_id bigint = NULL ) AS DECLARE @v_user_key bigint BEGIN /*========================================================*/ /* OPERACIONES DE CONSULTA */ /*========================================================*/ IF @i_operation = 'S' BEGIN /*========================================================*/ /* Opcion para listar los atributos de Administrador */ /*========================================================*/ IF @i_option ='A' BEGIN SELECT A.coad_id, A.coad_comp_id, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name, O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name FROM AVS_COMPANY_ADMIN AS A JOIN AVS_USER AS U ON U.user_key = A.coad_id JOIN AVS_COMPANY AS O ON O.comp_id = A.coad_comp_id JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id END /*========================================================*/ /* Opcion para filtrar por id de especialista por empresa */ /*========================================================*/ IF @i_option ='B' BEGIN IF NOT @i_coad_comp_id IS NULL BEGIN SELECT A.coad_id, A.coad_comp_id, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name, O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name FROM AVS_COMPANY_ADMIN AS A JOIN AVS_USER AS U ON U.user_key = A.coad_id JOIN AVS_COMPANY AS O ON O.comp_id = A.coad_comp_id JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id WHERE A.coad_id = @i_user_id AND A.coad_comp_id = @i_coad_comp_id END ELSE IF NOT @i_comp_prov_id IS NULL BEGIN SELECT A.coad_id, A.coad_comp_id, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name, O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name FROM AVS_COMPANY_ADMIN AS A JOIN AVS_USER AS U ON U.user_key = A.coad_id JOIN AVS_COMPANY AS O ON O.comp_id = A.coad_comp_id JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id WHERE A.coad_id = @i_user_id AND O.comp_prov_id = @i_comp_prov_id END ELSE BEGIN SELECT A.coad_id, A.coad_comp_id, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name, O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name FROM AVS_COMPANY_ADMIN AS A JOIN AVS_USER AS U ON U.user_key = A.coad_id JOIN AVS_COMPANY AS O ON O.comp_id = A.coad_comp_id JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id WHERE A.coad_id = @i_user_id END END /*========================================================*/ /* Opcion para filtrar por id de empresa */ /*========================================================*/ IF @i_option ='C' BEGIN IF NOT @i_coad_comp_id IS NULL BEGIN SELECT A.coad_id, A.coad_comp_id, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name, O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name FROM AVS_COMPANY_ADMIN AS A JOIN AVS_USER AS U ON U.user_key = A.coad_id JOIN AVS_COMPANY AS O ON O.comp_id = A.coad_comp_id JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id WHERE A.coad_comp_id = @i_coad_comp_id END ELSE IF NOT @i_comp_prov_id IS NULL BEGIN SELECT A.coad_id, A.coad_comp_id, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name, O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name FROM AVS_COMPANY_ADMIN AS A JOIN AVS_USER AS U ON U.user_key = A.coad_id JOIN AVS_COMPANY AS O ON O.comp_id = A.coad_comp_id JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id WHERE O.comp_prov_id = @i_comp_prov_id END ELSE BEGIN SELECT A.coad_id, A.coad_comp_id, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name, O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name FROM AVS_COMPANY_ADMIN AS A JOIN AVS_USER AS U ON U.user_key = A.coad_id JOIN AVS_COMPANY AS O ON O.comp_id = A.coad_comp_id JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id END END END /*========================================================*/ /* OPERACIONES DE INSERCION */ /*========================================================*/ IF @i_operation = 'I' BEGIN /*========================================================*/ /* Opcion para el registro de Administradores */ /*========================================================*/ IF @i_option ='A' BEGIN EXECUTE PRC_AVS_USER @i_operation, @i_option, @i_user_id, @i_user_nick, @i_user_password, @i_user_registry_date, @i_user_mail, @i_user_is_activate, @i_user_role_id, @i_user_full_name, @i_user_notify, @i_user_last_login, @i_user_last_ip, null, @v_user_key output INSERT INTO AVS_COMPANY_ADMIN (coad_id, coad_comp_id) VALUES (@v_user_key, @i_coad_comp_id) END END /*========================================================*/ /* OPERACIONES DE ACTUALIZACION */ /*========================================================*/ IF @i_operation = 'U' BEGIN /*========================================================*/ /* Opcion para actualizar un Cliente */ /*========================================================*/ IF @i_option ='A' BEGIN EXECUTE PRC_AVS_USER @i_operation, @i_option, @i_user_id, @i_user_nick, @i_user_password, @i_user_registry_date, @i_user_mail, @i_user_is_activate, @i_user_role_id, @i_user_full_name, @i_user_notify, @i_user_last_login, @i_user_last_ip, null, null UPDATE AVS_COMPANY_ADMIN SET coad_comp_id = @i_coad_comp_id WHERE coad_id = @i_user_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_COMPANY_ADMIN WHERE coad_id = @i_user_id EXECUTE PRC_AVS_USER @i_operation, @i_option, @i_user_id, @i_user_nick, @i_user_password, @i_user_registry_date, @i_user_mail, @i_user_is_activate, @i_user_role_id, @i_user_full_name, @i_user_notify, @i_user_last_login, @i_user_last_ip, null, null END END END GO PRINT N'Creating [dbo].[PRC_AVS_PROVIDER_ADMIN]...'; GO CREATE PROCEDURE PRC_AVS_PROVIDER_ADMIN ( /*=======================================================================*/ /* Archivo: PRC_AVS_PROVIDER_ADMIN.sql */ /* Stored procedure: PRC_AVS_PROVIDER_ADMIN */ /* 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 */ /* Administradores de Proveedor */ /* PARAMETROS DE ENTRADA: */ /* @i_user_id : identificador de usuario */ /* @i_user_nick : nombre de usuario */ /* @i_user_password: contraseña */ /* @i_user_registry_date: fecha de registro */ /* @i_user_mail: correo electrónico */ /* @i_user_is_activate: flag está activo */ /* @i_user_role_id: rol asignado */ /* @i_user_full_name: nombre completo */ /* @i_user_notify: flag recibe notificaciones */ /* @i_user_last_login: fecha última sesión */ /* @i_user_last_ip: dirección ip última sesión */ /* @i_prad_prov_id : Proveedor Asociado */ /* PARAMETROS DE SALIDA: */ /* @o_err_cod: Codigo de mensaje. Mayor a 0 si hay error */ /* @o_err_msg: Mensaje de error */ /* RETORNA: */ /* No hay retorno */ /*=======================================================================*/ @i_operation char(1), @i_option char(1), @i_user_id bigint = NULL, @i_user_nick nvarchar(128) = NULL, @i_user_password nvarchar(128) = NULL, @i_user_registry_date datetime = NULL, @i_user_mail nvarchar(512) = NULL, @i_user_is_activate int = NULL, @i_user_role_id bigint = NULL, @i_user_full_name nvarchar(512) = NULL, @i_user_notify tinyint = NULL, @i_user_last_login datetime = NULL, @i_user_last_ip nvarchar(128) = null, @i_prad_prov_id bigint = NULL ) AS BEGIN /*========================================================*/ /* OPERACIONES DE CONSULTA */ /*========================================================*/ IF @i_operation = 'S' BEGIN /*========================================================*/ /* Opcion para listar los atributos de Administrador */ /*========================================================*/ IF @i_option ='A' BEGIN SELECT A.prad_id, A.prad_prov_id, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name, O.prov_id, O.prov_companies_assigned, O.prov_companies_available, O.prov_wildcards, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code FROM AVS_PROVIDER_ADMIN AS A JOIN AVS_USER AS U ON U.user_key = A.prad_id JOIN AVS_PROVIDER AS O ON O.prov_id = A.prad_prov_id JOIN AVS_CLIENT AS L ON L.clin_id = O.prov_id END /*========================================================*/ /* Opcion para tener el Id del último Administrador */ /*========================================================*/ IF @i_option ='B' BEGIN IF NOT @i_prad_prov_id IS NULL BEGIN SELECT A.prad_id, A.prad_prov_id, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name, O.prov_id, O.prov_companies_assigned, O.prov_companies_available, O.prov_wildcards, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code FROM AVS_PROVIDER_ADMIN AS A JOIN AVS_USER AS U ON U.user_key = A.prad_id JOIN AVS_PROVIDER AS O ON O.prov_id = A.prad_prov_id JOIN AVS_CLIENT AS L ON L.clin_id = O.prov_id WHERE A.prad_id = @i_user_id AND A.prad_prov_id = @i_prad_prov_id END ELSE BEGIN SELECT A.prad_id, A.prad_prov_id, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name, O.prov_id, O.prov_companies_assigned, O.prov_companies_available, O.prov_wildcards, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code FROM AVS_PROVIDER_ADMIN AS A JOIN AVS_USER AS U ON U.user_key = A.prad_id JOIN AVS_PROVIDER AS O ON O.prov_id = A.prad_prov_id JOIN AVS_CLIENT AS L ON L.clin_id = O.prov_id WHERE A.prad_id = @i_user_id END END /*========================================================*/ /* Opcion para tener el Id del último Administrador */ /*========================================================*/ IF @i_option ='C' BEGIN SELECT A.prad_id, A.prad_prov_id, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name, O.prov_id, O.prov_companies_assigned, O.prov_companies_available, O.prov_wildcards, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code FROM AVS_PROVIDER_ADMIN AS A JOIN AVS_USER AS U ON U.user_key = A.prad_id JOIN AVS_PROVIDER AS O ON O.prov_id = A.prad_prov_id JOIN AVS_CLIENT AS L ON L.clin_id = O.prov_id WHERE A.prad_prov_id = @i_prad_prov_id END END /*========================================================*/ /* OPERACIONES DE INSERCION */ /*========================================================*/ IF @i_operation = 'I' BEGIN /*========================================================*/ /* Opcion para el registro de Administradores */ /*========================================================*/ IF @i_option ='A' BEGIN DECLARE @i_user_key bigint EXECUTE PRC_AVS_USER @i_operation, @i_option, @i_user_id, @i_user_nick, @i_user_password, @i_user_registry_date, @i_user_mail, @i_user_is_activate, @i_user_role_id, @i_user_full_name, @i_user_notify, @i_user_last_login, @i_user_last_ip, null, @i_user_key output INSERT INTO AVS_PROVIDER_ADMIN (prad_id, prad_prov_id) VALUES (@i_user_key, @i_prad_prov_id) END END /*========================================================*/ /* OPERACIONES DE ACTUALIZACION */ /*========================================================*/ IF @i_operation = 'U' BEGIN /*========================================================*/ /* Opcion para actualizar un Cliente */ /*========================================================*/ IF @i_option ='A' BEGIN EXECUTE PRC_AVS_USER @i_operation, @i_option, @i_user_id, @i_user_nick, @i_user_password, @i_user_registry_date, @i_user_mail, @i_user_is_activate, @i_user_role_id, @i_user_full_name, @i_user_notify, @i_user_last_login, @i_user_last_ip, null, null UPDATE AVS_PROVIDER_ADMIN SET prad_prov_id = @i_prad_prov_id WHERE prad_id = @i_user_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_PROVIDER_ADMIN WHERE prad_id = @i_user_id EXECUTE PRC_AVS_USER @i_operation, @i_option, @i_user_id, @i_user_nick, @i_user_password, @i_user_registry_date, @i_user_mail, @i_user_is_activate, @i_user_role_id, @i_user_full_name, @i_user_notify, @i_user_last_login, @i_user_last_ip, null, null END END END GO PRINT N'Creating [dbo].[PRC_AVS_SER_SESSION]...'; GO CREATE PROCEDURE [dbo].[PRC_AVS_SER_SESSION] ( /*=======================================================================*/ /* Archivo: PRC_AVS_SER_SESSION.sql */ /* Stored procedure: PRC_AVS_SER_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 Servicio */ /* 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_sese_agse_id : sesión de agente asociada */ /* @i_sese_spse_id : sesión de especialista asociada */ /* @i_sese_agent: ip del agente */ /* @i_sese_console: ip de la consola */ /* @i_sese_has_video: flat que indica si tiene video */ /* @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 asignado */ /*=======================================================================*/ @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_sese_agse_id BIGINT = NULL, @i_sese_spse_id BIGINT = NULL, @i_sese_agent NVARCHAR(128) = NULL, @i_sese_console NVARCHAR(128) = NULL, @i_sese_has_video INT = 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 los atributos de Sessión */ /*========================================================*/ IF @i_option ='A' BEGIN SELECT E.sese_id, E.sese_agse_id, E.sese_spse_id, E.sese_agent, E.sese_console, E.sese_has_video, 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 V WHERE V.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_SER_SESSION AS E JOIN AVS_SESSION AS S ON S.sess_id = E.sese_id ORDER BY S.sess_end_time DESC END /*========================================================*/ /* Opcion para tener Sessión por ID */ /*========================================================*/ IF @i_option ='B' BEGIN SELECT E.sese_id, E.sese_agse_id, E.sese_spse_id, E.sese_agent, E.sese_console, E.sese_has_video, 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 V WHERE V.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_SER_SESSION AS E JOIN AVS_SESSION AS S ON S.sess_id = E.sese_id WHERE S.sess_id = @i_sess_id ORDER BY S.sess_end_time DESC END /*========================================================*/ /* Opcion para tener Sessión por SessionCode */ /*========================================================*/ IF @i_option ='C' BEGIN SELECT E.sese_id, E.sese_agse_id, E.sese_spse_id, E.sese_agent, E.sese_console, E.sese_has_video, 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 V WHERE V.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_SER_SESSION AS E JOIN AVS_SESSION AS S ON S.sess_id = E.sese_id WHERE S.sess_code = @i_sess_code ORDER BY S.sess_end_time DESC END /*========================================================*/ /* Opcion para filtrar listado por Cliente */ /*========================================================*/ IF @i_option ='D' BEGIN /* Filtro por Empresa */ IF NOT @i_lice_client_id IS NULL BEGIN SELECT E.sese_id, E.sese_agse_id, E.sese_spse_id, E.sese_agent, E.sese_console, E.sese_has_video, 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 V WHERE V.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_SER_SESSION AS E JOIN AVS_SESSION AS S ON S.sess_id = E.sese_id JOIN AVS_LICENSE AS L ON S.sess_lice_id = L.lice_id AND L.lice_client_id = @i_lice_client_id ORDER BY S.sess_end_time DESC END /* Filtro por Proveedor */ ELSE IF NOT @i_comp_prov_id IS NULL BEGIN SELECT E.sese_id, E.sese_agse_id, E.sese_spse_id, E.sese_agent, E.sese_console, E.sese_has_video, 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 V WHERE V.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_SER_SESSION AS E JOIN AVS_SESSION AS S ON S.sess_id = E.sese_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 END END /*========================================================*/ /* OPERACIONES DE INSERCION */ /*========================================================*/ IF @i_operation = 'I' BEGIN /*========================================================*/ /* Opcion para el registro de Sesiones */ /*========================================================*/ IF @i_option ='A' BEGIN EXECUTE @i_sess_id = 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_SER_SESSION (sese_id, sese_agse_id, sese_spse_id, sese_agent, sese_console, sese_has_video) VALUES (@o_sess_id, @i_sese_agse_id, @i_sese_spse_id, @i_sese_agent, @i_sese_console, @i_sese_has_video) END END END /*========================================================*/ /* OPERACIONES DE ACTUALIZACION */ /*========================================================*/ IF @i_operation = 'U' BEGIN /*========================================================*/ /* Opcion para actualizar una Sesión de Servicio */ /*========================================================*/ 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_SER_SESSION SET sese_agse_id = @i_sese_agse_id, sese_spse_id = @i_sese_spse_id, sese_agent = @i_sese_agent, sese_console = @i_sese_console, sese_has_video = @i_sese_has_video WHERE sese_id = @i_sess_id END /*========================================================*/ /* Opcion para cerrar todas las Sesiones Pendientes */ /*========================================================*/ IF @i_option ='B' 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 END END /*========================================================*/ /* OPERACIONES DE ELIMINACION */ /*========================================================*/ IF @i_operation = 'D' BEGIN /*========================================================*/ /* Opcion para eliminar una Sesión de Servicio */ /*========================================================*/ IF @i_option ='A' BEGIN DELETE FROM AVS_SER_SESSION WHERE sese_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 PRINT N'Creating [dbo].[PRC_AVS_SPECIALIST]...'; GO CREATE PROCEDURE PRC_AVS_SPECIALIST ( /*=======================================================================*/ /* Archivo: PRC_AVS_SPECIALIST.sql */ /* Stored procedure: PRC_AVS_SPECIALIST */ /* 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 */ /* configuración del Gateway */ /* PARAMETROS DE ENTRADA: */ /* @i_user_id : identificador de usuario */ /* @i_user_nick : nombre de usuario */ /* @i_user_password: contraseña */ /* @i_user_registry_date: fecha de registro */ /* @i_user_mail: correo electrónico */ /* @i_user_is_activate: flag está activo */ /* @i_user_role_id: rol asignado */ /* @i_user_full_name: nombre completo */ /* @i_user_notify: flag recibe notificaciones */ /* @i_user_last_login: fecha última sesión */ /* @i_user_last_ip: dirección ip última sesión */ /* @i_spec_comp_id: Empresa asociada */ /* @i_spec_is_vip: flag es VIP */ /* @i_spec_is_audit: flag es Auditado */ /* @i_comp_prov_id : Proveedor asociado */ /* PARAMETROS DE SALIDA: */ /* @o_err_cod: Codigo de mensaje. Mayor a 0 si hay error */ /* @o_err_msg: Mensaje de error */ /* RETORNA: */ /* No hay retorno */ /*=======================================================================*/ @i_operation char(1), @i_option char(1), @i_user_id bigint = NULL, @i_user_nick nvarchar(128) = NULL, @i_user_password nvarchar(128) = NULL, @i_user_registry_date datetime = NULL, @i_user_mail nvarchar(512) = NULL, @i_user_is_activate int = NULL, @i_user_role_id bigint = NULL, @i_user_full_name nvarchar(512) = NULL, @i_user_notify tinyint = NULL, @i_user_last_login datetime = NULL, @i_user_last_ip nvarchar(128) = null, @i_spec_comp_id bigint = NULL, @i_spec_is_vip int = NULL, @i_spec_is_audit int = NULL, @i_comp_prov_id bigint = NULL ) AS BEGIN /*========================================================*/ /* OPERACIONES DE CONSULTA */ /*========================================================*/ IF @i_operation = 'S' BEGIN /*========================================================*/ /* Opcion para listar los Especialista */ /*========================================================*/ IF @i_option ='A' BEGIN SELECT S.spec_id, S.spec_comp_id, S.spec_is_vip, S.spec_is_audit, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name, O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name FROM AVS_SPECIALIST AS S JOIN AVS_USER AS U ON U.user_key = S.spec_id JOIN AVS_COMPANY AS O ON O.comp_id = S.spec_comp_id JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id END /*========================================================*/ /* Opcion Especialista por id y id de cliente */ /*========================================================*/ IF @i_option ='B' BEGIN IF NOT @i_spec_comp_id IS NULL BEGIN SELECT S.spec_id, S.spec_comp_id, S.spec_is_vip, S.spec_is_audit, U.user_key, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name, O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name FROM AVS_SPECIALIST AS S JOIN AVS_USER AS U ON U.user_key = S.spec_id JOIN AVS_COMPANY AS O ON O.comp_id = S.spec_comp_id JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id WHERE S.spec_id = @i_user_id AND S.spec_comp_id = @i_spec_comp_id END ELSE IF NOT @i_comp_prov_id IS NULL BEGIN SELECT S.spec_id, S.spec_comp_id, S.spec_is_vip, S.spec_is_audit, U.user_key, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name, O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name FROM AVS_SPECIALIST AS S JOIN AVS_USER AS U ON U.user_key = S.spec_id JOIN AVS_COMPANY AS O ON O.comp_id = S.spec_comp_id AND O.comp_prov_id = @i_comp_prov_id JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id WHERE S.spec_id = @i_user_id END ELSE BEGIN SELECT S.spec_id, S.spec_comp_id, S.spec_is_vip, S.spec_is_audit, U.user_key, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name, O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name FROM AVS_SPECIALIST AS S JOIN AVS_USER AS U ON U.user_key = S.spec_id JOIN AVS_COMPANY AS O ON O.comp_id = S.spec_comp_id JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id WHERE S.spec_id = @i_user_id END END /*========================================================*/ /* Opcion Especialista por nick y contraseña */ /*========================================================*/ IF @i_option ='C' BEGIN SELECT S.spec_id, S.spec_comp_id, S.spec_is_vip, S.spec_is_audit, U.user_key, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name, O.comp_id, O.comp_prov_id, CG.gate_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name FROM AVS_SPECIALIST AS S JOIN AVS_USER AS U ON U.user_key = S.spec_id AND U.user_nick = @i_user_nick AND U.user_password = @i_user_password JOIN AVS_COMPANY AS O ON O.comp_id = S.spec_comp_id JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id JOIN AVS_CLIENT_GATEWAY AS CG ON CG.clin_id = L.clin_id END /*========================================================*/ /* Opcion Especialista por id de cliente */ /*========================================================*/ IF @i_option ='D' BEGIN IF NOT @i_spec_comp_id IS NULL BEGIN SELECT S.spec_id, S.spec_comp_id, S.spec_is_vip, S.spec_is_audit, U.user_key, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name, O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name FROM AVS_SPECIALIST AS S JOIN AVS_USER AS U ON U.user_key = S.spec_id JOIN AVS_COMPANY AS O ON O.comp_id = S.spec_comp_id JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id WHERE S.spec_comp_id = @i_spec_comp_id END ELSE IF NOT @i_comp_prov_id IS NULL BEGIN SELECT S.spec_id, S.spec_comp_id, S.spec_is_vip, S.spec_is_audit, U.user_key, U.user_nick, U.user_password, U.user_registry_date, U.user_mail, U.user_is_activate, U.user_role_id, U.user_full_name, U.user_notify, U.user_last_login, U.user_last_ip, (SELECT role_name FROM AVS_ROLE AS R WHERE R.role_id = U.user_role_id) AS role_name, O.comp_id, O.comp_prov_id, L.clin_activate_date, L.clin_is_activate, L.clin_contact, L.clin_name, L.clin_code, (SELECT clin_name FROM AVS_CLIENT AS C WHERE clin_id = O.comp_prov_id) AS prov_name FROM AVS_SPECIALIST AS S JOIN AVS_USER AS U ON U.user_key = S.spec_id JOIN AVS_COMPANY AS O ON O.comp_id = S.spec_comp_id AND O.comp_prov_id = @i_comp_prov_id JOIN AVS_CLIENT AS L ON L.clin_id = O.comp_id END END END /*========================================================*/ /* OPERACIONES DE INSERCION */ /*========================================================*/ IF @i_operation = 'I' BEGIN /*========================================================*/ /* Opcion para el registro de Especialistas */ /*========================================================*/ IF @i_option ='A' BEGIN DECLARE @i_user_key bigint EXECUTE PRC_AVS_USER @i_operation, @i_option, @i_user_id, @i_user_nick, @i_user_password, @i_user_registry_date, @i_user_mail, @i_user_is_activate, @i_user_role_id, @i_user_full_name, @i_user_notify, @i_user_last_login, @i_user_last_ip, null, @i_user_key output INSERT INTO AVS_SPECIALIST (spec_id, spec_comp_id, spec_is_vip, spec_is_audit) VALUES (@i_user_key, @i_spec_comp_id, @i_spec_is_vip, @i_spec_is_audit) END END /*========================================================*/ /* OPERACIONES DE ACTUALIZACION */ /*========================================================*/ IF @i_operation = 'U' BEGIN /*========================================================*/ /* Opcion para actualizar un Especialista */ /*========================================================*/ IF @i_option ='A' BEGIN EXECUTE PRC_AVS_USER @i_operation, @i_option, @i_user_id, @i_user_nick, @i_user_password, @i_user_registry_date, @i_user_mail, @i_user_is_activate, @i_user_role_id, @i_user_full_name, @i_user_notify, @i_user_last_login, @i_user_last_ip, null, null UPDATE AVS_SPECIALIST SET spec_comp_id = @i_spec_comp_id, spec_is_vip = @i_spec_is_vip, spec_is_audit = @i_spec_is_audit WHERE spec_id = @i_user_id END END /*========================================================*/ /* OPERACIONES DE ELIMINACION */ /*========================================================*/ IF @i_operation = 'D' BEGIN /*========================================================*/ /* Opcion para eliminar un Especialista */ /*========================================================*/ IF @i_option ='A' BEGIN DELETE FROM AVS_SPECIALIST WHERE spec_id = @i_user_id EXECUTE PRC_AVS_USER @i_operation, @i_option, @i_user_id, @i_user_nick, @i_user_password, @i_user_registry_date, @i_user_mail, @i_user_is_activate, @i_user_role_id, @i_user_full_name, @i_user_notify, @i_user_last_login, @i_user_last_ip, null, null END END END GO /* Post-Deployment Script Template -------------------------------------------------------------------------------------- This file contains SQL statements that will be appended to the build script. Use SQLCMD syntax to include a file in the post-deployment script. Example: :r .\myfile.sql Use SQLCMD syntax to reference a variable in the post-deployment script. Example: --:setvar TableName MyTable SELECT * FROM [$(TableName)] -------------------------------------------------------------------------------------- */ IF NOT EXISTS (SELECT 1 FROM [AVS_GATEWAY_BASE]) INSERT INTO [AVS_GATEWAY_BASE]( [gate_server_port], [gate_console_page_size], [gate_agent_page_size], [gate_service_page_size], [gate_version], [gate_last_change], [gate_last_state], [gate_last_update], [gate_last_username], [gate_audit_time], [gate_wait_time], [gate_enable_ssl], [gate_cert_subject], [gate_name], [gate_activate], [gate_audit_url], [gate_install_url], [gate_host], [gate_description] ) VALUES( 443, 5, 5, 5, N'8.0', GETDATE(), 0, GETDATE(), N'', 500, 60000, 1, N'arandasoft.com', N'', N'', N'', N'', N'', N'Aranda Gateway' ) Print '[AVS_GATEWAY_BASE] Metadata' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MetadataInsert]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[MetadataInsert] GO CREATE PROCEDURE [dbo].[MetadataInsert] @role_id BIGINT, @role_name NVARCHAR(128), @role_description NVARCHAR(4000) AS BEGIN IF EXISTS (SELECT 1 FROM [AVS_ROLE] WHERE [role_id] = @role_id ) UPDATE [AVS_ROLE] SET [role_name] = @role_name, [role_description] = @role_description WHERE [role_id] = @role_id ELSE INSERT INTO [AVS_ROLE]( [role_id], [role_name], [role_description] )VALUES( @role_id, @role_name, @role_description ) END GO --METADATA EXEC [dbo].[MetadataInsert] 1, N'Admin', N'Administrador general' EXEC [dbo].[MetadataInsert] 2, N'Admin Proveedor', N'Administrador de un proveedor' EXEC [dbo].[MetadataInsert] 3, N'Admin Empresa', N'Administrador de una empresa' EXEC [dbo].[MetadataInsert] 4, N'Especialista', N'Especialista de una empresa' GO PRINT '[AVS_ROLE] Metadata' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MetadataInsert]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[MetadataInsert] GO CREATE PROCEDURE [dbo].[MetadataInsert] @user_nick NVARCHAR(128), @user_password NVARCHAR(128), @user_mail NVARCHAR(512), @user_is_activate INT, @user_role_id BIGINT, @user_full_name NVARCHAR(512), @user_notify INT AS BEGIN IF NOT EXISTS (SELECT 1 FROM [AVS_USER] WHERE [user_nick] = @user_nick) INSERT INTO [AVS_USER]( [user_nick] ,[user_password] ,[user_registry_date] ,[user_mail] ,[user_is_activate] ,[user_role_id] ,[user_full_name] ,[user_notify] ,[user_last_login] ,[user_last_ip] )VALUES( @user_nick, @user_password, GETDATE(), @user_mail, @user_is_activate, @user_role_id, @user_full_name , @user_notify, GETDATE(), '' ) END GO --METADATA EXEC [dbo].[MetadataInsert] N'admin', '01-92-02-3A-7B-BD-73-25-05-16-F0-69-DF-18-B5-00', 'store@arandasoft.com',1,1,'Administrador Gateway',1 PRINT '[AVS_USER] Metadata' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MetadataInsert]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[MetadataInsert] GO CREATE PROCEDURE [dbo].[MetadataInsert] @serv_name NVARCHAR(128), @serv_code NVARCHAR(128), @serv_description NVARCHAR(4000), @serv_is_activate INT, @serv_is_master INT AS BEGIN IF EXISTS (SELECT 1 FROM [AVS_SERVICE] WHERE [serv_code] = @serv_code ) UPDATE [AVS_SERVICE] SET [serv_name] = @serv_name, [serv_code] = @serv_code, [serv_description] = @serv_description, [serv_is_activate] = @serv_is_activate, [serv_is_master] = @serv_is_master WHERE [serv_code] = @serv_code ELSE INSERT INTO [AVS_SERVICE]( [serv_name], [serv_code], [serv_description], [serv_is_activate], [serv_is_master] )VALUES( @serv_name, @serv_code, @serv_description, @serv_is_activate, @serv_is_master ) END GO --METADATA EXEC [dbo].[MetadataInsert] N'AVS', N'100', N'Aranda Virtual Support', 1, 1 EXEC [dbo].[MetadataInsert] N'Remote Control', N'1', N'Control remoto', 1, 0 EXEC [dbo].[MetadataInsert] N'File Transfer', N'2', N'Transferencia de archivos', 1, 0 GO PRINT '[AVS_SERVICE] Metadata' GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MetadataInsert]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[MetadataInsert] GO CREATE PROCEDURE [dbo].[MetadataInsert] @in_subs_master NVARCHAR(128), @in_subs_child NVARCHAR(128) AS DECLARE @subs_master BIGINT, @subs_child BIGINT BEGIN SELECT @subs_master = [serv_id] FROM [AVS_SERVICE] WHERE [serv_code] = @in_subs_master SELECT @subs_child = [serv_id] FROM [AVS_SERVICE] WHERE [serv_code] = @in_subs_child IF NOT EXISTS (SELECT 1 FROM [AVS_SUBSERVICE] WHERE [subs_master] = @subs_master AND [subs_child] = @subs_child ) INSERT INTO [AVS_SUBSERVICE]( [subs_master], [subs_child] )VALUES( @subs_master, @subs_child ) END GO --METADATA EXEC [dbo].[MetadataInsert] N'100', N'1' EXEC [dbo].[MetadataInsert] N'100', N'2' GO PRINT '[AVS_SUBSERVICE] Metadata' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MetadataInsert]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[MetadataInsert] GO CREATE PROCEDURE [dbo].[MetadataInsert] @swcl_name NVARCHAR(128), @swcl_code NVARCHAR(128), @swcl_description NVARCHAR(4000), @swcl_base_path NVARCHAR(1024), @swcl_comp_path NVARCHAR(1024), @swcl_file_name NVARCHAR(1024) AS BEGIN IF EXISTS (SELECT 1 FROM [AVS_SW_CLIENT] WHERE [swcl_code] = @swcl_code ) UPDATE [AVS_SW_CLIENT] SET [swcl_name] = @swcl_name, [swcl_code] = @swcl_code, [swcl_description] = @swcl_description, [swcl_base_path] = @swcl_base_path, [swcl_comp_path] = @swcl_comp_path, [swcl_file_name] =@swcl_file_name WHERE [swcl_code] = @swcl_code ELSE INSERT INTO [AVS_SW_CLIENT]( [swcl_name], [swcl_code], [swcl_description], [swcl_base_path], [swcl_comp_path], [swcl_file_name] )VALUES( @swcl_name , @swcl_code , @swcl_description , @swcl_base_path , @swcl_comp_path , @swcl_file_name ) END GO --METADATA EXEC [dbo].[MetadataInsert] N'Agente Windows', N'0', N'Agente de Aranda Virtual Support (Windows)', N'~/installers/base/win/{0}', N'~/installers/{0}/win/avsaw.exe', N'avsaw.dat' EXEC [dbo].[MetadataInsert] N'Consola AVS', N'1', N'Consola de Aranda Virtual Support', N'~/installers/base/console/avsc.zip', N'~/installers/{0}/console/avsc.zip', N'avsc.zip' EXEC [dbo].[MetadataInsert] N'Agente Linux', N'2', N'Agente de Aranda Virtual Support (Linux)', N'~/installers/base/linux/{0}', N'~/installers/{0}/linux/avsal', N'avsal.dat' EXEC [dbo].[MetadataInsert] N'Agente Mac', N'3', N'Agente de Aranda Virtual Support (Mac)', N'~/installers/base/mac/{0}', N'~/installers/{0}/mac/avsam', N'avsam.dat' EXEC [dbo].[MetadataInsert] N'Database', N'4', N'Base de datos', N'', N'', N'' GO PRINT '[AVS_SW_CLIENT] Metadata' GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MetadataInsert]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[MetadataInsert] 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 PRINT '[AVS_SW_VERSION] Metadata' GO GO PRINT N'Checking existing data against newly created constraints'; GO --USE [$(DatabaseName)]; GO ALTER TABLE [dbo].[AVS_AG_SESSION] WITH CHECK CHECK CONSTRAINT [FK_AVS_AG_SESSION_AVS_SESSION]; ALTER TABLE [dbo].[AVS_CD_KEY] WITH CHECK CHECK CONSTRAINT [FK_AVS_CD_KEY_AVS_CD_KEY]; ALTER TABLE [dbo].[AVS_CLIENT_GATEWAY] WITH CHECK CHECK CONSTRAINT [FK_AVS_CLIENT_GATEWAY_AVS_CLIENT]; ALTER TABLE [dbo].[AVS_CLIENT_GATEWAY] WITH CHECK CHECK CONSTRAINT [FK_AVS_CLIENT_GATEWAY_AVS_GATEWAY_BASE]; ALTER TABLE [dbo].[AVS_COMPANY] WITH CHECK CHECK CONSTRAINT [FK_AVS_COMPANY_AVS_CLIENT]; ALTER TABLE [dbo].[AVS_COMPANY] WITH CHECK CHECK CONSTRAINT [FK_AVS_COMPANY_AVS_TENANT]; ALTER TABLE [dbo].[AVS_COMPANY_ADMIN] WITH CHECK CHECK CONSTRAINT [FK_AVS_COMPANY_ADMIN_AVS_COMPANY]; ALTER TABLE [dbo].[AVS_COMPANY_ADMIN] WITH CHECK CHECK CONSTRAINT [FK_AVS_COMPANY_ADMIN_AVS_USER]; ALTER TABLE [dbo].[AVS_LICENSE] WITH CHECK CHECK CONSTRAINT [FK_AVS_LICENSE_AVS_CLIENT]; ALTER TABLE [dbo].[AVS_LICENSE] WITH CHECK CHECK CONSTRAINT [FK_AVS_LICENSE_AVS_SERVICE]; ALTER TABLE [dbo].[AVS_PROVIDER] WITH CHECK CHECK CONSTRAINT [FK_AVS_PROVIDER_AVS_CLIENT]; ALTER TABLE [dbo].[AVS_PROVIDER_ADMIN] WITH CHECK CHECK CONSTRAINT [FK_AVS_PROVIDER_ADMIN_AVS_USER]; ALTER TABLE [dbo].[AVS_PROVIDER_ADMIN] WITH CHECK CHECK CONSTRAINT [FK_AVS_TENANT_ADMIN_AVS_TENANT]; ALTER TABLE [dbo].[AVS_SER_SESSION] WITH CHECK CHECK CONSTRAINT [FK_AVS_SER_SESSION_AVS_AG_SESSION]; ALTER TABLE [dbo].[AVS_SER_SESSION] WITH CHECK CHECK CONSTRAINT [FK_AVS_SER_SESSION_AVS_SESSION]; ALTER TABLE [dbo].[AVS_SER_SESSION] WITH CHECK CHECK CONSTRAINT [FK_AVS_SER_SESSION_AVS_SPE_SESSION1]; ALTER TABLE [dbo].[AVS_SESSION] WITH CHECK CHECK CONSTRAINT [FK_AVS_SESSION_GATEWAY]; ALTER TABLE [dbo].[AVS_SESSION] WITH CHECK CHECK CONSTRAINT [FK_AVS_SESSION_LICENSE]; ALTER TABLE [dbo].[AVS_SESSION] WITH CHECK CHECK CONSTRAINT [FK_AVS_SESSION_SERVICE]; ALTER TABLE [dbo].[AVS_SPE_SESSION] WITH CHECK CHECK CONSTRAINT [FK_AVS_SPE_SESSION_AVS_SESSION]; ALTER TABLE [dbo].[AVS_SPE_SESSION] WITH CHECK CHECK CONSTRAINT [FK_AVS_SPE_SESSION_AVS_SPECIALIST]; ALTER TABLE [dbo].[AVS_SPECIALIST] WITH CHECK CHECK CONSTRAINT [FK_AVS_SPECIALIST_AVS_COMPANY]; ALTER TABLE [dbo].[AVS_SPECIALIST] WITH CHECK CHECK CONSTRAINT [FK_AVS_SPECIALIST_AVS_USER]; ALTER TABLE [dbo].[AVS_SUBLICENSE] WITH CHECK CHECK CONSTRAINT [FK_AVS_SUBLICENSE_AVS_LICENSE]; ALTER TABLE [dbo].[AVS_SUBLICENSE] WITH CHECK CHECK CONSTRAINT [FK_AVS_SUBLICENSE_AVS_LICENSE1]; ALTER TABLE [dbo].[AVS_SUBSERVICE] WITH CHECK CHECK CONSTRAINT [FK_AVS_SUBSERVICE_AVS_SERVICE2]; ALTER TABLE [dbo].[AVS_SUBSERVICE] WITH CHECK CHECK CONSTRAINT [FK_AVS_SUBSERVICE_AVS_SERVICE3]; ALTER TABLE [dbo].[AVS_SW_VERSION] WITH CHECK CHECK CONSTRAINT [FK_AVS_SW_VERSION_AVS_SW_CLIENT]; ALTER TABLE [dbo].[AVS_USER] WITH CHECK CHECK CONSTRAINT [FK_AVS_USER_AVS_ROLE]; GO PRINT N'Update complete.'; GO