XinYu_LZMES/.svn/pristine/78/78216f4415d93c3e0ffa2c3e1f0e51137f172a88.svn-base

package QCM.JHY01.JHY0102;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.TreeSet;

import org.apache.commons.lang.StringUtils;
import org.apache.ibatis.session.SqlSession;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

import CoreFS.SA01.CoreIComponent;
import CoreFS.SA06.CoreReturnObject;
import QCM.COMMUNAL.QCM02.SqlJoint;
import QCM.COMMUNAL.VO.QcmJudgeOperateLog;
import QCM.JHY01.JHY0101.QuerryQltySample;
import UIB.COM.TechDcsResult;
import UIB.COM.XmlSqlParsersFactory;

public class QuerryJhyQtly extends CoreIComponent{
	
	CoreReturnObject cro = new CoreReturnObject();	
	SqlSession SqlSession = QCM.COMMUNAL.SqlSessionBuilder.openSqlSession();
	QCM.COMMUNAL.OrderSqMapper mapper = SqlSession.getMapper(QCM.COMMUNAL.OrderSqMapper.class);

	public String StrSky(String begintime,String endtime){
		String sql = "select (to_date('"+endtime+"','yyyy-MM-dd') - to_date('"+begintime+"','yyyy-MM-dd')) SKY from dual";
		HashMap list = mapper.queryOne(sql);
		Double ble = Double.parseDouble(list.get("SKY").toString());
		String tstr="FALSE";
		if(ble>=0 && ble <=30 ){
			tstr="TRUE";
		}
		return tstr;
	}
	
	/**
	 * 根据条件查询材质检验实绩
	 * @param gradeName
	 * @param heatNo
	 * @return
	 */
	public CoreReturnObject Query(String batchNo,String batchNo2,String design_key,String psc_desc,String startTime,String endTime,
			String pline_name,String dateTimeName,String materialNo) {
		
		if (!SqlJoint.IsNullOrSpace(startTime) && !SqlJoint.IsNullOrSpace(endTime)) {	
			String sky=StrSky(startTime,endTime);
			if(sky.equals("FALSE")){
				SqlSession.close();
				cro.setV_errCode(-1);
				cro.setV_errMsg("查询时间不能大于30天,请核实查询时间条件!");
				return cro;
			}
		}
		
		String sql = "select ord.DESIGN_KEY, "
      +" ord.SMP_NO, "
      +"  ord.PSC, "
      +"  ord.PSC_DESC, "
      +"  ord.HEAT_NO, "
      +"  ord.BATCH_NO, "
      +"  ord.INSPECTION_LOT, "
      +"  ord.DELIVERY_STATE_CODE, "
      +" ord.DELIVERY_STATE_DESC, "
      +" to_char(ord.THICK, 'fm990.099') THICK, "
      +" ord.WIDTH, "
      +" ord.LENGTH, "
      +" ord.PLINE_CODE, "
      +" ord.PLINE_NAME, "
      +" ord.MSC_PLINE, "
      +" ord.CERT_INST_CODE, "
      +" ord.CERT_INST_NAME, "
      +" ord.SMP_TYPE_CODE, "
      +" ord.SMP_TYPE_NAME, "
      +" ord.PROD_NAME, "
      +" ord.STEEL_NAME, "
      +" ord.STD_NAME, "
      +" ord.SMELTING_CNT, "
      +" ord.PRODUCT_CNT, "
      +" ord.ASSAY_NO_CP, "
      +" ord.ASSAY_NO_RL, "
      +" ord.CIC_ID, "
      +" ord.PHY_ID, "
      +" ord.JUDGE_STATUS, "
      +" ord.JUDGE_RESULT_DESC, "
      +" ord.JUDGE_NAME, "
      +" to_char(ord.JUDGE_TIME, 'yyyy-MM-dd hh24:mi:ss') JUDGE_TIME, "
      +" ord.JUDGE_MEMO, "
      +" ord.CREATE_NAME, "
      +" to_char(ord.CREATE_TIME, 'yyyy-MM-dd hh24:mi:ss') CREATE_TIME, "
      +" to_char(d.send_time, 'yyyy-MM-dd hh24:mi:ss') 发送时间, "
      +"    (select max(material_no) "
      +"    from qcm_jhy_sample_consign_d a "
      +"   where a.smp_no = ord.smp_no) 取样材料号, "
      +" (select decode(max(a.Smp_Type_Code), '1', '复样', '0', '初样') "
      +"    from qcm_jhy_sample_consign_d a "
      +"   where a.smp_no = ord.smp_no) 取样方式,d.PROCESS_NO,d.PROCESS_NOS,d.FREQ_NAME,d.SMP_LOCATION       "
 +" FROM QCM_JHY_SAMPLE_R_ORD ord "
 +" inner join QCM_JHY_SAMPLE_CONSIGN_M m "
 +"   on m.smp_no = ord.smp_no "
 +" inner join QCM_JHY_SAMPLE_CONSIGN_D d "
 +"   on ord.smp_no = d.smp_no "
+" WHERE 1 = 1 and d.STATUS <> 5 and d.STATUS <> 6 and ord.JUDGE_STATUS<>3 ";

		if (batchNo!=null && !"".equals(batchNo)
				&& batchNo2!=null && !"".equals(batchNo2)) {
					
			sql +="  and upper(ord.batch_no) >= upper('"+batchNo+"')";	
			sql +="  and upper(ord.batch_no) <= upper('"+batchNo2+"')";	
		}else if(batchNo!=null && !"".equals(batchNo)
				&& (batchNo2==null || "".equals(batchNo2)) ){
			sql +="  and ord.batch_no like '"+batchNo+"%' ";	
		}
		
		if (pline_name!=null && !"".equals(pline_name) && !pline_name.equals("全部")) {
			if(pline_name.equals("高线")){
				sql +=" and ord.PLINE_NAME in ('高线','二线厂')";
			}else{
				sql +=" and ord.PLINE_NAME like '"+pline_name+"%'";
			}
		}
		
		if(!"".equals(design_key)){
			sql +=" and lower(ord.DESIGN_KEY) like lower('"+design_key+"%')";
		}
		if(!"".equals(psc_desc)){
			sql +=" and lower(ord.PSC_DESC) like lower('"+psc_desc+"%')";
		}
		if(!"".equals(materialNo)){
			sql +=" and lower(d.material_no) like lower('"+materialNo+"%')";
		}
		if (startTime!=null && !"".equals(startTime) && endTime !=null && !"".equals(endTime)) {
			if(dateTimeName.equals("判定时间")){
				sql +=" and ord.JUDGE_TIME>=to_date(concat('"+startTime+"',' 00:00:00'),'yyyy-MM-dd HH24:mi:ss')";
				sql +=" and ord.JUDGE_TIME<=to_date(concat('"+endTime+"',' 23:59:59'),'yyyy-MM-dd HH24:mi:ss') ";
			}else if(dateTimeName.equals("发送时间")){
				sql +=" and d.send_time>=to_date(concat('"+startTime+"',' 00:00:00'),'yyyy-MM-dd HH24:mi:ss')";
				sql +=" and d.send_time<=to_date(concat('"+endTime+"',' 23:59:59'),'yyyy-MM-dd HH24:mi:ss') ";
			}else{
				sql +=" and ord.CREATE_TIME>=to_date(concat('"+startTime+"',' 00:00:00'),'yyyy-MM-dd HH24:mi:ss')";
				sql +=" and ord.CREATE_TIME<=to_date(concat('"+endTime+"',' 23:59:59'),'yyyy-MM-dd HH24:mi:ss') ";
			}
		}

		sql +=" order by  ord.BATCH_NO desc";
		cro=this.getDao("testDao").ExcuteQuery(sql.toString());		
		
		return cro;
	}
	
	
	/**
	 * 查询材质检验项目信息
	 * @param parmas
	 * @return
	 * @throws SQLException 
	 */
/*	public CoreReturnObject getQltySampleItem(String smp_no) throws SQLException {
		try{
		StringBuffer sqlucomm = new StringBuffer();
		sqlucomm.append("SELECT SPECIMEN_NO,SEQ,SMP_NO,PHY_NAME_L,PHY_CODE_L,PHY_CODE_M,PHY_NAME_M,");
		sqlucomm.append("PHY_CODE_S,PHY_NAME_S,ITEM_CODE_D,ITEM_NAME_D,ITEM_CODE_T,ITEM_NAME_T,ITEM_CODE_S,ITEM_NAME_S,");
		sqlucomm.append("ISJUDGE,JUDGE_BASIS,PHY_UNIT,GROUP_SEQ,QUOTE_SPECIMEN_NO,QUOTE_SEQ,MEMO,TEST_QTY,");
		sqlucomm.append(" CREATE_NAME,CREATE_TIME FROM QCM_JHY_SAMPLE_CONSIGN_D_ITEM WHERE SMP_NO='"+smp_no+"'");
		ResultSet results=this.getDao("KgDao").ExceuteQueryForResultSet(sqlucomm.toString());
		JSONArray jsonArray=QuerryQltySample.resultSetToJsonArry(results);
		results.close();
		if (jsonArray!=null && jsonArray.size()>0){
			for (int i = 0; i < jsonArray.size(); i++) {
				JSONObject result = (JSONObject)jsonArray.get(i);
				sqlucomm.setLength(0);
				sqlucomm.append("SELECT R.VAL1,R.VAL2,R.VAL3,R.AVG_VAL,R.MAX_VAL,R.MIN_VAL,R.ITEM_CODE_L,R.ITEM_DESC_L from QCM_JHY_INSP_PHYSICS R where R.SPECIMEN_NO='"+result.get("SPECIMEN_NO")+"' ");
				sqlucomm.append(" AND R.PHY_CODE_L='"+result.get("PHY_CODE_L")+"' AND R.PHY_CODE_M='"+result.get("PHY_CODE_M")+"' AND R.PHY_CODE_S='"+result.get("PHY_CODE_S")+"'");
				if (result.getString("ITEM_CODE_D")==null && !"".equals(result.getString("ITEM_CODE_D"))) {
					sqlucomm.append(" AND R.ITEM_CODE_D='"+result.get("ITEM_CODE_D")+"' ");
	
				}
				if (result.getString("ITEM_CODE_T")==null && !"".equals(result.getString("ITEM_CODE_T"))) {
					sqlucomm.append(" AND R.ITEM_CODE_T='"+result.get("ITEM_CODE_T")+"' ");				
				}
				if (result.getString("ITEM_CODE_S")==null && !"".equals(result.getString("ITEM_CODE_S"))) {
					sqlucomm.append(" AND R.ITEM_CODE_S='"+result.get("ITEM_CODE_S")+"' ");
				}
	              ResultSet quoteResults=this.getDao("KgDao").ExceuteQueryForResultSet(sqlucomm.toString());//QUOTE_SPECIMEN_NO  QUOTE_SEQ
	  			  JSONArray jsonArray2=QuerryQltySample.resultSetToJsonArry(quoteResults);
	  			  quoteResults.close();
	              if (jsonArray2!=null && jsonArray2.size()>0){
						JSONObject jsonQuote=(JSONObject)jsonArray2.get(0);
						
						result.put("VAL1", jsonQuote.get("VAL1"));
						result.put("VAL2", jsonQuote.get("VAL2"));
						result.put("VAL3", jsonQuote.get("VAL3"));
						
						result.put("AVG_VAL", jsonQuote.get("AVG_VAL"));
						result.put("MAX_VAL", jsonQuote.get("MAX_VAL"));
						result.put("MIN_VAL", jsonQuote.get("MIN_VAL"));
						result.put("ITEM_CODE_L", jsonQuote.get("ITEM_CODE_L"));
						result.put("ITEM_DESC_L", jsonQuote.get("ITEM_DESC_L"));
					}
			}	
			cro.setResult(jsonArray);
		}
		
		
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return cro;	
	}*/
	

	//String pline_code
	public CoreReturnObject getQltySampleItem(String smp_no) throws SQLException {	
		
		String sql =" SELECT d.PROCESS_NOS 热处理号,t.SPECIMEN_NO,  "
	+"       t.SEQ,  "
	+"       t.SMP_NO,  "
	+"       t.PHY_NAME_L,  "
	+"       t.PHY_CODE_L,  "
	+"       t.PHY_CODE_M,  "
	+"       t.PHY_NAME_M,  "
	+"       t.PHY_CODE_S,  "
	+"       t.PHY_NAME_S,  "
	+"       t.ITEM_CODE_D,  "
	+"       t.ITEM_NAME_D,  "
	+"       t.ITEM_CODE_T,  "
	+"       t.ITEM_NAME_T,  "
	+"       t.ITEM_CODE_S,  "
	+"       t.ITEM_NAME_S,  "
	+"       t.ISJUDGE,  "
	+"       t.JUDGE_BASIS,  "
	+"       t.PHY_UNIT,  "
	+"       t.GROUP_SEQ,  "
	+"       t.QUOTE_SPECIMEN_NO,  "
	+"       t.QUOTE_SEQ,  "
	+"       t.MEMO,  "
	+"       t.TEST_QTY,  "
	+"       t.CREATE_NAME,  "
	+"       to_char(t.CREATE_TIME, 'yyyy-MM-dd hh24:mi:ss') CREATE_TIME,  "
	+"       r.VAL1,  "
	+"       r.VAL2,  "
	+"       r.VAL3,  "
	+"       r.AVG_VAL,  "
	+"       r.MAX_VAL,  "
	+"       r.MIN_VAL,  "
	+"       r.ITEM_CODE_L,  "
	+"       r.ITEM_DESC_L,  "
	+"       (select max(material_no)  "
	+"          from qcm_jhy_sample_consign_d a  "
	+"           where a.specimen_no = t.specimen_no) 取样产品序号,  "
	+"       decode(t.FY_QUOTE_SPECIMEN_NO,  "
	+"              null,  "
	 +"             (select decode(max(a.Smp_Type_Code), '1', '复样', '0', '初样')  "
	 +"                from qcm_jhy_sample_consign_d a  "  
	 +"               where a.specimen_no = t.specimen_no),  "
	 +"             '初样') FY_QUOTE_SPECIMEN_NO,  "
	 +"(select IS_PASS from QCM_JUDGE_PHYSICAL_RESULT q where  t.SPECIMEN_NO = q.Specimen_No  AND t.PHY_CODE_L = q.Phy_Code_l AND t.PHY_CODE_M = q.Phy_Code_m AND t.PHY_CODE_S = q.Phy_Code_s and rownum < = 1) IS_PASS"
	 +" FROM QCM_JHY_SAMPLE_CONSIGN_D_ITEM t, QCM_JHY_INSP_PHYSICS r,QCM_JHY_SAMPLE_CONSIGN_D d  "
	+" WHERE t.SPECIMEN_NO = r.Specimen_No(+)  "
	+"   and t.seq = r.seq(+)   and t.specimen_no = d.specimen_no "
	+"   AND t.PHY_CODE_L = r.Phy_Code_l(+)  "
	+"   AND t.PHY_CODE_M = r.Phy_Code_m(+)  "
	+"   AND t.PHY_CODE_S = r.Phy_Code_s(+)  "
	+"   and t.SMP_NO = '"+smp_no+"'  "
	+" order by t.SEQ asc ";
		cro=this.getDao("testDao").ExcuteQuery(sql.toString());
		
		
		return cro;
	}

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