maya
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
ot_rw_gnxcommandstatusfix()
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',3,1) WHEN '0' THEN 'off' ELSE 'on' END ignition, CASE substr(d.OTHERS->'switches',4,1) WHEN '0' 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 join technicalaccount ta ON ta.idtechnicalaccount = d.idtechnicalaccount WHERE d.idmodeldevice = 1 AND ce.senddate > base_date AND co.idcommand IN (1,2,3,4) /*security command*/ AND ta.idtechnicalaccount NOT in (4,6616) /* exclude su transporte */ 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 IN (1,2) 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 IN (1,2) 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;