From 7f7993e0fde1e08bfd37982ab6eb9e5a08545b2d Mon Sep 17 00:00:00 2001 From: gongchunyi <deslre0381@gmail.com> Date: 星期五, 20 三月 2026 11:24:33 +0800 Subject: [PATCH] refactor: 生产订单绑定工艺路线的子表重命名 --- doc/宁夏-中盛建材.sql | 248 +++++++++++++++++++++++++++++++++++++++++++++++++ 1 files changed, 248 insertions(+), 0 deletions(-) diff --git "a/doc/\345\256\201\345\244\217-\344\270\255\347\233\233\345\273\272\346\235\220.sql" "b/doc/\345\256\201\345\244\217-\344\270\255\347\233\233\345\273\272\346\235\220.sql" index 64a938d..f91a888 100644 --- "a/doc/\345\256\201\345\244\217-\344\270\255\347\233\233\345\273\272\346\235\220.sql" +++ "b/doc/\345\256\201\345\244\217-\344\270\255\347\233\233\345\273\272\346\235\220.sql" @@ -167,3 +167,251 @@ ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1; + + +alter table production_plan + add status tinyint default 0 not null comment '鐘舵�� 0鏈笅鍙� 1宸蹭笅鍙�'; + + +ALTER TABLE `product-inventory-management-zsjc`.`production_plan` + DROP COLUMN `data_sync_type`, + MODIFY COLUMN `data_source_type` tinyint NULL DEFAULT 1 COMMENT '鏁版嵁鏉ユ簮绫诲瀷锛�1=閽夐拤鍚屾 2=鎵嬪姩鏂板' AFTER `form_modified_time`; + +-- 鍒犻櫎鏃ц〃 +DROP TABLE IF EXISTS `base_param`; +DROP TABLE IF EXISTS `product_process_param`; + +CREATE TABLE `base_param` +( + `id` bigint NOT NULL AUTO_INCREMENT COMMENT '涓婚敭ID', + `param_key` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '鍙傛暟鍞竴鏍囪瘑', + `param_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '鍙傛暟鍚嶇О', + `param_type` tinyint NOT NULL COMMENT '鍙傛暟绫诲瀷(1鏁板瓧 2鏂囨湰 3涓嬫媺閫夋嫨 4鏃堕棿)', + `param_format` varchar(255) DEFAULT NULL COMMENT '鍙傛暟鏍煎紡', + `value_mode` tinyint DEFAULT '1' COMMENT '鍊兼ā寮�(1鍗曞�� 2鍖洪棿)', + `unit` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '鍗曚綅', + `is_required` tinyint DEFAULT '0' COMMENT '鏄惁蹇呭～', + `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '澶囨敞', + `create_user` varchar(64) DEFAULT NULL COMMENT '鍒涘缓浜�', + `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿', + `update_user` varchar(64) DEFAULT NULL COMMENT '淇敼浜�', + `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '淇敼鏃堕棿', + `tenant_id` bigint DEFAULT NULL COMMENT '绉熸埛ID', + PRIMARY KEY (`id`) USING BTREE +) ENGINE = InnoDB + AUTO_INCREMENT = 10 + DEFAULT CHARSET = utf8mb4 + COLLATE = utf8mb4_0900_ai_ci COMMENT ='鍩虹鍙傛暟瀹氫箟琛�'; + +CREATE TABLE `product_process_param` +( + `id` bigint NOT NULL AUTO_INCREMENT COMMENT '涓婚敭ID', + `process_id` bigint NOT NULL COMMENT '鎵�灞炲伐搴廔D (product_process.id)', + `param_id` bigint NOT NULL COMMENT '鍏宠仈鍩虹鍙傛暟ID (base_param.id)', + `standard_value` varchar(200) DEFAULT NULL COMMENT '鍦ㄦ宸ュ簭璁惧畾鐨勬爣鍑嗗��(鍗曞�兼ā寮�)', + `min_value` decimal(10, 2) DEFAULT NULL COMMENT '鍦ㄦ宸ュ簭璁惧畾鐨勬爣鍑嗘渶灏忓��(鍖洪棿妯″紡)', + `max_value` decimal(10, 2) DEFAULT NULL COMMENT '鍦ㄦ宸ュ簭璁惧畾鐨勬爣鍑嗘渶澶у��(鍖洪棿妯″紡)', + `is_required` tinyint NOT NULL DEFAULT '0' COMMENT '鍦ㄦ宸ュ簭涓槸鍚﹀繀濉�(0-鍚�, 1-鏄�)', + `sort` int NOT NULL DEFAULT '0' COMMENT '鎺掑簭鍙�', + `tenant_id` bigint DEFAULT NULL COMMENT '绉熸埛ID', + `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿', + `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '鏇存柊鏃堕棿', + PRIMARY KEY (`id`) USING BTREE, + KEY `idx_process_id` (`process_id`) USING BTREE, + KEY `idx_param_id` (`param_id`) USING BTREE +) ENGINE = InnoDB + AUTO_INCREMENT = 1 + DEFAULT CHARSET = utf8mb4 + COLLATE = utf8mb4_0900_ai_ci COMMENT ='宸ュ簭缁戝畾鍙傛暟琛�'; + +ALTER TABLE `product_process` MODIFY COLUMN `name` varchar(255) COMMENT '宸ュ簭鍚嶇О'; +ALTER TABLE `product_process` MODIFY COLUMN `no` varchar(255) COMMENT '宸ュ簭缂栧彿'; +ALTER TABLE `product_process` ADD COLUMN `status` tinyint(1) DEFAULT '1' COMMENT '鐘舵�侊細0-鍋滅敤锛�1-鍚敤' AFTER `no`; +ALTER TABLE `product_process` MODIFY COLUMN `type` bigint COMMENT '绫诲瀷锛�0-璁℃椂锛�1-璁′欢'; +ALTER TABLE `product_process` MODIFY COLUMN `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿'; +ALTER TABLE `product_process` MODIFY COLUMN `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '淇敼鏃堕棿'; + +ALTER TABLE `product-inventory-management-zsjc`.`customer` + ADD COLUMN `form_instance_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '瀹滄惌琛ㄥ崟瀹炰緥ID' AFTER `customer_type`, + ADD COLUMN `form_modified_time` datetime(0) NULL DEFAULT NULL COMMENT '瀹滄惌淇敼鏃堕棿' AFTER `form_instance_id`; + +ALTER TABLE `process_route` + ADD COLUMN `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '鐘舵�侊細0-鑽夌锛�1-宸叉壒鍑�' AFTER `bom_id`; + +CREATE TABLE `process_route_item_param` +( + `id` bigint NOT NULL AUTO_INCREMENT COMMENT '涓婚敭ID', + `route_item_id` bigint NOT NULL COMMENT '鍏宠仈宸ヨ壓璺嚎鏄庣粏ID (process_route_item.id)', + `param_id` bigint NOT NULL COMMENT '鍏宠仈鍩虹鍙傛暟瀹氫箟ID (base_param.id)', + `process_param_id` bigint DEFAULT NULL COMMENT '鏉ユ簮宸ュ簭鍙傛暟ID', + + `standard_value` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '姝よ矾绾胯妭鐐硅瀹氱殑鏍囧噯鍊�', + `min_value` decimal(10, 2) DEFAULT NULL COMMENT '姝よ矾绾胯妭鐐硅瀹氱殑鏍囧噯鏈�灏忓��', + `max_value` decimal(10, 2) DEFAULT NULL COMMENT '姝よ矾绾胯妭鐐硅瀹氱殑鏍囧噯鏈�澶у��', + `is_required` tinyint NOT NULL DEFAULT '0' COMMENT '鏄惁蹇呭～', + + `sort` int NOT NULL DEFAULT '0' COMMENT '鎺掑簭', + `tenant_id` bigint DEFAULT NULL COMMENT '绉熸埛ID', + `create_time` datetime DEFAULT CURRENT_TIMESTAMP, + `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + PRIMARY KEY (`id`) USING BTREE, + KEY `idx_route_item_id` (`route_item_id`) USING BTREE, + KEY `idx_param_id` (`param_id`) USING BTREE +) ENGINE = InnoDB + DEFAULT CHARSET = utf8mb4 + COLLATE = utf8mb4_0900_ai_ci COMMENT ='宸ヨ壓璺嚎宸ュ簭鍙傛暟琛�'; + + +ALTER TABLE `product-inventory-management-zsjc`.`quality_inspect` + ADD COLUMN `inspect_nature` varchar(255) NULL COMMENT '妫�娴嬫�ц川' AFTER `test_standard_id`, +ADD COLUMN `sample_code` varchar(255) NULL COMMENT '璇曟牱缂栧彿' AFTER `inspect_nature`, +ADD COLUMN `sample_state` varchar(255) NULL COMMENT '璇曟牱鐘舵��' AFTER `sample_code`, +ADD COLUMN `sample_time` date NULL COMMENT '鍙栨牱鏃ユ湡' AFTER `sample_state`, +ADD COLUMN `license_plate_number` varchar(255) NULL COMMENT '杞︾墝鍙�' AFTER `sample_time`; + + +ALTER TABLE `product_bom` + ADD COLUMN `dict_code` bigint NOT NULL COMMENT '鍏宠仈瀛楀吀鏁版嵁缂栫爜锛坰ys_dict_data.dict_code锛�'; +ALTER TABLE `product_bom` ADD INDEX `idx_dict_code` (`dict_code`); + +ALTER TABLE `product-inventory-management-zsjc`.`process_route` + ADD COLUMN `dict_code` bigint NOT NULL COMMENT '浜у搧绫诲瀷瀛楀吀缂栫爜' AFTER `status`; + + +ALTER TABLE `product-inventory-management-zsjc`.`product_order` + ADD COLUMN `status` int NULL COMMENT '鐘舵�侊紙1.寰呭紑濮嬨��2.杩涜涓��3.宸插畬鎴愩��4.宸插彇娑堬級' AFTER `plan_complete_time`; + + +ALTER TABLE `product-inventory-management-zsjc`.`product_order` + ADD COLUMN `strength` varchar(255) NULL COMMENT '浜у搧绫诲瀷' AFTER `plan_complete_time`; + +CREATE TABLE `product-inventory-management-zsjc`.`sales_delivery` ( + `id` int NOT NULL AUTO_INCREMENT, + `delivery_date` date NULL COMMENT '渚涜揣鏃ユ湡', + `delivery_code` varchar(255) NULL COMMENT '鍙戣揣鍗曠紪鍙�', + `project_name` varchar(255) NULL COMMENT '椤圭洰鍚嶇О', + `delivery_place` varchar(255) NULL COMMENT '閫佽揣鍦扮偣', + `link_man` varchar(255) NULL COMMENT '鑱旂郴浜�', + `link_phone` varchar(255) NULL COMMENT '鑱旂郴鐢佃瘽', + `delivery_remark` varchar(255) NULL COMMENT '鍙戣揣澶囨敞', + `material_code` varchar(255) NULL COMMENT '鐗╂枡浠ｇ爜', + `product_name` varchar(255) NULL COMMENT '浜у搧鍚嶇О', + `model` varchar(255) NULL COMMENT '浜у搧瑙勬牸', + `volume` decimal(10, 4) NULL COMMENT '鏂归噺', + `unit_price` decimal(10, 2) NULL COMMENT '鍗曚环', + `price` decimal(10, 2) NULL COMMENT '浠锋牸', + `product_remark` varchar(255) NULL COMMENT '浜у搧澶囨敞', + `customer` varchar(255) NULL COMMENT '瀹㈡埛鍚嶇О', + `salesman` varchar(255) NULL COMMENT '涓氬姟鍛�', + `delivery_method` varchar(255) NULL COMMENT '鍙戣揣鏂瑰紡', + `form_instance_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '瀹滄惌琛ㄥ崟瀹炰緥ID', + `form_modified_time` datetime(0) NULL DEFAULT NULL COMMENT '瀹滄惌淇敼鏃堕棿', + `create_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0), + `update_time` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0), + PRIMARY KEY (`id`) + ) COMMENT = '閿�鍞彂璐ф槑缁�(瀹滄惌)'; + + +ALTER TABLE product_process_param + DROP COLUMN param_id; + +ALTER TABLE product_process_param + ADD COLUMN `param_key` varchar(100) DEFAULT NULL COMMENT '鍙傛暟鍞竴鏍囪瘑', + ADD COLUMN `param_name` varchar(100) NOT NULL COMMENT '鍙傛暟鍚嶇О', + ADD COLUMN `param_type` tinyint NOT NULL COMMENT '鍙傛暟绫诲瀷(1鏁板瓧 2鏂囨湰 3涓嬫媺閫夋嫨 4鏃堕棿)', + ADD COLUMN `param_format` varchar(255) DEFAULT NULL COMMENT '鍙傛暟鏍煎紡', + ADD COLUMN `value_mode` tinyint DEFAULT '1' COMMENT '鍊兼ā寮�(1鍗曞�� 2鍖洪棿)', + ADD COLUMN `unit` varchar(50) DEFAULT NULL COMMENT '鍗曚綅', + ADD COLUMN `remark` varchar(255) DEFAULT NULL COMMENT '澶囨敞'; + +ALTER TABLE process_route_item_param + DROP COLUMN param_id; + +ALTER TABLE process_route_item_param + ADD COLUMN `param_key` varchar(100) DEFAULT NULL COMMENT '鍙傛暟鍞竴鏍囪瘑', + ADD COLUMN `param_name` varchar(100) NOT NULL COMMENT '鍙傛暟鍚嶇О', + ADD COLUMN `param_type` tinyint NOT NULL COMMENT '鍙傛暟绫诲瀷(1鏁板瓧 2鏂囨湰 3涓嬫媺閫夋嫨 4鏃堕棿)', + ADD COLUMN `param_format` varchar(255) DEFAULT NULL COMMENT '鍙傛暟鏍煎紡', + ADD COLUMN `value_mode` tinyint DEFAULT '1' COMMENT '鍊兼ā寮�(1鍗曞�� 2鍖洪棿)', + ADD COLUMN `unit` varchar(50) DEFAULT NULL COMMENT '鍗曚綅', + ADD COLUMN `remark` varchar(255) DEFAULT NULL COMMENT '澶囨敞'; + +ALTER TABLE `product-inventory-management-zsjc`.`product_structure` + MODIFY COLUMN `unit_quantity` decimal(20, 15) NOT NULL COMMENT '鍗曚綅浜у嚭闇�瑕佹暟閲�' AFTER `process_id`, + MODIFY COLUMN `demanded_quantity` decimal(20, 15) NULL DEFAULT NULL COMMENT '闇�姹傛暟閲�' AFTER `unit_quantity`, + + +CREATE TABLE `production_order_route_item` +( + `id` bigint NOT NULL AUTO_INCREMENT, + `order_id` bigint NOT NULL COMMENT '鐢熶骇璁㈠崟id', + `route_id` bigint NOT NULL DEFAULT '0' COMMENT '宸ヨ壓璺嚎id', + `product_model_id` bigint DEFAULT '0' COMMENT '浜у搧id', + `process_id` bigint NOT NULL DEFAULT '0' COMMENT '宸ュ簭id', + `tenant_id` bigint NOT NULL COMMENT '绉熸埛id', + `create_time` datetime DEFAULT NULL COMMENT '褰曞叆鏃堕棿', + `update_time` datetime DEFAULT NULL COMMENT '鏇存柊鏃堕棿', + `drag_sort` int DEFAULT NULL COMMENT '鎷栧姩鎺掑簭', + `is_quality` tinyint(1) DEFAULT '1' COMMENT '鏄惁璐ㄦ宸ュ簭', + PRIMARY KEY (`id`) USING BTREE, + KEY `idx_order_id` (`order_id`) +) ENGINE = InnoDB + AUTO_INCREMENT = 83 + DEFAULT CHARSET = utf8mb4 + COLLATE = utf8mb4_0900_ai_ci + ROW_FORMAT = DYNAMIC + COMMENT ='鐢熶骇璁㈠崟缁戝畾鐨勫伐鑹鸿矾绾�--宸ュ簭琛�'; + +CREATE TABLE `production_order_route_item_param` +( + `id` bigint NOT NULL AUTO_INCREMENT COMMENT '涓婚敭ID', + `order_id` bigint DEFAULT NULL COMMENT '鐢熶骇璁㈠崟ID', + `route_item_id` bigint NOT NULL COMMENT '鍏宠仈宸ヨ壓璺嚎鏄庣粏ID (production_order_route_item.id)', + `process_param_id` bigint DEFAULT NULL COMMENT '鏉ユ簮宸ュ簭鍙傛暟ID', + `param_key` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '鍙傛暟鍞竴鏍囪瘑', + `param_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '鍙傛暟鍚嶇О', + `param_type` tinyint NOT NULL COMMENT '鍙傛暟绫诲瀷(1鏁板瓧 2鏂囨湰 3涓嬫媺閫夋嫨 4鏃堕棿)', + `param_format` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '鍙傛暟鏍煎紡', + `value_mode` tinyint DEFAULT '1' COMMENT '鍊兼ā寮�(1鍗曞�� 2鍖洪棿)', + `unit` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '鍗曚綅', + `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '澶囨敞', + `standard_value` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '鏍囧噯鍊�', + `min_value` decimal(10, 2) DEFAULT NULL COMMENT '姝よ矾绾胯妭鐐硅瀹氱殑鏍囧噯鏈�灏忓��', + `max_value` decimal(10, 2) DEFAULT NULL COMMENT '姝よ矾绾胯妭鐐硅瀹氱殑鏍囧噯鏈�澶у��', + `is_required` tinyint NOT NULL DEFAULT '0' COMMENT '鏄惁蹇呭～', + `sort` int NOT NULL DEFAULT '0' COMMENT '鎺掑簭', + `tenant_id` bigint DEFAULT NULL COMMENT '绉熸埛ID', + `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿', + `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '鏇存柊鏃堕棿', + PRIMARY KEY (`id`) USING BTREE, + KEY `idx_route_item_id` (`route_item_id`) USING BTREE, + KEY `idx_param_key` (`param_key`) +) ENGINE = InnoDB + DEFAULT CHARSET = utf8mb4 + COLLATE = utf8mb4_0900_ai_ci + ROW_FORMAT = DYNAMIC + COMMENT ='鐢熶骇璁㈠崟缁戝畾鐨勫伐鑹鸿矾绾垮伐搴�--鍙傛暟琛�'; + +CREATE TABLE `production_order_structure` +( + `id` bigint NOT NULL AUTO_INCREMENT, + `order_id` bigint NOT NULL COMMENT '鐢熶骇璁㈠崟id', + `parent_id` bigint DEFAULT NULL COMMENT '鐖惰妭鐐笽D', + `product_model_id` bigint DEFAULT NULL COMMENT '浜у搧id', + `process_id` bigint DEFAULT NULL COMMENT '宸ュ簭id', + `unit_quantity` decimal(16, 4) NOT NULL COMMENT '鍗曚綅浜у嚭闇�瑕佹暟閲�', + `demanded_quantity` decimal(16, 4) DEFAULT NULL COMMENT '闇�姹傛暟閲�', + `unit` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '鍗曚綅', + `tenant_id` bigint DEFAULT NULL COMMENT '绉熸埛id', + `bom_id` bigint NOT NULL COMMENT 'bom鐨刬d', + PRIMARY KEY (`id`) USING BTREE, + KEY `idx_order_id` (`order_id`) +) ENGINE = InnoDB + DEFAULT CHARSET = utf8mb4 + COLLATE = utf8mb4_0900_ai_ci + ROW_FORMAT = DYNAMIC + COMMENT ='鐢熶骇璁㈠崟缁戝畾鐨凚OM瀛愯〃'; + +DROP TABLE IF EXISTS process_route_item_instance; +DROP TABLE IF EXISTS process_route_item_param_instance; +DROP TABLE IF EXISTS product_structure_instance; \ No newline at end of file -- Gitblit v1.9.3