AiDOPWarehouse/scripts/demo-qdc-summary-data.sql

-- TASK-010-QDC-DEMO-DATA-1: S9 QDC 四主线演示数据
-- 目的：让质量/交付/库存三主线在 today/this_week/this_month 均有数据，
--       且 onTimeCloseRate / avgProcessHours / closeRate 均可计算。
-- 成本异常不写入（待业务定义口径）。
-- 幂等：先软删除同前缀旧数据，再写入。只操作 exception_code LIKE 'DEMO-QDC-%'。

START TRANSACTION;

-- 软删除旧 DEMO-QDC 数据（保证幂等）
UPDATE ado_s8_exception
SET is_deleted = 1, updated_at = NOW()
WHERE exception_code LIKE 'DEMO-QDC-%' AND is_deleted = 0;

-- ═══════════════════════════════════════════
-- 质量异常（MFG_QUALITY_ABNORMAL / S2S6_PRODUCTION / S2）
-- 4条：2 CLOSED-内SLA + 1 CLOSED-超SLA + 1 IN_PROGRESS
-- ═══════════════════════════════════════════
INSERT INTO ado_s8_exception
  (tenant_id, factory_id, exception_code, title,
   scene_code, source_type, module_code, exception_type_code,
   status, severity, priority_score, priority_level,
   occurrence_dept_id, responsible_dept_id,
   timeout_flag, is_deleted,
   created_at, sla_deadline, closed_at,
   consecutive_hit_count, consecutive_miss_count)
VALUES
  -- CLOSED 8h, 在SLA内
  (1, 1, 'DEMO-QDC-QUALITY-001', '制造协同质量异常-焊接工位A外观不良',
   'S2S6_PRODUCTION', 'MANUAL', 'S2', 'MFG_QUALITY_ABNORMAL',
   'CLOSED', 'SERIOUS', 80.00, 'P1', 2, 2,
   0, 0,
   '2026-05-07 08:00:00', '2026-05-09 08:00:00', '2026-05-07 16:00:00',
   1, 0),
  -- CLOSED 26h, 在SLA内
  (1, 1, 'DEMO-QDC-QUALITY-002', '制造协同质量异常-注塑件尺寸超差',
   'S2S6_PRODUCTION', 'MANUAL', 'S2', 'MFG_QUALITY_ABNORMAL',
   'CLOSED', 'MODERATE', 65.00, 'P2', 2, 2,
   0, 0,
   '2026-05-06 09:00:00', '2026-05-08 09:00:00', '2026-05-07 11:00:00',
   1, 0),
  -- CLOSED 47h, 超SLA（SLA=24h，实际47h）
  (1, 1, 'DEMO-QDC-QUALITY-003', '制造协同质量异常-喷涂色差批次超标',
   'S2S6_PRODUCTION', 'MANUAL', 'S2', 'MFG_QUALITY_ABNORMAL',
   'CLOSED', 'SERIOUS', 75.00, 'P1', 2, 2,
   1, 0,
   '2026-05-05 10:00:00', '2026-05-06 10:00:00', '2026-05-07 09:00:00',
   1, 0),
  -- IN_PROGRESS（今日创建，未关闭）
  (1, 1, 'DEMO-QDC-QUALITY-004', '制造协同质量异常-铝压铸气孔率超标',
   'S2S6_PRODUCTION', 'MANUAL', 'S2', 'MFG_QUALITY_ABNORMAL',
   'IN_PROGRESS', 'MODERATE', 60.00, 'P2', 2, 2,
   0, 0,
   '2026-05-07 10:00:00', '2026-05-09 10:00:00', NULL,
   1, 0);

-- ═══════════════════════════════════════════
-- 交付异常（DELIVERY_DELAY / S7 / S7）
-- 4条：2 CLOSED-内SLA + 1 CLOSED-超SLA + 1 IN_PROGRESS
-- ═══════════════════════════════════════════
INSERT INTO ado_s8_exception
  (tenant_id, factory_id, exception_code, title,
   scene_code, source_type, module_code, exception_type_code,
   status, severity, priority_score, priority_level,
   occurrence_dept_id, responsible_dept_id,
   timeout_flag, is_deleted,
   created_at, sla_deadline, closed_at,
   consecutive_hit_count, consecutive_miss_count)
VALUES
  -- CLOSED 6h, 在SLA内
  (1, 1, 'DEMO-QDC-DELIVERY-001', '交期延迟-成品发货延误 PO-20260507-001',
   'S7', 'AUTO_WATCH', 'S7', 'DELIVERY_DELAY',
   'CLOSED', 'SERIOUS', 82.00, 'P1', 2, 2,
   0, 0,
   '2026-05-07 08:00:00', '2026-05-09 08:00:00', '2026-05-07 14:00:00',
   1, 0),
  -- CLOSED 26h, 在SLA内
  (1, 1, 'DEMO-QDC-DELIVERY-002', '交期延迟-成品发货延误 PO-20260506-003',
   'S7', 'AUTO_WATCH', 'S7', 'DELIVERY_DELAY',
   'CLOSED', 'MODERATE', 68.00, 'P2', 2, 2,
   0, 0,
   '2026-05-06 08:00:00', '2026-05-08 08:00:00', '2026-05-07 10:00:00',
   1, 0),
  -- CLOSED 72h, 超SLA（SLA=24h，实际72h）
  (1, 1, 'DEMO-QDC-DELIVERY-003', '交期延迟-发货节点延误 PO-20260504-007',
   'S7', 'AUTO_WATCH', 'S7', 'DELIVERY_DELAY',
   'CLOSED', 'SERIOUS', 78.00, 'P1', 2, 2,
   1, 0,
   '2026-05-04 08:00:00', '2026-05-05 08:00:00', '2026-05-07 08:00:00',
   1, 0),
  -- IN_PROGRESS
  (1, 1, 'DEMO-QDC-DELIVERY-004', '交期延迟-客户催货在途未确认 PO-20260507-009',
   'S7', 'AUTO_WATCH', 'S7', 'DELIVERY_DELAY',
   'IN_PROGRESS', 'MODERATE', 62.00, 'P2', 2, 2,
   0, 0,
   '2026-05-07 09:00:00', '2026-05-09 09:00:00', NULL,
   1, 0);

-- ═══════════════════════════════════════════
-- 库存异常（MATERIAL_STOCK_ABNORMAL / S3S5_SUPPLY / S3）
-- 4条：2 CLOSED-内SLA + 1 CLOSED-超SLA + 1 IN_PROGRESS
-- ═══════════════════════════════════════════
INSERT INTO ado_s8_exception
  (tenant_id, factory_id, exception_code, title,
   scene_code, source_type, module_code, exception_type_code,
   status, severity, priority_score, priority_level,
   occurrence_dept_id, responsible_dept_id,
   timeout_flag, is_deleted,
   created_at, sla_deadline, closed_at,
   consecutive_hit_count, consecutive_miss_count)
VALUES
  -- CLOSED 4h, 在SLA内
  (1, 1, 'DEMO-QDC-INVENTORY-001', '库存异常-SMT物料实物库存低于安全库存',
   'S3S5_SUPPLY', 'MANUAL', 'S3', 'MATERIAL_STOCK_ABNORMAL',
   'CLOSED', 'MODERATE', 70.00, 'P2', 2, 2,
   0, 0,
   '2026-05-07 08:00:00', '2026-05-09 08:00:00', '2026-05-07 12:00:00',
   1, 0),
  -- CLOSED 27h, 在SLA内
  (1, 1, 'DEMO-QDC-INVENTORY-002', '库存异常-连接器库存水位异常',
   'S3S5_SUPPLY', 'MANUAL', 'S3', 'MATERIAL_STOCK_ABNORMAL',
   'CLOSED', 'MODERATE', 65.00, 'P2', 1, 1,
   0, 0,
   '2026-05-06 08:00:00', '2026-05-08 08:00:00', '2026-05-07 11:00:00',
   1, 0),
  -- CLOSED 72h, 超SLA
  (1, 1, 'DEMO-QDC-INVENTORY-003', '库存异常-铝型材批次呆滞库存超标',
   'S3S5_SUPPLY', 'MANUAL', 'S3', 'MATERIAL_STOCK_ABNORMAL',
   'CLOSED', 'SERIOUS', 76.00, 'P1', 2, 2,
   1, 0,
   '2026-05-04 08:00:00', '2026-05-05 08:00:00', '2026-05-07 08:00:00',
   1, 0),
  -- IN_PROGRESS
  (1, 1, 'DEMO-QDC-INVENTORY-004', '库存异常-PCB板材实物盘点与系统差异',
   'S3S5_SUPPLY', 'MANUAL', 'S3', 'MATERIAL_STOCK_ABNORMAL',
   'IN_PROGRESS', 'MODERATE', 58.00, 'P2', 1, 1,
   0, 0,
   '2026-05-07 10:00:00', '2026-05-09 10:00:00', NULL,
   1, 0);

-- 验证写入
SELECT exception_type_code, status, COUNT(*) AS cnt,
       SUM(CASE WHEN sla_deadline IS NOT NULL THEN 1 ELSE 0 END) AS has_sla,
       SUM(CASE WHEN closed_at IS NOT NULL THEN 1 ELSE 0 END) AS has_closed
FROM ado_s8_exception
WHERE exception_code LIKE 'DEMO-QDC-%' AND is_deleted = 0
GROUP BY exception_type_code, status
ORDER BY exception_type_code, status;

COMMIT;