feat(device): 1.添加设备导入导出功能并优化数据库关联查询 2.计量溯源性-标准物质清单导入导出

liding

2026-08-28 de775139ac660c9d2e38ae54364f25e6724ca668

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

@@ -2,6 +2,7 @@

import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONObject;
import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson2.JSON;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
@@ -15,9 +16,12 @@
import com.ruoyi.basic.mapper.StructureItemParameterMapper;
import com.ruoyi.basic.pojo.StructureItemParameter;
import com.ruoyi.common.core.domain.Result;
import com.ruoyi.common.core.domain.entity.SysDictData;
import com.ruoyi.common.core.domain.entity.SysDept;
import com.ruoyi.common.core.domain.entity.User;
import com.ruoyi.common.utils.QueryWrappers;
import com.ruoyi.device.dto.*;
import com.ruoyi.device.excel.upload.DeviceImport;
import com.ruoyi.device.mapper.CollectBridgeMapper;
import com.ruoyi.device.mapper.DeviceMaintenanceMapper;
import com.ruoyi.device.mapper.DeviceMapper;
@@ -31,6 +35,8 @@
import com.ruoyi.inspect.mapper.InsSampleMapper;
import com.ruoyi.inspect.pojo.InsProduct;
import com.ruoyi.inspect.util.HackLoopTableRenderPolicy;
import com.ruoyi.system.mapper.SysDictDataMapper;
import com.ruoyi.system.mapper.SysDeptMapper;
import com.ruoyi.system.mapper.UserMapper;
import lombok.AllArgsConstructor;
import org.apache.logging.log4j.util.Strings;
@@ -42,6 +48,7 @@
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
@@ -60,6 +67,10 @@
    private DeviceMapper deviceMapper;

    private UserMapper userMapper;

    private SysDeptMapper sysDeptMapper;

    private SysDictDataMapper sysDictDataMapper;

    private StructureItemParameterMapper structureItemParameterMapper;

@@ -495,4 +506,179 @@
            throw new RuntimeException("导出失败");
        }
    }

    @Override
    public void downloadImportTemplate(HttpServletResponse response) {
        response.reset();
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        try {
            String fileName = URLEncoder.encode("设备导入模板.xlsx", "UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            EasyExcel.write(response.getOutputStream(), DeviceImport.class)
                    .sheet("设备导入模板")
                    .doWrite(Collections.emptyList());
        } catch (Exception e) {
            throw new RuntimeException("设备导入模板下载失败", e);
        }
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public int importDevice(org.springframework.web.multipart.MultipartFile file) {
        if (file == null || file.isEmpty()) {
            throw new RuntimeException("请选择需要导入的Excel文件");
        }
        String fileName = file.getOriginalFilename();
        if (fileName == null || !fileName.toLowerCase(Locale.ROOT).endsWith(".xlsx")) {
            throw new RuntimeException("仅支持.xlsx格式文件");
        }

        final List<DeviceImport> rows;
        try {
            rows = EasyExcel.read(file.getInputStream())
                    .head(DeviceImport.class)
                    .sheet()
                    .doReadSync();
        } catch (Exception e) {
            throw new RuntimeException("Excel解析失败，请使用下载的导入模板", e);
        }
        if (CollectionUtils.isEmpty(rows)) {
            throw new RuntimeException("导入文件没有设备数据");
        }

        Set<String> managementNumbers = new HashSet<>();
        for (int i = 0; i < rows.size(); i++) {
            String managementNumber = required(rows.get(i).getManagementNumber(), i + 2, "管理编号");
            if (!managementNumbers.add(managementNumber)) {
                throw new RuntimeException("第" + (i + 2) + "行：管理编号" + managementNumber + "在文件中重复");
            }
        }
        Set<String> existingNumbers = baseMapper.selectList(Wrappers.<Device>lambdaQuery()
                        .in(Device::getManagementNumber, managementNumbers)
                        .select(Device::getManagementNumber))
                .stream().map(Device::getManagementNumber).collect(Collectors.toSet());

        Map<String, List<SysDept>> departmentMap = sysDeptMapper.selectDeptList(new SysDept()).stream()
                .filter(item -> StringUtils.isNotBlank(item.getDeptName()))
                .collect(Collectors.groupingBy(item -> item.getDeptName().trim()));
        Map<String, List<User>> userMap = userMapper.selectList(Wrappers.<User>lambdaQuery()
                        .isNotNull(User::getName).select(User::getId, User::getName))
                .stream().collect(Collectors.groupingBy(item -> item.getName().trim()));
        Map<String, String> statusMap = dictMap("device_status");
        Map<String, String> typeMap = dictMap("device_type");

        List<Device> devices = new ArrayList<>();
        for (int i = 0; i < rows.size(); i++) {
            int rowNumber = i + 2;
            DeviceImport row = rows.get(i);
            String managementNumber = required(row.getManagementNumber(), rowNumber, "管理编号");
            if (existingNumbers.contains(managementNumber)) {
                throw new RuntimeException("第" + rowNumber + "行：管理编号" + managementNumber + "已存在");
            }

            String laboratoryName = required(row.getLaboratoryName(), rowNumber, "所属部门");
            List<SysDept> departments = departmentMap.get(laboratoryName);
            if (CollectionUtils.isEmpty(departments) || departments.size() > 1) {
                throw new RuntimeException("第" + rowNumber + "行：所属部门“" + laboratoryName + "”不存在或不唯一");
            }

            Device device = new Device();
            device.setDeviceName(required(row.getDeviceName(), rowNumber, "仪器名称"));
            device.setSpecificationModel(required(row.getSpecificationModel(), rowNumber, "规格型号"));
            device.setManagementNumber(managementNumber);
            device.setActivationDate(parseDate(row.getActivationDate(), rowNumber, "校准有效期", true));
            device.setSubordinateDepartmentsId(departments.get(0).getDeptId().intValue());
            device.setDeviceStatus(Integer.valueOf(dictValue(statusMap, row.getDeviceStatus(), rowNumber, "当前状态", true)));
            device.setCalibrationDate(required(row.getCalibrationDate(), rowNumber, "校准周期(月)"));
            device.setEnDeviceName(trim(row.getEnDeviceName()));
            device.setManufacturer(trim(row.getManufacturer()));
            device.setFactoryNo(trim(row.getFactoryNo()));
            device.setAssetCode(trim(row.getAssetCode()));
            device.setOrigin(trim(row.getOrigin()));
            device.setAcquisitionDate(parseDate(row.getAcquisitionDate(), rowNumber, "购置日期", false));
            device.setStoragePoint(trim(row.getStoragePoint()));
            device.setTechnicalIndicators(trim(row.getTechnicalIndicators()));
            device.setCalibrationServices(trim(row.getCalibrationServices()));
            device.setLastCalibrationDate(parseDate(row.getLastCalibrationDate(), rowNumber, "最近校准日期", false));
            device.setNextCalibrationDate(parseDate(row.getNextCalibrationDate(), rowNumber, "下次校准日期", false));
            device.setLargeCategory(dictValue(typeMap, row.getLargeCategory(), rowNumber, "设备类型", false));
            device.setUnitPrice(parseDecimal(row.getUnitPrice(), rowNumber, "单价(万元)"));

            String managerName = trim(row.getEquipmentManagerName());
            if (StringUtils.isNotBlank(managerName)) {
                List<User> users = userMap.get(managerName);
                if (CollectionUtils.isEmpty(users) || users.size() > 1) {
                    throw new RuntimeException("第" + rowNumber + "行：负责人“" + managerName + "”不存在或不唯一");
                }
                device.setEquipmentManager(users.get(0).getId());
            }
            devices.add(device);
        }
        saveBatch(devices);
        return devices.size();
    }

    private Map<String, String> dictMap(String dictType) {
        return sysDictDataMapper.selectDictDataByType(dictType).stream()
                .collect(Collectors.toMap(item -> item.getDictLabel().trim(), SysDictData::getDictValue, (first, second) -> first));
    }

    private String dictValue(Map<String, String> values, String input, int rowNumber, String column, boolean required) {
        String text = trim(input);
        if (StringUtils.isBlank(text)) {
            if (required) {
                throw new RuntimeException("第" + rowNumber + "行：" + column + "不能为空");
            }
            return null;
        }
        String value = values.get(text);
        if (value == null && values.containsValue(text)) {
            value = text;
        }
        if (value == null) {
            throw new RuntimeException("第" + rowNumber + "行：" + column + "“" + text + "”不在系统字典中");
        }
        return value;
    }

    private LocalDateTime parseDate(String input, int rowNumber, String column, boolean required) {
        String text = trim(input);
        if (StringUtils.isBlank(text)) {
            if (required) {
                throw new RuntimeException("第" + rowNumber + "行：" + column + "不能为空");
            }
            return null;
        }
        try {
            return LocalDate.parse(text.substring(0, Math.min(text.length(), 10)).replace('/', '-'), DateTimeFormatter.ofPattern("yyyy-M-d"))
                    .atStartOfDay();
        } catch (Exception e) {
            throw new RuntimeException("第" + rowNumber + "行：" + column + "格式应为yyyy-MM-dd", e);
        }
    }

    private BigDecimal parseDecimal(String input, int rowNumber, String column) {
        String text = trim(input);
        if (StringUtils.isBlank(text)) {
            return null;
        }
        try {
            return new BigDecimal(text);
        } catch (NumberFormatException e) {
            throw new RuntimeException("第" + rowNumber + "行：" + column + "必须是数字", e);
        }
    }

    private String required(String input, int rowNumber, String column) {
        String value = trim(input);
        if (StringUtils.isBlank(value)) {
            throw new RuntimeException("第" + rowNumber + "行：" + column + "不能为空");
        }
        return value;
    }

    private String trim(String value) {
        return value == null ? null : value.trim();
    }
}