From 2c86a8bd60deed0dd0e044bad6fb83f75d19a332 Mon Sep 17 00:00:00 2001 From: wlzboy <66905212@qq.com> Date: 星期日, 26 十月 2025 15:05:50 +0800 Subject: [PATCH] Merge branch 'feature-task' --- sql/task_tables.sql | 120 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 files changed, 120 insertions(+), 0 deletions(-) diff --git a/sql/task_tables.sql b/sql/task_tables.sql new file mode 100644 index 0000000..ab8e4d3 --- /dev/null +++ b/sql/task_tables.sql @@ -0,0 +1,120 @@ +-- ---------------------------- +-- 閫氱敤浠诲姟绠＄悊鐩稿叧琛ㄧ粨鏋� +-- ---------------------------- + +-- 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 '浠诲姟绫诲瀷锛歁AINTENANCE-缁翠慨淇濆吇锛孎UEL-鍔犳补浠诲姟锛孫THER-鍏朵粬', + 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 '鐩殑鍦板潃', + + -- GPS鍧愭爣淇℃伅 + departure_longitude DECIMAL(10,7) COMMENT '鍑哄彂鍦扮粡搴�', + departure_latitude DECIMAL(10,7) COMMENT '鍑哄彂鍦扮含搴�', + destination_longitude DECIMAL(10,7) COMMENT '鐩殑鍦扮粡搴�', + destination_latitude DECIMAL(10,7) COMMENT '鐩殑鍦扮含搴�', + estimated_distance DECIMAL(8,2) 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 '鍒涘缓浜篒D', + assignee_id BIGINT COMMENT '鎵ц浜篒D', + 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 '鎿嶄綔绫诲瀷锛欳REATE-鍒涘缓锛孶PDATE-鏇存柊锛孉SSIGN-鍒嗛厤锛孲TATUS_CHANGE-鐘舵�佸彉鏇达紝DELETE-鍒犻櫎', + operation_desc VARCHAR(500) COMMENT '鎿嶄綔鎻忚堪', + old_value TEXT COMMENT '鎿嶄綔鍓嶅��', + new_value TEXT COMMENT '鎿嶄綔鍚庡��', + operator_id BIGINT NOT NULL COMMENT '鎿嶄綔浜篒D', + 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; -- Gitblit v1.9.1