maya
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
ot_rw_fscommandstatusfix()
Parameters
Name
Type
Mode
Definition
DECLARE cnt integer; base_date timestamptz; BEGIN base_date = now() - '1 month'::interval; /* To update the status of commands that shouldn't be on a not applied status */ /* To order the sent commands by row number desc */ WITH q AS ( SELECT row_number() over w rn, ce.idcmdexe, ce.senddate, d.serialnumber, d.lastgpsdate, co.idcommand, co.alias comando, ce.status, CASE substr(d.OTHERS->'switches',15,1) WHEN '1' THEN 'off' ELSE 'on' END ignition, CASE substr(d.OTHERS->'switches',14,1) WHEN '1' THEN 'off' ELSE 'on' END acceleration FROM commandexec ce join device d ON d.iddevice = ce.iddevice join command co ON co.idcommand = ce.idcommand WHERE d.idmodeldevice = 4 AND ce.senddate > base_date AND co.idcommand IN (1,2,3,4) /*security command*/ window w AS (PARTITION BY d.serialnumber ORDER BY ce.senddate DESC)) /* To update the status of commands sent before the last */ ,q1 AS ( UPDATE commandexec ce SET status = 4 FROM q WHERE q.idcmdexe = ce.idcmdexe AND q.rn > 1 AND ce.status = 3 RETURNING ce.*), /* To update the last send command status to applied if it was already applied */ q2 AS (UPDATE commandexec ce SET status = 4 FROM q WHERE q.idcmdexe = ce.idcmdexe AND q.rn = 1 AND (q.lastgpsdate - ce.senddate) > '5 minutes'::interval AND ce.status = 3 AND ((ce.idcommand = 1 /* inhabilitacion de ignicion */ AND ignition = 'off' ) OR (ce.idcommand = 2 /* inhabilitacion confirmacion */ AND ignition = 'on' ) OR (ce.idcommand = 3 /* recuperacion */ AND (ignition = 'off' AND acceleration = 'off') ) OR (ce.idcommand = 4 /* recuperacion confirmacion */ AND (ignition = 'on' AND acceleration = 'on') )) RETURNING ce.* ), q3 AS (SELECT idcmdexe FROM q1 UNION SELECT idcmdexe FROM q2) SELECT count(*) INTO cnt FROM q3 ; RETURN cnt; END;