package UIK.UIK02;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import UIB.COM.XmlSqlParsersFactory;

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

/**
 * 酸轧计划查询
 * 
 * @author siy
 * @date 2010-09-16
 */
public class UIK020040 extends CoreIComponent {
	/**
	 * 查询酸轧轧制单元
	 * 
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryRollManaNo() throws SQLException {
		CoreReturnObject cro = new CoreReturnObject();
		StringBuffer sqlBuffer = new StringBuffer();
		sqlBuffer.append(" select * from(\n");
		sqlBuffer.append(" select distinct r.roll_mana_no,\n");
		sqlBuffer.append(" row_number() over(order by r.roll_mana_no desc) seq \n");
		sqlBuffer.append(" from c_tbf03_spec_roll r) \n");
		sqlBuffer.append(" where seq < 21\n");
		sqlBuffer.append(" order by seq,roll_mana_no desc\n");

		PreparedStatement pSta = this.getDao("KgDao").getPreparedStatement(sqlBuffer.toString());
		ResultSet rs = pSta.executeQuery();
		ArrayList<String[]> list = new ArrayList<String[]>();
		String[] param = null;
		while (rs.next()) {
			param = new String[2];
			param[0] = rs.getString("SEQ");
			param[1] = rs.getString("ROLL_MANA_NO");
			list.add(param);
		}

		cro.setResult(list);
		rs.close();
		Connection con = pSta.getConnection();
		pSta.close();
		con.close();
		return cro;
	}
	/**
	 * 查询流程卡热卷对应的冷卷号
	 * @param hCoil
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject queryPrintInfor(String hCoil) throws SQLException
	{
		CoreReturnObject cro = new CoreReturnObject();
		String sql = "select * from(select distinct r.OLD_SAMPL_NO,row_number() over(order by r.OLD_SAMPL_NO desc) seq from c_tbl02_coil_comm r where r.H_COIL_NO1 = '"+hCoil+"'AND R.OLD_SAMPL_NO IS NOT NULL)";

		PreparedStatement pSta = this.getDao("KgDao").getPreparedStatement(sql);
		ResultSet rs = pSta.executeQuery();
		ArrayList<String[]> list = new ArrayList<String[]>();
		String[] param = null;
		while (rs.next()) {
			param = new String[2];
			param[0] = rs.getString("SEQ");
			param[1] = rs.getString("OLD_SAMPL_NO");
			list.add(param);
		}

		cro.setResult(list);
		rs.close();
		Connection con = pSta.getConnection();
		pSta.close();
		con.close();
		return cro;

	}
	/**
	 * 查询冷卷对应的信息
	 * @param cCoil
	 * @return
	 * @throws SQLException
	 */
	public CoreReturnObject querycCoilInfor(String cCoil) throws SQLException
	{
			CoreReturnObject cro = new CoreReturnObject();
							
			String sqlQuery	=	"select t.ACT_WGT,t.COIL_THK,t.COIL_WTH,t.CUR_LOAD_LOC,decode(t.TOT_DEC_GRD,'1','合格','2','不合格','3','外观异常','4','废品') TOT_DEC_GRD from c_tbl02_coil_comm t where t.OLD_SAMPL_NO = '"+cCoil+"'";
				       
			cro = this.getDao("KgDao").ExcuteQuery(sqlQuery);
					
			return cro;

	}
	/**
	 * 查询酸轧计划
	 * @param rollManaNo
	 * @param pFromDate
	 * @param pToDate
	 * @param mFromDate
	 * @param mToDate
	 * @param ordNo
	 * @param ordSeq
	 * @return
	 * @throws SQLException
	 */
//	public CoreReturnObject queryRollPlan(String rollManaNo,String rollManaNo2,String pFromDate,
//			String pToDate, String mFromDate, String mToDate, String ordNo,
//			String ordSeq) throws SQLException {
//		CoreReturnObject cro = new CoreReturnObject();
//		StringBuffer sqlBuffer = new StringBuffer();
//		sqlBuffer.append("SELECT   A.ROLL_COIL_SEQ SEQ --轧辊内顺序\n");
//		
//		//sqlBuffer.append(" ,ROW_NUMBER() OVER ( PARTITION BY SUBSTR(H.OLD_SAMPL_NO,1,10) ORDER BY A.ROLL_COIL_SEQ ) PIHAO_SEQ            -- 序号\n");
//		sqlBuffer.append(" ,A.STL_GRD                                        --热轧牌号 \n");
//		sqlBuffer.append(" ,B.COIL_THK                                                  --热卷厚度\n");	
//		
//		sqlBuffer.append(" ,B.COIL_INDIA                                                  --热卷内径\n");
//		sqlBuffer.append(" ,B.COIL_OUTDIA                                                  --热卷外径\n");
//		sqlBuffer.append(" ,B.ORD_FL                                                         --订单区分\n");
//		  
//		sqlBuffer.append(" ,B.COIL_WTH                                                  --热卷宽度\n");
//		sqlBuffer.append(" ,B.COIL_LEN                                                     --热卷长度\n");
//		sqlBuffer.append(" ,B.ACT_WGT COIL_WGT                                                     --热卷重量 \n");		
//		sqlBuffer.append(" ,E.C_ORD_THK ORD_THK                                         --订单厚度\n");
//		sqlBuffer.append(" ,E.C_ORD_WTH ORD_WTH                                        --订单宽度\n");
//		sqlBuffer.append(" ,E.ORD_UNIT_WGT ORD_WGT                                        --订单重量\n");
//		
//		sqlBuffer.append(" ,E.C_SPEC_STL_GRD                                         --冷轧牌号\n");
//		sqlBuffer.append(" ,E.C_ORD_INDIA INSTR_COIL_INDIA                                                  --订单内径\n");
//		sqlBuffer.append(" ,E.C_ORD_OUTDIA INSTR_COIL_OUTDIA                                                  --订单外径\n");
//		sqlBuffer.append(" ,E.C_ORD_LEN INSTR_COIL_LEN                                                  --订单长度\n");
//		
//
// 		sqlBuffer.append(" ,E.ORD_THK H_ORD_THK                                         --热轧订单厚度\n");
//		sqlBuffer.append(" ,E.ORD_WTH H_ORD_WTH                                        --热轧订单宽度\n");
//		sqlBuffer.append(" ,E.ORD_LEN H_ORD_LEN                                        --热轧订单长度\n");		
//		sqlBuffer.append(" ,E.ORD_INDIA H_ORD_INDIA                                                  --热轧订单内径\n");
//		sqlBuffer.append(" ,E.ORD_OUTDIA H_ORD_OUTDIA                                                 --热轧订单外径\n");
//
// 
//		
//		sqlBuffer.append(" ,A.COIL_NO                                            --热卷号\n");
//		sqlBuffer.append(" ,A.C_COIL_NO OLD_SAMPL_NO                  --实绩冷卷号\n");
//		
//		sqlBuffer.append(" ,(select E.C_EXTSHAPE_REQ from TBE02_ORD_PRC E WHERE E.ORD_SEQ = A.ORD_SEQ AND E.ORD_NO = A.ORD_NO ) C_EXTSHAPE_REQ                --表面要求\n");
//		sqlBuffer.append(" ,(select E.ACID_PROD_PATH from TBE02_ORD_PRC E WHERE E.ORD_SEQ = A.ORD_SEQ AND E.ORD_NO = A.ORD_NO ) ACID_PROD_PATH                  --工艺流程\n");
//
//		
//		sqlBuffer.append(" -- ,SUBSTR(A.SLAB_NO,1,10)                          CHARGE_NO  --炉号\n");
//		sqlBuffer.append(" ,T2.SM_CFNM                                      BZ   --发货方式\n");
//		sqlBuffer.append(" ,T3.SM_CFNM ORDCUST_CD              --发货单位\n");
//		sqlBuffer.append(" ,T1.SM_CFNM                          --订货单位\n");
//		sqlBuffer.append(" --,A.C_COIL_NO                                                     --酸轧卷号\n");
//		sqlBuffer.append(" ,CASE  WHEN (A.STATUS_CD = 'A' AND B.COIL_STAT = '2' )    THEN '轧制作业待机'\n");
//		//sqlBuffer.append(" WHEN (A.STATUS_CD = 'B' AND B.COIL_STAT = '3' AND H.COIL_STAT = '1')       THEN '上料进行'\n");
//		sqlBuffer.append(" WHEN (A.STATUS_CD = 'E'                           )  THEN '轧制吊销'\n");
//		sqlBuffer.append(" WHEN (A.STATUS_CD = 'B'                           )  THEN '上料完成'\n");
//		sqlBuffer.append(" WHEN (A.STATUS_CD = 'C')                            THEN '轧制进行'\n");
//		sqlBuffer.append(" WHEN (A.STATUS_CD = 'D' AND H.MISSNO_CLF_CD is null) THEN '轧制完成'\n");
//		sqlBuffer.append(" WHEN (A.STATUS_CD = 'D' AND H.MISSNO_CLF_CD =  '1')  THEN '轧废'\n");
//		sqlBuffer.append(" ELSE ''                                      END    STATUS_CD\n");
//		sqlBuffer.append(" ,A.ROLL_MANA_NO                                   --轧制单元号\n");
//		sqlBuffer.append(" ,A.ORD_NO                         --合同号\n");
//		sqlBuffer.append(" ,A.ORD_SEQ                      --订单号\n");
//		sqlBuffer.append(" ,H.CUR_LOAD_LOC  C_COIL_LOAD_LOC                    --酸轧卷位置\n");
//		sqlBuffer.append(" ,H.ACT_WGT                                --酸轧卷实际重量\n");
//		sqlBuffer.append(" ,B.CUR_LOAD_LOC  H_COIL_LOAD_LOC     --热轧卷位置\n");
//		sqlBuffer.append(" ,DECODE(K.DSN_KIND, '1', 'Y', 'N') AS INGR_YN     --客户特殊要求\n");
//		sqlBuffer.append(" ,H.MILL_DTIME               --轧制时间\n");
//		sqlBuffer.append(" ,DECODE(B.INGR_DEC_GRD,'0','待判定','1','合格','2','不合格','') INGR_DEC_GRD    --成份等级 \n");
//		sqlBuffer.append(" FROM   C_TBF03_SPEC_MILL A  \n");
//		sqlBuffer.append(" ,C_TBK02_COIL_COMM B\n");
//		sqlBuffer.append(" ,TBE02_ORD_PRC   E\n");
//		sqlBuffer.append(" ,C_TBL02_COIL_COMM H\n");
//		sqlBuffer.append(" ,TBA01_ORD_LINE  L\n");
//		sqlBuffer.append(" ,(SELECT T.CUST_CD SM_CD,T.CUST_NM SM_CFNM FROM TBZ00_CUSTOMER T) T1\n");
//		sqlBuffer.append(" ,(SELECT   SM_CD, SM_CFNM FROM TBZ00_COMMCD  WHERE LG_CD = 'A01012') T2\n");
//		sqlBuffer.append(" ,(SELECT T.CUST_CD SM_CD,T.CUST_NM SM_CFNM FROM TBZ00_CUSTOMER T) T3\n");
//		sqlBuffer.append(" ,(SELECT DISTINCT ORD_NO, ORD_SEQ, DSN_KIND\n");
//		sqlBuffer.append(" FROM TBB01_ORD_INGR\n");
//		sqlBuffer.append(" WHERE DSN_KIND = '1') K\n");
//		sqlBuffer.append(" WHERE  A.ORD_NO           = E.ORD_NO(+)\n");
//		sqlBuffer.append(" AND A.ORD_SEQ          = E.ORD_SEQ(+) \n");
//		sqlBuffer.append(" AND A.ORD_NO           = K.ORD_NO(+)\n");	
//		sqlBuffer.append(" AND A.ORD_SEQ          = K.ORD_SEQ(+)\n");
//		sqlBuffer.append(" AND A.ORD_NO           =L.ORD_NO(+)\n");
//		sqlBuffer.append(" AND A.ORD_SEQ         = L.ORD_SEQ(+)\n");
//		sqlBuffer.append(" AND A.COIL_NO          =  B.COIL_NO(+)\n");
//		sqlBuffer.append(" AND B.CUST_CD          =  T1.SM_CD(+)  \n");
//		sqlBuffer.append(" AND E.ORDCUST_CD       =  T3.SM_CD(+)\n");
//		sqlBuffer.append(" AND L.DLIV_TP          =  T2.SM_CD(+)  \n");
//		sqlBuffer.append(" AND A.C_COIL_NO      =  H.COIL_NO(+)\n");
//		sqlBuffer.append(" AND A.COIL_NO = B.COIL_NO(+)\n");
//		sqlBuffer.append(" AND A.STATUS_CD NOT LIKE 'R'\n");
//		sqlBuffer.append(" AND A.ROLL_MANA_NO  >= ? ||'%' AND A.ROLL_MANA_NO <= ?||'%'\n");
//		//sqlBuffer.append(" AND A.ROLL_MANA_NO  like ?||'%'\n");
//		sqlBuffer.append(" AND SUBSTR(A.REG_DTIME,1,8)    BETWEEN ? AND ?\n");
//		sqlBuffer.append(" AND NVL(H.MILL_DTIME,'000000')  BETWEEN ?||'000000' AND ?||'999999'\n");
//		sqlBuffer.append(" and a.ord_no like ?||'%'\n");
//		sqlBuffer.append(" and a.ord_seq like ?||'%'\n");				
//		//sqlBuffer.append(" ORDER BY A.ROLL_MANA_NO,A.ROLL_COIL_SEQ\n");
//		//sqlBuffer.append(" ORDER BY A.ROLL_MANA_NO,A.ROLL_COIL_SEQ,A.C_COIL_NO\n");
//		sqlBuffer.append(" ORDER BY A.ROLL_MANA_NO,A.ROLL_COIL_SEQ\n");
//	//	sqlBuffer.append(" \n");
//
//		cro = this.getDao("KgDao").ExcuteQuery(
//				sqlBuffer.toString(),
//				new Object[] { rollManaNo,rollManaNo2,pFromDate, pToDate, mFromDate,
//						mToDate, ordNo, ordSeq });
//
//		return cro;
//	}
//	
	public CoreReturnObject queryRollPlan(String rollManaNo,String rollManaNo2,String pFromDate,String pToDate, String mFromDate, String mToDate, String ordNo,String ordSeq,Boolean flag) throws SQLException 
	{
		CoreReturnObject cro = new CoreReturnObject();
		
		if(!flag)
		{

				String sqlQuery = XmlSqlParsersFactory.getSql("UIK020040_08.SELECT");
				cro = this.getDao("KgDao").ExcuteQuery(sqlQuery,new Object[]{rollManaNo,rollManaNo2, mFromDate, mToDate, ordNo, ordSeq});
				return cro;
				
		}
		else
		{
			
				
				String sqlQuery = XmlSqlParsersFactory.getSql("UIK020040_09.SELECT");    
				cro = this.getDao("KgDao").ExcuteQuery(sqlQuery,new Object[]{ pFromDate, pToDate, mFromDate, mToDate, ordNo, ordSeq});
				return cro;
			
			
		}
	}
}