牡丹客
0%

每日数据统计

发表于 更新于 分类于

1.依赖

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.25</version>
</dependency>

<!--excel工具 -->
<dependency>
    <groupId>cn.idev.excel</groupId>
    <artifactId>fastexcel</artifactId>
    <version>1.0.0</version>
</dependency>


<dependency>
   <groupId>org.projectlombok</groupId>
   <artifactId>lombok</artifactId>
   <version>1.18.24</version>
</dependency>

2.实体类

以 @ExcelProperty({“每日质量报警统计信息”,”站别”})为例

“每日质量报警统计信息”代表标题;”站别”代表表头

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
import cn.idev.excel.annotation.ExcelProperty;
import cn.idev.excel.annotation.write.style.ColumnWidth;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;

@Data
@ColumnWidth(26) // 默认列宽
@EqualsAndHashCode
@AllArgsConstructor
@NoArgsConstructor
public class QualityAlarmExcelDTO {
    @ExcelProperty({"每日质量报警统计信息","站别"})
    private String  deviceName ;
    @ExcelProperty({"每日质量报警统计信息","抽样总数"})
    private String  all ;
    @ExcelProperty({"每日质量报警统计信息","超时未测量"})
    private String  timeOut ;
    @ExcelProperty({"每日质量报警统计信息","占比"})
    private String  timeOutRatio ;

    @ExcelProperty({"每日质量报警统计信息","数据超差"})
    private String dataOut;
    @ExcelProperty({"每日质量报警统计信息","占比"})
    private String dataOutRatio;
    @ExcelProperty({"每日质量报警统计信息","总异常比例"})
    private String allRatio;

}

3.方法

此处使用的是若以框架,定时任务为每日凌晨发送(0 0 0 * * ?)

alarmInfoService获取前一天的数据信息

deviceWorkStationService查询设备

两者之间通过id关联

qualitySummaryExcelDay()方法中

qualitySummaryExcelDay()方法为固定格式

其中 String fileName = “C:/export/工作站检测质量报警_”+ DateUtil.formatDate(DateUtil.yesterday()) + “.xlsx”;是文件路径以及文件名设置

FastExcel.write(fileName, QualityAlarmExcelDTO.class)
.registerWriteHandler(horizontalCellStyleStrategy)
.sheet(“质量报警”).doWrite(qualityInfo);

文件名,引用实体类,表格样式,工作表(见下图),具体的数据

getQualityInfo()方法中

用hash存入两种报警信息的id,以及报警次数

检索前一天的报警信息根据具体的sql来调整

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
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
package com.caya.quartz.task;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.idev.excel.FastExcel;
import cn.idev.excel.write.metadata.style.WriteCellStyle;
import cn.idev.excel.write.style.HorizontalCellStyleStrategy;
import com.caya.manager.domain.DeviceWorkStation;
import com.caya.manager.domain.VO.AlarmInfoVO;
import com.caya.manager.domain.dto.QualityAlarmExcelDTO;
import com.caya.manager.service.IAlarmInfoService;
import com.caya.manager.service.IDeviceWorkStationService;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.io.File;
import java.util.*;

@Component("qualityAlarmExcelTask")
public class QualityAlarmExcelTask {


    @Autowired
    private IAlarmInfoService alarmInfoService;


    @Autowired
    private IDeviceWorkStationService deviceWorkStationService;
    private static final int ALARM_TYPE_TIMEOUT = 147;
    private static final int ALARM_TYPE_DATA_OUT = 148;


    public void qualitySummaryExcelDay(){

        List<QualityAlarmExcelDTO> qualityInfo = getQualityInfo();


        String fileName =  "C:/export/工作站检测质量报警_"+ DateUtil.formatDate(DateUtil.yesterday()) + ".xlsx";
        File file = new File(fileName);
        if (!file.getParentFile().exists()){
            file.getParentFile().mkdirs();
        }
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置头居中
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置内容
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容策略
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        FastExcel.write(fileName, QualityAlarmExcelDTO.class)
                .registerWriteHandler(horizontalCellStyleStrategy)
                .sheet("质量报警").doWrite(qualityInfo);

    }

    public List<QualityAlarmExcelDTO> getQualityInfo(){
        //查询所有类型的工作站
        List<DeviceWorkStation> deviceWorkStations = deviceWorkStationService.selectDeviceWorkStationList(new DeviceWorkStation());


        //超时未测量统计
        HashMap<Long,Float> timeOutNum=new HashMap<>();
        for (DeviceWorkStation deviceWorkStation:deviceWorkStations ) {
            timeOutNum.put(deviceWorkStation.getId(), (float) 0);
        }
        //数据超差统计
        HashMap<Long,Float> dataOutNum=new HashMap<>();
        for (DeviceWorkStation deviceWorkStation:deviceWorkStations ) {
            dataOutNum.put(deviceWorkStation.getId(), (float) 0);
        }


        //检索前一天的报警信息
        DateTime yesterday = DateUtil.yesterday();
        String beginOfDay = DateUtil.beginOfDay(yesterday).toString("yyyy-MM-dd HH:mm:ss");
        String endOfDay = DateUtil.endOfDay(yesterday).toString("yyyy-MM-dd HH:mm:ss");
        AlarmInfoVO alarmInfoVO = new AlarmInfoVO();
        Map<String,Object> params=new HashMap<>();
        params.put("beginTime",beginOfDay);
        params.put("endTime",endOfDay);
        alarmInfoVO.setParams(params);
        List<AlarmInfoVO> alarmInfoVOS = alarmInfoService.selectAlarmInfoList(alarmInfoVO);

        //查询报警类型
//        Object redisData = redisCache.getCacheObject("sys_dict:alarm_type_info");
//        List<SysDictData> cacheList = JSON.parseArray(redisData.toString(), SysDictData.class);
//        for (SysDictData sysDictData:cacheList) {
//            //查询编号
//            sysDictData.getDictCode();
                //此处编号147:超时未测量; 148:数据超差
//        }

        String deviceName="";
        String all="";
        String timeOut="";
        String timeOutRatio="";
        String dataOut="";
        String dataOutRatio="";
        String allRatio="";

        //超时未测量总数
        float timeOutAll=0;
        //数据采集异常报警数
        float dataOutAll=0;

        List<QualityAlarmExcelDTO> qualityAlarmExcel=new ArrayList<>();

        //遍历报警
        for (AlarmInfoVO alarm:alarmInfoVOS) {
            //查询报警类型为147的报警
            if (alarm.getTypeSysDictDataId()==ALARM_TYPE_TIMEOUT){
                //匹配报警的id
                for (Long deviceId :timeOutNum.keySet()) {
                    //id相同,报警类型相同,对应的报警数量+1
                    if (Objects.equals(deviceId, alarm.getDeviceWorkStationId())){
                        float timeOutAlarm = timeOutNum.get(deviceId);
                        timeOutNum.put(deviceId,timeOutAlarm+1);
                    }
                }
                //超时未测量+1
                timeOutAll++;
            }
               //查询报警类型为148的报警
            if (alarm.getTypeSysDictDataId()==ALARM_TYPE_DATA_OUT){
                //匹配报警的id
                for (Long deviceId :dataOutNum.keySet()) {
                    //id相同,报警类型相同,对应的报警数量+1
                    if (Objects.equals(deviceId, alarm.getDeviceWorkStationId())){
                        float dataOutAlarm = dataOutNum.get(deviceId);
                        dataOutNum.put(deviceId,dataOutAlarm+1);
                    }
                }
                //超时未测量+1
                dataOutAll++;
            }
        }

        for (DeviceWorkStation deviceWorkStation: deviceWorkStations) {
            deviceName=deviceWorkStation.getName();
            Float aFloat = timeOutNum.get(deviceWorkStation.getId());
            Float bFloat = dataOutNum.get(deviceWorkStation.getId());
            all= String.valueOf(aFloat.intValue() + bFloat.intValue());
            timeOut= String.valueOf(aFloat.intValue());
            timeOutRatio = timeOutAll == 0 ? "0%" : (aFloat / timeOutAll * 100) + "%";
            dataOut=String.valueOf(bFloat.intValue());
            dataOutRatio = dataOutAll == 0 ? "0%" : (bFloat / dataOutAll * 100) + "%";
            allRatio = (timeOutAll + dataOutAll) == 0 ? "0%" : ((aFloat + bFloat) / (timeOutAll + dataOutAll) * 100) + "%";
            QualityAlarmExcelDTO qualityAlarmExcelDTO=new QualityAlarmExcelDTO(deviceName,all,timeOut,timeOutRatio,dataOut,dataOutRatio,allRatio);
            qualityAlarmExcel.add(qualityAlarmExcelDTO);
        }

        return qualityAlarmExcel;
    }
}

4.效果图