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/ProductionRealTimeInfoService.java | 1021 ++++++++++++++++++++++++++++----------------------------
1 files changed, 509 insertions(+), 512 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 c10e3ad..88ead0a 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
@@ -41,577 +41,574 @@
public class ProductionRealTimeInfoService extends AbstractBaseService {
- public void getReportExcel(HttpServletRequest request, HttpServletResponse response) throws BaseException, IOException {
- FieldSetEntity fse = BaseUtil.getFieldSetEntity(request);
- DataTableEntity manufacturingCommandSheet = this.getManufacturingCommandSheet(fse);
- if (DataTableEntity.isEmpty(manufacturingCommandSheet)) {
- throw new BaseException(ErrorCode.NOT_EXPORT_DATA);
- }
- response.setContentType("multipart/form-data");
- response.setCharacterEncoding("utf-8");
- // 杩欓噷URLEncoder.encode鍙互闃叉涓枃涔辩爜 褰撶劧鍜宔asyexcel娌℃湁鍏崇郴
- String fileName = URLEncoder.encode("鍒朵护鍗曠敓浜т俊鎭�" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx", "UTF-8");
- response.setHeader("Content-disposition", "attachment;filename=" + fileName);
- List<ManufacturingOrderEntity> manufacturingOrderEntityList = new ArrayList<>();
- for (int i = 0; i < manufacturingCommandSheet.getRows(); i++) {
- FieldSetEntity fieldSetEntity = manufacturingCommandSheet.getFieldSetEntity(i);
- Map<Object, Object> values = fieldSetEntity.getValues();
- JSONObject jsonObject = new JSONObject((Map) values);
+ public void getReportExcel(HttpServletRequest request, HttpServletResponse response) throws BaseException, IOException {
+ FieldSetEntity fse = BaseUtil.getFieldSetEntity(request);
+ DataTableEntity manufacturingCommandSheet = this.getManufacturingCommandSheet(fse);
+ if (DataTableEntity.isEmpty(manufacturingCommandSheet)) {
+ throw new BaseException(ErrorCode.NOT_EXPORT_DATA);
+ }
+ response.setContentType("multipart/form-data");
+ response.setCharacterEncoding("utf-8");
+ // 杩欓噷URLEncoder.encode鍙互闃叉涓枃涔辩爜 褰撶劧鍜宔asyexcel娌℃湁鍏崇郴
+ String fileName = URLEncoder.encode("鍒朵护鍗曠敓浜т俊鎭�" + DateUtil.format(new Date(), "yyyyMMddHHmmss") + ".xlsx", "UTF-8");
+ response.setHeader("Content-disposition", "attachment;filename=" + fileName);
+ List<ManufacturingOrderEntity> manufacturingOrderEntityList = new ArrayList<>();
+ for (int i = 0; i < manufacturingCommandSheet.getRows(); i++) {
+ FieldSetEntity fieldSetEntity = manufacturingCommandSheet.getFieldSetEntity(i);
+ Map<Object, Object> values = fieldSetEntity.getValues();
+ JSONObject jsonObject = new JSONObject((Map) values);
- ManufacturingOrderEntity manufacturingOrderEntity = jsonObject.toJavaObject(ManufacturingOrderEntity.class);
- manufacturingOrderEntityList.add(manufacturingOrderEntity);
- }
- ServletOutputStream outputStream = response.getOutputStream();
- ExcelWriter excelWriter = EasyExcel.write(outputStream, ManufacturingOrderEntity.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
- try {
- // 鍘昏皟鐢ㄥ啓鍏�,杩欓噷鎴戣皟鐢ㄤ簡浜旀锛屽疄闄呬娇鐢ㄦ椂鏍规嵁鏁版嵁搴撳垎椤电殑鎬荤殑椤垫暟鏉ャ�傝繖閲屾渶缁堜細鍐欏埌5涓猻heet閲岄潰
- // 姣忔閮借鍒涘缓writeSheet 杩欓噷娉ㄦ剰蹇呴』鎸囧畾sheetNo 鑰屼笖sheetName蹇呴』涓嶄竴鏍�
- WriteSheet writeSheet = EasyExcel.writerSheet(1, "鍒朵护鍗曚俊鎭�").build();
- // 鍒嗛〉鍘绘暟鎹簱鏌ヨ鏁版嵁 杩欓噷鍙互鍘绘暟鎹簱鏌ヨ姣忎竴椤电殑鏁版嵁
- excelWriter.write(manufacturingOrderEntityList, writeSheet);
+ ManufacturingOrderEntity manufacturingOrderEntity = jsonObject.toJavaObject(ManufacturingOrderEntity.class);
+ manufacturingOrderEntityList.add(manufacturingOrderEntity);
+ }
+ ServletOutputStream outputStream = response.getOutputStream();
+ ExcelWriter excelWriter = EasyExcel.write(outputStream, ManufacturingOrderEntity.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
+ try {
+ // 鍘昏皟鐢ㄥ啓鍏�,杩欓噷鎴戣皟鐢ㄤ簡浜旀锛屽疄闄呬娇鐢ㄦ椂鏍规嵁鏁版嵁搴撳垎椤电殑鎬荤殑椤垫暟鏉ャ�傝繖閲屾渶缁堜細鍐欏埌5涓猻heet閲岄潰
+ // 姣忔閮借鍒涘缓writeSheet 杩欓噷娉ㄦ剰蹇呴』鎸囧畾sheetNo 鑰屼笖sheetName蹇呴』涓嶄竴鏍�
+ WriteSheet writeSheet = EasyExcel.writerSheet(1, "鍒朵护鍗曚俊鎭�").build();
+ // 鍒嗛〉鍘绘暟鎹簱鏌ヨ鏁版嵁 杩欓噷鍙互鍘绘暟鎹簱鏌ヨ姣忎竴椤电殑鏁版嵁
+ excelWriter.write(manufacturingOrderEntityList, writeSheet);
// writeSheet = EasyExcel.writerSheet(2, "鍒朵护鍗曚俊鎭�").build();
// excelWriter.write(manufacturingOrderEntityList, writeSheet);
// writeSheet = EasyExcel.writerSheet(3, "鍒朵护鍗曚俊鎭�").build();
// excelWriter.write(manufacturingOrderEntityList, writeSheet);
- String process = fse.getString("process");
+ 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];
- FieldSetEntity ff = new FieldSetEntity();
- ff.setTableName("temp");
- ff.setValue("process", process);
- ff.setValue("mo_number", moNumber);
- DataTableEntity processSheet = getProcessSheet(ff);
- if (DataTableEntity.isEmpty(processSheet)) {
- continue;
- }
- fileName = moNumber + "鐨勭敓浜ф槑缁�";
- writeSheet = EasyExcel.writerSheet(i + 1, 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());
- ProductionDetailEntity productionDetail = jsonObject.toJavaObject(ProductionDetailEntity.class);
- productionDetailEntities.add(productionDetail);
- }
- excelWriter.write(productionDetailEntities, writeSheet);
- }
- } catch (Exception e) {
- throw e;
- } finally {
- excelWriter.finish();
- outputStream.flush();
- outputStream.close();
- }
- }
+ 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];
+ FieldSetEntity ff = new FieldSetEntity();
+ ff.setTableName("temp");
+ ff.setValue("process", process);
+ ff.setValue("mo_number", moNumber);
+ DataTableEntity processSheet = getProcessSheet(ff);
+ if (DataTableEntity.isEmpty(processSheet)) {
+ continue;
+ }
+ fileName = moNumber + "鐨勭敓浜ф槑缁�";
+ writeSheet = EasyExcel.writerSheet(i + 1, 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());
+ ProductionDetailEntity productionDetail = jsonObject.toJavaObject(ProductionDetailEntity.class);
+ productionDetailEntities.add(productionDetail);
+ }
+ excelWriter.write(productionDetailEntities, writeSheet);
+ }
+ } catch (Exception e) {
+ throw e;
+ } finally {
+ excelWriter.finish();
+ outputStream.flush();
+ outputStream.close();
+ }
+ }
- /**
- * 宸ュ簭鍒楄〃鑾峰彇
- *
- * @return
- */
- public DataTableEntity getProcessList() {
- StringBuilder sql = new StringBuilder();
- sql.append(" SELECT ");
- sql.append(" group_next ");
- sql.append(" FROM da_t_co_route_control where state_flag <> '2' AND group_next <> '0' AND isrotegroup = 'Y' ");
- sql.append(" GROUP BY group_next ");
- return getBaseDao().listTable(sql.toString(), new Object[]{});
- }
+ /**
+ * 宸ュ簭鍒楄〃鑾峰彇
+ *
+ * @return
+ */
+ public DataTableEntity getProcessList() {
+ StringBuilder sql = new StringBuilder();
+ sql.append(" SELECT ");
+ sql.append(" group_next ");
+ sql.append(" FROM da_t_co_route_control where state_flag <> '2' AND group_next <> '0' AND isrotegroup = 'Y' ");
+ sql.append(" GROUP BY group_next ");
+ return getBaseDao().listTable(sql.toString(), new Object[]{});
+ }
- // T_CO_ROUTE_CONTROL T_PM_MO_BASE T_CO_CUSTOMER T_CO_AREA T_PM_PROJECT_BASE T_CO_ITEM T_WIP_STATION_REC
- // T_PM_PRODUCT T_WIP_TRACKING T_CO_EMP_DESC T_SMO_TECHNICS
- // T_WIP_ERROR_SCRAP T_WIP_DETAIL
+ // T_CO_ROUTE_CONTROL T_PM_MO_BASE T_CO_CUSTOMER T_CO_AREA T_PM_PROJECT_BASE T_CO_ITEM T_WIP_STATION_REC
+ // T_PM_PRODUCT T_WIP_TRACKING T_CO_EMP_DESC T_SMO_TECHNICS
+ // T_WIP_ERROR_SCRAP T_WIP_DETAIL
- public void test() {
- DataBaseEntity dataBaseEntity = new DataBaseEntity("ef7b1348-b2ec-40d1-a0b8-76cd29b99a1232d");
- Dao dao = dataBaseEntity.getDao();
- double ceil = Math.ceil(48548455 / 500);
- try {
- Connection connection = dao.getConnection();
- PreparedStatement pst = connection.prepareStatement("\n" +
- "\n" +
- " update T_PM_PRODUCT_SN SET CREATE_DATE=to_date((select '20'||(select trunc(dbms_random.value(17,22)) from dual)||'-'||(select trunc(dbms_random.value(1,10)) from dual)||'-'||(select trunc(dbms_random.value(1,28)) from dual)||' 00:00:00'\n" +
- "\t from dual),'yyyy-mm-dd hh24:mi:ss')\n" +
- "\t where product_sn in (\n" +
- "\t\tSELECT PRODUCT_SN from T_PM_PRODUCT_SN where CREATE_DATE=TO_DATE('2022-11-17 16:30:28','yyyy-mm-dd hh24:mi:ss') and rownum<=500\n" +
- "\t )");
- for (int i = 0; i < ceil; i++) {
+ public void test() {
+ DataBaseEntity dataBaseEntity = new DataBaseEntity("ef7b1348-b2ec-40d1-a0b8-76cd29b99a1232d");
+ Dao dao = dataBaseEntity.getDao();
+ double ceil = Math.ceil(48548455 / 500);
+ try {
+ Connection connection = dao.getConnection();
+ PreparedStatement pst = connection.prepareStatement("\n" +
+ "\n" +
+ " update T_PM_PRODUCT_SN SET CREATE_DATE=to_date((select '20'||(select trunc(dbms_random.value(17,22)) from dual)||'-'||(select trunc(dbms_random.value(1,10)) from dual)||'-'||(select trunc(dbms_random.value(1,28)) from dual)||' 00:00:00'\n" +
+ "\t from dual),'yyyy-mm-dd hh24:mi:ss')\n" +
+ "\t where product_sn in (\n" +
+ "\t\tSELECT PRODUCT_SN from T_PM_PRODUCT_SN where CREATE_DATE=TO_DATE('2022-11-17 16:30:28','yyyy-mm-dd hh24:mi:ss') and rownum<=500\n" +
+ "\t )");
+ for (int i = 0; i < ceil; i++) {
- pst.execute();
- System.out.println("鍓╀綑--" + (ceil - i + 1));
- }
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
+ pst.execute();
+ System.out.println("鍓╀綑--" + (ceil - i + 1));
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ }
- /**
- * 鑾峰彇鍒朵护鍗曚俊鎭�
- *
- * @param fse
- * @return
- * @throws BaseException
- */
- public DataTableEntity getManufacturingCommandSheet(FieldSetEntity fse) throws BaseException {
- Integer cpage = fse.getInteger("cpage");
- if (cpage == null || cpage <= 0) {
- cpage = 1;
- }
- Integer pageSize = fse.getInteger("page_size");
- if (pageSize == null || pageSize <= 0) {
- pageSize = Integer.MAX_VALUE;
- }
- //杩戞湡
- String queryTime = fse.getString("query_time");
- String timeValue = fse.getString("time_value");
- String areaName = fse.getString("areaname");
- String productMaterial = fse.getString("product_material");
- String moNumber = fse.getString("mo_number");
+ /**
+ * 鑾峰彇鍒朵护鍗曚俊鎭�
+ *
+ * @param fse
+ * @return
+ * @throws BaseException
+ */
+ public DataTableEntity getManufacturingCommandSheet(FieldSetEntity fse) throws BaseException {
+ Integer cpage = fse.getInteger("cpage");
+ if (cpage == null || cpage <= 0) {
+ cpage = 1;
+ }
+ Integer pageSize = fse.getInteger("page_size");
+ if (pageSize == null || pageSize <= 0) {
+ pageSize = Integer.MAX_VALUE;
+ }
+ //杩戞湡
+ String queryTime = fse.getString("query_time");
+ String timeValue = fse.getString("time_value");
+ String areaName = fse.getString("areaname");
+ String productMaterial = fse.getString("product_material");
+ String moNumber = fse.getString("mo_number");
// String projectId = fse.getString("project_id");
- String closeFlag = fse.getString("close_flag");
- String timeFilter = fse.getString("time_filter");
- String timeField = fse.getString("timeField");
- if (StringUtils.isAllEmpty(areaName, productMaterial, moNumber, productMaterial)
- && ((!"1".equals(timeFilter) || StringUtils.isEmpty(timeValue)) && !"1".equals(queryTime))) {
+ String closeFlag = fse.getString("close_flag");
+ String timeFilter = fse.getString("time_filter");
+ String timeField = fse.getString("timeField");
+ if (StringUtils.isAllEmpty(areaName, productMaterial, moNumber, productMaterial)
+ && ((!"1".equals(timeFilter) || StringUtils.isEmpty(timeValue)) && !"1".equals(queryTime))) {
- throw new BaseException(ErrorCode.REPORT_SELECT_TIME_FILTER_CANNOT_EMPTY);
- }
- StringBuilder sql = new StringBuilder(128);
- sql.append("\n select distinct ");
- sql.append("\n p.project_id as project_name , ");//--宸ュ崟鍙�,
- sql.append("\n p.destroy_no , ");//--璁㈠崟鍙�,
- sql.append("\n p.product_material , ");//--鏈虹,
- sql.append("\n m.co_item_name as product_name , ");//--鍝佸悕,
- sql.append("\n m.co_item_spec as product_standard , ");//--瑙勬牸,
+ throw new BaseException(ErrorCode.REPORT_SELECT_TIME_FILTER_CANNOT_EMPTY);
+ }
+ StringBuilder sql = new StringBuilder(128);
+ sql.append("\n select distinct ");
+ sql.append("\n p.project_id as project_name , ");//--宸ュ崟鍙�,
+ sql.append("\n p.destroy_no , ");//--璁㈠崟鍙�,
+ sql.append("\n p.product_material , ");//--鏈虹,
+ sql.append("\n m.co_item_name as product_name , ");//--鍝佸悕,
+ sql.append("\n m.co_item_spec as product_standard , ");//--瑙勬牸,
// sql.append("\n c.customer , ");//--瀹㈡埛鍚嶇О,
- sql.append("\n ifnull(p.product_count,0) product_count ,");//--宸ュ崟鏁伴噺,
- sql.append("\n t.mo_number , ");//--鍒朵护鍗曞彿,
- sql.append("\n case ");
- sql.append("\n when process_face = 'a' then '鍗曢潰' ");
- sql.append("\n when process_face = 's' then '姝ｉ潰' ");
- sql.append("\n when process_face = 'c' then '鍙嶉潰' ");
- sql.append("\n else '' end process_face , ");//--鍔犲伐闈�,
- sql.append("\n case ");
- sql.append("\n when master_flag='Y' then '鏄�' ");
- sql.append("\n else '鍚�' end master_flag , ");//--浜у嚭鏍囪瘑,
- sql.append("\n a.areaname , ");//--鍖哄煙鍚嶇О,
- sql.append("\n t.target_qty , ");//--鐩爣鏁伴噺,
- sql.append("\n t.input_qty ,");//--鎶曞叆鏁伴噺,
- sql.append("\n t.output_qty , ");//--浜у嚭鏁伴噺,
- sql.append("\n t.input_qty-t.output_qty wip_qty , ");//--鍦ㄥ埗鍝佹暟閲�,
- sql.append("\n t.total_scrap_qty , ");//--浣滃簾鏁伴噺,
- sql.append("\n ifnull(t.tp_number,0) tp_number , ");//--鍗曠墖鐐规暟,
- sql.append("\n ifnull(t.output_qty *t.tp_number,0) totoaltp_number, ");//--鎬荤偣鏁�,
- sql.append("\n t.mo_create_date , ");//--寮�绔嬫椂闂�,
- sql.append("\n t.mo_start_date , ");//--瀹為檯鎶曞叆鏃堕棿,
- sql.append("\n t.mo_due_date , ");//--棰勫叧缁撴椂闂�,
- sql.append("\n t.mo_close_date ");//--鍏崇粨鏃堕棿
- sql.append("\n from ");
- sql.append("\n [={da_t_pm_mo_base}{areaid,project_id,master_flag,process_face,target_qty,input_qty,output_qty,total_scrap_qty");
- sql.append(",tp_number,mo_create_date,mo_start_date,mo_due_date,mo_close_date");
- sql.append(",close_flag,mo_number}");
- boolean isF = false;
- if ("1".equals(queryTime) && (StringUtils.isEmpty(timeValue) || !"1".equals(timeFilter))) {
- //娌℃湁鏃堕棿鏉′欢涓斿嬀閫変簡鍙煡鐪嬭繎涓ゅ勾鐨勬暟鎹�
-// DateTime offset = DateUtil.offset(new Date(), DateField.YEAR, -2);
-// sql.append(" {where ( mo_create_date >= str_to_date('" + DateUtil.format(offset, "yyyy-MM-dd") + "','%Y-%m-%d') )}");
-// filterParams.add(DateUtil.format(offset, "yyyy-MM-dd"));
- } else if (!StringUtils.isEmpty(timeValue) && "1".equals(timeFilter)) {
- //鏈夋椂闂存潯浠舵牴鎹敤鎴烽�夋嫨鐨勬椂闂磋繃婊�
- String[] times = timeValue.split(",");
- String startTime = times[0];
- String endTime = times[1];
- sql.append(" {WHERE");
- if ("production_time".equals(timeField)) {
- //鐗规畩锛� 鐢熶骇鏃堕棿
- sql.append(" (mo_number in (SELECT mo_number from [={da_t_wip_station_rec}{mo_number}");
- sql.append("{where create_date >=str_to_date('" + startTime + "','%Y-%m-%d %H:%i:%s') and create_date<=str_to_date('" + endTime + "','%Y-%m-%d %H:%i:%s')}=] ");
- sql.append(" ) ");
- } else {
- //鏃堕棿鏉′欢
- sql.append("\n (").append(timeField).append(">= str_to_date('" + startTime + "','%Y-%m-%d %H:%i:%s')");
- sql.append("\n and ").append(timeField).append("<=str_to_date('" + endTime + "','%Y-%m-%d %H:%i:%s') )");
- }
- sql.append("}");
- }
- sql.append("=] t ");
+ sql.append("\n ifnull(p.product_count,0) product_count ,");//--宸ュ崟鏁伴噺,
+ sql.append("\n t.mo_number , ");//--鍒朵护鍗曞彿,
+ sql.append("\n case ");
+ sql.append("\n when process_face = 'a' then '鍗曢潰' ");
+ sql.append("\n when process_face = 's' then '姝ｉ潰' ");
+ sql.append("\n when process_face = 'c' then '鍙嶉潰' ");
+ sql.append("\n else '' end process_face , ");//--鍔犲伐闈�,
+ sql.append("\n case ");
+ sql.append("\n when master_flag='Y' then '鏄�' ");
+ sql.append("\n else '鍚�' end master_flag , ");//--浜у嚭鏍囪瘑,
+ sql.append("\n a.areaname , ");//--鍖哄煙鍚嶇О,
+ sql.append("\n t.target_qty , ");//--鐩爣鏁伴噺,
+ sql.append("\n t.input_qty ,");//--鎶曞叆鏁伴噺,
+ sql.append("\n t.output_qty , ");//--浜у嚭鏁伴噺,
+ sql.append("\n t.input_qty-t.output_qty wip_qty , ");//--鍦ㄥ埗鍝佹暟閲�,
+ sql.append("\n t.total_scrap_qty , ");//--浣滃簾鏁伴噺,
+ sql.append("\n ifnull(t.tp_number,0) tp_number , ");//--鍗曠墖鐐规暟,
+ sql.append("\n ifnull(t.output_qty *t.tp_number,0) totoaltp_number, ");//--鎬荤偣鏁�,
+ sql.append("\n t.mo_create_date , ");//--寮�绔嬫椂闂�,
+ sql.append("\n t.mo_start_date , ");//--瀹為檯鎶曞叆鏃堕棿,
+ sql.append("\n t.mo_due_date , ");//--棰勫叧缁撴椂闂�,
+ sql.append("\n t.mo_close_date ");//--鍏崇粨鏃堕棿
+ sql.append("\n from ");
+ sql.append("\n [={da_t_pm_mo_base}{areaid,project_id,master_flag,process_face,target_qty,input_qty,output_qty,total_scrap_qty");
+ sql.append(",tp_number,mo_create_date,mo_start_date,mo_due_date,mo_close_date");
+ sql.append(",close_flag,mo_number}");
+ boolean isF = false;
+// if ("1".equals(queryTime) && (StringUtils.isEmpty(timeValue) || !"1".equals(timeFilter))) {
+// //娌℃湁鏃堕棿鏉′欢涓斿嬀閫変簡鍙煡鐪嬭繎涓ゅ勾鐨勬暟鎹�
+//// DateTime offset = DateUtil.offset(new Date(), DateField.YEAR, -2);
+//// sql.append(" {where ( mo_create_date >= str_to_date('" + DateUtil.format(offset, "yyyy-MM-dd") + "','%Y-%m-%d') )}");
+//// filterParams.add(DateUtil.format(offset, "yyyy-MM-dd"));
+// } else
+ if (!StringUtils.isEmpty(timeValue) && "1".equals(timeFilter)) {
+ //鏈夋椂闂存潯浠舵牴鎹敤鎴烽�夋嫨鐨勬椂闂磋繃婊�
+ String[] times = timeValue.split(",");
+ String startTime = times[0];
+ String endTime = times[1];
+// sql.append(" (mo_number in (SELECT mo_number from [={da_t_wip_station_rec}{mo_number}");
+// sql.append("{where create_date >=str_to_date('" + startTime + "','%Y-%m-%d %H:%i:%s') and create_date<=str_to_date('" + endTime + "','%Y-%m-%d %H:%i:%s')}=] ");
+// sql.append(" ) ");
+ //鏃堕棿鏉′欢
+ if (!"production_time".equals(timeField)) {
+ sql.append(" {WHERE");
+ sql.append("\n (").append(timeField).append(">= str_to_date('" + startTime + "','%Y-%m-%d %H:%i:%s')");
+ sql.append("\n and ").append(timeField).append("<=str_to_date('" + endTime + "','%Y-%m-%d %H:%i:%s') )");
+
+ }
+ sql.append("}=] t");
+ if ("production_time".equals(timeField)) {
+ //鐗规畩锛� 鐢熶骇鏃堕棿
+ sql.append(" JOIN [={da_t_wip_station_rec}{mo_number}{where create_date >=str_to_date('" + startTime + "','%Y-%m-%d %H:%i:%s') and create_date<=str_to_date('" + endTime + "','%Y-%m-%d %H:%i:%s')}=] rec ON rec.mo_number = t.mo_number ");
+ }
+ } else {
+ sql.append("=] t ");
+ }
+
// sql.append("\n left join [=da_t_co_customer=] c on t.cust_code = c.cust_code ");
- sql.append("\n left join da_t_co_area a on t.areaid = a.areaid ");
- sql.append("\n left join [={da_t_pm_project_base}{project_base_id,product_material");
- sql.append(",project_id,destroy_no,product_count");
- sql.append("}=] p on p.project_base_id = t.project_id ");
- sql.append("\n left join [={da_t_co_item}{co_item_code,co_item_name,co_item_spec}=] m on p.product_material = m.co_item_code ");
+ sql.append("\n left join da_t_co_area a on t.areaid = a.areaid ");
+ sql.append("\n left join [={da_t_pm_project_base}{project_base_id,product_material");
+ sql.append(",project_id,destroy_no,product_count");
+ sql.append("}=] p on p.project_base_id = t.project_id ");
+ sql.append("\n left join [={da_t_co_item}{co_item_code,co_item_name,co_item_spec}=] m on p.product_material = m.co_item_code ");
+ boolean isFilter = false;
+ StringBuilder sqlFilter = new StringBuilder();
+ List<Object> filterParams = new ArrayList<>();
- boolean isFilter = false;
- StringBuilder sqlFilter = new StringBuilder();
- List<Object> filterParams = new ArrayList<>();
+ isFilter = appendLikeFilter(areaName, "a.areaname", sqlFilter, filterParams, isFilter);
+ isFilter = appendLikeFilter(productMaterial, "p.product_material", false, sqlFilter, filterParams, isFilter);
+ isFilter = appendLikeFilter(moNumber, "t.mo_number", sqlFilter, filterParams, isFilter);
+ if (!"-1".equals(closeFlag)) {
+ isFilter = appendLikeFilter(closeFlag, "t.close_flag", false, sqlFilter, filterParams, isFilter);
+ }
- isFilter = appendLikeFilter(areaName, "a.areaname", sqlFilter, filterParams, isFilter);
- isFilter = appendLikeFilter(productMaterial, "p.product_material", false, sqlFilter, filterParams, isFilter);
- isFilter = appendLikeFilter(moNumber, "t.mo_number", sqlFilter, filterParams, isFilter);
- if (!"-1".equals(closeFlag)) {
- isFilter = appendLikeFilter(closeFlag, "t.close_flag", false, sqlFilter, filterParams, isFilter);
- }
+ if (isFilter) {
+ sql.append(" where (").append(sqlFilter).append(") ");
+ }
+ sql.append(" order by mo_create_date");
+ System.out.println(sql);
+ String parseSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString(), 2);
+ DataTableEntity dataTableEntity = getBaseDao().listTable(parseSql, filterParams.toArray(), pageSize, cpage);
- if (isFilter) {
- sql.append(" where (").append(sqlFilter).append(") ");
- }
- sql.append(" order by mo_create_date");
- String parseSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString(), 2);
- DataTableEntity dataTableEntity = getBaseDao().listTable(parseSql, filterParams.toArray(), pageSize, cpage);
+ return dataTableEntity;
+ }
- return dataTableEntity;
- }
+ public DataTableEntity getProductSheet(FieldSetEntity fse) throws BaseException {
+ String trackingTable = "da_t_wip_tracking";
+ 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<>();
- public DataTableEntity getProductSheet(FieldSetEntity fse) throws BaseException {
- String bak = fse.getString("~bak~");
- String trackingTable = "da_t_wip_tracking";
- if ("1".equals(bak)) {
- trackingTable = "da_t_wip_tracking_bak";
- }
- 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);
- 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,display_group,item_count,routeendflage,depanel_flag,wait_scrap_qty");
- sql.append("lot_number,in_station_time,serial_number,group_name,error_flag,group_name,repair,container_sn,in_station_time");
- sql.append("}{where mo_number='" + moNumber + "' and t.number_type='" + numberType + "' and display_group='" + groupNext + "' and t.item_count!=0 and routeendflage<>'Y'}=] t ");
- sql.append("\n left join da_t_co_emp_desc e ");
- sql.append("\n on t.emp_no=e.emp_no ");
+ 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,display_group,item_count,routeendflage,depanel_flag,wait_scrap_qty");
+ sql.append("lot_number,in_station_time,serial_number,group_name,error_flag,group_name,repair,container_sn,in_station_time");
+ sql.append("}{where mo_number='" + moNumber + "' and t.number_type='" + numberType + "' and display_group='" + groupNext + "' and t.item_count!=0 and routeendflage<>'Y'}=] 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.display_group=? ");
// sql.append("\n and t.item_count!=0 and routeendflage<>'Y' ");
- 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,group_name,repair,container_sn");
- sql.append(",number_type,mo_number,item_count,routeendflage,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 routeendflage<>'Y' 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,group_name,item_count,repair");
- sql.append(",container_sn,serial_number,wait_scrap_qty,depanel_flag,number_type,emp_no}");
+ 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,group_name,repair,container_sn");
+ sql.append(",number_type,mo_number,item_count,routeendflage,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 routeendflage<>'Y' 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,group_name,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 + "' and routeendflage='Y' ");
- 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 ");
+ sql.append("\n {where number_type='" + numberType + "' and mo_number='" + moNumber + "' and routeendflage='Y' ");
+ 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,display_group,routeendflage}");
- sql.append("{ WHERE number_type='" + numberType + "' and display_group='" + groupNext + "' and mo_number='" + moNumber + "' and routeendflage<>'Y' }");
- 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 ");
+ } 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,display_group,routeendflage}");
+ sql.append("{ WHERE number_type='" + numberType + "' and display_group='" + groupNext + "' and mo_number='" + moNumber + "' and routeendflage<>'Y' }");
+ 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.display_group=? ");
// sql.append("\n and routeendflage<>'Y' ");
- 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,display_group,routeendflage}{ where number_type='" + numberType + "' and mo_number='" + moNumber + "' and group_name='" + groupNext + "'");
- sql.append(" and routeendflage<>'Y' 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 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,display_group,routeendflage}{ where number_type='" + numberType + "' and mo_number='" + moNumber + "' and group_name='" + groupNext + "'");
+ sql.append(" and routeendflage<>'Y' 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 routeendflage<>'Y' and error_flag='1' ");
- sql.append("\n order by error_flag asc,in_station_time asc ");
+ 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 + "' and routeendflage='Y'}");
- sql.append("=] t ");
- sql.append("\n left join ");
- sql.append("\n da_t_co_emp_desc e on t.emp_no=e.emp_no ");
+ } 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 + "' and routeendflage='Y'}");
+ 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=? and routeendflage='Y' ");
- sql.append("\n order by error_flag asc,in_station_time asc ");
+ sql.append("\n order by error_flag asc,in_station_time asc ");
- } else {
- return null;
- }
- if (!"娴佺▼缁撴潫".equals(groupNext)) {
- params.add(scrap);
- params.add(lend);
+ } 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(scrap);
+ params.add(lend);
// params.add(numberType);
// params.add(moNumber);
// params.add(groupNext);
- } else {
- params.add(scrap);
- params.add(lend);
+ } 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;
- }
+ }
+ System.out.println(sql);
+ String parseSql1 = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString(), 2);
+ return getBaseDao().listTable(parseSql1, params.toArray());
+ }
+ return null;
+ }
- /**
- * 宸ュ簭鎶ヨ〃
- *
- * @param fse
- * @return
- * @throws BaseException
- */
- public DataTableEntity getProcessSheet(FieldSetEntity fse) throws BaseException {
- String bak = fse.getString("~bak~");
- String detailTable = "da_t_wip_detail";
- String trackTable = "da_t_wip_tracking";
- if ("1".equals(bak)) {
- detailTable = "da_t_wip_detail_bak";
- trackTable = "da_t_wip_tracking_bak";
- }
- //鍒朵护鍗曞彿
- String moNumber = fse.getString("mo_number");
- //宸ュ簭
- String process = fse.getString("process");
- StringBuilder sql = new StringBuilder(128);
- sql.append("\n select ");
- sql.append("\n * ");
- sql.append("\n from ");
- sql.append("\n ( ");
- sql.append("\n select ");
- sql.append("\n c.* ");
- sql.append("\n from ");
- sql.append("\n ( ");
- sql.append("\n select ");
- sql.append("\n a.*, ");
- sql.append("\n ifnull( c1.wait_scrap_qty_all, 0 ) wait_scrap_qty_all, ");
- sql.append("\n ifnull( l.pass_itemcount, 0 ) pass_itemcount, ");
- sql.append("\n ifnull( k.second_passcount, 0 ) second_passcount, ");
- sql.append("\n ifnull( m.use_itemcount, 0 ) use_itemcount, ");
- sql.append("\n ifnull( n.error_itemcount, 0 ) error_itemcount ");
- sql.append("\n from ");
- sql.append("\n ( ");
- sql.append("\n select ");
- sql.append("\n v.route_code, ");
- sql.append("\n v.group_next, ");
- sql.append("\n min( v.step_sequence ) as step_sequence, ");
- sql.append("\n min( state_flag ) as state_flag, ");
- sql.append("\n v.mo_number ");
- sql.append("\n from ");
- sql.append("\n ( ");
- sql.append("\n select ");
- sql.append("\n a.route_groupid, ");
- sql.append("\n a.route_code, ");
- sql.append("\n a.group_code, ");
- sql.append("\n a.group_name, ");
- sql.append("\n a.group_next, ");
- sql.append("\n a.state_flag, ");
- sql.append("\n a.step_sequence, ");
- sql.append("\n a.ismustpass, ");
- sql.append("\n a.isrotegroup, ");
- sql.append("\n a.ms_code, ");
- sql.append("\n a.route_inout_flag, ");
- sql.append("\n a.route_cycle_time, ");
- sql.append("\n b.technicsid, ");
- sql.append("\n c.mo_number ");
- sql.append("\n from ");
- sql.append("\n da_t_co_route_control a ");
- sql.append("\n left join da_t_smo_technics b on a.route_code = b.route_code ");
- sql.append("\n join [={da_t_pm_mo_base b1}{technicsid,mo_number}{ where mo_number = '" + moNumber + "' }=] c on b.technicsid = c.technicsid ");
- sql.append("\n ) v ");
- sql.append("\n left join da_t_co_group g on v.group_next = g.group_name ");
- sql.append("\n where ");
+ /**
+ * 宸ュ簭鎶ヨ〃
+ *
+ * @param fse
+ * @return
+ * @throws BaseException
+ */
+ public DataTableEntity getProcessSheet(FieldSetEntity fse) throws BaseException {
+ String detailTable = "da_t_wip_detail";
+ String trackTable = "da_t_wip_tracking";
+ //鍒朵护鍗曞彿
+ String moNumber = fse.getString("mo_number");
+ //宸ュ簭
+ String process = fse.getString("process");
+ StringBuilder sql = new StringBuilder(128);
+ sql.append("\n select ");
+ sql.append("\n * ");
+ sql.append("\n from ");
+ sql.append("\n ( ");
+ sql.append("\n select ");
+ sql.append("\n c.* ");
+ sql.append("\n from ");
+ sql.append("\n ( ");
+ sql.append("\n select ");
+ sql.append("\n a.*, ");
+ sql.append("\n ifnull( c1.wait_scrap_qty_all, 0 ) wait_scrap_qty_all, ");
+ sql.append("\n ifnull( l.pass_itemcount, 0 ) pass_itemcount, ");
+ sql.append("\n ifnull( k.second_passcount, 0 ) second_passcount, ");
+ sql.append("\n ifnull( m.use_itemcount, 0 ) use_itemcount, ");
+ sql.append("\n ifnull( n.error_itemcount, 0 ) error_itemcount ");
+ sql.append("\n from ");
+ sql.append("\n ( ");
+ sql.append("\n select ");
+ sql.append("\n v.route_code, ");
+ sql.append("\n v.group_next, ");
+ sql.append("\n min( v.step_sequence ) as step_sequence, ");
+ sql.append("\n min( state_flag ) as state_flag, ");
+ sql.append("\n v.mo_number ");
+ sql.append("\n from ");
+ sql.append("\n ( ");
+ sql.append("\n select ");
+ sql.append("\n a.route_groupid, ");
+ sql.append("\n a.route_code, ");
+ sql.append("\n a.group_code, ");
+ sql.append("\n a.group_name, ");
+ sql.append("\n a.group_next, ");
+ sql.append("\n a.state_flag, ");
+ sql.append("\n a.step_sequence, ");
+ sql.append("\n a.ismustpass, ");
+ sql.append("\n a.isrotegroup, ");
+ sql.append("\n a.ms_code, ");
+ sql.append("\n a.route_inout_flag, ");
+ sql.append("\n a.route_cycle_time, ");
+ sql.append("\n b.technicsid, ");
+ sql.append("\n c.mo_number ");
+ sql.append("\n from ");
+ sql.append("\n da_t_co_route_control a ");
+ sql.append("\n left join da_t_smo_technics b on a.route_code = b.route_code ");
+ sql.append("\n join [={da_t_pm_mo_base b1}{technicsid,mo_number}{ where mo_number = '" + moNumber + "' }=] c on b.technicsid = c.technicsid ");
+ sql.append("\n ) v ");
+ sql.append("\n left join da_t_co_group g on v.group_next = g.group_name ");
+ sql.append("\n where ");
// sql.append("\n v.mo_number = '"+moNumber+"' ");
- sql.append("\n v.state_flag <> '2' ");
- sql.append("\n and v.group_next <> '0' ");
- sql.append("\n and v.isrotegroup = 'Y' ");
- sql.append("\n group by ");
- sql.append("\n v.route_code, ");
- sql.append("\n v.group_next, ");
- sql.append("\n v.mo_number ");
- sql.append("\n order by ");
- sql.append("\n v.route_code, ");
- sql.append("\n v.group_next, ");
- sql.append("\n v.mo_number ");
- sql.append("\n ) a ");
- sql.append("\n left join ( select scrap_process, mo_number, sum( ng_count ) wait_scrap_qty_all from da_t_wip_error_scrap where mo_number = '" + moNumber + "' group by scrap_process, mo_number ) c1 on a.group_next = c1.scrap_process ");
- sql.append("\n and a.mo_number = c1.mo_number ");
- sql.append("\n left join ( ");
- sql.append("\n select ");
- sql.append("\n count( distinct a.serial_number ) pass_itemcount, ");
- sql.append("\n a.group_name, ");
- sql.append("\n a.mo_number ");
- sql.append("\n from ");
- sql.append("\n [={" + detailTable + " d1}{serial_number,group_name,mo_number,reflux_flag}{ where mo_number='" + moNumber + "' and reflux_flag='N' }=] a ");
+ sql.append("\n v.state_flag <> '2' ");
+ sql.append("\n and v.group_next <> '0' ");
+ sql.append("\n and v.isrotegroup = 'Y' ");
+ sql.append("\n group by ");
+ sql.append("\n v.route_code, ");
+ sql.append("\n v.group_next, ");
+ sql.append("\n v.mo_number ");
+ sql.append("\n order by ");
+ sql.append("\n v.route_code, ");
+ sql.append("\n v.group_next, ");
+ sql.append("\n v.mo_number ");
+ sql.append("\n ) a ");
+ sql.append("\n left join ( select scrap_process, mo_number, sum( ng_count ) wait_scrap_qty_all from da_t_wip_error_scrap where mo_number = '" + moNumber + "' group by scrap_process, mo_number ) c1 on a.group_next = c1.scrap_process ");
+ sql.append("\n and a.mo_number = c1.mo_number ");
+ sql.append("\n left join ( ");
+ sql.append("\n select ");
+ sql.append("\n count( distinct a.serial_number ) pass_itemcount, ");
+ sql.append("\n a.group_name, ");
+ sql.append("\n a.mo_number ");
+ sql.append("\n from ");
+ sql.append("\n [={" + detailTable + " d1}{serial_number,group_name,mo_number,reflux_flag}{ where mo_number='" + moNumber + "' and reflux_flag='N' }=] a ");
// sql.append("\n where ");
// sql.append("\n a.mo_number = '"+moNumber+"' ");
// sql.append("\n and a.reflux_flag = 'N' ");
- sql.append("\n group by ");
- sql.append("\n group_name, ");
- sql.append("\n mo_number ");
- sql.append("\n ) l on a.mo_number = l.mo_number ");
- sql.append("\n and a.group_next = l.group_name ");
- sql.append("\n left join ( ");
- sql.append("\n select ");
- sql.append("\n sum( item_count ) second_passcount, ");
- sql.append("\n group_name, ");
- sql.append("\n mo_number ");
- sql.append("\n from ");
- sql.append("\n ( ");
- sql.append("\n select ");
- sql.append("\n a.group_name, ");
- sql.append("\n a.mo_number, ");
- sql.append("\n a.serial_number, ");
- sql.append("\n min( item_count ) item_count ");
- sql.append("\n from ");
- sql.append("\n [={" + detailTable + " d2}{group_name,mo_number,serial_number,item_count,reflux_flag}{where mo_number='" + moNumber + "' and reflux_flag='Y'}=] a ");
+ sql.append("\n group by ");
+ sql.append("\n group_name, ");
+ sql.append("\n mo_number ");
+ sql.append("\n ) l on a.mo_number = l.mo_number ");
+ sql.append("\n and a.group_next = l.group_name ");
+ sql.append("\n left join ( ");
+ sql.append("\n select ");
+ sql.append("\n sum( item_count ) second_passcount, ");
+ sql.append("\n group_name, ");
+ sql.append("\n mo_number ");
+ sql.append("\n from ");
+ sql.append("\n ( ");
+ sql.append("\n select ");
+ sql.append("\n a.group_name, ");
+ sql.append("\n a.mo_number, ");
+ sql.append("\n a.serial_number, ");
+ sql.append("\n min( item_count ) item_count ");
+ sql.append("\n from ");
+ sql.append("\n [={" + detailTable + " d2}{group_name,mo_number,serial_number,item_count,reflux_flag}{where mo_number='" + moNumber + "' and reflux_flag='Y'}=] a ");
// sql.append("\n where ");
// sql.append("\n a.mo_number = '"+moNumber+"' ");
// sql.append("\n and reflux_flag = 'Y' ");
- sql.append("\n group by ");
- sql.append("\n a.group_name, ");
- sql.append("\n a.mo_number, ");
- sql.append("\n a.serial_number ");
- sql.append("\n ) tttt ");
- sql.append("\n group by ");
- sql.append("\n group_name, ");
- sql.append("\n mo_number ");
- sql.append("\n ) k on a.mo_number = k.mo_number ");
- sql.append("\n and a.group_next = k.group_name ");
- sql.append("\n left join ( ");
- sql.append("\n select ");
- sql.append("\n display_group, ");
- sql.append("\n mo_number, ");
- sql.append("\n sum( item_count ) use_itemcount ");
- sql.append("\n from ");
- sql.append("\n [={"+trackTable+" t1}{display_group,mo_number,item_count,routeendflage,depanel_flag}{ where mo_number='" + moNumber + "' and routeendflage <> 'Y' and depanel_flag <> 'Y' }=] ");
+ sql.append("\n group by ");
+ sql.append("\n a.group_name, ");
+ sql.append("\n a.mo_number, ");
+ sql.append("\n a.serial_number ");
+ sql.append("\n ) tttt ");
+ sql.append("\n group by ");
+ sql.append("\n group_name, ");
+ sql.append("\n mo_number ");
+ sql.append("\n ) k on a.mo_number = k.mo_number ");
+ sql.append("\n and a.group_next = k.group_name ");
+ sql.append("\n left join ( ");
+ sql.append("\n select ");
+ sql.append("\n display_group, ");
+ sql.append("\n mo_number, ");
+ sql.append("\n sum( item_count ) use_itemcount ");
+ sql.append("\n from ");
+ sql.append("\n [={" + trackTable + " t1}{display_group,mo_number,item_count,routeendflage,depanel_flag}{ where mo_number='" + moNumber + "' and routeendflage <> 'Y' and depanel_flag <> 'Y' }=] ");
// sql.append("\n where ");
// sql.append("\n mo_number = '"+moNumber+"' ");
// sql.append("\n and routeendflage <> 'Y' ");
// sql.append("\n and depanel_flag <> 'Y' ");
- sql.append("\n group by ");
- sql.append("\n display_group, ");
- sql.append("\n mo_number ");
- sql.append("\n ) m on a.group_next = m.display_group ");
- sql.append("\n and a.mo_number = m.mo_number ");
- sql.append("\n left join ( select group_name, mo_number, sum( repair ) error_itemcount from [={"+trackTable+" t2}{group_name,mo_number,repair,error_flag}{where mo_number = '" + moNumber + "' and error_flag = '1'}=] group by group_name, mo_number ) n on a.group_next = n.group_name ");
- sql.append("\n and a.mo_number = n.mo_number ");
- sql.append("\n ) c union all ");
- sql.append("\n select ");
- sql.append("\n 0 route_code, ");
- sql.append("\n '娴佺▼缁撴潫' group_next, ");
- sql.append("\n 150 step_sequence, ");
- sql.append("\n 5 state_flag, ");
- sql.append("\n '" + moNumber + "' mo_number, ");
- sql.append("\n ifnull( ( select sum( wait_scrap_qty ) from [={"+trackTable+" t3}{wait_scrap_qty,mo_number}{ where mo_number = '" + moNumber + "' }=] as tt ), 0 ) wait_scrap_qty_all, ");
- sql.append("\n 0 pass_itemcount, ");
- sql.append("\n 0 second_passcount, ");
- sql.append("\n ifnull( ( select sum( item_count ) from [={"+trackTable+" t4}{item_count,mo_number,routeendflage}{where mo_number = '" + moNumber + "' and routeendflage = 'Y'}=] as ccc ), 0 ) use_itemcount, ");
- sql.append("\n 0 error_itemcount ");
- sql.append("\n from ");
- sql.append("\n dual ");
- sql.append("\n ) a ");
+ sql.append("\n group by ");
+ sql.append("\n display_group, ");
+ sql.append("\n mo_number ");
+ sql.append("\n ) m on a.group_next = m.display_group ");
+ sql.append("\n and a.mo_number = m.mo_number ");
+ sql.append("\n left join ( select group_name, mo_number, sum( repair ) error_itemcount from [={" + trackTable + " t2}{group_name,mo_number,repair,error_flag}{where mo_number = '" + moNumber + "' and error_flag = '1'}=] group by group_name, mo_number ) n on a.group_next = n.group_name ");
+ sql.append("\n and a.mo_number = n.mo_number ");
+ sql.append("\n ) c union all ");
+ sql.append("\n select ");
+ sql.append("\n 0 route_code, ");
+ sql.append("\n '娴佺▼缁撴潫' group_next, ");
+ sql.append("\n 150 step_sequence, ");
+ sql.append("\n 5 state_flag, ");
+ sql.append("\n '" + moNumber + "' mo_number, ");
+ sql.append("\n ifnull( ( select sum( wait_scrap_qty ) from [={" + trackTable + " t3}{wait_scrap_qty,mo_number}{ where mo_number = '" + moNumber + "' }=] as tt ), 0 ) wait_scrap_qty_all, ");
+ sql.append("\n 0 pass_itemcount, ");
+ sql.append("\n 0 second_passcount, ");
+ sql.append("\n ifnull( ( select sum( item_count ) from [={" + trackTable + " t4}{item_count,mo_number,routeendflage}{where mo_number = '" + moNumber + "' and routeendflage = 'Y'}=] as ccc ), 0 ) use_itemcount, ");
+ sql.append("\n 0 error_itemcount ");
+ sql.append("\n from ");
+ sql.append("\n dual ");
+ sql.append("\n ) a ");
- if (!StringUtils.isEmpty(process)) {
- String[] processArray = process.split(",");
- sql.append("\n where ").append(BaseUtil.buildQuestionMarkFilter("group_next", processArray, true));
- }
- sql.append("\n ORDER BY ");
- sql.append("\n state_flag ASC, ");
- sql.append("\n step_sequence ASC ");
+ if (!StringUtils.isEmpty(process)) {
+ String[] processArray = process.split(",");
+ sql.append("\n where ").append(BaseUtil.buildQuestionMarkFilter("group_next", processArray, true));
+ }
+ sql.append("\n ORDER BY ");
+ sql.append("\n state_flag ASC, ");
+ sql.append("\n step_sequence ASC ");
- String parseSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString(), 2);
+ String parseSql = QuerySqlParseUtil.parseSplitTableSql(getBaseDao(), sql.toString(), 2);
- return getBaseDao().listTable(parseSql, new Object[]{});
- }
+ return getBaseDao().listTable(parseSql, new Object[]{});
+ }
- private boolean appendLikeFilter(String value, String field, StringBuilder sqlFilter, List<Object> filterParams, boolean isFilter) {
- return appendLikeFilter(value, field, true, sqlFilter, filterParams, isFilter);
- }
+ private boolean appendLikeFilter(String value, String field, StringBuilder sqlFilter, List<Object> filterParams, boolean isFilter) {
+ return appendLikeFilter(value, field, true, sqlFilter, filterParams, isFilter);
+ }
- private boolean appendLikeFilter(String value, String field, boolean like, StringBuilder sqlFilter, List<Object> filterParams, boolean isFilter) {
- if (!StringUtils.isEmpty(value)) {
- if (isFilter) {
- sqlFilter.append(" AND \n");
- }
- sqlFilter.append("\n ( ").append(field);
- if (like) {
- sqlFilter.append(" like concat(?,'%') ");
- } else {
- sqlFilter.append("= ?");
- }
- sqlFilter.append(" ) ");
- filterParams.add(value);
- isFilter = true;
- }
- return isFilter;
- }
+ private boolean appendLikeFilter(String value, String field, boolean like, StringBuilder sqlFilter, List<Object> filterParams, boolean isFilter) {
+ if (!StringUtils.isEmpty(value)) {
+ if (isFilter) {
+ sqlFilter.append(" AND \n");
+ }
+ sqlFilter.append("\n ( ").append(field);
+ if (like) {
+ sqlFilter.append(" like concat(?,'%') ");
+ } else {
+ sqlFilter.append("= ?");
+ }
+ sqlFilter.append(" ) ");
+ filterParams.add(value);
+ isFilter = true;
+ }
+ return isFilter;
+ }
}
--
Gitblit v1.9.2