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/SNAssociatedItemService.java | 384 ++++++++++++++++++++++++++++-------------------------- 1 files changed, 196 insertions(+), 188 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..3ff044d 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,14 @@ 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 org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Component; @@ -31,50 +30,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 +81,91 @@ jsonReturn.set("keyp_info", BaseUtil.dataTableEntityToJson(dtKeyp)); return jsonReturn; } - + //Method2:浣跨敤宸﹀叧鑱旀煡璇紙鑰冭檻鍒癝QL鐨勯暱搴︽爣鍑嗭紝寤鸿涓嶄娇鐢級 - - }else { + + } else { jsonReturn.set("total", 0); } return null; } - + /** - * 鏍规嵁杩囨护鏉′欢瑙f瀽tacking鐨凷QL + * 鏍规嵁tacking琛ㄧ殑SN瑙f瀽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; + } + + /** + * 鏍规嵁杩囨护鏉′欢瑙f瀽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)); + //瑙f瀽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瑙f瀽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,108 +173,122 @@ } 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)); } } } - + /** - * 瑙f瀽鎶ヨ〃杩囨护鏉′欢 + * 瑙f瀽鎶ヨ〃杩囨护鏉′欢 + * * @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); - + //鍒濆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<SNAssociatedItemEntity> traceSNItemEntityList = new ArrayList<>(); for (int i = 0; i < workCenterOperationInfomationSheet.getRows(); i++) { FieldSetEntity fieldSetEntity = workCenterOperationInfomationSheet.getFieldSetEntity(i); @@ -294,49 +301,50 @@ ExcelWriter excelWriter = EasyExcel.write(outputStream, SNAssociatedItemEntity.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build(); try { WriteSheet writeSheet = EasyExcel.writerSheet(1, "data").build(); - excelWriter.write(traceSNItemEntityList, writeSheet); + excelWriter.write(traceSNItemEntityList, writeSheet); } 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); //鐩爣椤� + //鍒涘缓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); - + 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); + Object[] serialNumbers = dtTracking.getUuids(); + String keypSql = createKeypSql(serialNumbers, isBakTable, isRecentData); System.out.println(keypSql); - DataTableEntity dtKeyp=baseDao.listTable(keypSql, new Object[] {}); - + DataTableEntity dtKeyp = baseDao.listTable(keypSql, new Object[]{}); + if (!BaseUtil.dataTableIsEmpty(dtKeyp)) { //灏唗racking琛ㄧ殑鏁版嵁杞负(key锛寁alue)褰㈠紡瀛樺偍 JSONObject json = convertDataTableToJson(dtTracking); addKeypInfo(json, dtKeyp); return dtKeyp; } - } + } return null; } } -- Gitblit v1.9.2