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/20220601dump.tables+views.sql | 245 +++++++++++++++++++++++++++++++++ 1 file changed, 245 insertions(+) create mode 100644 back/sql/20220601dump.tables+views.sql (limited to 'back/sql/20220601dump.tables+views.sql') 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