DROP FUNCTION public._corrigi_data();

DROP FUNCTION public._odometro_tudo();

DROP FUNCTION public.asdf();

DROP FUNCTION public.get_delivery_sms(psms text);

DROP FUNCTION public.get_value(text);

DROP FUNCTION public.mce_device_fnc2(pidmonitoringcenter bigint);

DROP FUNCTION public.rep_data_util_fnc(pdate_ini varchar, pdate_fim varchar);

DROP FUNCTION public.reprocessa_transaction(pidtransaction bigint);

DROP FUNCTION public.set_fuel(ptransaction bigint, pdevice bigint, pcommand varchar);

DROP FUNCTION public.set_refuel(ptransaction bigint, pdevice bigint, pcommand varchar);

DROP FUNCTION public.set_value(text, text);

DROP FUNCTION public.set_workday(ptransaction bigint, pdevice bigint, pcommand varchar);

DROP FUNCTION public.utl_interval_to_hours_fnc(pinterval interval);

DROP FUNCTION public."utl_interval_to_hours_fnc(pinterval interval)"(pinterval interval);

DROP FUNCTION public.vlt_agrupa_transaction01(piddevice bigint, pidtransaction bigint, pidrecordtype bigint);

DROP FUNCTION public.vlt_agrupa_transaction2(piddevice bigint, pidtransaction bigint, pidrecordtype bigint);

DROP FUNCTION public.vlt_agrupa_transaction_teste(piddevice bigint, pidtransaction bigint, pidrecordtype bigint);

DROP FUNCTION public.vlt_command_sms_fnc();

DROP FUNCTION public.vlt_device_fnc(pidcustomer bigint, psearch varchar);

DROP FUNCTION public.vlt_device_fnc(pidcustomer bigint);

DROP FUNCTION public.vlt_device_fnc_point(pidcustomer bigint, piduser bigint, psearch varchar);

DROP FUNCTION public.vlt_device_has_alert2_fnc(piddevice bigint);

DROP FUNCTION public.vlt_driver_workday_screen_fnc(piddriver bigint, prefmonth text);

DROP FUNCTION public.vlt_driver_workday_update_hours_fnc(piddriverworkdayold bigint, piddriverworkdaynew bigint);

DROP FUNCTION public.vlt_last_invalid_tran_dev_fnc(piddevice bigint);

DROP FUNCTION public.vlt_last_position_fnc2(pidcustomer numeric);

DROP FUNCTION public.vlt_last_position_fnc_ant(pidcustomer numeric);

DROP FUNCTION public.vlt_last_position_fnc_bkp(pidcustomer numeric);

DROP FUNCTION public.vlt_last_position_fnc_bkp2(pidcustomer numeric);

DROP FUNCTION public.vlt_list_coordinates_fnc2(piddevice numeric, pinitial_date timestamptz, pfinal_date timestamptz, precord_type varchar);

DROP FUNCTION public.vlt_list_coordinates_fnc_a(piddevice numeric, pinitial_date timestamptz, pfinal_date timestamptz, precord_type varchar);

DROP FUNCTION public.vlt_odometer_transaction_teste(piddevice bigint, pcommanddate timestamp, pini boolean);

DROP FUNCTION public.vlt_socket_con_gsn_prc2(pcommand varchar);

DROP FUNCTION public.vlt_transaction_driver_fnc();

DROP FUNCTION public.vlt_verifica_modelo();

DROP SEQUENCE public.vlt_components_idcomponents_seq;

DROP SEQUENCE public.vlt_time_zone_interval_idtimezoneinterval_seq;

DROP SEQUENCE public.vlt_time_zone_local_idtimezonelocal_seq;

DROP INDEX public.par_access_index01;

DROP INDEX public.par_customer_idx;

ALTER TABLE public.rba_menu
  DROP CONSTRAINT rba_menu_index01 RESTRICT;

DROP INDEX public.vlt_alert_index01;

DROP INDEX public.vlt_alert_index02;

ALTER TABLE public.vlt_alert_customer
  DROP CONSTRAINT vlt_alert_customer_uk RESTRICT;

DROP INDEX public.vlt_alert_user_idx2;

ALTER TABLE public.vlt_command
  DROP COLUMN sms_code;

ALTER TABLE public.vlt_command
  DROP COLUMN sms_sending;

ALTER TABLE public.vlt_command
  DROP COLUMN sms_delivery;

DROP TRIGGER vlt_command_sms_trg ON public.vlt_command;

ALTER TABLE public.vlt_country
  DROP CONSTRAINT vlt_country_pk RESTRICT;

ALTER TABLE public.vlt_device
  DROP COLUMN blocked;

DROP INDEX public.vlt_device_idx5;

DROP INDEX public.vlt_device_search_idx;

DROP INDEX public.vlt_device_interval_index01;

DROP INDEX public.vlt_device_interval_index02;

DROP TRIGGER vlt_device_model_verif_trg ON public.vlt_device_model;

DROP INDEX public.vlt_device_odometer_index01;

DROP INDEX public.vlt_driver_record_idx01;

ALTER TABLE public.vlt_driver_workday
  DROP COLUMN idtransaction;

DROP INDEX public.vlt_driver_workday_idx;

DROP TRIGGER vlt_driver_workday_active_trg ON public.vlt_driver_workday;

ALTER TABLE public.vlt_fuel
  DROP COLUMN min_value;

ALTER TABLE public.vlt_fuel
  DROP COLUMN max_variation;

ALTER TABLE public.vlt_fuel
  DROP COLUMN alert_min;

ALTER TABLE public.vlt_fuel
  DROP COLUMN alert_variation;

ALTER TABLE public.vlt_fuel
  DROP COLUMN idfuelvalue_previous;

ALTER TABLE public.vlt_fuel_value
  DROP CONSTRAINT vlt_fuel_value_fk01 RESTRICT;

DROP INDEX public.fki_vlt_fuel_value_fk01;

DROP INDEX public.vlt_interest_point_index01;

DROP INDEX public.vlt_model_command_idx1;

DROP INDEX public.vlt_model_cmd_param_ixd1;

DROP TRIGGER vlt_model_command_param_trg ON public.vlt_model_command_param;

ALTER TABLE public.vlt_parameter
  DROP COLUMN ref_date_num;

DROP INDEX public.vlt_port_status_index01;

DROP INDEX public.vlt_port_status_index02;

ALTER TABLE public.vlt_refuel_station
  DROP COLUMN station_name;

ALTER TABLE public.vlt_transaction
  DROP COLUMN temp_data;

ALTER TABLE public.vlt_transaction
  DROP COLUMN temp_data2;

ALTER TABLE public.vlt_transaction
  DROP COLUMN duplicate;

ALTER TABLE public.vlt_transaction
  DROP COLUMN address_updated;

DROP INDEX public.vlt_transaction_index04;

DROP INDEX public.vlt_transaction_index06;

DROP INDEX public.vlt_transaction_index07;

DROP INDEX public.vlt_transaction_index08;

DROP INDEX public.vlt_transaction_driver_idx1;

DROP INDEX public.vlt_transaction_driver_idx2;

DROP INDEX public.vlt_transaction_driver_idx3;

DROP TABLE public.bktran_null;

DROP TABLE public.quantidade;

DROP TABLE public.temp;

DROP TABLE public.temp2032;

DROP TABLE public.viddevice;

DROP TABLE public.vidtransaction;

DROP TABLE public.vlast_valid_idodometer;

DROP TABLE public.vlimit_group;

DROP TABLE public.vlt_alert_backup;

DROP TABLE public.vlt_components;

DROP TABLE public.vlt_time_zone;

DROP TABLE public.vlt_time_zone_interval;

DROP VIEW public.rep_online_offline_vw;

DROP VIEW public.rep_route_vw;

DROP VIEW public.ultima_comunicacao;

DROP VIEW public.ultimo_erro;

DROP VIEW public.vlt_device_vw_ant;

DROP VIEW public.vlt_device_vw_old;

CREATE TABLESPACE crm_data
  OWNER postgres
  LOCATION E'/usr/local/pgsql/index/crm_data';

CREATE TABLESPACE crm_index
  OWNER postgres
  LOCATION E'/usr/local/pgsql/index/crm_index';

CREATE TABLESPACE dr_data
  OWNER pgsql
  LOCATION E'/home/postgres/index/data';

CREATE TABLESPACE dr_index
  OWNER pgsql
  LOCATION E'/home/postgres/index/index';

CREATE TABLESPACE entrega_data
  OWNER pgsql
  LOCATION E'/home/postgres/index/entrega_data';

CREATE TABLESPACE entrega_index
  OWNER pgsql
  LOCATION E'/home/postgres/index/entrega_index';

CREATE TABLESPACE garagem_data
  OWNER pgsql
  LOCATION E'/home/postgres/index/garagem_data';

CREATE TABLESPACE garagem_index
  OWNER pgsql
  LOCATION E'/home/postgres/index/garagem_index';

CREATE TABLESPACE gerencial_data
  OWNER pgsql
  LOCATION E'/home/postgres/index/gerencial_data';

CREATE TABLESPACE gerencial_index
  OWNER pgsql
  LOCATION E'/home/postgres/index/gerencial_index';

CREATE OR REPLACE FUNCTION public.agrupa_tudo (
)
RETURNS integer AS
$body$
declare
  result        bigint;
  reg table01%ROWTYPE;
begin
  FOR reg in select distinct iddevice,idrecordtype
  from vlt_transaction
  LOOP
    SELECT  public.vlt_agrupa_transaction(reg.iddevice, NULL, reg.idrecordtype)
      into result;
  END LOOP;

  return result;
end
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE OR REPLACE FUNCTION public.dataf (
  num integer = 0
)
RETURNS varchar AS
$body$
declare
  Year  integer;
  Month integer;
  Day   integer;
  val   integer;
begin
 val := 5454 + num;
 year := TRUNC(val / 417);
 month := TRUNC((val - year * 417) / 32);
 day := val - year * 417 - month * 32;
 year:=year+2000;
 return(day || '/' || month || '/' ||year);
end
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE FUNCTION public.fin_bank_update_search_fnc (
)
RETURNS trigger AS
$body$
BEGIN
   perform fin_update_bank_search_fnc(new.idbank);
   return new;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

CREATE FUNCTION public.fin_update_bank_search_fnc (
  pidbank bigint
)
RETURNS pg_catalog.void AS
$body$
BEGIN
   update fin_bank
      set bank_search = to_tsvector(utl_text_search((coalesce(fin_bank.bank, '') || ' ' || coalesce(fin_bank.cnpj, '') || ' ' || coalesce(fin_bank.code, ''))))
    where idbank = pidbank;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

CREATE OR REPLACE FUNCTION public.ini_device (
  pdevice varchar
)
RETURNS integer AS
$body$
declare
  vdevice bigint;
begin
  if pdevice in ('357805023984942','861001003748320') then
--  RAISE EXCEPTION 'BLOQUEADO';
     select *
       into vdevice
       from ini_device_teste2_1(pdevice);
  else
     select *
       into vdevice
       from ini_device_teste2_0(pdevice);
--       from ini_device_producao(pdevice);
  end if;
  return vdevice;
end
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE FUNCTION public.ini_device_teste2 (
  pdevice varchar
)
RETURNS integer AS
$body$
declare
  reg    RECORD;
--  vvalid boolean;
  viddevice bigint;
vcommand_date timestamp;
  vidcustomer bigint;
  vbit_connected boolean;
  vdif integer;
  vlast_valid_idodometer bigint;
vlast_valid_odometer bigint;
vDISTANCE_TYPE char(1);
       vlimit_group_on integer;
       vlimit_group_off integer;
vvehicle_code varchar;
Vspeed_limit NUMERIC;
Vvalr_min_temperature NUMERIC;
Vvalr_max_temperature NUMERIC;
Vvalr_min_preassure NUMERIC;
Vvalr_max_preassure NUMERIC;
vidtpmsmodel numeric;
begin
-- tipos de registros
   create local temp table record_type on commit preserve rows as
   select *
     from vlt_record_type;
-- consulta device
 select iddevice,idcustomer,bit_connected
          , case when s.daylight_saving
                        then s.difference_daylight_saving
                        else s.difference
           end
           ,DISTANCE_TYPE
           ,limit_group_on
           ,limit_group_off
           , COALESCE(vehicle_code,D.CODE)
           ,speed_limit
           ,t.valr_min_temperature
           ,t.valr_max_temperature
           ,t.valr_min_preassure
           ,t.valr_max_preassure
           ,d.idtpmsmodel
into viddevice
   , vidcustomer
   , vbit_connected
   , vdif
   , vDISTANCE_TYPE
   , vlimit_group_on
   , vlimit_group_off
   , vvehicle_code
   , Vspeed_limit
   , Vvalr_min_temperature
   , Vvalr_max_temperature
   , Vvalr_min_preassure
   , Vvalr_max_preassure
   , vidtpmsmodel
   from vlt_device d,vlt_device_model m,VLT_SCHEDULE s,vlt_tpms_model t
  where code= pdevice
    and d.iddevicemodel=m.iddevicemodel
    and s.idschedule=d.idschedule
    and d.idtpmsmodel = t.id;

-- cria device
   if viddevice is null then
     SELECT nextval('vlt_device_iddevice_seq')
       into viddevice;
     select idcustomer_default
       into vidcustomer
      from vlt_parameter;
      insert into vlt_device(iddevice ,code   , description, idschedule, idfirmware, idfirmware_new, firmware_size, vehicle, idcustomer , idtpmsmodel, iddevicemodel, vehicle_code, device_search, tag , speed_limit)
                      values(viddevice,pdevice, pdevice    , 8         , 1         , null          , 0            , null   , vidcustomer, 1          , 1            , null        , null         , null, 0);
     vbit_connected:=false;
     vdif:=-3;
     vDISTANCE_TYPE:='G';
     vlimit_group_on:=20;
     vlimit_group_off:=200;
   end if;

   create local temp table alert_customer on commit preserve rows as
   select m.*
     from vlt_alert_customer m
    where m.idcustomer = vidcustomer;

-- tpms model
   create local temp table tpms_model on commit preserve rows as
   select m.*
     from vlt_tpms_model m
    where m.id= vidtpmsmodel;
-- device interval
   create local temp table device_interval on commit preserve rows as
   select m.*
     from vlt_device_interval m
    where iddevice  = viddevice;

-- pontos do device
   Create local temp table interest_point (
          idinterestpoint        bigint,
          active                 boolean,
          speed_alert            boolean,
          latitude               numeric,
          longitude              numeric,
          radius                 numeric,
          speed                  numeric,
          interest_point        varchar(200),
          alert                  boolean)on commit preserve rows;
   insert into interest_point(idinterestpoint,active,speed_alert,alert,latitude,longitude,radius,interest_point,speed)
   select p.idinterestpoint,false,false,false,latitude,longitude,radius,interest_point,speed from vlt_interest_point p
    where p.idcustomer = vidcustomer;
-- cercas do device
   Create local temp table fence (
          idfence        bigint,
          active         boolean,
          speed_alert    boolean,
          fence          varchar(200),
          speed numeric,
          alert          boolean)on commit preserve rows;
   insert into fence(idfence,active,speed_alert,alert,fence,speed)
   select p.idfence,false,false,false,fence,speed from vlt_fence p
    where p.idcustomer = vidcustomer;

-- tabela temporaria
create local temp table device(
       iddevice bigint,
       limit_group_on integer,
       limit_group_off integer,
       dif integer,
       speed_limit_alert BOOLEAN DEFAULT FALSE,
       speed_limit NUMERIC,
       idcustomer bigint,
       vehicle_code            varchar(20),
       last_transaction_sequence   bigint,
       last_command_date           timestamp WITHOUT TIME ZONE,
       last_latitude               numeric,
       last_longitude              numeric,
       last_valid_idodometer       bigint,
       last_valid_odometer         bigint,
       DISTANCE_TYPE char(1),
       last_iddriver               bigint,
       bit_connected               boolean,
       last_connected              boolean,
       last_valid                  boolean,
       valr_min_temperature NUMERIC DEFAULT 0,
       valr_max_temperature NUMERIC DEFAULT 0,
       valr_min_preassure NUMERIC DEFAULT 0,
       valr_max_preassure NUMERIC DEFAULT 0,
       alert_connected boolean default false
       )on commit preserve rows;

-- informações do device
 insert into device(speed_limit ,vehicle_code ,iddevice ,idcustomer ,bit_connected ,dif ,last_transaction_sequence,last_command_date,last_latitude,last_longitude,last_iddriver,last_connected,last_valid,limit_group_on,limit_group_off,valr_min_temperature,valr_max_temperature,valr_min_preassure,valr_max_preassure,alert_connected)
             values(Vspeed_limit,COALESCE(vvehicle_code,PDEVICE),viddevice,vidcustomer,vbit_connected,vdif,0                        ,null             ,0            ,0             ,null         ,false         ,false,vlimit_group_on,vlimit_group_off,vvalr_min_temperature,Vvalr_max_temperature,Vvalr_min_preassure,Vvalr_max_preassure,exists(select null from device_interval));
-- informações de odometro
select COALESCE(odometer,0)
     , command_date
  into vlast_valid_odometer
     , vcommand_date
  from vlt_transaction
 where iddevice = viddevice
   and valid
   and idrecordtype<>12
 order by idtransaction desc
 limit 1;

      select iddeviceodometer
        into vlast_valid_idodometer
        from vlt_device_odometer o
       where o.odometer_date<vcommand_date
         and o.iddevice = viddevice
       order by odometer_date desc
       limit 1;

      update device set
            last_valid_idodometer = vlast_valid_idodometer,
            last_valid_odometer   = vlast_valid_odometer;

   create local temp table record_type_last on commit preserve rows as
   select r.idrecordtype
        , r.idtransaction
        , t.command_date
        , t.connected
        , t.latitude
        , t.longitude
        , COALESCE(t.idtransaction_group,t.idtransaction) as idtransaction_group
        , COALESCE(g.command_date,t.command_date) as command_date_group
        , COALESCE(g.connected,t.connected) as connected_group
        , COALESCE(g.latitude,t.latitude) as latitude_group
        , COALESCE(g.longitude,t.longitude) as longitude_group
     from (select idrecordtype
                , (select idtransaction
                    from vlt_transaction t
                   where t.iddevice=viddevice
                     and t.idrecordtype=record_type.idrecordtype
                  --   and valid
                   order by idtransaction desc
                    limit 1) idtransaction
            from record_type where idrecordtype not in(12))r left join vlt_transaction t left join vlt_transaction g
                                                                      on t.idtransaction_group=g.idtransaction
                                          on r.idtransaction = t.idtransaction;
-- last transaction
 for reg in select transaction_sequence,
                   command_date,
                   latitude,
                   longitude,
                   iddriver,
                   connected,
                   valid
              from vlt_transaction
             where idtransaction=(select max(idtransaction) from record_type_last)
 loop
      update device set
            last_transaction_sequence = reg.transaction_sequence,
            last_command_date         = reg.command_date,
            last_latitude             = reg.latitude,
            last_longitude            = reg.longitude,
            last_iddriver             = reg.iddriver,
            last_connected            = reg.connected,
            last_valid                = reg.valid;
 end loop;

   return(0);
end
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

CREATE OR REPLACE FUNCTION public.ini_device_teste2_1 (
  pdevice varchar
)
RETURNS integer AS
$body$
declare
  reg    RECORD;
--  vvalid boolean;
  viddevice bigint;
vcommand_date timestamp;
  vidcustomer bigint;
  vbit_connected boolean;
  vdif integer;
  vlast_valid_idodometer bigint;
vlast_valid_odometer bigint;
vDISTANCE_TYPE char(1);
       vlimit_group_on integer;
       vlimit_group_off integer;
vvehicle_code varchar;
Vspeed_limit NUMERIC;
Vvalr_min_temperature NUMERIC;
Vvalr_max_temperature NUMERIC;
Vvalr_min_preassure NUMERIC;
Vvalr_max_preassure NUMERIC;
vidtpmsmodel numeric;
vblocked boolean;
begin
-- tipos de registros
   create local temp table record_type on commit preserve rows as
   select *
     from vlt_record_type;
-- consulta device
 select iddevice,idcustomer,bit_connected
          , case when s.daylight_saving
                        then s.difference_daylight_saving
                        else s.difference
           end
           ,DISTANCE_TYPE
           ,limit_group_on
           ,limit_group_off
           , COALESCE(vehicle_code,D.CODE)
           ,speed_limit
           ,t.valr_min_temperature
           ,t.valr_max_temperature
           ,t.valr_min_preassure
           ,t.valr_max_preassure
           ,d.idtpmsmodel
           ,blocked
into viddevice
   , vidcustomer
   , vbit_connected
   , vdif
   , vDISTANCE_TYPE
   , vlimit_group_on
   , vlimit_group_off
   , vvehicle_code
   , Vspeed_limit
   , Vvalr_min_temperature
   , Vvalr_max_temperature
   , Vvalr_min_preassure
   , Vvalr_max_preassure
   , vidtpmsmodel
   , vblocked
   from vlt_device d,vlt_device_model m,VLT_SCHEDULE s,vlt_tpms_model t
  where code= pdevice
    and d.iddevicemodel=m.iddevicemodel
    and s.idschedule=d.idschedule
    and d.idtpmsmodel = t.id;
    if  vblocked then
       RAISE EXCEPTION 'BLOQUEADO';
    end if;

-- cria device
   if viddevice is null then
     SELECT nextval('vlt_device_iddevice_seq')
       into viddevice;
     select idcustomer_default
       into vidcustomer
      from vlt_parameter;
      insert into vlt_device(iddevice ,code   , description, idschedule, idfirmware, idfirmware_new, firmware_size, vehicle, idcustomer , idtpmsmodel, iddevicemodel, vehicle_code, device_search, tag , speed_limit)
                      values(viddevice,pdevice, pdevice    , 8         , 1         , null          , 0            , null   , vidcustomer, 1          , 3            , null        , null         , null, 0);
     vbit_connected:=false;
     vdif:=-3;
     vDISTANCE_TYPE:='G';
     vlimit_group_on:=20;
     vlimit_group_off:=200;
   end if;

   create local temp table alert_customer on commit preserve rows as
   select m.*
     from vlt_alert_customer m
    where m.idcustomer = vidcustomer;

-- tpms model
   create local temp table tpms_model on commit preserve rows as
   select m.*
     from vlt_tpms_model m
    where m.id= vidtpmsmodel;
-- device interval
   create local temp table device_interval on commit preserve rows as
   select m.*
     from vlt_device_interval m
    where iddevice  = viddevice;

-- pontos do device
   Create local temp table interest_point (
          idinterestpoint        bigint,
          active                 boolean,
          speed_alert            boolean,
          latitude               numeric,
          longitude              numeric,
          radius                 numeric,
          speed                  numeric,
          interest_point        varchar(200),
          alert                  boolean)on commit preserve rows;

   insert into interest_point(idinterestpoint,active,speed_alert,alert,latitude,longitude,radius,interest_point,speed)
   select p.idinterestpoint,false,false,false,latitude,longitude,radius,interest_point,speed from vlt_interest_point p
    where p.idcustomer = vidcustomer;
-- cercas do device
   Create local temp table fence (
          idfence        bigint,
          active         boolean,
          speed_alert    boolean,
          fence          varchar(200),
          speed numeric,
          alert          boolean)on commit preserve rows;
   insert into fence(idfence,active,speed_alert,alert,fence,speed)
   select p.idfence,false,false,false,fence,speed from vlt_fence p
    where p.idcustomer = vidcustomer;

-- tabela temporaria
create local temp table device(
       iddevice bigint,
       limit_group_on integer,
       limit_group_off integer,
       dif integer,
       speed_limit_alert BOOLEAN DEFAULT FALSE,
       speed_limit NUMERIC,
       idcustomer bigint,
       vehicle_code            varchar(20),
       last_transaction_sequence   bigint,
       last_command_date           timestamp WITHOUT TIME ZONE,
       last_latitude               numeric,
       last_longitude              numeric,
       last_valid_idodometer       bigint,
       last_valid_odometer         bigint,
       DISTANCE_TYPE char(1),
       last_iddriver               bigint,
       bit_connected               boolean,
       last_connected              boolean,
       last_valid                  boolean,
       valr_min_temperature NUMERIC DEFAULT 0,
       valr_max_temperature NUMERIC DEFAULT 0,
       valr_min_preassure NUMERIC DEFAULT 0,
       valr_max_preassure NUMERIC DEFAULT 0,
       alert_connected boolean default false,
       iddriverrecord bigint
       )on commit preserve rows;

-- informações do device
 insert into device(speed_limit ,vehicle_code ,iddevice ,idcustomer ,bit_connected ,dif ,last_transaction_sequence,last_command_date,last_latitude,last_longitude,last_iddriver,last_connected,last_valid,limit_group_on,limit_group_off,valr_min_temperature,valr_max_temperature,valr_min_preassure,valr_max_preassure,alert_connected,iddriverrecord)
             values(Vspeed_limit,COALESCE(vvehicle_code,PDEVICE),viddevice,vidcustomer,vbit_connected,vdif,0                        ,null             ,0            ,0             ,null         ,false         ,false,vlimit_group_on,vlimit_group_off,vvalr_min_temperature,Vvalr_max_temperature,Vvalr_min_preassure,Vvalr_max_preassure,exists(select null from device_interval),(select max(iddriverrecord) from vlt_driver_record where iddevice=viddevice));
-- informações de odometro
select COALESCE(odometer,0)
     , command_date
  into vlast_valid_odometer
     , vcommand_date
  from vlt_transaction
 where iddevice = viddevice
   and valid
   and idrecordtype<>12
 order by idtransaction desc
 limit 1;

      select iddeviceodometer
        into vlast_valid_idodometer
        from vlt_device_odometer o
       where o.odometer_date<vcommand_date
         and o.iddevice = viddevice
       order by odometer_date desc
       limit 1;

      update device set
            last_valid_idodometer = vlast_valid_idodometer,
            last_valid_odometer   = vlast_valid_odometer;

   create local temp table record_type_last on commit preserve rows as
   select r.idrecordtype
        , r.idtransaction
        , t.command_date
        , t.connected
        , t.latitude
        , t.longitude
        , COALESCE(t.idtransaction_group,t.idtransaction) as idtransaction_group
        , COALESCE(g.command_date,t.command_date) as command_date_group
        , COALESCE(g.connected,t.connected) as connected_group
        , COALESCE(g.latitude,t.latitude) as latitude_group
        , COALESCE(g.longitude,t.longitude) as longitude_group
     from (select idrecordtype
                , (select idtransaction
                    from vlt_transaction t
                   where t.iddevice=viddevice
                     and t.idrecordtype=record_type.idrecordtype
                  --   and valid
                   order by idtransaction desc
                    limit 1) idtransaction
            from record_type where idrecordtype not in(12))r left join vlt_transaction t left join vlt_transaction g
                                                                      on t.idtransaction_group=g.idtransaction
                                          on r.idtransaction = t.idtransaction;
-- last transaction
 for reg in select transaction_sequence,
                   command_date,
                   latitude,
                   longitude,
                   iddriver,
                   connected,
                   valid
              from vlt_transaction
             where idtransaction=(select max(idtransaction) from record_type_last)
 loop
      update device set
            last_transaction_sequence = reg.transaction_sequence,
            last_command_date         = reg.command_date,
            last_latitude             = reg.latitude,
            last_longitude            = reg.longitude,
            last_iddriver             = reg.iddriver,
            last_connected            = reg.connected,
            last_valid                = reg.valid;
 end loop;

   return(0);
end
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE FUNCTION public.mce_device_fnc (
  pidmonitoringcenter bigint,
  pidcustomer bigint
)
RETURNS TABLE (
  iddevice bigint,
  description varchar,
  valid boolean,
  devicetag text,
  tag text,
  latitude numeric,
  longitude numeric,
  vehicle_code varchar,
  idcustomer bigint,
  code varchar,
  device_search tsvector,
  has_battery boolean,
  has_analog_ports boolean,
  has_digital_ports boolean,
  has_tpms boolean,
  has_fuel boolean,
  has_temperature boolean,
  connected text,
  is_connected boolean,
  speed integer,
  command_date varchar,
  start_date varchar,
  driver varchar,
  address varchar,
  idtransaction bigint,
  online char,
  record_type varchar,
  has_alert boolean,
  active boolean,
  odometer numeric,
  customer varchar
) AS
$body$
declare
   devices bigint [];
   transactions bigint [];
   tempvalue bigint;
   rec record;
begin
   for rec in (select mc.iddevice from vlt_device_mc mc where mc.idmonitoringcenter = pidmonitoringcenter) loop
      transactions := array_append(transactions, (select l.idtransaction from vlt_last_transaction_fnc(rec.iddevice) l));
   end loop;

  return query select dev.iddevice
      ,dev.description
      ,tt.valid
      ,mc.devicetag
      ,dev.tag
      ,tt.latitude
      ,tt.longitude
      ,dev.vehicle_code
      ,dev.idcustomer
      ,dev.code
      ,dev.device_search
      ,devmodel.has_battery
      ,devmodel.has_analog_ports
      ,devmodel.has_digital_ports
      ,devmodel.has_tpms
      ,devmodel.has_fuel
      ,devmodel.has_temperature
      ,CASE WHEN tt.connected THEN 'Sim'::text ELSE 'Não'::text END AS connected
      ,tt.connected AS is_connected
      ,tt.speed
      ,to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS command_date
      ,to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS start_date
      ,(select drv.driver from vlt_driver drv where drv.iddriver = tt.iddriver) driver
      ,tt.address
      ,tt.idtransaction
      ,tt.online
      ,(select rt.description from vlt_record_type rt where rt.idrecordtype = tt.idrecordtype) record_type
      ,false AS has_alert
      ,dev.active
      ,round(tt.odometer::numeric / 1000::numeric, 1) AS odometer
      ,(select cus.customer from par_customer cus where cus.idcustomer = dev.idcustomer) customer
  from vlt_transaction tt
      ,vlt_device dev
      ,vlt_device_mc mc
      ,vlt_device_model devmodel
 where tt.idtransaction = any (transactions)
   and dev.iddevice = tt.iddevice
   and devmodel.iddevicemodel = dev.iddevicemodel
   and dev.active = true
   and dev.iddevice = mc.iddevice
   and mc.idmonitoringcenter = pidmonitoringcenter
   and case when pidcustomer is null
	    then true
	    else dev.idcustomer = pidcustomer
	end

 order by tt.command_date DESC;
         --,dev.vehicle_code
         --,dev.description;
end
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

CREATE OR REPLACE FUNCTION public.par_login_fnc (
  pdesc_uid varchar,
  pdesc_date varchar,
  pdesc_useragent varchar,
  pdesc_ip varchar
)
RETURNS TABLE (
  idcustomer bigint,
  iduser bigint,
  pdesc_key varchar,
  idseller bigint,
  systype varchar
) AS
$body$
declare
   viduser      bigint;
   vrbaprof_id  bigint;
   vidcustomer	bigint;
   vdthr_access timestamp without time zone;
   vidseller    bigint;
   vsystype     varchar;
   vcolor       varchar;
   vlogo        varchar;
   vstatus      varchar;
begin

   vdthr_access := current_timestamp;

   begin
      select par_user.iduser
           , par_user.rbaprof_id
           , par_user.status
        into viduser
           , vrbaprof_id
           , vstatus
        from par_user
       where md5(par_user.login || ':' || par_user.password || ':' || pdesc_date) = pdesc_uid;
   exception
      when others then
         raise exception 'Ocorreu um erro ao buscar os dados do usuário. Descrição: %', SQLERRM;
   end;

   if not found then
      raise exception 'invalid_user';
   end if;

  if (vstatus = 'I') then
     raise exception 'Usuário Inativo';
  end if;



   begin
      select
        case when rba_profile.parcust_id is not null then rba_profile.parcust_id
             when rba_profile.idseller   is not null then rba_profile.idseller
             when rba_profile.idmonitoringcenter is not null then rba_profile.idmonitoringcenter
             else 0 end,
        case when rba_profile.parcust_id is not null then 'CUS'
             when rba_profile.idseller   is not null then 'SEL'
             when rba_profile.idmonitoringcenter is not null then 'MCE'
             else 'ADM' end
        into vidcustomer, vsystype
        from rba_profile
       where id = vrbaprof_id;
   exception
      when others then
         raise exception 'Ocorreu um erro ao buscar o perfil do usuário. Descrição: %', SQLERRM;
   end;

   begin
      if (vsystype = 'CUS') and ((select active
                                   from par_customer
                                  where par_customer.idcustomer = vidcustomer) = false) then
         raise exception 'Cadastro inativo. %', (select block_message
                                                   from par_customer
                                                  where par_customer.idcustomer = vidcustomer);
      end if;
   end;

   if not found then
      raise exception 'Perfil de usuário não localizado. Entre em contato com o administrador.';
   end if;

select par_customer.idseller
      into vidseller
      from par_customer
     where par_customer.idcustomer = (
        select rba_profile.parcust_id
          from rba_profile
         where rba_profile.id = vrbaprof_id);

    if not found then
       vidseller := null;
    end if;

   --TODO: verificar qual o último acesso dele pela data e ver se ainda está logado.
   begin
       insert into par_access (parusua_id
                              ,dthr_access
                              ,desc_ip
                              ,desc_useragent
                              ,desc_uid
                              ,valid)
                       values (viduser
                              ,vdthr_access
                              ,pdesc_ip
                              ,pdesc_useragent
                              ,md5(viduser || ':' || vdthr_access || ':' || pdesc_ip || ':' || pdesc_useragent)
                              ,'S');
    exception
        when others then
            raise exception 'Não foi possível registrar o seu acesso. Descricao: %', SQLERRM;
    end;

    return query select vidcustomer, viduser, md5(viduser || ':' || vdthr_access || ':' || pdesc_ip || ':' || pdesc_useragent)::character varying, vidseller, vsystype;
end;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

ALTER FUNCTION public.par_login_fnc (pdesc_uid varchar, pdesc_date varchar, pdesc_useragent varchar, pdesc_ip varchar)
  OWNER TO pgc;

ALTER FUNCTION public.par_message_atualiza_is_read_fnc ()
  OWNER TO pgsql;

ALTER FUNCTION public.par_message_atualiza_origin_fnc ()
  OWNER TO pgsql;

ALTER FUNCTION public.par_update_customer_search_fnc (pidcustomer bigint)
  OWNER TO pgsql;

ALTER FUNCTION public.par_update_user_search_fnc (piduser bigint)
  OWNER TO pgsql;

ALTER FUNCTION public.par_user_inactivate_user_fnc ()
  OWNER TO pgsql;

ALTER FUNCTION public.par_user_update_search_fnc ()
  OWNER TO pgsql;

CREATE OR REPLACE FUNCTION public.par_verifica_acesso_fnc (
  pdesc_uid varchar,
  pdesc_useragent varchar,
  pdesc_ip varchar,
  pdesc_route varchar,
  pindr_type varchar
)
RETURNS varchar AS
$body$
declare
   vparusua_id	numeric;
   vdthr_acesso timestamp;
   vrbaprof_id bigint;
   vvalid char;
begin

   begin
      select parusua_id, dthr_access, valid
        from par_access
	into vparusua_id, vdthr_acesso, vvalid
       where desc_ip = pdesc_ip
         and desc_useragent = pdesc_useragent
         and desc_uid = pdesc_uid;
   exception
      when others then
         raise exception 'Ocorreu um erro ao buscar o usuário. Descrição: %', SQLERRM;
   end;



   if vparusua_id is null then
      raise 'Você não está logado.' USING ERRCODE = '42000';
   end if;

   if not exists(select null from par_user where iduser = vparusua_id and status = 'A') then
      raise exception 'Usuario desativado.';
   end if;

   begin
      select rbaprof_id
        into vrbaprof_id
        from par_user
       where iduser = vparusua_id;
   exception
	when others then
	   raise exception 'Ocorreu um erro ao buscar o perfil do usuario. Descrição: %', SQLERRM;
   end;

   if (select count(*)
         from rba_routes
         join rba_profile_routes on rba_profile_routes.rbarout_id = rba_routes.id
        where rba_routes.desc_route = pdesc_route
				and rba_routes.indr_type = pindr_type
          and rba_profile_routes.rbaprof_id = vrbaprof_id) = 0 then
      raise exception 'Você não tem permissão para %', (SELECT desc_route_name from rba_routes where desc_route = pdesc_route and indr_type=pindr_type limit 1);
   end if;

   if vdthr_acesso + '1 day'::interval < current_timestamp then
      RAISE 'Sua sessão expirou. Logue novamente.' USING ERRCODE = '28000';
   end if;

   if vvalid = 'N' then
      RAISE 'Sua sessão foi encerrada. Logue novamente.' USING ERRCODE = '28000';
   end if;

   return vparusua_id;
end;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE OR REPLACE FUNCTION public.rba_insert_basic_access_fnc (
  pidcustomer numeric,
  psystype varchar
)
RETURNS bigint AS
$body$
DECLARE
   vrbaprof_id numeric;
   vskel record;
   aux  varchar;
BEGIN

   vrbaprof_id := nextval('rba_profile_seq');

   begin
      if psystype = 'CUS' then
         insert into rba_profile (id, parcust_id, desc_profile) values (vrbaprof_id, pidcustomer, 'MASTER');
      elseif psystype = 'SEL' then
         insert into rba_profile (id, idseller, desc_profile) values (vrbaprof_id, pidcustomer, 'MASTER');
      elseif psystype = 'ADM' then
         insert into rba_profile (id, is_administrator, desc_profile) values (vrbaprof_id, true, 'MASTER');
      elseif psystype = 'MCE' then
         insert into rba_profile (id, idmonitoringcenter, desc_profile) values (vrbaprof_id, pidcustomer, 'MASTER');
      else
         raise exception 'Tipo de sistema não localizado.';
      end if;
   exception
      when others then
         raise exception 'Não foi possível registrar o grupo padrão para o cliente. Erro: %', SQLERRM;
   end;

   begin
      for vskel in (select id
                      from rba_routes
                     where rba_routes.id not in (select rba_routes.id from rba_routes
                                                  where rba_routes.basic
                                                    and (rba_routes.indr_systype is null or rba_routes.indr_systype = psystype))
                       and (rba_routes.indr_systype = psystype or rba_routes.indr_systype is null)
                  order by rba_routes.id) loop
            insert into rba_profile_routes (rbaprof_id, rbarout_id) values (vrbaprof_id, vskel.id);
      end loop;
      exception
         when others then
            raise exception 'Não foi possível inserir os acessos básicos para o perfil. Motivo: %', SQLERRM;
   end;

   begin
      update rba_profile set desc_profile = 'ADMINISTRADOR' where id = vrbaprof_id;
   exception
      when others then
         raise exception 'Não foi possível registrar o grupo ADMINISTRADOR. Descrição: %', SQLERRM;
   end;

   return vrbaprof_id;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

ALTER FUNCTION public.rba_profile_after_trg ()
  OWNER TO pgsql;

CREATE FUNCTION public.rba_update_admin_profiles_fnc (
  pidprofile bigint
)
RETURNS pg_catalog.void AS
$body$
DECLARE
   rbaprof record;
   rbarout record;
   systype varchar;
BEGIN
   -- ATUALIZA O PERFIL ADMINISTRADOR DE TODOS OS CLIENTES COM OS NOVOS ACESSOS
   -- EXECUTAR ESSA FUNÇÃO A CADA NOVA VERSÃO

   -- DESABILITA A TRIGGER QUE PROTEGE ALTERAÇÕES DO PERFIL ADMINISTRADOR
   ALTER TABLE rba_profile_routes DISABLE TRIGGER rba_profile_routes_trg;

   -- PERCORRE A TABELA DE PERFIS EM BUSCA DOS PERFIS DE ADMINISTRADOR
   for rbaprof in (select *
                     from rba_profile
                    where rba_profile.desc_profile = 'ADMINISTRADOR'
                      and rba_profile.id = pidprofile) loop
      if rbaprof.is_administrator then
         systype := 'ADM';
      elseif rbaprof.idseller is not null then
         systype := 'SEL';
      elseif rbaprof.idmonitoringcenter is not null then
         systype := 'MCE';
      else
         systype := 'CUS';
      end if;
      -- CARREGA TODOS OS ACESSOS EXISTENTES
      for rbarout in (select id from rba_routes where rba_routes.indr_systype is null or rba_routes.indr_systype = '' or rba_routes.indr_systype = systype) loop
         if (select count(*)
               from rba_profile_routes
              where rbaprof_id = rbaprof.id
                and rbarout_id = rbarout.id) = 0 then
            begin
               insert into rba_profile_routes (rbaprof_id, rbarout_id) values (rbaprof.id, rbarout.id);
            exception
               when others then
                  -- EM CASO DE ERRO HABILITA A TRIGGER
                  ALTER TABLE rba_profile_routes ENABLE TRIGGER rba_profile_routes_trg;
                  raise exception 'Erro ao atualizar os perfis ADMINISTRADOR';
            end;
         end if;
      end loop;
   end loop;

   -- HABILITA A TRIGGER QUE PROTEGE ALTERAÇÕES DO PERFIL ADMINISTRADOR
   ALTER TABLE rba_profile_routes ENABLE TRIGGER rba_profile_routes_trg;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

CREATE OR REPLACE FUNCTION public.sel_register_device (
  pdescription varchar,
  pcode varchar,
  pidcustomer numeric,
  pvehicle_code varchar = NULL::character varying
)
RETURNS pg_catalog.void AS
$body$
DECLARE
   vserial_key varchar;
   transaction record;
   alert       record;
   vidcustomer_default bigint;
BEGIN

   select idcustomer_default
     into vidcustomer_default
     from vlt_parameter
    limit 1;

   if (select count(*)
         from vlt_device
        where code = pcode) = 0 then
      raise exception 'Equipamento não localizado. Verifique! %', pcode;
   end if;

   --verificar se outro usuário não o registrou
   if (select count(*)
         from vlt_device
        where vlt_device.code = pcode
          and (vlt_device.idcustomer = pidcustomer)
   ) > 0 then
      raise exception 'Equipamento indisponível para registro!';
   end if;

   --verificar se outro usuário não o registrou
   if (select count(*)
         from vlt_device
        where vlt_device.code = pcode
          and (vlt_device.idcustomer is not null and vlt_device.idcustomer <> vidcustomer_default)
   ) > 0 then
      raise exception 'Equipamento indisponível para registro!';
   end if;

   -- limpando transações antigas
   /*begin
      for transaction in (select idtransaction
                            from vlt_transaction
                           where iddevice = (select iddevice
                                               from vlt_device
                                              where code = pcode)) loop
         -- limpando alertas       (vlt_alert)
         for alert in (select idalert
                         from vlt_alert
                        where idtransaction = transaction.idtransaction) loop
            -- limpando históricos de alertas (vlt_alert_history)
            delete from vlt_alert_history
                  where idalert = alert.idalert;
         end loop;

         delete from vlt_alert
               where idtransaction = transaction.idtransaction;

         -- limpando driver_record (vlt_driver_record)
         delete from vlt_driver_record
               where idtransaction_start = transaction.idtransaction;
         delete from vlt_driver_record
               where idtransaction_end = transaction.idtransaction;
         -- limpando i2c           (vlt_i2c_value)
         delete from vlt_i2c_value
               where idtransaction = transaction.idtransaction;
         -- limpando portas        (vlt_port_status)
         delete from vlt_port_status
               where idtransaction = transaction.idtransaction;
         -- limpando temperature   (vlt_temperature)
         delete from vlt_temperature
               where idtransaction = transaction.idtransaction;
         -- limpando tpms          (vlt_tpms_value)
         delete from vlt_transaction
               where idtransaction = transaction.idtransaction;
      end loop;
   exception
      when others then
         raise exception 'Não foi possível preparar o equipamento para o registro. Descrição: %', SQLERRM;
   end;*/

   --efetuar cadastro
   begin
      update vlt_device
         set idcustomer = pidcustomer
            ,description = pdescription
            ,vehicle_code = pvehicle_code
            ,date_register = current_timestamp
       where code = pcode;
   exception
      when others then
         raise exception 'Não foi possível registrar o equipamento. Descrição: %', SQLERRM;
   end;

   --inserindo na tabela de transferência
   begin
   		insert into vlt_device_history(iddevice, idcustomer_newer, iduser)
        values ((select iddevice from vlt_device where code = pcode), pidcustomer, vlt_min_user_fnc(cast (pidcustomer as bigint) , cast ('CUS' as varchar)) );
   exception
   when others then
   		raise exception 'Não foi possível registrar o histórico de transferência de equipamento. %', SQLERRM;
   end;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

ALTER FUNCTION public.sel_register_device (pdescription varchar, pcode varchar, pidcustomer numeric, pvehicle_code varchar)
  OWNER TO pgsql;

CREATE OR REPLACE FUNCTION public.set_port_status (
  ptransaction bigint,
  pvalue integer,
  pport_sequence integer,
  pport_type varchar,
  pexpander integer,
  pdevice bigint
)
RETURNS integer AS
$body$
declare
vid bigint;
vcode varchar;
valert_priority char(1);
vdescription varchar;
valert_port boolean;
viddeviceport bigint;
vidcustomer bigint;
vidalerttype bigint;
vconnected boolean;
vfunction varchar(200);
valert varchar(200);
v_sql character varying(200);
begin
select vlt_port.idport
     , vlt_port.connected
     , vlt_port.function_port
     , alert_function
     , idcustomer
     , idalerttype
     , vehicle_code
     , vlt_port.description
     ,alert_priority
  into vid
     , vconnected
     , vfunction
     , valert
     , vidcustomer
     , vidalerttype
     , vcode
     , vdescription
     , valert_priority
  from vlt_port,vlt_device
 where vlt_port.port_sequence = pport_sequence
   and vlt_port.port_type     = pport_type
   and vlt_port.expander      = pexpander
   and vlt_device.iddevice    = pdevice
   and vlt_port.iddevicemodel = vlt_device.iddevicemodel;

 vfunction:= replace(replace(vfunction,'#',cast(pvalue as varchar)),'\047','''');

 insert into vlt_port_status(idtransaction,idport,value,original_value)
                      values(ptransaction ,vid   ,eval(vfunction)::numeric,pvalue);
 --ALERT
 if (valert is not null) then
    valert :=eval(replace(replace(valert,'#',cast(pvalue as varchar)),'\047',''''));

    select iddeviceport,alert_port
      into viddeviceport,valert_port
      from vlt_device_port
     where iddevice = pdevice
       and idport = vid;

    if viddeviceport is null then
      SELECT nextval('vlt_device_port_iddeviceport_seq')
        into viddeviceport;

      insert into vlt_device_port (iddeviceport,iddevice,idport,alert_port)
      values(viddeviceport,pdevice,vid,false);
      valert_port:=false;

    end if;

    if valert is not null and (not valert_port) then
           insert into vlt_alert
              (alert,
               idcustomer,
               alert_date,
               idtransaction,
               idalerttype,
               priority)
          values
               ('Veículo '||vcode||', Porta '||vdescription||', '||valert,
               vidcustomer,
               now(),
               ptransaction,
               vidalerttype,
               valert_priority);
      update vlt_device_port
         set alert_port = true
       where iddeviceport=viddeviceport;
      else
      update vlt_device_port
         set alert_port = false
       where iddeviceport=viddeviceport and alert_port;
    end if;
 end if;

 if vconnected then
  update vlt_transaction
  set connected=(pvalue=0)
  where idtransaction=ptransaction;

  update vlt_device
  set connected=(pvalue=0)
  where iddevice=pdevice;
 end if;

 RETURN 0;

end
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE OR REPLACE FUNCTION public.set_tpms_value (
  ptransaction bigint,
  pdevice bigint,
  pcommand varchar
)
RETURNS varchar AS
$body$
declare
seq integer;
vpressao integer;
vtemperatura integer;
vflag varchar(10);
vfunc_temperatura varchar(100);
vfunc_pressao varchar(100);
vid bigint;
vid_value bigint;
vid_value_first bigint;
vconnected boolean;
vseq integer;
vseqp integer;
vqtde integer;
vdados varchar(200);
vpneu varchar(200);
vinf varchar(8);
vcompleto boolean;
q integer;
c integer;

begin
 vcompleto := pcommand like 'B%';
 if vcompleto then
    c:=14;
    q:=4;
    -- completo
    if (length(pcommand)/2) in (24, 31, 38, 45) then
        vdados:=substr(pcommand,33,length(pcommand));
        vinf:=hextobin(substr(pcommand,31,2));

    else
        vdados:=substr(pcommand,17,length(pcommand));
        vinf:=hextobin(substr(pcommand,15,2));
    end if;
 else
     -- resumido
     c:=6;
     q:=9;
--  A0000283621610000465E9EA4B312309114A23A646210F4B23104B23114C23124A23114B23104A23114C238E
     if (length(pcommand)/2) in (20, 23, 26, 29, 32, 35, 38, 41, 44) then
        vdados:=substr(pcommand,33,length(pcommand));
        vinf:=hextobin(substr(pcommand,31,2));
    else
        vdados:=substr(pcommand,17,length(pcommand));
        vinf:=hextobin(substr(pcommand,15,2));
     end if;
 end if;

 vseq:=  (substr(vinf,2,3)::bit(3))::integer;
 vqtde:= (substr(vinf,5,4)::bit(4))::integer;

 if vseq =0 then
    vid_value_first:=ptransaction;
    if vcompleto then
         update vlt_tpms
           set tpms_sequence_send= 0
          where iddevice=pdevice;
    end if;
 else
    vid_value_first:=null;
    select idtransaction
      into vid_value_first
      from vlt_tpms,vlt_tpms_value
     where iddevice=pdevice
       and tpms_sequence_send=1
       and vlt_tpms.idtpmsvalue_last=vlt_tpms_value.idtpmsvalue;
 end if;

 FOR i IN 0..(vqtde -1) LOOP
   vpneu:=substr(vdados,(i*c)+1,c);
   vseqp:=(vseq*q)+1+i;

   select hextoint(substr(vpneu,case when vcompleto then 7 else 1 end,2))
        , hextoint(substr(vpneu,case when vcompleto then 9 else 1 end,2))
        , hextoint(substr(vpneu,case when vcompleto then 11 else 3 end,2))
        ,         (substr(vpneu,case when vcompleto then 13 else 5 end,2))
     into seq,vpressao,vtemperatura,vflag;
   if vcompleto then
   select idtpms
     into vid
     from vlt_tpms
    where iddevice=pdevice
      and tpms_sequence =seq;

      -- cria registro tpms
      if not found then
         SELECT nextval('vlt_tpms_idtpms_seq')
           into vid;
         insert into vlt_tpms
                      (idtpms ,tpms_sequence_send,tpms_sequence,tpms_name     ,tire_client_id,iddevice ,idtrailer,side,axle,"position",sensor_id)
                values(vid    ,vseqp             ,seq          ,'PNEU ' || seq,null,pdevice,null     ,null,null,null,substr(vpneu,1,6));
         else
         update vlt_tpms
           set tpms_sequence_send= vseqp
          where idtpms=vid;
      end if;
   else
   vid:=null;
   select idtpms
     into vid
     from vlt_tpms
    where iddevice=pdevice
      and tpms_sequence_send =vseqp;

   if vid is null then
     select idtpms
       into vid
       from vlt_tpms
      where iddevice=pdevice
        and tpms_sequence =vseqp;
   end if;
end if;
         SELECT nextval('vlt_tpms_value_idtpmsvalue_seq')
           into vid_value;
   -- valores
   select function_temperature,function_pressure
     into vfunc_temperatura,vfunc_pressao
     from vlt_tpms_model m,vlt_device d
    where d.iddevice  = pdevice
    and d.idtpmsmodel =m.id;

    vfunc_temperatura:= replace(replace(vfunc_temperatura,'#',cast(vtemperatura as varchar)),'\047','''');
    vfunc_pressao:= replace(replace(vfunc_pressao,'#',cast(vpressao as varchar)),'\047','''');

   insert into vlt_tpms_value(idtpmsvalue,idtpms,idtransaction,temperature            ,pressure           ,original_temperature,original_pressure,flag,idtransaction_first,active,batery,leak)
                       values(vid_value
                              ,vid
                              ,ptransaction
                              ,eval(vfunc_temperatura)::numeric
                              ,eval(vfunc_pressao)::numeric
                              ,vtemperatura
                              ,vpressao
                              ,vflag
                              ,vid_value_first
                              ,COALESCE(substr(hextobin(vflag),3,1)='0',false)
                              ,COALESCE(substr(hextobin(vflag),4,1)='0',false)
                              ,COALESCE(substr(hextobin(vflag),5,1)='1',false));
 if vcompleto then
         update vlt_tpms
            set sensor_id = substr(vpneu,1,6)
              , idtpmsvalue_last=vid_value
          where idtpms = vid;
    else
         update vlt_tpms
            set idtpmsvalue_last=vid_value
          where idtpms = vid;
 end if;
 END LOOP;
 RETURN 0;
end
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE FUNCTION public."utl_interval_to_hours_fnc(pinterval interval)" (
  pinterval interval
)
RETURNS varchar AS
$body$
begin
   return trim(to_char(extract(day  from pinterval)::integer * 24 +
                       extract(hour from pinterval)::integer,'900')) || ':' ||
                       to_char(pinterval,'mi');
end;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

ALTER FUNCTION public."utl_interval_to_hours_fnc(pinterval interval)" (pinterval interval)
  OWNER TO pgc;

CREATE OR REPLACE FUNCTION public.vlt_agrupa_transaction (
  piddevice bigint,
  pidtransaction bigint,
  pidrecordtype bigint
)
RETURNS integer AS
$body$
declare
  reg            RECORD;
  idgrupo        bigint;
  distancia      bigint;
  distanciag     bigint;
  speedg         numeric;
  vvidtransaction  bigint;
  vidtransaction   bigint;
  idrecordtype     bigint;
  latitude       numeric;
  longitude      numeric;
  latitudeg      numeric;
  longitudeg     numeric;
  rlatitude      numeric;
  rlongitude     numeric;
  rcourse        numeric;
  c              bigint;
  vconnected     boolean;
  command_date   timestamp WITHOUT TIME ZONE;
  diff time;
  vlimit_group integer;
begin
  select limit_group
    into vlimit_group
    from vlt_device
   where iddevice=piddevice;
  if vlimit_group is null then
     vlimit_group = 5;
  end if;
  c:=0;
  speedg:=-1;
  vvidtransaction:=0;
  idgrupo:=null;
  FOR reg in select t.idtransaction,t.idrecordtype,t.latitude,t.longitude,t.command_date,t.idtransaction_group,speed,t.course,t.connected
               from vlt_transaction t
              where iddevice= piddevice
                and (pidtransaction is null
                or  t.idtransaction >= (select vlt_device_last.idtransaction
                                          from vlt_device_last
                                         where vlt_device_last.iddevice     = piddevice
                                           and vlt_device_last.idrecordtype = pidrecordtype))
                and t.idrecordtype=pidrecordtype
              order by 1
  LOOP
    if vconnected is null and pidtransaction is not null then
       select vlt_transaction.connected
         into vconnected
         from vlt_device,vlt_transaction
        where vlt_device.iddevice         = piddevice
          and vlt_device.last_transaction = vlt_transaction.idtransaction;

    end if;
    if reg.latitude=0 then
    update vlt_transaction
    set latitude=rlatitude
      , longitude=rlongitude
      , course=rcourse
      where idtransaction=reg.idtransaction;
    else
      rlatitude:=reg.latitude;
      rlongitude:=reg.longitude;
      rcourse:=reg.course;
    end if;

    if idgrupo is not null  then
      select distance
        into distanciag
        from distance(latitudeg,longitudeg,rlatitude,rlongitude);

        if (distanciag >= vlimit_group) or (vconnected <> reg.connected) then
           idgrupo    := reg.idtransaction;
           latitudeg  := rlatitude;
           longitudeg := rlongitude;
           vconnected:=reg.connected;
        end if;
        c:=c+1;
    else
      vconnected:=reg.connected;
      if pidtransaction is null then
         idgrupo   := reg.idtransaction;
         latitudeg  := rlatitude;
         longitudeg := rlongitude;
         c:=c+1;
      else
      select vlt_transaction.idtransaction,vlt_transaction.latitude,vlt_transaction.longitude
        into idgrupo,latitudeg,longitudeg
        from vlt_transaction
       where vlt_transaction.idtransaction = reg.idtransaction_group;
       latitude  := latitudeg;
       longitude := longitudeg;
      end if;
    end if;
      if idgrupo is null then
           distancia:=0;
           diff:=null;
      else
        if reg.command_date>command_date then
           diff:=CAST(reg.command_date-command_date AS TIME);
        else
         diff:=CAST(command_date-reg.command_date AS TIME);
        end if;
      select distance
        into distancia
        from distance(latitude,longitude,rlatitude,rlongitude);
    end if;

    if c>0 then
      update vlt_transaction
         set idtransaction_group = idgrupo
           , distance            = distancia
           , time_dif            = diff
       where vlt_transaction.idtransaction = reg.idtransaction;
    end if;

    IF RLATITUDE<>0 THEN
    vvidtransaction := reg.idtransaction;
    END IF;

    idrecordtype  := reg.idrecordtype;
    latitude      := rlatitude;
    longitude     := rlongitude;
    command_date  := reg.command_date;
    speedg:=reg.speed;
  END LOOP;

  IF vvidtransaction>0 THEN
  update vlt_device
     set last_transaction=vvidtransaction
     ,last_latitude  = rlatitude
     ,last_longitude = rlongitude
   where iddevice    = piddevice;
  END IF;

  if pidtransaction is not null then
    idgrupo:=null;
     select idtransaction_group
       into idgrupo
       from vlt_transaction
      where idtransaction=pidtransaction;
     if idgrupo is null then
      SELECT public.vlt_agrupa_transaction(piddevice, NULL, pidrecordtype)
        into idgrupo;
     end if;
  end if;
  RETURN 0;
end
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

ALTER FUNCTION public.vlt_alert_insert_fnc ()
  OWNER TO pgsql;

ALTER FUNCTION public.vlt_alert_isnew_fnc ()
  OWNER TO pgsql;

ALTER FUNCTION public.vlt_atualiza_has_parameter_command_fnc ()
  OWNER TO pgsql;

ALTER FUNCTION public.vlt_command_fnc ()
  OWNER TO pgsql;

ALTER FUNCTION public.vlt_console_register_fnc (puid varchar, pdescription varchar)
  OWNER TO pgsql;

ALTER FUNCTION public.vlt_console_update_date_fnc ()
  OWNER TO pgsql;

ALTER FUNCTION public.vlt_customer_history_before_fnc ()
  OWNER TO pgsql;

CREATE OR REPLACE FUNCTION public.vlt_device_driver_record_fnc (
)
RETURNS trigger AS
$body$
DECLARE
   c BIGINT;
   t1 bigint;
   t2 bigint;
BEGIN
   if new.connected and not old.connected and old.last_driver is not null then  -- ligou o veiculo
      insert into vlt_driver_record (iddriver, iddevice, idtransaction_start, start_date)
                             values (old.last_driver, old.iddevice, new.last_transaction, (select command_date
                                                                                             from vlt_transaction
                                                                                            where idtransaction = new.last_transaction));
   end if;

   if not new.connected and old.connected and old.last_driver is not null then  -- desligou o veiculo
      select max(iddriverrecord) into c
        from vlt_driver_record
       where iddevice = new.iddevice;

      update vlt_driver_record
         set idtransaction_end = new.last_transaction
            ,end_date = (select command_date
                           from vlt_transaction
                          where idtransaction = new.last_transaction)
       where iddriverrecord = c;
   end if;

   if new.last_driver <> old.last_driver then  -- trocando o motorista
      select count(*) into c
        from vlt_driver_record
       where iddevice = new.iddevice
         and idtransaction_start is not null
         and idtransaction_end is null;

      select max(iddriverrecord) into c
        from vlt_driver_record
       where iddevice = new.iddevice;

      if c is not null then
         select idtransaction_start
               ,idtransaction_end
           into t1
               ,t2
           from vlt_driver_record
          where iddriverrecord = c;

         update vlt_driver_record
            set idtransaction_end = new.last_transaction
               ,end_date = (select command_date
                              from vlt_transaction
                             where idtransaction = new.last_transaction)
          where iddriverrecord = c;

         insert into vlt_driver_record (iddriver, iddevice, idtransaction_start, start_date)
                                values (new.last_driver, old.iddevice, new.last_transaction, (select command_date
                                                                                                from vlt_transaction
                                                                                               where idtransaction = new.last_transaction));
      end if;
   end if;

   return new;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE OR REPLACE FUNCTION public.vlt_device_fnc (
  pidcustomer bigint,
  piduser bigint,
  psearch varchar = NULL::character varying
)
RETURNS TABLE (
  iddevice bigint,
  description varchar,
  latitude numeric,
  longitude numeric,
  vehicle_code varchar,
  idcustomer bigint,
  code varchar,
  device_search tsvector,
  has_battery boolean,
  has_analog_ports boolean,
  has_digital_ports boolean,
  has_tpms boolean,
  has_fuel boolean,
  has_temperature boolean,
  connected text,
  is_connected boolean,
  speed integer,
  command_date varchar,
  start_date varchar,
  driver varchar,
  address varchar,
  idtransaction bigint,
  online char,
  record_type varchar,
  has_alert boolean,
  active boolean,
  odometer numeric,
  interestpoint varchar
) AS
$body$
DECLARE
    vtaguser varchar;
BEGIN
   select tag
     into vtaguser
     from par_user
    where iduser = piduser;

    if vtaguser = '' then
      vtaguser := null;
    end if;
   return query select dev.iddevice
      ,dev.description
      ,tt.latitude
      ,tt.longitude
      ,dev.vehicle_code
      ,dev.idcustomer
      ,dev.code
      ,dev.device_search
      ,dm.has_battery
      ,dm.has_analog_ports
      ,dm.has_digital_ports
      ,dm.has_tpms
      ,dm.has_fuel
      ,dm.has_temperature
      ,CASE WHEN tt.connected THEN 'Sim'::text ELSE 'Não'::text END AS connected
      ,tt.connected AS is_connected
      ,tt.speed
      ,to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS command_date
      ,to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS start_date
      ,drv.driver
      ,tt.address
      ,tt.idtransaction
      ,tt.online
      ,rt.description AS record_type
      ,vlt_device_has_alert_fnc(dev.iddevice) AS has_alert
      ,dev.active
      ,round(tt.odometer::numeric / 1000::numeric, 1) AS odometer
      ,(select interest_point from vlt_transaction_point_fnc(tt.idtransaction))
  from vlt_transaction tt
  join vlt_device dev on dev.iddevice = tt.iddevice
  join vlt_device_model dm on dm.iddevicemodel = dev.iddevicemodel
  join vlt_record_type rt on rt.idrecordtype = tt.idrecordtype
  left join vlt_driver drv on drv.iddriver = vlt_transaction_driver_fnc(tt.idtransaction)
 where tt.idtransaction in (select vlt_last_tran_dev_fnc(device.iddevice)
                              from vlt_device device
                             where device.idcustomer = pidcustomer)
   and dev.active is true
   and case when psearch is not null then dev.device_search @@ to_tsquery(utl_text_search(psearch) || ':*') else true end
   and case when dev.tag is null or vtaguser is null then true else (utl_compare_arrays_fnc(dev.tag,vtaguser)) end;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

ALTER FUNCTION public.vlt_device_history_before_fnc ()
  OWNER TO pgsql;

ALTER FUNCTION public.vlt_fence_speed_fnc ()
  OWNER TO pgsql;

ALTER FUNCTION public.vlt_fence_speed_trg ()
  OWNER TO pgsql;

ALTER FUNCTION public.vlt_gera_senha_device_fnc (pcode varchar)
  OWNER TO pgsql;

ALTER FUNCTION public.vlt_insert_driver_fingerprint_fnc (piddriver bigint, pfinger_index integer, ptemplate_base64 varchar)
  OWNER TO pgsql;

CREATE OR REPLACE FUNCTION public.vlt_last_position_fnc (
  pidcustomer numeric
)
RETURNS TABLE (
  description varchar,
  connected text,
  is_connected boolean,
  speed integer,
  latitude numeric,
  longitude numeric,
  command_date varchar,
  start_date varchar,
  driver varchar,
  address varchar,
  idtransaction bigint,
  online char,
  iddevice bigint,
  vehicle_code varchar,
  hour text,
  record_type varchar,
  has_battery boolean,
  has_analog_ports boolean,
  has_digital_ports boolean,
  has_tpms boolean,
  has_fuel boolean,
  has_temperature boolean
) AS
$body$
BEGIN
 return query select vlt_device.description
      ,case when vlt_transaction.connected then 'Sim' ELSE 'Não' end connected
      ,vlt_transaction.connected is_connected
      ,vlt_transaction.speed
      ,vlt_transaction.latitude
      ,vlt_transaction.longitude
      ,cast(to_char(vlt_transaction.command_date, 'dd/mm/yyyy hh24:mi:ss')as varchar) command_date
      ,cast(to_char(vlt_transaction.command_date, 'dd/mm/yyyy hh24:mi:ss')as varchar) start_date
      ,(select d.driver from vlt_driver d where d.iddriver = vlt_transaction.iddriver) driver
      ,vlt_transaction.address
--      ,last_transaction idtransaction
      ,(select max(vlt_transaction.idtransaction) from vlt_transaction where vlt_transaction.iddevice = vlt_device.iddevice and vlt_transaction.valid) idtransaction
      ,vlt_transaction.online
      ,vlt_device.iddevice
      ,vlt_device.vehicle_code
      ,to_char((select max(t.command_date) - min(t.command_date) from vlt_transaction t where t.idtransaction_group = vlt_transaction.idtransaction_group), 'HH24:MI:SS') as hour
      ,(select rt.description from vlt_record_type rt where rt.idrecordtype = vlt_transaction.idrecordtype) record_type
      ,vlt_device_model.has_battery
          ,vlt_device_model.has_analog_ports
          ,vlt_device_model.has_digital_ports
          ,vlt_device_model.has_tpms
          ,vlt_device_model.has_fuel
   ,vlt_device_model.has_temperature
  from vlt_device
  join vlt_transaction on vlt_transaction.idtransaction = vlt_last_tran_dev_fnc(vlt_device.iddevice)
  join vlt_device_model on vlt_device_model.iddevicemodel = vlt_device.iddevicemodel
 where vlt_device.idcustomer = pidcustomer and vlt_device.active;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

-- object recreation
DROP FUNCTION public.vlt_list_coordinates_fnc(piddevice numeric, pinitial_date timestamptz, pfinal_date timestamptz, precord_type varchar);

CREATE FUNCTION public.vlt_list_coordinates_fnc (
  piddevice numeric,
  pinitial_date timestamptz = NULL::timestamp with time zone,
  pfinal_date timestamptz = NULL::timestamp with time zone,
  precord_type varchar = '0,1,2,4,6,7,8'::character varying
)
RETURNS TABLE (
  description varchar,
  connected varchar,
  is_connected boolean,
  speed integer,
  latitude numeric,
  longitude numeric,
  start_date varchar,
  driver varchar,
  address varchar,
  idtransaction bigint,
  online char,
  iddevice bigint,
  vehicle_code varchar,
  grouped bigint,
  record_type varchar,
  has_battery boolean,
  has_analog_ports boolean,
  has_digital_ports boolean,
  has_tpms boolean,
  has_fuel boolean,
  has_temperature boolean
) AS
$body$
declare
  vtotal_coordinates numeric;
  vqtd_registry numeric;
begin
  if pfinal_date is null then
    pfinal_date := current_timestamp;
  end if;

  return query (
    select v.description
          ,cast(case when f.connected then 'Sim' else 'Não' end as varchar) connected
          ,f.connected
          ,r.speed
          ,g.latitude
          ,g.longitude
--          ,cast (to_char(f.command_date, 'dd/mm/yyyy hh24:mi:ss') as varchar) start_date
          ,
	  case when COALESCE((select min(vlttg.idtransaction)
                                                                 from vlt_transaction vlttg
                                                                where vlttg.idtransaction_group = g.idtransaction
                                                                  and vlttg.valid), g.idtransaction) =
                    COALESCE((select max(vlttg.idtransaction)
                                                                 from vlt_transaction vlttg
                                                                where vlttg.idtransaction_group = g.idtransaction
                                                                  and vlttg.valid), g.idtransaction)
               then
          cast (to_char((select vltt.command_date
                           from vlt_transaction vltt
                          where vltt.idtransaction = COALESCE((select min(vlttg.idtransaction)
                                                                 from vlt_transaction vlttg
                                                                where vlttg.idtransaction_group = g.idtransaction
                                                                  and vlttg.valid), g.idtransaction)), 'dd/mm/yyyy hh24:mi')
                                                                  as varchar)
                else
          cast (to_char((select vltt.command_date
                           from vlt_transaction vltt
                          where vltt.idtransaction = COALESCE((select min(vlttg.idtransaction)
                                                                 from vlt_transaction vlttg
                                                                where vlttg.idtransaction_group = g.idtransaction
                                                                  and vlttg.valid), g.idtransaction)), 'dd/mm/yyyy hh24:mi')
                ||  ' - ' ||
                to_char((select vltt.command_date
                           from vlt_transaction vltt
                          where vltt.idtransaction = COALESCE((select max(vlttg.idtransaction)
                                                                 from vlt_transaction vlttg
                                                                where vlttg.idtransaction_group = g.idtransaction
                                                                  and vlttg.valid), g.idtransaction)), 'dd/mm hh24:mi') as varchar)
           end start_date
          ,case when g.iddriver is not null then (select vlt_driver.driver from vlt_driver where iddriver = g.iddriver) else '' end as driver
          ,g.address
          ,g.idtransaction
          ,l.online
          ,f.iddevice
          ,v.vehicle_code
          --,r.grouped
          ,(select count(*) from vlt_transaction where idtransaction_group = g.idtransaction and valid) grouped
          ,(select rt.description from vlt_record_type rt where idrecordtype = g.idrecordtype) as tipo
          ,vm.has_battery
          ,vm.has_analog_ports
          ,vm.has_digital_ports
          ,vm.has_tpms
          ,vm.has_fuel
          ,vm.has_temperature
    from (
      select r.idtransaction_group idtransaction
            ,min(r.idtransaction) first_transaction
            ,max(r.idtransaction) last_transaction
            ,max(r.speed) speed
            --,count(distinct r.idtransaction) grouped
      from (
        select vlt_transaction.idtransaction_group
              ,vlt_transaction.idtransaction
              ,vlt_transaction.speed
              ,vlt_transaction.command_date as data
              ,vlt_transaction.idrecordtype
              ,coalesce(LEAD(vlt_transaction.command_date, 1) OVER(PARTITION BY vlt_transaction.iddevice ORDER BY vlt_transaction.idtransaction_group, vlt_transaction.idtransaction), coalesce((select min(command_date) from vlt_transaction t where t.iddevice = vlt_transaction.iddevice and t.idtransaction > vlt_transaction.idtransaction), now())) datap
        from vlt_transaction
       where vlt_transaction.iddevice = piddevice
         and vlt_transaction.valid
         and vlt_transaction.command_date BETWEEN pinitial_date and pfinal_date
         and vlt_transaction.idrecordtype = any (string_to_array(precord_type, ',') ::integer [ ])) r
         group by idtransaction_group
         order by 1
       ) r,
       vlt_transaction g,
       vlt_transaction f,
       vlt_transaction l,
       vlt_device v,
       vlt_device_model vm
  where r.idtransaction = g.idtransaction and
        r.first_transaction = f.idtransaction and
        r.last_transaction = l.idtransaction and
        g.iddevice = v.iddevice and
        v.iddevicemodel = vm.iddevicemodel
  order by r.idtransaction desc,
           r.first_transaction,
           r.last_transaction);
end;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

CREATE OR REPLACE FUNCTION public.vlt_min_user_fnc (
  pid bigint,
  psystype varchar
)
RETURNS bigint AS
$body$
declare
   returnid bigint;
BEGIN
   returnid := null;

   if psystype = 'MCE' then
      select min(iduser)
        into returnid
        from par_user
        join rba_profile on rba_profile.id = par_user.rbaprof_id
       where rba_profile.idmonitoringcenter = pid;

      if returnid is null then
         raise exception 'Central de monitoramento não localizada. Verifique!';
      end if;
   end if;

   if psystype = 'CUS' then
      select min(iduser)
        into returnid
        from par_user
        join rba_profile on rba_profile.id = par_user.rbaprof_id
       where rba_profile.parcust_id = pid;

      if returnid is null then
         raise exception 'Cliente não localizado. Verifique!%', pid;
      end if;
   end if;

   if psystype = 'SEL' then
      select min(iduser)
        into returnid
        from par_user
        join rba_profile on rba_profile.id = par_user.rbaprof_id
       where rba_profile.idseller = pid;

       if returnid is null then
          raise exception 'Representante não localizada. Verifique!';
       end if;
   end if;

   if returnid is null then
      raise exception 'Tipo de sistema não localizado. Verifique!';
   end if;

   return returnid;

END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE OR REPLACE FUNCTION public.vlt_mobile_battery_fnc (
  pidtransaction bigint
)
RETURNS TABLE (
  description varchar,
  value numeric
) AS
$body$
DECLARE
   viddevice bigint;
   vidrecordtype bigint;
   vidtransaction bigint;
BEGIN
   select iddevice into viddevice
     from vlt_transaction
    where idtransaction = pidtransaction;

   vidrecordtype := 7;

   select idtransaction into vidtransaction
     from vlt_transaction
    where iddevice = viddevice
      and idrecordtype = vidrecordtype
      and idtransaction < pidtransaction
      and valid
 order by idtransaction desc limit 1;

    return query select vlt_port.description
                       ,vlt_port_status.value
                   from vlt_port_status
                   join vlt_port on vlt_port.idport = vlt_port_status.idport
                  where idtransaction = vidtransaction;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

CREATE OR REPLACE FUNCTION public.vlt_mobile_smc_fnc (
  pidtransaction bigint
)
RETURNS TABLE (
  sensor_id integer,
  value numeric
) AS
$body$
DECLARE
   viddevice bigint;
   vidrecordtype bigint;
   vidtransaction bigint;
BEGIN
   select iddevice into viddevice
     from vlt_transaction
    where idtransaction = pidtransaction;

   raise info 'IDDEVICE: %', viddevice;

   vidrecordtype := 9;

   select idtransaction into vidtransaction
     from vlt_transaction
    where iddevice = viddevice
      and idrecordtype = vidrecordtype
      and idtransaction < pidtransaction
      and valid
 order by idtransaction desc limit 1;

    return query select vlt_fuel.sensor_id
                       ,vlt_fuel_value.value
                   from vlt_fuel_value
                   join vlt_fuel on vlt_fuel.idfuel = vlt_fuel_value.idfuel
                  where idtransaction = vidtransaction;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

ALTER FUNCTION public.vlt_odometer_transaction (piddevice bigint, pcommanddate timestamp, pini boolean)
  OWNER TO pgsql;

ALTER FUNCTION public.vlt_odometer_up_ins_trg ()
  OWNER TO pgsql;

CREATE OR REPLACE FUNCTION public.vlt_register_device (
  pdescription varchar,
  pcode varchar,
  ppassword varchar,
  pidcustomer numeric,
  pvehicle_code varchar = NULL::character varying
)
RETURNS pg_catalog.void AS
$body$
DECLARE
   vserial_key varchar;
   transaction record;
   alert       record;
   vidcustomer_default bigint;
BEGIN
   --validar a senha do equipamento
   vserial_key := substr(md5(pcode || 'elder'), 1, 10);

   -- msg_device_not_found: Equipamento não localizado. Verifique!
   if (select count(*)
         from vlt_device
         where code = pcode) = 0 then
         raise exception 'msg_device_not_found';
   end if;

   -- msg_invalid_password: Senha do equipamento inválida. Verifique!
   if (vserial_key <> ppassword) then
      raise exception 'msg_invalid_password %', vserial_key;
   end if;

   select idcustomer_default
     into vidcustomer_default
     from vlt_parameter
    limit 1;

   --verificar se outro usuário não o registrou
   if (select count(*)
         from vlt_device
        where vlt_device.code = pcode
          and vlt_device.idcustomer = pidcustomer
   ) > 0 then
      raise exception 'Equipamento já registrado para esse cliente!';
   end if;

   -- msg_unavailable_device: Equipamento indisponível para registro!
   --verificar se outro usuário não o registrou
   if (select count(*)
         from vlt_device
        where vlt_device.code = pcode
          and (vlt_device.idcustomer is not null and vlt_device.idcustomer <> vidcustomer_default)
   ) > 0 then
      raise exception 'msg_unavailable_device';
   end if;

 -- limpando transações antigas
   /*begin
      for transaction in (select idtransaction
                            from vlt_transaction
                           where iddevice = (select iddevice
                                               from vlt_device
                                              where code = pcode)) loop
         -- limpando alertas       (vlt_alert)
         for alert in (select idalert
                         from vlt_alert
                        where idtransaction = transaction.idtransaction) loop
            -- limpando históricos de alertas (vlt_alert_history)
            delete from vlt_alert_history
                  where idalert = alert.idalert;
         end loop;

         delete from vlt_alert
               where idtransaction = transaction.idtransaction;

         -- limpando driver_record (vlt_driver_record)
         delete from vlt_driver_record
               where idtransaction_start = transaction.idtransaction;
         delete from vlt_driver_record
               where idtransaction_end = transaction.idtransaction;
         -- limpando i2c           (vlt_i2c_value)
         delete from vlt_i2c_value
               where idtransaction = transaction.idtransaction;
         -- limpando portas        (vlt_port_status)
         delete from vlt_port_status
               where idtransaction = transaction.idtransaction;
         -- limpando temperature   (vlt_temperature)
         delete from vlt_temperature
               where idtransaction = transaction.idtransaction;
         -- limpando tpms          (vlt_tpms_value)
         delete from vlt_transaction
               where idtransaction = transaction.idtransaction;
      end loop;
   exception
      when others then
         raise exception 'Não foi possível preparar o equipamento para o registro. Descrição: %', SQLERRM;
   end;
   */
   --efetuar cadastro
   begin
      update vlt_device
         set idcustomer = pidcustomer
            ,description = pdescription
            ,vehicle_code = pvehicle_code
            ,date_register = current_timestamp
       where code = pcode;
   exception
      when others then
         raise exception 'Não foi possível registrar o equipamento.';
   end;

   --inserindo na tabela de transferência
   begin
   insert into vlt_device_history(iddevice, idcustomer_newer, iduser)
        values ((select iddevice from vlt_device where code = pcode), pidcustomer, vlt_min_user_fnc(cast (pidcustomer as bigint) , cast ('CUS' as varchar)) );
   exception
   when others then
              raise exception 'Não foi possível registrar o histórico de transferência de equipamento. Erro: %', SQLERRM;
   end;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

-- object recreation
DROP FUNCTION public.vlt_report_distance_fnc(pidcustomer integer, pdateini date, pdatefim date);

CREATE FUNCTION public.vlt_report_distance_fnc (
  pidcustomer integer,
  pdateini date,
  pdatefim date
)
RETURNS TABLE (
  distance numeric,
  iddevice bigint,
  command_date date,
  vehicle varchar
) AS
$body$
begin
   return query select sum(q.distance), q.iddevice, q.command_date, vlt_device.vehicle_code
                  from (select case when (vlt_distance_type_fnc(vlt_transaction.iddevice) = 'C')
                                    then sum(vlt_transaction.distance) / 1000
                                    else sum(vlt_transaction.distance1) / 1000
                                     end distance
                              ,vlt_transaction.command_date::date
                              ,vlt_transaction.iddevice
                                    from vlt_transaction
                                group by vlt_transaction.iddevice
                                        ,vlt_transaction.command_date::date
                                order by command_date) q
                 join vlt_device on vlt_device.iddevice = q.iddevice
                where q.iddevice in (select vlt_device.iddevice from vlt_device where idcustomer = pidcustomer) and q.command_date between pdateini and pdatefim
                                group by q.iddevice, q.command_date,vlt_device.vehicle_code
                                order by iddevice;
end
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;

CREATE FUNCTION public.vlt_sim_fnc (
)
RETURNS trigger AS
$body$
BEGIN
   perform vlt_update_sim_search_fnc(new.idsim);
   return new;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

CREATE FUNCTION public.vlt_sim_history_fnc (
  pidsim bigint = NULL::bigint,
  piddevice bigint = NULL::bigint,
  pstatus char = 'I'::bpchar,
  pidseller bigint = NULL::bigint,
  piduser bigint = NULL::bigint
)
RETURNS pg_catalog.void AS
$body$
DECLARE
   viddevice     bigint;
   vidsim        bigint;
   vidsimhistory bigint;
BEGIN
   -- registra o historico do SIM no sistema
   if pidsim is not null and not exists (select null from vlt_sim where idsim = pidsim) then
      raise exception 'SIM Card informado nao existe.';
   end if;

   if piddevice is not null and not exists (select null from vlt_device where iddevice = piddevice) then
      raise exception 'Veiculo informado nao existe.';
   end if;

   if pstatus = 'D' then
      if pidsim is not null then
         select max(idsimhistory)
           into vidsimhistory
           from vlt_sim_history
          where idsim = pidsim;
         insert into vlt_sim_history (
            idsim
          , iddevice
          , status
          , idseller
          , iduser) values (
            pidsim
          , NULL
          , (select status
               from vlt_sim_history
              where idsimhistory = vidsimhistory)
          , pidseller
          , piduser);
          return;
      elsif piddevice is not null then
         select max(idsimhistory)
           into vidsimhistory
           from vlt_sim_history
          where iddevice = piddevice;

          insert into vlt_sim_history (idsim, iddevice, status, idseller, iduser)
                               values ((select idsim from vlt_sim_history where idsimhistory = vidsimhistory), null, (select status from vlt_sim_history where idsimhistory = vidsimhistory), pidseller, piduser);
                               return;
      end if;
   end if;

   -- registrando historico para um equipamento
   if pidsim is not null and piddevice is not null then
      select idsimhistory
        into vidsimhistory
        from vlt_sim_history
       where idsimhistory = (select max(idsimhistory)
                               from vlt_sim_history
                              where idsim = pidsim);

      if exists (select null from vlt_sim_history where idsimhistory = vidsimhistory and idsim = pidsim and iddevice = piddevice and status = pstatus) then
         raise exception 'Esse SIM CARD ja foi associado a esse VEICULO com o STATUS informado';
      end if;
      insert into vlt_sim_history (idsim,  iddevice,  status,  idseller,  iduser)
                           values (pidsim, piddevice, pstatus, pidseller, piduser);
      return;
   elsif pidsim is not null then
      select max(idsimhistory)
        into vidsimhistory
        from vlt_sim_history
       where idsim = pidsim;

      if pstatus = (select status from vlt_sim_history where idsimhistory = vidsimhistory) then
         raise exception 'Esse SIM CARD ja foi associado a esse VEICULO com o STATUS informado';
         return;
      end if;

      select iddevice
        into viddevice
        from vlt_sim_history
       where idsimhistory = (select max(idsimhistory)
                                     from vlt_sim_history
                                    where idsim = pidsim);
         insert into vlt_sim_history (idsim, iddevice,   status,  idseller,  iduser)
                              values (pidsim, viddevice, pstatus, pidseller, piduser);
         return;
      elsif piddevice is not null then
         select idsim
           into vidsim
           from vlt_sim_history
          where idsimhistory = (select max(idsimhistory)
                                  from vlt_sim_history
                                 where iddevice = piddevice);
         insert into vlt_sim_history (idsim,  iddevice,  status,  idseller,  iduser)
                              values (vidsim, piddevice, pstatus, pidseller, piduser);
         return;
      end if;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

CREATE OR REPLACE FUNCTION public.vlt_socket_con_00_prc (
  pdevice varchar,
  pcommand varchar,
  pboot boolean,
  ponline boolean,
  pdata timestamp,
  pseq integer,
  pvel numeric,
  pcurso numeric,
  plat numeric,
  plong numeric,
  pdistance1 numeric = 0,
  pdistance2 numeric = 0,
  phdop numeric = 0,
  psatelite numeric = 0,
  pconnected boolean = false,
  psocket varchar = NULL::character varying,
  psocketport integer = NULL::integer
)
RETURNS integer AS
$body$
declare
  vtransaction bigint;
begin
  if pdevice in ('357805023984942','861001003748320') then
--  RAISE EXCEPTION 'BLOQUEADO';
     select *
       into vtransaction
       from vlt_socket_con_00_teste2_1_prc(pdevice,pcommand,pboot,ponline,pdata,pseq,pvel,pcurso,plat,plong,pdistance1,pdistance2,phdop,psatelite,pconnected,psocket,psocketport);
  else
     select *
       into vtransaction
       from vlt_socket_con_00_teste2_0_prc(pdevice,pcommand,pboot,ponline,pdata,pseq,pvel,pcurso,plat,plong,pdistance1,pdistance2,phdop,psatelite,pconnected,psocket,psocketport);
--       from vlt_socket_con_00_producao_prc(pdevice,pcommand,pboot,ponline,pdata,pseq,pvel,pcurso,plat,plong,pdistance1,pdistance2,phdop,psatelite,pconnected,psocket,psocketport);
  end if;
  return vtransaction;
end
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

CREATE FUNCTION public.vlt_socket_con_00_teste2_prc (
  pdevice varchar,
  pcommand varchar,
  pboot boolean,
  ponline boolean,
  pdata timestamp,
  pseq integer,
  pvel numeric,
  pcurso numeric,
  plat numeric,
  plong numeric,
  pdistance1 numeric,
  pdistance2 numeric,
  phdop numeric,
  psatelite numeric,
  pconnected boolean,
  psocket varchar,
  psocketport integer
)
RETURNS integer AS
$body$
declare
           info record;     -- cursor com as informações do devide
          infor record;     -- cursor com as informações do devide por registro
        vdriver bigint;     -- codigo driver
  vidrecordtype bigint;     -- id tipo registro
   vtransaction bigint;     -- id da transacao
      viddriver bigint;     -- id do motorista
         vvalid boolean;    -- registro valido
     vconnected boolean;    -- device conectado
      vresumido boolean;    -- gps resumido
     vduplicate boolean;    -- se registro esta duplicado
          vdate timestamp;  -- data atual do servidor
        vresult integer;    -- retorno de funções
     vlongitude numeric;    -- longitude
      vlatitude numeric;    -- latitude
     vsequencia integer;    -- sequencia de envio do equipamento
 viddevicealert bigint;
      vdistance bigint;
     vdistanceg bigint;
      vtime_dif time WITHOUT TIME ZONE;
          vtemp varchar;
      vodometer bigint;
vodometer_value bigint;
    vidodometer bigint;
   vidtransaction_group bigint;
          vcommand_date timestamp;  -- data do comando
 vcommand_date_original timestamp;  -- data do comando original
vvel NUMERIC;
 vport varchar;
q integer;
s varchar;
begin
  for info in SELECT * FROM device
  loop
-- Inicalização de variaveis
   vdate          := now();
   vresumido      := pdata<to_timestamp(1);
   vduplicate     := false;
   vvalid         := false;
   vresult        := 0;
   vconnected     := info.last_connected;
   vvalid         := info.last_valid;
   if info.bit_connected then
      vconnected:=pconnected;
   end if;
   --resumido/completo
   if vresumido then
      vsequencia    := info.last_transaction_sequence  + 1;
      if to_hex(pseq) <> substr(reverse(to_hex(vsequencia)),1,1) then
         RAISE EXCEPTION 'SEQUENCIA INVALIDA: %, % -> % ',vsequencia,to_hex(pseq), substr(reverse(to_hex(vsequencia)),1,1);
      end if;
      vvalid        := info.last_valid;
      vlatitude     := info.last_latitude  + plat;
      vlongitude    := info.last_longitude + plong;
      vcommand_date_original := info.last_command_date + to_char(PDATA,'00:MI:SS')::time;
      vcommand_date_original := case when info.dif<0 then vcommand_date_original + cast(abs(info.dif)||':00:00' as time) else vcommand_date_original - cast(abs(info.dif)||':00:00' as time) end;
   else
      vlatitude     := plat;
      vlongitude    := plong;
      vsequencia    := pseq;
      vcommand_date_original := pdata;
      vvalid:= (vlatitude     is not null)
           and (vcommand_date_original is not null)
           and (vlatitude     <> 0)
           and ((vcommand_date_original::date) <= (vdate::date+30))
           and (to_date('01/01/2013','dd/mm/yyyy')<>(vcommand_date_original::date));
   end if;

   -- se data invalida usar a ultima
   if ((vcommand_date_original is null)
      or (vcommand_date_original::date)>(now()::date+30))
      or (to_date('01/01/2013','dd/mm/yyyy')=(vcommand_date_original::date)) then
      vcommand_date_original:=info.last_command_date;
   end if;

   vcommand_date:=case when info.dif<0 then vcommand_date_original - cast(abs(info.dif)||':00:00' as time) else vcommand_date_original + cast(abs(info.dif)||':00:00' as time) end;

--  atualiza transaction invalido
    if not vvalid then
       vlatitude  := info.last_latitude;
       vlongitude := info.last_longitude;
    end if;
-- Tipo de Registro
   if substr(pcommand,1,1)in('3','9')
   then vidrecordtype:=9;
   else if substr(pcommand,1,1)in('B','A')
        then vidrecordtype:=8;
        else if substr(pcommand,1,1)in('1','0')
             then vidrecordtype:=1;
             else select idrecordtype
                    into vidrecordtype
                    from record_type
                   where code=substr(pcommand,1,1);
             end if;
        end if;
   end if;
--  verifica duplicado
    if vvalid and vcommand_date <= info.last_command_date and vidrecordtype<>12  then
       if exists(select null
                   from vlt_transaction t
                  where iddevice             = info.iddevice
                    and valid
                    and command_date         = vcommand_date
                    and idrecordtype         = vidrecordtype
                    and transaction_sequence = vsequencia
                    and latitude             = vlatitude
                    and connected            = vconnected
                    and longitude            = vlongitude) then
           vvalid:=False;
          vduplicate:=True;
       end if;
    end if;

-- id transação
   SELECT nextval('vlt_transaction_idtransaction_seq')
     into vtransaction;

   if vidrecordtype<>12 then
  --  Mensagem do equipamento
     if substr(pcommand,1,1) ='5' then

        vtemp := substr(RIGHT(pcommand,4),1,2);

        select iddevicealert
          into viddevicealert
          from vlt_device_alert
         where code= vtemp;

        if viddevicealert is null then
           SELECT nextval('vlt_device_alert_iddevicealert_seq')
             into viddevicealert;
           insert into vlt_device_alert (iddevicealert,code,description) values(viddevicealert,vtemp,vtemp);
        end if;
     end if;

  -- motorista
      if substr(pcommand,1,1) ='2' then
         IF info.idcustomer IS NULL THEN
            RAISE EXCEPTION 'TENTATIVA DE INSERIR MOTORISTA PARA DISPOSITIVO SEM CLIENTE';
         END IF;
         if vresumido then
            vdriver:= (select hextoint(invert_hex(substr(pcommand,15,4))));
         else
            vdriver:= (select hextoint(invert_hex(substr(pcommand,31,4))));
         end if;
         if vdriver>0 then
            select iddriver
              into viddriver
              from vlt_driver
             where code=cast(vdriver as varchar)
               and parcust_id = info.idcustomer;
            if not FOUND then
               SELECT nextval('vlt_driver_iddriver_seq')
                 into viddriver;
               insert into vlt_driver(iddriver  ,code,driver,last_date,parcust_id)
                               values(viddriver,vdriver,vdriver,vdate ,info.idcustomer);
            end if;
          insert into vlt_transaction_driver(iddriver,transaction_date,iddevice) values(viddriver,vcommand_date,info.iddevice);
         end if;
   end if;

-- distancia
   select distance
     into vdistance
     from distance(info.last_latitude,info.last_longitude,vlatitude,vlongitude);
     if vcommand_date>info.last_command_date then
        vtime_dif := CAST(vcommand_date-info.last_command_date AS TIME);
     else
        vtime_dif  := CAST(info.last_command_date-vcommand_date AS TIME);
     end if;
   -- odometro
   if vvalid then
      -- inicializa variaveis
      vodometer       := 0;
      vidodometer     := null;
      vodometer_value := 0;

      -- identifica odometro
      select iddeviceodometer
           , odometer_value
        into vidodometer
           , vodometer_value
        from vlt_device_odometer o
       where o.odometer_date<vcommand_date
         and info.iddevice = o.iddevice
       order by odometer_date desc
       limit 1;

       if vidodometer <> info.last_valid_idodometer then
          vodometer := vodometer_value;
       else
          vodometer := info.last_valid_odometer;
       end if;

       IF info.DISTANCE_TYPE='C' THEN
          vodometer := vodometer + vdistance;
       ELSE
          vodometer := vodometer + PDISTANCE1;
       END IF;
   end if;
   -- agrupamento
   for infor in SELECT * FROM record_type_last where idrecordtype=vidrecordtype
   loop
       vidtransaction_group := vtransaction;
       if (infor.idtransaction_group is not null) then
           select distance
             into vdistanceg
             from distance(infor.latitude_group,infor.longitude_group,vlatitude,vlongitude);
           if (vconnected=infor.connected_group)
              and (vdistanceg <= case when infor.connected then info.limit_group_on else info.limit_group_off end) then
              vidtransaction_group := infor.idtransaction_group;
           end if;
       end if;

       if (COALESCE(infor.idtransaction_group,0) <> vidtransaction_group) then
          update record_type_last
             set idtransaction_group = vidtransaction_group
               , command_date_group  = vcommand_date
               , connected_group     = vconnected
               , latitude_group      = vlatitude
               , longitude_group     = vlongitude
           where  idrecordtype = vidrecordtype;
       end if;
   end loop;
  end if;
  vodometer:= COALESCE(vodometer,0);
  VVEL:=(pvel*case when info.bit_connected then 3 else 2 end);
   insert into vlt_transaction(idtransaction
                              ,iddevice
                              ,idrecordtype
                              ,command
                              ,transaction_date
                              ,boot
                              ,online
                              ,command_date
                              ,command_date_original
                              ,transaction_sequence
                              ,speed
                              ,course
                              ,latitude
                              ,longitude
                              ,connected
                              ,valid
                              ,distance1
                              ,distance2
                              ,hdop
                              ,satellite
                              ,iddevicealert
                              ,duplicate
                              ,idtransaction_group
                              ,distance
                              ,time_dif
                              ,odometer)
                       values (vtransaction
                              ,info.iddevice
                              ,vidrecordtype
                              ,pcommand
                              ,vdate
                              ,case when pboot   then 'S' else 'N' end
                              ,case when ponline then 'O' else 'M' end
                              ,vcommand_date
                              ,vcommand_date_original
                              ,VSEQUENCIA
                              ,VVEL
                              ,pcurso
                              ,VLATITUDE
                              ,VLONGITUDE
                              ,vconnected
                              ,vvalid
                              ,pdistance1
                              ,0 --pdistance2
                              ,phdop
                              ,psatelite
                              ,viddevicealert
                              ,vduplicate
                              ,vidtransaction_group
                              ,vdistance
                              ,vtime_dif
                              , vodometer);
      if vidrecordtype=12 then
         return 0;
      end if;
      update device
         set last_transaction_sequence = VSEQUENCIA,
             last_command_date         = vcommand_date,
             last_latitude             = VLATITUDE,
             last_longitude            = VLONGITUDE,
             last_iddriver             = viddriver,
             last_connected            = vconnected,
             last_valid                = vvalid or vduplicate,
             last_valid_idodometer     = case when vvalid then vidodometer else last_valid_idodometer end,
             last_valid_odometer       = case when vvalid then vodometer   else last_valid_odometer end;
--  last transaction por tipo
    update record_type_last
       set idtransaction = vtransaction
         , command_date  = vcommand_date
         , connected     = vconnected
         , latitude      = vlatitude
         , longitude     = vlongitude
     where idrecordtype  = vidrecordtype
       and vcommand_date > command_date;
-- ANDROID
   if (substr(pcommand,1,1)='F') then
      SELECT set_refuel(vtransaction,info.iddevice,pcommand) into vresult;
      SELECT set_workday(vtransaction,info.iddevice,pcommand) into vresult;
   end if;
-- TPMS
   if vidrecordtype in(8,17)  then
      SELECT set_tpms_value(vtransaction, info.iddevice, pcommand) into vresult;
   end if;
-- fuel
   if substr(pcommand,1,1) in('3','9') then
      SELECT set_fuel(vtransaction, info.iddevice, substr(pcommand,case when resumido then 15 else 31 end,50)) into vresult;
    end if;
-- digital port's
   if substr(pcommand,1,1)='6' then
      if pdata<to_timestamp(1) then
        vport:=substr(pcommand,15,6);
      else
       vport:=substr(pcommand,31,6);
      end if;
      SELECT set_port_digital(vtransaction,info.iddevice,vport) into vresult;
   end if;
-- analog port's
   if substr(pcommand,1,1) in('7','8') then
      if pdata<to_timestamp(1) then
         vport:=(substr(pcommand,15,6));
      else
         vport:=(substr(pcommand,31,6));
      end if;

      SELECT set_port_status(vtransaction,(select hextoint(substr(vport,1,3))),0,'A',case when substr(pcommand,1,1) ='8' then 1 else 0 end,info.iddevice) into vresult;
      SELECT set_port_status(vtransaction,(select hextoint(substr(vport,4,3))),1,'A',case when substr(pcommand,1,1) ='8' then 1 else 0 end,info.iddevice) into vresult;
   end if;
-- temperature
   if substr(pcommand,1,1)='4' then
      if pdata<to_timestamp(1) then
         q:=(length(pcommand)-16)/2;
         vport:=substr(Pcommand,15,q*2);
      else
         q:=(length(pcommand)-32)/2;
         vport:=substr(Pcommand,31,q*2);
      end if;
      FOR i IN 1..q LOOP
          S:= substr(vport,(i-1)*2+1,2);
          vtemp:=(select hextoint2(S));
          insert into vlt_temperature(idtransaction,temperature_sequence,temperature)
          values(vtransaction,i,VTEMP+80);
      END LOOP;
   end if;
-- alertas
--  device interval
   if info.alert_connected and vconnected  and (not info.last_connected) then
    if not exists ( select null from  device_interval where weekday = to_char(vcommand_date,'D')::integer and to_char(vcommand_date,'hh24:mi:ss')::time between start_time and end_time) then
       insert into vlt_alert
              (alert,
               idcustomer,
               alert_date,
               idtransaction,
               idalerttype,
               priority,
               iddevice)
               VALUES('Veículo "'||INFO.vehicle_code||'" Fora do horário de uso ('||to_char(vcommand_date,'dd/mm/yyyy hh24:mi:ss')||')'
                     , INFO.idcustomer
                     , current_timestamp
                     , vtransaction
                     , 10
                     , 'L'
                     ,info.iddevice
              );
    end if;
   end if;

   if /*((vlatitude=0) or (Vvel=0)) and (vidrecordtype=1) OR */(NOT VVALID) then
     return vtransaction;
   end if;
 -- alerta de velocidade do device
 if (Vvel>INFO.speed_limit) then
       insert into vlt_alert
              (alert,
               idcustomer,
               alert_date,
               idtransaction,
               idalerttype,
               priority,iddevice)
       select 'Veículo "'||INFO.vehicle_code||'" ultrapassou a velocidade de '||cast(INFO.speed_limit as varchar)||' km/h ('||cast(Vvel as varchar)||' Km/h).'  alert,
              INFO.idcustomer,
              current_timestamp alert_date,
              vtransaction,
              idalerttype,
              priority,info.iddevice
         from alert_customer
        where idalerttype=1
          AND NOT INFO.SPEED_LIMIT_ALERT;
       update device
          set speed_limit_alert = true;
 else
       update device
          set speed_limit_alert = false;
 end if;

-- marca pontos
   update interest_point
     set active= distance(latitude, longitude, vlatitude, vlongitude)<=radius;

 -- alerta proximidade de ponto
       insert into vlt_alert
              (alert,
               idcustomer,
               alert_date,
               idtransaction,
               idalerttype,
               priority,iddevice)
       select 'Veículo "'||info.vehicle_code||'" afastou do ponto "' || interest_point||'"'  alert,
              c.idcustomer,
              current_timestamp alert_date,
              vtransaction,
              idalerttype,
              priority,info.iddevice
  from alert_customer c
     , interest_point     dp
 where c.idalerttype=5
   and not dp.active and alert;

 update interest_point
  set alert = false,speed_alert=false
 where not active;

 -- marca certas ativas
 update fence
    set active= vlt_point_in_polygon_fnc(idfence, vlatitude, vlongitude);

 -- alerta saida da cerca
       insert into vlt_alert
              (alert,
               idcustomer,
               alert_date,
               idtransaction,
               idalerttype,
               priority,iddevice)
select 'Veículo "'||INFO.vehicle_code||'" saiu da cerca "' || fence||'"'  alert,
              c.idcustomer,
              current_timestamp alert_date,
              vtransaction,
              idalerttype,
              priority,info.iddevice
  from alert_customer c
     , fence dp
 where c.idalerttype=4
   and not dp.active and alert;

 update fence
  set alert= false,speed_alert=false
 where not active;

 -- alerta proximidade de ponto
       insert into vlt_alert
              (alert,
               idcustomer,
               alert_date,
               idtransaction,
               idalerttype,
               priority,iddevice)
select 'Veículo "'||INFO.vehicle_code||'" está próximo do ponto "' || interest_point||'"'  alert,
              c.idcustomer,
              current_timestamp alert_date,
              vtransaction,
              idalerttype,
              priority,info.iddevice
  from alert_customer c
     , interest_point dp
 where c.idalerttype=5
   and dp.active and not alert;

  update interest_point
  set alert=true
  where active
    and not alert;
 -- alerta entrada da cerca
       insert into vlt_alert
              (alert,
               idcustomer,
               alert_date,
               idtransaction,
               idalerttype,
               priority,iddevice)
select 'Veículo "'||INFO.vehicle_code||'" entrou na cerca "' || fence||'"'  alert,
              c.idcustomer,
              current_timestamp alert_date,
              vtransaction,
              idalerttype,
              priority,info.iddevice
  from alert_customer c
     , fence dp
 where c.idalerttype=4
   and dp.active and not alert;

  update fence
  set alert=true
  where idfence in(select dp.idfence
    from alert_customer c
       , fence dp
   where c.idalerttype=4
     and dp.active and not alert);

 -- alerta de velocidade do ponto
       insert into vlt_alert
              (alert,
               idcustomer,
               alert_date,
               idtransaction,
               idalerttype,
               priority,iddevice)
select 'Veículo "'||INFO.vehicle_code||'" próximo do ponto "' || interest_point||'", ultrapassou a velocidade limite de '||dp.speed||' Km/h'  alert,
              c.idcustomer,
              current_timestamp alert_date,
              vtransaction,
              idalerttype,
              priority,info.iddevice
  from alert_customer c
     , interest_point dp
 where c.idalerttype = 5
   and Vvel > speed
   and speed > 0
   and dp.active
   and not speed_alert;

  update interest_point
     set speed_alert=true
   where Vvel > speed
   and active
   and not speed_alert;

 update interest_point
    set speed_alert= active and Vvel >  CASE WHEN COALESCE(speed,0)=0 THEN VVEL ELSE COALESCE(speed,0) END;

 -- alerta de velocidade da cerca
       insert into vlt_alert
              (alert,
               idcustomer,
               alert_date,
               idtransaction,
               idalerttype,
               priority,iddevice)
select 'Veículo "'||INFO.vehicle_code||'" dentro da cerca "' || fence||'", ultrapassou a velocidade limite de '||speed||' Km/h'  alert,
              c.idcustomer,
              current_timestamp alert_date,
              vtransaction,
              idalerttype,
              priority,info.iddevice
  from alert_customer c
     , fence dp
 where c.idalerttype = 4
   and Vvel > speed
   and speed > 0
   and dp.active
   and not speed_alert;

  update fence
  set speed_alert=true
  where Vvel > speed
   and active
   and not speed_alert;

 update fence
    set speed_alert= active and Vvel > CASE WHEN COALESCE(speed,0)=0 THEN VVEL ELSE COALESCE(speed,0) END;
 -- pontos da transaction
 insert into vlt_transaction_interest_point(idtransaction,idinterestpoint)
  select vtransaction,idinterestpoint from interest_point
   where active;
 -- cercas da transaction
 insert into vlt_transaction_fence(idtransaction,idfence)
  select vtransaction,idfence from fence
   where active;

 if  vidrecordtype = 8 then
  -- alerta de tpms
       insert into vlt_alert
              (alert,
               idcustomer,
               alert_date,
               idtransaction,
               idalerttype,
               priority,
               iddevice)
        select case  tipo
                     when 'T' then 'Veículo "'||INFO.vehicle_code||'", Pneu '||t.tpms_sequence||', Temperatura ('||trunc(temperature)||') fora da faixa permitida ('||valr_min_temperature||' a '||valr_max_temperature||')'
                     when 'P' then 'Veículo "'||INFO.vehicle_code||'", Pneu '||t.tpms_sequence||', Pressão ('    ||trunc(pressure)   ||') fora da faixa permitida ('||valr_min_preassure||' a '||valr_max_preassure||')'
                     when 'L' then 'Veículo "'||INFO.vehicle_code||'", Pneu '||t.tpms_sequence||', Tem Vazamento'
                     when 'A' then 'Veículo "'||INFO.vehicle_code||'", Pneu '||t.tpms_sequence||', Problema na Comunicação'
                     when 'B' then 'Veículo "'||INFO.vehicle_code||'", Pneu '||t.tpms_sequence||', Problema na Bateria'
                end  alert,
                INFO.idcustomer,
                current_timestamp alert_date,
                v.idtransaction,
                idalerttype,
                priority,
                info.iddevice
          from vlt_tpms   t
             , alert_customer ac
             , vlt_tpms_model m
             , vlt_tpms_value v
             , (select 'T' tipo union select 'P' union select 'L' union select 'A' union select 'B') tipo
         where ac.idalerttype     = 2
           and t.iddevice        = INFO.iddevice
           and t.idtpmsvalue_last = v.idtpmsvalue
           and ((tipo='P'
                and v.pressure not between valr_min_preassure and valr_max_preassure
     and not alert_preassure)
        or
        (tipo='T'
     and v.temperature not between valr_min_temperature  and valr_max_temperature
     and not alert_temperature)
        or
        (tipo='L' and (v.leak)   and not alert_leak)
        or
        (tipo='A' and (not v.active) and not alert_active)
        or
        (tipo='B' and (not v.batery) and not alert_batery)
       );

      update vlt_tpms
         set alert_preassure =  (select (v.pressure not between info.valr_min_preassure and info.valr_max_preassure)
                from vlt_tpms_value v
               where vlt_tpms.idtpmsvalue_last = v.idtpmsvalue)
           , alert_temperature = (select (v.temperature not between info.valr_min_temperature  and info.valr_max_temperature)
                from vlt_tpms_value v
               where vlt_tpms.idtpmsvalue_last = v.idtpmsvalue)
           , alert_leak = (select leak
                from vlt_tpms_value v
               where vlt_tpms.idtpmsvalue_last = v.idtpmsvalue)
           , alert_active = (select not active
                from vlt_tpms_value v
               where vlt_tpms.idtpmsvalue_last = v.idtpmsvalue)
           , alert_batery = (select not batery
                from vlt_tpms_value v
               where vlt_tpms.idtpmsvalue_last = v.idtpmsvalue)
       where iddevice=info.iddevice;
 end if;


 end loop;
 return vtransaction;
end
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

CREATE OR REPLACE FUNCTION public.vlt_socket_con_gsn_prc (
  pcommand varchar
)
RETURNS varchar AS
$body$
declare
 vtransaction bigint;
 vdescription varchar;
 viddevice bigint;
 vdifh integer;
 pvel numeric;
 vidcustomer bigint;
 vcommand  varchar[];
 vconnected boolean;
 vdate     timestamp;
 vresult   integer;
 vplat numeric;
 vplong numeric;
 vpdata timestamp;
 vidrecordtype bigint;
 viddriver bigint;
 vlast_transaction bigint;
 vspeed_limit integer;
 vlimit_alert boolean;

/* exemplo
 01 - GSN:355096030559917      -- codigo equipamento
 02 - CID:89550531680001916829 -- codigo chip
 03 - CMD:PA:0                 -- <indefinido>
 04 - IG:1                     -- ignição 1=true
 05 - A1:0                     -- porta analogica 1
 06 - A2:0                     -- porta analogica 2
 07 - A3:0                     -- porta analogica 3
 08 - A4:0                     -- porta analogica 4
 09 - PW:120                   -- <indefinido>
 10 - TP:438                   -- <indefinido>
 11 - O1:0                     -- <indefinido>
 12 - O2:0                     -- <indefinido>
 13 - O3:1                     -- <indefinido>
 14 - GPS:134639.998           -- time
 15 - A                        -- validity: A-ok , V-invalid
 16 - 1643.2520                -- Latitude (16,7208666666667)
 17 - S                        -- S(-1)/N(1)
 18 - 04917.2596               -- Longitude (49,28766)
 19 - W                        -- W(-1)/E(1)
 20 - 000.0                    -- velocidade em nós
 21 - 000.0                    -- curso
 22 - 020913                   -- data
 23 - <vazio>                  -- <indefinido>
 24 - <vazio>                  -- <indefinido>
 25 - <vazio>                  -- <indefinido>
 26 - A*PER:NAFPVS:7.3.0FVJM:0 -- <indefinido>
 27 - SPD:                     -- <indefinido>
*/
begin
 if pcommand is null or pcommand='' then
    RAISE EXCEPTION 'Enter the command of the device';
 end if;
 select '{"'||replace(pcommand,',','","')||'"}'
   into vcommand;

 vpdata:= to_timestamp(vcommand[22]||' '||replace(vcommand[14],'GPS:',''), 'DDMMYY HH24MISS');
 --code
 vcommand[1]:= replace(vcommand[1],'nullLOG:GSN:','');
 vcommand[1]:= replace(vcommand[1],'LOG:GSN:','');
 vcommand[1]:= replace(vcommand[1],'GSN:','');

 --latitude
 vplat:=dm2dd(vcommand[16]);
 if vcommand[17]='S' then
    vplat:=-1*vplat;
 end if;

 --longitude
 vplong:=dm2dd(vcommand[18]);
 if vcommand[19]='W' then
    vplong:=-1*vplong;
 end if;

 vresult:=0;
 vdate:=now();


 vconnected:= vcommand[4] = 'IG:1';

 pvel:=(vcommand[20]::numeric)* 1.852;

 select iddevice
      , idcustomer
      , (select case when s.daylight_saving
                      then s.difference_daylight_saving
                      else s.difference
                 end
           FROM VLT_SCHEDULE s
          where s.idschedule=vlt_device.idschedule)
       , last_transaction
       , description
       , speed_limit
       ,vlimit_alert
   into viddevice
      , vidcustomer
      , vdifh
      , vlast_transaction
      , vdescription
      ,vspeed_limit
      ,vlimit_alert
   from vlt_device
  where code = vcommand[1];

  if not FOUND then
     insert into vlt_device(code,description,last_date,last_sequence,last_latitude,last_longitude,connected,iddevicemodel,idtpmsmodel)
                     values(vcommand[1],'gsn:'||vcommand[1],vdate,0,vplat,vplong,vconnected,(select iddevicemodel from vlt_device_model where description like 'gsn%' limit 1),(select id from vlt_tpms_model where desc_model_tmps like 'gsn%' limit 1));
    vdescription:='gsn:'||vcommand[1];
       select iddevice
            , (select case when s.daylight_saving then s.difference_daylight_saving else s.difference end
         FROM VLT_SCHEDULE s
        where s.idschedule=vlt_device.idschedule)
         into viddevice,vdifh
         from vlt_device
        where code=vcommand[1];

        insert into vlt_device_last(iddevice,idrecordtype)
             select viddevice,idrecordtype from vlt_record_type;
  end if;
  SELECT nextval('vlt_transaction_idtransaction_seq')
    into vtransaction;
  --gps
  vidrecordtype:=1;
  insert into
    vlt_transaction(idtransaction
            , iddevice
            , idrecordtype
            , command
            , transaction_date
            , boot
            , online
            , command_date
            , command_date_original
            , transaction_sequence
            , speed
            , course
            , latitude
            , longitude
            , iddriver
            , connected
            , valid
            )
     values(vtransaction
           ,viddevice
           ,vidrecordtype
           ,pcommand
           ,vdate
           ,'N'
           ,'O'
           ,case when vdifh<0 then vpdata - cast(abs(vdifh)||':00:00' as time) else vpdata + cast(abs(vdifh)||':00:00' as time) end
           ,vpdata
           ,0
           ,pvel
           ,cast(vcommand[21] as numeric)
           ,vplat
           ,vplong
           ,viddriver
           ,vconnected
           ,vplat<>0
           );
    SELECT vlt_agrupa_transaction(viddevice,vlast_transaction,vidrecordtype) into vresult;

   update vlt_device
     set last_date=vpdata
       , last_sequence=0
   where iddevice= viddevice;

  update vlt_device_last
     set idtransaction = vtransaction
   where iddevice      = viddevice
     and idrecordtype  = vidrecordtype;

 insert into vlt_transaction_last(idtransaction,idrecordtype,idtransaction_last)
  select vtransaction,idrecordtype,idtransaction
    from vlt_device_last
   where iddevice=viddevice
     and idtransaction is not null;

  -- verifica pontos do device
  insert into vlt_device_interest_point(iddevice,idinterestpoint,active)
  select iddevice,p.idinterestpoint,false from vlt_device d,vlt_interest_point p
   where d.idcustomer = p.idcustomer
     and p.idcustomer = vidcustomer
     and not exists(select null
                      from vlt_device_interest_point di
                     where di.iddevice = d.iddevice
                       and di.idinterestpoint = p.idinterestpoint);
  -- verifica cercas do device
  insert into vlt_device_fence(iddevice,idfence,active)
  select iddevice,p.idfence,false from vlt_device d,vlt_fence p
   where d.idcustomer = p.idcustomer
     and p.idcustomer = vidcustomer
     and not exists(select null
                      from vlt_device_fence di
                     where di.iddevice = d.iddevice
                       and di.idfence = p.idfence);

  -- marca pontos
 update vlt_device_interest_point
  set active= (select distance(latitude, longitude, vplat, vplong)<=vlt_interest_point.radius
                 from vlt_interest_point
                where vlt_interest_point.idinterestpoint=vlt_device_interest_point.idinterestpoint)
 where iddevice=viddevice;
 -- alerta proximidade de ponto
       insert into vlt_alert
              (alert,
               idcustomer,
               alert_date,
               idtransaction,
               idalerttype,
               priority)
select 'O veículo "'||vdescription||'" afastou do ponto "' || interest_point||'"'  alert,
              c.idcustomer,
              current_timestamp alert_date,
              vtransaction,
              idalerttype,
              priority
  from vlt_alert_customer c,vlt_device_interest_point dp,vlt_interest_point p
 where c.idcustomer=vidcustomer
   and c.idalerttype=5
   and dp.iddevice=viddevice
   and not dp.active and alert
   and dp.idinterestpoint=p.idinterestpoint;

 update vlt_device_interest_point
  set alert= false,speed_alert=false
 where not active;

 -- marca certas ativas
 update vlt_device_fence
    set active= vlt_point_in_polygon_fnc(idfence, vplat, vplong)
  where iddevice=viddevice;
 -- alerta saida da cerca
       insert into vlt_alert
              (alert,
               idcustomer,
               alert_date,
               idtransaction,
               idalerttype,
               priority)
select 'O veículo "'||vdescription||'" saiu da cerca "' || fence||'"'  alert,
              c.idcustomer,
              current_timestamp alert_date,
              vtransaction,
              idalerttype,
              priority
  from vlt_alert_customer c,vlt_device_fence dp,vlt_fence p
 where c.idcustomer=vidcustomer
   and c.idalerttype=5
   and dp.iddevice=viddevice
   and not dp.active and alert
   and dp.idfence=p.idfence;
 update vlt_device_fence
  set alert= false,speed_alert=false
 where not active;

 -- alerta proximidade de ponto
       insert into vlt_alert
              (alert,
               idcustomer,
               alert_date,
               idtransaction,
               idalerttype,
               priority)
select 'O veículo "'||vdescription||'" está próximo do ponto "' || interest_point||'"'  alert,
              c.idcustomer,
              current_timestamp alert_date,
              vtransaction,
              idalerttype,
              priority
  from vlt_alert_customer c,vlt_device_interest_point dp,vlt_interest_point p
 where c.idcustomer=vidcustomer
   and c.idalerttype=5
   and dp.iddevice=viddevice
   and dp.active and not alert
   and dp.idinterestpoint=p.idinterestpoint;

  update vlt_device_interest_point
  set alert=true
  where iddeviceinterestpoint in(select dp.iddeviceinterestpoint
    from vlt_alert_customer c,vlt_device_interest_point dp,vlt_interest_point p
   where c.idcustomer=vidcustomer
     and c.idalerttype=5
     and dp.iddevice=viddevice
     and dp.active and not alert
     and dp.idinterestpoint=p.idinterestpoint);
 -- alerta entrada da cerca
       insert into vlt_alert
              (alert,
               idcustomer,
               alert_date,
               idtransaction,
               idalerttype,
               priority)
select 'O veículo "'||vdescription||'" entrou na cerca "' || fence||'"'  alert,
              c.idcustomer,
              current_timestamp alert_date,
              vtransaction,
              idalerttype,
              priority
  from vlt_alert_customer c,vlt_device_fence dp,vlt_fence p
 where c.idcustomer=vidcustomer
   and c.idalerttype=5
   and dp.iddevice=viddevice
   and dp.active and not alert
   and dp.idfence=p.idfence;

  update vlt_device_fence
  set alert=true
  where iddevicefence in(select dp.iddevicefence
    from vlt_alert_customer c,vlt_device_fence dp,vlt_fence p
   where c.idcustomer=vidcustomer
     and c.idalerttype=5
     and dp.iddevice=viddevice
     and dp.active and not alert
     and dp.idfence=p.idfence);

 -- alerta de velocidade do device
 if (pvel>vspeed_limit) then
    if not vlimit_alert then
       insert into vlt_alert
              (alert,
               idcustomer,
               alert_date,
               idtransaction,
               idalerttype,
               priority)
       select 'O veículo "'||vdescription||'" ultrapassou a velocidade de '||cast(vspeed_limit as varchar)||' km/h ('||cast(pvel as varchar)||' Km/h).'  alert,
              vidcustomer,
              current_timestamp alert_date,
              vtransaction,
              idalerttype,
              priority
         from vlt_alert_customer
        where idalerttype=1
          and idcustomer=vidcustomer;

       update vlt_device
          set speed_limit_alert = true
        where iddevice = viddevice;
  end if;
 else
   if vlimit_alert then
       update vlt_device
          set speed_limit_alert = false
        where iddevice = viddevice;
   end if;
 end if;
 -- alerta de velocidade do ponto
       insert into vlt_alert
              (alert,
               idcustomer,
               alert_date,
               idtransaction,
               idalerttype,
               priority)
select 'O veículo "'||vdescription||'" próximo do ponto "' || interest_point||'", ultrapassou a velocidade limite de '||p.speed||' Km/h'  alert,
              c.idcustomer,
              current_timestamp alert_date,
              vtransaction,
              idalerttype,
              priority
  from vlt_alert_customer c,vlt_device_interest_point dp,vlt_interest_point p
 where c.idcustomer  = vidcustomer
   and c.idalerttype = 5
   and dp.iddevice   = viddevice
   and pvel > p.speed
   and p.speed > 0
   and dp.active
   and not speed_alert
   and dp.idinterestpoint = p.idinterestpoint;

  update vlt_device_interest_point
  set speed_alert=true
  where iddeviceinterestpoint in(select dp.iddeviceinterestpoint
    from vlt_alert_customer c,vlt_device_interest_point dp,vlt_interest_point p
 where c.idcustomer  = vidcustomer
   and c.idalerttype = 5
   and dp.iddevice   = viddevice
   and pvel > p.speed
   and dp.active
   and not speed_alert
   and dp.idinterestpoint = p.idinterestpoint);

 update vlt_device_interest_point
    set speed_alert= active and pvel > (select CASE WHEN COALESCE(speed,0)=0 THEN PVEL ELSE COALESCE(speed,0) END
                               from vlt_interest_point
                              where vlt_device_interest_point.idinterestpoint = vlt_interest_point.idinterestpoint)
  where iddevice=viddevice;

 -- alerta de velocidade da cerca
       insert into vlt_alert
              (alert,
               idcustomer,
               alert_date,
               idtransaction,
               idalerttype,
               priority)
select 'O veículo "'||vdescription||'" dentro da cerca "' || fence||'", ultrapassou a velocidade limite de '||p.speed||' Km/h'  alert,
              c.idcustomer,
              current_timestamp alert_date,
              vtransaction,
              idalerttype,
              priority
  from vlt_alert_customer c,vlt_device_fence dp,vlt_fence p
 where c.idcustomer  = vidcustomer
   and c.idalerttype = 5
   and dp.iddevice   = viddevice
   and pvel > p.speed
   and p.speed > 0
   and dp.active
   and not speed_alert
   and dp.idfence = p.idfence;

  update vlt_device_fence
  set speed_alert=true
  where iddevicefence in(select dp.iddevicefence
    from vlt_alert_customer c,vlt_device_fence dp,vlt_fence p
 where c.idcustomer  = vidcustomer
   and c.idalerttype = 5
   and dp.iddevice   = viddevice
   and pvel > p.speed
   and dp.active
   and not speed_alert
   and dp.idfence = p.idfence);

 update vlt_device_fence
    set speed_alert= active and pvel > (select CASE WHEN COALESCE(speed,0)=0 THEN PVEL ELSE COALESCE(speed,0) END
                               from vlt_fence
                              where vlt_device_fence.idfence = vlt_fence.idfence)
  where iddevice=viddevice;
 -- pontos da transaction
 insert into vlt_transaction_interest_point(idtransaction,idinterestpoint)
  select vtransaction,idinterestpoint from vlt_device_interest_point
   where iddevice=viddevice
     and active;
 -- cercas da transaction
 insert into vlt_transaction_fence(idtransaction,idfence)
  select vtransaction,idfence from vlt_device_fence
   where iddevice=viddevice
     and active;
 if  vidrecordtype = 8 then
  -- alerta de tpms
       insert into vlt_alert
              (alert,
               idcustomer,
               alert_date,
               idtransaction,
               idalerttype,
               priority)
        select case  tipo
                   when 'T' then 'Veículo "'||description||'", Pneu '||t.tpms_sequence||', temperatura ('||trunc(temperature)||') fora da faixa permitida ('||valr_min_temperature||' a '||valr_max_temperature||')'
                   when 'P' then 'Veículo "'||description||'", Pneu '||t.tpms_sequence||', Pressão ('    ||trunc(pressure)   ||') fora da faixa permitida ('||valr_min_preassure||' a '||valr_max_preassure||')'
                   when 'F' then 'Veículo "'||description||'", Pneu '||t.tpms_sequence||', Tem Vazamento'
               end  alert,
                      ac.idcustomer,
                      current_timestamp alert_date,
                      v.idtransaction,
                      idalerttype,
                      priority
          from vlt_tpms   t
             , vlt_device d
             , vlt_alert_customer ac
             , vlt_tpms_model m
             , vlt_tpms_value v
             , (select 'T' tipo union select 'P' union select 'F') tipo
         where ac.idalerttype     = 2
           and  t.iddevice        = viddevice
           and t.iddevice         = d.iddevice
           and ac.idcustomer      = vidcustomer
           and d.idtpmsmodel      = m.id
           and t.idtpmsvalue_last = v.idtpmsvalue
           and ((tipo='P'
                and v.pressure not between valr_min_preassure and valr_max_preassure
     and not alert_preassure)
        or
        (tipo='T'
     and v.temperature not between valr_min_temperature  and valr_max_temperature
     and not alert_temperature)
        or
        (tipo='F' and leak and not alert_leak)
       );

      update vlt_tpms
         set alert_preassure =  (select (v.pressure not between valr_min_preassure and valr_max_preassure)
                from vlt_tpms_model m
                   , vlt_tpms_value v
               where m.id = vidtpmsmodel
                 and vlt_tpms.idtpmsvalue_last = v.idtpmsvalue)
           , alert_temperature = (select (v.temperature not between valr_min_temperature  and valr_max_temperature)
                from vlt_tpms_model m
                   , vlt_tpms_value v
               where m.id = vidtpmsmodel
                 and vlt_tpms.idtpmsvalue_last = v.idtpmsvalue)
           , alert_leak = (select leak
                from vlt_tpms_model m
                   , vlt_tpms_value v
               where m.id = vidtpmsmodel
                 and vlt_tpms.idtpmsvalue_last = v.idtpmsvalue)
       where iddevice=viddevice;
 end if;
 return 0;
end
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

ALTER FUNCTION public.vlt_transaction_address_fnc ()
  OWNER TO pgsql;

CREATE OR REPLACE FUNCTION public.vlt_transaction_error_new (
)
RETURNS trigger AS
$body$
begin
insert into vlt_alert(alert,iduser,idcustomer,alert_date,read_date,idtransaction,idalerttype,priority,notification)
select 'Erro Velotrack: '||trim(case when new.iddevice is not null
            then ' Equipamento: '||(select code from vlt_device where iddevice=new.iddevice)||', '
            else ''
       end
      ||
      case when new.command is not null
            then ' Comando: '||new.command||', '
            else ''
       end
      ||
      new.error) alert,
       null iduser,
       idcustomer,
       now() alert_date,
       null  read_date,
       null  idtransaction,
       idalerttype,
       'L'   priority,
       false notification
  from vlt_alert_customer
 where idalerttype=9
   and (cast(date_part('epoch',age(now(),alert_last))as BIGINT)>alert_time or alert_last is null);
  return new;
end;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

ALTER FUNCTION public.vlt_update_install_center_search_fnc (pidinstallcenter bigint)
  OWNER TO pgsql;

ALTER FUNCTION public.vlt_update_model_command_search_fnc (pidmodelcommand bigint)
  OWNER TO pgsql;

ALTER FUNCTION public.vlt_update_monitoring_center_search_fnc (pidmonitoringcenter bigint)
  OWNER TO pgsql;

CREATE FUNCTION public.vlt_update_sim_search_fnc (
  pidsim bigint
)
RETURNS pg_catalog.void AS
$body$
begin
   update vlt_sim
      set sim_search = to_tsvector(utl_text_search((coalesce(code_sim, '') || ' ' || coalesce(desc_phone, ''))))
    where idsim = pidsim;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER;

CREATE SEQUENCE public.fin_bank_account_idbankaccount_seq
  INCREMENT 1 MINVALUE 1
  MAXVALUE 9223372036854775807 START 1
  CACHE 1;

ALTER SEQUENCE public.fin_bank_account_idbankaccount_seq RESTART WITH 5;

CREATE SEQUENCE public.fin_bank_idbank_seq
  INCREMENT 1 MINVALUE 1
  MAXVALUE 9223372036854775807 START 1
  CACHE 1;

ALTER SEQUENCE public.fin_bank_idbank_seq RESTART WITH 17;

CREATE SEQUENCE public.fin_billet_idbillet_seq
  INCREMENT 1 MINVALUE 1
  MAXVALUE 9223372036854775807 START 1
  CACHE 1;

ALTER SEQUENCE public.fin_billet_idbillet_seq RESTART WITH 5;

CREATE SEQUENCE public.fin_billet_item_idbilletitem_seq
  INCREMENT 1 MINVALUE 1
  MAXVALUE 9223372036854775807 START 1
  CACHE 1;

ALTER SEQUENCE public.fin_billet_item_idbilletitem_seq RESTART WITH 29;

CREATE SEQUENCE public.fin_item_model_billet_iditemmodelbillet_seq
  INCREMENT 1 MINVALUE 1
  MAXVALUE 9223372036854775807 START 1
  CACHE 1;

ALTER SEQUENCE public.fin_item_model_billet_iditemmodelbillet_seq RESTART WITH 29;

CREATE SEQUENCE public.fin_item_model_billet_value_iditemmodelbilletvalue_seq
  INCREMENT 1 MINVALUE 1
  MAXVALUE 9223372036854775807 START 1
  CACHE 1;

ALTER SEQUENCE public.fin_item_model_billet_value_iditemmodelbilletvalue_seq RESTART WITH 9;

CREATE SEQUENCE public.fin_model_billet_idmodelbillet_seq
  INCREMENT 1 MINVALUE 1
  MAXVALUE 9223372036854775807 START 1
  CACHE 1;

ALTER SEQUENCE public.fin_model_billet_idmodelbillet_seq RESTART WITH 5;

CREATE SEQUENCE public.par_sns_application_idsnsapplication_seq
  INCREMENT 1 MINVALUE 1
  MAXVALUE 9223372036854775807 START 1
  CACHE 1;

ALTER SEQUENCE public.par_sns_application_idsnsapplication_seq RESTART WITH 6;

CREATE SEQUENCE public.par_sns_idsns_seq
  INCREMENT 1 MINVALUE 1
  MAXVALUE 9223372036854775807 START 1
  CACHE 1;

ALTER SEQUENCE public.par_sns_idsns_seq RESTART WITH 18;

CREATE SEQUENCE public.vlt_device_sin_iddevicesin_seq
  INCREMENT 1 MINVALUE 1
  MAXVALUE 9223372036854775807 START 1
  CACHE 1;

CREATE SEQUENCE public.vlt_sim_history_idsimhistory_seq
  INCREMENT 1 MINVALUE 1
  MAXVALUE 9223372036854775807 START 1
  CACHE 1;

ALTER SEQUENCE public.vlt_sim_history_idsimhistory_seq RESTART WITH 35;

CREATE SEQUENCE public.vlt_sin_idsin_seq
  INCREMENT 1 MINVALUE 1
  MAXVALUE 9223372036854775807 START 1
  CACHE 1;

ALTER SEQUENCE public.vlt_sin_idsin_seq RESTART WITH 15;

CREATE TABLE public.fin_bank (
  idbank BIGINT DEFAULT nextval('fin_bank_idbank_seq'::regclass) NOT NULL,
  code VARCHAR(3) NOT NULL,
  cnpj VARCHAR(18),
  bank VARCHAR(60) NOT NULL,
  bank_search TSVECTOR,
  CONSTRAINT fin_bank_pk PRIMARY KEY(idbank),
  CONSTRAINT fin_bank_uk UNIQUE(bank)
) WITHOUT OIDS;

CREATE TRIGGER fin_bank_update_search_trg AFTER INSERT OR UPDATE
ON public.fin_bank FOR EACH ROW
EXECUTE PROCEDURE public.fin_bank_update_search_fnc();

CREATE TABLE public.fin_bank_account (
  idbankaccount BIGINT DEFAULT nextval('fin_bank_account_idbankaccount_seq'::regclass) NOT NULL,
  idbank BIGINT NOT NULL,
  account VARCHAR(60) NOT NULL,
  agency VARCHAR(10) NOT NULL,
  agency_dv VARCHAR(10),
  account_number VARCHAR(10) NOT NULL,
  account_dv VARCHAR(10),
  wallet VARCHAR(10),
  modality VARCHAR(10),
  accord VARCHAR(10),
  contract VARCHAR(10),
  variation_wallet VARCHAR(10),
  idseller BIGINT NOT NULL,
  CONSTRAINT fin_bank_account_pk PRIMARY KEY(idbankaccount),
  CONSTRAINT fin_bank_account_fk1 FOREIGN KEY (idbank)
    REFERENCES public.fin_bank(idbank)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT fin_bank_account_fk2 FOREIGN KEY (idseller)
    REFERENCES public.vlt_seller(idseller)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE TABLE public.fin_billet (
  idbillet BIGINT DEFAULT nextval('fin_billet_idbillet_seq'::regclass) NOT NULL,
  idseller BIGINT NOT NULL,
  idcustomer BIGINT NOT NULL,
  idbankaccount BIGINT NOT NULL,
  billet VARCHAR(60),
  value VARCHAR(10),
  CONSTRAINT fin_billet_pk PRIMARY KEY(idbillet),
  CONSTRAINT fin_billet_fk1 FOREIGN KEY (idseller)
    REFERENCES public.vlt_seller(idseller)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT fin_billet_fk2 FOREIGN KEY (idcustomer)
    REFERENCES public.par_customer(idcustomer)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT fin_billet_fk3 FOREIGN KEY (idbankaccount)
    REFERENCES public.fin_bank_account(idbankaccount)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE TABLE public.fin_billet_item (
  idbilletitem BIGINT DEFAULT nextval('fin_billet_item_idbilletitem_seq'::regclass) NOT NULL,
  idbillet BIGINT NOT NULL,
  value VARCHAR(120),
  iditemmodelbillet BIGINT NOT NULL,
  CONSTRAINT fin_billet_item_pk PRIMARY KEY(idbilletitem),
  CONSTRAINT fin_billet_item_fk1 FOREIGN KEY (idbillet)
    REFERENCES public.fin_billet(idbillet)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT fin_billet_item_fk2 FOREIGN KEY (iditemmodelbillet)
    REFERENCES public.fin_item_model_billet(iditemmodelbillet)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE INDEX fin_billet_item_idx01 ON public.fin_billet_item
  USING btree (iditemmodelbillet);

CREATE TABLE public.fin_item_model_billet (
  iditemmodelbillet BIGINT DEFAULT nextval('fin_item_model_billet_iditemmodelbillet_seq'::regclass) NOT NULL,
  idmodelbillet BIGINT NOT NULL,
  item_model_billet VARCHAR(60),
  max_size BIGINT,
  min_size BIGINT,
  code VARCHAR(60),
  type pg_catalog."char",
  required pg_catalog."char",
  CONSTRAINT fin_item_model_billet_pk PRIMARY KEY(iditemmodelbillet),
  CONSTRAINT required CHECK (required = ANY (ARRAY['S'::"char", 'N'::"char"])),
  CONSTRAINT type CHECK (type = ANY (ARRAY['T'::"char", 'D'::"char", 'N'::"char"])),
  CONSTRAINT fin_item_model_billet_fk1 FOREIGN KEY (idmodelbillet)
    REFERENCES public.fin_model_billet(idmodelbillet)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE INDEX fin_item_model_billet_idx01 ON public.fin_item_model_billet
  USING btree (idmodelbillet);

CREATE TABLE public.fin_item_model_billet_value (
  iditemmodelbilletvalue BIGINT DEFAULT nextval('fin_item_model_billet_value_iditemmodelbilletvalue_seq'::regclass) NOT NULL,
  iditemmodelbillet BIGINT NOT NULL,
  value VARCHAR(10),
  name VARCHAR(10),
  CONSTRAINT fin_item_model_billet_value_pk PRIMARY KEY(iditemmodelbilletvalue),
  CONSTRAINT fin_item_model_billet_value_fk1 FOREIGN KEY (iditemmodelbillet)
    REFERENCES public.fin_item_model_billet(iditemmodelbillet)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE TABLE public.fin_model_billet (
  idmodelbillet BIGINT DEFAULT nextval('fin_model_billet_idmodelbillet_seq'::regclass) NOT NULL,
  model_billet VARCHAR(60),
  idbank BIGINT NOT NULL,
  CONSTRAINT fin_model_billet_pk PRIMARY KEY(idmodelbillet),
  CONSTRAINT fin_model_billet_fk1 FOREIGN KEY (idbank)
    REFERENCES public.fin_bank(idbank)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE INDEX fin_model_billet_idx01 ON public.fin_model_billet
  USING btree (idbank);

-- object recreation
ALTER TABLE public.par_access
  DROP CONSTRAINT check01 RESTRICT;

ALTER TABLE public.par_access
  ADD CONSTRAINT check01 CHECK ((valid)::text = ANY ((ARRAY['S'::character varying, 'N'::character varying])::text[]));

ALTER TABLE public.par_etiqueta
  OWNER TO pgsql;

-- object recreation
ALTER TABLE public.par_message
  DROP CONSTRAINT check01 RESTRICT;

ALTER TABLE public.par_message
  ADD CONSTRAINT check01 CHECK ((is_read)::text = ANY ((ARRAY['N'::character varying, 'S'::character varying])::text[]));

-- object recreation
ALTER TABLE public.par_message
  DROP CONSTRAINT check02 RESTRICT;

ALTER TABLE public.par_message
  ADD CONSTRAINT check02 CHECK ((priority)::text = ANY ((ARRAY['L'::character varying, 'M'::character varying, 'H'::character varying])::text[]));

ALTER TABLE public.par_message
  ADD COLUMN desc_type VARCHAR(20);

COMMENT ON COLUMN public.par_message.desc_type
IS 'Tipo de notificação do SNS';

ALTER TABLE public.par_message
  ADD COLUMN to_idcustomer BIGINT;

ALTER TABLE public.par_message
  ADD COLUMN idsns BIGINT;

ALTER TABLE public.par_message
  ADD CONSTRAINT foreign_key05 FOREIGN KEY (to_idcustomer)
    REFERENCES public.par_customer(idcustomer)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

ALTER TABLE public.par_message
  ADD CONSTRAINT foreign_key06 FOREIGN KEY (idsns)
    REFERENCES public.par_sns(idsns)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

CREATE TABLE public.par_sns (
  idsns BIGINT DEFAULT nextval('par_sns_idsns_seq'::regclass) NOT NULL,
  desc_token VARCHAR(400) NOT NULL,
  iduser BIGINT NOT NULL,
  desc_arn VARCHAR(500),
  idsnsapplication BIGINT,
  CONSTRAINT par_sns_pkey PRIMARY KEY(idsns),
  CONSTRAINT foreign_key01 FOREIGN KEY (idsnsapplication)
    REFERENCES public.par_sns_application(idsnsapplication)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT foreign_key02 FOREIGN KEY (iduser)
    REFERENCES public.par_user(iduser)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE TABLE public.par_sns_application (
  idsnsapplication BIGINT DEFAULT nextval('par_sns_application_idsnsapplication_seq'::regclass) NOT NULL,
  application VARCHAR(60) NOT NULL,
  arn VARCHAR(400) NOT NULL,
  CONSTRAINT par_sns_application_pk PRIMARY KEY(idsnsapplication),
  CONSTRAINT par_sns_application_uk UNIQUE(arn)
) WITHOUT OIDS;

DROP VIEW public.rba_profile_routes_vw;
ALTER TABLE public.rba_routes
  ALTER COLUMN desc_route SET NOT NULL;
CREATE OR REPLACE VIEW public.rba_profile_routes_vw (
    id,
    idroute,
    desc_route_name,
    has_access)
AS
 SELECT q.id, rba_routes.id AS idroute, rba_routes.desc_route_name, COALESCE(( SELECT true AS bool
           FROM rba_profile_routes pf
          WHERE pf.rbaprof_id = q.id AND pf.rbarout_id = rba_routes.id), false) AS has_access
   FROM ( SELECT rba_profile.id,
                CASE
                    WHEN rba_profile.parcust_id IS NOT NULL THEN 'CUS'::text
                    WHEN rba_profile.idseller IS NOT NULL THEN 'SEL'::text
                    WHEN rba_profile.idmonitoringcenter IS NOT NULL THEN 'MCE'::text
                    WHEN rba_profile.is_administrator THEN 'ADM'::text
                    ELSE NULL::text
                END AS systype
           FROM rba_profile) q
   JOIN rba_routes ON rba_routes.indr_systype::text = q.systype
  ORDER BY q.id, rba_routes.id;

CREATE UNIQUE INDEX rba_routes_all_uk ON public.rba_routes
  USING btree (desc_route, indr_type, desc_route_name, indr_systype);

CREATE TABLE public.tbl_migration (
  version VARCHAR(255) NOT NULL,
  apply_time INTEGER,
  CONSTRAINT tbl_migration_pkey PRIMARY KEY(version)
) WITHOUT OIDS;

-- object recreation
DROP INDEX public.vlt_alert_idx2;

CREATE INDEX vlt_alert_idx2 ON public.vlt_alert
  USING btree (idcustomer, read_date);

ALTER INDEX public.vlt_alert_idx2
  OWNER TO pgc;

-- object recreation
ALTER TABLE public.vlt_alert_history
  DROP CONSTRAINT vlt_alert_history_fk RESTRICT;

ALTER TABLE public.vlt_alert_history
  ADD CONSTRAINT vlt_alert_history_fk FOREIGN KEY (idalert)
    REFERENCES public.vlt_alert(idalert)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

-- object recreation
ALTER TABLE public.vlt_command
  DROP CONSTRAINT vlt_command_ck2 RESTRICT;

ALTER TABLE public.vlt_command
  ADD CONSTRAINT vlt_command_ck2 CHECK ((status)::text = ANY ((ARRAY['I'::character varying, 'A'::character varying, 'E'::character varying, 'X'::character varying, 'O'::character varying, 'T'::character varying])::text[]));

ALTER TABLE public.vlt_console
  OWNER TO pgsql;

ALTER TABLE public.vlt_console_update
  OWNER TO pgsql;

ALTER TABLE public.vlt_country
  ADD CONSTRAINT vlt_country_pkey
    PRIMARY KEY (idcountry);

ALTER TABLE public.vlt_customer_history
  ALTER COLUMN idcustomerhistory SET STATISTICS 0;

ALTER TABLE public.vlt_customer_history
  ALTER COLUMN idcustomer SET STATISTICS 0;

ALTER TABLE public.vlt_customer_history
  ALTER COLUMN idseller_newer SET STATISTICS 0;

DROP VIEW public.vlt_transaction_driver_vw;
DROP VIEW public.vlt_tpms_disparity_vw;
DROP VIEW public.vlt_seller_dc_vw;
DROP VIEW public.vlt_refuel_vw;
DROP VIEW public.vlt_port_status_vw;
DROP VIEW public.vlt_grouped_transactions_vw;
DROP VIEW public.vlt_device_vw_old;
DROP VIEW public.vlt_device_vw_ant;
DROP VIEW public.vlt_device_vw2;
DROP VIEW public.vlt_device_vw;
DROP VIEW public.vlt_device_search_vw;
DROP VIEW public.vlt_device_finger_vw;
DROP VIEW public.vlt_command_status_vw;
DROP VIEW public.sel_customer_list_vw;
DROP VIEW public.rpt_worked_hours_driver_vw;
DROP VIEW public.rpt_worked_hours_device_vw;
DROP VIEW public.rpt_speed_vw;
DROP VIEW public.rpt_route_vw;
DROP VIEW public.rep_route_vw;
DROP VIEW public.rep_online_offline_vw;
DROP VIEW public.par_message_vw;
DROP VIEW public.par_etiqueta_vw;
DROP VIEW public.mce_refuel_vw;
DROP VIEW public.mce_refuel_station_vw;
DROP VIEW public.mce_driver_vw;
DROP VIEW public.mce_device_vw;
DROP VIEW public.mce_customer_vw;
ALTER TABLE public.vlt_device
  ALTER COLUMN iddevicemodel DROP DEFAULT;

ALTER TABLE public.vlt_device
  ALTER COLUMN iddevicemodel DROP NOT NULL;
CREATE OR REPLACE VIEW public.mce_customer_vw (
    iddevice,
    idmonitoringcenter,
    idcustomer,
    customer,
    email,
    dthr_cadastro,
    desc_address,
    desc_complement,
    numr_address,
    desc_district,
    desc_city,
    desc_state,
    desc_country,
    desc_zip_address,
    desc_phone,
    desc_cellphone,
    desc_cgc,
    desc_ddd_cellphone,
    desc_ddd_phone,
    idseller,
    customer_search,
    rg,
    active,
    block_message)
AS
 SELECT vlt_device.iddevice, vlt_device_mc.idmonitoringcenter, par_customer.idcustomer, par_customer.customer, par_customer.email, par_customer.dthr_cadastro, par_customer.desc_address, par_customer.desc_complement, par_customer.numr_address, par_customer.desc_district, par_customer.desc_city, par_customer.desc_state, par_customer.desc_country, par_customer.desc_zip_address, par_customer.desc_phone, par_customer.desc_cellphone, par_customer.desc_cgc, par_customer.desc_ddd_cellphone, par_customer.desc_ddd_phone, par_customer.idseller, par_customer.customer_search, par_customer.rg, par_customer.active, par_customer.block_message
   FROM vlt_device
   JOIN par_customer ON par_customer.idcustomer = vlt_device.idcustomer
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice;
CREATE OR REPLACE VIEW public.mce_device_vw (
    iddevice,
    code,
    device_search,
    description,
    vehicle_code,
    address,
    speed,
    connected,
    command_date,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    idmonitoringcenter,
    customer,
    idcustomer,
    driver,
    active,
    speed_limit,
    renavam,
    chassi,
    mark_vehicle,
    model_vehicle,
    color_vehicle,
    fuel_estimate)
AS
 SELECT vlt_device.iddevice, vlt_device.code, vlt_device.device_search, vlt_device.description, vlt_device.vehicle_code, vlt_transaction.address, vlt_transaction.speed, vlt_transaction.connected, vlt_transaction.command_date, vlt_device_model.has_battery, vlt_device_model.has_analog_ports, vlt_device_model.has_digital_ports, vlt_device_model.has_tpms, vlt_device_model.has_fuel, vlt_device_model.has_temperature, vlt_device_mc.idmonitoringcenter, par_customer.customer, par_customer.idcustomer, vlt_driver.driver, vlt_device.active, vlt_device.speed_limit, vlt_device.renavam, vlt_device.chassi, vlt_device.mark_vehicle, vlt_device.model_vehicle, vlt_device.color_vehicle, vlt_device.fuel_estimate
   FROM vlt_device
   LEFT JOIN vlt_driver ON vlt_driver.iddriver = vlt_device.last_driver
   JOIN vlt_transaction ON vlt_transaction.idtransaction = vlt_last_tran_dev_fnc(vlt_device.iddevice)
   JOIN vlt_device_model ON vlt_device_model.iddevicemodel = vlt_device.iddevicemodel
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice
   JOIN par_customer ON par_customer.idcustomer = vlt_device.idcustomer
  WHERE vlt_device.active
  ORDER BY vlt_device.vehicle_code, vlt_device.description;
CREATE OR REPLACE VIEW public.mce_driver_vw (
    iddriver,
    code,
    driver,
    last_date,
    parcust_id,
    password,
    idmonitoringcenter,
    idcustomer)
AS
 SELECT vlt_driver.iddriver, vlt_driver.code, vlt_driver.driver, vlt_driver.last_date, vlt_driver.parcust_id, vlt_driver.password, vlt_device_mc.idmonitoringcenter, vlt_device.idcustomer
   FROM vlt_driver
   JOIN vlt_device ON vlt_device.idcustomer = vlt_driver.parcust_id
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice
  GROUP BY vlt_driver.iddriver, vlt_device_mc.idmonitoringcenter, vlt_device.idcustomer
  ORDER BY vlt_device_mc.idmonitoringcenter;
CREATE OR REPLACE VIEW public.mce_refuel_station_vw (
    idrefuelstation,
    code,
    name,
    idinterestpoint,
    idcustomer,
    idmonitoringcenter)
AS
 SELECT vlt_refuel_station.idrefuelstation, vlt_refuel_station.code, vlt_refuel_station.name, vlt_refuel_station.idinterestpoint, vlt_refuel_station.idcustomer, vlt_device_mc.idmonitoringcenter
   FROM vlt_refuel_station
   JOIN vlt_device ON vlt_device.idcustomer = vlt_refuel_station.idcustomer
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice
  GROUP BY vlt_refuel_station.idrefuelstation, vlt_device_mc.idmonitoringcenter
  ORDER BY vlt_device_mc.idmonitoringcenter;
CREATE OR REPLACE VIEW public.mce_refuel_vw (
    idrefuel,
    idrefuelstation,
    refuel_code,
    refuel_date,
    refuel_liters,
    refuel_value,
    idtransaction,
    iddevice,
    name,
    vehicle_code,
    per_liter,
    idmonitoringcenter,
    idcustomer)
AS
 SELECT vlt_refuel.idrefuel, vlt_refuel.idrefuelstation, vlt_refuel.refuel_code, vlt_refuel.refuel_date, vlt_refuel.refuel_liters, vlt_refuel.refuel_value, vlt_refuel.idtransaction, vlt_refuel.iddevice, vlt_refuel_station.name, vlt_device.vehicle_code, vlt_refuel.refuel_value / vlt_refuel.refuel_liters AS per_liter, vlt_device_mc.idmonitoringcenter, vlt_device.idcustomer
   FROM vlt_refuel
   JOIN vlt_refuel_station ON vlt_refuel_station.idrefuelstation = vlt_refuel.idrefuelstation
   JOIN vlt_device ON vlt_device.iddevice = vlt_refuel.iddevice
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice
  ORDER BY vlt_device.vehicle_code;
CREATE OR REPLACE VIEW public.par_etiqueta_vw (
    code,
    password,
    device_model,
    processador,
    model,
    iddevice)
AS
 SELECT vlt_device.code, vlt_gera_senha_device_fnc(vlt_device.code) AS password, vlt_device_model.description AS device_model, NULL::character varying AS processador, NULL::character varying AS model, vlt_device.iddevice
   FROM vlt_device
   JOIN vlt_device_model ON vlt_device_model.iddevicemodel = vlt_device.iddevicemodel;
CREATE OR REPLACE VIEW public.par_message_vw (
    idmessage,
    message_date,
    iduser,
    name,
    title,
    message,
    read_date,
    is_read,
    priority,
    from_iddevice,
    deviceorigin,
    to_iddevice,
    devicereceiver,
    from_iduser,
    messageorigin,
    messagereceiver)
AS
 SELECT par_message.idmessage, par_message.message_date, par_message.iduser, touser.name, par_message.title, par_message.message, par_message.read_date, par_message.is_read, par_message.priority, par_message.from_iddevice, fromdevice.vehicle_code AS deviceorigin, par_message.to_iddevice, todevice.vehicle_code AS devicereceiver, par_message.from_iduser,
        CASE
            WHEN par_message.origin::text = 'S'::text THEN 'Sistema'::character varying
            WHEN par_message.origin::text = 'U'::text THEN fromuser.name
            WHEN par_message.origin::text = 'D'::text THEN fromdevice.vehicle_code
            ELSE NULL::character varying
        END AS messageorigin,
        CASE
            WHEN par_message.iduser IS NOT NULL THEN touser.name
            WHEN par_message.to_iddevice IS NOT NULL THEN todevice.vehicle_code
            ELSE NULL::character varying
        END AS messagereceiver
   FROM par_message
   LEFT JOIN par_user fromuser ON fromuser.iduser = par_message.from_iduser
   LEFT JOIN par_user touser ON touser.iduser = par_message.iduser
   LEFT JOIN vlt_device fromdevice ON fromdevice.iddevice = par_message.from_iddevice
   LEFT JOIN vlt_device todevice ON todevice.iddevice = par_message.to_iddevice;
CREATE OR REPLACE VIEW public.rep_online_offline_vw (
    total,
    online,
    offline,
    vehicle,
    perc_online,
    perc_offline,
    iddevice)
AS
 SELECT o.online + of.offline AS total, o.online, of.offline, (vlt_device.vehicle_code::text || ' | '::text) || vlt_device.description::text AS vehicle, o.online * 100 / (o.online + of.offline) AS perc_online, of.offline * 100 / (o.online + of.offline) AS perc_offline, vlt_device.iddevice
   FROM vlt_device
   JOIN ( SELECT vlt_transaction.iddevice, count(vlt_transaction.online) AS online
           FROM vlt_transaction
          WHERE vlt_transaction.online = 'O'::bpchar AND vlt_transaction.online <> 'N'::bpchar
          GROUP BY vlt_transaction.iddevice, vlt_transaction.online) o ON o.iddevice = vlt_device.iddevice
   JOIN ( SELECT vlt_transaction.iddevice, count(vlt_transaction.online) AS offline
      FROM vlt_transaction
     WHERE vlt_transaction.online = 'M'::bpchar AND vlt_transaction.online <> 'N'::bpchar
     GROUP BY vlt_transaction.iddevice, vlt_transaction.online) of ON of.iddevice = vlt_device.iddevice;
CREATE OR REPLACE VIEW public.rep_route_vw (
    command_date,
    address,
    vehicle,
    date,
    latitude,
    longitude,
    iddevice)
AS
 SELECT min(vlt_transaction.command_date) AS command_date, vlt_transaction.address, (vlt_device.vehicle_code::text || ' - '::text) || vlt_device.description::text AS vehicle, vlt_transaction.command_date AS date, vlt_transaction.latitude, vlt_transaction.longitude, vlt_device.iddevice
   FROM vlt_transaction
   JOIN vlt_device ON vlt_device.iddevice = vlt_transaction.iddevice
  GROUP BY vlt_transaction.address, vlt_device.vehicle_code, vlt_device.description, vlt_transaction.latitude, vlt_transaction.longitude, vlt_transaction.command_date, vlt_device.iddevice
  ORDER BY min(vlt_transaction.command_date);
CREATE OR REPLACE VIEW public.rpt_route_vw (
    idtransaction,
    command_date,
    address,
    vehicle,
    date,
    latitude,
    longitude,
    iddevice)
AS
 SELECT tt.idtransaction_group AS idtransaction, tt.command_date, tt.address, dev.vehicle_code AS vehicle, tt.command_date AS date, tt.latitude, tt.longitude, dev.iddevice
   FROM vlt_transaction tt
   JOIN vlt_device dev ON dev.iddevice = tt.iddevice
  WHERE tt.valid AND tt.idtransaction_group IS NOT NULL AND tt.idtransaction_group = tt.idtransaction
  ORDER BY tt.command_date;
CREATE OR REPLACE VIEW public.rpt_speed_vw (
    idtransaction,
    vehicle,
    date,
    speed,
    address,
    iddevice,
    speed_limit,
    idcustomer,
    latitude,
    longitude,
    command_date)
AS
 SELECT vlt_transaction.idtransaction, vlt_device.vehicle_code::text AS vehicle, to_char(vlt_transaction.command_date, 'dd/mm/yyyy hh24:mi'::text) AS date, vlt_transaction.speed, vlt_transaction.address, vlt_device.iddevice, vlt_device.speed_limit, vlt_device.idcustomer, vlt_transaction.latitude, vlt_transaction.longitude, vlt_transaction.command_date
   FROM vlt_transaction
   JOIN vlt_device ON vlt_device.iddevice = vlt_transaction.iddevice
  WHERE vlt_transaction.valid
  ORDER BY vlt_transaction.command_date;
CREATE OR REPLACE VIEW public.rpt_worked_hours_device_vw (
    vehicle,
    driver,
    data_trabalhada,
    inicio,
    termino,
    horas,
    iddevice)
AS
 SELECT vlt_device.vehicle_code AS vehicle, ( SELECT vlt_driver.driver
           FROM vlt_driver
          WHERE vlt_driver.iddriver = vlt_transaction_driver_fnc(vlt_driver_record.idtransaction_start)) AS driver, to_date(to_char(tend.command_date, 'dd/mm/yyyy'::text), 'dd/mm/yyyy'::text) AS data_trabalhada, tstart.command_date AS inicio, tend.command_date AS termino, tend.command_date - tstart.command_date AS horas, vlt_driver_record.iddevice
   FROM vlt_driver_record
   JOIN vlt_transaction tstart ON tstart.idtransaction = vlt_driver_record.idtransaction_start
   JOIN vlt_transaction tend ON tend.idtransaction = vlt_driver_record.idtransaction_end
   JOIN vlt_device ON vlt_device.iddevice = vlt_driver_record.iddevice
  GROUP BY vlt_driver_record.iddevice, tstart.command_date, tend.command_date, vlt_device.vehicle_code, vlt_driver_record.idtransaction_start
  ORDER BY tstart.command_date, tend.command_date;
CREATE OR REPLACE VIEW public.rpt_worked_hours_driver_vw (
    vehicle,
    driver,
    data_trabalhada,
    inicio,
    termino,
    horas,
    iddriver)
AS
 SELECT ( SELECT vlt_device.vehicle_code
           FROM vlt_device
          WHERE vlt_device.iddevice = vlt_driver_record.iddevice) AS vehicle, ( SELECT vlt_driver.driver
           FROM vlt_driver
          WHERE vlt_driver.iddriver = vlt_transaction_driver_fnc(vlt_driver_record.idtransaction_start)) AS driver, to_date(to_char(tend.command_date, 'dd/mm/yyyy'::text), 'dd/mm/yyyy'::text) AS data_trabalhada, tstart.command_date AS inicio, tend.command_date AS termino, tend.command_date - tstart.command_date AS horas, vlt_transaction_driver_fnc(vlt_driver_record.idtransaction_start) AS iddriver
   FROM vlt_driver_record
   JOIN vlt_transaction tstart ON tstart.idtransaction = vlt_driver_record.idtransaction_start
   JOIN vlt_transaction tend ON tend.idtransaction = vlt_driver_record.idtransaction_end
  GROUP BY vlt_driver_record.iddevice, tstart.command_date, tend.command_date, vlt_driver_record.idtransaction_start
  ORDER BY tstart.command_date, tend.command_date;
CREATE OR REPLACE VIEW public.sel_customer_list_vw (
    idcustomer,
    customer,
    desc_phone,
    desc_cellphone,
    customer_search,
    idseller,
    quantidade)
AS
 SELECT par_customer.idcustomer, par_customer.customer, par_customer.desc_phone, par_customer.desc_cellphone, par_customer.customer_search, par_customer.idseller, ( SELECT count(*) AS count
           FROM vlt_device
          WHERE vlt_device.idcustomer = par_customer.idcustomer) AS quantidade
   FROM par_customer;
CREATE OR REPLACE VIEW public.vlt_command_status_vw (
    idmodelcommand,
    description,
    icon,
    command_text,
    iddevicemodel,
    btnclass,
    iddevice,
    has_parameter,
    parameter,
    access_level,
    message,
    command_date,
    buttoncolor,
    blocked,
    loading,
    sms)
AS
 SELECT vlt_model_command.idmodelcommand, vlt_model_command.description, vlt_model_command.icon, vlt_model_command.command_text, vlt_model_command.iddevicemodel, ( SELECT
                CASE
                    WHEN vlt_command.status::text = 'O'::text THEN 'btn-success'::text
                    WHEN vlt_command.status::text = 'X'::text THEN 'btn-danger'::text
                    WHEN vlt_command.status::text = 'A'::text THEN 'btn-info'::text
                    WHEN vlt_command.status::text = 'T'::text THEN 'btn-danger'::text
                    ELSE NULL::text
                END AS "case"
           FROM vlt_command
          WHERE vlt_command.idcommand = (( SELECT max(vlt_command.idcommand) AS max
                   FROM vlt_command
                  WHERE vlt_command.idmodelcommand = vlt_model_command.idmodelcommand AND vlt_command.iddevice = vlt_device.iddevice))) AS btnclass, vlt_device.iddevice, vlt_model_command.has_parameter, vlt_model_command.parameter, vlt_model_command.access_level, ( SELECT vlt_command.message
           FROM vlt_command
          WHERE vlt_command.idcommand = (( SELECT max(vlt_command.idcommand) AS max
                   FROM vlt_command
                  WHERE vlt_command.iddevice = vlt_device.iddevice AND vlt_command.idmodelcommand = vlt_model_command.idmodelcommand))) AS message, ( SELECT vlt_command.command_date
           FROM vlt_command
          WHERE vlt_command.idcommand = (( SELECT max(vlt_command.idcommand) AS max
                   FROM vlt_command
                  WHERE vlt_command.iddevice = vlt_device.iddevice AND vlt_command.idmodelcommand = vlt_model_command.idmodelcommand))) AS command_date, COALESCE(( SELECT
                CASE
                    WHEN vlt_command.status::text = 'O'::text THEN '#86b558'::text
                    WHEN vlt_command.status::text = 'X'::text THEN '#d3413b'::text
                    WHEN vlt_command.status::text = 'A'::text THEN '#68adde'::text
                    WHEN vlt_command.status::text = 'T'::text THEN '#d3413b'::text
                    ELSE '#68adde'::text
                END AS "case"
           FROM vlt_command
          WHERE vlt_command.idcommand = (( SELECT max(vlt_command.idcommand) AS max
                   FROM vlt_command
                  WHERE vlt_command.idmodelcommand = vlt_model_command.idmodelcommand AND vlt_command.iddevice = vlt_device.iddevice))), '#68adde'::text) AS buttoncolor,
        CASE
            WHEN (( SELECT count(*) AS count
               FROM vlt_command
              WHERE vlt_command.iddevice = vlt_device.iddevice AND (vlt_command.status::text = ANY (ARRAY['A'::text, 'E'::text])))) > 0 THEN true
            ELSE false
        END AS blocked, COALESCE(( SELECT
                CASE
                    WHEN vlt_command.status::text = 'E'::text THEN true
                    WHEN vlt_command.status::text = 'A'::text THEN true
                    ELSE false
                END AS "case"
           FROM vlt_command
          WHERE vlt_command.idcommand = (( SELECT max(vlt_command.idcommand) AS max
                   FROM vlt_command
                  WHERE vlt_command.idmodelcommand = vlt_model_command.idmodelcommand AND vlt_command.iddevice = vlt_device.iddevice))), false) AS loading, vlt_model_command.sms
   FROM vlt_model_command
   JOIN vlt_device ON vlt_device.iddevicemodel = vlt_model_command.iddevicemodel
   LEFT JOIN vlt_model_command_param ON vlt_model_command_param.idmodelcommand = vlt_model_command.idmodelcommand
  GROUP BY vlt_model_command.idmodelcommand, vlt_device.iddevice
  ORDER BY vlt_model_command.idmodelcommand;
CREATE OR REPLACE VIEW public.vlt_device_finger_vw (
    iddevicefinger,
    iddevice,
    vehicle_code,
    iddriver,
    driver,
    idcustomer)
AS
 SELECT def.iddevicefinger, dev.iddevice, (COALESCE(dev.vehicle_code, ''::character varying)::text || ' '::text) || COALESCE(dev.description, ''::character varying)::text AS vehicle_code, drv.iddriver, drv.driver, dev.idcustomer
   FROM vlt_device_finger def, vlt_device dev, vlt_driver_finger drf, vlt_driver drv
  WHERE def.iddevice = dev.iddevice AND drf.iddriverfinger = def.iddriverfinger AND drf.iddriver = drv.iddriver
  ORDER BY dev.vehicle_code, drv.code;
CREATE OR REPLACE VIEW public.vlt_device_search_vw (
    iddevice,
    vehicle_code,
    device_search,
    description,
    tag,
    idcustomer)
AS
 SELECT vlt_device.iddevice, vlt_device.vehicle_code, vlt_device.device_search, vlt_device.description, vlt_device.tag, vlt_device.idcustomer
   FROM vlt_device;
CREATE OR REPLACE VIEW public.vlt_device_vw (
    iddevice,
    description,
    latitude,
    longitude,
    vehicle_code,
    idcustomer,
    code,
    device_search,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    connected,
    is_connected,
    speed,
    command_date,
    start_date,
    driver,
    address,
    idtransaction,
    online,
    record_type,
    has_alert,
    active,
    odometer)
AS
 SELECT q.iddevice, q.description, q.latitude, q.longitude, q.vehicle_code, q.idcustomer, q.code, q.device_search, q.has_battery, q.has_analog_ports, q.has_digital_ports, q.has_tpms, q.has_fuel, q.has_temperature, q.connected, q.is_connected, q.speed, q.command_date, q.start_date, q.driver, q.address, q.idtransaction, q.online, q.record_type, q.has_alert, q.active, q.odometer
   FROM ( SELECT dev.iddevice, dev.description, tt.latitude, tt.longitude, dev.vehicle_code, dev.idcustomer, dev.code, dev.device_search, dm.has_battery, dm.has_analog_ports, dm.has_digital_ports, dm.has_tpms, dm.has_fuel, dm.has_temperature,
                CASE
                    WHEN tt.connected THEN 'Sim'::text
                    ELSE 'Não'::text
                END AS connected, tt.connected AS is_connected, tt.speed, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS command_date, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS start_date, ( SELECT drv.driver
                   FROM vlt_driver drv
                  WHERE drv.iddriver = tt.iddriver) AS driver, tt.address, tt.idtransaction, tt.online, rt.description AS record_type, vlt_device_has_alert_fnc(dev.iddevice) AS has_alert, dev.active, round(tt.odometer::numeric / 1000::numeric, 1) AS odometer
           FROM ( SELECT vlt_device.iddevice, vlt_last_invalid_tran_dev_fnc(vlt_device.iddevice) AS idtransaction, vlt_device.idcustomer
                   FROM vlt_device) tbl
      JOIN vlt_transaction tt ON tt.idtransaction = tbl.idtransaction
   JOIN vlt_device dev ON dev.iddevice = tbl.iddevice
   JOIN vlt_device_model dm ON dm.iddevicemodel = dev.iddevicemodel
   JOIN vlt_record_type rt ON rt.idrecordtype = tt.idrecordtype) q;
CREATE OR REPLACE VIEW public.vlt_device_vw2 (
    idcustomer,
    iddevice,
    code,
    device_search,
    description,
    vehicle_code,
    address,
    speed,
    connected,
    command_date,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature)
AS
 SELECT vlt_device.idcustomer, vlt_device.iddevice, vlt_device.code, vlt_device.device_search, vlt_device.description, vlt_device.vehicle_code, vlt_transaction.address, vlt_transaction.speed, vlt_transaction.connected, vlt_transaction.command_date, vlt_device_model.has_battery, vlt_device_model.has_analog_ports, vlt_device_model.has_digital_ports, vlt_device_model.has_tpms, vlt_device_model.has_fuel, vlt_device_model.has_temperature
   FROM vlt_device
   JOIN vlt_transaction ON vlt_transaction.idtransaction = vlt_device.last_transaction
   JOIN vlt_device_model ON vlt_device_model.iddevicemodel = vlt_device.iddevicemodel;
CREATE OR REPLACE VIEW public.vlt_device_vw_ant (
    iddevice,
    description,
    latitude,
    longitude,
    vehicle_code,
    idcustomer,
    code,
    device_search,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    connected,
    is_connected,
    speed,
    command_date,
    start_date,
    driver,
    address,
    idtransaction,
    online,
    record_type,
    has_alert,
    active,
    odometer)
AS
 SELECT dev.iddevice, dev.description, dev.last_latitude AS latitude, dev.last_longitude AS longitude, dev.vehicle_code, dev.idcustomer, dev.code, dev.device_search, dm.has_battery, dm.has_analog_ports, dm.has_digital_ports, dm.has_tpms, dm.has_fuel, dm.has_temperature,
        CASE
            WHEN tt.connected THEN 'Sim'::text
            ELSE 'Não'::text
        END AS connected, tt.connected AS is_connected, tt.speed, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS command_date, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS start_date, drv.driver, tt.address, tt.idtransaction, tt.online, rt.description AS record_type, vlt_device_has_alert_fnc(dev.iddevice) AS has_alert, dev.active, round(tt.odometer::numeric / 1000::numeric, 1) AS odometer
   FROM vlt_device dev
   JOIN vlt_transaction tt ON tt.idtransaction = (( SELECT vlt_transaction.idtransaction
      FROM vlt_transaction
     WHERE vlt_transaction.iddevice = dev.iddevice AND vlt_transaction.valid
     ORDER BY vlt_transaction.idtransaction DESC
    LIMIT 1))
   JOIN vlt_device_model dm ON dm.iddevicemodel = dev.iddevicemodel
   JOIN vlt_record_type rt ON rt.idrecordtype = tt.idrecordtype
   LEFT JOIN vlt_driver drv ON drv.iddriver = tt.iddriver
  WHERE dev.active;
CREATE OR REPLACE VIEW public.vlt_device_vw_old (
    iddevice,
    description,
    latitude,
    longitude,
    vehicle_code,
    idcustomer,
    code,
    device_search,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    connected,
    is_connected,
    speed,
    command_date,
    start_date,
    driver,
    address,
    idtransaction,
    online,
    record_type,
    has_alert,
    active,
    odometer)
AS
 SELECT dev.iddevice, dev.description, dev.last_latitude AS latitude, dev.last_longitude AS longitude, dev.vehicle_code, dev.idcustomer, dev.code, dev.device_search, dm.has_battery, dm.has_analog_ports, dm.has_digital_ports, dm.has_tpms, dm.has_fuel, dm.has_temperature,
        CASE
            WHEN tt.connected THEN 'Sim'::text
            ELSE 'Não'::text
        END AS connected, tt.connected AS is_connected, tt.speed, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS command_date, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS start_date, drv.driver, tt.address, tt.idtransaction, tt.online, rt.description AS record_type, vlt_device_has_alert_fnc(dev.iddevice) AS has_alert, dev.active, round(tt.odometer::numeric / 1000::numeric, 1) AS odometer
   FROM vlt_device dev
   JOIN vlt_transaction tt ON tt.idtransaction = (( SELECT vlt_transaction.idtransaction
      FROM vlt_transaction
     WHERE vlt_transaction.iddevice = dev.iddevice AND vlt_transaction.valid
     ORDER BY vlt_transaction.idtransaction DESC
    LIMIT 1))
   JOIN vlt_device_model dm ON dm.iddevicemodel = dev.iddevicemodel
   JOIN vlt_record_type rt ON rt.idrecordtype = tt.idrecordtype
   LEFT JOIN vlt_driver drv ON drv.iddriver = tt.iddriver
  WHERE dev.active;
CREATE OR REPLACE VIEW public.vlt_grouped_transactions_vw (
    address,
    connected,
    driver,
    description,
    hour,
    iddevice,
    idtransaction,
    idtransaction_group,
    is_connected,
    latitude,
    longitude,
    online,
    speed,
    start_date,
    vehicle_code,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature)
AS
 SELECT vlt_transaction.address,
        CASE
            WHEN vlt_transaction.connected THEN 'Sim'::text
            ELSE 'Não'::text
        END AS connected, vlt_driver.driver, vlt_device.description, vlt_transaction.time_dif AS hour, vlt_transaction.iddevice, vlt_transaction.idtransaction, vlt_transaction.idtransaction_group, vlt_transaction.connected AS is_connected, vlt_transaction.latitude, vlt_transaction.longitude, vlt_transaction.online, vlt_transaction.speed, to_char(vlt_transaction.command_date, 'dd/mm/yyyy hh24:mi:ss'::text) AS start_date, vlt_device.vehicle_code, vlt_device_model.has_battery, vlt_device_model.has_analog_ports, vlt_device_model.has_digital_ports, vlt_device_model.has_tpms, vlt_device_model.has_fuel, vlt_device_model.has_temperature
   FROM vlt_transaction
   JOIN vlt_device ON vlt_device.iddevice = vlt_transaction.iddevice
   LEFT JOIN vlt_driver ON vlt_driver.iddriver = vlt_transaction.iddriver
   JOIN vlt_device_model ON vlt_device_model.iddevicemodel = vlt_device.iddevicemodel
  WHERE vlt_transaction.valid
  ORDER BY vlt_transaction.command_date;
CREATE OR REPLACE VIEW public.vlt_port_status_vw (
    description,
    value,
    type,
    idtransaction_filter,
    output)
AS
 SELECT vlt_port.description, vlt_port_status.value,
        CASE
            WHEN vlt_record_type.code::text = '6'::text THEN 'D'::text
            ELSE 'A'::text
        END AS type, vlt_transaction_last.idtransaction AS idtransaction_filter, vlt_port.output
   FROM vlt_transaction_last
   JOIN vlt_record_type ON vlt_record_type.idrecordtype = vlt_transaction_last.idrecordtype AND (vlt_record_type.code::text = ANY (ARRAY['6'::text, '7'::text]))
   JOIN vlt_transaction ON vlt_transaction.idtransaction = vlt_transaction_last.idtransaction_last
   JOIN vlt_device ON vlt_device.iddevice = vlt_transaction.iddevice
   JOIN vlt_port ON vlt_port.iddevicemodel = vlt_device.iddevicemodel AND vlt_port.fuel = false
   JOIN vlt_port_status ON vlt_port_status.idport = vlt_port.idport AND vlt_port_status.idtransaction = vlt_transaction_last.idtransaction_last;
CREATE OR REPLACE VIEW public.vlt_refuel_vw (
    idrefuel,
    idrefuelstation,
    refuel_code,
    refuel_date,
    refuel_liters,
    refuel_value,
    idtransaction,
    iddevice,
    name,
    vehicle_code,
    per_liter,
    idcustomer)
AS
 SELECT vlt_refuel.idrefuel, vlt_refuel.idrefuelstation, vlt_refuel.refuel_code, vlt_refuel.refuel_date, vlt_refuel.refuel_liters, vlt_refuel.refuel_value, vlt_refuel.idtransaction, vlt_refuel.iddevice, vlt_refuel_station.name, vlt_device.vehicle_code, vlt_refuel.refuel_value /
        CASE
            WHEN vlt_refuel.refuel_liters = 0::numeric THEN 1::numeric
            ELSE vlt_refuel.refuel_liters
        END AS per_liter, vlt_device.idcustomer
   FROM vlt_refuel
   JOIN vlt_refuel_station ON vlt_refuel_station.idrefuelstation = vlt_refuel.idrefuelstation
   JOIN vlt_device ON vlt_device.iddevice = vlt_refuel.iddevice
  ORDER BY vlt_device.vehicle_code;
CREATE OR REPLACE VIEW public.vlt_seller_dc_vw (
    iddevice,
    idseller,
    vehicle_code,
    device_search,
    vehicle,
    command_date,
    record_type,
    active,
    code)
AS
 SELECT q.iddevice, q.idseller, q.vehicle_code, q.device_search, q.vehicle, tt.command_date, rt.description AS record_type, q.active, q.code
   FROM ( SELECT vlt_device.iddevice, par_customer.idseller, vlt_device.vehicle_code, vlt_device.device_search, vlt_device.description AS vehicle, vlt_device.active, vlt_device.code, vlt_last_tran_dev_fnc(vlt_device.iddevice) AS idtransaction
           FROM vlt_device, par_customer
          WHERE par_customer.idseller IS NOT NULL AND par_customer.idcustomer = vlt_device.idcustomer) q, vlt_transaction tt, vlt_record_type rt
  WHERE tt.idtransaction = q.idtransaction AND rt.idrecordtype = tt.idrecordtype;
CREATE OR REPLACE VIEW public.vlt_tpms_disparity_vw (
    vehicle_code,
    description,
    tpms_sequence,
    temperature,
    valr_min_temperature,
    valr_max_temperature,
    pressure,
    valr_min_preassure,
    valr_max_preassure,
    idcustomer,
    transaction_date)
AS
 SELECT vlt_device.vehicle_code, vlt_device.description, vlt_tpms.tpms_sequence, vlt_tpms_value.temperature, vlt_tpms_model.valr_min_temperature, vlt_tpms_model.valr_max_temperature, vlt_tpms_value.pressure, vlt_tpms_model.valr_min_preassure, vlt_tpms_model.valr_max_preassure, vlt_device.idcustomer, to_char(vlt_transaction.transaction_date, 'dd/mm/yyyy hh24:mi'::text) AS transaction_date
   FROM vlt_tpms
   JOIN vlt_device ON vlt_device.iddevice = vlt_tpms.iddevice
   JOIN vlt_tpms_value ON vlt_tpms_value.idtpmsvalue = vlt_tpms.idtpmsvalue_last
   JOIN vlt_tpms_model ON vlt_tpms_model.id = vlt_device.idtpmsmodel AND (vlt_tpms_model.valr_min_temperature > vlt_tpms_value.temperature OR vlt_tpms_model.valr_max_temperature < vlt_tpms_value.temperature OR vlt_tpms_model.valr_min_preassure > vlt_tpms_value.pressure OR vlt_tpms_model.valr_max_preassure < vlt_tpms_value.pressure)
   JOIN vlt_transaction ON vlt_transaction.idtransaction = vlt_tpms_value.idtransaction
  ORDER BY vlt_device.vehicle_code, vlt_tpms.tpms_sequence;
CREATE OR REPLACE VIEW public.vlt_transaction_driver_vw (
    idtransactiondriver,
    iddriver,
    transaction_date,
    iddevice,
    idcustomer,
    driver,
    device)
AS
 SELECT td.idtransactiondriver, td.iddriver, td.transaction_date, td.iddevice, dev.idcustomer, d.driver, dev.vehicle_code AS device
   FROM vlt_device dev
   JOIN vlt_transaction_driver td ON td.iddevice = dev.iddevice
   JOIN vlt_driver d ON d.iddriver = td.iddriver
  WHERE dev.active
  ORDER BY td.transaction_date;

DROP VIEW public.vlt_transaction_driver_vw;
DROP VIEW public.vlt_tpms_disparity_vw;
DROP VIEW public.vlt_seller_dc_vw;
DROP VIEW public.vlt_refuel_vw;
DROP VIEW public.vlt_port_status_vw;
DROP VIEW public.vlt_grouped_transactions_vw;
DROP VIEW public.vlt_device_vw_old;
DROP VIEW public.vlt_device_vw_ant;
DROP VIEW public.vlt_device_vw2;
DROP VIEW public.vlt_device_vw;
DROP VIEW public.vlt_device_search_vw;
DROP VIEW public.vlt_device_finger_vw;
DROP VIEW public.vlt_command_status_vw;
DROP VIEW public.sel_customer_list_vw;
DROP VIEW public.rpt_worked_hours_driver_vw;
DROP VIEW public.rpt_worked_hours_device_vw;
DROP VIEW public.rpt_speed_vw;
DROP VIEW public.rpt_route_vw;
DROP VIEW public.rep_route_vw;
DROP VIEW public.rep_online_offline_vw;
DROP VIEW public.par_message_vw;
DROP VIEW public.par_etiqueta_vw;
DROP VIEW public.mce_refuel_vw;
DROP VIEW public.mce_refuel_station_vw;
DROP VIEW public.mce_driver_vw;
DROP VIEW public.mce_device_vw;
DROP VIEW public.mce_customer_vw;
ALTER TABLE public.vlt_device
  ALTER COLUMN limit_group_off SET DEFAULT 0;
CREATE OR REPLACE VIEW public.mce_customer_vw (
    iddevice,
    idmonitoringcenter,
    idcustomer,
    customer,
    email,
    dthr_cadastro,
    desc_address,
    desc_complement,
    numr_address,
    desc_district,
    desc_city,
    desc_state,
    desc_country,
    desc_zip_address,
    desc_phone,
    desc_cellphone,
    desc_cgc,
    desc_ddd_cellphone,
    desc_ddd_phone,
    idseller,
    customer_search,
    rg,
    active,
    block_message)
AS
 SELECT vlt_device.iddevice, vlt_device_mc.idmonitoringcenter, par_customer.idcustomer, par_customer.customer, par_customer.email, par_customer.dthr_cadastro, par_customer.desc_address, par_customer.desc_complement, par_customer.numr_address, par_customer.desc_district, par_customer.desc_city, par_customer.desc_state, par_customer.desc_country, par_customer.desc_zip_address, par_customer.desc_phone, par_customer.desc_cellphone, par_customer.desc_cgc, par_customer.desc_ddd_cellphone, par_customer.desc_ddd_phone, par_customer.idseller, par_customer.customer_search, par_customer.rg, par_customer.active, par_customer.block_message
   FROM vlt_device
   JOIN par_customer ON par_customer.idcustomer = vlt_device.idcustomer
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice;
CREATE OR REPLACE VIEW public.mce_device_vw (
    iddevice,
    code,
    device_search,
    description,
    vehicle_code,
    address,
    speed,
    connected,
    command_date,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    idmonitoringcenter,
    customer,
    idcustomer,
    driver,
    active,
    speed_limit,
    renavam,
    chassi,
    mark_vehicle,
    model_vehicle,
    color_vehicle,
    fuel_estimate)
AS
 SELECT vlt_device.iddevice, vlt_device.code, vlt_device.device_search, vlt_device.description, vlt_device.vehicle_code, vlt_transaction.address, vlt_transaction.speed, vlt_transaction.connected, vlt_transaction.command_date, vlt_device_model.has_battery, vlt_device_model.has_analog_ports, vlt_device_model.has_digital_ports, vlt_device_model.has_tpms, vlt_device_model.has_fuel, vlt_device_model.has_temperature, vlt_device_mc.idmonitoringcenter, par_customer.customer, par_customer.idcustomer, vlt_driver.driver, vlt_device.active, vlt_device.speed_limit, vlt_device.renavam, vlt_device.chassi, vlt_device.mark_vehicle, vlt_device.model_vehicle, vlt_device.color_vehicle, vlt_device.fuel_estimate
   FROM vlt_device
   LEFT JOIN vlt_driver ON vlt_driver.iddriver = vlt_device.last_driver
   JOIN vlt_transaction ON vlt_transaction.idtransaction = vlt_last_tran_dev_fnc(vlt_device.iddevice)
   JOIN vlt_device_model ON vlt_device_model.iddevicemodel = vlt_device.iddevicemodel
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice
   JOIN par_customer ON par_customer.idcustomer = vlt_device.idcustomer
  WHERE vlt_device.active
  ORDER BY vlt_device.vehicle_code, vlt_device.description;
CREATE OR REPLACE VIEW public.mce_driver_vw (
    iddriver,
    code,
    driver,
    last_date,
    parcust_id,
    password,
    idmonitoringcenter,
    idcustomer)
AS
 SELECT vlt_driver.iddriver, vlt_driver.code, vlt_driver.driver, vlt_driver.last_date, vlt_driver.parcust_id, vlt_driver.password, vlt_device_mc.idmonitoringcenter, vlt_device.idcustomer
   FROM vlt_driver
   JOIN vlt_device ON vlt_device.idcustomer = vlt_driver.parcust_id
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice
  GROUP BY vlt_driver.iddriver, vlt_device_mc.idmonitoringcenter, vlt_device.idcustomer
  ORDER BY vlt_device_mc.idmonitoringcenter;
CREATE OR REPLACE VIEW public.mce_refuel_station_vw (
    idrefuelstation,
    code,
    name,
    idinterestpoint,
    idcustomer,
    idmonitoringcenter)
AS
 SELECT vlt_refuel_station.idrefuelstation, vlt_refuel_station.code, vlt_refuel_station.name, vlt_refuel_station.idinterestpoint, vlt_refuel_station.idcustomer, vlt_device_mc.idmonitoringcenter
   FROM vlt_refuel_station
   JOIN vlt_device ON vlt_device.idcustomer = vlt_refuel_station.idcustomer
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice
  GROUP BY vlt_refuel_station.idrefuelstation, vlt_device_mc.idmonitoringcenter
  ORDER BY vlt_device_mc.idmonitoringcenter;
CREATE OR REPLACE VIEW public.mce_refuel_vw (
    idrefuel,
    idrefuelstation,
    refuel_code,
    refuel_date,
    refuel_liters,
    refuel_value,
    idtransaction,
    iddevice,
    name,
    vehicle_code,
    per_liter,
    idmonitoringcenter,
    idcustomer)
AS
 SELECT vlt_refuel.idrefuel, vlt_refuel.idrefuelstation, vlt_refuel.refuel_code, vlt_refuel.refuel_date, vlt_refuel.refuel_liters, vlt_refuel.refuel_value, vlt_refuel.idtransaction, vlt_refuel.iddevice, vlt_refuel_station.name, vlt_device.vehicle_code, vlt_refuel.refuel_value / vlt_refuel.refuel_liters AS per_liter, vlt_device_mc.idmonitoringcenter, vlt_device.idcustomer
   FROM vlt_refuel
   JOIN vlt_refuel_station ON vlt_refuel_station.idrefuelstation = vlt_refuel.idrefuelstation
   JOIN vlt_device ON vlt_device.iddevice = vlt_refuel.iddevice
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice
  ORDER BY vlt_device.vehicle_code;
CREATE OR REPLACE VIEW public.par_etiqueta_vw (
    code,
    password,
    device_model,
    processador,
    model,
    iddevice)
AS
 SELECT vlt_device.code, vlt_gera_senha_device_fnc(vlt_device.code) AS password, vlt_device_model.description AS device_model, NULL::character varying AS processador, NULL::character varying AS model, vlt_device.iddevice
   FROM vlt_device
   JOIN vlt_device_model ON vlt_device_model.iddevicemodel = vlt_device.iddevicemodel;
CREATE OR REPLACE VIEW public.par_message_vw (
    idmessage,
    message_date,
    iduser,
    name,
    title,
    message,
    read_date,
    is_read,
    priority,
    from_iddevice,
    deviceorigin,
    to_iddevice,
    devicereceiver,
    from_iduser,
    messageorigin,
    messagereceiver)
AS
 SELECT par_message.idmessage, par_message.message_date, par_message.iduser, touser.name, par_message.title, par_message.message, par_message.read_date, par_message.is_read, par_message.priority, par_message.from_iddevice, fromdevice.vehicle_code AS deviceorigin, par_message.to_iddevice, todevice.vehicle_code AS devicereceiver, par_message.from_iduser,
        CASE
            WHEN par_message.origin::text = 'S'::text THEN 'Sistema'::character varying
            WHEN par_message.origin::text = 'U'::text THEN fromuser.name
            WHEN par_message.origin::text = 'D'::text THEN fromdevice.vehicle_code
            ELSE NULL::character varying
        END AS messageorigin,
        CASE
            WHEN par_message.iduser IS NOT NULL THEN touser.name
            WHEN par_message.to_iddevice IS NOT NULL THEN todevice.vehicle_code
            ELSE NULL::character varying
        END AS messagereceiver
   FROM par_message
   LEFT JOIN par_user fromuser ON fromuser.iduser = par_message.from_iduser
   LEFT JOIN par_user touser ON touser.iduser = par_message.iduser
   LEFT JOIN vlt_device fromdevice ON fromdevice.iddevice = par_message.from_iddevice
   LEFT JOIN vlt_device todevice ON todevice.iddevice = par_message.to_iddevice;
CREATE OR REPLACE VIEW public.rep_online_offline_vw (
    total,
    online,
    offline,
    vehicle,
    perc_online,
    perc_offline,
    iddevice)
AS
 SELECT o.online + of.offline AS total, o.online, of.offline, (vlt_device.vehicle_code::text || ' | '::text) || vlt_device.description::text AS vehicle, o.online * 100 / (o.online + of.offline) AS perc_online, of.offline * 100 / (o.online + of.offline) AS perc_offline, vlt_device.iddevice
   FROM vlt_device
   JOIN ( SELECT vlt_transaction.iddevice, count(vlt_transaction.online) AS online
           FROM vlt_transaction
          WHERE vlt_transaction.online = 'O'::bpchar AND vlt_transaction.online <> 'N'::bpchar
          GROUP BY vlt_transaction.iddevice, vlt_transaction.online) o ON o.iddevice = vlt_device.iddevice
   JOIN ( SELECT vlt_transaction.iddevice, count(vlt_transaction.online) AS offline
      FROM vlt_transaction
     WHERE vlt_transaction.online = 'M'::bpchar AND vlt_transaction.online <> 'N'::bpchar
     GROUP BY vlt_transaction.iddevice, vlt_transaction.online) of ON of.iddevice = vlt_device.iddevice;
CREATE OR REPLACE VIEW public.rep_route_vw (
    command_date,
    address,
    vehicle,
    date,
    latitude,
    longitude,
    iddevice)
AS
 SELECT min(vlt_transaction.command_date) AS command_date, vlt_transaction.address, (vlt_device.vehicle_code::text || ' - '::text) || vlt_device.description::text AS vehicle, vlt_transaction.command_date AS date, vlt_transaction.latitude, vlt_transaction.longitude, vlt_device.iddevice
   FROM vlt_transaction
   JOIN vlt_device ON vlt_device.iddevice = vlt_transaction.iddevice
  GROUP BY vlt_transaction.address, vlt_device.vehicle_code, vlt_device.description, vlt_transaction.latitude, vlt_transaction.longitude, vlt_transaction.command_date, vlt_device.iddevice
  ORDER BY min(vlt_transaction.command_date);
CREATE OR REPLACE VIEW public.rpt_route_vw (
    idtransaction,
    command_date,
    address,
    vehicle,
    date,
    latitude,
    longitude,
    iddevice)
AS
 SELECT tt.idtransaction_group AS idtransaction, tt.command_date, tt.address, dev.vehicle_code AS vehicle, tt.command_date AS date, tt.latitude, tt.longitude, dev.iddevice
   FROM vlt_transaction tt
   JOIN vlt_device dev ON dev.iddevice = tt.iddevice
  WHERE tt.valid AND tt.idtransaction_group IS NOT NULL AND tt.idtransaction_group = tt.idtransaction
  ORDER BY tt.command_date;
CREATE OR REPLACE VIEW public.rpt_speed_vw (
    idtransaction,
    vehicle,
    date,
    speed,
    address,
    iddevice,
    speed_limit,
    idcustomer,
    latitude,
    longitude,
    command_date)
AS
 SELECT vlt_transaction.idtransaction, vlt_device.vehicle_code::text AS vehicle, to_char(vlt_transaction.command_date, 'dd/mm/yyyy hh24:mi'::text) AS date, vlt_transaction.speed, vlt_transaction.address, vlt_device.iddevice, vlt_device.speed_limit, vlt_device.idcustomer, vlt_transaction.latitude, vlt_transaction.longitude, vlt_transaction.command_date
   FROM vlt_transaction
   JOIN vlt_device ON vlt_device.iddevice = vlt_transaction.iddevice
  WHERE vlt_transaction.valid
  ORDER BY vlt_transaction.command_date;
CREATE OR REPLACE VIEW public.rpt_worked_hours_device_vw (
    vehicle,
    driver,
    data_trabalhada,
    inicio,
    termino,
    horas,
    iddevice)
AS
 SELECT vlt_device.vehicle_code AS vehicle, ( SELECT vlt_driver.driver
           FROM vlt_driver
          WHERE vlt_driver.iddriver = vlt_transaction_driver_fnc(vlt_driver_record.idtransaction_start)) AS driver, to_date(to_char(tend.command_date, 'dd/mm/yyyy'::text), 'dd/mm/yyyy'::text) AS data_trabalhada, tstart.command_date AS inicio, tend.command_date AS termino, tend.command_date - tstart.command_date AS horas, vlt_driver_record.iddevice
   FROM vlt_driver_record
   JOIN vlt_transaction tstart ON tstart.idtransaction = vlt_driver_record.idtransaction_start
   JOIN vlt_transaction tend ON tend.idtransaction = vlt_driver_record.idtransaction_end
   JOIN vlt_device ON vlt_device.iddevice = vlt_driver_record.iddevice
  GROUP BY vlt_driver_record.iddevice, tstart.command_date, tend.command_date, vlt_device.vehicle_code, vlt_driver_record.idtransaction_start
  ORDER BY tstart.command_date, tend.command_date;
CREATE OR REPLACE VIEW public.rpt_worked_hours_driver_vw (
    vehicle,
    driver,
    data_trabalhada,
    inicio,
    termino,
    horas,
    iddriver)
AS
 SELECT ( SELECT vlt_device.vehicle_code
           FROM vlt_device
          WHERE vlt_device.iddevice = vlt_driver_record.iddevice) AS vehicle, ( SELECT vlt_driver.driver
           FROM vlt_driver
          WHERE vlt_driver.iddriver = vlt_transaction_driver_fnc(vlt_driver_record.idtransaction_start)) AS driver, to_date(to_char(tend.command_date, 'dd/mm/yyyy'::text), 'dd/mm/yyyy'::text) AS data_trabalhada, tstart.command_date AS inicio, tend.command_date AS termino, tend.command_date - tstart.command_date AS horas, vlt_transaction_driver_fnc(vlt_driver_record.idtransaction_start) AS iddriver
   FROM vlt_driver_record
   JOIN vlt_transaction tstart ON tstart.idtransaction = vlt_driver_record.idtransaction_start
   JOIN vlt_transaction tend ON tend.idtransaction = vlt_driver_record.idtransaction_end
  GROUP BY vlt_driver_record.iddevice, tstart.command_date, tend.command_date, vlt_driver_record.idtransaction_start
  ORDER BY tstart.command_date, tend.command_date;
CREATE OR REPLACE VIEW public.sel_customer_list_vw (
    idcustomer,
    customer,
    desc_phone,
    desc_cellphone,
    customer_search,
    idseller,
    quantidade)
AS
 SELECT par_customer.idcustomer, par_customer.customer, par_customer.desc_phone, par_customer.desc_cellphone, par_customer.customer_search, par_customer.idseller, ( SELECT count(*) AS count
           FROM vlt_device
          WHERE vlt_device.idcustomer = par_customer.idcustomer) AS quantidade
   FROM par_customer;
CREATE OR REPLACE VIEW public.vlt_command_status_vw (
    idmodelcommand,
    description,
    icon,
    command_text,
    iddevicemodel,
    btnclass,
    iddevice,
    has_parameter,
    parameter,
    access_level,
    message,
    command_date,
    buttoncolor,
    blocked,
    loading,
    sms)
AS
 SELECT vlt_model_command.idmodelcommand, vlt_model_command.description, vlt_model_command.icon, vlt_model_command.command_text, vlt_model_command.iddevicemodel, ( SELECT
                CASE
                    WHEN vlt_command.status::text = 'O'::text THEN 'btn-success'::text
                    WHEN vlt_command.status::text = 'X'::text THEN 'btn-danger'::text
                    WHEN vlt_command.status::text = 'A'::text THEN 'btn-info'::text
                    WHEN vlt_command.status::text = 'T'::text THEN 'btn-danger'::text
                    ELSE NULL::text
                END AS "case"
           FROM vlt_command
          WHERE vlt_command.idcommand = (( SELECT max(vlt_command.idcommand) AS max
                   FROM vlt_command
                  WHERE vlt_command.idmodelcommand = vlt_model_command.idmodelcommand AND vlt_command.iddevice = vlt_device.iddevice))) AS btnclass, vlt_device.iddevice, vlt_model_command.has_parameter, vlt_model_command.parameter, vlt_model_command.access_level, ( SELECT vlt_command.message
           FROM vlt_command
          WHERE vlt_command.idcommand = (( SELECT max(vlt_command.idcommand) AS max
                   FROM vlt_command
                  WHERE vlt_command.iddevice = vlt_device.iddevice AND vlt_command.idmodelcommand = vlt_model_command.idmodelcommand))) AS message, ( SELECT vlt_command.command_date
           FROM vlt_command
          WHERE vlt_command.idcommand = (( SELECT max(vlt_command.idcommand) AS max
                   FROM vlt_command
                  WHERE vlt_command.iddevice = vlt_device.iddevice AND vlt_command.idmodelcommand = vlt_model_command.idmodelcommand))) AS command_date, COALESCE(( SELECT
                CASE
                    WHEN vlt_command.status::text = 'O'::text THEN '#86b558'::text
                    WHEN vlt_command.status::text = 'X'::text THEN '#d3413b'::text
                    WHEN vlt_command.status::text = 'A'::text THEN '#68adde'::text
                    WHEN vlt_command.status::text = 'T'::text THEN '#d3413b'::text
                    ELSE '#68adde'::text
                END AS "case"
           FROM vlt_command
          WHERE vlt_command.idcommand = (( SELECT max(vlt_command.idcommand) AS max
                   FROM vlt_command
                  WHERE vlt_command.idmodelcommand = vlt_model_command.idmodelcommand AND vlt_command.iddevice = vlt_device.iddevice))), '#68adde'::text) AS buttoncolor,
        CASE
            WHEN (( SELECT count(*) AS count
               FROM vlt_command
              WHERE vlt_command.iddevice = vlt_device.iddevice AND (vlt_command.status::text = ANY (ARRAY['A'::text, 'E'::text])))) > 0 THEN true
            ELSE false
        END AS blocked, COALESCE(( SELECT
                CASE
                    WHEN vlt_command.status::text = 'E'::text THEN true
                    WHEN vlt_command.status::text = 'A'::text THEN true
                    ELSE false
                END AS "case"
           FROM vlt_command
          WHERE vlt_command.idcommand = (( SELECT max(vlt_command.idcommand) AS max
                   FROM vlt_command
                  WHERE vlt_command.idmodelcommand = vlt_model_command.idmodelcommand AND vlt_command.iddevice = vlt_device.iddevice))), false) AS loading, vlt_model_command.sms
   FROM vlt_model_command
   JOIN vlt_device ON vlt_device.iddevicemodel = vlt_model_command.iddevicemodel
   LEFT JOIN vlt_model_command_param ON vlt_model_command_param.idmodelcommand = vlt_model_command.idmodelcommand
  GROUP BY vlt_model_command.idmodelcommand, vlt_device.iddevice
  ORDER BY vlt_model_command.idmodelcommand;
CREATE OR REPLACE VIEW public.vlt_device_finger_vw (
    iddevicefinger,
    iddevice,
    vehicle_code,
    iddriver,
    driver,
    idcustomer)
AS
 SELECT def.iddevicefinger, dev.iddevice, (COALESCE(dev.vehicle_code, ''::character varying)::text || ' '::text) || COALESCE(dev.description, ''::character varying)::text AS vehicle_code, drv.iddriver, drv.driver, dev.idcustomer
   FROM vlt_device_finger def, vlt_device dev, vlt_driver_finger drf, vlt_driver drv
  WHERE def.iddevice = dev.iddevice AND drf.iddriverfinger = def.iddriverfinger AND drf.iddriver = drv.iddriver
  ORDER BY dev.vehicle_code, drv.code;
CREATE OR REPLACE VIEW public.vlt_device_search_vw (
    iddevice,
    vehicle_code,
    device_search,
    description,
    tag,
    idcustomer)
AS
 SELECT vlt_device.iddevice, vlt_device.vehicle_code, vlt_device.device_search, vlt_device.description, vlt_device.tag, vlt_device.idcustomer
   FROM vlt_device;
CREATE OR REPLACE VIEW public.vlt_device_vw (
    iddevice,
    description,
    latitude,
    longitude,
    vehicle_code,
    idcustomer,
    code,
    device_search,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    connected,
    is_connected,
    speed,
    command_date,
    start_date,
    driver,
    address,
    idtransaction,
    online,
    record_type,
    has_alert,
    active,
    odometer)
AS
 SELECT q.iddevice, q.description, q.latitude, q.longitude, q.vehicle_code, q.idcustomer, q.code, q.device_search, q.has_battery, q.has_analog_ports, q.has_digital_ports, q.has_tpms, q.has_fuel, q.has_temperature, q.connected, q.is_connected, q.speed, q.command_date, q.start_date, q.driver, q.address, q.idtransaction, q.online, q.record_type, q.has_alert, q.active, q.odometer
   FROM ( SELECT dev.iddevice, dev.description, tt.latitude, tt.longitude, dev.vehicle_code, dev.idcustomer, dev.code, dev.device_search, dm.has_battery, dm.has_analog_ports, dm.has_digital_ports, dm.has_tpms, dm.has_fuel, dm.has_temperature,
                CASE
                    WHEN tt.connected THEN 'Sim'::text
                    ELSE 'Não'::text
                END AS connected, tt.connected AS is_connected, tt.speed, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS command_date, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS start_date, ( SELECT drv.driver
                   FROM vlt_driver drv
                  WHERE drv.iddriver = tt.iddriver) AS driver, tt.address, tt.idtransaction, tt.online, rt.description AS record_type, vlt_device_has_alert_fnc(dev.iddevice) AS has_alert, dev.active, round(tt.odometer::numeric / 1000::numeric, 1) AS odometer
           FROM ( SELECT vlt_device.iddevice, vlt_last_invalid_tran_dev_fnc(vlt_device.iddevice) AS idtransaction, vlt_device.idcustomer
                   FROM vlt_device) tbl
      JOIN vlt_transaction tt ON tt.idtransaction = tbl.idtransaction
   JOIN vlt_device dev ON dev.iddevice = tbl.iddevice
   JOIN vlt_device_model dm ON dm.iddevicemodel = dev.iddevicemodel
   JOIN vlt_record_type rt ON rt.idrecordtype = tt.idrecordtype) q;
CREATE OR REPLACE VIEW public.vlt_device_vw2 (
    idcustomer,
    iddevice,
    code,
    device_search,
    description,
    vehicle_code,
    address,
    speed,
    connected,
    command_date,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature)
AS
 SELECT vlt_device.idcustomer, vlt_device.iddevice, vlt_device.code, vlt_device.device_search, vlt_device.description, vlt_device.vehicle_code, vlt_transaction.address, vlt_transaction.speed, vlt_transaction.connected, vlt_transaction.command_date, vlt_device_model.has_battery, vlt_device_model.has_analog_ports, vlt_device_model.has_digital_ports, vlt_device_model.has_tpms, vlt_device_model.has_fuel, vlt_device_model.has_temperature
   FROM vlt_device
   JOIN vlt_transaction ON vlt_transaction.idtransaction = vlt_device.last_transaction
   JOIN vlt_device_model ON vlt_device_model.iddevicemodel = vlt_device.iddevicemodel;
CREATE OR REPLACE VIEW public.vlt_device_vw_ant (
    iddevice,
    description,
    latitude,
    longitude,
    vehicle_code,
    idcustomer,
    code,
    device_search,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    connected,
    is_connected,
    speed,
    command_date,
    start_date,
    driver,
    address,
    idtransaction,
    online,
    record_type,
    has_alert,
    active,
    odometer)
AS
 SELECT dev.iddevice, dev.description, dev.last_latitude AS latitude, dev.last_longitude AS longitude, dev.vehicle_code, dev.idcustomer, dev.code, dev.device_search, dm.has_battery, dm.has_analog_ports, dm.has_digital_ports, dm.has_tpms, dm.has_fuel, dm.has_temperature,
        CASE
            WHEN tt.connected THEN 'Sim'::text
            ELSE 'Não'::text
        END AS connected, tt.connected AS is_connected, tt.speed, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS command_date, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS start_date, drv.driver, tt.address, tt.idtransaction, tt.online, rt.description AS record_type, vlt_device_has_alert_fnc(dev.iddevice) AS has_alert, dev.active, round(tt.odometer::numeric / 1000::numeric, 1) AS odometer
   FROM vlt_device dev
   JOIN vlt_transaction tt ON tt.idtransaction = (( SELECT vlt_transaction.idtransaction
      FROM vlt_transaction
     WHERE vlt_transaction.iddevice = dev.iddevice AND vlt_transaction.valid
     ORDER BY vlt_transaction.idtransaction DESC
    LIMIT 1))
   JOIN vlt_device_model dm ON dm.iddevicemodel = dev.iddevicemodel
   JOIN vlt_record_type rt ON rt.idrecordtype = tt.idrecordtype
   LEFT JOIN vlt_driver drv ON drv.iddriver = tt.iddriver
  WHERE dev.active;
CREATE OR REPLACE VIEW public.vlt_device_vw_old (
    iddevice,
    description,
    latitude,
    longitude,
    vehicle_code,
    idcustomer,
    code,
    device_search,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    connected,
    is_connected,
    speed,
    command_date,
    start_date,
    driver,
    address,
    idtransaction,
    online,
    record_type,
    has_alert,
    active,
    odometer)
AS
 SELECT dev.iddevice, dev.description, dev.last_latitude AS latitude, dev.last_longitude AS longitude, dev.vehicle_code, dev.idcustomer, dev.code, dev.device_search, dm.has_battery, dm.has_analog_ports, dm.has_digital_ports, dm.has_tpms, dm.has_fuel, dm.has_temperature,
        CASE
            WHEN tt.connected THEN 'Sim'::text
            ELSE 'Não'::text
        END AS connected, tt.connected AS is_connected, tt.speed, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS command_date, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS start_date, drv.driver, tt.address, tt.idtransaction, tt.online, rt.description AS record_type, vlt_device_has_alert_fnc(dev.iddevice) AS has_alert, dev.active, round(tt.odometer::numeric / 1000::numeric, 1) AS odometer
   FROM vlt_device dev
   JOIN vlt_transaction tt ON tt.idtransaction = (( SELECT vlt_transaction.idtransaction
      FROM vlt_transaction
     WHERE vlt_transaction.iddevice = dev.iddevice AND vlt_transaction.valid
     ORDER BY vlt_transaction.idtransaction DESC
    LIMIT 1))
   JOIN vlt_device_model dm ON dm.iddevicemodel = dev.iddevicemodel
   JOIN vlt_record_type rt ON rt.idrecordtype = tt.idrecordtype
   LEFT JOIN vlt_driver drv ON drv.iddriver = tt.iddriver
  WHERE dev.active;
CREATE OR REPLACE VIEW public.vlt_grouped_transactions_vw (
    address,
    connected,
    driver,
    description,
    hour,
    iddevice,
    idtransaction,
    idtransaction_group,
    is_connected,
    latitude,
    longitude,
    online,
    speed,
    start_date,
    vehicle_code,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature)
AS
 SELECT vlt_transaction.address,
        CASE
            WHEN vlt_transaction.connected THEN 'Sim'::text
            ELSE 'Não'::text
        END AS connected, vlt_driver.driver, vlt_device.description, vlt_transaction.time_dif AS hour, vlt_transaction.iddevice, vlt_transaction.idtransaction, vlt_transaction.idtransaction_group, vlt_transaction.connected AS is_connected, vlt_transaction.latitude, vlt_transaction.longitude, vlt_transaction.online, vlt_transaction.speed, to_char(vlt_transaction.command_date, 'dd/mm/yyyy hh24:mi:ss'::text) AS start_date, vlt_device.vehicle_code, vlt_device_model.has_battery, vlt_device_model.has_analog_ports, vlt_device_model.has_digital_ports, vlt_device_model.has_tpms, vlt_device_model.has_fuel, vlt_device_model.has_temperature
   FROM vlt_transaction
   JOIN vlt_device ON vlt_device.iddevice = vlt_transaction.iddevice
   LEFT JOIN vlt_driver ON vlt_driver.iddriver = vlt_transaction.iddriver
   JOIN vlt_device_model ON vlt_device_model.iddevicemodel = vlt_device.iddevicemodel
  WHERE vlt_transaction.valid
  ORDER BY vlt_transaction.command_date;
CREATE OR REPLACE VIEW public.vlt_port_status_vw (
    description,
    value,
    type,
    idtransaction_filter,
    output)
AS
 SELECT vlt_port.description, vlt_port_status.value,
        CASE
            WHEN vlt_record_type.code::text = '6'::text THEN 'D'::text
            ELSE 'A'::text
        END AS type, vlt_transaction_last.idtransaction AS idtransaction_filter, vlt_port.output
   FROM vlt_transaction_last
   JOIN vlt_record_type ON vlt_record_type.idrecordtype = vlt_transaction_last.idrecordtype AND (vlt_record_type.code::text = ANY (ARRAY['6'::text, '7'::text]))
   JOIN vlt_transaction ON vlt_transaction.idtransaction = vlt_transaction_last.idtransaction_last
   JOIN vlt_device ON vlt_device.iddevice = vlt_transaction.iddevice
   JOIN vlt_port ON vlt_port.iddevicemodel = vlt_device.iddevicemodel AND vlt_port.fuel = false
   JOIN vlt_port_status ON vlt_port_status.idport = vlt_port.idport AND vlt_port_status.idtransaction = vlt_transaction_last.idtransaction_last;
CREATE OR REPLACE VIEW public.vlt_refuel_vw (
    idrefuel,
    idrefuelstation,
    refuel_code,
    refuel_date,
    refuel_liters,
    refuel_value,
    idtransaction,
    iddevice,
    name,
    vehicle_code,
    per_liter,
    idcustomer)
AS
 SELECT vlt_refuel.idrefuel, vlt_refuel.idrefuelstation, vlt_refuel.refuel_code, vlt_refuel.refuel_date, vlt_refuel.refuel_liters, vlt_refuel.refuel_value, vlt_refuel.idtransaction, vlt_refuel.iddevice, vlt_refuel_station.name, vlt_device.vehicle_code, vlt_refuel.refuel_value /
        CASE
            WHEN vlt_refuel.refuel_liters = 0::numeric THEN 1::numeric
            ELSE vlt_refuel.refuel_liters
        END AS per_liter, vlt_device.idcustomer
   FROM vlt_refuel
   JOIN vlt_refuel_station ON vlt_refuel_station.idrefuelstation = vlt_refuel.idrefuelstation
   JOIN vlt_device ON vlt_device.iddevice = vlt_refuel.iddevice
  ORDER BY vlt_device.vehicle_code;
CREATE OR REPLACE VIEW public.vlt_seller_dc_vw (
    iddevice,
    idseller,
    vehicle_code,
    device_search,
    vehicle,
    command_date,
    record_type,
    active,
    code)
AS
 SELECT q.iddevice, q.idseller, q.vehicle_code, q.device_search, q.vehicle, tt.command_date, rt.description AS record_type, q.active, q.code
   FROM ( SELECT vlt_device.iddevice, par_customer.idseller, vlt_device.vehicle_code, vlt_device.device_search, vlt_device.description AS vehicle, vlt_device.active, vlt_device.code, vlt_last_tran_dev_fnc(vlt_device.iddevice) AS idtransaction
           FROM vlt_device, par_customer
          WHERE par_customer.idseller IS NOT NULL AND par_customer.idcustomer = vlt_device.idcustomer) q, vlt_transaction tt, vlt_record_type rt
  WHERE tt.idtransaction = q.idtransaction AND rt.idrecordtype = tt.idrecordtype;
CREATE OR REPLACE VIEW public.vlt_tpms_disparity_vw (
    vehicle_code,
    description,
    tpms_sequence,
    temperature,
    valr_min_temperature,
    valr_max_temperature,
    pressure,
    valr_min_preassure,
    valr_max_preassure,
    idcustomer,
    transaction_date)
AS
 SELECT vlt_device.vehicle_code, vlt_device.description, vlt_tpms.tpms_sequence, vlt_tpms_value.temperature, vlt_tpms_model.valr_min_temperature, vlt_tpms_model.valr_max_temperature, vlt_tpms_value.pressure, vlt_tpms_model.valr_min_preassure, vlt_tpms_model.valr_max_preassure, vlt_device.idcustomer, to_char(vlt_transaction.transaction_date, 'dd/mm/yyyy hh24:mi'::text) AS transaction_date
   FROM vlt_tpms
   JOIN vlt_device ON vlt_device.iddevice = vlt_tpms.iddevice
   JOIN vlt_tpms_value ON vlt_tpms_value.idtpmsvalue = vlt_tpms.idtpmsvalue_last
   JOIN vlt_tpms_model ON vlt_tpms_model.id = vlt_device.idtpmsmodel AND (vlt_tpms_model.valr_min_temperature > vlt_tpms_value.temperature OR vlt_tpms_model.valr_max_temperature < vlt_tpms_value.temperature OR vlt_tpms_model.valr_min_preassure > vlt_tpms_value.pressure OR vlt_tpms_model.valr_max_preassure < vlt_tpms_value.pressure)
   JOIN vlt_transaction ON vlt_transaction.idtransaction = vlt_tpms_value.idtransaction
  ORDER BY vlt_device.vehicle_code, vlt_tpms.tpms_sequence;
CREATE OR REPLACE VIEW public.vlt_transaction_driver_vw (
    idtransactiondriver,
    iddriver,
    transaction_date,
    iddevice,
    idcustomer,
    driver,
    device)
AS
 SELECT td.idtransactiondriver, td.iddriver, td.transaction_date, td.iddevice, dev.idcustomer, d.driver, dev.vehicle_code AS device
   FROM vlt_device dev
   JOIN vlt_transaction_driver td ON td.iddevice = dev.iddevice
   JOIN vlt_driver d ON d.iddriver = td.iddriver
  WHERE dev.active
  ORDER BY td.transaction_date;

DROP VIEW public.vlt_transaction_driver_vw;
DROP VIEW public.vlt_tpms_disparity_vw;
DROP VIEW public.vlt_seller_dc_vw;
DROP VIEW public.vlt_refuel_vw;
DROP VIEW public.vlt_port_status_vw;
DROP VIEW public.vlt_grouped_transactions_vw;
DROP VIEW public.vlt_device_vw_old;
DROP VIEW public.vlt_device_vw_ant;
DROP VIEW public.vlt_device_vw2;
DROP VIEW public.vlt_device_vw;
DROP VIEW public.vlt_device_search_vw;
DROP VIEW public.vlt_device_finger_vw;
DROP VIEW public.vlt_command_status_vw;
DROP VIEW public.sel_customer_list_vw;
DROP VIEW public.rpt_worked_hours_driver_vw;
DROP VIEW public.rpt_worked_hours_device_vw;
DROP VIEW public.rpt_speed_vw;
DROP VIEW public.rpt_route_vw;
DROP VIEW public.rep_route_vw;
DROP VIEW public.rep_online_offline_vw;
DROP VIEW public.par_message_vw;
DROP VIEW public.par_etiqueta_vw;
DROP VIEW public.mce_refuel_vw;
DROP VIEW public.mce_refuel_station_vw;
DROP VIEW public.mce_driver_vw;
DROP VIEW public.mce_device_vw;
DROP VIEW public.mce_customer_vw;
ALTER TABLE public.vlt_device
  ALTER COLUMN limit_group_on SET DEFAULT 0;
CREATE OR REPLACE VIEW public.mce_customer_vw (
    iddevice,
    idmonitoringcenter,
    idcustomer,
    customer,
    email,
    dthr_cadastro,
    desc_address,
    desc_complement,
    numr_address,
    desc_district,
    desc_city,
    desc_state,
    desc_country,
    desc_zip_address,
    desc_phone,
    desc_cellphone,
    desc_cgc,
    desc_ddd_cellphone,
    desc_ddd_phone,
    idseller,
    customer_search,
    rg,
    active,
    block_message)
AS
 SELECT vlt_device.iddevice, vlt_device_mc.idmonitoringcenter, par_customer.idcustomer, par_customer.customer, par_customer.email, par_customer.dthr_cadastro, par_customer.desc_address, par_customer.desc_complement, par_customer.numr_address, par_customer.desc_district, par_customer.desc_city, par_customer.desc_state, par_customer.desc_country, par_customer.desc_zip_address, par_customer.desc_phone, par_customer.desc_cellphone, par_customer.desc_cgc, par_customer.desc_ddd_cellphone, par_customer.desc_ddd_phone, par_customer.idseller, par_customer.customer_search, par_customer.rg, par_customer.active, par_customer.block_message
   FROM vlt_device
   JOIN par_customer ON par_customer.idcustomer = vlt_device.idcustomer
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice;
CREATE OR REPLACE VIEW public.mce_device_vw (
    iddevice,
    code,
    device_search,
    description,
    vehicle_code,
    address,
    speed,
    connected,
    command_date,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    idmonitoringcenter,
    customer,
    idcustomer,
    driver,
    active,
    speed_limit,
    renavam,
    chassi,
    mark_vehicle,
    model_vehicle,
    color_vehicle,
    fuel_estimate)
AS
 SELECT vlt_device.iddevice, vlt_device.code, vlt_device.device_search, vlt_device.description, vlt_device.vehicle_code, vlt_transaction.address, vlt_transaction.speed, vlt_transaction.connected, vlt_transaction.command_date, vlt_device_model.has_battery, vlt_device_model.has_analog_ports, vlt_device_model.has_digital_ports, vlt_device_model.has_tpms, vlt_device_model.has_fuel, vlt_device_model.has_temperature, vlt_device_mc.idmonitoringcenter, par_customer.customer, par_customer.idcustomer, vlt_driver.driver, vlt_device.active, vlt_device.speed_limit, vlt_device.renavam, vlt_device.chassi, vlt_device.mark_vehicle, vlt_device.model_vehicle, vlt_device.color_vehicle, vlt_device.fuel_estimate
   FROM vlt_device
   LEFT JOIN vlt_driver ON vlt_driver.iddriver = vlt_device.last_driver
   JOIN vlt_transaction ON vlt_transaction.idtransaction = vlt_last_tran_dev_fnc(vlt_device.iddevice)
   JOIN vlt_device_model ON vlt_device_model.iddevicemodel = vlt_device.iddevicemodel
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice
   JOIN par_customer ON par_customer.idcustomer = vlt_device.idcustomer
  WHERE vlt_device.active
  ORDER BY vlt_device.vehicle_code, vlt_device.description;
CREATE OR REPLACE VIEW public.mce_driver_vw (
    iddriver,
    code,
    driver,
    last_date,
    parcust_id,
    password,
    idmonitoringcenter,
    idcustomer)
AS
 SELECT vlt_driver.iddriver, vlt_driver.code, vlt_driver.driver, vlt_driver.last_date, vlt_driver.parcust_id, vlt_driver.password, vlt_device_mc.idmonitoringcenter, vlt_device.idcustomer
   FROM vlt_driver
   JOIN vlt_device ON vlt_device.idcustomer = vlt_driver.parcust_id
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice
  GROUP BY vlt_driver.iddriver, vlt_device_mc.idmonitoringcenter, vlt_device.idcustomer
  ORDER BY vlt_device_mc.idmonitoringcenter;
CREATE OR REPLACE VIEW public.mce_refuel_station_vw (
    idrefuelstation,
    code,
    name,
    idinterestpoint,
    idcustomer,
    idmonitoringcenter)
AS
 SELECT vlt_refuel_station.idrefuelstation, vlt_refuel_station.code, vlt_refuel_station.name, vlt_refuel_station.idinterestpoint, vlt_refuel_station.idcustomer, vlt_device_mc.idmonitoringcenter
   FROM vlt_refuel_station
   JOIN vlt_device ON vlt_device.idcustomer = vlt_refuel_station.idcustomer
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice
  GROUP BY vlt_refuel_station.idrefuelstation, vlt_device_mc.idmonitoringcenter
  ORDER BY vlt_device_mc.idmonitoringcenter;
CREATE OR REPLACE VIEW public.mce_refuel_vw (
    idrefuel,
    idrefuelstation,
    refuel_code,
    refuel_date,
    refuel_liters,
    refuel_value,
    idtransaction,
    iddevice,
    name,
    vehicle_code,
    per_liter,
    idmonitoringcenter,
    idcustomer)
AS
 SELECT vlt_refuel.idrefuel, vlt_refuel.idrefuelstation, vlt_refuel.refuel_code, vlt_refuel.refuel_date, vlt_refuel.refuel_liters, vlt_refuel.refuel_value, vlt_refuel.idtransaction, vlt_refuel.iddevice, vlt_refuel_station.name, vlt_device.vehicle_code, vlt_refuel.refuel_value / vlt_refuel.refuel_liters AS per_liter, vlt_device_mc.idmonitoringcenter, vlt_device.idcustomer
   FROM vlt_refuel
   JOIN vlt_refuel_station ON vlt_refuel_station.idrefuelstation = vlt_refuel.idrefuelstation
   JOIN vlt_device ON vlt_device.iddevice = vlt_refuel.iddevice
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice
  ORDER BY vlt_device.vehicle_code;
CREATE OR REPLACE VIEW public.par_etiqueta_vw (
    code,
    password,
    device_model,
    processador,
    model,
    iddevice)
AS
 SELECT vlt_device.code, vlt_gera_senha_device_fnc(vlt_device.code) AS password, vlt_device_model.description AS device_model, NULL::character varying AS processador, NULL::character varying AS model, vlt_device.iddevice
   FROM vlt_device
   JOIN vlt_device_model ON vlt_device_model.iddevicemodel = vlt_device.iddevicemodel;
CREATE OR REPLACE VIEW public.par_message_vw (
    idmessage,
    message_date,
    iduser,
    name,
    title,
    message,
    read_date,
    is_read,
    priority,
    from_iddevice,
    deviceorigin,
    to_iddevice,
    devicereceiver,
    from_iduser,
    messageorigin,
    messagereceiver)
AS
 SELECT par_message.idmessage, par_message.message_date, par_message.iduser, touser.name, par_message.title, par_message.message, par_message.read_date, par_message.is_read, par_message.priority, par_message.from_iddevice, fromdevice.vehicle_code AS deviceorigin, par_message.to_iddevice, todevice.vehicle_code AS devicereceiver, par_message.from_iduser,
        CASE
            WHEN par_message.origin::text = 'S'::text THEN 'Sistema'::character varying
            WHEN par_message.origin::text = 'U'::text THEN fromuser.name
            WHEN par_message.origin::text = 'D'::text THEN fromdevice.vehicle_code
            ELSE NULL::character varying
        END AS messageorigin,
        CASE
            WHEN par_message.iduser IS NOT NULL THEN touser.name
            WHEN par_message.to_iddevice IS NOT NULL THEN todevice.vehicle_code
            ELSE NULL::character varying
        END AS messagereceiver
   FROM par_message
   LEFT JOIN par_user fromuser ON fromuser.iduser = par_message.from_iduser
   LEFT JOIN par_user touser ON touser.iduser = par_message.iduser
   LEFT JOIN vlt_device fromdevice ON fromdevice.iddevice = par_message.from_iddevice
   LEFT JOIN vlt_device todevice ON todevice.iddevice = par_message.to_iddevice;
CREATE OR REPLACE VIEW public.rep_online_offline_vw (
    total,
    online,
    offline,
    vehicle,
    perc_online,
    perc_offline,
    iddevice)
AS
 SELECT o.online + of.offline AS total, o.online, of.offline, (vlt_device.vehicle_code::text || ' | '::text) || vlt_device.description::text AS vehicle, o.online * 100 / (o.online + of.offline) AS perc_online, of.offline * 100 / (o.online + of.offline) AS perc_offline, vlt_device.iddevice
   FROM vlt_device
   JOIN ( SELECT vlt_transaction.iddevice, count(vlt_transaction.online) AS online
           FROM vlt_transaction
          WHERE vlt_transaction.online = 'O'::bpchar AND vlt_transaction.online <> 'N'::bpchar
          GROUP BY vlt_transaction.iddevice, vlt_transaction.online) o ON o.iddevice = vlt_device.iddevice
   JOIN ( SELECT vlt_transaction.iddevice, count(vlt_transaction.online) AS offline
      FROM vlt_transaction
     WHERE vlt_transaction.online = 'M'::bpchar AND vlt_transaction.online <> 'N'::bpchar
     GROUP BY vlt_transaction.iddevice, vlt_transaction.online) of ON of.iddevice = vlt_device.iddevice;
CREATE OR REPLACE VIEW public.rep_route_vw (
    command_date,
    address,
    vehicle,
    date,
    latitude,
    longitude,
    iddevice)
AS
 SELECT min(vlt_transaction.command_date) AS command_date, vlt_transaction.address, (vlt_device.vehicle_code::text || ' - '::text) || vlt_device.description::text AS vehicle, vlt_transaction.command_date AS date, vlt_transaction.latitude, vlt_transaction.longitude, vlt_device.iddevice
   FROM vlt_transaction
   JOIN vlt_device ON vlt_device.iddevice = vlt_transaction.iddevice
  GROUP BY vlt_transaction.address, vlt_device.vehicle_code, vlt_device.description, vlt_transaction.latitude, vlt_transaction.longitude, vlt_transaction.command_date, vlt_device.iddevice
  ORDER BY min(vlt_transaction.command_date);
CREATE OR REPLACE VIEW public.rpt_route_vw (
    idtransaction,
    command_date,
    address,
    vehicle,
    date,
    latitude,
    longitude,
    iddevice)
AS
 SELECT tt.idtransaction_group AS idtransaction, tt.command_date, tt.address, dev.vehicle_code AS vehicle, tt.command_date AS date, tt.latitude, tt.longitude, dev.iddevice
   FROM vlt_transaction tt
   JOIN vlt_device dev ON dev.iddevice = tt.iddevice
  WHERE tt.valid AND tt.idtransaction_group IS NOT NULL AND tt.idtransaction_group = tt.idtransaction
  ORDER BY tt.command_date;
CREATE OR REPLACE VIEW public.rpt_speed_vw (
    idtransaction,
    vehicle,
    date,
    speed,
    address,
    iddevice,
    speed_limit,
    idcustomer,
    latitude,
    longitude,
    command_date)
AS
 SELECT vlt_transaction.idtransaction, vlt_device.vehicle_code::text AS vehicle, to_char(vlt_transaction.command_date, 'dd/mm/yyyy hh24:mi'::text) AS date, vlt_transaction.speed, vlt_transaction.address, vlt_device.iddevice, vlt_device.speed_limit, vlt_device.idcustomer, vlt_transaction.latitude, vlt_transaction.longitude, vlt_transaction.command_date
   FROM vlt_transaction
   JOIN vlt_device ON vlt_device.iddevice = vlt_transaction.iddevice
  WHERE vlt_transaction.valid
  ORDER BY vlt_transaction.command_date;
CREATE OR REPLACE VIEW public.rpt_worked_hours_device_vw (
    vehicle,
    driver,
    data_trabalhada,
    inicio,
    termino,
    horas,
    iddevice)
AS
 SELECT vlt_device.vehicle_code AS vehicle, ( SELECT vlt_driver.driver
           FROM vlt_driver
          WHERE vlt_driver.iddriver = vlt_transaction_driver_fnc(vlt_driver_record.idtransaction_start)) AS driver, to_date(to_char(tend.command_date, 'dd/mm/yyyy'::text), 'dd/mm/yyyy'::text) AS data_trabalhada, tstart.command_date AS inicio, tend.command_date AS termino, tend.command_date - tstart.command_date AS horas, vlt_driver_record.iddevice
   FROM vlt_driver_record
   JOIN vlt_transaction tstart ON tstart.idtransaction = vlt_driver_record.idtransaction_start
   JOIN vlt_transaction tend ON tend.idtransaction = vlt_driver_record.idtransaction_end
   JOIN vlt_device ON vlt_device.iddevice = vlt_driver_record.iddevice
  GROUP BY vlt_driver_record.iddevice, tstart.command_date, tend.command_date, vlt_device.vehicle_code, vlt_driver_record.idtransaction_start
  ORDER BY tstart.command_date, tend.command_date;
CREATE OR REPLACE VIEW public.rpt_worked_hours_driver_vw (
    vehicle,
    driver,
    data_trabalhada,
    inicio,
    termino,
    horas,
    iddriver)
AS
 SELECT ( SELECT vlt_device.vehicle_code
           FROM vlt_device
          WHERE vlt_device.iddevice = vlt_driver_record.iddevice) AS vehicle, ( SELECT vlt_driver.driver
           FROM vlt_driver
          WHERE vlt_driver.iddriver = vlt_transaction_driver_fnc(vlt_driver_record.idtransaction_start)) AS driver, to_date(to_char(tend.command_date, 'dd/mm/yyyy'::text), 'dd/mm/yyyy'::text) AS data_trabalhada, tstart.command_date AS inicio, tend.command_date AS termino, tend.command_date - tstart.command_date AS horas, vlt_transaction_driver_fnc(vlt_driver_record.idtransaction_start) AS iddriver
   FROM vlt_driver_record
   JOIN vlt_transaction tstart ON tstart.idtransaction = vlt_driver_record.idtransaction_start
   JOIN vlt_transaction tend ON tend.idtransaction = vlt_driver_record.idtransaction_end
  GROUP BY vlt_driver_record.iddevice, tstart.command_date, tend.command_date, vlt_driver_record.idtransaction_start
  ORDER BY tstart.command_date, tend.command_date;
CREATE OR REPLACE VIEW public.sel_customer_list_vw (
    idcustomer,
    customer,
    desc_phone,
    desc_cellphone,
    customer_search,
    idseller,
    quantidade)
AS
 SELECT par_customer.idcustomer, par_customer.customer, par_customer.desc_phone, par_customer.desc_cellphone, par_customer.customer_search, par_customer.idseller, ( SELECT count(*) AS count
           FROM vlt_device
          WHERE vlt_device.idcustomer = par_customer.idcustomer) AS quantidade
   FROM par_customer;
CREATE OR REPLACE VIEW public.vlt_command_status_vw (
    idmodelcommand,
    description,
    icon,
    command_text,
    iddevicemodel,
    btnclass,
    iddevice,
    has_parameter,
    parameter,
    access_level,
    message,
    command_date,
    buttoncolor,
    blocked,
    loading,
    sms)
AS
 SELECT vlt_model_command.idmodelcommand, vlt_model_command.description, vlt_model_command.icon, vlt_model_command.command_text, vlt_model_command.iddevicemodel, ( SELECT
                CASE
                    WHEN vlt_command.status::text = 'O'::text THEN 'btn-success'::text
                    WHEN vlt_command.status::text = 'X'::text THEN 'btn-danger'::text
                    WHEN vlt_command.status::text = 'A'::text THEN 'btn-info'::text
                    WHEN vlt_command.status::text = 'T'::text THEN 'btn-danger'::text
                    ELSE NULL::text
                END AS "case"
           FROM vlt_command
          WHERE vlt_command.idcommand = (( SELECT max(vlt_command.idcommand) AS max
                   FROM vlt_command
                  WHERE vlt_command.idmodelcommand = vlt_model_command.idmodelcommand AND vlt_command.iddevice = vlt_device.iddevice))) AS btnclass, vlt_device.iddevice, vlt_model_command.has_parameter, vlt_model_command.parameter, vlt_model_command.access_level, ( SELECT vlt_command.message
           FROM vlt_command
          WHERE vlt_command.idcommand = (( SELECT max(vlt_command.idcommand) AS max
                   FROM vlt_command
                  WHERE vlt_command.iddevice = vlt_device.iddevice AND vlt_command.idmodelcommand = vlt_model_command.idmodelcommand))) AS message, ( SELECT vlt_command.command_date
           FROM vlt_command
          WHERE vlt_command.idcommand = (( SELECT max(vlt_command.idcommand) AS max
                   FROM vlt_command
                  WHERE vlt_command.iddevice = vlt_device.iddevice AND vlt_command.idmodelcommand = vlt_model_command.idmodelcommand))) AS command_date, COALESCE(( SELECT
                CASE
                    WHEN vlt_command.status::text = 'O'::text THEN '#86b558'::text
                    WHEN vlt_command.status::text = 'X'::text THEN '#d3413b'::text
                    WHEN vlt_command.status::text = 'A'::text THEN '#68adde'::text
                    WHEN vlt_command.status::text = 'T'::text THEN '#d3413b'::text
                    ELSE '#68adde'::text
                END AS "case"
           FROM vlt_command
          WHERE vlt_command.idcommand = (( SELECT max(vlt_command.idcommand) AS max
                   FROM vlt_command
                  WHERE vlt_command.idmodelcommand = vlt_model_command.idmodelcommand AND vlt_command.iddevice = vlt_device.iddevice))), '#68adde'::text) AS buttoncolor,
        CASE
            WHEN (( SELECT count(*) AS count
               FROM vlt_command
              WHERE vlt_command.iddevice = vlt_device.iddevice AND (vlt_command.status::text = ANY (ARRAY['A'::text, 'E'::text])))) > 0 THEN true
            ELSE false
        END AS blocked, COALESCE(( SELECT
                CASE
                    WHEN vlt_command.status::text = 'E'::text THEN true
                    WHEN vlt_command.status::text = 'A'::text THEN true
                    ELSE false
                END AS "case"
           FROM vlt_command
          WHERE vlt_command.idcommand = (( SELECT max(vlt_command.idcommand) AS max
                   FROM vlt_command
                  WHERE vlt_command.idmodelcommand = vlt_model_command.idmodelcommand AND vlt_command.iddevice = vlt_device.iddevice))), false) AS loading, vlt_model_command.sms
   FROM vlt_model_command
   JOIN vlt_device ON vlt_device.iddevicemodel = vlt_model_command.iddevicemodel
   LEFT JOIN vlt_model_command_param ON vlt_model_command_param.idmodelcommand = vlt_model_command.idmodelcommand
  GROUP BY vlt_model_command.idmodelcommand, vlt_device.iddevice
  ORDER BY vlt_model_command.idmodelcommand;
CREATE OR REPLACE VIEW public.vlt_device_finger_vw (
    iddevicefinger,
    iddevice,
    vehicle_code,
    iddriver,
    driver,
    idcustomer)
AS
 SELECT def.iddevicefinger, dev.iddevice, (COALESCE(dev.vehicle_code, ''::character varying)::text || ' '::text) || COALESCE(dev.description, ''::character varying)::text AS vehicle_code, drv.iddriver, drv.driver, dev.idcustomer
   FROM vlt_device_finger def, vlt_device dev, vlt_driver_finger drf, vlt_driver drv
  WHERE def.iddevice = dev.iddevice AND drf.iddriverfinger = def.iddriverfinger AND drf.iddriver = drv.iddriver
  ORDER BY dev.vehicle_code, drv.code;
CREATE OR REPLACE VIEW public.vlt_device_search_vw (
    iddevice,
    vehicle_code,
    device_search,
    description,
    tag,
    idcustomer)
AS
 SELECT vlt_device.iddevice, vlt_device.vehicle_code, vlt_device.device_search, vlt_device.description, vlt_device.tag, vlt_device.idcustomer
   FROM vlt_device;
CREATE OR REPLACE VIEW public.vlt_device_vw (
    iddevice,
    description,
    latitude,
    longitude,
    vehicle_code,
    idcustomer,
    code,
    device_search,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    connected,
    is_connected,
    speed,
    command_date,
    start_date,
    driver,
    address,
    idtransaction,
    online,
    record_type,
    has_alert,
    active,
    odometer)
AS
 SELECT q.iddevice, q.description, q.latitude, q.longitude, q.vehicle_code, q.idcustomer, q.code, q.device_search, q.has_battery, q.has_analog_ports, q.has_digital_ports, q.has_tpms, q.has_fuel, q.has_temperature, q.connected, q.is_connected, q.speed, q.command_date, q.start_date, q.driver, q.address, q.idtransaction, q.online, q.record_type, q.has_alert, q.active, q.odometer
   FROM ( SELECT dev.iddevice, dev.description, tt.latitude, tt.longitude, dev.vehicle_code, dev.idcustomer, dev.code, dev.device_search, dm.has_battery, dm.has_analog_ports, dm.has_digital_ports, dm.has_tpms, dm.has_fuel, dm.has_temperature,
                CASE
                    WHEN tt.connected THEN 'Sim'::text
                    ELSE 'Não'::text
                END AS connected, tt.connected AS is_connected, tt.speed, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS command_date, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS start_date, ( SELECT drv.driver
                   FROM vlt_driver drv
                  WHERE drv.iddriver = tt.iddriver) AS driver, tt.address, tt.idtransaction, tt.online, rt.description AS record_type, vlt_device_has_alert_fnc(dev.iddevice) AS has_alert, dev.active, round(tt.odometer::numeric / 1000::numeric, 1) AS odometer
           FROM ( SELECT vlt_device.iddevice, vlt_last_invalid_tran_dev_fnc(vlt_device.iddevice) AS idtransaction, vlt_device.idcustomer
                   FROM vlt_device) tbl
      JOIN vlt_transaction tt ON tt.idtransaction = tbl.idtransaction
   JOIN vlt_device dev ON dev.iddevice = tbl.iddevice
   JOIN vlt_device_model dm ON dm.iddevicemodel = dev.iddevicemodel
   JOIN vlt_record_type rt ON rt.idrecordtype = tt.idrecordtype) q;
CREATE OR REPLACE VIEW public.vlt_device_vw2 (
    idcustomer,
    iddevice,
    code,
    device_search,
    description,
    vehicle_code,
    address,
    speed,
    connected,
    command_date,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature)
AS
 SELECT vlt_device.idcustomer, vlt_device.iddevice, vlt_device.code, vlt_device.device_search, vlt_device.description, vlt_device.vehicle_code, vlt_transaction.address, vlt_transaction.speed, vlt_transaction.connected, vlt_transaction.command_date, vlt_device_model.has_battery, vlt_device_model.has_analog_ports, vlt_device_model.has_digital_ports, vlt_device_model.has_tpms, vlt_device_model.has_fuel, vlt_device_model.has_temperature
   FROM vlt_device
   JOIN vlt_transaction ON vlt_transaction.idtransaction = vlt_device.last_transaction
   JOIN vlt_device_model ON vlt_device_model.iddevicemodel = vlt_device.iddevicemodel;
CREATE OR REPLACE VIEW public.vlt_device_vw_ant (
    iddevice,
    description,
    latitude,
    longitude,
    vehicle_code,
    idcustomer,
    code,
    device_search,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    connected,
    is_connected,
    speed,
    command_date,
    start_date,
    driver,
    address,
    idtransaction,
    online,
    record_type,
    has_alert,
    active,
    odometer)
AS
 SELECT dev.iddevice, dev.description, dev.last_latitude AS latitude, dev.last_longitude AS longitude, dev.vehicle_code, dev.idcustomer, dev.code, dev.device_search, dm.has_battery, dm.has_analog_ports, dm.has_digital_ports, dm.has_tpms, dm.has_fuel, dm.has_temperature,
        CASE
            WHEN tt.connected THEN 'Sim'::text
            ELSE 'Não'::text
        END AS connected, tt.connected AS is_connected, tt.speed, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS command_date, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS start_date, drv.driver, tt.address, tt.idtransaction, tt.online, rt.description AS record_type, vlt_device_has_alert_fnc(dev.iddevice) AS has_alert, dev.active, round(tt.odometer::numeric / 1000::numeric, 1) AS odometer
   FROM vlt_device dev
   JOIN vlt_transaction tt ON tt.idtransaction = (( SELECT vlt_transaction.idtransaction
      FROM vlt_transaction
     WHERE vlt_transaction.iddevice = dev.iddevice AND vlt_transaction.valid
     ORDER BY vlt_transaction.idtransaction DESC
    LIMIT 1))
   JOIN vlt_device_model dm ON dm.iddevicemodel = dev.iddevicemodel
   JOIN vlt_record_type rt ON rt.idrecordtype = tt.idrecordtype
   LEFT JOIN vlt_driver drv ON drv.iddriver = tt.iddriver
  WHERE dev.active;
CREATE OR REPLACE VIEW public.vlt_device_vw_old (
    iddevice,
    description,
    latitude,
    longitude,
    vehicle_code,
    idcustomer,
    code,
    device_search,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    connected,
    is_connected,
    speed,
    command_date,
    start_date,
    driver,
    address,
    idtransaction,
    online,
    record_type,
    has_alert,
    active,
    odometer)
AS
 SELECT dev.iddevice, dev.description, dev.last_latitude AS latitude, dev.last_longitude AS longitude, dev.vehicle_code, dev.idcustomer, dev.code, dev.device_search, dm.has_battery, dm.has_analog_ports, dm.has_digital_ports, dm.has_tpms, dm.has_fuel, dm.has_temperature,
        CASE
            WHEN tt.connected THEN 'Sim'::text
            ELSE 'Não'::text
        END AS connected, tt.connected AS is_connected, tt.speed, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS command_date, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS start_date, drv.driver, tt.address, tt.idtransaction, tt.online, rt.description AS record_type, vlt_device_has_alert_fnc(dev.iddevice) AS has_alert, dev.active, round(tt.odometer::numeric / 1000::numeric, 1) AS odometer
   FROM vlt_device dev
   JOIN vlt_transaction tt ON tt.idtransaction = (( SELECT vlt_transaction.idtransaction
      FROM vlt_transaction
     WHERE vlt_transaction.iddevice = dev.iddevice AND vlt_transaction.valid
     ORDER BY vlt_transaction.idtransaction DESC
    LIMIT 1))
   JOIN vlt_device_model dm ON dm.iddevicemodel = dev.iddevicemodel
   JOIN vlt_record_type rt ON rt.idrecordtype = tt.idrecordtype
   LEFT JOIN vlt_driver drv ON drv.iddriver = tt.iddriver
  WHERE dev.active;
CREATE OR REPLACE VIEW public.vlt_grouped_transactions_vw (
    address,
    connected,
    driver,
    description,
    hour,
    iddevice,
    idtransaction,
    idtransaction_group,
    is_connected,
    latitude,
    longitude,
    online,
    speed,
    start_date,
    vehicle_code,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature)
AS
 SELECT vlt_transaction.address,
        CASE
            WHEN vlt_transaction.connected THEN 'Sim'::text
            ELSE 'Não'::text
        END AS connected, vlt_driver.driver, vlt_device.description, vlt_transaction.time_dif AS hour, vlt_transaction.iddevice, vlt_transaction.idtransaction, vlt_transaction.idtransaction_group, vlt_transaction.connected AS is_connected, vlt_transaction.latitude, vlt_transaction.longitude, vlt_transaction.online, vlt_transaction.speed, to_char(vlt_transaction.command_date, 'dd/mm/yyyy hh24:mi:ss'::text) AS start_date, vlt_device.vehicle_code, vlt_device_model.has_battery, vlt_device_model.has_analog_ports, vlt_device_model.has_digital_ports, vlt_device_model.has_tpms, vlt_device_model.has_fuel, vlt_device_model.has_temperature
   FROM vlt_transaction
   JOIN vlt_device ON vlt_device.iddevice = vlt_transaction.iddevice
   LEFT JOIN vlt_driver ON vlt_driver.iddriver = vlt_transaction.iddriver
   JOIN vlt_device_model ON vlt_device_model.iddevicemodel = vlt_device.iddevicemodel
  WHERE vlt_transaction.valid
  ORDER BY vlt_transaction.command_date;
CREATE OR REPLACE VIEW public.vlt_port_status_vw (
    description,
    value,
    type,
    idtransaction_filter,
    output)
AS
 SELECT vlt_port.description, vlt_port_status.value,
        CASE
            WHEN vlt_record_type.code::text = '6'::text THEN 'D'::text
            ELSE 'A'::text
        END AS type, vlt_transaction_last.idtransaction AS idtransaction_filter, vlt_port.output
   FROM vlt_transaction_last
   JOIN vlt_record_type ON vlt_record_type.idrecordtype = vlt_transaction_last.idrecordtype AND (vlt_record_type.code::text = ANY (ARRAY['6'::text, '7'::text]))
   JOIN vlt_transaction ON vlt_transaction.idtransaction = vlt_transaction_last.idtransaction_last
   JOIN vlt_device ON vlt_device.iddevice = vlt_transaction.iddevice
   JOIN vlt_port ON vlt_port.iddevicemodel = vlt_device.iddevicemodel AND vlt_port.fuel = false
   JOIN vlt_port_status ON vlt_port_status.idport = vlt_port.idport AND vlt_port_status.idtransaction = vlt_transaction_last.idtransaction_last;
CREATE OR REPLACE VIEW public.vlt_refuel_vw (
    idrefuel,
    idrefuelstation,
    refuel_code,
    refuel_date,
    refuel_liters,
    refuel_value,
    idtransaction,
    iddevice,
    name,
    vehicle_code,
    per_liter,
    idcustomer)
AS
 SELECT vlt_refuel.idrefuel, vlt_refuel.idrefuelstation, vlt_refuel.refuel_code, vlt_refuel.refuel_date, vlt_refuel.refuel_liters, vlt_refuel.refuel_value, vlt_refuel.idtransaction, vlt_refuel.iddevice, vlt_refuel_station.name, vlt_device.vehicle_code, vlt_refuel.refuel_value /
        CASE
            WHEN vlt_refuel.refuel_liters = 0::numeric THEN 1::numeric
            ELSE vlt_refuel.refuel_liters
        END AS per_liter, vlt_device.idcustomer
   FROM vlt_refuel
   JOIN vlt_refuel_station ON vlt_refuel_station.idrefuelstation = vlt_refuel.idrefuelstation
   JOIN vlt_device ON vlt_device.iddevice = vlt_refuel.iddevice
  ORDER BY vlt_device.vehicle_code;
CREATE OR REPLACE VIEW public.vlt_seller_dc_vw (
    iddevice,
    idseller,
    vehicle_code,
    device_search,
    vehicle,
    command_date,
    record_type,
    active,
    code)
AS
 SELECT q.iddevice, q.idseller, q.vehicle_code, q.device_search, q.vehicle, tt.command_date, rt.description AS record_type, q.active, q.code
   FROM ( SELECT vlt_device.iddevice, par_customer.idseller, vlt_device.vehicle_code, vlt_device.device_search, vlt_device.description AS vehicle, vlt_device.active, vlt_device.code, vlt_last_tran_dev_fnc(vlt_device.iddevice) AS idtransaction
           FROM vlt_device, par_customer
          WHERE par_customer.idseller IS NOT NULL AND par_customer.idcustomer = vlt_device.idcustomer) q, vlt_transaction tt, vlt_record_type rt
  WHERE tt.idtransaction = q.idtransaction AND rt.idrecordtype = tt.idrecordtype;
CREATE OR REPLACE VIEW public.vlt_tpms_disparity_vw (
    vehicle_code,
    description,
    tpms_sequence,
    temperature,
    valr_min_temperature,
    valr_max_temperature,
    pressure,
    valr_min_preassure,
    valr_max_preassure,
    idcustomer,
    transaction_date)
AS
 SELECT vlt_device.vehicle_code, vlt_device.description, vlt_tpms.tpms_sequence, vlt_tpms_value.temperature, vlt_tpms_model.valr_min_temperature, vlt_tpms_model.valr_max_temperature, vlt_tpms_value.pressure, vlt_tpms_model.valr_min_preassure, vlt_tpms_model.valr_max_preassure, vlt_device.idcustomer, to_char(vlt_transaction.transaction_date, 'dd/mm/yyyy hh24:mi'::text) AS transaction_date
   FROM vlt_tpms
   JOIN vlt_device ON vlt_device.iddevice = vlt_tpms.iddevice
   JOIN vlt_tpms_value ON vlt_tpms_value.idtpmsvalue = vlt_tpms.idtpmsvalue_last
   JOIN vlt_tpms_model ON vlt_tpms_model.id = vlt_device.idtpmsmodel AND (vlt_tpms_model.valr_min_temperature > vlt_tpms_value.temperature OR vlt_tpms_model.valr_max_temperature < vlt_tpms_value.temperature OR vlt_tpms_model.valr_min_preassure > vlt_tpms_value.pressure OR vlt_tpms_model.valr_max_preassure < vlt_tpms_value.pressure)
   JOIN vlt_transaction ON vlt_transaction.idtransaction = vlt_tpms_value.idtransaction
  ORDER BY vlt_device.vehicle_code, vlt_tpms.tpms_sequence;
CREATE OR REPLACE VIEW public.vlt_transaction_driver_vw (
    idtransactiondriver,
    iddriver,
    transaction_date,
    iddevice,
    idcustomer,
    driver,
    device)
AS
 SELECT td.idtransactiondriver, td.iddriver, td.transaction_date, td.iddevice, dev.idcustomer, d.driver, dev.vehicle_code AS device
   FROM vlt_device dev
   JOIN vlt_transaction_driver td ON td.iddevice = dev.iddevice
   JOIN vlt_driver d ON d.iddriver = td.iddriver
  WHERE dev.active
  ORDER BY td.transaction_date;

-- object recreation
DROP INDEX public.vlt_device_idx4;

CREATE INDEX vlt_device_idx4 ON public.vlt_device
  USING btree (idcustomer);

-- object recreation
ALTER TABLE public.vlt_device_finger
  DROP CONSTRAINT check01 RESTRICT;

ALTER TABLE public.vlt_device_finger
  ADD CONSTRAINT check01 CHECK ((status)::text = ANY ((ARRAY['A'::character varying, 'E'::character varying, 'O'::character varying, 'X'::character varying, 'T'::character varying])::text[]));

CREATE TABLE public.vlt_device_sim (
  iddevicesim BIGINT DEFAULT nextval('vlt_device_sin_iddevicesin_seq'::regclass) NOT NULL,
  iddevice BIGINT NOT NULL,
  idsim BIGINT NOT NULL,
  CONSTRAINT vltdevicesin_pkey PRIMARY KEY(iddevicesim),
  CONSTRAINT foreign_key01 FOREIGN KEY (iddevice)
    REFERENCES public.vlt_device(iddevice)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT foreign_key02 FOREIGN KEY (idsim)
    REFERENCES public.vlt_sim(idsim)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE INDEX vlt_device_sim_idx01 ON public.vlt_device_sim
  USING btree (iddevice);

DROP VIEW public.vlt_transaction_driver_vw;
DROP VIEW public.vlt_grouped_transactions_vw;
DROP VIEW public.vlt_driver_workday_date_vw;
DROP VIEW public.vlt_device_vw_old;
DROP VIEW public.vlt_device_vw_ant;
DROP VIEW public.vlt_device_vw;
DROP VIEW public.vlt_device_finger_vw;
DROP VIEW public.rpt_worked_hours_driver_vw;
DROP VIEW public.rpt_worked_hours_device_vw;
DROP VIEW public.mce_driver_vw;
DROP VIEW public.mce_device_vw;
ALTER TABLE public.vlt_driver
  ALTER COLUMN code TYPE VARCHAR(4);
CREATE OR REPLACE VIEW public.mce_device_vw (
    iddevice,
    code,
    device_search,
    description,
    vehicle_code,
    address,
    speed,
    connected,
    command_date,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    idmonitoringcenter,
    customer,
    idcustomer,
    driver,
    active,
    speed_limit,
    renavam,
    chassi,
    mark_vehicle,
    model_vehicle,
    color_vehicle,
    fuel_estimate)
AS
 SELECT vlt_device.iddevice, vlt_device.code, vlt_device.device_search, vlt_device.description, vlt_device.vehicle_code, vlt_transaction.address, vlt_transaction.speed, vlt_transaction.connected, vlt_transaction.command_date, vlt_device_model.has_battery, vlt_device_model.has_analog_ports, vlt_device_model.has_digital_ports, vlt_device_model.has_tpms, vlt_device_model.has_fuel, vlt_device_model.has_temperature, vlt_device_mc.idmonitoringcenter, par_customer.customer, par_customer.idcustomer, vlt_driver.driver, vlt_device.active, vlt_device.speed_limit, vlt_device.renavam, vlt_device.chassi, vlt_device.mark_vehicle, vlt_device.model_vehicle, vlt_device.color_vehicle, vlt_device.fuel_estimate
   FROM vlt_device
   LEFT JOIN vlt_driver ON vlt_driver.iddriver = vlt_device.last_driver
   JOIN vlt_transaction ON vlt_transaction.idtransaction = vlt_last_tran_dev_fnc(vlt_device.iddevice)
   JOIN vlt_device_model ON vlt_device_model.iddevicemodel = vlt_device.iddevicemodel
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice
   JOIN par_customer ON par_customer.idcustomer = vlt_device.idcustomer
  WHERE vlt_device.active
  ORDER BY vlt_device.vehicle_code, vlt_device.description;
CREATE OR REPLACE VIEW public.mce_driver_vw (
    iddriver,
    code,
    driver,
    last_date,
    parcust_id,
    password,
    idmonitoringcenter,
    idcustomer)
AS
 SELECT vlt_driver.iddriver, vlt_driver.code, vlt_driver.driver, vlt_driver.last_date, vlt_driver.parcust_id, vlt_driver.password, vlt_device_mc.idmonitoringcenter, vlt_device.idcustomer
   FROM vlt_driver
   JOIN vlt_device ON vlt_device.idcustomer = vlt_driver.parcust_id
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice
  GROUP BY vlt_driver.iddriver, vlt_device_mc.idmonitoringcenter, vlt_device.idcustomer
  ORDER BY vlt_device_mc.idmonitoringcenter;
CREATE OR REPLACE VIEW public.rpt_worked_hours_device_vw (
    vehicle,
    driver,
    data_trabalhada,
    inicio,
    termino,
    horas,
    iddevice)
AS
 SELECT vlt_device.vehicle_code AS vehicle, ( SELECT vlt_driver.driver
           FROM vlt_driver
          WHERE vlt_driver.iddriver = vlt_transaction_driver_fnc(vlt_driver_record.idtransaction_start)) AS driver, to_date(to_char(tend.command_date, 'dd/mm/yyyy'::text), 'dd/mm/yyyy'::text) AS data_trabalhada, tstart.command_date AS inicio, tend.command_date AS termino, tend.command_date - tstart.command_date AS horas, vlt_driver_record.iddevice
   FROM vlt_driver_record
   JOIN vlt_transaction tstart ON tstart.idtransaction = vlt_driver_record.idtransaction_start
   JOIN vlt_transaction tend ON tend.idtransaction = vlt_driver_record.idtransaction_end
   JOIN vlt_device ON vlt_device.iddevice = vlt_driver_record.iddevice
  GROUP BY vlt_driver_record.iddevice, tstart.command_date, tend.command_date, vlt_device.vehicle_code, vlt_driver_record.idtransaction_start
  ORDER BY tstart.command_date, tend.command_date;
CREATE OR REPLACE VIEW public.rpt_worked_hours_driver_vw (
    vehicle,
    driver,
    data_trabalhada,
    inicio,
    termino,
    horas,
    iddriver)
AS
 SELECT ( SELECT vlt_device.vehicle_code
           FROM vlt_device
          WHERE vlt_device.iddevice = vlt_driver_record.iddevice) AS vehicle, ( SELECT vlt_driver.driver
           FROM vlt_driver
          WHERE vlt_driver.iddriver = vlt_transaction_driver_fnc(vlt_driver_record.idtransaction_start)) AS driver, to_date(to_char(tend.command_date, 'dd/mm/yyyy'::text), 'dd/mm/yyyy'::text) AS data_trabalhada, tstart.command_date AS inicio, tend.command_date AS termino, tend.command_date - tstart.command_date AS horas, vlt_transaction_driver_fnc(vlt_driver_record.idtransaction_start) AS iddriver
   FROM vlt_driver_record
   JOIN vlt_transaction tstart ON tstart.idtransaction = vlt_driver_record.idtransaction_start
   JOIN vlt_transaction tend ON tend.idtransaction = vlt_driver_record.idtransaction_end
  GROUP BY vlt_driver_record.iddevice, tstart.command_date, tend.command_date, vlt_driver_record.idtransaction_start
  ORDER BY tstart.command_date, tend.command_date;
CREATE OR REPLACE VIEW public.vlt_device_finger_vw (
    iddevicefinger,
    iddevice,
    vehicle_code,
    iddriver,
    driver,
    idcustomer)
AS
 SELECT def.iddevicefinger, dev.iddevice, (COALESCE(dev.vehicle_code, ''::character varying)::text || ' '::text) || COALESCE(dev.description, ''::character varying)::text AS vehicle_code, drv.iddriver, drv.driver, dev.idcustomer
   FROM vlt_device_finger def, vlt_device dev, vlt_driver_finger drf, vlt_driver drv
  WHERE def.iddevice = dev.iddevice AND drf.iddriverfinger = def.iddriverfinger AND drf.iddriver = drv.iddriver
  ORDER BY dev.vehicle_code, drv.code;
CREATE OR REPLACE VIEW public.vlt_device_vw (
    iddevice,
    description,
    latitude,
    longitude,
    vehicle_code,
    idcustomer,
    code,
    device_search,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    connected,
    is_connected,
    speed,
    command_date,
    start_date,
    driver,
    address,
    idtransaction,
    online,
    record_type,
    has_alert,
    active,
    odometer)
AS
 SELECT q.iddevice, q.description, q.latitude, q.longitude, q.vehicle_code, q.idcustomer, q.code, q.device_search, q.has_battery, q.has_analog_ports, q.has_digital_ports, q.has_tpms, q.has_fuel, q.has_temperature, q.connected, q.is_connected, q.speed, q.command_date, q.start_date, q.driver, q.address, q.idtransaction, q.online, q.record_type, q.has_alert, q.active, q.odometer
   FROM ( SELECT dev.iddevice, dev.description, tt.latitude, tt.longitude, dev.vehicle_code, dev.idcustomer, dev.code, dev.device_search, dm.has_battery, dm.has_analog_ports, dm.has_digital_ports, dm.has_tpms, dm.has_fuel, dm.has_temperature,
                CASE
                    WHEN tt.connected THEN 'Sim'::text
                    ELSE 'Não'::text
                END AS connected, tt.connected AS is_connected, tt.speed, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS command_date, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS start_date, ( SELECT drv.driver
                   FROM vlt_driver drv
                  WHERE drv.iddriver = tt.iddriver) AS driver, tt.address, tt.idtransaction, tt.online, rt.description AS record_type, vlt_device_has_alert_fnc(dev.iddevice) AS has_alert, dev.active, round(tt.odometer::numeric / 1000::numeric, 1) AS odometer
           FROM ( SELECT vlt_device.iddevice, vlt_last_invalid_tran_dev_fnc(vlt_device.iddevice) AS idtransaction, vlt_device.idcustomer
                   FROM vlt_device) tbl
      JOIN vlt_transaction tt ON tt.idtransaction = tbl.idtransaction
   JOIN vlt_device dev ON dev.iddevice = tbl.iddevice
   JOIN vlt_device_model dm ON dm.iddevicemodel = dev.iddevicemodel
   JOIN vlt_record_type rt ON rt.idrecordtype = tt.idrecordtype) q;
CREATE OR REPLACE VIEW public.vlt_device_vw_ant (
    iddevice,
    description,
    latitude,
    longitude,
    vehicle_code,
    idcustomer,
    code,
    device_search,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    connected,
    is_connected,
    speed,
    command_date,
    start_date,
    driver,
    address,
    idtransaction,
    online,
    record_type,
    has_alert,
    active,
    odometer)
AS
 SELECT dev.iddevice, dev.description, dev.last_latitude AS latitude, dev.last_longitude AS longitude, dev.vehicle_code, dev.idcustomer, dev.code, dev.device_search, dm.has_battery, dm.has_analog_ports, dm.has_digital_ports, dm.has_tpms, dm.has_fuel, dm.has_temperature,
        CASE
            WHEN tt.connected THEN 'Sim'::text
            ELSE 'Não'::text
        END AS connected, tt.connected AS is_connected, tt.speed, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS command_date, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS start_date, drv.driver, tt.address, tt.idtransaction, tt.online, rt.description AS record_type, vlt_device_has_alert_fnc(dev.iddevice) AS has_alert, dev.active, round(tt.odometer::numeric / 1000::numeric, 1) AS odometer
   FROM vlt_device dev
   JOIN vlt_transaction tt ON tt.idtransaction = (( SELECT vlt_transaction.idtransaction
      FROM vlt_transaction
     WHERE vlt_transaction.iddevice = dev.iddevice AND vlt_transaction.valid
     ORDER BY vlt_transaction.idtransaction DESC
    LIMIT 1))
   JOIN vlt_device_model dm ON dm.iddevicemodel = dev.iddevicemodel
   JOIN vlt_record_type rt ON rt.idrecordtype = tt.idrecordtype
   LEFT JOIN vlt_driver drv ON drv.iddriver = tt.iddriver
  WHERE dev.active;
CREATE OR REPLACE VIEW public.vlt_device_vw_old (
    iddevice,
    description,
    latitude,
    longitude,
    vehicle_code,
    idcustomer,
    code,
    device_search,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    connected,
    is_connected,
    speed,
    command_date,
    start_date,
    driver,
    address,
    idtransaction,
    online,
    record_type,
    has_alert,
    active,
    odometer)
AS
 SELECT dev.iddevice, dev.description, dev.last_latitude AS latitude, dev.last_longitude AS longitude, dev.vehicle_code, dev.idcustomer, dev.code, dev.device_search, dm.has_battery, dm.has_analog_ports, dm.has_digital_ports, dm.has_tpms, dm.has_fuel, dm.has_temperature,
        CASE
            WHEN tt.connected THEN 'Sim'::text
            ELSE 'Não'::text
        END AS connected, tt.connected AS is_connected, tt.speed, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS command_date, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS start_date, drv.driver, tt.address, tt.idtransaction, tt.online, rt.description AS record_type, vlt_device_has_alert_fnc(dev.iddevice) AS has_alert, dev.active, round(tt.odometer::numeric / 1000::numeric, 1) AS odometer
   FROM vlt_device dev
   JOIN vlt_transaction tt ON tt.idtransaction = (( SELECT vlt_transaction.idtransaction
      FROM vlt_transaction
     WHERE vlt_transaction.iddevice = dev.iddevice AND vlt_transaction.valid
     ORDER BY vlt_transaction.idtransaction DESC
    LIMIT 1))
   JOIN vlt_device_model dm ON dm.iddevicemodel = dev.iddevicemodel
   JOIN vlt_record_type rt ON rt.idrecordtype = tt.idrecordtype
   LEFT JOIN vlt_driver drv ON drv.iddriver = tt.iddriver
  WHERE dev.active;
CREATE OR REPLACE VIEW public.vlt_driver_workday_date_vw (
    date,
    idcustomer,
    to_char)
AS
 SELECT DISTINCT to_char(vlt_driver_workday.workday_date::timestamp with time zone, 'mm/yyyy'::text) AS date, par_customer.idcustomer, to_char(vlt_driver_workday.workday_date::timestamp with time zone, 'yyyy-mm'::text) AS to_char
   FROM vlt_driver_workday
   JOIN vlt_driver ON vlt_driver.iddriver = vlt_driver_workday.iddriver
   JOIN par_customer ON vlt_driver.parcust_id = par_customer.idcustomer
  ORDER BY to_char(vlt_driver_workday.workday_date::timestamp with time zone, 'yyyy-mm'::text) DESC;
CREATE OR REPLACE VIEW public.vlt_grouped_transactions_vw (
    address,
    connected,
    driver,
    description,
    hour,
    iddevice,
    idtransaction,
    idtransaction_group,
    is_connected,
    latitude,
    longitude,
    online,
    speed,
    start_date,
    vehicle_code,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature)
AS
 SELECT vlt_transaction.address,
        CASE
            WHEN vlt_transaction.connected THEN 'Sim'::text
            ELSE 'Não'::text
        END AS connected, vlt_driver.driver, vlt_device.description, vlt_transaction.time_dif AS hour, vlt_transaction.iddevice, vlt_transaction.idtransaction, vlt_transaction.idtransaction_group, vlt_transaction.connected AS is_connected, vlt_transaction.latitude, vlt_transaction.longitude, vlt_transaction.online, vlt_transaction.speed, to_char(vlt_transaction.command_date, 'dd/mm/yyyy hh24:mi:ss'::text) AS start_date, vlt_device.vehicle_code, vlt_device_model.has_battery, vlt_device_model.has_analog_ports, vlt_device_model.has_digital_ports, vlt_device_model.has_tpms, vlt_device_model.has_fuel, vlt_device_model.has_temperature
   FROM vlt_transaction
   JOIN vlt_device ON vlt_device.iddevice = vlt_transaction.iddevice
   LEFT JOIN vlt_driver ON vlt_driver.iddriver = vlt_transaction.iddriver
   JOIN vlt_device_model ON vlt_device_model.iddevicemodel = vlt_device.iddevicemodel
  WHERE vlt_transaction.valid
  ORDER BY vlt_transaction.command_date;
CREATE OR REPLACE VIEW public.vlt_transaction_driver_vw (
    idtransactiondriver,
    iddriver,
    transaction_date,
    iddevice,
    idcustomer,
    driver,
    device)
AS
 SELECT td.idtransactiondriver, td.iddriver, td.transaction_date, td.iddevice, dev.idcustomer, d.driver, dev.vehicle_code AS device
   FROM vlt_device dev
   JOIN vlt_transaction_driver td ON td.iddevice = dev.iddevice
   JOIN vlt_driver d ON d.iddriver = td.iddriver
  WHERE dev.active
  ORDER BY td.transaction_date;

DROP VIEW public.vlt_transaction_driver_vw;
DROP VIEW public.vlt_grouped_transactions_vw;
DROP VIEW public.vlt_driver_workday_date_vw;
DROP VIEW public.vlt_device_vw_old;
DROP VIEW public.vlt_device_vw_ant;
DROP VIEW public.vlt_device_vw;
DROP VIEW public.vlt_device_finger_vw;
DROP VIEW public.rpt_worked_hours_driver_vw;
DROP VIEW public.rpt_worked_hours_device_vw;
DROP VIEW public.mce_driver_vw;
DROP VIEW public.mce_device_vw;
ALTER TABLE public.vlt_driver
  ALTER COLUMN driver TYPE VARCHAR(60);
CREATE OR REPLACE VIEW public.mce_device_vw (
    iddevice,
    code,
    device_search,
    description,
    vehicle_code,
    address,
    speed,
    connected,
    command_date,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    idmonitoringcenter,
    customer,
    idcustomer,
    driver,
    active,
    speed_limit,
    renavam,
    chassi,
    mark_vehicle,
    model_vehicle,
    color_vehicle,
    fuel_estimate)
AS
 SELECT vlt_device.iddevice, vlt_device.code, vlt_device.device_search, vlt_device.description, vlt_device.vehicle_code, vlt_transaction.address, vlt_transaction.speed, vlt_transaction.connected, vlt_transaction.command_date, vlt_device_model.has_battery, vlt_device_model.has_analog_ports, vlt_device_model.has_digital_ports, vlt_device_model.has_tpms, vlt_device_model.has_fuel, vlt_device_model.has_temperature, vlt_device_mc.idmonitoringcenter, par_customer.customer, par_customer.idcustomer, vlt_driver.driver, vlt_device.active, vlt_device.speed_limit, vlt_device.renavam, vlt_device.chassi, vlt_device.mark_vehicle, vlt_device.model_vehicle, vlt_device.color_vehicle, vlt_device.fuel_estimate
   FROM vlt_device
   LEFT JOIN vlt_driver ON vlt_driver.iddriver = vlt_device.last_driver
   JOIN vlt_transaction ON vlt_transaction.idtransaction = vlt_last_tran_dev_fnc(vlt_device.iddevice)
   JOIN vlt_device_model ON vlt_device_model.iddevicemodel = vlt_device.iddevicemodel
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice
   JOIN par_customer ON par_customer.idcustomer = vlt_device.idcustomer
  WHERE vlt_device.active
  ORDER BY vlt_device.vehicle_code, vlt_device.description;
CREATE OR REPLACE VIEW public.mce_driver_vw (
    iddriver,
    code,
    driver,
    last_date,
    parcust_id,
    password,
    idmonitoringcenter,
    idcustomer)
AS
 SELECT vlt_driver.iddriver, vlt_driver.code, vlt_driver.driver, vlt_driver.last_date, vlt_driver.parcust_id, vlt_driver.password, vlt_device_mc.idmonitoringcenter, vlt_device.idcustomer
   FROM vlt_driver
   JOIN vlt_device ON vlt_device.idcustomer = vlt_driver.parcust_id
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice
  GROUP BY vlt_driver.iddriver, vlt_device_mc.idmonitoringcenter, vlt_device.idcustomer
  ORDER BY vlt_device_mc.idmonitoringcenter;
CREATE OR REPLACE VIEW public.rpt_worked_hours_device_vw (
    vehicle,
    driver,
    data_trabalhada,
    inicio,
    termino,
    horas,
    iddevice)
AS
 SELECT vlt_device.vehicle_code AS vehicle, ( SELECT vlt_driver.driver
           FROM vlt_driver
          WHERE vlt_driver.iddriver = vlt_transaction_driver_fnc(vlt_driver_record.idtransaction_start)) AS driver, to_date(to_char(tend.command_date, 'dd/mm/yyyy'::text), 'dd/mm/yyyy'::text) AS data_trabalhada, tstart.command_date AS inicio, tend.command_date AS termino, tend.command_date - tstart.command_date AS horas, vlt_driver_record.iddevice
   FROM vlt_driver_record
   JOIN vlt_transaction tstart ON tstart.idtransaction = vlt_driver_record.idtransaction_start
   JOIN vlt_transaction tend ON tend.idtransaction = vlt_driver_record.idtransaction_end
   JOIN vlt_device ON vlt_device.iddevice = vlt_driver_record.iddevice
  GROUP BY vlt_driver_record.iddevice, tstart.command_date, tend.command_date, vlt_device.vehicle_code, vlt_driver_record.idtransaction_start
  ORDER BY tstart.command_date, tend.command_date;
CREATE OR REPLACE VIEW public.rpt_worked_hours_driver_vw (
    vehicle,
    driver,
    data_trabalhada,
    inicio,
    termino,
    horas,
    iddriver)
AS
 SELECT ( SELECT vlt_device.vehicle_code
           FROM vlt_device
          WHERE vlt_device.iddevice = vlt_driver_record.iddevice) AS vehicle, ( SELECT vlt_driver.driver
           FROM vlt_driver
          WHERE vlt_driver.iddriver = vlt_transaction_driver_fnc(vlt_driver_record.idtransaction_start)) AS driver, to_date(to_char(tend.command_date, 'dd/mm/yyyy'::text), 'dd/mm/yyyy'::text) AS data_trabalhada, tstart.command_date AS inicio, tend.command_date AS termino, tend.command_date - tstart.command_date AS horas, vlt_transaction_driver_fnc(vlt_driver_record.idtransaction_start) AS iddriver
   FROM vlt_driver_record
   JOIN vlt_transaction tstart ON tstart.idtransaction = vlt_driver_record.idtransaction_start
   JOIN vlt_transaction tend ON tend.idtransaction = vlt_driver_record.idtransaction_end
  GROUP BY vlt_driver_record.iddevice, tstart.command_date, tend.command_date, vlt_driver_record.idtransaction_start
  ORDER BY tstart.command_date, tend.command_date;
CREATE OR REPLACE VIEW public.vlt_device_finger_vw (
    iddevicefinger,
    iddevice,
    vehicle_code,
    iddriver,
    driver,
    idcustomer)
AS
 SELECT def.iddevicefinger, dev.iddevice, (COALESCE(dev.vehicle_code, ''::character varying)::text || ' '::text) || COALESCE(dev.description, ''::character varying)::text AS vehicle_code, drv.iddriver, drv.driver, dev.idcustomer
   FROM vlt_device_finger def, vlt_device dev, vlt_driver_finger drf, vlt_driver drv
  WHERE def.iddevice = dev.iddevice AND drf.iddriverfinger = def.iddriverfinger AND drf.iddriver = drv.iddriver
  ORDER BY dev.vehicle_code, drv.code;
CREATE OR REPLACE VIEW public.vlt_device_vw (
    iddevice,
    description,
    latitude,
    longitude,
    vehicle_code,
    idcustomer,
    code,
    device_search,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    connected,
    is_connected,
    speed,
    command_date,
    start_date,
    driver,
    address,
    idtransaction,
    online,
    record_type,
    has_alert,
    active,
    odometer)
AS
 SELECT q.iddevice, q.description, q.latitude, q.longitude, q.vehicle_code, q.idcustomer, q.code, q.device_search, q.has_battery, q.has_analog_ports, q.has_digital_ports, q.has_tpms, q.has_fuel, q.has_temperature, q.connected, q.is_connected, q.speed, q.command_date, q.start_date, q.driver, q.address, q.idtransaction, q.online, q.record_type, q.has_alert, q.active, q.odometer
   FROM ( SELECT dev.iddevice, dev.description, tt.latitude, tt.longitude, dev.vehicle_code, dev.idcustomer, dev.code, dev.device_search, dm.has_battery, dm.has_analog_ports, dm.has_digital_ports, dm.has_tpms, dm.has_fuel, dm.has_temperature,
                CASE
                    WHEN tt.connected THEN 'Sim'::text
                    ELSE 'Não'::text
                END AS connected, tt.connected AS is_connected, tt.speed, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS command_date, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS start_date, ( SELECT drv.driver
                   FROM vlt_driver drv
                  WHERE drv.iddriver = tt.iddriver) AS driver, tt.address, tt.idtransaction, tt.online, rt.description AS record_type, vlt_device_has_alert_fnc(dev.iddevice) AS has_alert, dev.active, round(tt.odometer::numeric / 1000::numeric, 1) AS odometer
           FROM ( SELECT vlt_device.iddevice, vlt_last_invalid_tran_dev_fnc(vlt_device.iddevice) AS idtransaction, vlt_device.idcustomer
                   FROM vlt_device) tbl
      JOIN vlt_transaction tt ON tt.idtransaction = tbl.idtransaction
   JOIN vlt_device dev ON dev.iddevice = tbl.iddevice
   JOIN vlt_device_model dm ON dm.iddevicemodel = dev.iddevicemodel
   JOIN vlt_record_type rt ON rt.idrecordtype = tt.idrecordtype) q;
CREATE OR REPLACE VIEW public.vlt_device_vw_ant (
    iddevice,
    description,
    latitude,
    longitude,
    vehicle_code,
    idcustomer,
    code,
    device_search,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    connected,
    is_connected,
    speed,
    command_date,
    start_date,
    driver,
    address,
    idtransaction,
    online,
    record_type,
    has_alert,
    active,
    odometer)
AS
 SELECT dev.iddevice, dev.description, dev.last_latitude AS latitude, dev.last_longitude AS longitude, dev.vehicle_code, dev.idcustomer, dev.code, dev.device_search, dm.has_battery, dm.has_analog_ports, dm.has_digital_ports, dm.has_tpms, dm.has_fuel, dm.has_temperature,
        CASE
            WHEN tt.connected THEN 'Sim'::text
            ELSE 'Não'::text
        END AS connected, tt.connected AS is_connected, tt.speed, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS command_date, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS start_date, drv.driver, tt.address, tt.idtransaction, tt.online, rt.description AS record_type, vlt_device_has_alert_fnc(dev.iddevice) AS has_alert, dev.active, round(tt.odometer::numeric / 1000::numeric, 1) AS odometer
   FROM vlt_device dev
   JOIN vlt_transaction tt ON tt.idtransaction = (( SELECT vlt_transaction.idtransaction
      FROM vlt_transaction
     WHERE vlt_transaction.iddevice = dev.iddevice AND vlt_transaction.valid
     ORDER BY vlt_transaction.idtransaction DESC
    LIMIT 1))
   JOIN vlt_device_model dm ON dm.iddevicemodel = dev.iddevicemodel
   JOIN vlt_record_type rt ON rt.idrecordtype = tt.idrecordtype
   LEFT JOIN vlt_driver drv ON drv.iddriver = tt.iddriver
  WHERE dev.active;
CREATE OR REPLACE VIEW public.vlt_device_vw_old (
    iddevice,
    description,
    latitude,
    longitude,
    vehicle_code,
    idcustomer,
    code,
    device_search,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    connected,
    is_connected,
    speed,
    command_date,
    start_date,
    driver,
    address,
    idtransaction,
    online,
    record_type,
    has_alert,
    active,
    odometer)
AS
 SELECT dev.iddevice, dev.description, dev.last_latitude AS latitude, dev.last_longitude AS longitude, dev.vehicle_code, dev.idcustomer, dev.code, dev.device_search, dm.has_battery, dm.has_analog_ports, dm.has_digital_ports, dm.has_tpms, dm.has_fuel, dm.has_temperature,
        CASE
            WHEN tt.connected THEN 'Sim'::text
            ELSE 'Não'::text
        END AS connected, tt.connected AS is_connected, tt.speed, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS command_date, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS start_date, drv.driver, tt.address, tt.idtransaction, tt.online, rt.description AS record_type, vlt_device_has_alert_fnc(dev.iddevice) AS has_alert, dev.active, round(tt.odometer::numeric / 1000::numeric, 1) AS odometer
   FROM vlt_device dev
   JOIN vlt_transaction tt ON tt.idtransaction = (( SELECT vlt_transaction.idtransaction
      FROM vlt_transaction
     WHERE vlt_transaction.iddevice = dev.iddevice AND vlt_transaction.valid
     ORDER BY vlt_transaction.idtransaction DESC
    LIMIT 1))
   JOIN vlt_device_model dm ON dm.iddevicemodel = dev.iddevicemodel
   JOIN vlt_record_type rt ON rt.idrecordtype = tt.idrecordtype
   LEFT JOIN vlt_driver drv ON drv.iddriver = tt.iddriver
  WHERE dev.active;
CREATE OR REPLACE VIEW public.vlt_driver_workday_date_vw (
    date,
    idcustomer,
    to_char)
AS
 SELECT DISTINCT to_char(vlt_driver_workday.workday_date::timestamp with time zone, 'mm/yyyy'::text) AS date, par_customer.idcustomer, to_char(vlt_driver_workday.workday_date::timestamp with time zone, 'yyyy-mm'::text) AS to_char
   FROM vlt_driver_workday
   JOIN vlt_driver ON vlt_driver.iddriver = vlt_driver_workday.iddriver
   JOIN par_customer ON vlt_driver.parcust_id = par_customer.idcustomer
  ORDER BY to_char(vlt_driver_workday.workday_date::timestamp with time zone, 'yyyy-mm'::text) DESC;
CREATE OR REPLACE VIEW public.vlt_grouped_transactions_vw (
    address,
    connected,
    driver,
    description,
    hour,
    iddevice,
    idtransaction,
    idtransaction_group,
    is_connected,
    latitude,
    longitude,
    online,
    speed,
    start_date,
    vehicle_code,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature)
AS
 SELECT vlt_transaction.address,
        CASE
            WHEN vlt_transaction.connected THEN 'Sim'::text
            ELSE 'Não'::text
        END AS connected, vlt_driver.driver, vlt_device.description, vlt_transaction.time_dif AS hour, vlt_transaction.iddevice, vlt_transaction.idtransaction, vlt_transaction.idtransaction_group, vlt_transaction.connected AS is_connected, vlt_transaction.latitude, vlt_transaction.longitude, vlt_transaction.online, vlt_transaction.speed, to_char(vlt_transaction.command_date, 'dd/mm/yyyy hh24:mi:ss'::text) AS start_date, vlt_device.vehicle_code, vlt_device_model.has_battery, vlt_device_model.has_analog_ports, vlt_device_model.has_digital_ports, vlt_device_model.has_tpms, vlt_device_model.has_fuel, vlt_device_model.has_temperature
   FROM vlt_transaction
   JOIN vlt_device ON vlt_device.iddevice = vlt_transaction.iddevice
   LEFT JOIN vlt_driver ON vlt_driver.iddriver = vlt_transaction.iddriver
   JOIN vlt_device_model ON vlt_device_model.iddevicemodel = vlt_device.iddevicemodel
  WHERE vlt_transaction.valid
  ORDER BY vlt_transaction.command_date;
CREATE OR REPLACE VIEW public.vlt_transaction_driver_vw (
    idtransactiondriver,
    iddriver,
    transaction_date,
    iddevice,
    idcustomer,
    driver,
    device)
AS
 SELECT td.idtransactiondriver, td.iddriver, td.transaction_date, td.iddevice, dev.idcustomer, d.driver, dev.vehicle_code AS device
   FROM vlt_device dev
   JOIN vlt_transaction_driver td ON td.iddevice = dev.iddevice
   JOIN vlt_driver d ON d.iddriver = td.iddriver
  WHERE dev.active
  ORDER BY td.transaction_date;

-- object recreation
ALTER TABLE public.vlt_driver_record
  DROP CONSTRAINT vlr_driver_record_fk3 RESTRICT;

ALTER TABLE public.vlt_driver_record
  ADD CONSTRAINT vlr_driver_record_fk3 FOREIGN KEY (idtransaction_start)
    REFERENCES public.vlt_transaction(idtransaction)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

ALTER TABLE public.vlt_firmware
  ALTER COLUMN file SET NOT NULL;

ALTER TABLE public.vlt_firmware
  ALTER COLUMN size SET NOT NULL;

ALTER TABLE public.vlt_firmware
  ENABLE TRIGGER vlt_firmware_update;

-- object recreation
ALTER TABLE public.vlt_fuel
  DROP CONSTRAINT foreign_key01 RESTRICT;

ALTER TABLE public.vlt_fuel
  ADD CONSTRAINT foreign_key01 FOREIGN KEY (idfuelvalue_last)
    REFERENCES public.vlt_fuel_value(idfuelvalue)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

DROP VIEW public.vlt_command_vw;
DROP VIEW public.vlt_command_status_vw;
ALTER TABLE public.vlt_model_command
  ALTER COLUMN command_text TYPE VARCHAR(80);
CREATE OR REPLACE VIEW public.vlt_command_status_vw (
    idmodelcommand,
    description,
    icon,
    command_text,
    iddevicemodel,
    btnclass,
    iddevice,
    has_parameter,
    parameter,
    access_level,
    message,
    command_date,
    buttoncolor,
    blocked,
    loading,
    sms)
AS
 SELECT vlt_model_command.idmodelcommand, vlt_model_command.description, vlt_model_command.icon, vlt_model_command.command_text, vlt_model_command.iddevicemodel, ( SELECT
                CASE
                    WHEN vlt_command.status::text = 'O'::text THEN 'btn-success'::text
                    WHEN vlt_command.status::text = 'X'::text THEN 'btn-danger'::text
                    WHEN vlt_command.status::text = 'A'::text THEN 'btn-info'::text
                    WHEN vlt_command.status::text = 'T'::text THEN 'btn-danger'::text
                    ELSE NULL::text
                END AS "case"
           FROM vlt_command
          WHERE vlt_command.idcommand = (( SELECT max(vlt_command.idcommand) AS max
                   FROM vlt_command
                  WHERE vlt_command.idmodelcommand = vlt_model_command.idmodelcommand AND vlt_command.iddevice = vlt_device.iddevice))) AS btnclass, vlt_device.iddevice, vlt_model_command.has_parameter, vlt_model_command.parameter, vlt_model_command.access_level, ( SELECT vlt_command.message
           FROM vlt_command
          WHERE vlt_command.idcommand = (( SELECT max(vlt_command.idcommand) AS max
                   FROM vlt_command
                  WHERE vlt_command.iddevice = vlt_device.iddevice AND vlt_command.idmodelcommand = vlt_model_command.idmodelcommand))) AS message, ( SELECT vlt_command.command_date
           FROM vlt_command
          WHERE vlt_command.idcommand = (( SELECT max(vlt_command.idcommand) AS max
                   FROM vlt_command
                  WHERE vlt_command.iddevice = vlt_device.iddevice AND vlt_command.idmodelcommand = vlt_model_command.idmodelcommand))) AS command_date, COALESCE(( SELECT
                CASE
                    WHEN vlt_command.status::text = 'O'::text THEN '#86b558'::text
                    WHEN vlt_command.status::text = 'X'::text THEN '#d3413b'::text
                    WHEN vlt_command.status::text = 'A'::text THEN '#68adde'::text
                    WHEN vlt_command.status::text = 'T'::text THEN '#d3413b'::text
                    ELSE '#68adde'::text
                END AS "case"
           FROM vlt_command
          WHERE vlt_command.idcommand = (( SELECT max(vlt_command.idcommand) AS max
                   FROM vlt_command
                  WHERE vlt_command.idmodelcommand = vlt_model_command.idmodelcommand AND vlt_command.iddevice = vlt_device.iddevice))), '#68adde'::text) AS buttoncolor,
        CASE
            WHEN (( SELECT count(*) AS count
               FROM vlt_command
              WHERE vlt_command.iddevice = vlt_device.iddevice AND (vlt_command.status::text = ANY (ARRAY['A'::text, 'E'::text])))) > 0 THEN true
            ELSE false
        END AS blocked, COALESCE(( SELECT
                CASE
                    WHEN vlt_command.status::text = 'E'::text THEN true
                    WHEN vlt_command.status::text = 'A'::text THEN true
                    ELSE false
                END AS "case"
           FROM vlt_command
          WHERE vlt_command.idcommand = (( SELECT max(vlt_command.idcommand) AS max
                   FROM vlt_command
                  WHERE vlt_command.idmodelcommand = vlt_model_command.idmodelcommand AND vlt_command.iddevice = vlt_device.iddevice))), false) AS loading, vlt_model_command.sms
   FROM vlt_model_command
   JOIN vlt_device ON vlt_device.iddevicemodel = vlt_model_command.iddevicemodel
   LEFT JOIN vlt_model_command_param ON vlt_model_command_param.idmodelcommand = vlt_model_command.idmodelcommand
  GROUP BY vlt_model_command.idmodelcommand, vlt_device.iddevice
  ORDER BY vlt_model_command.idmodelcommand;
CREATE OR REPLACE VIEW public.vlt_command_vw (
    iddevice,
    message,
    status,
    command_date,
    update_date,
    description,
    name,
    idmodelcommand)
AS
 SELECT vlt_command.iddevice, vlt_command.message, vlt_command.status, vlt_command.command_date, vlt_command.update_date, vlt_model_command.description, par_user.login AS name, vlt_model_command.idmodelcommand
   FROM vlt_command
   JOIN vlt_model_command ON vlt_model_command.idmodelcommand = vlt_command.idmodelcommand
   JOIN par_user ON par_user.iduser = vlt_command.iduser;

-- object recreation
ALTER TABLE public.vlt_model_command_param
  DROP CONSTRAINT check01 RESTRICT;

ALTER TABLE public.vlt_model_command_param
  ADD CONSTRAINT check01 CHECK ((data_type)::text = ANY ((ARRAY['T'::character varying, 'N'::character varying, 'D'::character varying, 'B'::character varying])::text[]));

ALTER TABLE public.vlt_model_command_param_value
  ALTER COLUMN value SET NOT NULL;

DROP VIEW public.vlt_seller_mc_vw;
ALTER TABLE public.vlt_monitoring_center
  ALTER COLUMN district TYPE VARCHAR(60);
CREATE OR REPLACE VIEW public.vlt_seller_mc_vw (
    idsellermc,
    idseller,
    idmonitoringcenter,
    name)
AS
 SELECT smc.idsellermc, smc.idseller, smc.idmonitoringcenter, mc.name
   FROM vlt_seller_mc smc
   JOIN vlt_monitoring_center mc ON mc.idmonitoringcenter = smc.idmonitoringcenter;

DROP VIEW public.vlt_seller_mc_vw;
ALTER TABLE public.vlt_monitoring_center
  ALTER COLUMN city TYPE VARCHAR(60);
CREATE OR REPLACE VIEW public.vlt_seller_mc_vw (
    idsellermc,
    idseller,
    idmonitoringcenter,
    name)
AS
 SELECT smc.idsellermc, smc.idseller, smc.idmonitoringcenter, mc.name
   FROM vlt_seller_mc smc
   JOIN vlt_monitoring_center mc ON mc.idmonitoringcenter = smc.idmonitoringcenter;

DROP VIEW public.vlt_refuel_vw;
DROP VIEW public.mce_refuel_vw;
DROP VIEW public.mce_refuel_station_vw;
ALTER TABLE public.vlt_refuel_station
  ALTER COLUMN name SET NOT NULL;
CREATE OR REPLACE VIEW public.mce_refuel_station_vw (
    idrefuelstation,
    code,
    name,
    idinterestpoint,
    idcustomer,
    idmonitoringcenter)
AS
 SELECT vlt_refuel_station.idrefuelstation, vlt_refuel_station.code, vlt_refuel_station.name, vlt_refuel_station.idinterestpoint, vlt_refuel_station.idcustomer, vlt_device_mc.idmonitoringcenter
   FROM vlt_refuel_station
   JOIN vlt_device ON vlt_device.idcustomer = vlt_refuel_station.idcustomer
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice
  GROUP BY vlt_refuel_station.idrefuelstation, vlt_device_mc.idmonitoringcenter
  ORDER BY vlt_device_mc.idmonitoringcenter;
CREATE OR REPLACE VIEW public.mce_refuel_vw (
    idrefuel,
    idrefuelstation,
    refuel_code,
    refuel_date,
    refuel_liters,
    refuel_value,
    idtransaction,
    iddevice,
    name,
    vehicle_code,
    per_liter,
    idmonitoringcenter,
    idcustomer)
AS
 SELECT vlt_refuel.idrefuel, vlt_refuel.idrefuelstation, vlt_refuel.refuel_code, vlt_refuel.refuel_date, vlt_refuel.refuel_liters, vlt_refuel.refuel_value, vlt_refuel.idtransaction, vlt_refuel.iddevice, vlt_refuel_station.name, vlt_device.vehicle_code, vlt_refuel.refuel_value / vlt_refuel.refuel_liters AS per_liter, vlt_device_mc.idmonitoringcenter, vlt_device.idcustomer
   FROM vlt_refuel
   JOIN vlt_refuel_station ON vlt_refuel_station.idrefuelstation = vlt_refuel.idrefuelstation
   JOIN vlt_device ON vlt_device.iddevice = vlt_refuel.iddevice
   JOIN vlt_device_mc ON vlt_device_mc.iddevice = vlt_device.iddevice
  ORDER BY vlt_device.vehicle_code;
CREATE OR REPLACE VIEW public.vlt_refuel_vw (
    idrefuel,
    idrefuelstation,
    refuel_code,
    refuel_date,
    refuel_liters,
    refuel_value,
    idtransaction,
    iddevice,
    name,
    vehicle_code,
    per_liter,
    idcustomer)
AS
 SELECT vlt_refuel.idrefuel, vlt_refuel.idrefuelstation, vlt_refuel.refuel_code, vlt_refuel.refuel_date, vlt_refuel.refuel_liters, vlt_refuel.refuel_value, vlt_refuel.idtransaction, vlt_refuel.iddevice, vlt_refuel_station.name, vlt_device.vehicle_code, vlt_refuel.refuel_value /
        CASE
            WHEN vlt_refuel.refuel_liters = 0::numeric THEN 1::numeric
            ELSE vlt_refuel.refuel_liters
        END AS per_liter, vlt_device.idcustomer
   FROM vlt_refuel
   JOIN vlt_refuel_station ON vlt_refuel_station.idrefuelstation = vlt_refuel.idrefuelstation
   JOIN vlt_device ON vlt_device.iddevice = vlt_refuel.iddevice
  ORDER BY vlt_device.vehicle_code;

CREATE TABLE public.vlt_sim (
  idsim BIGINT DEFAULT nextval('vlt_sin_idsin_seq'::regclass) NOT NULL,
  idseller BIGINT NOT NULL,
  code_sim VARCHAR(100) NOT NULL,
  desc_phone VARCHAR(15) NOT NULL,
  sim_search TSVECTOR,
  CONSTRAINT idsin_pkey PRIMARY KEY(idsim),
  CONSTRAINT foreign_key01 FOREIGN KEY (idseller)
    REFERENCES public.vlt_seller(idseller)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE INDEX vlt_sim_idx01 ON public.vlt_sim
  USING btree (idseller);

CREATE TRIGGER vlt_sim_trg AFTER INSERT OR UPDATE
ON public.vlt_sim FOR EACH ROW
EXECUTE PROCEDURE public.vlt_sim_fnc();

CREATE TABLE public.vlt_sim_history (
  idsimhistory BIGINT DEFAULT nextval('vlt_sim_history_idsimhistory_seq'::regclass) NOT NULL,
  idsim BIGINT NOT NULL,
  idseller BIGINT,
  iddevice BIGINT,
  history_date TIMESTAMP WITHOUT TIME ZONE DEFAULT now() NOT NULL,
  iduser BIGINT,
  status CHAR(1) DEFAULT 'I'::bpchar NOT NULL,
  CONSTRAINT vlt_sim_history_pkey PRIMARY KEY(idsimhistory),
  CONSTRAINT vlt_sim_history_chk CHECK (status = ANY (ARRAY['A'::bpchar, 'I'::bpchar, 'B'::bpchar])),
  CONSTRAINT foreign_key01 FOREIGN KEY (idsim)
    REFERENCES public.vlt_sim(idsim)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT foreign_key02 FOREIGN KEY (idseller)
    REFERENCES public.vlt_seller(idseller)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT foreign_key03 FOREIGN KEY (iddevice)
    REFERENCES public.vlt_device(iddevice)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE,
  CONSTRAINT foreign_key04 FOREIGN KEY (iduser)
    REFERENCES public.par_user(iduser)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
    NOT DEFERRABLE
) WITHOUT OIDS;

CREATE INDEX vlt_sim_history_idx01 ON public.vlt_sim_history
  USING btree (idsim);

CREATE INDEX vlt_sim_history_idx02 ON public.vlt_sim_history
  USING btree (iddevice);

-- object recreation
ALTER TABLE public.vlt_transaction
  DROP CONSTRAINT foreign_key04 RESTRICT;

ALTER TABLE public.vlt_transaction
  ADD CONSTRAINT foreign_key04 FOREIGN KEY (idtransaction_group)
    REFERENCES public.vlt_transaction(idtransaction)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
    NOT DEFERRABLE;

-- object recreation
DROP INDEX public.vlt_transaction_index03;

CREATE INDEX vlt_transaction_index03 ON public.vlt_transaction
  USING btree (iddevice, valid, idtransaction)
  WITH (fillfactor = 100);

ALTER TABLE public.adm_transaction_vw
  OWNER TO pgsql;

CREATE OR REPLACE VIEW public.fin_billet_vw (
    idbilletitem,
    code,
    item_model_billet,
    value,
    idbillet)
AS
 SELECT bi.idbilletitem, imb.code, imb.item_model_billet, bi.value, bil.idbillet
   FROM fin_item_model_billet imb, fin_model_billet mb, fin_bank b, fin_billet_item bi, fin_billet bil
  WHERE imb.idmodelbillet = mb.idmodelbillet AND b.idbank = mb.idbank AND imb.iditemmodelbillet = bi.iditemmodelbillet AND bil.idbillet = bi.idbillet;

ALTER TABLE public.mce_customer_vw
  OWNER TO pgsql;

ALTER TABLE public.mce_refuel_station_vw
  OWNER TO pgsql;

ALTER TABLE public.par_etiqueta_vw
  OWNER TO pgsql;

CREATE OR REPLACE VIEW public.par_message_vw (
    idmessage,
    message_date,
    iduser,
    name,
    title,
    message,
    read_date,
    is_read,
    priority,
    from_iddevice,
    deviceorigin,
    to_iddevice,
    devicereceiver,
    from_iduser,
    messageorigin,
    messagereceiver)
AS
 SELECT par_message.idmessage, par_message.message_date,
        CASE
            WHEN par_message.iduser IS NOT NULL THEN par_message.iduser
            WHEN par_message.to_idcustomer IS NOT NULL THEN ( SELECT par_user.iduser
               FROM par_user
              WHERE par_user.login::text = ((( SELECT par_customer.email
                       FROM par_customer
                      WHERE par_customer.idcustomer = par_message.to_idcustomer))::text))
            ELSE NULL::bigint
        END AS iduser, touser.name, par_message.title, par_message.message, par_message.read_date, par_message.is_read, par_message.priority, par_message.from_iddevice, fromdevice.vehicle_code AS deviceorigin, par_message.to_iddevice, todevice.vehicle_code AS devicereceiver, par_message.from_iduser,
        CASE
            WHEN par_message.origin::text = 'S'::text THEN 'Sistema'::character varying
            WHEN par_message.origin::text = 'U'::text THEN fromuser.name
            WHEN par_message.origin::text = 'D'::text THEN fromdevice.vehicle_code
            ELSE NULL::character varying
        END AS messageorigin,
        CASE
            WHEN par_message.iduser IS NOT NULL THEN touser.name
            WHEN par_message.to_iddevice IS NOT NULL THEN todevice.vehicle_code
            ELSE NULL::character varying
        END AS messagereceiver
   FROM par_message
   LEFT JOIN par_user fromuser ON fromuser.iduser = par_message.from_iduser
   LEFT JOIN par_user touser ON touser.iduser = par_message.iduser
   LEFT JOIN vlt_device fromdevice ON fromdevice.iddevice = par_message.from_iddevice
   LEFT JOIN vlt_device todevice ON todevice.iddevice = par_message.to_iddevice
   LEFT JOIN par_customer ON par_customer.idcustomer = par_message.to_idcustomer
  ORDER BY par_message.idmessage DESC;

ALTER TABLE public.par_message_vw
  OWNER TO pgsql;

CREATE OR REPLACE VIEW public.par_sns_vw (
    idsns,
    desc_token,
    iduser,
    idcustomer,
    idseller,
    "user")
AS
 SELECT sns.idsns, sns.desc_token, usr.iduser, prof.parcust_id AS idcustomer, cus.idseller, usr.name AS "user"
   FROM par_sns sns, par_user usr, rba_profile prof, par_customer cus
  WHERE sns.iduser = usr.iduser AND prof.id = usr.rbaprof_id AND cus.idcustomer = prof.parcust_id;

ALTER TABLE public.rba_profile_routes_vw
  RENAME COLUMN id TO idroute;

ALTER TABLE public.rba_profile_routes_vw
  RENAME COLUMN idroute TO desc_route_name;

ALTER TABLE public.rba_profile_routes_vw
  RENAME COLUMN desc_route_name TO has_access;

ALTER TABLE public.rba_profile_routes_vw
  RENAME COLUMN has_access TO id;

CREATE OR REPLACE VIEW public.rba_profile_routes_vw (
    idroute,
    desc_route_name,
    has_access,
    id)
AS
 SELECT r.id AS idroute, r.desc_route_name, (EXISTS ( SELECT NULL::unknown AS unknown
           FROM rba_profile_routes
          WHERE rba_profile_routes.rbarout_id = r.id AND rba_profile_routes.rbaprof_id = p.id)) AS has_access, p.id
   FROM rba_routes r, rba_profile p
  WHERE (r.indr_systype::text =
        CASE
            WHEN p.parcust_id IS NOT NULL THEN 'CUS'::text
            WHEN p.idseller IS NOT NULL THEN 'SEL'::text
            WHEN p.idmonitoringcenter IS NOT NULL THEN 'MCE'::text
            ELSE 'ADM'::text
        END OR r.indr_systype IS NULL) AND NOT r.basic
  ORDER BY r.desc_route_name;

ALTER TABLE public.rba_profile_routes_vw
  OWNER TO pgsql;

ALTER TABLE public.rpt_speed_vw
  OWNER TO pgsql;

CREATE OR REPLACE VIEW public.rpt_worked_hours_driver_vw (
    vehicle,
    driver,
    data_trabalhada,
    inicio,
    termino,
    horas,
    iddriver)
AS
 SELECT ( SELECT vlt_device.vehicle_code
           FROM vlt_device
          WHERE vlt_device.iddevice = vlt_driver_record.iddevice) AS vehicle, vlt_driver.driver, to_date(to_char(tend.command_date, 'dd/mm/yyyy'::text), 'dd/mm/yyyy'::text) AS data_trabalhada, tstart.command_date AS inicio, tend.command_date AS termino, tend.command_date - tstart.command_date AS horas, vlt_driver.iddriver
   FROM vlt_driver_record
   JOIN vlt_driver ON vlt_driver.iddriver = vlt_driver_record.iddriver
   JOIN vlt_transaction tstart ON tstart.idtransaction = vlt_driver_record.idtransaction_start
   JOIN vlt_transaction tend ON tend.idtransaction = vlt_driver_record.idtransaction_end
  GROUP BY vlt_driver_record.iddevice, vlt_driver.iddriver, vlt_driver.driver, tstart.command_date, tend.command_date
  ORDER BY tstart.command_date, tend.command_date;

ALTER TABLE public.vlt_alert_center_vw
  OWNER TO pgsql;

CREATE OR REPLACE VIEW public.vlt_command_vw (
    iddevice,
    message,
    status,
    command_date,
    update_date,
    description,
    name,
    idmodelcommand)
AS
 SELECT vlt_command.iddevice, vlt_command.message, vlt_command.status, vlt_command.command_date, vlt_command.update_date, vlt_model_command.description, par_user.name, vlt_model_command.idmodelcommand
   FROM vlt_command
   JOIN vlt_model_command ON vlt_model_command.idmodelcommand = vlt_command.idmodelcommand
   JOIN par_user ON par_user.iduser = vlt_command.iduser;

ALTER TABLE public.vlt_command_vw
  OWNER TO pgsql;

ALTER TABLE public.vlt_console_update_vw
  OWNER TO pgsql;

ALTER TABLE public.vlt_device_search_vw
  OWNER TO pgsql;

CREATE OR REPLACE VIEW public.vlt_device_vw (
    iddevice,
    description,
    latitude,
    longitude,
    vehicle_code,
    idcustomer,
    code,
    device_search,
    has_battery,
    has_analog_ports,
    has_digital_ports,
    has_tpms,
    has_fuel,
    has_temperature,
    connected,
    is_connected,
    speed,
    command_date,
    start_date,
    driver,
    address,
    idtransaction,
    online,
    record_type,
    has_alert,
    active,
    odometer)
AS
 SELECT q.iddevice, q.description, q.latitude, q.longitude, q.vehicle_code, q.idcustomer, q.code, q.device_search, q.has_battery, q.has_analog_ports, q.has_digital_ports, q.has_tpms, q.has_fuel, q.has_temperature, q.connected, q.is_connected, q.speed, q.command_date, q.start_date, q.driver, q.address, q.idtransaction, q.online, q.record_type, q.has_alert, q.active, q.odometer
   FROM ( SELECT dev.iddevice, dev.description, tt.latitude, tt.longitude, dev.vehicle_code, dev.idcustomer, dev.code, dev.device_search, dm.has_battery, dm.has_analog_ports, dm.has_digital_ports, dm.has_tpms, dm.has_fuel, dm.has_temperature,
                CASE
                    WHEN tt.connected THEN 'Sim'::text
                    ELSE 'Não'::text
                END AS connected, tt.connected AS is_connected, tt.speed, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS command_date, to_char(tt.command_date, 'dd/mm/yyyy hh24:mi:ss'::text)::character varying AS start_date, ( SELECT drv.driver
                   FROM vlt_driver drv
                  WHERE drv.iddriver = tt.iddriver) AS driver, tt.address, tt.idtransaction, tt.online, rt.description AS record_type, vlt_device_has_alert_fnc(dev.iddevice) AS has_alert, dev.active, round(tt.odometer::numeric / 1000::numeric, 1) AS odometer
           FROM ( SELECT vlt_device.iddevice, vlt_last_tran_dev_fnc(vlt_device.iddevice) AS idtransaction, vlt_device.idcustomer
                   FROM vlt_device) tbl
      JOIN vlt_transaction tt ON tt.idtransaction = tbl.idtransaction
   JOIN vlt_device dev ON dev.iddevice = tbl.iddevice
   JOIN vlt_device_model dm ON dm.iddevicemodel = dev.iddevicemodel
   JOIN vlt_record_type rt ON rt.idrecordtype = tt.idrecordtype) q;

ALTER TABLE public.vlt_port_status_vw
  OWNER TO pgsql;

CREATE OR REPLACE VIEW public.vlt_refuel_vw (
    idrefuel,
    idrefuelstation,
    refuel_code,
    refuel_date,
    refuel_liters,
    refuel_value,
    idtransaction,
    iddevice,
    name,
    vehicle_code,
    per_liter,
    idcustomer)
AS
 SELECT vlt_refuel.idrefuel, vlt_refuel.idrefuelstation, vlt_refuel.refuel_code, to_char(vlt_refuel.refuel_date, 'dd/mm/yyyy HH:mm:ss'::text) AS refuel_date, vlt_refuel.refuel_liters, vlt_refuel.refuel_value, vlt_refuel.idtransaction, vlt_refuel.iddevice, vlt_refuel_station.name, vlt_device.vehicle_code, vlt_refuel.refuel_value / vlt_refuel.refuel_liters AS per_liter, vlt_device.idcustomer
   FROM vlt_refuel
   JOIN vlt_refuel_station ON vlt_refuel_station.idrefuelstation = vlt_refuel.idrefuelstation
   JOIN vlt_device ON vlt_device.iddevice = vlt_refuel.iddevice
  ORDER BY vlt_device.vehicle_code;

ALTER TABLE public.vlt_seller_mc_vw
  OWNER TO pgsql;

CREATE OR REPLACE VIEW public.vlt_seller_vehicle_vw (
    iddevice,
    id,
    customer,
    idcustomer,
    vehicle_code)
AS
 SELECT vlt_device.iddevice, par_customer.idseller AS id, par_customer.customer, vlt_device.idcustomer, vlt_device.vehicle_code
   FROM vlt_device, par_customer
  WHERE vlt_device.idcustomer = par_customer.idcustomer;

CREATE OR REPLACE VIEW public.vlt_sim_history_vw (
    idsimhistory,
    history_date,
    status,
    vehicle_code,
    login,
    idsim,
    iddevice)
AS
 SELECT vlt_sim_history.idsimhistory, vlt_sim_history.history_date, vlt_sim_history.status, vlt_device.vehicle_code, par_user.login, vlt_sim_history.idsim, vlt_sim_history.iddevice
   FROM vlt_sim_history
   LEFT JOIN par_user ON par_user.iduser = vlt_sim_history.iduser
   JOIN vlt_sim ON vlt_sim.idsim = vlt_sim_history.idsim
   LEFT JOIN vlt_device ON vlt_device.iddevice = vlt_sim_history.iddevice
  ORDER BY vlt_sim_history.history_date DESC;

ALTER TABLE public.vlt_transaction_vw
  OWNER TO pgsql;