From 5192514d752720aee365d11a210480158fda4aeb Mon Sep 17 00:00:00 2001
From: Lius <Lius2225@163.com>
Date: 星期一, 09 六月 2025 13:22:13 +0800
Subject: [PATCH] 分段分析查询优化
---
lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/mapper/xml/MdcEquipmentStatisticalInfoMapper.xml | 4 ++
lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/mapper/MdcEquipmentStatisticalInfoMapper.java | 2 +
lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/IMdcEquipmentStatisticalInfoService.java | 8 ++++
lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/impl/MdcEfficiencyReportServiceImpl.java | 71 ++++++++++++++++++++++++++++-------
lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/impl/MdcEquipmentStatisticalInfoServiceImpl.java | 5 ++
lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/impl/MdcBoardServiceImpl.java | 10 +----
6 files changed, 77 insertions(+), 23 deletions(-)
diff --git a/lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/mapper/MdcEquipmentStatisticalInfoMapper.java b/lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/mapper/MdcEquipmentStatisticalInfoMapper.java
index a21b8db..0f0b4e3 100644
--- a/lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/mapper/MdcEquipmentStatisticalInfoMapper.java
+++ b/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);
}
diff --git a/lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/mapper/xml/MdcEquipmentStatisticalInfoMapper.xml b/lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/mapper/xml/MdcEquipmentStatisticalInfoMapper.xml
index dcf3f48..a91e6f0 100644
--- a/lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/mapper/xml/MdcEquipmentStatisticalInfoMapper.xml
+++ b/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,
diff --git a/lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/IMdcEquipmentStatisticalInfoService.java b/lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/IMdcEquipmentStatisticalInfoService.java
index 5050e95..e30b1b7 100644
--- a/lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/IMdcEquipmentStatisticalInfoService.java
+++ b/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);
}
diff --git a/lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/impl/MdcBoardServiceImpl.java b/lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/impl/MdcBoardServiceImpl.java
index 516ec3f..6cae1ce 100644
--- a/lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/impl/MdcBoardServiceImpl.java
+++ b/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")) {
diff --git a/lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/impl/MdcEfficiencyReportServiceImpl.java b/lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/impl/MdcEfficiencyReportServiceImpl.java
index 631c938..983d677 100644
--- a/lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/impl/MdcEfficiencyReportServiceImpl.java
+++ b/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) {
- 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));
+ 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) {
- 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));
+ 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);
diff --git a/lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/impl/MdcEquipmentStatisticalInfoServiceImpl.java b/lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/impl/MdcEquipmentStatisticalInfoServiceImpl.java
index 02efd34..080bdbc 100644
--- a/lxzn-module-mdc/src/main/java/org/jeecg/modules/mdc/service/impl/MdcEquipmentStatisticalInfoServiceImpl.java
+++ b/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("-", ""));
+ }
}
--
Gitblit v1.9.3