From ec9c7e5582d4c20c35e7c3016d64a2213e9f6a50 Mon Sep 17 00:00:00 2001 From: minhee <alsdldlfrl@gmail.com> Date: 화, 15 3월 2022 12:12:17 +0900 Subject: [PATCH] - 파트너 엑셀 임포트 시 이메일 정규식표현 검사하는 코드 제거 - 이슈 상세페이지 setHideCompleteIssue null 체크 - api로 이슈 추가 시 이슈유형에 업체가 설정되어있지 않는경우 메시지 추가 --- src/main/java/kr/wisestone/owl/service/impl/HostingFieldServiceImpl.java | 448 ++++++++++++++++++++++++++++++++++++++++++++++++++++++- 1 files changed, 435 insertions(+), 13 deletions(-) diff --git a/src/main/java/kr/wisestone/owl/service/impl/HostingFieldServiceImpl.java b/src/main/java/kr/wisestone/owl/service/impl/HostingFieldServiceImpl.java index 26f8d67..245534d 100644 --- a/src/main/java/kr/wisestone/owl/service/impl/HostingFieldServiceImpl.java +++ b/src/main/java/kr/wisestone/owl/service/impl/HostingFieldServiceImpl.java @@ -9,28 +9,29 @@ 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.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 { @@ -45,6 +46,9 @@ private WorkspaceService workspaceService; @Autowired + private CompanyFieldService companyFieldService; + + @Autowired private ExcelView excelView; @Autowired @@ -55,12 +59,41 @@ return this.hostingFieldRepository; } + private static final int EXCEL_IMPORT_MAX_ROWS = 10000; // excel import �젣�븳 + // Hosting 異붽� @Override public HostingField add(HostingFieldForm HostingFieldForm) { - HostingField HostingField = ConvertUtil.copyProperties(HostingFieldForm, HostingField.class); - hostingFieldRepository.saveAndFlush(HostingField); - return HostingField; + // �샇�뒪�똿紐� �쑀�슚�꽦 泥댄겕 + //this.verifyTitle(title, null); + if (HostingFieldForm.getTelList() != null && HostingFieldForm.getTelList().size() > 0) { + 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 = 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("")) { + 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; } // Hosting 紐⑸줉�쓣 媛��졇�삩�떎. @@ -96,8 +129,34 @@ // Hosting �젙濡쒕�� �닔�젙�븳�떎. @Override public void modify(HostingFieldForm HostingFieldForm) { + // �샇�뒪�똿紐� �쑀�슚�꽦 泥댄겕 + //this.verifyTitle(title, null); + if (HostingFieldForm.getTelList() != null && HostingFieldForm.getTelList().size() > 0) { + 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 = 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瑜� �궘�젣�븳�떎. @@ -105,13 +164,24 @@ public void remove(HostingFieldForm HostingFieldForm) { if (HostingFieldForm.getRemoveIds().size() < 1) { throw new OwlRuntimeException( - this.messageAccessor.getMessage(MsgConstants.COMPANY_REMOVE_NOT_SELECT)); + this.messageAccessor.getMessage(MsgConstants.HOSTING_REMOVE_NOT_SELECT)); } for (Long id : HostingFieldForm.getRemoveIds()) { + List<CompanyField> companyField = this.companyFieldService.findByHosting(id); + if (companyField != null && companyField.size() > 0) { + throw new OwlRuntimeException( + this.messageAccessor.getMessage(MsgConstants.HOSTING_EXIST_COMPANY)); + } else { this.hostingFieldRepository.deleteById(id); - + } } this.hostingFieldRepository.flush(); + } + + @Override + @Transactional(readOnly = true) + public List<HostingField> findAll() { + return this.hostingFieldRepository.findAll(); } // Hosting 紐⑸줉�쓣 �뿊��濡� �떎�슫濡쒕뱶 �븳�떎. @@ -151,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(); -- Gitblit v1.8.0