package kr.wisestone.owl.service.impl;

import kr.wisestone.owl.domain.*;
import kr.wisestone.owl.domain.enumType.CompanyFieldCategoryType;
import kr.wisestone.owl.repository.HostingFieldRepository;
import kr.wisestone.owl.repository.IspFieldRepository;
import kr.wisestone.owl.service.*;
import kr.wisestone.owl.util.CommonUtil;
import kr.wisestone.owl.util.MapUtil;
import kr.wisestone.owl.web.condition.CompanyFieldCondition;
import kr.wisestone.owl.web.form.CompanyFieldForm;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.ui.Model;
import com.google.common.collect.Lists;
import kr.wisestone.owl.common.ExcelConditionCheck;
import kr.wisestone.owl.constant.Constants;
import kr.wisestone.owl.constant.MsgConstants;
import kr.wisestone.owl.exception.OwlRuntimeException;
import kr.wisestone.owl.mapper.CompanyFieldMapper;
import kr.wisestone.owl.repository.CompanyFieldRepository;
import kr.wisestone.owl.util.ConvertUtil;
import kr.wisestone.owl.vo.*;
import kr.wisestone.owl.web.view.ExcelView;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;

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 {

    @Autowired
    private CompanyFieldRepository companyFieldRepository;

    @Autowired
    private CompanyFieldMapper companyFieldMapper;

    @Autowired
    private IspFieldRepository ispFieldRepository;

    @Autowired
    private HostingFieldRepository hostingFieldRepository;

    @Autowired
    private IspFieldService ispFieldService;

    @Autowired
    private HostingFieldService hostingFieldService;

    @Autowired
    private CompanyFieldCategoryService companyFieldCategoryService;

    @Autowired
    private UserService userService;

    @Autowired
    private WorkspaceService workspaceService;

    @Autowired
    private ExcelView excelView;

    @Autowired
    private ExcelConditionCheck excelConditionCheck;

    @Override
    protected JpaRepository<CompanyField, Long> getRepository() {
        return this.companyFieldRepository;
    }

    private static final int EXCEL_DOWNLOAD_MAX_ROWS = 10000;   //  excel download 제한
    private static final int EXCEL_IMPORT_MAX_ROWS = 10000; //  excel import 제한

    // 업체 추가
    @Override
    public CompanyField addCompany(CompanyFieldForm companyFieldForm) {
        //  업체명 중복 체크
        this.verifyTitle(companyFieldForm.getName(), null);

        if (companyFieldForm.getIpStart() != null && companyFieldForm.getIpEnd() != null) {
            //  아이피 유효성 체크
            this.verifyIp(companyFieldForm.getIpStart(), companyFieldForm.getIpEnd());
        }

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

        if (companyFieldForm.getTelList() != null && companyFieldForm.getTelList().size() > 0) {
            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 = 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);
        companyFieldRepository.saveAndFlush(companyField);
        return companyField;
    }

    /**
     * IP 유효성 체크
     * @param ip String
     */
    private void verifyIp(String ip, String ip2) {
        if (ip2 == null) {
            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));
                }
            }
        } else {
            if (!StringUtils.isEmpty(ip)) {
                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));
                }
            }
        }
    }

    //  url 유효성 체크
    private void verifyUrl(String url, Long id) {
        /*if (StringUtils.isEmpty(url)) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.COMPANY_NOT_URL));
        }*/
        if (!StringUtils.isEmpty(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 {
                urls.add(url);
            }

            if (urls.size() > 0) {
                condition.setUrl(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));
            }
        }
    }

    // 업체 목록을 가져온다.
    @Override
    public List<CompanyFieldVo> findCompany(Map<String, Object> resJsonData,
                                            CompanyFieldCondition condition, Pageable pageable) {
        condition.setPage(pageable.getPageNumber() * pageable.getPageSize());
        condition.setPageSize(pageable.getPageSize());

        List<Map<String, Object>> results = this.companyFieldMapper.find(condition);
        Long totalCompanyCount = this.companyFieldMapper.count(condition);

        return this.convertCompanyVoToMap(results, totalCompanyCount, pageable, resJsonData);
    }

    public List<Map<String, Object>> find(CompanyFieldCondition condition) {
        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
     */
    @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());
            }
        }
        if (companyField.getStatusName() != null && !companyField.getStatusName().equals("")) {
            companyFieldVo.setStatusName(companyField.getStatusName());
        }
        return companyFieldVo;
    }

    // 업체 상세 조회한다.
    @Override
    @Transactional
    public void detailCompany(Map<String, Object> resJsonData, CompanyFieldCondition companyFieldCondition) {
        CompanyFieldVo companyFieldVo = new CompanyFieldVo();
        IspFieldVo ispFieldVo = new IspFieldVo();
        HostingFieldVo hostingFieldVo = new HostingFieldVo();

        IspField ispField = new IspField();
        HostingField hostingField = new HostingField();

        Long companyId = companyFieldCondition.getId();
        if (companyId != null) {
            CompanyField companyField = this.getCompany(companyId);
            if(companyField.getIspId() != null && companyField.getIspId() != -1){
                ispField = this.ispFieldRepository.getOne(companyField.getIspId());
            }
            if(companyField.getHostingId() != null && companyField.getHostingId() != -1){
                hostingField = this.hostingFieldRepository.getOne(companyField.getHostingId());
            }
            companyFieldVo = ConvertUtil.copyProperties(companyField, CompanyFieldVo.class);
            ispFieldVo = ConvertUtil.copyProperties(ispField, IspFieldVo.class);
            hostingFieldVo = ConvertUtil.copyProperties(hostingField, HostingFieldVo.class);

            CreateCompanyFieldCategory(companyFieldVo, companyField);

            companyFieldVo.setIspFieldVo(ispFieldVo);
            companyFieldVo.setHostingFieldVo(hostingFieldVo);
        }
        resJsonData.put(Constants.REQ_KEY_CONTENT, companyFieldVo);
    }

    // 업체 정로를 수정한다.
    @Override
    public void modifyCompany(CompanyFieldForm companyFieldForm) {
        //  업체명 유효성 체크
        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();
            if (tels.contains("[")) {
                tels = tels.substring(1, tels.indexOf("]"));
            }
            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("]"));
            }
            companyFieldForm.setEmail(emails.trim());
        }

        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);
        companyFieldRepository.saveAndFlush(companyField);
    }


    // 업체를 삭제한다.
    @Override
    public void removeCompany(CompanyFieldForm companyFieldForm) {
        if (companyFieldForm.getRemoveIds().size() < 1) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.COMPANY_REMOVE_NOT_SELECT));
        }
        for (Long id : companyFieldForm.getRemoveIds()) {
            this.companyFieldRepository.deleteById(id);
            this.companyFieldRepository.flush();
        }
    }

    //  업체 Import 용 엑셀 템플릿 다운로드
    @Override
    @Transactional
    public ModelAndView downloadExcelTemplate(HttpServletRequest request, Model model) {

        ExportExcelVo excelInfo = new ExportExcelVo();
        excelInfo.setHideCount(true);
        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)); // 연락처
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("companyField.companyEmail"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 이메일
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("companyField.companyManager"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 담당자
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("companyField.companyTypeName"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 기업구분
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("companyField.parentSectorName"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 업종(대분류)
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("companyField.childSectorName"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 업종(중분류)
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("companyField.regionName"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 지역
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("companyField.statusName"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 상태
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("companyField.companyMemo"), 40, ExportExcelAttrVo.ALIGN_CENTER)); // 비고

        //  엑셀에 넣을 데이터 - CompanyFieldVos 데이터를 엑셀에서 표시할 수 있는 데이터로 변경한다.
        excelInfo.setDatas(Lists.newArrayList(new CompanyFieldVo()));

        model.addAttribute(Constants.EXCEL, excelInfo);
        return new ModelAndView(this.excelView);
    }

    //  업로드 파일 확장자 체크
    private void verifyMultipartFileExtension(MultipartFile multipartFile) {
        multipartFile.getOriginalFilename();

        int pos = multipartFile.getOriginalFilename().lastIndexOf(".");
        String ext = multipartFile.getOriginalFilename().substring(pos + 1);

        if (!ext.equals("xlsx")) {
            throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.EXCEL_NOT_EXTENSION));
        }
    }

    //  업체의 주요 속성을 map 에 저장하여 엑셀 import 에서 지정한 대상(업체 속성)을 빠르게 찾을 수 있게 한다.
    private void CompanyFieldAttributeMapToList(Map<String, IspField> ispFieldMaps, Map<String, HostingField> hostingFieldMaps,
                                                             Map<String, Map<String, Object>> companyTypeMaps, Map<String, Map<String, Object>> parentSectorMaps,
                                                             Map<String, Map<String, Object>> childSectorMaps, Map<String, Map<String, Object>> regionMaps, Map<String, Map<String, Object>> statusMaps) {

        List<IspField> ispFields = this.ispFieldService.findAll();
        for (IspField ispField : ispFields) {
            ispFieldMaps.put(ispField.getName(), ispField);
        }

        List<HostingField> hostingFields = this.hostingFieldService.findAll();
        for (HostingField hostingField : hostingFields) {
            hostingFieldMaps.put(hostingField.getName(), hostingField);
        }

        List<Map<String, Object>> companyTypes = this.companyFieldCategoryService.findByType(CompanyFieldCategoryType.COMPANYTYPE);
        for (Map<String, Object> companyType : companyTypes) {
            companyTypeMaps.put(MapUtil.getString(companyType, "useValue"), companyType);
        }

        List<Map<String, Object>> parentSectors = this.companyFieldCategoryService.findByType(CompanyFieldCategoryType.PARENTSECTOR);
        for (Map<String, Object> parentSector : parentSectors) {
            parentSectorMaps.put(MapUtil.getString(parentSector, "useValue"), parentSector);
        }

        List<Map<String, Object>> childSectors = this.companyFieldCategoryService.findByType(CompanyFieldCategoryType.CHILDSECTOR);
        for (Map<String, Object> childSector : childSectors) {
            childSectorMaps.put(MapUtil.getString(childSector, "useValue"), childSector);
        }

        List<Map<String, Object>> regions = this.companyFieldCategoryService.findByType(CompanyFieldCategoryType.REGION);
        for (Map<String, Object> region : regions) {
            regionMaps.put(MapUtil.getString(region, "useValue"), region);
        }

        List<Map<String, Object>> statuses = this.companyFieldCategoryService.findByType(CompanyFieldCategoryType.STATUS);
        for (Map<String, Object> status : statuses) {
            statusMaps.put(MapUtil.getString(status, "useValue"), status);
        }
    }

    //  엑셀 import 로 업체를 등록한다.
    @Override
    @Transactional
    public void importExcel(MultipartFile multipartFile) throws Exception {
        //  사용하고 있는 업무 공간이 활성 상태인지 확인한다. 사용 공간에서 로그인한 사용자가 비활성인지 확인한다.
        this.workspaceService.checkUseWorkspace();

        if (multipartFile != null) {
            //  업로드 파일 확장자 체크
            this.verifyMultipartFileExtension(multipartFile);

            Map<String, IspField> ispFieldMaps = new HashMap<>();   //  ISP 모음
            Map<String, HostingField> hostingFieldMaps = new HashMap<>();   //  호스팅 모음
            Map<String, Map<String, Object>> companyTypeMaps = new HashMap<>(); //  카테고리 모음
            Map<String, Map<String, Object>> parentSectorMaps = new HashMap<>(); //  카테고리 모음
            Map<String, Map<String, Object>> childSectorMaps = new HashMap<>(); //  카테고리 모음
            Map<String, Map<String, Object>> regionMaps = new HashMap<>(); //  카테고리 모음
            Map<String, Map<String, Object>> statusMaps = new HashMap<>(); //  카테고리 모음

            //  업체의 주요 속성을 map 에 저장하여 엑셀 import 에서 지정한 대상(이슈 속성)을 빠르게 찾을 수 있게 한다.
            this.CompanyFieldAttributeMapToList(ispFieldMaps, hostingFieldMaps, companyTypeMaps, parentSectorMaps, childSectorMaps, regionMaps, statusMaps);
            //  0.237 - 0.230

            List<CompanyFieldForm> companyFieldForms = Lists.newArrayList();
            List<String> headers = Lists.newArrayList();

            Workbook workbook;

            workbook = WorkbookFactory.create(multipartFile.getInputStream());
            Sheet sheet = workbook.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum() + 1;

            //  2건 - 제목, 헤더 - 성능을 위해 최대 1만건으로 제한
            if (lastRowNum > (EXCEL_IMPORT_MAX_ROWS + 2)) {
                throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_MAX_ROWS_OVER));
            }

            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);

                        if (cell == null) {
                            throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.EXCEL_EMPTY_CELL));
                        }

                        //  엑셀 import 데이터에서 cell 값을 문자열로 변환한다.
                        String cellValue = CommonUtil.convertExcelStringToCell(cell);

                        if (StringUtils.isEmpty(cellValue)) {
                            throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.EXCEL_HEADER_EMPTY_CELL));
                        }

                        headers.add(cellValue);
                    }
                }

                //  1번 헤더부터 데이터 영역
                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());

                    companyFieldForms.add(newCompanyFieldForm);
                }
            }

            if (companyFieldForms.size() < 1) {
                return;
            }

            for (CompanyFieldForm saveCompanyFieldForm : companyFieldForms) {
                CompanyField companyField = new CompanyField();
                ConvertUtil.copyProperties(saveCompanyFieldForm, companyField);

                companyField = this.companyFieldRepository.saveAndFlush(companyField);

                saveCompanyFieldForm.setId(companyField.getId());
            }
        }
    }

    /**
     * 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 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;
            }
        }
        return true;
    }

    /**
     * 전화번호 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,
                                                             Map<String, Map<String, Object>> companyTypeMaps, Map<String, Map<String, Object>> parentSectorMaps,
                                                             Map<String, Map<String, Object>> childSectorMaps, Map<String, Map<String, Object>> regionMaps,
                                                             Map<String, Map<String, Object>> statusMaps, List<String> headers) {
        CompanyFieldForm companyFieldForm = new CompanyFieldForm();
        companyFieldForm.setRegisterId(this.webAppUtil.getLoginId());

        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:
                    //  업체명
                    if (isNull) {
                        throw new OwlRuntimeException(
                                this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_COMPANY_NAME_IS_NULL, rowIndex));
                    }
                    this.setCompanyFormName(cellStr, companyFieldForm);
                    break;

                case 1:
                    //  url
                    this.setCompanyFormUrl(cellStr, companyFieldForm, isNull);
                    break;

                case 2:
                    //  ip시작주소
                    this.setCompanyFormIpStart(cellStr, companyFieldForm, isNull);
                    break;

                case 3:
                    //  ip종료주소
                    this.setCompanyFormIpEnd(cellStr, companyFieldForm, isNull);
                    break;

                case 4:
                    // isp명
                    this.setCompanyFormIspName(cellStr, ispFieldMaps, companyFieldForm, rowIndex, isNull);
                    break;

                case 5:
                    // 호스팅명
                    this.setCompanyFormHostingName(cellStr, hostingFieldMaps, companyFieldForm, rowIndex, isNull);
                    break;

                case 6:
                    // 연락처
                    telTypeCheck(cell, rowIndex);
                    this.setCompanyFormTel(cellStr, companyFieldForm, isNull);
                    break;

                case 7:
                    // 이메일
                    this.setCompanyFormEmail(cellStr, companyFieldForm, isNull);
                    break;

                case 8:
                    // 담당자
                    this.setCompanyFormManager(cellStr, companyFieldForm, isNull);
                    break;

                case 9:
                    // 기업구분
                    this.setCompanyFormCompanyType(cellStr, companyTypeMaps, companyFieldForm, rowIndex, isNull);
                    break;

                case 10:
                    // 업종(대분류)
                    this.setCompanyFormParentSector(cellStr, parentSectorMaps, companyFieldForm, rowIndex, isNull);
                    break;

                case 11:
                    // 업종(중분류)
                    this.setCompanyFormChildSector(cellStr, childSectorMaps, companyFieldForm, rowIndex, isNull);
                    break;

                case 12:
                    // 지역
                    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 setCompanyFormIpEnd(String ipEnd, CompanyFieldForm companyFieldForm, boolean isNull) {
        if (!isNull) {
            if (ipEnd.contains(" ")) {
                ipEnd = ipEnd.replace(" ", "");
            }
            this.verifyIp(ipEnd, null); //ip 유효성 검사

            companyFieldForm.setIpEnd(ipEnd);
        }
    }

    private void setCompanyFormIpStart(String ipStart, CompanyFieldForm companyFieldForm, boolean isNull) {
        if (!isNull) {
            if (ipStart.contains(" ")) {
                ipStart = ipStart.replace(" ", "");
            }
            this.verifyIp(ipStart, 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(cellStr);
        }
    }

    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));
            }
            companyFieldForm.setRegionId(MapUtil.getLong(regionMap, "id"));
        }
    }

    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) {
                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"));
        }
    }

    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));
            }
            companyFieldForm.setParentSectorId(MapUtil.getLong(parentSectorMap, "id"));
        }
    }

    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));
            }
            companyFieldForm.setCompanyTypeId(MapUtil.getLong(companyTypeMap, "id"));
        }
    }

    private void setCompanyFormManager(String manager, CompanyFieldForm companyFieldForm, boolean isNull) {
        if (!isNull) {
            companyFieldForm.setManager(manager);
        }
    }

    private void setCompanyFormEmail(String email, CompanyFieldForm companyFieldForm, boolean isNull) {
        if (!isNull) {
            //  이메일 유효성 검사
            email = this.verifyEmail(email);
            companyFieldForm.setEmail(email);
        }
    }

    /**
     * 이메일 유효성 검사
     * @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);
        }
    }

    /**
     * 연락처 유효성 검사
     * @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));
            }
            companyFieldForm.setHostingId(hostingField.getId());
        }
    }

    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));
            }
            companyFieldForm.setIspId(ispField.getId());
        }
    }

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

    private void setCompanyFormUrl(String url, CompanyFieldForm companyFieldForm, boolean isNull) {
        if (!isNull) {
            if (url.contains(" ")) {
                url = url.replace(" ", "");
            }
            this.verifyUrl(url, null); //url 유효성 검사

            companyFieldForm.setUrl(url);
        }
    }

    //  업체명 유효성 체크
    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.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.COMPANY_NAME_MAX_LENGTH_OUT));
        }
    }

    // 업체 목록을 엑셀로 다운로드 한다.
    @Override
    public ModelAndView downloadExcel(HttpServletRequest request, Model model) {
        ModelAndView modelAndView = this.workspaceService.checkUseExcelDownload(model);
        if (modelAndView != null) {
            return modelAndView;
        }

        Map<String, Object> conditions = new HashMap<>();
        //  엑셀 다운로드에 필요한 검색 조건 정보를 추출하고 검색 조건 추출에 오류가 발생하면 경고를 표시해준다.
        modelAndView = this.excelConditionCheck.checkCondition(conditions, request, model);
        if (modelAndView != null) {
            return modelAndView;
        }

        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명 설정
        ExportExcelVo excelInfo = new ExportExcelVo();
        excelInfo.setFileName(this.messageAccessor.message("업체 목록"));
        excelInfo.addAttrInfos(new ExportExcelAttrVo("name", this.messageAccessor.message("companyField.companyName"), 6, ExportExcelAttrVo.ALIGN_CENTER));
        excelInfo.addAttrInfos(new ExportExcelAttrVo("manager", this.messageAccessor.message("companyField.companyManager"), 10, ExportExcelAttrVo.ALIGN_CENTER));
        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));
        excelInfo.addAttrInfos(new ExportExcelAttrVo("regionName", this.messageAccessor.message("companyField.regionName"), 10, ExportExcelAttrVo.ALIGN_CENTER));
        excelInfo.addAttrInfos(new ExportExcelAttrVo("statusName", this.messageAccessor.message("companyField.statusName"), 10, ExportExcelAttrVo.ALIGN_CENTER));
        excelInfo.addAttrInfos(new ExportExcelAttrVo("memo", this.messageAccessor.message("companyField.companyMemo"), 10, ExportExcelAttrVo.ALIGN_CENTER));

        excelInfo.setDatas(companyFieldVos);

        model.addAttribute(Constants.EXCEL, excelInfo);
        return new ModelAndView(this.excelView);
    }

    //  검색 결과를 CompanyFieldVo 로 변환한다.
    private List<CompanyFieldVo> convertCompanyVoToMap(List<Map<String, Object>> results, Long totalCompanyCount, Pageable pageable, Map<String, Object> resJsonData) {
        List<CompanyFieldVo> companyFieldVos = Lists.newArrayList();

        for (Map<String, Object> result : results) {
            CompanyFieldVo companyFieldVo = ConvertUtil.convertMapToClass(result, CompanyFieldVo.class);

            if (companyFieldVo.getCompanyTypeId() != null && companyFieldVo.getCompanyTypeId() != -1) {
                CompanyFieldCategory companyType = this.companyFieldCategoryService.find(companyFieldVo.getCompanyTypeId());
                if (companyType != null) {
                    companyFieldVo.setCompanyTypeName(companyType.getUseValue());
                }
            }
            if (companyFieldVo.getParentSectorId() != null && companyFieldVo.getParentSectorId() != -1) {
                CompanyFieldCategory parentSector = this.companyFieldCategoryService.find(companyFieldVo.getParentSectorId());
                if (parentSector != null) {
                    companyFieldVo.setParentSectorName(parentSector.getUseValue());
                }
            }
            if (companyFieldVo.getChildSectorId() != null && companyFieldVo.getChildSectorId() != -1) {
                CompanyFieldCategory childSector = this.companyFieldCategoryService.find(companyFieldVo.getChildSectorId());
                if (childSector != null) {
                    companyFieldVo.setChildSectorName(childSector.getUseValue());
                }
            }
            if (companyFieldVo.getRegionId() != null && companyFieldVo.getRegionId() != -1) {
                CompanyFieldCategory region = this.companyFieldCategoryService.find(companyFieldVo.getRegionId());
                if (region != null) {
                    companyFieldVo.setRegionName(region.getUseValue());
                }
            }

            if(companyFieldVo.getIspId() != null && companyFieldVo.getIspId() != -1){
                IspField ispField = this.ispFieldService.getIsp(companyFieldVo.getIspId());
                if(ispField != null){
                    IspFieldVo ispFieldVo = ConvertUtil.copyProperties(ispField, IspFieldVo.class);
                    companyFieldVo.setIspFieldVo(ispFieldVo);
                }
            }
            if(companyFieldVo.getHostingId() != null && companyFieldVo.getHostingId() != -1){
                HostingField hostingField = this.hostingFieldService.getHosting(companyFieldVo.getHostingId());
                if(hostingField != null){
                    HostingFieldVo hostingFieldVo = ConvertUtil.copyProperties(hostingField, HostingFieldVo.class);
                    companyFieldVo.setHostingFieldVo(hostingFieldVo);
                }
            }
            companyFieldVos.add(companyFieldVo);
        }

        int totalPage = (int) Math.ceil((totalCompanyCount - 1) / pageable.getPageSize()) + 1;

        resJsonData.put(Constants.RES_KEY_CONTENTS, companyFieldVos);
        resJsonData.put(Constants.REQ_KEY_PAGE_VO, new ResPage(pageable.getPageNumber(), pageable.getPageSize(),
                totalPage, totalCompanyCount));

        return companyFieldVos;
    }

    // ISP ID 로 조회한다
    @Override
    public List<CompanyField> findByIsp(Long id) {
        return this.companyFieldRepository.findByIspId(id);
    }

    // HOSTING ID 로 조회한다
    @Override
    public List<CompanyField> findByHosting(Long id) {
        return this.companyFieldRepository.findByHostingId(id);
    }

    // 업체 ID 로 조회한다
    @Override
    public CompanyField getCompany(Long id) {
        if (id == null) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.COMPANY_NOT_EXIST));
        }
        CompanyField companyField = this.findOne(id);

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