java/talent-card.git

			@@ -185,6 +185,28 @@
			<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
			@@ -234,6 +256,24 @@
			<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
			@@ -244,6 +284,8 @@
			public String selectSettlementExportDetails() {
			return sql("""
			<script>
			select details.*
			from (
			select o.id as orderId,
			o.order_no as orderNo,
			o.order_type as orderType,
			@@ -268,7 +310,8 @@
			o.created_at as createdAt,
			o.updated_at as paidAt,
			v.verify_time as verifyTime,
			v.verify_operator as verifyOperator
			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
			@@ -288,7 +331,48 @@
			<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
			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>
			""");
			@@ -298,10 +382,13 @@
			return sql("""
			select
			coalesce((
			-- 只算已核销的实付金额：未核销的钱还扣在通道的延迟户里，核销后才分账给商户；
			-- 退款中的订单在通道结果收敛前仍是 order_status = 1，只有「已核销」能立刻挡住它。
			select sum(o.pay_amount)
			from benefit_order o
			where o.deleted = 0
			and o.order_status = 1
			and o.verify_status = 1
			and o.merchant_id = #{merchantId}
			), 0) as transactionAmountCents,
			coalesce((
			@@ -329,12 +416,24 @@
			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) {
			return template.replace("/AMOUNT/", GovernmentSubsidySql.AMOUNT_CENTS)
			String amount = template.contains("<script>")
			? GovernmentSubsidySql.AMOUNT_CENTS.replace("<", "<")
			: GovernmentSubsidySql.AMOUNT_CENTS;
			return template.replace("/AMOUNT/", amount)
			.replace("/ORDER_TIME/", GovernmentSubsidySql.ORDER_TIME);
			}
			}