· 5 years ago · May 26, 2020, 09:50 PM
1CREATE DEFINER=`root`@`localhost` TRIGGER `gestaomuseug15_v2`.`generateAlerts` AFTER INSERT ON `medicoessensores` FOR EACH ROW
2
3BEGIN
4 SET @limiar = (SELECT LimiarInferiorTmp FROM sistema);
5 SET @limite = (SELECT LimiteTemperatura FROM sistema);
6 SET @tsM0 = (select DataHoraMedicao from alerta where TipoSensor='tmp' order by DataHoraMedicao DESC LIMIT 1);
7 SET @PTA = (SELECT PeriodicidadeTmpAlerta FROM sistema);
8
9 SET @limiarH = (SELECT LimiarInferiorHum FROM sistema);
10 SET @limiteH = (SELECT LimiteHumidade FROM sistema);
11 SET @tsM0H = (select DataHoraMedicao from alerta where TipoSensor='hum' order by DataHoraMedicao DESC LIMIT 1);
12 SET @PHA = (SELECT PeriodicidadeHumAlerta FROM sistema);
13
14 SET @limiteL = (SELECT LimiteLuminosidade FROM sistema);
15 SET @tsM0L = (select DataHoraMedicao from alerta where TipoSensor='lum' order by DataHoraMedicao DESC LIMIT 1);
16 SET @PLA = (SELECT PeriodicidadeLumAlerta FROM sistema);
17
18 SET @tsM0M = (select DataHoraMedicao from alerta where TipoSensor='mov' order by DataHoraMedicao DESC LIMIT 1);
19 SET @PMA = (SELECT PeriodicidadeMovAlerta FROM sistema);
20
21 -- Temperatura
22
23 IF new.ValorMedicao >= @limiar and new.TipoSensor='tmp' THEN
24 SET @contador = (SELECT ContadorTmp FROM sistema);
25 UPDATE sistema SET ContadorTmp = @contador + 1;
26 SET @contador = (SELECT ContadorTmp FROM sistema);
27 IF @contador like 3 THEN
28 IF new.ValorMedicao >= @limite THEN
29 INSERT INTO alerta (DataHoraMedicao, TipoSensor, ValorMedicao, Limite, Descricao)
30 VALUES (new.DataHoraMedicao, 'tmp', new.ValorMedicao, @limite, 'LIMITE DE TEMPERATURA VIOLADO!');
31 UPDATE sistema SET ContadorTmp = 0;
32 ELSE
33 SET @f = EXISTS(Select * from alerta where TipoSensor like 'lum');
34 IF (@f like '1') THEN
35 IF UNIX_TIMESTAMP(new.DataHoraMedicao)-UNIX_TIMESTAMP(@tsM0) >= @PTA THEN
36 INSERT INTO alerta (DataHoraMedicao, TipoSensor, ValorMedicao, Limite, Descricao)
37 VALUES (new.DataHoraMedicao, 'tmp', new.ValorMedicao, @limite, 'APROXIMAÇÃO AO LIMITE DE TEMPERATURA EMINENTE!');
38 END IF;
39 END IF;
40 UPDATE sistema SET ContadorTmp = 0;
41 END IF;
42 END IF;
43 END IF;
44
45 -- Humidade
46
47 IF new.ValorMedicao >= @limiarH and new.TipoSensor='hum' THEN
48 SET @contador = (SELECT ContadorHum FROM sistema);
49 UPDATE sistema SET ContadorHum = @contador + 1;
50 SET @contador = (SELECT ContadorHum FROM sistema);
51 IF @contador = 3 THEN
52 IF new.ValorMedicao >= @limiteH THEN
53 INSERT INTO alerta (DataHoraMedicao, TipoSensor, ValorMedicao, Limite, Descricao)
54 VALUES (new.DataHoraMedicao, 'hum', new.ValorMedicao, @limiteH, 'LIMITE DE TEMPERATURA VIOLADO!');
55 UPDATE sistema SET ContadorHum = 0;
56 ELSE
57 SET @f = EXISTS(Select * from alerta where TipoSensor like 'lum');
58 IF (@f like '1') THEN
59 IF UNIX_TIMESTAMP(new.DataHoraMedicao)-UNIX_TIMESTAMP(@tsM0) >= @PHA THEN
60 INSERT INTO alerta (DataHoraMedicao, TipoSensor, ValorMedicao, Limite, Descricao)
61 VALUES (new.DataHoraMedicao, 'hum', new.ValorMedicao, @limiteH, 'APROXIMAÇÃO AO LIMITE DE TEMPERATURA EMINENTE!');
62 UPDATE sistema SET ContadorHum = 0;
63 END IF;
64 END IF;
65 END IF;
66 ELSE
67 UPDATE sistema SET ContadorHum = @contador + 1;
68 END IF;
69 END IF;
70
71 -- Luinosidade
72
73 SET @duracao = (SELECT DuracaoRonda from sistema);
74 IF new.ValorMedicao >= @limiteL and new.TipoSensor='lum' THEN
75 SET @f = EXISTS(Select * from alerta where TipoSensor like 'lum');
76 IF (@f like '1') THEN
77 IF UNIX_TIMESTAMP(new.DataHoraMedicao)-UNIX_TIMESTAMP(@tsM0L) >= @PLA THEN
78 SET @DayInt = (SELECT DAYOFWEEK(new.DataHoraMedicao));
79 SET @RoundTime = (SELECT TIME(new.DataHoraMedicao));
80 CREATE TEMPORARY TABLE IF NOT EXISTS temp SELECT HoraRonda, IF ((@RoundTime BETWEEN HoraRonda AND ADDTIME(HoraRonda,CONCAT('00:',@Duracao,':00')) AND (DiaSemana like @DayInt)), HoraRonda, null) as 'Result' FROM diasemana ;
81 SET @Ronda = (SELECT EXISTS (SELECT * FROM temp WHERE Result IS NOT NULL));
82 DELETE FROM temp;
83 CREATE TEMPORARY TABLE IF NOT EXISTS temp SELECT dataHoraEntrada, IF ((new.DataHoraMedicao BETWEEN dataHoraEntrada AND DATE_ADD(dataHoraEntrada,INTERVAL @Duracao MINUTE) AND (DiaSemana like @DayInt)), dataHoraEntrada, null) as 'Result' FROM rondaextra ;
84 SET @RondaExtra = (SELECT EXISTS (SELECT * FROM temp WHERE Result IS NOT NULL));
85 DELETE FROM temp;
86 IF @Ronda like '0' and @RondaExtra like '0' THEN
87 INSERT INTO alerta (DataHoraMedicao, TipoSensor, ValorMedicao, Limite, Descricao)
88 VALUES (new.DataHoraMedicao, 'lum', new.ValorMedicao, @limiteL, 'LIMITE MÁXIMO DE LUMINOSIDADE VIOLADO!');
89 END IF;
90 END IF;
91 ELSE
92 INSERT INTO alerta (DataHoraMedicao, TipoSensor, ValorMedicao, Limite, Descricao)
93 VALUES (new.DataHoraMedicao, 'lum', new.ValorMedicao, @limiteL, 'LIMITE MÁXIMO DE LUMINOSIDADE VIOLADO!');
94 END IF;
95 END IF;
96
97 -- Movimento
98
99 SET @Duracao = (SELECT DuracaoRonda from sistema);
100 IF new.ValorMedicao like '1' and new.TipoSensor='mov' THEN
101 SET @f = EXISTS(Select * from alerta where TipoSensor like 'mov');
102 IF (@f like '1') THEN
103 IF UNIX_TIMESTAMP(new.DataHoraMedicao)-UNIX_TIMESTAMP(@tsM0M) >= @PMA THEN
104 CREATE TEMPORARY TABLE IF NOT EXISTS temp SELECT HoraRonda, IF ((@RoundTime BETWEEN HoraRonda AND ADDTIME(HoraRonda,CONCAT('00:',@Duracao,':00')) AND (DiaSemana like @DayInt)), HoraRonda, null) as 'Result' FROM diasemana ;
105 SET @Ronda = (SELECT EXISTS (SELECT * FROM temp WHERE Result IS NOT NULL));
106 DELETE FROM temp;
107 CREATE TEMPORARY TABLE IF NOT EXISTS temp SELECT dataHoraEntrada, IF ((new.DataHoraMedicao BETWEEN dataHoraEntrada AND DATE_ADD(dataHoraEntrada,INTERVAL @Duracao MINUTE) AND (DiaSemana like @DayInt)), dataHoraEntrada, null) as 'Result' FROM rondaextra ;
108 SET @RondaExtra = (SELECT EXISTS (SELECT * FROM temp WHERE Result IS NOT NULL));
109 DELETE FROM temp;
110 IF @RondaExtra like '0' AND @Ronda like '0' THEN
111 INSERT INTO alerta (DataHoraMedicao, TipoSensor, ValorMedicao, Descricao)
112 VALUES (new.DataHoraMedicao, 'mov', new.ValorMedicao, 'MOVIMENTO DETETADO!');
113 END IF;
114 END IF;
115 END IF;
116 END IF;
117END