db_sandras.sql

-- MySQL Script generated by MySQL Workbench
-- Thu Jun 28 21:03:12 2018
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema sandras
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema sandras
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `sandras` DEFAULT CHARACTER SET utf8 ;
USE `sandras` ;

-- -----------------------------------------------------
-- Table `sandras`.`usuarios`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sandras`.`usuarios` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(20) NULL,
  `password` CHAR(60) NULL,
  `role` VARCHAR(20) NULL,
  `email` CHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sandras`.`clients`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sandras`.`clients` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `address` VARCHAR(45) NULL,
  `info` VARCHAR(45) NULL,
  `active` TINYINT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sandras`.`hr`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sandras`.`hr` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(80) NULL,
  `address` VARCHAR(120) NULL,
  `civil_status` VARCHAR(45) NULL,
  `tel` VARCHAR(45) NULL,
  `email` VARCHAR(45) NULL,
  `contact1` VARCHAR(45) NULL,
  `contact2` VARCHAR(45) NULL,
  `birthdate` DATE NULL,
  `over25` CHAR(5) NULL,
  `labor_status` VARCHAR(45) NULL,
  `position` VARCHAR(45) NULL,
  `type` VARCHAR(45) NULL,
  `shift` VARCHAR(45) NULL,
  `crew` INT NULL,
  `rate` DOUBLE NULL,
  `username` VARCHAR(20) NULL,
  `password` CHAR(60) NULL,
  `medical_card` VARCHAR(45) NULL,
  `mc_exp` DATETIME NULL,
  `drug_test` VARCHAR(45) NULL,
  `dt_exp` DATETIME NULL,
  `training` VARCHAR(45) NULL,
  `training_exp` DATETIME NULL,
  `license` VARCHAR(45) NULL,
  `license_exp` DATETIME NULL,
  `state` VARCHAR(45) NULL,
  `years_working` DATETIME NULL,
  `ssn` VARCHAR(45) NULL,
  `dependants` VARCHAR(10) NULL,
  `up` TINYINT NULL,
  `clients_id` INT UNSIGNED NOT NULL,
  `assigned` TINYINT NULL,
  `hire_date` DATETIME NULL,
  `dll_hr` DOUBLE NULL,
  `license_class` VARCHAR(45) NULL,
  `experience` DATETIME NULL,
  `payment_method` VARCHAR(45) NULL,
  `bank_account` VARCHAR(45) NULL,
  `routing_number` VARCHAR(45) NULL,
  `location` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_hr_clients1_idx` (`clients_id` ASC),
  CONSTRAINT `fk_hr_clients1`
    FOREIGN KEY (`clients_id`)
    REFERENCES `sandras`.`clients` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sandras`.`products`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sandras`.`products` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `clients_id` INT UNSIGNED NOT NULL,
  `active` TINYINT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_products_clients1_idx` (`clients_id` ASC),
  CONSTRAINT `fk_products_clients1`
    FOREIGN KEY (`clients_id`)
    REFERENCES `sandras`.`clients` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sandras`.`payroll_hr`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sandras`.`payroll_hr` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `wire_transfer` VARCHAR(45) NULL,
  `amount` DOUBLE NULL,
  `seconds_worked` DOUBLE NULL,
  `date` DATETIME NULL,
  `hr_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_payroll_hr_hr1_idx` (`hr_id` ASC),
  CONSTRAINT `fk_payroll_hr_hr1`
    FOREIGN KEY (`hr_id`)
    REFERENCES `sandras`.`hr` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sandras`.`locations`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sandras`.`locations` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `status` VARCHAR(45) NULL,
  `geolocation` VARCHAR(45) NULL,
  `start_date` DATETIME NULL,
  `end_date` DATETIME NULL,
  `clients_id` INT UNSIGNED NOT NULL,
  `active` TINYINT NULL,
  `last_date` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_locations_clients1_idx` (`clients_id` ASC),
  CONSTRAINT `fk_locations_clients1`
    FOREIGN KEY (`clients_id`)
    REFERENCES `sandras`.`clients` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sandras`.`tickets`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sandras`.`tickets` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tms` VARCHAR(45) NULL,
  `status` INT NULL,
  `substatus` INT NULL,
  `invoice_rate` DOUBLE NULL,
  `invoice_rate_currency` VARCHAR(10) NULL,
  `load_rate` DOUBLE NULL,
  `load_rate_currency` VARCHAR(45) NULL,
  `driver_rate` DOUBLE NULL,
  `driver_rate_currency` VARCHAR(45) NULL,
  `product` VARCHAR(45) NULL,
  `base` VARCHAR(45) NULL,
  `silo` VARCHAR(45) NULL,
  `po` VARCHAR(45) NULL,
  `facility` VARCHAR(45) NULL,
  `location` VARCHAR(45) NULL,
  `bol` VARCHAR(45) NULL,
  `sand_type` VARCHAR(45) NULL,
  `weight` DOUBLE NULL,
  `assign_date` DATETIME NULL,
  `completed_date` DATETIME NULL,
  `invoice_date` DATETIME NULL,
  `payrolled_date` DATETIME NULL,
  `starting_mi` INT NULL,
  `end_mi` INT NULL,
  `pick_date` DATETIME NULL,
  `drop_date` DATETIME NULL,
  `notes` VARCHAR(1000) NULL,
  `sr` VARCHAR(45) NULL,
  `hr_id` INT UNSIGNED NULL,
  `products_id` INT NULL,
  `clients_id` INT UNSIGNED NOT NULL,
  `truck` VARCHAR(45) NULL,
  `trailer` VARCHAR(45) NULL,
  `on_curse` TINYINT NULL,
  `born_date` DATETIME NULL,
  `payroll_hr_id` INT UNSIGNED NULL,
  `locations_id` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_tickets_hr1_idx` (`hr_id` ASC),
  INDEX `fk_tickets_clients1_idx` (`clients_id` ASC),
  INDEX `fk_tickets_products1_idx` (`products_id` ASC),
  INDEX `fk_tickets_payroll_hr1_idx` (`payroll_hr_id` ASC),
  INDEX `fk_tickets_locations1_idx` (`locations_id` ASC),
  CONSTRAINT `fk_tickets_hr1`
    FOREIGN KEY (`hr_id`)
    REFERENCES `sandras`.`hr` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_tickets_clients1`
    FOREIGN KEY (`clients_id`)
    REFERENCES `sandras`.`clients` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_tickets_products1`
    FOREIGN KEY (`products_id`)
    REFERENCES `sandras`.`products` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_tickets_payroll_hr1`
    FOREIGN KEY (`payroll_hr_id`)
    REFERENCES `sandras`.`payroll_hr` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_tickets_locations1`
    FOREIGN KEY (`locations_id`)
    REFERENCES `sandras`.`locations` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sandras`.`eventos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sandras`.`eventos` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `evento` VARCHAR(45) NULL,
  `longitude` VARCHAR(45) NULL,
  `latitude` VARCHAR(45) NULL,
  `notes` VARCHAR(120) NULL,
  `tickets_id` INT UNSIGNED NOT NULL,
  `date` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_eventos_tickets1_idx` (`tickets_id` ASC),
  CONSTRAINT `fk_eventos_tickets1`
    FOREIGN KEY (`tickets_id`)
    REFERENCES `sandras`.`tickets` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sandras`.`sand`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sandras`.`sand` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `clients_id` INT UNSIGNED NOT NULL,
  `active` TINYINT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_sand_clients1_idx` (`clients_id` ASC),
  CONSTRAINT `fk_sand_clients1`
    FOREIGN KEY (`clients_id`)
    REFERENCES `sandras`.`clients` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sandras`.`goals`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sandras`.`goals` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `lbs_goal` INT UNSIGNED NULL,
  `loads_goal` INT UNSIGNED NULL,
  `lbs_current` INT UNSIGNED NULL,
  `lads_current` INT UNSIGNED NULL,
  `locations_id` INT NOT NULL,
  `sand_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_goals_locations1_idx` (`locations_id` ASC),
  INDEX `fk_goals_sand1_idx` (`sand_id` ASC),
  CONSTRAINT `fk_goals_locations1`
    FOREIGN KEY (`locations_id`)
    REFERENCES `sandras`.`locations` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_goals_sand1`
    FOREIGN KEY (`sand_id`)
    REFERENCES `sandras`.`sand` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sandras`.`assets`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sandras`.`assets` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `type` VARCHAR(45) NULL,
  `plate` VARCHAR(45) NULL,
  `status` VARCHAR(45) NULL,
  `mi` INT UNSIGNED NULL,
  `mi_last_service` INT UNSIGNED NULL,
  `mtto_last` DATE NULL,
  `mtto_next` DATE NULL,
  `notes` VARCHAR(45) NULL,
  `clients_id` INT UNSIGNED NOT NULL,
  `up` TINYINT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_assets_clients1_idx` (`clients_id` ASC),
  CONSTRAINT `fk_assets_clients1`
    FOREIGN KEY (`clients_id`)
    REFERENCES `sandras`.`clients` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sandras`.`invoices`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sandras`.`invoices` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `payed_confirmation` VARCHAR(45) NULL,
  `payed_date` DATETIME NULL,
  `status` VARCHAR(45) NULL,
  `terms` VARCHAR(45) NULL,
  `notes` VARCHAR(120) NULL,
  `tickets_id` INT UNSIGNED NOT NULL,
  `printed` TINYINT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_invoices_tickets1_idx` (`tickets_id` ASC),
  CONSTRAINT `fk_invoices_tickets1`
    FOREIGN KEY (`tickets_id`)
    REFERENCES `sandras`.`tickets` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sandras`.`payroll`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sandras`.`payroll` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `wire_transfer` VARCHAR(45) NULL,
  `gross` DOUBLE NULL,
  `payrolled` DOUBLE NULL,
  `aproved` VARCHAR(45) NULL,
  `date` DATETIME NULL,
  `tickets_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_payroll_tickets1_idx` (`tickets_id` ASC),
  CONSTRAINT `fk_payroll_tickets1`
    FOREIGN KEY (`tickets_id`)
    REFERENCES `sandras`.`tickets` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sandras`.`clockin`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sandras`.`clockin` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_evento` VARCHAR(45) NULL,
  `in` DATETIME NULL,
  `out` DATETIME NULL,
  `paid` TINYINT NULL,
  `hr_id` INT UNSIGNED NOT NULL,
  `lat_in` VARCHAR(45) NULL,
  `long_in` VARCHAR(45) NULL,
  `lat_out` VARCHAR(45) NULL,
  `long_out` VARCHAR(45) NULL,
  `img_in` BLOB NULL,
  `img_in_name` VARCHAR(45) NULL,
  `img_out` BLOB NULL,
  `img_out_name` VARCHAR(45) NULL,
  `payroll_hr_id` INT UNSIGNED NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_clockin_hr1_idx` (`hr_id` ASC),
  INDEX `fk_clockin_payroll_hr1_idx` (`payroll_hr_id` ASC),
  CONSTRAINT `fk_clockin_hr1`
    FOREIGN KEY (`hr_id`)
    REFERENCES `sandras`.`hr` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_clockin_payroll_hr1`
    FOREIGN KEY (`payroll_hr_id`)
    REFERENCES `sandras`.`payroll_hr` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sandras`.`facilities`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sandras`.`facilities` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NULL,
  `active` TINYINT NULL,
  `clients_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_facilities_clients1_idx` (`clients_id` ASC),
  CONSTRAINT `fk_facilities_clients1`
    FOREIGN KEY (`clients_id`)
    REFERENCES `sandras`.`clients` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `sandras`.`scales_data`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `sandras`.`scales_data` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `sand_name1` VARCHAR(45) NULL,
  `sand_name2` VARCHAR(45) NULL,
  `sand_name3` VARCHAR(45) NULL,
  `sand_name4` VARCHAR(45) NULL,
  `sand_name5` VARCHAR(45) NULL,
  `sand_name6` VARCHAR(45) NULL,
  `weight1` VARCHAR(45) NULL,
  `weight2` VARCHAR(45) NULL,
  `weight3` VARCHAR(45) NULL,
  `weight4` VARCHAR(45) NULL,
  `weight5` VARCHAR(45) NULL,
  `weight6` VARCHAR(45) NULL,
  `status1` VARCHAR(45) NULL,
  `status2` VARCHAR(45) NULL,
  `status3` VARCHAR(45) NULL,
  `status4` VARCHAR(45) NULL,
  `status5` VARCHAR(45) NULL,
  `status6` VARCHAR(45) NULL,
  `porcent1` VARCHAR(45) NULL,
  `porcent2` VARCHAR(45) NULL,
  `porcent3` VARCHAR(45) NULL,
  `porcent4` VARCHAR(45) NULL,
  `porcent5` VARCHAR(45) NULL,
  `porcent6` VARCHAR(45) NULL,
  `locations_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_scales_data_locations1_idx` (`locations_id` ASC),
  CONSTRAINT `fk_scales_data_locations1`
    FOREIGN KEY (`locations_id`)
    REFERENCES `sandras`.`locations` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

USE `sandras` ;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;