XinYu_LZMES/.svn/pristine/23/23160c541f770cb38d5459b0119847f8e7236b2b.svn-base

package UIM.UIM06;

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 zsx
 * @date 2017-7-26
 */
public class UIM060010 extends CoreIComponent {
	/**
	 * 查询酸轧轧制计划
	 * 
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryAcidRollingPlan() throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		String sql = XmlSqlParsersFactory.getSql("UIM060010_01.SELECT");
		cro = this.getDao("KgDao").ExcuteQuery(sql);
		return cro;
	}

	public CoreReturnObject queryAddClassInf() throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		String sql = XmlSqlParsersFactory.getSql("UIM060010_02.SELECT");
		cro = this.getDao("KgDao").ExcuteQuery(sql);
		return cro;
	}

	/**
	 * 当前垛位上层是否存在钢卷
	 * 
	 * @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 clfCol = yardAddrs.substring(2, 4);
			String clfRow = yardAddrs.substring(6);
			String clfFl = yardAddrs.substring(4, 5);

			StringBuffer sqlBuffer = new StringBuffer();
			sqlBuffer
					.append("select count(coil_no) count from c_tbk08_coil_yard \n");
			sqlBuffer.append(" where clf_name = '");
			sqlBuffer.append(clfName);
			sqlBuffer.append("'\n");
			sqlBuffer.append(" and clf_col = ");
			sqlBuffer.append(Integer.parseInt(clfCol, 10));
			sqlBuffer.append("\n");
			sqlBuffer.append(" and clf_fl = ");
			sqlBuffer.append(Integer.parseInt(clfFl, 10) + 1);
			sqlBuffer.append("\n");
			sqlBuffer.append(" and clf_row in (");
			sqlBuffer.append(Integer.parseInt(clfRow, 10) - 1);
			sqlBuffer.append(",");
			sqlBuffer.append(Integer.parseInt(clfRow, 10));
			sqlBuffer.append(")");

			ResultSet rs = this.getDao("KgDao").ExceuteQueryForResultSet(
					sqlBuffer.toString());
        	if (rs.next()) {
				count = rs.getInt("COUNT");
			}
        	//20131014系统连接异常优化
        	this.getDao("KgDao").closeRs(rs);
		}

		cro.setResult(count);
		return cro;
	}

	/**
	 * 酸轧上料
	 * 
	 * @param coilNo
	 * @param c_coilNo
	 * @param millSeqCd
	 * @param trnfReg
	 * @param trnfShift
	 * @param trnfGroup
	 * @param trnfDTime
	 * @return
	 * @throws SQLException
	 * @throws SQLException
	 */
	public CoreReturnObject saveAcidRolling(String coilNo, String c_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 S_TBF03_SPEC_MILL A WHERE A.COIL_NO = '"
					+ coilNo + "' AND A.STATUS_CD = 'A'\n";
			System.out.print(sql);
			PreparedStatement pSta = conn.prepareStatement(sql);
			ResultSet rs = pSta.executeQuery();

			if (rs.next()) {
				// 修改钢卷公共表钢卷状态，清除垛位，生成出库记录
				String befProgCd = "";
				String befProgCdTime = "";
				String befProgCdPgm = "";
				String V_ACT_WGT = "";
				String curLoadLoc = "";
				// String V_BEF_PROG_CD ="";
				String qSql = "select cur_prog_cd,cur_prog_cd_dtime,cur_prog_cd_pgm,ACT_WGT,cur_load_loc from c_tbk02_coil_comm where OLD_SAMPL_NO = '"
						+ coilNo + "'\n";
				System.out.print(qSql);
				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").toString();
					curLoadLoc = rs2.getString("CUR_LOAD_LOC");
					// V_BEF_PROG_CD = rs2.getString("BEF_PROG_CD");
				}
				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();
				pSta1.close();

				sqlBuffer = new StringBuffer();
				sqlBuffer.append("update c_tbk02_coil_comm set coil_stat='3', cur_prog_cd = 'PCB'\n");// 取消状态位 
				sqlBuffer.append(" ,cur_prog_cd_dtime = to_char(sysdate,'YYYYMMDDHH24MISS')\n");
				sqlBuffer.append(" ,cur_prog_cd_pgm = 'UIM060010'\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(" ,OUT_YARD_KIND = '1'\n");
				sqlBuffer.append(" ,trnf_use_time=to_char(sysdate,'YYYYMMDDHH24MISS')\n");
				sqlBuffer.append(" ,trnf_dtime=?\n");
				sqlBuffer.append(" where coil_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 S_tbf03_spec_mill \n");
				sqlBuffer.append(" set \n");
				sqlBuffer.append(" STATUS_CD = 'B' \n");
				sqlBuffer.append(" where \n");
				sqlBuffer.append(" 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_name,clf_col,clf_row,clf_fl from c_tbk08_coil_yard where coil_no = '"
						+ coilNo + "'\n";
				System.out.print(sql1);
				pSta1 = conn.prepareStatement(sql1);
				ResultSet rs3 = pSta1.executeQuery();
				if (rs3.next()) {
					String clf_name = rs3.getString("CLF_NAME");
					String clf_col = rs3.getString("CLF_COL");
					String clf_row = rs3.getString("CLF_ROW");
					String clf_fl = rs3.getString("CLF_FL");
					sqlBuffer = new StringBuffer();
					sqlBuffer.append("update c_tbk08_coil_yard \n");
					sqlBuffer.append(" set \n");
					sqlBuffer.append(" coil_no = ''\n");
					sqlBuffer.append(" where \n");
					sqlBuffer.append(" clf_name = '");
					sqlBuffer.append(clf_name);
					sqlBuffer.append("'\n");
					sqlBuffer.append("and\n");
					sqlBuffer.append(" clf_col = '");
					sqlBuffer.append(clf_col);
					sqlBuffer.append("'\n");
					sqlBuffer.append("and\n");
					sqlBuffer.append("clf_row = '");
					sqlBuffer.append(clf_row);
					sqlBuffer.append("'\n");
					sqlBuffer.append("and\n");
					sqlBuffer.append(" clf_fl = '");
					sqlBuffer.append(clf_fl);
					sqlBuffer.append("'\n");
					PreparedStatement pSta3 = conn.prepareStatement(sqlBuffer
							.toString());
					pSta3.executeUpdate();
					pSta3.close();
				}
				rs3.close();
				pSta1.close();
				// 冷轧卷信息 插入酸扎C_TBL02_COIL_COMM
				String ssql = "SELECT A.C_COIL_NO FROM S_TBF03_SPEC_MILL A WHERE A.COIL_NO ='"
						+ coilNo + "' and C_COIL_NO = '" + c_coilNo + "'";
				pSta1 = conn.prepareStatement(ssql);
				ResultSet rrs1 = pSta1.executeQuery();
				if (rrs1.next()) {
					// 修改钢卷公共表钢卷状态，清除垛位，生成出库记录
					String V_ORD_NO = "";
					String V_ORD_SEQ = "";
					String V_PRODNM_CD = "";
					String V_C_SPEC_ABBSYM = "";
					String V_SPEC_STL_GRD = "";
					String V_C_ORD_USEAGE_CD = "";
					String V_ORD_THK = "";
					String V_ORD_WTH = "";
					String V_SLAB_NO = "";
					String V_SMP_NO = "";
					String V_SMP_CUT_LOC = "";
					String V_SMP_DIR_PIC_YN = "";
					String V_INGR_ONLY_TP = "";
					String V_MATLQLTY_DEC_GRD = "";
					String V_COIL_CUT_SEQ1 = ""; // C_TBF03_SPEC_MILL_D.COIL_CUT_SEQ%TYPE;
					String V_ORD_THK1 = ""; // C_TBF03_SPEC_MILL_D.ORD_THK%TYPE;
					String V_ORD_WTH1 = ""; // C_TBF03_SPEC_MILL_D.ORD_WTH%TYPE;
					String V_ORD_LEN1 = ""; // C_TBF03_SPEC_MILL_D.ORD_LEN%TYPE;
					String V_ORD_WGT1 = ""; // C_TBF03_SPEC_MILL_D.ORD_WGT%TYPE;
					String V_ORD_WGT_MIN1 = ""; // C_TBF03_SPEC_MILL_D.ORD_WGT_MIN%TYPE;
					String V_ORD_WGT_MAX1 = ""; // C_TBF03_SPEC_MILL_D.ORD_WGT_MAX%TYPE;
					String V_ORD_INDIA1 = ""; // C_TBF03_SPEC_MILL_D.ORD_INDIA%TYPE;
					String V_ORD_OUTDIA1 = ""; // C_TBF03_SPEC_MILL_D.ORD_OUTDIA%TYPE;
					String V_ORD_FL1 = ""; // C_TBF03_SPEC_MILL_D.ORD_FL%TYPE;

				/*	String qs = "select SLAB_NO FROM TBH02_COIL_COMM where OLD_SAMPL_NO = '"
							+ coilNo + "'\n";*/
					String qs = "select SLAB_NO FROM S_TBF03_SPEC_MILL  WHERE COIL_NO = '"
						+ coilNo + "'\n";
					PreparedStatement pSta2 = conn.prepareStatement(qs);
					ResultSet rx = pSta2.executeQuery();
					if (rx.next()) {
						V_SLAB_NO = rx.getString("SLAB_NO");
					}
					rx.close();
					pSta2.close();

					String qqSql = "select ORD_NO,ORD_SEQ,PRODNM_CD,C_SPEC_ABBSYM,SPEC_STL_GRD,C_ORD_USEAGE_CD,ORD_THK,ORD_WTH,SMP_NO,SMP_CUT_LOC,SMP_DIR_PIC_YN,INGR_ONLY_TP,MATLQLTY_DEC_GRD FROM S_tbf03_spec_mill where coil_no = '"
							+ coilNo + "'";
					pSta2 = conn.prepareStatement(qqSql);
					ResultSet rrs2 = pSta2.executeQuery();
					if (rrs2.next()) {
						V_ORD_NO = rrs2.getString("ORD_NO");
						V_ORD_SEQ = rrs2.getString("ORD_SEQ");
						V_PRODNM_CD = rrs2.getString("PRODNM_CD");
						V_C_SPEC_ABBSYM = rrs2.getString("C_SPEC_ABBSYM");
						V_SPEC_STL_GRD = rrs2.getString("SPEC_STL_GRD");
						V_C_ORD_USEAGE_CD = rrs2.getString("C_ORD_USEAGE_CD");
						V_ORD_THK = rrs2.getString("ORD_THK");
						V_ORD_WTH = rrs2.getString("ORD_WTH");
						V_SMP_NO = rrs2.getString("SMP_NO");
						V_SMP_CUT_LOC = rrs2.getString("SMP_CUT_LOC");
						V_SMP_DIR_PIC_YN = rrs2.getString("SMP_DIR_PIC_YN");
						V_INGR_ONLY_TP = rrs2.getString("INGR_ONLY_TP");
						V_MATLQLTY_DEC_GRD = rrs2.getString("MATLQLTY_DEC_GRD");
					}
					rrs2.close();
					pSta2.close();
					System.out.print(V_C_ORD_USEAGE_CD);
					String qqSqll = "select H_COIL_NO1 FROM C_TBL02_COIL_COMM where H_COIL_NO1 = '"
							+ coilNo + "'";
					pSta2 = conn.prepareStatement(qqSqll);
					ResultSet rrs22 = pSta2.executeQuery();
					if (rrs22.next()) {
						sqlBuffer = new StringBuffer();
						sqlBuffer
								.append("INSERT INTO C_TBL02_COIL_COMM(ORD_SEQ ,ORD_NO,PRODNM_CD,SPEC_ABBSYM,SPEC_STL_GRD,ORD_USE_CD,INSTR_COIL_THK,INSTR_COIL_WTH,SLAB_NO,H_COIL_NO1,COIL_STAT,COIL_NO,SMP_NO,SMP_CUT_LOC,SMP_DIR_PIC_YN,INGR_ONLY_TP,MATLQLTY_DEC_GRD,BEF_STL_GRD,LINE_TP) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)\n");
						PreparedStatement pSta4 = conn
								.prepareStatement(sqlBuffer.toString());
						pSta4.setString(1, V_ORD_SEQ);
						pSta4.setString(2, V_ORD_NO);
						pSta4.setString(3, V_PRODNM_CD);
						pSta4.setString(4, V_C_SPEC_ABBSYM);
						pSta4.setString(5, V_SPEC_STL_GRD);
						pSta4.setString(6, V_C_ORD_USEAGE_CD);
						pSta4.setString(7, V_ORD_THK);
						pSta4.setString(8, V_ORD_WTH);
						pSta4.setString(9, V_SLAB_NO);
						pSta4.setString(10, coilNo);
						pSta4.setString(11, "1");
						pSta4.setString(12, c_coilNo);
						pSta4.setString(13, V_SMP_NO);
						pSta4.setString(14, V_SMP_CUT_LOC);
						pSta4.setString(15, V_SMP_DIR_PIC_YN);
						pSta4.setString(16, V_INGR_ONLY_TP);
						pSta4.setString(17, V_MATLQLTY_DEC_GRD);
						pSta4.setString(18, V_SPEC_STL_GRD);
						pSta4.setString(19, "S");
						pSta4.executeUpdate();
						pSta4.close();
					} else {
						sqlBuffer = new StringBuffer();
						sqlBuffer
								.append("INSERT INTO C_TBL02_COIL_COMM(ORD_SEQ ,ORD_NO,PRODNM_CD,SPEC_ABBSYM,SPEC_STL_GRD,ORD_USE_CD,INSTR_COIL_THK,INSTR_COIL_WTH,SLAB_NO,H_COIL_NO1,COIL_STAT,COIL_NO,SMP_NO,SMP_CUT_LOC,SMP_DIR_PIC_YN,INGR_ONLY_TP,MATLQLTY_DEC_GRD,BEF_STL_GRD,LINE_TP) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)\n");
						PreparedStatement pSta4 = conn
								.prepareStatement(sqlBuffer.toString());
						pSta4.setString(1, V_ORD_SEQ);
						pSta4.setString(2, V_ORD_NO);
						pSta4.setString(3, V_PRODNM_CD);
						pSta4.setString(4, V_C_SPEC_ABBSYM);
						pSta4.setString(5, V_SPEC_STL_GRD);
						pSta4.setString(6, V_C_ORD_USEAGE_CD);
						pSta4.setString(7, V_ORD_THK);
						pSta4.setString(8, V_ORD_WTH);
						pSta4.setString(9, V_SLAB_NO);
						pSta4.setString(10, coilNo);
						pSta4.setString(11, "1");
						pSta4.setString(12, c_coilNo);
						pSta4.setString(13, V_SMP_NO);
						pSta4.setString(14, V_SMP_CUT_LOC);
						pSta4.setString(15, V_SMP_DIR_PIC_YN);
						pSta4.setString(16, V_INGR_ONLY_TP);
						pSta4.setString(17, V_MATLQLTY_DEC_GRD);
						pSta4.setString(18, V_SPEC_STL_GRD);
						pSta4.setString(19, "S");
						pSta4.executeUpdate();
						pSta4.close();
					}
					rrs22.close();
					pSta2.close();
					// 修改c_tbl02_coil_comm_d
					String tbl02d = " SELECT COIL_CUT_SEQ,ORD_THK,ORD_WTH,ORD_LEN,ORD_WGT,ORD_WGT_MIN,ORD_WGT_MAX,ORD_INDIA,ORD_OUTDIA,ORD_FL from S_TBF03_SPEC_MILL_D WHERE ORD_NO = '"
							+ V_ORD_NO + "' AND ORD_SEQ = '" + V_ORD_SEQ + "'";
					pSta2 = conn.prepareStatement(tbl02d);
					ResultSet rtbl02d = pSta2.executeQuery();
					System.out.print(tbl02d);
					if (rtbl02d.next()) {
						V_COIL_CUT_SEQ1 = rtbl02d.getString("COIL_CUT_SEQ");
						V_ORD_THK1 = rtbl02d.getString("ORD_THK");
						V_ORD_WTH1 = rtbl02d.getString("ORD_WTH");
						V_ORD_LEN1 = rtbl02d.getString("ORD_LEN");
						V_ORD_WGT1 = rtbl02d.getString("ORD_WGT");
						V_ORD_WGT_MIN1 = rtbl02d.getString("ORD_WGT_MIN");
						V_ORD_WGT_MAX1 = rtbl02d.getString("ORD_WGT_MAX");
						V_ORD_INDIA1 = rtbl02d.getString("ORD_INDIA");
						V_ORD_OUTDIA1 = rtbl02d.getString("ORD_OUTDIA");
						V_ORD_FL1 = rtbl02d.getString("ORD_FL");
					}
					rtbl02d.close();
					pSta2.close();

					String tbl02ds = "SELECT L_COIL_NO  FROM C_TBL02_COIL_COMM_D WHERE L_COIL_NO ='"
							+ c_coilNo + "'";
					pSta2 = conn.prepareStatement(tbl02ds);
					ResultSet rtbl02ds = pSta2.executeQuery();
					if (rtbl02ds.next()) {
						sqlBuffer = new StringBuffer();
						sqlBuffer
								.append("UPDATE C_TBL02_COIL_COMM_D SET CUT_SEQ = ?\n");
						sqlBuffer.append(" ,ORD_THK = ?\n");
						sqlBuffer.append(" ,ORD_WTH = ?\n");
						sqlBuffer.append(" ,ORD_LEN = ?\n");
						sqlBuffer.append(" ,ORD_WGT = ?\n");
						sqlBuffer.append(" ,ORD_WGT_MIN = ?\n");
						sqlBuffer.append(" ,ORD_WGT_MAX = ?\n");
						sqlBuffer.append(" ,ORD_INDIA = ?\n");
						sqlBuffer.append(" ,ORD_OUTDIA = ?\n");
						sqlBuffer.append(" ,ORD_NO = ?\n");
						sqlBuffer.append(" ,ORD_SEQ = ?\n");
						sqlBuffer.append(" ,ORD_FL = ?\n");
						sqlBuffer.append(" where L_COIL_NO = ?\n");
						PreparedStatement pSta7 = conn
								.prepareStatement(sqlBuffer.toString());
						pSta7.setString(1, V_COIL_CUT_SEQ1);
						pSta7.setString(2, V_ORD_THK1);
						pSta7.setString(3, V_ORD_WTH1);
						pSta7.setString(4, V_ORD_LEN1);
						pSta7.setString(5, V_ORD_WGT1);
						pSta7.setString(6, V_ORD_WGT_MIN1);
						pSta7.setString(7, V_ORD_WGT_MAX1);
						pSta7.setString(8, V_ORD_INDIA1);
						pSta7.setString(9, V_ORD_OUTDIA1);
						pSta7.setString(10, V_ORD_NO);
						pSta7.setString(11, V_ORD_SEQ);
						pSta7.setString(12, V_ORD_FL1);
						pSta7.setString(13, c_coilNo);
						pSta7.executeUpdate();
						pSta7.close();
					} else {
						sqlBuffer = new StringBuffer();
						sqlBuffer
								.append("INSERT INTO C_TBL02_COIL_COMM_D(L_COIL_NO,CUT_SEQ,ORD_THK,ORD_WTH,ORD_LEN,ORD_WGT,ORD_WGT_MIN,ORD_WGT_MAX,ORD_INDIA,ORD_OUTDIA,ORD_NO,ORD_SEQ,ORD_FL) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)\n");
						System.out.print(sqlBuffer.toString());
						PreparedStatement pSta5 = conn
								.prepareStatement(sqlBuffer.toString());
						pSta5.setString(1, c_coilNo);
						pSta5.setString(2, V_COIL_CUT_SEQ1);
						pSta5.setString(3, V_ORD_THK1);
						pSta5.setString(4, V_ORD_WTH1);
						pSta5.setString(5, V_ORD_LEN1);
						pSta5.setString(6, V_ORD_WGT1);
						pSta5.setString(7, V_ORD_WGT_MIN1);
						pSta5.setString(8, V_ORD_WGT_MAX1);
						pSta5.setString(9, V_ORD_INDIA1);
						pSta5.setString(10, V_ORD_OUTDIA1);
						pSta5.setString(11, V_ORD_NO);
						pSta5.setString(12, V_ORD_SEQ);
						pSta5.setString(13, V_ORD_FL1);
						pSta5.executeUpdate();
						pSta5.close();
					}
					rtbl02ds.close();
					pSta2.close();
					// 订单标记
					this.SAVE_ORD_STS("", "", V_ORD_NO, V_ORD_SEQ, befProgCd,
							"PCC", "CR", coilNo, V_ACT_WGT, "UIM060010",
							trnfReg);
					// 订单整理
					this.ORD_STS_MAIN("UIM060010", trnfReg);

				}
				rs.close();
				pSta.close();
				conn.commit();
			} else {
				cro.setV_errCode(-1);
				cro.setV_errMsg("输入的钢卷号不在轧制计划中，请重新查询后再进行上料操作！");
			}
		} catch (Exception ex) {
			System.out.print(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 SelectSTATUS_CD(String coilNo) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();

		String sqlQuery = "select COIL_EDT_SEQ from S_TBF03_SPEC_MILL where STATUS_CD ='B' AND COIL_NO = ?";

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

		cro = this.getDao("KgDao").ExcuteQuery(sqlQuery,
				new Object[] { coilNo });

		return cro;

	}

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

	public CoreReturnObject InsertInformation(String trnfRegId,
			String trnfShift, String trnfGroup, String trnfDTime, String coilNo)
			throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();

		String sqlQuery = "update c_tbk02_coil_comm  set TRNF_REG =?, TRNF_SHIFT=?,  TRNF_GROUP=?, TRNF_DTIME=? where OLD_SAMPL_NO = ? ";

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

		cro = this.getDao("KgDao").ExcuteNonQuery(sqlQuery, obj);

		return cro;

	}

	/**
	 * 钢卷吊销入库
	 * 
	 * @param coilNo
	 * @param yardAddr
	 * @param millSeqCd
	 * @param trnfReg
	 * @param trnfShift
	 * @param trnfGroup
	 * @param trnfDTime
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject SelectCoilYard(String yardAddr) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();

		String sqlQuery = "select coil_no from c_tbk02_coil_comm where CUR_LOAD_LOC =? ";

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

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

		return cro;

	}

	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);

			// 钢卷是否已上料
			boolean flag = false;
			sqlBuff = new StringBuffer();
			sqlBuff.append("select 1 from S_tbf03_spec_mill t \n");
			sqlBuff.append("where t.COIL_NO = ? \n");
			sqlBuff
					.append("and t.ROLL_MANA_NO = (select max(roll_mana_no) from S_tbf03_spec_mill where coil_no = ?)\n");
			sqlBuff.append("and t.STATUS_CD = 'A'\n");
			pSta1 = conn.prepareStatement(sqlBuff.toString());
			pSta1.setString(1, coilNo);
			pSta1.setString(2, coilNo);
			ResultSet rs1 = pSta1.executeQuery();
			if (rs1.next()) {
				flag = true;
			}
			rs1.close();
			pSta1.close();
			if (flag) {
				conn.rollback();
				cro.setV_errCode(-2);
				cro.setV_errMsg("请先将钢卷上料出库后再进行吊销操作！");
				return cro;
			}

			// 修改公共表信息
			sqlBuff = new StringBuffer();
			sqlBuff.append("update c_tbk02_coil_comm set COIL_STAT = ?\n");
			sqlBuff.append(",CUR_PROG_CD = ?\n");
			sqlBuff.append(",CUR_LOAD_LOC = ?\n");
			sqlBuff.append(",FL = ?\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, "PCA");
			pSta1.setString(3, yardAddr);
			pSta1.setString(4, "0");
			pSta1.setString(5, "2");
			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();

			// 修改酸扎计划状态
			sqlBuff = new StringBuffer();
			sqlBuff.append("update S_tbf03_spec_mill set STATUS_CD = ?\n");
			sqlBuff.append("where coil_no = ?\n");
			pSta1 = conn.prepareStatement(sqlBuff.toString());
			pSta1.setString(1, "E");
			pSta1.setString(2, coilNo);
			pSta1.executeUpdate();
			pSta1.close();

			// 将钢卷号重新绑定到剁位上
			sqlBuff = new StringBuffer();
			sqlBuff.append("update c_tbk08_coil_yard set COIL_NO = ?\n");
			sqlBuff.append(",COIL_SOURCE = ?\n");
			sqlBuff.append(",MOD_ID  = ?\n");
			sqlBuff.append(",MOD_TIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')\n");
			sqlBuff.append("where CLF_NAME = substr(?,0,1)\n");
			sqlBuff.append("AND CLF_COL = substr(?,3,2)\n");
			// sqlBuff.append("AND CLF_NAME = substr(?,0,1)\n");
			sqlBuff.append("AND CLF_ROW = substr(?,7,2)\n");
			sqlBuff
					.append("AND CLF_FL = DECODE(substr(?,5,1),'A','1','B','2','C','3','1')\n");
			System.out.print(sqlBuff.toString());
			pSta1 = conn.prepareStatement(sqlBuff.toString());
			pSta1.setString(1, coilNo);
			pSta1.setString(2, "2");
			pSta1.setString(3, reg_id);
			pSta1.setString(4, yardAddr);
			pSta1.setString(5, yardAddr);
			pSta1.setString(6, yardAddr);
			pSta1.setString(7, yardAddr);
			// pSta1.setString(8,yardAddr);
			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();

			// 分析c_tbf03_spec_roll
			String tbfrollno = "";
			String sqlTBFSM = "select ROLL_NO  from S_tbf03_spec_mill where coil_no ='"
					+ coilNo + "'";
			pSta1 = conn.prepareStatement(sqlTBFSM);
			ResultSet rsTBF = pSta1.executeQuery();
			if (rsTBF.next()) {
				tbfrollno = rsTBF.getString("ROLL_NO");
			}
			rsTBF.close();
			pSta1.close();

			sqlBuff = new StringBuffer();
			sqlBuff.append("update s_tbf03_spec_roll set STATUS_CD = ?\n");
			sqlBuff.append(" where roll_no = ?\n");
			sqlBuff
					.append(" AND STATUS_CD <> 'D' and (select count(c_coil_no) from S_tbf03_spec_mill where roll_no = ?\n");
			sqlBuff
					.append(" ) =(select count(c_coil_no) from S_tbf03_spec_mill where roll_no = ?\n");
			sqlBuff.append(" and (STATUS_CD = 'D' OR STATUS_CD = 'E'))\n");
			pSta1 = conn.prepareStatement(sqlBuff.toString());
			pSta1.setString(1, "D");
			pSta1.setString(2, tbfrollno);
			pSta1.setString(3, tbfrollno);
			pSta1.setString(4, tbfrollno);
			pSta1.executeUpdate();
			pSta1.close();

			// 执行脱单降级过程

			String stds = disrep(conn,coilNo,reg_id);
			System.out.println("=====================================>>>"
					+ stds);
			if (!"-20000".equals(stds)) {
				conn.commit();
			} else {
				conn.rollback();
				cro.setV_errCode(-1);
				cro.setV_errMsg("吊销失败！");
			}
			// 吊销钢卷后重新下发计划

			String strx = rePlan(conn);
			System.out.println("=====================================>>>"
					+ strx);
			if ("1".equals(strx)) {
				conn.commit();
			} else {
				conn.rollback();
				cro.setV_errCode(-1);
				cro.setV_errMsg("吊销失败！");
			}
		} catch (Exception ex) {
			System.out.print(ex.toString());
			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;
	}

	// 调用计划重新下发函数
	private String rePlan(Connection conn) throws SQLException {
		String rtMsg = "";
		String sql = XmlSqlParsersFactory.getSql("UIK050010_02.CALL");
		CallableStatement cstm = conn.prepareCall(sql);
		cstm.registerOutParameter(1, java.sql.Types.VARCHAR);
		cstm.execute();
		rtMsg = cstm.getString(1);
		cstm.close();
		return rtMsg;
	}
	// 调用酸洗卷吊销函数  20180718 wl
	private String disrep(Connection conn,String coilNo,String reg_id) throws SQLException {
		String rtMsg = "";
		String sql = XmlSqlParsersFactory.getSql("UIE043020_ORD_DISREP.call");
		CallableStatement cstm = conn.prepareCall(sql);
		cstm.setString(1, reg_id);
		cstm.setString(2, "UIM060010");
		cstm.setString(3, "2B");
		cstm.setString(4, coilNo);//PLTCM04
		cstm.setString(5, "PLTCM04");//PLTCM04
		
		cstm.registerOutParameter(6, java.sql.Types.VARCHAR);
		cstm.registerOutParameter(7, java.sql.Types.VARCHAR);
		cstm.execute();
		rtMsg = cstm.getString(6);
		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;
	}
}