From 88c94d51fb9f82e856245908696ea4a970541a33 Mon Sep 17 00:00:00 2001
From: shinnosuke <tantao@1hoper.com>
Date: 星期五, 28 八月 2026 16:55:59 +0800
Subject: [PATCH] 又又更新
---
talent-card-server/src/main/java/com/talentcard/server/mapper/MerchantSettlementStatisticsMapper.java | 255 +-------------------------------------------------
1 files changed, 7 insertions(+), 248 deletions(-)
diff --git a/talent-card-server/src/main/java/com/talentcard/server/mapper/MerchantSettlementStatisticsMapper.java b/talent-card-server/src/main/java/com/talentcard/server/mapper/MerchantSettlementStatisticsMapper.java
index 06b430e..3fb5366 100644
--- a/talent-card-server/src/main/java/com/talentcard/server/mapper/MerchantSettlementStatisticsMapper.java
+++ b/talent-card-server/src/main/java/com/talentcard/server/mapper/MerchantSettlementStatisticsMapper.java
@@ -9,74 +9,12 @@
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
+import org.apache.ibatis.annotations.SelectProvider;
@Mapper
public interface MerchantSettlementStatisticsMapper extends BaseMapper<BenefitOrder> {
- @Select("""
- <script>
- select base.period_key as periodKey,
- base.merchant_id as merchantId,
- base.merchant_name as merchantName,
- base.merchant_type as merchantType,
- count(*) as orderCount,
- coalesce(sum(base.pay_amount), 0) as transactionAmountCents,
- coalesce(sum(base.government_subsidy_amount), 0) as subsidyAmountCents,
- coalesce(sum(base.pay_amount + base.government_subsidy_amount), 0) as settlementAmountCents,
- coalesce(sum(base.order_amount), 0) as originalAmountCents,
- coalesce(sum(base.coupon_amount), 0) as couponAmountCents,
- coalesce(sum(case when base.government_subsidy_amount > 0 then 1 else 0 end), 0) as governmentSubsidyOrderCount,
- min(base.business_time) as periodStartTime,
- max(base.business_time) as periodEndTime
- from (
- select case
- when #{periodType} = 'MONTH' then date_format(coalesce(v.verify_time, o.updated_at, o.created_at), '%Y-%m')
- else date_format(coalesce(v.verify_time, o.updated_at, o.created_at), '%Y-%m-%d')
- end as period_key,
- coalesce(v.verify_time, o.updated_at, o.created_at) as business_time,
- o.merchant_id,
- m.merchant_name,
- m.merchant_type,
- coalesce(o.order_amount, 0) as order_amount,
- coalesce(o.pay_amount, 0) as pay_amount,
- coalesce(o.coupon_amount, 0) as coupon_amount,
- (
- case
- when coalesce(o.subsidy_amount, 0) > 0
- and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
- then coalesce(o.subsidy_amount, 0)
- else 0
- end
- +
- case
- when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
- then coalesce(o.coupon_amount, 0)
- else 0
- end
- ) as government_subsidy_amount
- from benefit_order o
- join merchant m on m.id = o.merchant_id and m.deleted = 0
- left join merchant_product p on p.id = o.product_id and p.deleted = 0
- left join merchant_coupon c on c.id = o.coupon_id and c.deleted = 0
- left join (
- select order_id, max(verify_time) as verify_time
- from verify_record
- where status = 1
- group by order_id
- ) v on v.order_id = o.id
- where o.deleted = 0
- and o.order_status = 1
- and o.verify_status = 1
- <if test="merchantId != null">and o.merchant_id = #{merchantId}</if>
- <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>
- ) 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
- limit #{size} offset #{offset}
- </script>
- """)
+ @SelectProvider(type = StatisticsQuerySql.class, method = "selectPeriodSummaries")
List<Map<String, Object>> selectPeriodSummaries(@Param("merchantId") Long merchantId,
@Param("merchantType") String merchantType,
@Param("periodType") String periodType,
@@ -119,200 +57,21 @@
@Param("startTime") LocalDateTime startTime,
@Param("endTime") LocalDateTime endTime);
- @Select("""
- <script>
- select base.merchant_id as merchantId,
- max(base.merchant_name) as merchantName,
- max(base.merchant_type) as merchantType,
- count(*) as orderCount,
- coalesce(sum(base.pay_amount), 0) as transactionAmountCents,
- coalesce(sum(base.government_subsidy_amount), 0) as subsidyAmountCents,
- coalesce(sum(base.pay_amount + base.government_subsidy_amount), 0) as settlementAmountCents,
- coalesce(sum(base.order_amount), 0) as originalAmountCents,
- coalesce(sum(base.coupon_amount), 0) as couponAmountCents,
- coalesce(sum(case when base.government_subsidy_amount > 0 then 1 else 0 end), 0) as governmentSubsidyOrderCount,
- min(base.business_time) as startTime,
- max(base.business_time) as endTime
- from (
- select coalesce(v.verify_time, o.updated_at, o.created_at) as business_time,
- o.merchant_id,
- m.merchant_name,
- m.merchant_type,
- coalesce(o.order_amount, 0) as order_amount,
- coalesce(o.pay_amount, 0) as pay_amount,
- coalesce(o.coupon_amount, 0) as coupon_amount,
- (
- case
- when coalesce(o.subsidy_amount, 0) > 0
- and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
- then coalesce(o.subsidy_amount, 0)
- else 0
- end
- +
- case
- when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
- then coalesce(o.coupon_amount, 0)
- else 0
- end
- ) as government_subsidy_amount
- from benefit_order o
- join merchant m on m.id = o.merchant_id and m.deleted = 0
- left join merchant_product p on p.id = o.product_id and p.deleted = 0
- left join merchant_coupon c on c.id = o.coupon_id and c.deleted = 0
- left join (
- select order_id, max(verify_time) as verify_time
- from verify_record
- where status = 1
- group by order_id
- ) v on v.order_id = o.id
- where o.deleted = 0
- and o.order_status = 1
- and o.verify_status = 1
- <if test="merchantId != null">and o.merchant_id = #{merchantId}</if>
- <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>
- ) base
- group by base.merchant_id
- order by settlementAmountCents desc, base.merchant_id desc
- </script>
- """)
+ @SelectProvider(type = StatisticsQuerySql.class, method = "selectMerchantOverviews")
List<Map<String, Object>> selectMerchantOverviews(@Param("merchantId") Long merchantId,
@Param("merchantType") String merchantType,
@Param("startTime") LocalDateTime startTime,
@Param("endTime") LocalDateTime endTime);
- @Select("""
- <script>
- select o.id as orderId,
- o.order_no as orderNo,
- o.order_type as orderType,
- o.merchant_id as merchantId,
- m.merchant_name as merchantName,
- m.merchant_type as merchantType,
- o.store_id as storeId,
- s.store_name as storeName,
- o.talent_id as talentId,
- t.name as talentName,
- t.mobile as talentMobile,
- tc.card_no as cardNo,
- tc.card_level as cardLevel,
- coalesce(o.product_name_snapshot, p.product_name, '') as productName,
- coalesce(o.order_amount, 0) as originalAmountCents,
- coalesce(o.pay_amount, 0) as transactionAmountCents,
- (
- case
- when coalesce(o.subsidy_amount, 0) > 0
- and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
- then coalesce(o.subsidy_amount, 0)
- else 0
- end
- +
- case
- when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
- then coalesce(o.coupon_amount, 0)
- else 0
- end
- ) as subsidyAmountCents,
- coalesce(o.pay_amount, 0)
- +
- (
- case
- when coalesce(o.subsidy_amount, 0) > 0
- and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
- then coalesce(o.subsidy_amount, 0)
- else 0
- end
- +
- case
- when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
- then coalesce(o.coupon_amount, 0)
- else 0
- end
- ) as settlementAmountCents,
- coalesce(o.coupon_amount, 0) as couponAmountCents,
- o.order_status as orderStatus,
- o.verify_status as verifyStatus,
- o.created_at as createdAt,
- o.updated_at as paidAt,
- v.verify_time as verifyTime,
- v.verify_operator as verifyOperator
- 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
- left join talent_user t on t.id = o.talent_id and t.deleted = 0
- left join talent_card tc on tc.id = o.card_id and tc.deleted = 0
- left join merchant_product p on p.id = o.product_id and p.deleted = 0
- left join merchant_coupon c on c.id = o.coupon_id and c.deleted = 0
- left join (
- select order_id, max(verify_time) as verify_time, max(staff_id) as verify_operator
- from verify_record
- where status = 1
- group by order_id
- ) v on v.order_id = o.id
- where o.deleted = 0
- and o.merchant_id = #{merchantId}
- <if test="orderStatus != null">and o.order_status = #{orderStatus}</if>
- <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
- limit #{limit}
- </script>
- """)
+
+ @SelectProvider(type = StatisticsQuerySql.class, method = "selectSettlementExportDetails")
List<Map<String, Object>> selectSettlementExportDetails(@Param("merchantId") Long merchantId,
@Param("orderStatus") Integer orderStatus,
@Param("verifyStatus") Integer verifyStatus,
@Param("startTime") LocalDateTime startTime,
@Param("endTime") LocalDateTime endTime,
@Param("limit") int limit);
- @Select("""
- select
- coalesce((
- select sum(o.pay_amount)
- from benefit_order o
- where o.deleted = 0
- and o.order_status = 1
- and o.merchant_id = #{merchantId}
- ), 0) as transactionAmountCents,
- coalesce((
- select count(*)
- from verify_record r
- where r.status = 1
- and r.merchant_id = #{merchantId}
- and r.verify_time >= #{todayStart}
- and r.verify_time < #{todayEnd}
- ), 0) as todayVerificationCount,
- coalesce((
- select count(*)
- from verify_record r
- where r.status = 1
- and r.merchant_id = #{merchantId}
- and r.verify_time >= #{weekStart}
- and r.verify_time < #{weekEnd}
- ), 0) as weekVerificationCount,
- coalesce((
- select sum(
- case
- when coalesce(o.subsidy_amount, 0) > 0
- and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
- then coalesce(o.subsidy_amount, 0)
- else 0
- end
- +
- case
- when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
- then coalesce(o.coupon_amount, 0)
- else 0
- end
- )
- from benefit_order o
- left join merchant_product p on p.id = o.product_id and p.deleted = 0
- left join merchant_coupon c on c.id = o.coupon_id and c.deleted = 0
- where o.deleted = 0
- and o.order_status = 1
- and o.merchant_id = #{merchantId}
- ), 0) as subsidyAmountCents
- """)
+
+ @SelectProvider(type = StatisticsQuerySql.class, method = "selectDashboardCards")
Map<String, Object> selectDashboardCards(@Param("merchantId") Long merchantId,
@Param("todayStart") LocalDateTime todayStart,
@Param("todayEnd") LocalDateTime todayEnd,
--
Gitblit v1.7.1