From c21c161bc5ecddbe2a1d5174c2d178d768939e17 Mon Sep 17 00:00:00 2001
From: jinpengyong <jpy123456>
Date: Fri, 26 Jan 2024 09:56:22 +0800
Subject: [PATCH] Merge branch 'dev' of http://blit.7drlb.com:8888/r/moral into wb
---
screen-job/src/main/java/com/moral/api/util/EmailSpringUtil.java | 115 ++++++++++++++
screen-job/src/main/resources/mapper/DeviceMapper.xml | 22 ++
screen-job/src/main/java/com/moral/api/util/DataListener.java | 47 +++++
screen-job/src/main/java/com/moral/api/service/DeviceService.java | 4
screen-job/src/main/java/com/moral/api/util/ExcelUtil.java | 31 +++
screen-job/src/main/java/com/moral/api/config/emile/EmailToLongConfig.java | 21 ++
screen-job/src/main/java/com/moral/api/controller/PubController.java | 50 +++++
screen-job/pom.xml | 22 ++
screen-job/src/main/resources/application-dev.yml | 19 ++
screen-job/src/main/resources/application-qa.yml | 12 +
screen-job/src/main/java/com/moral/api/util/DeviceExcelDTO.java | 50 ++++++
screen-job/src/main/java/com/moral/api/mapper/DeviceMapper.java | 5
screen-job/src/main/java/com/moral/api/service/impl/DeviceServiceImpl.java | 6
screen-job/src/main/java/com/moral/api/task/EmilInsetTask.java | 67 ++++++++
14 files changed, 465 insertions(+), 6 deletions(-)
diff --git a/screen-job/pom.xml b/screen-job/pom.xml
index 7ad595f..5d1fbf3 100644
--- a/screen-job/pom.xml
+++ b/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>
diff --git a/screen-job/src/main/java/com/moral/api/config/emile/EmailToLongConfig.java b/screen-job/src/main/java/com/moral/api/config/emile/EmailToLongConfig.java
new file mode 100644
index 0000000..b030855
--- /dev/null
+++ b/screen-job/src/main/java/com/moral/api/config/emile/EmailToLongConfig.java
@@ -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");
+ }
+}
diff --git a/screen-job/src/main/java/com/moral/api/controller/PubController.java b/screen-job/src/main/java/com/moral/api/controller/PubController.java
index 822ed13..c11ae9a 100644
--- a/screen-job/src/main/java/com/moral/api/controller/PubController.java
+++ b/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,16 @@
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.nio.file.Files;
+import java.util.*;
/**
* @ClassName UserController
@@ -46,6 +52,14 @@
private HistoryHourlyService historyHourlyService;
@Autowired
private HistoryMonthlyService historyMonthlyService;
+ @Autowired
+ private DeviceService deviceService;
+
+ private final EmailSpringUtil emailSpringUtil;
+
+ public PubController(EmailSpringUtil emailSpringUtil) {
+ this.emailSpringUtil = emailSpringUtil;
+ }
@GetMapping("insertHistoryDaily")
@ApiOperation(value = "���������������", notes = "���������������")
@@ -170,5 +184,33 @@
}
}
-
+ @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 = this.getClass().getClassLoader().getResource("/data/file").getPath();
+ String path = System.getProperty("user.dir")+"/";
+ 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();
+ } catch (MessagingException e) {
+ log.error(String.format("������������������������������%s",e));
+ e.printStackTrace();
+ }finally {
+ if(out != null){
+ out.close();
+ }
+ }
+ }
}
diff --git a/screen-job/src/main/java/com/moral/api/mapper/DeviceMapper.java b/screen-job/src/main/java/com/moral/api/mapper/DeviceMapper.java
index 4bb2d15..3b87ff3 100644
--- a/screen-job/src/main/java/com/moral/api/mapper/DeviceMapper.java
+++ b/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("id") int id);
}
diff --git a/screen-job/src/main/java/com/moral/api/service/DeviceService.java b/screen-job/src/main/java/com/moral/api/service/DeviceService.java
index 7e15c10..afe166d 100644
--- a/screen-job/src/main/java/com/moral/api/service/DeviceService.java
+++ b/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);
+
}
diff --git a/screen-job/src/main/java/com/moral/api/service/impl/DeviceServiceImpl.java b/screen-job/src/main/java/com/moral/api/service/impl/DeviceServiceImpl.java
index d624a60..8f258a8 100644
--- a/screen-job/src/main/java/com/moral/api/service/impl/DeviceServiceImpl.java
+++ b/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,id);
+ }
}
\ No newline at end of file
diff --git a/screen-job/src/main/java/com/moral/api/task/EmilInsetTask.java b/screen-job/src/main/java/com/moral/api/task/EmilInsetTask.java
new file mode 100644
index 0000000..d94153b
--- /dev/null
+++ b/screen-job/src/main/java/com/moral/api/task/EmilInsetTask.java
@@ -0,0 +1,67 @@
+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.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 EmilInsetTask {
+ @Autowired
+ private DeviceService deviceService;
+ private final EmailSpringUtil emailSpringUtil;
+
+ public EmilInsetTask(EmailSpringUtil emailSpringUtil) {
+ this.emailSpringUtil = emailSpringUtil;
+ }
+
+ @XxlJob("emilTask")
+ public void 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 = System.getProperty("user.dir")+"/";
+ 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 = "lili@nieh.chinacdc.cn";
+ 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();
+ } catch (MessagingException e) {
+ log.error(String.format("������������������������������%s",e));
+ e.printStackTrace();
+ }finally {
+ if(out != null){
+ out.close();
+ }
+ }
+ log.info("������������������");
+ }
+}
diff --git a/screen-job/src/main/java/com/moral/api/util/DataListener.java b/screen-job/src/main/java/com/moral/api/util/DataListener.java
new file mode 100644
index 0000000..51ae3f7
--- /dev/null
+++ b/screen-job/src/main/java/com/moral/api/util/DataListener.java
@@ -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;
+ }
+
+}
diff --git a/screen-job/src/main/java/com/moral/api/util/DeviceExcelDTO.java b/screen-job/src/main/java/com/moral/api/util/DeviceExcelDTO.java
new file mode 100644
index 0000000..a449542
--- /dev/null
+++ b/screen-job/src/main/java/com/moral/api/util/DeviceExcelDTO.java
@@ -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;
+
+
+}
diff --git a/screen-job/src/main/java/com/moral/api/util/EmailSpringUtil.java b/screen-job/src/main/java/com/moral/api/util/EmailSpringUtil.java
new file mode 100644
index 0000000..3cfe940
--- /dev/null
+++ b/screen-job/src/main/java/com/moral/api/util/EmailSpringUtil.java
@@ -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);
+ }
+}
diff --git a/screen-job/src/main/java/com/moral/api/util/ExcelUtil.java b/screen-job/src/main/java/com/moral/api/util/ExcelUtil.java
new file mode 100644
index 0000000..d5eb93c
--- /dev/null
+++ b/screen-job/src/main/java/com/moral/api/util/ExcelUtil.java
@@ -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;
+ }
+}
diff --git a/screen-job/src/main/resources/application-dev.yml b/screen-job/src/main/resources/application-dev.yml
index c05e54a..c3b37bd 100644
--- a/screen-job/src/main/resources/application-dev.yml
+++ b/screen-job/src/main/resources/application-dev.yml
@@ -66,7 +66,24 @@
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 # ���������������������������������������������������������������������������
+ socketFactory:
+ port: 465
+ class: javax.net.ssl.SSLSocketFactory
+ fallback: false
+ starttls: #SMTP������������:���������������TLS������������
+ enable: true
+ required: true
+ protocol: smtp
+ port: 465
+ default-encoding: UTF-8
mybatis-plus:
mapper-locations: classpath:mapper/*.xml
global-config:
diff --git a/screen-job/src/main/resources/application-qa.yml b/screen-job/src/main/resources/application-qa.yml
index 6a60364..520e023 100644
--- a/screen-job/src/main/resources/application-qa.yml
+++ b/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:
diff --git a/screen-job/src/main/resources/mapper/DeviceMapper.xml b/screen-job/src/main/resources/mapper/DeviceMapper.xml
index f3c46db..762cfa8 100644
--- a/screen-job/src/main/resources/mapper/DeviceMapper.xml
+++ b/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>
\ No newline at end of file
--
Gitblit v1.8.0