fix: 优化企业充电分析列表查询

longz

2026-07-21 2a48d95a6a53938ce656ffd83b26517677189057

fix: 优化企业充电分析列表查询

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

@@ -75,8 +75,14 @@

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

    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);

    List<EnterpriseChargingDetailVO> enterpriseChargingDetail(@Param("pageInfo") PageInfo<EnterpriseChargingDetailVO> pageInfo,
                                                              @Param("data") EnterpriseChargingAnalysisQuery query);


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

@@ -2983,20 +2983,66 @@
    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()) {
            pageInfo.setRecords(Collections.emptyList());
            return R.ok(pageInfo);
        }
        List<String> companyNames = list.stream()
                .map(EnterpriseChargingAnalysisVO::getCompanyName)
                .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) {
            EnterpriseChargingAnalysisQuery rowQuery = buildEnterpriseRowQuery(query, row);
            List<Map<String, Object>> topSites = this.baseMapper.enterpriseTopSites(rowQuery);
            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);
            row.setAverageChargingInterval(calculateAverageChargingInterval(this.baseMapper.enterpriseChargingTimes(rowQuery)));
            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));
        }
        pageInfo.setRecords(list);
        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();
        }
        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
    public R<PageInfo<EnterpriseChargingDetailVO>> enterpriseChargingDetail(EnterpriseChargingAnalysisQuery query) {
        PageInfo<EnterpriseChargingDetailVO> pageInfo = new PageInfo<>(query.getPageCurr(), query.getPageSize());

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

@@ -2695,6 +2695,27 @@
        LIMIT 3
    </select>

    <select id="enterpriseTopSitesByCompanies" resultType="map">
        SELECT
            toi.name AS companyName,
            IFNULL(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 toi.name IN
            <foreach collection="companyNames" item="companyName" open="(" separator="," close=")">
                #{companyName}
            </foreach>
        </where>
        GROUP BY toi.name, 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
@@ -2708,6 +2729,26 @@
        ORDER BY co.start_time ASC
    </select>

    <select id="enterpriseChargingTimesByCompanies" resultType="map">
        SELECT DISTINCT
            toi.name AS companyName,
            IFNULL(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 toi.name IN
            <foreach collection="companyNames" item="companyName" open="(" separator="," close=")">
                #{companyName}
            </foreach>
        </where>
        ORDER BY toi.name, co.start_time
    </select>

    <sql id="enterpriseChargingDetailWhere">
        <include refid="enterpriseChargingBaseWhere" />
        <if test="data.code != null and data.code != ''">