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/AdminWorkbenchStatisticsMapper.java | 146 +-----------------------------------------------
1 files changed, 4 insertions(+), 142 deletions(-)
diff --git a/talent-card-server/src/main/java/com/talentcard/server/mapper/AdminWorkbenchStatisticsMapper.java b/talent-card-server/src/main/java/com/talentcard/server/mapper/AdminWorkbenchStatisticsMapper.java
index fcf7fe8..2c86409 100644
--- a/talent-card-server/src/main/java/com/talentcard/server/mapper/AdminWorkbenchStatisticsMapper.java
+++ b/talent-card-server/src/main/java/com/talentcard/server/mapper/AdminWorkbenchStatisticsMapper.java
@@ -4,6 +4,7 @@
import com.talentcard.server.entity.BenefitOrder;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
+import org.apache.ibatis.annotations.SelectProvider;
import java.util.List;
import java.util.Map;
@@ -12,152 +13,13 @@
@Mapper
public interface AdminWorkbenchStatisticsMapper extends BaseMapper<BenefitOrder> {
- @Select("""
- select
- coalesce(sum(case
- when coalesce(o.subsidy_amount, 0) > 0
- and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
- then o.subsidy_amount else 0 end), 0) as subsidyAmountCents,
- (select count(*) from benefit_order verified_order
- where verified_order.deleted = 0
- and verified_order.order_status = 1
- and verified_order.verify_status = 1
- and coalesce(
- (select max(v.verify_time) from verify_record v
- where v.order_id = verified_order.id and v.status = 1),
- (select max(pt.paid_at) from payment_transaction pt
- where pt.order_no = verified_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
- verified_order.updated_at
- ) >= date_format(current_date, '%Y-%m-01')
- and coalesce(
- (select max(v.verify_time) from verify_record v
- where v.order_id = verified_order.id and v.status = 1),
- (select max(pt.paid_at) from payment_transaction pt
- where pt.order_no = verified_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
- verified_order.updated_at
- ) < date_add(last_day(current_date), interval 1 day)) as monthlyVerificationCount,
- (select count(*) from talent_user u where u.deleted = 0) as talentCount,
- (select count(*) from benefit_order po where po.deleted = 0 and po.order_status = 0) as pendingOrderCount,
- (select count(*) from merchant_product pp where pp.deleted = 0 and pp.audit_status = 1) as pendingProductCount,
- (select count(*) from service_application sa where sa.deleted = 0 and sa.status in ('待处理', '待审核')) as pendingApplicationCount
- from benefit_order o
- left join merchant_product p on p.id = o.product_id and p.deleted = 0
- where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
- """)
+ @SelectProvider(type = StatisticsQuerySql.class, method = "selectSummary")
Map<String, Object> selectSummary();
- @Select("""
- select
- coalesce((select count(*) from talent_user u
- where u.deleted = 0
- and date(u.created_at) = date_sub(current_date, interval 1 day)), 0) as talentCompare,
- (coalesce((select count(*) from benefit_order current_month_order
- where current_month_order.deleted = 0
- and current_month_order.order_status = 1
- and current_month_order.verify_status = 1
- and coalesce(
- (select max(v.verify_time) from verify_record v
- where v.order_id = current_month_order.id and v.status = 1),
- (select max(pt.paid_at) from payment_transaction pt
- where pt.order_no = current_month_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
- current_month_order.updated_at
- ) >= date_format(current_date, '%Y-%m-01')
- and coalesce(
- (select max(v.verify_time) from verify_record v
- where v.order_id = current_month_order.id and v.status = 1),
- (select max(pt.paid_at) from payment_transaction pt
- where pt.order_no = current_month_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
- current_month_order.updated_at
- ) < date_add(last_day(current_date), interval 1 day)), 0)
- - coalesce((select count(*) from benefit_order previous_month_order
- where previous_month_order.deleted = 0
- and previous_month_order.order_status = 1
- and previous_month_order.verify_status = 1
- and coalesce(
- (select max(v.verify_time) from verify_record v
- where v.order_id = previous_month_order.id and v.status = 1),
- (select max(pt.paid_at) from payment_transaction pt
- where pt.order_no = previous_month_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
- previous_month_order.updated_at
- ) >= date_sub(date_format(current_date, '%Y-%m-01'), interval 1 month)
- and coalesce(
- (select max(v.verify_time) from verify_record v
- where v.order_id = previous_month_order.id and v.status = 1),
- (select max(pt.paid_at) from payment_transaction pt
- where pt.order_no = previous_month_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
- previous_month_order.updated_at
- ) < date_format(current_date, '%Y-%m-01')), 0)) as monthlyVerificationCompare,
- coalesce(sum(case
- when coalesce(o.subsidy_amount, 0) > 0
- and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
- then o.subsidy_amount else 0 end), 0) as subsidyCompareCents
- from benefit_order o
- left join merchant_product p on p.id = o.product_id and p.deleted = 0
- where o.deleted = 0
- and o.order_status = 1
- and o.verify_status = 1
- and exists (
- select 1 from verify_record yesterday_verify
- where yesterday_verify.order_id = o.id
- and yesterday_verify.status = 1
- and yesterday_verify.verify_time >= date_sub(current_date, interval 1 day)
- and yesterday_verify.verify_time < current_date
- )
- """)
+ @SelectProvider(type = StatisticsQuerySql.class, method = "selectCompareSummary")
Map<String, Object> selectCompareSummary();
- @Select("""
- select date_format(days.bucket_day, '%c/%e') as time,
- (select count(*)
- from talent_user u
- where u.deleted = 0
- and u.created_at < date_add(days.bucket_day, interval 1 day)) as talent,
- (select count(*)
- from benefit_order verified_order
- where verified_order.deleted = 0
- and verified_order.order_status = 1
- and verified_order.verify_status = 1
- and coalesce(
- (select max(v.verify_time) from verify_record v
- where v.order_id = verified_order.id and v.status = 1),
- (select max(pt.paid_at) from payment_transaction pt
- where pt.order_no = verified_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
- verified_order.updated_at
- ) >= days.bucket_day
- and coalesce(
- (select max(v.verify_time) from verify_record v
- where v.order_id = verified_order.id and v.status = 1),
- (select max(pt.paid_at) from payment_transaction pt
- where pt.order_no = verified_order.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
- verified_order.updated_at
- ) < date_add(days.bucket_day, interval 1 day)) as verification,
- round(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 o.subsidy_amount else 0 end)
- from benefit_order o
- left join merchant_product p on p.id = o.product_id and p.deleted = 0
- where o.deleted = 0
- and o.order_status = 1
- and o.verify_status = 1
- and exists (
- select 1
- from verify_record subsidy_verify
- where subsidy_verify.order_id = o.id
- and subsidy_verify.status = 1
- and subsidy_verify.verify_time < date_add(days.bucket_day, interval 1 day)
- )), 0) / 100, 2) as subsidy
- from (
- select date_sub(current_date, interval 6 day) as bucket_day union all
- select date_sub(current_date, interval 5 day) union all
- select date_sub(current_date, interval 4 day) union all
- select date_sub(current_date, interval 3 day) union all
- select date_sub(current_date, interval 2 day) union all
- select date_sub(current_date, interval 1 day) union all
- select current_date
- ) days
- order by days.bucket_day
- """)
+ @SelectProvider(type = StatisticsQuerySql.class, method = "selectSummaryTrend")
List<Map<String, Object>> selectSummaryTrend();
@Select("""
--
Gitblit v1.7.1