--
-- Script was generated by Devart dbForge Studio for MySQL, Version 9.2.105.0
-- Product home page: http://www.devart.com/dbforge/mysql/studio
-- Script date 15/09/2025 12:03:01
-- Client version: 4.1
--

-- 
-- Disable foreign keys
-- 

-- 
-- Set SQL mode
-- 

-- 
-- Set character set the client will use to send SQL statements to the server
--

--
-- Set default database
--
USE mobile;

--
-- Drop table `coletas`
--

--
-- Drop table `concoletor`
--

--
-- Drop table `entregas`
--

--
-- Drop table `impressoes`
--

--
-- Drop table `impressoras`
--

--
-- Drop table `inventario`
--

--
-- Drop table `localizacao`
--

--
-- Drop table `logs`
--

--
-- Drop table `loteinventario`
--

--
-- Drop table `ocorrenciascol`
--

--
-- Drop table `ocorrenciasent`
--

--
-- Drop table `usuarios`
--

--
-- Drop table `vlnotainventario`
--

--
-- Drop event `ExecLimpeza`
--
DROP EVENT IF EXISTS ExecLimpeza;

--
-- Drop procedure `limpa`
--
DROP PROCEDURE IF EXISTS limpa;

--
-- Set default database
--
USE mobile;

DELIMITER $$

--
-- Create procedure `limpa`
--
CREATE
DEFINER = 'anjotec'@'%'
PROCEDURE limpa ()
BEGIN
  UPDATE entregas_old2
  SET status = 5,
      usudelete = 'auto',
      dtdelete = CURRENT_TIMESTAMP()
  WHERE status <> 5
  AND statusmobile <= 2;
  COMMIT;
  UPDATE coletas_old
  SET status = 5,
      usudelete = 'auto',
      dtdelete = CURRENT_TIMESTAMP()
  WHERE status <> 5
  AND statusmobile <= 2;
  COMMIT;
END
$$

--
-- Create event `ExecLimpeza`
--
CREATE 
	DEFINER = 'anjotec'@'%'
EVENT ExecLimpeza
	ON SCHEDULE EVERY '1' DAY
	STARTS '2024-11-09 23:00:00'
	DO 
BEGIN
CALL mobile.limpa();
END
$$

ALTER 
	DEFINER = 'anjotec'@'%'
EVENT ExecLimpeza
	ENABLE
$$

DELIMITER ;

--
-- Create table `vlnotainventario`
--
CREATE TABLE vlnotainventario (
  codigo int(11) NOT NULL AUTO_INCREMENT,
  codinventario int(11) NOT NULL,
  volume int(11) NOT NULL,
  codlote int(11) DEFAULT NULL,
  PRIMARY KEY (codigo)
)
ENGINE = INNODB,
AUTO_INCREMENT = 65240,
AVG_ROW_LENGTH = 40,
CHARACTER SET latin1,
COLLATE latin1_swedish_ci,
ROW_FORMAT = COMPACT;

--
-- Create table `usuarios`
--
CREATE TABLE usuarios (
  codigo int(11) NOT NULL AUTO_INCREMENT,
  usuario varchar(200) NOT NULL,
  senha varchar(200) NOT NULL,
  nome varchar(200) NOT NULL,
  placa varchar(20) NOT NULL,
  inativo int(11) DEFAULT NULL,
  ultimasinc datetime DEFAULT NULL,
  gerencia int(11) NOT NULL,
  filial varchar(200) NOT NULL,
  master int(11) NOT NULL,
  cgc varchar(14) DEFAULT NULL,
  limpa int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (codigo)
)
ENGINE = INNODB,
AUTO_INCREMENT = 494,
AVG_ROW_LENGTH = 156,
CHARACTER SET latin1,
COLLATE latin1_swedish_ci,
ROW_FORMAT = COMPACT;

--
-- Create table `ocorrenciasent`
--
CREATE TABLE ocorrenciasent (
  codigo int(11) NOT NULL AUTO_INCREMENT,
  descricao varchar(300) NOT NULL,
  codcliente varchar(100) NOT NULL,
  imagem int(11) NOT NULL,
  observacao int(11) NOT NULL,
  nome int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (codigo)
)
ENGINE = INNODB,
AUTO_INCREMENT = 9,
AVG_ROW_LENGTH = 2048,
CHARACTER SET latin1,
COLLATE latin1_swedish_ci,
ROW_FORMAT = COMPACT;

--
-- Create table `ocorrenciascol`
--
CREATE TABLE ocorrenciascol (
  codigo int(11) NOT NULL AUTO_INCREMENT,
  descricao varchar(200) NOT NULL,
  imagem int(11) NOT NULL,
  observacao int(11) NOT NULL,
  codcliente varchar(80) NOT NULL,
  PRIMARY KEY (codigo)
)
ENGINE = INNODB,
AUTO_INCREMENT = 6,
AVG_ROW_LENGTH = 3276,
CHARACTER SET latin1,
COLLATE latin1_swedish_ci,
ROW_FORMAT = COMPACT;

--
-- Create table `loteinventario`
--
CREATE TABLE loteinventario (
  codigo int(11) NOT NULL AUTO_INCREMENT,
  filial varchar(3) NOT NULL,
  dtabertura timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  userabertura varchar(100) NOT NULL,
  userencerramento varchar(100) DEFAULT NULL,
  dtencerramento datetime DEFAULT NULL,
  status int(11) DEFAULT 0,
  PRIMARY KEY (codigo)
)
ENGINE = INNODB,
AUTO_INCREMENT = 231,
AVG_ROW_LENGTH = 16384,
CHARACTER SET latin1,
COLLATE latin1_swedish_ci,
ROW_FORMAT = COMPACT;

--
-- Create table `logs`
--
CREATE TABLE logs (
  codigo int(11) NOT NULL AUTO_INCREMENT,
  tabela varchar(100) NOT NULL,
  tipo varchar(2) NOT NULL,
  movimento text NOT NULL,
  dtmovimento datetime NOT NULL,
  usuario varchar(100) NOT NULL,
  PRIMARY KEY (codigo)
)
ENGINE = INNODB,
AUTO_INCREMENT = 2668,
AVG_ROW_LENGTH = 250,
CHARACTER SET latin1,
COLLATE latin1_swedish_ci,
ROW_FORMAT = COMPACT;

--
-- Create table `localizacao`
--
CREATE TABLE localizacao (
  codigo int(11) NOT NULL AUTO_INCREMENT,
  placa varchar(200) NOT NULL,
  latitude varchar(200) NOT NULL,
  longitude varchar(200) NOT NULL,
  dataatualizacao datetime NOT NULL,
  PRIMARY KEY (codigo)
)
ENGINE = INNODB,
AUTO_INCREMENT = 3369960,
AVG_ROW_LENGTH = 57,
CHARACTER SET latin1,
COLLATE latin1_swedish_ci,
ROW_FORMAT = COMPACT;

--
-- Create table `inventario`
--
CREATE TABLE inventario (
  codigo int(10) NOT NULL AUTO_INCREMENT,
  nota varchar(9) NOT NULL,
  sernota varchar(3) NOT NULL,
  remnota varchar(15) NOT NULL,
  cte varchar(9) NOT NULL,
  sercte varchar(3) NOT NULL,
  filcte varchar(3) NOT NULL,
  descgc varchar(15) NOT NULL,
  peso decimal(10, 2) NOT NULL,
  ciddestino varchar(100) NOT NULL,
  fildestino varchar(3) NOT NULL,
  dtmanif date NOT NULL,
  filmanif varchar(3) NOT NULL,
  manifesto varchar(9) NOT NULL,
  sermanif varchar(3) NOT NULL,
  filbo varchar(3) DEFAULT NULL,
  bo varchar(9) DEFAULT NULL,
  usuario varchar(100) NOT NULL,
  volumes int(11) NOT NULL,
  dtleitura timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  codlote int(11) DEFAULT NULL,
  leituracte char(10) DEFAULT 'falta',
  difvolscte int(11) DEFAULT NULL,
  PRIMARY KEY (codigo)
)
ENGINE = INNODB,
AUTO_INCREMENT = 11380,
AVG_ROW_LENGTH = 227,
CHARACTER SET latin1,
COLLATE latin1_swedish_ci,
ROW_FORMAT = COMPACT;

--
-- Create table `impressoras`
--
CREATE TABLE impressoras (
  codigo int(11) NOT NULL AUTO_INCREMENT,
  filial varchar(20) NOT NULL,
  impressora varchar(200) NOT NULL,
  tipo varbinary(25) DEFAULT NULL,
  PRIMARY KEY (codigo)
)
ENGINE = INNODB,
AUTO_INCREMENT = 25,
AVG_ROW_LENGTH = 1092,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
ROW_FORMAT = COMPACT;

--
-- Create table `impressoes`
--
CREATE TABLE impressoes (
  codigo int(11) NOT NULL AUTO_INCREMENT,
  tipo varchar(200) NOT NULL,
  conteudo varchar(200) NOT NULL,
  impresso int(11) NOT NULL DEFAULT 0,
  filial varchar(20) NOT NULL,
  usuario varchar(50) DEFAULT NULL,
  dtimpressao timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  erro varchar(100) DEFAULT NULL,
  volumes int(11) DEFAULT 0,
  PRIMARY KEY (codigo)
)
ENGINE = INNODB,
AUTO_INCREMENT = 64132,
AVG_ROW_LENGTH = 113,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
ROW_FORMAT = COMPACT;

--
-- Create table `entregas`
--
CREATE TABLE entregas (
  codigo int(11) NOT NULL AUTO_INCREMENT,
  filial varchar(100) NOT NULL,
  cte varchar(100) NOT NULL,
  data date NOT NULL,
  horalimite time DEFAULT NULL,
  cnpj varchar(15) NOT NULL,
  nome varchar(200) NOT NULL,
  estado varchar(200) NOT NULL,
  cidade varchar(200) NOT NULL,
  bairro varchar(200) NOT NULL,
  endereco varchar(200) NOT NULL,
  cep varchar(100) NOT NULL,
  latitude varchar(100) DEFAULT NULL,
  longitude varchar(100) DEFAULT NULL,
  peso decimal(14, 2) NOT NULL,
  volumes int(11) NOT NULL,
  status int(11) NOT NULL,
  statusmobile int(11) NOT NULL,
  ocorrencia varchar(100) DEFAULT NULL,
  observacao varchar(300) DEFAULT NULL,
  imagemcanc longblob DEFAULT NULL,
  assinatura longblob DEFAULT NULL,
  chavenota varchar(40) DEFAULT NULL,
  placa varchar(40) NOT NULL,
  datamovimento date DEFAULT NULL,
  horamovimento time DEFAULT NULL,
  statuscliente int(11) DEFAULT NULL,
  tipo varchar(100) DEFAULT NULL,
  manifesto varchar(100) DEFAULT NULL,
  filmanifesto varchar(100) DEFAULT NULL,
  serieman varchar(50) DEFAULT NULL,
  nomerecebedor varchar(200) DEFAULT NULL,
  docrecebedor varchar(50) DEFAULT NULL,
  usuario varchar(100) DEFAULT NULL,
  imagem longblob DEFAULT NULL,
  dtreccliente datetime DEFAULT NULL,
  dtreccelular datetime DEFAULT NULL,
  latend varchar(100) DEFAULT NULL,
  longend varchar(100) DEFAULT NULL,
  usudelete varchar(100) DEFAULT NULL,
  dtdelete datetime DEFAULT NULL,
  usuimporta varchar(100) DEFAULT NULL,
  expimagem char(1) DEFAULT 'N',
  enviaimgcli char(1) NOT NULL DEFAULT 'N',
  explatlong char(1) DEFAULT 'N',
  PRIMARY KEY (codigo)
)
ENGINE = INNODB,
AUTO_INCREMENT = 288118,
AVG_ROW_LENGTH = 327,
CHARACTER SET latin1,
COLLATE latin1_swedish_ci,
ROW_FORMAT = COMPACT;

--
-- Create index `IDX_entregas_cidade` on table `entregas`
--
ALTER TABLE entregas
ADD INDEX IDX_entregas_cidade (cidade (8));

--
-- Create index `IDX_entregas_placa` on table `entregas`
--
ALTER TABLE entregas
ADD INDEX IDX_entregas_placa (placa (3));

--
-- Create table `concoletor`
--
CREATE TABLE concoletor (
  codigo int(11) NOT NULL AUTO_INCREMENT,
  servidor varchar(200) NOT NULL,
  porta varchar(20) NOT NULL,
  usuario varchar(200) NOT NULL,
  senha varchar(200) NOT NULL,
  PRIMARY KEY (codigo)
)
ENGINE = INNODB,
CHARACTER SET latin1,
COLLATE latin1_swedish_ci,
ROW_FORMAT = COMPACT;

--
-- Create table `coletas`
--
CREATE TABLE coletas (
  codigo int(11) NOT NULL AUTO_INCREMENT,
  filial varchar(100) NOT NULL,
  coleta varchar(100) NOT NULL,
  data date DEFAULT NULL,
  horalimite time DEFAULT NULL,
  cnpj varchar(15) NOT NULL,
  nome varchar(200) NOT NULL,
  estado varchar(200) NOT NULL,
  cidade varchar(200) NOT NULL,
  bairro varchar(200) NOT NULL,
  endereco varchar(200) NOT NULL,
  cep varchar(100) NOT NULL,
  latitude varchar(100) DEFAULT NULL,
  longitude varchar(100) DEFAULT NULL,
  peso decimal(14, 2) NOT NULL,
  volumes int(11) NOT NULL,
  status int(11) NOT NULL,
  statusmobile int(11) NOT NULL,
  ocorrencia varchar(100) DEFAULT NULL,
  observacao varchar(300) DEFAULT NULL,
  imagemcanc longblob DEFAULT NULL,
  assinatura longblob DEFAULT NULL,
  chavenota varchar(40) DEFAULT NULL,
  placa varchar(40) NOT NULL,
  datamovimento date DEFAULT NULL,
  horamovimento time DEFAULT NULL,
  statuscliente int(11) DEFAULT NULL,
  usuario varchar(200) DEFAULT NULL,
  manfilial varchar(50) DEFAULT NULL,
  manmanife varchar(50) DEFAULT NULL,
  manserie varchar(50) DEFAULT NULL,
  dtreccliente datetime DEFAULT NULL,
  dtreccelular datetime DEFAULT NULL,
  longend varchar(100) DEFAULT NULL,
  latend varchar(100) DEFAULT NULL,
  usudelete varchar(100) DEFAULT NULL,
  dtdelete datetime DEFAULT NULL,
  usuimporta varchar(100) DEFAULT NULL,
  expimagem char(1) DEFAULT 'N',
  explatlong char(1) DEFAULT 'N',
  PRIMARY KEY (codigo)
)
ENGINE = INNODB,
AUTO_INCREMENT = 161670,
AVG_ROW_LENGTH = 259,
CHARACTER SET latin1,
COLLATE latin1_swedish_ci,
ROW_FORMAT = COMPACT;

-- 
-- Restore previous SQL mode
-- 

-- 
-- Enable foreign keys
-- 
