maya.public

Parameters

Name	Type	Mode
in_iddevice	integer	IN
in_start_date	timestamp with time zone	IN
in_end_date	timestamp with time zone	IN
serialnumber	character varying	OUT
iddevice	integer	OUT
idunit	integer	OUT
alias	character varying	OUT
start_date	timestamp with time zone	OUT
end_date	timestamp with time zone	OUT
start_location	text	OUT
end_location	text	OUT
max_speed	real	OUT
vms	character varying	OUT
duration_secs	numeric	OUT
moving_time_hrs	numeric	OUT
stop_time_hrs	numeric	OUT
idle_time_hrs	numeric	OUT
distance_gps_km	numeric	OUT
total_stops	integer	OUT
not_authorized_stops	integer	OUT
speeding_alerts	integer	OUT
harshbreak_alerts	integer	OUT

Definition

/* V20190926 Driving hours by driver report by ignition segments */
DECLARE
r record;
BEGIN
FOR r IN(
   WITH q AS
   ( SELECT row_number() over(ORDER BY c.senddate,c.gnx_idmessage) rn,
   c.senddate,
   c.ignition_on,
   c.speed,
   nullif(c.odometer,0) as odometer,
   c.gnx_idmessage,
   c.otros->'vehicle_movement_status'  vms,
   c.location->'ciudad' ||' '||(location->'estado')::text loc,
   Lag(c.otros->'vehicle_movement_status') over (ORDER BY c.senddate) vms0
   FROM ot_ro_event_cassandra(in_iddevice,in_start_date,in_end_date) as c),
   q1 AS( SELECT *,
   CASE WHEN q.vms <> q.vms0 THEN 1 ELSE 0 END shift,
   SUM( CASE WHEN q.vms <> q.vms0 THEN 1 ELSE 0 END) over (ORDER BY rn) gnum
   FROM q),
   q2 AS (SELECT
   first(q1.senddate) over w start_date,
   last(q1.senddate) over w end_date,
   first(q1.loc) over w start_location,
   last(q1.loc) over w end_location,
   first(q1.ignition_on) over w ignition_on,
   q1.vms,
   max(q1.senddate) over w - min(q1.senddate) over w AS duration,
   max(q1.speed) over w as max_speed,
   max(q1.odometer) over w - min(q1.odometer) over w AS distance_gps
   FROM q1 window w as (partition by q1.gnum)),
   Q3 as (SELECT distinct
              q2.start_date,q2.end_date,q2.ignition_on,q2.start_location,q2.end_location,q2.max_speed,
              cvms.description as vms,q2.duration,q2.distance_gps, (CASE WHEN q2.vms = '3' THEN 1 ELSE 0 END) total_stops
   FROM q2 JOIN catalogs.vehicle_movementstatus cvms ON cvms.id = q2.vms::int ORDER BY q2.start_date),
   Q4 AS (SELECT d.serialnumber,
          d.iddevice,
   	u.idunit,
   	u.alias,
   	q3.start_date,
   	q3.end_date,
	COALESCE(CASE WHEN q3.ignition_on <> lag(q3.ignition_on,1) over (partition by d.iddevice order by q3.start_date) THEN 1 ELSE 0 END,0) switch_ignition,
   	q3.start_location,
   	q3.end_location,
   	q3.max_speed,
   	q3.vms,
   	(extract(epoch from q3.duration))::numeric duration_secs,
           (CASE q3.vms WHEN 'MOVING' THEN (extract(epoch from q3.duration))/(3600.0) END)::numeric(12,2) moving_time_hrs,
           (CASE q3.vms WHEN 'IDLING' THEN (extract(epoch from q3.duration))/(3600.0) END)::numeric(12,2) idle_time_hrs,
           (CASE q3.vms WHEN 'STOPPED' THEN (extract(epoch from q3.duration))/(3600.0) END)::numeric(12,2) stop_time_hrs,
   	q3.distance_gps as distance_gps_km,
   	q3.total_stops
    FROM Q3 join device d ON d.iddevice = in_iddevice JOIN unit u ON u.idunit = d.idunit),
    Q5 AS (SELECT
	q4.serialnumber,
        q4.iddevice,
        q4.idunit,
        q4.alias,
        q4.start_date,
        q4.end_date,
        sum(q4.switch_ignition) over (partition by q4.iddevice order by q4.start_date) group_id,
        q4.start_location,
        q4.end_location,
        q4.max_speed,
        q4.vms,
	q4.duration_secs,
	q4.moving_time_hrs,
	q4.idle_time_hrs,
	q4.stop_time_hrs,
	q4.distance_gps_km,
	q4.total_stops
    FROM Q4)
   SELECT 
	last(q5.serialnumber) serialnumber,
        last(q5.iddevice) iddevice,
        last(q5.idunit) idunit,
        last(q5.alias) alias,
        first(q5.start_date) start_date,
        last(q5.end_date) end_date,
        first(q5.start_location) start_location,
        last(q5.end_location) end_location,
        max(q5.max_speed) max_speed,
        last(q5.vms) vms,
	sum(q5.duration_secs) duration_secs,
	sum(q5.moving_time_hrs) moving_time_hrs,
	sum(q5.idle_time_hrs) idle_time_hrs,
	sum(q5.stop_time_hrs) stop_time_hrs,
	sum(q5.distance_gps_km) distance_gps_km,
	sum(q5.total_stops) total_stops
	FROM Q5 GROUP by group_id
)
LOOP
    SELECT r.serialnumber ,r.iddevice ,r.idunit ,r.alias ,r.start_date, r.end_date,r.start_location, r.end_location , coalesce(r.max_speed,0) , r.vms , r.duration_secs , coalesce(r.moving_time_hrs,0) , coalesce(r.stop_time_hrs,0) , coalesce(r.idle_time_hrs,0) , coalesce(r.distance_gps_km,0) ,coalesce(r.total_stops,0) INTO serialnumber ,iddevice ,idunit ,alias ,start_date, end_date , start_location, end_location , max_speed , vms , duration_secs , moving_time_hrs , stop_time_hrs, idle_time_hrs, distance_gps_km ,total_stops;
    SELECT count(*) INTO speeding_alerts FROM alert a WHERE a.iddevice = in_iddevice AND a.alertdate BETWEEN r.start_date AND r.end_date AND a.alert_type = 1; 
    SELECT count(*) INTO harshbreak_alerts FROM alert a WHERE a.iddevice = in_iddevice AND a.alertdate BETWEEN r.start_date AND r.end_date AND a.alert_type = 18; 
    SELECT 0 INTO not_authorized_stops; /* wasn't implemented */
    RETURN NEXT;
END LOOP;
END;

ot_ro_move_status_agg_v2(integer, timestamp with time zone, timestamp with time zone)

Parameters

Definition