package UIM;

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

import UIB.COM.XmlSqlParsersFactory;

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

/**
 * 冷轧中间库到成品库管理
 * @author wl
 * @date 2015-09-18
 */
public class UIM010202 extends CoreIComponent {
	/**
	 * 查询待入库钢卷
	 * @param productTime 生产时间 yyyymmdd
	 * @param coilNo 钢卷号
	 * @return CoreReturnObject
	 * @throws SQLException
	 */
	public CoreReturnObject queryCoilsInTblStock(String coilNo) throws SQLException
	{
		String sql = XmlSqlParsersFactory.getSql("UIM010202_01.SELECT");
		return this.getDao("KgDao").ExcuteQuery(sql, new Object[]{coilNo});
	}
	public CoreReturnObject queryCoilsInTblbuffer() throws SQLException
	{
		String sql = XmlSqlParsersFactory.getSql("UIM010202_02.SELECT");
		return this.getDao("KgDao").ExcuteQuery(sql, new Object[]{});
	}
	/**
	 * 成品库钢卷入库
	 * @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 sqlYard;
		List yardList;
		if(!"2".equals(yardAddr.substring(0, 1))){
		//判断垛位是否存在
			sqlYard = "select 1 from c_tbk08_coil_yard t where t.clf_no = C_PKG_UIM.GET_FINALYARDNO_BYYARDFLAG(?)";
			yardList = this.getDao("KgDao").ExcuteQueryReturnList(sqlYard, new Object[]{yardAddr});
		}
		else
		{
			sqlYard= "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,3) "
				+ "and CLF_FL = DECODE(substr(?,5,1),'A','1','B','2','C','3','1')";	//判断垛位是否存在
			yardList = this.getDao("KgDao").ExcuteQueryReturnList(sqlYard, new Object[]{yardAddr,yardAddr,yardAddr,yardAddr});
		}
		//List yardList = this.getDao("KgDao").ExcuteQueryReturnList(sqlYard, new Object[]{yardAddr});
		
		if(yardList.size() > 0){
			//查询钢卷入库信息
			String sqlEntry = "select COIL_NO from c_tbl02_coil_comm where OLD_SAMPL_NO = ? AND COIL_STAT = '3'";
			
			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{
					//将入库操作信息和垛位信息加入冷轧成品公共表
				
					if("2".equals(yardAddr.substring(0, 1))){
						if(yardList.size() > 0 && !Tools.isEmpty(((Map)(yardList.get(0))).get("coil_no"))){
							cro.setV_errCode(new Integer(2));
							cro.setV_errMsg("此垛位已存在钢卷！");
						}
						else{					
					//将钢卷信息加入垛位表
					String sqluyard = "update c_tbk08_coil_yard set COIL_NO = ?, " +
							"MOD_ID  = ?, MOD_TIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')" 
							+ "where CLF_NAME = substr(?,0,1) "
							+ "and CLF_ROW = substr(?,3,2) "
							+ "and CLF_COL = substr(?,7,3) "
							+ "and CLF_FL = DECODE(substr(?,5,1),'A','1','B','2','C','3','1')";	
					this.getDao("KgDao").ExcuteNonQuery(sqluyard, 
							new Object[]{coilNo,reg_id,yardAddr,yardAddr,yardAddr,yardAddr});
					
					String sqlucomm1 = "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 = ?,COIL_STAT='2', " +
					" YARD_ENTRY_USE_TIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')" +
					" where OLD_SAMPL_NO = ?";
					this.getDao("KgDao").ExcuteNonQuery(sqlucomm1, 
					new Object[]{yardAddr,entryShift,entryGroup,reg_id,entryDtime,coilNo});
					
					}
					}
					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 = ?,COIL_STAT='2', " +
						" 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});
					}
					//入库完成后将入库记录加入移垛记录，便于跟踪钢卷移动记录
					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;
	}
	public CoreReturnObject saveCoilHc(String coilNo,String outShift,
			String outGroup,String outDtime,String reg_id) throws SQLException
	{
		// 根据钢卷号查询垛位编号
		String sql1 = "select clf_no from c_tbk08_coil_yard where coil_no = '"
				+ coilNo + "'";
		ResultSet rs = this.getDao("KgDao").ExceuteQueryForResultSet(sql1);

		long yardNo = 0;
		// 如果钢卷在原料库中，查询出相应的垛位编号
		if (rs.next()) {
			yardNo = Long.parseLong(rs.getObject("clf_no").toString());
		//20131014系统连接异常优化
			this.getDao("KgDao").closeRs(rs);
		// 根据垛位编号清除钢卷信息
			String sql2 = "update c_tbk08_coil_yard set coil_no = '' where clf_no = ?";
			this.getDao("KgDao").ExcuteNonQuery(sql2, new Object[] { yardNo });
		}
		
		
		CoreReturnObject cro = new CoreReturnObject();
		//判断垛位是否存在
		String updCommStr = XmlSqlParsersFactory
		.getSql("UIM010202_01.update");
			this.getDao("KgDao").ExcuteNonQuery(
							updCommStr,
								new Object[] { outShift, outGroup, outDtime,
									reg_id,coilNo });
		
		return cro;
	}
	//select coil_no from c_tbk08_coil_yard where clf_name || clf_row || clf_col || clf_fl = '?'
	
}