12.30

CREATE DATABASE IF NOT EXISTS qhjc_system
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_general_ci;
CREATE TABLE task_info (
id VARCHAR(50) PRIMARY KEY COMMENT '任务编号 XT-2025-00001',
commit_date CHAR(8) NOT NULL COMMENT '委托时间 20241227',
client_name VARCHAR(100) NOT NULL COMMENT '委托单位名称',
item_name VARCHAR(200) NOT NULL COMMENT '委托项目名称',
item_content TEXT COMMENT '项目内容',
detect_class VARCHAR(20) DEFAULT '委托' COMMENT '检测类别: 委托/自检',
detect_method VARCHAR(200) COMMENT '检测方法',
sample_name VARCHAR(200) COMMENT '样品名称及状态',
dry_density DECIMAL(5,2) CHECK (dry_density >= 0 AND dry_density <= 100) COMMENT '干密度 0-100',
moist_density DECIMAL(5,2) CHECK (moist_density >= 0 AND moist_density <= 100) COMMENT '湿密度 0-100',
task_state ENUM('进行中', '经理待审核', '总监待审核', '已退回', '已完成') DEFAULT '进行中' COMMENT '任务状态',
operate_time DATETIME COMMENT '操作时间',
inspector_id VARCHAR(20) COMMENT '检测员ID',
manager_id VARCHAR(20) COMMENT '项目经理ID',
reject_reason TEXT COMMENT '退回意见',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_state (task_state),
INDEX idx_inspector (inspector_id),
INDEX idx_manager (manager_id),
FOREIGN KEY (inspector_id) REFERENCES user_info(user_id),
FOREIGN KEY (manager_id) REFERENCES user_info(user_id)
) ENGINE=InnoDB COMMENT='任务基本信息表';
CREATE TABLE user_info (
user_id VARCHAR(20) PRIMARY KEY COMMENT '人员ID 20250001',
user_name VARCHAR(50) NOT NULL COMMENT '姓名',
password VARCHAR(100) NOT NULL COMMENT '密码(加密存储)',
user_category ENUM('检测员', '项目经理', '项目总监', '委托客户') NOT NULL COMMENT '人员类别',
group_no INT DEFAULT 0 COMMENT '组号(总监为0，项目经理与检测员组号相同)',
phone VARCHAR(20) COMMENT '联系电话',
email VARCHAR(100) COMMENT '邮箱',
is_active BOOLEAN DEFAULT TRUE COMMENT '是否激活',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_category (user_category),
INDEX idx_group (group_no)
) ENGINE=InnoDB COMMENT='人员基本信息表';
CREATE TABLE commission_apply (
apply_id INT AUTO_INCREMENT PRIMARY KEY,
client_id VARCHAR(20) NOT NULL COMMENT '委托客户ID',
client_name VARCHAR(100) NOT NULL COMMENT '委托单位',
apply_date CHAR(8) NOT NULL COMMENT '委托时间',
project_name VARCHAR(200) NOT NULL COMMENT '委托项目名称',
project_content TEXT NOT NULL COMMENT '委托项目内容',
apply_status ENUM('待确认', '已确认', '已拒绝') DEFAULT '待确认',
confirm_result VARCHAR(10) COMMENT '确认结果: 同意/不同意',
confirm_time DATETIME COMMENT '确认时间',
confirm_user VARCHAR(20) COMMENT '确认人(总监ID)',
reject_reason TEXT COMMENT '拒绝原因',
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (client_id) REFERENCES user_info(user_id),
FOREIGN KEY (confirm_user) REFERENCES user_info(user_id)
) ENGINE=InnoDB COMMENT='委托申请表';