package UIJ.UIJ03;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.support.rowset.SqlRowSet;

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

import UIB.COM.XmlSqlParsersFactory;

import CoreFS.SA01.CoreIComponent;
import CoreFS.SA06.CoreReturnObject;

/**
 * 发运计划查询
 * 
 * @author siy
 * @date 2010-9-7
 */
public class UIJ030020 extends CoreIComponent {

	/**
	 * 查询运输单位
	 * 
	 * @return CoreReturnObject
	 * @throws SQLException
	 */
	public CoreReturnObject queryTransUnits() throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		String sql = XmlSqlParsersFactory.getSql("UIJ030020_01.SELECT");

		ArrayList list = (ArrayList) this.getDao("KgDao")
				.ExcuteQueryReturnList(sql, new Object[] {});
		cro.setResult(list);

		return cro;
	}

	/**
	 * 查询发运计划下钢卷详细信息
	 * 
	 * @param dlivDirNo
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryTransCoils(String dlivDirNo)
			throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		String sql = XmlSqlParsersFactory.getSql("UIJ030020_05.SELECT");

		ArrayList list = (ArrayList) this.getDao("KgDao")
				.ExcuteQueryReturnList(sql, new Object[] { dlivDirNo });
		cro.setResult(list);

		return cro;
	}

	/**
	 * 查询排车计划
	 * 
	 * @param shipProgCd
	 *            发货指示状态
	 * @param fromDate
	 *            查询开始时间
	 * @param toDate
	 *            查询结束时间
	 * @param shipDirNo
	 *            发货指示号
	 * @param curProgCd
	 *            物料进度
	 * @param shipCompNm
	 *            运输单位
	 * @param dlivTp
	 *            运输方式
	 * @param laneTp
	 *            火车入库道次
	 * @param enteranceSeq
	 *            装车次数
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryTransPlan(String shipProgCd, String fromDate,
			String toDate, String shipDirNo, String curProgCd,
			String shipCompNm, String dlivTp, String laneTp, String enteranceSeq)
			throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		String sql = XmlSqlParsersFactory.getSql("UIJ030020_02.SELECT");
		cro = this.getDao("KgDao").ExcuteQuery(
				sql,
				new Object[] { shipProgCd, fromDate, toDate, shipDirNo,
						curProgCd, fromDate, toDate, dlivTp, laneTp,
						enteranceSeq, shipCompNm, dlivTp, });
		return cro;
	}

	/**
	 * 查询待装车钢卷
	 * 
	 * @param dlivTp
	 * @return
	 */
	public CoreReturnObject queryShipDir(String dlivTp) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		String sql = XmlSqlParsersFactory.getSql("UIJ030020_03.SELECT");
		cro = this.getDao("KgDao").ExcuteQuery(sql, new Object[] { dlivTp });
		return cro;
	}

	/**
	 * 查询下达排车计划时排车计划对应的钢卷信息
	 * 
	 * @param dlivNo
	 * @return
	 */
	public CoreReturnObject queryPlanCoil(String dlivNo) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		String sql = XmlSqlParsersFactory.getSql("UIJ030020_04.SELECT");

		cro = this.getDao("KgDao").ExcuteQuery(sql, new Object[] { dlivNo });
		return cro;
	}

	/**
	 * 待装车取消
	 * 
	 * @param list
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject returnSHIPDIRCAR(ArrayList<String[]> list)
			throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		Map result = this.getDao("KgDao").CoreBeginTranscation();
		try {
			String[] param = null;
			for (int i = 0; i < list.size(); i++) {
				param = (String[]) list.get(i);
				// 取消放入运输表的车厢号和排车顺序号，不能改变运输表的状态
				String sql = XmlSqlParsersFactory.getSql("UIJ030020_01.UPDATE");
				this.getDao("KgDao").ExcuteNonQuery(sql,
						new Object[] { param[0] });
				// 取消钢卷公共表中的车辆号，排车顺序号
				sql = XmlSqlParsersFactory.getSql("UIJ030020_02.UPDATE");
				this.getDao("KgDao").ExcuteNonQuery(sql,
						new Object[] { param[0] });
				// 更新发货表状态标志
				sql = XmlSqlParsersFactory.getSql("UIJ030020_03.UPDATE");
				this.getDao("KgDao").ExcuteNonQuery(sql,
						new Object[] { param[1] });
			}

			this.getDao("KgDao").CoreCommit(result);
		} catch (Exception ex) {
			this.getDao("KgDao").CoreRollBack(result);
			cro.setV_errCode(-1);
			cro.setV_errMsg("取消待装车失败！");
		}
		return cro;
	}
	public CoreReturnObject saveSHIPDIRCAR(ArrayList<String[]> list)
			throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		Connection conn = null;
		PreparedStatement pSta = null;
		try {
			conn = this.getDao("KgDao").getConnection();
			conn.setAutoCommit(false);
			StringBuffer sqlBuffer = null;
			for (int i = 0; i < list.size(); i++) {
				
				// 判断是否全部选择的标志位
				boolean flag = true;
				String ysr="",url="";
                String[] param = list.get(i);
                
               // 查询钢卷公共表中的SHIP_PROG_CD状态
				sqlBuffer = new StringBuffer();
				sqlBuffer
				     .append("select a.SHIP_PROG_CD from C_TBL02_COIL_COMM a  where a.ship_dirno = '"
						  + param[3] + "'\n");
				Statement sta = conn.createStatement();
				ResultSet rs = sta.executeQuery(sqlBuffer.toString());
				while (rs.next()) {
					String shipProgCd = rs.getString("SHIP_PROG_CD");
					if (!"03".equals(shipProgCd)) {
						flag = false;
						cro.setV_errCode(-1);
						cro.setV_errMsg("指示号"+param[2]+"已装车或明细已打印，请检查相应钢卷信息，再进行操作！！！");
						break;
					}
				}
				rs.close();
				sta.close();
				if (flag) {
                // 把车厢号和排车顺序号放入运输表，不改变运输表的状态
				sqlBuffer = new StringBuffer();
				sqlBuffer.append(" update tbj01_dliv_dir\n");
				sqlBuffer.append(" set \n");
				sqlBuffer.append(" ALLOC_SEQ = ?,\n");
				sqlBuffer.append(" TRANS_CAR_NO = ? \n");
				sqlBuffer.append(" where DLIV_DIRNO = ?\n");
				pSta = conn.prepareStatement(sqlBuffer.toString());
				pSta.setString(1, param[0]);
				pSta.setString(2, param[1]);
				pSta.setString(3, param[2]);
				pSta.executeUpdate();
				pSta.close();
				// 更新钢卷公共表中的车辆号，排车顺序号
				sqlBuffer = new StringBuffer();
				sqlBuffer.append(" update C_TBL02_COIL_COMM\n");
				sqlBuffer.append(" set \n");
				sqlBuffer.append(" TRAIN_STEP_NO = ?,\n");
				sqlBuffer.append(" TRANS_CAR_NO = ?,\n");
				sqlBuffer.append(" SHIP_PROG_CD = '04'\n");
				sqlBuffer.append(" where DLIV_DIRNO =?\n");
				pSta = conn.prepareStatement(sqlBuffer.toString());
				pSta.setString(1, param[0]);
				pSta.setString(2, param[1]);
				pSta.setString(3, param[2]);
				pSta.executeUpdate();
				pSta.close();
				// 更新发货表发货状态
				sqlBuffer = new StringBuffer();
				sqlBuffer.append(" update tbj01_ship_dir\n");
				sqlBuffer.append(" set \n");
				sqlBuffer.append(" ship_prog_cd = '04' \n");
				sqlBuffer.append(" where ship_dirno =?\n");
				pSta = conn.prepareStatement(sqlBuffer.toString());
				pSta.setString(1, param[3]);
				pSta.executeUpdate();
				pSta.close();
				}
			}
			conn.commit();
		} catch (SQLException ex) {
			if(conn != null){
				conn.rollback();
			}
			cro.setV_errCode(-1);
			cro.setV_errMsg("装车失败，请检查钢卷信息后重新操作！");
		} finally{
			if(conn != null && !conn.isClosed()){
				conn.close();
			}
		}

		return cro;
	}
	/**
	 * 汽车排车及装车指示，运送指示状态不由下车改变，下车只读运送指示表。发货指示状态由3改为4，不改变物料进度状态'SFB'(运送待机)
	 * 这里需要判断同一发货指示下的运输指示是否全部发完，没有发完的情况下是不能改变发货指示状态的。
	 * 
	 * @param list
	 * @return
	 * @throws SQLException
	 */
	/*public CoreReturnObject saveSHIPDIRCAR(ArrayList<String[]> list)
			throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		StringBuffer sqlBuffer = null;
		Map result = this.getDao("KgDao").CoreBeginTranscation();
		try {
			for (int i = 0; i < list.size(); i++) {
				// 判断是否全部选择的标志位
				boolean flag = true;
				String[] param = list.get(i);--20230818注释*/
	
				/*司机身份核验           ---------------------------------               */
				/*String ysr="",url="";
				 sqlBuffer = new StringBuffer();
	        		sqlBuffer
	        				.append("SELECT nvl(t1.ysr,'0') ysr from tba01_ord_line t, sel_test t1 " +
	        		    			"where t.DEST_PCD = t1.stationcode and t.ORD_NO='"+param[4]+"' and t.ORD_SEQ='"+param[5]+"' and rownum=1");
	        		ArrayList xslist = (ArrayList) this.getDao("KgDao")
	        				.ExcuteQueryReturnList(sqlBuffer.toString(),
	        						new String[] {});
	        		for (int j = 0; j < xslist.size(); j++) {
	        			Map map1 = (Map) xslist.get(j);
	        			ysr = map1.get("ysr").toString();	        			
	        		}
	        		if("0".equals(ysr)) 
	        		{	
	        			UIJ030052 uij030052 = new UIJ030052();
	        			url="http://172.16.0.49/xg56/webservice/webWaybill/queryIsChecked/"+param[2];
	        			String retr=uij030052.jsonPost(url, param[2]);
	        			//retr="["+retr+"]";
	        			JSONObject jsonObject1 =JSONObject.parseObject(retr);
	        			JSONObject jsonObj = new JSONObject(jsonObject1);
	        		        String RET_CODE = jsonObj.getString("RET_CODE");
	        		        if("0".equals(RET_CODE))
	        		        {
	        		        	cro.setV_errCode(new Integer(-1));
	        					cro.setV_errMsg("司机身份未核对！");
	        					return cro;
	        		        }
	        	
	        		}
	        		*/
	        		/*司机身份核验             ------------------------------------             */
				
				
				
				// 查询钢卷公共表中的SHIP_PROG_CD状态
	//20280818注释
				/*sqlBuffer = new StringBuffer();
				sqlBuffer
						.append("select a.SHIP_PROG_CD from C_TBL02_COIL_COMM a  where a.ship_dirno = '"
								+ param[3] + "'\n");
				ArrayList shipList = (ArrayList) this.getDao("KgDao")
						.ExcuteQueryReturnList(sqlBuffer.toString(),
								new String[] {});
				for (int j = 0; j < shipList.size(); j++) {
					Map map = (Map) shipList.get(j);
					String shipProgCd = map.get("SHIP_PROG_CD").toString();
					if (!"03".equals(shipProgCd)) {
						flag = false;
						cro.setV_errCode(-1);
						cro.setV_errMsg("请检查:指示号" + param[2]
								+ "下相应钢卷信息，再做操作！！！！！");
						break;
					}
				}
				if (flag) {
					// 把车厢号和排车顺序号放入运输表，不改变运输表的状态
					sqlBuffer = new StringBuffer();
					sqlBuffer.append(" update tbj01_dliv_dir\n");
					sqlBuffer.append(" set \n");
					sqlBuffer.append(" ALLOC_SEQ = ?,\n");
					sqlBuffer.append(" TRANS_CAR_NO = ? \n");
					sqlBuffer.append(" where DLIV_DIRNO = ?\n");
					this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
							new Object[] { param[0], param[1], param[2] });
					// 更新钢卷公共表中的车辆号，排车顺序号
					sqlBuffer = new StringBuffer();
					sqlBuffer.append(" update C_TBL02_COIL_COMM\n");
					sqlBuffer.append(" set \n");
					sqlBuffer.append(" TRAIN_STEP_NO = ?,\n");
					sqlBuffer.append(" TRANS_CAR_NO = ?,\n");
					sqlBuffer.append(" SHIP_PROG_CD = '04'\n");
					sqlBuffer.append(" where DLIV_DIRNO =?\n");
					this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
							new Object[] { param[0], param[1], param[2] });
					// 更新发货表发货状态
					sqlBuffer = new StringBuffer();
					sqlBuffer.append(" update tbj01_ship_dir\n");
					sqlBuffer.append(" set \n");
					sqlBuffer.append(" ship_prog_cd = '04' \n");
					sqlBuffer.append(" where ship_dirno =?\n");
					this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
							new Object[] { param[3] });
				}
			}
			this.getDao("KgDao").CoreCommit(result);
		} catch (Exception ex) {
			this.getDao("KgDao").CoreRollBack(result);
			cro.setV_errCode(-1);
			cro.setV_errMsg("汽车排车失败！");
		}
		return cro;
	}*/

	 /** 20131107
	   * 火车排车及装车指示，运送指示状态不由下车改变，下车只读运送指示表。发货指示状态由3改为4，不改变物料进度状态'SFB'(运送待机)
	   * 这里需要判断同一发货指示下的运输指示是否全部发完，没有发完的情况下是不能改变发货指示状态的。
	   * 
	   * @param list
	   * @return
	   * @throws SQLException
	   */
	  public CoreReturnObject saveSHIPTrain(ArrayList<String[]> list)
	      throws SQLException {
	    CoreReturnObject cro = new CoreReturnObject();
	    StringBuffer sqlBuffer = null;
	    Map result = this.getDao("KgDao").CoreBeginTranscation();
	    try {
	      for (int i = 0; i < list.size(); i++) {
	        // 判断是否全部选择的标志位
	        boolean flag = true;
	        String[] param = list.get(i);
	        // 查询钢卷公共表中的SHIP_PROG_CD状态
	        sqlBuffer = new StringBuffer();
	        sqlBuffer.append("SELECT  A.SHIP_PROG_CD FROM C_TBL02_COIL_COMM A  WHERE  A.SHIP_DIRNO = '"
	                + param[5] + "'\n");
	        ArrayList shipList = (ArrayList) this.getDao("KgDao")
	            .ExcuteQueryReturnList(sqlBuffer.toString(),
	                new String[] {});
	        for (int j = 0; j < shipList.size(); j++) {
	          Map map = (Map) shipList.get(j);
	          String shipProgCd = map.get("SHIP_PROG_CD").toString();
	          if (!"03".equals(shipProgCd)) {
	            flag = false;
	            cro.setV_errCode(-1);
	            cro.setV_errMsg("请检查:指示号" + param[4]
	                + "下相应钢卷信息，再做操作！！！！！");
	            break;
	          }
	        }
	        if (flag) {
	          // 把车厢号和排车顺序号放入运输表，不改变运输表的状态
	          sqlBuffer = new StringBuffer();
	          sqlBuffer.append(" UPDATE TBJ01_DLIV_DIR\n");
	          sqlBuffer.append(" SET \n");
	          sqlBuffer.append(" ALLOC_SEQ = ?,\n");
	          sqlBuffer.append(" TRANS_CAR_NO = ? ,\n");
	          sqlBuffer.append(" LANE_TP = ?,\n");
	          sqlBuffer.append(" ENTERANCE_SEQ = ?\n");
	          sqlBuffer.append(" WHERE DLIV_DIRNO = ?\n");
	          this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
	              new Object[] { param[0], param[1], param[2] ,param[3],param[4]});
	          // 更新钢卷公共表中的车辆号，排车顺序号
	          sqlBuffer = new StringBuffer();
	          sqlBuffer.append(" UPDATE C_TBL02_COIL_COMM\n");
	            sqlBuffer.append(" SET  \n");
	            sqlBuffer.append(" TRAIN_STEP_NO = ?,\n");
	            sqlBuffer.append(" TRANS_CAR_NO = ?,\n");
	            sqlBuffer.append(" SHIP_PROG_CD = '04'\n");
	            sqlBuffer.append(" WHERE DLIV_DIRNO =?\n");
	          this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
	              new Object[] { param[0], param[1], param[4] });
	          // 更新发货表发货状态
	          sqlBuffer = new StringBuffer();
	          sqlBuffer.append(" UPDATE TBJ01_SHIP_DIR \n");
	          sqlBuffer.append(" SET \n");
	          sqlBuffer.append(" SHIP_PROG_CD = '04' \n");
	          sqlBuffer.append(" WHERE SHIP_DIRNO=?\n");
	          this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
	              new Object[] { param[5] });
	        }
	      }
	      this.getDao("KgDao").CoreCommit(result);
	    } catch (Exception ex) {
	      this.getDao("KgDao").CoreRollBack(result);
	      cro.setV_errCode(-1);
	      cro.setV_errMsg("火车排车失败！");
	    }
	    return cro;
	  }
	  
	  
	public CoreReturnObject GetCarNo(String dlivno,String ordno,String ordseq)
      throws SQLException {
    CoreReturnObject cro = new CoreReturnObject();
    StringBuffer sqlBuffer = null;
   
    String url,ysr = null;
    Map<String, Object> map = new HashMap<String, Object>();
    try {
     
    	
    	sqlBuffer = new StringBuffer();
		sqlBuffer
				.append("SELECT nvl(t1.ysr,'0') ysr from tba01_ord_line t, sel_test t1 " +
		    			"where t.DEST_PCD = t1.stationcode and t.ORD_NO='"+ordno+"' and t.ORD_SEQ='"+ordseq+"' and rownum=1");
		ArrayList xslist = (ArrayList) this.getDao("KgDao")
				.ExcuteQueryReturnList(sqlBuffer.toString(),
						new String[] {});
		for (int j = 0; j < xslist.size(); j++) {
			Map map1 = (Map) xslist.get(j);
			ysr = map1.get("ysr").toString();
			
		}
		if(!"0".equals(ysr)) 
		{	
	
			UIJ030052 uij030052 = new UIJ030052();
			url="http://172.16.0.49/xg56/webservice/webWaybill/queryIsChecked/"+dlivno;
			String retr= uij030052.jsonPost(url, dlivno);
			JSONObject jsonObject1 =JSONObject.parseObject(retr);
		//	retr="["+retr+"]";
			JSONObject jsonObj = new JSONObject(jsonObject1);
	        String  RET_CODE = jsonObj.getString("RET_CODE");
	        cro.setResult(jsonObj);
		}
		else
			return cro;
      
    } catch (Exception ex) {
      
      cro.setV_errCode(-1);
      cro.setV_errMsg("排车失败！");
    }
    return cro;
  }
	/**
	 * 确认排车操作
	 * 
	 * @param regId
	 * @param list
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject saveDLIVDIRCAR(String regId, String regOrder,
			String regGroup, ArrayList<String[]> list) throws SQLException {

		CoreReturnObject cro = new CoreReturnObject();
		Connection conn = null;
		PreparedStatement pSta = null;
		ResultSet rs = null;
		String[] param = null;
		ArrayList<String> dlivDirNoList = new ArrayList<String>();
		String sql = "";

		try {
			conn = this.getDao("KgDao").getConnection();
			conn.setAutoCommit(false);
			// 根据运输指示号的个数来生成发货清单号
			for (int i = 0; i < list.size(); i++) {
				param = list.get(i);
				boolean flag = false;// 运输号是否重复标识
				for (int j = 0; j < dlivDirNoList.size(); j++) {
					if (param[2].equals(dlivDirNoList.get(j))) {
						flag = true;
						break;
					}
				}
				dlivDirNoList.add(param[2]);
				// 生成发货清单号
				if (!flag) {
					sql = XmlSqlParsersFactory.getSql("UIJ030020_04.UPDATE");
					pSta = conn.prepareStatement(sql);
					pSta.setString(1, param[2]);
					pSta.executeUpdate();
					pSta.close();
				}

				// 将发货清单号更新到钢卷公共表
				sql = "SELECT A.INV_NO FROM TBJ01_DLIV_DIR  A WHERE A.DLIV_DIRNO = '"
						+ param[2] + "'";
				pSta = conn.prepareStatement(sql);
				rs = pSta.executeQuery();
				if (rs.next()) {
					String invNo = rs.getString("INV_NO");
					sql = XmlSqlParsersFactory.getSql("UIJ030020_05.UPDATE");
					PreparedStatement pStac = conn.prepareStatement(sql);
					pStac.setString(1, invNo);
					pStac.setString(2, param[2]);
					pStac.executeUpdate();
					pStac.close();
				}
				// 20131011
				rs.close();
				pSta.close();
				
				
				// 插入移垛记录
				StringBuffer sqlBuffer = new StringBuffer();
				sqlBuffer.append("INSERT INTO C_TBK08_COIL_MOVE (ROLL_SEQ\n");
				sqlBuffer
						.append(",CUR_LOAD_LOC_F,CUR_LOAD_LOC_T,COIL_NO,REG_ID\n");
				sqlBuffer
						.append(",REG_SHIFT,REG_GROUP,REG_DTIME,REG_USE_DTIME,MOVE_TYPE)\n");
				sqlBuffer
						.append("VALUES((SELECT MAX(ROLL_SEQ)+1 ROLL_SEQ FROM C_TBK08_COIL_MOVE)\n");
				sqlBuffer
						.append(",?,?,?,?,?,?,TO_CHAR(SYSDATE,'YYYYMMDD'),TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),'12')\n");
				PreparedStatement pStaI = conn.prepareStatement(sqlBuffer
						.toString());
				pStaI.setString(1, param[7]);// 垛位
				pStaI.setString(2, "");
				pStaI.setString(3, param[4]);// 钢卷号
				pStaI.setString(4, regId);
				pStaI.setString(5, regOrder);
				pStaI.setString(6, regGroup);
				pStaI.executeUpdate();
				pStaI.close();

				// 根据钢卷号更新车厢号和排车顺序
				sql = XmlSqlParsersFactory.getSql("UIJ030020_06.UPDATE");
				pSta = conn.prepareStatement(sql);
				pSta.setString(1, param[1]);
				pSta.setString(2, param[0]);
				pSta.setString(3, param[2]);
				pSta.setString(4, param[3]);
				pSta.setString(5, param[6]);
				pSta.setString(6, param[5]);
				pSta.executeUpdate();
				pSta.close();
				// 清除垛位表钢卷信息
				sql = XmlSqlParsersFactory.getSql("UIJ030020_07.UPDATE");
				pSta = conn.prepareStatement(sql);
				pSta.setString(1, param[5]);
				pSta.executeUpdate();
				pSta.close();

				flag = true;
				// 查询钢卷发运状态
				sql = "select a.SHIP_PROG_CD from C_TBL02_COIL_COMM a  where a.ship_dirno = '"
						+ param[3] + "'";
				pSta = conn.prepareStatement(sql);
				rs = pSta.executeQuery();
				while (rs.next()) {
					String tmpShipProgCd = rs.getString("SHIP_PROG_CD");
					if ("03".equals(tmpShipProgCd)
							|| "04".equals(tmpShipProgCd)) {
						flag = false;
						break;
					}
				}
				rs.close();
				pSta.close();

				if (flag) {
					// 更新发运表发运状态
					sql = XmlSqlParsersFactory.getSql("UIJ030020_08.UPDATE");
					pSta = conn.prepareStatement(sql);
					pSta.setString(1, param[3]);
					pSta.executeUpdate();
					pSta.close();

					if ("YY".equals(createCretNo(conn, param[3]))) {
						sql = "select coil_no,ord_no,ord_seq,bef_prog_cd,cur_prog_Cd,act_wgt from c_tbl02_coil_comm where dliv_dirno ='"
								+ param[2] + "'";
						pSta = conn.prepareStatement(sql);
						rs = pSta.executeQuery();
						while (rs.next()) {
							String coilNo = rs.getString("COIL_NO");
							String ordNo = rs.getString("ORD_NO");
							String ordSeq = rs.getString("ORD_SEQ");
							String befProgCd = rs.getString("BEF_PROG_CD");
							String curProgCd = rs.getString("CUR_PROG_CD");
							String actWgt = rs.getString("ACT_WGT");
							SAVE_ORD_STS("", "", ordNo, ordSeq, befProgCd,
									curProgCd, "CR", coilNo, actWgt,
									"UIJ030020", regId);
						}
						// 20131011
						rs.close();
						pSta.close();
						ORD_STS_MAIN("UIJ030020", regId);

						//conn.commit();
					} else {
						// 回滚
						conn.rollback();
						// 这里出现的异常情况是板坯号与钢卷号不匹配
						cro.setV_errCode(-2);
						cro.setV_errMsg("开具质保书失败，请联系管理员！");
					}
				}
			}
			conn.commit();
		} catch (Exception ex) {
			if (conn != null) {
				conn.rollback();
			}
			cro.setV_errCode(-1);
			cro.setV_errMsg("排车确认失败！");
		} finally {
			try{
				//添加了出现异常时最终关闭rs、pSta
				if (rs != null) {
					rs.close();
				}
				if (pSta != null) {
					pSta.close();
				}
				if (conn != null && !conn.isClosed()) {
					conn.close();
				}
			} catch (Exception e) {
				// TODO: handle exception
			}
		}

		return cro;
	}

	/**
	 * 订单进程标记
	 * 
	 * @param P_BEF_ORD_NO
	 *            前合同号
	 * @param P_BEF_ORD_SEQ
	 *            前订单号
	 * @param P_ORD_NO
	 *            合同号
	 * @param P_ORD_SEQ
	 *            订单号
	 * @param P_BEF_PROG_CD
	 *            前进程状态
	 * @param P_PROG_CD
	 *            进程状态
	 * @param P_MAT_TYPE
	 *            物料类型
	 * @param P_MAT_NO
	 *            物料号
	 * @param P_WGT
	 *            物料重量
	 * @param P_REG_PRGM
	 *            程序名
	 * @param P_REG_ID
	 *            操作人
	 * @return
	 */
	private void SAVE_ORD_STS(String P_BEF_ORD_NO, String P_BEF_ORD_SEQ,
			String P_ORD_NO, String P_ORD_SEQ, String P_BEF_PROG_CD,
			String P_PROG_CD, String P_MAT_TYPE, String P_MAT_NO, String P_WGT,
			String P_REG_PRGM, String P_REG_ID) throws SQLException {
		this.getDao("KgDao").ExcuteProcedure(
				XmlSqlParsersFactory.getSql("UIJ030020_03.CALL"),
				new String[] { P_BEF_ORD_NO, P_BEF_ORD_SEQ, P_ORD_NO,
						P_ORD_SEQ, P_BEF_PROG_CD, P_PROG_CD, P_MAT_TYPE,
						P_MAT_NO, P_WGT, P_REG_PRGM, P_REG_ID }, new String[0]);
	}

	/**
	 * 订单进程整理 regId 操作人
	 * 
	 * @return
	 */
	private boolean ORD_STS_MAIN(String pgmId, String regId)
			throws SQLException {
		boolean flag = true;
		String retMsg = "";
		this.getDao("KgDao").ExcuteProcedure(
				XmlSqlParsersFactory.getSql("UIJ030020_02.CALL"),
				new String[] { pgmId, regId }, new String[] { retMsg });
		if (retMsg != null && !retMsg.equals("")) {
			flag = false;
		}
		return flag;
	}

	/**
	 * 生成质保书号
	 * 
	 * @param shipDlivNo
	 * @return
	 * @throws SQLException
	 */
	private String createCretNo(Connection conn, String shipDlivNo)
			throws SQLException {
		String retMsg = "";
		String sql = XmlSqlParsersFactory.getSql("UIJ030020_01.CALL");
		CallableStatement cstm = conn.prepareCall(sql);
		cstm.setString(1, shipDlivNo);
		cstm.registerOutParameter(2, java.sql.Types.VARCHAR);
		cstm.registerOutParameter(3, java.sql.Types.VARCHAR);
		cstm.execute();
		String cretNo = cstm.getString(2);
		retMsg = cstm.getString(3);
		cstm.close();
		return retMsg;
	}

	/**
	 * 查询同订单下可发钢卷
	 * 
	 * @param ordNo
	 * @param ordSeq
	 * @param coilNo
	 * @param thkMin
	 * @param thkMax
	 * @param wthMin
	 * @param wthMax
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject querySameCoils(String ordNo, String ordSeq,
			String coilNo, String thkMin, String thkMax, String wthMin,
			String wthMax) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		String sql = XmlSqlParsersFactory.getSql("UIJ030020_06.SELECT");

		cro = this.getDao("KgDao").ExcuteQuery(
				sql,
				new Object[] { ordNo, ordSeq, coilNo, thkMin, thkMax, wthMin,
						wthMax });
		return cro;
	}

	/**
	 * 钢卷替换
	 * 
	 * @param oldCoils
	 * @param newCoils
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject replaceCoils(ArrayList oldCoils, ArrayList newCoils)
			throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		Map result = this.getDao("KgDao").CoreBeginTranscation();
		try {
			// 将替换的钢卷加入发运计划
			String sql = XmlSqlParsersFactory.getSql("UIJ030020_09.UPDATE");
			Object[] params = new Object[] { toSqlStr(oldCoils),
					toSqlStr(oldCoils), toSqlStr(oldCoils), toSqlStr(oldCoils),
					toSqlStr(oldCoils), toSqlStr(oldCoils), toSqlStr(oldCoils),
					toSqlStr(newCoils) };
			int i = 0;
			while (sql.indexOf("?") >= 0 && i < params.length) {
				sql = replaceFirst(sql, "?", params[i++].toString());
			}
			this.getDao("KgDao").ExcuteNonQuery(sql);
			// 将需要替换的钢卷取消发运计划
			sql = XmlSqlParsersFactory.getSql("UIJ030020_10.UPDATE");
			params = new Object[] { toSqlStr(oldCoils) };
			i = 0;
			while (sql.indexOf("?") >= 0 && i < params.length) {
				sql = replaceFirst(sql, "?", params[i++].toString());
			}
			this.getDao("KgDao").ExcuteNonQuery(sql);
			this.getDao("KgDao").CoreCommit(result);
		} catch (Exception ex) {
			this.getDao("KgDao").CoreRollBack(result);
			cro.setV_errCode(-1);
			cro.setV_errMsg("钢卷替换失败！");
		}
		return cro;
	}

	private String toSqlStr(ArrayList coils) {
		String sqlStr = "";

		for (int i = 0; i < coils.size(); i++) {
			sqlStr = "'" + coils.get(i) + "',";
		}
		sqlStr += "''";
		return sqlStr;
	}

	static public String replaceFirst(String str, String oldStr, String newStr) {
		int i = str.indexOf(oldStr);
		if (i == -1)
			return str;
		str = str.substring(0, i) + newStr + str.substring(i + oldStr.length());
		return str;
	}
}