- api로 이슈 추가 시 url/ip로 업체 찾는 코드 수정

이민희

2022-03-17 916a3cbabe4e50062fce61ff6f2f5d46c05dfbd1

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

@@ -9,7 +9,6 @@
import kr.wisestone.owl.util.MapUtil;
import kr.wisestone.owl.web.condition.CompanyFieldCondition;
import kr.wisestone.owl.web.form.CompanyFieldForm;
import kr.wisestone.owl.web.form.IssueForm;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.transaction.annotation.Transactional;
@@ -33,6 +32,7 @@

import javax.servlet.http.HttpServletRequest;
import java.util.*;
import java.util.regex.Pattern;

@Service
public class CompanyFieldServiceImpl extends AbstractServiceImpl<CompanyField, Long, JpaRepository<CompanyField, Long>> implements CompanyFieldService {
@@ -89,25 +89,159 @@
            this.verifyUrl(companyFieldForm.getUrl(), null);
        }

        if (companyFieldForm.getIpStarts() != null && companyFieldForm.getIpStarts().size() > 0
                && companyFieldForm.getIpEnds() != null && companyFieldForm.getIpEnds().size() > 0) {

            for (int i=0; i<companyFieldForm.getIpStarts().size(); i++) {
                //  아이피 유효성 체크
                this.verifyIp(companyFieldForm.getIpStarts().get(i), companyFieldForm.getIpEnds().get(i), null);
            }

            String startIps = companyFieldForm.getIpStarts().toString();
            //  대괄호 제거
            startIps = this.removeSquare(startIps);
            companyFieldForm.setIpStart(startIps.trim());

            String endIps = companyFieldForm.getIpEnds().toString();
            //  대괄호 제거
            endIps = this.removeSquare(endIps);
            companyFieldForm.setIpEnd(endIps.trim());
        }

        if (companyFieldForm.getTelList() != null && companyFieldForm.getTelList().size() > 0) {
            String tels = companyFieldForm.getTelList().toString();
            if (tels.contains("[")) {
                tels = tels.substring(1, tels.indexOf("]"));
            }
            //  대괄호 제거
            tels = this.removeSquare(tels);
            companyFieldForm.setTel(tels.trim());
        }
        if (companyFieldForm.getEmailList() != null && companyFieldForm.getEmailList().size() > 0) {
            String emails = companyFieldForm.getEmailList().toString();
            String email = "";
            if (emails.contains("[")) {
                email = emails.substring(1, emails.indexOf("]"));
            }
            companyFieldForm.setEmail(email.trim());
            //  대괄호 제거
            emails = this.removeSquare(emails);
            companyFieldForm.setEmail(emails.trim());
        }

        CompanyField companyField = ConvertUtil.copyProperties(companyFieldForm, CompanyField.class);
        companyFieldRepository.saveAndFlush(companyField);
        return companyField;
    }

    /**
     * 대괄호([]) 제거 함수
     * @param str String
     * @return str
     */
    private String removeSquare(String str) {
        if (str.contains("[")) {
            str = str.substring(1, str.indexOf("]"));
        }
        
        return str;
    }

    /**
     * IP 유효성 체크
     * @param ip String
     * @param ip2 String
     * @param id Long
     */
    private void verifyIp(String ip, String ip2, Long id) {
        if (!StringUtils.isEmpty(ip)) {
            if (!Pattern.matches("^(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\" +
                    ".(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$", ip)) {
                throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.IP_NOT_INVALID));
            }
        }
        if (!StringUtils.isEmpty(ip2)) {
            if (!Pattern.matches("^(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\" +
                    ".(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)$", ip2)) {
                throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.IP_NOT_INVALID));
            }
        }

        if (!StringUtils.isEmpty(ip) && !StringUtils.isEmpty(ip2)) {
            long ipStart = ConvertUtil.ipToLong(ip);
            long ipEnd = ConvertUtil.ipToLong(ip2);
            if (ipEnd < ipStart) {
                throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.IP_START_NOT_LARGER_THAN_END));
            }

            List<CompanyField> companyFields = Lists.newArrayList();
            CompanyFieldCondition condition = new CompanyFieldCondition();
            if (id != null) {
                condition.setId(id);
                companyFields = this.companyFieldRepository.findByIdNot(condition.getId());
            } else {
                companyFields = this.companyFieldRepository.findAll();
            }

            //  IP대역대 중복 체크
            this.ipOverlapChk(companyFields, ipStart, ipEnd);

            /*if (companyFields != null && companyFields.size() > 0) {
                throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.COMPANY_EXIST_IP));
            }*/
        }
    }

    /**
     * ip 대역대 중복 체크
     * @param companyFields List<CompanyField>
     */
    private void ipOverlapChk(List<CompanyField> companyFields, long ipStart, long ipEnd) {
        String[] ipStartArr = null;
        String[] ipEndArr = null;
        List<String> startIpList = Lists.newArrayList();
        List<String> endIpList = Lists.newArrayList();
        String startIp = "";
        String endIp = "";
        List<Long> startIps = Lists.newArrayList();
        List<Long> endIps = Lists.newArrayList();

        if (companyFields.size() > 0) {
            for (CompanyField companyField : companyFields) {
                if(companyField.getIpStart() != null && companyField.getIpEnd() != null) {
                    startIp = companyField.getIpStart();
                    if (startIp.contains(" ")) {
                        startIp = startIp.replace(" ","");
                    }
                    if (startIp.contains(",")) {
                        ipStartArr = startIp.split(",");
                        startIpList.addAll(Arrays.asList(ipStartArr));
                    } else {
                        startIpList.add(startIp.trim());
                    }

                    endIp = companyField.getIpEnd();
                    if (endIp.contains(" ")) {
                        endIp = endIp.replace(" ","");
                    }
                    if (endIp.contains(",")) {
                        ipEndArr = endIp.split(",");
                        endIpList.addAll(Arrays.asList(ipEndArr));
                    } else {
                        endIpList.add(endIp.trim());
                    }
                }
            }
            if (startIpList.size() > 0) {
                for (String ipS : startIpList) {
                    long start = ConvertUtil.ipToLong(ipS);
                    startIps.add(start);
                }
                for (String ipE : endIpList) {
                    long end = ConvertUtil.ipToLong(ipE);
                    endIps.add(end);
                }
            }

            for (int i=0; i<startIps.size(); i++) {
                if (startIps.get(i) >= ipStart && startIps.get(i) <= ipEnd || endIps.get(i) >= ipStart && endIps.get(i) <= ipEnd
                    || ipStart >= startIps.get(i) && ipStart <= endIps.get(i) || ipEnd >= startIps.get(i) && ipEnd <= endIps.get(i)) {
                    throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.COMPANY_EXIST_IP));
                }
            }
        }
    }

    //  url 유효성 체크
@@ -117,14 +251,33 @@
                    this.messageAccessor.getMessage(MsgConstants.COMPANY_NOT_URL));
        }*/
        if (!StringUtils.isEmpty(url)) {
            CompanyField companyField;
            if(id == null){
                companyField = this.companyFieldRepository.findByUrl(url);
            List<CompanyField> companyFieldList = Lists.newArrayList();
            CompanyFieldCondition condition = new CompanyFieldCondition();
            String[] urlArr = null;
            List<String> urls = Lists.newArrayList();

            if (url.contains(" ")) {
                url = url.replace(" ","");
            }
            if (url.contains(",")) {
                urlArr = url.split(",");
                urls.addAll(Arrays.asList(urlArr));
            } else {
                companyField = this.companyFieldRepository.findByUrlAndIdNot(url,id);
                urls.add(url);
            }

            if (companyField != null) {
            if (urls.size() > 0) {
                condition.setUrls(urls);

                if(id == null){
                    companyFieldList = this.companyFieldMapper.findByUrls(condition);
                } else {
                    condition.setId(id);
                    companyFieldList = this.companyFieldMapper.findByUrlsAndIdNot(condition);
                }
            }

            if (companyFieldList != null && companyFieldList.size() > 0) {
                throw new OwlRuntimeException(
                        this.messageAccessor.getMessage(MsgConstants.COMPANY_USED_URL));
            }
@@ -186,6 +339,9 @@
                companyFieldVo.setRegionName(region.getUseValue());
            }
        }
        if (companyField.getStatusName() != null && !companyField.getStatusName().equals("")) {
            companyFieldVo.setStatusName(companyField.getStatusName());
        }
        return companyFieldVo;
    }

@@ -232,18 +388,35 @@
            this.verifyUrl(companyFieldForm.getUrl(), companyFieldForm.getId());
        }

        if (companyFieldForm.getIpStarts() != null && companyFieldForm.getIpStarts().size() > 0
                && companyFieldForm.getIpEnds() != null && companyFieldForm.getIpEnds().size() > 0) {

            for (int i=0; i<companyFieldForm.getIpStarts().size(); i++) {
                //  아이피 유효성 체크
                this.verifyIp(companyFieldForm.getIpStarts().get(i), companyFieldForm.getIpEnds().get(i), companyFieldForm.getId());
            }

            String startIps = companyFieldForm.getIpStarts().toString();
            //  대괄호 제거
            startIps = this.removeSquare(startIps);
            companyFieldForm.setIpStart(startIps.trim());

            String endIps = companyFieldForm.getIpEnds().toString();
            //  대괄호 제거
            endIps = this.removeSquare(endIps);
            companyFieldForm.setIpEnd(endIps.trim());
        }

        if (companyFieldForm.getTelList() != null && companyFieldForm.getTelList().size() > 0) {
            String tels = companyFieldForm.getTelList().toString();
            if (tels.contains("[")) {
                tels = tels.substring(1, tels.indexOf("]"));
            }
            //  대괄호 제거
            tels = this.removeSquare(tels);
            companyFieldForm.setTel(tels.trim());
        }
        if (companyFieldForm.getEmailList() != null && companyFieldForm.getEmailList().size() > 0) {
            String emails = companyFieldForm.getEmailList().toString();
            if (emails.contains("[")) {
                emails = emails.substring(1, emails.indexOf("]"));
            }
            //  대괄호 제거
            emails = this.removeSquare(emails);
            companyFieldForm.setEmail(emails.trim());
        }

@@ -283,6 +456,8 @@
        excelInfo.setFileName(this.messageAccessor.message("common.registerExcelCompanyField")); // 엑셀로 업체 등록하기
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("companyField.companyName"), 20, ExportExcelAttrVo.ALIGN_CENTER)); // 업체명
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("companyField.companyUrl"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // url
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("companyField.companyIpStart"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // ip 시작 주소
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("companyField.companyIpEnd"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // ip 종료 주소
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("isp.ispName"), 20, ExportExcelAttrVo.ALIGN_CENTER)); // isp명
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("Hosting.HostingName"), 20, ExportExcelAttrVo.ALIGN_CENTER)); // 호스팅명
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("companyField.companyTel"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 연락처
@@ -355,7 +530,7 @@
        }
    }

    //  엑셀 import 로 이슈를 등록한다.
    //  엑셀 import 로 업체를 등록한다.
    @Override
    @Transactional
    public void importExcel(MultipartFile multipartFile) throws Exception {
@@ -419,6 +594,8 @@
                if (rowIndex > 1) {
                    //  업체로 등록하기 위해 CompanyFieldForm 에 데이터를 셋팅한다.
                    CompanyFieldForm newCompanyFieldForm = this.setCompanyFieldFormToExcelField(row, (rowIndex + 1), ispFieldMaps, hostingFieldMaps, companyTypeMaps, parentSectorMaps, childSectorMaps, regionMaps, statusMaps, headers);
                    //  ip 유효성 체크
                    this.verifyIp(newCompanyFieldForm.getIpStart(), newCompanyFieldForm.getIpEnd(), null);

                    companyFieldForms.add(newCompanyFieldForm);
                }
@@ -440,38 +617,53 @@
    }

    /**
     * cell String으로 변환 함수
     * @param cell Cell
     * @param isNull boolean
     * @return String
     */
    private String stringToCell (Cell cell, boolean isNull) {
        String cellStr = "";
        if (!isNull) {
            cellStr = CommonUtil.convertExcelStringToCell(cell);
            //  공백 제거
            cell.setCellValue(cellStr.trim());
        } else {
            cell.setCellValue(cellStr);
        }
        return cellStr;
    }

    /**
     * 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));
    private Boolean cellNullCheck (Cell cell) {
        int cellType = cell.getCellType();
        if (cellType < Cell.CELL_TYPE_BLANK) {
            if (cellType == Cell.CELL_TYPE_STRING) {
                if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) {
                    return false;
                }
            } else {
                return false;
            }
        }

        //  공백 제거
        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;
        return true;
    }

    /*private void telTypeCheck (Cell cell, int rowIndex) {
    /**
     * 전화번호 CellType 체크 함수
     * @param cell Cell
     * @param rowIndex int
     */
    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,
@@ -483,121 +675,143 @@

        for (int cellIndex = 0; cellIndex < headers.size(); cellIndex++) {
            Cell cell = row.getCell(cellIndex);

            String cellStr = "";
            boolean isNull = true;

            if (cell != null) {
                isNull = cellNullCheck(cell);
                cellStr = stringToCell(cell, isNull); //cell을 String으로 변환
            }

            switch (cellIndex) {
                case 0:
                    //  업체명
                    this.setCompanyFormName(cell, companyFieldForm, rowIndex);
                    if (isNull) {
                        throw new OwlRuntimeException(
                                this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_COMPANY_NAME_IS_NULL, rowIndex));
                    }
                    this.setCompanyFormName(cellStr, companyFieldForm);
                    break;

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

                case 2:
                    // isp명
                    if (cellNullCheck(cell, rowIndex)) {
                        this.setCompanyFormIspName(cell, ispFieldMaps, companyFieldForm, rowIndex);
                    }
                    //  ip시작주소
                    this.setCompanyFormIpStart(cellStr, companyFieldForm, isNull);
                    break;

                case 3:
                    // 호스팅명
                    if (cellNullCheck(cell, rowIndex)) {
                        this.setCompanyFormHostingName(cell, hostingFieldMaps, companyFieldForm, rowIndex);
                    }
                    //  ip종료주소
                    this.setCompanyFormIpEnd(cellStr, companyFieldForm, isNull);
                    break;

                case 4:
                    // 연락처
                    if (cellNullCheck(cell, rowIndex)) {
                        //this.telTypeCheck(cell, rowIndex);
                        this.setCompanyFormTel(cell, companyFieldForm, rowIndex);
                    }
                    // isp명
                    this.setCompanyFormIspName(cellStr, ispFieldMaps, companyFieldForm, rowIndex, isNull);
                    break;

                case 5:
                    // 이메일
                    if (cellNullCheck(cell, rowIndex)) {
                        this.setCompanyFormEmail(cell, companyFieldForm, rowIndex);
                    }
                    // 호스팅명
                    this.setCompanyFormHostingName(cellStr, hostingFieldMaps, companyFieldForm, rowIndex, isNull);
                    break;

                case 6:
                    // 담당자
                    if (cellNullCheck(cell, rowIndex)) {
                        this.setCompanyFormManager(cell, companyFieldForm, rowIndex);
                    }
                    // 연락처
                    telTypeCheck(cell, rowIndex);
                    this.setCompanyFormTel(cellStr, companyFieldForm, isNull);
                    break;

                case 7:
                    // 기업구분
                    if (cellNullCheck(cell, rowIndex)) {
                        this.setCompanyFormCompanyType(cell, companyTypeMaps, companyFieldForm, rowIndex);
                    }
                    // 이메일
                    this.setCompanyFormEmail(cellStr, companyFieldForm, isNull);
                    break;

                case 8:
                    // 업종(대분류)
                    if (cellNullCheck(cell, rowIndex)) {
                        this.setCompanyFormParentSector(cell, parentSectorMaps, companyFieldForm, rowIndex);
                    }
                    // 담당자
                    this.setCompanyFormManager(cellStr, companyFieldForm, isNull);
                    break;

                case 9:
                    // 업종(중분류)
                    if (cellNullCheck(cell, rowIndex)) {
                        this.setCompanyFormChildSector(cell, childSectorMaps, companyFieldForm, rowIndex);
                    }
                    // 기업구분
                    this.setCompanyFormCompanyType(cellStr, companyTypeMaps, companyFieldForm, rowIndex, isNull);
                    break;

                case 10:
                    // 지역
                    if (cellNullCheck(cell, rowIndex)) {
                        this.setCompanyFormRegion(cell, regionMaps, companyFieldForm, rowIndex);
                    }
                    // 업종(대분류)
                    this.setCompanyFormParentSector(cellStr, parentSectorMaps, companyFieldForm, rowIndex, isNull);
                    break;

                case 11:
                    // 상태
                    if (cellNullCheck(cell, rowIndex)) {
                        this.setCompanyFormStatus(cell, statusMaps, companyFieldForm, rowIndex);
                    }
                    // 업종(중분류)
                    this.setCompanyFormChildSector(cellStr, childSectorMaps, companyFieldForm, rowIndex, isNull);
                    break;

                case 12:
                    // 비고
                    if (cellNullCheck(cell, rowIndex)) {
                        this.setCompanyFormMemo(cell, companyFieldForm, rowIndex);
                    }
                    // 지역
                    this.setCompanyFormRegion(cellStr, regionMaps, companyFieldForm, rowIndex, isNull);
                    break;

                case 13:
                    // 상태
                    this.setCompanyFormStatus(cellStr, statusMaps, companyFieldForm, isNull);
                    break;

                case 14:
                    // 비고
                    this.setCompanyFormMemo(cellStr, companyFieldForm, isNull);
            }
        }

        return companyFieldForm;
    }

    private void setCompanyFormMemo(Cell cell, CompanyFieldForm companyFieldForm, int rowIndex) {
        companyFieldForm.setMemo(CommonUtil.convertExcelStringToCell(cell));
    private void setCompanyFormIpEnd(String ipEnd, CompanyFieldForm companyFieldForm, boolean isNull) {
        if (!isNull) {
            if (ipEnd.contains(" ")) {
                ipEnd = ipEnd.replace(" ", "");
            }
            this.verifyIp(ipEnd, null, null); //ip 유효성 검사

            companyFieldForm.setIpEnd(ipEnd);
        }
    }

    private void setCompanyFormStatus(Cell cell, Map<String, Map<String, Object>> statusMaps, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (cell != null) {
            Map<String, Object> statusMap = statusMaps.get(CommonUtil.convertExcelStringToCell(cell));
    private void setCompanyFormIpStart(String ipStart, CompanyFieldForm companyFieldForm, boolean isNull) {
        if (!isNull) {
            if (ipStart.contains(" ")) {
                ipStart = ipStart.replace(" ", "");
            }
            this.verifyIp(ipStart, null, null); //ip 유효성 검사

            companyFieldForm.setIpStart(ipStart);
        }
    }

    private void setCompanyFormMemo(String cellStr, CompanyFieldForm companyFieldForm, boolean isNull) {
        if (!isNull) {
            companyFieldForm.setMemo(cellStr);
        }
    }

    private void setCompanyFormStatus(String cellStr, Map<String, Map<String, Object>> statusMaps, CompanyFieldForm companyFieldForm, boolean isNull) {
        if (!isNull) {
            Map<String, Object> statusMap = statusMaps.get(cellStr);
            if (MapUtil.getLong(statusMap, "id") != null) {
                companyFieldForm.setStatusId(MapUtil.getLong(statusMap, "id"));
            } else {
                companyFieldForm.setStatusId(120L);
            }
            companyFieldForm.setStatusName(CommonUtil.convertExcelStringToCell(cell));
            companyFieldForm.setStatusName(cellStr);
        }
    }

    private void setCompanyFormRegion(Cell cell, Map<String, Map<String, Object>> regionMaps, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (cell != null) {
            Map<String, Object> regionMap = regionMaps.get(CommonUtil.convertExcelStringToCell(cell));
    private void setCompanyFormRegion(String cellStr, Map<String, Map<String, Object>> regionMaps, CompanyFieldForm companyFieldForm, int rowIndex, boolean isNull) {
        if (!isNull) {
            Map<String, Object> regionMap = regionMaps.get(cellStr);
            if (regionMap == null) {
                throw new OwlRuntimeException(
                        this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_REGION_NOT_EXIST, rowIndex));
@@ -606,9 +820,9 @@
        }
    }

    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));
    private void setCompanyFormChildSector(String cellStr, Map<String, Map<String, Object>> childSectorMaps, CompanyFieldForm companyFieldForm, int rowIndex, boolean isNull) {
        if (!isNull) {
            Map<String, Object> childSectorMap = childSectorMaps.get(cellStr);

            //  대분류 없이 중분류만 입력했을경우
            if (companyFieldForm.getParentSectorId() == null) {
@@ -626,9 +840,9 @@
        }
    }

    private void setCompanyFormParentSector(Cell cell, Map<String, Map<String, Object>> parentSectorMaps, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (cell != null) {
            Map<String, Object> parentSectorMap = parentSectorMaps.get(CommonUtil.convertExcelStringToCell(cell));
    private void setCompanyFormParentSector(String cellStr, Map<String, Map<String, Object>> parentSectorMaps, CompanyFieldForm companyFieldForm, int rowIndex, boolean isNull) {
        if (!isNull) {
            Map<String, Object> parentSectorMap = parentSectorMaps.get(cellStr);
            if (parentSectorMap == null) {
                throw new OwlRuntimeException(
                        this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_PARENT_SECTOR_NOT_EXIST, rowIndex));
@@ -637,9 +851,9 @@
        }
    }

    private void setCompanyFormCompanyType(Cell cell, Map<String, Map<String, Object>> companyTypeMaps, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (cell != null) {
            Map<String, Object> companyTypeMap = companyTypeMaps.get(CommonUtil.convertExcelStringToCell(cell));
    private void setCompanyFormCompanyType(String cellStr, Map<String, Map<String, Object>> companyTypeMaps, CompanyFieldForm companyFieldForm, int rowIndex, boolean isNull) {
        if (!isNull) {
            Map<String, Object> companyTypeMap = companyTypeMaps.get(cellStr);
            if (companyTypeMap == null) {
                throw new OwlRuntimeException(
                        this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_COMPANY_TYPE_NOT_EXIST, rowIndex));
@@ -648,38 +862,68 @@
        }
    }

    private void setCompanyFormManager(Cell cell, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (cell != null) {
            companyFieldForm.setManager(CommonUtil.convertExcelStringToCell(cell));
    private void setCompanyFormManager(String manager, CompanyFieldForm companyFieldForm, boolean isNull) {
        if (!isNull) {
            companyFieldForm.setManager(manager);
        }
    }

    private void setCompanyFormEmail(Cell cell, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (cell != null) {
            String email = CommonUtil.convertExcelStringToCell(cell);
            if (CommonUtil.convertExcelStringToCell(cell).contains(" ")) {
                email = CommonUtil.convertExcelStringToCell(cell).replace(" ", "");
            }
    private void setCompanyFormEmail(String email, CompanyFieldForm companyFieldForm, boolean isNull) {
        if (!isNull) {
            //  이메일 유효성 검사
            email = this.verifyEmail(email);
            companyFieldForm.setEmail(email);
        }
    }

    private void setCompanyFormTel(Cell cell, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (cell != null) {
            String tel = CommonUtil.convertExcelStringToCell(cell);
            if (tel.contains("-")) {
                tel = tel.replace("-", "");
            }
            if (tel.contains(" ")) {
                tel = tel.replace(" ", "");
            }
    /**
     * 이메일 유효성 검사
     * @param email String
     * @return String
     */
    private String verifyEmail(String email) {
        /*if (!Pattern.matches("^[a-zA-Z0-9._-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,4}$", email)) {
            throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.EMAIL_NOT_INVALID));
        }*/

        if (email.contains(" ")) {
            email = email.replace(" ", "");
        }

        return email;
    }

    private void setCompanyFormTel(String tel, CompanyFieldForm companyFieldForm, boolean isNull) {
        if (!isNull) {
            //  연락처 유효성 검사
            tel = this.verifyTel(tel);
            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));
    /**
     * 연락처 유효성 검사
     * @param tel String
     * @return String
     */
    private String verifyTel(String tel) {
        if (!Pattern.matches("^[0-9-]{2,20}$", tel)) {
            throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.TEL_NOT_INVALID));
        }

        if (tel.contains("-")) {
            tel = tel.replace("-", "");
        }
        if (tel.contains(" ")) {
            tel = tel.replace(" ", "");
        }

        return tel;
    }

    private void setCompanyFormHostingName(String cellStr, Map<String, HostingField> hostingFieldMaps, CompanyFieldForm companyFieldForm, int rowIndex, boolean isNull) {
        if (!isNull) {
            HostingField hostingField = hostingFieldMaps.get(cellStr);
            if (hostingField == null) {
                throw new OwlRuntimeException(
                        this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_HOSTING_NOT_EXIST, rowIndex));
@@ -688,9 +932,9 @@
        }
    }

    private void setCompanyFormIspName(Cell cell, Map<String, IspField> ispFieldMaps, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (cell != null) {
            IspField ispField = ispFieldMaps.get(CommonUtil.convertExcelStringToCell(cell));
    private void setCompanyFormIspName(String cellStr, Map<String, IspField> ispFieldMaps, CompanyFieldForm companyFieldForm, int rowIndex, boolean isNull) {
        if (!isNull) {
            IspField ispField = ispFieldMaps.get(cellStr);
            if (ispField == null) {
                throw new OwlRuntimeException(
                        this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_ISP_NOT_EXIST, rowIndex));
@@ -699,22 +943,14 @@
        }
    }

    private void setCompanyFormName(Cell cell, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (!cellNullCheck(cell, rowIndex)) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_COMPANY_NAME_IS_NULL, rowIndex));
        }

        String title = CommonUtil.convertExcelStringToCell(cell);

    private void setCompanyFormName(String title, CompanyFieldForm companyFieldForm) {
        //  업체명 유효성 체크
        this.verifyTitle(title, null);
        companyFieldForm.setName(title);
    }

    private void setCompanyFormUrl(Cell cell, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (cell != null) {
            String url = CommonUtil.convertExcelStringToCell(cell);
    private void setCompanyFormUrl(String url, CompanyFieldForm companyFieldForm, boolean isNull) {
        if (!isNull) {
            if (url.contains(" ")) {
                url = url.replace(" ", "");
            }
@@ -793,6 +1029,7 @@
        excelInfo.addAttrInfos(new ExportExcelAttrVo("tel", this.messageAccessor.message("companyField.companyTel"), 10, ExportExcelAttrVo.ALIGN_CENTER));
        excelInfo.addAttrInfos(new ExportExcelAttrVo("email", this.messageAccessor.message("companyField.companyEmail"), 10, ExportExcelAttrVo.ALIGN_CENTER));
        excelInfo.addAttrInfos(new ExportExcelAttrVo("url", this.messageAccessor.message("companyField.companyUrl"), 10, ExportExcelAttrVo.ALIGN_CENTER));
        excelInfo.addAttrInfos(new ExportExcelAttrVo("ipRange", this.messageAccessor.message("companyField.companyIp"), 10, ExportExcelAttrVo.ALIGN_CENTER));
        excelInfo.addAttrInfos(new ExportExcelAttrVo("companyTypeName", this.messageAccessor.message("companyField.companyTypeName"), 10, ExportExcelAttrVo.ALIGN_CENTER));
        excelInfo.addAttrInfos(new ExportExcelAttrVo("parentSectorName", this.messageAccessor.message("companyField.parentSectorName"), 10, ExportExcelAttrVo.ALIGN_CENTER));
        excelInfo.addAttrInfos(new ExportExcelAttrVo("childSectorName", this.messageAccessor.message("companyField.childSectorName"), 10, ExportExcelAttrVo.ALIGN_CENTER));