设备告警历史和统计

lxw

2023-12-08 b98ff4a43a253467592e2e1d963500173dfc0a97

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
package com.whyc.service;
 
import com.whyc.dto.DevAlarmHisDto;
import com.whyc.mapper.CallBack;
import com.whyc.pojo.db_ckpwrdev_alarm.CKPowerDevAlarmHistory;
import com.whyc.util.DateUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
 
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
@Service
public class SubTablePageInfoService {
    @Autowired
    private MybatisSqlExecuteService sqlExecuteService;
    //查询DevAlm历史告警数量
    public int getCountForDevAlm(DevAlarmHisDto alm) {
        String sql="select  count(distinct num) as number from db_ckpwrdev_alarm." +alm.getRecordYear()
                +" where 1=1 ";
 
        if(alm.getAlmStartTime()!=null){
            sql+=" and alm_starttime  >='"+ DateUtil.format_YYYY_MM_DD_HH_MM_SS(alm.getAlmStartTime())+"' ";
        }
        if(alm.getAlmEndTime()!=null){
            sql+=" and alm_endtime  <='"+DateUtil.format_YYYY_MM_DD_HH_MM_SS(alm.getAlmEndTime())+"' ";
        }
        if(alm.getDevType()!=0){
           sql+=" and dev_type="+alm.getDevType();
        }
        if(alm.getAlmLevel()!=0){
            sql+=" and alm_level="+alm.getAlmLevel();
        }
        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 getList2(DevAlarmHisDto alm){
        String sql="select  * from db_ckpwrdev_alarm." +alm.getRecordYear()
                +" where 1=1 ";
        if(alm.getAlmStartTime()!=null){
            sql+=" and alm_starttime  >='"+ DateUtil.format_YYYY_MM_DD_HH_MM_SS(alm.getAlmStartTime())+"' ";
        }
        if(alm.getAlmEndTime()!=null){
            sql+=" and alm_endtime  <='"+DateUtil.format_YYYY_MM_DD_HH_MM_SS(alm.getAlmEndTime())+"' ";
        }
        if(alm.getDevType()!=0){
            sql+=" and dev_type="+alm.getDevType();
        }
        if(alm.getAlmLevel()!=0){
            sql+=" and alm_level="+alm.getAlmLevel();
        }
        sql+="  ORDER BY alm_starttime desc limit "+alm.getLimitStart()+","+alm.getLimitEnd()+" ";
        List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List<CKPowerDevAlarmHistory> list=new ArrayList<>();
                while (rs.next()){
                    CKPowerDevAlarmHistory ph=new CKPowerDevAlarmHistory();
                    ph.setNum(rs.getLong("num"));
                    ph.setPowerDeviceId(rs.getInt("power_device_id"));
                    ph.setAlmId(rs.getInt("alm_id"));
                    ph.setAlmName(rs.getString("alm_name"));
                    ph.setDevType(rs.getInt("dev_type"));
                    ph.setAlmLevel(rs.getInt("alm_level"));
                    ph.setAlmStartTime(rs.getTimestamp("alm_starttime"));
                    ph.setAlmEndTime(rs.getTimestamp("alm_endtime"));
                    ph.setAlmIsConfirmed(rs.getInt("alm_is_confirmed"));
                    ph.setAlmConfirmedTime(rs.getTimestamp("alm_confirmed_time"));
                    ph.setAlmClearedType(rs.getInt("alm_cleared_type"));
                    list.add(ph);
                }
                return list;
            }
        });
        return list;
    }
    //查询所有的历史时间表
    public List getDevAlmHisList() {
        String sql="SELECT table_name FROM information_schema.tables WHERE table_name LIKE 'tb_ckpowerdev_alarm_history_%'";
        List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List<String> list=new ArrayList<>();
                while (rs.next()){
                    list.add(rs.getString("table_name"));
                }
                return list;
            }
        });
        return list;
    }
    //统计按照等级的告警信息
    public List<CKPowerDevAlarmHistory> selectLevelCountList(String tableYear) {
        String sql="select  alm_level from db_ckpwrdev_alarm." +tableYear;
        List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List<CKPowerDevAlarmHistory> list=new ArrayList<>();
                while (rs.next()){
                    CKPowerDevAlarmHistory ph=new CKPowerDevAlarmHistory();
                    ph.setAlmLevel(rs.getInt("alm_level"));
                    list.add(ph);
                }
                return list;
            }
        });
        return list;
    }
}