更新告警历史统计查询

whycxzp

2023-08-24 df8b076c97cd2b0bb1dad158bd7f51982c6ee4d7

更新告警历史统计查询

 src/main/java/com/whyc/controller/AlarmHistoryController.java

New file
@@ -0,0 +1,32 @@
package com.whyc.controller;

import com.whyc.dto.Response;
import com.whyc.service.AlarmHistoryService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.format.annotation.DateTimeFormat;
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.util.Date;

@RequestMapping("alarmHistory")
@RestController
@Api(tags = "告警历史统计")
public class AlarmHistoryController {

    @Autowired
    private AlarmHistoryService service;

    @ApiOperation("告警统计")
    @GetMapping("alarmStatistics")
    public Response getAlarmStatistics(@RequestParam @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") Date startTime, @RequestParam @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss") Date endTime, @RequestParam(required = false) String stationId) throws ParseException {
        return service.getAlarmStatistics(startTime,endTime,stationId);

    }

}

 src/main/java/com/whyc/mapper/BattalarmDataHistoryMapper.java

@@ -32,4 +32,6 @@

    List<BattalarmDataHistory> getDataBeforeRetentionMonth(@Param("retentionTime") Date retentionTime);

    List<BattalarmDataHistory> getListByStationId(Date startTime, Date endTime, String stationId,String tableName);

}

 src/main/java/com/whyc/mapper/DevalarmDataHistoryMapper.java

@@ -31,4 +31,7 @@
    List<DevalarmDataHistory> getDataBeforeRetentionMonth(@Param("retentionTime") Date retentionTime);

    List<BattalarmDataHistory> getLevelSubList(int uId, String tableName);

    List<DevalarmDataHistory> getListByStationId(Date startTime, Date endTime, String stationId,String tableName);

}

 src/main/java/com/whyc/mapper/PwrdevAlarmHistoryMapper.java

@@ -33,4 +33,6 @@

    List<BattalarmDataHistory> getLevelSubList(int uId, String tableName, List<Integer> almTypes);

    List<PwrdevAlarmHistory> getListByStationId(Date startTime, Date endTime, String stationId,String tableName);

}

 src/main/java/com/whyc/pojo/BattalarmDataHistory.java

@@ -89,4 +89,16 @@
    @ApiModelProperty(value = "告警重要等级[0:重要；1:紧急]")
    private Integer almSeverity;

    @TableField(exist = false)
    private String stationName1;

    @TableField(exist = false)
    private String stationName2;

    @TableField(exist = false)
    private String stationName3;

    @TableField(exist = false)
    private String stationName5;

}

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

New file
@@ -0,0 +1,146 @@
package com.whyc.service;

import com.whyc.dto.AlarmDaoFactory;
import com.whyc.dto.Response;
import com.whyc.mapper.CommonMapper;
import com.whyc.mapper.CustomMapper;
import com.whyc.pojo.BattalarmDataHistory;
import com.whyc.pojo.DevalarmDataHistory;
import com.whyc.pojo.PwrdevAlarmHistory;
import com.whyc.util.DateUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.text.ParseException;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class AlarmHistoryService {

    @Autowired
    private BattalarmDataHistoryService battAlarmService;
    @Autowired
    private DevalarmDataHistoryService deviceAlarmService;
    @Autowired
    private PwrdevAlarmHistoryService powerAlarmService;

    @Resource
    private CommonMapper commonMapper;

    public Response getAlarmStatistics(Date startTime, Date endTime, String stationId) throws ParseException {
        Map<String,Object> resMap = new HashMap<>();

        Map<String, List<Date>> tableMap = DateUtil.getQueryTimeForSubTablesDesc(startTime, endTime);
        Set<String> yearSet = tableMap.keySet();
        List<BattalarmDataHistory> battList = new LinkedList<>();
        List<DevalarmDataHistory> deviceList =  new LinkedList<>();
        List<PwrdevAlarmHistory> powerList =  new LinkedList<>();
        for (String year : yearSet) {
            String battTableName;
            String deviceTableName;
            String powerTableName;
            if(year.equals("default")){
                battTableName = "tb_battalarm_data_history";
                deviceTableName = "tb_devalarm_data_history";
                powerTableName = "tb_pwrdev_alarm_history";
            }else{
                battTableName = "tb_battalarm_data_history_"+year;
                deviceTableName = "tb_devalarm_data_history_"+year;
                powerTableName = "tb_pwrdev_alarm_history_"+year;
            }
            List<Date> dates = tableMap.get(year);
            String existTableNameBatt = commonMapper.existTable("db_alarm", battTableName);
            if(existTableNameBatt != null){
                List<BattalarmDataHistory> battTemp =  battAlarmService.getListByStationId(dates.get(0),dates.get(1),stationId,battTableName);
                battList.addAll(battTemp);
            }
            String existTableNameDevice = commonMapper.existTable("db_alarm", deviceTableName);
            if(existTableNameDevice != null){
                List<DevalarmDataHistory> deviceTemp =  deviceAlarmService.getListByStationId(dates.get(0),dates.get(1),stationId,deviceTableName);
                deviceList.addAll(deviceTemp);
            }
            String existTableNamePower= commonMapper.existTable("db_pwrdev_alarm", powerTableName);
            if(existTableNamePower != null){
                List<PwrdevAlarmHistory> powerTemp =  powerAlarmService.getListByStationId(dates.get(0),dates.get(1),stationId,powerTableName);
                powerList.addAll(powerTemp);
            }

        }

        if(battList.size()!=0) {
            Map<String, BattalarmDataHistory> battResMap = new HashMap<>();
            Map<Integer, List<BattalarmDataHistory>> battMap = battList.stream().collect(Collectors.groupingBy(BattalarmDataHistory::getAlmSignalId));
            Set<Integer> battAlarmSignalIdSet = battMap.keySet();
            for (Integer battAlarmSignalId : battAlarmSignalIdSet) {
                List<BattalarmDataHistory> temp = battMap.get(battAlarmSignalId);
                int size = temp.size();
                Integer almLevel = temp.get(0).getAlmLevel();
                Integer almSeverity = temp.get(0).getAlmSeverity();

                BattalarmDataHistory history = new BattalarmDataHistory();
                history.setAlmLevel(almLevel);
                history.setAlmSeverity(almSeverity);
                history.setNum((long) size);

                battResMap.put(AlarmDaoFactory.getAllAlarmName(battAlarmSignalId), history);

            }

            resMap.put("batt", battResMap);
        }else{
            resMap.put("batt",null);
        }

        if(deviceList.size()!=0) {
            Map<String, DevalarmDataHistory> deviceResMap = new HashMap<>();
            Map<Integer, List<DevalarmDataHistory>> deviceMap = deviceList.stream().collect(Collectors.groupingBy(DevalarmDataHistory::getAlmType));
            Set<Integer> deviceAlarmTypeSet = deviceMap.keySet();
            for (Integer deviceAlarmType : deviceAlarmTypeSet) {
                List<DevalarmDataHistory> temp = deviceMap.get(deviceAlarmType);
                int size = temp.size();
                Integer almLevel = temp.get(0).getAlmLevel();
                Integer almSeverity = temp.get(0).getAlmSeverity();

                DevalarmDataHistory history = new DevalarmDataHistory();
                history.setAlmLevel(almLevel);
                history.setAlmSeverity(almSeverity);
                history.setNum((long) size);

                deviceResMap.put(AlarmDaoFactory.getAllAlarmName(deviceAlarmType), history);

            }

            resMap.put("device", deviceResMap);
        }else{
            resMap.put("device",null);
        }

        if(powerList.size()!=0) {
            Map<String, PwrdevAlarmHistory> powerResMap = new HashMap<>();
            Map<Integer, List<PwrdevAlarmHistory>> powerMap = powerList.stream().collect(Collectors.groupingBy(PwrdevAlarmHistory::getAlmType));
            Set<Integer> powerAlarmTypeSet = powerMap.keySet();
            for (Integer powerAlarmType : powerAlarmTypeSet) {
                List<PwrdevAlarmHistory> temp = powerMap.get(powerAlarmType);
                int size = temp.size();
                Integer almLevel = temp.get(0).getAlmLevel();
                Integer almSeverity = temp.get(0).getAlmSeverity();

                PwrdevAlarmHistory history = new PwrdevAlarmHistory();
                history.setAlmLevel(almLevel);
                history.setAlmSeverity(almSeverity);
                history.setNum((long) size);

                powerResMap.put(AlarmDaoFactory.getAllAlarmName(powerAlarmType), history);

            }

            resMap.put("power", powerResMap);
        }else{
            resMap.put("power",null);
        }
        return new Response().set(1,resMap);

    }
}

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

@@ -137,4 +137,8 @@
        dataHistory.setUsrId(data.getUsrId());
        mapper.insert(dataHistory);
    }
}

    public List<BattalarmDataHistory> getListByStationId(Date startTime, Date endTime, String stationId,String tableName) {
        return mapper.getListByStationId(startTime,endTime,stationId,tableName);
    }
}

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

@@ -105,4 +105,8 @@
        mapper.insert(dataHistory);

    }

    public List<DevalarmDataHistory> getListByStationId(Date startTime, Date endTime, String stationId,String tableName) {
        return mapper.getListByStationId(startTime,endTime,stationId,tableName);
    }
}

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

@@ -188,4 +188,8 @@

        mapper.insert(dataHistory);
    }

    public List<PwrdevAlarmHistory> getListByStationId(Date startTime, Date endTime, String stationId,String tableName) {
        return mapper.getListByStationId(startTime,endTime,stationId,tableName);
    }
}

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

@@ -424,8 +424,8 @@

    public static void main(String[] args) throws ParseException {
        Date d1 = YYYY_MM_DD_HH_MM_SS.parse("2020-12-25 14:12:22");
        Date d2 = YYYY_MM_DD_HH_MM_SS.parse("2022-12-29 09:12:22");
        Map<String, List<Date>> yearListForSubTables = getQueryTimeForSubTablesDesc(d1, d2);
        Date d2 = YYYY_MM_DD_HH_MM_SS.parse("2023-07-29 09:12:22");
        Map<String, List<Date>> yearListForSubTables = getQueryTimeForSubTables(d1, d2);
        Set<String> strings = yearListForSubTables.keySet();
        Set<Map.Entry<String, List<Date>>> entries = yearListForSubTables.entrySet();
        for (Map.Entry<String, List<Date>> entry : entries) {

 src/main/resources/mapper/BattalarmDataHistoryMapper.xml

@@ -221,5 +221,14 @@
        and db_user.tb_user_inf.uid = #{uId}
        )
    </select>
    <select id="getListByStationId" resultType="com.whyc.pojo.BattalarmDataHistory">
        select h.*,b.StationName1,b.StationName2,b.StationName3,b.StationName5 from db_alarm.${tableName} h,db_battinf.tb_battinf b
        where h.BattGroupId = b.BattGroupId
        <if test="stationId !=null and stationId !=''">
            and b.stationId = #{stationId}
        </if>
        and h.alm_start_time &gt;= #{startTime}
        and h.alm_start_time &lt;= #{endTime}
    </select>

</mapper>

 src/main/resources/mapper/CommonMapper.xml

@@ -53,6 +53,8 @@
        `alm_confirmed_time` datetime NOT NULL DEFAULT '1982-01-01 00:00:00',
        `alm_cleared_type` int(11) NOT NULL DEFAULT '0',
        `usr_Id` int(11) NOT NULL DEFAULT '0',
        `alm_trigger` int(11) NOT NULL DEFAULT '0',
        `alm_severity` int(11) NOT NULL DEFAULT '0',
        PRIMARY KEY (`num`),
        KEY `index_record_id` (`record_id`),
        KEY `index_pwrdev_id` (`PowerDeviceId`),
@@ -63,11 +65,11 @@
    </insert>
    <insert id="insertBatch4PowerAlarmHistoryByYear">
        insert into db_pwrdev_alarm.`tb_pwrdev_alarm_history_${year}`
        (record_id,PowerDeviceId,alm_type,alm_level,alm_source,alm_index,alm_start_time,alm_end_time,alm_value,alm_is_confirmed,alm_confirmed_time,alm_cleared_type,usr_Id)
        (record_id,PowerDeviceId,alm_type,alm_level,alm_source,alm_index,alm_start_time,alm_end_time,alm_value,alm_is_confirmed,alm_confirmed_time,alm_cleared_type,usr_Id,alm_severity)
        values
        <foreach collection="temp" item="item" separator=",">
            (#{item.recordId},#{item.PowerDeviceId},#{item.almType},#{item.almLevel},#{item.almSource},#{item.almIndex},#{item.almStartTime},#{item.almEndTime},#{item.almValue},#{item.almIsConfirmed},
            #{item.almConfirmedTime},#{item.almClearedType},#{item.usrId})
            #{item.almConfirmedTime},#{item.almClearedType},#{item.usrId},#{item.almSeverity})
        </foreach>
    </insert>
    <insert id="createTable4BattAlarmDataHistoryByYear">
@@ -86,6 +88,7 @@
        `alm_confirmed_time` datetime NOT NULL DEFAULT '1982-01-01 00:00:00',
        `alm_cleared_type` int(11) NOT NULL DEFAULT '0',
        `usr_Id` int(11) NOT NULL DEFAULT '0',
        `alm_severity` int(11) NOT NULL DEFAULT '0',
        PRIMARY KEY (`num`),
        KEY `index_battgroup_id` (`BattGroupId`),
        KEY `index_Record_Id` (`Record_Id`),
@@ -98,11 +101,11 @@
    </insert>
    <insert id="insertBatch4BattAlarmDataHistoryByYear">
        insert into db_alarm.`tb_battalarm_data_history_${year}`
        (BattGroupId,MonNum,Record_Id,alm_id,alm_signal_id,alm_level,alm_start_time,alm_end_time,alm_value,alm_is_confirmed,alm_confirmed_time,alm_cleared_type,usr_Id)
        (BattGroupId,MonNum,Record_Id,alm_id,alm_signal_id,alm_level,alm_start_time,alm_end_time,alm_value,alm_is_confirmed,alm_confirmed_time,alm_cleared_type,usr_Id,alm_severity)
        values
        <foreach collection="temp" item="item" separator=",">
            (#{item.BattGroupId},#{item.MonNum},#{item.recordId},#{item.almId},#{item.almSignalId},#{item.almLevel},#{item.almStartTime},#{item.almEndTime},#{item.almValue},#{item.almIsConfirmed},
            #{item.almConfirmedTime},#{item.almClearedType},#{item.usrId})
            #{item.almConfirmedTime},#{item.almClearedType},#{item.usrId},#{item.almSeverity})
        </foreach>
    </insert>
    <insert id="createTable4DevAlarmDataHistoryByYear">
@@ -120,6 +123,7 @@
        `alm_confirmed_time` datetime NOT NULL DEFAULT '1982-01-01 00:00:00',
        `alm_cleared_type` int(11) NOT NULL DEFAULT '0',
        `usr_Id` int(11) NOT NULL DEFAULT '0',
        `alm_severity` int(11) NOT NULL DEFAULT '0',
        PRIMARY KEY (`num`),
        KEY `index_record_id` (`record_id`),
        KEY `index_dev_id` (`dev_id`),
@@ -130,11 +134,11 @@
    </insert>
    <insert id="insertBatch4DevAlarmDataHistoryByYear">
        insert into db_alarm.`tb_devalarm_data_history_${year}`
        (record_id,dev_id,dev_ip,alm_type,alm_level,alm_start_time,alm_end_time,alm_value,alm_is_confirmed,alm_confirmed_time,alm_cleared_type,usr_Id)
        (record_id,dev_id,dev_ip,alm_type,alm_level,alm_start_time,alm_end_time,alm_value,alm_is_confirmed,alm_confirmed_time,alm_cleared_type,usr_Id,alm_severity)
        values
        <foreach collection="temp" item="item" separator=",">
            (#{item.recordId},#{item.devId},#{item.devIp},#{item.almType},#{item.almLevel},#{item.almStartTime},#{item.almEndTime},#{item.almValue},#{item.almIsConfirmed},
            #{item.almConfirmedTime},#{item.almClearedType},#{item.usrId})
            #{item.almConfirmedTime},#{item.almClearedType},#{item.usrId},#{item.almSeverity})
        </foreach>
    </insert>
    <insert id="createBattState_RT_RamDB_Table">

 src/main/resources/mapper/DevalarmDataHistoryMapper.xml

@@ -212,6 +212,15 @@
        db_user.tb_user_battgroup_baojigroup_usr.uid
        and db_user.tb_user_inf.uid = #{uId})
    </select>
    <select id="getListByStationId" resultType="com.whyc.pojo.DevalarmDataHistory">
        select h.*,b.StationName1,b.StationName2,b.StationName3,b.StationName5 from db_alarm.${tableName} h,db_battinf.tb_battinf b
        where h.dev_id = b.FBSDeviceId
        <if test="stationId !=null and stationId !=''">
            and b.stationId = #{stationId}
        </if>
        and h.alm_start_time &gt;= #{startTime}
        and h.alm_start_time &lt;= #{endTime}
    </select>


</mapper>

 src/main/resources/mapper/PwrdevAlarmHistoryMapper.xml

@@ -359,5 +359,14 @@
            and db_user.tb_user_inf.uid=#{uId} )
        </where>
    </select>
    <select id="getListByStationId" resultType="com.whyc.pojo.PwrdevAlarmHistory">
        select h.*,b.StationName1,b.StationName2,b.StationName3,b.StationName5 from db_pwrdev_alarm.${tableName} h,db_pwrdev_inf.tb_pwrdev_inf b
        where h.PowerDeviceId = b.PowerDeviceId
        <if test="stationId !=null and stationId !=''">
            and b.stationId = #{stationId}
        </if>
        and h.alm_start_time &gt;= #{startTime}
        and h.alm_start_time &lt;= #{endTime}
    </select>

</mapper>