From dfcc9f5693c44fcc52a9b0b261ae4dce69bbdb8c Mon Sep 17 00:00:00 2001
From: 6c <420680616@qq.com>
Date: 星期五, 19 九月 2025 10:50:12 +0800
Subject: [PATCH] 请求历史特殊处理-若是多次维修导致报表库数据重复，那么按照主键取最新数据
---
product-server-data-center/src/main/java/com/product/data/center/service/ProductionRealTimeInfoService.java | 248 +++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 245 insertions(+), 3 deletions(-)
diff --git a/product-server-data-center/src/main/java/com/product/data/center/service/ProductionRealTimeInfoService.java b/product-server-data-center/src/main/java/com/product/data/center/service/ProductionRealTimeInfoService.java
index 88ead0a..015c95b 100644
--- a/product-server-data-center/src/main/java/com/product/data/center/service/ProductionRealTimeInfoService.java
+++ b/product-server-data-center/src/main/java/com/product/data/center/service/ProductionRealTimeInfoService.java
@@ -15,6 +15,7 @@
import com.product.core.service.support.AbstractBaseService;
import com.product.data.center.config.ErrorCode;
import com.product.data.center.entity.ManufacturingOrderEntity;
+import com.product.data.center.entity.ProductDetailEntity;
import com.product.data.center.entity.ProductionDetailEntity;
import com.product.data.center.utils.QuerySqlParseUtil;
import com.product.datasource.dao.Dao;
@@ -53,6 +54,7 @@
String fileName = URLEncoder.encode("鍒朵护鍗曠敓浜т俊鎭�" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx", "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
List<ManufacturingOrderEntity> manufacturingOrderEntityList = new ArrayList<>();
+ int page=1;
for (int i = 0; i < manufacturingCommandSheet.getRows(); i++) {
FieldSetEntity fieldSetEntity = manufacturingCommandSheet.getFieldSetEntity(i);
Map<Object, Object> values = fieldSetEntity.getValues();
@@ -74,7 +76,6 @@
// writeSheet = EasyExcel.writerSheet(3, "鍒朵护鍗曚俊鎭�").build();
// excelWriter.write(manufacturingOrderEntityList, writeSheet);
String process = fse.getString("process");
-
String[] moNumbers = manufacturingCommandSheet.getData().stream().map(item -> item.getString("mo_number")).toArray(String[]::new);
for (int i = 1; i <= moNumbers.length; i++) {
String moNumber = moNumbers[i - 1];
@@ -86,8 +87,9 @@
if (DataTableEntity.isEmpty(processSheet)) {
continue;
}
- fileName = moNumber + "鐨勭敓浜ф槑缁�";
- writeSheet = EasyExcel.writerSheet(i + 1, fileName).head(ProductionDetailEntity.class).build();
+ page++;
+ fileName = moNumber + "鐢熶骇鏄庣粏";
+ writeSheet = EasyExcel.writerSheet(page, fileName).head(ProductionDetailEntity.class).build();
List<ProductionDetailEntity> productionDetailEntities = new ArrayList<>();
for (int k = 0; k < processSheet.getRows(); k++) {
JSONObject jsonObject = new JSONObject((Map) processSheet.getFieldSetEntity(k).getValues());
@@ -95,6 +97,24 @@
productionDetailEntities.add(productionDetail);
}
excelWriter.write(productionDetailEntities, writeSheet);
+ //2025-09-03鏀� 澧炲姞杩囨护淇℃伅-------------------------------
+
+ //鏌ュ嚭璁㈠崟鐨勬墍鏈夎繃绔欐槑缁�
+ DataTableEntity detailSheet = getProductDetailSheet(ff);
+ if (DataTableEntity.isEmpty(detailSheet)) {
+ continue;
+ }
+ page++;
+ fileName = moNumber + "杩囩珯鏄庣粏";
+ writeSheet = EasyExcel.writerSheet(page, fileName).head(ProductDetailEntity.class).build();
+ List<ProductDetailEntity> productDetailEntity = new ArrayList<>();
+ for (int k = 0; k < detailSheet.getRows(); k++) {
+ JSONObject jsonObject = new JSONObject((Map) detailSheet.getFieldSetEntity(k).getValues());
+ ProductDetailEntity productDetail = jsonObject.toJavaObject(ProductDetailEntity.class);
+ productDetailEntity.add(productDetail);
+ }
+ excelWriter.write(productDetailEntity, writeSheet);
+ //2025-09-03鏀�-------------------------------------------
}
} catch (Exception e) {
throw e;
@@ -269,7 +289,229 @@
return dataTableEntity;
}
+ /**
+ * 2025-09-03鏀�
+ * 鐢熶骇瀹炴椂鏁版嵁锛屾煡璇㈠鍑哄鍔犺繃绔欎俊鎭�
+ * @param fse
+ * @return
+ * @throws BaseException
+ */
+ public DataTableEntity getProductDetailSheet(FieldSetEntity fse) throws BaseException {
+ String trackingTable = "da_t_wip_detail";
+ String moNumber = fse.getString("mo_number");
+ StringBuilder sql = new StringBuilder(64);
+ sql.append("select number_type from [={" + trackingTable + "}{number_type,mo_number}{ where mo_number='" + moNumber + "' and number_type!=0}=] ");
+ String parseSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString());
+ FieldSetEntity fs = getBaseDao().getFieldSetEntityBySQL(parseSql, new Object[]{}, false);
+ if (fs != null) {
+ List<Object> params = new ArrayList<>();
+ Integer numberType = NumberUtil.isNumber(fs.getString("number_type")) ? NumberUtil.parseInt(fs.getString("number_type")) : 0;
+ if (numberType == null || numberType < 1) {
+ return null;
+ }
+ sql.setLength(0);
+
+ String scrap = "鎶ュ簾";
+ String lend = "鍊熷嚭";
+ sql.append("\n select t.serial_number,t.in_station_time, ");
+ sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
+ sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
+ sql.append("\n t.error_flag, ");
+ sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
+ sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
+ sql.append("\n from [={" + trackingTable + " d1}{serial_number,in_station_time,group_name,error_flag,mo_number,item_count,repair,container_sn,wait_scrap_qty,depanel_flag");
+ sql.append(",emp_no,number_type }");
+ sql.append("{ WHERE number_type='" + numberType + "' and mo_number='" + moNumber + "' }");
+ sql.append("=] t ");
+ sql.append("\n left join ");
+ sql.append("\n da_t_co_emp_desc e ");
+ sql.append("\n on t.emp_no=e.emp_no ");
+ sql.append("\n union all ");
+ sql.append("\n select t.serial_number,t.in_station_time, ");
+ sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
+ sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
+ sql.append("\n t.error_flag, ");
+ sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
+ sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
+ sql.append("\n from [={" + trackingTable + " d2}{serial_number,in_station_time,group_name,error_flag,mo_number,item_count,repair,container_sn,wait_scrap_qty,depanel_flag");
+ sql.append(",emp_no,number_type }{ where number_type='" + numberType + "' and mo_number='" + moNumber + "'");
+ sql.append(" and error_flag='1'}");
+ sql.append("=] t");
+ sql.append("\n left join ");
+ sql.append("\n da_t_co_emp_desc e ");
+ sql.append("\n on t.emp_no=e.emp_no ");
+ sql.append("\n order by error_flag asc,in_station_time asc ");
+ params.add(scrap);
+ params.add(lend);
+ params.add(scrap);
+ params.add(lend);
+ System.out.println(sql);
+ String parseSql1 = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString(), 2);
+ return getBaseDao().listTable(parseSql1, params.toArray());
+ }
+ return null;
+ }
+ /**
+ * 2025-09-02鏀�
+ * 鐢熶骇瀹炴椂鏁版嵁锛屽洜da_t_wip_tracking琛╠isplay_group='N/A',鏌ヤ笉鍑烘暟鎹�
+ * 鏀逛负鏌� da_t_wip_detail琛� 鏇存敼浜哾isplay_group->group_name瀛楁锛屽幓鎺塺outeendflage 鍜宺outeendflage鏉′欢
+ * @param fse
+ * @return
+ * @throws BaseException
+ */
+ public DataTableEntity getProductSheetV2(FieldSetEntity fse) throws BaseException {
+ String trackingTable = "da_t_wip_detail";
+ String groupNext = fse.getString("group_next");
+ String moNumber = fse.getString("mo_number");
+ StringBuilder sql = new StringBuilder(64);
+ sql.append("select number_type from [={" + trackingTable + "}{number_type,mo_number}{ where mo_number='" + moNumber + "' and number_type!=0}=] ");
+ String parseSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString());
+ FieldSetEntity fs = getBaseDao().getFieldSetEntityBySQL(parseSql, new Object[]{}, false);
+ if (fs != null) {
+ List<Object> params = new ArrayList<>();
+
+ Integer numberType = NumberUtil.isNumber(fs.getString("number_type")) ? NumberUtil.parseInt(fs.getString("number_type")) : 0;
+ if (numberType == null || numberType < 1) {
+ return null;
+ }
+ sql.setLength(0);
+
+ String scrap = "鎶ュ簾";
+ String lend = "鍊熷嚭";
+ if (1 == numberType && !"娴佺▼缁撴潫".equals(groupNext)) {
+ sql.append("\n select t.lot_number,t.in_station_time,t.serial_number, ");
+ sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
+ sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
+ sql.append("\n t.error_flag, ");
+ sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
+ sql.append("\n (select sum(count) from [={da_t_pm_product_sn}{COUNT(1) count,product_sn }{GROUP BY product_sn}=] where product_sn=t.serial_number) product_count, ");
+ sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
+ sql.append("\n from [={" + trackingTable + " d1}{emp_no,number_type,mo_number,item_count,depanel_flag,wait_scrap_qty");
+ sql.append("lot_number,in_station_time,serial_number,group_name,error_flag,repair,container_sn,in_station_time");
+ sql.append("}{where mo_number='" + moNumber + "' and t.number_type='" + numberType + "' and GROUP_NAME='" + groupNext + "' and t.item_count!=0 }=] t ");
+ sql.append("\n left join da_t_co_emp_desc e ");
+ sql.append("\n on t.emp_no=e.emp_no ");
+// sql.append("\n where t.number_type=? and t.mo_number=? and t.GROUP_NAME=? ");
+// sql.append("\n and t.item_count!=0 ");
+ sql.append("\n union all ");
+ sql.append("\n select t.lot_number,t.in_station_time,t.serial_number, ");
+ sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
+ sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
+ sql.append("\n t.error_flag, ");
+ sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
+ sql.append("\n (select sum(count) from [={da_t_pm_product_sn}{COUNT(1) count,product_sn }{GROUP BY product_sn}=] where product_sn=t.serial_number) product_count, ");
+ sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
+ sql.append("\n from [={" + trackingTable + " d2}{");
+ sql.append("lot_number,in_station_time,serial_number,group_name,error_flag,repair,container_sn");
+ sql.append(",number_type,mo_number,item_count,in_station_time,depanel_flag,wait_scrap_qty,serial_number,emp_name}");
+ sql.append("{where number_type='" + numberType + "' and mo_number='" + moNumber + "' and group_name='" + groupNext + "' ");
+ sql.append(" and item_count!=0 and error_flag='1'} ");
+ sql.append("=] t ");
+ sql.append("\n left join da_t_co_emp_desc e ");
+ sql.append("\n on t.emp_no=e.emp_no ");
+ sql.append("\n order by error_flag asc,in_station_time asc ");
+ } else if (2 == numberType && "娴佺▼缁撴潫".equals(groupNext)) {
+ sql.append("\n select t.lot_number,t.in_station_time,t.serial_number, ");
+ sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
+ sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
+ sql.append("\n t.error_flag, ");
+ sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
+ sql.append("\n ifnull(c.product_count,0) product_count, ");
+ sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
+ sql.append("\n from [={" + trackingTable + "}");
+ sql.append(" {lot_number,in_station_time,serial_number,group_name,error_flag,mo_number,item_count,repair");
+ sql.append(",container_sn,serial_number,wait_scrap_qty,depanel_flag,number_type,emp_no}");
+
+ sql.append("\n {where number_type='" + numberType + "' and mo_number='" + moNumber + "' ");
+ sql.append("}=] t ");
+ sql.append("\n left join ");
+ sql.append("\n da_t_co_emp_desc e ");
+ sql.append("\n on t.emp_no=e.emp_no ");
+ sql.append("\n LEFT JOIN ");
+ sql.append("\n(SELECT SUM(COUNT) product_count,product_sn FROM [={da_t_pm_product_sn}{COUNT(1) count,product_sn}{WHERE product_sn in (select serial_number from " + trackingTable + ") group by product_sn }=] group by product_sn ) C ");
+ sql.append("\n ON t.serial_number=c.product_sn ");
+ sql.append("\n order by error_flag asc,in_station_time asc ");
+
+ } else if (2 == numberType && !"娴佺▼缁撴潫".equals(groupNext)) {
+ sql.append("\n select t.serial_number,t.in_station_time, ");
+ sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
+ sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
+ sql.append("\n t.error_flag, ");
+ sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
+ sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
+ sql.append("\n from [={" + trackingTable + " d1}{serial_number,in_station_time,group_name,error_flag,mo_number,item_count,repair,container_sn,wait_scrap_qty,depanel_flag");
+ sql.append(",emp_no,number_type }");
+ sql.append("{ WHERE number_type='" + numberType + "' and GROUP_NAME='" + groupNext + "' and mo_number='" + moNumber + "' }");
+ sql.append("=] t ");
+ sql.append("\n left join ");
+ sql.append("\n da_t_co_emp_desc e ");
+ sql.append("\n on t.emp_no=e.emp_no ");
+// sql.append("\n where t.number_type=? ");
+// sql.append("\n and t.mo_number=? and t.GROUP_NAME=? ");
+// sql.append("\n ");
+ sql.append("\n union all ");
+ sql.append("\n select t.serial_number,t.in_station_time, ");
+ sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
+ sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
+ sql.append("\n t.error_flag, ");
+ sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
+ sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
+ sql.append("\n from [={" + trackingTable + " d2}{serial_number,in_station_time,group_name,error_flag,mo_number,item_count,repair,container_sn,wait_scrap_qty,depanel_flag");
+ sql.append(",emp_no,number_type }{ where number_type='" + numberType + "' and mo_number='" + moNumber + "' and group_name='" + groupNext + "'");
+ sql.append(" and error_flag='1'}");
+ sql.append("=] t");
+ sql.append("\n left join ");
+ sql.append("\n da_t_co_emp_desc e ");
+ sql.append("\n on t.emp_no=e.emp_no ");
+// sql.append("\n where t.number_type=? ");
+// sql.append("\n and t.mo_number=? and t.group_name=? ");
+// sql.append("\n and error_flag='1' ");
+ sql.append("\n order by error_flag asc,in_station_time asc ");
+
+ } else if (1 == numberType && "娴佺▼缁撴潫".equals(groupNext)) {
+ sql.append("\n select t.serial_number,t.in_station_time, ");
+ sql.append("\n (case t.group_name when 'n/a' then ' ' else ");
+ sql.append("\n (case t.error_flag when '0' then 'ok' when '1' then 'ng' when '3' then ? when '4' then ? else 'ng' end ) end) error_flag_text, ");
+ sql.append("\n t.error_flag, ");
+ sql.append("\n t.mo_number,t.group_name,t.item_count,t.repair,e.emp_name,t.container_sn, ");
+ sql.append("\n t.wait_scrap_qty,t.depanel_flag ");
+ sql.append("\n from [={" + trackingTable + "}");
+ sql.append("{serial_number,in_station_time,group_name,error_flag,mo_number,item_count,repair,emp_name,container_sn,");
+ sql.append("wait_scrap_qty,depanel_flag,emp_no,number_type}{where number_type='" + numberType + "' and mo_number='" + moNumber + "' }");
+ sql.append("=] t ");
+ sql.append("\n left join ");
+ sql.append("\n da_t_co_emp_desc e on t.emp_no=e.emp_no ");
+// sql.append("\n where t.number_type=? ");
+// sql.append("\n and t.mo_number=? ");
+ sql.append("\n order by error_flag asc,in_station_time asc ");
+
+ } else {
+ return null;
+ }
+ if (!"娴佺▼缁撴潫".equals(groupNext)) {
+ params.add(scrap);
+ params.add(lend);
+// params.add(numberType);
+// params.add(moNumber);
+// params.add(groupNext);
+ params.add(scrap);
+ params.add(lend);
+// params.add(numberType);
+// params.add(moNumber);
+// params.add(groupNext);
+ } else {
+ params.add(scrap);
+ params.add(lend);
+// params.add(moNumber);
+// params.add(numberType);
+ }
+ System.out.println(sql);
+ String parseSql1 = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString(), 2);
+ return getBaseDao().listTable(parseSql1, params.toArray());
+ }
+ return null;
+ }
public DataTableEntity getProductSheet(FieldSetEntity fse) throws BaseException {
String trackingTable = "da_t_wip_tracking";
String groupNext = fse.getString("group_next");
--
Gitblit v1.9.2