package com.trafficaudit.reportexport.calc;
|
|
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
|
import com.trafficaudit.common.util.RegionUtil;
|
import com.trafficaudit.dataimport.entity.CityBusMonthly;
|
import com.trafficaudit.dataimport.entity.CityTaxiMonthly;
|
import com.trafficaudit.dataimport.entity.FreightTurnoverImport;
|
import com.trafficaudit.dataimport.entity.H2032EnterpriseMonthly;
|
import com.trafficaudit.dataimport.entity.PassengerEnterpriseMonthly;
|
import com.trafficaudit.dataimport.entity.ScaleSplitTransport;
|
import com.trafficaudit.dataimport.entity.WycOrderMonthly;
|
import com.trafficaudit.dataimport.entity.WycTotalMonthly;
|
import com.trafficaudit.dataimport.mapper.CityBusMonthlyMapper;
|
import com.trafficaudit.dataimport.mapper.CityTaxiMonthlyMapper;
|
import com.trafficaudit.dataimport.mapper.FreightTurnoverImportMapper;
|
import com.trafficaudit.dataimport.mapper.H2032EnterpriseMonthlyMapper;
|
import com.trafficaudit.dataimport.mapper.PassengerEnterpriseMonthlyMapper;
|
import com.trafficaudit.dataimport.mapper.ScaleSplitTransportMapper;
|
import com.trafficaudit.dataimport.mapper.WycOrderMonthlyMapper;
|
import com.trafficaudit.dataimport.mapper.WycTotalMonthlyMapper;
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.poi.openxml4j.util.ZipSecureFile;
|
import org.apache.poi.ss.usermodel.Cell;
|
import org.apache.poi.ss.usermodel.CellType;
|
import org.apache.poi.ss.usermodel.Row;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.ss.usermodel.Workbook;
|
import org.apache.poi.ss.usermodel.WorkbookFactory;
|
import org.springframework.beans.factory.annotation.Value;
|
import org.springframework.stereotype.Service;
|
|
import javax.annotation.Resource;
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.io.InputStream;
|
import java.util.ArrayList;
|
import java.util.LinkedHashMap;
|
import java.util.LinkedHashSet;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.Set;
|
|
/**
|
* 汇总大表「缺数清单」（只读，2026-10-12 新增，用户要求）。
|
*
|
* <p>与《货运基数体检》{@link FreightReadinessCheck} 互补、必须分开：
|
* 那个查「不可能的数」（倒减为负、Σ市州≠省行…），本类查「该有的数没有」。
|
* 2026-08 体检 0 命中、但 2026-09 出表时 8 月列仍为空 —— 就是只有前者、没有后者的后果。
|
*
|
* <p>检查项：
|
* <ol>
|
* <li>程序逐月从库回填的页（班线包车、公交、出租、轨道轮渡）：1..目标月 逐月查来源是否有数、17 市州是否齐全；</li>
|
* <li>程序只回填目标月的页（货运、网约车）：目标月三项来源是否齐全；</li>
|
* <li>上述两页的 1..目标月-1 由母版承担：母版该月值列全空则出表即缺（库里有数、程序不回填）。</li>
|
* </ol>
|
*
|
* <p>本类只读、不抛异常（异常转成清单文字），供导出日志 / 弹窗 / 生成件《数据缺失清单》页使用。
|
*/
|
@Slf4j
|
@Service
|
public class SummaryMissingDataCheck {
|
|
@Resource
|
private PassengerEnterpriseMonthlyMapper h2031Mapper; // 班线包车（公路旅客企业月报）
|
@Resource
|
private CityBusMonthlyMapper cityBusMapper; // 公交 + 轨道/轮渡
|
@Resource
|
private CityTaxiMonthlyMapper cityTaxiMapper; // 出租（网约车拆分也用它的平均运距）
|
@Resource
|
private H2032EnterpriseMonthlyMapper h2032Mapper; // 货运规上（①）
|
@Resource
|
private FreightTurnoverImportMapper freightTurnoverMapper; // 货运全口径（②）
|
@Resource
|
private ScaleSplitTransportMapper scaleSplitMapper; // 规上规下拆分（③）
|
@Resource
|
private WycOrderMonthlyMapper wycOrderMapper; // 网约车订单 + 全省 pin
|
@Resource
|
private WycTotalMonthlyMapper wycTotalMapper; // 网约车 pin 专表（兜底）
|
|
@Value("${summary.template-dir:docs/生成汇总大表}")
|
private String summaryTemplateDir;
|
|
/** 程序只回填「目标月」的页 —— 更早月份由母版承担 */
|
private static final String[] TARGET_ONLY_PAGES = {" 货运", "网约车"};
|
|
public List<String> check(String period) {
|
List<String> issues = new ArrayList<>();
|
if (period == null || period.length() < 7) return issues;
|
int year;
|
int month;
|
try {
|
year = Integer.parseInt(period.substring(0, 4));
|
month = Integer.parseInt(period.substring(5, 7));
|
} catch (Exception e) {
|
return issues;
|
}
|
// 1) 逐月回填页：1..目标月
|
for (int m = 1; m <= month; m++) {
|
String p = String.format("%04d-%02d", year, m);
|
checkH2031Month(p, issues);
|
checkCityBusMonth(p, issues);
|
checkRailFerryMonth(p, issues);
|
checkCityTaxiMonth(p, issues);
|
}
|
// 2) 只回填目标月的两页：目标月来源
|
String target = String.format("%04d-%02d", year, month);
|
checkFreightTarget(target, issues);
|
checkWycTarget(target, issues);
|
// 3) 这两页的历史月：母版是否为空
|
checkMotherHistory(target, year, month, issues);
|
if (!issues.isEmpty()) log.warn("缺数清单（{}）：命中 {} 条", period, issues.size());
|
return issues;
|
}
|
|
// ---------- 各页检查 ----------
|
|
private void checkH2031Month(String p, List<String> issues) {
|
try {
|
List<PassengerEnterpriseMonthly> rows = h2031Mapper.selectList(
|
new LambdaQueryWrapper<PassengerEnterpriseMonthly>().eq(PassengerEnterpriseMonthly::getReportPeriod, p));
|
if (rows.isEmpty()) {
|
issues.add(String.format("缺数：班线包车 %s 无《公路旅客企业月报》来源 → 该月客运量/周转量、中口径「公路班线」将为空", p));
|
return;
|
}
|
Set<String> got = new LinkedHashSet<>();
|
for (PassengerEnterpriseMonthly r : rows) {
|
String c = RegionUtil.cityByCode(r.getRegionCode());
|
if (c != null) got.add(c);
|
}
|
addMissingCities("班线包车", p, got, issues);
|
} catch (Exception e) {
|
issues.add("缺数检查异常：班线包车 " + p + " :: " + e.getMessage());
|
}
|
}
|
|
private void checkCityBusMonth(String p, List<String> issues) {
|
try {
|
List<CityBusMonthly> rows = cityBusMapper.selectList(
|
new LambdaQueryWrapper<CityBusMonthly>().eq(CityBusMonthly::getReportPeriod, p));
|
if (rows.isEmpty()) {
|
issues.add(String.format("缺数：公交 %s 无《城市公交月报》来源 → 公交页为空、城市客运页将缺公交部分", p));
|
return;
|
}
|
addMissingCities("公交", p, citySetOf(rows), issues);
|
} catch (Exception e) {
|
issues.add("缺数检查异常：公交 " + p + " :: " + e.getMessage());
|
}
|
}
|
|
private void checkRailFerryMonth(String p, List<String> issues) {
|
try {
|
List<CityBusMonthly> rows = cityBusMapper.selectList(
|
new LambdaQueryWrapper<CityBusMonthly>().eq(CityBusMonthly::getReportPeriod, p));
|
if (rows.isEmpty()) {
|
issues.add(String.format("缺数：轨道、轮渡 %s 无《城市公交月报》来源（轨道/轮渡同表）→ 该页为空", p));
|
return;
|
}
|
boolean rail = false;
|
boolean ferry = false;
|
for (CityBusMonthly r : rows) {
|
if (r.getRailPassengerVolume() != null) rail = true;
|
if (r.getFerryPassengerVolume() != null) ferry = true;
|
}
|
List<String> miss = new ArrayList<>();
|
if (!rail) miss.add("轨道");
|
if (!ferry) miss.add("轮渡");
|
if (!miss.isEmpty()) {
|
issues.add(String.format("缺数：轨道、轮渡 %s 的 %s 字段全空 → 该块将为空",
|
p, String.join("、", miss)));
|
}
|
} catch (Exception e) {
|
issues.add("缺数检查异常：轨道、轮渡 " + p + " :: " + e.getMessage());
|
}
|
}
|
|
private void checkCityTaxiMonth(String p, List<String> issues) {
|
try {
|
List<CityTaxiMonthly> rows = cityTaxiMapper.selectList(
|
new LambdaQueryWrapper<CityTaxiMonthly>().eq(CityTaxiMonthly::getReportPeriod, p));
|
if (rows.isEmpty()) {
|
issues.add(String.format("缺数：出租 %s 无《巡游出租汽车月报》来源 → 出租页为空，且网约车拆分取不到平均运距", p));
|
return;
|
}
|
Set<String> got = new LinkedHashSet<>();
|
for (CityTaxiMonthly r : rows) {
|
String c = RegionUtil.normalizeCityName(r.getCity());
|
if (c != null && !c.isEmpty()) got.add(c);
|
}
|
addMissingCities("出租", p, got, issues);
|
} catch (Exception e) {
|
issues.add("缺数检查异常：出租 " + p + " :: " + e.getMessage());
|
}
|
}
|
|
private void checkFreightTarget(String p, List<String> issues) {
|
try {
|
// ① H203-2 企业月报（规上货运量/周转量）
|
List<H2032EnterpriseMonthly> h = h2032Mapper.selectList(
|
new LambdaQueryWrapper<H2032EnterpriseMonthly>().eq(H2032EnterpriseMonthly::getReportPeriod, p));
|
if (h.isEmpty()) {
|
issues.add(String.format("缺数：货运 %s 无①《H203-2 道路货物运输月度生产情况》→ 规上货运量/周转量取不到", p));
|
} else {
|
Set<String> got = new LinkedHashSet<>();
|
for (H2032EnterpriseMonthly r : h) {
|
String c = RegionUtil.cityByCode(r.getRegionCode());
|
if (c != null) got.add(c);
|
}
|
addMissingCities("货运①规上", p, got, issues);
|
}
|
// ② 货运量周转量表（合计 = 规上+规下）
|
List<FreightTurnoverImport> f = freightTurnoverMapper.selectList(
|
new LambdaQueryWrapper<FreightTurnoverImport>().eq(FreightTurnoverImport::getReportPeriod, p));
|
if (f.isEmpty()) {
|
issues.add(String.format("缺数：货运 %s 无②《货运量周转量表》→ 合计货运量/周转量、规下（倒减）全部取不到", p));
|
} else {
|
Set<String> got = new LinkedHashSet<>();
|
for (FreightTurnoverImport r : f) {
|
String c = RegionUtil.normalizeCityName(r.getRegionName());
|
if (c != null && !RegionUtil.cityList().contains(c) && !"湖北省".equals(c)) continue;
|
if (c != null && RegionUtil.cityList().contains(c)) got.add(c);
|
}
|
addMissingCities("货运②全口径", p, got, issues);
|
}
|
// ③ 拆分表（规上/合计周转量），当月 + 累计
|
List<ScaleSplitTransport> s = scaleSplitMapper.selectList(
|
new LambdaQueryWrapper<ScaleSplitTransport>().eq(ScaleSplitTransport::getReportPeriod, p));
|
if (s.isEmpty()) {
|
issues.add(String.format("缺数：货运 %s 无③《规上规下拆分表》→ 规上/合计周转量取不到（该月周转量将为空/0）", p));
|
} else {
|
Set<String> month = new LinkedHashSet<>();
|
Set<String> cum = new LinkedHashSet<>();
|
for (ScaleSplitTransport r : s) {
|
String c = RegionUtil.normalizeCityName(r.getRegionName());
|
if (c == null || !RegionUtil.cityList().contains(c)) continue;
|
if ("MONTH".equals(r.getPeriodType())) month.add(c);
|
else if ("CUMULATIVE".equals(r.getPeriodType())) cum.add(c);
|
}
|
addMissingCities("货运③拆分(当月)", p, month, issues);
|
addMissingCities("货运③拆分(累计)", p, cum, issues);
|
}
|
} catch (Exception e) {
|
issues.add("缺数检查异常：货运 " + p + " :: " + e.getMessage());
|
}
|
}
|
|
private void checkWycTarget(String p, List<String> issues) {
|
try {
|
List<WycOrderMonthly> orders = wycOrderMapper.selectList(
|
new LambdaQueryWrapper<WycOrderMonthly>().eq(WycOrderMonthly::getReportPeriod, p));
|
boolean okOrders = false;
|
boolean okPin = false;
|
if (!orders.isEmpty()) {
|
WycOrderMonthly o = orders.get(0);
|
double sum = nz(o.getOrderWuhan()) + nz(o.getOrderHuangshi()) + nz(o.getOrderShiyan()) + nz(o.getOrderYichang())
|
+ nz(o.getOrderXiangyang()) + nz(o.getOrderEzhou()) + nz(o.getOrderJingmen()) + nz(o.getOrderXiaogan())
|
+ nz(o.getOrderJingzhou()) + nz(o.getOrderHuanggang()) + nz(o.getOrderXianning()) + nz(o.getOrderSuizhou())
|
+ nz(o.getOrderEnshi()) + nz(o.getOrderXiantao()) + nz(o.getOrderQianjiang()) + nz(o.getOrderTianmen())
|
+ nz(o.getOrderShennong());
|
okOrders = sum > 0;
|
okPin = nz(o.getPinK()) > 0 && nz(o.getPinCK()) > 0 && nz(o.getPinZ()) > 0 && nz(o.getPinCZ()) > 0;
|
}
|
if (!okOrders) {
|
issues.add(String.format("缺数：网约车 %s 无订单数据（wyc_order_monthly 17 市州订单）→ 拆分无法计算，该页将为空", p));
|
}
|
if (!okPin) {
|
List<WycTotalMonthly> tot = wycTotalMapper.selectList(
|
new LambdaQueryWrapper<WycTotalMonthly>().eq(WycTotalMonthly::getReportPeriod, p));
|
boolean okTot = !tot.isEmpty()
|
&& nz(tot.get(0).getPinK()) > 0 && nz(tot.get(0).getPinCK()) > 0
|
&& nz(tot.get(0).getPinZ()) > 0 && nz(tot.get(0).getPinCZ()) > 0;
|
if (!okTot) {
|
issues.add(String.format("缺数：网约车 %s 缺全省 pin（order 表与 wyc_total_monthly 都没有）→ 拆分无法计算", p));
|
}
|
}
|
List<CityTaxiMonthly> taxi = cityTaxiMapper.selectList(
|
new LambdaQueryWrapper<CityTaxiMonthly>().eq(CityTaxiMonthly::getReportPeriod, p));
|
Set<String> got = new LinkedHashSet<>();
|
for (CityTaxiMonthly r : taxi) {
|
String c = RegionUtil.normalizeCityName(r.getCity());
|
if (c != null && !c.isEmpty()) got.add(c);
|
}
|
addMissingCities("网约车(依赖出租平均运距)", p, got, issues);
|
} catch (Exception e) {
|
issues.add("缺数检查异常：网约车 " + p + " :: " + e.getMessage());
|
}
|
}
|
|
/** 货运/网约车的历史月（1..M-1）由母版承担（母版没有时按「同期备份母版」还原）：
|
* 只有母版与备份母版都没有数，才算真正的出表缺口（否则会误报 7 月那种「骨架母版 + donor 还原」的情况）。 */
|
private void checkMotherHistory(String period, int year, int month, List<String> issues) {
|
File mother = resolveMother(year, month);
|
if (mother == null) {
|
issues.add(String.format("缺数：未找到母版《%d年%d月道路运输量汇总表.xlsx》（%s）", year, month, summaryTemplateDir));
|
return;
|
}
|
File donor = resolveDonor(year, month);
|
double saved = ZipSecureFile.getMinInflateRatio();
|
ZipSecureFile.setMinInflateRatio(0.0001);
|
try (InputStream in = new FileInputStream(mother);
|
Workbook wb = WorkbookFactory.create(in);
|
InputStream in2 = (donor != null) ? new FileInputStream(donor) : null;
|
Workbook dwb = (in2 != null) ? WorkbookFactory.create(in2) : null) {
|
for (String pageName : TARGET_ONLY_PAGES) {
|
List<String> blankMonths = new ArrayList<>();
|
for (int m = 1; m < month; m++) {
|
if (monthHasData(wb, pageName, year, m)) continue;
|
if (dwb != null && monthHasData(dwb, pageName, year, m)) continue;
|
blankMonths.add(String.format("%d年%d月", year, m));
|
}
|
if (!blankMonths.isEmpty()) {
|
issues.add(String.format("缺数：%s 母版（%s 期）的 %s 值列全空%s，而程序只回填目标月 → 出表该月将为空（库里可能有数据）",
|
pageName.trim(), period, String.join("、", blankMonths),
|
donor == null ? "" : "（同期备份母版也没有）"));
|
}
|
}
|
} catch (Exception e) {
|
issues.add("缺数检查异常：读母版 " + mother.getName() + " :: " + e.getMessage());
|
} finally {
|
ZipSecureFile.setMinInflateRatio(saved);
|
}
|
}
|
|
/** 该工作簿该页、该年该月的「值列」是否已有数值（表头行之外） */
|
private boolean monthHasData(Workbook wb, String pageName, int year, int m) {
|
Sheet sheet = findSheet(wb, pageName);
|
if (sheet == null) return false;
|
int[] ref = detectYearMonthCols(sheet, year).get(m);
|
if (ref == null) return false;
|
return countNumericInColumn(sheet, ref[1], ref[0]) > 0;
|
}
|
|
private Sheet findSheet(Workbook wb, String pageName) {
|
Sheet s = wb.getSheet(pageName);
|
if (s == null && " 货运".equals(pageName)) s = wb.getSheet("货运");
|
return s;
|
}
|
|
/** 同期备份母版（清理前的完整月母版），命名 _备份_{年}年{月}月…道路运输量汇总表 */
|
private File resolveDonor(int year, int month) {
|
String rel = summaryTemplateDir;
|
while (rel.startsWith("./")) rel = rel.substring(2);
|
String[] roots = {summaryTemplateDir, System.getProperty("user.dir") + "/" + rel,
|
System.getProperty("user.dir") + "/../" + rel};
|
for (String root : roots) {
|
if (root == null || root.trim().isEmpty()) continue;
|
File dir = new File(root);
|
File[] files = dir.listFiles((d, name) -> name.startsWith("_备份_")
|
&& name.contains(year + "年" + month + "月") && name.contains("道路运输量汇总表"));
|
if (files != null && files.length > 0) return files[0];
|
}
|
return null;
|
}
|
|
// ---------- 工具 ----------
|
|
private Set<String> citySetOf(List<CityBusMonthly> rows) {
|
Set<String> got = new LinkedHashSet<>();
|
for (CityBusMonthly r : rows) {
|
String c = RegionUtil.normalizeCityName(r.getCity());
|
if (c != null && !c.isEmpty()) got.add(c);
|
}
|
return got;
|
}
|
|
private void addMissingCities(String page, String period, Set<String> got, List<String> issues) {
|
List<String> miss = new ArrayList<>();
|
for (String c : RegionUtil.cityList()) {
|
if (!got.contains(c)) miss.add(c);
|
}
|
if (!miss.isEmpty()) {
|
issues.add(String.format("缺数：%s %s 缺市州：%s", page, period, String.join("、", miss)));
|
}
|
}
|
|
/** 母版里定位目标年各月「值列」：返回 月 -> [表头行(0基), 列(0基)]；兼容文本「2026年M月」与日期序列号 */
|
private Map<Integer, int[]> detectYearMonthCols(Sheet sheet, int year) {
|
Map<Integer, int[]> out = new LinkedHashMap<>();
|
int lastRow = Math.min(5, sheet.getLastRowNum());
|
for (int r = 0; r <= lastRow; r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
for (Cell c : row) {
|
Integer m = monthOfCell(c, year);
|
if (m != null && !out.containsKey(m)) {
|
out.put(m, new int[]{r, c.getColumnIndex()});
|
}
|
}
|
}
|
return out;
|
}
|
|
private Integer monthOfCell(Cell c, int year) {
|
try {
|
if (c == null) return null;
|
if (c.getCellType() == CellType.STRING) {
|
String s = c.getStringCellValue();
|
if (s == null) return null;
|
java.util.regex.Matcher mt = java.util.regex.Pattern
|
.compile("(20\\d\\d)\\s*年\\s*(\\d{1,2})\\s*月").matcher(s);
|
if (mt.find()) {
|
if (Integer.parseInt(mt.group(1)) != year) return null;
|
int m = Integer.parseInt(mt.group(2));
|
return (m >= 1 && m <= 12) ? m : null;
|
}
|
return null;
|
}
|
if (c.getCellType() == CellType.NUMERIC) {
|
double v = c.getNumericCellValue();
|
if (v < 20000 || v > 60000 || v != Math.floor(v)) return null;
|
java.util.Date d = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(v);
|
java.util.Calendar cal = java.util.Calendar.getInstance();
|
cal.setTime(d);
|
if (cal.get(java.util.Calendar.YEAR) != year) return null;
|
return cal.get(java.util.Calendar.MONTH) + 1;
|
}
|
} catch (Exception ignore) {
|
// 非月份表头
|
}
|
return null;
|
}
|
|
/** 统计某列（跳过表头行）非空数值格数 */
|
private int countNumericInColumn(Sheet sheet, int col, int headerRow) {
|
int n = 0;
|
for (int r = 0; r <= sheet.getLastRowNum(); r++) {
|
if (r == headerRow) continue;
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
Cell c = row.getCell(col);
|
if (c == null) continue;
|
if (c.getCellType() == CellType.NUMERIC || c.getCellType() == CellType.FORMULA) n++;
|
}
|
return n;
|
}
|
|
private File resolveMother(int year, int month) {
|
String rel = summaryTemplateDir;
|
while (rel.startsWith("./")) rel = rel.substring(2);
|
String name = year + "年" + month + "月道路运输量汇总表.xlsx";
|
String[] roots = {summaryTemplateDir, System.getProperty("user.dir") + "/" + rel,
|
System.getProperty("user.dir") + "/../" + rel};
|
for (String root : roots) {
|
if (root == null || root.trim().isEmpty()) continue;
|
File f = new File(root, name);
|
if (f.exists() && f.isFile()) return f;
|
}
|
return null;
|
}
|
|
private double nz(Double v) {
|
return v == null ? 0.0 : v;
|
}
|
}
|
