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 CoreFS.SA01.CoreIComponent;
import CoreFS.SA06.CoreReturnObject;

/**
 * 成品库移垛操作
 * 
 * @author FLZ
 * @date 2011-08-31
 */
public class UIM010250 extends CoreIComponent {
	/**
	 * 查询传位置上的钢卷
	 * 
	 * @param yard
	 *          垛位  
	 * @return CoreReturnObject
	 * @throws SQLException
	 */
	public CoreReturnObject queryCoilNo(String yard)throws Exception
	{
		CoreReturnObject cro = new CoreReturnObject();
		
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("SELECT  A.OLD_SAMPL_NO COIL_NO, A.CUR_LOAD_LOC \n");
		sqlBuffer.append("  FROM C_TBC02_COIL_COMM A \n");
		sqlBuffer.append(" WHERE c_Pkg_Uim.GET_FINALYARDNO_BYYARDFLAG(A.CUR_LOAD_LOC) = c_Pkg_Uim.GET_FINALYARDNO_BYYARDFLAG(?) AND A.CUR_LOAD_LOC IS NOT NULL\n");
		
		cro = this.getDao("KgDao").ExcuteQuery(sqlBuffer.toString(),
				new Object[] { yard });
		return cro;
	}
	
	/**
	 * 查询钢卷的现在的垛位
	 * 
	 * @param coilNo
	 *          钢卷号  
	 * @return CoreReturnObject
	 * @throws SQLException
	 */
	public CoreReturnObject queryLoadLoc(String coilNo)throws Exception
	{
		CoreReturnObject cro = new CoreReturnObject();
		
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append("SELECT DECODE(A.CUR_LOAD_LOC, '', '无垛位', A.CUR_LOAD_LOC, A.CUR_LOAD_LOC) CUR_LOAD_LOC, \n");
		sqlBuffer.append("DECODE(A.COIL_STAT,'1','未入库','2','在库','3','出库/结束',null) COIL_STAT  \n");
		sqlBuffer.append(" FROM C_TBC02_COIL_COMM A \n");
		sqlBuffer.append(" WHERE A.OLD_SAMPL_NO = ? \n");
		
		cro = this.getDao("KgDao").ExcuteQuery(sqlBuffer.toString(),
				new Object[] { coilNo });
		return cro;
	}

	/**
	 * 查询钢卷的移垛记录
	 * 
	 * @param coilNo
	 *          钢卷号  
	 * @return CoreReturnObject
	 * @throws SQLException
	 */
	public CoreReturnObject queryCoilNoLOG(String coilNo)throws Exception
	{
		CoreReturnObject cro = new CoreReturnObject();
	
	StringBuffer sqlBuffer = new StringBuffer();
	sqlBuffer.append("SELECT A.CUR_LOAD_LOC_F, A.CUR_LOAD_LOC_T,  A.CR_NO, A.REG_DTIME, \n");
	sqlBuffer.append("       (SELECT U.USERNAME FROM CORE_APP_USER U WHERE U.USERID = A.REG_ID) REG_ID, \n");
	sqlBuffer.append("       DECODE(A.REG_SHIFT,'0','常白班','1','早班','2','中班','3','晚班') REG_SHIFT, \n");
	sqlBuffer.append("       DECODE(A.REG_GROUP,'0','常白班','1','甲班','2','乙班','3','丙班','4','丁班') REG_GROUP, \n");
	sqlBuffer.append("       (SELECT U.USERNAME FROM CORE_APP_USER U WHERE U.USERID = A.REG_USE_ID) REG_USE_ID, \n");
	sqlBuffer.append("       A.REG_USE_DTIME \n");
	sqlBuffer.append("  FROM C_TBK08_COIL_MOVE A \n");
	sqlBuffer.append("  WHERE A.COIL_NO = ? order by A.REG_USE_DTIME DESC\n");
	
	cro = this.getDao("KgDao").ExcuteQuery(sqlBuffer.toString(),
			new Object[] { coilNo });
	return cro;
	}
	
	
	/**
	 * 移垛
	 * 
	 * @param coilNo
	 *          钢卷号组  
	 * @return CoreReturnObject
	 * @throws SQLException
	 */
	public CoreReturnObject moveYard(String[] coilNo,String yardNOW,String yardTO,String regID,String regShift,String regGroup)throws Exception
	{
		//更新公共表
		CoreReturnObject cro1 = new CoreReturnObject();
		String sql1 = "UPDATE C_TBC02_COIL_COMM A \n SET A.CUR_LOAD_LOC = ? \n" +
				",A.CUR_LOAD_LOC_DTIME = TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') \n" +
				" WHERE A.OLD_SAMPL_NO = ? \n";
		
		Object[] obj1 = new Object[coilNo.length + 1];
		obj1[0] = yardTO;
		obj1[1] = coilNo[0];
		for(int i = 1;i < coilNo.length;i++)
		{
			sql1 += "OR A.OLD_SAMPL_NO = ? \n";
			obj1[i+1] = coilNo[i];
		}
		//sql1 += "; COMMIT \n";
		cro1 = this.getDao("KgDao").ExcuteNonQuery(sql1,obj1);
		//添加移垛记录
		CoreReturnObject cro2 = new CoreReturnObject();
		String sql2 = "INSERT INTO C_TBK08_COIL_MOVE (ROLL_SEQ,CUR_LOAD_LOC_F,CUR_LOAD_LOC_T,COIL_NO,REG_USE_ID,REG_ID" +
				",REG_SHIFT,REG_GROUP,REG_DTIME,REG_USE_DTIME,MOVE_TYPE)" +
				"VALUES" +
				"\n((SELECT MAX(ROLL_SEQ)+1 ROLL_SEQ FROM C_TBK08_COIL_MOVE)" + //主键 ROLL_SEQ
				",'" + yardNOW + 	//CUR_LOAD_LOC_F
				"','" + yardTO +	//CUR_LOAD_LOC_T
				"',?" + 			//COIL_NO
				",'" + regID +		//REG_USE_ID
				"','" +	regID +			//REG_ID
				"','" +	regShift +			//REG_SHIFT
				"','" +	regGroup +			//REG_GROUP
				"',TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS')" +				//REG_DTIME
				",TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'),'00')";//REG_USE_DTIME
		for(int i = 0;i<coilNo.length;i++)
		{
			cro2 = this.getDao("KgDao").ExcuteNonQuery(sql2,new Object[]{coilNo[i]});
		}
		//sqlBuffer.append("; COMMIT \n");	
//		if(cro1.getV_errCode() != 1 || cro2.getV_errCode() != 1)
//		{
//			cro2.setV_errMsg("fall to move! Querry again.");
//		}
		return cro2;
		
	}
	
	//垛位表查询 返回区域垛位情况
	public CoreReturnObject queryLoad(String kuNo,String areaNoMin,String areaNoMax)throws Exception
	{
		CoreReturnObject cro = new CoreReturnObject();

		StringBuffer sqlBuffer = new StringBuffer();
		
		sqlBuffer.append("select decode(y.AREA_NO,'10','3','11','4','12','5')||'-'||trim(to_char(y.CLF_COL,'00'))||decode(y.CLF_FL,1,'A',2,'B',3,'C')||'-'||trim(to_char(y.CLF_ROW,'000')) cur_load_loc, k.OLD_SAMPL_NO COIL_NO \n");
		sqlBuffer.append("from c_tbk08_coil_yard y,c_tbc02_coil_comm k \n");
		sqlBuffer.append("where decode(y.AREA_NO,'10','3','11','4','12','5')||'-'||trim(to_char(y.CLF_COL,'00'))||decode(y.CLF_FL,1,'A',2,'B',3,'C')||'-'||trim(to_char(y.CLF_ROW,'000')) = k.CUR_LOAD_LOC(+) \n");
		sqlBuffer.append("and y.CLF_ROW >= " +areaNoMin + " \n");
		sqlBuffer.append("and y.CLF_ROW <= " +areaNoMax + " \n");
		sqlBuffer.append("and decode(y.AREA_NO,'10','3','11','4','12','5') = '" +kuNo + "' \n");
		sqlBuffer.append("order by y.CLF_COL,y.CLF_ROW,y.CLF_FL");
		

		cro = this.getDao("KgDao").ExcuteQuery(sqlBuffer.toString(),
				new Object[] { });
		
		return cro;
	}
}