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
alias	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
max_speed	real	OUT
moving_time_hrs	real	OUT
stop_time_hrs	real	OUT
idle_time_hrs	real	OUT
distance_gps	real	OUT
total_stops	integer	OUT
not_authorized_stops	integer	OUT
speeding_alerts	integer	OUT
harshbreak_alerts	integer	OUT

Definition

DECLARE
_idunit integer;
_iddevice integer;
_speeding_alerts integer;
_harshbreak_alerts integer;
BEGIN
FOR _idunit IN (SELECT unnest(in_idunits)) LOOP
SELECT d.iddevice INTO _iddevice FROM device d WHERE d.idunit = _idunit;
SELECT count(*) INTO _speeding_alerts FROM alert a WHERE a.iddevice = _iddevice AND a.alertdate BETWEEN in_start_date AND in_end_date AND a.alert_type = 1; 
SELECT count(*) INTO _harshbreak_alerts FROM alert a WHERE a.iddevice = _iddevice AND a.alertdate BETWEEN in_start_date AND in_end_date AND a.alert_type = 18;
RETURN QUERY(
SELECT  msa.idunit,
	msa.alias,
	first(msa.start_date) as start_date,
	last(msa.end_date) as end_date,
	first(msa.start_location)::varchar as start_location,
	last(msa.end_location)::varchar as end_location,
	max(msa.max_speed)::real as max_speed,
	sum(msa.moving_time_hrs)::real as moving_time_hrs,
	sum(msa.stop_time_hrs)::real as stop_time_hrs,
	sum(msa.idle_time_hrs)::real idle_time_hrs,
	sum(msa.distance_gps_km)::real as distance_gps,
	sum(msa.total_stops)::int as total_stops,
	sum(msa.not_authorized_stops)::int as not_authorized_stops,
	_speeding_alerts as speeding_alerts,
	_harshbreak_alerts as harshbreak_alerts  
		FROM ot_ro_move_status_agg(_iddevice,in_start_date,in_end_date) as msa group by 1,2,14,15);
END LOOP;
END;

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

Parameters

Definition