maya.public

Parameters

Name	Type	Mode
_startdate	timestamp with time zone	IN
_enddate	timestamp with time zone	IN
_date_key	integer	OUT
_idunit	integer	OUT
_idclient	integer	OUT
_iddriver	integer	OUT
_geofence	integer	OUT
_entrancedate	timestamp with time zone	OUT
_exitdate	timestamp with time zone	OUT
_inside_duration	integer	OUT
mov_moving_time	integer	OUT
mov_idle_time	integer	OUT
mov_stopped_time	integer	OUT

Definition

BEGIN
RETURN QUERY 
(WITH q as (SELECT u.idunit,u.idclient,du.iddriver,unnest(geofencein) geofence,'in' event,seendate event_date 
FROM geofence_history gh JOIN unit u ON u.idunit = gh.idunit JOIN driver_unit du ON (du.idunit = u.idunit AND du.enabled = true and assignenddate is null) where geofencein is not null and seendate between _startdate and _enddate 
UNION select u.idunit,u.idclient,du.iddriver,unnest(geofenceout) geofence,'out' event,seendate 
 FROM geofence_history gh JOIN unit u ON u.idunit = gh.idunit JOIN driver_unit du ON (du.idunit = u.idunit AND du.enabled = true and assignenddate is null) where geofenceOUT is not null and seendate between _startdate and _enddate 
ORDER BY idunit,geofence,event,event_date) 
SELECT 
(extract(year from _startdate)*10000 + extract(month from _startdate)*100 + extract(day from _startdate))::int date_key
,q.idunit,q.idclient,q.iddriver,q.geofence,q.event_date entrance_date,qself.event_date exit_date, 
(extract(epoch from (qself.event_date - q.event_date))/60)::int inside_duration,
((extract(epoch from (qself.event_date - q.event_date))*.8)/60)::int mov_moving_time, 
((extract(epoch from (qself.event_date - q.event_date))*.1)/60)::int mov_idle_time,
((extract(epoch from (qself.event_date - q.event_date))*.1)/60)::int mov_stopped_time
FROM q JOIN q as qself ON (qself.idunit = q.idunit AND q.geofence = qself.geofence AND qself.event = 'out' AND q.event = 'in' AND q.event_date < qself.event_date) ORDER BY q.idunit,q.event_date
);
END

ot_ro_dw_geofence_activity(timestamp with time zone, timestamp with time zone)

Parameters

Definition