package com.web.spcBasic.service.internal; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.text.DecimalFormat; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.commons.lang3.StringUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.domain.Page; import org.springframework.data.domain.PageRequest; import org.springframework.data.jpa.domain.Specifications; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Propagation; import org.springframework.transaction.annotation.Transactional; import com.app.base.data.ApiResponseResult; import com.app.base.data.DataGrid; import com.app.query.dao.JdbcDao; import com.utils.BaseSql; import com.utils.UserUtil; import com.web.spcBasic.service.ChartSpcService; import oracle.jdbc.OracleTypes; @Service(value = "ChartSpcImpl") @Transactional(propagation = Propagation.REQUIRED) public class ChartSpcImpl extends BaseSql implements ChartSpcService { protected Logger logger = LoggerFactory.getLogger(this.getClass()); // @Autowired // private JdbcDao jdbcDao; @Autowired private JdbcTemplate jdbcTemplate; @Override public ApiResponseResult getProductList(String keyword,PageRequest pageReques) throws java.lang.Exception { // TODO Auto-generated method stub if("null".equals(keyword) || keyword == null){ keyword = ""; } /*String sql = "select b.Board_Item from (select a.*,rownum rn from (select mes.Board_Item from MES_BOARD_BASE_INFO mes where mes.board_item like '%%')a where rownum<"+pageReques.getPageSize()*(pageReques.getPageNumber()+1)+")b where b.rn>="+((pageReques.getPageNumber())*pageReques.getPageSize()+1); String count_sql = "select count(*) from MES_BOARD_BASE_INFO mes where mes.board_item like '%"+keyword+"%' "; Map param = new HashMap(); List> lmap_list = createSQLQueryMap(sql,param); List> lmap_count = createSQLQueryMap(count_sql,param); DataGrid dataGrid = DataGrid.create(lmap_list, lmap_count.size(), pageReques.getPageNumber() + 1, pageReques.getPageSize()); return ApiResponseResult.success().data(dataGrid);*/ String sql = "select distinct mes.prod_no,mes.prod_name from MES_SPC_CONTPARA mes where (mes.prod_no like '%"+keyword+"%') or (mes.prod_name like '%"+keyword+"%')"; Map param = new HashMap(); List> lmap_list = createSQLQueryMap(sql,param); return ApiResponseResult.success().data(lmap_list); } @Override public ApiResponseResult getProcessList(String productCode, String keyword) throws Exception { // TODO Auto-generated method stub String sql = "select distinct mes.proc_no,mes.proc_name from MES_SPC_CONTPARA mes where mes.prod_no='"+productCode+"' and concat(mes.proc_no,mes.proc_name) like '%"+keyword+"%'"; Map param = new HashMap(); List> lmap_list = createSQLQueryMap(sql,param); return ApiResponseResult.success().data(lmap_list); } @Override public ApiResponseResult getItemnoList(String productCode, String keyword) throws Exception { String sql = "select distinct b.ITEM_NO,b.ITEM_NAME from MES_SPC_CONTPARA a left join MES_SPC_FORMULA b on a.id=b.mid where b.item_no is not null and PROD_NO = '" + productCode + "'"; Map param = new HashMap<>(); List> lmap_list = createSQLQueryMap(sql, param); return ApiResponseResult.success().data(lmap_list); } @Override public ApiResponseResult getItemList(String productCode, String porcessCode, String keyword) throws Exception { // TODO Auto-generated method stub String sql = "select distinct mes.id,mes.check_item from MES_SPC_CONTPARA mes where mes.prod_no='"+productCode+"' and mes.proc_no='"+porcessCode+"' and mes.check_item like '%"+keyword+"%'"; Map param = new HashMap(); List> lmap_list = createSQLQueryMap(sql,param); return ApiResponseResult.success().data(lmap_list); } private String subString(String str,int len){ if(StringUtils.isEmpty(str)){ return str; } return str.length()>len?str.substring(0, len):str; } /*private ArrayList getChild(String pcode){ List lfp = featureProductDao.findByBsIsDelAndBsPrCode(BooleanStateEnum.FALSE.intValue(), pcode); ArrayList ml = new ArrayList(); for(FeatureProduct f:lfp){ HashMap map = new HashMap(); map.put("value", f.getFeature().getId()); map.put("label", f.getFeature().getBsName()); map.put("label_en", f.getFeature().getBsNameEn()); ml.add(map); } return ml; }*/ @Override public ApiResponseResult getFirstInOneChart(String suppCode, String prCode, String feId, String fdate, String tdate) throws Exception { // TODO Auto-generated method stub return null; /*if (StringUtils.isEmpty(prCode)) { return ApiResponseResult.failure("产品编号为必填项"); } List> lm = chartSpcDao.findBySuppByCode(prCode); ArrayList child = this.getChild(prCode); List> lp = new ArrayList>(); ArrayList ml = new ArrayList(); for(int i=0;i map = new HashMap(); String name = lm.get(i).get("name").toString(); String code = lm.get(i).get("code").toString(); map.put("title", name); map.put("sample", this.subString(name, 9)); map.put("code", code); map.put("child", child); ml.add(map); //if(prCode.equals(code)){ //} } Map m = new HashMap(); m.put("Menu", ml); String msg = ""; if(child.size() >0 ){ feId = child.get(0).get("value").toString(); } prCode= "1020200104"; suppCode="PS-0038"; feId = "5003"; fdate = "2020-05-21"; tdate = "2020-06-29"; ApiResponseResult api = this.getInOnePrc(suppCode, prCode, feId, fdate, tdate); if(!api.isResult()){ msg = api.getMsg(); } m.put("List", api.getData()); return ApiResponseResult.success(msg).data(m);*/ } @Override public ApiResponseResult getInOneChart(String suppCode, String prCode, String feId, String fdate, String tdate) throws Exception { // TODO Auto-generated method stub //return this.getInOnePrc(suppCode, prCode, feId, fdate, tdate); String ins = "suppCode, prCode, feId, fdate, tdate"; String[] in_params = ins.split(","); String[] in_values = new String[5]; in_values[0] = suppCode; in_values[1] = prCode; in_values[2] = feId; in_values[3] = fdate; in_values[4] = tdate; return getInOnePrc(0, in_params, in_values); } @Override public ApiResponseResult getInOneChartAndItemnos(String suppCode, String prCode, String item_nos, String feId, String fdate, String tdate) throws Exception { String ins = "prCode, suppCode,item_nos, feId, fdate, tdate"; String[] in_params = ins.split(","); String[] in_values = new String[6]; in_values[1] = suppCode; in_values[0] = prCode; in_values[3] = feId; in_values[2] = item_nos; in_values[4] = fdate; in_values[5] = tdate; return getInOnePrc(1, in_params, in_values); } private ApiResponseResult getInOnePrc(int i_in, String[] in_params, String[] in_values) { Connection conn = null; CallableStatement callStmt = null; ResultSet rs = null; List> lo = new ArrayList<>(); try { conn = this.jdbcTemplate.getDataSource().getConnection(); callStmt = conn.prepareCall("{call Prc_Capa_SixPack(" + getQuestion(i_in) + "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}"); callStmt.setString(1, (new StringBuilder(String.valueOf(Math.random() * 100.0D))).toString()); for (int i = 0; i < in_params.length; i++) callStmt.setString(i + 2, in_values[i]); callStmt.registerOutParameter(7 + i_in, 2); callStmt.registerOutParameter(8 + i_in, 12); callStmt.registerOutParameter(9 + i_in, -10); callStmt.registerOutParameter(10 + i_in, -10); callStmt.registerOutParameter(11 + i_in, -10); callStmt.registerOutParameter(12 + i_in, -10); callStmt.registerOutParameter(13 + i_in, -10); callStmt.registerOutParameter(14 + i_in, -10); callStmt.registerOutParameter(15 + i_in, -10); callStmt.registerOutParameter(16 + i_in, -10); callStmt.execute(); String flag = callStmt.getString(7 + i_in); String msg = callStmt.getString(8 + i_in); if (flag.equals("1")) { String featureName = ""; return ApiResponseResult.failure(msg); } String spc = ""; String spcDemo = "", spcDemo_en = ""; List> list16 = fitMap((ResultSet)callStmt.getObject(16 + i_in)); if (list16.size() == 0) spc = "未设定判异准则"; Map spcXar = new HashMap<>(); Map spcRs = new HashMap<>(); for (Map map16 : list16) { String xbar = (map16.get("BS_RESULT_XBAR") == null) ? "" : map16.get("BS_RESULT_XBAR").toString().trim(); if (!StringUtils.isEmpty(xbar)) { String[] str16 = xbar.split(","); String s = (map16.get("BS_CODE") == null) ? "" : map16.get("BS_CODE").toString().trim(); byte b; int j; String[] arrayOfString1; for (j = (arrayOfString1 = str16).length, b = 0; b < j; ) { String s16 = arrayOfString1[b]; if (spcXar.containsKey(s16)) { spcXar.put(s16, String.valueOf(spcXar.get(s16)) + "," + s); } else { spcXar.put(s16, s); } b++; } } String rs16 = (map16.get("BS_RESULT_RS") == null) ? "" : map16.get("BS_RESULT_RS").toString().trim(); if (!StringUtils.isEmpty(rs16)) { String[] str16 = rs16.split(","); String s = (map16.get("BS_CODE") == null) ? "" : map16.get("BS_CODE").toString().trim(); byte b; int j; String[] arrayOfString1; for (j = (arrayOfString1 = str16).length, b = 0; b < j; ) { String s16 = arrayOfString1[b]; if (spcRs.containsKey(s16)) { spcRs.put(s16, String.valueOf(spcXar.get(s16)) + "," + s); } else { spcRs.put(s16, s); } b++; } } if (!StringUtils.isEmpty(rs16) || !StringUtils.isEmpty(xbar)) { String s = (map16.get("BS_CODE") == null) ? "" : map16.get("BS_CODE").toString().trim(); String ss = (map16.get("BS_NAME") == null) ? "" : map16.get("BS_NAME").toString().trim(); String ss_en = (map16.get("BS_NAME_EN") == null) ? "" : map16.get("BS_NAME_EN").toString().trim(); String k = (map16.get("BS_VALUE") == null) ? "" : map16.get("BS_VALUE").toString().trim(); ss = ss.replace("K", k); spcDemo = String.valueOf(spcDemo) + s + ":" + ss + ";"; spcDemo_en = String.valueOf(spcDemo_en) + s + ":" + ss_en + ";"; } } List> list = fitMap((ResultSet)callStmt.getObject(10 + i_in)); String[] xAxis = new String[list.size()]; String[] series1 = new String[list.size()]; String[][] series11 = new String[list.size()][3]; String ucl1 = new String(); String xl1 = new String(); String lcl1 = new String(); String[] series2 = new String[list.size()]; String[][] series21 = new String[list.size()][3]; String ucl2 = new String(); String xl2 = new String(); String lcl2 = new String(); float max = 0.0F; float min = 0.0F; float max1 = 0.0F; float min1 = 0.0F; List xarList = new ArrayList(); List rsList = new ArrayList(); List> listXar = new ArrayList<>(); List> listRs = new ArrayList<>(); Map m_temp = new HashMap<>(); m_temp.put("value", Integer.valueOf(0)); m_temp.put("color", "red"); listXar.add(m_temp); listRs.add(m_temp); if (list.size() > 0) { max = Float.parseFloat(((Map)list.get(0)).get("SPOT_1").toString()); min = Float.parseFloat(((Map)list.get(0)).get("SPOT_1").toString()); max1 = Float.parseFloat(((Map)list.get(0)).get("SPOT_2").toString()); min1 = Float.parseFloat(((Map)list.get(0)).get("SPOT_2").toString()); for (int j = 0; j < list.size(); j++) { Map map1 = list.get(j); xAxis[j] = (map1.get("FGROUP") != null) ? map1.get("FGROUP").toString() : ""; series1[j] = (map1.get("SPOT_1") != null) ? map1.get("SPOT_1").toString() : ""; series2[j] = (map1.get("SPOT_2") != null) ? map1.get("SPOT_2").toString() : ""; series11[j][0] = (map1.get("FGROUP") != null) ? map1.get("FGROUP").toString() : ""; series11[j][1] = (map1.get("SPOT_1") != null) ? map1.get("SPOT_1").toString() : ""; series11[j][2] = "-"; series21[j][0] = (map1.get("FGROUP") != null) ? map1.get("FGROUP").toString() : ""; series21[j][1] = getZero((map1.get("SPOT_2") != null) ? map1.get("SPOT_2").toString() : ""); series21[j][2] = "-"; if (!StringUtils.isEmpty(series11[j][1])) { float temp = Float.parseFloat(series11[j][1]); if (temp > max) max = temp; if (temp < min) min = temp; } if (!StringUtils.isEmpty(series21[j][1])) { float temp = Float.parseFloat(series21[j][1]); if (temp > max1) max1 = temp; if (temp < min1) min1 = temp; } if (spcXar.containsKey((new StringBuilder(String.valueOf(j + 1))).toString())) { xarList.add((map1.get("FGROUP") != null) ? map1.get("FGROUP").toString() : ""); Map m = new HashMap<>(); m.put("value", (map1.get("FGROUP") != null) ? map1.get("FGROUP").toString() : ""); m.put("color", "red"); listXar.add(m); series11[j][2] = ((String)spcXar.get((new StringBuilder(String.valueOf(j + 1))).toString())).toString(); } if (spcRs.containsKey((new StringBuilder(String.valueOf(j + 1))).toString())) { rsList.add((map1.get("FGROUP") != null) ? map1.get("FGROUP").toString() : ""); Map m = new HashMap<>(); m.put("value", (map1.get("FGROUP") != null) ? map1.get("FGROUP").toString() : ""); m.put("color", "red"); listRs.add(m); series21[j][2] = ((String)spcRs.get((new StringBuilder(String.valueOf(j + 1))).toString())).toString(); } } Map map0 = list.get(0); ucl1 = getZero((map0.get("UCL_1") == null) ? "" : map0.get("UCL_1").toString()); xl1 = getZero((map0.get("CL_1") == null) ? "" : map0.get("CL_1").toString()); lcl1 = getZero((map0.get("LCL_1") == null) ? "" : map0.get("LCL_1").toString()); ucl2 = getZero((map0.get("UCL_2") == null) ? "" : map0.get("UCL_2").toString()); xl2 = getZero((map0.get("CL_2") == null) ? "" : map0.get("CL_2").toString()); lcl2 = getZero((map0.get("LCL_2") == null) ? "" : map0.get("LCL_2").toString()); } HashMap map = new HashMap<>(); map.put("XAxis", xAxis); map.put("Series1", series11); map.put("Series2", series21); map.put("UCL_1", ucl1); map.put("CL_1", xl1); map.put("LCL_1", lcl1); map.put("UCL_2", ucl2); map.put("CL_2", xl2); map.put("LCL_2", lcl2); map.put("Xbar_Red", listXar); map.put("Rs_Red", listRs); DecimalFormat decimalFormat = new DecimalFormat(".00"); if (!StringUtils.isEmpty(ucl1) && max < Float.parseFloat(ucl1)) map.put("Max", decimalFormat.format((max - min) * 0.1D + Float.parseFloat(ucl1))); if (!StringUtils.isEmpty(lcl1) && min > Float.parseFloat(lcl1)) map.put("Min", decimalFormat.format(Float.parseFloat(lcl1) - (max - min) * 0.1D)); if (!StringUtils.isEmpty(ucl2) && max1 < Float.parseFloat(ucl2)) map.put("Max2", decimalFormat.format((max1 - min1) * 0.1D + Float.parseFloat(ucl2))); if (!StringUtils.isEmpty(lcl2) && min1 < Float.parseFloat(lcl2)) map.put("Min2", decimalFormat.format(Float.parseFloat(lcl2) - (max1 - min1) * 0.1D)); List> list2 = fitMap((ResultSet)callStmt.getObject(12 + i_in)); HashMap map2 = new HashMap<>(); if (list2.size() > 0) { List> list2_1 = list2; if (list2_1.size() > 0) { String[][] series22 = new String[list2_1.size()][2]; String[][] line2 = new String[list2_1.size()][2]; String[][] line_xu = new String[list2_1.size()][2]; for (int j = 0; j < list2_1.size(); j++) { Map map1 = list2_1.get(j); String x = map1.get("FVAL").toString().trim(); series22[j][0] = x; series22[j][1] = map1.get("FPS").toString(); line2[j][0] = x; line2[j][1] = (new StringBuilder(String.valueOf(Double.parseDouble(map1.get("FZT").toString()) * 1.0D))).toString(); line_xu[j][0] = x; line_xu[j][1] = (new StringBuilder(String.valueOf(Double.parseDouble(map1.get("FZT_ZN").toString()) * 1.0D))).toString(); } map2.put("Series", series22); map2.put("Line", line2); map2.put("Line_xu", line_xu); map2.put("LSL", ((Map)list2_1.get(0)).get("LSL")); map2.put("USL", ((Map)list2_1.get(0)).get("USL")); map2.put("Target", ((Map)list2_1.get(0)).get("TARGET")); if (((Map)list2_1.get(0)).get("LSL") == null) { map2.put("Min", series22[0][0]); } else { map2.put("Min", (Double.parseDouble(((Map)list2_1.get(0)).get("LSL").toString()) > Double.parseDouble(series22[0][0])) ? series22[0][0] : Double.valueOf(Double.parseDouble(((Map)list2_1.get(0)).get("LSL").toString()) - 0.01D)); } if (((Map)list2_1.get(0)).get("USL") == null) { map2.put("Max", series22[list2_1.size() - 1][0]); } else { map2.put("Max", (Double.parseDouble(((Map)list2_1.get(0)).get("USL").toString()) < Double.parseDouble(series22[0][0])) ? series22[0][0] : Double.valueOf(Double.parseDouble(((Map)list2_1.get(0)).get("USL").toString()))); } } } List> list4 = fitMap((ResultSet)callStmt.getObject(13 + i_in)); List> list4_1 = fitMap((ResultSet)callStmt.getObject(14 + i_in)); HashMap map4 = new HashMap<>(); if (list4.size() > 0) { String[][] series = new String[list4.size()][2]; for (int j = 0; j < list4.size(); j++) { Map map1 = list4.get(j); series[j][0] = map1.get("SPOT_X").toString(); series[j][1] = map1.get("FZ").toString(); } String p = getZero(((Map)list4.get(0)).get("P").toString()); if (p.equals("0.005")) { map4.put("Ad", "AD:" + getZero(((Map)list4.get(0)).get("AD").toString()).trim() + ",p:<0.005"); } else { map4.put("Ad", "AD:" + getZero(((Map)list4.get(0)).get("AD").toString()).trim() + ",p:=" + p); } map4.put("Series", series); } if (list4_1.size() > 0) { String[][] line1 = new String[list4_1.size()][2]; String[][] line2 = new String[list4_1.size()][2]; String[][] line3 = new String[list4_1.size()][2]; for (int j = 0; j < list4_1.size(); j++) { Map map1 = list4_1.get(j); String y = map1.get("FZ").toString(); line1[j][0] = map1.get("LINE1").toString(); line1[j][1] = y; line2[j][0] = map1.get("LINE2").toString(); line2[j][1] = y; line3[j][0] = map1.get("LINE3").toString(); line3[j][1] = y; } map4.put("Line1", line1); map4.put("Line2", line2); map4.put("Line3", line3); map4.put("Min", ((Map)list4_1.get(0)).get("FZ")); map4.put("Max", ((Map)list4_1.get(list4_1.size() - 1)).get("FZ")); } List> list5 = fitMap((ResultSet)callStmt.getObject(11 + i_in)); HashMap map5 = new HashMap<>(); if (list5.size() > 0) { String[][] series = new String[list5.size()][2]; for (int j = 0; j < list5.size(); j++) { Map map1 = list5.get(j); series[j][0] = map1.get("FGROUP").toString(); series[j][1] = map1.get("FSPOT").toString(); } map5.put("Series", series); map5.put("Line5", ((Map)list5.get(0)).get("FCL").toString()); map5.put("Start", ((Map)list5.get(0)).get("FGROUP")); map5.put("End", ((Map)list5.get(list5.size() - 1)).get("FGROUP")); } List> list6 = fitMap((ResultSet)callStmt.getObject(15 + i_in)); HashMap map6 = new HashMap<>(); if (list6.size() > 0) { String[][] zn = new String[3][2]; String[][] zt = new String[3][2]; String[][] gg = new String[2][2]; Map map1 = list6.get(0); zn[0][0] = map1.get("ZN_L").toString(); zn[0][1] = "30"; zn[1][0] = map1.get("ZN_M").toString(); zn[1][1] = "30"; zn[2][0] = map1.get("ZN_R").toString(); zn[2][1] = "30"; zt[0][0] = map1.get("ZT_L").toString(); zt[0][1] = "20"; zt[1][0] = map1.get("ZT_M").toString(); zt[1][1] = "20"; zt[2][0] = map1.get("ZT_R").toString(); zt[2][1] = "20"; gg[0][0] = (map1.get("LSL") == null) ? "0" : map1.get("LSL").toString(); gg[0][1] = "10"; gg[1][0] = (map1.get("USL") == null) ? "0" : map1.get("USL").toString(); gg[1][1] = "10"; map6.put("ZN", zn); map6.put("ZT", zt); map6.put("GG", gg); map6.put("Min", Double.valueOf(Double.valueOf(gg[0][0]).doubleValue() * 0.9D)); map6.put("Max", Double.valueOf(Double.valueOf(gg[1][0]).doubleValue() * 1.1D)); } List> list7 = fitMap((ResultSet)callStmt.getObject(9 + i_in)); HashMap map7 = new HashMap<>(); String bzc_zn = ""; String bxz_zt = ""; String cp = ""; String cpk = ""; String pp = ""; String ppk = ""; String cpm = ""; String ppm_zn = ""; String ppm_zt = ""; if (list7.size() > 0) { Map map1 = list7.get(0); bzc_zn = map1.get("ZN_BJC").toString(); bxz_zt = map1.get("ZT_BJC").toString(); cp = map1.get("CP").toString(); cpk = map1.get("CPK").toString(); pp = map1.get("PP").toString(); ppk = map1.get("PPK").toString(); cpm = map1.get("CPM").toString(); ppm_zn = map1.get("ZN_PPM").toString(); ppm_zt = map1.get("ZT_PPM").toString(); } map7.put("Bzc_zn", bzc_zn); map7.put("Bxz_zt", bxz_zt); map7.put("Cp", cp); map7.put("Cpk", cpk); map7.put("Pp", pp); map7.put("Ppk", ppk); map7.put("Cpm", cpm); map7.put("Ppm_zn", ppm_zn); map7.put("Ppm_zt", ppm_zt); HashMap mapAll = new HashMap<>(); mapAll.put("Xbar", map); mapAll.put("LnGroup", map5); mapAll.put("ZFT", map2); mapAll.put("ZTFB", map4); mapAll.put("NL", map6); mapAll.put("Cpk", map7); mapAll.put("Spc", spcDemo); mapAll.put("Spc_en", spcDemo_en); return ApiResponseResult.success().data(mapAll); } catch (Exception e) { this.logger.info("getitemandvendor", e); return ApiResponseResult.failure(e.toString()); } finally { if (rs != null) try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } if (callStmt != null) try { callStmt.close(); } catch (SQLException sQLException) {} if (conn != null) try { conn.close(); } catch (SQLException sQLException) {} } } private String getQuestion(int num) { String ques = ""; for (int i = 0; i < num; i++) ques = String.valueOf(ques) + "?,"; return ques; } private ApiResponseResult getInOnePrc_bak(String suppCode,String prCode,String feId,String fdate,String tdate){ Connection conn = null; CallableStatement callStmt = null; ResultSet rs = null; List> lo = new ArrayList>(); try { conn = jdbcTemplate.getDataSource().getConnection(); callStmt = conn.prepareCall("{call Prc_Capa_SixPack(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}"); //登录账号id callStmt.setString(1, (Math.random()*100)+""); //工序编码 callStmt.setString(2, suppCode); //产品编号 callStmt.setString(3, prCode); //特性项目id callStmt.setString(4, feId); //开始时间 callStmt.setString(5, fdate); //结束时间 callStmt.setString(6, tdate); callStmt.registerOutParameter(7, OracleTypes.NUMBER);//返回标志 callStmt.registerOutParameter(8, OracleTypes.VARCHAR);//返回信息 callStmt.registerOutParameter(9, OracleTypes.CURSOR);//正态分析数据 callStmt.registerOutParameter(10, OracleTypes.CURSOR);//Xbar控制图数据集 callStmt.registerOutParameter(11, OracleTypes.CURSOR);//最后N个子组数据集 callStmt.registerOutParameter(12, OracleTypes.CURSOR);//能力直方图数据集 callStmt.registerOutParameter(13, OracleTypes.CURSOR);//正态概率图数据集(AD/P/Y轴点位) callStmt.registerOutParameter(14, OracleTypes.CURSOR);//正态概率图数据集(三条曲线/Y轴) callStmt.registerOutParameter(15, OracleTypes.CURSOR);//能力图数据集 callStmt.registerOutParameter(16, OracleTypes.CURSOR);//SPC判异准则数据集 callStmt.execute(); //返回的标志 String flag = callStmt.getString(7); String msg = callStmt.getString(8); if(flag.equals("1")){ //添加提示信息 //获取特性项目名称 String featureName = ""; try{ //Feature feature = featureInfoDao.findOne(Long.parseLong(feId)); //featureName = feature.getBsName(); }catch (Exception e){ } //封装提示信息 msg += ",产品编号为"+prCode+",供应商编号为"+suppCode+",特性项目名称为"+featureName+",起止日期为"+fdate+"至"+tdate; return ApiResponseResult.success(msg).data(""); } String spc = "";//SPC判异准则数据集 String spcDemo = "";String spcDemo_en = ""; List> list16 = this.fitMap((ResultSet) callStmt.getObject(16)); if(list16.size() == 0){ spc = "未设定判异准则"; } //BS_RESULT_XBAR 标红的序号Xbar图;BS_RESULT_RS -S图 //转换成List> 其中，map的key值为标红序号，value值是判异的序号 Map spcXar = new HashMap(); Map spcRs = new HashMap(); for(Map map16:list16){ String xbar = map16.get("BS_RESULT_XBAR")==null?"":map16.get("BS_RESULT_XBAR").toString().trim(); if(!StringUtils.isEmpty(xbar)){ String[] str16 = xbar.split(","); String s = map16.get("BS_CODE")==null?"":map16.get("BS_CODE").toString().trim(); for(String s16:str16){ if(spcXar.containsKey(s16)){ spcXar.put(s16, spcXar.get(s16)+","+s); }else{ spcXar.put(s16, s); } } } String rs16 = map16.get("BS_RESULT_RS")==null?"":map16.get("BS_RESULT_RS").toString().trim(); if(!StringUtils.isEmpty(rs16)){ String[] str16 = rs16.split(","); String s = map16.get("BS_CODE")==null?"":map16.get("BS_CODE").toString().trim(); for(String s16:str16){ if(spcRs.containsKey(s16)){ spcRs.put(s16, spcXar.get(s16)+","+s); }else{ spcRs.put(s16, s); } } } if(!StringUtils.isEmpty(rs16) || !StringUtils.isEmpty(xbar)){ String s = map16.get("BS_CODE")==null?"":map16.get("BS_CODE").toString().trim(); String ss = map16.get("BS_NAME")==null?"":map16.get("BS_NAME").toString().trim(); String ss_en = map16.get("BS_NAME_EN")==null?"":map16.get("BS_NAME_EN").toString().trim(); //20210130-fyx-K值 String k = map16.get("BS_VALUE")==null?"":map16.get("BS_VALUE").toString().trim(); ss = ss.replace("K", k); //--end spcDemo += s+":"+ss+";"; spcDemo_en +=s+":"+ss_en+";"; } } //Xbar图 List> list = this.fitMap((ResultSet) callStmt.getObject(10)); String[] xAxis = new String[list.size()];//X轴,子组序号 String[] series1 = new String[list.size()];//点数据 String[][] series11 = new String[list.size()][3];//点数据 String ucl1 = new String();//上线 String xl1 = new String();//中线 String lcl1 = new String();//下线 String[] series2 = new String[list.size()];//点数据 String[][] series21 = new String[list.size()][3];//点数据 String ucl2 = new String();//上线 String xl2= new String();//中线 String lcl2 = new String();//下线 float max = 0; float min = 0; float max1 = 0; float min1 = 0; List xarList = new ArrayList(); List rsList = new ArrayList(); List> listXar = new ArrayList>(); List> listRs = new ArrayList>(); Map m_temp = new HashMap(); m_temp.put("value", 0); m_temp.put("color", "red"); listXar.add(m_temp); listRs.add(m_temp); if(list.size()>0){ max = Float.parseFloat(list.get(0).get("SPOT_1").toString()); min = Float.parseFloat(list.get(0).get("SPOT_1").toString()); max1 = Float.parseFloat(list.get(0).get("SPOT_2").toString()); min1 = Float.parseFloat(list.get(0).get("SPOT_2").toString()); //加工成图表数据 for(int i=0;i map = list.get(i); xAxis[i] = map.get("FGROUP")!=null ? map.get("FGROUP").toString() : ""; series1[i] = map.get("SPOT_1")!=null ? map.get("SPOT_1").toString() : ""; series2[i] = map.get("SPOT_2")!=null ? map.get("SPOT_2").toString() : ""; series11[i][0] = map.get("FGROUP")!=null ? map.get("FGROUP").toString() : ""; series11[i][1] = map.get("SPOT_1")!=null ? map.get("SPOT_1").toString() : ""; series11[i][2] = "-"; series21[i][0] = map.get("FGROUP")!=null ? map.get("FGROUP").toString() : ""; series21[i][1] = getZero(map.get("SPOT_2")!=null ? map.get("SPOT_2").toString() : ""); series21[i][2] = "-"; if(!StringUtils.isEmpty(series11[i][1])){ float temp = Float.parseFloat(series11[i][1]); if(temp>max){ max = temp; } if(tempmax1){ max1 = temp; } if(temp m = new HashMap(); m.put("value", map.get("FGROUP")!=null ? map.get("FGROUP").toString() : ""); m.put("color", "red"); listXar.add(m); series11[i][2] = spcXar.get((i+1)+"").toString(); } if(spcRs.containsKey((i+1)+"")){ rsList.add(map.get("FGROUP")!=null ? map.get("FGROUP").toString() : ""); Map m = new HashMap(); m.put("value", map.get("FGROUP")!=null ? map.get("FGROUP").toString() : ""); m.put("color", "red"); listRs.add(m); series21[i][2] = spcRs.get((i+1)+"").toString(); } } Map map0 = list.get(0); ucl1 = getZero(map0.get("UCL_1")==null?"":map0.get("UCL_1").toString());//上线 xl1 = getZero(map0.get("CL_1")==null?"":map0.get("CL_1").toString());//中线 lcl1 = getZero(map0.get("LCL_1")==null?"":map0.get("LCL_1").toString());//下线 ucl2 = getZero(map0.get("UCL_2")==null?"":map0.get("UCL_2").toString());//上线 xl2 = getZero(map0.get("CL_2")==null?"":map0.get("CL_2").toString());//中线 lcl2 = getZero(map0.get("LCL_2")==null?"":map0.get("LCL_2").toString());//下线 } HashMap map = new HashMap(); map.put("XAxis", xAxis); /*map.put("Series1", series1); map.put("Series2",series2);*/ map.put("Series1", series11); map.put("Series2",series21); map.put("UCL_1", ucl1); map.put("CL_1", xl1); map.put("LCL_1", lcl1); map.put("UCL_2", ucl2); map.put("CL_2", xl2); map.put("LCL_2", lcl2); map.put("Xbar_Red", listXar); map.put("Rs_Red", listRs); DecimalFormat decimalFormat=new DecimalFormat(".00");//构造方法的字符格式这里如果小数不足2位,会以0补足. //String p=decimalFomat.format(price);//format 返回的是字符串 //判断y轴刻度的上下线 if(!StringUtils.isEmpty(ucl1)){ if(maxFloat.parseFloat(lcl1)){ map.put("Min", decimalFormat.format(Float.parseFloat(lcl1)-(max-min)*0.1)); } } if(!StringUtils.isEmpty(ucl2)){ if(max1> list2 = this.fitMap((ResultSet) callStmt.getObject(12)); HashMap map2 = new HashMap(); if(list2.size() > 0){ //FPS>0 //List> list2_1 = list2.stream().filter(s->!s.get("FPS").toString().equals("0")).collect(Collectors.toList()); List> list2_1 = list2; if(list2_1.size()>0){ String[][] series22 = new String[list2_1.size()][2]; String[][] line2 = new String[list2_1.size()][2]; String[][] line_xu = new String[list2_1.size()][2]; for(int i=0;i map1 = list2_1.get(i); String x = map1.get("FVAL").toString().trim(); series22[i][0] = x; series22[i][1] = map1.get("FPS").toString(); line2[i][0] = x; line2[i][1] = Double.parseDouble(map1.get("FZT").toString())*1+""; line_xu[i][0] = x; line_xu[i][1] = Double.parseDouble(map1.get("FZT_ZN").toString())*1+""; } map2.put("Series", series22); map2.put("Line", line2); map2.put("Line_xu", line_xu); map2.put("LSL", list2_1.get(0).get("LSL")); map2.put("USL", list2_1.get(0).get("USL")); map2.put("Target", list2_1.get(0).get("TARGET")); if(list2_1.get(0).get("LSL") == null){ map2.put("Min", series22[0][0]); }else{ map2.put("Min", Double.parseDouble(list2_1.get(0).get("LSL").toString())>Double.parseDouble(series22[0][0])?series22[0][0]:Double.parseDouble(list2_1.get(0).get("LSL").toString())-0.01); } if(list2_1.get(0).get("USL") == null){ map2.put("Max", series22[list2_1.size()-1][0]); }else{ map2.put("Max", Double.parseDouble(list2_1.get(0).get("USL").toString())> list4 = this.fitMap((ResultSet) callStmt.getObject(13)); List> list4_1 = this.fitMap((ResultSet) callStmt.getObject(14)); HashMap map4 = new HashMap(); if(list4.size()>0){ String[][] series = new String[list4.size()][2]; for(int i=0;i map1 = list4.get(i); series[i][0] = map1.get("SPOT_X").toString(); series[i][1] = map1.get("FZ").toString();//map1.get("SPOT_Y").toString(); } //判断P值 String p = getZero(list4.get(0).get("P").toString()); if(p.equals("0.005")){ map4.put("Ad", "AD:"+getZero(list4.get(0).get("AD").toString()).trim()+",p:<0.005"); }else{ map4.put("Ad", "AD:"+getZero(list4.get(0).get("AD").toString()).trim()+",p:="+p); } map4.put("Series", series); } if(list4_1.size()>0){ String[][] line1 = new String[list4_1.size()][2]; String[][] line2 = new String[list4_1.size()][2]; String[][] line3 = new String[list4_1.size()][2]; for(int i=0;i map1 = list4_1.get(i); String y = map1.get("FZ").toString();// map1.get("SPOT_Y").toString(); line1[i][0] = map1.get("LINE1").toString(); line1[i][1] = y; line2[i][0] = map1.get("LINE2").toString(); line2[i][1] = y; line3[i][0] = map1.get("LINE3").toString(); line3[i][1] = y; } map4.put("Line1", line1); map4.put("Line2", line2); map4.put("Line3", line3); map4.put("Min", list4_1.get(0).get("FZ")); map4.put("Max", list4_1.get(list4_1.size()-1).get("FZ")); } //最后N个子组 List> list5 = this.fitMap((ResultSet) callStmt.getObject(11)); HashMap map5 = new HashMap(); if(list5.size()>0){ String[][] series = new String[list5.size()][2]; for(int i=0;i map1 = list5.get(i); series[i][0] = map1.get("FGROUP").toString(); series[i][1] = map1.get("FSPOT").toString(); } map5.put("Series", series); map5.put("Line5", list5.get(0).get("FCL").toString()); map5.put("Start", list5.get(0).get("FGROUP")); map5.put("End", list5.get(list5.size()-1).get("FGROUP")); } //能力图 List> list6 = this.fitMap((ResultSet) callStmt.getObject(15)); HashMap map6 = new HashMap(); if(list6.size() >0){ String[][] zn = new String[3][2]; String[][] zt = new String[3][2]; String[][] gg = new String[2][2]; Map map1 = list6.get(0); //组内的三个点 zn[0][0] = map1.get("ZN_L").toString(); zn[0][1] = "30"; zn[1][0] = map1.get("ZN_M").toString(); zn[1][1] = "30"; zn[2][0] = map1.get("ZN_R").toString(); zn[2][1] = "30"; //整体 zt[0][0] = map1.get("ZT_L").toString(); zt[0][1] = "20"; zt[1][0] = map1.get("ZT_M").toString(); zt[1][1] = "20"; zt[2][0] = map1.get("ZT_R").toString(); zt[2][1] = "20"; //规格 gg[0][0] = map1.get("LSL")==null?"0":map1.get("LSL").toString(); gg[0][1] = "10"; gg[1][0] = map1.get("USL")==null?"0":map1.get("USL").toString(); gg[1][1] = "10"; map6.put("ZN", zn); map6.put("ZT", zt); map6.put("GG", gg); map6.put("Min", Double.valueOf(gg[0][0])*0.9); map6.put("Max", Double.valueOf(gg[1][0])*1.1); } //CPK-能力图的说明 List> list7 = this.fitMap((ResultSet) callStmt.getObject(9)); HashMap map7 = new HashMap(); String bzc_zn = ""; String bxz_zt = ""; String cp = ""; String cpk = ""; String pp = ""; String ppk = ""; String cpm = ""; String ppm_zn = ""; String ppm_zt = ""; if(list7.size() >0){ Map map1 = list7.get(0); bzc_zn = map1.get("ZN_BJC").toString(); bxz_zt = map1.get("ZT_BJC").toString(); cp = map1.get("CP").toString(); cpk = map1.get("CPK").toString(); pp = map1.get("PP").toString(); ppk = map1.get("PPK").toString(); cpm = map1.get("CPM").toString(); ppm_zn = map1.get("ZN_PPM").toString(); ppm_zt = map1.get("ZT_PPM").toString(); } map7.put("Bzc_zn", bzc_zn); map7.put("Bxz_zt", bxz_zt); map7.put("Cp",cp); map7.put("Cpk", cpk); map7.put("Pp", pp); map7.put("Ppk", ppk); map7.put("Cpm", cpm); map7.put("Ppm_zn", ppm_zn); map7.put("Ppm_zt", ppm_zt); HashMap mapAll = new HashMap(); mapAll.put("Xbar", map); mapAll.put("LnGroup", map5); mapAll.put("ZFT", map2); mapAll.put("ZTFB", map4); mapAll.put("NL", map6); mapAll.put("Cpk", map7); mapAll.put("Spc", spcDemo); mapAll.put("Spc_en", spcDemo_en); return ApiResponseResult.success().data(mapAll); } catch (Exception e) { logger.info("getitemandvendor", e); return ApiResponseResult.failure(e.toString()); } finally { if (null!=rs) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (null != callStmt) { try { callStmt.close(); } catch (SQLException e) { } } if (null != conn) { try { conn.close(); } catch (SQLException e) { } } } } private List> fitMap(ResultSet rs) throws Exception{ List> list = new ArrayList<>(); if(null!=rs) { Map map; int colNum = rs.getMetaData().getColumnCount(); List columnNames = new ArrayList(); for (int i = 1; i <= colNum; i++) { columnNames.add(rs.getMetaData().getColumnName(i)); } while(rs.next()) { map = new HashMap(); for (String columnName : columnNames) { map.put(columnName, rs.getString(columnName)); } list.add(map); } } return list; } //如果缺失小数点前面的0则自动补 private String getZero(String str){ String[] strs = str.split("\\."); if(strs.length > 0){ if(strs[0].equals("") || strs[0]==null){ return "0"+str; } } return str; } @Override public ApiResponseResult getSpcData(String keyword, String suppCode, String prCode, String feId, String fdate, String tdate,PageRequest pageRequest) throws Exception { // TODO Auto-generated method stub return null; /*List filters = new ArrayList(); filters.add(new SearchFilter("bsIsDel", SearchFilter.Operator.EQ, BooleanStateEnum.FALSE.intValue())); filters.add(new SearchFilter("bsSupplierCode", SearchFilter.Operator.EQ, suppCode)); filters.add(new SearchFilter("bsPrCode", SearchFilter.Operator.EQ, prCode)); filters.add(new SearchFilter("bsIsValid", SearchFilter.Operator.EQ, 1)); filters.add(new SearchFilter("featureProduct.bsPrCode", SearchFilter.Operator.EQ, prCode)); filters.add(new SearchFilter("featureProduct.pkFeature", SearchFilter.Operator.EQ, feId)); // 查询条件2 List filters1 = new ArrayList(); if(!StringUtils.isEmpty(keyword)){ filters1.add(new SearchFilter("bsValues", SearchFilter.Operator.LIKE, keyword)); filters1.add(new SearchFilter("bsPrincipal", SearchFilter.Operator.LIKE, keyword)); filters1.add(new SearchFilter("bsQc", SearchFilter.Operator.LIKE, keyword)); filters1.add(new SearchFilter("bsBatch", SearchFilter.Operator.LIKE, keyword)); } Specifications spec = Specifications.where(super.and(filters, SampleSupplier.class)); Specifications spec1 = spec.and(super.or(filters1, SampleSupplier.class)); Page page = sampleSupplierDao.findAll(spec1, pageRequest); return ApiResponseResult.success().data(Datagrid.create(page.getContent(), (int) page.getTotalElements(), pageRequest.getPageNumber() + 1, pageRequest.getPageSize()));*/ } }