From 2f55cebbad3dea187a5f91d16ec80a9677dab699 Mon Sep 17 00:00:00 2001
From: 13693261870 <252740454@qq.com>
Date: 星期三, 13 十一月 2024 11:16:53 +0800
Subject: [PATCH] 1
---
src/main/java/com/yssh/service/XlsReportService.java | 246 +++++++++++++++++++++++++++++++++++++-----------
1 files changed, 187 insertions(+), 59 deletions(-)
diff --git a/src/main/java/com/yssh/service/XlsReportService.java b/src/main/java/com/yssh/service/XlsReportService.java
index e010172..236cd7d 100644
--- a/src/main/java/com/yssh/service/XlsReportService.java
+++ b/src/main/java/com/yssh/service/XlsReportService.java
@@ -5,10 +5,12 @@
import com.yssh.entity.xls.DayExcel;
import com.yssh.entity.xls.MonthExcel;
import com.yssh.entity.xls.WeekExcel;
+import com.yssh.entity.xls.XlsReport;
import com.yssh.mapper.XlsReportMapper;
import com.yssh.utils.CalculateUtils;
import com.yssh.utils.DateUtils;
import com.yssh.utils.ExcelUtils;
+import com.yssh.utils.WebUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
@@ -16,15 +18,11 @@
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
+import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.util.*;
-/**
- * Excel瀵煎嚭鏈嶅姟绫�
- * @author WWW
- * @author 2023-08-05
- */
@Service
public class XlsReportService {
protected final Logger logger = LoggerFactory.getLogger(this.getClass());
@@ -54,17 +52,11 @@
private double bjz;
/**
- * Excel鏄惁宸插瓨鍦�
+ * 鎶ヨ鍊�
*/
- private boolean xlsExists(String type, String name) {
- int rows = mapper.reportExists(type, name);
+ @Value("${report.templates}")
+ private String templates;
- return rows > 0;
- }
-
- /**
- * 鑾峰彇瀵煎嚭璺緞
- */
private String getExpPath(String type) {
String path = reportPath + File.separator + type;
@@ -76,18 +68,13 @@
return path;
}
- /**
- * 鑾峰彇Excel妯℃澘
- */
private String getXslTemplate(String type) throws IOException {
- ClassPathResource resource = new ClassPathResource(String.format("templates/%s.xlsx", type));
+ //ClassPathResource resource = new ClassPathResource(String.format("templates/%s.xlsx", type));
+ //return resource.exists() ? resource.getFile().getPath() : null;
- return resource.exists() ? resource.getFile().getPath() : null;
+ return String.format(templates + File.separator + type + ".xlsx");
}
- /**
- * 鍒涘缓Excel
- */
private <T> void createExcel(String source, String target, List<T> list) {
Map<String, List<T>> map = new HashMap<>();
map.put("data", list);
@@ -95,28 +82,65 @@
ExcelUtils.writeToTemplate(source, target, map);
}
- /**
- * 鍒涘缓Excel
- */
+ private String getStrDate(String type, Date date) {
+ return DateUtils.parseDateToStr("month".equals(type) ? DateUtils.YYYYMM : DateUtils.YYYYMMDD, date);
+ }
+
+ private String getTarget(String type, Date date) {
+ String strData = getStrDate(type, date);
+
+ return String.format("%s\\%s.xlsx", getExpPath(type), strData);
+ }
+
+ private boolean xlsExists(String type, Date date) {
+ String target = getTarget(type, date);
+ File f = new File(target);
+
+ return f.exists() && !f.isDirectory();
+ }
+
private <T> String createExcel(String type, Date date, List<T> list) throws Exception {
String source = getXslTemplate(type);
- String strData = DateUtils.parseDateToStr("month".equals(type) ? DateUtils.YYYYMM : DateUtils.YYYYMMDD, date);
- String target = String.format("%s\\%s.xlsx", getExpPath(type), strData);
+ String strData = getStrDate(type, date);
+ String target = getTarget(type, date);
- createExcel(source, target, list);
+ if (!xlsExists(type, date)) {
+ createExcel(source, target, list);
+ }
return String.format("%s\\%s.xlsx", type, strData);
}
- /**
- * 鑾峰彇鍙楀奖鍝嶅洜绱犲強鍘熷洜
- */
+ private boolean recordExists(String type, Date date) {
+ String name = String.format("%s.xlsx", getStrDate(type, date));
+ int rows = mapper.reportExists(type, name);
+
+ return rows > 0;
+ }
+
+ private <T> void saveResult(String type, Date date, List<T> list) throws Exception {
+ String filePath = createExcel(type, date, list);
+ if (recordExists(type, date)) return;
+
+ String name = filePath.replace(type + "\\", "");
+ date = DateUtils.trimTime(date);
+
+ XlsReport xls = new XlsReport();
+ xls.setName(name);
+ xls.setType(type);
+ xls.setPath(filePath);
+ xls.setCreateTime(date);
+
+ mapper.insertReport(xls);
+ }
+
public String getYs(List<SuYuan700> suList) {
if (null == suList || 0 == suList.size()) return "";
List<String> list = new ArrayList<>();
for (SuYuan700 su : suList) {
List<String> sub = new ArrayList<>();
+ sub.add(DateUtils.getYyyyMmDdHh(su.getCreateTime()));
if (null != su.getAddr1())
sub.add(su.getAddr1() + "锛屾鐜囷細" + (su.getOdds1() * 100) + "%锛孹锛�" + su.getX1() + "锛孻锛�" + su.getY1());
if (null != su.getAddr2())
@@ -131,75 +155,179 @@
}
/**
- * 鍒涘缓鏃ユ姤锛欴ayExcel day = new DayExcel("AI-" + (i < 10 ? "0" : "") + i, "lj", "ljtb", "ys", "3m/s", "涓滃崡", "掳c");
+ * 鍒涘缓鏃ユ姤锛歯ew DayExcel("AI-" + (i < 10 ? "0" : "") + i, "lj", "ljtb", "ys", "3m/s", "涓滃崡", "掳c");
*/
public void createDayReport(Date date) {
try {
- Date yesterday = DateUtils.getAPeriodOfTime(date, -1, Calendar.DATE);
+ String type = "day";
+ Date yesterday = DateUtils.getAPeriodOfTime(date, -1, Calendar.DATE); // 2023-07-24
+ if (xlsExists(type, yesterday) && recordExists(type, yesterday)) return;
+
String yyyy = DateUtils.parseDateToStr(DateUtils.YYYY, yesterday); // 2023
String yyyymmdd = DateUtils.parseDateToStr(DateUtils.YYYYMMDD, yesterday); // 20230724
- String yyyy_mm_dd = DateUtils.parseDateToStr(DateUtils.YYYY_MM_DD, yesterday); // 2023-07-24
- Integer start = Integer.parseInt(yyyy + "010100"); // 2023010100
+ Integer yearStart = Integer.parseInt(yyyy + "010100"); // 2023010100
+ Integer dayStart = Integer.parseInt(yyyymmdd + "00"); // 2023072400
Integer end = Integer.parseInt(yyyymmdd + "23"); // 2023072423
String lastYear = DateUtils.parseDateToStr(DateUtils.YYYY, DateUtils.lastYear(yesterday)); // 2022
List<DayExcel> list = new ArrayList<>();
for (int i = 1; i < 47; i++) {
String name = "AI-" + (i < 10 ? "0" : "") + i;
- double rjz = mapper.selectDayAvg(yyyymmdd + "%", name);
- double lj = mapper.selectAccumulate(start, end, name);
- double lastLj = mapper.selectYearAccumulate(lastYear, name);
- double ljtb = CalculateUtils.round2((lj - lastLj) / lastLj * 100);
+ double rjz = mapper.selectDayAvg(yyyymmdd + "%", name); // 鏃ュ潎鍊�
+
+ double lj = mapper.selectAccumulate(yearStart, end, name); // 绱
+
+ double ljjz = mapper.selectYearAccumulate(yyyy + "%", name); // 浠婂勾绱鍧囧��
+ double qnLjjz = mapper.selectYearAccumulate(lastYear + "%", name); // 鍘诲勾绱鍧囧��
+ String ljtb = 0 == qnLjjz ? "--" : CalculateUtils.round2((ljjz - qnLjjz) / qnLjjz * 100) + "%"; // 绱鍚屾瘮
+
MonitorPointPosition point = commonService.select3dCheckPointByName(name);
String id = point.getId().substring(0, point.getId().lastIndexOf("_") + 1) + "0";
- List<SuYuan700> suList = mapper.selectSuYuanByTime(id, yyyy_mm_dd + " 00:00:00", yyyy_mm_dd + " 23:00:00");
- String ys = getYs(suList);
+ // List<SuYuan700> suList = mapper.selectSuYuanByTime(id, yyyy_mm_dd + " 00:00:00", yyyy_mm_dd + " 23:00:00");
+ List<SuYuan700> suList = mapper.selectSuYuanByStartAndEnd(id, name, yjz, dayStart, end);
+ String ys = getYs(suList); // 鍙楀奖鍝嶅洜绱犲強鍘熷洜
- list.add(new DayExcel("" + rjz, "" + lj, "" + ljtb, ys, "", "", ""));
+ list.add(new DayExcel("" + rjz, "" + lj, ljtb, ys, "", "", ""));
}
-
- String filePath = createExcel("day", yesterday, list);
+ saveResult(type, yesterday, list);
} catch (Exception ex) {
logger.error(ex.getMessage(), ex);
}
}
/**
- * 鍒涘缓鍛ㄦ姤
+ * 鍒涘缓鍛ㄦ姤锛歯ew WeekExcel("AI-" + (i < 10 ? "0" : "") + i, "zhb", "ztq", "ztb", "zhb2", "lj", "ljtb", "syn", "ys", "fs", "fx", "掳c");
*/
public void createWeekReport(Date date) {
try {
- Date start = DateUtils.getAPeriodOfTime(date, -7, Calendar.DATE);
- Date end = DateUtils.getAPeriodOfTime(date, -1, Calendar.DATE);
+ String type = "week";
+ Date mon = DateUtils.getAPeriodOfTime(date, -7, Calendar.DATE); // 2023-07-24
+ Date sun = DateUtils.getAPeriodOfTime(date, -1, Calendar.DATE); // 2023-07-30
+ if (xlsExists(type, sun) && recordExists(type, sun)) return;
+
+ Date lastMon = DateUtils.getAPeriodOfTime(date, -14, Calendar.DATE); // 2023-07-17
+ Date lastSun = DateUtils.getAPeriodOfTime(date, -8, Calendar.DATE); // 2023-07-23
+ Date lastYear1 = DateUtils.getAPeriodOfTime(mon, -1, Calendar.YEAR); // 2022-07-24
+ Date lastYear7 = DateUtils.getAPeriodOfTime(sun, -1, Calendar.YEAR); // 2022-07-30
+ Integer intMon = Integer.parseInt(DateUtils.parseDateToStr(DateUtils.YYYYMMDD, mon) + "00"); // 2023072400
+ Integer intSun = Integer.parseInt(DateUtils.parseDateToStr(DateUtils.YYYYMMDD, sun) + "23"); // 2023073023
+ Integer intLastMon = Integer.parseInt(DateUtils.parseDateToStr(DateUtils.YYYYMMDD, lastMon) + "00"); // 2023071700
+ Integer intLastSun = Integer.parseInt(DateUtils.parseDateToStr(DateUtils.YYYYMMDD, lastSun) + "23"); // 2023072323
+ Integer intLastYear1 = Integer.parseInt(DateUtils.parseDateToStr(DateUtils.YYYYMMDD, lastYear1) + "00"); // 2022072400
+ Integer intLastYear7 = Integer.parseInt(DateUtils.parseDateToStr(DateUtils.YYYYMMDD, lastYear7) + "23"); // 2022073023
+ String year = DateUtils.parseDateToStr(DateUtils.YYYY, sun); // 2023
+ Integer yearStart = Integer.parseInt(year + "010100"); // 2023010100
+ String lastYear = DateUtils.parseDateToStr(DateUtils.YYYY, DateUtils.lastYear(sun)); // 2022
+ int weekOfYear = DateUtils.getWeekOfYear(sun); // 31
+ Date lastYearSun = DateUtils.getWeekOfYearForSun(Integer.parseInt(lastYear), weekOfYear); // 2022-07-24
+ Date lastYearMon = DateUtils.getAPeriodOfTime(lastYearSun, -6, Calendar.DATE); // 2022-07-18
+ Integer intLastYearMon = Integer.parseInt(DateUtils.parseDateToStr(DateUtils.YYYYMMDD, lastYearMon) + "00"); // 2022071800
+ Integer intLastYearSun = Integer.parseInt(DateUtils.parseDateToStr(DateUtils.YYYYMMDD, lastYearSun) + "23"); // 2022072423
List<WeekExcel> list = new ArrayList<>();
for (int i = 1; i < 47; i++) {
- WeekExcel day = new WeekExcel("AI-" + (i < 10 ? "0" : "") + i, "zhb", "ztq", "ztb", "zhb2", "lj", "ljtb", "syn", "ys", "fs", "fx", "掳c");
- list.add(day);
- }
+ String name = "AI-" + (i < 10 ? "0" : "") + i;
+ double sz = mapper.selectAccumulate(intMon, intSun, name); // 涓婂懆
- String filePath = createExcel("week", end, list);
+ double ssz = mapper.selectAccumulate(intLastMon, intLastSun, name); // 涓婁笂鍛�
+ String zhb = 0 == ssz ? "--" : CalculateUtils.round2((sz - ssz) / ssz * 100) + "%"; // 鍛ㄧ幆姣�
+
+ double qntq = mapper.selectAccumulate(intLastYear1, intLastYear7, name); // 鍘诲勾鍚屾湡
+ String ztq = 0 == qntq ? "--" : CalculateUtils.round2((sz - qntq) / qntq * 100) + "%"; // 鍛ㄥ悓鏈�
+
+ double qntz = mapper.selectAccumulate(intLastYearMon, intLastYearSun, name); // 鍘诲勾鍚屽懆
+ String ztb = 0 == qntz ? "--" : CalculateUtils.round2((sz - qntz) / qntz * 100) + "%"; // 鍛ㄥ悓姣�
+
+ double lj = mapper.selectAccumulate(yearStart, intSun, name); // 绱
+
+ double ljjz = mapper.selectYearAccumulate(year + "%", name); // 浠婂勾绱鍧囧��
+ double qnLjjz = mapper.selectYearAccumulate(lastYear + "%", name); // 鍘诲勾绱鍧囧��
+ String ljtb = 0 == qnLjjz ? "--" : CalculateUtils.round2((ljjz - qnLjjz) / qnLjjz * 100) + "%"; // 绱鍚屾瘮
+
+ String syn = 0 == qnLjjz ? "--" : CalculateUtils.round2((ljjz - qnLjjz) / qnLjjz * 100) + "%"; // 杈冧笂涓�骞村害鍙樺寲骞呭害
+
+ MonitorPointPosition point = commonService.select3dCheckPointByName(name);
+ String id = point.getId().substring(0, point.getId().lastIndexOf("_") + 1) + "0";
+ List<SuYuan700> suList = mapper.selectSuYuanByStartAndEnd(id, name, yjz, intMon, intSun);
+ String ys = getYs(suList); // 鍙楀奖鍝嶅洜绱犲強鍘熷洜
+
+ list.add(new WeekExcel("" + sz, zhb, ztq, ztb, "" + lj, ljtb, syn, ys, "", "", ""));
+ }
+ saveResult(type, sun, list);
} catch (Exception ex) {
logger.error(ex.getMessage(), ex);
}
}
/**
- * 鍒涘缓鏈堟姤
+ * 鍒涘缓鏈堟姤锛歯ew MonthExcel("AI-" + (i < 10 ? "0" : "") + i, "yhb", "ytq", "ytb", "lj", "ljtb", "qyn", "ys", "fs", "fx", "掳c");
*/
public void createMonthReport(Date date) {
try {
- Date yesterday = DateUtils.getAPeriodOfTime(date, -1, Calendar.DATE);
- Date start = DateUtils.getMonthStart(yesterday);
- Date end = DateUtils.getMonthEnd(yesterday);
+ String type = "month";
+ Date yesterday = DateUtils.getAPeriodOfTime(date, -1, Calendar.DATE); // 2023-07-31
+ Date monthStart = DateUtils.getMonthStart(yesterday); // 2023-07-01
+ Date monthEnd = DateUtils.getMonthEnd(yesterday); // 2023-07-31
+ if (xlsExists(type, monthEnd) && recordExists(type, monthEnd)) return;
+
+ int intMonthStart = Integer.parseInt(DateUtils.parseDateToStr(DateUtils.YYYYMMDD, monthStart) + "00"); // 2023070100
+ int intMonthEnd = Integer.parseInt(DateUtils.parseDateToStr(DateUtils.YYYYMMDD, monthEnd) + "23"); // 2023073123
+ Date lastMonth = DateUtils.getAPeriodOfTime(yesterday, -1, Calendar.MONTH); // 2023-06-30
+ Date lastMonthStart = DateUtils.getMonthStart(lastMonth); // 2023-06-01
+ Date lastMonthEnd = DateUtils.getMonthEnd(lastMonth); // 2023-06-30
+ int intLastMonthStart = Integer.parseInt(DateUtils.parseDateToStr(DateUtils.YYYYMMDD, lastMonthStart) + "00"); // 2023060100
+ int intLastMonthEnd = Integer.parseInt(DateUtils.parseDateToStr(DateUtils.YYYYMMDD, lastMonthEnd) + "23"); // 2023063023
+ int lastYearMonthStart = intMonthStart - 1000000; // 2023070100
+ int lastYearMonthEnd = intMonthEnd - 1000000; // 2023073123
+ String year = DateUtils.parseDateToStr(DateUtils.YYYY, yesterday); // 2023
+ Integer yearStart = Integer.parseInt(year + "010100"); // 2023010100
+ Date lastDay = DateUtils.lastYear(yesterday); // 2022-07-31
+ String lastYear = DateUtils.parseDateToStr(DateUtils.YYYY, lastDay); // 2022
List<MonthExcel> list = new ArrayList<>();
for (int i = 1; i < 47; i++) {
- MonthExcel day = new MonthExcel("AI-" + (i < 10 ? "0" : "") + i, "yhb", "ytq", "ytb", "yhb2", "lj", "ljtb", "qyn", "ys", "fs", "fx", "掳c");
- list.add(day);
- }
+ String name = "AI-" + (i < 10 ? "0" : "") + i;
+ double sy = mapper.selectAccumulate(intMonthStart, intMonthEnd, name); // 涓婃湀
- String filePath = createExcel("month", end, list);
+ double ssy = mapper.selectAccumulate(intLastMonthStart, intLastMonthEnd, name); // 涓婁笂鏈�
+ String yhb = 0 == ssy ? "--" : CalculateUtils.round2((sy - ssy) / ssy * 100) + "%"; // 鏈堢幆姣�
+
+ double qntq = mapper.selectAccumulate(lastYearMonthStart, lastYearMonthEnd, name); // 鍘诲勾鍚屾湡
+ String ytq = 0 == qntq ? "--" : CalculateUtils.round2((sy - qntq) / qntq * 100) + "%"; // 鏈堝悓鏈�
+
+ String ytb = 0 == qntq ? "--" : CalculateUtils.round2((sy - qntq) / qntq * 100) + "%"; // 鏈堝悓姣�
+
+ double lj = mapper.selectAccumulate(yearStart, intMonthEnd, name); // 绱
+
+ double ljjz = mapper.selectYearAccumulate(year + "%", name); // 浠婂勾绱鍧囧��
+ double qnLjjz = mapper.selectYearAccumulate(lastYear + "%", name); // 鍘诲勾绱鍧囧��
+ String ljtb = 0 == qnLjjz ? "--" : CalculateUtils.round2((ljjz - qnLjjz) / qnLjjz * 100) + "%"; // 绱鍚屾瘮
+
+ String qyn = 0 == qnLjjz ? "--" : CalculateUtils.round2((ljjz - qnLjjz) / qnLjjz * 100) + "%"; // 杈冧笂涓�骞村害鍙樺寲骞呭害
+
+ MonitorPointPosition point = commonService.select3dCheckPointByName(name);
+ String id = point.getId().substring(0, point.getId().lastIndexOf("_") + 1) + "0";
+ List<SuYuan700> suList = mapper.selectSuYuanByStartAndEnd(id, name, yjz, intMonthStart, intMonthEnd);
+ String ys = getYs(suList); // 鍙楀奖鍝嶅洜绱犲強鍘熷洜
+
+ list.add(new MonthExcel("" + sy, yhb, ytq, ytb, "" + lj, ljtb, qyn, ys, "", "", ""));
+ }
+ saveResult(type, monthEnd, list);
+ } catch (Exception ex) {
+ logger.error(ex.getMessage(), ex);
+ }
+ }
+
+ public void downloadById(Integer id, HttpServletResponse res) {
+ XlsReport xlsReport = mapper.selectById(id);
+ if (null == xlsReport) return;
+
+ String file = reportPath + File.separator + xlsReport.getPath();
+ File f = new File(file);
+ if (!f.exists() || f.isDirectory()) return;
+
+ try {
+ WebUtils.download(file, xlsReport.getName(), false, res);
} catch (Exception ex) {
logger.error(ex.getMessage(), ex);
}
--
Gitblit v1.9.3