From fbce3ff8d28f55fdc8e7c6e446534dcfa181f488 Mon Sep 17 00:00:00 2001
From: longz <longzhaoz@qq.com>
Date: 星期三, 05 八月 2026 20:00:52 +0800
Subject: [PATCH] fix: 企业用户充电分析慢查询，改逆反查 + Service 层聚合（去跨分片 JOIN）
---
ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/TChargingOrderServiceImpl.java | 241 +++++++++++++++++++----
ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/EnterpriseChargingAnalysisMapperTest.java | 46 ++-
ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/EnterpriseChargingDetailMapperTest.java | 5
ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/dto/EnterpriseChargingAggRow.java | 50 +++++
ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/mapper/TChargingOrderMapper.java | 32 ++
ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml | 188 +++++++-----------
ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/AccountSettlementStatementMapperTest.java | 2
7 files changed, 371 insertions(+), 193 deletions(-)
diff --git a/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/dto/EnterpriseChargingAggRow.java b/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/dto/EnterpriseChargingAggRow.java
new file mode 100644
index 0000000..c73e166
--- /dev/null
+++ b/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/dto/EnterpriseChargingAggRow.java
@@ -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() {
+ }
+}
diff --git a/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/mapper/TChargingOrderMapper.java b/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/mapper/TChargingOrderMapper.java
index 6f4c266..282501f 100644
--- a/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/mapper/TChargingOrderMapper.java
+++ b/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,
- @Param("data") EnterpriseChargingAnalysisQuery query);
+ /**
+ * 企业用户充电分析 —— 企业发票行（t_order_invoice 单表，无跨分片 JOIN）。
+ * 只按抬头级别条件过滤（名称/税号/抬头类型/状态），站点等订单级条件在 Service 层经
+ * enterpriseAnalysisMatchingOrderIds 过滤订单后再归并。
+ */
+ List<EnterpriseChargingAggRow.Invoice> enterpriseInvoiceRows(@Param("data") EnterpriseChargingAnalysisQuery query);
- List<Map<String, Object>> enterpriseTopSites(@Param("data") EnterpriseChargingAnalysisQuery query);
+ /**
+ * 企业发票充电明细（t_order_invoice_detail 按 order_invoice_id 分片，IN 可直接路由）。
+ */
+ List<EnterpriseChargingAggRow.Detail> enterpriseInvoiceDetailRows(@Param("invoiceIds") List<Long> invoiceIds);
- List<Map<String, Object>> enterpriseTopSitesByCompanies(@Param("data") EnterpriseChargingAnalysisQuery query,
- @Param("companyNames") List<String> companyNames);
+ /**
+ * 企业分析命中订单 id（co.id IN + 订单级条件，co 按 app_user_id 分片但 id IN 可广播走主键）。
+ * 仅当存在订单级筛选（站点/时间等）时调用。
+ */
+ List<Long> enterpriseAnalysisMatchingOrderIds(@Param("orderIds") List<Long> orderIds,
+ @Param("data") EnterpriseChargingAnalysisQuery query);
- List<LocalDateTime> enterpriseChargingTimes(@Param("data") EnterpriseChargingAnalysisQuery query);
+ /**
+ * 企业分析充电订单数据（id IN 走主键，取 start_time/site_id）。
+ */
+ List<EnterpriseChargingAggRow.Order> enterpriseAnalysisChargingOrders(@Param("orderIds") List<Long> orderIds);
- 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);
diff --git a/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/TChargingOrderServiceImpl.java b/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/TChargingOrderServiceImpl.java
index 30ca3b7..38a1afb 100644
--- a/ruoyi-service/ruoyi-order/src/main/java/com/ruoyi/order/service/impl/TChargingOrderServiceImpl.java
+++ b/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;
+ }
+ 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
diff --git a/ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml b/ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml
index 85a5aff..b7148c7 100644
--- a/ruoyi-service/ruoyi-order/src/main/resources/mapper/order/TChargingOrderMapper.xml
+++ b/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 >= #{data.createTime1}
- </if>
- <if test="data.createTime2 != null">
- AND co.create_time <= #{data.createTime2}
- </if>
- </sql>
+ </select>
- <select id="enterpriseChargingAnalysis" resultType="com.ruoyi.order.dto.EnterpriseChargingAnalysisVO">
+ <!-- 企业发票充电明细（t_order_invoice_detail 按 order_invoice_id 分片，IN 直接路由；order_type=1 只取充电明细） -->
+ <select id="enterpriseInvoiceDetailRows" resultType="com.ruoyi.order.dto.EnterpriseChargingAggRow$Detail">
SELECT
- b.companyName,
- b.socialCreditCode,
- MAX(b.companyAddress) AS companyAddress,
- MAX(b.companyPhone) AS companyPhone,
- COUNT(1) AS invoiceCount,
- SUM(CASE WHEN b.detailInvoiceAmount > 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
+ 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>
</select>
- <select id="enterpriseTopSites" resultType="map">
+ <!-- 命中订单级条件的订单 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="enterpriseChargingOrderWhere" />
+ </where>
+ </select>
+
+ <!-- 命中订单数据（co.id IN 走主键广播，仅取 start_time/site_id） -->
+ <select id="enterpriseAnalysisChargingOrders" resultType="com.ruoyi.order.dto.EnterpriseChargingAggRow$Order">
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
+ 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>
</select>
- <select id="enterpriseTopSitesByCompanies" resultType="map">
+ <!-- 站点名（charging_pile_service.t_site 单表，id IN） -->
+ <select id="enterpriseAnalysisSites" resultType="com.ruoyi.order.dto.EnterpriseChargingAggRow$Site">
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}
- </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
- <where>
- <include refid="enterpriseChargingBaseWhere" />
- AND co.start_time IS NOT NULL
- </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}
- </foreach>
- </where>
- ORDER BY TRIM(toi.name), co.start_time
+ 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">
diff --git a/ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/AccountSettlementStatementMapperTest.java b/ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/AccountSettlementStatementMapperTest.java
index aeb8bf6..d7a450b 100644
--- a/ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/AccountSettlementStatementMapperTest.java
+++ b/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'"));
diff --git a/ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/EnterpriseChargingAnalysisMapperTest.java b/ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/EnterpriseChargingAnalysisMapperTest.java
index 5e4f516..b571624 100644
--- a/ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/EnterpriseChargingAnalysisMapperTest.java
+++ b/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);
}
}
diff --git a/ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/EnterpriseChargingDetailMapperTest.java b/ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/EnterpriseChargingDetailMapperTest.java
index b1871ce..94a129b 100644
--- a/ruoyi-service/ruoyi-order/src/test/java/com/ruoyi/order/mapper/EnterpriseChargingDetailMapperTest.java
+++ b/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\""),
--
Gitblit v1.7.1