From 3d665d84b1a4ffd6749ca9354247838ce0622f4b Mon Sep 17 00:00:00 2001
From: jinpengyong <jpy123456>
Date: Tue, 13 Dec 2022 13:47:53 +0800
Subject: [PATCH] 修改sql语句问题
---
screen-api/src/main/java/com/moral/api/service/impl/SpecialDeviceServiceImpl.java | 354 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++-
1 files changed, 347 insertions(+), 7 deletions(-)
diff --git a/screen-api/src/main/java/com/moral/api/service/impl/SpecialDeviceServiceImpl.java b/screen-api/src/main/java/com/moral/api/service/impl/SpecialDeviceServiceImpl.java
index 0a0e66c..8e3e681 100644
--- a/screen-api/src/main/java/com/moral/api/service/impl/SpecialDeviceServiceImpl.java
+++ b/screen-api/src/main/java/com/moral/api/service/impl/SpecialDeviceServiceImpl.java
@@ -1,34 +1,50 @@
package com.moral.api.service.impl;
+import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
+import com.moral.api.entity.HistorySecondCruiser;
import com.moral.api.entity.Organization;
import com.moral.api.entity.SpecialDevice;
import com.moral.api.entity.SpecialDeviceHistory;
+import com.moral.api.entity.TbDaily;
+import com.moral.api.mapper.DailyMapper;
import com.moral.api.mapper.HistorySecondCruiserMapper;
-import com.moral.api.mapper.HistorySecondSpecialMapper;
import com.moral.api.mapper.SpecialDeviceMapper;
+import com.moral.api.pojo.bo.ExcelBO;
+import com.moral.api.pojo.vo.excel.DailyVo;
import com.moral.api.service.OrganizationService;
import com.moral.api.service.SpecialDeviceHistoryService;
import com.moral.api.service.SpecialDeviceService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.moral.constant.Constants;
+import com.moral.util.AmendUtils;
import com.moral.util.DateUtils;
+import com.moral.util.FileUtils;
import com.moral.util.GeodesyUtils;
import com.moral.util.TokenUtils;
+import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.boot.system.ApplicationHome;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;
+import org.springframework.web.multipart.MultipartFile;
+import java.io.File;
+import java.text.ParseException;
+import java.text.SimpleDateFormat;
import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
+import java.util.UUID;
import java.util.stream.Collectors;
/**
@@ -43,7 +59,7 @@
public class SpecialDeviceServiceImpl extends ServiceImpl<SpecialDeviceMapper, SpecialDevice> implements SpecialDeviceService {
@Autowired
- private HistorySecondSpecialMapper historySecondSpecialMapper;
+ private HistorySecondCruiserMapper HistorySecondCruiserMapper;
@Autowired
private SpecialDeviceHistoryService specialDeviceHistoryService;
@@ -56,6 +72,12 @@
@Autowired
private HistorySecondCruiserMapper historySecondCruiserMapper;
+
+ @Autowired
+ private SpecialDeviceService specialDeviceService;
+
+ @Autowired
+ private DailyMapper dailyMapper;
@Autowired
RedisTemplate redisTemplate;
@@ -74,7 +96,7 @@
orgIds.add(orgId);
QueryWrapper<SpecialDeviceHistory> queryWrapper = new QueryWrapper<>();
queryWrapper.select("mac", "name")
- .eq("special_type", Constants.SPECIAL_DEVICE_CRUISER)
+ //.eq("special_type", Constants.SPECIAL_DEVICE_CRUISER)
.eq("is_delete", Constants.NOT_DELETE)
.in("organization_id", orgIds);
return specialDeviceHistoryService.listMaps(queryWrapper);
@@ -88,14 +110,14 @@
Integer orgId = (Integer) orgInfo.get("id");
params.put("orgId", orgId);
//������������������������������������
- List<Map<String, Object>> data = historySecondSpecialMapper.getSpecialDeviceData(params);
+ List<Map<String, Object>> data = HistorySecondCruiserMapper.getCruiserData(params);
if (ObjectUtils.isEmpty(data)) {
return data;
}
data.removeIf(o -> {
Map<String, Object> value = JSONObject.parseObject(o.remove("value").toString(), Map.class);
- Object flylon = value.get("flylon");
- Object flylat = value.get("flylat");
+ Object flylon = value.get(Constants.SENSOR_CODE_LON);
+ Object flylat = value.get(Constants.SENSOR_CODE_LAT);
if (ObjectUtils.isEmpty(flylon) || ObjectUtils.isEmpty(flylat)) {
return true;
}
@@ -107,6 +129,17 @@
o.putAll(value);
return false;
});
+ /*for (Map<String, Object> map:data) {
+ Object third = map.get("dustld");
+ map.put("dustld",map.get("a21026"));
+ map.put("a21026",String.valueOf(Double.parseDouble(third.toString())*10000));
+ }*/
+ /*for (Map<String, Object> map : data) {
+ String a21026 = String.valueOf(map.get("a21026"));
+ String a21004 = String.valueOf(map.get("a21004"));
+ map.put("a21026",String.valueOf(Double.parseDouble(a21026) / 100));
+ map.put("a21004",String.valueOf(Double.parseDouble(a21004) / 10));
+ }*/
return filterData(data);
}
@@ -118,7 +151,6 @@
SpecialDevice specialDevice = specialDeviceMapper.selectOne(queryWrapper);
return specialDevice;
}
-
@Override
public List<String> getDatesByMac(String mac) {
@@ -135,6 +167,314 @@
}
+
+ /**
+ * ���������������
+ * @param params
+ * @return
+ */
+ @Override
+ public ExcelBO getDaily(Map<String, Object> params, List<MultipartFile> files) throws ParseException {
+ ExcelBO excelBO = new ExcelBO();
+ ArrayList<String> list = new ArrayList<>();
+ SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+ String time1 = params.get("time1").toString();
+ String time2 = params.get("time2").toString();
+ String rsTime = getTime(time1, time2);
+ list.add(rsTime);
+ List<Map<String, Object>> maps = specialDeviceService.carTrajectory(params);
+ if (ObjectUtils.isEmpty(maps)){
+ return null;
+ }
+ if (params.containsKey("time3")){
+ Map<String, Object> map = new HashMap<>();
+ String time3 = params.get("time3").toString();
+ String time4 = params.get("time4").toString();
+ String mac = params.get("mac").toString();
+ map.put("time1",time3);
+ map.put("time2",time4);
+ map.put("mac",mac);
+ List<Map<String, Object>> maps1 = specialDeviceService.carTrajectory(map);
+ if (ObjectUtils.isEmpty(maps1)){
+ return null;
+ }
+ maps.addAll(maps1);
+ list.add(getTime(time3, time4));
+
+ }
+ if (params.containsKey("time5")){
+ Map<String, Object> map = new HashMap<>();
+ String time5 = params.get("time5").toString();
+ String time6 = params.get("time6").toString();
+ String mac = params.get("mac").toString();
+ map.put("time1",time5);
+ map.put("time2",time6);
+ map.put("mac",mac);
+ List<Map<String, Object>> maps1 = specialDeviceService.carTrajectory(map);
+ if (ObjectUtils.isEmpty(maps1)){
+ return null;
+ }
+ maps.addAll(maps1);
+ list.add(getTime(time5, time6));
+
+ }
+ HashMap<String, Object> rsMap = getStringObjectHashMap(maps);
+
+ rsMap.put("time",list);
+
+
+ //������jar���������������
+ ApplicationHome applicationHome = new ApplicationHome(getClass());
+ //���jar������������������������������upload������������������������������������
+ String path = applicationHome.getSource().getParentFile().toString() + "/static/img";
+
+
+
+ String type = params.get("type").toString();
+ String code = params.get("code").toString();
+ String area = params.get("area").toString();
+ rsMap.put("area",area);
+ String value = JSON.toJSONString(rsMap);
+
+ String mac = params.get("mac").toString();
+ QueryWrapper<TbDaily> queryWrapper = new QueryWrapper<>();
+ queryWrapper.eq("mac",mac);
+ queryWrapper.likeRight("time",time1.substring(0,10));
+ TbDaily daily = dailyMapper.selectOne(queryWrapper);
+ ArrayList<String> images = new ArrayList<>();
+
+ if (daily==null){
+ TbDaily daily1 = new TbDaily();
+ //������������
+ if (files.size()>0){
+ for (MultipartFile file : files) {
+ String fileType = file.getContentType();
+ if ("image/jpg".equals(fileType) || "image/png".equals(fileType) || "image/jpeg".equals(fileType)){
+ //���������������
+ String fileName = file.getOriginalFilename();
+ //���������������������
+ String suffixName = fileName.substring(fileName.lastIndexOf("."));
+ //������������������
+ fileName.substring(fileName.lastIndexOf("."));
+ //���������������������
+ fileName = UUID.randomUUID() + suffixName;
+ //������������
+ if (FileUtils.upload(file, path, fileName)) {
+ images.add(fileName);
+ }
+ }
+ }
+ }
+
+ if (!ObjectUtils.isEmpty(images)) {
+ String image = images.stream()
+ .map(String::valueOf)
+ .collect(Collectors.joining(","));
+ daily1.setImages(image);
+ }
+ //������������
+ daily1.setMac(mac);
+ daily1.setTime(sdf.parse(time1));
+ Date date = new Date();
+ daily1.setDate(date);
+ daily1.setValue(value);
+ daily1.setType(type);
+ daily1.setCode(code);
+ dailyMapper.insert(daily1);
+ excelBO.setId(daily1.getId());
+ excelBO.setTime(sdf.parse(time1));
+ excelBO.setDate(date);
+ }else {
+ //���������������������������������
+ String images1 = daily.getImages();
+// ObjectUtils.isEmpty(images1);
+ if (!ObjectUtils.isEmpty(images1)){
+ String[] splits = images1.split(",");
+ for (String split : splits) {
+ String realPath = path + File.separator + split;
+ File file = new File(realPath);
+ if (file.exists() && file.isFile()) {
+ file.delete();
+ }
+ }
+ }
+
+
+ if (files.size() > 0) {
+ for (MultipartFile file : files) {
+ //������������������������
+ String fileType = file.getContentType();
+ if ("image/jpg".equals(fileType) || "image/png".equals(fileType) || "image/jpeg".equals(fileType)) {
+ //���������������
+ String fileName = file.getOriginalFilename();
+ //���������������������
+ String suffixName = fileName.substring(fileName.lastIndexOf("."));
+ //���������������������
+ fileName = UUID.randomUUID() + suffixName;
+ //������������
+ if (FileUtils.upload(file, path, fileName)) {
+ images.add(fileName);
+ }
+ }
+ }
+ }
+
+ if (!ObjectUtils.isEmpty(images)) {
+ String image = images.stream()
+ .map(String::valueOf)
+ .collect(Collectors.joining(","));
+ daily.setImages(image);
+
+ }else {
+ daily.setImages("");
+ }
+ daily.setValue(value);
+ daily.setTime(sdf.parse(time1));
+ daily.setTime(sdf.parse(time1));
+ daily.setDate(new Date());
+ daily.setType(type);
+ dailyMapper.updateById(daily);
+ excelBO.setId(daily.getId());
+ excelBO.setTime(sdf.parse(time1));
+ excelBO.setDate(new Date());
+
+ }
+ return excelBO;
+ }
+
+ private String getTime(String time1, String time2) {
+ String substring1 = time1.substring(11, 13);
+ String substring2= time2.substring(11, 13);
+ String substring3 = time1.substring(5, 7);
+ String substring4 = time1.substring(8, 10);
+ return substring3 + "���" + substring4 + "���" + substring1 + "���-" + substring2+"���";
+ }
+
+ /**
+ * ���������������������
+ * @param id
+ * @return
+ */
+ @Override
+ public DailyVo loadDaily(Integer id) {
+ ArrayList<String> rsList = new ArrayList<>();
+ DailyVo dailyVo = new DailyVo();
+ TbDaily tbDaily = dailyMapper.selectById(id);
+ if (tbDaily==null){
+ return null;
+ }
+ String images = tbDaily.getImages();
+ if (!ObjectUtils.isEmpty(images)){
+ String[] split = images.split(",");
+ List<String> list1 = Arrays.asList(split);
+ dailyVo.setImages(list1);
+ }else {
+ dailyVo.setImages(rsList);
+ }
+ String value = tbDaily.getValue();
+ Map map = JSON.parseObject(value, Map.class);
+ dailyVo.setCode(map);
+ return dailyVo;
+ }
+
+ /**
+ * ������������
+ * @param params
+ * @return
+ */
+ @Override
+ public List<ExcelBO> selectDaily(Map<String, Object> params) {
+ ArrayList<ExcelBO> excelBOS = new ArrayList<>();
+ String startTime = params.get("startTime").toString();
+ String endTime = params.get("endTime").toString();
+ String type = params.get("type").toString();
+ String code = params.get("code").toString();
+
+ Object mac = params.get("mac");
+ if (!ObjectUtils.isEmpty(mac)){
+ String MAC = mac.toString();
+ QueryWrapper<TbDaily> queryWrapper = new QueryWrapper<>();
+ queryWrapper.eq("type",type).eq("code",code).eq("mac",MAC);
+ queryWrapper.between("time",startTime,endTime);
+ List<TbDaily> dailies = dailyMapper.selectList(queryWrapper);
+ for (TbDaily daily : dailies) {
+ ExcelBO excelBO = new ExcelBO();
+ BeanUtils.copyProperties(daily,excelBO);
+ excelBOS.add(excelBO);
+ }
+ return excelBOS;
+ }
+ QueryWrapper<TbDaily> queryWrapper = new QueryWrapper<>();
+ queryWrapper.eq("type",type).eq("code",code);
+ queryWrapper.between("time",startTime,endTime);
+ List<TbDaily> dailies = dailyMapper.selectList(queryWrapper);
+ for (TbDaily daily : dailies) {
+ ExcelBO excelBO = new ExcelBO();
+ BeanUtils.copyProperties(daily,excelBO);
+ excelBOS.add(excelBO);
+ }
+ return excelBOS;
+ }
+
+ //���������������������������
+ private HashMap<String, Object> getStringObjectHashMap(List<Map<String, Object>> maps) {
+ HashMap<String, Object> rsMap = new HashMap<>();
+
+ ArrayList<Double> pm25List = new ArrayList<>();
+ ArrayList<Double> pm10List = new ArrayList<>();
+ ArrayList<Double> COList = new ArrayList<>();
+ ArrayList<Double> SO2List = new ArrayList<>();
+ ArrayList<Double> NO2List = new ArrayList<>();
+ ArrayList<Double> O3List = new ArrayList<>();
+ ArrayList<Double> VOCList = new ArrayList<>();
+
+
+ for (Map<String, Object> map : maps) {
+// String flylon = map.get("flylon").toString();
+// String flylat = map.get("flylat").toString();
+// String s = flylon + "-" + flylat;
+// if (Double.parseDouble(map.get("a34004").toString())>200){
+//
+// }
+ pm25List.add(Double.parseDouble(map.get("a34004").toString()));
+ pm10List.add(Double.parseDouble(map.get("a34002").toString()));
+ COList.add(Double.parseDouble(map.get("a21005").toString()));
+ SO2List.add(Double.parseDouble(map.get("a21026").toString()));
+ NO2List.add(Double.parseDouble(map.get("a21004").toString()));
+ O3List.add(Double.parseDouble(map.get("a05024").toString()));
+ VOCList.add(Double.parseDouble(map.get("a99054").toString()));
+
+ }
+
+ //���������������
+ double PM25 = AmendUtils.sciCal(pm25List.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 2);
+ double PM10 = AmendUtils.sciCal(pm10List.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 2);
+ double CO = AmendUtils.sciCal(COList.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 2);
+ double SO2 = AmendUtils.sciCal(SO2List.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 2);
+ double O3 = AmendUtils.sciCal(O3List.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 2);
+ double VOC= AmendUtils.sciCal(VOCList.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 2);
+ double NO2= AmendUtils.sciCal(NO2List.stream().collect(Collectors.averagingDouble(Double::doubleValue)), 2);
+
+
+ rsMap.put("maxPm25", Collections.max(pm25List));
+ rsMap.put("avgPm25",PM25);
+ rsMap.put("maxPm10",Collections.max(pm10List));
+ rsMap.put("avgPm10",PM10);
+ rsMap.put("maxCO",Collections.max(COList));
+ rsMap.put("avgCO",CO);
+ rsMap.put("maxSO2",Collections.max(SO2List));
+ rsMap.put("avgSO2",SO2);
+ rsMap.put("maxO3",Collections.max(O3List));
+ rsMap.put("avgO3",O3);
+ rsMap.put("maxVOC",Collections.max(VOCList));
+ rsMap.put("avgVOC",VOC);
+ rsMap.put("maxNO2",Collections.max(NO2List));
+ rsMap.put("avgNO2",NO2);
+ return rsMap;
+ }
+
+
+
//������������������������
private List<Map<String, Object>> filterData(List<Map<String, Object>> data) {
List<Map<String, Object>> result = new ArrayList<>();
--
Gitblit v1.8.0