电源历史告警

whyclxw

2024-12-12 8ea6bcb91b4a8b50dfac568b0d0b52cd543b9f8c

电源历史告警

 src/main/java/com/whyc/pojo/db_alarm/BattAlarmHistory.java

@@ -47,7 +47,7 @@

    @TableField("alm_value")
    @ApiModelProperty("告警值")
    private Float almValue;
    private Integer almValue;


    @TableField(exist = false)

 src/main/java/com/whyc/pojo/db_power_alarm/PowerAlarmHistory.java

@@ -57,4 +57,16 @@
    @TableField("alm_level")
    @ApiModelProperty("告警等级[1-紧急 2-重大 -3-一般]")
    private Integer almLevel;

    @TableField(exist = false)
    @ApiModelProperty("表名字拼接")
    private String recordYear;

    @TableField(exist = false)
    @ApiModelProperty("分页开始位置")
    private Integer limitStart;

    @TableField(exist = false)
    @ApiModelProperty("分页结束位置")
    private Integer limitEnd;
}

 src/main/java/com/whyc/service/PowerAlarmHistoryService.java

@@ -1,13 +1,26 @@
package com.whyc.service;

import com.github.pagehelper.PageInfo;
import com.whyc.dto.Response;
import com.whyc.pojo.db_power_alarm.PowerAlarmHistory;
import com.whyc.util.SubTablePageInfoUtil;
import com.whyc.util.ThreadLocalUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.ParseException;

@Service
public class PowerAlarmHistoryService {
    @Autowired
    private SubTablePageInfoUtil util;

    //查询电源历史时间告警
    public Response getHisAlarm(int pinfId, String startTime, String endTime, int pageNum, int pageSize) {
        return new Response().set(1);
    public Response getHisAlarm(int pinfId, String startTime, String endTime, int pageNum, int pageSize) throws ParseException {
        PowerAlarmHistory his=new PowerAlarmHistory();
        his.setPowerId(pinfId);
        PageInfo pageInfo=util.getPageInfo(pageNum,pageSize, ThreadLocalUtil.parse(startTime,1),ThreadLocalUtil.parse(endTime,1)
                ,"db_power_alarm","power_alarm_history",his);
        return new Response().setII(1,pageInfo.getList()!=null,pageInfo,"查询电源历史时间告警");
    }
}

 src/main/java/com/whyc/service/SubTableService.java

@@ -3,6 +3,7 @@
import com.whyc.mapper.CallBack;
import com.whyc.pojo.db_alarm.BattAlarmHistory;
import com.whyc.pojo.db_dis_batt.BattTestInfData;
import com.whyc.pojo.db_power_alarm.PowerAlarmHistory;
import com.whyc.util.ActionUtil;
import com.whyc.util.ThreadLocalUtil;
import org.springframework.beans.factory.annotation.Autowired;
@@ -52,7 +53,7 @@
        });
        return list;
    }
    //告警历史实时计算每张表查询总数
    //电池告警历史实时计算每张表查询总数
    public int getBattHisCount(BattAlarmHistory battAlarmHistory) {
        String sql="SELECT  count(*) as number FROM db_alarm."+ battAlarmHistory.getRecordYear()+" history " +
                " where history.binf_id="+ battAlarmHistory.getBinfId() ;
@@ -81,7 +82,7 @@
        }
        return num;
    }
    //告警历史实时
    //电池告警历史实时
    public List<BattAlarmHistory> getBattHisList(BattAlarmHistory battAlarmHistory) {
        String sql="SELECT * FROM db_alarm."+ battAlarmHistory.getRecordYear()+" history " +
                " where history.binf_id="+ battAlarmHistory.getBinfId() ;
@@ -105,7 +106,68 @@
                    data.setMonNum(rs.getInt("mon_num"));
                    data.setAlmId(rs.getInt("alm_id"));
                    data.setAlmLevel(rs.getInt("alm_level"));
                    data.setAlmValue(rs.getFloat("alm_value"));
                    data.setAlmValue(rs.getInt("alm_value"));
                    list.add(data);
                }
                return list;
            }
        });
        return list;
    }

    //电源告警历史实时计算每张表查询总数
    public int getPowerHisCount(PowerAlarmHistory powerAlarmHistory) {
        String sql="SELECT  count(*) as number FROM db_power_alarm."+ powerAlarmHistory.getRecordYear()+" history " +
                " where history.power_id="+ powerAlarmHistory.getPowerId() ;
        if(powerAlarmHistory.getAlmStartTime()!=null){
            sql+=" and alm_start_time  >='"+ ThreadLocalUtil.format(powerAlarmHistory.getAlmStartTime(),1)+"' ";
        }
        if(powerAlarmHistory.getAlmEndTime()!=null){
            sql+=" and alm_start_time  <='"+ThreadLocalUtil.format(powerAlarmHistory.getAlmEndTime(),1)+"' ";
        }
        List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
            @Override
            public List getResults(ResultSet rs) throws SQLException {
                LinkedList<Object> temp = new LinkedList<>();
                try {
                    while (rs.next())
                        temp.add(rs.getInt("number"));
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return temp;
            }
        });
        int num =0;
        if(list!=null){
            num= (int) list.get(0);
        }
        return num;
    }
    //电源告警历史实时
    public List<PowerAlarmHistory> getPowerHisList(PowerAlarmHistory powerAlarmHistory) {
        String sql="SELECT * FROM db_power_alarm."+ powerAlarmHistory.getRecordYear()+" history " +
                " where history.power_id="+ powerAlarmHistory.getPowerId() ;
        if(powerAlarmHistory.getAlmStartTime()!=null){
            sql+=" and alm_start_time  >='"+ ThreadLocalUtil.format(powerAlarmHistory.getAlmStartTime(),1)+"' ";
        }
        if(powerAlarmHistory.getAlmEndTime()!=null){
            sql+=" and alm_start_time  <='"+ThreadLocalUtil.format(powerAlarmHistory.getAlmEndTime(),1)+"' ";
        }
        sql+="  ORDER BY alm_start_time asc  limit "+ powerAlarmHistory.getLimitStart()+","+ powerAlarmHistory.getLimitEnd()+" ";
        List<PowerAlarmHistory> list=sqlExecuteService.executeQuery_call(sql, new CallBack() {
            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List list=new ArrayList();
                while (rs.next()){
                    PowerAlarmHistory data=new PowerAlarmHistory();
                    data.setNum(rs.getInt("num"));
                    data.setPowerId(rs.getInt("power_id"));
                    data.setAlmStartTime(rs.getTimestamp("alm_start_time"));
                    data.setAlmEndTime(rs.getTimestamp("alm_end_time"));
                    data.setAlmId(rs.getInt("alm_id"));
                    data.setAlmLevel(rs.getInt("alm_level"));
                    data.setAlmValue(rs.getInt("alm_value"));
                    list.add(data);
                }
                return list;

 src/main/java/com/whyc/util/DateUtil.java

@@ -545,6 +545,39 @@
    }

    /**
     *  查询使用时,需要处理 默认表查询的问题 倒序
     *  示例:
     *  <p>
     * {2020=[Fri Dec 25 14:12:22 CST 2020, Thu Dec 31 23:59:59 CST 2020]}
     * {2021=[Fri Jan 01 00:00:00 CST 2021, Fri Dec 31 23:59:59 CST 2021]}
     * {2022=[Sat Jan 01 00:00:00 CST 2022, Tue Nov 29 09:12:22 CST 2022]}
     * 或
     * {2020=[Fri Dec 25 14:12:22 CST 2020, Thu Dec 31 23:59:59 CST 2020]}
     * {2021=[Fri Jan 01 00:00:00 CST 2021, Fri Dec 31 23:59:59 CST 2021]}
     * {2022=[Sat Jan 01 00:00:00 CST 2022, Thu Dec 29 09:12:22 CST 2022]}
     * {default=[Wed Dec 14 15:27:13 CST 2022, Thu Dec 29 09:12:22 CST 2022]}
     * </p>
     * key为数字,表示年度分表;default为默认表
     * @param startTimeDate 起始时间
     * @param endTimeDate 终止时间
     * @return 需要查询的表及查询时间
     * @throws ParseException
     */
    public static Map<String,List<Date>> getQueryTimeForSubTablesDescWithOutDefault(Date startTimeDate,Date endTimeDate) throws ParseException {
        //根据筛选的时间段,查询出符合要求的各分表记录数
        //首先,如果当前时间超过7月,默认表中只有本年度的;如果没超过7月,默认表还存在上一年度的记录
        Calendar now = Calendar.getInstance();
        if(endTimeDate.compareTo(now.getTime())>0){ //校验优化最大查询时间
            endTimeDate = now.getTime();
        }
        Calendar timeBefore7Month = Calendar.getInstance();
        timeBefore7Month.add(Calendar.MONTH,-7);

        LinkedHashMap<String,List<Date>> yearTimeMap = (LinkedHashMap<String, List<Date>>) DateUtil.getYearListDesc(startTimeDate, endTimeDate);
        return yearTimeMap;
    }

    /**
     * 按年划分
     * 优化getQueryTimeForSubTablesDesc,待测试
     * 更新default表是否查询的逻辑,改||为&&

 src/main/java/com/whyc/util/SubTablePageInfoUtil.java

@@ -4,6 +4,8 @@
import com.whyc.factory.ThreadPoolExecutorFactory;
import com.whyc.mapper.CommonMapper;
import com.whyc.pojo.db_alarm.BattAlarmHistory;
import com.whyc.pojo.db_power_alarm.PowerAlarm;
import com.whyc.pojo.db_power_alarm.PowerAlarmHistory;
import com.whyc.service.SubTableService;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
@@ -32,24 +34,17 @@
                                        Date startTime,Date endTime,
                                        String dbName,String tablePrefix,
                                        Object pojo) throws ParseException {
        Map<String, List<Date>> queryTimeForSubTables = DateUtil.getQueryTimeForSubTablesDesc(startTime, endTime);
        Map<String, List<Date>> queryTimeForSubTables = DateUtil.getQueryTimeForSubTablesDescWithOutDefault(startTime, endTime);
        //查询分表是否存在,存在则查询结果
        Map<String,Integer> queryCountMap = new LinkedHashMap<>();
        Set<String> tableYearKeySet = queryTimeForSubTables.keySet();
        for (String tableYear : tableYearKeySet) {
            List<Date> queryTime = queryTimeForSubTables.get(tableYear);

            String tableName;
            if(!tableYear.equals("default")){
                //数值
                tableName = tablePrefix+"_"+tableYear;
                String existTableName = commonMapper.existTable(dbName, tableName);
                if(existTableName == null){
                    continue;
                }
            }else{
                //不存在default
                //tableName = tablePrefix;
            //数值
            String tableName = tablePrefix+"_"+tableYear;
            String existTableName = commonMapper.existTable(dbName, tableName);
            if(existTableName == null){
                continue;
            }
            //====== 根据不同类型类型对象对应调整 ======
@@ -60,6 +55,15 @@
                battAlarmHistory.setAlmEndTime(queryTime.get(1));
                battAlarmHistory.setRecordYear(tableName);
                int currentCount = service.getBattHisCount(battAlarmHistory);
                queryCountMap.put(tableYear, currentCount);
            }
            else if(pojo instanceof PowerAlarmHistory) {
                PowerAlarmHistory powerAlarmHistory = new PowerAlarmHistory();
                BeanUtils.copyProperties(pojo, powerAlarmHistory);
                powerAlarmHistory.setAlmStartTime(queryTime.get(0));
                powerAlarmHistory.setAlmEndTime(queryTime.get(1));
                powerAlarmHistory.setRecordYear(tableName);
                int currentCount = service.getPowerHisCount(powerAlarmHistory);
                queryCountMap.put(tableYear, currentCount);
            }

@@ -92,7 +96,7 @@
        for (String key : keySet) {
            List<Date> queryTime = queryTimeForSubTables.get(key);
            //====== 根据不同类型类型对象对应调整 ======
            String recordYear = key.equals("default") ? tablePrefix : tablePrefix + "_" + key;
            String recordYear = tablePrefix + "_" + key;
            if(pojo instanceof BattAlarmHistory) {
                BattAlarmHistory battAlarmHistory = new BattAlarmHistory();
                BeanUtils.copyProperties(pojo, battAlarmHistory);
@@ -105,6 +109,18 @@
                List<BattAlarmHistory> list =  service.getBattHisList(battAlarmHistory);
                dataList.addAll(list);
            }
            else if(pojo instanceof PowerAlarmHistory) {
                PowerAlarmHistory powerAlarmHistory = new PowerAlarmHistory();
                BeanUtils.copyProperties(pojo, powerAlarmHistory);
                powerAlarmHistory.setAlmStartTime(queryTime.get(0));
                powerAlarmHistory.setAlmEndTime(queryTime.get(1));
                powerAlarmHistory.setRecordYear(recordYear);
                List<Integer> limitList = tableAndLimitMap.get(key);
                powerAlarmHistory.setLimitStart(limitList.get(0));
                powerAlarmHistory.setLimitEnd(limitList.get(1));
                List<PowerAlarmHistory> list =  service.getPowerHisList(powerAlarmHistory);
                dataList.addAll(list);
            }
        }
        pageInfo.setList(dataList);
        return pageInfo;