package com.trafficaudit.dataimport.service;
|
|
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
|
import com.trafficaudit.auditengine.entity.AuditResult;
|
import com.trafficaudit.auditengine.mapper.AuditResultMapper;
|
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.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.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 cn.hutool.core.io.IoUtil;
|
import org.springframework.stereotype.Service;
|
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.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 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 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());
|
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);
|
}
|
|
|
|
/** 公路旅客个体客运量/周转量（企业线下汇报数据，按所属地区导入，供分市州表个体行填充） */
|
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));
|
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);
|
}
|
} 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);
|
}
|
|
/** 公路旅客运政车辆数（独立模板：企业代码/企业名称/车辆数/载客位数） */
|
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 = buildHeaderMap(sheet.getRow(0));
|
requireHeaderCols(sheet, "旅客运政车辆数", "企业名称", "车辆数", "载客位数");
|
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;
|
PassengerAuthVehicle auth = new PassengerAuthVehicle();
|
auth.setReportPeriod(period);
|
auth.setEnterpriseCode(gs(row, colMap, "企业代码"));
|
auth.setEnterpriseName(name.trim());
|
auth.setVehicleCount(gi(row, colMap, "车辆数"));
|
auth.setSeatCount(gi(row, colMap, "载客位数"));
|
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};
|
|
/** 能耗导入按报表期互斥锁（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, "道路货运车辆能源消耗情况", "企业名称", "车牌号");
|
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, "能耗车辆运政信息", "车牌号");
|
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();
|
}
|
|
// ========== 运政车辆 ==========
|
|
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);
|
requireHeaderKeyword(sheet, "运政车辆", "企业名称");
|
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;
|
TransportAuthVehicle entity = new TransportAuthVehicle();
|
entity.setReportPeriod(period);
|
entity.setEnterpriseName(name);
|
entity.setTractorCount(getInt(row, 1));
|
entity.setTrailerCount(getInt(row, 2));
|
entity.setOtherCount(getInt(row, 3));
|
entity.setTrailerTons(getDecimal(row, 4));
|
entity.setOtherTons(getDecimal(row, 5));
|
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;
|
}
|
|
// ========== 轨迹里程 ==========
|
|
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);
|
requireHeaderKeyword(sheet, "轨迹里程", "企业名称");
|
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String name = getString(row, 1);
|
if (name == null || name.trim().isEmpty()) continue;
|
VehicleTrackMileage entity = new VehicleTrackMileage();
|
entity.setEnterpriseName(name);
|
entity.setMonthlyMileage(getDecimal(row, 2));
|
entity.setTrackedVehicles(getInt(row, 3));
|
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())) {
|
boolean hasPeriodSheet = false;
|
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
|
String sn = wb.getSheetAt(i).getSheetName();
|
if (sn != null && (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;
|
if (sheetName.contains("当月")) periodType = "MONTH";
|
else if (sheetName.contains("累计")) periodType = "CUMULATIVE";
|
if (periodType == null) continue;
|
|
// 检测子表头：是否包含货运量列（扩展布局）
|
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 {
|
List<FreightTurnoverImport> list = new ArrayList<>();
|
int failRows = 0;
|
// 报表期 N 月：左半为今年 1-N 月（列数不固定），右半固定为去年 1-12 月（算同比用）
|
int monthCount = Integer.parseInt(period.split("-")[1]);
|
try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {
|
Sheet sheet = wb.getSheetAt(0);
|
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);
|
}
|
} catch (Exception e) {
|
throw e;
|
}
|
|
// 幂等：同报表期先删除
|
freightTurnoverMapper.delete(new LambdaQueryWrapper<FreightTurnoverImport>()
|
.eq(FreightTurnoverImport::getReportPeriod, period));
|
|
int success = 0;
|
for (FreightTurnoverImport entity : list) {
|
try {
|
freightTurnoverMapper.insert(entity);
|
success++;
|
} catch (Exception e) {
|
log.error("freight turnover insert error: {}", entity.getRegionName(), e);
|
failRows++;
|
}
|
}
|
recordBatch(file.getOriginalFilename(), "FREIGHT_TURNOVER", period, list.size(), success, failRows, null);
|
log.info("Freight turnover imported: {} rows for {}", success, period);
|
return success;
|
}
|
|
/** 解析货运量区块行：左半 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 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) + "】等列，请确认是否选错了数据类型");
|
}
|
|
/** 按表头名称建立列号映射，兼容新旧模板列位差异 */
|
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;
|
}
|
|
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));
|
return norm.contains(month + "月") || norm.contains(String.format("%02d", month) + "月")
|
|| name.contains(period) || name.contains(period.replace("-0", "-"));
|
}
|
|
/** 解析单个市州报表：选 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);
|
int score = cnt + (cnt > 0 && investSheetMatchesPeriod(sh, period) ? 1000000 : 0);
|
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]+", "");
|
if (nt.contains("合计") || nt.contains("总计")) continue;
|
if (nt.matches("^[一二三四五六七八九十]+、.*")) continue;
|
if (nt.matches("^（[一二三四五六七八九十]+）.*")) continue;
|
if (nt.replace(" ", "").matches(".*小\\s*计.*")) 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 (!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);
|
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;
|
for (int c = 0; c <= row.getLastCellNum(); c++) {
|
String v = cellStr(row, c);
|
if (v != null && v.contains("项目名称")) { hasName = true; break; }
|
}
|
if (!hasName) continue;
|
Map<Integer, String> colTexts = new HashMap<>();
|
for (int rr = r; rr <= Math.min(r + 3, sh.getLastRowNum()); 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()) {
|
int c = e.getKey();
|
String v = e.getValue();
|
if (v.contains("项目名称")) col.putIfAbsent("name", c);
|
else if (v.contains("投资建设") || v.contains("建设单位") || v.contains("项目业主")) col.putIfAbsent("builder", c);
|
else if (v.contains("建设性质")) col.putIfAbsent("nature", c);
|
else if (v.contains("开工")) col.putIfAbsent("start", c);
|
else if (v.contains("竣工") || v.contains("建成")) col.putIfAbsent("end", c);
|
else if ((v.contains("计划总投资") || v.contains("总投资")) && !v.contains("累计")) col.putIfAbsent("total", c);
|
else if (v.contains("自开始建设")) col.putIfAbsent("startcum", c);
|
else if (v.contains("本年计划")) col.putIfAbsent("yearplan", c);
|
else if (v.contains("自年初累计")) col.putIfAbsent("yearcum", c);
|
else if (v.contains("本月完成")) col.putIfAbsent("monthdone", c);
|
else if (v.contains("建设阶段")) col.putIfAbsent("stage", c);
|
else if (v.contains("形象进度")) col.putIfAbsent("desc", c);
|
else if (v.contains("工可") && !v.contains("项目名称")) col.putIfAbsent("gk", c);
|
else if (v.contains("初设") && !v.contains("项目名称")) col.putIfAbsent("cs", c);
|
else if (v.contains("建筑面积") && !v.contains("新增")) col.putIfAbsent("area", c);
|
}
|
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);
|
}
|
for (String k : new String[]{"builder", "nature", "start", "end", "total", "startcum",
|
"yearplan", "yearcum", "monthdone", "stage", "desc", "gk", "cs", "area"}) {
|
col.putIfAbsent(k, -1);
|
}
|
col.put("headerRow", r);
|
return col;
|
}
|
return null;
|
}
|
|
/** 统计 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]+", "");
|
if (nt.contains("合计") || nt.contains("总计")) continue;
|
if (nt.matches("^[一二三四五六七八九十]+、.*")) continue;
|
if (nt.replace(" ", "").matches(".*小\\s*计.*")) 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++;
|
}
|
}
|
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;
|
}
|
|
/** 项目名规范化：去空白/换行/括号内容/标点/「项目」后缀，用于十五五清单匹配 */
|
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;
|
}
|
}
|
/** 投资系统导出导入（审核比对基准），固定8列：行号/单位/时期/计划总投资/自开始累计/本年计划/自年初累计/当月完成 */
|
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, "投资系统导出", "项目名称", "计划总投资", "单位");
|
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
|
Row row = sheet.getRow(r);
|
if (row == null) continue;
|
String name = getString(row, 1);
|
if (name == null || name.trim().isEmpty()) continue;
|
InvestmentSystem sys = new InvestmentSystem();
|
sys.setReportPeriod(period);
|
sys.setProjectName(name.trim());
|
sys.setTotalInvestment(getDecimal(row, 3));
|
sys.setStartCum(getDecimal(row, 4));
|
sys.setYearPlan(getDecimal(row, 5));
|
sys.setYearCum(getDecimal(row, 6));
|
sys.setMonthDone(getDecimal(row, 7));
|
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;
|
}
|
|
/** 城市公交月报导入（城市公共交通月度运营情况，企业级明细，取公共汽电车字段） */
|
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);
|
Row header = sheet.getRow(0);
|
if (header == null || getString(header, 2) == null
|
|| !getString(header, 2).contains("企业名称")) {
|
throw new RuntimeException("未找到表头行（需包含 企业名称 列）");
|
}
|
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 行；含核实说明/上报说明/修改记录） */
|
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);
|
Row header = sheet.getRow(0);
|
if (header == null || getString(header, 2) == null
|
|| !getString(header, 2).contains("企业名称")) {
|
throw new RuntimeException("未找到表头行（需包含 企业名称 列）");
|
}
|
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;
|
}
|
|
/** 运政出租车市州提取：道路运输证字号→档案号→经营权号→业户地址，取 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<>());
|
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);
|
}
|
}
|
}
|
}
|
