分段分析查询优化

Lius

2025-06-09 5192514d752720aee365d11a210480158fda4aeb

分段分析查询优化

 lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/mapper/MdcEquipmentStatisticalInfoMapper.java

@@ -36,4 +36,6 @@
    List<MdcEquipmentStatisticalInfo> findByEquIdAndDate(@Param("equipmentId") String equipmentId, @Param("start") String start, @Param("end") String end);

    MdcEquipmentStatisticalInfo findByEquIdAndMonth(@Param("equipmentId") String equipmentId, @Param("month") String month);

    List<MdcEquipmentStatisticalInfo> findMdcEquipmentStatisticalInfo(@Param("equipmentId") String equipmentId, @Param("start") String start, @Param("end") String end);
}

 lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/mapper/xml/MdcEquipmentStatisticalInfoMapper.xml

@@ -13,6 +13,10 @@
        SELECT TOP 1 process_long FROM mdc_equipment_statistical_info WHERE equipment_id = #{equipmentId} AND the_date = #{validDate}
    </select>

    <select id="findMdcEquipmentStatisticalInfo" resultType="org.jeecg.modules.mdc.entity.MdcEquipmentStatisticalInfo">
        SELECT * FROM mdc_equipment_statistical_info WHERE equipment_id = #{equipmentId} AND the_date BETWEEN #{start} AND #{end}
    </select>

    <select id="findByEquipmentAndMonth" resultType="org.jeecg.modules.mdc.dto.MdcEquipmentStatisticalDto">
        SELECT
            SUM(open_long) openLong,

 lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/IMdcEquipmentStatisticalInfoService.java

@@ -38,4 +38,12 @@
    List<MdcEquipmentStatisticalInfo> findByEquIdAndDate(String equipmentId, String start, String end);

    MdcEquipmentStatisticalInfo findByEquIdAndMonth(String equipmentId, String month);

    /**
     * 根据设备编号和日期查询运行数据
     *
     * @param equipmentId
     * @return
     */
    List<MdcEquipmentStatisticalInfo> findMdcEquipmentStatisticalInfo(String equipmentId, String start, String end);
}

 lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/impl/MdcBoardServiceImpl.java

@@ -2,15 +2,11 @@

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.sun.org.apache.bcel.internal.generic.NEW;
import org.jeecg.common.api.CommonAPI;
import org.jeecg.common.constant.CommonConstant;
import org.jeecg.common.util.TranslateDictTextUtils;
import org.jeecg.modules.eam.constant.EquipmentMaintenanceStatus;
import org.jeecg.modules.eam.constant.EquipmentOperationTagEnum;
import org.jeecg.modules.eam.constant.EquipmentRepairStatus;
import org.jeecg.modules.eam.constant.MaintenanceCategoryEnum;
import org.jeecg.modules.mdc.constant.MdcConstant;
import org.jeecg.modules.mdc.dto.EamEquipmentExtendDto;
import org.jeecg.modules.mdc.entity.*;
import org.jeecg.modules.mdc.mapper.MdcBoardMapper;
@@ -22,14 +18,12 @@
import org.jeecg.modules.mdcJc.service.IMdcJcRcJobreportService;
import org.jeecg.modules.system.entity.MdcProduction;
import org.jeecg.modules.system.service.IMdcProductionService;
import org.joda.time.LocalDateTime;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.stream.Collectors;

@@ -352,8 +346,8 @@
                Object object = mapData.get("spindlespeed");
                mdcBoardEquRealTImeVo.setSpindleSpeed(object == null || "关机".equals(mdcBoardEquRealTImeVo.getOporationDict()) ? "" : object.toString());
            }
            if (mapData.containsKey("spindleload")) {
                Object object = mapData.get("spindleload");
            if (mapData.containsKey("actualspindlespeed")) {
                Object object = mapData.get("actualspindlespeed");
                mdcBoardEquRealTImeVo.setSpindleLoad(object == null || "关机".equals(mdcBoardEquRealTImeVo.getOporationDict()) ? "" : object.toString());
            }
            if (mapData.containsKey("spindlebeilv")) {

 lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/impl/MdcEfficiencyReportServiceImpl.java

@@ -17,7 +17,6 @@
import org.jeecg.modules.system.service.IMdcProductionService;
import org.jeecg.modules.system.service.ISysDepartService;
import org.jeecg.modules.system.service.ISysDictService;
import org.jeecg.modules.system.vo.MdcProOptionsVo;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
@@ -60,6 +59,9 @@

    @Resource
    private ISysDictService sysDictService;

    @Resource
    private IMdcEquipmentStatisticalInfoService mdcEquipmentStatisticalInfoService;

    /**
     * 利用率报表
@@ -1611,10 +1613,17 @@
                    mdcUtilizationRateListDto.setEquipmentType(mdcEquDepDto.getEquipmentType());
                    //
                    List<MdcUtilizationResultDto> list = new ArrayList<>();
                    List<MdcEquipmentStatisticalInfo> mdcEquipmentStatisticalInfo = mdcEquipmentStatisticalInfoService.findMdcEquipmentStatisticalInfo(mdcEquDepDto.getEquipmentId(), dateList.get(0), dateList.get(dateList.size() - 1));
                    Map<String, MdcEquipmentStatisticalInfo> map = mdcEquipmentStatisticalInfo.stream()
                            .collect(Collectors.toMap(MdcEquipmentStatisticalInfo::getTheDate, m -> m));
                    for (String date : dateList) {
                        if ("00:00".equals(startString) && "23:59".equals(endString)) {
                            list.add(this.utilizationRateTrend(mdcEquDepDto.getEquipmentId(), mdcEquDepDto.getEquipmentName(), mdcEquDepDto.getEquipmentType(), date, mdcUtilizationRateList, map));
                        } else {
                        Date startTime = DateUtils.toDate(date + " " + startString + ":00", DateUtils.STR_DATE_TIME_SMALL);
                        Date endTime = DateUtils.toDate(date + " " + endString + ":00", DateUtils.STR_DATE_TIME_SMALL);
                        list.add(this.utilizationRate(mdcEquDepDto.getEquipmentId(), mdcEquDepDto.getEquipmentName(), mdcEquDepDto.getEquipmentType(), startTime, endTime, date, mdcUtilizationRateList));
                        }
                    }
                    mdcUtilizationRateListDto.setDataList(list);
                    listDtos.add(mdcUtilizationRateListDto);
@@ -1631,10 +1640,17 @@
                    mdcEfficiencyListDto.setEquipmentType(mdcEquProDto.getEquipmentType());
                    //
                    List<MdcUtilizationResultDto> list = new ArrayList<>();
                    List<MdcEquipmentStatisticalInfo> mdcEquipmentStatisticalInfo = mdcEquipmentStatisticalInfoService.findMdcEquipmentStatisticalInfo(mdcEquProDto.getEquipmentId(), dateList.get(0), dateList.get(dateList.size() - 1));
                    Map<String, MdcEquipmentStatisticalInfo> map = mdcEquipmentStatisticalInfo.stream()
                            .collect(Collectors.toMap(MdcEquipmentStatisticalInfo::getTheDate, m -> m));
                    for (String date : dateList) {
                        if ("00:00".equals(startString) && "23:59".equals(endString)) {
                            list.add(this.utilizationRateTrend(mdcEquProDto.getEquipmentId(), mdcEquProDto.getEquipmentName(), mdcEquProDto.getEquipmentType(), date, mdcUtilizationRateList, map));
                        } else {
                        Date startTime = DateUtils.toDate(date + " " + startString + ":00", DateUtils.STR_DATE_TIME_SMALL);
                        Date endTime = DateUtils.toDate(date + " " + endString + ":00", DateUtils.STR_DATE_TIME_SMALL);
                        list.add(this.utilizationRate(mdcEquProDto.getEquipmentId(), mdcEquProDto.getEquipmentName(), mdcEquProDto.getEquipmentType(), startTime, endTime, date, mdcUtilizationRateList));
                        }
                    }
                    mdcEfficiencyListDto.setDataList(list);
                    listDtos.add(mdcEfficiencyListDto);
@@ -1702,11 +1718,11 @@
                    mdcEfficiencyListDto.setEquipmentType(mdcEquDepDto.getEquipmentType());

                    List<MdcUtilizationResultDto> list = new ArrayList<>();
                    List<MdcEquipmentStatisticalInfo> mdcEquipmentStatisticalInfo = mdcEquipmentStatisticalInfoService.findMdcEquipmentStatisticalInfo(mdcEquDepDto.getEquipmentId(), dateList.get(0), dateList.get(dateList.size() - 1));
                    Map<String, MdcEquipmentStatisticalInfo> map = mdcEquipmentStatisticalInfo.stream()
                            .collect(Collectors.toMap(MdcEquipmentStatisticalInfo::getTheDate, m -> m));
                    for (String date : dateList) {
                        Date startTime = DateUtils.toDate(date + " 00:00:00", DateUtils.STR_DATE_TIME_SMALL);
                        Date endTime = DateUtils.toDate(date + " 00:00:00", DateUtils.STR_DATE_TIME_SMALL);
                        endTime = DateUtils.addDays(endTime, 1);
                        list.add(this.utilizationRate(mdcEquDepDto.getEquipmentId(), mdcEquDepDto.getEquipmentName(), mdcEquDepDto.getEquipmentType(), startTime, endTime, date, mdcUtilizationRateList));
                        list.add(this.utilizationRateTrend(mdcEquDepDto.getEquipmentId(), mdcEquDepDto.getEquipmentName(), mdcEquDepDto.getEquipmentType(), date, mdcUtilizationRateList, map));
                    }
                    mdcEfficiencyListDto.setDataList(list);
                    listDtos.add(mdcEfficiencyListDto);
@@ -1722,11 +1738,11 @@
                    mdcEfficiencyListDto.setEquipmentName(mdcEquProDto.getEquipmentName());
                    mdcEfficiencyListDto.setEquipmentType(mdcEquProDto.getEquipmentType());
                    List<MdcUtilizationResultDto> list = new ArrayList<>();
                    List<MdcEquipmentStatisticalInfo> mdcEquipmentStatisticalInfo = mdcEquipmentStatisticalInfoService.findMdcEquipmentStatisticalInfo(mdcEquProDto.getEquipmentId(), dateList.get(0), dateList.get(dateList.size() - 1));
                    Map<String, MdcEquipmentStatisticalInfo> map = mdcEquipmentStatisticalInfo.stream()
                            .collect(Collectors.toMap(MdcEquipmentStatisticalInfo::getTheDate, m -> m));
                    for (String date : dateList) {
                        Date startTime = DateUtils.toDate(date + " 00:00:00", DateUtils.STR_DATE_TIME_SMALL);
                        Date endTime = DateUtils.toDate(date + " 00:00:00", DateUtils.STR_DATE_TIME_SMALL);
                        endTime = DateUtils.addDays(endTime, 1);
                        list.add(this.utilizationRate(mdcEquProDto.getEquipmentId(), mdcEquProDto.getEquipmentName(), mdcEquProDto.getEquipmentType(), startTime, endTime, date, mdcUtilizationRateList));
                        list.add(this.utilizationRateTrend(mdcEquProDto.getEquipmentId(), mdcEquProDto.getEquipmentName(), mdcEquProDto.getEquipmentType(), date, mdcUtilizationRateList, map));
                    }
                    mdcEfficiencyListDto.setDataList(list);
                    listDtos.add(mdcEfficiencyListDto);
@@ -1738,6 +1754,31 @@
        return result;
    }

    private MdcUtilizationResultDto utilizationRateTrend(String equipmentId, String equipmentName, String equipmentType, String date, List<MdcUtilizationRate> mdcUtilizationRateList, Map<String, MdcEquipmentStatisticalInfo> map) {
        MdcUtilizationResultDto dto = new MdcUtilizationResultDto();
        dto.setEquipmentId(equipmentId);
        dto.setEquipmentName(equipmentName);
        dto.setEquipmentType(equipmentType);
        dto.setTheDate(date);
        if (map.containsKey(date.replaceAll("-", ""))) {
            MdcEquipmentStatisticalInfo mdcEquipmentStatisticalInfo = map.get(date.replaceAll("-", ""));
            dto.setUtilizationRate(mdcEquipmentStatisticalInfo.getProcessLong().divide(new BigDecimal("86400"), 6, RoundingMode.HALF_UP));
        } else {
            dto.setUtilizationRate(new BigDecimal("0"));
        }

        long rate = dto.getUtilizationRate().multiply(new BigDecimal("100")).longValue();
        for (MdcUtilizationRate mdcUtilizationRate : mdcUtilizationRateList) {
            if (rate >= mdcUtilizationRate.getMinimumRange() && rate < mdcUtilizationRate.getMaximumRange()) {
                dto.setColor(mdcUtilizationRate.getRateParameterColor());
            }
        }
        if (StringUtils.isBlank(dto.getColor())) {
            dto.setColor(mdcUtilizationRateList.get(mdcUtilizationRateList.size() - 1).getRateParameterColor());
        }
        return dto;
    }

    @Override
    public List<BigDecimal> getEfficiencyRate(String equipmentId, String date) {
        return mdcEfficiencyReportMapper.getEfficiencyRate(equipmentId, date);

 lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/impl/MdcEquipmentStatisticalInfoServiceImpl.java

@@ -350,4 +350,9 @@
    public MdcEquipmentStatisticalInfo findByEquIdAndMonth(String equipmentId, String month) {
        return this.baseMapper.findByEquIdAndMonth(equipmentId, month);
    }

    @Override
    public List<MdcEquipmentStatisticalInfo> findMdcEquipmentStatisticalInfo(String equipmentId, String start, String end) {
        return this.baseMapper.findMdcEquipmentStatisticalInfo(equipmentId, start.replaceAll("-", ""), end.replaceAll("-", ""));
    }
}