XinYu_LZMES/.svn/pristine/03/033fb80042dbd5ce3bcd041a2381d2b9ccf9ddc9.svn-base

package UIM.UIM06;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Map;

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

public class UIM060020 extends CoreIComponent {
	/**
	 * 外购卷入库
	 * 
	 * @param coilNo
	 * @param entryInfo
	 * @param coilInfo
	 * @param qltyList
	 * @param chemList
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject saveCoilYard(String coilNo, String[] entryInfo,
			String[] coilInfo, ArrayList<String[]> qltyList,
			ArrayList<String[]> chemList) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		Map result = this.getDao("KgDao").CoreBeginTranscation();

		try {
			// 将入库操作信息和垛位信息加入冷轧原料公共表
			StringBuffer sqlucomm = new StringBuffer();
			sqlucomm
					.append("insert into c_tbk02_coil_comm (coil_no,coil_stat,cur_prog_cd,cur_prog_cd_dtime\n");
			sqlucomm
					.append(" ,cur_prog_cd_pgm,coil_len,coil_india,coil_outdia,coil_thk,coil_wth,act_wgt\n");
			sqlucomm
					.append(" ,ord_fl,prodnm_cd,spec_abbsym,ord_use_cd,stl_grd,cur_load_loc,cur_load_loc_dtime\n");
			sqlucomm
					.append(" ,yard_entry_reg,yard_entry_shift,yard_entry_group,yard_entry_dtime,yard_entry_use_time\n");
			sqlucomm
					.append(" ,old_sampl_no) values(?,2,'PRC',TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),'UIM060020'\n");
			sqlucomm
					.append(" ,?,?,?,?,?,?,'2',?,?,?,?,?,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),?,?,?,?,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),?)\n");
			this.getDao("KgDao").ExcuteNonQuery(
					sqlucomm.toString(),
					new Object[] { coilNo, coilInfo[6], coilInfo[9],
							coilInfo[8], coilInfo[4], coilInfo[5], coilInfo[7],
							coilInfo[3], coilInfo[8], coilInfo[2], coilInfo[1],
							entryInfo[0], entryInfo[4], entryInfo[1],
							entryInfo[2], entryInfo[3], coilNo });

			// 将钢卷信息加入垛位表
			String sqluyard = "update c_tbk08_coil_yard set COIL_NO = ?,COIL_SOURCE = 1, "
					+ "MOD_ID  = ?, MOD_TIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')"
					+ " where CLF_NAME = substr(?,1,1) and CLF_COL = substr(?,3,2)"
					+ " and CLF_ROW = substr(?,7,2) and CLF_FL = DECODE(substr(?,5,1),'A','1','B','2','C','3','1')";
			this.getDao("KgDao").ExcuteNonQuery(
					sqluyard,
					new Object[] { coilNo, entryInfo[4], entryInfo[0],
							entryInfo[0], entryInfo[0], entryInfo[0] });

			// 入库完成后将入库记录加入移垛记录，便于跟踪钢卷移动记录
			long seq = 0;// 移垛记录表主键

			String sqlqmaxseq = "select max(ROLL_SEQ) ROLL_SEQ from c_tbk08_coil_move";
			PreparedStatement pSta = this.getDao("KgDao").getPreparedStatement(
					sqlqmaxseq);
			ResultSet rs = pSta.executeQuery();
			if (rs.next()) {
				seq = rs.getLong("ROLL_SEQ");
			}
			try{
				rs.close();
				Connection con = pSta.getConnection(); //目前未发现有什么作用，暂时屏蔽
				pSta.close();
				con.close();
			} catch (Exception e) {
			}

			String sqlimove = "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)VALUES(?,?,?,?,?,?,?,?,?,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'))";
			this.getDao("KgDao").ExcuteNonQuery(
					sqlimove,
					new Object[] { seq + 1, "", entryInfo[0], coilNo,
							entryInfo[4], entryInfo[1], entryInfo[2],
							entryInfo[3], entryInfo[4] });

			// 成份信息
			String chargeNo = coilInfo[10];// 炉号
			String procCd = "JJ";// 工序
			String chemSeq = "01";// 次数
			String chemValTp = "L";// 实绩编码
			String chemL2ProcCd = "CP";
			int chemSize = chemList.size();
			for (int i = 0; i < chemSize; i++) {
				String[] param = chemList.get(i);
				String chemCd = param[0];// 元素名
				String chemVal = param[1];// 实际值
				String remark = param[2];// 备注

				StringBuffer chemBuffer = new StringBuffer();
				chemBuffer
						.append(" insert into tbb02_work_ingr(charge_no,proc_cd,chem_seq,chem_cd\n");
				chemBuffer
						.append(" ,chem_val_tp,chem_val,reg_id,reg_dtime,reg_pgm_id,chem_l2_proc_cd,chem_l2_val,ingr_rmk)\n");
				chemBuffer
						.append(" values(?,?,?,?,?,?,?,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),?,?,?,?)\n");
				this.getDao("KgDao").ExcuteNonQuery(
						chemBuffer.toString(),
						new Object[] { chargeNo, procCd, chemSeq, chemCd,
								chemValTp, chemVal, entryInfo[4],
								"UIM060020", chemL2ProcCd, chemVal, remark });
			}

			// 性能信息
			String proName = "{call ACID_QLTY_DCS.ACID_OUT_SOURCING(?,?,?,?,?,?,?)}";
			Connection proConn = null;
			CallableStatement cs = null;
			try{
				proConn = this.getDao("KgDao").getConnection();
				int qltySize = qltyList.size();
				for (int i = 0; i < qltySize; i++) {
					String[] param = qltyList.get(i);
					
					cs = proConn.prepareCall(proName);
					cs.setString(1, coilNo);
					cs.setString(2, param[0]);
					cs.setString(3, param[1]);
					cs.setString(4, param[2]);
					cs.setString(5, param[3]);
					cs.setString(6, param[4]);
		            cs.registerOutParameter(7,Types.VARCHAR);
		            cs.execute();
		            
		            String rtMsg = cs.getString(7);
				    cs.close();
					
					if(!rtMsg.equals("YY")){
						cro.setV_errCode(-1);
						cro.setV_errMsg("生成冷轧外购卷试样号及性能失败！");
						proConn.close();
						this.getDao("KgDao").CoreRollBack(result);// 回滚
						return cro;
					}
				}
				proConn.close();
			}catch(Exception exp){
				exp.printStackTrace();
				if(cs != null){
					cs.close();
				}
				if(proConn != null){
					proConn.close();
				}
				this.getDao("KgDao").CoreRollBack(result);// 回滚
				return cro;
			}

			this.getDao("KgDao").CoreCommit(result);// 提交
		} catch (Exception ex) {
			ex.printStackTrace();
			this.getDao("KgDao").CoreRollBack(result);// 回滚
		}

		return cro;
	}

	/**
	 * 查询所有标准号
	 * 
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject querySpecAbbsymList() throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		String sql = "SELECT DISTINCT T.SPEC_ABBSYM   FROM TBB01_KEY_PROD T";
		cro = this.getDao("KgDao").ExcuteQuery(sql);
		return cro;
	}

	/**
	 * 查询所有牌号
	 * 
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject querySpecStlGrdList() throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		String sql = "SELECT DISTINCT T.SPEC_STL_GRD   FROM TBB01_KEY_PROD T";
		cro = this.getDao("KgDao").ExcuteQuery(sql);
		return cro;
	}

	/**
	 * 查询所有交货状态
	 * 
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryProdNmList() throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		String sql = "SELECT DISTINCT T.PRDNM_CD FROM TBB01_KEY_PROD T";
		cro = this.getDao("KgDao").ExcuteQuery(sql);
		return cro;
	}

	/**
	 * 查询所有订单用途
	 * 
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryOrdUseTyList() throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		String sql = "SELECT T.SM_CD , T.SM_CFNM FROM TBZ00_COMMCD T WHERE T.LG_CD = 'A01007' AND UPPER(USED_YN) = 'Y' ORDER BY SM_CFNM";
		cro = this.getDao("KgDao").ExcuteQuery(sql);
		return cro;
	}

	/**
	 * 查询所有成份信息
	 * 
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryChemList() throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append(" SELECT \n");
		sqlBuffer.append(" T1.CHEM_CD \n");
		sqlBuffer
				.append(" ,( CASE WHEN T1.COMP_YN = 'Y' THEN (CASE WHEN T1.COMP_DETAIL='复合元素' THEN T1.COMP_CAL  ELSE T1.COMP_DETAIL  END ) ELSE T1.CHEM_CD END ) CHEM_DIS \n");
		sqlBuffer
				.append(" FROM   TBB01_SEQ_INGR T1  ORDER BY T1.COMP_YN,T1.DISPLAY_SEQ  \n");
		cro = this.getDao("KgDao").ExcuteQuery(sqlBuffer.toString());
		return cro;
	}

	/**
	 * 查询材质信息
	 * 
	 * @param prdNmCd
	 * @param specAbbsym
	 * @param specStlGrd
	 * @param ordUseTp
	 * @param thk
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryQltyList(String prdNmCd, String specAbbsym,
			String specStlGrd, String ordUseTp, Double thk) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer
				.append("SELECT t1.QLTY_CD_CFNM , T2.QLTY_MIN , T2.QLTY_MAX FROM TBB01_SEQ_QLTY T1 , TBB01_SPEC_QLTY T2\n");
		sqlBuffer
				.append(" WHERE  T1.QLTY_CD               =     T2.QLTY_CD(+)\n");
		sqlBuffer.append(" AND    T2.PRDNM_CD(+)           =     ?   --交货状态\n");
		sqlBuffer.append(" AND    T2.SPEC_ABBSYM(+)        =     ?   --标准号\n");
		sqlBuffer.append(" AND    T2.SPEC_STL_GRD(+)       =     ?   --标准牌号\n");
		sqlBuffer.append(" AND    T2.ORD_USE_TP(+)         =     ?   --订单用途\n");
		sqlBuffer.append(" AND    T2.THK_MIN(+)            <=    ?   --厚度\n");
		sqlBuffer.append(" AND    T2.THK_MAX(+)            >=    ?   --厚度\n");

		cro = this.getDao("KgDao").ExcuteQuery(
				sqlBuffer.toString(),
				new Object[] { prdNmCd, specAbbsym, specAbbsym, ordUseTp, thk,
						thk });
		return cro;
	}

	public CoreReturnObject queryColdCoilNo(String coilNo) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		int haveCoil = 0;
		String sql = "select count(coil_no) count from c_tbk02_coil_comm where coil_no = '"
				+ coilNo + "'";
		PreparedStatement pretStat = this.getDao("KgDao").getPreparedStatement(sql);
		ResultSet rs = pretStat.executeQuery();
		if (rs.next()) {
			haveCoil = rs.getInt("COUNT");
		}
		cro.setResult(haveCoil);
		try{
			rs.close();
			Connection con = pretStat.getConnection(); //关闭连接
			pretStat.close();
			con.close();
		} catch (Exception e) {
		}
		return cro;
	}
}