From 21887ddd12bdba4e2b00456c94a3cb8e1568c000 Mon Sep 17 00:00:00 2001
From: ZengTao <2773468879@qq.com>
Date: 星期一, 12 五月 2025 18:21:52 +0800
Subject: [PATCH] Merge branch 'master' of http://10.153.19.25:10105/r/YiWuProject
---
hangzhoumesParent/common/servicebase/src/main/java/com/mes/utils/excel/ExcelMergeStrategy.java | 118 +++++++++++++
hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/entity/DailyProductionData.java | 98 ++++++++++
hangzhoumesParent/common/servicebase/src/main/java/com/mes/utils/excel/ExcelUtil.java | 19 ++
hangzhoumesParent/common/servicebase/src/main/resources/mapper/LargenScreenMapper.xml | 161 +++++++++++++++++
hangzhoumesParent/common/servicebase/src/main/java/com/mes/utils/excel/ExcelMerge.java | 27 +++
hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/mapper/LargenScreenMapper.java | 3
hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/service/impl/LargenScreenServiceImpl.java | 44 ++++
hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/controller/LargenScreenController.java | 14 +
hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/service/LargenScreenService.java | 5
9 files changed, 488 insertions(+), 1 deletions(-)
diff --git a/hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/controller/LargenScreenController.java b/hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/controller/LargenScreenController.java
index c4d4b17..9ab3d43 100644
--- a/hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/controller/LargenScreenController.java
+++ b/hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/controller/LargenScreenController.java
@@ -1,6 +1,7 @@
package com.mes.largenscreen.controller;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
+import com.mes.largenscreen.entity.DailyProductionData;
import com.mes.largenscreen.entity.DailyProductionVO;
import com.mes.largenscreen.entity.DateRequest;
import com.mes.largenscreen.service.LargenScreenService;
@@ -14,6 +15,7 @@
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
+import java.util.List;
/**
* @Author : zhoush
@@ -34,5 +36,17 @@
return Result.build(200, "鏌ヨ鎴愬姛", largenScreenService.queryDailyProduction(query));
}
+ @ApiOperation("鎸夌収鏉′欢缁熻姣忔棩鐢熶骇鎯呭喌瀵煎嚭")
+ @PostMapping("/exportDailyProduction")
+ public void exportDailyProduction(@RequestBody @Validated DateRequest query) {
+ largenScreenService.exportDailyProduction(query);
+ }
+
+ @ApiOperation("鎸夌収鏉′欢缁熻鐢熶骇鎯呭喌")
+ @PostMapping("/queryProduction")
+ public Result<List<DailyProductionData>> queryProduction(@RequestBody @Validated DateRequest query) {
+ return Result.build(200, "鏌ヨ鎴愬姛", largenScreenService.queryProduction(query));
+ }
+
}
diff --git a/hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/entity/DailyProductionData.java b/hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/entity/DailyProductionData.java
new file mode 100644
index 0000000..89e8ab7
--- /dev/null
+++ b/hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/entity/DailyProductionData.java
@@ -0,0 +1,98 @@
+package com.mes.largenscreen.entity;
+
+import com.alibaba.excel.annotation.ExcelProperty;
+import com.mes.utils.excel.ExcelMerge;
+import lombok.Data;
+
+/**
+ * @Author : zhoush
+ * @Date: 2025/3/12 14:30
+ * @Description:
+ */
+@Data
+public class DailyProductionData {
+
+ @ExcelProperty(value = "鏃ユ湡")
+ @ExcelMerge(merge = true)
+ private String productDate;
+
+ @ExcelProperty(value = {"鍒囧壊", "寮�濮嬫椂闂�"})
+ @ExcelMerge(merge = true)
+ private String edgBeginTime;
+ @ExcelProperty(value = {"鍒囧壊", "缁撴潫鏃堕棿"})
+ @ExcelMerge(merge = true)
+ private String edgEndTime;
+ @ExcelProperty(value = {"鍒囧壊", "鎬昏�楁椂"})
+ @ExcelMerge(merge = true)
+ private String edgTimeTotal;
+ @ExcelProperty(value = {"鍒囧壊", "绌洪棽鏃堕暱"})
+ @ExcelMerge(merge = true)
+ private String edgTimeFree;
+ @ExcelProperty(value = {"鍒囧壊", "宸ヤ綔鏃堕暱"})
+ @ExcelMerge(merge = true)
+ private String edgTimeDiff;
+ @ExcelProperty(value = {"鍒囧壊", "鎬荤墖鏁�"})
+ @ExcelMerge(merge = true)
+ private String countOut;
+ @ExcelProperty(value = {"鍒囧壊", "鎬婚潰绉�(m^2)"})
+ @ExcelMerge(merge = true)
+ private String totalAreaOut;
+
+
+ @ExcelProperty(value = {"閽㈠寲", "寮�濮嬫椂闂�"})
+ @ExcelMerge(merge = true)
+ private String bigBeginTime;
+ @ExcelProperty(value = {"閽㈠寲", "缁撴潫鏃堕棿"})
+ @ExcelMerge(merge = true)
+ private String bigEndTime;
+ @ExcelProperty(value = {"閽㈠寲", "鎬昏�楁椂"})
+ @ExcelMerge(merge = true)
+ private String bigTimeTotal;
+ @ExcelMerge(merge = true)
+ @ExcelProperty(value = {"閽㈠寲", "绌洪棽鏃堕暱"})
+ private String bigTimeFree;
+ @ExcelProperty(value = {"閽㈠寲", "宸ヤ綔鏃堕暱"})
+ @ExcelMerge(merge = true)
+ private String bigTimeDiff;
+ @ExcelProperty(value = {"閽㈠寲", "鎬荤倝鏁�"})
+ @ExcelMerge(merge = true)
+ private String temperingLayoutCount;
+ @ExcelProperty(value = {"閽㈠寲", "鎬荤墖鏁�"})
+ @ExcelMerge(merge = true)
+ private String temperingGlassCount;
+ @ExcelProperty(value = {"閽㈠寲", "鎬婚潰绉�(m^2)"})
+ @ExcelMerge(merge = true)
+ private String temperingArea;
+
+
+ @ExcelProperty(value = {"涓┖", "寮�濮嬫椂闂�"})
+ @ExcelMerge(merge = true)
+ private String hollowBeginTime;
+ @ExcelProperty(value = {"涓┖", "缁撴潫鏃堕棿"})
+ @ExcelMerge(merge = true)
+ private String hollowEndTime;
+ @ExcelProperty(value = {"涓┖", "鎬昏�楁椂"})
+ @ExcelMerge(merge = true)
+ private String hollowTimeTotal;
+ @ExcelProperty(value = {"涓┖", "绌洪棽鏃堕暱"})
+ @ExcelMerge(merge = true)
+ private String hollowTimeFree;
+ @ExcelProperty(value = {"涓┖", "宸ヤ綔鏃堕暱"})
+ @ExcelMerge(merge = true)
+ private String hollowTimeDiff;
+ @ExcelProperty(value = {"涓┖", "鎬荤墖鏁�"})
+ @ExcelMerge(merge = true)
+ private String hollowGlassCount;
+ @ExcelProperty(value = {"涓┖", "鎬婚潰绉�(m^2)"})
+ @ExcelMerge(merge = true)
+ private String hollowArea;
+
+ @ExcelProperty(value = {"宸ョ▼淇℃伅", "宸ョ▼鍙�"})
+ private String engineerId;
+ @ExcelProperty(value = {"宸ョ▼淇℃伅", "鎬荤墖閲�"})
+ private String glassTotal;
+ @ExcelProperty(value = {"宸ョ▼淇℃伅", "鎬婚潰绉�(m^2)"})
+ private String glassTotalArea;
+
+
+}
diff --git a/hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/mapper/LargenScreenMapper.java b/hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/mapper/LargenScreenMapper.java
index 529b5fd..482f59c 100644
--- a/hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/mapper/LargenScreenMapper.java
+++ b/hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/mapper/LargenScreenMapper.java
@@ -1,5 +1,6 @@
package com.mes.largenscreen.mapper;
+import com.mes.largenscreen.entity.DailyProductionData;
import com.mes.largenscreen.entity.DailyProductionVO;
import java.util.List;
@@ -13,4 +14,6 @@
public interface LargenScreenMapper {
List<DailyProductionVO> queryDailyProduction(String beginDate, String endDate);
+
+ List<DailyProductionData> exportDailyProduction(String beginDate, String endDate);
}
diff --git a/hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/service/LargenScreenService.java b/hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/service/LargenScreenService.java
index f3a419f..7951023 100644
--- a/hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/service/LargenScreenService.java
+++ b/hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/service/LargenScreenService.java
@@ -1,9 +1,9 @@
package com.mes.largenscreen.service;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
+import com.mes.largenscreen.entity.DailyProductionData;
import com.mes.largenscreen.entity.DailyProductionVO;
import com.mes.largenscreen.entity.DateRequest;
-import com.mes.largenscreen.entity.PieChartVO;
import java.util.List;
@@ -18,4 +18,7 @@
List<DailyProductionVO> querySameDayProduction(DateRequest query);
+ void exportDailyProduction(DateRequest query);
+
+ List<DailyProductionData> queryProduction(DateRequest query);
}
diff --git a/hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/service/impl/LargenScreenServiceImpl.java b/hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/service/impl/LargenScreenServiceImpl.java
index 1e0505a..27572cc 100644
--- a/hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/service/impl/LargenScreenServiceImpl.java
+++ b/hangzhoumesParent/common/servicebase/src/main/java/com/mes/largenscreen/service/impl/LargenScreenServiceImpl.java
@@ -1,15 +1,22 @@
package com.mes.largenscreen.service.impl;
+import com.alibaba.excel.EasyExcel;
+import com.alibaba.excel.support.ExcelTypeEnum;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
+import com.mes.largenscreen.entity.DailyProductionData;
import com.mes.largenscreen.entity.DailyProductionVO;
import com.mes.largenscreen.entity.DateRequest;
import com.mes.largenscreen.mapper.LargenScreenMapper;
import com.mes.largenscreen.service.LargenScreenService;
import com.mes.tools.PageUtil;
+import com.mes.utils.excel.ExcelMergeStrategy;
+import com.mes.utils.excel.ExcelUtil;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import javax.annotation.Resource;
+import javax.servlet.http.HttpServletResponse;
+import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
@@ -25,6 +32,8 @@
@Resource
private LargenScreenMapper largenScreenMapper;
+ @Resource
+ private HttpServletResponse response;
@Override
public Page<DailyProductionVO> queryDailyProduction(DateRequest query) {
@@ -50,5 +59,40 @@
return list;
}
+ @Override
+ public void exportDailyProduction(DateRequest query) {
+ try {
+ if (StringUtils.isEmpty(query.getBeginDate())) {
+ SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
+ Calendar calendar = Calendar.getInstance();
+ calendar.add(Calendar.DAY_OF_MONTH, -6);
+ query.setBeginDate(sdf.format(calendar.getTime()));
+ query.setEndDate(sdf.format(new Date()));
+ }
+ ExcelUtil.setExcelResponseProp(response, "渚涘簲鍟嗗垪琛�-鍚堝苟");
+ List<DailyProductionData> providerList = largenScreenMapper.exportDailyProduction(query.getBeginDate(), query.getEndDate());
+ EasyExcel.write(response.getOutputStream())
+ .head(DailyProductionData.class)
+ .registerWriteHandler(new ExcelMergeStrategy(DailyProductionData.class))
+ .excelType(ExcelTypeEnum.XLSX).sheet().doWrite(providerList);
+ } catch (IOException e) {
+ throw new RuntimeException(e);
+ }
+ }
+
+ @Override
+ public List<DailyProductionData> queryProduction(DateRequest query) {
+ if (StringUtils.isEmpty(query.getBeginDate())) {
+ SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
+ Calendar calendar = Calendar.getInstance();
+ calendar.add(Calendar.DAY_OF_MONTH, -6);
+ query.setBeginDate(sdf.format(calendar.getTime()));
+ query.setEndDate(sdf.format(new Date()));
+ }
+ return largenScreenMapper.exportDailyProduction(query.getBeginDate(), query.getEndDate());
+
+
+ }
+
}
diff --git a/hangzhoumesParent/common/servicebase/src/main/java/com/mes/utils/excel/ExcelMerge.java b/hangzhoumesParent/common/servicebase/src/main/java/com/mes/utils/excel/ExcelMerge.java
new file mode 100644
index 0000000..4776f27
--- /dev/null
+++ b/hangzhoumesParent/common/servicebase/src/main/java/com/mes/utils/excel/ExcelMerge.java
@@ -0,0 +1,27 @@
+package com.mes.utils.excel;
+
+import java.lang.annotation.*;
+
+/**
+ * @Author : zhoush
+ * @Date: 2025/5/12 16:51
+ * @Description:
+ */
+@Target({ElementType.FIELD})
+@Retention(RetentionPolicy.RUNTIME)
+@Documented
+public @interface ExcelMerge {
+ /**
+ * 鏄惁鍚堝苟鍗曞厓鏍�
+ *
+ * @return true || false
+ */
+ boolean merge() default true;
+
+ /**
+ * 鏄惁涓轰富閿紙鍗宠瀛楁鐩稿悓鐨勮鍚堝苟锛�
+ *
+ * @return true || false
+ */
+ boolean isPrimaryKey() default false;
+}
diff --git a/hangzhoumesParent/common/servicebase/src/main/java/com/mes/utils/excel/ExcelMergeStrategy.java b/hangzhoumesParent/common/servicebase/src/main/java/com/mes/utils/excel/ExcelMergeStrategy.java
new file mode 100644
index 0000000..dab4d9f
--- /dev/null
+++ b/hangzhoumesParent/common/servicebase/src/main/java/com/mes/utils/excel/ExcelMergeStrategy.java
@@ -0,0 +1,118 @@
+package com.mes.utils.excel;
+
+import com.alibaba.excel.annotation.ExcelProperty;
+import com.alibaba.excel.write.handler.RowWriteHandler;
+import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
+import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.util.CellRangeAddress;
+
+import java.lang.reflect.Field;
+import java.util.ArrayList;
+import java.util.List;
+
+/**
+ * @Author : zhoush
+ * @Date: 2025/5/12 16:55
+ * @Description:
+ */
+public class ExcelMergeStrategy implements RowWriteHandler {
+
+ /**
+ * 涓婚敭涓嬫爣
+ */
+ private Integer primaryKeyIndex;
+
+ /**
+ * 闇�瑕佸悎骞剁殑鍒楃殑涓嬫爣闆嗗悎
+ */
+ private final List<Integer> mergeColumnIndexList = new ArrayList<>();
+
+ /**
+ * 鏁版嵁绫诲瀷
+ */
+ private final Class<?> elementType;
+
+ public ExcelMergeStrategy(Class<?> elementType) {
+ this.elementType = elementType;
+ }
+
+ @Override
+ public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
+ // 鍒ゆ柇鏄惁涓烘爣棰�
+ if (isHead) {
+ return;
+ }
+ // 鑾峰彇褰撳墠宸ヤ綔琛�
+ Sheet sheet = writeSheetHolder.getSheet();
+ // 鍒濆鍖栦富閿笅鏍囧拰闇�瑕佸悎骞跺瓧娈电殑涓嬫爣
+ if (primaryKeyIndex == null) {
+ this.initPrimaryIndexAndMergeIndex(writeSheetHolder);
+ }
+ // 鍒ゆ柇鏄惁闇�瑕佸拰涓婁竴琛岃繘琛屽悎骞�
+ // 涓嶈兘鍜屾爣棰樺悎骞讹紝鍙兘鏁版嵁琛屼箣闂村悎骞�
+ if (row.getRowNum() <= 1) {
+ return;
+ }
+ // 鑾峰彇涓婁竴琛屾暟鎹�
+ Row lastRow = sheet.getRow(row.getRowNum() - 1);
+ // 灏嗘湰琛屽拰涓婁竴琛屾槸鍚屼竴绫诲瀷鐨勬暟鎹紙閫氳繃涓婚敭瀛楁杩涜鍒ゆ柇锛夛紝鍒欓渶瑕佸悎骞�
+ if (lastRow.getCell(primaryKeyIndex).getStringCellValue().equalsIgnoreCase(row.getCell(primaryKeyIndex).getStringCellValue())) {
+ for (Integer mergeIndex : mergeColumnIndexList) {
+ CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(), mergeIndex, mergeIndex);
+ sheet.addMergedRegionUnsafe(cellRangeAddress);
+ }
+ }
+ }
+
+ /**
+ * 鍒濆鍖栦富閿笅鏍囧拰闇�瑕佸悎骞跺瓧娈电殑涓嬫爣
+ *
+ * @param writeSheetHolder WriteSheetHolder
+ */
+ private void initPrimaryIndexAndMergeIndex(WriteSheetHolder writeSheetHolder) {
+ // 鑾峰彇褰撳墠宸ヤ綔琛�
+ Sheet sheet = writeSheetHolder.getSheet();
+ // 鑾峰彇鏍囬琛�
+ Row titleRow = sheet.getRow(0);
+ // 鑾峰彇鎵�鏈夊睘鎬у瓧娈�
+ Field[] fields = this.elementType.getDeclaredFields();
+ // 閬嶅巻鎵�鏈夊瓧娈�
+ for (Field field : fields) {
+ // 鑾峰彇@ExcelProperty娉ㄨВ锛岀敤浜庤幏鍙栬瀛楁瀵瑰簲鍒楃殑涓嬫爣
+ ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
+ // 鍒ゆ柇鏄惁涓虹┖
+ if (null == excelProperty) {
+ continue;
+ }
+ // 鑾峰彇鑷畾涔夋敞瑙ｏ紝鐢ㄤ簬鍚堝苟鍗曞厓鏍�
+ ExcelMerge excelMerge = field.getAnnotation(ExcelMerge.class);
+ // 鍒ゆ柇鏄惁闇�瑕佸悎骞�
+ if (null == excelMerge) {
+ continue;
+ }
+ for (int i = 0; i < fields.length; i++) {
+ Cell cell = titleRow.getCell(i);
+ if (null == cell) {
+ continue;
+ }
+ // 灏嗗瓧娈靛拰琛ㄥご鍖归厤涓�
+ if (excelProperty.value()[0].equalsIgnoreCase(cell.getStringCellValue())) {
+ if (excelMerge.isPrimaryKey()) {
+ primaryKeyIndex = i;
+ }
+ if (excelMerge.merge()) {
+ mergeColumnIndexList.add(i);
+ }
+ }
+ }
+ }
+
+ // 娌℃湁鎸囧畾涓婚敭锛屽垯寮傚父
+ if (null == this.primaryKeyIndex) {
+ throw new IllegalStateException("浣跨敤@ExcelMerge娉ㄨВ蹇呴』鎸囧畾涓婚敭");
+ }
+ }
+}
diff --git a/hangzhoumesParent/common/servicebase/src/main/java/com/mes/utils/excel/ExcelUtil.java b/hangzhoumesParent/common/servicebase/src/main/java/com/mes/utils/excel/ExcelUtil.java
new file mode 100644
index 0000000..b8559ef
--- /dev/null
+++ b/hangzhoumesParent/common/servicebase/src/main/java/com/mes/utils/excel/ExcelUtil.java
@@ -0,0 +1,19 @@
+package com.mes.utils.excel;
+
+import javax.servlet.http.HttpServletResponse;
+import java.io.UnsupportedEncodingException;
+import java.net.URLEncoder;
+
+/**
+ * @Author : zhoush
+ * @Date: 2025/5/12 17:03
+ * @Description:
+ */
+public class ExcelUtil {
+ public static void setExcelResponseProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
+ response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
+ response.setCharacterEncoding("utf-8");
+ String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+", "%20");
+ response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
+ }
+}
diff --git a/hangzhoumesParent/common/servicebase/src/main/resources/mapper/LargenScreenMapper.xml b/hangzhoumesParent/common/servicebase/src/main/resources/mapper/LargenScreenMapper.xml
index 14a179a..f505063 100644
--- a/hangzhoumesParent/common/servicebase/src/main/resources/mapper/LargenScreenMapper.xml
+++ b/hangzhoumesParent/common/servicebase/src/main/resources/mapper/LargenScreenMapper.xml
@@ -136,6 +136,167 @@
t6.product_date
order by t.date
</select>
+ <select id="exportDailyProduction" resultType="com.mes.largenscreen.entity.DailyProductionData">
+ with edg_storage_history_temp as (
+ select t.glass_id_in as glass_id, t.create_time, STR_TO_DATE(t.create_time, '%Y-%m-%d') as product_date
+ from edg_storage_device_task_history t
+ where t.task_type in
+ (1, 3)
+ and STR_TO_DATE(t.create_time, '%Y-%m-%d') BETWEEN #{beginDate} and #{endDate}
+ )
+ , big_storage_cage_history_temp as (
+ select t.glass_id, t.create_time, STR_TO_DATE(t.create_time, '%Y-%m-%d') as product_date
+ from big_storage_cage_history_task t
+ where t.task_type = 2
+ and STR_TO_DATE(t.create_time, '%Y-%m-%d') BETWEEN #{beginDate} and #{endDate}
+ )
+ , hollow_big_storage_cage_history_temp as (
+ select t.glass_id, t.create_time, STR_TO_DATE(t.create_time, '%Y-%m-%d') as product_date
+ from hollow_big_storage_cage_history_task t
+ where t.task_type = 5
+ and STR_TO_DATE(t.create_time, '%Y-%m-%d') BETWEEN #{beginDate} and #{endDate}
+ )
+ , engineer_temp as (
+ select STR_TO_DATE(create_time, '%Y-%m-%d') as product_date, engineer_id, glass_total, glass_total_area
+ from engineering
+ where STR_TO_DATE(create_time, '%Y-%m-%d') BETWEEN #{beginDate} and #{endDate}
+ )
+ , edg_temp as (
+ select t.product_date,
+ count(t.glass_id) as
+ count_out,
+ round(sum(t1.width * t1.height) / 1000000, 1) as total_area_out
+ from edg_storage_history_temp t
+ inner join glass_info t1 on t.glass_id = t1.glass_id
+ group by product_date
+ )
+ , edg_time_one as (
+ select product_date,
+ min(create_time) as edg_begin_time,
+ max(create_time) as edg_end_time,
+ TIMESTAMPDIFF(MINUTE, min(create_time), max(create_time)) AS edg_time_total
+ from edg_storage_history_temp
+ group by product_date
+ )
+ , edg_time_two as (
+ SELECT t1.product_date,
+ sum(TIMESTAMPDIFF(MINUTE, t1.create_time, t2.create_time)) AS edg_time_free
+ FROM (SELECT product_date,
+ create_time,
+ LEAD(create_time) OVER (ORDER BY create_time) AS next_timestamp
+ FROM edg_storage_history_temp
+ ) t1
+ JOIN
+ (SELECT create_time,
+ LEAD(create_time) OVER (ORDER BY create_time) AS next_timestamp
+ FROM edg_storage_history_temp) t2 ON t1.next_timestamp = t2.create_time
+ WHERE TIMESTAMPDIFF(MINUTE, t1.create_time, t2.create_time) between 6 and 500
+ group by t1.product_date
+ )
+ , big_storage_temp as (
+ select t.product_date,
+ count(distinct t1.engineer_id, t1.tempering_layout_id) as tempering_layout_count,
+ count(distinct t1.glass_id) as tempering_glass_count,
+ round(sum(t1.width * t1.height) / 1000000, 1) as tempering_area
+ from big_storage_cage_history_temp t
+ inner join glass_info t1
+ on t.glass_id = t1.glass_id
+ group by t.product_date
+ )
+ , big_storage_time_base as (
+ select distinct product_date, create_time from big_storage_cage_history_temp
+ )
+ , big_storage_time_one as (select product_date,
+ min(create_time) as big_begin_time,
+ max(create_time) as big_end_time,
+ TIMESTAMPDIFF(MINUTE, min(create_time), max(create_time)) AS big_time_total
+ from big_storage_time_base
+ group by product_date)
+ , big_storage_time_two as (
+ SELECT t1.product_date,
+ IFNULL(sum(TIMESTAMPDIFF(MINUTE, t1.create_time, t2.create_time)), 0) AS big_time_free
+ FROM (SELECT product_date,
+ create_time,
+ LEAD(create_time) OVER (ORDER BY create_time) AS next_timestamp
+ FROM big_storage_time_base) t1
+ JOIN
+ (SELECT create_time,
+ LEAD(create_time) OVER (ORDER BY create_time) AS next_timestamp
+ FROM big_storage_time_base) t2 ON t1.next_timestamp = t2.create_time
+ WHERE TIMESTAMPDIFF(MINUTE, t1.create_time, t2.create_time) between 11 and 500
+ group by t1.product_date
+ )
+ , hollow_big_storage_temp as (
+ select t.product_date,
+ count(distinct t1.glass_id) as hollow_glass_count,
+ round(sum(t1.width * t1.height) / 1000000, 1) as hollow_area
+ from hollow_big_storage_cage_history_temp t
+ inner join glass_info t1
+ on t.glass_id = t1.glass_id
+ group by t.product_date
+ )
+ , hollow_big_storage_time_base as (
+ select distinct product_date, create_time from hollow_big_storage_cage_history_temp
+ )
+ , hollow_big_storage_time_one as (select product_date,
+ min(create_time) as hollow_begin_time,
+ max(create_time) as hollow_end_time,
+ TIMESTAMPDIFF(MINUTE, min(create_time), max(create_time)) AS hollow_time_total
+ from hollow_big_storage_time_base
+ group by product_date)
+ , hollow_big_storage_time_two as (
+ SELECT t1.product_date,
+ IFNULL(sum(TIMESTAMPDIFF(MINUTE, t1.create_time, t2.create_time)), 0) AS hollow_time_free
+ FROM (SELECT product_date,
+ create_time,
+ LEAD(create_time) OVER (ORDER BY create_time) AS next_timestamp
+ FROM hollow_big_storage_time_base) t1
+ JOIN
+ (SELECT create_time,
+ LEAD(create_time) OVER (ORDER BY create_time) AS next_timestamp
+ FROM hollow_big_storage_time_base) t2 ON t1.next_timestamp = t2.create_time
+ WHERE TIMESTAMPDIFF(MINUTE, t1.create_time, t2.create_time) between 11 and 500
+ group by t1.product_date
+ )
+ , result as (
+ select t.count_out,
+ t.total_area_out,
+ t1.*,
+ t2.edg_time_free,
+ t1.edg_time_total - t2.edg_time_free as edg_time_diff,
+ t3.tempering_layout_count,
+ t3.tempering_glass_count,
+ t3.tempering_area,
+ t4.big_begin_time,
+ t4.big_end_time,
+ t4.big_time_total,
+ t5.big_time_free,
+ t4.big_time_total - t5.big_time_free as big_time_diff,
+ t6.hollow_glass_count,
+ t6.hollow_area,
+ t7.hollow_begin_time,
+ t7.hollow_end_time,
+ t7.hollow_time_total,
+ t8.hollow_time_free,
+ t7.hollow_time_total - t8.hollow_time_free as hollow_time_diff,
+ t9.engineer_id,
+ t9.glass_total,
+ t9.glass_total_area
+ from edg_temp t
+ LEFT JOIN edg_time_one t1 on t.product_date = t1.product_date
+ LEFT JOIN edg_time_two t2 on t.product_date = t2.product_date
+ LEFT JOIN big_storage_temp t3 on t.product_date = t3.product_date
+ LEFT JOIN big_storage_time_one t4 on t.product_date = t4.product_date
+ LEFT JOIN big_storage_time_two t5 on t.product_date = t5.product_date
+ LEFT JOIN hollow_big_storage_temp t6 on t.product_date = t6.product_date
+ LEFT JOIN hollow_big_storage_time_one t7 on t.product_date = t7.product_date
+ LEFT JOIN hollow_big_storage_time_two t8 on t.product_date = t8.product_date
+ LEFT JOIN engineer_temp t9 on t.product_date = t9.product_date
+ )
+ select *
+ from result
+ order by product_date
+ </select>
</mapper>
\ No newline at end of file
--
Gitblit v1.8.0