-- ----------------------------
|
-- 通用任务管理相关表结构
|
-- ----------------------------
|
|
-- 1. 任务主表
|
-- ----------------------------
|
DROP TABLE IF EXISTS sys_task;
|
CREATE TABLE sys_task (
|
task_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '任务ID',
|
task_code VARCHAR(50) NOT NULL UNIQUE COMMENT '任务编号',
|
task_type VARCHAR(20) NOT NULL COMMENT '任务类型：MAINTENANCE-维修保养，FUEL-加油任务，OTHER-其他',
|
task_status VARCHAR(20) NOT NULL DEFAULT 'PENDING' COMMENT '任务状态：PENDING-待开始，IN_PROGRESS-任务中，COMPLETED-已完成，CANCELLED-已取消',
|
task_description VARCHAR(1000) COMMENT '任务描述',
|
|
-- 地址信息
|
departure_address VARCHAR(500) COMMENT '出发地址',
|
destination_address VARCHAR(500) COMMENT '目的地址',
|
|
-- 时间信息
|
planned_start_time DATETIME COMMENT '计划开始时间',
|
planned_end_time DATETIME COMMENT '计划结束时间',
|
actual_start_time DATETIME COMMENT '实际开始时间',
|
actual_end_time DATETIME COMMENT '实际结束时间',
|
|
-- 人员信息
|
creator_id BIGINT NOT NULL COMMENT '创建人ID',
|
assignee_id BIGINT COMMENT '执行人ID',
|
dept_id BIGINT NOT NULL COMMENT '归属部门ID',
|
|
-- 系统字段
|
create_time DATETIME NOT NULL COMMENT '创建时间',
|
update_time DATETIME NOT NULL COMMENT '更新时间',
|
create_by VARCHAR(64) NOT NULL COMMENT '创建者',
|
update_by VARCHAR(64) COMMENT '更新者',
|
remark VARCHAR(500) COMMENT '备注',
|
del_flag CHAR(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
|
|
INDEX idx_task_code (task_code),
|
INDEX idx_task_type (task_type),
|
INDEX idx_task_status (task_status),
|
INDEX idx_creator_id (creator_id),
|
INDEX idx_assignee_id (assignee_id),
|
INDEX idx_dept_id (dept_id),
|
INDEX idx_planned_start_time (planned_start_time),
|
INDEX idx_create_time (create_time)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='任务管理表';
|
|
-- 2. 任务车辆关联表
|
-- ----------------------------
|
DROP TABLE IF EXISTS sys_task_vehicle;
|
CREATE TABLE sys_task_vehicle (
|
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '关联ID',
|
task_id BIGINT NOT NULL COMMENT '任务ID',
|
vehicle_id BIGINT NOT NULL COMMENT '车辆ID',
|
assign_time DATETIME NOT NULL COMMENT '分配时间',
|
assign_by VARCHAR(64) NOT NULL COMMENT '分配人',
|
status VARCHAR(20) DEFAULT 'ASSIGNED' COMMENT '关联状态：ASSIGNED-已分配，ACTIVE-执行中，COMPLETED-已完成，CANCELLED-已取消',
|
remark VARCHAR(500) COMMENT '备注',
|
|
INDEX idx_task_id (task_id),
|
INDEX idx_vehicle_id (vehicle_id),
|
INDEX idx_status (status),
|
INDEX idx_assign_time (assign_time),
|
|
UNIQUE KEY uk_task_vehicle (task_id, vehicle_id),
|
FOREIGN KEY (task_id) REFERENCES sys_task(task_id) ON DELETE CASCADE,
|
FOREIGN KEY (vehicle_id) REFERENCES tb_vehicle_info(vehicle_id) ON DELETE CASCADE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='任务车辆关联表';
|
|
-- 3. 任务附件表
|
-- ----------------------------
|
DROP TABLE IF EXISTS sys_task_attachment;
|
CREATE TABLE sys_task_attachment (
|
attachment_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '附件ID',
|
task_id BIGINT NOT NULL COMMENT '任务ID',
|
file_name VARCHAR(255) NOT NULL COMMENT '文件名',
|
file_path VARCHAR(500) NOT NULL COMMENT '文件路径',
|
file_size BIGINT COMMENT '文件大小（字节）',
|
file_type VARCHAR(50) COMMENT '文件类型',
|
upload_time DATETIME NOT NULL COMMENT '上传时间',
|
upload_by VARCHAR(64) NOT NULL COMMENT '上传者',
|
|
INDEX idx_task_id (task_id),
|
FOREIGN KEY (task_id) REFERENCES sys_task(task_id) ON DELETE CASCADE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='任务附件表';
|
|
-- 4. 任务操作日志表
|
-- ----------------------------
|
DROP TABLE IF EXISTS sys_task_log;
|
CREATE TABLE sys_task_log (
|
log_id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '日志ID',
|
task_id BIGINT NOT NULL COMMENT '任务ID',
|
operation_type VARCHAR(20) NOT NULL COMMENT '操作类型：CREATE-创建，UPDATE-更新，ASSIGN-分配，STATUS_CHANGE-状态变更，DELETE-删除',
|
operation_desc VARCHAR(500) COMMENT '操作描述',
|
old_value TEXT COMMENT '操作前值',
|
new_value TEXT COMMENT '操作后值',
|
operator_id BIGINT NOT NULL COMMENT '操作人ID',
|
operator_name VARCHAR(64) NOT NULL COMMENT '操作人姓名',
|
operation_time DATETIME NOT NULL COMMENT '操作时间',
|
ip_address VARCHAR(128) COMMENT 'IP地址',
|
|
INDEX idx_task_id (task_id),
|
INDEX idx_operation_type (operation_type),
|
INDEX idx_operator_id (operator_id),
|
INDEX idx_operation_time (operation_time),
|
FOREIGN KEY (task_id) REFERENCES sys_task(task_id) ON DELETE CASCADE
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='任务操作日志表';
|
|
-- 5. 更新车辆信息表，添加机构关联字段
|
-- ----------------------------
|
ALTER TABLE tb_vehicle_info ADD COLUMN dept_id BIGINT(20) DEFAULT NULL COMMENT '归属机构ID';
|
ALTER TABLE tb_vehicle_info ADD INDEX idx_dept_id (dept_id);
|
ALTER TABLE tb_vehicle_info ADD FOREIGN KEY (dept_id) REFERENCES sys_dept(dept_id) ON DELETE SET NULL;
|
