· 8 years ago · Aug 19, 2018, 06:12 PM
1-- Database: "SistemaProduccion"
2
3-- DROP DATABASE "SistemaProduccion";
4drop database if exists SistemaProduccion;
5
6drop table if exists Productos;
7drop table if exists Insumos;
8drop table if exists Areas;
9drop table if exists Empleados;
10drop table if exists ValoresIniciales;
11drop table if exists InicioSesion;
12drop table if exists CompraXInsumo;
13drop table if exists Nomina;
14drop table if exists NominaXEmpleado;
15drop table if exists VentaXProducto;
16
17CREATE DATABASE "SistemaProduccion"
18 WITH OWNER = postgres
19 ENCODING = 'UTF8'
20 TABLESPACE = pg_default
21 LC_COLLATE = 'es_CR.UTF-8'
22 LC_CTYPE = 'es_CR.UTF-8'
23 CONNECTION LIMIT = -1;
24
25create table Insumos (
26 Id serial not null,
27 Nombre varchar(25) not null,
28 CONSTRAINT PK_INSUMOS PRIMARY KEY (Id),
29 unique(Nombre)
30);
31
32create table Productos(
33 Id serial not null,
34 Nombre varchar(25) not null,
35 Precio int not null,
36 ImpuestoAplicado float not null,
37 CONSTRAINT PK_PRODUCTOS PRIMARY KEY (Id),
38 UNIQUE (Nombre)
39);
40
41create table Areas(
42 Id serial not null,
43 Nombre varchar(25) not null,
44 Dimension float not null,
45 ProductoProducido int not null REFERENCES Productos(Id),
46 Estado boolean not null,
47 CONSTRAINT PK_AREAS PRIMARY KEY (Id),
48 UNIQUE(Nombre)
49);
50
51create table Empleados(
52 Cedula varchar(25) not null,
53 Nombre varchar(25) not null,
54 Apellidos varchar(30) not null,
55 Labor varchar(25) not null,
56 SalarioMensual int not null,
57 SalarioCarga int not null,
58 Estado boolean not null,
59 CONSTRAINT PK_EMPLEADOS PRIMARY KEY (Cedula)
60);
61
62create table ValoresIniciales(
63 Nombre varchar(25) not null,
64 Telefono varchar(10) not null,
65 CedulaJuridica varchar(15) not null,
66 NumeroSecuencial int not null
67);
68
69create table InicioSesion(
70 Usuario varchar(25) not null,
71 Contrasenia varchar(25) not null
72);
73
74
75-- MODIFIQUE NOMINA
76create table Nomina(
77 Id serial not null,
78 Mes int not null,
79 Anio int not null,
80 CONSTRAINT PK_NOMINA PRIMARY KEY(Id)
81);
82create table NominaXEmpleado(
83 IdNomina int not null references Nomina(Id),
84 CedulaEmpleado varchar(25) not null references Empleados(Cedula),
85 NombreEmpleado varchar(25) not null,
86 LaborEmpleado varchar(25) not null,
87 SalarioMensual int not null,
88 SalarioCarga float not null
89);
90
91create table Venta(
92 Id serial not null,
93 NombreCliente varchar(25) not null,
94 Fecha date not null,
95 LocalComercial varchar(25) not null,
96 CedJuridica varchar(15) not null,
97 Telefono varchar(10) not null,
98 CONSTRAINT PK_PRODUCTO PRIMARY KEY(Id)
99);
100create table VentaXProducto(
101 IdVenta int not null REFERENCES Venta(Id),
102 IdProducto int not null REFERENCES Productos(Id),
103 Cantidad int not null,
104 Area varchar(25) not null,
105 PrecioUnitario int not null,
106 ImpuestoUnitario float not null
107);
108
109
110create table Compra(
111 Id serial not null,
112 NumFactura varchar(25) not null,
113 NombreProveedor varchar(25) not null,
114 Fecha date not null,
115 CONSTRAINT PK_COMPRA PRIMARY KEY (Id)
116);
117
118
119create table CompraXInsumo(
120 IdInsumo int not null REFERENCES Insumos(Id),
121 IdFactura int not null REFERENCES Compra(Id),
122 Cantidad int not null,
123 PrecioUnitario int not null,
124 ImpuestoUnitario float not null
125
126);
127
128-- Funcion de top 5 Insumos
129create or replace function TopInsumos(date,date)
130RETURNS TABLE(Nick character varying) AS $$
131BEGIN
132RETURN QUERY
133 select Nombre from Insumos,(SELECT CI.IdInsumo, SUM(CI.Cantidad) as SUMA
134 FROM CompraXInsumo as CI INNER JOIN Compra as C ON (CI.IdFactura = C.Id) where C.Fecha BETWEEN $1
135 AND $2 group by CI.IdInsumo) as SUB where Insumos.Id = SUB.IdInsumo order by SUB.SUMA desc fetch first 5 rows only;
136END;
137$$ LANGUAGE plpgsql;
138
139select TopInsumos('13/1/2015','16/1/2020');
140
141-- Funcion de top 5 productos
142create or replace function TopProductos(date,date)
143RETURNS TABLE(Nick character varying) AS $$
144BEGIN
145RETURN QUERY
146 select Nombre from Productos,(SELECT VP.IdProducto, SUM(VP.Cantidad) as SUMA
147 FROM VentaXProducto as VP INNER JOIN Venta as V ON (VP.IdVenta = V.Id) where V.Fecha BETWEEN $1
148 AND $2 group by VP.IdProducto) as SUB where Productos.Id = SUB.IdProducto order by SUB.SUMA desc fetch first 5 rows only;
149END;
150$$ LANGUAGE plpgsql;
151
152select TopProductos('13/1/2015','16/8/2015');
153
154
155
156--CONSULTA DE NOMINAS
157
158-- Select de nomina con el subtotal y total
159create view VistaNomina as
160 SELECT N.Id,N.Mes,N.Anio,SUM(NE.SalarioMensual) as SubTotal, SUM(NE.SalarioMensual + NE.SalarioCarga)
161 FROM Nomina as N INNER JOIN NominaXEmpleado as NE ON (N.Id = NE.IdNomina) group by N.Id;
162
163select * from VistaNomina;
164--Funcion para ver los detalles de una nomina
165create or replace function DetalleNomina(int)
166RETURNS TABLE(Ced character varying,Nick character varying,Labor character varying, SalarioM int, SalarioC float) AS $$
167BEGIN
168RETURN QUERY
169 select CedulaEmpleado,NombreEmpleado,LaborEmpleado,SalarioMensual,SalarioCarga from NominaXEmpleado where IdNomina = $1;
170END;
171$$ LANGUAGE plpgsql;
172
173select * from DetalleNomina(2);
174
175
176--CONSULTA DE COMPRAS
177-- Select de Compras con el id, fecha, subtotal y total
178create view VistaCompras as
179 SELECT C.Id,C.Fecha,SUM(CI.PrecioUnitario*CI.Cantidad) as SubTotal, SUM(((CI.ImpuestoUnitario*CI.PrecioUnitario)+CI.PrecioUnitario)*CI.Cantidad) as Total
180 FROM Compra as C INNER JOIN CompraXInsumo as CI ON (C.Id = CI.IdFactura) group by C.Id;
181
182select * from VistaCompras;
183--Funcion para ver los detalles de la compra de insumo
184create or replace function DetalleCompra(int)
185RETURNS TABLE(Nick character varying,Cant int,PrecioU int, Impuesto float) AS $$
186BEGIN
187RETURN QUERY
188 SELECT I.Nombre, CI.Cantidad,CI.PrecioUnitario,CI.ImpuestoUnitario
189 FROM Insumos as I INNER JOIN CompraXInsumo as CI ON (CI.IdInsumo = I.Id) where CI.IdFactura = $1;
190END;
191$$ LANGUAGE plpgsql;
192
193select * from DetalleCompra(5);
194
195
196insert into Insumos (Nombre) values ('Clavo');
197insert into Insumos (nombre) values ('Pegamento');
198insert into Insumos (nombre) values ('Levadura');
199insert into Insumos (nombre) values ('Huevos');
200insert into Insumos (nombre) values ('Queso');
201insert into Insumos (nombre) values ('Cebolla');
202
203select * from Insumos;
204
205insert into Productos (Nombre,Precio,ImpuestoAplicado) values('Atun',2000,0.13);
206insert into Productos (Nombre,Precio,ImpuestoAplicado) values('Garbanzos',2000,0.13);
207insert into Productos (Nombre,Precio,ImpuestoAplicado) values('Aceite',2000,0.13);
208insert into Productos (Nombre,Precio,ImpuestoAplicado) values('Natilla',1000,0.13);
209insert into Productos (Nombre,Precio,ImpuestoAplicado) values('Papas',800,0.13);
210insert into Productos (Nombre,Precio,ImpuestoAplicado) values('Cafe',2000,0.13);
211insert into Productos (Nombre,Precio,ImpuestoAplicado) values('Arroz',5000,0.13);
212insert into Productos (Nombre,Precio,ImpuestoAplicado) values('Frijoles',4000,0.13);
213
214select * from Productos;
215
216insert into Areas (Nombre,Dimension,ProductoProducido,Estado) values ('Las Mantas',500,2,'1');
217insert into Areas (Nombre,Dimension,ProductoProducido,Estado) values ('Las Catalinas',500,1,'1');
218insert into Areas (Nombre,Dimension,ProductoProducido,Estado) values ('Las Margaritas',500,3,'1');
219insert into Areas (Nombre,Dimension,ProductoProducido,Estado) values ('Las Marinelas',500,4,'1');
220insert into Areas (Nombre,Dimension,ProductoProducido,Estado) values ('Recicladoras',500,5,'1');
221insert into Areas (Nombre,Dimension,ProductoProducido,Estado) values ('MamaLucha',500,6,'1');
222insert into Areas (Nombre,Dimension,ProductoProducido,Estado) values ('Los TEC',500,7,'1');
223insert into Areas (Nombre,Dimension,ProductoProducido,Estado) values ('MercadoTEC',500,8,'1');
224
225select * from Areas;
226
227insert into Empleados values('702550708','Luis','Araya Aragon','Cargador',10000,10000*(1.50),'1');
228insert into Empleados values('602210128','Mario','Altamirano Aragon','Inspector',20000,20000*(1.50),'1');
229insert into Empleados values('123456789','Fred','Manzukic Aragon','Inspector',50000,50000*(1.50),'1');
230insert into Empleados values('287654321','Marta','Barrantes Aragon','Cargador',90000,90000*(1.50),'1');
231insert into Empleados values('127654321','Heiner','Salvatierra Aragon','Inspector',60000,60000*(1.50),'1');
232insert into Empleados values('456789123','Allan','Barquero Aragon','Cargador',80000,80000*(1.50),'1');
233
234select * from Empleados;
235
236insert into ValoresIniciales values('Megasuper','27184576','3024150012',2);
237select * from ValoresIniciales;
238
239Insert into InicioSesion values('operativo','operativo');
240Insert into InicioSesion values('administrador','administrador');
241select * from InicioSesion;
242
243
244insert into Compra (NumFactura,NombreProveedor,Fecha) values('123456','wallmart','14/8/2018');
245insert into Compra (NumFactura,NombreProveedor,Fecha) values('123654','ElPais','14/8/2017');
246insert into Compra (NumFactura,NombreProveedor,Fecha) values('321456','Clotilde','14/8/2016');
247insert into Compra (NumFactura,NombreProveedor,Fecha) values('456654','wallmart','14/2/2016');
248insert into Compra (NumFactura,NombreProveedor,Fecha) values('987654','wallmart','14/1/2015');
249insert into Compra (NumFactura,NombreProveedor,Fecha) values('789654','wallmart','14/4/2018');
250
251
252select * from Compra
253
254insert into CompraXInsumo values(1,1,5,5000,0.13);
255insert into CompraXInsumo values(2,1,5,5000,0.13);
256insert into CompraXInsumo values(3,1,10,5000,0.13);
257insert into CompraXInsumo values(5,1,2,5000,0.13);
258
259insert into CompraXInsumo values(2,4,2,5000,0.13);
260insert into CompraXInsumo values(3,4,10,5000,0.13);
261insert into CompraXInsumo values(4,4,5,5000,0.13);
262insert into CompraXInsumo values(1,4,5,5000,0.13);
263
264insert into CompraXInsumo values(6,3,5,5000,0.13);
265insert into CompraXInsumo values(5,3,5,5000,0.13);
266insert into CompraXInsumo values(4,3,8,5000,0.13);
267insert into CompraXInsumo values(3,3,10,5000,0.13);
268
269insert into CompraXInsumo values(2,5,10,5000,0.13);
270insert into CompraXInsumo values(5,5,10,5000,0.13);
271insert into CompraXInsumo values(6,5,10,5000,0.13);
272insert into CompraXInsumo values(1,5,20,5000,0.13);
273insert into CompraXInsumo values(3,5,30,5000,0.13);
274
275
276insert into CompraXInsumo values(2,6,20,5000,0.13);
277insert into CompraXInsumo values(4,6,30,5000,0.13);
278insert into CompraXInsumo values(6,6,15,5000,0.13);
279insert into CompraXInsumo values(5,6,3,5000,0.13);
280select * from CompraXInsumo
281
282Insert into Nomina (Mes,Anio) values(8,2018);
283Insert into Nomina (Mes,Anio) values(7,2016);
284Insert into Nomina (Mes,Anio) values(4,2015);
285Insert into Nomina (Mes,Anio) values(9,2018);
286
287select * from Nomina
288
289insert into NominaXEmpleado values(1,'702550708','Luis','Cargador',10000,15000);
290insert into NominaXEmpleado values(1,'602210128','Mario','Inspector',20000,30000);
291insert into NominaXEmpleado values(1,'123456789','Fred','Inspector',50000,75000);
292insert into NominaXEmpleado values(1,'287654321','Marta','Cargador',90000,135000);
293
294insert into NominaXEmpleado values(2,'287654321','Marta','Cargador',90000,135000);
295insert into NominaXEmpleado values(2,'456789123','Allan','Cargador',80000,120000);
296insert into NominaXEmpleado values(2,'127654321','Heiner','Inspector',60000,90000);
297insert into NominaXEmpleado values(2,'123456789','Fred','Inspector',50000,75000);
298
299insert into NominaXEmpleado values(3,'287654321','Marta','Cargador',90000,135000);
300insert into NominaXEmpleado values(3,'702550708','Luis','Cargador',10000,15000);
301insert into NominaXEmpleado values(3,'602210128','Mario','Inspector',20000,30000);
302
303insert into NominaXEmpleado values(4,'287654321','Marta','Cargador',90000,135000);
304insert into NominaXEmpleado values(4,'127654321','Heiner','Inspector',60000,90000);
305insert into NominaXEmpleado values(4,'123456789','Fred','Inspector',50000,75000);
306
307select * from NominaXEmpleado
308
309
310
311
312
313insert into Venta(NombreCliente,Fecha,LocalComercial,CedJuridica,Telefono) values('Marcos','14/8/2018','Megasuper','3024150012','27184576');
314insert into Venta(NombreCliente,Fecha,LocalComercial,CedJuridica,Telefono) values('Luis','14/8/2015','Megasuper','3024150012','27184576');
315insert into Venta(NombreCliente,Fecha,LocalComercial,CedJuridica,Telefono) values('Maria','14/8/2016','Megasuper','3024150012','27184576');
316insert into Venta(NombreCliente,Fecha,LocalComercial,CedJuridica,Telefono) values('Freddy','14/8/2017','Megasuper','3024150012','27184576');
317insert into Venta(NombreCliente,Fecha,LocalComercial,CedJuridica,Telefono) values('Freddy','14/4/2016','Megasuper','3024150012','27184576');
318
319select * from Venta
320
321
322insert into VentaXProducto values(1,2,5,'Las Mantas',2000,0.13);
323insert into VentaXProducto values(1,1,10,'Las Catalinas',2000,0.13);
324insert into VentaXProducto values(1,3,5,'Las Margaritas',2000,0.13);
325insert into VentaXProducto values(1,4,10,'Las Marinelas',1000,0.13);
326insert into VentaXProducto values(1,5,1,'Recicladoras',800,0.13);
327insert into VentaXProducto values(1,6,2,'MamaLucha',2000,0.13);
328
329insert into VentaXProducto values(2,8,1,'MercadoTEC',4000,0.13);
330insert into VentaXProducto values(2,7,5,'Los TEC',5000,0.13);
331insert into VentaXProducto values(2,6,1,'MamaLucha',2000,0.13);
332insert into VentaXProducto values(2,5,5,'Recicladoras',800,0.13);
333insert into VentaXProducto values(2,3,10,'Las Margaritas',2000,0.13);
334
335
336insert into VentaXProducto values(3,4,5,'Las Marinelas',1000,0.13);
337insert into VentaXProducto values(3,5,2,'Recicladoras',800,0.13);
338insert into VentaXProducto values(3,6,1,'MamaLucha',2000,0.13);
339insert into VentaXProducto values(3,7,4,'Los TEC',5000,0.13);
340insert into VentaXProducto values(3,8,3,'MercadoTEC',4000,0.13);
341insert into VentaXProducto values(3,2,1,'Las Mantas',2000,0.13);
342
343
344insert into VentaXProducto values(4,1,2,'Las Catalinas',2000,0.13);
345insert into VentaXProducto values(4,2,1,'Las Mantas',2000,0.13);
346insert into VentaXProducto values(4,3,10,'Las Margaritas',2000,0.13);
347insert into VentaXProducto values(4,4,4,'Las Marinelas',1000,0.13);
348insert into VentaXProducto values(4,8,2,'MercadoTEC',4000,0.13);
349insert into VentaXProducto values(4,5,10,'Recicladoras',800,0.13);
350
351
352insert into VentaXProducto values(5,1,2,'Las Catalinas',2000,0.13);
353insert into VentaXProducto values(5,2,1,'Las Mantas',2000,0.13);
354insert into VentaXProducto values(5,6,1,'MamaLucha',2000,0.13);
355insert into VentaXProducto values(5,4,4,'Las Marinelas',1000,0.13);
356insert into VentaXProducto values(5,8,2,'MercadoTEC',4000,0.13);
357insert into VentaXProducto values(5,5,10,'Recicladoras',800,0.13);
358insert into VentaXProducto values(5,7,5,'Los TEC',5000,0.13);
359
360
361select * from VentaXproducto