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 void createExcel(String source, String target, List list) { Map> map = new HashMap<>(); map.put("data", list); ExcelUtils.writeToTemplate(source, target, map); } /** * 创建Excel */ private String createExcel(String type, Date date, List 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 void saveResult(String type, Date date, List 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 suList) { if (null == suList || 0 == suList.size()) return ""; List list = new ArrayList<>(); for (SuYuan700 su : suList) { List 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); 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 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); // 去年累计均值 double ljtb = CalculateUtils.round2((ljjz - qnLjjz) / qnLjjz * 100); // 累计同比 MonitorPointPosition point = commonService.select3dCheckPointByName(name); String id = point.getId().substring(0, point.getId().lastIndexOf("_") + 1) + "0"; // List suList = mapper.selectSuYuanByTime(id, yyyy_mm_dd + " 00:00:00", yyyy_mm_dd + " 23:00:00"); List 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); Date sun = DateUtils.getAPeriodOfTime(date, -1, Calendar.DATE); Date lastMon = DateUtils.getAPeriodOfTime(date, -14, Calendar.DATE); Date lastSun = DateUtils.getAPeriodOfTime(date, -8, Calendar.DATE); Date lastYearMon = DateUtils.getAPeriodOfTime(mon, -1, Calendar.YEAR); Date lastYearSun = DateUtils.getAPeriodOfTime(sun, -1, Calendar.YEAR); 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 intLastYearMon = Integer.parseInt(DateUtils.parseDateToStr(DateUtils.YYYYMMDD, lastYearMon) + "00"); // 2022071700 Integer intLastYearSun = Integer.parseInt(DateUtils.parseDateToStr(DateUtils.YYYYMMDD, lastYearSun) + "23"); // 2022072323 String yyyy = DateUtils.parseDateToStr(DateUtils.YYYY, sun); // 2023 Integer yearStart = Integer.parseInt(yyyy + "010100"); // 2023010100 String lastYear = DateUtils.parseDateToStr(DateUtils.YYYY, DateUtils.lastYear(sun)); // 2022 List 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); // 上上周 double zhb = CalculateUtils.round2((sz - ssz) / ssz * 100); // 周环比 double qntq = mapper.selectAccumulate(intLastYearMon, intLastYearSun, name); // 去年同期 double ztb = CalculateUtils.round2((sz - qntq) / qntq * 100); // 周同比 double lj = mapper.selectAccumulate(yearStart, intSun, name); // 累计 double lastLj = mapper.selectYearAccumulate(lastYear + "%", name); // 去年累计 double ljtb = CalculateUtils.round2((lj - lastLj) / lastLj * 100); // 累计同比 double syn = CalculateUtils.round2((lj - lastLj) / lastLj * 100); // 较上一年度变化幅度 MonitorPointPosition point = commonService.select3dCheckPointByName(name); String id = point.getId().substring(0, point.getId().lastIndexOf("_") + 1) + "0"; List suList = mapper.selectSuYuanByStartAndEnd(id, name, yjz, intMon, intSun); String ys = getYs(suList); list.add(new WeekExcel("" + sz, zhb + "%", "", ztb + "%", "" + lj, "" + ljtb, "", 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); Date start = DateUtils.getMonthStart(yesterday); Date end = DateUtils.getMonthEnd(yesterday); List list = new ArrayList<>(); for (int i = 1; i < 47; i++) { String name = "AI-" + (i < 10 ? "0" : "") + i; // list.add(new MonthExcel()); } saveResult("month", end, list); } catch (Exception ex) { logger.error(ex.getMessage(), ex); } } }