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


@@ -2,7 +2,9 @@




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;


@@ -18,6 +20,8 @@


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;


@@ -36,6 +40,8 @@


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;


@@ -52,6 +58,7 @@


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


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;


@@ -139,6 +146,8 @@


    @Resource


    private AuditResultMapper auditResultMapper;


    @Resource


    private AuditRunMapper auditRunMapper;


    @Resource


    private PassengerEnterpriseMonthlyMapper passengerMapper;


    @Resource


    private PassengerIndividualMonthlyMapper passengerIndividualMapper;


@@ -162,6 +171,10 @@


    private CityTaxiMonthlyMapper cityTaxiMapper;


    @Resource


    private CityTaxiAuthMapper cityTaxiAuthMapper;


    @Resource


    private WycTotalMonthlyMapper wycTotalMapper;


    @Resource


    private WycOrderMonthlyMapper wycOrderMapper;


    @Resource


    private SqlSessionFactory sqlSessionFactory;




@@ -550,24 +563,30 @@


        }


    }




    /** 公路旅客运政车辆数（独立模板：企业代码/企业名称/车辆数/载客位数） */


    /** 公路旅客运政车辆数（兼容两种表头：① 企业代码/企业名称/车辆数/载客位数；② 运政库导出 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 = buildHeaderMap(sheet.getRow(0));


            requireHeaderCols(sheet, "旅客运政车辆数", "企业名称", "车辆数", "载客位数");


            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 = gs(row, colMap, "企业名称");


                String name = getString(row, nameCol);


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


                PassengerAuthVehicle auth = new PassengerAuthVehicle();


                auth.setReportPeriod(period);


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


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


                auth.setEnterpriseName(name.trim());


                auth.setVehicleCount(gi(row, colMap, "车辆数"));


                auth.setSeatCount(gi(row, colMap, "载客位数"));


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


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


                authList.add(auth);


            }


        } catch (Exception e) {


@@ -916,10 +935,11 @@


        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("累计"))) { hasPeriodSheet = true; break; }


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


            }


            if (!hasPeriodSheet) {


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


@@ -928,8 +948,16 @@


                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行子表头应含「周转量」或「货运量」（扩展布局）


@@ -1289,6 +1317,64 @@


        }


    }




    /** 清空指定报表期全部已导入数据与审核结果（含导入批次、审核通过标记），用于整月重导；投资项目主档保留 */


    @Transactional(rollbackFor = Exception.class)


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


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


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


        }


        int dataRows = 0;


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


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


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


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


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


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


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


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


        stats.put("period", period);


        stats.put("dataRows", dataRows);


        stats.put("auditRows", auditRows);


        stats.put("auditRunRows", auditRunRows);


        stats.put("batchRows", batchRows);


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


            period, dataRows, auditRows, auditRunRows, batchRows);


        return stats;


    }




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


    private void deleteRuleResults(String reportType, String period) {


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


@@ -1377,6 +1463,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) {


@@ -2704,6 +2817,155 @@


        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;


    }




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


@@ -3040,6 +3302,8 @@


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


            default: throw new RuntimeException("不支持的导入类型：" + type);


        }


    }