excel中一棵完整的树、只有子级没有父级的树的导入逻辑

最新推荐文章于 2024-07-12 23:03:07 发布
weixin_45209491
最新推荐文章于 2024-07-12 23:03:07 发布
阅读量220
点赞数
分类专栏： Java 文章标签：算法 java 前端
本文链接：https://blog.csdn.net/weixin_45209491/article/details/126304416
版权
Java 专栏收录该内容
40 篇文章 0 订阅
订阅专栏
node的导入

excel中一棵完整的树、只有子级没有父级的树的导入逻辑
private void checkDataAndInsert(Long tenantId, List<LimsUnitDTO> units, List<LimsExperimentItemTypeImportDTO> importVOList){
        //事业部
        List<String> unitNameList = units.stream().map(LimsUnitDTO::getUnitName).collect(Collectors.toList());
        Map<String, List<LimsUnitDTO>> unitNameMap = units.stream().collect(Collectors.groupingBy(LimsUnitDTO::getUnitName));
        List<Long> unitIds = units.stream().map(LimsUnitDTO::getUnitId).collect(Collectors.toList());
        Map<String, Long> unitNameIdMap = units.stream().collect(Collectors.toMap(LimsUnitDTO::getUnitName, LimsUnitDTO::getUnitId, (key1, key2) -> key2));

        //层级、分类类型
        List<LovValueDTO> lovValueLevelList = lovAdapter.queryLovValue("FYDS.LIMS.ITEM_TYPE_LEVEL", tenantId);//分类层级
        List<LovValueDTO> lovValueSortList = lovAdapter.queryLovValue("FYDS.LIMS.ITEM_TYPE_SORT", tenantId);//分类类型
        Map<String, String> levelMap = lovValueLevelList.stream().collect(Collectors.toMap(LovValueDTO::getValue, d -> d.getMeaning()));
        Map<String, String> sortMap = lovValueSortList.stream().collect(Collectors.toMap(LovValueDTO::getValue, d -> d.getMeaning()));

        List<LimsExperimentItemType> limsExperimentItemTypeList = new ArrayList<>(importVOList.size());
        Set<String> limsExperimentItemTypeSet = new HashSet<>(importVOList.size());
        Set<String> errorUnitNameSet = new HashSet<>(importVOList.size());
        Set<String> userNames = new HashSet<>(importVOList.size());
        for (LimsExperimentItemTypeImportDTO data : importVOList) {
            //入参必输性校验
            processValidateMessage(tenantId, StringUtils.isBlank(data.getUnitName()), "LIMS_EXPERIMENT_ITEM_TYPE_IMPORT_0001", "事业部");
            processValidateMessage(tenantId, StringUtils.isBlank(data.getItemTypeName()), "LIMS_EXPERIMENT_ITEM_TYPE_IMPORT_0001", "分类名称");
            processValidateMessage(tenantId, StringUtils.isBlank(data.getItemTypeSort()), "LIMS_EXPERIMENT_ITEM_TYPE_IMPORT_0001", "分类类型");
            processValidateMessage(tenantId, StringUtils.isBlank(data.getItemTypeLevel()), "LIMS_EXPERIMENT_ITEM_TYPE_IMPORT_0001", "分类层级");
            processValidateMessage(tenantId, StringUtils.isBlank(data.getCreatedByName()), "LIMS_EXPERIMENT_ITEM_TYPE_IMPORT_0001", "创建人");
            processValidateMessage(tenantId, StringUtils.isBlank(data.getLastUpdatedByName()), "LIMS_EXPERIMENT_ITEM_TYPE_IMPORT_0001", "更新人");
            userNames.add(data.getCreatedByName());
            userNames.add(data.getLastUpdatedByName());

            //分类层级校验
            if (MapUtils.isNotEmpty(levelMap) && !levelMap.containsKey(data.getItemTypeLevel())){
                processValidateMessage(tenantId, "LIMS_EXPERIMENT_ITEM_TYPE_IMPORT_0003", data.getItemTypeName());
            }

            //分类类型校验
            if (MapUtils.isNotEmpty(sortMap) && !sortMap.containsKey(data.getItemTypeSort())){
                processValidateMessage(tenantId, "LIMS_EXPERIMENT_ITEM_TYPE_IMPORT_0004", data.getItemTypeName());
            }

            //事业部校验
            if (CollectionUtils.isNotEmpty(unitNameList) && !unitNameList.contains(data.getUnitName())){
                errorUnitNameSet.add(data.getUnitName());
            }

            //校验上层分类名称是否存在
            if (!StringUtils.equals("ROOT",data.getItemTypeLevel()) && Objects.isNull(data.getItemTypeUpName()) && Objects.isNull(data.getItemTypeUpLevel())){
                processValidateMessage(tenantId, "LIMS_EXPERIMENT_ITEM_TYPE_IMPORT_0005", data.getItemTypeName());
            }

            //入参中唯一性校验：“分类层级+分类名称+事业部”唯一,不存重复值
            if (limsExperimentItemTypeSet.add(data.getItemTypeName() + "_" + data.getItemTypeLevel()+ "_" + data.getUnitName())){
                //唯一性校验通过
            }else {
                processValidateMessage(tenantId, "LIMS_EXPERIMENT_ITEM_TYPE_IMPORT_0006", data.getItemTypeName());
            }
        }

        //校验创建人/更新人存在性
        Map<String, List<MtUserInfo>> userMap = new HashMap<>(userNames.size());
        if (CollectionUtils.isNotEmpty(userNames)){
            List<MtUserInfo> mtUserInfos = limsStandardItemMapper.selectUserInfoByRealNameBatch(new ArrayList<>(userNames));
            if (CollectionUtils.isNotEmpty(mtUserInfos)){
                userMap = mtUserInfos.stream().collect(Collectors.groupingBy(MtUserInfo::getRealName));
            }
        }

        //校验事业部是否都是用户所属的事业部
        if (CollectionUtils.isNotEmpty(errorUnitNameSet)){
            processValidateMessage(tenantId, "FYDS_LIMS_STANDARD_ITEM_0001", errorUnitNameSet.toString());
        }

        //校验创建/更新人是否存在，是否找到多个
        for (LimsExperimentItemTypeImportDTO data : importVOList) {
            data.setTenantId(tenantId);
            if (MapUtils.isNotEmpty(unitNameIdMap) && unitNameIdMap.containsKey(data.getUnitName())) {
                data.setUnitId(unitNameIdMap.get(data.getUnitName()));
            }

            processValidateMessage(tenantId, !userMap.containsKey(data.getCreatedByName()), "FYDS_LIMS_STANDARD_ITEM_0022", data.getCreatedByName());
            processValidateMessage(tenantId, userMap.get(data.getCreatedByName()).size() > 1, "FYDS_LIMS_STANDARD_ITEM_0022", data.getCreatedByName());
            processValidateMessage(tenantId, !userMap.containsKey(data.getLastUpdatedByName()), "FYDS_LIMS_STANDARD_ITEM_0023", data.getLastUpdatedByName());
            processValidateMessage(tenantId, userMap.get(data.getLastUpdatedByName()).size() > 1, "FYDS_LIMS_STANDARD_ITEM_0023", data.getLastUpdatedByName());
        }

        //获取新增事件
        MtEventCreateVO eventCreateVO2 = new MtEventCreateVO();
        eventCreateVO2.setEventTypeCode("FYDS_LIMS_ITEM_TYPE_IMPORT");
        Long eventId = eventRepository.eventCreate(tenantId, eventCreateVO2);

        //数据构造及插入
        List<LimsExperimentItemTypeT> addFristTempTList = new ArrayList<>(importVOList.size());
        List<LimsExperimentItemTypeT> updateFristTempTList = new ArrayList<>(importVOList.size());
        List<LimsExperimentItemType> addFristList = new ArrayList<>(importVOList.size());
        List<LimsExperimentItemType> updateFristList = new ArrayList<>(importVOList.size());
        List<LimsExperimentItemTypeHis> addFristHisList = new ArrayList<>(importVOList.size());
        List<LimsExperimentItemTypeHis> updateFristHisList = new ArrayList<>(importVOList.size());

        //保存分类名称+ID的映射
        List<Long> nextPrimaryKeys = customDbRepository.getNextPrimaryKeys(LimsExperimentItemType.class, importVOList.size());
        Map<String, Long> itemTypeNameIdMap = new HashMap<>(importVOList.size());
        List<String> validList = new ArrayList<>();
        int num = 0;
        for (LimsExperimentItemTypeImportDTO data : importVOList) {
            itemTypeNameIdMap.put(data.getItemTypeName()+"-"+data.getItemTypeLevel()+"-"+data.getUnitName(), nextPrimaryKeys.get(num));
            validList.add(data.getItemTypeName()+"-"+data.getItemTypeLevel()+"-"+data.getUnitName());
            num++;
        }

        //表中是否存在子级的根目录
        List<LimsExperimentItemTypeDto> existUpItemTypeIdList = limsExperimentItemTypeMapper.selectUpItemTypeByContion(tenantId, unitIds, importVOList);
        //事业部名称
        List<Long> unitIdList = existUpItemTypeIdList.stream().map(LimsExperimentItemType::getUnitId).distinct().collect(Collectors.toList());
        UnitQueryDTO unitQueryDTO = new UnitQueryDTO();
        unitQueryDTO.setUnitIds(unitIdList);
        List<LimsUnitDTO> unitList = hzeroPlatformFeignClient.listCompanyUnitBatch(tenantId, unitQueryDTO).getBody();
        Map<Long, String> unitMap = unitList.stream().collect(Collectors.toMap(LimsUnitDTO::getUnitId, LimsUnitDTO::getUnitName, (key1, key2) -> key2));
        existUpItemTypeIdList.stream().forEach(item -> {
            if (MapUtils.isNotEmpty(unitMap) && unitMap.containsKey(item.getUnitId())) {
                item.setUnitName(unitMap.get(item.getUnitId()));
            }
        });
        //对于excel中没有根目录的子级，在表中查看是否存在
        Map<String, Long> existUpIdList = existUpItemTypeIdList.stream().distinct().collect(Collectors.toMap(t -> t.getItemTypeName() + "-" + t.getItemTypeLevel() + "-" +
                t.getUnitName(), s -> s.getItemTypeId()));

        //校验子级是否存在根目录：validList（excel中是否包含）、existIdList（表中是否包含）
        for (LimsExperimentItemTypeImportDTO importDto : importVOList) {
            //如果不是顶层分类
            if (StringUtils.isNotBlank(importDto.getItemTypeUpName())){
                //excel存在父级
                String valid = importDto.getItemTypeUpName()+"-"+importDto.getItemTypeUpLevel()+"-"+importDto.getUnitName();
                if (!validList.contains(valid) && !existUpIdList.containsKey(valid)){
                    processValidateMessage(tenantId, "LIMS_EXPERIMENT_ITEM_TYPE_IMPORT_0005", importDto.getItemTypeUpName());
                }
            }
        }


        //表中是否存在子级的根目录
        List<LimsExperimentItemTypeDto> existItemTypeIdList = limsExperimentItemTypeMapper.selectExperimentItemTypeByContion(tenantId, unitIds, importVOList);
        //表中唯一性校验(根目录)：“分类层级+分类名称+事业部”唯一
        if (CollectionUtils.isNotEmpty(existItemTypeIdList)){
            processValidateMessage(tenantId, "LIMS_EXPERIMENT_ITEM_TYPE_IMPORT_0006", existItemTypeIdList.get(0).getItemTypeName());
        }

        //事业部名称
        List<Long> unitIdLists = existItemTypeIdList.stream().map(LimsExperimentItemType::getUnitId).distinct().collect(Collectors.toList());
        UnitQueryDTO unitQueryDTO1 = new UnitQueryDTO();
        unitQueryDTO1.setUnitIds(unitIdLists);
        List<LimsUnitDTO> unitList1 = hzeroPlatformFeignClient.listCompanyUnitBatch(tenantId, unitQueryDTO1).getBody();
        Map<Long, String> unitMap1 = unitList1.stream().collect(Collectors.toMap(LimsUnitDTO::getUnitId, LimsUnitDTO::getUnitName, (key1, key2) -> key2));
        existItemTypeIdList.stream().forEach(item -> {
            if (MapUtils.isNotEmpty(unitMap1) && unitMap1.containsKey(item.getUnitId())) {
                item.setUnitName(unitMap1.get(item.getUnitId()));
            }
        });
        /因为先做“表中唯一性校验(根目录)：“分类层级+分类名称+事业部”唯一”，所以existIdList 判断更新永远走不到这一步。
        Map<String, Long> existIdList = existItemTypeIdList.stream().distinct().collect(Collectors.toMap(t -> t.getItemTypeName() + "-" + t.getItemTypeLevel() + "-" +
                t.getUnitName(), s -> s.getItemTypeId()));

        //插入、更新数据
        for (LimsExperimentItemTypeImportDTO importDto : importVOList) {
            LimsExperimentItemType root = new LimsExperimentItemType();
            if (existIdList.containsKey(importDto.getItemTypeName() + "-" + importDto.getItemTypeLevel() + "-" + importDto.getUnitName())){
                Long existId = existIdList.get(importDto.getItemTypeName() + "-" + importDto.getItemTypeLevel() + "-" + importDto.getUnitName());
                BeanUtils.copyProperties(importDto, root);
                root.setItemTypeId(existId);
                root.setTenantId(tenantId);
                root.setUnitId(unitNameMap.get(importDto.getUnitName()).get(0).getUnitId());
                root.setItemTypeStatus(StringUtils.isNotBlank(importDto.getApprovalStatus()) ? importDto.getApprovalStatus() : LimsContants.EnableFlag.N);
                root.setApprovalStatus(LimsContants.ExperimentItemTypeStatus.APPROVED);
                root.setCreatedBy(userMap.get(importDto.getCreatedByName()).get(0).getId());
                root.setLastUpdatedBy(userMap.get(importDto.getLastUpdatedByName()).get(0).getId());
                //如果不是顶层分类
                if (StringUtils.isNotBlank(importDto.getItemTypeUpName())){
                    //excel存在父级
                    if (itemTypeNameIdMap.containsKey(importDto.getItemTypeUpName()+"-"+importDto.getItemTypeUpLevel()+"-"+importDto.getUnitName())){
                        root.setItemTypeCodeUp(itemTypeNameIdMap.get(importDto.getItemTypeUpName()+"-"+importDto.getItemTypeUpLevel()+"-"+importDto.getUnitName()));
                    }else if(existUpIdList.containsKey(importDto.getItemTypeUpName()+"-"+importDto.getItemTypeUpLevel()+"-"+importDto.getUnitName())){
                        //excel不存在父级、表里存在父级
                        root.setItemTypeCodeUp(existUpIdList.get(importDto.getItemTypeUpName() + "-" + importDto.getItemTypeUpLevel() + "-" + importDto.getUnitName()));
                    }
                }
                updateFristList.add(root);

                //临时表
                LimsExperimentItemTypeT updatetTemp = new LimsExperimentItemTypeT();
                BeanUtils.copyProperties(root, updatetTemp);
                updatetTemp.setSubmitFlag(LimsContants.EnableFlag.Y);
                updatetTemp.setOperationType(LimsContants.ExperimentItemTypeStatus.ADD);
                updateFristTempTList.add(updatetTemp);

                LimsExperimentItemTypeHis rootHis = new LimsExperimentItemTypeHis();
                BeanUtils.copyProperties(root, rootHis);
                rootHis.setEventId(eventId);
                updateFristHisList.add(rootHis);

            }else{
                //主表
                BeanUtils.copyProperties(importDto, root);
                root.setTenantId(tenantId);
                root.setItemTypeId(itemTypeNameIdMap.get(importDto.getItemTypeName()+"-"+importDto.getItemTypeLevel()+"-"+importDto.getUnitName()));
                root.setUnitId(unitNameMap.get(importDto.getUnitName()).get(0).getUnitId());
                root.setItemTypeStatus(StringUtils.isNotBlank(importDto.getApprovalStatus()) ? importDto.getApprovalStatus() : LimsContants.EnableFlag.N);
                root.setApprovalStatus(LimsContants.ExperimentItemTypeStatus.APPROVED);
                root.setCreatedBy(userMap.get(importDto.getCreatedByName()).get(0).getId());
                root.setLastUpdatedBy(userMap.get(importDto.getLastUpdatedByName()).get(0).getId());
                //如果不是顶层分类
                if (StringUtils.isNotBlank(importDto.getItemTypeUpName())){
                    //excel存在父级
                    if (itemTypeNameIdMap.containsKey(importDto.getItemTypeUpName()+"-"+importDto.getItemTypeUpLevel()+"-"+importDto.getUnitName())){
                        root.setItemTypeCodeUp(itemTypeNameIdMap.get(importDto.getItemTypeUpName()+"-"+importDto.getItemTypeUpLevel()+"-"+importDto.getUnitName()));
                    }else if(existUpIdList.containsKey(importDto.getItemTypeUpName()+"-"+importDto.getItemTypeUpLevel()+"-"+importDto.getUnitName())){
                        //excel不存在父级、表里存在父级
                        root.setItemTypeCodeUp(existUpIdList.get(importDto.getItemTypeUpName() + "-" + importDto.getItemTypeUpLevel() + "-" + importDto.getUnitName()));
                    }
                }

                addFristList.add(root);

                //临时表
                LimsExperimentItemTypeT addfristTemp = new LimsExperimentItemTypeT();
                BeanUtils.copyProperties(root, addfristTemp);
                addfristTemp.setSubmitFlag(LimsContants.EnableFlag.Y);
                addfristTemp.setOperationType(LimsContants.ExperimentItemTypeStatus.ADD);
                addFristTempTList.add(addfristTemp);

                LimsExperimentItemTypeHis rootHis = new LimsExperimentItemTypeHis();
                BeanUtils.copyProperties(root, rootHis);
                rootHis.setEventId(eventId);
                addFristHisList.add(rootHis);
            }
        }

        if (CollectionUtils.isNotEmpty(addFristList)){
            limsCustomDbRepository.batchInsertWithPrimaryKey(addFristList);
            limsCustomDbRepository.batchInsertWithPrimaryKey(addFristTempTList);
            limsCustomDbRepository.batchInsert(addFristHisList);
        }
        if (CollectionUtils.isNotEmpty(updateFristList)){
            limsCustomDbRepository.batchUpdateSelective(updateFristList);
            limsCustomDbRepository.batchUpdateSelective(updateFristTempTList);
            limsCustomDbRepository.batchInsert(updateFristHisList);
        }
    }