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

minhee

2022-02-21 ad486a401ad59d44bd4187f6406fe8c11b046a1f

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

@@ -6,31 +6,34 @@
import kr.wisestone.owl.constant.MsgConstants;
import kr.wisestone.owl.domain.CompanyField;
import kr.wisestone.owl.domain.IspField;
import kr.wisestone.owl.domain.Priority;
import kr.wisestone.owl.exception.OwlRuntimeException;
import kr.wisestone.owl.mapper.IspFieldMapper;
import kr.wisestone.owl.repository.IspFieldRepository;
import kr.wisestone.owl.service.CompanyFieldService;
import kr.wisestone.owl.service.IspFieldService;
import kr.wisestone.owl.service.WorkspaceService;
import kr.wisestone.owl.util.CommonUtil;
import kr.wisestone.owl.util.ConvertUtil;
import kr.wisestone.owl.vo.IspFieldVo;
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.IspFieldCondition;
import kr.wisestone.owl.web.form.CompanyFieldForm;
import kr.wisestone.owl.web.form.IspFieldForm;
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.HashMap;
import java.util.List;
import java.util.Map;
import java.util.*;
import java.util.regex.Pattern;

@Service
public class IspFieldServiceImpl extends AbstractServiceImpl<IspField, Long, JpaRepository<IspField, Long>> implements IspFieldService {
@@ -45,6 +48,9 @@
    private WorkspaceService workspaceService;

    @Autowired
    private CompanyFieldService companyFieldService;

    @Autowired
    private ExcelView excelView;

    @Autowired
@@ -55,17 +61,42 @@
        return this.ispFieldRepository;
    }

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

    // Isp 추가
    @Override
    public IspField add(IspFieldForm IspFieldForm) {
        //  url 유효성 체크
        if(IspFieldForm.getUrl() != null){
            this.verifyUrl(IspFieldForm.getUrl(), null);
        //  ISP명 중복 체크
        //this.verifyTitle(IspFieldForm.getName(), null);
        
        if (IspFieldForm.getTelList() != null && IspFieldForm.getTelList().size() > 0) {
            String tels = IspFieldForm.getTelList().toString();
            if (tels.contains("[")) {
                tels = tels.substring(1, tels.indexOf("]"));
            }
            IspFieldForm.setTel(tels.trim());
        }
        if (IspFieldForm.getEmailList() != null && IspFieldForm.getEmailList().size() > 0) {
            String emails = IspFieldForm.getEmailList().toString();
            if (emails.contains("[")) {
                emails = emails.substring(1, emails.indexOf("]"));
            }
            IspFieldForm.setEmail(emails.trim());
        }

        IspField IspField = ConvertUtil.copyProperties(IspFieldForm, IspField.class);
        ispFieldRepository.saveAndFlush(IspField);
        return IspField;
        IspField ispField = ConvertUtil.copyProperties(IspFieldForm, IspField.class);

        if (ispField.getCode() != null && !ispField.getCode().equals("")) {
            try {
                ispFieldRepository.saveAndFlush(ispField);
            } catch (Exception e) {
                throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.ISP_CODE_USED_EXIST_VALUE));
            }
        } else {
            throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.ISP_CODE_NOT_ENTER));
        }

        return ispField;
    }

    // Isp 목록을 가져온다.
@@ -81,22 +112,9 @@
        return this.convertIspVoToMap(results, totalIspCount, pageable, resJsonData);
    }

    //  url 유효성 체크
    private void verifyUrl(String url, Long id) {
        IspField ispField;

        if(id == null){
            ispField = this.ispFieldRepository.findByUrl(url);
        } else {
            ispField = this.ispFieldRepository.findByUrlAndIdNot(url,id);
        }

        if (ispField != null) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.COMPANYFIELD_USED_URL));
        }
    public Map<String, Object> find(Long id) {
        return this.ispFieldMapper.findById(id);
    }


    // Isp 상세 조회한다.
    @Override
@@ -114,13 +132,36 @@
    // Isp 정로를 수정한다.
    @Override
    public void modify(IspFieldForm IspFieldForm) {
        if(IspFieldForm.getUrl() != null){
            //  url 유효성 체크
            this.verifyUrl(IspFieldForm.getUrl(), IspFieldForm.getId());
        //  ISP명 유효성 체크
        //this.verifyTitle(IspFieldForm.getName(), IspFieldForm.getId());

        if (IspFieldForm.getTelList() != null && IspFieldForm.getTelList().size() > 0) {
            String tels = IspFieldForm.getTelList().toString();
            if (tels.contains("[")) {
                tels = tels.substring(1, tels.indexOf("]"));
            }
            IspFieldForm.setTel(tels.trim());
        }
        if (IspFieldForm.getEmailList() != null && IspFieldForm.getEmailList().size() > 0) {
            String emails = IspFieldForm.getEmailList().toString();
            if (emails.contains("[")) {
                emails = emails.substring(1, emails.indexOf("]"));
            }
            IspFieldForm.setEmail(emails.trim());
        }

        IspField IspField = ConvertUtil.copyProperties(IspFieldForm, IspField.class);
        ispFieldRepository.saveAndFlush(IspField);
        IspField ispField = ConvertUtil.copyProperties(IspFieldForm, IspField.class);

        if (ispField.getCode() != null && !ispField.getCode().equals("")) {
            try {
                ispFieldRepository.saveAndFlush(ispField);
            } catch (Exception e) {
                throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.ISP_CODE_USED_EXIST_VALUE));
            }
        } else {
            throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.ISP_CODE_NOT_ENTER));
        }

    }


@@ -132,9 +173,21 @@
                    this.messageAccessor.getMessage(MsgConstants.ISP_REMOVE_NOT_SELECT));
        }
        for (Long id : IspFieldForm.getRemoveIds()) {
            List<CompanyField> companyField = this.companyFieldService.findByIsp(id);
            if (companyField != null && companyField.size() > 0) {
                throw new OwlRuntimeException(
                        this.messageAccessor.getMessage(MsgConstants.ISP_EXIST_COMPANY));
            } else {
                this.ispFieldRepository.deleteById(id);
            }
        }
        this.ispFieldRepository.flush();
    }

    @Override
    @Transactional(readOnly = true)
    public List<IspField> findAll() {
        return this.ispFieldRepository.findAll();
    }

    // Isp 목록을 엑셀로 다운로드 한다.
@@ -174,6 +227,360 @@
        return new ModelAndView(this.excelView);
    }

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

        ExportExcelVo excelInfo = new ExportExcelVo();
        excelInfo.setHideCount(true);
        excelInfo.setFileName(this.messageAccessor.message("common.registerExcelIspField")); // 엑셀로 ISP 등록하기
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("isp.ispName"), 20, ExportExcelAttrVo.ALIGN_CENTER)); // ISP명
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("isp.ispCode"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 코드
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("isp.ispManager"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 담당자
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("isp.ispTel"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 연락처
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("isp.ispEmail"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 이메일
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("isp.ispUrl"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // url
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("isp.ispMemo"), 40, ExportExcelAttrVo.ALIGN_CENTER)); // 비고

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

        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 로 ISP를 등록한다.
    @Override
    @Transactional
    public void importExcel(MultipartFile multipartFile) throws Exception {
        //  사용하고 있는 업무 공간이 활성 상태인지 확인한다. 사용 공간에서 로그인한 사용자가 비활성인지 확인한다.
        this.workspaceService.checkUseWorkspace();

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

            List<IspFieldForm> ispFieldForms = 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) {
                    //  ISP로 등록하기 위해 ispFieldForm 에 데이터를 셋팅한다.
                    IspFieldForm newIspFieldForm = this.setIspFieldFormToExcelField(row, (rowIndex + 1), headers);

                    ispFieldForms.add(newIspFieldForm);
                }
            }

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

            for (IspFieldForm saveIspFieldForm : ispFieldForms) {
                IspField ispField = new IspField();
                ConvertUtil.copyProperties(saveIspFieldForm, ispField);

                if (ispField.getCode() != null && !ispField.getCode().equals("")) {
                    try {
                        ispField = ispFieldRepository.saveAndFlush(ispField);
                    } catch (Exception e) {
                        throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.ISP_CODE_USED_EXIST_VALUE));
                    }
                } else {
                    throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.ISP_CODE_NOT_ENTER));
                }

                saveIspFieldForm.setId(ispField.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));
        }
    }

    //  엑셀 필드에 있는 정보를 ISP form 으로 옮긴다.
    private IspFieldForm setIspFieldFormToExcelField(Row row, int rowIndex, List<String> headers) {
        IspFieldForm ispFieldForm = new IspFieldForm();
        ispFieldForm.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:
                    //  ISP명
                    if (isNull) {
                        throw new OwlRuntimeException(
                                this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_ISP_NAME_IS_NULL, rowIndex));
                    }
                    this.setIspFormName(cellStr, ispFieldForm);
                    break;

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

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

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

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

                case 5:
                    // url
                    this.setIspFormUrl(cellStr, ispFieldForm, isNull);
                    break;

                case 6:
                    // 비고
                    this.setIspFormMemo(cellStr, ispFieldForm, isNull);
            }
        }

        return ispFieldForm;
    }

    private void setIspFormMemo(String memo, IspFieldForm ispFieldForm, boolean isNull) {
        if (!isNull) {
            ispFieldForm.setMemo(memo);
        }
    }

    private void setIspFormUrl(String url, IspFieldForm ispFieldForm, boolean isNull) {
        if (!isNull) {
            if (url.contains(" ")) {
                url = url.replace(" ", "");
            }
            ispFieldForm.setUrl(url);
        }
    }

    private void setIspFormEmail(String email, IspFieldForm ispFieldForm, boolean isNull) {
        if (!isNull) {
            //  이메일 유효성 검사
            email = this.verifyEmail(email);
            ispFieldForm.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 setIspFormTel(String tel, IspFieldForm ispFieldForm, int rowIndex, boolean isNull) {
        if (!isNull) {
            //  연락처 유효성 검사
            tel = this.verifyTel(tel);
            ispFieldForm.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 setIspFormManager(String manager, IspFieldForm ispFieldForm, boolean isNull) {
        if (!isNull) {
            ispFieldForm.setManager(manager);
        }
    }

    private void setIspFormCode(String code, IspFieldForm ispFieldForm) {
        //코드 유효성 체크
        this.verifyCode(code);
        ispFieldForm.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 setIspFormName(String title, IspFieldForm ispFieldForm) {
        //  ISP명 유효성 체크
        //this.verifyTitle(title, null);
        ispFieldForm.setName(title);
    }

    //  ISP명 유효성 체크
    private void verifyTitle(String title, Long id) {
        List<IspField> ispFields = new ArrayList<>();

        //  ISP명 중복 체크

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

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

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

    //  검색 결과를 IspVo 로 변환한다.
    private List<IspFieldVo> convertIspVoToMap(List<Map<String, Object>> results, Long totalIspCount, Pageable pageable, Map<String, Object> resJsonData) {
        List<IspFieldVo> ispFieldVos = Lists.newArrayList();
@@ -208,4 +615,6 @@
        }
        return IspField;
    }


}