From 8dbcc40964ee315fc35abad757ae9a368f298bf9 Mon Sep 17 00:00:00 2001
From: shinnosuke <tantao@1hoper.com>
Date: 星期四, 27 八月 2026 13:54:36 +0800
Subject: [PATCH] fix: correct benefit tags and verification statistics
---
talent-card-server/src/main/java/com/talentcard/server/mapper/AdminWorkbenchStatisticsMapper.java | 158 +++++++++++++++++++++++++++++++++++++++++-----------
1 files changed, 125 insertions(+), 33 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 6c960bb..fcf7fe8 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
@@ -18,10 +18,24 @@
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 verify_record v
- where v.status = 1
- and v.verify_time >= date_format(current_date, '%Y-%m-01')
- and v.verify_time < date_add(last_day(current_date), interval 1 day)) as monthlyVerificationCount,
+ (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,
@@ -37,14 +51,42 @@
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 verify_record current_month_verify
- where current_month_verify.status = 1
- and current_month_verify.verify_time >= date_format(current_date, '%Y-%m-01')
- and current_month_verify.verify_time < date_add(last_day(current_date), interval 1 day)), 0)
- - coalesce((select count(*) from verify_record previous_month_verify
- where previous_month_verify.status = 1
- and previous_month_verify.verify_time >= date_sub(date_format(current_date, '%Y-%m-01'), interval 1 month)
- and previous_month_verify.verify_time < date_format(current_date, '%Y-%m-01')), 0)) as monthlyVerificationCompare,
+ (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)
@@ -71,10 +113,24 @@
where u.deleted = 0
and u.created_at < date_add(days.bucket_day, interval 1 day)) as talent,
(select count(*)
- from verify_record v
- where v.status = 1
- and v.verify_time >= days.bucket_day
- and v.verify_time < date_add(days.bucket_day, interval 1 day)) as verification,
+ 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)
@@ -107,7 +163,7 @@
@Select("""
select buckets.bucket_time as time,
coalesce(count(v.id), 0) as `order`,
- round(coalesce(sum(v.verify_amount), 0) / 100, 2) as amount
+ round(coalesce(sum(v.pay_amount), 0) / 100, 2) as amount
from (
select 0 bucket_hour, '0:00' bucket_time union all select 2, '2:00'
union all select 4, '4:00' union all select 6, '6:00'
@@ -116,10 +172,19 @@
union all select 16, '16:00' union all select 18, '18:00'
union all select 20, '20:00' union all select 22, '22:00'
) buckets
- left join verify_record v on v.status = 1
- and v.verify_time >= current_date
- and v.verify_time < date_add(current_date, interval 1 day)
- and floor(hour(v.verify_time) / 2) * 2 = buckets.bucket_hour
+ left join (
+ select o.id, o.pay_amount,
+ coalesce(
+ (select max(vr.verify_time) from verify_record vr where vr.order_id = o.id and vr.status = 1),
+ (select max(pt.paid_at) from payment_transaction pt
+ where pt.order_no = o.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
+ o.updated_at
+ ) as effective_time
+ from benefit_order o
+ where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
+ ) v on v.effective_time >= current_date
+ and v.effective_time < date_add(current_date, interval 1 day)
+ and floor(hour(v.effective_time) / 2) * 2 = buckets.bucket_hour
group by buckets.bucket_hour, buckets.bucket_time
order by buckets.bucket_hour
""")
@@ -128,7 +193,7 @@
@Select("""
select date_format(days.bucket_day, '%c/%e') as time,
coalesce(count(v.id), 0) as `order`,
- round(coalesce(sum(v.verify_amount), 0) / 100, 2) as amount
+ round(coalesce(sum(v.pay_amount), 0) / 100, 2) as amount
from (
select date_sub(current_date, interval 6 day) as bucket_day union all
select date_sub(current_date, interval 5 day) union all
@@ -138,9 +203,18 @@
select date_sub(current_date, interval 1 day) union all
select current_date
) days
- left join verify_record v on v.status = 1
- and v.verify_time >= days.bucket_day
- and v.verify_time < date_add(days.bucket_day, interval 1 day)
+ left join (
+ select o.id, o.pay_amount,
+ coalesce(
+ (select max(vr.verify_time) from verify_record vr where vr.order_id = o.id and vr.status = 1),
+ (select max(pt.paid_at) from payment_transaction pt
+ where pt.order_no = o.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
+ o.updated_at
+ ) as effective_time
+ from benefit_order o
+ where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
+ ) v on v.effective_time >= days.bucket_day
+ and v.effective_time < date_add(days.bucket_day, interval 1 day)
group by days.bucket_day
order by days.bucket_day
""")
@@ -149,7 +223,7 @@
@Select("""
select concat(weeks.week_start, '~', weeks.week_end) as time,
coalesce(count(v.id), 0) as `order`,
- round(coalesce(sum(v.verify_amount), 0) / 100, 2) as amount
+ round(coalesce(sum(v.pay_amount), 0) / 100, 2) as amount
from (
select 0 bucket_index,
date_format(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 0 day), '%c/%e') as week_start,
@@ -181,9 +255,18 @@
date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 28 day),
last_day(current_date)
) weeks
- left join verify_record v on v.status = 1
- and v.verify_time >= weeks.start_date
- and v.verify_time < date_add(weeks.end_date, interval 1 day)
+ left join (
+ select o.id, o.pay_amount,
+ coalesce(
+ (select max(vr.verify_time) from verify_record vr where vr.order_id = o.id and vr.status = 1),
+ (select max(pt.paid_at) from payment_transaction pt
+ where pt.order_no = o.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
+ o.updated_at
+ ) as effective_time
+ from benefit_order o
+ where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
+ ) v on v.effective_time >= weeks.start_date
+ and v.effective_time < date_add(weeks.end_date, interval 1 day)
where weeks.start_date <= last_day(current_date)
group by weeks.bucket_index, weeks.week_start, weeks.week_end, weeks.start_date
order by weeks.bucket_index
@@ -193,15 +276,24 @@
@Select("""
select concat(months.bucket_month, '月') as time,
coalesce(count(v.id), 0) as `order`,
- round(coalesce(sum(v.verify_amount), 0) / 100, 2) as amount
+ round(coalesce(sum(v.pay_amount), 0) / 100, 2) as amount
from (
select 1 bucket_month union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8
union all select 9 union all select 10 union all select 11 union all select 12
) months
- left join verify_record v on v.status = 1
- and year(v.verify_time) = year(current_date)
- and month(v.verify_time) = months.bucket_month
+ left join (
+ select o.id, o.pay_amount,
+ coalesce(
+ (select max(vr.verify_time) from verify_record vr where vr.order_id = o.id and vr.status = 1),
+ (select max(pt.paid_at) from payment_transaction pt
+ where pt.order_no = o.order_no and pt.status = 'SUCCEEDED' and pt.paid_at is not null),
+ o.updated_at
+ ) as effective_time
+ from benefit_order o
+ where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
+ ) v on year(v.effective_time) = year(current_date)
+ and month(v.effective_time) = months.bucket_month
group by months.bucket_month
order by months.bucket_month
""")
--
Gitblit v1.7.1