-- =========================================================

-- Enterprise: [MyloSoft-BinarySoft]
-- Project: [Justicia 360 V 1.0]
-- Author: [Yazlin Saldivar Merino]
-- Create date: [2 de Febrero de 2025]
-- Country: [México]
-- Objective: [Modelo de datos del proyecto Justicia 360]
-- =========================================================

--
***********************************************************************************
****************** --
-- NOTA [09-03-2025 20:39 UTC-6] PARA ÁNGEL DEL FUTURO -> COMO SIEMPRE PENSANDO
DESDE EL PASADO EN TÍ
--
***********************************************************************************
****************** --
-- 1. WEY NO OLVIDES QUE NO HAS REVISADO CON MARIO LA LÓGICA DE PASSWORD_TOKENS_A
Y PASSWORD_TOKENS_GO
-- 2. FALTA LA RELACIÓN DE MUCHOS A MUCHOS DE UNA TABLA
-- 3. VALIDAR REGLAS DE NEGOCIO POR TABLA
-- 4. VALIDAR SI HAY ELIMINACIONES EN CASCADA
-- No reutilizar contraseñas
--
***********************************************************************************
****************** --
CREATE DATABASE IF NOT EXISTS justice_360_dev;
USE justice_360_dev;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS audience_offences;
DROP TABLE IF EXISTS audience_made_pauses;
DROP TABLE IF EXISTS audience_marks;
DROP TABLE IF EXISTS audience_participants;
DROP TABLE IF EXISTS catalog_audience_types_marks;
DROP TABLE IF EXISTS audience_judges;
DROP TABLE IF EXISTS audience_access;
DROP TABLE IF EXISTS audiences;
DROP TABLE IF EXISTS audience_participants;
DROP TABLE IF EXISTS audience_documents;
DROP TABLE IF EXISTS catalog_menu;
DROP TABLE IF EXISTS catalog_roles;
DROP TABLE IF EXISTS catalog_roles_permissions;
DROP TABLE IF EXISTS catalog_users;
DROP TABLE IF EXISTS catalog_web_pages;
DROP TABLE IF EXISTS catalog_marks;
DROP TABLE IF EXISTS catalog_participant_types;
DROP TABLE IF EXISTS catalog_criminal_laws;
DROP TABLE IF EXISTS catalog_management_units;
DROP TABLE IF EXISTS catalog_audience_types;
DROP TABLE IF EXISTS catalog_offenses;
DROP TABLE IF EXISTS catalog_rooms;
DROP TABLE IF EXISTS catalog_judges;
DROP TABLE IF EXISTS catalog_participant_types;
DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS authentication_logs;
DROP TABLE IF EXISTS audience_counters;
DROP TABLE IF EXISTS password_tokens;
-- ---------------------------------------------------
DROP PROCEDURE IF EXISTS CATALOG_MARKS_A;
DROP PROCEDURE IF EXISTS CATALOG_MARKS_D;
DROP PROCEDURE IF EXISTS CATALOG_MARKS_U;
DROP PROCEDURE IF EXISTS CATALOG_MARKS_GA;
DROP PROCEDURE IF EXISTS CATALOG_MARKS_GO;
-- ---------------------------------------------------
DROP PROCEDURE IF EXISTS CATALOG_AUDIENCE_TYPES_A;
DROP PROCEDURE IF EXISTS CATALOG_AUDIENCE_TYPES_D;
DROP PROCEDURE IF EXISTS CATALOG_AUDIENCE_TYPES_U;
DROP PROCEDURE IF EXISTS CATALOG_AUDIENCE_TYPES_GA;
DROP PROCEDURE IF EXISTS CATALOG_AUDIENCE_TYPES_GO;
-- ---------------------------------------------------
DROP PROCEDURE IF EXISTS CATALOG_CRIMINAL_LAWS_A;
DROP PROCEDURE IF EXISTS CATALOG_CRIMINAL_LAWS_D;
DROP PROCEDURE IF EXISTS CATALOG_CRIMINAL_LAWS_U;
DROP PROCEDURE IF EXISTS CATALOG_CRIMINAL_LAWS_GA;
DROP PROCEDURE IF EXISTS CATALOG_CRIMINAL_LAWS_GO;
-- ---------------------------------------------------
DROP PROCEDURE IF EXISTS CATALOG_OFFENSES_A;
DROP PROCEDURE IF EXISTS CATALOG_OFFENSES_D;
DROP PROCEDURE IF EXISTS CATALOG_OFFENSES_U;
DROP PROCEDURE IF EXISTS CATALOG_OFFENSES_GA;
DROP PROCEDURE IF EXISTS CATALOG_OFFENSES_GO;
-- ---------------------------------------------------
DROP PROCEDURE IF EXISTS CATALOG_AUDIENCE_TYPES_MARKS_A;
DROP PROCEDURE IF EXISTS CATALOG_AUDIENCE_TYPES_MARKS_D;
DROP PROCEDURE IF EXISTS CATALOG_AUDIENCE_TYPES_MARKS_U;
DROP PROCEDURE IF EXISTS CATALOG_AUDIENCE_TYPES_MARKS_GA;
DROP PROCEDURE IF EXISTS CATALOG_AUDIENCE_TYPES_MARKS_GO;
-- ----------------------------------------------------
DROP PROCEDURE IF EXISTS CATALOG_MANAGEMENT_UNITS_A;
DROP PROCEDURE IF EXISTS CATALOG_MANAGEMENT_UNITS_D;
DROP PROCEDURE IF EXISTS CATALOG_MANAGEMENT_UNITS_U;
DROP PROCEDURE IF EXISTS CATALOG_MANAGEMENT_UNITS_GA;
DROP PROCEDURE IF EXISTS CATALOG_MANAGEMENT_UNITS_GO;
-- ----------------------------------------------------
DROP PROCEDURE IF EXISTS CATALOG_ROOMS_A;
DROP PROCEDURE IF EXISTS CATALOG_ROOMS_D;
DROP PROCEDURE IF EXISTS CATALOG_ROOMS_U;
DROP PROCEDURE IF EXISTS CATALOG_ROOMS_GA;
DROP PROCEDURE IF EXISTS CATALOG_ROOMS_GO;
-- -----------------------------------------
DROP PROCEDURE IF EXISTS CATALOG_USERS_A;
DROP PROCEDURE IF EXISTS CATALOG_USERS_D;
DROP PROCEDURE IF EXISTS CATALOG_USERS_U;
DROP PROCEDURE IF EXISTS CATALOG_USERS_UP;
DROP PROCEDURE IF EXISTS CATALOG_USERS_GA;
DROP PROCEDURE IF EXISTS CATALOG_USERS_GO;
DROP PROCEDURE IF EXISTS CATALOG_USERS_GObyUSERNAMEandPASSWORD;
-- -----------------------------------------
DROP PROCEDURE IF EXISTS CATALOG_MENU_A;
DROP PROCEDURE IF EXISTS CATALOG_MENU_D;
DROP PROCEDURE IF EXISTS CATALOG_MENU_U;
DROP PROCEDURE IF EXISTS CATALOG_MENU_GA;
DROP PROCEDURE IF EXISTS CATALOG_MENU_GO;
-- -----------------------------------------
DROP PROCEDURE IF EXISTS CATALOG_ROLES_A;
DROP PROCEDURE IF EXISTS CATALOG_ROLES_D;
DROP PROCEDURE IF EXISTS CATALOG_ROLES_U;
DROP PROCEDURE IF EXISTS CATALOG_ROLES_GA;
DROP PROCEDURE IF EXISTS CATALOG_ROLES_GO;
-- ----------------------------------------
DROP PROCEDURE IF EXISTS CATALOG_ROLES_PERMISSIONS_A;
DROP PROCEDURE IF EXISTS CATALOG_ROLES_PERMISSIONS_D;
DROP PROCEDURE IF EXISTS CATALOG_ROLES_PERMISSIONS_U;
DROP PROCEDURE IF EXISTS CATALOG_ROLES_PERMISSIONS_GA;
DROP PROCEDURE IF EXISTS CATALOG_ROLES_PERMISSIONS_GO;
-- ------------------------------------------
DROP PROCEDURE IF EXISTS CATALOG_WEB_PAGES_A;
DROP PROCEDURE IF EXISTS CATALOG_WEB_PAGES_D;
DROP PROCEDURE IF EXISTS CATALOG_WEB_PAGES_U;
DROP PROCEDURE IF EXISTS CATALOG_WEB_PAGES_GA;
DROP PROCEDURE IF EXISTS CATALOG_WEB_PAGES_GO;
-- ------------------------------------------
DROP PROCEDURE IF EXISTS CATALOG_AUTHENTICATION_LOGS_A;
DROP PROCEDURE IF EXISTS CATALOG_AUTHENTICATION_LOGS_GA;
DROP PROCEDURE IF EXISTS CATALOG_AUDIT_LOGS_GA;
DROP PROCEDURE IF EXISTS PASSWORD_TOKENS_A;
DROP PROCEDURE IF EXISTS PASSWORD_TOKENS_GO;
DROP PROCEDURE IF EXISTS AUDIT_LOG_A;
-- ----------------------------------------
SET FOREIGN_KEY_CHECKS = 1;
--
###################################################################################
#########
-- TABLES --
--
###################################################################################
#########
CREATE TABLE `catalog_menu` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`OptionName` varchar(20) NOT NULL CHECK (`OptionName` <> ''),
`Collapsible` int(11) NOT NULL,
`MenuLevel` tinyint(1) NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `catalog_web_pages` (

`Id` int(11) NOT NULL AUTO_INCREMENT,
`IdMenu` int(11) DEFAULT NULL,
`WebPageName` varchar(35) NOT NULL CHECK (`WebPageName` <> ''),
`NavigateURL` varchar(60) NOT NULL,
`Status` tinyint(1) NOT NULL DEFAULT 1 CHECK (`Status` IN (0, 1)),
PRIMARY KEY (`Id`),
KEY `IdMenu` (`IdMenu`),
CONSTRAINT `CATALOG_WEB_PAGES_ibfk_1` FOREIGN KEY (`IdMenu`) REFERENCES
`catalog_menu` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `catalog_roles` (

`Id` int(11) NOT NULL AUTO_INCREMENT,
`RoleName` varchar(25) NOT NULL CHECK (`RoleName` <> ''),
`Status` tinyint(1) NOT NULL DEFAULT 1 CHECK (`Status` IN (0, 1)),
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `catalog_roles_permissions` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`IdRole` int(11) NOT NULL,
`IdWebPage` int(11) NOT NULL,
`Deny` tinyint(1) NOT NULL DEFAULT 0 CHECK (`Deny` IN (0, 1)),
PRIMARY KEY (`Id`),
KEY `IdRole` (`IdRole`),
KEY `IdWebPage` (`IdWebPage`),
CONSTRAINT `CATALOG_ROLES_PERMISSIONS_ibfk_1` FOREIGN KEY (`IdRole`) REFERENCES
`catalog_roles` (`Id`),
CONSTRAINT `CATALOG_ROLES_PERMISSIONS_ibfk_2` FOREIGN KEY (`IdWebPage`)
REFERENCES `catalog_web_pages` (`Id`),
UNIQUE (IdRole, IdWebPage)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `catalog_users` (

`Id` int(11) NOT NULL AUTO_INCREMENT,
`IdRole` int(11) NOT NULL,
`IdUGA` int(11) DEFAULT NULL,
`Name` varchar(20) NOT NULL CHECK (`Name` <> ''),
`LastName` varchar(25) NOT NULL CHECK (`LastName` <> ''),
`Surname` varchar(25) DEFAULT NULL,
`Gender` ENUM('M', 'F', 'O') NOT NULL,
`Email` varchar(80) NOT NULL CHECK (`Email` <> ''),
`Password` varchar(64) NOT NULL CHECK (`Password` <> ''),
`Status` tinyint(1) NOT NULL DEFAULT 1 CHECK (`Status` IN (0, 1)),
`IsJudge` tinyint(1) NOT NULL DEFAULT 0 CHECK (`IsJudge` IN (0, 1)),-- 1 SI ES
JUEZ
`CreatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`UpdatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Id`),
KEY `IdRole` (`IdRole`),
CONSTRAINT `CATALOG_USERS_ibfk_1` FOREIGN KEY (`IdRole`) REFERENCES
`catalog_roles` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `catalog_criminal_laws` (

`Id` int(11) NOT NULL AUTO_INCREMENT,
`CriminalLawName` varchar(100) NOT NULL CHECK (`CriminalLawName` <> ''),
`Status` tinyint(1) NOT NULL DEFAULT 1 CHECK (`Status` IN (0, 1)),
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `catalog_management_units` (

`Id` int(11) NOT NULL AUTO_INCREMENT,
`IdCriminalLaw` int(11) NOT NULL,
`ManagementUnitName` varchar(200) NOT NULL CHECK (`ManagementUnitName` <> ''),
`UnitCode` varchar(10) DEFAULT NULL,
`Status` tinyint(1) NOT NULL DEFAULT 1 CHECK (`Status` IN (0, 1)),
PRIMARY KEY (`Id`),
KEY `IdCriminalLaw` (`IdCriminalLaw`),
CONSTRAINT `CATALOG_MANAGEMENT_UNITS_ibfk_1` FOREIGN KEY (`IdCriminalLaw`)
REFERENCES `catalog_criminal_laws` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
CREATE TABLE `catalog_audience_types` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`IdCriminalLaw` int(11) NOT NULL,
`AudienceTypeName` varchar(200) NOT NULL CHECK (`AudienceTypeName` <> ''),
`Status` tinyint(1) NOT NULL DEFAULT 1 CHECK (`Status` IN (0, 1)),
PRIMARY KEY (`Id`),
KEY `IdCriminalLaw` (`IdCriminalLaw`),
CONSTRAINT `CATALOG_AUDIENCE_TYPES_ibfk_1` FOREIGN KEY (`IdCriminalLaw`)
REFERENCES `catalog_criminal_laws` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `catalog_offenses` (

`Id` int(11) NOT NULL AUTO_INCREMENT,
`IdAudienceType` int(11) NOT NULL,
`OffenseName` varchar(200) NOT NULL CHECK (`OffenseName` <> ''),
`Status` tinyint(1) NOT NULL DEFAULT 1 CHECK (`Status` IN (0, 1)),
PRIMARY KEY (`Id`),
KEY `IdAudienceType` (`IdAudienceType`),
CONSTRAINT `CATALOG_OFFENSES_ibfk_1` FOREIGN KEY (`IdAudienceType`) REFERENCES
`catalog_audience_types` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `catalog_rooms` (

`Id` int(11) NOT NULL AUTO_INCREMENT,
`RoomName` varchar(20) NOT NULL CHECK (`RoomName` <> ''),
`RecClassCode` varchar(20) NOT NULL,
`Flat` int(11) DEFAULT NULL,
`WebRtc` varchar(100) DEFAULT NULL,
`Hls` varchar(100) DEFAULT NULL,
`DeferredStreaming` varchar(100) DEFAULT NULL,
`Channel` varchar(50) NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `audiences` (

`Id` int(11) NOT NULL AUTO_INCREMENT,
`IdAudienceType` int(11) NOT NULL,
`IdRoom` int(11) NOT NULL,
`CaseFile` varchar(60) NOT NULL CHECK (`CaseFile` <> ''),
`Date` date NOT NULL,
`Time` time NOT NULL,
`StartRecording` datetime DEFAULT NULL,
`EndRecording` datetime DEFAULT NULL,
`IsHideAudience` tinyint(1) NOT NULL DEFAULT 0 CHECK (`IsHideAudience` IN (0,
1)),
`IsWebService` tinyint(1) NOT NULL DEFAULT 0 CHECK (`IsWebService` IN (0, 1)),
`IsTest` tinyint(1) NOT NULL DEFAULT 0 CHECK (`IsTest` IN (0, 1)),
`IsBPMToUnify` tinyint(1) NOT NULL DEFAULT 0 CHECK (`IsBPMToUnify` IN (0, 1)),
`Process` int(11) DEFAULT 0,
`CreatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`UpdatedAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`Id`),
KEY `IdAudienceType` (`IdAudienceType`),
KEY `IdRoom` (`IdRoom`),
CONSTRAINT `AUDIENCES_ibfk_1` FOREIGN KEY (`IdAudienceType`) REFERENCES
`catalog_audience_types` (`Id`),
 CONSTRAINT `AUDIENCES_ibfk_2` FOREIGN KEY (`IdRoom`) REFERENCES `catalog_rooms`
(`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `catalog_marks` (

`Id` int(11) NOT NULL AUTO_INCREMENT,
`MarkName` varchar(200) NOT NULL CHECK (`MarkName` <> ''),
`Status` tinyint(1) NOT NULL DEFAULT 1 CHECK (`Status` IN (0, 1)),
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `catalog_audience_types_marks` (

`Id` int(11) NOT NULL AUTO_INCREMENT,
`IdAudienceType` int(11) NOT NULL,
`IdMark` int(11) NOT NULL,
PRIMARY KEY (`Id`),
KEY `IdAudienceType` (`IdAudienceType`),
KEY `IdMark` (`IdMark`),
CONSTRAINT `FK_AUDIENCE_TYPE_MARKS_ibfk_1` FOREIGN KEY (`IdMark`) REFERENCES
`catalog_marks` (`Id`),
CONSTRAINT `FK_AUDIENCE_TYPE_MARKS_ibfk_2` FOREIGN KEY (`IdAudienceType`)
REFERENCES `catalog_audience_types` (`Id`),
UNIQUE (IdAudienceType, IdMark)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

-- **PauseDuration minutos
CREATE TABLE `audience_made_pauses` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`IdAudience` int(11) NOT NULL,
`PauseDuration` int(11) NOT NULL,
PRIMARY KEY (`Id`),
KEY `IdAudience` (`IdAudience`),
CONSTRAINT `FK_AUDIENCE_MADE_PAUSES_ibfk_1` FOREIGN KEY (`IdAudience`) REFERENCES
`audiences` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

-- ORDENADO DE MENOR A MAYOR - **MarkTime MILISEGUNDOS

CREATE TABLE `audience_marks` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`IdAudience` int(11) NOT NULL,
`IdAudienceTypeMark` int(11) NOT NULL,
`MarkTime` int(11) NOT NULL,
`Observations` varchar(250) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `IdAudience` (`IdAudience`),
KEY `IdAudienceTypeMark` (`IdAudienceTypeMark`),
CONSTRAINT `FK_AUDIENCE_MARKS_ibfk_1` FOREIGN KEY (`IdAudienceTypeMark`)
REFERENCES `catalog_audience_types_marks` (`Id`),
CONSTRAINT `FK_AUDIENCE_MARKS_ibfk_2` FOREIGN KEY (`IdAudience`) REFERENCES
`audiences` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `catalog_participant_types` (

`Id` int(11) NOT NULL AUTO_INCREMENT,
 `ParticipantTypeName` varchar(150) NOT NULL CHECK (`ParticipantTypeName` <> ''),
`Status` tinyint(1) NOT NULL DEFAULT 1 CHECK (`Status` IN (0, 1)),
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `audience_participants` (

`Id` int(11) NOT NULL AUTO_INCREMENT,
`IdAudience` int(11) NOT NULL,
`IdParticipantType` int(11) NOT NULL,
`Name` varchar(20) NOT NULL,
`LastName` varchar(25) NOT NULL,
`Surname` varchar(25) DEFAULT NULL,
`Alias` varchar(100) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `IdAudience` (`IdAudience`),
KEY `IdParticipantType` (`IdParticipantType`),
CONSTRAINT `AUDIENCE_PARTICIPANTS_ibfk_1` FOREIGN KEY (`IdAudience`) REFERENCES
`audiences` (`Id`),
CONSTRAINT `AUDIENCE_PARTICIPANTS_ibfk_2` FOREIGN KEY (`IdParticipantType`)
REFERENCES `catalog_participant_types` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `audience_offences` (

`Id` int(11) NOT NULL AUTO_INCREMENT,
`IdAudience` int(11) NOT NULL,
`IdOffence` int(11) NOT NULL,
PRIMARY KEY (`Id`),
KEY `IdAudience` (`IdAudience`),
KEY `IdOffence` (`IdOffence`),
CONSTRAINT `AUDIENCE_OFFENCES_ibfk_1` FOREIGN KEY (`IdAudience`) REFERENCES
`audiences` (`Id`),
CONSTRAINT `AUDIENCE_OFFENCES_ibfk_2` FOREIGN KEY (`IdOffence`) REFERENCES
`catalog_offenses` (`Id`),
UNIQUE (IdAudience, IdOffence)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `audience_judges` (

`Id` int(11) NOT NULL AUTO_INCREMENT,
`IdAudience` int(11) NOT NULL,
`IdUser` int(11) NOT NULL,
PRIMARY KEY (`Id`),
KEY `IdAudience` (`IdAudience`),
KEY `IdUser` (`IdUser`),
CONSTRAINT `AUDIENCE_JUDGES_ibfk_1` FOREIGN KEY (`IdAudience`) REFERENCES
`audiences` (`Id`),
CONSTRAINT `AUDIENCE_JUDGES_ibfk_2` FOREIGN KEY (`IdUser`) REFERENCES
`catalog_users` (`Id`),
UNIQUE (IdAudience, IdUser)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `audience_documents` (

`Id` int(11) NOT NULL AUTO_INCREMENT,
`IdAudience` int(11) NOT NULL,
`DocumentName` varchar(100) NOT NULL,
`Path` varchar(200) NOT NULL,
 PRIMARY KEY (`Id`),
KEY `IdAudience` (`IdAudience`),
CONSTRAINT `AUDIENCE_DOCUMENTS_ibfk_1` FOREIGN KEY (`IdAudience`) REFERENCES
`audiences` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `audit_logs` (

`Id` INT AUTO_INCREMENT PRIMARY KEY,
`TableName` VARCHAR(100) NOT NULL,
`Operation` ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
`RecordId` INT NOT NULL,
`OldData` TEXT NULL,
`NewData` TEXT NULL,
`ExecutedBy` INT NOT NULL,
`ExecutionTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `authentication_logs` (

`Id` INT AUTO_INCREMENT PRIMARY KEY,
`IdUser` INT NULL,
`UserName` VARCHAR(80) NULL,
`IpAddress` VARCHAR(45) NOT NULL,
`Status` ENUM('SUCCESS', 'FAILED') NOT NULL,
`AttemptTime` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`Reason` VARCHAR(255) NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `audience_counters` (

`Id` INT AUTO_INCREMENT PRIMARY KEY,
`CounterType` ENUM('real', 'test') NOT NULL, -- Tipo de audiencia (real o
prueba)
`Process` ENUM('registrada', 'actualizada', 'cancelada', 'preparada', 'en
curso', 'en receso', 'finalizada', 'consolidada', 'verificada', 'notificada') NOT
NULL,
`Count` INT DEFAULT 0,
`LastUpdated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `password_tokens` (

`Id` INT AUTO_INCREMENT PRIMARY KEY,
`IdUser` INT NOT NULL,
`Token` VARCHAR(255) NOT NULL,
`CreatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
`ExpiresAt` DATETIME NOT NULL,
`Used` BOOLEAN DEFAULT FALSE,
FOREIGN KEY (`IdUser`) REFERENCES catalog_users(`Id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
--
###################################################################################
#########
-- TRIGGERS --
--
###################################################################################
#########
DELIMITER $$
CREATE TRIGGER trg_audience_insert
AFTER INSERT ON audiences
FOR EACH ROW
BEGIN
UPDATE audience_counters
SET Count = Count + 1
WHERE CounterType = (CASE WHEN [Link] = 1 THEN 'test' ELSE 'real' END)
AND Process = [Link];
END$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER trg_audience_update
AFTER UPDATE ON audiences
FOR EACH ROW
BEGIN
-- Disminuir el contador del estado anterior
IF [Link] != [Link] THEN
UPDATE audience_counters
SET Count = Count - 1
WHERE CounterType = (CASE WHEN [Link] = 1 THEN 'test' ELSE 'real' END)
AND Process = [Link];

-- Aumentar el contador del nuevo estado

UPDATE audience_counters
SET Count = Count + 1
WHERE CounterType = (CASE WHEN [Link] = 1 THEN 'test' ELSE 'real' END)
AND Process = [Link];
END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER trg_audience_delete
AFTER DELETE ON audiences
FOR EACH ROW
BEGIN
UPDATE audience_counters
SET Count = Count - 1
WHERE CounterType = (CASE WHEN [Link] = 1 THEN 'test' ELSE 'real' END)
AND Process = [Link];
END$$
DELIMITER ;
--
###################################################################################
#########
-- INDEX --
--
###################################################################################
#########
CREATE INDEX idx_audience_offences ON audience_offences(IdAudience, IdOffence);
CREATE INDEX idx_audience_marks ON audience_marks(IdAudience, IdAudienceTypeMark);
CREATE INDEX idx_audiences_date_type ON audiences(Date, IdAudienceType);
CREATE INDEX idx_catalog_users_uga ON catalog_users(IdUGA);
CREATE INDEX idx_catalog_users_email ON catalog_users(Email);
CREATE INDEX idx_catalog_management_units ON catalog_management_units(UnitCode);
CREATE INDEX idx_catalog_audience_types ON catalog_audience_types(IdCriminalLaw);
CREATE INDEX idx_audit_logs ON audit_logs (TableName, Operation,
RecordId,ExecutedBy, ExecutionTime);
--
###################################################################################
#########
-- STORED PROCEDURES --
--
###################################################################################
#########
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_MARKS_A`(
IN p_MarkName VARCHAR(200),
IN p_Status TINYINT(1),
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_marks WHERE MarkName = p_MarkName

COLLATE utf8mb4_unicode_ci;

IF v_ExistsRow = 0 THEN
INSERT INTO catalog_marks (MarkName, Status) VALUES (p_MarkName, p_Status);
SET p_Answer = LAST_INSERT_ID();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_MARKS', 'INSERT', p_Answer,NULL,
JSON_OBJECT('Name', p_MarkName, 'Status', p_Status), p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_MARKS_D`(
IN p_Id INT,
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;
 START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_marks WHERE Id = p_Id;

IF v_ExistsRow = 1 THEN

SELECT JSON_OBJECT('Name', MarkName, 'Status', Status) INTO v_OldData

FROM catalog_marks WHERE Id = p_Id;
DELETE FROM catalog_marks WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_MARKS', 'DELETE', p_Id,
v_OldData,null,p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_MARKS_GA`(
IN p_search VARCHAR(50),
IN p_isDesc TINYINT(1),
IN p_page INT,
IN p_pageSize INT,
OUT p_totalRecords INT
)
BEGIN
DECLARE v_orderBy VARCHAR(50);
DECLARE v_offset INT;

IF p_isDesc = 1 THEN
SET v_orderBy = ' ORDER BY MarkName DESC';
ELSE
SET v_orderBy = ' ORDER BY MarkName ASC';
END IF;

IF p_search IS NULL OR p_search = '' THEN

SELECT COUNT(*) INTO p_totalRecords FROM catalog_marks;
SET @v_query = CONCAT('SELECT Id, MarkName, (CASE WHEN Status = 1 THEN
''Alta'' ELSE ''Baja'' END) AS Status FROM catalog_marks ', v_orderBy);
ELSE
SELECT COUNT(*) INTO p_totalRecords FROM catalog_marks WHERE MarkName
COLLATE utf8mb4_general_ci LIKE CONCAT('%', p_search, '%');
SET @v_query = CONCAT('SELECT Id, MarkName, (CASE WHEN Status = 1 THEN
''Alta'' ELSE ''Baja'' END) AS Status FROM catalog_marks WHERE MarkName COLLATE
utf8mb4_general_ci LIKE ''%', p_search, '%'' ', v_orderBy);
END IF;

IF p_totalRecords > 100 THEN

SET v_offset = (p_page - 1) * p_pageSize;
SET @v_query = CONCAT(@v_query, ' LIMIT ', p_pageSize, ' OFFSET ',
v_offset);
END IF;
 PREPARE stmt FROM @v_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_MARKS_GO`(
IN p_Id INT
)
BEGIN
SELECT Id, MarkName, Status FROM catalog_marks WHERE Id = p_Id;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_MARKS_U`(
IN p_Id INT,
IN p_MarkName VARCHAR(200),
IN p_Status TINYINT(1),
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_ExistsName INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsName FROM catalog_marks WHERE MarkName =

p_MarkName COLLATE utf8mb4_unicode_ci AND Id != p_Id;
SELECT COUNT(*) INTO v_ExistsRow FROM catalog_marks WHERE Id = p_Id;

IF v_ExistsRow = 1 AND v_ExistsName=0 THEN

SELECT JSON_OBJECT('Name', MarkName, 'Status', Status) INTO v_OldData FROM

catalog_marks WHERE Id = p_Id;
UPDATE catalog_marks SET MarkName = p_MarkName, Status = p_Status WHERE Id
= p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_MARKS', 'UPDATE', p_Id,v_OldData,
JSON_OBJECT('Name', p_MarkName, 'Status', p_Status), p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_CRIMINAL_LAWS_A`(
IN p_CriminalLawName VARCHAR(200),
IN p_Status TINYINT(1),
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_criminal_laws WHERE

CriminalLawName = p_CriminalLawName COLLATE utf8mb4_unicode_ci;

IF v_ExistsRow = 0 THEN
INSERT INTO catalog_criminal_laws (CriminalLawName, Status) VALUES
(p_CriminalLawName, p_Status);
SET p_Answer = LAST_INSERT_ID();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_CRIMINAL_LAWS', 'INSERT', p_Answer, NULL,
JSON_OBJECT('Name', p_CriminalLawName, 'Status', p_Status), p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_CRIMINAL_LAWS_D`(
IN p_Id INT,
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_criminal_laws WHERE Id = p_Id;

 IF v_ExistsRow = 1 THEN

SELECT JSON_OBJECT('Name', CriminalLawName, 'Status', Status) INTO

v_OldData FROM catalog_criminal_laws WHERE Id = p_Id;
DELETE FROM catalog_criminal_laws WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_CRIMINAL_LAWS', 'DELETE', p_Id,
v_OldData,NULL, p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;

DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_CRIMINAL_LAWS_GA`(
IN p_search VARCHAR(50),
IN p_isDesc TINYINT(1),
IN p_page INT,
IN p_pageSize INT,
OUT p_totalRecords INT
)
BEGIN
DECLARE v_orderBy VARCHAR(50);
DECLARE v_offset INT;

IF p_isDesc = 1 THEN
SET v_orderBy = ' ORDER BY CriminalLawName DESC';
ELSE
SET v_orderBy = ' ORDER BY CriminalLawName ASC';
END IF;

IF p_search IS NULL OR p_search = '' THEN

SELECT COUNT(*) INTO p_totalRecords FROM catalog_criminal_laws;
SET @v_query = CONCAT('SELECT Id, CriminalLawName, (CASE WHEN Status = 1
THEN ''Alta'' ELSE ''Baja'' END) AS Status FROM catalog_criminal_laws ',
v_orderBy);
ELSE
SELECT COUNT(*) INTO p_totalRecords FROM catalog_criminal_laws WHERE
CriminalLawName COLLATE utf8mb4_general_ci LIKE CONCAT('%', p_search, '%');
SET @v_query = CONCAT('SELECT Id, CriminalLawName, (CASE WHEN Status = 1
THEN ''Alta'' ELSE ''Baja'' END) AS Status FROM catalog_criminal_laws WHERE
CriminalLawName COLLATE utf8mb4_general_ci LIKE ''%', p_search, '%'' ', v_orderBy);
END IF;

IF p_totalRecords > 100 THEN

SET v_offset = (p_page - 1) * p_pageSize;
SET @v_query = CONCAT(@v_query, ' LIMIT ', p_pageSize, ' OFFSET ',
v_offset);
END IF;

PREPARE stmt FROM @v_query;

EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_CRIMINAL_LAWS_GO`(
IN p_Id INT
)
BEGIN
SELECT Id, CriminalLawName, Status FROM catalog_criminal_laws WHERE Id = p_Id;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_CRIMINAL_LAWS_U`(
IN p_Id INT,
IN p_CriminalLawName VARCHAR(200),
IN p_Status TINYINT(1),
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_ExistsName INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsName FROM catalog_criminal_laws WHERE

CriminalLawName = p_CriminalLawName COLLATE utf8mb4_unicode_ci AND Id != p_Id;
SELECT COUNT(*) INTO v_ExistsRow FROM catalog_criminal_laws WHERE Id = p_Id;

IF v_ExistsRow = 1 AND v_ExistsName=0 THEN

SELECT JSON_OBJECT('Name', CriminalLawName, 'Status', Status) INTO

v_OldData FROM catalog_criminal_laws WHERE Id = p_Id;
UPDATE catalog_criminal_laws SET CriminalLawName = p_CriminalLawName,
Status = p_Status WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_CRIMINAL_LAWS', 'UPDATE', p_Id, v_OldData,
JSON_OBJECT('Name', p_CriminalLawName, 'Status', p_Status), p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_AUDIENCE_TYPES_A`(
 IN p_IdCriminalLaw INT,
IN p_AudienceTypeName VARCHAR(200),
IN p_Status TINYINT(1),
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_NewId INT DEFAULT NULL;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_audience_types WHERE

AudienceTypeName = p_AudienceTypeName COLLATE utf8mb4_unicode_ci;

IF v_ExistsRow = 0 THEN
INSERT INTO catalog_audience_types (IdCriminalLaw, AudienceTypeName,
Status) VALUES (p_IdCriminalLaw, p_AudienceTypeName, p_Status);
SET p_Answer = LAST_INSERT_ID();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_AUDIENCE_TYPES', 'INSERT',
p_Answer,NULL, JSON_OBJECT('IdCriminalLaw', p_IdCriminalLaw, 'AudienceTypeName',
p_AudienceTypeName, 'Status', p_Status),p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_AUDIENCE_TYPES_D`(
IN p_Id INT,
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_audience_types WHERE Id = p_Id;

 IF v_ExistsRow = 1 THEN

SELECT JSON_OBJECT('IdCriminalLaw', IdCriminalLaw, 'AudienceTypeName',

AudienceTypeName, 'Status', Status) INTO v_OldData FROM catalog_audience_types
WHERE Id = p_Id;
DELETE FROM catalog_audience_types WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_AUDIENCE_TYPES', 'DELETE', p_Id,
v_OldData,NULL, p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_AUDIENCE_TYPES_GA`(
IN p_search VARCHAR(50),
IN p_isDesc TINYINT(1),
IN p_page INT,
IN p_pageSize INT,
OUT p_totalRecords INT
)
BEGIN
DECLARE v_orderBy VARCHAR(50);
DECLARE v_offset INT;

IF p_isDesc = 1 THEN
SET v_orderBy = ' ORDER BY [Link] DESC';
ELSE
SET v_orderBy = ' ORDER BY [Link] ASC';
END IF;

IF p_search IS NULL OR p_search = '' THEN

SELECT COUNT(*) INTO p_totalRecords FROM catalog_audience_types;
SET @v_query = CONCAT('SELECT [Link], [Link] ,
[Link], (CASE WHEN [Link] = 1 THEN ''Alta'' ELSE ''Baja'' END) AS
Status FROM catalog_audience_types at INNER JOIN catalog_criminal_laws cl ON
[Link] = [Link] ', v_orderBy);
ELSE
SELECT COUNT(*) INTO p_totalRecords FROM catalog_audience_types WHERE
AudienceTypeName COLLATE utf8mb4_general_ci LIKE CONCAT('%', p_search, '%');
SET @v_query = CONCAT('SELECT [Link], [Link] ,
[Link], (CASE WHEN [Link] = 1 THEN ''Alta'' ELSE ''Baja'' END) AS
Status FROM catalog_audience_types at INNER JOIN catalog_criminal_laws cl ON
[Link] = [Link] WHERE [Link] COLLATE utf8mb4_general_ci LIKE
''%', p_search, '%'' ', v_orderBy);
END IF;

IF p_totalRecords > 100 THEN

SET v_offset = (p_page - 1) * p_pageSize;
SET @v_query = CONCAT(@v_query, ' LIMIT ', p_pageSize, ' OFFSET ',
v_offset);
END IF;
 PREPARE stmt FROM @v_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_AUDIENCE_TYPES_GO`(
IN p_Id INT
)
BEGIN
SELECT Id, IdCriminalLaw, AudienceTypeName, Status FROM catalog_audience_types
WHERE Id = p_Id;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_AUDIENCE_TYPES_U`(
IN p_Id INT,
IN p_IdCriminalLaw INT,
IN p_AudienceTypeName VARCHAR(200),
IN p_Status TINYINT(1),
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_ExistsName INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsName FROM catalog_audience_types WHERE

AudienceTypeName = p_AudienceTypeName COLLATE utf8mb4_unicode_ci AND Id != p_Id;
SELECT COUNT(*) INTO v_ExistsRow FROM catalog_audience_types WHERE Id = p_Id;

IF v_ExistsRow = 1 AND v_ExistsName = 0 THEN

SELECT JSON_OBJECT('IdCriminalLaw', IdCriminalLaw, 'AudienceTypeName',
AudienceTypeName, 'Status', Status) INTO v_OldData FROM catalog_audience_types
WHERE Id = p_Id;
UPDATE catalog_audience_types SET IdCriminalLaw = p_IdCriminalLaw,
AudienceTypeName = p_AudienceTypeName, Status = p_Status WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_AUDIENCE_TYPES', 'UPDATE',
p_Id,v_OldData,JSON_OBJECT('IdCriminalLaw', p_IdCriminalLaw, 'AudienceTypeName',
p_AudienceTypeName, 'Status', p_Status), p_ExecutedBy);
END IF;

COMMIT;
ELSE
 SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_OFFENSES_A`(
IN p_IdAudienceType INT,
IN p_OffenseName VARCHAR(200),
IN p_Status INT,
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_offenses WHERE OffenseName =

p_OffenseName COLLATE utf8mb4_unicode_ci;

IF v_ExistsRow = 0 THEN
INSERT INTO catalog_offenses (IdAudienceType, OffenseName, Status) VALUES
(p_IdAudienceType, p_OffenseName, p_Status);
SET p_Answer = LAST_INSERT_ID();
IF p_Answer > 0 THEN
CALL AUDIT_LOG_A('CATALOG_OFFENSES', 'INSERT', p_Answer,NULL,
JSON_OBJECT('IdAudienceType', p_IdAudienceType, 'OffenseName', p_OffenseName,
'Status', p_Status), p_ExecutedBy);
END IF;
COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_OFFENSES_D`(
IN p_Id INT,
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;
 START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_offenses WHERE Id = p_Id;

IF v_ExistsRow = 1 THEN

SELECT JSON_OBJECT('IdAudienceType', IdAudienceType, 'OffenseName',

OffenseName, 'Status', Status) INTO v_OldData FROM catalog_offenses WHERE Id =
p_Id;
DELETE FROM catalog_offenses WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_OFFENSES', 'DELETE', p_Id, v_OldData,
null, p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_OFFENSES_GA`(
IN p_search VARCHAR(50),
IN p_isDesc TINYINT(1),
IN p_page INT,
IN p_pageSize INT,
OUT p_totalRecords INT
)
BEGIN
DECLARE v_orderBy VARCHAR(50);
DECLARE v_offset INT;

IF p_isDesc = 1 THEN
SET v_orderBy = ' ORDER BY [Link] DESC';
ELSE
SET v_orderBy = ' ORDER BY [Link] ASC';
END IF;
IF p_search IS NULL OR p_search = '' THEN
SELECT COUNT(*) INTO p_totalRecords FROM catalog_offenses;
SET @v_query = CONCAT('SELECT [Link], [Link], [Link],
(CASE WHEN [Link] = 1 THEN ''Alta'' ELSE ''Baja'' END) AS Status FROM
catalog_offenses o INNER JOIN catalog_audience_types at ON [Link] = [Link]
', v_orderBy);
ELSE
SELECT COUNT(*) INTO p_totalRecords FROM catalog_offenses WHERE OffenseName
COLLATE utf8mb4_general_ci LIKE CONCAT('%', p_search, '%');
SET @v_query = CONCAT('SELECT [Link], [Link], [Link],
(CASE WHEN [Link] = 1 THEN ''Alta'' ELSE ''Baja'' END) AS Status FROM
catalog_offenses o INNER JOIN catalog_audience_types at ON [Link] = [Link]
WHERE OffenseName COLLATE utf8mb4_general_ci LIKE ''%', p_search, '%'' ',
v_orderBy);
END IF;

IF p_totalRecords > 100 THEN

SET v_offset = (p_page - 1) * p_pageSize;
 SET @v_query = CONCAT(@v_query, ' LIMIT ', p_pageSize, ' OFFSET ',
v_offset);
END IF;

PREPARE stmt FROM @v_query;

EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_OFFENSES_GO`(
IN p_Id INT
)
BEGIN
SELECT Id, IdAudienceType, OffenseName, Status FROM catalog_offenses WHERE Id =
p_Id;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_OFFENSES_U`(
IN p_Id INT,
IN p_IdAudienceType INT,
IN p_OffenseName VARCHAR(200),
IN p_Status INT,
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_ExistsName INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsName FROM catalog_offenses WHERE OffenseName =

p_OffenseName COLLATE utf8mb4_unicode_ci AND Id != p_Id;
SELECT COUNT(*) INTO v_ExistsRow FROM catalog_offenses WHERE Id = p_Id;

IF v_ExistsRow = 1 AND v_ExistsName=0 THEN

SELECT JSON_OBJECT('IdAudienceType', IdAudienceType, 'OffenseName',

OffenseName, 'Status', Status) INTO v_OldData FROM catalog_offenses WHERE Id =
p_Id;
UPDATE catalog_offenses SET IdAudienceType = p_IdAudienceType, OffenseName
= p_OffenseName, Status = p_Status WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_OFFENSES', 'UPDATE', p_Id,
v_OldData,JSON_OBJECT('IdAudienceType', p_IdAudienceType, 'OffenseName',
p_OffenseName, 'Status', p_Status), p_ExecutedBy);
 END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_AUDIENCE_TYPES_MARKS_A`(
IN p_IdAudienceType INT,
IN p_IdMark INT,
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_audience_types_marks WHERE

IdAudienceType = p_IdAudienceType AND IdMark = p_IdMark;

IF v_ExistsRow = 0 THEN
INSERT INTO catalog_audience_types_marks (IdAudienceType, IdMark) VALUES
(p_IdAudienceType, p_IdMark);
SET p_Answer = LAST_INSERT_ID();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_AUDIENCE_TYPES_MARKS', 'INSERT', p_Answer,
NULL, JSON_OBJECT('IdAudienceType', p_IdAudienceType, 'IdMark',
p_IdMark),p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_AUDIENCE_TYPES_MARKS_D`(
IN p_Id INT,
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

 BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_audience_types_marks WHERE Id =

p_Id;

IF v_ExistsRow = 1 THEN
SELECT JSON_OBJECT('IdAudienceType', IdAudienceType, 'IdMark', IdMark) INTO
v_OldData FROM catalog_audience_types_marks WHERE Id = p_Id;
DELETE FROM catalog_audience_types_marks WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_AUDIENCE_TYPES_MARKS', 'DELETE', p_Id,
v_OldData, NULL, p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_AUDIENCE_TYPES_MARKS_GO`(
IN p_Id INT
)
BEGIN
SELECT Id, IdAudienceType, IdMark FROM catalog_audience_types_marks WHERE Id =
p_Id;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_AUDIENCE_TYPES_MARKS_U`(
IN p_Id INT,
IN p_IdAudienceType INT,
IN p_IdMark INT,
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_ExistsRelation INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;
 SELECT COUNT(*) INTO v_ExistsRelation FROM catalog_audience_types_marks WHERE
IdAudienceType = p_IdAudienceType AND IdMark = p_IdMark AND Id != p_Id;
SELECT COUNT(*) INTO v_ExistsRow FROM catalog_audience_types_marks WHERE Id =
p_Id;

IF v_ExistsRow = 1 AND v_ExistsRelation = 0 THEN

SELECT JSON_OBJECT('IdAudienceType', IdAudienceType, 'IdMark', IdMark) INTO
v_OldData FROM catalog_audience_types_marks WHERE Id = p_Id;
UPDATE catalog_audience_types_marks SET IdAudienceType = p_IdAudienceType,
IdMark = p_IdMark WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_AUDIENCE_TYPES_MARKS', 'UPDATE', p_Id,
v_OldData,JSON_OBJECT('IdAudienceType', p_IdAudienceType, 'IdMark',
p_IdMark),p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_AUDIENCE_TYPES_MARKS_GA`(
IN p_search VARCHAR(50),
IN p_isDesc TINYINT(1),
IN p_page INT,
IN p_pageSize INT,
OUT p_totalRecords INT
)
BEGIN
DECLARE v_orderBy VARCHAR(50);
DECLARE v_offset INT;

IF p_isDesc = 1 THEN
SET v_orderBy = ' ORDER BY IdAudienceType DESC, IdMark DESC';
ELSE
SET v_orderBy = ' ORDER BY IdAudienceType ASC, IdMark ASC';
END IF;

IF p_search IS NULL OR p_search = '' THEN

SELECT COUNT(*) INTO p_totalRecords FROM catalog_audience_types_marks;
SET @v_query = CONCAT('SELECT [Link], [Link], [Link]
FROM catalog_audience_types_marks atm INNER JOIN catalog_audience_types at ON
[Link] = [Link] INNER JOIN catalog_marks cm ON [Link] = [Link] ',
v_orderBy);
ELSE
SELECT COUNT(*) INTO p_totalRecords FROM catalog_audience_types_marks atm
INNER JOIN catalog_audience_types at ON [Link] = [Link] INNER JOIN
catalog_marks cm ON [Link] = [Link] WHERE ([Link] LIKE CONCAT('%',
p_search, '%') OR [Link] LIKE CONCAT('%', p_search, '%'));
SET @v_query = CONCAT('SELECT [Link], [Link], [Link] FROM
catalog_audience_types_marks atm INNER JOIN catalog_audience_types at ON
[Link] = [Link] INNER JOIN catalog_marks cm ON [Link] = [Link] WHERE
([Link] LIKE ''%', p_search, '%'' OR [Link] LIKE ''%', p_search,
'%'' ) ', v_orderBy);
 END IF;

IF p_totalRecords > 100 THEN

SET v_offset = (p_page - 1) * p_pageSize;
SET @v_query = CONCAT(@v_query, ' LIMIT ', p_pageSize, ' OFFSET ',
v_offset);
END IF;

PREPARE stmt FROM @v_query;

EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE `CATALOG_MANAGEMENT_UNITS_A`(
IN p_IdCriminalLaw INT,
IN p_ManagementUnitName VARCHAR(200),
IN p_UnitCode VARCHAR(10),
IN p_Status TINYINT(1),
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_management_units WHERE

ManagementUnitName = p_ManagementUnitName COLLATE utf8mb4_unicode_ci;

IF v_ExistsRow = 0 THEN
INSERT INTO catalog_management_units (IdCriminalLaw, ManagementUnitName,
UnitCode, Status) VALUES (p_IdCriminalLaw, p_ManagementUnitName, p_UnitCode,
p_Status);
SET p_Answer = LAST_INSERT_ID();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_MANAGEMENT_UNITS', 'INSERT',
p_Answer,NULL,JSON_OBJECT('IdCriminalLaw', p_IdCriminalLaw, 'ManagementUnitName',
p_ManagementUnitName, 'UnitCode', p_UnitCode, 'Status', p_Status), p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE `CATALOG_MANAGEMENT_UNITS_D`(
IN p_Id INT,
 IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_management_units WHERE Id = p_Id;

IF v_ExistsRow = 1 THEN

SELECT JSON_OBJECT('IdCriminalLaw', IdCriminalLaw, 'ManagementUnitName',

ManagementUnitName, 'UnitCode', UnitCode, 'Status', Status) INTO v_OldData FROM
catalog_management_units WHERE Id = p_Id;
DELETE FROM catalog_management_units WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_MANAGEMENT_UNITS', 'DELETE', p_Id,
v_OldData, NULL, p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE `CATALOG_MANAGEMENT_UNITS_GA`(
IN p_search VARCHAR(50),
IN p_isDesc TINYINT(1),
IN p_page INT,
IN p_pageSize INT,
OUT p_totalRecords INT
)
BEGIN
DECLARE v_orderBy VARCHAR(50);
DECLARE v_offset INT;

IF p_isDesc = 1 THEN
SET v_orderBy = ' ORDER BY [Link] DESC';
ELSE
SET v_orderBy = ' ORDER BY [Link] ASC';
END IF;

IF p_search IS NULL OR p_search = '' THEN

SELECT COUNT(*) INTO p_totalRecords FROM catalog_management_units;
SET @v_query = CONCAT('SELECT [Link], [Link], [Link],
[Link], (CASE WHEN [Link] = 1 THEN ''Alta'' ELSE ''Baja'' END) AS
Status FROM catalog_management_units mu INNER JOIN catalog_criminal_laws cl ON
[Link] = [Link] ', v_orderBy);
ELSE
SELECT COUNT(*) INTO p_totalRecords FROM catalog_management_units mu INNER
JOIN catalog_criminal_laws cl ON [Link] = [Link] WHERE
[Link] COLLATE utf8mb4_general_ci LIKE CONCAT('%', p_search, '%')
OR [Link] COLLATE utf8mb4_general_ci LIKE CONCAT('%', p_search, '%');
SET @v_query = CONCAT('SELECT [Link], [Link], [Link],
[Link], (CASE WHEN [Link] = 1 THEN ''Alta'' ELSE ''Baja'' END) AS
Status FROM catalog_management_units mu INNER JOIN catalog_criminal_laws cl ON
[Link] = [Link] WHERE [Link] COLLATE utf8mb4_general_ci
LIKE ''%', p_search, '%'' OR [Link] COLLATE utf8mb4_general_ci LIKE
''%', p_search, '%'' ', v_orderBy);
END IF;

IF p_totalRecords > 100 THEN

SET v_offset = (p_page - 1) * p_pageSize;
SET @v_query = CONCAT(@v_query, ' LIMIT ', p_pageSize, ' OFFSET ',
v_offset);
END IF;

PREPARE stmt FROM @v_query;

EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE `CATALOG_MANAGEMENT_UNITS_GO`(
IN p_Id INT
)
BEGIN
SELECT [Link], [Link], [Link], [Link], [Link]
FROM catalog_management_units mu INNER JOIN catalog_criminal_laws cl ON
[Link] = [Link] WHERE [Link] = p_Id;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE `CATALOG_MANAGEMENT_UNITS_U`(
IN p_Id INT,
IN p_IdCriminalLaw INT,
IN p_ManagementUnitName VARCHAR(200),
IN p_UnitCode VARCHAR(10),
IN p_Status TINYINT(1),
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_ExistsName INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;
 START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsName FROM catalog_management_units WHERE

ManagementUnitName = p_ManagementUnitName COLLATE utf8mb4_unicode_ci AND Id !=
p_Id;
SELECT COUNT(*) INTO v_ExistsRow FROM catalog_management_units WHERE Id =
p_Id;

IF v_ExistsRow = 1 AND v_ExistsName = 0 THEN

SELECT JSON_OBJECT('IdCriminalLaw', IdCriminalLaw, 'ManagementUnitName',
ManagementUnitName, 'UnitCode', UnitCode, 'Status', Status) INTO v_OldData FROM
catalog_management_units WHERE Id = p_Id;
UPDATE catalog_management_units SET IdCriminalLaw = p_IdCriminalLaw,
ManagementUnitName = p_ManagementUnitName, UnitCode = p_UnitCode, Status =
p_Status WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_MANAGEMENT_UNITS', 'UPDATE', p_Id,v_OldData,
JSON_OBJECT('IdCriminalLaw', p_IdCriminalLaw, 'ManagementUnitName',
p_ManagementUnitName, 'UnitCode', p_UnitCode, 'Status', p_Status), p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_ROOMS_A`(
IN p_RoomName VARCHAR(20),
IN p_RecClassCode VARCHAR(20),
IN p_Flat INT,
IN p_WebRtc VARCHAR(100),
IN p_Hls VARCHAR(100),
IN p_DeferredStreaming VARCHAR(100),
IN p_Channel VARCHAR(50),
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_rooms WHERE RoomName = p_RoomName

COLLATE utf8mb4_unicode_ci;

IF v_ExistsRow = 0 THEN
INSERT INTO catalog_rooms (RoomName, RecClassCode, Flat, WebRtc, Hls,
DeferredStreaming, Channel) VALUES (p_RoomName, p_RecClassCode, p_Flat, p_WebRtc,
p_Hls, p_DeferredStreaming, p_Channel);
SET p_Answer = LAST_INSERT_ID();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_ROOMS', 'INSERT',
p_Answer,NULL,JSON_OBJECT('RoomName', p_RoomName, 'RecClassCode', p_RecClassCode,
'Flat', p_Flat, 'WebRtc', p_WebRtc, 'Hls', p_Hls, 'DeferredStreaming',
p_DeferredStreaming, 'Channel', p_Channel), p_ExecutedBy);
END IF;
COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_ROOMS_D`(
IN p_Id INT,
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_rooms WHERE Id = p_Id;

IF v_ExistsRow = 1 THEN

SELECT JSON_OBJECT('RoomName', RoomName, 'RecClassCode', RecClassCode,

'Flat', Flat, 'WebRtc', WebRtc, 'Hls', Hls, 'DeferredStreaming', DeferredStreaming,
'Channel', Channel) INTO v_OldData FROM catalog_rooms WHERE Id = p_Id;
DELETE FROM catalog_rooms WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_ROOMS', 'DELETE', p_Id, v_OldData, NULL,
p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_ROOMS_GO`(
IN p_Id INT
)
BEGIN
SELECT Id, RoomName, RecClassCode, Flat, WebRtc, Hls, DeferredStreaming,
Channel FROM catalog_rooms WHERE Id = p_Id;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_ROOMS_U`(
IN p_Id INT,
IN p_RoomName VARCHAR(20),
IN p_RecClassCode VARCHAR(20),
IN p_Flat INT,
IN p_WebRtc VARCHAR(100),
IN p_Hls VARCHAR(100),
IN p_DeferredStreaming VARCHAR(100),
IN p_Channel VARCHAR(50),
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_ExistsName INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsName FROM catalog_rooms WHERE RoomName =

p_RoomName COLLATE utf8mb4_unicode_ci AND Id != p_Id;
SELECT COUNT(*) INTO v_ExistsRow FROM catalog_rooms WHERE Id = p_Id;

IF v_ExistsRow = 1 AND v_ExistsName = 0 THEN

SELECT JSON_OBJECT('RoomName', RoomName, 'RecClassCode', RecClassCode,
'Flat', Flat, 'WebRtc', WebRtc, 'Hls', Hls, 'DeferredStreaming', DeferredStreaming,
'Channel', Channel) INTO v_OldData FROM catalog_rooms WHERE Id = p_Id;
UPDATE catalog_rooms SET RoomName = p_RoomName, RecClassCode =
p_RecClassCode, Flat = p_Flat, WebRtc = p_WebRtc, Hls = p_Hls, DeferredStreaming =
p_DeferredStreaming, Channel = p_Channel WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_ROOMS', 'UPDATE', p_Id, v_OldData,
JSON_OBJECT('RoomName', p_RoomName, 'RecClassCode', p_RecClassCode, 'Flat', p_Flat,
'WebRtc', p_WebRtc, 'Hls', p_Hls, 'DeferredStreaming', p_DeferredStreaming,
'Channel', p_Channel), p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_ROOMS_GA`(
IN p_search VARCHAR(50),
IN p_isDesc TINYINT(1),
IN p_page INT,
IN p_pageSize INT,
OUT p_totalRecords INT
)
BEGIN
DECLARE v_orderBy VARCHAR(50);
DECLARE v_offset INT;

IF p_isDesc = 1 THEN
SET v_orderBy = ' ORDER BY RoomName DESC';
ELSE
SET v_orderBy = ' ORDER BY RoomName ASC';
END IF;

IF p_search IS NULL OR p_search = '' THEN

SELECT COUNT(*) INTO p_totalRecords FROM catalog_rooms;
SET @v_query = CONCAT('SELECT Id, RoomName, RecClassCode, Flat, WebRtc,
Hls, DeferredStreaming, Channel FROM catalog_rooms ', v_orderBy);
ELSE
SELECT COUNT(*) INTO p_totalRecords FROM catalog_rooms WHERE RoomName
COLLATE utf8mb4_general_ci LIKE CONCAT('%', p_search, '%');
SET @v_query = CONCAT('SELECT Id, RoomName, RecClassCode, Flat, WebRtc,
Hls, DeferredStreaming, Channel FROM catalog_rooms WHERE RoomName COLLATE
utf8mb4_general_ci LIKE ''%', p_search, '%'' ', v_orderBy);
END IF;

IF p_totalRecords > 100 THEN

SET v_offset = (p_page - 1) * p_pageSize;
SET @v_query = CONCAT(@v_query, ' LIMIT ', p_pageSize, ' OFFSET ',
v_offset);
END IF;

PREPARE stmt FROM @v_query;

EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_USERS_A`(
IN p_IdRole INT,
IN p_IdUGA INT,
IN p_Name VARCHAR(20),
IN p_LastName VARCHAR(25),
IN p_Surname VARCHAR(25),
IN p_Gender ENUM('M', 'F', 'O'),
IN p_Email VARCHAR(80),
IN p_Password VARCHAR(15),
IN p_Status TINYINT(1),
IN p_IsJudge TINYINT(1),
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
 DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_NewId INT DEFAULT NULL;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

IF (p_IdUGA IS NOT NULL AND p_IsJudge = 1) OR (p_IdUGA IS NULL AND p_IsJudge =

0) THEN
SET p_Answer = -2;
ELSE
SELECT COUNT(*) INTO v_ExistsRow FROM catalog_users WHERE Email =
p_Email COLLATE utf8mb4_unicode_ci;

IF v_ExistsRow = 0 THEN

INSERT INTO catalog_users (IdRole, IdUGA, Name, LastName,

Surname, Gender, Email, Password, Status, IsJudge) VALUES (p_IdRole, p_IdUGA,
p_Name, p_LastName, p_Surname, p_Gender, p_Email, SHA2(p_Password, 256), p_Status,
p_IsJudge);
SET p_Answer = LAST_INSERT_ID();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_USERS', 'INSERT', p_Answer,
NULL, JSON_OBJECT('IdRole', p_IdRole, 'IdUGA', p_IdUGA, 'Name', p_Name, 'LastName',
p_LastName, 'Surname', p_Surname, 'Gender', p_Gender, 'Email', p_Email, 'Status',
p_Status, 'IsJudge', p_IsJudge), p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END IF;

END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_USERS_D`(
IN p_Id INT,
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;
 SELECT COUNT(*) INTO v_ExistsRow FROM catalog_users WHERE Id = p_Id;

IF v_ExistsRow = 1 THEN
SELECT JSON_OBJECT('IdRole', IdRole, 'IdUGA', IdUGA, 'Name', Name,
'LastName', LastName, 'Surname', Surname, 'Gender', Gender, 'Email', Email,
'Status', Status, 'IsJudge', IsJudge) INTO v_OldData FROM catalog_users WHERE Id =
p_Id;
DELETE FROM catalog_users WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_USERS', 'DELETE', p_Id, v_OldData,NULL,
p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_USERS_GA`(
IN p_search VARCHAR(50),
IN p_isDesc TINYINT(1),
IN p_page INT,
IN p_pageSize INT,
OUT p_totalRecords INT
)
BEGIN
DECLARE v_orderBy VARCHAR(50);
DECLARE v_offset INT;

IF p_isDesc = 1 THEN
SET v_orderBy = ' ORDER BY Name DESC, LastName DESC';
ELSE
SET v_orderBy = ' ORDER BY Name ASC, LastName ASC';
END IF;

IF p_search IS NULL OR p_search = '' THEN

SELECT COUNT(*) INTO p_totalRecords FROM catalog_users;
SET @v_query = CONCAT('SELECT [Link], [Link] AS RoleName,
[Link] AS UGAName, [Link], [Link], [Link], [Link],
[Link], (CASE WHEN [Link] = 1 THEN ''Alta'' ELSE ''Baja'' END) AS Status,
(CASE WHEN [Link] = 1 THEN ''Sí'' ELSE ''No'' END) AS IsJudge FROM
catalog_users cu LEFT JOIN catalog_roles cr ON [Link] = [Link] INNER JOIN
catalog_management_units cmu ON [Link] = [Link] ', v_orderBy);
ELSE
SELECT COUNT(*) INTO p_totalRecords FROM catalog_users WHERE CONCAT(Name,
' ', LastName, ' ', Surname) COLLATE utf8mb4_general_ci LIKE CONCAT('%', p_search,
'%');
SET @v_query = CONCAT('SELECT [Link], [Link] AS RoleName,
[Link] AS UGAName, [Link], [Link], [Link], [Link],
[Link], (CASE WHEN [Link] = 1 THEN ''Alta'' ELSE ''Baja'' END) AS Status,
(CASE WHEN [Link] = 1 THEN ''Sí'' ELSE ''No'' END) AS IsJudge FROM
catalog_users cu LEFT JOIN catalog_roles cr ON [Link] = [Link] INNER JOIN
catalog_management_units cmu ON [Link] = [Link] WHERE CONCAT(Name, " ",
LastName, " ", Surname) COLLATE utf8mb4_general_ci LIKE ''%', p_search, '%'' ',
v_orderBy);
END IF;

IF p_totalRecords > 100 THEN

SET v_offset = (p_page - 1) * p_pageSize;
SET @v_query = CONCAT(@v_query, ' LIMIT ', p_pageSize, ' OFFSET ',
v_offset);
END IF;

PREPARE stmt FROM @v_query;

EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_USERS_GO`(
IN p_Id INT
)
BEGIN
SELECT Id, IdRole, IdUGA, Name, LastName, Surname, Gender, Email, Status,
IsJudge FROM catalog_users WHERE Id = p_Id;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_USERS_U`(
IN p_Id INT,
IN p_IdRole INT,
IN p_IdUGA INT,
IN p_Name VARCHAR(20),
IN p_LastName VARCHAR(25),
IN p_Surname VARCHAR(25),
IN p_Gender ENUM('M', 'F', 'O'),
IN p_Email VARCHAR(80),
IN p_Password VARCHAR(15),
IN p_Status TINYINT(1),
IN p_IsJudge TINYINT(1),
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_ExistsEmail INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

IF (p_IdUGA IS NOT NULL AND p_IsJudge = 1) OR (p_IdUGA IS NULL AND p_IsJudge

= 0) THEN
SET p_Answer = -2;
ELSE
 SELECT COUNT(*) INTO v_ExistsEmail FROM catalog_users WHERE Email =
p_Email COLLATE utf8mb4_unicode_ci AND Id != p_Id;
SELECT COUNT(*) INTO v_ExistsRow FROM catalog_users WHERE Id = p_Id;

IF v_ExistsRow = 1 AND v_ExistsEmail = 0 THEN

SELECT JSON_OBJECT('IdRole', IdRole, 'IdUGA', IdUGA, 'Name',
Name, 'LastName', LastName, 'Surname', Surname, 'Gender', Gender, 'Email', Email,
'Status', Status, 'IsJudge', IsJudge) INTO v_OldData FROM catalog_users WHERE Id =
p_Id;
UPDATE catalog_users SET IdRole = p_IdRole, IdUGA = p_IdUGA, Name
= p_Name, LastName = p_LastName, Surname = p_Surname, Gender = p_Gender, Email =
p_Email, Password = SHA2(p_Password, 256), Status = p_Status, IsJudge = p_IsJudge
WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_USERS', 'UPDATE',
p_Id,v_OldData, JSON_OBJECT('IdRole', p_IdRole, 'IdUGA', p_IdUGA, 'Name', p_Name,
'LastName', p_LastName, 'Surname', p_Surname, 'Gender', p_Gender, 'Email', p_Email,
'Status', p_Status, 'IsJudge', p_IsJudge), p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;

END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_USERS_UP`(
IN p_Id INT,
IN p_Password VARCHAR(15),
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_users WHERE Id = p_Id;

IF v_ExistsRow = 1 THEN
SELECT JSON_OBJECT('Password', Password) INTO v_OldData FROM
catalog_users WHERE Id = p_Id;
UPDATE catalog_users SET Password = SHA2(p_Password, 256) WHERE Id =
p_Id;
SET p_Answer = ROW_COUNT();
 IF p_Answer > 0 THEN
CALL AUDIT_LOG_A('CATALOG_USERS', 'UPDATE', p_Id, v_OldData,
JSON_OBJECT('Password', SHA2(p_Password, 256)), p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;

END$$
DELIMITER ;

-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_AUDIT_LOGS_GA`(
IN p_search VARCHAR(50),
IN p_isDesc TINYINT(1),
IN p_page INT,
IN p_pageSize INT,
OUT p_totalRecords INT
)
BEGIN
DECLARE v_orderBy VARCHAR(50);
DECLARE v_offset INT;

IF p_isDesc = 1 THEN
SET v_orderBy = ' ORDER BY [Link] DESC';
ELSE
SET v_orderBy = ' ORDER BY [Link] ASC';
END IF;

IF p_search IS NULL OR p_search = '' THEN

SELECT COUNT(*) INTO p_totalRecords FROM audit_logs;
SET @v_query = CONCAT('SELECT [Link], [Link], [Link],
[Link], [Link], [Link], [Link], [Link],
DATE_FORMAT([Link], "%d/%m/%Y") AS ExecutionDate FROM audit_logs al LEFT
JOIN catalog_users cu ON [Link] = [Link] ', v_orderBy);
ELSE
SELECT COUNT(*) INTO p_totalRecords FROM audit_logs al
LEFT JOIN catalog_users cu ON [Link] = [Link] WHERE [Link]
COLLATE utf8mb4_general_ci LIKE CONCAT('%', p_search, '%')
OR [Link] COLLATE utf8mb4_general_ci LIKE CONCAT('%', p_search,
'%') OR [Link] LIKE CONCAT('%', p_search, '%')
OR [Link] COLLATE utf8mb4_general_ci LIKE CONCAT('%', p_search, '%')
OR CONCAT([Link], ' ', [Link], ' ', [Link]) COLLATE utf8mb4_general_ci
LIKE CONCAT('%', p_search, '%');

SET @v_query = CONCAT( 'SELECT [Link],

[Link],[Link],[Link], [Link],[Link], [Link], [Link],
DATE_FORMAT([Link], "%d/%m/%Y") AS ExecutionDate
FROM audit_logs al LEFT JOIN catalog_users cu ON [Link] = [Link]
WHERE [Link] COLLATE utf8mb4_general_ci LIKE ''%', p_search,
'%''
OR [Link] COLLATE utf8mb4_general_ci LIKE ''%', p_search,
'%''
OR [Link] LIKE ''%', p_search, '%'' OR [Link] COLLATE
utf8mb4_general_ci LIKE ''%', p_search, '%''
OR CONCAT([Link], '' '', [Link], '' '', [Link]) COLLATE
utf8mb4_general_ci LIKE ''%', p_search, '%'' ', v_orderBy
);
END IF;

IF p_totalRecords > 100 THEN

SET v_offset = (p_page - 1) * p_pageSize;
SET @v_query = CONCAT(@v_query, ' LIMIT ', p_pageSize, ' OFFSET ',
v_offset);
END IF;

PREPARE stmt FROM @v_query;

EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_USERS_GObyUSERNAMEandPASSWORD`(
IN p_UserName VARCHAR(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
IN p_Password VARCHAR(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
IN p_IpAddress VARCHAR(45)
)
BEGIN
DECLARE v_UserId INT DEFAULT NULL;
DECLARE v_RoleId INT DEFAULT NULL;
DECLARE v_UserCount INT DEFAULT 0;
DECLARE v_Status ENUM('SUCCESS', 'FAILED');
DECLARE v_Reason VARCHAR(255);
DECLARE v_Answer INT;

SELECT Id, IdRole INTO v_UserId, v_RoleId FROM catalog_users WHERE Status = 1
AND Email = p_UserName AND Password = SHA2(p_Password, 256);

IF v_UserId IS NOT NULL THEN

SET v_Status = 'SUCCESS';
SET v_Reason = 'Inicio de sesión correcto';

SELECT [Link] AS UserId, [Link],CONCAT([Link], ' ', [Link], ' ',

COALESCE([Link], '')) AS EmployeeName,[Link], [Link] AS RoleId, [Link]
FROM catalog_users cu INNER JOIN catalog_roles cr ON [Link] = [Link]
WHERE [Link] = 1 AND [Link] = p_UserName AND [Link] =
SHA2(p_Password, 256);

SELECT [Link], [Link]

FROM catalog_roles_permissions rp INNER JOIN catalog_web_pages wp ON [Link]
= [Link] LEFT JOIN catalog_menu cm ON [Link] = [Link]
WHERE [Link] = v_RoleId AND [Link] = 0 AND [Link] IS NOT NULL
GROUP BY [Link], [Link], [Link] ORDER BY [Link];

SELECT [Link], [Link],[Link], [Link],

[Link]
FROM catalog_roles_permissions rp INNER JOIN catalog_web_pages wp ON [Link]
= [Link] LEFT JOIN catalog_menu cm ON [Link] = [Link]
WHERE [Link] = v_RoleId AND [Link] = 0
ORDER BY [Link], [Link];
ELSE
SET v_Status = 'FAILED';
 SET v_Reason = 'Usuario o contraseña incorrectos';
SET v_Answer = -1;
SELECT v_Answer;
END IF;

CALL CATALOG_AUTHENTICATION_LOGS_A(v_UserId, p_UserName, p_IpAddress,

v_Status, v_Reason, v_Answer);

END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_MENU_A`(
IN p_OptionName VARCHAR(20),
IN p_Collapsible INT,
IN p_MenuLevel TINYINT(1),
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_menu WHERE OptionName =

p_OptionName COLLATE utf8mb4_unicode_ci;

IF v_ExistsRow = 0 THEN
INSERT INTO catalog_menu (OptionName, Collapsible, MenuLevel) VALUES
(p_OptionName, p_Collapsible, p_MenuLevel);
SET p_Answer = LAST_INSERT_ID();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_MENU', 'INSERT',
p_Answer,NULL,JSON_OBJECT('OptionName', p_OptionName, 'Collapsible', p_Collapsible,
'MenuLevel', p_MenuLevel), p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_MENU_D`(
IN p_Id INT,
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
 DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_menu WHERE Id = p_Id;

IF v_ExistsRow = 1 THEN
SELECT JSON_OBJECT('OptionName', OptionName, 'Collapsible', Collapsible,
'MenuLevel', MenuLevel) INTO v_OldData FROM catalog_menu WHERE Id = p_Id;
DELETE FROM catalog_menu WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_MENU', 'DELETE', p_Id, v_OldData, NULL,
p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_MENU_GA`(
IN p_search VARCHAR(50),
IN p_isDesc TINYINT(1),
IN p_page INT,
IN p_pageSize INT,
OUT p_totalRecords INT
)
BEGIN
DECLARE v_orderBy VARCHAR(50);
DECLARE v_offset INT;

IF p_isDesc = 1 THEN
SET v_orderBy = ' ORDER BY OptionName DESC';
ELSE
SET v_orderBy = ' ORDER BY OptionName ASC';
END IF;

IF p_search IS NULL OR p_search = '' THEN

SELECT COUNT(*) INTO p_totalRecords FROM catalog_menu;
SET @v_query = CONCAT('SELECT Id, OptionName, Collapsible, MenuLevel FROM
catalog_menu ', v_orderBy);
ELSE
SELECT COUNT(*) INTO p_totalRecords FROM catalog_menu WHERE OptionName
COLLATE utf8mb4_general_ci LIKE CONCAT('%', p_search, '%');
SET @v_query = CONCAT('SELECT Id, OptionName, Collapsible, MenuLevel FROM
catalog_menu WHERE OptionName COLLATE utf8mb4_general_ci LIKE ''%', p_search, '%''
', v_orderBy);
END IF;
 IF p_totalRecords > 100 THEN
SET v_offset = (p_page - 1) * p_pageSize;
SET @v_query = CONCAT(@v_query, ' LIMIT ', p_pageSize, ' OFFSET ',
v_offset);
END IF;

PREPARE stmt FROM @v_query;

EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_MENU_GO`(
IN p_Id INT
)
BEGIN
SELECT Id, OptionName, Collapsible, MenuLevel FROM catalog_menu WHERE Id =
p_Id;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_MENU_U`(
IN p_Id INT,
IN p_OptionName VARCHAR(20),
IN p_Collapsible INT,
IN p_MenuLevel TINYINT(1),
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_ExistsName INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsName FROM catalog_menu WHERE OptionName =

p_OptionName COLLATE utf8mb4_unicode_ci AND Id != p_Id;
SELECT COUNT(*) INTO v_ExistsRow FROM catalog_menu WHERE Id = p_Id;

IF v_ExistsRow = 1 AND v_ExistsName = 0 THEN

SELECT JSON_OBJECT('OptionName', OptionName, 'Collapsible', Collapsible,
'MenuLevel', MenuLevel) INTO v_OldData FROM catalog_menu WHERE Id = p_Id;
UPDATE catalog_menu SET OptionName = p_OptionName, Collapsible =
p_Collapsible, MenuLevel = p_MenuLevel WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_MENU', 'UPDATE', p_Id,v_OldData,
JSON_OBJECT('OptionName', p_OptionName, 'Collapsible', p_Collapsible, 'MenuLevel',
p_MenuLevel),p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_ROLES_A`(
IN p_RoleName VARCHAR(25),
IN p_Status TINYINT(1),
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_roles WHERE RoleName = p_RoleName

COLLATE utf8mb4_unicode_ci;

IF v_ExistsRow = 0 THEN
INSERT INTO catalog_roles (RoleName, Status) VALUES (p_RoleName, p_Status);
SET p_Answer = LAST_INSERT_ID();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_ROLES', 'INSERT', p_Answer,NULL,
JSON_OBJECT('RoleName', p_RoleName, 'Status', p_Status),p_ExecutedBy);
END IF;
COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_ROLES_D`(
IN p_Id INT,
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
 ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_roles WHERE Id = p_Id;

IF v_ExistsRow = 1 THEN
SELECT JSON_OBJECT('RoleName', RoleName, 'Status', Status) INTO v_OldData
FROM catalog_roles WHERE Id = p_Id;
DELETE FROM catalog_roles WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_ROLES', 'DELETE', p_Id, v_OldData,NULL,
p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_ROLES_GA`(
IN p_search VARCHAR(50),
IN p_isDesc TINYINT(1),
IN p_page INT,
IN p_pageSize INT,
OUT p_totalRecords INT
)
BEGIN
DECLARE v_orderBy VARCHAR(50);
DECLARE v_offset INT;

IF p_isDesc = 1 THEN
SET v_orderBy = ' ORDER BY RoleName DESC';
ELSE
SET v_orderBy = ' ORDER BY RoleName ASC';
END IF;

IF p_search IS NULL OR p_search = '' THEN

SELECT COUNT(*) INTO p_totalRecords FROM catalog_roles;
SET @v_query = CONCAT('SELECT Id, RoleName, (CASE WHEN Status = 1 THEN
''Alta'' ELSE ''Baja'' END) AS Status FROM catalog_roles ', v_orderBy);
ELSE
SELECT COUNT(*) INTO p_totalRecords FROM catalog_roles WHERE RoleName
COLLATE utf8mb4_general_ci LIKE CONCAT('%', p_search, '%');
SET @v_query = CONCAT('SELECT Id, RoleName, (CASE WHEN Status = 1 THEN
''Alta'' ELSE ''Baja'' END) AS Status FROM catalog_roles WHERE RoleName COLLATE
utf8mb4_general_ci LIKE ''%', p_search, '%'' ', v_orderBy);
END IF;

IF p_totalRecords > 100 THEN

SET v_offset = (p_page - 1) * p_pageSize;
SET @v_query = CONCAT(@v_query, ' LIMIT ', p_pageSize, ' OFFSET ',
v_offset);
 END IF;

PREPARE stmt FROM @v_query;

EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_ROLES_GO`(
IN p_Id INT
)
BEGIN
SELECT Id, RoleName, Status FROM catalog_roles WHERE Id = p_Id;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_ROLES_U`(
IN p_Id INT,
IN p_RoleName VARCHAR(25),
IN p_Status TINYINT(1),
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_ExistsName INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsName FROM catalog_roles WHERE RoleName =

p_RoleName COLLATE utf8mb4_unicode_ci AND Id != p_Id;
SELECT COUNT(*) INTO v_ExistsRow FROM catalog_roles WHERE Id = p_Id;

IF v_ExistsRow = 1 AND v_ExistsName = 0 THEN

SELECT JSON_OBJECT('RoleName', RoleName, 'Status', Status) INTO v_OldData
FROM catalog_roles WHERE Id = p_Id;
UPDATE catalog_roles SET RoleName = p_RoleName, Status = p_Status WHERE Id
= p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_ROLES', 'UPDATE', p_Id,v_OldData,
JSON_OBJECT('RoleName', p_RoleName, 'Status', p_Status), p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_ROLES_PERMISSIONS_A`(
IN p_IdRole INT,
IN p_IdWebPage INT,
IN p_Deny TINYINT(1),
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_roles_permissions WHERE IdRole =

p_IdRole AND IdWebPage = p_IdWebPage;

IF v_ExistsRow = 0 THEN
INSERT INTO catalog_roles_permissions (IdRole, IdWebPage, Deny) VALUES
(p_IdRole, p_IdWebPage, p_Deny);
SET p_Answer = LAST_INSERT_ID();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_ROLES_PERMISSIONS', 'INSERT',
p_Answer,NULL, JSON_OBJECT('IdRole', p_IdRole, 'IdWebPage', p_IdWebPage, 'Deny',
p_Deny),p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_ROLES_PERMISSIONS_D`(
IN p_Id INT,
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;
 SELECT COUNT(*) INTO v_ExistsRow FROM catalog_roles_permissions WHERE Id =
p_Id;

IF v_ExistsRow = 1 THEN
SELECT JSON_OBJECT('IdRole', IdRole, 'IdWebPage', IdWebPage, 'Deny', Deny)
INTO v_OldData FROM catalog_roles_permissions WHERE Id = p_Id;
DELETE FROM catalog_roles_permissions WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_ROLES_PERMISSIONS', 'DELETE', p_Id,
v_OldData,NULL, p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_ROLES_PERMISSIONS_GA`(
IN p_search VARCHAR(50),
IN p_isDesc TINYINT(1),
IN p_page INT,
IN p_pageSize INT,
OUT p_totalRecords INT
)
BEGIN
DECLARE v_orderBy VARCHAR(50);
DECLARE v_offset INT;

IF p_isDesc = 1 THEN
SET v_orderBy = ' ORDER BY [Link] DESC, [Link] DESC';
ELSE
SET v_orderBy = ' ORDER BY [Link] ASC, [Link] ASC';
END IF;

IF p_search IS NULL OR p_search = '' THEN

SELECT COUNT(*) INTO p_totalRecords FROM catalog_roles_permissions;

SET @v_query = CONCAT( 'SELECT [Link], [Link], [Link], [Link]

FROM catalog_roles_permissions rp
LEFT JOIN catalog_roles cr ON [Link] = [Link]
LEFT JOIN catalog_web_pages wp ON [Link] = [Link] ', v_orderBy);
ELSE
SELECT COUNT(*) INTO p_totalRecords FROM catalog_roles_permissions rp LEFT
JOIN catalog_roles cr ON [Link] = [Link]
LEFT JOIN catalog_web_pages wp ON [Link] = [Link] WHERE [Link]
COLLATE utf8mb4_general_ci LIKE CONCAT('%', p_search, '%')
OR [Link] COLLATE utf8mb4_general_ci LIKE CONCAT('%', p_search,
'%');

SET @v_query = CONCAT('SELECT [Link], [Link], [Link], [Link]

FROM catalog_roles_permissions rp
LEFT JOIN catalog_roles cr ON [Link] = [Link]
LEFT JOIN catalog_web_pages wp ON [Link] = [Link]
 WHERE [Link] COLLATE utf8mb4_general_ci LIKE ''%', p_search, '%''
OR [Link] COLLATE utf8mb4_general_ci LIKE ''%', p_search,
'%'' ', v_orderBy);
END IF;

IF p_totalRecords > 100 THEN

SET v_offset = (p_page - 1) * p_pageSize;
SET @v_query = CONCAT(@v_query, ' LIMIT ', p_pageSize, ' OFFSET ',
v_offset);
END IF;

PREPARE stmt FROM @v_query;

EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_ROLES_PERMISSIONS_GO`(
IN p_Id INT
)
BEGIN
SELECT Id, IdRole, IdWebPage, Deny FROM catalog_roles_permissions WHERE [Link]
= p_Id;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_ROLES_PERMISSIONS_U`(
IN p_Id INT,
IN p_IdRole INT,
IN p_IdWebPage INT,
IN p_Deny TINYINT(1),
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_ExistsPermission INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsPermission FROM catalog_roles_permissions WHERE

IdRole = p_IdRole AND IdWebPage = p_IdWebPage AND Id != p_Id;
SELECT COUNT(*) INTO v_ExistsRow FROM catalog_roles_permissions WHERE Id =
p_Id;

IF v_ExistsRow = 1 AND v_ExistsPermission = 0 THEN

SELECT JSON_OBJECT('IdRole', IdRole, 'IdWebPage', IdWebPage, 'Deny', Deny)
INTO v_OldData FROM catalog_roles_permissions WHERE Id = p_Id;
UPDATE catalog_roles_permissions SET IdRole = p_IdRole, IdWebPage =
p_IdWebPage, Deny = p_Deny WHERE Id = p_Id;
 SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_ROLES_PERMISSIONS', 'UPDATE',
p_Id,v_OldData,JSON_OBJECT('IdRole', p_IdRole, 'IdWebPage', p_IdWebPage, 'Deny',
p_Deny), p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_AUTHENTICATION_LOGS_GA`(
IN p_search VARCHAR(50),
IN p_isDesc TINYINT(1),
IN p_page INT,
IN p_pageSize INT,
OUT p_totalRecords INT
)
BEGIN
DECLARE v_orderBy VARCHAR(50);
DECLARE v_offset INT;

-- Ordenamiento por fecha de intento

IF p_isDesc = 1 THEN
SET v_orderBy = ' ORDER BY [Link] DESC';
ELSE
SET v_orderBy = ' ORDER BY [Link] ASC';
END IF;

IF p_search IS NULL OR p_search = '' THEN

SELECT COUNT(*) INTO p_totalRecords FROM authentication_logs;

SET @v_query = CONCAT(

'SELECT [Link],
[Link],
[Link],
[Link],
[Link],
[Link],
CASE
WHEN [Link] = ''SUCCESS'' THEN ''Éxito''
ELSE ''Fallido''
END AS StatusText,
DATE_FORMAT([Link], "%d/%m/%Y") AS AttemptDate,
[Link]
FROM authentication_logs al
LEFT JOIN catalog_users cu ON [Link] = [Link] ',
v_orderBy);
ELSE
SELECT COUNT(*) INTO p_totalRecords
FROM authentication_logs al
LEFT JOIN catalog_users cu ON [Link] = [Link]
WHERE [Link] COLLATE utf8mb4_general_ci LIKE CONCAT('%', p_search, '%')
OR CONCAT([Link], ' ', [Link], ' ', [Link]) COLLATE
utf8mb4_general_ci LIKE CONCAT('%', p_search, '%')
OR [Link] COLLATE utf8mb4_general_ci LIKE CONCAT('%', p_search, '%');

SET @v_query = CONCAT(

'SELECT [Link],
[Link],
[Link],
[Link],
[Link],
[Link],
CASE
WHEN [Link] = ''SUCCESS'' THEN ''Éxito''
ELSE ''Fallido''
END AS StatusText,
DATE_FORMAT([Link], "%d/%m/%Y") AS AttemptDate,
[Link]
FROM authentication_logs al
LEFT JOIN catalog_users cu ON [Link] = [Link]
WHERE [Link] COLLATE utf8mb4_general_ci LIKE ''%', p_search, '%''
OR CONCAT([Link], '' '', [Link], '' '', [Link]) COLLATE
utf8mb4_general_ci LIKE ''%', p_search, '%''
OR [Link] COLLATE utf8mb4_general_ci LIKE ''%', p_search, '%''
',
v_orderBy);
END IF;

IF p_totalRecords > 100 THEN

SET v_offset = (p_page - 1) * p_pageSize;
SET @v_query = CONCAT(@v_query, ' LIMIT ', p_pageSize, ' OFFSET ',
v_offset);
END IF;

PREPARE stmt FROM @v_query;

EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_AUTHENTICATION_LOGS_A`(
IN p_IdUser INT,
IN p_UserName VARCHAR(80),
IN p_IpAddress VARCHAR(45),
IN p_Status ENUM('SUCCESS', 'FAILED'),
IN p_Reason VARCHAR(255),
OUT p_Answer INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

INSERT INTO authentication_logs (IdUser, UserName, IpAddress, Status,

AttemptTime, Reason) VALUES (p_IdUser, p_UserName, p_IpAddress, p_Status, NOW(),
p_Reason);
 SET p_Answer = LAST_INSERT_ID();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('AUTHENTICATION_LOGS', 'INSERT', p_Answer,NULL,
JSON_OBJECT('IdUser', p_IdUser, 'UserName', p_UserName, 'IpAddress', p_IpAddress,
'Status', p_Status, 'Reason', p_Reason), p_IdUser);
END IF;

COMMIT;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_WEB_PAGES_A`(
IN p_IdMenu INT,
IN p_WebPageName VARCHAR(35),
IN p_NavigateURL VARCHAR(60),
IN p_Status TINYINT(1),
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_web_pages WHERE WebPageName =

p_WebPageName COLLATE utf8mb4_unicode_ci;

IF v_ExistsRow = 0 THEN
INSERT INTO catalog_web_pages (IdMenu, WebPageName, NavigateURL, Status)
VALUES (p_IdMenu, p_WebPageName, p_NavigateURL, p_Status);
SET p_Answer = LAST_INSERT_ID();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_WEB_PAGES', 'INSERT', p_Answer, NULL,
JSON_OBJECT('IdMenu', p_IdMenu, 'WebPageName', p_WebPageName, 'NavigateURL',
p_NavigateURL, 'Status', p_Status),p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_WEB_PAGES_D`(
IN p_Id INT,
IN p_ExecutedBy INT,
OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_web_pages WHERE Id = p_Id;

IF v_ExistsRow = 1 THEN
SELECT JSON_OBJECT('IdMenu', IdMenu, 'WebPageName', WebPageName,
'NavigateURL', NavigateURL, 'Status', Status) INTO v_OldData FROM catalog_web_pages
WHERE Id = p_Id;
DELETE FROM catalog_web_pages WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_WEB_PAGES', 'DELETE', p_Id, v_OldData, NULL,
p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_WEB_PAGES_GA`(
IN p_search VARCHAR(50),
IN p_isDesc TINYINT(1),
IN p_page INT,
IN p_pageSize INT,
OUT p_totalRecords INT
)
BEGIN
DECLARE v_orderBy VARCHAR(50);
DECLARE v_offset INT;

IF p_isDesc = 1 THEN
SET v_orderBy = ' ORDER BY [Link] DESC';
ELSE
SET v_orderBy = ' ORDER BY [Link] ASC';
END IF;

IF p_search IS NULL OR p_search = '' THEN

SELECT COUNT(*) INTO p_totalRecords FROM catalog_web_pages;

SET @v_query = CONCAT(

'SELECT [Link],
[Link] AS MenuOption,
[Link],
[Link],
 CASE WHEN [Link] = 1 THEN ''Activo'' ELSE ''Inactivo'' END
AS StatusText
FROM catalog_web_pages wp
LEFT JOIN catalog_menu cm ON [Link] = [Link] ', v_orderBy);
ELSE
SELECT COUNT(*) INTO p_totalRecords
FROM catalog_web_pages wp
LEFT JOIN catalog_menu cm ON [Link] = [Link]
WHERE [Link] COLLATE utf8mb4_general_ci LIKE CONCAT('%', p_search,
'%')
OR [Link] COLLATE utf8mb4_general_ci LIKE CONCAT('%', p_search,
'%');

SET @v_query = CONCAT(

'SELECT [Link],
[Link] AS MenuOption,
[Link],
[Link],
CASE WHEN [Link] = 1 THEN ''Activo'' ELSE ''Inactivo'' END
AS StatusText
FROM catalog_web_pages wp
LEFT JOIN catalog_menu cm ON [Link] = [Link]
WHERE [Link] COLLATE utf8mb4_general_ci LIKE ''%', p_search,
'%''
OR [Link] COLLATE utf8mb4_general_ci LIKE ''%', p_search,
'%'' ',
v_orderBy);
END IF;

IF p_totalRecords > 100 THEN

SET v_offset = (p_page - 1) * p_pageSize;
SET @v_query = CONCAT(@v_query, ' LIMIT ', p_pageSize, ' OFFSET ',
v_offset);
END IF;

PREPARE stmt FROM @v_query;

EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_WEB_PAGES_GO`(
IN p_Id INT
)
BEGIN
SELECT Id, WebPageName, NavigateURL, Status FROM catalog_web_pages WHERE [Link]
= p_Id;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `CATALOG_WEB_PAGES_U`(
IN p_Id INT,
IN p_IdMenu INT,
IN p_WebPageName VARCHAR(35),
IN p_NavigateURL VARCHAR(60),
IN p_Status TINYINT(1),
IN p_ExecutedBy INT,
 OUT p_Answer INT
)
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_ExistsName INT DEFAULT 0;
DECLARE v_OldData JSON;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsName FROM catalog_web_pages WHERE WebPageName =

p_WebPageName COLLATE utf8mb4_unicode_ci AND Id != p_Id;
SELECT COUNT(*) INTO v_ExistsRow FROM catalog_web_pages WHERE Id = p_Id;

IF v_ExistsRow = 1 AND v_ExistsName = 0 THEN

SELECT JSON_OBJECT('IdMenu', IdMenu, 'WebPageName', WebPageName,
'NavigateURL', NavigateURL, 'Status', Status) INTO v_OldData FROM catalog_web_pages
WHERE Id = p_Id;
UPDATE catalog_web_pages SET IdMenu = p_IdMenu, WebPageName =
p_WebPageName, NavigateURL = p_NavigateURL, Status = p_Status WHERE Id = p_Id;
SET p_Answer = ROW_COUNT();

IF p_Answer > 0 THEN

CALL AUDIT_LOG_A('CATALOG_WEB_PAGES',
'UPDATE',p_Id,v_OldData,JSON_OBJECT('IdMenu', p_IdMenu, 'WebPageName',
p_WebPageName, 'NavigateURL', p_NavigateURL, 'Status', p_Status), p_ExecutedBy);
END IF;

COMMIT;
ELSE
SET p_Answer = -1;
END IF;
END$$
DELIMITER ;
-- -----------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `PASSWORD_TOKENS_A`(
IN p_Email VARCHAR(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
OUT p_Answer VARCHAR(255) )
BEGIN
DECLARE v_ExistsRow INT DEFAULT 0;
DECLARE v_NewId INT DEFAULT NULL;

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN
SET p_Answer = -100;
ROLLBACK;
END;

START TRANSACTION;

SELECT COUNT(*) INTO v_ExistsRow FROM catalog_users WHERE Email = p_Email AND
Status=1;
 IF v_ExistsRow = 1 THEN

INSERT INTO password_tokens (IdUser, Token, ExpiresAt) VALUES ((SELECT

id FROM catalog_users WHERE email = p_Email),SHA2(CONCAT(UUID(), NOW()), 256),
NOW() + INTERVAL 1 HOUR);
SET v_NewId = LAST_INSERT_ID();
SELECT Token INTO p_Answer FROM password_tokens WHERE Id = v_NewId ;
-- -1 es porque es un usuario sin autenticación
IF v_NewId > 0 THEN
CALL AUDIT_LOG_A('PASSWORD_RESET_TOKENS', 'INSERT',
v_NewId,NULL,JSON_OBJECT('Email',p_Email,'Token', p_Answer),-1);
END IF;

COMMIT;
ELSE
SET p_Answer = '-1';
END IF;
END$$
DELIMITER ;
-- ------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE AUDIT_LOG_A(
IN p_TableName VARCHAR(100),
IN p_Operation ENUM('INSERT', 'UPDATE', 'DELETE'),
IN p_RecordId INT,
IN p_OldData TEXT,
IN p_NewData TEXT,
IN p_ExecutedBy INT
-- ,OUT p_Answer INT
)
BEGIN
INSERT INTO audit_logs (TableName, Operation, RecordId, OldData, NewData,
ExecutedBy, ExecutionTime) VALUES (p_TableName, p_Operation, p_RecordId, p_OldData,
p_NewData, p_ExecutedBy, NOW());
-- SET p_Answer = LAST_INSERT_ID();
END$$
DELIMITER ;