sandras.sql

drop database sandras;

insert into clients (name) value ('HALLIBURTON');

insert into assets (name, type, clients_id, up) values ('TRAILER1', 'TRAILER', 1, TRUE);
insert into assets (name, type, clients_id, up) values ('TRUCK1', 'TRUCK', 1, TRUE);

insert into tickets (tms, status, substatus, location, facility, product, clients_id) values ('75738501', 1, "0", "TIMBERWOLF STATE UNIT 1702H,REEVES", "RANGELAND", "50-59", 1);
insert into tickets (tms, status, substatus, location, facility, product, clients_id) values ('75738501', 1, "1", "TIMBERWOLF STATE UNIT 1702H,REEVES", "RANGELAND", "50-59", 1);
insert into tickets (tms, status, substatus, location, facility, product, clients_id) values ('75846743', 1, "2", "TIMBERWOLF STATE UNIT 1702H,REEVES", "RANGELAND", "50-59", 1);
insert into tickets (tms, status, substatus, location, facility, product, clients_id) values ('75846744', 1, "3", "TIMBERWOLF STATE UNIT 1702H,REEVES", "RANGELAND", "50-59", 1);
insert into tickets (tms, status, substatus, location, facility, product, clients_id) values ('75846747', 1, "4", "TIMBERWOLF STATE UNIT 1702H,REEVES", "RANGELAND", "50-59", 1);
insert into tickets (tms, status, substatus, location, facility, product, clients_id) values ('75846748', 1, "5", "TIMBERWOLF STATE UNIT 1702H,REEVES", "RANGELAND", "50-59", 1);
insert into tickets (tms, status, substatus, location, facility, product, clients_id) values ('75846754', 1, "6", "TIMBERWOLF STATE UNIT 1702H,REEVES", "RANGELAND", "50-59", 1);


insert into hr (name, username, password, up, clients_id) values ('Daneil Rodriguez', 'arviso', 'arviso', 1, 1);
insert into hr (name, username, password, up, clients_id) values ('Rogelio', 'rogelio', 'rogelio', 1, 1);
insert into hr (name, username, password, up, clients_id) values ('Ricardo Estrada', 'estrada', 'estrada', 0, 1);

insert into products (name, clients_id) values ('0-49', 1);
insert into products (name, clients_id) values ('50-59', 1);
insert into products (name, clients_id) values ('60-69', 1);
insert into products (name, clients_id) values ('70-79', 1);
insert into products (name, clients_id) values ('80-89', 1);
insert into products (name, clients_id) values ('90-100', 1);
insert into products (name, clients_id) values ('100-109', 1);
insert into products (name, clients_id) values ('110-120', 1);

-- MySQL Script generated by MySQL Workbench
-- Fri Apr  6 15:39:32 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` INT 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,
  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`.`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,
  `price` DOUBLE NULL,
  `currency` VARCHAR(10) 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,
  `pro` VARCHAR(45) NULL,
  `equipment` VARCHAR(45) NULL,
  `notes` VARCHAR(120) 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,
  `load_rate` DOUBLE NULL,
  `load_rate_currency` VARCHAR(45) 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),
  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)
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`.`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,
  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`.`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,
  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,
  `day` DATETIME NULL,
  `in` TIME NULL,
  `out` TIME NULL,
  `hr_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_clockin_hr1_idx` (`hr_id` ASC),
  CONSTRAINT `fk_clockin_hr1`
    FOREIGN KEY (`hr_id`)
    REFERENCES `sandras`.`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;


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