maya.public

Parameters

Name	Type	Mode
in_idunits	ARRAY	IN
in_start_date	timestamp with time zone	IN
in_end_date	timestamp with time zone	IN
idunit	integer	OUT
serialnumber	character varying	OUT
econum	character varying	OUT
start_date	timestamp with time zone	OUT
end_date	timestamp with time zone	OUT
start_location	character varying	OUT
end_location	character varying	OUT
activity_type	character varying	OUT
duration_minutes	integer	OUT
distance_gps	real	OUT
max_speed	real	OUT
speed_avg	real	OUT
speeding_alerts	integer	OUT
harshbreak_alerts	integer	OUT
not_authorized_stops	integer	OUT
panic_button_alerts	integer	OUT
idle_too_long_alerts	integer	OUT

Definition

DECLARE
in_iddevices INTEGER[];
r0 record;
BEGIN
SELECT array_agg(iddevice) INTO in_iddevices FROM device d WHERE d.idunit = ANY(in_idunits);
FOR r0 IN (WITH q AS (SELECT _iddevice, _start_date, _end_date, _start_location, _end_location, _vms, _duration_minutes, _distance_gps, _max_speed, _speed_avg 
	FROM ot_ro_report_sar_citus(in_iddevices,in_start_date,in_end_date)) 
		SELECT _iddevice,d.idunit,d.serialnumber,u.alias econum,q._start_date start_date,q._end_date end_date,(((q._start_location::hstore)->'ciudad') || ' , ' || ((_start_location::hstore)->'estado'))::varchar start_location, 
		(((_end_location::hstore)->'ciudad') || ' , '|| ((_end_location::hstore)->'estado'))::varchar end_location,
		vms.description activity_type, q._duration_minutes::integer duration_minutes, q._distance_gps::real distance_gps,q._max_speed::real max_speed,q._speed_avg::real speed_avg 
		FROM q JOIN device d ON d.iddevice = q._iddevice JOIN unit u ON u.idunit = d.idunit JOIN catalogs.vehicle_movementstatus vms ON vms.id = q._vms) LOOP
SELECT r0.idunit, r0.serialnumber, r0.econum, r0.start_date, r0.end_date, r0.start_location, r0.end_location, r0.activity_type, r0.duration_minutes, round(r0.distance_gps::numeric,2)::real, round(r0.max_speed::numeric,2)::real, round(r0.speed_avg::numeric,2)::real
		INTO idunit, serialnumber, econum, start_date, end_date, start_location, end_location,activity_type, duration_minutes,distance_gps,max_speed, speed_avg;
		SELECT coalesce(count(*),0)::int INTO speeding_alerts FROM alert WHERE iddevice = r0._iddevice AND alertdate BETWEEN r0.start_date AND r0.end_date AND alert_type = 1;
		SELECT coalesce(count(*),0)::int INTO harshbreak_alerts FROM alert WHERE iddevice = r0._iddevice AND alertdate BETWEEN r0.start_date AND r0.end_date AND alert_type = 18;
		SELECT coalesce(count(*),0)::int INTO panic_button_alerts FROM alert WHERE iddevice = r0._iddevice AND alertdate BETWEEN r0.start_date AND r0.end_date AND alert_type = 4;
		SELECT coalesce(count(*),0)::int INTO idle_too_long_alerts FROM alert WHERE iddevice = r0._iddevice AND alertdate BETWEEN r0.start_date AND r0.end_date AND alert_type = 6;
		not_authorized_stops := 0;
		RETURN NEXT;
END LOOP;
END;

ot_ro_report_sar(integer[], timestamp with time zone, timestamp with time zone)

Parameters

Definition