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,102 +13,19 @@
			@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 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 ('待处理', '待审核')) 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 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(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 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,
			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("""
			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 +34,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 +55,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 +65,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 +85,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 +117,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 +138,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
			""")