From 50784fc40d98faa46af89a0b2c2e4f22c06d6dcb Mon Sep 17 00:00:00 2001
From: yeg <1347747024@qq.com>
Date: 星期六, 12 九月 2026 17:42:03 +0800
Subject: [PATCH] fix(merchant): 收紧自动提现汇总的结算配置与门店范围
---
talent-card-server/src/main/java/com/talentcard/server/business/merchant/AutoWithdrawalAggregationService.java | 186 ++++++++++++++++++++++++++--------------------
1 files changed, 106 insertions(+), 80 deletions(-)
diff --git a/talent-card-server/src/main/java/com/talentcard/server/business/merchant/AutoWithdrawalAggregationService.java b/talent-card-server/src/main/java/com/talentcard/server/business/merchant/AutoWithdrawalAggregationService.java
index c15cc29..43ed10b 100644
--- a/talent-card-server/src/main/java/com/talentcard/server/business/merchant/AutoWithdrawalAggregationService.java
+++ b/talent-card-server/src/main/java/com/talentcard/server/business/merchant/AutoWithdrawalAggregationService.java
@@ -14,11 +14,10 @@
import com.talentcard.server.business.DatabaseRows;
import com.talentcard.server.business.order.OrderFundBoundaryService;
import com.talentcard.server.common.BusinessException;
-import com.talentcard.server.common.ErrorCode;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
-import org.springframework.dao.DuplicateKeyException;
import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.support.TransactionTemplate;
@@ -26,26 +25,28 @@
/**
* 每日自动提现汇总。
*
- * <p>按北京时间取前一个自然日，把每个商户「截至该日结束已核销、未退款、还没进入其它提现记录」的订单
- * 汇总成一条提现记录：金额写 {@code merchant_withdrawal}，来源订单写内部关联表
+ * <p>按北京时间取前一个自然日，把每个商户结算账户下「已支付、已核销、未退款、还没进入其它提现记录」
+ * 的订单汇总成一条提现记录：金额写 {@code merchant_withdrawal}，来源订单写内部关联表
* {@code merchant_withdrawal_order}，提现记录的业务日写 {@code withdrawal_date}。
* 汇总后的订单通过 {@link OrderFundBoundaryService#lockIntoWithdrawal(long)} 标记为已进入提现记录，
- * 因此不能再撤销核销或退款，也不会被以后的任务重复计入。
+ * 因此不能再撤销核销或退款，也不会被以后的任务重复计入（关联表的 {@code order_id} 唯一索引兜底）。
*
- * <p>业务日窗口是「核销时间不晚于该业务日 24:00」，没有下界：任务在凌晨跑，因此当天白天核销的订单
- * 都落在窗口内；某个业务日没被汇总的订单（核销时退款还在途、或商户当时没有可用的结算银行卡）
- * 不会被丢掉，下一次任务仍在窗口内，会随新订单一起汇总。这个选择保证订单不会因为一次跳过而永久
- * 留在未提现状态，代价是一次任务会扫描商户全部未进入提现记录的已核销订单（{@code verify_record.verify_time}
- * 和 {@code benefit_order.merchant_id} 都有索引）。
+ * <p>业务日窗口是「核销时间不晚于该业务日 24:00，且不早于该业务日往前 {@value #MAX_LOOKBACK_DAYS} 天」。
+ * 任务在凌晨跑，因此当天白天核销的订单都落在窗口内；某天被跳过的订单（核销时退款还在途、商户当时
+ * 没有可用的结算银行卡、当天金额不足）在下一次任务里仍在窗口内，会继续累积（顺延），不会因为一次
+ * 跳过就永久留在未提现状态。下界的作用是给首次上线兜底：不会把多年前的历史订单一次性汇总进来。
*
- * <p>可提金额低于最低提现金额、或扣掉手续费缓冲后不足最低金额时，这个商户当天不生成记录，
- * 金额留到下一次任务继续累积（顺延）。
+ * <p>只有「审批通过、登记了斗拱子商户号、且有审核通过的结算银行卡（带 token）」的商户才会生成记录，
+ * 否则这一天的金额顺延——生成一条提交不出去的记录会把订单永久锁死。
+ *
+ * <p>可提金额低于最低提现金额、或扣掉手续费缓冲后仍不足最低金额时，这一天的金额顺延到下一次任务。
*
* <p>幂等：同一商户同一业务日只可能有一条日汇总记录（先查后写，并有
- * {@code uk_merchant_withdrawal_daily} 兜底），任务重复执行不会重复生成提现金额。
+ * {@code uk_merchant_withdrawal_daily(merchant_id, store_key, withdrawal_date)} 兜底），
+ * 任务重复执行或两个实例并发都不会重复生成提现金额。
*
- * <p>只在本服务内生成提现记录并把订单标记为处理中，不调用斗拱主动取现接口；提交给斗拱、处理
- * 到账结果和失败顺延由后续工单负责。
+ * <p>只在本服务内生成提现记录并把订单标记为已进入提现记录，不调用斗拱主动取现接口；提交给斗拱、
+ * 处理到账结果和失败顺延由工单 05 负责。
*/
@Service
public class AutoWithdrawalAggregationService {
@@ -55,46 +56,56 @@
/** 日汇总和日期边界一律按北京时间计算，与商户看到的「日」一致。 */
public static final ZoneId BEIJING = ZoneId.of("Asia/Shanghai");
+ /** 业务日窗口的下界长度：订单最多在窗口里等这么久，给首次上线和长期跳过的订单兜底。 */
+ static final int MAX_LOOKBACK_DAYS = 30;
+
private static final DateTimeFormatter BUSINESS_DATE = DateTimeFormatter.ofPattern("yyyyMMdd");
+ /**
+ * 每个「商户 + 门店」在业务日窗口内有可汇总订单、且结算配置完整时的候选。
+ *
+ * <p>结算银行卡必须和 token 来自同一张卡：审核通过的首绑卡带 token 就用它，否则整张换成该门店
+ * 最新一张带 token 的审核通过卡，再取这张卡的 id。否则会出现「记录指向一张没有 token 的卡」
+ * 这种提交不出去的组合。
+ */
private static final String ELIGIBLE_MERCHANTS = """
- select o.merchant_id, o.store_id, coalesce(i.id, r.id) as bank_card_id,
- coalesce(i.provider_token_no, r2.provider_token_no) as token_no
+ select o.merchant_id, o.store_id, card.id as bank_card_id
from merchant_onboarding_application o
- join (select merchant_id, store_id, max(id) as onboarding_id
- from merchant_onboarding_application
- where status = 'approved'
- group by merchant_id, store_id) newest
- on newest.onboarding_id = o.id
left join merchant_bank_card i on i.id = o.initial_bank_card_id and i.status = 'approved'
left join (select merchant_id, store_id, max(id) as id
from merchant_bank_card
where status = 'approved' and provider_token_no is not null and provider_token_no <> ''
- group by merchant_id, store_id) r
- on r.merchant_id = o.merchant_id and r.store_id = o.store_id
- left join merchant_bank_card r2 on r2.id = r.id
- where coalesce(i.provider_token_no, r2.provider_token_no) is not null
- and coalesce(i.provider_token_no, r2.provider_token_no) <> ''
+ group by merchant_id, store_id) fallback
+ on fallback.merchant_id = o.merchant_id and fallback.store_id = o.store_id
+ join merchant_bank_card card
+ on card.id = if(i.provider_token_no is not null and i.provider_token_no <> '', i.id, fallback.id)
+ where card.status = 'approved'
+ and card.provider_token_no is not null and card.provider_token_no <> ''
+ and o.provider_huifu_id is not null and o.provider_huifu_id <> ''
+ and o.status = 'approved'
+ and o.id = (select max(newest.id) from merchant_onboarding_application newest
+ where newest.merchant_id = o.merchant_id and newest.store_id = o.store_id
+ and newest.status = 'approved')
and exists (select 1
from benefit_order bo
join verify_record vr on vr.order_id = bo.id and vr.status = 1
- where bo.merchant_id = o.merchant_id
+ where bo.merchant_id = o.merchant_id and bo.store_id = o.store_id
and bo.deleted = 0 and bo.order_status = 1 and bo.verify_status = 1
and bo.refund_status = 0 and bo.withdrawal_status = 0
- and vr.verify_time < ?)
- order by o.id desc
+ and vr.verify_time < ? and vr.verify_time >= ?)
""";
+ /** 权威的可汇总订单查询：行锁复核之前先按同样的条件取一次。 */
private static final String ELIGIBLE_ORDERS = """
select bo.id, bo.pay_amount
from benefit_order bo
- where bo.merchant_id = ?
+ where bo.merchant_id = ? and bo.store_id = ?
and bo.deleted = 0 and bo.order_status = 1 and bo.verify_status = 1
and bo.refund_status = 0 and bo.withdrawal_status = 0
and exists (select 1
from verify_record vr
where vr.order_id = bo.id and vr.status = 1
- and vr.verify_time < ?)
+ and vr.verify_time < ? and vr.verify_time >= ?)
order by bo.id asc
""";
@@ -131,32 +142,30 @@
/** 某个业务日的汇总。业务日始终按北京时间解释，因此测试和补跑都可以指定具体日期。 */
public Result run(LocalDate businessDate) {
- // 窗口右端是该业务日 24:00（北京时间）。任务在凌晨执行，当天核销的订单都在窗口内；
- // 没被汇总的订单下一次仍在窗口内，会随新订单一起顺延。
+ // 窗口右端是该业务日 24:00：任务在凌晨执行，当天核销的订单都在窗口内。
LocalDateTime windowEnd = businessDate.plusDays(1).atStartOfDay();
+ // 窗口左端给首次上线兜底，避免把历史订单一次性全部汇总。
+ LocalDateTime windowStart = businessDate.minusDays(MAX_LOOKBACK_DAYS).atStartOfDay();
String dateText = businessDate.format(BUSINESS_DATE);
Result result = new Result(dateText);
for (Map<String, Object> candidate : DatabaseRows.normalize(jdbc.queryForList(
- ELIGIBLE_MERCHANTS, windowEnd))) {
+ ELIGIBLE_MERCHANTS, windowEnd, windowStart))) {
Long merchantId = number(candidate.get("merchantId"));
Long storeId = number(candidate.get("storeId"));
Long bankCardId = number(candidate.get("bankCardId"));
+ if (merchantId == null || storeId == null || bankCardId == null) continue;
try {
- aggregateMerchant(merchantId, storeId, bankCardId, dateText, windowEnd, result);
+ aggregate(merchantId, storeId, bankCardId, dateText, windowEnd, windowStart, result);
+ } catch (DuplicateKeyException exception) {
+ // 与另一个实例的汇总撞在一起：这一天的记录由对方生成，本实例没有出账。
+ result.merchantSkipped();
+ log.info("Auto withdrawal for merchant {} store {} on {} already exists, created by another run",
+ merchantId, storeId, dateText);
} catch (RuntimeException exception) {
- // 一个商户汇总失败不影响其它商户；失败的订单保持未锁定，下一次任务会重新汇总。
- // 与另一个实例撞在一起时唯一索引会抛 DuplicateKeyException：那一天的记录由对方生成，
- // 本实例没有出账，属于「已存在」而不是失败。
- if (exception instanceof DuplicateKeyException) {
- result.merchantSkipped();
- log.info("Auto withdrawal for merchant {} on {} already exists, created by another run",
- merchantId, dateText);
- } else {
- result.merchantFailed();
- log.warn("Auto withdrawal aggregation failed for merchant {} on {}: {}",
- merchantId, dateText, exception.getClass().getSimpleName());
- }
+ result.merchantFailed();
+ log.warn("Auto withdrawal aggregation failed for merchant {} store {} on {}: {}: {}",
+ merchantId, storeId, dateText, exception.getClass().getSimpleName(), exception.getMessage());
}
}
return result;
@@ -167,39 +176,45 @@
return LocalDate.ofInstant(instant, BEIJING).minusDays(1);
}
- private void aggregateMerchant(Long merchantId, Long storeId, Long bankCardId, String dateText,
- LocalDateTime windowEnd, Result result) {
- if (merchantId == null || storeId == null || bankCardId == null) return;
- transactions.executeWithoutResult(status -> {
- if (hasDailyWithdrawal(merchantId, dateText)) {
- result.merchantSkipped();
- return;
- }
+ /** 一个「商户 + 门店」一个业务日的汇总，整体在一个事务里完成。 */
+ private void aggregate(Long merchantId, Long storeId, Long bankCardId, String dateText,
+ LocalDateTime windowEnd, LocalDateTime windowStart, Result result) {
+ Contribution contribution = transactions.execute(status -> {
+ if (hasDailyWithdrawal(merchantId, storeId, dateText)) return Contribution.SKIPPED;
List<OrderAmount> orders = new ArrayList<>();
long total = 0;
for (Map<String, Object> row : DatabaseRows.normalize(jdbc.queryForList(
- ELIGIBLE_ORDERS, merchantId, windowEnd))) {
+ ELIGIBLE_ORDERS, merchantId, storeId, windowEnd, windowStart))) {
Long orderId = number(row.get("id"));
Long payAmount = number(row.get("payAmount"));
- long amount = payAmount == null ? 0 : payAmount;
OrderFundBoundaryService.LockedOrder locked = orderFundBoundary.lock(orderId);
- if (locked.withdrawalLocked()) throw new BusinessException(ErrorCode.CONFLICT, "订单已进入其它提现记录");
- if (!isEligible(locked)) throw new BusinessException(ErrorCode.CONFLICT, "订单已不满足进入提现记录的条件");
+ if (!isEligible(locked)) {
+ // 候选查询之后订单被退款或撤销核销：只跳过这一单，同一天其它订单照常汇总。
+ log.info("Skip order {} of merchant {} store {} on {}: {}", orderId, merchantId, storeId,
+ dateText, locked.withdrawalLocked() ? "already in a withdrawal record" : "no longer eligible");
+ continue;
+ }
+ long amount = payAmount == null ? 0 : payAmount;
orders.add(new OrderAmount(orderId, amount));
total += amount;
}
- if (orders.isEmpty()) return;
- if (deferred(total)) {
- result.merchantDeferred();
- return;
- }
+ if (orders.isEmpty()) return Contribution.NONE;
+ if (deferred(total)) return Contribution.DEFERRED;
createWithdrawal(merchantId, storeId, bankCardId, dateText, total, orders);
- result.merchantAggregated(total, orders.size());
+ return new Contribution(Contribution.Outcome.AGGREGATED, total, orders.size());
});
+ // 事务提交成功之后才计数，提交失败不会被算成已汇总。
+ if (contribution == null) return;
+ switch (contribution.outcome()) {
+ case AGGREGATED -> result.merchantAggregated(contribution.amountCents(), contribution.orders());
+ case DEFERRED -> result.merchantDeferred();
+ case SKIPPED -> result.merchantSkipped();
+ case NONE -> { }
+ }
}
- /** 行锁拿到的状态才是判断依据：候选查询之后订单可能已经被退款或核销撤销。 */
+ /** 行锁拿到的状态才是判断依据：候选查询之后订单可能已经被退款、撤销核销或进入别的提现记录。 */
private boolean isEligible(OrderFundBoundaryService.LockedOrder locked) {
return locked.orderStatus() == 1 && locked.verifyStatus() == 1
&& locked.refundStatus() == OrderFundBoundaryService.REFUND_NONE
@@ -212,17 +227,17 @@
return total < minimum || total - buffer < minimum;
}
- private boolean hasDailyWithdrawal(Long merchantId, String dateText) {
+ private boolean hasDailyWithdrawal(Long merchantId, Long storeId, String dateText) {
Integer count = jdbc.queryForObject(
- "select count(*) from merchant_withdrawal where merchant_id = ? and withdrawal_date = ?",
- Integer.class, merchantId, dateText);
+ "select count(*) from merchant_withdrawal where merchant_id = ? and store_id = ? and withdrawal_date = ?",
+ Integer.class, merchantId, storeId, dateText);
return count != null && count > 0;
}
private void createWithdrawal(Long merchantId, Long storeId, Long bankCardId, String dateText,
long total, List<OrderAmount> orders) {
- // 业务请求号固定为商户 + 业务日：重复插入会被 uk_merchant_withdrawal_key 拦住，不会重复出账。
- String idempotencyKey = "auto-withdrawal:" + merchantId + ":" + dateText;
+ // 业务请求号固定为商户 + 门店 + 业务日：重复插入会被 uk_merchant_withdrawal_key 拦住，不会重复出账。
+ String idempotencyKey = "auto-withdrawal:" + merchantId + ":" + storeId + ":" + dateText;
jdbc.update("""
insert into merchant_withdrawal(merchant_id, store_id, withdrawal_no, bank_card_id,
amount_cents, idempotency_key, withdrawal_date)
@@ -231,13 +246,14 @@
Long withdrawalId = jdbc.queryForObject(
"select id from merchant_withdrawal where merchant_id = ? and idempotency_key = ?",
Long.class, merchantId, idempotencyKey);
- for (OrderAmount order : orders) {
- orderFundBoundary.lockIntoWithdrawal(order.orderId());
- jdbc.update("""
- insert into merchant_withdrawal_order(withdrawal_id, order_id, amount_cents)
- values (?, ?, ?)
- """, withdrawalId, order.orderId(), order.amount());
- }
+ // 行已经被 lock(orderId) 锁住，这里只需要写锁定状态并登记关联。
+ jdbc.batchUpdate("update benefit_order set withdrawal_status = 1 where id = ? and withdrawal_status = 0",
+ orders.stream().map(order -> new Object[] { order.orderId() }).toList());
+ jdbc.batchUpdate("""
+ insert into merchant_withdrawal_order(withdrawal_id, order_id, amount_cents) values (?, ?, ?)
+ """, orders.stream()
+ .map(order -> new Object[] { withdrawalId, order.orderId(), order.amount() })
+ .toList());
}
private Long number(Object value) {
@@ -246,7 +262,7 @@
private record OrderAmount(Long orderId, long amount) { }
- /** 一次汇总的结果，用于日志和测试断言。 */
+ /** 一次（商户 + 门店 + 业务日）汇总的结果，用于日志和测试断言。 */
public static final class Result {
private final String businessDate;
@@ -294,4 +310,14 @@
+ " failed=" + merchantsFailed + " orders=" + ordersAggregated + " amountCents=" + amountCents;
}
}
+
+ /** 一个（商户 + 门店 + 业务日）汇总的事务结果。 */
+ private record Contribution(Outcome outcome, long amountCents, int orders) {
+
+ static final Contribution NONE = new Contribution(Outcome.NONE, 0, 0);
+ static final Contribution DEFERRED = new Contribution(Outcome.DEFERRED, 0, 0);
+ static final Contribution SKIPPED = new Contribution(Outcome.SKIPPED, 0, 0);
+
+ enum Outcome { AGGREGATED, DEFERRED, SKIPPED, NONE }
+ }
}
--
Gitblit v1.7.1