XinYu_LZMES/.svn/pristine/45/4533a12bd3ca5e5ace4a3953c23bb68ccb8786b4.svn-base

package QCM.JHY01.JHY0103;

import CoreFS.SA01.CoreIComponent;
import CoreFS.SA06.CoreReturnObject;
import QCM.COMMUNAL.QCM02.SqlJoint;
import QCM.COMMUNAL.VO.QcmJudgeOperateLog;
import QCM.JHY01.VO.LimsDeclare;
import QCM.JHY01.VO.listItem;

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

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.ibatis.session.SqlSession;

import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 第三方认证申请接口
 * @author freedom
 *
 */
public class AddThirdInfo extends CoreIComponent{
	private static Log logger	=	LogFactory.getLog(AddThirdInfo.class);
	CoreReturnObject cro = new CoreReturnObject();	

	SqlSession SqlSession = 
			QCM.COMMUNAL.SqlSessionBuilder.openSqlSession();
	QCM.COMMUNAL.OrderSqMapper mapper = SqlSession.getMapper(QCM.COMMUNAL.OrderSqMapper.class);
	
	private String getMaxNum(String strDate) throws Exception {
		ResultSet rs=null;
		String count=null;
		Integer cnume = 0;
		StringBuffer sqlucomm = new StringBuffer();
		sqlucomm.append("SELECT max(to_number(substr(CERT_NO,-3))) CERT_NO FROM QCM_MAT_CERT_M WHERE CERT_NO like '"+strDate+"%'");
		
		try {
			rs=this.getDao("testDao").ExceuteQueryForResultSet(sqlucomm.toString());
			if (rs.next()) {
				count=rs.getString("CERT_NO");
				if (count==null || count.equals("")) {
					count="0";
				}
				cnume = Integer.parseInt(count);
			}
			cnume++;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception();
		}
		return strDate+String.format("%03d",cnume);
	}
	
	
	
	//第三方认证申请接口2
	public CoreReturnObject addList(ArrayList<String> cert_inst_code,ArrayList<String> cert_inst_name,ArrayList<String> memo, ArrayList<String> create_id,ArrayList<String> create_name,
			ArrayList<String> cert_no,ArrayList<String> cert_status,ArrayList<String> apply_org_no,ArrayList<String> apply_org_desc,ArrayList<String> dep_type_code,ArrayList<String> duty_id,ArrayList<String> instance_id,
			ArrayList<String> material_no,ArrayList<String> design_key,ArrayList<String> psc,ArrayList<String> prod_code,ArrayList<String> prod_name,ArrayList<String> steel_code,
			ArrayList<String> steel_name,ArrayList<String> std_code,ArrayList<String> std_name,ArrayList<String> delivery_state_code,ArrayList<String> delivery_state_desc,
			ArrayList<String> delivery_date,ArrayList<String> cut_type,ArrayList<String> heat_no,ArrayList<String> batch_no,
			ArrayList<String> inspection_lot,ArrayList<String> board_no,ArrayList<String> thick,ArrayList<String> width,ArrayList<String> length,ArrayList<String> mat_weight,
			ArrayList<String> pline_code,ArrayList<String> pline_name,String userId,String userName) {
		
		StringBuffer sqlucomm=null;
		StringBuffer sqlucomm2=null;
		CoreReturnObject cro = new CoreReturnObject();	

		try {
		Date date = new Date();
		String strDate="SQ-"+(new SimpleDateFormat("yyMMdd")).format(date); 
		String cret_no = getMaxNum(strDate);
		
		    sqlucomm=new StringBuffer();
		    sqlucomm.append("INSERT " +
				"INTO QCM_MAT_CERT_M" +
				"(CERT_NO,CERT_INST_CODE,CERT_INST_NAME,CERT_STATUS,MEMO,CREATE_ID,CREATE_NAME,CREATE_TIME,SEND_FLAG) "+
				"VALUES('"+cret_no+"',?,?,'00',?,?,?,sysdate,'0') ");
			Object[] obj = new Object[]{cert_inst_code.get(0),cert_inst_name.get(0),memo.get(0),userId,userName};
			this.getDao("testDao").ExcuteNonQuery(sqlucomm.toString(),obj);
			
			for (int i = 0; i < cert_inst_code.size(); i++) {
			    sqlucomm2=new StringBuffer();
			    sqlucomm2.append("INSERT INTO QCM_MAT_CERT_D (CERT_NO,MATERIAL_NO,DESIGN_KEY,");
			    sqlucomm2.append("PSC,PROD_CODE,PROD_NAME,STEEL_CODE,STEEL_NAME,STD_CODE,STD_NAME,");
			    sqlucomm2.append("DELIVERY_STATE_CODE,DELIVERY_STATE_DESC,DELIVERY_DATE,CUT_TYPE,");
			    sqlucomm2.append("HEAT_NO,BATCH_NO,INSPECTION_LOT,BOARD_NO,THICK,WIDTH,LENGTH,MAT_WEIGHT,PLINE_CODE,PLINE_NAME) VALUES ('");
			    sqlucomm2.append(cret_no+"',?,?,");
			    sqlucomm2.append("?,?,?,?,?,?,?, ?,?,?,?, ?,?,?,?,?,?,?,?,?,?)");
				Object[] obj2 = new Object[]{material_no.get(i),design_key.get(i),psc.get(i),prod_code.get(i),prod_name.get(i),steel_code.get(i),steel_name.get(i),std_code.get(i),std_name.get(i),
						delivery_state_code.get(i),delivery_state_desc.get(i),delivery_date.get(i),cut_type.get(i),
						heat_no.get(i),batch_no.get(i),inspection_lot.get(i),board_no.get(i),thick.get(i),width.get(i),length.get(i),mat_weight.get(i),
						pline_code.get(i),pline_name.get(i)};
				this.getDao("testDao").ExcuteNonQuery(sqlucomm2.toString(),obj2);
				
			}
			} catch (Exception e) {
				e.printStackTrace();
				System.out.println(e.getMessage());
				cro.setV_errCode(-1);
				cro.setV_errMsg("认证申报失败!"+e.getMessage());
				return cro;
			}

		cro.setV_errCode(1);
		cro.setV_errMsg("认证申报成功");
		return cro;
	}
	
	
	
	/**
     * 第三方认证申请接口2
     */
    public CoreReturnObject addList2(String jsonArray,String userId,String userName) {
    	StringBuffer sqlucomm=null;
    	StringBuffer sqlucomm2=null;
		CoreReturnObject cro = new CoreReturnObject();	
		PreparedStatement ps = null;
		PreparedStatement ps2 = null;
		Connection cn = this.getDao("testDao").getConnection();
		String certInstCode = "";
		
		try {
			TreeSet<String> treeSet = new TreeSet<String>();
			JSONArray tarArr=JSON.parseArray(jsonArray);
			
			if (tarArr.size()== 0) {
				cro.setV_errCode(-1);
				cro.setV_errMsg("请勾选申请数据!");
				return cro;
			}
			
			for (Object list : tarArr) {
				JSONObject map = (JSONObject) list;
				// 获取认证机构代码
				certInstCode = map.getString("CERT_INST_CODE");
				treeSet.add(certInstCode);
			}
			if (treeSet.size() != 1) {
				cro.setV_errCode(-1);
				cro.setV_errMsg("请勾选同一个认证机构！！！");
				return cro;	
			}
	    	Date date = new Date();
			String strDate="SQ-"+(new SimpleDateFormat("yyMMdd")).format(date); 
			String cret_no = getMaxNum(strDate);
			
		    sqlucomm=new StringBuffer();
		    sqlucomm.append("INSERT " +
				"INTO QCM_MAT_CERT_M" +
				"(CERT_NO,CERT_INST_CODE,CERT_INST_NAME,CERT_STATUS,MEMO,CREATE_ID,CREATE_NAME,CREATE_TIME,SEND_FLAG) "+
				"VALUES('"+cret_no+"',?,?,'50',?,'"+userId+"','"+userName+"',sysdate,'0') ");
		       ps = cn.prepareStatement(sqlucomm.toString());
		       
		   	for (int i = 0; i < tarArr.size(); i++) {
				 JSONObject jsonObj = tarArr.getJSONObject(i);
				 FillPhyItem(ps, jsonObj);
				 /*ps.addBatch();*/
				 ps.execute();
				 break;
			}
		   	
		   	
		   	sqlucomm2=new StringBuffer();
		   	sqlucomm2.append(" INSERT INTO QCM_MAT_CERT_D (CERT_NO,MATERIAL_NO,DESIGN_KEY,");
		    sqlucomm2.append("PSC,PROD_CODE,PROD_NAME,STEEL_CODE,STEEL_NAME,STD_CODE,STD_NAME,");
		    sqlucomm2.append("DELIVERY_STATE_CODE,DELIVERY_STATE_DESC,DELIVERY_DATE,CUT_TYPE,");
		    sqlucomm2.append("HEAT_NO,BATCH_NO,INSPECTION_LOT,BOARD_NO,THICK,WIDTH,LENGTH,MAT_WEIGHT,PLINE_CODE,PLINE_NAME, ");
		    sqlucomm2.append(" APPROACH,INCEPTCORPCODE,BUYERCODE,CONTRACT_NO,PSC_DESC,VESSEL,CREATE_NAME,CREATE_TIME,MAT_WEIGHT2,VALIDFLAG)");
		    sqlucomm2.append("VALUES ('");
		    sqlucomm2.append(cret_no+"',?,?,");
		    sqlucomm2.append("?,?,?,?,?,?,?, ?,?,?,?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'"+userName+"',sysdate,?,?)");
		    ps2 = cn.prepareStatement(sqlucomm2.toString());
		   	for (int i = 0; i < tarArr.size(); i++) {
				 JSONObject jsonObj = tarArr.getJSONObject(i);
				 
				 //查询有没有当前子板的数据
				 String sqlmno="SELECT * FROM QCM_MAT_CERT_D T WHERE T.MATERIAL_NO = '"+jsonObj.getString("MATERIAL_NO")+"' "
				 		+ " and t.STEEL_CODE = '"+jsonObj.getString("STEEL_CODE")+"' ";
				 List<HashMap> hmList = mapper.query(sqlmno);
				 if(hmList!=null && hmList.size()>=1){
					 String sqlupdate="update QCM_MAT_CERT_D t set t.VALIDFLAG = '0' where t.MATERIAL_NO = '"+jsonObj.getString("MATERIAL_NO")+"'"
					 		+ " and t.STEEL_CODE = '"+jsonObj.getString("STEEL_CODE")+"'  ";
				  	 this.getDao("testDao").ExcuteNonQuery(sqlupdate.toString());
				 }
				 
				 //查询订单下的牌号
				 //查询有没有当前子板的数据
				 String sqlmno2="SELECT * FROM QCM_MAT_CERT_D T WHERE T.MATERIAL_NO = '"+jsonObj.getString("MATERIAL_NO")+"'  "
							+"  and t.STEEL_CODE NOT IN ( select t.STEEL_CODE from QCM_ORD_DESIGN_SAMPLE_M t  "
							+" 	where t.design_key = '"+jsonObj.getString("DESIGN_KEY")+"' group by t.steel_code) and t.VALIDFLAG is null ";
				 List<HashMap> hmList2 = mapper.query(sqlmno2);
				 if(hmList2!=null && hmList2.size()>=1){
					 for (int z = 0; z < hmList2.size(); z++) {
					 String sqlupdate="update QCM_MAT_CERT_D t set t.VALIDFLAG = '0' where t.MATERIAL_NO = '"+hmList2.get(z).get("MATERIAL_NO")+"'"
					 		+ " and t.STEEL_CODE = '"+hmList2.get(z).get("STEEL_CODE")+"'  ";
				  	 this.getDao("testDao").ExcuteNonQuery(sqlupdate.toString());
					 }
				 }
				 
				 //查询订单下的牌号
				 String sqlmno3=" select t.STEEL_CODE from QCM_ORD_DESIGN_SAMPLE_M t  "
							+" 	where t.design_key = '"+jsonObj.getString("DESIGN_KEY")+"' AND t.STEEL_CODE =  '"+jsonObj.getString("STEEL_CODE")+"' ";
				 List<HashMap> hmList3 = mapper.query(sqlmno3);
				 if(hmList3!=null && hmList3.size()>=1){
					 jsonObj.put("VALIDFLAG", "");
				 }else{
					 jsonObj.put("VALIDFLAG", "0");
				 }
				 
				 
				 FillPhyItem2(ps2, jsonObj);
				/* ps2.addBatch();*/
				 ps2.execute();
				 
				/*    String sql ="INSERT INTO QCM_MASS_PRODUCE (MATERIAL_NO) VALUES('"+jsonObj.getString("MATERIAL_NO")+"') ";
				    this.getDao("testDao").ExcuteNonQuery(sql.toString());*/
			}
		   	
			/*ps.executeBatch();
			ps.clearBatch();*/
			ps.close();
			
		/*	ps2.executeBatch();
			ps2.clearBatch();*/
			ps2.close();
			
			cn.commit();
			
			List<LimsDeclare> limsD= new ArrayList<LimsDeclare>();
			//调用lims接口
			for (Object list : tarArr) {
				String num="0";
				JSONObject map = (JSONObject) list;
				String DESIGN_KEY = map.getString("DESIGN_KEY");
				String BATCH_NO = map.getString("BATCH_NO");
				String STEEL_NAME = map.getString("STEEL_NAME");//产品牌号
				String CERT_INST_CODE = map.getString("CERT_INST_CODE");//认证机构代码
				
				String newItem=DESIGN_KEY+"-"+BATCH_NO;
				newItem=newItem+"-"+(STEEL_NAME!=null?STEEL_NAME:"null");
				newItem=newItem+"-"+(CERT_INST_CODE!=null?CERT_INST_CODE:"null");
				
				for (int j = 0; j < limsD.size(); j++) {
					LimsDeclare lis = limsD.get(j);
					String designKey = (String) lis.getDESIGN_KEY();
					String batchNo = (String) lis.getBATCH_NO();
					String steelName = (String) lis.getSTEEL_NAME();
					String certInst = (String) lis.getCERT_INST_CODE();
			
					String newItem2=designKey+"-"+batchNo;
					newItem2=newItem2+"-"+(steelName!=null?steelName:"null");
					newItem2=newItem2+"-"+(certInst!=null?certInst:"null");
					
					//判断这条数据有没有给lims发送
					if(newItem.equals(newItem2)){
						num = "1";//1 代表已经给Lims那边发送过
						break;
					}
				}
				
			if(num.equals("0")){
				LimsDeclare lde = new LimsDeclare();
				lde.setDESIGN_KEY(DESIGN_KEY);
				lde.setBATCH_NO(BATCH_NO);
				lde.setSTEEL_NAME(STEEL_NAME);
				lde.setCERT_INST_CODE(CERT_INST_CODE);
				limsD.add(lde);
				
				JSONArray tar= new JSONArray();
				tar.add(map);//json 转 Array
				String tara=tar.toString();
				
				//一条一条传给Lims
				String limsRet = httpUrlPostLims(tara);//调用url
				
			}
				
			}
			for (Object list : tarArr) {
				JSONObject map = (JSONObject) list;
				QcmJudgeOperateLog qjog = new QcmJudgeOperateLog();
				qjog.setOperate_name(userName);
				qjog.setBatchno(map.get("BATCH_NO") == null ?"":map.get("BATCH_NO").toString());
				if(certInstCode.equals("IC011") || certInstCode.equals("IC012") || certInstCode.equals("IC013") || certInstCode.equals("IC014") || 
				  certInstCode.equals("IC015") || certInstCode.equals("IC016") || certInstCode.equals("IC017") || certInstCode.equals("IC018") || 
				  certInstCode.equals("IC019") || certInstCode.equals("IC020") || certInstCode.equals("IC021") || certInstCode.equals("IC022") || 
				  certInstCode.equals("IC023") || certInstCode.equals("IC024") || certInstCode.equals("IC026") ){
					
				    qjog.setOperate_type("第三方申报");
				}else{
					qjog.setOperate_type("船检申报");
				}
				qjog.setParams(map.get("MATERIAL_NO") == null ?"":map.get("MATERIAL_NO").toString());
				if(map.get("PLINE_CODE").toString().contains("ZB1")){
					qjog.setProd_line("ZB1");
				}else if(map.get("PLINE_CODE").toString().contains("HB1")){
					qjog.setProd_line("HB1");
				}
				
				mapper.insertQcmJudgeOperateLog(qjog);	 				 
			 }
			SqlSession.commit();
			cro.setV_errCode(0);
			cro.setV_errMsg("操作成功!");
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			cro.setV_errCode(-1);
			cro.setV_errMsg("认证失败!"+e.getMessage());
			e.printStackTrace();
			SqlSession.rollback();
		}finally {
			SqlSession.close();
		}
		
		return cro;	
		}
	
	   void FillPhyItem(PreparedStatement pSta1 , JSONObject qlty) throws Exception{
		   pSta1.setString(1,qlty.getString("CERT_INST_CODE"));
			pSta1.setString(2, qlty.getString("CERT_INST_NAME"));
			pSta1.setString(3, qlty.getString("MEMO"));
		}
	
	   void FillPhyItem2(PreparedStatement pSta1 , JSONObject qlty) throws Exception{
		   pSta1.setString(1,qlty.getString("MATERIAL_NO"));
		   pSta1.setString(2, qlty.getString("DESIGN_KEY"));
		   pSta1.setString(3, qlty.getString("PSC"));
		   pSta1.setString(4, qlty.getString("PROD_CODE"));
		   pSta1.setString(5, qlty.getString("PROD_NAME"));
		   pSta1.setString(6, qlty.getString("STEEL_CODE"));
		   pSta1.setString(7, qlty.getString("STEEL_NAME"));
		   pSta1.setString(8, qlty.getString("STD_CODE"));
		   pSta1.setString(9, qlty.getString("STD_NAME"));
		   pSta1.setString(10, qlty.getString("DELIVERY_STATE_CODE"));
		   pSta1.setString(11, qlty.getString("DELIVERY_STATE_DESC"));
		   pSta1.setString(12, qlty.getString("DELIVERY_DATE"));
		   pSta1.setString(13, qlty.getString("CUT_TYPE"));
		   pSta1.setString(14, qlty.getString("HEAT_NO"));
		   pSta1.setString(15, qlty.getString("BATCH_NO"));
		   pSta1.setString(16, qlty.getString("INSPECTION_LOT"));
		   pSta1.setString(17, qlty.getString("BOARD_NO"));
		   pSta1.setString(18, qlty.getString("THICK"));
		   pSta1.setString(19, qlty.getString("WIDTH"));
		   pSta1.setString(20, qlty.getString("LENGTH"));
		   pSta1.setString(21, qlty.getString("MAT_WEIGHT"));
		   pSta1.setString(22, qlty.getString("PLINE_CODE"));
		   pSta1.setString(23, qlty.getString("PLINE_NAME"));
		   
		   pSta1.setString(24, qlty.getString("APPROACH"));
		   pSta1.setString(25, qlty.getString("INCEPTCORPCODE"));
		   pSta1.setString(26, qlty.getString("BUYERCODE"));
		   pSta1.setString(27, qlty.getString("CONTRACTNO"));
		   pSta1.setString(28, qlty.getString("PSC_DESC"));
		   pSta1.setString(29, qlty.getString("VESSEL"));
		   pSta1.setString(30, qlty.getString("MAT_WEIGHT"));
		   pSta1.setString(31, qlty.getString("VALIDFLAG"));
		}
	
	
		 
		 /**
		     * 发送Http Post请求 第三方认证申报  调用lims接口
		 * @throws Exception */
		    public String httpUrlPostLims(String data) throws Exception {
		        HttpURLConnection connection = null;
		        PrintWriter pw = null;
		        BufferedReader reader = null;
		        String line = "";
		        StringBuilder result = new StringBuilder();
		        try {
		        	String methodUrl ="http://172.16.0.169:82/Limsinterface/http/Boatorder";
		            URL url = new URL(methodUrl);
		            connection = (HttpURLConnection)url.openConnection();// 根据URL生成HttpURLConnection
		            connection.setDoOutput(true);// 设置是否向connection输出，因为这个是post请求，参数要放在http正文内，因此需要设为true,默认情况下是false
		            connection.setDoInput(true); // 设置是否从connection读入，默认情况下是true;
		            connection.setRequestMethod("POST");// 设置请求方式为post,默认GET请求
		            connection.setUseCaches(false);// post请求不能使用缓存设为false
		            connection.setConnectTimeout(10000);// 连接主机的超时时间
		            connection.setReadTimeout(10000);// 从主机读取数据的超时时间
		            connection.setInstanceFollowRedirects(true);// 设置该HttpURLConnection实例是否自动执行重定向
		            connection.setRequestProperty("accept", "*/*");
		            connection.setRequestProperty("connection", "Keep-Alive");// 连接复用
		            connection.setRequestProperty("charset", "utf-8");//utf-8 GBK
		            connection.setRequestProperty("Content-Type", "application/x-www-form-urlencoded");
		            connection.setRequestProperty("user-agent","Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1;SV1)");
		            connection.connect();// 建立TCP连接,getOutputStream会隐含的进行connect,所以此处可以不要
		            
		            OutputStream outputstream = connection.getOutputStream();
		            String jsonStr = "data="+data;
		            
		            outputstream.write(jsonStr.getBytes("UTF-8"));
		            outputstream.flush();
		            outputstream.close();

		            if (connection.getResponseCode() == HttpURLConnection.HTTP_OK) {
		                reader = new BufferedReader(new InputStreamReader(connection.getInputStream(), "UTF-8"));// 发送http请求
		                // 循环读取流
		                while (null != (line = reader.readLine())) {
		                    result.append(line);
		                }
		                reader.close();
		            }
		        } catch (IOException e) {
		            e.printStackTrace();
		           // throw new Exception("发送LIMS系统失败，具体原因为["+e.getMessage()+"]");
		        } finally {
		            connection.disconnect();
		        }
		        return result.toString();
		    }


	/**
	 * 发送Http Post请求  调用一体化接口
	 *
	 * @throws Exception
	 */
	public String httpUrlPostYTH(String data, String methodUrl) throws Exception {
		HttpURLConnection connection = null;
		PrintWriter pw = null;
		BufferedReader reader = null;
		String line = "";
		StringBuilder result = new StringBuilder();
		try {
			URL url = new URL(methodUrl);
			connection = (HttpURLConnection) url.openConnection();// 根据URL生成HttpURLConnection
			connection.setDoOutput(true);// 设置是否向connection输出，因为这个是post请求，参数要放在http正文内，因此需要设为true,默认情况下是false
			connection.setDoInput(true); // 设置是否从connection读入，默认情况下是true;
			connection.setRequestMethod("POST");// 设置请求方式为post,默认GET请求
			connection.setUseCaches(false);// post请求不能使用缓存设为false
			connection.setConnectTimeout(10000);// 连接主机的超时时间
			connection.setReadTimeout(10000);// 从主机读取数据的超时时间
			connection.setInstanceFollowRedirects(true);// 设置该HttpURLConnection实例是否自动执行重定向
			connection.setRequestProperty("accept", "*/*");
			connection.setRequestProperty("connection", "Keep-Alive");// 连接复用
			connection.setRequestProperty("charset", "utf-8");//utf-8 GBK
			connection.setRequestProperty("Content-Type", "application/x-www-form-urlencoded");
			connection.setRequestProperty("user-agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1;SV1)");
			connection.connect();// 建立TCP连接,getOutputStream会隐含的进行connect,所以此处可以不要

			OutputStream outputstream = connection.getOutputStream();
			String jsonStr = "data=" + data;

			outputstream.write(jsonStr.getBytes("UTF-8"));
			outputstream.flush();
			outputstream.close();

			if (connection.getResponseCode() == HttpURLConnection.HTTP_OK) {
				reader = new BufferedReader(new InputStreamReader(connection.getInputStream(), "UTF-8"));// 发送http请求
				// 循环读取流
				while (null != (line = reader.readLine())) {
					result.append(line);
				}
				reader.close();
			}
		} catch (IOException e) {
			e.printStackTrace();
			logger.error("调用一体化接口失败,url:" + methodUrl + "，json数据：" + data + "，具体原因为[" + e.getMessage() + "]");
		} finally {
			connection.disconnect();
		}
		return result.toString();
	}


	/**
	 * 调用一体化接口 船板质保书开具清单接口
	 *
	 * @throws Exception
	 */
	public void sendYTHWarrantyDetail() throws Exception {
		SqlSession SqlSession = QCM.COMMUNAL.SqlSessionBuilder.openSqlSession();
		QCM.COMMUNAL.OrderSqMapper mapper = SqlSession.getMapper(QCM.COMMUNAL.OrderSqMapper.class);
		//综合判定合格，第三方申报未发送一体化的数据
		try {
			String url = "http://172.16.4.136:14008/tcpdi/getMESTestRlt?queueid=setMesControlNo";
			/*String result = httpUrlPostYTH("", url);
			if (!SqlJoint.IsNullOrSpace(result)) {
				JSONObject jo = JSONObject.parseObject(result);
				if (jo != null && jo.get("status") != null && jo.get("status").toString().equals("200")) {
				} else {
					logger.error("调用一体化船板质保书开具清单接口失败具体原因为[" + result + "]");
				}
			}*/

			String sql = "select t1.material_no, t2.cert_inst_code, '' control_no\n" +
					"  from qcm_mat_cert_d t1, qcm_mat_cert_m t2, qcm_judge_coil_result t3\n" +
					" where t1.cert_no = t2.cert_no(+)\n" +
					"   and t1.material_no = t3.coil_no(+)\n" +
					"   and t1.send_flag_yth = '1'\n" +
					"   and t3.judgeresult = '1'\n" +
					"   and t1.material_no is not null\n" +
					"   and t2.cert_inst_code is not null\n" +
					"union\n" +
					"select t1.material_no, t2.cert_inst_code, '' control_no\n" +
					"  from qcm_mat_cert_d t1, qcm_mat_cert_m t2, zj_result_all@xgcx t3\n" +
					" where t1.cert_no = t2.cert_no(+)\n" +
					"   and t1.material_no = t3.billetid(+)\n" +
					"   and t1.send_flag_yth = '1'\n" +
					"   and t3.judgeresult = '1'\n" +
					"   and t1.material_no is not null\n" +
					"   and t2.cert_inst_code is not null";
			List<HashMap> hmList = mapper.query(sql);
			if (CollectionUtils.isNotEmpty(hmList)) {
				JSONArray ttmaps = new JSONArray();
				for (HashMap hashMap : hmList) {
					JSONObject jsobj1 = new JSONObject();
					jsobj1.put("PROD_NO", hashMap.get("MATERIAL_NO"));
					jsobj1.put("ORG_CODE", hashMap.get("CERT_INST_CODE"));
					jsobj1.put("CONTROL_NO", "");
					ttmaps.add(jsobj1);
				}
				String date = JSON.toJSONString(ttmaps);
				String result = httpUrlPostYTH(date, url);
				if (!SqlJoint.IsNullOrSpace(result)) {
					JSONObject jo = JSONObject.parseObject(result);
					if (jo != null && jo.get("status") != null && jo.get("status").toString().equals("200")) {
						//修改发送标识
						for (HashMap hashMap : hmList) {
							String sqlupdate = "update qcm_mat_cert_d t set t.send_flag_yth = '0' where t.material_no = '" + hashMap.get("MATERIAL_NO").toString() + "'";
							cro = this.getDao("testDao").ExcuteNonQuery(sqlupdate.toString());
						}
					} else {
						logger.error("调用一体化船板质保书开具清单接口失败具体原因为[" + result + "]");
					}
				}
			}

		} catch (Exception ex) {
			ex.printStackTrace();
			logger.error("调用一体化船板质保书开具清单接口失败具体原因为[" + ex.getMessage() + "]");
		}
		SqlSession.close();
	}

	/**
	 * 调用一体化接口 船板质保书取消接口
	 *
	 * @throws Exception
	 */
	public void sendCancelYTHWarrantyDetail() throws Exception {
		SqlSession SqlSession = QCM.COMMUNAL.SqlSessionBuilder.openSqlSession();
		QCM.COMMUNAL.OrderSqMapper mapper = SqlSession.getMapper(QCM.COMMUNAL.OrderSqMapper.class);
		//综合判定未判，第三方申报已发送一体化的数据
		try {
			String url = "http://172.16.4.136:14008/slpdi/csCancel";
			/*String result = httpUrlPostYTH("", url);
			if (!SqlJoint.IsNullOrSpace(result)) {
				JSONObject jo = JSONObject.parseObject(result);
				if (jo != null && jo.get("status") != null && jo.get("status").toString().equals("200")) {

				} else {
					logger.error("调用一体化船板质保书取消接口失败具体原因为[" + result + "]");
				}
			}*/
			String sql = "select t1.material_no, t2.cert_inst_code, '' control_no\n" +
					"  from qcm_mat_cert_d t1, qcm_mat_cert_m t2, qcm_judge_coil_result t3\n" +
					" where t1.cert_no = t2.cert_no(+)\n" +
					"   and t1.material_no = t3.coil_no(+)\n" +
					"   and t1.send_flag_yth = '0'\n" +
					"   and t3.judgeresult = '0'\n" +
					"   and t1.material_no is not null\n" +
					"   and t1.create_time > sysdate - 10 \n" +
					"union\n" +
					"select t1.material_no, t2.cert_inst_code, '' control_no\n" +
					"  from qcm_mat_cert_d t1, qcm_mat_cert_m t2, zj_result_all@xgcx t3\n" +
					" where t1.cert_no = t2.cert_no(+)\n" +
					"   and t1.material_no = t3.billetid(+)\n" +
					"   and t1.send_flag_yth = '0'\n" +
					"   and t3.judgeresult = '0'\n" +
					"   and t1.material_no is not null\n" +
					"   and t1.create_time > sysdate - 10 ";
			List<HashMap> hmList = mapper.query(sql);
			if (CollectionUtils.isNotEmpty(hmList)) {
				JSONArray ttmaps = new JSONArray();
				for (HashMap hashMap : hmList) {
					JSONObject jsobj1 = new JSONObject();
					jsobj1.put("PROD_NO", hashMap.get("MATERIAL_NO").toString());
					ttmaps.add(jsobj1);
				}
				String date = JSON.toJSONString(ttmaps);
				String result = httpUrlPostYTH(date, url);
				if (!SqlJoint.IsNullOrSpace(result)) {
					JSONObject jo = JSONObject.parseObject(result);
					if (jo != null && jo.get("status") != null && jo.get("status").toString().equals("200")) {
						//修改发送标识
						for (HashMap hashMap : hmList) {
							String sqlupdate = "update qcm_mat_cert_d t set t.send_flag_yth = '1' where t.material_no = '" + hashMap.get("MATERIAL_NO").toString() + "'";
							cro = this.getDao("testDao").ExcuteNonQuery(sqlupdate.toString());
						}
					} else {
						logger.error("调用一体化船板质保书开具清单接口失败具体原因为[" + result + "]");
					}
				}
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			logger.error("调用一体化船板质保书开具清单接口失败具体原因为[" + ex.getMessage() + "]");
		}
		SqlSession.close();
	}
	
	//第三方见证委托申报
	 public CoreReturnObject addMatWitneD(String jsonstr,String userId,String userName) {
		 try {
			 Connection cn = this.getDao("testDao").getConnection();
			 PreparedStatement ps = null;
				String[] ja=jsonstr.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 r.design_key,r.steel_code,r.steel_name,r.thick,t.heat_no,t.batch_no,t.material_no,t.smp_no,t.specimen_no, "
						+" t.inspection_lot,t.freq_code,t.freq_name,t.smp_type_code,t.smp_type_name,t.smp_location_code,t.smp_location, "
						+" t.board_no,t.pline_code,t.pline_name,t.process_nos,t.phy_item,m.smp_catg,m.cert_inst_code,m.cert_inst_name, "
						+" r.psc,r.psc_desc,r.delivery_state_code,r.delivery_state_desc,r.width,r.length,r.msc_pline,r.process_code,r.prod_code, "
						+" r.prod_name,r.std_code,r.std_name,r.three_memo,t.memo,t.improve_memo "
						+"   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 t.specimen_no in ("+msInfo+") ";
				List<HashMap> lists=mapper.query(sql.toString());
				if (lists==null || lists.size()<1) {
					cro.setV_errCode(-1);
					cro.setV_errMsg("待申报的见证委托数据已经不存在，请核实数据!");
					return cro;
				}
				UUID uuid = UUID.randomUUID();
				StringBuffer sqlucomm=new StringBuffer();
			    sqlucomm.append("insert into qcm_mat_witne_d "+
				  "(design_key, steel_code, steel_name, thick, heat_no, batch_no, material_no, smp_no, specimen_no, inspection_lot, freq_code, freq_name,"+ 
				  " smp_type_code, smp_type_name, smp_location_code, smp_location, board_no, pline_code, pline_name, process_nos, phy_item, smp_catg,"+
				  "  cert_inst_code, cert_inst_name, psc, psc_desc, delivery_state_code, delivery_state_desc, width, length, msc_pline, process_code,"+
				  "   prod_code, prod_name, std_code, std_name, three_memo, memo, improve_memo, create_id, create_name,GUID)"+
				  " values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, "+
				  " 	  ?,'"+userId+"', '"+userName+"','"+uuid+"') ");
			    ps = cn.prepareStatement(sqlucomm.toString());
			    
			  	for (int i = 0; i < lists.size(); i++) {
			  		HashMap obj=lists.get(i);
					 matWitneItem(ps, obj);
					 ps.execute();
					 
					 String sqlupdate="update QCM_JHY_SAMPLE_CONSIGN_D t set t.mat_witne = '1' where t.specimen_no = '"+obj.get("SPECIMEN_NO").toString()+"'";
				  		this.getDao("testDao").ExcuteNonQuery(sqlupdate.toString());
				}
			  	ps.close();
				cn.commit();
				
				
				for (int i = 0; i < lists.size(); i++) {
					HashMap obj=lists.get(i);
					JSONObject map = JSONObject.parseObject(JSON.toJSONString(obj));
					JSONArray tar= new JSONArray();
					tar.add(map);//json 转 Array
					String tara=tar.toString();
					String limsRet = httpUrlPostLims(tara);//调用url
				}
				
				for (int i = 0; i < lists.size(); i++) {
					HashMap obj=lists.get(i);
					QcmJudgeOperateLog qjog = new QcmJudgeOperateLog();
					qjog.setOperate_name(userName);
					qjog.setBatchno(obj.get("BATCH_NO").toString() == null ?"":obj.get("BATCH_NO").toString());			
					qjog.setOperate_type("第三方申报");
					qjog.setParams(obj.get("MATERIAL_NO").toString() == null ?"":obj.get("MATERIAL_NO").toString());
				    qjog.setProd_line(obj.get("PLINE_CODE").toString());
				    qjog.setMemo(obj.get("SPECIMEN_NO").toString());
					mapper.insertQcmJudgeOperateLog(qjog);	 				 
				 }
				SqlSession.commit();
				
		 } catch (Exception e) {
				e.printStackTrace();
				SqlSession.rollback();
				cro.setV_errCode(-1);
				cro.setV_errMsg("申报见证委托失败!"+e.getMessage());
				return cro;
			}finally {
				SqlSession.close();
			 }
			  
			return cro;
	 }
	 
	  void matWitneItem(PreparedStatement pSta1 , HashMap obj) throws Exception{
		   pSta1.setString(1, (String)obj.get("DESIGN_KEY"));
		   pSta1.setString(2, (String)obj.get("STEEL_CODE"));
		   pSta1.setString(3, (String)obj.get("STEEL_NAME"));
		   pSta1.setString(4, (String)obj.get("THICK"));
		   pSta1.setString(5, (String)obj.get("HEAT_NO"));
		   pSta1.setString(6, (String)obj.get("BATCH_NO"));
		   pSta1.setString(7, (String)obj.get("MATERIAL_NO"));
		   pSta1.setString(8, (String)obj.get("SMP_NO"));
		   pSta1.setString(9, (String)obj.get("SPECIMEN_NO"));
		   pSta1.setString(10, (String)obj.get("INSPECTION_LOT"));
		   pSta1.setString(11, (String)obj.get("FREQ_CODE"));
		   pSta1.setString(12, (String)obj.get("FREQ_NAME"));
		   pSta1.setString(13, (String)obj.get("SMP_TYPE_CODE"));
		   pSta1.setString(14, (String)obj.get("SMP_TYPE_NAME"));
		   pSta1.setString(15, (String)obj.get("SMP_LOCATION_CODE"));
		   pSta1.setString(16, (String)obj.get("SMP_LOCATION"));
		   pSta1.setString(17, (String)obj.get("BOARD_NO"));
		   pSta1.setString(18, (String)obj.get("PLINE_CODE"));
		   pSta1.setString(19, (String)obj.get("PLINE_NAME"));
		   pSta1.setString(20, (String)obj.get("PROCESS_NOS"));
		   pSta1.setString(21, (String)obj.get("PHY_ITEM"));
		   pSta1.setString(22, (String)obj.get("SMP_CATG"));
		   pSta1.setString(23, (String)obj.get("CERT_INST_CODE"));
		   pSta1.setString(24, (String)obj.get("CERT_INST_NAME"));
		   pSta1.setString(25, (String)obj.get("PSC"));
		   pSta1.setString(26, (String)obj.get("PSC_DESC"));
		   pSta1.setString(27, (String)obj.get("DELIVERY_STATE_CODE"));
		   pSta1.setString(28, (String)obj.get("DELIVERY_STATE_DESC"));
		   pSta1.setString(29, (String)obj.get("WIDTH"));
		   pSta1.setString(30, (String)obj.get("LENGTH"));
		   pSta1.setString(31, (String)obj.get("MSC_PLINE"));
		   pSta1.setString(32, (String)obj.get("PROCESS_CODE"));
		   pSta1.setString(33, (String)obj.get("PROD_CODE"));
		   pSta1.setString(34, (String)obj.get("PROD_NAME"));
		   pSta1.setString(35, (String)obj.get("STD_CODE"));
		   pSta1.setString(36, (String)obj.get("STD_NAME"));
		   pSta1.setString(37, (String)obj.get("THREE_MEMO"));
		   pSta1.setString(38, (String)obj.get("MEMO"));
		   pSta1.setString(39, (String)obj.get("IMPROVE_MEMO"));
		}
}