交通统计审核
blame | 历史 | 补丁 | 提交 | 提交对比 | ignore whitespace
zhizhijie
2026-09-21 2e6830bcf59c01f29f040652b3e5209a7f21238e 
 traffic-audit-server/src/main/java/com/trafficaudit/dataimport/service/DataImportService.java


@@ -2,21 +2,53 @@




import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;


import com.trafficaudit.auditengine.entity.AuditResult;


import com.trafficaudit.auditengine.entity.AuditRun;


import com.trafficaudit.auditengine.mapper.AuditResultMapper;


import com.trafficaudit.auditengine.mapper.AuditRunMapper;


import com.trafficaudit.common.util.RegionUtil;


import com.trafficaudit.dataimport.entity.EnergyAuthVehicle;


import com.trafficaudit.dataimport.entity.EnergyVehicleQuarterly;


import com.trafficaudit.dataimport.entity.FreightTurnoverImport;


import com.trafficaudit.dataimport.entity.H2032EnterpriseMonthly;


import com.trafficaudit.dataimport.entity.PassengerAuthVehicle;


import com.trafficaudit.dataimport.entity.PassengerEnterpriseMonthly;


import com.trafficaudit.dataimport.entity.PassengerIndividualMonthly;


import com.trafficaudit.dataimport.entity.ImportBatch;


import com.trafficaudit.dataimport.entity.InvestmentMonthly;


import com.trafficaudit.dataimport.entity.InvestmentProject;


import com.trafficaudit.dataimport.entity.InvestmentSystem;


import com.trafficaudit.dataimport.entity.CityBusMonthly;


import com.trafficaudit.dataimport.entity.CityTaxiMonthly;


import com.trafficaudit.dataimport.entity.CityTaxiAuth;


import com.trafficaudit.dataimport.entity.WycOrderMonthly;


import com.trafficaudit.dataimport.entity.WycTotalMonthly;


import com.trafficaudit.dataimport.entity.ScaleSplitTransport;


import com.trafficaudit.dataimport.entity.TransportAuthVehicle;


import com.trafficaudit.dataimport.entity.VehicleTrackMileage;


import com.trafficaudit.dataimport.mapper.EnergyAuthVehicleMapper;


import com.trafficaudit.dataimport.mapper.EnergyVehicleQuarterlyMapper;


import com.trafficaudit.dataimport.mapper.FreightTurnoverImportMapper;


import com.trafficaudit.dataimport.mapper.H2032EnterpriseMonthlyMapper;


import com.trafficaudit.dataimport.mapper.PassengerAuthVehicleMapper;


import com.trafficaudit.dataimport.mapper.PassengerEnterpriseMonthlyMapper;


import com.trafficaudit.dataimport.mapper.PassengerIndividualMonthlyMapper;


import com.trafficaudit.rulemanage.mapper.AuditRuleMapper;


import com.trafficaudit.dataimport.mapper.ImportBatchMapper;


import com.trafficaudit.dataimport.mapper.InvestmentMonthlyMapper;


import com.trafficaudit.dataimport.mapper.InvestmentProjectMapper;


import com.trafficaudit.dataimport.mapper.InvestmentSystemMapper;


import com.trafficaudit.dataimport.mapper.CityBusMonthlyMapper;


import com.trafficaudit.dataimport.mapper.CityTaxiMonthlyMapper;


import com.trafficaudit.dataimport.mapper.CityTaxiAuthMapper;


import com.trafficaudit.dataimport.mapper.WycOrderMonthlyMapper;


import com.trafficaudit.dataimport.mapper.WycTotalMonthlyMapper;


import com.trafficaudit.dataimport.mapper.ScaleSplitTransportMapper;


import com.trafficaudit.dataimport.mapper.TransportAuthVehicleMapper;


import com.trafficaudit.dataimport.mapper.VehicleTrackMileageMapper;


import lombok.extern.slf4j.Slf4j;


import org.apache.ibatis.session.ExecutorType;


import org.apache.ibatis.session.SqlSession;


import org.apache.ibatis.session.SqlSessionFactory;


import org.apache.poi.ss.usermodel.Cell;


import org.apache.poi.ss.usermodel.CellType;


import org.apache.poi.ss.usermodel.DataFormatter;


@@ -24,18 +56,41 @@


import org.apache.poi.ss.usermodel.Sheet;


import org.apache.poi.ss.usermodel.Workbook;


import org.apache.poi.ss.usermodel.WorkbookFactory;


import org.apache.poi.xwpf.usermodel.XWPFDocument;


import org.apache.poi.xwpf.usermodel.XWPFParagraph;


import org.apache.poi.xwpf.usermodel.XWPFTable;


import org.apache.poi.xwpf.usermodel.XWPFTableRow;


import org.w3c.dom.Document;


import org.w3c.dom.Element;


import org.w3c.dom.Node;


import org.w3c.dom.NodeList;


import cn.hutool.core.io.IoUtil;


import org.springframework.stereotype.Service;


import org.springframework.transaction.annotation.Transactional;


import org.springframework.web.multipart.MultipartFile;




import javax.annotation.Resource;


import java.io.ByteArrayInputStream;


import java.io.File;


import java.io.FileInputStream;


import java.io.FileOutputStream;


import java.io.IOException;


import java.io.InputStream;


import java.util.ArrayList;


import java.util.HashMap;


import java.util.HashSet;


import java.util.List;


import java.util.Map;


import java.util.Set;


import java.util.concurrent.ConcurrentHashMap;


import com.trafficaudit.dataimport.dto.ImportResult;


import org.apache.poi.ss.usermodel.DateUtil;


import java.util.Comparator;


import java.util.Arrays;


import java.util.LinkedHashMap;


import java.util.LinkedHashSet;


import java.util.TreeMap;


import java.util.stream.Collectors;




import java.text.SimpleDateFormat;




@@ -47,6 +102,43 @@


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;


@@ -62,6 +154,38 @@


    private FreightTurnoverImportMapper freightTurnoverMapper;


    @Resource


    private AuditResultMapper auditResultMapper;


    @Resource


    private AuditRunMapper auditRunMapper;


    @Resource


    private PassengerEnterpriseMonthlyMapper passengerMapper;


    @Resource


    private PassengerIndividualMonthlyMapper passengerIndividualMapper;


    @Resource


    private PassengerAuthVehicleMapper passengerAuthMapper;


    @Resource


    private AuditRuleMapper ruleMapper;


    @Resource


    private EnergyVehicleQuarterlyMapper energyMapper;


    @Resource


    private EnergyAuthVehicleMapper energyAuthMapper;


    @Resource


    private InvestmentProjectMapper investProjectMapper;


    @Resource


    private InvestmentMonthlyMapper investMonthlyMapper;


    @Resource


    private InvestmentSystemMapper investSystemMapper;


    @Resource


    private CityBusMonthlyMapper cityBusMapper;


    @Resource


    private CityTaxiMonthlyMapper cityTaxiMapper;


    @Resource


    private CityTaxiAuthMapper cityTaxiAuthMapper;


    @Resource


    private WycTotalMonthlyMapper wycTotalMapper;


    @Resource


    private WycOrderMonthlyMapper wycOrderMapper;


    @Resource


    private SqlSessionFactory sqlSessionFactory;




    // ========== H2032 月报 ==========




@@ -70,6 +194,7 @@


        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);


@@ -136,11 +261,18 @@


            throw e;


        }




        // 幂等：同报表期重新导入，先删除旧数据


        // 幂等：同报表期重新导入，先删除旧数据（只清 H2032 规则的审核结果）


        h2032Mapper.delete(new LambdaQueryWrapper<H2032EnterpriseMonthly>()


            .eq(H2032EnterpriseMonthly::getReportPeriod, period));


        auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()


            .eq(AuditResult::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<>();


@@ -171,26 +303,570 @@


    }






    // ========== H203-1 公路旅客月报（含运政车辆数sheet） ==========




    public ImportResult importPassengerMonthly(MultipartFile file, String period) throws Exception {


        List<PassengerRow> rows = new ArrayList<>();


        List<PassengerAuthVehicle> authList = new ArrayList<>();


        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {


            // sheet0: 公路旅客6月报


            Sheet sheet = wb.getSheetAt(0);


            requireHeaderCols(sheet, "公路旅客运输月度生产情况", "企业名称", "客运量_总计");


            Map<String, Integer> colMap = buildHeaderMap(sheet.getRow(0));


            for (int r = 1; r <= sheet.getLastRowNum(); r++) {


                Row row = sheet.getRow(r);


                if (row == null) continue;


                String name = gs(row, colMap, "企业名称");


                if (name == null || name.trim().isEmpty()) continue;


                PassengerEnterpriseMonthly entity = new PassengerEnterpriseMonthly();


                entity.setReportPeriod(period);


                entity.setRegionCode(gs(row, colMap, "所属地区"));


                entity.setEnterpriseCode(gs(row, colMap, "企业代码"));


                entity.setEnterpriseName(name.trim());


                // 新版模板在「企业名称」后新增了两列（统一社会信用代码/填报单位），按表头取值，老模板无该列时为 null


                entity.setUnifiedCreditCode(gs(row, colMap, "统一社会信用代码"));


                entity.setReportUnit(gs(row, colMap, "填报单位"));


                entity.setVehicleTotal(gi(row, colMap, "客运车辆_总计"));


                entity.setSeatTotal(gi(row, colMap, "核定载客位_总计"));


                entity.setVehicleSchedule(gi(row, colMap, "车辆数_客运班车（含定线旅游客运客车）"));


                entity.setSeatSchedule(gi(row, colMap, "核定载客位_客运班车（含定线旅游客运客车）"));


                entity.setVehicleCharter(gi(row, colMap, "车辆数_客运包车（含非定线旅游客运客车）"));


                entity.setSeatCharter(gi(row, colMap, "核定载客位_客运包车（含非定线旅游客运客车）"));


                entity.setPassengerTotal(gd(row, colMap, "客运量_总计"));


                entity.setTurnoverTotal(gd(row, colMap, "旅客周转量_总计"));


                entity.setPassengerClass1(gd(row, colMap, "客运量_一类客运班线（含定线旅游）"));


                entity.setTurnoverClass1(gd(row, colMap, "旅客周转量_一类客运班线（含定线旅游）"));


                entity.setPassengerClass2(gd(row, colMap, "客运量_二类客运班线（含定线旅游）"));


                entity.setTurnoverClass2(gd(row, colMap, "旅客周转量_二类客运班线（含定线旅游）"));


                entity.setPassengerClass3(gd(row, colMap, "客运量_三类客运班线（含定线旅游）"));


                entity.setTurnoverClass3(gd(row, colMap, "旅客周转量_三类客运班线（含定线旅游）"));


                entity.setPassengerClass4(gd(row, colMap, "客运量_四类客运班线（含定线旅游）"));


                entity.setTurnoverClass4(gd(row, colMap, "旅客周转量_四类客运班线（含定线旅游）"));


                entity.setPassengerCharter(gd(row, colMap, "客运量_客运包车（含非定线旅游）"));


                entity.setTurnoverCharter(gd(row, colMap, "旅客周转量_客运包车（含非定线旅游）"));


                entity.setAvgDistanceTotal(gd(row, colMap, "客运平均运距"));


                entity.setAvgDistanceClass1(gd(row, colMap, "一类客运班线平均运距"));


                entity.setAvgDistanceClass2(gd(row, colMap, "二类客运班线平均运距"));


                entity.setAvgDistanceClass3(gd(row, colMap, "三类客运班线平均运距"));


                entity.setAvgDistanceClass4(gd(row, colMap, "四类客运班线平均运距"));


                entity.setAvgDistanceCharter(gd(row, colMap, "旅游、包车平均运距"));


                entity.setVerifyExplanation(gs(row, colMap, "核实性原因解释"));


                rows.add(new PassengerRow(r + 1, entity));


            }


            // sheet1: 运政车辆数（固定4列：企业代码/企业名称/车辆数/载客位数）


            if (wb.getNumberOfSheets() > 1) {


                Sheet authSheet = wb.getSheetAt(1);


                for (int r = 1; r <= authSheet.getLastRowNum(); r++) {


                    Row row = authSheet.getRow(r);


                    if (row == null) continue;


                    String name = getString(row, 1);


                    if (name == null || name.trim().isEmpty()) continue;


                    PassengerAuthVehicle auth = new PassengerAuthVehicle();


                    auth.setReportPeriod(period);


                    auth.setEnterpriseCode(getString(row, 0));


                    auth.setEnterpriseName(name.trim());


                    auth.setVehicleCount(getInt(row, 2));


                    auth.setSeatCount(getInt(row, 3));


                    authList.add(auth);


                }


            }


        } catch (Exception e) {


            log.error("Passenger monthly parse error", e);


            throw e;


        }




        // 幂等：同报表期重新导入，先删除旧数据


        passengerMapper.delete(new LambdaQueryWrapper<PassengerEnterpriseMonthly>()


            .eq(PassengerEnterpriseMonthly::getReportPeriod, period));


        passengerAuthMapper.delete(new LambdaQueryWrapper<PassengerAuthVehicle>()


            .eq(PassengerAuthVehicle::getReportPeriod, period));


        // 只清 H2031 规则产生的审核结果，避免误删同期的货运审核结果


        List<Long> h2031RuleIds = new ArrayList<>();


        for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {


            if ("H2031".equals(rule.getReportType())) h2031RuleIds.add(rule.getId());


        }


        if (!h2031RuleIds.isEmpty()) {


            auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()


                .eq(AuditResult::getReportPeriod, period)


                .in(AuditResult::getRuleId, h2031RuleIds));


        }




        int success = 0;


        List<String> failDetails = new ArrayList<>();


        for (PassengerRow item : rows) {


            try {


                passengerMapper.insert(item.entity);


                success++;


            } catch (Exception e) {


                Throwable cause = e;


                while (cause.getCause() != null) {


                    cause = cause.getCause();


                }


                String reason = cause.getMessage();


                if (reason == null || reason.trim().isEmpty()) {


                    reason = e.getMessage();


                }


                failDetails.add("第 " + item.excelRow + " 行（" + item.entity.getEnterpriseName() + "）：" + reason);


                log.error("Passenger insert error: row {}, {}", item.excelRow, item.entity.getEnterpriseName(), e);


            }


        }


        String errorDetail = String.join("\n", failDetails);


        if (errorDetail.length() > 4000) {


            errorDetail = errorDetail.substring(0, 4000) + "\n……";


        }


        recordBatch(file.getOriginalFilename(), "H2031", period, rows.size(), success, failDetails.size(), errorDetail);




        int authSuccess = 0;


        for (PassengerAuthVehicle auth : authList) {


            try {


                passengerAuthMapper.insert(auth);


                authSuccess++;


            } catch (Exception e) {


                log.error("passenger auth insert error: {}", auth.getEnterpriseName(), e);


            }


        }


        recordBatch(file.getOriginalFilename(), "PASSENGER_AUTH", period, authList.size(), authSuccess, 0, null);


        log.info("Passenger monthly imported: {} rows, auth {} rows for {}", success, authSuccess, period);


        return new ImportResult(success, failDetails.size(), failDetails);


    }








    /** 公路旅客个体客运量/周转量（导入后按市州汇总填充分市州表个体行），支持两种模板：


     *  ① 企业线下汇报明细（表头含「企业名称」）：逐企业入库；


     *  ② 省厅市州汇总表《湖北省公路旅客运输个体户月度生产情况（YYYY.M）》：


     *     sheet 内为「全省N月个体客运量汇总表」标题块 + 「市州/个体车辆数/…/个体客运量/个体旅客周转量」表头，


     *     按市州逐行入库（同一市州多行如 县内班线+跨县班线 均入库，报表侧按市州自动汇总）。 */


    public ImportResult importPassengerIndividual(MultipartFile file, String period) throws Exception {


        List<PassengerIndividualMonthly> rows = new ArrayList<>();


        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {


            Sheet sheet = wb.getSheetAt(0);


            Map<String, Integer> colMap = buildHeaderMap(sheet.getRow(0));


            if (colMap.containsKey("企业名称")) {


                parsePassengerIndividualEnterprise(sheet, colMap, period, rows);


            } else {


                parsePassengerIndividualCitySheet(sheet, period, rows);


            }


        } catch (Exception e) {


            log.error("Passenger individual parse error", e);


            throw e;


        }


        passengerIndividualMapper.delete(new LambdaQueryWrapper<PassengerIndividualMonthly>()


            .eq(PassengerIndividualMonthly::getReportPeriod, period));


        int success = 0;


        List<String> failDetails = new ArrayList<>();


        for (PassengerIndividualMonthly item : rows) {


            try {


                passengerIndividualMapper.insert(item);


                success++;


            } catch (Exception e) {


                Throwable cause = e;


                while (cause.getCause() != null) {


                    cause = cause.getCause();


                }


                String reason = cause.getMessage();


                if (reason == null || reason.trim().isEmpty()) {


                    reason = e.getMessage();


                }


                failDetails.add("第 " + (rows.indexOf(item) + 2) + " 行（" + item.getEnterpriseName() + "）：" + reason);


            }


        }


        String errorDetail = String.join("\n", failDetails);


        if (errorDetail.length() > 4000) {


            errorDetail = errorDetail.substring(0, 4000) + "\n……";


        }


        recordBatch(file.getOriginalFilename(), "PASSENGER_INDIVIDUAL", period, rows.size(), success, failDetails.size(), errorDetail);


        log.info("Passenger individual imported: {} rows for {}", success, period);


        return new ImportResult(success, failDetails.size(), failDetails);


    }




    /** 旧版个体客运量模板（企业线下汇报明细：所属地区/企业代码/企业名称/…个体客运量/…个体旅客周转量） */


    private void parsePassengerIndividualEnterprise(Sheet sheet, Map<String, Integer> colMap, String period,


                                                    List<PassengerIndividualMonthly> rows) {


        Integer codeCol = colMap.get("所属地区");


        Integer entCol = colMap.get("企业代码");


        Integer nameCol = colMap.get("企业名称");


        Integer passCol = null;


        Integer turnCol = null;


        for (Map.Entry<String, Integer> e : colMap.entrySet()) {


            String k = e.getKey();


            if (k != null && k.contains("个体") && k.contains("客运量")) passCol = e.getValue();


            if (k != null && k.contains("个体") && (k.contains("周转量") || k.contains("周转"))) turnCol = e.getValue();


        }


        if (passCol == null && turnCol == null) {


            throw new RuntimeException("未找到「个体客运量/个体旅客周转量」列，请检查模板（列名需包含“个体客运量”“个体周转量”）");


        }


        for (int r = 1; r <= sheet.getLastRowNum(); r++) {


            Row row = sheet.getRow(r);


            if (row == null) continue;


            String name = nameCol != null ? getString(row, nameCol) : null;


            if (name == null || name.trim().isEmpty()) continue;


            Double pass = passCol != null ? getDecimalOrNull(row, passCol) : null;


            Double turn = turnCol != null ? getDecimalOrNull(row, turnCol) : null;


            if ((pass == null || pass == 0.0) && (turn == null || turn == 0.0)) continue;


            PassengerIndividualMonthly entity = new PassengerIndividualMonthly();


            entity.setReportPeriod(period);


            entity.setRegionCode(codeCol != null ? getString(row, codeCol) : null);


            entity.setEnterpriseCode(entCol != null ? getString(row, entCol) : null);


            entity.setEnterpriseName(name.trim());


            entity.setPassengerCount(pass);


            entity.setTurnover(turn);


            rows.add(entity);


        }


    }




    /** 新版市州汇总模板：《全省N月个体客运量汇总表》标题行 → 表头行（市州/…/个体客运量/个体旅客周转量）→ 市州数据行 */


    private void parsePassengerIndividualCitySheet(Sheet sheet, String period, List<PassengerIndividualMonthly> rows) {


        int targetMonth;


        try {


            targetMonth = Integer.parseInt(period.split("-")[1]);


        } catch (Exception e) {


            throw new RuntimeException("报表期格式应为 yyyy-MM：" + period);


        }


        int titleRow = -1;


        for (int r = 0; r <= sheet.getLastRowNum(); r++) {


            Row row = sheet.getRow(r);


            if (row == null) continue;


            String t = getString(row, 0);


            if (t == null || !t.contains("全省") || !t.contains("月")) continue;


            java.util.regex.Matcher m = java.util.regex.Pattern.compile("全省(\\d+)月").matcher(t);


            if (!m.find()) continue;


            if (Integer.parseInt(m.group(1)) == targetMonth) { titleRow = r; break; }


            if (titleRow < 0) titleRow = r; // 标题不含目标月份时暂记第一个标题块兜底


        }


        if (titleRow < 0) {


            throw new RuntimeException("未找到「全省N月个体客运量汇总表」标题块，请使用《模板_湖北省公路旅客运输个体户月度生产情况（YYYY.M）》整理后导入");


        }


        Row header = sheet.getRow(titleRow + 1);


        if (header == null) {


            throw new RuntimeException("「全省" + targetMonth + "月…汇总表」标题后缺少表头行（市州/个体客运量/个体旅客周转量）");


        }


        int cityCol = -1, passCol = -1, turnCol = -1;


        for (Cell c : header) {


            String h = FORMATTER.formatCellValue(c).trim();


            if (h.isEmpty()) continue;


            if (h.contains("市州")) cityCol = c.getColumnIndex();


            else if (h.contains("个体") && h.contains("客运量")) passCol = c.getColumnIndex();


            else if (h.contains("个体") && (h.contains("周转量") || h.contains("周转"))) turnCol = c.getColumnIndex();


        }


        if (cityCol < 0 || (passCol < 0 && turnCol < 0)) {


            throw new RuntimeException("未找到「市州 / 个体客运量 / 个体旅客周转量」列，请使用系统模板整理后导入");


        }


        int lastRow = Math.min(sheet.getLastRowNum(), titleRow + 40);


        for (int r = titleRow + 2; r <= lastRow; r++) {


            Row row = sheet.getRow(r);


            if (row == null) continue;


            String first = getString(row, cityCol);


            if (first == null) break;


            String t = first.trim();


            if (t.isEmpty() || t.contains("合计") || t.contains("环比") || t.contains("全省")) break;


            String city = RegionUtil.normalizeCityName(t);


            if (!RegionUtil.cityList().contains(city)) continue;


            Double pass = passCol >= 0 ? getDecimalOrNull(row, passCol) : null;


            Double turn = turnCol >= 0 ? getDecimalOrNull(row, turnCol) : null;


            if ((pass == null || pass == 0.0) && (turn == null || turn == 0.0)) continue;


            PassengerIndividualMonthly entity = new PassengerIndividualMonthly();


            entity.setReportPeriod(period);


            entity.setRegionCode(RegionUtil.codeOfCity(city));


            entity.setEnterpriseName(city);


            entity.setPassengerCount(pass);


            entity.setTurnover(turn);


            rows.add(entity);


        }


    }




    /** 公路旅客运政车辆数（兼容两种表头：① 企业代码/企业名称/车辆数/载客位数；② 运政库导出 OWNERNAME/总车辆数/客位数，无企业代码列） */


    public ImportResult importPassengerAuth(MultipartFile file, String period) throws Exception {


        List<PassengerAuthVehicle> authList = new ArrayList<>();


        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {


            Sheet sheet = wb.getSheetAt(0);


            Map<String, Integer> colMap = buildHeaderMapCompat(sheet.getRow(0));


            Integer nameCol = firstColIdx(colMap, "企业名称", "OWNERNAME");


            Integer vehicleCol = firstColIdx(colMap, "车辆数", "总车辆数");


            Integer seatCol = firstColIdx(colMap, "载客位数", "客位数");


            Integer codeCol = firstColIdx(colMap, "企业代码");


            if (nameCol == null || vehicleCol == null || seatCol == null) {


                throw new RuntimeException("所选数据类型【旅客运政车辆数】与文件内容不符：表头需含【企业名称/车辆数/载客位数】或【OWNERNAME/总车辆数/客位数】，请确认是否选错了数据类型或使用了旧模板");


            }


            for (int r = 1; r <= sheet.getLastRowNum(); r++) {


                Row row = sheet.getRow(r);


                if (row == null) continue;


                String name = getString(row, nameCol);


                if (name == null || name.trim().isEmpty()) continue;


                PassengerAuthVehicle auth = new PassengerAuthVehicle();


                auth.setReportPeriod(period);


                auth.setEnterpriseCode(codeCol == null ? null : getString(row, codeCol));


                auth.setEnterpriseName(name.trim());


                auth.setVehicleCount(getInt(row, vehicleCol));


                auth.setSeatCount(getInt(row, seatCol));


                authList.add(auth);


            }


        } catch (Exception e) {


            log.error("Passenger auth parse error", e);


            throw e;


        }


        // 幂等：同报表期重新导入，先删除旧数据


        passengerAuthMapper.delete(new LambdaQueryWrapper<PassengerAuthVehicle>()


            .eq(PassengerAuthVehicle::getReportPeriod, period));


        deleteRuleResults("H2031", period);


        int success = 0;


        List<String> failDetails = new ArrayList<>();


        for (PassengerAuthVehicle auth : authList) {


            try {


                passengerAuthMapper.insert(auth);


                success++;


            } catch (Exception e) {


                Throwable cause = e;


                while (cause.getCause() != null) cause = cause.getCause();


                String reason = cause.getMessage();


                if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();


                failDetails.add("第 " + (authList.indexOf(auth) + 2) + " 行（" + auth.getEnterpriseName() + "）：" + reason);


                log.error("Passenger auth insert error: {}", auth.getEnterpriseName(), e);


            }


        }


        String errorDetail = String.join("\n", failDetails);


        if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";


        recordBatch(file.getOriginalFilename(), "PASSENGER_AUTH", period, authList.size(), success, failDetails.size(), errorDetail);


        log.info("Passenger auth imported: {} rows for {}", success, period);


        return new ImportResult(success, failDetails.size(), failDetails);


    }




    // ========== H204 道路货运车辆能源消耗情况（季报，含运政车辆信息sheet） ==========




    /** 能耗明细每组固定18列，车牌号起始列（0-based）：3,21,39,57,75 */


    private static final int[] ENERGY_GROUP_COLS = {3, 21, 39, 57, 75};




    /** 能耗明细模板表头关键列（0-based）：程序按固定列位解析，解析前校验表头，错位直接拒绝 */


    private static final int[] ENERGY_HEADER_COLS = {3, 4, 5, 21, 39, 57, 75};


    private static final String[] ENERGY_HEADER_KEYWORDS = {"车牌号", "车辆类型", "燃料类型", "车牌号", "车牌号", "车牌号", "车牌号"};




    /** 能耗导入按报表期互斥锁（H204 明细与运政信息共用，避免同报表期并发重导互相覆盖） */


    private Object energyLock(String period) {


        return energyImportLocks.computeIfAbsent(period == null ? "" : period, k -> new Object());


    }




    public ImportResult importEnergyMonthly(MultipartFile file, String period) throws Exception {


        List<EnergyRow> rows = new ArrayList<>();


        List<EnergyAuthVehicle> authList = new ArrayList<>();


        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {


            // sheet0: 第二季度货车能耗明细（每行左右各5组车辆，每组固定18列）


            Sheet sheet = wb.getSheetAt(0);


            requireHeaderKeyword(sheet, "道路货运车辆能源消耗情况", "企业名称", "车牌号");


            requireEnergyHeaderLayout(sheet);


            for (int r = 1; r <= sheet.getLastRowNum(); r++) {


                Row row = sheet.getRow(r);


                if (row == null) continue;


                String enterpriseName = getString(row, 2);


                if (enterpriseName == null || enterpriseName.trim().isEmpty()) continue;


                for (int group : ENERGY_GROUP_COLS) {


                    String plate = getString(row, group);


                    if (plate == null || plate.trim().isEmpty() || "0".equals(plate.trim())) continue;


                    EnergyVehicleQuarterly e = new EnergyVehicleQuarterly();


                    e.setReportPeriod(period);


                    e.setRegionCode(getString(row, 0));


                    e.setEnterpriseCode(getString(row, 1));


                    e.setEnterpriseName(enterpriseName.trim());


                    e.setPlateNo(plate.trim());


                    String vtc = getString(row, group + 1);


                    e.setVehicleTypeCode(vtc);


                    e.setVehicleType(vtc == null ? null : ENERGY_VEHICLE_TYPE_NAMES.getOrDefault(vtc, vtc));


                    String ftc = getString(row, group + 2);


                    e.setFuelTypeCode(ftc);


                    e.setFuelType(ftc == null ? null : ENERGY_FUEL_TYPE_NAMES.getOrDefault(ftc, ftc));


                    e.setManufactureYear(getIntOrNull(row, group + 3));


                    e.setMarkedTonnage(getDecimalOrNull(row, group + 4));


                    e.setTotalMileage(getDecimalOrNull(row, group + 5));


                    e.setLoadedMileage(getDecimalOrNull(row, group + 6));


                    e.setEmptyMileage(getDecimalOrNull(row, group + 7));


                    e.setFreight(getDecimalOrNull(row, group + 8));


                    e.setTurnover(getDecimalOrNull(row, group + 9));


                    e.setTripCount(getIntOrNull(row, group + 10));


                    String unit = getString(row, group + 11);


                    e.setFuelUnit(unit == null ? null : ENERGY_FUEL_UNIT_NAMES.getOrDefault(unit, unit));


                    e.setFuelConsumption(getDecimalOrNull(row, group + 12));


                    e.setAvgDistance(getDecimalOrNull(row, group + 13));


                    e.setAvgLoadedTonnage(getDecimalOrNull(row, group + 14));


                    e.setAvgTripMileage(getDecimalOrNull(row, group + 15));


                    e.setFuelPer100km(getDecimalOrNull(row, group + 16));


                    e.setTurnoverFuel(getDecimalOrNull(row, group + 17));


                    rows.add(new EnergyRow(r + 1, e));


                }


            }


            // sheet1: 运政车辆信息（固定5列：车牌号/车辆类型/燃料类型/标记吨位/准牵引质量）


            if (wb.getNumberOfSheets() > 1) {


                Sheet authSheet = wb.getSheetAt(1);


                for (int r = 1; r <= authSheet.getLastRowNum(); r++) {


                    Row row = authSheet.getRow(r);


                    if (row == null) continue;


                    String plate = getString(row, 0);


                    if (plate == null || plate.trim().isEmpty() || "0".equals(plate.trim())) continue;


                    EnergyAuthVehicle auth = new EnergyAuthVehicle();


                    auth.setReportPeriod(period);


                    auth.setPlateNo(plate.trim());


                    auth.setVehicleType(getString(row, 1));


                    auth.setFuelType(getString(row, 2));


                    auth.setMarkedTonnage(getDecimal(row, 3));


                    auth.setTractionQuality(getDecimal(row, 4));


                    authList.add(auth);


                }


            }


        } catch (Exception e) {


            log.error("Energy monthly parse error", e);


            throw e;


        }




        // 幂等：同报表期重新导入，先删除旧数据（两表 + H204 规则审核结果）


        // 加锁：同报表期并发重导互斥，防止 delete/insert 交错导致数据残留或翻倍


        synchronized (energyLock(period)) {


            energyMapper.delete(new LambdaQueryWrapper<EnergyVehicleQuarterly>()


                .eq(EnergyVehicleQuarterly::getReportPeriod, period));


            energyAuthMapper.delete(new LambdaQueryWrapper<EnergyAuthVehicle>()


                .eq(EnergyAuthVehicle::getReportPeriod, period));


            List<Long> h204RuleIds = new ArrayList<>();


            for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {


                if ("H204".equals(rule.getReportType())) h204RuleIds.add(rule.getId());


            }


            if (!h204RuleIds.isEmpty()) {


                auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()


                    .eq(AuditResult::getReportPeriod, period)


                    .in(AuditResult::getRuleId, h204RuleIds));


            }




            int success = 0;


            List<String> failDetails = new ArrayList<>();


            for (EnergyRow item : rows) {


                try {


                    energyMapper.insert(item.entity);


                    success++;


                } catch (Exception e) {


                    Throwable cause = e;


                    while (cause.getCause() != null) cause = cause.getCause();


                    String reason = cause.getMessage();


                    if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();


                    failDetails.add("第 " + item.excelRow + " 行（" + item.entity.getEnterpriseName()


                        + " " + item.entity.getPlateNo() + "）：" + reason);


                    log.error("Energy insert error: row {}, {}", item.excelRow, item.entity.getPlateNo(), e);


                }


            }


            String errorDetail = String.join("\n", failDetails);


            if (errorDetail.length() > 4000) {


                errorDetail = errorDetail.substring(0, 4000) + "\n……";


            }


            recordBatch(file.getOriginalFilename(), "H204", period, rows.size(), success, failDetails.size(), errorDetail);




            int authSuccess = 0;


            for (EnergyAuthVehicle auth : authList) {


                try {


                    energyAuthMapper.insert(auth);


                    authSuccess++;


                } catch (Exception e) {


                    log.error("energy auth insert error: {}", auth.getPlateNo(), e);


                }


            }


            recordBatch(file.getOriginalFilename(), "ENERGY_AUTH", period, authList.size(), authSuccess, 0, null);


            log.info("Energy monthly imported: {} vehicles, auth {} rows for {}", success, authSuccess, period);


            return new ImportResult(success, failDetails.size(), failDetails);


        }


    }




    /** 能耗车辆运政信息（独立模板：车牌号/车辆类型/燃料类型/标记吨位/准牵引质量） */


    public ImportResult importEnergyAuth(MultipartFile file, String period) throws Exception {


        List<EnergyAuthVehicle> authList = new ArrayList<>();


        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {


            Sheet sheet = wb.getSheetAt(0);


            requireHeaderKeyword(sheet, "能耗车辆运政信息", "车牌号");


            Row authHeader = sheet.getRow(0);


            String authH0 = authHeader == null ? null : FORMATTER.formatCellValue(authHeader.getCell(0)).trim();


            if (authH0 == null || authH0.isEmpty() || !authH0.contains("车牌号")) {


                throw new RuntimeException("能耗车辆运政信息表头与系统模板不一致：第 1 列应为「车牌号」，实际为「"


                    + (authH0 == null || authH0.isEmpty() ? "空" : authH0) + "」。请使用系统模板《模板_能耗车辆运政信息.xlsx》整理后再导入");


            }


            for (int r = 1; r <= sheet.getLastRowNum(); r++) {


                Row row = sheet.getRow(r);


                if (row == null) continue;


                String plate = getString(row, 0);


                if (plate == null || plate.trim().isEmpty() || "0".equals(plate.trim())) continue;


                EnergyAuthVehicle auth = new EnergyAuthVehicle();


                auth.setReportPeriod(period);


                auth.setPlateNo(plate.trim());


                auth.setVehicleType(getString(row, 1));


                auth.setFuelType(getString(row, 2));


                auth.setMarkedTonnage(getDecimal(row, 3));


                auth.setTractionQuality(getDecimal(row, 4));


                authList.add(auth);


            }


        } catch (Exception e) {


            log.error("Energy auth parse error", e);


            throw e;


        }


        // 幂等：同报表期重新导入，先删除旧数据；与 H204 明细共用报表期锁，防并发互相覆盖


        synchronized (energyLock(period)) {


            energyAuthMapper.delete(new LambdaQueryWrapper<EnergyAuthVehicle>()


                .eq(EnergyAuthVehicle::getReportPeriod, period));


            deleteRuleResults("H204", period);


            int success = 0;


            List<String> failDetails = new ArrayList<>();


            for (EnergyAuthVehicle auth : authList) {


                try {


                    energyAuthMapper.insert(auth);


                    success++;


                } catch (Exception e) {


                    Throwable cause = e;


                    while (cause.getCause() != null) cause = cause.getCause();


                    String reason = cause.getMessage();


                    if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();


                    failDetails.add("第 " + (authList.indexOf(auth) + 2) + " 行（" + auth.getPlateNo() + "）：" + reason);


                    log.error("Energy auth insert error: {}", auth.getPlateNo(), e);


                }


            }


            String errorDetail = String.join("\n", failDetails);


            if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";


            recordBatch(file.getOriginalFilename(), "ENERGY_AUTH", period, authList.size(), success, failDetails.size(), errorDetail);


            log.info("Energy auth imported: {} rows for {}", success, period);


            return new ImportResult(success, failDetails.size(), failDetails);


        }


    }




    /** H204 行记录：保留 Excel 行号用于失败定位 */


    private static class EnergyRow {


        final int excelRow;


        final EnergyVehicleQuarterly entity;




        EnergyRow(int excelRow, EnergyVehicleQuarterly entity) {


            this.excelRow = excelRow;


            this.entity = entity;


        }


    }




    /** 空单元格返回 null（用于能耗明细未填写项与 0 区分） */


    private Integer getIntOrNull(Row row, int idx) {


        Double val = getDecimalOrNull(row, idx);


        return val == null ? null : val.intValue();


    }




    // ========== 运政车辆 ==========




    /** 运政车辆导入：与 H203-2 月报一致，按表头名称定位列，列顺序变化也能正确导入 */


    public int importTransportAuth(MultipartFile file, String period) throws Exception {


        List<TransportAuthVehicle> list = new ArrayList<>();


        int failRows = 0;


        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {


            Sheet sheet = wb.getSheetAt(0);


            requireHeaderCols(sheet, "运政车辆", "企业名称", "总牵引车数", "总挂车数", "其它车辆数", "挂车总吨位(吨)", "其它车辆总吨位(吨)");


            Map<String, Integer> colMap = buildHeaderMap(sheet.getRow(0));


            for (int r = 1; r <= sheet.getLastRowNum(); r++) {


                Row row = sheet.getRow(r);


                if (row == null) continue;


                String name = getString(row, 0);


                String name = gs(row, colMap, "企业名称");


                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));


                entity.setTractorCount(gi(row, colMap, "总牵引车数"));


                entity.setTrailerCount(gi(row, colMap, "总挂车数"));


                entity.setOtherCount(gi(row, colMap, "其它车辆数"));


                entity.setTrailerTons(gd(row, colMap, "挂车总吨位(吨)"));


                entity.setOtherTons(gd(row, colMap, "其它车辆总吨位(吨)"));


                list.add(entity);


            }


        } catch (Exception e) {


@@ -220,20 +896,23 @@




    // ========== 轨迹里程 ==========




    /** 轨迹里程导入：与 H203-2 月报一致，按表头名称定位列，列顺序变化也能正确导入 */


    public int importTrackMileage(MultipartFile file, String period) throws Exception {


        List<VehicleTrackMileage> list = new ArrayList<>();


        int failRows = 0;


        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {


            Sheet sheet = wb.getSheetAt(0);


            requireHeaderCols(sheet, "轨迹里程", "企业名称", "月度总里程(公里)", "有里程的车辆数");


            Map<String, Integer> colMap = buildHeaderMap(sheet.getRow(0));


            for (int r = 1; r <= sheet.getLastRowNum(); r++) {


                Row row = sheet.getRow(r);


                if (row == null) continue;


                String name = getString(row, 1);


                String name = gs(row, colMap, "企业名称");


                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.setMonthlyMileage(gd(row, colMap, "月度总里程(公里)"));


                entity.setTrackedVehicles(gi(row, colMap, "有里程的车辆数"));


                entity.setReportPeriod(period);


                list.add(entity);


            }


@@ -268,13 +947,44 @@


        List<ScaleSplitTransport> list = new ArrayList<>();


        int failRows = 0;


        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {


            int targetMonth = Integer.parseInt(period.split("-")[1]); // 仅导入目标期所在月的页签（1-N月文件按目标期取当月/累计）


            boolean hasPeriodSheet = false;


            for (int i = 0; i < wb.getNumberOfSheets(); i++) {


                String sn = wb.getSheetAt(i).getSheetName();


                if (sn != null && (sn.contains("当月") || sn.contains("月份") || sn.contains("累计"))) { hasPeriodSheet = true; break; }


            }


            if (!hasPeriodSheet) {


                throw new RuntimeException("所选数据类型【规上规下拆分】与文件内容不符：未找到含 当月/累计 的 sheet，请确认是否选错了数据类型");


            }


            for (int i = 0; i < wb.getNumberOfSheets(); i++) {


                Sheet sheet = wb.getSheetAt(i);


                String sheetName = sheet.getSheetName();


                String periodType = null;


                if (sheetName.contains("当月")) periodType = "MONTH";


                else if (sheetName.contains("累计")) periodType = "CUMULATIVE";


                java.util.regex.Matcher mSheetMonth = java.util.regex.Pattern.compile("(\\d{1,2})\\u6708(\\u4efd|\\u7d2f\\u8ba1)").matcher(sheetName);


                if (mSheetMonth.find()) {


                    int sheetMonth = Integer.parseInt(mSheetMonth.group(1));


                    if (sheetMonth == targetMonth) {


                        periodType = "份".equals(mSheetMonth.group(2)) ? "MONTH" : "CUMULATIVE";


                    }


                } else {


                    if (sheetName.contains("当月")) periodType = "MONTH";


                    else if (sheetName.contains("累计")) periodType = "CUMULATIVE";


                }


                if (periodType == null) continue;




                // 校验 sheet 布局：第2行应含「市州/规上企业运输量」，第3行子表头应含「周转量」或「货运量」（扩展布局）


                Row headRow = sheet.getRow(1);


                Row subHeadRow = sheet.getRow(2);


                boolean layoutOk = headRow != null


                    && getString(headRow, 0) != null && getString(headRow, 0).contains("市州")


                    && getString(headRow, 1) != null && getString(headRow, 1).contains("规上")


                    && subHeadRow != null


                    && ("货运量".equals(getString(subHeadRow, 0))


                        || (getString(subHeadRow, 1) != null && getString(subHeadRow, 1).contains("周转量")));


                if (!layoutOk) {


                    throw new RuntimeException("所选数据类型【规上规下拆分】与文件内容不符：sheet「" + sheetName


                        + "」未找到「市州/规上企业运输量/周转量」表头，请确认是否选错了数据类型");


                }




                // 检测子表头：是否包含货运量列（扩展布局）


                Row subHeader = sheet.getRow(2);


@@ -352,12 +1062,41 @@


    // ========== 货运量周转量（模板_货运量周转量.xlsx，每月导入） ==========




    public int importFreightTurnover(MultipartFile file, String period) throws Exception {


        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {


            // 多年度工作簿（一年一个 Sheet：整年 1-12 月，如 21-26 年定稿文件）→ 全部年份入库


            List<String> yearSheets = detectYearSheets(wb);


            if (!yearSheets.isEmpty()) {


                return importFreightTurnoverByYear(wb, file, yearSheets);


            }


            return importFreightTurnoverByMonth(wb, file, period);


        }


    }




    /** 每月导入（模板_货运量周转量.xlsx）：左半今年 1-N 月，右半去年 1-12 月 */


    private int importFreightTurnoverByMonth(Workbook wb, MultipartFile file, String period) throws Exception {


        List<FreightTurnoverImport> list = new ArrayList<>();


        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);


            Row header = sheet.getRow(0);


            String h0 = header == null ? null : getString(header, 0);


            String h1 = header == null ? null : getString(header, 1);


            if (header == null || h0 == null || !h0.contains("货运量") || h1 == null || !h1.contains("月")) {


                throw new RuntimeException("所选数据类型【货运量周转量】与文件内容不符：表头应为「货运量(万吨)/1月…12月」布局，请确认是否选错了数据类型");


            }


            boolean cityRowFound = false;


            for (int r = 1; r <= Math.min(sheet.getLastRowNum(), 30); r++) {


                Row rr = sheet.getRow(r);


                if (rr == null) continue;


                String nm = getString(rr, 0);


                if (nm == null || nm.trim().isEmpty()) continue;


                String region = RegionUtil.normalizeCityName(nm);


                if (RegionUtil.cityList().contains(region) || "湖北省".equals(region)) { cityRowFound = true; break; }


            }


            if (!cityRowFound) {


                throw new RuntimeException("所选数据类型【货运量周转量】与文件内容不符：未识别到市州行（首列应为市州名称），请确认是否选错了数据类型");


            }


            Map<String, FreightTurnoverImport> freightMap = new HashMap<>();


            Map<String, FreightTurnoverImport> turnoverMap = new HashMap<>();


            Set<String> seen = new HashSet<>();


@@ -403,27 +1142,149 @@


                }


                list.add(entity);


            }


        } catch (Exception e) {


            throw e;


        }


        Map<String, List<FreightTurnoverImport>> byPeriod = new LinkedHashMap<>();


        byPeriod.put(period, list);


        return persistFreightTurnover(byPeriod, file.getOriginalFilename());


    }




        // 幂等：同报表期先删除


        freightTurnoverMapper.delete(new LambdaQueryWrapper<FreightTurnoverImport>()


            .eq(FreightTurnoverImport::getReportPeriod, period));


    /** 识别「多年度工作簿」：每个 Sheet 一年（Sheet 名 2021年/2022年…），且表头为「货运量/1月…12月」；否则返回空列表 */


    private List<String> detectYearSheets(Workbook wb) {


        List<String> names = new ArrayList<>();


        if (wb.getNumberOfSheets() < 2) return names;


        for (int i = 0; i < wb.getNumberOfSheets(); i++) {


            Sheet sheet = wb.getSheetAt(i);


            String nm = sheet.getSheetName() == null ? "" : sheet.getSheetName().trim();


            if (!nm.matches("\\d{4}\\s*年?")) { names.clear(); return names; }


            Row header = sheet.getRow(0);


            String h0 = header == null ? null : getString(header, 0);


            String h1 = header == null ? null : getString(header, 1);


            if (h0 == null || !h0.contains("货运量") || h1 == null || !h1.contains("月")) {


                names.clear();


                return names;


            }


            names.add(nm);


        }


        return names;


    }




        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++;


    /** 多年度工作簿导入：逐年一条报表期（报表期=该年最后有数据的月份），上年同期取上一年度 Sheet */


    private int importFreightTurnoverByYear(Workbook wb, MultipartFile file, List<String> yearSheets) {


        Map<Integer, String> sheetByYear = new TreeMap<>();


        for (String sheetName : yearSheets) {


            sheetByYear.put(Integer.parseInt(sheetName.replace("年", "").trim()), sheetName);


        }


        Map<Integer, Map<String, FreightTurnoverImport>> byYear = new TreeMap<>();


        Map<Integer, Integer> maxMonthByYear = new HashMap<>();


        for (Map.Entry<Integer, String> entry : sheetByYear.entrySet()) {


            Map<String, FreightTurnoverImport> parsed = new LinkedHashMap<>();


            int maxMonth = parseWholeYearSheet(wb.getSheet(entry.getValue()), parsed);


            if (parsed.isEmpty()) continue;


            byYear.put(entry.getKey(), parsed);


            maxMonthByYear.put(entry.getKey(), Math.max(1, Math.min(12, maxMonth)));


        }


        Map<String, List<FreightTurnoverImport>> byPeriod = new LinkedHashMap<>();


        for (Map.Entry<Integer, Map<String, FreightTurnoverImport>> entry : byYear.entrySet()) {


            int year = entry.getKey();


            String yearPeriod = year + "-" + String.format("%02d", maxMonthByYear.get(year));


            Map<String, FreightTurnoverImport> lastYear = byYear.get(year - 1);


            List<FreightTurnoverImport> rows = new ArrayList<>();


            for (Map.Entry<String, FreightTurnoverImport> regionEntry : entry.getValue().entrySet()) {


                FreightTurnoverImport entity = regionEntry.getValue();


                entity.setReportPeriod(yearPeriod);


                // 上年同期 = 上一年度 Sheet（多年度文件没有右半「去年1-12月」区块）


                FreightTurnoverImport last = lastYear == null ? null : lastYear.get(regionEntry.getKey());


                if (last != null) {


                    for (int m = 1; m <= 12; m++) {


                        setLastFreight(entity, m, getFreight(last, m));


                        setLastTurnover(entity, m, getTurnover(last, m));


                    }


                }


                rows.add(entity);


            }


            byPeriod.put(yearPeriod, rows);


        }


        return persistFreightTurnover(byPeriod, file.getOriginalFilename());


    }




    /** 解析整年 Sheet（1-12 月列）：货运量块 + 周转量块；返回最后一个有货运量数据的月份 */


    private int parseWholeYearSheet(Sheet sheet, Map<String, FreightTurnoverImport> out) {


        Map<String, FreightTurnoverImport> freightMap = new LinkedHashMap<>();


        Map<String, FreightTurnoverImport> turnoverMap = new LinkedHashMap<>();


        Set<String> seen = new HashSet<>();


        int maxMonth = 0;


        for (int r = 1; r <= sheet.getLastRowNum(); r++) {


            Row row = sheet.getRow(r);


            if (row == null) continue;


            String name = getString(row, 0);


            if (name == null || name.trim().isEmpty()) continue;


            String region = RegionUtil.normalizeCityName(name);


            boolean isCity = RegionUtil.cityList().contains(region);


            if (!isCity && !"湖北省".equals(region)) continue; // 表头/说明行


            if (seen.contains(region)) {


                // 该名称已出现过 -> 周转量区块


                FreightTurnoverImport entity = turnoverMap.get(region);


                if (entity == null) {


                    entity = new FreightTurnoverImport();


                    entity.setRegionName(name.trim());


                    turnoverMap.put(region, entity);


                }


                for (int m = 1; m <= 12; m++) {


                    setTurnover(entity, m, getDecimalOrNull(row, m));


                }


            } else {


                seen.add(region);


                FreightTurnoverImport entity = freightMap.get(region);


                if (entity == null) {


                    entity = new FreightTurnoverImport();


                    entity.setRegionName(name.trim());


                    freightMap.put(region, entity);


                }


                for (int m = 1; m <= 12; m++) {


                    Double v = getDecimalOrNull(row, m);


                    setFreight(entity, m, v);


                    if (v != null && m > maxMonth) maxMonth = m;


                }


            }


        }


        recordBatch(file.getOriginalFilename(), "FREIGHT_TURNOVER", period, list.size(), success, failRows, null);


        log.info("Freight turnover imported: {} rows for {}", success, period);


        return success;


        // 合并：一个市州一行（货运量 + 周转量）


        for (Map.Entry<String, FreightTurnoverImport> entry : freightMap.entrySet()) {


            FreightTurnoverImport entity = entry.getValue();


            FreightTurnoverImport turnover = turnoverMap.get(entry.getKey());


            if (turnover != null) {


                for (int m = 1; m <= 12; m++) {


                    setTurnover(entity, m, getTurnover(turnover, m));


                }


            }


            out.put(entry.getKey(), entity);


        }


        return maxMonth;


    }




    /** 落库：按报表期先删后插（幂等），每个报表期一条导入批次记录，返回成功条数 */


    private int persistFreightTurnover(Map<String, List<FreightTurnoverImport>> byPeriod, String fileName) {


        int total = 0;


        for (Map.Entry<String, List<FreightTurnoverImport>> entry : byPeriod.entrySet()) {


            String p = entry.getKey();


            List<FreightTurnoverImport> rows = entry.getValue();


            freightTurnoverMapper.delete(new LambdaQueryWrapper<FreightTurnoverImport>()


                .eq(FreightTurnoverImport::getReportPeriod, p));


            int failRows = 0;


            int success = 0;


            for (FreightTurnoverImport entity : rows) {


                try {


                    freightTurnoverMapper.insert(entity);


                    success++;


                } catch (Exception e) {


                    log.error("freight turnover insert error: {}", entity.getRegionName(), e);


                    failRows++;


                }


            }


            recordBatch(fileName, "FREIGHT_TURNOVER", p, rows.size(), success, failRows, null);


            log.info("Freight turnover imported: {} rows for {}", success, p);


            total += success;


        }


        return total;


    }




    /** 解析货运量区块行：左半 B 起 N 列为今年 1-N 月，右半为去年 1-12 月 */


@@ -601,6 +1462,381 @@


        }


    }




    /** 支持“按类型清空”的数据类型 -> 数据表操作键（同表同类别归并，避免重复删除） */


    private static final Map<String, String> CLEAR_TYPE_TO_OP = new LinkedHashMap<>();


    /** 数据类型 -> 关联审核规则类型（同步清除审核结果与已审核标记） */


    private static final Map<String, String[]> CLEAR_TYPE_TO_AUDIT = new HashMap<>();


    /** 数据类型 -> 关联导入批次 importType（同步清除导入批次记录） */


    private static final Map<String, String[]> CLEAR_TYPE_TO_BATCH = new HashMap<>();




    static {


        CLEAR_TYPE_TO_OP.put("h2032", "H2032");


        CLEAR_TYPE_TO_OP.put("transportAuth", "TRANSPORT_AUTH");


        CLEAR_TYPE_TO_OP.put("trackMileage", "TRACK_MILEAGE");


        CLEAR_TYPE_TO_OP.put("scaleSplit", "SCALE_SPLIT");


        CLEAR_TYPE_TO_OP.put("freightTurnover", "FREIGHT_TURNOVER");


        CLEAR_TYPE_TO_OP.put("passengerMonthly", "H2031");


        CLEAR_TYPE_TO_OP.put("passengerAuth", "PASSENGER_AUTH");


        CLEAR_TYPE_TO_OP.put("passengerIndividual", "PASSENGER_INDIVIDUAL");


        CLEAR_TYPE_TO_OP.put("energyMonthly", "H204");


        CLEAR_TYPE_TO_OP.put("energyAuth", "ENERGY_AUTH");


        CLEAR_TYPE_TO_OP.put("investment", "INVEST_MONTHLY_PASSENGER");


        CLEAR_TYPE_TO_OP.put("investCityPassenger", "INVEST_MONTHLY_PASSENGER");


        CLEAR_TYPE_TO_OP.put("investmentLogistics", "INVEST_MONTHLY_LOGISTICS");


        CLEAR_TYPE_TO_OP.put("investCityLogistics", "INVEST_MONTHLY_LOGISTICS");


        CLEAR_TYPE_TO_OP.put("investmentSystem", "INVEST_SYSTEM");


        CLEAR_TYPE_TO_OP.put("cityBus", "CITY_BUS");


        CLEAR_TYPE_TO_OP.put("cityTaxi", "CITY_TAXI");


        CLEAR_TYPE_TO_OP.put("cityTaxiAuth", "CITY_TAXI_AUTH");


        CLEAR_TYPE_TO_OP.put("wycTotal", "WYC_TOTAL");


        CLEAR_TYPE_TO_OP.put("wycOrder", "WYC_ORDER");


        CLEAR_TYPE_TO_OP.put("wycOrderReport", "WYC_ORDER");




        CLEAR_TYPE_TO_AUDIT.put("h2032", new String[]{"H2032"});


        CLEAR_TYPE_TO_AUDIT.put("transportAuth", new String[]{"H2032"});


        CLEAR_TYPE_TO_AUDIT.put("trackMileage", new String[]{"H2032"});


        CLEAR_TYPE_TO_AUDIT.put("scaleSplit", new String[]{"H2032"});


        CLEAR_TYPE_TO_AUDIT.put("freightTurnover", new String[]{"H2032"});


        CLEAR_TYPE_TO_AUDIT.put("passengerMonthly", new String[]{"H2031"});


        CLEAR_TYPE_TO_AUDIT.put("passengerAuth", new String[]{"H2031"});


        CLEAR_TYPE_TO_AUDIT.put("passengerIndividual", new String[]{"H2031"});


        CLEAR_TYPE_TO_AUDIT.put("energyMonthly", new String[]{"H204"});


        CLEAR_TYPE_TO_AUDIT.put("energyAuth", new String[]{"H204"});


        CLEAR_TYPE_TO_AUDIT.put("investment", new String[]{"INVEST"});


        CLEAR_TYPE_TO_AUDIT.put("investmentLogistics", new String[]{"INVEST"});


        CLEAR_TYPE_TO_AUDIT.put("investCityPassenger", new String[]{"INVEST"});


        CLEAR_TYPE_TO_AUDIT.put("investCityLogistics", new String[]{"INVEST"});


        CLEAR_TYPE_TO_AUDIT.put("investmentSystem", new String[]{"INVEST"});


        CLEAR_TYPE_TO_AUDIT.put("cityBus", new String[]{"CITY_BUS"});


        CLEAR_TYPE_TO_AUDIT.put("cityTaxi", new String[]{"CITY_TAXI"});


        CLEAR_TYPE_TO_AUDIT.put("cityTaxiAuth", new String[]{"CITY_TAXI"});




        String[] investPassengerBatch = {"INVEST_客运站场", "INVEST_客运站场_CITY"};


        String[] investLogisticsBatch = {"INVEST_物流园区", "INVEST_物流园区_CITY"};


        CLEAR_TYPE_TO_BATCH.put("h2032", new String[]{"H2032"});


        CLEAR_TYPE_TO_BATCH.put("transportAuth", new String[]{"TRANSPORT_AUTH"});


        CLEAR_TYPE_TO_BATCH.put("trackMileage", new String[]{"TRACK_MILEAGE"});


        CLEAR_TYPE_TO_BATCH.put("scaleSplit", new String[]{"SCALE_SPLIT"});


        CLEAR_TYPE_TO_BATCH.put("freightTurnover", new String[]{"FREIGHT_TURNOVER"});


        CLEAR_TYPE_TO_BATCH.put("passengerMonthly", new String[]{"H2031"});


        CLEAR_TYPE_TO_BATCH.put("passengerAuth", new String[]{"PASSENGER_AUTH"});


        CLEAR_TYPE_TO_BATCH.put("passengerIndividual", new String[]{"PASSENGER_INDIVIDUAL"});


        CLEAR_TYPE_TO_BATCH.put("energyMonthly", new String[]{"H204"});


        CLEAR_TYPE_TO_BATCH.put("energyAuth", new String[]{"ENERGY_AUTH"});


        CLEAR_TYPE_TO_BATCH.put("investment", investPassengerBatch);


        CLEAR_TYPE_TO_BATCH.put("investCityPassenger", investPassengerBatch);


        CLEAR_TYPE_TO_BATCH.put("investmentLogistics", investLogisticsBatch);


        CLEAR_TYPE_TO_BATCH.put("investCityLogistics", investLogisticsBatch);


        CLEAR_TYPE_TO_BATCH.put("investmentSystem", new String[]{"INVEST_SYSTEM"});


        CLEAR_TYPE_TO_BATCH.put("cityBus", new String[]{"CITY_BUS"});


        CLEAR_TYPE_TO_BATCH.put("cityTaxi", new String[]{"CITY_TAXI"});


        CLEAR_TYPE_TO_BATCH.put("cityTaxiAuth", new String[]{"CITY_TAXI_AUTH"});


        CLEAR_TYPE_TO_BATCH.put("wycTotal", new String[]{"WYC_TOTAL"});


        CLEAR_TYPE_TO_BATCH.put("wycOrder", new String[]{"WYC_ORDER", "WYC_ORDER_REPORT"});


        CLEAR_TYPE_TO_BATCH.put("wycOrderReport", new String[]{"WYC_ORDER_REPORT", "WYC_ORDER"});


    }




    /**


     * 清空指定报表期已导入数据与审核结果，用于整月重导；投资项目主档保留。


     *


     * @param types 需清空的数据类型（前端类型键，如 h2032/cityBus）；为空则清空该报表期全部类型


     */


    @Transactional(rollbackFor = Exception.class)


    public Map<String, Object> clearPeriod(String period, List<String> types) {


        if (period == null || !period.matches("\\d{4}-\\d{2}")) {


            throw new IllegalArgumentException("报表期格式不正确，应为 yyyy-MM");


        }


        Set<String> selected = new LinkedHashSet<>();


        if (types != null) {


            for (String t : types) {


                if (t != null && !t.trim().isEmpty()) selected.add(t.trim());


            }


        }


        boolean clearAll = selected.isEmpty();


        int dataRows = 0;


        int auditRows = 0;


        int auditRunRows = 0;


        int batchRows = 0;


        if (clearAll) {


        dataRows += h2032Mapper.delete(new LambdaQueryWrapper<H2032EnterpriseMonthly>()


            .eq(H2032EnterpriseMonthly::getReportPeriod, period));


        dataRows += transportAuthMapper.delete(new LambdaQueryWrapper<TransportAuthVehicle>()


            .eq(TransportAuthVehicle::getReportPeriod, period));


        dataRows += trackMileageMapper.delete(new LambdaQueryWrapper<VehicleTrackMileage>()


            .eq(VehicleTrackMileage::getReportPeriod, period));


        dataRows += scaleSplitMapper.delete(new LambdaQueryWrapper<ScaleSplitTransport>()


            .eq(ScaleSplitTransport::getReportPeriod, period));


        dataRows += freightTurnoverMapper.delete(new LambdaQueryWrapper<FreightTurnoverImport>()


            .eq(FreightTurnoverImport::getReportPeriod, period));


        dataRows += passengerMapper.delete(new LambdaQueryWrapper<PassengerEnterpriseMonthly>()


            .eq(PassengerEnterpriseMonthly::getReportPeriod, period));


        dataRows += passengerIndividualMapper.delete(new LambdaQueryWrapper<PassengerIndividualMonthly>()


            .eq(PassengerIndividualMonthly::getReportPeriod, period));


        dataRows += passengerAuthMapper.delete(new LambdaQueryWrapper<PassengerAuthVehicle>()


            .eq(PassengerAuthVehicle::getReportPeriod, period));


        dataRows += energyMapper.delete(new LambdaQueryWrapper<EnergyVehicleQuarterly>()


            .eq(EnergyVehicleQuarterly::getReportPeriod, period));


        dataRows += energyAuthMapper.delete(new LambdaQueryWrapper<EnergyAuthVehicle>()


            .eq(EnergyAuthVehicle::getReportPeriod, period));


        dataRows += investMonthlyMapper.delete(new LambdaQueryWrapper<InvestmentMonthly>()


            .eq(InvestmentMonthly::getReportPeriod, period));


        dataRows += investSystemMapper.delete(new LambdaQueryWrapper<InvestmentSystem>()


            .eq(InvestmentSystem::getReportPeriod, period));


        dataRows += cityBusMapper.delete(new LambdaQueryWrapper<CityBusMonthly>()


            .eq(CityBusMonthly::getReportPeriod, period));


        dataRows += cityTaxiMapper.delete(new LambdaQueryWrapper<CityTaxiMonthly>()


            .eq(CityTaxiMonthly::getReportPeriod, period));


        dataRows += cityTaxiAuthMapper.delete(new LambdaQueryWrapper<CityTaxiAuth>()


            .eq(CityTaxiAuth::getReportPeriod, period));


        dataRows += wycTotalMapper.delete(new LambdaQueryWrapper<WycTotalMonthly>()


            .eq(WycTotalMonthly::getReportPeriod, period));


        dataRows += wycOrderMapper.delete(new LambdaQueryWrapper<WycOrderMonthly>()


            .eq(WycOrderMonthly::getReportPeriod, period));


        auditRows = auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()


            .eq(AuditResult::getReportPeriod, period));


        auditRunRows = auditRunMapper.delete(new LambdaQueryWrapper<AuditRun>()


            .eq(AuditRun::getReportPeriod, period));


        batchRows = importBatchMapper.delete(new LambdaQueryWrapper<ImportBatch>()


            .eq(ImportBatch::getReportPeriod, period));


        } else {


            Set<String> ops = new LinkedHashSet<>();


            Set<String> auditTypes = new LinkedHashSet<>();


            Set<String> batchTypes = new LinkedHashSet<>();


            for (String t : selected) {


                String op = CLEAR_TYPE_TO_OP.get(t);


                if (op == null) {


                    log.warn("清空数据：未知数据类型 {}，已忽略", t);


                    continue;


                }


                ops.add(op);


                String[] at = CLEAR_TYPE_TO_AUDIT.get(t);


                if (at != null) java.util.Collections.addAll(auditTypes, at);


                String[] bt = CLEAR_TYPE_TO_BATCH.get(t);


                if (bt != null) java.util.Collections.addAll(batchTypes, bt);


            }


            if (ops.isEmpty()) {


                throw new IllegalArgumentException("未指定有效的数据类型");


            }


            for (String op : ops) {


                dataRows += clearTypeData(op, period);


            }


            if (!auditTypes.isEmpty()) {


                List<Long> ruleIds = new ArrayList<>();


                for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {


                    if (rule.getReportType() != null && auditTypes.contains(rule.getReportType())) {


                        ruleIds.add(rule.getId());


                    }


                }


                if (!ruleIds.isEmpty()) {


                    auditRows = auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()


                        .eq(AuditResult::getReportPeriod, period)


                        .in(AuditResult::getRuleId, ruleIds));


                }


                for (String rt : auditTypes) {


                    auditRunRows += auditRunMapper.delete(new LambdaQueryWrapper<AuditRun>()


                        .eq(AuditRun::getReportPeriod, period)


                        .eq(AuditRun::getReportType, rt));


                }


            }


            if (!batchTypes.isEmpty()) {


                batchRows = importBatchMapper.delete(new LambdaQueryWrapper<ImportBatch>()


                    .eq(ImportBatch::getReportPeriod, period)


                    .in(ImportBatch::getImportType, batchTypes));


            }


        }


        Map<String, Object> stats = new LinkedHashMap<>();


        stats.put("period", period);


        stats.put("scope", clearAll ? "all" : "types");


        if (!clearAll) stats.put("types", new ArrayList<>(selected));


        stats.put("dataRows", dataRows);


        stats.put("auditRows", auditRows);


        stats.put("auditRunRows", auditRunRows);


        stats.put("batchRows", batchRows);


        log.info("清空报表期 {}（{}）完成：业务数据 {} 行、审核结果 {} 行、审核标记 {} 行、导入批次 {} 行",


            period, clearAll ? "全部类型" : String.join(",", selected), dataRows, auditRows, auditRunRows, batchRows);


        return stats;


    }




    /** 按“数据表操作键”清空对应业务表（投资月度按项目类别过滤），返回删除行数 */


    private int clearTypeData(String op, String period) {


        switch (op) {


            case "H2032":


                return h2032Mapper.delete(new LambdaQueryWrapper<H2032EnterpriseMonthly>()


                    .eq(H2032EnterpriseMonthly::getReportPeriod, period));


            case "TRANSPORT_AUTH":


                return transportAuthMapper.delete(new LambdaQueryWrapper<TransportAuthVehicle>()


                    .eq(TransportAuthVehicle::getReportPeriod, period));


            case "TRACK_MILEAGE":


                return trackMileageMapper.delete(new LambdaQueryWrapper<VehicleTrackMileage>()


                    .eq(VehicleTrackMileage::getReportPeriod, period));


            case "SCALE_SPLIT":


                return scaleSplitMapper.delete(new LambdaQueryWrapper<ScaleSplitTransport>()


                    .eq(ScaleSplitTransport::getReportPeriod, period));


            case "FREIGHT_TURNOVER":


                return freightTurnoverMapper.delete(new LambdaQueryWrapper<FreightTurnoverImport>()


                    .eq(FreightTurnoverImport::getReportPeriod, period));


            case "H2031":


                return passengerMapper.delete(new LambdaQueryWrapper<PassengerEnterpriseMonthly>()


                    .eq(PassengerEnterpriseMonthly::getReportPeriod, period));


            case "PASSENGER_AUTH":


                return passengerAuthMapper.delete(new LambdaQueryWrapper<PassengerAuthVehicle>()


                    .eq(PassengerAuthVehicle::getReportPeriod, period));


            case "PASSENGER_INDIVIDUAL":


                return passengerIndividualMapper.delete(new LambdaQueryWrapper<PassengerIndividualMonthly>()


                    .eq(PassengerIndividualMonthly::getReportPeriod, period));


            case "H204":


                return energyMapper.delete(new LambdaQueryWrapper<EnergyVehicleQuarterly>()


                    .eq(EnergyVehicleQuarterly::getReportPeriod, period));


            case "ENERGY_AUTH":


                return energyAuthMapper.delete(new LambdaQueryWrapper<EnergyAuthVehicle>()


                    .eq(EnergyAuthVehicle::getReportPeriod, period));


            case "INVEST_MONTHLY_PASSENGER":


                return deleteInvestmentMonthly(period, "客运站场");


            case "INVEST_MONTHLY_LOGISTICS":


                return deleteInvestmentMonthly(period, "物流园区");


            case "INVEST_SYSTEM":


                return investSystemMapper.delete(new LambdaQueryWrapper<InvestmentSystem>()


                    .eq(InvestmentSystem::getReportPeriod, period));


            case "CITY_BUS":


                return cityBusMapper.delete(new LambdaQueryWrapper<CityBusMonthly>()


                    .eq(CityBusMonthly::getReportPeriod, period));


            case "CITY_TAXI":


                return cityTaxiMapper.delete(new LambdaQueryWrapper<CityTaxiMonthly>()


                    .eq(CityTaxiMonthly::getReportPeriod, period));


            case "CITY_TAXI_AUTH":


                return cityTaxiAuthMapper.delete(new LambdaQueryWrapper<CityTaxiAuth>()


                    .eq(CityTaxiAuth::getReportPeriod, period));


            case "WYC_TOTAL":


                return wycTotalMapper.delete(new LambdaQueryWrapper<WycTotalMonthly>()


                    .eq(WycTotalMonthly::getReportPeriod, period));


            case "WYC_ORDER":


                return wycOrderMapper.delete(new LambdaQueryWrapper<WycOrderMonthly>()


                    .eq(WycOrderMonthly::getReportPeriod, period));


            default:


                log.warn("清空数据：未知数据表操作 {}", op);


                return 0;


        }


    }




    /** 投资月度按项目类别清除（投资项目主档保留） */


    private int deleteInvestmentMonthly(String period, String category) {


        List<Long> projectIds = new ArrayList<>();


        for (InvestmentProject p : investProjectMapper.selectList(new LambdaQueryWrapper<InvestmentProject>()


                .eq(InvestmentProject::getCategory, category))) {


            projectIds.add(p.getId());


        }


        if (projectIds.isEmpty()) return 0;


        return investMonthlyMapper.delete(new LambdaQueryWrapper<InvestmentMonthly>()


            .eq(InvestmentMonthly::getReportPeriod, period)


            .in(InvestmentMonthly::getProjectId, projectIds));


    }




    /** 数据变化后删除指定报表类型指定报表期的审核结果（需重新执行审核） */


    private void deleteRuleResults(String reportType, String period) {


        List<Long> ruleIds = new ArrayList<>();


        for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {


            if (reportType.equals(rule.getReportType())) ruleIds.add(rule.getId());


        }


        if (!ruleIds.isEmpty()) {


            auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()


                .eq(AuditResult::getReportPeriod, period)


                .in(AuditResult::getRuleId, ruleIds));


        }


    }




    /** 类型-表头校验：按必需列名（解析器实际依赖的列），缺失即抛"类型与文件不符" */


    private void requireHeaderCols(Sheet sheet, String typeLabel, String... cols) {


        Map<String, Integer> colMap = buildHeaderMap(sheet.getRow(0));


        List<String> missing = new ArrayList<>();


        for (String c : cols) {


            if (!colMap.containsKey(c)) missing.add(c);


        }


        if (!missing.isEmpty()) {


            throw new RuntimeException("所选数据类型【" + typeLabel + "】与文件内容不符：表头缺少必需列【" + String.join("】【", missing) + "】，请确认是否选错了数据类型");


        }


    }




    /** 类型-表头校验：扫描表头行，命中任一关键字即通过 */


    private void requireHeaderKeyword(Sheet sheet, String typeLabel, String... keywords) {


        Row header = sheet == null ? null : sheet.getRow(0);


        if (header != null) {


            for (Cell cell : header) {


                String v = FORMATTER.formatCellValue(cell);


                if (v == null || v.isEmpty()) continue;


                for (String k : keywords) {


                    if (v.contains(k)) return;


                }


            }


        }


        throw new RuntimeException("所选数据类型【" + typeLabel + "】与文件内容不符：表头未包含【" + String.join("】【", keywords) + "】等列，请确认是否选错了数据类型");


    }




    /** 能耗明细表头列位校验：系统模板为“前3列 + 5组×18列”，程序按固定列位解析，表头错位会导致整表错乱，错位时直接拒绝 */


    private void requireEnergyHeaderLayout(Sheet sheet) {


        Row header = sheet == null ? null : sheet.getRow(0);


        if (header == null) {


            throw new RuntimeException("能耗明细表头缺失，请使用系统模板《模板_道路货运车辆能源消耗情况（2026）.xlsx》整理后再导入");


        }


        for (int i = 0; i < ENERGY_HEADER_COLS.length; i++) {


            int col = ENERGY_HEADER_COLS[i];


            String keyword = ENERGY_HEADER_KEYWORDS[i];


            Cell cell = header.getCell(col);


            String hv = cell == null ? null : FORMATTER.formatCellValue(cell).trim();


            if (hv == null || hv.isEmpty() || !hv.contains(keyword)) {


                throw new RuntimeException("表头与系统模板不一致：第 " + (col + 1) + " 列应为「" + keyword + "」，实际为「"


                    + (hv == null || hv.isEmpty() ? "空" : hv) + "」。请使用系统模板《模板_道路货运车辆能源消耗情况（2026）.xlsx》整理后再导入");


            }


        }


    }




    /** 通用表头列位校验：程序按固定列位解析，表头错位会导致整表错乱，错位时直接拒绝导入 */


    private void requireHeaderLayout(Sheet sheet, String typeLabel, String templateHint,


                                     int[] cols, String[] keywords) {


        Row header = sheet == null ? null : sheet.getRow(0);


        if (header == null) {


            throw new RuntimeException("所选数据类型【" + typeLabel + "】文件表头缺失，请使用系统模板《" + templateHint + "》整理后再导入");


        }


        for (int i = 0; i < cols.length; i++) {


            int col = cols[i];


            String keyword = keywords[i];


            Cell cell = header.getCell(col);


            String hv = cell == null ? null : FORMATTER.formatCellValue(cell).trim();


            if (hv == null || hv.isEmpty() || !hv.contains(keyword)) {


                throw new RuntimeException("表头与系统模板不一致：第 " + (col + 1) + " 列应为「" + keyword + "」，实际为「"


                    + (hv == null || hv.isEmpty() ? "空" : hv) + "」。所选数据类型【" + typeLabel + "】请使用系统模板《" + templateHint + "》整理后再导入");


            }


        }


    }




    /** 按表头关键字定位列号：从第 1 列向右扫，命中任一关键字即返回该列；未命中返回 -1 */


    private int findColByKeywords(Row header, String... keywords) {


        if (header == null) return -1;


        for (int c = 0; c <= header.getLastCellNum(); c++) {


            Cell cell = header.getCell(c);


            if (cell == null) continue;


            String h = FORMATTER.formatCellValue(cell).trim();


            if (h.isEmpty()) continue;


            for (String k : keywords) {


                if (h.contains(k)) return c;


            }


        }


        return -1;


    }




    /** 表头行文本（报错提示用），最多取 12 列 */


    private String headerText(Row header) {


        if (header == null) return "空";


        StringBuilder sb = new StringBuilder();


        for (int c = 0; c < Math.min(header.getLastCellNum(), 12); c++) {


            Cell cell = header.getCell(c);


            String h = cell == null ? "" : FORMATTER.formatCellValue(cell).trim();


            if (h.isEmpty()) continue;


            if (sb.length() > 0) sb.append(" | ");


            sb.append(h);


        }


        return sb.length() == 0 ? "空" : sb.toString();


    }




    /** 按表头名称建立列号映射，兼容新旧模板列位差异 */


    private Map<String, Integer> buildHeaderMap(Row header) {


        Map<String, Integer> colMap = new HashMap<>();


@@ -612,6 +1848,33 @@


            }


        }


        return colMap;


    }




    /** 兼容表头映射：去引号/去空白；纯英文表头统一大写（如 'OWNERNAME' → OWNERNAME），便于新旧模板混用 */


    private Map<String, Integer> buildHeaderMapCompat(Row header) {


        Map<String, Integer> colMap = new HashMap<>();


        if (header == null) return colMap;


        for (Cell cell : header) {


            if (cell == null) continue;


            String h = FORMATTER.formatCellValue(cell).replace("'", "").replace("\"", "").trim();


            if (h.isEmpty()) continue;


            boolean ascii = true;


            for (int i = 0; i < h.length(); i++) {


                if (h.charAt(i) > 127) { ascii = false; break; }


            }


            if (ascii) h = h.toUpperCase();


            if (!colMap.containsKey(h)) colMap.put(h, cell.getColumnIndex());


        }


        return colMap;


    }




    /** 按别名顺序取第一个存在的列号，全部缺失返回 null */


    private Integer firstColIdx(Map<String, Integer> colMap, String... aliases) {


        for (String a : aliases) {


            Integer idx = colMap.get(a);


            if (idx != null) return idx;


        }


        return null;


    }




    private int colIdx(Map<String, Integer> colMap, String header) {


@@ -662,10 +1925,26 @@


        }


    }




    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;


    }


@@ -675,6 +1954,10 @@


        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();


@@ -699,6 +1982,9 @@


        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 {


@@ -707,4 +1993,2171 @@


            return null;


        }


    }


    // ========== 投资模块（客运站场 / 物流园区，全省汇总大表） ==========




    /** 亿元投资项目固定名单（2026年规上项目表） */


    private static final java.util.Set<String> INVEST_BILLION_NAMES = new LinkedHashSet<>(Arrays.asList(


        "汉口客运中心", "钟祥市综合交通客运枢纽一期项目", "航发智慧物流园", "国胜公路港",


        "十堰生产服务型国家物流枢纽工程项目", "秭归县脐橙产业综合物流中心", "宜都供销商贸物流园",


        "长阳县城乡冷链物流园", "宜昌港兴山港区管公水多式联运建设项目", "玉湖冷链（襄阳）交易中心项目",


        "武汉万吨华中冷链港二期项目", "湖北长江现代物流产业集聚示范区·现代物流园",


        "鄂州花湖国际机场空港型国家物流枢纽智慧公共国际货站项目", "荆门智慧冷链物流园",


        "荆门北站顺洋物流基地（荆门北子陵铁路物流园）", "沙洋多式联运现代物流园一期（沙洋煤炭储备•集装箱散改集基地）",


        "首衡城智慧冷链物流二期项目G区", "孝昌广浔智慧物流电商产业园", "申通（孝感）智慧物流电商产业园（三期）",


        "公安县多式联运综合物流园", "荆州市铁水联运物流中心", "荆州市瑞海国际物流园一期", "荆州经开区智能仓储物流园项目",


        "鄂东（黄冈）数智物流综合服务中心",


        "鄂湘赣商贸物流中心一期（通城县物流配送中心、鄂南智慧物流产业园建设、鄂南智慧物流产业园建设项目（二期）、冷链配送中心）项目",


        "咸宁国际陆港物流园-咸宁市国际陆港综合物流园（一期）", "咸宁国际陆港物流园-新建咸宁铁路物流基地项目",


        "崇阳县综合物流园及配套设施建设项目", "恩施七里坪物流园", "职业工装产业集聚区仓储物流项目",


        "极兔速递华中物流枢纽基地项目", "湖北迈睿达供应链总部研发及生产基地项目", "东楚航空冷链物流中心",


        "三峡智慧航空物流产业园", "汉江襄阳（小河）港多式联运物流园", "宏茂全球通运项目", "武汉天河综合客运枢纽",


        "广水市杨家寨公铁物流园项目---广水市杨寨铁路货场（广水市华鑫冶金物流货场）", "阳新综合客运枢纽站",


        "当阳市高铁综合客运站", "宜昌北客运站", "南漳县公铁换乘中心(郑万高铁南漳综合客运枢纽)", "京山南公铁综合客运枢纽",


        "沿江高铁汉川北站站前广场(综合客运枢纽)", "武杭高铁黄冈西综合客运枢纽", "黄黄高铁浠水综合客运枢纽",


        "郑万高铁巴东综合客运枢纽", "中通供应链湖北管理中心项目", "阳逻港铁水联运二期中欧班列集结中心",


        "西马国际（黄石）智慧物流园", "十堰市城发仓储物流中心", "枝江市顾家店仓储物流中心（一期）", "三峡建材物流园",


        "宜都市陆城综合物流运输廊道", "宜昌传化公路港二期", "武汉港航襄阳智慧物流园", "谷城县城乡智慧仓储物流产业园",


        "九州通沃田国际供应链中心项目", "湖北恒峰国际物流园", "湖北中青供应链有限公司包装配套、仓储运输项目",


        "大悟县电商物流产业园", "安陆市城乡冷链物流配送中心", "大别山智慧物流产业园", "武穴市供销社集采集配综合服务中心工程",


        "罗田县综合物流园", "黑豹国际物流园（黄冈综合园区）", "麻城市农产品综合物流园", "巴东经济开发区青龙桥智慧物流产业园",


        "鹤峰武陵山仓储物流园", "咸丰县综合物流园", "宣恩县寄递物流园", "来凤县龙凤物流园交通物流中心",


        "仙桃国家高新区物流枢纽中心建设项目", "天门市物流中心建设项目"));




    /** 经济强县项目固定名单 */


    private static final java.util.Set<String> INVEST_COUNTY_NAMES = new LinkedHashSet<>(Arrays.asList(


        "西马国际（黄石）智慧物流园", "黄石大冶湖高新区智慧冷链物流园", "大冶湖高新智慧物流港项目（城西北供应链服务中心）",


        "秭归县脐橙产业综合物流中心", "宜都供销商贸物流园", "长阳县城乡冷链物流园及其配套设施建设项目",


        "宜昌港兴山港区管公水多式联运建设项目", "枝江市顾家店仓储物流中心（一期）", "宜都市陆城综合物流运输廊道",


        "老河口市农产品仓储物流交易中心建设项目", "谷城县城乡智慧仓储物流产业园", "沙洋多式联运现代物流园",


        "大悟县电商物流产业园", "应城市智慧冷链仓储物流园", "孝昌广浔智慧物流电商产业园建设项目", "安陆市城乡冷链物流配送中心",


        "松滋市星络物流园", "公安县多式联运综合物流园", "中国供销公安商贸物流园（公安县域集采集配中心）", "同洲智慧物流园",


        "大别山智慧物流产业园", "武穴市供销社集采集配综合服务中心工程", "罗田县综合物流园", "鄂东（黄冈）数智物流综合服务中心",


        "麻城市农产品综合物流园", "鄂湘赣商贸物流中心（一期）", "嘉鱼县智慧交通物流枢纽中心",


        "崇阳县国家级综合物流枢纽及配套设施建设项目", "康华智慧物流园", "综合物流园（湖北交投随州智慧供应链产业园）",


        "巴东经济开发区青龙桥智慧物流产业园", "恩施市七里坪物流园", "鹤峰武陵山仓储物流园", "咸丰县综合物流园",


        "来凤县龙凤物流园交通物流中心", "宣恩县寄递物流园", "仙桃国家高新区物流枢纽中心建设项目",


        "职业工装产业集聚区仓储物流项目", "中国虾谷冷链仓储物流园", "天门市物流中心建设项目", "神农架物流园"));




    /** "十五五"重点物流项目固定名单 */


    private static final java.util.Set<String> INVEST_FIVE_YEAR_NAMES = new LinkedHashSet<>(Arrays.asList(


        "航发智慧物流园", "中通供应链湖北管理中心项目", "阳逻港铁水联运二期中欧班列集结中心", "极兔速递华中物流枢纽基地项目",


        "中欧班列配套多式联运中转中心项目", "新开发银行贷款长江中游（武汉）智慧物流枢纽项目铁路工业站子项",


        "黄石新港国胜智慧公路港项目", "十堰生产服务型国家物流枢纽工程项目", "十堰市城发仓储物流中心",


        "寿康永乐智能化物流设备升级改造", "十堰汽配（广场）物流中心扩能升级项目", "十堰武当山机场航空物流园项目",


        "丹江口市陈家港铁水公空联运基地", "长阳县城乡冷链物流园及其配套设施建设项目", "三峡建材物流园",


        "宜都市陆城综合物流运输廊道", "三峡智慧航空物流产业园", "当阳市坝陵化工园区综合物流园", "远安县物流中心提能升级项目",


        "宜昌市智慧物流集散中心", "宜都市枝城多式联运临港物流园", "玉湖冷链（襄阳）交易中心项目", "传化鄂西北智慧物流中心项目",


        "武汉港航襄阳智慧物流园", "唐白河港散货转运中心项目", "汉江襄阳（小河）港多式联运物流园", "襄州区大宗商品多式联运交易中心",


        "综合性供应链产业园区（中国有机谷供应链智慧产业园、谷城智慧冷链物流供应链产业园）", "南漳县现代智能物流园区",


        "湖北长江现代物流产业集聚示范区·现代物流园", "弘业现代物流产业园", "宏茂全球通运项目", "沙洋多式联运现代物流园",


        "京山市公铁物流中心", "新材料工业品智慧物流园", "长荆铁路（钟祥）公铁联运集装箱物流园", "屈家岭智慧物流园",


        "东方冷链智慧物流配送中心", "应城市智慧冷链仓储物流园", "孝昌广浔智慧物流电商产业园建设项目",


        "京汉粤大通道孝感国家综合货运枢纽", "公安县多式联运综合物流园", "荆州经开区智能仓储物流园项目",


        "中国供销公安商贸物流园（公安县域集采集配中心）", "监利市港口物流园（城区园）", "鄂东（黄冈）数智物流综合服务中心",


        "崇阳县国家级综合物流枢纽及配套设施建设项目", "咸宁高新区冻库冷链项目（一期）", "咸安区数字物流产业园及基础设施建设项目",


        "通山县特色农产品现代物流基地建设项目", "赤壁沿江公铁联运绿色物流中心项目", "咸宁城乡智慧物流园",


        "综合物流园（湖北交投随州智慧供应链产业园）", "随州综合仓储配送中心",


        "广水市杨家寨公铁物流园项目---第一期广水市杨寨铁路货场（广水市华鑫冶金物流货场）",


        "巴东经济开发区青龙桥智慧物流产业园", "鹤峰武陵山仓储物流园", "咸丰县综合物流园",


        "仙桃国家高新区物流枢纽中心建设项目", "天门市域公共综合物流园", "潜江市农产品现代物流基础设施建设项目",


        "潜江现代农业科技示范园冷链物流中心项目", "湖北潜电物流有限公司现代物流中心（一、二、三期）"));




    /** 汇总大表市州短名 -> 规范名 */


    private static final Map<String, String> INVEST_CITY_SHORT_TO_FULL = new LinkedHashMap<>();




    static {


        INVEST_CITY_SHORT_TO_FULL.put("武汉", "武汉市");


        INVEST_CITY_SHORT_TO_FULL.put("黄石", "黄石市");


        INVEST_CITY_SHORT_TO_FULL.put("十堰", "十堰市");


        INVEST_CITY_SHORT_TO_FULL.put("宜昌", "宜昌市");


        INVEST_CITY_SHORT_TO_FULL.put("襄阳", "襄阳市");


        INVEST_CITY_SHORT_TO_FULL.put("鄂州", "鄂州市");


        INVEST_CITY_SHORT_TO_FULL.put("荆门", "荆门市");


        INVEST_CITY_SHORT_TO_FULL.put("孝感", "孝感市");


        INVEST_CITY_SHORT_TO_FULL.put("荆州", "荆州市");


        INVEST_CITY_SHORT_TO_FULL.put("黄冈", "黄冈市");


        INVEST_CITY_SHORT_TO_FULL.put("咸宁", "咸宁市");


        INVEST_CITY_SHORT_TO_FULL.put("随州", "随州市");


        INVEST_CITY_SHORT_TO_FULL.put("恩施", "恩施州");


        INVEST_CITY_SHORT_TO_FULL.put("仙桃", "仙桃市");


        INVEST_CITY_SHORT_TO_FULL.put("潜江", "潜江市");


        INVEST_CITY_SHORT_TO_FULL.put("天门", "天门市");


        INVEST_CITY_SHORT_TO_FULL.put("林区", "神农架林区");


    }




    /**


     * 投资月报导入（全省汇总大表）。


     * category: 客运站场（xlsx 明细 sheet）/ 物流园区（xls 分项目投资完成情况 sheet）


     * 幂等：同报表期旧月度数据与 INVEST 审核结果先删，项目主档按 项目名+类别 upsert。


     */


    public ImportResult importInvestment(MultipartFile file, String period, String category) throws Exception {


        boolean passenger = "客运站场".equals(category);


        List<InvestRow> rows = new ArrayList<>();


        List<String> cityOrder = new ArrayList<>();


        String currentCity = null;


        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {


            Sheet sheet = pickInvestmentSheet(wb, passenger);


            // 定位表头行：包含"项目名称"且其后有数据


            int headerRow = -1;


            for (int r = 0; r <= Math.min(sheet.getLastRowNum(), 8); r++) {


                Row row = sheet.getRow(r);


                if (row == null) continue;


                String a = getString(row, 0);


                String b = getString(row, 1);


                if (b != null && b.contains("项目名称") && (a == null || a.contains("序号") || a.contains("序"))) {


                    headerRow = r;


                    break;


                }


            }


            if (headerRow < 0) {


                throw new RuntimeException("所选数据类型【" + (passenger ? "投资月报（客运站场）" : "投资月报（物流园区）") + "】与文件内容不符：未找到表头行（需包含 序号/项目名称 列），请确认是否选错了数据类型");


            }


            for (int r = headerRow + 1; r <= sheet.getLastRowNum(); r++) {


                Row row = sheet.getRow(r);


                if (row == null) continue;


                String a = getString(row, 0);


                String b = getString(row, 1);


                if (a == null || a.trim().isEmpty()) {


                    if (b == null || b.trim().isEmpty()) continue;


                }


                String aTrim = a == null ? "" : a.trim();


                String bTrim = b == null ? "" : b.trim();


                // 合计/标题行


                if (aTrim.contains("合计") || aTrim.contains("总计")) continue;


                // 资金类别/分组标题行（一、二、三、四、…）


                if (aTrim.matches("^[一二三四五六七八九十]+、.*")) continue;


                // 市州小节行


                String city = investmentCity(aTrim);


                if (city != null) {


                    currentCity = city;


                    if (!cityOrder.contains(city)) cityOrder.add(city);


                    continue;


                }


                // 项目行：序号为数字且项目名非空


                boolean seqNum = aTrim.matches("\\d+(\\.\\d+)?");


                if (seqNum && !bTrim.isEmpty()) {


                    InvestmentProject project = new InvestmentProject();


                    project.setProjectName(bTrim);


                    project.setCategory(category);


                    project.setCity(currentCity);


                    project.setBuilderName(getString(row, 2));


                    project.setConstructNature(getString(row, 3));


                    project.setStartTime(investTime(getString(row, 4)));


                    project.setEndTime(investTime(getString(row, 5)));


                    project.setTotalInvestment(getDecimal(row, 6));


                    project.setApprovalGk(getString(row, 18));


                    project.setApprovalCs(getString(row, 19));


                    project.setSource("全省汇总大表");


                    project.setIsBillion(INVEST_BILLION_NAMES.contains(bTrim) ? 1 : 0);


                    project.setIsCounty(INVEST_COUNTY_NAMES.contains(bTrim) ? 1 : 0);


                    project.setIsFiveYear(INVEST_FIVE_YEAR_NAMES.contains(bTrim) ? 1 : 0);




                    InvestmentMonthly monthly = new InvestmentMonthly();


                    monthly.setReportPeriod(period);


                    monthly.setYearPlan(getDecimal(row, 8));


                    monthly.setYearCum(getDecimal(row, 9));


                    monthly.setMonthDone(getDecimal(row, 10));


                    monthly.setStartCum(getDecimal(row, 7));


                    monthly.setProgressStage(getString(row, 11));


                    monthly.setProgressDesc(getString(row, 12));


                    monthly.setBuildingArea(getDecimalOrNull(row, 15));


                    rows.add(new InvestRow(r + 1, project, monthly));


                }


            }


        } catch (Exception e) {


            log.error("Investment import parse error: {}", category, e);


            throw e;


        }




        // 幂等清理：同报表期、同类别月度数据 + INVEST 审核结果（项目主档跨月保留，按名 upsert）


        List<InvestmentProject> sameCat = investProjectMapper.selectList(


            new LambdaQueryWrapper<InvestmentProject>().eq(InvestmentProject::getCategory, category));


        java.util.Set<Long> catProjectIds = new HashSet<>();


        for (InvestmentProject pc : sameCat) catProjectIds.add(pc.getId());


        if (!catProjectIds.isEmpty()) {


            investMonthlyMapper.delete(new LambdaQueryWrapper<InvestmentMonthly>()


                .eq(InvestmentMonthly::getReportPeriod, period)


                .in(InvestmentMonthly::getProjectId, catProjectIds));


        }


        List<Long> investRuleIds = new ArrayList<>();


        for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {


            if ("INVEST".equals(rule.getReportType())) investRuleIds.add(rule.getId());


        }


        if (!investRuleIds.isEmpty()) {


            auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()


                .eq(AuditResult::getReportPeriod, period)


                .in(AuditResult::getRuleId, investRuleIds));


        }




        int success = 0;


        List<String> failDetails = new ArrayList<>();


        for (InvestRow item : rows) {


            try {


                // 主档 upsert（按 项目名+类别）


                InvestmentProject exist = investProjectMapper.selectOne(new LambdaQueryWrapper<InvestmentProject>()


                    .eq(InvestmentProject::getProjectName, item.project.getProjectName())


                    .eq(InvestmentProject::getCategory, category)


                    .last("LIMIT 1"));


                if (exist == null) {


                    investProjectMapper.insert(item.project);


                    item.monthly.setProjectId(item.project.getId());


                } else {


                    item.project.setId(exist.getId());


                    item.project.setCreatedAt(exist.getCreatedAt());


                    investProjectMapper.updateById(item.project);


                    item.monthly.setProjectId(exist.getId());


                }


                investMonthlyMapper.insert(item.monthly);


                success++;


            } catch (Exception e) {


                Throwable cause = e;


                while (cause.getCause() != null) cause = cause.getCause();


                String reason = cause.getMessage();


                if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();


                failDetails.add("第 " + item.excelRow + " 行（" + item.project.getProjectName() + "）：" + reason);


                log.error("Investment insert error: row {}", item.excelRow, e);


            }


        }


        String errorDetail = String.join("\n", failDetails);


        if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";


        recordBatch(file.getOriginalFilename(), "INVEST_" + category, period, rows.size(), success, failDetails.size(), errorDetail);


        log.info("Investment {} imported: {} rows for {}", category, success, period);


        return new ImportResult(success, failDetails.size(), failDetails);


    }




    // ========== 投资模块 M2：市州单表批量导入（容错解析） ==========




    /**


     * 市州单表批量导入：扫描 docs/投资/输入/{类别目录}/{X月} 下全部 xls/xlsx/et（跳过 pdf），


     * 容错解析每个文件（定位表头行 + 列名映射），upsert 项目主档 + 写入月度，


     * 并与「十五五」模板清单规范化匹配后标记 is_five_year。


     */


    public Map<String, Object> importInvestCityDir(String period, String category) throws Exception {


        boolean passenger = "客运站场".equals(category);


        String catDir = passenger ? "客运（客运站）" : "物流（物流园区）";


        String monthDir = investMonthDir(period);


        if (monthDir.isEmpty()) throw new RuntimeException("报表期格式应为 yyyy-MM，例如 2026-07");


        File dir = resolveInvestInputDir(catDir, monthDir);


        if (dir == null || !dir.isDirectory()) {


            throw new RuntimeException("未找到市州单表目录：docs/投资/输入/" + catDir + "/" + monthDir);


        }


        Set<String> fiveYearNames = loadFiveYearProjectNames();


        List<InvestCityRow> rows = new ArrayList<>();


        List<Map<String, Object>> fileResults = new ArrayList<>();


        File[] files = dir.listFiles((d, n) -> {


            String lower = n.toLowerCase();


            return lower.endsWith(".xls") || lower.endsWith(".xlsx") || lower.endsWith(".et");


        });


        if (files == null || files.length == 0) {


            throw new RuntimeException("目录中没有可解析的报表文件（xls/xlsx/et）：" + dir.getAbsolutePath());


        }


        Arrays.sort(files, Comparator.comparing(File::getName));


        for (File f : files) {


            Map<String, Object> fr = new LinkedHashMap<>();


            fr.put("fileName", f.getName());


            try (java.io.InputStream is = new java.io.FileInputStream(f);


                 Workbook wb = WorkbookFactory.create(is)) {


                parseInvestCityWorkbook(wb, f.getName(), category, period, fiveYearNames, rows, fr);


            } catch (Exception e) {


                fr.put("success", 0);


                fr.put("error", e.getMessage() == null ? e.toString() : e.getMessage());


                log.error("invest city file parse error: {}", f.getName(), e);


            }


            fileResults.add(fr);


        }


        // 幂等清理：按本次文件涉及市州清理月度数据 + INVEST 审核结果（项目主档跨月保留，按名 upsert）


        cleanInvestMonthlyByCity(category, period, rows);


        Map<String, Set<String>> knownCities = loadInvestKnownCities(category);


        Map<String, String> fileFirstMismatch = new LinkedHashMap<>();


        int success = 0;


        List<String> failDetails = new ArrayList<>();


        for (InvestCityRow item : rows) {


            String mismatch = investCityMismatchReason(item, knownCities);


            if (mismatch != null) {


                failDetails.add(mismatch);


                fileFirstMismatch.putIfAbsent(item.fileName, mismatch);


                continue;


            }


            try {


                saveInvestProjectRow(item.project, item.monthly);


                success++;


            } catch (Exception e) {


                Throwable cause = e;


                while (cause.getCause() != null) cause = cause.getCause();


                String reason = cause.getMessage();


                if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();


                failDetails.add(item.fileName + " 第 " + item.excelRow + " 行（" + item.project.getProjectName() + "）：" + reason);


                log.error("Invest city insert error: file {}, row {}", item.fileName, item.excelRow, e);


            }


        }


        // 把首个市州错位提示挂到对应文件结果上，便于页面弹窗直接看到


        for (Map<String, Object> fr2 : fileResults) {


            String msg = fileFirstMismatch.get(fr2.get("fileName"));


            if (msg != null && fr2.get("error") == null) fr2.put("error", msg);


        }


        String errorDetail = String.join("\n", failDetails);


        if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";


        recordBatch("市州单表批量导入（" + catDir + "/" + monthDir + "）", "INVEST_" + category + "_CITY",


            period, rows.size(), success, failDetails.size(), errorDetail);


        int fiveYearMatched = 0;


        for (InvestCityRow item : rows) {


            if (Integer.valueOf(1).equals(item.project.getIsFiveYear())) fiveYearMatched++;


        }


        Map<String, Object> result = new LinkedHashMap<>();


        result.put("total", rows.size());


        result.put("success", success);


        result.put("fail", failDetails.size());


        result.put("failDetails", failDetails);


        result.put("files", fileResults);


        result.put("fiveYearTemplateProjects", fiveYearNames.size());


        result.put("fiveYearMatched", fiveYearMatched);


        log.info("Invest city {} {} imported: {} rows (fiveYear matched {}/{})",


            category, period, success, fiveYearMatched, fiveYearNames.size());


        return result;


    }




    /**


     * 多文件批量导入：前端一次选择多个 Excel 上传。


     * 投资类型（investment/investmentLogistics）按市州单表解析（文件名/填报单位识别市州，十五五自动标记）；


     * 其他类型逐个走现有解析逻辑（importDirFile）。


     */


    public Map<String, Object> importBatchFiles(String type, String period, List<MultipartFile> files) throws Exception {


        if (files == null || files.isEmpty()) {


            throw new RuntimeException("未接收到任何文件");


        }


        if ("investment".equals(type) || "investmentLogistics".equals(type)) {


            String category = "investment".equals(type) ? "客运站场" : "物流园区";


            return importInvestCityFiles(period, category, files);


        }


        List<Map<String, Object>> fileResults = new ArrayList<>();


        int totalSuccess = 0, totalFail = 0;


        for (MultipartFile f : files) {


            Map<String, Object> fr = new LinkedHashMap<>();


            fr.put("fileName", f.getOriginalFilename());


            try {


                ImportResult r = importDirFile(type, f, period);


                fr.put("success", r.getSuccess());


                fr.put("fail", r.getFail());


                if (r.getFail() > 0 && r.getFailDetails() != null && !r.getFailDetails().isEmpty()) {


                    fr.put("error", r.getFailDetails().get(0));


                }


                totalSuccess += r.getSuccess();


                totalFail += r.getFail();


            } catch (Exception e) {


                fr.put("success", 0);


                fr.put("fail", 0);


                fr.put("error", e.getMessage() == null ? e.toString() : e.getMessage());


                totalFail++;


            }


            fileResults.add(fr);


        }


        Map<String, Object> result = new LinkedHashMap<>();


        result.put("type", type);


        result.put("total", files.size());


        result.put("success", totalSuccess);


        result.put("fail", totalFail);


        result.put("files", fileResults);


        return result;


    }




    /** 投资多文件批量：逐文件按市州单表解析，统一幂等清理落库，批次汇总 */


    public Map<String, Object> importInvestCityFiles(String period, String category, List<MultipartFile> files) throws Exception {


        boolean passenger = "客运站场".equals(category);


        Set<String> fiveYearNames = loadFiveYearProjectNames();


        List<InvestCityRow> rows = new ArrayList<>();


        List<Map<String, Object>> fileResults = new ArrayList<>();


        for (MultipartFile f : files) {


            Map<String, Object> fr = new LinkedHashMap<>();


            fr.put("fileName", f.getOriginalFilename());


            try (Workbook wb = WorkbookFactory.create(f.getInputStream())) {


                parseInvestCityWorkbook(wb, f.getOriginalFilename(), category, period, fiveYearNames, rows, fr);


            } catch (Exception e) {


                fr.put("success", 0);


                fr.put("error", e.getMessage() == null ? e.toString() : e.getMessage());


                log.error("invest city file parse error: {}", f.getOriginalFilename(), e);


            }


            fileResults.add(fr);


        }


        String catDir = passenger ? "客运（客运站）" : "物流（物流园区）";


        cleanInvestMonthlyByCity(category, period, rows);


        Map<String, Set<String>> knownCities = loadInvestKnownCities(category);


        Map<String, String> fileFirstMismatch = new LinkedHashMap<>();


        int success = 0;


        List<String> failDetails = new ArrayList<>();


        for (InvestCityRow item : rows) {


            String mismatch = investCityMismatchReason(item, knownCities);


            if (mismatch != null) {


                failDetails.add(mismatch);


                fileFirstMismatch.putIfAbsent(item.fileName, mismatch);


                continue;


            }


            try {


                saveInvestProjectRow(item.project, item.monthly);


                success++;


            } catch (Exception e) {


                Throwable cause = e;


                while (cause.getCause() != null) cause = cause.getCause();


                String reason = cause.getMessage();


                if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();


                failDetails.add(item.fileName + " 第 " + item.excelRow + " 行（" + item.project.getProjectName() + "）：" + reason);


                log.error("Invest city insert error: file {}, row {}", item.fileName, item.excelRow, e);


            }


        }


        // 把首个市州错位提示挂到对应文件结果上，便于页面弹窗直接看到


        for (Map<String, Object> fr2 : fileResults) {


            String msg = fileFirstMismatch.get(fr2.get("fileName"));


            if (msg != null && fr2.get("error") == null) fr2.put("error", msg);


        }


        String errorDetail = String.join("\n", failDetails);


        if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";


        recordBatch("多文件批量导入（" + catDir + "/" + period + "）", "INVEST_" + category + "_CITY",


            period, rows.size(), success, failDetails.size(), errorDetail);


        int fiveYearMatched = 0;


        for (InvestCityRow item : rows) {


            if (Integer.valueOf(1).equals(item.project.getIsFiveYear())) fiveYearMatched++;


        }


        Map<String, Object> result = new LinkedHashMap<>();


        result.put("total", rows.size());


        result.put("success", success);


        result.put("fail", failDetails.size());


        result.put("failDetails", failDetails);


        result.put("files", fileResults);


        result.put("fiveYearTemplateProjects", fiveYearNames.size());


        result.put("fiveYearMatched", fiveYearMatched);


        log.info("Invest city multi-file {} {} imported: {} rows (fiveYear matched {}/{})",


            category, period, success, fiveYearMatched, fiveYearNames.size());


        return result;


    }




    /** 多 sheet 历史文件（襄阳 .et 等）：sheet 名含报表期优先，其次选数据行最多 */


    private boolean investSheetMatchesPeriod(Sheet sh, String period) {


        if (period == null || !period.matches("\\d{4}-\\d{2}")) return false;


        String name = sh.getSheetName();


        if (name == null || name.isEmpty()) return false;


        String norm = name.replace(" ", "");


        int month = Integer.parseInt(period.substring(5, 7));


        String normDash = norm.replace('.', '-'); // "2026.8"/"2026.08" -> "2026-8"/"2026-08"


        return norm.contains(month + "月") || norm.contains(String.format("%02d", month) + "月")


            || name.contains(period) || name.contains(period.replace("-0", "-"))


            || normDash.contains(period) || normDash.contains(period.replace("-0", "-"));


    }




    /** 解析单个市州报表：选 sheet（报表期名优先 + 数据行数），按列名映射提取项目行 */


    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 (cnt > 0 && investSheetMatchesPeriod(sh, period)) {


                // 当期真实报表 sheet 名通常带年份（如 2026.8/2026年8月），历史sheet多为纯月份名（8月）：带年份的优先


                String norm = sh.getSheetName() == null ? "" : sh.getSheetName().replace(" ", "");


                if (norm.contains(period.substring(0, 4))) score += 100000;


            }


            if (score > bestScore) {


                best = sh;


                bestCol = col;


                bestScore = score;


            }


        }


        if (best == null) {


            fr.put("success", 0);


            fr.put("error", "未找到含「项目名称」的表头");


            return;


        }


        String city = cityFromFileName(fileName);


        if (city == null) city = cityFromSheetHeader(best);


        fr.put("city", city == null ? "" : city);


        String currentCity = city;


        Map<String, Integer> col = bestCol;


        int nameCol = col.get("name");


        boolean hasSeq = col.containsKey("seq");


        int seqCol = hasSeq ? col.get("seq") : 0;


        int parsed = 0;


        for (int r = col.get("headerRow") + 1; r <= best.getLastRowNum(); r++) {


            Row row = best.getRow(r);


            if (row == null) continue;


            String name = cellStr(row, nameCol);


            String seq = hasSeq ? cellStr(row, seqCol) : null;


            // 市州小节行：部分汇总表把市州名写在序号列（如十堰汇总表 A 列写"武汉"），需同样识别


            if (seq != null && !seq.trim().isEmpty()) {


                String seqCity = investmentCity(seq.trim());


                if (seqCity != null) {


                    currentCity = seqCity;


                    continue;


                }


            }


            if (name == null || name.trim().isEmpty()) continue;


            String nt = name.trim().replaceAll("[\r\n]+", "");


            String ntFlat = nt.replaceAll("\\s+", "");


            if (ntFlat.contains("合计") || ntFlat.contains("总计") || ntFlat.contains("小计")) continue;


            if (nt.matches("^[一二三四五六七八九十]+、.*")) continue;


            if (nt.matches("^（[一二三四五六七八九十]+）.*")) continue;


            if (nt.startsWith("填报") || nt.startsWith("单位负责人") || nt.matches("^\\d+、.*")) continue;


            if (hasSeq) {


                if (seq == null || seq.trim().isEmpty()) {


                    // 市州小节行（如 "武汉" "天 门"），更新当前市州


                    String c = investmentCity(nt);


                    if (c != null) { currentCity = c; continue; }


                    // 序号为空的真实项目行（部分市州月报不填序号，如神农架物流园）也按项目处理


                    if (!isLikelyInvestProjectName(nt)) continue;


                } else if (!seq.trim().matches("\\d+(\\.\\d+)?")) {


                    continue;


                }


            } else {


                // 无序号列（随州等）：项目名非空即项目行，市州小节行用 investmentCity 识别


                String c = investmentCity(nt);


                if (c != null) { currentCity = c; continue; }


            }


            InvestmentProject project = new InvestmentProject();


            project.setProjectName(nt);


            project.setCategory(category);


            project.setCity(currentCity);


            String countyCell = cellStr(row, col.get("county"));


            if (countyCell != null && !countyCell.trim().isEmpty()) project.setCounty(countyCell.trim());


            project.setBuilderName(cellStr(row, col.get("builder")));


            project.setConstructNature(cellStr(row, col.get("nature")));


            project.setStartTime(investTime(cellStr(row, col.get("start"))));


            project.setEndTime(investTime(cellStr(row, col.get("end"))));


            project.setTotalInvestment(cellNum(row, col.get("total")));


            project.setApprovalGk(cellStr(row, col.get("gk")));


            project.setApprovalCs(cellStr(row, col.get("cs")));


            project.setBuildingArea(cellNumOrNull(row, col.get("area")));


            project.setSource("市州单表");


            project.setIsBillion(INVEST_BILLION_NAMES.contains(nt) ? 1 : 0);


            project.setIsCounty(INVEST_COUNTY_NAMES.contains(nt) ? 1 : 0);


            project.setIsFiveYear(fiveYearNames.contains(normalizeProjectName(nt)) ? 1 : 0);


            InvestmentMonthly monthly = new InvestmentMonthly();


            monthly.setReportPeriod(period);


            monthly.setStartCum(cellNum(row, col.get("startcum")));


            monthly.setYearPlan(cellNum(row, col.get("yearplan")));


            monthly.setYearCum(cellNum(row, col.get("yearcum")));


            monthly.setMonthDone(cellNum(row, col.get("monthdone")));


            monthly.setProgressStage(cellStr(row, col.get("stage")));


            monthly.setProgressDesc(cellStr(row, col.get("desc")));


            monthly.setBuildingArea(cellNumOrNull(row, col.get("area")));


            rows.add(new InvestCityRow(r + 1, fileName, project, monthly));


            parsed++;


        }


        fr.put("city", currentCity);


        fr.put("success", parsed);


    }




    /** 定位含「项目名称」的表头行并按列名关键词建立列映射（支持双层表头合并） */


    private Map<String, Integer> findInvestHeader(Sheet sh) {


        int maxHeaderRow = Math.min(22, sh.getLastRowNum());


        for (int r = 0; r <= maxHeaderRow; r++) {


            Row row = sh.getRow(r);


            if (row == null) continue;


            boolean hasName = false;


            int headerCells = 0;


            for (int c = 0; c <= row.getLastCellNum(); c++) {


                String v = cellStr(row, c);


                if (v == null) continue;


                String t = v.trim();


                if (t.isEmpty() || t.length() > 20) continue;   // 说明性长句不算表头字段


                String k = investHeaderKey(t);


                if (k == null) continue;


                headerCells++;


                if ("name".equals(k)) hasName = true;


            }


            // 真表头行至少有 2 个短表头字段；只有一格长句含「项目名称」的说明页会被排除


            if (!hasName || headerCells < 2) continue;


            Map<Integer, String> colTexts = new HashMap<>();


            int scanEnd = investHeaderScanEnd(sh, r, row);


            for (int rr = r; rr <= scanEnd; rr++) {


                Row rr2 = sh.getRow(rr);


                if (rr2 == null) continue;


                for (int c = 0; c <= rr2.getLastCellNum(); c++) {


                    String v = cellStr(rr2, c);


                    if (v == null) continue;


                    colTexts.merge(c, v, (a, b) -> a + "|" + b);


                }


            }


            Map<String, Integer> col = new HashMap<>();


            for (Map.Entry<Integer, String> e : colTexts.entrySet()) {


                String k = investHeaderKey(e.getValue());


                if (k != null) col.putIfAbsent(k, e.getKey());


            }


            if (!col.containsKey("name")) continue;


            for (int c = 0; c <= row.getLastCellNum(); c++) {


                String v = cellStr(row, c);


                if (v != null && v.trim().matches("序\\s*号")) { col.put("seq", c); break; }


            }


            // fallback：yearcum 缺失时按 startcum+2（总投资/自开始累计/本年计划/自年初累计 的标准列序）


            if (!col.containsKey("yearcum") && col.containsKey("startcum")) {


                int yc = col.get("startcum") + 2;


                if (yc <= row.getLastCellNum()) col.put("yearcum", yc);


            }


            // monthdone 缺失时按 yearcum+1


            if (!col.containsKey("monthdone") && col.containsKey("yearcum")) {


                int md = col.get("yearcum") + 1;


                if (md <= row.getLastCellNum()) col.put("monthdone", md);


            }


            // desc 缺失时按 stage+1 兜底：省标「项目建设阶段、进度情况」是 L/M 合并表头，


            // 孝感/恩施/林区/随州等市州只在 L 写「建设阶段」、M 直接写进度文字而不写标题


            if (!col.containsKey("desc") && col.containsKey("stage") && col.get("stage") >= 0) {


                int dc = col.get("stage") + 1;


                if (dc <= row.getLastCellNum()) col.put("desc", dc);


            }


            for (String k : new String[]{"builder", "nature", "start", "end", "total", "startcum",


                "yearplan", "yearcum", "monthdone", "stage", "desc", "gk", "cs", "area", "county"}) {


                col.putIfAbsent(k, -1);


            }


            col.put("headerRow", r);


            return col;


        }


        return null;


    }




    /** 单个表头单元格 -> 字段键（null 表示不是表头字段）；供表头行判定与列映射共用 */


    private String investHeaderKey(String v) {


        if (v == null) return null;


        String flat = v.replace(" ", "");


        if (v.contains("项目名称")) return "name";


        // 「县（市、区）」的各种写法：半/全角括号、顿号/逗号、有无括号，统一归一化后再判定


        String flatCounty = flat.replace("（", "(").replace("）", ")").replace("、", "").replace("，", "").replace(",", "");


        if (flatCounty.contains("县(市区)") || flatCounty.contains("县市区") || flatCounty.contains("区县")) return "county";


        if (v.contains("投资建设") || v.contains("建设单位") || v.contains("项目业主")) return "builder";


        if (v.contains("建设性质")) return "nature";


        if (v.contains("开工")) return "start";


        if (v.contains("竣工") || v.contains("建成")) return "end";


        if ((v.contains("计划总投资") || v.contains("总投资")) && !v.contains("累计")) return "total";


        if (v.contains("自开始建设")) return "startcum";


        if (v.contains("本年计划")) return "yearplan";


        if (v.contains("自年初累计")) return "yearcum";


        if (v.contains("本月完成")) return "monthdone";


        if (v.contains("建设阶段")) return "stage";


        if (v.contains("形象进度") || v.contains("建设进展")) return "desc";


        if (v.contains("工可") && !v.contains("项目名称")) return "gk";


        if (v.contains("初设") && !v.contains("项目名称")) return "cs";


        if (v.contains("建筑面积") && !v.contains("新增")) return "area";


        return null;


    }




    /** 表头文本扫描下界：默认表头行 +3；若「项目名称」列首个数据行更靠下，则扩展到数据行前一行（上限 +12）。


     *  背景：咸宁/十堰等市州把「建设阶段/形象进度」标签写在表头下第 4~8 行，固定 +3 会漏列（形象进度读不到）。 */


    private int investHeaderScanEnd(Sheet sh, int headerRow, Row headerRowObj) {


        int hardEnd = Math.min(headerRow + 12, sh.getLastRowNum());


        int nameCol = -1;


        if (headerRowObj != null) {


            for (int c = 0; c <= headerRowObj.getLastCellNum(); c++) {


                String v = cellStr(headerRowObj, c);


                if (v != null && v.contains("项目名称")) { nameCol = c; break; }


            }


        }


        int firstData = findFirstInvestDataRow(sh, headerRow, nameCol);


        int scanEnd = (firstData > headerRow)


            ? Math.min(hardEnd, firstData - 1)


            : Math.min(headerRow + 3, sh.getLastRowNum());


        if (scanEnd < headerRow) scanEnd = headerRow;


        return scanEnd;


    }




    /** 「项目名称」列首个数据行下标（跳过表头续行与空行）；找不到返回 -1 */


    private int findFirstInvestDataRow(Sheet sh, int headerRow, int nameCol) {


        if (nameCol < 0) return -1;


        for (int r = headerRow + 1; r <= sh.getLastRowNum(); r++) {


            Row row = sh.getRow(r);


            if (row == null) continue;


            String name = cellStr(row, nameCol);


            if (name == null || name.trim().isEmpty()) continue;


            String nt = name.trim().replaceAll("[\r\n]+", "");


            if (nt.contains("项目名称")) continue;


            String flat = nt.replaceAll("\\s+", "");


            if (flat.contains("合计") || flat.contains("总计") || flat.contains("小计")) continue;


            if (nt.matches("^[一二三四五六七八九十]+、.*")) continue;


            if (nt.matches("^（[一二三四五六七八九十]+）.*")) continue;


            if (nt.startsWith("填报") || nt.startsWith("单位负责人") || nt.matches("^\\d+、.*")) continue;


            if (investmentCity(nt) != null) continue;


            return r;


        }


        return -1;


    }




    /** 统计 sheet 内项目数据行数（用于多 sheet 历史文件选当月 sheet） */


    private int countInvestRows(Sheet sh, Map<String, Integer> col) {


        int nameCol = col.get("name");


        boolean hasSeq = col.containsKey("seq");


        int seqCol = hasSeq ? col.get("seq") : 0;


        int count = 0;


        for (int r = col.get("headerRow") + 1; r <= sh.getLastRowNum(); r++) {


            Row row = sh.getRow(r);


            if (row == null) continue;


            String name = cellStr(row, nameCol);


            if (name == null || name.trim().isEmpty()) continue;


            String nt = name.trim().replaceAll("[\r\n]+", "");


            String ntFlat = nt.replaceAll("\\s+", "");


            if (ntFlat.contains("合计") || ntFlat.contains("总计") || ntFlat.contains("小计")) continue;


            if (nt.matches("^[一二三四五六七八九十]+、.*")) continue;


            if (nt.startsWith("填报") || nt.startsWith("单位负责人") || nt.matches("^\\d+、.*")) continue;


            if (!hasSeq) {


                if (nt.length() > 4) count++;


            } else {


                String seq = cellStr(row, seqCol);


                if (seq != null && seq.trim().matches("\\d+(\\.\\d+)?")) {


                    count++;


                } else if ((seq == null || seq.trim().isEmpty()) && isLikelyInvestProjectName(nt)) {


                    count++;


                }


            }


        }


        return count;


    }




    /** 从文件名提取市州（如 "（孝感市道运中心）…" -> 孝感市） */


    private String cityFromFileName(String fileName) {


        for (Map.Entry<String, String> e : INVEST_CITY_SHORT_TO_FULL.entrySet()) {


            if (fileName.contains(e.getKey())) return e.getValue();


        }


        return null;


    }




    /** 从 sheet 头部「填报单位：XX」行提取市州 */


    private String cityFromSheetHeader(Sheet sh) {


        for (int r = 0; r < Math.min(6, sh.getLastRowNum() + 1); r++) {


            Row row = sh.getRow(r);


            if (row == null) continue;


            for (int c = 0; c <= row.getLastCellNum(); c++) {


                String v = cellStr(row, c);


                if (v == null || !v.contains("填报单位")) continue;


                java.util.regex.Matcher m = java.util.regex.Pattern


                    .compile("填报单位[：:（(]?(盖章|签章)?[）)]?[：:]*([^\\s，,；;]{2,14})")


                    .matcher(v);


                if (m.find()) {


                    String raw = m.group(2);


                    for (Map.Entry<String, String> e : INVEST_CITY_SHORT_TO_FULL.entrySet()) {


                        if (raw.contains(e.getKey())) return e.getValue();


                    }


                    String city = investmentCity(raw);


                    if (city != null) return city;


                }


            }


        }


        return null;


    }




    /** 解析「十五五」规划物流项目模板清单，返回规范化项目名集合 */


    private Set<String> loadFiveYearProjectNames() {


        Set<String> names = new LinkedHashSet<>();


        File template = resolveInvestTemplate("模板_十五五规划物流项目进展情况.xls");


        if (template == null || !template.exists()) {


            log.warn("十五五模板不存在，is_five_year 标记跳过: {}", template);


            return names;


        }


        try (java.io.InputStream is = new java.io.FileInputStream(template);


             Workbook wb = WorkbookFactory.create(is)) {


            Sheet sh = null;


            for (int i = 0; i < wb.getNumberOfSheets(); i++) {


                if (wb.getSheetName(i).contains("分项目投资")) { sh = wb.getSheetAt(i); break; }


            }


            if (sh == null) sh = wb.getSheetAt(0);


            Map<String, Integer> col = findInvestHeader(sh);


            if (col == null) return names;


            int nameCol = col.get("name");


            int seqCol = col.containsKey("seq") ? col.get("seq") : 0;


            for (int r = col.get("headerRow") + 1; r <= sh.getLastRowNum(); r++) {


                Row row = sh.getRow(r);


                if (row == null) continue;


                String seq = cellStr(row, seqCol);


                String name = cellStr(row, nameCol);


                if (seq == null || name == null) continue;


                if (!seq.trim().matches("\\d+")) continue;


                names.add(normalizeProjectName(name.trim()));


            }


        } catch (Exception e) {


            log.error("load five year project names error", e);


        }


        return names;


    }




    /** 序号为空的非市州行是否像项目行：长度>4 且非备注/说明等杂项 */


    private boolean isLikelyInvestProjectName(String nt) {


        if (nt == null || nt.length() <= 4) return false;


        if (investmentCity(nt) != null) return false;


        return !nt.startsWith("备注") && !nt.startsWith("说明") && !nt.startsWith("注：")


            && !nt.startsWith("填表") && !nt.startsWith("审核") && !nt.startsWith("统计")


            && !nt.startsWith("负责");


    }




    /** 项目名规范化：去空白/换行/括号内容/标点/「项目」后缀，用于十五五清单匹配 */


    private String normalizeProjectName(String name) {


        String s = name.replaceAll("\\s+", "").replace("\n", "");


        s = s.replaceAll("[（(【\\[][^）)】\\]]*[）)】\\]]", "");


        s = s.replaceAll("[，,。.、；;：:—\\-－_/\\\\|]", "");


        s = s.replace("物流项目", "物流").replace("项目", "");


        return s;


    }




    /** 报表期 "2026-07" -> "7月" */


    private String investMonthDir(String period) {


        if (period == null || !period.matches("\\d{4}-\\d{2}")) return "";


        int month = Integer.parseInt(period.substring(5, 7));


        return month + "月";


    }




    /** 投资输入目录定位：user.dir -> user.dir/.. 逐级回退（spring-boot:run 的 user.dir 是 traffic-audit-server） */


    private File resolveInvestInputDir(String catDir, String monthDir) {


        String rel = "docs/投资/输入/" + catDir + "/" + monthDir;


        for (String baseDir : new String[]{System.getProperty("user.dir"), System.getProperty("user.dir") + "/.."}) {


            File f = new File(baseDir, rel);


            if (f.isDirectory()) return f;


        }


        return null;


    }




    private File resolveInvestTemplate(String fileName) {


        String rel = "docs/投资/模板/" + fileName;


        for (String baseDir : new String[]{System.getProperty("user.dir"), System.getProperty("user.dir") + "/.."}) {


            File f = new File(baseDir, rel);


            if (f.isFile()) return f;


        }


        return null;


    }




    /** 同类别同报表期月度数据 + INVEST 审核结果清理（幂等，全省汇总大表全量清理） */


    private void cleanInvestMonthly(String category, String period) {


        List<InvestmentProject> sameCat = investProjectMapper.selectList(


            new LambdaQueryWrapper<InvestmentProject>().eq(InvestmentProject::getCategory, category));


        java.util.Set<Long> catProjectIds = new HashSet<>();


        for (InvestmentProject pc : sameCat) catProjectIds.add(pc.getId());


        if (!catProjectIds.isEmpty()) {


            investMonthlyMapper.delete(new LambdaQueryWrapper<InvestmentMonthly>()


                .eq(InvestmentMonthly::getReportPeriod, period)


                .in(InvestmentMonthly::getProjectId, catProjectIds));


        }


        deleteInvestAuditResults(period, null);


    }




    /** 按市州清理：只删除本次文件涉及市州（含未识别市州的 null 项目）的月度数据 + INVEST 审核结果 */


    private void cleanInvestMonthlyByCity(String category, String period, List<InvestCityRow> rows) {


        Set<String> citySet = new HashSet<>();


        boolean hasNullCity = false;


        for (InvestCityRow item : rows) {


            String c = item.project.getCity();


            if (c == null || c.trim().isEmpty()) hasNullCity = true;


            else citySet.add(c.trim());


        }


        if (citySet.isEmpty() && !hasNullCity) return;


        boolean cleanNullCity = hasNullCity;


        LambdaQueryWrapper<InvestmentProject> pw = new LambdaQueryWrapper<InvestmentProject>()


            .eq(InvestmentProject::getCategory, category);


        pw.and(w -> {


            if (!citySet.isEmpty()) w.in(InvestmentProject::getCity, citySet);


            if (cleanNullCity) {


                if (!citySet.isEmpty()) w.or();


                w.isNull(InvestmentProject::getCity);


            }


        });


        List<InvestmentProject> cityProjects = investProjectMapper.selectList(pw);


        java.util.Set<Long> catProjectIds = new HashSet<>();


        for (InvestmentProject pc : cityProjects) catProjectIds.add(pc.getId());


        if (catProjectIds.isEmpty()) return;


        List<InvestmentMonthly> oldMonthlies = investMonthlyMapper.selectList(


            new LambdaQueryWrapper<InvestmentMonthly>()


                .eq(InvestmentMonthly::getReportPeriod, period)


                .in(InvestmentMonthly::getProjectId, catProjectIds));


        java.util.Set<Long> oldMonthlyIds = new HashSet<>();


        for (InvestmentMonthly m : oldMonthlies) oldMonthlyIds.add(m.getId());


        investMonthlyMapper.delete(new LambdaQueryWrapper<InvestmentMonthly>()


            .eq(InvestmentMonthly::getReportPeriod, period)


            .in(InvestmentMonthly::getProjectId, catProjectIds));


        deleteInvestAuditResults(period, oldMonthlyIds);


    }




    /** 清理 INVEST 审核结果（monthlyIds 为 null 表示该期间全量清理） */


    private void deleteInvestAuditResults(String period, java.util.Set<Long> monthlyIds) {


        List<Long> investRuleIds = new ArrayList<>();


        for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {


            if ("INVEST".equals(rule.getReportType())) investRuleIds.add(rule.getId());


        }


        if (investRuleIds.isEmpty()) return;


        LambdaQueryWrapper<AuditResult> w = new LambdaQueryWrapper<AuditResult>()


            .eq(AuditResult::getReportPeriod, period)


            .in(AuditResult::getRuleId, investRuleIds);


        if (monthlyIds != null) {


            if (monthlyIds.isEmpty()) return;


            w.in(AuditResult::getReportId, monthlyIds);


        }


        auditResultMapper.delete(w);


    }




    /** 项目主档 upsert（按 项目名+类别）+ 月度写入 */


    private void saveInvestProjectRow(InvestmentProject project, InvestmentMonthly monthly) {


        InvestmentProject exist = investProjectMapper.selectOne(new LambdaQueryWrapper<InvestmentProject>()


            .eq(InvestmentProject::getProjectName, project.getProjectName())


            .eq(InvestmentProject::getCategory, project.getCategory())


            .last("LIMIT 1"));


        if (exist == null) {


            investProjectMapper.insert(project);


            monthly.setProjectId(project.getId());


        } else {


            project.setId(exist.getId());


            project.setCreatedAt(exist.getCreatedAt());


            investProjectMapper.updateById(project);


            monthly.setProjectId(exist.getId());


        }


        investMonthlyMapper.insert(monthly);


    }




    /** 载入某类别「规范化项目名 -> 已入库市州集合」，用于市州一致性校验（名称在多个市州重复时放弃校验） */


    private Map<String, Set<String>> loadInvestKnownCities(String category) {


        Map<String, Set<String>> map = new HashMap<>();


        List<InvestmentProject> list = investProjectMapper.selectList(


            new LambdaQueryWrapper<InvestmentProject>().eq(InvestmentProject::getCategory, category));


        for (InvestmentProject p : list) {


            if (p.getCity() == null || p.getCity().trim().isEmpty()) continue;


            map.computeIfAbsent(normalizeProjectName(p.getProjectName()), k -> new HashSet<>())


               .add(p.getCity().trim());


        }


        return map;


    }




    /** 市州一致性校验：库中唯一归属市州与本次解析不一致时返回错误说明，否则返回 null */


    private String investCityMismatchReason(InvestCityRow item, Map<String, Set<String>> knownCities) {


        String parsed = item.project.getCity();


        if (parsed == null || parsed.trim().isEmpty()) return null;


        Set<String> known = knownCities.get(normalizeProjectName(item.project.getProjectName()));


        if (known == null || known.size() != 1) return null;


        String knownCity = known.iterator().next();


        if (knownCity.equals(parsed.trim())) return null;


        return item.fileName + " 第 " + item.excelRow + " 行（" + item.project.getProjectName()


            + "）：市州一致性校验未通过——库中该项目归属「" + knownCity + "」，本次文件解析为「" + parsed.trim()


            + "」，疑似市州错位，已跳过导入，请核对源文件";


    }




    private String cellStr(Row row, int idx) {


        if (idx < 0) return null;


        return getString(row, idx);


    }




    private Double cellNum(Row row, int idx) {


        if (idx < 0) return 0.0;


        return getDecimal(row, idx);


    }




    private Double cellNumOrNull(Row row, int idx) {


        if (idx < 0) return null;


        return getDecimalOrNull(row, idx);


    }




    /** 市州单表行记录：保留文件 + Excel 行号用于失败定位 */


    private static class InvestCityRow {


        final int excelRow;


        final String fileName;


        final InvestmentProject project;


        final InvestmentMonthly monthly;




        InvestCityRow(int excelRow, String fileName, InvestmentProject project, InvestmentMonthly monthly) {


            this.excelRow = excelRow;


            this.fileName = fileName;


            this.project = project;


            this.monthly = monthly;


        }


    }


    /** 投资系统导出导入（审核比对基准）。


     *  列位**按表头关键字定位**，兼容两种模板：


     *   - 老模板《模板_查询结果(投资系统-客运+物流 2026.7).xls》：行号/单位/时期/计划总投资/自开始建设累计/本年计划投资/自年初累计/当月完成投资


     *   - 新模板《2026年X月投资系统项目明细.xls》（用户 2026-09-21 更换）：行号/投资项目/时期/项目所处阶段/计划总投资/本年计划投资/自开始建设至本年（月）底-累计完成投资/当月完成投资


     *  新模板**没有「自年初累计」列** → year_cum 留空（审核时按「基准缺失」跳过，不再当成 0 参与比对）。 */


    public int importInvestmentSystem(MultipartFile file, String period) throws Exception {


        List<InvestmentSystem> list = new ArrayList<>();


        int failRows = 0;


        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {


            Sheet sheet = wb.getSheetAt(0);


            requireHeaderKeyword(sheet, "投资系统导出", "投资项目", "项目名称", "单位", "计划总投资");


            Row header = sheet.getRow(0);


            int cName = findColByKeywords(header, "投资项目", "项目名称", "单位");


            int cTotal = findColByKeywords(header, "计划总投资");


            int cYearPlan = findColByKeywords(header, "本年计划投资");


            int cStartCum = findColByKeywords(header, "自开始建设");


            int cYearCum = findColByKeywords(header, "自年初");


            int cMonthDone = findColByKeywords(header, "当月完成投资");


            if (cName < 0 || cTotal < 0 || cYearPlan < 0 || cStartCum < 0 || cMonthDone < 0) {


                throw new RuntimeException("所选数据类型【投资系统导出】表头列不完整：需要「投资项目(或单位)、计划总投资、本年计划投资、"


                    + "自开始建设…累计完成投资、当月完成投资」等列，实际表头为「" + headerText(header)


                    + "」。请使用系统模板《2026年X月投资系统项目明细》或《模板_查询结果(投资系统-客运+物流)》整理后再导入");


            }


            for (int r = 1; r <= sheet.getLastRowNum(); r++) {


                Row row = sheet.getRow(r);


                if (row == null) continue;


                String name = getString(row, cName);


                if (name == null || name.trim().isEmpty()) continue;


                InvestmentSystem sys = new InvestmentSystem();


                sys.setReportPeriod(period);


                sys.setProjectName(name.trim());


                sys.setTotalInvestment(getDecimal(row, cTotal));


                sys.setStartCum(getDecimal(row, cStartCum));


                sys.setYearPlan(getDecimal(row, cYearPlan));


                sys.setYearCum(cYearCum < 0 ? null : getDecimal(row, cYearCum));


                sys.setMonthDone(getDecimal(row, cMonthDone));


                list.add(sys);


            }


        } catch (Exception e) {


            log.error("Investment system parse error", e);


            throw e;


        }


        investSystemMapper.delete(new LambdaQueryWrapper<InvestmentSystem>()


            .eq(InvestmentSystem::getReportPeriod, period));


        int success = 0;


        for (InvestmentSystem sys : list) {


            try {


                investSystemMapper.insert(sys);


                success++;


            } catch (Exception e) {


                failRows++;


                log.error("Investment system insert error: {}", sys.getProjectName(), e);


            }


        }


        recordBatch(file.getOriginalFilename(), "INVEST_SYSTEM", period, list.size(), success, failRows, null);


        log.info("Investment system imported: {} rows for {}", success, period);


        return success;


    }




    /** 城市公交月报导入（城市公共交通月度运营情况，企业级明细，取公共汽电车字段） */


    /** 城市公交（U203）模板关键列位（0-based）：所属地区/企业代码/企业名称/运营车数/客运量/轨道客运量/轮渡客运量/平均运距/填表人/联系电话/报出日期 */


    private static final int[] CITY_BUS_HEADER_COLS = {0, 1, 2, 5, 8, 13, 17, 19, 24, 25, 26};


    private static final String[] CITY_BUS_HEADER_KEYWORDS =


        {"所属地区", "企业代码", "企业名称", "公共汽电车_运营车数", "公共汽电车_客运量", "轨道交通_客运量",


         "城市客运轮渡_客运量", "公共汽电车平均运距", "填表人", "联系电话", "报出日期"};




    public ImportResult importCityBus(MultipartFile file, String period) throws Exception {


        List<CityBusRow> rows = new ArrayList<>();


        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {


            Sheet sheet = wb.getSheetAt(0);


            requireHeaderLayout(sheet, "城市公交", "模板_交企统U203表_城市公共交通月度运营情况（2026年7月）.xls",


                CITY_BUS_HEADER_COLS, CITY_BUS_HEADER_KEYWORDS);


            for (int r = 1; r <= sheet.getLastRowNum(); r++) {


                Row row = sheet.getRow(r);


                if (row == null) continue;


                String name = getString(row, 2);


                if (name == null || name.trim().isEmpty()) continue;


                CityBusMonthly item = new CityBusMonthly();


                item.setReportPeriod(period);


                item.setRegionCode(getString(row, 0));


                item.setCity(RegionUtil.cityByCode(getString(row, 0)));


                item.setEnterpriseCode(getString(row, 1));


                item.setEnterpriseName(name.trim());


                item.setOpVehicles(getDecimal(row, 5));


                item.setStopTotal(getDecimal(row, 6));


                item.setStopCity(getDecimal(row, 7));


                item.setPassengerVolume(getDecimal(row, 8));


                item.setPassengerCity(getDecimal(row, 9));


                item.setTurnover(getDecimal(row, 10));


                item.setTurnoverCity(getDecimal(row, 11));


                item.setRailPassengerVolume(getDecimal(row, 13));


                item.setRailTurnover(getDecimal(row, 14));


                item.setFerryPassengerVolume(getDecimal(row, 17));


                item.setFerryTurnover(getDecimal(row, 18));


                item.setAvgDistance(getDecimal(row, 19));


                item.setAvgDistanceCity(getDecimal(row, 20));


                item.setContactPerson(getString(row, 24));


                item.setContactPhone(getString(row, 25));


                item.setReportDate(getDateString(row, 26));


                item.setRateOpVehiclesMom(getDecimal(row, 29));


                item.setRatePassengerMom(getDecimal(row, 30));


                item.setRatePassengerCityMom(getDecimal(row, 31));


                item.setRateChengxiangMom(getDecimal(row, 32));


                item.setRateChengxiangTurnoverMom(getDecimal(row, 33));


                item.setAvgDistanceMomChange(getDecimal(row, 34));


                item.setAvgDistanceCityMomChange(getDecimal(row, 35));


                item.setDailyPassengerPerVehicle(getDecimal(row, 36));


                item.setRateOpVehiclesYoy(getDecimal(row, 39));


                item.setRatePassengerYoy(getDecimal(row, 40));


                item.setRatePassengerCityYoy(getDecimal(row, 41));


                item.setRateChengxiangYoy(getDecimal(row, 42));


                item.setRateChengxiangTurnoverYoy(getDecimal(row, 43));


                item.setStopCityRatio(getDecimal(row, 44));


                item.setPassengerCityRatio(getDecimal(row, 45));


                item.setChengxiangRatio(getDecimal(row, 46));


                item.setRateTurnoverYoy(getDecimal(row, 47));


                item.setRateTurnoverMom(getDecimal(row, 48));


                item.setPassengerChengxiang(getDecimal(row, 49));


                item.setTurnoverChengxiang(getDecimal(row, 50));


                item.setAvgDistanceChengxiang(getDecimal(row, 51));


                item.setVerifyExplanation(getString(row, 65));


                item.setReportNote(getString(row, 66));


                item.setModifyRecord(getString(row, 67));


                rows.add(new CityBusRow(r + 1, item));


            }


        } catch (Exception e) {


            log.error("CityBus import parse error: {}", e.getMessage());


            throw e;


        }


        // 幂等：同报表期先清旧数据与 CITY_BUS 审核结果


        cityBusMapper.delete(new LambdaQueryWrapper<CityBusMonthly>()


            .eq(CityBusMonthly::getReportPeriod, period));


        List<Long> busRuleIds = new ArrayList<>();


        for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {


            if ("CITY_BUS".equals(rule.getReportType())) busRuleIds.add(rule.getId());


        }


        if (!busRuleIds.isEmpty()) {


            auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()


                .eq(AuditResult::getReportPeriod, period)


                .in(AuditResult::getRuleId, busRuleIds));


        }


        int success = 0;


        List<String> failDetails = new ArrayList<>();


        for (CityBusRow item : rows) {


            try {


                cityBusMapper.insert(item.entity);


                success++;


            } catch (Exception e) {


                Throwable cause = e;


                while (cause.getCause() != null) cause = cause.getCause();


                String reason = cause.getMessage();


                if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();


                failDetails.add("第 " + item.excelRow + " 行（" + item.entity.getEnterpriseName() + "）：" + reason);


                log.error("CityBus insert error: row {}", item.excelRow, e);


            }


        }


        String errorDetail = String.join("\n", failDetails);


        if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";


        recordBatch(file.getOriginalFilename(), "CITY_BUS", period, rows.size(), success, failDetails.size(), errorDetail);


        log.info("CityBus imported: {} rows for {}", success, period);


        return new ImportResult(success, failDetails.size(), failDetails);


    }




    /** 城市公交行记录：保留 Excel 行号用于失败定位 */


    private static class CityBusRow {


        final int excelRow;


        final CityBusMonthly entity;




        CityBusRow(int excelRow, CityBusMonthly entity) {


            this.excelRow = excelRow;


            this.entity = entity;


        }


    }




    /** 巡游出租月报导入（市州级，17 行；含核实说明/上报说明/修改记录） */


    /** 巡游出租（U402）模板关键列位（0-based）：所属地区/企业代码/企业名称/载客车次总数/客运量/旅客周转量/运营车辆数/平均运距/填表人/联系电话/报出日期 */


    private static final int[] CITY_TAXI_HEADER_COLS = {0, 1, 2, 4, 6, 8, 10, 11, 15, 16, 17};


    private static final String[] CITY_TAXI_HEADER_KEYWORDS =


        {"所属地区", "企业代码", "企业名称", "载客车次总数", "客运量", "旅客周转量", "运营车辆数",


         "巡游出租汽车平均运距", "填表人", "联系电话", "报出日期"};




    public ImportResult importCityTaxi(MultipartFile file, String period) throws Exception {


        List<TaxiRow> rows = new ArrayList<>();


        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {


            Sheet sheet = wb.getSheetAt(0);


            requireHeaderLayout(sheet, "巡游出租", "模板_交行统U402表-巡游出租汽车运营服务情况月报(2026).xls",


                CITY_TAXI_HEADER_COLS, CITY_TAXI_HEADER_KEYWORDS);


            for (int r = 1; r <= sheet.getLastRowNum(); r++) {


                Row row = sheet.getRow(r);


                if (row == null) continue;


                String name = getString(row, 2);


                if (name == null || name.trim().isEmpty()) continue;


                CityTaxiMonthly item = new CityTaxiMonthly();


                item.setReportPeriod(period);


                item.setRegionCode(getString(row, 0));


                item.setCity(cityOf(getString(row, 0), name));


                item.setTripTotal(getDecimal(row, 4));


                item.setTripCity(getDecimal(row, 5));


                item.setPassengerVolume(getDecimal(row, 6));


                item.setPassengerCity(getDecimal(row, 7));


                item.setTurnover(getDecimal(row, 8));


                item.setTurnoverCity(getDecimal(row, 9));


                item.setOpVehicles(getDecimal(row, 10));


                item.setAvgDistance(getDecimal(row, 11));


                item.setAvgDistanceCity(getDecimal(row, 12));


                item.setContactPerson(getString(row, 15));


                item.setContactPhone(getString(row, 16));


                item.setReportDate(getDateString(row, 17));


                item.setDailyTripsPerVehicle(getDecimal(row, 18));


                item.setDailyPassengerPerVehicle(getDecimal(row, 19));


                item.setPassengersPerTrip(getDecimal(row, 20));


                item.setRateOpVehiclesMom(getDecimal(row, 21));


                item.setRateOpVehiclesYoy(getDecimal(row, 22));


                item.setRateTripMom(getDecimal(row, 23));


                item.setRateTripYoy(getDecimal(row, 24));


                item.setRatePassengerMom(getDecimal(row, 25));


                item.setRatePassengerYoy(getDecimal(row, 26));


                item.setAvgDistanceMomChange(getDecimal(row, 27));


                item.setPassengersPerTripCity(getDecimal(row, 28));


                item.setRateTripCityMom(getDecimal(row, 29));


                item.setRatePassengerCityMom(getDecimal(row, 30));


                item.setAvgDistanceCityMomChange(getDecimal(row, 31));


                item.setTripCityRatio(getDecimal(row, 32));


                item.setPassengerCityRatio(getDecimal(row, 33));


                item.setPassengersPerTripChengxiang(getDecimal(row, 34));


                item.setRateTripChengxiangMom(getDecimal(row, 35));


                item.setRatePassengerChengxiangMom(getDecimal(row, 36));


                item.setAvgDistanceChengxiang(getDecimal(row, 37));


                item.setVerifyExplanation(getString(row, 38));


                item.setReportNote(getString(row, 39));


                item.setModifyRecord(getString(row, 40));


                rows.add(new TaxiRow(r + 1, item));


            }


        } catch (Exception e) {


            log.error("CityTaxi import parse error: {}", e.getMessage());


            throw e;


        }


        // 幂等：同报表期先清旧数据与 CITY_TAXI 审核结果


        cityTaxiMapper.delete(new LambdaQueryWrapper<CityTaxiMonthly>()


            .eq(CityTaxiMonthly::getReportPeriod, period));


        deleteAuditResults(period, "CITY_TAXI");


        int success = 0;


        List<String> failDetails = new ArrayList<>();


        for (TaxiRow item : rows) {


            try {


                cityTaxiMapper.insert(item.entity);


                success++;


            } catch (Exception e) {


                Throwable cause = e;


                while (cause.getCause() != null) cause = cause.getCause();


                String reason = cause.getMessage();


                if (reason == null || reason.trim().isEmpty()) reason = e.getMessage();


                failDetails.add("第 " + item.excelRow + " 行（" + item.entity.getCity() + "）：" + reason);


                log.error("CityTaxi insert error: row {}", item.excelRow, e);


            }


        }


        String errorDetail = String.join("\n", failDetails);


        if (errorDetail.length() > 4000) errorDetail = errorDetail.substring(0, 4000) + "\n……";


        recordBatch(file.getOriginalFilename(), "CITY_TAXI", period, rows.size(), success, failDetails.size(), errorDetail);


        log.info("CityTaxi imported: {} rows for {}", success, period);


        return new ImportResult(success, failDetails.size(), failDetails);


    }




    /** 出租车运政车辆信息导入（车辆级明细，按市州归属统计用） */


    public synchronized int importCityTaxiAuth(MultipartFile file, String period) throws Exception {


        List<CityTaxiAuth> list = new ArrayList<>();


        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {


            Sheet sheet = wb.getSheetAt(0);


            requireHeaderKeyword(sheet, "出租车运政车辆", "车牌");


            for (int r = 1; r <= sheet.getLastRowNum(); r++) {


                Row row = sheet.getRow(r);


                if (row == null) continue;


                String plate = getString(row, 0);


                if (plate == null || plate.trim().isEmpty()) continue;


                CityTaxiAuth item = new CityTaxiAuth();


                item.setReportPeriod(period);


                item.setCity(taxiCity(getString(row, 9), getString(row, 8), getString(row, 4), getString(row, 18)));


                item.setPlateNo(plate.trim());


                item.setVehicleType(getString(row, 1));


                item.setBizType(getString(row, 2));


                item.setOwnerName(getString(row, 3));


                item.setLicenseNo(getString(row, 4));


                item.setFuelType(getString(row, 7));


                item.setArchiveNo(getString(row, 8));


                item.setRoadCertNo(getString(row, 9));


                item.setOperatorName(getString(row, 17));


                item.setOperatorAddr(getString(row, 18));


                item.setOpStatus(getString(row, 19));


                item.setCertStatus(getString(row, 20));


                list.add(item);


            }


        } catch (Exception e) {


            log.error("CityTaxiAuth import parse error", e);


            throw e;


        }


        // 幂等：同报表期先清旧数据（运政车辆量大，批量清理）


        cityTaxiAuthMapper.delete(new LambdaQueryWrapper<CityTaxiAuth>()


            .eq(CityTaxiAuth::getReportPeriod, period));


        deleteAuditResults(period, "CITY_TAXI");


        // 批量插入（BATCH 模式）：42636 行约 20~40 秒，逐条插入需约 6 分钟会触发前端超时


        int success = 0;


        int fail = 0;


        try (SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH)) {


            CityTaxiAuthMapper batchMapper = session.getMapper(CityTaxiAuthMapper.class);


            for (CityTaxiAuth item : list) {


                batchMapper.insert(item);


            }


            session.flushStatements();


            session.commit();


            success = list.size();


        } catch (Exception e) {


            fail = list.size();


            log.error("CityTaxiAuth batch insert error", e);


        }


        recordBatch(file.getOriginalFilename(), "CITY_TAXI_AUTH", period, list.size(), success, fail, null);


        log.info("CityTaxiAuth imported: {} rows for {}", success, period);


        return success;


    }






    // ========== 网约车月度输入导入（全省 pin / 订单） ==========




    /** 网约车总数月度输入：模板_网约车总数.xlsx（sheet1；A 列=月份日期；B=全省客运量 C=其中城市内 D=周转量 E=其中城市内周转量）。整表按报表期覆盖。 */


    public synchronized int importWycTotal(MultipartFile file, String period) throws Exception {


        Map<String, WycTotalMonthly> rows = new LinkedHashMap<>();


        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {


            Sheet sheet = wb.getSheetAt(0);


            for (int r = 2; r <= sheet.getLastRowNum(); r++) {


                Row row = sheet.getRow(r);


                if (row == null) continue;


                String rp = periodOfCell(row.getCell(0));


                if (rp == null) continue;


                double pk = dbl(row, 1), pck = dbl(row, 2), pz = dbl(row, 3), pcz = dbl(row, 4);


                if (pk <= 0 && pck <= 0 && pz <= 0 && pcz <= 0) continue;


                WycTotalMonthly item = new WycTotalMonthly();


                item.setReportPeriod(rp);


                item.setPinK(pk > 0 ? pk : null);


                item.setPinCK(pck > 0 ? pck : null);


                item.setPinZ(pz > 0 ? pz : null);


                item.setPinCZ(pcz > 0 ? pcz : null);


                rows.put(rp, item);


            }


        }


        if (rows.isEmpty()) throw new RuntimeException("未识别到有效月份行：A 列应为日期（如 2026-07），B-E 为全省客运量/城市内/周转量/城市内周转量");


        int ok = upsertWycTotal(rows);


        log.info("WycTotal imported: {} periods ok={}", rows.size(), ok);


        return ok;


    }




    /** 网约车订单及全省总量：模板_网约车订单及全省总量.xlsx 首个数据 sheet（A=月份；B-E=全省 pin；F=订单合计；G-W=17 市州订单） */


    public synchronized int importWycOrder(MultipartFile file, String period) throws Exception {


        Map<String, WycOrderMonthly> rows = new LinkedHashMap<>();


        try (Workbook wb = WorkbookFactory.create(file.getInputStream())) {


            Sheet sheet = wb.getSheetAt(0);


            int head = -1;


            for (int r = 0; r <= Math.min(sheet.getLastRowNum(), 4); r++) {


                Row row = sheet.getRow(r);


                if (row == null) continue;


                String a = getString(row, 0);


                if (a != null && a.contains("月份")) { head = r; break; }


            }


            if (head < 0) throw new RuntimeException("未找到表头：A 列需含“月份”");


            for (int r = head + 1; r <= sheet.getLastRowNum(); r++) {


                Row row = sheet.getRow(r);


                if (row == null) continue;


                String rp = periodOfCell(row.getCell(0));


                if (rp == null) continue;


                double pk = dbl(row, 1), pck = dbl(row, 2), pz = dbl(row, 3), pcz = dbl(row, 4), sum = dbl(row, 5);


                double citySum = 0;


                for (int k = 0; k < 17; k++) citySum += dbl(row, 6 + k);


                if (pk <= 0 && pck <= 0 && pz <= 0 && pcz <= 0 && sum <= 0 && citySum <= 0) continue;


                WycOrderMonthly it = new WycOrderMonthly();


                it.setReportPeriod(rp);


                it.setPinK(pk > 0 ? pk : null);


                it.setPinCK(pck > 0 ? pck : null);


                it.setPinZ(pz > 0 ? pz : null);


                it.setPinCZ(pcz > 0 ? pcz : null);


                if (sum <= 0 && citySum > 0) sum = citySum;


                it.setOrderSum(sum > 0 ? sum : null);


                it.setOrderWuhan(dblN(row, 6));


                it.setOrderHuangshi(dblN(row, 7));


                it.setOrderShiyan(dblN(row, 8));


                it.setOrderYichang(dblN(row, 9));


                it.setOrderXiangyang(dblN(row, 10));


                it.setOrderEzhou(dblN(row, 11));


                it.setOrderJingmen(dblN(row, 12));


                it.setOrderXiaogan(dblN(row, 13));


                it.setOrderJingzhou(dblN(row, 14));


                it.setOrderHuanggang(dblN(row, 15));


                it.setOrderXianning(dblN(row, 16));


                it.setOrderSuizhou(dblN(row, 17));


                it.setOrderEnshi(dblN(row, 18));


                it.setOrderXiantao(dblN(row, 19));


                it.setOrderQianjiang(dblN(row, 20));


                it.setOrderTianmen(dblN(row, 21));


                it.setOrderShennong(dblN(row, 22));


                rows.put(rp, it);


            }


        }


        if (rows.isEmpty()) throw new RuntimeException("未识别到有效月份行：A 列应为 yyyy-MM，B-E 全省 pin / F 合计 / G-W 17 市州订单");


        int ok = upsertWyc(rows);


        log.info("WycOrder imported: {} periods ok={}", rows.size(), ok);


        return ok;


    }




    private int upsertWyc(Map<String, WycOrderMonthly> rows) {


        int ok = 0, fail = 0;


        for (WycOrderMonthly item : rows.values()) {


            try {


                wycOrderMapper.delete(new LambdaQueryWrapper<WycOrderMonthly>().eq(WycOrderMonthly::getReportPeriod, item.getReportPeriod()));


                wycOrderMapper.insert(item);


                recordBatch("网约车订单及全省总量", "WYC_ORDER", item.getReportPeriod(), 1, 1, 0, null);


                ok++;


            } catch (Exception e) {


                fail++;


                log.error("WycOrder insert error period={}", item.getReportPeriod(), e);


            }


        }


        return ok;


    }




    private int upsertWycTotal(Map<String, WycTotalMonthly> rows) {


        int ok = 0, fail = 0;


        for (WycTotalMonthly item : rows.values()) {


            try {


                wycTotalMapper.delete(new LambdaQueryWrapper<WycTotalMonthly>().eq(WycTotalMonthly::getReportPeriod, item.getReportPeriod()));


                wycTotalMapper.insert(item);


                recordBatch("网约车总数", "WYC_TOTAL", item.getReportPeriod(), 1, 1, 0, null);


                ok++;


            } catch (Exception e) {


                fail++;


                log.error("WycTotal insert error period={}", item.getReportPeriod(), e);


            }


        }


        return ok;


    }




    // ========== 网约车订单运行监测分析报告（docx）导入 ==========




    /** 网约车订单报告导入结果（报表期/订单合计/覆盖市州数/缺失市州） */


    public static class WycOrderReportResult {


        private final String period;


        private final double orderSum;


        private final int cityCount;


        private final List<String> missingCities;




        public WycOrderReportResult(String period, double orderSum, int cityCount, List<String> missingCities) {


            this.period = period;


            this.orderSum = orderSum;


            this.cityCount = cityCount;


            this.missingCities = missingCities;


        }




        public String getPeriod() { return period; }


        public double getOrderSum() { return orderSum; }


        public int getCityCount() { return cityCount; }


        public List<String> getMissingCities() { return missingCities; }


    }




    /**


     * 《网约车行业YYYY年M月运行监测情况分析报告》（docx）导入：


     * 解析报告附件中「地市名称 | … | 订单数总数」表的 17 市州订单数，写入 wyc_order_monthly 的


     * F 订单合计与 G-W 17 市州订单列；全省 pin（B-E）沿用该期已有行，若无该期行则从


     * wyc_total_monthly 同报表期复制。报表期优先取报告标题年月，取不到时用页面所选报表期。


     */


    public synchronized WycOrderReportResult importWycOrderReport(MultipartFile file, String period) throws Exception {


        Map<String, Double> orders = new LinkedHashMap<>();


        double[] sumHolder = new double[]{0.0};


        String docPeriod;


        byte[] bytes;


        try (java.io.InputStream in = file.getInputStream()) {


            bytes = IoUtil.readBytes(in);


        }


        boolean isZip = bytes.length > 3 && (bytes[0] & 0xFF) == 0x50 && (bytes[1] & 0xFF) == 0x4B;


        boolean isWordMl = !isZip && looksLikeWordMl(bytes);


        try {


            if (isZip && !isWordPackage(bytes)) {


                throw new RuntimeException("这不是 Word 文档（压缩包里没有 word/document.xml），"


                        + "多半是 Excel 或其它格式。请上传网约车订单分析报告的 .docx（或 Word 2003 XML）");


            }


            if (isZip) {


                try (XWPFDocument doc = new XWPFDocument(new java.io.ByteArrayInputStream(bytes))) {


                    docPeriod = periodOfParagraphs(paragraphsOfDocx(doc));


                    parseWycOrderReportTables(doc, orders, sumHolder);


                }


            } else if (isWordMl) {


                // 网约车监管平台下发的报告常被 WPS/Word 存成「Word 2003 XML（平铺 XML）」后再改名 .docx：


                // 这种文件不是 zip 包，XWPFDocument 打不开（报 "File is not a zip file"），需按 XML 直接解析。


                String xml = new String(bytes, java.nio.charset.StandardCharsets.UTF_8);


                Document dom = parseXmlSafely(xml);


                docPeriod = periodOfParagraphs(paragraphsOfDocxXml(dom));


                parseWycOrderReportTablesXml(dom, orders, sumHolder);


            } else if (isOle2(bytes)) {


                throw new RuntimeException("这是 Word 97-2003 老格式（.doc），请用 WPS/Word 打开后「另存为」docx 格式再导入");


            } else {


                throw new RuntimeException("无法识别的文件格式：既不是 docx（zip 包），也不是 Word 2003 XML 或 .doc");


            }


        } catch (RuntimeException e) {


            throw e;


        } catch (Exception e) {


            throw new RuntimeException("无法读取 Word 文档，请上传 .docx 格式的《网约车行业YYYY年M月运行监测情况分析报告》；"


                    + "若上传的其实是 Excel/PDF，请改用对应模板导入。（原始错误：" + e.getMessage() + "）");


        }


        if (orders.isEmpty()) {


            throw new RuntimeException("未解析到任何市州订单数，请确认上传的是《网约车行业YYYY年M月运行监测情况分析报告》");


        }


        String rp = null;


        if (docPeriod != null && docPeriod.matches("\\d{4}-\\d{2}")) rp = docPeriod;


        else if (period != null && period.matches("\\d{4}-\\d{2}")) rp = period;


        if (rp == null) {


            throw new RuntimeException("无法确定报表期：报告标题未含「YYYY年M月」，且页面未选择报表期");


        }




        List<String> missing = new ArrayList<>();


        double citySum = 0.0;


        for (String city : RegionUtil.cityList()) {


            Double v = orders.get(city);


            if (v == null) missing.add(city);


            else citySum += v;


        }


        double sum = sumHolder[0] > 0 ? sumHolder[0] : citySum;




        WycOrderMonthly it = wycOrderMapper.selectOne(new LambdaQueryWrapper<WycOrderMonthly>()


                .eq(WycOrderMonthly::getReportPeriod, rp).last("LIMIT 1"));


        if (it == null) it = new WycOrderMonthly();


        it.setReportPeriod(rp);


        it.setOrderSum(sum > 0 ? sum : null);


        it.setOrderWuhan(orderOf(orders, "武汉市"));


        it.setOrderHuangshi(orderOf(orders, "黄石市"));


        it.setOrderShiyan(orderOf(orders, "十堰市"));


        it.setOrderYichang(orderOf(orders, "宜昌市"));


        it.setOrderXiangyang(orderOf(orders, "襄阳市"));


        it.setOrderEzhou(orderOf(orders, "鄂州市"));


        it.setOrderJingmen(orderOf(orders, "荆门市"));


        it.setOrderXiaogan(orderOf(orders, "孝感市"));


        it.setOrderJingzhou(orderOf(orders, "荆州市"));


        it.setOrderHuanggang(orderOf(orders, "黄冈市"));


        it.setOrderXianning(orderOf(orders, "咸宁市"));


        it.setOrderSuizhou(orderOf(orders, "随州市"));


        it.setOrderEnshi(orderOf(orders, "恩施州"));


        it.setOrderXiantao(orderOf(orders, "仙桃市"));


        it.setOrderQianjiang(orderOf(orders, "潜江市"));


        it.setOrderTianmen(orderOf(orders, "天门市"));


        it.setOrderShennong(orderOf(orders, "神农架林区"));




        if (it.getPinK() == null || it.getPinCK() == null || it.getPinZ() == null || it.getPinCZ() == null) {


            WycTotalMonthly tot = wycTotalMapper.selectOne(new LambdaQueryWrapper<WycTotalMonthly>()


                    .eq(WycTotalMonthly::getReportPeriod, rp).last("LIMIT 1"));


            if (tot != null) {


                if (it.getPinK() == null) it.setPinK(tot.getPinK());


                if (it.getPinCK() == null) it.setPinCK(tot.getPinCK());


                if (it.getPinZ() == null) it.setPinZ(tot.getPinZ());


                if (it.getPinCZ() == null) it.setPinCZ(tot.getPinCZ());


            }


        }




        wycOrderMapper.delete(new LambdaQueryWrapper<WycOrderMonthly>().eq(WycOrderMonthly::getReportPeriod, rp));


        wycOrderMapper.insert(it);


        recordBatch(file.getOriginalFilename(), "WYC_ORDER_REPORT", rp, 1, 1, 0, null);


        log.info("WycOrderReport imported: period={} sum={} cities={} missing={}", rp, sum, orders.size(), missing);


        return new WycOrderReportResult(rp, sum, orders.size(), missing);


    }




    /** 报告标题中的报表期：网约车行业2026年7月 -> 2026-07；取不到返回 null */


    private String periodOfParagraphs(List<String> paragraphs) {


        java.util.regex.Pattern titlePat = java.util.regex.Pattern.compile("网约车行业\\s*(\\d{4})\\s*年\\s*(\\d{1,2})\\s*月");


        java.util.regex.Pattern anyPat = java.util.regex.Pattern.compile("(\\d{4})\\s*年\\s*(\\d{1,2})\\s*月");


        String fallback = null;


        int scanned = 0;


        for (String text : paragraphs) {


            if (text == null) continue;


            String flat = text.replaceAll("\\s+", "");


            if (flat.isEmpty()) continue;


            java.util.regex.Matcher m = titlePat.matcher(flat);


            if (m.find()) return ymd(m.group(1), m.group(2));


            if (fallback == null && scanned < 10) {


                java.util.regex.Matcher m2 = anyPat.matcher(flat);


                if (m2.find()) fallback = ymd(m2.group(1), m2.group(2));


            }


            scanned++;


        }


        return fallback;


    }




    /** docx 段落文本（保持原始顺序） */


    private List<String> paragraphsOfDocx(XWPFDocument doc) {


        List<String> out = new ArrayList<>();


        for (XWPFParagraph para : doc.getParagraphs()) out.add(para.getText());


        return out;


    }




    /** Word 2003 XML（WordML）段落文本：<w:p> 下所有 <w:t> 拼接后压掉空白 */


    private List<String> paragraphsOfDocxXml(Document dom) {


        List<String> out = new ArrayList<>();


        for (Element p : elementsByLocalName(dom, "p")) out.add(textOfXmlElement(p));


        return out;


    }




    /** 是否为 Word 2003 XML（WordML 平铺 XML）——这种文件常被改名成 .docx */


    private boolean looksLikeWordMl(byte[] bytes) {


        int n = Math.min(bytes.length, 4096);


        String head = new String(bytes, 0, n, java.nio.charset.StandardCharsets.UTF_8);


        return head.contains("wordml") && head.contains("<w:wordDocument");


    }




    /** zip 包内是否含 word/document.xml（用于区分 docx 与 xlsx/其它 OPC 包） */


    private boolean isWordPackage(byte[] bytes) {


        try (java.util.zip.ZipInputStream zin =


                     new java.util.zip.ZipInputStream(new java.io.ByteArrayInputStream(bytes))) {


            java.util.zip.ZipEntry e;


            while ((e = zin.getNextEntry()) != null) {


                String n = e.getName();


                if ("word/document.xml".equals(n)) return true;


            }


        } catch (Exception ignore) {


            return false;


        }


        return false;


    }




    /** 是否为 OLE2 复合文档（Word 97-2003 .doc） */


    private boolean isOle2(byte[] b) {


        return b.length > 8 && (b[0] & 0xFF) == 0xD0 && (b[1] & 0xFF) == 0xCF


                && (b[2] & 0xFF) == 0x11 && (b[3] & 0xFF) == 0xE0;


    }




    /** 安全解析 XML：禁用外部实体/DTD（报告来自外部平台，防 XXE） */


    private Document parseXmlSafely(String xml) throws Exception {


        javax.xml.parsers.DocumentBuilderFactory f = javax.xml.parsers.DocumentBuilderFactory.newInstance();


        f.setNamespaceAware(true);


        try { f.setFeature("http://apache.org/xml/features/nonvalidating/load-external-dtd", false); } catch (Exception ignore) { }


        try { f.setFeature("http://xml.org/sax/features/external-general-entities", false); } catch (Exception ignore) { }


        try { f.setFeature("http://xml.org/sax/features/external-parameter-entities", false); } catch (Exception ignore) { }


        javax.xml.parsers.DocumentBuilder b = f.newDocumentBuilder();


        b.setEntityResolver(new org.xml.sax.EntityResolver() {


            @Override


            public org.xml.sax.InputSource resolveEntity(String publicId, String systemId) {


                return new org.xml.sax.InputSource(new java.io.StringReader(""));


            }


        });


        return b.parse(new org.xml.sax.InputSource(new java.io.StringReader(xml)));


    }




    /** 按 localName 取全部后代元素（命名空间无关，兼容 WordML 与 docx 的 w: 前缀） */


    private List<Element> elementsByLocalName(Document dom, String localName) {


        List<Element> out = new ArrayList<>();


        NodeList list = dom.getElementsByTagNameNS("*", localName);


        for (int i = 0; i < list.getLength(); i++) out.add((Element) list.item(i));


        return out;


    }




    /** 只取直接子元素（避免嵌套表格/嵌套属性元素被算进来） */


    private List<Element> childElementsByLocalName(Element parent, String localName) {


        List<Element> out = new ArrayList<>();


        NodeList kids = parent.getChildNodes();


        for (int i = 0; i < kids.getLength(); i++) {


            Node n = kids.item(i);


            if (n.getNodeType() == Node.ELEMENT_NODE && localName.equals(n.getLocalName())) out.add((Element) n);


        }


        return out;


    }




    /** 元素内所有 <w:t> 文本拼接并压掉空白 */


    private String textOfXmlElement(Element el) {


        StringBuilder sb = new StringBuilder();


        NodeList ts = el.getElementsByTagNameNS("*", "t");


        for (int i = 0; i < ts.getLength(); i++) sb.append(ts.item(i).getTextContent());


        return flat(sb.toString());


    }




    private String ymd(String y, String mo) {


        try {


            int month = Integer.parseInt(mo);


            if (month < 1 || month > 12) return null;


            return String.format("%04d-%02d", Integer.parseInt(y), month);


        } catch (NumberFormatException e) {


            return null;


        }


    }




    /** 解析报告中的「地市名称 | … | 订单数总数」表，orders=市州订单，sumHolder[0]=合计行订单数 */


    private void parseWycOrderReportTables(XWPFDocument doc, Map<String, Double> orders, double[] sumHolder) {


        List<List<List<String>>> tables = new ArrayList<>();


        for (XWPFTable table : doc.getTables()) {


            List<List<String>> rows = new ArrayList<>();


            for (XWPFTableRow row : table.getRows()) {


                List<String> cells = new ArrayList<>();


                for (org.apache.poi.xwpf.usermodel.XWPFTableCell c : row.getTableCells()) cells.add(flat(c.getText()));


                rows.add(cells);


            }


            tables.add(rows);


        }


        matchWycOrderTable(tables, orders, sumHolder);


    }




    /** Word 2003 XML（WordML）分支：<w:tbl>/<w:tr>/<w:tc> 抽成文本表后走同一套匹配逻辑 */


    private void parseWycOrderReportTablesXml(Document dom, Map<String, Double> orders, double[] sumHolder) {


        List<List<List<String>>> tables = new ArrayList<>();


        for (Element tbl : elementsByLocalName(dom, "tbl")) {


            List<List<String>> rows = new ArrayList<>();


            for (Element tr : childElementsByLocalName(tbl, "tr")) {


                List<String> cells = new ArrayList<>();


                for (Element tc : childElementsByLocalName(tr, "tc")) cells.add(textOfXmlElement(tc));


                rows.add(cells);


            }


            tables.add(rows);


        }


        matchWycOrderTable(tables, orders, sumHolder);


    }




    /**


     * 在「文本化表格」里找附件1（表头含「订单数总数」/「订单数」）并取 17 市州订单数与合计。


     * 入参 tables = 表 -> 行 -> 单元格文本（已压空白），与来源格式无关。


     */


    private void matchWycOrderTable(List<List<List<String>>> tables, Map<String, Double> orders, double[] sumHolder) {


        for (List<List<String>> rows : tables) {


            if (rows.size() < 3) continue;


            int headerRow = -1;


            int orderCol = -1;


            for (int r = 0; r < Math.min(rows.size(), 3); r++) {


                List<String> cells = rows.get(r);


                for (int c = 0; c < cells.size(); c++) {


                    String norm = cells.get(c);


                    if (norm.contains("订单数总数") || "订单数".equals(norm)) {


                        headerRow = r;


                        orderCol = c;


                        break;


                    }


                }


                if (headerRow >= 0) break;


            }


            if (headerRow < 0 || orderCol < 0) continue;




            Map<String, Double> found = new LinkedHashMap<>();


            double citySum = 0.0;


            boolean hasTotal = false;


            for (int r = headerRow + 1; r < rows.size(); r++) {


                List<String> cells = rows.get(r);


                if (cells.isEmpty() || orderCol >= cells.size()) continue;


                String name = cells.get(0);


                if (name.isEmpty()) continue;


                Double val = parsePlainNumber(cells.get(orderCol));


                if (val == null) continue;


                String city = RegionUtil.normalizeCityName(name);


                if (city != null && RegionUtil.cityList().contains(city)) {


                    found.put(city, val);


                    citySum += val;


                } else if (name.contains("合计") || name.contains("总计") || name.contains("全省") || name.contains("湖北")) {


                    hasTotal = true;


                    sumHolder[0] = val;


                }


            }


            if (!found.isEmpty()) {


                orders.clear();


                orders.putAll(found);


                if (!hasTotal) sumHolder[0] = citySum;


                return;


            }


        }


    }




    private String flat(String s) {


        return s == null ? "" : s.replaceAll("\\s+", "").trim();


    }




    /** 「11,183,267 单」这类文本取数值 */


    private Double parsePlainNumber(String s) {


        if (s == null) return null;


        String t = s.replaceAll("[,\\s]", "").trim();


        if (t.isEmpty()) return null;


        java.util.regex.Matcher m = java.util.regex.Pattern.compile("-?\\d+(?:\\.\\d+)?").matcher(t);


        if (!m.find()) return null;


        try {


            return Double.parseDouble(m.group());


        } catch (NumberFormatException e) {


            return null;


        }


    }




    private Double orderOf(Map<String, Double> orders, String city) {


        Double v = orders.get(city);


        return (v == null || v <= 0) ? null : v;


    }


    private String periodOfCell(Cell c) {


        if (c == null) return null;


        if (c.getCellType() == CellType.NUMERIC) {


            try {


                double v = c.getNumericCellValue();


                if (v >= 40000 && v <= 60000) {


                    java.util.Date d = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(v);


                    java.text.SimpleDateFormat f = new java.text.SimpleDateFormat("yyyy-MM");


                    return f.format(d);


                }


            } catch (Exception ignore) { }


        }


        String s = null;


        try { s = c.getStringCellValue(); } catch (Exception ignore) { }


        if (s != null) {


            java.util.regex.Matcher m = java.util.regex.Pattern.compile("(\\d{4})[-/.年](\\d{1,2})").matcher(s.trim());


            if (m.find()) return String.format("%04d-%02d", Integer.parseInt(m.group(1)), Integer.parseInt(m.group(2)));


        }


        return null;


    }




    private double dbl(Row row, int idx) {


        Double v = getDecimal(row, idx);


        return v == null ? 0.0 : v;


    }




    private Double dblN(Row row, int idx) {


        Double v = getDecimal(row, idx);


        return (v == null || v <= 0) ? null : v;


    }




    /** 运政出租车市州提取：道路运输证字号→档案号→经营权号→业户地址，取 6 位区划代码 */


    private static final java.util.regex.Pattern TAXI_REGION_PAT =


        java.util.regex.Pattern.compile("(4290\\d{2}|42(?:0[1-9]|1[0-3]|28)\\d{2})");




    private String taxiCity(String... fields) {


        for (String f : fields) {


            if (f == null) continue;


            java.util.regex.Matcher m = TAXI_REGION_PAT.matcher(f);


            if (m.find()) {


                String city = RegionUtil.cityByCode(m.group(1));


                if (city != null) return city;


            }


        }


        return null;


    }




    /** 市州归属：优先按区划代码识别，其次按名称规范化 */


    private String cityOf(String regionCode, String name) {


        String city = RegionUtil.cityByCode(regionCode);


        if (city != null) return city;


        return RegionUtil.normalizeCityName(name);


    }




    /** 清除某报表类型在指定报表期的审核结果（导入幂等） */


    private void deleteAuditResults(String period, String reportType) {


        List<Long> ruleIds = new ArrayList<>();


        for (com.trafficaudit.rulemanage.entity.AuditRule rule : ruleMapper.selectList(null)) {


            if (reportType.equals(rule.getReportType())) ruleIds.add(rule.getId());


        }


        if (!ruleIds.isEmpty()) {


            auditResultMapper.delete(new LambdaQueryWrapper<AuditResult>()


                .eq(AuditResult::getReportPeriod, period)


                .in(AuditResult::getRuleId, ruleIds));


        }


    }




    /** 巡游出租行记录：保留 Excel 行号用于失败定位 */


    private static class TaxiRow {


        final int excelRow;


        final CityTaxiMonthly entity;




        TaxiRow(int excelRow, CityTaxiMonthly entity) {


            this.excelRow = excelRow;


            this.entity = entity;


        }


    }




    private Sheet pickInvestmentSheet(Workbook wb, boolean passenger) {


        if (passenger) {


            for (int i = 0; i < wb.getNumberOfSheets(); i++) {


                String name = wb.getSheetName(i);


                if (name != null && name.contains("明细")) return wb.getSheetAt(i);


            }


            return wb.getSheetAt(0);


        }


        for (int i = 0; i < wb.getNumberOfSheets(); i++) {


            String name = wb.getSheetName(i);


            if (name != null && name.contains("分项目投资")) return wb.getSheetAt(i);


        }


        return wb.getSheetAt(0);


    }




    /** 市州小节行识别：短名/规范名 -> 规范名，非市州行返回 null */


    private String investmentCity(String a) {


        if (a == null) return null;


        String full = INVEST_CITY_SHORT_TO_FULL.get(a);


        if (full != null) return full;


        String norm = RegionUtil.normalizeCityName(a);


        if (RegionUtil.CITY_LIST.contains(norm)) return norm;


        return null;


    }




    /** 开工/建成时间：数字（2025 或 202501）或文本统一为 yyyy 或 yyyyMM */


    private String investTime(String v) {


        if (v == null || v.trim().isEmpty()) return null;


        String t = v.trim();


        if (t.matches("\\d+\\.0")) t = t.substring(0, t.length() - 2);


        if (t.matches("\\d{4}")) return t;


        if (t.matches("\\d{6}")) return t;


        if (t.matches("\\d{4}-\\d{2}-\\d{2}")) return t;


        return t;


    }




    /** 投资行记录：保留 Excel 行号用于失败定位 */


    private static class InvestRow {


        final int excelRow;


        final InvestmentProject project;


        final InvestmentMonthly monthly;




        InvestRow(int excelRow, InvestmentProject project, InvestmentMonthly monthly) {


            this.excelRow = excelRow;


            this.project = project;


            this.monthly = monthly;


        }


    }




    // ========== 通用文件夹批量导入（服务端目录浏览器） ==========




    /**


     * 目录浏览器：path 为空时返回项目 docs 数据目录下的子目录；


     * 返回当前目录绝对路径、上级目录、子目录列表及其中直接包含的 Excel 文件名/数量。


     */


    public Map<String, Object> listDirs(String path) throws Exception {


        File cur;


        if (path == null || path.trim().isEmpty()) {


            File docs = resolveDocsDir();


            if (!docs.isDirectory()) {


                throw new RuntimeException("未找到数据目录 docs（当前运行目录：" + System.getProperty("user.dir") + "）");


            }


            cur = docs;


        } else {


            cur = new File(path);


            if (!cur.isDirectory()) {


                throw new RuntimeException("目录不存在或不是文件夹：" + path);


            }


        }


        Map<String, Object> result = new LinkedHashMap<>();


        result.put("current", cur.getAbsolutePath());


        result.put("parent", cur.getParent() == null ? "" : cur.getParent());


        List<Map<String, Object>> dirs = new ArrayList<>();


        File[] subs = cur.listFiles(File::isDirectory);


        int dirTotal = 0;


        if (subs != null) {


            Arrays.sort(subs, Comparator.comparing(File::getName));


            for (File d : subs) {


                if (d.isHidden()) continue;


                dirTotal++;


                if (dirs.size() >= 300) continue;


                Map<String, Object> m = new LinkedHashMap<>();


                m.put("name", d.getName());


                m.put("path", d.getAbsolutePath());


                dirs.add(m);


            }


        }


        result.put("dirs", dirs);


        result.put("dirsTruncated", dirTotal > dirs.size());


        List<String> excelFiles = new ArrayList<>();


        File[] files = cur.listFiles((d, n) -> {


            String lower = n.toLowerCase();


            return lower.endsWith(".xlsx") || lower.endsWith(".xls");


        });


        int excelTotal = files == null ? 0 : files.length;


        if (files != null) {


            Arrays.sort(files, Comparator.comparing(File::getName));


            int take = Math.min(files.length, 300);


            for (int i = 0; i < take; i++) excelFiles.add(files[i].getName());


        }


        result.put("excelCount", excelTotal);


        result.put("files", excelFiles);


        result.put("filesTruncated", excelTotal > excelFiles.size());


        return result;


    }




    /** 向上查找项目 docs 数据目录（兼容从 IDEA 以 server 模块目录启动的情况） */


    private File resolveDocsDir() {


        File base = new File(System.getProperty("user.dir"));


        File cur = base;


        for (int i = 0; i < 4 && cur != null; i++) {


            File candidate = new File(cur, "docs");


            if (candidate.isDirectory()) return candidate;


            cur = cur.getParentFile();


        }


        return new File(base, "docs");


    }




    /**


     * 从服务端文件夹批量导入：只扫描 dir 直接包含的 .xlsx/.xls，


     * 按 type 逐个调用对应导入方法（与上传导入同一套解析逻辑），返回逐文件结果。


     */


    public Map<String, Object> importFromDir(String type, String period, boolean skipImported, String dir) throws Exception {


        File folder = new File(dir);


        if (!folder.isDirectory()) {


            throw new RuntimeException("目录不存在或不是文件夹：" + dir);


        }


        File[] files = folder.listFiles((d, n) -> {


            String lower = n.toLowerCase();


            return lower.endsWith(".xlsx") || lower.endsWith(".xls");


        });


        if (files == null || files.length == 0) {


            throw new RuntimeException("目录中没有 Excel 文件（.xlsx/.xls）：" + folder.getAbsolutePath());


        }


        Arrays.sort(files, Comparator.comparing(File::getName));


        List<Map<String, Object>> fileResults = new ArrayList<>();


        int totalSuccess = 0, totalFail = 0;


        for (File f : files) {


            Map<String, Object> fr = new LinkedHashMap<>();


            fr.put("fileName", f.getName());


            try {


                String filePeriod = detectPeriod(f.getName(), period);


                if (filePeriod == null || filePeriod.trim().isEmpty()) {


                    throw new RuntimeException("无法从文件名识别报表期（支持 2026年7月 / 2026.7 / 2026-07 / 7月），也未选择报表期：" + f.getName());


                }


                fr.put("period", filePeriod);


                if (skipImported && alreadyImported(filePeriod, f.getName())) {


                    fr.put("skipped", true);


                    fr.put("success", 0);


                    fr.put("fail", 0);


                    totalFail++;


                    fileResults.add(fr);


                    continue;


                }


                byte[] bytes;


                try (InputStream is = new FileInputStream(f)) {


                    bytes = IoUtil.readBytes(is);


                }


                MultipartFile mf = new ByteArrayMultipartFile(f.getName(), bytes);


                ImportResult r = importDirFile(type, mf, filePeriod);


                fr.put("success", r.getSuccess());


                fr.put("fail", r.getFail());


                if (r.getFail() > 0 && r.getFailDetails() != null && !r.getFailDetails().isEmpty()) {


                    String d = r.getFailDetails().get(0);


                    if (d.length() > 300) d = d.substring(0, 300);


                    fr.put("error", d);


                }


                totalSuccess += r.getSuccess();


                totalFail += r.getFail();


            } catch (Exception e) {


                String msg = e.getMessage() == null ? e.toString() : e.getMessage();


                if (msg.length() > 300) msg = msg.substring(0, 300);


                fr.put("success", 0);


                fr.put("fail", 0);


                fr.put("error", msg);


                totalFail++;


            }


            fileResults.add(fr);


        }


        Map<String, Object> result = new LinkedHashMap<>();


        result.put("type", type);


        result.put("total", fileResults.size());


        result.put("success", totalSuccess);


        result.put("fail", totalFail);


        result.put("files", fileResults);


        return result;


    }




    /**


     * 按文件路径导入单个 Excel（供前端逐文件导入显示进度）。


     * 报表期优先从文件名识别（如 2026年7月 / 2026.7 / 2026-07 / 7月），识别不到才用 period。


     */


    public Map<String, Object> importDirFileByPath(String type, String period, boolean skipImported, String filePath) throws Exception {


        File f = new File(filePath);


        if (!f.isFile()) {


            throw new RuntimeException("文件不存在：" + filePath);


        }


        String filePeriod = detectPeriod(f.getName(), period);


        if (filePeriod == null || filePeriod.trim().isEmpty()) {


            throw new RuntimeException("无法从文件名识别报表期（支持 2026年7月 / 2026.7 / 2026-07 / 7月），也未选择报表期：" + f.getName());


        }


        Map<String, Object> fr = new LinkedHashMap<>();


        fr.put("fileName", f.getName());


        fr.put("period", filePeriod);


        if (skipImported && alreadyImported(filePeriod, f.getName())) {


            fr.put("skipped", true);


            fr.put("success", 0);


            fr.put("fail", 0);


            fr.put("error", "该报表期已导入过此文件，已跳过（如需覆盖请取消勾选“跳过已导入文件”）");


            return fr;


        }


        byte[] bytes;


        try (InputStream is = new FileInputStream(f)) {


            bytes = IoUtil.readBytes(is);


        }


        ImportResult r = importDirFile(type, new ByteArrayMultipartFile(f.getName(), bytes), filePeriod);


        fr.put("success", r.getSuccess());


        fr.put("fail", r.getFail());


        if (r.getFail() > 0 && r.getFailDetails() != null && !r.getFailDetails().isEmpty()) {


            String d = r.getFailDetails().get(0);


            if (d.length() > 300) d = d.substring(0, 300);


            fr.put("error", d);


        }


        return fr;


    }




    /** 该报表期是否已成功导入过同名文件（用于文件夹重复导入时跳过） */


    private boolean alreadyImported(String period, String fileName) {


        if (period == null || fileName == null) return false;


        Long cnt = importBatchMapper.selectCount(new LambdaQueryWrapper<ImportBatch>()


            .eq(ImportBatch::getReportPeriod, period)


            .eq(ImportBatch::getFileName, fileName)


            .eq(ImportBatch::getFailRows, 0));


        return cnt != null && cnt > 0;


    }




    /** 从文件名识别报表期：2026年7月 / 2026年07月 / 2026.7 / 2026-07 / 202607 / 7月（无年份用 period 年份或当前年份） */


    private String detectPeriod(String fileName, String defaultPeriod) {


        if (fileName == null) return defaultPeriod;


        String name = fileName.replace("（", "(").replace("）", ")").replace(" ", "");


        java.util.regex.Matcher m;


        m = java.util.regex.Pattern.compile("(\\d{4})\\s*年\\s*(\\d{1,2})\\s*月").matcher(name);


        if (m.find()) return String.format("%04d-%02d", Integer.parseInt(m.group(1)), Integer.parseInt(m.group(2)));


        m = java.util.regex.Pattern.compile("(\\d{4})[.\\-/\\s](\\d{1,2})").matcher(name);


        if (m.find()) return String.format("%04d-%02d", Integer.parseInt(m.group(1)), Integer.parseInt(m.group(2)));


        m = java.util.regex.Pattern.compile("(?:^|[^\\d])(\\d{4})(\\d{2})(?=[^\\d]|$)").matcher(name);


        if (m.find()) {


            int y = Integer.parseInt(m.group(1));


            int mo = Integer.parseInt(m.group(2));


            if (y >= 2000 && y <= 2100 && mo >= 1 && mo <= 12) {


                return String.format("%04d-%02d", y, mo);


            }


        }


        m = java.util.regex.Pattern.compile("(\\d{1,2})\\s*月").matcher(name);


        if (m.find()) {


            int mo = Integer.parseInt(m.group(1));


            if (mo >= 1 && mo <= 12) {


                return String.format("%04d-%02d", defaultYear(defaultPeriod), mo);


            }


        }


        return defaultPeriod;


    }




    private int defaultYear(String defaultPeriod) {


        if (defaultPeriod != null && defaultPeriod.matches("\\d{4}-\\d{2}")) {


            return Integer.parseInt(defaultPeriod.substring(0, 4));


        }


        return java.time.Year.now().getValue();


    }




    /** 类型分发：与上传导入同一套解析方法 */


    private ImportResult importDirFile(String type, MultipartFile file, String period) throws Exception {


        switch (type) {


            case "h2032": return importH2032(file, period);


            case "passengerMonthly": return importPassengerMonthly(file, period);


            case "passengerIndividual": return importPassengerIndividual(file, period);


            case "passengerAuth": return importPassengerAuth(file, period);


            case "energyMonthly": return importEnergyMonthly(file, period);


            case "energyAuth": return importEnergyAuth(file, period);


            case "cityBus": return importCityBus(file, period);


            case "cityTaxi": return importCityTaxi(file, period);


            case "cityTaxiAuth": return new ImportResult(importCityTaxiAuth(file, period), 0, new ArrayList<>());


            case "investment": return importInvestment(file, period, "客运站场");


            case "investmentLogistics": return importInvestment(file, period, "物流园区");


            case "investmentSystem": return new ImportResult(importInvestmentSystem(file, period), 0, new ArrayList<>());


            case "transportAuth": return new ImportResult(importTransportAuth(file, period), 0, new ArrayList<>());


            case "trackMileage": return new ImportResult(importTrackMileage(file, period), 0, new ArrayList<>());


            case "freightTurnover": return new ImportResult(importFreightTurnover(file, period), 0, new ArrayList<>());


            case "scaleSplit": return new ImportResult(importScaleSplit(file, period), 0, new ArrayList<>());


            case "wycTotal": return new ImportResult(importWycTotal(file, period), 0, new ArrayList<>());


            case "wycOrder": return new ImportResult(importWycOrder(file, period), 0, new ArrayList<>());


            case "wycOrderReport": return new ImportResult(importWycOrderReport(file, period).getCityCount(), 0, new ArrayList<>());


            default: throw new RuntimeException("不支持的导入类型：" + type);


        }


    }




    /** 把服务端文件包装为 MultipartFile，复用各导入方法 */


    public static class ByteArrayMultipartFile implements MultipartFile {


        private final String name;


        private final byte[] content;




        public ByteArrayMultipartFile(String name, byte[] content) {


            this.name = name;


            this.content = content;


        }




        @Override


        public String getName() { return name; }




        @Override


        public String getOriginalFilename() { return name; }




        @Override


        public String getContentType() {


            String lower = name.toLowerCase();


            return lower.endsWith(".xls")


                ? "application/vnd.ms-excel"


                : "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";


        }




        @Override


        public boolean isEmpty() { return content == null || content.length == 0; }




        @Override


        public long getSize() { return content == null ? 0 : content.length; }




        @Override


        public byte[] getBytes() { return content; }




        @Override


        public InputStream getInputStream() { return new ByteArrayInputStream(content); }




        @Override


        public void transferTo(File dest) throws IOException {


            try (FileOutputStream out = new FileOutputStream(dest)) {


                out.write(content);


            }


        }


    }


}