From a7b18ead641993787f29cfb3c143a39ef3e60b5e Mon Sep 17 00:00:00 2001
From: 许鹏程 <1821349743@qq.com>
Date: 星期一, 23 十月 2023 18:49:54 +0800
Subject: [PATCH] commit
---
product-server-data-center/src/main/java/com/product/data/center/service/TraceToSourceService.java | 255 ++++++++++++++++++++++++--------------------------
1 files changed, 123 insertions(+), 132 deletions(-)
diff --git a/product-server-data-center/src/main/java/com/product/data/center/service/TraceToSourceService.java b/product-server-data-center/src/main/java/com/product/data/center/service/TraceToSourceService.java
index deb321b..ec70e7f 100644
--- a/product-server-data-center/src/main/java/com/product/data/center/service/TraceToSourceService.java
+++ b/product-server-data-center/src/main/java/com/product/data/center/service/TraceToSourceService.java
@@ -35,49 +35,46 @@
/**
- * SN杩芥函鏌ヨ
- * @author du
+ * SN杩芥函鏌ヨ
*
+ * @author du
*/
@Service
-public class TraceToSourceService extends AbstractBaseService{
+public class TraceToSourceService extends AbstractBaseService {
@Autowired
BaseDao baseDao;
/**
- * 鐗╂枡SN鍙嶈拷婧巻鍙叉煡璇�
- * @param querySNSN 涓讳欢SN
+ * 鐗╂枡SN鍙嶈拷婧巻鍙叉煡璇�
+ *
+ * @param querySNSN 涓讳欢SN
* @param isBakTable 鏄惁BAK琛�
* @return
*/
public JSONObject antitraceReport(String querySN, String isBakTable) {
- JSONObject jsonInfo=new JSONObject();
+ JSONObject jsonInfo = new JSONObject();
//鏌ヨ鍏抽敭浠讹紝鑾峰彇鏈哄彿SN
- String currentSql = "";
- if ("1".equals(isBakTable)) {
- currentSql = "SELECT DISTINCT pk_product_sn FROM [={da_t_wip_product_keyp_bak}{}{WHERE pk_keyp_sn = '"+querySN+"' }=]";
- }else {
- currentSql = "SELECT DISTINCT pk_product_sn FROM [={da_t_wip_product_keyp}{}{WHERE pk_keyp_sn = '"+querySN+"' }=]";
- }
-
+ String currentSql = "SELECT DISTINCT pk_product_sn FROM [={da_t_wip_product_keyp}{}{WHERE pk_keyp_sn = '" + querySN + "' }=]";
+
String parseSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), currentSql);
- FieldSetEntity fse=baseDao.getFieldSetBySQL(parseSql, new Object[] {}, false);
- if (fse!=null) {
- jsonInfo=traceReport(fse.getString(CmnConst.PK_PRODUCT_SN), isBakTable);
+ FieldSetEntity fse = baseDao.getFieldSetBySQL(parseSql, new Object[]{}, false);
+ if (fse != null) {
+ jsonInfo = traceReport(fse.getString(CmnConst.PK_PRODUCT_SN), isBakTable);
}
return jsonInfo;
}
/**
- * SN杩芥函鍘嗗彶鏌ヨ
- * @param 鏈哄櫒SN 鏈哄櫒搴忓垪鍙�
+ * SN杩芥函鍘嗗彶鏌ヨ
+ *
+ * @param 鏈哄櫒SN 鏈哄櫒搴忓垪鍙�
* @param isBakTable 鏄惁BAK琛�
* @return
*/
public JSONObject traceReport(String querySN, String isBakTable) {
- JSONObject jsonInfo=new JSONObject();
+ JSONObject jsonInfo = new JSONObject();
jsonInfo.set("orderInfo", BaseUtil.fieldSetEntityToJson(getManufacturingOrderInformation(querySN, isBakTable)));
jsonInfo.set("materialInfo", BaseUtil.dataTableEntityToJson(getWorkCenterOperationInfomation(querySN, isBakTable)));
@@ -89,7 +86,8 @@
}
/**
- * 鑾峰彇鍒朵护鍗曚俊鎭�
+ * 鑾峰彇鍒朵护鍗曚俊鎭�
+ *
* @param querySN
* @param isBakTable 鏄惁BAK琛�
* @return
@@ -98,33 +96,29 @@
//璁㈠崟淇℃伅
StringBuilder sb1 = new StringBuilder(1024);
sb1.append("SELECT ")
- .append(" t.mo_number,t.model_name,t.area_name,t.container_sn, ")
- .append(" t.next_station,t.station_name,t.display_group, ")
- .append(" t.old_begin_time,t.old_end_time,w.pi_sn, ")
- .append(" DATE_FORMAT(p.mo_start_date, '%Y-%m-%d %H:%i:%s') mo_start_date, ")
- .append(" DATE_FORMAT(t.in_station_time, '%Y-%m-%d %H:%i:%s') in_station_time, ")
- .append(" (CASE t.group_name WHEN 'N/A' THEN t.next_station ELSE t.group_name END) group_name, ")
- .append(" (CASE t.error_flag WHEN '0' THEN '姝ｅ父' WHEN '1' THEN '涓嶈壇' WHEN '3' THEN '鎶ュ簾' ELSE 'N/A' END) error_flag ")
- .append("")
- .append(" FROM [=da_t_pm_mo_base=] p,[={");
- if ("1".equals(isBakTable)) {
- sb1.append("da_t_wip_tracking_bak");
- }else {
- sb1.append("da_t_wip_tracking");
- }
-
+ .append(" t.mo_number,t.model_name,t.area_name,t.container_sn, ")
+ .append(" t.next_station,t.station_name,t.display_group, ")
+ .append(" t.old_begin_time,t.old_end_time,w.pi_sn, ")
+ .append(" DATE_FORMAT(p.mo_start_date, '%Y-%m-%d %H:%i:%s') mo_start_date, ")
+ .append(" DATE_FORMAT(t.in_station_time, '%Y-%m-%d %H:%i:%s') in_station_time, ")
+ .append(" (CASE t.group_name WHEN 'N/A' THEN t.next_station ELSE t.group_name END) group_name, ")
+ .append(" (CASE t.error_flag WHEN '0' THEN '姝ｅ父' WHEN '1' THEN '涓嶈壇' WHEN '3' THEN '鎶ュ簾' ELSE 'N/A' END) error_flag ")
+ .append("")
+ .append(" FROM [=da_t_pm_mo_base=] p,[={");
+ sb1.append("da_t_wip_tracking");
+
sb1.append("}{}{WHERE serial_number = '")
- .append(querySN)
- .append("'}=] t ")
- .append(" LEFT JOIN [={da_t_wip_packaging_infor}{}{WHERE pi_product_sn='")
- .append(querySN)
- .append("'}=] w ON t.serial_number = w.pi_product_sn ")
- .append(" WHERE p.mo_number = t.mo_number AND t.serial_number = ?");
+ .append(querySN)
+ .append("'}=] t ")
+ .append(" LEFT JOIN [={da_t_wip_packaging_infor}{}{WHERE pi_product_sn='")
+ .append(querySN)
+ .append("'}=] w ON t.serial_number = w.pi_product_sn ")
+ .append(" WHERE p.mo_number = t.mo_number AND t.serial_number = ?");
//鎶ヨ〃瑙ｆ瀽SQL锛岀敓鎴怶ITH
String finalSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sb1.toString());
- FieldSetEntity orderInfo=baseDao.getFieldSetEntityBySQL(finalSql, new Object[] {querySN} , false);
- if (orderInfo!=null) {
+ FieldSetEntity orderInfo = baseDao.getFieldSetEntityBySQL(finalSql, new Object[]{querySN}, false);
+ if (orderInfo != null) {
orderInfo.setValue("outPack", this.getOutermostContainer(querySN));
}
return orderInfo;
@@ -132,7 +126,8 @@
/**
- * 鑾峰彇鏈�澶栧眰瀹瑰櫒SN
+ * 鑾峰彇鏈�澶栧眰瀹瑰櫒SN
+ *
* @param querySN
* @return
*/
@@ -140,12 +135,12 @@
//鍗曠嫭鏌ヨ鍖呰淇℃伅
StringBuilder sb = new StringBuilder();
sb.append("SELECT pi_sn,pi_pack_level,pi_child_sn")
- .append(" FROM [={da_t_wip_packaging_infor}{pi_sn,ROUND(pi_pack_level)pi_pack_level,pi_child_sn}{WHERE pi_product_sn='")
- .append(querySN)
- .append("'}=] ");
+ .append(" FROM [={da_t_wip_packaging_infor}{pi_sn,ROUND(pi_pack_level)pi_pack_level,pi_child_sn}{WHERE pi_product_sn='")
+ .append(querySN)
+ .append("'}=] ");
// .append(" WHERE pi_product_sn=? ");
String finalSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sb.toString());
- DataTableEntity dtPackingInfo=baseDao.listTable(finalSql, new Object[] {});
+ DataTableEntity dtPackingInfo = baseDao.listTable(finalSql, new Object[]{});
if (!BaseUtil.dataTableIsEmpty(dtPackingInfo)) {
//鐩爣SN闆嗗悎锛堢储寮曠瓑浜庢繁搴︼紝绱㈠紩瀵瑰簲鍊肩瓑浜庡悓娣卞害鐨勪换鎰忎竴涓猄N锛屽嵆绱㈠紩鏈�澶х殑SN涓虹洰鏍嘢N锛�
@@ -153,18 +148,18 @@
for (int i = 0; i < dtPackingInfo.getRows(); i++) {
FieldSetEntity fseBaseData = dtPackingInfo.getFieldSetEntity(i);
//cheng 2022骞�12鏈�29鏃�14:16:52
- int level= NumberUtil.parseInt(fseBaseData.getString("pi_pack_level"));
- String ID=fseBaseData.getString("pi_sn");
- while (listLevel.size()<level) {
+ int level = NumberUtil.parseInt(fseBaseData.getString("pi_pack_level"));
+ String ID = fseBaseData.getString("pi_sn");
+ while (listLevel.size() < level) {
listLevel.add(new HashMap<>());
}
- listLevel.get(level-1).put(ID, fseBaseData);
+ listLevel.get(level - 1).put(ID, fseBaseData);
}
//鍙屽惊鐜亶鍘嗗幓鎺夐潪涓婁笅绾х殑鍖呰鏁版嵁
for (int i = 1; i < listLevel.size(); i++) {
- Map<String, FieldSetEntity> mapPreCommonLevel=listLevel.get(i-1);
- Map<String, FieldSetEntity> mapCommonLevel=listLevel.get(i);
+ Map<String, FieldSetEntity> mapPreCommonLevel = listLevel.get(i - 1);
+ Map<String, FieldSetEntity> mapCommonLevel = listLevel.get(i);
for (Map.Entry<String, FieldSetEntity> entry : mapCommonLevel.entrySet()) {
String key = entry.getKey();
FieldSetEntity val = entry.getValue();
@@ -173,14 +168,14 @@
}
}
if (mapCommonLevel.isEmpty()) {
- listLevel.remove(listLevel.subList(i, listLevel.size()-1));
+ listLevel.remove(listLevel.subList(i, listLevel.size() - 1));
}
}
//杩斿洖鏈�澶栧眰鍖呰鏉＄爜
- for (Map.Entry<String, FieldSetEntity> entry : listLevel.get(listLevel.size()-1).entrySet()) {
- String key=entry.getKey();
- if (key!=null) {
+ for (Map.Entry<String, FieldSetEntity> entry : listLevel.get(listLevel.size() - 1).entrySet()) {
+ String key = entry.getKey();
+ if (key != null) {
return key;
}
}
@@ -190,7 +185,8 @@
/**
- * 鑾峰彇宸ヤ綔涓績鐗╂枡浣滀笟淇℃伅
+ * 鑾峰彇宸ヤ綔涓績鐗╂枡浣滀笟淇℃伅
+ *
* @param querySN
* @param isBakTable 鏄惁BAK琛�
* @return
@@ -199,33 +195,30 @@
//鐗╂枡浣滀笟淇℃伅
StringBuilder sb2 = new StringBuilder(1024);
sb2.append("SELECT ")
- .append(" DISTINCT pk_station_name AS stationName, ")
- .append(" l.pk_product_lot AS lot_number,l.pk_item_code AS keyPartNo, ")
- .append(" c.co_item_name AS assistantToolName,c.co_item_spec AS itemSpec, ")
- .append(" pk_keyp_sn AS itemSN,pk_keyp_sn_qty AS itemCount, ")
- .append(" DATE_FORMAT(pk_loadtime,'%Y-%m-%d %H:%i:%s') AS loadTime, ")
- .append(" (SELECT d.emp_name FROM da_t_co_emp_desc d WHERE d.emp_no=l.pk_empno)empName, ")
- .append(" (CASE 0 WHEN 0 THEN '涓婃枡' WHEN '1' THEN '涓嬫枡' WHEN '2' THEN '鎹㈡枡' WHEN '3' THEN '寮傚父鎹㈡枡' ELSE '' END) actionType ")
- .append(" FROM [={");
- if ("1".equals(isBakTable)) {
- sb2.append("da_t_wip_product_keyp_bak");
- }else {
- sb2.append("da_t_wip_product_keyp");
- }
+ .append(" DISTINCT pk_station_name AS stationName, ")
+ .append(" l.pk_product_lot AS lot_number,l.pk_item_code AS keyPartNo, ")
+ .append(" c.co_item_name AS assistantToolName,c.co_item_spec AS itemSpec, ")
+ .append(" pk_keyp_sn AS itemSN,pk_keyp_sn_qty AS itemCount, ")
+ .append(" DATE_FORMAT(pk_loadtime,'%Y-%m-%d %H:%i:%s') AS loadTime, ")
+ .append(" (SELECT d.emp_name FROM da_t_co_emp_desc d WHERE d.emp_no=l.pk_empno)empName, ")
+ .append(" (CASE 0 WHEN 0 THEN '涓婃枡' WHEN '1' THEN '涓嬫枡' WHEN '2' THEN '鎹㈡枡' WHEN '3' THEN '寮傚父鎹㈡枡' ELSE '' END) actionType ")
+ .append(" FROM [={");
+ sb2.append("da_t_wip_product_keyp");
sb2.append("}{}{ WHERE pk_replace_flag='N' AND pk_product_sn='")
- .append(querySN)
- .append("'}=] l ")
- .append(" LEFT JOIN [=da_t_co_item=] c ON c.co_item_code=l.pk_item_code ")
- .append(" WHERE l.pk_replace_flag='N' AND l.pk_product_sn=? ")
- .append(" ORDER BY loadtime ");
+ .append(querySN)
+ .append("'}=] l ")
+ .append(" LEFT JOIN [=da_t_co_item=] c ON c.co_item_code=l.pk_item_code ")
+ .append(" WHERE l.pk_replace_flag='N' AND l.pk_product_sn=? ")
+ .append(" ORDER BY loadtime ");
//鎶ヨ〃瑙ｆ瀽SQL锛岀敓鎴怶ITH
String finalSql2 = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sb2.toString());
- DataTableEntity materialInfo=baseDao.listTable(finalSql2, new Object[] {querySN});
+ DataTableEntity materialInfo = baseDao.listTable(finalSql2, new Object[]{querySN});
return materialInfo;
}
/**
- * 鑾峰彇鍘嗗彶杩囩珯淇℃伅
+ * 鑾峰彇鍘嗗彶杩囩珯淇℃伅
+ *
* @param querySN
* @param isBakTable 鏄惁BAK琛�
* @return
@@ -234,41 +227,38 @@
//杩囩珯淇℃伅
StringBuilder sb3 = new StringBuilder();
sb3.append(" SELECT ")
- .append(" t.mo_number,t.lot_number,t.model_name,t.area_name, ")
- .append(" t.group_name,t.next_station,t.station_name, ")
- .append(" t.customer_no,t.emp_no,c.emp_name,t.item_count,t.serial_number, ")
- .append(" DATE_FORMAT(t.in_station_time, '%Y-%m-%d %H:%i:%s') AS in_station_time, ")
- .append(" (CASE t.error_flag WHEN '0' THEN '姝ｅ父' WHEN '1' THEN '涓嶈壇' WHEN '3' THEN '鎶ュ簾' ELSE 'N/A' END) error_flag ")
- .append(" FROM [={");
- if ("1".equals(isBakTable)) {
- sb3.append("da_t_wip_detail_bak");
- }else {
- sb3.append("da_t_wip_detail");
- }
+ .append(" t.mo_number,t.lot_number,t.model_name,t.area_name, ")
+ .append(" t.group_name,t.next_station,t.station_name, ")
+ .append(" t.customer_no,t.emp_no,c.emp_name,t.item_count,t.serial_number, ")
+ .append(" DATE_FORMAT(t.in_station_time, '%Y-%m-%d %H:%i:%s') AS in_station_time, ")
+ .append(" (CASE t.error_flag WHEN '0' THEN '姝ｅ父' WHEN '1' THEN '涓嶈壇' WHEN '3' THEN '鎶ュ簾' ELSE 'N/A' END) error_flag ")
+ .append(" FROM [={");
+ sb3.append("da_t_wip_detail");
sb3.append("}{}{")
- .append(" WHERE serial_number = ( ")
- .append(" SELECT '")
- .append(querySN)
- .append("' old_sn ")
- .append(" UNION ")
- .append(" SELECT DISTINCT old_sn ")
- .append(" FROM da_t_co_sn_relation ")
- .append(" WHERE new_sn = '")
- .append(querySN)
- .append("' ")
- .append(" ) ")
- .append("}=] t ")
- .append(" LEFT JOIN da_t_co_emp_desc c ON t.emp_no = c.emp_no ")
- .append(" ORDER BY t.in_station_time ");
+ .append(" WHERE serial_number = ( ")
+ .append(" SELECT '")
+ .append(querySN)
+ .append("' old_sn ")
+ .append(" UNION ")
+ .append(" SELECT DISTINCT old_sn ")
+ .append(" FROM da_t_co_sn_relation ")
+ .append(" WHERE new_sn = '")
+ .append(querySN)
+ .append("' ")
+ .append(" ) ")
+ .append("}=] t ")
+ .append(" LEFT JOIN da_t_co_emp_desc c ON t.emp_no = c.emp_no ")
+ .append(" ORDER BY t.in_station_time ");
//鎶ヨ〃瑙ｆ瀽SQL锛岀敓鎴怶ITH
String finalSql3 = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sb3.toString());
- DataTableEntity crossInfo=baseDao.listTable(finalSql3, new Object[] {});
+ DataTableEntity crossInfo = baseDao.listTable(finalSql3, new Object[]{});
return crossInfo;
}
/**
- * 鑾峰彇鍘嗗彶缁翠慨璁板綍
+ * 鑾峰彇鍘嗗彶缁翠慨璁板綍
+ *
* @param querySN
* @return
*/
@@ -276,47 +266,48 @@
//缁翠慨璁板綍
StringBuilder sb4 = new StringBuilder();
sb4.append(" SELECT ")
- .append(" c.error_desc,d.reason_desc,m.repair_moth, ")
- .append(" e.test_code,e.ng_count,e.test_point, ")
- .append(" r.reason_code,r.duty_type,r.repair_station, ")
- .append(" r.repair_group,r.error_item,r.item_desc, ")
- .append(" r.date_code,r.repair_desc,r.repairer, ")
- .append(" r.repair_time,s.emp_name,t.duty_desc ")
- .append(" FROM [=da_t_wip_repair=] r ")
- .append(" LEFT JOIN [={da_t_wip_error}{test_code,ng_count,test_point,ng_record_id,serial_number}{WHERE serial_number='")
- .append(querySN)
- .append("'}=] e ON e.ng_record_id = r.ng_record_id ")
- .append(" LEFT JOIN da_t_co_error_code c ON e.test_code = c.error_code ")
- .append(" LEFT JOIN da_t_co_repair_moth m ON r.repair_month_code = m.repair_code ")
- .append(" LEFT JOIN da_t_co_reason_code d ON r.reason_code = d.reason_code ")
- .append(" LEFT JOIN da_t_co_duty t ON r.duty_type = t.duty_type ")
- .append(" LEFT JOIN da_t_co_emp_desc s ON r.repairer = s.emp_no ")
- .append(" WHERE e.serial_number= ?");
+ .append(" c.error_desc,d.reason_desc,m.repair_moth, ")
+ .append(" e.test_code,e.ng_count,e.test_point, ")
+ .append(" r.reason_code,r.duty_type,r.repair_station, ")
+ .append(" r.repair_group,r.error_item,r.item_desc, ")
+ .append(" r.date_code,r.repair_desc,r.repairer, ")
+ .append(" r.repair_time,s.emp_name,t.duty_desc ")
+ .append(" FROM [=da_t_wip_repair=] r ")
+ .append(" LEFT JOIN [={da_t_wip_error}{test_code,ng_count,test_point,ng_record_id,serial_number}{WHERE serial_number='")
+ .append(querySN)
+ .append("'}=] e ON e.ng_record_id = r.ng_record_id ")
+ .append(" LEFT JOIN da_t_co_error_code c ON e.test_code = c.error_code ")
+ .append(" LEFT JOIN da_t_co_repair_moth m ON r.repair_month_code = m.repair_code ")
+ .append(" LEFT JOIN da_t_co_reason_code d ON r.reason_code = d.reason_code ")
+ .append(" LEFT JOIN da_t_co_duty t ON r.duty_type = t.duty_type ")
+ .append(" LEFT JOIN da_t_co_emp_desc s ON r.repairer = s.emp_no ")
+ .append(" WHERE e.serial_number= ?");
//鎶ヨ〃瑙ｆ瀽SQL锛岀敓鎴怶ITH
String finalSql4 = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sb4.toString());
- DataTableEntity repairInfo=baseDao.listTable(finalSql4, new Object[] {querySN});
+ DataTableEntity repairInfo = baseDao.listTable(finalSql4, new Object[]{querySN});
return repairInfo;
}
/**
- * 鐢熸垚EXCEL鏁版嵁
+ * 鐢熸垚EXCEL鏁版嵁
+ *
* @param request
* @param response
- * @throws IOException
+ * @throws IOException
*/
public void getReportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
//鑾峰彇鏈哄彿鏌ヨ淇℃伅
FieldSetEntity fse = BaseUtil.getFieldSetEntity(request);
DataTableEntity workCenterOperationInfomationSheet = this.getWorkCenterOperationInfomation(fse.getString(CmnConst.SERIAL_NUMBER), fse.getString(CmnConst.IS_BAK_TABLE));
-
+
//鍒濆EXCEL淇℃伅
response.setContentType("multipart/form-data");
- response.setCharacterEncoding("utf-8");
- //杩欓噷URLEncoder.encode鍙互闃叉涓枃涔辩爜
- String fileName = URLEncoder.encode("SN鍘嗗彶杩芥函" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx", "UTF-8");
- response.setHeader("Content-disposition", "attachment;filename=" + fileName);
-
+ response.setCharacterEncoding("utf-8");
+ //杩欓噷URLEncoder.encode鍙互闃叉涓枃涔辩爜
+ String fileName = URLEncoder.encode("SN鍘嗗彶杩芥函" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx", "UTF-8");
+ response.setHeader("Content-disposition", "attachment;filename=" + fileName);
+
List<TraceSNItemEntity> traceSNItemEntityList = new ArrayList<>();
for (int i = 0; i < workCenterOperationInfomationSheet.getRows(); i++) {
FieldSetEntity fieldSetEntity = workCenterOperationInfomationSheet.getFieldSetEntity(i);
@@ -329,7 +320,7 @@
ExcelWriter excelWriter = EasyExcel.write(outputStream, TraceSNItemEntity.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
try {
WriteSheet writeSheet = EasyExcel.writerSheet(1, "鐗╂枡浣滀笟淇℃伅").build();
- excelWriter.write(traceSNItemEntityList, writeSheet);
+ excelWriter.write(traceSNItemEntityList, writeSheet);
} catch (Exception e) {
throw e;
} finally {
--
Gitblit v1.9.2