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

longz

2026-08-05 fbce3ff8d28f55fdc8e7c6e446534dcfa181f488

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

@@ -2693,12 +2693,28 @@
        String startTime2 = timeRange[1];
        String endTime1 = timeRange[2];
        String endTime2 = timeRange[3];
        PageInfo<ChargingOrderVO> pageInfo = new PageInfo<>(dto.getPageCurr(), dto.getPageSize());
        int pageCurr = dto.getPageCurr() == null ? 1 : dto.getPageCurr();
        int pageSize = dto.getPageSize() == null ? 50 : dto.getPageSize();
        PageInfo<ChargingOrderVO> pageInfo = new PageInfo<>(pageCurr, pageSize);
        Long userId = tokenService.getLoginUser().getUserid();
        SysUser sysUser = sysUserClient.getSysUser(userId).getData();
        Integer roleType = sysUser.getRoleType();
        setChargingOrderAuthSiteIds(dto, sysUser, userId, roleType);
        List<ChargingOrderVO> list = this.baseMapper.chargingOrder(pageInfo, dto, startTime1, startTime2, endTime1, endTime2);
        if (StringUtils.hasLength(dto.getPhone()) && (dto.getUserIds() == null || dto.getUserIds().isEmpty())) {
            List<Long> data = appUserClient.getUserIdsByPhone(dto.getPhone()).getData();
            if (data == null || data.isEmpty()) {
                pageInfo.setTotal(0L);
                pageInfo.setRecords(Collections.emptyList());
                return pageInfo;
            }
            dto.setUserIds(data);
        }
        // 手动分页：不传 IPage（避免分页拦截器生成无法下推的全量 COUNT），COUNT 与 ORDER BY + LIMIT 均由 SQL 下推到分片
        long offset = (long) (pageCurr - 1) * pageSize;
        long total = this.baseMapper.chargingOrderCount(dto, startTime1, startTime2, endTime1, endTime2);
        pageInfo.setTotal(total);
        List<ChargingOrderVO> list = total == 0L ? Collections.emptyList()
                : this.baseMapper.chargingOrder(dto, startTime1, startTime2, endTime1, endTime2, offset, pageSize);
        fillChargingOrderBalanceIncome(list);
        fillChargingOrderShareIncome(list);
        fillChargingOrderPageExtra(list, dto.getSiteIds(), sysUser, roleType);
@@ -2710,6 +2726,11 @@
    public TCharingOrderVO chargingOrderSummary(ChargingOrderQuery dto) {
        TCharingOrderVO tCharingOrderVO = new TCharingOrderVO();
        fillEmptyChargingOrderSummary(tCharingOrderVO);
        if (isChargingOrderFullSweep(dto)) {
            // 无任何筛选即全量扫描：汇总聚合跨分片 JOIN（accounting_strategy，分片键 charging_order_id 与 t_charging_order 不一致）
            // 且需把全量订单 id 反查 share/balance，MyCat 只能全量拉回代理层，必然超时。汇总没有 LIMIT 可下推，直接返回空汇总。
            return tCharingOrderVO;
        }
        String[] timeRange = parseChargingOrderTimeRange(dto);
        String startTime1 = timeRange[0];
        String startTime2 = timeRange[1];
@@ -2758,6 +2779,23 @@
        return new String[]{startTime1, startTime2, endTime1, endTime2};
    }

    /** 是否全量扫描：用户未提供任何可收窄的筛选条件（时间/订单号/手机号/站点/桩/枪/状态等），无界聚合无法下推。 */
    private boolean isChargingOrderFullSweep(ChargingOrderQuery dto) {
        return !StringUtils.hasLength(dto.getStartTime())
                && !StringUtils.hasLength(dto.getEndTime())
                && !StringUtils.hasLength(dto.getCode())
                && !StringUtils.hasLength(dto.getPhone())
                && dto.getSiteId() == null
                && dto.getPileId() == null
                && dto.getGunId() == null
                && dto.getOrderType() == null
                && dto.getStatus() == null
                && dto.getIsWhitelist() == null
                && dto.getPaymentType() == null
                && dto.getOrderSource() == null
                && (dto.getUserIds() == null || dto.getUserIds().isEmpty());
    }

    private void fillChargingOrderPageExtra(List<ChargingOrderVO> list, List<Integer> siteIds, SysUser sysUser, Integer roleType) {
        if (list == null || list.isEmpty()) {
            return;
@@ -2777,6 +2815,25 @@
        Map<Integer, Site> siteMap = pageSites.stream()
                .filter(item -> item.getId() != null)
                .collect(Collectors.toMap(Site::getId, item -> item, (a, b) -> a));
        // 列表 SQL 不再跨库 JOIN app_user/partner（避免分片表无筛选全量广播），手机号与合作商名在此回填
        Map<Long, String> phoneMap = new HashMap<>();
        List<Long> pageUserIds = list.stream().map(ChargingOrderVO::getAppUserId).filter(Objects::nonNull).distinct().collect(Collectors.toList());
        if (!pageUserIds.isEmpty()) {
            List<TAppUser> pageUsers = Optional.ofNullable(appUserClient.getUserByIds(pageUserIds).getData()).orElse(new ArrayList<>());
            for (TAppUser user : pageUsers) {
                if (user != null && user.getId() != null) {
                    phoneMap.put(user.getId(), user.getPhone());
                }
            }
        }
        Map<Integer, String> partnerNameMap = new HashMap<>();
        List<Integer> pagePartnerIds = siteMap.values().stream().map(Site::getPartnerId).filter(Objects::nonNull).distinct().collect(Collectors.toList());
        for (Integer partnerId : pagePartnerIds) {
            Partner partner = Optional.ofNullable(partnerClient.getPartnerById(partnerId).getData()).orElse(null);
            if (partner != null && partner.getId() != null) {
                partnerNameMap.put(partner.getId(), partner.getName());
            }
        }
        Map<Long, List<TChargingOrderAccountingStrategy>> strategyMap = orderIds.isEmpty() ? new HashMap<>() :
                chargingOrderAccountingStrategyService.lambdaQuery()
                        .in(TChargingOrderAccountingStrategy::getChargingOrderId, orderIds)
@@ -2794,12 +2851,14 @@
            if (roleType == 2 && chargingOrderVO.getSiteId() != null) {
                chargingOrderVO.setAuthRecord(Boolean.TRUE.equals(authRecordMap.get(chargingOrderVO.getSiteId())));
            }
            chargingOrderVO.setPhone(phoneMap.get(chargingOrderVO.getAppUserId()));
            chargingOrderVO.setCommissionAmount(chargingOrderVO.getRechargeAmount() != null ? chargingOrderVO.getRechargeAmount().multiply(new BigDecimal("0.006")) : new BigDecimal("0"));
            chargingOrderVO.setPlatFormMoney(chargingOrderVO.getRechargeAmount() != null ? chargingOrderVO.getRechargeAmount().multiply(new BigDecimal("0.006")).setScale(2, BigDecimal.ROUND_DOWN) : new BigDecimal("0"));
            chargingOrderVO.setUid(chargingOrderVO.getId() + "");
            Site site = siteMap.get(chargingOrderVO.getSiteId());
            if (site != null) {
                chargingOrderVO.setSiteName(site.getName());
                chargingOrderVO.setPartnerName(partnerNameMap.get(site.getPartnerId()));
            }
            TChargingGun gun = gunMap.get(chargingOrderVO.getChargingGunId());
            TChargingPile pile = pileMap.get(chargingOrderVO.getChargingPileId());
@@ -2890,6 +2949,10 @@
     */
    @Override
    public List<ChargingOrderBalanceIncomeDetailVO> balanceIncomeDetail(ChargingOrderQuery dto) {
        if (isChargingOrderFullSweep(dto)) {
            // 与汇总同根因：全量扫描会把全量订单 id 反查 exchange_code_use_record，MyCat 超时，直接返回空明细
            return new ArrayList<>();
        }
        String startTime1 = null;
        String startTime2 = null;
        String endTime1 = null;
@@ -3087,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;
            }
            for (EnterpriseChargingAggRow.Detail detail : details) {
                if (detail.getOrderId() == null) {
                    continue;
                }
                detailsByInvoice.computeIfAbsent(detail.getInvoiceId(), k -> new ArrayList<>()).add(detail);
                detailByOrder.putIfAbsent(detail.getOrderId(), detail);
            }
        }
        pageInfo.setRecords(list);
        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;
    }

    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;
        }
        return rows.stream().collect(Collectors.groupingBy(row -> companyKey(
                Objects.toString(row.get("companyName"), null),
                Objects.toString(row.get("socialCreditCode"), ""))));
    }

    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