· 8 years ago · Dec 29, 2017, 03:24 PM
1ALTER TABLE alarmasvehiculos ADD COLUMN encen_indice_buzer integer DEFAULT 0;
2ALTER TABLE alarmasvehiculos ADD COLUMN encen_buzer_primera_activacion timestamp without time zone;
3
4ALTER TABLE alarmasvehiculos ADD COLUMN apag_indice_relay integer DEFAULT 0;
5ALTER TABLE alarmasvehiculos ADD COLUMN apag_relay_primera_activacion timestamp without time zone;
6
7ALTER TABLE alarmasvehiculos ADD COLUMN envia_reset_indice integer DEFAULT 0;
8ALTER TABLE alarmasvehiculos ADD COLUMN envia_reset_primera_activacion timestamp without time zone;
9ALTER TABLE alarmasvehiculos ADD COLUMN envia_reset_primera_accion timestamp without time zone;
10ALTER TABLE alarmasvehiculos ADD COLUMN envia_reset_conteo_acciones integer DEFAULT 0;
11
12ALTER TABLE alarmasvehiculos ADD COLUMN envia_cons_pasj_indice integer DEFAULT 0;
13ALTER TABLE alarmasvehiculos ADD COLUMN envia_cons_pasj_primera_activacion timestamp without time zone;
14ALTER TABLE alarmasvehiculos ADD COLUMN envia_cons_conteo_acciones integer DEFAULT 0;
15ALTER TABLE alarmasvehiculos ADD COLUMN envia_cons_primera_accion timestamp without time zone;
16
17CREATE OR REPLACE FUNCTION process_alarms(
18 registers_ integer,
19 inactivate_time_ integer,
20 inactivate_count_ integer)
21 RETURNS SETOF alarm AS
22$BODY$
23DECLARE
24
25 OK_ BOOLEAN;
26 inactivate_ BOOLEAN;
27
28 event_ INTEGER;
29 index_ INTEGER;
30 alarm_count_ INTEGER;
31
32 alarm_ BIGINT;
33 alarm_date TIMESTAMP WITHOUT TIME ZONE;
34
35 zone_control_id_ BIGINT;
36
37 name_ CHARACTER VARYING;
38
39 rule_radius_ INTEGER;
40 latitude_ DOUBLE PRECISION;
41 longitude_ DOUBLE PRECISION;
42 latitude_rule_ CHARACTER VARYING;
43 longitude_rule_ CHARACTER VARYING;
44 alarm_area GEOMETRY;
45 speed_ DOUBLE PRECISION;
46
47 zone_type_ CHARACTER VARYING;
48
49 vehicle_ CHARACTER VARYING;
50 driver_ BIGINT;
51
52 address_ TEXT;
53 municipality_ TEXT;
54 department_ TEXT;
55
56 date_ TIMESTAMP WITHOUT TIME ZONE;
57
58 alarms_ RECORD;
59 events_ RECORD;
60
61 row_ ALARM;
62
63 events_index INTEGER;
64
65 buzer_on_command_id_ bigint;
66 relay_off_command_id_ bigint;
67
68 vehicle_gps_ boolean;
69 vehicle_gsm_ boolean;
70
71 actions_occurrences_ integer;
72
73BEGIN
74 UPDATE insert_events
75 SET estado = 'X'
76 FROM (
77 SELECT
78 idvehiculo,
79 min(idinsert_events)
80 FROM insert_events
81 WHERE estado = 'E'
82 GROUP BY 1
83 ORDER BY 2
84 LIMIT registers_) i
85 WHERE insert_events.idvehiculo = i.idvehiculo AND estado = 'E';
86
87 events_index = 0;
88
89 FOR events_ IN (
90 SELECT DISTINCT
91 ie.idvehiculo,
92 ie.idevento,
93 ie.latitud,
94 ie.longitud,
95 ie.velocidad,
96 ie.fecha_evento,
97 ie.indicegeocerca,
98 ie.fecha,
99 v.numinterno,
100 v.placa,
101 v.tipo_modem,
102 ed.descripcion AS nombre_evento
103 FROM insert_events ie
104 INNER JOIN alarmasvehiculos av
105 ON ie.idvehiculo = av.idvehiculo
106 INNER JOIN vehiculos v
107 ON av.idvehiculo = v.idvehiculo
108 INNER JOIN alarmas a
109 ON av.idalarma = a.idalarma
110 LEFT OUTER JOIN eventosdesc ed
111 ON ie.idevento :: CHARACTER VARYING = ed.evento
112 WHERE ie.estado = 'X' AND av.estado = 'A' AND a.estado = 'A' AND v.estado = 'Y'
113 AND localtimestamp :: TIME WITHOUT TIME ZONE BETWEEN a.hhorainicial AND a.hhorafinal
114 AND (a.hdias IS NULL OR TRIM(a.hdias) = '' OR a.hdias ILIKE '%' || EXTRACT(DOW FROM localtimestamp) || '%')
115 AND a.tipo = 'EVE' AND ie.tipotrama = 'E'
116 ORDER BY fecha
117 ) LOOP
118
119 events_index = events_index + 1;
120
121 event_ = events_.idevento;
122 index_ = events_.indicegeocerca;
123 latitude_ = events_.latitud;
124 longitude_ = events_.longitud;
125 speed_ = events_.velocidad;
126 vehicle_ = events_.idvehiculo;
127 date_ = events_.fecha_evento;
128
129 RAISE NOTICE 'INDEX: %, VEHICULO: %, FECHA: %', events_index, vehicle_, date_;
130
131 FOR alarms_ IN (
132
133 SELECT
134 idalarma,
135 descripcion,
136 consola,
137 email,
138 sms,
139 notificar,
140 movil,
141 generar_accion,
142 encen_buzer,
143 encen_ocurr_buzer,
144 encen_indice_buzer,
145 encen_tiempo_buzer,
146 encen_buzer_primera_activacion,
147 apag_buzer,
148 apag_tiempo_buzer,
149 apag_relay,
150 apag_ocurr_relay,
151 apag_tiempo_relay,
152 apag_indice_relay,
153 apag_relay_primera_activacion,
154 envia_reset,
155 envia_reset_tiempo_ocurr,
156 envia_reset_indice,
157 envia_reset_cant,
158 envia_reset_primera_activacion,
159 envia_reset_limite_acciones,
160 envia_reset_conteo_acciones,
161 envia_reset_tiempo_acciones,
162 envia_reset_primera_accion,
163 envia_consulta_pasajeros,
164 envia_cons_pasj_tiempo_ocurr,
165 envia_cons_pasj_indice,
166 envia_cons_pasj_ocurr,
167 envia_cons_pasj_primera_activacion,
168 envia_cons_limite_acciones,
169 envia_cons_conteo_acciones,
170 envia_cons_tiempo_acciones,
171 envia_cons_primera_accion,
172 maneja_buzer,
173 maneja_relay,
174 maneja_sensor_pasajeros,
175 proceso,
176 mensaje,
177 nombre,
178 ocurrencias_limite,
179 contactos,
180 usuarios_tokens,
181 (CASE WHEN tiempo > 0
182 THEN minutos >= tiempo
183 ELSE FALSE END) AS resetear,
184 ok,
185 NULL AS vehiculos
186 FROM (
187 SELECT
188 a.idalarma,
189 a.descripcion,
190 a.proceso,
191 (a.alerta_consola = 'Y') AS consola,
192 (a.alerta_email = 'Y') AS email,
193 (a.alerta_sms = 'Y') AS sms,
194 (a.alerta_movil = 'Y') AS movil,
195 (a.genera_accion AND
196 COALESCE(((a.acciones_dias IS NULL OR TRIM(a.acciones_dias) = '')
197 OR (
198 (localtimestamp :: TIME WITHOUT TIME ZONE BETWEEN a.acciones_hinicial AND a.acciones_hfinal)
199 AND (a.acciones_dias ILIKE '%' || EXTRACT(DOW FROM localtimestamp) || '%'))),
200 FALSE)) AS generar_accion,
201 a.encen_buzer,
202 a.encen_ocurr_buzer,
203 av.encen_indice_buzer,
204 a.encen_tiempo_buzer,
205 av.encen_buzer_primera_activacion,
206 a.apag_buzer,
207 a.apag_tiempo_buzer,
208 a.apag_relay,
209 a.apag_ocurr_relay,
210 a.apag_tiempo_relay,
211 av.apag_indice_relay,
212 av.apag_relay_primera_activacion,
213 a.envia_reset,
214 a.envia_reset_tiempo_ocurr,
215 av.envia_reset_indice,
216 a.envia_reset_cant,
217 av.envia_reset_primera_activacion,
218 a.envia_reset_limite_acciones,
219 av.envia_reset_conteo_acciones,
220 a.envia_reset_tiempo_acciones,
221 av.envia_reset_primera_accion,
222 a.envia_consulta_pasajeros,
223 a.envia_cons_pasj_tiempo_ocurr,
224 av.envia_cons_pasj_indice,
225 a.envia_cons_pasj_ocurr,
226 av.envia_cons_pasj_primera_activacion,
227 a.envia_cons_limite_acciones,
228 av.envia_cons_conteo_acciones,
229 a.envia_cons_tiempo_acciones,
230 av.envia_cons_primera_accion,
231 (p.maneja_buzer = 'S' AND dev.buzer) AS maneja_buzer,
232 (p.maneja_relay = 'S' AND dev.relay) AS maneja_relay,
233 (p.manejasensorpasajeros = 'S') AS maneja_sensor_pasajeros,
234 a.mensaje,
235 a.nombre,
236 a.ocurrencias_limite,
237 a.tiempo_ocurrencia AS tiempo,
238 ROUND(EXTRACT(EPOCH FROM (localtimestamp - av.fecha_primera_activacion)) / 60) AS minutos,
239 COALESCE(((a.notificaciondias IS NULL OR TRIM(a.notificaciondias) = '')
240 OR (
241 (localtimestamp :: TIME WITHOUT TIME ZONE BETWEEN notificacionhorainicial AND notificacionhorafinal)
242 AND (a.notificaciondias ILIKE '%' || EXTRACT(DOW FROM localtimestamp) || '%'))),
243 FALSE) AS notificar,
244 bool_and(CASE
245 WHEN ar.tipo = 'E'
246 THEN
247 CASE
248 WHEN event_ != 69 AND event_ BETWEEN 25 AND 76
249 THEN
250 COALESCE(
251 (
252 SELECT TRUE
253 FROM alarmas_regla_eventos
254 WHERE idalarma = ar.idalarma AND idregla = ar.idregla AND
255 evento = event_ :: CHARACTER VARYING AND indice = index_
256 ),
257 FALSE)
258 ELSE
259 COALESCE(
260 (
261 SELECT TRUE
262 FROM alarmas_regla_eventos
263 WHERE idalarma = ar.idalarma AND idregla = ar.idregla AND
264 evento = event_ :: CHARACTER VARYING
265 ),
266 FALSE)
267 END
268 WHEN ar.tipo = 'U' AND (latitude_ BETWEEN -90 AND 90) AND (longitude_ BETWEEN -200 AND 200)
269 THEN
270 CASE
271 WHEN TRIM(ar.tipo_geocerca) = 'AC'
272 THEN
273 COALESCE(
274 (
275 SELECT CASE WHEN ST_Within(
276 ST_GeomFromText('POINT(' || longitude_ || ' ' || latitude_ || ')', 4326),
277 geom)
278 THEN
279 TRIM(ar.condicion) = 'IN'
280 ELSE
281 TRIM(ar.condicion) = 'OUT'
282 END
283 FROM areascontrol
284 WHERE idarea = COALESCE(ar.idgeocerca, 0) AND estado = 'A'
285 ),
286 FALSE)
287 WHEN TRIM(ar.tipo_geocerca) = 'PC'
288 THEN
289 COALESCE(
290 (
291 SELECT CASE WHEN ST_Distance(ST_Transform(ST_GeomFromText(
292 'POINT(' || longitud || ' ' ||
293 latitud
294 || ')', 4326), 2163),
295 ST_Transform(ST_GeomFromText(
296 'POINT(' || longitude_ || ' ' ||
297 latitude_ || ')', 4326), 2163)) <=
298 radio
299 THEN
300 TRIM(ar.condicion) = 'IN'
301 ELSE
302 TRIM(ar.condicion) = 'OUT'
303 END
304 FROM puntoscontrol
305 WHERE idpunto = COALESCE(ar.idpunto, 0) AND estado = 'A'
306 AND latitud IS NOT NULL AND longitud IS NOT NULL
307 AND latitud != '' AND longitud != ''
308 AND latitud::DOUBLE PRECISION BETWEEN -90 AND 90
309 AND longitud::DOUBLE PRECISION BETWEEN -200 AND 200
310 ),
311 FALSE)
312 WHEN TRIM(ar.tipo_geocerca) = 'PI'
313 THEN
314 COALESCE(
315 (
316 SELECT CASE WHEN ST_Distance(ST_Transform(ST_GeomFromText(
317 'POINT(' || longitud || ' ' ||
318 latitud
319 || ')', 4326), 2163),
320 ST_Transform(ST_GeomFromText(
321 'POINT(' || longitude_ || ' ' ||
322 latitude_ || ')', 4326), 2163)) <=
323 TRIM(ar.valor) :: NUMERIC
324 THEN
325 TRIM(ar.condicion) = 'IN'
326 ELSE
327 TRIM(ar.condicion) = 'OUT'
328 END
329 FROM puntosdeinteres
330 WHERE idpunto = COALESCE(ar.idpunto, 0) AND estado = 'A'
331 AND latitud IS NOT NULL AND longitud IS NOT NULL
332 AND latitud != '' AND longitud != ''
333 AND latitud::DOUBLE PRECISION BETWEEN -90 AND 90
334 AND longitud::DOUBLE PRECISION BETWEEN -200 AND 200
335 ),
336 FALSE)
337 WHEN TRIM(ar.tipo_geocerca) = 'R'
338 THEN
339 COALESCE(
340 (
341 SELECT CASE WHEN ST_Distance(ST_Transform(geom, 2163),
342 ST_Transform(ST_GeomFromText(
343 'POINT(' || longitude_ || ' ' ||
344 latitude_ || ')', 4326), 2163)) <=
345 TRIM(ar.valor) :: NUMERIC
346 THEN
347 TRIM(ar.condicion) = 'IN'
348 ELSE
349 TRIM(ar.condicion) = 'OUT'
350 END
351 FROM rutasgeograficas
352 WHERE idruta = COALESCE(ar.idgeocerca, 0) AND estado_ruta = 'A'
353 ),
354 FALSE)
355 WHEN TRIM(ar.tipo_geocerca) = 'RG'
356 THEN
357 COALESCE(
358 (
359 SELECT CASE WHEN ST_Distance(ST_Transform(rg.geom, 2163),
360 ST_Transform(ST_GeomFromText(
361 'POINT(' || longitude_ || ' ' ||
362 latitude_ || ')', 4326), 2163)) <=
363 TRIM(ar.valor) :: NUMERIC
364 THEN
365 TRIM(ar.condicion) = 'IN'
366 ELSE
367 TRIM(ar.condicion) = 'OUT'
368 END
369 FROM vehiculos v
370 INNER JOIN progvehiculos pg ON pg.idprogramacion = v.idprogactual
371 INNER JOIN rutas rt ON rt.idruta = pg.idruta
372 INNER JOIN rutasgeograficas rg ON rt.idrutageo = rg.idruta
373 WHERE rt.estado_ruta = 'A' AND v.idvehiculo = vehicle_
374 ),
375 FALSE)
376 ELSE
377 FALSE
378 END
379 WHEN ar.tipo = 'V'
380 THEN
381 CASE
382 WHEN TRIM(ar.variable) = 'V'
383 THEN
384 CASE WHEN ar.valor IS NULL OR TRIM(ar.valor) = ''
385 THEN
386 FALSE
387 ELSE
388 COALESCE(
389 (
390 CASE
391 WHEN TRIM(ar.condicion) = 'M'
392 THEN
393 valor :: NUMERIC(18, 2) > 0 AND speed_ > valor :: NUMERIC(18, 2)
394 WHEN TRIM(ar.condicion) = 'L'
395 THEN
396 valor :: NUMERIC(18, 2) > 1 AND speed_ < valor :: NUMERIC(18, 2)
397 WHEN TRIM(ar.condicion) = 'I'
398 THEN
399 split_part(valor, ';', 1) :: NUMERIC(18, 2) > 0 AND
400 split_part(valor, ';', 2) :: NUMERIC(18, 2) > 1 AND
401 speed_ BETWEEN split_part(valor, ';', 1) :: NUMERIC(18, 2)
402 AND split_part(valor, ';', 2) :: NUMERIC(18, 2)
403 ELSE
404 FALSE
405 END
406 ),
407 FALSE)
408 END
409 ELSE
410 FALSE
411 END
412 ELSE FALSE
413 END) AS ok,
414 array_to_json(
415 array_agg(array_to_json(ARRAY [ac.idcontacto :: CHARACTER VARYING, c.email, c.celp, ac.operador,
416 CASE WHEN ac.enviaemail
417 THEN 'TRUE'
418 ELSE 'FALSE' END,
419 CASE WHEN ac.enviasms
420 THEN 'TRUE'
421 ELSE 'FALSE' END]))) :: CHARACTER VARYING AS contactos,
422 array_to_json(array_agg(ut.token)) :: CHARACTER VARYING AS usuarios_tokens
423 FROM alarmas a
424 INNER JOIN alarmasvehiculos av ON a.idalarma = av.idalarma
425 INNER JOIN vehiculos v ON av.idvehiculo = v.idvehiculo
426 INNER JOIN alarmasreglas ar ON a.idalarma = ar.idalarma
427 INNER JOIN "Procesos" p ON a.proceso = p.proceso
428 LEFT OUTER JOIN alarmas_usuarios au ON au.idalarma = a.idalarma
429 LEFT OUTER JOIN usuarios_tokens ut ON ut.usuario = au.usuario AND au.aviso_movil
430 LEFT OUTER JOIN alarmascontactos ac ON a.idalarma = ac.idalarma
431 LEFT OUTER JOIN contactos c ON ac.idcontacto = c.idcontacto,
432 (SELECT (max(dv.buzer) > 0) AS buzer, (max(dv.relay) > 0) AS relay
433 FROM (
434 SELECT
435 (CASE WHEN iddispositivo = '1' THEN 1 END) AS buzer,
436 (CASE WHEN iddispositivo = '2' THEN 1 END) AS relay
437 FROM modem_dispositivo_vehiculo
438 WHERE idvehiculo = vehicle_) dv) dev
439 WHERE a.estado = 'A' AND a.tipo = 'EVE' AND av.estado = 'A' AND v.estado = 'Y'
440 AND localtimestamp :: TIME WITHOUT TIME ZONE BETWEEN a.hhorainicial AND a.hhorafinal
441 AND av.idvehiculo = vehicle_ AND a.hdias ILIKE '%' || EXTRACT(DOW FROM localtimestamp) || '%'
442 AND (a.estadoruta IS NULL OR TRIM(a.estadoruta) = '' OR a.estadoruta = 'N' OR
443 (a.estadoruta = 'R' AND v.estadoruta = 'R'))
444 GROUP BY a.idalarma, a.mensaje, av.fecha_primera_activacion,
445 av.encen_indice_buzer, av.encen_buzer_primera_activacion, av.apag_indice_relay,
446 av.apag_relay_primera_activacion, av.envia_reset_indice, av.envia_reset_primera_activacion,
447 av.envia_reset_conteo_acciones, av.envia_reset_primera_accion, av.envia_cons_pasj_indice,
448 av.envia_cons_pasj_primera_activacion, av.envia_cons_conteo_acciones, av.envia_cons_primera_accion,
449 p.maneja_buzer, p.maneja_relay, p.manejasensorpasajeros, dev.buzer, dev.relay
450
451 UNION
452
453 SELECT
454 a.idalarma,
455 a.descripcion,
456 a.proceso,
457 (a.alerta_consola = 'Y') AS consola,
458 (a.alerta_email = 'Y') AS email,
459 (a.alerta_sms = 'Y') AS sms,
460 (a.alerta_movil = 'Y') AS movil,
461 (a.genera_accion AND
462 COALESCE(((a.acciones_dias IS NULL OR TRIM(a.acciones_dias) = '')
463 OR (
464 (localtimestamp :: TIME WITHOUT TIME ZONE BETWEEN a.acciones_hinicial AND a.acciones_hfinal)
465 AND (a.acciones_dias ILIKE '%' || EXTRACT(DOW FROM localtimestamp) || '%'))),
466 FALSE)) AS generar_accion,
467 a.encen_buzer,
468 a.encen_ocurr_buzer,
469 av.encen_indice_buzer,
470 a.encen_tiempo_buzer,
471 av.encen_buzer_primera_activacion,
472 a.apag_buzer,
473 a.apag_tiempo_buzer,
474 a.apag_relay,
475 a.apag_ocurr_relay,
476 a.apag_tiempo_relay,
477 av.apag_indice_relay,
478 av.apag_relay_primera_activacion,
479 a.envia_reset,
480 a.envia_reset_tiempo_ocurr,
481 av.envia_reset_indice,
482 a.envia_reset_cant,
483 av.envia_reset_primera_activacion,
484 a.envia_reset_limite_acciones,
485 av.envia_reset_conteo_acciones,
486 a.envia_reset_tiempo_acciones,
487 av.envia_reset_primera_accion,
488 a.envia_consulta_pasajeros,
489 a.envia_cons_pasj_tiempo_ocurr,
490 av.envia_cons_pasj_indice,
491 a.envia_cons_pasj_ocurr,
492 av.envia_cons_pasj_primera_activacion,
493 a.envia_cons_limite_acciones,
494 av.envia_cons_conteo_acciones,
495 a.envia_cons_tiempo_acciones,
496 av.envia_cons_primera_accion,
497 (p.maneja_buzer = 'S' AND dev.buzer) AS maneja_buzer,
498 (p.maneja_relay = 'S' AND dev.relay) AS maneja_relay,
499 (p.manejasensorpasajeros = 'S') AS maneja_sensor_pasajeros,
500 a.mensaje,
501 a.nombre,
502 a.ocurrencias_limite,
503 a.tiempo_ocurrencia AS tiempo,
504 ROUND(EXTRACT(EPOCH FROM (localtimestamp - av.fecha_primera_activacion)) / 60) AS minutos,
505 COALESCE(((a.notificaciondias IS NULL OR TRIM(a.notificaciondias) = '')
506 OR (
507 (localtimestamp :: TIME WITHOUT TIME ZONE BETWEEN notificacionhorainicial AND notificacionhorafinal)
508 AND (a.notificaciondias ILIKE '%' || EXTRACT(DOW FROM localtimestamp) || '%'))),
509 FALSE) AS notificar,
510 bool_and(CASE
511 WHEN ar.tipo = 'E'
512 THEN
513 CASE
514 WHEN event_ != 69 AND event_ BETWEEN 25 AND 76
515 THEN
516 COALESCE(
517 (
518 SELECT TRUE
519 FROM alarmas_regla_eventos
520 WHERE idalarma = ar.idalarma AND idregla = ar.idregla AND
521 evento = event_ :: CHARACTER VARYING AND indice = index_
522 ),
523 FALSE)
524 ELSE
525 COALESCE(
526 (
527 SELECT TRUE
528 FROM alarmas_regla_eventos
529 WHERE idalarma = ar.idalarma AND idregla = ar.idregla AND
530 evento = event_ :: CHARACTER VARYING
531 ),
532 FALSE)
533 END
534 WHEN ar.tipo = 'U' AND (latitude_ BETWEEN -90 AND 90) AND (longitude_ BETWEEN -200 AND 200)
535 THEN
536 CASE
537 WHEN TRIM(ar.tipo_geocerca) = 'AC'
538 THEN
539 COALESCE(
540 (
541 SELECT CASE WHEN ST_Within(
542 ST_GeomFromText(
543 'POINT(' || longitude_ || ' ' || latitude_ || ')',
544 4326), geom)
545 THEN
546 TRIM(ar.condicion) = 'IN'
547 ELSE
548 TRIM(ar.condicion) = 'OUT'
549 END
550 FROM areascontrol
551 WHERE idarea = COALESCE(ar.idgeocerca, 0) AND estado = 'A'
552 ),
553 FALSE)
554 WHEN TRIM(ar.tipo_geocerca) = 'PC'
555 THEN
556 COALESCE(
557 (
558 SELECT CASE WHEN ST_Distance(ST_Transform(ST_GeomFromText(
559 'POINT(' || longitud || ' ' ||
560 latitud
561 || ')', 4326), 2163),
562 ST_Transform(ST_GeomFromText(
563 'POINT(' || longitude_ || ' ' ||
564 latitude_ || ')', 4326), 2163)) <=
565 radio
566 THEN
567 TRIM(ar.condicion) = 'IN'
568 ELSE
569 TRIM(ar.condicion) = 'OUT'
570 END
571 FROM puntoscontrol
572 WHERE idpunto = COALESCE(ar.idpunto, 0) AND estado = 'A'
573 AND latitud IS NOT NULL AND longitud IS NOT NULL
574 AND latitud != '' AND longitud != ''
575 AND latitud::DOUBLE PRECISION BETWEEN -90 AND 90
576 AND longitud::DOUBLE PRECISION BETWEEN -200 AND 200
577 ),
578 FALSE)
579 WHEN TRIM(ar.tipo_geocerca) = 'PI'
580 THEN
581 COALESCE(
582 (
583 SELECT CASE WHEN ST_Distance(ST_Transform(ST_GeomFromText(
584 'POINT(' || longitud || ' ' ||
585 latitud
586 || ')', 4326), 2163),
587 ST_Transform(ST_GeomFromText(
588 'POINT(' || longitude_ || ' ' ||
589 latitude_ || ')', 4326), 2163)) <=
590 TRIM(ar.valor) :: NUMERIC
591 THEN
592 TRIM(ar.condicion) = 'IN'
593 ELSE
594 TRIM(ar.condicion) = 'OUT'
595 END
596 FROM puntosdeinteres
597 WHERE idpunto = COALESCE(ar.idpunto, 0) AND estado = 'A'
598 AND latitud IS NOT NULL AND longitud IS NOT NULL
599 AND latitud != '' AND longitud != ''
600 AND latitud::DOUBLE PRECISION BETWEEN -90 AND 90
601 AND longitud::DOUBLE PRECISION BETWEEN -200 AND 200
602 ),
603 FALSE)
604 WHEN TRIM(ar.tipo_geocerca) = 'R'
605 THEN
606 COALESCE(
607 (
608 SELECT CASE WHEN ST_Distance(ST_Transform(geom, 2163),
609 ST_Transform(ST_GeomFromText(
610 'POINT(' || longitude_ || ' ' ||
611 latitude_ || ')', 4326), 2163)) <=
612 TRIM(ar.valor) :: NUMERIC
613 THEN
614 TRIM(ar.condicion) = 'IN'
615 ELSE
616 TRIM(ar.condicion) = 'OUT'
617 END
618 FROM rutasgeograficas
619 WHERE idruta = COALESCE(ar.idgeocerca, 0) AND estado_ruta = 'A'
620 ),
621 FALSE)
622 WHEN TRIM(ar.tipo_geocerca) = 'RG'
623 THEN
624 COALESCE(
625 (
626 SELECT CASE WHEN ST_Distance(ST_Transform(rg.geom, 2163),
627 ST_Transform(ST_GeomFromText(
628 'POINT(' || longitude_ || ' ' ||
629 latitude_ || ')', 4326), 2163)) <=
630 TRIM(ar.valor) :: NUMERIC
631 THEN
632 TRIM(ar.condicion) = 'IN'
633 ELSE
634 TRIM(ar.condicion) = 'OUT'
635 END
636 FROM vehiculos v
637 INNER JOIN progvehiculos pg ON pg.idprogramacion = v.idprogactual
638 INNER JOIN rutas rt ON rt.idruta = pg.idruta
639 INNER JOIN rutasgeograficas rg ON rt.idrutageo = rg.idruta
640 WHERE rt.estado_ruta = 'A' AND v.idvehiculo = vehicle_
641 ),
642 FALSE)
643 ELSE
644 FALSE
645 END
646 WHEN ar.tipo = 'V'
647 THEN
648 CASE
649 WHEN TRIM(ar.variable) = 'V'
650 THEN
651 CASE WHEN ar.valor IS NULL OR TRIM(ar.valor) = ''
652 THEN
653 FALSE
654 ELSE
655 COALESCE(
656 (
657 CASE
658 WHEN TRIM(ar.condicion) = 'M'
659 THEN
660 valor :: NUMERIC(18, 2) > 0 AND speed_ > valor :: NUMERIC(18, 2)
661 WHEN TRIM(ar.condicion) = 'L'
662 THEN
663 valor :: NUMERIC(18, 2) > 1 AND speed_ < valor :: NUMERIC(18, 2)
664 WHEN TRIM(ar.condicion) = 'I'
665 THEN
666 split_part(valor, ';', 1) :: NUMERIC(18, 2) > 0 AND
667 split_part(valor, ';', 2) :: NUMERIC(18, 2) > 1 AND
668 speed_ BETWEEN split_part(valor, ';', 1) :: NUMERIC(18, 2)
669 AND split_part(valor, ';', 2) :: NUMERIC(18, 2)
670 ELSE
671 FALSE
672 END
673 ),
674 FALSE)
675 END
676 ELSE
677 FALSE
678 END
679 ELSE FALSE
680 END) AS ok,
681 array_to_json(
682 array_agg(array_to_json(ARRAY [ac.idcontacto :: CHARACTER VARYING, c.email, c.celp, ac.operador,
683 CASE WHEN ac.enviaemail
684 THEN 'TRUE'
685 ELSE 'FALSE' END,
686 CASE WHEN ac.enviasms
687 THEN 'TRUE'
688 ELSE 'FALSE' END]))) :: CHARACTER VARYING AS contactos,
689 array_to_json(array_agg(ut.token)) :: CHARACTER VARYING AS usuarios_tokens
690 FROM alarmas a
691 INNER JOIN alarmasvehiculos av ON a.idalarma = av.idalarma
692 INNER JOIN vehiculos v ON av.idvehiculo = v.idvehiculo
693 INNER JOIN alarmasreglas ar ON a.idalarma = ar.idalarma
694 INNER JOIN "Procesos" p ON a.proceso = p.proceso
695 LEFT OUTER JOIN alarmas_usuarios au ON au.idalarma = a.idalarma
696 LEFT OUTER JOIN usuarios_tokens ut ON ut.usuario = au.usuario AND au.aviso_movil
697 LEFT OUTER JOIN alarmascontactos ac ON a.idalarma = ac.idalarma
698 LEFT OUTER JOIN contactos c ON ac.idcontacto = c.idcontacto,
699 (SELECT (max(dv.buzer) > 0) AS buzer, (max(dv.relay) > 0) AS relay
700 FROM (
701 SELECT
702 (CASE WHEN iddispositivo = '1' THEN 1 END) AS buzer,
703 (CASE WHEN iddispositivo = '2' THEN 1 END) AS relay
704 FROM modem_dispositivo_vehiculo
705 WHERE idvehiculo = vehicle_) dv) dev
706 WHERE a.estado = 'A' AND a.tipo = 'EVE' AND av.estado = 'A' AND v.estado = 'Y'
707 AND localtimestamp :: TIME WITHOUT TIME ZONE BETWEEN a.hhorainicial AND a.hhorafinal
708 AND av.idvehiculo = vehicle_ AND (a.hdias IS NULL OR TRIM(a.hdias) = '')
709 AND (a.estadoruta IS NULL OR TRIM(a.estadoruta) = '' OR a.estadoruta = 'N' OR
710 (a.estadoruta = 'R' AND v.estadoruta = 'R'))
711 GROUP BY a.idalarma, a.mensaje, av.fecha_primera_activacion,
712 av.encen_indice_buzer, av.encen_buzer_primera_activacion, av.apag_indice_relay,
713 av.apag_relay_primera_activacion, av.envia_reset_indice, av.envia_reset_primera_activacion,
714 av.envia_reset_conteo_acciones, av.envia_reset_primera_accion, av.envia_cons_pasj_indice,
715 av.envia_cons_pasj_primera_activacion, av.envia_cons_conteo_acciones, av.envia_cons_primera_accion,
716 p.maneja_buzer, p.maneja_relay, p.manejasensorpasajeros, dev.buzer, dev.relay
717 ) x
718 WHERE x.ok
719
720
721 ) LOOP
722
723 alarm_ = alarms_.idalarma;
724 name_ = alarms_.nombre;
725
726 RAISE NOTICE 'ALARMA: %', alarm_;
727
728 IF alarms_.resetear
729 THEN
730
731 UPDATE alarmasocurrencias
732 SET ocurrencias = 0
733 WHERE idvehiculo = vehicle_ AND idregla IN (
734 SELECT idregla
735 FROM alarmasreglas
736 WHERE idalarma = alarm_
737 );
738
739 UPDATE alarmasvehiculos
740 SET fecha_primera_activacion = localtimestamp
741 WHERE idvehiculo = vehicle_ AND idalarma = alarm_;
742 END IF;
743
744 INSERT INTO alarmasocurrencias (idvehiculo, idregla, ocurrencias)
745 SELECT
746 vehicle_,
747 idregla,
748 0
749 FROM alarmasreglas
750 WHERE idalarma = alarm_ AND idregla NOT IN (
751 SELECT idregla
752 FROM alarmasocurrencias
753 WHERE idvehiculo = vehicle_
754 );
755
756 UPDATE alarmasocurrencias
757 SET ocurrencias = ocurrencias + 1
758 WHERE idvehiculo = vehicle_ AND idregla IN (
759 SELECT idregla
760 FROM alarmasreglas
761 WHERE idalarma = alarm_
762 );
763
764 SELECT bool_and(ao.ocurrencias >= alarms_.ocurrencias_limite)
765 INTO OK_
766 FROM alarmasocurrencias ao
767 INNER JOIN alarmasreglas ar
768 ON ao.idregla = ar.idregla
769 WHERE ao.idvehiculo = vehicle_ AND ar.idalarma = alarm_;
770
771 RAISE NOTICE 'OK %', OK_;
772
773 IF OK_
774 THEN
775 SELECT
776 direccion,
777 municipio,
778 departamento
779 INTO address_, municipality_, department_
780 FROM getdireccion(latitude_, longitude_);
781
782 BEGIN
783
784 alarm_date := COALESCE(date_, localtimestamp);
785
786 INSERT INTO alarmashistorico (idalarma, idvehiculo, fecha, latitud, longitud, evento, mensaje, velocidad,
787 direccion, municipio, departamento, idconductor, idruta_flota, idzona_control,
788 tipo_zona_control, area_regla, latitud_regla, longitud_regla, radio_regla)
789 (SELECT
790 alarm_,
791 vehicle_,
792 alarm_date,
793 latitude_,
794 longitude_,
795 event_ :: TEXT,
796 alarms_.mensaje,
797 speed_,
798 address_,
799 municipality_,
800 department_,
801 COALESCE(idconductor_actual, idconductor),
802 CASE WHEN estadoruta = 'R'
803 THEN (CASE WHEN ar.tipo = 'U' AND TRIM(ar.tipo_geocerca) = 'RG'
804 THEN
805 (SELECT rg.idruta
806 FROM vehiculos v INNER JOIN progvehiculos pg ON (pg.idprogramacion = v.idprogactual)
807 INNER JOIN rutas rt ON (rt.idruta = pg.idruta)
808 INNER JOIN rutasgeograficas rg ON (rt.idrutageo = rg.idruta)
809 WHERE rt.estado_ruta = 'A' AND v.idvehiculo = vehicle_)
810 ELSE v.idruta END)
811 ELSE NULL END,
812 COALESCE(ar.idgeocerca, ar.idpunto),
813 tipo_geocerca,
814 CASE WHEN ar.tipo = 'U' AND (TRIM(ar.tipo_geocerca) = 'RG' OR TRIM(ar.tipo_geocerca) = 'R')
815 THEN rg.geom
816 ELSE ac.geom END,
817 COALESCE(pc.latitud, pi.latitud) :: DOUBLE PRECISION,
818 COALESCE(pc.longitud, pi.longitud) :: DOUBLE PRECISION,
819 COALESCE(pc.radio, 0)
820 FROM vehiculos v
821 INNER JOIN alarmasvehiculos av ON v.idvehiculo = av.idvehiculo
822 LEFT OUTER JOIN alarmasreglas ar ON av.idalarma = ar.idalarma AND ar.tipo = 'U'
823 LEFT OUTER JOIN areascontrol ac ON ar.idgeocerca = ac.idarea
824 LEFT OUTER JOIN puntoscontrol pc ON ar.idpunto = pc.idpunto
825 LEFT OUTER JOIN puntosdeinteres pi ON ar.idpunto = pi.idpunto
826 LEFT OUTER JOIN rutasgeograficas rg ON ar.idgeocerca = rg.idruta
827 WHERE v.idvehiculo = vehicle_ AND av.idalarma = alarm_
828 LIMIT 1)
829 RETURNING idconductor, latitud_regla, longitud_regla, radio_regla, idzona_control, tipo_zona_control, area_regla
830 INTO driver_, latitude_rule_, longitude_rule_, rule_radius_, zone_control_id_, zone_type_, alarm_area;
831
832 UPDATE alarmasocurrencias
833 SET ocurrencias = 0
834 WHERE idvehiculo = vehicle_ AND idregla IN (
835 SELECT idregla
836 FROM alarmasreglas
837 WHERE idalarma = alarm_
838 );
839
840 inactivate_ = FALSE;
841 alarm_count_ = 0;
842
843 IF inactivate_time_ > 0 AND inactivate_count_ > 0
844 THEN
845 SELECT COUNT(*)
846 INTO alarm_count_
847 FROM alarmashistorico
848 WHERE idalarma = alarm_ AND idvehiculo = vehicle_
849 AND fecha BETWEEN (LOCALTIMESTAMP - (inactivate_time_ ||
850 ' minutes') :: INTERVAL) :: TIMESTAMP WITHOUT TIME ZONE AND LOCALTIMESTAMP;
851
852 inactivate_ = alarm_count_ >= inactivate_count_;
853
854 IF inactivate_
855 THEN
856 UPDATE alarmas
857 SET estado = 'I'
858 WHERE idalarma = alarm_;
859 END IF;
860 END IF;
861
862 IF (alarms_.generar_accion)
863 THEN
864
865 --- Acciones generadas
866
867 IF (alarms_.encen_buzer AND alarms_.maneja_buzer)
868 THEN
869
870 actions_occurrences_ = 0;
871
872 IF (alarms_.encen_buzer_primera_activacion IS NULL OR (ROUND(EXTRACT(EPOCH FROM (alarm_date - alarms_.encen_buzer_primera_activacion)) / 60) > alarms_.encen_tiempo_buzer)
873 OR (coalesce(alarms_.encen_indice_buzer, 0) >= alarms_.encen_ocurr_buzer))
874 THEN
875
876 UPDATE alarmasvehiculos SET encen_buzer_primera_activacion = localtimestamp, encen_indice_buzer = 1
877 WHERE idalarma = alarm_ AND idvehiculo = vehicle_
878 RETURNING encen_indice_buzer INTO actions_occurrences_;
879
880 ELSE
881
882 UPDATE alarmasvehiculos SET encen_indice_buzer = coalesce(encen_indice_buzer, 0) + 1
883 WHERE idalarma = alarm_ AND idvehiculo = vehicle_
884 RETURNING encen_indice_buzer INTO actions_occurrences_;
885
886 END IF;
887
888 IF (actions_occurrences_ = alarms_.encen_ocurr_buzer)
889 THEN
890
891 INSERT INTO comandos_programados (idvehiculo, iddispositivo, proceso, usuario, idestado, medio_envio, maximo_intentos, idtipocomando)
892 VALUES (vehicle_, '1', alarms_.proceso, 'sistema', (SELECT idestado FROM comandos_estado WHERE estado = 'P'), (SELECT id FROM tipos_medio WHERE codigo = 'COR_ALAR'), 2, '1')
893 RETURNING idcomando INTO buzer_on_command_id_;
894
895 IF (alarms_.apag_buzer)
896 THEN
897
898 INSERT INTO comandos_programados (idvehiculo, iddispositivo, proceso, usuario, idestado, medio_envio, maximo_intentos, idtipocomando, programado, minutos_programacion, idcomando_asociado)
899 VALUES (vehicle_, '1', alarms_.proceso, 'sistema', (SELECT idestado FROM comandos_estado WHERE estado = 'B'), (SELECT id FROM tipos_medio WHERE codigo = 'COR_ALAR'), 2, '2', true, alarms_.apag_tiempo_buzer, buzer_on_command_id_);
900
901 END IF;
902
903 END IF;
904
905 --END IF;
906
907 END IF;
908
909 IF (alarms_.apag_relay AND alarms_.maneja_relay)
910 THEN
911
912 actions_occurrences_ = 0;
913
914 /*IF (((ROUND(EXTRACT(EPOCH FROM (alarm_date - coalesce(alarms_.apag_relay_primera_activacion, localtimestamp))) / 60) <= alarms_.apag_tiempo_relay)
915 AND (coalesce(alarms_.apag_indice_relay, 0) < alarms_.apag_ocurr_relay))
916 OR (ROUND(EXTRACT(EPOCH FROM (alarm_date - alarms_.apag_relay_primera_activacion)) / 60) > alarms_.apag_tiempo_relay))
917 THEN*/
918
919 IF (alarms_.apag_relay_primera_activacion IS NULL OR (ROUND(EXTRACT(EPOCH FROM (alarm_date - alarms_.apag_relay_primera_activacion)) / 60) > alarms_.apag_tiempo_relay)
920 OR (coalesce(alarms_.apag_indice_relay, 0) >= alarms_.apag_ocurr_relay))
921 THEN
922
923 UPDATE alarmasvehiculos SET apag_relay_primera_activacion = localtimestamp, apag_indice_relay = 1
924 WHERE idalarma = alarm_ AND idvehiculo = vehicle_
925 RETURNING apag_indice_relay INTO actions_occurrences_;
926
927 ELSE
928
929 UPDATE alarmasvehiculos SET apag_indice_relay = coalesce(apag_indice_relay, 0) + 1
930 WHERE idalarma = alarm_ AND idvehiculo = vehicle_
931 RETURNING apag_indice_relay INTO actions_occurrences_;
932
933 END IF;
934
935 IF (actions_occurrences_ = alarms_.apag_ocurr_relay)
936 THEN
937
938 SELECT send_relay_vehicle_command(vehicle_, '2', alarms_.proceso, 'sistema', 'P', 'COR_ALAR', 2, '2', '1', 'CP')
939 INTO relay_off_command_id_;
940
941 END IF;
942
943 --END IF;
944
945 END IF;
946
947 IF (alarms_.envia_reset)
948 THEN
949
950 actions_occurrences_ = 0;
951
952 vehicle_gps_ = exists(SELECT * FROM modem_comando
953 WHERE tipo_modem = events_.tipo_modem AND idtipocomando = '3' AND iddispositivo = '3');
954
955 vehicle_gsm_ = exists(SELECT * FROM modem_comando
956 WHERE tipo_modem = events_.tipo_modem AND idtipocomando = '3' AND iddispositivo = '4');
957
958 IF (vehicle_gps_ OR vehicle_gsm_) THEN
959
960 IF (((ROUND(EXTRACT(EPOCH FROM (alarm_date - coalesce(alarms_.envia_reset_primera_accion, localtimestamp))) / 60) <= alarms_.envia_reset_tiempo_acciones)
961 AND (coalesce(alarms_.envia_reset_conteo_acciones, 0) < alarms_.envia_reset_limite_acciones))
962 OR (ROUND(EXTRACT(EPOCH FROM (alarm_date - alarms_.envia_reset_primera_accion)) / 60) > alarms_.envia_reset_tiempo_acciones))
963 THEN
964
965 IF (alarms_.envia_reset_primera_activacion IS NULL OR (ROUND(EXTRACT(EPOCH FROM (alarm_date - alarms_.envia_reset_primera_activacion)) / 60) > alarms_.envia_reset_tiempo_ocurr)
966 OR (coalesce(alarms_.envia_reset_indice, 0) >= alarms_.envia_reset_cant))
967 THEN
968
969 UPDATE alarmasvehiculos SET envia_reset_primera_activacion = localtimestamp, envia_reset_indice = 1
970 WHERE idalarma = alarm_ AND idvehiculo = vehicle_
971 RETURNING envia_reset_indice INTO actions_occurrences_;
972
973 ELSE
974
975 UPDATE alarmasvehiculos SET envia_reset_indice = coalesce(envia_reset_indice, 0) + 1
976 WHERE idalarma = alarm_ AND idvehiculo = vehicle_
977 RETURNING envia_reset_indice INTO actions_occurrences_;
978
979 END IF;
980
981 IF (actions_occurrences_ = alarms_.envia_reset_cant) THEN
982
983 IF (alarms_.envia_reset_primera_accion IS NULL OR (ROUND(EXTRACT(EPOCH FROM (alarm_date - alarms_.envia_reset_primera_accion)) / 60) > alarms_.envia_reset_tiempo_acciones)) THEN
984
985 UPDATE alarmasvehiculos SET envia_reset_primera_accion = localtimestamp, envia_reset_conteo_acciones = 1
986 WHERE idalarma = alarm_ AND idvehiculo = vehicle_;
987
988 ELSE
989
990 UPDATE alarmasvehiculos SET envia_reset_conteo_acciones = coalesce(envia_reset_conteo_acciones, 0) + 1
991 WHERE idalarma = alarm_ AND idvehiculo = vehicle_;
992
993 END IF;
994
995 IF vehicle_gps_ THEN
996
997 INSERT INTO comandos_programados (idvehiculo, iddispositivo, proceso, usuario, idestado, medio_envio, maximo_intentos, idtipocomando)
998 VALUES (vehicle_, '3', alarms_.proceso, 'sistema', (SELECT idestado FROM comandos_estado WHERE estado = 'P'), (SELECT id FROM tipos_medio WHERE codigo = 'COR_ALAR'), 2, '3');
999
1000 END IF;
1001
1002 IF vehicle_gsm_ THEN
1003
1004 INSERT INTO comandos_programados (idvehiculo, iddispositivo, proceso, usuario, idestado, medio_envio, maximo_intentos, idtipocomando)
1005 VALUES(vehicle_, '4', alarms_.proceso, 'sistema', (SELECT idestado FROM comandos_estado WHERE estado = 'P'), (SELECT id FROM tipos_medio WHERE codigo = 'COR_ALAR'), 2, '3');
1006
1007 END IF;
1008
1009 END IF;
1010
1011 END IF;
1012
1013 END IF;
1014
1015
1016 END IF;
1017
1018 IF (alarms_.envia_consulta_pasajeros AND alarms_.maneja_sensor_pasajeros)
1019 THEN
1020
1021 actions_occurrences_ = 0;
1022
1023 PERFORM * FROM sensoresxvehiculo
1024 WHERE idvehiculo = vehicle_ AND sensor IN ('INS50','INS50_HEXA','INS50_PER');
1025
1026 IF FOUND THEN
1027
1028 IF (((ROUND(EXTRACT(EPOCH FROM (alarm_date - coalesce(alarms_.envia_cons_primera_accion, localtimestamp))) / 60) <= alarms_.envia_cons_tiempo_acciones)
1029 AND (coalesce(alarms_.envia_cons_conteo_acciones, 0) < alarms_.envia_cons_limite_acciones))
1030 OR (ROUND(EXTRACT(EPOCH FROM (alarm_date - alarms_.envia_cons_primera_accion)) / 60) > alarms_.envia_cons_tiempo_acciones))
1031 THEN
1032
1033 IF (alarms_.envia_cons_pasj_primera_activacion IS NULL OR (ROUND(EXTRACT(EPOCH FROM (alarm_date - alarms_.envia_cons_pasj_primera_activacion)) / 60) > alarms_.envia_cons_pasj_tiempo_ocurr)
1034 OR (coalesce(alarms_.envia_cons_pasj_indice, 0) >= alarms_.envia_cons_pasj_ocurr))
1035 THEN
1036
1037 UPDATE alarmasvehiculos SET envia_cons_pasj_primera_activacion = localtimestamp, envia_cons_pasj_indice = 1
1038 WHERE idalarma = alarm_ AND idvehiculo = vehicle_
1039 RETURNING envia_cons_pasj_indice INTO actions_occurrences_;
1040
1041 ELSE
1042
1043 UPDATE alarmasvehiculos SET envia_cons_pasj_indice = coalesce(envia_cons_pasj_indice, 0) + 1
1044 WHERE idalarma = alarm_ AND idvehiculo = vehicle_
1045 RETURNING envia_cons_pasj_indice INTO actions_occurrences_;
1046
1047 END IF;
1048
1049 RAISE NOTICE 'Ocurrencias acciones: % - ', actions_occurrences_;
1050
1051 IF (actions_occurrences_ = alarms_.envia_cons_pasj_ocurr) THEN
1052
1053 IF (alarms_.envia_cons_primera_accion IS NULL OR (ROUND(EXTRACT(EPOCH FROM (alarm_date - alarms_.envia_cons_primera_accion)) / 60) > alarms_.envia_cons_tiempo_acciones)) THEN
1054
1055 UPDATE alarmasvehiculos SET envia_cons_primera_accion = localtimestamp, envia_cons_conteo_acciones = 1
1056 WHERE idalarma = alarm_ AND idvehiculo = vehicle_;
1057
1058 ELSE
1059
1060 UPDATE alarmasvehiculos SET envia_cons_conteo_acciones = coalesce(envia_cons_conteo_acciones, 0) + 1
1061 WHERE idalarma = alarm_ AND idvehiculo = vehicle_;
1062
1063 END IF;
1064
1065 RAISE NOTICE 'Comando generado: % - ', actions_occurrences_;
1066
1067 INSERT INTO comandos_programados (idvehiculo, iddispositivo, proceso, usuario, idestado, medio_envio, maximo_intentos, idtipocomando)
1068 VALUES(vehicle_, '5', alarms_.proceso, 'sistema', (SELECT idestado FROM comandos_estado WHERE estado = 'P'), (SELECT id FROM tipos_medio WHERE codigo = 'COR_ALAR'), 2, '6');
1069
1070 END IF;
1071
1072 END IF;
1073
1074 END IF;
1075
1076 END IF;
1077
1078 END IF;
1079
1080 IF alarms_.notificar OR inactivate_
1081 THEN
1082
1083 row_.idconductor = driver_;
1084 row_.idalarma = alarm_;
1085 row_.id_zona_control = zone_control_id_;
1086 row_.consola = alarms_.consola;
1087 row_.email = alarms_.email;
1088 row_.desactivada = inactivate_;
1089 row_.notificar = alarms_.notificar;
1090 row_.sms = alarms_.sms;
1091 row_.movil = alarms_.movil;
1092 row_.latitud = latitude_;
1093 row_.longitud = longitude_;
1094 row_.velocidad = speed_;
1095 row_.radio_regla = rule_radius_;
1096 row_.ocurrencias = alarm_count_;
1097 row_.contactos = alarms_.contactos;
1098 row_.departamento = department_;
1099 row_.descripcion = alarms_.descripcion;
1100 row_.direccion = address_;
1101 row_.evento = events_.nombre_evento;
1102 row_.idvehiculo = vehicle_;
1103 row_.latitud_regla = latitude_rule_;
1104 row_.longitud_regla = longitude_rule_;
1105 row_.municipio = municipality_;
1106 row_.nombre = name_;
1107 row_.numerointerno = events_.numinterno;
1108 row_.placa = events_.placa;
1109 row_.proceso = alarms_.proceso;
1110 row_.tipo_zona = zone_type_;
1111 row_.usuarios_tokens = alarms_.usuarios_tokens;
1112 row_.fecha = alarm_date;
1113 row_.geometry_type = lower(GeometryType(alarm_area));
1114
1115 RETURN NEXT row_;
1116 END IF;
1117
1118 EXCEPTION
1119 WHEN duplicate_column
1120 THEN RAISE NOTICE 'row already exists';
1121 WHEN OTHERS
1122 THEN RAISE NOTICE 'Another exception % - %', SQLERRM, SQLSTATE;
1123 END;
1124 END IF;
1125 END LOOP;
1126 END LOOP;
1127
1128 UPDATE insert_events
1129 SET estado = 'P'
1130 WHERE estado = 'X';
1131
1132 RETURN;
1133END
1134$BODY$
1135 LANGUAGE plpgsql VOLATILE
1136 COST 100
1137 ROWS 1000;
1138ALTER FUNCTION process_alarms(integer, integer, integer)
1139 OWNER TO postgres;
1140
1141CREATE OR REPLACE FUNCTION process_monitoring_alarms()
1142 RETURNS SETOF monitoring_alarm AS
1143$BODY$
1144DECLARE
1145
1146 generate_alarms_ monitoring_alarm;
1147 alarms_ record;
1148 ocurrencias_actual bigint;
1149
1150 actions_occurrences_ integer;
1151
1152 vehicle_gps_ boolean;
1153 vehicle_gsm_ boolean;
1154
1155BEGIN
1156
1157 FOR alarms_ IN (
1158
1159 SELECT
1160 a.idalarma,
1161 a.descripcion,
1162 a.proceso,
1163 a.ocurrencias_limite,
1164 (CASE WHEN a.alerta_consola = 'Y'
1165 THEN TRUE
1166 ELSE FALSE END) AS consola,
1167 (CASE WHEN a.alerta_sms = 'Y'
1168 THEN TRUE
1169 ELSE FALSE END) AS sms,
1170 (CASE WHEN a.alerta_email = 'Y'
1171 THEN TRUE
1172 ELSE FALSE END) AS email,
1173 (CASE WHEN a.alerta_movil = 'Y'
1174 THEN TRUE
1175 ELSE FALSE END) AS movil,
1176 (a.genera_accion AND
1177 COALESCE(((a.acciones_dias IS NULL OR TRIM(a.acciones_dias) = '')
1178 OR (
1179 (localtimestamp :: TIME WITHOUT TIME ZONE BETWEEN a.acciones_hinicial AND a.acciones_hfinal)
1180 AND (a.acciones_dias ILIKE '%' || EXTRACT(DOW FROM localtimestamp) || '%'))),
1181 FALSE)) AS generar_accion,
1182 a.envia_reset,
1183 a.envia_reset_tiempo_ocurr,
1184 av.envia_reset_indice,
1185 a.envia_reset_cant,
1186 av.envia_reset_primera_activacion,
1187 a.envia_reset_limite_acciones,
1188 av.envia_reset_conteo_acciones,
1189 a.envia_reset_tiempo_acciones,
1190 av.envia_reset_primera_accion,
1191 a.envia_consulta_pasajeros,
1192 a.envia_cons_pasj_tiempo_ocurr,
1193 av.envia_cons_pasj_indice,
1194 a.envia_cons_pasj_ocurr,
1195 av.envia_cons_pasj_primera_activacion,
1196 a.envia_cons_limite_acciones,
1197 av.envia_cons_conteo_acciones,
1198 a.envia_cons_tiempo_acciones,
1199 av.envia_cons_primera_accion,
1200 (p.maneja_buzer = 'S' AND dev.buzer) AS maneja_buzer,
1201 (p.maneja_relay = 'S' AND dev.relay) AS maneja_relay,
1202 (p.manejasensorpasajeros = 'S') AS maneja_sensor_pasajeros,
1203 (SELECT bool_and(r.regla)
1204 FROM (
1205 SELECT (
1206 CASE WHEN ar.tipo = 'G'
1207 THEN (
1208 COALESCE(
1209 (
1210 (LOCALTIMESTAMP - (ar.tiempo || (CASE WHEN ar.unidad_tiempo = 'H' THEN ' hours' ELSE ' minutes' END)) :: INTERVAL) BETWEEN v.ultimaactualizacion AND LOCALTIMESTAMP
1211 ), TRUE
1212 )
1213 )
1214 ELSE (
1215 COALESCE(
1216 (
1217 (LOCALTIMESTAMP - (ar.tiempo || (CASE WHEN ar.unidad_tiempo = 'H' THEN ' hours' ELSE ' minutes' END)) :: INTERVAL) BETWEEN v.fecha_ultimo_conteo AND LOCALTIMESTAMP
1218 ), TRUE
1219 )
1220 ) END) AS regla) r
1221 ) AS regla,
1222 ((a.alerta_consola = 'Y' OR a.alerta_email = 'Y' OR a.alerta_sms = 'Y' OR a.alerta_movil = 'Y') AND
1223 COALESCE(((a.notificaciondias IS NULL OR TRIM(a.notificaciondias) = '') OR (
1224 (localtimestamp :: TIME WITHOUT TIME ZONE BETWEEN notificacionhorainicial AND notificacionhorafinal)
1225 AND (a.notificaciondias ILIKE '%' || EXTRACT(DOW FROM localtimestamp) || '%'))), FALSE)) AS notifica,
1226 (
1227 (
1228 CASE WHEN tiempo_ocurrencia > 0
1229 THEN ROUND(EXTRACT(EPOCH FROM (localtimestamp - av.fecha_primera_activacion)) / 60) >= tiempo_ocurrencia
1230 ELSE
1231 FALSE
1232 END
1233 )
1234 ) AS resetear,
1235 v.idvehiculo,
1236 v.placa,
1237 v.numinterno,
1238 v.tipo_modem,
1239 TRIM(REPLACE(REPLACE(REPLACE(v.latitud, 'S', '-'), 'N', ''), ' ', '')) AS latitud,
1240 TRIM(REPLACE(REPLACE(REPLACE(v.longitud, 'W', '-'), 'E', ''), ' ', '')) AS longitud,
1241 COALESCE(v.direccion, 'No disponible') AS direccion,
1242 COALESCE(v.municipio, 'No disponible') AS municipio,
1243 COALESCE(v.departamento, 'No disponible') AS departamento,
1244 v.velocidad,
1245 COALESCE(v.idconductor_actual, v.idconductor) AS idconductor,
1246 v.ultimaactualizacion,
1247 v.modem_min AS numero,
1248 localtimestamp AS fecha,
1249 v.fecha_ultimo_conteo,
1250 COALESCE(CASE WHEN p.manejaflotas = 'S' OR p.transporteespecial = 'S'
1251 THEN (SELECT coalesce(co.nombre, 'Sin conductor')
1252 FROM conductores co
1253 WHERE co.idconductor = v.idconductor_actual)
1254 ELSE CASE WHEN v.idconductor_actual IS NOT NULL
1255 THEN (SELECT coalesce(co.nombre, 'Sin conductor')
1256 FROM conductores co
1257 WHERE co.idconductor = v.idconductor_actual)
1258 ELSE (SELECT coalesce(co.nombre, 'Sin conductor')
1259 FROM conductores co
1260 WHERE co.idconductor = v.idconductor) END END, 'Sin conductor') AS nombre_conductor,
1261 (SELECT array_to_json(array_agg(ut.token))
1262 FROM alarmas_usuarios au
1263 LEFT JOIN usuarios_tokens ut ON (ut.usuario = au.usuario AND au.aviso_movil)
1264 WHERE au.idalarma = a.idalarma) AS usuarios_tokens,
1265 array_to_json(
1266 array_agg(array_to_json(ARRAY [ac.idcontacto :: CHARACTER VARYING, ct.email, ct.celp, ac.operador,
1267 CASE WHEN ac.enviaemail
1268 THEN 'TRUE'
1269 ELSE 'FALSE' END,
1270 CASE WHEN ac.enviasms
1271 THEN 'TRUE'
1272 ELSE 'FALSE' END]))) AS contactos,
1273 (SELECT row_to_json(veh)::text FROM (SELECT v.idvehiculo, v.placa, v.numinterno AS numerointerno, v.estado_envio AS estado_envio_bloqueo, (MAX(veh_dip.estado_buzer) IS NOT NULL AND p.maneja_buzer = 'S') AS maneja_buzer,
1274 (MAX(veh_dip.estado_relay) IS NOT NULL AND p.maneja_relay = 'S') AS maneja_relay, (MAX(veh_dip.estado_buzer) = 'ON') AS estado_buzer,
1275 MAX(veh_dip.estado_relay) AS estado_relay, p.proceso
1276 FROM (
1277 SELECT (CASE WHEN iddispositivo = '1'
1278 THEN estado END) AS estado_buzer,
1279 (CASE WHEN iddispositivo = '2'
1280 THEN estado END) AS estado_relay
1281 FROM modem_dispositivo_vehiculo
1282 WHERE idvehiculo = v.idvehiculo
1283 ) veh_dip
1284 ) veh) AS vehiculo,
1285 (SELECT row_to_json(proc)::text FROM (SELECT p.proceso, p.nombre, p.manejainterno) proc) AS procesobj
1286 ,an.notificaciones, a.limite_notificaciones
1287 FROM alarmas a
1288 INNER JOIN alarmasreglas ar ON (ar.idalarma = a.idalarma)
1289 INNER JOIN alarmasvehiculos av ON (a.idalarma = av.idalarma)
1290 INNER JOIN alarmas_notificaciones an ON (an.idregla = ar.idregla AND an.idvehiculo = av.idvehiculo)
1291 INNER JOIN vehiculos v ON (av.idvehiculo = v.idvehiculo)
1292 INNER JOIN "Procesos" p ON (p.proceso = a.proceso)
1293 LEFT JOIN alarmascontactos ac ON (a.idalarma = ac.idalarma)
1294 LEFT JOIN contactos ct ON (ct.idcontacto = ac.idcontacto)
1295 LEFT JOIN progvehiculos pg ON (pg.idprogramacion = v.idprogactual)
1296 LEFT JOIN sensoresxvehiculo sv ON (v.idvehiculo = sv.idvehiculo)
1297 LEFT JOIN sensores s ON (s.sensor = sv.sensor)
1298 LEFT JOIN
1299 (SELECT dv.idvehiculo, (max(dv.buzer) > 0) AS buzer, (max(dv.relay) > 0) AS relay
1300 FROM (
1301 SELECT idvehiculo,
1302 (CASE WHEN iddispositivo = '1' THEN 1 END) AS buzer,
1303 (CASE WHEN iddispositivo = '2' THEN 1 END) AS relay
1304 FROM modem_dispositivo_vehiculo) dv
1305 GROUP BY dv.idvehiculo) dev ON (dev.idvehiculo = v.idvehiculo)
1306 WHERE a.estado = 'A' AND a.tipo = 'MON' AND av.estado = 'A' AND v.estado = 'Y' AND
1307 localtimestamp :: TIME WITHOUT TIME ZONE BETWEEN a.hhorainicial AND a.hhorafinal AND
1308 ((a.hdias ILIKE '%' || EXTRACT(DOW FROM localtimestamp) || '%')
1309 OR ((a.hdias IS NULL OR TRIM(a.hdias) = ''))) AND
1310 (a.estadoruta IS NULL OR TRIM(a.estadoruta) = '' OR a.estadoruta = 'N' OR
1311 (a.estadoruta = 'R' AND v.estadoruta = 'R' AND pg.fechasalida :: DATE = localtimestamp :: DATE)) AND
1312 (s.sensor IS NULL OR btrim(s.sensor) = '' OR ar.tipo = 'G' OR
1313 (ar.tipo = 'S' AND s.sensor IN ('INS50', 'INS50_HEXA', 'INS50_PER')))
1314 GROUP BY v.idvehiculo, a.idalarma, ar.idregla, ar.tipo, ar.tiempo, ar.unidad_tiempo, p.manejaflotas,
1315 p.transporteespecial, p.maneja_buzer, p.maneja_relay, p.proceso, an.notificaciones, av.fecha_primera_activacion,
1316 av.envia_reset_indice, av.envia_reset_primera_activacion, av.envia_reset_conteo_acciones, av.envia_reset_primera_accion, av.envia_cons_pasj_indice,
1317 av.envia_cons_pasj_primera_activacion, av.envia_cons_conteo_acciones, av.envia_cons_primera_accion,
1318 dev.buzer, dev.relay
1319
1320 ) LOOP
1321
1322 IF (alarms_.regla)
1323 THEN
1324
1325 IF (alarms_.notificaciones < alarms_.limite_notificaciones)
1326 THEN
1327
1328 ocurrencias_actual = 0;
1329
1330 IF (alarms_.resetear)
1331 THEN
1332
1333 UPDATE alarmasocurrencias SET ocurrencias = 1
1334 WHERE idvehiculo = alarms_.idvehiculo AND ocurrencias < alarms_.ocurrencias_limite
1335 AND idregla IN (
1336 SELECT idregla
1337 FROM alarmasreglas
1338 WHERE idalarma = alarms_.idalarma AND (tipo = 'G' OR tipo = 'S')
1339 )
1340 RETURNING ocurrencias INTO ocurrencias_actual;
1341
1342 UPDATE alarmasvehiculos
1343 SET fecha_primera_activacion = localtimestamp
1344 WHERE idvehiculo = alarms_.idvehiculo AND idalarma = alarms_.idalarma;
1345
1346 ELSE
1347
1348 UPDATE alarmasocurrencias SET ocurrencias = ocurrencias + 1
1349 WHERE idvehiculo = alarms_.idvehiculo AND ocurrencias < alarms_.ocurrencias_limite
1350 AND idregla IN (
1351 SELECT idregla
1352 FROM alarmasreglas
1353 WHERE idalarma = alarms_.idalarma AND (tipo = 'G' OR tipo = 'S')
1354 )
1355 RETURNING ocurrencias INTO ocurrencias_actual;
1356
1357 END IF;
1358
1359 IF (ocurrencias_actual >= alarms_.ocurrencias_limite)
1360 THEN
1361
1362 -- Registrar alarma historico
1363 -- Resetear ocurrencias
1364 -- Contar notificaciones
1365
1366 INSERT INTO alarmashistorico (idalarma, idvehiculo, fecha, latitud, longitud, direccion, velocidad, municipio, departamento, idconductor)
1367 VALUES (alarms_.idalarma, alarms_.idvehiculo, localtimestamp, alarms_.latitud, alarms_.longitud, alarms_.direccion, alarms_.velocidad, alarms_.municipio, alarms_.departamento, alarms_.idconductor);
1368
1369 UPDATE alarmasocurrencias SET ocurrencias = 0
1370 WHERE idvehiculo = alarms_.idvehiculo AND idregla IN
1371 (
1372 SELECT idregla
1373 FROM alarmasreglas
1374 WHERE idalarma = alarms_.idalarma AND (tipo = 'G' OR tipo = 'S')
1375 );
1376
1377 UPDATE alarmas_notificaciones SET notificaciones = notificaciones + 1
1378 WHERE idvehiculo = alarms_.idvehiculo AND idregla IN
1379 (
1380 SELECT idregla
1381 FROM alarmasreglas
1382 WHERE idalarma = alarms_.idalarma AND (tipo = 'G' OR tipo = 'S')
1383 );
1384
1385 RAISE NOTICE 'Genera accion - %', alarms_.generar_accion;
1386
1387 IF (alarms_.generar_accion)
1388 THEN
1389
1390 IF (alarms_.envia_reset)
1391 THEN
1392
1393 actions_occurrences_ = 0;
1394
1395 RAISE NOTICE 'Genera RESET - %', alarms_.envia_reset;
1396
1397 vehicle_gps_ = exists(SELECT * FROM modem_comando
1398 WHERE tipo_modem = alarms_.tipo_modem AND idtipocomando = '3' AND iddispositivo = '3');
1399
1400 vehicle_gsm_ = exists(SELECT * FROM modem_comando
1401 WHERE tipo_modem = alarms_.tipo_modem AND idtipocomando = '3' AND iddispositivo = '4');
1402
1403 IF (vehicle_gps_ OR vehicle_gsm_) THEN
1404
1405 IF (((ROUND(EXTRACT(EPOCH FROM (alarms_.fecha - coalesce(alarms_.envia_reset_primera_accion, localtimestamp))) / 60) <= alarms_.envia_reset_tiempo_acciones)
1406 AND (coalesce(alarms_.envia_reset_conteo_acciones, 0) < alarms_.envia_reset_limite_acciones))
1407 OR (ROUND(EXTRACT(EPOCH FROM (alarms_.fecha - alarms_.envia_reset_primera_accion)) / 60) > alarms_.envia_reset_tiempo_acciones))
1408 THEN
1409
1410 IF (alarms_.envia_reset_primera_activacion IS NULL OR (ROUND(EXTRACT(EPOCH FROM (alarms_.fecha - alarms_.envia_reset_primera_activacion)) / 60) > alarms_.envia_reset_tiempo_ocurr)
1411 OR (coalesce(alarms_.envia_reset_indice, 0) >= alarms_.envia_reset_cant))
1412 THEN
1413
1414 UPDATE alarmasvehiculos SET envia_reset_primera_activacion = localtimestamp, envia_reset_indice = 1
1415 WHERE idalarma = alarms_.idalarma AND idvehiculo = alarms_.idvehiculo
1416 RETURNING envia_reset_indice INTO actions_occurrences_;
1417
1418 ELSE
1419
1420 UPDATE alarmasvehiculos SET envia_reset_indice = coalesce(envia_reset_indice, 0) + 1
1421 WHERE idalarma = alarms_.idalarma AND idvehiculo = alarms_.idvehiculo
1422 RETURNING envia_reset_indice INTO actions_occurrences_;
1423
1424 END IF;
1425
1426 IF (actions_occurrences_ = alarms_.envia_reset_cant) THEN
1427
1428 IF (alarms_.envia_reset_primera_accion IS NULL OR (ROUND(EXTRACT(EPOCH FROM (alarms_.fecha - alarms_.envia_reset_primera_accion)) / 60) > alarms_.envia_reset_tiempo_acciones)) THEN
1429
1430 UPDATE alarmasvehiculos SET envia_reset_primera_accion = localtimestamp, envia_reset_conteo_acciones = 1
1431 WHERE idalarma = alarms_.idalarma AND idvehiculo = alarms_.idvehiculo;
1432
1433 ELSE
1434
1435 UPDATE alarmasvehiculos SET envia_reset_conteo_acciones = coalesce(envia_reset_conteo_acciones, 0) + 1
1436 WHERE idalarma = alarms_.idalarma AND idvehiculo = alarms_.idvehiculo;
1437
1438 END IF;
1439
1440 IF vehicle_gps_ THEN
1441
1442 INSERT INTO comandos_programados (idvehiculo, iddispositivo, proceso, usuario, idestado, medio_envio, maximo_intentos, idtipocomando)
1443 VALUES (alarms_.idvehiculo, '3', alarms_.proceso, 'sistema', (SELECT idestado FROM comandos_estado WHERE estado = 'P'), (SELECT id FROM tipos_medio WHERE codigo = 'COR_ALAR'), 2, '3');
1444
1445 END IF;
1446
1447 IF vehicle_gsm_ THEN
1448
1449 INSERT INTO comandos_programados (idvehiculo, iddispositivo, proceso, usuario, idestado, medio_envio, maximo_intentos, idtipocomando)
1450 VALUES(alarms_.idvehiculo, '4', alarms_.proceso, 'sistema', (SELECT idestado FROM comandos_estado WHERE estado = 'P'), (SELECT id FROM tipos_medio WHERE codigo = 'COR_ALAR'), 2, '3');
1451
1452 END IF;
1453
1454 END IF;
1455
1456 END IF;
1457
1458 END IF;
1459
1460
1461 END IF;
1462
1463 IF (alarms_.envia_consulta_pasajeros AND alarms_.maneja_sensor_pasajeros)
1464 THEN
1465
1466 actions_occurrences_ = 0;
1467
1468 PERFORM * FROM sensoresxvehiculo
1469 WHERE idvehiculo = alarms_.idvehiculo AND sensor IN ('INS50','INS50_HEXA','INS50_PER');
1470
1471 IF FOUND THEN
1472
1473 IF (((ROUND(EXTRACT(EPOCH FROM (alarms_.fecha - coalesce(alarms_.envia_cons_primera_accion, localtimestamp))) / 60) <= alarms_.envia_cons_tiempo_acciones)
1474 AND (coalesce(alarms_.envia_cons_conteo_acciones, 0) < alarms_.envia_cons_limite_acciones))
1475 OR (ROUND(EXTRACT(EPOCH FROM (alarms_.fecha - alarms_.envia_cons_primera_accion)) / 60) > alarms_.envia_cons_tiempo_acciones))
1476 THEN
1477
1478 IF (alarms_.envia_cons_pasj_primera_activacion IS NULL OR (ROUND(EXTRACT(EPOCH FROM (alarms_.fecha - alarms_.envia_cons_pasj_primera_activacion)) / 60) > alarms_.envia_cons_pasj_tiempo_ocurr)
1479 OR (coalesce(alarms_.envia_cons_pasj_indice, 0) >= alarms_.envia_cons_pasj_ocurr))
1480 THEN
1481
1482 UPDATE alarmasvehiculos SET envia_cons_pasj_primera_activacion = localtimestamp, envia_cons_pasj_indice = 1
1483 WHERE idalarma = alarms_.idalarma AND idvehiculo = alarms_.idvehiculo
1484 RETURNING envia_cons_pasj_indice INTO actions_occurrences_;
1485
1486 ELSE
1487
1488 UPDATE alarmasvehiculos SET envia_cons_pasj_indice = coalesce(envia_cons_pasj_indice, 0) + 1
1489 WHERE idalarma = alarms_.idalarma AND idvehiculo = alarms_.idvehiculo
1490 RETURNING envia_cons_pasj_indice INTO actions_occurrences_;
1491
1492 END IF;
1493
1494 RAISE NOTICE 'Ocurrencias acciones: % - ', actions_occurrences_;
1495
1496 IF (actions_occurrences_ = alarms_.envia_cons_pasj_ocurr) THEN
1497
1498 IF (alarms_.envia_cons_primera_accion IS NULL OR (ROUND(EXTRACT(EPOCH FROM (alarms_.fecha - alarms_.envia_cons_primera_accion)) / 60) > alarms_.envia_cons_tiempo_acciones)) THEN
1499
1500 UPDATE alarmasvehiculos SET envia_cons_primera_accion = localtimestamp, envia_cons_conteo_acciones = 1
1501 WHERE idalarma = alarms_.idalarma AND idvehiculo = alarms_.idvehiculo;
1502
1503 ELSE
1504
1505 UPDATE alarmasvehiculos SET envia_cons_conteo_acciones = coalesce(envia_cons_conteo_acciones, 0) + 1
1506 WHERE idalarma = alarms_.idalarma AND idvehiculo = alarms_.idvehiculo;
1507
1508 END IF;
1509
1510 RAISE NOTICE 'Comando generado: % - ', actions_occurrences_;
1511
1512 INSERT INTO comandos_programados (idvehiculo, iddispositivo, proceso, usuario, idestado, medio_envio, maximo_intentos, idtipocomando)
1513 VALUES(alarms_.idvehiculo, '5', alarms_.proceso, 'sistema', (SELECT idestado FROM comandos_estado WHERE estado = 'P'), (SELECT id FROM tipos_medio WHERE codigo = 'COR_ALAR'), 2, '6');
1514
1515 END IF;
1516
1517 END IF;
1518
1519 END IF;
1520
1521 END IF;
1522
1523 END IF;
1524
1525 RAISE NOTICE 'Notifica - %', alarms_.notifica;
1526
1527 IF (alarms_.notifica)
1528 THEN
1529
1530 generate_alarms_.consola = alarms_.consola;
1531 generate_alarms_.email = alarms_.email;
1532 generate_alarms_.sms = alarms_.sms;
1533 generate_alarms_.movil = alarms_.movil;
1534 generate_alarms_.descripcion = alarms_.descripcion;
1535 generate_alarms_.departamento = alarms_.departamento;
1536 generate_alarms_.direccion = alarms_.direccion;
1537 generate_alarms_.fecha = alarms_.fecha;
1538 generate_alarms_.fecha_generacion = to_char(alarms_.fecha, 'yyyy/MM/dd HH12:MI:SS AM');
1539 generate_alarms_.idalarma = alarms_.idalarma;
1540 generate_alarms_.idvehiculo = alarms_.idvehiculo;
1541 generate_alarms_.municipio = alarms_.municipio;
1542 generate_alarms_.nombre_conductor = alarms_.nombre_conductor;
1543 generate_alarms_.numerointerno = alarms_.numinterno;
1544 generate_alarms_.placa = alarms_.placa;
1545 generate_alarms_.latitud = alarms_.latitud;
1546 generate_alarms_.longitud = alarms_.longitud;
1547 generate_alarms_.velocidad = (COALESCE(ROUND(alarms_.velocidad:: NUMERIC, 2), 0) || ' Km/h');
1548 generate_alarms_.contactos = alarms_.contactos;
1549 generate_alarms_.usuarios_tokens = alarms_.usuarios_tokens;
1550 generate_alarms_.proceso = alarms_.proceso;
1551 generate_alarms_.procesobj = alarms_.procesobj;
1552 generate_alarms_.vehiculo = alarms_.vehiculo;
1553
1554 RETURN NEXT generate_alarms_;
1555
1556 END IF;
1557
1558 END IF;
1559
1560 END IF;
1561
1562 ELSE
1563
1564 --Reiniciar alarma (ocurrencias y notificaciones)
1565
1566 UPDATE alarmasocurrencias SET ocurrencias = 0
1567 WHERE idvehiculo = alarms_.idvehiculo AND idregla IN
1568 (
1569 SELECT idregla
1570 FROM alarmasreglas
1571 WHERE idalarma = alarms_.idalarma AND (tipo = 'G' OR tipo = 'S')
1572 );
1573
1574 UPDATE alarmas_notificaciones SET notificaciones = 0 WHERE idvehiculo = alarms_.idvehiculo AND idregla IN
1575 (
1576 SELECT idregla
1577 FROM alarmasreglas
1578 WHERE idalarma = alarms_.idalarma AND (tipo = 'G' OR tipo = 'S')
1579 );
1580
1581 -- Reinicio de acciones
1582
1583 UPDATE alarmas SET envia_reset_primera_activacion = null, envia_reset_indice = 0
1584 WHERE idalarma = alarms_.idalarma;
1585
1586 UPDATE alarmas SET envia_reset_primera_accion = null, envia_reset_conteo_acciones = 0
1587 WHERE idalarma = alarms_.idalarma;
1588
1589 UPDATE alarmas SET envia_cons_pasj_primera_activacion = null, envia_cons_pasj_indice = 0
1590 WHERE idalarma = alarms_.idalarma;
1591
1592 UPDATE alarmas SET envia_cons_primera_accion = null, envia_cons_conteo_acciones = 0
1593 WHERE idalarma = alarms_.idalarma;
1594
1595 END IF;
1596
1597 END LOOP;
1598
1599END
1600$BODY$
1601 LANGUAGE plpgsql VOLATILE
1602 COST 100
1603 ROWS 1000;