zkxw/docs/sql/migrations/2026-10-15-protocol-payment-v1-to-v2-checklist.sql

-- 采集协议单缴费 v1→v2 迁移 · 清单与校验（只读，不改数据）
-- 实际迁移请用：backend/scripts/migrate_protocol_payment_v1_to_v2.py
-- MySQL 5.7 / utf8mb4

SET NAMES utf8mb4;

-- ---------------------------------------------------------------------------
-- 1) 仍为 v1 的协议概览
-- ---------------------------------------------------------------------------
SELECT
  p.id AS protocol_id,
  p.protocol_no,
  p.tenant_no,
  p.payment_model_version,
  (
    SELECT COUNT(*)
    FROM biz_protocol_sample s
    WHERE s.collection_protocol_id = p.id
      AND s.del_flag = 0
      AND IFNULL(s.sample_status, '') <> 'yzf'
  ) AS active_sample_count,
  (
    SELECT COUNT(*)
    FROM biz_protocol_payment pay
    WHERE pay.collection_protocol_id = p.id AND pay.del_flag = 0
  ) AS v1_payment_count,
  (
    SELECT COUNT(*)
    FROM biz_protocol_sample_payment sp
    WHERE sp.collection_protocol_id = p.id AND sp.del_flag = 0
  ) AS v2_sample_payment_count
FROM biz_collection_protocol p
WHERE p.del_flag = 0
  AND p.payment_model_version = 1
ORDER BY p.create_time;

-- ---------------------------------------------------------------------------
-- 2) 人工清单候选：多样本
-- ---------------------------------------------------------------------------
SELECT
  p.id AS protocol_id,
  p.protocol_no,
  COUNT(s.id) AS active_sample_count,
  'multi_sample' AS skip_reason
FROM biz_collection_protocol p
INNER JOIN biz_protocol_sample s
  ON s.collection_protocol_id = p.id
 AND s.del_flag = 0
 AND IFNULL(s.sample_status, '') <> 'yzf'
WHERE p.del_flag = 0
  AND p.payment_model_version = 1
GROUP BY p.id, p.protocol_no
HAVING COUNT(s.id) > 1;

-- ---------------------------------------------------------------------------
-- 3) 人工清单候选：有退费痕迹的明细
-- ---------------------------------------------------------------------------
SELECT DISTINCT
  p.id AS protocol_id,
  p.protocol_no,
  d.id AS detail_id,
  d.pay_status,
  d.refund_amount,
  'has_refund' AS skip_reason
FROM biz_collection_protocol p
INNER JOIN biz_protocol_payment pay
  ON pay.collection_protocol_id = p.id AND pay.del_flag = 0
INNER JOIN biz_protocol_payment_detail d
  ON d.payment_id = pay.id AND d.del_flag = 0
WHERE p.del_flag = 0
  AND p.payment_model_version = 1
  AND (
    d.pay_status IN ('tfqrz', 'ytf')
    OR (d.refund_amount IS NOT NULL AND d.refund_amount > 0)
    OR IFNULL(d.refund_attachments, '') <> ''
    OR IFNULL(d.refund_remark, '') <> ''
    OR IFNULL(d.refund_finance_confirm_attachments, '') <> ''
    OR IFNULL(d.refund_finance_remark, '') <> ''
  );

-- ---------------------------------------------------------------------------
-- 4) 可自动迁（单样本、有 v1 缴费、尚无 v2 方案）粗筛
--    注意：退费还需用第 3 节排除，本查询仅看样本数
-- ---------------------------------------------------------------------------
SELECT
  p.id AS protocol_id,
  p.protocol_no,
  s.sample_code,
  pay.contract_amount,
  pay.pay_mode
FROM biz_collection_protocol p
INNER JOIN biz_protocol_payment pay
  ON pay.collection_protocol_id = p.id AND pay.del_flag = 0
INNER JOIN biz_protocol_sample s
  ON s.collection_protocol_id = p.id
 AND s.del_flag = 0
 AND IFNULL(s.sample_status, '') <> 'yzf'
WHERE p.del_flag = 0
  AND p.payment_model_version = 1
  AND NOT EXISTS (
    SELECT 1 FROM biz_protocol_sample_payment sp
    WHERE sp.collection_protocol_id = p.id AND sp.del_flag = 0
  )
GROUP BY p.id, p.protocol_no, s.sample_code, pay.contract_amount, pay.pay_mode
HAVING COUNT(DISTINCT s.id) = 1;

-- ---------------------------------------------------------------------------
-- 5) 迁移后抽样校验：某协议 v1 detail 金额合计 vs v2 transfer 金额合计
--    将 @pid 换成协议 ID
-- ---------------------------------------------------------------------------
-- SET @pid := 'YOUR_PROTOCOL_ID';
-- SELECT 'v1_detail' AS src, COUNT(*) AS cnt, IFNULL(SUM(transfer_amount), 0) AS amt
-- FROM biz_protocol_payment_detail d
-- INNER JOIN biz_protocol_payment pay ON pay.id = d.payment_id AND pay.del_flag = 0
-- WHERE pay.collection_protocol_id = @pid AND d.del_flag = 0
-- UNION ALL
-- SELECT 'v2_transfer', COUNT(*), IFNULL(SUM(transfer_amount), 0)
-- FROM biz_protocol_payment_transfer t
-- WHERE t.collection_protocol_id = @pid AND t.del_flag = 0;