添加导入Excel表数据功能

huang

8 天以前 f13ba9e05f653bc3083c4d17fe8658e67054131e

 mes-processes/mes-plcSend/src/main/java/com/mes/device/service/impl/GlassInfoServiceImpl.java

@@ -1,19 +1,24 @@
package com.mes.device.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.mes.device.entity.GlassInfo;
import com.mes.device.mapper.DeviceGlassInfoMapper;
import com.mes.device.service.GlassInfoService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import static java.util.stream.IntStream.range;

/**
 * 玻璃信息服务实现类
@@ -100,8 +105,35 @@
            GlassInfo existing = baseMapper.selectByGlassId(glassInfo.getGlassId());
            if (existing != null) {
                glassInfo.setId(existing.getId());
                // 保留原始创建信息
                if (glassInfo.getCreatedTime() == null) {
                    glassInfo.setCreatedTime(existing.getCreatedTime());
                }
                if (glassInfo.getCreatedBy() == null) {
                    glassInfo.setCreatedBy(existing.getCreatedBy());
                }
                // 更新为当前时间
                if (glassInfo.getUpdatedTime() == null) {
                    glassInfo.setUpdatedTime(new Date());
                }
                if (glassInfo.getUpdatedBy() == null) {
                    glassInfo.setUpdatedBy("system");
                }
                return updateById(glassInfo);
            } else {
                Date now = new Date();
                if (glassInfo.getCreatedTime() == null) {
                    glassInfo.setCreatedTime(now);
                }
                if (glassInfo.getUpdatedTime() == null) {
                    glassInfo.setUpdatedTime(now);
                }
                if (glassInfo.getCreatedBy() == null) {
                    glassInfo.setCreatedBy("system");
                }
                if (glassInfo.getUpdatedBy() == null) {
                    glassInfo.setUpdatedBy("system");
                }
                return save(glassInfo);
            }
        } catch (Exception e) {
@@ -136,7 +168,7 @@
            Date timeThreshold = new Date(System.currentTimeMillis() - minutes * 60 * 1000L);
            
            LambdaQueryWrapper<GlassInfo> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(GlassInfo::getStatus, GlassInfo.Status.ACTIVE)
            wrapper.eq(GlassInfo::getStatus, GlassInfo.Status.PENDING)
                   .ge(GlassInfo::getCreatedTime, timeThreshold)
                   .orderByDesc(GlassInfo::getCreatedTime)
                   .last("LIMIT " + limit);
@@ -160,5 +192,320 @@
            return Collections.emptyList();
        }
    }

    @Override
    public boolean updateGlassStatus(List<String> glassIds, String status) {
        if (CollectionUtils.isEmpty(glassIds) || status == null) {
            return true;
        }
        try {
            LambdaUpdateWrapper<GlassInfo> wrapper = new LambdaUpdateWrapper<>();
            wrapper.in(GlassInfo::getGlassId, glassIds);
            GlassInfo update = new GlassInfo();
            update.setStatus(status);
            update.setUpdatedTime(new Date());
            update.setUpdatedBy("system");
            return this.update(update, wrapper);
        } catch (Exception e) {
            log.error("批量更新玻璃状态失败, glassIds={}, status={}", glassIds, status, e);
            return false;
        }
    }

    @Override
    public Map<String, Object> buildEngineerImportPayload(List<Map<String, Object>> excelRows) {
        Map<String, Object> result = new HashMap<>();
        if (excelRows == null || excelRows.isEmpty()) {
            return result;
        }

        // 工程号生成：P + yyMMdd + 序号(2位)
        AtomicInteger seq = new AtomicInteger(1);
        final String engineerId = generateEngineerId(firstValue(excelRows, "glassId"), seq.getAndIncrement());
        final String filmsIdDefault = firstValue(excelRows, "filmsId", "白玻");
        final double thicknessDefault = parseDouble(firstValue(excelRows, "thickness"), 0d);

        // glassInfolList
        final String engineerIdFinal = engineerId;
        final String filmsIdDefaultFinal = filmsIdDefault;
        final double thicknessDefaultFinal = thicknessDefault;

        List<Map<String, Object>> glassInfolList = excelRows.stream()
                .flatMap(row -> {
                    int qty = (int) parseDouble(row.getOrDefault("quantity", 1), 1);
                    if (qty <= 0) qty = 1;
                    String glassId = str(row.get("glassId"));
                    Integer orderNumber = Integer.parseInt(str(row.get("orderNumber")));
                    String filmsId = strOrDefault(row.get("filmsId"), filmsIdDefaultFinal);
                    String flowCardId = str(row.get("flowCardId"));
                    String productName = str(row.get("productName"));
                    String customerName = str(row.get("customerName"));
                    double width = parseDouble(row.get("width"), 0d);
                    double height = parseDouble(row.get("height"), 0d);
                    double thickness = parseDouble(row.get("thickness"), thicknessDefaultFinal);

                    int finalQty = qty;
                    return range(0, qty).mapToObj(idx -> {
                        String finalGlassId = finalQty > 1 ? glassId + "_" + (idx + 1) : glassId;
                        String finalFlowCardId = flowCardId.isEmpty() ? finalGlassId : flowCardId;
                        Map<String, Object> m = new HashMap<>();
                        m.put("xAxis", 0);
                        m.put("xCoordinate", 0);
                        m.put("yAxis", 0);
                        m.put("yCoordinate", 0);
                        m.put("glassId", finalGlassId);
                        m.put("engineerId", engineerIdFinal);
                        m.put("flowCardId", finalFlowCardId);
                        m.put("orderNumber", orderNumber);
                        m.put("productSortNumber", idx + 1);
                        m.put("hollowCombineDirection", "0");
                        m.put("width", width);
                        m.put("height", height);
                        m.put("thickness", thickness);
                        m.put("filmsId", filmsId);
                        m.put("layer", 0);
                        m.put("totalLayer", 0);
                        m.put("edgWidth", width);
                        m.put("edgHeight", height);
                        m.put("isMultiple", 0);
                        m.put("maxWidth", width);
                        m.put("maxHeight", height);
                        m.put("isHorizontal", 0);
                        m.put("rawSequence", 0);
                        m.put("temperingLayoutId", 0);
                        m.put("temperingFeedSequence", 0);
                        m.put("angle", 0);
                        m.put("ruleId", 0);
                        m.put("combine", 0);
                        m.put("markIcon", "");
                        m.put("filmRemove", 0);
                        m.put("flowCardSequence", String.valueOf(idx + 1));
                        m.put("process", "");
                        m.put("rawAngle", 0);
                        m.put("graphNo", 0);
                        m.put("processParam", "");
                        return m;
                    });
                })
                .collect(Collectors.toList());

        // 原片信息去重
        Map<String, Map<String, Object>> rawGlassMap = new HashMap<>();
        for (Map<String, Object> row : excelRows) {
            double width = parseDouble(row.get("width"), 0d);
            double height = parseDouble(row.get("height"), 0d);
            double thickness = parseDouble(row.get("thickness"), thicknessDefaultFinal);
            String filmsId = strOrDefault(row.get("filmsId"), filmsIdDefaultFinal);
            String key = width + "_" + height + "_" + thickness + "_" + filmsId;
            if (!rawGlassMap.containsKey(key)) {
                Map<String, Object> m = new HashMap<>();
                m.put("engineeringId", engineerIdFinal);
                m.put("filmsId", filmsId);
                m.put("rawGlassWidth", width);
                m.put("rawGlassHeight", height);
                m.put("rawGlassThickness", thickness);
                m.put("rawSequence", rawGlassMap.size() + 1);
                m.put("usageRate", "0.95");
                rawGlassMap.put(key, m);
            }
        }

        List<Map<String, Object>> engineeringRawQueueList = rawGlassMap.values().stream().collect(Collectors.toList());

        // 流程卡信息
        Map<String, Map<String, Object>> flowCardMap = new HashMap<>();
        for (Map<String, Object> row : excelRows) {
            String glassId = str(row.get("glassId"));
            String flowCardId = str(row.get("flowCardId"));
            if (flowCardId.isEmpty()) {
                flowCardId = glassId;
            }
            double width = parseDouble(row.get("width"), 0d);
            double height = parseDouble(row.get("height"), 0d);
            double thickness = parseDouble(row.get("thickness"), thicknessDefaultFinal);
            String filmsId = strOrDefault(row.get("filmsId"), filmsIdDefaultFinal);
            Integer orderNumber = Integer.parseInt(str(row.get("orderNumber")));
            String productName = str(row.get("productName"));
            String customerName = str(row.get("customerName"));

            Map<String, Object> exist = flowCardMap.get(flowCardId);
            if (exist == null) {
                Map<String, Object> m = new HashMap<>();
                m.put("flowCardId", flowCardId);
                m.put("width", width);
                m.put("height", height);
                m.put("thickness", thickness);
                m.put("filmsId", filmsId);
                m.put("totalLayer", 0);
                m.put("layer", 0);
                m.put("glassTotal", 1);
                m.put("orderNumber", orderNumber);
                m.put("productName", productName);
                m.put("customerName", customerName);
                flowCardMap.put(flowCardId, m);
            } else {
                int count = (int) exist.getOrDefault("glassTotal", 1);
                exist.put("glassTotal", count + 1);
            }
        }
        List<Map<String, Object>> flowCardInfoList = flowCardMap.values().stream().collect(Collectors.toList());

        // 汇总
        int glassTotal = glassInfolList.size();
        double glassTotalArea = glassInfolList.stream()
                .mapToDouble(m -> parseDouble(m.get("width"), 0d) * parseDouble(m.get("height"), 0d) / 1_000_000d)
                .sum();
        double patternArea = engineeringRawQueueList.stream()
                .mapToDouble(m -> parseDouble(m.get("rawGlassWidth"), 0d) * parseDouble(m.get("rawGlassHeight"), 0d) / 1_000_000d)
                .sum();

        result.put("engineerId", engineerIdFinal);
        result.put("engineerName", "工程_" + engineerIdFinal);
        result.put("avgAvailability", "90");
        result.put("validAvailability", "90");
        result.put("lastAvailability", "90");
        result.put("glassTotal", glassTotal);
        result.put("glassTotalArea", round2(glassTotalArea));
        result.put("planPatternTotal", engineeringRawQueueList.size());
        result.put("planPatternTotalArea", round2(patternArea));
        result.put("realityPatternTotal", engineeringRawQueueList.size());
        result.put("realityPatternTotalArea", round2(patternArea));
        result.put("filmsId", filmsIdDefaultFinal);
        result.put("thickness", thicknessDefaultFinal);
        result.put("engineeringRawQueueList", engineeringRawQueueList);
        result.put("glassInfolList", glassInfolList);
        result.put("flowCardInfoList", flowCardInfoList);
        result.put("hollowFormulaDetailsList", null);
        result.put("temperingParameterList", null);

        return result;
    }

    // 日期格式化器（线程不安全，使用ThreadLocal保证线程安全）
    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyMMdd");

    // 数字匹配正则（预编译提升性能）
    private static final Pattern DIGIT_PATTERN = Pattern.compile("\\d+");

    /**
     * 生成工程师ID
     * 格式规则：P + 年月日(yyMMdd) + 两位序号
     * 序号优先从glassId中提取末尾两位数字，否则使用传入的index补零
     *
     * @param glassId 玻璃ID（可为null，用于提取数字序号）
     * @param index   备用序号（当glassId无有效数字时使用）
     * @return 格式化的工程师ID（如：P25010801）
     */
    private String generateEngineerId(Object glassId, int index) {
        // 1. 生成日期前缀（yyMMdd）
        String base = LocalDate.now().format(DATE_FORMATTER);

        // 2. 初始化序号（两位补零）
        String seq = String.format("%02d", index);

        // 3. 从glassId中提取末尾两位数字（覆盖默认序号）
        if (glassId != null) {
            String glassIdStr = glassId.toString();
            Matcher matcher = DIGIT_PATTERN.matcher(glassIdStr);
            String lastDigitStr = null;

            // 遍历匹配所有数字段，取最后一个
            while (matcher.find()) {
                lastDigitStr = matcher.group();
            }

            // 若数字段长度≥2，取最后两位；否则保留原序号
            if (lastDigitStr != null && lastDigitStr.length() >= 2) {
                seq = lastDigitStr.substring(lastDigitStr.length() - 2);
            }
        }

        return "P" + base + seq;
    }

    /**
     * 提取List中第一个Map的指定key值（默认空字符串）
     *
     * @param rows 数据行列表（可为null/空）
     * @param key  要提取的键
     * @return 第一个Map的key对应值（空则返回""）
     */
    private String firstValue(List<Map<String, Object>> rows, String key) {
        return firstValue(rows, key, "");
    }

    /**
     * 提取List中第一个Map的指定key值（自定义默认值）
     *
     * @param rows       数据行列表（可为null/空）
     * @param key        要提取的键
     * @param defaultVal 空值时的默认返回值
     * @return 第一个Map的key对应值（空则返回defaultVal）
     */
    private String firstValue(List<Map<String, Object>> rows, String key, String defaultVal) {
        if (rows == null || rows.isEmpty() || key == null) {
            return defaultVal;
        }

        Map<String, Object> firstRow = rows.get(0);
        Object value = firstRow.get(key);
        return value == null ? defaultVal : value.toString();
    }

    /**
     * 对象转字符串（null转空串，自动去除首尾空格）
     *
     * @param v 待转换对象
     * @return 处理后的字符串
     */
    private String str(Object v) {
        return v == null ? "" : v.toString().trim();
    }

    /**
     * 对象转字符串（空串时返回默认值，自动去除首尾空格）
     *
     * @param v   待转换对象
     * @param def 空值默认值
     * @return 处理后的字符串
     */
    private String strOrDefault(Object v, String def) {
        String result = str(v);
        return result.isEmpty() ? def : result;
    }

    /**
     * 解析对象为double（失败/空值返回默认值）
     *
     * @param v   待解析对象（支持数字/字符串类型）
     * @param def 解析失败时的默认值
     * @return 解析后的double值
     */
    private double parseDouble(Object v, double def) {
        if (v == null) {
            return def;
        }

        try {
            if (v instanceof Number) {
                return ((Number) v).doubleValue();
            }
            return Double.parseDouble(v.toString().trim());
        } catch (NumberFormatException e) {
            // 仅捕获数字格式化异常，避免吞掉其他异常
            return def;
        }
    }

    /**
     * 保留两位小数（四舍五入）
     *
     * @param v 原始数值
     * @return 保留两位小数后的数值
     */
    private double round2(double v) {
        return Math.round(v * 100.0) / 100.0;
    }

}