From f2f77f203fd6b054197ecaba0517a159e8b3ac8f Mon Sep 17 00:00:00 2001
From: cjl <276999030@qq.com>
Date: Mon, 30 Oct 2023 15:48:24 +0800
Subject: [PATCH] fix：补偿0问题修改
---
screen-api/src/main/java/com/moral/api/service/impl/ExcelServiceImpl.java | 372 +++++++++++++++++++++++++++++++++++++++++++++++-----
1 files changed, 335 insertions(+), 37 deletions(-)
diff --git a/screen-api/src/main/java/com/moral/api/service/impl/ExcelServiceImpl.java b/screen-api/src/main/java/com/moral/api/service/impl/ExcelServiceImpl.java
index 99491f1..6d6bce9 100644
--- a/screen-api/src/main/java/com/moral/api/service/impl/ExcelServiceImpl.java
+++ b/screen-api/src/main/java/com/moral/api/service/impl/ExcelServiceImpl.java
@@ -1,6 +1,7 @@
package com.moral.api.service.impl;
+import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
@@ -11,7 +12,6 @@
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
-import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
@@ -25,30 +25,34 @@
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;
-
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
+import com.moral.api.entity.HistoryDaily;
import com.moral.api.entity.TbExcel;
import com.moral.api.mapper.ExcelMapper;
+import com.moral.api.mapper.HistoryDailyMapper;
import com.moral.api.pojo.bo.ExcelBO;
import com.moral.api.pojo.vo.excel.ExcelVo;
import com.moral.api.service.ExcelService;
import com.moral.api.utils.ExcelUtils;
-import com.moral.pojo.AQI;
-import com.moral.util.AQIUtils;
+
import com.moral.util.DateUtils;
@Service
+@Slf4j
public class ExcelServiceImpl implements ExcelService {
@Autowired
private ExcelMapper excelMapper;
+ @Autowired
+ private HistoryDailyMapper historyDailyMapper;
+
/**
- * ������
+ * ���������������
* @param files
* @param params
* @return
@@ -56,11 +60,12 @@
*/
@Override
public ExcelBO importTemplate(List<MultipartFile> files, Map<String, Object> params) throws IOException {
+ Date date2 = new Date();
String time = (String) params.get("time");
String code = (String) params.get("code");
- String time1 = (String) params.get("date");
+// String time1 = (String) params.get("date");
Date date = DateUtils.getDate(time, "yyyy-MM-dd");
- Date date1 = DateUtils.getDate(time1, "yyyy-MM-dd");
+// Date date1 = DateUtils.getDate(time1, "yyyy-MM-dd");
ExcelBO excelBO = new ExcelBO();
QueryWrapper<TbExcel> wrapper = new QueryWrapper<>();
wrapper.eq("time",date).eq("code",code);
@@ -83,18 +88,20 @@
MultipartFile file5 = files.get(4);
Map<String, Object> map5 = getMap5(file5);
map.put("c5",map5);
- MultipartFile file6 = files.get(5);
- Map<String, Object> map6 = getMap6(file6);
- map.put("c6",map6);
+ if (files.size()>5){
+ MultipartFile file6 = files.get(5);
+ Map<String, Object> map6 = getMap6(file6);
+ map.put("c6",map6);
+ }
String s = JSON.toJSONString(map);
excel1.setValue(s);
excel1.setTime(date);
excel1.setCode(code);
- excel1.setDate(date1);
+ excel1.setDate(date2);
excelMapper.insert(excel1);
excelBO.setId(excel1.getId());
excelBO.setTime(excel1.getTime());
- excelBO.setDate(excel1.getDate());
+ excelBO.setDate(date2);
}else {
HashMap<String, Object> map = new HashMap<>();
@@ -113,28 +120,28 @@
MultipartFile file5 = files.get(4);
Map<String, Object> map5 = getMap5(file5);
map.put("c5",map5);
- MultipartFile file6 = files.get(5);
- Map<String, Object> map6 = getMap6(file6);
- map.put("c6",map6);
+ if (files.size()>5){
+ MultipartFile file6 = files.get(5);
+ Map<String, Object> map6 = getMap6(file6);
+ map.put("c6",map6);
+ }
String s = JSON.toJSONString(map);
excel.setValue(s);
excelMapper.updateById(excel);
excelBO.setId(excel.getId());
excelBO.setTime(excel.getTime());
- excelBO.setDate(excel.getDate());
+ excelBO.setDate(date2);
}
return excelBO;
}
/**
- * ������
+ * ���������������
* @param id
* @return
*/
@Override
public ExcelVo export(Integer id) {
-
-
HashMap<String, Object> map1 = new HashMap<>();
ArrayList<Map<String,Object>> list1 = new ArrayList<>();
ArrayList<Map<String,Object>> list2 = new ArrayList<>();
@@ -180,11 +187,19 @@
Map map5 = JSON.parseObject(s, Map.class);
Set set1s = map5.keySet();
for (Object set1 : set1s) {
- Object o1 = map5.get(set1);
- String s1 = JSON.toJSONString(o1);
- Map map6 = JSON.parseObject(s1, Map.class);
- map6.put("place",set1);
- list3.add(map6);
+ if (set1.equals("���������")){
+ Object o1 = map5.get(set1);
+ String s1 = JSON.toJSONString(o1);
+ Map map6 = JSON.parseObject(s1, Map.class);
+ map6.put("place","���������");
+ list3.add(map6);
+ }else {
+ Object o1 = map5.get(set1);
+ String s1 = JSON.toJSONString(o1);
+ Map map6 = JSON.parseObject(s1, Map.class);
+ map6.put("place",set1.toString().substring(3));
+ list3.add(map6);
+ }
}
}
if (set.equals("c4")){
@@ -367,8 +382,10 @@
if (Double.parseDouble(CO)<4){
count++;
}
- if (Double.parseDouble(O8)<160){
- count++;
+ if (!O8.equals("--")) {
+ if (Double.parseDouble(O8) < 160) {
+ count++;
+ }
}
@@ -384,7 +401,7 @@
// "������8���������������������90���������������������"+O3y+"������/������������������"+code5+"%���" +
// "���������������������������������"+AQIy+"%���������"+code6+"%���";
- excelVo.setQuality(s3+s4);
+ excelVo.setQuality(s4+s3);
excelVo.setSu(s1);
excelVo.setCount(count);
excelVo.setPM10(PM10);
@@ -440,6 +457,126 @@
}
return excelBOS;
+ }
+
+ /**
+ * ���������������
+ * @param files
+ * @return
+ */
+ @Override
+ public void rexcelImport(List<MultipartFile> files) throws IOException {
+ MultipartFile file = files.get(0);
+ XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
+// HashMap<String, Object> rsMap = new HashMap<>();
+ ArrayList<Map<String, Object>> list = new ArrayList<>();
+ XSSFSheet sheetAt = workbook.getSheetAt(0);
+ //������������������
+ int lastRowNum = sheetAt.getLastRowNum();
+ for (int i = 1; i <= lastRowNum; i++) {
+ XSSFRow row = sheetAt.getRow(i);
+ if (row == null) {
+ continue;
+ }
+ short lastCellNum = row.getLastCellNum();
+// if (lastCellNum < 10) {
+// continue;
+// }
+ Object[] objects = new Object[lastCellNum];
+ for (int j = 0; j < lastCellNum; j++) {
+ Cell cell = row.getCell(j);
+ Object value = ExcelUtils.getValue(cell);
+ objects[j] = value;
+ }
+ HashMap<String, Object> map = new HashMap<>();
+ map.put("mac", objects[0]);
+ map.put("time", objects[1]);
+ // pm2.5
+ if (!objects[2].toString().equals("-")){
+ map.put("a34004", objects[2]);
+ }
+
+ // pm10
+ if (!objects[3].toString().equals("-")){
+ map.put("a34002", objects[3]);
+ }
+ // ������������
+ if (!objects[4].toString().equals("-")){
+ map.put("a21026", objects[4]);
+ }
+ // ������������
+ if (!objects[5].toString().equals("-")){
+ map.put("a21004", objects[5]);
+ }
+ // co
+ if (!objects[6].toString().equals("-")){
+ map.put("a21005", objects[6]);
+ }
+ // o3
+ if (!objects[7].toString().equals("-")){
+ map.put("a05024", objects[7]);
+ }
+
+ // ������
+ if (!objects[8].toString().equals("-")){
+ map.put("a01001", objects[8]);
+ }
+ // ������
+ if (!objects[9].toString().equals("-")){
+ map.put("a01002", objects[9]);
+ }
+ // ������
+ if (!objects[10].toString().equals("-")){
+ map.put("a01007", objects[10]);
+ }
+ // ������
+ if (!objects[11].toString().equals("-")){
+ map.put("a01008", objects[11]);
+ }
+
+ // ������
+ if (!objects[12].toString().equals("-")){
+ map.put("a01006", objects[12]);
+ }
+ // ������������
+ if (!objects[13].toString().equals("-")){
+ map.put("a00e12", objects[13]);
+ }
+ // tvoc
+ if (!objects[14].toString().equals("-")){
+ map.put("a99054", objects[14]);
+ }
+
+// map.put("���������0.3", objects[15]);
+// map.put("���������2.5", objects[16]);
+// map.put("���������", objects[17]);
+// map.put("������", objects[18]);
+// map.put("���", objects[19]);
+// map.put("������", objects[20]);
+// map.put("���������", objects[21]);
+// map.put("���������������", objects[22]);
+ list.add(map);
+ }
+// ArrayList<HistoryDaily> rsList = new ArrayList<>();
+ for (Map<String, Object> map : list) {
+ HistoryDaily historyDaily = new HistoryDaily();
+ String time = map.remove("time").toString();
+ Date date=null;
+ if (time.length()>20){
+ date = DateUtils.dateStringToDate(time);
+ }else {
+ date = DateUtils.getDate(time, DateUtils.yyyy_MM_dd_HH_mm_ss_EN);
+ }
+
+ String mac = map.remove("mac").toString();
+ String value = JSONObject.toJSONString(map);
+ historyDaily.setMac(mac);
+ historyDaily.setTime(date);
+ historyDaily.setValue(value);
+// log.info(historyDaily.getValue());
+ historyDailyMapper.insert(historyDaily);
+// rsList.add(historyDaily);
+ }
}
@@ -590,7 +727,11 @@
objects[j] = value;
}
HashMap<String, Object> map = new HashMap<>();
- map.put("place", objects[0]);
+ if (objects[0].toString().equals("���������")){
+ map.put("place", "���������");
+ }else {
+ map.put("place",objects[0].toString().substring(3));
+ }
map.put("������", objects[1]);
map.put("SO2", objects[2]);
map.put("SO2���������", objects[3]);
@@ -760,25 +901,37 @@
for (String s : strings1) {
HashMap<String, Object> map = new HashMap<>();
List<Double> doubles = ListHashMap.get(s);
+
+ ArrayList<Double> list = new ArrayList<>();
+ double co = getCo(doubles.get(0));
+ double so2 = getSo2(doubles.get(1));
+ double no2 = getNo2(doubles.get(2));
+ double pm10 = getPm10(doubles.get(3));
+ double pm2_5 = getPm2_5(doubles.get(4));
+ double o3 = getO3(doubles.get(5));
+ list.add(co);
+ list.add(so2);
+ list.add(no2);
+ list.add(pm10);
+ list.add(pm2_5);
+ list.add(o3);
+
+ Double aqi = Collections.max(list);
+
map.put("a21005",doubles.get(0));
map.put("a21026",doubles.get(1));
map.put("a21004",doubles.get(2));
map.put("a34002",doubles.get(3));
map.put("a34004",doubles.get(4));
map.put("a05024",doubles.get(5));
- AQI aqi = AQIUtils.dailyAQI(map);
- Integer aqiValue = aqi.getAQIValue();
- map.put("AQI",aqiValue);
- List<String> primaryPollutantNames = aqi.getPrimaryPollutantNames();
- String primaryPollutant = "__";
- if (!ObjectUtils.isEmpty(primaryPollutantNames)) {
- primaryPollutant = primaryPollutantNames.stream().map(String::valueOf).collect(Collectors.joining(","));
- }
- map.put("SU", primaryPollutant);
+ map.put("AQI",aqi);
+ map.put("SU", getSU(aqi,list));
resultMap.put(s,map);
}
return resultMap;
}
+
+
/**
* ���������������
@@ -947,4 +1100,149 @@
}
return round;
}
+ /**
+ * ���������������������
+ * @param aqi
+ * @param list
+ * @return
+ */
+ private String getSU(Double aqi,ArrayList list){
+ String SU=null;
+ if (aqi<=50){
+ SU="-";
+ } else if (aqi==list.get(1)){
+ SU="SO2";
+ }else if (aqi==list.get(0)){
+ SU="CO";
+ }else if (aqi==list.get(2)){
+ SU="NO2";
+ }else if (aqi==list.get(3)){
+ SU="PM10";
+ }else if (aqi==list.get(4)){
+ SU="PM2.5";
+ }else if (aqi==list.get(5)){
+ SU="O3";
+ }
+ return SU;
+ }
+
+ private static double getSo2(double so2){
+ double v = 0.0;
+ if (so2<50){
+ v = 50 * so2 / 50;
+ }else if (so2<150 && so2 >= 50){
+ v = 50 * (so2 - 50) / 100 + 50;
+ }else if (so2<475 && so2 >= 150){
+ v = 50 * (so2 - 150) / 325 + 100;
+ }else if (so2<800 && so2 >= 475){
+ v = 50 * (so2 - 475) / 325 + 150;
+ }else {
+ v = 100 * (so2 - 800) / 800 + 200;
+ }
+ return Math.round(v);
+ }
+
+ private static double getNo2(double no2){
+ double v = 0.0;
+ if (no2<40){
+ v = 50 * no2 / 40;
+ }else if (no2<80 && no2 >= 40){
+ v = 50 * (no2 - 40) / 40 + 50;
+ }else if (no2<180 && no2 >= 80){
+ v = 50 * (no2 - 80) / 100 + 100;
+ }else if (no2<280 && no2 >= 180){
+ v = 50 * (no2 - 180) / 100 + 150;
+ }else if (no2<565 && no2 >= 280){
+ v = 100 * (no2 - 280) / 285 + 200;
+ }else if (no2<750 && no2 >= 565){
+ v = 100 * (no2 - 565) / 185 + 300;
+ }
+ else {
+ v = 100 * (no2 - 750) / 190 + 400;
+ }
+ return Math.round(v);
+ }
+
+
+ private static double getCo(double co){
+ double v = 0.0;
+ if (co<2){
+ v = 50 * co / 2;
+ }else if (co<4 && co >= 2){
+ v = 50 * (co - 2) / 2 + 50;
+ }else if (co<14 && co >= 4){
+ v = 50 * (co - 4) / 10 + 100;
+ }else if (co<24 && co >= 14){
+ v = 50 * (co - 14) / 10 + 150;
+ }else if (co<36 && co >= 24){
+ v = 100 * (co - 24) / 12 + 200;
+ }else if (co<48 && co >= 36){
+ v = 100 * (co - 36) / 12 + 300;
+ }
+ else {
+ v = 100 * (co - 48) / 12 + 400;
+ }
+ return Math.round(v);
+ }
+
+ private static double getPm10(double pm10){
+ double v = 0.0;
+ if (pm10<50){
+ v = 50 * pm10 / 50;
+ }else if (pm10<150 && pm10 >= 50){
+ v = 50 * (pm10 - 50) / 100 + 50;
+ }else if (pm10<250 && pm10 >= 150){
+ v = 50 * (pm10 - 150) / 100 + 100;
+ }else if (pm10<350 && pm10 >= 250){
+ v = 50 * (pm10 - 250) / 100 + 150;
+ }else if (pm10<420 && pm10 >= 350){
+ v = 100 * (pm10 - 350) / 70 + 200;
+ }else if (pm10<500 && pm10 >= 420){
+ v = 100 * (pm10 - 420) / 80 + 300;
+ }
+ else {
+ v = 100 * (pm10 - 500) / 100 + 400;
+ }
+ return Math.round(v);
+ }
+
+ private static double getPm2_5(double pm2_5){
+ double v = 0.0;
+ if (pm2_5<35){
+ v = 50 * pm2_5 / 35;
+ }else if (pm2_5<75 && pm2_5 >= 35){
+ v = 50 * (pm2_5 - 35) / 40 + 50;
+ }else if (pm2_5<115 && pm2_5 >= 75){
+ v = 50 * (pm2_5 - 75) / 40 + 100;
+ }else if (pm2_5<150 && pm2_5 >= 115){
+ v = 50 * (pm2_5 - 115) / 35 + 150;
+ }else if (pm2_5<250 && pm2_5 >= 150){
+ v = 100 * (pm2_5 - 150) / 100 + 200;
+ }else if (pm2_5<350 && pm2_5 >= 250){
+ v = 100 * (pm2_5 - 250) / 100 + 300;
+ }
+ else {
+ v = 100 * (pm2_5 - 350) / 150 + 400;
+ }
+ return Math.round(v);
+ }
+
+
+ private static double getO3(double o3){
+ double v = 0.0;
+ if (o3<100){
+ v = 50 * o3 / 100;
+ }else if (o3<160 && o3 >= 100){
+ v = 50 * (o3 - 100) / 60 + 50;
+ }else if (o3<215 && o3 >= 160){
+ v = 50 * (o3 - 160) / 55 + 100;
+ }else if (o3<265 && o3 >= 215){
+ v = 50 * (o3 - 215) / 50 + 150;
+ }else if (o3<800 && o3 >= 265){
+ v = 100 * (o3 - 265) / 535 + 200;
+ }else if (o3<1000 && o3 >= 800){
+ v = 100 * (o3 - 800) / 200 + 300;
+ }
+ return Math.round(v);
+ }
}
\ No newline at end of file
--
Gitblit v1.8.0