保养规范点检导入

lyh

2025-07-05 ec8a22b7ea91d9e845216ef573d0436aaa9993ef

 lxzn-module-eam/src/main/java/org/jeecg/modules/eam/service/impl/EamMaintenanceStandardServiceImpl.java

@@ -9,16 +9,12 @@
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.jeecg.weibo.exception.BusinessException;
import org.apache.commons.lang3.StringUtils;
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.ss.usermodel.*;
import org.apache.shiro.SecurityUtils;
import org.flowable.engine.TaskService;
import org.flowable.task.api.Task;
import org.jeecg.common.api.vo.FileUploadResult;
import org.jeecg.common.api.vo.Result;
import org.jeecg.common.constant.CommonConstant;
import org.jeecg.common.exception.JeecgBootException;
@@ -37,7 +33,7 @@
import org.jeecg.modules.eam.service.IEamEquipmentService;
import org.jeecg.modules.eam.service.IEamMaintenanceStandardDetailService;
import org.jeecg.modules.eam.service.IEamMaintenanceStandardService;
import org.jeecg.modules.eam.vo.EamMaintenanceStandardVo;
import org.jeecg.modules.eam.vo.*;
import org.jeecg.modules.flowable.apithird.business.entity.FlowMyBusiness;
import org.jeecg.modules.flowable.apithird.business.service.IFlowMyBusinessService;
import org.jeecg.modules.flowable.apithird.service.FlowCallBackServiceI;
@@ -53,6 +49,8 @@
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@@ -524,132 +522,369 @@

    /*导入文件--------------------------开始*/
    /**
     * 导入点检表数据
     * @param file Excel文件
     * @return 导入结果
     */
//    public Result<?> importInspectionData(MultipartFile file) {
//        int successCount = 0;
//
//        try (Workbook workbook = WorkbookFactory.create(file.getInputStream())) {
//            Sheet sheet = workbook.getSheetAt(0);
//            // 1. 解析表头信息
//            Map<String, String> header = parseHeader(sheet, errors);
//
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//
//        return new ImportResult(successCount, errors);
//    }
        * 点检表导入入口
    */
    @Override
    @Transactional(rollbackFor = Exception.class)
    public Result<?> importPointInspectionExcel(MultipartFile file) {
        try (Workbook workbook = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = workbook.getSheetAt(0);

            // 提取名称
            String name = extractInspectionTitle(file) + "点检表";

            // 1. 提取设备信息
            EamMaintenanceStandard eamMaintenanceStandard = extractDeviceInfo(sheet);
            if (eamMaintenanceStandard == null) {
                throw new BusinessException("设备信息提取失败");
            }

            eamMaintenanceStandard.setStandardName(name);
            eamMaintenanceStandardMapper.insert(eamMaintenanceStandard);

            // 2. 提取每日点检项目
            List<EamMaintenanceStandardDetail> dailyDetails = extractDailyItems(sheet, eamMaintenanceStandard);

            // 3. 提取周保养项目
            List<EamMaintenanceStandardDetail> weeklyDetails = extractWeeklyItems(sheet, eamMaintenanceStandard);

            // 合并并保存所有项目
            List<EamMaintenanceStandardDetail> allDetails = new ArrayList<>();
            allDetails.addAll(dailyDetails);
            allDetails.addAll(weeklyDetails);

            if (!CollectionUtils.isEmpty(allDetails)) {
                eamMaintenanceStandardDetailService.saveBatch(allDetails);
            }

            return Result.OK("点检表导入成功");

        } catch (Exception e) {
            throw new BusinessException("点检表导入失败: " + e.getMessage());
        }
    }

    /**
     * 解析表头信息
        * 提取点检表标题
    */
    private String extractInspectionTitle(MultipartFile file) {
        try (Workbook workbook = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = workbook.getSheetAt(0);
            Row firstRow = sheet.getRow(0);

            if (firstRow == null) {
                return "未找到标题";
            }

            Cell firstCell = firstRow.getCell(0);
            if (firstCell == null) {
                return "";
            }

            String title = getCellStringValue(firstCell);
            return extractTextBeforeInspection(title);

        } catch (Exception e) {
            return "";
        }
    }

    /**
        * 提取点检表之前的文字
     */
//    private Map<String, String> parseHeader(Sheet sheet, List<ImportError> errors) {
//        Map<String, String> header = new HashMap<>();
//        try {
//            Row headerRow = sheet.getRow(0);
//            // 示例：第一行：设备名称:割炬  型号:gta001  统一编号:xc001
//            String cellValue = getCellStringValue(headerRow.getCell(0));
//
//            // 使用正则表达式解析关键信息
//            Pattern pattern = Pattern.compile("设备名称[:：](\\S+)\\s+型号[:：](\\S+)\\s+统一编号[:：](\\S+)\\s+日期[:：](\\S+)\\s+单位[:：](\\S+)");
//            Matcher matcher = pattern.matcher(cellValue);
//
//            if (matcher.find()) {
//                header.put("deviceName", matcher.group(1));
//                header.put("deviceModel", matcher.group(2));
//                header.put("deviceCode", matcher.group(3));
//                header.put("inspectionMonth", matcher.group(4));
//                header.put("unit", matcher.group(5));
//            } else {
//                errors.add(new ImportError("表头格式错误", cellValue, 1));
//            }
//        } catch (Exception e) {
//            errors.add(new ImportError("解析表头异常", e.getMessage(), 1));
//        }
//        return header;
//    }
//
//    /**
//     * 解析每日点检项目
//     */
//    private List<InspectionItem> parseDailyItems(Sheet sheet, List<ImportError> errors) {
//        List<InspectionItem> items = new ArrayList<>();
//
//        // 点检项目从第3行开始（序号从1开始）
//        int startRow = 2;
//        int rowNum = startRow;
//
//        try {
//            // 找到每日点检表的结束位置（维护责任人签字）
//            while (rowNum <= sheet.getLastRowNum()) {
//                Row row = sheet.getRow(rowNum);
//                if (row == null) {
//                    rowNum++;
//                    continue;
//                }
//
//                // 判断是否到达结束位置
//                String sequenceValue = getCellStringValue(row.getCell(0));
//                if ("9".equals(sequenceValue) || "维护责任人签字".contains(sequenceValue)) {
//                    break;
//                }
//
//                // 解析项目行
//                InspectionItem item = new InspectionItem();
//                item.setSequence(Integer.parseInt(sequenceValue));
//                item.setProjectName(getCellStringValue(row.getCell(1)));
//                item.setRequirement(getCellStringValue(row.getCell(2)));
//
//                items.add(item);
//                rowNum++;
//            }
//        } catch (Exception e) {
//            errors.add(new ImportError("解析每日项目异常", e.getMessage(), rowNum + 1));
//        }
//
//        return items;
//    }
//
//    /**
//    * 解析周保养项目
//     */
//    private List<InspectionItem> parseWeeklyItems(Sheet sheet, List<ImportError> errors) {
//        List<InspectionItem> items = new ArrayList<>();
//
//        try {
//            // 定位周保养标题行（行号11）
//            int startRow = 10;
//
//            // 从第12行开始是周保养项目
//            for (int rowNum = startRow + 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
//                Row row = sheet.getRow(rowNum);
//                if (row == null) continue;
//
//                String sequenceValue = getCellStringValue(row.getCell(0));
//
//                // 遇到结束标志则停止解析
//                if ("周保养操作者执行".contains(sequenceValue)) break;
//
//                try {
//                    InspectionItem item = new InspectionItem();
//                    item.setSequence(Integer.parseInt(sequenceValue));
//                    item.setProjectName(getCellStringValue(row.getCell(1)));
//                    item.setStandard(getCellStringValue(row.getCell(2)));
//                    item.setRequirement(getCellStringValue(row.getCell(3))); // 执行记录
//
//                    items.add(item);
//                } catch (Exception e) {
//                    errors.add(new ImportError("解析周保养项目异常", e.getMessage(), rowNum + 1));
//                }
//            }
//        } catch (Exception e) {
//            errors.add(new ImportError("定位周保养项目异常", e.getMessage(), 0));
//        }
//
//        return items;
//    }
    private String extractTextBeforeInspection(String title) {
        if (StringUtils.isBlank(title)) {
            return "";
        }

        int index = title.indexOf("点检表");
        return index > 0 ? title.substring(0, index).trim() : title;
    }

    /**
        * 提取设备信息
    */
    private EamMaintenanceStandard extractDeviceInfo(Sheet sheet) {
        Row headerRow = sheet.getRow(0);
        if (headerRow == null) {
            return null;
        }

        String headerText = getCellStringValue(headerRow.getCell(0));
        if (StringUtils.isBlank(headerText)) {
            return null;
        }

        EamMaintenanceStandard standard = new EamMaintenanceStandard();
        standard.setEquipmentName(extractField(headerText, "设备名称[:：]\\s*(\\S+)"));
        standard.setEquipmentId(extractField(headerText, "设备型号[:：]\\s*(\\S+)"));
        standard.setStandardCode(extractField(headerText, "统一编号[:：]\\s*(\\S+)"));

        // 日期处理
        String dateStr = extractField(headerText, "日期[:：]\\s*(\\S+)");
        if (StringUtils.isNotBlank(dateStr)) {
            try {
                // 支持多种日期格式
                Date date = parseDate(dateStr);
                standard.setDesignTime(date);
                standard.setInitialDate(date);
            } catch (ParseException ignored) {

            }
        }

        standard.setMaintenanceCategory("POINT_INSPECTION");
        standard.setPeriodUnit("天");
        standard.setStandardStatus(MaintenanceStandardStatusEnum.START.name());
        standard.setStandardVersion("v" + CommonConstant.OPERATE_TYPE_1);
        standard.setDelFlag(0);

        return standard;
    }

    /**
        * 解析日期字符串
     */
    private Date parseDate(String dateStr) throws ParseException {
        // 尝试多种日期格式
        String[] patterns = {
                "yyyy年MM月",
                "yyyy-MM",
                "yyyy/MM",
                "yyyyMM",
                "yyyy年M月"
        };

        for (String pattern : patterns) {
            try {
                SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                sdf.setLenient(false);
                return sdf.parse(dateStr);
            } catch (ParseException e) {
                // 尝试下一个格式
            }
        }

        throw new ParseException("无法解析日期: " + dateStr, 0);
    }

    /**
        * 提取每日点检项目
    */
    private List<EamMaintenanceStandardDetail> extractDailyItems(Sheet sheet, EamMaintenanceStandard standard) {
        return extractItems(sheet, standard, "点检项目", "完成数据/要求", "DAY_INSPECTION");
    }

    /**
        * 提取周保养项目
    */
    private List<EamMaintenanceStandardDetail> extractWeeklyItems(Sheet sheet, EamMaintenanceStandard standard) {
        return extractItems(sheet, standard, "周保养项目", "检查标准", "WEEK_INSPECTION");
    }

    /**
        * 通用项目提取方法
    */
    private List<EamMaintenanceStandardDetail> extractItems(Sheet sheet, EamMaintenanceStandard standard,
                                                            String primaryHeader, String secondaryHeader,
                                                            String itemCategory) {
        int[] section = findTableSection(sheet, primaryHeader, secondaryHeader);
        if (section == null) {
            return Collections.emptyList();
        }

        List<EamMaintenanceStandardDetail> details = new ArrayList<>();
        for (int rowIdx = section[0]; rowIdx <= section[1]; rowIdx++) {
            Row row = sheet.getRow(rowIdx);
            if (row == null || isEmptyRow(row)) {
                continue;
            }

            // 确保第一列是序号（数字）
            Cell seqCell = row.getCell(0);
            if (seqCell == null || seqCell.getCellType() != CellType.NUMERIC) {
                continue;
            }

            // 创建项目详情
            EamMaintenanceStandardDetail detail = new EamMaintenanceStandardDetail();
            detail.setStandardId(standard.getId());
            detail.setItemName(getCellStringValue(row.getCell(1)));
            detail.setItemCategory(itemCategory);

            // 根据项目类型设置需求字段
            if ("DAY_INSPECTION".equals(itemCategory)) {
                detail.setItemDemand(getCellStringValue(row.getCell(2)));
            } else if ("WEEK_INSPECTION".equals(itemCategory)) {
                detail.setItemDemand(getCellStringValue(row.getCell(2)));
            }

            details.add(detail);
        }

        return details;
    }

    /**
        * 查找表格区域
     */
    private int[] findTableSection(Sheet sheet, String primaryHeader, String secondaryHeader) {
        for (int rowIdx = 0; rowIdx <= sheet.getLastRowNum(); rowIdx++) {
            Row row = sheet.getRow(rowIdx);
            if (row == null) continue;

            if (isHeaderRow(row, primaryHeader, secondaryHeader)) {
                int startRow = rowIdx + 1;
                int endRow = findDataEnd(sheet, startRow);
                return new int[]{startRow, endRow};
            }
        }
        return null;
    }

    /**
        * 检查是否为表头行
    */
    private boolean isHeaderRow(Row row, String header1, String header2) {
        boolean foundHeader1 = false;
        boolean foundHeader2 = false;

        for (int colIdx = 0; colIdx < row.getLastCellNum(); colIdx++) {
            Cell cell = row.getCell(colIdx);
            if (cell == null) continue;

            String cellValue = getCellStringValue(cell);
            if (cellValue.contains(header1)) foundHeader1 = true;
            if (cellValue.contains(header2)) foundHeader2 = true;
        }

        return foundHeader1 && foundHeader2;
    }

    /**
        * 查找数据结束位置
     */
    private int findDataEnd(Sheet sheet, int startRow) {
        for (int rowIdx = startRow; rowIdx <= sheet.getLastRowNum(); rowIdx++) {
            Row row = sheet.getRow(rowIdx);
            if (row == null) return rowIdx - 1;

            // 检查是否结束标志行（如签字行）
            if (isSignatureRow(row)) {
                return rowIdx - 1;
            }

            // 检查是否新的表头开始
            if (isNewHeaderStart(row)) {
                return rowIdx - 1;
            }
        }
        return sheet.getLastRowNum();
    }

    /**
        * 识别签字行特征
    */
    private boolean isSignatureRow(Row row) {
        for (int colIdx = 0; colIdx < row.getLastCellNum(); colIdx++) {
            Cell cell = row.getCell(colIdx);
            if (cell == null) continue;

            String value = getCellStringValue(cell);
            if (value.contains("签字") || value.contains("责任人") ||
                    value.contains("执行") || value.contains("确认")) {
                return true;
            }
        }
        return false;
    }

    /**
        * 识别新表头开始
    */
    private boolean isNewHeaderStart(Row row) {
        for (int colIdx = 0; colIdx < row.getLastCellNum(); colIdx++) {
            Cell cell = row.getCell(colIdx);
            if (cell == null) continue;

            String value = getCellStringValue(cell);
            if ("序号".equals(value) || "点检项目".equals(value) || "周保养项目".equals(value)) {
                return true;
            }
        }
        return false;
    }

    /**
        * 检查行是否为空
    */
    private boolean isEmptyRow(Row row) {
        if (row == null) return true;
        for (int colIdx = 0; colIdx < row.getLastCellNum(); colIdx++) {
            Cell cell = row.getCell(colIdx);
            if (cell != null && cell.getCellType() != CellType.BLANK) {
                String value = getCellStringValue(cell);
                if (StringUtils.isNotBlank(value)) {
                    return false;
                }
            }
        }
        return true;
    }

    /**
        * 使用正则提取字段
    */
    private String extractField(String text, String regex) {
        if (StringUtils.isBlank(text)) return "";

        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(text);
        return matcher.find() ? matcher.group(1) : "";
    }

    /**
        * 获取单元格字符串值
    */
    private String getCellStringValue(Cell cell) {
        if (cell == null) return "";

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return new SimpleDateFormat("yyyy年MM月").format(cell.getDateCellValue());
                }
                return String.valueOf((int) cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return handleFormulaCell(cell);
            default:
                return "";
        }
    }

    /**
        * 处理公式单元格
    */
    private String handleFormulaCell(Cell cell) {
        try {
            FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
            CellValue cellValue = evaluator.evaluate(cell);

            if (cellValue == null) return "";

            switch (cellValue.getCellType()) {
                case STRING: return cellValue.getStringValue();
                case NUMERIC: return String.valueOf((int) cellValue.getNumberValue());
                case BOOLEAN: return String.valueOf(cellValue.getBooleanValue());
                default: return "";
            }
        } catch (Exception e) {
            return "";
        }
    }
    /*导入文件--------------------------结束*/
}