maya.public

Parameters

Name	Type	Mode
_serialnumber	character varying	IN
_startdate	timestamp with time zone	IN
_enddate	timestamp with time zone	IN
_mode	integer	IN
serialnumber	character varying	OUT
dmodel	character varying	OUT
startdate	timestamp with time zone	OUT
start_lat	real	OUT
start_lon	real	OUT
start_loc	character varying	OUT
geofence	character varying	OUT
t_detenido	integer	OUT
odometer	real	OUT
movement_status	character varying	OUT
iddriver	integer	OUT
speed	real	OUT
reason	character varying	OUT
sum_km	real	OUT

Definition

DECLARE
stored_ms varchar := 'unknown';
current_ms varchar := 'unknown';
starttime_ms timestamptz;
endtime_ms timestamptz;
BEGIN 
FOR serialnumber,dmodel,startdate,start_lat,start_lon,start_loc,geofence,t_detenido,odometer,movement_status,iddriver,speed,reason,sum_km IN
SELECT d.serialnumber,
       dm.description, 
       e.senddate, 
       e.latitude,
       e.longitude,
       (e.location->'ciudad')::varchar||' '||(e.location->'estado')::varchar,
       e.location->'localidad',
       t_detenido,
       e.odometer,
       vms.description,
       du.iddriver, 
       max(e.speed) OVER (ORDER BY e.senddate), 
       er.description,
       (max(e.odometer) OVER (order by e.senddate) - min(e.odometer) OVER (order by e.senddate))::numeric(10,2)  /*sum_km*/
FROM   event e 
       JOIN device d 
         ON d.iddevice = e.iddevice 
       JOIN catalogs.vehicle_movementstatus vms 
         ON vms.id = ( e.otros -> 'vehicle_movement_status' )::int
       JOIN devicemodel dm ON dm.idmodeldevice = d.idmodeldevice
       JOIN catalogs.event_reason er ON er.id = e.reason
       LEFT JOIN unit u ON u.idunit = d.idunit
       LEFT JOIN driver_unit du ON (d.idunit = du.idunit AND coalesce(assignenddate,now()) >= now() AND du.enabled=true)
WHERE e.senddate BETWEEN _startdate AND _enddate AND d.serialnumber = _serialnumber ORDER BY e.senddate
LOOP
	t_detenido := 0;
	/* identify a change in movement status */
	IF stored_ms = 'unknown' THEN
	stored_ms := movement_status;
	starttime_ms := startdate;
	RETURN NEXT; /* always return the initial state */
	ELSIF stored_ms <> movement_status AND stored_ms IN ('IDLING','STOPPED') THEN 
	/* the previous ms ends at the start of the current ms */
        endtime_ms := startdate;
	/* calculate the total duration of the previous ms */
        t_detenido := extract(epoch from (endtime_ms - starttime_ms));
	/* mark the starting of the new ms */
	stored_ms := movement_status;
	starttime_ms := startdate;
	RETURN NEXT;
	ELSIF stored_ms = movement_status AND stored_ms IN ('IDLING','STOPPED') THEN
	/* do nothing, don't return anything */
	ELSE /* moving to any other state incling moving itself */ 
	stored_ms := movement_status;
	starttime_ms := startdate;
        RETURN NEXT;
	END IF;
END LOOP;
/* return the last record in the resultset */
endtime_ms := startdate;
t_detenido := extract(epoch from (endtime_ms - starttime_ms));
IF FOUND THEN
RETURN NEXT; 
END IF;
END;

ot_ro_summarize_activity(character varying, timestamp with time zone, timestamp with time zone, integer)

Parameters

Definition