java/talent-card.git

			@@ -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("""