CREATE DATABASE IF NOT EXISTS iwb_railway_costing_v1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; USE iwb_railway_costing_v1; -- 创建项目任务表 CREATE TABLE IF NOT EXISTS project_task ( id INT AUTO_INCREMENT PRIMARY KEY, task_name VARCHAR(255) NOT NULL COMMENT '任务名称', task_sort INT DEFAULT 0 COMMENT '任务排序', task_desc VARCHAR(1000) COMMENT '任务描述', is_cover TINYINT DEFAULT 0 COMMENT '是否覆盖', project_id VARCHAR(50) NOT NULL COMMENT '项目编号', budget_id INT NOT NULL DEFAULT 0 COMMENT '概算序号', item_id INT NOT NULL COMMENT '条目序号', item_code VARCHAR(255) NOT NULL COMMENT '条目编号', file_path TEXT COMMENT '文件路径', process_status INT NOT NULL DEFAULT 0 COMMENT '处理状态(0:草稿, 1:待运行, 2:运行中, 200:运行成功, 4:运行失败 5:取消运行)', process_time DATETIME COMMENT '处理时间', process_error VARCHAR(4000) COMMENT '处理错误信息', send_status INT NOT NULL DEFAULT 0 COMMENT '推送状态(0:未推送, 1:推送中 ,2:推送成功, 3:推送失败, 4:已修改)', send_time DATETIME COMMENT '推送时间', send_error VARCHAR(4000) COMMENT '推送错误信息', is_del TINYINT NOT NULL DEFAULT 0 COMMENT '是否删除(0:否, 1:是)', deleted_by VARCHAR(50) COMMENT '删除人', deleted_at DATETIME COMMENT '删除时间', created_by VARCHAR(50) COMMENT '创建人', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', updated_by VARCHAR(50) COMMENT '更新人', updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', INDEX idx_project_id (project_id), INDEX idx_budget_id (budget_id), INDEX idx_item_id (item_id), INDEX idx_item_code (item_code), INDEX idx_created_at (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='项目任务表'; -- 创建项目定额表 CREATE TABLE IF NOT EXISTS project_quota ( id INT AUTO_INCREMENT PRIMARY KEY, task_id INT NOT NULL COMMENT '任务编号', project_id VARCHAR(50) NOT NULL COMMENT '项目编号', budget_id INT NOT NULL COMMENT '概算序号', budget_code VARCHAR(50) COMMENT '概算编号', item_id INT NOT NULL COMMENT '条目序号', item_code VARCHAR(255) NOT NULL COMMENT '条目编号', quota_id INT NULL DEFAULT 0 COMMENT '定额序号', quota_code VARCHAR(50) COMMENT '定额编号', entry_name VARCHAR(255) COMMENT '工程或费用项目名称', units VARCHAR(20) COMMENT '单位', amount FLOAT COMMENT '数量', ex_file VARCHAR(4000) COMMENT 'excel文件', ex_cell VARCHAR(50) COMMENT '数量单元格位置', ex_row VARCHAR(4000) COMMENT '该行内容', ex_unit VARCHAR(50) COMMENT 'excel中给出的单位', ex_amount FLOAT COMMENT 'excel中给出的数量', send_status INT NOT NULL DEFAULT 0 COMMENT '推送状态(0:未推送,1:推送中 ,2:已推送, 3:推送失败, 4:已修改)', send_time DATETIME COMMENT '推送时间', send_error VARCHAR(4000) COMMENT '推送错误信息', is_del TINYINT NOT NULL DEFAULT 0 COMMENT '是否删除(0:否, 1:是)', deleted_by VARCHAR(50) COMMENT '删除人', deleted_at DATETIME COMMENT '删除时间', created_by VARCHAR(50) COMMENT '创建人', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', updated_by VARCHAR(50) COMMENT '更新人', updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', INDEX idx_project_id (project_id), INDEX idx_budget_id (budget_id), INDEX idx_item_id (item_id), INDEX idx_item_code (item_code), INDEX idx_created_at (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='项目定额表'; -- 创建日志表 CREATE TABLE IF NOT EXISTS sys_log ( id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(255) NOT NULL COMMENT '用户名', operation_type VARCHAR(50) NOT NULL COMMENT '操作类型', operation_desc VARCHAR(1000) COMMENT '操作描述', operation_result TINYINT COMMENT '操作结果', operation_module VARCHAR(100) COMMENT '操作模块', operation_data TEXT COMMENT '操作数据', data_changes TEXT COMMENT '数据变更记录', operation_ip VARCHAR(50) COMMENT '操作IP', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', INDEX idx_username (username), INDEX idx_created_at (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='操作日志表';