- 업체 excel import 오류 수정 및 유효성 검사 추가

이민희

2022-01-24 2738a018508fda6ea79af263fb605af8d306897f

 src/main/java/kr/wisestone/owl/service/impl/CompanyFieldServiceImpl.java

@@ -85,12 +85,19 @@
        this.verifyUrl(companyFieldForm.getUrl(), null);

        if (companyFieldForm.getTelList() != null && companyFieldForm.getTelList().size() > 0) {
            String[] tels = ConvertUtil.ToArray(companyFieldForm.getTelList());
            companyFieldForm.setTel(Arrays.toString(tels));
            String tels = companyFieldForm.getTelList().toString();
            if (tels.contains("[")) {
                tels = tels.substring(1, tels.indexOf("]"));
            }
            companyFieldForm.setTel(tels.trim());
        }
        if (companyFieldForm.getEmailList() != null && companyFieldForm.getEmailList().size() > 0) {
            String[] emails = ConvertUtil.ToArray(companyFieldForm.getEmailList());
            companyFieldForm.setEmail(Arrays.toString(emails));
            String emails = companyFieldForm.getEmailList().toString();
            String email = "";
            if (emails.contains("[")) {
                email = emails.substring(1, emails.indexOf("]"));
            }
            companyFieldForm.setEmail(email.trim());
        }

        CompanyField companyField = ConvertUtil.copyProperties(companyFieldForm, CompanyField.class);
@@ -135,6 +142,40 @@
        return this.companyFieldMapper.find(condition);
    }

    /**
     * companyFieldCategory Name 설정
     * @param companyFieldVo CompanyFieldVo
     * @param companyField CompanyField
     */
    @Override
    public CompanyFieldVo CreateCompanyFieldCategory(CompanyFieldVo companyFieldVo, CompanyField companyField) {
        if (companyField.getCompanyTypeId() != null && companyField.getCompanyTypeId() != -1) {
            CompanyFieldCategory companyType = this.companyFieldCategoryService.find(companyField.getCompanyTypeId());
            if (companyType != null) {
                companyFieldVo.setCompanyTypeName(companyType.getUseValue());
            }
        }
        if (companyField.getParentSectorId() != null && companyField.getParentSectorId() != -1) {
            CompanyFieldCategory parentSector = this.companyFieldCategoryService.find(companyField.getParentSectorId());
            if (parentSector != null) {
                companyFieldVo.setParentSectorName(parentSector.getUseValue());
            }
        }
        if (companyField.getChildSectorId() != null && companyField.getChildSectorId() != -1) {
            CompanyFieldCategory childSector = this.companyFieldCategoryService.find(companyField.getChildSectorId());
            if (childSector != null) {
                companyFieldVo.setChildSectorName(childSector.getUseValue());
            }
        }
        if (companyField.getRegionId() != null && companyField.getRegionId() != -1) {
            CompanyFieldCategory region = this.companyFieldCategoryService.find(companyField.getRegionId());
            if (region != null) {
                companyFieldVo.setRegionName(region.getUseValue());
            }
        }
        return companyFieldVo;
    }

    // 업체 상세 조회한다.
    @Override
    @Transactional
@@ -159,36 +200,7 @@
            ispFieldVo = ConvertUtil.copyProperties(ispField, IspFieldVo.class);
            hostingFieldVo = ConvertUtil.copyProperties(hostingField, HostingFieldVo.class);

            if (companyField.getCompanyTypeId() != null && companyField.getCompanyTypeId() != -1) {
                CompanyFieldCategory companyType = this.companyFieldCategoryService.find(companyField.getCompanyTypeId());
                if (companyType != null) {
                    companyFieldVo.setCompanyTypeName(companyType.getUseValue());
                }
            }
            if (companyField.getParentSectorId() != null && companyField.getParentSectorId() != -1) {
                CompanyFieldCategory parentSector = this.companyFieldCategoryService.find(companyField.getParentSectorId());
                if (parentSector != null) {
                    companyFieldVo.setParentSectorName(parentSector.getUseValue());
                }
            }
            if (companyField.getChildSectorId() != null && companyField.getChildSectorId() != -1) {
                CompanyFieldCategory childSector = this.companyFieldCategoryService.find(companyField.getChildSectorId());
                if (childSector != null) {
                    companyFieldVo.setChildSectorName(childSector.getUseValue());
                }
            }
            if (companyField.getRegionId() != null && companyField.getRegionId() != -1) {
                CompanyFieldCategory region = this.companyFieldCategoryService.find(companyField.getRegionId());
                if (region != null) {
                    companyFieldVo.setRegionName(region.getUseValue());
                }
            }
            /*if (companyField.getStatusId() != null && companyField.getStatusId() != -1) {
                CompanyFieldCategory status = this.companyFieldCategoryService.find(companyField.getStatusId());
                if (status != null) {
                    companyFieldVo.setStatusName(status.getUseValue());
                }
            }*/
            CreateCompanyFieldCategory(companyFieldVo, companyField);

            companyFieldVo.setIspFieldVo(ispFieldVo);
            companyFieldVo.setHostingFieldVo(hostingFieldVo);
@@ -203,12 +215,24 @@
        this.verifyUrl(companyFieldForm.getUrl(), companyFieldForm.getId());

        if (companyFieldForm.getTelList() != null && companyFieldForm.getTelList().size() > 0) {
            String[] tels = ConvertUtil.ToArray(companyFieldForm.getTelList());
            companyFieldForm.setTel(Arrays.toString(tels));
            String tels = companyFieldForm.getTelList().toString();
            if (tels.contains("[")) {
                tels = tels.substring(1, tels.indexOf("]"));
            }
            companyFieldForm.setTel(tels.trim());
        }
        if (companyFieldForm.getEmailList() != null && companyFieldForm.getEmailList().size() > 0) {
            String[] emails = ConvertUtil.ToArray(companyFieldForm.getEmailList());
            companyFieldForm.setEmail(Arrays.toString(emails));
            String emails = companyFieldForm.getEmailList().toString();
            if (emails.contains("[")) {
                emails = emails.substring(1, emails.indexOf("]"));
            }
            companyFieldForm.setEmail(emails.trim());
        }

        CompanyFieldCategory companyFieldCategory = this.companyFieldCategoryService.find(companyFieldForm.getChildSectorId());
        if (!companyFieldCategory.getParentId().equals(companyFieldForm.getParentSectorId())) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.PARENT_SECTOR_NOT_EQUAL));
        }

        CompanyField companyField = ConvertUtil.copyProperties(companyFieldForm, CompanyField.class);
@@ -395,6 +419,15 @@
        }
    }

    /**
     * cell NULL 체크 함수
     * @param cell Cell
     * @return boolean
     */
    private Boolean cellNullCheck (Cell cell) {
        return cell != null && cell.getStringCellValue() != null && cell.getCellType() != Cell.CELL_TYPE_BLANK;
    }

    //  엑셀 필드에 있는 정보를 업체 form 으로 옮긴다.
    private CompanyFieldForm setCompanyFieldFormToExcelField(Row row, int rowIndex, Map<String, IspField> ispFieldMaps, Map<String, HostingField> hostingFieldMaps,
                                                             Map<String, Map<String, Object>> companyTypeMaps, Map<String, Map<String, Object>> parentSectorMaps,
@@ -419,76 +452,76 @@

                case 2:
                    // isp명
                    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    if (cellNullCheck(cell)) {
                        this.setCompanyFormIspName(cell, ispFieldMaps, companyFieldForm, rowIndex);
                    }
                    break;

                case 3:
                    // 호스팅명
                    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    if (cellNullCheck(cell)) {
                        this.setCompanyFormHostingName(cell, hostingFieldMaps, companyFieldForm, rowIndex);
                    }
                    break;

                case 4:
                    // 연락처
                    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    if (cellNullCheck(cell)) {
                        this.setCompanyFormTel(cell, companyFieldForm, rowIndex);
                    }
                    break;

                case 5:
                    // 이메일
                    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    if (cellNullCheck(cell)) {
                        this.setCompanyFormEmail(cell, companyFieldForm, rowIndex);
                    }
                    break;

                case 6:
                    // 담당자
                    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    if (cellNullCheck(cell)) {
                        this.setCompanyFormManager(cell, companyFieldForm, rowIndex);
                    }
                    break;

                case 7:
                    // 기업구분
                    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    if (cellNullCheck(cell)) {
                        this.setCompanyFormCompanyType(cell, companyTypeMaps, companyFieldForm, rowIndex);
                    }
                    break;

                case 8:
                    // 업종(대분류)
                    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    if (cellNullCheck(cell)) {
                        this.setCompanyFormParentSector(cell, parentSectorMaps, companyFieldForm, rowIndex);
                    }
                    break;

                case 9:
                    // 업종(중분류)
                    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    if (cellNullCheck(cell)) {
                        this.setCompanyFormChildSector(cell, childSectorMaps, companyFieldForm, rowIndex);
                    }
                    break;

                case 10:
                    // 지역
                    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    if (cellNullCheck(cell)) {
                        this.setCompanyFormRegion(cell, regionMaps, companyFieldForm, rowIndex);
                    }
                    break;

                case 11:
                    // 상태
                    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    if (cellNullCheck(cell)) {
                        this.setCompanyFormStatus(cell, statusMaps, companyFieldForm, rowIndex);
                    }

                case 12:
                    // 비고
                    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    if (cellNullCheck(cell)) {
                        this.setCompanyFormMemo(cell, companyFieldForm, rowIndex);
                    }
                    break;
@@ -566,19 +599,29 @@

    private void setCompanyFormEmail(Cell cell, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (cell != null) {
            companyFieldForm.setEmail(CommonUtil.convertExcelStringToCell(cell));
            String email = CommonUtil.convertExcelStringToCell(cell);
            if (CommonUtil.convertExcelStringToCell(cell).contains(" ")) {
                email = CommonUtil.convertExcelStringToCell(cell).replace(" ", "");
            }
            companyFieldForm.setEmail(email);
        }
    }

    private void setCompanyFormTel(Cell cell, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (cell != null) {
            companyFieldForm.setTel(CommonUtil.convertExcelStringToCell(cell));
            String tel = CommonUtil.convertExcelStringToCell(cell);
            if (tel.contains("-")) {
                tel = tel.replace("-", "");
            }
            if (tel.contains(" ")) {
                tel = tel.replace(" ", "");
            }
            companyFieldForm.setTel(tel);
        }
    }

    private void setCompanyFormHostingName(Cell cell, Map<String, HostingField> hostingFieldMaps, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (cell != null) {

            HostingField hostingField = hostingFieldMaps.get(CommonUtil.convertExcelStringToCell(cell));
            if (hostingField == null) {
                throw new OwlRuntimeException(
@@ -590,7 +633,6 @@

    private void setCompanyFormIspName(Cell cell, Map<String, IspField> ispFieldMaps, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (cell != null) {

            IspField ispField = ispFieldMaps.get(CommonUtil.convertExcelStringToCell(cell));
            if (ispField == null) {
                throw new OwlRuntimeException(
@@ -619,6 +661,9 @@
                    this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_URL_IS_NULL, rowIndex));
        }
        String url = CommonUtil.convertExcelStringToCell(cell);
        if (url.contains(" ")) {
            url = url.replace(" ", "");
        }
        this.verifyUrl(url, null); //url 유효성 검사

        companyFieldForm.setUrl(url);
@@ -654,6 +699,19 @@

        CompanyFieldCondition companyFieldCondition = CompanyFieldCondition.make(conditions);
        List<Map<String, Object>> results = this.companyFieldMapper.find(companyFieldCondition);
        CompanyFieldVo companyFieldVo = new CompanyFieldVo();

        if (results != null && results.size() > 0) {
            for (Map<String, Object> result : results) {
                CompanyField companyField = ConvertUtil.convertMapToClass(result, CompanyField.class);
                CompanyFieldVo companyFieldVo2 = CreateCompanyFieldCategory(companyFieldVo, companyField);

                result.put("companyTypeName", companyFieldVo2.getCompanyTypeName());
                result.put("parentSectorName", companyFieldVo2.getParentSectorName());
                result.put("childSectorName", companyFieldVo2.getChildSectorName());
                result.put("regionName", companyFieldVo2.getRegionName());
            }
        }
        List<CompanyFieldVo> companyFieldVos = ConvertUtil.convertListToListClass(results, CompanyFieldVo.class);

        // code_ko_KR 에 code명 설정