package UIM.UIM02;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.CallableStatement;
import java.sql.Connection;
import UIB.COM.XmlSqlParsersFactory;

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

/**
 * 重卷上料管理
 * 
 * @author siy
 * @date 2011-8-2
 */
public class UIM020050 extends CoreIComponent {
	/**
	 * 查询重卷作业计划
	 * 
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryReelPlan(String coilno) throws SQLException {
		String sql = XmlSqlParsersFactory.getSql("UIM020050_01.SELECT");
		return this.getDao("KgDao").ExcuteQuery(sql,new Object[] {coilno});
	}

	public CoreReturnObject queryAddClassInf() throws SQLException {
		String sql = XmlSqlParsersFactory.getSql("UIM020050_02.SELECT");
		return this.getDao("KgDao").ExcuteQuery(sql);
	}
	public CoreReturnObject queryReelPltcmPlan() throws SQLException {
		String sql = XmlSqlParsersFactory.getSql("UIM020050_04.SELECT");
		return this.getDao("KgDao").ExcuteQuery(sql);
	}

	public CoreReturnObject queryPltcmAddClassInf() throws SQLException {
		String sql = XmlSqlParsersFactory.getSql("UIM020050_05.SELECT");
		return this.getDao("KgDao").ExcuteQuery(sql);
	}
	/**
	 * 当前垛位上层是否存在钢卷
	 * 
	 * @param yardAddrs
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject hasCoilOnYardAddr(String yardAddrs)
			throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		int count = 0;
//		if (null != yardAddrs && yardAddrs.length() > 8) {
//			String clfName = yardAddrs.substring(0, 1);
//			String clfRow = yardAddrs.substring(2, 4);
//			String clfCol = yardAddrs.substring(6);
//			String clfFl = yardAddrs.substring(4, 5);
//
//			String yard1 = clfName
//					+ "-"
//					+ clfCol
//					+ String
//							.valueOf((char) ((int) (clfFl.toCharArray()[0]) + 1))
//					+ "-" + (Integer.parseInt(clfRow, 10) - 1);
//			String yard2 = clfName
//			+ "-"
//			+ clfCol
//			+ String
//					.valueOf((char) ((int) (clfFl.toCharArray()[0]) + 1))
//			+ "-" + (Integer.parseInt(clfRow, 10));
//
//			StringBuffer sqlBuffer = new StringBuffer();
//			sqlBuffer
//					.append("select count(coil_no) count from c_tbk08_coil_yard where clf_no = C_PKG_UIM.GET_FINALYARDNO_BYYARDFLAG(?) or clf_no = C_PKG_UIM.GET_FINALYARDNO_BYYARDFLAG(?)");
//
//			ResultSet rs = this.getDao("KgDao").ExceuteQueryForResultSet(
//					sqlBuffer.toString(), new Object[] { yardAddrs });
//
//			if (rs.next()) {
//				count = rs.getInt("COUNT");
//			}
//		}

		cro.setResult(count);
		return cro;
	}

	/**
	 * 重卷上料
	 * 
	 * @param coilNo
	 * @param l_coilNo
	 * @param millSeqCd
	 * @param trnfReg
	 * @param trnfShift
	 * @param trnfGroup
	 * @param trnfDTime
	 * @return
	 * @throws SQLException
	 * @throws SQLException
	 */
	public CoreReturnObject outStockByReel(String coilNo, String l_coilNo,
			String millSeqCd, String trnfReg, String trnfShift,
			String trnfGroup, String trnfDTime) throws SQLException// throws
	// SQLException
	{
		CoreReturnObject cro = new CoreReturnObject();

		Connection conn = null;
		PreparedStatement pSta1 = null;
		StringBuffer sqlBuffer = new StringBuffer();

		try {

			conn = this.getDao("KgDao").getConnection();
			conn.setAutoCommit(false);

			// 判断钢卷是否在轧制计划中
			String sql = "SELECT A.STATUS_CD FROM L_TBF03_SPEC_REEL A WHERE A.C_COIL_NO = '"
					+ coilNo + "' AND A.STATUS_CD = 'A'\n";

			PreparedStatement pSta = conn.prepareStatement(sql);
			ResultSet rs = pSta.executeQuery();

			if (rs.next()) {
				// 修改钢卷公共表钢卷状态，清除垛位，生成出库记录
				String befProgCd = "";
				String befProgCdTime = "";
				String befProgCdPgm = "";
				String V_ACT_WGT = "";
				String ordNo = "", ordSeq = "";
				String curLoadLoc = "";
				String qSql = "select cur_prog_cd,cur_prog_cd_dtime,cur_prog_cd_pgm,ACT_WGT,ord_no,ord_seq,cur_load_loc from c_tbc02_coil_comm where OLD_SAMPL_NO = '"
						+ coilNo + "'\n";

				pSta1 = conn.prepareStatement(qSql);
				ResultSet rs2 = pSta1.executeQuery();
				if (rs2.next()) {
					befProgCd = rs2.getString("CUR_PROG_CD");
					befProgCdTime = rs2.getString("CUR_PROG_CD_DTIME");
					befProgCdPgm = rs2.getString("CUR_PROG_CD_PGM");
					V_ACT_WGT = rs2.getString("ACT_WGT");
					ordNo = rs2.getString("ORD_NO");
					ordSeq = rs2.getString("ORD_SEQ");
					curLoadLoc = rs2.getString("CUR_LOAD_LOC");
				}
				rs2.close();
				pSta1.close();
				
				String sqlMove = "insert into c_tbk08_coil_move T(T.ROLL_SEQ,T.CUR_LOAD_LOC_F,T.CUR_LOAD_LOC_T,T.COIL_NO,T.REG_ID,T.REG_SHIFT,T.REG_GROUP,T.REG_DTIME,T.REG_USE_ID,T.REG_USE_DTIME,T.MOVE_TYPE)"
					+ "VALUES((SELECT MAX(ROLL_SEQ)+1 ROLL_SEQ FROM C_TBK08_COIL_MOVE),?,?,?,?,?,?,?,?,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),'11')";
				pSta1 = conn.prepareStatement(sqlMove);
				pSta1.setString(1, curLoadLoc);
				pSta1.setString(2, "");
				pSta1.setString(3, coilNo);
				pSta1.setString(4, trnfReg);
				pSta1.setString(5, trnfShift);
				pSta1.setString(6, trnfGroup);
				pSta1.setString(7, trnfDTime);
				pSta1.setString(8, trnfReg);
				pSta1.executeUpdate();
				try{
					pSta1.close();   //添加了关闭pSta1
				} catch (Exception e) {
				}

				sqlBuffer = new StringBuffer();
				sqlBuffer
						.append("update c_tbc02_coil_comm set coil_stat = '3',cur_prog_cd = 'DBC'\n");// 取消状态位
				sqlBuffer
						.append(" ,cur_prog_cd_dtime = to_char(sysdate,'YYYYMMDDHH24MISS')\n");
				sqlBuffer.append(" ,cur_prog_cd_pgm = 'UIM020050'\n");
				sqlBuffer.append(" ,bef_prog_cd = ?\n");
				sqlBuffer.append(" ,bef_prog_cd_dtime = ?\n");
				sqlBuffer.append(" ,bef_prog_cd_pgm = ?\n");
				sqlBuffer.append(" ,cur_load_loc = ''\n");
				sqlBuffer.append(" ,trnf_reg = ?\n");
				sqlBuffer.append(" ,trnf_shift=?\n");
				sqlBuffer.append(" ,trnf_group=?\n");
				sqlBuffer
						.append(" ,trnf_use_time=to_char(sysdate,'YYYYMMDDHH24MISS')\n");
				sqlBuffer.append(" ,trnf_dtime=?\n");
				sqlBuffer.append(" where old_sampl_no = ?\n");
				pSta1 = conn.prepareStatement(sqlBuffer.toString());
				pSta1.setString(1, befProgCd);
				pSta1.setString(2, befProgCdTime);
				pSta1.setString(3, befProgCdPgm);
				pSta1.setString(4, trnfReg);
				pSta1.setString(5, trnfShift);
				pSta1.setString(6, trnfGroup);
				pSta1.setString(7, trnfDTime);
				pSta1.setString(8, coilNo);
				pSta1.executeUpdate();
				pSta1.close();

				// 修改重卷计划表计划状态
				sqlBuffer = new StringBuffer();
				sqlBuffer.append("update l_tbf03_spec_reel \n");
				sqlBuffer.append(" set \n");
				sqlBuffer.append(" STATUS_CD = 'B' \n");
				sqlBuffer.append(" where \n");
				sqlBuffer.append(" c_coil_no = ?\n");
				sqlBuffer.append(" and STATUS_CD = 'A'\n");
				pSta1 = conn.prepareStatement(sqlBuffer.toString());
				pSta1.setString(1, coilNo);
				pSta1.executeUpdate();
				pSta1.close();

				// 清除垛位表钢卷
				String sql1 = "select CLF_NO from c_tbk08_coil_yard where coil_no = '"
						+ coilNo + "'\n";

				pSta1 = conn.prepareStatement(sql1);
				ResultSet rs3 = pSta1.executeQuery();
				if (rs3.next()) {
					String clfNo = rs3.getString("CLF_NO");
					sqlBuffer = new StringBuffer();
					sqlBuffer
							.append("update c_tbk08_coil_yard  set coil_no = '' where CLF_NO = ?");
					PreparedStatement pSta3 = conn.prepareStatement(sqlBuffer
							.toString());
					pSta3.setString(1, clfNo);
					pSta3.executeUpdate();
					pSta3.close();
				}
				rs3.close();
				pSta1.close();
				// 冷轧卷信息 插入成品库C_TBC02_COIL_COMM
				String ssql = "SELECT A.R_COIL_NO FROM L_TBF03_SPEC_REEL A WHERE A.C_COIL_NO ='"
						+ coilNo + "' and R_COIL_NO = '" + l_coilNo + "'";
				pSta1 = conn.prepareStatement(ssql);
				ResultSet rrs1 = pSta1.executeQuery();
				if (rrs1.next()) {
					String sqlQ = XmlSqlParsersFactory
							.getSql("UIM020050_03.SELECT");
					PreparedStatement pStaQ = conn.prepareStatement(sqlQ);
					pStaQ.setString(1, l_coilNo);
					ResultSet rsQ = pStaQ.executeQuery();
					if (!rsQ.next()) {
						// 插入钢卷成品表
						String sqlI = XmlSqlParsersFactory
								.getSql("UIM020050_01.INSERT");
						PreparedStatement pStaI = conn.prepareStatement(sqlI);
						pStaI.setString(1, coilNo);
						pStaI.executeUpdate();
						pStaI.close();
					} else {
						// 更新成品公共表
					}
					rsQ.close();
					pStaQ.close();

					// 删除钢卷成品_D表
					String sqlD = XmlSqlParsersFactory
							.getSql("UIM020050_01.DELETE");
					PreparedStatement pStaD = conn.prepareStatement(sqlD);
					pStaD.setString(1, l_coilNo);
					pStaD.executeUpdate();
					pStaD.close();
					// 插入钢卷成品_D表
					String sqlI = XmlSqlParsersFactory
							.getSql("UIM020050_02.INSERT");
					PreparedStatement pStaI = conn.prepareStatement(sqlI);
					pStaI.setString(1, coilNo);
					pStaI.executeUpdate();
					pStaI.close();

					// 订单标记
					this.SAVE_ORD_STS("", "", ordNo, ordSeq, befProgCd, "DBC",
							"CR", coilNo, V_ACT_WGT, "UIM020050", trnfReg);
					// 订单整理
					this.ORD_STS_MAIN("UIM020050", trnfReg);

				}
				rrs1.close();
				pSta1.close();

				conn.commit();
			} else {
				cro.setV_errCode(-1);
				cro.setV_errMsg("输入的钢卷号不在重卷计划中，请重新查询后再进行上料操作！");
			}

			rs.close();
			pSta.close();
		} 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;
	}
	public CoreReturnObject outStockBypltcmReel(String coilNo, String l_coilNo,
			String millSeqCd, String trnfReg, String trnfShift,
			String trnfGroup, String trnfDTime) throws SQLException// throws
	// SQLException
	{
		CoreReturnObject cro = new CoreReturnObject();

		Connection conn = null;
		PreparedStatement pSta1 = null;
		StringBuffer sqlBuffer = new StringBuffer();

		try {

			conn = this.getDao("KgDao").getConnection();
			conn.setAutoCommit(false);

			// 判断钢卷是否在轧制计划中
			String sql = "SELECT A.STATUS_CD FROM L_TBF03_SPEC_REEL A WHERE A.C_COIL_NO = '"
					+ coilNo + "' AND A.STATUS_CD = 'A'\n";

			PreparedStatement pSta = conn.prepareStatement(sql);
			ResultSet rs = pSta.executeQuery();

			if (rs.next()) {
				// 修改钢卷公共表钢卷状态，清除垛位，生成出库记录
				String befProgCd = "";
				String befProgCdTime = "";
				String befProgCdPgm = "";
				String V_ACT_WGT = "";
				String ordNo = "", ordSeq = "";
				String curLoadLoc = "";
				String qSql = "select cur_prog_cd,cur_prog_cd_dtime,cur_prog_cd_pgm,ACT_WGT,ord_no,ord_seq,cur_load_loc from c_tbl02_coil_comm where OLD_SAMPL_NO = '"
						+ coilNo + "'\n";

				pSta1 = conn.prepareStatement(qSql);
				ResultSet rs2 = pSta1.executeQuery();
				if (rs2.next()) {
					befProgCd = rs2.getString("CUR_PROG_CD");
					befProgCdTime = rs2.getString("CUR_PROG_CD_DTIME");
					befProgCdPgm = rs2.getString("CUR_PROG_CD_PGM");
					V_ACT_WGT = rs2.getString("ACT_WGT");
					ordNo = rs2.getString("ORD_NO");
					ordSeq = rs2.getString("ORD_SEQ");
					curLoadLoc = rs2.getString("CUR_LOAD_LOC");
				}
				rs2.close();
				pSta1.close();
				
				String sqlMove = "insert into c_tbk08_coil_move T(T.ROLL_SEQ,T.CUR_LOAD_LOC_F,T.CUR_LOAD_LOC_T,T.COIL_NO,T.REG_ID,T.REG_SHIFT,T.REG_GROUP,T.REG_DTIME,T.REG_USE_ID,T.REG_USE_DTIME,T.MOVE_TYPE)"
					+ "VALUES((SELECT MAX(ROLL_SEQ)+1 ROLL_SEQ FROM C_TBK08_COIL_MOVE),?,?,?,?,?,?,?,?,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),'11')";
				pSta1 = conn.prepareStatement(sqlMove);
				pSta1.setString(1, curLoadLoc);
				pSta1.setString(2, "");
				pSta1.setString(3, coilNo);
				pSta1.setString(4, trnfReg);
				pSta1.setString(5, trnfShift);
				pSta1.setString(6, trnfGroup);
				pSta1.setString(7, trnfDTime);
				pSta1.setString(8, trnfReg);
				pSta1.executeUpdate();
				try{
					pSta1.close();  //添加了关闭pSta1
				} catch (Exception e) {
				}
				sqlBuffer = new StringBuffer();
				sqlBuffer
						.append("update c_tbl02_coil_comm set coil_stat = '3',cur_prog_cd = 'DBC'\n");// 取消状态位
				sqlBuffer
						.append(" ,cur_prog_cd_dtime = to_char(sysdate,'YYYYMMDDHH24MISS')\n");
				sqlBuffer.append(" ,cur_prog_cd_pgm = 'UIM020050'\n");
				sqlBuffer.append(" ,bef_prog_cd = ?\n");
				sqlBuffer.append(" ,bef_prog_cd_dtime = ?\n");
				sqlBuffer.append(" ,bef_prog_cd_pgm = ?\n");
				sqlBuffer.append(" ,cur_load_loc = ''\n");
				sqlBuffer.append(" ,trnf_reg = ?\n");
				sqlBuffer.append(" ,trnf_shift=?\n");
				sqlBuffer.append(" ,trnf_group=?\n");
				sqlBuffer
						.append(" ,trnf_use_time=to_char(sysdate,'YYYYMMDDHH24MISS')\n");
				sqlBuffer.append(" ,trnf_dtime=?\n");
				sqlBuffer.append(" where old_sampl_no = ?\n");
				pSta1 = conn.prepareStatement(sqlBuffer.toString());
				pSta1.setString(1, befProgCd);
				pSta1.setString(2, befProgCdTime);
				pSta1.setString(3, befProgCdPgm);
				pSta1.setString(4, trnfReg);
				pSta1.setString(5, trnfShift);
				pSta1.setString(6, trnfGroup);
				pSta1.setString(7, trnfDTime);
				pSta1.setString(8, coilNo);
				pSta1.executeUpdate();
				pSta1.close();

				// 修改重卷计划表计划状态
				sqlBuffer = new StringBuffer();
				sqlBuffer.append("update l_tbf03_spec_reel \n");
				sqlBuffer.append(" set \n");
				sqlBuffer.append(" STATUS_CD = 'B' \n");
				sqlBuffer.append(" where \n");
				sqlBuffer.append(" c_coil_no = ?\n");
				sqlBuffer.append(" and STATUS_CD = 'A'\n");
				pSta1 = conn.prepareStatement(sqlBuffer.toString());
				pSta1.setString(1, coilNo);
				pSta1.executeUpdate();
				pSta1.close();

				// 清除垛位表钢卷
				String sql1 = "select CLF_NO from c_tbk08_coil_yard where coil_no = '"
						+ coilNo + "'\n";

				pSta1 = conn.prepareStatement(sql1);
				ResultSet rs3 = pSta1.executeQuery();
				if (rs3.next()) {
					String clfNo = rs3.getString("CLF_NO");
					sqlBuffer = new StringBuffer();
					sqlBuffer
							.append("update c_tbk08_coil_yard  set coil_no = '' where CLF_NO = ?");
					PreparedStatement pSta3 = conn.prepareStatement(sqlBuffer
							.toString());
					pSta3.setString(1, clfNo);
					pSta3.executeUpdate();
					pSta3.close();
				}
				rs3.close();
				pSta1.close();
				// 冷轧卷信息 插入成品库C_TBC02_COIL_COMM
				String ssql = "SELECT A.R_COIL_NO FROM L_TBF03_SPEC_REEL A WHERE A.C_COIL_NO ='"
						+ coilNo + "' and R_COIL_NO = '" + l_coilNo + "'";
				pSta1 = conn.prepareStatement(ssql);
				ResultSet rrs1 = pSta1.executeQuery();
				if (rrs1.next()) {
					String sqlQ = XmlSqlParsersFactory
							.getSql("UIM020050_06.SELECT");
					PreparedStatement pStaQ = conn.prepareStatement(sqlQ);
					pStaQ.setString(1, l_coilNo);
					ResultSet rsQ = pStaQ.executeQuery();
					if (!rsQ.next()) {
						// 插入钢卷成品表
						String sqlI = XmlSqlParsersFactory
								.getSql("UIM020050_03.INSERT");
						PreparedStatement pStaI = conn.prepareStatement(sqlI);
						pStaI.setString(1, coilNo);
						pStaI.executeUpdate();
						pStaI.close();
					} else {
						// 更新成品公共表
					}
					rsQ.close();
					pStaQ.close();

					// 删除钢卷成品_D表
					String sqlD = XmlSqlParsersFactory
							.getSql("UIM020050_03.DELETE");
					PreparedStatement pStaD = conn.prepareStatement(sqlD);
					pStaD.setString(1, l_coilNo);
					pStaD.executeUpdate();
					pStaD.close();
					// 插入钢卷成品_D表
					String sqlI = XmlSqlParsersFactory
							.getSql("UIM020050_04.INSERT");
					PreparedStatement pStaI = conn.prepareStatement(sqlI);
					pStaI.setString(1, coilNo);
					pStaI.executeUpdate();
					pStaI.close();

					// 订单标记
					this.SAVE_ORD_STS("", "", ordNo, ordSeq, befProgCd, "DBC",
							"CR", coilNo, V_ACT_WGT, "UIM020050", trnfReg);
					// 订单整理
					this.ORD_STS_MAIN("UIM020050", trnfReg);

				}
				rrs1.close();
				pSta1.close();

				conn.commit();
			} else {
				cro.setV_errCode(-1);
				cro.setV_errMsg("输入的钢卷号不在重卷计划中，请重新查询后再进行上料操作！");
			}

			rs.close();
			pSta.close();
		} catch (SQLException ex) {
			if (conn != null) {
				conn.rollback();
			}
			cro.setV_errCode(-1);
			cro.setV_errMsg("上料失败！");
		} finally {
			try{
				//添加了出现异常时关闭pSta1
				if (pSta1 != null) {
					pSta1.close();
				}
				if (conn != null && !conn.isClosed()) {
					conn.close();
				}
			} catch (Exception e) {
			}
		}
		return cro;
	}
	/**
	 * 查询钢卷是否已经上料了
	 */
	public CoreReturnObject isReceivedL2(String coilNo) throws SQLException {
		String sqlQuery = "select C_COIL_NO from L_TBF03_SPEC_REEL L where L.STATUS_CD ='B' AND L.C_COIL_NO = ?";
		return this.getDao("KgDao").ExcuteQuery(sqlQuery,
				new Object[] { coilNo });
	}

	/*
	 * 电文修改后，插入操作人，操作时间，操作班组
	 */

	public CoreReturnObject insertInformation(String trnfRegId,
			String trnfShift, String trnfGroup, String trnfDTime, String coilNo)
			throws SQLException {
		String sqlQuery = "update c_tbC02_coil_comm  set TRNF_REG =?, TRNF_SHIFT=?,  TRNF_GROUP=?, TRNF_DTIME=? ,OUT_YARD_KIND='1',TRNF_USE_TIME=TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') where OLD_SAMPL_NO = ? ";

		Object[] obj = new Object[] { trnfRegId, trnfShift, trnfGroup,
				trnfDTime, coilNo };

		return this.getDao("KgDao").ExcuteNonQuery(sqlQuery, obj);
	}
	
	public CoreReturnObject insertPltcmInformation(String trnfRegId,
			String trnfShift, String trnfGroup, String trnfDTime, String coilNo)
			throws SQLException {
		String sqlQuery = "update c_tbl02_coil_comm  set TRNF_REG =?, TRNF_SHIFT=?,  TRNF_GROUP=?, TRNF_DTIME=? ,OUT_YARD_KIND='1',TRNF_USE_TIME=TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') where OLD_SAMPL_NO = ? ";

		Object[] obj = new Object[] { trnfRegId, trnfShift, trnfGroup,
				trnfDTime, coilNo };

		return this.getDao("KgDao").ExcuteNonQuery(sqlQuery, obj);
	}

	/**
	 * 钢卷吊销入库
	 * 
	 * @param coilNo
	 * @param yardAddr
	 * @param millSeqCd
	 * @param trnfReg
	 * @param trnfShift
	 * @param trnfGroup
	 * @param trnfDTime
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject selectCoilYard(String yardAddr) throws SQLException {
		String sqlQuery = "select coil_no from c_tbk08_coil_yard where clf_no = C_PKG_UIM.GET_MIDDLEYARDNO_BYYARDFLAG(?)";

		// cro = this.getDao("KgDao").ExcuteQuery(sqlQuery);

		return this.getDao("KgDao").ExcuteQuery(sqlQuery,
				new Object[] { yardAddr });

	}

	public CoreReturnObject saveCoilYard(String coilNo, String yardAddr,
			String entryShift, String entryGroup, String entryDtime,
			String reg_id, String millSeqCd) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		Connection conn = null;
		PreparedStatement pSta1 = null;
		StringBuffer sqlBuff = new StringBuffer();
		try {

			conn = this.getDao("KgDao").getConnection();
			conn.setAutoCommit(false);

			// 判断垛位是否存在
			String sqlYard = "select 1 from c_tbk08_coil_yard t where t.clf_no = C_PKG_UIM.GET_FINALYARDNO_BYYARDFLAG(?)";
			PreparedStatement pStaYard = conn.prepareStatement(sqlYard);
			pStaYard.setString(1, yardAddr);
			ResultSet rsYard = pStaYard.executeQuery();
			if (rsYard.next()) {

				// 修改公共表信息
				sqlBuff = new StringBuffer();
				sqlBuff.append("update c_tbC02_coil_comm set COIL_STAT = ?\n");
				sqlBuff.append(",CUR_PROG_CD = ?\n");
				sqlBuff.append(",CUR_PROG_CD_PGM = 'UIM020050'\n");
				sqlBuff.append(",CUR_PROG_CD_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')\n");
				sqlBuff.append(",CUR_LOAD_LOC = ?\n");
				sqlBuff.append(",FL = ?\n");
				sqlBuff.append(",MISSNO_CLF_CD = 'E'\n");//返修标记
				sqlBuff.append(",IN_YARD_KIND = ?\n");
				sqlBuff
						.append(",CUR_LOAD_LOC_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')\n");
				sqlBuff.append(",YARD_ENTRY_SHIFT = ?\n");
				sqlBuff.append(",YARD_ENTRY_GROUP = ?\n");
				sqlBuff.append(",YARD_ENTRY_REG = ?\n");
				sqlBuff.append(",bef_prog_cd = ?\n");
				sqlBuff.append(",YARD_ENTRY_DTIME = ?\n");
				sqlBuff
						.append(",YARD_ENTRY_USE_TIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')\n");
				sqlBuff.append("WHERE OLD_SAMPL_NO = ?\n");
				System.out.print(sqlBuff.toString());
				pSta1 = conn.prepareStatement(sqlBuff.toString());
				pSta1.setString(1, "2");
				pSta1.setString(2, "DBA");
				pSta1.setString(3, yardAddr);
				pSta1.setString(4, "0");
				pSta1.setString(5, "4");
				pSta1.setString(6, entryShift);
				pSta1.setString(7, entryGroup);
				pSta1.setString(8, reg_id);
				pSta1.setString(9, "");
				pSta1.setString(10, entryDtime);
				pSta1.setString(11, coilNo);
				pSta1.executeUpdate();
				pSta1.close();
				
				// 将信息插入移垛表
				long seq = 0;
				String sqlqmaxseq = "select max(ROLL_SEQ) ROLL_SEQ from c_tbk08_coil_move\n";
				pSta1 = conn.prepareStatement(sqlqmaxseq);
				ResultSet rs = pSta1.executeQuery();
				if (rs.next()) {
					seq = rs.getLong("ROLL_SEQ");
				}
				rs.close();
				pSta1.close();

				sqlBuff = new StringBuffer();
				sqlBuff
						.append("insert into c_tbk08_coil_move T(T.ROLL_SEQ,T.CUR_LOAD_LOC_F,T.CUR_LOAD_LOC_T,T.COIL_NO,T.REG_ID,T.REG_SHIFT,T.REG_GROUP,T.REG_DTIME,T.REG_USE_ID,T.REG_USE_DTIME,MOVE_TYPE)" +
								" VALUES(?,?,?,?,?,?,?,?,?,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),'05')\n");
				pSta1 = conn.prepareStatement(sqlBuff.toString());
				pSta1.setLong(1, seq + 1);
				pSta1.setString(2, "");
				pSta1.setString(3, yardAddr);
				pSta1.setString(4, coilNo);
				pSta1.setString(5, reg_id);
				pSta1.setString(6, entryShift);
				pSta1.setString(7, entryGroup);
				pSta1.setString(8, entryDtime);
				pSta1.setString(9, reg_id);
				pSta1.executeUpdate();
				pSta1.close();
				
				// 吊销后删除成品表数据
				String sql = XmlSqlParsersFactory.getSql("UIM020050_02.DELETE");
				pSta1 = conn.prepareStatement(sql);
				pSta1.setString(1, coilNo);
				pSta1.executeUpdate();
				pSta1.close();
				
				// 修改重卷计划状态
				sqlBuff = new StringBuffer();
				sqlBuff.append("update l_tbf03_spec_REEl set STATUS_CD = ?\n");
				sqlBuff.append("where c_coil_no = ? and STATUS_CD <> 'E'\n");
				pSta1 = conn.prepareStatement(sqlBuff.toString());
				pSta1.setString(1, "E");
				pSta1.setString(2, coilNo);
				pSta1.executeUpdate();
				pSta1.close();
			} else {
				cro.setV_errCode(new Integer(3));
				cro.setV_errMsg("垛位不存在！");
			}

			rsYard.close();
			pStaYard.close();

			conn.commit();
		} catch (Exception ex) {
			System.out.print(ex.toString());
			if (conn != null) {
				conn.rollback();
			}
			cro.setV_errCode(-1);
			cro.setV_errMsg("吊销失败！");
		} finally {
			if (conn != null && !conn.isClosed()) {
				conn.close();
			}
		}
		return cro;
	}

	// 调用计划重新下发函数
	private String rePlan(Connection conn) throws SQLException {
		String rtMsg = "";
		String sql = XmlSqlParsersFactory.getSql("UIK050010_01.CALL");
		CallableStatement cstm = conn.prepareCall(sql);
		cstm.registerOutParameter(1, java.sql.Types.VARCHAR);
		cstm.execute();
		rtMsg = cstm.getString(1);
		cstm.close();
		return rtMsg;
	}

	/**
	 * 订单进程标记
	 * 
	 * @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;
	}
}