chore(scratch): 补订单/支付/退款排查脚本，全部只读

yeg

2026-09-16 4b702cd9710bb223538ac68b9d96106a394199f6

chore(scratch): 补订单/支付/退款排查脚本，全部只读

本轮排查开出的四个 JDBC 小工具，放在 .scratch 里备用（仓库已有同类脚本）：

- OrderProbe / OrderProbe2 / RefundProbe：按订单号或"最近若干条"读出订单、支付流水、退款、
  回调事件、核销码与问题记录，用来核对通道事实与本地状态是否一致（只读，仅查开发库）。
- OrderStateProbe：把「已取消/已退款订单在列表与订单页显示成什么」用 SQL 直接算出来，改状态
  词表时不用起前端就能核对口径。
- ShufflePendingPayments：把共享测试库里历史遗留的挂起流水挪出对账窗口（只动 talent_card_test）。

前四个只读；最后一个写测试库，脚本里已注明不得指向开发库。

 .scratch/payment-cancel-confirmation/OrderProbe.java

New file
@@ -0,0 +1,58 @@
import java.sql.*;

/** Read-only probe: what the dev database says about the most recent orders and their payments. */
public class OrderProbe {
    public static void main(String[] args) throws Exception {
        String url = args.length > 0 ? args[0] : "jdbc:mysql://127.0.0.1:8134/talent_card?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false";
        try (Connection connection = DriverManager.getConnection(url, "root", "123456")) {
            query(connection, """
                    select id, order_no, order_type, order_status, verify_status, pay_amount, collect_huifu_id,
                           created_at, updated_at
                    from benefit_order order by id desc limit 12
                    """, "== 最近 12 笔订单 ==");
            query(connection, """
                    select payment_no, order_no, status, attempt_no, provider_huifu_id,
                           provider_response_code, provider_response_message, provider_transaction_no,
                           paid_at, closed_at, funds_confirmation_status, created_at
                    from payment_transaction order by id desc limit 15
                    """, "== 最近 15 条支付流水 ==");
            query(connection, """
                    select provider_request_no, process_status, signature_valid, received_at, processed_at
                    from payment_callback_event order by id desc limit 10
                    """, "== 最近 10 条回调事件（有没有回调真的到达过） ==");
            query(connection, """
                    select issue_type, business_no, order_no, expected_status, actual_status, amount, status,
                           detected_at, detail_message
                    from payment_reconciliation_issue order by id desc limit 10
                    """, "== 最近 10 条支付问题记录 ==");
            query(connection, """
                    select c.id, c.order_id, c.status, c.expires_at, c.created_at
                    from order_dynamic_verification_code c order by c.id desc limit 10
                    """, "== 最近 10 条订单核销码 ==");
            query(connection, """
                    select vr.verify_no, vr.order_id, vr.verify_type, vr.verify_time, vr.staff_id
                    from verify_record vr order by vr.id desc limit 10
                    """, "== 最近 10 条核销记录 ==");
        }
    }

    private static void query(Connection connection, String sql, String title) throws SQLException {
        System.out.println();
        System.out.println(title);
        try (Statement statement = connection.createStatement(); ResultSet rows = statement.executeQuery(sql)) {
            ResultSetMetaData meta = rows.getMetaData();
            int columns = meta.getColumnCount();
            StringBuilder header = new StringBuilder();
            for (int index = 1; index <= columns; index++) header.append(meta.getColumnLabel(index)).append(" | ");
            System.out.println(header);
            while (rows.next()) {
                StringBuilder line = new StringBuilder();
                for (int index = 1; index <= columns; index++) {
                    Object value = rows.getObject(index);
                    line.append(value == null ? "NULL" : String.valueOf(value)).append(" | ");
                }
                System.out.println(line);
            }
        }
    }
}

 .scratch/payment-cancel-confirmation/OrderProbe2.java

New file
@@ -0,0 +1,34 @@
import java.sql.*;

/** Read-only probe for one order: what the app would be served for it. */
public class OrderProbe2 {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://127.0.0.1:8134/talent_card?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false";
        String orderNo = args.length > 0 ? args[0] : "P202609161422152298";
        try (Connection connection = DriverManager.getConnection(url, "root", "123456")) {
            query(connection, "select id, order_no, talent_id, merchant_id, order_type, order_status, verify_status, pay_amount, expires_at, collect_huifu_id from benefit_order where order_no = '" + orderNo + "'", "== 订单 ==");
            query(connection, "select c.id, c.code_token, c.display_code, c.status, c.expires_at, c.created_at from order_dynamic_verification_code c join benefit_order o on o.id = c.order_id where o.order_no = '" + orderNo + "' order by c.id desc limit 4", "== 该订单最新核销码 ==");
            query(connection, "select payment_no, status, amount, paid_at, funds_confirmation_status from payment_transaction where order_no = '" + orderNo + "'", "== 该订单支付流水 ==");
        }
    }

    private static void query(Connection connection, String sql, String title) throws SQLException {
        System.out.println();
        System.out.println(title);
        try (Statement statement = connection.createStatement(); ResultSet rows = statement.executeQuery(sql)) {
            ResultSetMetaData meta = rows.getMetaData();
            int columns = meta.getColumnCount();
            StringBuilder header = new StringBuilder();
            for (int index = 1; index <= columns; index++) header.append(meta.getColumnLabel(index)).append(" | ");
            System.out.println(header);
            while (rows.next()) {
                StringBuilder line = new StringBuilder();
                for (int index = 1; index <= columns; index++) {
                    Object value = rows.getObject(index);
                    line.append(value == null ? "NULL" : String.valueOf(value)).append(" | ");
                }
                System.out.println(line);
            }
        }
    }
}

 .scratch/payment-cancel-confirmation/OrderStateProbe.java

New file
@@ -0,0 +1,49 @@
import java.sql.*;

/**
 * Read-only probe: how the talent app derives the state of orders that left the payment flow
 * (cancelled before payment, and refunded after payment). Mirrors the new CASE in
 * UsageRecordPortalService plus the cancelSource/refundStatusText the order page computes.
 */
public class OrderStateProbe {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://127.0.0.1:8134/talent_card?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false";
        try (Connection connection = DriverManager.getConnection(url, "root", "123456")) {
            query(connection, """
                    select o.order_no, o.order_status, o.verify_status, o.refund_status, o.pay_amount,
                           (select count(*) from order_refund r where r.order_id = o.id and r.deleted = 0) as refund_rows,
                           case
                             when o.order_status = 0 then 'pending_payment'
                             when o.order_status = 2 or o.refund_status <> 0 then 'cancelled'
                             when o.verify_status = 1 then 'completed'
                             else 'pending_verification'
                           end as list_status,
                           case when o.refund_status = 0 then '订单取消' else '退款处理' end as cancel_source,
                           case o.refund_status when 1 then '退款中' when 2 then '退款成功' when 3 then '退款失败' else '无需退款' end as refund_text
                    from benefit_order o
                    where o.deleted = 0 and (o.order_status = 2 or o.refund_status <> 0)
                    order by o.id desc limit 12
                    """, "== 已取消 / 已退款订单：列表状态与订单页文案 ==");
        }
    }

    private static void query(Connection connection, String sql, String title) throws SQLException {
        System.out.println();
        System.out.println(title);
        try (Statement statement = connection.createStatement(); ResultSet rows = statement.executeQuery(sql)) {
            ResultSetMetaData meta = rows.getMetaData();
            int columns = meta.getColumnCount();
            StringBuilder header = new StringBuilder();
            for (int index = 1; index <= columns; index++) header.append(meta.getColumnLabel(index)).append(" | ");
            System.out.println(header);
            while (rows.next()) {
                StringBuilder line = new StringBuilder();
                for (int index = 1; index <= columns; index++) {
                    Object value = rows.getObject(index);
                    line.append(value == null ? "NULL" : String.valueOf(value)).append(" | ");
                }
                System.out.println(line);
            }
        }
    }
}

 .scratch/payment-cancel-confirmation/RefundProbe.java

New file
@@ -0,0 +1,46 @@
import java.sql.*;

/** Read-only probe: the most recent refunds and the order/fund state they left behind. */
public class RefundProbe {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://127.0.0.1:8134/talent_card?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false";
        try (Connection connection = DriverManager.getConnection(url, "root", "123456")) {
            query(connection, """
                    select r.id, r.refund_no, r.order_no, r.order_type, r.paid_amount, r.refund_amount, r.refundable_amount,
                           r.refund_status, r.provider_refund_no, r.provider_response_code, r.provider_response_message,
                           r.reason, r.operator_name, r.refund_time, r.created_at
                    from order_refund r where r.deleted = 0 order by r.id desc limit 8
                    """, "== 最近 8 笔退款 ==");
            query(connection, """
                    select o.id, o.order_no, o.order_type, o.order_status, o.verify_status, o.refund_status,
                           o.pay_amount, o.updated_at,
                           (select coalesce(sum(r.refund_amount), 0) from order_refund r
                             where r.order_id = o.id and r.deleted = 0 and r.refund_status = 2) as refunded_success,
                           (select count(*) from order_refund r where r.order_id = o.id and r.deleted = 0) as refund_rows
                    from benefit_order o
                    where exists (select 1 from order_refund r where r.order_id = o.id and r.deleted = 0)
                    order by o.id desc limit 8
                    """, "== 有退款记录的订单：订单状态 / 核销状态 / 退款状态 / 已成功退款额 ==");
        }
    }

    private static void query(Connection connection, String sql, String title) throws SQLException {
        System.out.println();
        System.out.println(title);
        try (Statement statement = connection.createStatement(); ResultSet rows = statement.executeQuery(sql)) {
            ResultSetMetaData meta = rows.getMetaData();
            int columns = meta.getColumnCount();
            StringBuilder header = new StringBuilder();
            for (int index = 1; index <= columns; index++) header.append(meta.getColumnLabel(index)).append(" | ");
            System.out.println(header);
            while (rows.next()) {
                StringBuilder line = new StringBuilder();
                for (int index = 1; index <= columns; index++) {
                    Object value = rows.getObject(index);
                    line.append(value == null ? "NULL" : String.valueOf(value)).append(" | ");
                }
                System.out.println(line);
            }
        }
    }
}

 .scratch/payment-cancel-confirmation/ShufflePendingPayments.java

New file
@@ -0,0 +1,25 @@
import java.sql.*;

/**
 * Clears accumulated rubbish from the shared test schema: pending payment rows left behind by earlier
 * runs would otherwise fall inside the reconciliation window and pollute tests that assert on it.
 * Test schema only — never point this at the dev schema.
 */
public class ShufflePendingPayments {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://127.0.0.1:8134/talent_card_test?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true&useSSL=false";
        try (Connection connection = DriverManager.getConnection(url, "root", "123456");
             Statement statement = connection.createStatement()) {
            int moved = statement.executeUpdate("""
                    update payment_transaction set created_at = now()
                    where status in ('CREATED', 'PREPAY_CREATED', 'CREATE_UNKNOWN', 'CANCEL_UNCONFIRMED')
                      and created_at <= date_sub(now(), interval 5 minute)
                    """);
            int closed = statement.executeUpdate("""
                    update payment_transaction set status = 'CLOSED', closed_at = coalesce(closed_at, now())
                    where status = 'CANCEL_UNCONFIRMED' and created_at <= date_sub(now(), interval 5 minute)
                    """);
            System.out.println("movedIntoWindow=" + moved + ", strayCancelUnconfirmedClosed=" + closed);
        }
    }
}