| | |
|---|
| | | |
|---|
| | | 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 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; |
|---|
| | |
|---|
| | | private FreightTurnoverImportMapper freightTurnoverMapper; |
|---|
| | | @Resource |
|---|
| | | private AuditResultMapper auditResultMapper; |
|---|
| | | @Resource |
|---|
| | | private AuditRunMapper auditRunMapper; |
|---|
| | | @Resource |
|---|
| | | private PassengerEnterpriseMonthlyMapper passengerMapper; |
|---|
| | | @Resource |
|---|
| | |
|---|
| | | } |
|---|
| | | } |
|---|
| | | |
|---|
| | | /** 公路旅客运政车辆数（独立模板：企业代码/企业名称/车辆数/载客位数） */ |
|---|
| | | /** 公路旅客运政车辆数（兼容两种表头：① 企业代码/企业名称/车辆数/载客位数；② 运政库导出 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) { |
|---|
| | |
|---|
| | | 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，请确认是否选错了数据类型"); |
|---|
| | |
|---|
| | | 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行子表头应含「周转量」或「货运量」（扩展布局） |
|---|
| | |
|---|
| | | } |
|---|
| | | } |
|---|
| | | |
|---|
| | | /** 清空指定报表期全部已导入数据与审核结果（含导入批次、审核通过标记），用于整月重导；投资项目主档保留 */ |
|---|
| | | @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<>(); |
|---|
| | |
|---|
| | | return colMap; |
|---|
| | | } |
|---|
| | | |
|---|
| | | /** 兼容表头映射：去引号/去空白；纯英文表头统一大写（如 'OWNERNAME' → OWNERNAME），便于新旧模板混用 */ |
|---|
| | | private Map<String, Integer> buildHeaderMapCompat(Row header) { |
|---|
| | | Map<String, Integer> colMap = new HashMap<>(); |
|---|
| | | if (header == null) return colMap; |
|---|
| | | for (Cell cell : header) { |
|---|
| | | if (cell == null) continue; |
|---|
| | | String h = FORMATTER.formatCellValue(cell).replace("'", "").replace("\"", "").trim(); |
|---|
| | | if (h.isEmpty()) continue; |
|---|
| | | boolean ascii = true; |
|---|
| | | for (int i = 0; i < h.length(); i++) { |
|---|
| | | if (h.charAt(i) > 127) { ascii = false; break; } |
|---|
| | | } |
|---|
| | | if (ascii) h = h.toUpperCase(); |
|---|
| | | if (!colMap.containsKey(h)) colMap.put(h, cell.getColumnIndex()); |
|---|
| | | } |
|---|
| | | return colMap; |
|---|
| | | } |
|---|
| | | |
|---|
| | | /** 按别名顺序取第一个存在的列号，全部缺失返回 null */ |
|---|
| | | private Integer firstColIdx(Map<String, Integer> colMap, String... aliases) { |
|---|
| | | for (String a : aliases) { |
|---|
| | | Integer idx = colMap.get(a); |
|---|
| | | if (idx != null) return idx; |
|---|
| | | } |
|---|
| | | return null; |
|---|
| | | } |
|---|
| | | |
|---|
| | | private int colIdx(Map<String, Integer> colMap, String header) { |
|---|
| | | Integer idx = colMap.get(header); |
|---|
| | | return idx == null ? -1 : idx; |
|---|
