package com.talentcard.server.mapper;
|
|
/** MyBatis SQL provider：政府补贴表达式在运行期拼接，兼容 MySQL 5.7。 */
|
public class StatisticsQuerySql {
|
public String selectSummary() {
|
return sql("""
|
select
|
coalesce(sum(/*AMOUNT*/), 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
|
left join merchant m on m.id = o.merchant_id and m.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
|
""");
|
}
|
|
public String selectCompareSummary() {
|
return sql("""
|
select
|
coalesce((select count(*) from talent_user u
|
where u.deleted = 0
|
and u.created_at >= current_date
|
and u.created_at < date_add(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(/*AMOUNT*/), 0) as subsidyCompareCents
|
from benefit_order o
|
left join merchant_product p on p.id = o.product_id and p.deleted = 0
|
left join merchant m on m.id = o.merchant_id and m.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 /*ORDER_TIME*/ >= current_date
|
and /*ORDER_TIME*/ < date_add(current_date, interval 1 day)
|
""");
|
}
|
|
public String selectSummaryTrend() {
|
return sql("""
|
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(/*AMOUNT*/)
|
from benefit_order o
|
left join merchant_product p on p.id = o.product_id and p.deleted = 0
|
left join merchant m on m.id = o.merchant_id and m.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 /*ORDER_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
|
""");
|
}
|
|
public String selectPeriodSummaries() {
|
return sql("""
|
<script>
|
select base.period_key as periodKey,
|
base.merchant_id as merchantId,
|
base.merchant_name as merchantName,
|
base.merchant_type as merchantType,
|
count(*) as orderCount,
|
coalesce(sum(base.pay_amount), 0) as transactionAmountCents,
|
coalesce(sum(base.government_subsidy_amount), 0) as subsidyAmountCents,
|
coalesce(sum(base.pay_amount + base.government_subsidy_amount), 0) as settlementAmountCents,
|
coalesce(sum(base.order_amount), 0) as originalAmountCents,
|
coalesce(sum(base.coupon_amount), 0) as couponAmountCents,
|
coalesce(sum(case when base.government_subsidy_amount > 0 then 1 else 0 end), 0) as governmentSubsidyOrderCount,
|
min(base.business_time) as periodStartTime,
|
max(base.business_time) as periodEndTime
|
from (
|
select case
|
when #{periodType} = 'MONTH' then date_format(coalesce(v.verify_time, o.updated_at, o.created_at), '%Y-%m')
|
else date_format(coalesce(v.verify_time, o.updated_at, o.created_at), '%Y-%m-%d')
|
end as period_key,
|
coalesce(v.verify_time, o.updated_at, o.created_at) as business_time,
|
o.merchant_id,
|
m.merchant_name,
|
m.merchant_type,
|
coalesce(o.order_amount, 0) as order_amount,
|
coalesce(o.pay_amount, 0) as pay_amount,
|
coalesce(o.coupon_amount, 0) as coupon_amount,
|
/*AMOUNT*/ as government_subsidy_amount
|
from benefit_order o
|
join merchant m on m.id = o.merchant_id and m.deleted = 0
|
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
|
left join (
|
select order_id, max(verify_time) as verify_time
|
from verify_record
|
where status = 1
|
group by order_id
|
) v on v.order_id = o.id
|
where o.deleted = 0
|
and o.order_status = 1
|
and o.verify_status = 1
|
<if test="merchantId != null">and o.merchant_id = #{merchantId}</if>
|
<if test="merchantType != null and merchantType != ''">and m.merchant_type_code = #{merchantType}</if>
|
<if test="startTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) >= #{startTime}</if>
|
<if test="endTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) < #{endTime}</if>
|
union all
|
select case
|
when #{periodType} = 'MONTH' then date_format(r.verify_time, '%Y-%m')
|
else date_format(r.verify_time, '%Y-%m-%d')
|
end as period_key,
|
r.verify_time as business_time,
|
r.merchant_id,
|
m.merchant_name,
|
m.merchant_type,
|
0 as order_amount,
|
0 as pay_amount,
|
0 as coupon_amount,
|
coalesce(r.subsidy_amount, 0) as government_subsidy_amount
|
from verify_record r
|
join merchant m on m.id = r.merchant_id and m.deleted = 0
|
where r.status = 1
|
and r.order_id is null
|
and upper(r.verify_type) = 'HOTEL_FREE'
|
<if test="merchantId != null">and r.merchant_id = #{merchantId}</if>
|
<if test="merchantType != null and merchantType != ''">and m.merchant_type_code = #{merchantType}</if>
|
<if test="startTime != null">and r.verify_time >= #{startTime}</if>
|
<if test="endTime != null">and r.verify_time < #{endTime}</if>
|
) base
|
group by base.period_key, base.merchant_id, base.merchant_name, base.merchant_type
|
order by base.period_key desc, base.merchant_id desc
|
limit #{size} offset #{offset}
|
</script>
|
""");
|
}
|
|
public String selectMerchantOverviews() {
|
return sql("""
|
<script>
|
select base.merchant_id as merchantId,
|
max(base.merchant_name) as merchantName,
|
max(base.merchant_type) as merchantType,
|
count(*) as orderCount,
|
coalesce(sum(base.pay_amount), 0) as transactionAmountCents,
|
coalesce(sum(base.government_subsidy_amount), 0) as subsidyAmountCents,
|
coalesce(sum(base.pay_amount + base.government_subsidy_amount), 0) as settlementAmountCents,
|
coalesce(sum(base.order_amount), 0) as originalAmountCents,
|
coalesce(sum(base.coupon_amount), 0) as couponAmountCents,
|
coalesce(sum(case when base.government_subsidy_amount > 0 then 1 else 0 end), 0) as governmentSubsidyOrderCount,
|
min(base.business_time) as startTime,
|
max(base.business_time) as endTime
|
from (
|
select coalesce(v.verify_time, o.updated_at, o.created_at) as business_time,
|
o.merchant_id,
|
m.merchant_name,
|
m.merchant_type,
|
coalesce(o.order_amount, 0) as order_amount,
|
coalesce(o.pay_amount, 0) as pay_amount,
|
coalesce(o.coupon_amount, 0) as coupon_amount,
|
/*AMOUNT*/ as government_subsidy_amount
|
from benefit_order o
|
join merchant m on m.id = o.merchant_id and m.deleted = 0
|
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
|
left join (
|
select order_id, max(verify_time) as verify_time
|
from verify_record
|
where status = 1
|
group by order_id
|
) v on v.order_id = o.id
|
where o.deleted = 0
|
and o.order_status = 1
|
and o.verify_status = 1
|
<if test="merchantId != null">and o.merchant_id = #{merchantId}</if>
|
<if test="merchantType != null and merchantType != ''">and m.merchant_type_code = #{merchantType}</if>
|
<if test="startTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) >= #{startTime}</if>
|
<if test="endTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) < #{endTime}</if>
|
union all
|
select r.verify_time as business_time,
|
r.merchant_id,
|
m.merchant_name,
|
m.merchant_type,
|
0 as order_amount,
|
0 as pay_amount,
|
0 as coupon_amount,
|
coalesce(r.subsidy_amount, 0) as government_subsidy_amount
|
from verify_record r
|
join merchant m on m.id = r.merchant_id and m.deleted = 0
|
where r.status = 1
|
and r.order_id is null
|
and upper(r.verify_type) = 'HOTEL_FREE'
|
<if test="merchantId != null">and r.merchant_id = #{merchantId}</if>
|
<if test="merchantType != null and merchantType != ''">and m.merchant_type_code = #{merchantType}</if>
|
<if test="startTime != null">and r.verify_time >= #{startTime}</if>
|
<if test="endTime != null">and r.verify_time < #{endTime}</if>
|
) base
|
group by base.merchant_id
|
order by settlementAmountCents desc, base.merchant_id desc
|
</script>
|
""");
|
}
|
|
public String selectSettlementExportDetails() {
|
return sql("""
|
<script>
|
select details.*
|
from (
|
select o.id as orderId,
|
o.order_no as orderNo,
|
o.order_type as orderType,
|
o.merchant_id as merchantId,
|
m.merchant_name as merchantName,
|
m.merchant_type as merchantType,
|
o.store_id as storeId,
|
s.store_name as storeName,
|
o.talent_id as talentId,
|
t.name as talentName,
|
t.mobile as talentMobile,
|
tc.card_no as cardNo,
|
tc.card_level as cardLevel,
|
coalesce(o.product_name_snapshot, p.product_name, '') as productName,
|
coalesce(o.order_amount, 0) as originalAmountCents,
|
coalesce(o.pay_amount, 0) as transactionAmountCents,
|
/*AMOUNT*/ as subsidyAmountCents,
|
coalesce(o.pay_amount, 0) + /*AMOUNT*/ as settlementAmountCents,
|
coalesce(o.coupon_amount, 0) as couponAmountCents,
|
o.order_status as orderStatus,
|
o.verify_status as verifyStatus,
|
o.created_at as createdAt,
|
o.updated_at as paidAt,
|
v.verify_time as verifyTime,
|
v.verify_operator as verifyOperator,
|
coalesce(v.verify_time, o.updated_at, o.created_at) as businessTime
|
from benefit_order o
|
join merchant m on m.id = o.merchant_id and m.deleted = 0
|
left join merchant_store s on s.id = o.store_id and s.deleted = 0
|
left join talent_user t on t.id = o.talent_id and t.deleted = 0
|
left join talent_card tc on tc.id = o.card_id and tc.deleted = 0
|
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
|
left join (
|
select order_id, max(verify_time) as verify_time, max(staff_id) as verify_operator
|
from verify_record
|
where status = 1
|
group by order_id
|
) v on v.order_id = o.id
|
where o.deleted = 0
|
and o.merchant_id = #{merchantId}
|
<if test="orderStatus != null">and o.order_status = #{orderStatus}</if>
|
<if test="verifyStatus != null">and o.verify_status = #{verifyStatus}</if>
|
<if test="startTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) >= #{startTime}</if>
|
<if test="endTime != null">and coalesce(v.verify_time, o.updated_at, o.created_at) < #{endTime}</if>
|
union all
|
select r.id as orderId,
|
r.verify_no as orderNo,
|
'HOTEL_FREE' as orderType,
|
r.merchant_id as merchantId,
|
m.merchant_name as merchantName,
|
m.merchant_type as merchantType,
|
r.store_id as storeId,
|
s.store_name as storeName,
|
r.talent_id as talentId,
|
t.name as talentName,
|
t.mobile as talentMobile,
|
tc.card_no as cardNo,
|
tc.card_level as cardLevel,
|
'酒店免费入住券' as productName,
|
0 as originalAmountCents,
|
0 as transactionAmountCents,
|
coalesce(r.subsidy_amount, 0) as subsidyAmountCents,
|
coalesce(r.subsidy_amount, 0) as settlementAmountCents,
|
0 as couponAmountCents,
|
1 as orderStatus,
|
1 as verifyStatus,
|
r.verify_time as createdAt,
|
null as paidAt,
|
r.verify_time as verifyTime,
|
r.staff_id as verifyOperator,
|
r.verify_time as businessTime
|
from verify_record r
|
join merchant m on m.id = r.merchant_id and m.deleted = 0
|
left join merchant_store s on s.id = r.store_id and s.deleted = 0
|
left join talent_user t on t.id = r.talent_id and t.deleted = 0
|
left join talent_card tc on tc.id = r.card_id and tc.deleted = 0
|
where r.status = 1
|
and r.order_id is null
|
and upper(r.verify_type) = 'HOTEL_FREE'
|
and r.merchant_id = #{merchantId}
|
<if test="orderStatus != null">and #{orderStatus} = 1</if>
|
<if test="verifyStatus != null">and #{verifyStatus} = 1</if>
|
<if test="startTime != null">and r.verify_time >= #{startTime}</if>
|
<if test="endTime != null">and r.verify_time < #{endTime}</if>
|
) details
|
order by details.businessTime desc, details.orderId desc
|
limit #{limit}
|
</script>
|
""");
|
}
|
|
public String selectDashboardCards() {
|
return sql("""
|
select
|
coalesce((
|
select sum(o.pay_amount)
|
from benefit_order o
|
where o.deleted = 0
|
and o.order_status = 1
|
and o.merchant_id = #{merchantId}
|
), 0) as transactionAmountCents,
|
coalesce((
|
select count(*)
|
from verify_record r
|
where r.status = 1
|
and r.merchant_id = #{merchantId}
|
and r.verify_time >= #{todayStart}
|
and r.verify_time < #{todayEnd}
|
), 0) as todayVerificationCount,
|
coalesce((
|
select count(*)
|
from verify_record r
|
where r.status = 1
|
and r.merchant_id = #{merchantId}
|
and r.verify_time >= #{weekStart}
|
and r.verify_time < #{weekEnd}
|
), 0) as weekVerificationCount,
|
coalesce((
|
select sum(/*AMOUNT*/)
|
from benefit_order o
|
left join merchant_product p on p.id = o.product_id and p.deleted = 0
|
left join merchant m on m.id = o.merchant_id and m.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.merchant_id = #{merchantId}
|
), 0)
|
+
|
coalesce((
|
select sum(r.subsidy_amount)
|
from verify_record r
|
where r.status = 1
|
and r.order_id is null
|
and upper(r.verify_type) = 'HOTEL_FREE'
|
and r.merchant_id = #{merchantId}
|
), 0) as subsidyAmountCents
|
""");
|
}
|
|
private static String sql(String template) {
|
String amount = template.contains("<script>")
|
? GovernmentSubsidySql.AMOUNT_CENTS.replace("<", "<")
|
: GovernmentSubsidySql.AMOUNT_CENTS;
|
return template.replace("/*AMOUNT*/", amount)
|
.replace("/*ORDER_TIME*/", GovernmentSubsidySql.ORDER_TIME);
|
}
|
}
|
