/***文档注释*********************************************** * 作者 ：freedom * 创建日期 ：2022-02-24 * 描述 ：检验委托-查询材质检验委托 * 注意事项 ： * 遗留BUG ： * 修改日期 ： * 修改人员 ： * 修改内容 ： ***********************************************************/ package QCM.JHY01.JHY0101; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; 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.JSONException; import com.alibaba.fastjson.JSONObject; import CoreFS.SA01.CoreIComponent; import CoreFS.SA06.CoreReturnObject; import QCM.COMMUNAL.QCM02.SqlJoint; import QCM.JHY01.JHY0102.UpdateInfo; public class QuerryQltySample 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 parmas * @return */ public CoreReturnObject getQltySampleInfo(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; } } StringBuffer sqlucomm = new StringBuffer(); sqlucomm.append("select t1.SMP_NO,t1.SPECIMEN_NO,t1.HEAT_NO,t1.BATCH_NO,t1.INSPECTION_LOT,t1.FREQ_CODE,"); sqlucomm.append("t1.FREQ_NAME,t1.MATERIAL_NO,t1.SEND_MEMO,t1.SMP_TYPE_CODE, "); sqlucomm.append(" case when t1.SMP_TYPE_CODE = '0' then " +" case when t2.CERT_INST_CODE in ('IC003','IC004','IC001','IC010','5000') then " +" t1.SMP_TYPE_NAME " +" else '认证样' end " +" else t1.SMP_TYPE_NAME " +" end SMP_TYPE_NAME,"); sqlucomm.append("t1.CONSIGN_NO, t1.CONSIGN_NO_SEQ,t1.TEST_QTY,t1.SMP_QTY,t1.SMP_LOCATION, t1.BOARD_NO, t1.QUOTE_CONSIGN_NO,t1.OLD_CONSIGN_NO,"); sqlucomm.append("t1.PLINE_CODE,t1.PLINE_NAME,t1.STATUS,t1.VALIDFLAG,t1.SOURCE, "); sqlucomm.append("t1.SEND_NAME,to_char(t1.SEND_TIME,'yyyy-mm-dd hh24:mi:ss') SEND_TIME,"); sqlucomm.append("t1.SOURCE, t1.memo,t1.WEIGHT,t1.WEIGHT_STD,t1.WEIGHT_SFD,t2.SMP_TYPE_CODE as SMP_TYPE_CODE1,t2.SMP_TYPE_NAME as SMP_TYPE_NAME1,"); sqlucomm.append("t2.CERT_INST_CODE,t2.CREATE_NAME,to_char(t1.CREATE_TIME,'yyyy-mm-dd hh24:mi:ss') CREATE_TIME,t2.smp_catg as smp_catg, t2.CERT_INST_NAME,"); sqlucomm.append("t2.BOARD_NO, t2.MATERIAL_NO,t2.psc,t2.psc_desc, " +" case when t1.phy_item is not null then t1.phy_item else dbms_lob.substr((select wmsys.wm_concat(DISTINCT PHY_NAME_S) " +" from QCM_JHY_SAMPLE_CONSIGN_D_ITEM WHERE SPECIMEN_NO = t1.SPECIMEN_NO and fy_quote_specimen_no is null)) end PHY_NAME_L_BJ, " ); sqlucomm.append(" ord.DESIGN_KEY,to_char(ord.THICK, 'fm990.099') THICK,ord.WIDTH,"); if(parmas.get("PLINE_NAME").equals("厚板线")){ sqlucomm.append(" (select length from kch_turnofflist@xgcx where BILLETID = t1.MATERIAL_NO and rownum <= 1) LENGTH,"); sqlucomm.append(" (select max(INSTORAGETIME) INTIME from kch_turnofflist@xgcx where BILLETID = t1.MATERIAL_NO and rownum <= 1) INTIME,"); }else if(parmas.get("PLINE_NAME").equals("中板线")){ sqlucomm.append(" (select length from kcz_turnofflist@xgcx where BILLETID = t1.MATERIAL_NO and rownum <= 1) LENGTH,"); sqlucomm.append(" (select max(INTIME) from kcz_turnofflist@xgcx where BILLETID = t1.MATERIAL_NO and rownum <= 1) INTIME,"); }else if (parmas.get("PLINE_CODE").equals("LT")){ sqlucomm.append(" t1.PRINT_LOG,t1.SEND_SEQ,t1.PRINT_SEQ, "); }else{ sqlucomm.append(" ord.LENGTH,"); } sqlucomm.append(" ord.DELIVERY_STATE_CODE,ord.DELIVERY_STATE_DESC,ord.PROD_NAME,ord.STEEL_NAME,ord.STD_NAME,t1.ITEM_FLAG "); sqlucomm.append(" from QCM_JHY_SAMPLE_CONSIGN_D t1 left join QCM_JHY_SAMPLE_CONSIGN_M t2 on t1.SMP_NO=t2.SMP_NO"); sqlucomm.append(" inner join QCM_JHY_SAMPLE_R_ORD ord on t1.smp_no = ord.smp_no "); sqlucomm.append(" where 1=1"); 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()) ) { sqlucomm.append(" and upper(t1.batch_no) >= upper('"+parmas.get("BATCH_NO")+"')"); sqlucomm.append(" and upper(t1.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")){ sqlucomm.append(" and t1.batch_no like '"+parmas.get("BATCH_NO")+"%' "); } if (parmas.get("CERT_INST_NAME")!=null && !"".equals(((String)parmas.get("CERT_INST_NAME")).trim())) { sqlucomm.append(" and t2.cert_inst_name like '"+parmas.get("CERT_INST_NAME")+"%'"); } if (parmas.get("STEEL_NAME")!=null && !"".equals(((String)parmas.get("STEEL_NAME")).trim())) { sqlucomm.append(" and ord.steel_name like '"+parmas.get("STEEL_NAME")+"%'"); } if (parmas.get("SMP_CATG")!=null && !"".equals(((String)parmas.get("SMP_CATG")).trim())) { sqlucomm.append(" and t2.smp_catg = '"+parmas.get("SMP_CATG")+"'"); } if (parmas.get("VALIDFLAG")!=null && !"".equals(((String)parmas.get("VALIDFLAG")).trim())) { sqlucomm.append(" and t1.validflag='"+parmas.get("VALIDFLAG")+"'"); } if (parmas.get("STATUS")!=null && !"".equals(((String)parmas.get("STATUS")).trim())) { sqlucomm.append(" and t1.STATUS='"+parmas.get("STATUS")+"' and t1.STATUS <> '6' "); }else{ sqlucomm.append(" and t1.STATUS <> '0' and t1.STATUS <> '5' and t1.STATUS <> '6' "); } if(parmas.get("DESIGN_KEY")!=null && !"".equals(((String)parmas.get("DESIGN_KEY")).trim())){ sqlucomm.append(" and ord.DESIGN_KEY like '"+parmas.get("DESIGN_KEY")+"%'"); } if(parmas.get("TAB_NAME")!=null && !"".equals(((String)parmas.get("TAB_NAME")).trim())){ //发送时间 if (parmas.get("STARTTIME")!=null && !"".equals(((String)parmas.get("STARTTIME")).trim())) { sqlucomm.append(" and t1.send_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())) { sqlucomm.append(" and t1.send_time<=to_date(concat('"+parmas.get("ENDTIME")+"',' 23:59:59'),'yyyy-MM-dd HH24:mi:ss') "); } }else{ //创建时间 if (parmas.get("STARTTIME")!=null && !"".equals(((String)parmas.get("STARTTIME")).trim())) { sqlucomm.append(" and t1.CREATE_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())) { sqlucomm.append(" and t1.CREATE_TIME<=to_date(concat('"+parmas.get("ENDTIME")+"',' 23:59:59'),'yyyy-MM-dd HH24:mi:ss') "); } } if(parmas.get("PLINE_NAME").equals("厚板线")){ sqlucomm.append(" and t1.BATCH_NO like 'H%' "); }else if(parmas.get("PLINE_NAME").equals("中板线")){ sqlucomm.append(" and t1.BATCH_NO like 'Z%' "); } if (parmas.get("PLINE_NAME")!=null && !"".equals(((String)parmas.get("PLINE_NAME")).trim())) { if(parmas.get("PLINE_NAME").equals("高线")){ sqlucomm.append(" and t2.PLINE_NAME in ('高线','二线厂')"); }else{ sqlucomm.append(" and t2.PLINE_NAME like '"+parmas.get("PLINE_NAME")+"%'"); } } if (parmas.get("PLINE_CODE")!=null && !"".equals(((String)parmas.get("PLINE_CODE")).trim())) { //sqlucomm.append(" and t2.PLINE_CODE = '"+parmas.get("PLINE_CODE")+"'"); } if (parmas.get("HEAT_NO")!=null && !"".equals(((String)parmas.get("HEAT_NO")).trim())) { sqlucomm.append(" and t1.HEAT_NO like '"+parmas.get("HEAT_NO")+"%'"); } if(parmas.get("TAB_NAME")!=null && !"".equals(((String)parmas.get("TAB_NAME")).trim())){ sqlucomm.append(" order by t1.send_time desc,t1.BATCH_NO desc"); }else{ sqlucomm.append(" order by t1.CREATE_TIME desc,t1.BATCH_NO desc "); } List listEle= mapper.query(sqlucomm.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 getQltySampleInfoZHB(HashMap parmas) { try{//t1.PROCESS_NO, 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 sqlzh = ""; String timezh = ""; String taskno = ""; if(parmas.get("PLINE_NAME").equals("厚板线")){ // timezh+="(select length from kch_turnofflist@xgcx where BILLETID = t1.MATERIAL_NO and rownum <= 1) LENGTH,"; // timezh+= "(select max(INSTORAGETIME) INTIME from kch_turnofflist@xgcx where BILLETID = t1.MATERIAL_NO and rownum <= 1) INTIME,"; sqlzh = " left join kch_turnofflist@xgcx st on t1.MATERIAL_NO = st.BILLETID "; timezh = "to_char(st.INSTORAGETIME, 'yyyy-mm-dd hh24:mi:ss') INTIME, st.RCL_STATE,";// taskno = "(select TASKNO from zyhb_plandetailofplate@xgcx t where t.SLABNO = t1.material_no and rownum=1) TASKNO,"; }else if(parmas.get("PLINE_NAME").equals("中板线")){ //timezh +="(select length from kcz_turnofflist@xgcx where BILLETID = t1.MATERIAL_NO and rownum <= 1) LENGTH,"; //timezh += "(select max(INTIME) from kcz_turnofflist@xgcx where BILLETID = t1.MATERIAL_NO and rownum <= 1) INTIME,"; sqlzh = " left join kcz_turnofflist@xgcx st on t1.MATERIAL_NO = st.BILLETID "; timezh = "to_char(st.INTIME, 'yyyy-mm-dd hh24:mi:ss') INTIME, st.RCL_STATE,";// taskno = "(select TASKNO from mb_plandetailofplate@xgcx t where t.productno = t1.material_no and rownum=1) TASKNO,"; } String sql = "select t1.SMP_NO, " +" t1.SPECIMEN_NO, " +" t1.HEAT_NO, " +" t1.BATCH_NO, " +" t1.INSPECTION_LOT, " +" t1.FREQ_CODE, " +" t1.FREQ_NAME, " +" t1.MATERIAL_NO, " +" t1.SEND_MEMO, " +" t1.SMP_TYPE_CODE, " +" case " +" when t1.SMP_TYPE_CODE = '0' then " +" case " +" when t2.CERT_INST_CODE in " +" ('IC003', 'IC004', 'IC001', 'IC010', '5000') then " +" t1.SMP_TYPE_NAME " +" else " +" '认证样' " +" end " +" else " +" t1.SMP_TYPE_NAME " +" end SMP_TYPE_NAME, " +" t1.CONSIGN_NO, " +" t1.CONSIGN_NO_SEQ, " +" t1.TEST_QTY, " +" t1.SMP_QTY, " +" t1.SMP_LOCATION, " +" t1.BOARD_NO, " +" t1.QUOTE_CONSIGN_NO, " +" t1.OLD_CONSIGN_NO, " +" t1.PLINE_CODE, " +" t1.PLINE_NAME, " +" t1.STATUS, " //+" t1.VALIDFLAG, " //+" t1.SOURCE, " +" t1.SEND_NAME, " +" to_char(t1.SEND_TIME, 'yyyy-mm-dd hh24:mi:ss') SEND_TIME, " //+" t1.SOURCE, " +" t1.memo, " +" t2.SMP_TYPE_CODE as SMP_TYPE_CODE1, " +" t2.SMP_TYPE_NAME as SMP_TYPE_NAME1, " +" t2.CERT_INST_CODE, " +" t2.CREATE_NAME, " +" to_char(t1.CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss') CREATE_TIME, " +" t2.smp_catg as smp_catg, " +" t2.CERT_INST_NAME, " //+" t2.BOARD_NO, " //+" t2.MATERIAL_NO, " +" t2.psc, " +" t2.psc_desc, " +" case when t1.phy_item is not null then " +" t1.phy_item else " +" dbms_lob.substr((select wmsys.wm_concat(DISTINCT PHY_NAME_S) " +" from QCM_JHY_SAMPLE_CONSIGN_D_ITEM " +" WHERE SPECIMEN_NO = t1.SPECIMEN_NO " +" and fy_quote_specimen_no is null)) " +" end PHY_NAME_L_BJ, " +" ord.DESIGN_KEY, " +" to_char(ord.THICK, 'fm990.099') THICK, " +" ord.WIDTH, " +" nvl(st.length,ord.length) length , " +timezh +taskno +" t1.PROCESS_NO, ord.DELIVERY_STATE_CODE, " +" ord.DELIVERY_STATE_DESC, " +" ord.PROD_NAME, " +" ord.STEEL_NAME, " +" ord.STD_NAME, " +" t1.ITEM_FLAG,t1.IMPROVE_MEMO,t1.PROCESS_NOS," + " to_char(t1.COLLECT_TIME, 'yyyy-mm-dd hh24:mi:ss') COLLECT_TIME," + " t1.ZHB_SPECIMEN_NO,t1.QUOTE_MEMO,t1.SAMPLE_DELIVERY_TIME," + " t1.ONEMEMO,t1.WEIGHT,t1.RZ_MIXROLL,t1.RZ_OLD_SAMPL_NO,t1.RZ_ROLL_SLAB_SEQ,t1.SPECIAL_QUOTE_MEMO," + " to_char(t1.STOVE_TIME, 'yyyy-mm-dd hh24:mi:ss') STOVE_TIME," + " decode(t1.CHEM_INTERNALCONTROL,'1','符合','0','不符合') CHEM_INTERNALCONTROL, " + " decode(t1.CRAFT_EXECUTE,'1','符合','0','不符合') CRAFT_EXECUTE, " + " decode(t1.FUNCTION_FUHE,'1','合格','0','不合格') FUNCTION_FUHE," + " decode(t1.SHOULD_SAMPLE,'1','N','0','Y','2','Y(抽样)') SHOULD_SAMPLE," + " t1.PRINT_LOG,t1.PRINT_SEQ,t1.PRINT_SEQJ,t1.PLINE_CODE_LIMS " +" from QCM_JHY_SAMPLE_CONSIGN_D t1 " +" left join QCM_JHY_SAMPLE_CONSIGN_M t2 " +" on t1.SMP_NO = t2.SMP_NO " +" left join QCM_JHY_SAMPLE_R_ORD ord " +" on t1.smp_no = ord.smp_no "; sql += sqlzh +" where 1 = 1 "; 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()) ) { sql +=" and upper(t1.batch_no) >= upper('"+parmas.get("BATCH_NO")+"')"; sql +=" and upper(t1.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")){ sql +=" and t1.batch_no like '"+parmas.get("BATCH_NO")+"%' "; } if (parmas.get("CERT_INST_NAME")!=null && !"".equals(((String)parmas.get("CERT_INST_NAME")).trim())) { sql +=" and t2.cert_inst_name like '"+parmas.get("CERT_INST_NAME")+"%'"; } if (parmas.get("STEEL_NAME")!=null && !"".equals(((String)parmas.get("STEEL_NAME")).trim())) { sql +=" and ord.steel_name like '"+parmas.get("STEEL_NAME")+"%'"; } if (parmas.get("SMP_CATG")!=null && !"".equals(((String)parmas.get("SMP_CATG")).trim())) { sql +=" and t2.smp_catg = '"+parmas.get("SMP_CATG")+"'"; } if (parmas.get("VALIDFLAG")!=null && !"".equals(((String)parmas.get("VALIDFLAG")).trim())) { sql +=" and t1.validflag='"+parmas.get("VALIDFLAG")+"'"; } if (parmas.get("STATUS")!=null && !"".equals(((String)parmas.get("STATUS")).trim())) { sql +=" and t1.STATUS='"+parmas.get("STATUS")+"' and t1.STATUS <> '6' "; }else{ sql +=" and t1.STATUS not in ('0','5','6') "; } if(parmas.get("DESIGN_KEY")!=null && !"".equals(((String)parmas.get("DESIGN_KEY")).trim())){ sql +=" and ord.DESIGN_KEY like '"+parmas.get("DESIGN_KEY")+"%'"; } if(parmas.get("TAB_NAME")!=null && !"".equals(((String)parmas.get("TAB_NAME")).trim())){ //发送时间 if (parmas.get("STARTTIME")!=null && !"".equals(((String)parmas.get("STARTTIME")).trim())) { sql +=" and t1.send_time>=to_date('"+parmas.get("STARTTIME")+" 00:00:00','yyyy-MM-dd HH24:mi:ss')"; } if (parmas.get("ENDTIME")!=null && !"".equals(((String)parmas.get("ENDTIME")).trim())) { sql +=" and t1.send_time<=to_date('"+parmas.get("ENDTIME")+" 23:59:59','yyyy-MM-dd HH24:mi:ss') "; } }else{ //创建时间 if (parmas.get("STARTTIME")!=null && !"".equals(((String)parmas.get("STARTTIME")).trim())) { sql +=" and t1.CREATE_TIME>=to_date('"+parmas.get("STARTTIME")+" 00:00:00','yyyy-MM-dd HH24:mi:ss')"; } if (parmas.get("ENDTIME")!=null && !"".equals(((String)parmas.get("ENDTIME")).trim())) { sql +=" and t1.CREATE_TIME<=to_date('"+parmas.get("ENDTIME")+" 23:59:59','yyyy-MM-dd HH24:mi:ss') "; } } if(parmas.get("PLINE_NAME").equals("厚板线")){ sql +=" and t1.BATCH_NO like 'H%' and t1.PLINE_CODE = 'HB1' "; }else if(parmas.get("PLINE_NAME").equals("中板线")){ sql +=" and t1.BATCH_NO like 'Z%' and t1.PLINE_CODE = 'ZB1' "; } if (parmas.get("HEAT_NO")!=null && !"".equals(((String)parmas.get("HEAT_NO")).trim())) { sql +=" and t1.HEAT_NO like '"+parmas.get("HEAT_NO")+"%'"; } if (parmas.get("SAMPLE_DELIVERY_TIME")!=null && !"".equals(((String)parmas.get("SAMPLE_DELIVERY_TIME")).trim())) { sql +=" and t1.SAMPLE_DELIVERY_TIME = '"+parmas.get("SAMPLE_DELIVERY_TIME")+"'"; } if (parmas.get("RZ_MIXROLL")!=null && !"".equals(((String)parmas.get("RZ_MIXROLL")).trim())) { sql +=" and t1.RZ_MIXROLL = '"+parmas.get("RZ_MIXROLL")+"'"; } if(parmas.get("CHECKBOKTXTL").equals("T")){ sql+=" and t1.ZHB_SPECIMEN_NO is not null "; } if(parmas.get("TAB_NAME")!=null && !"".equals(((String)parmas.get("TAB_NAME")).trim())){ sql +=" order by t1.send_time desc,t1.BATCH_NO desc"; }else{ sql +=" order by t1.CREATE_TIME desc,t1.BATCH_NO 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; } /** * 查询材质试样信息(热轧) * @param parmas * @return */ public CoreReturnObject getQltySampleInfoRZ(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 sql = "select case when T3.SMP_DIR_PIC_YN = 'N' then '' else QCM_RZ_DATE(SUBSTR(T3.OLD_SAMPL_NO, 1, 10),t3.OLD_SAMPL_NO,t1.specimen_no) end SAMPLE_DATE, " +" case when T3.SMP_DIR_PIC_YN = 'N' then '' else QCM_RZ_TIME(t1.batch_no,t3.OLD_SAMPL_NO,t1.specimen_no) end JHQ_DATE, " +" t3.OLD_SAMPL_NO, " +" t4.ROLL_MANA_NO, " +" t4.ROLL_SLAB_SEQ, " +" t4.MIXROLL, " +" t1.SMP_NO, " +" t1.SPECIMEN_NO, " //+" t1.HEAT_NO, " //+" t1.BATCH_NO, " +" SUBSTR(T3.SLAB_NO, 1, 10) HEAT_NO," +" SUBSTR(T3.OLD_SAMPL_NO, 1, 10) BATCH_NO," +" t1.INSPECTION_LOT, " +" t1.FREQ_CODE, " +" t1.FREQ_NAME, " +" t1.MATERIAL_NO, " +" t1.SEND_MEMO, " +" t1.SMP_TYPE_CODE, " //+" t1.SMP_TYPE_NAME, " +" case when t1.SMP_TYPE_CODE = '0' then " +" case when t2.CERT_INST_CODE in ('IC003','IC004','IC001','IC010','5000') then " +" t1.SMP_TYPE_NAME " +" else '认证样' end " +" else t1.SMP_TYPE_NAME " +" end SMP_TYPE_NAME," +" t1.CONSIGN_NO, " +" t1.CONSIGN_NO_SEQ, " +" t1.TEST_QTY, " +" t1.SMP_QTY, " +" t1.SMP_LOCATION, " +" t1.BOARD_NO, " +" t1.QUOTE_CONSIGN_NO, " +" t1.OLD_CONSIGN_NO, " +" t1.PLINE_CODE, " +" t1.PLINE_NAME, " +" t1.STATUS, " +" t1.VALIDFLAG, " +" t1.SOURCE, " +" decode(t1.PLINE_CODE, 'ZB1', '认证样', 'HB1', '船级社留样') 类型, " +" t1.SEND_NAME, " +" to_char(t1.SEND_TIME, 'yyyy-mm-dd hh24:mi:ss') SEND_TIME, " +" t1.SOURCE, " +" t1.memo, " +" t1.WEIGHT, " +" t1.WEIGHT_STD, " +" t1.WEIGHT_SFD, " +" t2.SMP_TYPE_CODE as SMP_TYPE_CODE1, " +" t2.SMP_TYPE_NAME as SMP_TYPE_NAME1, " +" t2.CERT_INST_CODE, " +" t2.CREATE_NAME, " +" to_char(t1.CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss') CREATE_TIME, " +" t2.smp_catg as smp_catg, " +" t2.CERT_INST_NAME, " +" t2.BOARD_NO, " +" t2.MATERIAL_NO, " +" t2.psc, " +" t2.psc_desc, " +" case when t1.phy_item is not null then " +" t1.phy_item else " +" dbms_lob.substr((select wmsys.wm_concat(DISTINCT PHY_NAME_S) " +" from QCM_JHY_SAMPLE_CONSIGN_D_ITEM " +" WHERE SPECIMEN_NO = t1.SPECIMEN_NO " +" and fy_quote_specimen_no is null)) " +" end PHY_NAME_L_BJ, " +" ord.DESIGN_KEY, " //+" ord.WIDTH, " // to_char(ord.thick, 'fm990.099') +" to_char(nvl(ord.thick,t5.ORD_THK), 'fm990.099') THICK," +" t5.ORD_WTH WIDTH," +" ord.LENGTH, " +" ord.DELIVERY_STATE_CODE, " +" ord.DELIVERY_STATE_DESC, " +" ord.PROD_NAME, " +" ord.STEEL_NAME, " +" ord.STD_NAME, " +" t1.ITEM_FLAG,t1.RZ_OLD_SAMPL_NO RZ_ENTRUST_NUMBER," + " DECODE(t3.SPEC_STL_GRD,'Q235B',GETMAXMIXROLL(t4.ROLL_MANA_NO, t4.MIXROLL, t4.COIL_NO),'Q355B', " + " GETMAXMIXROLL(t4.ROLL_MANA_NO, t4.MIXROLL, t4.COIL_NO),'0') MIXFLAG," + " T3.SMP_DIR_PIC_YN,t1.SAMPL_PICK_RY,t1.PRINT_LOG,t1.SEND_SEQ,t1.PRINT_SEQ,t1.QRZ_SPECIMEN_NO " +" from tbh02_coil_comm t3, " +" tbf02_spec_mill t4, " +" TBE02_ORD_PRC t5, " +" QCM_JHY_SAMPLE_CONSIGN_D t1, " +" QCM_JHY_SAMPLE_CONSIGN_M t2, " +" QCM_JHY_SAMPLE_R_ORD ord " +" where t3.SAMPL_NO = t1.inspection_lot(+) " +" and t3.COIL_NO = t4.COIL_NO(+) " +" AND t3.ORD_NO = t5.ORD_NO(+) " +" AND t3.ORD_SEQ = t5.ORD_SEQ(+) " +" and t1.SMP_NO = t2.SMP_NO(+) " +" and t1.smp_no = ord.smp_no(+) and T3.OLD_SAMPL_NO is not null "; 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()) ) { sql +=" and upper(t1.batch_no) >= upper('"+parmas.get("BATCH_NO")+"')"; sql +=" and upper(t1.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")){ sql +=" and t3.OLD_SAMPL_NO like '"+parmas.get("BATCH_NO")+"%' "; } if (parmas.get("CERT_INST_NAME")!=null && !"".equals(((String)parmas.get("CERT_INST_NAME")).trim())) { sql +=" and t2.cert_inst_name like '"+parmas.get("CERT_INST_NAME")+"%'"; } if (parmas.get("STEEL_NAME")!=null && !"".equals(((String)parmas.get("STEEL_NAME")).trim())) { sql +=" and ord.steel_name like '"+parmas.get("STEEL_NAME")+"%'"; } /* if (parmas.get("SMP_CATG")!=null && !"".equals(((String)parmas.get("SMP_CATG")).trim())) { //sql +=" and t2.smp_catg = '"+parmas.get("SMP_CATG")+"'"; } if (parmas.get("VALIDFLAG")!=null && !"".equals(((String)parmas.get("VALIDFLAG")).trim())) { //sql +=" and t1.validflag='"+parmas.get("VALIDFLAG")+"'"; } if (parmas.get("STATUS")!=null && !"".equals(((String)parmas.get("STATUS")).trim())) { //sql +=" and (t1.STATUS is null or t1.STATUS = '0')"; }else{ //sql +=" and t1.STATUS <> '0' "; }*/ sql +=" and nvl(t1.STATUS ,'null') <> '5' and nvl(t1.STATUS ,'null') <> '6'";//2022 09 28 add by WL if(parmas.get("TAB_NAME")!=null && !"".equals(((String)parmas.get("TAB_NAME")).trim())){ //发送时间 if (parmas.get("STARTTIME")!=null && !"".equals(((String)parmas.get("STARTTIME")).trim())) { sql +=" and t1.send_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())) { sql +=" and t1.send_time<=to_date(concat('"+parmas.get("ENDTIME")+"',' 23:59:59'),'yyyy-MM-dd HH24:mi:ss') "; } }else{ //创建时间 if (parmas.get("STARTTIME")!=null && !"".equals(((String)parmas.get("STARTTIME")).trim())) { sql +=" and t3.COIL_END_DTIME between '"+parmas.get("STARTTIME")+"000000' and '"+parmas.get("ENDTIME")+"999999'"; } } //轧辊单元 if (parmas.get("ROLL_MANA_NO")!=null && !"".equals(((String)parmas.get("ROLL_MANA_NO")).trim())) { sql +=" and t4.ROLL_MANA_NO = '"+parmas.get("ROLL_MANA_NO")+"'"; } //sql +=" and t3.COIL_END_DTIME > '20220915000000' "; if (parmas.get("HEAT_NO")!=null && !"".equals(((String)parmas.get("HEAT_NO")).trim())) { sql +=" and t1.HEAT_NO like '"+parmas.get("HEAT_NO")+"%'"; } sql +=" and nvl(T3.MISSNO_CLF_CD,'null') !='C' ";//C废品，S再回加热炉 if(parmas.get("TAB_NAME")!=null && !"".equals(((String)parmas.get("TAB_NAME")).trim())){ sql +=" order by t1.send_time ,t4.ROLL_MANA_NO, t4.ROLL_SLAB_SEQ asc"; }else{ sql +=" order by t4.ROLL_MANA_NO, t4.ROLL_SLAB_SEQ 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; } /** * 查询材质试样信息(热轧记录) * @param parmas * @return */ public CoreReturnObject getQltySampleInfoRZ2(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; } } StringBuffer sqlucomm = new StringBuffer(); sqlucomm.append("select t1.RZ_ROLL_MANA_NO ROLL_MANA_NO,t1.RZ_MIXROLL MIXROLL,t1.material_no OLD_SAMPL_NO,t1.RZ_ROLL_SLAB_SEQ ROLL_SLAB_SEQ," + "t1.RZ_OLD_SAMPL_NO RZ_ENTRUST_NUMBER,t1.SMP_NO,t1.SPECIMEN_NO,t1.HEAT_NO,t1.BATCH_NO,t1.INSPECTION_LOT,t1.FREQ_CODE,"); sqlucomm.append("t1.FREQ_NAME,t1.MATERIAL_NO,t1.SEND_MEMO,t1.SMP_TYPE_CODE, "); sqlucomm.append(" case when t1.SMP_TYPE_CODE = '0' then " +" case when t2.CERT_INST_CODE in ('IC003','IC004','IC001','IC010','5000') then " +" t1.SMP_TYPE_NAME " +" else '认证样' end " +" else t1.SMP_TYPE_NAME " +" end SMP_TYPE_NAME,"); sqlucomm.append("t1.CONSIGN_NO, t1.CONSIGN_NO_SEQ,t1.TEST_QTY,t1.SMP_QTY,t1.SMP_LOCATION, t1.BOARD_NO, t1.QUOTE_CONSIGN_NO,t1.OLD_CONSIGN_NO,"); sqlucomm.append("t1.PLINE_CODE,t1.PLINE_NAME,t1.STATUS,t1.VALIDFLAG,t1.SOURCE,t1.PRINT_LOG,t1.SEND_SEQ,t1.PRINT_SEQ,"); sqlucomm.append("t1.SEND_NAME,to_char(t1.SEND_TIME,'yyyy-mm-dd hh24:mi:ss') SEND_TIME,"); sqlucomm.append("t1.SOURCE, t1.memo,t1.WEIGHT,t1.WEIGHT_STD,t1.WEIGHT_SFD,t2.SMP_TYPE_CODE as SMP_TYPE_CODE1,t2.SMP_TYPE_NAME as SMP_TYPE_NAME1,"); sqlucomm.append("t2.CERT_INST_CODE,t2.CREATE_NAME,to_char(t1.CREATE_TIME,'yyyy-mm-dd hh24:mi:ss') CREATE_TIME,t2.smp_catg as smp_catg, t2.CERT_INST_NAME,"); sqlucomm.append("t2.BOARD_NO, t2.MATERIAL_NO,t2.psc,t2.psc_desc, "); sqlucomm.append(" ord.DESIGN_KEY,to_char(ord.THICK, 'fm990.099') THICK,ord.WIDTH,ord.LENGTH,"); sqlucomm.append(" ord.DELIVERY_STATE_CODE,ord.DELIVERY_STATE_DESC,ord.PROD_NAME,ord.STEEL_NAME,ord.STD_NAME,t1.ITEM_FLAG,t1.Sampl_Pick_Ry, " + " case when t1.phy_item is not null then t1.phy_item else dbms_lob.substr((select wmsys.wm_concat(DISTINCT PHY_NAME_S) " + " from QCM_JHY_SAMPLE_CONSIGN_D_ITEM WHERE SPECIMEN_NO = t1.SPECIMEN_NO and fy_quote_specimen_no is null)) end PHY_NAME_L_BJ "); sqlucomm.append(" from QCM_JHY_SAMPLE_CONSIGN_D t1 left join QCM_JHY_SAMPLE_CONSIGN_M t2 on t1.SMP_NO=t2.SMP_NO"); sqlucomm.append(" inner join QCM_JHY_SAMPLE_R_ORD ord on t1.smp_no = ord.smp_no "); sqlucomm.append(" where 1=1"); 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()) ) { sqlucomm.append(" and upper(t1.batch_no) >= upper('"+parmas.get("BATCH_NO")+"')"); sqlucomm.append(" and upper(t1.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")){ sqlucomm.append(" and t1.batch_no like '"+parmas.get("BATCH_NO")+"%' "); } if (parmas.get("CERT_INST_NAME")!=null && !"".equals(((String)parmas.get("CERT_INST_NAME")).trim())) { sqlucomm.append(" and t2.cert_inst_name like '"+parmas.get("CERT_INST_NAME")+"%'"); } if (parmas.get("STEEL_NAME")!=null && !"".equals(((String)parmas.get("STEEL_NAME")).trim())) { sqlucomm.append(" and ord.steel_name like '"+parmas.get("STEEL_NAME")+"%'"); } if (parmas.get("SMP_CATG")!=null && !"".equals(((String)parmas.get("SMP_CATG")).trim())) { sqlucomm.append(" and t2.smp_catg = '"+parmas.get("SMP_CATG")+"'"); } if (parmas.get("VALIDFLAG")!=null && !"".equals(((String)parmas.get("VALIDFLAG")).trim())) { sqlucomm.append(" and t1.validflag='"+parmas.get("VALIDFLAG")+"'"); } if (parmas.get("STATUS")!=null && !"".equals(((String)parmas.get("STATUS")).trim())) { sqlucomm.append(" and t1.STATUS='"+parmas.get("STATUS")+"' and t1.STATUS <> '6' "); }else{ sqlucomm.append(" and t1.STATUS <> '0' and t1.STATUS <> '5' and t1.STATUS <> '6' "); } if(parmas.get("DESIGN_KEY")!=null && !"".equals(((String)parmas.get("DESIGN_KEY")).trim())){ sqlucomm.append(" and ord.DESIGN_KEY like '"+parmas.get("DESIGN_KEY")+"%'"); } if(parmas.get("TAB_NAME")!=null && !"".equals(((String)parmas.get("TAB_NAME")).trim())){ //发送时间 if (parmas.get("STARTTIME")!=null && !"".equals(((String)parmas.get("STARTTIME")).trim())) { sqlucomm.append(" and t1.send_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())) { sqlucomm.append(" and t1.send_time<=to_date(concat('"+parmas.get("ENDTIME")+"',' 23:59:59'),'yyyy-MM-dd HH24:mi:ss') "); } }else{ //创建时间 if (parmas.get("STARTTIME")!=null && !"".equals(((String)parmas.get("STARTTIME")).trim())) { sqlucomm.append(" and t1.CREATE_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())) { sqlucomm.append(" and t1.CREATE_TIME<=to_date(concat('"+parmas.get("ENDTIME")+"',' 23:59:59'),'yyyy-MM-dd HH24:mi:ss') "); } } if (parmas.get("PLINE_NAME")!=null && !"".equals(((String)parmas.get("PLINE_NAME")).trim())) { sqlucomm.append(" and t2.PLINE_NAME like '"+parmas.get("PLINE_NAME")+"%'"); } if (parmas.get("PLINE_CODE")!=null && !"".equals(((String)parmas.get("PLINE_CODE")).trim())) { //sqlucomm.append(" and t2.PLINE_CODE = '"+parmas.get("PLINE_CODE")+"'"); } if (parmas.get("HEAT_NO")!=null && !"".equals(((String)parmas.get("HEAT_NO")).trim())) { sqlucomm.append(" and t1.HEAT_NO like '"+parmas.get("HEAT_NO")+"%'"); } //轧辊单元 if (parmas.get("ROLL_MANA_NO")!=null && !"".equals(((String)parmas.get("ROLL_MANA_NO")).trim())) { sqlucomm.append(" and t1.RZ_ROLL_MANA_NO = '"+parmas.get("ROLL_MANA_NO")+"'"); } if(parmas.get("TAB_NAME")!=null && !"".equals(((String)parmas.get("TAB_NAME")).trim())){ sqlucomm.append(" order by t1.send_time desc,t1.BATCH_NO desc"); }else{ sqlucomm.append(" order by t1.CREATE_TIME desc,t1.BATCH_NO desc "); } List listEle= mapper.query(sqlucomm.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 getQCMRZYZ(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 sql = "select (select to_char(TO_DATE(t.ORD_DEVLMT_DATE,'YYYY-MM-DD HH24:MI:SS'), 'yyyy-mm-dd') from tba01_ord_line t,tbh02_coil_comm c " +" where t.ORD_NO = c.ORD_NO and t.ORD_SEQ = c.ORD_SEQ and c.OLD_SAMPL_NO = t3.OLD_SAMPL_NO ) ORD_DEVLMT_DATE, " +" QCM_RZ_STATUS(t1.batch_no,t3.OLD_SAMPL_NO,t1.specimen_no) JHQ_STATUS, " +" t3.OLD_SAMPL_NO, " +" t4.ROLL_MANA_NO, " +" t4.ROLL_SLAB_SEQ, " +" t4.MIXROLL, " +" t1.SMP_NO, " +" t1.SPECIMEN_NO, " +" SUBSTR(T3.SLAB_NO, 1, 10) HEAT_NO," +" SUBSTR(T3.OLD_SAMPL_NO, 1, 10) BATCH_NO," +" t1.INSPECTION_LOT, " +" t1.FREQ_CODE, " +" t1.FREQ_NAME, " +" t1.MATERIAL_NO, " +" t1.SEND_MEMO, " +" t1.SMP_TYPE_CODE, " +" t1.SMP_TYPE_NAME, " +" t1.CONSIGN_NO, " +" t1.CONSIGN_NO_SEQ, " +" t1.TEST_QTY, " +" t1.SMP_QTY, " +" t1.SMP_LOCATION, " +" t1.BOARD_NO, " +" t1.QUOTE_CONSIGN_NO, " +" t1.OLD_CONSIGN_NO, " +" t1.PLINE_CODE, " +" t1.PLINE_NAME, " +" t1.STATUS, " +" t1.VALIDFLAG, " +" t1.SOURCE, " +" t1.SEND_NAME, " +" to_char(t1.SEND_TIME, 'yyyy-mm-dd hh24:mi:ss') SEND_TIME, " +" t1.SOURCE, " +" t1.memo, " +" t1.WEIGHT, " +" t1.WEIGHT_STD, " +" t1.WEIGHT_SFD, " +" t2.SMP_TYPE_CODE as SMP_TYPE_CODE1, " +" t2.SMP_TYPE_NAME as SMP_TYPE_NAME1, " +" t2.CERT_INST_CODE, " +" t2.CREATE_NAME, " +" to_char(t1.CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss') CREATE_TIME, " +" t2.smp_catg as smp_catg, " +" t2.CERT_INST_NAME, " +" t2.BOARD_NO, " +" t2.MATERIAL_NO, " +" t2.psc, " +" t2.psc_desc, " +" ord.DESIGN_KEY," +" t1.PHY_ITEM, " +" to_char(ord.thick, 'fm990.099') THICK," +" t5.ORD_WTH WIDTH," +" ord.LENGTH, " +" ord.DELIVERY_STATE_CODE, " +" ord.DELIVERY_STATE_DESC, " +" ord.PROD_NAME, " +" ord.STEEL_NAME, " +" ord.STD_NAME, " +" t1.ITEM_FLAG,t1.RZ_OLD_SAMPL_NO RZ_ENTRUST_NUMBER," + " DECODE(t3.SPEC_STL_GRD,'Q235B',GETMAXMIXROLL(t4.ROLL_MANA_NO, t4.MIXROLL, t4.COIL_NO),'Q355B', " + " GETMAXMIXROLL(t4.ROLL_MANA_NO, t4.MIXROLL, t4.COIL_NO),'0') MIXFLAG," + " T3.SMP_DIR_PIC_YN,t1.SAMPL_PICK_RY,t1.PRINT_LOG,t1.SEND_SEQ,t1.PRINT_SEQ " +" from tbh02_coil_comm t3, " +" tbf02_spec_mill t4, " +" TBE02_ORD_PRC t5, " +" QCM_JHY_SAMPLE_CONSIGN_D t1, " +" QCM_JHY_SAMPLE_CONSIGN_M t2, " +" QCM_JHY_SAMPLE_R_ORD ord " +" where t3.SAMPL_NO = t1.inspection_lot(+) " +" and t3.COIL_NO = t4.COIL_NO(+) " +" AND t3.ORD_NO = t5.ORD_NO(+) " +" AND t3.ORD_SEQ = t5.ORD_SEQ(+) " +" and t1.SMP_NO = t2.SMP_NO(+) " +" and t1.smp_no = ord.smp_no(+) and T3.OLD_SAMPL_NO is not null "; 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()) ) { sql +=" and upper(t1.batch_no) >= upper('"+parmas.get("BATCH_NO")+"')"; sql +=" and upper(t1.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")){ sql +=" and t3.OLD_SAMPL_NO like '"+parmas.get("BATCH_NO")+"%' "; } if (parmas.get("CERT_INST_NAME")!=null && !"".equals(((String)parmas.get("CERT_INST_NAME")).trim())) { sql +=" and t2.cert_inst_name like '"+parmas.get("CERT_INST_NAME")+"%'"; } if (parmas.get("STEEL_NAME")!=null && !"".equals(((String)parmas.get("STEEL_NAME")).trim())) { sql +=" and ord.steel_name like '"+parmas.get("STEEL_NAME")+"%'"; } sql +=" and t1.STATUS = '0' AND nvl(T3.SMP_DIR_PIC_YN,'null') !='N' "; //卷取时间 if (parmas.get("STARTTIME")!=null && !"".equals(((String)parmas.get("STARTTIME")).trim())) { sql +=" and t3.COIL_END_DTIME between '"+parmas.get("STARTTIME")+"000000' and '"+parmas.get("ENDTIME")+"999999'"; } //轧辊单元 if (parmas.get("ROLL_MANA_NO")!=null && !"".equals(((String)parmas.get("ROLL_MANA_NO")).trim())) { sql +=" and t4.ROLL_MANA_NO = '"+parmas.get("ROLL_MANA_NO")+"'"; } if (parmas.get("HEAT_NO")!=null && !"".equals(((String)parmas.get("HEAT_NO")).trim())) { sql +=" and t1.HEAT_NO like '"+parmas.get("HEAT_NO")+"%'"; } sql +=" and nvl(T3.MISSNO_CLF_CD,'null') !='C' ";//C废品，S再回加热炉 sql +=" order by t4.ROLL_MANA_NO, t4.ROLL_SLAB_SEQ 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; } /** * 查询下拉框 轧批号 * @param parmas * @return */ public CoreReturnObject getBatchNo(String heatNo,String plineCode) { try{ heatNo = heatNo.substring(0, 9);//一共10位 截取9位 最后一位字母不要 String strPlineCode = ""; if(!plineCode.equals("ZB1") && !plineCode.equals("HB1") ){ strPlineCode = " and t.pline_code = '"+plineCode+"' "; }else{ strPlineCode = " and t.pline_code in ('ZB1','HB1') "; } String sql =""; sql =""; sql = "select t.BATCH_NO from QCM_JHY_SAMPLE_CONSIGN_D t " +" where t.heat_no like '"+heatNo+"%' and t.FREQ_CODE ='D' and t.QUOTE_CONSIGN_NO is not null " +strPlineCode +" group by t.batch_no order by t.batch_no "; 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 getBilletId(String batchNo,String plineCode) { try{ StringBuffer sqlucomm = new StringBuffer(); if(plineCode.equals("HB1")){ //厚 sqlucomm.append("select BILLETID as MATERIAL_NO from kch_turnofflist@xgcx where ROLLNUMBER='"+batchNo+"'" + " union select SLABNO MATERIAL_NO from ZYHB_PLANDETAILOFPLATE@xgcx where BATCHNO='"+batchNo+"' "); }else if(plineCode.equals("ZB1")){ //中 sqlucomm.append("select BILLETID as MATERIAL_NO from kcz_turnofflist@xgcx where ROLLNUMBER = '"+batchNo+"' " + " union select PRODUCTNO MATERIAL_NO from MB_PLANDETAILOFPLATE@xgcx where BATCHNO='"+batchNo+"' "); }else if(plineCode.equals("GX1") || plineCode.equals("BC2") || plineCode.equals("GX2")){ //线棒 sqlucomm.append("select BILLETID as MATERIAL_NO from kcx_turnofflist@xgcx where BILLETID like '"+batchNo+"'||'%' "); }else if(plineCode.equals("RZ1")){ //热轧 sqlucomm.append("select OLD_SAMPL_NO as MATERIAL_NO from tbh02_coil_comm where OLD_SAMPL_NO like '"+batchNo+"'||'%' "); }else if(plineCode.equals("LT1")){ //连退 sqlucomm.append("select OLD_SAMPL_NO as MATERIAL_NO from C_TBC02_COIL_COMM where OLD_SAMPL_NO like '"+batchNo+"'||'%' "); }else if(plineCode.equals("YT1")){ sqlucomm.append("SELECT t.COILNO as MATERIAL_NO FROM YDM_PRODUCT_DETAIL@LINK_YTG T WHERE t.BATCHNO = '"+batchNo+"' AND t.ORDERNO IS NOT NULL ORDER BY T.COILNO "); } List listEle= mapper.query(sqlucomm.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 getProcessNo(String batchNo,String plineCode) { try{ StringBuffer sqlucomm = new StringBuffer(); if(plineCode.equals("HB1")){ //厚 sqlucomm.append("select RCL_STATE from kch_turnofflist@xgcx where ROLLNUMBER='"+batchNo+"' and allotorderform is not null group by RCL_STATE "); }else if(plineCode.equals("ZB1")){ //中 sqlucomm.append("select RCL_STATE from kcz_turnofflist@xgcx where ROLLNUMBER = '"+batchNo+"' and orderno is not null group by RCL_STATE "); } List listEle= mapper.query(sqlucomm.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 getQltySampleOrd(String smp_no) { try{ StringBuffer sqlucomm = new StringBuffer(); sqlucomm.append("SELECT DESIGN_KEY,SMP_NO,PSC,PSC_DESC,to_char(THICK,'fm90.099') THICK,WIDTH,LENGTH," + " HEAT_NO,BATCH_NO,INSPECTION_LOT,DELIVERY_STATE_CODE,DELIVERY_STATE_DESC,PLINE_CODE," + " PLINE_NAME,PROD_NAME,STEEL_NAME,STD_NAME FROM QCM_JHY_SAMPLE_R_ORD WHERE SMP_NO='"+smp_no+"'"); List listEle= mapper.query(sqlucomm.toString()); cro.setResult(listEle); }catch(Exception ex){ cro.setV_errCode(-1); cro.setV_errMsg("获取材质合同信息出错"+ex.getMessage()); SqlSession.rollback(); } SqlSession.close(); return cro; } public CoreReturnObject getQltySampleOrdNew(String smp_no) { try{ StringBuffer sqlucomm = new StringBuffer(); sqlucomm.append("SELECT DESIGN_KEY,SMP_NO,PSC,PSC_DESC,to_char(THICK,'fm990.099') THICK,WIDTH,LENGTH," + " HEAT_NO,BATCH_NO,INSPECTION_LOT,DELIVERY_STATE_CODE,DELIVERY_STATE_DESC,PLINE_CODE," + " PLINE_NAME,PROD_NAME,STEEL_NAME,STD_NAME FROM QCM_JHY_SAMPLE_R_ORD WHERE SMP_NO='"+smp_no+"' "); /* cro = this.getDao("testDao").ExcuteQuery( sqlucomm.toString(), new Object[] { smp_no });*/ List listEle= mapper.query(sqlucomm.toString()); cro.setResult(listEle); }catch(Exception ex){ SqlSession.rollback(); } SqlSession.close(); return cro; } /** * 查询材质检验项目信息 * @param parmas * @return * @throws SQLException */ public CoreReturnObject getQltySampleItem(String specimen_no) throws SQLException { 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,QUOTE_SPECIMEN_NO,QUOTE_SEQ,MEMO,ITEM_CODE_L,ITEM_DESC_L,"); sqlucomm.append(" ITEM_FLAG FROM QCM_JHY_SAMPLE_CONSIGN_D_ITEM WHERE SPECIMEN_NO='"+specimen_no+"' order by seq asc"); ResultSet results=this.getDao("testDao").ExceuteQueryForResultSet(sqlucomm.toString()); if (results!=null ){ JSONArray jsonArray=resultSetToJsonArry(results); results.close(); for (int i = 0; i < jsonArray.size(); i++) { JSONObject result = (JSONObject)jsonArray.get(i); if (result.get("QUOTE_SPECIMEN_NO")!=null && !"".equals((String)result.get("QUOTE_SPECIMEN_NO"))) { sqlucomm.setLength(0); sqlucomm.append("SELECT R.VAL1,R.VAL2,R.VAL3 FROM QCM_JHY_SAMPLE_CONSIGN_D_ITEM J RIGHT JOIN QCM_JHY_INSP_PHYSICS R ON R.SPECIMEN_NO=J.SPECIMEN_NO "); sqlucomm.append("AND R.PHY_CODE_M=J.PHY_CODE_M AND R.PHY_CODE_S=J.PHY_CODE_S AND R.ITEM_CODE_D=J.ITEM_CODE_D AND R.ITEM_CODE_T=J.ITEM_CODE_T "); sqlucomm.append("AND R.ITEM_CODE_S=J.ITEM_CODE_S AND J.SPECIMEN_NO='"+result.get("QUOTE_SPECIMEN_NO")+"' AND SEQ="+result.get("QUOTE_SEQ")); ResultSet quoteResults=this.getDao("testDao").ExceuteQueryForResultSet(sqlucomm.toString()); if (quoteResults!=null){ JSONObject jsonQuote=(JSONObject)resultSetToJsonArry(quoteResults).get(0); quoteResults.close(); result.put("VAL1", jsonQuote.get("VAL1")); result.put("VAL2", jsonQuote.get("VAL2")); result.put("VAL3", jsonQuote.get("VAL3")); } } } cro.setResult(jsonArray); } SqlSession.close(); return cro; } public CoreReturnObject getQltySampleItemNew(String specimen_no) throws SQLException { try{ String sqlmba = " select r.design_key,m.* 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 m.smp_no = r.smp_no and t.specimen_no = '"+specimen_no+"' "; List mbahm= mapper.query(sqlmba.toString()); String lrasql=""; String lrasql2=""; if(mbahm.get(0).get("PLINE_CODE").equals("ZB1")|| mbahm.get(0).get("PLINE_CODE").equals("HB1")){ lrasql=",t.MEMOLAR,t.STDMIN,t.STDMAX"; }else if(mbahm.get(0).get("PLINE_CODE").equals("RZ1")){ lrasql=",t.STDMIN,t.STDMAX"; } if(mbahm.get(0).get("PLINE_CODE").equals("GX1")|| mbahm.get(0).get("PLINE_CODE").equals("GX2") || mbahm.get(0).get("PLINE_CODE").equals("BC2")){ lrasql2="order by t.phy_code_l desc, t.PHY_CODE_M asc, t.phy_code_s asc"; }else{ lrasql2="order by t.phy_code_l asc,t.PHY_CODE_M asc,t.phy_code_s asc"; } String sql =""; if(mbahm.get(0).get("SMP_CATG").toString().equals("A")){ sql ="select t.CHEM_CODE PHY_NAME_S from QCM_ORD_DESIGN_SAMPLE_D t where t.design_key ='"+mbahm.get(0).get("DESIGN_KEY").toString()+"'"; }else{ sql = "SELECT decode(t.SPECL_FL,'0','交付标准','1','特殊要求','4','加取项目样') SPECL_FL," + " 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.QUOTE_SPECIMEN_NO,t.QUOTE_SEQ,t.MEMO,t.ITEM_CODE_L,t.ITEM_DESC_L," +"t.ITEM_FLAG,t.QUOTE_MEMO,r.VAL1,r.VAL2,r.VAL3,t.STDMEMO " +lrasql + " FROM QCM_JHY_SAMPLE_CONSIGN_D_ITEM t,QCM_JHY_INSP_PHYSICS r " + " WHERE t.SPECIMEN_NO = r.Specimen_No(+) " + " and t.seq = r.seq(+) " + " 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.SPECIMEN_NO='"+specimen_no+"' and t.FY_QUOTE_SPECIMEN_NO is null " + lrasql2; } List listEle= mapper.query(sql.toString()); cro.setResult(listEle); }catch(Exception ex){ SqlSession.rollback(); } SqlSession.close(); return cro; } //中厚板导出界面 子级 public CoreReturnObject getQltySampleItemNew2(String specimen_no) throws SQLException { try{ String sql =""; sql = "SELECT decode(t.SPECL_FL,'0','交付标准','1','特殊要求','4','加取项目样') 标准类型," + " t.SPECIMEN_NO 取样编号,t.SEQ 项目序号,t.SMP_NO 试样号,t.PHY_NAME_L 检验大项,t.PHY_NAME_M 试样组," +" t.PHY_NAME_S 材质检验项,t.ITEM_NAME_D 试验方向,t.ITEM_NAME_T 试验温度,t.ITEM_NAME_S 试样尺寸,t.ITEM_DESC_L 试验位置," +" r.VAL1 值1,r.VAL2 值2,r.VAL3 值3,t.PHY_UNIT 值单位 " + " FROM QCM_JHY_SAMPLE_CONSIGN_D_ITEM t,QCM_JHY_INSP_PHYSICS r " + " WHERE t.SPECIMEN_NO = r.Specimen_No(+) " + " and t.seq = r.seq(+) " + " 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.SPECIMEN_NO='"+specimen_no+"' and t.FY_QUOTE_SPECIMEN_NO is null " + " order by t.phy_code_l asc,t.PHY_CODE_M asc,t.phy_code_s asc "; List listEle= mapper.query(sql.toString()); cro.setResult(listEle); }catch(Exception ex){ SqlSession.rollback(); } SqlSession.close(); return cro; } /** *ResultSet转换为JSON数组 * * @param ResultSet * @return JSONArray */ public static JSONArray resultSetToJsonArry(ResultSet rs) throws SQLException, JSONException { JSONArray array = new JSONArray(); ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount(); while (rs.next()) { JSONObject jsonObj = new JSONObject(); for (int i = 1; i <= columnCount; i++) { String columnName = metaData.getColumnLabel(i); String value = rs.getString(columnName); jsonObj.put(columnName, value); } array.add(jsonObj); } return array; } /** * 查询质量设计-交付标准-材质标准 * @param parmas * @return */ public CoreReturnObject getQltyBasePhy(String phyDescL,String phyDescM,String phyNameS,String plineCode) { try{ String sql=""; if(plineCode.equals("ZHB")){ sql = " and PROCESS_CODE in ('ZB1','HB1','HT1') "; }else if(plineCode.equals("GB")){ sql = " and PROCESS_CODE in ('GX1','BC1','BC2','GX2') "; } else if(plineCode.equals("LT1")){ sql = " and PROCESS_CODE ='LT1' "; } else if(plineCode.equals("RZ1")){ sql = " and PROCESS_CODE ='RZ1' "; } else if(plineCode.equals("YT1")){ sql = " and PROCESS_CODE = 'YT1' "; } if(!"".equals(phyDescL)){ sql += " and phy_desc_l = '"+phyDescL+"' "; } if(!"".equals(phyDescM)){ sql += " and phy_desc_m = '"+phyDescM+"' "; } if(!"".equals(phyNameS)){ sql += " and phy_name_s = '"+phyNameS+"' "; } StringBuffer sqlucomm = new StringBuffer(); sqlucomm.append(" select distinct phy_code_l,phy_code_m, phy_code_s,item_code_d,item_code_t,item_code_s,item_code_l, "); sqlucomm.append(" phy_desc_l, phy_desc_m,phy_name_s,item_desc_d,item_desc_t,item_desc_s,item_desc_l,"); sqlucomm.append(" ISJUDGE,JUDGE_BASIS,PHY_UNIT,MEMO "); sqlucomm.append(" from (select phy_code_l,phy_code_m, phy_code_s,item_code_d,item_code_t,item_code_s, phy_desc_l, phy_desc_m, "); sqlucomm.append(" phy_name_s,item_desc_d,item_desc_t,item_desc_s, isjudge,judge_basis,phy_unit, STDMEMO MEMO, item_code_l, "); sqlucomm.append(" item_desc_l,sample_style_code,sample_style_name, stdmin_sign,stdmin,stdmax_sign,stdmax,org_code,"); sqlucomm.append(" org_name,psc_desc,prod_code,prod_name, steel_code, steel_name,std_code,std_name,to_char(CREATE_TIME,'yyyy-MM-dd') CREATE_TIME"); sqlucomm.append(" from qcm_ord_design_std_pic where 1=1 " + sql + " ) t"); sqlucomm.append(" where 1=1"); if(!"".equals(phyDescL)){ sqlucomm.append(" and lower(phy_desc_l) like lower('"+phyDescL+"%') "); } if(!"".equals(phyDescM)){ sqlucomm.append(" and lower(phy_desc_m) like lower('"+phyDescM+"%') "); } if(!"".equals(phyNameS)){ sqlucomm.append(" and lower(phy_name_s) like lower('"+phyNameS+"%') "); } List listEle= mapper.query(sqlucomm.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 getPhyDescL(String plineCode) { try{ String sql=""; if(plineCode.equals("ZHB")){ sql = " and PROCESS_CODE in ('ZB1','HB1','HT1') "; }else if(plineCode.equals("GB")){ sql = " and PROCESS_CODE in ('GX1','BC1','BC2','GX2') "; } else if(plineCode.equals("LT1")){ sql = " and PROCESS_CODE ='LT1' "; } else if(plineCode.equals("RZ1")){ sql = " and PROCESS_CODE ='RZ1' "; } else if(plineCode.equals("YT1")){ sql = " and PROCESS_CODE = 'YT1' "; } StringBuffer sqlucomm = new StringBuffer(); sqlucomm.append("select phy_code_l, phy_desc_l from qcm_ord_design_std_pic where 1 = 1 " + sql + " group by phy_code_l, phy_desc_l"); List listEle= mapper.query(sqlucomm.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 getPhyDescM(String phyDescL,String plineCode) { try{ /*String sql=""; if(plineCode.equals("ZHB")){ sql = " and PROCESS_CODE in ('ZB1','HB1','HT1') "; }else if(plineCode.equals("GB")){ sql = " and PROCESS_CODE in ('GX1','BC1','BC2','GX2') "; } else if(plineCode.equals("LT1")){ sql = " and PROCESS_CODE ='LT1' "; } else if(plineCode.equals("RZ1")){ sql = " and PROCESS_CODE ='RZ1' "; } else if(plineCode.equals("YT1")){ sql = " and PROCESS_CODE = 'YT1' "; }*/ String sqlphy= "select t.* from QCM_JHY_SAMPLE_CONSIGN_D_ITEM t where phy_name_l = '"+phyDescL+"' and rownum=1 "; List listphy= mapper.query(sqlphy.toString()); String phyCodeL=listphy.get(0).get("PHY_CODE_L").toString(); StringBuffer sqlucomm = new StringBuffer(); sqlucomm.append("select phy_code_m, phy_name_m as phy_desc_m from QCM_JHY_SAMPLE_CONSIGN_D_ITEM "); sqlucomm.append(" where 1 = 1 and phy_name_l = '"+phyDescL+"' and PHY_CODE_L= '"+phyCodeL+"' " //+ sql + " group by phy_code_m, phy_name_m"); List listEle= mapper.query(sqlucomm.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 getPhyNameS(String phyDescL,String phyDescM,String plineCode) { try{ /* String sql=""; if(plineCode.equals("ZHB")){ sql = " and PROCESS_CODE in ('ZB1','HB1','HT1') "; }else if(plineCode.equals("GB")){ sql = " and PROCESS_CODE in ('GX1','BC1','BC2','GX2') "; } else if(plineCode.equals("LT1")){ sql = " and PROCESS_CODE ='LT1' "; } else if(plineCode.equals("RZ1")){ sql = " and PROCESS_CODE ='RZ1' "; } else if(plineCode.equals("YT1")){ sql = " and PROCESS_CODE = 'YT1' "; }*/ StringBuffer sqlucomm = new StringBuffer(); sqlucomm.append("select phy_code_s, phy_name_s from QCM_JHY_SAMPLE_CONSIGN_D_ITEM "); sqlucomm.append(" where 1 = 1 and phy_name_l = '"+phyDescL+"' and phy_name_m = '"+phyDescM+"' " //+ sql + " group by phy_code_s, phy_name_s order by phy_code_s "); List listEle= mapper.query(sqlucomm.toString()); cro.setResult(listEle); }catch(Exception ex){ cro.setV_errCode(-1); cro.setV_errMsg("获取数据出错"+ex.getMessage()); SqlSession.rollback(); } SqlSession.close(); return cro; } /** * 添加检验项 */ public CoreReturnObject addPhyItem(String jsonArray,String specimen_no,String smp_no) { //获取取样编号向下最大流水号 StringBuffer sqlucomm = new StringBuffer(); PreparedStatement ps = null; Connection cn = this.getDao("testDao").getConnection(); ResultSet rs =null; ResultSet results=null; JSONArray tarArr=JSON.parseArray(jsonArray); try { //验证只有未发送的才可以添加检验项 sqlucomm.append("select count(1) from QCM_JHY_SAMPLE_CONSIGN_D where SPECIMEN_NO='"+specimen_no+"' and STATUS <> '0'"); rs =this.getDao("testDao").ExceuteQueryForResultSet(sqlucomm.toString()); if(rs.next()){ if(rs.getInt(1)>0){ cro.setV_errCode(-1); cro.setV_errMsg("只有待发送的数据才能够添加检验项,请核实数据状态!"); return cro; } } rs.close(); //获取取样编号对应的项目，判断是否存在重复 sqlucomm.setLength(0); sqlucomm.append("select * from QCM_JHY_SAMPLE_CONSIGN_D_ITEM where SPECIMEN_NO='"+specimen_no+"'"); results=this.getDao("testDao").ExceuteQueryForResultSet(sqlucomm.toString()); JSONArray jsonObject=QuerryQltySample.resultSetToJsonArry(results); results.close(); if (jsonObject!=null && jsonObject.size()>0) { String oldStr=""; String newStr=""; for (int i = 0; i < jsonObject.size(); i++) { JSONObject oldTar=jsonObject.getJSONObject(i); oldStr=oldTar.getString("PHY_CODE_L")+"-"; oldStr=oldStr+oldTar.getString("PHY_CODE_M")+"-"; oldStr=oldStr+oldTar.getString("PHY_CODE_S")+"-"; if (oldTar.getString("ITEM_CODE_D")==null && !"".equals(oldTar.getString("ITEM_CODE_D"))) { oldStr=oldStr+oldTar.getString("ITEM_CODE_D")+"-"; }else{ oldStr=oldStr+"null"+"-"; } if (oldTar.getString("ITEM_CODE_T")==null && !"".equals(oldTar.getString("ITEM_CODE_T"))) { oldStr=oldStr+oldTar.getString("ITEM_CODE_T")+"-"; }else{ oldStr=oldStr+"null"+"-"; } if (oldTar.getString("ITEM_CODE_S")==null && !"".equals(oldTar.getString("ITEM_CODE_S"))) { oldStr=oldStr+oldTar.getString("ITEM_CODE_S"); }else{ oldStr=oldStr+"null"; } for (int j = 0; j < tarArr.size(); j++) { JSONObject newTar=tarArr.getJSONObject(j); newStr=newTar.getString("PHY_CODE_L")+"-"; newStr=newStr+newTar.getString("PHY_CODE_M")+"-"; newStr=newStr+newTar.getString("PHY_CODE_S")+"-"; if (newTar.getString("ITEM_CODE_D")==null && !"".equals(newTar.getString("ITEM_CODE_D"))) { newStr=newStr+newTar.getString("ITEM_CODE_D")+"-"; }else{ newStr=newStr+"null"+"-"; } if (newTar.getString("ITEM_CODE_T")==null && !"".equals(newTar.getString("ITEM_CODE_T"))) { newStr=newStr+newTar.getString("ITEM_CODE_T")+"-"; }else{ newStr=newStr+"null"+"-"; } if (newTar.getString("ITEM_CODE_S")==null && !"".equals(newTar.getString("ITEM_CODE_S"))) { newStr=newStr+newTar.getString("ITEM_CODE_S"); }else{ newStr=newStr+"null"; } if (oldStr.equals(newStr)) { cro.setV_errCode(-1); cro.setV_errMsg("原检验项已经存在["+newTar.getString("PHY_NAME_S")+"]项目，不能重复添加,请核实数据状态!"); return cro; } } } } //获取最大流水号 sqlucomm.setLength(0); sqlucomm.append("select max(SEQ) AS SEQ from QCM_JHY_SAMPLE_CONSIGN_D_ITEM where SPECIMEN_NO='"+specimen_no+"'"); Integer SEQ=0; results=this.getDao("testDao").ExceuteQueryForResultSet(sqlucomm.toString()); if (results.next()) { SEQ=results.getInt("SEQ"); SEQ++; } results.close(); cn.setAutoCommit(false); sqlucomm.setLength(0); sqlucomm.append("INSERT INTO QCM_JHY_SAMPLE_CONSIGN_D_ITEM ("); sqlucomm.append("SPECIMEN_NO,SEQ,SMP_NO,PHY_NAME_L,PHY_CODE_L,"); sqlucomm.append("PHY_CODE_M,PHY_NAME_M,PHY_CODE_S,PHY_NAME_S,ITEM_CODE_D,ITEM_NAME_D,ITEM_CODE_T,ITEM_NAME_T,"); sqlucomm.append("ITEM_CODE_S,ITEM_NAME_S,ISJUDGE,JUDGE_BASIS,PHY_UNIT,GROUP_SEQ,QUOTE_SPECIMEN_NO,"); sqlucomm.append("QUOTE_SEQ,MEMO,ITEM_CODE_L,ITEM_DESC_L,ITEM_FLAG) VALUES ("); sqlucomm.append("?,?,?,?,?,"); sqlucomm.append("?,?,?,?,?,?,?,?,"); sqlucomm.append("?,?,?,?,?,?,?,"); sqlucomm.append("?,?,?,?,'1')"); ps = cn.prepareStatement(sqlucomm.toString()); for (int i = 0; i < tarArr.size(); i++) { JSONObject jsonObj = tarArr.getJSONObject(i); jsonObj.put("SPECIMEN_NO", specimen_no); jsonObj.put("SMP_NO", smp_no); jsonObj.put("SEQ", SEQ++); FillPhyItem(ps, jsonObj); ps.addBatch(); } ps.executeBatch(); ps.clearBatch(); ps.close(); cn.commit(); cro.setV_errCode(0); cro.setV_errMsg("操作成功!"); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { try { if(null != results) results.close(); if(null != rs) rs.close(); if(null != ps) ps.close(); if(null != cn) cn.close();; } catch (SQLException e) { e.printStackTrace(); } } SqlSession.close(); return cro; } void FillPhyItem(PreparedStatement pSta1 , JSONObject qlty) throws Exception{ pSta1.setString(1,qlty.getString("SPECIMEN_NO")); pSta1.setLong(2, qlty.getLong("SEQ")); pSta1.setString(3, qlty.getString("SMP_NO")); pSta1.setString(4, qlty.getString("PHY_NAME_L")); pSta1.setString(5, qlty.getString("PHY_CODE_L")); pSta1.setString(6, qlty.getString("PHY_CODE_M")); pSta1.setString(7, qlty.getString("PHY_NAME_M")); pSta1.setString(8, qlty.getString("PHY_CODE_S")); pSta1.setString(9, qlty.getString("PHY_NAME_S")); pSta1.setString(10, qlty.getString("ITEM_CODE_D")); pSta1.setString(11, qlty.getString("ITEM_NAME_D")); pSta1.setString(12, qlty.getString("ITEM_CODE_T")); pSta1.setString(13, qlty.getString("ITEM_NAME_T")); pSta1.setString(14, qlty.getString("ITEM_CODE_S")); pSta1.setString(15, qlty.getString("ITEM_NAME_S")); pSta1.setString(16, qlty.getString("ISJUDGE")); pSta1.setString(17, qlty.getString("JUDGE_BASIS")); pSta1.setString(18, qlty.getString("PHY_UNIT")); pSta1.setString(19, qlty.getString("GROUP_SEQ")); pSta1.setString(20, qlty.getString("QUOTE_SPECIMEN_NO")); pSta1.setString(21, qlty.getString("QUOTE_SEQ")); pSta1.setString(22, qlty.getString("MEMO")); pSta1.setString(23, qlty.getString("ITEM_CODE_L")); pSta1.setString(24, qlty.getString("ITEM_DESC_L")); } /** * 人工委托 厚板线 * @param parmas * @return */ public CoreReturnObject getKchTurnofFlist(String DesignKey,String HeatNo,String BatchNo) { try{ if(StringUtils.isBlank(BatchNo) && StringUtils.isBlank(DesignKey)){ cro.setV_errCode(-1); cro.setV_errMsg("轧批号不能为空!!!"); SqlSession.rollback(); return cro; } String sql ="SELECT decode(GET_LRAWEGHT_YN(t.GRADE_CODE),'1',GET_LRAWEGHT,'0','') LRAWEGHT,t.* ," + " case when nvl((select count(1) num from qcm_jhy_sample_r_ord r where r.design_key = t.design_key and r.batch_no = t.batch_no),'0') = '0' then '否' else '是' end NUMUER " + " FROM " +" (SELECT '' RCL_STATE,T.ORDERID DESIGN_KEY,A.PSRNO PSC, " +" to_char(T.HEATNO) HEAT_NO,T.BATCHNO BATCH_NO,T.MOTHERSLAB BOARD_NO, " +" to_char(T.STEELTYPE) GRADE_CODE,to_char(T.STEELTYPE) GRADE_NAME,'ABBR' PLINE_ABBR,T.SLABNO MATERIAL_NO, " +" 'L-HPT-00005872' MSC_PLINE,'HB1' PLINE_CODE,'厚板线' PLINE_NAME,A.STANDROLLER DELIVERY_STATE_CODE, " +" A.STANDSTATUS DELIVERY_STATE_DESC,'HR' PROCESS_CODE,T.THICK THICK, T.WIDTH WIDTH,T.LEN LENGTH, " +" nvl(T.BILLETID_SY,T.BATCHNO||'0001') INSPECTION_LOT, " +" (select CASE WHEN COUNT(1)>0 THEN '1' ELSE '0' END from QCM_ORD_DESIGN_SAMPLE_M where design_key= T.ORDERID) IS_QTLY, " +" (select CASE WHEN COUNT(1)>0 THEN '1' ELSE '0' END from QCM_ORD_DESIGN_STD_CIC where design_key=T.ORDERID " +" and std_type_code='C') IS_CHEM,'0' TYPE,' ' MEMO,' ' QLTY_SMP_NO,A.ISTHIRD VESSEL,A.SAMPLINGREQUESTTIME " +" FROM ZYHB_PLANDETAILOFPLATE@xgcx T,SEL_PACTDETAIL_NEW@xgcx A " +" WHERE T.ORDERID = A.ORDERNO " + " and T.SLABNO not in (select T.BILLETID from KCH_TURNOFFLIST@xgcx T where t.ROLLNUMBER like '"+BatchNo+"%' )" + " and T.ORDERID is not null " // +" AND T.MAKETIME > SYSDATE - 15 " // +" AND A.SAMPLINGREQUESTTIME IN ('A','AB') " +" union all " +" SELECT t.RCL_STATE,T.ALLOTORDERFORM DESIGN_KEY,A.PSRNO PSC, " +" T.STOVENO HEAT_NO,T.ROLLNUMBER BATCH_NO,T.MOTHERBOARDNUMBER BOARD_NO, " +" T.DETERMINANTCARDNUMBER GRADE_CODE,T.DETERMINANTCARDNUMBER GRADE_NAME,'ABBR' PLINE_ABBR,T.BILLETID MATERIAL_NO, " +" 'L-HPT-00005872' MSC_PLINE,'HB1' PLINE_CODE,'厚板线' PLINE_NAME,A.STANDROLLER DELIVERY_STATE_CODE, " +" A.STANDSTATUS DELIVERY_STATE_DESC,'HR' PROCESS_CODE,T.PLY THICK,T.WIDTH WIDTH,T.LENGTH LENGTH, " +" nvl(T.BILLETID_HB_SY,T.ROLLNUMBER||'0001') INSPECTION_LOT, " +" (select CASE WHEN COUNT(1)>0 THEN '1' ELSE '0' END from QCM_ORD_DESIGN_SAMPLE_M where design_key= T.ALLOTORDERFORM) IS_QTLY, " +" (select CASE WHEN COUNT(1)>0 THEN '1' ELSE '0' END from QCM_ORD_DESIGN_STD_CIC where design_key=T.ALLOTORDERFORM " +" and std_type_code='C') IS_CHEM,'0' TYPE,' ' MEMO,' ' QLTY_SMP_NO,A.ISTHIRD VESSEL,A.SAMPLINGREQUESTTIME " +" FROM KCH_TURNOFFLIST@xgcx T,SEL_PACTDETAIL_NEW@xgcx A " +" WHERE T.ALLOTORDERFORM = A.ORDERNO" + " and T.ALLOTORDERFORM is not null " // +" AND (select count(1) from zyzb_heatinfo@xgcx " // +" where ISOUT = '1' and ISVALID = '1' and BILLETID = t.billetid and substr(RCL_STATE,3,1) = 'O') > 0 " // +" AND T.CREATTIME > SYSDATE - 15 " // +" AND A.SAMPLINGREQUESTTIME IN ('B','AB') " +" AND T.PSTATFLAG <> '0' " +" )t WHERE (t.IS_QTLY <> 0 OR t.IS_CHEM > 0) "; if(DesignKey != null && !DesignKey.equals("")){ sql += " and t.DESIGN_KEY like '"+DesignKey+"%'";//销售订单号 } if(HeatNo != null && !HeatNo.equals("")){ sql += " and t.HEAT_NO like '"+HeatNo+"%'";//炉号 } if(BatchNo != null && !BatchNo.equals("")){ sql += " and t.BATCH_NO like '"+BatchNo+"%'";//轧批号 } sql +=" and t.BATCH_NO like 'H%' order by t.MATERIAL_NO desc"; StringBuffer sqlucomm = new StringBuffer(); sqlucomm.append(sql); List listEle= mapper.query(sqlucomm.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 getKczTurnofFlist(String DesignKey,String HeatNo,String BatchNo) { try{ if(StringUtils.isBlank(BatchNo)&& StringUtils.isBlank(DesignKey)){ cro.setV_errCode(-1); cro.setV_errMsg("轧批号不能为空!!!"); SqlSession.rollback(); return cro; } String sql = "SELECT decode(GET_LRAWEGHT_YN(t.GRADE_CODE),'1',GET_LRAWEGHT,'0','') LRAWEGHT,t.* ," + " case when nvl((select count(1) num from qcm_jhy_sample_r_ord r where r.design_key = t.design_key and r.batch_no = t.batch_no),'0') = '0' then '否' else '是' end NUMUER " +" FROM (SELECT '' RCL_STATE,T.ORDERNO DESIGN_KEY, " +" A.PSRNO PSC, " +" to_char(T.HEATNO) HEAT_NO, " +" T.BATCHNO BATCH_NO, " +" T.MOTHERPLATEID BOARD_NO, " +" to_char(T.STEELCODE) GRADE_CODE, " +" to_char(T.STEELCODE) GRADE_NAME, " +" 'ABBR' PLINE_ABBR, " +" T.PRODUCTNO MATERIAL_NO, " +" 'L-HPT-00005872' MSC_PLINE, " +" 'ZB1' PLINE_CODE, " +" '中板线' PLINE_NAME, " +" A.STANDROLLER DELIVERY_STATE_CODE, " +" A.STANDSTATUS DELIVERY_STATE_DESC, " +" 'HR' PROCESS_CODE, " +" T.HEIGHT THICK, " +" T.WIDTH WIDTH, " +" T.LENGTH LENGTH, " +" nvl(T.BILLETID_SY, T.BATCHNO || '0001') INSPECTION_LOT, " +" (select CASE " +" WHEN COUNT(1) > 0 THEN " +" '1' " +" ELSE " +" '0' " +" END " +" from QCM_ORD_DESIGN_SAMPLE_M " +" where design_key = T.ORDERNO) IS_QTLY, " +" (select CASE " +" WHEN COUNT(1) > 0 THEN " +" '1' " +" ELSE " +" '0' " +" END " +" from QCM_ORD_DESIGN_STD_CIC " +" where design_key = T.ORDERNO " +" and std_type_code = 'C') IS_CHEM, " +" '0' TYPE, " +" ' ' MEMO, " +" ' ' QLTY_SMP_NO, " +" A.ISTHIRD VESSEL, " +" (SELECT min(ALLPHYTESTNAMES) FROM LH_SAMPLE_CONSIGN_DETAIL@xgcx where PID_MB_CB_ID = t.productno) ALLPHYTESTNAMES, " // +" dbms_lob.substr((select wmsys.wm_concat(DISTINCT phy_name_s) from QCM_ORD_DESIGN_STD_PIC WHERE design_key=T.ORDERNO)) ALLPHYTESTNAMES2" // +"(select wmsys.wm_concat(DISTINCT phy_name_s) from QCM_ORD_DESIGN_STD_PIC WHERE design_key = T.ORDERNO) ALLPHYTESTNAMES2" +" (select listagg(phy_name_s,',')within group(order by phy_name_s)from " +" (select distinct phy_name_s,design_key from QCM_ORD_DESIGN_STD_PIC) " +" WHERE design_key = T.ORDERNO ) ALLPHYTESTNAMES2, " +" A.SAMPLINGREQUESTTIME " +" FROM MB_PLANDETAILOFPLATE@xgcx T, SEL_PACTDETAIL_NEW@xgcx A " +" WHERE T.ORDERNO = A.ORDERNO " + " and T.PRODUCTNO not in (select T.BILLETID from KCZ_TURNOFFLIST@xgcx T where t.ROLLNUMBER like '"+BatchNo+"%' )" + " and T.ORDERNO is not null " // +" AND T.MAKETIME > SYSDATE - 3 " +" AND A.SAMPLINGREQUESTTIME IN ('A', 'AB') " +" union all " +" SELECT t.RCL_STATE,T.ORDERNO DESIGN_KEY, " +" A.PSRNO PSC, " +" T.STOVENO HEAT_NO, " +" T.ROLLNUMBER BATCH_NO, " +" T.MOTHERBOARDNUMBER BOARD_NO, " +" T.DETERMINANTCARDNUMBER GRADE_CODE, " +" T.DETERMINANTCARDNUMBER GRADE_NAME, " +" 'ABBR' PLINE_ABBR, " +" T.BILLETID MATERIAL_NO, " +" 'L-HPT-00005872' MSC_PLINE, " +" 'ZB1' PLINE_CODE, " +" '中板线' PLINE_NAME, " +" A.STANDROLLER DELIVERY_STATE_CODE, " +" A.STANDSTATUS DELIVERY_STATE_DESC, " +" 'HR' PROCESS_CODE, " +" T.PLY THICK, " +" T.WIDTH WIDTH, " +" T.LENGTH LENGTH, " +" nvl(T.BILLETID_SY, T.ROLLNUMBER || '0001') INSPECTION_LOT, " +" (select CASE " +" WHEN COUNT(1) > 0 THEN " +" '1' " +" ELSE " +" '0' " +" END " +" from QCM_ORD_DESIGN_SAMPLE_M " +" where design_key = T.ORDERNO) IS_QTLY, " +" (select CASE " +" WHEN COUNT(1) > 0 THEN " +" '1' " +" ELSE " +" '0' " +" END " +" from QCM_ORD_DESIGN_STD_CIC " +" where design_key = T.ORDERNO " +" and std_type_code = 'C') IS_CHEM, " +" '0' TYPE, " +" ' ' MEMO, " +" ' ' QLTY_SMP_NO, " +" A.ISTHIRD VESSEL, " +" (SELECT min(ALLPHYTESTNAMES) FROM LH_SAMPLE_CONSIGN_DETAIL@xgcx where PID_MB_CB_ID = t.BILLETID) ALLPHYTESTNAMES, " // +" dbms_lob.substr((select wmsys.wm_concat(DISTINCT phy_name_s) from QCM_ORD_DESIGN_STD_PIC WHERE design_key=T.ORDERNO)) ALLPHYTESTNAMES2" // +"(select wmsys.wm_concat(DISTINCT phy_name_s) from QCM_ORD_DESIGN_STD_PIC WHERE design_key = T.ORDERNO) ALLPHYTESTNAMES2" +" (select listagg(phy_name_s,',')within group(order by phy_name_s)from " +" (select distinct phy_name_s,design_key from QCM_ORD_DESIGN_STD_PIC) " +" WHERE design_key = T.ORDERNO ) ALLPHYTESTNAMES2, " +" A.SAMPLINGREQUESTTIME " +" FROM KCZ_TURNOFFLIST@xgcx T, SEL_PACTDETAIL_NEW@xgcx A " +" WHERE T.ORDERNO = A.ORDERNO " + " and T.ORDERNO is not null " // +" AND T.CREATTIME > SYSDATE - 3 " // +" AND (select count(1) " // +" from zyzb_heatinfo@xgcx " // +" where ISOUT = '1' " // +" and ISVALID = '1' " // +" and BILLETID = t.billetid " // +" and substr(RCL_STATE, 3, 1) = 'O') > 0 " // +" AND A.SAMPLINGREQUESTTIME IN ('B', 'AB') " +" AND T.ISVALID <> '0') t " +" WHERE (t.IS_QTLY <> 0 OR t.IS_CHEM > 0) "; if(DesignKey != null && !DesignKey.equals("")){ sql += " and t.DESIGN_KEY like '"+DesignKey+"%'";//销售订单号 } if(HeatNo != null && !HeatNo.equals("")){ sql += " and t.HEAT_NO like '"+HeatNo+"%'";//炉号 } if(BatchNo != null && !BatchNo.equals("")){ sql += " and t.BATCH_NO like '"+BatchNo+"%'";//轧批号 } sql +=" and t.BATCH_NO like 'Z%' order by t.MATERIAL_NO desc"; StringBuffer sqlucomm = new StringBuffer(); sqlucomm.append(sql); List listEle= mapper.query(sqlucomm.toString()); cro.setResult(listEle); /*cro = this.getDao("testDao").ExcuteQuery(sql.toString());*/ }catch(Exception ex){ cro.setV_errCode(-1); cro.setV_errMsg("获取数据出错"+ex.getMessage()); SqlSession.rollback(); } SqlSession.close(); return cro; } /** * 人工委托 高棒线 * @param parmas * @return */ public CoreReturnObject getKcxTurnofFlist(String DesignKey,String HeatNo,String BatchNo) { try{ /* String sql = " SELECT (SELECT MAX(BILLETID_JY) FROM ZJ_RESULT_ALL@xgcx WHERE BILLETID = T.BILLETID) as INSPECTION_LOT,ORDERNO as DESIGN_KEY, " +" (SELECT PSRNO FROM SEL_PACTDETAIL@xgcx WHERE ORDERNO = T.ORDERNO) as PSC,STOVENO as HEAT_NO,ROLLNUMBER as BATCH_NO,ROLLNUMBER as BOARD_NO, " +" (SELECT MAX(STEELCODE) FROM kcx_stufflist@xgcx WHERE STOVENO = T.STOVENO) as GRADE_CODE, " +" (SELECT MAX(STEELCODE) FROM kcx_stufflist@xgcx WHERE STOVENO = T.STOVENO) as GRADE_NAME, " +" BILLETID as MATERIAL_NO,SUBSTR(PLD,5,3) as MSC_PLINE,SUBSTR(PLD,5,3) as PLINE_CODE,C2N@xgcx(PLD) as PLINE_NAME, " +" (select max(STANDROLLER) from sel_pactdetail where ORDERNO = t.ORDERNO) as DELIVERY_STATE_CODE, " +" (select max(STANDROLLER) from sel_pactdetail where ORDERNO = t.ORDERNO) as DELIVERY_STATE_DESC," +" '' as PROCESS_CODE,PLY as THICK,WIDTH,LENGTH,'1' as IS_QTLY,'1' as IS_CHEM,'0' as TYPE,'' as MEMO " +" FROM KCX_TURNOFFLIST@xgcx T WHERE 1=1 ";//BILLETID LIKE 'Y2%' if(DesignKey != null && !DesignKey.equals("")){ sql += " and ALLOTORDERFORM like '%"+DesignKey+"%'";//销售订单号 } if(HeatNo != null && !HeatNo.equals("")){ sql += " and STOVENO like '%"+HeatNo+"%'";//炉号 } if(BatchNo != null && !BatchNo.equals("")){ sql += " and ROLLNUMBER like '%"+BatchNo+"%'";//轧批号 } if((DesignKey == null || DesignKey.equals("")) && (HeatNo == null || HeatNo.equals("")) && (BatchNo == null || BatchNo.equals("")) ){ sql += " and T.intime > sysdate - 3 "; } sql +=" AND T.DETERMINANTRESULT IS NOT NULL order by BILLETID desc";*/ //--'ABBR' PLINE_ABBR, -- ' ' QLTY_SMP_NO, -- T.INTIME if(StringUtils.isBlank(BatchNo)){ cro.setV_errCode(-1); cro.setV_errMsg("轧批号不能为空!!!"); SqlSession.rollback(); return cro; } String sql = "SELECT * " +" FROM (SELECT T.ORDERNO DESIGN_KEY, " +" A.PSRNO PSC, " +" T.STOVENO HEAT_NO, " +" T.ROLLNUMBER BATCH_NO, " +" T.ROLLNUMBER BOARD_NO, " +" T.DETERMINANTCARDNUMBER GRADE_CODE, " +" T.DETERMINANTCARDNUMBER GRADE_NAME, " +" " +" T.BILLETID MATERIAL_NO, " +" 'L-HPT-00005872' MSC_PLINE, " +" substr(T.PLD, 5, 3) PLINE_CODE, " +" C2N@xgcx(T.PLD) PLINE_NAME, " +" A.STANDROLLER DELIVERY_STATE_CODE, " +" A.STANDSTATUS DELIVERY_STATE_DESC, " +" 'HR' PROCESS_CODE, " +" A.HEIGHT THICK, " +" A.WIDTH WIDTH, " +" A.LENGTH LENGTH, " +" nvl(T.BILLETID_SY, T.ROLLNUMBER || '0001') INSPECTION_LOT, " +" (select CASE WHEN COUNT(1)>0 THEN '1' ELSE '0' END from QCM_ORD_DESIGN_SAMPLE_M where design_key= T.ORDERNO) IS_QTLY, " +" (select CASE WHEN COUNT(1)>0 THEN '1' ELSE '0' END from QCM_ORD_DESIGN_STD_CIC where design_key=T.ORDERNO and std_type_code='C') IS_CHEM, " +" '0' TYPE, " +" ' ' MEMO, " +" " +" nvl(A.XB_BATCHWEIGHT_MAX, '0') WEIGHT_STD, " +" nvl((select sum(WEIGHT) " +" from kcx_stuffoutstorage@xgcx " +" where ROLLNO = t.rollnumber " +" AND ISVALID = '1'), " +" (select sum(THEORYWEIGHT) " +" from kcx_turnofflist " +" where ROLLNUMBER = t.rollnumber)) WEIGHT, " +" nvl(A.ADDFREQNUM, '0') WEIGHT_SFD " +" " +" FROM KCX_TURNOFFLIST@xgcx T, SEL_PACTDETAIL_NEW@xgcx A " +" WHERE T.ORDERNO = A.ORDERNO "; if(DesignKey != null && !DesignKey.equals("")){ sql += " and ORDERNO like '"+DesignKey+"%'";//销售订单号 } if(HeatNo != null && !HeatNo.equals("")){ sql += " and STOVENO like '"+HeatNo+"%'";//炉号 } if(BatchNo != null && !BatchNo.equals("")){ sql += " and ROLLNUMBER like '"+BatchNo+"%'";//轧批号 } //AND T.INTIME > SYSDATE - 3 AND T.DETERMINANTRESULT IS NOT NULL sql +=" order by t.intime desc)" +" WHERE (IS_QTLY <> 0 OR IS_CHEM > 0)"; StringBuffer sqlucomm = new StringBuffer(); sqlucomm.append(sql); List listEle= mapper.query(sqlucomm.toString()); cro.setResult(listEle); }catch(Exception ex){ cro.setV_errCode(-1); cro.setV_errMsg("获取数据出错"+ex.getMessage()); SqlSession.rollback(); } SqlSession.close(); return cro; } /** * 人工委托 热轧线 @sq101 * @param parmas * @return */ public CoreReturnObject getHotRoll(String DesignKey,String HeatNo,String BatchNo) { try{ if(StringUtils.isBlank(BatchNo)){ cro.setV_errCode(-1); cro.setV_errMsg("轧批号不能为空!!!"); SqlSession.rollback(); return cro; } String sql = " select * from(select DISTINCT T1.SAMPL_NO INSPECTION_LOT, " + " T1.ORD_NO || T1.ORD_SEQ DESIGN_KEY, " + " (SELECT T.PSC " + " FROM QCM_ORD_DESIGN_STD_PIC T " + " WHERE T.DESIGN_KEY = T1.ORD_NO || T1.ORD_SEQ AND ROWNUM = 1) PSC, " + " SUBSTR(T1.SLAB_NO, 1, 10) HEAT_NO, " + " SUBSTR(T1.OLD_SAMPL_NO, 1, 10) BATCH_NO, " + " T1.SLAB_NO BOARD_NO, " + " t1.STL_GRD GRADE_CODE, " + " t1.STL_GRD GRADE_name, " + " 'RZ10' PLINE_ABBR, " + " T1.OLD_SAMPL_NO MATERIAL_NO, " + " 'RZ' MSC_PLINE, " + " 'RZ1' PLINE_CODE, " + " '热轧' PLINE_NAME, " + " t2.PRODNM_CD DELIVERY_STATE_CODE, " + " t2.PRODNM_CD DELIVERY_STATE_desc, " + " '' PROCESS_CODE, " + " t2.ORD_THK THICK, " + " t2.ORD_WTH WIDTH, " + " t1.COIL_LEN LENGTH, " + " '1' IS_QTLY, " + " '0' IS_CHEM, " + " '0' TYPE, " + " '' MEMO, " + " '' QLTY_OLD_INSPECTION, " + " '' CHEM_OLD_INSPECTION " + " from qcm_judge_coil_result t, tbh02_coil_comm t1 ,TBE02_ORD_PRC T2" +" where t1.OLD_SAMPL_NO = t.COIL_NO " + " AND T1.ORD_NO=T2.ORD_NO AND T1.ORD_SEQ=T2.ORD_SEQ "; if(DesignKey != null && !DesignKey.equals("")){ sql += " and T1.ORD_NO || T1.ORD_SEQ like '"+DesignKey+"%'";//销售订单号 } if(HeatNo != null && !HeatNo.equals("")){ sql += " and T1.SLAB_NO like '"+HeatNo+"%'";//炉号 } if(BatchNo != null && !BatchNo.equals("")){ sql += " and T1.OLD_SAMPL_NO like '"+BatchNo+"%'";//轧批号 } sql +=" order by T1.SAMPL_NO desc)t"; sql +=" where t.psc is not null "; StringBuffer sqlucomm = new StringBuffer(); sqlucomm.append(sql); List listEle= mapper.query(sqlucomm.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 getRetreat(String DesignKey,String HeatNo,String BatchNo) { try{ if(StringUtils.isBlank(BatchNo)){ cro.setV_errCode(-1); cro.setV_errMsg("轧批号不能为空!!!"); SqlSession.rollback(); return cro; } String sql = " select * from( select DISTINCT T1.SMP_NO INSPECTION_LOT, " + " T1.ORD_NO || T1.ORD_SEQ DESIGN_KEY, " + " (SELECT T.PSC FROM QCM_ORD_DESIGN_STD_PIC T " + " WHERE T.DESIGN_KEY = T1.ORD_NO || T1.ORD_SEQ AND ROWNUM = 1) PSC, " + " SUBSTR(T1.SLAB_NO, 1, 10) HEAT_NO, " + " SUBSTR(T1.OLD_SAMPL_NO, 1, 12) BATCH_NO, " + " T1.SLAB_NO BOARD_NO, " + " t1.SPEC_STL_GRD GRADE_CODE, " + " t1.SPEC_STL_GRD GRADE_name, " + " 'LT10' PLINE_ABBR, " + " T1.OLD_SAMPL_NO MATERIAL_NO, " + " 'LT' MSC_PLINE, " + " 'LT1' PLINE_CODE, " + " '连退' PLINE_NAME, " + " t2.C_PRODNM_CD DELIVERY_STATE_CODE, " + " t2.C_PRODNM_CD DELIVERY_STATE_desc, " + " '' PROCESS_CODE, " + " t2.C_ORD_THK THICK, " + " t2.C_ORD_WTH WIDTH, " + " t1.COIL_LEN LENGTH, " + " '1' IS_QTLY, " + " '0' IS_CHEM, " + " '0' TYPE, " + " '' MEMO, " + " '' QLTY_OLD_INSPECTION, " + " '' CHEM_OLD_INSPECTION " + " from qcm_judge_coil_result t, C_TBC02_COIL_COMM t1,TBE02_ORD_PRC T2 " +" where t1.OLD_sampl_no = t.coil_no " + " AND T1.ORD_NO=T2.ORD_NO AND T1.ORD_SEQ=T2.ORD_SEQ "; if(DesignKey != null && !DesignKey.equals("")){ sql += " and T1.ORD_NO || T1.ORD_SEQ like '"+DesignKey+"%'";//销售订单号 } if(HeatNo != null && !HeatNo.equals("")){ sql += " and T1.SLAB_NO like '"+HeatNo+"%'";//炉号 } if(BatchNo != null && !BatchNo.equals("")){ sql += " and T1.OLD_SAMPL_NO like '"+BatchNo+"%'";//轧批号 } sql +=" order by T1.SMP_NO desc ) t"; sql +=" where t.psc is not null "; StringBuffer sqlucomm = new StringBuffer(); sqlucomm.append(sql); List listEle= mapper.query(sqlucomm.toString()); cro.setResult(listEle); }catch(Exception ex){ cro.setV_errCode(-1); cro.setV_errMsg("获取数据出错"+ex.getMessage()); SqlSession.rollback(); } SqlSession.close(); return cro; } //查询人工委托的数据是否已委托还未发送 public CoreReturnObject selEntrust(String date) { ResultSet rs =null; JSONArray jsonArray = JSON.parseArray(date); if (jsonArray==null || jsonArray.size()<1) { cro.setV_errCode(-1); cro.setV_errMsg("请传入参数！"); return cro; } try { //循环执行每一条数据 for(int i=0; i < jsonArray.size();i++){ JSONObject jobject=jsonArray.getJSONObject(i); String designKey = (String)jobject.get("DESIGN_KEY");//订单号 String BATCH_NO = (String)jobject.get("BATCH_NO");// String PLINE_CODE = (String)jobject.get("PLINE_CODE");// String sql = " select count(*) countnum from QCM_JHY_SAMPLE_R_ORD ord inner join QCM_JHY_SAMPLE_CONSIGN_D d on ord.smp_no = d.smp_no " + " where ord.inspection_lot = d.inspection_lot and ord.psc = ord.psc and ord.heat_no = d.heat_no " + " and ord.batch_no = ord.batch_no and d.status = '0' and ord.design_key ='"+designKey+"' and d.PLINE_CODE = '"+PLINE_CODE+"' "; sql += "and ord.BATCH_NO ='"+BATCH_NO+"'"; StringBuffer sqlucomm = new StringBuffer(); sqlucomm.append(sql); rs =this.getDao("testDao").ExceuteQueryForResultSet(sqlucomm.toString()); if(rs.next()){ if(rs.getInt(1)>0){ cro.setV_errCode(1); SqlSession.close(); return cro; } } } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } cro.setV_errCode(0); SqlSession.close(); return cro; } /** * 导出中厚板 * @param parmas * @return */ public CoreReturnObject ZHBQueryExcel(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 sqlzh = ""; String timezh = ""; if(parmas.get("PLINE_NAME").equals("厚板线")){ sqlzh = " left join kch_turnofflist@xgcx st on t1.MATERIAL_NO = st.BILLETID "; timezh = "to_char(st.INSTORAGETIME, 'yyyy-mm-dd hh24:mi:ss') 入库时间, st.RCL_STATE 库存热处理号,"; }else if(parmas.get("PLINE_NAME").equals("中板线")){ sqlzh = " left join kcz_turnofflist@xgcx st on t1.MATERIAL_NO = st.BILLETID "; timezh = "to_char(st.INTIME, 'yyyy-mm-dd hh24:mi:ss') 入库时间, st.RCL_STATE 库存热处理号,"; } String sql = "select " +timezh + " t1.PROCESS_NOS 委托热处理号," +" t1.IMPROVE_MEMO 性能改善标志," +" t1.HEAT_NO 炉号, " +" t1.BATCH_NO 轧批号, " +" t1.BOARD_NO 母板号, " + " t1.SMP_NO 试样号, " +" ord.DESIGN_KEY 订单号, " +" ord.STEEL_NAME 牌号, " +" to_char(ord.THICK, 'fm990.099') 厚度, " +" t1.MATERIAL_NO 取样材料号, " +" case when t1.phy_item is not null then " +" t1.phy_item else " +" dbms_lob.substr((select wmsys.wm_concat(DISTINCT PHY_NAME_S) " +" from QCM_JHY_SAMPLE_CONSIGN_D_ITEM " +" WHERE SPECIMEN_NO = t1.SPECIMEN_NO " +" and fy_quote_specimen_no is null)) " +" end 检验项目, " +" t1.FREQ_NAME 取样频次, " +" case " +" when t1.SMP_TYPE_CODE = '0' then " +" case " +" when t2.CERT_INST_CODE in " +" ('IC003', 'IC004', 'IC001', 'IC010', '5000') then " +" t1.SMP_TYPE_NAME " +" else " +" '认证样' " +" end " +" else " +" t1.SMP_TYPE_NAME " +" end 取样类型, " +" t1.SMP_LOCATION 取样位置, " +" t1.SPECIMEN_NO 取样编号, " +" t1.QUOTE_CONSIGN_NO 引用取样编号, " + " t1.ZHB_SPECIMEN_NO 合并取样编号," +" decode(t1.STATUS,'1','已引用','2','已接收','3','已完成','7','确认接收样') 状态, " +" t2.CERT_INST_NAME 认证机构, " +" ord.DELIVERY_STATE_DESC 交货状态, " +" ord.PROD_NAME 产品名称, " +" ord.STD_NAME 执行标准, " +" t2.psc 产品规范代码, " +" t2.psc_desc 产品规范描述, " +" ord.WIDTH 宽, " +" st.length 长, " +" t1.INSPECTION_LOT 检验号, " +" t1.PLINE_NAME 产线, " +" t1.SEND_MEMO 发送备注, " +" t1.CONSIGN_NO 委托编号, " +" t1.CONSIGN_NO_SEQ 委托编号序号, " +" t1.TEST_QTY 试验次数, " +" t1.SMP_QTY 取样数量, " +" t2.SMP_TYPE_NAME 试样类型, " +" t2.smp_catg 试样类别, " +" t1.memo 订单备注, " + " t1.SAMPLE_DELIVERY_TIME 送样时间点," + " to_char(t1.COLLECT_TIME, 'yyyy-mm-dd hh24:mi:ss') LIMS接收时间," +" t1.SEND_NAME 发送人, " +" to_char(t1.SEND_TIME, 'yyyy-mm-dd hh24:mi:ss') 发送时间, " +" to_char(t1.CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss') 创建时间," + " t1.rz_mixroll 组批区间,to_char(t1.STOVE_TIME, 'yyyy-mm-dd hh24:mi:ss') 加热炉出炉时间," + " decode(t1.SHOULD_SAMPLE,'1','N','0','Y','2','Y(抽样)') 是否预测抽样," + " decode(t1.FUNCTION_FUHE,'1','合格','0','不合格') 预测性能," + " decode(t1.CRAFT_EXECUTE,'1','符合','0','不符合') 工艺是否符合," + " decode(t1.CHEM_INTERNALCONTROL,'1','符合','0','不符合') 成分是否符合" +" from QCM_JHY_SAMPLE_CONSIGN_D t1 " +" left join QCM_JHY_SAMPLE_CONSIGN_M t2 " +" on t1.SMP_NO = t2.SMP_NO " +" left join QCM_JHY_SAMPLE_R_ORD ord " +" on t1.smp_no = ord.smp_no "; sql += sqlzh +" where 1 = 1 "; 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()) ) { sql +=" and upper(t1.batch_no) >= upper('"+parmas.get("BATCH_NO")+"')"; sql +=" and upper(t1.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")){ sql +=" and t1.batch_no like '"+parmas.get("BATCH_NO")+"%' "; } if (parmas.get("CERT_INST_NAME")!=null && !"".equals(((String)parmas.get("CERT_INST_NAME")).trim())) { sql +=" and t2.cert_inst_name like '"+parmas.get("CERT_INST_NAME")+"%'"; } if (parmas.get("STEEL_NAME")!=null && !"".equals(((String)parmas.get("STEEL_NAME")).trim())) { sql +=" and ord.steel_name like '"+parmas.get("STEEL_NAME")+"%'"; } if (parmas.get("SMP_CATG")!=null && !"".equals(((String)parmas.get("SMP_CATG")).trim())) { sql +=" and t2.smp_catg = '"+parmas.get("SMP_CATG")+"'"; } if (parmas.get("VALIDFLAG")!=null && !"".equals(((String)parmas.get("VALIDFLAG")).trim())) { sql +=" and t1.validflag='"+parmas.get("VALIDFLAG")+"'"; } if (parmas.get("STATUS")!=null && !"".equals(((String)parmas.get("STATUS")).trim())) { sql +=" and t1.STATUS='"+parmas.get("STATUS")+"' and t1.STATUS <> '6' "; }else{ sql +=" and t1.STATUS not in ('0','5','6') "; } if(parmas.get("DESIGN_KEY")!=null && !"".equals(((String)parmas.get("DESIGN_KEY")).trim())){ sql +=" and ord.DESIGN_KEY like '"+parmas.get("DESIGN_KEY")+"%'"; } if(parmas.get("CHECKBOKTXTL").equals("T")){ sql+=" and t1.ZHB_SPECIMEN_NO is not null "; } if(parmas.get("TAB_NAME")!=null && !"".equals(((String)parmas.get("TAB_NAME")).trim())){ //发送时间 if (parmas.get("STARTTIME")!=null && !"".equals(((String)parmas.get("STARTTIME")).trim())) { sql +=" and t1.send_time>=to_date('"+parmas.get("STARTTIME")+" 00:00:00','yyyy-MM-dd HH24:mi:ss')"; } if (parmas.get("ENDTIME")!=null && !"".equals(((String)parmas.get("ENDTIME")).trim())) { sql +=" and t1.send_time<=to_date('"+parmas.get("ENDTIME")+" 23:59:59','yyyy-MM-dd HH24:mi:ss') "; } }else{ //创建时间 if (parmas.get("STARTTIME")!=null && !"".equals(((String)parmas.get("STARTTIME")).trim())) { sql +=" and t1.CREATE_TIME>=to_date('"+parmas.get("STARTTIME")+" 00:00:00','yyyy-MM-dd HH24:mi:ss')"; } if (parmas.get("ENDTIME")!=null && !"".equals(((String)parmas.get("ENDTIME")).trim())) { sql +=" and t1.CREATE_TIME<=to_date('"+parmas.get("ENDTIME")+" 23:59:59','yyyy-MM-dd HH24:mi:ss') "; } } if(parmas.get("PLINE_NAME").equals("厚板线")){ sql +=" and t1.BATCH_NO like 'H%' and t1.PLINE_CODE = 'HB1' "; }else if(parmas.get("PLINE_NAME").equals("中板线")){ sql +=" and t1.BATCH_NO like 'Z%' and t1.PLINE_CODE = 'ZB1' "; } if (parmas.get("HEAT_NO")!=null && !"".equals(((String)parmas.get("HEAT_NO")).trim())) { sql +=" and t1.HEAT_NO like '"+parmas.get("HEAT_NO")+"%'"; } if (parmas.get("SAMPLE_DELIVERY_TIME")!=null && !"".equals(((String)parmas.get("SAMPLE_DELIVERY_TIME")).trim())) { sql +=" and t1.SAMPLE_DELIVERY_TIME = '"+parmas.get("SAMPLE_DELIVERY_TIME")+"'"; } if(parmas.get("TAB_NAME")!=null && !"".equals(((String)parmas.get("TAB_NAME")).trim())){ sql +=" order by t1.send_time desc,t1.BATCH_NO desc"; }else{ sql +=" order by t1.CREATE_TIME desc,t1.BATCH_NO 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; } /** * 中板线 查询是否有复样这个轧批号 * @param parmas * @return */ public CoreReturnObject getDuplicateSample(String BATCH_NO,String PLINE_CODE,String INSPECTION_LOT) { try{ //and t.INSPECTION_LOT = '"+INSPECTION_LOT+"' 初样引用复样 降级 String sql= "select count(1) count from qcm_jhy_sample_consign_d t " + " where t.batch_no = '"+BATCH_NO+"' and t.pline_code = '"+PLINE_CODE+"' " + " and t.freq_code <> 'D' and t.smp_type_code = '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; } /** * 查询材质试样信息(优特钢) * @param parmas * @return */ public CoreReturnObject getQltySampleInfoYT(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 sql = "select t1.SMP_NO, " +" t1.SPECIMEN_NO, " +" t1.HEAT_NO, " +" t1.BATCH_NO, " +" t1.INSPECTION_LOT, " +" t1.FREQ_CODE, " +" t1.FREQ_NAME, " +" t1.MATERIAL_NO, " +" t1.SEND_MEMO, " +" t1.SMP_TYPE_CODE, " +" case " +" when t1.SMP_TYPE_CODE = '0' then " +" case " +" when t2.CERT_INST_CODE in " +" ('IC003', 'IC004', 'IC001', 'IC010', '5000') then " +" t1.SMP_TYPE_NAME " +" else " +" '认证样' " +" end " +" else " +" t1.SMP_TYPE_NAME " +" end SMP_TYPE_NAME, " +" t1.CONSIGN_NO, " +" t1.CONSIGN_NO_SEQ, " +" t1.TEST_QTY, " +" t1.SMP_QTY, " +" t1.SMP_LOCATION, " +" t1.BOARD_NO, " +" t1.QUOTE_CONSIGN_NO, " +" t1.OLD_CONSIGN_NO, " +" t1.PLINE_CODE, " +" t1.PLINE_NAME, " +" t1.STATUS, " +" t1.SEND_NAME, " +" to_char(t1.SEND_TIME, 'yyyy-mm-dd hh24:mi:ss') SEND_TIME, " +" t1.memo, " +" t2.SMP_TYPE_CODE as SMP_TYPE_CODE1, " +" t2.SMP_TYPE_NAME as SMP_TYPE_NAME1, " +" t2.CERT_INST_CODE, " +" t2.CREATE_NAME, " +" to_char(t1.CREATE_TIME, 'yyyy-mm-dd hh24:mi:ss') CREATE_TIME, " +" t2.smp_catg as smp_catg, " +" t2.CERT_INST_NAME, " +" t2.psc, " +" t2.psc_desc, " +" case when t1.phy_item is not null then " +" t1.phy_item else " +" dbms_lob.substr((select wmsys.wm_concat(DISTINCT PHY_NAME_S) " +" from QCM_JHY_SAMPLE_CONSIGN_D_ITEM " +" WHERE SPECIMEN_NO = t1.SPECIMEN_NO " +" and fy_quote_specimen_no is null)) " +" end PHY_NAME_L_BJ, " +" ord.DESIGN_KEY, " +" to_char(ord.THICK, 'fm990.099') THICK, " +" ord.WIDTH, " +" ord.length , " +" ord.DELIVERY_STATE_CODE, " +" ord.DELIVERY_STATE_DESC, " +" ord.PROD_NAME, " +" ord.STEEL_NAME, " +" ord.STD_NAME, " +" t1.ITEM_FLAG,to_char(t1.COLLECT_TIME, 'yyyy-mm-dd hh24:mi:ss') COLLECT_TIME," + " t1.PRINT_LOG,t1.PROCESS_CODE " +" from QCM_JHY_SAMPLE_CONSIGN_D t1 " +" left join QCM_JHY_SAMPLE_CONSIGN_M t2 " +" on t1.SMP_NO = t2.SMP_NO " +" left join QCM_JHY_SAMPLE_R_ORD ord " +" on t1.smp_no = ord.smp_no " +" where 1 = 1 and t1.PLINE_CODE = 'YT1' "; if(parmas.get("MATERIAL_NO")!=null && !"".equals(((String)parmas.get("MATERIAL_NO")).trim()) && parmas.get("checkboxVal").equals("1")){ sql +=" and t1.MATERIAL_NO like '"+parmas.get("MATERIAL_NO")+"%' "; } 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()) ) { sql +=" and upper(t1.batch_no) >= upper('"+parmas.get("BATCH_NO")+"')"; sql +=" and upper(t1.batch_no) <= upper('"+parmas.get("BATCH_NO2")+"')"; }else if(parmas.get("BATCH_NO")!=null && !"".equals(((String)parmas.get("BATCH_NO")).trim())){ sql +=" and t1.batch_no like '"+parmas.get("BATCH_NO")+"%' "; } if (parmas.get("CERT_INST_NAME")!=null && !"".equals(((String)parmas.get("CERT_INST_NAME")).trim())) { sql +=" and t2.cert_inst_name like '"+parmas.get("CERT_INST_NAME")+"%'"; } if (parmas.get("SMP_CATG")!=null && !"".equals(((String)parmas.get("SMP_CATG")).trim())) { sql +=" and t2.smp_catg = '"+parmas.get("SMP_CATG")+"'"; } if (parmas.get("VALIDFLAG")!=null && !"".equals(((String)parmas.get("VALIDFLAG")).trim())) { sql +=" and t1.validflag='"+parmas.get("VALIDFLAG")+"'"; } if (parmas.get("STATUS")!=null && !"".equals(((String)parmas.get("STATUS")).trim())) { sql +=" and t1.STATUS='"+parmas.get("STATUS")+"' and t1.STATUS <> '6' "; }else{ sql +=" and t1.STATUS not in ('0','5','6') "; } if(parmas.get("DESIGN_KEY")!=null && !"".equals(((String)parmas.get("DESIGN_KEY")).trim())){ sql +=" and ord.DESIGN_KEY like '"+parmas.get("DESIGN_KEY")+"%'"; } if(parmas.get("TAB_NAME")!=null && !"".equals(((String)parmas.get("TAB_NAME")).trim())){ //发送时间 if (parmas.get("STARTTIME")!=null && !"".equals(((String)parmas.get("STARTTIME")).trim())) { sql +=" and t1.send_time>=to_date('"+parmas.get("STARTTIME")+" 00:00:00','yyyy-MM-dd HH24:mi:ss')"; } if (parmas.get("ENDTIME")!=null && !"".equals(((String)parmas.get("ENDTIME")).trim())) { sql +=" and t1.send_time<=to_date('"+parmas.get("ENDTIME")+" 23:59:59','yyyy-MM-dd HH24:mi:ss') "; } }else{ //创建时间 if (parmas.get("STARTTIME")!=null && !"".equals(((String)parmas.get("STARTTIME")).trim())) { sql +=" and t1.CREATE_TIME>=to_date('"+parmas.get("STARTTIME")+" 00:00:00','yyyy-MM-dd HH24:mi:ss')"; } if (parmas.get("ENDTIME")!=null && !"".equals(((String)parmas.get("ENDTIME")).trim())) { sql +=" and t1.CREATE_TIME<=to_date('"+parmas.get("ENDTIME")+" 23:59:59','yyyy-MM-dd HH24:mi:ss') "; } } if (parmas.get("HEAT_NO")!=null && !"".equals(((String)parmas.get("HEAT_NO")).trim())) { sql +=" and t1.HEAT_NO like '"+parmas.get("HEAT_NO")+"%'"; } if (parmas.get("SAMPLE_DELIVERY_TIME")!=null && !"".equals(((String)parmas.get("SAMPLE_DELIVERY_TIME")).trim())) { sql +=" and t1.SAMPLE_DELIVERY_TIME = '"+parmas.get("SAMPLE_DELIVERY_TIME")+"'"; } if(parmas.get("TAB_NAME")!=null && !"".equals(((String)parmas.get("TAB_NAME")).trim())){ sql +=" order by t1.send_time desc,t1.BATCH_NO desc"; }else{ sql +=" order by t1.CREATE_TIME desc,t1.BATCH_NO 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; } /** * 人工委托 优特钢 * @param parmas * @return */ public CoreReturnObject getTurnofFlistYT(String DesignKey,String COILNO,String BatchNo) { try{ if(StringUtils.isBlank(BatchNo) && StringUtils.isBlank(DesignKey) && StringUtils.isBlank(COILNO)){ cro.setV_errCode(-1); cro.setV_errMsg("轧批号不能为空!!!"); SqlSession.rollback(); return cro; } String sql =" select '' INSPECTION_LOT, " +" substr(t.ORDERNO,0,LENGTH(t.ORDERNO)-3) DESIGN_KEY," +" '' PSC, " +" t.HEATNO HEAT_NO, " +" t.BATCHNO BATCH_NO, " +" substr(t.COILNO,0,length(t.COILNO)-2) BOARD_NO, " +" cbs.STEEL_CODE GRADE_CODE, " +" cbs.STEEL_NAME GRADE_NAME, " +" t.COILNO MATERIAL_NO, " +" '' MSC_PLINE, " +" 'YT1' PLINE_CODE, " +" '优特钢' PLINE_NAME, " +" t.DELIVERY_STATE_CODE, " +" t.DELIVERY_STATE_DESC, " +" t.MACHINE_NAME PROCESS_CODE, " +" t.THICK, " +" t.WIDTH, " +" t.LENGTH, " +" '1' IS_QTLY, " +" (select CASE " +" WHEN COUNT(1) > 0 THEN " +" '1' " +" ELSE " +" '0' " +" END " +" from QCM_ORD_DESIGN_STD_CIC " +" where design_key = T.ORDERNO " +" and std_type_code = 'C') IS_CHEM, " +" '0' TYPE, " +" '' MEMO " +" from YDM_PRODUCT_DETAIL@LINK_YTG t " +" left join COM_BASE_STEEL@LINK_YTG cbs " +" on t.STEELCODE = cbs.STEEL_CODE " +" where 1=1 AND t.ORDERNO IS NOT NULL "; if(DesignKey != null && !DesignKey.equals("")){ sql += " and t.ORDERNO like '"+DesignKey+"%'";//销售订单号 } if(COILNO != null && !COILNO.equals("")){ sql += " and t.COILNO like '"+COILNO+"%'";//子板号 } if(BatchNo != null && !BatchNo.equals("")){ sql += " and t.BATCHNO like '"+BatchNo+"%'";//轧批号 } sql +=" order by t.COILNO desc"; StringBuffer sqlucomm = new StringBuffer(); sqlucomm.append(sql); List listEle= mapper.query(sqlucomm.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 getQltyRZSpecimenNo(String specimenNo,String plineCode,String thick) { try{ String[] ja=specimenNo.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+","; } } } String sql = "select t3.OLD_SAMPL_NO,t1.BATCH_NO,t1.SPECIMEN_NO,t1.SMP_TYPE_CODE,r.THICK,r.HEAT_NO " +" from tbh02_coil_comm t3, " +" QCM_JHY_SAMPLE_CONSIGN_D t1, " +" qcm_jhy_sample_r_ord r " +" where t3.SAMPL_NO = t1.inspection_lot " +" and t1.smp_no = r.smp_no " +" and t3.SAMPL_NO = r.inspection_lot " +" and t1.inspection_lot = r.inspection_lot " +" and T3.OLD_SAMPL_NO is not null " +" and t1.specimen_no in ("+msInfo+") " +" and to_char(r.thick, 'fm990.099') = to_char('"+thick+"', 'fm990.099') " +" and t1.pline_code = 'RZ1' " +" and t1.STATUS='0' " +" and nvl(T3.MISSNO_CLF_CD, 'null') != 'C' " +" order by t3.OLD_SAMPL_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; } /** * 查询人工选样(热轧)限制提示 * @param parmas * @return */ public CoreReturnObject getQltyRZHeatNo(String specimenNo) { try{ TreeSet treeSet = new TreeSet(); TreeSet treeSet1 = new TreeSet(); String[] ja=specimenNo.split(","); String msInfo=""; if (ja!=null && ja.length>0) { for (int i = 0; i < ja.length; i++) { String sp = ja[i]; // String jsno2= sp.substring(sp.length()-2);//截取后两位 测试 if ("".equals(msInfo)) { msInfo="'"+sp+"'"; }else{ msInfo=msInfo+"'"+sp+"'"; } if (i !=ja.length-1) { msInfo=msInfo+","; } } } String sql = " select r.design_key,r.steel_code,t.* from qcm_jhy_sample_consign_d t,qcm_jhy_sample_r_ord r " + " where t.smp_no = r.smp_no and t.specimen_no in ("+msInfo+") "; List listEle= mapper.query(sql.toString()); if(listEle==null || listEle.size()<=0){ cro.setV_errCode(-1); cro.setV_errMsg("未找到相应数据!"); return cro; } String sqlitme="select t.THICK,t.specimen_no,t.batch_no,t.n from (select To_Number(r.THICK) THICK,d.specimen_no,d.batch_no, " +" (select count(1) from qcm_jhy_sample_consign_d_item i where i.specimen_no = d.specimen_no and i.seq < 50) n " +" from qcm_jhy_sample_consign_d d, qcm_jhy_sample_r_ord r where d.smp_no = r.smp_no and d.specimen_no in ("+msInfo+") order by r.thick desc " +" ) t order by t.THICK desc,t.n desc "; List hmitme= mapper.query(sqlitme.toString()); if(hmitme!=null && hmitme.size()>=1){ //获取最大的规格数据 String batch_no= hmitme.get(0).get("BATCH_NO").toString(); String THICK= hmitme.get(0).get("THICK").toString(); int nmu= Integer.parseInt(hmitme.get(0).get("N").toString()); for (int i = 0; i < hmitme.size(); i++) { HashMap obj=hmitme.get(i); String batch_no2= obj.get("BATCH_NO").toString(); String THICK2= obj.get("THICK").toString(); int nmu2= Integer.parseInt(obj.get("N").toString()); if(nmu2 > nmu){ String memo="选样中最大规格轧批号["+batch_no+"]["+THICK+"]有"+nmu+"个检验项,轧批号["+batch_no2+"]["+THICK2+"]有"+nmu2+"个检验项,请核实选样委托!"; cro.setV_errCode(-1); cro.setV_errMsg(memo); return cro; } } } for (int i = 0; i < listEle.size(); i++) { HashMap obj=listEle.get(i); String SPECIMEN_NO=obj.get("SPECIMEN_NO").toString(); String heatNo=obj.get("HEAT_NO").toString(); heatNo = heatNo.substring(0, heatNo.length()-1); treeSet.add(heatNo); treeSet1.add((String)obj.get("SMP_TYPE_CODE")); if("3".equals((String)obj.get("SMP_TYPE_CODE"))){ cro.setV_errCode(-1); cro.setV_errMsg("件件取样不能用人工选样按钮!"); return cro; } UpdateInfo uinfo= new UpdateInfo(); uinfo.doPhyItem(SPECIMEN_NO); //拿到牌号去查当前牌号是否可以人工选样 String steelCode=obj.get("STEEL_CODE").toString(); String sqlCode="select t.* from QCM_JHY_STEEL t where t.regulation = 'RZ1' and t.yn_log = '1' and t.steel_name = '"+steelCode+"' "; List listCode= mapper.query(sqlCode.toString()); if(listCode==null || listCode.size()<=0){ String batchNo=obj.get("BATCH_NO").toString(); cro.setV_errCode(-1); cro.setV_errMsg("轧批号:"+batchNo+",牌号:"+steelCode+",不能用人工选样发送按钮,优钢组批钢种并不包含这个牌号!请核实牌号数据!"); return cro; } } if(treeSet.size()>=2){ cro.setV_errCode(-1); cro.setV_errMsg("只能同炉号选样!"); return cro; } if(treeSet1.size()>=2){ cro.setV_errCode(-1); cro.setV_errMsg("只能勾选同一取样类型!"); return cro; } cro.setV_errCode(0); }catch(Exception ex){ cro.setV_errCode(-1); cro.setV_errMsg("获取出错"+ex.getMessage()); SqlSession.rollback(); }finally { SqlSession.close(); } return cro; } }