package com.trafficaudit.dataimport.service;
|
|
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
|
import com.trafficaudit.auditengine.entity.AuditResult;
|
import com.trafficaudit.auditengine.entity.AuditRun;
|
import com.trafficaudit.auditengine.mapper.AuditResultMapper;
|
import com.trafficaudit.auditengine.mapper.AuditRunMapper;
|
import com.trafficaudit.common.util.RegionUtil;
|
import com.trafficaudit.dataimport.entity.EnergyAuthVehicle;
|
import com.trafficaudit.dataimport.entity.EnergyVehicleQuarterly;
|
import com.trafficaudit.dataimport.entity.FreightTurnoverImport;
|
import com.trafficaudit.dataimport.entity.H2032EnterpriseMonthly;
|
import com.trafficaudit.dataimport.entity.PassengerAuthVehicle;
|
import com.trafficaudit.dataimport.entity.PassengerEnterpriseMonthly;
|
import com.trafficaudit.dataimport.entity.PassengerIndividualMonthly;
|
import com.trafficaudit.dataimport.entity.ImportBatch;
|
import com.trafficaudit.dataimport.entity.InvestmentMonthly;
|
import com.trafficaudit.dataimport.entity.InvestmentProject;
|
import com.trafficaudit.dataimport.entity.InvestmentSystem;
|
import com.trafficaudit.dataimport.entity.CityBusMonthly;
|
import com.trafficaudit.dataimport.entity.CityTaxiMonthly;
|
import com.trafficaudit.dataimport.entity.CityTaxiAuth;
|
import com.trafficaudit.dataimport.entity.WycOrderMonthly;
|
import com.trafficaudit.dataimport.entity.WycTotalMonthly;
|
import com.trafficaudit.dataimport.entity.ScaleSplitTransport;
|
import com.trafficaudit.dataimport.entity.TransportAuthVehicle;
|
import com.trafficaudit.dataimport.entity.VehicleTrackMileage;
|
import com.trafficaudit.dataimport.mapper.EnergyAuthVehicleMapper;
|
import com.trafficaudit.dataimport.mapper.EnergyVehicleQuarterlyMapper;
|
import com.trafficaudit.dataimport.mapper.FreightTurnoverImportMapper;
|
import com.trafficaudit.dataimport.mapper.H2032EnterpriseMonthlyMapper;
|
import com.trafficaudit.dataimport.mapper.PassengerAuthVehicleMapper;
|
import com.trafficaudit.dataimport.mapper.PassengerEnterpriseMonthlyMapper;
|
import com.trafficaudit.dataimport.mapper.PassengerIndividualMonthlyMapper;
|
import com.trafficaudit.rulemanage.mapper.AuditRuleMapper;
|
import com.trafficaudit.dataimport.mapper.ImportBatchMapper;
|
import com.trafficaudit.dataimport.mapper.InvestmentMonthlyMapper;
|
import com.trafficaudit.dataimport.mapper.InvestmentProjectMapper;
|
import com.trafficaudit.dataimport.mapper.InvestmentSystemMapper;
|
import com.trafficaudit.dataimport.mapper.CityBusMonthlyMapper;
|
import com.trafficaudit.dataimport.mapper.CityTaxiMonthlyMapper;
|
import com.trafficaudit.dataimport.mapper.CityTaxiAuthMapper;
|
import com.trafficaudit.dataimport.mapper.WycOrderMonthlyMapper;
|
import com.trafficaudit.dataimport.mapper.WycTotalMonthlyMapper;
|
import com.trafficaudit.dataimport.mapper.ScaleSplitTransportMapper;
|
import com.trafficaudit.dataimport.mapper.TransportAuthVehicleMapper;
|
import com.trafficaudit.dataimport.mapper.VehicleTrackMileageMapper;
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.ibatis.session.ExecutorType;
|
import org.apache.ibatis.session.SqlSession;
|
import org.apache.ibatis.session.SqlSessionFactory;
|
import org.apache.poi.ss.usermodel.Cell;
|
import org.apache.poi.ss.usermodel.CellType;
|
import org.apache.poi.ss.usermodel.DataFormatter;
|
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.apache.poi.xwpf.usermodel.XWPFDocument;
|
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
|
import org.apache.poi.xwpf.usermodel.XWPFTable;
|
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
|
import org.w3c.dom.Document;
|
import org.w3c.dom.Element;
|
import org.w3c.dom.Node;
|
import org.w3c.dom.NodeList;
|
import cn.hutool.core.io.IoUtil;
|
import org.springframework.stereotype.Service;
|
import org.springframework.transaction.annotation.Transactional;
|
import org.springframework.web.multipart.MultipartFile;
|
|
import javax.annotation.Resource;
|
import java.io.ByteArrayInputStream;
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.io.FileOutputStream;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.HashSet;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.Set;
|
import java.util.concurrent.ConcurrentHashMap;
|
import com.trafficaudit.dataimport.dto.ImportResult;
|
import org.apache.poi.ss.usermodel.DateUtil;
|
import java.util.Comparator;
|
import java.util.Arrays;
|
import java.util.LinkedHashMap;
|
import java.util.LinkedHashSet;
|
import java.util.TreeMap;
|
import java.util.stream.Collectors;
|
|
import java.text.SimpleDateFormat;
|
|
/**
|
* 数据导入服务：支持4类模板导入
|
*/
|
@Slf4j
|
@Service
|
public class DataImportService {
|
|
private static final DataFormatter FORMATTER = new DataFormatter();
|
|
/** H204 能耗明细车辆类型码 -> 中文 */
|
private static final Map<String, String> ENERGY_VEHICLE_TYPE_NAMES = new HashMap<>();
|
|
/** H204 能耗明细燃料类型码 -> 中文 */
|
private static final Map<String, String> ENERGY_FUEL_TYPE_NAMES = new HashMap<>();
|
|
/** H204 燃料1计量单位码 -> 中文单位 */
|
private static final Map<String, String> ENERGY_FUEL_UNIT_NAMES = new HashMap<>();
|
|
/** 能耗导入按报表期互斥，防止同报表期并发重导互相覆盖 */
|
private final ConcurrentHashMap<String, Object> energyImportLocks = new ConcurrentHashMap<>();
|
|
static {
|
ENERGY_VEHICLE_TYPE_NAMES.put("01", "普通货车");
|
ENERGY_VEHICLE_TYPE_NAMES.put("02", "平板货车");
|
ENERGY_VEHICLE_TYPE_NAMES.put("03", "仓栅式货车");
|
ENERGY_VEHICLE_TYPE_NAMES.put("04", "厢式货车");
|
ENERGY_VEHICLE_TYPE_NAMES.put("06", "罐式货车");
|
ENERGY_VEHICLE_TYPE_NAMES.put("07", "特殊结构货车");
|
ENERGY_VEHICLE_TYPE_NAMES.put("08", "自卸货车");
|
ENERGY_VEHICLE_TYPE_NAMES.put("09", "车辆运输车");
|
ENERGY_VEHICLE_TYPE_NAMES.put("11", "重型半挂牵引车");
|
|
ENERGY_FUEL_TYPE_NAMES.put("01", "汽油");
|
ENERGY_FUEL_TYPE_NAMES.put("02", "柴油");
|
ENERGY_FUEL_TYPE_NAMES.put("03", "压缩天然气");
|
ENERGY_FUEL_TYPE_NAMES.put("04", "液化天然气");
|
ENERGY_FUEL_TYPE_NAMES.put("07", "电动");
|
ENERGY_FUEL_TYPE_NAMES.put("08", "氢气");
|
|
ENERGY_FUEL_UNIT_NAMES.put("1", "升");
|
ENERGY_FUEL_UNIT_NAMES.put("2", "升");
|
ENERGY_FUEL_UNIT_NAMES.put("4", "千克");
|
ENERGY_FUEL_UNIT_NAMES.put("5", "千克");
|
ENERGY_FUEL_UNIT_NAMES.put("7", "千瓦时");
|
}
|
|
@Resource
|
private H2032EnterpriseMonthlyMapper h2032Mapper;
|
@Resource
|
private TransportAuthVehicleMapper transportAuthMapper;
|
@Resource
|
private VehicleTrackMileageMapper trackMileageMapper;
|
@Resource
|
private ScaleSplitTransportMapper scaleSplitMapper;
|
@Resource
|
private ImportBatchMapper importBatchMapper;
|
@Resource
|
private FreightTurnoverImportMapper freightTurnoverMapper;
|
@Resource
|
private AuditResultMapper auditResultMapper;
|
@Resource
|
private AuditRunMapper auditRunMapper;
|
@Resource
|
private PassengerEnterpriseMonthlyMapper passengerMapper;
|
@Resource
|
private PassengerIndividualMonthlyMapper passengerIndividualMapper;
|
@Resource
|
private PassengerAuthVehicleMapper passengerAuthMapper;
|
@Resource
|
private AuditRuleMapper ruleMapper;
|
@Resource
|
private EnergyVehicleQuarterlyMapper energyMapper;
|
@Resource
|
private EnergyAuthVehicleMapper energyAuthMapper;
|
@Resource
|
private InvestmentProjectMapper investProjectMapper;
|
@Resource
|
private InvestmentMonthlyMapper investMonthlyMapper;
|
@Resource
|
private InvestmentSystemMapper investSystemMapper;
|
@Resource
|
private CityBusMonthlyMapper cityBusMapper;
|
@Resource
|
private CityTaxiMonthlyMapper cityTaxiMapper;
|
@Resource
|
private CityTaxiAuthMapper cityTaxiAuthMapper;
|
@Resource
|
private WycTotalMonthlyMapper wycTotalMapper;
|
@Resource
|
private WycOrderMonthlyMapper wycOrderMapper;
|
@Resource
|
private SqlSessionFactory sqlSessionFactory;
|
|
// ========== H2032 月报 ==========
|
|
public ImportResult importH2032(MultipartFile file, String period) throws Exception {
|
List<H2032Row> rows = new ArrayList<>();
|
Map<String, Integer> colMap;
|
try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
|
Sheet sheet = wb.getSheetAt(0);
|
requireHeaderCols(sheet, "道路货物运输月度生产情况", "企业名称", "货运量_总计");
|
colMap = buildHeaderMap(sheet.getRow(0));
|
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String name = gs(row, colMap, "企业名称");
|
if (name == null || name.trim().isEmpty()) continue;
|
H2032EnterpriseMonthly entity = new H2032EnterpriseMonthly();
|
entity.setReportPeriod(period);
|
entity.setRegionCode(gs(row, colMap, "所属地区"));
|
entity.setEnterpriseCode(gs(row, colMap, "企业代码"));
|
entity.setEnterpriseName(name);
|
entity.setUnifiedCreditCode(gs(row, colMap, "统一社会信用代码"));
|
entity.setReportUnit(gs(row, colMap, "填报单位"));
|
entity.setVehicleTotal(gi(row, colMap, "车辆数_总计"));
|
entity.setTonsTotal(gd(row, colMap, "标记吨位数_总计"));
|
entity.setVehicleTractor(gi(row, colMap, "车辆数_牵引车"));
|
entity.setVehicleTrailer(gi(row, colMap, "车辆数_挂车"));
|
entity.setTonsTrailer(gd(row, colMap, "标记吨位数_挂车"));
|
entity.setVehicleContainer(gi(row, colMap, "车辆数_集装箱车"));
|
entity.setTonsContainer(gd(row, colMap, "标记吨位数_集装箱车"));
|
entity.setFreightTotal(gd(row, colMap, "货运量_总计"));
|
entity.setTurnoverTotal(gd(row, colMap, "货物周转量_总计"));
|
entity.setFreightContainer(gd(row, colMap, "货运量_集装箱"));
|
entity.setFreightCoal(gd(row, colMap, "货运量_煤炭及制品"));
|
entity.setFreightOilGas(gd(row, colMap, "货运量_石油、天然气及制品（吨）"));
|
entity.setFreightCrudeOil(gd(row, colMap, "货运量_其中：原油（吨）"));
|
entity.setFreightMetalOre(gd(row, colMap, "货运量_金属矿石"));
|
entity.setFreightIronOre(gd(row, colMap, "货运量_其中：铁矿石（吨）"));
|
entity.setFreightBuilding(gd(row, colMap, "货运量_矿物性建筑材料（吨）"));
|
entity.setFreightGrain(gd(row, colMap, "货运量_粮食（吨）"));
|
entity.setUnitLeader(gs(row, colMap, "单位负责人"));
|
entity.setStatsLeader(gs(row, colMap, "统计负责人"));
|
entity.setContactPerson(gs(row, colMap, "填表人"));
|
entity.setContactPhone(gs(row, colMap, "联系电话"));
|
entity.setReportDate(gds(row, colMap, "报出日期"));
|
entity.setAvgTonnage(gd(row, colMap, "货车平均吨位"));
|
entity.setAvgTonnageTrailer(gd(row, colMap, "平均吨位_挂车"));
|
entity.setAvgTonnageWhole(gd(row, colMap, "平均吨位_整车"));
|
entity.setAvgDistance(gd(row, colMap, "货车平均运距"));
|
entity.setDailyTripsPerTon(gd(row, colMap, "单吨日均载运次数"));
|
entity.setDailyMileagePerVehicle(gd(row, colMap, "单车单天行驶里程"));
|
entity.setDailyVolumePerVehicle(gd(row, colMap, "单车日均运量"));
|
entity.setDailyTripsWithTractor(gd(row, colMap, "单吨日均载运次数（结合牵引车）"));
|
entity.setVehicleMomGrowth(gd(row, colMap, "车辆数-总计环比增速"));
|
entity.setTonsMomGrowth(gd(row, colMap, "标记吨位-总计环比增速"));
|
entity.setFreightMomGrowth(gd(row, colMap, "货运量-总计环比增速"));
|
entity.setTurnoverMomGrowth(gd(row, colMap, "货物周转量-总计环比增速"));
|
entity.setAvgDistanceMomGrowth(gd(row, colMap, "货车平均运距-环比增速"));
|
entity.setVehicleYoyGrowth(gd(row, colMap, "车辆数-总计同比增速"));
|
entity.setTonsYoyGrowth(gd(row, colMap, "标记吨位-总计同比增速"));
|
entity.setTractorTrailerRatio(gd(row, colMap, "牵引车挂车比"));
|
entity.setFreightYoyGrowth(gd(row, colMap, "货运量-总计同比增速"));
|
entity.setTurnoverYoyGrowth(gd(row, colMap, "货物周转量-总计同比增速"));
|
entity.setAvgDistanceYoyGrowth(gd(row, colMap, "货车平均运距-同比增速"));
|
entity.setVehicleWhole(gi(row, colMap, "车辆数_整车"));
|
entity.setTonsWhole(gd(row, colMap, "标记吨位数_整车"));
|
entity.setVerifyExplanation(gs(row, colMap, "核实性原因解释"));
|
entity.setReportNotes(gs(row, colMap, "上报说明"));
|
entity.setModifyRecord(gs(row, colMap, "修改记录"));
|
rows.add(new H2032Row(r + 1, entity));
|
}
|
} catch (Exception e) {
|
log.error("H2032 parse error", e);
|
throw e;
|
}
|
|
// 幂等：同报表期重新导入，先删除旧数据（只清 H2032 规则的审核结果）
|
h2032Mapper.delete(new LambdaQueryWrapper<H2032EnterpriseMonthly>()
|
.eq(H2032EnterpriseMonthly::getReportPeriod, period));
|
List<Long> h2032RuleIds = new ArrayList<>();
|
for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {
|
if ("H2032".equals(rule.getReportType())) h2032RuleIds.add(rule.getId());
|
}
|
if (!h2032RuleIds.isEmpty()) {
|
auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
|
.eq(AuditResult::getReportPeriod, period)
|
.in(AuditResult::getRuleId, h2032RuleIds));
|
}
|
|
int success = 0;
|
List<String> failDetails = new ArrayList<>();
|
for (H2032Row item : rows) {
|
try {
|
h2032Mapper.insert(item.entity);
|
success++;
|
} catch (Exception e) {
|
Throwable cause = e;
|
while (cause.getCause() != null) {
|
cause = cause.getCause();
|
}
|
String reason = cause.getMessage();
|
if (reason == null || reason.trim().isEmpty()) {
|
reason = e.getMessage();
|
}
|
failDetails.add("第 " + item.excelRow + " 行（" + item.entity.getEnterpriseName() + "）：" + reason);
|
log.error("H2032 insert error: row {}, {}", item.excelRow, item.entity.getEnterpriseName(), e);
|
}
|
}
|
String errorDetail = String.join("\n", failDetails);
|
if (errorDetail.length() > 4000) {
|
errorDetail = errorDetail.substring(0, 4000) + "\n……";
|
}
|
recordBatch(file.getOriginalFilename(), "H2032", period, rows.size(), success, failDetails.size(), errorDetail);
|
log.info("H2032 imported: {} rows for {} (fail {})", success, period, failDetails.size());
|
return new ImportResult(success, failDetails.size(), failDetails);
|
}
|
|
|
// ========== H203-1 公路旅客月报（含运政车辆数sheet） ==========
|
|
public ImportResult importPassengerMonthly(MultipartFile file, String period) throws Exception {
|
List<PassengerRow> rows = new ArrayList<>();
|
List<PassengerAuthVehicle> authList = new ArrayList<>();
|
try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
|
// sheet0: 公路旅客6月报
|
Sheet sheet = wb.getSheetAt(0);
|
requireHeaderCols(sheet, "公路旅客运输月度生产情况", "企业名称", "客运量_总计");
|
Map<String, Integer> colMap = buildHeaderMap(sheet.getRow(0));
|
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String name = gs(row, colMap, "企业名称");
|
if (name == null || name.trim().isEmpty()) continue;
|
PassengerEnterpriseMonthly entity = new PassengerEnterpriseMonthly();
|
entity.setReportPeriod(period);
|
entity.setRegionCode(gs(row, colMap, "所属地区"));
|
entity.setEnterpriseCode(gs(row, colMap, "企业代码"));
|
entity.setEnterpriseName(name.trim());
|
// 新版模板在「企业名称」后新增了两列（统一社会信用代码/填报单位），按表头取值，老模板无该列时为 null
|
entity.setUnifiedCreditCode(gs(row, colMap, "统一社会信用代码"));
|
entity.setReportUnit(gs(row, colMap, "填报单位"));
|
entity.setVehicleTotal(gi(row, colMap, "客运车辆_总计"));
|
entity.setSeatTotal(gi(row, colMap, "核定载客位_总计"));
|
entity.setVehicleSchedule(gi(row, colMap, "车辆数_客运班车（含定线旅游客运客车）"));
|
entity.setSeatSchedule(gi(row, colMap, "核定载客位_客运班车（含定线旅游客运客车）"));
|
entity.setVehicleCharter(gi(row, colMap, "车辆数_客运包车（含非定线旅游客运客车）"));
|
entity.setSeatCharter(gi(row, colMap, "核定载客位_客运包车（含非定线旅游客运客车）"));
|
entity.setPassengerTotal(gd(row, colMap, "客运量_总计"));
|
entity.setTurnoverTotal(gd(row, colMap, "旅客周转量_总计"));
|
entity.setPassengerClass1(gd(row, colMap, "客运量_一类客运班线（含定线旅游）"));
|
entity.setTurnoverClass1(gd(row, colMap, "旅客周转量_一类客运班线（含定线旅游）"));
|
entity.setPassengerClass2(gd(row, colMap, "客运量_二类客运班线（含定线旅游）"));
|
entity.setTurnoverClass2(gd(row, colMap, "旅客周转量_二类客运班线（含定线旅游）"));
|
entity.setPassengerClass3(gd(row, colMap, "客运量_三类客运班线（含定线旅游）"));
|
entity.setTurnoverClass3(gd(row, colMap, "旅客周转量_三类客运班线（含定线旅游）"));
|
entity.setPassengerClass4(gd(row, colMap, "客运量_四类客运班线（含定线旅游）"));
|
entity.setTurnoverClass4(gd(row, colMap, "旅客周转量_四类客运班线（含定线旅游）"));
|
entity.setPassengerCharter(gd(row, colMap, "客运量_客运包车（含非定线旅游）"));
|
entity.setTurnoverCharter(gd(row, colMap, "旅客周转量_客运包车（含非定线旅游）"));
|
entity.setAvgDistanceTotal(gd(row, colMap, "客运平均运距"));
|
entity.setAvgDistanceClass1(gd(row, colMap, "一类客运班线平均运距"));
|
entity.setAvgDistanceClass2(gd(row, colMap, "二类客运班线平均运距"));
|
entity.setAvgDistanceClass3(gd(row, colMap, "三类客运班线平均运距"));
|
entity.setAvgDistanceClass4(gd(row, colMap, "四类客运班线平均运距"));
|
entity.setAvgDistanceCharter(gd(row, colMap, "旅游、包车平均运距"));
|
entity.setVerifyExplanation(gs(row, colMap, "核实性原因解释"));
|
rows.add(new PassengerRow(r + 1, entity));
|
}
|
// sheet1: 运政车辆数（固定4列：企业代码/企业名称/车辆数/载客位数）
|
if (wb.getNumberOfSheets() > 1) {
|
Sheet authSheet = wb.getSheetAt(1);
|
for (int r = 1; r <= authSheet.getLastRowNum(); r++) {
|
Row row = authSheet.getRow(r);
|
if (row == null) continue;
|
String name = getString(row, 1);
|
if (name == null || name.trim().isEmpty()) continue;
|
PassengerAuthVehicle auth = new PassengerAuthVehicle();
|
auth.setReportPeriod(period);
|
auth.setEnterpriseCode(getString(row, 0));
|
auth.setEnterpriseName(name.trim());
|
auth.setVehicleCount(getInt(row, 2));
|
auth.setSeatCount(getInt(row, 3));
|
authList.add(auth);
|
}
|
}
|
} catch (Exception e) {
|
log.error("Passenger monthly parse error", e);
|
throw e;
|
}
|
|
// 幂等：同报表期重新导入，先删除旧数据
|
passengerMapper.delete(new LambdaQueryWrapper<PassengerEnterpriseMonthly>()
|
.eq(PassengerEnterpriseMonthly::getReportPeriod, period));
|
passengerAuthMapper.delete(new LambdaQueryWrapper<PassengerAuthVehicle>()
|
.eq(PassengerAuthVehicle::getReportPeriod, period));
|
// 只清 H2031 规则产生的审核结果，避免误删同期的货运审核结果
|
List<Long> h2031RuleIds = new ArrayList<>();
|
for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {
|
if ("H2031".equals(rule.getReportType())) h2031RuleIds.add(rule.getId());
|
}
|
if (!h2031RuleIds.isEmpty()) {
|
auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
|
.eq(AuditResult::getReportPeriod, period)
|
.in(AuditResult::getRuleId, h2031RuleIds));
|
}
|
|
int success = 0;
|
List<String> failDetails = new ArrayList<>();
|
for (PassengerRow item : rows) {
|
try {
|
passengerMapper.insert(item.entity);
|
success++;
|
} catch (Exception e) {
|
Throwable cause = e;
|
while (cause.getCause() != null) {
|
cause = cause.getCause();
|
}
|
String reason = cause.getMessage();
|
if (reason == null || reason.trim().isEmpty()) {
|
reason = e.getMessage();
|
}
|
failDetails.add("第 " + item.excelRow + " 行（" + item.entity.getEnterpriseName() + "）：" + reason);
|
log.error("Passenger insert error: row {}, {}", item.excelRow, item.entity.getEnterpriseName(), e);
|
}
|
}
|
String errorDetail = String.join("\n", failDetails);
|
if (errorDetail.length() > 4000) {
|
errorDetail = errorDetail.substring(0, 4000) + "\n……";
|
}
|
recordBatch(file.getOriginalFilename(), "H2031", period, rows.size(), success, failDetails.size(), errorDetail);
|
|
int authSuccess = 0;
|
for (PassengerAuthVehicle auth : authList) {
|
try {
|
passengerAuthMapper.insert(auth);
|
authSuccess++;
|
} catch (Exception e) {
|
log.error("passenger auth insert error: {}", auth.getEnterpriseName(), e);
|
}
|
}
|
recordBatch(file.getOriginalFilename(), "PASSENGER_AUTH", period, authList.size(), authSuccess, 0, null);
|
log.info("Passenger monthly imported: {} rows, auth {} rows for {}", success, authSuccess, period);
|
return new ImportResult(success, failDetails.size(), failDetails);
|
}
|
|
|
|
/** 公路旅客个体客运量/周转量（导入后按市州汇总填充分市州表个体行），支持两种模板：
|
* ① 企业线下汇报明细（表头含「企业名称」）：逐企业入库；
|
* ② 省厅市州汇总表《湖北省公路旅客运输个体户月度生产情况（YYYY.M）》：
|
* sheet 内为「全省N月个体客运量汇总表」标题块 + 「市州/个体车辆数/…/个体客运量/个体旅客周转量」表头，
|
* 按市州逐行入库（同一市州多行如 县内班线+跨县班线 均入库，报表侧按市州自动汇总）。 */
|
public ImportResult importPassengerIndividual(MultipartFile file, String period) throws Exception {
|
List<PassengerIndividualMonthly> rows = new ArrayList<>();
|
try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
|
Sheet sheet = wb.getSheetAt(0);
|
Map<String, Integer> colMap = buildHeaderMap(sheet.getRow(0));
|
if (colMap.containsKey("企业名称")) {
|
parsePassengerIndividualEnterprise(sheet, colMap, period, rows);
|
} else {
|
parsePassengerIndividualCitySheet(sheet, period, rows);
|
}
|
} catch (Exception e) {
|
log.error("Passenger individual parse error", e);
|
throw e;
|
}
|
passengerIndividualMapper.delete(new LambdaQueryWrapper<PassengerIndividualMonthly>()
|
.eq(PassengerIndividualMonthly::getReportPeriod, period));
|
int success = 0;
|
List<String> failDetails = new ArrayList<>();
|
for (PassengerIndividualMonthly item : rows) {
|
try {
|
passengerIndividualMapper.insert(item);
|
success++;
|
} catch (Exception e) {
|
Throwable cause = e;
|
while (cause.getCause() != null) {
|
cause = cause.getCause();
|
}
|
String reason = cause.getMessage();
|
if (reason == null || reason.trim().isEmpty()) {
|
reason = e.getMessage();
|
}
|
failDetails.add("第 " + (rows.indexOf(item) + 2) + " 行（" + item.getEnterpriseName() + "）：" + reason);
|
}
|
}
|
String errorDetail = String.join("\n", failDetails);
|
if (errorDetail.length() > 4000) {
|
errorDetail = errorDetail.substring(0, 4000) + "\n……";
|
}
|
recordBatch(file.getOriginalFilename(), "PASSENGER_INDIVIDUAL", period, rows.size(), success, failDetails.size(), errorDetail);
|
log.info("Passenger individual imported: {} rows for {}", success, period);
|
return new ImportResult(success, failDetails.size(), failDetails);
|
}
|
|
/** 旧版个体客运量模板（企业线下汇报明细：所属地区/企业代码/企业名称/…个体客运量/…个体旅客周转量） */
|
private void parsePassengerIndividualEnterprise(Sheet sheet, Map<String, Integer> colMap, String period,
|
List<PassengerIndividualMonthly> rows) {
|
Integer codeCol = colMap.get("所属地区");
|
Integer entCol = colMap.get("企业代码");
|
Integer nameCol = colMap.get("企业名称");
|
Integer passCol = null;
|
Integer turnCol = null;
|
for (Map.Entry<String, Integer> e : colMap.entrySet()) {
|
String k = e.getKey();
|
if (k != null && k.contains("个体") && k.contains("客运量")) passCol = e.getValue();
|
if (k != null && k.contains("个体") && (k.contains("周转量") || k.contains("周转"))) turnCol = e.getValue();
|
}
|
if (passCol == null && turnCol == null) {
|
throw new RuntimeException("未找到「个体客运量/个体旅客周转量」列，请检查模板（列名需包含“个体客运量”“个体周转量”）");
|
}
|
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String name = nameCol != null ? getString(row, nameCol) : null;
|
if (name == null || name.trim().isEmpty()) continue;
|
Double pass = passCol != null ? getDecimalOrNull(row, passCol) : null;
|
Double turn = turnCol != null ? getDecimalOrNull(row, turnCol) : null;
|
if ((pass == null || pass == 0.0) && (turn == null || turn == 0.0)) continue;
|
PassengerIndividualMonthly entity = new PassengerIndividualMonthly();
|
entity.setReportPeriod(period);
|
entity.setRegionCode(codeCol != null ? getString(row, codeCol) : null);
|
entity.setEnterpriseCode(entCol != null ? getString(row, entCol) : null);
|
entity.setEnterpriseName(name.trim());
|
entity.setPassengerCount(pass);
|
entity.setTurnover(turn);
|
rows.add(entity);
|
}
|
}
|
|
/** 新版市州汇总模板：《全省N月个体客运量汇总表》标题行 → 表头行（市州/…/个体客运量/个体旅客周转量）→ 市州数据行 */
|
private void parsePassengerIndividualCitySheet(Sheet sheet, String period, List<PassengerIndividualMonthly> rows) {
|
int targetMonth;
|
try {
|
targetMonth = Integer.parseInt(period.split("-")[1]);
|
} catch (Exception e) {
|
throw new RuntimeException("报表期格式应为 yyyy-MM：" + period);
|
}
|
int titleRow = -1;
|
for (int r = 0; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String t = getString(row, 0);
|
if (t == null || !t.contains("全省") || !t.contains("月")) continue;
|
java.util.regex.Matcher m = java.util.regex.Pattern.compile("全省(\\d+)月").matcher(t);
|
if (!m.find()) continue;
|
if (Integer.parseInt(m.group(1)) == targetMonth) { titleRow = r; break; }
|
if (titleRow < 0) titleRow = r; // 标题不含目标月份时暂记第一个标题块兜底
|
}
|
if (titleRow < 0) {
|
throw new RuntimeException("未找到「全省N月个体客运量汇总表」标题块，请使用《模板_湖北省公路旅客运输个体户月度生产情况（YYYY.M）》整理后导入");
|
}
|
Row header = sheet.getRow(titleRow + 1);
|
if (header == null) {
|
throw new RuntimeException("「全省" + targetMonth + "月…汇总表」标题后缺少表头行（市州/个体客运量/个体旅客周转量）");
|
}
|
int cityCol = -1, passCol = -1, turnCol = -1;
|
for (Cell c : header) {
|
String h = FORMATTER.formatCellValue(c).trim();
|
if (h.isEmpty()) continue;
|
if (h.contains("市州")) cityCol = c.getColumnIndex();
|
else if (h.contains("个体") && h.contains("客运量")) passCol = c.getColumnIndex();
|
else if (h.contains("个体") && (h.contains("周转量") || h.contains("周转"))) turnCol = c.getColumnIndex();
|
}
|
if (cityCol < 0 || (passCol < 0 && turnCol < 0)) {
|
throw new RuntimeException("未找到「市州 / 个体客运量 / 个体旅客周转量」列，请使用系统模板整理后导入");
|
}
|
int lastRow = Math.min(sheet.getLastRowNum(), titleRow + 40);
|
for (int r = titleRow + 2; r <= lastRow; r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String first = getString(row, cityCol);
|
if (first == null) break;
|
String t = first.trim();
|
if (t.isEmpty() || t.contains("合计") || t.contains("环比") || t.contains("全省")) break;
|
String city = RegionUtil.normalizeCityName(t);
|
if (!RegionUtil.cityList().contains(city)) continue;
|
Double pass = passCol >= 0 ? getDecimalOrNull(row, passCol) : null;
|
Double turn = turnCol >= 0 ? getDecimalOrNull(row, turnCol) : null;
|
if ((pass == null || pass == 0.0) && (turn == null || turn == 0.0)) continue;
|
PassengerIndividualMonthly entity = new PassengerIndividualMonthly();
|
entity.setReportPeriod(period);
|
entity.setRegionCode(RegionUtil.codeOfCity(city));
|
entity.setEnterpriseName(city);
|
entity.setPassengerCount(pass);
|
entity.setTurnover(turn);
|
rows.add(entity);
|
}
|
}
|
|
/** 公路旅客运政车辆数（兼容两种表头：① 企业代码/企业名称/车辆数/载客位数；② 运政库导出 OWNERNAME/总车辆数/客位数，无企业代码列） */
|
public ImportResult importPassengerAuth(MultipartFile file, String period) throws Exception {
|
List<PassengerAuthVehicle> authList = new ArrayList<>();
|
try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
|
Sheet sheet = wb.getSheetAt(0);
|
Map<String, Integer> colMap = buildHeaderMapCompat(sheet.getRow(0));
|
Integer nameCol = firstColIdx(colMap, "企业名称", "OWNERNAME");
|
Integer vehicleCol = firstColIdx(colMap, "车辆数", "总车辆数");
|
Integer seatCol = firstColIdx(colMap, "载客位数", "客位数");
|
Integer codeCol = firstColIdx(colMap, "企业代码");
|
if (nameCol == null || vehicleCol == null || seatCol == null) {
|
throw new RuntimeException("所选数据类型【旅客运政车辆数】与文件内容不符：表头需含【企业名称/车辆数/载客位数】或【OWNERNAME/总车辆数/客位数】，请确认是否选错了数据类型或使用了旧模板");
|
}
|
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String name = getString(row, nameCol);
|
if (name == null || name.trim().isEmpty()) continue;
|
PassengerAuthVehicle auth = new PassengerAuthVehicle();
|
auth.setReportPeriod(period);
|
auth.setEnterpriseCode(codeCol == null ? null : getString(row, codeCol));
|
auth.setEnterpriseName(name.trim());
|
auth.setVehicleCount(getInt(row, vehicleCol));
|
auth.setSeatCount(getInt(row, seatCol));
|
authList.add(auth);
|
}
|
} catch (Exception e) {
|
log.error("Passenger auth parse error", e);
|
throw e;
|
}
|
// 幂等：同报表期重新导入，先删除旧数据
|
passengerAuthMapper.delete(new LambdaQueryWrapper<PassengerAuthVehicle>()
|
.eq(PassengerAuthVehicle::getReportPeriod, period));
|
deleteRuleResults("H2031", period);
|
int success = 0;
|
List<String> failDetails = new ArrayList<>();
|
for (PassengerAuthVehicle auth : authList) {
|
try {
|
passengerAuthMapper.insert(auth);
|
success++;
|
} catch (Exception e) {
|
Throwable cause = e;
|
while (cause.getCause() != null) cause = cause.getCause();
|
String reason = cause.getMessage();
|
if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();
|
failDetails.add("第 " + (authList.indexOf(auth) + 2) + " 行（" + auth.getEnterpriseName() + "）：" + reason);
|
log.error("Passenger auth insert error: {}", auth.getEnterpriseName(), e);
|
}
|
}
|
String errorDetail = String.join("\n", failDetails);
|
if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";
|
recordBatch(file.getOriginalFilename(), "PASSENGER_AUTH", period, authList.size(), success, failDetails.size(), errorDetail);
|
log.info("Passenger auth imported: {} rows for {}", success, period);
|
return new ImportResult(success, failDetails.size(), failDetails);
|
}
|
|
// ========== H204 道路货运车辆能源消耗情况（季报，含运政车辆信息sheet） ==========
|
|
/** 能耗明细每组固定18列，车牌号起始列（0-based）：3,21,39,57,75 */
|
private static final int[] ENERGY_GROUP_COLS = {3, 21, 39, 57, 75};
|
|
/** 能耗明细模板表头关键列（0-based）：程序按固定列位解析，解析前校验表头，错位直接拒绝 */
|
private static final int[] ENERGY_HEADER_COLS = {3, 4, 5, 21, 39, 57, 75};
|
private static final String[] ENERGY_HEADER_KEYWORDS = {"车牌号", "车辆类型", "燃料类型", "车牌号", "车牌号", "车牌号", "车牌号"};
|
|
/** 能耗导入按报表期互斥锁（H204 明细与运政信息共用，避免同报表期并发重导互相覆盖） */
|
private Object energyLock(String period) {
|
return energyImportLocks.computeIfAbsent(period == null ? "" : period, k -> new Object());
|
}
|
|
public ImportResult importEnergyMonthly(MultipartFile file, String period) throws Exception {
|
List<EnergyRow> rows = new ArrayList<>();
|
List<EnergyAuthVehicle> authList = new ArrayList<>();
|
try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
|
// sheet0: 第二季度货车能耗明细（每行左右各5组车辆，每组固定18列）
|
Sheet sheet = wb.getSheetAt(0);
|
requireHeaderKeyword(sheet, "道路货运车辆能源消耗情况", "企业名称", "车牌号");
|
requireEnergyHeaderLayout(sheet);
|
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String enterpriseName = getString(row, 2);
|
if (enterpriseName == null || enterpriseName.trim().isEmpty()) continue;
|
for (int group : ENERGY_GROUP_COLS) {
|
String plate = getString(row, group);
|
if (plate == null || plate.trim().isEmpty() || "0".equals(plate.trim())) continue;
|
EnergyVehicleQuarterly e = new EnergyVehicleQuarterly();
|
e.setReportPeriod(period);
|
e.setRegionCode(getString(row, 0));
|
e.setEnterpriseCode(getString(row, 1));
|
e.setEnterpriseName(enterpriseName.trim());
|
e.setPlateNo(plate.trim());
|
String vtc = getString(row, group + 1);
|
e.setVehicleTypeCode(vtc);
|
e.setVehicleType(vtc == null ? null : ENERGY_VEHICLE_TYPE_NAMES.getOrDefault(vtc, vtc));
|
String ftc = getString(row, group + 2);
|
e.setFuelTypeCode(ftc);
|
e.setFuelType(ftc == null ? null : ENERGY_FUEL_TYPE_NAMES.getOrDefault(ftc, ftc));
|
e.setManufactureYear(getIntOrNull(row, group + 3));
|
e.setMarkedTonnage(getDecimalOrNull(row, group + 4));
|
e.setTotalMileage(getDecimalOrNull(row, group + 5));
|
e.setLoadedMileage(getDecimalOrNull(row, group + 6));
|
e.setEmptyMileage(getDecimalOrNull(row, group + 7));
|
e.setFreight(getDecimalOrNull(row, group + 8));
|
e.setTurnover(getDecimalOrNull(row, group + 9));
|
e.setTripCount(getIntOrNull(row, group + 10));
|
String unit = getString(row, group + 11);
|
e.setFuelUnit(unit == null ? null : ENERGY_FUEL_UNIT_NAMES.getOrDefault(unit, unit));
|
e.setFuelConsumption(getDecimalOrNull(row, group + 12));
|
e.setAvgDistance(getDecimalOrNull(row, group + 13));
|
e.setAvgLoadedTonnage(getDecimalOrNull(row, group + 14));
|
e.setAvgTripMileage(getDecimalOrNull(row, group + 15));
|
e.setFuelPer100km(getDecimalOrNull(row, group + 16));
|
e.setTurnoverFuel(getDecimalOrNull(row, group + 17));
|
rows.add(new EnergyRow(r + 1, e));
|
}
|
}
|
// sheet1: 运政车辆信息（固定5列：车牌号/车辆类型/燃料类型/标记吨位/准牵引质量）
|
if (wb.getNumberOfSheets() > 1) {
|
Sheet authSheet = wb.getSheetAt(1);
|
for (int r = 1; r <= authSheet.getLastRowNum(); r++) {
|
Row row = authSheet.getRow(r);
|
if (row == null) continue;
|
String plate = getString(row, 0);
|
if (plate == null || plate.trim().isEmpty() || "0".equals(plate.trim())) continue;
|
EnergyAuthVehicle auth = new EnergyAuthVehicle();
|
auth.setReportPeriod(period);
|
auth.setPlateNo(plate.trim());
|
auth.setVehicleType(getString(row, 1));
|
auth.setFuelType(getString(row, 2));
|
auth.setMarkedTonnage(getDecimal(row, 3));
|
auth.setTractionQuality(getDecimal(row, 4));
|
authList.add(auth);
|
}
|
}
|
} catch (Exception e) {
|
log.error("Energy monthly parse error", e);
|
throw e;
|
}
|
|
// 幂等：同报表期重新导入，先删除旧数据（两表 + H204 规则审核结果）
|
// 加锁：同报表期并发重导互斥，防止 delete/insert 交错导致数据残留或翻倍
|
synchronized (energyLock(period)) {
|
energyMapper.delete(new LambdaQueryWrapper<EnergyVehicleQuarterly>()
|
.eq(EnergyVehicleQuarterly::getReportPeriod, period));
|
energyAuthMapper.delete(new LambdaQueryWrapper<EnergyAuthVehicle>()
|
.eq(EnergyAuthVehicle::getReportPeriod, period));
|
List<Long> h204RuleIds = new ArrayList<>();
|
for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {
|
if ("H204".equals(rule.getReportType())) h204RuleIds.add(rule.getId());
|
}
|
if (!h204RuleIds.isEmpty()) {
|
auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
|
.eq(AuditResult::getReportPeriod, period)
|
.in(AuditResult::getRuleId, h204RuleIds));
|
}
|
|
int success = 0;
|
List<String> failDetails = new ArrayList<>();
|
for (EnergyRow item : rows) {
|
try {
|
energyMapper.insert(item.entity);
|
success++;
|
} catch (Exception e) {
|
Throwable cause = e;
|
while (cause.getCause() != null) cause = cause.getCause();
|
String reason = cause.getMessage();
|
if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();
|
failDetails.add("第 " + item.excelRow + " 行（" + item.entity.getEnterpriseName()
|
+ " " + item.entity.getPlateNo() + "）：" + reason);
|
log.error("Energy insert error: row {}, {}", item.excelRow, item.entity.getPlateNo(), e);
|
}
|
}
|
String errorDetail = String.join("\n", failDetails);
|
if (errorDetail.length() > 4000) {
|
errorDetail = errorDetail.substring(0, 4000) + "\n……";
|
}
|
recordBatch(file.getOriginalFilename(), "H204", period, rows.size(), success, failDetails.size(), errorDetail);
|
|
int authSuccess = 0;
|
for (EnergyAuthVehicle auth : authList) {
|
try {
|
energyAuthMapper.insert(auth);
|
authSuccess++;
|
} catch (Exception e) {
|
log.error("energy auth insert error: {}", auth.getPlateNo(), e);
|
}
|
}
|
recordBatch(file.getOriginalFilename(), "ENERGY_AUTH", period, authList.size(), authSuccess, 0, null);
|
log.info("Energy monthly imported: {} vehicles, auth {} rows for {}", success, authSuccess, period);
|
return new ImportResult(success, failDetails.size(), failDetails);
|
}
|
}
|
|
/** 能耗车辆运政信息（独立模板：车牌号/车辆类型/燃料类型/标记吨位/准牵引质量） */
|
public ImportResult importEnergyAuth(MultipartFile file, String period) throws Exception {
|
List<EnergyAuthVehicle> authList = new ArrayList<>();
|
try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
|
Sheet sheet = wb.getSheetAt(0);
|
requireHeaderKeyword(sheet, "能耗车辆运政信息", "车牌号");
|
Row authHeader = sheet.getRow(0);
|
String authH0 = authHeader == null ? null : FORMATTER.formatCellValue(authHeader.getCell(0)).trim();
|
if (authH0 == null || authH0.isEmpty() || !authH0.contains("车牌号")) {
|
throw new RuntimeException("能耗车辆运政信息表头与系统模板不一致：第 1 列应为「车牌号」，实际为「"
|
+ (authH0 == null || authH0.isEmpty() ? "空" : authH0) + "」。请使用系统模板《模板_能耗车辆运政信息.xlsx》整理后再导入");
|
}
|
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String plate = getString(row, 0);
|
if (plate == null || plate.trim().isEmpty() || "0".equals(plate.trim())) continue;
|
EnergyAuthVehicle auth = new EnergyAuthVehicle();
|
auth.setReportPeriod(period);
|
auth.setPlateNo(plate.trim());
|
auth.setVehicleType(getString(row, 1));
|
auth.setFuelType(getString(row, 2));
|
auth.setMarkedTonnage(getDecimal(row, 3));
|
auth.setTractionQuality(getDecimal(row, 4));
|
authList.add(auth);
|
}
|
} catch (Exception e) {
|
log.error("Energy auth parse error", e);
|
throw e;
|
}
|
// 幂等：同报表期重新导入，先删除旧数据；与 H204 明细共用报表期锁，防并发互相覆盖
|
synchronized (energyLock(period)) {
|
energyAuthMapper.delete(new LambdaQueryWrapper<EnergyAuthVehicle>()
|
.eq(EnergyAuthVehicle::getReportPeriod, period));
|
deleteRuleResults("H204", period);
|
int success = 0;
|
List<String> failDetails = new ArrayList<>();
|
for (EnergyAuthVehicle auth : authList) {
|
try {
|
energyAuthMapper.insert(auth);
|
success++;
|
} catch (Exception e) {
|
Throwable cause = e;
|
while (cause.getCause() != null) cause = cause.getCause();
|
String reason = cause.getMessage();
|
if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();
|
failDetails.add("第 " + (authList.indexOf(auth) + 2) + " 行（" + auth.getPlateNo() + "）：" + reason);
|
log.error("Energy auth insert error: {}", auth.getPlateNo(), e);
|
}
|
}
|
String errorDetail = String.join("\n", failDetails);
|
if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";
|
recordBatch(file.getOriginalFilename(), "ENERGY_AUTH", period, authList.size(), success, failDetails.size(), errorDetail);
|
log.info("Energy auth imported: {} rows for {}", success, period);
|
return new ImportResult(success, failDetails.size(), failDetails);
|
}
|
}
|
|
/** H204 行记录：保留 Excel 行号用于失败定位 */
|
private static class EnergyRow {
|
final int excelRow;
|
final EnergyVehicleQuarterly entity;
|
|
EnergyRow(int excelRow, EnergyVehicleQuarterly entity) {
|
this.excelRow = excelRow;
|
this.entity = entity;
|
}
|
}
|
|
/** 空单元格返回 null（用于能耗明细未填写项与 0 区分） */
|
private Integer getIntOrNull(Row row, int idx) {
|
Double val = getDecimalOrNull(row, idx);
|
return val == null ? null : val.intValue();
|
}
|
|
// ========== 运政车辆 ==========
|
|
/** 运政车辆导入：与 H203-2 月报一致，按表头名称定位列，列顺序变化也能正确导入 */
|
public int importTransportAuth(MultipartFile file, String period) throws Exception {
|
List<TransportAuthVehicle> list = new ArrayList<>();
|
int failRows = 0;
|
try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
|
Sheet sheet = wb.getSheetAt(0);
|
requireHeaderCols(sheet, "运政车辆", "企业名称", "总牵引车数", "总挂车数", "其它车辆数", "挂车总吨位(吨)", "其它车辆总吨位(吨)");
|
Map<String, Integer> colMap = buildHeaderMap(sheet.getRow(0));
|
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String name = gs(row, colMap, "企业名称");
|
if (name == null || name.trim().isEmpty()) continue;
|
TransportAuthVehicle entity = new TransportAuthVehicle();
|
entity.setReportPeriod(period);
|
entity.setEnterpriseName(name);
|
entity.setTractorCount(gi(row, colMap, "总牵引车数"));
|
entity.setTrailerCount(gi(row, colMap, "总挂车数"));
|
entity.setOtherCount(gi(row, colMap, "其它车辆数"));
|
entity.setTrailerTons(gd(row, colMap, "挂车总吨位(吨)"));
|
entity.setOtherTons(gd(row, colMap, "其它车辆总吨位(吨)"));
|
list.add(entity);
|
}
|
} catch (Exception e) {
|
throw e;
|
}
|
|
// 幂等：同报表期重新导入，先删除旧数据
|
transportAuthMapper.delete(new LambdaQueryWrapper<TransportAuthVehicle>()
|
.eq(TransportAuthVehicle::getReportPeriod, period));
|
auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
|
.eq(AuditResult::getReportPeriod, period));
|
|
int success = 0;
|
for (TransportAuthVehicle entity : list) {
|
try {
|
transportAuthMapper.insert(entity);
|
success++;
|
} catch (Exception e) {
|
log.error("transport auth insert error: {}", entity.getEnterpriseName(), e);
|
failRows++;
|
}
|
}
|
recordBatch(file.getOriginalFilename(), "TRANSPORT_AUTH", period, list.size(), success, failRows, null);
|
log.info("Transport auth imported: {} rows for {}", success, period);
|
return success;
|
}
|
|
// ========== 轨迹里程 ==========
|
|
/** 轨迹里程导入：与 H203-2 月报一致，按表头名称定位列，列顺序变化也能正确导入 */
|
public int importTrackMileage(MultipartFile file, String period) throws Exception {
|
List<VehicleTrackMileage> list = new ArrayList<>();
|
int failRows = 0;
|
try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
|
Sheet sheet = wb.getSheetAt(0);
|
requireHeaderCols(sheet, "轨迹里程", "企业名称", "月度总里程(公里)", "有里程的车辆数");
|
Map<String, Integer> colMap = buildHeaderMap(sheet.getRow(0));
|
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String name = gs(row, colMap, "企业名称");
|
if (name == null || name.trim().isEmpty()) continue;
|
VehicleTrackMileage entity = new VehicleTrackMileage();
|
entity.setEnterpriseName(name);
|
entity.setMonthlyMileage(gd(row, colMap, "月度总里程(公里)"));
|
entity.setTrackedVehicles(gi(row, colMap, "有里程的车辆数"));
|
entity.setReportPeriod(period);
|
list.add(entity);
|
}
|
} catch (Exception e) {
|
throw e;
|
}
|
|
// 幂等：同报表期先删除
|
trackMileageMapper.delete(new LambdaQueryWrapper<VehicleTrackMileage>()
|
.eq(VehicleTrackMileage::getReportPeriod, period));
|
auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
|
.eq(AuditResult::getReportPeriod, period));
|
|
int success = 0;
|
for (VehicleTrackMileage entity : list) {
|
try {
|
trackMileageMapper.insert(entity);
|
success++;
|
} catch (Exception e) {
|
log.error("track mileage insert error: {}", entity.getEnterpriseName(), e);
|
failRows++;
|
}
|
}
|
recordBatch(file.getOriginalFilename(), "TRACK_MILEAGE", period, list.size(), success, failRows, null);
|
log.info("Track mileage imported: {} rows for {}", success, period);
|
return success;
|
}
|
|
// ========== 规上规下拆分 ==========
|
|
public int importScaleSplit(MultipartFile file, String period) throws Exception {
|
List<ScaleSplitTransport> list = new ArrayList<>();
|
int failRows = 0;
|
try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
|
int targetMonth = Integer.parseInt(period.split("-")[1]); // 仅导入目标期所在月的页签（1-N月文件按目标期取当月/累计）
|
boolean hasPeriodSheet = false;
|
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
|
String sn = wb.getSheetAt(i).getSheetName();
|
if (sn != null && (sn.contains("当月") || sn.contains("月份") || sn.contains("累计"))) { hasPeriodSheet = true; break; }
|
}
|
if (!hasPeriodSheet) {
|
throw new RuntimeException("所选数据类型【规上规下拆分】与文件内容不符：未找到含 当月/累计 的 sheet，请确认是否选错了数据类型");
|
}
|
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
|
Sheet sheet = wb.getSheetAt(i);
|
String sheetName = sheet.getSheetName();
|
String periodType = null;
|
java.util.regex.Matcher mSheetMonth = java.util.regex.Pattern.compile("(\\d{1,2})\\u6708(\\u4efd|\\u7d2f\\u8ba1)").matcher(sheetName);
|
if (mSheetMonth.find()) {
|
int sheetMonth = Integer.parseInt(mSheetMonth.group(1));
|
if (sheetMonth == targetMonth) {
|
periodType = "份".equals(mSheetMonth.group(2)) ? "MONTH" : "CUMULATIVE";
|
}
|
} else {
|
if (sheetName.contains("当月")) periodType = "MONTH";
|
else if (sheetName.contains("累计")) periodType = "CUMULATIVE";
|
}
|
if (periodType == null) continue;
|
|
// 校验 sheet 布局：第2行应含「市州/规上企业运输量」，第3行子表头应含「周转量」或「货运量」（扩展布局）
|
Row headRow = sheet.getRow(1);
|
Row subHeadRow = sheet.getRow(2);
|
boolean layoutOk = headRow != null
|
&& getString(headRow, 0) != null && getString(headRow, 0).contains("市州")
|
&& getString(headRow, 1) != null && getString(headRow, 1).contains("规上")
|
&& subHeadRow != null
|
&& ("货运量".equals(getString(subHeadRow, 0))
|
|| (getString(subHeadRow, 1) != null && getString(subHeadRow, 1).contains("周转量")));
|
if (!layoutOk) {
|
throw new RuntimeException("所选数据类型【规上规下拆分】与文件内容不符：sheet「" + sheetName
|
+ "」未找到「市州/规上企业运输量/周转量」表头，请确认是否选错了数据类型");
|
}
|
|
// 检测子表头：是否包含货运量列（扩展布局）
|
Row subHeader = sheet.getRow(2);
|
boolean hasFreight = subHeader != null
|
&& "货运量".equals(getString(subHeader, 0));
|
|
for (int r = 3; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String regionName = getString(row, 0);
|
if (regionName == null || regionName.trim().isEmpty()) continue;
|
|
ScaleSplitTransport entity = new ScaleSplitTransport();
|
entity.setReportPeriod(period);
|
entity.setPeriodType(periodType);
|
entity.setRegionName(regionName.trim());
|
|
if (hasFreight) {
|
entity.setAboveScaleFreight(getDecimal(row, 0));
|
entity.setAboveScaleTurnover(getDecimal(row, 1));
|
entity.setAboveScaleRank(getInt(row, 2));
|
entity.setAboveScaleYoy(getDecimal(row, 3));
|
entity.setAboveScaleYoyRank(getInt(row, 4));
|
entity.setBelowScaleFreight(getDecimal(row, 5));
|
entity.setBelowScaleTurnover(getDecimal(row, 6));
|
entity.setBelowScaleRank(getInt(row, 7));
|
entity.setBelowScaleYoy(getDecimal(row, 8));
|
entity.setBelowScaleYoyRank(getInt(row, 9));
|
entity.setTotalFreight(getDecimal(row, 10));
|
entity.setTotalTurnover(getDecimal(row, 11));
|
entity.setTotalRank(getInt(row, 12));
|
entity.setTotalYoy(getDecimal(row, 13));
|
entity.setTotalYoyRank(getInt(row, 14));
|
} else {
|
entity.setAboveScaleTurnover(getDecimal(row, 1));
|
entity.setAboveScaleRank(getInt(row, 2));
|
entity.setAboveScaleYoy(getDecimal(row, 3));
|
entity.setAboveScaleYoyRank(getInt(row, 4));
|
entity.setBelowScaleTurnover(getDecimal(row, 5));
|
entity.setBelowScaleRank(getInt(row, 6));
|
entity.setBelowScaleYoy(getDecimal(row, 7));
|
entity.setBelowScaleYoyRank(getInt(row, 8));
|
entity.setTotalTurnover(getDecimal(row, 9));
|
entity.setTotalRank(getInt(row, 10));
|
entity.setTotalYoy(getDecimal(row, 11));
|
entity.setTotalYoyRank(getInt(row, 12));
|
}
|
list.add(entity);
|
}
|
}
|
} catch (Exception e) {
|
throw e;
|
}
|
|
// 幂等：同报表期先删除
|
scaleSplitMapper.delete(new LambdaQueryWrapper<ScaleSplitTransport>()
|
.eq(ScaleSplitTransport::getReportPeriod, period));
|
|
int success = 0;
|
for (ScaleSplitTransport entity : list) {
|
try {
|
scaleSplitMapper.insert(entity);
|
success++;
|
} catch (Exception e) {
|
log.error("scale split insert error: {} - {}", entity.getRegionName(), entity.getPeriodType(), e);
|
failRows++;
|
}
|
}
|
recordBatch(file.getOriginalFilename(), "SCALE_SPLIT", period, list.size(), success, failRows, null);
|
log.info("Scale split imported: {} rows for {}", success, period);
|
return success;
|
}
|
|
|
// ========== 货运量周转量（模板_货运量周转量.xlsx，每月导入） ==========
|
|
public int importFreightTurnover(MultipartFile file, String period) throws Exception {
|
try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
|
// 多年度工作簿（一年一个 Sheet：整年 1-12 月，如 21-26 年定稿文件）→ 全部年份入库
|
List<String> yearSheets = detectYearSheets(wb);
|
if (!yearSheets.isEmpty()) {
|
return importFreightTurnoverByYear(wb, file, yearSheets);
|
}
|
return importFreightTurnoverByMonth(wb, file, period);
|
}
|
}
|
|
/** 每月导入（模板_货运量周转量.xlsx）：左半今年 1-N 月，右半去年 1-12 月 */
|
private int importFreightTurnoverByMonth(Workbook wb, MultipartFile file, String period) throws Exception {
|
List<FreightTurnoverImport> list = new ArrayList<>();
|
// 报表期 N 月：左半为今年 1-N 月（列数不固定），右半固定为去年 1-12 月（算同比用）
|
int monthCount = Integer.parseInt(period.split("-")[1]);
|
{
|
Sheet sheet = wb.getSheetAt(0);
|
Row header = sheet.getRow(0);
|
String h0 = header == null ? null : getString(header, 0);
|
String h1 = header == null ? null : getString(header, 1);
|
if (header == null || h0 == null || !h0.contains("货运量") || h1 == null || !h1.contains("月")) {
|
throw new RuntimeException("所选数据类型【货运量周转量】与文件内容不符：表头应为「货运量(万吨)/1月…12月」布局，请确认是否选错了数据类型");
|
}
|
boolean cityRowFound = false;
|
for (int r = 1; r <= Math.min(sheet.getLastRowNum(), 30); r++) {
|
Row rr = sheet.getRow(r);
|
if (rr == null) continue;
|
String nm = getString(rr, 0);
|
if (nm == null || nm.trim().isEmpty()) continue;
|
String region = RegionUtil.normalizeCityName(nm);
|
if (RegionUtil.cityList().contains(region) || "湖北省".equals(region)) { cityRowFound = true; break; }
|
}
|
if (!cityRowFound) {
|
throw new RuntimeException("所选数据类型【货运量周转量】与文件内容不符：未识别到市州行（首列应为市州名称），请确认是否选错了数据类型");
|
}
|
Map<String, FreightTurnoverImport> freightMap = new HashMap<>();
|
Map<String, FreightTurnoverImport> turnoverMap = new HashMap<>();
|
Set<String> seen = new HashSet<>();
|
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String name = getString(row, 0);
|
if (name == null || name.trim().isEmpty()) continue;
|
String region = RegionUtil.normalizeCityName(name);
|
boolean isCity = RegionUtil.cityList().contains(region);
|
if (!isCity && !"湖北省".equals(region)) continue; // 表头行
|
if (seen.contains(region)) {
|
// 该名称已出现过 -> 周转量区块
|
FreightTurnoverImport entity = turnoverMap.get(region);
|
if (entity == null) {
|
entity = new FreightTurnoverImport();
|
entity.setReportPeriod(period);
|
entity.setRegionName(name.trim());
|
turnoverMap.put(region, entity);
|
}
|
fillTurnoverRow(row, entity, monthCount);
|
} else {
|
seen.add(region);
|
FreightTurnoverImport entity = freightMap.get(region);
|
if (entity == null) {
|
entity = new FreightTurnoverImport();
|
entity.setReportPeriod(period);
|
entity.setRegionName(name.trim());
|
freightMap.put(region, entity);
|
}
|
fillFreightRow(row, entity, monthCount);
|
}
|
}
|
// 合并：一个市州一行（货运量 + 周转量）
|
for (Map.Entry<String, FreightTurnoverImport> entry : freightMap.entrySet()) {
|
FreightTurnoverImport entity = entry.getValue();
|
FreightTurnoverImport turnover = turnoverMap.get(entry.getKey());
|
if (turnover != null) {
|
for (int m = 1; m <= 12; m++) {
|
setTurnover(entity, m, getTurnover(turnover, m));
|
setLastTurnover(entity, m, getLastTurnover(turnover, m));
|
}
|
}
|
list.add(entity);
|
}
|
}
|
Map<String, List<FreightTurnoverImport>> byPeriod = new LinkedHashMap<>();
|
byPeriod.put(period, list);
|
return persistFreightTurnover(byPeriod, file.getOriginalFilename());
|
}
|
|
/** 识别「多年度工作簿」：每个 Sheet 一年（Sheet 名 2021年/2022年…），且表头为「货运量/1月…12月」；否则返回空列表 */
|
private List<String> detectYearSheets(Workbook wb) {
|
List<String> names = new ArrayList<>();
|
if (wb.getNumberOfSheets() < 2) return names;
|
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
|
Sheet sheet = wb.getSheetAt(i);
|
String nm = sheet.getSheetName() == null ? "" : sheet.getSheetName().trim();
|
if (!nm.matches("\\d{4}\\s*年?")) { names.clear(); return names; }
|
Row header = sheet.getRow(0);
|
String h0 = header == null ? null : getString(header, 0);
|
String h1 = header == null ? null : getString(header, 1);
|
if (h0 == null || !h0.contains("货运量") || h1 == null || !h1.contains("月")) {
|
names.clear();
|
return names;
|
}
|
names.add(nm);
|
}
|
return names;
|
}
|
|
/** 多年度工作簿导入：逐年一条报表期（报表期=该年最后有数据的月份），上年同期取上一年度 Sheet */
|
private int importFreightTurnoverByYear(Workbook wb, MultipartFile file, List<String> yearSheets) {
|
Map<Integer, String> sheetByYear = new TreeMap<>();
|
for (String sheetName : yearSheets) {
|
sheetByYear.put(Integer.parseInt(sheetName.replace("年", "").trim()), sheetName);
|
}
|
Map<Integer, Map<String, FreightTurnoverImport>> byYear = new TreeMap<>();
|
Map<Integer, Integer> maxMonthByYear = new HashMap<>();
|
for (Map.Entry<Integer, String> entry : sheetByYear.entrySet()) {
|
Map<String, FreightTurnoverImport> parsed = new LinkedHashMap<>();
|
int maxMonth = parseWholeYearSheet(wb.getSheet(entry.getValue()), parsed);
|
if (parsed.isEmpty()) continue;
|
byYear.put(entry.getKey(), parsed);
|
maxMonthByYear.put(entry.getKey(), Math.max(1, Math.min(12, maxMonth)));
|
}
|
Map<String, List<FreightTurnoverImport>> byPeriod = new LinkedHashMap<>();
|
for (Map.Entry<Integer, Map<String, FreightTurnoverImport>> entry : byYear.entrySet()) {
|
int year = entry.getKey();
|
String yearPeriod = year + "-" + String.format("%02d", maxMonthByYear.get(year));
|
Map<String, FreightTurnoverImport> lastYear = byYear.get(year - 1);
|
List<FreightTurnoverImport> rows = new ArrayList<>();
|
for (Map.Entry<String, FreightTurnoverImport> regionEntry : entry.getValue().entrySet()) {
|
FreightTurnoverImport entity = regionEntry.getValue();
|
entity.setReportPeriod(yearPeriod);
|
// 上年同期 = 上一年度 Sheet（多年度文件没有右半「去年1-12月」区块）
|
FreightTurnoverImport last = lastYear == null ? null : lastYear.get(regionEntry.getKey());
|
if (last != null) {
|
for (int m = 1; m <= 12; m++) {
|
setLastFreight(entity, m, getFreight(last, m));
|
setLastTurnover(entity, m, getTurnover(last, m));
|
}
|
}
|
rows.add(entity);
|
}
|
byPeriod.put(yearPeriod, rows);
|
}
|
return persistFreightTurnover(byPeriod, file.getOriginalFilename());
|
}
|
|
/** 解析整年 Sheet（1-12 月列）：货运量块 + 周转量块；返回最后一个有货运量数据的月份 */
|
private int parseWholeYearSheet(Sheet sheet, Map<String, FreightTurnoverImport> out) {
|
Map<String, FreightTurnoverImport> freightMap = new LinkedHashMap<>();
|
Map<String, FreightTurnoverImport> turnoverMap = new LinkedHashMap<>();
|
Set<String> seen = new HashSet<>();
|
int maxMonth = 0;
|
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String name = getString(row, 0);
|
if (name == null || name.trim().isEmpty()) continue;
|
String region = RegionUtil.normalizeCityName(name);
|
boolean isCity = RegionUtil.cityList().contains(region);
|
if (!isCity && !"湖北省".equals(region)) continue; // 表头/说明行
|
if (seen.contains(region)) {
|
// 该名称已出现过 -> 周转量区块
|
FreightTurnoverImport entity = turnoverMap.get(region);
|
if (entity == null) {
|
entity = new FreightTurnoverImport();
|
entity.setRegionName(name.trim());
|
turnoverMap.put(region, entity);
|
}
|
for (int m = 1; m <= 12; m++) {
|
setTurnover(entity, m, getDecimalOrNull(row, m));
|
}
|
} else {
|
seen.add(region);
|
FreightTurnoverImport entity = freightMap.get(region);
|
if (entity == null) {
|
entity = new FreightTurnoverImport();
|
entity.setRegionName(name.trim());
|
freightMap.put(region, entity);
|
}
|
for (int m = 1; m <= 12; m++) {
|
Double v = getDecimalOrNull(row, m);
|
setFreight(entity, m, v);
|
if (v != null && m > maxMonth) maxMonth = m;
|
}
|
}
|
}
|
// 合并：一个市州一行（货运量 + 周转量）
|
for (Map.Entry<String, FreightTurnoverImport> entry : freightMap.entrySet()) {
|
FreightTurnoverImport entity = entry.getValue();
|
FreightTurnoverImport turnover = turnoverMap.get(entry.getKey());
|
if (turnover != null) {
|
for (int m = 1; m <= 12; m++) {
|
setTurnover(entity, m, getTurnover(turnover, m));
|
}
|
}
|
out.put(entry.getKey(), entity);
|
}
|
return maxMonth;
|
}
|
|
/** 落库：按报表期先删后插（幂等），每个报表期一条导入批次记录，返回成功条数 */
|
private int persistFreightTurnover(Map<String, List<FreightTurnoverImport>> byPeriod, String fileName) {
|
int total = 0;
|
for (Map.Entry<String, List<FreightTurnoverImport>> entry : byPeriod.entrySet()) {
|
String p = entry.getKey();
|
List<FreightTurnoverImport> rows = entry.getValue();
|
freightTurnoverMapper.delete(new LambdaQueryWrapper<FreightTurnoverImport>()
|
.eq(FreightTurnoverImport::getReportPeriod, p));
|
int failRows = 0;
|
int success = 0;
|
for (FreightTurnoverImport entity : rows) {
|
try {
|
freightTurnoverMapper.insert(entity);
|
success++;
|
} catch (Exception e) {
|
log.error("freight turnover insert error: {}", entity.getRegionName(), e);
|
failRows++;
|
}
|
}
|
recordBatch(fileName, "FREIGHT_TURNOVER", p, rows.size(), success, failRows, null);
|
log.info("Freight turnover imported: {} rows for {}", success, p);
|
total += success;
|
}
|
return total;
|
}
|
|
/** 解析货运量区块行：左半 B 起 N 列为今年 1-N 月，右半为去年 1-12 月 */
|
private void fillFreightRow(Row row, FreightTurnoverImport entity, int monthCount) {
|
int rightNameCol = findSecondRegionCol(row);
|
for (int m = 1; m <= monthCount; m++) {
|
setFreight(entity, m, getDecimalOrNull(row, m));
|
}
|
for (int m = 1; m <= 12; m++) {
|
int col = rightNameCol >= 0 ? rightNameCol + m : 9 + m;
|
setLastFreight(entity, m, getDecimalOrNull(row, col));
|
}
|
}
|
|
/** 解析周转量区块行：左半 B 起 N 列为今年 1-N 月，右半为去年 1-12 月 */
|
private void fillTurnoverRow(Row row, FreightTurnoverImport entity, int monthCount) {
|
int rightNameCol = findSecondRegionCol(row);
|
for (int m = 1; m <= monthCount; m++) {
|
setTurnover(entity, m, getDecimalOrNull(row, m));
|
}
|
for (int m = 1; m <= 12; m++) {
|
int col = rightNameCol >= 0 ? rightNameCol + m : 9 + m;
|
setLastTurnover(entity, m, getDecimalOrNull(row, col));
|
}
|
}
|
|
/** 找行内第二个市州名列（右半市州名），找不到返回 -1（回退 J 列起 12 列） */
|
private int findSecondRegionCol(Row row) {
|
String firstName = getString(row, 0);
|
if (firstName == null) return -1;
|
for (int c = 1; c <= 20; c++) {
|
String v = getString(row, c);
|
if (v != null && !v.isEmpty()
|
&& RegionUtil.normalizeCityName(v).equals(RegionUtil.normalizeCityName(firstName))) {
|
return c;
|
}
|
}
|
return -1;
|
}
|
|
private Double getFreight(FreightTurnoverImport e, int m) {
|
return monthValue(m, e::getFreightM01, e::getFreightM02, e::getFreightM03, e::getFreightM04,
|
e::getFreightM05, e::getFreightM06, e::getFreightM07, e::getFreightM08,
|
e::getFreightM09, e::getFreightM10, e::getFreightM11, e::getFreightM12);
|
}
|
|
private Double getLastFreight(FreightTurnoverImport e, int m) {
|
return monthValue(m, e::getLastFreightM01, e::getLastFreightM02, e::getLastFreightM03, e::getLastFreightM04,
|
e::getLastFreightM05, e::getLastFreightM06, e::getLastFreightM07, e::getLastFreightM08,
|
e::getLastFreightM09, e::getLastFreightM10, e::getLastFreightM11, e::getLastFreightM12);
|
}
|
|
private Double getTurnover(FreightTurnoverImport e, int m) {
|
return monthValue(m, e::getTurnoverM01, e::getTurnoverM02, e::getTurnoverM03, e::getTurnoverM04,
|
e::getTurnoverM05, e::getTurnoverM06, e::getTurnoverM07, e::getTurnoverM08,
|
e::getTurnoverM09, e::getTurnoverM10, e::getTurnoverM11, e::getTurnoverM12);
|
}
|
|
private Double getLastTurnover(FreightTurnoverImport e, int m) {
|
return monthValue(m, e::getLastTurnoverM01, e::getLastTurnoverM02, e::getLastTurnoverM03, e::getLastTurnoverM04,
|
e::getLastTurnoverM05, e::getLastTurnoverM06, e::getLastTurnoverM07, e::getLastTurnoverM08,
|
e::getLastTurnoverM09, e::getLastTurnoverM10, e::getLastTurnoverM11, e::getLastTurnoverM12);
|
}
|
|
private Double monthValue(int m, java.util.function.Supplier<Double> g1, java.util.function.Supplier<Double> g2,
|
java.util.function.Supplier<Double> g3, java.util.function.Supplier<Double> g4,
|
java.util.function.Supplier<Double> g5, java.util.function.Supplier<Double> g6,
|
java.util.function.Supplier<Double> g7, java.util.function.Supplier<Double> g8,
|
java.util.function.Supplier<Double> g9, java.util.function.Supplier<Double> g10,
|
java.util.function.Supplier<Double> g11, java.util.function.Supplier<Double> g12) {
|
switch (m) {
|
case 1: return g1.get();
|
case 2: return g2.get();
|
case 3: return g3.get();
|
case 4: return g4.get();
|
case 5: return g5.get();
|
case 6: return g6.get();
|
case 7: return g7.get();
|
case 8: return g8.get();
|
case 9: return g9.get();
|
case 10: return g10.get();
|
case 11: return g11.get();
|
default: return g12.get();
|
}
|
}
|
|
private void setFreight(FreightTurnoverImport e, int m, Double v) {
|
switch (m) {
|
case 1: e.setFreightM01(v); break;
|
case 2: e.setFreightM02(v); break;
|
case 3: e.setFreightM03(v); break;
|
case 4: e.setFreightM04(v); break;
|
case 5: e.setFreightM05(v); break;
|
case 6: e.setFreightM06(v); break;
|
case 7: e.setFreightM07(v); break;
|
case 8: e.setFreightM08(v); break;
|
case 9: e.setFreightM09(v); break;
|
case 10: e.setFreightM10(v); break;
|
case 11: e.setFreightM11(v); break;
|
case 12: e.setFreightM12(v); break;
|
default: break;
|
}
|
}
|
|
private void setLastFreight(FreightTurnoverImport e, int m, Double v) {
|
switch (m) {
|
case 1: e.setLastFreightM01(v); break;
|
case 2: e.setLastFreightM02(v); break;
|
case 3: e.setLastFreightM03(v); break;
|
case 4: e.setLastFreightM04(v); break;
|
case 5: e.setLastFreightM05(v); break;
|
case 6: e.setLastFreightM06(v); break;
|
case 7: e.setLastFreightM07(v); break;
|
case 8: e.setLastFreightM08(v); break;
|
case 9: e.setLastFreightM09(v); break;
|
case 10: e.setLastFreightM10(v); break;
|
case 11: e.setLastFreightM11(v); break;
|
case 12: e.setLastFreightM12(v); break;
|
default: break;
|
}
|
}
|
|
private void setTurnover(FreightTurnoverImport e, int m, Double v) {
|
switch (m) {
|
case 1: e.setTurnoverM01(v); break;
|
case 2: e.setTurnoverM02(v); break;
|
case 3: e.setTurnoverM03(v); break;
|
case 4: e.setTurnoverM04(v); break;
|
case 5: e.setTurnoverM05(v); break;
|
case 6: e.setTurnoverM06(v); break;
|
case 7: e.setTurnoverM07(v); break;
|
case 8: e.setTurnoverM08(v); break;
|
case 9: e.setTurnoverM09(v); break;
|
case 10: e.setTurnoverM10(v); break;
|
case 11: e.setTurnoverM11(v); break;
|
case 12: e.setTurnoverM12(v); break;
|
default: break;
|
}
|
}
|
|
private void setLastTurnover(FreightTurnoverImport e, int m, Double v) {
|
switch (m) {
|
case 1: e.setLastTurnoverM01(v); break;
|
case 2: e.setLastTurnoverM02(v); break;
|
case 3: e.setLastTurnoverM03(v); break;
|
case 4: e.setLastTurnoverM04(v); break;
|
case 5: e.setLastTurnoverM05(v); break;
|
case 6: e.setLastTurnoverM06(v); break;
|
case 7: e.setLastTurnoverM07(v); break;
|
case 8: e.setLastTurnoverM08(v); break;
|
case 9: e.setLastTurnoverM09(v); break;
|
case 10: e.setLastTurnoverM10(v); break;
|
case 11: e.setLastTurnoverM11(v); break;
|
case 12: e.setLastTurnoverM12(v); break;
|
default: break;
|
}
|
}
|
|
// ========== 辅助方法 ==========
|
|
private void recordBatch(String fileName, String importType, String period,
|
int total, int success, int fail, String errorDetail) {
|
try {
|
ImportBatch batch = new ImportBatch();
|
batch.setFileName(fileName);
|
batch.setImportType(importType);
|
batch.setReportPeriod(period);
|
batch.setTotalRows(total);
|
batch.setSuccessRows(success);
|
batch.setFailRows(fail);
|
batch.setErrorDetail(errorDetail);
|
importBatchMapper.insert(batch);
|
} catch (Exception e) {
|
log.error("record batch error", e);
|
}
|
}
|
|
/** 支持“按类型清空”的数据类型 -> 数据表操作键（同表同类别归并，避免重复删除） */
|
private static final Map<String, String> CLEAR_TYPE_TO_OP = new LinkedHashMap<>();
|
/** 数据类型 -> 关联审核规则类型（同步清除审核结果与已审核标记） */
|
private static final Map<String, String[]> CLEAR_TYPE_TO_AUDIT = new HashMap<>();
|
/** 数据类型 -> 关联导入批次 importType（同步清除导入批次记录） */
|
private static final Map<String, String[]> CLEAR_TYPE_TO_BATCH = new HashMap<>();
|
|
static {
|
CLEAR_TYPE_TO_OP.put("h2032", "H2032");
|
CLEAR_TYPE_TO_OP.put("transportAuth", "TRANSPORT_AUTH");
|
CLEAR_TYPE_TO_OP.put("trackMileage", "TRACK_MILEAGE");
|
CLEAR_TYPE_TO_OP.put("scaleSplit", "SCALE_SPLIT");
|
CLEAR_TYPE_TO_OP.put("freightTurnover", "FREIGHT_TURNOVER");
|
CLEAR_TYPE_TO_OP.put("passengerMonthly", "H2031");
|
CLEAR_TYPE_TO_OP.put("passengerAuth", "PASSENGER_AUTH");
|
CLEAR_TYPE_TO_OP.put("passengerIndividual", "PASSENGER_INDIVIDUAL");
|
CLEAR_TYPE_TO_OP.put("energyMonthly", "H204");
|
CLEAR_TYPE_TO_OP.put("energyAuth", "ENERGY_AUTH");
|
CLEAR_TYPE_TO_OP.put("investment", "INVEST_MONTHLY_PASSENGER");
|
CLEAR_TYPE_TO_OP.put("investCityPassenger", "INVEST_MONTHLY_PASSENGER");
|
CLEAR_TYPE_TO_OP.put("investmentLogistics", "INVEST_MONTHLY_LOGISTICS");
|
CLEAR_TYPE_TO_OP.put("investCityLogistics", "INVEST_MONTHLY_LOGISTICS");
|
CLEAR_TYPE_TO_OP.put("investmentSystem", "INVEST_SYSTEM");
|
CLEAR_TYPE_TO_OP.put("cityBus", "CITY_BUS");
|
CLEAR_TYPE_TO_OP.put("cityTaxi", "CITY_TAXI");
|
CLEAR_TYPE_TO_OP.put("cityTaxiAuth", "CITY_TAXI_AUTH");
|
CLEAR_TYPE_TO_OP.put("wycTotal", "WYC_TOTAL");
|
CLEAR_TYPE_TO_OP.put("wycOrder", "WYC_ORDER");
|
CLEAR_TYPE_TO_OP.put("wycOrderReport", "WYC_ORDER");
|
|
CLEAR_TYPE_TO_AUDIT.put("h2032", new String[]{"H2032"});
|
CLEAR_TYPE_TO_AUDIT.put("transportAuth", new String[]{"H2032"});
|
CLEAR_TYPE_TO_AUDIT.put("trackMileage", new String[]{"H2032"});
|
CLEAR_TYPE_TO_AUDIT.put("scaleSplit", new String[]{"H2032"});
|
CLEAR_TYPE_TO_AUDIT.put("freightTurnover", new String[]{"H2032"});
|
CLEAR_TYPE_TO_AUDIT.put("passengerMonthly", new String[]{"H2031"});
|
CLEAR_TYPE_TO_AUDIT.put("passengerAuth", new String[]{"H2031"});
|
CLEAR_TYPE_TO_AUDIT.put("passengerIndividual", new String[]{"H2031"});
|
CLEAR_TYPE_TO_AUDIT.put("energyMonthly", new String[]{"H204"});
|
CLEAR_TYPE_TO_AUDIT.put("energyAuth", new String[]{"H204"});
|
CLEAR_TYPE_TO_AUDIT.put("investment", new String[]{"INVEST"});
|
CLEAR_TYPE_TO_AUDIT.put("investmentLogistics", new String[]{"INVEST"});
|
CLEAR_TYPE_TO_AUDIT.put("investCityPassenger", new String[]{"INVEST"});
|
CLEAR_TYPE_TO_AUDIT.put("investCityLogistics", new String[]{"INVEST"});
|
CLEAR_TYPE_TO_AUDIT.put("investmentSystem", new String[]{"INVEST"});
|
CLEAR_TYPE_TO_AUDIT.put("cityBus", new String[]{"CITY_BUS"});
|
CLEAR_TYPE_TO_AUDIT.put("cityTaxi", new String[]{"CITY_TAXI"});
|
CLEAR_TYPE_TO_AUDIT.put("cityTaxiAuth", new String[]{"CITY_TAXI"});
|
|
String[] investPassengerBatch = {"INVEST_客运站场", "INVEST_客运站场_CITY"};
|
String[] investLogisticsBatch = {"INVEST_物流园区", "INVEST_物流园区_CITY"};
|
CLEAR_TYPE_TO_BATCH.put("h2032", new String[]{"H2032"});
|
CLEAR_TYPE_TO_BATCH.put("transportAuth", new String[]{"TRANSPORT_AUTH"});
|
CLEAR_TYPE_TO_BATCH.put("trackMileage", new String[]{"TRACK_MILEAGE"});
|
CLEAR_TYPE_TO_BATCH.put("scaleSplit", new String[]{"SCALE_SPLIT"});
|
CLEAR_TYPE_TO_BATCH.put("freightTurnover", new String[]{"FREIGHT_TURNOVER"});
|
CLEAR_TYPE_TO_BATCH.put("passengerMonthly", new String[]{"H2031"});
|
CLEAR_TYPE_TO_BATCH.put("passengerAuth", new String[]{"PASSENGER_AUTH"});
|
CLEAR_TYPE_TO_BATCH.put("passengerIndividual", new String[]{"PASSENGER_INDIVIDUAL"});
|
CLEAR_TYPE_TO_BATCH.put("energyMonthly", new String[]{"H204"});
|
CLEAR_TYPE_TO_BATCH.put("energyAuth", new String[]{"ENERGY_AUTH"});
|
CLEAR_TYPE_TO_BATCH.put("investment", investPassengerBatch);
|
CLEAR_TYPE_TO_BATCH.put("investCityPassenger", investPassengerBatch);
|
CLEAR_TYPE_TO_BATCH.put("investmentLogistics", investLogisticsBatch);
|
CLEAR_TYPE_TO_BATCH.put("investCityLogistics", investLogisticsBatch);
|
CLEAR_TYPE_TO_BATCH.put("investmentSystem", new String[]{"INVEST_SYSTEM"});
|
CLEAR_TYPE_TO_BATCH.put("cityBus", new String[]{"CITY_BUS"});
|
CLEAR_TYPE_TO_BATCH.put("cityTaxi", new String[]{"CITY_TAXI"});
|
CLEAR_TYPE_TO_BATCH.put("cityTaxiAuth", new String[]{"CITY_TAXI_AUTH"});
|
CLEAR_TYPE_TO_BATCH.put("wycTotal", new String[]{"WYC_TOTAL"});
|
CLEAR_TYPE_TO_BATCH.put("wycOrder", new String[]{"WYC_ORDER", "WYC_ORDER_REPORT"});
|
CLEAR_TYPE_TO_BATCH.put("wycOrderReport", new String[]{"WYC_ORDER_REPORT", "WYC_ORDER"});
|
}
|
|
/**
|
* 清空指定报表期已导入数据与审核结果，用于整月重导；投资项目主档保留。
|
*
|
* @param types 需清空的数据类型（前端类型键，如 h2032/cityBus）；为空则清空该报表期全部类型
|
*/
|
@Transactional(rollbackFor = Exception.class)
|
public Map<String, Object> clearPeriod(String period, List<String> types) {
|
if (period == null || !period.matches("\\d{4}-\\d{2}")) {
|
throw new IllegalArgumentException("报表期格式不正确，应为 yyyy-MM");
|
}
|
Set<String> selected = new LinkedHashSet<>();
|
if (types != null) {
|
for (String t : types) {
|
if (t != null && !t.trim().isEmpty()) selected.add(t.trim());
|
}
|
}
|
boolean clearAll = selected.isEmpty();
|
int dataRows = 0;
|
int auditRows = 0;
|
int auditRunRows = 0;
|
int batchRows = 0;
|
if (clearAll) {
|
dataRows += h2032Mapper.delete(new LambdaQueryWrapper<H2032EnterpriseMonthly>()
|
.eq(H2032EnterpriseMonthly::getReportPeriod, period));
|
dataRows += transportAuthMapper.delete(new LambdaQueryWrapper<TransportAuthVehicle>()
|
.eq(TransportAuthVehicle::getReportPeriod, period));
|
dataRows += trackMileageMapper.delete(new LambdaQueryWrapper<VehicleTrackMileage>()
|
.eq(VehicleTrackMileage::getReportPeriod, period));
|
dataRows += scaleSplitMapper.delete(new LambdaQueryWrapper<ScaleSplitTransport>()
|
.eq(ScaleSplitTransport::getReportPeriod, period));
|
dataRows += freightTurnoverMapper.delete(new LambdaQueryWrapper<FreightTurnoverImport>()
|
.eq(FreightTurnoverImport::getReportPeriod, period));
|
dataRows += passengerMapper.delete(new LambdaQueryWrapper<PassengerEnterpriseMonthly>()
|
.eq(PassengerEnterpriseMonthly::getReportPeriod, period));
|
dataRows += passengerIndividualMapper.delete(new LambdaQueryWrapper<PassengerIndividualMonthly>()
|
.eq(PassengerIndividualMonthly::getReportPeriod, period));
|
dataRows += passengerAuthMapper.delete(new LambdaQueryWrapper<PassengerAuthVehicle>()
|
.eq(PassengerAuthVehicle::getReportPeriod, period));
|
dataRows += energyMapper.delete(new LambdaQueryWrapper<EnergyVehicleQuarterly>()
|
.eq(EnergyVehicleQuarterly::getReportPeriod, period));
|
dataRows += energyAuthMapper.delete(new LambdaQueryWrapper<EnergyAuthVehicle>()
|
.eq(EnergyAuthVehicle::getReportPeriod, period));
|
dataRows += investMonthlyMapper.delete(new LambdaQueryWrapper<InvestmentMonthly>()
|
.eq(InvestmentMonthly::getReportPeriod, period));
|
dataRows += investSystemMapper.delete(new LambdaQueryWrapper<InvestmentSystem>()
|
.eq(InvestmentSystem::getReportPeriod, period));
|
dataRows += cityBusMapper.delete(new LambdaQueryWrapper<CityBusMonthly>()
|
.eq(CityBusMonthly::getReportPeriod, period));
|
dataRows += cityTaxiMapper.delete(new LambdaQueryWrapper<CityTaxiMonthly>()
|
.eq(CityTaxiMonthly::getReportPeriod, period));
|
dataRows += cityTaxiAuthMapper.delete(new LambdaQueryWrapper<CityTaxiAuth>()
|
.eq(CityTaxiAuth::getReportPeriod, period));
|
dataRows += wycTotalMapper.delete(new LambdaQueryWrapper<WycTotalMonthly>()
|
.eq(WycTotalMonthly::getReportPeriod, period));
|
dataRows += wycOrderMapper.delete(new LambdaQueryWrapper<WycOrderMonthly>()
|
.eq(WycOrderMonthly::getReportPeriod, period));
|
auditRows = auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
|
.eq(AuditResult::getReportPeriod, period));
|
auditRunRows = auditRunMapper.delete(new LambdaQueryWrapper<AuditRun>()
|
.eq(AuditRun::getReportPeriod, period));
|
batchRows = importBatchMapper.delete(new LambdaQueryWrapper<ImportBatch>()
|
.eq(ImportBatch::getReportPeriod, period));
|
} else {
|
Set<String> ops = new LinkedHashSet<>();
|
Set<String> auditTypes = new LinkedHashSet<>();
|
Set<String> batchTypes = new LinkedHashSet<>();
|
for (String t : selected) {
|
String op = CLEAR_TYPE_TO_OP.get(t);
|
if (op == null) {
|
log.warn("清空数据：未知数据类型 {}，已忽略", t);
|
continue;
|
}
|
ops.add(op);
|
String[] at = CLEAR_TYPE_TO_AUDIT.get(t);
|
if (at != null) java.util.Collections.addAll(auditTypes, at);
|
String[] bt = CLEAR_TYPE_TO_BATCH.get(t);
|
if (bt != null) java.util.Collections.addAll(batchTypes, bt);
|
}
|
if (ops.isEmpty()) {
|
throw new IllegalArgumentException("未指定有效的数据类型");
|
}
|
for (String op : ops) {
|
dataRows += clearTypeData(op, period);
|
}
|
if (!auditTypes.isEmpty()) {
|
List<Long> ruleIds = new ArrayList<>();
|
for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {
|
if (rule.getReportType() != null && auditTypes.contains(rule.getReportType())) {
|
ruleIds.add(rule.getId());
|
}
|
}
|
if (!ruleIds.isEmpty()) {
|
auditRows = auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
|
.eq(AuditResult::getReportPeriod, period)
|
.in(AuditResult::getRuleId, ruleIds));
|
}
|
for (String rt : auditTypes) {
|
auditRunRows += auditRunMapper.delete(new LambdaQueryWrapper<AuditRun>()
|
.eq(AuditRun::getReportPeriod, period)
|
.eq(AuditRun::getReportType, rt));
|
}
|
}
|
if (!batchTypes.isEmpty()) {
|
batchRows = importBatchMapper.delete(new LambdaQueryWrapper<ImportBatch>()
|
.eq(ImportBatch::getReportPeriod, period)
|
.in(ImportBatch::getImportType, batchTypes));
|
}
|
}
|
Map<String, Object> stats = new LinkedHashMap<>();
|
stats.put("period", period);
|
stats.put("scope", clearAll ? "all" : "types");
|
if (!clearAll) stats.put("types", new ArrayList<>(selected));
|
stats.put("dataRows", dataRows);
|
stats.put("auditRows", auditRows);
|
stats.put("auditRunRows", auditRunRows);
|
stats.put("batchRows", batchRows);
|
log.info("清空报表期 {}（{}）完成：业务数据 {} 行、审核结果 {} 行、审核标记 {} 行、导入批次 {} 行",
|
period, clearAll ? "全部类型" : String.join(",", selected), dataRows, auditRows, auditRunRows, batchRows);
|
return stats;
|
}
|
|
/** 按“数据表操作键”清空对应业务表（投资月度按项目类别过滤），返回删除行数 */
|
private int clearTypeData(String op, String period) {
|
switch (op) {
|
case "H2032":
|
return h2032Mapper.delete(new LambdaQueryWrapper<H2032EnterpriseMonthly>()
|
.eq(H2032EnterpriseMonthly::getReportPeriod, period));
|
case "TRANSPORT_AUTH":
|
return transportAuthMapper.delete(new LambdaQueryWrapper<TransportAuthVehicle>()
|
.eq(TransportAuthVehicle::getReportPeriod, period));
|
case "TRACK_MILEAGE":
|
return trackMileageMapper.delete(new LambdaQueryWrapper<VehicleTrackMileage>()
|
.eq(VehicleTrackMileage::getReportPeriod, period));
|
case "SCALE_SPLIT":
|
return scaleSplitMapper.delete(new LambdaQueryWrapper<ScaleSplitTransport>()
|
.eq(ScaleSplitTransport::getReportPeriod, period));
|
case "FREIGHT_TURNOVER":
|
return freightTurnoverMapper.delete(new LambdaQueryWrapper<FreightTurnoverImport>()
|
.eq(FreightTurnoverImport::getReportPeriod, period));
|
case "H2031":
|
return passengerMapper.delete(new LambdaQueryWrapper<PassengerEnterpriseMonthly>()
|
.eq(PassengerEnterpriseMonthly::getReportPeriod, period));
|
case "PASSENGER_AUTH":
|
return passengerAuthMapper.delete(new LambdaQueryWrapper<PassengerAuthVehicle>()
|
.eq(PassengerAuthVehicle::getReportPeriod, period));
|
case "PASSENGER_INDIVIDUAL":
|
return passengerIndividualMapper.delete(new LambdaQueryWrapper<PassengerIndividualMonthly>()
|
.eq(PassengerIndividualMonthly::getReportPeriod, period));
|
case "H204":
|
return energyMapper.delete(new LambdaQueryWrapper<EnergyVehicleQuarterly>()
|
.eq(EnergyVehicleQuarterly::getReportPeriod, period));
|
case "ENERGY_AUTH":
|
return energyAuthMapper.delete(new LambdaQueryWrapper<EnergyAuthVehicle>()
|
.eq(EnergyAuthVehicle::getReportPeriod, period));
|
case "INVEST_MONTHLY_PASSENGER":
|
return deleteInvestmentMonthly(period, "客运站场");
|
case "INVEST_MONTHLY_LOGISTICS":
|
return deleteInvestmentMonthly(period, "物流园区");
|
case "INVEST_SYSTEM":
|
return investSystemMapper.delete(new LambdaQueryWrapper<InvestmentSystem>()
|
.eq(InvestmentSystem::getReportPeriod, period));
|
case "CITY_BUS":
|
return cityBusMapper.delete(new LambdaQueryWrapper<CityBusMonthly>()
|
.eq(CityBusMonthly::getReportPeriod, period));
|
case "CITY_TAXI":
|
return cityTaxiMapper.delete(new LambdaQueryWrapper<CityTaxiMonthly>()
|
.eq(CityTaxiMonthly::getReportPeriod, period));
|
case "CITY_TAXI_AUTH":
|
return cityTaxiAuthMapper.delete(new LambdaQueryWrapper<CityTaxiAuth>()
|
.eq(CityTaxiAuth::getReportPeriod, period));
|
case "WYC_TOTAL":
|
return wycTotalMapper.delete(new LambdaQueryWrapper<WycTotalMonthly>()
|
.eq(WycTotalMonthly::getReportPeriod, period));
|
case "WYC_ORDER":
|
return wycOrderMapper.delete(new LambdaQueryWrapper<WycOrderMonthly>()
|
.eq(WycOrderMonthly::getReportPeriod, period));
|
default:
|
log.warn("清空数据：未知数据表操作 {}", op);
|
return 0;
|
}
|
}
|
|
/** 投资月度按项目类别清除（投资项目主档保留） */
|
private int deleteInvestmentMonthly(String period, String category) {
|
List<Long> projectIds = new ArrayList<>();
|
for (InvestmentProject p : investProjectMapper.selectList(new LambdaQueryWrapper<InvestmentProject>()
|
.eq(InvestmentProject::getCategory, category))) {
|
projectIds.add(p.getId());
|
}
|
if (projectIds.isEmpty()) return 0;
|
return investMonthlyMapper.delete(new LambdaQueryWrapper<InvestmentMonthly>()
|
.eq(InvestmentMonthly::getReportPeriod, period)
|
.in(InvestmentMonthly::getProjectId, projectIds));
|
}
|
|
/** 数据变化后删除指定报表类型指定报表期的审核结果（需重新执行审核） */
|
private void deleteRuleResults(String reportType, String period) {
|
List<Long> ruleIds = new ArrayList<>();
|
for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {
|
if (reportType.equals(rule.getReportType())) ruleIds.add(rule.getId());
|
}
|
if (!ruleIds.isEmpty()) {
|
auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
|
.eq(AuditResult::getReportPeriod, period)
|
.in(AuditResult::getRuleId, ruleIds));
|
}
|
}
|
|
/** 类型-表头校验：按必需列名（解析器实际依赖的列），缺失即抛"类型与文件不符" */
|
private void requireHeaderCols(Sheet sheet, String typeLabel, String... cols) {
|
Map<String, Integer> colMap = buildHeaderMap(sheet.getRow(0));
|
List<String> missing = new ArrayList<>();
|
for (String c : cols) {
|
if (!colMap.containsKey(c)) missing.add(c);
|
}
|
if (!missing.isEmpty()) {
|
throw new RuntimeException("所选数据类型【" + typeLabel + "】与文件内容不符：表头缺少必需列【" + String.join("】【", missing) + "】，请确认是否选错了数据类型");
|
}
|
}
|
|
/** 类型-表头校验：扫描表头行，命中任一关键字即通过 */
|
private void requireHeaderKeyword(Sheet sheet, String typeLabel, String... keywords) {
|
Row header = sheet == null ? null : sheet.getRow(0);
|
if (header != null) {
|
for (Cell cell : header) {
|
String v = FORMATTER.formatCellValue(cell);
|
if (v == null || v.isEmpty()) continue;
|
for (String k : keywords) {
|
if (v.contains(k)) return;
|
}
|
}
|
}
|
throw new RuntimeException("所选数据类型【" + typeLabel + "】与文件内容不符：表头未包含【" + String.join("】【", keywords) + "】等列，请确认是否选错了数据类型");
|
}
|
|
/** 能耗明细表头列位校验：系统模板为“前3列 + 5组×18列”，程序按固定列位解析，表头错位会导致整表错乱，错位时直接拒绝 */
|
private void requireEnergyHeaderLayout(Sheet sheet) {
|
Row header = sheet == null ? null : sheet.getRow(0);
|
if (header == null) {
|
throw new RuntimeException("能耗明细表头缺失，请使用系统模板《模板_道路货运车辆能源消耗情况（2026）.xlsx》整理后再导入");
|
}
|
for (int i = 0; i < ENERGY_HEADER_COLS.length; i++) {
|
int col = ENERGY_HEADER_COLS[i];
|
String keyword = ENERGY_HEADER_KEYWORDS[i];
|
Cell cell = header.getCell(col);
|
String hv = cell == null ? null : FORMATTER.formatCellValue(cell).trim();
|
if (hv == null || hv.isEmpty() || !hv.contains(keyword)) {
|
throw new RuntimeException("表头与系统模板不一致：第 " + (col + 1) + " 列应为「" + keyword + "」，实际为「"
|
+ (hv == null || hv.isEmpty() ? "空" : hv) + "」。请使用系统模板《模板_道路货运车辆能源消耗情况（2026）.xlsx》整理后再导入");
|
}
|
}
|
}
|
|
/** 通用表头列位校验：程序按固定列位解析，表头错位会导致整表错乱，错位时直接拒绝导入 */
|
private void requireHeaderLayout(Sheet sheet, String typeLabel, String templateHint,
|
int[] cols, String[] keywords) {
|
Row header = sheet == null ? null : sheet.getRow(0);
|
if (header == null) {
|
throw new RuntimeException("所选数据类型【" + typeLabel + "】文件表头缺失，请使用系统模板《" + templateHint + "》整理后再导入");
|
}
|
for (int i = 0; i < cols.length; i++) {
|
int col = cols[i];
|
String keyword = keywords[i];
|
Cell cell = header.getCell(col);
|
String hv = cell == null ? null : FORMATTER.formatCellValue(cell).trim();
|
if (hv == null || hv.isEmpty() || !hv.contains(keyword)) {
|
throw new RuntimeException("表头与系统模板不一致：第 " + (col + 1) + " 列应为「" + keyword + "」，实际为「"
|
+ (hv == null || hv.isEmpty() ? "空" : hv) + "」。所选数据类型【" + typeLabel + "】请使用系统模板《" + templateHint + "》整理后再导入");
|
}
|
}
|
}
|
|
/** 按表头关键字定位列号：从第 1 列向右扫，命中任一关键字即返回该列；未命中返回 -1 */
|
private int findColByKeywords(Row header, String... keywords) {
|
if (header == null) return -1;
|
for (int c = 0; c <= header.getLastCellNum(); c++) {
|
Cell cell = header.getCell(c);
|
if (cell == null) continue;
|
String h = FORMATTER.formatCellValue(cell).trim();
|
if (h.isEmpty()) continue;
|
for (String k : keywords) {
|
if (h.contains(k)) return c;
|
}
|
}
|
return -1;
|
}
|
|
/** 表头行文本（报错提示用），最多取 12 列 */
|
private String headerText(Row header) {
|
if (header == null) return "空";
|
StringBuilder sb = new StringBuilder();
|
for (int c = 0; c < Math.min(header.getLastCellNum(), 12); c++) {
|
Cell cell = header.getCell(c);
|
String h = cell == null ? "" : FORMATTER.formatCellValue(cell).trim();
|
if (h.isEmpty()) continue;
|
if (sb.length() > 0) sb.append(" | ");
|
sb.append(h);
|
}
|
return sb.length() == 0 ? "空" : sb.toString();
|
}
|
|
/** 按表头名称建立列号映射，兼容新旧模板列位差异 */
|
private Map<String, Integer> buildHeaderMap(Row header) {
|
Map<String, Integer> colMap = new HashMap<>();
|
if (header == null) return colMap;
|
for (Cell cell : header) {
|
String h = FORMATTER.formatCellValue(cell).trim();
|
if (!h.isEmpty() && !colMap.containsKey(h)) {
|
colMap.put(h, cell.getColumnIndex());
|
}
|
}
|
return colMap;
|
}
|
|
/** 兼容表头映射：去引号/去空白；纯英文表头统一大写（如 'OWNERNAME' → OWNERNAME），便于新旧模板混用 */
|
private Map<String, Integer> buildHeaderMapCompat(Row header) {
|
Map<String, Integer> colMap = new HashMap<>();
|
if (header == null) return colMap;
|
for (Cell cell : header) {
|
if (cell == null) continue;
|
String h = FORMATTER.formatCellValue(cell).replace("'", "").replace("\"", "").trim();
|
if (h.isEmpty()) continue;
|
boolean ascii = true;
|
for (int i = 0; i < h.length(); i++) {
|
if (h.charAt(i) > 127) { ascii = false; break; }
|
}
|
if (ascii) h = h.toUpperCase();
|
if (!colMap.containsKey(h)) colMap.put(h, cell.getColumnIndex());
|
}
|
return colMap;
|
}
|
|
/** 按别名顺序取第一个存在的列号，全部缺失返回 null */
|
private Integer firstColIdx(Map<String, Integer> colMap, String... aliases) {
|
for (String a : aliases) {
|
Integer idx = colMap.get(a);
|
if (idx != null) return idx;
|
}
|
return null;
|
}
|
|
private int colIdx(Map<String, Integer> colMap, String header) {
|
Integer idx = colMap.get(header);
|
return idx == null ? -1 : idx;
|
}
|
|
private String gs(Row row, Map<String, Integer> colMap, String header) {
|
int idx = colIdx(colMap, header);
|
return idx < 0 ? null : getString(row, idx);
|
}
|
|
private Double gd(Row row, Map<String, Integer> colMap, String header) {
|
int idx = colIdx(colMap, header);
|
return idx < 0 ? 0.0 : getDecimal(row, idx);
|
}
|
|
private Integer gi(Row row, Map<String, Integer> colMap, String header) {
|
int idx = colIdx(colMap, header);
|
return idx < 0 ? 0 : getInt(row, idx);
|
}
|
|
private String gds(Row row, Map<String, Integer> colMap, String header) {
|
int idx = colIdx(colMap, header);
|
return idx < 0 ? null : getDateString(row, idx);
|
}
|
|
/** 报出日期：Excel 日期单元格或文本统一转为 yyyy-MM-dd，空值返回 null */
|
private String getDateString(Row row, int idx) {
|
Cell cell = row.getCell(idx);
|
if (cell == null) return null;
|
if (cell.getCellType() == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {
|
return new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
|
}
|
String val = FORMATTER.formatCellValue(cell).trim();
|
if (val.isEmpty()) return null;
|
return val.length() > 10 ? val.substring(0, 10) : val;
|
}
|
|
/** H2032 行记录：保留 Excel 行号用于失败定位 */
|
private static class H2032Row {
|
final int excelRow;
|
final H2032EnterpriseMonthly entity;
|
|
H2032Row(int excelRow, H2032EnterpriseMonthly entity) {
|
this.excelRow = excelRow;
|
this.entity = entity;
|
}
|
}
|
|
private static class PassengerRow {
|
int excelRow;
|
PassengerEnterpriseMonthly entity;
|
|
PassengerRow(int excelRow, PassengerEnterpriseMonthly entity) {
|
this.excelRow = excelRow;
|
this.entity = entity;
|
}
|
}
|
|
|
private String getString(Row row, int idx) {
|
Cell cell = row.getCell(idx);
|
if (cell == null) return null;
|
// 公式单元格：读缓存字符串（.et/WPS 文件常见，DataFormatter 无 evaluator 时只返回公式文本）
|
if (cell.getCellType() == CellType.FORMULA && cell.getCachedFormulaResultType() == CellType.STRING) {
|
String cached = cell.getStringCellValue();
|
cached = cached == null ? null : cached.trim();
|
return cached == null || cached.isEmpty() ? null : cached;
|
}
|
String val = FORMATTER.formatCellValue(cell).trim();
|
return val.isEmpty() ? null : val;
|
}
|
|
private Double getDecimal(Row row, int idx) {
|
Cell cell = row.getCell(idx);
|
if (cell == null) return 0.0;
|
// 数值单元格直接取原始值，避免被 Excel 显示格式（百分比/0_ 等）影响解析
|
if (cell.getCellType() == CellType.NUMERIC) {
|
return cell.getNumericCellValue();
|
}
|
// 公式单元格：读缓存数值（.et/WPS 跨表公式常见，DataFormatter 无 evaluator 时只返回公式文本）
|
if (cell.getCellType() == CellType.FORMULA && cell.getCachedFormulaResultType() == CellType.NUMERIC) {
|
return cell.getNumericCellValue();
|
}
|
String val = FORMATTER.formatCellValue(cell).trim();
|
if (val.isEmpty()) return 0.0;
|
try {
|
return Double.parseDouble(val.replace(",", ""));
|
} catch (NumberFormatException e) {
|
return 0.0;
|
}
|
}
|
|
private Integer getInt(Row row, int idx) {
|
Double val = getDecimal(row, idx);
|
if (val == null) return 0;
|
return val.intValue();
|
}
|
|
/** 空单元格返回 null（用于货运量周转量模板，未填月份与 0 区分） */
|
private Double getDecimalOrNull(Row row, int idx) {
|
Cell cell = row.getCell(idx);
|
if (cell == null) return null;
|
if (cell.getCellType() == CellType.NUMERIC) {
|
return cell.getNumericCellValue();
|
}
|
if (cell.getCellType() == CellType.FORMULA && cell.getCachedFormulaResultType() == CellType.NUMERIC) {
|
return cell.getNumericCellValue();
|
}
|
String val = FORMATTER.formatCellValue(cell).trim();
|
if (val.isEmpty()) return null;
|
try {
|
return Double.parseDouble(val.replace(",", ""));
|
} catch (NumberFormatException e) {
|
return null;
|
}
|
}
|
// ========== 投资模块（客运站场 / 物流园区，全省汇总大表） ==========
|
|
/** 亿元投资项目固定名单（2026年规上项目表） */
|
private static final java.util.Set<String> INVEST_BILLION_NAMES = new LinkedHashSet<>(Arrays.asList(
|
"汉口客运中心", "钟祥市综合交通客运枢纽一期项目", "航发智慧物流园", "国胜公路港",
|
"十堰生产服务型国家物流枢纽工程项目", "秭归县脐橙产业综合物流中心", "宜都供销商贸物流园",
|
"长阳县城乡冷链物流园", "宜昌港兴山港区管公水多式联运建设项目", "玉湖冷链（襄阳）交易中心项目",
|
"武汉万吨华中冷链港二期项目", "湖北长江现代物流产业集聚示范区·现代物流园",
|
"鄂州花湖国际机场空港型国家物流枢纽智慧公共国际货站项目", "荆门智慧冷链物流园",
|
"荆门北站顺洋物流基地（荆门北子陵铁路物流园）", "沙洋多式联运现代物流园一期（沙洋煤炭储备•集装箱散改集基地）",
|
"首衡城智慧冷链物流二期项目G区", "孝昌广浔智慧物流电商产业园", "申通（孝感）智慧物流电商产业园（三期）",
|
"公安县多式联运综合物流园", "荆州市铁水联运物流中心", "荆州市瑞海国际物流园一期", "荆州经开区智能仓储物流园项目",
|
"鄂东（黄冈）数智物流综合服务中心",
|
"鄂湘赣商贸物流中心一期（通城县物流配送中心、鄂南智慧物流产业园建设、鄂南智慧物流产业园建设项目（二期）、冷链配送中心）项目",
|
"咸宁国际陆港物流园-咸宁市国际陆港综合物流园（一期）", "咸宁国际陆港物流园-新建咸宁铁路物流基地项目",
|
"崇阳县综合物流园及配套设施建设项目", "恩施七里坪物流园", "职业工装产业集聚区仓储物流项目",
|
"极兔速递华中物流枢纽基地项目", "湖北迈睿达供应链总部研发及生产基地项目", "东楚航空冷链物流中心",
|
"三峡智慧航空物流产业园", "汉江襄阳（小河）港多式联运物流园", "宏茂全球通运项目", "武汉天河综合客运枢纽",
|
"广水市杨家寨公铁物流园项目---广水市杨寨铁路货场（广水市华鑫冶金物流货场）", "阳新综合客运枢纽站",
|
"当阳市高铁综合客运站", "宜昌北客运站", "南漳县公铁换乘中心(郑万高铁南漳综合客运枢纽)", "京山南公铁综合客运枢纽",
|
"沿江高铁汉川北站站前广场(综合客运枢纽)", "武杭高铁黄冈西综合客运枢纽", "黄黄高铁浠水综合客运枢纽",
|
"郑万高铁巴东综合客运枢纽", "中通供应链湖北管理中心项目", "阳逻港铁水联运二期中欧班列集结中心",
|
"西马国际（黄石）智慧物流园", "十堰市城发仓储物流中心", "枝江市顾家店仓储物流中心（一期）", "三峡建材物流园",
|
"宜都市陆城综合物流运输廊道", "宜昌传化公路港二期", "武汉港航襄阳智慧物流园", "谷城县城乡智慧仓储物流产业园",
|
"九州通沃田国际供应链中心项目", "湖北恒峰国际物流园", "湖北中青供应链有限公司包装配套、仓储运输项目",
|
"大悟县电商物流产业园", "安陆市城乡冷链物流配送中心", "大别山智慧物流产业园", "武穴市供销社集采集配综合服务中心工程",
|
"罗田县综合物流园", "黑豹国际物流园（黄冈综合园区）", "麻城市农产品综合物流园", "巴东经济开发区青龙桥智慧物流产业园",
|
"鹤峰武陵山仓储物流园", "咸丰县综合物流园", "宣恩县寄递物流园", "来凤县龙凤物流园交通物流中心",
|
"仙桃国家高新区物流枢纽中心建设项目", "天门市物流中心建设项目"));
|
|
/** 经济强县项目固定名单 */
|
private static final java.util.Set<String> INVEST_COUNTY_NAMES = new LinkedHashSet<>(Arrays.asList(
|
"西马国际（黄石）智慧物流园", "黄石大冶湖高新区智慧冷链物流园", "大冶湖高新智慧物流港项目（城西北供应链服务中心）",
|
"秭归县脐橙产业综合物流中心", "宜都供销商贸物流园", "长阳县城乡冷链物流园及其配套设施建设项目",
|
"宜昌港兴山港区管公水多式联运建设项目", "枝江市顾家店仓储物流中心（一期）", "宜都市陆城综合物流运输廊道",
|
"老河口市农产品仓储物流交易中心建设项目", "谷城县城乡智慧仓储物流产业园", "沙洋多式联运现代物流园",
|
"大悟县电商物流产业园", "应城市智慧冷链仓储物流园", "孝昌广浔智慧物流电商产业园建设项目", "安陆市城乡冷链物流配送中心",
|
"松滋市星络物流园", "公安县多式联运综合物流园", "中国供销公安商贸物流园（公安县域集采集配中心）", "同洲智慧物流园",
|
"大别山智慧物流产业园", "武穴市供销社集采集配综合服务中心工程", "罗田县综合物流园", "鄂东（黄冈）数智物流综合服务中心",
|
"麻城市农产品综合物流园", "鄂湘赣商贸物流中心（一期）", "嘉鱼县智慧交通物流枢纽中心",
|
"崇阳县国家级综合物流枢纽及配套设施建设项目", "康华智慧物流园", "综合物流园（湖北交投随州智慧供应链产业园）",
|
"巴东经济开发区青龙桥智慧物流产业园", "恩施市七里坪物流园", "鹤峰武陵山仓储物流园", "咸丰县综合物流园",
|
"来凤县龙凤物流园交通物流中心", "宣恩县寄递物流园", "仙桃国家高新区物流枢纽中心建设项目",
|
"职业工装产业集聚区仓储物流项目", "中国虾谷冷链仓储物流园", "天门市物流中心建设项目", "神农架物流园"));
|
|
/** "十五五"重点物流项目固定名单 */
|
private static final java.util.Set<String> INVEST_FIVE_YEAR_NAMES = new LinkedHashSet<>(Arrays.asList(
|
"航发智慧物流园", "中通供应链湖北管理中心项目", "阳逻港铁水联运二期中欧班列集结中心", "极兔速递华中物流枢纽基地项目",
|
"中欧班列配套多式联运中转中心项目", "新开发银行贷款长江中游（武汉）智慧物流枢纽项目铁路工业站子项",
|
"黄石新港国胜智慧公路港项目", "十堰生产服务型国家物流枢纽工程项目", "十堰市城发仓储物流中心",
|
"寿康永乐智能化物流设备升级改造", "十堰汽配（广场）物流中心扩能升级项目", "十堰武当山机场航空物流园项目",
|
"丹江口市陈家港铁水公空联运基地", "长阳县城乡冷链物流园及其配套设施建设项目", "三峡建材物流园",
|
"宜都市陆城综合物流运输廊道", "三峡智慧航空物流产业园", "当阳市坝陵化工园区综合物流园", "远安县物流中心提能升级项目",
|
"宜昌市智慧物流集散中心", "宜都市枝城多式联运临港物流园", "玉湖冷链（襄阳）交易中心项目", "传化鄂西北智慧物流中心项目",
|
"武汉港航襄阳智慧物流园", "唐白河港散货转运中心项目", "汉江襄阳（小河）港多式联运物流园", "襄州区大宗商品多式联运交易中心",
|
"综合性供应链产业园区（中国有机谷供应链智慧产业园、谷城智慧冷链物流供应链产业园）", "南漳县现代智能物流园区",
|
"湖北长江现代物流产业集聚示范区·现代物流园", "弘业现代物流产业园", "宏茂全球通运项目", "沙洋多式联运现代物流园",
|
"京山市公铁物流中心", "新材料工业品智慧物流园", "长荆铁路（钟祥）公铁联运集装箱物流园", "屈家岭智慧物流园",
|
"东方冷链智慧物流配送中心", "应城市智慧冷链仓储物流园", "孝昌广浔智慧物流电商产业园建设项目",
|
"京汉粤大通道孝感国家综合货运枢纽", "公安县多式联运综合物流园", "荆州经开区智能仓储物流园项目",
|
"中国供销公安商贸物流园（公安县域集采集配中心）", "监利市港口物流园（城区园）", "鄂东（黄冈）数智物流综合服务中心",
|
"崇阳县国家级综合物流枢纽及配套设施建设项目", "咸宁高新区冻库冷链项目（一期）", "咸安区数字物流产业园及基础设施建设项目",
|
"通山县特色农产品现代物流基地建设项目", "赤壁沿江公铁联运绿色物流中心项目", "咸宁城乡智慧物流园",
|
"综合物流园（湖北交投随州智慧供应链产业园）", "随州综合仓储配送中心",
|
"广水市杨家寨公铁物流园项目---第一期广水市杨寨铁路货场（广水市华鑫冶金物流货场）",
|
"巴东经济开发区青龙桥智慧物流产业园", "鹤峰武陵山仓储物流园", "咸丰县综合物流园",
|
"仙桃国家高新区物流枢纽中心建设项目", "天门市域公共综合物流园", "潜江市农产品现代物流基础设施建设项目",
|
"潜江现代农业科技示范园冷链物流中心项目", "湖北潜电物流有限公司现代物流中心（一、二、三期）"));
|
|
/** 汇总大表市州短名 -> 规范名 */
|
private static final Map<String, String> INVEST_CITY_SHORT_TO_FULL = new LinkedHashMap<>();
|
|
static {
|
INVEST_CITY_SHORT_TO_FULL.put("武汉", "武汉市");
|
INVEST_CITY_SHORT_TO_FULL.put("黄石", "黄石市");
|
INVEST_CITY_SHORT_TO_FULL.put("十堰", "十堰市");
|
INVEST_CITY_SHORT_TO_FULL.put("宜昌", "宜昌市");
|
INVEST_CITY_SHORT_TO_FULL.put("襄阳", "襄阳市");
|
INVEST_CITY_SHORT_TO_FULL.put("鄂州", "鄂州市");
|
INVEST_CITY_SHORT_TO_FULL.put("荆门", "荆门市");
|
INVEST_CITY_SHORT_TO_FULL.put("孝感", "孝感市");
|
INVEST_CITY_SHORT_TO_FULL.put("荆州", "荆州市");
|
INVEST_CITY_SHORT_TO_FULL.put("黄冈", "黄冈市");
|
INVEST_CITY_SHORT_TO_FULL.put("咸宁", "咸宁市");
|
INVEST_CITY_SHORT_TO_FULL.put("随州", "随州市");
|
INVEST_CITY_SHORT_TO_FULL.put("恩施", "恩施州");
|
INVEST_CITY_SHORT_TO_FULL.put("仙桃", "仙桃市");
|
INVEST_CITY_SHORT_TO_FULL.put("潜江", "潜江市");
|
INVEST_CITY_SHORT_TO_FULL.put("天门", "天门市");
|
INVEST_CITY_SHORT_TO_FULL.put("林区", "神农架林区");
|
}
|
|
/**
|
* 投资月报导入（全省汇总大表）。
|
* category: 客运站场（xlsx 明细 sheet）/ 物流园区（xls 分项目投资完成情况 sheet）
|
* 幂等：同报表期旧月度数据与 INVEST 审核结果先删，项目主档按 项目名+类别 upsert。
|
*/
|
public ImportResult importInvestment(MultipartFile file, String period, String category) throws Exception {
|
boolean passenger = "客运站场".equals(category);
|
List<InvestRow> rows = new ArrayList<>();
|
List<String> cityOrder = new ArrayList<>();
|
String currentCity = null;
|
try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
|
Sheet sheet = pickInvestmentSheet(wb, passenger);
|
// 定位表头行：包含"项目名称"且其后有数据
|
int headerRow = -1;
|
for (int r = 0; r <= Math.min(sheet.getLastRowNum(), 8); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String a = getString(row, 0);
|
String b = getString(row, 1);
|
if (b != null && b.contains("项目名称") && (a == null || a.contains("序号") || a.contains("序"))) {
|
headerRow = r;
|
break;
|
}
|
}
|
if (headerRow < 0) {
|
throw new RuntimeException("所选数据类型【" + (passenger ? "投资月报（客运站场）" : "投资月报（物流园区）") + "】与文件内容不符：未找到表头行（需包含 序号/项目名称 列），请确认是否选错了数据类型");
|
}
|
for (int r = headerRow + 1; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String a = getString(row, 0);
|
String b = getString(row, 1);
|
if (a == null || a.trim().isEmpty()) {
|
if (b == null || b.trim().isEmpty()) continue;
|
}
|
String aTrim = a == null ? "" : a.trim();
|
String bTrim = b == null ? "" : b.trim();
|
// 合计/标题行
|
if (aTrim.contains("合计") || aTrim.contains("总计")) continue;
|
// 资金类别/分组标题行（一、二、三、四、…）
|
if (aTrim.matches("^[一二三四五六七八九十]+、.*")) continue;
|
// 市州小节行
|
String city = investmentCity(aTrim);
|
if (city != null) {
|
currentCity = city;
|
if (!cityOrder.contains(city)) cityOrder.add(city);
|
continue;
|
}
|
// 项目行：序号为数字且项目名非空
|
boolean seqNum = aTrim.matches("\\d+(\\.\\d+)?");
|
if (seqNum && !bTrim.isEmpty()) {
|
InvestmentProject project = new InvestmentProject();
|
project.setProjectName(bTrim);
|
project.setCategory(category);
|
project.setCity(currentCity);
|
project.setBuilderName(getString(row, 2));
|
project.setConstructNature(getString(row, 3));
|
project.setStartTime(investTime(getString(row, 4)));
|
project.setEndTime(investTime(getString(row, 5)));
|
project.setTotalInvestment(getDecimal(row, 6));
|
project.setApprovalGk(getString(row, 18));
|
project.setApprovalCs(getString(row, 19));
|
project.setSource("全省汇总大表");
|
project.setIsBillion(INVEST_BILLION_NAMES.contains(bTrim) ? 1 : 0);
|
project.setIsCounty(INVEST_COUNTY_NAMES.contains(bTrim) ? 1 : 0);
|
project.setIsFiveYear(INVEST_FIVE_YEAR_NAMES.contains(bTrim) ? 1 : 0);
|
|
InvestmentMonthly monthly = new InvestmentMonthly();
|
monthly.setReportPeriod(period);
|
monthly.setYearPlan(getDecimal(row, 8));
|
monthly.setYearCum(getDecimal(row, 9));
|
monthly.setMonthDone(getDecimal(row, 10));
|
monthly.setStartCum(getDecimal(row, 7));
|
monthly.setProgressStage(getString(row, 11));
|
monthly.setProgressDesc(getString(row, 12));
|
monthly.setBuildingArea(getDecimalOrNull(row, 15));
|
rows.add(new InvestRow(r + 1, project, monthly));
|
}
|
}
|
} catch (Exception e) {
|
log.error("Investment import parse error: {}", category, e);
|
throw e;
|
}
|
|
// 幂等清理：同报表期、同类别月度数据 + INVEST 审核结果（项目主档跨月保留，按名 upsert）
|
List<InvestmentProject> sameCat = investProjectMapper.selectList(
|
new LambdaQueryWrapper<InvestmentProject>().eq(InvestmentProject::getCategory, category));
|
java.util.Set<Long> catProjectIds = new HashSet<>();
|
for (InvestmentProject pc : sameCat) catProjectIds.add(pc.getId());
|
if (!catProjectIds.isEmpty()) {
|
investMonthlyMapper.delete(new LambdaQueryWrapper<InvestmentMonthly>()
|
.eq(InvestmentMonthly::getReportPeriod, period)
|
.in(InvestmentMonthly::getProjectId, catProjectIds));
|
}
|
List<Long> investRuleIds = new ArrayList<>();
|
for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {
|
if ("INVEST".equals(rule.getReportType())) investRuleIds.add(rule.getId());
|
}
|
if (!investRuleIds.isEmpty()) {
|
auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
|
.eq(AuditResult::getReportPeriod, period)
|
.in(AuditResult::getRuleId, investRuleIds));
|
}
|
|
int success = 0;
|
List<String> failDetails = new ArrayList<>();
|
for (InvestRow item : rows) {
|
try {
|
// 主档 upsert（按 项目名+类别）
|
InvestmentProject exist = investProjectMapper.selectOne(new LambdaQueryWrapper<InvestmentProject>()
|
.eq(InvestmentProject::getProjectName, item.project.getProjectName())
|
.eq(InvestmentProject::getCategory, category)
|
.last("LIMIT 1"));
|
if (exist == null) {
|
investProjectMapper.insert(item.project);
|
item.monthly.setProjectId(item.project.getId());
|
} else {
|
item.project.setId(exist.getId());
|
item.project.setCreatedAt(exist.getCreatedAt());
|
investProjectMapper.updateById(item.project);
|
item.monthly.setProjectId(exist.getId());
|
}
|
investMonthlyMapper.insert(item.monthly);
|
success++;
|
} catch (Exception e) {
|
Throwable cause = e;
|
while (cause.getCause() != null) cause = cause.getCause();
|
String reason = cause.getMessage();
|
if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();
|
failDetails.add("第 " + item.excelRow + " 行（" + item.project.getProjectName() + "）：" + reason);
|
log.error("Investment insert error: row {}", item.excelRow, e);
|
}
|
}
|
String errorDetail = String.join("\n", failDetails);
|
if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";
|
recordBatch(file.getOriginalFilename(), "INVEST_" + category, period, rows.size(), success, failDetails.size(), errorDetail);
|
log.info("Investment {} imported: {} rows for {}", category, success, period);
|
return new ImportResult(success, failDetails.size(), failDetails);
|
}
|
|
// ========== 投资模块 M2：市州单表批量导入（容错解析） ==========
|
|
/**
|
* 市州单表批量导入：扫描 docs/投资/输入/{类别目录}/{X月} 下全部 xls/xlsx/et（跳过 pdf），
|
* 容错解析每个文件（定位表头行 + 列名映射），upsert 项目主档 + 写入月度，
|
* 并与「十五五」模板清单规范化匹配后标记 is_five_year。
|
*/
|
public Map<String, Object> importInvestCityDir(String period, String category) throws Exception {
|
boolean passenger = "客运站场".equals(category);
|
String catDir = passenger ? "客运（客运站）" : "物流（物流园区）";
|
String monthDir = investMonthDir(period);
|
if (monthDir.isEmpty()) throw new RuntimeException("报表期格式应为 yyyy-MM，例如 2026-07");
|
File dir = resolveInvestInputDir(catDir, monthDir);
|
if (dir == null || !dir.isDirectory()) {
|
throw new RuntimeException("未找到市州单表目录：docs/投资/输入/" + catDir + "/" + monthDir);
|
}
|
Set<String> fiveYearNames = loadFiveYearProjectNames();
|
List<InvestCityRow> rows = new ArrayList<>();
|
List<Map<String, Object>> fileResults = new ArrayList<>();
|
File[] files = dir.listFiles((d, n) -> {
|
String lower = n.toLowerCase();
|
return lower.endsWith(".xls") || lower.endsWith(".xlsx") || lower.endsWith(".et");
|
});
|
if (files == null || files.length == 0) {
|
throw new RuntimeException("目录中没有可解析的报表文件（xls/xlsx/et）：" + dir.getAbsolutePath());
|
}
|
Arrays.sort(files, Comparator.comparing(File::getName));
|
for (File f : files) {
|
Map<String, Object> fr = new LinkedHashMap<>();
|
fr.put("fileName", f.getName());
|
try (java.io.InputStream is = new java.io.FileInputStream(f);
|
Workbook wb = WorkbookFactory.create(is)) {
|
parseInvestCityWorkbook(wb, f.getName(), category, period, fiveYearNames, rows, fr);
|
} catch (Exception e) {
|
fr.put("success", 0);
|
fr.put("error", e.getMessage() == null ? e.toString() : e.getMessage());
|
log.error("invest city file parse error: {}", f.getName(), e);
|
}
|
fileResults.add(fr);
|
}
|
// 幂等清理：按本次文件涉及市州清理月度数据 + INVEST 审核结果（项目主档跨月保留，按名 upsert）
|
cleanInvestMonthlyByCity(category, period, rows);
|
Map<String, Set<String>> knownCities = loadInvestKnownCities(category);
|
Map<String, String> fileFirstMismatch = new LinkedHashMap<>();
|
int success = 0;
|
List<String> failDetails = new ArrayList<>();
|
for (InvestCityRow item : rows) {
|
String mismatch = investCityMismatchReason(item, knownCities);
|
if (mismatch != null) {
|
failDetails.add(mismatch);
|
fileFirstMismatch.putIfAbsent(item.fileName, mismatch);
|
continue;
|
}
|
try {
|
saveInvestProjectRow(item.project, item.monthly);
|
success++;
|
} catch (Exception e) {
|
Throwable cause = e;
|
while (cause.getCause() != null) cause = cause.getCause();
|
String reason = cause.getMessage();
|
if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();
|
failDetails.add(item.fileName + " 第 " + item.excelRow + " 行（" + item.project.getProjectName() + "）：" + reason);
|
log.error("Invest city insert error: file {}, row {}", item.fileName, item.excelRow, e);
|
}
|
}
|
// 把首个市州错位提示挂到对应文件结果上，便于页面弹窗直接看到
|
for (Map<String, Object> fr2 : fileResults) {
|
String msg = fileFirstMismatch.get(fr2.get("fileName"));
|
if (msg != null && fr2.get("error") == null) fr2.put("error", msg);
|
}
|
String errorDetail = String.join("\n", failDetails);
|
if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";
|
recordBatch("市州单表批量导入（" + catDir + "/" + monthDir + "）", "INVEST_" + category + "_CITY",
|
period, rows.size(), success, failDetails.size(), errorDetail);
|
int fiveYearMatched = 0;
|
for (InvestCityRow item : rows) {
|
if (Integer.valueOf(1).equals(item.project.getIsFiveYear())) fiveYearMatched++;
|
}
|
Map<String, Object> result = new LinkedHashMap<>();
|
result.put("total", rows.size());
|
result.put("success", success);
|
result.put("fail", failDetails.size());
|
result.put("failDetails", failDetails);
|
result.put("files", fileResults);
|
result.put("fiveYearTemplateProjects", fiveYearNames.size());
|
result.put("fiveYearMatched", fiveYearMatched);
|
log.info("Invest city {} {} imported: {} rows (fiveYear matched {}/{})",
|
category, period, success, fiveYearMatched, fiveYearNames.size());
|
return result;
|
}
|
|
/**
|
* 多文件批量导入：前端一次选择多个 Excel 上传。
|
* 投资类型（investment/investmentLogistics）按市州单表解析（文件名/填报单位识别市州，十五五自动标记）；
|
* 其他类型逐个走现有解析逻辑（importDirFile）。
|
*/
|
public Map<String, Object> importBatchFiles(String type, String period, List<MultipartFile> files) throws Exception {
|
if (files == null || files.isEmpty()) {
|
throw new RuntimeException("未接收到任何文件");
|
}
|
if ("investment".equals(type) || "investmentLogistics".equals(type)) {
|
String category = "investment".equals(type) ? "客运站场" : "物流园区";
|
return importInvestCityFiles(period, category, files);
|
}
|
List<Map<String, Object>> fileResults = new ArrayList<>();
|
int totalSuccess = 0, totalFail = 0;
|
for (MultipartFile f : files) {
|
Map<String, Object> fr = new LinkedHashMap<>();
|
fr.put("fileName", f.getOriginalFilename());
|
try {
|
ImportResult r = importDirFile(type, f, period);
|
fr.put("success", r.getSuccess());
|
fr.put("fail", r.getFail());
|
if (r.getFail() > 0 && r.getFailDetails() != null && !r.getFailDetails().isEmpty()) {
|
fr.put("error", r.getFailDetails().get(0));
|
}
|
totalSuccess += r.getSuccess();
|
totalFail += r.getFail();
|
} catch (Exception e) {
|
fr.put("success", 0);
|
fr.put("fail", 0);
|
fr.put("error", e.getMessage() == null ? e.toString() : e.getMessage());
|
totalFail++;
|
}
|
fileResults.add(fr);
|
}
|
Map<String, Object> result = new LinkedHashMap<>();
|
result.put("type", type);
|
result.put("total", files.size());
|
result.put("success", totalSuccess);
|
result.put("fail", totalFail);
|
result.put("files", fileResults);
|
return result;
|
}
|
|
/** 投资多文件批量：逐文件按市州单表解析，统一幂等清理落库，批次汇总 */
|
public Map<String, Object> importInvestCityFiles(String period, String category, List<MultipartFile> files) throws Exception {
|
boolean passenger = "客运站场".equals(category);
|
Set<String> fiveYearNames = loadFiveYearProjectNames();
|
List<InvestCityRow> rows = new ArrayList<>();
|
List<Map<String, Object>> fileResults = new ArrayList<>();
|
for (MultipartFile f : files) {
|
Map<String, Object> fr = new LinkedHashMap<>();
|
fr.put("fileName", f.getOriginalFilename());
|
try (Workbook wb = WorkbookFactory.create(f.getInputStream())) {
|
parseInvestCityWorkbook(wb, f.getOriginalFilename(), category, period, fiveYearNames, rows, fr);
|
} catch (Exception e) {
|
fr.put("success", 0);
|
fr.put("error", e.getMessage() == null ? e.toString() : e.getMessage());
|
log.error("invest city file parse error: {}", f.getOriginalFilename(), e);
|
}
|
fileResults.add(fr);
|
}
|
String catDir = passenger ? "客运（客运站）" : "物流（物流园区）";
|
cleanInvestMonthlyByCity(category, period, rows);
|
Map<String, Set<String>> knownCities = loadInvestKnownCities(category);
|
Map<String, String> fileFirstMismatch = new LinkedHashMap<>();
|
int success = 0;
|
List<String> failDetails = new ArrayList<>();
|
for (InvestCityRow item : rows) {
|
String mismatch = investCityMismatchReason(item, knownCities);
|
if (mismatch != null) {
|
failDetails.add(mismatch);
|
fileFirstMismatch.putIfAbsent(item.fileName, mismatch);
|
continue;
|
}
|
try {
|
saveInvestProjectRow(item.project, item.monthly);
|
success++;
|
} catch (Exception e) {
|
Throwable cause = e;
|
while (cause.getCause() != null) cause = cause.getCause();
|
String reason = cause.getMessage();
|
if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();
|
failDetails.add(item.fileName + " 第 " + item.excelRow + " 行（" + item.project.getProjectName() + "）：" + reason);
|
log.error("Invest city insert error: file {}, row {}", item.fileName, item.excelRow, e);
|
}
|
}
|
// 把首个市州错位提示挂到对应文件结果上，便于页面弹窗直接看到
|
for (Map<String, Object> fr2 : fileResults) {
|
String msg = fileFirstMismatch.get(fr2.get("fileName"));
|
if (msg != null && fr2.get("error") == null) fr2.put("error", msg);
|
}
|
String errorDetail = String.join("\n", failDetails);
|
if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";
|
recordBatch("多文件批量导入（" + catDir + "/" + period + "）", "INVEST_" + category + "_CITY",
|
period, rows.size(), success, failDetails.size(), errorDetail);
|
int fiveYearMatched = 0;
|
for (InvestCityRow item : rows) {
|
if (Integer.valueOf(1).equals(item.project.getIsFiveYear())) fiveYearMatched++;
|
}
|
Map<String, Object> result = new LinkedHashMap<>();
|
result.put("total", rows.size());
|
result.put("success", success);
|
result.put("fail", failDetails.size());
|
result.put("failDetails", failDetails);
|
result.put("files", fileResults);
|
result.put("fiveYearTemplateProjects", fiveYearNames.size());
|
result.put("fiveYearMatched", fiveYearMatched);
|
log.info("Invest city multi-file {} {} imported: {} rows (fiveYear matched {}/{})",
|
category, period, success, fiveYearMatched, fiveYearNames.size());
|
return result;
|
}
|
|
/** 多 sheet 历史文件（襄阳 .et 等）：sheet 名含报表期优先，其次选数据行最多 */
|
private boolean investSheetMatchesPeriod(Sheet sh, String period) {
|
if (period == null || !period.matches("\\d{4}-\\d{2}")) return false;
|
String name = sh.getSheetName();
|
if (name == null || name.isEmpty()) return false;
|
String norm = name.replace(" ", "");
|
int month = Integer.parseInt(period.substring(5, 7));
|
String normDash = norm.replace('.', '-'); // "2026.8"/"2026.08" -> "2026-8"/"2026-08"
|
return norm.contains(month + "月") || norm.contains(String.format("%02d", month) + "月")
|
|| name.contains(period) || name.contains(period.replace("-0", "-"))
|
|| normDash.contains(period) || normDash.contains(period.replace("-0", "-"));
|
}
|
|
/**
|
* 报表期单元格命中：扫描 sheet 前 8 行，找形如 2026.08 / 2026年8月 / 2026-08-24 的报表期文字或日期单元格。
|
* 背景（2026-09-22）：潜江单表含 20 个历史 sheet（潜江（1月）…潜江（8月）…），旧逻辑只按「8月」匹配 sheet 名，
|
* 会把 2018 年的「潜江（8月）」当成 2026-08，导入成 2018 年数据；当期 sheet 名是「潜江（2022.3）」，
|
* 只能靠表内报表期（2026.08.24）识别。
|
*/
|
private boolean investSheetPeriodCellHit(Sheet sh, String period) {
|
if (sh == null || period == null || !period.matches("\\d{4}-\\d{2}")) return false;
|
String year = period.substring(0, 4);
|
int month = Integer.parseInt(period.substring(5, 7));
|
int last = Math.min(sh.getLastRowNum(), 7);
|
for (int r = 0; r <= last; r++) {
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
int lastCell = Math.max(1, row.getLastCellNum());
|
for (int c = 0; c < Math.min(lastCell, 12); c++) {
|
Cell cell = row.getCell(c);
|
if (cell == null) continue;
|
if (cell.getCellType() == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {
|
java.time.LocalDate d = cell.getLocalDateTimeCellValue().toLocalDate();
|
if (d.getYear() == Integer.parseInt(year) && d.getMonthValue() == month) return true;
|
continue;
|
}
|
String v = cellStr(row, c);
|
if (v == null) continue;
|
String t = v.trim().replace(" ", "");
|
if (t.isEmpty() || t.length() > 30) continue;
|
java.util.regex.Matcher mm = java.util.regex.Pattern
|
.compile("(\\d{4})\\s*[年.\\-/]\\s*(\\d{1,2})").matcher(t);
|
if (mm.find() && year.equals(mm.group(1)) && month == Integer.parseInt(mm.group(2))) return true;
|
}
|
}
|
return false;
|
}
|
|
/** 解析单个市州报表：选 sheet（报表期名优先 + 数据行数），按列名映射提取项目行 */
|
private void parseInvestCityWorkbook(Workbook wb, String fileName, String category, String period,
|
Set<String> fiveYearNames, List<InvestCityRow> rows, Map<String, Object> fr) {
|
Sheet best = null;
|
Map<String, Integer> bestCol = null;
|
int bestScore = -1;
|
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
|
Sheet sh = wb.getSheetAt(i);
|
Map<String, Integer> col = findInvestHeader(sh);
|
if (col == null) continue;
|
int cnt = countInvestRows(sh, col);
|
// 2026-09-22：表内报表期（单元格）命中权重最高——历史同月 sheet（如潜江（8月））不能盖过当期 sheet
|
boolean cellHit = cnt > 0 && investSheetPeriodCellHit(sh, period);
|
int score = cnt + (cnt > 0 && investSheetMatchesPeriod(sh, period) ? 1000000 : 0)
|
+ (cellHit ? 2000000 : 0);
|
if (cnt > 0 && investSheetMatchesPeriod(sh, period)) {
|
// 当期真实报表 sheet 名通常带年份（如 2026.8/2026年8月），历史sheet多为纯月份名（8月）：带年份的优先
|
String norm = sh.getSheetName() == null ? "" : sh.getSheetName().replace(" ", "");
|
if (norm.contains(period.substring(0, 4))) score += 100000;
|
}
|
if (score > bestScore) {
|
best = sh;
|
bestCol = col;
|
bestScore = score;
|
}
|
}
|
if (best == null) {
|
fr.put("success", 0);
|
fr.put("error", "未找到含「项目名称」的表头");
|
return;
|
}
|
String city = cityFromFileName(fileName);
|
if (city == null) city = cityFromSheetHeader(best);
|
fr.put("city", city == null ? "" : city);
|
String currentCity = city;
|
Map<String, Integer> col = bestCol;
|
int nameCol = col.get("name");
|
boolean hasSeq = col.containsKey("seq");
|
int seqCol = hasSeq ? col.get("seq") : 0;
|
int parsed = 0;
|
for (int r = col.get("headerRow") + 1; r <= best.getLastRowNum(); r++) {
|
Row row = best.getRow(r);
|
if (row == null) continue;
|
String name = cellStr(row, nameCol);
|
String seq = hasSeq ? cellStr(row, seqCol) : null;
|
// 市州小节行：部分汇总表把市州名写在序号列（如十堰汇总表 A 列写"武汉"），需同样识别
|
if (seq != null && !seq.trim().isEmpty()) {
|
String seqCity = investmentCity(seq.trim());
|
if (seqCity != null) {
|
currentCity = seqCity;
|
continue;
|
}
|
}
|
if (name == null || name.trim().isEmpty()) continue;
|
String nt = name.trim().replaceAll("[\r\n]+", "");
|
String ntFlat = nt.replaceAll("\\s+", "");
|
if (ntFlat.contains("合计") || ntFlat.contains("总计") || ntFlat.contains("小计")) continue;
|
if (nt.matches("^[一二三四五六七八九十]+、.*")) continue;
|
if (nt.matches("^（[一二三四五六七八九十]+）.*")) continue;
|
if (nt.startsWith("填报") || nt.startsWith("单位负责人") || nt.matches("^\\d+、.*")) continue;
|
if (hasSeq) {
|
if (seq == null || seq.trim().isEmpty()) {
|
// 市州小节行（如 "武汉" "天 门"），更新当前市州
|
String c = investmentCity(nt);
|
if (c != null) { currentCity = c; continue; }
|
// 序号为空的真实项目行（部分市州月报不填序号，如神农架物流园）也按项目处理
|
if (!isLikelyInvestProjectName(nt)) continue;
|
} else if (!seq.trim().matches("\\d+(\\.\\d+)?")) {
|
continue;
|
}
|
} else {
|
// 无序号列（随州等）：项目名非空即项目行，市州小节行用 investmentCity 识别
|
String c = investmentCity(nt);
|
if (c != null) { currentCity = c; continue; }
|
}
|
InvestmentProject project = new InvestmentProject();
|
project.setProjectName(nt);
|
project.setCategory(category);
|
project.setCity(currentCity);
|
String countyCell = cellStr(row, col.get("county"));
|
if (countyCell != null && !countyCell.trim().isEmpty()) project.setCounty(countyCell.trim());
|
project.setBuilderName(cellStr(row, col.get("builder")));
|
project.setConstructNature(cellStr(row, col.get("nature")));
|
project.setStartTime(investTime(cellStr(row, col.get("start"))));
|
project.setEndTime(investTime(cellStr(row, col.get("end"))));
|
project.setTotalInvestment(cellNum(row, col.get("total")));
|
project.setApprovalGk(cellStr(row, col.get("gk")));
|
project.setApprovalCs(cellStr(row, col.get("cs")));
|
project.setBuildingArea(cellNumOrNull(row, col.get("area")));
|
project.setSource("市州单表");
|
project.setIsBillion(INVEST_BILLION_NAMES.contains(nt) ? 1 : 0);
|
project.setIsCounty(INVEST_COUNTY_NAMES.contains(nt) ? 1 : 0);
|
project.setIsFiveYear(fiveYearNames.contains(normalizeProjectName(nt)) ? 1 : 0);
|
InvestmentMonthly monthly = new InvestmentMonthly();
|
monthly.setReportPeriod(period);
|
monthly.setStartCum(cellNum(row, col.get("startcum")));
|
monthly.setYearPlan(cellNum(row, col.get("yearplan")));
|
monthly.setYearCum(cellNum(row, col.get("yearcum")));
|
monthly.setMonthDone(cellNum(row, col.get("monthdone")));
|
monthly.setProgressStage(cellStr(row, col.get("stage")));
|
monthly.setProgressDesc(cellStr(row, col.get("desc")));
|
monthly.setBuildingArea(cellNumOrNull(row, col.get("area")));
|
rows.add(new InvestCityRow(r + 1, fileName, project, monthly));
|
parsed++;
|
}
|
fr.put("city", currentCity);
|
fr.put("success", parsed);
|
}
|
|
/** 定位含「项目名称」的表头行并按列名关键词建立列映射（支持双层表头合并） */
|
private Map<String, Integer> findInvestHeader(Sheet sh) {
|
int maxHeaderRow = Math.min(22, sh.getLastRowNum());
|
for (int r = 0; r <= maxHeaderRow; r++) {
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
boolean hasName = false;
|
int headerCells = 0;
|
for (int c = 0; c <= row.getLastCellNum(); c++) {
|
String v = cellStr(row, c);
|
if (v == null) continue;
|
String t = v.trim();
|
if (t.isEmpty() || t.length() > 20) continue; // 说明性长句不算表头字段
|
String k = investHeaderKey(t);
|
if (k == null) continue;
|
headerCells++;
|
if ("name".equals(k)) hasName = true;
|
}
|
// 真表头行至少有 2 个短表头字段；只有一格长句含「项目名称」的说明页会被排除
|
if (!hasName || headerCells < 2) continue;
|
Map<Integer, String> colTexts = new HashMap<>();
|
int scanEnd = investHeaderScanEnd(sh, r, row);
|
for (int rr = r; rr <= scanEnd; rr++) {
|
Row rr2 = sh.getRow(rr);
|
if (rr2 == null) continue;
|
for (int c = 0; c <= rr2.getLastCellNum(); c++) {
|
String v = cellStr(rr2, c);
|
if (v == null) continue;
|
colTexts.merge(c, v, (a, b) -> a + "|" + b);
|
}
|
}
|
Map<String, Integer> col = new HashMap<>();
|
for (Map.Entry<Integer, String> e : colTexts.entrySet()) {
|
String k = investHeaderKey(e.getValue());
|
if (k != null) col.putIfAbsent(k, e.getKey());
|
}
|
if (!col.containsKey("name")) continue;
|
for (int c = 0; c <= row.getLastCellNum(); c++) {
|
String v = cellStr(row, c);
|
if (v != null && v.trim().matches("序\\s*号")) { col.put("seq", c); break; }
|
}
|
// fallback：yearcum 缺失时按 startcum+2（总投资/自开始累计/本年计划/自年初累计 的标准列序）
|
if (!col.containsKey("yearcum") && col.containsKey("startcum")) {
|
int yc = col.get("startcum") + 2;
|
if (yc <= row.getLastCellNum()) col.put("yearcum", yc);
|
}
|
// monthdone 缺失时按 yearcum+1
|
if (!col.containsKey("monthdone") && col.containsKey("yearcum")) {
|
int md = col.get("yearcum") + 1;
|
if (md <= row.getLastCellNum()) col.put("monthdone", md);
|
}
|
// desc 缺失时按 stage+1 兜底：省标「项目建设阶段、进度情况」是 L/M 合并表头，
|
// 孝感/恩施/林区/随州等市州只在 L 写「建设阶段」、M 直接写进度文字而不写标题
|
if (!col.containsKey("desc") && col.containsKey("stage") && col.get("stage") >= 0) {
|
int dc = col.get("stage") + 1;
|
if (dc <= row.getLastCellNum()) col.put("desc", dc);
|
}
|
for (String k : new String[]{"builder", "nature", "start", "end", "total", "startcum",
|
"yearplan", "yearcum", "monthdone", "stage", "desc", "gk", "cs", "area", "county"}) {
|
col.putIfAbsent(k, -1);
|
}
|
col.put("headerRow", r);
|
return col;
|
}
|
return null;
|
}
|
|
/** 单个表头单元格 -> 字段键（null 表示不是表头字段）；供表头行判定与列映射共用 */
|
private String investHeaderKey(String v) {
|
if (v == null) return null;
|
// 2026-09-22：表头单元格常带换行/全角空格（如天门「自开始建\n设累计完\n成投资\n(万元)」），
|
// 统一去掉全部空白后再判定关键词；否则 H(自开始建设累计)/J(自年初累计)/K(本月完成) 会匹配失败，
|
// 列映射落到 -1 → 三个数值被写成 0（天门 2026-08 就是这样丢的 16677.7/16677.7/500）。
|
String flat = v.replaceAll("[\\s\\u3000]+", "");
|
if (flat.contains("项目名称")) return "name";
|
// 「县（市、区）」的各种写法：半/全角括号、顿号/逗号、有无括号，统一归一化后再判定
|
String flatCounty = flat.replace("（", "(").replace("）", ")").replace("、", "").replace("，", "").replace(",", "");
|
if (flatCounty.contains("县(市区)") || flatCounty.contains("县市区") || flatCounty.contains("区县")) return "county";
|
if (flat.contains("投资建设") || flat.contains("建设单位") || flat.contains("项目业主")) return "builder";
|
if (flat.contains("建设性质")) return "nature";
|
if (flat.contains("开工")) return "start";
|
if (flat.contains("竣工") || flat.contains("建成")) return "end";
|
if ((flat.contains("计划总投资") || flat.contains("总投资")) && !flat.contains("累计")) return "total";
|
if (flat.contains("自开始建设")) return "startcum";
|
if (flat.contains("本年计划")) return "yearplan";
|
if (flat.contains("自年初累计")) return "yearcum";
|
if (flat.contains("本月完成")) return "monthdone";
|
if (flat.contains("建设阶段")) return "stage";
|
if (flat.contains("形象进度") || flat.contains("建设进展")) return "desc";
|
if (flat.contains("工可") && !flat.contains("项目名称")) return "gk";
|
if (flat.contains("初设") && !flat.contains("项目名称")) return "cs";
|
if (flat.contains("建筑面积") && !flat.contains("新增")) return "area";
|
return null;
|
}
|
|
/** 表头文本扫描下界：默认表头行 +3；若「项目名称」列首个数据行更靠下，则扩展到数据行前一行（上限 +12）。
|
* 背景：咸宁/十堰等市州把「建设阶段/形象进度」标签写在表头下第 4~8 行，固定 +3 会漏列（形象进度读不到）。 */
|
private int investHeaderScanEnd(Sheet sh, int headerRow, Row headerRowObj) {
|
int hardEnd = Math.min(headerRow + 12, sh.getLastRowNum());
|
int nameCol = -1;
|
if (headerRowObj != null) {
|
for (int c = 0; c <= headerRowObj.getLastCellNum(); c++) {
|
String v = cellStr(headerRowObj, c);
|
if (v != null && v.contains("项目名称")) { nameCol = c; break; }
|
}
|
}
|
int firstData = findFirstInvestDataRow(sh, headerRow, nameCol);
|
int scanEnd = (firstData > headerRow)
|
? Math.min(hardEnd, firstData - 1)
|
: Math.min(headerRow + 3, sh.getLastRowNum());
|
if (scanEnd < headerRow) scanEnd = headerRow;
|
return scanEnd;
|
}
|
|
/** 「项目名称」列首个数据行下标（跳过表头续行与空行）；找不到返回 -1 */
|
private int findFirstInvestDataRow(Sheet sh, int headerRow, int nameCol) {
|
if (nameCol < 0) return -1;
|
for (int r = headerRow + 1; r <= sh.getLastRowNum(); r++) {
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
String name = cellStr(row, nameCol);
|
if (name == null || name.trim().isEmpty()) continue;
|
String nt = name.trim().replaceAll("[\r\n]+", "");
|
if (nt.contains("项目名称")) continue;
|
String flat = nt.replaceAll("\\s+", "");
|
if (flat.contains("合计") || flat.contains("总计") || flat.contains("小计")) continue;
|
if (nt.matches("^[一二三四五六七八九十]+、.*")) continue;
|
if (nt.matches("^（[一二三四五六七八九十]+）.*")) continue;
|
if (nt.startsWith("填报") || nt.startsWith("单位负责人") || nt.matches("^\\d+、.*")) continue;
|
if (investmentCity(nt) != null) continue;
|
return r;
|
}
|
return -1;
|
}
|
|
/** 统计 sheet 内项目数据行数（用于多 sheet 历史文件选当月 sheet） */
|
private int countInvestRows(Sheet sh, Map<String, Integer> col) {
|
int nameCol = col.get("name");
|
boolean hasSeq = col.containsKey("seq");
|
int seqCol = hasSeq ? col.get("seq") : 0;
|
int count = 0;
|
for (int r = col.get("headerRow") + 1; r <= sh.getLastRowNum(); r++) {
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
String name = cellStr(row, nameCol);
|
if (name == null || name.trim().isEmpty()) continue;
|
String nt = name.trim().replaceAll("[\r\n]+", "");
|
String ntFlat = nt.replaceAll("\\s+", "");
|
if (ntFlat.contains("合计") || ntFlat.contains("总计") || ntFlat.contains("小计")) continue;
|
if (nt.matches("^[一二三四五六七八九十]+、.*")) continue;
|
if (nt.startsWith("填报") || nt.startsWith("单位负责人") || nt.matches("^\\d+、.*")) continue;
|
if (!hasSeq) {
|
if (nt.length() > 4) count++;
|
} else {
|
String seq = cellStr(row, seqCol);
|
if (seq != null && seq.trim().matches("\\d+(\\.\\d+)?")) {
|
count++;
|
} else if ((seq == null || seq.trim().isEmpty()) && isLikelyInvestProjectName(nt)) {
|
count++;
|
}
|
}
|
}
|
return count;
|
}
|
|
/** 从文件名提取市州（如 "（孝感市道运中心）…" -> 孝感市） */
|
private String cityFromFileName(String fileName) {
|
for (Map.Entry<String, String> e : INVEST_CITY_SHORT_TO_FULL.entrySet()) {
|
if (fileName.contains(e.getKey())) return e.getValue();
|
}
|
return null;
|
}
|
|
/** 从 sheet 头部「填报单位：XX」行提取市州 */
|
private String cityFromSheetHeader(Sheet sh) {
|
for (int r = 0; r < Math.min(6, sh.getLastRowNum() + 1); r++) {
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
for (int c = 0; c <= row.getLastCellNum(); c++) {
|
String v = cellStr(row, c);
|
if (v == null || !v.contains("填报单位")) continue;
|
java.util.regex.Matcher m = java.util.regex.Pattern
|
.compile("填报单位[：:（(]?(盖章|签章)?[）)]?[：:]*([^\\s，,；;]{2,14})")
|
.matcher(v);
|
if (m.find()) {
|
String raw = m.group(2);
|
for (Map.Entry<String, String> e : INVEST_CITY_SHORT_TO_FULL.entrySet()) {
|
if (raw.contains(e.getKey())) return e.getValue();
|
}
|
String city = investmentCity(raw);
|
if (city != null) return city;
|
}
|
}
|
}
|
return null;
|
}
|
|
/** 解析「十五五」规划物流项目模板清单，返回规范化项目名集合 */
|
private Set<String> loadFiveYearProjectNames() {
|
Set<String> names = new LinkedHashSet<>();
|
File template = resolveInvestTemplate("模板_十五五规划物流项目进展情况.xls");
|
if (template == null || !template.exists()) {
|
log.warn("十五五模板不存在，is_five_year 标记跳过: {}", template);
|
return names;
|
}
|
try (java.io.InputStream is = new java.io.FileInputStream(template);
|
Workbook wb = WorkbookFactory.create(is)) {
|
Sheet sh = null;
|
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
|
if (wb.getSheetName(i).contains("分项目投资")) { sh = wb.getSheetAt(i); break; }
|
}
|
if (sh == null) sh = wb.getSheetAt(0);
|
Map<String, Integer> col = findInvestHeader(sh);
|
if (col == null) return names;
|
int nameCol = col.get("name");
|
int seqCol = col.containsKey("seq") ? col.get("seq") : 0;
|
for (int r = col.get("headerRow") + 1; r <= sh.getLastRowNum(); r++) {
|
Row row = sh.getRow(r);
|
if (row == null) continue;
|
String seq = cellStr(row, seqCol);
|
String name = cellStr(row, nameCol);
|
if (seq == null || name == null) continue;
|
if (!seq.trim().matches("\\d+")) continue;
|
names.add(normalizeProjectName(name.trim()));
|
}
|
} catch (Exception e) {
|
log.error("load five year project names error", e);
|
}
|
return names;
|
}
|
|
/** 序号为空的非市州行是否像项目行：长度>4 且非备注/说明等杂项 */
|
private boolean isLikelyInvestProjectName(String nt) {
|
if (nt == null || nt.length() <= 4) return false;
|
if (investmentCity(nt) != null) return false;
|
return !nt.startsWith("备注") && !nt.startsWith("说明") && !nt.startsWith("注：")
|
&& !nt.startsWith("填表") && !nt.startsWith("审核") && !nt.startsWith("统计")
|
&& !nt.startsWith("负责");
|
}
|
|
/** 项目名规范化：去空白/换行/括号内容/标点/「项目」后缀，用于十五五清单匹配 */
|
private String normalizeProjectName(String name) {
|
String s = name.replaceAll("\\s+", "").replace("\n", "");
|
s = s.replaceAll("[（(【\\[][^）)】\\]]*[）)】\\]]", "");
|
s = s.replaceAll("[，,。.、；;：:—\\-－_/\\\\|]", "");
|
s = s.replace("物流项目", "物流").replace("项目", "");
|
return s;
|
}
|
|
/** 报表期 "2026-07" -> "7月" */
|
private String investMonthDir(String period) {
|
if (period == null || !period.matches("\\d{4}-\\d{2}")) return "";
|
int month = Integer.parseInt(period.substring(5, 7));
|
return month + "月";
|
}
|
|
/** 投资输入目录定位：user.dir -> user.dir/.. 逐级回退（spring-boot:run 的 user.dir 是 traffic-audit-server） */
|
private File resolveInvestInputDir(String catDir, String monthDir) {
|
String rel = "docs/投资/输入/" + catDir + "/" + monthDir;
|
for (String baseDir : new String[]{System.getProperty("user.dir"), System.getProperty("user.dir") + "/.."}) {
|
File f = new File(baseDir, rel);
|
if (f.isDirectory()) return f;
|
}
|
return null;
|
}
|
|
private File resolveInvestTemplate(String fileName) {
|
String rel = "docs/投资/模板/" + fileName;
|
for (String baseDir : new String[]{System.getProperty("user.dir"), System.getProperty("user.dir") + "/.."}) {
|
File f = new File(baseDir, rel);
|
if (f.isFile()) return f;
|
}
|
return null;
|
}
|
|
/** 同类别同报表期月度数据 + INVEST 审核结果清理（幂等，全省汇总大表全量清理） */
|
private void cleanInvestMonthly(String category, String period) {
|
List<InvestmentProject> sameCat = investProjectMapper.selectList(
|
new LambdaQueryWrapper<InvestmentProject>().eq(InvestmentProject::getCategory, category));
|
java.util.Set<Long> catProjectIds = new HashSet<>();
|
for (InvestmentProject pc : sameCat) catProjectIds.add(pc.getId());
|
if (!catProjectIds.isEmpty()) {
|
investMonthlyMapper.delete(new LambdaQueryWrapper<InvestmentMonthly>()
|
.eq(InvestmentMonthly::getReportPeriod, period)
|
.in(InvestmentMonthly::getProjectId, catProjectIds));
|
}
|
deleteInvestAuditResults(period, null);
|
}
|
|
/** 按市州清理：只删除本次文件涉及市州（含未识别市州的 null 项目）的月度数据 + INVEST 审核结果 */
|
private void cleanInvestMonthlyByCity(String category, String period, List<InvestCityRow> rows) {
|
Set<String> citySet = new HashSet<>();
|
boolean hasNullCity = false;
|
for (InvestCityRow item : rows) {
|
String c = item.project.getCity();
|
if (c == null || c.trim().isEmpty()) hasNullCity = true;
|
else citySet.add(c.trim());
|
}
|
if (citySet.isEmpty() && !hasNullCity) return;
|
boolean cleanNullCity = hasNullCity;
|
LambdaQueryWrapper<InvestmentProject> pw = new LambdaQueryWrapper<InvestmentProject>()
|
.eq(InvestmentProject::getCategory, category);
|
pw.and(w -> {
|
if (!citySet.isEmpty()) w.in(InvestmentProject::getCity, citySet);
|
if (cleanNullCity) {
|
if (!citySet.isEmpty()) w.or();
|
w.isNull(InvestmentProject::getCity);
|
}
|
});
|
List<InvestmentProject> cityProjects = investProjectMapper.selectList(pw);
|
java.util.Set<Long> catProjectIds = new HashSet<>();
|
for (InvestmentProject pc : cityProjects) catProjectIds.add(pc.getId());
|
if (catProjectIds.isEmpty()) return;
|
List<InvestmentMonthly> oldMonthlies = investMonthlyMapper.selectList(
|
new LambdaQueryWrapper<InvestmentMonthly>()
|
.eq(InvestmentMonthly::getReportPeriod, period)
|
.in(InvestmentMonthly::getProjectId, catProjectIds));
|
java.util.Set<Long> oldMonthlyIds = new HashSet<>();
|
for (InvestmentMonthly m : oldMonthlies) oldMonthlyIds.add(m.getId());
|
investMonthlyMapper.delete(new LambdaQueryWrapper<InvestmentMonthly>()
|
.eq(InvestmentMonthly::getReportPeriod, period)
|
.in(InvestmentMonthly::getProjectId, catProjectIds));
|
deleteInvestAuditResults(period, oldMonthlyIds);
|
}
|
|
/** 清理 INVEST 审核结果（monthlyIds 为 null 表示该期间全量清理） */
|
private void deleteInvestAuditResults(String period, java.util.Set<Long> monthlyIds) {
|
List<Long> investRuleIds = new ArrayList<>();
|
for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {
|
if ("INVEST".equals(rule.getReportType())) investRuleIds.add(rule.getId());
|
}
|
if (investRuleIds.isEmpty()) return;
|
LambdaQueryWrapper<AuditResult> w = new LambdaQueryWrapper<AuditResult>()
|
.eq(AuditResult::getReportPeriod, period)
|
.in(AuditResult::getRuleId, investRuleIds);
|
if (monthlyIds != null) {
|
if (monthlyIds.isEmpty()) return;
|
w.in(AuditResult::getReportId, monthlyIds);
|
}
|
auditResultMapper.delete(w);
|
}
|
|
/** 项目主档 upsert（按 项目名+类别）+ 月度写入 */
|
private void saveInvestProjectRow(InvestmentProject project, InvestmentMonthly monthly) {
|
InvestmentProject exist = investProjectMapper.selectOne(new LambdaQueryWrapper<InvestmentProject>()
|
.eq(InvestmentProject::getProjectName, project.getProjectName())
|
.eq(InvestmentProject::getCategory, project.getCategory())
|
.last("LIMIT 1"));
|
if (exist == null) {
|
investProjectMapper.insert(project);
|
monthly.setProjectId(project.getId());
|
} else {
|
project.setId(exist.getId());
|
project.setCreatedAt(exist.getCreatedAt());
|
investProjectMapper.updateById(project);
|
monthly.setProjectId(exist.getId());
|
}
|
investMonthlyMapper.insert(monthly);
|
}
|
|
/** 载入某类别「规范化项目名 -> 已入库市州集合」，用于市州一致性校验（名称在多个市州重复时放弃校验） */
|
private Map<String, Set<String>> loadInvestKnownCities(String category) {
|
Map<String, Set<String>> map = new HashMap<>();
|
List<InvestmentProject> list = investProjectMapper.selectList(
|
new LambdaQueryWrapper<InvestmentProject>().eq(InvestmentProject::getCategory, category));
|
for (InvestmentProject p : list) {
|
if (p.getCity() == null || p.getCity().trim().isEmpty()) continue;
|
map.computeIfAbsent(normalizeProjectName(p.getProjectName()), k -> new HashSet<>())
|
.add(p.getCity().trim());
|
}
|
return map;
|
}
|
|
/** 市州一致性校验：库中唯一归属市州与本次解析不一致时返回错误说明，否则返回 null */
|
private String investCityMismatchReason(InvestCityRow item, Map<String, Set<String>> knownCities) {
|
String parsed = item.project.getCity();
|
if (parsed == null || parsed.trim().isEmpty()) return null;
|
Set<String> known = knownCities.get(normalizeProjectName(item.project.getProjectName()));
|
if (known == null || known.size() != 1) return null;
|
String knownCity = known.iterator().next();
|
if (knownCity.equals(parsed.trim())) return null;
|
return item.fileName + " 第 " + item.excelRow + " 行（" + item.project.getProjectName()
|
+ "）：市州一致性校验未通过——库中该项目归属「" + knownCity + "」，本次文件解析为「" + parsed.trim()
|
+ "」，疑似市州错位，已跳过导入，请核对源文件";
|
}
|
|
private String cellStr(Row row, int idx) {
|
if (idx < 0) return null;
|
return getString(row, idx);
|
}
|
|
private Double cellNum(Row row, int idx) {
|
if (idx < 0) return 0.0;
|
return getDecimal(row, idx);
|
}
|
|
private Double cellNumOrNull(Row row, int idx) {
|
if (idx < 0) return null;
|
return getDecimalOrNull(row, idx);
|
}
|
|
/** 市州单表行记录：保留文件 + Excel 行号用于失败定位 */
|
private static class InvestCityRow {
|
final int excelRow;
|
final String fileName;
|
final InvestmentProject project;
|
final InvestmentMonthly monthly;
|
|
InvestCityRow(int excelRow, String fileName, InvestmentProject project, InvestmentMonthly monthly) {
|
this.excelRow = excelRow;
|
this.fileName = fileName;
|
this.project = project;
|
this.monthly = monthly;
|
}
|
}
|
/** 投资系统导出导入（审核比对基准）。
|
* 列位**按表头关键字定位**，兼容两种模板：
|
* - 老模板《模板_查询结果(投资系统-客运+物流 2026.7).xls》：行号/单位/时期/计划总投资/自开始建设累计/本年计划投资/自年初累计/当月完成投资
|
* - 新模板《2026年X月投资系统项目明细.xls》（用户 2026-09-21 更换）：行号/投资项目/时期/项目所处阶段/计划总投资/本年计划投资/自开始建设至本年（月）底-累计完成投资/当月完成投资
|
* 新模板**没有「自年初累计」列** → year_cum 留空（审核时按「基准缺失」跳过，不再当成 0 参与比对）。 */
|
public int importInvestmentSystem(MultipartFile file, String period) throws Exception {
|
List<InvestmentSystem> list = new ArrayList<>();
|
int failRows = 0;
|
try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
|
Sheet sheet = wb.getSheetAt(0);
|
requireHeaderKeyword(sheet, "投资系统导出", "投资项目", "项目名称", "单位", "计划总投资");
|
Row header = sheet.getRow(0);
|
int cName = findColByKeywords(header, "投资项目", "项目名称", "单位");
|
int cTotal = findColByKeywords(header, "计划总投资");
|
int cYearPlan = findColByKeywords(header, "本年计划投资");
|
int cStartCum = findColByKeywords(header, "自开始建设");
|
int cYearCum = findColByKeywords(header, "自年初");
|
int cMonthDone = findColByKeywords(header, "当月完成投资");
|
if (cName < 0 || cTotal < 0 || cYearPlan < 0 || cStartCum < 0 || cMonthDone < 0) {
|
throw new RuntimeException("所选数据类型【投资系统导出】表头列不完整：需要「投资项目(或单位)、计划总投资、本年计划投资、"
|
+ "自开始建设…累计完成投资、当月完成投资」等列，实际表头为「" + headerText(header)
|
+ "」。请使用系统模板《2026年X月投资系统项目明细》或《模板_查询结果(投资系统-客运+物流)》整理后再导入");
|
}
|
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String name = getString(row, cName);
|
if (name == null || name.trim().isEmpty()) continue;
|
InvestmentSystem sys = new InvestmentSystem();
|
sys.setReportPeriod(period);
|
sys.setProjectName(name.trim());
|
sys.setTotalInvestment(getDecimal(row, cTotal));
|
sys.setStartCum(getDecimal(row, cStartCum));
|
sys.setYearPlan(getDecimal(row, cYearPlan));
|
sys.setYearCum(cYearCum < 0 ? null : getDecimal(row, cYearCum));
|
sys.setMonthDone(getDecimal(row, cMonthDone));
|
list.add(sys);
|
}
|
} catch (Exception e) {
|
log.error("Investment system parse error", e);
|
throw e;
|
}
|
investSystemMapper.delete(new LambdaQueryWrapper<InvestmentSystem>()
|
.eq(InvestmentSystem::getReportPeriod, period));
|
int success = 0;
|
for (InvestmentSystem sys : list) {
|
try {
|
investSystemMapper.insert(sys);
|
success++;
|
} catch (Exception e) {
|
failRows++;
|
log.error("Investment system insert error: {}", sys.getProjectName(), e);
|
}
|
}
|
recordBatch(file.getOriginalFilename(), "INVEST_SYSTEM", period, list.size(), success, failRows, null);
|
log.info("Investment system imported: {} rows for {}", success, period);
|
return success;
|
}
|
|
/** 城市公交月报导入（城市公共交通月度运营情况，企业级明细，取公共汽电车字段） */
|
/** 城市公交（U203）模板关键列位（0-based）：所属地区/企业代码/企业名称/运营车数/客运量/轨道客运量/轮渡客运量/平均运距/填表人/联系电话/报出日期 */
|
private static final int[] CITY_BUS_HEADER_COLS = {0, 1, 2, 5, 8, 13, 17, 19, 24, 25, 26};
|
private static final String[] CITY_BUS_HEADER_KEYWORDS =
|
{"所属地区", "企业代码", "企业名称", "公共汽电车_运营车数", "公共汽电车_客运量", "轨道交通_客运量",
|
"城市客运轮渡_客运量", "公共汽电车平均运距", "填表人", "联系电话", "报出日期"};
|
|
public ImportResult importCityBus(MultipartFile file, String period) throws Exception {
|
List<CityBusRow> rows = new ArrayList<>();
|
try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
|
Sheet sheet = wb.getSheetAt(0);
|
requireHeaderLayout(sheet, "城市公交", "模板_交企统U203表_城市公共交通月度运营情况（2026年7月）.xls",
|
CITY_BUS_HEADER_COLS, CITY_BUS_HEADER_KEYWORDS);
|
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String name = getString(row, 2);
|
if (name == null || name.trim().isEmpty()) continue;
|
CityBusMonthly item = new CityBusMonthly();
|
item.setReportPeriod(period);
|
item.setRegionCode(getString(row, 0));
|
item.setCity(RegionUtil.cityByCode(getString(row, 0)));
|
item.setEnterpriseCode(getString(row, 1));
|
item.setEnterpriseName(name.trim());
|
item.setOpVehicles(getDecimal(row, 5));
|
item.setStopTotal(getDecimal(row, 6));
|
item.setStopCity(getDecimal(row, 7));
|
item.setPassengerVolume(getDecimal(row, 8));
|
item.setPassengerCity(getDecimal(row, 9));
|
item.setTurnover(getDecimal(row, 10));
|
item.setTurnoverCity(getDecimal(row, 11));
|
item.setRailPassengerVolume(getDecimal(row, 13));
|
item.setRailTurnover(getDecimal(row, 14));
|
item.setFerryPassengerVolume(getDecimal(row, 17));
|
item.setFerryTurnover(getDecimal(row, 18));
|
item.setAvgDistance(getDecimal(row, 19));
|
item.setAvgDistanceCity(getDecimal(row, 20));
|
item.setContactPerson(getString(row, 24));
|
item.setContactPhone(getString(row, 25));
|
item.setReportDate(getDateString(row, 26));
|
item.setRateOpVehiclesMom(getDecimal(row, 29));
|
item.setRatePassengerMom(getDecimal(row, 30));
|
item.setRatePassengerCityMom(getDecimal(row, 31));
|
item.setRateChengxiangMom(getDecimal(row, 32));
|
item.setRateChengxiangTurnoverMom(getDecimal(row, 33));
|
item.setAvgDistanceMomChange(getDecimal(row, 34));
|
item.setAvgDistanceCityMomChange(getDecimal(row, 35));
|
item.setDailyPassengerPerVehicle(getDecimal(row, 36));
|
item.setRateOpVehiclesYoy(getDecimal(row, 39));
|
item.setRatePassengerYoy(getDecimal(row, 40));
|
item.setRatePassengerCityYoy(getDecimal(row, 41));
|
item.setRateChengxiangYoy(getDecimal(row, 42));
|
item.setRateChengxiangTurnoverYoy(getDecimal(row, 43));
|
item.setStopCityRatio(getDecimal(row, 44));
|
item.setPassengerCityRatio(getDecimal(row, 45));
|
item.setChengxiangRatio(getDecimal(row, 46));
|
item.setRateTurnoverYoy(getDecimal(row, 47));
|
item.setRateTurnoverMom(getDecimal(row, 48));
|
item.setPassengerChengxiang(getDecimal(row, 49));
|
item.setTurnoverChengxiang(getDecimal(row, 50));
|
item.setAvgDistanceChengxiang(getDecimal(row, 51));
|
item.setVerifyExplanation(getString(row, 65));
|
item.setReportNote(getString(row, 66));
|
item.setModifyRecord(getString(row, 67));
|
rows.add(new CityBusRow(r + 1, item));
|
}
|
} catch (Exception e) {
|
log.error("CityBus import parse error: {}", e.getMessage());
|
throw e;
|
}
|
// 幂等：同报表期先清旧数据与 CITY_BUS 审核结果
|
cityBusMapper.delete(new LambdaQueryWrapper<CityBusMonthly>()
|
.eq(CityBusMonthly::getReportPeriod, period));
|
List<Long> busRuleIds = new ArrayList<>();
|
for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {
|
if ("CITY_BUS".equals(rule.getReportType())) busRuleIds.add(rule.getId());
|
}
|
if (!busRuleIds.isEmpty()) {
|
auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
|
.eq(AuditResult::getReportPeriod, period)
|
.in(AuditResult::getRuleId, busRuleIds));
|
}
|
int success = 0;
|
List<String> failDetails = new ArrayList<>();
|
for (CityBusRow item : rows) {
|
try {
|
cityBusMapper.insert(item.entity);
|
success++;
|
} catch (Exception e) {
|
Throwable cause = e;
|
while (cause.getCause() != null) cause = cause.getCause();
|
String reason = cause.getMessage();
|
if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();
|
failDetails.add("第 " + item.excelRow + " 行（" + item.entity.getEnterpriseName() + "）：" + reason);
|
log.error("CityBus insert error: row {}", item.excelRow, e);
|
}
|
}
|
String errorDetail = String.join("\n", failDetails);
|
if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";
|
recordBatch(file.getOriginalFilename(), "CITY_BUS", period, rows.size(), success, failDetails.size(), errorDetail);
|
log.info("CityBus imported: {} rows for {}", success, period);
|
return new ImportResult(success, failDetails.size(), failDetails);
|
}
|
|
/** 城市公交行记录：保留 Excel 行号用于失败定位 */
|
private static class CityBusRow {
|
final int excelRow;
|
final CityBusMonthly entity;
|
|
CityBusRow(int excelRow, CityBusMonthly entity) {
|
this.excelRow = excelRow;
|
this.entity = entity;
|
}
|
}
|
|
/** 巡游出租月报导入（市州级，17 行；含核实说明/上报说明/修改记录） */
|
/** 巡游出租（U402）模板关键列位（0-based）：所属地区/企业代码/企业名称/载客车次总数/客运量/旅客周转量/运营车辆数/平均运距/填表人/联系电话/报出日期 */
|
private static final int[] CITY_TAXI_HEADER_COLS = {0, 1, 2, 4, 6, 8, 10, 11, 15, 16, 17};
|
private static final String[] CITY_TAXI_HEADER_KEYWORDS =
|
{"所属地区", "企业代码", "企业名称", "载客车次总数", "客运量", "旅客周转量", "运营车辆数",
|
"巡游出租汽车平均运距", "填表人", "联系电话", "报出日期"};
|
|
public ImportResult importCityTaxi(MultipartFile file, String period) throws Exception {
|
List<TaxiRow> rows = new ArrayList<>();
|
try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
|
Sheet sheet = wb.getSheetAt(0);
|
requireHeaderLayout(sheet, "巡游出租", "模板_交行统U402表-巡游出租汽车运营服务情况月报(2026).xls",
|
CITY_TAXI_HEADER_COLS, CITY_TAXI_HEADER_KEYWORDS);
|
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String name = getString(row, 2);
|
if (name == null || name.trim().isEmpty()) continue;
|
CityTaxiMonthly item = new CityTaxiMonthly();
|
item.setReportPeriod(period);
|
item.setRegionCode(getString(row, 0));
|
item.setCity(cityOf(getString(row, 0), name));
|
item.setTripTotal(getDecimal(row, 4));
|
item.setTripCity(getDecimal(row, 5));
|
item.setPassengerVolume(getDecimal(row, 6));
|
item.setPassengerCity(getDecimal(row, 7));
|
item.setTurnover(getDecimal(row, 8));
|
item.setTurnoverCity(getDecimal(row, 9));
|
item.setOpVehicles(getDecimal(row, 10));
|
item.setAvgDistance(getDecimal(row, 11));
|
item.setAvgDistanceCity(getDecimal(row, 12));
|
item.setContactPerson(getString(row, 15));
|
item.setContactPhone(getString(row, 16));
|
item.setReportDate(getDateString(row, 17));
|
item.setDailyTripsPerVehicle(getDecimal(row, 18));
|
item.setDailyPassengerPerVehicle(getDecimal(row, 19));
|
item.setPassengersPerTrip(getDecimal(row, 20));
|
item.setRateOpVehiclesMom(getDecimal(row, 21));
|
item.setRateOpVehiclesYoy(getDecimal(row, 22));
|
item.setRateTripMom(getDecimal(row, 23));
|
item.setRateTripYoy(getDecimal(row, 24));
|
item.setRatePassengerMom(getDecimal(row, 25));
|
item.setRatePassengerYoy(getDecimal(row, 26));
|
item.setAvgDistanceMomChange(getDecimal(row, 27));
|
item.setPassengersPerTripCity(getDecimal(row, 28));
|
item.setRateTripCityMom(getDecimal(row, 29));
|
item.setRatePassengerCityMom(getDecimal(row, 30));
|
item.setAvgDistanceCityMomChange(getDecimal(row, 31));
|
item.setTripCityRatio(getDecimal(row, 32));
|
item.setPassengerCityRatio(getDecimal(row, 33));
|
item.setPassengersPerTripChengxiang(getDecimal(row, 34));
|
item.setRateTripChengxiangMom(getDecimal(row, 35));
|
item.setRatePassengerChengxiangMom(getDecimal(row, 36));
|
item.setAvgDistanceChengxiang(getDecimal(row, 37));
|
item.setVerifyExplanation(getString(row, 38));
|
item.setReportNote(getString(row, 39));
|
item.setModifyRecord(getString(row, 40));
|
rows.add(new TaxiRow(r + 1, item));
|
}
|
} catch (Exception e) {
|
log.error("CityTaxi import parse error: {}", e.getMessage());
|
throw e;
|
}
|
// 幂等：同报表期先清旧数据与 CITY_TAXI 审核结果
|
cityTaxiMapper.delete(new LambdaQueryWrapper<CityTaxiMonthly>()
|
.eq(CityTaxiMonthly::getReportPeriod, period));
|
deleteAuditResults(period, "CITY_TAXI");
|
int success = 0;
|
List<String> failDetails = new ArrayList<>();
|
for (TaxiRow item : rows) {
|
try {
|
cityTaxiMapper.insert(item.entity);
|
success++;
|
} catch (Exception e) {
|
Throwable cause = e;
|
while (cause.getCause() != null) cause = cause.getCause();
|
String reason = cause.getMessage();
|
if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();
|
failDetails.add("第 " + item.excelRow + " 行（" + item.entity.getCity() + "）：" + reason);
|
log.error("CityTaxi insert error: row {}", item.excelRow, e);
|
}
|
}
|
String errorDetail = String.join("\n", failDetails);
|
if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";
|
recordBatch(file.getOriginalFilename(), "CITY_TAXI", period, rows.size(), success, failDetails.size(), errorDetail);
|
log.info("CityTaxi imported: {} rows for {}", success, period);
|
return new ImportResult(success, failDetails.size(), failDetails);
|
}
|
|
/** 出租车运政车辆信息导入（车辆级明细，按市州归属统计用） */
|
public synchronized int importCityTaxiAuth(MultipartFile file, String period) throws Exception {
|
List<CityTaxiAuth> list = new ArrayList<>();
|
try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
|
Sheet sheet = wb.getSheetAt(0);
|
requireHeaderKeyword(sheet, "出租车运政车辆", "车牌");
|
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String plate = getString(row, 0);
|
if (plate == null || plate.trim().isEmpty()) continue;
|
CityTaxiAuth item = new CityTaxiAuth();
|
item.setReportPeriod(period);
|
item.setCity(taxiCity(getString(row, 9), getString(row, 8), getString(row, 4), getString(row, 18)));
|
item.setPlateNo(plate.trim());
|
item.setVehicleType(getString(row, 1));
|
item.setBizType(getString(row, 2));
|
item.setOwnerName(getString(row, 3));
|
item.setLicenseNo(getString(row, 4));
|
item.setFuelType(getString(row, 7));
|
item.setArchiveNo(getString(row, 8));
|
item.setRoadCertNo(getString(row, 9));
|
item.setOperatorName(getString(row, 17));
|
item.setOperatorAddr(getString(row, 18));
|
item.setOpStatus(getString(row, 19));
|
item.setCertStatus(getString(row, 20));
|
list.add(item);
|
}
|
} catch (Exception e) {
|
log.error("CityTaxiAuth import parse error", e);
|
throw e;
|
}
|
// 幂等：同报表期先清旧数据（运政车辆量大，批量清理）
|
cityTaxiAuthMapper.delete(new LambdaQueryWrapper<CityTaxiAuth>()
|
.eq(CityTaxiAuth::getReportPeriod, period));
|
deleteAuditResults(period, "CITY_TAXI");
|
// 批量插入（BATCH 模式）：42636 行约 20~40 秒，逐条插入需约 6 分钟会触发前端超时
|
int success = 0;
|
int fail = 0;
|
try (SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
|
CityTaxiAuthMapper batchMapper = session.getMapper(CityTaxiAuthMapper.class);
|
for (CityTaxiAuth item : list) {
|
batchMapper.insert(item);
|
}
|
session.flushStatements();
|
session.commit();
|
success = list.size();
|
} catch (Exception e) {
|
fail = list.size();
|
log.error("CityTaxiAuth batch insert error", e);
|
}
|
recordBatch(file.getOriginalFilename(), "CITY_TAXI_AUTH", period, list.size(), success, fail, null);
|
log.info("CityTaxiAuth imported: {} rows for {}", success, period);
|
return success;
|
}
|
|
|
// ========== 网约车月度输入导入（全省 pin / 订单） ==========
|
|
/** 网约车总数月度输入：模板_网约车总数.xlsx（sheet1；A 列=月份日期；B=全省客运量 C=其中城市内 D=周转量 E=其中城市内周转量）。整表按报表期覆盖。 */
|
public synchronized int importWycTotal(MultipartFile file, String period) throws Exception {
|
Map<String, WycTotalMonthly> rows = new LinkedHashMap<>();
|
try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
|
Sheet sheet = wb.getSheetAt(0);
|
for (int r = 2; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String rp = periodOfCell(row.getCell(0));
|
if (rp == null) continue;
|
double pk = dbl(row, 1), pck = dbl(row, 2), pz = dbl(row, 3), pcz = dbl(row, 4);
|
if (pk <= 0 && pck <= 0 && pz <= 0 && pcz <= 0) continue;
|
WycTotalMonthly item = new WycTotalMonthly();
|
item.setReportPeriod(rp);
|
item.setPinK(pk > 0 ? pk : null);
|
item.setPinCK(pck > 0 ? pck : null);
|
item.setPinZ(pz > 0 ? pz : null);
|
item.setPinCZ(pcz > 0 ? pcz : null);
|
rows.put(rp, item);
|
}
|
}
|
if (rows.isEmpty()) throw new RuntimeException("未识别到有效月份行：A 列应为日期（如 2026-07），B-E 为全省客运量/城市内/周转量/城市内周转量");
|
int ok = upsertWycTotal(rows);
|
log.info("WycTotal imported: {} periods ok={}", rows.size(), ok);
|
return ok;
|
}
|
|
/** 网约车订单及全省总量：模板_网约车订单及全省总量.xlsx 首个数据 sheet（A=月份；B-E=全省 pin；F=订单合计；G-W=17 市州订单） */
|
public synchronized int importWycOrder(MultipartFile file, String period) throws Exception {
|
Map<String, WycOrderMonthly> rows = new LinkedHashMap<>();
|
try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
|
Sheet sheet = wb.getSheetAt(0);
|
int head = -1;
|
for (int r = 0; r <= Math.min(sheet.getLastRowNum(), 4); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String a = getString(row, 0);
|
if (a != null && a.contains("月份")) { head = r; break; }
|
}
|
if (head < 0) throw new RuntimeException("未找到表头：A 列需含“月份”");
|
for (int r = head + 1; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String rp = periodOfCell(row.getCell(0));
|
if (rp == null) continue;
|
double pk = dbl(row, 1), pck = dbl(row, 2), pz = dbl(row, 3), pcz = dbl(row, 4), sum = dbl(row, 5);
|
double citySum = 0;
|
for (int k = 0; k < 17; k++) citySum += dbl(row, 6 + k);
|
if (pk <= 0 && pck <= 0 && pz <= 0 && pcz <= 0 && sum <= 0 && citySum <= 0) continue;
|
WycOrderMonthly it = new WycOrderMonthly();
|
it.setReportPeriod(rp);
|
it.setPinK(pk > 0 ? pk : null);
|
it.setPinCK(pck > 0 ? pck : null);
|
it.setPinZ(pz > 0 ? pz : null);
|
it.setPinCZ(pcz > 0 ? pcz : null);
|
if (sum <= 0 && citySum > 0) sum = citySum;
|
it.setOrderSum(sum > 0 ? sum : null);
|
it.setOrderWuhan(dblN(row, 6));
|
it.setOrderHuangshi(dblN(row, 7));
|
it.setOrderShiyan(dblN(row, 8));
|
it.setOrderYichang(dblN(row, 9));
|
it.setOrderXiangyang(dblN(row, 10));
|
it.setOrderEzhou(dblN(row, 11));
|
it.setOrderJingmen(dblN(row, 12));
|
it.setOrderXiaogan(dblN(row, 13));
|
it.setOrderJingzhou(dblN(row, 14));
|
it.setOrderHuanggang(dblN(row, 15));
|
it.setOrderXianning(dblN(row, 16));
|
it.setOrderSuizhou(dblN(row, 17));
|
it.setOrderEnshi(dblN(row, 18));
|
it.setOrderXiantao(dblN(row, 19));
|
it.setOrderQianjiang(dblN(row, 20));
|
it.setOrderTianmen(dblN(row, 21));
|
it.setOrderShennong(dblN(row, 22));
|
rows.put(rp, it);
|
}
|
}
|
if (rows.isEmpty()) throw new RuntimeException("未识别到有效月份行：A 列应为 yyyy-MM，B-E 全省 pin / F 合计 / G-W 17 市州订单");
|
int ok = upsertWyc(rows);
|
log.info("WycOrder imported: {} periods ok={}", rows.size(), ok);
|
return ok;
|
}
|
|
private int upsertWyc(Map<String, WycOrderMonthly> rows) {
|
int ok = 0, fail = 0;
|
for (WycOrderMonthly item : rows.values()) {
|
try {
|
wycOrderMapper.delete(new LambdaQueryWrapper<WycOrderMonthly>().eq(WycOrderMonthly::getReportPeriod, item.getReportPeriod()));
|
wycOrderMapper.insert(item);
|
recordBatch("网约车订单及全省总量", "WYC_ORDER", item.getReportPeriod(), 1, 1, 0, null);
|
ok++;
|
} catch (Exception e) {
|
fail++;
|
log.error("WycOrder insert error period={}", item.getReportPeriod(), e);
|
}
|
}
|
return ok;
|
}
|
|
private int upsertWycTotal(Map<String, WycTotalMonthly> rows) {
|
int ok = 0, fail = 0;
|
for (WycTotalMonthly item : rows.values()) {
|
try {
|
wycTotalMapper.delete(new LambdaQueryWrapper<WycTotalMonthly>().eq(WycTotalMonthly::getReportPeriod, item.getReportPeriod()));
|
wycTotalMapper.insert(item);
|
recordBatch("网约车总数", "WYC_TOTAL", item.getReportPeriod(), 1, 1, 0, null);
|
ok++;
|
} catch (Exception e) {
|
fail++;
|
log.error("WycTotal insert error period={}", item.getReportPeriod(), e);
|
}
|
}
|
return ok;
|
}
|
|
// ========== 网约车订单运行监测分析报告（docx）导入 ==========
|
|
/** 网约车订单报告导入结果（报表期/订单合计/覆盖市州数/缺失市州） */
|
public static class WycOrderReportResult {
|
private final String period;
|
private final double orderSum;
|
private final int cityCount;
|
private final List<String> missingCities;
|
|
public WycOrderReportResult(String period, double orderSum, int cityCount, List<String> missingCities) {
|
this.period = period;
|
this.orderSum = orderSum;
|
this.cityCount = cityCount;
|
this.missingCities = missingCities;
|
}
|
|
public String getPeriod() { return period; }
|
public double getOrderSum() { return orderSum; }
|
public int getCityCount() { return cityCount; }
|
public List<String> getMissingCities() { return missingCities; }
|
}
|
|
/**
|
* 《网约车行业YYYY年M月运行监测情况分析报告》（docx）导入：
|
* 解析报告附件中「地市名称 | … | 订单数总数」表的 17 市州订单数，写入 wyc_order_monthly 的
|
* F 订单合计与 G-W 17 市州订单列；全省 pin（B-E）沿用该期已有行，若无该期行则从
|
* wyc_total_monthly 同报表期复制。报表期优先取报告标题年月，取不到时用页面所选报表期。
|
*/
|
public synchronized WycOrderReportResult importWycOrderReport(MultipartFile file, String period) throws Exception {
|
Map<String, Double> orders = new LinkedHashMap<>();
|
double[] sumHolder = new double[]{0.0};
|
String docPeriod;
|
byte[] bytes;
|
try (java.io.InputStream in = file.getInputStream()) {
|
bytes = IoUtil.readBytes(in);
|
}
|
boolean isZip = bytes.length > 3 && (bytes[0] & 0xFF) == 0x50 && (bytes[1] & 0xFF) == 0x4B;
|
boolean isWordMl = !isZip && looksLikeWordMl(bytes);
|
try {
|
if (isZip && !isWordPackage(bytes)) {
|
throw new RuntimeException("这不是 Word 文档（压缩包里没有 word/document.xml），"
|
+ "多半是 Excel 或其它格式。请上传网约车订单分析报告的 .docx（或 Word 2003 XML）");
|
}
|
if (isZip) {
|
try (XWPFDocument doc = new XWPFDocument(new java.io.ByteArrayInputStream(bytes))) {
|
docPeriod = periodOfParagraphs(paragraphsOfDocx(doc));
|
parseWycOrderReportTables(doc, orders, sumHolder);
|
}
|
} else if (isWordMl) {
|
// 网约车监管平台下发的报告常被 WPS/Word 存成「Word 2003 XML（平铺 XML）」后再改名 .docx：
|
// 这种文件不是 zip 包，XWPFDocument 打不开（报 "File is not a zip file"），需按 XML 直接解析。
|
String xml = new String(bytes, java.nio.charset.StandardCharsets.UTF_8);
|
Document dom = parseXmlSafely(xml);
|
docPeriod = periodOfParagraphs(paragraphsOfDocxXml(dom));
|
parseWycOrderReportTablesXml(dom, orders, sumHolder);
|
} else if (isOle2(bytes)) {
|
throw new RuntimeException("这是 Word 97-2003 老格式（.doc），请用 WPS/Word 打开后「另存为」docx 格式再导入");
|
} else {
|
throw new RuntimeException("无法识别的文件格式：既不是 docx（zip 包），也不是 Word 2003 XML 或 .doc");
|
}
|
} catch (RuntimeException e) {
|
throw e;
|
} catch (Exception e) {
|
throw new RuntimeException("无法读取 Word 文档，请上传 .docx 格式的《网约车行业YYYY年M月运行监测情况分析报告》；"
|
+ "若上传的其实是 Excel/PDF，请改用对应模板导入。（原始错误：" + e.getMessage() + "）");
|
}
|
if (orders.isEmpty()) {
|
throw new RuntimeException("未解析到任何市州订单数，请确认上传的是《网约车行业YYYY年M月运行监测情况分析报告》");
|
}
|
String rp = null;
|
if (docPeriod != null && docPeriod.matches("\\d{4}-\\d{2}")) rp = docPeriod;
|
else if (period != null && period.matches("\\d{4}-\\d{2}")) rp = period;
|
if (rp == null) {
|
throw new RuntimeException("无法确定报表期：报告标题未含「YYYY年M月」，且页面未选择报表期");
|
}
|
|
List<String> missing = new ArrayList<>();
|
double citySum = 0.0;
|
for (String city : RegionUtil.cityList()) {
|
Double v = orders.get(city);
|
if (v == null) missing.add(city);
|
else citySum += v;
|
}
|
double sum = sumHolder[0] > 0 ? sumHolder[0] : citySum;
|
|
WycOrderMonthly it = wycOrderMapper.selectOne(new LambdaQueryWrapper<WycOrderMonthly>()
|
.eq(WycOrderMonthly::getReportPeriod, rp).last("LIMIT 1"));
|
if (it == null) it = new WycOrderMonthly();
|
it.setReportPeriod(rp);
|
it.setOrderSum(sum > 0 ? sum : null);
|
it.setOrderWuhan(orderOf(orders, "武汉市"));
|
it.setOrderHuangshi(orderOf(orders, "黄石市"));
|
it.setOrderShiyan(orderOf(orders, "十堰市"));
|
it.setOrderYichang(orderOf(orders, "宜昌市"));
|
it.setOrderXiangyang(orderOf(orders, "襄阳市"));
|
it.setOrderEzhou(orderOf(orders, "鄂州市"));
|
it.setOrderJingmen(orderOf(orders, "荆门市"));
|
it.setOrderXiaogan(orderOf(orders, "孝感市"));
|
it.setOrderJingzhou(orderOf(orders, "荆州市"));
|
it.setOrderHuanggang(orderOf(orders, "黄冈市"));
|
it.setOrderXianning(orderOf(orders, "咸宁市"));
|
it.setOrderSuizhou(orderOf(orders, "随州市"));
|
it.setOrderEnshi(orderOf(orders, "恩施州"));
|
it.setOrderXiantao(orderOf(orders, "仙桃市"));
|
it.setOrderQianjiang(orderOf(orders, "潜江市"));
|
it.setOrderTianmen(orderOf(orders, "天门市"));
|
it.setOrderShennong(orderOf(orders, "神农架林区"));
|
|
if (it.getPinK() == null || it.getPinCK() == null || it.getPinZ() == null || it.getPinCZ() == null) {
|
WycTotalMonthly tot = wycTotalMapper.selectOne(new LambdaQueryWrapper<WycTotalMonthly>()
|
.eq(WycTotalMonthly::getReportPeriod, rp).last("LIMIT 1"));
|
if (tot != null) {
|
if (it.getPinK() == null) it.setPinK(tot.getPinK());
|
if (it.getPinCK() == null) it.setPinCK(tot.getPinCK());
|
if (it.getPinZ() == null) it.setPinZ(tot.getPinZ());
|
if (it.getPinCZ() == null) it.setPinCZ(tot.getPinCZ());
|
}
|
}
|
|
wycOrderMapper.delete(new LambdaQueryWrapper<WycOrderMonthly>().eq(WycOrderMonthly::getReportPeriod, rp));
|
wycOrderMapper.insert(it);
|
recordBatch(file.getOriginalFilename(), "WYC_ORDER_REPORT", rp, 1, 1, 0, null);
|
log.info("WycOrderReport imported: period={} sum={} cities={} missing={}", rp, sum, orders.size(), missing);
|
return new WycOrderReportResult(rp, sum, orders.size(), missing);
|
}
|
|
/** 报告标题中的报表期：网约车行业2026年7月 -> 2026-07；取不到返回 null */
|
private String periodOfParagraphs(List<String> paragraphs) {
|
java.util.regex.Pattern titlePat = java.util.regex.Pattern.compile("网约车行业\\s*(\\d{4})\\s*年\\s*(\\d{1,2})\\s*月");
|
java.util.regex.Pattern anyPat = java.util.regex.Pattern.compile("(\\d{4})\\s*年\\s*(\\d{1,2})\\s*月");
|
String fallback = null;
|
int scanned = 0;
|
for (String text : paragraphs) {
|
if (text == null) continue;
|
String flat = text.replaceAll("\\s+", "");
|
if (flat.isEmpty()) continue;
|
java.util.regex.Matcher m = titlePat.matcher(flat);
|
if (m.find()) return ymd(m.group(1), m.group(2));
|
if (fallback == null && scanned < 10) {
|
java.util.regex.Matcher m2 = anyPat.matcher(flat);
|
if (m2.find()) fallback = ymd(m2.group(1), m2.group(2));
|
}
|
scanned++;
|
}
|
return fallback;
|
}
|
|
/** docx 段落文本（保持原始顺序） */
|
private List<String> paragraphsOfDocx(XWPFDocument doc) {
|
List<String> out = new ArrayList<>();
|
for (XWPFParagraph para : doc.getParagraphs()) out.add(para.getText());
|
return out;
|
}
|
|
/** Word 2003 XML（WordML）段落文本：<w:p> 下所有 <w:t> 拼接后压掉空白 */
|
private List<String> paragraphsOfDocxXml(Document dom) {
|
List<String> out = new ArrayList<>();
|
for (Element p : elementsByLocalName(dom, "p")) out.add(textOfXmlElement(p));
|
return out;
|
}
|
|
/** 是否为 Word 2003 XML（WordML 平铺 XML）——这种文件常被改名成 .docx */
|
private boolean looksLikeWordMl(byte[] bytes) {
|
int n = Math.min(bytes.length, 4096);
|
String head = new String(bytes, 0, n, java.nio.charset.StandardCharsets.UTF_8);
|
return head.contains("wordml") && head.contains("<w:wordDocument");
|
}
|
|
/** zip 包内是否含 word/document.xml（用于区分 docx 与 xlsx/其它 OPC 包） */
|
private boolean isWordPackage(byte[] bytes) {
|
try (java.util.zip.ZipInputStream zin =
|
new java.util.zip.ZipInputStream(new java.io.ByteArrayInputStream(bytes))) {
|
java.util.zip.ZipEntry e;
|
while ((e = zin.getNextEntry()) != null) {
|
String n = e.getName();
|
if ("word/document.xml".equals(n)) return true;
|
}
|
} catch (Exception ignore) {
|
return false;
|
}
|
return false;
|
}
|
|
/** 是否为 OLE2 复合文档（Word 97-2003 .doc） */
|
private boolean isOle2(byte[] b) {
|
return b.length > 8 && (b[0] & 0xFF) == 0xD0 && (b[1] & 0xFF) == 0xCF
|
&& (b[2] & 0xFF) == 0x11 && (b[3] & 0xFF) == 0xE0;
|
}
|
|
/** 安全解析 XML：禁用外部实体/DTD（报告来自外部平台，防 XXE） */
|
private Document parseXmlSafely(String xml) throws Exception {
|
javax.xml.parsers.DocumentBuilderFactory f = javax.xml.parsers.DocumentBuilderFactory.newInstance();
|
f.setNamespaceAware(true);
|
try { f.setFeature("http://apache.org/xml/features/nonvalidating/load-external-dtd", false); } catch (Exception ignore) { }
|
try { f.setFeature("http://xml.org/sax/features/external-general-entities", false); } catch (Exception ignore) { }
|
try { f.setFeature("http://xml.org/sax/features/external-parameter-entities", false); } catch (Exception ignore) { }
|
javax.xml.parsers.DocumentBuilder b = f.newDocumentBuilder();
|
b.setEntityResolver(new org.xml.sax.EntityResolver() {
|
@Override
|
public org.xml.sax.InputSource resolveEntity(String publicId, String systemId) {
|
return new org.xml.sax.InputSource(new java.io.StringReader(""));
|
}
|
});
|
return b.parse(new org.xml.sax.InputSource(new java.io.StringReader(xml)));
|
}
|
|
/** 按 localName 取全部后代元素（命名空间无关，兼容 WordML 与 docx 的 w: 前缀） */
|
private List<Element> elementsByLocalName(Document dom, String localName) {
|
List<Element> out = new ArrayList<>();
|
NodeList list = dom.getElementsByTagNameNS("*", localName);
|
for (int i = 0; i < list.getLength(); i++) out.add((Element) list.item(i));
|
return out;
|
}
|
|
/** 只取直接子元素（避免嵌套表格/嵌套属性元素被算进来） */
|
private List<Element> childElementsByLocalName(Element parent, String localName) {
|
List<Element> out = new ArrayList<>();
|
NodeList kids = parent.getChildNodes();
|
for (int i = 0; i < kids.getLength(); i++) {
|
Node n = kids.item(i);
|
if (n.getNodeType() == Node.ELEMENT_NODE && localName.equals(n.getLocalName())) out.add((Element) n);
|
}
|
return out;
|
}
|
|
/** 元素内所有 <w:t> 文本拼接并压掉空白 */
|
private String textOfXmlElement(Element el) {
|
StringBuilder sb = new StringBuilder();
|
NodeList ts = el.getElementsByTagNameNS("*", "t");
|
for (int i = 0; i < ts.getLength(); i++) sb.append(ts.item(i).getTextContent());
|
return flat(sb.toString());
|
}
|
|
private String ymd(String y, String mo) {
|
try {
|
int month = Integer.parseInt(mo);
|
if (month < 1 || month > 12) return null;
|
return String.format("%04d-%02d", Integer.parseInt(y), month);
|
} catch (NumberFormatException e) {
|
return null;
|
}
|
}
|
|
/** 解析报告中的「地市名称 | … | 订单数总数」表，orders=市州订单，sumHolder[0]=合计行订单数 */
|
private void parseWycOrderReportTables(XWPFDocument doc, Map<String, Double> orders, double[] sumHolder) {
|
List<List<List<String>>> tables = new ArrayList<>();
|
for (XWPFTable table : doc.getTables()) {
|
List<List<String>> rows = new ArrayList<>();
|
for (XWPFTableRow row : table.getRows()) {
|
List<String> cells = new ArrayList<>();
|
for (org.apache.poi.xwpf.usermodel.XWPFTableCell c : row.getTableCells()) cells.add(flat(c.getText()));
|
rows.add(cells);
|
}
|
tables.add(rows);
|
}
|
matchWycOrderTable(tables, orders, sumHolder);
|
}
|
|
/** Word 2003 XML（WordML）分支：<w:tbl>/<w:tr>/<w:tc> 抽成文本表后走同一套匹配逻辑 */
|
private void parseWycOrderReportTablesXml(Document dom, Map<String, Double> orders, double[] sumHolder) {
|
List<List<List<String>>> tables = new ArrayList<>();
|
for (Element tbl : elementsByLocalName(dom, "tbl")) {
|
List<List<String>> rows = new ArrayList<>();
|
for (Element tr : childElementsByLocalName(tbl, "tr")) {
|
List<String> cells = new ArrayList<>();
|
for (Element tc : childElementsByLocalName(tr, "tc")) cells.add(textOfXmlElement(tc));
|
rows.add(cells);
|
}
|
tables.add(rows);
|
}
|
matchWycOrderTable(tables, orders, sumHolder);
|
}
|
|
/**
|
* 在「文本化表格」里找附件1（表头含「订单数总数」/「订单数」）并取 17 市州订单数与合计。
|
* 入参 tables = 表 -> 行 -> 单元格文本（已压空白），与来源格式无关。
|
*/
|
private void matchWycOrderTable(List<List<List<String>>> tables, Map<String, Double> orders, double[] sumHolder) {
|
for (List<List<String>> rows : tables) {
|
if (rows.size() < 3) continue;
|
int headerRow = -1;
|
int orderCol = -1;
|
for (int r = 0; r < Math.min(rows.size(), 3); r++) {
|
List<String> cells = rows.get(r);
|
for (int c = 0; c < cells.size(); c++) {
|
String norm = cells.get(c);
|
if (norm.contains("订单数总数") || "订单数".equals(norm)) {
|
headerRow = r;
|
orderCol = c;
|
break;
|
}
|
}
|
if (headerRow >= 0) break;
|
}
|
if (headerRow < 0 || orderCol < 0) continue;
|
|
Map<String, Double> found = new LinkedHashMap<>();
|
double citySum = 0.0;
|
boolean hasTotal = false;
|
for (int r = headerRow + 1; r < rows.size(); r++) {
|
List<String> cells = rows.get(r);
|
if (cells.isEmpty() || orderCol >= cells.size()) continue;
|
String name = cells.get(0);
|
if (name.isEmpty()) continue;
|
Double val = parsePlainNumber(cells.get(orderCol));
|
if (val == null) continue;
|
String city = RegionUtil.normalizeCityName(name);
|
if (city != null && RegionUtil.cityList().contains(city)) {
|
found.put(city, val);
|
citySum += val;
|
} else if (name.contains("合计") || name.contains("总计") || name.contains("全省") || name.contains("湖北")) {
|
hasTotal = true;
|
sumHolder[0] = val;
|
}
|
}
|
if (!found.isEmpty()) {
|
orders.clear();
|
orders.putAll(found);
|
if (!hasTotal) sumHolder[0] = citySum;
|
return;
|
}
|
}
|
}
|
|
private String flat(String s) {
|
return s == null ? "" : s.replaceAll("\\s+", "").trim();
|
}
|
|
/** 「11,183,267 单」这类文本取数值 */
|
private Double parsePlainNumber(String s) {
|
if (s == null) return null;
|
String t = s.replaceAll("[,\\s]", "").trim();
|
if (t.isEmpty()) return null;
|
java.util.regex.Matcher m = java.util.regex.Pattern.compile("-?\\d+(?:\\.\\d+)?").matcher(t);
|
if (!m.find()) return null;
|
try {
|
return Double.parseDouble(m.group());
|
} catch (NumberFormatException e) {
|
return null;
|
}
|
}
|
|
private Double orderOf(Map<String, Double> orders, String city) {
|
Double v = orders.get(city);
|
return (v == null || v <= 0) ? null : v;
|
}
|
private String periodOfCell(Cell c) {
|
if (c == null) return null;
|
if (c.getCellType() == CellType.NUMERIC) {
|
try {
|
double v = c.getNumericCellValue();
|
if (v >= 40000 && v <= 60000) {
|
java.util.Date d = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(v);
|
java.text.SimpleDateFormat f = new java.text.SimpleDateFormat("yyyy-MM");
|
return f.format(d);
|
}
|
} catch (Exception ignore) { }
|
}
|
String s = null;
|
try { s = c.getStringCellValue(); } catch (Exception ignore) { }
|
if (s != null) {
|
java.util.regex.Matcher m = java.util.regex.Pattern.compile("(\\d{4})[-/.年](\\d{1,2})").matcher(s.trim());
|
if (m.find()) return String.format("%04d-%02d", Integer.parseInt(m.group(1)), Integer.parseInt(m.group(2)));
|
}
|
return null;
|
}
|
|
private double dbl(Row row, int idx) {
|
Double v = getDecimal(row, idx);
|
return v == null ? 0.0 : v;
|
}
|
|
private Double dblN(Row row, int idx) {
|
Double v = getDecimal(row, idx);
|
return (v == null || v <= 0) ? null : v;
|
}
|
|
/** 运政出租车市州提取：道路运输证字号→档案号→经营权号→业户地址，取 6 位区划代码 */
|
private static final java.util.regex.Pattern TAXI_REGION_PAT =
|
java.util.regex.Pattern.compile("(4290\\d{2}|42(?:0[1-9]|1[0-3]|28)\\d{2})");
|
|
private String taxiCity(String... fields) {
|
for (String f : fields) {
|
if (f == null) continue;
|
java.util.regex.Matcher m = TAXI_REGION_PAT.matcher(f);
|
if (m.find()) {
|
String city = RegionUtil.cityByCode(m.group(1));
|
if (city != null) return city;
|
}
|
}
|
return null;
|
}
|
|
/** 市州归属：优先按区划代码识别，其次按名称规范化 */
|
private String cityOf(String regionCode, String name) {
|
String city = RegionUtil.cityByCode(regionCode);
|
if (city != null) return city;
|
return RegionUtil.normalizeCityName(name);
|
}
|
|
/** 清除某报表类型在指定报表期的审核结果（导入幂等） */
|
private void deleteAuditResults(String period, String reportType) {
|
List<Long> ruleIds = new ArrayList<>();
|
for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {
|
if (reportType.equals(rule.getReportType())) ruleIds.add(rule.getId());
|
}
|
if (!ruleIds.isEmpty()) {
|
auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
|
.eq(AuditResult::getReportPeriod, period)
|
.in(AuditResult::getRuleId, ruleIds));
|
}
|
}
|
|
/** 巡游出租行记录：保留 Excel 行号用于失败定位 */
|
private static class TaxiRow {
|
final int excelRow;
|
final CityTaxiMonthly entity;
|
|
TaxiRow(int excelRow, CityTaxiMonthly entity) {
|
this.excelRow = excelRow;
|
this.entity = entity;
|
}
|
}
|
|
private Sheet pickInvestmentSheet(Workbook wb, boolean passenger) {
|
if (passenger) {
|
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
|
String name = wb.getSheetName(i);
|
if (name != null && name.contains("明细")) return wb.getSheetAt(i);
|
}
|
return wb.getSheetAt(0);
|
}
|
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
|
String name = wb.getSheetName(i);
|
if (name != null && name.contains("分项目投资")) return wb.getSheetAt(i);
|
}
|
return wb.getSheetAt(0);
|
}
|
|
/** 市州小节行识别：短名/规范名 -> 规范名，非市州行返回 null */
|
private String investmentCity(String a) {
|
if (a == null) return null;
|
String full = INVEST_CITY_SHORT_TO_FULL.get(a);
|
if (full != null) return full;
|
String norm = RegionUtil.normalizeCityName(a);
|
if (RegionUtil.CITY_LIST.contains(norm)) return norm;
|
return null;
|
}
|
|
/** 开工/建成时间：数字（2025 或 202501）或文本统一为 yyyy 或 yyyyMM */
|
private String investTime(String v) {
|
if (v == null || v.trim().isEmpty()) return null;
|
String t = v.trim();
|
if (t.matches("\\d+\\.0")) t = t.substring(0, t.length() - 2);
|
if (t.matches("\\d{4}")) return t;
|
if (t.matches("\\d{6}")) return t;
|
if (t.matches("\\d{4}-\\d{2}-\\d{2}")) return t;
|
return t;
|
}
|
|
/** 投资行记录：保留 Excel 行号用于失败定位 */
|
private static class InvestRow {
|
final int excelRow;
|
final InvestmentProject project;
|
final InvestmentMonthly monthly;
|
|
InvestRow(int excelRow, InvestmentProject project, InvestmentMonthly monthly) {
|
this.excelRow = excelRow;
|
this.project = project;
|
this.monthly = monthly;
|
}
|
}
|
|
// ========== 通用文件夹批量导入（服务端目录浏览器） ==========
|
|
/**
|
* 目录浏览器：path 为空时返回项目 docs 数据目录下的子目录；
|
* 返回当前目录绝对路径、上级目录、子目录列表及其中直接包含的 Excel 文件名/数量。
|
*/
|
public Map<String, Object> listDirs(String path) throws Exception {
|
File cur;
|
if (path == null || path.trim().isEmpty()) {
|
File docs = resolveDocsDir();
|
if (!docs.isDirectory()) {
|
throw new RuntimeException("未找到数据目录 docs（当前运行目录：" + System.getProperty("user.dir") + "）");
|
}
|
cur = docs;
|
} else {
|
cur = new File(path);
|
if (!cur.isDirectory()) {
|
throw new RuntimeException("目录不存在或不是文件夹：" + path);
|
}
|
}
|
Map<String, Object> result = new LinkedHashMap<>();
|
result.put("current", cur.getAbsolutePath());
|
result.put("parent", cur.getParent() == null ? "" : cur.getParent());
|
List<Map<String, Object>> dirs = new ArrayList<>();
|
File[] subs = cur.listFiles(File::isDirectory);
|
int dirTotal = 0;
|
if (subs != null) {
|
Arrays.sort(subs, Comparator.comparing(File::getName));
|
for (File d : subs) {
|
if (d.isHidden()) continue;
|
dirTotal++;
|
if (dirs.size() >= 300) continue;
|
Map<String, Object> m = new LinkedHashMap<>();
|
m.put("name", d.getName());
|
m.put("path", d.getAbsolutePath());
|
dirs.add(m);
|
}
|
}
|
result.put("dirs", dirs);
|
result.put("dirsTruncated", dirTotal > dirs.size());
|
List<String> excelFiles = new ArrayList<>();
|
File[] files = cur.listFiles((d, n) -> {
|
String lower = n.toLowerCase();
|
return lower.endsWith(".xlsx") || lower.endsWith(".xls");
|
});
|
int excelTotal = files == null ? 0 : files.length;
|
if (files != null) {
|
Arrays.sort(files, Comparator.comparing(File::getName));
|
int take = Math.min(files.length, 300);
|
for (int i = 0; i < take; i++) excelFiles.add(files[i].getName());
|
}
|
result.put("excelCount", excelTotal);
|
result.put("files", excelFiles);
|
result.put("filesTruncated", excelTotal > excelFiles.size());
|
return result;
|
}
|
|
/** 向上查找项目 docs 数据目录（兼容从 IDEA 以 server 模块目录启动的情况） */
|
private File resolveDocsDir() {
|
File base = new File(System.getProperty("user.dir"));
|
File cur = base;
|
for (int i = 0; i < 4 && cur != null; i++) {
|
File candidate = new File(cur, "docs");
|
if (candidate.isDirectory()) return candidate;
|
cur = cur.getParentFile();
|
}
|
return new File(base, "docs");
|
}
|
|
/**
|
* 从服务端文件夹批量导入：只扫描 dir 直接包含的 .xlsx/.xls，
|
* 按 type 逐个调用对应导入方法（与上传导入同一套解析逻辑），返回逐文件结果。
|
*/
|
public Map<String, Object> importFromDir(String type, String period, boolean skipImported, String dir) throws Exception {
|
File folder = new File(dir);
|
if (!folder.isDirectory()) {
|
throw new RuntimeException("目录不存在或不是文件夹：" + dir);
|
}
|
File[] files = folder.listFiles((d, n) -> {
|
String lower = n.toLowerCase();
|
return lower.endsWith(".xlsx") || lower.endsWith(".xls");
|
});
|
if (files == null || files.length == 0) {
|
throw new RuntimeException("目录中没有 Excel 文件（.xlsx/.xls）：" + folder.getAbsolutePath());
|
}
|
Arrays.sort(files, Comparator.comparing(File::getName));
|
List<Map<String, Object>> fileResults = new ArrayList<>();
|
int totalSuccess = 0, totalFail = 0;
|
for (File f : files) {
|
Map<String, Object> fr = new LinkedHashMap<>();
|
fr.put("fileName", f.getName());
|
try {
|
String filePeriod = detectPeriod(f.getName(), period);
|
if (filePeriod == null || filePeriod.trim().isEmpty()) {
|
throw new RuntimeException("无法从文件名识别报表期（支持 2026年7月 / 2026.7 / 2026-07 / 7月），也未选择报表期：" + f.getName());
|
}
|
fr.put("period", filePeriod);
|
if (skipImported && alreadyImported(filePeriod, f.getName())) {
|
fr.put("skipped", true);
|
fr.put("success", 0);
|
fr.put("fail", 0);
|
totalFail++;
|
fileResults.add(fr);
|
continue;
|
}
|
byte[] bytes;
|
try (InputStream is = new FileInputStream(f)) {
|
bytes = IoUtil.readBytes(is);
|
}
|
MultipartFile mf = new ByteArrayMultipartFile(f.getName(), bytes);
|
ImportResult r = importDirFile(type, mf, filePeriod);
|
fr.put("success", r.getSuccess());
|
fr.put("fail", r.getFail());
|
if (r.getFail() > 0 && r.getFailDetails() != null && !r.getFailDetails().isEmpty()) {
|
String d = r.getFailDetails().get(0);
|
if (d.length() > 300) d = d.substring(0, 300);
|
fr.put("error", d);
|
}
|
totalSuccess += r.getSuccess();
|
totalFail += r.getFail();
|
} catch (Exception e) {
|
String msg = e.getMessage() == null ? e.toString() : e.getMessage();
|
if (msg.length() > 300) msg = msg.substring(0, 300);
|
fr.put("success", 0);
|
fr.put("fail", 0);
|
fr.put("error", msg);
|
totalFail++;
|
}
|
fileResults.add(fr);
|
}
|
Map<String, Object> result = new LinkedHashMap<>();
|
result.put("type", type);
|
result.put("total", fileResults.size());
|
result.put("success", totalSuccess);
|
result.put("fail", totalFail);
|
result.put("files", fileResults);
|
return result;
|
}
|
|
/**
|
* 按文件路径导入单个 Excel（供前端逐文件导入显示进度）。
|
* 报表期优先从文件名识别（如 2026年7月 / 2026.7 / 2026-07 / 7月），识别不到才用 period。
|
*/
|
public Map<String, Object> importDirFileByPath(String type, String period, boolean skipImported, String filePath) throws Exception {
|
File f = new File(filePath);
|
if (!f.isFile()) {
|
throw new RuntimeException("文件不存在：" + filePath);
|
}
|
String filePeriod = detectPeriod(f.getName(), period);
|
if (filePeriod == null || filePeriod.trim().isEmpty()) {
|
throw new RuntimeException("无法从文件名识别报表期（支持 2026年7月 / 2026.7 / 2026-07 / 7月），也未选择报表期：" + f.getName());
|
}
|
Map<String, Object> fr = new LinkedHashMap<>();
|
fr.put("fileName", f.getName());
|
fr.put("period", filePeriod);
|
if (skipImported && alreadyImported(filePeriod, f.getName())) {
|
fr.put("skipped", true);
|
fr.put("success", 0);
|
fr.put("fail", 0);
|
fr.put("error", "该报表期已导入过此文件，已跳过（如需覆盖请取消勾选“跳过已导入文件”）");
|
return fr;
|
}
|
byte[] bytes;
|
try (InputStream is = new FileInputStream(f)) {
|
bytes = IoUtil.readBytes(is);
|
}
|
ImportResult r = importDirFile(type, new ByteArrayMultipartFile(f.getName(), bytes), filePeriod);
|
fr.put("success", r.getSuccess());
|
fr.put("fail", r.getFail());
|
if (r.getFail() > 0 && r.getFailDetails() != null && !r.getFailDetails().isEmpty()) {
|
String d = r.getFailDetails().get(0);
|
if (d.length() > 300) d = d.substring(0, 300);
|
fr.put("error", d);
|
}
|
return fr;
|
}
|
|
/** 该报表期是否已成功导入过同名文件（用于文件夹重复导入时跳过） */
|
private boolean alreadyImported(String period, String fileName) {
|
if (period == null || fileName == null) return false;
|
Long cnt = importBatchMapper.selectCount(new LambdaQueryWrapper<ImportBatch>()
|
.eq(ImportBatch::getReportPeriod, period)
|
.eq(ImportBatch::getFileName, fileName)
|
.eq(ImportBatch::getFailRows, 0));
|
return cnt != null && cnt > 0;
|
}
|
|
/** 从文件名识别报表期：2026年7月 / 2026年07月 / 2026.7 / 2026-07 / 202607 / 7月（无年份用 period 年份或当前年份） */
|
private String detectPeriod(String fileName, String defaultPeriod) {
|
if (fileName == null) return defaultPeriod;
|
String name = fileName.replace("（", "(").replace("）", ")").replace(" ", "");
|
java.util.regex.Matcher m;
|
m = java.util.regex.Pattern.compile("(\\d{4})\\s*年\\s*(\\d{1,2})\\s*月").matcher(name);
|
if (m.find()) return String.format("%04d-%02d", Integer.parseInt(m.group(1)), Integer.parseInt(m.group(2)));
|
m = java.util.regex.Pattern.compile("(\\d{4})[.\\-/\\s](\\d{1,2})").matcher(name);
|
if (m.find()) return String.format("%04d-%02d", Integer.parseInt(m.group(1)), Integer.parseInt(m.group(2)));
|
m = java.util.regex.Pattern.compile("(?:^|[^\\d])(\\d{4})(\\d{2})(?=[^\\d]|$)").matcher(name);
|
if (m.find()) {
|
int y = Integer.parseInt(m.group(1));
|
int mo = Integer.parseInt(m.group(2));
|
if (y >= 2000 && y <= 2100 && mo >= 1 && mo <= 12) {
|
return String.format("%04d-%02d", y, mo);
|
}
|
}
|
m = java.util.regex.Pattern.compile("(\\d{1,2})\\s*月").matcher(name);
|
if (m.find()) {
|
int mo = Integer.parseInt(m.group(1));
|
if (mo >= 1 && mo <= 12) {
|
return String.format("%04d-%02d", defaultYear(defaultPeriod), mo);
|
}
|
}
|
return defaultPeriod;
|
}
|
|
private int defaultYear(String defaultPeriod) {
|
if (defaultPeriod != null && defaultPeriod.matches("\\d{4}-\\d{2}")) {
|
return Integer.parseInt(defaultPeriod.substring(0, 4));
|
}
|
return java.time.Year.now().getValue();
|
}
|
|
/** 类型分发：与上传导入同一套解析方法 */
|
private ImportResult importDirFile(String type, MultipartFile file, String period) throws Exception {
|
switch (type) {
|
case "h2032": return importH2032(file, period);
|
case "passengerMonthly": return importPassengerMonthly(file, period);
|
case "passengerIndividual": return importPassengerIndividual(file, period);
|
case "passengerAuth": return importPassengerAuth(file, period);
|
case "energyMonthly": return importEnergyMonthly(file, period);
|
case "energyAuth": return importEnergyAuth(file, period);
|
case "cityBus": return importCityBus(file, period);
|
case "cityTaxi": return importCityTaxi(file, period);
|
case "cityTaxiAuth": return new ImportResult(importCityTaxiAuth(file, period), 0, new ArrayList<>());
|
case "investment": return importInvestment(file, period, "客运站场");
|
case "investmentLogistics": return importInvestment(file, period, "物流园区");
|
case "investmentSystem": return new ImportResult(importInvestmentSystem(file, period), 0, new ArrayList<>());
|
case "transportAuth": return new ImportResult(importTransportAuth(file, period), 0, new ArrayList<>());
|
case "trackMileage": return new ImportResult(importTrackMileage(file, period), 0, new ArrayList<>());
|
case "freightTurnover": return new ImportResult(importFreightTurnover(file, period), 0, new ArrayList<>());
|
case "scaleSplit": return new ImportResult(importScaleSplit(file, period), 0, new ArrayList<>());
|
case "wycTotal": return new ImportResult(importWycTotal(file, period), 0, new ArrayList<>());
|
case "wycOrder": return new ImportResult(importWycOrder(file, period), 0, new ArrayList<>());
|
case "wycOrderReport": return new ImportResult(importWycOrderReport(file, period).getCityCount(), 0, new ArrayList<>());
|
default: throw new RuntimeException("不支持的导入类型：" + type);
|
}
|
}
|
|
/** 把服务端文件包装为 MultipartFile，复用各导入方法 */
|
public static class ByteArrayMultipartFile implements MultipartFile {
|
private final String name;
|
private final byte[] content;
|
|
public ByteArrayMultipartFile(String name, byte[] content) {
|
this.name = name;
|
this.content = content;
|
}
|
|
@Override
|
public String getName() { return name; }
|
|
@Override
|
public String getOriginalFilename() { return name; }
|
|
@Override
|
public String getContentType() {
|
String lower = name.toLowerCase();
|
return lower.endsWith(".xls")
|
? "application/vnd.ms-excel"
|
: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
|
}
|
|
@Override
|
public boolean isEmpty() { return content == null || content.length == 0; }
|
|
@Override
|
public long getSize() { return content == null ? 0 : content.length; }
|
|
@Override
|
public byte[] getBytes() { return content; }
|
|
@Override
|
public InputStream getInputStream() { return new ByteArrayInputStream(content); }
|
|
@Override
|
public void transferTo(File dest) throws IOException {
|
try (FileOutputStream out = new FileOutputStream(dest)) {
|
out.write(content);
|
}
|
}
|
}
|
}
|
