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;
|
}
|
|
}
|
