From 004cb343aeac53839bb390df3d1c6f2ba6b0d41e Mon Sep 17 00:00:00 2001
From: liu <2021943741@qq.com>
Date: 星期五, 16 十月 2026 17:58:58 +0800
Subject: [PATCH] docs(mes): 补袋码绑定托盘免先录批次的数据库自检脚本
---
sql/mysql/mes_bag_code_bind_pallet_without_batch.sql | 107 +++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 107 insertions(+), 0 deletions(-)
diff --git a/sql/mysql/mes_bag_code_bind_pallet_without_batch.sql b/sql/mysql/mes_bag_code_bind_pallet_without_batch.sql
new file mode 100644
index 0000000..4a12b60
--- /dev/null
+++ b/sql/mysql/mes_bag_code_bind_pallet_without_batch.sql
@@ -0,0 +1,107 @@
+-- =============================================================================
+-- 琚嬬爜缁戝畾鎵樼洏锛堝厤鍏堝綍鐢熶骇鎵规锛夆�斺�� 鏁版嵁搴撹剼鏈�
+-- 鏃ユ湡锛�2026-10-16
+-- 鍏宠仈鎻愪氦锛�66517edf feat(mes): 琚嬬爜缁戝畾鎵樼洏鍏嶅厛褰曠敓浜ф壒娆�
+--
+-- 銆愮粨璁恒�戞湰娆℃敼鍔� **涓嶉渶瑕佷换浣� DDL锛屼篃涓嶉渶瑕佹柊澧炶彍鍗�/鏉冮檺鏁版嵁**銆�
+-- 鏈剼鏈彧鍋氶儴缃插墠鑷涓庡洖褰掓牳瀵癸紝鐩存帴鎵ц鍗冲彲锛屼笉浜х敓浠讳綍缁撴瀯鍙樻洿銆�
+--
+-- 渚濇嵁锛�
+-- 1. 鏂版帴鍙� GET /mes/pro/pallet/bindable-list 澶嶇敤宸叉湁鏉冮檺 mes:pro-pallet:query
+-- 锛坰ystem_menu.id = 602625锛夛紝鏈紩鍏ユ柊鏉冮檺鐮侊紝鏁呮棤闇� INSERT system_menu /
+-- system_role_menu銆�
+-- 2. 璇ユ帴鍙ｅ彧鏌ヨ mes_pro_pallet 鐨勭幇鏈夊垪锛�
+-- status / item_id / batch_id / batch_code / code / temp_code / bag_count / deleted锛�
+-- 鏈柊澧炶〃銆佸垪鎴栫储寮曘��
+-- 3. 銆岀粦瀹氭墭鐩樸�嶇殑鍐欒矾寰勶紙鍚湭褰曟壒娆¤鐮佽嚜鍔ㄨˉ褰曟壒娆★級澶嶇敤鏃㈡湁
+-- mes_pro_bag_code / mes_pro_pallet / mes_pro_batch 鍒楋紝鏃犵粨鏋勫彉鍖栥��
+-- =============================================================================
+
+
+-- -----------------------------------------------------------------------------
+-- 鑷 1锛氭柊鎺ュ彛渚濊禆鐨勬潈闄愬凡瀛樺湪涓斿惎鐢紙鏈熸湜杩斿洖 1 琛岋紝status = 0锛�
+-- -----------------------------------------------------------------------------
+SELECT id, name, permission, type, status
+FROM system_menu
+WHERE permission = 'mes:pro-pallet:query';
+
+
+-- -----------------------------------------------------------------------------
+-- 鑷 2锛氭帴鍙ｇ敤鍒扮殑 mes_pro_pallet 鍒楅綈鍏紙鏈熸湜杩斿洖 9 琛岋級
+-- -----------------------------------------------------------------------------
+SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_COMMENT
+FROM information_schema.COLUMNS
+WHERE TABLE_SCHEMA = DATABASE()
+ AND TABLE_NAME = 'mes_pro_pallet'
+ AND COLUMN_NAME IN (
+ 'id', 'status', 'item_id', 'batch_id', 'batch_code',
+ 'code', 'temp_code', 'bag_count', 'deleted'
+ )
+ORDER BY COLUMN_NAME;
+
+
+-- -----------------------------------------------------------------------------
+-- 鑷 3锛氱‘璁ゃ�岀粦瀹氭墭鐩樸�嶆寜閽潈闄愪笌鎵樼洏鏌ヨ鏉冮檺鐨勬寔鏈夎鑹�
+-- 璇存槑锛歜indable-list 闇�瑕� mes:pro-pallet:query锛涚粦瀹氭寜閽渶瑕� mes:pro-bag-code:update銆�
+-- 鑻ユ煇瑙掕壊姝ゅ墠鑳芥甯告墦寮�銆岀粦瀹氭墭鐩樸�嶅脊绐楀苟鎷夊埌鎵樼洏涓嬫媺锛岃鏄庡畠宸叉寔鏈�
+-- mes:pro-pallet:query锛堟棫瀹炵幇涓嬫媺璧扮殑涔熸槸鎵樼洏鏌ヨ鎺ュ彛锛夛紝鏃犻渶琛ユ巿鏉冦��
+-- 瓒呯骇绠＄悊鍛橈紙super_admin锛夎蛋鏀捐閫昏緫锛屼笉浣撶幇鍦� system_role_menu 涓��
+-- -----------------------------------------------------------------------------
+SELECT r.id AS role_id, r.name AS role_name, r.code AS role_code, m.permission
+FROM system_role r
+JOIN system_role_menu rm ON rm.role_id = r.id
+JOIN system_menu m ON m.id = rm.menu_id
+WHERE m.permission IN ('mes:pro-pallet:query', 'mes:pro-bag-code:update')
+ORDER BY r.id, m.permission;
+
+
+-- -----------------------------------------------------------------------------
+-- 鍙�夋巿鏉冿細浠呭綋闇�瑕佺粰銆岄潪瓒呯骇绠＄悊鍛樿鑹层�嶅紑閫氳祴鐮佺鐞嗘椂鎵ц锛堥粯璁ゆ棤闇�鎵ц锛�
+-- 鎶� @role_id 鎹㈡垚鐩爣瑙掕壊缂栧彿锛涘凡鎺堟潈鐨勮彍鍗曠敱 NOT EXISTS 璺宠繃锛屽彲閲嶅鎵ц銆�
+-- -----------------------------------------------------------------------------
+-- SET @role_id = 2;
+--
+-- INSERT INTO system_role_menu (role_id, menu_id, creator, create_time, updater, update_time, deleted)
+-- SELECT @role_id, m.id, 'admin', NOW(), 'admin', NOW(), b'0'
+-- FROM system_menu m
+-- WHERE m.permission = 'mes:pro-pallet:query'
+-- AND NOT EXISTS (
+-- SELECT 1 FROM system_role_menu rm
+-- WHERE rm.role_id = @role_id AND rm.menu_id = m.id
+-- );
+
+
+-- -----------------------------------------------------------------------------
+-- 鍥炲綊鏍稿锛氭壒娆℃眹鎬诲瓧娈典笌鏄庣粏鏄惁涓�鑷�
+-- 鏀归�犲悗銆屾湭褰曟壒娆¤鐮佺粦鍒版煇鎵规鐨勬寮忔墭鐩樸�嶄細鑷姩琛ュ綍鎵规锛岄渶纭
+-- mes_pro_batch.bag_quantity / pallet_quantity 涓庢槑缁嗗悓姝ャ��
+-- 鐢ㄦ硶锛氭妸 @batch_id 鎹㈡垚楠岃瘉鐢ㄦ壒娆＄紪鍙凤紝瀵规瘮缁戝畾鎿嶄綔鍓嶅悗鐨勮緭鍑恒��
+-- 鏈熸湜锛歜ag_quantity = 瀹為檯鏈夋晥琚嬬爜鏁帮紝pallet_quantity = 瀹為檯鎵樼洏鏁般��
+-- -----------------------------------------------------------------------------
+-- SET @batch_id = 123;
+--
+-- SELECT b.id,
+-- b.code,
+-- b.status,
+-- b.item_id,
+-- b.bag_quantity AS 鎵规鐧昏琚嬫暟,
+-- (SELECT COUNT(*) FROM mes_pro_bag_code bc
+-- WHERE bc.batch_id = b.id AND bc.deleted = b'0' AND bc.status <> 30) AS 瀹為檯鏈夋晥琚嬬爜鏁�,
+-- b.pallet_quantity AS 鎵规鐧昏鎵樼洏鏁�,
+-- (SELECT COUNT(*) FROM mes_pro_pallet p
+-- WHERE p.batch_id = b.id AND p.deleted = b'0') AS 瀹為檯鎵樼洏鏁�
+-- FROM mes_pro_batch b
+-- WHERE b.id = @batch_id;
+
+
+-- -----------------------------------------------------------------------------
+-- 鍥炲綊鏍稿锛氭壘鍑恒�屾墭鐩樻湁鎵规銆佽鐮佸嵈鏃犳壒娆°�嶇殑婕忚ˉ褰曟暟鎹�
+-- 姝ｅ父搴斾负 0 琛屻�傝嫢瀛樺湪锛岃鏄庢湁琚嬬爜鎸傚湪鏌愭壒娆＄殑鎵樼洏涓婂嵈娌″綍鍏ユ壒娆°��
+-- -----------------------------------------------------------------------------
+SELECT p.id AS pallet_id, p.code AS pallet_code, p.batch_id, p.batch_code,
+ COUNT(bc.id) AS 鏈綍鎵规琚嬬爜鏁�
+FROM mes_pro_pallet p
+JOIN mes_pro_bag_code bc
+ ON bc.pallet_id = p.id AND bc.deleted = b'0' AND bc.batch_id IS NULL
+WHERE p.deleted = b'0' AND p.batch_id IS NOT NULL
+GROUP BY p.id, p.code, p.batch_id, p.batch_code;
--
Gitblit v1.9.3