zkxw/docs/sql/clear_biz_production_material_data.sql

-- ============================================================
-- 清空「生产物料库」界面相关业务表数据
--
-- 覆盖界面模块：
--   1. 物料列表          → biz_production_material / biz_production_material_stock_batch
--   2. 变动记录          → biz_production_material_usage
--   3. 库存盘点          → biz_production_material_stock_check*
--   4. 配制单            → biz_production_material_prep / biz_production_material_prep_line
--   5. 我的物料领用记录  → biz_production_material_usage（按用户过滤，无独立表）
--   6. 消耗明细          → biz_production_material_usage（prep 关联子记录，无独立表）
--
-- 附加：配置试剂配方 BOM → biz_production_material_formula
--
-- 用法（执行前请先备份）：
--   SET @tenant_no = 'zkxw' COLLATE utf8mb4_unicode_ci;   -- 仅清空指定租户
--   SET @tenant_no = NULL;     -- 清空全部租户（生产环境慎用）
--
-- 说明：
--   - 本脚本为物理 DELETE，包含 del_flag=1 的软删数据
--   - 表间无数据库外键，按业务依赖顺序删除
--   - 生产任务步骤表可能仍保留 requisition_usage_id 快照引用（见文末可选清理）
-- ============================================================

-- 与 biz_production_material* 表 tenant_no 列排序规则一致，避免与 @tenant_no 比较时报 1267
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
SET @tenant_no = 'zkxw' COLLATE utf8mb4_unicode_ci;

START TRANSACTION;

-- ------------------------------------------------------------------
-- 1. 库存盘点（子表 → 主表）
-- ------------------------------------------------------------------
DELETE FROM `biz_production_material_stock_check_unused_line`
WHERE @tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci);

DELETE FROM `biz_production_material_stock_check_batch_line`
WHERE @tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci);

DELETE FROM `biz_production_material_stock_check`
WHERE @tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci);

-- ------------------------------------------------------------------
-- 2. 配制单（明细 → 主表）
-- ------------------------------------------------------------------
DELETE FROM `biz_production_material_prep_line`
WHERE @tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci);

DELETE FROM `biz_production_material_prep`
WHERE @tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci);

-- ------------------------------------------------------------------
-- 3. 变动记录 / 领用记录 / 消耗明细（同一张表）
--    含 parent_usage_id 自引用，整表删除即可
-- ------------------------------------------------------------------
DELETE FROM `biz_production_material_usage`
WHERE @tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci);

-- ------------------------------------------------------------------
-- 4. 物料入库批次
-- ------------------------------------------------------------------
DELETE FROM `biz_production_material_stock_batch`
WHERE @tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci);

-- ------------------------------------------------------------------
-- 5. 配置试剂配方 BOM
-- ------------------------------------------------------------------
DELETE FROM `biz_production_material_formula`
WHERE @tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci);

-- ------------------------------------------------------------------
-- 6. 物料目录（主数据）
-- ------------------------------------------------------------------
DELETE FROM `biz_production_material`
WHERE @tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci);

COMMIT;

-- ------------------------------------------------------------------
-- 可选：解除生产任务步骤中对已删领用记录的引用（默认注释，按需启用）
-- ------------------------------------------------------------------
-- START TRANSACTION;
-- UPDATE `biz_production_task_step_reagent`
-- SET `requisition_usage_id` = NULL, `update_time` = NOW()
-- WHERE `requisition_usage_id` IS NOT NULL
--   AND (@tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci));
--
-- UPDATE `biz_production_task_step_consumable`
-- SET `requisition_usage_id` = NULL, `update_time` = NOW()
-- WHERE `requisition_usage_id` IS NOT NULL
--   AND (@tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci));
--
-- UPDATE `biz_production_task_step_configured_reagent`
-- SET `requisition_usage_id` = NULL, `update_time` = NOW()
-- WHERE `requisition_usage_id` IS NOT NULL
--   AND (@tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci));
-- COMMIT;

-- ------------------------------------------------------------------
-- 校验：各表剩余行数（按 @tenant_no 过滤统计）
-- ------------------------------------------------------------------
SELECT 'biz_production_material_stock_check_unused_line' AS tbl,
       COUNT(*) AS remain_cnt
FROM `biz_production_material_stock_check_unused_line`
WHERE @tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci)
UNION ALL
SELECT 'biz_production_material_stock_check_batch_line', COUNT(*)
FROM `biz_production_material_stock_check_batch_line`
WHERE @tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci)
UNION ALL
SELECT 'biz_production_material_stock_check', COUNT(*)
FROM `biz_production_material_stock_check`
WHERE @tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci)
UNION ALL
SELECT 'biz_production_material_prep_line', COUNT(*)
FROM `biz_production_material_prep_line`
WHERE @tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci)
UNION ALL
SELECT 'biz_production_material_prep', COUNT(*)
FROM `biz_production_material_prep`
WHERE @tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci)
UNION ALL
SELECT 'biz_production_material_usage', COUNT(*)
FROM `biz_production_material_usage`
WHERE @tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci)
UNION ALL
SELECT 'biz_production_material_stock_batch', COUNT(*)
FROM `biz_production_material_stock_batch`
WHERE @tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci)
UNION ALL
SELECT 'biz_production_material_formula', COUNT(*)
FROM `biz_production_material_formula`
WHERE @tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci)
UNION ALL
SELECT 'biz_production_material', COUNT(*)
FROM `biz_production_material`
WHERE @tenant_no IS NULL OR `tenant_no` = (@tenant_no COLLATE utf8mb4_unicode_ci);