package UIM;

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

import UIB.COM.ProduceFactory;

import UIB.COM.XmlSqlParsersFactory;

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

/**
 * 冷轧原料库出库管理
 * 
 * @author siy
 * @date 2010-08-19
 */
public class UIM010092 extends CoreIComponent {
	// /**
	// * 查询酸轧计划
	// *
	// * @param ordNo
	// * 合同号
	// * @param ordSeq
	// * 订单号
	// * @param coilNo
	// * 热轧卷号
	// * @return CoreReturnObject
	// * @throws SQLException
	// */
	// public CoreReturnObject queryMillPlan(String coilNo) throws SQLException
	// {
	// CoreReturnObject cro = new CoreReturnObject();
	//
	// StringBuffer sqlBuffer = new StringBuffer();
	//
	// sqlBuffer.append("SELECT \n");
	// sqlBuffer.append("a.ROLL_MANA_NO --轧制单元号\n");
	// sqlBuffer.append(",a.ROLL_COIL_SEQ --单元内序号\n");
	// sqlBuffer.append(",a.COIL_EDT_SEQ --轧制序号\n");
	// sqlBuffer.append(",a.C_COIL_NO --冷卷号 \n");
	// sqlBuffer.append(",a.COIL_NO --热卷号 \n");
	// sqlBuffer.append(",a.SPEC_STL_GRD --酸轧牌号 \n");
	// sqlBuffer.append(",a.STL_GRD --热轧牌号 \n");
	// sqlBuffer.append(",a.STL_GRP_CD --钢号组 \n");
	// sqlBuffer.append(",a.COIL_THK --热卷厚度 \n");
	// sqlBuffer.append(",a.COIL_WTH --热卷宽度 \n");
	// sqlBuffer.append(",a.COIL_WGT --热卷重量 \n");
	// sqlBuffer.append(",b.CUR_LOAD_LOC --堆放位置 \n");
	// sqlBuffer.append(",a.ORD_NO --合同号 \n");
	// sqlBuffer.append(",a.ORD_SEQ --订单号 \n");
	// sqlBuffer.append(",a.ORD_THK --订单厚度 \n");
	// sqlBuffer.append(",a.ORD_WTH --订单宽度 \n");
	// sqlBuffer.append(",a.ORD_WGT --订单重量 \n");
	// sqlBuffer.append(",a.ORD_WGT_MIN --订单下限 \n");
	// sqlBuffer.append(",a.ORD_WGT_MAX --订单上限 \n");
	// sqlBuffer.append(",a.ORD_INDIA --订单内径 \n");
	// sqlBuffer.append(",a.ORD_OUTDIA --订单外径 \n");
	// sqlBuffer.append("FROM c_tbf03_spec_mill a, \n");
	// sqlBuffer.append("c_tbk02_coil_comm b \n");
	// sqlBuffer.append("WHERE a.COIL_NO = b.COIL_NO \n");
	// sqlBuffer.append("and a.STATUS_CD = 'A' \n");
	// sqlBuffer.append("and b.COIL_NO like ?||'%' \n");
	// sqlBuffer.append("ORDER BY a.ROLL_MANA_NO,a.ROLL_COIL_SEQ \n");
	//
	// cro = this.getDao("KgDao").ExcuteQuery(sqlBuffer.toString(),
	// new Object[] { coilNo });
	//
	// return cro;
	// }

	/**
	 * 查询退回出库卷信息
	 * 
	 * @param coilNo
	 *            热轧卷号
	 * @return CoreReturnObject
	 */
	public CoreReturnObject queryOutCoil(String ordNo, String ordSeq,
			String coilNo) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		String sql = XmlSqlParsersFactory.getSql("UIM010090_01.SELECT");
		cro = this.getDao("KgDao").ExcuteQuery(sql,
				new Object[] { ordNo, ordSeq, coilNo });

		return cro;
	}

	/**
	 * 原料库出库
	 * 
	 * @param coilNo
	 * @param regID
	 * @param trnfShift
	 * @param trnfGroup
	 * @param trnfDtime
	 * @param trnfType
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject saveCoilOut(String coilNo, String regID,
			String trnfShift, String trnfGroup, String trnfDtime,
			Integer trnfType) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		boolean crs = false;
		if (trnfType.intValue() == 9) {// 退帐面
			crs = returnCoil(coilNo, regID, trnfShift,
					trnfGroup, trnfDtime);
		} else {// 退实物
			// 清除原料库垛位表钢卷信息并将原料公共表钢卷垛位信息置为出库
			crs = cleanCoil(coilNo, regID, trnfShift,
					trnfGroup, trnfDtime);
			
			//调用存货异动点存储过程   20220408退账面不发送退回信息
			//ProduceFactory PD= new ProduceFactory();
			//PD.ErpDataCover("HCOIL",coilNo,"55A","0","N",regID,"热轧转出退回");
			//PD.ErpDataCover("CORICOIL",coilNo,"55A","0","O",regID,"酸轧原料退库");
			returnch(coilNo);
		}
		
 
		return cro;
	}

	/**
	 * 清空钢卷库存信息
	 * 
	 * @param outType
	 *            出库类型
	 * @param coilNo
	 *            钢卷号
	 * @param regId
	 *            出库人
	 * @param trnfShift
	 *            出库班次
	 * @param trnfGroup
	 *            出库班组
	 * @param tranTime
	 *            出库时间
	 * @return boolean
	 */
	private boolean cleanCoil(String coilNo, String regId,
			String trnfShift, String trnfGroup, String trnfTime)
			throws SQLException {
		Map result = this.getDao("KgDao").CoreBeginTranscation();

		try {
			// 根据钢卷号查询垛位编号
			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());
			} else {
				return false;
			}
			//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 });
           // 修改原料公共表钢卷操作信息
			String sql3 = XmlSqlParsersFactory.getSql("UIM010092_01.UPDATE");
			this.getDao("KgDao").ExcuteNonQuery(
					sql3,
					new Object[] { coilNo });
			
			// 修改原料表钢卷操作信息
			String sql4 = XmlSqlParsersFactory.getSql("UIM010092_02.UPDATE");
			this.getDao("KgDao").ExcuteNonQuery(sql4,
					new Object[] {  trnfShift, trnfGroup, regId, trnfTime,
					coilNo });
 
			this.getDao("KgDao").CoreCommit(result);
		} catch (Exception ex) {
			this.getDao("KgDao").CoreRollBack(result);
			ex.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * 退帐面回热轧
	 * 
	 * @param outType
	 * @param coilNo
	 * @param regId
	 * @param trnfShift
	 * @param trnfGroup
	 * @param trnfTime
	 * @return
	 * @throws SQLException
	 */
	private boolean returnCoil(String coilNo, String regId,
			String trnfShift, String trnfGroup, String trnfTime)
			throws SQLException {
		Map result = this.getDao("KgDao").CoreBeginTranscation();

		try {
			// 根据钢卷号查询垛位编号
			String sql1 = "select cur_load_loc from c_tbk02_coil_comm where coil_no = '"
					+ coilNo + "'";
			ResultSet rs = this.getDao("KgDao").ExceuteQueryForResultSet(sql1);

			String curLoadLoc = "";
			// 查询垛位
			if (rs.next()) {
				curLoadLoc = rs.getObject("CUR_LOAD_LOC").toString();
			} else {
				return false;
			}
			//20131014系统连接异常优化
        	this.getDao("KgDao").closeRs(rs);
			// 修改原料公共表钢卷操作信息
			String sql2 = XmlSqlParsersFactory.getSql("UIM010090_02.UPDATE");
			this.getDao("KgDao").ExcuteNonQuery(
					sql2,
					new Object[] { trnfShift, trnfGroup, regId, trnfTime,
							coilNo });
			// 修改原料_D表钢卷操作信息
			String sql4 = XmlSqlParsersFactory.getSql("UIM010090_04.UPDATE");
			this.getDao("KgDao").ExcuteNonQuery(sql4,
					new Object[] { coilNo });
			// 修改热轧公共表钢卷操作信息
			String sql3 = XmlSqlParsersFactory.getSql("UIM010090_03.UPDATE");
			this.getDao("KgDao").ExcuteNonQuery(sql3,
					new Object[] { curLoadLoc, coilNo });

			this.getDao("KgDao").CoreCommit(result);
		} catch (Exception ex) {
			this.getDao("KgDao").CoreRollBack(result);
			ex.printStackTrace();
			return false;
		}
		return true;
	}
     public CoreReturnObject deletereturn(String coilNo,String regId,
							String trnfShift, String trnfGroup, String trnfTime)
							throws SQLException {
						CoreReturnObject cro = new CoreReturnObject();
						Map result = this.getDao("KgDao").CoreBeginTranscation();
						try {
							String sql = " select * from TBH02_COIL_COMM A WHERE A.OLD_SAMPL_NO= '"
							+ coilNo + "'   AND A.ORD_FL='1' AND A.ORD_NO IS NOT NULL ";
							ResultSet rs = this.getDao("KgDao").ExceuteQueryForResultSet(sql);
					if (rs.next()) {
						this.getDao("KgDao").closeRs(rs);
						cro.setV_errCode(new Integer(-1));
						cro.setV_errMsg("钢卷已经在热轧进行了充当，无法撤销！");
						return cro;
					}
				     else {
				    	 this.getDao("KgDao").closeRs(rs);
						// 撤销退回的卷入冷轧库
						String sql1 = XmlSqlParsersFactory
								.getSql("UIM010090_05.UPDATE");
						this.getDao("KgDao").ExcuteNonQuery(sql1,
								new Object[] {trnfShift, trnfGroup,regId,trnfTime,coilNo});
						// 修改热轧库存数据为移送完成并且钢卷出库
						String sql2 = XmlSqlParsersFactory
								.getSql("UIM010090_06.UPDATE");
						this.getDao("KgDao").ExcuteNonQuery(sql2,
								new Object[] { coilNo });
					}
					
					this.getDao("KgDao").CoreCommit(result);
				} catch (SQLException ex) {
					this.getDao("KgDao").CoreRollBack(result);
					throw ex;
				}
				
				return cro;
				}
					
					public CoreReturnObject returnch(String coil_no)
					throws SQLException {
				CoreReturnObject cro = new CoreReturnObject();
				Connection conn = null;
				
				try {
					conn = this.getDao("KgDao").getConnection();
					conn.setAutoCommit(false);
				  
						//发送优特钢
					String sql6 = "{call CK_RZ_COMM.RETURN_ZY(?)}";
					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;
				}
}