From 4ca4444ad33a2a1a51f5c06e78ee9365dfb1eed6 Mon Sep 17 00:00:00 2001
From: liu <2021943741@qq.com>
Date: 星期四, 08 十月 2026 17:11:11 +0800
Subject: [PATCH] feat(mes): 种子二维码标签国标四要素合规改造
---
sql/mysql/mes_wm_qualified_warehouse.sql | 85 ++++++++++++++++++++++++++++++++++++++++++
1 files changed, 85 insertions(+), 0 deletions(-)
diff --git a/sql/mysql/mes_wm_qualified_warehouse.sql b/sql/mysql/mes_wm_qualified_warehouse.sql
new file mode 100644
index 0000000..29379d1
--- /dev/null
+++ b/sql/mysql/mes_wm_qualified_warehouse.sql
@@ -0,0 +1,85 @@
+-- =====================================================================
+-- 鏂板缓鍚堟牸浠撳簱 + 浠撳簱绫诲瀷 + 璐ㄦ鍏ュ悎鏍煎簱鏀归��
+--
+-- 1) mes_wm_warehouse 澧炲姞 type 瀛楁锛�10 鏆傚瓨搴� / 20 鍚堟牸搴擄級
+-- 2) 鐜版湁浠撳簱鍒嗙被锛氳櫄鎷熺嚎杈逛粨搴�=10锛堟殏瀛樺簱锛夛紝浜у搧搴�/鐗╂枡搴�=20锛堝悎鏍煎簱锛�
+-- 3) 鏂板缓"鎴愬搧鍚堟牸搴�"(type=20) + 涓�涓簱鍖� + 涓�涓簱浣�
+-- 4) 铏氭嫙浠撳簱涓� stock_state=20 鐨勫悎鏍煎簱瀛樿縼绉诲埌鏂板悎鏍煎簱
+--
+-- 璇存槑锛氭湰鑴氭湰骞傜瓑锛屽彲閲嶅鎵ц锛涙棤绉熸埛瀛楁銆�
+-- =====================================================================
+
+-- 1) 澧炲姞浠撳簱绫诲瀷瀛楁锛圡ySQL 涓嶆敮鎸� ALTER ADD COLUMN IF NOT EXISTS锛屽厛鏌ュ瓨鍦ㄦ�э級
+SET @type_exists = (
+ SELECT COUNT(*)
+ FROM information_schema.columns
+ WHERE table_schema = DATABASE()
+ AND table_name = 'mes_wm_warehouse'
+ AND column_name = 'type'
+);
+SET @type_sql = IF(
+ @type_exists = 0,
+ 'ALTER TABLE mes_wm_warehouse ADD COLUMN type tinyint NOT NULL DEFAULT 10 COMMENT ''浠撳簱绫诲瀷锛�10鏆傚瓨搴擄紝20鍚堟牸搴�'' AFTER frozen',
+ 'SELECT 1'
+);
+PREPARE type_stmt FROM @type_sql;
+EXECUTE type_stmt;
+DEALLOCATE PREPARE type_stmt;
+
+-- 2) 鐜版湁浠撳簱鍒嗙被
+UPDATE mes_wm_warehouse SET type = 10 WHERE code = 'WIP_VIRTUAL_WAREHOUSE' AND deleted = 0;
+UPDATE mes_wm_warehouse SET type = 20 WHERE code IN ('1212', 'WHS000001') AND deleted = 0;
+
+-- 3) 鏂板缓"鎴愬搧鍚堟牸搴�"锛坱ype=20锛�
+SET @wh_exists = (
+ SELECT COUNT(*) FROM mes_wm_warehouse WHERE code = 'FG_QUALIFIED_WH' AND deleted = 0
+);
+INSERT INTO mes_wm_warehouse (code, name, frozen, type, remark)
+SELECT 'FG_QUALIFIED_WH', '鎴愬搧鍚堟牸搴�', 0, 20, '璐ㄦ鍚堟牸鐩存帴鍏ュ簱鐨勭洰鏍囦粨搴擄紙type=20 鍚堟牸搴擄級'
+WHERE @wh_exists = 0;
+SET @wh_id = (SELECT id FROM mes_wm_warehouse WHERE code = 'FG_QUALIFIED_WH' AND deleted = 0 LIMIT 1);
+
+-- 鏂板缓"鎴愬搧鍚堟牸搴撳尯"
+SET @loc_exists = (
+ SELECT COUNT(*) FROM mes_wm_warehouse_location WHERE code = 'FG_QUALIFIED_WH_LOC' AND deleted = 0
+);
+INSERT INTO mes_wm_warehouse_location (code, name, warehouse_id, frozen, remark)
+SELECT 'FG_QUALIFIED_WH_LOC', '鎴愬搧鍚堟牸搴撳尯', @wh_id, 0, '璐ㄦ鍚堟牸鍏ュ簱榛樿搴撳尯'
+WHERE @loc_exists = 0;
+SET @loc_id = (SELECT id FROM mes_wm_warehouse_location WHERE code = 'FG_QUALIFIED_WH_LOC' AND deleted = 0 LIMIT 1);
+
+-- 鏂板缓"鎴愬搧鍚堟牸搴撲綅"
+SET @area_exists = (
+ SELECT COUNT(*) FROM mes_wm_warehouse_area WHERE code = 'FG_QUALIFIED_WH_AREA' AND deleted = 0
+);
+INSERT INTO mes_wm_warehouse_area (code, name, location_id, frozen, remark)
+SELECT 'FG_QUALIFIED_WH_AREA', '鎴愬搧鍚堟牸搴撲綅', @loc_id, 0, '璐ㄦ鍚堟牸鍏ュ簱榛樿搴撲綅'
+WHERE @area_exists = 0;
+SET @area_id = (SELECT id FROM mes_wm_warehouse_area WHERE code = 'FG_QUALIFIED_WH_AREA' AND deleted = 0 LIMIT 1);
+
+-- 4) 杩佺Щ铏氭嫙浠撳簱鍚堟牸搴撳瓨鍒版柊鍚堟牸搴�
+-- 4.1 鑻ユ柊鍚堟牸搴撳凡瀛樺湪鍚� (item_id, batch_id, vendor_id) 鐨� state=20 璁板綍锛屽厛鏁伴噺绱姞鍚堝苟
+UPDATE mes_wm_material_stock t
+JOIN mes_wm_material_stock s
+ ON s.warehouse_id = 3 AND s.stock_state = 20 AND s.deleted = 0
+ AND t.warehouse_id = @wh_id AND t.stock_state = 20 AND t.deleted = 0
+ AND t.item_id = s.item_id
+ AND IFNULL(t.batch_id, 0) = IFNULL(s.batch_id, 0)
+ AND IFNULL(t.vendor_id, 0) = IFNULL(s.vendor_id, 0)
+SET t.quantity = t.quantity + s.quantity,
+ t.reserved_quantity = t.reserved_quantity + s.reserved_quantity,
+ t.frozen_quantity = t.frozen_quantity + s.frozen_quantity;
+
+-- 4.2 鍒犻櫎宸插悎骞跺埌鏂板悎鏍煎簱鐨勬簮璁板綍锛堣櫄鎷熶粨搴撲腑锛�
+DELETE s FROM mes_wm_material_stock s
+JOIN mes_wm_material_stock t
+ ON s.warehouse_id = 3 AND s.stock_state = 20 AND s.deleted = 0
+ AND t.warehouse_id = @wh_id AND t.stock_state = 20 AND t.deleted = 0
+ AND t.item_id = s.item_id
+ AND IFNULL(t.batch_id, 0) = IFNULL(s.batch_id, 0)
+ AND IFNULL(t.vendor_id, 0) = IFNULL(s.vendor_id, 0);
+
+-- 4.3 鍏朵綑鍚堟牸搴撳瓨鐩存帴鏀逛粨搴�/搴撳尯/搴撲綅
+UPDATE mes_wm_material_stock
+SET warehouse_id = @wh_id, location_id = @loc_id, area_id = @area_id
+WHERE warehouse_id = 3 AND stock_state = 20 AND deleted = 0;
--
Gitblit v1.9.3