chore(scratch): 补退旧取消流程遗留在延迟户的两笔 ¥0.02（订单 520/521）

yeg

2026-09-17 fd5fc5e6e8d20ec9cb1b1288ea119c0b293cec1b

chore(scratch): 补退旧取消流程遗留在延迟户的两笔 ¥0.02（订单 520/521）

2026-09-16 旧取消逻辑只看本地流水就把订单取消并写成 LOCAL_CLOSED，而通道其实已经收款、
钱停在延迟户（trans_stat=S、delay_acct_flag=Y、unconfirm_amt=0.02、付款 openid 就是本人）。
本地既没有 SUCCEEDED 流水也没有退款记录，人才端显示「已取消」，退款入口按规则也不可用
（退款要求订单是已支付）。这里按仓库自己的网关类做一次性补退。

工具（默认只读查单，--execute 才退，--check 查退款结果）：
- 复用 HuifuMiniProgramPaymentGateway / HuifuRefundGateway / BusinessIdGenerator，
  汇付配置从 application.yml 读（私钥不进命令行）；
- 退款成功后把本地账补成与通道一致：支付流水由 CLOSED 改为 SUCCEEDED、补 order_refund 记录、
  按结果落订单退款状态；处理中留给对账任务收敛。

执行结果（2026-09-17 10:14，prod 环境）：
- 两笔退款均受理（bank_accepted_flag=Y），退款回调 4 秒内到达并 PROCESSED，
  本地落成 refund_status=2（通道回执与退款查询均为 S / 退款成功）；
- 通道侧核对：unconfirm_amt 由 0.02 → 0.00（延迟户已清），wx_response 显示
  cash_refund_fee=0.02、user_received_account=建设银行借记卡，即 ¥0.04 已退回付款人；
- 订单 520/521 现在 refund_status=2，人才端按新的状态词表显示「已退款」。

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

New file
@@ -0,0 +1,242 @@
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.talentcard.server.business.BusinessIdGenerator;
import com.talentcard.server.business.payment.huifu.HuifuMiniProgramPaymentGateway;
import com.talentcard.server.business.payment.huifu.HuifuPaymentProperties;
import com.talentcard.server.business.payment.huifu.HuifuPaymentQueryCommand;
import com.talentcard.server.business.payment.huifu.HuifuRefundCommand;
import com.talentcard.server.business.payment.huifu.HuifuRefundGateway;

/**
 * 一次性工具：把 2026-09-16 旧取消流程遗留在通道延迟户里的两笔 ¥0.02 退回来（订单 520/521）。
 *
 * <p>当时取消只看本地流水（写成 LOCAL_CLOSED），钱其实已经被通道收下并停在延迟户；本地既没有
 * SUCCEEDED 流水，也没有退款记录，人才端显示「已取消」。这里按仓库自己的网关类调接口：
 * 先查单确认钱在，再发起全额退款，成功后把本地账补成与通道一致（流水置 SUCCEEDED、补退款记录、
 * 订单退出结算范围）。
 *
 * <p>默认只读（查单）；加 {@code --execute} 才真正发起退款并落库。
 * 用法：java -cp ... RefundStuckDelayedPayments [--execute] &lt;yml路径&gt; &lt;jdbc-url&gt;
 */
public class RefundStuckDelayedPayments {

    /** 两笔目标：订单号 / 支付流水号 / 收款子商户 / 原交易请求日期（均为 2026-09-16）。 */
    private static final List<Target> TARGETS = List.of(
            new Target("P202609161320506589", "P202609161320512100", "6666000241804122", "20260916", 2L),
            new Target("P202609161325126470", "P202609161325168095", "6666000241804122", "20260916", 2L));

    private record Target(String orderNo, String paymentNo, String huifuId, String requestDate, long amountInCent) {}

    public static void main(String[] args) throws Exception {
        boolean execute = List.of(args).contains("--execute");
        boolean check = List.of(args).contains("--check");
        Path yml = Path.of(args[args.length - 2]);
        String jdbcUrl = args[args.length - 1];
        HuifuPaymentProperties properties = properties(yml);
        System.out.println("环境=" + properties.getEnvironment() + " 系统号=" + properties.getSystemId()
                + " 产品号=" + properties.getProductId() + " 收款商户=" + properties.getMerchantId()
                + " 模式=" + (execute ? "执行退款" : check ? "查退款结果" : "只读查单"));
        HuifuMiniProgramPaymentGateway queryGateway = new HuifuMiniProgramPaymentGateway(properties);
        HuifuRefundGateway refundGateway = new HuifuRefundGateway(properties);
        if (check) {
            checkRefunds(refundGateway, jdbcUrl);
            return;
        }

        try (Connection connection = DriverManager.getConnection(jdbcUrl, "root", "123456")) {
            for (Target target : TARGETS) {
                System.out.println();
                System.out.println("==== 订单 " + target.orderNo() + " / 支付流水 " + target.paymentNo());
                Map<String, Object> data = businessData(queryGateway.queryMiniProgramPayment(
                        new HuifuPaymentQueryCommand(target.paymentNo(), target.huifuId(), null, target.requestDate())));
                print("查单", data);
                String status = first(data, "trans_stat", "transStat");
                String code = first(data, "sub_resp_code", "resp_code", "code");
                String providerNo = first(data, "org_hf_seq_id", "orgHfSeqId", "hf_seq_id", "hfSeqId");
                String amount = first(data, "trans_amt", "transAmt");
                if (!"S".equalsIgnoreCase(status) || !successCode(code)) {
                    System.out.println("!! 通道未确认收款（trans_stat=" + status + " code=" + code + "），跳过退款");
                    continue;
                }
                if (!execute) {
                    System.out.println("-- 只读模式：确认收款 ¥" + amount + "（通道流水号 " + providerNo + "），加 --execute 才退款");
                    continue;
                }
                String refundNo = BusinessIdGenerator.next("R");
                Map<String, Object> refundData = businessData(refundGateway.createRefund(new HuifuRefundCommand(
                        refundNo, target.huifuId(), target.paymentNo(), providerNo, target.requestDate(), target.amountInCent())));
                print("退款", refundData);
                String refundCode = first(refundData, "sub_resp_code", "resp_code", "code");
                String refundTransStatus = first(refundData, "trans_stat", "transStat");
                String refundProviderNo = first(refundData, "hf_seq_id", "hfSeqId");
                // 与 HuifuRefundService 同一套判定：成功 / 处理中 / 失败，处理中留给对账任务收敛。
                int localRefundStatus = successCode(refundCode) || "S".equalsIgnoreCase(refundTransStatus) ? 2
                        : ("P".equalsIgnoreCase(refundTransStatus) || "00000100".equals(refundCode) ? 1 : 3);
                record(connection, target, refundNo, providerNo, refundData, refundProviderNo, localRefundStatus);
                System.out.println("-- 本地已补齐：流水置 SUCCEEDED、退款记录 " + refundNo + "（本地按 "
                        + (localRefundStatus == 2 ? "退款成功" : localRefundStatus == 1 ? "退款中，交对账收敛" : "退款失败") + " 落库）");
            }
        }
    }

    /** 查两笔退款的最新结果，并顺带看本地记录：退款成功后订单应退出结算范围。 */
    private static void checkRefunds(HuifuRefundGateway refundGateway, String jdbcUrl) throws Exception {
        try (Connection connection = DriverManager.getConnection(jdbcUrl, "root", "123456")) {
            for (Target target : TARGETS) {
                Map<String, Object> local = new LinkedHashMap<>();
                try (PreparedStatement statement = connection.prepareStatement("""
                        select r.refund_no, r.refund_status, r.provider_refund_no, r.provider_response_code,
                               r.provider_response_message, o.order_no, o.refund_status order_refund_status,
                               p.status payment_status
                        from order_refund r join benefit_order o on o.id = r.order_id
                        join payment_transaction p on p.payment_no = r.provider_original_req_seq_id
                        where r.order_no = ? and r.deleted = 0 order by r.id desc limit 1
                        """)) {
                    statement.setString(1, target.orderNo());
                    try (ResultSet rows = statement.executeQuery()) {
                        ResultSetMetaData meta = rows.getMetaData();
                        if (rows.next()) {
                            for (int i = 1; i <= meta.getColumnCount(); i++) local.put(meta.getColumnLabel(i), rows.getObject(i));
                        }
                    }
                }
                String refundNo = String.valueOf(local.getOrDefault("refund_no", ""));
                String providerRefundNo = String.valueOf(local.getOrDefault("provider_refund_no", ""));
                System.out.println();
                System.out.println("==== 订单 " + target.orderNo() + " / 退款 " + refundNo);
                System.out.println("-- 本地：refund_status=" + local.get("refund_status") + " 订单 refund_status=" + local.get("order_refund_status")
                        + " 支付流水=" + local.get("payment_status") + " 通道退款号=" + providerRefundNo);
                if (refundNo.isBlank()) continue;
                // 查询参数与 HuifuRefundService.query 一致：退款请求号 + 退款通道号 + 原交易日期都在记录里。
                Map<String, Object> data = businessData(refundGateway.queryRefund(new HuifuRefundCommand(
                        refundNo, target.huifuId(), text(local.get("provider_original_req_seq_id")),
                        text(local.get("provider_original_no")), text(local.get("provider_original_req_date")),
                        target.amountInCent()), providerRefundNo));
                print("退款查询", data);
            }
        }
    }

    /** 把本地账补成与通道一致：支付流水不再谎称 CLOSED，退款落到 order_refund，订单退出结算范围。 */
    private static void record(Connection connection, Target target, String refundNo, String providerNo,
                               Map<String, Object> refundData, String refundProviderNo, int localRefundStatus) throws Exception {
        connection.setAutoCommit(false);
        try (PreparedStatement payment = connection.prepareStatement("""
                update payment_transaction
                set status = 'SUCCEEDED', provider_transaction_no = coalesce(nullif(?, ''), provider_transaction_no),
                    provider_response_code = ?, provider_response_message = ?
                where payment_no = ? and status = 'CLOSED'
                """);
             PreparedStatement refund = connection.prepareStatement("""
                insert into order_refund(refund_no, order_id, order_no, talent_id, card_id, benefit_id, merchant_id,
                                         order_type, paid_amount, refund_amount, refundable_amount, refund_status,
                                         provider, provider_huifu_id, provider_refund_no, provider_original_no,
                                         provider_original_req_seq_id, provider_original_req_date,
                                         provider_response_code, provider_response_message, reason, operator_name, refund_time)
                select ?, o.id, o.order_no, o.talent_id, o.card_id, o.benefit_id, o.merchant_id, o.order_type,
                       ?, ?, 0, ?, 'HUIFU', ?, ?, ?, ?, ?, ?, ?, ?, ?, now()
                from benefit_order o where o.order_no = ? and o.deleted = 0
                """);
             PreparedStatement order = connection.prepareStatement(
                     "update benefit_order set refund_status = ? where order_no = ? and deleted = 0 and refund_status <> ?")) {
            payment.setString(1, providerNo);
            payment.setString(2, first(refundData, "sub_resp_code", "resp_code", "code"));
            payment.setString(3, first(refundData, "sub_resp_desc", "resp_desc", "message"));
            payment.setString(4, target.paymentNo());
            System.out.println("   支付流水更新 " + payment.executeUpdate() + " 行");

            refund.setString(1, refundNo);
            refund.setLong(2, target.amountInCent());
            refund.setLong(3, target.amountInCent());
            refund.setInt(4, localRefundStatus);
            refund.setString(5, target.huifuId());
            refund.setString(6, refundProviderNo);
            refund.setString(7, providerNo);
            refund.setString(8, target.paymentNo());
            refund.setString(9, target.requestDate());
            refund.setString(10, first(refundData, "sub_resp_code", "resp_code", "code"));
            refund.setString(11, first(refundData, "sub_resp_desc", "resp_desc", "message"));
            refund.setString(12, "取消订单后未退款的人工补退（旧取消流程遗留）");
            refund.setString(13, "系统补退");
            refund.setString(14, target.orderNo());
            System.out.println("   退款记录插入 " + refund.executeUpdate() + " 行（refund_status=" + localRefundStatus + "）");
            // 退款成功才让订单退出结算范围；失败则把订单退款状态退回未退款（与 markRefundFailed 一致）。
            int orderRefundStatus = localRefundStatus == 2 ? 2 : 0;
            if (localRefundStatus != 1) {
                order.setInt(1, orderRefundStatus);
                order.setString(2, target.orderNo());
                order.setInt(3, orderRefundStatus);
                System.out.println("   订单退款状态置为 " + orderRefundStatus + "：" + order.executeUpdate() + " 行（order_no=" + target.orderNo() + "）");
            }
            connection.commit();
        } catch (Exception exception) {
            connection.rollback();
            throw exception;
        }
    }

    /** 从 application.yml 读汇付配置：私钥不进命令行，也不写进这个文件。 */
    private static HuifuPaymentProperties properties(Path yml) throws Exception {
        String content = Files.readString(yml, StandardCharsets.UTF_8);
        HuifuPaymentProperties properties = new HuifuPaymentProperties();
        properties.setEnabled(true);
        properties.setEnvironment(value(content, "environment"));
        properties.setSystemId(value(content, "system-id"));
        properties.setProductId(value(content, "product-id"));
        properties.setMerchantId(value(content, "merchant-id"));
        properties.setMiniProgramAppId(value(content, "mini-program-app-id"));
        properties.setPrivateKey(value(content, "private-key"));
        properties.setPlatformPublicKey(value(content, "platform-public-key"));
        properties.setNotifyUrl(value(content, "notify-url"));
        return properties;
    }

    private static String value(String content, String key) {
        Matcher matcher = Pattern.compile("(?m)^\\s*" + Pattern.quote(key) + ":\\s*(.+?)\\s*$").matcher(content);
        if (!matcher.find()) throw new IllegalStateException("application.yml 缺少 " + key);
        String raw = matcher.group(1);
        Matcher env = Pattern.compile("^\\$\\{[^:}]+:(.*)\\}$").matcher(raw);
        return env.matches() ? env.group(1) : raw;
    }

    private static void print(String label, Map<String, Object> data) {
        StringBuilder line = new StringBuilder("-- " + label + "回执：");
        for (Map.Entry<String, Object> entry : data.entrySet()) line.append(entry.getKey()).append('=').append(entry.getValue()).append(' ');
        System.out.println(line);
    }

    @SuppressWarnings("unchecked")
    private static Map<String, Object> businessData(Map<String, Object> response) {
        Object data = response.get("data");
        if (data instanceof Map<?, ?> map) return new LinkedHashMap<>((Map<String, Object>) map);
        return new LinkedHashMap<>(response);
    }

    private static String text(Object value) {
        return value == null ? "" : String.valueOf(value).trim();
    }

    private static String first(Map<String, Object> data, String... keys) {
        for (String key : keys) {
            Object value = data.get(key);
            if (value != null && !String.valueOf(value).isBlank()) return String.valueOf(value).trim();
        }
        return "";
    }

    private static boolean successCode(String code) {
        return "00000000".equals(code) || "10000".equals(code) || "0".equals(code) || "SUCCESS".equalsIgnoreCase(code);
    }
}