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