- 파트너 정보 엑셀 임포트 시 연락처, 코드, 이메일 유효성 검사 - 특정 프로젝트로 설정된 상태에서 타 프로젝트의 이슈 클릭 시 대...

minhee

2022-02-21 ad486a401ad59d44bd4187f6406fe8c11b046a1f

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

@@ -6,35 +6,32 @@
import kr.wisestone.owl.constant.MsgConstants;
import kr.wisestone.owl.domain.CompanyField;
import kr.wisestone.owl.domain.HostingField;
import kr.wisestone.owl.domain.IspField;
import kr.wisestone.owl.exception.OwlRuntimeException;
import kr.wisestone.owl.mapper.HostingFieldMapper;
import kr.wisestone.owl.repository.HostingFieldRepository;
import kr.wisestone.owl.service.CompanyFieldService;
import kr.wisestone.owl.service.HostingFieldService;
import kr.wisestone.owl.service.WorkspaceService;
import kr.wisestone.owl.util.CommonUtil;
import kr.wisestone.owl.util.ConvertUtil;
import kr.wisestone.owl.vo.HostingFieldVo;
import kr.wisestone.owl.vo.ExportExcelAttrVo;
import kr.wisestone.owl.vo.ExportExcelVo;
import kr.wisestone.owl.vo.ResPage;
import kr.wisestone.owl.vo.*;
import kr.wisestone.owl.web.condition.HostingFieldCondition;
import kr.wisestone.owl.web.form.HostingFieldForm;
import kr.wisestone.owl.web.view.ExcelView;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
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.transaction.annotation.Transactional;
import org.springframework.ui.Model;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletRequest;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.*;
import java.util.regex.Pattern;

@Service
public class HostingFieldServiceImpl extends AbstractServiceImpl<HostingField, Long, JpaRepository<HostingField, Long>> implements HostingFieldService {
@@ -62,24 +59,40 @@
        return this.hostingFieldRepository;
    }

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

    // Hosting 추가
    @Override
    public HostingField add(HostingFieldForm HostingFieldForm) {
        //  호스팅명 유효성 체크
        //this.verifyTitle(title, null);
        if (HostingFieldForm.getTelList() != null && HostingFieldForm.getTelList().size() > 0) {
            String[] tels = ConvertUtil.ToArray(HostingFieldForm.getTelList());
            HostingFieldForm.setTel(Arrays.toString(tels));
            String tels = HostingFieldForm.getTelList().toString();
            if (tels.contains("[")) {
                tels = tels.substring(1, tels.indexOf("]"));
            }
            HostingFieldForm.setTel(tels.trim());
        }
        if (HostingFieldForm.getEmailList() != null && HostingFieldForm.getEmailList().size() > 0) {
            String[] emails = ConvertUtil.ToArray(HostingFieldForm.getEmailList());
            HostingFieldForm.setEmail(Arrays.toString(emails));
            String emails = HostingFieldForm.getEmailList().toString();
            if (emails.contains("[")) {
                emails = emails.substring(1, emails.indexOf("]"));
            }
            HostingFieldForm.setEmail(emails.trim());
        }

        HostingField hostingField = ConvertUtil.copyProperties(HostingFieldForm, HostingField.class);

        if (hostingField.getCode() != null && !hostingField.getCode().equals("")) {
            hostingFieldRepository.saveAndFlush(hostingField);
            try {
                hostingFieldRepository.saveAndFlush(hostingField);
            } catch (Exception e) {
                throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.HOSTING_CODE_USED_EXIST_VALUE));
            }
        } else {
            throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.HOSTING_CODE_NOT_ENTER));
        }

        return hostingField;
    }

@@ -116,16 +129,34 @@
    // Hosting 정로를 수정한다.
    @Override
    public void modify(HostingFieldForm HostingFieldForm) {
        //  호스팅명 유효성 체크
        //this.verifyTitle(title, null);
        if (HostingFieldForm.getTelList() != null && HostingFieldForm.getTelList().size() > 0) {
            String[] tels = ConvertUtil.ToArray(HostingFieldForm.getTelList());
            HostingFieldForm.setTel(Arrays.toString(tels));
            String tels = HostingFieldForm.getTelList().toString();
            if (tels.contains("[")) {
                tels = tels.substring(1, tels.indexOf("]"));
            }
            HostingFieldForm.setTel(tels.trim());
        }
        if (HostingFieldForm.getEmailList() != null && HostingFieldForm.getEmailList().size() > 0) {
            String[] emails = ConvertUtil.ToArray(HostingFieldForm.getEmailList());
            HostingFieldForm.setEmail(Arrays.toString(emails));
            String emails = HostingFieldForm.getEmailList().toString();
            if (emails.contains("[")) {
                emails = emails.substring(1, emails.indexOf("]"));
            }
            HostingFieldForm.setEmail(emails.trim());
        }
        HostingField HostingField = ConvertUtil.copyProperties(HostingFieldForm, HostingField.class);
        hostingFieldRepository.saveAndFlush(HostingField);

        if (HostingField.getCode() != null && !HostingField.getCode().equals("")) {
            try {
                hostingFieldRepository.saveAndFlush(HostingField);
            } catch (Exception e) {
                throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.HOSTING_CODE_USED_EXIST_VALUE));
            }
        } else {
            throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.HOSTING_CODE_NOT_ENTER));
        }

    }

    // Hosting를 삭제한다.
@@ -190,6 +221,358 @@
        return new ModelAndView(this.excelView);
    }

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

        ExportExcelVo excelInfo = new ExportExcelVo();
        excelInfo.setHideCount(true);
        excelInfo.setFileName(this.messageAccessor.message("common.registerExcelHostingField")); // 엑셀로 호스팅 등록하기
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("Hosting.HostingName"), 20, ExportExcelAttrVo.ALIGN_CENTER)); // 호스팅명
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("Hosting.HostingCode"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 코드
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("Hosting.HostingManager"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 담당자
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("Hosting.HostingTel"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 연락처
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("Hosting.HostingEmail"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 이메일
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("Hosting.HostingUrl"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // url
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("Hosting.HostingMemo"), 40, ExportExcelAttrVo.ALIGN_CENTER)); // 비고

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

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

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

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

            List<HostingFieldForm> hostingFieldForms = 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) {
                    //  호스팅으로 등록하기 위해 newHostingFieldForm 에 데이터를 셋팅한다.
                    HostingFieldForm newHostingFieldForm = this.setHostingFieldFormToExcelField(row, (rowIndex + 1), headers);

                    hostingFieldForms.add(newHostingFieldForm);
                }
            }

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

            for (HostingFieldForm saveHostingFieldForm : hostingFieldForms) {
                HostingField hostingField = new HostingField();
                ConvertUtil.copyProperties(saveHostingFieldForm, hostingField);

                if (hostingField.getCode() != null && !hostingField.getCode().equals("")) {
                    try {
                        hostingField = hostingFieldRepository.saveAndFlush(hostingField);
                    } catch (Exception e) {
                        throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.HOSTING_CODE_USED_EXIST_VALUE));
                    }
                } else {
                    throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.HOSTING_CODE_NOT_ENTER));
                }

                saveHostingFieldForm.setId(hostingField.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));
        }
    }

    //  엑셀 필드에 있는 정보를 HOSTING form 으로 옮긴다.
    private HostingFieldForm setHostingFieldFormToExcelField(Row row, int rowIndex, List<String> headers) {
        HostingFieldForm hostingFieldForm = new HostingFieldForm();
        hostingFieldForm.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_HOSTING_NAME_IS_NULL, rowIndex));
                    }
                    this.setHostingFormName(cellStr, hostingFieldForm);
                    break;

                case 1:
                    //  코드
                    if (isNull) {
                        throw new OwlRuntimeException(
                                this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_HOSTING_CODE_IS_NULL, rowIndex));
                    }
                    this.setHostingFormCode(cellStr, hostingFieldForm);
                    break;

                case 2:
                    // 담당자
                    this.setHostingFormManager(cellStr, hostingFieldForm, isNull);
                    break;

                case 3:
                    // 연락처
                    telTypeCheck(cell, rowIndex);
                    this.setHostingFormTel(cellStr, hostingFieldForm, isNull);
                    break;

                case 4:
                    // 이메일
                    this.setHostingFormEmail(cellStr, hostingFieldForm, isNull);
                    break;

                case 5:
                    // url
                    this.setHostingFormUrl(cellStr, hostingFieldForm, isNull);
                    break;

                case 6:
                    // 비고
                    this.setHostingFormMemo(cellStr, hostingFieldForm, isNull);
            }
        }

        return hostingFieldForm;
    }

    private void setHostingFormMemo(String memo, HostingFieldForm hostingFieldForm, boolean isNull) {
        if (!isNull) {
            hostingFieldForm.setMemo(memo);
        }
    }

    private void setHostingFormUrl(String url, HostingFieldForm hostingFieldForm, boolean isNull) {
        if (!isNull) {
            if (url.contains(" ")) {
                url = url.replace(" ", "");
            }
            hostingFieldForm.setUrl(url);
        }
    }

    private void setHostingFormEmail(String email, HostingFieldForm hostingFieldForm, boolean isNull) {
        if (!isNull) {
            //  이메일 유효성 검사
            email = this.verifyEmail(email);
            hostingFieldForm.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 setHostingFormTel(String tel, HostingFieldForm hostingFieldForm, boolean isNull) {
        if (!isNull) {
            //  연락처 유효성 검사
            tel = this.verifyTel(tel);
            hostingFieldForm.setTel(tel);
        }
    }

    /**
     * 연락처 유효성 검사
     * @param tel 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 setHostingFormManager(String manager, HostingFieldForm hostingFieldForm, boolean isNull) {
        if (!isNull) {
            hostingFieldForm.setManager(manager);
        }
    }

    private void setHostingFormCode(String code, HostingFieldForm hostingFieldForm) {
        //코드 유효성 체크
        this.verifyCode(code);
        hostingFieldForm.setCode(code);
    }

    /**
     * 코드 유효성 검사
     * @param code String
     */
    private void verifyCode(String code) {
        if (!Pattern.matches("^[a-zA-Z0-9가-힣ㄱ-ㅎㅏ-ㅣ\\u318D\\u119E\\u11A2\\u2022\\u2025a\\u00B7\\uFE55]+$", code)) {
            throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.CODE_NOT_INVALID));
        }
    }

    private void setHostingFormName(String title, HostingFieldForm hostingFieldForm) {
        //  호스팅명 유효성 체크
        //this.verifyTitle(title, null);
        hostingFieldForm.setName(title);
    }

    //  호스팅명 유효성 체크
    private void verifyTitle(String title, Long id) {
        List<HostingField> hostingFields = new ArrayList<>();

        //  호스팅명 중복 체크
        if (id != null) { //수정 일 경우
            hostingFields = this.hostingFieldRepository.findByNameAndIdNot(title, id);
        } else { // 추가 일 경우
            hostingFields = this.hostingFieldRepository.findByName(title);
        }
        if (hostingFields != null && hostingFields.size() > 0) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.HOSTING_NAME_ALREADY_IN_USE));
        }

        //  호스팅명 빈값 체크
        if (StringUtils.isEmpty(title)) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.HOSTING_NO_TITLE));
        }

        //  호스팅명 길이 체크
        if (title.length() > 300) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.HOSTING_NAME_MAX_LENGTH_OUT));
        }
    }

    //  검색 결과를 HostingVo 로 변환한다.
    private List<HostingFieldVo> convertHostingVoToMap(List<Map<String, Object>> results, Long totalHostingCount, Pageable pageable, Map<String, Object> resJsonData) {
        List<HostingFieldVo> hostingFieldVos = Lists.newArrayList();