package com.shlanbao.tzsc.pms.equ.effective.service.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.TreeMap;

import org.apache.cxf.bus.blueprint.BlueprintBeanLocator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.shlanbao.tzsc.base.dao.BaseDaoI;
import com.shlanbao.tzsc.base.dao.EqmCullRecordDaoI;
import com.shlanbao.tzsc.base.dao.EqmParamDaoI;
import com.shlanbao.tzsc.base.dao.EqmWorkOrderTimeDaoI;
import com.shlanbao.tzsc.base.mapping.EqmCullRecordBean;
import com.shlanbao.tzsc.base.model.Combobox;
import com.shlanbao.tzsc.base.model.DataGrid;
import com.shlanbao.tzsc.base.model.PageParams;
import com.shlanbao.tzsc.base.model.PublicBean;
import com.shlanbao.tzsc.base.service.BaseService;
import com.shlanbao.tzsc.init.BaseParams;
import com.shlanbao.tzsc.pms.equ.effective.beans.EffectiveGraphBean;
import com.shlanbao.tzsc.pms.equ.effective.beans.EffectiveOperaBean;
import com.shlanbao.tzsc.pms.equ.effective.beans.EffectiveRunTime;
import com.shlanbao.tzsc.pms.equ.effective.beans.EffectiveUtilizeTime;
import com.shlanbao.tzsc.pms.equ.effective.beans.EqmCullRecord;
import com.shlanbao.tzsc.pms.equ.effective.service.EffectiveOperaServiceI;
import com.shlanbao.tzsc.utils.excel.ExportExcel;
import com.shlanbao.tzsc.utils.params.SysEqpTypeBase;
import com.shlanbao.tzsc.utils.tools.DateUtil;
import com.shlanbao.tzsc.utils.tools.MathUtil;
import com.shlanbao.tzsc.utils.tools.StringUtil;
/**
 *
* @ClassName: EffectiveOperaServiceImpl
* @Description:
* @author luo
* @date 2015年3月9日 下午3:01:32
*
 */
@Service
public class EffectiveOperaServiceImpl extends BaseService implements EffectiveOperaServiceI{


	private static String EXCLUDKEY="TCSJ";//剔除时间key
	@Autowired
	private BaseDaoI<Object> baseDao;
	@Autowired
	public  EqmParamDaoI eqmParamDaoI;
	@Autowired
	public  EqmWorkOrderTimeDaoI eqmWorkOrderTimeDaoI;
	@Autowired
	public  EqmCullRecordDaoI eqmCullRecordDao;

	@Override
	public DataGrid queryList(EffectiveOperaBean seachBean,PageParams pageParams) {
		//查询总条数
		String sql = queryWorkEffic(seachBean,pageParams,0);
		List<?> totalList=baseDao.queryBySql(sql);
		long total =StringUtil.converObj2Long(totalList.get(0));
		List<EffectiveOperaBean> returnList=new ArrayList<EffectiveOperaBean>();
		if(total>0){
			//查询详细
			sql = queryWorkEffic(seachBean,pageParams,1);
			List<?> dataList=baseDao.queryBySql(sql);
			Object[] obj=null;
			EffectiveOperaBean bean=null;
			String param=null;//临时变量，存放转换数据
			List<Combobox> shiftBox=BaseParams.getShiftCombobox(false);
			List<Combobox> workShopBox=BaseParams.getWorkShopCombobox(false);
			List<Combobox> teamBox=BaseParams.getTeamCombobox(false);
			for (Object data : dataList) {
				obj=(Object[]) data;
				bean=new EffectiveOperaBean();
				param=StringUtil.convertObjToString(obj[1]);
				param = exChangeStr(param, workShopBox);
				bean.setArea(param);//车间
				bean.setEqpName(StringUtil.convertObjToString(obj[2]));//设备名称
				bean.setEqpType(StringUtil.convertObjToString(obj[4]));//设备型号
				bean.setDate(StringUtil.convertObjToString(obj[5]));//工单日期
				param=StringUtil.convertObjToString(obj[6]);
				param = exChangeStr(param, shiftBox);
				bean.setShift(param);//班次

				param=StringUtil.convertObjToString(obj[7]);
				param = exChangeStr(param, teamBox);
				bean.setTeam(param);//班组
				bean.setRunTime(StringUtil.converObj2Double(obj[8]));//实际运行时间
				bean.setDownTime(StringUtil.converObj2Double(obj[9]));//停机时间
				bean.setRbTime(StringUtil.converObj2Double(obj[14]));//维保时间
				bean.setExcludTime(StringUtil.converObj2Double(obj[15]));//剔除时间
				bean.setpRunTime(StringUtil.converObj2Double(obj[13]));//计划运行时间
				bean.setQty(StringUtil.converObj2Double(obj[11]));//实际产量
				bean.setEqty(StringUtil.converObj2Double(obj[12]));//台时产能
				bean.setEqpCategory(StringUtil.convert2Integer(obj[10]));//设备类型 1卷烟机  2包装机  3 封  4 成型
				//现在mes发来的设备产能   卷烟、包装  都是：万支/每分钟    成型 ：米/秒   封箱机  箱。现阶段计算全部转成	箱/分     成型：  万只/分
				//设备有效率=实际产量（箱）/（计划开机（时）*产能（箱/时））
				//计划开机时长扣除用餐时间和保养时间,剔除时间(含有用餐时间、其他剔除时间)
				//double kjTime=bean.getpRunTime()-bean.getExcludTime()-bean.getRbTime();
				double kjTime = bean.getpRunTime()*60-30-30-bean.getExcludTime();//工单计划生产时间-班中餐(30分钟)-日保养时间（30分钟）
				double worNum =bean.getQty()/(bean.getEqty()/5*kjTime)*100;//有效作业率=实际产量/（台时产量*（工单计划生产时间-班中餐-日保养时间）
				//double worNum=bean.getQty()/(kjTime*60*bean.getEqty())*100;
				bean.setWorNum(String.format("%.3f", worNum));//有效作业率
				returnList.add(bean);
			}
		}
		return new DataGrid(returnList,total);
	}
	/**
	 * <p>功能描述：班次、班组、车间  使用id转换成name</p>
	 *@param param
	 *@param workShopBox
	 *@return
	 *shisihai
	 *2016下午4:42:32
	 */
	private String exChangeStr(String param, List<Combobox> workShopBox) {
		for (Combobox combobox : workShopBox) {
			if(combobox.getId().equals(param)){
				param=combobox.getName();
				break;
			}
		}
		return param;
	}
	/**
	 *
	 * <p>功能描述：获取设备有效作业率sql</p>
	 *@param seachBean
	 *@param pageParams
	 *@param type 0查总条数  1 查询详细
	 *@return
	 *shisihai
	 *2016上午11:25:54
	 */
	private String queryWorkEffic(EffectiveOperaBean seachBean,PageParams pageParams,int type){
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT selectCols from( ");
		sql.append("SELECT ROWNUM as rn,tab0.* from ( ");
		sql.append("SELECT ");
		sql.append("b.WORK_SHOP as workShop, ");
		sql.append("b.equipment_name as eqpName, ");
		sql.append("b.equipment_code as eqpCode, ");
		sql.append("c.name as eqpType, ");
		sql.append("to_char(a.DATE_,'yyyy-mm-dd') as produDate, ");
		sql.append("a.shift as shift, ");
		sql.append("a.team as team, ");
		sql.append("round(nvl(f.run_time,0)/60,2) as rRuntime, ");
		sql.append("round(nvl(f.stop_time,0)/60,2) as rStoptime, ");
		sql.append("d.code as eqpCategory, ");
		sql.append("nvl(f.qty,0.0) as rQty, ");
		sql.append("b.YIE_ID as yie, ");
		sql.append("round(to_number(a.ETIM-a.STIM)*24,2) as pRunTime, ");
		sql.append("sum(nvl(e.plan_times,0.0)) as pMaintenanceTime, ");
		sql.append("sum(round(nvl(g.num,0),2)) as kickOutTime ");
		sql.append("from SCH_WORKORDER a  ");
		sql.append("LEFT JOIN MD_EQUIPMENT b on a.eqp=b.id ");
		sql.append("LEFT JOIN MD_EQP_TYPE c on b.eqp_type_id=c.id ");
		sql.append("LEFT JOIN MD_EQP_CATEGORY d on c.cid=d.id ");
		sql.append("LEFT JOIN EQM_WHEEL_COVEL_PLAN e on e.eqp_id=b.id and e.plan_date= a.date_ and e.shift_id = a.shift ");
		sql.append("LEFT JOIN SCH_STAT_OUTPUT f on f.oid=a.id ");
		sql.append("LEFT JOIN EQP_STOP_MAINTAIN g on g.eqp_id=b.id and a.shift=g.shift and a.team=g.team and to_char(g.actual_date,'yyyy-mm-dd') = to_char(a.date_,'yyyy-mm-dd') and g.free1 = 1");
		sql.append("WHERE a.sts=4 ");
		//添加过滤条件
		if(StringUtil.notEmpty(seachBean.getStartTime())){
			sql.append("and to_char(a.DATE_,'yyyy-mm-dd') >= '"+seachBean.getStartTime()+"' ");
		}
		if(StringUtil.notEmpty(seachBean.getEndTime())){
			sql.append("and to_char(a.DATE_,'yyyy-mm-dd') <= '"+seachBean.getEndTime()+"' ");
		}
		if(StringUtil.notEmpty(seachBean.getEqpType())){
			sql.append("and  c.id='"+seachBean.getEqpType()+"' ");
		}
		if(StringUtil.notEmpty(seachBean.getEqpName())){
			sql.append("and b.equipment_name like '%"+seachBean.getEqpName()+"%' ");
		}
		if(StringUtil.notEmpty(seachBean.getArea())){
			sql.append("and  b.work_shop='"+seachBean.getArea()+"' ");
		}
		sql.append("GROUP BY ");
		sql.append("b.WORK_SHOP,b.equipment_name,c.name,a.DATE_, ");
		sql.append("a.shift,a.team,f.qty,d.code,f.run_time,f.stop_time, ");
		sql.append("round(to_number(a.ETIM-a.STIM)*24,2),b.YIE_ID,b.equipment_code ");
		sql.append("ORDER BY a.date_ asc,b.WORK_SHOP asc,a.shift asc,a.team asc,cast(eqpCode as int) asc");
		sql.append(") tab0 ");
		sql.append(") ");
		//判断所需sql类型
		if(type==0){
			//查询总行数
			return sql.toString().replaceFirst("selectCols", "count(*)");
		}else{
			//查询详细
			//图表和导出报表不分页
			if(pageParams==null){
				return sql.toString().replaceFirst("selectCols", "*");
			}
			sql.append("WHERE  rn > "+(pageParams.getPage()-1)*pageParams.getRows());
			sql.append(" and rn <= "+pageParams.getPage()*pageParams.getRows());
			return sql.toString().replaceFirst("selectCols", "*");
		}
	}

	/**
	 * type  1-查询总数   2-查询集合
	 *
	 * */
	public StringBuffer genHql(EffectiveOperaBean seachBean,int type){
		StringBuffer b = new StringBuffer();
		if(type==1){
			b.append(" select count(*) ");
		}else{
			b.append(" SELECT * FROM  ( select ROW_NUMBER () OVER (ORDER BY t.code) AS trownum,t.workshop,t.name AS eqmName,t.eqp_type,t.datef,t.shiftName AS shift,t.team_name AS team,t.gd_all_time,t.STOP_TIME,t.tctm_all_time,t.rb_all_time,t.TIME_UNIT,t.qty,t.qty_unit,t.yie_id,t.yield_unit,t.code,t.EQP_TYPE_ID ");
		}
		b.append(" from ( SELECT DISTINCT (gd.shift), gd.shiftName,gd.TIME_UNIT,gd.qty_Unit AS qty_unit,gd.YIELD_UNIT AS yield_unit, gd.team_name, ");
		b.append(" gd.code,gd.EQP_TYPE_ID, gd.STOP_TIME,gd.eqp,gd.eqp_type AS eqp_type,gd.workshop,gd.equipment_name AS name,gd.datef, NVL(gd.qty, 0) AS qty,");
		b.append("  gd.yie_id,NVL(gd.gd_all_time, 0) AS gd_all_time,NVL(tc.rb_all_time, 0) AS rb_all_time, NVL(gd.tc_all_time, 0) AS tctm_all_time");
		b.append(" FROM ( SELECT  a.shift, c.EQUIPMENT_CODE AS code, i.NAME AS TIME_UNIT,j.NAME AS qty_Unit,d.NAME AS shiftName,e.NAME AS team_name,f.NAME AS workshop,g.NAME AS eqp_type, g.ID AS EQP_TYPE_ID,a.team, a.eqp, a.mat,a.DATE_ AS datef, c.equipment_name, b.qty, b.bad_qty,b.stim,b.etim,b.run_time,b.STOP_TIME,b.stop_times,c.yie_id,c.YIELD_UNIT,ROUND(TO_NUMBER(b.etim - b.stim) * 24 * 60) AS gd_all_time, ");
		b.append(" ( SELECT SUM (stop_time) FROM EQM_CULL_RECORD WHERE to_char (st_date, 'yyyy-MM-dd') = to_char (a.DATE_, 'yyyy-MM-dd') AND eqp_id = a.eqp  AND shift_id = a.shift  GROUP BY  to_char (st_date, 'yyyy-MM-dd'),  eqp_id, shift_id ) AS tc_all_time");
		b.append(" FROM SCH_WORKORDER a  LEFT JOIN SCH_STAT_OUTPUT b ON a.id = b.oid  LEFT JOIN MD_EQUIPMENT c ON c.id = a.eqp  LEFT JOIN MD_SHIFT d ON a.shift = d.ID LEFT JOIN MD_TEAM e ON e.id = a.TEAM LEFT JOIN MD_WORKSHOP f ON f.id = c.WORK_SHOP  LEFT JOIN MD_EQP_TYPE g ON g.id = c.EQP_TYPE_ID  LEFT JOIN MD_UNIT i ON i.ID = b.TIME_UNIT ");
		//b.append(" LEFT JOIN MD_SHIFT d ON a.shift = d.ID LEFT JOIN MD_TEAM e ON e.id=a.TEAM LEFT JOIN MD_WORKSHOP f ON f.id=c.WORK_SHOP LEFT JOIN MD_EQP_TYPE g ON g.id=c.EQP_TYPE_ID	LEFT JOIN MD_UNIT i on i.ID=b.TIME_UNIT");
		b.append(" LEFT JOIN MD_UNIT j ON j.id = b.UNIT WHERE  a.del = 0  AND b.del = 0  AND b.etim IS NOT NULL AND sts = 4");
		if(StringUtil.notNull(seachBean.getEqpType())){
			b.append(" AND g.ID='"+seachBean.getEqpType()+"' ");//设备类型
		}
		if(StringUtil.notNull(seachBean.getEqpName())){
			b.append(" AND c.EQUIPMENT_NAME like  '%"+seachBean.getEqpName()+"%' ");//设备名称
		}
		//开始时间
		if(StringUtil.notNull(seachBean.getStartTime())){
			b.append(" AND to_char(a.DATE_,'yyyy-MM-dd') >='"+seachBean.getStartTime()+"' ");
		}
		//结束时间
		if(StringUtil.notNull(seachBean.getEndTime())){
			b.append(" AND to_char(a.DATE_,'yyyy-MM-dd') <='"+seachBean.getEndTime()+"' ");
		}
		b.append(" ) gd ");
		b.append(" LEFT JOIN ( SELECT shift, date_p AS datef,  eqp_type, ROUND(TO_NUMBER(etim - stim) * 24 * 60) AS rb_all_time  FROM  EQM_PAULDAY  WHERE  del = 0  AND status = 3  ) tc ON tc.shift = gd.shift AND to_char (tc.datef, 'yyyy-MM-dd') = to_char (gd.datef, 'yyyy-MM-dd') )t   ");

		return b;
	}
	/**
	 *
	 * <p>功能描述：</p>
	 *设备有效作业率图表
	 *shisihai
	 *2016下午3:35:45
	 */
	@Override
	public EffectiveGraphBean queryGraph(EffectiveOperaBean seachBean){
        //获得sql
		String hql=this.queryWorkEffic(seachBean,null,1);
        //得到结果集
		List<?> list=baseDao.queryBySql(hql);
		//定义拼接结果集
		TreeMap<String,String> ht1=new TreeMap<String,String>();//
		TreeMap<String,String> ht2=new TreeMap<String,String>();
		TreeMap<String,Object[]> ht3=new TreeMap<String,Object[]>();
		int xlength=0;
		int ylength=0;
		Object[] temp=null;
		List<Combobox> teams=BaseParams.getTeamCombobox(false);
		for(Object o:list){
			temp=(Object[])o;
			temp[5]=StringUtil.convertObjToString(temp[5]);//日期
			String team=StringUtil.convertObjToString(temp[7]);//班组
			team = exChangeStr(team, teams);
			String eqpName=StringUtil.convertObjToString(temp[2]);//设备名称
			try {
				//获取设备名称
				if(!StringUtil.notEmpty(eqpName)){
					continue;
				}
				//ht1  设备+日期：设备
				if(ht1.get(eqpName+temp[5])==null){
					ht1.put(eqpName+temp[5],eqpName+temp[5]);
					xlength++;
				}
				//获取班组名称
				//h2  班组：班次+班次
				if(ht2.get(team)==null){
					ht2.put(team, team);
					ylength++;
				}
				//设备+日期+班次+班组：当前数据行
				if(ht3.get(eqpName+temp[5]+team)==null)
					ht3.put(eqpName+temp[5]+team, temp);

			    }catch (Exception e) {
				e.printStackTrace();
			}
	     }
		Iterator<String> i1=ht1.keySet().iterator();
		Iterator<String> i2=ht2.keySet().iterator();
		//X轴值类型
		String[] xValue=new String[xlength];
		//Y轴值类型
		String[] yvalueType=new String[ylength];
		//Y轴值
		double[][] yValue=new double[ylength][xlength];
		int i=0;
		int j;
		while(i1.hasNext()){
			String key=i1.next();
			xValue[i]=ht1.get(key);//设备名+日期
			j=0;
			i2=ht2.keySet().iterator();
			while(i2.hasNext()){
				String i2Key=i2.next();
				yvalueType[j]=i2Key;
				Object o=ht3.get(ht1.get(key)+ht2.get(i2Key));
				Object[] temp2=null;
				if(o!=null){
					temp2=(Object[])o;
					//现在mes发来的设备产能   卷烟、包装  都是：万支/每分钟    成型 ：米/秒   封箱机  箱。现阶段计算全部转成	箱/分     成型：  万只/分
					//设备有效率=实际产量（箱）/（计划开机（时）*产能（箱/时））
					//计划开机时长扣除用餐时间和保养时间,剔除时间(含有用餐时间、其他剔除时间)
					double kjTime=StringUtil.converObj2Double(temp2[13])-StringUtil.converObj2Double(temp2[15])-StringUtil.converObj2Double(temp2[14]);
					double worNum=StringUtil.converObj2Double(temp2[11])/(kjTime*60*StringUtil.converObj2Double(temp2[12]))*100;
					yValue[j][i]=StringUtil.converObj2Double(String.format("%.3f", worNum));//有效作业率
				}else{
					yValue[j][i]=0.0;
				}
				j++;
			}
			i++;
		}
		EffectiveGraphBean bean=new EffectiveGraphBean();
		bean.setXvalue(xValue);
		bean.setYvalue(yValue);
		bean.setYvalueType(yvalueType);
		return bean;
	}
	//设备运行效率
	@SuppressWarnings("unchecked")
	@Override
	public DataGrid queryRunTimeEffective(EffectiveRunTime seachBean,PageParams pageParams){
		String sql=this.getRunEfficSql(seachBean,pageParams,0,false);
		List<Object[]> list=(List<Object[]>) baseDao.queryBySql(sql);
		long total=StringUtil.converObj2Long(list.get(0));
		List<EffectiveRunTime> returnList=new ArrayList<EffectiveRunTime>();
		if(total>0){
			sql=this.getRunEfficSql(seachBean,pageParams,1,false);
			list=(List<Object[]>) baseDao.queryBySql(sql);
			EffectiveRunTime bean=null;
			String param=null;
			List<Combobox> workShopBox=BaseParams.getWorkShopCombobox(false);
			for (Object[] obj : list) {
				bean=new EffectiveRunTime();
				param=StringUtil.convertObjToString(obj[7]);
				param = exChangeStr(param, workShopBox);
				bean.setArea(param);//车间
				bean.setEqpName(StringUtil.convertObjToString(obj[5]));
				bean.setEqpType(StringUtil.convertObjToString(obj[4]));
				bean.setYild(StringUtil.converObj2Double(obj[3]));
				bean.setpRuntime(StringUtil.converObj2Double(obj[1]));
				bean.setRunTime(StringUtil.converObj2Double(obj[2]));
				double effic=bean.getRunTime()/bean.getpRuntime();
				String efficStr=String.format("%.2f", effic*100);
				bean.setEffectiveRunTime(Double.valueOf(efficStr));
				returnList.add(bean);
			}
		}
		return new DataGrid(returnList, total);
	}
	/**
	 * 设备运行效率
	 * <p>功能描述：</p>
	 *@return  type=0 条数   type=1 详细
	 *shisihai
	 *2016下午4:22:42
	 */
	private String getRunEfficSql(EffectiveRunTime seachBean,PageParams pageParams,int type,boolean flag){
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT selectCols from( ");
		sql.append("SELECT ROWNUM rn,tab0.* from ( ");
		sql.append("SELECT sum(round(to_number(a.ETIM-a.STIM)*24,2)) as pRunTime, ");
		sql.append("round(sum(d.run_time/60),2) as rRunTime, ");
		sql.append("b.YIE_ID,c.name,b.EQUIPMENT_NAME,b.EQUIPMENT_CODE,b.WORK_SHOP ");
		if(flag){
			sql.append(",a.shift ");
		}
		sql.append("from SCH_WORKORDER a ");
		sql.append("LEFT JOIN MD_EQUIPMENT b on a.eqp=b.id ");
		sql.append("LEFT JOIN MD_EQP_TYPE c on b.eqp_type_id=c.id ");
		sql.append( " left join SCH_STAT_OUTPUT d on a.id = d.oid " );
		sql.append("WHERE a.sts=4 AND a.REAL_ETIM is not NULL AND a.REAL_STIM is not NULL ");
		//添加过滤条件
		if(StringUtil.notEmpty(seachBean.getArea())){
			sql.append("and b.work_shop ='"+seachBean.getArea()+"'");
		}
		if(StringUtil.notEmpty(seachBean.getEqpName())){
			sql.append(" and b.EQUIPMENT_NAME like'%"+seachBean.getEqpName()+"%'");
		}
		if(StringUtil.notEmpty(seachBean.getEqpType())){
			sql.append("and c.id ='"+seachBean.getEqpType()+"'");
		}
		if(StringUtil.notEmpty(seachBean.getStartTime())){
			sql.append(" and to_char(a.date_,'yyyy-mm-dd') >='"+seachBean.getStartTime()+"'");
		}
		if(StringUtil.notEmpty(seachBean.getEndTime())){
			sql.append(" and to_char(a.date_,'yyyy-mm-dd') <='"+seachBean.getEndTime()+"'");
		}
		sql.append("GROUP BY b.YIE_ID,c.name,b.EQUIPMENT_NAME,b.EQUIPMENT_CODE,b.WORK_SHOP ");
		if(flag){
			sql.append(",a.shift ");
		}
		sql.append(" ORDER BY cast(b.EQUIPMENT_CODE as int) asc ");
		if(flag){
			sql.append(",a.shift asc");
		}
		sql.append(")tab0)  ");
		//分页信息
		//条数
		if(type==0){
			return sql.toString().replaceFirst("selectCols", "count(*)");
		}else{
			//查询详细     为null则是导出，不分页
			if(pageParams!=null){
				sql.append(" where rn> "+(pageParams.getPage()-1)*pageParams.getRows()+" and rn <= "+pageParams.getPage()*pageParams.getRows()+"");
			}
			return sql.toString().replaceFirst("selectCols", "*");
		}
	}

	//查询运行效率图表数据源
	@SuppressWarnings("unchecked")
	public EffectiveGraphBean queryRunTimeEffectiveChart(EffectiveRunTime seachBean){
		String hql=this.getRunEfficSql(seachBean,null,1,true);
		List<Object> list=(List<Object>) baseDao.queryBySql(hql);
		TreeMap<String,String> ht1=new TreeMap<String,String>();
		TreeMap<String,String> ht2=new TreeMap<String,String>();
		TreeMap<String,Object[]> ht3=new TreeMap<String,Object[]>();
		int xlength=0;
		int ylength=0;
		for(Object o:list){
			Object[] temp=(Object[])o;
			if(temp[5]==null){
				continue;
			}
			//获取设备名称
			if(ht1.get(temp[5].toString())==null){
				ht1.put(temp[5].toString(), temp[5].toString());
				xlength++;
			}
			//获取机型
			if(ht2.get(temp[4].toString())==null){
				ht2.put(temp[4].toString(), temp[4].toString());
				ylength++;
			}
			//
			if(ht3.get(temp[5].toString()+temp[4].toString())==null)
				ht3.put(temp[5].toString()+temp[4].toString(), temp);

		}
		Iterator<String> i1=ht1.keySet().iterator();
		Iterator<String> i2=ht2.keySet().iterator();
		//X轴值类型
		String[] xValue=new String[xlength];
		//Y轴值类型
		String[] yvalueType=new String[ylength];
		//Y轴值
		double[][] yValue=new double[ylength][xlength];
		int i=0;
		int j;
		//循环设备
		while(i1.hasNext()){
			String key=i1.next();
			xValue[i]=ht1.get(key);
			j=0;
			i2=ht2.keySet().iterator();
			//循环班组信息
			while(i2.hasNext()){
				String i2Key=i2.next();
				yvalueType[j]=i2Key;
				Object o=ht3.get(ht1.get(key)+ht2.get(i2Key));
				Object[] temp=null;
				if(o!=null){
					temp=(Object[])o;
					//以防除数为0
					if(Double.parseDouble(temp[1].toString())>0){
						double prunTimes=StringUtil.converObj2Double(temp[1]);
						double runTime=StringUtil.converObj2Double(temp[2]);
						double effic=runTime/prunTimes;
						String efficStr=String.format("%.2f", effic);
						yValue[j][i]=Double.valueOf(efficStr);
					}else{
						yValue[j][i]=0.0;
					}
				}else{
					yValue[j][i]=0.0;
				}
				j++;
			}
			i++;
		}
		EffectiveGraphBean bean=new EffectiveGraphBean();
		bean.setXvalue(xValue);
		bean.setYvalue(yValue);
		bean.setYvalueType(yvalueType);
		return bean;
	}

	//数据转换 1,2,3,4 转换成 '1','2','3','4'
	private String parseString(String value){
		String[] obj=value.split(",");
		String temp="";
		for(String o:obj){
			if(o.equals("")){continue;}
			temp+=",'"+o+"'";
		}
		if(temp.length()<2){return "";}
		return temp.substring(1, temp.length());
	}

	//设备有效利用率查询方法
	@Override
	public DataGrid quertyUtilizeEffective(EffectiveUtilizeTime seachBean,PageParams pageParams) {
		String eqpName="";
		String stime="";
		String etime="";
		String team="";
		String eqpTypeId="";
		String eqpId="";
		Integer start=1;
		Integer ednum=1;
		//起始页-每页多少条
		if(pageParams.getPage()==1){// 1-10  10-20  20-30
			start=1;
			ednum=pageParams.getRows();
		}else{
			ednum=pageParams.getPage()*pageParams.getRows();
			start=ednum-pageParams.getRows();
		}
		//设备机型
		if(StringUtil.notNull(seachBean.getEqpType())){
			eqpTypeId=" and b.eqp_type_id ='"+seachBean.getEqpType()+"' ";
		}
		//机组code,可多匹配
		if(StringUtil.notNull(seachBean.getEqpId())){
			if(StringUtil.notNull(parseString(seachBean.getEqpId()))){
				eqpId=" and a.eqp in ("+parseString(seachBean.getEqpId())+") ";
			}
		}
		//设备名称
		if(StringUtil.notNull(seachBean.getEqpName())){
			eqpName=" and b.equipment_name like '%"+seachBean.getEqpName()+"%' ";
		}
		//开始时间
		if(StringUtil.notNull(seachBean.getStartTime())){
			stime=" and a.date >='"+seachBean.getStartTime()+"' ";
		}
		//结束时间
		if(StringUtil.notNull(seachBean.getEndTime())){
			etime=" and a.date  <='"+seachBean.getEndTime()+"' ";
		}
		//班组
		if(StringUtil.notNull(seachBean.getTeam())){
			if(StringUtil.notNull(parseString(seachBean.getTeam()))){
				team=" and a.team in ("+parseString(seachBean.getTeam())+") ";
			}
		}
		String colum="select"
                +" (select name from MD_WORKSHOP where id=b.work_shop) as work_shop_name,"
				+" b.equipment_name ,"
				+" (select name from MD_EQP_TYPE where id=b.eqp_type_id) as eqp_type_name,"
				+" a.date,"
				+" (select name from md_shift where id=a.shift) as shift_name,"
				+" (select name from MD_TEAM where id=a.team) as team_name,"
				+" datediff(minute, a.stim, a.etim) as all_time,"
				+" c.run_time,"
				+" (c.run_time/datediff(minute, a.stim, a.etim))*100 as all_num,"
				+" row_number() over(ORDER BY a.date DESC) as rum"
				+" from  SCH_WORKORDER  a left join MD_EQUIPMENT b on a.eqp=b.id "
				+" left join SCH_STAT_OUTPUT c on a.id=c.oid"
				+" where 1=1 "+stime+etime+eqpName+team+eqpTypeId+eqpId+" and a.sts=4 and a.del=0";
                //+")t where 1=1 and t.rum  between "+start+" and "+ednum+"";

		String sqlCount="select count(*) from ("+colum+")t where 1=1";
		String sqllist="select * from ("+colum+")t where 1=1 and t.rum  between "+start+" and "+ednum+"";


		List<?> totalList=baseDao.queryBySql(sqlCount);
		long total =Long.parseLong(totalList.get(0).toString());
		List<?> list=baseDao.queryBySql(sqllist);
		List<EffectiveUtilizeTime> returnList=new ArrayList<EffectiveUtilizeTime>();
		for(Object o:list){
			Object[] temp=(Object[]) o;
			EffectiveUtilizeTime b=new EffectiveUtilizeTime();
			b.setArea(temp[0].toString());
			b.setEqpName(temp[1].toString());
			b.setEqpType(temp[2].toString());
			b.setDate(DateUtil.formatStrDate(temp[3].toString(),"yyyy-MM-dd HH:mm:ss","yyyy-MM-dd"));
			b.setTeam(temp[4].toString());
			b.setShift(temp[5].toString());
			b.setPlanTime(Double.parseDouble(temp[6].toString()));
			b.setRunSumTime(Double.parseDouble(temp[7].toString()));
			Double val=Double.parseDouble(temp[8].toString());
			double ret = convert(val);
			b.setEffectiveUtilize(ret);
			returnList.add(b);
		}
		return new DataGrid(returnList,total);
	}



	private double convert(Double val) {
		long ft = Math.round(val*100); //四舍五入
		double ret = ft/100.0; //注意:使用 100.0 而不是 100
		return ret;
	}

	@Override
	public EffectiveGraphBean quertyUtilizeEffectiveChart(EffectiveUtilizeTime seachBean) {
		String eqpName="";
		String stime="";
		String etime="";
		String team="";
		String eqpTypeId="";
		String eqpId="";
		//设备机型
		if(StringUtil.notNull(seachBean.getEqpType())){
			eqpTypeId=" and b.eqp_type_id ='"+seachBean.getEqpType()+"' ";
		}
		//机组code,可多匹配
		if(StringUtil.notNull(seachBean.getEqpId())){
			if(StringUtil.notNull(parseString(seachBean.getEqpId()))){
				eqpId=" and a.eqp in ("+parseString(seachBean.getEqpId())+") ";
			}
		}
		//设备名称
		if(StringUtil.notNull(seachBean.getEqpName())){
			eqpName=" and b.equipment_name like '%"+seachBean.getEqpName()+"%' ";
		}
		//开始时间
		if(StringUtil.notNull(seachBean.getStartTime())){
			stime=" and a.date >='"+seachBean.getStartTime()+"' ";
		}
		//结束时间
		if(StringUtil.notNull(seachBean.getEndTime())){
			etime=" and a.date  <='"+seachBean.getEndTime()+"' ";
		}
		//班组
		if(StringUtil.notNull(seachBean.getTeam())){
			if(StringUtil.notNull(parseString(seachBean.getTeam()))){
				team=" and a.team in ("+parseString(seachBean.getTeam())+") ";
			}
		}

		String sql=" select t.equipment_name,t.eqp_type_name,t.shift_name,sum(t.run_time)/sum(t.all_time) as all_num from ("
				+" select"
				+"  b.equipment_name ,"
				+" (select name from MD_EQP_TYPE where id=b.eqp_type_id) as eqp_type_name,"
				+" (select name from md_shift where id=a.shift) as shift_name,"
				+" datediff(minute, a.stim, a.etim) as all_time,"
				+" c.run_time as run_time"
				+" from  SCH_WORKORDER  a left join MD_EQUIPMENT b on a.eqp=b.id "
				+" left join SCH_STAT_OUTPUT c on a.id=c.oid"
				+" where 1=1 "+stime+etime+eqpName+team+eqpTypeId+eqpId+" and a.sts=4 and a.del=0"
				+" )t where 1=1 group by t.equipment_name,t.eqp_type_name,t.shift_name";

		List<?> list=baseDao.queryBySql(sql);
		Hashtable<String,String> ht1=new Hashtable<String,String>();
		Hashtable<String,String> ht2=new Hashtable<String,String>();
		Hashtable<String,Object[]> ht3=new Hashtable<String,Object[]>();
		int xlength=0;
		int ylength=0;
		for(Object o:list){
			Object[] temp=(Object[])o;
			try {
				//获取设备名称
				if(ht1.get(temp[0].toString())==null){
					ht1.put(temp[0].toString(), temp[0].toString());
					xlength++;
				}
				//获取班组名称
				if(ht2.get(temp[2].toString())==null){
					ht2.put(temp[2].toString(), temp[2].toString());
					ylength++;
				}
				//
				if(ht3.get(temp[0].toString()+temp[2].toString())==null)
					ht3.put(temp[0].toString()+temp[2].toString(), temp);
			} catch (Exception e) {
				// TODO: handle exception
			}
		    }
		Iterator<String> i1=ht1.keySet().iterator();
		Iterator<String> i2=ht2.keySet().iterator();
		//X轴值类型
		String[] xValue=new String[xlength];
		//Y轴值类型
		String[] yvalueType=new String[ylength];
		//Y轴值
		double[][] yValue=new double[ylength][xlength];
		int i=0;
		int j;
		//循环设备
		while(i1.hasNext()){
			String key=i1.next();
			xValue[i]=ht1.get(key);
			j=0;
			i2=ht2.keySet().iterator();
			//循环班组信息
			while(i2.hasNext()){
				String i2Key=i2.next();
				yvalueType[j]=i2Key;
				Object o=ht3.get(ht1.get(key)+ht2.get(i2Key));
				if(o!=null){
					Object[] temp=(Object[])o;
					//以防除数为0
					Double valt=Double.parseDouble(temp[3].toString())*100;
					if(valt>0){
						yValue[j][i]=MathUtil.roundHalfUp(valt,2);
					}else{
						yValue[j][i]=0.0;
					}
				}else{
					yValue[j][i]=0.0;
				}
				j++;
			}
			i++;
		}
		EffectiveGraphBean bean=new EffectiveGraphBean();
		bean.setXvalue(xValue);
		bean.setYvalue(yValue);
		bean.setYvalueType(yvalueType);
		return bean;
	}

	/**
	 * 【功能说明】：获得PMS系统的设备有效作业率
	 *
	 *  @time 2015年7月26日9:16:56
	 *  @author wchuang
	 *
	 * */
	public StringBuffer retHqlEuipment(EffectiveOperaBean seachBean){
		StringBuffer b = new StringBuffer();
		b.append(" select k.eqpName,k.eqpType,k.team,sum( k.qty) as snum,sum(k.f) as enum from ( ");
		b.append(" 		 select t.eqpName,t.eqpType,t.team,t.qty,t.yieId, ( (t.all_time-t.stop_time)/60 )*t.yieId  as f  from ( ");
		b.append(" 		   SELECT ");
		b.append(" 		      case when ( (select sum(stop_time) from EQM_CULL_RECORD where 1=1  and to_char( st_date, 'yyyy-MM-dd') =  b.[date]  and  eqp_id=b.eqp and shift_id=b.shift  and  team_id=b.team  GROUP BY eqp_id,shift_id,team_id ) is null ) then 0.00 else ");
		b.append(" 		           (select sum(stop_time) from EQM_CULL_RECORD where 1=1  and to_char( st_date, 'yyyy-MM-dd') =  b.[date]  and  eqp_id=b.eqp and shift_id=b.shift  and  team_id=b.team  GROUP BY eqp_id,shift_id,team_id ) ");
		b.append(" 		      end as stop_time, ");
		b.append(" 		      (select EQUIPMENT_NAME from MD_EQUIPMENT where id=b.eqp) as eqpName, ");
		b.append(" 		      d.name as eqpType, ");
		b.append(" 		      d.id as eqpTypeId, ");
		b.append(" 		      b.[date], ");
		b.append(" 		      (select name from MD_TEAM where id=b.team) as team, ");
		b.append(" 		      (select name from MD_SHIFT where id=b.shift) as shift, ");
		b.append(" 		      a.stim,a.etim, (a.qty-a.bad_qty) as qty,  (select yie_id from MD_EQUIPMENT where id=b.eqp) as yieId, ");
		b.append(" 		      case  when ( ( datediff(minute, a.stim, a.etim)) IS  NULL) then 0.00 else ( datediff(minute, a.stim, a.etim)) end as all_time ");
		b.append(" 		   FROM ");
		b.append(" 			   SCH_STAT_OUTPUT a ");
		b.append(" 			 LEFT JOIN SCH_WORKORDER b ON a.oid = b.id ");
		b.append(" 			 left JOIN MD_EQUIPMENT c on b.EQP=c.id ");
		b.append(" 			 left join MD_EQP_TYPE d on c.eqp_type_id=d.id ");
		b.append(" 		   where a.del=0  ");
		//设备机型
		if(StringUtil.notNull(seachBean.getEqpType())){
			b.append(" and d.id ='"+seachBean.getEqpType()+"' ");
		}
		//设备名称
		if(StringUtil.notNull(seachBean.getEqpName())){
			b.append("and c.equipment_name like '%"+seachBean.getEqpName()+"%' ");
		}
		//开始时间
		if(StringUtil.notNull(seachBean.getStartTime())){
			b.append("and to_char( b.[date],'yyyy-MM-dd')  >='"+seachBean.getStartTime()+"' ");
		}
		//结束时间
		if(StringUtil.notNull(seachBean.getEndTime())){
			b.append("and to_char( b.[date], 'yyyy-MM-dd') <='"+seachBean.getEndTime()+"' ");
		}
		b.append(" 		 ) t where 1=1   ");
		b.append(" 		)k group by k.eqpName,k.eqpType,k.team ");
		return b;
	}

	/**
     *  功能说明：设备停机剔除时间管理-添加
     *  @param bean 数据实体对象
     *  @return
     *  @author wchuang
     *  @time 2015年7月21日16:18:46
     *
     * */
	@Override
	public boolean addCullRecord(EqmCullRecordBean bean) {
		boolean falg=false;
		try {
			falg=eqmCullRecordDao.save(bean);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return falg;
	}

	/**
	 *  【功能说明】：设备停机剔除时间管理-查询
     *  @param bean 数据实体对象
     *  @return
     *  @author wchuang
     *  @time 2015年7月22日10:36:42
     *
	 * */
	@Override
	public DataGrid queryCullRecord(EqmCullRecordBean bean,
			PageParams pageParams) {
		String eqp_id="";
		String shift_id="";
		String st_date="";
		String ed_date="";
		String type_id="";
		//停机类型
		if(StringUtil.notNull(bean.getType_id())){
			type_id=" and type_id='"+bean.getType_id()+"'";
		}
		//设备ID
		if(StringUtil.notNull(bean.getEqp_id())){
			eqp_id=" and eqp_id='"+bean.getEqp_id()+"'";
		}
		//班次
        if(StringUtil.notNull(bean.getShift_id())){
			shift_id=" and shift_id="+bean.getShift_id();
		}
		//开始时间
        if(StringUtil.notNull(bean.getRuntime())){
			st_date=" and to_char( st_date, 'yyyy-MM-dd') >='"+bean.getRuntime()+"'";
		}
        //结束时间
        if(StringUtil.notNull(bean.getEndtime())){
			ed_date=" and to_char( st_date, 'yyyy-MM-dd') <='"+bean.getEndtime()+"'";
		}
		String clumn="( select ROW_NUMBER () OVER ( ORDER BY ed_date desc ) bh,id,(select equipment_name from MD_EQUIPMENT where id=eqp_id ) as eqp_name,(select name from  md_shift where id=shift_id) as shift_name,st_date,ed_date,remark,stop_time,"
				+"case type_id  when '1' then '固定停机' when '2' then '不可控停机' when '3' then '提前完工' end as type_id, type_name from EQM_CULL_RECORD  where 1=1 and del=1 "+type_id+eqp_id+shift_id+st_date+ed_date+" ) t ";

		Integer start=1;
		Integer ednum=1;
		//起始页-每页多少条
		if(pageParams.getPage()==1){// 1-10  10-20  20-30
			start=1;
			ednum=pageParams.getRows();
		}else{
			ednum=pageParams.getPage()*pageParams.getRows();
			start=ednum-pageParams.getRows();
		}
		String sqllit="select * from"+clumn+" where 1=1 and  t.bh BETWEEN "+start+" and "+ednum;
		String sqltol="select count(*) from" +clumn;
	    //总条数
    	List<?> tl= baseDao.queryBySql(sqltol);
		String t=tl.get(0).toString();
		long total=Long.parseLong(t);
		//对象集合
		List<?> list=baseDao.queryBySql(sqllit);
		List<EqmCullRecord> returnList=new ArrayList<EqmCullRecord>();
		for(Object o:list){
			Object[] temp=(Object[]) o;
			EqmCullRecord b=new EqmCullRecord();
			try {
				b.setId(temp[1].toString());
				b.setEqp_name(temp[2].toString());
				b.setShift_name(temp[3].toString());
				b.setSt_date(temp[4].toString().substring(0,19));
				b.setEd_date(temp[5].toString().substring(0,19));
				b.setRemark(temp[6]==null?"":temp[6].toString());
				b.setStop_time(temp[7]==null?"":temp[7].toString());
				b.setType_id(temp[8]==null?"":temp[8].toString());
				b.setType_name(temp[9]==null?"":temp[9].toString());
			} catch (Exception e) {
				e.printStackTrace();
			}
			returnList.add(b);
		}
		return new DataGrid(returnList,total);
	}

	/**
	 * 【功能说明】：设备停机剔除时间管理-查询
	 *  @param bean 数据实体对象
     *  @return
     *  @author wchuang
     *  @time 2015年7月22日14:26:21
	 *
	 * */
	@Override
	public boolean deleteCullRecordById(EqmCullRecordBean bean) {
		boolean flag=false;
		try {
			eqmCullRecordDao.deleteById(bean.getId(), EqmCullRecordBean.class);
			flag=true;
		} catch (Exception e) {
			e.printStackTrace();
			flag=false;
		}
		return flag;
	}

	@Override
	public HSSFWorkbook ExportExcelJBEffic(EffectiveOperaBean bean) {
		HSSFWorkbook wb = null;
		ExportExcel ee = new ExportExcel();
		try {
			// th 当前开始行,当前结束行,一共多少列
			int[] thTables = { 1, 3, 7 };
			List<String> th = new ArrayList<String>();
			String area="卷包车间";
			String unit="箱";
			if(StringUtil.notEmpty(bean.getArea())&& bean.getArea().equals("2")){
				area="成型车间";
				unit="万支";
			}
			// 第1行;第1列开始 ~ 第1列结束 一共 跨到第3行
			th.add("1,1,7,1,"+area+"设备有效作业率");
			th.add("2,1,2,1,生产日期：");
			th.add("2,4,7,1," + bean.getStartTime()+ "   到     "
					+ bean.getEndTime());
			th.add("3,1,1,3,生产日期");
			th.add("3,2,2,3,班组");
			th.add("3,3,3,3,设备名称");
			th.add("3,4,4,3,产能"+unit+"/时");
			th.add("3,5,5,3,计划开机时间（时）");
			th.add("3,6,6,3,实际产量"+unit);
			th.add("3,7,7,3,设备有效率");
			List<EffectiveOperaBean> ls = queryList(bean, null).getRows();
			String[] method=new String[]
					{"getDate","getTeam","getEqpName","getEqty",
					  "getpRunTime","getQty","getWorNum"
					};
			//开始行
			int startLine=3;
			wb = ee.exportExcel(thTables, th, startLine, method,EffectiveOperaBean.class,ls);

		} catch (Exception e) {
			e.printStackTrace();
		}
		return wb;
	}

	private List<EffectiveOperaBean> geteqpEfficList(EffectiveOperaBean bean){
		StringBuffer sql=this.genHql(bean, 2);
		sql.append(" ) eff ");
		sql.append("  ORDER BY datef desc");
		List<?> list=baseDao.queryBySql(sql.toString());
		List<EffectiveOperaBean> returnList=new ArrayList<EffectiveOperaBean>();
		for(Object o:list){
			Object[] temp=(Object[]) o;
			EffectiveOperaBean b=new EffectiveOperaBean();
			b.setArea(temp[1].toString());//chejian
			b.setEqpName(temp[2].toString());
			b.setEqpType(temp[3].toString());
			b.setDate(temp[4].toString());
			b.setShift(temp[5].toString());
			b.setTeam(temp[6].toString());
			b.setRunTime(Double.parseDouble(temp[7].toString()));
			b.setDownTime(Double.parseDouble(temp[8].toString()));
			b.setExcludTime(Double.parseDouble(temp[9].toString()));
			b.setRbTime(Double.parseDouble(temp[10].toString()));
			b.setTimeUnit(temp[11].toString());
			b.setQty(Double.parseDouble(temp[12].toString()));
			b.setDwUnit(temp[13].toString());
			b.setEqty(Double.parseDouble(temp[14].toString()));
			b.setEqtyUnit(temp[15].toString());
			double rTime=Double.parseDouble(temp[7].toString());
			double rbTime=Double.parseDouble(temp[10].toString());
			double ExcludTime=Double.parseDouble(temp[9].toString());
			double eatTime=SysEqpTypeBase.jcTime;
			double q1=(rTime-rbTime-ExcludTime-eatTime)/(60+0.0)*Double.parseDouble(temp[14].toString());
			double q2=Double.parseDouble(temp[12].toString());//（实际运行时间-日保时间-剔除时间-吃饭时间）/60 * 台时产量
			if(q1==0){
				b.setWorNum("0");
			}else{
				BigDecimal a1=new BigDecimal(q2);
				BigDecimal b1=new BigDecimal(q1);
				b.setWorNum(""+MathUtil.roundHalfUp((a1.divide(b1,4, BigDecimal.ROUND_HALF_EVEN).doubleValue()*100), 2));
			}
			returnList.add(b);
		}
		return returnList;
	}
	/**
	 * 导出设备运行效率
	 * shisihai
	 * 2015-9-28
	 */
	@Override
	public HSSFWorkbook deriveEqpRunEfficExcel(EffectiveRunTime bean) {
		HSSFWorkbook wb = null;
		ExportExcel ee = new ExportExcel();
		try {
			// th 当前开始行,当前结束行,一共多少列
			int[] thTables = { 1, 3, 6 };
			List<String> th = new ArrayList<String>();
			String area="卷包";
			if(StringUtil.notEmpty(bean.getArea())&& bean.getArea().equals("2")){
				area="成型";
			}
			// 第1行;第1列开始 ~ 第1列结束 一共 跨到第3行
			th.add("1,1,6,1,"+area+"设备运行效率月统计");
			th.add("2,1,2,2,生产日期：");
			th.add("2,3,6,2," + bean.getStartTime()+ "   到     "
					+ bean.getEndTime());
			th.add("3,1,1,3,机组");
			th.add("3,2,2,3,机型");
			th.add("3,3,3,3,铭牌能力");
			th.add("3,4,4,3,实际开机时间（时）");
			th.add("3,5,5,3,计划开机时间（时）");
			th.add("3,6,6,3,利用率");
			List<EffectiveRunTime> data=queryRunTimeEffective(bean,null).getRows();
			String[] method=new String[]
			{"getEqpName","getEqpType","getYild","getRunTime","getpRuntime","getEffectiveRunTime"};
			//开始行
			int startLine=3;
			//WorkShopShiftBean.class List集合中对应的实体类
			wb = ee.exportExcel(thTables, th, startLine, method,EffectiveRunTime.class,data);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return wb;
	}

	@Override
	public void batchDeleteCullRecord(String ids) {
		String[] split = ids.split(",");
		for (int i = 0; i < split.length; i++) {
			eqmCullRecordDao.deleteById(split[i], EqmCullRecordBean.class);
		}

	}

	;
	/**
	 * 设备月运行效率查询
	 * shisihai
	 * 2015-09-28
	 * @param seachBean
	 * @param code  机台code  30以下是卷烟机
	 * @return
	 */
	public DataGrid queryRunTimeEffective2(EffectiveRunTime seachBean,String code){

		StringBuffer hql = new StringBuffer();
		hql.append("SELECT sour.code,sour.eqp_name AS eqp_name,ISNULL(SUM (sour.qty), 0.0) AS qty,sour.yie_id AS yid,ISNULL(SUM (sour.run_time), 0.0) AS run_time,ISNULL(SUM (sour.tc_time), 0.0) AS tc_time,ISNULL(SUM (sour.rb_time), 0.0) AS rb_time");
		hql.append(" FROM (SELECT me.equipment_name AS eqp_name,ISNULL(a.qty, 0.0)AS qty,sw. DATE AS work_Date,b.yie_id,ISNULL(a.run_time, 0.0) as run_time,ISNULL(c.tc_time, 0.0) as tc_time,ISNULL(d.rb_time, 0.0) as rb_time,me.equipment_code AS code ");
		hql.append("  FROM SCH_STAT_OUTPUT sso");
		hql.append("  LEFT JOIN SCH_WORKORDER sw ON sso.oid = sw.id ");
		hql.append(" LEFT JOIN MD_EQUIPMENT me ON sw.eqp = me.ID ");
		//设备名称
		if(StringUtil.notNull(seachBean.getEqpName())){
			hql.append(" and me.equipment_Name like '%"+seachBean.getEqpName()+"%' ");
		}
		hql.append(" 	LEFT JOIN MD_WORKSHOP mw ON me.WORK_SHOP = mw.ID ");
		hql.append(" LEFT JOIN MD_EQP_TYPE met ON me.EQP_TYPE_ID = met.ID");
		//设备机型
		if(StringUtil.notNull(seachBean.getEqpType())){
			hql.append(" and met.id ='"+seachBean.getEqpType()+"' ");
		}
		hql.append(" LEFT JOIN (SELECT SUM (ISNULL(DATEDIFF(MINUTE, s1.STIM, s1.ETIM), 0.0)) AS run_time,SUM (ISNULL(s1.QTY, 0)) AS qty,s1.OID FROM SCH_STAT_OUTPUT s1 GROUP BY s1.OID ) a ON a.OID = sw.ID ");
		hql.append(" LEFT JOIN (SELECT yie_id,id FROM MD_EQUIPMENT) b ON b.id = sw.EQP");
		hql.append(" LEFT JOIN (SELECT SUM (ISNULL(s2.stop_time, 0)) AS tc_time,s2.eqp_id,to_char( st_date, 'yyyy-MM-dd') AS c_date FROM EQM_CULL_RECORD s2 GROUP BY s2.eqp_id,s2.st_date,s2.shift_id) c ON c.eqp_id = me.ID AND c.c_date = to_char( sw.[DATE], 'yyyy-MM-dd') ");
		hql.append(" LEFT JOIN (SELECT SUM (ISNULL(datediff(MINUTE,s3.STIM,s3.ETIM)+"+SysEqpTypeBase.jcTime+",0)) AS rb_time,s3.DATE_P AS dp,s3.EQP_TYPE	FROM EQM_PAULDAY s3 where s3.STATUS=3 GROUP BY s3.DATE_P,s3.EQP_TYPE,s3.SHIFT ) d ON to_char( sw.[DATE], 'yyyy-MM-dd') =to_char( d.dp, 'yyyy-MM-dd') AND met.CID = d.EQP_TYPE");
		hql.append(" GROUP BY me.equipment_name ,a.qty ,sw.date ,b.yie_id,a.run_time,c.tc_time,d.rb_time,me.equipment_code ) sour ");
		hql.append(" where 1=1 and  ");
		//分机型查询
		if(StringUtil.notNull(code)){
			if(code.equals("jy")){
				hql.append(" sour.code <=30 ");
			}else{
				hql.append(" sour.code >=31 and sour.code <=60 ");
			}
		}
		//开始时间
		if(StringUtil.notNull(seachBean.getStartTime())){
			hql.append("and sour.work_date >='"+seachBean.getStartTime()+"' ");
		}
		//结束时间
		if(StringUtil.notNull(seachBean.getEndTime())){
				hql.append("and sour.work_date <='"+seachBean.getEndTime()+"' ");
		}
		hql.append("  GROUP BY eqp_name  ,sour.yie_id ,sour.code ");

		List<Object[]> list=(List<Object[]>) baseDao.queryBySql(hql.toString());
		long total =list.size();
		List<EffectiveOperaBean> returnList=new ArrayList<EffectiveOperaBean>();
		for(Object[] o:list){
			Object[] temp=o;
			EffectiveOperaBean b=new EffectiveOperaBean();
			b.setEqpName(temp[1].toString());//设备名称
			b.setQty(Double.parseDouble(temp[2].toString()));//总产量
			b.setEqty(Double.parseDouble(temp[3].toString()));//台时产量
			b.setRunTime(Double.parseDouble(temp[4].toString()));//运行时间
			b.setExcludTime(Double.parseDouble(temp[5].toString()));//剔除时间
			b.setRbTime(Double.parseDouble(temp[6].toString()));//日保时间
			double q1=(Double.parseDouble(temp[4].toString())-Double.parseDouble(temp[5].toString())-Double.parseDouble(temp[6].toString()))/(60+0.0)*Double.parseDouble(temp[3].toString());
			double q2=Double.parseDouble(temp[2].toString());
			if(q1==0){
				b.setWorNum("0");
			}else{
				BigDecimal a1=new BigDecimal(q2);
				BigDecimal b1=new BigDecimal(q1);
				b.setWorNum(""+MathUtil.roundHalfUp((a1.divide(b1,4, BigDecimal.ROUND_HALF_EVEN).doubleValue()*100), 2));
			}
			returnList.add(b);
		}
		return new DataGrid(returnList,total);
	}
	/**
	 * 获取合计
	 * shisihai
	 * 2015-09-28
	 * @param ls
	 * @return qty,r_time,p_qty,r_effic
	 */
	public String[] getHj(List<EffectiveOperaBean> ls){
		double qty=0.0;
		double r_time=0.0;
		double p_qty=0.0;
		double r_effic=0.0;
		long size=ls.size();
		for (EffectiveOperaBean bean : ls) {
			qty+=bean.getQty();
			r_time+=(bean.getRunTime()-bean.getExcludTime()-bean.getRbTime())/(60+0.0);
			p_qty+=((bean.getRunTime()-bean.getExcludTime()-bean.getRbTime())/(60+0.0))*bean.getEqty();
		}
		if(p_qty==0){
			r_effic=0.0;
		}else{
			BigDecimal a1=new BigDecimal(p_qty);
			BigDecimal b1=new BigDecimal(qty);
			r_effic=MathUtil.roundHalfUp((b1.divide(a1,4, BigDecimal.ROUND_HALF_EVEN).doubleValue()*100), 2);
		}
		return new String[]{""+MathUtil.roundHalfUp(qty,2),""+MathUtil.roundHalfUp(r_time,2),""+MathUtil.roundHalfUp(p_qty,2),""+r_effic};
	}

	private StringBuffer getSql(EffectiveRunTime seachBean){
		StringBuffer sb=new StringBuffer();
		sb.append(" SELECT ");
		sb.append(" sour.shopName,"//车间
				+ "sour.eqp_name AS eqp_name,"//机台名
				+ "sour.eqpType,"//设备型号
				+ "sour.shift,"//班次
				+ "sour.code,");//设备code
		sb.append(" ISNULL(SUM(sour.qty), 0.0) AS qty, ");//产量
		sb.append(" sour.yie_id AS yid,");//台时产量
		sb.append(" ISNULL(SUM(sour.run_time), 0.0) AS run_time, ");//运行时间
		sb.append(" ISNULL(SUM(sour.tc_time), 0.0) AS tc_time,");//剔除时间
		sb.append(" ISNULL(SUM(sour.rb_time), 0.0) AS rb_time,");//日保时间和吃饭时间
		sb.append(" ISNULL(SUM(sour.stims), 0.0) as stims, ");//停机时间
		sb.append(" sour.unit_name ");
		sb.append(" FROM ");
		sb.append(" (SELECT mw.NAME as shopName,ms.name as shift,met.name as eqpType,me.equipment_name AS eqp_name,ISNULL(a.qty, 0.0) AS qty,");
		sb.append(" sw. DATE AS work_Date,b.yie_id,ISNULL(a.run_time, 0.0) AS run_time,ISNULL(c.tc_time, 0.0) AS tc_time,ISNULL(d.rb_time, 0.0) AS rb_time,");
		sb.append(" me.equipment_code AS code,e.stims,met.id as type_id,mu.NAME as unit_name ");
		sb.append(" FROM ");
		sb.append(" SCH_STAT_OUTPUT sso ");
		sb.append(" LEFT JOIN SCH_WORKORDER sw ON sso.oid = sw.id and sso.etim is not null and sw.sts=4 ");
		sb.append(" LEFT JOIN MD_EQUIPMENT me ON sw.eqp = me.ID and me.del=0 ");
		sb.append(" LEFT JOIN MD_WORKSHOP mw ON me.WORK_SHOP = mw.ID ");
		sb.append(" LEFT JOIN MD_EQP_TYPE met ON me.EQP_TYPE_ID = met.ID and met.del=0 ");
		sb.append(" LEFT JOIN MD_SHIFT ms ON ms.ID=sw.SHIFT ");
		sb.append(" LEFT JOIN MD_TEAM mt ON mt.id=sw.TEAM ");
		sb.append(" LEFT JOIN MD_UNIT mu ON sso.TIME_UNIT=mu.ID ");
		sb.append(" LEFT JOIN (SELECT SUM (ISNULL( DATEDIFF(MINUTE, s1.STIM, s1.ETIM),0.0)) AS run_time, ");
		sb.append(" SUM (ISNULL(s1.QTY, 0)) AS qty, ");
		sb.append(" s1.OID ");
		sb.append(" FROM SCH_STAT_OUTPUT s1 where s1.etim is not null ");
		sb.append(" GROUP BY s1.OID ) a ON a.OID = sw.ID and sw.sts=4 ");
		sb.append(" LEFT JOIN (SELECT yie_id,id FROM MD_EQUIPMENT ) b ON b.id = sw.EQP ");
		sb.append(" LEFT JOIN (SELECT SUM (ISNULL(s2.stop_time, 0)) AS tc_time, ");
		sb.append(" s2.eqp_id,"
				+ "to_char( st_date, 'yyyy-MM-dd') AS c_date");
		sb.append(" FROM EQM_CULL_RECORD s2 ");
		sb.append(" GROUP BY s2.eqp_id,s2.st_date,s2.shift_id ");
		sb.append(" ) c ON c.eqp_id = me.ID ");
		sb.append(" AND c.c_date = to_char( sw.[DATE], 'yyyy-MM-dd') ");
		sb.append(" LEFT JOIN (SELECT ");
		sb.append(" SUM (ISNULL(datediff(MINUTE, s3.STIM, s3.ETIM) + "+SysEqpTypeBase.jcTime+",0)) AS rb_time, ");
		sb.append(" s3.DATE_P AS dp,s3.EQP_TYPE,s3.shift ");
		sb.append(" FROM EQM_PAULDAY s3 ");
		sb.append(" WHERE s3.STATUS = 3 ");
		sb.append(" GROUP BY s3.DATE_P,s3.EQP_TYPE,s3.SHIFT) d  ");
		sb.append(" ON to_char( sw.[DATE],'yyyy-MM-dd') = to_char( d.dp, 'yyyy-MM-dd') ");
		sb.append(" AND met.CID = d.EQP_TYPE  AND sw.SHIFT=d.shift ");
		sb.append(" LEFT JOIN (SELECT OID,ISNULL(STOP_TIME, 0) as stims FROM SCH_STAT_OUTPUT  ) e ON e.OID=sw.ID ");
		sb.append(" GROUP BY me.equipment_name,a.qty,sw. DATE,b.yie_id,a.run_time,c.tc_time,d.rb_time,me.equipment_code,e.stims,mw.NAME,ms.NAME,met.name,met.id,mu.NAME ");
		sb.append(" ) sour ");
		sb.append(" WHERE 1=1 ");
		//eqpType
		if(StringUtil.notNull(seachBean.getEqpType())){
			sb.append(" and sour.type_id = '"+seachBean.getEqpType()+"'");
		}
		//eqpName
		if(StringUtil.notNull(seachBean.getEqpName())){
			sb.append(" and sour.eqp_name like '%"+seachBean.getEqpName()+"%' ");
		}
		if(StringUtil.notNull(seachBean.getStartTime())){
			sb.append(" and sour.work_date >= '"+seachBean.getStartTime()+"'");
		}
		if(StringUtil.notNull(seachBean.getEndTime())){
			sb.append(" and sour.work_date <= '"+seachBean.getEndTime()+"'");
		}
		sb.append(" GROUP BY eqp_name,sour.yie_id,sour.code,sour.shopName,sour.shift,sour.eqpType,sour.type_id,sour.unit_name ");
		return sb;
	}

}