fix：疾控中心设备分钟数据导出

cjl

2024-01-24 3dbc786738cdf005fac8ae5c27023e39a0554a68

fix：疾控中心设备分钟数据导出

 screen-job/pom.xml

@@ -34,6 +34,28 @@
            <artifactId>commons-lang3</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.1</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-mail</artifactId>
        </dependency>

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-email</artifactId>
            <version>1.5</version>
        </dependency>

        <dependency>
            <groupId>javax.mail</groupId>
            <artifactId>mail</artifactId>
            <version>1.4.7</version>
        </dependency>

        <dependency>
            <groupId>org.apache.httpcomponents</groupId>

 screen-job/src/main/java/com/moral/api/config/emile/EmailToLongConfig.java

New file
@@ -0,0 +1,21 @@
package com.moral.api.config.emile;

import org.springframework.context.annotation.Configuration;

import javax.annotation.PostConstruct;

/**
 * @ClassName EmailToLongConfig
 * @Description TODO
 * @Author @cjl
 * @Date 2024-01-24 14:58
 * @Version 1.0
 */
@Configuration
public class EmailToLongConfig {
    @PostConstruct
    private void init(){
        // 解决邮件附件名称太长会自动截取，导致附件变成.bin格式问题
        System.setProperty("mail.mime.splitlongparameters","false");
    }
}

 screen-job/src/main/java/com/moral/api/controller/PubController.java

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

import com.moral.api.service.*;
import com.moral.api.util.DeviceExcelDTO;
import com.moral.api.util.EmailSpringUtil;
import com.moral.api.util.ExcelUtil;
import com.moral.api.util.HttpUtils;
import com.moral.constant.ResultMessage;
import com.moral.util.DateUtils;
@@ -10,13 +13,15 @@
import org.apache.http.HttpResponse;
import org.apache.http.util.EntityUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import javax.mail.MessagingException;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.*;

/**
 * @ClassName UserController
@@ -46,6 +51,10 @@
    private HistoryHourlyService historyHourlyService;
    @Autowired
    private HistoryMonthlyService historyMonthlyService;
    @Autowired
    private DeviceService deviceService;
    @Autowired
    private EmailSpringUtil emailSpringUtil;

    @GetMapping("insertHistoryDaily")
    @ApiOperation(value = "天数据补录", notes = "天数据补录")
@@ -159,5 +168,32 @@
        }
    }


    @GetMapping("excelDevice")
    @ApiOperation(value = "excle", notes = "excle")
    public void  excelDevice() throws IOException {
        String startTime = DateUtils.dateToDateString(DateUtils.addDays(new Date(),-1),DateUtils.yyyy_MM_dd_EN);
        List<DeviceExcelDTO> list = deviceService.ListDeviceExcel(startTime,null,72);
        String path = Objects.requireNonNull(this.getClass().getClassLoader().getResource("")).getPath();
        String fileName = String.format("%s疾控中心数据-%s.xlsx",path,startTime);
        ByteArrayOutputStream out = null;
        try {
            // 生成excel文件
            out = ExcelUtil.generateExcel(list, DeviceExcelDTO.class);
            // 发送邮件
            String content = startTime+"疾控中心设备统计数据";
            String toMail = "909710561@qq.com";
            emailSpringUtil.sendEmail("设备分钟数据",content,false,"909710561@qq.com",
                    toMail,"null",null, fileName, new ByteArrayResource(out.toByteArray()));
        } catch (IOException e) {
            log.error(String.format("生成excel失败，原因：%s",e));
            e.printStackTrace();
        } catch (MessagingException e) {
            log.error(String.format("邮件发送失败，原因：%s",e));
            e.printStackTrace();
        }finally {
            if(out != null){
                out.close();
            }
        }
    }
}

 screen-job/src/main/java/com/moral/api/mapper/DeviceMapper.java

@@ -2,6 +2,10 @@

import com.moral.api.entity.Device;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.moral.api.util.DeviceExcelDTO;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * <p>
@@ -13,4 +17,5 @@
 */
public interface DeviceMapper extends BaseMapper<Device> {

    List<DeviceExcelDTO> ListDeviceExcel(@Param("startTime") String startTime,@Param("endTime") String endTime,@Param("id") int id);
}

 screen-job/src/main/java/com/moral/api/service/DeviceService.java

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

import com.moral.api.entity.Device;
import com.baomidou.mybatisplus.extension.service.IService;
import com.moral.api.util.DeviceExcelDTO;

import java.util.List;

@@ -30,4 +31,7 @@
     **/
    List<Device> getDateByOrgId(int orgId);


    List<DeviceExcelDTO> ListDeviceExcel(String startTime,String endTime,int id);

}

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

@@ -6,6 +6,7 @@
import com.moral.api.mapper.DeviceMapper;
import com.moral.api.service.DeviceService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.moral.api.util.DeviceExcelDTO;
import com.moral.constant.Constants;
import com.moral.constant.RedisConstants;
import com.moral.util.DateUtils;
@@ -74,4 +75,9 @@
        devices = deviceMapper.selectList(wrapper_device);
        return devices;
    }

    @Override
    public List<DeviceExcelDTO> ListDeviceExcel(String startTime, String endTime, int id) {
        return this.baseMapper.ListDeviceExcel(startTime,endTime,id);
    }
}

 screen-job/src/main/java/com/moral/api/task/EmilTask.java

New file
@@ -0,0 +1,68 @@
package com.moral.api.task;

import com.moral.api.service.DeviceService;
import com.moral.api.util.DeviceExcelDTO;
import com.moral.api.util.EmailSpringUtil;
import com.moral.api.util.ExcelUtil;
import com.moral.util.DateUtils;
import com.xxl.job.core.biz.model.ReturnT;
import com.xxl.job.core.context.XxlJobHelper;
import com.xxl.job.core.handler.annotation.XxlJob;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ByteArrayResource;
import org.springframework.stereotype.Component;

import javax.mail.MessagingException;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.List;
import java.util.Objects;

/**
 * @ClassName EmaleTask
 * @Description TODO
 * @Author @cjl
 * @Date 2024-01-24 15:33
 * @Version 1.0
 */
@Component
@Slf4j
public class EmilTask {
    @Autowired
    private DeviceService deviceService;
    @Autowired
    private EmailSpringUtil emailSpringUtil;
    @XxlJob("emilTask")
    public ReturnT emilTask()throws IOException {
        String startTime = DateUtils.dateToDateString(DateUtils.addDays(new Date(),-1),DateUtils.yyyy_MM_dd_EN);
        List<DeviceExcelDTO> list = deviceService.ListDeviceExcel(startTime,null,72);
        String path = Objects.requireNonNull(this.getClass().getClassLoader().getResource("")).getPath();
        String fileName = String.format("%s疾控中心数据-%s.xlsx",path,startTime);
        ByteArrayOutputStream out = null;
        try {
            // 生成excel文件
            out = ExcelUtil.generateExcel(list, DeviceExcelDTO.class);
            // 发送邮件
            String content = startTime+"疾控中心设备统计数据";
            String toMail = "ad@7drlb.com";
            emailSpringUtil.sendEmail("设备分钟数据",content,false,"909710561@qq.com",
                    toMail,"909710561@qq.com",null, fileName, new ByteArrayResource(out.toByteArray()));
        } catch (IOException e) {
            log.error(String.format("生成excel失败，原因：%s",e));
            e.printStackTrace();
            return new ReturnT(ReturnT.FAIL_CODE, e.getMessage());
        } catch (MessagingException e) {
            log.error(String.format("邮件发送失败，原因：%s",e));
            e.printStackTrace();
            return new ReturnT(ReturnT.FAIL_CODE, e.getMessage());
        }finally {
            if(out != null){
                out.close();
            }
        }
        return ReturnT.SUCCESS;

    }
}

 screen-job/src/main/java/com/moral/api/util/DataListener.java

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

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.util.ConverterUtils;
import groovy.transform.EqualsAndHashCode;
import org.apache.poi.ss.formula.functions.T;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @ClassName DataListener
 * @Description TODO
 * @Author @cjl
 * @Date 2024-01-24 11:10
 * @Version 1.0
 */
@EqualsAndHashCode(callSuper = true)
public class DataListener extends AnalysisEventListener<T> {
    /**
     * 缓存数据列表
     */
    private final List<T> dataList = new ArrayList<>();

    @Override
    public void invoke(T data, AnalysisContext context) {
        dataList.add(data);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

    }

    @Override
    public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
        this.invokeHeadMap(ConverterUtils.convertToStringMap(headMap,context),context);
    }

    public List<T> getDataList() {
        return dataList;
    }

}

 screen-job/src/main/java/com/moral/api/util/DeviceExcelDTO.java

New file
@@ -0,0 +1,50 @@
package com.moral.api.util;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;

/**
 * @ClassName DeviceExcelDTO
 * @Description TODO
 * @Author @cjl
 * @Date 2024-01-24 11:13
 * @Version 1.0
 */
@Data
public class DeviceExcelDTO implements Serializable {
    @ExcelProperty(value = "设备名称")
    private String name;

    @ExcelProperty(value = "时间")
    @DateTimeFormat(value = "yyyy-MM-dd HH:mm")
    private Date time;

    @ExcelProperty(value = "PM2.5")
    private BigDecimal a34004;

    @ExcelProperty(value = "温度")
    private BigDecimal a01001;

    @ExcelProperty(value = "湿度")
    private BigDecimal a01002;

    @ExcelProperty(value = "噪音")
    private BigDecimal a00e13;

    @ExcelProperty(value = "二氧化碳")
    private BigDecimal a00e19;

    @ExcelProperty(value = "甲醛")
    private BigDecimal a31001;

    @ExcelProperty(value = "TVOC")
    private BigDecimal a99054;


}

 screen-job/src/main/java/com/moral/api/util/EmailSpringUtil.java

New file
@@ -0,0 +1,115 @@
package com.moral.api.util;

import lombok.AllArgsConstructor;
import org.springframework.boot.autoconfigure.mail.MailProperties;
import org.springframework.core.io.InputStreamSource;
import org.springframework.mail.SimpleMailMessage;
import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.mail.javamail.MimeMessageHelper;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;
import org.springframework.util.ObjectUtils;

import javax.activation.FileDataSource;
import javax.mail.MessagingException;
import javax.mail.internet.MimeMessage;
import java.io.File;
import java.io.UnsupportedEncodingException;
import java.security.GeneralSecurityException;
import java.util.List;

/**
 * @ClassName EmailSpringUtil
 * @Description TODO
 * @Author @cjl
 * @Date 2024-01-24 13:23
 * @Version 1.0
 */
@Component
@AllArgsConstructor
public class EmailSpringUtil {
    private final JavaMailSender javaMailSender;
    private final MailProperties mailProperties;
    /**
     * 邮件发送
     *
     * @param subject              邮件主题
     * @param content              邮件内容
     * @param contentIsHtml        内容是否为html格式
     * @param fromMailPersonalName 发件人昵称
     * @param toMail               收件人邮箱
     * @param ccMail               抄送人邮箱
     * @param bccMail              秘密抄送人邮箱
     * @param fileNames            文件名（本地路径）
     * @throws GeneralSecurityException
     * @throws UnsupportedEncodingException
     * @throws MessagingException
     */
    public void sendEmail(String subject, String content, boolean contentIsHtml, String fromMailPersonalName,
                          String toMail, String ccMail, String bccMail, List<String> fileNames) throws MessagingException, UnsupportedEncodingException {
        MimeMessage message = javaMailSender.createMimeMessage();
        MimeMessageHelper helper = new MimeMessageHelper(message, true);
        helper.setFrom(mailProperties.getUsername(), fromMailPersonalName);
        helper.setTo(toMail);
        if (!ObjectUtils.isEmpty(ccMail)) {
            helper.setCc(ccMail);
        }
        if (!ObjectUtils.isEmpty(bccMail)) {
            helper.setBcc(bccMail);
        }
        helper.setSubject(subject);
        helper.setText(content, contentIsHtml);
        // 设置附件（注意这里的fileName必须是服务器本地文件名，不能是远程文件链接）
        if (!CollectionUtils.isEmpty(fileNames)) {
            for (String fileName : fileNames) {
                FileDataSource fileDataSource = new FileDataSource(fileName);
                helper.addAttachment(fileDataSource.getName(), fileDataSource);
            }
        }
        javaMailSender.send(message);
    }

    public void sendEmail(String subject, String content, boolean contentIsHtml, String fromMailPersonalName,
                          String toMail, String ccMail, String bccMail, File[] files) throws MessagingException, UnsupportedEncodingException {
        MimeMessage message = javaMailSender.createMimeMessage();
        MimeMessageHelper helper = new MimeMessageHelper(message, true);
        helper.setFrom(mailProperties.getUsername(), fromMailPersonalName);
        helper.setTo(toMail);
        if (!ObjectUtils.isEmpty(ccMail)) {
            helper.setCc(ccMail);
        }
        if (!ObjectUtils.isEmpty(bccMail)) {
            helper.setBcc(bccMail);
        }
        helper.setSubject(subject);
        helper.setText(content, contentIsHtml);
        // 设置附件（注意这里的fileName必须是服务器本地文件名，不能是远程文件链接）
        if (!ObjectUtils.isEmpty(files)) {
            for (File file : files) {
                helper.addAttachment(file.getName(), file);
            }
        }
        javaMailSender.send(message);
    }

    public void sendEmail(String subject, String content, boolean contentIsHtml, String fromMailPersonalName,
                          String toMail, String ccMail, String bccMail, String fileName, InputStreamSource fileInput) throws MessagingException, UnsupportedEncodingException {
        MimeMessage message = javaMailSender.createMimeMessage();
        MimeMessageHelper helper = new MimeMessageHelper(message, true);
        helper.setFrom(mailProperties.getUsername(), fromMailPersonalName);
        helper.setTo(toMail);
        if (!ObjectUtils.isEmpty(ccMail)) {
            helper.setCc(ccMail);
        }
        if (!ObjectUtils.isEmpty(bccMail)) {
            helper.setBcc(bccMail);
        }
        helper.setSubject(subject);
        helper.setText(content, contentIsHtml);
        // 设置附件（注意这里的fileName必须是服务器本地文件名，不能是远程文件链接）
        if (fileInput != null) {
            helper.addAttachment(fileName, fileInput);
        }
        javaMailSender.send(message);
    }
}

 screen-job/src/main/java/com/moral/api/util/ExcelUtil.java

New file
@@ -0,0 +1,31 @@
package com.moral.api.util;

import com.alibaba.excel.EasyExcel;

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.util.List;

/**
 * @ClassName ExcelUtil
 * @Description TODO
 * @Author @cjl
 * @Date 2024-01-24 11:12
 * @Version 1.0
 */
public class ExcelUtil {
    public static <T> File generateExcel(String fileName, List<T> dataList, Class<T> clazz) throws IOException {
        // 生成文件
        File excel = new File(fileName);
        // excel写入
        EasyExcel.write(excel,clazz).sheet(0).doWrite(dataList);
        return excel;
    }
    public static <T> ByteArrayOutputStream generateExcel(List<T> dataList, Class<T> clazz) throws IOException {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        // excel写入
        EasyExcel.write(out,clazz).sheet(0).doWrite(dataList);
        return out;
    }
}

 screen-job/src/main/resources/application-dev.yml

@@ -66,7 +66,17 @@
    driver-class-name: com.mysql.cj.jdbc.Driver
    max-conn-lifetime-millis: 20
    test-on-return: false

  mail:
    host: smtp.qq.com   # 设置邮箱主机(服务商),这里使用QQ邮件服务器
    username: 909710561@qq.com  # 设置用户名 - 发送方
    password: vimrublcqpktbdjh   # 设置密码，该处的密码是QQ邮箱开启SMTP的授权码而非QQ密码
    properties:
      mail:
        smtp:
          auth: true  # 必须进行授权认证，它的目的就是阻止他人任意乱发邮件
          starttls: #SMTP加密方式:连接到一个TLS保护连接
          enable: true
          required: true
mybatis-plus:
  mapper-locations: classpath:mapper/*.xml
  global-config:

 screen-job/src/main/resources/application-qa.yml

@@ -66,7 +66,17 @@
    driver-class-name: com.mysql.cj.jdbc.Driver
    max-conn-lifetime-millis: 20
    test-on-return: false

  mail:
    host: smtp.qq.com   # 设置邮箱主机(服务商),这里使用QQ邮件服务器
    username: 909710561@qq.com  # 设置用户名 - 发送方
    password: vimrublcqpktbdjh   # 设置密码，该处的密码是QQ邮箱开启SMTP的授权码而非QQ密码
    properties:
      mail:
        smtp:
          auth: true  # 必须进行授权认证，它的目的就是阻止他人任意乱发邮件
          starttls: #SMTP加密方式:连接到一个TLS保护连接
          enable: true
          required: true
mybatis-plus:
  mapper-locations: classpath:mapper/*.xml
  global-config:

 screen-job/src/main/resources/mapper/DeviceMapper.xml

@@ -28,4 +28,26 @@
        <result column="town_code" property="townCode"/>
    </resultMap>

<select id="ListDeviceExcel" resultType="com.moral.api.util.DeviceExcelDTO">
    SELECT
    t1.mac,
    t.NAME,
    t1.time,
    cast( t1.`value` -> '$.a34004' AS DOUBLE ) AS a34004,
    cast( t1.`value` -> '$.a01001' AS DOUBLE ) AS a01001,
    cast( t1.`value` -> '$.a01002' AS DOUBLE ) AS a01002,
    cast( t1.`value` -> '$.a00e13' AS DOUBLE ) AS a00e13,
    cast( t1.`value` -> '$.a00e19' AS DOUBLE ) AS a00e19,
    cast( t1.`value` -> '$.a31001' AS DOUBLE ) AS a31001,
    cast( t1.`value` -> '$.a99054' AS DOUBLE ) AS a99054
    FROM
    device t
    LEFT JOIN history_minutely_202401 t1 ON t1.mac = t.mac
    WHERE
    t.organization_id = #{id}
    AND date(t1.time) <![CDATA[=]]> #{startTime}
    ORDER BY
    t1.time,
    t1.mac
</select>
</mapper>