package UIM;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Iterator;
import java.util.List;
import java.util.Map;


import UIB.COM.ProduceFactory;
import UIB.COM.TechDcsResult;
import UIB.COM.XmlSqlParsersFactory;

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

/**
 * 冷轧原料库钢卷入库管理
 * 
 * @author siy
 * @date 2010-08-10
 */
public class UIM010040 extends CoreIComponent {
	/**
	 * 查询钢卷状态
	 * 
	 * @param coilNo
	 * @return CoreReturnObject
	 * @throws SQLException
	 */
	public CoreReturnObject queryCoilStatus(String coilNo) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		String sql = XmlSqlParsersFactory.getSql("UIM010040_22.SELECT");
		//做生产规范标准判定
		TechDcsResult tdr = new TechDcsResult();
		cro = tdr.doTechDcs(sql,new Object[] { coilNo,coilNo});
		if(null == cro) {
			sql = XmlSqlParsersFactory.getSql("UIM010040_02.SELECT");
			cro = this.getDao("KgDao").ExcuteQuery(sql, new Object[] { coilNo });
		}
		return cro;
	}
	public CoreReturnObject queryfanxiu(String coilNo) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		String sql = XmlSqlParsersFactory.getSql("UIM010040_06.SELECT");
		cro = this.getDao("KgDao").ExcuteQuery(sql, new Object[] { coilNo });
		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, String curProgCd) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		Connection con = null;//pSta.getConnection();
		// 查询钢卷入库信息
		String sqlEntry = "select COIL_NO from c_tbk02_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 {
			if(!"1-81A-1".equals(yardAddr)&&!"1-81A-01".equals(yardAddr))//20160318新增一个虚拟垛位用于热轧移送by:wl
			{
			String sqlYard = XmlSqlParsersFactory.getSql("UIM010040_03.SELECT");
			PreparedStatement staYard = this.getDao("KgDao")
					.getPreparedStatement(sqlYard);
			staYard.setString(1, yardAddr);
			staYard.setString(2, yardAddr);
			staYard.setString(3, yardAddr);
			staYard.setString(4, yardAddr);
			ResultSet rsYard = staYard.executeQuery();
			con=staYard.getConnection();
			if (rsYard.next() && rsYard.getString("COIL_NO") != null
					&& !rsYard.getString("COIL_NO").equals("")) {
				cro.setV_errCode(new Integer(1));
				cro.setV_errMsg("该垛位上已经存在其他钢卷，请将钢卷移出该垛位或选择其他垛位！");
				try{
					rsYard.close(); //添加了关闭rsYard
					staYard.close(); //添加了关闭staYard
					con.close();
				} catch (Exception e) {
				}
				return cro;
			}
			try{
				rsYard.close(); //添加了关闭rsYard
				staYard.close(); //添加了关闭staYard
				con.close();
			} catch (Exception e) {
			}
			}
			// 查询钢卷出库信息
			String sqlOut = "select COIL_NO from c_tbk08_coil_yard where COIL_NO = ?";
			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_COL = substr(?,3,2)"
						+ " and CLF_ROW = 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();
					PreparedStatement pSta = null;
					ResultSet rs = null;
					try {
						// 将入库操作信息和垛位信息加入酸轧公共表
						String sqlucomm = "update c_tbk02_coil_comm set COIL_STAT = '2', CUR_PROG_CD = ?, 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[] { curProgCd, yardAddr,
												entryShift, entryGroup, reg_id,
												entryDtime, coilNo });

						// 将钢卷信息加入垛位表
						String sqluyard = "update c_tbk08_coil_yard set COIL_NO = ?,COIL_SOURCE = 0, "
								+ "MOD_ID  = ?, MOD_TIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')"
								+ " where CLF_NAME = substr(?,0,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, reg_id, yardAddr,
										yardAddr, yardAddr, yardAddr });

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

						String sqlqmaxseq = "select max(ROLL_SEQ) ROLL_SEQ from c_tbk08_coil_move";
						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_TBK02_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 = "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(?,?,?,?,?,?,?,?,?,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),decode(?,'1','01','2','05','3','','4','04'))";
						this.getDao("KgDao").ExcuteNonQuery(
								sqlimove,
								new Object[] { seq + 1, "", yardAddr, coilNo,
										reg_id, entryShift, entryGroup,
										entryDtime, reg_id, inType });
						
						//调用存货异动点存储过程
						
						
						//入库类型
						ProduceFactory PD= new ProduceFactory();
						String ISCHANGH = "select NVL(T.CR_PLANT_TRS_CLF,'NULL') CR_PLANT_TRS_CLF from TBH02_COIL_COMM T " +
								"where T.OLD_SAMPL_NO = ?  AND CR_PLANT_TRS_CLF='Z1'";
						pSta = this.getDao("KgDao").getPreparedStatement(
								ISCHANGH);
						pSta.setString(1, coilNo);
						rs = pSta.executeQuery();
						con=pSta.getConnection();
						if (rs.next()) {//20221114常化卷在热轧出库的时候加了热卷出库，所以此处只需要做常化入和酸轧入即可
							PD.ErpDataCover("HCOIL",coilNo,"55A","0","O","CHUA","常化转出");
							PD.ErpDataCover("CORICOIL",coilNo,"55A","0","O",reg_id,"酸轧原料入库");
						}
						else{
							PD.ErpDataCover("HCOIL",coilNo,"55A","0","O","蔡伟平","热轧转出");
							PD.ErpDataCover("CORICOIL",coilNo,"55A","0","O",reg_id,"酸轧原料入库");
						}
						rs.close();
						pSta.close();
						con.close();
						

						// 移送优特钢
//						con = this.getDao("KgDao").getConnection();
//						con.setAutoCommit(false);
//						String sqlString = "{call C_PKG_UIM.YTGCOIL(?,?)}";
//						CallableStatement cstm = con.prepareCall(sqlString);
//						
//						cstm.setString(1, coilNo);
//						cstm.registerOutParameter(2, java.sql.Types.VARCHAR);
//						cstm.execute();
						
						//移送优特钢
//						String sql6 = "{call C_PKG_UIM.YTGCOIL(?)}";
//						this.getDao("KgDao").ExcuteProcedure(sql6,new String[] {coilNo}, 
//								new String[0]);
						
						this.getDao("KgDao").CoreCommit(result);
					} catch (Exception ex) {
						ex.printStackTrace();
						this.getDao("KgDao").CoreRollBack(result);
						cro.setV_errCode(-1);
						cro.setV_errMsg(ex.getMessage());
					} finally {  
						try{
							//添加了最终关闭连接
							if(rs != null){
								rs.close();
							}
							if(pSta != null){
								pSta.close();
							}
							if(con!=null){
								con.close();
							}
						} catch (Exception e) {
						}
					}
				}
			}
		}

		return cro;
	}

	/**
	 * 修改钢卷规格
	 * 
	 * @param coilNo
	 * @param thk
	 * @param wth
	 * @param len
	 * @param actWgt
	 * @param calWgt
	 * @param india
	 * @param outdia
	 * @param crkCd1
	 * @param crkCd2
	 * @param crkCd3
	 * @param crkCd4
	 * @param crkCd5
	 * @param reg_id
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject updateCoilInfo(String coilNo, String thk,
			String wth, String len, String actWgt, String calWgt, String india,
			String outdia, String crkCd1, String crkCd2, String crkCd3,
			String crkCd4, String crkCd5, String fmAvg, String cdAvg,
			String reg_id, String cExtShape ,String crkCDDesc) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();

		Map result = this.getDao("KgDao").CoreBeginTranscation();
		try {
			// 查询钢卷进程状态
			String curProgCd = "";
			String befProgCd = "";
			String ordNo = "";
			String ordSeq = "";
			String sql = XmlSqlParsersFactory.getSql("UIM010040_04.SELECT");
			List list = this.getDao("KgDao").ExcuteQueryReturnList(sql,
					new Object[] { coilNo });
			Iterator it = list.iterator();
			if (it.hasNext()) {
				Map map = (Map) it.next();
				curProgCd = map.get("CUR_PROG_CD") != null ? map.get(
						"CUR_PROG_CD").toString() : "";
				befProgCd = map.get("BEF_PROG_CD") != null ? map.get(
						"BEF_PROG_CD").toString() : "";
				ordNo = map.get("ORD_NO") != null ? map.get("ORD_NO")
						.toString() : "";
				ordSeq = map.get("ORD_SEQ") != null ? map.get("ORD_SEQ")
						.toString() : "";
			}

			// 如果钢卷已经下发轧制计划或开始轧制，不能修改钢卷信息
			if ("PCB".equals(curProgCd) || "PCC".equals(curProgCd)) {
				cro.setV_errCode(-1);
				cro.setV_errMsg("钢卷已经下发轧制计划或已经开始轧制，不能修改钢卷信息！");
			} else {
				sql = XmlSqlParsersFactory.getSql("UIM010040_01.UPDATE");
				cro = this.getDao("KgDao").ExcuteNonQuery(
						sql,
						new Object[] { thk, wth, len, actWgt, calWgt, india,
								outdia, crkCd1, crkCd2, crkCd3, crkCd4, crkCd5,
								fmAvg, cdAvg, reg_id, cExtShape, crkCDDesc,coilNo });
				// 降余材
				if (("PCA".equals(curProgCd) || "PRC".equals(curProgCd))
						&& "2".equals(cExtShape)) {
					sql = XmlSqlParsersFactory.getSql("UIM010040_02.UPDATE");
					cro = this.getDao("KgDao").ExcuteNonQuery(sql,
							new Object[] { coilNo });
					//20140417 确保数据一致
					sql = XmlSqlParsersFactory.getSql("UIM010040_03.UPDATE");
					cro = this.getDao("KgDao").ExcuteNonQuery(sql,
							new Object[] { coilNo });
					// 订单标记
					this.SAVE_ORD_STS(ordNo, ordSeq, "", "", befProgCd,
							curProgCd, "CR", coilNo, actWgt, "UIM010040",
							reg_id);
					// 订单整理
					this.ORD_STS_MAIN("UIM010040", reg_id);
				}
			}

			this.getDao("KgDao").CoreCommit(result);
		} catch (Exception ex) {
			cro.setV_errCode(-2);
			cro.setV_errMsg("钢卷信息修改失败！");
			this.getDao("KgDao").CoreRollBack(result);
		}

		return cro;
	}

	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;

	}

	// select coil_no from c_tbk08_coil_yard where clf_name || clf_row ||
	// clf_col || clf_fl = '?'
	/**
	 * 查询库存区域
	 * 
	 * @param areaType
	 * @return CoreReturnObject
	 * @throws SQLException
	 */
	public CoreReturnObject queryYardAreas(Integer areaType)
			throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		String sql = XmlSqlParsersFactory.getSql("UIM010040_01.SELECT");
		// "select area_no,area_name from c_tbk08_coil_yard_area where area_type
		// = ?";
		cro = this.getDao("KgDao").ExcuteQuery(sql, new Object[] { areaType });
		return cro;
	}

	/**
	 * 根据区域编号查询仓库区分
	 * 
	 * @param areaNo
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryYardColsByArea(Integer areaNo)
			throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer
				.append("select distinct(clf_name||'-'||trim(to_char(clf_col,'00'))) clf_col\n");
		sqlBuffer
				.append(" from c_tbk08_coil_yard where area_no = ? order by clf_col\n");
		cro = this.getDao("KgDao").ExcuteQuery(sqlBuffer.toString(),
				new Object[] { areaNo });
		return cro;
	}

	/**
	 * 查询所有缺陷
	 * 
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryDefectInfo() throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("SELECT SM_CFNM\n");
		sqlBuffer.append(" , SM_CD\n");
		sqlBuffer.append("  FROM  TBZ00_COMMCD T   WHERE   LG_CD='H02001'\n");
		cro = this.getDao("KgDao").ExcuteQuery(sqlBuffer.toString());
		return cro;
	}

	// public CoreReturnObject DoSelect(String C_CUR_LOAD_LOC,String COIL_NO )
	// throws SQLException
	// {
	// CoreReturnObject cro = new CoreReturnObject();
	//			
	// //String sqlQuery = "select * from c_tbk02_coil_comm where CUR_LOAD_LOC =
	// '"+C_CUR_LOAD_LOC+"' and COIL_NO ='"+COIL_NO+"'";
	// String sqlQuery = "select * from c_tbk02_coil_comm where CUR_LOAD_LOC = ?
	// and COIL_NO = ? ";
	// //cro = this.getDao("KgDao").ExcuteQuery(sqlQuery);
	//			
	// cro = this.getDao("KgDao").ExcuteQuery(sqlQuery,new Object[] {
	// C_CUR_LOAD_LOC,COIL_NO });
	//			
	// return cro;
	//		
	// }
	// public CoreReturnObject addYard() throws SQLException {
	// CoreReturnObject cro = new CoreReturnObject();
	// StringBuffer sqlBuffer = new StringBuffer();
	// sqlBuffer
	// .append("insert into c_tbk08_coil_yard
	// (clf_no,clf_name,CLF_ROW,clf_col,clf_fl,area_no)values(?,'2',?,?,1,9)");
	// int clf_no = 1879;
	// for (int clf_row = 49; clf_row < 54; clf_row++) {
	// for (int clf_col = 1; clf_col < 17; clf_col++) {
	// cro = this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
	// new Object[] { clf_no, clf_row, clf_col });
	// clf_no++;
	// }
	// }
	// return cro;
	// }

	/**
	 * 订单进程标记
	 * 
	 * @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;
	}

	/**
	 * 查询钢卷其他信息
	 * 
	 * @param coilNo
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryCoilOtherInfo(String coilNo)
			throws SQLException {
		String sql = XmlSqlParsersFactory.getSql("UIM010040_05.SELECT");
		return this.getDao("KgDao").ExcuteQuery(sql, new Object[] { coilNo });
	}
	
				
	public CoreReturnObject SaveYtg(String coil_no)
				throws SQLException {
			CoreReturnObject cro = new CoreReturnObject();
			Connection conn = null;

			try {
				conn = this.getDao("KgDao").getConnection();
				conn.setAutoCommit(false);
			  
					//发送优特钢
				String sql6 = "{call C_PKG_UIM.YTGCOIL(?,?)}";
				this.getDao("KgDao").ExcuteProcedure(sql6,new String[] {coil_no,""}, 
						new String[0]);
				conn.commit();
	
			} catch (SQLException ex) {
				throw ex;
			}
			finally {
				if (conn != null && !conn.isClosed()) {
					conn.close();
				}
			}
			return cro;
			}
}