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.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
|
+ case when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
|
then o.coupon_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 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 (0, 1)) as pendingApplicationCount
|
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.verify_status = 1
|
""")
|
Map<String, Object> selectSummary();
|
|
@Select("""
|
select count(*) from benefit_order o
|
where o.deleted = 0 and o.order_status = 1 and o.verify_status = 1
|
and o.created_at >= date_format(current_date, '%Y-%m-01')
|
and o.created_at < date_add(last_day(current_date), interval 1 day)
|
and (o.subsidy_amount > 0 or o.coupon_amount > 0)
|
""")
|
Long countMonthlySubsidyOrders();
|
}
|
