¿crear evento mysql que consulte los registro de una tabla y por cada registro los inserte en otra
Publicado por DEIHER (1 intervención) el 23/03/2019 16:22:53
Estoy tratando de realizar un evento que se ejecute todos los días a las 11:59 pm. Que me consulte los registros(codigo) de una tabla(tblpuntoAtencion) y con el resultado haga una especie de foreach, y con cada registro de la tabla sume el valor de otra una tabla(tblenvios) agrupado por codigo(registros consultados anteriormente) y dependiendo del estado que es 1 y le reste datos de la misma tabla dependiendo el estado que es 2 adicionalmente le sume el valor total de valorRecaudos de otra tabla(tblrecaudo), y los inserte en otra tabla(tblsaldos). Espero me puedan ayudar. Estas son las tablas y algunos registros:
**tblPuntoAtencion**
----------------------------------------------------------
id codigo barrio
1 8000 bosque
2 8001 mercado
3 8002 palmeras
-----------------------------------------------
**tblEnvios**
-------------------------------------------
idEnvio valor fecha estado puntoA_E
100001 50000 18-03-2019 1 8000
100002 80000 18-03-2019 1 8000
100003 100000 18-03-2019 2 8000
100004 60000 18-03-2019 1 8002
-----------------------------------------------------
**tblrecaudo**
----------------------------------------------------
idrecaudo valorRecaudo fecha puntoA
1 30000 2019-03-16 8000
2 20000 2019-03-18 8000
3 40000 2019-03-18 8001
----------------------------------------------------------
Aquí quiero que se guarden los registros:
**tblsaldo**
idSaldo
codigoPa
saldo
fecha
En la tblEnvios suma valor con estado 1 por puntoA_E y fecha actual y le resta el valor con estado 2 por puntoA, fecha actual y se le suma valorRecaudo de la tblrecaudo por puntoA y fecha actual "suponiendo que la fecha actual es 2019-03-18"
Ejemplo:
codigo valor estado - codigo valor estado + puntoA valorRecaudo = puntoA total
8000 130000 1 - 8000 100000 2 + 8000 20000 = 8000 50000
**resultados esperados**
puntoA total fechaactual
8000 50000 2019-03-18
8001 40000 2019-03-18
8002 60000 2019-03-18
He realizado algunas consultas, pero no sé como hacer el ciclo para que, por cada registro que esté en la tblpuntoAtencion, se guarde en los datos en la tblSaldo
Y estos datos se guarden en la tabla tblsaldo
SELECT codigo FROM tblpuntoAtencion
Con este código puedo obtener los códigos de los puntos de atención:
SELECT SUM(CASE WHEN E.puntoA_E = 8000 AND E.estado = 1 THEN E.valor ELSE 0 END) - SUM(CASE WHEN E.puntoA_E = 8000 AND E.estado = 2 THEN E.valor ELSE 0 END) + SUM(CASE WHEN R.puntoA = 8000 THEN R.valorRecaudo ELSE 0 END) AS total FROM tblenvios E,tblrecaudo R where E.puntoA_E=R.puntoA AND R.fecha ='2019-03-18' AND E.fecha ='2019-03-18' order by puntoA_E;
Con esta consulta me trae el resultado de la sumatoria de la tblEnvios y tblrecaudo pero faltaría algo en la condicion no me arroja el dato exacto
Tratando de hacer el evento:
CREATE EVENT guardaSaldo
ON SCHEDULE
EVERY 1 day STARTS '2019-03-18 23:59:00' + interval 1 day do
begin
SELECT codigo FROM tblpuntoAtencion;
foreach (CODIGO as COD) {
SELECT SUM(CASE WHEN E.puntoA_E = COD AND E.estado = 1 THEN E.valor ELSE 0 END) - SUM(CASE WHEN E.puntoA_E = COD AND E.estado = 2 THEN E.valor ELSE 0 END) + SUM(CASE WHEN R.puntoA = COD THEN R.valorRecaudo ELSE 0 END) AS total FROM tblenvios E,tblrecaudo R where E.puntoA_E=R.puntoA AND R.fecha ='2019-03-18' AND E.fecha ='2019-03-18' order by puntoA_E;
INSERT INTO tblsaldo(codigoPa,saldo,fecha) VALUES(8000,50000,'2019-03-18')
end;
}
el **foreach** es para hacerme entender de lo que quiero hacer
No sé cómo hacer que haga la consulta de todos los puntoA y los inserte los datos en la tabla tblsaldo de forma dinamica
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
CREATE TABLE tblenvios (idEnvio int(11) NOT NULL,
valor double NOT NULL, fecha date NOT NULL,estado enum('1','2') NOT NULL,
puntoA_E int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO tblenvios (idEnvio, valor, fecha, estado, puntoA_E) VALUES
(100001, 50000, '2019-03-18', '1', 8000),
(100002, 80000, '2019-03-18', '1', 8000),
(100003, 100000, '0000-00-00', '2', 8000),
(100004, 60000, '2019-03-18', '1', 8002);
CREATE TABLE tblpuntoatencion (id int(11) NOT NULL,
codigo int(11) NOT NULL,
barrio varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO tblpuntoatencion (id, codigo, barrio) VALUES
(1, 8000, 'Bosque'),
(2, 8001, 'Mercado'),
(3, 8002, 'Palmeras');
CREATE TABLE tblrecaudo (idRecaudo int(11) NOT NULL,
valorRecaudo double NOT NULL, fecha date NOT NULL,puntoA int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO tblrecaudo (idRecaudo, valorRecaudo, fecha, puntoA) VALUES
(1, 30000, '2019-03-16', 8000),
(2, 20000, '2019-03-18', 8000),
(3, 40000, '2019-03-18', 8001);
CREATE TABLE tblsaldo (idSaldo int(11) NOT NULL,
codigoPa int(11) NOT NULL,
saldo int(11) NOT NULL,
fecha int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
**tblPuntoAtencion**
----------------------------------------------------------
id codigo barrio
1 8000 bosque
2 8001 mercado
3 8002 palmeras
-----------------------------------------------
**tblEnvios**
-------------------------------------------
idEnvio valor fecha estado puntoA_E
100001 50000 18-03-2019 1 8000
100002 80000 18-03-2019 1 8000
100003 100000 18-03-2019 2 8000
100004 60000 18-03-2019 1 8002
-----------------------------------------------------
**tblrecaudo**
----------------------------------------------------
idrecaudo valorRecaudo fecha puntoA
1 30000 2019-03-16 8000
2 20000 2019-03-18 8000
3 40000 2019-03-18 8001
----------------------------------------------------------
Aquí quiero que se guarden los registros:
**tblsaldo**
idSaldo
codigoPa
saldo
fecha
En la tblEnvios suma valor con estado 1 por puntoA_E y fecha actual y le resta el valor con estado 2 por puntoA, fecha actual y se le suma valorRecaudo de la tblrecaudo por puntoA y fecha actual "suponiendo que la fecha actual es 2019-03-18"
Ejemplo:
codigo valor estado - codigo valor estado + puntoA valorRecaudo = puntoA total
8000 130000 1 - 8000 100000 2 + 8000 20000 = 8000 50000
**resultados esperados**
puntoA total fechaactual
8000 50000 2019-03-18
8001 40000 2019-03-18
8002 60000 2019-03-18
He realizado algunas consultas, pero no sé como hacer el ciclo para que, por cada registro que esté en la tblpuntoAtencion, se guarde en los datos en la tblSaldo
Y estos datos se guarden en la tabla tblsaldo
SELECT codigo FROM tblpuntoAtencion
Con este código puedo obtener los códigos de los puntos de atención:
SELECT SUM(CASE WHEN E.puntoA_E = 8000 AND E.estado = 1 THEN E.valor ELSE 0 END) - SUM(CASE WHEN E.puntoA_E = 8000 AND E.estado = 2 THEN E.valor ELSE 0 END) + SUM(CASE WHEN R.puntoA = 8000 THEN R.valorRecaudo ELSE 0 END) AS total FROM tblenvios E,tblrecaudo R where E.puntoA_E=R.puntoA AND R.fecha ='2019-03-18' AND E.fecha ='2019-03-18' order by puntoA_E;
Con esta consulta me trae el resultado de la sumatoria de la tblEnvios y tblrecaudo pero faltaría algo en la condicion no me arroja el dato exacto
Tratando de hacer el evento:
CREATE EVENT guardaSaldo
ON SCHEDULE
EVERY 1 day STARTS '2019-03-18 23:59:00' + interval 1 day do
begin
SELECT codigo FROM tblpuntoAtencion;
foreach (CODIGO as COD) {
SELECT SUM(CASE WHEN E.puntoA_E = COD AND E.estado = 1 THEN E.valor ELSE 0 END) - SUM(CASE WHEN E.puntoA_E = COD AND E.estado = 2 THEN E.valor ELSE 0 END) + SUM(CASE WHEN R.puntoA = COD THEN R.valorRecaudo ELSE 0 END) AS total FROM tblenvios E,tblrecaudo R where E.puntoA_E=R.puntoA AND R.fecha ='2019-03-18' AND E.fecha ='2019-03-18' order by puntoA_E;
INSERT INTO tblsaldo(codigoPa,saldo,fecha) VALUES(8000,50000,'2019-03-18')
end;
}
el **foreach** es para hacerme entender de lo que quiero hacer
No sé cómo hacer que haga la consulta de todos los puntoA y los inserte los datos en la tabla tblsaldo de forma dinamica
Valora esta pregunta
0