업체 정보 일괄 import 기능

이민희

2022-01-12 836af5be0ec8b59171d7c7ffa392a6bd234f0af7

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

@@ -1,14 +1,17 @@
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.web.condition.CompanyFieldCategoryCondition;
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;
import org.springframework.ui.Model;
import com.google.common.collect.Lists;
@@ -25,6 +28,7 @@
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;
@@ -70,6 +74,9 @@
    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
@@ -222,6 +229,414 @@
        }
    }

    //  업체 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("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);

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

    //  엑셀 필드에 있는 정보를 업체 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);
            switch (cellIndex) {
                case 0:
                    //  업체명
                    this.setCompanyFormName(cell, companyFieldForm, rowIndex);
                    break;

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

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

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

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

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

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

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

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

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

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

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

                case 12:
                    // 비고
                    if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                        this.setCompanyFormMemo(cell, companyFieldForm, rowIndex);
                    }
                    break;
            }
        }

        return companyFieldForm;
    }

    private void setCompanyFormMemo(Cell cell, CompanyFieldForm companyFieldForm, int rowIndex) {
        companyFieldForm.setMemo(CommonUtil.convertExcelStringToCell(cell));
    }

    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));
            if (MapUtil.getLong(statusMap, "id") != null) {
                companyFieldForm.setStatusId(MapUtil.getLong(statusMap, "id"));
            } else {
                companyFieldForm.setStatusId(120L);
            }
            companyFieldForm.setStatusName(CommonUtil.convertExcelStringToCell(cell));
        }
    }

    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));
            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(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().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(Cell cell, Map<String, Map<String, Object>> parentSectorMaps, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (cell != null) {
            Map<String, Object> parentSectorMap = parentSectorMaps.get(CommonUtil.convertExcelStringToCell(cell));
            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(Cell cell, Map<String, Map<String, Object>> companyTypeMaps, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (cell != null) {
            Map<String, Object> companyTypeMap = companyTypeMaps.get(CommonUtil.convertExcelStringToCell(cell));
            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(Cell cell, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (cell != null) {
            companyFieldForm.setManager(CommonUtil.convertExcelStringToCell(cell));
        }
    }

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

    private void setCompanyFormTel(Cell cell, CompanyFieldForm companyFieldForm, int rowIndex) {
        if (cell != null) {
            companyFieldForm.setTel(CommonUtil.convertExcelStringToCell(cell));
        }
    }

    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(
                        this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_HOSTING_NOT_EXIST, rowIndex));
            }
            companyFieldForm.setHostingId(hostingField.getId());
        }
    }

    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(
                        this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_ISP_NOT_EXIST, rowIndex));
            }
            companyFieldForm.setIspId(ispField.getId());
        }
    }

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

        String title = CommonUtil.convertExcelStringToCell(cell);

        //  업체명 유효성 체크
        this.verifyTitle(title);
        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 유효성 검사

        companyFieldForm.setUrl(url);
    }

    //  업체명 유효성 체크
    private void verifyTitle(String title) {
        if (StringUtils.isEmpty(title)) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.ISSUE_NO_TITLE));
        }

        if (title.length() > 300) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.ISSUE_TITLE_MAX_LENGTH_OUT));
        }
    }

    // 업체 목록을 엑셀로 다운로드 한다.
    @Override
    public ModelAndView downloadExcel(HttpServletRequest request, Model model) {