Jonathan Miller
223 lines
8.7 KiB
SQL
223 lines
8.7 KiB
SQL
--
|
|
-- MokoSuite Construction Tables
|
|
--
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_jobs` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`job_number` VARCHAR(20) NOT NULL DEFAULT '',
|
|
`title` VARCHAR(255) NOT NULL,
|
|
`description` TEXT,
|
|
`address` TEXT,
|
|
`city` VARCHAR(100) NOT NULL DEFAULT '',
|
|
`state` VARCHAR(50) NOT NULL DEFAULT '',
|
|
`postal_code` VARCHAR(20) NOT NULL DEFAULT '',
|
|
`owner_contact_id` INT DEFAULT NULL,
|
|
`architect_contact_id` INT DEFAULT NULL,
|
|
`superintendent_id` INT DEFAULT NULL,
|
|
`contract_value` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`approved_changes` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`revised_value` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`retention_pct` DECIMAL(5,2) NOT NULL DEFAULT 10.00,
|
|
`phase` ENUM('preconstruction','active','punch_list','closeout','warranty','completed') NOT NULL DEFAULT 'preconstruction',
|
|
`start_date` DATE DEFAULT NULL,
|
|
`end_date` DATE DEFAULT NULL,
|
|
`actual_start` DATE DEFAULT NULL,
|
|
`actual_end` DATE DEFAULT NULL,
|
|
`created` DATETIME NOT NULL,
|
|
`modified` DATETIME DEFAULT NULL,
|
|
`created_by` INT NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `idx_job_number` (`job_number`),
|
|
KEY `idx_owner` (`owner_contact_id`),
|
|
KEY `idx_phase` (`phase`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_cost_codes` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`job_id` INT UNSIGNED NOT NULL,
|
|
`division` VARCHAR(10) NOT NULL,
|
|
`section` VARCHAR(20) NOT NULL DEFAULT '',
|
|
`title` VARCHAR(255) NOT NULL,
|
|
`budget_amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`committed_amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`actual_amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`ordering` INT NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_job` (`job_id`),
|
|
KEY `idx_division` (`division`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_change_orders` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`job_id` INT UNSIGNED NOT NULL,
|
|
`co_number` INT UNSIGNED NOT NULL DEFAULT 1,
|
|
`title` VARCHAR(255) NOT NULL,
|
|
`description` TEXT,
|
|
`amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`days_impact` INT NOT NULL DEFAULT 0,
|
|
`status` ENUM('draft','submitted','approved','rejected') NOT NULL DEFAULT 'draft',
|
|
`submitted_at` DATETIME DEFAULT NULL,
|
|
`approved_at` DATETIME DEFAULT NULL,
|
|
`approved_by` INT DEFAULT NULL,
|
|
`created` DATETIME NOT NULL,
|
|
`created_by` INT NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_job` (`job_id`),
|
|
KEY `idx_status` (`status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_rfis` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`job_id` INT UNSIGNED NOT NULL,
|
|
`rfi_number` INT UNSIGNED NOT NULL DEFAULT 1,
|
|
`subject` VARCHAR(255) NOT NULL,
|
|
`question` TEXT NOT NULL,
|
|
`spec_section` VARCHAR(50) NOT NULL DEFAULT '',
|
|
`drawing_ref` VARCHAR(100) NOT NULL DEFAULT '',
|
|
`ball_in_court` VARCHAR(100) NOT NULL DEFAULT '',
|
|
`answer` TEXT,
|
|
`status` ENUM('open','answered','closed') NOT NULL DEFAULT 'open',
|
|
`submitted_by` INT DEFAULT NULL,
|
|
`answered_by` INT DEFAULT NULL,
|
|
`submitted_at` DATETIME NOT NULL,
|
|
`answered_at` DATETIME DEFAULT NULL,
|
|
`created` DATETIME NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_job` (`job_id`),
|
|
KEY `idx_status` (`status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_submittals` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`job_id` INT UNSIGNED NOT NULL,
|
|
`submittal_number` INT UNSIGNED NOT NULL DEFAULT 1,
|
|
`title` VARCHAR(255) NOT NULL,
|
|
`spec_section` VARCHAR(50) NOT NULL DEFAULT '',
|
|
`subcontractor_contact_id` INT DEFAULT NULL,
|
|
`status` ENUM('pending','in_review','approved','approved_as_noted','rejected','resubmit') NOT NULL DEFAULT 'pending',
|
|
`submitted_at` DATETIME DEFAULT NULL,
|
|
`reviewed_at` DATETIME DEFAULT NULL,
|
|
`reviewed_by` INT DEFAULT NULL,
|
|
`notes` TEXT,
|
|
`created` DATETIME NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_job` (`job_id`),
|
|
KEY `idx_status` (`status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_daily_logs` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`job_id` INT UNSIGNED NOT NULL,
|
|
`log_date` DATE NOT NULL,
|
|
`weather` VARCHAR(100) NOT NULL DEFAULT '',
|
|
`temperature_high` INT DEFAULT NULL,
|
|
`temperature_low` INT DEFAULT NULL,
|
|
`crew_count` INT UNSIGNED NOT NULL DEFAULT 0,
|
|
`work_performed` TEXT,
|
|
`equipment_used` TEXT,
|
|
`visitors` TEXT,
|
|
`safety_incidents` TEXT,
|
|
`delays` TEXT,
|
|
`photos_count` INT UNSIGNED NOT NULL DEFAULT 0,
|
|
`created_by` INT NOT NULL DEFAULT 0,
|
|
`created` DATETIME NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `idx_job_date` (`job_id`, `log_date`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_pay_applications` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`job_id` INT UNSIGNED NOT NULL,
|
|
`app_number` INT UNSIGNED NOT NULL DEFAULT 1,
|
|
`period_from` DATE NOT NULL,
|
|
`period_to` DATE NOT NULL,
|
|
`contract_sum` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`change_orders_total` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`revised_contract` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`work_completed` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`materials_stored` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`total_earned` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`retention_held` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`previous_payments` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`current_due` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`status` ENUM('draft','submitted','approved','paid') NOT NULL DEFAULT 'draft',
|
|
`submitted_at` DATETIME DEFAULT NULL,
|
|
`approved_at` DATETIME DEFAULT NULL,
|
|
`paid_at` DATETIME DEFAULT NULL,
|
|
`created` DATETIME NOT NULL,
|
|
`created_by` INT NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_job` (`job_id`),
|
|
KEY `idx_status` (`status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_pay_app_lines` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`pay_app_id` INT UNSIGNED NOT NULL,
|
|
`cost_code_id` INT UNSIGNED NOT NULL,
|
|
`scheduled_value` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`previous_work` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`current_work` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`materials_stored` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`total_completed` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`pct_complete` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
|
|
`balance_to_finish` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_pay_app` (`pay_app_id`),
|
|
KEY `idx_cost_code` (`cost_code_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_lien_waivers` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`job_id` INT UNSIGNED NOT NULL,
|
|
`vendor_contact_id` INT NOT NULL,
|
|
`type` ENUM('conditional_partial','unconditional_partial','conditional_final','unconditional_final') NOT NULL,
|
|
`amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`through_date` DATE DEFAULT NULL,
|
|
`received` TINYINT NOT NULL DEFAULT 0,
|
|
`received_at` DATETIME DEFAULT NULL,
|
|
`document_path` VARCHAR(500) NOT NULL DEFAULT '',
|
|
`created` DATETIME NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_job` (`job_id`),
|
|
KEY `idx_vendor` (`vendor_contact_id`),
|
|
KEY `idx_type` (`type`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_punch_items` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`job_id` INT UNSIGNED NOT NULL,
|
|
`location` VARCHAR(255) NOT NULL DEFAULT '',
|
|
`description` TEXT NOT NULL,
|
|
`responsible_contact_id` INT DEFAULT NULL,
|
|
`priority` ENUM('high','medium','low') NOT NULL DEFAULT 'medium',
|
|
`status` ENUM('open','in_progress','completed','verified') NOT NULL DEFAULT 'open',
|
|
`photo_path` VARCHAR(500) NOT NULL DEFAULT '',
|
|
`completed_at` DATETIME DEFAULT NULL,
|
|
`verified_at` DATETIME DEFAULT NULL,
|
|
`created` DATETIME NOT NULL,
|
|
`created_by` INT NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_job` (`job_id`),
|
|
KEY `idx_status` (`status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `#__mokosuiteconstruction_subcontracts` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`job_id` INT UNSIGNED NOT NULL,
|
|
`subcontractor_contact_id` INT NOT NULL,
|
|
`trade` VARCHAR(100) NOT NULL DEFAULT '',
|
|
`scope` TEXT,
|
|
`contract_amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
|
|
`retention_pct` DECIMAL(5,2) NOT NULL DEFAULT 10.00,
|
|
`status` ENUM('draft','executed','in_progress','completed','terminated') NOT NULL DEFAULT 'draft',
|
|
`executed_at` DATETIME DEFAULT NULL,
|
|
`insurance_gl_expires` DATE DEFAULT NULL,
|
|
`insurance_wc_expires` DATE DEFAULT NULL,
|
|
`created` DATETIME NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_job` (`job_id`),
|
|
KEY `idx_sub` (`subcontractor_contact_id`),
|
|
KEY `idx_status` (`status`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|