-- MySQL dump 10.13 Distrib 8.0.32, for Win64 (x86_64) -- -- Host: 127.0.0.1 Database: db_network_tool -- ------------------------------------------------------ -- Server version 8.0.32 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `account` -- DROP TABLE IF EXISTS `account`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `account` ( `id_account` int NOT NULL AUTO_INCREMENT, `acc_name` varchar(40) NOT NULL, `acc_password` varchar(1000) NOT NULL, `acc_role` int NOT NULL DEFAULT '0', `acc_fullname` varchar(70) NOT NULL, PRIMARY KEY (`id_account`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `account` -- LOCK TABLES `account` WRITE; /*!40000 ALTER TABLE `account` DISABLE KEYS */; INSERT INTO `account` VALUES (1,'admin','$2b$10$Y99gKeYVP.5mf.t3y7AYEOC6GUjxp5QIufCKRMkwtopRX4Tcg9R7O',0,'Joseph Le'); /*!40000 ALTER TABLE `account` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `command_group` -- DROP TABLE IF EXISTS `command_group`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `command_group` ( `id_cmd` int NOT NULL AUTO_INCREMENT, `cmd_message` varchar(100) NOT NULL, `group_id` int NOT NULL, PRIMARY KEY (`id_cmd`), KEY `fk_group_cmd` (`group_id`), CONSTRAINT `fk_group_cmd` FOREIGN KEY (`group_id`) REFERENCES `group` (`id_group`) ) ENGINE=InnoDB AUTO_INCREMENT=129 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `command_group` -- LOCK TABLES `command_group` WRITE; /*!40000 ALTER TABLE `command_group` DISABLE KEYS */; INSERT INTO `command_group` VALUES (51,'show inv',27),(52,'show ver',27),(53,'show post',27),(54,'show logging',27),(55,'show power',27),(58,'show ip',31),(71,'show inv',32),(72,'show ver',32),(73,'show post',32),(74,'sleep 2',36),(75,'send \"\r\"',36),(76,'send \"\r\"',36),(77,'send \"flash_init\r\"',36),(78,'sleep 15',36),(79,'expect \"MORE\"',36),(80,'send \" \"',36),(81,'sleep 50',36),(82,'send \"\r\"',36),(83,'send \"\r\"',36),(84,'expect \"switch:\"',36),(85,'send \"delete flash:config.text\r\"',36),(86,'expect \"?\"',36),(87,'send \"y\r\"',36),(88,'send \"\r\"',36),(89,'expect \"switch:\"',36),(90,'send \"delete flash:private-config.text\r\"',36),(91,'expect \"?\"',36),(92,'send \"y\r\"',36),(93,'send \"\r\"',36),(94,'expect \"switch:\"',36),(95,'sleep 5',36),(96,'send \"\r\"',36),(97,'send \"\r\"',36),(98,'sleep 5',36),(99,'send \"boot\r\"',36),(100,'sleep 5',36),(101,'send \"boot\r\"',36),(102,'sleep 3',37),(103,'send \"\r\"',37),(104,'send \"\r\"',37),(105,'expect \"switch:\"',37),(106,'send \"SWITCH_IGNORE_STARTUP_CFG=1\"',37),(107,'sleep 5',37),(108,'send \"\r\"',37),(109,'send \"\r\"',37),(110,'sleep 5',37),(111,'send \"boot\r\"',37),(112,'sleep 5',37),(113,'send \"boot\r\"',37),(114,'sleep 360',37),(115,'send \"\r\"',37),(116,'send \"\r\"',37),(117,'send \"\r\"',37),(118,'expect \">\"',37),(119,'send \"enable\r\"',37),(120,'sleep 2',37),(121,'send \"\r\"',37),(122,'send \"erase /all non-default\r\"',37),(123,'sleep 5',37),(124,'send \"\r\"',37),(125,'send \"\r\"',37),(126,'sleep 2',37),(127,'send \"reload\r\"',37),(128,'sleep 20',37); /*!40000 ALTER TABLE `command_group` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `command_model` -- DROP TABLE IF EXISTS `command_model`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `command_model` ( `id_cmd` int NOT NULL AUTO_INCREMENT, `cmd_message` varchar(100) NOT NULL, `model_id` varchar(45) NOT NULL, PRIMARY KEY (`id_cmd`), KEY `fk_model_cmd` (`model_id`), CONSTRAINT `fk_model_cmd` FOREIGN KEY (`model_id`) REFERENCES `model` (`id_model`) ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `command_model` -- LOCK TABLES `command_model` WRITE; /*!40000 ALTER TABLE `command_model` DISABLE KEYS */; /*!40000 ALTER TABLE `command_model` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `exclude_error` -- DROP TABLE IF EXISTS `exclude_error`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `exclude_error` ( `id_error` int NOT NULL AUTO_INCREMENT, `err_message` varchar(300) NOT NULL, `err_model` varchar(50) NOT NULL, PRIMARY KEY (`id_error`) ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `exclude_error` -- LOCK TABLES `exclude_error` WRITE; /*!40000 ALTER TABLE `exclude_error` DISABLE KEYS */; INSERT INTO `exclude_error` VALUES (7,'abcdef','WS-C3560V2-24PS-S'),(10,'overruns','WS-C3560V2'),(13,'portasic','All'),(14,'registration failed','WS-C3560V2-24PS-S'); /*!40000 ALTER TABLE `exclude_error` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `group` -- DROP TABLE IF EXISTS `group`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `group` ( `id_group` int NOT NULL AUTO_INCREMENT, `group_name` varchar(100) NOT NULL, `group_action` varchar(50) NOT NULL, `created_by` varchar(50) NOT NULL, `created_at` varchar(50) NOT NULL, PRIMARY KEY (`id_group`) ) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `group` -- LOCK TABLES `group` WRITE; /*!40000 ALTER TABLE `group` DISABLE KEYS */; INSERT INTO `group` VALUES (27,'Switch_test','test','Admin','1676970115959'),(31,'router','UpdateIOS','Admin','1677464774952'),(32,'Default_Test','test','Admin','1677468444927'),(36,'Default_Break-2960/3560/3750','break','Admin','1678074026272'),(37,'Default_Break_anhKen_dat_ten_di','break','Admin','1679474911810'); /*!40000 ALTER TABLE `group` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `group_model` -- DROP TABLE IF EXISTS `group_model`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `group_model` ( `idGroup` int NOT NULL, `idModel` varchar(50) NOT NULL, KEY `fk1_group` (`idGroup`), KEY `fk2_model` (`idModel`), CONSTRAINT `fk1_group` FOREIGN KEY (`idGroup`) REFERENCES `group` (`id_group`), CONSTRAINT `fk2_model` FOREIGN KEY (`idModel`) REFERENCES `model` (`id_model`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `group_model` -- LOCK TABLES `group_model` WRITE; /*!40000 ALTER TABLE `group_model` DISABLE KEYS */; INSERT INTO `group_model` VALUES (27,'WS-C3750X-48P'),(31,'WS-C3750X-48P'),(27,'WS-C3560V2-24PS-S'),(27,'WS-C3750X-48PF-S'),(27,'WS-C3750X-48P-L'),(27,'WS-C3750X-24T-L'),(27,'WS-C3750X-24P-E'),(32,'WS-C2950T-24'),(32,'WS-C2960+48PST-L'),(32,'ISR4331/K9'),(32,'ISR4351/K9'),(32,'WS-C2950SX-24'); /*!40000 ALTER TABLE `group_model` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `line` -- DROP TABLE IF EXISTS `line`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `line` ( `id_line` int NOT NULL AUTO_INCREMENT, `line_ip` varchar(20) NOT NULL, `line_port` int NOT NULL, `line_clear` varchar(2) NOT NULL, `line_gateway` varchar(20) DEFAULT '111.111.111.1', `line_number` varchar(2) NOT NULL, `station_id` int NOT NULL, `enable` int NOT NULL DEFAULT '1', PRIMARY KEY (`id_line`), KEY `fk1` (`station_id`), CONSTRAINT `fk1` FOREIGN KEY (`station_id`) REFERENCES `station` (`id_station`) ) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `line` -- LOCK TABLES `line` WRITE; /*!40000 ALTER TABLE `line` DISABLE KEYS */; INSERT INTO `line` VALUES (25,'172.16.20.17',2018,'3',NULL,'1',1,1),(26,'172.16.20.17',2018,'3',NULL,'12',1,1),(27,'172.16.20.17',2033,'3',NULL,'3',1,1),(28,'172.16.20.17',2000,'3',NULL,'14',1,1),(29,'172.16.20.18',1234,'3',NULL,'5',1,1),(30,'172.16.20.17',1235,'3',NULL,'6',1,1),(31,'172.16.20.17',2018,'18','111.111.111.1','1',2,1),(32,'172.16.20.17',2019,'19','123.123.123.1','2',2,1),(33,'172.16.20.17',2020,'20',NULL,'3',2,1),(34,'172.16.20.18',2022,'22',NULL,'4',2,1),(35,'172.16.20.7',2003,'3',NULL,'1',3,1),(36,'172.16.20.7',2004,'4',NULL,'2',3,1),(37,'',2005,'5',NULL,'3',3,1),(38,'',2006,'6',NULL,'4',3,1),(39,'172.16.20.7',2007,'7',NULL,'5',3,1),(40,'172.16.20.7',2008,'8',NULL,'6',3,1),(41,'172.16.20.7',2009,'9',NULL,'7',3,1),(42,'172.16.20.7',2010,'10',NULL,'8',3,1); /*!40000 ALTER TABLE `line` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `log` -- DROP TABLE IF EXISTS `log`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `log` ( `id_log` int NOT NULL AUTO_INCREMENT, `log_content` text NOT NULL, `log_status` varchar(45) NOT NULL, `log_line` int NOT NULL, `PID` varchar(45) NOT NULL, `SN` varchar(45) NOT NULL, `created_by` varchar(45) NOT NULL, `created_at` varchar(45) NOT NULL, PRIMARY KEY (`id_log`), KEY `fk_line` (`log_line`), CONSTRAINT `fk_line` FOREIGN KEY (`log_line`) REFERENCES `line` (`id_line`) ) ENGINE=InnoDB AUTO_INCREMENT=284 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `log` -- LOCK TABLES `log` WRITE; /*!40000 ALTER TABLE `log` DISABLE KEYS */; INSERT INTO `log` VALUES (275,'./public/filesTest/1679132415257','Error',33,'','','client','1679132424382'),(276,'./public/filesTest/1679132415287','Good',32,'WS-C2950SX-24','FOC1021X1A5','client','1679132437674'),(277,'./public/filesTest/1679132415318','Good',34,'WS-C2960+48PST-L','FOC1922S2N0','client','1679132439007'),(278,'./public/filesTest/1679132415224','Good',31,'WS-C3560V2-24PS-S','FDO1537Y08J','client','1679132440901'),(279,'./public/filesTest/1679270574744','Error',33,'','','client','1679270584330'),(280,'./public/filesTest/1679270574668','Good',32,'WS-C2950SX-24','FOC1021X1A5','client','1679270597695'),(281,'./public/filesTest/1679270574789','Good',34,'WS-C2960+48PST-L','FOC1922S2N0','client','1679270598973'),(282,'./public/filesTest/1679270574707','Good',31,'WS-C3560V2-24PS-S','FDO1537Y08J','client','1679270600801'),(283,'./public/filesTest/1679270651638','Error',33,'','','client','1679270660713'); /*!40000 ALTER TABLE `log` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `model` -- DROP TABLE IF EXISTS `model`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `model` ( `id_model` varchar(50) NOT NULL, `model_type` varchar(10) NOT NULL, `created_at` varchar(45) NOT NULL, PRIMARY KEY (`id_model`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `model` -- LOCK TABLES `model` WRITE; /*!40000 ALTER TABLE `model` DISABLE KEYS */; INSERT INTO `model` VALUES ('CISCO1841','Group','1677036767335'),('ISR4331/K9','Group','1678941086053'),('ISR4351/K9','Group','1678941087981'),('WS-C2950SX-24','Group','1679105872471'),('WS-C2950T-24','Group','1678175742392'),('WS-C2960+48PST-L','Group','1678175845137'),('WS-C3560V2-24PS-S','Group','1677483340689'),('WS-C3750X-24P-E','Group','1677721180101'),('WS-C3750X-24T-L','Group','1677721223262'),('WS-C3750X-48P','Group','1676970145366'),('WS-C3750X-48P-L','Group','1677721208963'),('WS-C3750X-48PF-S','Group','1677721194595'); /*!40000 ALTER TABLE `model` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `station` -- DROP TABLE IF EXISTS `station`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `station` ( `id_station` int NOT NULL AUTO_INCREMENT, `sta_name` varchar(45) NOT NULL, `sta_ip` varchar(20) NOT NULL, `sta_netmask` varchar(20) NOT NULL, `sta_network` varchar(20) NOT NULL, `sta_gateway` varchar(20) NOT NULL, `sta_tftp` varchar(20) NOT NULL, `netmask_line` varchar(20) NOT NULL, `gateway_line` varchar(20) NOT NULL, `updated_by` varchar(45) DEFAULT NULL, `updated_at` varchar(45) DEFAULT NULL, PRIMARY KEY (`id_station`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `station` -- LOCK TABLES `station` WRITE; /*!40000 ALTER TABLE `station` DISABLE KEYS */; INSERT INTO `station` VALUES (1,'US','172.16.20.1','255.255.0.0','192.168.10.0','172.16.5.2','172.16.5.7','','','Admin','21:12:58 - 02/02/2023'),(2,'AU','172.16.20.17','255.255.255.0','192.168.11.0','192.168.11.1','172.16.5.7','255.255.0.0','172.16.5.2','Admin','21:12:58 - 02/02/2023'),(3,'VN','172.16.20.7','255.255.0.0','192.168.11.0','172.16.5.2','172.16.5.8','','','Admin','21:12:58 - 02/02/2023'); /*!40000 ALTER TABLE `station` ENABLE KEYS */; UNLOCK TABLES; -- -- Dumping routines for database 'db_network_tool' -- /*!50003 DROP PROCEDURE IF EXISTS `pro_account_addAccount` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_account_addAccount`( in pUsername text, in pPassword text, in pRole text, in pFullName text) BEGIN insert into `account`(acc_name, acc_password, acc_role, acc_fullname) values (pUsername, pPassword, pRole, pFullName); select 1 as pCode; select concat("add user") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_account_getAccountByUsername` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_account_getAccountByUsername`(in pUsername text) BEGIN select * from `account` where acc_name = pUsername; select 1 as pCode; select concat("get user by id") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_device_addError` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_device_addError`( in pMessage text, in pModel text) BEGIN insert into exclude_error(err_message, err_model) values (pMessage, pModel); select 1 as pCode; select concat("add exclude error") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_device_deleteError` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_device_deleteError`( in pId text) BEGIN delete from exclude_error where id_error=pId; select 1 as pCode; select concat("delete exclude error") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_device_getAllError` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_device_getAllError`() BEGIN select * from exclude_error; select 1 as pCode; select concat("get all exclude error") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_group_addCmdForGroup` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_group_addCmdForGroup`( in pIdGroup text, in pMessage text) BEGIN insert into `command_group`(group_id, cmd_message) values (pIdGroup, pMessage); select 1 as pCode; select concat("add command group") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_group_addCmdForModel` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_group_addCmdForModel`( in pIdModel text, in pMessage text) BEGIN insert into `command_model`(model_id, cmd_message) values (pIdModel, pMessage); select 1 as pCode; select concat("add command model") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_group_addGroup` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_group_addGroup`( in pNameGroup text, in pCreatedBy text, in pCreatedAt text, in pAction text) BEGIN insert into `group`(group_name, created_by, created_at, group_action) values (pNameGroup, pCreatedBy, pCreatedAt, pAction); select id_group from `group` where created_at = pCreatedAt; select 1 as pCode; select concat("add group") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_group_addModel` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_group_addModel`( in pIdModel text, in pIdGroup text, in pType text, in pCreatedAt text) BEGIN declare checkModel int(2) default -1; select 1 into checkModel from model where id_model = pIdModel; IF(pType = "Group") THEN insert into `model`(id_model, model_type, created_at) values (pIdModel, pType, pCreatedAt); insert into `group_model`(idModel, idGroup) values (pIdModel, pIdGroup); select 1 as pCode; select concat("add model group") as pMess; ELSE insert into `model`(id_model, model_type, created_at) values (pIdModel, pType, pCreatedAt); select 1 as pCode; select concat("add model single") as pMess; END IF; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_group_addModelForGroup` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_group_addModelForGroup`( in pIdGroup text, in pIdModel text) BEGIN declare checkModel int(2) default -1; select 1 into checkModel from model where id_model = pIdModel; IF(checkModel = 1) THEN insert into `group_model`(idGroup, idModel) values (pIdGroup, pIdModel); END IF; select 1 as pCode; select concat("add model for group") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_group_clearCmdOfModel` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_group_clearCmdOfModel`( in pIdModel text) BEGIN delete from command_model where model_id = pIdModel; select 1 as pCode; select concat("clear cmd of model") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_group_clearGroupOfModel` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_group_clearGroupOfModel`( in pIdModel text) BEGIN delete from group_model where idModel = pIdModel; select 1 as pCode; select concat("clear group of model") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_group_deleteCmdOfGroup` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_group_deleteCmdOfGroup`( in pMessage text, in pIdGroup text) BEGIN delete from command_group where group_id=pIdGroup and cmd_message = pMessage; select 1 as pCode; select concat("delete cmd of group") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_group_deleteCmdOfModel` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_group_deleteCmdOfModel`( in pMessage text, in pIdModel text) BEGIN delete from command_model where model_id=pIdModel and cmd_message = pMessage; select 1 as pCode; select concat("delete cmd of model") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_group_deleteGroup` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_group_deleteGroup`( in pIdGroup text) BEGIN delete from command_group where group_id=pIdGroup; delete from group_model where idGroup=pIdGroup; delete from `group` where id_group=pIdGroup; select 1 as pCode; select concat("delete group") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_group_deleteModel` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_group_deleteModel`( in pIdModel text) BEGIN delete from command_model where model_id=pIdModel; delete from group_model where idModel=pIdModel; delete from `model` where id_model=pIdModel; select 1 as pCode; select concat("delete model") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_group_deleteModelOfGroup` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_group_deleteModelOfGroup`( in pIdModel text, in pIdGroup text) BEGIN delete from group_model where idGroup=pIdGroup and idModel = pIdModel; select 1 as pCode; select concat("delete model of group") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_group_editGroup` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_group_editGroup`(in pId text, in pName text) BEGIN update `group` set group_name = pName where id_group = pId; select 1 as pCode; select concat("edit information group") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_group_editModel` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_group_editModel`(in pId text, in pType text) BEGIN update `model` set model_type = pType where id_model = pId; select 1 as pCode; select concat("edit information model") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_group_getAllCmdOfGroup` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_group_getAllCmdOfGroup`(in pIdGroup text) BEGIN select * from command_group where group_id=pIdGroup; select 1 as pCode; select concat("get all command of group") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_group_getAllCmdOfModel` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_group_getAllCmdOfModel`(in pIdModel text) BEGIN select * from command_model where model_id=pIdModel; select 1 as pCode; select concat("get all command of model") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_group_getAllGroup` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_group_getAllGroup`() BEGIN select * from `group`; select 1 as pCode; select concat("get all group") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_group_getAllGroupOfModel` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_group_getAllGroupOfModel`(in pIdModel text) BEGIN select * from `model`, `group`, group_model where id_model=idModel and id_group = idGroup and idModel=pIdModel; select 1 as pCode; select concat("get all group of model") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_group_getAllModel` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_group_getAllModel`() BEGIN select * from `model`; select 1 as pCode; select concat("get all model") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_group_getAllModelOfGroup` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_group_getAllModelOfGroup`(in pIdGroup text) BEGIN select * from `model`, `group`, group_model where id_model=idModel and id_group = idGroup and idGroup=pIdGroup; select 1 as pCode; select concat("get all model of group") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_log_addLog` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; CREATE PROCEDURE `pro_log_addLog`( in pIdLine text, in pContent text, in pStatus text, in pCreatedAt text, in pCreatedBy text , in pPID text, in pSN text) BEGIN insert into `log`(log_content, log_status, log_line, created_at, created_by, PID, SN) values (pContent, pStatus, pIdLine, pCreatedAt, pCreatedBy,pPID,pSN); select 1 as pCode; select concat("add log") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_log_getAllLogOfLine` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_log_getAllLogOfLine`( in pIdLine text) BEGIN select * from `log` where log_line = pIdLine order by created_at desc; select 1 as pCode; select concat("add log") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_station_addLineForStation` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_station_addLineForStation`( in pNumber text, in pPort text, in pClear text, in pIp text, in pStation text) BEGIN insert into line(line_number, line_port, line_clear, line_ip, station_id) values (pNumber, pPort, pClear, pIp, pStation); select 1 as pCode; select concat("add line for station") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_station_editLine` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_station_editLine`(in pId text, in pNumber text, in pPort text, in pClear text, in pIp text) BEGIN update line set line_number = pNumber, line_port = pPort, line_clear = pClear, line_ip = pIp where id_line = pId; select 1 as pCode; select concat("edit information line") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_station_editStation` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_station_editStation`(in pId text, in pIp text, in pTftp text, in pNetwork text, in pNetmask text, in pGateway text, in pGatewayLine text, in pNetmaskLine text) BEGIN update station set sta_ip = pIp, sta_tftp = pTftp, sta_network = pNetwork, sta_netmask = pNetmask, sta_gateway = pGateway, gateway_line = pGatewayLine, netmask_line = pNetmaskLine where id_station = pId; select 1 as pCode; select concat("edit information station") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_station_enableLine` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = '' */ ; DELIMITER ;; CREATE PROCEDURE `pro_station_enableLine`(in pId text, in pStatus text) BEGIN update line set `enable` = pStatus where id_line = pId; select 1 as pCode; select concat("edit enable line") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_station_getAllLineOfStation` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_station_getAllLineOfStation`(in pId text) BEGIN select * from line where station_id = pId; select 1 as pCode; select concat("get all line of station") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_station_getAllStation` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_station_getAllStation`() BEGIN select * from station; select 1 as pCode; select concat("get all station") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `pro_test_getCommandByModel` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8mb4 */ ; /*!50003 SET character_set_results = utf8mb4 */ ; /*!50003 SET collation_connection = utf8mb4_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; CREATE PROCEDURE `pro_test_getCommandByModel`( in pIdModel text, in pAction text) BEGIN select id_group from model, `group`, group_model where id_model = idModel and id_group=idGroup AND id_model=pIdModel AND group_action=pAction; #select 1 as pCode; #select concat("delete model") as pMess; END ;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2023-03-22 2:25:11