package com.talentcard.server.mapper;
|
|
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
|
import com.talentcard.server.entity.BenefitOrder;
|
import org.apache.ibatis.annotations.Mapper;
|
import org.apache.ibatis.annotations.Select;
|
|
import java.util.List;
|
import java.util.Map;
|
|
/** 后台工作台统计查询，负责读取真实统计数据。 */
|
@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
|
""")
|
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
|
)
|
""")
|
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
|
""")
|
List<Map<String, Object>> selectSummaryTrend();
|
|
@Select("""
|
select buckets.bucket_time as time,
|
coalesce(count(v.id), 0) as `order`,
|
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'
|
union all select 8, '8:00' union all select 10, '10:00'
|
union all select 12, '12:00' union all select 14, '14:00'
|
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 (
|
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
|
""")
|
List<Map<String, Object>> selectDailyVerificationTrend();
|
|
@Select("""
|
select date_format(days.bucket_day, '%c/%e') as time,
|
coalesce(count(v.id), 0) as `order`,
|
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
|
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
|
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
|
""")
|
List<Map<String, Object>> selectWeeklyVerificationTrend();
|
|
@Select("""
|
select concat(weeks.week_start, '~', weeks.week_end) as time,
|
coalesce(count(v.id), 0) as `order`,
|
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,
|
date_format(least(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 6 day), last_day(current_date)), '%c/%e') as week_end,
|
date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 0 day) as start_date,
|
least(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 6 day), last_day(current_date)) as end_date
|
union all
|
select 1,
|
date_format(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 7 day), '%c/%e'),
|
date_format(least(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 13 day), last_day(current_date)), '%c/%e'),
|
date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 7 day),
|
least(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 13 day), last_day(current_date))
|
union all
|
select 2,
|
date_format(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 14 day), '%c/%e'),
|
date_format(least(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 20 day), last_day(current_date)), '%c/%e'),
|
date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 14 day),
|
least(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 20 day), last_day(current_date))
|
union all
|
select 3,
|
date_format(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 21 day), '%c/%e'),
|
date_format(least(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 27 day), last_day(current_date)), '%c/%e'),
|
date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 21 day),
|
least(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 27 day), last_day(current_date))
|
union all
|
select 4,
|
date_format(date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 28 day), '%c/%e'),
|
date_format(last_day(current_date), '%c/%e'),
|
date_add(date_sub(current_date, interval dayofmonth(current_date) - 1 day), interval 28 day),
|
last_day(current_date)
|
) weeks
|
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
|
""")
|
List<Map<String, Object>> selectMonthlyVerificationTrend();
|
|
@Select("""
|
select concat(months.bucket_month, '月') as time,
|
coalesce(count(v.id), 0) as `order`,
|
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 (
|
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
|
""")
|
List<Map<String, Object>> selectYearlyVerificationTrend();
|
|
@Select("""
|
select '体检预约' as label, coalesce(sum(case when application_type = 'MEDICAL_RESERVATION' then 1 else 0 end), 0) as value, '#2f91ff' as color
|
from service_application
|
where deleted = 0 and status in ('待处理', '待审核')
|
union all
|
select '就医申请' as label, coalesce(sum(case when application_type = 'FAST_TRACK' then 1 else 0 end), 0) as value, '#20c6ee' as color
|
from service_application
|
where deleted = 0 and status in ('待处理', '待审核')
|
union all
|
select '停车车牌' as label, count(*) as value, '#35bfc7' as color
|
from transport_parking_plate_registration
|
where deleted = 0 and status in ('SUBMITTED', 'PENDING', '待处理', '待审核')
|
union all
|
select '酒店预约' as label, coalesce(sum(case when application_type = 'HOTEL_RESERVATION' then 1 else 0 end), 0) as value, '#7cb5ff' as color
|
from service_application
|
where deleted = 0 and status in ('待处理', '待审核')
|
union all
|
select '家属卡申请' as label, count(*) as value, '#9b7cff' as color
|
from dependent_card_apply
|
where deleted = 0 and status = 0
|
union all
|
select '配偶就业申请' as label, coalesce(sum(case when application_type = 'SPOUSE_EMPLOYMENT' then 1 else 0 end), 0) as value, '#ff9f5f' as color
|
from service_application
|
where deleted = 0 and status in ('待处理', '待审核')
|
""")
|
List<Map<String, Object>> selectPendingItems();
|
|
@Select("""
|
select coalesce(nullif(m.merchant_name, ''), '未知商家') as merchantName,
|
count(v.id) as verificationCount,
|
round(coalesce(sum(v.verify_amount), 0) / 100, 2) as verificationAmount,
|
coalesce(v.merchant_id, o.merchant_id) as merchantGroupId,
|
coalesce(m.id, 0) as merchantSortId
|
from verify_record v
|
join benefit_order o on o.id = v.order_id and o.deleted = 0
|
left join merchant m on m.id = coalesce(v.merchant_id, o.merchant_id) and m.deleted = 0
|
where v.status = 1
|
group by coalesce(v.merchant_id, o.merchant_id), coalesce(nullif(m.merchant_name, ''), '未知商家'), m.id
|
order by verificationCount desc, verificationAmount desc, merchantSortId desc
|
limit 50
|
""")
|
List<Map<String, Object>> selectHotMerchants();
|
|
@Select("""
|
select category.label,
|
case when total.total_count = 0 then 0 else round(category.category_count * 100 / total.total_count, 2) end as value,
|
category.color
|
from (
|
select '到店消费' as label,
|
'#2f91ff' as color,
|
coalesce(sum(case when o.order_type in ('STORE', 'STORE_CONSUME', '到店消费', '消费') then 1 else 0 end), 0) as category_count
|
from benefit_order o
|
where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
|
union all
|
select '团购' as label,
|
'#20c6ee' as color,
|
coalesce(sum(case when o.order_type in ('GROUP', 'GROUP_BUY', '团购', '团购订单') then 1 else 0 end), 0) as category_count
|
from benefit_order o
|
where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
|
) category
|
cross join (
|
select coalesce(sum(total_source.category_count), 0) as total_count
|
from (
|
select coalesce(sum(case when o.order_type in ('STORE', 'STORE_CONSUME', '到店消费', '消费') then 1 else 0 end), 0) as category_count
|
from benefit_order o
|
where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
|
union all
|
select coalesce(sum(case when o.order_type in ('GROUP', 'GROUP_BUY', '团购', '团购订单') then 1 else 0 end), 0) as category_count
|
from benefit_order o
|
where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
|
) total_source
|
) total
|
order by field(category.label, '到店消费', '团购')
|
""")
|
List<Map<String, Object>> selectOrderCategoryShares();
|
}
|
