From cb5a42c97fe9ae23b891bdc3231dc8eb100de218 Mon Sep 17 00:00:00 2001
From: shinnosuke <tantao@1hoper.com>
Date: 星期一, 31 八月 2026 16:49:41 +0800
Subject: [PATCH] fix: route hotel free voucher scans to dedicated verification page
---
talent-card-server/src/main/java/com/talentcard/server/mapper/StatisticsQuerySql.java | 102 +++++++++++++++++++++++++++++++++++++++++++++++++-
1 files changed, 99 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..0f856d6 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>
""");
@@ -329,12 +413,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