package UIM;

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.XmlSqlParsersFactory;

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

/**
 * 冷轧原料库库存数据移动
 * 
 * @author siy
 * @date 2010-08-27
 */
public class UIM010100 extends CoreIComponent {
	/**
	 * 查询钢卷位置
	 * 
	 * @param coilNo
	 *            热轧卷号
	 * @return CoreReturnObject
	 * @throws SQLException
	 */
	public CoreReturnObject queryLoadLoc(String coilNo) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();

		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("select k.cur_load_loc from c_tbk02_coil_comm k,\n");
		sqlBuffer.append("c_tbk08_coil_yard y,c_tbk08_coil_yard_area a \n");
		sqlBuffer.append("where k.COIL_NO = y.COIL_NO \n");
		sqlBuffer.append("and y.AREA_NO = a.AREA_NO \n");
		sqlBuffer.append("and a.AREA_TYPE = 1 \n");
		sqlBuffer.append("and k.coil_no = ?");

		List list = this.getDao("KgDao").ExcuteQueryReturnList(
				sqlBuffer.toString(), new Object[] { coilNo });// .ExcuteQuery(sql);

		String curLoadLoc = "";

		Iterator it = list.iterator();
		while (it.hasNext()) {
			Map map = (Map) it.next();

			curLoadLoc = map.get("cur_load_loc").toString();
		}
		// System.out.println(count);
		cro.setResult(curLoadLoc);

		return cro;
	}

	/**
	 * 查询区域中的垛位及钢卷
	 * 
	 * @param areaType
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryYardCoils(String areaType) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append(" select 'FALSE' chk\n");
		sqlBuffer
				.append(" ,y.CLF_NAME||'-'||trim(to_char(y.CLF_COL,'00'))||decode(y.CLF_FL,1,'A',2,'B',3,'C')||'-'||trim(to_char(y.CLF_ROW,'00')) cur_load_loc\n");
		sqlBuffer.append(" ,k.coil_no\n");
		sqlBuffer.append(" from c_tbk08_coil_yard y,c_tbk02_coil_comm k\n");
		sqlBuffer
				.append(" where y.CLF_NAME||'-'||trim(to_char(y.CLF_COL,'00'))||decode(y.CLF_FL,1,'A',2,'B',3,'C')||'-'||trim(to_char(y.CLF_ROW,'00')) = k.CUR_LOAD_LOC(+)\n");
		sqlBuffer.append(" and y.AREA_NO = ? and y.CLF_NAME NOT IN('6','7')\n");
		sqlBuffer.append(" order by y.CLF_COL,y.CLF_ROW,y.CLF_FL\n");
		cro = this.getDao("KgDao").ExcuteQuery(sqlBuffer.toString(),
				new Object[] { areaType });
		return cro;
	}
	/*
	 * 酸洗库存数据查询 20171129*/
	public CoreReturnObject queryYardCoilsSx(String areaType) throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append(" select 'FALSE' chk\n");
		sqlBuffer
				.append(" ,y.CLF_NAME||'-'||trim(to_char(y.CLF_COL,'00'))||decode(y.CLF_FL,1,'A',2,'B',3,'C')||'-'||trim(to_char(y.CLF_ROW,'00')) cur_load_loc\n");
		sqlBuffer.append(" ,k.coil_no\n");
		sqlBuffer.append(" from c_tbk08_coil_yard y,c_tbk02_coil_comm k\n");
		sqlBuffer
				.append(" where y.CLF_NAME||'-'||trim(to_char(y.CLF_COL,'00'))||decode(y.CLF_FL,1,'A',2,'B',3,'C')||'-'||trim(to_char(y.CLF_ROW,'00')) = k.CUR_LOAD_LOC(+)\n");
		sqlBuffer.append(" and y.AREA_NO = ? and y.CLF_NAME='6'\n");
		sqlBuffer.append(" order by y.CLF_COL,y.CLF_ROW,y.CLF_FL\n");
		cro = this.getDao("KgDao").ExcuteQuery(sqlBuffer.toString(),
				new Object[] { areaType });
		return cro;
	}

	/**
	 * 钢卷移垛
	 * 
	 * @param moveList
	 *            移垛钢卷垛位信息
	 * @param regId
	 *            移垛人
	 * @param moveOrder
	 *            移垛班次
	 * @param moveGroup
	 *            移垛班组
	 * @param moveDate
	 *            移垛时间
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject moveColdCoil(ArrayList<String[]> moveList,
			String regId, String moveOrder, String moveGroup, String moveDate)
			throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		StringBuffer sqlBuffer = null;

		Map result = this.getDao("KgDao").CoreBeginTranscation();
		try {
			for (int i = 0; i < moveList.size(); i++) {
				String[] moveCoil = moveList.get(i);
				// 更新钢卷公共表
				sqlBuffer = new StringBuffer();
				sqlBuffer.append("\n");
				sqlBuffer
						.append("UPDATE C_TBK02_COIL_COMM K SET K.CUR_LOAD_LOC = ?\n");
				sqlBuffer
						.append(",K.CUR_LOAD_LOC_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') WHERE K.COIL_NO = ?\n");
				this.getDao("KgDao").ExcuteNonQuery(sqlBuffer.toString(),
						new Object[] { moveCoil[1], moveCoil[2] });

				// 更新垛位表
				sqlBuffer = new StringBuffer();
				sqlBuffer
						.append("UPDATE C_TBK08_COIL_YARD Y SET Y.COIL_NO = ?,Y.MOD_ID  = ?\n");
				sqlBuffer
						.append(" , Y.MOD_TIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')\n");
				sqlBuffer
						.append(" WHERE Y.CLF_NAME = SUBSTR(?,0,1) AND CLF_COL = SUBSTR(?,3,2)\n");
				sqlBuffer.append(" AND CLF_ROW = SUBSTR(?,7,2) \n");
				sqlBuffer
						.append(" AND CLF_FL = DECODE(SUBSTR(?,5,1),'A','1','B','2','C','3','1')\n");
				this.getDao("KgDao").ExcuteNonQuery(
						sqlBuffer.toString(),
						new Object[] { "", regId, moveCoil[0], moveCoil[0],
								moveCoil[0], moveCoil[0] });

				this.getDao("KgDao").ExcuteNonQuery(
						sqlBuffer.toString(),
						new Object[] { moveCoil[2], regId, moveCoil[1],
								moveCoil[1], moveCoil[1], moveCoil[1] });

				// 如果钢卷已退回热轧，需要同步更新热轧成品卷垛位
				int hCoilCount = 0;
				String sql = XmlSqlParsersFactory.getSql("UIM010100_01.SELECT");
				List list = this.getDao("KgDao").ExcuteQueryReturnList(sql,
						new Object[] { moveCoil[2] });
				Iterator it = list.iterator();
				if (it.hasNext()) {
					Map map = (Map) it.next();
					try {
						hCoilCount = Integer.parseInt(map.get("COUNT")
								.toString());
					} catch (Exception ex) {

					}
				}
				if (hCoilCount > 0) {
					sql = XmlSqlParsersFactory.getSql("UIM010100_01.UPDATE");
					this.getDao("KgDao").ExcuteNonQuery(sql,
							new Object[] { moveCoil[1], moveCoil[2] });
				}

				// 移垛完成后将移垛记录加入移垛记录表，便于跟踪钢卷移动记录
				sqlBuffer = new StringBuffer();
				sqlBuffer.append("INSERT INTO C_TBK08_COIL_MOVE (ROLL_SEQ\n");
				sqlBuffer
						.append(" ,CUR_LOAD_LOC_F,CUR_LOAD_LOC_T,COIL_NO,REG_ID\n");
				sqlBuffer
						.append(" ,REG_SHIFT,REG_GROUP,REG_DTIME,REG_USE_DTIME,MOVE_TYPE)\n");
				sqlBuffer
						.append(" VALUES((SELECT MAX(ROLL_SEQ)+1 ROLL_SEQ FROM C_TBK08_COIL_MOVE),?,?,?,?,?,?,?,TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),'00')\n");
				this.getDao("KgDao").ExcuteNonQuery(
						sqlBuffer.toString(),
						new Object[] { moveCoil[0], moveCoil[1],
								moveCoil[2], regId, moveOrder, moveGroup,
								moveDate });
			}
			this.getDao("KgDao").CoreCommit(result);
		} catch (Exception e) {
			e.printStackTrace();
			this.getDao("KgDao").CoreRollBack(result);
		}

		return cro;
	}
}