TestMetricInfoController.java 13.0 KB
package com.skua.modules.erp.controller;

import cn.hutool.core.util.RandomUtil;
import com.skua.common.util.ChineseInital;
import com.skua.core.api.vo.Result;
import com.skua.core.aspect.annotation.AutoLog;
import com.skua.core.context.SpringContextUtils;
import com.skua.core.service.IFactoryInfoService;
import com.skua.core.util.ConvertUtils;
import com.skua.core.util.DateUtils;
import com.skua.modules.equipment.vo.EquipmentRealTimeVO;
import com.skua.modules.erp.vo.MetricInfoVO;
import com.skua.modules.erp.vo.RealTimeHistoryDataVO;
import com.skua.modules.guest.util.DateUtil;
import com.skua.tool.util.JSUtils;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

@Api(tags="设备-设备运行台时报表")
@RestController
@RequestMapping("/test/metricInfo")
public class TestMetricInfoController {

    @Value("${skua.collectionFrequency:}")
    private String collectionFrequency;

    @Autowired
    private IFactoryInfoService iFactoryInfoService;


    @AutoLog(value = "设备-设备运行台时报表")
    @ApiOperation(value = "设备-设备运行台时报表(timeType:1 当日数据,=2表示月数据;=3表示年数据)", notes = "设备-设备运行台时报表")
    @GetMapping(value = "/realTimeHistoryData")
    public Result<RealTimeHistoryDataVO> equipRealTimeHistoryDataCtrl(@RequestParam(value = "departId") String departId,
                                                                      @RequestParam(value = "startTime") String startTime,
                                                                      @RequestParam(value = "endTime") String endTime,
                                                                      @RequestParam(value = "timeType",defaultValue = "1")String timeType) {
        if(StringUtils.isEmpty(startTime)){
            startTime = DateUtil.getCurrentDate() ;
        }
        if(StringUtils.isEmpty(endTime)){
            endTime = DateUtil.getCurrentDate();
        }
        if(startTime != null && startTime.length() < 19){
            startTime = startTime+" 00:00:00";
        }
        if(endTime != null && endTime.length() < 19){
            endTime = endTime+" 23:59:59";
        }
        Result<RealTimeHistoryDataVO> result = new Result<>();
        RealTimeHistoryDataVO realTimeHistoryDataVO  = new  RealTimeHistoryDataVO();
        //String fields = "TCSWSCLC_PLC300_Tag182,TCSWSCLC_PLC300_Tag185";
        JdbcTemplate masterDB = (JdbcTemplate)SpringContextUtils.getBean("master");
        String metricInfoSql = "select   ei.equipment_name ,  t.metric_express1 'gzExpress' , t.metric_express2 'yxExpress' ,t.metric_express3   'tzExpress' from sys_monitor_metric_info t ";
        metricInfoSql += " left join equipment_info ei on ei.id =  t.equipment_code ";
        metricInfoSql   += "  where t.metric_type  = 1 and  t.depart_id = '"+departId+"'  and t.equipment_code > 0  " ;
        List<MetricInfoVO> metricInfoVOList = masterDB.query(metricInfoSql,new BeanPropertyRowMapper<MetricInfoVO>(MetricInfoVO.class));

        //StringBuffer fields= new StringBuffer();
        String fields = "";
        if(metricInfoVOList != null ){
            for(MetricInfoVO metricInfoVO : metricInfoVOList){
                String equipmentName = metricInfoVO.getEquipmentName().replaceAll("[^\\u4e00-\\u9fa5]", "");
                String  equipmentCode  = ChineseInital.getAllFirstLetter(equipmentName)+"_"+ RandomUtil.randomNumbers(2);
                //metricInfoVO.setEquipmentName(equipmentName );
                metricInfoVO.setEquipmentCode(equipmentCode );
                if(StringUtils.isNotEmpty(metricInfoVO.getGzExpress())){
                    fields += metricInfoVO.getGzExpress()+",";
                }
                if(StringUtils.isNotEmpty(metricInfoVO.getYxExpress())){
                    fields += metricInfoVO.getYxExpress()+",";
                }
                if(StringUtils.isNotEmpty(metricInfoVO.getTzExpress())){
                    fields += metricInfoVO.getTzExpress()+",";
                }
            }
        }

        if (fields.endsWith(",")) {
            fields = fields.substring(0, fields.length() - 1);
        }
        List<Map<String, Object>> pgDataList = queryFactoryInfos(departId, fields, startTime, endTime, timeType);

        List<Map<String, Object>>  dataList = null;

        if(pgDataList != null ) {
            dataList = new ArrayList<>(pgDataList.size());
            Map<String, Object>  dataMap = null;
            //elect ts as time,nm as monitorId,v as monitorValue
            String equipmentCode = null;
            for (Map<String, Object> paramMap : pgDataList) {
                dataMap = new HashMap<>();
                dataMap.put("time",paramMap.get("time") );
                if(timeType.equals( "1")){
                    dataMap.put("time",DateUtils.formatTime(Long.parseLong(paramMap.get("time") + "000")) );
                }
                for(MetricInfoVO metricInfoVO : metricInfoVOList){
                    dataMap.put(  metricInfoVO.getEquipmentCode()+"_YX" , JSUtils.format(paramMap.get(metricInfoVO.getYxExpress()),"0.00"));//运行
                    dataMap.put(  metricInfoVO.getEquipmentCode()+"_GZ" , JSUtils.format(paramMap.get(metricInfoVO.getGzExpress()),"0.00"));//故障
                    //时数据
                    if (timeType.equals("1")) dataMap.put(  metricInfoVO.getEquipmentCode()+"_ZT" ,JSUtils.subtract( 60, paramMap.get(metricInfoVO.getYxExpress()),paramMap.get(metricInfoVO.getGzExpress()) )  );//暂停
                    //日数据
                    if (timeType.equals("2")) dataMap.put(  metricInfoVO.getEquipmentCode()+"_ZT" ,JSUtils.subtract( 24*60, paramMap.get(metricInfoVO.getYxExpress()),paramMap.get(metricInfoVO.getGzExpress()) )  );//暂停
                    //月数据
                    if (timeType.equals("3")) dataMap.put(  metricInfoVO.getEquipmentCode()+"_ZT" ,JSUtils.subtract( 30*24*60, paramMap.get(metricInfoVO.getYxExpress()),paramMap.get(metricInfoVO.getGzExpress()) )  );//暂停
                }
                dataList.add( dataMap );
            }
        }
       /* //拼装数据
        Map<String, Map<String,String>> linkedHashMap = new LinkedHashMap<String, Map<String,String>>();//返回数据结果
        String timeTemp  = null;
        Map<String,String> dataMap = null;
        if(pgDataList != null ){
            //elect ts as time,nm as monitorId,v as monitorValue
            for( Map<String, Object>   paramMap  : pgDataList){
                timeTemp = DateUtils.formatTime(Long.parseLong(paramMap.get("time") + "000"));
                dataMap = linkedHashMap.get(timeTemp);
                if(dataMap == null ){
                    dataMap = new HashMap<>();
                    dataMap.put("time",timeTemp);
                    for (Map.Entry<String, String> entry : nameMap.entrySet()) {
                        dataMap.put(entry.getKey(),"0");
                    }
                }
                // 使用For-Each迭代entries,通过Map.entrySet遍历key和value
                for (Map.Entry<String, String> entry : nameMap.entrySet()) {
                    System.out.println("entry.getKey()" +entry.getKey()+ "****"+"paramMap.get() = "+paramMap.get(entry.getKey()));
                    if(entry.getKey().equals( ConvertUtils.getString(paramMap.get(entry.getKey())) )){
                        dataMap.put(  entry.getValue() , ConvertUtils.getString(paramMap.get(entry.getKey())));
                    }
                }
                linkedHashMap.put(timeTemp,dataMap);
            }
        }*/

        realTimeHistoryDataVO.setHeadList( metricInfoVOList);
        realTimeHistoryDataVO.setDataList( dataList );
        result.setResult( realTimeHistoryDataVO);
        return result;
    }

    public List<Map<String, Object>> queryFactoryInfos(String departId, String fields, String startTime, String endTime, String tableType) {
        String fieldSqlPart = "";
        String[] fieldsArr = fields.split(",");

        for(int i = 0; i < fieldsArr.length; ++i) {
            fieldSqlPart = fieldSqlPart + ",'" + fieldsArr[i] + "'";
        }

        if (!StringUtils.isEmpty(fieldSqlPart)) {
            fieldSqlPart = fieldSqlPart.substring(1);
        }

        JdbcTemplate pgDB = (JdbcTemplate)SpringContextUtils.getBean("pg-db");
        JdbcTemplate masterDB = (JdbcTemplate)SpringContextUtils.getBean("master");
        List<Map<String, Object>> result = new ArrayList();
        List<Map<String, Object>> factoryDesign = masterDB.queryForList("select * from sys_factory_device where depart_id = '" + departId + "'");
        if (factoryDesign != null && factoryDesign.size() != 0) {
            String countTableName = ConvertUtils.getString(((Map)factoryDesign.get(0)).get("device_id"));
            String sql = null;
            //时数据
            if (tableType.equals("1")) {
                sql = "select ts as time,nm as monitorId,v as monitorValue from " + countTableName + "_count where nm in(" + fieldSqlPart + ") and  ts>=? and ts <? ";
            }
            //日数据
            if (tableType.equals("2")) {
                sql = "select aaa.date as time , aaa.monitorId ,SUM( to_number ( aaa.monitorValue, '9999999999999999.99' ) )as monitorValue from (";
                sql += "select to_char(to_timestamp(ts::INT8)::DATE,'YYYY-MM-DD') as date, ts as time,nm as monitorId,v as monitorValue from " + countTableName + "_count where nm in(" + fieldSqlPart + ") and  ts>=? and ts <? ";
                sql += " )aaa group by aaa.date ,aaa.monitorId  order by aaa.date asc";
            }
            //月数据
            if (tableType.equals("3")) {
                sql = "select aaa.date as time , aaa.monitorId ,SUM( to_number ( aaa.monitorValue, '9999999999999999.99' ) )as monitorValue from (";
                sql += "select to_char(to_timestamp(ts::INT8)::DATE,'YYYY-MM') as date, ts as time,nm as monitorId,v as monitorValue from " + countTableName + "_count where nm in(" + fieldSqlPart + ") and  ts>=? and ts <? ";
                sql += " )aaa group by aaa.date ,aaa.monitorId  order by aaa.date asc";
            }
            try {
                String tsStartTime = String.valueOf(DateUtils.getMillis(DateUtils.parseDate(startTime, "yyyy-MM-dd HH:mm:ss")) / 1000L);
                String tsEndTime = String.valueOf(DateUtils.getMillis(DateUtils.parseDate(endTime, "yyyy-MM-dd HH:mm:ss")) / 1000L);
                List dataList = null;
                try {
                    dataList = pgDB.queryForList(sql, new Object[]{tsStartTime, tsEndTime});
                } catch (DataAccessException var23) {
                    Object pgDbSlaveObject = SpringContextUtils.getBean("pg-db-slave");
                    if (pgDbSlaveObject != null) {
                        pgDB = (JdbcTemplate)pgDbSlaveObject;
                        dataList = pgDB.queryForList(sql, new Object[]{tsStartTime, tsEndTime});
                    }
                }
                this.dataRowToCellByTime(result, dataList);
            } catch (Exception var24) {
                var24.printStackTrace();
            }

            return result;
        } else {
            return result;
        }
    }
    private List<Map<String, Object>> dataRowToCellByTime(List<Map<String, Object>> result, List<Map<String, Object>> dataList) {
        List<String> orderTsList = new ArrayList();
        Map<String, Map<String, Object>> resultMap = new HashMap();
        Iterator var5 = dataList.iterator();

        while(true) {
            while(var5.hasNext()) {
                Map<String, Object> tempMap = (Map)var5.next();
                String key = tempMap.get("time").toString();
                Map<String, Object> fieldDataMap = (Map)resultMap.get(key);
                if (fieldDataMap != null && fieldDataMap.size() != 0) {
                    fieldDataMap.put(tempMap.get("monitorId").toString(), tempMap.get("monitorValue"));
                } else {
                    orderTsList.add(key);
                    fieldDataMap = new HashMap();
                    fieldDataMap.put(tempMap.get("monitorId").toString(), tempMap.get("monitorValue"));
                    resultMap.put(key, fieldDataMap);
                }
            }
            var5 = orderTsList.iterator();
            while(var5.hasNext()) {
                String order = (String)var5.next();
                Map<String, Object> map = (Map)resultMap.get(order);
                map.put("time", order);
                result.add(map);
            }

            return result;
        }
    }
}