· 6 years ago · Jan 27, 2020, 05:52 PM
1CREATE DATABASE `chronos`;
2
3USE chronos;
4CREATE TABLE IF NOT EXISTS `chronos`.`departamentos` (
5 `departamento_id` INT NOT NULL AUTO_INCREMENT,
6 `nombre` VARCHAR(150) NOT NULL,
7 PRIMARY KEY (`departamento_id`))
8ENGINE = InnoDB;
9
10CREATE TABLE IF NOT EXISTS `chronos`.`paises` (
11 `pais_id` INT NOT NULL,
12 `nombre` VARCHAR(150) NOT NULL,
13 PRIMARY KEY (`pais_id`))
14ENGINE = InnoDB;
15
16CREATE TABLE IF NOT EXISTS `chronos`.`empresas` (
17 `cif` VARCHAR(9) NOT NULL,
18 `nombre` VARCHAR(150) NULL DEFAULT NULL,
19 `direccion` VARCHAR(150) NULL DEFAULT NULL,
20 `codigo_postal` VARCHAR(5) NULL DEFAULT NULL,
21 `poblacion` VARCHAR(150) NULL DEFAULT NULL,
22 `provincia` VARCHAR(100) NULL DEFAULT NULL,
23 `telefono` VARCHAR(20) NULL DEFAULT NULL,
24 `email` VARCHAR(100) NULL DEFAULT NULL,
25 `web` VARCHAR(100) NULL DEFAULT NULL,
26 `logo_url` VARCHAR(100) NULL DEFAULT NULL,
27 `pais_id` INT NULL DEFAULT NULL,
28 PRIMARY KEY (`cif`),
29 INDEX `fk_pais_empresa` (`pais_id` ASC),
30 CONSTRAINT `fk_pais_empresa`
31 FOREIGN KEY (`pais_id`)
32 REFERENCES `chronos`.`paises` (`pais_id`)
33 ON DELETE NO ACTION
34 ON UPDATE NO ACTION)
35ENGINE = InnoDB;
36
37CREATE TABLE IF NOT EXISTS `chronos`.`puesto_trabajo` (
38 `puesto_trabajo_id` INT NOT NULL AUTO_INCREMENT,
39 `nombre` VARCHAR(50) NOT NULL,
40 `empresa_id` VARCHAR(9) NOT NULL,
41 PRIMARY KEY (`puesto_trabajo_id`),
42 INDEX `fk_puesto_empresa_idx` (`empresa_id` ASC),
43 CONSTRAINT `fk_puesto_empresa`
44 FOREIGN KEY (`empresa_id`)
45 REFERENCES `chronos`.`empresas` (`cif`)
46 ON DELETE NO ACTION
47 ON UPDATE NO ACTION)
48ENGINE = InnoDB;
49
50CREATE TABLE IF NOT EXISTS `chronos`.`centros_trabajo` (
51 `centro_trabajo_id` INT NOT NULL AUTO_INCREMENT,
52 `nombre` VARCHAR(50) NOT NULL,
53 `empresa_id` VARCHAR(9) NULL,
54 PRIMARY KEY (`centro_trabajo_id`),
55 INDEX `fk_centro_trabajo_empresa_idx` (`empresa_id` ASC),
56 CONSTRAINT `fk_centro_trabajo_empresa`
57 FOREIGN KEY (`empresa_id`)
58 REFERENCES `chronos`.`empresas` (`cif`)
59 ON DELETE NO ACTION
60 ON UPDATE NO ACTION)
61ENGINE = InnoDB;
62
63CREATE TABLE IF NOT EXISTS `chronos`.`horarios` (
64 `horario_id` INT NOT NULL AUTO_INCREMENT,
65 `empresa_id` VARCHAR(9) NULL DEFAULT NULL,
66 `nombre` VARCHAR(100) NULL DEFAULT NULL,
67 `lunes_entrada` TIME NULL DEFAULT NULL,
68 `lunes_salida` TIME NULL DEFAULT NULL,
69 `martes_entrada` TIME NULL DEFAULT NULL,
70 `martes_salida` TIME NULL DEFAULT NULL,
71 `miercoles_entrada` TIME NULL DEFAULT NULL,
72 `miercoles_salida` TIME NULL DEFAULT NULL,
73 `jueves_entrada` TIME NULL DEFAULT NULL,
74 `jueves_salida` TIME NULL DEFAULT NULL,
75 `viernes_entrada` TIME NULL DEFAULT NULL,
76 `viernes_salida` TIME NULL DEFAULT NULL,
77 `sabado_entrada` TIME NULL DEFAULT NULL,
78 `sabado_salida` TIME NULL DEFAULT NULL,
79 `domingo_entrada` TIME NULL DEFAULT NULL,
80 `domingo_salida` TIME NULL DEFAULT NULL,
81 `descanso_entrada` TIME NULL DEFAULT NULL,
82 `descanso_salida` TIME NULL DEFAULT NULL,
83 PRIMARY KEY (`horario_id`),
84 INDEX `fk_horarios_detalle_empresas` (`empresa_id` ASC),
85 CONSTRAINT `fk_horarios_detalle_empresas`
86 FOREIGN KEY (`empresa_id`)
87 REFERENCES `chronos`.`empresas` (`cif`)
88 ON DELETE NO ACTION
89 ON UPDATE NO ACTION)
90ENGINE = InnoDB;
91
92CREATE TABLE IF NOT EXISTS `chronos`.`usuarios` (
93 `dni` VARCHAR(9) NOT NULL,
94 `password` LONGTEXT NULL DEFAULT NULL,
95 `nombre` VARCHAR(50) NULL DEFAULT NULL,
96 `apellidos` VARCHAR(100) NULL DEFAULT NULL,
97 `direccion` VARCHAR(150) NULL DEFAULT NULL,
98 `fecha_nacimiento` DATE NULL DEFAULT NULL,
99 `codigo_postal` VARCHAR(5) NULL DEFAULT NULL,
100 `poblacion` VARCHAR(100) NULL DEFAULT NULL,
101 `provincia` VARCHAR(100) NULL DEFAULT NULL,
102 `pais_id` INT NULL DEFAULT NULL,
103 `nacionalidad` VARCHAR(100) NULL DEFAULT NULL,
104 `telefono1` VARCHAR(20) NULL DEFAULT NULL,
105 `telefono2` VARCHAR(20) NULL DEFAULT NULL,
106 `email` VARCHAR(100) NULL DEFAULT NULL,
107 `num_seg_social` VARCHAR(15) NULL DEFAULT NULL,
108 `fecha_alta` DATE NULL DEFAULT NULL,
109 `empresa_id` VARCHAR(50) NULL DEFAULT NULL,
110 `departamento_id` INT NULL DEFAULT NULL,
111 `puesto_trabajo_id` INT NULL DEFAULT NULL,
112 `centro_trabajo_id` INT NULL DEFAULT NULL,
113 `rol` VARCHAR(50) NULL DEFAULT NULL,
114 `horario_id` INT NULL DEFAULT NULL,
115 `dias_vacaciones` INT NULL DEFAULT NULL,
116 `url_foto` VARCHAR(100) NULL DEFAULT NULL,
117 `activo` TINYINT NULL DEFAULT NULL,
118 PRIMARY KEY (`dni`),
119 INDEX `unq_trabajadores_empresa_id` (`empresa_id` ASC),
120 INDEX `fk_usuarios_departamentos` (`departamento_id` ASC),
121 INDEX `fk_puesto_trabajo` (`puesto_trabajo_id` ASC),
122 INDEX `fk_horario` (`horario_id` ASC),
123 INDEX `fk_usuarios_paises` (`pais_id` ASC),
124 INDEX `fk_usuarios_centro_trabajo_idx` (`centro_trabajo_id` ASC),
125 CONSTRAINT `fk_trabajadores_empresas`
126 FOREIGN KEY (`empresa_id`)
127 REFERENCES `chronos`.`empresas` (`cif`)
128 ON DELETE NO ACTION
129 ON UPDATE NO ACTION,
130 CONSTRAINT `fk_usuarios_departamentos`
131 FOREIGN KEY (`departamento_id`)
132 REFERENCES `chronos`.`departamentos` (`departamento_id`)
133 ON DELETE NO ACTION
134 ON UPDATE NO ACTION,
135 CONSTRAINT `fk_puesto_trabajo`
136 FOREIGN KEY (`puesto_trabajo_id`)
137 REFERENCES `chronos`.`puesto_trabajo` (`puesto_trabajo_id`)
138 ON DELETE NO ACTION
139 ON UPDATE NO ACTION,
140 CONSTRAINT `fk_horario`
141 FOREIGN KEY (`horario_id`)
142 REFERENCES `chronos`.`horarios` (`horario_id`)
143 ON DELETE NO ACTION
144 ON UPDATE NO ACTION,
145 CONSTRAINT `fk_usuarios_paises`
146 FOREIGN KEY (`pais_id`)
147 REFERENCES `chronos`.`paises` (`pais_id`)
148 ON DELETE NO ACTION
149 ON UPDATE NO ACTION,
150 CONSTRAINT `fk_usuarios_centro_trabajo`
151 FOREIGN KEY (`centro_trabajo_id`)
152 REFERENCES `chronos`.`centros_trabajo` (`centro_trabajo_id`)
153 ON DELETE NO ACTION
154 ON UPDATE NO ACTION)
155ENGINE = InnoDB;
156
157CREATE TABLE IF NOT EXISTS `chronos`.`registro_horas` (
158 `registro_horas_id` INT NOT NULL AUTO_INCREMENT,
159 `empresa_id` VARCHAR(9) NULL DEFAULT NULL,
160 `usuario_id` VARCHAR(9) NULL DEFAULT NULL,
161 `fecha` DATE NULL DEFAULT NULL,
162 `entrada` DATETIME NULL DEFAULT NULL,
163 `salida` DATETIME NULL DEFAULT NULL,
164 PRIMARY KEY (`registro_horas_id`),
165 INDEX `fk_registro_horas_usuarios` (`usuario_id` ASC),
166 INDEX `fk_empresa_registro_horas` (`empresa_id` ASC),
167 CONSTRAINT `fk_registro_horas_usuarios`
168 FOREIGN KEY (`usuario_id`)
169 REFERENCES `chronos`.`usuarios` (`dni`)
170 ON DELETE NO ACTION
171 ON UPDATE NO ACTION,
172 CONSTRAINT `fk_empresa_registro_horas`
173 FOREIGN KEY (`empresa_id`)
174 REFERENCES `chronos`.`usuarios` (`empresa_id`)
175 ON DELETE NO ACTION
176 ON UPDATE NO ACTION)
177ENGINE = InnoDB;
178
179CREATE TABLE IF NOT EXISTS `chronos`.`vacaciones` (
180 `vacaciones_id` INT NOT NULL AUTO_INCREMENT,
181 `usuario_id` VARCHAR(9) NULL DEFAULT NULL,
182 `empresa_id` VARCHAR(9) NULL DEFAULT NULL,
183 `desde` DATE NULL DEFAULT NULL,
184 `hasta` DATE NULL DEFAULT NULL,
185 `estado` VARCHAR(20) NULL DEFAULT NULL,
186 PRIMARY KEY (`vacaciones_id`),
187 INDEX `fk_vacaciones_empresa` (`empresa_id` ASC),
188 INDEX `fk_vacaciones_usuarios` (`usuario_id` ASC),
189 CONSTRAINT `fk_vacaciones_empresa`
190 FOREIGN KEY (`empresa_id`)
191 REFERENCES `chronos`.`usuarios` (`empresa_id`)
192 ON DELETE NO ACTION
193 ON UPDATE NO ACTION,
194 CONSTRAINT `fk_vacaciones_usuarios`
195 FOREIGN KEY (`usuario_id`)
196 REFERENCES `chronos`.`usuarios` (`dni`)
197 ON DELETE NO ACTION
198 ON UPDATE NO ACTION)
199ENGINE = InnoDB;
200
201USE chronos;
202CREATE OR REPLACE VIEW `registro_horas_sumadas` AS
203SELECT
204 `chronos`.`registro_horas`.`registro_horas_id` AS `registro_horas_id`,
205 `chronos`.`registro_horas`.`empresa_id` AS `empresa_id`,
206 `chronos`.`registro_horas`.`usuario_id` AS `usuario_id`,
207 `chronos`.`registro_horas`.`fecha` AS `fecha`,
208 `chronos`.`registro_horas`.`entrada` AS `entrada`,
209 `chronos`.`registro_horas`.`salida` AS `salida`,
210 SEC_TO_TIME(TIMESTAMPDIFF(SECOND,
211 `chronos`.`registro_horas`.`entrada`,
212 `chronos`.`registro_horas`.`salida`)) AS `horas_trabajadas`
213 FROM
214 `chronos`.`registro_horas`;
215
216USE chronos;
217CREATE OR REPLACE VIEW `registro_horas_mensual` AS
218SELECT
219 `chronos`.`registro_horas_sumadas`.`usuario_id` AS `usuario_id`,
220 MONTH(`chronos`.`registro_horas_sumadas`.`fecha`) AS `mes`,
221 YEAR(`chronos`.`registro_horas_sumadas`.`fecha`) AS `anno`,
222 SEC_TO_TIME(SUM(TIME_TO_SEC(`chronos`.`registro_horas_sumadas`.`horas_trabajadas`))) AS `horas_mensuales`
223 FROM
224 `chronos`.`registro_horas_sumadas`
225 GROUP BY `usuario_id` , `mes` , `anno`;
226
227ALTER TABLE chronos.departamentos ADD empresa_id VARCHAR(9);
228ALTER TABLE chronos.departamentos ADD CONSTRAINT fk_empresa_departamento FOREIGN KEY (empresa_id) REFERENCES chronos.empresas(cif);