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.FreightTurnoverImport;
|
import com.trafficaudit.dataimport.entity.H2032EnterpriseMonthly;
|
import com.trafficaudit.dataimport.entity.ImportBatch;
|
import com.trafficaudit.dataimport.entity.ScaleSplitTransport;
|
import com.trafficaudit.dataimport.entity.TransportAuthVehicle;
|
import com.trafficaudit.dataimport.entity.VehicleTrackMileage;
|
import com.trafficaudit.dataimport.mapper.FreightTurnoverImportMapper;
|
import com.trafficaudit.dataimport.mapper.H2032EnterpriseMonthlyMapper;
|
import com.trafficaudit.dataimport.mapper.ImportBatchMapper;
|
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.poi.ss.usermodel.Cell;
|
import org.apache.poi.ss.usermodel.CellType;
|
import org.apache.poi.ss.usermodel.DataFormatter;
|
import org.apache.poi.ss.usermodel.Row;
|
import org.apache.poi.ss.usermodel.Sheet;
|
import org.apache.poi.ss.usermodel.Workbook;
|
import org.apache.poi.ss.usermodel.WorkbookFactory;
|
import org.springframework.stereotype.Service;
|
import org.springframework.web.multipart.MultipartFile;
|
|
import javax.annotation.Resource;
|
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 com.trafficaudit.dataimport.dto.ImportResult;
|
import org.apache.poi.ss.usermodel.DateUtil;
|
|
import java.text.SimpleDateFormat;
|
|
/**
|
* 数据导入服务：支持4类模板导入
|
*/
|
@Slf4j
|
@Service
|
public class DataImportService {
|
|
private static final DataFormatter FORMATTER = new DataFormatter();
|
|
@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;
|
|
// ========== 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);
|
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;
|
}
|
|
// 幂等：同报表期重新导入，先删除旧数据
|
h2032Mapper.delete(new LambdaQueryWrapper<H2032EnterpriseMonthly>()
|
.eq(H2032EnterpriseMonthly::getReportPeriod, period));
|
auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()
|
.eq(AuditResult::getReportPeriod, period));
|
|
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);
|
}
|
|
|
// ========== 运政车辆 ==========
|
|
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);
|
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);
|
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())) {
|
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);
|
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 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 String getString(Row row, int idx) {
|
Cell cell = row.getCell(idx);
|
if (cell == null) return null;
|
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();
|
}
|
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();
|
}
|
String val = FORMATTER.formatCellValue(cell).trim();
|
if (val.isEmpty()) return null;
|
try {
|
return Double.parseDouble(val.replace(",", ""));
|
} catch (NumberFormatException e) {
|
return null;
|
}
|
}
|
}
|
