fix: 企业用户充电分析慢查询，改逆反查 + Service 层聚合（去跨分片 JOIN）

longz

2026-08-05 fbce3ff8d28f55fdc8e7c6e446534dcfa181f488

fix: 企业用户充电分析慢查询，改逆反查 + Service 层聚合（去跨分片 JOIN）

/order/t-charging-order/enterprise/charging/analysis 加载慢，无筛选时 MyCat 必超时，根因：
- 原 enterpriseChargingAnalysis 把 t_order_invoice(10片/app_user_id) +
  t_order_invoice_detail(20片/order_invoice_id) + t_charging_order(50片/app_user_id)
  在代理层跨分片 JOIN，分片键不一致只能全量广播，每次请求跑 3 遍
  （主聚合 + 前三站点 + 充电时间间隔，均重新扫描整条链路）
- mapper 带 IPage，分页拦截器生成无法下推的全量 COUNT（GROUP BY 外层）

修复（沿用 getRefundList / enterpriseChargingDetail 逆反查模式）：
- 去掉 IPage，改 Service 层手工分页
- 新增单表/按分片键 IN 路由的构建块：enterpriseInvoiceRows(单表) →
  enterpriseInvoiceDetailRows(order_invoice_id IN) →
  enterpriseAnalysisChargingOrders(id IN 走主键) → enterpriseAnalysisSites(id IN)
- 订单级筛选（站点/时间等）用 enterpriseAnalysisMatchingOrderIds 先筛命中订单，
  口径与 enterpriseChargingDetail 一致；按公司聚合在 Java 完成
- 语义保持：status IN (2,3)、order_type=1 明细、历史订单被删的发票仍计数、
  明细金额为 0 回退发票总金额、发票无命中订单时剔除

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

 ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/dto/EnterpriseChargingAggRow.java

New file
@@ -0,0 +1,50 @@
package com.ruoyi.order.dto;

import lombok.Data;

import java.math.BigDecimal;
import java.time.LocalDateTime;

/**
 * 企业用户充电分析 —— 逆反查聚合的中间行。
 *
 * <p>原实现（enterpriseChargingAnalysis 等）把 t_order_invoice(10 片/app_user_id)、
 * t_order_invoice_detail(20 片/order_invoice_id)、t_charging_order(50 片/app_user_id)
 * 在 MyCat 代理层做跨分片 JOIN，分片键不一致导致只能全量广播拉取，无筛选时必超时。
 * 改为分表单查 + 按各自分片键 IN 路由，再在 Service 层聚合（与 enterpriseChargingDetail 同模式）。
 */
public class EnterpriseChargingAggRow {

    @Data
    public static class Invoice {
        private Long id;
        private String companyName;
        private String socialCreditCode;
        private String companyAddress;
        private String companyPhone;
        private BigDecimal totalAmount;
    }

    @Data
    public static class Detail {
        private Long invoiceId;
        private Long orderId;
        private BigDecimal invoiceAmount;
    }

    @Data
    public static class Order {
        private Long orderId;
        private LocalDateTime startTime;
        private Long siteId;
    }

    @Data
    public static class Site {
        private Long siteId;
        private String siteName;
    }

    private EnterpriseChargingAggRow() {
    }
}

 ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/mapper/TChargingOrderMapper.java

@@ -85,18 +85,34 @@

    List<PayOrderDto> payOrderQuery(@Param("pageInfo") PageInfo<PayOrderDto> pageInfo, @Param("data") PayOrderQueryDto payOrderQueryDto);

    List<EnterpriseChargingAnalysisVO> enterpriseChargingAnalysis(@Param("pageInfo") PageInfo<EnterpriseChargingAnalysisVO> pageInfo,
    /**
     * 企业用户充电分析 —— 企业发票行（t_order_invoice 单表，无跨分片 JOIN）。
     * 只按抬头级别条件过滤（名称/税号/抬头类型/状态），站点等订单级条件在 Service 层经
     * enterpriseAnalysisMatchingOrderIds 过滤订单后再归并。
     */
    List<EnterpriseChargingAggRow.Invoice> enterpriseInvoiceRows(@Param("data") EnterpriseChargingAnalysisQuery query);

    /**
     * 企业发票充电明细（t_order_invoice_detail 按 order_invoice_id 分片，IN 可直接路由）。
     */
    List<EnterpriseChargingAggRow.Detail> enterpriseInvoiceDetailRows(@Param("invoiceIds") List<Long> invoiceIds);

    /**
     * 企业分析命中订单 id（co.id IN + 订单级条件，co 按 app_user_id 分片但 id IN 可广播走主键）。
     * 仅当存在订单级筛选（站点/时间等）时调用。
     */
    List<Long> enterpriseAnalysisMatchingOrderIds(@Param("orderIds") List<Long> orderIds,
                                                                  @Param("data") EnterpriseChargingAnalysisQuery query);

    List<Map<String, Object>> enterpriseTopSites(@Param("data") EnterpriseChargingAnalysisQuery query);
    /**
     * 企业分析充电订单数据（id IN 走主键，取 start_time/site_id）。
     */
    List<EnterpriseChargingAggRow.Order> enterpriseAnalysisChargingOrders(@Param("orderIds") List<Long> orderIds);

    List<Map<String, Object>> enterpriseTopSitesByCompanies(@Param("data") EnterpriseChargingAnalysisQuery query,
                                                            @Param("companyNames") List<String> companyNames);

    List<LocalDateTime> enterpriseChargingTimes(@Param("data") EnterpriseChargingAnalysisQuery query);

    List<Map<String, Object>> enterpriseChargingTimesByCompanies(@Param("data") EnterpriseChargingAnalysisQuery query,
                                                                 @Param("companyNames") List<String> companyNames);
    /**
     * 企业分析站点名（charging_pile_service.t_site 单表，id IN）。
     */
    List<EnterpriseChargingAggRow.Site> enterpriseAnalysisSites(@Param("siteIds") List<Long> siteIds);

    List<Long> enterpriseInvoiceIds(@Param("data") EnterpriseChargingAnalysisQuery query);


 ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/TChargingOrderServiceImpl.java

@@ -3150,66 +3150,217 @@

    @Override
    public R<PageInfo<EnterpriseChargingAnalysisVO>> enterpriseChargingAnalysis(EnterpriseChargingAnalysisQuery query) {
        PageInfo<EnterpriseChargingAnalysisVO> pageInfo = new PageInfo<>(query.getPageCurr(), query.getPageSize());
        List<EnterpriseChargingAnalysisVO> list = this.baseMapper.enterpriseChargingAnalysis(pageInfo, query);
        if (list == null || list.isEmpty()) {
        int pageCurr = query.getPageCurr() == null ? 1 : query.getPageCurr();
        int pageSize = query.getPageSize() == null ? 50 : query.getPageSize();
        PageInfo<EnterpriseChargingAnalysisVO> pageInfo = new PageInfo<>(pageCurr, pageSize);
        // 逆反查聚合（不再跨分片 JOIN，构建块见 TChargingOrderMapper.xml enterpriseInvoiceRows 等）：
        //   1) t_order_invoice 单表查企业发票（10 片按 app_user_id 分片，抬头级条件可下推）
        //   2) t_order_invoice_detail 按 order_invoice_id IN 路由查充电明细
        //   3) 命中订单级条件（站点/时间等）→ 命中订单 id
        //   4) t_charging_order 按 id IN 走主键取 start_time/site_id
        //   5) t_site 单表取站点名
        //   6) 按公司聚合 + 手工分页（不再传 IPage，避免分页拦截器生成无法下推的全量 COUNT）
        List<EnterpriseChargingAggRow.Invoice> invoices = this.baseMapper.enterpriseInvoiceRows(query);
        if (invoices == null || invoices.isEmpty()) {
            pageInfo.setRecords(Collections.emptyList());
            return R.ok(pageInfo);
        }
        List<String> companyNames = list.stream()
                .map(EnterpriseChargingAnalysisVO::getCompanyName)
        Map<Long, List<EnterpriseChargingAggRow.Detail>> detailsByInvoice = new HashMap<>();
        Map<Long, EnterpriseChargingAggRow.Detail> detailByOrder = new LinkedHashMap<>();
        List<Long> invoiceIds = invoices.stream()
                .map(EnterpriseChargingAggRow.Invoice::getId)
                .filter(Objects::nonNull)
                .distinct()
                .collect(Collectors.toList());
        Map<String, List<Map<String, Object>>> topSitesByCompany = groupByCompany(
                this.baseMapper.enterpriseTopSitesByCompanies(query, companyNames));
        Map<String, List<Map<String, Object>>> chargingTimesByCompany = groupByCompany(
                this.baseMapper.enterpriseChargingTimesByCompanies(query, companyNames));
        for (EnterpriseChargingAnalysisVO row : list) {
            String companyKey = companyKey(row.getCompanyName(), row.getSocialCreditCode());
            List<Map<String, Object>> topSites = topSitesByCompany.getOrDefault(companyKey, Collections.emptyList());
            String topSiteNames = topSites.stream()
                    .sorted(Comparator.comparing((Map<String, Object> item) -> ((Number) item.getOrDefault("counts", 0)).longValue()).reversed())
                    .limit(3)
                    .map(item -> Objects.toString(item.get("siteName"), ""))
                    .filter(org.springframework.util.StringUtils::hasText)
                    .collect(Collectors.joining("、"));
            row.setTopSiteNames(topSiteNames);
            List<LocalDateTime> chargingTimes = chargingTimesByCompany.getOrDefault(companyKey, Collections.emptyList()).stream()
                    .map(item -> toLocalDateTime(item.get("startTime")))
                    .filter(Objects::nonNull)
                    .sorted()
                    .collect(Collectors.toList());
            row.setAverageChargingInterval(calculateAverageChargingInterval(chargingTimes));
        for (int start = 0; start < invoiceIds.size(); start += ENTERPRISE_INVOICE_BATCH_SIZE) {
            int end = Math.min(start + ENTERPRISE_INVOICE_BATCH_SIZE, invoiceIds.size());
            List<EnterpriseChargingAggRow.Detail> details =
                    this.baseMapper.enterpriseInvoiceDetailRows(invoiceIds.subList(start, end));
            if (details == null) {
                continue;
        }
        pageInfo.setRecords(list);
            for (EnterpriseChargingAggRow.Detail detail : details) {
                if (detail.getOrderId() == null) {
                    continue;
                }
                detailsByInvoice.computeIfAbsent(detail.getInvoiceId(), k -> new ArrayList<>()).add(detail);
                detailByOrder.putIfAbsent(detail.getOrderId(), detail);
            }
        }
        if (detailByOrder.isEmpty()) {
            pageInfo.setRecords(Collections.emptyList());
            return R.ok(pageInfo);
        }
        List<Long> candidateOrderIds = new ArrayList<>(detailByOrder.keySet());
        boolean orderFilter = hasEnterpriseOrderFilter(query);
        Set<Long> matchedOrderIds = null;
        if (orderFilter) {
            matchedOrderIds = new HashSet<>();
            for (int start = 0; start < candidateOrderIds.size(); start += ENTERPRISE_ORDER_ID_BATCH_SIZE) {
                int end = Math.min(start + ENTERPRISE_ORDER_ID_BATCH_SIZE, candidateOrderIds.size());
                List<Long> matched = this.baseMapper.enterpriseAnalysisMatchingOrderIds(
                        candidateOrderIds.subList(start, end), query);
                if (matched != null) {
                    matchedOrderIds.addAll(matched);
                }
            }
            if (matchedOrderIds.isEmpty()) {
                pageInfo.setRecords(Collections.emptyList());
                return R.ok(pageInfo);
            }
        }
        List<Long> effectiveOrderIds = orderFilter ? new ArrayList<>(matchedOrderIds) : candidateOrderIds;
        Map<Long, EnterpriseChargingAggRow.Order> orderMap = new HashMap<>();
        for (int start = 0; start < effectiveOrderIds.size(); start += ENTERPRISE_ORDER_ID_BATCH_SIZE) {
            int end = Math.min(start + ENTERPRISE_ORDER_ID_BATCH_SIZE, effectiveOrderIds.size());
            List<EnterpriseChargingAggRow.Order> orders =
                    this.baseMapper.enterpriseAnalysisChargingOrders(effectiveOrderIds.subList(start, end));
            if (orders == null) {
                continue;
            }
            for (EnterpriseChargingAggRow.Order order : orders) {
                orderMap.put(order.getOrderId(), order);
            }
        }
        Set<Long> siteIds = orderMap.values().stream()
                .map(EnterpriseChargingAggRow.Order::getSiteId)
                .filter(Objects::nonNull)
                .collect(Collectors.toSet());
        Map<Long, String> siteNameMap = new HashMap<>();
        if (!siteIds.isEmpty()) {
            List<EnterpriseChargingAggRow.Site> sites = this.baseMapper.enterpriseAnalysisSites(new ArrayList<>(siteIds));
            if (sites != null) {
                for (EnterpriseChargingAggRow.Site site : sites) {
                    siteNameMap.put(site.getSiteId(), site.getSiteName());
                }
            }
        }
        Map<String, EnterpriseCompanyAgg> companyAggs = new LinkedHashMap<>();
        for (EnterpriseChargingAggRow.Invoice invoice : invoices) {
            List<EnterpriseChargingAggRow.Detail> details =
                    detailsByInvoice.getOrDefault(invoice.getId(), Collections.emptyList());
            if (details.isEmpty()) {
                // 无充电明细（order_type=1）的发票不计，等同原 INNER JOIN tod.order_type=1 语义
                continue;
            }
            List<EnterpriseChargingAggRow.Order> invoiceOrders = new ArrayList<>();
            BigDecimal detailAmount = BigDecimal.ZERO;
            for (EnterpriseChargingAggRow.Detail detail : details) {
                if (orderFilter && !matchedOrderIds.contains(detail.getOrderId())) {
                    continue;
                }
                detailAmount = detailAmount.add(nullToZero(detail.getInvoiceAmount()));
                EnterpriseChargingAggRow.Order order = orderMap.get(detail.getOrderId());
                if (order != null) {
                    invoiceOrders.add(order);
                }
            }
            if (orderFilter && invoiceOrders.isEmpty()) {
                // 订单级筛选后该发票无命中订单 → 剔除，等同原 LEFT JOIN + 订单级 WHERE 语义
                continue;
            }
            BigDecimal invoiceAmount = detailAmount.signum() > 0 ? detailAmount : nullToZero(invoice.getTotalAmount());
            String key = companyKey(invoice.getCompanyName(), invoice.getSocialCreditCode());
            EnterpriseCompanyAgg agg = companyAggs.get(key);
            if (agg == null) {
                agg = new EnterpriseCompanyAgg(invoice.getCompanyName(), invoice.getSocialCreditCode(),
                        invoice.getCompanyAddress(), invoice.getCompanyPhone());
                companyAggs.put(key, agg);
            }
            agg.invoiceCount++;
            agg.invoiceAmount = agg.invoiceAmount.add(invoiceAmount);
            for (EnterpriseChargingAggRow.Order order : invoiceOrders) {
                if (order.getStartTime() != null) {
                    agg.startTimes.add(order.getStartTime());
                    if (agg.latestChargingTime == null || order.getStartTime().isAfter(agg.latestChargingTime)) {
                        agg.latestChargingTime = order.getStartTime();
                    }
                }
                if (order.getSiteId() != null) {
                    agg.siteOrderIds.computeIfAbsent(order.getSiteId(), k -> new HashSet<>()).add(order.getOrderId());
                }
            }
        }
        if (companyAggs.isEmpty()) {
            pageInfo.setRecords(Collections.emptyList());
            return R.ok(pageInfo);
        }
        List<EnterpriseChargingAnalysisVO> all = companyAggs.values().stream()
                .sorted(Comparator.comparingLong((EnterpriseCompanyAgg agg) -> agg.invoiceCount).reversed())
                .map(agg -> buildEnterpriseAnalysisVO(agg, siteNameMap))
                .collect(Collectors.toList());
        pageInfo.setTotal(all.size());
        long offset = (long) (pageCurr - 1L) * pageSize;
        if (offset >= all.size()) {
            pageInfo.setRecords(Collections.emptyList());
        } else {
            int toIndex = (int) Math.min(offset + pageSize, all.size());
            pageInfo.setRecords(all.subList((int) offset, toIndex));
        }
        return R.ok(pageInfo);
    }

    private Map<String, List<Map<String, Object>>> groupByCompany(List<Map<String, Object>> rows) {
        if (rows == null || rows.isEmpty()) {
            return Collections.emptyMap();
    private boolean hasEnterpriseOrderFilter(EnterpriseChargingAnalysisQuery query) {
        return query.getSiteType() != null
                || query.getPartnerId() != null
                || query.getSiteId() != null
                || query.getCreateTime1() != null
                || query.getCreateTime2() != null
                || (query.getCode() != null && !query.getCode().isEmpty())
                || query.getOrderType() != null
                || (query.getPhone() != null && !query.getPhone().isEmpty())
                || query.getStatus() != null
                || (query.getPartnerName() != null && !query.getPartnerName().isEmpty())
                || query.getOrderSource() != null
                || query.getStartTime1() != null
                || query.getStartTime2() != null
                || query.getEndTime1() != null
                || query.getEndTime2() != null;
        }
        return rows.stream().collect(Collectors.groupingBy(row -> companyKey(
                Objects.toString(row.get("companyName"), null),
                Objects.toString(row.get("socialCreditCode"), ""))));

    private EnterpriseChargingAnalysisVO buildEnterpriseAnalysisVO(EnterpriseCompanyAgg agg,
                                                                  Map<Long, String> siteNameMap) {
        EnterpriseChargingAnalysisVO vo = new EnterpriseChargingAnalysisVO();
        vo.setCompanyName(agg.companyName);
        vo.setSocialCreditCode(agg.socialCreditCode);
        vo.setCompanyAddress(agg.companyAddress);
        vo.setCompanyPhone(agg.companyPhone);
        vo.setInvoiceCount(agg.invoiceCount);
        vo.setInvoiceAmount(agg.invoiceAmount);
        vo.setLatestChargingTime(agg.latestChargingTime);
        String topSiteNames = agg.siteOrderIds.entrySet().stream()
                .sorted(Comparator.comparingInt((Map.Entry<Long, Set<Long>> e) -> e.getValue().size()).reversed())
                .limit(3)
                .map(e -> siteNameMap.getOrDefault(e.getKey(), ""))
                .filter(org.springframework.util.StringUtils::hasText)
                .collect(Collectors.joining("、"));
        vo.setTopSiteNames(topSiteNames);
        vo.setAverageChargingInterval(calculateAverageChargingInterval(new ArrayList<>(agg.startTimes)));
        return vo;
    }

    private static class EnterpriseCompanyAgg {
        private final String companyName;
        private final String socialCreditCode;
        private final String companyAddress;
        private final String companyPhone;
        private long invoiceCount;
        private BigDecimal invoiceAmount = BigDecimal.ZERO;
        private LocalDateTime latestChargingTime;
        private final Map<Long, Set<Long>> siteOrderIds = new HashMap<>();
        private final TreeSet<LocalDateTime> startTimes = new TreeSet<>();

        private EnterpriseCompanyAgg(String companyName, String socialCreditCode,
                                     String companyAddress, String companyPhone) {
            this.companyName = companyName;
            this.socialCreditCode = socialCreditCode;
            this.companyAddress = companyAddress;
            this.companyPhone = companyPhone;
        }
    }

    private String companyKey(String companyName, String socialCreditCode) {
        return Objects.toString(companyName, "") + "\u0000" + Objects.toString(socialCreditCode, "");
    }

    private LocalDateTime toLocalDateTime(Object value) {
        if (value instanceof LocalDateTime) {
            return (LocalDateTime) value;
        }
        if (value instanceof java.sql.Timestamp) {
            return ((java.sql.Timestamp) value).toLocalDateTime();
        }
        if (value instanceof Date) {
            return LocalDateTime.ofInstant(((Date) value).toInstant(), ZoneId.systemDefault());
        }
        return null;
    }

    @Override

 ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml

@@ -3019,137 +3019,91 @@
        ORDER BY b.site_id
    </select>

    <sql id="enterpriseChargingBaseWhere">
        toi.invoicing_object_type = 2
        AND toi.status IN (2, 3)
        AND tod.order_type = 1
    <!--
        企业用户充电分析 —— 逆反查聚合（原实现见 git：enterpriseChargingAnalysis 等把
        t_order_invoice(10片/app_user_id) + t_order_invoice_detail(20片/order_invoice_id) +
        t_charging_order(50片/app_user_id) 在代理层跨分片 JOIN，分片键不一致只能全量广播，无筛选必超时）。
        这里只提供单表/按分片键 IN 路由的构建块，按公司聚合在 Service 层完成（与 enterpriseChargingDetail 同模式）。
    -->

    <!-- 企业发票行（t_order_invoice 单表 10 片，抬头级过滤可下推到各分片；id 唯一故无需 GROUP BY） -->
    <select id="enterpriseInvoiceRows" resultType="com.ruoyi.order.dto.EnterpriseChargingAggRow$Invoice">
        SELECT
            id,
            TRIM(name) AS companyName,
            IFNULL(TRIM(tax_identification_number), '') AS socialCreditCode,
            company_address AS companyAddress,
            company_phone AS companyPhone,
            IFNULL(total_amount, 0) AS totalAmount
        FROM t_order_invoice
        WHERE invoicing_object_type = 2
          AND status IN (2, 3)
        <if test="data.exactCompanyName != null and data.exactCompanyName != ''">
            AND TRIM(toi.name) = TRIM(#{data.exactCompanyName})
            AND TRIM(name) = TRIM(#{data.exactCompanyName})
        </if>
        <if test="(data.exactCompanyName == null or data.exactCompanyName == '') and data.companyName != null and data.companyName != ''">
            AND TRIM(toi.name) LIKE CONCAT('%', TRIM(#{data.companyName}), '%')
            AND TRIM(name) LIKE CONCAT('%', TRIM(#{data.companyName}), '%')
        </if>
        <if test="data.socialCreditCode != null and data.socialCreditCode != ''">
            AND IFNULL(TRIM(toi.tax_identification_number), '') = TRIM(#{data.socialCreditCode})
            AND IFNULL(TRIM(tax_identification_number), '') = TRIM(#{data.socialCreditCode})
        </if>
        <if test="data.siteType != null">
            AND s.site_type = #{data.siteType}
        </if>
        <if test="data.partnerId != null">
            AND s.partner_id = #{data.partnerId}
        </if>
        <if test="data.siteId != null">
            AND co.site_id = #{data.siteId}
        </if>
        <if test="data.createTime1 != null">
            AND co.create_time &gt;= #{data.createTime1}
        </if>
        <if test="data.createTime2 != null">
            AND co.create_time &lt;= #{data.createTime2}
        </if>
    </sql>

    <select id="enterpriseChargingAnalysis" resultType="com.ruoyi.order.dto.EnterpriseChargingAnalysisVO">
        SELECT
            b.companyName,
            b.socialCreditCode,
            MAX(b.companyAddress) AS companyAddress,
            MAX(b.companyPhone) AS companyPhone,
            COUNT(1) AS invoiceCount,
            SUM(CASE WHEN b.detailInvoiceAmount &gt; 0 THEN b.detailInvoiceAmount ELSE b.totalAmount END) AS invoiceAmount,
            MAX(b.latestChargingTime) AS latestChargingTime
        FROM (
            SELECT
                toi.id AS invoiceId,
                TRIM(toi.name) AS companyName,
                IFNULL(TRIM(toi.tax_identification_number), '') AS socialCreditCode,
                MAX(toi.company_address) AS companyAddress,
                MAX(toi.company_phone) AS companyPhone,
                MAX(IFNULL(toi.total_amount, 0)) AS totalAmount,
                SUM(IFNULL(tod.invoice_amount, 0)) AS detailInvoiceAmount,
                MAX(co.start_time) AS latestChargingTime
            FROM t_order_invoice toi
            INNER JOIN t_order_invoice_detail tod ON tod.order_invoice_id = toi.id
            LEFT JOIN t_charging_order co ON co.id = tod.order_id
            LEFT JOIN charging_pile_service.t_site s ON s.id = co.site_id
            <where>
                <include refid="enterpriseChargingBaseWhere" />
            </where>
            GROUP BY toi.id, TRIM(toi.name), TRIM(toi.tax_identification_number)
        ) b
        GROUP BY b.companyName, b.socialCreditCode
        ORDER BY invoiceCount DESC
    </select>

    <select id="enterpriseTopSites" resultType="map">
    <!-- 企业发票充电明细（t_order_invoice_detail 按 order_invoice_id 分片，IN 直接路由；order_type=1 只取充电明细） -->
    <select id="enterpriseInvoiceDetailRows" resultType="com.ruoyi.order.dto.EnterpriseChargingAggRow$Detail">
        SELECT
            s.name AS siteName,
            COUNT(DISTINCT co.id) AS counts
        FROM t_order_invoice toi
        INNER JOIN t_order_invoice_detail tod ON tod.order_invoice_id = toi.id
        INNER JOIN t_charging_order co ON co.id = tod.order_id
        LEFT JOIN charging_pile_service.t_site s ON s.id = co.site_id
        <where>
            <include refid="enterpriseChargingBaseWhere" />
            AND s.name IS NOT NULL
        </where>
        GROUP BY s.id, s.name
        ORDER BY counts DESC
        LIMIT 3
    </select>

    <select id="enterpriseTopSitesByCompanies" resultType="map">
        SELECT
            TRIM(toi.name) AS companyName,
            IFNULL(TRIM(toi.tax_identification_number), '') AS socialCreditCode,
            s.name AS siteName,
            COUNT(DISTINCT co.id) AS counts
        FROM t_order_invoice toi
        INNER JOIN t_order_invoice_detail tod ON tod.order_invoice_id = toi.id
        INNER JOIN t_charging_order co ON co.id = tod.order_id
        LEFT JOIN charging_pile_service.t_site s ON s.id = co.site_id
        <where>
            <include refid="enterpriseChargingBaseWhere" />
            AND s.name IS NOT NULL
            AND TRIM(toi.name) IN
            <foreach collection="companyNames" item="companyName" open="(" separator="," close=")">
                #{companyName}
            order_invoice_id AS invoiceId,
            order_id AS orderId,
            IFNULL(invoice_amount, 0) AS invoiceAmount
        FROM t_order_invoice_detail
        WHERE order_type = 1
          AND order_invoice_id IN
        <foreach collection="invoiceIds" item="invoiceId" open="(" separator="," close=")">
            #{invoiceId}
            </foreach>
        </where>
        GROUP BY TRIM(toi.name), TRIM(toi.tax_identification_number), s.id, s.name
    </select>

    <select id="enterpriseChargingTimes" resultType="java.time.LocalDateTime">
        SELECT DISTINCT co.start_time
        FROM t_order_invoice toi
        INNER JOIN t_order_invoice_detail tod ON tod.order_invoice_id = toi.id
        INNER JOIN t_charging_order co ON co.id = tod.order_id
        LEFT JOIN charging_pile_service.t_site s ON s.id = co.site_id
    <!-- 命中订单级条件的订单 id（co.id IN 走主键广播，条件同 enterpriseChargingOrderWhere，与明细口径一致） -->
    <select id="enterpriseAnalysisMatchingOrderIds" resultType="long">
        SELECT DISTINCT co.id
        FROM t_charging_order co
        <if test="data.siteType != null or data.partnerId != null or (data.partnerName != null and data.partnerName != '')">
            INNER JOIN charging_pile_service.t_site s ON s.id = co.site_id
        </if>
        <if test="data.partnerName != null and data.partnerName != ''">
            INNER JOIN charging_pile_service.t_partner p ON p.id = s.partner_id
        </if>
        <if test="data.phone != null and data.phone != ''">
            INNER JOIN charging_pile_account.t_app_user au ON au.id = co.app_user_id
        </if>
        <where>
            <include refid="enterpriseChargingBaseWhere" />
            AND co.start_time IS NOT NULL
            <include refid="enterpriseChargingOrderWhere" />
        </where>
        ORDER BY co.start_time ASC
    </select>

    <select id="enterpriseChargingTimesByCompanies" resultType="map">
        SELECT DISTINCT
            TRIM(toi.name) AS companyName,
            IFNULL(TRIM(toi.tax_identification_number), '') AS socialCreditCode,
            co.start_time AS startTime
        FROM t_order_invoice toi
        INNER JOIN t_order_invoice_detail tod ON tod.order_invoice_id = toi.id
        INNER JOIN t_charging_order co ON co.id = tod.order_id
        LEFT JOIN charging_pile_service.t_site s ON s.id = co.site_id
        <where>
            <include refid="enterpriseChargingBaseWhere" />
            AND co.start_time IS NOT NULL
            AND TRIM(toi.name) IN
            <foreach collection="companyNames" item="companyName" open="(" separator="," close=")">
                #{companyName}
    <!-- 命中订单数据（co.id IN 走主键广播，仅取 start_time/site_id） -->
    <select id="enterpriseAnalysisChargingOrders" resultType="com.ruoyi.order.dto.EnterpriseChargingAggRow$Order">
        SELECT
            id AS orderId,
            start_time AS startTime,
            site_id AS siteId
        FROM t_charging_order
        WHERE id IN
        <foreach collection="orderIds" item="orderId" open="(" separator="," close=")">
            #{orderId}
            </foreach>
        </where>
        ORDER BY TRIM(toi.name), co.start_time
    </select>

    <!-- 站点名（charging_pile_service.t_site 单表，id IN） -->
    <select id="enterpriseAnalysisSites" resultType="com.ruoyi.order.dto.EnterpriseChargingAggRow$Site">
        SELECT
            id AS siteId,
            name AS siteName
        FROM charging_pile_service.t_site
        WHERE id IN
        <foreach collection="siteIds" item="siteId" open="(" separator="," close=")">
            #{siteId}
        </foreach>
    </select>

    <select id="enterpriseInvoiceIds" resultType="long">

 ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/AccountSettlementStatementMapperTest.java

@@ -27,7 +27,7 @@
        String orderMapper = resource("mapper/order/TChargingOrderMapper.xml");
        String settlementQuery = orderMapper.substring(
                orderMapper.indexOf("<select id=\"getAccountSettlementStatement\""),
                orderMapper.indexOf("<sql id=\"enterpriseChargingBaseWhere\""));
                orderMapper.indexOf("<select id=\"enterpriseInvoiceRows\""));
        assertTrue(settlementQuery.contains("b.order_source = 1"));
        assertTrue(settlementQuery.contains("b.source_name = 'ICBC'"));
        assertTrue(settlementQuery.contains("pay_channel = 'ICBC'"));

 ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/EnterpriseChargingAnalysisMapperTest.java

@@ -26,23 +26,35 @@
            mapper = reader.lines().collect(Collectors.joining("\n"));
        }

        String baseWhere = mapper.substring(
                mapper.indexOf("<sql id=\"enterpriseChargingBaseWhere\""),
                mapper.indexOf("<select id=\"enterpriseChargingAnalysis\""));
        String analysis = mapper.substring(
                mapper.indexOf("<select id=\"enterpriseChargingAnalysis\""),
                mapper.indexOf("<select id=\"enterpriseTopSites\""));
        String invoiceRows = block(mapper, "enterpriseInvoiceRows");
        String detailRows = block(mapper, "enterpriseInvoiceDetailRows");
        String orderRows = block(mapper, "enterpriseAnalysisChargingOrders");

        assertFalse(baseWhere.contains("co.del_flag"));
        assertFalse(baseWhere.contains("co.recharge_payment_status"));
        assertTrue(baseWhere.contains("AND toi.status IN (2, 3)"));
        assertFalse(baseWhere.contains("AND toi.status IN (1, 2, 3)"));
        assertTrue(analysis.contains("LEFT JOIN t_charging_order co ON co.id = tod.order_id"));
        assertTrue(analysis.contains("COUNT(1) AS invoiceCount"));
        assertTrue(analysis.contains("ORDER BY invoiceCount DESC"));
        assertFalse(analysis.contains("chargingCount"));
        assertTrue(analysis.contains("TRIM(toi.name) AS companyName"));
        assertTrue(analysis.contains("IFNULL(TRIM(toi.tax_identification_number), '') AS socialCreditCode"));
        assertTrue(analysis.contains("detailInvoiceAmount"));
        // 企业发票单表查（逆反查，不再跨分片 JOIN）：抬头级别过滤可下推到各分片
        assertTrue(invoiceRows.contains("invoicing_object_type = 2"));
        assertTrue(invoiceRows.contains("status IN (2, 3)"));
        assertFalse(invoiceRows.contains("LEFT JOIN"));
        assertFalse(invoiceRows.contains("INNER JOIN"));
        // 不按订单维度过滤：历史充电订单被删除的发票仍会计数（订单缺失的兜底逻辑在 Service 层）
        assertFalse(invoiceRows.contains("co.del_flag"));
        assertFalse(invoiceRows.contains("recharge_payment_status"));

        // 明细只取充电订单（order_type=1），按 order_invoice_id 分片键 IN 路由
        assertTrue(detailRows.contains("order_type = 1"));
        assertTrue(detailRows.contains("order_invoice_id IN"));

        // 订单数据按 id IN 走主键、单表不 JOIN（t_charging_order 50 片按 app_user_id 分片，不可与发票跨片 JOIN）
        assertTrue(orderRows.contains("FROM t_charging_order"));
        assertTrue(orderRows.contains("WHERE id IN"));
        assertFalse(orderRows.contains("JOIN"));
    }

    private static String block(String mapper, String id) {
        int start = mapper.indexOf("<select id=\"" + id + "\"");
        int end = mapper.indexOf("</select>", start);
        if (start < 0 || end < 0) {
            throw new AssertionError("missing select block: " + id);
        }
        return mapper.substring(start, end);
    }
}

 ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/EnterpriseChargingDetailMapperTest.java

@@ -29,9 +29,6 @@
        String detailSelect = mapper.substring(
                mapper.indexOf("<sql id=\"enterpriseChargingDetailSelect\""),
                mapper.indexOf("<select id=\"enterpriseChargingDetail\""));
        String baseWhere = mapper.substring(
                mapper.indexOf("<sql id=\"enterpriseChargingBaseWhere\""),
                mapper.indexOf("<select id=\"enterpriseChargingAnalysis\""));

        assertFalse(detailSelect.contains("SELECT DISTINCT"));
        assertFalse(detailSelect.contains("FROM t_charging_order_accounting_strategy"));
@@ -42,8 +39,6 @@
        assertFalse(detailSelect.contains("AS electrovalence"));
        assertFalse(detailSelect.contains("AS serviceCharge"));
        assertTrue(detailSelect.contains("AS endMode"));
        assertTrue(baseWhere.contains("AND toi.status IN (2, 3)"));
        assertFalse(baseWhere.contains("AND toi.status IN (1, 2, 3)"));

        String countSelect = mapper.substring(
                mapper.indexOf("<select id=\"enterpriseChargingDetailCount\""),