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
in_summarize_stops	boolean	IN
in_summarize_movement	boolean	IN
out_idunit	integer	OUT
out_latlon	character varying	OUT
out_location	character varying	OUT
out_georeference	character varying	OUT
out_senddate	timestamp with time zone	OUT
out_total_minutes_moving	integer	OUT
out_total_minutes_stopped	integer	OUT
out_odometer	numeric	OUT
out_movement_status	character varying	OUT
out_driver	character varying	OUT
out_speed	real	OUT
out_event_reason	character varying	OUT
out_daily_km	numeric	OUT
out_power_connected	character varying	OUT
out_battery_status	character varying	OUT

Definition

DECLARE
_curr_idunit INTEGER;
_curr_iddevice INTEGER;
_summarizing_stops_in_progress BOOLEAN := false;
_summarizing_movements_in_progress BOOLEAN := false;
_curr_r record;
_saved_r_s record;
_saved_r_m record;
BEGIN
FOR _curr_idunit IN SELECT unnest(in_idunits) LOOP
SELECT iddevice INTO _curr_iddevice FROM device WHERE idunit = _curr_idunit;
FOR _curr_r IN (SELECT _curr_idunit out_idunit,(e.latitude||','||e.longitude)::varchar out_latlon,((e.location->'ciudad')::varchar||','||(e.location->'estado')::varchar)::varchar out_location,
   nullif((e.location->'localidad'),'null')::varchar out_georeference,e.senddate out_senddate,NULL::integer  out_total_minutes_moving,NULL::integer out_total_minutes_stopped,e.odometer::numeric out_odometer,
   vms.description::varchar out_movement_status,(dr1.name||' '||dr1.lastname)::varchar /*driver*/ out_driver,e.speed::real out_speed,er.description::varchar out_event_reason,
   (max(e.odometer) OVER w - min(e.odometer) OVER w)::numeric /*daily_km*/ out_daily_km,eps.description out_power_connected,ebs.description out_battery_status 
   FROM event e JOIN catalogs.vehicle_movementstatus vms ON vms.id = (e.otros->'vehicle_movement_status')::int JOIN catalogs.event_reason er ON er.id = e.reason::int 
   LEFT JOIN driver_unit du ON (du.idunit = _curr_idunit AND du.assignstartdate <= e.senddate AND coalesce(du.assignenddate,now()) >= e.senddate) 
   LEFT JOIN driver dr1 ON dr1.iddriver = du.iddriver LEFT JOIN catalogs.event_power_supply eps ON eps.id = (e.otros->'power_supply')::int 
   LEFT JOIN catalogs.event_battery_status ebs ON ebs.id = (otros->'battery_status')::int WHERE e.iddevice = _curr_iddevice AND e.senddate 
   BETWEEN in_start_date AND in_end_date AND (e.eventcat = 2 OR e.eventcat IS NULL) WINDOW w AS (PARTITION BY _curr_idunit ORDER BY e.senddate) ORDER BY e.senddate) LOOP
	IF in_summarize_stops = true  THEN
		IF _curr_r.out_movement_status = 'STOPPED' AND NOT _summarizing_stops_in_progress THEN
			_summarizing_stops_in_progress := true;
			_saved_r_s := _curr_r;
		ELSIF _summarizing_stops_in_progress AND (_curr_r.out_movement_status)::varchar <> 'STOPPED' THEN 
			_summarizing_stops_in_progress := false;
			SELECT _saved_r_s.out_idunit,_saved_r_s.out_latlon,_saved_r_s.out_location,_saved_r_s.out_georeference,_saved_r_s.out_senddate,_saved_r_s.out_total_minutes_moving,_saved_r_s.out_total_minutes_stopped,
			_saved_r_s.out_odometer,_saved_r_s.out_movement_status,_saved_r_s.out_driver,_saved_r_s.out_speed,_saved_r_s.out_event_reason,_saved_r_s.out_daily_km, _saved_r_s.out_power_connected,
			 _saved_r_s.out_battery_status INTO out_idunit,out_latlon,out_location,out_georeference,out_senddate,out_total_minutes_moving,out_total_minutes_stopped,out_odometer,out_movement_status,
			 out_driver,out_speed,out_event_reason,out_daily_km, out_power_connected, out_battery_status;
			out_total_minutes_stopped := extract(epoch from (_curr_r.out_senddate - _saved_r_s.out_senddate)) / 60;
			RETURN next;
		END IF;
	END IF;
	IF in_summarize_movement = true  THEN
		IF _curr_r.out_movement_status = 'MOVING' AND NOT _summarizing_movements_in_progress = true THEN
			_summarizing_movements_in_progress := true;
			_saved_r_m := _curr_r;
		ELSIF _summarizing_movements_in_progress = true AND (_curr_r.out_movement_status)::varchar <> 'MOVING'  THEN 
			_summarizing_movements_in_progress := false;
			SELECT _saved_r_m.out_idunit,_saved_r_m.out_latlon,_saved_r_m.out_location,_saved_r_m.out_georeference,_saved_r_m.out_senddate,_saved_r_m.out_total_minutes_moving,
			_saved_r_m.out_total_minutes_stopped,_saved_r_m.out_odometer,_saved_r_m.out_movement_status,_saved_r_m.out_driver,_saved_r_m.out_speed,_saved_r_m.out_event_reason,
			_saved_r_m.out_daily_km, _saved_r_m.out_power_connected, _saved_r_m.out_battery_status INTO out_idunit,out_latlon,out_location,out_georeference,out_senddate,out_total_minutes_moving,
			out_total_minutes_stopped,out_odometer,out_movement_status,out_driver,out_speed,out_event_reason,out_daily_km, out_power_connected, out_battery_status;
			out_total_minutes_moving := extract(epoch from (_curr_r.out_senddate - _saved_r_m.out_senddate)) / 60;
			RETURN next;
		END IF;
	END IF;

IF  _summarizing_stops_in_progress = false AND _summarizing_movements_in_progress = false THEN  
        SELECT _curr_r.out_idunit,_curr_r.out_latlon,_curr_r.out_location,_curr_r.out_georeference,_curr_r.out_senddate,_curr_r.out_total_minutes_moving,
        _curr_r.out_total_minutes_stopped,_curr_r.out_odometer,_curr_r.out_movement_status,_curr_r.out_driver,_curr_r.out_speed,_curr_r.out_event_reason,
        _curr_r.out_daily_km, _curr_r.out_power_connected, _curr_r.out_battery_status INTO out_idunit,out_latlon,out_location,out_georeference,out_senddate,out_total_minutes_moving,
        out_total_minutes_stopped,out_odometer,out_movement_status,out_driver,out_speed,out_event_reason,out_daily_km, out_power_connected, out_battery_status;
        RETURN NEXT;
    END IF;
END LOOP;
END LOOP;
	IF (_curr_r.out_movement_status = 'MOVING' AND in_summarize_movement = TRUE) THEN
    SELECT _saved_r_m.out_idunit,_saved_r_m.out_latlon,_saved_r_m.out_location,_saved_r_m.out_georeference,_saved_r_m.out_senddate,_saved_r_m.out_total_minutes_moving,_saved_r_m.out_total_minutes_stopped,
		_saved_r_m.out_odometer,_saved_r_m.out_movement_status,_saved_r_m.out_driver,_saved_r_m.out_speed,_saved_r_m.out_event_reason,_saved_r_m.out_daily_km, _saved_r_m.out_power_connected, 
		_saved_r_m.out_battery_status INTO out_idunit,out_latlon,out_location,out_georeference,out_senddate,out_total_minutes_moving,out_total_minutes_stopped,out_odometer,
		out_movement_status,out_driver,out_speed,out_event_reason,out_daily_km, out_power_connected, out_battery_status;
		out_total_minutes_moving := extract(epoch from (_curr_r.out_senddate - _saved_r_m.out_senddate)) / 60;
    ELSIF (_curr_r.out_movement_status = 'STOPPED' AND in_summarize_stops = TRUE) THEN
        SELECT _saved_r_s.out_idunit,_saved_r_s.out_latlon,_saved_r_s.out_location,_saved_r_s.out_georeference,_saved_r_s.out_senddate,_saved_r_s.out_total_minutes_moving,_saved_r_s.out_total_minutes_stopped,
		_saved_r_s.out_odometer,_saved_r_s.out_movement_status,_saved_r_s.out_driver,_saved_r_s.out_speed,_saved_r_s.out_event_reason,_saved_r_s.out_daily_km, _saved_r_s.out_power_connected, 
		_saved_r_s.out_battery_status INTO out_idunit,out_latlon,out_location,out_georeference,out_senddate,out_total_minutes_moving,out_total_minutes_stopped,out_odometer,
		out_movement_status,out_driver,out_speed,out_event_reason,out_daily_km, out_power_connected, out_battery_status;
		out_total_minutes_stopped := extract(epoch from (_curr_r.out_senddate - _saved_r_s.out_senddate)) / 60;
    END IF;
    RETURN NEXT;
END;

ot_ro_activity_report_pos_original(integer[], timestamp with time zone, timestamp with time zone, boolean, boolean)

Parameters

Definition