package QCM.JHY01.JHY0102; import java.math.BigDecimal; import java.sql.ResultSet; import java.sql.SQLException; import java.util.HashMap; import java.util.List; import java.util.TreeSet; import org.apache.commons.lang.StringUtils; import org.apache.ibatis.session.SqlSession; import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import CoreFS.SA01.CoreIComponent; import CoreFS.SA06.CoreReturnObject; import QCM.COMMUNAL.QCM02.SqlJoint; import QCM.COMMUNAL.VO.QcmJudgeOperateLog; import QCM.JHY01.JHY0101.QuerryQltySample; import UIB.COM.TechDcsResult; import UIB.COM.XmlSqlParsersFactory; public class QuerryJhyQtly extends CoreIComponent{ CoreReturnObject cro = new CoreReturnObject(); SqlSession SqlSession = QCM.COMMUNAL.SqlSessionBuilder.openSqlSession(); QCM.COMMUNAL.OrderSqMapper mapper = SqlSession.getMapper(QCM.COMMUNAL.OrderSqMapper.class); public String StrSky(String begintime,String endtime){ String sql = "select (to_date('"+endtime+"','yyyy-MM-dd') - to_date('"+begintime+"','yyyy-MM-dd')) SKY from dual"; HashMap list = mapper.queryOne(sql); Double ble = Double.parseDouble(list.get("SKY").toString()); String tstr="FALSE"; if(ble>=0 && ble <=30 ){ tstr="TRUE"; } return tstr; } /** * 根据条件查询材质检验实绩 * @param gradeName * @param heatNo * @return */ public CoreReturnObject Query(String batchNo,String batchNo2,String design_key,String psc_desc,String startTime,String endTime, String pline_name,String dateTimeName,String materialNo) { if (!SqlJoint.IsNullOrSpace(startTime) && !SqlJoint.IsNullOrSpace(endTime)) { String sky=StrSky(startTime,endTime); if(sky.equals("FALSE")){ SqlSession.close(); cro.setV_errCode(-1); cro.setV_errMsg("查询时间不能大于30天,请核实查询时间条件!"); return cro; } } String sql = "select ord.DESIGN_KEY, " +" ord.SMP_NO, " +" ord.PSC, " +" ord.PSC_DESC, " +" ord.HEAT_NO, " +" ord.BATCH_NO, " +" ord.INSPECTION_LOT, " +" ord.DELIVERY_STATE_CODE, " +" ord.DELIVERY_STATE_DESC, " +" to_char(ord.THICK, 'fm990.099') THICK, " +" ord.WIDTH, " +" ord.LENGTH, " +" ord.PLINE_CODE, " +" ord.PLINE_NAME, " +" ord.MSC_PLINE, " +" ord.CERT_INST_CODE, " +" ord.CERT_INST_NAME, " +" ord.SMP_TYPE_CODE, " +" ord.SMP_TYPE_NAME, " +" ord.PROD_NAME, " +" ord.STEEL_NAME, " +" ord.STD_NAME, " +" ord.SMELTING_CNT, " +" ord.PRODUCT_CNT, " +" ord.ASSAY_NO_CP, " +" ord.ASSAY_NO_RL, " +" ord.CIC_ID, " +" ord.PHY_ID, " +" ord.JUDGE_STATUS, " +" ord.JUDGE_RESULT_DESC, " +" ord.JUDGE_NAME, " +" to_char(ord.JUDGE_TIME, 'yyyy-MM-dd hh24:mi:ss') JUDGE_TIME, " +" ord.JUDGE_MEMO, " +" ord.CREATE_NAME, " +" to_char(ord.CREATE_TIME, 'yyyy-MM-dd hh24:mi:ss') CREATE_TIME, " +" to_char(d.send_time, 'yyyy-MM-dd hh24:mi:ss') 发送时间, " +" (select max(material_no) " +" from qcm_jhy_sample_consign_d a " +" where a.smp_no = ord.smp_no) 取样材料号, " +" (select decode(max(a.Smp_Type_Code), '1', '复样', '0', '初样') " +" from qcm_jhy_sample_consign_d a " +" where a.smp_no = ord.smp_no) 取样方式,d.PROCESS_NO,d.PROCESS_NOS,d.FREQ_NAME,d.SMP_LOCATION " +" FROM QCM_JHY_SAMPLE_R_ORD ord " +" inner join QCM_JHY_SAMPLE_CONSIGN_M m " +" on m.smp_no = ord.smp_no " +" inner join QCM_JHY_SAMPLE_CONSIGN_D d " +" on ord.smp_no = d.smp_no " +" WHERE 1 = 1 and d.STATUS <> 5 and d.STATUS <> 6 and ord.JUDGE_STATUS<>3 "; if (batchNo!=null && !"".equals(batchNo) && batchNo2!=null && !"".equals(batchNo2)) { sql +=" and upper(ord.batch_no) >= upper('"+batchNo+"')"; sql +=" and upper(ord.batch_no) <= upper('"+batchNo2+"')"; }else if(batchNo!=null && !"".equals(batchNo) && (batchNo2==null || "".equals(batchNo2)) ){ sql +=" and ord.batch_no like '"+batchNo+"%' "; } if (pline_name!=null && !"".equals(pline_name) && !pline_name.equals("全部")) { if(pline_name.equals("高线")){ sql +=" and ord.PLINE_NAME in ('高线','二线厂')"; }else{ sql +=" and ord.PLINE_NAME like '"+pline_name+"%'"; } } if(!"".equals(design_key)){ sql +=" and lower(ord.DESIGN_KEY) like lower('"+design_key+"%')"; } if(!"".equals(psc_desc)){ sql +=" and lower(ord.PSC_DESC) like lower('"+psc_desc+"%')"; } if(!"".equals(materialNo)){ sql +=" and lower(d.material_no) like lower('"+materialNo+"%')"; } if (startTime!=null && !"".equals(startTime) && endTime !=null && !"".equals(endTime)) { if(dateTimeName.equals("判定时间")){ sql +=" and ord.JUDGE_TIME>=to_date(concat('"+startTime+"',' 00:00:00'),'yyyy-MM-dd HH24:mi:ss')"; sql +=" and ord.JUDGE_TIME<=to_date(concat('"+endTime+"',' 23:59:59'),'yyyy-MM-dd HH24:mi:ss') "; }else if(dateTimeName.equals("发送时间")){ sql +=" and d.send_time>=to_date(concat('"+startTime+"',' 00:00:00'),'yyyy-MM-dd HH24:mi:ss')"; sql +=" and d.send_time<=to_date(concat('"+endTime+"',' 23:59:59'),'yyyy-MM-dd HH24:mi:ss') "; }else{ sql +=" and ord.CREATE_TIME>=to_date(concat('"+startTime+"',' 00:00:00'),'yyyy-MM-dd HH24:mi:ss')"; sql +=" and ord.CREATE_TIME<=to_date(concat('"+endTime+"',' 23:59:59'),'yyyy-MM-dd HH24:mi:ss') "; } } sql +=" order by ord.BATCH_NO desc"; cro=this.getDao("testDao").ExcuteQuery(sql.toString()); return cro; } /** * 查询材质检验项目信息 * @param parmas * @return * @throws SQLException */ /* public CoreReturnObject getQltySampleItem(String smp_no) throws SQLException { try{ StringBuffer sqlucomm = new StringBuffer(); sqlucomm.append("SELECT SPECIMEN_NO,SEQ,SMP_NO,PHY_NAME_L,PHY_CODE_L,PHY_CODE_M,PHY_NAME_M,"); sqlucomm.append("PHY_CODE_S,PHY_NAME_S,ITEM_CODE_D,ITEM_NAME_D,ITEM_CODE_T,ITEM_NAME_T,ITEM_CODE_S,ITEM_NAME_S,"); sqlucomm.append("ISJUDGE,JUDGE_BASIS,PHY_UNIT,GROUP_SEQ,QUOTE_SPECIMEN_NO,QUOTE_SEQ,MEMO,TEST_QTY,"); sqlucomm.append(" CREATE_NAME,CREATE_TIME FROM QCM_JHY_SAMPLE_CONSIGN_D_ITEM WHERE SMP_NO='"+smp_no+"'"); ResultSet results=this.getDao("KgDao").ExceuteQueryForResultSet(sqlucomm.toString()); JSONArray jsonArray=QuerryQltySample.resultSetToJsonArry(results); results.close(); if (jsonArray!=null && jsonArray.size()>0){ for (int i = 0; i < jsonArray.size(); i++) { JSONObject result = (JSONObject)jsonArray.get(i); sqlucomm.setLength(0); sqlucomm.append("SELECT R.VAL1,R.VAL2,R.VAL3,R.AVG_VAL,R.MAX_VAL,R.MIN_VAL,R.ITEM_CODE_L,R.ITEM_DESC_L from QCM_JHY_INSP_PHYSICS R where R.SPECIMEN_NO='"+result.get("SPECIMEN_NO")+"' "); sqlucomm.append(" AND R.PHY_CODE_L='"+result.get("PHY_CODE_L")+"' AND R.PHY_CODE_M='"+result.get("PHY_CODE_M")+"' AND R.PHY_CODE_S='"+result.get("PHY_CODE_S")+"'"); if (result.getString("ITEM_CODE_D")==null && !"".equals(result.getString("ITEM_CODE_D"))) { sqlucomm.append(" AND R.ITEM_CODE_D='"+result.get("ITEM_CODE_D")+"' "); } if (result.getString("ITEM_CODE_T")==null && !"".equals(result.getString("ITEM_CODE_T"))) { sqlucomm.append(" AND R.ITEM_CODE_T='"+result.get("ITEM_CODE_T")+"' "); } if (result.getString("ITEM_CODE_S")==null && !"".equals(result.getString("ITEM_CODE_S"))) { sqlucomm.append(" AND R.ITEM_CODE_S='"+result.get("ITEM_CODE_S")+"' "); } ResultSet quoteResults=this.getDao("KgDao").ExceuteQueryForResultSet(sqlucomm.toString());//QUOTE_SPECIMEN_NO QUOTE_SEQ JSONArray jsonArray2=QuerryQltySample.resultSetToJsonArry(quoteResults); quoteResults.close(); if (jsonArray2!=null && jsonArray2.size()>0){ JSONObject jsonQuote=(JSONObject)jsonArray2.get(0); result.put("VAL1", jsonQuote.get("VAL1")); result.put("VAL2", jsonQuote.get("VAL2")); result.put("VAL3", jsonQuote.get("VAL3")); result.put("AVG_VAL", jsonQuote.get("AVG_VAL")); result.put("MAX_VAL", jsonQuote.get("MAX_VAL")); result.put("MIN_VAL", jsonQuote.get("MIN_VAL")); result.put("ITEM_CODE_L", jsonQuote.get("ITEM_CODE_L")); result.put("ITEM_DESC_L", jsonQuote.get("ITEM_DESC_L")); } } cro.setResult(jsonArray); } }catch(Exception e){ e.printStackTrace(); } return cro; }*/ //String pline_code public CoreReturnObject getQltySampleItem(String smp_no) throws SQLException { String sql =" SELECT d.PROCESS_NOS 热处理号,t.SPECIMEN_NO, " +" t.SEQ, " +" t.SMP_NO, " +" t.PHY_NAME_L, " +" t.PHY_CODE_L, " +" t.PHY_CODE_M, " +" t.PHY_NAME_M, " +" t.PHY_CODE_S, " +" t.PHY_NAME_S, " +" t.ITEM_CODE_D, " +" t.ITEM_NAME_D, " +" t.ITEM_CODE_T, " +" t.ITEM_NAME_T, " +" t.ITEM_CODE_S, " +" t.ITEM_NAME_S, " +" t.ISJUDGE, " +" t.JUDGE_BASIS, " +" t.PHY_UNIT, " +" t.GROUP_SEQ, " +" t.QUOTE_SPECIMEN_NO, " +" t.QUOTE_SEQ, " +" t.MEMO, " +" t.TEST_QTY, " +" t.CREATE_NAME, " +" to_char(t.CREATE_TIME, 'yyyy-MM-dd hh24:mi:ss') CREATE_TIME, " +" r.VAL1, " +" r.VAL2, " +" r.VAL3, " +" r.AVG_VAL, " +" r.MAX_VAL, " +" r.MIN_VAL, " +" r.ITEM_CODE_L, " +" r.ITEM_DESC_L, " +" (select max(material_no) " +" from qcm_jhy_sample_consign_d a " +" where a.specimen_no = t.specimen_no) 取样产品序号, " +" decode(t.FY_QUOTE_SPECIMEN_NO, " +" null, " +" (select decode(max(a.Smp_Type_Code), '1', '复样', '0', '初样') " +" from qcm_jhy_sample_consign_d a " +" where a.specimen_no = t.specimen_no), " +" '初样') FY_QUOTE_SPECIMEN_NO, " +"(select IS_PASS from QCM_JUDGE_PHYSICAL_RESULT q where t.SPECIMEN_NO = q.Specimen_No AND t.PHY_CODE_L = q.Phy_Code_l AND t.PHY_CODE_M = q.Phy_Code_m AND t.PHY_CODE_S = q.Phy_Code_s and rownum < = 1) IS_PASS" +" FROM QCM_JHY_SAMPLE_CONSIGN_D_ITEM t, QCM_JHY_INSP_PHYSICS r,QCM_JHY_SAMPLE_CONSIGN_D d " +" WHERE t.SPECIMEN_NO = r.Specimen_No(+) " +" and t.seq = r.seq(+) and t.specimen_no = d.specimen_no " +" AND t.PHY_CODE_L = r.Phy_Code_l(+) " +" AND t.PHY_CODE_M = r.Phy_Code_m(+) " +" AND t.PHY_CODE_S = r.Phy_Code_s(+) " +" and t.SMP_NO = '"+smp_no+"' " +" order by t.SEQ asc "; cro=this.getDao("testDao").ExcuteQuery(sql.toString()); return cro; } /** * 人工引用界面 引用项目 * @param parmas * @return */ public CoreReturnObject getRgYitem(HashMap parmas) { try{ String sql ="select r.design_key,r.steel_code,to_char(r.THICK, 'fm990.099') thick,r.CERT_INST_NAME,r.batch_no,t.material_no,t.smp_type_name, " +" i.phy_name_l,i.phy_name_m,i.phy_name_s,i.item_name_d,i.item_name_t,i.item_name_s,i.item_desc_l,t.specimen_no,t.process_nos,t.Improve_Memo," +" (select count(1) from qcm_jhy_sample_consign_d_item it where it.specimen_no = i.specimen_no and it.seq<=49) numb,t.SMP_LOCATION " +" from qcm_jhy_sample_consign_d t, " +" qcm_jhy_sample_r_ord r, " +" qcm_jhy_sample_consign_d_item i " +" where t.smp_no = r.smp_no " +" and t.specimen_no = i.specimen_no " +" and t.specimen_no = '"+parmas.get("SPECIMEN_NO")+"' and i.seq<=49 " +" order by i.seq "; List listEle= mapper.query(sql.toString()); cro.setResult(listEle); UpdateInfo uinfo= new UpdateInfo(); uinfo.doPhyItem(parmas.get("SPECIMEN_NO").toString()); }catch(Exception ex){ cro.setV_errCode(-1); cro.setV_errMsg("获取引用项目出错"+ex.getMessage()); SqlSession.rollback(); } SqlSession.close(); return cro; } /** * 人工引用界面 被引用项目 * @param parmas * @return */ public CoreReturnObject getRgBYitem(HashMap parmas) { try{ String sqlStr=" and r.CERT_INST_CODE !='IC005' ";//美国船级社不能被引用 if(parmas.get("B_DESIGN_KEY").toString()!=null && parmas.get("B_DESIGN_KEY").toString()!="" && !parmas.get("B_DESIGN_KEY").toString().equals("全部")){ sqlStr += " and r.DESIGN_KEY='"+parmas.get("B_DESIGN_KEY").toString()+"' "; } if(parmas.get("B_STEEL_CODE").toString()!=null && parmas.get("B_STEEL_CODE").toString()!="" && !parmas.get("B_STEEL_CODE").toString().equals("全部")){ sqlStr += " and r.steel_code='"+parmas.get("B_STEEL_CODE").toString()+"' "; } if(parmas.get("B_THICK").toString()!=null && parmas.get("B_THICK").toString()!="" && !parmas.get("B_THICK").toString().equals("全部")){ sqlStr += " and to_char(r.THICK, 'fm990.099')=to_char("+parmas.get("B_THICK").toString()+", 'fm990.099') "; } String sqlD="select t.MATERIAL_NO,t.SPECIMEN_NO,t.FREQ_CODE,t.SMP_LOCATION_CODE from qcm_jhy_sample_consign_d t " + " where t.specimen_no = '"+parmas.get("SPECIMEN_NO")+"' "; List listD= mapper.query(sqlD.toString()); if(listD !=null && listD.size()>=1){ String FREQ_CODE = listD.get(0).get("FREQ_CODE").toString(); String SMP_LOCATION_CODE = listD.get(0).get("SMP_LOCATION_CODE").toString(); if(FREQ_CODE.equals("C") || FREQ_CODE.equals("H") || FREQ_CODE.equals("I")){ String MATERIAL_NO = listD.get(0).get("MATERIAL_NO").toString(); sqlStr +=" and t.MATERIAL_NO = '"+MATERIAL_NO+"' "; } if(SMP_LOCATION_CODE.equals("HA") || SMP_LOCATION_CODE.equals("HB")){ sqlStr += " and t.SMP_LOCATION_CODE in ('HA','HB') "; }else{ sqlStr += " and t.SMP_LOCATION_CODE = '"+SMP_LOCATION_CODE+"' "; } } String sql ="select r.design_key,i.seq,r.steel_code,to_char(r.THICK, 'fm990.099') THICK,r.batch_no,t.material_no," + " case when i.Quote_Specimen_No is null then " +" t.smp_type_name " +" else " +" (select d.smp_type_name from qcm_jhy_sample_consign_d d where d.specimen_no = i.quote_specimen_no and rownum=1) " +" end smp_type_name, " +" i.phy_name_l,i.phy_name_m,i.Phy_Code_s,i.phy_name_s,i.item_name_d,i.item_name_t,i.item_name_s,i.item_desc_l," +" t.specimen_no,t.process_nos,t.IMPROVE_MEMO,t.Smp_Location,r.Cert_Inst_Name,t.Freq_Name, " +" i.val1,i.val2,i.val3,i.avg_val,i.max_val,i.min_val,i.guid,p.phy_id,decode(p.is_pass,'0','合格','1','不合格') is_pass " +" from qcm_jhy_sample_consign_d t, " +" qcm_jhy_sample_r_ord r, " +" QCM_JHY_INSP_PHYSICS i, " +" QCM_JUDGE_PHYSICAL_RESULT p " +" where t.smp_no = r.smp_no " +" and t.specimen_no = i.specimen_no " +" and t.smp_no = p.smp_no " +" and t.specimen_no = p.specimen_no " +" and i.specimen_no = p.specimen_no " +" and i.guid = p.guid and r.phy_id = p.phy_id and i.create_name <>'现场默认' " +" and t.batch_no = '"+parmas.get("BATCH_NO")+"' " +" and r.JUDGE_STATUS <> '0' " //+" and r.Judge_Result_Code='1' " // +" AND p.SMP_NO not in ( " // +" select e.smp_no from QCM_JUDGE_PHYSICAL_RESULT e where e.batch_no = '"+parmas.get("BATCH_NO")+"' " // +" and e.PHY_CODE_S NOT IN ('J01','J02','J03','J04','J05','J06','J07','J08','J09') and e.is_pass = '1' group by e.smp_no " // +" ) " // +" and p.is_pass='0' " +" and t.status='3' " +" and t.Freq_Code <> 'D' " +" and to_number(to_char(r.THICK, 'fm990.099')) <= to_number(to_char('"+parmas.get("THICK")+"', 'fm990.099')) " +sqlStr +" group by r.design_key,i.seq,r.steel_code,r.thick,r.batch_no,t.material_no,t.smp_type_name, " +" i.phy_name_l,i.phy_name_m,i.Phy_Code_s,i.phy_name_s,i.item_name_d,i.item_name_t,i.item_name_s,i.item_desc_l," +" t.specimen_no,t.process_nos,t.IMPROVE_MEMO,t.Smp_Location,r.Cert_Inst_Name,t.Freq_Name, " +" i.val1,i.val2,i.val3,i.avg_val,i.max_val,i.min_val,i.guid,p.phy_id,i.Quote_Specimen_No,p.is_pass " +" order by t.specimen_no,i.seq "; List listEle= mapper.query(sql.toString()); if(listEle==null || listEle.size()<=0){ sql= ""; sql= "select * from (select r.design_key,i.seq,r.steel_code,to_char(r.THICK, 'fm990.099') THICK,r.batch_no,t.material_no, " +" case when i.Quote_Specimen_No is null then t.smp_type_name else " +" (select d.smp_type_name from qcm_jhy_sample_consign_d d where d.specimen_no = i.quote_specimen_no and rownum = 1) " +" end smp_type_name,i.phy_name_l,i.phy_name_m,i.Phy_Code_s,i.phy_name_s,i.item_name_d,i.item_name_t,i.item_name_s, " +" i.item_desc_l,t.specimen_no,t.process_nos,t.IMPROVE_MEMO,t.Smp_Location,r.Cert_Inst_Name,t.Freq_Name,i.val1, " +" i.val2,i.val3,i.avg_val,i.max_val,i.min_val,i.guid,p.phy_id,decode(p.is_pass, '0', '合格', '1', '不合格') is_pass " +" from qcm_jhy_sample_consign_d t,qcm_jhy_sample_r_ord r,QCM_JHY_INSP_PHYSICS i,QCM_JUDGE_PHYSICAL_RESULT p " +" where t.smp_no = r.smp_no and t.specimen_no = i.specimen_no and t.smp_no = p.smp_no and t.specimen_no = p.specimen_no " +" and i.specimen_no = p.specimen_no and i.guid = p.guid and r.phy_id = p.phy_id and i.create_name <>'现场默认' " +" and t.batch_no = '"+parmas.get("BATCH_NO")+"' " +" and r.JUDGE_STATUS <> '0' and t.status = '3' and t.Freq_Code <> 'D' " +" and to_number(to_char(r.THICK, 'fm990.099')) <= to_number(to_char('"+parmas.get("THICK")+"', 'fm990.099')) " +sqlStr +" UNION ALL " +" select r.design_key,i.seq,r.steel_code,to_char(r.THICK, 'fm990.099') THICK,r.batch_no,t.material_no, " +" case when i.Quote_Specimen_No is null then t.smp_type_name else " +" (select d.smp_type_name from qcm_jhy_sample_consign_d d where d.specimen_no = i.quote_specimen_no and rownum = 1) " +" end smp_type_name,i.phy_name_l,i.phy_name_m,i.Phy_Code_s,i.phy_name_s,i.item_name_d,i.item_name_t,i.item_name_s, " +" i.item_desc_l,t.specimen_no,t.process_nos,t.IMPROVE_MEMO,t.Smp_Location,r.Cert_Inst_Name,t.Freq_Name,i.val1, " +" i.val2,i.val3,i.avg_val,i.max_val,i.min_val,i.guid, '' phy_id,'未材质判定' is_pass " +" from qcm_jhy_sample_consign_d t,qcm_jhy_sample_r_ord r,QCM_JHY_INSP_PHYSICS i " +" where t.smp_no = r.smp_no and t.specimen_no = i.specimen_no and i.create_name <>'现场默认' " +" and t.batch_no = '"+parmas.get("BATCH_NO")+"' " +" and r.JUDGE_STATUS <> '0' and (r.Judge_Result_Code is null or r.JUDGE_RESULT_CODE='3') and t.status = '3' and t.Freq_Code <> 'D' " +" and to_number(to_char(r.THICK, 'fm990.099')) <= to_number(to_char('"+parmas.get("THICK")+"', 'fm990.099')) " +sqlStr +" ) t " +" group by t.design_key,t.seq,t.steel_code,t.thick,t.batch_no,t.material_no,t.smp_type_name,t.phy_name_l,t.phy_name_m, " +" t.Phy_Code_s,t.phy_name_s,t.item_name_d,t.item_name_t,t.item_name_s,t.item_desc_l,t.specimen_no,t.process_nos, " +" t.IMPROVE_MEMO,t.Smp_Location,t.Cert_Inst_Name,t.Freq_Name,t.val1,t.val2,t.val3,t.avg_val,t.max_val,t.min_val, " +" t.guid,t.phy_id,t.is_pass order by t.specimen_no, t.seq "; listEle= mapper.query(sql.toString()); } cro.setResult(listEle); }catch(Exception ex){ cro.setV_errCode(-1); cro.setV_errMsg("获取引用项目出错"+ex.getMessage()); SqlSession.rollback(); } SqlSession.close(); return cro; } /** * 人工引用界面 订单号 牌号 规格 * @param parmas * @return */ public CoreReturnObject getQueryDesignKey(HashMap parmas) { try{ String sql ="select r.design_key,r.steel_code,to_char(r.THICK, 'fm990.099') thick from qcm_jhy_sample_r_ord r " +" where r.batch_no = '"+parmas.get("BATCH_NO")+"' " +" and to_number(to_char(r.thick,'fm990.099'))<=to_number(to_char('"+parmas.get("THICK")+"','fm990.099')) " +" group by r.design_key,r.steel_code,r.thick "; List listEle= mapper.query(sql.toString()); cro.setResult(listEle); }catch(Exception ex){ cro.setV_errCode(-1); cro.setV_errMsg("获取当前订单号出错"+ex.getMessage()); SqlSession.rollback(); } SqlSession.close(); return cro; } /** * 人工引用界面 引用已选实绩 * @param parmas * @return */ public CoreReturnObject getBYITEM(String jsonArray,String specimenNo) { try{ int quotenum = 0;//已引用数据 int num1=0; String specimenNo2="";//被引用 String username= "系统引用"; String SEND_ID= "系统引用"; TreeSet trStr=new TreeSet(); if(specimenNo=="" || specimenNo==null){ cro.setV_errCode(-1); cro.setV_errMsg("引用取样编号为空！！！"); return cro; } JSONArray tarArr=JSON.parseArray(jsonArray);//被引用项目 //引用基本数据 String sql="select t.pline_code,t.batch_no,t.SMP_TYPE_CODE,t.MATERIAL_NO, " +" t.smp_no, " +" t.specimen_no," +" i.seq, " +" r.design_key, " +" r.steel_code,r.CERT_INST_CODE, " +" to_char(r.THICK, 'fm990.099') THICK," +" i.PHY_CODE_L, " +" i.phy_name_l, " +" i.PHY_CODE_M, " +" i.phy_name_m, " +" i.PHY_CODE_S, " +" i.phy_name_s, " +" i.ITEM_CODE_D, " +" i.item_name_d, " +" i.ITEM_CODE_T, " +" i.item_name_t, " +" i.ITEM_CODE_S, " +" i.item_name_s, " +" i.item_code_l, " +" i.item_desc_l " +" from qcm_jhy_sample_consign_d t, " +" qcm_jhy_sample_consign_m m, " +" qcm_jhy_sample_r_ord r, " +" qcm_jhy_sample_consign_d_item i " +" where t.smp_no = m.smp_no " +" and t.smp_no = r.smp_no " +" and m.smp_no = r.smp_no " +" and t.specimen_no = i.specimen_no " +" and t.freq_code <> 'D' " +" and i.seq <= 49 " +" and t.specimen_no = '"+specimenNo+"' "; List listItme= mapper.query(sql.toString()); if(listItme==null || listItme.size()<=0){ cro.setV_errCode(-1); cro.setV_errMsg("未找到引用项目！！！"); return cro; } //判断规格 String s_THICK = (String) listItme.get(0).get("THICK");//引用规格 String s_CERT_INST_CODE = (String) listItme.get(0).get("CERT_INST_CODE");//引用认证机构 String s_STEEL_CODE = (String) listItme.get(0).get("STEEL_CODE");//引用牌号 String s_PHY_CODE_S = "0"; //被引用规格 for (int j = 0; j < tarArr.size(); j++) { JSONObject newTar=tarArr.getJSONObject(j); String s_THICK2 =newTar.getString("THICK").toString(); if(!s_THICK.equals(s_THICK2)){ SqlSession.rollback(); SqlSession.close(); cro.setV_errCode(-1); cro.setV_errMsg("引用失败!选择的【'"+newTar.getString("PHY_NAME_S")+"'】规格不一样!"); return cro; } trStr.add(newTar.getString("SPECIMEN_NO").toString());//被引用取样编号 //判断是否勾选了冲击 if(newTar.getString("PHY_CODE_S").toString().equals("C01") || newTar.getString("PHY_CODE_S").toString().equals("C02")){ s_PHY_CODE_S="1"; } } //判断是否是挪威 if(s_CERT_INST_CODE.equals("IC001") && s_PHY_CODE_S.equals("1")){ String sqlcode=" select * from qcm_jhy_sample_consign_d_item i " +" where i.specimen_no = '"+specimenNo+"' and i.PHY_CODE_S = 'C01' and rownum = 1 "; List listcode= mapper.query(sqlcode.toString()); if(listcode !=null && listcode.size()>=1){ String ITEM_CODE_T = listcode.get(0).get("ITEM_CODE_T").toString();//温度 for (String str : trStr) {//被引用取样编号 String sqlcode2=" select * from qcm_jhy_sample_consign_d_item i " +" where i.specimen_no = '"+str+"' and i.PHY_CODE_S = 'C01' and rownum = 1 "; List listcode2= mapper.query(sqlcode2.toString()); if(listcode2 !=null && listcode2.size()>=1){ String ITEM_CODE_T2 = listcode2.get(0).get("ITEM_CODE_T").toString();//温度 if(!ITEM_CODE_T.equals(ITEM_CODE_T2)){ SqlSession.close(); cro.setV_errCode(-1); cro.setV_errMsg("引用失败!技术中心规定挪威船级社冲击温度一样才可引用!请核实数据"); return cro; } } } } } //船检限制 if(s_CERT_INST_CODE.equals("IC002") || s_CERT_INST_CODE.equals("IC005") || s_CERT_INST_CODE.equals("IC006") || s_CERT_INST_CODE.equals("IC007") || s_CERT_INST_CODE.equals("IC009") || s_CERT_INST_CODE.equals("IC008") ){ for (String str : trStr) {//被引用取样编号 String sqlr="select r.* from qcm_jhy_sample_consign_d d,qcm_jhy_sample_r_ord r where d.smp_no = r.smp_no and d.specimen_no = '"+str+"' and rownum = 1"; List hmr= mapper.query(sqlr.toString()); if(hmr!=null && hmr.size()>=1){ String strCIC=hmr.get(0).get("CERT_INST_CODE").toString(); String strSteelCode=hmr.get(0).get("STEEL_CODE").toString(); if(strCIC.equals("5000")){ SqlSession.rollback(); SqlSession.close(); cro.setV_errCode(-1); cro.setV_errMsg("引用失败!船检委托不能引用初样检测中心性能!请核实数据"); return cro; } if(!s_STEEL_CODE.equals(strSteelCode)){ SqlSession.rollback(); SqlSession.close(); cro.setV_errCode(-1); cro.setV_errMsg("引用失败!船检委托只能引用相同牌号性能!请核实数据"); return cro; } } } } String hbB01 ="";//弯曲 //引用项目循环 for (int i = 0; i < listItme.size(); i++) { if("B01".equals((String) listItme.get(i).get("PHY_CODE_S"))){ hbB01 ="0"; }else{ hbB01 =""; } //代码 String phyCodeL = (String) listItme.get(i).get("PHY_CODE_L");//材质检验大项代码 String phyCodeM = (String) listItme.get(i).get("PHY_CODE_M");//试样组代码 String phyCodeS = (String) listItme.get(i).get("PHY_CODE_S");//材质检验项目代码 String itemCodeD = (String) listItme.get(i).get("ITEM_CODE_D");//试验方向代码 String itemCodeT = (String) listItme.get(i).get("ITEM_CODE_T");//试验温度代码 试验温度代码 冲击 被引用 String itemCodeS = (String) listItme.get(i).get("ITEM_CODE_S");//试样尺寸代码 String itemCodeL = (String) listItme.get(i).get("ITEM_CODE_L");//试验位置代码 String newItem=phyCodeL+"-"+phyCodeM+"-"+phyCodeS; newItem=newItem+"-"+(itemCodeD!=null?itemCodeD:"null"); newItem=newItem+"-"+(itemCodeT!=null?itemCodeT:"null"); newItem=newItem+"-"+(itemCodeS!=null?itemCodeS:"null"); //弯曲 ///// String newItemHB=phyCodeL+"-"+phyCodeM+"-"+phyCodeS+"-"+(itemCodeD!=null?itemCodeD:"null")+"-"+(itemCodeT!=null?itemCodeT:"null"); //被引用 for (int j = 0; j < tarArr.size(); j++) { JSONObject newTar=tarArr.getJSONObject(j); username=newTar.getString("CREATE_NAME"); SEND_ID=newTar.getString("SEND_ID"); specimenNo2=newTar.getString("SPECIMEN_NO"); String phyId= newTar.getString("PHY_ID"); sql=""; if(!StringUtils.isBlank(phyId)){ sql="select to_char(r.THICK, 'fm990.099') THICK,t.specimen_no,t.PHY_CODE_L,t.PHY_CODE_M,t.PHY_CODE_S,t.ITEM_CODE_D,t.ITEM_CODE_T,t.ITEM_CODE_S,t.item_code_l,t.phy_name_s, " +" i.val1,i.val2,i.val3,i.avg_val,i.max_val,i.min_val,i.DEFECT_FLAG " +" from qcm_jhy_sample_consign_d d,qcm_jhy_sample_r_ord r,QCM_JHY_SAMPLE_CONSIGN_D_ITEM t, " +" QCM_JHY_INSP_PHYSICS i, " +" QCM_JUDGE_PHYSICAL_RESULT p " +" where d.smp_no=r.smp_no and d.specimen_no = t.specimen_no " + " and t.specimen_no = i.specimen_no and t.seq = i.seq " +" and t.specimen_no = p.specimen_no " +" and i.specimen_no = p.specimen_no " +" and i.guid = p.guid " +" and p.phy_id ='"+newTar.getString("PHY_ID")+"' " +" and t.specimen_no = '"+newTar.getString("SPECIMEN_NO")+"' " +" and t.PHY_CODE_S='"+newTar.getString("PHY_CODE_S")+"' " +" and i.guid='"+newTar.getString("GUID")+"' " +" and rownum=1 "; }else{ sql="select to_char(r.THICK, 'fm990.099') THICK,t.specimen_no,t.PHY_CODE_L,t.PHY_CODE_M,t.PHY_CODE_S,t.ITEM_CODE_D,t.ITEM_CODE_T,t.ITEM_CODE_S,t.item_code_l,t.phy_name_s, " +" i.val1,i.val2,i.val3,i.avg_val,i.max_val,i.min_val,i.DEFECT_FLAG " +" from qcm_jhy_sample_consign_d d,qcm_jhy_sample_r_ord r,QCM_JHY_SAMPLE_CONSIGN_D_ITEM t, " +" QCM_JHY_INSP_PHYSICS i " +" where d.smp_no=r.smp_no and d.specimen_no = t.specimen_no " + " and t.specimen_no = i.specimen_no and t.seq = i.seq " +" and t.specimen_no = '"+newTar.getString("SPECIMEN_NO")+"' " +" and t.PHY_CODE_S='"+newTar.getString("PHY_CODE_S")+"' " +" and i.guid='"+newTar.getString("GUID")+"' " +" and rownum=1 "; } List qltyLists=mapper.query(sql.toString()); if(qltyLists!=null && qltyLists.size()>=1){ //代码 String phyCodeL2 = (String) qltyLists.get(0).get("PHY_CODE_L");//材质检验大项代码 String phyCodeM2 = (String) qltyLists.get(0).get("PHY_CODE_M");//试样组代码 String phyCodeS2 = (String) qltyLists.get(0).get("PHY_CODE_S");//材质检验项目代码 String itemCodeD2 = (String) qltyLists.get(0).get("ITEM_CODE_D");//试验方向代码 String itemCodeT2 = (String) qltyLists.get(0).get("ITEM_CODE_T");//试验温度代码 试验温度代码 冲击 被引用 String itemCodeS2 = (String) qltyLists.get(0).get("ITEM_CODE_S");//试样尺寸代码 String itemCodeL2 = (String) qltyLists.get(0).get("ITEM_CODE_L");//试验位置代码 String VAL1 = (String) qltyLists.get(0).get("VAL1"); if(StringUtils.isBlank(VAL1)){VAL1="";} String VAL2 = (String) qltyLists.get(0).get("VAL2"); if(StringUtils.isBlank(VAL2)){VAL2="";} String VAL3 = (String) qltyLists.get(0).get("VAL3"); if(StringUtils.isBlank(VAL3)){VAL3="";} String AVG_VAL = (String) qltyLists.get(0).get("AVG_VAL"); if(StringUtils.isBlank(AVG_VAL)){AVG_VAL="";} String DEFECT_FLAG = (String) qltyLists.get(0).get("DEFECT_FLAG"); if(StringUtils.isBlank(DEFECT_FLAG)){DEFECT_FLAG="";} String MAX_VAL = (String) qltyLists.get(0).get("MAX_VAL"); if(StringUtils.isBlank(MAX_VAL)){MAX_VAL="";} String MIN_VAL = (String) qltyLists.get(0).get("MIN_VAL"); if(StringUtils.isBlank(MIN_VAL)){MIN_VAL="";} String newItem2=phyCodeL2+"-"+phyCodeM2+"-"+phyCodeS2; newItem2=newItem2+"-"+(itemCodeD2!=null?itemCodeD2:"null"); newItem2=newItem2+"-"+(itemCodeT2!=null?itemCodeT2:"null"); newItem2=newItem2+"-"+(itemCodeS2!=null?itemCodeS2:"null"); //弯曲 ///// String newItemHB2=phyCodeL2+"-"+phyCodeM2+"-"+phyCodeS2+"-"+(itemCodeD2!=null?itemCodeD2:"null")+"-"+(itemCodeT2!=null?itemCodeT2:"null"); //判断是否是冲击试验 if(phyCodeS.equals("C01") || phyCodeS.equals("C02")){ if(phyCodeS.equals(phyCodeS2)){ Integer co1t= Integer.parseInt(itemCodeT);//引用 Integer co2t= Integer.parseInt(itemCodeT2);//被引用 if(co1t >= co2t){ //删除引用实绩 sql = ""; sql = "DELETE qcm_jhy_insp_physics t where t.SPECIMEN_NO= '"+listItme.get(i).get("SPECIMEN_NO")+"' " + " and t.PHY_CODE_S = '"+listItme.get(i).get("PHY_CODE_S")+"' " + " and t.seq = '"+listItme.get(i).get("SEQ")+"' " + " and t.seq<=49 "; mapper.delete(sql.toString()); sql = ""; sql = " insert into qcm_jhy_insp_physics " +" (specimen_no, seq, test_qty, phy_code_l, phy_name_l, phy_code_m, phy_name_m, phy_code_s, phy_name_s, item_code_d, item_name_d, item_code_t, item_name_t, item_code_s, item_name_s, " +" val1, val2, val3, avg_val, create_name, create_time, group_seq, defect_flag, max_val, min_val, item_code_l, item_desc_l, phy_unit) " +" select i.specimen_no,i.seq,i.test_qty,i.phy_code_l,i.phy_name_l,i.phy_code_m,i.phy_name_m, i.phy_code_s, i.phy_name_s, i.item_code_d, i.item_name_d, i.item_code_t, i.item_name_t, i.item_code_s, i.item_name_s, " +" '"+VAL1+"','"+VAL2+"','"+VAL3+"','"+AVG_VAL+"','"+newTar.getString("CREATE_NAME")+"',sysdate,i.group_seq,'"+DEFECT_FLAG+"','"+MAX_VAL+"','"+MIN_VAL+"',i.item_code_l,i.item_desc_l,i.phy_unit " +" from qcm_jhy_sample_consign_d_item i " +" where i.specimen_no = '"+listItme.get(i).get("SPECIMEN_NO")+"' " +" and i.phy_code_s = '"+listItme.get(i).get("PHY_CODE_S")+"' " +" and i.seq= '"+listItme.get(i).get("SEQ")+"' "; mapper.insert(sql); //修改ITEM表 sql = ""; sql =" update QCM_JHY_SAMPLE_CONSIGN_D_ITEM t set t.QUOTE_MEMO = '已引用"+newTar.getString("SPECIMEN_NO")+"' " + " where t.SPECIMEN_NO ='"+listItme.get(i).get("SPECIMEN_NO")+"' " + " and t.PHY_CODE_S = '"+listItme.get(i).get("PHY_CODE_S")+"' and t.seq = '"+listItme.get(i).get("SEQ")+"' "; mapper.updateJudgeStatus(sql.toString()); quotenum++; break; } } }else{//其它试验 //标准一样的引用 if(newItem.equals(newItem2)){ //删除引用实绩 sql = ""; sql = "DELETE qcm_jhy_insp_physics t where t.SPECIMEN_NO= '"+listItme.get(i).get("SPECIMEN_NO")+"' " + " and t.PHY_CODE_S = '"+listItme.get(i).get("PHY_CODE_S")+"' " + " and t.seq = '"+listItme.get(i).get("SEQ")+"' " + " and t.seq<=49 "; mapper.delete(sql.toString()); sql = ""; sql = " insert into qcm_jhy_insp_physics " +" (specimen_no, seq, test_qty, phy_code_l, phy_name_l, phy_code_m, phy_name_m, phy_code_s, phy_name_s, item_code_d, item_name_d, item_code_t, item_name_t, item_code_s, item_name_s, " +" val1, val2, val3, avg_val, create_name, create_time, group_seq, defect_flag, max_val, min_val, item_code_l, item_desc_l, phy_unit) " +" select i.specimen_no,i.seq,i.test_qty,i.phy_code_l,i.phy_name_l,i.phy_code_m,i.phy_name_m, i.phy_code_s, i.phy_name_s, i.item_code_d, i.item_name_d, i.item_code_t, i.item_name_t, i.item_code_s, i.item_name_s, " +" '"+VAL1+"','"+VAL2+"','"+VAL3+"','"+AVG_VAL+"','"+newTar.getString("CREATE_NAME")+"',sysdate,i.group_seq,'"+DEFECT_FLAG+"','"+MAX_VAL+"','"+MIN_VAL+"',i.item_code_l,i.item_desc_l,i.phy_unit " +" from qcm_jhy_sample_consign_d_item i " +" where i.specimen_no = '"+listItme.get(i).get("SPECIMEN_NO")+"' " +" and i.phy_code_s = '"+listItme.get(i).get("PHY_CODE_S")+"' " +" and i.seq= '"+listItme.get(i).get("SEQ")+"' "; mapper.insert(sql); //修改ITEM表 sql = ""; sql =" update QCM_JHY_SAMPLE_CONSIGN_D_ITEM t set t.QUOTE_MEMO = '已引用"+newTar.getString("SPECIMEN_NO")+"' " + " where t.SPECIMEN_NO ='"+listItme.get(i).get("SPECIMEN_NO")+"' " + " and t.PHY_CODE_S = '"+listItme.get(i).get("PHY_CODE_S")+"' and t.seq = '"+listItme.get(i).get("SEQ")+"' "; mapper.updateJudgeStatus(sql.toString()); quotenum++; break; } } } } //申报:郭明清 主题:非计划船板改普板，冷弯需要系统默认合格 申报时间:2023-04-27 16:49:59 //原牌号为船板 BV/IC003法国船级社,KR/IC004韩国船级社,DNV/IC001挪威船级社,CCS/IC010中国船级社 、ABS/IC005美国船级社、LR/IC009 英国船级社、NK/KA36/IC006 日本船级社、RINA/IC007 意大利船级社 、RS/IC002 俄罗斯 IC012 客户认证 //改成普锰板(Q235A/B、Q345A/B、Q355B) //被引用是原牌号船板 引用是改判 普锰板 //弯曲 默认合格 if("B01".equals((String) listItme.get(i).get("PHY_CODE_S"))&& !"".equals(hbB01) && hbB01.equals("0") && !listItme.get(i).get("SPECIMEN_NO").toString().contains("YT1") ){ sql=""; sql="select d.* from qcm_jhy_sample_consign_d d,qcm_jhy_sample_r_ord r " +" where d.smp_no = r.smp_no and r.steel_code in ('Q235A','Q235B','Q345A','Q345B','Q355B') " +" and d.specimen_no = '"+specimenNo+"' "; List hashMap=mapper.query(sql.toString()); if(hashMap!=null && hashMap.size()>=1){ sql=""; sql="select r.* from qcm_jhy_sample_consign_d d,qcm_jhy_sample_r_ord r " +" where d.smp_no = r.smp_no and r.cert_inst_code in ('IC005','IC009','IC006','IC007','IC002','IC003','IC004','IC001','IC010') " +" and d.specimen_no = '"+specimenNo2+"' "; List hashMap2=mapper.query(sql.toString()); sql=""; sql="select * from qcm_jhy_sample_consign_d_item where specimen_no = '"+specimenNo2+"' and phy_code_s='B01' "; List hashMap3=mapper.query(sql.toString()); if(hashMap2!=null && hashMap2.size()>=1 && hashMap3.size()<=0 ){ sql=""; sql="insert into QCM_JHY_INSP_PHYSICS " +" (SPECIMEN_NO,SEQ,TEST_QTY,PHY_CODE_L,PHY_NAME_L,PHY_CODE_M,PHY_NAME_M,PHY_CODE_S,PHY_NAME_S,ITEM_CODE_D,ITEM_NAME_D, " +" ITEM_CODE_T,ITEM_NAME_T,ITEM_CODE_S,ITEM_NAME_S,VAL1,AVG_VAL,CREATE_NAME,CREATE_TIME,DEFECT_FLAG,MAX_VAL, " +" MIN_VAL,ITEM_CODE_L,ITEM_DESC_L,PHY_UNIT,MEMO) " +" select t.specimen_no,t.seq,t.test_qty,t.phy_code_l,t.phy_name_l,t.phy_code_m,t.phy_name_m,t.phy_code_s,t.phy_name_s,t.item_code_d,t.item_name_d, " +" t.item_code_t,t.item_name_t,t.item_code_s,t.item_name_s,'合格','合格','"+username+"',sysdate,'0','合格','合格',t.item_code_l,t.item_desc_l, " +" t.phy_unit,'默认合格' " +" from qcm_jhy_sample_consign_d_item t " +" where t.specimen_no = '"+specimenNo+"' " +" and t.phy_code_s = 'B01' "; mapper.insert(sql); //修改ITEM表 sql = ""; sql =" update QCM_JHY_SAMPLE_CONSIGN_D_ITEM t set t.QUOTE_MEMO = '默认合格' " + " where t.SPECIMEN_NO ='"+specimenNo+"' and t.phy_code_s = 'B01' "; mapper.updateJudgeStatus(sql.toString()); quotenum++; } } } } //获取被引用的LIMS接收时间先放这 sql=""; sql="select * from qcm_jhy_insp_physics t where t.specimen_no = '"+specimenNo+"' and t.seq <= 49 "; List snoSQL= mapper.query(sql.toString()); //被引用的项目 全部 已引用 if((quotenum == listItme.size()) || (listItme.size() == snoSQL.size())){ //修改D表 sql = ""; sql =" update QCM_JHY_SAMPLE_CONSIGN_D t set t.STATUS = '3',t.SEND_ID = '"+SEND_ID+"'," + " t.SEND_NAME = '"+username+"',t.SEND_TIME = sysdate,t.SEND_MEMO = '有引用',t.QUOTE_MEMO = '引用实绩' " + " where t.batch_no = '"+listItme.get(0).get("BATCH_NO")+"' " + " and SPECIMEN_NO ='"+listItme.get(0).get("SPECIMEN_NO")+"' "; mapper.updateJudgeStatus(sql.toString()); if(listItme.get(0).get("SMP_TYPE_CODE").toString().equals("1")){ //往log表中插入p sql = ""; sql = " INSERT INTO QCM_JUDGE_LOG ( JUDGE_TYPE,MATERIAL_NO,CREATE_TIME,CREATE_NAME,PROD_LINE) " +" VALUES ( 'Q', '"+listItme.get(0).get("SMP_NO")+"', SYSDATE,'system','"+listItme.get(0).get("PLINE_CODE")+"')"; mapper.insert(sql); }else{ //往log表中插入p sql = ""; sql = " INSERT INTO QCM_JUDGE_LOG ( JUDGE_TYPE,MATERIAL_NO,CREATE_TIME,CREATE_NAME,PROD_LINE) " +" VALUES ( 'P', '"+listItme.get(0).get("SMP_NO")+"', SYSDATE,'system','"+listItme.get(0).get("PLINE_CODE")+"')"; mapper.insert(sql); } }else{ //修改D表 sql = ""; sql =" update QCM_JHY_SAMPLE_CONSIGN_D t set t.SEND_MEMO = '有引用',t.QUOTE_MEMO = '引用实绩' " + " where t.batch_no = '"+listItme.get(0).get("BATCH_NO")+"' " + " and SPECIMEN_NO ='"+listItme.get(0).get("SPECIMEN_NO")+"' "; mapper.updateJudgeStatus(sql.toString()); } //记录 QcmJudgeOperateLog qjog = new QcmJudgeOperateLog(); qjog.setOperate_name(username== null ?"":username); qjog.setBatchno(listItme.get(0).get("BATCH_NO") == null ?"":listItme.get(0).get("BATCH_NO").toString()); qjog.setOperate_type("引用同批实绩"); qjog.setParams(listItme.get(0).get("MATERIAL_NO") == null ?"":listItme.get(0).get("MATERIAL_NO").toString()); qjog.setProd_line(listItme.get(0).get("PLINE_CODE") == null ?"":listItme.get(0).get("PLINE_CODE").toString()); qjog.setMemo(specimenNo); mapper.insertQcmJudgeOperateLog(qjog); }catch(Exception e){ SqlSession.rollback(); SqlSession.close(); cro.setV_errCode(-1); cro.setV_errMsg("引用失败!"+e.getMessage()); return cro; } SqlSession.commit(); SqlSession.close(); cro.setV_errCode(1); cro.setV_errMsg("引用成功!"); UpdateInfo uinfo= new UpdateInfo(); try { uinfo.doPhyItem(specimenNo); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return cro; } /** * 订单标准错误 按订单删除委托 * @param parmas * @return */ public CoreReturnObject getConsignD(HashMap parmas) { try{ String sqlStr=""; if(parmas.get("B_DK").toString()!=null && parmas.get("B_DK").toString()!="" && !parmas.get("B_DK").toString().equals("全部")){ sqlStr += " and r.DESIGN_KEY like '"+parmas.get("B_DK").toString()+"%' "; } if(parmas.get("DESIGN_KEY").toString()!=null && parmas.get("DESIGN_KEY").toString()!="" && !parmas.get("DESIGN_KEY").toString().equals("全部")){ sqlStr += " and r.DESIGN_KEY='"+parmas.get("DESIGN_KEY").toString()+"' "; } if(parmas.get("BATCH_NO").toString()!=null && parmas.get("BATCH_NO").toString()!="" && !parmas.get("BATCH_NO").toString().equals("全部")){ sqlStr += " and t.BATCH_NO='"+parmas.get("BATCH_NO").toString()+"' "; } String sql ="select r.design_key,r.steel_code,r.thick,t.batch_no,t.heat_no,t.material_no,t.smp_no,t.specimen_no,t.quote_consign_no,t.inspection_lot, " +" t.freq_code,t.freq_name,t.pline_code,t.pline_name,t.smp_type_code,t.smp_type_name,t.SMP_LOCATION_CODE,t.smp_location,t.onememo,m.Smp_Catg," +" m.Cert_Inst_Code,m.cert_inst_name,t.process_nos,t.Improve_Memo,t.status,r.Judge_Result_Code,r.Judge_Result_Desc " +" from qcm_jhy_sample_consign_d t,qcm_jhy_sample_consign_m m, qcm_jhy_sample_r_ord r " +" where t.smp_no = r.smp_no " +" and t.smp_no = m.smp_no " +" and m.smp_no = r.smp_no and t.status in ('0','1','2','3','7') " +sqlStr; List listEle= mapper.query(sql.toString()); cro.setResult(listEle); }catch(Exception ex){ cro.setV_errCode(-1); cro.setV_errMsg("获取当前订单号出错"+ex.getMessage()); SqlSession.rollback(); } SqlSession.close(); return cro; } /** * 订单标准错误 按订单删除委托日志 * @param parmas * @return */ public CoreReturnObject getConsignDLog(HashMap parmas) { try{ String sqlStr=""; if(parmas.get("B_DK").toString()!=null && parmas.get("B_DK").toString()!="" && !parmas.get("B_DK").toString().equals("全部")){ sqlStr += " and t.JUDGE_RESULT like '"+parmas.get("B_DK").toString()+"%' "; } if(parmas.get("DESIGN_KEY").toString()!=null && parmas.get("DESIGN_KEY").toString()!="" && !parmas.get("DESIGN_KEY").toString().equals("全部")){ sqlStr += " and t.JUDGE_RESULT='"+parmas.get("DESIGN_KEY").toString()+"' "; } if(parmas.get("BATCH_NO").toString()!=null && parmas.get("BATCH_NO").toString()!="" && !parmas.get("BATCH_NO").toString().equals("全部")){ sqlStr += " and t.BATCHNO='"+parmas.get("BATCH_NO").toString()+"' "; } String sql =" select t.judge_result DESIGN_KEY,t.batchno,t.prod_line,t.params,t.operate_name, " +" to_char(t.operate_time,'YYYY-MM-DD hh24:mi:ss') operate_time,t.memo from qcm_judge_operate_log t" +" where t.operate_type = '一键删除委托' " +sqlStr; List listEle= mapper.query(sql.toString()); cro.setResult(listEle); }catch(Exception ex){ cro.setV_errCode(-1); cro.setV_errMsg("获取当前订单号出错"+ex.getMessage()); SqlSession.rollback(); } SqlSession.close(); return cro; } /** * LIMS退回轧批查询 * @param parmas * @return */ public CoreReturnObject ZHBQueryLims(HashMap parmas) { try{ if (!SqlJoint.IsNullOrSpace(parmas.get("STARTTIME").toString()) && !SqlJoint.IsNullOrSpace(parmas.get("ENDTIME").toString())) { String sky=StrSky(parmas.get("STARTTIME").toString(),parmas.get("ENDTIME").toString()); if(sky.equals("FALSE")){ SqlSession.close(); cro.setV_errCode(-1); cro.setV_errMsg("查询时间不能大于30天,请核实查询时间条件!"); return cro; } } String sqlStr = " and t.PLINE_CODE like '"+parmas.get("PLINE_CODE").toString()+"%' "; if(StringUtils.isNotBlank(parmas.get("DESIGN_KEY").toString()) ){ sqlStr += " and r.DESIGN_KEY='"+parmas.get("DESIGN_KEY").toString()+"' "; } if (parmas.get("BATCH_NO")!=null && !"".equals(((String)parmas.get("BATCH_NO")).trim()) && parmas.get("BATCH_NO2")!=null && !"".equals(((String)parmas.get("BATCH_NO2")).trim()) ) { sqlStr +=" and upper(t.batch_no) >= upper('"+parmas.get("BATCH_NO")+"')"; sqlStr +=" and upper(t.batch_no) <= upper('"+parmas.get("BATCH_NO2")+"')"; }else if(parmas.get("BATCH_NO")!=null && !"".equals(((String)parmas.get("BATCH_NO")).trim()) && parmas.get("checkboxVal").equals("1")){ sqlStr +=" and t.batch_no like '"+parmas.get("BATCH_NO")+"%' "; } //退回时间 if (parmas.get("STARTTIME")!=null && !"".equals(((String)parmas.get("STARTTIME")).trim())) { sqlStr +=" and t.RETURN_TIME>=to_date(concat('"+parmas.get("STARTTIME")+"',' 00:00:00'),'yyyy-MM-dd HH24:mi:ss')"; } if (parmas.get("ENDTIME")!=null && !"".equals(((String)parmas.get("ENDTIME")).trim())) { sqlStr +=" and t.RETURN_TIME<=to_date(concat('"+parmas.get("ENDTIME")+"',' 23:59:59'),'yyyy-MM-dd HH24:mi:ss') "; } //YT1 有子板查询条件 if("YT1".equals(parmas.get("PLINE_CODE").toString())){ if (parmas.get("MATERIAL_NO")!=null && !"".equals(((String)parmas.get("MATERIAL_NO")).trim())) { sqlStr += " and t.material_no LIKE '"+parmas.get("MATERIAL_NO").toString()+"%' "; } } String sql ="select r.design_key,r.steel_code,r.thick,t.batch_no,t.heat_no,t.material_no,t.smp_no,t.specimen_no,t.quote_consign_no,t.inspection_lot, " +" t.freq_code,t.freq_name,t.pline_code,t.pline_name,t.smp_type_code,t.smp_type_name,t.SMP_LOCATION_CODE,t.smp_location,t.onememo,m.Smp_Catg," +" m.Cert_Inst_Code,m.cert_inst_name,t.process_nos,t.Improve_Memo,t.status,r.Judge_Result_Code,r.Judge_Result_Desc,to_char(t.Collect_Time,'yyyy-MM-dd hh24:mi:ss') COLLECT_TIME," +" to_char(t.RETURN_TIME,'yyyy-MM-dd hh24:mi:ss') RETURN_TIME,T.RETURN_MEMO,T.RETURN_NAME " +" from qcm_jhy_sample_consign_d t,qcm_jhy_sample_consign_m m, qcm_jhy_sample_r_ord r " +" where t.smp_no = r.smp_no and t.smp_no = m.smp_no and m.smp_no = r.smp_no " +" and T.RETURN_NAME IS NOT NULL and t.status not in ('4','5','6') " +sqlStr; List listEle= mapper.query(sql.toString()); cro.setResult(listEle); }catch(Exception ex){ cro.setV_errCode(-1); cro.setV_errMsg("获取委托出错"+ex.getMessage()); SqlSession.rollback(); } SqlSession.close(); return cro; } //查询中厚板LRA public CoreReturnObject getZHBLRA(HashMap parmas) { try{ String sqlStr=""; if(parmas.get("STARTTIME").toString()!=null && !"".equals(((String)parmas.get("STARTTIME")).trim()) && parmas.get("ENDTIME").toString()!=null && !"".equals(((String)parmas.get("ENDTIME")).trim()) ){ sqlStr +=" and t.CREATE_TIME>=to_date('"+parmas.get("STARTTIME")+" 00:00:00','yyyy-MM-dd HH24:mi:ss')"; sqlStr +=" and t.CREATE_TIME<=to_date('"+parmas.get("ENDTIME")+" 23:59:59','yyyy-MM-dd HH24:mi:ss') "; } if(parmas.get("BATCH_NO").toString()!=null && !"".equals(((String)parmas.get("BATCH_NO")).trim()) ){ sqlStr += " and t.BATCH_NO='"+parmas.get("BATCH_NO").toString()+"' "; } if(parmas.get("SPECIMEN_NO").toString()!=null && !"".equals(((String)parmas.get("SPECIMEN_NO")).trim()) ){ sqlStr += " and t.SPECIMEN_NO='"+parmas.get("SPECIMEN_NO").toString()+"' "; } String sql =" select r.design_key,r.steel_code,r.thick,t.batch_no,t.material_no,t.smp_no,t.specimen_no,t.freq_name, " +" t.smp_type_name,t.smp_location,t.pline_code,t.pline_name,t.status,r.cert_inst_name,to_char(t.create_time,'YYYY-MM-dd hh24:mi:ss') create_time " +" from qcm_jhy_sample_consign_d t,qcm_jhy_sample_consign_m m,qcm_jhy_sample_r_ord r " +" where t.smp_no = m.smp_no and t.smp_no = r.smp_no and m.smp_no = r.smp_no " +" and m.Smp_Catg='B' and r.steel_code like 'LRA%' and t.Special_Quote_Memo is not null " +" and t.pline_code in ('HB1','ZB1') and t.status = '0' and t.Smp_Type_Code = '0' " + sqlStr +" order by t.create_time desc "; List listEle= mapper.query(sql.toString()); cro.setResult(listEle); }catch(Exception ex){ cro.setV_errCode(-1); cro.setV_errMsg("获取出错"+ex.getMessage()); SqlSession.rollback(); } SqlSession.close(); return cro; } //查询中厚板LRA引用 public CoreReturnObject getZHBLRAITEM(HashMap parmas) { try{ String sql =" select r.design_key,r.steel_code,r.thick,t.batch_no,t.material_no,t.smp_no,t.specimen_no,t.freq_name, " +" t.smp_type_name,t.smp_location,t.pline_code,t.pline_name,t.status,r.cert_inst_name,to_char(t.create_time,'YYYY-MM-dd hh24:mi:ss') create_time " +" from qcm_jhy_sample_consign_d t,qcm_jhy_sample_consign_m m,qcm_jhy_sample_r_ord r,qcm_jhy_sample_consign_d_item i " +" where t.smp_no = m.smp_no and t.smp_no = r.smp_no and m.smp_no = r.smp_no and t.specimen_no = i.specimen_no " +" and t.smp_no = i.smp_no and m.smp_no = i.smp_no and r.smp_no = i.smp_no " +" and m.Smp_Catg='B' and r.steel_code like 'LRA%' and t.pline_code in ('HB1','ZB1') and t.Smp_Type_Code = '0' " +" and i.quote_specimen_no = '"+parmas.get("SPECIMEN_NO").toString()+"' " +" group by r.design_key,r.steel_code,r.thick,t.batch_no,t.material_no,t.smp_no,t.specimen_no,t.freq_name, " +" t.smp_type_name,t.smp_location,t.pline_code,t.pline_name,t.status,r.cert_inst_name,t.create_time " +" order by t.status asc "; List listEle= mapper.query(sql.toString()); cro.setResult(listEle); }catch(Exception ex){ cro.setV_errCode(-1); cro.setV_errMsg("获取出错"+ex.getMessage()); SqlSession.rollback(); } SqlSession.close(); return cro; } //查询中厚板是否有合并 public CoreReturnObject Sel_ZHB_HB(String Entrust) { try{ String[] ja=Entrust.split(","); String msInfo=""; if (ja!=null && ja.length>0) { for (int i = 0; i < ja.length; i++) { String sp = ja[i]; if ("".equals(msInfo)) { msInfo="'"+sp+"'"; }else{ msInfo=msInfo+"'"+sp+"'"; } if (i !=ja.length-1) { msInfo=msInfo+","; } } } //主题：关于DNV性能引用 技术中心李娇丽提出 申报时间2024-10-28 11:06:27 //2024年挪威船级社年审提出，低等级不能引用高等级性能，因为低等级的冲击未进行试验，没有试验原始记录，挪威船级社DNV需要在冲击温度相同的情况下才能引用性能。 //241028修改 ,'IC001' 去掉 String sql =" select d.specimen_no,r.* from qcm_jhy_sample_consign_d d,qcm_jhy_sample_consign_m m,qcm_jhy_sample_r_ord r " +" where d.smp_no = m.smp_no and d.smp_no = r.smp_no and m.smp_no = r.smp_no " +" and r.design_key not like '19%' " +" and d.freq_code = 'A' " +" and d.smp_type_code = '0' " +" and d.status = '0' and d.IMPROVE_MEMO is null " +" and r.std_code not like '%ASTM%' " +" and r.std_code not like '%ASME%' " +" and r.Cert_Inst_Code in ('5000','IC010','IC003','IC004') " +" and m.SMP_CATG = 'B' " +" and d.specimen_no in ("+msInfo+") " +" and d.specimen_no not in ( select i.specimen_no from qcm_jhy_sample_consign_d_item i " +" where i.specimen_no in ("+msInfo+") and i.phy_code_l = 'HC' and to_number(REGEXP_REPLACE(i.item_code_t,'[^-0-9]','')) <= -40 ) "; List listEle= mapper.query(sql.toString()); if(listEle!=null && listEle.size()>=1){ for (HashMap hashMap : listEle) { String BATCH_NO = (String) hashMap.get("BATCH_NO"); String mnhhsql="select * from qcm_jhy_sample_consign_d t where t.Smp_Location like '%模拟焊后%' and t.batch_no = '"+BATCH_NO+"' ";//判断是否是模拟焊后轧批 List listm= mapper.query(mnhhsql.toString()); if(listm!=null && listm.size()>=1){ continue; } String DELIVERY_STATE_DESC = (String) hashMap.get("DELIVERY_STATE_DESC"); String THICK = (String) hashMap.get("THICK"); sql=" select * from ( select d.specimen_no,r.design_key,r.steel_code,r.thick,r.batch_no, " +" QCM_SPECFLAG(r.STEEL_CODE, r.THICK, r.std_code, r.CERT_INST_NAME) qcmSpecflag " +" from qcm_jhy_sample_consign_d d,qcm_jhy_sample_consign_m m,qcm_jhy_sample_r_ord r " +" where d.smp_no = m.smp_no and d.smp_no = r.smp_no and m.smp_no = r.smp_no " +" and r.design_key not like '19%' " +" and d.freq_code = 'A' " +" and d.smp_type_code = '0' " +" and d.status = '0' and d.IMPROVE_MEMO is null " +" and r.std_code not like '%ASTM%' " +" and r.std_code not like '%ASME%' " +" and r.Cert_Inst_Code in ('5000','IC010','IC003','IC004') " +" and m.SMP_CATG = 'B' " +" and r.Delivery_State_Desc='"+DELIVERY_STATE_DESC+"' " +" and r.thick='"+THICK+"' " +" and d.batch_no ='"+BATCH_NO+"' " +" and d.specimen_no not in ( select i.specimen_no from qcm_jhy_sample_consign_d t,qcm_jhy_sample_consign_d_item i " +" where t.specimen_no = i.specimen_no and t.batch_no = '"+BATCH_NO+"' and t.specimen_no in ("+msInfo+") and i.phy_code_l = 'HC' " +" and to_number(REGEXP_REPLACE(i.item_code_t,'[^-0-9]','')) <= -40 ) " +" )t where t.qcmSpecflag in ('1','0') "; List listBatchNo= mapper.query(sql.toString()); if(listBatchNo!=null && listBatchNo.size()>=2){ cro.setV_errCode(-1); cro.setV_errMsg("轧批:"+BATCH_NO+",有多牌号或同牌号多委托其中有可以合并的[请选择同批合并发送]"); return cro; }else{ sql=""; sql="select * from (select d.specimen_no,r.design_key,r.steel_code,r.thick,r.batch_no, " +" QCM_SPECFLAG(r.STEEL_CODE, r.THICK, r.std_code, r.CERT_INST_NAME) qcmSpecflag " +" from qcm_jhy_sample_consign_d d,qcm_jhy_sample_consign_m m,qcm_jhy_sample_r_ord r " +" where d.smp_no = m.smp_no and d.smp_no = r.smp_no and m.smp_no = r.smp_no " +" and r.design_key not like '19%' " +" and d.freq_code = 'A' " +" and d.smp_type_code = '0' " +" and d.status = '0' and d.IMPROVE_MEMO is null " +" and r.std_code not like '%ASTM%' " +" and r.std_code not like '%ASME%' " +" and r.Cert_Inst_Code in ('5000','IC010','IC003','IC004') " +" and m.SMP_CATG = 'B' " +" and r.Delivery_State_Desc in ('TMCP','AR','CR') " +" and r.thick='"+THICK+"' " +" and (r.STD_CODE like '%Rules' OR r.steel_code in ('Q235A','Q235B','Q345A','Q345B','Q355B')) " +" and d.batch_no ='"+BATCH_NO+"' " +" and d.specimen_no not in ( select i.specimen_no from qcm_jhy_sample_consign_d t,qcm_jhy_sample_consign_d_item i " +" where t.specimen_no = i.specimen_no and t.batch_no = '"+BATCH_NO+"' and t.specimen_no in ("+msInfo+") and i.phy_code_l = 'HC' " +" and to_number(REGEXP_REPLACE(i.item_code_t,'[^-0-9]','')) <= -40 ) " +" )t where t.qcmSpecflag in ('1','0') "; List listBatchNo2= mapper.query(sql.toString()); if(listBatchNo2!=null && listBatchNo2.size()>=2){ cro.setV_errCode(-1); cro.setV_errMsg("轧批:"+BATCH_NO+",有多牌号或同牌号多委托其中有可以合并的[请选择同批合并发送]"); return cro; } } } } }catch(Exception ex){ cro.setV_errCode(-1); cro.setV_errMsg("获取出错"+ex.getMessage()); SqlSession.rollback(); } cro.setV_errCode(0); SqlSession.close(); return cro; } //船检编号 public CoreReturnObject getHZBNumber(HashMap parmas) { try{ if (!SqlJoint.IsNullOrSpace(parmas.get("SEND_TIME1").toString()) && !SqlJoint.IsNullOrSpace(parmas.get("SEND_TIME2").toString())) { String sky=StrSky(parmas.get("SEND_TIME1").toString(),parmas.get("SEND_TIME2").toString()); if(sky.equals("FALSE")){ SqlSession.close(); cro.setV_errCode(-1); cro.setV_errMsg("查询时间不能大于30天,请核实查询时间条件!"); return cro; } } String sqlStr=""; if(!StringUtils.isBlank(parmas.get("SEND_TIME1")) && !StringUtils.isBlank(parmas.get("SEND_TIME2"))){ sqlStr +=" and d.SEND_TIME>=to_date('"+parmas.get("SEND_TIME1")+" 00:00:00','yyyy-MM-dd HH24:mi:ss')"; sqlStr +=" and d.SEND_TIME<=to_date('"+parmas.get("SEND_TIME2")+" 23:59:59','yyyy-MM-dd HH24:mi:ss') "; } if(!StringUtils.isBlank(parmas.get("SAMPLE_DELIVERY_TIME"))){ sqlStr += " and d.SAMPLE_DELIVERY_TIME='"+parmas.get("SAMPLE_DELIVERY_TIME").toString()+"' "; } if(!StringUtils.isBlank(parmas.get("CERT_INST_CODE"))){ sqlStr += " and m.CERT_INST_CODE='"+parmas.get("CERT_INST_CODE").toString()+"' "; } if(!StringUtils.isBlank(parmas.get("SMP_CATG_CODE"))){ sqlStr += " and m.SMP_CATG='"+parmas.get("SMP_CATG_CODE").toString()+"' "; } if(!StringUtils.isBlank(parmas.get("BATCH_NO1")) && !StringUtils.isBlank(parmas.get("BATCH_NO2"))){ sqlStr +=" and upper(d.batch_no) >= upper('"+parmas.get("BATCH_NO1")+"') "; sqlStr +=" and upper(d.batch_no) <= upper('"+parmas.get("BATCH_NO2")+"') "; }else if(!StringUtils.isBlank(parmas.get("BATCH_NO1"))){ sqlStr +=" and upper(d.batch_no) = '"+parmas.get("BATCH_NO1")+"' "; } if(!StringUtils.isBlank(parmas.get("PLINE_CODE"))){ sqlStr += " and d.PLINE_CODE='"+parmas.get("PLINE_CODE").toString()+"' "; }else{ sqlStr += " and d.PLINE_CODE in ('ZB1','HB1') "; } String sql =" select d.SAMPLE_DELIVERY_TIME, d.SEND_SEQ, " +" d.PRINT_SEQ, " +" d.PRINT_SEQJ, " +" d.HEAT_NO, " +" d.BATCH_NO, " +" d.MATERIAL_NO, " +" d.PLINE_CODE_LIMS, " +" r.STEEL_CODE, " +" m.CERT_INST_CODE, " +" m.CERT_INST_NAME, " +" d.SMP_TYPE_CODE, " +" case " +" when d.SMP_TYPE_CODE = '0' then " +" case " +" when m.CERT_INST_CODE in " +" ('IC003', 'IC004', 'IC001', 'IC010', '5000') then " +" d.SMP_TYPE_NAME " +" else " +" '认证样' " +" end " +" else " +" d.SMP_TYPE_NAME " +" end SMP_TYPE_NAME, " +" d.STATUS, " +" d.FREQ_CODE, " +" d.FREQ_NAME, " +" m.SMP_CATG, " +" d.SMP_NO, " +" d.SPECIMEN_NO, " +" d.INSPECTION_LOT, " +" r.DESIGN_KEY, " +" to_char(r.THICK, 'fm990.099') THICK, " +" d.PROCESS_NOS, " +" d.SMP_LOCATION, " +" d.PLINE_CODE, " +" d.PLINE_NAME, " +" d.BOARD_NO, " +" r.DELIVERY_STATE_DESC, " +" r.PROD_NAME, " +" r.STD_NAME, " +" to_char(d.COLLECT_TIME, 'yyyy-mm-dd hh24:mi:ss') COLLECT_TIME, " +" d.ZHB_SPECIMEN_NO, " +" d.QUOTE_CONSIGN_NO, " +" d.SEND_NAME, " +" to_char(d.SEND_TIME, 'yyyy-mm-dd hh24:mi:ss') SEND_TIME, " +" to_char(d.CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss') CREATE_TIME " +" from qcm_jhy_sample_consign_d d, " +" qcm_jhy_sample_consign_m m, " +" qcm_jhy_sample_r_ord r " +" where d.smp_no = m.smp_no " +" and d.smp_no = r.smp_no " +" and m.smp_no = r.smp_no " +" and d.status in ('2', '3', '7') " +" and d.Quote_Consign_No is null " //+" and nvl(d.Send_Memo, 'null') <> '有引用' " +" and d.send_name not in ('QMS', 'sys', 'system', '引用', '引用发送', '系统自动') " +" and d.SAMPLE_DELIVERY_TIME is not null and d.PLINE_CODE_LIMS is not null " + sqlStr +" order by d.SAMPLE_DELIVERY_TIME asc, r.steel_code asc,d.MATERIAL_NO asc "; List listEle= mapper.query(sql.toString()); cro.setResult(listEle); }catch(Exception ex){ cro.setV_errCode(-1); cro.setV_errMsg("获取出错"+ex.getMessage()); SqlSession.rollback(); } SqlSession.close(); return cro; } //重置船检编号查询 public CoreReturnObject getHZBNumberQ(HashMap parmas) { try{ String sqlStr=""; /*if(!StringUtils.isBlank(parmas.get("SEND_TIME1")) && !StringUtils.isBlank(parmas.get("SEND_TIME2"))){ sqlStr +=" and t.CREATE_TIME>=to_date('"+parmas.get("SEND_TIME1")+" 00:00:00','yyyy-MM-dd HH24:mi:ss')"; sqlStr +=" and t.CREATE_TIME<=to_date('"+parmas.get("SEND_TIME2")+" 23:59:59','yyyy-MM-dd HH24:mi:ss') "; }*/ if(!StringUtils.isBlank(parmas.get("CERT_INST_CODE"))){ sqlStr += " and t.CERT_INST_CODE='"+parmas.get("CERT_INST_CODE").toString()+"' "; } if(parmas.get("YN").equals("1")){ sqlStr += " and t.YN_LOG='"+parmas.get("YN").toString()+"' "; } String sql ="select T.YN_LOG, " +" T.FH_LOG, " +" T.CERT_INST_CODE, " +" T.CERT_INST_NAME, " +" T.YYMM, " +" to_char(T.CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss') CREATE_TIME, " +" T.CREATE_NAME, " +" (SELECT NVL(MAX(d.PRINT_SEQJ),0) PRINT_SEQJ " +" FROM QCM_JHY_SAMPLE_CONSIGN_D d " +" WHERE d.PRINT_SEQJ like T.FH_LOG || '%' " +" AND d.PRINT_SEQJ IS NOT NULL " +" AND d.PRINT_SEQJLOT IS NULL) PRINT_SEQJ " +" from QCM_JHY_SHIP_INSPEC t " +" where 1 = 1 " + sqlStr +" order by T.YN_LOG desc "; List listEle= mapper.query(sql.toString()); cro.setResult(listEle); }catch(Exception ex){ cro.setV_errCode(-1); cro.setV_errMsg("获取出错"+ex.getMessage()); SqlSession.rollback(); } SqlSession.close(); return cro; } //螺纹钢牌号 public CoreReturnObject getLWGSTEEL(HashMap parmas) { try{ String sql =" select rownum as RNUM,t.YN_LOG, " +" t.STEEL_NAME, " +" t.CREATE_NAME, " +" to_char(t.CREATE_TIME,'yyyy-mm-dd hh24:mi:ss') CREATE_TIME, " +" t.DELETE_NAME, " +" to_char(t.DELETE_TIME,'yyyy-mm-dd hh24:mi:ss') DELETE_TIME " +" from QCM_JHY_STEEL t " +" where t.regulation = '1' "; List listEle= mapper.query(sql.toString()); cro.setResult(listEle); }catch(Exception ex){ cro.setV_errCode(-1); cro.setV_errMsg("获取出错"+ex.getMessage()); SqlSession.rollback(); } SqlSession.close(); return cro; } //自动查询螺纹钢牌号 public CoreReturnObject getBZSTEEL(HashMap parmas) { try{ String sql ="select STEEL_CODE,STEEL_NAME from QCM_ORD_DESIGN_STD_PIC_SMP t" + " where t.process_code in ('BC2','GX1','GX2','BC1')" + " and t.prod_code in ('040','041') group by STEEL_CODE,STEEL_NAME order by STEEL_CODE "; List listEle= mapper.query(sql.toString()); cro.setResult(listEle); }catch(Exception ex){ cro.setV_errCode(-1); cro.setV_errMsg("获取出错"+ex.getMessage()); SqlSession.rollback(); } SqlSession.close(); return cro; } /** * 中厚板合并委托 挪威船级社限制 冲击温度一样才可以合并引用 * 主题：关于DNV性能引用 技术中心李娇丽提出 申报时间2024-10-28 11:06:27 * 2024年挪威船级社年审提出，低等级不能引用高等级性能，因为低等级的冲击未进行试验，没有试验原始记录，挪威船级社DNV需要在冲击温度相同的情况下才能引用性能。 * @param parmas * @return */ public CoreReturnObject getZHBDNV(HashMap parmas) { try{ String BATCH_NO=parmas.get("BATCH_NO").toString(); //查询当前轧批号下是否有挪威 挪威下面是否有冲击项目 String sql =" select d.batch_no,r.cert_inst_name,r.DELIVERY_STATE_DESC,r.thick,i.* " +" from qcm_jhy_sample_consign_d d,qcm_jhy_sample_r_ord r,qcm_jhy_sample_consign_d_item i " +" where d.smp_no = r.smp_no and d.specimen_no = i.specimen_no and r.smp_no = i.smp_no and r.Cert_Inst_Code = 'IC001' " +" and i.PHY_CODE_L = 'HC' and d.status = '0' and d.batch_no = '"+BATCH_NO+"' "; List listEle= mapper.query(sql.toString()); if(listEle!=null && listEle.size()>=1){ String SPECIMEN_NO = listEle.get(0).get("SPECIMEN_NO").toString(); String THICK = listEle.get(0).get("THICK").toString(); String sql1=" select * from (select t.*,QCM_SPECFLAG(r.STEEL_CODE,r.THICK,r.std_code,m.CERT_INST_NAME) qcmSpecflag " +" from qcm_jhy_sample_consign_d t,qcm_jhy_sample_consign_m m,qcm_jhy_sample_r_ord r " +" where t.smp_no = m.smp_no and t.smp_no = r.smp_no and t.freq_code <> 'D' and t.smp_type_code = '0' " +" and t.status = '0' and r.std_code not like '%ASTM%' and r.std_code not like '%ASME%' " +" and r.Cert_Inst_Code in ('5000', 'IC010', 'IC003', 'IC004', 'IC001') and m.SMP_CATG = 'B' " +" and r.Delivery_State_Desc in ('TMCP','AR','CR') and r.design_key not like '19%' " +" and t.batch_no = '"+BATCH_NO+"' " +" and r.thick = '"+THICK+"' " +" and t.SPECIMEN_NO != '"+SPECIMEN_NO+"' " +"order by r.STEEL_CODE) t where t.qcmSpecflag in ('1', '0') "; //获取能合并的委托 List list= mapper.query(sql1.toString()); if(list!=null && list.size()>=1){ String sql2 = " select * from qcm_jhy_sample_consign_d_item i where i.PHY_CODE_L = 'HC' " +" and i.specimen_no ='"+SPECIMEN_NO+"' and i.seq <=49 "; //获取挪威冲击 List list2= mapper.query(sql2.toString()); //循环其它委托 for (int i = 0; i < list.size(); i++) { String sql3 = " select * from qcm_jhy_sample_consign_d_item i where i.PHY_CODE_L = 'HC' " +" and i.specimen_no ='"+list.get(i).get("SPECIMEN_NO")+"' and i.seq <=49 "; //获取其它委托的冲击 List list3= mapper.query(sql3.toString()); if(list3!=null && list3.size()>=1){ for (HashMap hashMap : list3) { String itemCodeT = (String) hashMap.get("ITEM_CODE_T");//试验温度代码 for (HashMap hashMap1 : list2) {//挪威 String itemCodeT1 = (String) hashMap1.get("ITEM_CODE_T");//试验温度代码 //判断冲击温度是否一样 if(!itemCodeT.equals(itemCodeT1)){ cro.setV_errCode(-1); cro.setV_errMsg(BATCH_NO+"轧批中有挪威认证机构,技术中心规定挪威委托项目中冲击温度不一样不能合并引用,请单独下发委托,谢谢!"); return cro; } } } } } } } }catch(Exception ex){ cro.setV_errCode(-1); cro.setV_errMsg("挪威冲击比较出错"+ex.getMessage()); SqlSession.rollback(); }finally { SqlSession.close(); } cro.setV_errCode(0); return cro; } }