设备导入优化

liding

2025-03-28 9fce552f48c898b61fa1cb9be73aa4ae7dfc5bb4

设备导入优化

 basic-server/src/main/java/com/ruoyi/basic/mapper/LaboratoryMapper.java

@@ -7,6 +7,9 @@
import com.ruoyi.basic.pojo.Laboratory;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Set;

/**
 * å®žéªŒå®¤ç®¡ç†(Laboratory)表数据库访问层
 */
@@ -16,5 +19,6 @@

    Object obtainItemParameterList(@Param("page") Page page, @Param("ew") QueryWrapper<Laboratory> ew);

    List<Laboratory> selectByName(@Param("names") Set<String> names);
}


 basic-server/src/main/resources/mapper/LaboratoryMapper.xml

@@ -39,4 +39,12 @@
                         left join user u1 on l.create_user = u1.id
            ) a
    </select>
    <select id="selectByName" resultType="com.ruoyi.basic.pojo.Laboratory">
        SELECT id, laboratory_name
        FROM laboratory
        WHERE laboratory_name IN
        <foreach collection="names" item="name" open="(" separator="," close=")">
            #{name}
        </foreach>
    </select>
</mapper>

 cnas-device/src/main/java/com/ruoyi/device/controller/DeviceController.java

@@ -3,9 +3,8 @@

import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.ruoyi.basic.excel.StructureTestObjectData;
import com.ruoyi.basic.excel.StructureTestObjectListener;
import com.ruoyi.common.core.domain.Result;
import com.ruoyi.common.utils.LocalDateTimeStringConverters;
import com.ruoyi.device.dto.DataConfigDto;
import com.ruoyi.device.dto.DeviceCollectionDto;
import com.ruoyi.device.dto.DeviceDto;
@@ -16,7 +15,6 @@
import com.ruoyi.device.service.DeviceService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.commons.codec.digest.DigestUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.bind.annotation.*;
@@ -27,7 +25,6 @@
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
@@ -209,7 +206,9 @@
    @PostMapping("/importDevice")
    public Result importDevice(@RequestParam("file") MultipartFile file){
        try {
            EasyExcel.read(file.getInputStream(), Device.class, new DeviceListener(deviceService)).sheet().doRead();
            EasyExcel.read(file.getInputStream(), Device.class, new DeviceListener(deviceService))
                    .registerConverter(new LocalDateTimeStringConverters())
                    .sheet().doRead();
        } catch (IOException e) {
            // è¿™é‡Œå¯ä»¥æ ¹æ®å®žé™…情况进行更完善的错误处理，比如记录日志等
            System.err.println("读取文件时发生错误: " + e.getMessage());

 cnas-device/src/main/java/com/ruoyi/device/dto/DeviceDto.java

@@ -6,7 +6,6 @@
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.time.LocalDateTime;

@Data
@@ -39,5 +38,14 @@
    @ApiModelProperty(value = "下次校准日期")
    private LocalDateTime nextCalibrationDateTwo;

    private String deviceName;
    private String specModel;
    private String manageNumber;

    // å…¨å‚构造函数
    public DeviceDto(String deviceName, String specModel, String manageNumber) {
        this.deviceName = deviceName;
        this.specModel = specModel;
        this.manageNumber = manageNumber;
    }
}

 cnas-device/src/main/java/com/ruoyi/device/excel/upload/DeviceListener.java

@@ -3,12 +3,8 @@

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.ruoyi.basic.excel.StructureTestObjectData;
import com.ruoyi.basic.service.ProductService;
import com.ruoyi.device.pojo.Device;
import com.ruoyi.device.service.DeviceService;
import com.ruoyi.performance.dto.AuxiliaryCorrectionHoursDto;
import com.ruoyi.performance.service.AuxiliaryCorrectionHoursService;

import java.util.ArrayList;
import java.util.List;

 cnas-device/src/main/java/com/ruoyi/device/mapper/DeviceMapper.java

@@ -10,6 +10,7 @@

import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * è®¾å¤‡(Device)表数据库访问层
@@ -36,5 +37,7 @@
    List<Map<String, Object>> treeDevice(@Param("deviceName") String deviceName);

    DeviceDto selectDeviceByCode(Integer id);

    List<Device> selectByKeys(@Param("keys") Set<DeviceDto> keys);
}


 cnas-device/src/main/java/com/ruoyi/device/service/impl/DeviceServiceImpl.java

@@ -1,9 +1,10 @@
package com.ruoyi.device.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONObject;
import com.alibaba.fastjson2.JSON;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
@@ -34,16 +35,14 @@
import com.ruoyi.inspect.mapper.InsSampleMapper;
import com.ruoyi.inspect.pojo.InsProduct;
import com.ruoyi.inspect.util.HackLoopTableRenderPolicy;
import com.ruoyi.performance.dto.AuxiliaryCorrectionHoursDto;
import com.ruoyi.performance.pojo.AuxiliaryCorrectionHours;
import com.ruoyi.system.mapper.SysDictDataMapper;
import com.ruoyi.system.mapper.UserMapper;
import lombok.AllArgsConstructor;
import org.apache.logging.log4j.util.Strings;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@@ -54,7 +53,7 @@
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.regex.Pattern;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
@@ -509,49 +508,113 @@
        }
    }


    //导入设备
    @Override
    public void importExcel(List<Device> list){
        if (CollectionUtil.isEmpty(list)) {
    @Transactional(isolation = Isolation.READ_COMMITTED, rollbackFor = Exception.class)
    public void importExcel(List<Device> batch) {
        if (CollectionUtils.isEmpty(batch)) {
            return;
        }
        List<Device> deviceList = new ArrayList<>();
        List<Device> devices = new ArrayList<>();
        for (Device device : list) {
            Device device1 = deviceMapper.selectOne(Wrappers.<Device>lambdaQuery()
                    .eq(Device::getDeviceName, device.getDeviceName())
                    .eq(Device::getSpecificationModel, device.getSpecificationModel())
                    .eq(Device::getManagementNumber, device.getManagementNumber()));
            //管理人
            if (ObjectUtils.isNotEmpty(device.getEquipmentManagerName())){
                //查对应管理人
                User user = userMapper.selectOne(Wrappers.<User>lambdaQuery()
                        .eq(User::getName, device.getEquipmentManagerName()));
                device.setEquipmentManager(user.getId());
            }
            //所属部门
            if (ObjectUtils.isNotEmpty(device.getSubordinateDepartments())){
                //查对应所属部门
                Laboratory laboratory = laboratoryMapper.selectOne(Wrappers.<Laboratory>lambdaQuery()
                        .eq(Laboratory::getLaboratoryName,device.getSubordinateDepartments()));
                device.setSubordinateDepartmentsId(laboratory.getId());
            }

        // 1. æ‰¹é‡æŸ¥è¯¢å…³è”数据（用户、实验室）
        Map<String, Integer> userMap = queryUserMap(batch);
        Map<String, Integer> labMap = queryLabMap(batch);

        // 2. å†…存处理：转换关联字段并去重
        processBatch(batch, userMap, labMap);

        // 3. æ‰¹é‡æ“ä½œæ•°æ®åº“
        batchOperate(batch);
    }

    private Map<String, Integer> queryUserMap(List<Device> batch) {
        Set<String> managerNames = batch.stream()
                .map(Device::getEquipmentManagerName)
                .filter(Objects::nonNull)
                .collect(Collectors.toSet());
        if (CollUtil.isEmpty(managerNames)) {
            return Collections.emptyMap();
        }
        return userMapper.selectList(
                new LambdaQueryWrapper<User>().in(User::getName, managerNames)
        ).stream().collect(Collectors.toMap(User::getName, User::getId));
    }

    private Map<String, Integer> queryLabMap(List<Device> batch) {
        Set<String> labNames = batch.stream()
                .map(Device::getSubordinateDepartments)
                .filter(Objects::nonNull)
                .collect(Collectors.toSet());
        if (CollUtil.isEmpty(labNames)) {
            return Collections.emptyMap();
        }
        return laboratoryMapper.selectList(
                new LambdaQueryWrapper<Laboratory>().in(Laboratory::getLaboratoryName, labNames)
        ).stream().collect(Collectors.toMap(Laboratory::getLaboratoryName, Laboratory::getId));
    }

    private void processBatch(List<Device> batch, Map<String, Integer> userMap, Map<String, Integer> labMap) {
        // é¢„计算唯一键（设备名称+型号+管理编号）
        Map<String, Device> existDevices = deviceMapper.selectList(
                new LambdaQueryWrapper<Device>()
                        .in(Device::getDeviceName, batch.stream().map(Device::getDeviceName).collect(Collectors.toSet()))
                        .in(Device::getSpecificationModel, batch.stream().map(Device::getSpecificationModel).collect(Collectors.toSet()))
                        .in(Device::getManagementNumber, batch.stream().map(Device::getManagementNumber).collect(Collectors.toSet()))
        ).stream().collect(Collectors.toMap(
                d -> d.getDeviceName() + "|" + d.getSpecificationModel() + "|" + d.getManagementNumber(),
                Function.identity()
        ));

        List<Device> toInsert = new ArrayList<>();
        List<Device> toUpdate = new ArrayList<>();

        for (Device device : batch) {
            // è½¬æ¢å…³è”字段
            device.setEquipmentManager(userMap.get(device.getEquipmentManagerName()));
            device.setSubordinateDepartmentsId(labMap.get(device.getSubordinateDepartments()));

            //设备状态
            if (ObjectUtils.isNotEmpty(device.getDeviceStatusName())){
                //查字典对应的值
                String status = sysDictDataMapper.selectDictValue("device_status", device.getDeviceStatusName());
                device.setDeviceStatus(Integer.parseInt(status));
            }
            if (ObjectUtils.isNotEmpty(device1)) {
                devices.add(device1);

            // ç”Ÿæˆå”¯ä¸€é”®
            String key = device.getDeviceName() + "|" + device.getSpecificationModel() + "|" + device.getManagementNumber();
            if (existDevices.containsKey(key)) {
                Device exist = existDevices.get(key);
                BeanUtils.copyProperties(device, exist, "id");
                toUpdate.add(exist);
            } else {
                deviceList.add(device);
                toInsert.add(device);
            }
        }
        //批量新增
        saveBatch(deviceList);
        //批量修改
        updateBatchById(devices);

        batch.clear();
        batch.addAll(toInsert);
        batch.addAll(toUpdate);
    }

    private void batchOperate(List<Device> batch) {
        // æ‹†åˆ†æ’入和更新列表
        List<Device> toInsert = new ArrayList<>();
        List<Device> toUpdate = new ArrayList<>();
        for (Device device : batch) {
            if (device.getId() == null) {
                toInsert.add(device);
            } else {
                toUpdate.add(device);
            }
        }

        // æ‰¹é‡æ’入（使用MyBatis-Plus的insertBatch，默认batchSize=1000）
        if (CollUtil.isNotEmpty(toInsert)) {
            saveOrUpdateBatch(toInsert);
        }

        // æ‰¹é‡æ›´æ–°ï¼ˆä½¿ç”¨MyBatis-Plus的updateBatchById，默认batchSize=1000）
        if (CollUtil.isNotEmpty(toUpdate)) {
            updateBatchById(toUpdate);
        }
    }
}

 cnas-device/src/main/resources/mapper/DeviceMapper.xml

@@ -181,4 +181,20 @@
                 u1.name,
                 u2.name
    </select>

    <select id="selectByKeys" resultType="com.ruoyi.device.pojo.Device">
        SELECT
        id, device_name, specification_model, management_number,
        manufacturer, factory_no, technical_indicators, acquisition_date,
        activation_date, equipment_manager, storage_point, subordinate_departments_id,
        calibration_services, last_calibration_date, next_calibration_date, large_category,
        unit_price, device_status, calibration_date, image_upload, image_name
        FROM device
        WHERE
        <foreach collection="keys" item="key" separator=" OR " open="(" close=")">
            (device_name = #{key.deviceName}
            AND specification_model = #{key.specModel}
            AND management_number = #{key.manageNumber})
        </foreach>
    </select>
</mapper>

 ruoyi-common/src/main/java/com/ruoyi/common/utils/LocalDateTimeStringConverters.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,84 @@
package com.ruoyi.common.utils;

import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import com.ruoyi.common.exception.base.BaseException;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

public class LocalDateTimeStringConverters implements Converter<LocalDateTime> {

    // æ”¯æŒçš„文本日期格式（按优先级尝试）
    private static final List<DateTimeFormatter> STRING_FORMATTERS = Arrays.asList(
            DateTimeFormatter.ofPattern("yyyy.MM.dd"),
            DateTimeFormatter.ofPattern("yyyy.M.d"),
            DateTimeFormatter.ofPattern("yyyy/MM/dd"),
            DateTimeFormatter.ofPattern("yyyy-MM-dd"),
            DateTimeFormatter.ofPattern("yyyyMMdd")
    );

    @Override
    public Class<LocalDateTime> supportJavaTypeKey() {
        return LocalDateTime.class; // ç›®æ ‡ Java ç±»åž‹
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING; // é»˜è®¤æ”¯æŒçš„ Excel å•å…ƒæ ¼ç±»åž‹
    }

    @Override
    public LocalDateTime convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        // æ£€æŸ¥å•å…ƒæ ¼æ˜¯å¦ä¸ºç©ºæˆ–无效值
        if (cellData == null || cellData.getType() == null ||
                (cellData.getType() == CellDataTypeEnum.EMPTY)) {
            return null;
        }

        // æ ¹æ®å•å…ƒæ ¼ç±»åž‹è¿›è¡Œå¤„理
        switch (cellData.getType()) {
            case STRING: // å­—符串类型
                String dateString = cellData.getStringValue().trim();
                if (dateString.isEmpty() || "/".equals(dateString)) {
                    return null; // ç‰¹æ®Šå€¼å¤„理
                }
                // å°è¯•æ‰€æœ‰æ”¯æŒçš„日期格式
                for (DateTimeFormatter formatter : STRING_FORMATTERS) {
                    try {
                        LocalDate localDate = LocalDate.parse(dateString, formatter);
                        LocalDateTime localDateTime = localDate.atStartOfDay();
                        return localDateTime; // è®¾ç½®é»˜è®¤æ—¶é—´ä¸º 00:00:00
                    } catch (DateTimeParseException ignored) {
                        // å¿½ç•¥å•ä¸ªæ ¼å¼çš„解析失败，继续尝试下一个格式
                    }
                }
                // å¦‚果所有格式均失败，则抛出异常
                throw new BaseException("无法解析字符串日期: " + dateString);

            case NUMBER: // æ•°å€¼ç±»åž‹ï¼ˆExcel æ—¥æœŸæ ¼å¼ï¼‰
                try {
                    double excelNumber = cellData.getNumberValue().doubleValue();
                    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(excelNumber, false);
                    return date.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
                } catch (Exception e) {
                    throw new BaseException("无法解析数值日期: " + cellData.getNumberValue());
                }

            case BOOLEAN: // å¸ƒå°”类型
                throw new BaseException("布尔类型不支持作为日期字段");

            default:
                throw new BaseException("不支持的单元格类型: " + cellData.getType());
        }
    }
}

 ruoyi-system/src/main/java/com/ruoyi/system/mapper/UserMapper.java

@@ -8,6 +8,7 @@

import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * ç”¨æˆ·ä¿¡æ¯è¡¨
@@ -60,5 +61,6 @@
    List<User> selectDepartmentLimsUserList(@Param("userId") Integer userId);


    List<User> selectByNames(@Param("names") Set<String> names);
}


 ruoyi-system/src/main/resources/mapper/system/UserMapper.xml

@@ -94,5 +94,11 @@
                                from user u2
                                where u2.id = #{userId})
    </select>
    <select id="selectByNames" resultType="com.ruoyi.common.core.domain.entity.User">
        SELECT id, name FROM user WHERE name IN
        <foreach item="name" collection="names" open="(" separator="," close=")">
            #{name}
        </foreach>
    </select>

</mapper>