feat:空气质量报告

cjl

2023-08-03 03151f30b03113dcc72972972495c7735b5afb66

feat:空气质量报告

 screen-api/src/main/java/com/moral/api/controller/SysTestController.java

@@ -1,6 +1,10 @@
package com.moral.api.controller;

import cn.afterturn.easypoi.word.WordExportUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.moral.api.dto.SysTestDTO;
import com.moral.api.entity.SysTest;
import com.moral.api.service.SysTestService;
import com.moral.util.DateUtils;
@@ -17,6 +21,7 @@

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
@@ -40,23 +45,82 @@


    @GetMapping("resultWord")
    public void adresultWordd(HttpServletResponse response,String time) {
        List<SysTest> listAll = sysTestService.listAll(time,"2022-06-22",null);
    public void adresultWordd(HttpServletResponse response,String time,String endTime) {
       // for(int i = 495;i<1095;i++){
            List<SysTest> listAll = sysTestService.listAll(time,endTime,null);
            if(CollectionUtils.isEmpty(listAll)){
                return;
            }
          /*  Date endTime =listAll.get(0).getTime();
            String endTimeStr = DateUtils.dateToDateString(endTime,DateUtils.yyyyMMdd_EN);
            String  fileName = "空气质量日报.docx";
            Map<String,Object> map = resultMap(listAll);
            downloadWord(fileName, map, response,"空气质量日报"+endTimeStr+".docx");*/
          //  time = DateUtils.dateToDateString(DateUtils.addDays(endTime,1),DateUtils.yyyy_MM_dd_EN);
     //   }
    }

    @GetMapping("excelM")
    public void excelM(HttpServletResponse response,String time,String endTime) {
        // for(int i = 495;i<1095;i++){
        String ks = "2020-06-01";
        Date ksDate = DateUtils.convertDate(ks);
        List<SysTest> result = new ArrayList<>();
        for(int i = 0;i<37;i++){
            Date jsDate =  DateUtils.getLastDayOfCurrMonth(ksDate);
            List<SysTest> listAll = sysTestService.listAllAvg(DateUtils.dateToDateString(ksDate,DateUtils.yyyy_MM_dd_EN),DateUtils.dateToDateString(jsDate,DateUtils.yyyy_MM_dd_EN),null);
            System.out.println("开始："+DateUtils.dateToDateString(ksDate,DateUtils.yyyy_MM_dd_EN));
            System.out.println("结束："+DateUtils.dateToDateString(jsDate,DateUtils.yyyy_MM_dd_EN));
            ksDate =  DateUtils.addMonths(ksDate,1);
            result.addAll(listAll);
        }

        if(CollectionUtils.isEmpty(result)){
            return;
        }
        String path = "D:\\空气质量月数据.xls";
        WriteSheet build = EasyExcel.writerSheet("因子信息").head(SysTestDTO.class).build();
        // 创建 easyexcel 的写出类构造器 参数 告诉构造器，我的excel将来要写到哪里，以及execl中数据是基于哪个java对象模板创建的
        ExcelWriter excelWriter = EasyExcel.write(path, SysTestDTO.class).build();
        excelWriter.write(result, build);
        excelWriter.finish();
    }

    @GetMapping("excelDay")
    public void excelDay(HttpServletResponse response,String time,String endTime) {

        List<SysTest> listAll = sysTestService.listAll(time,endTime,null);

        if(CollectionUtils.isEmpty(listAll)){
            return;
        }
        Date endTime =listAll.get(0).getTime();
        String endTimeStr = DateUtils.dateToDateString(endTime,DateUtils.yyyyMMdd_EN);
        String  fileName = "空气质量日报.docx";
        Map<String,Object> map = resultMap(listAll);
        String path = "D:\\空气质量日数据.xls";
        WriteSheet build = EasyExcel.writerSheet("因子信息").head(SysTestDTO.class).build();
        // 创建 easyexcel 的写出类构造器 参数 告诉构造器，我的excel将来要写到哪里，以及execl中数据是基于哪个java对象模板创建的
        ExcelWriter excelWriter = EasyExcel.write(path, SysTestDTO.class).build();
        excelWriter.write(listAll, build);
        excelWriter.finish();
    }

        //实体转map
       // Map result = JSON.parseObject(JSON.toJSONString(entity), Map.class);
        //        Map<String, Object> result = EasyPoiUtil.entityToMap(entity);
        downloadWord(fileName, map, response,"空气质量日报"+endTimeStr+".docx");



    @GetMapping("resultWordWeek")
    public void resultWordWeek(HttpServletResponse response) {
        String ks = "2020-06-22";
        Date ksDate = DateUtils.convertDate(ks);
        for(int i = 0;i<157;i++){
            Date jsDate =  DateUtils.addDays(ksDate,6);
            String time = DateUtils.dateToDateString(ksDate,DateUtils.yyyy_MM_dd_EN);
            String endTime = DateUtils.dateToDateString(jsDate,DateUtils.yyyy_MM_dd_EN);
            List<SysTest> listAll = sysTestService.listAllAvg(time,endTime,null);
            if(CollectionUtils.isEmpty(listAll)){
                return;
            }
            String  fileName = "空气质量周报.docx";
            String time1 = DateUtils.dateToDateString(ksDate,"yyyy.MM.dd");
            String endTime1= DateUtils.dateToDateString(jsDate,"yyyy.MM.dd");
            Map<String,Object> map = resultWeekMap(listAll,i+1,time1+"-"+endTime1);
            downloadWord(fileName, map, response,"空气质量周报"+time+".docx");
            ksDate =  DateUtils.addDays(ksDate,7);
        }
    }


@@ -64,15 +128,14 @@
        String path = getPath(fileName);//我这放 resources包下
        try {
            //获取模板文档

            OutputStream out = new FileOutputStream("E:\\home\\week\\" + newFileName);
            File rootFile = new File(ResourceUtils.getURL("classpath:").getPath());
            File file= new File(rootFile, path);

            XWPFDocument word = WordExportUtil.exportWord07(file.getPath(), params);
            response.setCharacterEncoding("utf-8");
            response.setContentType("multipart/form-data");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(newFileName, "utf-8"));
            OutputStream out = response.getOutputStream();
            //OutputStream out = response.getOutputStream();
            word.write(out);
            out.flush();
            out.close();
@@ -146,11 +209,39 @@
            resultMap.put(name+"S", StringUtils.isNotEmpty(s.getSu())?s.getSu():"--");
        }

        String bodyName = stringButterStr(listAll);
         //日报
         String bodyName = stringButterStr(listAll);

        resultMap.put("主要内容",bodyName);
        return resultMap;
    }

    private Map<String,Object> resultWeekMap(List<SysTest> listAll,int week,String weekTime){
        Map<String,Object> resultMap = new HashMap<>();
        resultMap.put("周",week);
        resultMap.put("日期",weekTime);
        String name = "沙";
        for(SysTest s : listAll){
            if(s.getName().contains("友")){
                name = "友";
            }else if(s.getName().contains("中")){
                name = "中";
            }else {
                name = "沙";
            }
            resultMap.put(name+"AQI", StringUtils.isNotEmpty(s.getAqi())?s.getAqi():"--");
            resultMap.put(name+"PM10", StringUtils.isNotEmpty(s.getPm10())?s.getPm10():"--");
            resultMap.put(name+"PM2", StringUtils.isNotEmpty(s.getPm25())?s.getPm25():"--");
            resultMap.put(name+"SO2", StringUtils.isNotEmpty(s.getSo2())?s.getSo2():"--");
            resultMap.put(name+"NO2", StringUtils.isNotEmpty(s.getNo2())?s.getNo2():"--");
            resultMap.put(name+"CO", StringUtils.isNotEmpty(s.getCo())?s.getCo():"--");
            resultMap.put(name+"O3", StringUtils.isNotEmpty(s.getO3())?s.getO3():"--");
            resultMap.put(name+"TV", StringUtils.isNotEmpty(s.getVoc())?s.getVoc():"--");
            resultMap.put(name+"S", StringUtils.isNotEmpty(s.getSu())?s.getSu():"--");
        }
        String bodyName = stringButterStrWeek(listAll);
        resultMap.put("主要内容",bodyName);
        return resultMap;
    }
    private String stringButterStr(List<SysTest> listAll){

        // 0 一样1 两高 2两低，3高，低
@@ -279,7 +370,41 @@

    }


    private String stringButterStrWeek(List<SysTest> listAll){
        StringBuffer stringBuffer = new StringBuffer();
        SysTest result0 = listAll.get(0);
        SysTest result1 = listAll.get(1);
        SysTest result2 = listAll.get(2);
        Map<String,String> pm10 = nameCode(result0.getName(),result1.getName(),result2.getName(),result0.getPm10(),result1.getPm10(),result2.getPm10());
        Map<String,String> pm25 = nameCode(result0.getName(),result1.getName(),result2.getName(),result0.getPm25(),result1.getPm25(),result2.getPm25());
        Map<String,String> so = nameCode(result0.getName(),result1.getName(),result2.getName(),result0.getSo2(),result1.getSo2(),result2.getSo2());
        Map<String,String> no = nameCode(result0.getName(),result1.getName(),result2.getName(),result0.getNo2(),result1.getNo2(),result2.getNo2());
        Map<String,String> co = nameCode(result0.getName(),result1.getName(),result2.getName(),result0.getCo(),result1.getCo(),result2.getCo());
        Map<String,String> o3 = nameCode(result0.getName(),result1.getName(),result2.getName(),result0.getO3(),result1.getO3(),result2.getO3());
        if(pm10.isEmpty()&& pm25.isEmpty()&&so.isEmpty()&& no.isEmpty()&& co.isEmpty()&& o3.isEmpty()){
            return stringBuffer.toString();
        }
        stringBuffer.append("其中：");
        if(!pm10.isEmpty()){
            stringBuffer.append("PM10较高的是："+pm10.get("high")+"；较低的是："+pm10.get("low")+"。");
        }
        if(!pm25.isEmpty()){
            stringBuffer.append("PM2.5较高的是："+pm25.get("high")+"；较低的是："+pm25.get("low")+"。");
        }
        if(!so.isEmpty()){
            stringBuffer.append("SO2较高的是："+so.get("high")+"；较低的是："+so.get("low")+"。");
        }
        if(!no.isEmpty()){
            stringBuffer.append("NO2较高的是："+no.get("high")+"；较低的是："+no.get("low")+"。");
        }
        if(!co.isEmpty()){
            stringBuffer.append("CO较高的是："+co.get("high")+"；较低的是："+co.get("low")+"。");
        }
        if(!o3.isEmpty()){
            stringBuffer.append("O3较高的是："+o3.get("high")+"；较低的是："+o3.get("low")+"。");
        }
        return stringBuffer.toString();
    }
    private Map<String,String> nameCode(String name1,String name2,String name3,String nums1,String nums2,String nums3){
        double num1 = StringUtils.isNotEmpty(nums1)?Double.parseDouble(nums1):0;
        double num2 = StringUtils.isNotEmpty(nums2)?Double.parseDouble(nums2):0;
@@ -339,7 +464,28 @@

    }

    public static void main(String[] args) {
        /*String ks = "2020-06-01";
        Date ksDate = DateUtils.convertDate(ks);
        for(int i = 0;i<37;i++){
            Date jsDate =  DateUtils.getLastDayOfCurrMonth(ksDate);
            System.out.println("开始："+DateUtils.dateToDateString(ksDate,DateUtils.yyyy_MM_dd_EN));
            System.out.println("结束："+DateUtils.dateToDateString(jsDate,DateUtils.yyyy_MM_dd_EN));
            ksDate =  DateUtils.addMonths(ksDate,1);
        }*/


        String ks = "2020-06-22";
        Date ksDate = DateUtils.convertDate(ks);
        for(int i = 0;i<157;i++){
            Date jsDate =  DateUtils.addDays(ksDate,6);
            System.out.println("开始："+DateUtils.dateToDateString(ksDate,DateUtils.yyyy_MM_dd_EN));
            System.out.println("结束："+DateUtils.dateToDateString(jsDate,DateUtils.yyyy_MM_dd_EN));
            ksDate =  DateUtils.addDays(ksDate,7);
        }


    }


}

 screen-api/src/main/java/com/moral/api/dto/SysTestDTO.java

New file
@@ -0,0 +1,47 @@
package com.moral.api.dto;

import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableField;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.util.Date;

/**
 * @ClassName SysTestDTO
 * @Description TODO
 * @Author @lizijie
 * @Date 2023-08-02 17:22
 * @Version 1.0
 */
@Data
@EqualsAndHashCode(callSuper = false)
public class SysTestDTO {
    private static final long serialVersionUID = 1L;
    /*@ExcelProperty("月份")
    private String mon;*/
    @ExcelProperty("名称")
    private String name;
/*
    private String mac;*//**/
   @ExcelProperty("时间")
    private Date time;
    @ExcelProperty("PM10")
    private String pm10;
    @ExcelProperty("PM2.5")
    private String pm25;
    @ExcelProperty("SO2")
    private String so2;
    @ExcelProperty("NO2")
    private String no2;
    @ExcelProperty("CO")
    private String co;
    @ExcelProperty("O3")
    private String o3;
    @ExcelProperty("TVOC")
    private String voc;
    @ExcelProperty("AQI")
    private String aqi;
    @ExcelProperty("首污")
    private String su;
}

 screen-api/src/main/java/com/moral/api/entity/SysTest.java

@@ -49,4 +49,7 @@
    @TableField(exist = false)
    private String su;

    @TableField(exist = false)
    private String mon;

}

 screen-api/src/main/java/com/moral/api/service/SysTestService.java

@@ -16,4 +16,7 @@
public interface SysTestService extends IService<SysTest> {

    List<SysTest> listAll(String startTime,String endTime ,String mac);


    List<SysTest> listAllAvg(String startTime,String endTime ,String mac);
}

 screen-api/src/main/java/com/moral/api/service/impl/SysTestServiceImpl.java

@@ -8,11 +8,12 @@
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.moral.api.utils.AqiUtils;
import com.moral.util.DateUtils;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.stereotype.Service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

/**
 * <p>
@@ -28,8 +29,11 @@
    @Override
    public List<SysTest> listAll(String startTime,String endTime, String mac) {
        QueryWrapper<SysTest> queryWrapper = new QueryWrapper<>();
       // queryWrapper.between(!StringUtils.isEmpty(startTime)&&!StringUtils.isEmpty(endTime),"time",startTime,endTime);
        queryWrapper.eq(!StringUtils.isEmpty(startTime),"time", DateUtils.convertDate(startTime));
        if(!StringUtils.isEmpty(startTime)&&!StringUtils.isEmpty(endTime)){
            queryWrapper.between(!StringUtils.isEmpty(startTime)&&!StringUtils.isEmpty(endTime),"time",DateUtils.convertDate(startTime),DateUtils.convertDate(endTime));
        }else if(!StringUtils.isEmpty(startTime)){
            queryWrapper.eq(!StringUtils.isEmpty(startTime),"time", DateUtils.convertDate(startTime));
        }
        queryWrapper.eq(!StringUtils.isEmpty(mac),"mac",mac);
        queryWrapper.orderByAsc("time","mac");
        List<SysTest> listAll = this.baseMapper.selectList(queryWrapper);
@@ -47,4 +51,122 @@
        }
        return listAll;
    }

    @Override
    public List<SysTest> listAllAvg(String startTime, String endTime, String mac) {
        Date time = DateUtils.convertDate(startTime);
        QueryWrapper<SysTest> queryWrapper = new QueryWrapper<>();
        if(!StringUtils.isEmpty(startTime)&&!StringUtils.isEmpty(endTime)){
            queryWrapper.between(!StringUtils.isEmpty(startTime)&&!StringUtils.isEmpty(endTime),"time",DateUtils.convertDate(startTime),DateUtils.convertDate(endTime));
        }else if(!StringUtils.isEmpty(startTime)){
            queryWrapper.eq(!StringUtils.isEmpty(startTime),"time", DateUtils.convertDate(startTime));
        }
        queryWrapper.eq(!StringUtils.isEmpty(mac),"mac",mac);
        queryWrapper.orderByAsc("time","mac");
        List<SysTest> listAll = this.baseMapper.selectList(queryWrapper);
        return resultList(listAll,time);
    }

    private List<SysTest> resultList(List<SysTest> listAll,Date time){
        List<SysTest> list = new ArrayList<>();
        if(CollectionUtils.isEmpty(listAll)) {
            return list;
        }
        String mon = DateUtils.dateToDateString(time,DateUtils.yyyy_MM_EN);
        List<String> nameList = new ArrayList<>();
        Map<String, ArrayList<Double>> map = new HashMap<>();
        for(SysTest s : listAll) {
            if(nameList.size()<4 && !nameList.contains(s.getName())){
                nameList.add(s.getName());
                SysTest sysTest = new SysTest();
                sysTest.setName(s.getName());
                sysTest.setMon(mon);
                list.add(sysTest);
            }
            String pm10 = s.getName()+"_pm10";
            ArrayList<Double> pm10D = Objects.nonNull(map.get(pm10))?map.get(pm10):new ArrayList<>();
            pm10D.add(Double.parseDouble(s.getPm10()));
            map.put(pm10,pm10D);

            String pm25 = s.getName()+"_pm25";
            ArrayList<Double> pm25D = Objects.nonNull(map.get(pm25))?map.get(pm25):new ArrayList<>();
            pm25D.add(Double.parseDouble(s.getPm25()));
            map.put(pm25,pm25D);

            String o2 = s.getName()+"_o2";
            ArrayList<Double> o2D = Objects.nonNull(map.get(o2))?map.get(o2):new ArrayList<>();
            o2D.add(Double.parseDouble(s.getSo2()));
            map.put(o2,o2D);

            String no = s.getName()+"_no";
            ArrayList<Double> noD = Objects.nonNull(map.get(no))?map.get(no):new ArrayList<>();
            noD.add(Double.parseDouble(s.getNo2()));
            map.put(no,noD);

            String co = s.getName()+"_co";
            ArrayList<Double> coD = Objects.nonNull(map.get(co))?map.get(co):new ArrayList<>();
            coD.add(Double.parseDouble(s.getCo()));
            map.put(co,coD);

            String o3 = s.getName()+"_o3";
            ArrayList<Double> o3D = Objects.nonNull(map.get(o3))?map.get(o3):new ArrayList<>();
            o3D.add(Double.parseDouble(s.getO3()));
            map.put(o3,o3D);

            String tv = s.getName()+"_tv";
            ArrayList<Double> tvD = Objects.nonNull(map.get(tv))?map.get(tv):new ArrayList<>();
            tvD.add(Double.parseDouble(s.getVoc()));
            map.put(tv,tvD);
        }
        for(SysTest s : list){
            String name = s.getName();
            if(map.containsKey(name+"_pm10")){
                Double ListAvg = map.get(name+"_pm10").stream().collect(Collectors.averagingDouble(Double::doubleValue));
                double rsAvg = new BigDecimal(ListAvg).setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue();
                s.setPm10(String.valueOf(rsAvg));
            }
            if(map.containsKey(name+"_pm25")){
                Double ListAvg = map.get(name+"_pm25").stream().collect(Collectors.averagingDouble(Double::doubleValue));
                double rsAvg = new BigDecimal(ListAvg).setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue();
                s.setPm25(String.valueOf(rsAvg));
            }
            if(map.containsKey(name+"_o2")){
                Double ListAvg = map.get(name+"_o2").stream().collect(Collectors.averagingDouble(Double::doubleValue));
                double rsAvg = new BigDecimal(ListAvg).setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue();
                s.setSo2(String.valueOf(rsAvg));
            }
            if(map.containsKey(name+"_no")){
                Double ListAvg = map.get(name+"_no").stream().collect(Collectors.averagingDouble(Double::doubleValue));
                double rsAvg = new BigDecimal(ListAvg).setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue();
                s.setNo2(String.valueOf(rsAvg));
            }
            if(map.containsKey(name+"_tv")){
                Double ListAvg = map.get(name+"_tv").stream().collect(Collectors.averagingDouble(Double::doubleValue));
                double rsAvg = new BigDecimal(ListAvg).setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue();
                s.setVoc(String.valueOf(rsAvg));
            }
            if(map.containsKey(name+"_co")){
                double rsAvg = percentile(map.get(name+"_co"),95d);
                s.setCo(String.valueOf(rsAvg));
            }

            if(map.containsKey(name+"_o3")){
                double rsAvg = percentile(map.get(name+"_o3"),90d);
                s.setO3(String.valueOf(rsAvg));
            }
        }
        return list;
    }

    private Double percentile(List<Double> date,double num){
        Collections.sort(date);
        double position = (num / 100) * (date.size() - 1);
        int index = (int) position;
        // 获取小数部分的位置索引
        double fraction = position - index;

        // 获取百分位值
        double percentileValue = date.get(index) + fraction * (date.get(index + 1) - date.get(index));
        return new BigDecimal(percentileValue).setScale(3, BigDecimal.ROUND_HALF_UP).doubleValue();
    }
}

 screen-api/src/main/resources/word/??????.docx

Binary files differ

 screen-common/src/main/java/com/moral/util/DateUtils.java

@@ -1064,6 +1064,14 @@
        return getDate(dateToDateString(cal.getTime(), yyyy_MM_dd_EN));
    }

    public static Date getLastDayOfCurrMonth(Date time) {
        Calendar cal = Calendar.getInstance();
        cal.setTime(time);
        cal.add(Calendar.MONTH, 1);
        cal.set(Calendar.DAY_OF_MONTH, 0);
        return getDate(dateToDateString(cal.getTime(), yyyy_MM_dd_EN));
    }

    /**
     * 根据日期追加的天数，得到一个新日期
     *