历史数据查询

DYL

2025-02-08 182751d469c239ac6f718cd37fb0249d61abae21

历史数据查询

 zhitan-admin/src/main/java/com/zhitan/web/controller/history/HistoryDataTrendController.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,224 @@
package com.zhitan.web.controller.history;

import cn.hutool.core.date.DateUtil;
import com.zhitan.basicdata.domain.MeterImplement;
import com.zhitan.basicdata.services.IMeterImplementService;
import com.zhitan.common.annotation.Log;
import com.zhitan.common.core.controller.BaseController;
import com.zhitan.common.core.domain.AjaxResult;
import com.zhitan.common.enums.BusinessType;
import com.zhitan.common.enums.RetrievalModes;
import com.zhitan.common.utils.poi.ExcelUtil;
import com.zhitan.history.domain.dto.HistoricalDataDTO;
import com.zhitan.history.domain.vo.HistoricalDataExcel;
import com.zhitan.history.domain.vo.HistoricalDataVO;
import com.zhitan.model.domain.EnergyIndex;
import com.zhitan.model.service.IEnergyIndexService;
import com.zhitan.realtimedata.domain.TagValue;
import com.zhitan.realtimedata.service.RealtimeDatabaseService;
import org.apache.commons.lang3.ObjectUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * è®¾å¤‡å¯åœå®žæ—¶ç›‘测Controller
 *
 * @author sys
 * @date 2020-03-30
 */
@RestController
@RequestMapping("/dataMonitoring/historyDataTrend")
public class HistoryDataTrendController extends BaseController {
    @Autowired
    private final IEnergyIndexService energyIndexService;

    @Autowired
    private final IMeterImplementService meterImplementService;

    @Autowired
    private final RealtimeDatabaseService realtimeDatabaseService;


    public HistoryDataTrendController(IEnergyIndexService energyIndexService,
                                      IMeterImplementService meterImplementService,
                                      RealtimeDatabaseService realtimeDatabaseService) {
        this.energyIndexService = energyIndexService;
        this.meterImplementService = meterImplementService;
        this.realtimeDatabaseService = realtimeDatabaseService;
    }


    @Log(title = "获取模型节点关联采集指标", businessType = BusinessType.UPDATE)
    @GetMapping("/energyIndex/list")
    public AjaxResult getSettingIndex(EnergyIndex energyIndex) {
        try {
            List<EnergyIndex> infoList = energyIndexService.selectEnergyIndexList(energyIndex);
//            List<String> codeList= infoList.stream().map(EnergyIndex::getCode).collect(Collectors.toList());
//            List<TagValue> valList = realtimeDatabaseService.retrieve(codeList);
//            List resultList = new ArrayList();
            return AjaxResult.success(infoList);
        } catch (Exception ex) {
            logger.error("获取关联采集指标出错！", ex);
            return AjaxResult.error("获取关联指标出错!");
        }
    }

    @Log(title = "根据时间与点位查询历史监测数据", businessType = BusinessType.UPDATE)
    @GetMapping("/getHistoricalDataByIndexId")
    public AjaxResult getHistoricalDataByIndexId(HistoricalDataDTO dto) {
        try {
            // èŽ·å–点位信息
            EnergyIndex energyIndex = energyIndexService.selectEnergyIndexById(dto.getIndexId());
            if (ObjectUtils.isEmpty(energyIndex)) {
                return AjaxResult.error("未找到点位信息");
            }
            Date beginTime = dto.getDataTime();
            Date endTime;
            // æŸ¥è¯¢æ¡æ•°
            int count = 1440;
            if ("DAY".equals(dto.getTimeType())) {
                endTime = DateUtil.endOfDay(beginTime);
            } else {
                count = 3600;
                endTime = DateUtil.offsetSecond(DateUtil.offsetHour(beginTime, 1), -1);
            }
            // æŸ¥è¯¢è®¡é‡å™¨å…·
            MeterImplement info = meterImplementService.selectMeterImplementById(energyIndex.getMeterId());
            List<TagValue> tagValueList = realtimeDatabaseService.retrieve(energyIndex.getCode(), beginTime, endTime,
                    RetrievalModes.BestFit, count);
            List<HistoricalDataVO> voList = new ArrayList<>();
            Date date = DateUtil.date();
            for (int i = 0; i < count + 1; i++) {
                HistoricalDataVO vo = new HistoricalDataVO();
                vo.setIndexId(energyIndex.getIndexId());
                String indexName = energyIndex.getName();
                if (ObjectUtils.isNotEmpty(info)) {
                    indexName = info.getInstallactionLocation() + "_" + info.getMeterName() + "_" + indexName;
                }
                vo.setIndexName(indexName);
                // å–值
                String value = "--";
                String usedValue = "--";
                if (beginTime.getTime() <= date.getTime()) {
                    try {
                        TagValue tagValue = tagValueList.get(i);
                        BigDecimal cumulative = BigDecimal.valueOf(tagValue.getValue());

                        if ("SWWSDJ_SD".equals(energyIndex.getCode()) || "SWWSDJ_WD".equals(energyIndex.getCode())) {
                            cumulative = cumulative.multiply(BigDecimal.valueOf(0.1));
                        }
                        if (i > 0) {
                            TagValue previousTagValue = tagValueList.get(i - 1);
                            BigDecimal previousValue = BigDecimal.ZERO;
                            if (ObjectUtils.isNotEmpty(previousTagValue.getValue())) {
                                previousValue = BigDecimal.valueOf(previousTagValue.getValue());
                            }
                            if ("SWWSDJ_SD".equals(energyIndex.getCode()) || "SWWSDJ_WD".equals(energyIndex.getCode())) {
                                previousValue = previousValue.multiply(BigDecimal.valueOf(0.1));

                            }
                            usedValue = String.valueOf(cumulative.subtract(previousValue).setScale(2, RoundingMode.HALF_UP));
                        }

                        value = String.valueOf(cumulative.setScale(2, RoundingMode.HALF_UP));
                    } catch (Exception ignored) {
                    }
                }
                // æ—¶é—´
                String timeName = DateUtil.formatDateTime(beginTime);
                vo.setDataTime(timeName);
                if ("DAY".equals(dto.getTimeType())) {
                    beginTime = DateUtil.offsetMinute(beginTime, 1);
                } else {
                    beginTime = DateUtil.offsetSecond(beginTime, 1);
                }
                vo.setUsedValue(String.valueOf(usedValue));
                vo.setValue(String.valueOf(value));
                voList.add(vo);
            }
            return AjaxResult.success(voList);
        } catch (Exception ex) {
            logger.error("查询历史监测数据出错！", ex);
            return AjaxResult.error("查询历史监测数据出错!");
        }
    }

    @Log(title = "导出Excel", businessType = BusinessType.UPDATE)
    @GetMapping("/export")
    public AjaxResult export(HistoricalDataDTO dto) {
        try {
            // èŽ·å–点位信息
            EnergyIndex energyIndex = energyIndexService.selectEnergyIndexById(dto.getIndexId());
            if (ObjectUtils.isEmpty(energyIndex)) {
                return AjaxResult.success("未找到点位信息");
            }
            Date beginTime = dto.getDataTime();
            Date endTime;
            // æŸ¥è¯¢æ¡æ•°
            int count = 23;
            if ("DAY".equals(dto.getTimeType())) {
                endTime = DateUtil.endOfDay(beginTime);
            } else {
                count = 19;
                endTime = DateUtil.offsetSecond(DateUtil.offsetHour(beginTime, 1), -1);
            }
            // æŸ¥è¯¢è®¡é‡å™¨å…·
            MeterImplement infor = meterImplementService.selectMeterImplementById(energyIndex.getMeterId());
            List<TagValue> tagValueList = realtimeDatabaseService.retrieve(energyIndex.getCode(), beginTime, endTime,
                    RetrievalModes.BestFit, count);
            List<HistoricalDataExcel> excelList = new ArrayList<>();
            Date date = DateUtil.date();
            for (int i = 0; i < count + 1; i++) {
                HistoricalDataExcel vo = new HistoricalDataExcel();
                String indexName = energyIndex.getName();
                if (ObjectUtils.isNotEmpty(infor)) {
                    indexName = infor.getInstallactionLocation() + "_" + infor.getMeterName() + "_" + indexName;
                }
                vo.setIndexName(indexName);
                // å–值
                String value = "--";
                String usedValue = "--";
                if (beginTime.getTime() <= date.getTime()) {
                    try {
                        TagValue tagValue = tagValueList.get(i);
                        BigDecimal cumulative = BigDecimal.valueOf(tagValue.getValue());
                        if (i > 0) {
                            TagValue previousTagValue = tagValueList.get(i - 1);
                            BigDecimal previousValue = BigDecimal.valueOf(previousTagValue.getValue());
                            usedValue = String.valueOf(cumulative.subtract(previousValue).setScale(2, RoundingMode.HALF_UP));
                        }
                        value = String.valueOf(cumulative.setScale(2, RoundingMode.HALF_UP));
                    } catch (Exception ignored) {
                    }
                }
                // æ—¶é—´
                String timeName = DateUtil.formatDateTime(beginTime);
                vo.setDataTime(timeName);
                if ("DAY".equals(dto.getTimeType())) {
                    beginTime = DateUtil.offsetHour(beginTime, 1);
                } else {
                    beginTime = DateUtil.offsetMinute(beginTime, 3);
                }
                vo.setValue(String.valueOf(value));
                vo.setUsedValue(String.valueOf(usedValue));
                excelList.add(vo);
            }
            ExcelUtil<HistoricalDataExcel> util = new ExcelUtil<>(HistoricalDataExcel.class);
            String sheetName = "历史数据统计" + DateUtil.formatDate(dto.getDataTime());
//            return util.exportRealTimeDataExcel(excelList, sheetName);
            return util.exportExcel(excelList, sheetName);
        } catch (Exception ex) {
            logger.error("导出Excel数据出错！", ex);
            return AjaxResult.error("导出Excel数据出错!");
        }
    }

}

 zhitan-system/src/main/java/com/zhitan/history/domain/dto/HistoricalDataDTO.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,63 @@
package com.zhitan.history.domain.dto;


import com.fasterxml.jackson.annotation.JsonFormat;
import org.springframework.format.annotation.DateTimeFormat;

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
import java.util.Date;

/**
 * åŽ†å²ç›‘测数据请求 DTO
 *
 * @Author: Zhujw
 * @Date: 2023/3/7
 */
public class HistoricalDataDTO {

    /**
     * ç‚¹ä½id
     */
    @NotBlank(message = "未找到点位信息")
    private String indexId;

    /**
     * æ—¶é—´ç±»åž‹
     */
    @NotBlank(message = "未找到时间类型")
    private String timeType;

    /**
     * æŸ¥è¯¢æ—¶é—´
     */
    @JsonFormat(timezone = "GMT+8",pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
    @NotNull(message = "未找到时间信息")
    private Date dataTime;

    public String getIndexId() {
        return indexId;
    }

    public void setIndexId(String indexId) {
        this.indexId = indexId;
    }

    public String getTimeType() {
        return timeType;
    }

    public void setTimeType(String timeType) {
        this.timeType = timeType;
    }

    public Date getDataTime() {
        return dataTime;
    }

    public void setDataTime(Date dataTime) {
        this.dataTime = dataTime;
    }

}

 zhitan-system/src/main/java/com/zhitan/history/domain/vo/HistoricalDataExcel.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,70 @@
package com.zhitan.history.domain.vo;


import com.zhitan.common.annotation.Excel;

/**
 * åŽ†å²ç›‘测数据返回 Excel
 *
 * @Author: Zhujw
 * @Date: 2023/3/7
 */
public class HistoricalDataExcel {

    /**
     * ç‚¹ä½åç§°
     */
    @Excel(name = "点位名称")
    private String indexName;

    /**
     * å€¼
     */
    @Excel(name = "值")
    private String value;

    /**
     * ä½¿ç”¨é‡
     */
    @Excel(name = "使用量")
    private String usedValue;

    /**
     * æ—¶é—´
     */
    @Excel(name = "时间")
    private String dataTime;


    public String getIndexName() {
        return indexName;
    }

    public void setIndexName(String indexName) {
        this.indexName = indexName;
    }

    public String getDataTime() {
        return dataTime;
    }

    public void setDataTime(String dataTime) {
        this.dataTime = dataTime;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }

    public String getUsedValue() {
        return usedValue;
    }

    public void setUsedValue(String usedValue) {
        this.usedValue = usedValue;
    }
}

 zhitan-system/src/main/java/com/zhitan/history/domain/vo/HistoricalDataVO.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,77 @@
package com.zhitan.history.domain.vo;


/**
 * åŽ†å²ç›‘测数据返回 VO
 *
 * @Author: Zhujw
 * @Date: 2023/3/7
 */
public class HistoricalDataVO {

    /**
     * ç‚¹ä½id
     */
    private String indexId;

    /**
     * ç‚¹ä½åç§°
     */
    private String indexName;

    /**
     * æ—¶é—´
     */
    private String dataTime;

    /**
     * å€¼
     */
    private String value;

    /**
     * ä½¿ç”¨é‡
     */
    private String usedValue;


    public String getIndexId() {
        return indexId;
    }

    public void setIndexId(String indexId) {
        this.indexId = indexId;
    }

    public String getIndexName() {
        return indexName;
    }

    public void setIndexName(String indexName) {
        this.indexName = indexName;
    }

    public String getDataTime() {
        return dataTime;
    }

    public void setDataTime(String dataTime) {
        this.dataTime = dataTime;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }

    public String getUsedValue() {
        return usedValue;
    }

    public void setUsedValue(String usedValue) {
        this.usedValue = usedValue;
    }
}

 zhitan-system/src/main/java/com/zhitan/realtimedata/data/RealtimeDatabaseManager.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,163 @@
package com.zhitan.realtimedata.data;

import com.influxdb.client.InfluxDBClient;
import com.influxdb.client.InfluxDBClientFactory;
import com.influxdb.client.domain.HealthCheck;
import com.zhitan.common.enums.CollectionModes;
import com.zhitan.common.enums.RetrievalModes;
import com.zhitan.realtimedata.config.RtdbConfig;
import com.zhitan.realtimedata.domain.TagValue;
import com.zhitan.realtimedata.service.RealtimeDatabase;
import org.apache.commons.lang3.StringUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Optional;

/**
 * @author èŒƒæ–°å¯Œ å®žæ—¶æ•°æ®åº“访问管理.
 */
@Component
public class RealtimeDatabaseManager {

    private final RealtimeDatabase connection;

    private final Logger logger = LogManager.getLogger(RealtimeDatabaseManager.class);

    public RealtimeDatabaseManager(RtdbConfig config) {
    connection = new VirtualRtdb();
        try {
            InfluxDBClient influxDBClient = InfluxDBClientFactory.create(config.getHost(),
                    config.getToken().toCharArray(), config.getOrg(), config.getBucket());

            logger.error("--------------------实时库连接成功--------------------");
            HealthCheck health = influxDBClient.health();
            if (health.getStatus() == HealthCheck.StatusEnum.FAIL) {
                influxDBClient.close();
            }
        } catch (Exception e) {
            logger.error(e);
        }
    }

    public TagValue retrieve(String tagCode) {
        List<String> tagCodes = new ArrayList<>();
        tagCodes.add(tagCode);
        List<TagValue> tagValues = retrieve(tagCodes);
        if (!tagValues.isEmpty()) {
            return tagValues.get(0);
        }

        return null;
    }

    public List<TagValue> retrieve(List<String> tagCodes) {
        List<TagValue> tagValues = new ArrayList<>();
        try {
            tagValues = connection.retrieve(tagCodes);
        } catch (Exception e) {
            logger.error(e);
        }

        return tagValues;
    }

    public TagValue retrieve(String tagCode, Date dataTime, String timeCode) {
        List<String> tagCodes = new ArrayList<>();
        tagCodes.add(tagCode);
        List<TagValue> tagValues = retrieve(tagCodes, dataTime, timeCode);
        if (!tagValues.isEmpty()) {
            return tagValues.get(0);
        }

        return null;
    }

    public List<TagValue> retrieve(List<String> tagCodes, Date dataTime, String timeCode) {
        List<TagValue> tagValues = new ArrayList<>();
        try {
            List<TagValue> tmp = connection.retrieve(tagCodes, dataTime, timeCode);
            for (String tagCode : tagCodes) {
                Optional<TagValue> tagValue = tmp.stream()
                        .filter(f -> StringUtils.equalsIgnoreCase(f.getTagCode(), tagCode)).findAny();
                TagValue value;
                if (!tagValue.isPresent()) {
                    value = new TagValue();
                    value.setTagCode(tagCode);
                    value.setDataTime(dataTime);
                } else {
                    value = tagValue.get();
                }

                tagValues.add(value);
            }
        } catch (Exception e1) {
            logger.error(e1);
        }

        return tagValues;
    }

    public List<TagValue> retrieve(String tagCode, Date beginTime, Date endTime,
                                   RetrievalModes retrievalModes, int pointCount) {
        List<String> tagCodes = new ArrayList<>();
        tagCodes.add(tagCode);
        return retrieve(tagCodes, beginTime, endTime, retrievalModes, pointCount);
    }

    public List<TagValue> retrieve(List<String> tagCodes, Date beginTime, Date endTime,
                                   RetrievalModes retrievalModes, int pointCount) {
        List<TagValue> tagValues = new ArrayList<>();
        try {
            tagValues = connection.retrieve(tagCodes, beginTime, endTime, retrievalModes, pointCount);
        } catch (Exception e1) {
            logger.error(e1);
        }

        return tagValues;
    }

    public TagValue statistics(String tagCode, Date beginTime, Date endTime,
                               CollectionModes collectionModes) {
        List<String> tagCodes = new ArrayList<>();
        tagCodes.add(tagCode);
        List<TagValue> tagValues = statistics(tagCodes, beginTime, endTime, collectionModes);
        if (!tagValues.isEmpty()) {
            return tagValues.get(0);
        }

        return null;
    }

    public List<TagValue> statistics(List<String> tagCodes, Date beginTime, Date endTime,
                                     CollectionModes collectionModes) {
        List<TagValue> tagValues = new ArrayList<>();
        try {
            tagValues = connection.statistics(tagCodes, beginTime, endTime, collectionModes);
        } catch (Exception e1) {
            logger.error(e1);
        }

        return tagValues;
    }

    public void storeData(List<TagValue> tagValues) {
        try {
            connection.storeData(tagValues);
        } catch (Exception e1) {
            logger.error(e1);
        }
    }

    public void insertData(List<TagValue> tagValues) {
        try {
            connection.insertData(tagValues);
        } catch (Exception e1) {
            logger.error(e1);
        }
    }
}

 zhitan-system/src/main/java/com/zhitan/realtimedata/service/RealtimeDatabaseService.java

@@ -2,6 +2,7 @@


import com.zhitan.common.enums.CollectionModes;
import com.zhitan.common.enums.RetrievalModes;
import com.zhitan.realtimedata.domain.TagValue;

import java.util.Date;
@@ -105,4 +106,17 @@
     * @param tagValues æµ‹ç‚¹åŽ†å²æ—¶åˆ»æ•°æ®
     */
    void insertData(List<TagValue> tagValues);

    /**
     * èŽ·å–一段时间内测点的历史数据.
     *
     * @param tagCode        æµ‹ç‚¹ç¼–号
     * @param beginTime      å¼€å§‹æ—¶é—´
     * @param endTime        ç»“束时间
     * @param retrievalModes æŸ¥è¯¢æ•°æ®æ–¹å¼
     * @param pointCount     æµ‹ç‚¹å¾—到的数据个数
     * @return æµ‹ç‚¹åŽ†å²æ•°æ®
     */
    List<TagValue> retrieve(String tagCode, Date beginTime, Date endTime,
                            RetrievalModes retrievalModes, int pointCount);
}

 zhitan-system/src/main/java/com/zhitan/realtimedata/service/impl/RealtimeDatabaseServiceImpl.java

@@ -2,6 +2,8 @@

import com.google.common.collect.Lists;
import com.zhitan.common.enums.CollectionModes;
import com.zhitan.common.enums.RetrievalModes;
import com.zhitan.realtimedata.data.RealtimeDatabaseManager;
import com.zhitan.realtimedata.data.influxdb.InfluxDBRepository;
import com.zhitan.realtimedata.domain.TagValue;
import com.zhitan.realtimedata.service.RealtimeDatabaseService;
@@ -20,8 +22,11 @@

    private final InfluxDBRepository repository;

    public RealtimeDatabaseServiceImpl(InfluxDBRepository repository) {
    private final RealtimeDatabaseManager realtimeDatabaseManager;

    public RealtimeDatabaseServiceImpl(InfluxDBRepository repository, RealtimeDatabaseManager realtimeDatabaseManager) {
        this.repository = repository;
        this.realtimeDatabaseManager = realtimeDatabaseManager;
    }

    /**
@@ -146,4 +151,9 @@
        repository.store(tagValues);
    }

    @Override
    public List<TagValue> retrieve(String tagCode, Date beginTime, Date endTime,
                                   RetrievalModes retrievalModes, int pointCount) {
        return realtimeDatabaseManager.retrieve(tagCode, beginTime, endTime, retrievalModes, pointCount);
    }
}