maya.public

Parameters

Name	Type	Mode
_start_date	timestamp with time zone	IN
_end_date	timestamp with time zone	IN
idcmdexec	bigint	OUT
iddevice	integer	OUT
command_sent_at	timestamp with time zone	OUT
unit_online_at	timestamp with time zone	OUT
online_ignition	boolean	OUT
sec_status_change_at	timestamp with time zone	OUT
sec_status_change_ignition	boolean	OUT

Definition

DECLARE
r1 record;
r0 record;
_end_event timestamptz;
_tmp_securitystatus INTEGER;
_command_waiting BOOLEAN := false;
BEGIN
/* v20180215 To detect commands that are taking too long to be applied after the unit could communicate */
_end_event := _end_date + '1 day'::interval;
FOR r0 IN (SELECT distinct ce.iddevice FROM commandexec ce WHERE ce.senddate BETWEEN _start_date AND _end_date) LOOP
FOR r1 IN (SELECT co.iddevice,co.senddate,co.senddate creationdate,co.idcmdexe,0 securitystatus,null::boolean ignition_on FROM commandexec co WHERE co.iddevice = r0.iddevice  AND co.senddate BETWEEN _start_date AND _end_date 
	UNION SELECT e.iddevice,e.senddate,e.creationdate,null::integer,e.securitystatus,ignition_on FROM tmp.event e WHERE e.iddevice = r0.iddevice AND senddate BETWEEN _start_date AND _end_event ORDER BY 2)  LOOP
    IF r1.idcmdexe IS NOT NULL THEN
		SELECT r1.idcmdexe,r1.iddevice,r1.senddate INTO idcmdexec,iddevice,command_sent_at;
		_command_waiting := true;
		unit_online_at := null;
		sec_status_change_at := null;
	ELSE
	   IF _command_waiting = true THEN
		   IF unit_online_at IS NULL THEN
			   IF (r1.creationdate - r1.senddate) < '5 minutes'::interval THEN
			   SELECT r1.creationdate INTO unit_online_at;
			   SELECT r1.ignition_on INTO online_ignition;
			   END IF;
		   END IF;
		   IF _tmp_securitystatus <> r1.securitystatus AND sec_status_change_at IS NULL THEN
				SELECT r1.senddate INTO sec_status_change_at;
				SELECT r1.ignition_on INTO sec_status_change_ignition;
				_command_waiting := false;
				RETURN NEXT;
		   END IF;		
	   END IF;
	   _tmp_securitystatus := r1.securitystatus;
	END IF;
END LOOP;
END LOOP;
END;

ot_ro_qos_commands(timestamp with time zone, timestamp with time zone)

Parameters

Definition