[100 million] report analysis on the fourth day

Posted by interrupt on Tue, 25 Jan 2022 16:36:07 +0100

Chapter IV statement analysis

Product prototype address:

https://app.mockplus.cn/run/prototype/wDiGCuki3_/iLu4KtCQ8ew/1Bv0BoAUyv

1. Equipment status statistics

1.1 demand analysis

In the system, the percentage distribution of equipment in different states shall be displayed in the form of pie chart on the home page. The specific effect is shown in the figure below:

![](images/4-1.png)

1.2 implementation ideas

(1) Write a method to obtain the total number of disconnected devices, disconnected devices and alarms from ElasticSearch

(2) By calculating the number of normal devices, the data is combined into the data format required by the front end and returned

1.3 code implementation

1.3.1 equipment quantity statistics

(1) Add a method to count the total number of devices in ESRepository:

/**
 * Count the number of all equipment
 * @return
 */
public Long getAllDeviceCount(){

    CountRequest countRequest=new CountRequest("devices");
    countRequest.query( QueryBuilders.matchAllQuery() );
    try {
        CountResponse response = restHighLevelClient.count(countRequest, RequestOptions.DEFAULT);
        return response.getCount();
    } catch (IOException e) {
        e.printStackTrace();
        return 0L;
    }
}

(2) Add a method to count the number of offline devices in ESRepository:

/**
 * Count the number of all offline devices
 * @return
 */
public Long getOfflineCount(){

    CountRequest countRequest=new CountRequest("devices");
    BoolQueryBuilder boolQueryBuilder=QueryBuilders.boolQuery();
    boolQueryBuilder.must( QueryBuilders.termQuery("online",false)  );

    countRequest.query( boolQueryBuilder );

    try {
        CountResponse response = restHighLevelClient.count(countRequest, RequestOptions.DEFAULT);
        return response.getCount();
    } catch (IOException e) {
        e.printStackTrace();
        return 0L;
    }

}

(3) Add a method to count the number of alarm devices in ESRepository:

/**
 * Count the number of all alarm devices
 * @return
 */
public Long getAlarmCount(){

    CountRequest countRequest=new CountRequest("devices");
    BoolQueryBuilder boolQueryBuilder=QueryBuilders.boolQuery();
    boolQueryBuilder.must( QueryBuilders.termQuery("online",true)  );
    boolQueryBuilder.must( QueryBuilders.termQuery("alarm",true)  );
    countRequest.query( boolQueryBuilder );

    try {
        CountResponse response = restHighLevelClient.count(countRequest, RequestOptions.DEFAULT);
        return response.getCount();
    } catch (IOException e) {
        e.printStackTrace();
        return 0L;
    }

}

Write unit tests:

@Test
public void testCount(){

    Long allDeviceCount = esRepository.getAllDeviceCount();//Total equipment
    System.out.println("Total equipment:"+allDeviceCount);

    Long offlineCount = esRepository.getOfflineCount();//Number of offline devices
    System.out.println("Offline device:"+offlineCount);

    Long alarmCount = esRepository.getAlarmCount();//Number of alarm equipment
    System.out.println("Alarm equipment:"+alarmCount);

}

1.3.2 equipment status distribution

(1) Add vo for encapsulation of status proportion

The chart tool used in the front end is EChars. The data format required for EChars pie chart is as follows:

data: [
           {value: 10200, name: 'Normal operation'},
           {value: 310, name: 'off-line'},
           {value: 234, name: 'call the police'}          
      ]

API is defined as follows:

![](images/4-90.png)

We define vo according to the format required by the front end

package com.yikekong.vo;

import lombok.Data;

@Data
public class PieVO {

    private String name;//name

    private Long value;//value
}

(2) Create the report service interface ReportService, and define the method to obtain the device status distribution

package com.yikekong.service;

import com.yikekong.vo.PieVO;

import java.util.List;

/**
 * Report service
 */
public interface ReportService {


    /**
     * Equipment status distribution
     * @return
     */
    List<PieVO> getStatusCollect();

}

Create an implementation class and implement the method in the implementation class

package com.yikekong.service.impl;

import com.google.common.collect.Lists;
import com.yikekong.es.ESRepository;
import com.yikekong.service.ReportService;
import com.yikekong.vo.PieVO;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
@Service
@Slf4j
public class ReportServiceImpl implements ReportService {

    @Autowired
    private ESRepository esRepository;

    @Override
    public List<PieVO> getStatusCollect() {

        Long allDeviceCount = esRepository.getAllDeviceCount();//Total equipment quantity
        Long offlineCount = esRepository.getOfflineCount();//Number of offline devices
        Long alarmCount = esRepository.getAlarmCount();//Number of alarm devices

        PieVO devicePie=new PieVO();
        devicePie.setName("Normal operation");
        devicePie.setValue(allDeviceCount-offlineCount-alarmCount);

        PieVO offlinePie=new PieVO();
        offlinePie.setName("off-line");
        offlinePie.setValue(offlineCount);

        PieVO alarmPie =new PieVO();
        alarmPie.setName("call the police");
        alarmPie.setValue(alarmCount);

        List<PieVO> pieVOList= Lists.newArrayList();
        pieVOList.add(devicePie);
        pieVOList.add(offlinePie);
        pieVOList.add(alarmPie);

        return pieVOList;
    }
}

(3) New method of DeviceController

package com.yikekong.controller;

import com.yikekong.service.ReportService;
import com.yikekong.vo.PieVO;
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.util.List;

@RestController
@RequestMapping("/report")
public class ReportController {

    @Autowired
    private ReportService reportService;

    /**
     * Equipment status distribution
     * @return
     */
    @GetMapping("/statusCollect")
    public List<PieVO> getStatusCollect(){
        return  reportService.getStatusCollect();
    }

}

1.3.3 real time monitoring data

The home page also needs to display the number of devices and the number of alarm devices

![](images/4-4.png)

API design:

![](images/4-5.png)

(1) Create vo class MonitorVO

/**
 * Real time monitoring data
 */
@Data
public class MonitorVO implements Serializable{
    /**
     * Number of equipment
     */
    private Long deviceCount;
    /**
     * Number of alarm devices
     */
    private Long alarmCount;
}

(2) New method of ReportController

@Autowired
private ESRepository esRepository;

/**
 * Obtain real-time monitoring data
 * @return
 */
@GetMapping("/monitor")
public MonitorVO getMonitorData(){
    MonitorVO monitor = new MonitorVO();
    monitor.setDeviceCount(esRepository.getAllDeviceCount());
    monitor.setAlarmCount(esRepository.getAlarmCount());
    return monitor;
}

2. Abnormal alarm Trend Report

2.1 demand analysis

The system shall be able to summarize the total number of exceptions reported at each time point according to the current 1 hour (the first data per minute, the current minute has no data, and the last data is displayed), the current 1 day (the first data per hour, the current hour has no data, and the last data is displayed), and the current 7 days (the display of 7-day hour data), Then, the front-end displays the data in the form of discount chart.

The specific time filtering and presentation format is as follows:

![](images/3-7.png)

The horizontal axis shows the time, and the vertical axis shows the total number of alarm times.

2.2 realization ideas

(1) Query the quota table in influxdb and make grouping statistics according to time points

(2) In the service layer, the statement is encapsulated and transformed into the data format required by the front end

2.3 code implementation

2.3.1 incluxdb time grouping statistics

The date format command can display the year, month, day, hour, minute and second after execution

precision rfc3339

Execute query

select count(value) from quota where alarm='1' and time>='2020-09-01' and time<='2020-09-30' group by time(1d)

Get running results

![](images/3-9.png)

It should be noted here:

The time function of InfluxDB is used in the group by group in the sql to be executed

group by time(1m): Group summary by minute
group by time(1h): group and summarize by hour
Group by time (1D): Group summary by day

Then, with the count function in select, we can get the total number in the summary in the specific time dimension, so that we can get the summary data we want.

In addition to returning pointValue according to the sql statement, InfluxDB will also return the time column corresponding to each data.

2.3.2 alarm trend statistics

(1) Define the pojo class of query results from incluxdb:

package com.yikekong.dto;

import lombok.Data;
import org.influxdb.annotation.Column;
import org.influxdb.annotation.Measurement;
import java.io.Serializable;

/**
 * Trend index point
 */
@Data
@Measurement(name = "quota")
public class TrendPoint implements Serializable{

    /**
     * time
     */
    @Column(name = "time")
    private String time;

    /**
     * Point in time data
     */
    @Column(name = "pointValue")
    private Integer pointValue;
}

(3) Define the method to obtain the abnormal indicator trend in the ReportService interface:

/**
 * Obtain abnormal trend indicators
 * @param start Start time yyyy MM DD HH: mm: SS
 * @param end End time yyyy MM DD HH: mm: SS
 * @param type Time statistics type (within 1:60 minutes, 2:24 hours on the same day, 3:7 days)
 * @return
 */
List<TrendPoint> getAlarmTrend(String start, String end, int type);

Implement this method in the implementation class ReportServiceImpl:

@Autowired
private InfluxRepository influxRepository;

@Override
public List<TrendPoint> getAlarmTrend(String start, String end, int type) {

    StringBuilder ql=new StringBuilder("select count(value) as pointValue from quota where alarm='1' ");
    ql.append("and time>='"+ start +"' and time<='"+ end+"' ");
    if(type==1){
        ql.append("group by time(1m)");
    }
    if(type==2){
        ql.append("group by time(1h)");
    }
    if(type==3){
        ql.append("group by time(1d)");
    }
    List<TrendPoint> trendPointList = influxRepository.query(ql.toString(), TrendPoint.class);

    return trendPointList;
}

Unit test:

@Autowired
private ReportService reportService;

@Test
public void testAlarmTrend(){

    List<TrendPoint> trendPointList = reportService.getAlarmTrend("2020-09-01", "2020-09-30", 3);

    for(TrendPoint trendPoint:trendPointList){
        try {
            System.out.println(JsonUtil.serialize(trendPoint));
        } catch (JsonProcessingException e) {
            e.printStackTrace();
        }
    }

}

The second parameter of getAlarmTrend is 1, 2 and 3 respectively. Observe the test results. The test results with parameter 3 are as follows:

{"time":"2020-09-01T00:00:00Z","pointValue":0}
{"time":"2020-09-02T00:00:00Z","pointValue":0}
{"time":"2020-09-03T00:00:00Z","pointValue":0}
{"time":"2020-09-04T00:00:00Z","pointValue":0}
{"time":"2020-09-05T00:00:00Z","pointValue":0}
{"time":"2020-09-06T00:00:00Z","pointValue":0}
{"time":"2020-09-07T00:00:00Z","pointValue":0}
{"time":"2020-09-08T00:00:00Z","pointValue":0}
{"time":"2020-09-09T00:00:00Z","pointValue":0}
{"time":"2020-09-10T00:00:00Z","pointValue":0}
{"time":"2020-09-11T00:00:00Z","pointValue":0}
{"time":"2020-09-12T00:00:00Z","pointValue":0}
{"time":"2020-09-13T00:00:00Z","pointValue":0}
{"time":"2020-09-14T00:00:00Z","pointValue":0}
{"time":"2020-09-15T00:00:00Z","pointValue":0}
{"time":"2020-09-16T00:00:00Z","pointValue":0}
{"time":"2020-09-17T00:00:00Z","pointValue":0}
{"time":"2020-09-18T00:00:00Z","pointValue":0}
{"time":"2020-09-19T00:00:00Z","pointValue":2}
{"time":"2020-09-20T00:00:00Z","pointValue":0}
{"time":"2020-09-21T00:00:00Z","pointValue":0}
{"time":"2020-09-22T00:00:00Z","pointValue":2379}
{"time":"2020-09-23T00:00:00Z","pointValue":0}
{"time":"2020-09-24T00:00:00Z","pointValue":14412}
{"time":"2020-09-25T00:00:00Z","pointValue":5}
{"time":"2020-09-26T00:00:00Z","pointValue":0}
{"time":"2020-09-27T00:00:00Z","pointValue":0}
{"time":"2020-09-28T00:00:00Z","pointValue":0}
{"time":"2020-09-29T00:00:00Z","pointValue":0}
{"time":"2020-09-30T00:00:00Z","pointValue":0}

2.3.3 report data encapsulation

The chart tool used in the front end is EChars, and the data format required for line chart is:

xAxis: {
  type: 'category',
  data: ['Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun']
},
yAxis: {
  type: 'value'
},
series: [{
  data: [820, 932, 901, 934, 1290, 1330, 1320],
  type: 'line'
}]

The above format can be in[ https://echarts.apache.org/examples/zh/index.html ]( https://echarts.apache.org/examples/zh/index.html )Inquiry

API interface is defined as follows:

![](images/4-7.png)

![](images/4-6.png)

(1) Define the VO class required for front-end display. The code is as follows:

package com.yikekong.vo;

import lombok.Data;

import java.io.Serializable;
import java.util.List;

/**
 * Line graph encapsulation class
 */
@Data
public class LineVO implements Serializable {

    private List<String>  xdata;//x-axis

    private List<Long> series;//data
}

(2) Add a method call getAlarmTrend in the ReportController to get the data, convert it and return it to the front end

/**
 * Get alarm trend
 * @return
 */
@GetMapping("/trend/{startTime}/{endTime}/{type}")
public LineVO getQuotaTrendCollect(@PathVariable String startTime,@PathVariable String endTime,@PathVariable Integer type){
    List<TrendPoint> trendPointList = reportService.getAlarmTrend(startTime, endTime, type);
    LineVO lineVO=new LineVO();
    lineVO.setXdata(Lists.newArrayList());
    lineVO.setSeries(Lists.newArrayList());
    trendPointList.forEach( t->{
        lineVO.getXdata().add( t.getTime() );
        lineVO.getSeries().add( t.getPointValue().longValue());
    });
    return lineVO;
}

Test:

http://127.0.0.1:9093/report/trend?start=2020-08-01&end=2020-08-31&type=3

2.3.4 time format processing

Requirements:

After the test just now, the date format we returned is 2020-09-01T00:00:00Z. But the format required by the front end is

If it is a minute, the minute value is displayed

If it is an hour, the hour value is displayed

If it is a day, it displays xx month xx day

(1) ReportController adds a new private method to format the date string

/**
 * Format date string
 * @param time
 * @param type
 * @return
 */
private String formatTime(String time,int type){
    LocalDateTime localTime = LocalDateTime.parse(time,DateTimeFormatter.ISO_OFFSET_DATE_TIME);
    if(type == 1){
       return  localTime.getMinute()+"";
    }else if(type == 2){
        return localTime.getHour()+"";
    }else if(type == 3){
        return localTime.getMonthValue()+"month"+localTime.getDayOfMonth()+"day";
    }
    return time;
}

(2) Modify getAlarmTrend method and call formatTime to format date

/**
 * Get alarm trend
 * @return
 */
@GetMapping("/trend/{startTime}/{endTime}/{type}")
public LineVO getQuotaTrendCollect(@PathVariable String startTime,@PathVariable String endTime,@PathVariable Integer type){

    List<TrendPoint> trendPointList = reportService.getAlarmTrend(startTime, endTime, type);

    LineVO lineVO=new LineVO();
    lineVO.setXdata(Lists.newArrayList());
    lineVO.setSeries(Lists.newArrayList());

    trendPointList.forEach( t->{
        lineVO.getXdata().add( formatTime(t.getTime(),type) );//Revise this sentence
        lineVO.getSeries().add( t.getPointValue().longValue());
    });

    return lineVO;
}

3. Alarm times top10 equipment index report

3.1 demand analysis

The system needs to display the top 10 equipment and indicators with the highest number of alarms in the current hour, the current day and the current 7 days, which are displayed in a histogram. The display effect is as follows:

![](images/3-8.png)

The vertical axis shows the equipment number + indicator name, and the horizontal axis shows the number of alarms, arranged from more to less.

3.2 implementation ideas

(1) Write query statements to realize the top 10 statistics of the number of exceptions. We will learn a new function top here.

(2) Define the specific pojo classes retrieved from the InfluxDB, including the attributes such as device id, indicator id, indicator name, and the number of times counted

(3) Define and obtain the top10 alarm device indicators within a certain time range in the AlarmService service interface and implement them in the implementation class

(4) invoke the method in Service in Controller to get data back to the front end.

3.3 code implementation

3.3.1 alarm times top10 query

(1) First, write a query statement to query the number of alarms in groups according to the device ID and indicator ID

select count(value) as heapValue 
  from quota 
  where alarm='1'  and time>='2020-09-01' and time<='2020-09-30' 
  group by deviceId,quotaId,quotaName

Note: in the standard SQL syntax, the grouping condition must appear after the select sentence, while the incluxql does not need to be written like that.

The operation effect of the above statement is as follows:

![](images/4-8.png)

(2) The above result is actually the intermediate result we want. We should also get the top 10 data based on this result. We need to use the top function

select top(heapValue,deviceId,quotaId,quotaName,10) as heapValue 
from  (
  select count(value) as heapValue 
  from quota 
  where alarm='1'  and time>='2020-09-01' and time<='2020-09-30' 
  group by deviceId,quotaId  ) 
order by desc

The first parameter of the top function is the field to be sorted, the last parameter is the number of results obtained, and the middle parameter is other data to be listed.

The running results of the above statement are as follows:

![](images/4-9.png)

3.3.2 report data query and encapsulation

API design is as follows:

![](images/4-10.png)

(1) Define the pojo class corresponding to top10

package com.yikekong.dto;
import lombok.Data;
import org.influxdb.annotation.Column;
import org.influxdb.annotation.Measurement;
import java.io.Serializable;
/**
 * Cumulative index
 */
@Data
@Measurement(name = "quota")
public class HeapPoint implements Serializable{
    @Column(name = "deviceId")
    private String deviceId;
    @Column(name = "heapValue")
    private Double heapValue;
    @Column(name = "quotaId")
    private String quotaId;
    @Column(name = "quotaName")
    private String quotaName;
}

(2) Define the method of obtaining data in the ReportService service interface

/**
 * Obtain the equipment index with the largest number of alarms within a certain time range
 * @return
 */
List<HeapPoint> getTop10Alarm(String startTime, String endTime);

Implement this method in the ReportServiceImpl implementation class:

@Override
public List<HeapPoint> getTop10Alarm(String startTime, String endTime) {
    StringBuilder sbSql =
            new StringBuilder("select top(heapValue,deviceId,quotaId,quotaName,10) as heapValue " +
                    " from(select count(value) as heapValue from quota where alarm='1' ");
    sbSql.append("and time>='");
    sbSql.append(startTime);
    sbSql.append("' and time<='");
    sbSql.append(endTime);
    sbSql.append("' group by deviceId,quotaId) order by desc");

    return influxDBRepository.query(sbSql.toString(),HeapPoint.class);
}

The specific implementation idea of sql statement here is: the total number of alarms grouped according to equipment and indicators within a certain time range is obtained in sql sub query, and then the top function is used to get the top ten of the result set after reverse order in external query, so as to obtain the data we want.

(3) Add a method to get the top ten data in the ReportController, and call the method of the service layer in this method to return to the front end:

/**
 * Obtain the equipment index with the highest number of alarms in the top 10 within a certain time range
 * @param startTime
 * @param endTime
 * @return
 */
@GetMapping("/top10Alarm/{startTime}/{endTime}")
public List<HeapPoint> getTop10Alarm(@PathVariable String startTime, @PathVariable String endTime){
    return reportService
                    .getTop10Alarm(startTime,endTime);
}

4. Custom indicator Kanban

4.1 demand analysis

System managers can add a user-defined Kanban to display data in the system. The user-defined Kanban is the indicator selected by the user and some devices that report the indicator according to the indicator, so as to facilitate tracking and observing the trend of relevant devices under the indicator. The displayed trend is also multi-dimensional, Display the current 60 minute indicators and relevant equipment trends in an hour according to the hour; Display the equipment trend of each hour of the day according to the index 24 hours a day; Display the 24-hour indicator equipment trend in the last 7 days according to 7 days.

The prototype diagram of adding Kanban is as follows:

![](images/4-2.png)

The prototype design of the added Kanban is shown on the home page as follows:

![](images/4-3.png)

The horizontal axis shows the time, and the data on the vertical axis is the index data reported by the equipment for the first time at each time point.

4.2 realization ideas

(1) When adding Kanban in the system, you can only select numerical indicators. Load all numerical indicators from mysql database in pages. Indicators are single choice

(2) After selecting indicators when adding Kanban, you can only select the equipment that has submitted the indicator under the previously selected indicators when selecting equipment. The equipment is multi-choice, and a maximum of 10 equipment can be selected

(3) When providing data to the front end, first query the indicator Id of the panel and all corresponding device numbers from mysql, and obtain the first indicator value of all device indicators in the corresponding time period from InfluxDB according to the time type passed in from the front end

(4) Combine all data into the data format desired by the front end and return

4.3 code implementation

4.3.1 get the list of numerical indicators

![](images/4-11.png)

(1) Load all numerical indicators from mysql database page by page, and define methods in the QuotaService interface

IPage<QuotaEntity> queryNumberQuota(Long page, Long pageSize);

Implement this method in the QuotaServiceImpl implementation class:

@Override
public IPage<QuotaEntity> queryNumberQuota(Long page, Long pageSize) {
    Page<QuotaEntity> pageResult = new Page<>(page,pageSize);
    LambdaQueryWrapper<QuotaEntity> wrapper = new LambdaQueryWrapper<>();
    wrapper
        .eq(QuotaEntity::getValueType,"Long")
        .or()
        .eq(QuotaEntity::getValueType,"Integer")
        .or()
        .eq(QuotaEntity::getValueType,"Double");
    return this.page(pageResult,wrapper);
}

(2) Add an interface to get the data in the QuotaController and call the above method to return the data to the front end

/**
     * Get numeric indicators by page
     * @param page
     * @param pageSize
     * @return
     */
@GetMapping("/numberQuota")
public Pager<QuotaEntity> queryNumberQuota(@RequestParam(value = "page",required = false,defaultValue = "1") Long page,
                                           @RequestParam(value = "pageSize",required = false,defaultValue = "10") Long pageSize){
    return new Pager<>(quotaService.queryNumberQuota(page,pageSize));
}

4.3.2 obtain equipment related to an index

(1) Query statement

Due to the large number of devices, we need to use paging query. The statement is as follows:

select distinct(deviceId) as deviceId 
from(select deviceId,value from quota where quotaId='1' group by deviceId,quotaId)  limit 10 OFFSET 0

Write a statement to obtain the number of devices for paging

select count(distinct(deviceId)) as deviceCount from(select deviceId,value from quota where quotaId='' group by deviceId,quotaId)

(2) In the ReportService interface, define the method to obtain the associated device through the indicator ID

/**
     * Obtain associated equipment through indicators
     * @param quotaId
     * @return
     */
Pager<String> getDeviceByQuota(Long page,Long pageSize,String quotaId);

Implement this method in the ReportServiceImpl implementation class:

@Override
public Pager<String> getDeviceByQuota(Long page, Long pageSize, String quotaId) {

    String fromQl=" from ( select deviceId,value from quota where quotaId='"+ quotaId+"' group by deviceId,quotaId  ) ";

    String listQl="select distinct(deviceId ) as deviceId "+fromQl+" limit "+pageSize  +" offset "+(page-1)*pageSize;

    String countQl=" select count( distinct(deviceId )) as count "+fromQl;

    List<QuotaInfo> quotaInfoList = influxRepository.query(listQl, QuotaInfo.class);

    //Device id list
    List<String> deviceIdList = quotaInfoList.stream().map(quotaInfo -> quotaInfo.getDeviceId()).collect(Collectors.toList());

    //Number of statistical records
    List<QuotaCount> quotaCountList = influxRepository.query(countQl, QuotaCount.class);

    if( quotaCountList==null || quotaCountList.size()==0 ){
        Pager<String> pager=new Pager<String>(0L,0L);
        pager.setItems(Lists.newArrayList());
        return pager;
    }

    Long count = quotaCountList.get(0).getCount();

    Pager<String> pager=new Pager<String>(count,pageSize);
    pager.setItems(deviceIdList);
    return pager;
}

(3) Add an interface to obtain the data in the ReportController and call the above method to return the data to the front end:

/**
 * Query equipment list through indicators
 * @param quotaId
 * @return
 */
@GetMapping("/devices")
public Pager<String> getDeviceByQuota(
  @RequestParam(value = "page",required = false,defaultValue = "1") Long page,
  @RequestParam(value = "pageSize",required = false,defaultValue = "10") Long pageSize,
  @RequestParam(value = "quotaId")String quotaId){
    return reportService.getDeviceByQuota(page,pageSize,quotaId);
}

4.3.3 query index value sequence by equipment index

(1) Write query statements

select first(value) as pointValue from quota 
where time>='2020-09-01' and time<='2020-09-30' 
and quotaId='1' 
and deviceId='100001' 
group by time(1d)

In order to view the running effect more conveniently, we need to execute precision rfc3339 to set the time display format

The results are as follows:

![](images/4-12.png)

(2) Write encapsulation class

package com.yikekong.dto;

import lombok.Data;
import org.influxdb.annotation.Column;
import org.influxdb.annotation.Measurement;

import java.io.Serializable;

/**
 * Encapsulates the data of line graph class results
 */
@Data
@Measurement(name = "quota")
public class TrendPoint2 implements Serializable {

    @Column(name = "time")
    private String time;//time


    @Column(name = "pointValue")
    private Double pointValue;//Point in time data
}

(3) Define the method to obtain the trend indicator point set under a certain indicator within a period of time in the ReportService interface

/**
 * Get indicator trends
 * @param start Start time yyyy MM DD HH: mm: SS
 * @param end End time yyyy MM DD HH: mm: SS
 * @param quotaId Indicator Id
 * @param type Time statistics type (within 1:60 minutes, 2:24 hours on the same day, 3:7 days)
 * @param deviceId Equipment code
 * @return
 */
List<TrendPoint2> getQuotaTrend(
                 String start, String end, String quotaId,String deviceId,int type);

Implement this method in the ReportServiceImpl implementation class

@Override
public List<TrendPoint2> getQuotaTrend(String startTime, String endTime, String quotaId, String deviceId, int type) {

    StringBuilder ql=new StringBuilder("select first(value) as pointValue from quota ");
    ql.append("where time>='"+ startTime+"' and time<='"+ endTime +"' "  );
    ql.append("and quotaId='"+quotaId +"' ");
    ql.append("and deviceId='"+ deviceId +"' ");

    if(type==1){ //1 hour
        ql.append("group by time(1m)");
    }
    if(type==2){ //1 day
        ql.append("group by time(1h)");
    }
    if(type==3){ //7 days
        ql.append("group by time(1d)");
    }
    List<TrendPoint2> trendPoint2List = influxRepository.query(ql.toString(), TrendPoint2.class);
    return trendPoint2List;
}

Unit test:

@Test
public void testQuotaTrend(){
    List<TrendPoint2> trendPoint2List = reportService.getQuotaTrend("2020-09-01 00:00:00", "2020-09-30 23:59:59"
            , "1", "100001", 3);
    for( TrendPoint2 trendPoint2:trendPoint2List ){
        try {
            System.out.println( JsonUtil.serialize(trendPoint2) );
        } catch (JsonProcessingException e) {
            e.printStackTrace();
        }
    }
}

The test results are as follows:

![](images/4-13.png)

4.3.4 vacancy data completion

Through the above unit test results, we found that many time nodes have no values. If it is null, how should we display it in the graph? Press 0? Obviously unreasonable. For example, for temperature, the meaning of temperature 0 is obviously different from that of no temperature data!

So let's process the vacancy data here. The processing rules are as follows:

If the value of a node is null, it is set to the previous non null value

If the first value of the sequence is null, all values from the first value to the first non null value are the first non null value.

The code is written as follows:

(1) ReportServiceImpl new private method

/**
 * Fill data
 * @param trendPoint2List
 * @return
 */
private List<TrendPoint2> replenish(List<TrendPoint2> trendPoint2List){

    Double  previousValue=null;// Previous value
    for(TrendPoint2 trendPoint2: trendPoint2List ){
        if(trendPoint2.getPointValue()!=null){
            previousValue=trendPoint2.getPointValue();
            break;
        }
    }
    if(previousValue==null){
        previousValue=0d;
    }

    //Data filling logic
    for( TrendPoint2 trendPoint2: trendPoint2List){
        if(trendPoint2.getPointValue()==null){
            trendPoint2.setPointValue(previousValue);
        }
        previousValue=trendPoint2.getPointValue();
    }
    return trendPoint2List;
}

(2) Modify the return value of getQuotaTrend method

return replenish(trendPoint2List);

4.3.5 report data preview

The format required by the front end is as follows:

{
  "xdata": [
    "8 January 1",
    "8 February 2",
    "8 March 3"
  ],
  "series": [
    {
      "name": "100001",
      "data": [
        70,
        90,
        100
      ]
    },
    {
      "name": "100002",
      "data": [
        50,
        60,
        80,
      ]
    }
  ],
  "name": "Panel name"
}

(1) Define vo the data sequence used to encapsulate an indicator

package com.yikekong.vo;
import lombok.Data;
import java.io.Serializable;
import java.util.List;
/**
 * Panel index data
 */
@Data
public class BoardQuotaData implements Serializable{
    /**
     * Name (equipment number)
     */
    private String name;
    /**
     * Index data
     */
    private List<Double> data;
}

(2) Define vo, which is used to encapsulate the data structure of the whole report

package com.yikekong.vo;
import lombok.Data;
import java.io.Serializable;
import java.util.List;
/**
 * Panel VO object
 */
@Data
public class BoardQuotaVO implements Serializable{
    
    /**
     * x Axis data
     */
    private List<String> xdata;
   
    /**
     * Y Axis data
     */
    private List<BoardQuotaData> series;
  
    /**
     * Panel name
     */
    private String name;
}

(3) Define the method of obtaining panel indicator data in the ReportService interface

/**
 * Indicator trend chart
 * @param quotaId
 * @param deviceIds
 * @param startTime
 * @param endTime
 * @param type
 * @return
 */
BoardQuotaVO getBoardData(String quotaId,List<String> deviceIds,String startTime,String endTime,Integer type  );