From 32bcf3f12aef0de69f7cb83d3521b2452a01e50d Mon Sep 17 00:00:00 2001 From: wenwj <‘1106994300@qq.com> Date: 星期六, 12 四月 2025 17:28:29 +0800 Subject: [PATCH] Merge branch 'dev' of http://192.168.50.149:8085/r/aps-backend into dev --- aps-modules/aps-core/src/main/java/com/aps/core/controller/basicData/ApsResourceGroupController.java | 6 aps-modules/aps-core/src/main/java/com/aps/core/domain/ApsPartRouteStat.java | 10 + aps-modules/aps-core/src/main/resources/mapper/core/ApsPartRouteStatMapper.xml | 52 +++++- aps-modules/aps-core/src/main/java/com/aps/core/mapper/ApsPartRouteStatMapper.java | 2 aps-common/aps-common-core/src/main/java/com/aps/common/core/utils/poi/ExcelUtil.java | 2 aps-modules/aps-core/src/main/java/com/aps/core/service/impl/ApsPartRouteStatServiceImpl.java | 190 ++++++++++++++++++++++++++- aps-modules/aps-core/src/main/java/com/aps/core/controller/ApsPartRouteStatController.java | 28 +++ aps-modules/aps-core/src/main/java/com/aps/core/controller/basicData/ApsResourceGroupTempController.java | 2 aps-modules/aps-core/src/main/java/com/aps/core/domain/ApsResourceDateStat.java | 66 +++++++++ aps-modules/aps-core/src/main/java/com/aps/core/service/IApsPartRouteStatService.java | 8 + 10 files changed, 340 insertions(+), 26 deletions(-) diff --git a/aps-common/aps-common-core/src/main/java/com/aps/common/core/utils/poi/ExcelUtil.java b/aps-common/aps-common-core/src/main/java/com/aps/common/core/utils/poi/ExcelUtil.java index 99ae776..7a3a3c9 100644 --- a/aps-common/aps-common-core/src/main/java/com/aps/common/core/utils/poi/ExcelUtil.java +++ b/aps-common/aps-common-core/src/main/java/com/aps/common/core/utils/poi/ExcelUtil.java @@ -688,7 +688,7 @@ * @param wb 宸ヤ綔钖勫璞� * @return 鏍峰紡鍒楄〃 */ - private Map<String, CellStyle> createStyles(Workbook wb) + public Map<String, CellStyle> createStyles(Workbook wb) { // 鍐欏叆鍚勬潯璁板綍,姣忔潯璁板綍瀵瑰簲excel琛ㄤ腑鐨勪竴琛� Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); diff --git a/aps-modules/aps-core/src/main/java/com/aps/core/controller/ApsPartRouteStatController.java b/aps-modules/aps-core/src/main/java/com/aps/core/controller/ApsPartRouteStatController.java index 2540418..550ed09 100644 --- a/aps-modules/aps-core/src/main/java/com/aps/core/controller/ApsPartRouteStatController.java +++ b/aps-modules/aps-core/src/main/java/com/aps/core/controller/ApsPartRouteStatController.java @@ -80,7 +80,6 @@ { return toAjax(apsPartRouteStatService.insertApsPartRouteStat(apsPartRouteStat)); } - /** * 淇敼闆朵欢缁熻琛� */ @@ -91,7 +90,6 @@ { return toAjax(apsPartRouteStatService.updateApsPartRouteStat(apsPartRouteStat)); } - /** * 鍒犻櫎闆朵欢缁熻琛� */ @@ -102,4 +100,30 @@ { return toAjax(apsPartRouteStatService.deleteApsPartRouteStatByIds(ids)); } + + /** + * 1.鏍规嵁鐜版湁闆朵欢璁″垝 鍜屽伐鑹鸿矾绾裤�佽祫婧愭睜淇℃伅 鏇存柊缁熻琛� + * */ + @PostMapping("/updateStat") + public AjaxResult updateStat() + { + apsPartRouteStatService.updatePartRoutPlanDate(); + return success(); + } + + /*** + * 2. 鏍规嵁鏃ユ湡銆佸伐鑹鸿矾绾裤�佽祫婧愭睜淇℃伅 鏌ヨ缁熻琛� + * */ + @GetMapping("/query") + public AjaxResult query() + { + return apsPartRouteStatService.selectResourceDateStat(); + } + + @GetMapping("/exportExcel") + public void exportExcel(HttpServletResponse response) + { + apsPartRouteStatService.exportExcel(response); + } + } diff --git a/aps-modules/aps-core/src/main/java/com/aps/core/controller/basicData/ApsResourceGroupController.java b/aps-modules/aps-core/src/main/java/com/aps/core/controller/basicData/ApsResourceGroupController.java index 6b79ff2..3ac41ea 100644 --- a/aps-modules/aps-core/src/main/java/com/aps/core/controller/basicData/ApsResourceGroupController.java +++ b/aps-modules/aps-core/src/main/java/com/aps/core/controller/basicData/ApsResourceGroupController.java @@ -129,9 +129,9 @@ */ @PostMapping("/confirmData") public AjaxResult confirmData(@RequestBody ApsResourceGroupTemp temp) { - DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"); - LocalDateTime localDateTime = LocalDateTime.parse(temp.getParams().get("requestDate").toString(), formatter); - apsResourceGroupService.confirmData(temp.getBatchNumber(),localDateTime); +// DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"); +// LocalDateTime localDateTime = LocalDateTime.parse(temp.getParams().get("requestDate").toString(), formatter); + apsResourceGroupService.confirmData(temp.getBatchNumber(),temp.getRequestDate()); return AjaxResult.success("纭鎴愬姛锛�"); } } diff --git a/aps-modules/aps-core/src/main/java/com/aps/core/controller/basicData/ApsResourceGroupTempController.java b/aps-modules/aps-core/src/main/java/com/aps/core/controller/basicData/ApsResourceGroupTempController.java index 6fbfd15..58f2b2d 100644 --- a/aps-modules/aps-core/src/main/java/com/aps/core/controller/basicData/ApsResourceGroupTempController.java +++ b/aps-modules/aps-core/src/main/java/com/aps/core/controller/basicData/ApsResourceGroupTempController.java @@ -34,7 +34,7 @@ @GetMapping("/list") public TableDataInfo list(ApsResourceGroupTemp apsResourceGroupTemp) { - startPage(); + //startPage(); List<ApsResourceGroupTemp> list = apsResourceGroupTempService.selectApsResourceGroupTempList(apsResourceGroupTemp); return getDataTable(list); } diff --git a/aps-modules/aps-core/src/main/java/com/aps/core/domain/ApsPartRouteStat.java b/aps-modules/aps-core/src/main/java/com/aps/core/domain/ApsPartRouteStat.java index 4eb129e..07fbe19 100644 --- a/aps-modules/aps-core/src/main/java/com/aps/core/domain/ApsPartRouteStat.java +++ b/aps-modules/aps-core/src/main/java/com/aps/core/domain/ApsPartRouteStat.java @@ -25,6 +25,8 @@ @Excel(name = "宸ュ崟鍙�") private String workOrderNo; + + private String resourceGroupName; /** 宸ュ簭鍙� */ @Excel(name = "宸ュ簭鍙�") private BigDecimal roadProcessNumber; @@ -170,6 +172,14 @@ return batchNumber; } + public String getResourceGroupName() { + return resourceGroupName; + } + + public void setResourceGroupName(String resourceGroupName) { + this.resourceGroupName = resourceGroupName; + } + @Override public String toString() { return new ToStringBuilder(this,ToStringStyle.MULTI_LINE_STYLE) diff --git a/aps-modules/aps-core/src/main/java/com/aps/core/domain/ApsResourceDateStat.java b/aps-modules/aps-core/src/main/java/com/aps/core/domain/ApsResourceDateStat.java new file mode 100644 index 0000000..5ea6181 --- /dev/null +++ b/aps-modules/aps-core/src/main/java/com/aps/core/domain/ApsResourceDateStat.java @@ -0,0 +1,66 @@ +package com.aps.core.domain; + + +import java.math.BigDecimal; +import java.time.LocalDate; + +/* +* 鐢ㄤ簬缁熻璧勬簮缁勬棩璁″垝鏁版嵁 +* */ + +public class ApsResourceDateStat { + public LocalDate getPlanDay() { + return planDay; + } + + public void setPlanDay(LocalDate planDay) { + this.planDay = planDay; + } + + public Integer getDesignTimes() { + return designTimes; + } + + public void setDesignTimes(Integer designTimes) { + this.designTimes = designTimes; + } + + public String getResourceName() { + return resourceName; + } + + public void setResourceName(String resourceName) { + this.resourceName = resourceName; + } + + public Integer getRequireTimes() { + return requireTimes; + } + + public void setRequireTimes(Integer requireTimes) { + this.requireTimes = requireTimes; + } + + public BigDecimal getCapacityLoad() { + return capacityLoad; + } + + public void setCapacityLoad(BigDecimal capacityLoad) { + this.capacityLoad = capacityLoad; + } + + public String getResourceGroupName() { + return resourceGroupName; + } + + public void setResourceGroupName(String resourceGroupName) { + this.resourceGroupName = resourceGroupName; + } + + private LocalDate planDay; + private String resourceName; + private Integer requireTimes; + private Integer designTimes; + private BigDecimal capacityLoad; + private String resourceGroupName; +} diff --git a/aps-modules/aps-core/src/main/java/com/aps/core/mapper/ApsPartRouteStatMapper.java b/aps-modules/aps-core/src/main/java/com/aps/core/mapper/ApsPartRouteStatMapper.java index 1b7f892..9aca473 100644 --- a/aps-modules/aps-core/src/main/java/com/aps/core/mapper/ApsPartRouteStatMapper.java +++ b/aps-modules/aps-core/src/main/java/com/aps/core/mapper/ApsPartRouteStatMapper.java @@ -2,6 +2,7 @@ import java.util.List; import com.aps.core.domain.ApsPartRouteStat; +import com.aps.core.domain.ApsResourceDateStat; import org.apache.ibatis.annotations.Mapper; /** @@ -71,4 +72,5 @@ * */ int deleteLastBatch(String batchNumber); + List<ApsResourceDateStat> selectResourceDateStat(); } diff --git a/aps-modules/aps-core/src/main/java/com/aps/core/service/IApsPartRouteStatService.java b/aps-modules/aps-core/src/main/java/com/aps/core/service/IApsPartRouteStatService.java index 9bd2305..87d06f9 100644 --- a/aps-modules/aps-core/src/main/java/com/aps/core/service/IApsPartRouteStatService.java +++ b/aps-modules/aps-core/src/main/java/com/aps/core/service/IApsPartRouteStatService.java @@ -1,7 +1,10 @@ package com.aps.core.service; import java.util.List; + +import com.aps.common.core.web.domain.AjaxResult; import com.aps.core.domain.ApsPartRouteStat; +import jakarta.servlet.http.HttpServletResponse; /** * 闆朵欢缁熻琛⊿ervice鎺ュ彛 @@ -59,4 +62,9 @@ */ public int deleteApsPartRouteStatById(String id); + void updatePartRoutPlanDate(); + + AjaxResult selectResourceDateStat(); + + void exportExcel(HttpServletResponse response); } diff --git a/aps-modules/aps-core/src/main/java/com/aps/core/service/impl/ApsPartRouteStatServiceImpl.java b/aps-modules/aps-core/src/main/java/com/aps/core/service/impl/ApsPartRouteStatServiceImpl.java index c9c4baa..2a856cb 100644 --- a/aps-modules/aps-core/src/main/java/com/aps/core/service/impl/ApsPartRouteStatServiceImpl.java +++ b/aps-modules/aps-core/src/main/java/com/aps/core/service/impl/ApsPartRouteStatServiceImpl.java @@ -1,13 +1,29 @@ package com.aps.core.service.impl; +import java.math.BigDecimal; +import java.time.LocalDate; import java.time.LocalDateTime; -import java.util.List; -import java.util.Map; +import java.time.format.DateTimeFormatter; +import java.time.temporal.ChronoUnit; +import java.util.*; +import java.util.stream.Collectors; +import com.aps.common.core.utils.poi.ExcelUtil; import com.aps.common.core.utils.uuid.IdUtils; -import com.aps.common.core.utils.uuid.UUID; +import com.aps.common.core.web.domain.AjaxResult; +import com.aps.core.domain.ApsPartPlan; +import com.aps.core.domain.ApsResourceDateStat; +import com.aps.core.mapper.ApsResourceGroupMapper; +import jakarta.servlet.http.HttpServletResponse; +import lombok.extern.slf4j.Slf4j; +import org.apache.poi.ss.usermodel.*; +import org.apache.poi.ss.util.CellRangeAddress; +import org.apache.poi.util.IOUtils; +import org.apache.poi.xssf.streaming.SXSSFCell; +import org.apache.poi.xssf.streaming.SXSSFRow; +import org.apache.poi.xssf.streaming.SXSSFSheet; +import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.springframework.beans.factory.annotation.Autowired; -import org.springframework.cloud.commons.config.DefaultsBindHandlerAdvisor; import org.springframework.stereotype.Service; import com.aps.core.mapper.ApsPartRouteStatMapper; import com.aps.core.domain.ApsPartRouteStat; @@ -21,12 +37,15 @@ * @author zhl * @date 2025-04-11 */ +@Slf4j @Service public class ApsPartRouteStatServiceImpl implements IApsPartRouteStatService { + @Autowired private ApsPartRouteStatMapper apsPartRouteStatMapper; - private DefaultsBindHandlerAdvisor.MappingsProvider mappingsProvider; + @Autowired + private ApsResourceGroupMapper resourceGroupMapper; /** * 鏌ヨ闆朵欢缁熻琛� @@ -103,13 +122,16 @@ + /** + * 鏌ヨ闆朵欢宸ュ簭骞舵洿鏂拌鍒掑紑宸ユ棩鏈� + * */ + @Override public void updatePartRoutPlanDate() { List<ApsPartRouteStat> tempList = apsPartRouteStatMapper.selectPartRoutStat(); /*瀵箃empList 鎸夌収 workOrderNo 杩涜鍒嗙粍*/ Map<String, List<ApsPartRouteStat>> groupByWorkOrderNo = tempList.stream().collect(groupingBy(ApsPartRouteStat::getWorkOrderNo)); /*鏈璁$畻鎵规鍙�*/ String batchNum = IdUtils.fastSimpleUUID(); - apsPartRouteStatMapper.deleteLastBatch(batchNum); for (Map.Entry<String, List<ApsPartRouteStat>> entry : groupByWorkOrderNo.entrySet()) { /*鍙栧嚭姣忎釜闆朵欢宸ュ崟鐨勫伐鑹鸿矾绾夸俊鎭�*/ List<ApsPartRouteStat> apsPartRouteStatList = entry.getValue(); @@ -118,7 +140,7 @@ /*涓存椂淇濆瓨 涓婁竴閬撳伐搴忕殑宸ュ簭鎬诲伐鏃�*/ ApsPartRouteStat last=null; for (int i = 0; i <apsPartRouteStatList.size(); i++) { - ApsPartRouteStat stat = apsPartRouteStatList.get(0); + ApsPartRouteStat stat = apsPartRouteStatList.get(i); /*鍒ゆ柇褰撳墠宸ュ簭 褰撳墠宸ュ簭鍙湁涓�鏉�*/ if (stat.getCurrentProcessNumber().equals(stat.getRoadProcessNumber())) { /* 瀵� stat.getProcessPlanStartDay() 鍜屽綋鍓嶆棩鏈� 杩涜瀵规瘮锛屽彧瀵规瘮鍒版棩锛屼笉鐢ㄧ鍗佸垎绉�*/ @@ -127,7 +149,7 @@ } } /*鏈紑宸ュ伐搴忕殑璁″垝寮�宸ユ棩=涓婁竴閬撳伐搴忕殑璁″垝寮�宸ユ棩+涓婁竴閬撳伐搴忕殑宸ュ簭鎬诲伐鏃躲��*/ - if (stat.getCurrentProcessNumber().compareTo(stat.getRoadProcessNumber()) > 0) { + if (stat.getCurrentProcessNumber().compareTo(stat.getRoadProcessNumber()) < 0) { if (last != null) { stat.setProcessPlanStartDay(last.getProcessPlanStartDay().plusHours(last.getProcessTotalTime())); } @@ -140,6 +162,158 @@ } } apsPartRouteStatMapper.deleteLastBatch(batchNum); + } + /** 鏌ヨ璧勬簮鏃ュ巻琛� + */ + @Override + public AjaxResult selectResourceDateStat() { + /*鏌ュ嚭鎵�鏈夌粺璁$殑鏁版嵁*/ + AjaxResult result = new AjaxResult(200, "鐢熸垚鎴愬姛"); + List<ApsResourceDateStat> list = apsPartRouteStatMapper.selectResourceDateStat(); + if (!list.isEmpty()) { + /*姹傚嚭璁″垝鐨勬渶澶ф椂闂达紝娌℃湁 璁剧疆涓哄綋鍓嶆椂闂�+15澶�*/ + ApsResourceDateStat apsResourceDateStat = list.stream().max(Comparator.comparing(ApsResourceDateStat::getPlanDay)).orElse(null); + LocalDate maxPlanDay = LocalDate.now().plus(15, ChronoUnit.DAYS); + if (apsResourceDateStat != null) { + maxPlanDay = apsResourceDateStat.getPlanDay(); + } + /*缁勭粐鍑烘棩鏈熻〃澶�*/ + List<String> planDays = new ArrayList<>(); + LocalDate startDay = LocalDate.now(); + DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd"); + while (!startDay.isAfter(maxPlanDay)) { + planDays.add(startDay.format(formatter)); + startDay = startDay.plus(1, ChronoUnit.DAYS); + } + /*缁勭粐鍑哄垪澶存暟鎹�*/ + List<String> resourceNames =list.stream().map(ApsResourceDateStat::getResourceName).distinct().toList(); + /*鍑嗗璧勬簮缁勪俊鎭�*/ + List<Map<String, List<ApsResourceDateStat>>> targetList = new ArrayList<>(); + resourceNames.forEach(resourceName -> { + /*鏍规嵁鏃ユ湡琛ㄥご鐨勯『搴忥紝缁勭粐鍑烘瘡涓棩鏈熶笅鐨勭粺璁¢」鐩�*/ + Map<String, List<ApsResourceDateStat>> maps = new HashMap<>(Map.of()); + List<ApsResourceDateStat> currentDaysResouces=new ArrayList<>(); + for (String planDay : planDays) { + /*鏌ユ壘褰撳墠璁″垝鏃ユ湡涓嬬殑鎵�鏈夎祫婧愮粍鐨勬暟鎹�*/ + Optional<ApsResourceDateStat> first = list.stream() + .filter(item -> + item.getPlanDay().equals(LocalDate.parse(planDay, formatter)) + && item.getResourceGroupName().equals(resourceName) + ).collect(Collectors.toList()).stream().findFirst(); + if(first.isPresent()){ + currentDaysResouces.add(first.get()); + } + else { + ApsResourceDateStat empty = new ApsResourceDateStat(); + empty.setPlanDay(LocalDate.parse(planDay, formatter)); + empty.setDesignTimes(0); + empty.setRequireTimes(0); + empty.setResourceName(resourceName); + empty.setResourceGroupName(resourceName); + empty.setCapacityLoad(BigDecimal.ZERO); + currentDaysResouces.add(empty); + } + }; + maps.put(resourceName, currentDaysResouces); + targetList.add(maps); + }); + result.put("planTitle", planDays); + result.put("planTable", targetList); + } + return result; + } + + @Override + public void exportExcel(HttpServletResponse response) { + SXSSFWorkbook wb = new SXSSFWorkbook(500); + wb.createSheet(); + wb.setSheetName(0, "闆朵欢缁熻琛�"); + response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); + response.setCharacterEncoding("utf-8"); + + Map<String, CellStyle> styles = createStyles(wb); + CellStyle title = styles.get("title"); + try + { + + AjaxResult stat = selectResourceDateStat(); + List<String> days = (List<String>) stat.get("planTitle"); + List<Map<String, List<ApsResourceDateStat>>> table= (List<Map<String, List<ApsResourceDateStat>>>) stat.get("planTable"); + SXSSFSheet sheet = wb.getSheetAt(0); + /*濉啓鏃ユ湡鍒� 鍜� 宸ユ椂鍒�*/ + SXSSFRow rowDay = sheet.createRow(0); + SXSSFRow rowTitle = sheet.createRow(1); + + SXSSFCell daytitle = rowDay.createCell(0); + daytitle.setCellValue("鏃ユ湡"); + daytitle.setCellStyle(title); + SXSSFCell titleCell = rowTitle.createCell(0); + titleCell.setCellValue("璧勬簮缁�"); + titleCell.setCellStyle(title); + + for (int i = 0; i < days.size(); i++) { + SXSSFCell dateCell = rowDay.createCell(i * 3 + 1); + SXSSFCell designHoursCell = rowTitle.createCell(i * 3 + 1); + SXSSFCell requireHoursCell = rowTitle.createCell(i * 3 + 2); + SXSSFCell loadCell = rowTitle.createCell(i * 3 + 3); + dateCell.setCellValue(days.get(i)); + designHoursCell.setCellValue("璁捐宸ユ椂"); + requireHoursCell.setCellValue("闇�姹傚伐鏃�"); + loadCell.setCellValue("浜ц兘璐熻嵎"); + /*set cell style*/ + dateCell.setCellStyle(title); + designHoursCell.setCellStyle(title); + requireHoursCell.setCellStyle(title); + loadCell.setCellStyle(title); + + /*鍚堝苟鏃ユ湡鍗曞厓鏍�*/ + sheet.addMergedRegion( new CellRangeAddress(0, 0, i*3+1, i*3+3)); + } + for (int i = 0; i < table.size(); i++) { + Map<String, List<ApsResourceDateStat>> resourceList = table.get(i); + /*鍒涘缓鏁版嵁琛�*/ + SXSSFRow dataRow = sheet.createRow(i+2); + for( Map.Entry<String, List<ApsResourceDateStat>> entry : resourceList.entrySet()){ + String resourceName = entry.getKey(); + List<ApsResourceDateStat> resourceDateStats = entry.getValue(); + dataRow.createCell(0).setCellValue(resourceName); + for (int j = 0; j < resourceDateStats.size(); j++) { + ApsResourceDateStat apsResourceDateStat = resourceDateStats.get(j); + dataRow.createCell(j*3+1).setCellValue(apsResourceDateStat.getDesignTimes()); + dataRow.createCell(j*3+2).setCellValue(apsResourceDateStat.getRequireTimes()); + dataRow.createCell(j*3+3).setCellValue(apsResourceDateStat.getCapacityLoad().doubleValue()+"%"); + } + } + + } + wb.write(response.getOutputStream()); + } + catch (Exception e) + { + log.error("瀵煎嚭Excel寮傚父{}", e.getMessage()); + } + finally + { + IOUtils.closeQuietly(wb); + } } + private Map<String,CellStyle> createStyles(SXSSFWorkbook wb) + { + Map<String,CellStyle> styles=new HashMap<>(); + CellStyle style = wb.createCellStyle(); + style.setAlignment(HorizontalAlignment.CENTER); + style.setVerticalAlignment(VerticalAlignment.CENTER); + Font titleFont = wb.createFont(); + titleFont.setFontName("Arial"); + titleFont.setFontHeightInPoints((short) 16); + titleFont.setBold(true); + style.setFont(titleFont); + DataFormat dataFormat = wb.createDataFormat(); + style.setDataFormat(dataFormat.getFormat("@")); + styles.put("title", style); + return styles; + } + + } diff --git a/aps-modules/aps-core/src/main/resources/mapper/core/ApsPartRouteStatMapper.xml b/aps-modules/aps-core/src/main/resources/mapper/core/ApsPartRouteStatMapper.xml index d029bb9..250f555 100644 --- a/aps-modules/aps-core/src/main/resources/mapper/core/ApsPartRouteStatMapper.xml +++ b/aps-modules/aps-core/src/main/resources/mapper/core/ApsPartRouteStatMapper.xml @@ -17,10 +17,11 @@ <result property="delFlag" column="del_flag" /> <result property="createBy" column="create_by" /> <result property="batchNumber" column="batch_number" /> + <result property="resourceGroupName" column="resource_group_name" /> </resultMap> <sql id="selectApsPartRouteStatVo"> - select id, work_order_no, road_process_number, current_process_number, production_quantity, standard_time, process_total_time, process_plan_start_day, design_times, del_flag, create_by, batch_number from aps_part_route_stat + select id, work_order_no, road_process_number, current_process_number, production_quantity, standard_time, process_total_time, process_plan_start_day, design_times, del_flag, create_by, batch_number ,resource_group_name from aps_part_route_stat </sql> <select id="selectApsPartRouteStatList" parameterType="ApsPartRouteStat" resultMap="ApsPartRouteStatResult"> @@ -53,11 +54,12 @@ <if test="productionQuantity != null">production_quantity,</if> <if test="standardTime != null">standard_time,</if> <if test="processTotalTime != null">process_total_time,</if> - <if test="processPlanStartDay != null and processPlanStartDay != ''">process_plan_start_day,</if> + <if test="processPlanStartDay != null ">process_plan_start_day,</if> <if test="designTimes != null">design_times,</if> <if test="delFlag != null">del_flag,</if> <if test="createBy != null">create_by,</if> <if test="batchNumber != null">batch_number,</if> + <if test="resourceGroupName != null">resource_group_name,</if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="id != null">#{id},</if> @@ -67,11 +69,12 @@ <if test="productionQuantity != null">#{productionQuantity},</if> <if test="standardTime != null">#{standardTime},</if> <if test="processTotalTime != null">#{processTotalTime},</if> - <if test="processPlanStartDay != null and processPlanStartDay != ''">#{processPlanStartDay},</if> + <if test="processPlanStartDay != null">#{processPlanStartDay},</if> <if test="designTimes != null">#{designTimes},</if> <if test="delFlag != null">#{delFlag},</if> <if test="createBy != null">#{createBy},</if> <if test="batchNumber != null">#{batchNumber},</if> + <if test="resourceGroupName != null">#{resourceGroupName},</if> </trim> </insert> @@ -109,7 +112,7 @@ </update> <select id="selectPartRoutStat" resultMap="ApsPartRouteStatResult" > select rt.work_order_no, - rt.process_name, + rt.process_name as resource_group_name, cast( rt.process_number as numeric(18,2)) as road_process_number, cast( pl.process_number as numeric(18,2)) as current_process_number, pl.production_quantity, @@ -122,13 +125,40 @@ left join aps_resource_group as rs on rs.resource_group_name=rt.process_name where pl.del_flag='0' and rt.del_flag='0' /*闆朵欢璁″垝鐘舵�佷负锛氶潪瀹屽伐 */ - and ( pl.order_status!='' and ( pl.order_status!='3' or pl.order_status!='瀹屽伐') ) - /*宸ヨ壓璺嚎鐨勫伐搴忓彿>=闆朵欢璁″垝鐨勫綋鍓嶅伐搴�*/ - and cast( rt.process_number as decimal(18,2)) >= cast( pl.process_number as decimal(18,2)) - /*宸ュ簭璁″垝寮�宸ユ棩 <= 璧勬簮缁勭殑鎴鏃ユ湡*/ - and rt.process_plan_start_day<=rs.request_date - /*璧勬簮缁勯渶姹傛埅姝㈡棩鏈�<=褰撳墠鏃ユ湡+15澶�*/ - and rs.request_date<=(current_date + INTERVAL '15 day') + and ( pl.document_status is not null and ( pl.document_status!='3' or pl.order_status!='瀹屽伐') ) + /*宸ュ簭璁″垝寮�宸ユ棩 灏忎簬绛変簬 璧勬簮缁勭殑鎴鏃ユ湡*/ + and rt.process_plan_start_day <= rs.request_date order by rt.work_order_no,process_plan_start_day </select> + + <resultMap type="ApsResourceDateStat" id="ApsResourceDateStatResult"> + <result property="planDay" column="plan_day" /> + <result property="resourceName" column="resource_name" /> + <result property="requireTimes" column="require_times" /> + <result property="designTimes" column="design_times" /> + <result property="capacityLoad" column="capacity_load" /> + <result property="resourceGroupName" column="resource_name" /> + </resultMap> + + <select id="selectResourceDateStat" resultMap="ApsResourceDateStatResult"> + + with pl as (select DATE(st.process_plan_start_day) AS plan_day, + resource_group_name as resource_name, + sum(process_total_time) as require_times + from aps_part_route_stat as st + where st.del_flag = '0' + group by resource_group_name, plan_day + order by plan_day,resource_group_name + ) + select + pl.plan_day + ,pl.resource_name + , require_times + , (gp.theory_hours * gp.devices_quantity) as design_times + , ((gp.theory_hours * gp.devices_quantity) / require_times*100) as capacity_load + from pl + left join aps_resource_group as gp on pl.resource_name = gp.resource_group_name + where gp.del_flag = '0' + </select> + </mapper> \ No newline at end of file -- Gitblit v1.9.3