From 8dc19f0bd6c4abdc9cec1f9b99b90c33b9da966f Mon Sep 17 00:00:00 2001
From: yeg <1347747024@qq.com>
Date: 星期一, 12 十月 2026 17:24:12 +0800
Subject: [PATCH] fix(settlement): 共用收款主体那格改写「共用收款主体（N 家）」，悬停列出名单
---
talent-card-server/src/main/java/com/talentcard/server/mapper/StatisticsQuerySql.java | 105 +++++++++++++++++++++++++++++++++++++++++++++++++++-
1 files changed, 102 insertions(+), 3 deletions(-)
diff --git a/talent-card-server/src/main/java/com/talentcard/server/mapper/StatisticsQuerySql.java b/talent-card-server/src/main/java/com/talentcard/server/mapper/StatisticsQuerySql.java
index ef387db..e5ebb0b 100644
--- a/talent-card-server/src/main/java/com/talentcard/server/mapper/StatisticsQuerySql.java
+++ b/talent-card-server/src/main/java/com/talentcard/server/mapper/StatisticsQuerySql.java
@@ -185,6 +185,28 @@
<if test="merchantType != null and merchantType != ''">and m.merchant_type_code = #{merchantType}</if>
<if test="startTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) >= #{startTime}</if>
<if test="endTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) < #{endTime}</if>
+ union all
+ select case
+ when #{periodType} = 'MONTH' then date_format(r.verify_time, '%Y-%m')
+ else date_format(r.verify_time, '%Y-%m-%d')
+ end as period_key,
+ r.verify_time as business_time,
+ r.merchant_id,
+ m.merchant_name,
+ m.merchant_type,
+ 0 as order_amount,
+ 0 as pay_amount,
+ 0 as coupon_amount,
+ coalesce(r.subsidy_amount, 0) as government_subsidy_amount
+ from verify_record r
+ join merchant m on m.id = r.merchant_id and m.deleted = 0
+ where r.status = 1
+ and r.order_id is null
+ and upper(r.verify_type) = 'HOTEL_FREE'
+ <if test="merchantId != null">and r.merchant_id = #{merchantId}</if>
+ <if test="merchantType != null and merchantType != ''">and m.merchant_type_code = #{merchantType}</if>
+ <if test="startTime != null">and r.verify_time >= #{startTime}</if>
+ <if test="endTime != null">and r.verify_time < #{endTime}</if>
) base
group by base.period_key, base.merchant_id, base.merchant_name, base.merchant_type
order by base.period_key desc, base.merchant_id desc
@@ -234,6 +256,24 @@
<if test="merchantType != null and merchantType != ''">and m.merchant_type_code = #{merchantType}</if>
<if test="startTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) >= #{startTime}</if>
<if test="endTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) < #{endTime}</if>
+ union all
+ select r.verify_time as business_time,
+ r.merchant_id,
+ m.merchant_name,
+ m.merchant_type,
+ 0 as order_amount,
+ 0 as pay_amount,
+ 0 as coupon_amount,
+ coalesce(r.subsidy_amount, 0) as government_subsidy_amount
+ from verify_record r
+ join merchant m on m.id = r.merchant_id and m.deleted = 0
+ where r.status = 1
+ and r.order_id is null
+ and upper(r.verify_type) = 'HOTEL_FREE'
+ <if test="merchantId != null">and r.merchant_id = #{merchantId}</if>
+ <if test="merchantType != null and merchantType != ''">and m.merchant_type_code = #{merchantType}</if>
+ <if test="startTime != null">and r.verify_time >= #{startTime}</if>
+ <if test="endTime != null">and r.verify_time < #{endTime}</if>
) base
group by base.merchant_id
order by settlementAmountCents desc, base.merchant_id desc
@@ -244,6 +284,8 @@
public String selectSettlementExportDetails() {
return sql("""
<script>
+ select details.*
+ from (
select o.id as orderId,
o.order_no as orderNo,
o.order_type as orderType,
@@ -268,7 +310,8 @@
o.created_at as createdAt,
o.updated_at as paidAt,
v.verify_time as verifyTime,
- v.verify_operator as verifyOperator
+ v.verify_operator as verifyOperator,
+ coalesce(v.verify_time, o.updated_at, o.created_at) as businessTime
from benefit_order o
join merchant m on m.id = o.merchant_id and m.deleted = 0
left join merchant_store s on s.id = o.store_id and s.deleted = 0
@@ -288,7 +331,48 @@
<if test="verifyStatus != null">and o.verify_status = #{verifyStatus}</if>
<if test="startTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) >= #{startTime}</if>
<if test="endTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) < #{endTime}</if>
- order by coalesce(v.verify_time, o.updated_at, o.created_at) desc, o.id desc
+ union all
+ select r.id as orderId,
+ r.verify_no as orderNo,
+ 'HOTEL_FREE' as orderType,
+ r.merchant_id as merchantId,
+ m.merchant_name as merchantName,
+ m.merchant_type as merchantType,
+ r.store_id as storeId,
+ s.store_name as storeName,
+ r.talent_id as talentId,
+ t.name as talentName,
+ t.mobile as talentMobile,
+ tc.card_no as cardNo,
+ tc.card_level as cardLevel,
+ '酒店免费入住券' as productName,
+ 0 as originalAmountCents,
+ 0 as transactionAmountCents,
+ coalesce(r.subsidy_amount, 0) as subsidyAmountCents,
+ coalesce(r.subsidy_amount, 0) as settlementAmountCents,
+ 0 as couponAmountCents,
+ 1 as orderStatus,
+ 1 as verifyStatus,
+ r.verify_time as createdAt,
+ null as paidAt,
+ r.verify_time as verifyTime,
+ r.staff_id as verifyOperator,
+ r.verify_time as businessTime
+ from verify_record r
+ join merchant m on m.id = r.merchant_id and m.deleted = 0
+ left join merchant_store s on s.id = r.store_id and s.deleted = 0
+ left join talent_user t on t.id = r.talent_id and t.deleted = 0
+ left join talent_card tc on tc.id = r.card_id and tc.deleted = 0
+ where r.status = 1
+ and r.order_id is null
+ and upper(r.verify_type) = 'HOTEL_FREE'
+ and r.merchant_id = #{merchantId}
+ <if test="orderStatus != null">and #{orderStatus} = 1</if>
+ <if test="verifyStatus != null">and #{verifyStatus} = 1</if>
+ <if test="startTime != null">and r.verify_time >= #{startTime}</if>
+ <if test="endTime != null">and r.verify_time < #{endTime}</if>
+ ) details
+ order by details.businessTime desc, details.orderId desc
limit #{limit}
</script>
""");
@@ -298,10 +382,13 @@
return sql("""
select
coalesce((
+ -- 只算已核销的实付金额：未核销的钱还扣在通道的延迟户里，核销后才分账给商户；
+ -- 退款中的订单在通道结果收敛前仍是 order_status = 1，只有「已核销」能立刻挡住它。
select sum(o.pay_amount)
from benefit_order o
where o.deleted = 0
and o.order_status = 1
+ and o.verify_status = 1
and o.merchant_id = #{merchantId}
), 0) as transactionAmountCents,
coalesce((
@@ -329,12 +416,24 @@
where o.deleted = 0
and o.order_status = 1
and o.merchant_id = #{merchantId}
+ ), 0)
+ +
+ coalesce((
+ select sum(r.subsidy_amount)
+ from verify_record r
+ where r.status = 1
+ and r.order_id is null
+ and upper(r.verify_type) = 'HOTEL_FREE'
+ and r.merchant_id = #{merchantId}
), 0) as subsidyAmountCents
""");
}
private static String sql(String template) {
- return template.replace("/*AMOUNT*/", GovernmentSubsidySql.AMOUNT_CENTS)
+ String amount = template.contains("<script>")
+ ? GovernmentSubsidySql.AMOUNT_CENTS.replace("<", "<")
+ : GovernmentSubsidySql.AMOUNT_CENTS;
+ return template.replace("/*AMOUNT*/", amount)
.replace("/*ORDER_TIME*/", GovernmentSubsidySql.ORDER_TIME);
}
}
--
Gitblit v1.7.1