package com.talentcard.server.mapper;
|
|
import java.time.LocalDateTime;
|
import java.util.List;
|
import java.util.Map;
|
|
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
|
import com.talentcard.server.entity.BenefitOrder;
|
import org.apache.ibatis.annotations.Mapper;
|
import org.apache.ibatis.annotations.Param;
|
import org.apache.ibatis.annotations.Select;
|
|
@Mapper
|
public interface MerchantSettlementStatisticsMapper extends BaseMapper<BenefitOrder> {
|
|
@Select("""
|
<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,
|
(
|
case
|
when coalesce(o.subsidy_amount, 0) > 0
|
and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
|
then coalesce(o.subsidy_amount, 0)
|
else 0
|
end
|
+
|
case
|
when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
|
then coalesce(o.coupon_amount, 0)
|
else 0
|
end
|
) 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 = #{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>
|
) 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>
|
""")
|
List<Map<String, Object>> selectPeriodSummaries(@Param("merchantId") Long merchantId,
|
@Param("merchantType") String merchantType,
|
@Param("periodType") String periodType,
|
@Param("startTime") LocalDateTime startTime,
|
@Param("endTime") LocalDateTime endTime,
|
@Param("size") int size,
|
@Param("offset") int offset);
|
|
@Select("""
|
<script>
|
select count(*)
|
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,
|
o.merchant_id
|
from benefit_order o
|
join merchant m on m.id = o.merchant_id and m.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 = #{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>
|
group by period_key, o.merchant_id
|
) grouped
|
</script>
|
""")
|
Long countPeriodSummaries(@Param("merchantId") Long merchantId,
|
@Param("merchantType") String merchantType,
|
@Param("periodType") String periodType,
|
@Param("startTime") LocalDateTime startTime,
|
@Param("endTime") LocalDateTime endTime);
|
|
@Select("""
|
<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,
|
(
|
case
|
when coalesce(o.subsidy_amount, 0) > 0
|
and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
|
then coalesce(o.subsidy_amount, 0)
|
else 0
|
end
|
+
|
case
|
when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
|
then coalesce(o.coupon_amount, 0)
|
else 0
|
end
|
) 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 = #{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>
|
) base
|
group by base.merchant_id
|
order by settlementAmountCents desc, base.merchant_id desc
|
</script>
|
""")
|
List<Map<String, Object>> selectMerchantOverviews(@Param("merchantId") Long merchantId,
|
@Param("merchantType") String merchantType,
|
@Param("startTime") LocalDateTime startTime,
|
@Param("endTime") LocalDateTime endTime);
|
@Select("""
|
<script>
|
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,
|
(
|
case
|
when coalesce(o.subsidy_amount, 0) > 0
|
and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
|
then coalesce(o.subsidy_amount, 0)
|
else 0
|
end
|
+
|
case
|
when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
|
then coalesce(o.coupon_amount, 0)
|
else 0
|
end
|
) as subsidyAmountCents,
|
coalesce(o.pay_amount, 0)
|
+
|
(
|
case
|
when coalesce(o.subsidy_amount, 0) > 0
|
and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
|
then coalesce(o.subsidy_amount, 0)
|
else 0
|
end
|
+
|
case
|
when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
|
then coalesce(o.coupon_amount, 0)
|
else 0
|
end
|
) 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
|
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>
|
order by coalesce(v.verify_time, o.updated_at, o.created_at) desc, o.id desc
|
limit #{limit}
|
</script>
|
""")
|
List<Map<String, Object>> selectSettlementExportDetails(@Param("merchantId") Long merchantId,
|
@Param("orderStatus") Integer orderStatus,
|
@Param("verifyStatus") Integer verifyStatus,
|
@Param("startTime") LocalDateTime startTime,
|
@Param("endTime") LocalDateTime endTime,
|
@Param("limit") int limit);
|
@Select("""
|
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(
|
case
|
when coalesce(o.subsidy_amount, 0) > 0
|
and (o.product_id is null or coalesce(p.is_government_subsidy, 0) = 1)
|
then coalesce(o.subsidy_amount, 0)
|
else 0
|
end
|
+
|
case
|
when coalesce(o.coupon_amount, 0) > 0 and coalesce(c.government_subsidy, 0) = 1
|
then coalesce(o.coupon_amount, 0)
|
else 0
|
end
|
)
|
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.merchant_id = #{merchantId}
|
), 0) as subsidyAmountCents
|
""")
|
Map<String, Object> selectDashboardCards(@Param("merchantId") Long merchantId,
|
@Param("todayStart") LocalDateTime todayStart,
|
@Param("todayEnd") LocalDateTime todayEnd,
|
@Param("weekStart") LocalDateTime weekStart,
|
@Param("weekEnd") LocalDateTime weekEnd);
|
}
|
