XinYu_LZMES/.svn/pristine/dd/dd97a93419e0deeba1f925c50aca16f280228733.svn-base

package QCM.JHY01.JHY0102;

import java.util.HashMap;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.ibatis.session.SqlSession;

import CoreFS.SA01.CoreIComponent;
import CoreFS.SA06.CoreReturnObject;
import QCM.COMMUNAL.QCM02.SqlJoint;
import UIB.COM.XmlSqlParsersFactory;

public class QuerryJhyElements extends CoreIComponent{

	CoreReturnObject cro = new CoreReturnObject();	
	
	SqlSession SqlSession = QCM.COMMUNAL.SqlSessionBuilder.openSqlSession();
	QCM.COMMUNAL.OrderSqMapper mapper = SqlSession.getMapper(QCM.COMMUNAL.OrderSqMapper.class);
	
	public String StrSky(String begintime,String endtime){
		String sql = "select (to_date('"+endtime+"','yyyy-MM-dd') - to_date('"+begintime+"','yyyy-MM-dd')) SKY from dual";
		HashMap list = mapper.queryOne(sql);
		Double ble = Double.parseDouble(list.get("SKY").toString());
		String tstr="FALSE";
		if(ble>=0 && ble <=30 ){
			tstr="TRUE";
		}
		return tstr;
	}
	
	/**
	 * 根据条件查询成分检验实绩
	 * @param gradeName
	 * @param heatNo
	 * @return1
	 */
	public CoreReturnObject QueryElement() {
		try{
			//查询分组
			StringBuffer sqlucomm = new StringBuffer();
			sqlucomm.append("select distinct upper(CHEM_CODE) CHEM_CODE,");
			sqlucomm.append("  (select q.dsp_seq from qcm_base_chem q where q.chem_code = t.chem_code) dsp_seq ");
			sqlucomm.append("  FROM QCM_JHY_INSP_ELEMENTS t  where t.chem_code <> 'Ce' order by dsp_seq asc");
			//cro=this.getDao("testDao").ExcuteQuery(sqlucomm.toString());
			List<HashMap> listEle= mapper.query(sqlucomm.toString());
			cro.setResult(listEle);
			}catch(Exception ex){
				cro.setV_errCode(-1);
				cro.setV_errMsg("获取成分出错"+ex.getMessage());
				SqlSession.rollback();
			}
			SqlSession.close();
		return cro;
	}
	
	
	/**
	 * 根据条件查询成分检验实绩
	 * @param gradeName
	 * @param heatNo
	 * @return
	 */
	public CoreReturnObject QueryNew(String steelName,String heatNo,String STARTTIME,String ENDTIME) {
           //sqlId : WebRoot/WEB-INF/QUERY/QCM/JHY/QCM_JHY_QueryNew
			//cro=this.getDao("testDao").ExcuteQuery(XmlSqlParsersFactory.getSql(sqlId));
		try{
			if (!SqlJoint.IsNullOrSpace(STARTTIME) && !SqlJoint.IsNullOrSpace(ENDTIME)) {	
				String sky=StrSky(STARTTIME,ENDTIME);
				if(sky.equals("FALSE")){
					SqlSession.close();
					cro.setV_errCode(-1);
					cro.setV_errMsg("查询时间不能大于30天,请核实查询时间条件!");
					return cro;
				}
			}
			
		String AS="AS";
		String sql =" select * from ( "
				   +" select t.assay_no 成分化验号,t.HEAT_NO 炉号, t.STEEL_CODE 牌号, decode(t.ASSAY_TYPE,'L','熔炼成分','P','成品成分') 类型,"
				   + " t.chem_code, round(t.CHEM_VALUE, 5) CHEM_VALUE  "
					+"  from QCM_JHY_INSP_ELEMENTS t where 1=1  "
					+"     and CREATE_TIME >= "
					+"       to_date(concat('"+STARTTIME+"', ' 00:00:00'), 'yyyy-MM-dd HH24:mi:ss') "
					+"      and CREATE_TIME <= "
					+"       to_date(concat('"+ENDTIME+"', ' 23:59:59'), 'yyyy-MM-dd HH24:mi:ss') ";
		
					    if(StringUtils.isNotBlank(steelName)){
					          sql+=" and lower(STEEL_NAME)=lower('"+steelName+"')";
					    			}
					    if(StringUtils.isNotBlank(heatNo)){
							  sql+=" and lower(HEAT_NO)=lower('"+heatNo+"') ";
							    			}
					    
			   sql +="  ) t pivot ( "
					+"    max(chem_value) for chem_code in (  "
					+"      'C' C,'Si' Si,'Mn' Mn,'P' P,'S' S,'Cr' Cr,'Ni' Ni,'Cu' Cu,'Nb' Nb,'V' V,'Ti' Ti,'Als' Als, "
					+"      'Alt' Alt,'Mo' Mo,'N' N,'B' B,'As' As "+'"'+AS+'"'+",'Sn' Sn,'Pb' Pb,'Sb' Sb,'Bi' Bi,'Ca' Ca,'O' O,'H' H,'W' W, "
					+"      'Zr' Zr,'La' La,'Co' Co,'CE' CE,'CE1' CE1,'CE2' CE2,'CE3' CE3,'Ceq' Ceq,'Ceq2' Ceq2,'Ceq3' Ceq3, "
					+"      'CEV' CEV,'I' I,'J' J,'Pcm' Pcm,'Psr' Psr,'X' X,'Com1' Com1,'Com2' Com2,'Com3' Com3,'Com4' Com4, "
					+"      'Com5' Com5,'Com6' Com6,'Com7' Com7,'Com8' Com8,'Com9' Com9,'Com10' Com10,'Com11' Com11, "
					+"      'Com12' Com12,'Com13' Com13,'Com14' Com14,'Com15' Com15,'Com16' Com16,'Com17' Com17,'Com18' Com18, "
					+"      'Com19' Com19,'Com20' Com20,'Com21' Com21,'Com22' Com22,'Com23' Com23,'Al' Al,'Com24' Com24, "
					+"      'Com25' Com25,'Com26' Com26,'Com27' Com27,'Com28' Com28,'Com29' Com29,'Com30' Com30,'Mg' Mg, "
					+"      'Fe' Fe,'Zn' Zn,'Alsol' Alsol,'CE4' CE4,'Cd' Cd,'Cdl' Cdl,'Se' Se,'UCS' UCS) "
					+"  )  ORDER BY  成分化验号 DESC";
				List<HashMap> listEle= mapper.query(sql.toString());
				cro.setResult(listEle);
				}catch(Exception ex){
					cro.setV_errCode(-1);
					cro.setV_errMsg("获取成分出错"+ex.getMessage());
					SqlSession.rollback();
				}
				SqlSession.close();
		return cro;
	}
	
	
	
	/**
	 * 根据条件查询成分检验实绩
	 * @param gradeName
	 * @param heatNo
	 * @return
	 */
	public CoreReturnObject Query(String steelName,String heatNo,String STARTTIME,String ENDTIME) {
		//查询分组
		StringBuffer sqlucomm = new StringBuffer();
		sqlucomm.append("select BATCH_NO,HEAT_NO,ASSAY_NO,STEEL_CODE,ASSAY_TYPE FROM QCM_JHY_INSP_ELEMENTS WHERE  1=1");
		if(!"".equals(steelName)){
			sqlucomm.append(" and lower(STEEL_NAME)=lower('"+steelName+"') ");
		}
		if(!"".equals(heatNo)){
			sqlucomm.append(" and lower(HEAT_NO)=lower('"+heatNo+"') ");
		}
		if (STARTTIME!=null && !"".equals(STARTTIME)) {
			sqlucomm.append(" and CREATE_TIME>=to_date(concat('"+STARTTIME+"',' 00:00:00'),'yyyy-MM-dd HH24:mi:ss')");
		}
		if (ENDTIME!=null && !"".equals(ENDTIME)) {
			sqlucomm.append(" and CREATE_TIME<=to_date(concat('"+ENDTIME+"',' 23:59:59'),'yyyy-MM-dd HH24:mi:ss') ");
		}
		sqlucomm.append(" GROUP BY BATCH_NO,HEAT_NO,ASSAY_NO,STEEL_CODE,ASSAY_TYPE");
		cro=this.getDao("testDao").ExcuteQuery(sqlucomm.toString());
		Object group=(Object)cro.getResult();
		
		sqlucomm.setLength(0);
		//查询分条元素数据
		sqlucomm.append("select ASSAY_NO,STEEL_CODE,STEEL_NAME,ASSAY_TYPE,CHEM_CODE,");
		sqlucomm.append("CHEM_NAME,round(CHEM_VALUE,5) CHEM_VALUE FROM QCM_JHY_INSP_ELEMENTS WHERE  1=1");
		if(!"".equals(steelName)){
			sqlucomm.append(" and lower(STEEL_NAME)=lower('"+steelName+"') ");
		}
		if(!"".equals(heatNo)){
			sqlucomm.append(" and lower(HEAT_NO)=lower('"+heatNo+"') ");
		}
		if (STARTTIME!=null && !"".equals(STARTTIME)) {
			sqlucomm.append(" and CREATE_TIME>=to_date(concat('"+STARTTIME+"',' 00:00:00'),'yyyy-MM-dd HH24:mi:ss')");
		}
		if (ENDTIME!=null && !"".equals(ENDTIME)) {
			sqlucomm.append(" and CREATE_TIME<=to_date(concat('"+ENDTIME+"',' 23:59:59'),'yyyy-MM-dd HH24:mi:ss') ");
		}
		cro=this.getDao("testDao").ExcuteQuery(sqlucomm.toString());
		Object elements=(Object)cro.getResult();
		
		HashMap<String, Object> result=new HashMap<String, Object>();
		result.put("group", group);
		result.put("result", elements);
		cro.setResult(result);
			
		return cro;
	}
	
	
	
	
}