maya
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
ot_ro_get_ecm_jpod_bus(integer, timestamp with time zone, timestamp with time zone)
Parameters
Name
Type
Mode
in_idunit
integer
IN
in_start_date
timestamp with time zone
IN
in_end_date
timestamp with time zone
IN
Definition
/* v20180501 To guess the correct jpod bus of a unit based on traveled distance and fuel consumption data the algorithm is effective only if the unit has had movement RETURNS the idjpodbus 0 -> inconclusive, 1 -> j1939, 2-> j1708 */ DECLARE idjpodbus integer := 0; consumed_fuel_j1708 float := 0.0; consumed_fuel_j1939 float := 0.0; ecm_distance_j1708 float := 0.0; ecm_distance_j1939 float := 0.0; gps_distance float := 0.0; fuel_performance_j1708 float := 0.0; fuel_performance_j1939 float := 0.0; AVG_FUEL_PERFORMANCE float := 2.0; BEGIN SELECT max(e.odometer) - min(e.odometer),coalesce(max((otros->'j1939_HiresOdometer')::float) - min((otros->'j1939_HiresOdometer')::float), max((otros->'j1939_Odometer')::float) - min((otros->'j1939_Odometer')::float)),max((otros->'j1708_Odometer')::float) - min((otros->'j1708_Odometer')::float), max((otros->'j1939_TotalFuel')::float) - min((otros->'j1939_TotalFuel')::float),max((otros->'j1708_TotalFuel')::float) - min((otros->'j1708_TotalFuel')::float) INTO gps_distance,ecm_distance_j1939,ecm_distance_j1708,consumed_fuel_j1939,consumed_fuel_j1708 FROM event e JOIN device d ON d.iddevice = e.iddevice WHERE d.idunit = in_idunit AND senddate BETWEEN in_start_date AND in_end_date; fuel_performance_j1939 := coalesce(ecm_distance_j1939 / nullif(consumed_fuel_j1939,0),0); fuel_performance_j1708 := coalesce(ecm_distance_j1708 / nullif(consumed_fuel_j1708,0),0); IF gps_distance = 0 THEN RAISE WARNING 'No gps odometer change ecm_distance_j1708:% , ecm_distance_j1939:%',ecm_distance_j1708,ecm_distance_j1939; RETURN 0; ELSE IF abs(fuel_performance_j1939 - AVG_FUEL_PERFORMANCE ) < abs(fuel_performance_j1708 - AVG_FUEL_PERFORMANCE) THEN idjpodbus := 1; ELSIF abs(fuel_performance_j1939 - AVG_FUEL_PERFORMANCE ) > abs(fuel_performance_j1708 - AVG_FUEL_PERFORMANCE) THEN idjpodbus := 2; ELSE idjpodbus := 0; END IF; END IF; RAISE WARNING 'fuel_performance_j1939: % , fuel_performance_j1708: % ', fuel_performance_j1939, fuel_performance_j1708; RETURN idjpodbus; END;