vw_ecm_event1

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

iddevice

int4

√

null

serialnumber

varchar

√

null

device_rnt.serialnumber

Implied Constraint

idmodeldevice

int4

√

null

devicemodel.idmodeldevice

Implied Constraint

idport

int8

√

null

jpod_bus

int4

√

null

bus

text

2147483647

√

null

movement

text

2147483647

√

null

senddate

timestamptz

35,6

√

null

idunit

int4

√

null

idclient

int4

√

null

iddriver

int4

√

null

speed

float4

8,8

√

null

idle_time

float8

17,17

√

null

stopduration

float8

17,17

√

null

j1939_hiresodometer

float8

17,17

√

null

j1939_odometer

float8

17,17

√

null

j1708_odometer

float8

17,17

√

null

ems_odometer

float8

17,17

√

null

j1939_batteryvoltage

float8

17,17

√

null

j1708_batteryvoltage

float8

17,17

√

null

j1939_switchedbatvoltage

float8

17,17

√

null

j1708_switchedbatvoltage

float8

17,17

√

null

j1939_enginespeed

float8

17,17

√

null

j1708_enginespeed

float8

17,17

√

null

j1939_roadspeed

float8

17,17

√

null

j1708_roadspeed

float8

17,17

√

null

j1939_totalfuel

float8

17,17

√

null

j1708_totalfuel

float8

17,17

√

null

ems_totalfuel

float8

17,17

√

null

j1939_totalidlefuel

text

2147483647

√

null

j1708_totalidlefuel

text

2147483647

√

null

ems_totalidlefuel

float8

17,17

√

null

j1939_totalidlehours

float8

17,17

√

null

j1708_totalidlehours

float8

17,17

√

null

ems_totalidlehours

float8

17,17

√

null

j1939_totalenginehours

float8

17,17

√

null

j1708_totalenginehours

float8

17,17

√

null

ems_totalenginehours

float8

17,17

√

null

j1939_enginecoolanttemp

float8

17,17

√

null

j1708_enginecoolanttemp

float8

17,17

√

null

j1939_engineoiltemp

float8

17,17

√

null

j1708_engineoiltemp

float8

17,17

√

null

j1939_dm1

text

2147483647

√

null

j1708_diagnostics

text

2147483647

√

null

ems_batteryvoltage

float8

17,17

√

null

ems_switchedbatvoltage

float8

17,17

√

null

ems_roadspeed

float8

17,17

√

null

ems_enginecoolanttemp

float8

17,17

√

null

ems_engineoiltemp

float8

17,17

√

null

ems_speedrpmmatrix

_int4

√

null

longitude

float8

17,17

√

null

latitude

float8

17,17

√

null

harsh_break

int4

√

null

location

hstore

2147483647

√

null

odometer

float8

17,17

√

null

vms

int4

√

null

ignition_on

bool

√

null

idling

bool

√

null

eventcat

int4

√

null

text

2147483647

√

null

Relationships

Close relationships within degrees of separation

View Definition

SELECT e.iddevice,
    d.serialnumber,
    d.idmodeldevice,
    sc.idport,
    sc.bus AS jpod_bus,
        CASE sc.bus
            WHEN 2 THEN 'j1708'::text
            ELSE 'j1939'::text
        END AS bus,
        CASE (e.otros -> 'vehicle_movement_status'::text)
            WHEN '1'::text THEN 'MOVING'::text
            WHEN '2'::text THEN 'IDLING'::text
            WHEN '3'::text THEN 'STOPPED'::text
            ELSE NULL::text
        END AS movement,
    e.senddate,
    u.idunit,
    t.idclient,
    du.iddriver,
    e.speed,
    ((e.otros -> 'idle_time'::text))::double precision AS idle_time,
    ((e.otros -> 'stopduration'::text))::double precision AS stopduration,
    ((e.otros -> 'j1939_HiResOdometer'::text))::double precision AS j1939_hiresodometer,
    ((e.otros -> 'j1939_Odometer'::text))::double precision AS j1939_odometer,
    ((e.otros -> 'j1708_Odometer'::text))::double precision AS j1708_odometer,
        CASE sc.bus
            WHEN 2 THEN NULLIF(((e.otros -> 'j1708_Odometer'::text))::double precision, (0.0)::double precision)
            ELSE COALESCE(NULLIF(((e.otros -> 'j1939_HiResOdometer'::text))::double precision, (0.0)::double precision), NULLIF(((e.otros -> 'j1939_Odometer'::text))::double precision, (0.0)::double precision))
        END AS ems_odometer,
    ((e.otros -> 'j1939_BatteryVoltage'::text))::double precision AS j1939_batteryvoltage,
    ((e.otros -> 'j1708_BatteryVoltage'::text))::double precision AS j1708_batteryvoltage,
    ((e.otros -> 'j1939_SwitchedBatVoltage'::text))::double precision AS j1939_switchedbatvoltage,
    ((e.otros -> 'j1708_SwitchedBatVoltage'::text))::double precision AS j1708_switchedbatvoltage,
    ((e.otros -> 'j1939_EngineSpeed'::text))::double precision AS j1939_enginespeed,
    ((e.otros -> 'j1708_EngineSpeed'::text))::double precision AS j1708_enginespeed,
    ((e.otros -> 'j1939_RoadSpeed'::text))::double precision AS j1939_roadspeed,
    ((e.otros -> 'j1708_RoadSpeed'::text))::double precision AS j1708_roadspeed,
    ((e.otros -> 'j1939_TotalFuel'::text))::double precision AS j1939_totalfuel,
    ((e.otros -> 'j1708_TotalFuel'::text))::double precision AS j1708_totalfuel,
        CASE sc.bus
            WHEN 2 THEN NULLIF(((e.otros -> 'j1708_TotalFuel'::text))::double precision, (0.0)::double precision)
            ELSE NULLIF(((e.otros -> 'j1939_TotalFuel'::text))::double precision, (0.0)::double precision)
        END AS ems_totalfuel,
    (e.otros -> 'j1939_TotalIdleFuel'::text) AS j1939_totalidlefuel,
    (e.otros -> 'j1708_TotalIdleFuel'::text) AS j1708_totalidlefuel,
        CASE sc.bus
            WHEN 2 THEN NULLIF(((e.otros -> 'j1708_TotalIdleFuel'::text))::double precision, (0.0)::double precision)
            ELSE NULLIF(((e.otros -> 'j1939_TotalIdleFuel'::text))::double precision, (0.0)::double precision)
        END AS ems_totalidlefuel,
    ((e.otros -> 'j1939_TotalIdleHours'::text))::double precision AS j1939_totalidlehours,
    ((e.otros -> 'j1708_TotalIdleHours'::text))::double precision AS j1708_totalidlehours,
        CASE sc.bus
            WHEN 2 THEN NULLIF(((e.otros -> 'j1708_TotalIdleHours'::text))::double precision, (0.0)::double precision)
            ELSE NULLIF(((e.otros -> 'j1939_TotalIdleHours'::text))::double precision, (0.0)::double precision)
        END AS ems_totalidlehours,
    ((e.otros -> 'j1939_TotalEngineHours'::text))::double precision AS j1939_totalenginehours,
    ((e.otros -> 'j1708_TotalEngineHours'::text))::double precision AS j1708_totalenginehours,
        CASE sc.bus
            WHEN 2 THEN NULLIF(((e.otros -> 'j1708_TotalEngineHours'::text))::double precision, (0.0)::double precision)
            ELSE NULLIF(((e.otros -> 'j1939_TotalEngineHours'::text))::double precision, (0.0)::double precision)
        END AS ems_totalenginehours,
    ((e.otros -> 'j1939_EngineCoolantTemp'::text))::double precision AS j1939_enginecoolanttemp,
    ((e.otros -> 'j1708_EngineCoolantTemp'::text))::double precision AS j1708_enginecoolanttemp,
    ((e.otros -> 'j1939_EngineOilTemp'::text))::double precision AS j1939_engineoiltemp,
    ((e.otros -> 'j1708_EngineOilTemp'::text))::double precision AS j1708_engineoiltemp,
    (e.otros -> 'j1939_DM1'::text) AS j1939_dm1,
    (e.otros -> 'j1708_Diagnostics'::text) AS j1708_diagnostics,
        CASE sc.bus
            WHEN 2 THEN ((e.otros -> 'j1708_BatteryVoltage'::text))::double precision
            ELSE ((e.otros -> 'j1939_BatteryVoltage'::text))::double precision
        END AS ems_batteryvoltage,
        CASE sc.bus
            WHEN 2 THEN ((e.otros -> 'j1708_SwitchedBatVoltage'::text))::double precision
            ELSE ((e.otros -> 'j1939_SwitchedBatVoltage'::text))::double precision
        END AS ems_switchedbatvoltage,
        CASE sc.bus
            WHEN 2 THEN ((e.otros -> 'j1708_EngineSpeed'::text))::double precision
            ELSE ((e.otros -> 'j1939_RoadSpeed'::text))::double precision
        END AS ems_roadspeed,
        CASE sc.bus
            WHEN 2 THEN ((e.otros -> 'j1708_EngineCoolantTemp'::text))::double precision
            ELSE ((e.otros -> 'j1939_EngineCoolantTemp'::text))::double precision
        END AS ems_enginecoolanttemp,
        CASE sc.bus
            WHEN 2 THEN ((e.otros -> 'j1708_EngineOilTemp'::text))::double precision
            ELSE ((e.otros -> 'j1939_EngineOilTemp'::text))::double precision
        END AS ems_engineoiltemp,
    (regexp_replace((((e.otros -> 'speedRPMMatrix'::text))::character varying)::text, '}{'::text, '},{'::text, 'g'::text))::integer[] AS ems_speedrpmmatrix,
    (e.longitude)::double precision AS longitude,
    (e.latitude)::double precision AS latitude,
        CASE e.reason
            WHEN 115 THEN 1
            ELSE NULL::integer
        END AS harsh_break,
    e.location,
    NULLIF((e.odometer)::double precision, (0.0)::double precision) AS odometer,
    ((e.otros -> 'vehicle_movement_status'::text))::integer AS vms,
    e.ignition_on,
        CASE
            WHEN ((e.ignition_on = true) AND (e.speed < (1)::double precision)) THEN true
            ELSE false
        END AS idling,
    e.eventcat,
    (e.otros -> 'cl'::text) AS cl
   FROM (((((event e
     JOIN device d ON (((d.iddevice = e.iddevice) AND (e.eventcat = 5))))
     LEFT JOIN ( SELECT p.idport,
            p.iddevice,
            sc_1.bus
           FROM (port p
             JOIN serialcfg sc_1 ON (((p.idport = sc_1.idport) AND (sc_1.serialport_devicetype = 1))))) sc ON ((sc.iddevice = d.iddevice)))
     JOIN technicalaccount t ON ((t.idtechnicalaccount = d.idtechnicalaccount)))
     LEFT JOIN unit u ON ((u.idunit = d.idunit)))
     LEFT JOIN driver_unit du ON (((du.idunit = u.idunit) AND (du.enabled = true))))
  WHERE (d.idmodeldevice = 4);

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views