From 0516f1fa27daf3315bf883684bbd6191faf68844 Mon Sep 17 00:00:00 2001 From: Mitsuo Tokumori Date: Wed, 1 Jun 2022 02:03:17 -0500 Subject: Fixed typo --- back/sql/20220401dump.sampledata.sql | 85 ------------ back/sql/20220401dump.tables+views.sql | 245 --------------------------------- back/sql/20220601dump.sampledata.sql | 85 ++++++++++++ back/sql/20220601dump.tables+views.sql | 245 +++++++++++++++++++++++++++++++++ 4 files changed, 330 insertions(+), 330 deletions(-) delete mode 100644 back/sql/20220401dump.sampledata.sql delete mode 100644 back/sql/20220401dump.tables+views.sql create mode 100644 back/sql/20220601dump.sampledata.sql create mode 100644 back/sql/20220601dump.tables+views.sql (limited to 'back') diff --git a/back/sql/20220401dump.sampledata.sql b/back/sql/20220401dump.sampledata.sql deleted file mode 100644 index 5042e32..0000000 --- a/back/sql/20220401dump.sampledata.sql +++ /dev/null @@ -1,85 +0,0 @@ --- Adminer 4.7.9 MySQL dump - -SET NAMES utf8; -SET time_zone = '+00:00'; -SET foreign_key_checks = 0; -SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; - -SET NAMES utf8mb4; - -TRUNCATE `almacen`; -INSERT INTO `almacen` (`id`, `activo`, `ubigeo`, `departamento`, `provincia`, `latitud`, `longitud`, `esPrincipal`, `region_fk`) VALUES -(1, 1, '10201', 'Amazonas', 'Bagua', -5.63906152, -78.53166353, 0, 3), -(2, 1, '40101', 'Arequipa', 'Arequipa', -16.39881421, -71.537019649, 1, 1), -(3, 1, '50901', 'Ayacucho', 'Sucre', -14.01125749, -73.83884087, 0, 2), -(4, 1, '130101', 'La Libertad', 'Trujillo', -8.11176389, -79.02868652, 1, 1), -(5, 1, '150101', 'Lima', 'Lima', -12.04591952, -77.03049615, 1, 1), -(6, 1, '230101', 'Tacna', 'Tacna', -18.0137008, -70.2507964, 0, 1); - -TRUNCATE `averia`; - -TRUNCATE `camion`; -INSERT INTO `camion` (`id`, `activo`, `placa`, `kilometraje`, `estado`, `tipoCamion_fk`) VALUES -(1, 1, 'ETALLIC', 69420, 1, 1); - -TRUNCATE `cliente`; -INSERT INTO `cliente` (`id`, `activo`, `nombreCompleto`, `dni`, `correo`, `telefono`) VALUES -(1, 1, 'EmpresaAAA', '1234567890', 'contacto@empresaAAA.com', '123456789'), -(2, 1, 'EmpresaBBB', '1234567890', 'contacto@empresaBBB.com', '123456789'); - -TRUNCATE `pedido`; -INSERT INTO `pedido` (`id`, `activo`, `fechaPedido`, `cantidad`, `plazoEntrega`, `estado`, `codigoPedido`, `almacen_fk`, `cliente_fk`, `usuario_fk`, `PTG_fk`) VALUES -(2, 1, '2022-01-01 00:00:00', 10, 1, 0, '20220101_000001', 3, 1, 4, NULL); - -TRUNCATE `PTG`; -INSERT INTO `PTG` (`id`, `activo`, `fechaGenerado`, `esSimulacion`, `usuario_fk`) VALUES -(1, 1, '2022-06-01 05:10:47', 1, 1); - -TRUNCATE `region`; -INSERT INTO `region` (`id`, `activo`, `nombre`) VALUES -(1, 1, 'costa'), -(2, 1, 'sierra'), -(3, 1, 'selva'); - -TRUNCATE `rol`; -INSERT INTO `rol` (`id`, `activo`, `nombre`) VALUES -(1, 1, 'superuser'); - -TRUNCATE `ruta`; -INSERT INTO `ruta` (`id`, `activo`, `fechaInicio`, `fechaFin`, `PTG_fk`, `camion_fk`) VALUES -(1, 1, '2022-01-01 00:00:00', '2022-01-02 00:00:00', 1, 1); - -TRUNCATE `tipoAveria`; - -TRUNCATE `tipoCamion`; -INSERT INTO `tipoCamion` (`id`, `activo`, `nombre`, `capacidad`) VALUES -(1, 1, 'tipoA', 90), -(2, 1, 'tipoB', 45), -(3, 1, 'tipoC', 30); - -TRUNCATE `tramo`; -INSERT INTO `tramo` (`id`, `activo`, `distancia`, `estaBloqueado`, `almacen1_fk`, `almacen2_fk`) VALUES -(1, 1, 10, 0, 2, 5), -(2, 1, 10, 0, 3, 5), -(3, 1, 10, 0, 4, 5), -(4, 1, 10, 0, 6, 5), -(5, 1, 20, 0, 1, 4), -(6, 1, 20, 0, 4, 1), -(7, 1, 10, 0, 5, 2), -(8, 1, 10, 0, 5, 3), -(9, 1, 10, 0, 5, 4), -(10, 1, 10, 0, 5, 6); - -TRUNCATE `tramo_ruta`; -INSERT INTO `tramo_ruta` (`id`, `activo`, `orden`, `horaSalidaEst`, `horaLlegadaEst`, `horaSalidaReal`, `horaLlegadaReal`, `paquetesEntregar`, `ruta_fk`, `tramo_fk`) VALUES -(1, 1, 1, '2022-01-01 10:00:00', '2022-01-01 15:00:00', NULL, NULL, 0, 1, 9), -(2, 1, 2, '2022-01-01 16:00:00', '2022-01-01 21:00:00', NULL, NULL, 10, 1, 6); - -TRUNCATE `usuario`; -INSERT INTO `usuario` (`id`, `activo`, `usuario`, `clave`, `nombre`, `apellido`, `dni`, `correo`, `creador_fk`, `rol_fk`) VALUES -(1, 1, 'gabs', '*6AB7931FB2D104A478932D23AF4C7700D1A0C61A', 'Gabriela', 'Hernandez', '12345678', 'gabs@pucp.edu.pe', NULL, 1), -(2, 1, 'dayana', '*D279131EA232308E083B3EA192B78A0C15EA8701', 'Dayana', 'Alarcon', '12345678', 'dayana@pucp.edu.pe', 1, 1), -(3, 1, 'jannina', '*D279131EA232308E083B3EA192B84A0C15EA8701', 'Jannina', 'DeLosGodos', '12345678', 'jannina@pucp.edu.pe', 1, 1), -(4, 1, 'mitsuo', '*D279131EA232308E083B3EA192B74A0C15EA8701', 'Mitsuo', 'Tokumori', '12345678', 'mitsuo@pucp.edu.pe', 1, 1); - --- 2022-06-01 06:13:09 diff --git a/back/sql/20220401dump.tables+views.sql b/back/sql/20220401dump.tables+views.sql deleted file mode 100644 index 7ff1c24..0000000 --- a/back/sql/20220401dump.tables+views.sql +++ /dev/null @@ -1,245 +0,0 @@ --- Adminer 4.7.9 MySQL dump - -SET NAMES utf8; -SET time_zone = '+00:00'; -SET foreign_key_checks = 0; -SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; - -SET NAMES utf8mb4; - -DROP TABLE IF EXISTS `almacen`; -CREATE TABLE `almacen` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `activo` int(11) NOT NULL, - `ubigeo` varchar(10) NOT NULL, - `departamento` varchar(100) NOT NULL, - `provincia` varchar(100) NOT NULL, - `latitud` double NOT NULL, - `longitud` double NOT NULL, - `esPrincipal` int(11) NOT NULL, - `region_fk` int(11) NOT NULL, - PRIMARY KEY (`id`), - KEY `region_fk` (`region_fk`), - CONSTRAINT `almacen_ibfk_1` FOREIGN KEY (`region_fk`) REFERENCES `region` (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; - - -DROP TABLE IF EXISTS `averia`; -CREATE TABLE `averia` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `activo` int(11) NOT NULL, - `descripcion` varchar(1000) NOT NULL, - `fechaRegistro` datetime NOT NULL, - `camion_fk` int(11) NOT NULL, - `tipoAveria_fk` int(11) NOT NULL, - PRIMARY KEY (`id`), - KEY `camion_fk` (`camion_fk`), - KEY `tipoAveria_fk` (`tipoAveria_fk`), - CONSTRAINT `averia_ibfk_1` FOREIGN KEY (`camion_fk`) REFERENCES `camion` (`id`), - CONSTRAINT `averia_ibfk_2` FOREIGN KEY (`tipoAveria_fk`) REFERENCES `tipoAveria` (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; - - -DROP TABLE IF EXISTS `camion`; -CREATE TABLE `camion` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `activo` int(11) NOT NULL, - `placa` varchar(10) NOT NULL, - `kilometraje` double NOT NULL, - `estado` int(11) NOT NULL, - `tipoCamion_fk` int(11) NOT NULL, - PRIMARY KEY (`id`), - KEY `tipoCamion_fk` (`tipoCamion_fk`), - CONSTRAINT `camion_ibfk_1` FOREIGN KEY (`tipoCamion_fk`) REFERENCES `tipoCamion` (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; - - -DROP TABLE IF EXISTS `cliente`; -CREATE TABLE `cliente` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `activo` int(11) NOT NULL, - `nombreCompleto` varchar(100) NOT NULL, - `dni` varchar(10) NOT NULL, - `correo` varchar(100) NOT NULL, - `telefono` varchar(20) NOT NULL, - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; - - -DROP TABLE IF EXISTS `pedido`; -CREATE TABLE `pedido` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `activo` int(11) NOT NULL, - `fechaPedido` datetime NOT NULL, - `cantidad` int(11) NOT NULL, - `plazoEntrega` int(11) NOT NULL, - `estado` int(11) NOT NULL, - `codigoPedido` varchar(100) DEFAULT NULL, - `almacen_fk` int(11) NOT NULL, - `cliente_fk` int(11) NOT NULL, - `usuario_fk` int(11) NOT NULL, - `PTG_fk` int(11) DEFAULT NULL, - PRIMARY KEY (`id`), - KEY `usuario_fk` (`usuario_fk`), - KEY `PTG_fk` (`PTG_fk`), - KEY `cliente_fk` (`cliente_fk`), - KEY `almacen_fk` (`almacen_fk`), - CONSTRAINT `pedido_ibfk_1` FOREIGN KEY (`usuario_fk`) REFERENCES `usuario` (`id`), - CONSTRAINT `pedido_ibfk_2` FOREIGN KEY (`PTG_fk`) REFERENCES `PTG` (`id`), - CONSTRAINT `pedido_ibfk_3` FOREIGN KEY (`cliente_fk`) REFERENCES `cliente` (`id`), - CONSTRAINT `pedido_ibfk_4` FOREIGN KEY (`almacen_fk`) REFERENCES `almacen` (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; - - -DROP TABLE IF EXISTS `PTG`; -CREATE TABLE `PTG` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `activo` int(11) NOT NULL, - `fechaGenerado` datetime NOT NULL, - `esSimulacion` int(11) NOT NULL, - `usuario_fk` int(11) DEFAULT NULL, - PRIMARY KEY (`id`), - KEY `usuario_fk` (`usuario_fk`), - CONSTRAINT `PTG_ibfk_1` FOREIGN KEY (`usuario_fk`) REFERENCES `usuario` (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; - - -DROP TABLE IF EXISTS `region`; -CREATE TABLE `region` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `activo` int(11) NOT NULL, - `nombre` varchar(100) NOT NULL, - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; - - -DROP TABLE IF EXISTS `rol`; -CREATE TABLE `rol` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `activo` int(11) NOT NULL, - `nombre` varchar(100) NOT NULL, - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; - - -DROP TABLE IF EXISTS `ruta`; -CREATE TABLE `ruta` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `activo` int(11) NOT NULL, - `fechaInicio` datetime NOT NULL, - `fechaFin` datetime NOT NULL, - `PTG_fk` int(11) NOT NULL, - `camion_fk` int(11) NOT NULL, - PRIMARY KEY (`id`), - KEY `PTG_fk` (`PTG_fk`), - KEY `camion_fk` (`camion_fk`), - CONSTRAINT `ruta_ibfk_1` FOREIGN KEY (`PTG_fk`) REFERENCES `PTG` (`id`), - CONSTRAINT `ruta_ibfk_2` FOREIGN KEY (`camion_fk`) REFERENCES `camion` (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; - - -DROP TABLE IF EXISTS `tipoAveria`; -CREATE TABLE `tipoAveria` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `activo` int(11) NOT NULL, - `nombre` int(11) NOT NULL, - `duracion` int(11) NOT NULL, - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; - - -DROP TABLE IF EXISTS `tipoCamion`; -CREATE TABLE `tipoCamion` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `activo` int(11) NOT NULL, - `nombre` varchar(100) NOT NULL, - `capacidad` int(11) NOT NULL, - PRIMARY KEY (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; - - -DROP TABLE IF EXISTS `tramo`; -CREATE TABLE `tramo` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `activo` int(11) NOT NULL, - `distancia` double NOT NULL, - `estaBloqueado` int(11) NOT NULL, - `almacen1_fk` int(11) NOT NULL, - `almacen2_fk` int(11) NOT NULL, - PRIMARY KEY (`id`), - KEY `almacen1_fk` (`almacen1_fk`), - KEY `almacen2_fk` (`almacen2_fk`), - CONSTRAINT `tramo_ibfk_1` FOREIGN KEY (`almacen1_fk`) REFERENCES `almacen` (`id`), - CONSTRAINT `tramo_ibfk_2` FOREIGN KEY (`almacen2_fk`) REFERENCES `almacen` (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; - - -DROP TABLE IF EXISTS `tramo_ruta`; -CREATE TABLE `tramo_ruta` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `activo` int(11) NOT NULL, - `orden` int(11) NOT NULL, - `horaSalidaEst` datetime NOT NULL, - `horaLlegadaEst` datetime NOT NULL, - `horaSalidaReal` datetime DEFAULT NULL, - `horaLlegadaReal` datetime DEFAULT NULL, - `paquetesEntregar` int(11) NOT NULL, - `ruta_fk` int(11) NOT NULL, - `tramo_fk` int(11) NOT NULL, - PRIMARY KEY (`id`), - KEY `ruta_fk` (`ruta_fk`), - KEY `tramo_fk` (`tramo_fk`), - CONSTRAINT `tramo_ruta_ibfk_1` FOREIGN KEY (`ruta_fk`) REFERENCES `ruta` (`id`), - CONSTRAINT `tramo_ruta_ibfk_2` FOREIGN KEY (`tramo_fk`) REFERENCES `tramo` (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; - - -DROP TABLE IF EXISTS `usuario`; -CREATE TABLE `usuario` ( - `id` int(11) NOT NULL AUTO_INCREMENT, - `activo` int(11) NOT NULL, - `usuario` varchar(100) NOT NULL, - `clave` varchar(100) NOT NULL, - `nombre` varchar(100) NOT NULL, - `apellido` varchar(100) NOT NULL, - `dni` varchar(10) NOT NULL, - `correo` varchar(100) NOT NULL, - `creador_fk` int(11) DEFAULT NULL, - `rol_fk` int(11) NOT NULL, - PRIMARY KEY (`id`), - KEY `creador_fk` (`creador_fk`), - KEY `rol_fk` (`rol_fk`), - CONSTRAINT `usuario_ibfk_2` FOREIGN KEY (`creador_fk`) REFERENCES `usuario` (`id`), - CONSTRAINT `usuario_ibfk_3` FOREIGN KEY (`rol_fk`) REFERENCES `usuario` (`id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; - - -DROP VIEW IF EXISTS `v_almacen`; -CREATE TABLE `v_almacen` (`ubigeo` varchar(10), `departamento` varchar(100), `provincia` varchar(100), `latitud` double, `longitud` double, `esPrincipal` int(11), `region` varchar(100)); - - -DROP VIEW IF EXISTS `v_camion`; -CREATE TABLE `v_camion` (`id` int(11), `placa` varchar(10), `kilometraje` double, `estado` int(11), `tipo` varchar(100), `capacidad` int(11)); - - -DROP VIEW IF EXISTS `v_pedido`; -CREATE TABLE `v_pedido` (`cliente` varchar(100), `RUC` varchar(10), `ubigeo` varchar(10), `provincia` varchar(100), `cantidad` int(11), `fechaPedido` datetime, `plazoEntrega` int(11), `estado` int(11), `URL_pedido` varchar(100), `registrado por` varchar(100)); - - -DROP VIEW IF EXISTS `v_usuario`; -CREATE TABLE `v_usuario` (`usuario` varchar(100), `nombre` varchar(100), `apellido` varchar(100), `dni` varchar(10), `correo` varchar(100), `registered by` varchar(100), `rol` varchar(100)); - - -DROP TABLE IF EXISTS `v_almacen`; -CREATE ALGORITHM=UNDEFINED DEFINER=`gabs`@`%.%.%.%` SQL SECURITY DEFINER VIEW `v_almacen` AS select `a`.`ubigeo` AS `ubigeo`,`a`.`departamento` AS `departamento`,`a`.`provincia` AS `provincia`,`a`.`latitud` AS `latitud`,`a`.`longitud` AS `longitud`,`a`.`esPrincipal` AS `esPrincipal`,`r`.`nombre` AS `region` from (`almacen` `a` join `region` `r` on(`a`.`region_fk` = `r`.`id`)) where a.activo = 1; - -DROP TABLE IF EXISTS `v_camion`; -CREATE ALGORITHM=UNDEFINED DEFINER=`gabs`@`%.%.%.%` SQL SECURITY DEFINER VIEW `v_camion` AS select `c`.`id` AS `id`,`c`.`placa` AS `placa`,`c`.`kilometraje` AS `kilometraje`,`c`.`estado` AS `estado`,`tc`.`nombre` AS `tipo`,`tc`.`capacidad` AS `capacidad` from (`camion` `c` join `tipoCamion` `tc` on(`c`.`tipoCamion_fk` = `tc`.`id`)) where c.activo = 1; - -DROP TABLE IF EXISTS `v_pedido`; -CREATE ALGORITHM=UNDEFINED DEFINER=`gabs`@`%.%.%.%` SQL SECURITY DEFINER VIEW `v_pedido` AS select `cli`.`nombreCompleto` AS `cliente`,`cli`.`dni` AS `RUC`,`alm`.`ubigeo` AS `ubigeo`,`alm`.`provincia` AS `provincia`,`ped`.`cantidad` AS `cantidad`,`ped`.`fechaPedido` AS `fechaPedido`,`ped`.`plazoEntrega` AS `plazoEntrega`,`ped`.`estado` AS `estado`,`ped`.`codigoPedido` AS `URL_pedido`,`usu`.`usuario` AS `registrado por` from (((`pedido` `ped` join `cliente` `cli` on(`ped`.`cliente_fk` = `cli`.`id`)) join `usuario` `usu` on(`ped`.`usuario_fk` = `usu`.`id`)) join `almacen` `alm` on(`ped`.`almacen_fk` = `alm`.`id`)) where ped.activo = 1; - -DROP TABLE IF EXISTS `v_usuario`; -CREATE ALGORITHM=UNDEFINED DEFINER=`gabs`@`%.%.%.%` SQL SECURITY DEFINER VIEW `v_usuario` AS select `u`.`usuario` AS `usuario`,`u`.`nombre` AS `nombre`,`u`.`apellido` AS `apellido`,`u`.`dni` AS `dni`,`u`.`correo` AS `correo`,`u2`.`nombre` AS `registered by`,`r`.`nombre` AS `rol` from ((`usuario` `u` left join `usuario` `u2` on(`u`.`creador_fk` = `u2`.`id`)) join `rol` `r` on(`u`.`rol_fk` = `r`.`id`)) where u.activo = 1; - --- 2022-06-01 06:30:20 diff --git a/back/sql/20220601dump.sampledata.sql b/back/sql/20220601dump.sampledata.sql new file mode 100644 index 0000000..5042e32 --- /dev/null +++ b/back/sql/20220601dump.sampledata.sql @@ -0,0 +1,85 @@ +-- Adminer 4.7.9 MySQL dump + +SET NAMES utf8; +SET time_zone = '+00:00'; +SET foreign_key_checks = 0; +SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; + +SET NAMES utf8mb4; + +TRUNCATE `almacen`; +INSERT INTO `almacen` (`id`, `activo`, `ubigeo`, `departamento`, `provincia`, `latitud`, `longitud`, `esPrincipal`, `region_fk`) VALUES +(1, 1, '10201', 'Amazonas', 'Bagua', -5.63906152, -78.53166353, 0, 3), +(2, 1, '40101', 'Arequipa', 'Arequipa', -16.39881421, -71.537019649, 1, 1), +(3, 1, '50901', 'Ayacucho', 'Sucre', -14.01125749, -73.83884087, 0, 2), +(4, 1, '130101', 'La Libertad', 'Trujillo', -8.11176389, -79.02868652, 1, 1), +(5, 1, '150101', 'Lima', 'Lima', -12.04591952, -77.03049615, 1, 1), +(6, 1, '230101', 'Tacna', 'Tacna', -18.0137008, -70.2507964, 0, 1); + +TRUNCATE `averia`; + +TRUNCATE `camion`; +INSERT INTO `camion` (`id`, `activo`, `placa`, `kilometraje`, `estado`, `tipoCamion_fk`) VALUES +(1, 1, 'ETALLIC', 69420, 1, 1); + +TRUNCATE `cliente`; +INSERT INTO `cliente` (`id`, `activo`, `nombreCompleto`, `dni`, `correo`, `telefono`) VALUES +(1, 1, 'EmpresaAAA', '1234567890', 'contacto@empresaAAA.com', '123456789'), +(2, 1, 'EmpresaBBB', '1234567890', 'contacto@empresaBBB.com', '123456789'); + +TRUNCATE `pedido`; +INSERT INTO `pedido` (`id`, `activo`, `fechaPedido`, `cantidad`, `plazoEntrega`, `estado`, `codigoPedido`, `almacen_fk`, `cliente_fk`, `usuario_fk`, `PTG_fk`) VALUES +(2, 1, '2022-01-01 00:00:00', 10, 1, 0, '20220101_000001', 3, 1, 4, NULL); + +TRUNCATE `PTG`; +INSERT INTO `PTG` (`id`, `activo`, `fechaGenerado`, `esSimulacion`, `usuario_fk`) VALUES +(1, 1, '2022-06-01 05:10:47', 1, 1); + +TRUNCATE `region`; +INSERT INTO `region` (`id`, `activo`, `nombre`) VALUES +(1, 1, 'costa'), +(2, 1, 'sierra'), +(3, 1, 'selva'); + +TRUNCATE `rol`; +INSERT INTO `rol` (`id`, `activo`, `nombre`) VALUES +(1, 1, 'superuser'); + +TRUNCATE `ruta`; +INSERT INTO `ruta` (`id`, `activo`, `fechaInicio`, `fechaFin`, `PTG_fk`, `camion_fk`) VALUES +(1, 1, '2022-01-01 00:00:00', '2022-01-02 00:00:00', 1, 1); + +TRUNCATE `tipoAveria`; + +TRUNCATE `tipoCamion`; +INSERT INTO `tipoCamion` (`id`, `activo`, `nombre`, `capacidad`) VALUES +(1, 1, 'tipoA', 90), +(2, 1, 'tipoB', 45), +(3, 1, 'tipoC', 30); + +TRUNCATE `tramo`; +INSERT INTO `tramo` (`id`, `activo`, `distancia`, `estaBloqueado`, `almacen1_fk`, `almacen2_fk`) VALUES +(1, 1, 10, 0, 2, 5), +(2, 1, 10, 0, 3, 5), +(3, 1, 10, 0, 4, 5), +(4, 1, 10, 0, 6, 5), +(5, 1, 20, 0, 1, 4), +(6, 1, 20, 0, 4, 1), +(7, 1, 10, 0, 5, 2), +(8, 1, 10, 0, 5, 3), +(9, 1, 10, 0, 5, 4), +(10, 1, 10, 0, 5, 6); + +TRUNCATE `tramo_ruta`; +INSERT INTO `tramo_ruta` (`id`, `activo`, `orden`, `horaSalidaEst`, `horaLlegadaEst`, `horaSalidaReal`, `horaLlegadaReal`, `paquetesEntregar`, `ruta_fk`, `tramo_fk`) VALUES +(1, 1, 1, '2022-01-01 10:00:00', '2022-01-01 15:00:00', NULL, NULL, 0, 1, 9), +(2, 1, 2, '2022-01-01 16:00:00', '2022-01-01 21:00:00', NULL, NULL, 10, 1, 6); + +TRUNCATE `usuario`; +INSERT INTO `usuario` (`id`, `activo`, `usuario`, `clave`, `nombre`, `apellido`, `dni`, `correo`, `creador_fk`, `rol_fk`) VALUES +(1, 1, 'gabs', '*6AB7931FB2D104A478932D23AF4C7700D1A0C61A', 'Gabriela', 'Hernandez', '12345678', 'gabs@pucp.edu.pe', NULL, 1), +(2, 1, 'dayana', '*D279131EA232308E083B3EA192B78A0C15EA8701', 'Dayana', 'Alarcon', '12345678', 'dayana@pucp.edu.pe', 1, 1), +(3, 1, 'jannina', '*D279131EA232308E083B3EA192B84A0C15EA8701', 'Jannina', 'DeLosGodos', '12345678', 'jannina@pucp.edu.pe', 1, 1), +(4, 1, 'mitsuo', '*D279131EA232308E083B3EA192B74A0C15EA8701', 'Mitsuo', 'Tokumori', '12345678', 'mitsuo@pucp.edu.pe', 1, 1); + +-- 2022-06-01 06:13:09 diff --git a/back/sql/20220601dump.tables+views.sql b/back/sql/20220601dump.tables+views.sql new file mode 100644 index 0000000..7ff1c24 --- /dev/null +++ b/back/sql/20220601dump.tables+views.sql @@ -0,0 +1,245 @@ +-- Adminer 4.7.9 MySQL dump + +SET NAMES utf8; +SET time_zone = '+00:00'; +SET foreign_key_checks = 0; +SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; + +SET NAMES utf8mb4; + +DROP TABLE IF EXISTS `almacen`; +CREATE TABLE `almacen` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `activo` int(11) NOT NULL, + `ubigeo` varchar(10) NOT NULL, + `departamento` varchar(100) NOT NULL, + `provincia` varchar(100) NOT NULL, + `latitud` double NOT NULL, + `longitud` double NOT NULL, + `esPrincipal` int(11) NOT NULL, + `region_fk` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `region_fk` (`region_fk`), + CONSTRAINT `almacen_ibfk_1` FOREIGN KEY (`region_fk`) REFERENCES `region` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + + +DROP TABLE IF EXISTS `averia`; +CREATE TABLE `averia` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `activo` int(11) NOT NULL, + `descripcion` varchar(1000) NOT NULL, + `fechaRegistro` datetime NOT NULL, + `camion_fk` int(11) NOT NULL, + `tipoAveria_fk` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `camion_fk` (`camion_fk`), + KEY `tipoAveria_fk` (`tipoAveria_fk`), + CONSTRAINT `averia_ibfk_1` FOREIGN KEY (`camion_fk`) REFERENCES `camion` (`id`), + CONSTRAINT `averia_ibfk_2` FOREIGN KEY (`tipoAveria_fk`) REFERENCES `tipoAveria` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + + +DROP TABLE IF EXISTS `camion`; +CREATE TABLE `camion` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `activo` int(11) NOT NULL, + `placa` varchar(10) NOT NULL, + `kilometraje` double NOT NULL, + `estado` int(11) NOT NULL, + `tipoCamion_fk` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `tipoCamion_fk` (`tipoCamion_fk`), + CONSTRAINT `camion_ibfk_1` FOREIGN KEY (`tipoCamion_fk`) REFERENCES `tipoCamion` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + + +DROP TABLE IF EXISTS `cliente`; +CREATE TABLE `cliente` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `activo` int(11) NOT NULL, + `nombreCompleto` varchar(100) NOT NULL, + `dni` varchar(10) NOT NULL, + `correo` varchar(100) NOT NULL, + `telefono` varchar(20) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + + +DROP TABLE IF EXISTS `pedido`; +CREATE TABLE `pedido` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `activo` int(11) NOT NULL, + `fechaPedido` datetime NOT NULL, + `cantidad` int(11) NOT NULL, + `plazoEntrega` int(11) NOT NULL, + `estado` int(11) NOT NULL, + `codigoPedido` varchar(100) DEFAULT NULL, + `almacen_fk` int(11) NOT NULL, + `cliente_fk` int(11) NOT NULL, + `usuario_fk` int(11) NOT NULL, + `PTG_fk` int(11) DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `usuario_fk` (`usuario_fk`), + KEY `PTG_fk` (`PTG_fk`), + KEY `cliente_fk` (`cliente_fk`), + KEY `almacen_fk` (`almacen_fk`), + CONSTRAINT `pedido_ibfk_1` FOREIGN KEY (`usuario_fk`) REFERENCES `usuario` (`id`), + CONSTRAINT `pedido_ibfk_2` FOREIGN KEY (`PTG_fk`) REFERENCES `PTG` (`id`), + CONSTRAINT `pedido_ibfk_3` FOREIGN KEY (`cliente_fk`) REFERENCES `cliente` (`id`), + CONSTRAINT `pedido_ibfk_4` FOREIGN KEY (`almacen_fk`) REFERENCES `almacen` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + + +DROP TABLE IF EXISTS `PTG`; +CREATE TABLE `PTG` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `activo` int(11) NOT NULL, + `fechaGenerado` datetime NOT NULL, + `esSimulacion` int(11) NOT NULL, + `usuario_fk` int(11) DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `usuario_fk` (`usuario_fk`), + CONSTRAINT `PTG_ibfk_1` FOREIGN KEY (`usuario_fk`) REFERENCES `usuario` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + + +DROP TABLE IF EXISTS `region`; +CREATE TABLE `region` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `activo` int(11) NOT NULL, + `nombre` varchar(100) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + + +DROP TABLE IF EXISTS `rol`; +CREATE TABLE `rol` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `activo` int(11) NOT NULL, + `nombre` varchar(100) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + + +DROP TABLE IF EXISTS `ruta`; +CREATE TABLE `ruta` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `activo` int(11) NOT NULL, + `fechaInicio` datetime NOT NULL, + `fechaFin` datetime NOT NULL, + `PTG_fk` int(11) NOT NULL, + `camion_fk` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `PTG_fk` (`PTG_fk`), + KEY `camion_fk` (`camion_fk`), + CONSTRAINT `ruta_ibfk_1` FOREIGN KEY (`PTG_fk`) REFERENCES `PTG` (`id`), + CONSTRAINT `ruta_ibfk_2` FOREIGN KEY (`camion_fk`) REFERENCES `camion` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + + +DROP TABLE IF EXISTS `tipoAveria`; +CREATE TABLE `tipoAveria` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `activo` int(11) NOT NULL, + `nombre` int(11) NOT NULL, + `duracion` int(11) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + + +DROP TABLE IF EXISTS `tipoCamion`; +CREATE TABLE `tipoCamion` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `activo` int(11) NOT NULL, + `nombre` varchar(100) NOT NULL, + `capacidad` int(11) NOT NULL, + PRIMARY KEY (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + + +DROP TABLE IF EXISTS `tramo`; +CREATE TABLE `tramo` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `activo` int(11) NOT NULL, + `distancia` double NOT NULL, + `estaBloqueado` int(11) NOT NULL, + `almacen1_fk` int(11) NOT NULL, + `almacen2_fk` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `almacen1_fk` (`almacen1_fk`), + KEY `almacen2_fk` (`almacen2_fk`), + CONSTRAINT `tramo_ibfk_1` FOREIGN KEY (`almacen1_fk`) REFERENCES `almacen` (`id`), + CONSTRAINT `tramo_ibfk_2` FOREIGN KEY (`almacen2_fk`) REFERENCES `almacen` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + + +DROP TABLE IF EXISTS `tramo_ruta`; +CREATE TABLE `tramo_ruta` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `activo` int(11) NOT NULL, + `orden` int(11) NOT NULL, + `horaSalidaEst` datetime NOT NULL, + `horaLlegadaEst` datetime NOT NULL, + `horaSalidaReal` datetime DEFAULT NULL, + `horaLlegadaReal` datetime DEFAULT NULL, + `paquetesEntregar` int(11) NOT NULL, + `ruta_fk` int(11) NOT NULL, + `tramo_fk` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `ruta_fk` (`ruta_fk`), + KEY `tramo_fk` (`tramo_fk`), + CONSTRAINT `tramo_ruta_ibfk_1` FOREIGN KEY (`ruta_fk`) REFERENCES `ruta` (`id`), + CONSTRAINT `tramo_ruta_ibfk_2` FOREIGN KEY (`tramo_fk`) REFERENCES `tramo` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + + +DROP TABLE IF EXISTS `usuario`; +CREATE TABLE `usuario` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `activo` int(11) NOT NULL, + `usuario` varchar(100) NOT NULL, + `clave` varchar(100) NOT NULL, + `nombre` varchar(100) NOT NULL, + `apellido` varchar(100) NOT NULL, + `dni` varchar(10) NOT NULL, + `correo` varchar(100) NOT NULL, + `creador_fk` int(11) DEFAULT NULL, + `rol_fk` int(11) NOT NULL, + PRIMARY KEY (`id`), + KEY `creador_fk` (`creador_fk`), + KEY `rol_fk` (`rol_fk`), + CONSTRAINT `usuario_ibfk_2` FOREIGN KEY (`creador_fk`) REFERENCES `usuario` (`id`), + CONSTRAINT `usuario_ibfk_3` FOREIGN KEY (`rol_fk`) REFERENCES `usuario` (`id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + + +DROP VIEW IF EXISTS `v_almacen`; +CREATE TABLE `v_almacen` (`ubigeo` varchar(10), `departamento` varchar(100), `provincia` varchar(100), `latitud` double, `longitud` double, `esPrincipal` int(11), `region` varchar(100)); + + +DROP VIEW IF EXISTS `v_camion`; +CREATE TABLE `v_camion` (`id` int(11), `placa` varchar(10), `kilometraje` double, `estado` int(11), `tipo` varchar(100), `capacidad` int(11)); + + +DROP VIEW IF EXISTS `v_pedido`; +CREATE TABLE `v_pedido` (`cliente` varchar(100), `RUC` varchar(10), `ubigeo` varchar(10), `provincia` varchar(100), `cantidad` int(11), `fechaPedido` datetime, `plazoEntrega` int(11), `estado` int(11), `URL_pedido` varchar(100), `registrado por` varchar(100)); + + +DROP VIEW IF EXISTS `v_usuario`; +CREATE TABLE `v_usuario` (`usuario` varchar(100), `nombre` varchar(100), `apellido` varchar(100), `dni` varchar(10), `correo` varchar(100), `registered by` varchar(100), `rol` varchar(100)); + + +DROP TABLE IF EXISTS `v_almacen`; +CREATE ALGORITHM=UNDEFINED DEFINER=`gabs`@`%.%.%.%` SQL SECURITY DEFINER VIEW `v_almacen` AS select `a`.`ubigeo` AS `ubigeo`,`a`.`departamento` AS `departamento`,`a`.`provincia` AS `provincia`,`a`.`latitud` AS `latitud`,`a`.`longitud` AS `longitud`,`a`.`esPrincipal` AS `esPrincipal`,`r`.`nombre` AS `region` from (`almacen` `a` join `region` `r` on(`a`.`region_fk` = `r`.`id`)) where a.activo = 1; + +DROP TABLE IF EXISTS `v_camion`; +CREATE ALGORITHM=UNDEFINED DEFINER=`gabs`@`%.%.%.%` SQL SECURITY DEFINER VIEW `v_camion` AS select `c`.`id` AS `id`,`c`.`placa` AS `placa`,`c`.`kilometraje` AS `kilometraje`,`c`.`estado` AS `estado`,`tc`.`nombre` AS `tipo`,`tc`.`capacidad` AS `capacidad` from (`camion` `c` join `tipoCamion` `tc` on(`c`.`tipoCamion_fk` = `tc`.`id`)) where c.activo = 1; + +DROP TABLE IF EXISTS `v_pedido`; +CREATE ALGORITHM=UNDEFINED DEFINER=`gabs`@`%.%.%.%` SQL SECURITY DEFINER VIEW `v_pedido` AS select `cli`.`nombreCompleto` AS `cliente`,`cli`.`dni` AS `RUC`,`alm`.`ubigeo` AS `ubigeo`,`alm`.`provincia` AS `provincia`,`ped`.`cantidad` AS `cantidad`,`ped`.`fechaPedido` AS `fechaPedido`,`ped`.`plazoEntrega` AS `plazoEntrega`,`ped`.`estado` AS `estado`,`ped`.`codigoPedido` AS `URL_pedido`,`usu`.`usuario` AS `registrado por` from (((`pedido` `ped` join `cliente` `cli` on(`ped`.`cliente_fk` = `cli`.`id`)) join `usuario` `usu` on(`ped`.`usuario_fk` = `usu`.`id`)) join `almacen` `alm` on(`ped`.`almacen_fk` = `alm`.`id`)) where ped.activo = 1; + +DROP TABLE IF EXISTS `v_usuario`; +CREATE ALGORITHM=UNDEFINED DEFINER=`gabs`@`%.%.%.%` SQL SECURITY DEFINER VIEW `v_usuario` AS select `u`.`usuario` AS `usuario`,`u`.`nombre` AS `nombre`,`u`.`apellido` AS `apellido`,`u`.`dni` AS `dni`,`u`.`correo` AS `correo`,`u2`.`nombre` AS `registered by`,`r`.`nombre` AS `rol` from ((`usuario` `u` left join `usuario` `u2` on(`u`.`creador_fk` = `u2`.`id`)) join `rol` `r` on(`u`.`rol_fk` = `r`.`id`)) where u.activo = 1; + +-- 2022-06-01 06:30:20 -- cgit v1.2.3