업체 임포트 시 코드 보완

이민희

2022-02-07 4ab15ca2bb74d0d33d1325d849422635b9d415b1

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

@@ -81,8 +81,13 @@
    // 업체 추가
    @Override
    public CompanyField addCompany(CompanyFieldForm companyFieldForm) {
        //  url 유효성 체크
        this.verifyUrl(companyFieldForm.getUrl(), null);
        //  업체명 중복 체크
        this.verifyTitle(companyFieldForm.getName(), null);

        if (companyFieldForm.getUrl() != null) {
            //  url 유효성 체크
            this.verifyUrl(companyFieldForm.getUrl(), null);
        }

        if (companyFieldForm.getTelList() != null && companyFieldForm.getTelList().size() > 0) {
            String tels = companyFieldForm.getTelList().toString();
@@ -107,12 +112,11 @@

    //  url 유효성 체크
    private void verifyUrl(String url, Long id) {
        if (StringUtils.isEmpty(url)) {
        /*if (StringUtils.isEmpty(url)) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.COMPANYFIELD_NOT_URL));
        }
                    this.messageAccessor.getMessage(MsgConstants.COMPANY_NOT_URL));
        }*/
        CompanyField companyField;

        if(id == null){
            companyField = this.companyFieldRepository.findByUrl(url);
        } else {
@@ -121,7 +125,7 @@

        if (companyField != null) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.COMPANYFIELD_USED_URL));
                    this.messageAccessor.getMessage(MsgConstants.COMPANY_USED_URL));
        }
    }

@@ -142,12 +146,20 @@
        return this.companyFieldMapper.find(condition);
    }

    //  모든 업체정보를 조회한다. 이슈 엑셀 import 에서 사용
    @Override
    @Transactional(readOnly = true)
    public List<CompanyField> findAll() {
        return this.companyFieldRepository.findAll();
    }

    /**
     * companyFieldCategory Name 설정
     * @param companyFieldVo CompanyFieldVo
     * @param companyField CompanyField
     */
    private CompanyFieldVo CreateCompanyFieldCategory(CompanyFieldVo companyFieldVo, 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) {
@@ -210,8 +222,13 @@
    // 업체 정로를 수정한다.
    @Override
    public void modifyCompany(CompanyFieldForm companyFieldForm) {
        //  url 유효성 체크
        this.verifyUrl(companyFieldForm.getUrl(), companyFieldForm.getId());
        //  업체명 유효성 체크
        this.verifyTitle(companyFieldForm.getName(), companyFieldForm.getId());

        if (companyFieldForm.getUrl() != null) {
            //  url 유효성 체크
            this.verifyUrl(companyFieldForm.getUrl(), companyFieldForm.getId());
        }

        if (companyFieldForm.getTelList() != null && companyFieldForm.getTelList().size() > 0) {
            String tels = companyFieldForm.getTelList().toString();
@@ -228,10 +245,12 @@
            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));
        if (companyFieldForm.getChildSectorId() != null) {
            CompanyFieldCategory companyFieldCategory = this.companyFieldCategoryService.find(companyFieldForm.getChildSectorId());
            if (companyFieldCategory != null && !companyFieldCategory.getParentId().equals(companyFieldForm.getParentSectorId())) {
                throw new OwlRuntimeException(
                        this.messageAccessor.getMessage(MsgConstants.PARENT_SECTOR_NOT_EQUAL));
            }
        }

        CompanyField companyField = ConvertUtil.copyProperties(companyFieldForm, CompanyField.class);
@@ -374,7 +393,7 @@
            for (int rowIndex = 0; rowIndex < lastRowNum; rowIndex++) {
                //  0번은 헤더는 무시한다.
                Row row = sheet.getRow(rowIndex);
                //  헤더 정보를 추출한다 - 사용자 정의 필드 정보를 가져오기 위해
                //  헤더 정보를 추출한다
                if (rowIndex == 1) {
                    for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                        Cell cell = row.getCell(cellIndex);
@@ -418,6 +437,40 @@
        }
    }

    /**
     * cell NULL 체크 함수
     * @param cell Cell
     * @return boolean
     */
    private Boolean cellNullCheck (Cell cell, int rowIndex) {
        boolean result = false;
        
        //  문자형식인지 체크
        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK && cell.getCellType() != cell.CELL_TYPE_STRING) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_NOT_STRING_TYPE, rowIndex));
        }

        //  공백 제거
        if (cell != null && cell.getCellType() == cell.CELL_TYPE_STRING && cell.getStringCellValue() != null) {
            cell.setCellValue(cell.getStringCellValue().trim());
        }

        if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK && cell.getCellType() == cell.CELL_TYPE_STRING
                && cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) {
            result = true;
        }

        return result;
    }

    /*private void telTypeCheck (Cell cell, int rowIndex) {
        if (cell != null && cell.getCellType() != cell.CELL_TYPE_STRING) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_TEL_NOT_STRING_TYPE, rowIndex));
        }
    }*/

    //  엑셀 필드에 있는 정보를 업체 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,
@@ -426,7 +479,6 @@
        CompanyFieldForm companyFieldForm = new CompanyFieldForm();
        companyFieldForm.setRegisterId(this.webAppUtil.getLoginId());

        //  제목, 내용, 프로젝트 키, 이슈 타입, 우선순위, 중요도, 담당자, 시작일, 종료일, 사용자 정의 필드
        for (int cellIndex = 0; cellIndex < headers.size(); cellIndex++) {
            Cell cell = row.getCell(cellIndex);
            switch (cellIndex) {
@@ -437,81 +489,85 @@

                case 1:
                    //  url
                    this.setCompanyFormUrl(cell, companyFieldForm, rowIndex);
                    if (cellNullCheck(cell, rowIndex)) {
                        this.setCompanyFormUrl(cell, companyFieldForm, rowIndex);
                    }
                    break;

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

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

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

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

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

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

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

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

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

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

                case 12:
                    // 비고
                    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                    if (cellNullCheck(cell, rowIndex)) {
                        this.setCompanyFormMemo(cell, companyFieldForm, rowIndex);
                    }
                    break;
@@ -551,10 +607,19 @@
    private void setCompanyFormChildSector(Cell cell, Map<String, Map<String, Object>> childSectorMaps, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (cell != null) {
            Map<String, Object> childSectorMap = childSectorMaps.get(CommonUtil.convertExcelStringToCell(cell));

            //  대분류 없이 중분류만 입력했을경우
            if (companyFieldForm.getParentSectorId() == null) {
                throw new OwlRuntimeException(
                        this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_PARENT_SECTOR_IS_NULL, rowIndex));
            }

            //  중분류가 대분류에 속해있지 않는 경우
            if (!companyFieldForm.getParentSectorId().equals(MapUtil.getLong(childSectorMap, "parentId"))) {
                throw new OwlRuntimeException(
                        this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_PARENT_SECTOR_NOT_EQUAL, rowIndex));
            }

            companyFieldForm.setChildSectorId(MapUtil.getLong(childSectorMap, "id"));
        }
    }
@@ -589,19 +654,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(
@@ -613,7 +688,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(
@@ -624,7 +698,7 @@
    }

    private void setCompanyFormName(Cell cell, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (cell == null) {
        if (!cellNullCheck(cell, rowIndex)) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_COMPANY_NAME_IS_NULL, rowIndex));
        }
@@ -632,31 +706,48 @@
        String title = CommonUtil.convertExcelStringToCell(cell);

        //  업체명 유효성 체크
        this.verifyTitle(title);
        this.verifyTitle(title, null);
        companyFieldForm.setName(title);
    }

    private void setCompanyFormUrl(Cell cell, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (cell == null) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_URL_IS_NULL, rowIndex));
        }
        String url = CommonUtil.convertExcelStringToCell(cell);
        this.verifyUrl(url, null); //url 유효성 검사
        if (cell != null) {
            String url = CommonUtil.convertExcelStringToCell(cell);
            if (url.contains(" ")) {
                url = url.replace(" ", "");
            }
            this.verifyUrl(url, null); //url 유효성 검사

        companyFieldForm.setUrl(url);
            companyFieldForm.setUrl(url);
        }
    }

    //  업체명 유효성 체크
    private void verifyTitle(String title) {
        if (StringUtils.isEmpty(title)) {
    private void verifyTitle(String title, Long id) {
        List<CompanyField> companyFields = new ArrayList<>();

        //  업체명 중복 체크

        if (id != null) { //수정 일 경우
            companyFields = this.companyFieldRepository.findByNameAndIdNot(title, id);
        } else { // 추가 일 경우
            companyFields = this.companyFieldRepository.findByName(title);
        }
        if (companyFields != null && companyFields.size() > 0) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.ISSUE_NO_TITLE));
                    this.messageAccessor.getMessage(MsgConstants.COMPANY_NAME_ALREADY_IN_USE));
        }

        //  업체명 빈값 체크
        if (StringUtils.isEmpty(title)) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.COMPANY_NO_TITLE));
        }

        //  업체명 길이 체크
        if (title.length() > 300) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.ISSUE_TITLE_MAX_LENGTH_OUT));
                    this.messageAccessor.getMessage(MsgConstants.COMPANY_NAME_MAX_LENGTH_OUT));
        }
    }

@@ -788,13 +879,13 @@
    public CompanyField getCompany(Long id) {
        if (id == null) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.COMPANYFIELD_NOT_EXIST));
                    this.messageAccessor.getMessage(MsgConstants.COMPANY_NOT_EXIST));
        }
        CompanyField companyField = this.findOne(id);

        if (companyField == null) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.COMPANYFIELD_NOT_EXIST));
                    this.messageAccessor.getMessage(MsgConstants.COMPANY_NOT_EXIST));
        }
        return companyField;
    }