From c3b81ea987501dc2f5dc3c8db23038eeb8f290e9 Mon Sep 17 00:00:00 2001
From: gongchunyi <deslre0381@gmail.com>
Date: 星期四, 12 三月 2026 17:56:16 +0800
Subject: [PATCH] fix: 产品保存时唯一性导致数据丢失
---
doc/宁夏-中盛建材.sql | 206 ++++++++++++++++++++++++++++++++++++++++-----------
1 files changed, 161 insertions(+), 45 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 16572ba..64a938d 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"
@@ -1,53 +1,169 @@
-CREATE TABLE `prod_demand_main`
+DROP TABLE IF EXISTS `production_plan`;
+
+CREATE TABLE production_plan
(
- `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '鑷涓婚敭',
- `tenant_id` bigint(20) NOT NULL COMMENT '绉熸埛缂栧彿',
- `dept_id` bigint(20) DEFAULT NULL COMMENT '閮ㄩ棬ID (鐢ㄤ簬鏁版嵁鏉冮檺闅旂)',
- `form_instance_id` varchar(64) NOT NULL COMMENT '琛ㄥ崟瀹炰緥鍞竴ID (鍏宠仈瀛愯〃鐢�)',
- `serial_no` varchar(64) DEFAULT NULL COMMENT '鍗曟嵁娴佹按鍙� (serialNo)',
- `title` varchar(128) DEFAULT NULL COMMENT '鍗曟嵁鏍囬 (title)',
- `originator_user_id` varchar(64) DEFAULT NULL COMMENT '绗笁鏂瑰彂璧蜂汉ID (creatorUserId)',
- `originator_name` varchar(64) DEFAULT NULL COMMENT '绗笁鏂瑰彂璧蜂汉濮撳悕 (originator.userName)',
- `biz_create_time` datetime DEFAULT NULL COMMENT '绗笁鏂瑰崟鎹垱寤烘椂闂� (createdTimeGMT)',
- `project_name` varchar(128) DEFAULT NULL COMMENT '椤圭洰鍚嶇О/瀹㈡埛 (textField_lbkozohg)',
- `batch_no` varchar(128) DEFAULT NULL COMMENT '鎵规/鍏宠仈缂栧彿 (textField_l7fytfco)',
- `contact_person` varchar(128) DEFAULT NULL COMMENT '鑱旂郴浜�/澶囨敞 (textareaField_l7fytfcy)',
- `ext_text1` varchar(128) DEFAULT NULL COMMENT '棰勭暀鏂囨湰1 (textField_l7fytfcx)',
- `ext_text2` varchar(128) DEFAULT NULL COMMENT '棰勭暀鏂囨湰2 (textField_lbknlltp)',
- `create_user` bigint(20) DEFAULT NULL COMMENT '鍒涘缓鑰�',
- `create_time` datetime DEFAULT NULL COMMENT '鍒涘缓鏃堕棿',
- `update_user` bigint(20) DEFAULT NULL COMMENT '鏇存柊鑰�',
- `update_time` datetime DEFAULT NULL COMMENT '鏇存柊鏃堕棿',
- `remark` varchar(500) DEFAULT NULL COMMENT '澶囨敞',
+ id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '涓婚敭',
+ form_instance_id VARCHAR(64) COMMENT '琛ㄥ崟瀹炰緥ID',
+ serial_no VARCHAR(50) COMMENT '娴佹按鍙�',
+ apply_no VARCHAR(100) COMMENT '鐢宠鍗曠紪鍙�',
+ customer_name VARCHAR(200) COMMENT '瀹㈡埛鍚嶇О',
+ material_code VARCHAR(100) COMMENT '鐗╂枡缂栫爜',
+ product_name VARCHAR(200) COMMENT '浜у搧鍚嶇О',
+ product_spec VARCHAR(200) COMMENT '浜у搧瑙勬牸',
+ length INT COMMENT '闀�',
+ width INT COMMENT '瀹�',
+ height INT COMMENT '楂�',
+ quantity INT COMMENT '鍧楁暟',
+ total_count INT COMMENT '姝ゆ缁熻鏁伴噺',
+ volume DECIMAL(10, 4) COMMENT '鏂规暟',
+ strength VARCHAR(50) COMMENT '寮哄害',
+ start_date DATETIME COMMENT '寮�濮嬫棩鏈�',
+ end_date DATETIME COMMENT '缁撴潫鏃ユ湡',
+ submitter VARCHAR(100) COMMENT '鎻愪氦浜�',
+ submit_org VARCHAR(200) COMMENT '鎻愪氦浜虹粍缁�',
+ remark_one TEXT COMMENT '澶囨敞1',
+ remark_two TEXT COMMENT '澶囨敞2',
+ creator_name VARCHAR(100) COMMENT '鍒涘缓浜�',
+ modifier_name VARCHAR(100) COMMENT '淇敼浜�',
+ form_created_time DATETIME COMMENT '琛ㄥ崟鍒涘缓鏃堕棿',
+ form_modified_time DATETIME COMMENT '琛ㄥ崟淇敼鏃堕棿',
+ data_sync_type TINYINT DEFAULT 1 COMMENT '鏁版嵁鍚屾绫诲瀷锛�1=鎵嬪姩 2=瀹氭椂浠诲姟',
+ data_source_type TINYINT DEFAULT 1 COMMENT '鏁版嵁鏉ユ簮绫诲瀷锛�1=閿�鍞鍗� 2=閿�鍞娴�',
+ create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '鏁版嵁搴撳垱寤烘椂闂�',
+ update_time DATETIME COMMENT '鏁版嵁搴撴洿鏂版椂闂�',
- PRIMARY KEY (`id`),
- UNIQUE KEY `uk_form_instance_id` (`form_instance_id`)
+ INDEX idx_apply_no (apply_no),
+ INDEX idx_serial_no (serial_no),
+ INDEX idx_form_instance_id (form_instance_id),
+ INDEX idx_material_code (material_code)
) ENGINE = InnoDB
- DEFAULT CHARSET = utf8mb4 COMMENT ='閿�鍞敓浜ч渶姹�';
+ DEFAULT CHARSET = utf8mb4 COMMENT ='閿�鍞敓浜ц鍒�';
-CREATE TABLE `prod_demand_detail`
+CREATE TABLE `product-inventory-management-zsjc`.`energy`
(
- `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '鑷涓婚敭',
- `tenant_id` bigint(20) NOT NULL COMMENT '绉熸埛缂栧彿',
- `prod_demand_main_id` bigint(20) NOT NULL COMMENT '鐖剁被鍏宠仈ID',
- `material_code` varchar(64) DEFAULT NULL COMMENT '鐗╂枡缂栫爜 (textField_l9xo62q5)',
- `material_category` varchar(64) DEFAULT NULL COMMENT '鐗╂枡鍒嗙被 (textField_l9xo62q7)',
- `spec_desc` varchar(128) DEFAULT NULL COMMENT '瑙勬牸鎻忚堪 (textField_l9xo62q8)',
- `grade_type` varchar(32) DEFAULT NULL COMMENT '绛夌骇/绫诲瀷 (radioField_m9urarr2)',
- `length` decimal(10, 2) DEFAULT NULL COMMENT '闀垮害 (numberField_lb7lgatg)',
- `width` decimal(10, 2) DEFAULT NULL COMMENT '瀹藉害 (numberField_lb7lgath)',
- `thickness` decimal(10, 2) DEFAULT NULL COMMENT '鍘氬害 (numberField_lb7lgati)',
- `quantity` int(11) DEFAULT NULL COMMENT '鏁伴噺 (numberField_lb7lgatj)',
- `volume` decimal(10, 4) DEFAULT NULL COMMENT '鎬讳綋绉� (numberField_l7fytfd3)',
- `date_range` varchar(255) DEFAULT NULL COMMENT '鏃堕棿鍖洪棿鏁扮粍瀛楃涓� (cascadeDateField_lfxqqluw)',
- `association_receipt` text DEFAULT NULL COMMENT '鍏宠仈鐨勫叾浠栧崟鎹� (associationFormField_l9xo62q6_id)',
- `create_user` bigint(20) DEFAULT NULL COMMENT '鍒涘缓鑰�',
- `create_time` datetime DEFAULT NULL COMMENT '鍒涘缓鏃堕棿',
- `update_user` bigint(20) DEFAULT NULL COMMENT '鏇存柊鑰�',
- `update_time` datetime DEFAULT NULL COMMENT '鏇存柊鏃堕棿',
- `remark` varchar(500) DEFAULT NULL COMMENT '澶囨敞',
+ `id` int NOT NULL AUTO_INCREMENT,
+ `energy_tyep` varchar(255) NULL COMMENT '鑳芥簮绫诲瀷',
+ `energy_name` varchar(255) NULL COMMENT '鑳芥簮鍚嶇О',
+ `unit` varchar(255) NULL COMMENT '鍗曚綅',
+ `unit_price` decimal(10, 2) NULL COMMENT '鍗曚环',
+ `remark` varchar(255) NULL COMMENT '澶囨敞',
+ `create_time` datetime(0) NULL DEFAULT NULL COMMENT '鍒涘缓鏃堕棿',
+ `update_time` datetime(0) NULL DEFAULT NULL COMMENT '鏇存柊鏃堕棿',
+ `create_user` bigint NULL DEFAULT NULL COMMENT '鍒涘缓鑰�',
+ `update_user` bigint NULL DEFAULT NULL COMMENT '鏇存柊鑰�',
+ PRIMARY KEY (`id`)
+) COMMENT = '鑳芥簮绫诲瀷';
+CREATE TABLE `product-inventory-management-zsjc`.`energy_consumption_detail`
+(
+ `id` int NOT NULL AUTO_INCREMENT,
+ `energy_id` int NULL COMMENT '澶栨鍏宠仈鑳芥簮绫诲瀷id',
+ `type` varchar(255) NULL COMMENT '鍔炲叕/鐢熶骇',
+ `meter_reading_location` varchar(255) NULL COMMENT '鎶勮〃浣嶇疆',
+ `meter_reading_date` date NULL COMMENT '鎶勮〃鏃ユ湡',
+ `start_code` decimal(10, 2) NULL COMMENT '璧风爜',
+ `stop_code` decimal(10, 2) NULL COMMENT '姝㈢爜',
+ `dosage` decimal(10, 2) NULL COMMENT '鐢ㄩ噺',
+ `remark` varchar(255) NULL COMMENT '澶囨敞',
+ `create_time` datetime(0) NULL DEFAULT NULL COMMENT '鍒涘缓鏃堕棿',
+ `update_time` datetime(0) NULL DEFAULT NULL COMMENT '鏇存柊鏃堕棿',
+ `create_user` bigint NULL DEFAULT NULL COMMENT '鍒涘缓鑰�',
+ `update_user` bigint NULL DEFAULT NULL COMMENT '鏇存柊鑰�',
+ PRIMARY KEY (`id`)
+) COMMENT = '鑳芥簮绫诲瀷-鑳借�楁妱琛ㄦ槑缁�';
+
+CREATE TABLE `product-inventory-management-zsjc`.`energy_consumption_detail_file`
+(
+ `id` int NOT NULL AUTO_INCREMENT,
+ `energy_consumption_detail_id` int NULL COMMENT '鍏宠仈澶栭敭鑳借�楁妱琛╥d',
+ `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
+ `url` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
+ `file_size` double NULL DEFAULT NULL,
+ `create_time` timestamp(0) NULL DEFAULT NULL,
+ `create_user` int NULL DEFAULT NULL,
+ `update_time` timestamp(0) NULL DEFAULT NULL,
+ `update_user` int NULL DEFAULT NULL,
+ PRIMARY KEY (`id`)
+) COMMENT = '鑳芥簮绫诲瀷-鑳借�楁妱琛ㄦ槑缁哶闄勪欢';
+
+alter table product_order
+ drop column sales_ledger_id,
+ drop column sale_ledger_product_id,
+ drop column product_model_id;
+
+alter table production_plan
+ add assigned_quantity DECIMAL(10, 4) default 0 not null COMMENT '涓嬪彂鏁伴噺';
+
+alter table product_order
+ add plan_complete_time date NULL DEFAULT NULL COMMENT '璁″垝瀹屾垚鏃堕棿';
+
+# 鐢熶骇璁㈠崟涓庣敓浜ц鍒掑叧鑱旇〃
+drop table if exists product_order_plan;
+create table product_order_plan
+(
+ id bigint auto_increment primary key,
+ product_order_id bigint not null default 0 comment '鐢熶骇璁㈠崟id',
+ production_plan_id bigint not null default 0 comment '鐢熶骇璁″垝id',
+ create_time datetime null comment '褰曞叆鏃堕棿',
+ update_time datetime null comment '鏇存柊鏃堕棿',
+ assigned_quantity DECIMAL(10, 4) default 0 not null comment '涓嬪彂鏁伴噺',
+ index idx_product_order_id (product_order_id),
+ index idx_production_plan_id (production_plan_id),
+ unique idx_product_order_production_plan (product_order_id, production_plan_id)
+);
+
+CREATE TABLE `product_material`
+(
+ `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '涓婚敭ID',
+ `tenant_id` BIGINT DEFAULT NULL COMMENT '绉熸埛ID',
+ `material_type_id` INT DEFAULT NULL COMMENT '鐗╂枡绫诲瀷ID',
+ `inventory_category_id` INT DEFAULT NULL COMMENT '瀛樿揣绫诲埆ID',
+ `material_name` VARCHAR(255) NOT NULL COMMENT '鐗╂枡鍚嶇О',
+ `base_unit` VARCHAR(50) DEFAULT NULL COMMENT '鍩烘湰鍗曚綅',
+ `remark` TEXT COMMENT '澶囨敞',
+ `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
+ `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
- KEY `idx_prod_demand_main_id` (`prod_demand_main_id`)
+ KEY `idx_type_id` (`material_type_id`),
+ KEY `idx_cat_id` (`inventory_category_id`),
+ UNIQUE KEY `uk_material_name` (`material_name`)
+) ENGINE = INNODB
+ DEFAULT CHARSET = utf8mb4 COMMENT = '鐗╂枡涓昏〃';
+CREATE TABLE `product_material_sku`
+(
+ `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '涓婚敭ID',
+ `material_id` BIGINT NOT NULL COMMENT '鐗╂枡ID',
+ `identifier_code` VARCHAR(100) DEFAULT NULL COMMENT '鏍囪瘑缂栫爜',
+ `material_code` VARCHAR(100) DEFAULT NULL COMMENT '鐗╂枡缂栫爜',
+ `specification` VARCHAR(255) DEFAULT NULL COMMENT '瑙勬牸鍨嬪彿',
+ `supply_type` VARCHAR(20) DEFAULT NULL COMMENT '渚涘簲鏂瑰紡',
+ `originator_name` VARCHAR(100) DEFAULT NULL COMMENT '鎻愪氦浜哄鍚�',
+ `originator_org` VARCHAR(255) DEFAULT '瀹佸涓垱缁胯兘瀹炰笟闆嗗洟鏈夐檺鍏徃',
+ `form_instance_id` VARCHAR(100) DEFAULT NULL COMMENT '瀹滄惌琛ㄥ崟瀹炰緥ID',
+ `form_modified_time` DATETIME DEFAULT NULL COMMENT '瀹滄惌淇敼鏃堕棿',
+ `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
+ `update_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
+ PRIMARY KEY (`id`),
+ KEY `idx_material_id` (`material_id`),
+ UNIQUE KEY `uk_material_spec` (`material_id`, `specification`),
+ CONSTRAINT `fk_material` FOREIGN KEY (`material_id`) REFERENCES `product_material` (`id`) ON DELETE CASCADE
+) ENGINE = INNODB
+ DEFAULT CHARSET = utf8mb4 COMMENT = '鐗╂枡瑙勬牸琛�';
+
+ALTER TABLE product_material_sku
+ DROP FOREIGN KEY fk_material;
+
+DROP TABLE IF EXISTS `product_material_config`;
+CREATE TABLE `product_material_config`
+(
+ `id` int NOT NULL AUTO_INCREMENT,
+ `config_type` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '鍖哄垎绫诲瀷: MATERIAL_TYPE 鎴� INVENTORY_CAT',
+ `config_name` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '鏄剧ず鐨勫悕绉�',
+ PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB
- DEFAULT CHARSET = utf8mb4 COMMENT ='閿�鍞敓浜ч渶姹俖鏄庣粏琛�';
\ No newline at end of file
+ AUTO_INCREMENT = 39
+ CHARACTER SET = utf8mb3
+ COLLATE = utf8mb3_general_ci COMMENT = '鐗╂枡淇℃伅琛ㄩ厤缃〃'
+ ROW_FORMAT = Dynamic;
+
+SET FOREIGN_KEY_CHECKS = 1;
--
Gitblit v1.9.3