电池告警实时和历史

whycxzp

2025-03-24 82614438a300523cc13e22b6d175586a3ec8ed71

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

@@ -1,8 +1,11 @@
package com.whyc.service;

import com.whyc.dto.DevA200AlarmDto;
import com.whyc.mapper.CallBack;
import com.whyc.pojo.db_alarm.BattAlarmHis;
import com.whyc.pojo.db_alarm.BattAlarmHistory;
import com.whyc.pojo.db_alarm.DevLithiumAlarmDataYear;
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,15 +55,18 @@
        });
        return list;
    }
    //告警历史实时计算每张表查询总数
    public int getBattHisCount(BattAlarmHis battAlarmHis) {
        String sql="SELECT  count(*) as number FROM db_alarm."+battAlarmHis.getRecordYear()+" history " +
                " where history.binf_id="+battAlarmHis.getBinfId() ;
        if(battAlarmHis.getAlmStartTime()!=null){
            sql+=" and alm_start_time  >='"+ ThreadLocalUtil.format(battAlarmHis.getAlmStartTime(),1)+"' ";
    //电池告警历史实时计算每张表查询总数
    public int getBattHisCount(BattAlarmHistory battAlarmHistory) {
        String sql="SELECT  count(*) as number FROM db_alarm."+ battAlarmHistory.getRecordYear()+" history " +
                " where 1=1" ;
        if (battAlarmHistory.getAlmLevel()!=null){
            sql+=" and history.alm_level="+ battAlarmHistory.getAlmLevel();
        }
        if(battAlarmHis.getAlmEndTime()!=null){
            sql+=" and alm_start_time  <='"+ThreadLocalUtil.format(battAlarmHis.getAlmEndTime(),1)+"' ";
        if(battAlarmHistory.getAlmStartTime()!=null){
            sql+=" and alm_start_time  >='"+ ThreadLocalUtil.format(battAlarmHistory.getAlmStartTime(),1)+"' ";
        }
        if(battAlarmHistory.getAlmEndTime()!=null){
            sql+=" and alm_start_time  <='"+ThreadLocalUtil.format(battAlarmHistory.getAlmEndTime(),1)+"' ";
        }
        List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
            @Override
@@ -81,31 +87,95 @@
        }
        return num;
    }
    //告警历史实时
    public List<BattAlarmHis> getBattHisList(BattAlarmHis battAlarmHis) {
        String sql="SELECT * FROM db_alarm."+battAlarmHis.getRecordYear()+" history " +
                " where history.binf_id="+battAlarmHis.getBinfId() ;
        if(battAlarmHis.getAlmStartTime()!=null){
            sql+=" and alm_start_time  >='"+ ThreadLocalUtil.format(battAlarmHis.getAlmStartTime(),1)+"' ";
    //电池告警历史实时
    public List<BattAlarmHistory> getBattHisList(BattAlarmHistory battAlarmHistory) {
        String sql="SELECT * FROM db_alarm."+ battAlarmHistory.getRecordYear()+" history " +
                " where 1=1";
        if(battAlarmHistory.getAlmLevel()!=null){
            sql+=" and history.alm_level="+ battAlarmHistory.getAlmLevel();
        }
        if(battAlarmHis.getAlmEndTime()!=null){
            sql+=" and alm_start_time  <='"+ThreadLocalUtil.format(battAlarmHis.getAlmEndTime(),1)+"' ";
        if(battAlarmHistory.getAlmStartTime()!=null){
            sql+=" and alm_start_time  >='"+ ThreadLocalUtil.format(battAlarmHistory.getAlmStartTime(),1)+"' ";
        }
        sql+="  ORDER BY alm_start_time asc  limit "+battAlarmHis.getLimitStart()+","+battAlarmHis.getLimitEnd()+" ";
        List<BattAlarmHis> list=sqlExecuteService.executeQuery_call(sql, new CallBack() {
        if(battAlarmHistory.getAlmEndTime()!=null){
            sql+=" and alm_start_time  <='"+ThreadLocalUtil.format(battAlarmHistory.getAlmEndTime(),1)+"' ";
        }
        sql+="  ORDER BY alm_start_time asc  limit "+ battAlarmHistory.getLimitStart()+","+ battAlarmHistory.getLimitEnd()+" ";
        List<BattAlarmHistory> list=sqlExecuteService.executeQuery_call(sql, new CallBack() {
            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List list=new ArrayList();
                while (rs.next()){
                    BattAlarmHis data=new BattAlarmHis();
                    BattAlarmHistory data=new BattAlarmHistory();
                    data.setNum(rs.getInt("num"));
                    data.setBinfId(rs.getInt("binf_id"));
                    data.setBattGroupId(rs.getInt("binf_id"));
                    data.setAlmStartTime(rs.getTimestamp("alm_start_time"));
                    data.setAlmEndTime(rs.getTimestamp("alm_end_time"));
                    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;
@@ -148,4 +218,101 @@
        });
        return list.stream().findFirst().orElse((BattTestInfData) ActionUtil.objeNull);
    }

    //查询DevAlm历史告警数量
    public int getCountForDevAlm(DevA200AlarmDto dto) {
        String sql="select  count(distinct num) as number from db_alarm." +dto.getTableName()
                +" where 1=1 ";

        if(dto.getStartTime()!=null){
            sql+=" and alm_starttime  >='"+ ThreadLocalUtil.format(dto.getStartTime(),1)+"' ";
        }
        if(dto.getEndTime()!=null){
            sql+=" and alm_endtime  <='"+ThreadLocalUtil.format(dto.getEndTime(),1)+"' ";
        }
        if(dto.getDevType()!=null){
            sql+=" and  FLOOR(dev_id/100000000)="+dto.getDevType();
        }
        if(dto.getDevId()!=null){
            sql+=" and  dev_id="+dto.getDevId();
        }
        if(dto.getAlmId()!=null){
            sql+=" and alm_id="+dto.getAlmId();
        }
        sql+=" and dev_id in (" +
                "            SELECT distinct dev_id from db_user.tb_battgroup_baojigroup,db_user.tb_battgroup_usr" +
                "            where tb_battgroup_baojigroup.baoji_group_id=tb_battgroup_usr.baoji_group_id" +
                "           and uid="+dto.getUid()+
                ")";
        sql+="  order by alm_starttime desc ";
        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;
    }
    //查询devalm历史告警
    public List getListDevAlm(DevA200AlarmDto dto){
        String sql="select  * from db_alarm." +dto.getTableName()
                +" where 1=1 ";

        if(dto.getStartTime()!=null){
            sql+=" and alm_starttime  >='"+ ThreadLocalUtil.format(dto.getStartTime(),1)+"' ";
        }
        if(dto.getEndTime()!=null){
            sql+=" and alm_endtime  <='"+ThreadLocalUtil.format(dto.getEndTime(),1)+"' ";
        }
        if(dto.getDevType()!=null){
            sql+=" and  FLOOR(dev_id/100000000)="+dto.getDevType();
        }
        if(dto.getDevId()!=null){
            sql+=" and  dev_id="+dto.getDevId();
        }
        if(dto.getAlmId()!=null){
            sql+=" and alm_id="+dto.getAlmId();
        }
        sql+=" and dev_id in (" +
                "            SELECT distinct dev_id from db_user.tb_battgroup_baojigroup,db_user.tb_battgroup_usr" +
                "           where tb_battgroup_baojigroup.baoji_group_id=tb_battgroup_usr.baoji_group_id" +
                "           and uid="+dto.getUid()+
                ")";
        sql+="  ORDER BY alm_starttime desc  limit "+dto.getLimitStart()+","+dto.getLimitEnd()+" ";
        List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List<DevLithiumAlarmDataYear> list=new ArrayList<>();
                while (rs.next()){
                    DevLithiumAlarmDataYear dataYear=new DevLithiumAlarmDataYear();
                    dataYear.setNum(rs.getInt("num"));
                    dataYear.setDevId(rs.getInt("dev_id"));
                    dataYear.setAlmId(rs.getInt("alm_id"));
                    dataYear.setAlmSignalId(rs.getInt("alm_signal_id"));
                    dataYear.setAlmStarttime(rs.getTimestamp("alm_starttime"));
                    dataYear.setAlmValue(rs.getFloat("alm_value"));
                    dataYear.setAlmIsConfirmed(rs.getInt("alm_is_confirmed"));
                    dataYear.setConfirmedUid(rs.getInt("confirmed_uid"));
                    dataYear.setConfirmedTime(rs.getTimestamp("confirmed_time"));
                    dataYear.setAlmEndtime(rs.getTimestamp("alm_endtime"));
                    dataYear.setAlmClearedType(rs.getInt("alm_cleared_type"));
                    list.add(dataYear);
                }
                return list;
            }
        });
        return list;
    }

}