zkxw/docs/sql/qc_material_demo_seed.sql

-- 质检物料库测试数据（biz_qc_material / biz_qc_material_usage）
--
-- 用法：
--   1) 默认租户 zkxw，默认操作人 d3000000000000000000000000000001（与 zkxw.sql 演示账号一致）
--   2) 可重复执行：按物料编码前缀 QC-MAT-DEMO- 清理后重新插入
--   3) 执行前请确认已建表（见 docs/superpowers/plans/2026-05-20-qc-material-library.md 中 DDL）
--
-- 示例：
--   mysql -u root -p zkxw < docs/sql/qc_material_demo_seed.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

SET @tenant_no = 'zkxw';
SET @operator_id = 'd3000000000000000000000000000001';
SET @now = NOW();

START TRANSACTION;

-- 清理旧测试数据（先删变动记录，再删物料）
DELETE u
FROM `biz_qc_material_usage` u
INNER JOIN `biz_qc_material` m ON m.`id` = u.`material_id`
WHERE m.`tenant_no` = @tenant_no
  AND m.`material_code` LIKE 'QC-MAT-DEMO-%';

DELETE FROM `biz_qc_material`
WHERE `tenant_no` = @tenant_no
  AND `material_code` LIKE 'QC-MAT-DEMO-%';

-- ----------------------------
-- 质检物料主数据（10 条）
-- 覆盖：正常库存 / 库存预警 / 零库存 / 停用
-- ----------------------------
INSERT INTO `biz_qc_material` (
  `id`,
  `material_code`,
  `material_name`,
  `spec_model`,
  `unit`,
  `stock_qty`,
  `warning_qty`,
  `status`,
  `tenant_no`,
  `order_num`,
  `remark`,
  `create_by`,
  `create_dept_id`,
  `create_time`,
  `update_by`,
  `update_time`,
  `del_flag`
) VALUES
('q1000000000000000000000000000001', 'QC-MAT-DEMO-001', 'PCR 扩增引物 Mix', '20× 预混', '支', 120.0000, 50.0000, '1', @tenant_no, 1, '常温避光', @operator_id, '', @now, @operator_id, @now, 0),
('q1000000000000000000000000000002', 'QC-MAT-DEMO-002', '核酸提取试剂盒', '96 孔板适用', '盒', 35.0000, 40.0000, '1', @tenant_no, 2, '库存低于预警线', @operator_id, '', @now, @operator_id, @now, 0),
('q1000000000000000000000000000003', 'QC-MAT-DEMO-003', '琼脂糖', 'Bio-Rad 常规型', 'g', 8.0000, 10.0000, '1', @tenant_no, 3, '粉末，干燥保存', @operator_id, '', @now, @operator_id, @now, 0),
('q1000000000000000000000000000004', 'QC-MAT-DEMO-004', 'TAE 电泳缓冲液', '50× 浓缩', '瓶', 200.0000, 30.0000, '1', @tenant_no, 4, NULL, @operator_id, '', @now, @operator_id, @now, 0),
('q1000000000000000000000000000005', 'QC-MAT-DEMO-005', '无菌吸头', '200μL 滤芯', '盒', 5000.0000, 500.0000, '1', @tenant_no, 5, '每盒 96 支', @operator_id, '', @now, @operator_id, @now, 0),
('q1000000000000000000000000000006', 'QC-MAT-DEMO-006', 'EP 离心管', '1.5mL 锥底', '包', 3000.0000, 200.0000, '1', @tenant_no, 6, '每包 500 支', @operator_id, '', @now, @operator_id, @now, 0),
('q1000000000000000000000000000007', 'QC-MAT-DEMO-007', '医用丁腈手套', 'M 码 无粉', '盒', 150.0000, 50.0000, '1', @tenant_no, 7, '每盒 100 只', @operator_id, '', @now, @operator_id, @now, 0),
('q1000000000000000000000000000008', 'QC-MAT-DEMO-008', '75% 乙醇消毒液', '500ml 喷雾', '瓶', 0.0000, 20.0000, '1', @tenant_no, 8, '零库存，可测入库', @operator_id, '', @now, @operator_id, @now, 0),
('q1000000000000000000000000000009', 'QC-MAT-DEMO-009', '质检标准品', '冻干粉 1mg', '支', 25.0000, 10.0000, '1', @tenant_no, 9, '-20℃ 保存', @operator_id, '', @now, @operator_id, @now, 0),
('q1000000000000000000000000000010', 'QC-MAT-DEMO-010', '细胞培养基（旧批次）', '500ml', '瓶', 15.0000, 5.0000, '0', @tenant_no, 10, '停用批次，仅保留档案', @operator_id, '', @now, @operator_id, @now, 0);

-- ----------------------------
-- 物料变动记录（3 条，便于「物料变动记录」页联调）
-- change_type: in=入库 / use=使用；source_type: manual_in / task_complete
-- ----------------------------
INSERT INTO `biz_qc_material_usage` (
  `id`,
  `material_id`,
  `material_code`,
  `material_name`,
  `change_type`,
  `change_qty`,
  `stock_after`,
  `qc_task_id`,
  `qc_task_no`,
  `source_type`,
  `tenant_no`,
  `remark`,
  `create_by`,
  `create_dept_id`,
  `create_time`,
  `update_by`,
  `update_time`,
  `del_flag`
) VALUES
('qu100000000000000000000000000001', 'q1000000000000000000000000000001', 'QC-MAT-DEMO-001', 'PCR 扩增引物 Mix', 'in', 50.0000, 70.0000, NULL, NULL, 'manual_in', @tenant_no, '期初入库', @operator_id, '', DATE_SUB(@now, INTERVAL 7 DAY), @operator_id, DATE_SUB(@now, INTERVAL 7 DAY), 0),
('qu100000000000000000000000000002', 'q1000000000000000000000000000001', 'QC-MAT-DEMO-001', 'PCR 扩增引物 Mix', 'in', 50.0000, 120.0000, NULL, NULL, 'manual_in', @tenant_no, '补货入库', @operator_id, '', DATE_SUB(@now, INTERVAL 2 DAY), @operator_id, DATE_SUB(@now, INTERVAL 2 DAY), 0),
('qu100000000000000000000000000003', 'q1000000000000000000000000000002', 'QC-MAT-DEMO-002', '核酸提取试剂盒', 'use', -5.0000, 35.0000, NULL, 'QC-DEMO-TASK-001', 'task_complete', @tenant_no, '演示任务消耗', @operator_id, '', DATE_SUB(@now, INTERVAL 1 DAY), @operator_id, DATE_SUB(@now, INTERVAL 1 DAY), 0);

COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

-- 插入结果速览
SELECT
  material_code AS `物料编码`,
  material_name AS `物料名称`,
  stock_qty AS `当前库存`,
  warning_qty AS `预警库存`,
  CASE WHEN status = '1' THEN '正常' ELSE '停用' END AS `状态`,
  CASE WHEN stock_qty <= warning_qty THEN '库存预警' ELSE '正常' END AS `库存状态`
FROM `biz_qc_material`
WHERE `tenant_no` = @tenant_no
  AND `material_code` LIKE 'QC-MAT-DEMO-%'
  AND `del_flag` = 0
ORDER BY `order_num`;