alarm_daily表统计任务update

jinpengyong

2020-05-21 9b2e4fa2c1b8374d3496f56535547558efa61d30

alarm_daily表统计任务update

 src/main/java/com/moral/mapper/AlarmMapper.java

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

public interface AlarmMapper {

    List<Map<String, Object>> getAvgAlarmData(Map<String, Object> devices);
    List<Map<String, Object>> getAlarmData(Map<String, Object> devices);

    int insertAlarmDaily(List<Map<String,Object>> list);


 src/main/java/com/moral/service/AlarmService.java

@@ -5,7 +5,7 @@

public interface AlarmService {

    List<Map<String, Object>> getAvgAlarmData(Map<String,Object> devices);
    List<Map<String, Object>> getAlarmData();

    int insertAlarmDaily(List<Map<String,Object>> list);


 src/main/java/com/moral/service/impl/AlarmServiceImpl.java

@@ -1,14 +1,23 @@
package com.moral.service.impl;

import java.time.LocalDateTime;
import java.time.temporal.ChronoUnit;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;

import com.alibaba.druid.support.json.JSONUtils;
import com.moral.mapper.AlarmMapper;
import com.moral.service.AlarmService;
import com.moral.service.DeviceService;
import com.moral.service.SensorService;


@Service
@@ -17,9 +26,123 @@
    @Resource
    private AlarmMapper alarmMapper;

    @Resource
    private SensorService sensorService;

    @Resource
    private DeviceService deviceService;

    @Override
    public List<Map<String, Object>> getAvgAlarmData(Map<String, Object> devices) {
        return alarmMapper.getAvgAlarmData(devices);
    public List<Map<String, Object>> getAlarmData() {
        LocalDateTime time = LocalDateTime.now();
        LocalDateTime endTime = time.truncatedTo(ChronoUnit.DAYS);
        LocalDateTime startTime = endTime.minusDays(1);
        List<String> sensorKeys = sensorService.getSensorKeys();
        List<String> macs = deviceService.getMacs();
        String year = startTime.getYear() + "";
        String month = null;
        int monthValue = startTime.getMonthValue();
        if (monthValue < 10) {
            month = "0" + monthValue;
        } else {
            month = monthValue + "";
        }
        String yearAndMonth = year + month;
        Map<String, Object> devices = new HashMap<>();
        devices.put("sensorKeys", sensorKeys);
        devices.put("start", startTime);
        devices.put("end", endTime);
        devices.put("macs", macs);
        devices.put("yearAndMonth", yearAndMonth);
        List<Map<String, Object>> list = alarmMapper.getAlarmData(devices);
        for (Map<String, Object> map : list) {
            ArrayList<String> list1 = new ArrayList<>();
            ArrayList<String> list2 = new ArrayList<>();
            ArrayList<String> list3 = new ArrayList<>();
            for (String key : map.keySet()) {
                if (!key.equals("mac")) {
                    String value = map.get(key).toString().substring(1, 2);
                    if (value.equals("1")) {
                        list1.add(key);
                    } else if (value.equals("2")) {
                        list2.add(key);
                    } else if (value.equals("3")) {
                        list3.add(key);
                    }
                }
            }
            map.put("1", list1);
            map.put("2", list2);
            map.put("3", list3);
        }

        for (Map<String, Object> map : list) {
            Iterator<String> iter = map.keySet().iterator();
            while (iter.hasNext()) {
                String next = iter.next();
                if (!(next.equals("mac")) && !(next.equals("1")) && !(next.equals("2")) && !(next.equals("3"))) {
                    iter.remove();
                }
            }
        }

        for (int i = list.size() - 1; i >= 0; i--) {
            for (int j = i - 1; j >= 0; j--) {
                Map<String, Object> map1 = list.get(i);
                Map<String, Object> map2 = list.get(j);
                if (map1.get("mac").equals(map2.get("mac"))) {
                    List<String> frontList1 = (ArrayList<String>) map1.get("1");
                    List<String> frontList2 = (ArrayList<String>) map1.get("2");
                    List<String> frontList3 = (ArrayList<String>) map1.get("3");
                    List<String> backList1 = (ArrayList<String>) map2.get("1");
                    List<String> backList2 = (ArrayList<String>) map2.get("2");
                    List<String> backList3 = (ArrayList<String>) map2.get("3");
                    backList1.addAll(frontList1);
                    backList2.addAll(frontList2);
                    backList3.addAll(frontList3);
                    list.remove(map1);
                    List<String> list1 = backList1.stream().distinct().collect(Collectors.toList());
                    List<String> list2 = backList2.stream().distinct().collect(Collectors.toList());
                    List<String> list3 = backList3.stream().distinct().collect(Collectors.toList());
                    map2.put("1", list1);
                    map2.put("2", list2);
                    map2.put("3", list3);
                }
            }
        }

        for (Map<String, Object> map : list) {
            List<String> list1 = (ArrayList<String>) map.get("1");
            List<String> list2 = (ArrayList<String>) map.get("2");
            List<String> list3 = (ArrayList<String>) map.get("3");
            if (list1.size() == 0) {
                map.remove("1");
            }
            if (list2.size() == 0) {
                map.remove("2");
            }
            if (list3.size() == 0) {
                map.remove("3");
            }
        }

        List<Map<String, Object>> resultList = new ArrayList<>();
        for (Map<String, Object> map : list) {
            for (int i = 0; i < map.keySet().size() - 1; i++) {
                Map<String, Object> hashMap = new HashMap<>();
                Map<String, Object> jsonMap = new HashMap<>();
                hashMap.put("mac", map.get("mac").toString());
                hashMap.put("state", i + 1);
                hashMap.put("time", startTime);
                ArrayList<String> arrayList = (ArrayList<String>) map.get(i + 1 + "");
                for (String sensorKey : arrayList) {
                    jsonMap.put(sensorKey, 1);
                }
                hashMap.put("json", JSONUtils.toJSONString(jsonMap));
                resultList.add(hashMap);
            }
        }
        return resultList;
    }

    @Override
@@ -31,5 +154,4 @@
    public void createTable(String yearAndMonth) {
        alarmMapper.createTable(yearAndMonth);
    }

}

 src/main/java/com/moral/task/AlarmTableInsertTask.java

@@ -1,9 +1,6 @@
package com.moral.task;

import java.time.LocalDateTime;
import java.time.temporal.ChronoUnit;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@@ -14,90 +11,37 @@
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import com.alibaba.fastjson.JSONObject;
import com.moral.service.AlarmService;
import com.moral.service.DeviceService;
import com.moral.service.SensorService;
import com.xxl.job.core.biz.model.ReturnT;
import com.xxl.job.core.handler.annotation.XxlJob;

@Component
public class AlarmTableInsertTask {
    private static transient Logger logger = LoggerFactory.getLogger(AlarmTableInsertTask.class);

    @Resource
    private AlarmService alarmService;

    @Resource
    private SensorService sensorService;

    @Resource
    private DeviceService deviceService;

    @XxlJob("alarmDaily")
    public ReturnT insertAlarmDailyTable(String params) {
        LocalDateTime time = LocalDateTime.now();
        LocalDateTime endTime = time.truncatedTo(ChronoUnit.DAYS);
        LocalDateTime startTime = endTime.minusDays(1);
        List<String> sensorKeys = sensorService.getSensorKeys();
        List<String> macs = deviceService.getMacs();

        String year = startTime.getYear() + "";
        String month = null;
        int monthValue = startTime.getMonthValue();
        if (monthValue < 10) {
            month = "0" + monthValue;
        } else {
            month = monthValue + "";
        }
        String yearAndMonth = year + month;

        Map<String, Object> devices = new HashMap<>();
        devices.put("sensorKeys", sensorKeys);
        devices.put("start", startTime);
        devices.put("end", endTime);
        devices.put("macs", macs);
        devices.put("yearAndMonth",yearAndMonth);

        List<Map<String, Object>> dailyData = alarmService.getAvgAlarmData(devices);
        List<Map<String, Object>> dailyDataList = new ArrayList<>();
        for (Map<String, Object> deviceData : dailyData) {
            Map<String, Object> map = new HashMap<>();
            for (String sensorKey : sensorKeys) {
                if (deviceData.get(sensorKey) != null) {
                    map.put(sensorKey, deviceData.get(sensorKey).toString());
                }
            }
            JSONObject json = new JSONObject(map);
            deviceData.put("json", json);
        }

        for (Map<String, Object> dailyMap : dailyData) {
            Map<String, Object> map = new HashMap<>();
            String mac = dailyMap.get("mac").toString();
            String json = dailyMap.get("json").toString();
            String state = dailyMap.get("state").toString();
            map.put("mac", mac);
            map.put("json", json);
            map.put("state", state);
            map.put("time", startTime);
            dailyDataList.add(map);
        }
        if (!CollectionUtils.isEmpty(dailyDataList)) {
            alarmService.insertAlarmDaily(dailyDataList);
    public ReturnT insertAlarmDailyTable() {
        List<Map<String, Object>> list = alarmService.getAlarmData();
        if (!CollectionUtils.isEmpty(list)) {
            alarmService.insertAlarmDaily(list);
            return new ReturnT(200, "插入天表成功");
        }
        return new ReturnT(500, "插入天表失败");
    }


    @XxlJob("createAlarmSubTable")
    public void createAlarmSubTable(String params) {
        LocalDateTime time = LocalDateTime.now().plusMonths(1);;
    public ReturnT createAlarmSubTable() {
        LocalDateTime time = LocalDateTime.now().plusMonths(1);
        String year = time.getYear() + "";
        String month = time.getMonthValue() + "";
        if (time.getMonthValue() < 10) {
            month = "0" + time.getMonthValue();
        }
        alarmService.createTable(year + month);
        ReturnT returnT = new ReturnT(200, "创建alarm年月分表成功");
        return returnT;
    }
}

 src/main/resources/mapper/AlarmMapper.xml

@@ -1,25 +1,22 @@
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.moral.mapper.AlarmMapper">
    <select id="getAvgAlarmData" resultType="java.util.LinkedHashMap">
    <select id="getAlarmData" resultType="java.util.LinkedHashMap">
        SELECT
        a.mac,
        max(a.state) state
        mac
        <foreach collection="sensorKeys" open="," separator="," item="sensorKey">
            AVG(a.json->'$.${sensorKey}[0]') AS '${sensorKey}'
            json->'$.${sensorKey}[0]' AS '${sensorKey}'
        </foreach>
        FROM
        alarm_${yearAndMonth} a
        alarm_${yearAndMonth}
        WHERE
        a.time >= #{start}
        AND a.time <![CDATA[<]]> #{end}
        AND a.mac in
        time >= #{start}
        AND time <![CDATA[<]]> #{end}
        AND mac in
        <foreach collection="macs" open="(" separator="," close=")"
                 item="mac">
            #{mac}
        </foreach>
        group by
        a.mac
    </select>

    <insert id="insertAlarmDaily">
@@ -32,7 +29,7 @@
    </insert>

    <update id="createTable">
        CREATE TABLE `alarm_${yearAndMonth}` (
        CREATE TABLE IF NOT EXISTS `alarm_${yearAndMonth}` (
        `mac` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
        `json` json DEFAULT NULL,
        `state` int(2) DEFAULT NULL,