From a3ba5db359871eb7846882ae58a96ffa08816a44 Mon Sep 17 00:00:00 2001
From: 许鹏程 <1821349743@qq.com>
Date: 星期一, 29 七月 2024 17:00:03 +0800
Subject: [PATCH] 123
---
product-server-data-center/src/main/java/com/product/data/center/service/SNAssociatedItemService.java | 429 ++++++++++++++++++++++++++++-------------------------
1 files changed, 229 insertions(+), 200 deletions(-)
diff --git a/product-server-data-center/src/main/java/com/product/data/center/service/SNAssociatedItemService.java b/product-server-data-center/src/main/java/com/product/data/center/service/SNAssociatedItemService.java
index a2fc10a..b97dfbe 100644
--- a/product-server-data-center/src/main/java/com/product/data/center/service/SNAssociatedItemService.java
+++ b/product-server-data-center/src/main/java/com/product/data/center/service/SNAssociatedItemService.java
@@ -2,15 +2,15 @@
import java.io.IOException;
import java.net.URLEncoder;
-import java.util.ArrayList;
-import java.util.Date;
-import java.util.List;
-import java.util.Map;
+import java.text.DateFormat;
+import java.util.*;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
+import cn.hutool.core.date.DateTime;
+import com.product.core.entity.SQLEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
@@ -31,50 +31,50 @@
import cn.hutool.json.JSONObject;
/**
- * SN鍏宠仈鐗╂枡
- * @author 86151
+ * SN鍏宠仈鐗╂枡
*
+ * @author 86151
*/
@Component
-public class SNAssociatedItemService extends AbstractBaseService{
-
+public class SNAssociatedItemService extends AbstractBaseService {
+
@Autowired
BaseDao baseDao;
-
+
/**
- * 杩囩珯淇℃伅
+ * 杩囩珯淇℃伅
+ *
* @param fse
* @return
*/
public JSONObject listSNItemInfo(FieldSetEntity fse) {
-
- JSONObject jsonReturn=new JSONObject();
-
- String isBakTable = fse.getString(CmnConst.IS_BAK_TABLE); //鏄惁鍒囪〃
- Integer isRecentData = fse.getInteger(CmnConst.IS_RECENT_DATA);//鏄惁杩戞湡鏁版嵁
- Integer pageSize = fse.getInteger(CmnConst.PAGESIZE); //椤垫暟澶у皬
- Integer cpage = fse.getInteger(CmnConst.CPAGE); //鐩爣椤�
-
+
+ JSONObject jsonReturn = new JSONObject();
+
+ String isBakTable = fse.getString(CmnConst.IS_BAK_TABLE); //鏄惁鍒囪〃
+ boolean isRecentData = fse.getBoolean(CmnConst.IS_RECENT_DATA) == true;//鏄惁杩戞湡鏁版嵁
+ Integer pageSize = fse.getInteger(CmnConst.PAGESIZE); //椤垫暟澶у皬
+ Integer cpage = fse.getInteger(CmnConst.CPAGE); //鐩爣椤�
+
//鍒涘缓tracking琛⊿QL锛屽苟鑾峰彇杩囨护鐨勬暟鎹�
- String parseTrackingSql=createTrackingSql(fse, isBakTable);
- DataTableEntity dtTracking= baseDao.listTable(parseTrackingSql, new Object[] {}, pageSize, cpage);
+ String parseTrackingSql = createTrackingSql(fse, isBakTable, isRecentData);
+ DataTableEntity dtTracking = baseDao.listTable(parseTrackingSql, new Object[]{}, pageSize, cpage);
if (!BaseUtil.dataTableIsEmpty(dtTracking)) {
-
+
//鑾峰彇鎬绘潯鏁�
// String totalTrackingSql="SELECT COUNT(*)count_total FROM ("+parseTrackingSql+")t";
// FieldSetEntity fseTotalTracking=baseDao.getFieldSetBySQL(totalTrackingSql, new Object[] {}, false);
// jsonReturn.set("total", fseTotalTracking.getInteger("count_total"));
- int totalTrackingSql=dtTracking.getSqle().getTotalCount();
+ int totalTrackingSql = dtTracking.getSqle().getTotalCount();
jsonReturn.set("total", totalTrackingSql);
-
+
//閫氳繃tracking杩囨护keyp
//Method1:浣跨敤OR鏌ヨ
- Object [] serialNumbers = dtTracking.getUuids();
- String keypSql = createKeypSql(serialNumbers, isBakTable);
- System.out.println(keypSql);
- DataTableEntity dtKeyp=baseDao.listTable(keypSql, new Object[] {});
-
+ Object[] serialNumbers = dtTracking.getUuids();
+ String keypSql = createKeypSql(serialNumbers, isBakTable, isRecentData);
+ DataTableEntity dtKeyp = baseDao.listTable(keypSql, new Object[]{});
+
if (!BaseUtil.dataTableIsEmpty(dtKeyp)) {
//灏唗racking琛ㄧ殑鏁版嵁杞负(key锛寁alue)褰㈠紡瀛樺偍
JSONObject json = convertDataTableToJson(dtTracking);
@@ -82,97 +82,91 @@
jsonReturn.set("keyp_info", BaseUtil.dataTableEntityToJson(dtKeyp));
return jsonReturn;
}
-
+
//Method2:浣跨敤宸﹀叧鑱旀煡璇紙鑰冭檻鍒癝QL鐨勯暱搴︽爣鍑嗭紝寤鸿涓嶄娇鐢級
-
- }else {
+
+ } else {
jsonReturn.set("total", 0);
}
return null;
}
-
+
/**
- * 鏍规嵁杩囨护鏉′欢瑙ｆ瀽tacking鐨凷QL
+ * 鏍规嵁tacking琛ㄧ殑SN瑙ｆ瀽keyp琛�
+ *
+ * @param serialNumbers
+ * @param isBakTable 鏄惁BAK琛�
+ * @return
+ */
+ public String createKeypSql(Object[] serialNumbers, String isBakTable, boolean isRecentData) {
+ String keypFilter = BaseUtil.buildQuestionMarkFilter(CmnConst.PK_PRODUCT_SN, serialNumbers, true);
+
+ StringBuilder keypSql = new StringBuilder(2056);
+ keypSql.append(" SELECT ")
+ .append(" t.pk_product_sn AS pk_product_sn,b1.zj,c.dj,b.ysj, ")
+ .append(" d1.ysjmn,d.dk,e1.xsq,x1.ykq,c1.xfzj, ")
+ .append(" REGEXP_SUBSTR(d.dk, '[^|]+', 1, 5) zbrj, ")
+ .append(" e.mk,f.fjd,h.ccpg ")
+ .append(" FROM(SELECT DISTINCT pk_product_sn FROM [={");
+ keypSql.append("da_t_wip_product_keyp");
+ keypSql.append("}{pk_product_sn,pk_keyp_sn,pk_item_source_type}{")
+ .append(" WHERE instr( pk_mo,'DK' )=0 AND ")
+ .append(keypFilter)
+ .append("}=]) t ")
+ .append(" LEFT JOIN(SELECT u.pk_product_sn, u.pk_keyp_sn ysj FROM ").append("da_t_wip_product_keyp").append(" u WHERE u.pk_item_source_type= 'YSJ' AND u.pk_keyp_sn NOT LIKE 'R_%') b ON t.pk_product_sn = b.pk_product_sn ")
+ .append(" LEFT JOIN(SELECT v.pk_product_sn, v.pk_keyp_sn dj FROM ").append("da_t_wip_product_keyp").append(" v WHERE v.pk_item_source_type= 'DJ' AND v.pk_keyp_sn NOT LIKE 'R_%') c ON t.pk_product_sn = c.pk_product_sn ")
+ .append(" LEFT JOIN(SELECT w.pk_product_sn, w.pk_keyp_sn dk FROM ").append("da_t_wip_product_keyp").append(" w WHERE w.pk_item_source_type= 'DK' AND w.pk_keyp_sn NOT LIKE 'R_%') d ON t.pk_product_sn = d.pk_product_sn ")
+ .append(" LEFT JOIN(SELECT x.pk_product_sn, x.pk_keyp_sn mk FROM ").append("da_t_wip_product_keyp").append(" x WHERE x.pk_item_source_type= 'MK' AND x.pk_keyp_sn NOT LIKE 'R_%') e ON t.pk_product_sn = e.pk_product_sn ")
+ .append(" LEFT JOIN(SELECT y.pk_product_sn, y.pk_keyp_sn fjd FROM ").append("da_t_wip_product_keyp").append(" y WHERE y.pk_item_source_type= 'FJD' AND y.pk_keyp_sn NOT LIKE 'R_%') f ON t.pk_product_sn = f.pk_product_sn ")
+ .append(" LEFT JOIN(SELECT z.pk_product_sn, z.pk_keyp_sn zj FROM ").append("da_t_wip_product_keyp").append(" z WHERE (z.pk_item_source_type= 'ZJNJMN' or z.pk_item_source_type= 'ZJWJMN') AND z.pk_keyp_sn NOT LIKE 'R_%') b1 ON t.pk_product_sn = b1.pk_product_sn ")
+ .append(" LEFT JOIN(SELECT v1.pk_product_sn, v1.pk_keyp_sn ysjmn FROM ").append("da_t_wip_product_keyp").append(" v1 WHERE v1.pk_item_source_type= 'YSJMN' AND v1.pk_keyp_sn NOT LIKE 'R_%') d1 ON t.pk_product_sn = d1.pk_product_sn ")
+ .append(" LEFT JOIN(SELECT w1.pk_product_sn, w1.pk_keyp_sn xsq FROM ").append("da_t_wip_product_keyp").append(" w1 WHERE w1.pk_item_source_type= 'XSQ' AND w1.pk_keyp_sn NOT LIKE 'R_%') e1 ON t.pk_product_sn = e1.pk_product_sn ")
+ .append(" LEFT JOIN(SELECT x1.pk_product_sn, x1.pk_keyp_sn ykq FROM ").append("da_t_wip_product_keyp").append(" x1 WHERE x1.pk_item_source_type= 'YKQ' AND x1.pk_keyp_sn NOT LIKE 'R_%') x1 ON t.pk_product_sn = x1.pk_product_sn ")
+ .append(" LEFT JOIN(SELECT x1.pk_product_sn, x1.pk_keyp_sn xfzj FROM ").append("da_t_wip_product_keyp").append(" x1 WHERE x1.pk_item_source_type= 'XFZJ' AND x1.pk_keyp_sn NOT LIKE 'R_%') c1 ON t.pk_product_sn = c1.pk_product_sn ")
+ .append(" LEFT JOIN (SELECT x1.pk_product_sn, x1.pk_keyp_sn ccpg FROM ").append("da_t_wip_product_keyp").append(" x1 WHERE x1.pk_item_source_type = 'CCPG' AND x1.pk_keyp_sn NOT LIKE 'R_%') h ON t.pk_product_sn = h.pk_product_sn ");
+
+ String parseKeypSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), keypSql.toString(), isRecentData ? 2 : 0, false);
+ return parseKeypSql;
+ }
+
+ /**
+ * 鏍规嵁杩囨护鏉′欢瑙ｆ瀽tacking鐨凷QL
+ *
* @param fse
* @param isBakTable 鏄惁BAK琛�
* @return
*/
- public String createTrackingSql(FieldSetEntity fse, String isBakTable) {
-
- StringBuilder trackingSql=new StringBuilder(256);
+ public String createTrackingSql(FieldSetEntity fse, String isBakTable, boolean isRecentData) {
+
+ StringBuilder trackingSql = new StringBuilder(256);
trackingSql.append("SELECT a.mo_number AS moNumber,a.in_line_time AS inLineTime,a.area_name AS areaName,a.serial_number AS uuid,a.technicsid ")
- .append(" FROM [={");
- if ("1".equals(isBakTable)) {
- trackingSql.append("da_t_wip_tracking_bak");
- }else {
- trackingSql.append("da_t_wip_tracking");
- }
- trackingSql.append("}{mo_number,in_line_time,area_name,serial_number,technicsid}{")
- .append(parseCondition(fse,0))
- .append("}=] a")
- .append(" JOIN da_t_smo_technics h ON a.technicsid =h.technicsid ")
- .append(parseCondition(fse,1));
-
+ .append(" FROM [={da_t_wip_tracking}");
+ trackingSql.append("{mo_number,in_line_time,area_name,serial_number,technicsid}{")
+ .append(parseCondition(fse, 0))
+ .append("}=] a")
+ .append(" JOIN da_t_smo_technics h ON a.technicsid =h.technicsid ")
+ .append(parseCondition(fse, 1));
+
//瑙ｆ瀽SQL
- String parseTrackingSql= QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), trackingSql.toString(), 2, false);
+ String parseTrackingSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), trackingSql.toString(), isRecentData ? 2 : 0, false);
return parseTrackingSql;
}
-
+
/**
- * 鏍规嵁tacking琛ㄧ殑SN瑙ｆ瀽keyp琛�
- * @param serialNumbers
- * @param isBakTable 鏄惁BAK琛�
- * @return
- */
- public String createKeypSql(Object[] serialNumbers, String isBakTable) {
- String keypFilter = BaseUtil.buildQuestionMarkFilter(CmnConst.PK_PRODUCT_SN, serialNumbers, true);
-
- StringBuilder keypSql = new StringBuilder(2056);
- keypSql.append(" SELECT ")
- .append(" t.pk_product_sn AS pkProductSn,b1.zj,c.dj,b.ysj, ")
- .append(" d1.ysjmn,d.dk,e1.xsq,x1.ykq,c1.xfzj, ")
- .append(" REGEXP_SUBSTR(d.dk, '[^|]+', 1, 5) zbrj, ")
- .append(" e.mk,f.fjd,h.ccpg ")
- .append(" FROM(SELECT DISTINCT pk_product_sn FROM [={");
- if ("1".equals(isBakTable)) {
- keypSql.append("da_t_wip_product_keyp_bak");
- }else {
- keypSql.append("da_t_wip_product_keyp");
- }
- keypSql.append("}{pk_product_sn,pk_keyp_sn,pk_item_source_type}{")
- .append(" WHERE instr( pk_mo,'DK' )=0 AND ")
- .append(keypFilter)
- .append("}=]) t ")
- .append(" LEFT JOIN(SELECT u.pk_product_sn, u.pk_keyp_sn ysj FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" u WHERE u.pk_item_source_type= 'YSJ' AND u.pk_keyp_sn NOT LIKE 'R_%') b ON t.pk_product_sn = b.pk_product_sn ")
- .append(" LEFT JOIN(SELECT v.pk_product_sn, v.pk_keyp_sn dj FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" v WHERE v.pk_item_source_type= 'DJ' AND v.pk_keyp_sn NOT LIKE 'R_%') c ON t.pk_product_sn = c.pk_product_sn ")
- .append(" LEFT JOIN(SELECT w.pk_product_sn, w.pk_keyp_sn dk FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" w WHERE w.pk_item_source_type= 'DK' AND w.pk_keyp_sn NOT LIKE 'R_%') d ON t.pk_product_sn = d.pk_product_sn ")
- .append(" LEFT JOIN(SELECT x.pk_product_sn, x.pk_keyp_sn mk FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" x WHERE x.pk_item_source_type= 'MK' AND x.pk_keyp_sn NOT LIKE 'R_%') e ON t.pk_product_sn = e.pk_product_sn ")
- .append(" LEFT JOIN(SELECT y.pk_product_sn, y.pk_keyp_sn fjd FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" y WHERE y.pk_item_source_type= 'FJD' AND y.pk_keyp_sn NOT LIKE 'R_%') f ON t.pk_product_sn = f.pk_product_sn ")
- .append(" LEFT JOIN(SELECT z.pk_product_sn, z.pk_keyp_sn zj FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" z WHERE (z.pk_item_source_type= 'ZJNJMN' or z.pk_item_source_type= 'ZJWJMN') AND z.pk_keyp_sn NOT LIKE 'R_%') b1 ON t.pk_product_sn = b1.pk_product_sn ")
- .append(" LEFT JOIN(SELECT v1.pk_product_sn, v1.pk_keyp_sn ysjmn FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" v1 WHERE v1.pk_item_source_type= 'YSJMN' AND v1.pk_keyp_sn NOT LIKE 'R_%') d1 ON t.pk_product_sn = d1.pk_product_sn ")
- .append(" LEFT JOIN(SELECT w1.pk_product_sn, w1.pk_keyp_sn xsq FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" w1 WHERE w1.pk_item_source_type= 'XSQ' AND w1.pk_keyp_sn NOT LIKE 'R_%') e1 ON t.pk_product_sn = e1.pk_product_sn ")
- .append(" LEFT JOIN(SELECT x1.pk_product_sn, x1.pk_keyp_sn ykq FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" x1 WHERE x1.pk_item_source_type= 'YKQ' AND x1.pk_keyp_sn NOT LIKE 'R_%') x1 ON t.pk_product_sn = x1.pk_product_sn ")
- .append(" LEFT JOIN(SELECT x1.pk_product_sn, x1.pk_keyp_sn xfzj FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" x1 WHERE x1.pk_item_source_type= 'XFZJ' AND x1.pk_keyp_sn NOT LIKE 'R_%') c1 ON t.pk_product_sn = c1.pk_product_sn ")
- .append(" LEFT JOIN (SELECT x1.pk_product_sn, x1.pk_keyp_sn ccpg FROM ").append("1".equals(isBakTable)?"da_t_wip_product_keyp_bak":"da_t_wip_product_keyp").append(" x1 WHERE x1.pk_item_source_type = 'CCPG' AND x1.pk_keyp_sn NOT LIKE 'R_%') h ON t.pk_product_sn = h.pk_product_sn ");
-
- String parseKeypSql= QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), keypSql.toString(), 2, false);
- return parseKeypSql;
- }
-
- /**
- * tracking淇℃伅杞寲
+ * tracking淇℃伅杞寲
+ *
* @param dtTracking
* @return
*/
public JSONObject convertDataTableToJson(DataTableEntity dtTracking) {
- JSONObject json=new JSONObject();
- for(int i=0; i<dtTracking.getRows();i++) {
- String serialNumber=dtTracking.getFieldSetEntity(i).getString(CmnConst.UUID);
- String areaName=dtTracking.getFieldSetEntity(i).getString(CmnConst.AREANAME);
- String moNumber=dtTracking.getFieldSetEntity(i).getString(CmnConst.MONUMBER);
- String inLineTime=dtTracking.getFieldSetEntity(i).getString(CmnConst.INLINETIME);
- List<String> trackInfo=new ArrayList<>();
+ JSONObject json = new JSONObject();
+ for (int i = 0; i < dtTracking.getRows(); i++) {
+ String serialNumber = dtTracking.getFieldSetEntity(i).getString(CmnConst.UUID);
+ String areaName = dtTracking.getFieldSetEntity(i).getString(CmnConst.AREANAME);
+ String moNumber = dtTracking.getFieldSetEntity(i).getString(CmnConst.MONUMBER);
+ String inLineTime = dtTracking.getFieldSetEntity(i).getString(CmnConst.INLINETIME);
+ List<String> trackInfo = new ArrayList<>();
trackInfo.add(areaName);
trackInfo.add(moNumber);
trackInfo.add(inLineTime);
@@ -180,163 +174,198 @@
}
return json;
}
-
+
/**
- * 灏唗racking淇℃伅娣诲姞鑷砶eyp
+ * 灏唗racking淇℃伅娣诲姞鑷砶eyp
+ *
* @param json
* @param dtKeyp
*/
public void addKeypInfo(JSONObject json, DataTableEntity dtKeyp) {
for (int i = 0; i < dtKeyp.getRows(); i++) {
- FieldSetEntity fseKeyp=dtKeyp.getFieldSetEntity(i);
- String pkProductSN=fseKeyp.getString(CmnConst.PKPRODUCTSN);
+ FieldSetEntity fseKeyp = dtKeyp.getFieldSetEntity(i);
+ String pkProductSN = fseKeyp.getString("pk_product_sn");
+ fseKeyp.setValue(CmnConst.PKPRODUCTSN, pkProductSN);
@SuppressWarnings("unchecked")
List<String> trackInfo = (List<String>) json.get(pkProductSN);
- if (trackInfo.size()==3) {
- fseKeyp.setValue(CmnConst.AREANAME, trackInfo.get(0));
- fseKeyp.setValue(CmnConst.MONUMBER, trackInfo.get(1));
- fseKeyp.setValue(CmnConst.INLINETIME, trackInfo.get(2));
+ if (trackInfo.size() == 3) {
+ fseKeyp.setValue("area_name", trackInfo.get(0));
+ fseKeyp.setValue("mo_number", trackInfo.get(1));
+ fseKeyp.setValue("in_line_time", trackInfo.get(2));
}
}
}
-
+
/**
- * 瑙ｆ瀽鎶ヨ〃杩囨护鏉′欢
+ * 瑙ｆ瀽鎶ヨ〃杩囨护鏉′欢
+ *
* @param fse
* @return
*/
- public String parseCondition(FieldSetEntity fse ,int type) {
-
- StringBuilder sqlFilter=new StringBuilder(128);
-
- if (type==1) {
- String poka_yoke = fse.getString(CmnConst.POKA_YOKE); //闃插憜瑙勫垯锛堝皬绫筹紝闈炲皬绫筹級
- if (!BaseUtil.strIsNull(poka_yoke)) {
- if ("0".equals(poka_yoke)) {
- sqlFilter.append(" WHERE h.poka_yoke=1 ");
- }else {
- sqlFilter.append(" WHERE h.poka_yoke!=1 ");
- }
- }
- }else {
- sqlFilter.append(" WHERE scrap_flag IS NULL ");
- String moNumber = fse.getString(CmnConst.MO_NUMBER); //宸ュ崟鍙�
- String factory = fse.getString(CmnConst.FACTORY); //鍘傚埆
- String areasn = fse.getString(CmnConst.AREA_SN); //绾夸綋
- String startTime = fse.getString(CmnConst.START_TIME); //寮�濮嬫椂闂�
- String endTime = fse.getString(CmnConst.END_TIME); //缁撴潫鏃堕棿
-
- if(!BaseUtil.strIsNull(moNumber)) {
- sqlFilter.append(" AND mo_number = '")
- .append(moNumber)
- .append("'");
+ public String parseCondition(FieldSetEntity fse, int type) {
+
+ StringBuilder sqlFilter = new StringBuilder(128);
+
+ if (type == 1) {
+ String poka_yoke = fse.getString(CmnConst.POKA_YOKE); //闃插憜瑙勫垯锛堝皬绫筹紝闈炲皬绫筹級
+ if (!BaseUtil.strIsNull(poka_yoke)) {
+ if ("0".equals(poka_yoke)) {
+ sqlFilter.append(" WHERE h.poka_yoke=1 ");
+ } else {
+ sqlFilter.append(" WHERE h.poka_yoke!=1 ");
+ }
}
-
+ } else {
+ sqlFilter.append(" WHERE scrap_flag IS NULL ");
+ String moNumber = fse.getString(CmnConst.MO_NUMBER); //宸ュ崟鍙�
+ String factory = fse.getString(CmnConst.FACTORY); //鍘傚埆
+ String areasn = fse.getString(CmnConst.AREA_SN); //绾夸綋
+ Date startTime = fse.getDate(CmnConst.START_TIME); //寮�濮嬫椂闂�
+ Date endTime = fse.getDate(CmnConst.END_TIME); //缁撴潫鏃堕棿
+
+ if (!BaseUtil.strIsNull(moNumber)) {
+ sqlFilter.append(" AND mo_number = '")
+ .append(moNumber)
+ .append("'");
+ }
+
// if (!BaseUtil.strIsNull(factory)) {
// sqlFilter.append("");
// }
-
- if(!BaseUtil.strIsNull(areasn)) {
- String [] areasns=areasn.split(",");
- if (areasns.length>1) {
+
+ if (!BaseUtil.strIsNull(areasn)) {
+ String[] areasns = areasn.split(",");
+ if (areasns.length > 1) {
sqlFilter.append(" AND ");
sqlFilter.append(BaseUtil.buildQuestionMarkFilter(CmnConst.AREA_SN, areasns, true));
- }else {
+ } else {
sqlFilter.append(" AND area_sn = '")
- .append(areasn)
- .append("'");
+ .append(areasn)
+ .append("'");
}
}
-
- if(!BaseUtil.strIsNull(startTime)) {
- sqlFilter.append(" AND in_station_time >STR_TO_DATE('")
- .append(startTime)
- .append("','%Y-%m-%d %H:%i:%s')");
+
+ if (startTime != null) {
+ sqlFilter.append(" AND in_station_time >=STR_TO_DATE('")
+ .append(DateUtil.format(startTime, "yyyy-MM-dd HH:mm:ss"))
+ .append("','%Y-%m-%d %H:%i:%s')");
}
-
- if (!BaseUtil.strIsNull(endTime)) {
- sqlFilter.append(" AND in_station_time <STR_TO_DATE('")
- .append(endTime)
- .append("','%Y-%m-%d %H:%i:%s')");
+
+ if (endTime != null) {
+ //鍒ゆ柇缁撴潫鏃堕棿鏄惁涓哄綋澶╃殑寮�濮嬫椂闂� 00:00:00
+ DateTime dateTime = new DateTime(endTime);
+ Calendar calendar = dateTime.toCalendar();
+ if (calendar.get(Calendar.HOUR_OF_DAY) == 0 && calendar.get(Calendar.MINUTE) == 0 && calendar.get(Calendar.SECOND) == 0) {
+ //璁剧疆涓�23:59:59
+ calendar.set(Calendar.HOUR_OF_DAY, 23);
+ calendar.set(Calendar.MINUTE, 59);
+ calendar.set(Calendar.SECOND, 59);
+ endTime = calendar.getTime();
+ }
+ sqlFilter.append(" AND in_station_time <=STR_TO_DATE('")
+ .append(DateUtil.format(endTime, "yyyy-MM-dd HH:mm:ss"))
+ .append("','%Y-%m-%d %H:%i:%s')");
}
}
-
- return sqlFilter.toString();
+
+ return sqlFilter.toString();
}
-
- /**
- * 鐢熸垚EXCEL淇℃伅
- * @param request
- * @param response
- * @throws IOException
- */
- public void getReportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
+
+ /**
+ * 鐢熸垚EXCEL淇℃伅
+ *
+ * @param request
+ * @param response
+ * @throws IOException
+ */
+ public void getReportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
//鑾峰彇鏈哄彿鏌ヨ淇℃伅
FieldSetEntity fse = BaseUtil.getFieldSetEntity(request);
- DataTableEntity workCenterOperationInfomationSheet = this.dtSNItemInfo(fse);
-
+ Boolean isAll = fse.getBoolean("is_all");
+ if (isAll) {
+ fse.setValue(CmnConst.CPAGE, 1);
+ fse.setValue(CmnConst.PAGESIZE, 999);
+ }
//鍒濆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);
-
- List<SNAssociatedItemEntity> traceSNItemEntityList = new ArrayList<>();
- for (int i = 0; i < workCenterOperationInfomationSheet.getRows(); i++) {
- FieldSetEntity fieldSetEntity = workCenterOperationInfomationSheet.getFieldSetEntity(i);
- Map<Object, Object> values = fieldSetEntity.getValues();
- JSONObject jsonObject = new JSONObject((Map) values);
- SNAssociatedItemEntity manufacturingOrderEntity = jsonObject.toBean(SNAssociatedItemEntity.class);
- traceSNItemEntityList.add(manufacturingOrderEntity);
- }
+ 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);
ServletOutputStream outputStream = response.getOutputStream();
ExcelWriter excelWriter = EasyExcel.write(outputStream, SNAssociatedItemEntity.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
+
+ DataTableEntity workCenterOperationInfomationSheet;
try {
- WriteSheet writeSheet = EasyExcel.writerSheet(1, "data").build();
- excelWriter.write(traceSNItemEntityList, writeSheet);
+ do {
+ workCenterOperationInfomationSheet = this.dtSNItemInfo(fse);
+ if (DataTableEntity.isEmpty(workCenterOperationInfomationSheet)) {
+ continue;
+ }
+ List<SNAssociatedItemEntity> traceSNItemEntityList = new ArrayList<>();
+ for (int i = 0; i < workCenterOperationInfomationSheet.getRows(); i++) {
+ FieldSetEntity fieldSetEntity = workCenterOperationInfomationSheet.getFieldSetEntity(i);
+ Map<Object, Object> values = fieldSetEntity.getValues();
+ JSONObject jsonObject = new JSONObject((Map) values);
+ SNAssociatedItemEntity manufacturingOrderEntity = jsonObject.toBean(SNAssociatedItemEntity.class);
+ traceSNItemEntityList.add(manufacturingOrderEntity);
+ }
+
+ WriteSheet writeSheet = EasyExcel.writerSheet(1, "data").build();
+ excelWriter.write(traceSNItemEntityList, writeSheet);
+
+
+ fse.setValue(CmnConst.CPAGE, fse.getInteger(CmnConst.CPAGE) + 1);
+ } while (
+ isAll && workCenterOperationInfomationSheet != null && workCenterOperationInfomationSheet.getSqle().isCountLoad());
} catch (Exception e) {
throw e;
} finally {
excelWriter.finish();
}
}
-
- /**
- * 杩囩珯淇℃伅
+
+ /**
+ * 杩囩珯淇℃伅
+ *
* @param fse
* @return
*/
public DataTableEntity dtSNItemInfo(FieldSetEntity fse) {
-
+
String isBakTable = fse.getString(CmnConst.IS_BAK_TABLE);
- Integer isRecentData = fse.getInteger(CmnConst.IS_RECENT_DATA);//鏄惁杩戞湡鏁版嵁
- Integer pageSize = fse.getInteger(CmnConst.PAGESIZE); //椤垫暟澶у皬
- Integer cpage = fse.getInteger(CmnConst.CPAGE); //鐩爣椤�
-
+ boolean isRecentData = fse.getBoolean(CmnConst.IS_RECENT_DATA) == true;//鏄惁杩戞湡鏁版嵁
+ Integer pageSize = fse.getInteger(CmnConst.PAGESIZE); //椤垫暟澶у皬
+ Integer cpage = fse.getInteger(CmnConst.CPAGE); //鐩爣椤�
+ SQLEntity sqle = new SQLEntity();
+ sqle.setCountLoad(false);
//鍒涘缓tracking琛⊿QL锛屽苟鑾峰彇杩囨护鐨勬暟鎹�
- String parseTrackingSql=createTrackingSql(fse, isBakTable);
- DataTableEntity dtTracking= baseDao.listTable(parseTrackingSql, new Object[] {}, pageSize, cpage);
+ String parseTrackingSql = createTrackingSql(fse, isBakTable, isRecentData);
+ DataTableEntity dtTracking = baseDao.listTable(parseTrackingSql, new Object[]{}, pageSize, cpage);
if (!BaseUtil.dataTableIsEmpty(dtTracking)) {
-
+ if (dtTracking.getRows() == pageSize) {
+ sqle.setCountLoad(true);
+ }
//鑾峰彇鎬绘潯鏁�
- String totalTrackingSql="SELECT COUNT(*)count_total FROM ("+parseTrackingSql+")t";
- FieldSetEntity fseTotalTracking=baseDao.getFieldSetBySQL(totalTrackingSql, new Object[] {}, false);
-
+ String totalTrackingSql = "SELECT COUNT(*)count_total FROM (" + parseTrackingSql + ")t";
+// FieldSetEntity fseTotalTracking = baseDao.getFieldSetBySQL(totalTrackingSql, new Object[]{}, false);
+
//閫氳繃tracking杩囨护keyp
//Method1:浣跨敤OR鏌ヨ
- Object [] serialNumbers = dtTracking.getUuids();
- String keypSql = createKeypSql(serialNumbers, isBakTable);
- System.out.println(keypSql);
- DataTableEntity dtKeyp=baseDao.listTable(keypSql, new Object[] {});
-
+ Object[] serialNumbers = dtTracking.getUuids();
+ String keypSql = createKeypSql(serialNumbers, isBakTable, isRecentData);
+ DataTableEntity dtKeyp = baseDao.listTable(keypSql, new Object[]{});
+
if (!BaseUtil.dataTableIsEmpty(dtKeyp)) {
//灏唗racking琛ㄧ殑鏁版嵁杞负(key锛寁alue)褰㈠紡瀛樺偍
JSONObject json = convertDataTableToJson(dtTracking);
addKeypInfo(json, dtKeyp);
+ dtKeyp.setSqle(sqle);
return dtKeyp;
}
- }
- return null;
+ }
+ DataTableEntity dataTableEntity = new DataTableEntity();
+ dataTableEntity.setSqle(sqle);
+ return dataTableEntity;
}
}
--
Gitblit v1.9.2