From d607ad871d514d8cf4892939d1e8256a084b1e2e Mon Sep 17 00:00:00 2001
From: 云 <2163098428@qq.com>
Date: 星期四, 11 六月 2026 15:42:04 +0800
Subject: [PATCH] feat(database): 添加生产订单完整删除功能
---
docs/fn_delete_production_order.sql | 356 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 356 insertions(+), 0 deletions(-)
diff --git a/docs/fn_delete_production_order.sql b/docs/fn_delete_production_order.sql
new file mode 100644
index 0000000..c6c88a0
--- /dev/null
+++ b/docs/fn_delete_production_order.sql
@@ -0,0 +1,356 @@
+-- ============================================================
+-- 鐢熶骇璁㈠崟瀹屾暣鍒犻櫎鍑芥暟
+-- 閫傜敤鍦烘櫙锛氬緟寮�濮嬨�佽繘琛屼腑銆佸凡瀹屾垚涓夌鐘舵�佺殑鐢熶骇璁㈠崟鍒犻櫎
+-- 鐢熸垚鏃ユ湡锛�2026-06-11
+-- ============================================================
+
+DELIMITER $$
+
+DROP FUNCTION IF EXISTS fn_delete_production_order$$
+
+CREATE FUNCTION fn_delete_production_order(
+ p_order_id BIGINT,
+ p_force_delete TINYINT DEFAULT 0 -- 0=瀹夊叏妯″紡(宸茬敓浜т笉鍏佽鍒犻櫎), 1=寮哄埗鍒犻櫎
+) RETURNS VARCHAR(500)
+DETERMINISTIC
+READS SQL DATA
+BEGIN
+ DECLARE v_order_status INT DEFAULT 0;
+ DECLARE v_complete_quantity DECIMAL(20,4) DEFAULT 0;
+ DECLARE v_quantity DECIMAL(20,4) DEFAULT 0;
+ DECLARE v_nps_no VARCHAR(100) DEFAULT '';
+ DECLARE v_has_pick_record INT DEFAULT 0;
+ DECLARE v_has_product_main INT DEFAULT 0;
+ DECLARE v_has_quality_inspect INT DEFAULT 0;
+ DECLARE v_has_stock_record INT DEFAULT 0;
+ DECLARE v_deleted_count INT DEFAULT 0;
+ DECLARE v_result VARCHAR(500) DEFAULT '';
+
+ -- 鏌ヨ璁㈠崟鍩烘湰淇℃伅
+ SELECT status, complete_quantity, quantity, nps_no
+ INTO v_order_status, v_complete_quantity, v_quantity, v_nps_no
+ FROM production_order
+ WHERE id = p_order_id;
+
+ -- 璁㈠崟涓嶅瓨鍦�
+ IF v_order_status IS NULL THEN
+ RETURN CONCAT('鍒犻櫎澶辫触锛氱敓浜ц鍗曚笉瀛樺湪锛孖D=', p_order_id);
+ END IF;
+
+ -- 瀹夊叏妯″紡涓嬫鏌ユ槸鍚﹀厑璁稿垹闄�
+ IF p_force_delete = 0 THEN
+ -- 鐘舵�佹鏌ワ細鍙湁寰呭紑濮�(1)鍜屽凡鍙栨秷(4)鍏佽鍒犻櫎
+ IF v_order_status = 2 THEN
+ RETURN CONCAT('鍒犻櫎澶辫触锛氳鍗昜', v_nps_no, ']姝ｅ湪杩涜涓紝涓嶅厑璁稿垹闄�');
+ END IF;
+
+ IF v_order_status = 3 THEN
+ RETURN CONCAT('鍒犻櫎澶辫触锛氳鍗昜', v_nps_no, ']宸插畬鎴愶紝涓嶅厑璁稿垹闄�');
+ END IF;
+
+ IF v_order_status = 5 THEN
+ RETURN CONCAT('鍒犻櫎澶辫触锛氳鍗昜', v_nps_no, ']宸茬粨鏉燂紝涓嶅厑璁稿垹闄�');
+ END IF;
+ END IF;
+
+ -- ========== 绗竴姝ワ細妫�鏌ュ叧鑱旀暟鎹槸鍚﹀瓨鍦� ==========
+
+ -- 妫�鏌ラ鏂欒褰�
+ SELECT COUNT(*) INTO v_has_pick_record
+ FROM production_order_pick_record
+ WHERE production_order_id = p_order_id;
+
+ -- 妫�鏌ユ姤宸ヨ褰�
+ SELECT COUNT(*) INTO v_has_product_main
+ FROM production_product_main ppm
+ INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
+ WHERE pot.production_order_id = p_order_id;
+
+ -- 妫�鏌ヨ川妫�璁板綍
+ SELECT COUNT(*) INTO v_has_quality_inspect
+ FROM quality_inspect qi
+ INNER JOIN production_product_main ppm ON qi.product_main_id = ppm.id
+ INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
+ WHERE pot.production_order_id = p_order_id;
+
+ -- 妫�鏌ュ簱瀛樿褰曪紙閫氳繃鎶ュ伐鍏ュ簱锛�
+ SELECT COUNT(*) INTO v_has_stock_record
+ FROM stock_in_record sir
+ INNER JOIN production_product_main ppm ON sir.record_id = ppm.id
+ INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
+ WHERE pot.production_order_id = p_order_id
+ AND sir.record_type IN ('2', '5'); -- 鐢熶骇鎶ュ伐鍏ュ簱
+
+ -- ========== 绗簩姝ワ細鍒犻櫎鍏宠仈鏁版嵁锛堜粠鍙跺瓙鍒版牴锛� ==========
+
+ -- 銆愮1灞傘�戝垹闄よ川妫�闄勪欢
+ DELETE FROM quality_inspect_file
+ WHERE inspect_id IN (
+ SELECT qi.id FROM quality_inspect qi
+ INNER JOIN production_product_main ppm ON qi.product_main_id = ppm.id
+ INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
+ WHERE pot.production_order_id = p_order_id
+ );
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 銆愮2灞傘�戝垹闄よ川妫�鍙傛暟
+ DELETE FROM quality_inspect_param
+ WHERE inspect_id IN (
+ SELECT qi.id FROM quality_inspect qi
+ INNER JOIN production_product_main ppm ON qi.product_main_id = ppm.id
+ INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
+ WHERE pot.production_order_id = p_order_id
+ );
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 銆愮3灞傘�戝垹闄や笉鍚堟牸鍝佽褰�
+ DELETE FROM quality_unqualified
+ WHERE inspect_id IN (
+ SELECT qi.id FROM quality_inspect qi
+ INNER JOIN production_product_main ppm ON qi.product_main_id = ppm.id
+ INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
+ WHERE pot.production_order_id = p_order_id
+ );
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 銆愮4灞傘�戝垹闄よ川妫�璁板綍锛堣繃绋嬫楠屻�佸嚭鍘傛楠岋級
+ DELETE FROM quality_inspect
+ WHERE product_main_id IN (
+ SELECT ppm.id FROM production_product_main ppm
+ INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
+ WHERE pot.production_order_id = p_order_id
+ );
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 銆愮5灞傘�戝垹闄ゅ簱瀛樺叆搴撹褰曪紙鐢熶骇鎶ュ伐鍏ュ簱銆佹姤搴熷叆搴擄級
+ DELETE FROM stock_in_record
+ WHERE record_id IN (
+ SELECT ppm.id FROM production_product_main ppm
+ INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
+ WHERE pot.production_order_id = p_order_id
+ ) AND record_type IN ('2', '5', '7'); -- 鐢熶骇鎶ュ伐鍏ュ簱銆佹姤搴熷叆搴�
+
+ -- 鍒犻櫎璐ㄦ鍏ュ簱璁板綍
+ DELETE FROM stock_in_record
+ WHERE record_id IN (
+ SELECT qi.id FROM quality_inspect qi
+ INNER JOIN production_product_main ppm ON qi.product_main_id = ppm.id
+ INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
+ WHERE pot.production_order_id = p_order_id
+ ) AND record_type = '6'; -- 璐ㄦ鍏ュ簱
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 銆愮6灞傘�戝垹闄ゅ簱瀛樺嚭搴撹褰曪紙鐢熶骇鎶ュ伐鍑哄簱锛�
+ DELETE FROM stock_out_record
+ WHERE record_id IN (
+ SELECT ppm.id FROM production_product_main ppm
+ INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
+ WHERE pot.production_order_id = p_order_id
+ ) AND record_type = '13'; -- 鐢熶骇鎶ュ伐鍑哄簱
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 銆愮7灞傘�戝垹闄ゆ姤宸ヤ骇鍑�
+ DELETE FROM production_product_output
+ WHERE production_product_main_id IN (
+ SELECT ppm.id FROM production_product_main ppm
+ INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
+ WHERE pot.production_order_id = p_order_id
+ );
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 銆愮8灞傘�戝垹闄ゆ姤宸ユ姇鍏�
+ DELETE FROM production_product_input
+ WHERE production_product_main_id IN (
+ SELECT ppm.id FROM production_product_main ppm
+ INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
+ WHERE pot.production_order_id = p_order_id
+ );
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 銆愮9灞傘�戝垹闄ょ敓浜ф牳绠�
+ DELETE FROM production_account
+ WHERE production_product_main_id IN (
+ SELECT ppm.id FROM production_product_main ppm
+ INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
+ WHERE pot.production_order_id = p_order_id
+ );
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 銆愮10灞傘�戝垹闄ゆ姤宸ュ伐搴忓弬鏁帮紙閫氳繃production_product_main_id鍏宠仈锛�
+ DELETE FROM production_order_routing_operation_param
+ WHERE production_product_main_id IN (
+ SELECT ppm.id FROM production_product_main ppm
+ INNER JOIN production_operation_task pot ON ppm.production_operation_task_id = pot.id
+ WHERE pot.production_order_id = p_order_id
+ );
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 銆愮11灞傘�戝垹闄ゆ姤宸ヤ富琛�
+ DELETE FROM production_product_main
+ WHERE production_operation_task_id IN (
+ SELECT id FROM production_operation_task
+ WHERE production_order_id = p_order_id
+ );
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 銆愮12灞傘�戝垹闄ら鏂欒褰�
+ DELETE FROM production_order_pick_record
+ WHERE production_order_id = p_order_id;
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 銆愮13灞傘�戝垹闄ら鏂欏叆搴撹褰曪紙閫�鏂欙級
+ DELETE FROM stock_in_record
+ WHERE record_id IN (
+ SELECT id FROM production_order_pick
+ WHERE production_order_id = p_order_id
+ ) AND record_type IN ('20', '22'); -- 棰嗘枡閫�鏂欏叆搴�
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 銆愮14灞傘�戝垹闄ら鏂欏崟
+ DELETE FROM production_order_pick
+ WHERE production_order_id = p_order_id;
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 銆愮15灞傘�戝垹闄ょ敓浜у伐鍗�
+ DELETE FROM production_operation_task
+ WHERE production_order_id = p_order_id;
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 銆愮16灞傘�戝垹闄ゅ伐搴忓弬鏁帮紙璁㈠崟绾у埆锛�
+ DELETE FROM production_order_routing_operation_param
+ WHERE production_order_id = p_order_id;
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 銆愮17灞傘�戝垹闄ゅ伐搴�
+ DELETE FROM production_order_routing_operation
+ WHERE production_order_id = p_order_id;
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 銆愮18灞傘�戝垹闄ゅ伐鑹鸿矾绾�
+ DELETE FROM production_order_routing
+ WHERE production_order_id = p_order_id;
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 銆愮19灞傘�戝垹闄OM缁撴瀯
+ DELETE FROM production_bom_structure
+ WHERE production_order_id = p_order_id;
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 銆愮20灞傘�戝垹闄よ鍗旴OM
+ DELETE FROM production_order_bom
+ WHERE production_order_id = p_order_id;
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 銆愮21灞傘�戝垹闄ょ敓浜ц鍒掑叧鑱旓紙鍥為��璁″垝宸蹭笅鍙戞暟閲忥級
+ -- 娉ㄦ剰锛氳繖閲岄渶瑕佹牴鎹笟鍔￠�昏緫鍥為��production_plan鐨剄uantity_issued鍜宻tatus
+ -- 鐢变簬production_plan_ids鏄疛SON鏍煎紡[1,2,3]锛岄渶瑕佺壒娈婂鐞�
+ -- 姝ゅ浠呭垹闄よ鍗曪紝璁″垝鍥為��閫昏緫寤鸿鍦ㄥ簲鐢ㄥ眰澶勭悊
+
+ -- 銆愮22灞傘�戝垹闄ょ敓浜ц鍗曚富琛�
+ DELETE FROM production_order
+ WHERE id = p_order_id;
+ SET v_deleted_count = v_deleted_count + ROW_COUNT();
+
+ -- 杩斿洖缁撴灉
+ SET v_result = CONCAT(
+ '鍒犻櫎鎴愬姛锛氳鍗昜', v_nps_no, ']锛�',
+ '鐘舵��=', CASE v_order_status
+ WHEN 1 THEN '寰呭紑濮�'
+ WHEN 2 THEN '杩涜涓�'
+ WHEN 3 THEN '宸插畬鎴�'
+ WHEN 4 THEN '宸插彇娑�'
+ WHEN 5 THEN '宸茬粨鏉�'
+ ELSE '鏈煡'
+ END, '锛�',
+ '鍏卞垹闄�', v_deleted_count, '鏉″叧鑱旀暟鎹�'
+ );
+
+ RETURN v_result;
+
+END$$
+
+DELIMITER ;
+
+-- ============================================================
+-- 鎵归噺鍒犻櫎鐢熶骇璁㈠崟瀛樺偍杩囩▼
+-- ============================================================
+
+DELIMITER $$
+
+DROP PROCEDURE IF EXISTS sp_batch_delete_production_order$$
+
+CREATE PROCEDURE sp_batch_delete_production_order(
+ IN p_order_ids TEXT, -- 璁㈠崟ID鍒楄〃锛岄�楀彿鍒嗛殧锛屽 "1,2,3"
+ IN p_force_delete TINYINT -- 0=瀹夊叏妯″紡, 1=寮哄埗鍒犻櫎
+)
+BEGIN
+ DECLARE v_order_id BIGINT;
+ DECLARE v_done INT DEFAULT FALSE;
+ DECLARE v_cursor CURSOR FOR
+ SELECT CAST(TRIM(value) AS UNSIGNED)
+ FROM JSON_TABLE(CONCAT('[', p_order_ids, ']'), '$[*]' COLUMNS(value VARCHAR(20) PATH '$')) AS jt;
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
+
+ DECLARE v_success_count INT DEFAULT 0;
+ DECLARE v_fail_count INT DEFAULT 0;
+ DECLARE v_result_msg VARCHAR(500);
+
+ -- 鍒涘缓涓存椂琛ㄨ褰曠粨鏋�
+ DROP TEMPORARY TABLE IF EXISTS tmp_delete_result;
+ CREATE TEMPORARY TABLE tmp_delete_result (
+ order_id BIGINT,
+ result VARCHAR(500)
+ );
+
+ OPEN v_cursor;
+
+ read_loop: LOOP
+ FETCH v_cursor INTO v_order_id;
+ IF v_done THEN
+ LEAVE read_loop;
+ END IF;
+
+ -- 璋冪敤鍒犻櫎鍑芥暟
+ SET v_result_msg = fn_delete_production_order(v_order_id, p_force_delete);
+
+ -- 璁板綍缁撴灉
+ INSERT INTO tmp_delete_result (order_id, result) VALUES (v_order_id, v_result_msg);
+
+ -- 缁熻
+ IF v_result_msg LIKE '鍒犻櫎鎴愬姛%' THEN
+ SET v_success_count = v_success_count + 1;
+ ELSE
+ SET v_fail_count = v_fail_count + 1;
+ END IF;
+
+ END LOOP;
+
+ CLOSE v_cursor;
+
+ -- 杈撳嚭缁撴灉
+ SELECT
+ CONCAT('鎵归噺鍒犻櫎瀹屾垚锛氭垚鍔�', v_success_count, '涓紝澶辫触', v_fail_count, '涓�') AS summary;
+
+ SELECT * FROM tmp_delete_result;
+
+ DROP TEMPORARY TABLE IF EXISTS tmp_delete_result;
+
+END$$
+
+DELIMITER ;
+
+-- ============================================================
+-- 浣跨敤绀轰緥
+-- ============================================================
+
+-- 绀轰緥1锛氬畨鍏ㄦā寮忓垹闄ゅ崟涓鍗曪紙鍙兘鍒犻櫎寰呭紑濮�/宸插彇娑堢姸鎬侊級
+-- SELECT fn_delete_production_order(123, 0);
+
+-- 绀轰緥2锛氬己鍒跺垹闄ゅ崟涓鍗曪紙蹇界暐鐘舵�佹鏌ワ級
+-- SELECT fn_delete_production_order(123, 1);
+
+-- 绀轰緥3锛氭壒閲忓垹闄よ鍗�
+-- CALL sp_batch_delete_production_order('123,124,125', 0);
+
+-- 绀轰緥4锛氭壒閲忓己鍒跺垹闄�
+-- CALL sp_batch_delete_production_order('123,124,125', 1);
\ No newline at end of file
--
Gitblit v1.9.3