maya.public

Parameters

Name	Type	Mode
_serialnumber	character varying	IN
_startdate	timestamp with time zone	IN
_enddate	timestamp with time zone	IN
serialnumber	character varying	OUT
trip_starttime	timestamp with time zone	OUT
trip_endtime	timestamp with time zone	OUT
trip_duration_hours	double precision	OUT
trip_startlocation	character varying	OUT
trip_endlocation	character varying	OUT
movement_hours	double precision	OUT
stopped_hours	double precision	OUT
idling_hours	double precision	OUT
distance_km	double precision	OUT
speeding_count	integer	OUT
harshbreak_count	integer	OUT

Definition

DECLARE
_stored_ms varchar := 'unknown';
_stored_time timestamptz;
_duration_hours float; 
_tripstarted boolean := false;
_r event%rowtype;
_r1 record;
_odometer_start float;
_odometer_end float;
BEGIN 
movement_hours := 0;
stopped_hours := 0;
idling_hours := 0;
distance_km := 0;
DROP TABLE IF EXISTS trip;
CREATE TEMPORARY TABLE trip (LIKE event);
FOR _r IN (SELECT e.* FROM event e JOIN device d ON d.iddevice = e.iddevice WHERE e.senddate BETWEEN _startdate AND _enddate AND d.serialnumber = _serialnumber ORDER BY e.senddate) LOOP
	IF _tripstarted = true THEN
	INSERT INTO trip VALUES(_r.*);
	END IF;
	IF _r.ignition_on = false AND _tripstarted = true THEN /* end of the trip */
	_tripstarted := false;
	_odometer_end := _r.odometer;
	trip_endtime := _r.senddate;
	trip_duration_hours := extract(epoch from (trip_endtime - trip_starttime))/3600.0;
	trip_endlocation := (_r.location->'ciudad')::varchar||','||(_r.location->'estado')::varchar;
	/*Do aggregations, return summarized row*/
	FOR _r1 IN (SELECT e.*,vms.description as vms FROM trip e
		JOIN catalogs.vehicle_movementstatus vms
		ON vms.id = ( e.otros -> 'vehicle_movement_status' )::int ORDER BY e.senddate) 
	LOOP
		_duration_hours := extract(epoch FROM (_r1.senddate - _stored_time))/3600.0;
		IF _stored_ms = 'MOVING' THEN
		movement_hours := movement_hours + _duration_hours;
		ELSIF _stored_ms = 'IDLING' THEN
		idling_hours := idling_hours + _duration_hours;
		ELSIF _stored_ms = 'STOPPED' THEN
		stopped_hours := stopped_hours + _duration_hours;
		ELSE 
		/*DO NOTHING*/
		END IF;
		_stored_ms := _r1.vms;
		_stored_time := _r1.senddate;
	END LOOP;
	serialnumber := _serialnumber;
	distance_km := _odometer_end - _odometer_start;
	TRUNCATE trip;
	SELECT count(*)::int INTO speeding_count FROM alert a JOIN device d ON (d.iddevice = a.iddevice AND a.alertdate BETWEEN trip_starttime AND trip_endtime) JOIN alertconf ac ON (ac.idalertconf = a.idalertconf AND ac.eventype = 1) WHERE d.serialnumber = _serialnumber; 
	SELECT count(*)::int INTO harshbreak_count FROM alert a JOIN device d ON (d.iddevice = a.iddevice AND a.alertdate BETWEEN trip_starttime AND trip_endtime) JOIN alertconf ac ON (ac.idalertconf = a.idalertconf AND ac.eventype = 18) WHERE d.serialnumber = _serialnumber; 
	RETURN NEXT;
	movement_hours := 0;
	stopped_hours := 0;
	idling_hours := 0;
	distance_km := 0;
	ELSIF _r.ignition_on = true AND _tripstarted = false THEN /* beginning of trip */
	TRUNCATE trip;
	INSERT INTO trip VALUES(_r.*);
	_tripstarted := true; 
	_stored_time := _r.senddate;
	_odometer_start := _r.odometer;
	trip_starttime := _r.senddate;
        trip_startlocation := (_r.location->'ciudad')::varchar||','||(_r.location->'estado')::varchar;
	movement_hours := 0;
	stopped_hours := 0;
	idling_hours := 0;
	distance_km := 0;
	ELSE 
	/*DO NOTHING*/
	END IF;
END LOOP;
END;

ot_ro_summarize_trip(character varying, timestamp with time zone, timestamp with time zone)

Parameters

Definition