java/talent-card.git

			@@ -9,74 +9,12 @@
			import org.apache.ibatis.annotations.Mapper;
			import org.apache.ibatis.annotations.Param;
			import org.apache.ibatis.annotations.Select;
			import org.apache.ibatis.annotations.SelectProvider;

			@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_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>
			) 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>
			""")
			@SelectProvider(type = StatisticsQuerySql.class, method = "selectPeriodSummaries")
			List<Map<String, Object>> selectPeriodSummaries(@Param("merchantId") Long merchantId,
			@Param("merchantType") String merchantType,
			@Param("periodType") String periodType,
			@@ -119,200 +57,21 @@
			@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_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>
			) base
			group by base.merchant_id
			order by settlementAmountCents desc, base.merchant_id desc
			</script>
			""")
			@SelectProvider(type = StatisticsQuerySql.class, method = "selectMerchantOverviews")
			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>
			""")

			@SelectProvider(type = StatisticsQuerySql.class, method = "selectSettlementExportDetails")
			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
			""")

			@SelectProvider(type = StatisticsQuerySql.class, method = "selectDashboardCards")
			Map<String, Object> selectDashboardCards(@Param("merchantId") Long merchantId,
			@Param("todayStart") LocalDateTime todayStart,
			@Param("todayEnd") LocalDateTime todayEnd,