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): 种子二维码标签国标四要素合规改造 --- docs/sql/trace_platform_01_schema.sql | 79 +++++++++++++++++++++++++++++++++++++++ 1 files changed, 79 insertions(+), 0 deletions(-) diff --git a/docs/sql/trace_platform_01_schema.sql b/docs/sql/trace_platform_01_schema.sql new file mode 100644 index 0000000..b4bd50f --- /dev/null +++ b/docs/sql/trace_platform_01_schema.sql @@ -0,0 +1,79 @@ +-- 鎵规杩芥函鏁版嵁妯″瀷琛ュ己 +-- 璇存槑锛氬瓧娈靛厛鍏佽涓虹┖锛屽巻鍙叉暟鎹洖濉畬鎴愬悗鍐嶈瘎浼版槸鍚︽敹绱х害鏉熴�� +-- 娉ㄦ剰锛氶」鐩笉浣跨敤 tenant_id锛涙墽琛屽墠璇风‘璁ゅ瓧娈靛拰绱㈠紩涓嶅瓨鍦ㄣ�� + +ALTER TABLE mes_wm_batch + ADD COLUMN pro_batch_id BIGINT NULL COMMENT '瀵瑰簲鐢熶骇鎵规ID' AFTER id; +ALTER TABLE mes_wm_batch + ADD INDEX idx_mes_wm_batch_pro_batch_id (pro_batch_id); + +ALTER TABLE mes_wm_sn + ADD COLUMN pro_batch_id BIGINT NULL COMMENT '鐢熶骇鎵规ID' AFTER id; +ALTER TABLE mes_wm_sn + ADD COLUMN bag_code_id BIGINT NULL COMMENT '琚嬬爜ID' AFTER pro_batch_id; +ALTER TABLE mes_wm_sn + ADD COLUMN pallet_id BIGINT NULL COMMENT '鎵樼洏ID' AFTER bag_code_id; +ALTER TABLE mes_wm_sn + ADD COLUMN sales_id BIGINT NULL COMMENT '閿�鍞嚭搴撳崟ID' AFTER pallet_id; +ALTER TABLE mes_wm_sn + ADD INDEX idx_mes_wm_sn_pro_batch_id (pro_batch_id); +ALTER TABLE mes_wm_sn + ADD INDEX idx_mes_wm_sn_bag_code_id (bag_code_id); + +ALTER TABLE mes_qc_seed_quality + ADD COLUMN batch_id BIGINT NULL COMMENT '鐢熶骇鎵规ID' AFTER item_id; +ALTER TABLE mes_qc_seed_quality + ADD INDEX idx_mes_qc_seed_quality_batch_id (batch_id); + +CREATE TABLE mes_trace_event ( + id BIGINT NOT NULL AUTO_INCREMENT COMMENT '涓婚敭', + event_no VARCHAR(64) NOT NULL COMMENT '浜嬩欢缂栧彿', + event_type VARCHAR(32) NOT NULL COMMENT '浜嬩欢绫诲瀷', + event_category VARCHAR(32) NULL COMMENT '浜嬩欢鍒嗙被', + event_time DATETIME NOT NULL COMMENT '浜嬩欢鏃堕棿', + pro_batch_id BIGINT NULL COMMENT '鐢熶骇鎵规ID', + wm_batch_id BIGINT NULL COMMENT '浠撳偍鎵规ID', + bag_code_id BIGINT NULL COMMENT '琚嬬爜ID', + pallet_id BIGINT NULL COMMENT '鎵樼洏ID', + sn_id BIGINT NULL COMMENT 'SN ID', + item_id BIGINT NULL COMMENT '鐗╂枡ID', + biz_type VARCHAR(32) NULL COMMENT '涓氬姟绫诲瀷', + biz_id BIGINT NULL COMMENT '涓氬姟ID', + biz_code VARCHAR(100) NULL COMMENT '涓氬姟缂栫爜', + biz_line_id BIGINT NULL COMMENT '涓氬姟鏄庣粏ID', + from_node_type VARCHAR(32) NULL COMMENT '鏉ユ簮鑺傜偣绫诲瀷', + from_node_id BIGINT NULL COMMENT '鏉ユ簮鑺傜偣ID', + from_node_code VARCHAR(100) NULL COMMENT '鏉ユ簮鑺傜偣缂栫爜', + to_node_type VARCHAR(32) NULL COMMENT '鐩爣鑺傜偣绫诲瀷', + to_node_id BIGINT NULL COMMENT '鐩爣鑺傜偣ID', + to_node_code VARCHAR(100) NULL COMMENT '鐩爣鑺傜偣缂栫爜', + quantity DECIMAL(18, 6) NULL COMMENT '鏁伴噺', + warehouse_id BIGINT NULL COMMENT '浠撳簱ID', + location_id BIGINT NULL COMMENT '搴撳尯ID', + area_id BIGINT NULL COMMENT '搴撲綅ID', + line_id BIGINT NULL COMMENT '浜х嚎ID', + work_order_id BIGINT NULL COMMENT '宸ュ崟ID', + process_id BIGINT NULL COMMENT '宸ュ簭ID', + sales_id BIGINT NULL COMMENT '閿�鍞嚭搴撳崟ID', + quality_id BIGINT NULL COMMENT '璐ㄦ璁板綍ID', + operator_id BIGINT NULL COMMENT '鎿嶄綔浜篒D', + payload_json JSON NULL COMMENT '浜嬩欢鎵╁睍鏁版嵁', + content_hash VARCHAR(64) NULL COMMENT '浜嬩欢鍐呭SHA-256鎽樿', + prev_hash VARCHAR(64) NULL COMMENT '鍓嶄竴浜嬩欢鎽樿', + creator VARCHAR(64) NULL DEFAULT '' COMMENT '鍒涘缓鑰�', + create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿', + updater VARCHAR(64) NULL DEFAULT '' COMMENT '鏇存柊鑰�', + update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '鏇存柊鏃堕棿', + deleted BIT(1) NOT NULL DEFAULT b'0' COMMENT '鏄惁鍒犻櫎', + PRIMARY KEY (id), + UNIQUE KEY uk_mes_trace_event_no (event_no), + KEY idx_mes_trace_event_batch_time (pro_batch_id, event_time), + KEY idx_mes_trace_event_biz (biz_type, biz_id, biz_line_id), + KEY idx_mes_trace_event_nodes (from_node_id, to_node_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='MES缁熶竴杩芥函浜嬩欢'; + +-- 鍘嗗彶鏁版嵁鍥炲～绀轰緥锛岀‘璁ゆ暟鎹川閲忓悗鎵ц锛� +-- UPDATE mes_wm_batch w JOIN mes_pro_batch p ON p.code = w.code AND p.deleted = b'0' +-- SET w.pro_batch_id = p.id WHERE w.pro_batch_id IS NULL AND w.deleted = b'0'; +-- UPDATE mes_qc_seed_quality q JOIN mes_pro_batch p ON p.code = q.batch_code AND p.deleted = b'0' +-- SET q.batch_id = p.id WHERE q.batch_id IS NULL AND q.deleted = b'0'; -- Gitblit v1.9.3