Small changes to models. Add sql view and dump

1 files changed, 290 insertions, 0 deletions

diff --git a/back/sql/tables+sampledata_mini-noactivefieldyet.dump.sql b/back/sql/tables+sampledata_mini-noactivefieldyet.dump.sql
new file mode 100644
index 0000000..11c9a2d
--- /dev/null
+++ b/back/sql/tables+sampledata_mini-noactivefieldyet.dump.sql
@@ -0,0 +1,290 @@
+-- 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,
+  `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;
+
+TRUNCATE `almacen`;
+INSERT INTO `almacen` (`id`, `ubigeo`, `departamento`, `provincia`, `latitud`, `longitud`, `esPrincipal`, `region_fk`) VALUES
+(1,	'10201',	'Amazonas',	'Bagua',	-5.63906152,	-78.53166353,	0,	3),
+(2,	'40101',	'Arequipa',	'Arequipa',	-16.39881421,	-71.537019649,	1,	1),
+(3,	'50901',	'Ayacucho',	'Sucre',	-14.01125749,	-73.83884087,	0,	2),
+(4,	'130101',	'La Libertad',	'Trujillo',	-8.11176389,	-79.02868652,	1,	1),
+(5,	'150101',	'Lima',	'Lima',	-12.04591952,	-77.03049615,	1,	1),
+(6,	'230101',	'Tacna',	'Tacna',	-18.0137008,	-70.2507964,	0,	1);
+
+DROP TABLE IF EXISTS `averia`;
+CREATE TABLE `averia` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `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;
+
+TRUNCATE `averia`;
+
+DROP TABLE IF EXISTS `camion`;
+CREATE TABLE `camion` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `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;
+
+TRUNCATE `camion`;
+INSERT INTO `camion` (`id`, `placa`, `kilometraje`, `estado`, `tipoCamion_fk`) VALUES
+(1,	'ETALLIC',	69420,	1,	1);
+
+DROP TABLE IF EXISTS `cliente`;
+CREATE TABLE `cliente` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `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;
+
+TRUNCATE `cliente`;
+INSERT INTO `cliente` (`id`, `nombreCompleto`, `dni`, `correo`, `telefono`) VALUES
+(1,	'EmpresaAAA',	'1234567890',	'[email protected]',	'123456789'),
+(2,	'EmpresaBBB',	'1234567890',	'[email protected]',	'123456789');
+
+DROP TABLE IF EXISTS `pedido`;
+CREATE TABLE `pedido` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `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;
+
+TRUNCATE `pedido`;
+INSERT INTO `pedido` (`id`, `fechaPedido`, `cantidad`, `plazoEntrega`, `estado`, `codigoPedido`, `almacen_fk`, `cliente_fk`, `usuario_fk`, `PTG_fk`) VALUES
+(2,	'2022-01-01 00:00:00',	10,	1,	0,	'20220101_000001',	3,	1,	4,	NULL);
+
+DROP TABLE IF EXISTS `PTG`;
+CREATE TABLE `PTG` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `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;
+
+TRUNCATE `PTG`;
+INSERT INTO `PTG` (`id`, `fechaGenerado`, `esSimulacion`, `usuario_fk`) VALUES
+(1,	'2022-06-01 05:10:47',	1,	1);
+
+DROP TABLE IF EXISTS `region`;
+CREATE TABLE `region` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `nombre` varchar(100) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+TRUNCATE `region`;
+INSERT INTO `region` (`id`, `nombre`) VALUES
+(1,	'costa'),
+(2,	'sierra'),
+(3,	'selva');
+
+DROP TABLE IF EXISTS `rol`;
+CREATE TABLE `rol` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `nombre` varchar(100) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+TRUNCATE `rol`;
+INSERT INTO `rol` (`id`, `nombre`) VALUES
+(1,	'superuser');
+
+DROP TABLE IF EXISTS `ruta`;
+CREATE TABLE `ruta` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `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;
+
+TRUNCATE `ruta`;
+INSERT INTO `ruta` (`id`, `fechaInicio`, `fechaFin`, `PTG_fk`, `camion_fk`) VALUES
+(1,	'2022-01-01 00:00:00',	'2022-01-02 00:00:00',	1,	1);
+
+DROP TABLE IF EXISTS `tipoAveria`;
+CREATE TABLE `tipoAveria` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `nombre` int(11) NOT NULL,
+  `duracion` int(11) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+TRUNCATE `tipoAveria`;
+
+DROP TABLE IF EXISTS `tipoCamion`;
+CREATE TABLE `tipoCamion` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `nombre` varchar(100) NOT NULL,
+  `capacidad` int(11) NOT NULL,
+  PRIMARY KEY (`id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
+
+TRUNCATE `tipoCamion`;
+INSERT INTO `tipoCamion` (`id`, `nombre`, `capacidad`) VALUES
+(1,	'tipoA',	90),
+(2,	'tipoB',	45),
+(3,	'tipoC',	30);
+
+DROP TABLE IF EXISTS `tramo`;
+CREATE TABLE `tramo` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `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;
+
+TRUNCATE `tramo`;
+INSERT INTO `tramo` (`id`, `distancia`, `estaBloqueado`, `almacen1_fk`, `almacen2_fk`) VALUES
+(1,	10,	0,	2,	5),
+(2,	10,	0,	3,	5),
+(3,	10,	0,	4,	5),
+(4,	10,	0,	6,	5),
+(5,	20,	0,	1,	4),
+(6,	20,	0,	4,	1),
+(7,	10,	0,	5,	2),
+(8,	10,	0,	5,	3),
+(9,	10,	0,	5,	4),
+(10,	10,	0,	5,	6);
+
+DROP TABLE IF EXISTS `tramo_ruta`;
+CREATE TABLE `tramo_ruta` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `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;
+
+TRUNCATE `tramo_ruta`;
+INSERT INTO `tramo_ruta` (`id`, `orden`, `horaSalidaEst`, `horaLlegadaEst`, `horaSalidaReal`, `horaLlegadaReal`, `paquetesEntregar`, `ruta_fk`, `tramo_fk`) VALUES
+(1,	1,	'2022-01-01 10:00:00',	'2022-01-01 15:00:00',	NULL,	NULL,	0,	1,	9),
+(2,	2,	'2022-01-01 16:00:00',	'2022-01-01 21:00:00',	NULL,	NULL,	10,	1,	6);
+
+DROP TABLE IF EXISTS `usuario`;
+CREATE TABLE `usuario` (
+  `id` int(11) NOT NULL AUTO_INCREMENT,
+  `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;
+
+TRUNCATE `usuario`;
+INSERT INTO `usuario` (`id`, `usuario`, `clave`, `nombre`, `apellido`, `dni`, `correo`, `creador_fk`, `rol_fk`) VALUES
+(1,	'gabs',	'*6AB7931FB2D104A478932D23AF4C7700D1A0C61A',	'Gabriela',	'Hernandez',	'12345678',	'[email protected]',	NULL,	1),
+(4,	'mitsuo',	'*D279131EA232308E083B3EA192B74A0C15EA8701',	'Mitsuo',	'Tokumori',	'12345678',	'[email protected]',	1,	1);
+
+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`));
+
+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`));
+
+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`));
+
+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`));
+
+-- 2022-06-01 05:35:28