package UIM;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import UIB.COM.XmlSqlParsersFactory;

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

/**
 * 冷轧中间库钢卷入库管理
 * 
 * @author siy
 * @date 2010-08-10
 */
public class UIM010120 extends CoreIComponent {
	/**
	 * 查询钢卷在库信息
	 * 
	 * @param clfName
	 * @param clfRow
	 * @param clfCol
	 * @param clfFl
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryCoilStatus(String clfName, Integer clfRow,
			Integer clfCol, Integer clfFl) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		String sql = XmlSqlParsersFactory.getSql("UIM010120_01.SELECT");
		cro = this.getDao("KgDao").ExcuteQuery(sql,
				new Object[] { clfName, clfRow, clfCol, clfFl });
		return cro;
	}
	
	public CoreReturnObject queryCoilStatus2() throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		String sql = XmlSqlParsersFactory.getSql("UIM010120_07.SELECT");
		cro = this.getDao("KgDao").ExcuteQuery(sql);
		return cro;
	}
	public CoreReturnObject queryCoilsInStock(String productTime,String toproductTime,String coilNo,String flag) throws SQLException
	{
		String sql = XmlSqlParsersFactory.getSql("UIM010120_11.SELECT");
		return this.getDao("KgDao").ExcuteQuery(sql, new Object[]{productTime,toproductTime,coilNo});
	}
	

	public CoreReturnObject saveCoilYardN(String coilNo,String yardAddr,String entryShift,
			String entryGroup,String entryDtime,String reg_id) throws SQLException
	{
		CoreReturnObject cro = new CoreReturnObject();
		Connection con = null;//pSta.getConnection();
		//判断垛位是否存在
		//String sqlYard = "select 1 from c_tbk08_coil_yard t where t.clf_no = C_PKG_UIM.GET_FINALYARDNO_BYYARDFLAG(?)";
		//List yardList = this.getDao("KgDao").ExcuteQueryReturnList(sqlYard, new Object[]{yardAddr});
		String sqlYard = "select CLF_NO from c_tbk08_coil_yard "
			+ "where CLF_NAME = substr(?,0,1) "
			+ "and CLF_ROW = substr(?,3,2) "
			+ "and CLF_COL = substr(?,7,2) "
			+ "and CLF_FL = DECODE(substr(?,5,1),'A','1','B','2','C','3','1')";
		List yardList = this.getDao("KgDao")
			.ExcuteQueryReturnList(
					sqlYard,
					new Object[] { yardAddr, yardAddr, yardAddr,
							yardAddr });
		if(yardList.size() > 0){
			
			String chkcoil = "select COIL_NO from c_tbk08_coil_yard "
				+ "where CLF_NAME = substr(?,0,1) "
				+ "and CLF_ROW = substr(?,3,2) "
				+ "and CLF_COL = substr(?,7,2) "
				+ "and CLF_FL = DECODE(substr(?,5,1),'A','1','B','2','C','3','1') AND COIL_NO IS NOT NULL";
			List coilList = this.getDao("KgDao")
				.ExcuteQueryReturnList(
						chkcoil,
						new Object[] { yardAddr, yardAddr, yardAddr,
								yardAddr });
			String chkcoil2 = "select COIL_NO from C_TBL02_COIL_COMM where CUR_LOAD_LOC = ?";
			List coilList2 = this.getDao("KgDao")
				.ExcuteQueryReturnList(
						chkcoil2,
						new Object[] { yardAddr });
			if(coilList.size() > 0||coilList2.size()>0)
			{
				cro.setV_errCode(new Integer(-1));
				cro.setV_errMsg("垛位被占用！");
			}
			else
			{
			//查询钢卷入库信息
			String sqlEntry = "select COIL_NO from c_tbL02_coil_comm where OLD_SAMPL_NO = ? AND COIL_STAT = '2'";
			
			List listEntry = this.getDao("KgDao").ExcuteQueryReturnList(sqlEntry,new Object[]{coilNo});
			
			if(listEntry.size() == 0){
				cro.setV_errCode(new Integer(1));
				cro.setV_errMsg("此钢卷没有轧制完成,或者已经出库！");
			}
			else{
				//查询钢卷出库信息
				String sqlOut = "select CUR_LOAD_LOC from c_tbL02_coil_comm where OLD_SAMPL_NO = ?";
				List listOut = this.getDao("KgDao").ExcuteQueryReturnList(sqlOut, new Object[]{coilNo});
				
				if(listOut.size() > 0 && !Tools.isEmpty(((Map)(listOut.get(0))).get("CUR_LOAD_LOC"))){
					cro.setV_errCode(new Integer(2));
					cro.setV_errMsg("此钢卷已在垛位中存在！");
				}
				else{
					//将入库操作信息和垛位信息加入冷轧成品公共表
					String sqlucomm = "update c_tbL02_coil_comm set CUR_LOAD_LOC = ?," +
							" CUR_LOAD_LOC_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')," +
							" YARD_ENTRY_SHIFT = ?, YARD_ENTRY_GROUP = ?, YARD_ENTRY_REG  = ?," +
							" YARD_ENTRY_DTIME = ?, " +
							" YARD_ENTRY_USE_TIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')" +
							" where OLD_SAMPL_NO = ?";
					this.getDao("KgDao").ExcuteNonQuery(sqlucomm, 
							new Object[]{yardAddr,entryShift,entryGroup,reg_id,entryDtime,coilNo});
					
					//将钢卷信息加入垛位表
//					String sqluyard = "update c_tbk08_coil_yard set COIL_NO = ?, " +
//							"MOD_ID  = ?, MOD_TIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')" +
//							" where CLF_NO = C_PKG_UIM.GET_YARDNO_BYYARDFLAG(?)";
//					this.getDao("KgDao").ExcuteNonQuery(sqluyard, 
//							new Object[]{coilNo,reg_id,yardAddr});
					
					//入库完成后将入库记录加入移垛记录，便于跟踪钢卷移动记录
					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();
					con=pSta.getConnection();
					if(rs.next()){
						seq = rs.getLong("ROLL_SEQ")+1;
					}
					rs.close();
					pSta.close();
					con.close();
					
					// 入库类型
					String inType = "";
					String sqlType = "select T.IN_YARD_KIND from C_TBL02_COIL_COMM T where T.OLD_SAMPL_NO = ?";
					pSta = this.getDao("KgDao").getPreparedStatement(
							sqlType);
					pSta.setString(1, coilNo);
					rs = pSta.executeQuery();
					con=pSta.getConnection();
					if (rs.next()) {
						inType = rs.getString("IN_YARD_KIND");
					}
					rs.close();
					pSta.close();
					con.close();
					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,MOVE_TYPE)" +
							"VALUES(?,?,?,?,?,?,?,?,?,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),decode(?,'1','02','2','02','3','05','4','05','5','04','6','04','7','04'))";
					this.getDao("KgDao").ExcuteNonQuery(sqlimove, new Object[]{seq,"",yardAddr,coilNo,reg_id,entryShift,entryGroup,entryDtime,reg_id,inType});
				}
			}
		}
		}
		else{
			cro.setV_errCode(new Integer(3));
			cro.setV_errMsg("垛位不存在！");
		}
		
		return cro;
	}
	/**
	 * 中间库钢卷入库
	 * 
	 * @param coilNo
	 *            钢卷号
	 * @param yardAddr
	 *            垛位
	 * @param entryShift
	 *            入库班次
	 * @param entryGroup
	 *            入库班组
	 * @param entryDtime
	 *            入库时间
	 * @param reg_id
	 *            操作人
	 * @return CoreReturnObject
	 * @throws SQLException
	 */
	public CoreReturnObject saveCoilYard(String coilNo, String yardAddr,
			String entryShift, String entryGroup, String entryDtime,
			String reg_id) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		Connection con = null;
		// 查询钢卷入库信息
		String sqlEntry = XmlSqlParsersFactory.getSql("UIM010120_02.SELECT");

		List listEntry = this.getDao("KgDao").ExcuteQueryReturnList(sqlEntry,
				new Object[] { coilNo });

		if (listEntry.size() < 1) {
			cro.setV_errCode(new Integer(1));
			cro.setV_errMsg("此钢卷没有轧制完成,或者已经出库！");
		} else {
			// 查询钢卷出库信息
			String sqlOut = XmlSqlParsersFactory.getSql("UIM010120_03.SELECT");
			List listOut = this.getDao("KgDao").ExcuteQueryReturnList(sqlOut,
					new Object[] { coilNo });

			if (listOut.size() > 0) {
				cro.setV_errCode(new Integer(2));
				cro.setV_errMsg("此钢卷已在垛位中存在！");
			} else {
				// 查询垛位信息
				sqlOut = "select CLF_NO from c_tbk08_coil_yard "
						+ "where CLF_NAME = substr(?,0,1) "
						+ "and CLF_ROW = substr(?,3,2) "
						+ "and CLF_COL = substr(?,7,2) "
						+ "and CLF_FL = DECODE(substr(?,5,1),'A','1','B','2','C','3','1')";
				listOut = this.getDao("KgDao")
						.ExcuteQueryReturnList(
								sqlOut,
								new Object[] { yardAddr, yardAddr, yardAddr,
										yardAddr });
				if (listOut.size() == 0) {
					cro.setV_errCode(new Integer(3));
					cro.setV_errMsg("垛位不存在！");
				} else {
					Map result = this.getDao("KgDao").CoreBeginTranscation();
					ResultSet rs = null;
					PreparedStatement pSta = null;
					try {
						// 将入库操作信息和垛位信息加入酸轧公共表
						String sqlucomm = XmlSqlParsersFactory
								.getSql("UIM010120_01.UPDATE");
						this.getDao("KgDao")
								.ExcuteNonQuery(
										sqlucomm,
										new Object[] { yardAddr, entryShift,
												entryGroup, reg_id, entryDtime,
												coilNo });

						// 将钢卷信息加入垛位表
						String sqluyard = XmlSqlParsersFactory
								.getSql("UIM010120_02.UPDATE");
						this.getDao("KgDao").ExcuteNonQuery(
								sqluyard,
								new Object[] { coilNo, reg_id, yardAddr,
										yardAddr, yardAddr, yardAddr });

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

						String sqlqmaxseq = XmlSqlParsersFactory
								.getSql("UIM010120_04.SELECT");
						pSta = this.getDao("KgDao")
								.getPreparedStatement(sqlqmaxseq);
					    rs = pSta.executeQuery();
					    con=pSta.getConnection();
						if (rs.next()) {
							seq = rs.getLong("ROLL_SEQ");
						}
						rs.close();
						pSta.close();
						con.close();
						// 入库类型
						String inType = "";
						String sqlType = "select T.IN_YARD_KIND from C_TBL02_COIL_COMM T where T.COIL_NO = ?";
						pSta = this.getDao("KgDao").getPreparedStatement(
								sqlType);
						pSta.setString(1, coilNo);
						rs = pSta.executeQuery();
						con=pSta.getConnection();
						if (rs.next()) {
							inType = rs.getString("IN_YARD_KIND");
						}
						rs.close();
						pSta.close();
						con.close();
						String sqlimove = XmlSqlParsersFactory
								.getSql("UIM010120_01.INSERT");
						this.getDao("KgDao").ExcuteNonQuery(
								sqlimove,
								new Object[] { seq + 1, "", yardAddr, coilNo,
										reg_id, entryShift, entryGroup,
										entryDtime, reg_id, inType });
						this.getDao("KgDao").CoreCommit(result);
					} catch (Exception ex) {
						this.getDao("KgDao").CoreRollBack(result);
						cro.setV_errCode(-1);
						cro.setV_errMsg(ex.getMessage());
					} finally {
						try{
							//添加了关闭rs/pSta
							if(rs != null){
								rs.close();
							}
							if(pSta != null){
								pSta.close();
							}
							if(con != null){
								con.close();
							}
						} catch (Exception e) {
						}
					}
				}
			}
		}

		return cro;
	}
}