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