package com.yssh.service;
|
|
import com.yssh.entity.MonitorPointPosition;
|
import com.yssh.entity.SuYuan700;
|
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 org.slf4j.Logger;
|
import org.slf4j.LoggerFactory;
|
import org.springframework.beans.factory.annotation.Value;
|
import org.springframework.core.io.ClassPathResource;
|
import org.springframework.stereotype.Service;
|
|
import javax.annotation.Resource;
|
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());
|
|
@Resource
|
XlsReportMapper mapper;
|
|
@Resource
|
CommonService commonService;
|
|
/**
|
* 报告路径
|
*/
|
@Value("${report.path}")
|
private String reportPath;
|
|
/**
|
* 预警值
|
*/
|
@Value("${report.yjz}")
|
private double yjz;
|
|
/**
|
* 报警值
|
*/
|
@Value("${report.bjz}")
|
private double bjz;
|
|
/**
|
* 获取导出路径
|
*/
|
private String getExpPath(String type) {
|
String path = reportPath + File.separator + type;
|
|
File f = new File(path);
|
if (!f.exists() || !f.isDirectory()) {
|
f.mkdirs();
|
}
|
|
return path;
|
}
|
|
/**
|
* 获取Excel模板
|
*/
|
private String getXslTemplate(String type) throws IOException {
|
ClassPathResource resource = new ClassPathResource(String.format("templates/%s.xlsx", type));
|
|
return resource.exists() ? resource.getFile().getPath() : null;
|
}
|
|
/**
|
* 创建Excel
|
*/
|
private <T> void createExcel(String source, String target, List<T> list) {
|
Map<String, List<T>> map = new HashMap<>();
|
map.put("data", list);
|
|
ExcelUtils.writeToTemplate(source, target, map);
|
}
|
|
/**
|
* 创建Excel
|
*/
|
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);
|
|
File f = new File(target);
|
if (!f.exists() || f.isDirectory()) {
|
createExcel(source, target, list);
|
}
|
|
return String.format("%s\\%s.xlsx", type, strData);
|
}
|
|
/**
|
* 保存结果
|
*/
|
private <T> void saveResult(String type, Date date, List<T> list) throws Exception {
|
date = DateUtils.trimTime(date);
|
String filePath = createExcel(type, date, list);
|
String name = filePath.replace(type + "\\", "");
|
|
int rows = mapper.reportExists(type, name);
|
if (rows > 0) return;
|
|
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) + "%,X:" + su.getX1() + ",Y:" + su.getY1());
|
if (null != su.getAddr2())
|
sub.add(su.getAddr2() + ",概率:" + (su.getOdds2() * 100) + "%,X:" + su.getX2() + ",Y:" + su.getY2());
|
if (null != su.getAddr3())
|
sub.add(su.getAddr3() + ",概率:" + (su.getOdds3() * 100) + "%,X:" + su.getX3() + ",Y:" + su.getY3());
|
|
list.add(String.join(";", sub));
|
}
|
|
return String.join("\n", list);
|
}
|
|
/**
|
* 创建日报:new 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); // 2023-07-24
|
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 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(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");
|
List<SuYuan700> suList = mapper.selectSuYuanByStartAndEnd(id, name, yjz, dayStart, end);
|
String ys = getYs(suList); // 受影响因素及原因
|
|
list.add(new DayExcel("" + rjz, "" + lj, ljtb, ys, "", "", ""));
|
}
|
saveResult("day", yesterday, list);
|
} catch (Exception ex) {
|
logger.error(ex.getMessage(), ex);
|
}
|
}
|
|
/**
|
* 创建周报:new WeekExcel("AI-" + (i < 10 ? "0" : "") + i, "zhb", "ztq", "ztb", "zhb2", "lj", "ljtb", "syn", "ys", "fs", "fx", "°c");
|
*/
|
public void createWeekReport(Date date) {
|
try {
|
Date mon = DateUtils.getAPeriodOfTime(date, -7, Calendar.DATE); // 2023-07-24
|
Date sun = DateUtils.getAPeriodOfTime(date, -1, Calendar.DATE); // 2023-07-30
|
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++) {
|
String name = "AI-" + (i < 10 ? "0" : "") + i;
|
double sz = mapper.selectAccumulate(intMon, intSun, name); // 上周
|
|
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("week", sun, list);
|
} catch (Exception ex) {
|
logger.error(ex.getMessage(), ex);
|
}
|
}
|
|
/**
|
* 创建月报:new 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); // 2023-07-31
|
Date monthStart = DateUtils.getMonthStart(yesterday); // 2023-07-01
|
Date monthEnd = DateUtils.getMonthEnd(yesterday); // 2023-07-31
|
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++) {
|
String name = "AI-" + (i < 10 ? "0" : "") + i;
|
double sy = mapper.selectAccumulate(intMonthStart, intMonthEnd, name); // 上月
|
|
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 = ""; // 月同比
|
|
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("month", end, list);
|
} catch (Exception ex) {
|
logger.error(ex.getMessage(), ex);
|
}
|
}
|
}
|