ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/dto/ChargingRefundDto.java
@@ -18,4 +18,8 @@ private Boolean specialRefund; @ApiModelProperty(value = "可查看站点ID", hidden = true) private List<Integer> siteIds; @ApiModelProperty("退款开始时间 yyyy-MM-dd HH:mm:ss") private String refundTime1; @ApiModelProperty("退款结束时间 yyyy-MM-dd HH:mm:ss") private String refundTime2; } ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/mapper/TChargingOrderMapper.java
@@ -110,10 +110,69 @@ List<Map<String, Object>> enterpriseChargingPeriodCounts(@Param("orderIds") List<Long> orderIds); List<TChargingOrderRefund> getRefundList(@Param("pageInfo") PageInfo<TChargingOrderRefund> pageInfo, @Param("data") ChargingRefundDto chargingRefundDto); /** * 退款列表（分片下推版）。不带 IPage 参数，避免分页拦截器对 UNION 全量 COUNT。 * 每个 UNION 分支内联 ORDER BY refund_time DESC LIMIT branchLimit（=offset+pageSize），MyCat 可下推到各分片表， * 代理层只归并 3×branchLimit 行再按外层 LIMIT offset,pageSize 取页。 * * @param offset 跳过行数（pageCurr-1）×pageSize * @param limit 每页行数 pageSize * @param branchLimit 每个 UNION 分支的最大取数 offset+pageSize */ List<TChargingOrderRefund> getRefundListPage(@Param("data") ChargingRefundDto chargingRefundDto, @Param("offset") long offset, @Param("limit") int limit, @Param("branchLimit") int branchLimit); List<TChargingOrderRefund> getRefundListWithoutOrderJoin(@Param("pageInfo") PageInfo<TChargingOrderRefund> pageInfo, @Param("data") ChargingRefundDto chargingRefundDto); /** * 退款总数（分片下推版）。三分支分别 COUNT 再 SUM，每个 COUNT 可下推到各分片表。 */ long countRefundList(@Param("data") ChargingRefundDto chargingRefundDto); /** * 退款列表（筛选逆反查版）。serialNumber/siteIds 筛选时先按条件查订单 id（可路由/走索引）， * 再按各类型订单 id IN 过滤退款表（分片键路由），替代跨分片 LEFT JOIN。 * type1/type2/type3 订单 id 列表：null = 该类型不过滤；空列表 = 该类型无匹配（返回空）。 * * @param branchLimit 每个 UNION 分支的最大取数 offset+pageSize */ List<TChargingOrderRefund> getRefundListByOrderIds(@Param("data") ChargingRefundDto chargingRefundDto, @Param("type1OrderIds") List<Long> type1OrderIds, @Param("type2OrderIds") List<Long> type2OrderIds, @Param("type3OrderIds") List<Long> type3OrderIds, @Param("offset") long offset, @Param("limit") int limit, @Param("branchLimit") int branchLimit); /** * 退款总数（筛选逆反查版），与 getRefundListByOrderIds 同构。 */ long countRefundListByOrderIds(@Param("data") ChargingRefundDto chargingRefundDto, @Param("type1OrderIds") List<Long> type1OrderIds, @Param("type2OrderIds") List<Long> type2OrderIds, @Param("type3OrderIds") List<Long> type3OrderIds); /** * 按支付流水号模糊匹配充电订单 id（t_charging_order 按 app_user_id 分片，recharge_serial_number 走新增索引）。 */ List<Long> listChargingOrderIdsBySerialNumber(@Param("serialNumber") String serialNumber, @Param("limit") int limit); /** * 按支付流水号模糊匹配商城订单 id。 */ List<Long> listShoppingOrderIdsBySerialNumber(@Param("serialNumber") String serialNumber, @Param("limit") int limit); /** * 按支付流水号模糊匹配会员订单 id。 */ List<Long> listVipOrderIdsBySerialNumber(@Param("serialNumber") String serialNumber, @Param("limit") int limit); /** * 按站点 id 匹配充电订单 id（t_charging_order 的 site_id 走新增索引）。 */ List<Long> listChargingOrderIdsBySiteIds(@Param("siteIds") List<Integer> siteIds, @Param("limit") int limit); List<ChargingOrderGroup> chargingOrderGroup(@Param("chargingPercentProvinceDto") ChargingPercentProvinceDto chargingPercentProvinceDto);
@@ -3347,17 +3347,40 @@ .divide(BigDecimal.valueOf(3600), 2, RoundingMode.HALF_UP); } private static final int SERIAL_ORDER_ID_SEARCH_LIMIT = 5000; private static final int SITE_ORDER_ID_SEARCH_LIMIT = 50000; @Override public R<PageInfo<TChargingOrderRefund>> getRefundList(ChargingRefundDto chargingRefundDto) { PageInfo<TChargingOrderRefund> pageInfo = new PageInfo<>(chargingRefundDto.getPageCurr(), chargingRefundDto.getPageSize()); boolean withoutOrderJoin = !StringUtils.hasLength(chargingRefundDto.getSerialNumber()) && (chargingRefundDto.getSiteIds() == null || chargingRefundDto.getSiteIds().isEmpty()); List<TChargingOrderRefund> list = withoutOrderJoin ? this.baseMapper.getRefundListWithoutOrderJoin(pageInfo, chargingRefundDto) : this.baseMapper.getRefundList(pageInfo, chargingRefundDto); if (withoutOrderJoin) { enrichRefundOrderInfo(list); int pageCurr = chargingRefundDto.getPageCurr() == null ? 1 : chargingRefundDto.getPageCurr(); int pageSize = chargingRefundDto.getPageSize() == null ? 50 : chargingRefundDto.getPageSize(); PageInfo<TChargingOrderRefund> pageInfo = new PageInfo<>(pageCurr, pageSize); long offset = (long) (pageCurr - 1) * pageSize; // 每个 UNION 分支须取 offset+pageSize 行，外层再按 offset,pageSize 切页，否则翻页时池子不足导致丢数据 int branchLimit = (int) Math.min(offset + pageSize, Integer.MAX_VALUE); boolean filterBySerial = StringUtils.hasLength(chargingRefundDto.getSerialNumber()); boolean filterBySite = chargingRefundDto.getSiteIds() != null && !chargingRefundDto.getSiteIds().isEmpty(); List<TChargingOrderRefund> list; if (!filterBySerial && !filterBySite) { // 无筛选：手工分页。分页拦截器会对 UNION 查询生成全量 COUNT 且无法把 ORDER BY/LIMIT 下推到分片， // 这里改为分支内下推（getRefundListPage）加分支 COUNT 求和（countRefundList） pageInfo.setTotal(this.baseMapper.countRefundList(chargingRefundDto)); list = this.baseMapper.getRefundListPage(chargingRefundDto, offset, pageSize, branchLimit); } else { // 筛选：逆反查。先按条件查订单 id（t_charging_order 等可下推/走索引）， // 再按退款表分片键 IN 过滤（可路由），替代跨分片 LEFT JOIN RefundOrderIds ids = resolveRefundOrderIds(chargingRefundDto); if (ids.isEmpty()) { pageInfo.setRecords(Collections.emptyList()); return R.ok(pageInfo); } pageInfo.setTotal(this.baseMapper.countRefundListByOrderIds(chargingRefundDto, ids.type1OrderIds, ids.type2OrderIds, ids.type3OrderIds)); list = this.baseMapper.getRefundListByOrderIds(chargingRefundDto, ids.type1OrderIds, ids.type2OrderIds, ids.type3OrderIds, offset, pageSize, branchLimit); } enrichRefundOrderInfo(list); for (TChargingOrderRefund refund : list) { refund.setUid(refund.getType() + "-" + refund.getId()); } @@ -3366,6 +3389,75 @@ return R.ok(pageInfo); } /** * 逆反查订单 id：serialNumber 匹配充电/商城/会员订单 id（各自 LIMIT 防爆）， * siteIds 匹配充电订单 id；两者同时存在时取交集。 * 返回约定：null = 该类型不过滤；空列表 = 该类型无匹配（SQL 侧 AND 1=0）。 */ private RefundOrderIds resolveRefundOrderIds(ChargingRefundDto chargingRefundDto) { List<Long> type1OrderIds = null; List<Long> type2OrderIds = null; List<Long> type3OrderIds = null; if (StringUtils.hasLength(chargingRefundDto.getSerialNumber())) { // 模糊匹配结果可能很大（短流水号），截断上限防 IN 列表爆炸；匹配不到则列表为空 type1OrderIds = safeIds(this.baseMapper.listChargingOrderIdsBySerialNumber( chargingRefundDto.getSerialNumber(), SERIAL_ORDER_ID_SEARCH_LIMIT)); type2OrderIds = safeIds(this.baseMapper.listShoppingOrderIdsBySerialNumber( chargingRefundDto.getSerialNumber(), SERIAL_ORDER_ID_SEARCH_LIMIT)); type3OrderIds = safeIds(this.baseMapper.listVipOrderIdsBySerialNumber( chargingRefundDto.getSerialNumber(), SERIAL_ORDER_ID_SEARCH_LIMIT)); warnIfTruncated(chargingRefundDto.getSerialNumber(), type1OrderIds, SERIAL_ORDER_ID_SEARCH_LIMIT, "充电订单"); warnIfTruncated(chargingRefundDto.getSerialNumber(), type2OrderIds, SERIAL_ORDER_ID_SEARCH_LIMIT, "商城订单"); warnIfTruncated(chargingRefundDto.getSerialNumber(), type3OrderIds, SERIAL_ORDER_ID_SEARCH_LIMIT, "会员订单"); } if (chargingRefundDto.getSiteIds() != null && !chargingRefundDto.getSiteIds().isEmpty()) { List<Long> siteMatched = safeIds(this.baseMapper.listChargingOrderIdsBySiteIds( chargingRefundDto.getSiteIds(), SITE_ORDER_ID_SEARCH_LIMIT)); warnIfTruncated(String.valueOf(chargingRefundDto.getSiteIds()), siteMatched, SITE_ORDER_ID_SEARCH_LIMIT, "站点订单"); if (type1OrderIds == null) { type1OrderIds = siteMatched; } else { type1OrderIds.retainAll(new HashSet<>(siteMatched)); } } return new RefundOrderIds(type1OrderIds, type2OrderIds, type3OrderIds); } /** 反查结果达到上限说明可能被截断，告警便于排查（截断会导致部分退款不在列表内） */ private void warnIfTruncated(String condition, List<Long> ids, int limit, String orderType) { if (ids.size() >= limit) { log.warn("退款列表逆反查：条件[{}]匹配{}数达到上限{}，结果可能被截断", condition, orderType, limit); } } private static List<Long> safeIds(List<Long> ids) { return ids == null ? new ArrayList<>() : new ArrayList<>(ids); } /** 逆反查结果：null = 不过滤；空列表 = 无匹配 */ private static class RefundOrderIds { private final List<Long> type1OrderIds; private final List<Long> type2OrderIds; private final List<Long> type3OrderIds; private RefundOrderIds(List<Long> type1OrderIds, List<Long> type2OrderIds, List<Long> type3OrderIds) { this.type1OrderIds = type1OrderIds; this.type2OrderIds = type2OrderIds; this.type3OrderIds = type3OrderIds; } /** 三个类型全部明确无匹配（非 null 且为空）时，整页为空，可短路 */ private boolean isEmpty() { return type1OrderIds != null && type1OrderIds.isEmpty() && type2OrderIds != null && type2OrderIds.isEmpty() && type3OrderIds != null && type3OrderIds.isEmpty(); } } private void enrichRefundOrderInfo(List<TChargingOrderRefund> refunds) { if (refunds == null || refunds.isEmpty()) { return; ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml
@@ -294,115 +294,15 @@ ORDER BY COALESCE(o.pay_time, o.create_time, o.end_time) DESC, o.create_time DESC, o.id DESC </select> <select id="getRefundList" resultType="com.ruoyi.order.api.model.TChargingOrderRefund"> select * from ( SELECT re.id, re.`code`, re.refund_code, re.refund_title, re.refund_reason, re.refund_amount, re.balance_refund_amount, re.third_refund_amount, re.refund_total_amount, re.refund_time, re.pay_type, re.pay_amount, re.refund_remark, co.recharge_serial_number as serail_number, co.payment_amount, co.create_time as pay_time, co.id as order_id, co.app_user_id as user_id, co.site_id as site_id, co.pay_method as pay_method, 1 as type FROM t_charging_order_refund re LEFT JOIN t_charging_order co ON re.charging_order_id = co.id <if test="data.specialRefund != null and data.specialRefund"> WHERE re.refund_title = '特殊退款处理' </if> <if test="data.specialRefund == null or !data.specialRefund"> UNION ALL SELECT re.id, re.`code`, re.refund_code, re.refund_title, re.refund_reason, re.refund_amount, 0 as balance_refund_amount, re.refund_amount as third_refund_amount, re.refund_total_amount, re.refund_time, re.pay_type, re.pay_amount, re.refund_remark, so.serial_number, so.payment_amount, so.create_time as pay_time, so.id as order_id, so.app_user_id as user_id, null as site_id, 3 as pay_method, 2 as type FROM t_shopping_order_refund re LEFT JOIN t_shopping_order so on re.shopping_order_id = so.id UNION ALL SELECT vr.id, vr.`code`, vr.refund_code, vr.refund_title, vr.refund_reason, vr.refund_amount, 0 as balance_refund_amount, vr.refund_amount as third_refund_amount, vr.refund_total_amount, vr.refund_time, vr.pay_type, vr.pay_amount, vr.refund_remark, vo.serial_number, vo.payment_amount, vo.create_time as pay_time, vo.id as order_id, vo.app_user_id as user_id, null as site_id, 3 as pay_method, 3 as type FROM t_vip_order_refund vr LEFT JOIN t_vip_order vo on vr.vip_order_id = vo.id </if> ) o <where> <if test="data.code != null and data.code != ''"> AND o.code LIKE CONCAT('%',#{data.code},'%') </if> <if test="data.refundCode != null and data.refundCode != ''"> AND o.refund_code LIKE CONCAT('%',#{data.refundCode},'%') </if> <if test="data.serialNumber != null and data.serialNumber != ''"> AND o.serail_number LIKE CONCAT('%',#{data.serialNumber},'%') </if> <if test="data.specialRefund != null and data.specialRefund"> AND o.refund_title = '特殊退款处理' </if> <if test="data.siteIds != null and data.siteIds.size() > 0"> AND (o.type != 1 OR o.site_id IN <foreach collection="data.siteIds" item="siteId" open="(" separator="," close=")"> #{siteId} </foreach> ) </if> </where> order by o.refund_time desc </select> <select id="getRefundListWithoutOrderJoin" resultType="com.ruoyi.order.api.model.TChargingOrderRefund"> <!-- 退款列表（筛选逆反查版）。serialNumber/siteIds 筛选时，先由 Service 按条件查出订单 id 列表， 再按各类型订单 id IN 过滤退款表（IN 为退款表分片键，MyCat 可路由），替代跨分片 LEFT JOIN。 type1/type2/type3 订单 id：null = 不过滤；空列表 = 无匹配（AND 1=0）。 各分支同样内联 ORDER BY + LIMIT 下推。 --> <select id="getRefundListByOrderIds" resultType="com.ruoyi.order.api.model.TChargingOrderRefund"> SELECT * FROM ( SELECT (SELECT re.id, re.`code`, re.refund_code, @@ -429,10 +329,31 @@ <if test="data.refundCode != null and data.refundCode != ''"> AND re.refund_code LIKE CONCAT('%', #{data.refundCode}, '%') </if> <if test="data.refundTime1 != null and data.refundTime1 != ''"> AND re.refund_time >= #{data.refundTime1} </if> <if test="data.refundTime2 != null and data.refundTime2 != ''"> AND re.refund_time <= #{data.refundTime2} </if> <choose> <when test="type1OrderIds != null and type1OrderIds.size() > 0"> AND re.charging_order_id IN <foreach collection="type1OrderIds" item="id" open="(" separator="," close=")"> #{id} </foreach> </when> <otherwise> <if test="type1OrderIds != null"> AND 1 = 0 </if> </otherwise> </choose> </where> ORDER BY re.refund_time DESC LIMIT #{branchLimit}) <if test="data.specialRefund == null or !data.specialRefund"> UNION ALL SELECT (SELECT re.id, re.`code`, re.refund_code, @@ -456,9 +377,30 @@ <if test="data.refundCode != null and data.refundCode != ''"> AND re.refund_code LIKE CONCAT('%', #{data.refundCode}, '%') </if> <if test="data.refundTime1 != null and data.refundTime1 != ''"> AND re.refund_time >= #{data.refundTime1} </if> <if test="data.refundTime2 != null and data.refundTime2 != ''"> AND re.refund_time <= #{data.refundTime2} </if> <choose> <when test="type2OrderIds != null and type2OrderIds.size() > 0"> AND re.shopping_order_id IN <foreach collection="type2OrderIds" item="id" open="(" separator="," close=")"> #{id} </foreach> </when> <otherwise> <if test="type2OrderIds != null"> AND 1 = 0 </if> </otherwise> </choose> </where> ORDER BY re.refund_time DESC LIMIT #{branchLimit}) UNION ALL SELECT (SELECT re.id, re.`code`, re.refund_code, @@ -482,10 +424,348 @@ <if test="data.refundCode != null and data.refundCode != ''"> AND re.refund_code LIKE CONCAT('%', #{data.refundCode}, '%') </if> <if test="data.refundTime1 != null and data.refundTime1 != ''"> AND re.refund_time >= #{data.refundTime1} </if> <if test="data.refundTime2 != null and data.refundTime2 != ''"> AND re.refund_time <= #{data.refundTime2} </if> <choose> <when test="type3OrderIds != null and type3OrderIds.size() > 0"> AND re.vip_order_id IN <foreach collection="type3OrderIds" item="id" open="(" separator="," close=")"> #{id} </foreach> </when> <otherwise> <if test="type3OrderIds != null"> AND 1 = 0 </if> </otherwise> </choose> </where> ORDER BY re.refund_time DESC LIMIT #{branchLimit}) </if> ) o ORDER BY o.refund_time DESC LIMIT #{offset}, #{limit} </select> <!-- 退款总数（筛选逆反查版），与 getRefundListByOrderIds 同构，三分支 COUNT 可下推。 --> <select id="countRefundListByOrderIds" resultType="long"> SELECT SUM(cnt) FROM ( (SELECT COUNT(*) AS cnt FROM t_charging_order_refund re <where> <if test="data.specialRefund != null and data.specialRefund"> AND re.refund_title = '特殊退款处理' </if> <if test="data.code != null and data.code != ''"> AND re.code LIKE CONCAT('%', #{data.code}, '%') </if> <if test="data.refundCode != null and data.refundCode != ''"> AND re.refund_code LIKE CONCAT('%', #{data.refundCode}, '%') </if> <if test="data.refundTime1 != null and data.refundTime1 != ''"> AND re.refund_time >= #{data.refundTime1} </if> <if test="data.refundTime2 != null and data.refundTime2 != ''"> AND re.refund_time <= #{data.refundTime2} </if> <choose> <when test="type1OrderIds != null and type1OrderIds.size() > 0"> AND re.charging_order_id IN <foreach collection="type1OrderIds" item="id" open="(" separator="," close=")"> #{id} </foreach> </when> <otherwise> <if test="type1OrderIds != null"> AND 1 = 0 </if> </otherwise> </choose> </where>) <if test="data.specialRefund == null or !data.specialRefund"> UNION ALL (SELECT COUNT(*) AS cnt FROM t_shopping_order_refund re <where> <if test="data.code != null and data.code != ''"> AND re.code LIKE CONCAT('%', #{data.code}, '%') </if> <if test="data.refundCode != null and data.refundCode != ''"> AND re.refund_code LIKE CONCAT('%', #{data.refundCode}, '%') </if> <if test="data.refundTime1 != null and data.refundTime1 != ''"> AND re.refund_time >= #{data.refundTime1} </if> <if test="data.refundTime2 != null and data.refundTime2 != ''"> AND re.refund_time <= #{data.refundTime2} </if> <choose> <when test="type2OrderIds != null and type2OrderIds.size() > 0"> AND re.shopping_order_id IN <foreach collection="type2OrderIds" item="id" open="(" separator="," close=")"> #{id} </foreach> </when> <otherwise> <if test="type2OrderIds != null"> AND 1 = 0 </if> </otherwise> </choose> </where>) UNION ALL (SELECT COUNT(*) AS cnt FROM t_vip_order_refund re <where> <if test="data.code != null and data.code != ''"> AND re.code LIKE CONCAT('%', #{data.code}, '%') </if> <if test="data.refundCode != null and data.refundCode != ''"> AND re.refund_code LIKE CONCAT('%', #{data.refundCode}, '%') </if> <if test="data.refundTime1 != null and data.refundTime1 != ''"> AND re.refund_time >= #{data.refundTime1} </if> <if test="data.refundTime2 != null and data.refundTime2 != ''"> AND re.refund_time <= #{data.refundTime2} </if> <choose> <when test="type3OrderIds != null and type3OrderIds.size() > 0"> AND re.vip_order_id IN <foreach collection="type3OrderIds" item="id" open="(" separator="," close=")"> #{id} </foreach> </when> <otherwise> <if test="type3OrderIds != null"> AND 1 = 0 </if> </otherwise> </choose> </where>) </if> ) t </select> <!-- 按支付流水号模糊匹配充电订单 id（逆反查第一步）。 注意：前导通配符 LIKE '%x%' 无法走 recharge_serial_number 索引，MyCat 广播 50 张分片表全表扫描； 匹配行一般极少（流水号近似唯一），结果集小，可接受。ORDER BY id 保证截断时结果确定性。 --> <select id="listChargingOrderIdsBySerialNumber" resultType="long"> SELECT id FROM t_charging_order WHERE recharge_serial_number LIKE CONCAT('%', #{serialNumber}, '%') ORDER BY id DESC LIMIT #{limit} </select> <!-- 按支付流水号模糊匹配商城订单 id（同前导通配符说明，广播 20 张分片表） --> <select id="listShoppingOrderIdsBySerialNumber" resultType="long"> SELECT id FROM t_shopping_order WHERE serial_number LIKE CONCAT('%', #{serialNumber}, '%') ORDER BY id DESC LIMIT #{limit} </select> <!-- 按支付流水号模糊匹配会员订单 id（同前导通配符说明，广播 20 张分片表） --> <select id="listVipOrderIdsBySerialNumber" resultType="long"> SELECT id FROM t_vip_order WHERE serial_number LIKE CONCAT('%', #{serialNumber}, '%') ORDER BY id DESC LIMIT #{limit} </select> <!-- 按站点匹配充电订单 id（逆反查第一步，site_id 走新增索引；ORDER BY id 保证截断时结果确定性） --> <select id="listChargingOrderIdsBySiteIds" resultType="long"> SELECT id FROM t_charging_order WHERE site_id IN <foreach collection="siteIds" item="siteId" open="(" separator="," close=")"> #{siteId} </foreach> ORDER BY id DESC LIMIT #{limit} </select> <!-- 退款列表（分片下推版）。 三个 UNION 分支各自单表查询，分支内 ORDER BY + LIMIT 可被 MyCat 下推到每张分片表（每表最多取 offset+limit 行）， 代理层只归并三分支共 3×(offset+limit) 行再取页。相比旧版（90 张分片表全量拉回代理层排序）避免全量传输。 --> <select id="getRefundListPage" resultType="com.ruoyi.order.api.model.TChargingOrderRefund"> SELECT * FROM ( (SELECT re.id, re.`code`, re.refund_code, re.refund_title, re.refund_reason, re.refund_amount, re.balance_refund_amount, re.third_refund_amount, re.refund_total_amount, re.refund_time, re.pay_type, re.pay_amount, re.refund_remark, re.charging_order_id AS order_id, 1 AS type FROM t_charging_order_refund re <where> <if test="data.specialRefund != null and data.specialRefund"> AND re.refund_title = '特殊退款处理' </if> <if test="data.code != null and data.code != ''"> AND re.code LIKE CONCAT('%', #{data.code}, '%') </if> <if test="data.refundCode != null and data.refundCode != ''"> AND re.refund_code LIKE CONCAT('%', #{data.refundCode}, '%') </if> <if test="data.refundTime1 != null and data.refundTime1 != ''"> AND re.refund_time >= #{data.refundTime1} </if> <if test="data.refundTime2 != null and data.refundTime2 != ''"> AND re.refund_time <= #{data.refundTime2} </if> </where> ORDER BY re.refund_time DESC LIMIT #{branchLimit}) <if test="data.specialRefund == null or !data.specialRefund"> UNION ALL (SELECT re.id, re.`code`, re.refund_code, re.refund_title, re.refund_reason, re.refund_amount, 0 AS balance_refund_amount, re.refund_amount AS third_refund_amount, re.refund_total_amount, re.refund_time, re.pay_type, re.pay_amount, re.refund_remark, re.shopping_order_id AS order_id, 2 AS type FROM t_shopping_order_refund re <where> <if test="data.code != null and data.code != ''"> AND re.code LIKE CONCAT('%', #{data.code}, '%') </if> <if test="data.refundCode != null and data.refundCode != ''"> AND re.refund_code LIKE CONCAT('%', #{data.refundCode}, '%') </if> <if test="data.refundTime1 != null and data.refundTime1 != ''"> AND re.refund_time >= #{data.refundTime1} </if> <if test="data.refundTime2 != null and data.refundTime2 != ''"> AND re.refund_time <= #{data.refundTime2} </if> </where> ORDER BY re.refund_time DESC LIMIT #{branchLimit}) UNION ALL (SELECT re.id, re.`code`, re.refund_code, re.refund_title, re.refund_reason, re.refund_amount, 0 AS balance_refund_amount, re.refund_amount AS third_refund_amount, re.refund_total_amount, re.refund_time, re.pay_type, re.pay_amount, re.refund_remark, re.vip_order_id AS order_id, 3 AS type FROM t_vip_order_refund re <where> <if test="data.code != null and data.code != ''"> AND re.code LIKE CONCAT('%', #{data.code}, '%') </if> <if test="data.refundCode != null and data.refundCode != ''"> AND re.refund_code LIKE CONCAT('%', #{data.refundCode}, '%') </if> <if test="data.refundTime1 != null and data.refundTime1 != ''"> AND re.refund_time >= #{data.refundTime1} </if> <if test="data.refundTime2 != null and data.refundTime2 != ''"> AND re.refund_time <= #{data.refundTime2} </if> </where> ORDER BY re.refund_time DESC LIMIT #{branchLimit}) </if> ) o ORDER BY o.refund_time DESC LIMIT #{offset}, #{limit} </select> <!-- 退款总数（分片下推版）。三分支各自 COUNT（可下推到每张分片表），代理层只 SUM 三个数。 旧版由分页拦截器生成 SELECT COUNT(*) FROM (90 表全量 UNION)，MyCat 无法下推导致全量扫描。 --> <select id="countRefundList" resultType="long"> SELECT SUM(cnt) FROM ( (SELECT COUNT(*) AS cnt FROM t_charging_order_refund re <where> <if test="data.specialRefund != null and data.specialRefund"> AND re.refund_title = '特殊退款处理' </if> <if test="data.code != null and data.code != ''"> AND re.code LIKE CONCAT('%', #{data.code}, '%') </if> <if test="data.refundCode != null and data.refundCode != ''"> AND re.refund_code LIKE CONCAT('%', #{data.refundCode}, '%') </if> <if test="data.refundTime1 != null and data.refundTime1 != ''"> AND re.refund_time >= #{data.refundTime1} </if> <if test="data.refundTime2 != null and data.refundTime2 != ''"> AND re.refund_time <= #{data.refundTime2} </if> </where>) <if test="data.specialRefund == null or !data.specialRefund"> UNION ALL (SELECT COUNT(*) AS cnt FROM t_shopping_order_refund re <where> <if test="data.code != null and data.code != ''"> AND re.code LIKE CONCAT('%', #{data.code}, '%') </if> <if test="data.refundCode != null and data.refundCode != ''"> AND re.refund_code LIKE CONCAT('%', #{data.refundCode}, '%') </if> <if test="data.refundTime1 != null and data.refundTime1 != ''"> AND re.refund_time >= #{data.refundTime1} </if> <if test="data.refundTime2 != null and data.refundTime2 != ''"> AND re.refund_time <= #{data.refundTime2} </if> </where>) UNION ALL (SELECT COUNT(*) AS cnt FROM t_vip_order_refund re <where> <if test="data.code != null and data.code != ''"> AND re.code LIKE CONCAT('%', #{data.code}, '%') </if> <if test="data.refundCode != null and data.refundCode != ''"> AND re.refund_code LIKE CONCAT('%', #{data.refundCode}, '%') </if> <if test="data.refundTime1 != null and data.refundTime1 != ''"> AND re.refund_time >= #{data.refundTime1} </if> <if test="data.refundTime2 != null and data.refundTime2 != ''"> AND re.refund_time <= #{data.refundTime2} </if> </where>) </if> ) t </select> <select id="chargingOrderGroup" resultType="com.ruoyi.common.core.dto.ChargingOrderGroup"> SELECT SUM(tc.charging_capacity) as charging_capacity,tc.site_id ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/PayOrderQueryPerformanceMapperTest.java
@@ -16,20 +16,22 @@ class PayOrderQueryPerformanceMapperTest { @Test void readsRefundStatusFromOrderTablesWithoutJoiningRefundDetails() throws IOException { InputStream input = getClass().getClassLoader() private static String readMapper() throws IOException { InputStream input = PayOrderQueryPerformanceMapperTest.class.getClassLoader() .getResourceAsStream("mapper/order/TChargingOrderMapper.xml"); assertNotNull(input); String mapper; try (BufferedReader reader = new BufferedReader(new InputStreamReader(input, StandardCharsets.UTF_8))) { mapper = reader.lines().collect(Collectors.joining("\n")); return reader.lines().collect(Collectors.joining("\n")); } } @Test void readsRefundStatusFromOrderTablesWithoutJoiningRefundDetails() throws IOException { String mapper = readMapper(); String payOrderQuery = mapper.substring( mapper.indexOf("<select id=\"payOrderQuery\""), mapper.indexOf("<select id=\"getRefundList\"")); mapper.indexOf("<select id=\"getRefundListByOrderIds\"")); assertEquals(3, countOccurrences(payOrderQuery, "a.refund_status as refund_status")); assertFalse(payOrderQuery.contains("t_charging_order_refund")); assertFalse(payOrderQuery.contains("t_shopping_order_refund")); @@ -38,18 +40,11 @@ @Test void invoiceOrderViewRetainsLinkedOrdersAfterPaymentStatusChanges() throws IOException { InputStream input = getClass().getClassLoader() .getResourceAsStream("mapper/order/TChargingOrderMapper.xml"); assertNotNull(input); String mapper; try (BufferedReader reader = new BufferedReader(new InputStreamReader(input, StandardCharsets.UTF_8))) { mapper = reader.lines().collect(Collectors.joining("\n")); } String mapper = readMapper(); String payOrderQuery = mapper.substring( mapper.indexOf("<select id=\"payOrderQuery\""), mapper.indexOf("<select id=\"getRefundList\"")); mapper.indexOf("<select id=\"getRefundListByOrderIds\"")); assertEquals(3, countOccurrences(payOrderQuery, "#{data.invoiceId} IS NOT NULL")); assertTrue(payOrderQuery.contains( "a.recharge_payment_status = 2 OR a.status = -1 OR #{data.invoiceId} IS NOT NULL")); @@ -59,56 +54,54 @@ @Test void paymentOrderListUsesLatestPaymentTimeAsItsDefaultSort() throws IOException { InputStream input = getClass().getClassLoader() .getResourceAsStream("mapper/order/TChargingOrderMapper.xml"); assertNotNull(input); String mapper; try (BufferedReader reader = new BufferedReader(new InputStreamReader(input, StandardCharsets.UTF_8))) { mapper = reader.lines().collect(Collectors.joining("\n")); } String mapper = readMapper(); String payOrderQuery = mapper.substring( mapper.indexOf("<select id=\"payOrderQuery\""), mapper.indexOf("<select id=\"getRefundList\"")); mapper.indexOf("<select id=\"getRefundListByOrderIds\"")); assertTrue(payOrderQuery.contains( "ORDER BY COALESCE(o.pay_time, o.create_time, o.end_time) DESC, o.create_time DESC, o.id DESC")); } @Test void refundListAppliesPartnerSiteScopeInsidePagedQuery() throws IOException { InputStream input = getClass().getClassLoader() .getResourceAsStream("mapper/order/TChargingOrderMapper.xml"); assertNotNull(input); void refundListBranchesPushLimitIntoEachShard() throws IOException { String mapper = readMapper(); String mapper; try (BufferedReader reader = new BufferedReader(new InputStreamReader(input, StandardCharsets.UTF_8))) { mapper = reader.lines().collect(Collectors.joining("\n")); } String refundList = mapper.substring( mapper.indexOf("<select id=\"getRefundListPage\""), mapper.indexOf("<select id=\"countRefundList\"")); // 每个 UNION 分支必须内联 ORDER BY + LIMIT（branchLimit=offset+pageSize），外层只做归并切页 assertEquals(3, countOccurrences(refundList, "ORDER BY re.refund_time DESC")); assertEquals(3, countOccurrences(refundList, "LIMIT #{branchLimit})")); assertTrue(refundList.contains("LIMIT #{offset}, #{limit}")); } String refundQuery = mapper.substring( mapper.indexOf("<select id=\"getRefundList\""), mapper.indexOf("<select id=\"chargingOrderGroup\"")); assertTrue(refundQuery.contains("data.siteIds")); assertTrue(refundQuery.contains("o.site_id IN")); assertEquals(3, countOccurrences(refundQuery, "as site_id")); @Test void refundListAppliesPartnerSiteScopeViaReverseLookup() throws IOException { String mapper = readMapper(); // 合作商站点范围通过逆反查实现：先按 site_id 查订单 id，再按退款表分片键 IN 过滤 String siteLookup = mapper.substring( mapper.indexOf("<select id=\"listChargingOrderIdsBySiteIds\""), mapper.indexOf("<select id=\"getRefundListPage\"")); assertTrue(siteLookup.contains("site_id IN")); assertTrue(siteLookup.contains("ORDER BY id DESC")); String filteredList = mapper.substring( mapper.indexOf("<select id=\"getRefundListByOrderIds\""), mapper.indexOf("<select id=\"countRefundListByOrderIds\"")); assertTrue(filteredList.contains("type1OrderIds")); assertTrue(filteredList.contains("re.charging_order_id IN")); } @Test void specialRefundListSkipsUnrelatedOrderUnions() throws IOException { InputStream input = getClass().getClassLoader() .getResourceAsStream("mapper/order/TChargingOrderMapper.xml"); assertNotNull(input); String mapper; try (BufferedReader reader = new BufferedReader(new InputStreamReader(input, StandardCharsets.UTF_8))) { mapper = reader.lines().collect(Collectors.joining("\n")); } String mapper = readMapper(); String refundQuery = mapper.substring( mapper.indexOf("<select id=\"getRefundList\""), mapper.indexOf("<select id=\"chargingOrderGroup\"")); assertTrue(refundQuery.contains("WHERE re.refund_title = '特殊退款处理'")); mapper.indexOf("<select id=\"getRefundListPage\""), mapper.indexOf("<select id=\"countRefundList\"")); assertTrue(refundQuery.contains("refund_title = '特殊退款处理'")); assertTrue(refundQuery.contains("<if test=\"data.specialRefund == null or !data.specialRefund\">")); }
