maya.public

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;

ot_rw_gnxcommandstatusfix()

Parameters

Definition