vw_su_commands

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

serialnumber

varchar

64

√

null

device_rnt.serialnumber

Implied Constraint

R

alias

varchar

128

√

null

lastgpsdate

timestamp

29,6

√

null

rampas

text

2147483647

√

null

description

text

2147483647

√

null

commanddate

timestamp

29,6

√

null

username

varchar

64

√

null

delay

interval

49,6

√

null

pendiente

int4

10

√

null

Relationships

Close relationships within degrees of separation

View Definition

WITH tabla AS (
         SELECT DISTINCT ON (d.serialnumber) d.serialnumber,
            un.alias,
            timezone('AMERICA/MEXICO_CITY'::text, d.lastgpsdate) AS lastgpsdate,
                CASE
                    WHEN (d.idmodeldevice = 1) THEN
                    CASE
                        WHEN (d.ignition_on = false) THEN 'APAGADAS'::text
                        WHEN ((d.ignition_on = true) AND ("substring"((d.others -> 'switches'::text), 3, 1) = '1'::text)) THEN 'HABILITADAS'::text
                        WHEN ((d.ignition_on = true) AND ("substring"((d.others -> 'switches'::text), 3, 1) = '0'::text)) THEN 'INHABILITADAS'::text
                        ELSE 'NO DISPONIBLE'::text
                    END
                    WHEN (d.idmodeldevice = 4) THEN
                    CASE
                        WHEN (d.ignition_on = false) THEN 'APAGADAS'::text
                        WHEN ((d.ignition_on = true) AND ("substring"((d.others -> 'switches'::text), 15, 1) = '0'::text)) THEN 'HABILITADAS'::text
                        WHEN ((d.ignition_on = true) AND ("substring"((d.others -> 'switches'::text), 15, 1) = '1'::text)) THEN 'INHABILITADAS'::text
                        ELSE 'NO DISPONIBLE'::text
                    END
                    ELSE NULL::text
                END AS rampas,
                CASE
                    WHEN ((c.idcommand = 1) OR (c.idcommand = 3)) THEN 'INHABILITAR RAMPAS'::text
                    ELSE 'HABILITAR RAMPAS'::text
                END AS description,
            timezone('AMERICA/MEXICO_CITY'::text, ce.senddate) AS commanddate,
            u.username
           FROM ((((device d
             LEFT JOIN commandexec ce ON ((d.iddevice = ce.iddevice)))
             LEFT JOIN command c ON ((c.idcommand = ce.idcommand)))
             LEFT JOIN mayauser u ON ((u.idmayauser = ce.iduser)))
             LEFT JOIN unit un ON ((un.idunit = d.idunit)))
          WHERE (d.idtechnicalaccount = ANY (ARRAY[4, 6616]))
          ORDER BY d.serialnumber, (timezone('AMERICA/MEXICO_CITY'::text, ce.senddate)) DESC
        ), tabla2 AS (
         SELECT tabla.serialnumber,
            tabla.alias,
            tabla.lastgpsdate,
            tabla.rampas,
            tabla.description,
            tabla.commanddate,
            tabla.username,
                CASE
                    WHEN ((tabla.description = 'HABILITAR RAMPAS'::text) AND (tabla.rampas = 'DESHABILITADAS'::text)) THEN (timezone('AMERICA/MEXICO_CITY'::text, now()) - tabla.commanddate)
                    WHEN ((tabla.description = 'INHABILITAR RAMPAS'::text) AND (tabla.rampas = 'HABILITADAS'::text)) THEN (timezone('AMERICA/MEXICO_CITY'::text, now()) - tabla.commanddate)
                    ELSE '00:00:00'::interval
                END AS delay,
                CASE
                    WHEN ((tabla.description = 'HABILITAR RAMPAS'::text) AND (tabla.rampas = 'DESHABILITADAS'::text)) THEN 1
                    WHEN ((tabla.description = 'INHABILITAR RAMPAS'::text) AND (tabla.rampas = 'HABILITADAS'::text)) THEN 1
                    ELSE 0
                END AS pendiente
           FROM tabla
          ORDER BY tabla.commanddate DESC, tabla.lastgpsdate DESC
        )
 SELECT tabla2.serialnumber,
    tabla2.alias,
    tabla2.lastgpsdate,
    tabla2.rampas,
    tabla2.description,
    tabla2.commanddate,
    tabla2.username,
    tabla2.delay,
    tabla2.pendiente
   FROM tabla2
  ORDER BY tabla2.delay DESC;

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views