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
avg_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

/* V20190911 The basis for the "Actividad resumida" report */
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.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,
   q1.vms,
   max(q1.senddate) over w - min(q1.senddate) over w AS duration,
   max(q1.speed) over w as max_speed,
   avg(q1.speed) over w as avg_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.start_location,q2.end_location,q2.max_speed,q2.avg_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)
   SELECT d.serialnumber,
          d.iddevice,
    u.idunit,
    u.alias,
    q3.start_date,
    q3.end_date,
    q3.start_location,
    q3.end_location,
    q3.max_speed,
    q3.avg_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)
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),coalesce(r.avg_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,avg_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(integer, timestamp with time zone, timestamp with time zone)

Parameters

Definition