sms_machines_trackers

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

machine_id

uuid

2147483647

√

null

tenant_id

uuid

2147483647

√

null

machine_name

varchar

256

√

null

machine_sn

varchar

256

√

null

machine_model

varchar

256

√

null

machine_make

varchar

256

√

null

machine_category

int4

√

null

machine_type

int4

√

null

machine_type_name

varchar

256

√

null

tracker_teltonika_sn

text

2147483647

√

null

tracker_hiboo_name

varchar

256

√

null

last_position

geometry

2147483647

√

null

last_position_time

timestamptz

35,6

√

null

site_name

varchar

256

√

null

cost_per_hour

int2

√

null

theoretical_consumption

float8

17,17

√

null

kg_co2_per_liter

float8

17,17

√

null

tonne

float8

17,17

√

null

volume

float8

17,17

√

null

target_dump_time

int2

√

null

target_time_per_bucket

int2

√

null

is_deleted

bool

√

null

scraper_pusher

bool

√

null

Relationships

Close relationships within degrees of separation

View Definition

SELECT DISTINCT sm.id AS machine_id,
    sm.tenant_id,
    sm.name AS machine_name,
    sm.serial_number AS machine_sn,
    sm.model AS machine_model,
    sm.make AS machine_make,
    sm.category AS machine_category,
    sm.type AS machine_type,
    smt.name AS machine_type_name,
    smtt.tracker_serial AS tracker_teltonika_sn,
    she.name AS tracker_hiboo_name,
    f.geometry AS last_position,
    f.valid_from AS last_position_time,
    site.name AS site_name,
        CASE
            WHEN ((sm.type = 1) OR (sm.type = 7)) THEN truck_parameters.cost_per_hour
            WHEN (sm.type = 2) THEN loader_parameters.cost_per_hour
            WHEN (sm.type = 3) THEN dozer_parameters.cost_per_hour
            WHEN (sm.type = 6) THEN scraper_parameters.cost_per_hour
            ELSE other_parameters.cost_per_hour
        END AS cost_per_hour,
        CASE
            WHEN ((sm.type = 1) OR (sm.type = 7)) THEN truck_parameters.theoretical_consumption
            WHEN (sm.type = 2) THEN loader_parameters.theoretical_consumption
            WHEN (sm.type = 3) THEN dozer_parameters.theoretical_consumption
            WHEN (sm.type = 6) THEN scraper_parameters.theoretical_consumption
            ELSE other_parameters.theoretical_consumption
        END AS theoretical_consumption,
        CASE
            WHEN ((sm.type = 1) OR (sm.type = 7)) THEN truck_parameters.kg_co2_per_liter
            WHEN (sm.type = 2) THEN loader_parameters.kg_co2_per_liter
            WHEN (sm.type = 3) THEN dozer_parameters.kg_co2_per_liter
            WHEN (sm.type = 6) THEN scraper_parameters.kg_co2_per_liter
            ELSE other_parameters.kg_co2_per_liter
        END AS kg_co2_per_liter,
        CASE
            WHEN ((sm.type = 1) OR (sm.type = 7)) THEN truck_parameters.tonne
            WHEN (sm.type = 2) THEN loader_parameters.tonne
            WHEN (sm.type = 6) THEN scraper_parameters.tonne
            ELSE NULL::double precision
        END AS tonne,
        CASE
            WHEN ((sm.type = 1) OR (sm.type = 7)) THEN truck_parameters.volume
            WHEN (sm.type = 2) THEN loader_parameters.volume
            WHEN (sm.type = 6) THEN scraper_parameters.volume
            ELSE NULL::double precision
        END AS volume,
        CASE
            WHEN ((sm.type = 1) OR (sm.type = 7)) THEN truck_parameters.target_dump_time
            WHEN (sm.type = 6) THEN scraper_parameters.target_dump_time
            ELSE NULL::smallint
        END AS target_dump_time,
        CASE
            WHEN (sm.type = 2) THEN loader_parameters.target_time_per_bucket
            ELSE NULL::smallint
        END AS target_time_per_bucket,
        CASE
            WHEN (sm.deleted_at IS NULL) THEN false
            ELSE true
        END AS is_deleted,
        CASE
            WHEN (sm.type = 3) THEN dozer_parameters.scraper_pusher
            ELSE NULL::boolean
        END AS scraper_pusher
   FROM ((((((((((sms_machine sm
     LEFT JOIN ( SELECT smtt_1.machine_id,
            smtt_1.tracker_serial,
            smtt_1.tenant_id,
            smtt_1.valid_from,
            smtt_1.valid_to,
            smtt_1.deleted_at,
            smtt_1.state,
            smtt_1.created_at,
            smtt_1.id
           FROM sms_machine_teltonika_tracker smtt_1
          WHERE ((((smtt_1.valid_from <= now()) AND (smtt_1.valid_to > now())) OR ((smtt_1.valid_from <= now()) AND (smtt_1.valid_to IS NULL))) AND (smtt_1.deleted_at IS NULL))
          ORDER BY smtt_1.valid_from) smtt ON ((sm.id = smtt.machine_id)))
     LEFT JOIN features f ON ((sm.feature_id = f.id)))
     LEFT JOIN ( SELECT smsa.id,
            smsa.tenant_id,
            smsa.machine_id,
            smsa.site_id,
            smsa.created_at,
            smsa.created_by,
            smsa.updated_at,
            smsa.updated_by,
            smsa.is_system,
            smsa.deleted_at,
            smsa.valid_from,
            smsa.valid_to,
            ss.name
           FROM (sms_machine_site_association smsa
             JOIN sms_site ss ON ((smsa.site_id = ss.id)))
          WHERE ((((smsa.valid_from <= now()) AND (smsa.valid_to > now())) OR ((smsa.valid_from <= now()) AND (smsa.valid_to IS NULL))) AND (smsa.deleted_at IS NULL))
          ORDER BY smsa.valid_from) site ON ((site.machine_id = sm.id)))
     LEFT JOIN ( SELECT stp.id,
            stp.tenant_id,
            stp.machine_id,
            stp.valid_from,
            stp.valid_to,
            stp.deleted_at,
            stp.cost_per_hour,
            stp.theoretical_consumption,
            stp.kg_co2_per_liter,
            stp.target_dump_time,
            stp.tonne,
            stp.volume,
            stp.created_at,
            stp.created_by,
            stp.updated_at,
            stp.updated_by,
            stp.is_system
           FROM sms_truck_parameters stp
          WHERE ((((stp.valid_from <= now()) AND (stp.valid_to > now())) OR ((stp.valid_from <= now()) AND (stp.valid_to IS NULL))) AND (stp.deleted_at IS NULL))
          ORDER BY stp.valid_from) truck_parameters ON ((truck_parameters.machine_id = sm.id)))
     LEFT JOIN ( SELECT slp.id,
            slp.tenant_id,
            slp.machine_id,
            slp.valid_from,
            slp.valid_to,
            slp.deleted_at,
            slp.cost_per_hour,
            slp.theoretical_consumption,
            slp.kg_co2_per_liter,
            slp.target_time_per_bucket,
            slp.tonne,
            slp.volume,
            slp.created_at,
            slp.created_by,
            slp.updated_at,
            slp.updated_by,
            slp.is_system
           FROM sms_loader_parameters slp
          WHERE ((((slp.valid_from <= now()) AND (slp.valid_to > now())) OR ((slp.valid_from <= now()) AND (slp.valid_to IS NULL))) AND (slp.deleted_at IS NULL))
          ORDER BY slp.valid_from) loader_parameters ON ((loader_parameters.machine_id = sm.id)))
     LEFT JOIN ( SELECT ssp.id,
            ssp.tenant_id,
            ssp.machine_id,
            ssp.valid_from,
            ssp.valid_to,
            ssp.deleted_at,
            ssp.cost_per_hour,
            ssp.theoretical_consumption,
            ssp.kg_co2_per_liter,
            ssp.target_dump_time,
            ssp.tonne,
            ssp.volume,
            ssp.created_at,
            ssp.created_by,
            ssp.updated_at,
            ssp.updated_by,
            ssp.is_system
           FROM sms_scraper_parameters ssp
          WHERE ((((ssp.valid_from <= now()) AND (ssp.valid_to > now())) OR ((ssp.valid_from <= now()) AND (ssp.valid_to IS NULL))) AND (ssp.deleted_at IS NULL))
          ORDER BY ssp.valid_from) scraper_parameters ON ((scraper_parameters.machine_id = sm.id)))
     LEFT JOIN ( SELECT sdp.id,
            sdp.tenant_id,
            sdp.machine_id,
            sdp.valid_from,
            sdp.valid_to,
            sdp.deleted_at,
            sdp.cost_per_hour,
            sdp.theoretical_consumption,
            sdp.kg_co2_per_liter,
            sdp.scraper_pusher,
            sdp.created_at,
            sdp.created_by,
            sdp.updated_at,
            sdp.updated_by,
            sdp.is_system
           FROM sms_dozer_parameters sdp
          WHERE ((((sdp.valid_from <= now()) AND (sdp.valid_to > now())) OR ((sdp.valid_from <= now()) AND (sdp.valid_to IS NULL))) AND (sdp.deleted_at IS NULL))
          ORDER BY sdp.valid_from) dozer_parameters ON ((dozer_parameters.machine_id = sm.id)))
     LEFT JOIN ( SELECT sop.id,
            sop.tenant_id,
            sop.machine_id,
            sop.valid_from,
            sop.valid_to,
            sop.deleted_at,
            sop.cost_per_hour,
            sop.theoretical_consumption,
            sop.kg_co2_per_liter,
            sop.created_at,
            sop.created_by,
            sop.updated_at,
            sop.updated_by,
            sop.is_system
           FROM sms_other_parameters sop
          WHERE ((((sop.valid_from <= now()) AND (sop.valid_to > now())) OR ((sop.valid_from <= now()) AND (sop.valid_to IS NULL))) AND (sop.deleted_at IS NULL))
          ORDER BY sop.valid_from) other_parameters ON ((other_parameters.machine_id = sm.id)))
     LEFT JOIN sms_hiboo_equipment she ON (((sm.serial_number)::text = (she.serial_number)::text)))
     JOIN sms_machine_type smt ON ((sm.type = smt.id)));

sms_machines_trackers

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views