package itn.let.mjo.pay.web; import java.io.BufferedReader; import java.io.InputStream; import java.io.InputStreamReader; import java.io.OutputStream; import java.io.PrintWriter; import java.math.BigDecimal; import java.math.RoundingMode; import java.net.HttpURLConnection; import java.net.URL; import java.nio.charset.Charset; import java.text.NumberFormat; import java.text.SimpleDateFormat; import java.time.LocalDate; import java.time.LocalDateTime; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Locale; import java.util.Map; import java.util.regex.Matcher; import java.util.regex.Pattern; import javax.annotation.Resource; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.json.simple.JSONArray; import org.json.simple.JSONObject; import org.json.simple.parser.JSONParser; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.http.HttpStatus; import org.springframework.http.MediaType; import org.springframework.http.ResponseEntity; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.ui.ModelMap; import org.springframework.web.bind.annotation.ModelAttribute; import org.springframework.web.bind.annotation.RequestBody; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.ResponseBody; import org.springframework.web.multipart.MultipartFile; import org.springframework.web.multipart.MultipartHttpServletRequest; import org.springframework.web.servlet.HandlerMapping; import org.springframework.web.servlet.ModelAndView; import org.springframework.web.servlet.mvc.support.RedirectAttributes; import com.ibm.icu.text.DecimalFormat; import egovframework.rte.fdl.idgnr.EgovIdGnrService; import egovframework.rte.fdl.security.userdetails.util.EgovUserDetailsHelper; import egovframework.rte.ptl.mvc.tags.ui.pagination.PaginationInfo; import itn.com.cmm.ComDefaultCodeVO; import itn.com.cmm.EgovMessageSource; import itn.com.cmm.LoginVO; import itn.com.cmm.RestResponse; import itn.com.cmm.service.EgovCmmUseService; import itn.com.cmm.service.FileVO; import itn.com.cmm.util.DateUtils; import itn.com.cmm.util.MJUtil; import itn.com.cmm.util.PayUtils; import itn.com.cmm.util.RedirectUrlMaker; import itn.com.cmm.util.StringUtil; import itn.com.utl.fcc.service.EgovStringUtil; import itn.let.fax.admin.service.FaxStatVO; import itn.let.mail.service.StatusResponse; import itn.let.mjo.mjocommon.MjonCommon; import itn.let.mjo.msg.service.MjonMsgService; import itn.let.mjo.msg.service.MjonMsgVO; import itn.let.mjo.msgcampain.service.MjonCandidateService; import itn.let.mjo.msgcampain.service.MjonCandidateVO; import itn.let.mjo.msgdata.service.MjonMsgDataService; import itn.let.mjo.pay.service.KmcVO; import itn.let.mjo.pay.service.MjonPayService; import itn.let.mjo.pay.service.MjonPayVO; import itn.let.mjo.pay.service.MjonVaMsgLogVO; import itn.let.mjo.pay.service.RefundService; import itn.let.mjo.pay.service.RefundVO; import itn.let.mjo.pay.service.StVcVO; import itn.let.mjo.payva.service.VacsVactService; import itn.let.mjo.payva.service.VacsVactVO; import itn.let.mjo.reservmsg.service.MjonReservMsgService; import itn.let.mjo.tax.service.TaxService; import itn.let.mjo.tax.service.TaxVO; import itn.let.schdlr.service.SchdlrManageService; import itn.let.sym.grd.service.MberGrdService; import itn.let.sym.grd.service.MberGrdVO; import itn.let.sym.site.service.EgovSiteManagerService; import itn.let.sym.site.service.JoinSettingVO; import itn.let.uat.uia.service.AuthCertVO; import itn.let.uss.umt.service.EgovMberManageService; import itn.let.uss.umt.service.EgovUserManageService; import itn.let.uss.umt.service.MberManageVO; import itn.let.uss.umt.service.UserManageVO; import itn.let.utl.fcc.service.EgovCryptoUtil; import itn.let.utl.user.service.MjonNoticeSendUtil; @Controller public class MjonPayController { private static final Logger LOGGER = LoggerFactory.getLogger(MjonPayController.class); @Resource(name = "mjonPayService") private MjonPayService mjonPayService; /** EgovMessageSource */ @Resource(name="egovMessageSource") EgovMessageSource egovMessageSource; /** userManageService */ @Resource(name = "userManageService") private EgovUserManageService userManageService; /** refundService */ @Resource(name = "refundService") private RefundService refundService; @Resource(name = "mjonMsgService") private MjonMsgService mjonMsgService; @Resource(name = "MjonMsgDataService") private MjonMsgDataService mjonMsgDataService; @Resource(name = "mberManageService") private EgovMberManageService mberManageService; @Resource(name = "EgovCmmUseService") private EgovCmmUseService cmmUseService; @Resource(name = "egovCryptoUtil") EgovCryptoUtil egovCryptoUtil; @Resource(name = "mjonCandidateService") private MjonCandidateService mjonCandidateService; //전용계좌 서비스 @Resource(name = "vacsVactService") private VacsVactService vacsVactService; @Resource(name = "MjonReservMsgService") private MjonReservMsgService mjonReservMsgService; @Resource(name="taxService") private TaxService taxService; @Resource(name = "egovPgMoidGnrService") private EgovIdGnrService idgenPgMoid; /** 알림전송 Util */ @Resource(name = "mjonNoticeSendUtil") private MjonNoticeSendUtil mjonNoticeSendUtil; @Resource(name="MjonCommon") private MjonCommon mjonCommon; @Resource(name = "SchdlrManageService") private SchdlrManageService schdlrManageService; /** 사이트 설정 */ @Resource(name = "egovSiteManagerService") EgovSiteManagerService egovSiteManagerService; @Resource(name = "mberGrdService") MberGrdService mberGrdService; //배열 정의{"컬럼순차번호, 컬럼이름, 컬럼내용, 컬럼이름에 붙여야할 내용(엑셀코드양식다운로드시 필요)"} private String[][] sendPayExcelValue ={ {"0" ,"번호" , "1" , "" }, {"1", "결제일시" , "2021-06-08 11:05:38" , ""}, {"2", "결제방식" , "개인전용계좌", ""}, {"3", "결제금액" , "500", ""}, {"4", "결제상태" , "결제완료", ""}, {"5", "결제정보" , "승인번호 : nicepay00m03012105271819041065", ""}, {"6", "증빙서류 발행요청" , "카드전표", ""} } ; private String[][] sendPayExcelValue2 ={ {"0" ,"번호" , "1" , "" }, {"1", "결제일자" , "2021-06-08 11:05:38" , ""}, {"2", "결제시간" , "2021-06-08 11:05:38" , ""}, {"3", "결제수단" , "개인전용계좌", ""}, {"4", "결제상태" , "결제완료", ""}, {"5", "결제금액" , "500", ""} } ; //배열 정의{"컬럼순차번호, 컬럼이름, 컬럼내용, 컬럼이름에 붙여야할 내용(엑셀코드양식다운로드시 필요)"} private String[][] sendPayUserExcelValue ={ {"0" ,"번호" , "1" , "" }, {"1", "결제일시" , "2021-06-08 11:05:38" , ""}, {"2", "문자유형" , "단문", ""}, {"3", "발송건수" , "1", ""}, {"4", "문자내용" , "문자내용", ""}, {"5", "충전금액" , "100", ""}, {"6", "충전포인트" , "100", ""}, {"7", "사용금액" , "100", ""}, {"8", "사용포인트" , "100", ""}, {"9", "잔액금액" , "100", ""}, {"10", "잔액포인트" , "100", ""}, } ; private String[][] sendPointUseExcelValue ={ {"0" ,"번호" , "1" , "" }, {"1", "교환일시" , "2021-06-08 11:05:38" , ""}, {"2", "교환방식" , "현금", ""}, {"3", "교환포인트" , "10000", ""}, {"4", "교환상태" , "처리완료", ""}, {"5", "은행" , "우리은행", ""}, {"6", "계좌번호" , "10112314124", ""}, {"7", "예금주" , "홍길동", ""}, {"8", "연락처" , "01012341234", ""}, } ; private String[][] refundExcelValue ={ {"0" ,"번호" , "1" , "" }, {"1", "환불요청일시" , "2021-06-08 11:05:38" , ""}, {"2", "환불처리일시(취소/불가처리 포함)" , "2021-06-08 11:05:38", ""}, {"3", "환불요청금액" , "10000", ""}, {"4", "환불처리금액" , "10000", ""}, {"5", "환불처리상태" , "처리완료", ""}, } ; /** * 캐시 리스트 * @param searchVO * @param model * @return "/uss/ion/msg/SendNumberList" * @throws Exception */ @RequestMapping(value= {"/uss/ion/pay/CashList.do"}) public String selectCashList(@ModelAttribute("searchVO") MjonPayVO mjonPayVO, HttpServletRequest request, ModelMap model) throws Exception{ String pattern = (String) request.getAttribute( HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; /** pageing */ PaginationInfo paginationInfo = new PaginationInfo(); paginationInfo.setCurrentPageNo(mjonPayVO.getPageIndex()); paginationInfo.setRecordCountPerPage(mjonPayVO.getPageUnit()); paginationInfo.setPageSize(mjonPayVO.getPageSize()); mjonPayVO.setFirstIndex(paginationInfo.getFirstRecordIndex()); mjonPayVO.setLastIndex(paginationInfo.getLastRecordIndex()); mjonPayVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage()); if("".equals(mjonPayVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonPayVO.setSearchSortCnd("cashId"); mjonPayVO.setSearchSortOrd("desc"); } if(null == mjonPayVO.getSearchCondition2()) { //최초조회시 (미삭제 만 보이게) mjonPayVO.setSearchCondition2("N"); } // 기간검색 설정 String beforeMonthDay = MJUtil.getBefore1MonthDate(); //한달 전 if (null == mjonPayVO.getSearchStartDate() || mjonPayVO.getSearchStartDate().equals("")) { mjonPayVO.setSearchStartDate(beforeMonthDay); } List<MjonPayVO> resultList = mjonPayService.selectCashList(mjonPayVO); List<BigDecimal> cashList = new ArrayList<>(); for(int i = 0 ; i < resultList.size() ; i ++) { BigDecimal cash = new BigDecimal(resultList.get(i).getCash()).setScale(2, RoundingMode.HALF_EVEN); cashList.add(cash); } model.addAttribute("cashList", cashList); model.addAttribute("resultList", resultList); paginationInfo.setTotalRecordCount(resultList.size() > 0 ? ((MjonPayVO)resultList.get(0)).getTotCnt() : 0); model.addAttribute("paginationInfo", paginationInfo); if(pattern.equals("/web/msg/SendNumberList.do")) { //사용자 발신 return "/web/msg/SendNumberList"; } return "/uss/ion/pay/CashList"; } /** * 캐시 리스트 * @param searchVO * @param model * @return "/uss/ion/msg/SendNumberList" * @throws Exception */ @RequestMapping(value= {"/uss/ion/pay/CashRemainList.do"}) public String selectCashRemainList(@ModelAttribute("searchVO") MjonPayVO mjonPayVO, HttpServletRequest request, ModelMap model) throws Exception{ String pattern = (String) request.getAttribute( HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; /** pageing */ PaginationInfo paginationInfo = new PaginationInfo(); paginationInfo.setCurrentPageNo(mjonPayVO.getPageIndex()); paginationInfo.setRecordCountPerPage(mjonPayVO.getPageUnit()); paginationInfo.setPageSize(mjonPayVO.getPageSize()); mjonPayVO.setFirstIndex(paginationInfo.getFirstRecordIndex()); mjonPayVO.setLastIndex(paginationInfo.getLastRecordIndex()); mjonPayVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage()); if("".equals(mjonPayVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonPayVO.setSearchSortCnd("remainCash"); mjonPayVO.setSearchSortOrd("desc"); } List<MjonPayVO> resultList = mjonPayService.selectCashRemainNewList(mjonPayVO); int totCnt = mjonPayService.selectCashRemainNewCnt(mjonPayVO); model.addAttribute("resultList", resultList); paginationInfo.setTotalRecordCount(totCnt); model.addAttribute("paginationInfo", paginationInfo); return "/uss/ion/pay/CashRemainList"; } /** * 캐시 등록/수정 * @param searchVO * @param model * @return "/uss/ion/pay/CashModify.do" * @throws Exception */ @RequestMapping(value = {"/uss/ion/pay/CashModify.do"}) public String CashModify( @ModelAttribute("searchVO") MjonPayVO searchVO, HttpServletRequest request , Model model) throws Exception { String pattern = (String) request.getAttribute( HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; MjonPayVO mjonPayVO = new MjonPayVO(); if(null != searchVO.getCashId() && !"".equals(searchVO.getCashId())) { //수정 mjonPayVO = mjonPayService.selectCashVO(searchVO); } BigDecimal cash = new BigDecimal(mjonPayVO.getCash()).setScale(2, RoundingMode.HALF_EVEN); model.addAttribute("cash",cash); model.addAttribute("mjonPayVO", mjonPayVO); model.addAttribute("searchVO", searchVO); return "/uss/ion/pay/CashModify"; } /** * * 캐시 등록 * @param searchVO * @param model * @return "/uss/ion/pay/CashInsert.do" * @throws Exception */ @RequestMapping(value = {"/uss/ion/pay/InsertCash.do"}) public String insertCash( @ModelAttribute("searchVO") MjonPayVO mjonPayVO, HttpServletRequest request , RedirectAttributes redirectAttributes, Model model) throws Exception { String pattern = (String) request.getAttribute( HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; try { LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonPayVO.setFrstRegisterId(userId); mjonPayService.insertCash(mjonPayVO); }catch(Exception e) { } redirectAttributes.addFlashAttribute("message", "등록이 완료되었습니다."); RedirectUrlMaker redirectUrlMaker = new RedirectUrlMaker("/uss/ion/pay/CashList.do"); return redirectUrlMaker.getRedirectUrl(); } /** * 관리자 회원정보 팝업 캐쉬 충전 처리 * @param MjonPayVO * @param modelAndView * @return uss/umt/user/EgovGnrlselectedUserView.do * @throws Exception */ @RequestMapping(value = "/uss/ion/pay/InsertUserCashAjax.do") public ModelAndView InsertUserCashAjax(MjonPayVO mjonPayVO, HttpServletRequest request ) throws Exception { ModelAndView modelAndView = new ModelAndView(); modelAndView.setViewName("jsonView"); LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); if(userId == null) { modelAndView.addObject("result", "loginFail"); modelAndView.addObject("message", "로그인이 필요합니다."); } try { mjonPayVO.setFrstRegisterId(userId); //관리자 로그인 아이디 셋팅 double cash = mjonPayVO.getCash(); // 충전 금액 정보 double minusCash = mjonPayVO.getMinusCash(); // 차감 금액 정보 if(cash == 0) {//충전 금액이 없고 차감 금액 정보가 있으면 캐시 정보 변경 처리 cash = -Math.abs(minusCash);//넘어오는 차감 캐시 정보에 - 가 들어있을까봐 절대값으로 변경 후 음수 처리해줌 mjonPayVO.setCash(cash); } mjonPayService.insertCash(mjonPayVO); modelAndView.addObject("result", "success"); modelAndView.addObject("message", "캐쉬 충전이 완료 되었습니다."); }catch(Exception e) { System.out.println("회원정보 팝업 캐쉬 충전 오류 발생"); modelAndView.addObject("result", "fail"); modelAndView.addObject("message", "캐쉬 충전에 오류가 발생하였습니다."); } return modelAndView; } /** * 관리자 회원정보 팝업 포인트 충전 처리 * @param MjonPayVO * @param modelAndView * @return uss/umt/user/EgovGnrlselectedUserView.do * @throws Exception */ @RequestMapping(value = "/uss/ion/pay/InsertUserPointAjax.do") public ModelAndView InsertUserPointAjax(MjonPayVO mjonPayVO, HttpServletRequest request ) throws Exception { ModelAndView modelAndView = new ModelAndView(); modelAndView.setViewName("jsonView"); LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); if(userId == null) { modelAndView.addObject("result", "loginFail"); modelAndView.addObject("message", "로그인이 필요합니다."); } try { System.out.println(userId); mjonPayVO.setFrstRegisterId(userId); //관리자 로그인 아이디 셋팅 int point = mjonPayVO.getPoint(); // 충전 포인트 정보 int minusPoint = mjonPayVO.getMinusPoint(); // 차감포인트 정보 if(point == 0) {//충전포인트가 없고 차감 포인트 정보가 있으면 포인트 정보 변경 처리 point = -Math.abs(minusPoint);//넘어오는 차감 포인트 정보에 - 가 들어있을까봐 절대값으로 변경 후 음수 처리해줌 mjonPayVO.setPoint(point); } mjonPayService.insertPoint(mjonPayVO); modelAndView.addObject("result", "success"); modelAndView.addObject("message", "포인트 충전이 완료 되었습니다."); }catch(Exception e) { System.out.println("회원정보 팝업 포인트 충전 오류 발생 Controller"); modelAndView.addObject("result", "fail"); modelAndView.addObject("message", "포인트 충전에 오류가 발생하였습니다."); } return modelAndView; } /** * 캐쉬 수정. * @param searchVO * @param hpcmVO * @param bindingResult * @return "forward:/uss/ion/pay/UpdateCash" * @throws Exception */ @RequestMapping(value = {"/uss/ion/pay/UpdateCash.do"}) public String updateCash( MjonPayVO mjonPayVO, HttpServletRequest request , RedirectAttributes redirectAttributes ) throws Exception { String pattern = (String) request.getAttribute(HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); String lastUpdusrId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); try { mjonPayVO.setLastUpdusrId(lastUpdusrId); mjonPayService.deleteCash(mjonPayVO); // 상태만 업데이트 }catch(Exception e) { redirectAttributes.addFlashAttribute("message", egovMessageSource.getMessage("fail.common.update")); } redirectAttributes.addFlashAttribute("message", egovMessageSource.getMessage("success.common.update")); RedirectUrlMaker redirectUrlMaker = new RedirectUrlMaker("/uss/ion/pay/CashList.do"); return redirectUrlMaker.getRedirectUrl(); } /** * 캐쉬 삭제 프로세서(del_flag : Y로 업데이트) * @param searchVO * @param model * @return "/uss/ion/pay/CashDelete.do" * @throws Exception */ @RequestMapping(value = {"/uss/ion/pay/CashDelete.do"}) public String deleteCash( @RequestParam("del") String[] del, @ModelAttribute("searchVO") MjonPayVO mjonPayVO, HttpServletRequest request , RedirectAttributes redirectAttributes, Model model) throws Exception { try { LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); for(String id: del) { mjonPayVO.setCashId(id); mjonPayVO.setDelFlag("Y"); //삭제 Y로 업데이트 mjonPayService.deleteCash(mjonPayVO); } redirectAttributes.addFlashAttribute("message", egovMessageSource.getMessage("success.common.update")); RedirectUrlMaker redirectUrlMaker = new RedirectUrlMaker("/uss/ion/pay/CashList.do"); return redirectUrlMaker.getRedirectUrl(); }catch(Exception e) { redirectAttributes.addFlashAttribute("message", egovMessageSource.getMessage("fail.common.update")); } redirectAttributes.addFlashAttribute("message", egovMessageSource.getMessage("success.common.delete")); RedirectUrlMaker redirectUrlMaker = new RedirectUrlMaker("/uss/ion/pay/CashList.do"); return redirectUrlMaker.getRedirectUrl(); } /** * 결제 리스트 * @param searchVO * @param model * @return "/uss/ion/msg/SendNumberList" * @throws Exception */ @RequestMapping(value= {"/uss/ion/pay/PayList.do"}) public String selectPayList(@ModelAttribute("searchVO") MjonPayVO mjonPayVO, HttpServletRequest request, ModelMap model) throws Exception{ String pattern = (String) request.getAttribute(HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; /** pageing */ PaginationInfo paginationInfo = new PaginationInfo(); paginationInfo.setCurrentPageNo(mjonPayVO.getPageIndex()); paginationInfo.setRecordCountPerPage(mjonPayVO.getPageUnit()); paginationInfo.setPageSize(mjonPayVO.getPageSize()); mjonPayVO.setFirstIndex(paginationInfo.getFirstRecordIndex()); mjonPayVO.setLastIndex(paginationInfo.getLastRecordIndex()); mjonPayVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage()); if("".equals(mjonPayVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonPayVO.setSearchSortCnd("regDate"); mjonPayVO.setSearchSortOrd("desc"); } List<MjonPayVO> resultList = mjonPayService.selectPayList(mjonPayVO); model.addAttribute("resultList", resultList); paginationInfo.setTotalRecordCount(resultList.size() > 0 ? ((MjonPayVO)resultList.get(0)).getTotCnt() : 0); model.addAttribute("paginationInfo", paginationInfo); return "/uss/ion/pay/PayList"; } //관리자 결제 엑셀 다운로드 @RequestMapping(value= {"/uss/ion/pay/SendPayExcelDownload.do"}) public void SendPayExcelDownload( MjonPayVO mjonPayVO, HttpServletRequest request, HttpServletResponse response , ModelMap model) throws Exception { mjonPayVO.setRecordCountPerPage(5000); mjonPayVO.setFirstIndex(0); LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); // 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다. SXSSFWorkbook wb = new SXSSFWorkbook(100); CellStyle style = wb.createCellStyle(); style.setBorderBottom(CellStyle.BORDER_THIN); //테두리 두껍게 style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderTop(CellStyle.BORDER_THIN); Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); //글씨 bold Cell cell = null; Row row = null; String fileName ="결제내역"; String sheetTitle = ""; try{ if("".equals(mjonPayVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonPayVO.setSearchSortCnd("regDate"); mjonPayVO.setSearchSortOrd("desc"); } List<MjonPayVO> resultList = mjonPayService.selectPayList(mjonPayVO); { //화면 리스트 sheetTitle = "결제내역" ; //제목 Sheet sheet = wb.createSheet(sheetTitle); sheet.setColumnWidth(0, 2500); sheet.setColumnWidth(1, 3500); sheet.setColumnWidth(2, 3500); sheet.setColumnWidth(3, 4000); sheet.setColumnWidth(4, 3500); sheet.setColumnWidth(5, 5000); row = sheet.createRow(0); for(int i=0 ; i < sendPayExcelValue2.length ; i++) { cell = row.createCell(i); cell.setCellStyle(style); cell.setCellValue(sendPayExcelValue2[i][1]); } for(int i=0; i < resultList.size(); i++){ row = sheet.createRow(i+1); for(int j=0 ; j < sendPayExcelValue2.length ; j++) { // 번호, 결제일자, 결제시간, 결제수단, 결제상태, 결제금액 cell = row.createCell(j); cell.setCellStyle(style); if(j==0) cell.setCellValue(i+1); //번호 if(j==1) cell.setCellValue(((MjonPayVO)resultList.get(i)).getRegDate().substring(0,10)); //결제일시 if(j==2) cell.setCellValue(((MjonPayVO)resultList.get(i)).getRegDate().substring(11,19)); //결제일시 if(j==3) cell.setCellValue(((MjonPayVO)resultList.get(i)).getPayMethodTxt()); //결제수단 if(j==4) cell.setCellValue(((MjonPayVO)resultList.get(i)).getPgStatusTxt()); //결제상태 if(j==5) cell.setCellValue(((MjonPayVO)resultList.get(i)).getAmt()); //결제금액(부과세포함) } } } response.setHeader("Set-Cookie", "fileDownload=true; path=/"); SimpleDateFormat mSimpleDateFormat = new SimpleDateFormat ( "yyyy_MM_dd_HH_mm_ss", Locale.KOREA ); Date currentTime = new Date (); String mTime = mSimpleDateFormat.format ( currentTime ); fileName = fileName+"("+mTime+")"; response.setHeader("Content-Disposition", String.format("attachment; filename=\""+new String((fileName).getBytes("KSC5601"),"8859_1")+".xlsx")); wb.write(response.getOutputStream()); }catch(Exception e) { response.setHeader("Set-Cookie", "fileDownload=false; path=/"); response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate"); response.setHeader("Content-Type","text/html; charset=utf-8"); OutputStream out = null; try { out = response.getOutputStream(); byte[] data = new String("fail..").getBytes(); out.write(data, 0, data.length); } catch(Exception ignore) { ignore.printStackTrace(); } finally { if(out != null) try { out.close(); } catch(Exception ignore) {} } }finally { // 디스크 적었던 임시파일을 제거합니다. wb.dispose(); try { wb.close(); } catch(Exception ignore) {} } } /** * 결제 리스트 스미싱 on off 처리 * @param searchVO * @param model * @return "/uss/ion/pay/PayUsersSmishingUpdate.do" * @throws Exception */ @RequestMapping("/uss/ion/pay/PayUsersSmishingUpdate.do") public String PayUsersSmishingUpdate(@RequestParam("checkedIdForDel") String checkedIdForUpt, @ModelAttribute("userManageVO") UserManageVO userManageVO, RedirectAttributes redirectAttributes, Model model) throws Exception { // 미인증 사용자에 대한 보안처리 Boolean isAuthenticated = EgovUserDetailsHelper.isAuthenticated(); if(!isAuthenticated) { model.addAttribute("message", egovMessageSource.getMessage("fail.common.login")); return "uat/uia/EgovLoginUsr"; } userManageService.updateUserSmishingYn(userManageVO, checkedIdForUpt); redirectAttributes.addFlashAttribute("message", "스미싱의심 여부가 정상적으로 수정되었습니다."); RedirectUrlMaker redirectUrlMaker = new RedirectUrlMaker("/uss/ion/pay/PayList.do?pageIndex="+userManageVO.getPageIndex()); return redirectUrlMaker.getRedirectUrl(); } /** * 후불제 고객 입금확인 후 결제완료로 수정하기 위한 팝업 * @param mjonPayVO * @param request * @param model * @return * @throws Exception */ @RequestMapping("/uss/ion/pay/PgStatusPop.do") public String selectedUserView(@ModelAttribute MjonPayVO mjonPayVO, HttpServletRequest request, Model model) throws Exception { model.addAttribute("mjonPayVO",mjonPayVO); return "/uss/ion/pay/PgStatusPop"; } /** * 후불제 고객 입금확인 후 결제완료로 수정하기 위한 팝업 * @param mjonPayVO * @param request * @param model * @return * @throws Exception */ @RequestMapping("/uss/ion/pay/updatePgStatusAfterPayAjax.do") public ModelAndView updatePgStatusAfterPayAjax(@ModelAttribute("searchVO") MjonPayVO mjonPayVO, ModelMap model) throws Exception{ ModelAndView modelAndView = new ModelAndView(); modelAndView.setViewName("jsonView"); boolean isSuccess = true; int updtCnt = 0; String msg = "결제완료 처리되었습니다."; try { LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); if(userId.equals("")) { isSuccess = false; modelAndView.addObject("isSuccess", isSuccess); modelAndView.addObject("msg", "로그인 후 이용이 가능합니다."); return modelAndView; } // 후불 결제완료 + 포인트 추가 mjonPayService.updateMjonPgStatusAfterPayPointSave(mjonPayVO); } catch(Exception e) { isSuccess = false; msg = "오류 : " + e.getMessage(); } modelAndView.addObject("isSuccess", isSuccess); modelAndView.addObject("msg", msg); return modelAndView; } /** * 결제 등록/수정 * @param searchVO * @param model * @return "/uss/ion/pay/CashModify.do" * @throws Exception */ @RequestMapping(value = {"/uss/ion/pay/PayModify.do"}) public String PayModify( @ModelAttribute("searchVO") MjonPayVO searchVO, HttpServletRequest request , Model model) throws Exception { String pattern = (String) request.getAttribute( HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; try { MjonPayVO mjonPayVO = new MjonPayVO(); if(null != searchVO.getMoid() && !"".equals(searchVO.getMoid())) { //수정 mjonPayVO = mjonPayService.selectPayVO(searchVO); } String referer = (String)request.getHeader("REFERER"); model.addAttribute("pointPage", referer.contains("PointList")); //포인트페이지에서 온것 true model.addAttribute("cashPage", referer.contains("CashList")); //캐시페이지에서 온것 true model.addAttribute("mjonPayVO", mjonPayVO); model.addAttribute("searchVO", searchVO); } catch(Exception e) { System.out.println("searchVO.getMoid() : " + searchVO.getMoid()); System.out.println("PayModify Error : " + e.getMessage()); } return "/uss/ion/pay/PayModify"; } /** * 나이스페이(PG사 전송화면) * * @param * @return * @throws Exception */ @RequestMapping("/web/cop/nicepay/payRequestAjax.do") public String payRequest(HttpServletRequest request, ModelMap model , @RequestParam Map<String, Object> commandMap, @ModelAttribute("searchVO") MjonPayVO mjonPayVO) throws Exception { mjonPayVO.setReturnURL(request.getRequestURL().toString().split("nice")[0] + "nicepay/payResultAjax.do") ; //테스트 if(null == mjonPayVO.getPrice()) { mjonPayVO.setPrice("500"); } if(null == mjonPayVO.getBuyerName()) { mjonPayVO.setBuyerName("우영두"); } if(null == mjonPayVO.getBuyerTel()) { mjonPayVO.setBuyerTel("01063170383"); } if(null == mjonPayVO.getBuyerEmail()) { mjonPayVO.setBuyerEmail("rosehips@naver.com"); } if(null == mjonPayVO.getMoid()) { mjonPayVO.setMoid("mnoid1234567890"); } model.addAttribute("mjonPayVO", mjonPayVO); //return "web/cop/nicepay/payRequest"; return "web/cop/nicepay/payRequestAjax"; } /** * 나이스페이(PG사 결과 리턴 페이지) * * @param * @return * @throws Exception */ @RequestMapping( value = {"/web/cop/nicepay/payResultAjax.do" , "/web/member/pay/payResultAjax.do"}) public String payResult(HttpServletRequest request, ModelMap model , @RequestParam Map<String, Object> commandMap, @ModelAttribute("searchVO") MjonPayVO mjonPayVO) throws Exception { String pattern = (String) request.getAttribute( HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; //mjonPayVO.setReturnURL(request.getRequestURL().toString().split("nice")[0] + "payResult.do") ; MjonPayVO returnMjonPayVO = new MjonPayVO(); returnMjonPayVO = mjonPayService.insertPay(mjonPayVO , request, "nice" ); //PG, PGTXT, CASH , 포인트 insert / 회원정보 캐시,포인트 update model.addAttribute("mjonPayVO", returnMjonPayVO); if(pattern.equals("/web/member/pay/payResultAjax.do")){ model.addAttribute("userPage", true); } return "web/cop/nicepay/payResultAjax"; } /** * 나이스페이(PG사 결제취소화면) * * @param * @return * @throws Exception */ @RequestMapping("/web/cop/nicepay/cancelRequestAjax.do") public String cancelRequestAjax(HttpServletRequest request, ModelMap model , @RequestParam Map<String, Object> commandMap, @ModelAttribute("searchVO") MjonPayVO mjonPayVO) throws Exception { mjonPayVO.setReturnURL(request.getRequestURL().toString().split("nice")[0] + "nicepay/payResultAjax.do") ; //테스트 if(null == mjonPayVO.getPrice()) { mjonPayVO.setPrice("500"); } if(null == mjonPayVO.getBuyerName()) { mjonPayVO.setBuyerName("우영두"); } if(null == mjonPayVO.getBuyerTel()) { mjonPayVO.setBuyerTel("01063170383"); } if(null == mjonPayVO.getBuyerEmail()) { mjonPayVO.setBuyerEmail("rosehips@naver.com"); } if(null == mjonPayVO.getMoid()) { mjonPayVO.setMoid("mnoid1234567890"); } model.addAttribute("mjonPayVO", mjonPayVO); //return "web/cop/nicepay/payRequest"; return "web/cop/nicepay/cancelRequestAjax"; } /** * 나이스페이(PG사 결제취소 결과) * * @param * @return * @throws Exception */ @RequestMapping("/web/cop/nicepay/cancelResultAjax.do") public String cancelResultAjax(HttpServletRequest request, ModelMap model , @RequestParam Map<String, Object> commandMap, @ModelAttribute("searchVO") MjonPayVO mjonPayVO) throws Exception { MjonPayVO returnMjonPayVO = new MjonPayVO(); returnMjonPayVO = mjonPayService.cancelPay(mjonPayVO , request ); //PG, CASH , 포인트 insert / 회원정보 캐시,포인트 update model.addAttribute("mjonPayVO", returnMjonPayVO); return "web/cop/nicepay/cancelResultAjax"; } /** * 나이스페이(PG사 전송화면) * * @param * @return * @throws Exception */ @RequestMapping("/web/cop/kmc/authRequestAjax.do") public String authRequestAjax(HttpServletRequest request, HttpSession session, HttpServletResponse response, ModelMap model , @RequestParam Map<String, Object> commandMap, @ModelAttribute("searchVO") KmcVO kmcVO) throws Exception { //크롬 SameSite정책 방지 - 도메인이 다른 타사로 이동 시 크롬 정책에 의해 세션 유실이 일어나는 경우가 있는데, 이를 방지하기 위해 samesite 보안을 none처리 response.setHeader("Set-Cookie", "mberSession=mberSession; Secure; SameSite=None"); System.out.println("++++++++++++ 세션체크 ::: "+ session.getAttribute("mberSession")); String errMessage = ""; //에러메세지 String rec_cert = ""; // 결과값(암호화) String certNum = ""; // certNum rec_cert = request.getParameter("rec_cert").trim(); certNum = request.getParameter("certNum").trim(); kmcVO.setRecCert(rec_cert); kmcVO.setCertNum(certNum); // 파라미터 유효성 검증 if( rec_cert.length() == 0 || certNum.length() == 0 ){ errMessage = "비정상"; return returnPage(model , errMessage , kmcVO) ; } // 변수선언 -------------------------------------------------------------------------------------------------------- String k_certNum = ""; // 파라미터로 수신한 요청번호 k_certNum = certNum; String date = ""; // 요청일시 String CI = ""; // 연계정보(CI) String DI = ""; // 중복가입확인정보(DI) String phoneNo = ""; // 휴대폰번호 String phoneCorp = ""; // 이동통신사 String birthDay = ""; // 생년월일 String gender = ""; // 성별 String nation = ""; // 내국인 String name = ""; // 성명 String M_name = ""; // 미성년자 성명 String M_birthDay = ""; // 미성년자 생년월일 String M_Gender = ""; // 미성년자 성별 String M_nation = ""; // 미성년자 내외국인 String result = ""; // 결과값 String certMet = ""; // 인증방법 String ip = ""; // ip주소 String plusInfo = ""; String encPara = ""; String encMsg1 = ""; String encMsg2 = ""; String msgChk = ""; com.icert.comm.secu.IcertSecuManager seed = new com.icert.comm.secu.IcertSecuManager(); //02. 1차 복호화 //수신된 certNum를 이용하여 복호화 rec_cert = seed.getDec(rec_cert, k_certNum); //03. 1차 파싱 int inf1 = rec_cert.indexOf("/",0); int inf2 = rec_cert.indexOf("/",inf1+1); encPara = rec_cert.substring(0,inf1); //암호화된 통합 파라미터 encMsg1 = rec_cert.substring(inf1+1,inf2); //암호화된 통합 파라미터의 Hash값 //04. 위변조 검증 encMsg2 = seed.getMsg(encPara); kmcVO.setEncMsg2(encMsg2); if(encMsg2.equals(encMsg1)){ msgChk="Y"; } if(!"Y".equals(msgChk)) { errMessage = "비정상접근입니다."; return returnPage(model , errMessage , kmcVO) ; } //05. 2차 복호화 rec_cert = seed.getDec(encPara, k_certNum); kmcVO.setRecCert(rec_cert); //06. 2차 파싱 int info1 = rec_cert.indexOf("/",0); int info2 = rec_cert.indexOf("/",info1+1); int info3 = rec_cert.indexOf("/",info2+1); int info4 = rec_cert.indexOf("/",info3+1); int info5 = rec_cert.indexOf("/",info4+1); int info6 = rec_cert.indexOf("/",info5+1); int info7 = rec_cert.indexOf("/",info6+1); int info8 = rec_cert.indexOf("/",info7+1); int info9 = rec_cert.indexOf("/",info8+1); int info10 = rec_cert.indexOf("/",info9+1); int info11 = rec_cert.indexOf("/",info10+1); int info12 = rec_cert.indexOf("/",info11+1); int info13 = rec_cert.indexOf("/",info12+1); int info14 = rec_cert.indexOf("/",info13+1); int info15 = rec_cert.indexOf("/",info14+1); int info16 = rec_cert.indexOf("/",info15+1); int info17 = rec_cert.indexOf("/",info16+1); int info18 = rec_cert.indexOf("/",info17+1); certNum = rec_cert.substring(0,info1); kmcVO.setCertNum(certNum); date = rec_cert.substring(info1+1,info2); kmcVO.setDate(date); CI = rec_cert.substring(info2+1,info3); kmcVO.setCI(CI); phoneNo = rec_cert.substring(info3+1,info4); kmcVO.setPhoneNo(phoneNo); phoneCorp = rec_cert.substring(info4+1,info5); kmcVO.setPhoneCorp(phoneCorp); birthDay = rec_cert.substring(info5+1,info6); kmcVO.setBirthDay(birthDay); gender = rec_cert.substring(info6+1,info7); kmcVO.setGender(gender); nation = rec_cert.substring(info7+1,info8); kmcVO.setNation(nation); name = rec_cert.substring(info8+1,info9); kmcVO.setName(name); result = rec_cert.substring(info9+1,info10); kmcVO.setResult(result); certMet = rec_cert.substring(info10+1,info11); kmcVO.setCertMet(certMet); ip = rec_cert.substring(info11+1,info12); kmcVO.setIp(ip); M_name = rec_cert.substring(info12+1,info13); kmcVO.setMName(M_name); M_birthDay = rec_cert.substring(info13+1,info14); kmcVO.setMBirthDay(M_birthDay); M_Gender = rec_cert.substring(info14+1,info15); kmcVO.setMGender(M_Gender); M_nation = rec_cert.substring(info15+1,info16); kmcVO.setMNation(M_nation); plusInfo = rec_cert.substring(info16+1,info17); kmcVO.setPlusInfo(plusInfo); DI = rec_cert.substring(info17+1,info18); kmcVO.setDI(DI); //07. CI, DI 복호화 CI = seed.getDec(CI, k_certNum); kmcVO.setCI(CI); DI = seed.getDec(DI, k_certNum); kmcVO.setDI(DI); if("Y".equals(result)) { } //-------------------------------------------------------------- String regex = ""; if( certNum.length() == 0 || certNum.length() > 40){ errMessage = "요청번호 비정상."; return returnPage(model , errMessage , kmcVO) ; } regex = "[0-9]*"; if( date.length() != 14 || !paramChk(regex, date) ){ errMessage = "요청일시"; return returnPage(model , errMessage , kmcVO) ; } regex = "[A-Z]*"; if( certMet.length() != 1 || !paramChk(regex, certMet) ){ errMessage = "본인인증방법 비정상" + certMet; return returnPage(model , errMessage , kmcVO) ; } regex = "[0-9]*"; if( (phoneNo.length() != 10 && phoneNo.length() != 11) || !paramChk(regex, phoneNo) ){ errMessage = "휴대폰번호 비정상" ; return returnPage(model , errMessage , kmcVO) ; } regex = "[A-Z]*"; if( phoneCorp.length() != 3 || !paramChk(regex, phoneCorp) ){ errMessage = "이동통신사 비정상"; return returnPage(model , errMessage , kmcVO) ; } regex = "[0-9]*"; if( birthDay.length() != 8 || !paramChk(regex, birthDay) ){ errMessage = "생년월일 비정상"; return returnPage(model , errMessage , kmcVO) ; } regex = "[0-9]*"; if( gender.length() != 1 || !paramChk(regex, gender) ){ errMessage = "성별 비정상"; return returnPage(model , errMessage , kmcVO) ; } regex = "[0-9]*"; if( nation.length() != 1 || !paramChk(regex, nation) ){ errMessage = "내/외국인 비정상"; return returnPage(model , errMessage , kmcVO) ; } regex = "[\\sA-Za-z가-�R.,-]*"; if( name.length() > 60 || !paramChk(regex, name) ){ errMessage = "성명 비정상"; return returnPage(model , errMessage , kmcVO) ; } regex = "[A-Z]*"; if( result.length() != 1 || !paramChk(regex, result) ){ errMessage = "결과값 비정상"; return returnPage(model , errMessage , kmcVO) ; } regex = "[\\sA-Za-z가-?.,-]*"; if( M_name.length() != 0 ){ if( M_name.length() > 60 || !paramChk(regex, M_name) ){ errMessage = "미성년자 성명 비정상"; return returnPage(model , errMessage , kmcVO) ; } } regex = "[0-9]*"; if( M_birthDay.length() != 0 ){ if( M_birthDay.length() != 8 || !paramChk(regex, M_birthDay) ){ errMessage = "미성년자 생년월일 비정상"; return returnPage(model , errMessage , kmcVO) ; } } regex = "[0-9]*"; if( M_Gender.length() != 0 ){ if( M_Gender.length() != 1 || !paramChk(regex, M_Gender) ){ errMessage = "미성년자 성별 비정상"; return returnPage(model , errMessage , kmcVO) ; } } regex = "[0-9]*"; if( M_nation.length() != 0 ){ if( M_nation.length() != 1 || !paramChk(regex, M_nation) ){ errMessage = "미성년자 내/외국인 비정상"; return returnPage(model , errMessage , kmcVO) ; } } //본인인증 결과 내용 저장하기. /*System.out.println("+++++++++++++ getCertNum ::: "+kmcVO.getCertNum()); System.out.println("+++++++++++++ getDate ::: "+kmcVO.getDate()); System.out.println("+++++++++++++ getDI ::: "+kmcVO.getDI()); System.out.println("+++++++++++++ getPhoneNo ::: "+kmcVO.getPhoneNo()); System.out.println("+++++++++++++ getNation ::: "+kmcVO.getNation()); System.out.println("+++++++++++++ getName ::: "+kmcVO.getName()); System.out.println("+++++++++++++ getResult ::: "+kmcVO.getResult()); System.out.println("+++++++++++++ getCertMet ::: "+kmcVO.getCertMet()); System.out.println("+++++++++++++ getIp ::: "+kmcVO.getIp());*/ //KMC 본인인증 정보를 세션에 담아준다. /*AuthCertVO certVO = new AuthCertVO(); certVO.setCertNum(kmcVO.getCertNum()); certVO.setCertDate(kmcVO.getDate()); certVO.setCertDi(kmcVO.getDI()); certVO.setCertPhone(kmcVO.getPhoneNo()); certVO.setCertNation(kmcVO.getNation()); certVO.setCertName(kmcVO.getName()); certVO.setCertResult(kmcVO.getResult()); certVO.setCertType("KMC문자인증"); certVO.setCertIpaddr(kmcVO.getIp()); certVO.setBirthDay(kmcVO.getBirthDay()); certVO.setSexdstnCode(kmcVO.getGender());*/ /*request.getSession().setAttribute("AuthKmcCertVO", certVO);*/ /*System.out.println("++++++++++++ 세션체크22 ::: "+(MberManageVO) session.getAttribute("mberSession"));*/ //KMC 본인인증 로그 insert AuthCertVO certVO = new AuthCertVO(); certVO.setMberId(kmcVO.getPlusInfo()); certVO.setCertNum(kmcVO.getCertNum()); certVO.setCertDate(kmcVO.getDate()); certVO.setCertDi(kmcVO.getDI()); certVO.setCertPhone(kmcVO.getPhoneNo()); certVO.setCertNation(kmcVO.getNation()); certVO.setCertName(kmcVO.getName()); certVO.setCertResult(kmcVO.getResult()); certVO.setCertType("KMC_회원가입 인증"); certVO.setCertIpaddr(kmcVO.getIp()); certVO.setBirthDay(kmcVO.getBirthDay()); certVO.setSexdstnCode(kmcVO.getGender()); //디비 테이블에 저장하기 mberManageService.insertCertInfoLog(certVO); kmcVO.setIdx(certVO.getIdx()); //본인인증 로그 Idx - 로그 insert 후 idx selectKey model.addAttribute("kmcVO", kmcVO); return "web/cop/kmc/authRequestAjax"; } // 파라미터 유효성 검증 -------------------------------------------- public Boolean paramChk(String patn, String param){ Pattern pattern = Pattern.compile(patn); Matcher matcher = pattern.matcher(param); boolean b = matcher.matches(); return b; } // 파라미터 유효성 검증 -------------------------------------------- public String returnPage(ModelMap model , String errMessage , KmcVO kmcVO){ kmcVO.setErrMessage(errMessage); model.addAttribute("kmcVO", kmcVO); return "web/cop/nicepay/payRequestAjax"; } /** * 세틀뱅크 가상계좌 입금 자동충전 * * @param * @return * @throws Exception */ @RequestMapping("/vcCharge.do") public void vcCharge(HttpServletRequest request, HttpServletResponse response, ModelMap model ,@RequestParam Map<String, Object> commandMap) throws Exception { String param = (String) commandMap.get("vcp"); System.out.println("VACS vcCharge param: " + param); String result = "succ"; if (param != null) { String[] params = param.split("\\|"); // 기관코드|거래일자|거래번호 StVcVO paramVO = new StVcVO(); paramVO.setOrgCd(params[0]); paramVO.setTrIl(params[1]); paramVO.setTrNo(params[2]); // try { result = mjonPayService.insertVcCharge(paramVO); // 자동충전 // } catch (Exception e) { // // TODO: handle exception // } } else { result = "fail"; } response.setContentType("text/plain"); PrintWriter printwriter = response.getWriter(); printwriter.println(result); printwriter.flush(); printwriter.close(); } /** * 세틀뱅크 가상계좌 리스트 업로드 * * @param * @return * @throws Exception */ @RequestMapping("/vcAccUploadAjax.do") public void vcAccUpload(final MultipartHttpServletRequest multiRequest, ModelMap model ,@RequestParam Map<String, Object> commandMap) throws Exception { List<MultipartFile> files = (List<MultipartFile>) multiRequest.getFiles("file0"); BufferedReader br = null; for (MultipartFile file : files) { if(files.isEmpty()) continue; br = new BufferedReader(new InputStreamReader((InputStream) file, Charset.forName("UTF-8"))); String line = ""; while ((line = br.readLine()) != null) { if (line.length() < 5) continue; LOGGER.debug("가상계좌: " + line); } } } /** * 문자나라 웹연동 SMS 전송 샘플(우체국 홈페이지에서 사용) * * @param * @return * @throws Exception */ @RequestMapping("/cop/testMjnaraSend.do") public String testMjnaraSend(HttpServletRequest request, ModelMap model , @RequestParam Map<String, Object> commandMap) throws Exception { /** * 요기. 컨트롤러가 아닌 서비스나 메소드로 수정하여, * 갑질게시판 관리자 만큼 호출해서 사용하도록 변경 필요 > 이지우 * * 해당 메소드에 게시판ID, n명(담당자ID, 담당자전화번호) * * 프로세스. 게시판 등록 컨트롤러에서 갑질게시판 등록 시 이 컨트롤러를 서비스나 메소드로 변환하여 루프로 처리 */ String userid ="rlaqhal7278"; // 문자나라 아이디 String passwd ="dudfks0829"; // 문자나라 2차 비밀번호 dudfks0829 String hpSender ="01066137278"; // 보내는분 핸드폰번호 String hpReceiver ="01085326650"; // 받는분의 핸드폰번호 String hpMesg ="[우체국물류지원단 대표홈페이지] 갑질 우체통에 게시글이 등록되었습니다."; // 메시지 // hpMesg = URLEncoder.encode(hpMesg, "EUC-KR"); String strUrl = ""; strUrl = "https://www.munjanara.co.kr/MSG/send/web_admin_send.htm?userid=" + userid + "&passwd=" + passwd + "&sender=" + hpSender + "&receiver=" + hpReceiver + "&encode=0&message=" + hpMesg; BufferedReader in = null; StringBuffer response = new StringBuffer(); String excpMsg = ""; try { URL obj = new URL(strUrl); // 호출할 url HttpURLConnection con = (HttpURLConnection)obj.openConnection(); con.setRequestMethod("GET"); // in = new BufferedReader(new InputStreamReader(con.getInputStream(), "UTF-8")); in = new BufferedReader(new InputStreamReader(con.getInputStream(), "EUC-KR")); System.out.print("문자나라 전송결과: "); String line; while((line = in.readLine()) != null) { // response를 차례대로 출력 System.out.println(line); response.append(line); } if(in != null) try { in.close(); } catch(Exception e) { System.out.print("문자나라 전송 ERROR : " + e.getMessage()); } } catch(Exception e) { excpMsg = e.getMessage(); System.out.print("문자나라 전송 ERROR : " + excpMsg); } finally { if(in != null) try { in.close(); } catch(Exception e) { System.out.print("문자나라 전송 ERROR : " + e.getMessage()); } String result = response.toString(); // 문자나라 전송결과 리턴값. 코드|메세지 (9|"성공" , 1|"필수전달값이 빠짐" 등 문자나라 사이트 참조) /* * 요기. 전송내역 insert 필요 > 이지우 * 1. 전송내역 테이블 생성(IDX, 전송일시, 전송결과 코드, 전송결과 메세지, 게시글번호, 담당자ID, 담당자전화번호) * 2. 전송내역 insert * 2-1 result 리턴값이 없고, excpMsg 값이 있다면, 전송자체 오류건으로 insert(전송결과 -1, 전송결과메세지 excpMsg) */ } model.addAttribute("sendResult", "전송결과: " + response.toString()); return "cop/testMjnaraSend"; } /** * 캐시 리스트 * @param searchVO * @param model * @return "/uss/ion/msg/SendNumberList" * @throws Exception */ @RequestMapping(value= {"/uss/ion/pay/PointList.do"}) public String pointList(@ModelAttribute("searchVO") MjonPayVO mjonPayVO, HttpServletRequest request, ModelMap model) throws Exception{ String pattern = (String) request.getAttribute( HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; /** pageing */ PaginationInfo paginationInfo = new PaginationInfo(); paginationInfo.setCurrentPageNo(mjonPayVO.getPageIndex()); paginationInfo.setRecordCountPerPage(mjonPayVO.getPageUnit()); paginationInfo.setPageSize(mjonPayVO.getPageSize()); mjonPayVO.setFirstIndex(paginationInfo.getFirstRecordIndex()); mjonPayVO.setLastIndex(paginationInfo.getLastRecordIndex()); mjonPayVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage()); if("".equals(mjonPayVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonPayVO.setSearchSortCnd("frstRegistPnttm"); mjonPayVO.setSearchSortOrd("desc"); } if(null == mjonPayVO.getSearchCondition2()) { //최초조회시 (미삭제 만 보이게) mjonPayVO.setSearchCondition2("N"); } // 기간검색 설정 String beforeMonthDay = MJUtil.getBefore1MonthDate(); //한달 전 if (null == mjonPayVO.getSearchStartDate() || mjonPayVO.getSearchStartDate().equals("")) { mjonPayVO.setSearchStartDate(beforeMonthDay); } List<MjonPayVO> resultList = mjonPayService.selectPointList(mjonPayVO); model.addAttribute("resultList", resultList); paginationInfo.setTotalRecordCount(resultList.size() > 0 ? ((MjonPayVO)resultList.get(0)).getTotCnt() : 0); model.addAttribute("paginationInfo", paginationInfo); return "/uss/ion/pay/PointList"; } /** * 포인트 등록/수정 * @param searchVO * @param model * @return "/uss/ion/pay/PointModify.do" * @throws Exception */ @RequestMapping(value = {"/uss/ion/pay/PointModify.do"}) public String PointModify( @ModelAttribute("searchVO") MjonPayVO searchVO, HttpServletRequest request , Model model) throws Exception { String pattern = (String) request.getAttribute( HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; MjonPayVO mjonPayVO = new MjonPayVO(); if(null != searchVO.getPointId() && !"".equals(searchVO.getPointId())) { //수정 mjonPayVO = mjonPayService.selectPointVO(searchVO); } model.addAttribute("mjonPayVO", mjonPayVO); model.addAttribute("searchVO", searchVO); return "/uss/ion/pay/PointModify"; } /** * * 캐시 등록 * @param searchVO * @param model * @return "/uss/ion/pay/CashInsert.do" * @throws Exception */ @RequestMapping(value = {"/uss/ion/pay/InsertPoint.do"}) public String insertPoint( @ModelAttribute("searchVO") MjonPayVO mjonPayVO, HttpServletRequest request , RedirectAttributes redirectAttributes, Model model) throws Exception { String pattern = (String) request.getAttribute( HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; try { LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonPayVO.setFrstRegisterId(userId); mjonPayService.insertPoint(mjonPayVO); }catch(Exception e) { } redirectAttributes.addFlashAttribute("message", "등록이 완료되었습니다."); RedirectUrlMaker redirectUrlMaker = new RedirectUrlMaker("/uss/ion/pay/PointList.do"); return redirectUrlMaker.getRedirectUrl(); } /** * 포인트 캐쉬 삭제 프로세서(del_flag : Y로 업데이트) * @param searchVO * @param model * @return "/uss/ion/pay/CashDelete.do" * @throws Exception */ @RequestMapping(value = {"/uss/ion/pay/PointDelete.do"}) public String pointDelete( @RequestParam("del") String[] del, @ModelAttribute("searchVO") MjonPayVO mjonPayVO, HttpServletRequest request , RedirectAttributes redirectAttributes, Model model) throws Exception { try { LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); for(String id: del) { mjonPayVO.setPointId(id); mjonPayVO.setDelFlag("Y"); //삭제 Y로 업데이트 mjonPayService.deletePoint(mjonPayVO); } redirectAttributes.addFlashAttribute("message", egovMessageSource.getMessage("success.common.update")); RedirectUrlMaker redirectUrlMaker = new RedirectUrlMaker("/uss/ion/pay/PointList.do"); return redirectUrlMaker.getRedirectUrl(); }catch(Exception e) { redirectAttributes.addFlashAttribute("message", egovMessageSource.getMessage("fail.common.update")); } redirectAttributes.addFlashAttribute("message", egovMessageSource.getMessage("success.common.delete")); RedirectUrlMaker redirectUrlMaker = new RedirectUrlMaker("/uss/ion/pay/PointList.do"); return redirectUrlMaker.getRedirectUrl(); } /** * 캐쉬 수정. * @param searchVO * @param hpcmVO * @param bindingResult * @return "forward:/uss/ion/pay/UpdateCash" * @throws Exception */ @RequestMapping(value = {"/uss/ion/pay/UpdatePoint.do"}) public String updatePoint( MjonPayVO mjonPayVO, HttpServletRequest request , RedirectAttributes redirectAttributes ) throws Exception { String pattern = (String) request.getAttribute(HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); String lastUpdusrId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); try { mjonPayVO.setLastUpdusrId(lastUpdusrId); mjonPayService.deletePoint(mjonPayVO); // 상태만 업데이트 }catch(Exception e) { redirectAttributes.addFlashAttribute("message", egovMessageSource.getMessage("fail.common.update")); } redirectAttributes.addFlashAttribute("message", egovMessageSource.getMessage("success.common.update")); RedirectUrlMaker redirectUrlMaker = new RedirectUrlMaker("/uss/ion/pay/PointList.do"); return redirectUrlMaker.getRedirectUrl(); } /** * 입력한 사용자아이디의 중복여부를 체크하여 사용가능여부를 확인 * @param commandMap 파라메터전달용 commandMap * @param model 화면모델 * @return uss/umt/EgovIdDplctCnfirm * @throws Exception */ @RequestMapping(value = "/uss/umt/user/ExistMemberIdAjax.do") public ModelAndView EgovIdDplctCnfirmAjax(@RequestParam Map<String, Object> commandMap) throws Exception { ModelAndView modelAndView = new ModelAndView(); modelAndView.setViewName("jsonView"); String checkId = (String) commandMap.get("checkId"); int usedCnt = userManageService.checkIdDplct(checkId); modelAndView.addObject("usedCnt", usedCnt); modelAndView.addObject("checkId", checkId); return modelAndView; } /** * 요금안내/견적내기 * @param searchVO * @param model * @return "web/cop/bbs/NoticeList.do" * @throws Exception */ @RequestMapping(value= {"/web/pay/PayGuide.do"}) public String PayGuide(@ModelAttribute("searchVO") MjonPayVO mjonPayVO, @RequestParam(value = "selected", defaultValue="a") String selected, HttpServletRequest request, RedirectAttributes redirectAttributes, ModelMap model) throws Exception{ LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); MberManageVO mberManageVO = null; if(!userId.equals("")) { mberManageVO = mberManageService.selectMber(loginVO.getId()); model.addAttribute("mberManageVO", mberManageVO); MjonCandidateVO mjonCandidateVO = new MjonCandidateVO(); if(userId != "") { mjonCandidateVO = mjonCandidateService.selectCandidateDataInfo(userId); model.addAttribute("mjonCandidateVO",mjonCandidateVO); } } //개별문자 가격 및 총 금액 셋팅해주기 - 시스템 기본 단가기준으로 입력해줌 //1.시스템 기본 단가 정보 불러오기 JoinSettingVO sysJoinSetVO = mjonMsgDataService.selectJoinSettingInfo(); // 등급제 단가 추출 => 시스템 단가에 적용 sysJoinSetVO = mberGrdService.selectMberGrdDefaultInfo(sysJoinSetVO, userId); float shortPrice = 0; // 단문(SMS) float longPrice = 0; // 장문(LMS) float picturePrice = 0; // 그림문자(MMS) 1장 float picturePrice2 = 0; // 그림문자(MMS) 2장 float picturePrice3 = 0; // 그림문자(MMS) 3장 String customSamplePrice = "0"; // 문자온 샘플 수정(글자, 색상, 폰트 등) String customEditPrice = "0"; // 이미지 편집(첨부이미지 1장) String customEdit3Price = "0"; // 이미지 편집(첨부이미지 3장이하) String customTextPrice = "0"; // 텍스트 단순수정 float kakaoAtPrice = 0; // 카카오 알림톡 float faxPrice = 0; // 팩스 shortPrice = sysJoinSetVO.getShortPrice(); longPrice = sysJoinSetVO.getLongPrice(); picturePrice = sysJoinSetVO.getPicturePrice(); picturePrice2 = sysJoinSetVO.getPicture2Price(); picturePrice3 = sysJoinSetVO.getPicture3Price(); DecimalFormat df = new DecimalFormat("###,###"); customSamplePrice = df.format(sysJoinSetVO.getCustomSamplePrice()); customEditPrice = df.format(sysJoinSetVO.getCustomEditPrice()); customEdit3Price = df.format(sysJoinSetVO.getCustomEdit3Price()); customTextPrice = df.format(sysJoinSetVO.getCustomTextPrice()); kakaoAtPrice = sysJoinSetVO.getKakaoAtPrice(); faxPrice = sysJoinSetVO.getFaxPrice(); // 회원로그인시 협의된 단가 if (mberManageVO != null && (double)mberManageVO.getShortPrice() > 0) { shortPrice = mberManageVO.getShortPrice(); longPrice = mberManageVO.getLongPrice(); picturePrice = mberManageVO.getPicturePrice(); picturePrice2 = mberManageVO.getPicture2Price(); picturePrice3 = mberManageVO.getPicture3Price(); } if (mberManageVO != null && (double)mberManageVO.getKakaoAtPrice() > 0) { kakaoAtPrice = mberManageVO.getKakaoAtPrice(); } if (mberManageVO != null && (double)mberManageVO.getFaxPrice() > 0) { faxPrice = mberManageVO.getFaxPrice(); } model.addAttribute("shortPrice", shortPrice); model.addAttribute("longPrice", longPrice); model.addAttribute("picturePrice", picturePrice); model.addAttribute("picturePrice2", picturePrice2); model.addAttribute("picturePrice3", picturePrice3); model.addAttribute("customSamplePrice", customSamplePrice); model.addAttribute("customEditPrice", customEditPrice); model.addAttribute("customEdit3Price", customEdit3Price); model.addAttribute("customTextPrice", customTextPrice); model.addAttribute("kakaoAtPrice", kakaoAtPrice); model.addAttribute("faxPrice", faxPrice); model.addAttribute("SysJoinSetVO", sysJoinSetVO); // 탭이동 String tabType = "1"; if(request.getParameter("tabType") != null) { tabType = request.getParameter("tabType"); } model.addAttribute("tabType", tabType); MberGrdVO mberGrdVO = new MberGrdVO(); List<MberGrdVO> mberGrdSettingList = mberGrdService.selectMberGrdSettingList(mberGrdVO); model.addAttribute("mberGrdSettingList", mberGrdSettingList); return "/web/pay/PayGuide"; } /** * 요금안내/견적내기 * @param searchVO * @param model * @return "web/cop/bbs/NoticeList.do" * @throws Exception */ @RequestMapping(value= {"/web/pay/PayEstimateAjax.do"}) public String PayEstimateAjax(@ModelAttribute("searchVO") MjonPayVO mjonPayVO, @RequestParam(value = "selected", defaultValue="a") String selected, HttpServletRequest request, ModelMap model) throws Exception{ //개별문자 가격 및 총 금액 셋팅해주기 - 시스템 기본 단가기준으로 입력해줌 //1.시스템 기본 단가 정보 불러오기 JoinSettingVO sysJoinSetVO = mjonMsgDataService.selectJoinSettingInfo(); float shortPrice = 0; // 단문(SMS) float longPrice = 0; // 장문(LMS) float picturePrice = 0; // 그림문자(MMS) 1장 float picturePrice2 = 0; // 그림문자(MMS) 2장 float picturePrice3 = 0; // 그림문자(MMS) 3장 shortPrice = sysJoinSetVO.getShortPrice(); longPrice = sysJoinSetVO.getLongPrice(); picturePrice = sysJoinSetVO.getPicturePrice(); picturePrice2 = sysJoinSetVO.getPicturePrice(); picturePrice3 = sysJoinSetVO.getPicturePrice(); model.addAttribute("shortPrice", shortPrice); model.addAttribute("longPrice", longPrice); model.addAttribute("picturePrice", picturePrice); model.addAttribute("picturePrice2", picturePrice2); model.addAttribute("picturePrice3", picturePrice3); model.addAttribute("SysJoinSetVO", sysJoinSetVO); // 파라미터 request.setCharacterEncoding("UTF-8"); String isVat = request.getParameter("isVat"); String title = request.getParameter("title"); // 제목 String recipientName = request.getParameter("recipientName"); // 수신자 String managerName = request.getParameter("managerName"); // 담당자(성명) String mobile = request.getParameter("mobile"); // 연락처 String publishDate = request.getParameter("publishDate"); // 발행일 DecimalFormat decFormat = new DecimalFormat("###,###"); /**** JSONArray 받는 방법 ****/ String jsonStr = request.getParameter("smsList"); // request로 문자열형식의 데이터를 받고 JSONArray jsonArr = new JSONArray(); JSONParser parser = new JSONParser(); jsonArr = (JSONArray)parser.parse(jsonStr); // 문자열 형식의 데이터를 JSONArray로 가공 // 데이터의 길이만큼 반복 및 JSONObject로 변환하며 확인 for(int i=0;i<jsonArr.size();i++) { JSONObject jsonObj = (JSONObject)jsonArr.get(i); double vatPrice = 0.0; double supplyPrice = 0.0; double totPrice = 0.0; if (isVat.equals("1")) { // 부가세 포함 vatPrice = (Double.parseDouble(jsonObj.get("supplyPrice").toString()) * 0.1); supplyPrice = Double.parseDouble(jsonObj.get("supplyPrice").toString()); totPrice = (Double.parseDouble(jsonObj.get("totPrice").toString()) + vatPrice); jsonObj.put("vatPrice", decFormat.format(vatPrice)); jsonObj.put("supplyPrice", decFormat.format(supplyPrice)); jsonObj.put("totPrice", decFormat.format(totPrice)); } else { // 부가세 별도(부가세 : 0) vatPrice = 0; supplyPrice = (Double.parseDouble(jsonObj.get("supplyPrice").toString()) - vatPrice); totPrice = Double.parseDouble(jsonObj.get("totPrice").toString()); jsonObj.put("vatPrice", decFormat.format(vatPrice)); jsonObj.put("supplyPrice", decFormat.format(supplyPrice)); jsonObj.put("totPrice", decFormat.format(totPrice)); } } String supplySumPrice = request.getParameter("supplySumPrice"); // 공급가액 String vatSumPrice = request.getParameter("vatSumPrice"); // 부가세액 String totSumPrice = request.getParameter("totSumPrice"); // 합계 String isVatStr = ""; if (isVat.equals("1")) { isVatStr = "VAT 포함"; } else { isVatStr = "VAT 별도"; } model.addAttribute("smsList", jsonArr); model.addAttribute("isVat", isVat); model.addAttribute("isVatStr", isVatStr); model.addAttribute("title", title); model.addAttribute("recipientName", recipientName); model.addAttribute("managerName", managerName); model.addAttribute("mobile", mobile); model.addAttribute("publishDate", publishDate); model.addAttribute("smsList", jsonArr); model.addAttribute("supplySumPrice", decFormat.format(Double.parseDouble(supplySumPrice))); model.addAttribute("vatSumPrice", decFormat.format(Double.parseDouble(vatSumPrice))); model.addAttribute("totSumPrice", decFormat.format(Double.parseDouble(totSumPrice))); return "/web/pay/PayEstimate"; } /** * 결제하기 * @param searchVO * @param model * @return "/web/pay/PayView.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/PayView_OLD.do"}) public String PayView(@ModelAttribute("mjonPayVO") MjonPayVO mjonPayVO, HttpServletRequest request, RedirectAttributes redirectAttributes, ModelMap model) throws Exception{ String pattern = (String) request.getAttribute( HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; //로그인 정보 획득 // LoginVO loginVO = (LoginVO) EgovUserDetailsHelper.getAuthenticatedUser(); LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); if(loginVO == null) { //redirectAttributes.addFlashAttribute("message", "문자온 서비스는 로그인 후 이용 가능합니다."); return "redirect:/web/user/login/login.do"; } MberManageVO mberManageVO = mberManageService.selectMber(loginVO.getId()); model.addAttribute("mberManageVO", mberManageVO); MjonCandidateVO mjonCandidateVO = new MjonCandidateVO(); if(userId != "") { mjonCandidateVO = mjonCandidateService.selectCandidateDataInfo(userId); model.addAttribute("mjonCandidateVO",mjonCandidateVO); } mjonPayVO.setReturnURL(request.getRequestURL().toString().split("pay")[0] + "nicepay/payResultAjax.do") ; /** pageing */ PaginationInfo paginationInfo = new PaginationInfo(); paginationInfo.setCurrentPageNo(mjonPayVO.getPageIndex()); paginationInfo.setRecordCountPerPage(mjonPayVO.getPageUnit()); paginationInfo.setPageSize(mjonPayVO.getPageSize()); mjonPayVO.setFirstIndex(paginationInfo.getFirstRecordIndex()); mjonPayVO.setLastIndex(paginationInfo.getLastRecordIndex()); mjonPayVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage()); if("".equals(mjonPayVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonPayVO.setSearchSortCnd("moid"); mjonPayVO.setSearchSortOrd("desc"); } mjonPayVO.setUserId(loginVO.getId()); /*List<MjonPayVO> resultList = mjonPayService.selectPayList(mjonPayVO);*/ /*model.addAttribute("resultList", resultList); paginationInfo.setTotalRecordCount(resultList.size() > 0 ? ((MjonPayVO)resultList.get(0)).getTotCnt() : 0);*/ /*model.addAttribute("paginationInfo", paginationInfo);*/ //대상 리스트 가져오기 VacsVactVO vacsVactVO = new VacsVactVO(); vacsVactVO.setMberId(loginVO.getId()); //List<?> bankList = vacsVactService.selectBankAcctList(tempVO); //할당 가능한 계좌 수량 리스트 받아오기 List<?> bankList = vacsVactService.selectBankAcctAllList(vacsVactVO); //할당 가능한 계좌 수량 리스트 받아오기 List<?> myBankList = vacsVactService.selectMyBankAcctList(vacsVactVO); //내가 할당받은 전용계좌 리스트 받아오기 MjonVaMsgLogVO vaMsgLogVO = new MjonVaMsgLogVO(); vaMsgLogVO.setMberId(loginVO.getId()); Date now = new Date(); SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd"); String date = sdf.format(now); vaMsgLogVO.setSendDate(date); MjonVaMsgLogVO resultMsgInfo = mjonPayService.selectMyAcctSendCnt(vaMsgLogVO); //대상 리스트, 페이징 정보 전달 model.addAttribute("resultList", bankList); model.addAttribute("resultMyList", myBankList); model.addAttribute("resultMsgInfo", resultMsgInfo); model.addAttribute("mjonPayVO", mjonPayVO); // 탭이동 String tabType = "1"; if(request.getParameter("tabType") != null) { tabType = request.getParameter("tabType"); } model.addAttribute("tabType", tabType); MberGrdVO mberGrdVO = new MberGrdVO(); List<MberGrdVO> mberGrdSettingList = mberGrdService.selectMberGrdSettingList(mberGrdVO); model.addAttribute("mberGrdSettingList", mberGrdSettingList); return "/web/pay/PayView_OLD"; } /** * 요금결제내역 * @param searchVO * @param model * @return "/web/member/pay/PayList.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/PayList.do" , "/web/member/pay/PayListAllAjax.do", "/web/member/pay/PayListMobileAjax.do", "/web/member/pay/PayListCardAjax.do", "/web/member/pay/PayListVBankAjax.do", "/web/member/pay/PayListBankAjax.do", "/web/member/pay/PayListSPayAjax.do", "/web/member/pay/PayListOfflineAjax.do", "/web/member/pay/PayListRefundAjax.do" }) public String PayList(@ModelAttribute("searchVO") MjonPayVO mjonPayVO, HttpServletRequest request, RedirectAttributes redirectAttributes, ModelMap model) throws Exception{ String pattern = (String) request.getAttribute(HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; //세금계산서, 현금영수증 정보 조회 /*LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser();*/ LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); if(loginVO == null) { //redirectAttributes.addFlashAttribute("message", "문자온 서비스는 로그인 후 이용 가능합니다."); return "redirect:/web/user/login/login.do"; } MberManageVO mberManageVO = mberManageService.selectMber(loginVO.getId()); //개인주민번호 있을경우만 불러오기 수정(2022.0415) if(mberManageVO.getIhidnum() != null) { mberManageVO.setIhidnum(egovCryptoUtil.decrypt(mberManageVO.getIhidnum())); } model.addAttribute("mberManageVO", mberManageVO); //선거 후보자 정보 불러오기 MjonCandidateVO mjonCandidateVO = new MjonCandidateVO(); if(userId != ""){ mjonCandidateVO = mjonCandidateService.selectCandidateDataInfo(userId); //String cryptText = egovCryptoUtil.encrypt(plainText); if(mjonCandidateVO != null) { // 주민번호 복호화 하기 String regidentNo1 = egovCryptoUtil.decrypt(mjonCandidateVO.getRegidentNo1()); String regidentNo2 = egovCryptoUtil.decrypt(mjonCandidateVO.getRegidentNo2()); mjonCandidateVO.setRegidentNo1(regidentNo1); mjonCandidateVO.setRegidentNo2(regidentNo2); } } model.addAttribute("mjonCandidateVO", mjonCandidateVO); /* if(pattern.equals("/web/member/pay/PayListAllAjax.do")) { // 등급제 Start // 회원별 등급 적용 MberGrdVO mberGrdVO = new MberGrdVO(); mberGrdVO.setMberId(userId); mberGrdVO.setAmt("0"); mberGrdVO.setMoid(""); mberGrdService.mberGrdSaveByUserNoHist(mberGrdVO); // End } */ //후불제 회원 여부 조회 UserManageVO userManageVO = new UserManageVO(); userManageVO.setMberId(userId); userManageVO = userManageService.selectAdminSmsNoticeYn(userManageVO); model.addAttribute("prePaymentYn", userManageVO.getPrePaymentYn()); System.out.println("pattern :: "+ pattern); if(pattern.equals("/web/member/pay/PayListAllAjax.do") || pattern.equals("/web/member/pay/PayListMobileAjax.do") || pattern.equals("/web/member/pay/PayListCardAjax.do") || pattern.equals("/web/member/pay/PayListVBankAjax.do") || pattern.equals("/web/member/pay/PayListBankAjax.do") || pattern.equals("/web/member/pay/PayListSPayAjax.do") || pattern.equals("/web/member/pay/PayListOfflineAjax.do") || pattern.equals("/web/member/pay/PayListRefundAjax.do") ) { //전체 , 모바일, 신용카드 //resultList = mjonMsgService.selectMjonMsgGroupDtList(searchVO); /** pageing */ PaginationInfo paginationInfo = new PaginationInfo(); paginationInfo.setCurrentPageNo(mjonPayVO.getPageIndex()); paginationInfo.setRecordCountPerPage(mjonPayVO.getPageUnit()); paginationInfo.setPageSize(mjonPayVO.getPageSize()); mjonPayVO.setFirstIndex(paginationInfo.getFirstRecordIndex()); mjonPayVO.setLastIndex(paginationInfo.getLastRecordIndex()); mjonPayVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage()); mjonPayVO.setUserId(userId); //횐불요청 페이지 처리 if(pattern.equals("/web/member/pay/PayListRefundAjax.do")) { RefundVO refundVO = new RefundVO(); //환불 요청 리스트 정보 셋팅하기 refundVO.setSearchSortCnd(mjonPayVO.getSearchSortCnd()); refundVO.setSearchSortOrd(mjonPayVO.getSearchSortOrd()); if("".equals(refundVO.getSearchSortCnd())) { refundVO.setSearchSortCnd("refundId"); refundVO.setSearchSortOrd("desc"); }else { String sortCnd = refundVO.getSearchSortCnd(); if(sortCnd.equals("moid") || sortCnd.equals("pointUseId") || sortCnd.equals("type")) {//결제아이디, 포인트 아이디, 포인트 교환 방식 refundVO.setSearchSortCnd("refundId"); }else if(sortCnd.equals("regDate") || sortCnd.equals("frstRegistPnttm")) {//결제일자, refundVO.setSearchSortCnd("frstRegisterPnttm"); }else if(sortCnd.equals("amt") || sortCnd.equals("cash") || sortCnd.equals("point")) {//결제금액 refundVO.setSearchSortCnd("refundMoney"); }else if(sortCnd.equals("amt") || sortCnd.equals("cash") || sortCnd.equals("point")) {//결제금액 refundVO.setSearchSortCnd("refundMoney"); }else if(sortCnd.equals("cmpltYn") || sortCnd.equals("pgStatusTxt")) { refundVO.setSearchSortCnd("refundStatus"); } refundVO.setSearchSortOrd(mjonPayVO.getSearchSortOrd()); } refundVO.setFirstIndex(paginationInfo.getFirstRecordIndex()); refundVO.setLastIndex(paginationInfo.getLastRecordIndex()); refundVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage()); //회원 ID, 이름, 비밀번호, 캐쉬조회 refundVO.setMberId(loginVO.getId()); RefundVO resultRefundVO = refundService.selectRefundMberInfo(refundVO); String refundListType = mjonPayVO.getRefundListType(); if(refundListType == null || refundListType.equals("")) { mjonPayVO.setRefundListType("tab1"); } model.addAttribute("resultRefundVO", resultRefundVO); { // 초기 날짜 셋팅 model.addAttribute("startDate", DateUtils.getDateMonthsAgo(13)); model.addAttribute("endDate", DateUtils.getCurrentDate()); // model.addAttribute("startDate", DateUtils.getDateDaysAgo(365)); // model.addAttribute("endDate", DateUtils.getCurrentDate()); } //환불 요청 리스트 조회하기 List<RefundVO> refundList = refundService.selectRefundList(refundVO); int totCnt = 0; if(refundList.size() > 0) { totCnt = refundList.get(0).getTotCnt(); } paginationInfo.setTotalRecordCount(refundList.size() > 0 ? ((RefundVO)refundList.get(0)).getTotCnt() : 0); model.addAttribute("paginationInfo", paginationInfo); model.addAttribute("refundList", refundList); model.addAttribute("refundVO", refundVO); return "/web/pay/PayListRefundAjax"; } //일반 결제 페이지 처리 if("".equals(mjonPayVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonPayVO.setSearchSortCnd("moid"); mjonPayVO.setSearchSortOrd("desc"); }else {//포인트 교환내역에서 정렬 종류가 달라서 변환처리 해줌 String sortCnt = mjonPayVO.getSearchSortCnd(); if(sortCnt.equals("pointUseId") || sortCnt.equals("refundId")) { mjonPayVO.setSearchSortCnd("moid"); }else if(sortCnt.equals("frstRegistPnttm") || sortCnt.equals("frstRegisterPnttm") || sortCnt.equals("refundHandlePnttm")) { mjonPayVO.setSearchSortCnd("regDate"); }else if(sortCnt.equals("type")) { mjonPayVO.setSearchSortCnd("payMethodTxt"); }else if(sortCnt.equals("point") || sortCnt.equals("refundMoney") || sortCnt.equals("refundCash")) { mjonPayVO.setSearchSortCnd("amt"); }else if(sortCnt.equals("cmpltYn") || sortCnt.equals("refundStatus")) { mjonPayVO.setSearchSortCnd("pgStatusTxt"); } } if(pattern.equals("/web/member/pay/PayListAllAjax.do")) { //전체 mjonPayVO.setPageType("all"); } if(pattern.equals("/web/member/pay/PayListMobileAjax.do")) { //모바일일때 mjonPayVO.setSearchCondition2("CELLPHONE"); mjonPayVO.setPayMethod("CELLPHONE"); mjonPayVO.setPageType("cellphone"); } if(pattern.equals("/web/member/pay/PayListCardAjax.do")) { //신용카드 mjonPayVO.setSearchCondition2("CARD"); mjonPayVO.setPayMethod("CARD"); mjonPayVO.setPageType("card"); } if(pattern.equals("/web/member/pay/PayListVBankAjax.do")) { //전용계좌 mjonPayVO.setSearchCondition2("VBANK"); mjonPayVO.setPayMethod("VBANK"); mjonPayVO.setPageType("vbank"); } if(pattern.equals("/web/member/pay/PayListBankAjax.do")) { //즉시이체 mjonPayVO.setSearchCondition2("BANK"); mjonPayVO.setPayMethod("BANK"); mjonPayVO.setPageType("bank"); } if(pattern.equals("/web/member/pay/PayListSPayAjax.do")) { //즉시이체 mjonPayVO.setSearchCondition2("SPAY"); mjonPayVO.setPayMethod("SPAY"); mjonPayVO.setPageType("SPAY"); } if(pattern.equals("/web/member/pay/PayListOfflineAjax.do")) { //무통장 mjonPayVO.setSearchCondition2("OFFLINE"); mjonPayVO.setPayMethod("OFFLINE"); mjonPayVO.setPageType("offline"); } /*LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonPayVO.setUserId(userId);*/ // 이메일 코드조회 ComDefaultCodeVO voComCode = new ComDefaultCodeVO(); voComCode.setCodeId("ITN031"); model.addAttribute("emailCode", cmmUseService.selectCmmCodeDetail(voComCode)); // mjonPayVO.setStartDate(mjonPayVO.getStartDate() == null ? DateUtil.getDateDaysAgo(365) : mjonPayVO.getStartDate()); // mjonPayVO.setEndDate(mjonPayVO.getEndDate() == null ? DateUtil.getCurrentDate() : mjonPayVO.getEndDate()); if(!DateUtils.dateChkAndValueChk(mjonPayVO.getStartDate(),mjonPayVO.getEndDate(), 12 )) { mjonPayVO.setStartDate(DateUtils.getDateMonthsAgo(12)); mjonPayVO.setEndDate(DateUtils.getCurrentDate()); }; //결제 리스트 정보 불러오기 List<MjonPayVO> resultList = mjonPayService.selectPayList(mjonPayVO); model.addAttribute("totChgPay", resultList.size() > 0 ? PayUtils.getTrimToFirstDecimal(resultList.get(0).getTotChgPay()) : 0); model.addAttribute("resultList", resultList); paginationInfo.setTotalRecordCount(resultList.size() > 0 ? ((MjonPayVO)resultList.get(0)).getTotCnt() : 0); model.addAttribute("paginationInfo", paginationInfo); return "/web/pay/PayListAllAjax"; } return "/web/pay/PayList"; } /** * 포인트 전환내역 * @param searchVO * @param model * @return "/web/member/pay/PayList.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/PayListPointAjax.do"}) public String PoinUseList(@ModelAttribute("searchVO") MjonPayVO mjonPayVO, HttpServletRequest request, RedirectAttributes redirectAttributes, ModelMap model) throws Exception{ String pattern = (String) request.getAttribute(HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; //세금계산서, 현금영수증 정보 조회 /*LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser();*/ LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); if(loginVO == null) { //redirectAttributes.addFlashAttribute("message", "문자온 서비스는 로그인 후 이용 가능합니다."); return "redirect:/web/user/login/login.do"; } /** pageing */ PaginationInfo paginationInfo = new PaginationInfo(); paginationInfo.setCurrentPageNo(mjonPayVO.getPageIndex()); paginationInfo.setRecordCountPerPage(mjonPayVO.getPageUnit()); paginationInfo.setPageSize(mjonPayVO.getPageSize()); mjonPayVO.setFirstIndex(paginationInfo.getFirstRecordIndex()); mjonPayVO.setLastIndex(paginationInfo.getLastRecordIndex()); mjonPayVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage()); mjonPayVO.setType("1"); // 현금 mjonPayVO.setUserId(userId); if("".equals(mjonPayVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonPayVO.setSearchSortCnd("pointUseId"); mjonPayVO.setSearchSortOrd("desc"); }else {//포인트 교환내역에서 정렬 종류가 달라서 변환처리 해줌 String sortCnt = mjonPayVO.getSearchSortCnd(); if(sortCnt.equals("moid") || sortCnt.equals("refundId")) { mjonPayVO.setSearchSortCnd("pointUseId"); }else if(sortCnt.equals("regDate") || sortCnt.equals("refundHandlePnttm") || sortCnt.equals("frstRegisterPnttm")) { mjonPayVO.setSearchSortCnd("frstRegistPnttm"); }else if(sortCnt.equals("payMethodTxt")) { mjonPayVO.setSearchSortCnd("type"); }else if(sortCnt.equals("amt") || sortCnt.equals("cash") || sortCnt.equals("refundMoney") || sortCnt.equals("refundCash")) { mjonPayVO.setSearchSortCnd("point"); }else if(sortCnt.equals("pgStatusTxt") || sortCnt.equals("refundStatus")) { mjonPayVO.setSearchSortCnd("cmpltYn"); } } List<MjonPayVO> resultList = mjonPayService.selectPointUseList(mjonPayVO); model.addAttribute("resultList", resultList); paginationInfo.setTotalRecordCount(resultList.size() > 0 ? ((MjonPayVO)resultList.get(0)).getTotCnt() : 0); model.addAttribute("paginationInfo", paginationInfo); model.addAttribute("totChgPay", resultList.size() > 0 ? resultList.get(0).getTotChgPay() : 0); // String requestUri = request.getRequestURI(); // String fileName = requestUri.substring(requestUri.lastIndexOf('/') + 1); // // model.addAttribute("fileName", fileName); return "/web/pay/PayListPointAjax"; } @RequestMapping(value= {"/web/member/pay/PayPrintListPopUpAjax.do"}) public String printMsgSentDataAjax( @ModelAttribute("searchVO") MjonPayVO mjonPayVO , RedirectAttributes redirectAttributes , ModelMap model) throws Exception { //로그인 권한정보 불러오기 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); String userNm = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getName()); mjonPayVO.setUserId(userId); model.addAttribute("userNm", userNm); String pageType = mjonPayVO.getPageType(); if(pageType.equals("all")) { }else if(pageType.equals("vbank")) { mjonPayVO.setSearchCondition2("VBANK"); mjonPayVO.setPayMethod("VBANK"); }else if(pageType.equals("card")) { mjonPayVO.setSearchCondition2("CARD"); mjonPayVO.setPayMethod("CARD"); }else if(pageType.equals("cellphone")) { mjonPayVO.setSearchCondition2("CELLPHONE"); mjonPayVO.setPayMethod("CELLPHONE"); }else if(pageType.equals("bank")) { mjonPayVO.setSearchCondition2("BANK"); mjonPayVO.setPayMethod("BANK"); } mjonPayVO.setFirstIndex(0); mjonPayVO.setRecordCountPerPage(10000); //결제 리스트 정보 불러오기 List<MjonPayVO> resultList = mjonPayService.selectPayList(mjonPayVO); model.addAttribute("resultList", resultList); Calendar cal = Calendar.getInstance(); int year = cal.get(Calendar.YEAR); int month = cal.get(Calendar.MONTH) + 1; int day = cal.get(Calendar.DAY_OF_MONTH); model.addAttribute("year", year); model.addAttribute("month", month); model.addAttribute("day", day); return "web/pay/PayPrintListPopUp"; } @RequestMapping(value= {"/web/member/pay/PayPrintPointListPopUpAjax.do"}) public String payPrintPointPrintDataAjax( @ModelAttribute("searchVO") MjonPayVO mjonPayVO , RedirectAttributes redirectAttributes , ModelMap model) throws Exception { //로그인 권한정보 불러오기 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); String userNm = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getName()); mjonPayVO.setUserId(userId); model.addAttribute("userNm", userNm); mjonPayVO.setType("1"); // 현금 mjonPayVO.setFirstIndex(0); mjonPayVO.setRecordCountPerPage(10000); //결제 리스트 정보 불러오기 List<MjonPayVO> resultList = mjonPayService.selectPointUseList(mjonPayVO); model.addAttribute("resultList", resultList); Calendar cal = Calendar.getInstance(); int year = cal.get(Calendar.YEAR); int month = cal.get(Calendar.MONTH) + 1; int day = cal.get(Calendar.DAY_OF_MONTH); model.addAttribute("year", year); model.addAttribute("month", month); model.addAttribute("day", day); return "web/pay/PayPrintPointListPopUp"; } @RequestMapping(value= {"/web/member/pay/PayPrintRefundListPopUpAjax.do"}) public String payPrintRefundPrintDataAjax( @ModelAttribute("searchVO") MjonPayVO mjonPayVO , RedirectAttributes redirectAttributes , ModelMap model) throws Exception { //로그인 권한정보 불러오기 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); String userNm = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getName()); RefundVO refundVO = new RefundVO(); refundVO.setRecordCountPerPage(100000); refundVO.setFirstIndex(0); if("".equals(mjonPayVO.getSearchSortCnd())){ //최초조회시 최신것 조회List refundVO.setSearchSortCnd("refundId"); refundVO.setSearchSortOrd("desc"); }else { //환불 요청 리스트 정보 셋팅하기 refundVO.setSearchSortCnd(mjonPayVO.getSearchSortCnd()); refundVO.setSearchSortOrd(mjonPayVO.getSearchSortOrd()); } // JSPark 2022.03.16 => 뷰 목록과 데이터 상이 refundVO.setMberId(userId); model.addAttribute("userNm", userNm); String refundListType = mjonPayVO.getRefundListType(); if(refundListType == null || refundListType.equals("")) { mjonPayVO.setRefundListType("tab1"); } refundVO.setStartDate(mjonPayVO.getStartDate()); refundVO.setEndDate(mjonPayVO.getEndDate()); //결제 리스트 정보 불러오기 List<RefundVO> resultList = refundService.selectRefundList(refundVO); model.addAttribute("resultList", resultList); Calendar cal = Calendar.getInstance(); int year = cal.get(Calendar.YEAR); int month = cal.get(Calendar.MONTH) + 1; int day = cal.get(Calendar.DAY_OF_MONTH); model.addAttribute("year", year); model.addAttribute("month", month); model.addAttribute("day", day); return "web/pay/PayPrintRefundListPopUp"; } @RequestMapping(value= {"/web/member/pay/PayPdfListPopUpAjax.do"}) public String payPrintPdfDataAjax( @ModelAttribute("searchVO") MjonMsgVO mjonMsgVO , RedirectAttributes redirectAttributes , ModelMap model) throws Exception { //로그인 권한정보 불러오기 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); String userNm = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getName()); mjonMsgVO.setUserId(userId); model.addAttribute("userNm", userNm); mjonMsgVO.setFirstIndex(0); mjonMsgVO.setRecordCountPerPage(10000); //결제 리스트 정보 불러오기 List<MjonMsgVO> payUserList = mjonMsgDataService.selectPayUserList(mjonMsgVO); model.addAttribute("payUserList", payUserList); Calendar cal = Calendar.getInstance(); int year = cal.get(Calendar.YEAR); int month = cal.get(Calendar.MONTH) + 1; int day = cal.get(Calendar.DAY_OF_MONTH); model.addAttribute("year", year); model.addAttribute("month", month); model.addAttribute("day", day); return "web/pay/PrintPayUserListPdfPopUp"; } //사용내역 pdf저장 카카오 추가 @RequestMapping(value= {"/web/member/pay/PayPdfWithKakaoListPopUpAjax.do"}) public String PayPdfWithKakaoListPopUpAjax( @ModelAttribute("searchVO") MjonMsgVO mjonMsgVO , RedirectAttributes redirectAttributes , ModelMap model) throws Exception { //로그인 권한정보 불러오기 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); String userNm = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getName()); mjonMsgVO.setUserId(userId); model.addAttribute("userNm", userNm); mjonMsgVO.setFirstIndex(0); mjonMsgVO.setRecordCountPerPage(10000); //결제 리스트 정보 불러오기 List<MjonMsgVO> payUserList = mjonMsgDataService.selectPayUserWithKakaoList(mjonMsgVO); model.addAttribute("payUserList", payUserList); Calendar cal = Calendar.getInstance(); int year = cal.get(Calendar.YEAR); int month = cal.get(Calendar.MONTH) + 1; int day = cal.get(Calendar.DAY_OF_MONTH); model.addAttribute("year", year); model.addAttribute("month", month); model.addAttribute("day", day); return "web/pay/PrintPayUserWithKakaoListPdfPopUp"; } /** * 요금사용내역 * @param mjonMsgVO * @param model * @return "/web/member/pay/PayUserList.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/PayUserList.do"}) public String PayUserList(@ModelAttribute("mjonMsgVO") MjonMsgVO mjonMsgVO, MjonPayVO mjonPayVO, MberManageVO mberManageVO, RedirectAttributes redirectAttributes, HttpServletRequest request, ModelMap model) throws Exception{ /*LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser();*/ LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; if(loginVO == null) { //redirectAttributes.addFlashAttribute("message", "문자온 서비스는 로그인 후 이용 가능합니다."); return "redirect:/web/user/login/login.do"; } String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgVO.setUserId(userId); { // 충전캐시 String sumPayMoney = mjonPayService.selectAgentSumPayMoney(userId); model.addAttribute("sumPayMoney", sumPayMoney); } { // 충전포인트 String sumPoint = mjonPayService.selectSumPoint(userId); model.addAttribute("sumPoint", sumPoint); } { // 총 사용금액 mjonPayVO.setUserId(userId); List<MjonPayVO> usedCashTotList = mjonPayService.selectUsedCashWithKakaoTotCnt(mjonPayVO); model.addAttribute("usedCashTotList", usedCashTotList); } { // 사용가능한 금액 mberManageVO = mjonMsgDataService.selectMberManageInfo(userId); model.addAttribute("mberManageVO", mberManageVO); } { // 발송건수 mjonMsgVO.setReserveYn("N"); List<MjonMsgVO> sendTotList = mjonMsgService.selectWithKakaoSendTotCnt(mjonMsgVO); model.addAttribute("sendTotList", sendTotList); } { // 예약건수 mjonMsgVO.setReserveYn("Y"); List<MjonMsgVO> reservToList = mjonMsgService.selectWithKakaoSendTotCnt(mjonMsgVO); model.addAttribute("reservToList", reservToList); } { MjonCandidateVO mjonCandidateVO = new MjonCandidateVO(); if("p".equals(mberManageVO.getDept())) { //개인회원 선거 후보자 정보 불러오기 mjonCandidateVO = mjonCandidateService.selectCandidateDataInfo(userId); if(mjonCandidateVO != null) { mjonCandidateVO.setRegidentNo1(egovCryptoUtil.decrypt(mjonCandidateVO.getRegidentNo1())); mjonCandidateVO.setRegidentNo2(egovCryptoUtil.decrypt(mjonCandidateVO.getRegidentNo2())); } } model.addAttribute("mjonCandidateVO", mjonCandidateVO); } return "/web/pay/PayUserList"; } /** * 요금사용내역 카카오 테스트용 * @param mjonMsgVO * @param model * @return "/web/member/pay/PayUserWithKakaoList.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/PayUserWithKakaoList.do"}) public String PayUserWithKakaoList(@ModelAttribute("mjonMsgVO") MjonMsgVO mjonMsgVO, MjonPayVO mjonPayVO, MberManageVO mberManageVO, RedirectAttributes redirectAttributes, HttpServletRequest request, ModelMap model) throws Exception{ /*LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser();*/ LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; if(loginVO == null) { //redirectAttributes.addFlashAttribute("message", "문자온 서비스는 로그인 후 이용 가능합니다."); return "redirect:/web/user/login/login.do"; } String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgVO.setUserId(userId); { // 충전캐시 String sumPayMoney = mjonPayService.selectAgentSumPayMoney(userId); model.addAttribute("sumPayMoney", sumPayMoney); } { // 충전포인트 String sumPoint = mjonPayService.selectSumPoint(userId); model.addAttribute("sumPoint", sumPoint); } { // 총 사용금액 mjonPayVO.setUserId(userId); List<MjonPayVO> usedCashTotList = mjonPayService.selectUsedCashWithKakaoTotCnt(mjonPayVO); model.addAttribute("usedCashTotList", usedCashTotList); } { // 사용가능한 금액 mberManageVO = mjonMsgDataService.selectMberManageInfo(userId); model.addAttribute("mberManageVO", mberManageVO); } { // 발송건수 mjonMsgVO.setReserveYn("N"); List<MjonMsgVO> sendTotList = mjonMsgService.selectWithKakaoSendTotCnt(mjonMsgVO); model.addAttribute("sendTotList", sendTotList); } { // 예약건수 mjonMsgVO.setReserveYn("Y"); List<MjonMsgVO> reservToList = mjonMsgService.selectWithKakaoSendTotCnt(mjonMsgVO); model.addAttribute("reservToList", reservToList); } return "/web/pay/PayUserWithKakaoList"; } /** * 요금사용내역 하단 리스트 영역 * @param mjonMsgVO * @param model * @return "/web/member/pay/PayUserListAjax.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/PayUserListAjax.do"}) public String PayUserListAjax(@ModelAttribute("searchVO") MjonMsgVO mjonMsgVO, HttpServletRequest request, ModelMap model) throws Exception{ LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgVO.setUserId(userId); if(mjonMsgVO.getPageUnit() != 10) { mjonMsgVO.setPageUnit(mjonMsgVO.getPageUnit()); } /** pageing */ PaginationInfo paginationInfo = new PaginationInfo(); paginationInfo.setCurrentPageNo(mjonMsgVO.getPageIndex()); paginationInfo.setRecordCountPerPage(mjonMsgVO.getPageUnit()); paginationInfo.setPageSize(mjonMsgVO.getPageSize()); mjonMsgVO.setFirstIndex(paginationInfo.getFirstRecordIndex()); mjonMsgVO.setLastIndex(paginationInfo.getLastRecordIndex()); mjonMsgVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage()); if("".equals(mjonMsgVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonMsgVO.setSearchSortCnd("regDate"); mjonMsgVO.setSearchSortOrd("desc"); } List<MjonMsgVO> payUserList = mjonMsgDataService.selectPayUserList(mjonMsgVO); paginationInfo.setTotalRecordCount(payUserList.size()> 0 ? payUserList.get(0).getTotCnt() : 0); model.addAttribute("paginationInfo", paginationInfo); model.addAttribute("payUserList", payUserList); return "/web/pay/PayUserListAjax"; } /** * 요금사용내역 신규 화면 * 2023.12.22 우영두 * @param mjonMsgVO * @param model * @return "/web/member/pay/PayUserSWList.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/PayUserSWList.do"}) public String PayUserSWList(@ModelAttribute("mjonMsgVO") MjonMsgVO mjonMsgVO, MjonPayVO mjonPayVO, MberManageVO mberManageVO, RedirectAttributes redirectAttributes, HttpServletRequest request, ModelMap model) throws Exception{ /*LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser();*/ LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; if(loginVO == null) { //redirectAttributes.addFlashAttribute("message", "문자온 서비스는 로그인 후 이용 가능합니다."); return "redirect:/web/user/login/login.do"; } String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgVO.setUserId(userId); /* { // 충전캐시 String sumPayMoney = mjonPayService.selectAgentSumPayMoney(userId); model.addAttribute("sumPayMoney", sumPayMoney); } { // 충전포인트 String sumPoint = mjonPayService.selectSumPoint(userId); model.addAttribute("sumPoint", sumPoint); } */ { // 총 사용금액 mjonPayVO.setUserId(userId); List<MjonPayVO> usedCashTotList = mjonPayService.selectUsedCashWithKakaoTotCnt(mjonPayVO); model.addAttribute("usedCashTotList", usedCashTotList); } { // 사용가능한 금액 // mberManageVO.prePaymentYn 선불 후불 회원 구분으로 인해 select함 mberManageVO = mjonMsgDataService.selectMberManageInfo(userId); model.addAttribute("mberManageVO", mberManageVO); } /* { // 발송건수 mjonMsgVO.setReserveYn("N"); List<MjonMsgVO> sendTotList = mjonMsgService.selectWithKakaoSendTotCnt(mjonMsgVO); model.addAttribute("sendTotList", sendTotList); } { // 예약건수 mjonMsgVO.setReserveYn("Y"); List<MjonMsgVO> reservToList = mjonMsgService.selectWithKakaoSendTotCnt(mjonMsgVO); model.addAttribute("reservToList", reservToList); } */ {// 선거 후보자 정보 조회 MjonCandidateVO mjonCandidateVO = new MjonCandidateVO(); if("p".equals(mberManageVO.getDept())) { //개인회원 선거 후보자 정보 불러오기 mjonCandidateVO = mjonCandidateService.selectCandidateDataInfo(userId); if(mjonCandidateVO != null) { mjonCandidateVO.setRegidentNo1(egovCryptoUtil.decrypt(mjonCandidateVO.getRegidentNo1())); mjonCandidateVO.setRegidentNo2(egovCryptoUtil.decrypt(mjonCandidateVO.getRegidentNo2())); } } model.addAttribute("mjonCandidateVO", mjonCandidateVO); } { /** * 후불제 회원 관련 사용 요금 내역 정보 조회하기 * 20231228 우영두 추가 * 누적사용금액, 누적 납부금액, 당월 납부 예상금액 정보 조회 * * */ //누적 사용금액 조회 (캐시 테이블에서 현재까지 사용된 금액 합산 - 캐시 테이블에서 "-"로 사용된 금액만 합산) String totSumCashAfterPay = mjonPayService.selectTotalSumCashForAfterPay(userId); model.addAttribute("totSumCashAfterPay", totSumCashAfterPay); //누적 사용 포인트 조회 (포인트 테이블에서 현재까지 사용된 포인트 합산 - 회원 포인트 정보 업데이트시 사용되는 쿼리 이용) String totSumPointAfterPay = mjonPayService.selectTotalSumPointForAfterPay(userId); model.addAttribute("totSumPointAfterPay", totSumPointAfterPay); //누적 납부금액 조회 (후불회원이 사용금액을 실 납부한 금액 합산 - 미납 금액 제외한 총 합산 금액) String totSumPaymentAfterPay = mjonPayService.selectTotSumPaymentAfterPay(userId); model.addAttribute("totSumPaymentAfterPay", totSumPaymentAfterPay); //누적 납부 포인트 조회 ( 누적 납부금액 합산에 대한 2% 포인트 정보 계산) float p_i_re_point = 0; JoinSettingVO sysJoinSetVO = mjonMsgDataService.selectJoinSettingInfo(); if (sysJoinSetVO != null) { p_i_re_point = sysJoinSetVO.getPointPer(); } int paymentPointAfterPay = Math.round((Float.parseFloat(totSumPaymentAfterPay) * p_i_re_point / 100)); model.addAttribute("sumPaymentPointAfterPay", paymentPointAfterPay); //당월 납부 예상금액 (누적 사용금액 합산 - 누적 납부금액 합산 정보 계산) float unPaymentAfterPay = Float.parseFloat(totSumCashAfterPay) - Float.parseFloat(totSumPaymentAfterPay); model.addAttribute("unPaymentAfterPay", unPaymentAfterPay); //당월 납부 예상 포인트 (당월 납부 예상 금액에 대한 2% 포인트 정보 계산) int unPaymentPointAfterPay = Math.round((unPaymentAfterPay * p_i_re_point / 100)); model.addAttribute("unPaymentPointAfterPay", unPaymentPointAfterPay); /* * 2024.02.06 후불제 회원 중 협의단가를 사용하는 회원은 포인트 적립이 되지 않아야함. * 후불제 협의 단가 여부 체크 * 모든 협의 단가 금액을 합산하여 0보다 크면 협의단가 있음. * */ float shortPrice = mberManageVO.getShortPrice(); float longPrice = mberManageVO.getLongPrice(); float picturePrice = mberManageVO.getPicturePrice() + mberManageVO.getPicture2Price() + mberManageVO.getPicture3Price(); float kakaoAtPrice = mberManageVO.getKakaoAtPrice(); float kakaoFtPrice = mberManageVO.getKakaoFtPrice(); float totPrice = shortPrice + longPrice + picturePrice + kakaoAtPrice + kakaoFtPrice; String negoPriceYn = "N"; if(totPrice > 0) { negoPriceYn = "Y"; } model.addAttribute("negoPriceYn", negoPriceYn); } { // 초기 날짜 셋팅 model.addAttribute("startDate", DateUtils.getDateMonthsAgo(12)); model.addAttribute("endDate", DateUtils.getCurrentDate()); } return "/web/pay/PayUserSWList"; } /** * 요금사용내역 하단 리스트 영역 신규 화면 * 2023.12.22 우영두 * @param mjonMsgVO * @param model * @return "/web/member/pay/PayUserSWListAjax.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/PayUserSWListAjax.do"}) public String PayUserSWListAjax(@ModelAttribute("searchVO") MjonMsgVO mjonMsgVO, HttpServletRequest request, ModelMap model) throws Exception{ LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgVO.setUserId(userId); try { if(mjonMsgVO.getPageUnit() != 10) { mjonMsgVO.setPageUnit(mjonMsgVO.getPageUnit()); } /** pageing */ PaginationInfo paginationInfo = new PaginationInfo(); paginationInfo.setCurrentPageNo(mjonMsgVO.getPageIndex()); paginationInfo.setRecordCountPerPage(mjonMsgVO.getPageUnit()); paginationInfo.setPageSize(mjonMsgVO.getPageSize()); mjonMsgVO.setFirstIndex(paginationInfo.getFirstRecordIndex()); mjonMsgVO.setLastIndex(paginationInfo.getLastRecordIndex()); mjonMsgVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage()); if("".equals(mjonMsgVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonMsgVO.setSearchSortCnd("regDate"); mjonMsgVO.setSearchSortOrd("desc"); } // 검색 데이터가 없거나 // 시작일자가 종료일자보다 이후이거나 // 총 기간이 365일이 넘으면 현재일부터 365일 전 날짜를 넣어서 검색 if(!DateUtils.dateChkAndValueChk(mjonMsgVO.getStartDate(),mjonMsgVO.getEndDate(), 12 )) { mjonMsgVO.setStartDate(DateUtils.getDateMonthsAgo(12)); mjonMsgVO.setEndDate(DateUtils.getCurrentDate()); }; List<MjonMsgVO> payUserSWList = mjonMsgDataService.selectPayUserSWList(mjonMsgVO); paginationInfo.setTotalRecordCount(payUserSWList.size()> 0 ? payUserSWList.get(0).getTotCnt() : 0); model.addAttribute("paginationInfo", paginationInfo); model.addAttribute("payUserSWList", payUserSWList); model.addAttribute("totSuccSendPrice", payUserSWList.size() > 0 ? PayUtils.getTrimToFirstDecimal(payUserSWList.get(0).getTotSuccSendPrice()) : 0); model.addAttribute("mjonMsgVO", mjonMsgVO); } catch (Exception e) { e.printStackTrace(); System.out.println("PayUserSWListAjax Controller Error!!! " + e); } return "/web/pay/PayUserSWListAjax"; } @RequestMapping(value= {"/web/member/pay/PayPdfSWListPopUpAjax.do"}) public String payPrintPdfSWDataAjax( @ModelAttribute("searchVO") MjonMsgVO mjonMsgVO , RedirectAttributes redirectAttributes , ModelMap model) throws Exception { //로그인 권한정보 불러오기 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); String userNm = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getName()); mjonMsgVO.setUserId(userId); model.addAttribute("userNm", userNm); mjonMsgVO.setFirstIndex(0); mjonMsgVO.setRecordCountPerPage(10000); //결제 리스트 정보 불러오기 List<MjonMsgVO> payUserList = mjonMsgDataService.selectPayUserSWList(mjonMsgVO); model.addAttribute("payUserList", payUserList); Calendar cal = Calendar.getInstance(); int year = cal.get(Calendar.YEAR); int month = cal.get(Calendar.MONTH) + 1; int day = cal.get(Calendar.DAY_OF_MONTH); model.addAttribute("year", year); model.addAttribute("month", month); model.addAttribute("day", day); return "web/pay/PrintPayUserSWListPdfPopUp"; } /** * 요금 사용내역 엑셀 다운 * @param mjonMsgVO * @param model * @return "/web/member/pay/PayUserListAjax.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/PayUserSWListExcelDownload.do"}) public void PayUserSWListExcelDownload(@ModelAttribute("searchVO") MjonMsgVO mjonMsgVO, HttpServletRequest request, HttpServletResponse response , ModelMap model) throws Exception{ // 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다. SXSSFWorkbook wb = new SXSSFWorkbook(100); String fileName ="사용내역 엑셀 리스트"; // 저장 파일명 String sheetTitle = "요금사용내역" ; // 셀 제목 Sheet sheet = wb.createSheet(sheetTitle); Cell cell = null; Row row = null; CellStyle style = wb.createCellStyle(); style.setBorderBottom(CellStyle.BORDER_THIN); //테두리 두껍게 style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderTop(CellStyle.BORDER_THIN); // 정렬 style.setAlignment(CellStyle.ALIGN_CENTER); //가운데 정렬 style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //높이 가운데 정렬 Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); //글씨 bold sheet.setColumnWidth(1, 5000); // 칼럼의 폭 조절 sheet.setColumnWidth(4, 10000); // 칼럼의 폭 조절 try{ LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgVO.setUserId(userId); mjonMsgVO.setRecordCountPerPage(100000); mjonMsgVO.setFirstIndex(0); if("".equals(mjonMsgVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonMsgVO.setSearchSortCnd("regDate"); mjonMsgVO.setSearchSortOrd("desc"); } List<MjonMsgVO> payUserList = mjonMsgDataService.selectPayUserSWList(mjonMsgVO); {//화면 리스트 row = sheet.createRow(0); sheet.addMergedRegion(new CellRangeAddress(0,1,0,0)); //셀병합 sheet.addMergedRegion(new CellRangeAddress(0,1,1,1)); //셀병합 sheet.addMergedRegion(new CellRangeAddress(0,1,2,2)); //셀병합 sheet.addMergedRegion(new CellRangeAddress(0,1,3,3)); //셀병합 cell = row.createCell(0); cell.setCellValue("번호"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("날짜"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("문자유형"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("발송건수"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("내용"); cell.setCellStyle(style); /*cell = row.createCell(5); cell.setCellValue("충전"); sheet.addMergedRegion(new CellRangeAddress(0,0,5,6)); //셀병합 cell.setCellStyle(style); cell = row.createCell(6); cell.setCellStyle(style);*/ cell = row.createCell(5); cell.setCellValue("사용"); sheet.addMergedRegion(new CellRangeAddress(0,0,5,6)); //셀병합 cell.setCellStyle(style); cell = row.createCell(6); cell.setCellStyle(style); /*cell = row.createCell(9); cell.setCellValue("잔액"); sheet.addMergedRegion(new CellRangeAddress(0,0,9,10)); //셀병합 cell.setCellStyle(style); cell = row.createCell(10); cell.setCellStyle(style);*/ row = sheet.createRow(1); cell = row.createCell(0); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellStyle(style); /*cell = row.createCell(5); cell.setCellValue("충전금"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("포인트"); cell.setCellStyle(style);*/ cell = row.createCell(5); cell.setCellValue("충전금"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("포인트"); cell.setCellStyle(style); /*cell = row.createCell(9); cell.setCellValue("충전금"); cell.setCellStyle(style); cell = row.createCell(10); cell.setCellValue("포인트"); cell.setCellStyle(style);*/ DecimalFormat formatter = new DecimalFormat("###,###.##"); String befCash = ""; String befPoint = ""; String totPrice = ""; String thisPoint = ""; for(int i=0; i < payUserList.size(); i++) { befCash = formatter.format(Double.parseDouble(((MjonMsgVO)payUserList.get(i)).getBefCash())); befPoint = formatter.format(Double.parseDouble(((MjonMsgVO)payUserList.get(i)).getBefPoint())); totPrice = formatter.format(Double.parseDouble(((MjonMsgVO)payUserList.get(i)).getTotPrice())); thisPoint = formatter.format(Double.parseDouble(((MjonMsgVO)payUserList.get(i)).getThisPoint())); String msgType = "단문"; if(payUserList.get(i).getMsgTypeTxt().equals("6") && payUserList.get(i).getFileCnt().equals("0")) { msgType = "장문"; }else if(payUserList.get(i).getMsgTypeTxt().equals("6") && !payUserList.get(i).getFileCnt().equals("0")) { msgType = "그림"; }else if(payUserList.get(i).getMsgTypeTxt().equals("8")) { msgType = "알림톡"; }else if(payUserList.get(i).getMsgTypeTxt().equals("9")) { msgType = "친구톡"; }else if(payUserList.get(i).getMsgTypeTxt().equals("fax")) { msgType = "팩스"; } int sendCount = ((MjonMsgVO)payUserList.get(i)).getSendCount(); String sendSum = Integer.toString(sendCount); if(payUserList.get(i).getMsgTypeTxt().equals("fax")) { sendSum = sendSum+"("+payUserList.get(i).getFileCnt()+")"; } row = sheet.createRow(i+2); for(int j=0 ; j < 7 ; j++) { cell = row.createCell(j); cell.setCellStyle(style); if(j==0) cell.setCellValue(i+1); //번호 if(j==1) cell.setCellValue(((MjonMsgVO)payUserList.get(i)).getRegDate()); //결제일시 if(j==2) cell.setCellValue(msgType); //문자유형 if(j==3) cell.setCellValue(((MjonMsgVO)payUserList.get(i)).getSuccSendCnt()); //발송건수 if(j==4) cell.setCellValue(((MjonMsgVO)payUserList.get(i)).getSmsTxt()); //내용 if(j==5) cell.setCellValue(((MjonMsgVO)payUserList.get(i)).getSuccSendPrice()); //충전 - 충전금 if(j==6) cell.setCellValue(0); //충전 - 포인트 /*if(j==7) cell.setCellValue(totPrice); //사용 - 충전금 if(j==8) cell.setCellValue(0); //사용 - 포인트 if(j==9) cell.setCellValue(thisPoint); //잔액 - 충전금 if(j==10) cell.setCellValue(befPoint); //잔액 - 포인트 */ } } } response.setHeader("Set-Cookie", "fileDownload=true; path=/"); SimpleDateFormat mSimpleDateFormat = new SimpleDateFormat ( "yyyy_MM_dd_HH_mm_ss", Locale.KOREA ); Date currentTime = new Date (); String mTime = mSimpleDateFormat.format ( currentTime ); fileName = fileName+"("+mTime+")"; response.setHeader("Content-Disposition", String.format("attachment; filename=\""+new String((fileName).getBytes("KSC5601"),"8859_1")+".xlsx")); wb.write(response.getOutputStream()); }catch(Exception e) { response.setHeader("Set-Cookie", "fileDownload=false; path=/"); response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate"); response.setHeader("Content-Type","text/html; charset=utf-8"); OutputStream out = null; try { out = response.getOutputStream(); byte[] data = new String("fail..").getBytes(); out.write(data, 0, data.length); } catch(Exception ignore) { ignore.printStackTrace(); } finally { if(out != null) try { out.close(); } catch(Exception ignore) {} } }finally { // 디스크 적었던 임시파일을 제거합니다. wb.dispose(); try { wb.close(); } catch(Exception ignore) {} } } @RequestMapping(value= {"/web/member/pay/PrintPayUserSWListAjax.do"}) public String printPayUserSWListAjax( @ModelAttribute("searchVO") MjonMsgVO mjonMsgVO , RedirectAttributes redirectAttributes , ModelMap model) throws Exception { //로그인 권한정보 불러오기 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); String userNm = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getName()); mjonMsgVO.setUserId(userId); model.addAttribute("userNm", userNm); mjonMsgVO.setFirstIndex(0); mjonMsgVO.setRecordCountPerPage(10000); //결제 리스트 정보 불러오기 List<MjonMsgVO> payUserList = mjonMsgDataService.selectPayUserSWList(mjonMsgVO); model.addAttribute("payUserList", payUserList); Calendar cal = Calendar.getInstance(); int year = cal.get(Calendar.YEAR); int month = cal.get(Calendar.MONTH) + 1; int day = cal.get(Calendar.DAY_OF_MONTH); model.addAttribute("year", year); model.addAttribute("month", month); model.addAttribute("day", day); return "web/pay/PrintPayUserSWListPopUp"; } /** * 요금사용내역 - 카카오 테스트용 * @param mjonMsgVO * @param model * @return "/web/member/pay/PayUserListAjax.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/PayUserWithKakaoListAjax.do"}) public String PayUserWithKakaoListAjax(@ModelAttribute("searchVO") MjonMsgVO mjonMsgVO, HttpServletRequest request, ModelMap model) throws Exception{ LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgVO.setUserId(userId); if(mjonMsgVO.getPageUnit() != 10) { mjonMsgVO.setPageUnit(mjonMsgVO.getPageUnit()); } /** pageing */ PaginationInfo paginationInfo = new PaginationInfo(); paginationInfo.setCurrentPageNo(mjonMsgVO.getPageIndex()); paginationInfo.setRecordCountPerPage(mjonMsgVO.getPageUnit()); paginationInfo.setPageSize(mjonMsgVO.getPageSize()); mjonMsgVO.setFirstIndex(paginationInfo.getFirstRecordIndex()); mjonMsgVO.setLastIndex(paginationInfo.getLastRecordIndex()); mjonMsgVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage()); if("".equals(mjonMsgVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonMsgVO.setSearchSortCnd("regDate"); mjonMsgVO.setSearchSortOrd("desc"); } List<MjonMsgVO> payUserList = mjonMsgDataService.selectPayUserWithKakaoList(mjonMsgVO); paginationInfo.setTotalRecordCount(payUserList.size()> 0 ? payUserList.get(0).getTotCnt() : 0); model.addAttribute("paginationInfo", paginationInfo); model.addAttribute("payUserList", payUserList); return "/web/pay/PayUserWithKakaoListAjax"; } @RequestMapping(value= {"/web/member/pay/PrintPayUserListAjax.do"}) public String printPayUserListAjax( @ModelAttribute("searchVO") MjonMsgVO mjonMsgVO , RedirectAttributes redirectAttributes , ModelMap model) throws Exception { //로그인 권한정보 불러오기 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); String userNm = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getName()); mjonMsgVO.setUserId(userId); model.addAttribute("userNm", userNm); mjonMsgVO.setFirstIndex(0); mjonMsgVO.setRecordCountPerPage(10000); //결제 리스트 정보 불러오기 List<MjonMsgVO> payUserList = mjonMsgDataService.selectPayUserList(mjonMsgVO); model.addAttribute("payUserList", payUserList); Calendar cal = Calendar.getInstance(); int year = cal.get(Calendar.YEAR); int month = cal.get(Calendar.MONTH) + 1; int day = cal.get(Calendar.DAY_OF_MONTH); model.addAttribute("year", year); model.addAttribute("month", month); model.addAttribute("day", day); return "web/pay/PrintPayUserListPopUp"; } //사용내역 인쇄하기 카카오 추가 @RequestMapping(value= {"/web/member/pay/PrintPayUserWithKakaoListAjax.do"}) public String PrintPayUserWithKakaoListAjax( @ModelAttribute("searchVO") MjonMsgVO mjonMsgVO , RedirectAttributes redirectAttributes , ModelMap model) throws Exception { //로그인 권한정보 불러오기 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); String userNm = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getName()); mjonMsgVO.setUserId(userId); model.addAttribute("userNm", userNm); mjonMsgVO.setFirstIndex(0); mjonMsgVO.setRecordCountPerPage(10000); //결제 리스트 정보 불러오기 List<MjonMsgVO> payUserList = mjonMsgDataService.selectPayUserWithKakaoList(mjonMsgVO); model.addAttribute("payUserList", payUserList); Calendar cal = Calendar.getInstance(); int year = cal.get(Calendar.YEAR); int month = cal.get(Calendar.MONTH) + 1; int day = cal.get(Calendar.DAY_OF_MONTH); model.addAttribute("year", year); model.addAttribute("month", month); model.addAttribute("day", day); return "web/pay/PrintPayUserWithKakaoListPopUp"; } /** * 예약관리 문자 상세보기 내용 * @param searchVO * @param model * @return "/web/member/pay/selectReservMsgDetailDataAjax.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/selectPayUserMsgDetailDataAjax.do"}) public String selectPayUserMsgDetailDataAjax(@ModelAttribute("searchVO") MjonMsgVO mjonMsgVO, ModelMap model) throws Exception{ //로그인 권한정보 불러오기 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgVO.setUserId(userId); //문자 상세보기 처리 MjonMsgVO resultMsgDetail = mjonMsgDataService.selectPayUserMsgDetailDataAjax(mjonMsgVO); model.addAttribute("resultMsgDetail", resultMsgDetail); //문자 상세보기 이미지 아이디 받아오기 String atchFileId1 =""; String atchFileId2 =""; String atchFileId3 =""; String path1 = resultMsgDetail.getFilePath1(); String path2 = resultMsgDetail.getFilePath2(); String path3 = resultMsgDetail.getFilePath3(); FileVO fileVO1 = new FileVO(); FileVO fileVO2 = new FileVO(); FileVO fileVO3 = new FileVO(); if(StringUtil.isNotEmpty(path1)) { //파일경로 및 이름을 이용하여 atchFileId, fileExtsn 번호 받아오기 fileVO1 = getFilePathToAtchFileId(path1); } if(StringUtil.isNotEmpty(path2)) { //파일경로 및 이름을 이용하여 atchFileId, fileExtsn 번호 받아오기 fileVO2 = getFilePathToAtchFileId(path2); } if(StringUtil.isNotEmpty(path3)) { //파일경로 및 이름을 이용하여 atchFileId, fileExtsn 번호 받아오기 fileVO3 = getFilePathToAtchFileId(path3); } model.addAttribute("fileVO1", fileVO1); model.addAttribute("fileVO2", fileVO2); model.addAttribute("fileVO3", fileVO3); return "web/pay/PayUserMsgDetailPopAjax"; } /** * 사용내역 문자 상세보기 내용(카카오 추가) * @param searchVO * @param model * @return "/web/member/pay//selectPayUserMsgWithKakaoDetailDataAjax.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/selectPayUserMsgWithKakaoDetailDataAjax.do"}) public String selectPayUserMsgWithKakaoDetailDataAjax(@ModelAttribute("searchVO") MjonMsgVO mjonMsgVO, ModelMap model) throws Exception{ //로그인 권한정보 불러오기 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgVO.setUserId(userId); //문자 상세보기 처리 MjonMsgVO resultMsgDetail = mjonMsgDataService.selectPayUserMsgDetailDataAjax(mjonMsgVO); model.addAttribute("resultMsgDetail", resultMsgDetail); //문자 상세보기 이미지 아이디 받아오기 String atchFileId1 =""; String atchFileId2 =""; String atchFileId3 =""; String path1 = resultMsgDetail.getFilePath1(); String path2 = resultMsgDetail.getFilePath2(); String path3 = resultMsgDetail.getFilePath3(); FileVO fileVO1 = new FileVO(); FileVO fileVO2 = new FileVO(); FileVO fileVO3 = new FileVO(); if(StringUtil.isNotEmpty(path1)) { //파일경로 및 이름을 이용하여 atchFileId, fileExtsn 번호 받아오기 fileVO1 = getFilePathToAtchFileId(path1); } if(StringUtil.isNotEmpty(path2)) { //파일경로 및 이름을 이용하여 atchFileId, fileExtsn 번호 받아오기 fileVO2 = getFilePathToAtchFileId(path2); } if(StringUtil.isNotEmpty(path3)) { //파일경로 및 이름을 이용하여 atchFileId, fileExtsn 번호 받아오기 fileVO3 = getFilePathToAtchFileId(path3); } model.addAttribute("fileVO1", fileVO1); model.addAttribute("fileVO2", fileVO2); model.addAttribute("fileVO3", fileVO3); return "web/pay/PayUserMsgWithKakaoDetailPopAjax"; } //파일 이름을 이용하여 첨부파일 아이디(atchFileId), 첨부파일 순번(fileExtsn)을 찾는 함수 public FileVO getFilePathToAtchFileId(String filePath) throws Exception{ //마지막 / 의 인덱스 값 +1을 기준으로 문자열을 짤라줌 String strFilePath = filePath.substring(filePath.lastIndexOf("/")+1); FileVO returnFileVO = new FileVO(); //파일 이름과 확장자를 분리해줌 String[] arrFileNm = strFilePath.split("\\."); String fileNm = arrFileNm[0]; String fileExtsn = arrFileNm[1]; FileVO fileVO = new FileVO(); fileVO.setStreFileNm(fileNm); fileVO.setFileExtsn(fileExtsn); returnFileVO = mjonReservMsgService.selectFileNmToAtchFileIdInfo(fileVO); return returnFileVO; } /** * 요금사용내역 * @param mjonMsgVO * @param model * @return "/web/member/pay/simpRecipAjax.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/simpRecipAjax.do"}) public String simpRecipAjax(MjonPayVO mjonPayVO, HttpServletRequest request, ModelMap model) throws Exception{ LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonPayVO.setUserId(userId); mjonPayVO.setBuyerName(loginVO.getName()); System.out.println(mjonPayVO.getMoid()); System.out.println(mjonPayVO.getTid()); MjonPayVO resultPayVO = mjonPayService.selectPayVO(mjonPayVO); // Step 1. 세금계산서, 현금영수증 발행 TaxVO taxVO2 = new TaxVO(); taxVO2.setMoid(mjonPayVO.getMoid()); taxVO2.setRcptType("3"); taxService.deleteReceiptInfo(taxVO2); // Step 2. 간이영수증 발행 TaxVO taxVO = new TaxVO(); //간이영수증은 해당 팝업 호출 시 바로 완료처리함 taxVO.setMoid(mjonPayVO.getMoid()); taxVO.setRcptType("0"); // 발행유형-[PG결제-현금영수증 유형과 동일] 1:소득공제, 2:지출증빙, 3:의무발행, 9:세금계산서, 0:간이영수증 taxVO.setConfirmYn("Y"); taxVO.setFrstRegisterId(userId); taxService.insertCashBill(taxVO); Calendar cal = Calendar.getInstance(); int year = cal.get(Calendar.YEAR); int month = cal.get(Calendar.MONTH) + 1; int day = cal.get(Calendar.DAY_OF_MONTH); model.addAttribute("resultPayVO", resultPayVO); model.addAttribute("year", year); model.addAttribute("month", month); model.addAttribute("day", day); return "/web/pay/PaySimpRecipAjax"; } /** * 요금 사용내역 엑셀 다운 * @param mjonMsgVO * @param model * @return "/web/member/pay/PayUserListAjax.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/PayUserListExcelDownload.do"}) public void PayUserListExcelDownload(@ModelAttribute("searchVO") MjonMsgVO mjonMsgVO, HttpServletRequest request, HttpServletResponse response , ModelMap model) throws Exception{ // 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다. SXSSFWorkbook wb = new SXSSFWorkbook(100); String fileName ="사용내역 엑셀 리스트"; // 저장 파일명 String sheetTitle = "요금사용내역" ; // 셀 제목 Sheet sheet = wb.createSheet(sheetTitle); Cell cell = null; Row row = null; CellStyle style = wb.createCellStyle(); style.setBorderBottom(CellStyle.BORDER_THIN); //테두리 두껍게 style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderTop(CellStyle.BORDER_THIN); // 정렬 style.setAlignment(CellStyle.ALIGN_CENTER); //가운데 정렬 style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //높이 가운데 정렬 Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); //글씨 bold sheet.setColumnWidth(1, 5000); // 칼럼의 폭 조절 sheet.setColumnWidth(4, 10000); // 칼럼의 폭 조절 try{ LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgVO.setUserId(userId); mjonMsgVO.setRecordCountPerPage(100000); mjonMsgVO.setFirstIndex(0); if("".equals(mjonMsgVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonMsgVO.setSearchSortCnd("regDate"); mjonMsgVO.setSearchSortOrd("desc"); } List<MjonMsgVO> payUserList = mjonMsgDataService.selectPayUserList(mjonMsgVO); {//화면 리스트 row = sheet.createRow(0); sheet.addMergedRegion(new CellRangeAddress(0,1,0,0)); //셀병합 sheet.addMergedRegion(new CellRangeAddress(0,1,1,1)); //셀병합 sheet.addMergedRegion(new CellRangeAddress(0,1,2,2)); //셀병합 sheet.addMergedRegion(new CellRangeAddress(0,1,3,3)); //셀병합 cell = row.createCell(0); cell.setCellValue("번호"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("날짜"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("문자유형"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("발송건수"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("내용"); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("충전"); sheet.addMergedRegion(new CellRangeAddress(0,0,5,6)); //셀병합 cell.setCellStyle(style); cell = row.createCell(6); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("사용"); sheet.addMergedRegion(new CellRangeAddress(0,0,7,8)); //셀병합 cell.setCellStyle(style); cell = row.createCell(8); cell.setCellStyle(style); cell = row.createCell(9); cell.setCellValue("잔액"); sheet.addMergedRegion(new CellRangeAddress(0,0,9,10)); //셀병합 cell.setCellStyle(style); cell = row.createCell(10); cell.setCellStyle(style); row = sheet.createRow(1); cell = row.createCell(0); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("충전금"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("포인트"); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("충전금"); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue("포인트"); cell.setCellStyle(style); cell = row.createCell(9); cell.setCellValue("충전금"); cell.setCellStyle(style); cell = row.createCell(10); cell.setCellValue("포인트"); cell.setCellStyle(style); DecimalFormat formatter = new DecimalFormat("###,###.##"); String befCash = ""; String befPoint = ""; String totPrice = ""; String thisPoint = ""; for(int i=0; i < payUserList.size(); i++) { befCash = formatter.format(Double.parseDouble(((MjonMsgVO)payUserList.get(i)).getBefCash())); befPoint = formatter.format(Double.parseDouble(((MjonMsgVO)payUserList.get(i)).getBefPoint())); totPrice = formatter.format(Double.parseDouble(((MjonMsgVO)payUserList.get(i)).getTotPrice())); thisPoint = formatter.format(Double.parseDouble(((MjonMsgVO)payUserList.get(i)).getThisPoint())); String msgType = "단문"; if(payUserList.get(i).getMsgTypeTxt().equals("6") && payUserList.get(i).getFileCnt().equals("0")) { msgType = "장문"; }else if(payUserList.get(i).getMsgTypeTxt().equals("6") && !payUserList.get(i).getFileCnt().equals("0")) { msgType = "그림"; }else if(payUserList.get(i).getMsgTypeTxt().equals("8")) { msgType = "알림톡"; }else if(payUserList.get(i).getMsgTypeTxt().equals("9")) { msgType = "친구톡"; }else if(payUserList.get(i).getMsgTypeTxt().equals("fax")) { msgType = "팩스"; } int sendCount = ((MjonMsgVO)payUserList.get(i)).getSendCount(); String sendSum = Integer.toString(sendCount); if(payUserList.get(i).getMsgTypeTxt().equals("fax")) { sendSum = sendSum+"("+payUserList.get(i).getFileCnt()+")"; } row = sheet.createRow(i+2); for(int j=0 ; j <= sendPayUserExcelValue.length ; j++) { cell = row.createCell(j); cell.setCellStyle(style); if(j==0) cell.setCellValue(i+1); //번호 if(j==1) cell.setCellValue(((MjonMsgVO)payUserList.get(i)).getRegDate()); //결제일시 if(j==2) cell.setCellValue(msgType); //문자유형 if(j==3) cell.setCellValue(((MjonMsgVO)payUserList.get(i)).getSendCount()); //발송건수 if(j==4) cell.setCellValue(((MjonMsgVO)payUserList.get(i)).getSmsTxt()); //내용 if(j==5) cell.setCellValue(befCash); //충전 - 충전금 if(j==6) cell.setCellValue(befPoint); //충전 - 포인트 if(j==7) cell.setCellValue(totPrice); //사용 - 충전금 if(j==8) cell.setCellValue(0); //사용 - 포인트 if(j==9) cell.setCellValue(thisPoint); //잔액 - 충전금 if(j==10) cell.setCellValue(befPoint); //잔액 - 포인트 } } } response.setHeader("Set-Cookie", "fileDownload=true; path=/"); SimpleDateFormat mSimpleDateFormat = new SimpleDateFormat ( "yyyy_MM_dd_HH_mm_ss", Locale.KOREA ); Date currentTime = new Date (); String mTime = mSimpleDateFormat.format ( currentTime ); fileName = fileName+"("+mTime+")"; response.setHeader("Content-Disposition", String.format("attachment; filename=\""+new String((fileName).getBytes("KSC5601"),"8859_1")+".xlsx")); wb.write(response.getOutputStream()); }catch(Exception e) { response.setHeader("Set-Cookie", "fileDownload=false; path=/"); response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate"); response.setHeader("Content-Type","text/html; charset=utf-8"); OutputStream out = null; try { out = response.getOutputStream(); byte[] data = new String("fail..").getBytes(); out.write(data, 0, data.length); } catch(Exception ignore) { ignore.printStackTrace(); } finally { if(out != null) try { out.close(); } catch(Exception ignore) {} } }finally { // 디스크 적었던 임시파일을 제거합니다. wb.dispose(); try { wb.close(); } catch(Exception ignore) {} } } /** * 요금 사용내역 엑셀 다운 카카오 추가 * @param mjonMsgVO * @param model * @return "/web/member/pay/PayUserWithKakaoListExcelDownload.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/PayUserWithKakaoListExcelDownload.do"}) public void PayUserWithKakaoListExcelDownload(@ModelAttribute("searchVO") MjonMsgVO mjonMsgVO, HttpServletRequest request, HttpServletResponse response , ModelMap model) throws Exception{ // 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다. SXSSFWorkbook wb = new SXSSFWorkbook(100); String fileName ="사용내역 엑셀 리스트"; // 저장 파일명 String sheetTitle = "요금사용내역" ; // 셀 제목 Sheet sheet = wb.createSheet(sheetTitle); Cell cell = null; Row row = null; CellStyle style = wb.createCellStyle(); style.setBorderBottom(CellStyle.BORDER_THIN); //테두리 두껍게 style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderTop(CellStyle.BORDER_THIN); // 정렬 style.setAlignment(CellStyle.ALIGN_CENTER); //가운데 정렬 style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //높이 가운데 정렬 Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); //글씨 bold sheet.setColumnWidth(1, 5000); // 칼럼의 폭 조절 sheet.setColumnWidth(4, 10000); // 칼럼의 폭 조절 try{ LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgVO.setUserId(userId); mjonMsgVO.setRecordCountPerPage(100000); mjonMsgVO.setFirstIndex(0); if("".equals(mjonMsgVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonMsgVO.setSearchSortCnd("regDate"); mjonMsgVO.setSearchSortOrd("desc"); } List<MjonMsgVO> payUserList = mjonMsgDataService.selectPayUserWithKakaoList(mjonMsgVO); {//화면 리스트 row = sheet.createRow(0); sheet.addMergedRegion(new CellRangeAddress(0,1,0,0)); //셀병합 sheet.addMergedRegion(new CellRangeAddress(0,1,1,1)); //셀병합 sheet.addMergedRegion(new CellRangeAddress(0,1,2,2)); //셀병합 sheet.addMergedRegion(new CellRangeAddress(0,1,3,3)); //셀병합 cell = row.createCell(0); cell.setCellValue("번호"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("날짜"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("문자유형"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("발송건수"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("내용"); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("충전"); sheet.addMergedRegion(new CellRangeAddress(0,0,5,6)); //셀병합 cell.setCellStyle(style); cell = row.createCell(6); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("사용"); sheet.addMergedRegion(new CellRangeAddress(0,0,7,8)); //셀병합 cell.setCellStyle(style); cell = row.createCell(8); cell.setCellStyle(style); cell = row.createCell(9); cell.setCellValue("잔액"); sheet.addMergedRegion(new CellRangeAddress(0,0,9,10)); //셀병합 cell.setCellStyle(style); cell = row.createCell(10); cell.setCellStyle(style); row = sheet.createRow(1); cell = row.createCell(0); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("충전금"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("포인트"); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("충전금"); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue("포인트"); cell.setCellStyle(style); cell = row.createCell(9); cell.setCellValue("충전금"); cell.setCellStyle(style); cell = row.createCell(10); cell.setCellValue("포인트"); cell.setCellStyle(style); DecimalFormat formatter = new DecimalFormat("###,###.##"); String befCash = ""; String befPoint = ""; String totPrice = ""; String thisPoint = ""; for(int i=0; i < payUserList.size(); i++) { befCash = formatter.format(Double.parseDouble(((MjonMsgVO)payUserList.get(i)).getBefCash())); //카카오 테스트 데이터들에 beforPoint가 null이라 임시로 null체크 처리 if(payUserList.get(i).getBefPoint() != null){ befPoint = formatter.format(Double.parseDouble(((MjonMsgVO)payUserList.get(i)).getBefPoint())); } totPrice = formatter.format(Double.parseDouble(((MjonMsgVO)payUserList.get(i)).getTotPrice())); thisPoint = formatter.format(Double.parseDouble(((MjonMsgVO)payUserList.get(i)).getThisPoint())); String msgType = "단문"; if(payUserList.get(i).getMsgTypeTxt().equals("6") && payUserList.get(i).getFileCnt().equals("0")) { msgType = "장문"; }else if(payUserList.get(i).getMsgTypeTxt().equals("6") && !payUserList.get(i).getFileCnt().equals("0")) { msgType = "그림"; }else if(payUserList.get(i).getMsgTypeTxt().equals("8")) { msgType = "알림톡"; }else if(payUserList.get(i).getMsgTypeTxt().equals("9")) { msgType = "친구톡"; }else if(payUserList.get(i).getMsgTypeTxt().equals("fax")) { msgType = "팩스"; } int sendCount = ((MjonMsgVO)payUserList.get(i)).getSendCount(); String sendSum = Integer.toString(sendCount); if(payUserList.get(i).getMsgTypeTxt().equals("fax")) { sendSum = sendSum+"("+payUserList.get(i).getFileCnt()+")"; } row = sheet.createRow(i+2); for(int j=0 ; j < sendPayUserExcelValue.length ; j++) { cell = row.createCell(j); cell.setCellStyle(style); if(j==0) cell.setCellValue(i+1); //번호 if(j==1) cell.setCellValue(((MjonMsgVO)payUserList.get(i)).getRegDate()); //결제일시 if(j==2) cell.setCellValue(msgType); //문자유형 if(j==3) cell.setCellValue(sendSum); //발송건수 if(j==4) cell.setCellValue(((MjonMsgVO)payUserList.get(i)).getSmsTxt()); //내용 if(j==5) cell.setCellValue(befCash); //충전 - 충전금 if(j==6) cell.setCellValue(befPoint); //충전 - 포인트 if(j==7) cell.setCellValue(totPrice); //사용 - 충전금 if(j==8) cell.setCellValue(0); //사용 - 포인트 if(j==9) cell.setCellValue(thisPoint); //잔액 - 충전금 if(j==10) cell.setCellValue(befPoint); //잔액 - 포인트 } } } response.setHeader("Set-Cookie", "fileDownload=true; path=/"); SimpleDateFormat mSimpleDateFormat = new SimpleDateFormat ( "yyyy_MM_dd_HH_mm_ss", Locale.KOREA ); Date currentTime = new Date (); String mTime = mSimpleDateFormat.format ( currentTime ); fileName = fileName+"("+mTime+")"; response.setHeader("Content-Disposition", String.format("attachment; filename=\""+new String((fileName).getBytes("KSC5601"),"8859_1")+".xlsx")); wb.write(response.getOutputStream()); }catch(Exception e) { response.setHeader("Set-Cookie", "fileDownload=false; path=/"); response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate"); response.setHeader("Content-Type","text/html; charset=utf-8"); OutputStream out = null; try { out = response.getOutputStream(); byte[] data = new String("fail..").getBytes(); out.write(data, 0, data.length); } catch(Exception ignore) { ignore.printStackTrace(); } finally { if(out != null) try { out.close(); } catch(Exception ignore) {} } }finally { // 디스크 적었던 임시파일을 제거합니다. wb.dispose(); try { wb.close(); } catch(Exception ignore) {} } } /** * 계산서/현금영수증 발행 등록 * @param searchVO * @param model * @return "/web/pay/PayView.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/BillPub.do"}) public String BillPub(@ModelAttribute("searchVO") MjonPayVO mjonPayVO, @RequestParam(required = false) String billType, HttpServletRequest request, RedirectAttributes redirectAttributes, ModelMap model) throws Exception{ /*LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser();*/ LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; if(loginVO == null) { //redirectAttributes.addFlashAttribute("message", "문자온 서비스는 로그인 후 이용 가능합니다."); return "redirect:/web/user/login/login.do"; } String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); // 이메일 코드조회 ComDefaultCodeVO voComCode = new ComDefaultCodeVO(); voComCode.setCodeId("ITN031"); model.addAttribute("emailCode", cmmUseService.selectCmmCodeDetail(voComCode)); MberManageVO mberManageVO = mberManageService.selectMber(userId); //결제내역에서 왔을 시 세금계산서/현금영수증 수정 화면 맞게 뿌려주기 mberManageVO.setBillType(billType); MjonCandidateVO mjonCandidateVO = new MjonCandidateVO(); if("p".equals(mberManageVO.getDept())) { //개인회원 선거 후보자 정보 불러오기 mjonCandidateVO = mjonCandidateService.selectCandidateDataInfo(userId); if(mjonCandidateVO != null) { mjonCandidateVO.setRegidentNo1(egovCryptoUtil.decrypt(mjonCandidateVO.getRegidentNo1())); mjonCandidateVO.setRegidentNo2(egovCryptoUtil.decrypt(mjonCandidateVO.getRegidentNo2())); } } model.addAttribute("mberManageVO", mberManageVO); model.addAttribute("mjonCandidateVO", mjonCandidateVO); return "/web/pay/BillPub"; } /** * 계산서/현금영수증 자동발행 select - 세금계산서 자동 발행 일 시 현금영수증 탭 이동 막기, 현금영수증 자동 발행 시 세금계산서 탭 이동 막기 * @param searchVO * @param model * @return "/web/pay/PayView.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/billSelectAjax.do"}) public ModelAndView billSelectAjax(@ModelAttribute("searchVO") MjonPayVO mjonPayVO, HttpServletRequest request, ModelMap model) throws Exception{ ModelAndView modelAndView = new ModelAndView(); modelAndView.setViewName("jsonView"); LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); MberManageVO mberManageVO = mberManageService.selectMber(loginVO.getId()); //개인회원일 시 선거후보자 주민등록번호 체크 후 후보자 미등록시 세금계산서 발행 막기 MjonCandidateVO mjonCandidateVO = new MjonCandidateVO(); String regidentNo = ""; if("p".equals(mberManageVO.getDept())) { mjonCandidateVO = mjonCandidateService.selectCandidateDataInfo(mberManageVO.getMberId()); if(mjonCandidateVO != null) { regidentNo = mjonCandidateVO.getRegidentNo1() + mjonCandidateVO.getRegidentNo2(); } } modelAndView.addObject("dept", mberManageVO.getDept()); modelAndView.addObject("regidentNo", regidentNo); modelAndView.addObject("taxbillAutoYN", mberManageVO.getTaxbillAuto()); modelAndView.addObject("cashbillAutoYN", mberManageVO.getCashbillAuto()); return modelAndView; } /** * 계산서 발행 수정 * @param searchVO * @param model * @return "/web/pay/PayView.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/updateTaxbill.do"}) public String updateTaxbill(@ModelAttribute("searchVO") MjonPayVO mjonPayVO, @ModelAttribute("mberManageVO") MberManageVO mberManageVO, HttpServletRequest request, ModelMap model, RedirectAttributes redirectAttributes) throws Exception{ //230801 이지우 추가 - 세금계산서 발행 시 아이디 변조하여 다른 계정의 세금계산서 정보 수정하는 취약점 대응 조치 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getUniqId()); if(userId.equals("")) { redirectAttributes.addFlashAttribute("message", egovMessageSource.getMessage("fail.common.login")); return "redirect:/web/member/pay/BillPub.do"; } try { //세금계산서 정보 수정해주기 mberManageVO.setUniqId(userId); mberManageService.updateTaxbill(mberManageVO); /* * 20231122 우영두 기능 추가 * 개인회원인 경우 * 세금계산서 타입이 개인인 경우 * 후보자 정보가 있는지 확인하여 후보자 정보가 있다면 세금계산서 정보와 일치하도록 변경된 정보를 후보자 정보도 수정해준다. * */ String mberId = loginVO.getId(); String taxCNm = mberManageVO.getTaxCNm(); MberManageVO tmpMberManageVO = mjonMsgDataService.selectMberManageInfo(mberId); String dept = tmpMberManageVO.getDept(); //후보자 정보 조회 MjonCandidateVO mjonCandidateVO = mjonCandidateService.selectCandidateDataInfo(mberId); if(mjonCandidateVO != null && dept.equals("p") && taxCNm != null && taxCNm.length() > 0) { String candidateNm = mjonCandidateVO.getCandidateNm(); if(candidateNm != null && candidateNm.length() > 0) { MjonCandidateVO updtCandidateVO = new MjonCandidateVO(); //이메일 정보 아이디/도메인 정보 분리 String email = mberManageVO.getTaxMngEmail().split("@")[0]; String emailDomain = mberManageVO.getTaxMngEmail().split("@")[1]; /* * 주민번호 앞, 뒷 자리 암호화 처리하기 * 세금계산서에서 주민번호는 별도로 암/복호화 처리를 안하고 저장함 * 기본 가비아 암호화 솔루션 처리로 안한것 같음, 후보자 정보 쪽에서는 전자정부 프레임워크의 암/복호화 적용하여 저장함. * * */ String regidentNo1 = egovCryptoUtil.encrypt(mberManageVO.getTaxCIhidNum().substring(0, 6)); String regidentNo2 = egovCryptoUtil.encrypt(mberManageVO.getTaxCIhidNum().substring(6, 13)); updtCandidateVO.setMberId(mberId); updtCandidateVO.setCandidateNm(taxCNm); updtCandidateVO.setRegidentNo1(regidentNo1); updtCandidateVO.setRegidentNo2(regidentNo2); updtCandidateVO.setCandidateAddr(mberManageVO.getTaxCAddr()); updtCandidateVO.setManagerNm(mberManageVO.getTaxMngNm()); updtCandidateVO.setPhoneNo(mberManageVO.getTaxMngPhoneNum()); updtCandidateVO.setEmail(email); updtCandidateVO.setEmailDomain(emailDomain); updtCandidateVO.setLastUpdusrId(mberId); int resultCnt = mjonCandidateService.updateCandidateDataInfo(updtCandidateVO); } } redirectAttributes.addFlashAttribute("message", egovMessageSource.getMessage("success.common.update")); } catch (Exception e) { System.out.println("updateTaxbill Controller Error!!! " + e); redirectAttributes.addFlashAttribute("message", "세금계산서 정보 수정 중 오류가 발생하였습니다."); return "redirect:/web/member/pay/BillPub.do"; } return "redirect:/web/member/pay/BillPub.do"; } /** * 현금영수증 발행 수정 * @param searchVO * @param model * @return "/web/pay/PayView.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/updateCashbill.do"}) public String updateCashbill(@ModelAttribute("searchVO") MjonPayVO mjonPayVO, @ModelAttribute("mberManageVO") MberManageVO mberManageVO, HttpServletRequest request, ModelMap model, RedirectAttributes redirectAttributes) throws Exception{ mberManageService.updateCashbill(mberManageVO); redirectAttributes.addFlashAttribute("message", egovMessageSource.getMessage("success.common.update")); return "redirect:/web/member/pay/BillPub.do"; } //결제 엑셀 다운로드 @RequestMapping(value= {"/web/member/pay/PayExcelDownload.do"}) public void PayExcelDownload( MjonPayVO mjonPayVO, HttpServletRequest request, HttpServletResponse response , ModelMap model) throws Exception { mjonPayVO.setRecordCountPerPage(100000); mjonPayVO.setFirstIndex(0); LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); // 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다. SXSSFWorkbook wb = new SXSSFWorkbook(100); CellStyle style = wb.createCellStyle(); style.setBorderBottom(CellStyle.BORDER_THIN); //테두리 두껍게 style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderTop(CellStyle.BORDER_THIN); Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); //글씨 bold Cell cell = null; Row row = null; String fileName ="결제내역 엑셀 리스트"; String sheetTitle = ""; try{ if("".equals(mjonPayVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonPayVO.setSearchSortCnd("moid"); mjonPayVO.setSearchSortOrd("desc"); } if(!"".equals(mjonPayVO.getPayMethod())){ mjonPayVO.setSearchCondition2(mjonPayVO.getPayMethod()); } // JSPark 2022.03.16 => 뷰 목록과 데이터 상이 String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonPayVO.setUserId(userId); List<MjonPayVO> resultList = mjonPayService.selectPayList(mjonPayVO); { //화면 리스트 sheetTitle = "요금결제내역" ; //제목 Sheet sheet = wb.createSheet(sheetTitle); row = sheet.createRow(0); for(int i=0 ; i < sendPayExcelValue.length ; i++) { cell = row.createCell(i); cell.setCellStyle(style); cell.setCellValue(sendPayExcelValue[i][1]); } for(int i=0; i < resultList.size(); i++){ row = sheet.createRow(i+1); for(int j=0 ; j < sendPayExcelValue.length ; j++) { cell = row.createCell(j); cell.setCellStyle(style); if(j==0) cell.setCellValue(i+1); //번호 if(j==1) cell.setCellValue(((MjonPayVO)resultList.get(i)).getRegDate()); //결제일시 if(j==2) cell.setCellValue(((MjonPayVO)resultList.get(i)).getPayMethodTxt()); //결제방식 if(j==3) cell.setCellValue(((MjonPayVO)resultList.get(i)).getAmt()); //결제금액(부과세포함) if(j==4) cell.setCellValue(((MjonPayVO)resultList.get(i)).getPgStatusTxt()); //결제상태 if(j==5) cell.setCellValue(((MjonPayVO)resultList.get(i)).getTid()); //결제정보 if(j==6) cell.setCellValue(((MjonPayVO)resultList.get(i)).getRcptTypeTxt()); //증빙서류 발행요청 } } } response.setHeader("Set-Cookie", "fileDownload=true; path=/"); SimpleDateFormat mSimpleDateFormat = new SimpleDateFormat ( "yyyy_MM_dd_HH_mm_ss", Locale.KOREA ); Date currentTime = new Date (); String mTime = mSimpleDateFormat.format ( currentTime ); fileName = fileName+"("+mTime+")"; response.setHeader("Content-Disposition", String.format("attachment; filename=\""+new String((fileName).getBytes("KSC5601"),"8859_1")+".xlsx")); wb.write(response.getOutputStream()); }catch(Exception e) { response.setHeader("Set-Cookie", "fileDownload=false; path=/"); response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate"); response.setHeader("Content-Type","text/html; charset=utf-8"); OutputStream out = null; try { out = response.getOutputStream(); byte[] data = new String("fail..").getBytes(); out.write(data, 0, data.length); } catch(Exception ignore) { ignore.printStackTrace(); } finally { if(out != null) try { out.close(); } catch(Exception ignore) {} } }finally { // 디스크 적었던 임시파일을 제거합니다. wb.dispose(); try { wb.close(); } catch(Exception ignore) {} } } //포인트 교환내역 엑셀 다운로드 @RequestMapping(value= {"/web/member/pay/PointExcelDownload.do"}) public void PointExcelDownload( MjonPayVO mjonPayVO, HttpServletRequest request, HttpServletResponse response , ModelMap model) throws Exception { mjonPayVO.setRecordCountPerPage(100000); mjonPayVO.setFirstIndex(0); LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); // 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다. SXSSFWorkbook wb = new SXSSFWorkbook(100); CellStyle style = wb.createCellStyle(); style.setBorderBottom(CellStyle.BORDER_THIN); //테두리 두껍게 style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderTop(CellStyle.BORDER_THIN); Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); //글씨 bold Cell cell = null; Row row = null; String fileName ="포인트 교환내역 엑셀 리스트"; String sheetTitle = ""; try{ if("".equals(mjonPayVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonPayVO.setSearchSortCnd("pointUseId"); mjonPayVO.setSearchSortOrd("desc"); } // JSPark 2022.03.16 => 뷰 목록과 데이터 상이 String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonPayVO.setUserId(userId); mjonPayVO.setType("1"); // 현금 List<MjonPayVO> resultList = mjonPayService.selectPointUseList(mjonPayVO); { //화면 리스트 sheetTitle = "포인트 교환내역" ; //제목 Sheet sheet = wb.createSheet(sheetTitle); row = sheet.createRow(0); for(int i=0 ; i < sendPointUseExcelValue.length ; i++) { cell = row.createCell(i); cell.setCellStyle(style); cell.setCellValue(sendPointUseExcelValue[i][1]); } for(int i=0; i < resultList.size(); i++){ row = sheet.createRow(i+1); for(int j=0 ; j < sendPointUseExcelValue.length ; j++) { cell = row.createCell(j); cell.setCellStyle(style); if(j==0) cell.setCellValue(i+1); //번호 if(j==1) cell.setCellValue(((MjonPayVO)resultList.get(i)).getFrstRegistPnttm()); //교환일시 if(j==2) { //교환방식 String type = ((MjonPayVO)resultList.get(i)).getType(); if(type.equals("1")) { cell.setCellValue("현금"); }else { cell.setCellValue("캐쉬 전환"); } } if(j==3) cell.setCellValue(((MjonPayVO)resultList.get(i)).getPoint()); //교환 포인트 if(j==4) { //교환상태 String cmpltYn = ((MjonPayVO)resultList.get(i)).getCmpltYn(); if(cmpltYn.equals("Y")) { cell.setCellValue("전환 완료"); }else if(cmpltYn.equals("N")) { cell.setCellValue("처리중"); }else { cell.setCellValue("전환 불가"); } } if(j==5) cell.setCellValue(((MjonPayVO)resultList.get(i)).getBankNm()); //은행 if(j==6) cell.setCellValue(((MjonPayVO)resultList.get(i)).getAccountNum()); //계좌번호 if(j==7) cell.setCellValue(((MjonPayVO)resultList.get(i)).getAccountNm()); //예금주 if(j==8) cell.setCellValue(((MjonPayVO)resultList.get(i)).getMbtlNum()); //연락처 } } } response.setHeader("Set-Cookie", "fileDownload=true; path=/"); SimpleDateFormat mSimpleDateFormat = new SimpleDateFormat ( "yyyy_MM_dd_HH_mm_ss", Locale.KOREA ); Date currentTime = new Date (); String mTime = mSimpleDateFormat.format ( currentTime ); fileName = fileName+"("+mTime+")"; response.setHeader("Content-Disposition", String.format("attachment; filename=\""+new String((fileName).getBytes("KSC5601"),"8859_1")+".xlsx")); wb.write(response.getOutputStream()); }catch(Exception e) { response.setHeader("Set-Cookie", "fileDownload=false; path=/"); response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate"); response.setHeader("Content-Type","text/html; charset=utf-8"); OutputStream out = null; try { out = response.getOutputStream(); byte[] data = new String("fail..").getBytes(); out.write(data, 0, data.length); } catch(Exception ignore) { ignore.printStackTrace(); } finally { if(out != null) try { out.close(); } catch(Exception ignore) {} } }finally { // 디스크 적었던 임시파일을 제거합니다. wb.dispose(); try { wb.close(); } catch(Exception ignore) {} } } //환불 요청내역 엑셀 다운로드 @RequestMapping(value= {"/web/member/pay/refundExcelDownload.do"}) public void refundExcelDownload( MjonPayVO mjonPayVO, HttpServletRequest request, HttpServletResponse response , ModelMap model) throws Exception { // 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다. SXSSFWorkbook wb = new SXSSFWorkbook(100); CellStyle style = wb.createCellStyle(); style.setBorderBottom(CellStyle.BORDER_THIN); //테두리 두껍게 style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderTop(CellStyle.BORDER_THIN); Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); //글씨 bold Cell cell = null; Row row = null; String fileName ="환불 요청내역 엑셀 리스트"; String sheetTitle = ""; try{ RefundVO refundVO = new RefundVO(); refundVO.setRecordCountPerPage(100000); refundVO.setFirstIndex(0); LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); if("".equals(mjonPayVO.getSearchSortCnd())){ //최초조회시 최신것 조회List refundVO.setSearchSortCnd("refundId"); refundVO.setSearchSortOrd("desc"); }else { //환불 요청 리스트 정보 셋팅하기 refundVO.setSearchSortCnd(mjonPayVO.getSearchSortCnd()); refundVO.setSearchSortOrd(mjonPayVO.getSearchSortOrd()); } // JSPark 2022.03.16 => 뷰 목록과 데이터 상이 String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); refundVO.setMberId(userId); String refundListType = mjonPayVO.getRefundListType(); if(refundListType == null || refundListType.equals("")) { mjonPayVO.setRefundListType("tab1"); } refundVO.setStartDate(mjonPayVO.getStartDate()); refundVO.setEndDate(mjonPayVO.getEndDate()); List<RefundVO> resultList = refundService.selectRefundList(refundVO); { //화면 리스트 sheetTitle = "환불 요청내역" ; //제목 Sheet sheet = wb.createSheet(sheetTitle); row = sheet.createRow(0); for(int i=0 ; i < refundExcelValue.length ; i++) { cell = row.createCell(i); cell.setCellStyle(style); cell.setCellValue(refundExcelValue[i][1]); } for(int i=0; i < resultList.size(); i++){ row = sheet.createRow(i+1); for(int j=0 ; j < refundExcelValue.length ; j++) { cell = row.createCell(j); cell.setCellStyle(style); if(j==0) cell.setCellValue(i+1); //번호 if(j==1) cell.setCellValue(((RefundVO)resultList.get(i)).getFrstRegisterPnttm()); //환불요청일시 if(j==2) cell.setCellValue(((RefundVO)resultList.get(i)).getRefundHandlePnttm()); //환불처리일시 if(j==3) cell.setCellValue(((RefundVO)resultList.get(i)).getRefundMoney()); //환불요청금액 if(j==4) cell.setCellValue(((RefundVO)resultList.get(i)).getRefundCash()); //환불처리금액 if(j==5) { //교환상태 String refundStatus = ((RefundVO)resultList.get(i)).getRefundStatus(); if(refundStatus.equals("1")) { cell.setCellValue("환불 요청"); }else if(refundStatus.equals("2")) { cell.setCellValue("환불 처리중 "); }else if(refundStatus.equals("3")) { cell.setCellValue("환불 완료"); }else { cell.setCellValue("환불 취소"); } } } } } response.setHeader("Set-Cookie", "fileDownload=true; path=/"); SimpleDateFormat mSimpleDateFormat = new SimpleDateFormat ( "yyyy_MM_dd_HH_mm_ss", Locale.KOREA ); Date currentTime = new Date (); String mTime = mSimpleDateFormat.format ( currentTime ); fileName = fileName+"("+mTime+")"; response.setHeader("Content-Disposition", String.format("attachment; filename=\""+new String((fileName).getBytes("KSC5601"),"8859_1")+".xlsx")); wb.write(response.getOutputStream()); }catch(Exception e) { response.setHeader("Set-Cookie", "fileDownload=false; path=/"); response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate"); response.setHeader("Content-Type","text/html; charset=utf-8"); OutputStream out = null; try { out = response.getOutputStream(); byte[] data = new String("fail..").getBytes(); out.write(data, 0, data.length); } catch(Exception ignore) { ignore.printStackTrace(); } finally { if(out != null) try { out.close(); } catch(Exception ignore) {} } }finally { // 디스크 적었던 임시파일을 제거합니다. wb.dispose(); try { wb.close(); } catch(Exception ignore) {} } } /** * 결제 Action 페이지 * @param searchVO * @param model * @return "/web/pay/PayView.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/PayActionAjax.do"}) public String PayAction(@ModelAttribute("mjonPayVO") MjonPayVO mjonPayVO, HttpServletRequest request, ModelMap model) throws Exception{ String pattern = (String) request.getAttribute( HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; mjonPayVO.setReturnURL(request.getRequestURL().toString().split("pay")[0] + "pay/payResultAjax.do") ; LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); //테스트 mjonPayVO.setBuyerName(loginVO.getName()); //구매자 이름 mjonPayVO.setBuyerEmail(loginVO.getEmail()); //구매자 이메일 mjonPayVO.setBuyerTel(loginVO.getMoblphonNo()); //구매자 휴대폰 번호 //moid 생성 위치 변경 mjonPayVO.setMoid(idgenPgMoid.getNextStringId()); model.addAttribute("mjonPayVO", mjonPayVO); return "web/cop/nicepay/payRequestMber"; } /** * 포인트 전환 ajax * @param mjonPayVO * @param model * @throws Exception */ @RequestMapping(value= "/web/member/pay/pointUseAjax.do") public ModelAndView pointUseAjax(@ModelAttribute("mjonPayVO") MjonPayVO mjonPayVO, HttpServletRequest request, ModelMap model) throws Exception{ LoginVO user = (LoginVO) EgovUserDetailsHelper.getAuthenticatedUser(); ModelAndView modelAndView = new ModelAndView(); modelAndView.setViewName("jsonView"); mjonPayVO.setFrstRegisterId(user.getId()); mjonPayVO.setUserId(user.getId()); RefundVO refundVO = new RefundVO(); refundVO.setMberId(user.getId()); RefundVO mberInfoVO = refundService.selectRefundMberInfo(refundVO); //포인트 교환요청에서 포인트 정보가 음수(-)로 넘어오는 경우 처리를 위해 절대값 처리 해줌. - 20230824 우영두 mjonPayVO.setPoint(Math.abs(mjonPayVO.getPoint())); if(Double.parseDouble(mberInfoVO.getMberPoint()) < mjonPayVO.getPoint()) { modelAndView.addObject("status", "moreThanUserPoint"); modelAndView.addObject("msg", "현재 회원의 보유포인트보다 큰 포인트를 환불 할 수 없습니다."); return modelAndView; } if("1".equals(mjonPayVO.getType())){ //현금전환 시 mjonPayVO.setMemo("현금 "+mjonPayVO.getPoint()+" 전환"); //전환 내용 mjonPayVO.setCmpltYn("N"); //현금 전환 시 처리 전 }else { //잔액 전환 시 mjonPayVO.setMemo("잔액 "+mjonPayVO.getPoint()+" 전환"); //전환 내용 mjonPayVO.setCmpltYn("Y"); //잔액 전환 시 바로 처리 } try { //impl에서 point use 테이블, point 테이블, cash 테이블, 회워테이블 insert 및 update 처리 mjonPayService.insertPointUse(mjonPayVO); modelAndView.addObject("result", "success"); // 법인폰 알람여부 체크 JoinSettingVO joinSettingVO = new JoinSettingVO(); joinSettingVO = egovSiteManagerService.selectAdminNotiDetail(); // 이메일 체크 if (joinSettingVO != null && joinSettingVO.getEmailNoti().equals("Y")) { String emailTitle = "결제관리 > 포인트 현금 전환 요청"; // CS관리자 이메일 알림전송 mjonNoticeSendUtil.csAdminEmailNoticeSend(user.getName(), user.getId(), emailTitle); } // SLACK 체크 if (joinSettingVO != null && joinSettingVO.getSlackNoti().equals("Y")) { // Slack 메시지 발송(단순본문) String msg = "[문자온] 포인트 현금 전환 요청 - " + user.getName() +"("+ user.getId() + ")"; mjonCommon.sendSimpleSlackMsg(msg); } }catch (Exception e) { modelAndView.addObject("result", "fail"); } return modelAndView; } /** * 포인트 현금 전환 요청 목록 * @param searchVO * @param model * @return "/uss/ion/msg/pointUseCashList" * @throws Exception */ @RequestMapping(value= {"/uss/ion/pay/pointUseCashList.do"}) public String pointUseCashList(@ModelAttribute("searchVO") MjonPayVO mjonPayVO, HttpServletRequest request, ModelMap model) throws Exception{ String pattern = (String) request.getAttribute( HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; /** pageing */ PaginationInfo paginationInfo = new PaginationInfo(); paginationInfo.setCurrentPageNo(mjonPayVO.getPageIndex()); paginationInfo.setRecordCountPerPage(mjonPayVO.getPageUnit()); paginationInfo.setPageSize(mjonPayVO.getPageSize()); mjonPayVO.setFirstIndex(paginationInfo.getFirstRecordIndex()); mjonPayVO.setLastIndex(paginationInfo.getLastRecordIndex()); mjonPayVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage()); if("".equals(mjonPayVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonPayVO.setSearchSortCnd("frstRegistPnttm"); mjonPayVO.setSearchSortOrd("desc"); } mjonPayVO.setType("1"); // 현금 List<MjonPayVO> resultList = mjonPayService.selectPointUseList(mjonPayVO); model.addAttribute("resultList", resultList); paginationInfo.setTotalRecordCount(resultList.size() > 0 ? ((MjonPayVO)resultList.get(0)).getTotCnt() : 0); model.addAttribute("paginationInfo", paginationInfo); return "/uss/ion/pay/PointUseCashList"; } /** * 포인트 현금 전환 요청 상세 * @param searchVO * @param model * @return "/uss/ion/msg/pointuseCashDetail" * @throws Exception */ @RequestMapping(value= {"/uss/ion/pay/pointUseCashDetail.do"}) public String pointUseCashDetail(@ModelAttribute("searchVO") MjonPayVO mjonPayVO, HttpServletRequest request, ModelMap model) throws Exception{ String pattern = (String) request.getAttribute(HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; mjonPayVO = mjonPayService.selectPointUseDetail(mjonPayVO); model.addAttribute("mjonPayVO", mjonPayVO); String userId = mjonPayVO.getUserId(); //회원 캐시 및 포인트 조회하기 String userPoint = "0"; MjonMsgVO mjonMsgVO = new MjonMsgVO(); mjonMsgVO.setUserId(userId); //보유 포인트 정보 불러오기 userPoint = mjonMsgDataService.selectBeforePointData(mjonMsgVO); model.addAttribute("userPoint", userPoint); return "/uss/ion/pay/PointUseCashDetail"; } /** * 포인트 전환 내역 목록 * @param searchVO * @param model * @return "/uss/ion/msg/pointuseList" * @throws Exception */ @RequestMapping(value= {"/uss/ion/pay/pointUseList.do"}) public String pointuseList(@ModelAttribute("searchVO") MjonPayVO mjonPayVO, HttpServletRequest request, ModelMap model) throws Exception{ String pattern = (String) request.getAttribute( HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; /** pageing */ PaginationInfo paginationInfo = new PaginationInfo(); paginationInfo.setCurrentPageNo(mjonPayVO.getPageIndex()); paginationInfo.setRecordCountPerPage(mjonPayVO.getPageUnit()); paginationInfo.setPageSize(mjonPayVO.getPageSize()); mjonPayVO.setFirstIndex(paginationInfo.getFirstRecordIndex()); mjonPayVO.setLastIndex(paginationInfo.getLastRecordIndex()); mjonPayVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage()); if("".equals(mjonPayVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonPayVO.setSearchSortCnd("pointUseId"); mjonPayVO.setSearchSortOrd("desc"); } mjonPayVO.setType("2"); // 보유 포인트 List<MjonPayVO> resultList = mjonPayService.selectPointUseList(mjonPayVO); model.addAttribute("resultList", resultList); paginationInfo.setTotalRecordCount(resultList.size() > 0 ? ((MjonPayVO)resultList.get(0)).getTotCnt() : 0); model.addAttribute("paginationInfo", paginationInfo); return "/uss/ion/pay/PointUseList"; } /** * 포인트 전환 내역 상세 * @param searchVO * @param model * @return "/uss/ion/msg/pointuseDetail" * @throws Exception */ @RequestMapping(value= {"/uss/ion/pay/pointUseDetail.do"}) public String pointUseDetail(@ModelAttribute("searchVO") MjonPayVO mjonPayVO, HttpServletRequest request, ModelMap model) throws Exception{ String pattern = (String) request.getAttribute( HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; mjonPayVO = mjonPayService.selectPointUseDetail(mjonPayVO); model.addAttribute("mjonPayVO", mjonPayVO); return "/uss/ion/pay/PointUseDetail"; } /** * 포인트 등록/수정 * @param searchVO * @param model * @return "/uss/ion/pay/PointUseModify.do" * @throws Exception */ @RequestMapping(value = {"/uss/ion/pay/PointUseModify.do"}) public String PointUseModify( @ModelAttribute("searchVO") MjonPayVO searchVO, HttpServletRequest request , Model model) throws Exception { model.addAttribute("searchVO", searchVO); return "/uss/ion/pay/PointUseModify"; } // 포인트를 캐시로 전환 @RequestMapping(value = {"/uss/ion/pay/InsertUsePointAdmAjax.do"}) public ModelAndView insertUsePoint( @ModelAttribute("searchVO") MjonPayVO mjonPayVO ,HttpServletRequest request) throws Exception { ModelAndView modelAndView = new ModelAndView(); modelAndView.setViewName("jsonView"); boolean isSuccess = true; String msg = ""; try { MjonMsgVO mjonMsgVO = new MjonMsgVO(); mjonMsgVO.setUserId(mjonPayVO.getUserId()); int befPoint = Integer.parseInt(mjonMsgDataService.selectBeforePointData(mjonMsgVO)); if (befPoint < mjonPayVO.getPoint()) { isSuccess = false; msg = "보유포인트보다 이전할 포인트가 더 많습니다. 보유포인트를 확인해주세요."; } else { LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonPayVO.setFrstRegisterId(userId); mjonPayVO.setMemo("잔액 "+mjonPayVO.getPoint()+" 전환"); mjonPayVO.setType("2"); mjonPayVO.setScreenYn("Y"); // 사용자화면 노출여부 mjonPayVO.setCmpltYn("Y"); // 잔액 전환 시 바로 처리 mjonPayService.insertPointUseAdm(mjonPayVO); } }catch(Exception e) { isSuccess = false; msg = e.getMessage(); } modelAndView.addObject("isSuccess", isSuccess); modelAndView.addObject("msg", msg); return modelAndView; } /** * 포인트 사용 완료처리 변경 OLD * @param searchVO * @param model * @return "/uss/ion/pay/pointUseCashUpdateOLD.do" * @throws Exception */ @RequestMapping(value = {"/uss/ion/pay/pointUseCashUpdateOLD.do"}) public String pointUseUpdateOLD( @RequestParam("chk") String[] chk, @ModelAttribute("searchVO") MjonPayVO mjonPayVO, HttpServletRequest request , RedirectAttributes redirectAttributes, Model model) throws Exception { try { LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); for(String id: chk) { mjonPayVO.setPointUseId(id); mjonPayService.updatePointUse(mjonPayVO); } redirectAttributes.addFlashAttribute("message", egovMessageSource.getMessage("success.common.update")); RedirectUrlMaker redirectUrlMaker = new RedirectUrlMaker("/uss/ion/pay/pointUseList.do"); return redirectUrlMaker.getRedirectUrl(); }catch(Exception e) { redirectAttributes.addFlashAttribute("message", egovMessageSource.getMessage("fail.common.update")); } redirectAttributes.addFlashAttribute("message", egovMessageSource.getMessage("success.common.update")); RedirectUrlMaker redirectUrlMaker = new RedirectUrlMaker("/uss/ion/pay/pointUseList.do"); return redirectUrlMaker.getRedirectUrl(); } /** * 포인트 사용 완료처리 변경 */ @RequestMapping(value="/uss/ion/pay/pointUseCashUpdateAjax.do") public ModelAndView addrRecvListAjax(@ModelAttribute("searchVO") MjonPayVO mjonPayVO, HttpServletRequest request) throws Exception { ModelAndView modelAndView = new ModelAndView(); modelAndView.setViewName("jsonView"); LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); String adminUserId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); boolean isSuccess = true; String msg = "전환불가 처리되었습니다."; try { mjonPayVO.setAdminRegisterId(adminUserId); mjonPayVO.setFrstRegisterId(mjonPayVO.getUserId()); mjonPayVO.setUserId(mjonPayVO.getUserId()); mjonPayService.updatePointUseByCase(mjonPayVO); //완료 처리 시 사용자에게 알림 문자 전송 if("Y".equals(mjonPayVO.getCmpltYn())){ msg = "송금완료 처리되었습니다."; MjonCommon mjCommon = new MjonCommon(); MjonMsgVO mjonMsgVO = new MjonMsgVO(); mjonMsgVO.setCallFrom("15518011"); //포인트 3자리수 콤마 처리 NumberFormat numberFormat = NumberFormat.getInstance(); String point = numberFormat.format(mjonPayVO.getPoint()); //발송 문자 타입(단/장문) 등록 //문자열 길이 체크 해주기 String charset = "euc-kr"; //문자 바이트 계산에 필요한 캐릭터 셋 : 한글 2Byte로 계산 String smsTxt = "[문자온] 고객님, 요청하신 포인트 현금 전환("+point+"원) 처리가 완료되었습니다. 감사합니다."; mjonMsgVO.setSmsTxt(smsTxt); smsTxt = smsTxt.replace("\r\n", "\n"); int FrBytes = smsTxt.getBytes(charset).length; String msgType = "4"; //90바이트를 초과하는 경우 장문으로 발송 if(FrBytes > 90){ msgType = "6"; } mjonMsgVO.setMsgType(msgType); //그림이미지 첨부파일 갯수 0으로 셋팅 mjonMsgVO.setFileCnt("0"); // 문자타입 구분 if (mjonMsgVO.getMsgType().equals("4")) { mjonMsgVO.setMsgDiv("S"); // 단문 } else if (mjonMsgVO.getMsgType().equals("6")) { if (mjonMsgVO.getFileCnt().equals("0")) { mjonMsgVO.setMsgDiv("L"); // 장문 } else { mjonMsgVO.setMsgDiv("P"); // 그림 } } // 문자타입별 대표전송사 정보 MjonMsgVO mjonMsgVO2 = new MjonMsgVO(); mjonMsgVO2 = mjonMsgService.selectRepMsgAgetnInfo(mjonMsgVO); // 전송사 구분 코드 - 01 : 아이하트, 02 : 현대 퓨쳐넷, 03 : 아이엠오, 04 : 다우기술 mjonMsgVO.setAgentCode(mjonMsgVO2.getAgentCode()); //전송사 선택 // 전송금액 mjonMsgVO.setTotPrice(mjonMsgVO2.getAgentPrice().toString()); //총금액 mjonMsgVO.setEachPrice(mjonMsgVO2.getAgentPrice().toString()); //한건 금액 mjonMsgVO.setCallTo(mjonPayVO.getMbtlNum()); //문자발송을 위한 데이터 셋팅 함수 호출해주기 MjonMsgVO resultCom = mjCommon.getAdminToMberPhoneSendMsgDataComm(mjonMsgVO); //발신으로 문자 발송 처리 mjonMsgDataService.insertAdmToMberMsgDataInfo(resultCom); msg = msg += (System.lineSeparator()+"안내문자 발송 성공했습니다."); } } catch(Exception e) { isSuccess = false; msg = e.getMessage(); } modelAndView.addObject("isSuccess", isSuccess); modelAndView.addObject("msg", msg); return modelAndView; } /** * 요금사용내역 - > 사용내역서 팝업 * @param mjonMsgVO * @param model * @return "/web/member/pay/printMsgUsageDetailsAjax.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/printMsgUsageDetailsAjax.do"}) public String printMsgUsageDetailsAjax(@ModelAttribute("searchVO") MjonMsgVO mjonMsgVO, ModelMap model) throws Exception{ LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgVO.setUserId(userId); int sendSumCount = 0; double supplySumPrice = 0.0; double vatSumPrice = 0.0; double totalSumPrice = 0.0; Date minRegDate = null; Date maxRegDate = null; int i = 0; String pageType = mjonMsgVO.getPageType(); SimpleDateFormat transFormat = new SimpleDateFormat("yyyy-MM-dd"); List<MjonMsgVO> payUserSumList = new ArrayList<MjonMsgVO>(); if(!pageType.equals("fax")) { payUserSumList = mjonMsgDataService.selectPayUserSumList(mjonMsgVO); for(MjonMsgVO item : payUserSumList) { sendSumCount = sendSumCount + (item.getShtSendCount() + item.getLongSendCount() + item.getPictSendCount() + item.getAtSendCount() + item.getFtSendCount() + item.getFaxSendCount()); supplySumPrice += item.getSupplyPrice(); vatSumPrice += item.getVatPrice(); totalSumPrice += item.getTotalPrice(); // 시작일, 종료일 if (i == 0) { minRegDate = transFormat.parse(item.getMinRegDate()); maxRegDate = transFormat.parse(item.getMaxRegDate()); } else { if(minRegDate.after(transFormat.parse(item.getMinRegDate()))) { minRegDate = transFormat.parse(item.getMinRegDate()); } if(maxRegDate.before(transFormat.parse(item.getMaxRegDate()))) { maxRegDate = transFormat.parse(item.getMaxRegDate()); } } i++; } } //팩스 내용 불러오기 List<MjonMsgVO> payUserSumFaxList = new ArrayList<MjonMsgVO>(); if(pageType.equals("") || pageType.equals("fax")) { //팩스 정보 불러오기 payUserSumFaxList = mjonMsgDataService.selectPayUserSumFaxList(mjonMsgVO); //총 사용금액에 팩스 금액 합산해주기 for(MjonMsgVO item : payUserSumFaxList) { supplySumPrice += item.getSupplyPrice(); totalSumPrice += item.getTotalPrice(); } } model.addAttribute("payUserSumFaxList", payUserSumFaxList); // 현재 날짜 구하기 (시스템 시계, 시스템 타임존) LocalDate now = LocalDate.now(); int year = now.getYear(); int month = now.getMonthValue(); int day = now.getDayOfMonth(); String monthStr = now.getMonthValue() > 9 ? "" + now.getMonthValue() : "0" + now.getMonthValue(); int lastDay = 0; Calendar cal = Calendar.getInstance(); cal.set(year, month-1, day); lastDay = cal.getActualMaximum(Calendar.DAY_OF_MONTH); model.addAttribute("year", year); model.addAttribute("month", month); model.addAttribute("day", day); model.addAttribute("monthStr", monthStr); model.addAttribute("lastDay", lastDay); String startDate = mjonMsgVO.getStartDate(); if(!"".equals(startDate)) { startDate = startDate.replaceAll("/", "-"); }else { if(minRegDate != null) { startDate = transFormat.format(minRegDate); } } model.addAttribute("startDate", startDate); String endDate = mjonMsgVO.getEndDate(); if(!"".equals(endDate)) { endDate = endDate.replaceAll("/", "-"); }else { if(maxRegDate != null) { endDate = transFormat.format(maxRegDate); } } model.addAttribute("endDate", endDate); DecimalFormat decFormat = new DecimalFormat("###,###"); model.addAttribute("sendSumCount", decFormat.format(sendSumCount)); model.addAttribute("supplySumPrice", decFormat.format(supplySumPrice)); model.addAttribute("vatSumPrice", decFormat.format(vatSumPrice)); model.addAttribute("totalSumPrice", decFormat.format(totalSumPrice)); model.addAttribute("resultList", payUserSumList); // 수신자 정보 String mberNm = ""; // 회사명 String managerNm = ""; // 담당자명 String moblphonNo = ""; // 연락처 MberManageVO mberManageVO = new MberManageVO(); mberManageVO = mjonMsgDataService.selectMberManageInfo(userId); if (mberManageVO != null) { // 회원종류 p-개인 c-기업 if (mberManageVO.getDept().equals("c")) { mberNm = mberManageVO.getMberNm(); managerNm = mberManageVO.getManagerNm(); moblphonNo = mberManageVO.getMoblphonNo(); } else { mberNm = mberManageVO.getMberNm(); managerNm = mberManageVO.getMberNm(); moblphonNo = mberManageVO.getMoblphonNo(); } } model.addAttribute("mberNm", mberNm); model.addAttribute("managerNm", managerNm); model.addAttribute("moblphonNo", moblphonNo); //부가세 포함 가격 정보 계산 double addTax = Math.round(totalSumPrice * 0.1); model.addAttribute("addTax", decFormat.format(addTax)); model.addAttribute("addTaxSumPrice", decFormat.format(addTax+totalSumPrice)); model.addAttribute("pageType", mjonMsgVO.getPageType()); return "/web/pay/MsgPrintUsageDetailsPopUp"; } /** * 요금사용내역 - > 사용내역서 팝업 카카오 추가 * @param mjonMsgVO * @param model * @return "/web/member/pay/printMsgUsageDetailsAjax.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/printMsgWithKakaoUsageDetailsAjax.do"}) public String printMsgWithKakaoUsageDetailsAjax(@ModelAttribute("searchVO") MjonMsgVO mjonMsgVO, ModelMap model) throws Exception{ LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgVO.setUserId(userId); int sendSumCount = 0; double supplySumPrice = 0.0; double vatSumPrice = 0.0; double totalSumPrice = 0.0; Date minRegDate = null; Date maxRegDate = null; int i = 0; SimpleDateFormat transFormat = new SimpleDateFormat("yyyy-MM-dd"); List<MjonMsgVO> payUserSumList = mjonMsgDataService.selectPayUserWithKakaoSumList(mjonMsgVO); for(MjonMsgVO item : payUserSumList) { sendSumCount += item.getSendCount(); supplySumPrice += item.getSupplyPrice(); vatSumPrice += item.getVatPrice(); totalSumPrice += item.getTotalPrice(); // 시작일, 종료일 if (i == 0) { minRegDate = transFormat.parse(item.getMinRegDate()); maxRegDate = transFormat.parse(item.getMaxRegDate()); } else { if(minRegDate.after(transFormat.parse(item.getMinRegDate()))) { minRegDate = transFormat.parse(item.getMinRegDate()); } if(maxRegDate.before(transFormat.parse(item.getMaxRegDate()))) { maxRegDate = transFormat.parse(item.getMaxRegDate()); } } i++; } // 현재 날짜 구하기 (시스템 시계, 시스템 타임존) LocalDate now = LocalDate.now(); int year = now.getYear(); int month = now.getMonthValue(); int day = now.getDayOfMonth(); String monthStr = now.getMonthValue() > 9 ? "" + now.getMonthValue() : "0" + now.getMonthValue(); int lastDay = 0; Calendar cal = Calendar.getInstance(); cal.set(year, month-1, day); lastDay = cal.getActualMaximum(Calendar.DAY_OF_MONTH); model.addAttribute("year", year); model.addAttribute("month", month); model.addAttribute("day", day); model.addAttribute("monthStr", monthStr); model.addAttribute("lastDay", lastDay); String startDate = mjonMsgVO.getStartDate(); if(!"".equals(startDate)) { startDate = startDate.replaceAll("/", "-"); }else { startDate = transFormat.format(minRegDate); } model.addAttribute("startDate", startDate); String endDate = mjonMsgVO.getEndDate(); if(!"".equals(endDate)) { endDate = endDate.replaceAll("/", "-"); }else { endDate = transFormat.format(maxRegDate); } model.addAttribute("endDate", endDate); DecimalFormat decFormat = new DecimalFormat("###,###"); model.addAttribute("sendSumCount", decFormat.format(sendSumCount)); model.addAttribute("supplySumPrice", decFormat.format(supplySumPrice)); model.addAttribute("vatSumPrice", decFormat.format(vatSumPrice)); model.addAttribute("totalSumPrice", decFormat.format(totalSumPrice)); model.addAttribute("resultList", payUserSumList); // 수신자 정보 String mberNm = ""; // 회사명 String managerNm = ""; // 담당자명 String moblphonNo = ""; // 연락처 MberManageVO mberManageVO = new MberManageVO(); mberManageVO = mjonMsgDataService.selectMberManageInfo(userId); if (mberManageVO != null) { // 회원종류 p-개인 c-기업 if (mberManageVO.getDept().equals("c")) { mberNm = mberManageVO.getMberNm(); managerNm = mberManageVO.getManagerNm(); moblphonNo = mberManageVO.getMoblphonNo(); } else { mberNm = mberManageVO.getMberNm(); managerNm = mberManageVO.getMberNm(); moblphonNo = mberManageVO.getMoblphonNo(); } } model.addAttribute("mberNm", mberNm); model.addAttribute("managerNm", managerNm); model.addAttribute("moblphonNo", moblphonNo); return "/web/pay/MsgPrintUsageDetailsPopUp"; } /** * 요금사용내역 - > 거레명세서 팝업 * @param mjonMsgVO * @param model * @return "/web/member/pay/printMsgStatementAjax.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/printMsgStatementAjax.do"}) public String printMsgStatementAjax(@ModelAttribute("searchVO") MjonMsgVO mjonMsgVO, ModelMap model) throws Exception{ LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgVO.setUserId(userId); int sendSumCount = 0; double supplySumPrice = 0.0; double vatSumPrice = 0.0; double totalSumPrice = 0.0; Date minRegDate = null; Date maxRegDate = null; int i = 0; String pageType = mjonMsgVO.getPageType(); SimpleDateFormat transFormat = new SimpleDateFormat("yyyy-MM-dd"); List<MjonMsgVO> payUserSumList = new ArrayList<MjonMsgVO>(); if(!pageType.equals("fax")) { payUserSumList = mjonMsgDataService.selectPayUserSumList(mjonMsgVO); for(MjonMsgVO item : payUserSumList) { sendSumCount = sendSumCount + (item.getShtSendCount() + item.getLongSendCount() + item.getPictSendCount() + item.getAtSendCount() + item.getFtSendCount()); supplySumPrice += item.getSupplyPrice(); vatSumPrice += item.getVatPrice(); totalSumPrice += item.getTotalPrice(); // 시작일, 종료일 if (i == 0) { minRegDate = transFormat.parse(item.getMinRegDate()); maxRegDate = transFormat.parse(item.getMaxRegDate()); } else { if(minRegDate.after(transFormat.parse(item.getMinRegDate()))) { minRegDate = transFormat.parse(item.getMinRegDate()); } if(maxRegDate.before(transFormat.parse(item.getMaxRegDate()))) { maxRegDate = transFormat.parse(item.getMaxRegDate()); } } i++; } } //팩스 내용 불러오기 List<MjonMsgVO> payUserSumFaxList = new ArrayList<MjonMsgVO>(); if(pageType.equals("") || pageType.equals("fax")) { //팩스 정보 불러오기 payUserSumFaxList = mjonMsgDataService.selectPayUserSumFaxList(mjonMsgVO); //총 사용금액에 팩스 금액 합산해주기 for(MjonMsgVO item : payUserSumFaxList) { supplySumPrice += item.getSupplyPrice(); totalSumPrice += item.getTotalPrice(); } } model.addAttribute("payUserSumFaxList", payUserSumFaxList); // 현재 날짜 구하기 (시스템 시계, 시스템 타임존) LocalDate now = LocalDate.now(); int year = now.getYear(); int month = now.getMonthValue(); int day = now.getDayOfMonth(); String monthStr = now.getMonthValue() > 9 ? "" + now.getMonthValue() : "0" + now.getMonthValue(); int lastDay = 0; Calendar cal = Calendar.getInstance(); cal.set(year, month-1, day); lastDay = cal.getActualMaximum(Calendar.DAY_OF_MONTH); model.addAttribute("year", year); model.addAttribute("month", month); model.addAttribute("day", day); model.addAttribute("monthStr", monthStr); model.addAttribute("lastDay", lastDay); String startDate = mjonMsgVO.getStartDate(); if(!"".equals(startDate)) { startDate = startDate.replaceAll("/", "-"); }else { if(minRegDate != null) { startDate = transFormat.format(minRegDate); } } model.addAttribute("startDate", startDate); String endDate = mjonMsgVO.getEndDate(); if(!"".equals(endDate)) { endDate = endDate.replaceAll("/", "-"); }else { if(maxRegDate != null) { endDate = transFormat.format(maxRegDate); } } model.addAttribute("endDate", endDate); DecimalFormat decFormat = new DecimalFormat("###,###"); model.addAttribute("sendSumCount", decFormat.format(sendSumCount)); model.addAttribute("supplySumPrice", decFormat.format(supplySumPrice)); model.addAttribute("vatSumPrice", decFormat.format(vatSumPrice)); model.addAttribute("totalSumPrice", decFormat.format(totalSumPrice)); model.addAttribute("resultList", payUserSumList); // 수신자 정보 String mberNm = ""; // 회사명 String managerNm = ""; // 담당자명 String moblphonNo = ""; // 연락처 MberManageVO mberManageVO = new MberManageVO(); mberManageVO = mjonMsgDataService.selectMberManageInfo(userId); if (mberManageVO != null) { // 회원종류 p-개인 c-기업 if (mberManageVO.getDept().equals("c")) { mberNm = mberManageVO.getMberNm(); managerNm = mberManageVO.getManagerNm(); moblphonNo = mberManageVO.getMoblphonNo(); } else { mberNm = mberManageVO.getMberNm(); managerNm = mberManageVO.getMberNm(); moblphonNo = mberManageVO.getMoblphonNo(); } } model.addAttribute("mberNm", mberNm); model.addAttribute("managerNm", managerNm); model.addAttribute("moblphonNo", moblphonNo); //부가세 포함 가격 정보 계산 double addTax = Math.round(totalSumPrice * 0.1); model.addAttribute("addTax", decFormat.format(addTax)); model.addAttribute("addTaxSumPrice", decFormat.format(addTax+totalSumPrice)); model.addAttribute("pageType", mjonMsgVO.getPageType()); return "/web/pay/MsgPrintStatementPopUp"; } /** * 요금사용내역 - > 거레명세서 팝업(카카오 추가) * @param mjonMsgVO * @param model * @return "/web/member/pay/printMsgWithKakaoStatementAjax.do" * @throws Exception */ @RequestMapping(value= {"/web/member/pay/printMsgWithKakaoStatementAjax.do"}) public String printMsgWithKakaoStatementAjax(@ModelAttribute("searchVO") MjonMsgVO mjonMsgVO, ModelMap model) throws Exception{ LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgVO.setUserId(userId); int sendSumCount = 0; double supplySumPrice = 0.0; double vatSumPrice = 0.0; double totalSumPrice = 0.0; Date minRegDate = null; Date maxRegDate = null; int i = 0; SimpleDateFormat transFormat = new SimpleDateFormat("yyyy-MM-dd"); List<MjonMsgVO> payUserSumList = mjonMsgDataService.selectPayUserWithKakaoSumList(mjonMsgVO); for(MjonMsgVO item : payUserSumList) { sendSumCount += item.getSendCount(); supplySumPrice += item.getSupplyPrice(); vatSumPrice += item.getVatPrice(); totalSumPrice += item.getTotalPrice(); // 시작일, 종료일 if (i == 0) { minRegDate = transFormat.parse(item.getMinRegDate()); maxRegDate = transFormat.parse(item.getMaxRegDate()); } else { if(minRegDate.after(transFormat.parse(item.getMinRegDate()))) { minRegDate = transFormat.parse(item.getMinRegDate()); } if(maxRegDate.before(transFormat.parse(item.getMaxRegDate()))) { maxRegDate = transFormat.parse(item.getMaxRegDate()); } } i++; } // 현재 날짜 구하기 (시스템 시계, 시스템 타임존) LocalDate now = LocalDate.now(); int year = now.getYear(); int month = now.getMonthValue(); int day = now.getDayOfMonth(); String monthStr = now.getMonthValue() > 9 ? "" + now.getMonthValue() : "0" + now.getMonthValue(); int lastDay = 0; Calendar cal = Calendar.getInstance(); cal.set(year, month-1, day); lastDay = cal.getActualMaximum(Calendar.DAY_OF_MONTH); model.addAttribute("year", year); model.addAttribute("month", month); model.addAttribute("day", day); model.addAttribute("monthStr", monthStr); model.addAttribute("lastDay", lastDay); String startDate = mjonMsgVO.getStartDate(); if(!"".equals(startDate)) { startDate = startDate.replaceAll("/", "-"); }else { startDate = transFormat.format(minRegDate); } model.addAttribute("startDate", startDate); String endDate = mjonMsgVO.getEndDate(); if(!"".equals(endDate)) { endDate = endDate.replaceAll("/", "-"); }else { endDate = transFormat.format(maxRegDate); } model.addAttribute("endDate", endDate); DecimalFormat decFormat = new DecimalFormat("###,###"); model.addAttribute("sendSumCount", decFormat.format(sendSumCount)); model.addAttribute("supplySumPrice", decFormat.format(supplySumPrice)); model.addAttribute("vatSumPrice", decFormat.format(vatSumPrice)); model.addAttribute("totalSumPrice", decFormat.format(totalSumPrice)); model.addAttribute("resultList", payUserSumList); // 수신자 정보 String mberNm = ""; // 회사명 String managerNm = ""; // 담당자명 String moblphonNo = ""; // 연락처 MberManageVO mberManageVO = new MberManageVO(); mberManageVO = mjonMsgDataService.selectMberManageInfo(userId); if (mberManageVO != null) { // 회원종류 p-개인 c-기업 if (mberManageVO.getDept().equals("c")) { mberNm = mberManageVO.getMberNm(); managerNm = mberManageVO.getManagerNm(); moblphonNo = mberManageVO.getMoblphonNo(); } else { mberNm = mberManageVO.getMberNm(); managerNm = mberManageVO.getMberNm(); moblphonNo = mberManageVO.getMoblphonNo(); } } model.addAttribute("mberNm", mberNm); model.addAttribute("managerNm", managerNm); model.addAttribute("moblphonNo", moblphonNo); return "/web/pay/MsgPrintStatementPopUp"; } /** * 일별 결제 통계 * @param searchVO * @param model * @return "/uss/ion/pay/payDayChart" * @throws Exception */ @RequestMapping(value= {"/uss/ion/pay/payDayChart.do"}) public String selectPayDayChart(@ModelAttribute("searchVO") MjonPayVO mjonPayVO, HttpServletRequest request, ModelMap model) throws Exception{ String pattern = (String) request.getAttribute( HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; //오늘 Date today = new Date(); SimpleDateFormat date = new SimpleDateFormat("yyyy-MM-dd"); String toDay = date.format(today); //한달 전 Calendar mon = Calendar.getInstance(); mon.add(Calendar.MONTH , -1); String beforeMonthDay = new java.text.SimpleDateFormat("yyyy-MM-dd").format(mon.getTime()); if (null == mjonPayVO.getNtceBgnde() || mjonPayVO.getNtceBgnde().equals("")) { mjonPayVO.setNtceBgnde(beforeMonthDay); mjonPayVO.setNtceEndde(toDay); } /** pageing */ PaginationInfo paginationInfo = new PaginationInfo(); paginationInfo.setCurrentPageNo(mjonPayVO.getPageIndex()); paginationInfo.setRecordCountPerPage(mjonPayVO.getPageUnit()); paginationInfo.setPageSize(mjonPayVO.getPageSize()); mjonPayVO.setFirstIndex(paginationInfo.getFirstRecordIndex()); mjonPayVO.setLastIndex(paginationInfo.getLastRecordIndex()); mjonPayVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage()); if("".equals(mjonPayVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonPayVO.setSearchSortCnd("regDate"); mjonPayVO.setSearchSortOrd("asc"); } List<MjonPayVO> resultList = mjonPayService.selectPayDayChart(mjonPayVO); // Total List mjonPayVO.setFirstIndex(0); mjonPayVO.setRecordCountPerPage(10000); if("".equals(mjonPayVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonPayVO.setSearchSortCnd("regDate"); mjonPayVO.setSearchSortOrd("asc"); } List<MjonPayVO> resultList2 = mjonPayService.selectPayDayChart(mjonPayVO); // Total long amtCardSumTot = 0; //전체 신용카드 결제 금액 long amtSpaySumTot = 0; //전체 간편결제 결제 금액 long amtCellSumTot = 0; //전체 휴대폰 결제 금액 long amtBankSumTot = 0; //전체 즉시이체 결제 금액 long amtVbankSumTot = 0; //전체 전용계좌 결제 금액 long amtAfterPaySumTot = 0; //전체 전용계좌 결제 금액 long amtRefundSumTot = 0; //전체 환불 금액 long amtTotSumTot = 0; //전체 결제 금액 총액 for(MjonPayVO item: resultList2) { amtCardSumTot += Long.parseLong(item.getAmtCardSum()); amtSpaySumTot += Long.parseLong(item.getAmtSpaySum()); amtCellSumTot += Long.parseLong(item.getAmtCellSum()); amtBankSumTot += Long.parseLong(item.getAmtBankSum()); amtVbankSumTot += Long.parseLong(item.getAmtVbankSum()); amtAfterPaySumTot += Long.parseLong(item.getAmtAfterPaySum()); amtRefundSumTot += Long.parseLong(item.getAmtRefundSum()); amtTotSumTot += Long.parseLong(item.getAmtTotSum()); } mjonPayVO.setAmtCardSumTot(amtCardSumTot + ""); mjonPayVO.setAmtSpaySumTot(amtSpaySumTot + ""); mjonPayVO.setAmtCellSumTot(amtCellSumTot + ""); mjonPayVO.setAmtBankSumTot(amtBankSumTot + ""); mjonPayVO.setAmtVbankSumTot(amtVbankSumTot + ""); mjonPayVO.setAmtAfterPaySumTot(amtAfterPaySumTot + ""); mjonPayVO.setAmtRefundSumTot(amtRefundSumTot + ""); mjonPayVO.setAmtTotSumTot(amtTotSumTot + ""); model.addAttribute("mjonPayVO", mjonPayVO); model.addAttribute("resultList", resultList); paginationInfo.setTotalRecordCount(resultList.size() > 0 ? ((MjonPayVO)resultList.get(0)).getTotCnt() : 0); model.addAttribute("paginationInfo", paginationInfo); return "/uss/ion/pay/PayDayChart"; } /* * 일별 매출 통계 엑셀 다운로드 * * */ @RequestMapping(value= {"/uss/ion/pay/payDayChartExcelDownload.do"}) public void payDayChartExcelDownload( MjonPayVO mjonPayVO, HttpServletRequest request, HttpServletResponse response , ModelMap model) throws Exception { mjonPayVO.setSearchSortCnd("regDate"); mjonPayVO.setSearchSortOrd("asc"); mjonPayVO.setRecordCountPerPage(mjonPayVO.getPageUnit()); mjonPayVO.setFirstIndex(0); LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); if(null != loginVO && !"super".equals(loginVO.getSiteId())){ mjonPayVO.setSiteId(loginVO.getSiteId()); } // 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다. SXSSFWorkbook wb = new SXSSFWorkbook(100); CellStyle style = wb.createCellStyle(); style.setBorderBottom(CellStyle.BORDER_THIN); //테두리 두껍게 style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderTop(CellStyle.BORDER_THIN); Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); //글씨 bold Cell cell = null; Row row = null; String fileName ="일별 매출 통계"; String sheetTitle = ""; try{ List<MjonPayVO> resultList = mjonPayService.selectPayDayChart(mjonPayVO); { //화면 리스트 sheetTitle = "일별 매출 통계" ; //제목 Sheet sheet = wb.createSheet(sheetTitle); row = sheet.createRow(0); cell = row.createCell(0); cell.setCellValue("결제일"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("신용카드"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("휴대폰"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("즉시이체"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("전용계좌"); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("간편결제"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("후불결제"); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("환불"); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue("합계(VAT포함)"); cell.setCellStyle(style); long amtCardSumTot = 0; //전체 신용카드 결제 금액 long amtCellSumTot = 0; //전체 휴대폰 결제 금액 long amtBankSumTot = 0; //전체 즉시이체 결제 금액 long amtVbankSumTot = 0; //전체 전용계좌 결제 금액 long amtSpaySumTot = 0; //전체 간편결제 결제 금액 long amtAfterPaySumTot = 0; //전체 후불결제 금액 long amtRefundSumTot = 0; //전체 환불 금액 long amtTotSumTot = 0; //전체 결제 금액 총액 for(int i=0; i < resultList.size(); i++){ row = sheet.createRow(i+1); for(int j=0 ; j < 9 ; j++) { cell = row.createCell(j); cell.setCellStyle(style); String regDate = (resultList.get(i)).getRegDate(); String amtCardSum = (resultList.get(i)).getAmtCardSum(); String amtCellSum = (resultList.get(i)).getAmtCellSum(); String amtBankSum = (resultList.get(i)).getAmtBankSum(); String amtVbankSum = (resultList.get(i)).getAmtVbankSum(); String amtSpaySum = (resultList.get(i)).getAmtSpaySum(); String amtAfterPaySum = (resultList.get(i)).getAmtAfterPaySum(); String amtRefundSum = (resultList.get(i)).getAmtRefundSum(); String amtTotSum = (resultList.get(i)).getAmtTotSum(); if(j==0) cell.setCellValue(regDate); //결제월 if(j==1) { cell.setCellValue(amtCardSum); //신용카드 amtCardSumTot += Long.parseLong(amtCardSum); } if(j==2) { cell.setCellValue(amtCellSum); //휴대폰 amtCellSumTot += Long.parseLong(amtCellSum); } if(j==3) { cell.setCellValue(amtBankSum); //즉시이체 amtBankSumTot += Long.parseLong(amtBankSum); } if(j==4) { cell.setCellValue(amtVbankSum); //전용계좌 amtVbankSumTot += Long.parseLong(amtVbankSum); } if(j==5) { cell.setCellValue(amtSpaySum); //간편결제 amtSpaySumTot += Long.parseLong(amtSpaySum); } if(j==6) { cell.setCellValue(amtAfterPaySum); //후불결제 amtAfterPaySumTot += Long.parseLong(amtAfterPaySum); } if(j==7) { cell.setCellValue(amtRefundSum); //환불 amtRefundSumTot += Long.parseLong(amtRefundSum); } if(j==8) { cell.setCellValue(amtTotSum); //합계(VAT포함) amtTotSumTot += Long.parseLong(amtTotSum); } } } //전체 합계 행 추가 row = sheet.createRow(resultList.size()+1); cell = row.createCell(0); cell.setCellStyle(style); cell.setCellValue("합계"); //결제월 cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue(amtCardSumTot); //신용카드 cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue(amtCellSumTot); //휴대폰 cell = row.createCell(3); cell.setCellStyle(style); cell.setCellValue(amtBankSumTot); //즉시이체 cell = row.createCell(4); cell.setCellStyle(style); cell.setCellValue(amtVbankSumTot); //전용계좌 cell = row.createCell(5); cell.setCellStyle(style); cell.setCellValue(amtSpaySumTot); //간편결제 cell = row.createCell(6); cell.setCellStyle(style); cell.setCellValue(amtAfterPaySumTot); //후불결제 cell = row.createCell(7); cell.setCellStyle(style); cell.setCellValue(amtRefundSumTot); //환불 cell = row.createCell(8); cell.setCellStyle(style); cell.setCellValue(amtTotSumTot); //합계(VAT포함) } response.setHeader("Set-Cookie", "fileDownload=true; path=/"); SimpleDateFormat mSimpleDateFormat = new SimpleDateFormat ( "yyyy_MM_dd_HH_mm_ss", Locale.KOREA ); Date currentTime = new Date (); String mTime = mSimpleDateFormat.format ( currentTime ); fileName = fileName+"("+mTime+")"; response.setHeader("Content-Disposition", String.format("attachment; filename=\""+new String((fileName).getBytes("KSC5601"),"8859_1")+".xlsx")); wb.write(response.getOutputStream()); }catch(Exception e) { response.setHeader("Set-Cookie", "fileDownload=false; path=/"); response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate"); response.setHeader("Content-Type","text/html; charset=utf-8"); OutputStream out = null; try { out = response.getOutputStream(); byte[] data = new String("fail..").getBytes(); out.write(data, 0, data.length); } catch(Exception ignore) { ignore.printStackTrace(); } finally { if(out != null) try { out.close(); } catch(Exception ignore) {} if(wb != null) try { wb.dispose(); wb.close(); } catch(Exception ignore) {} } }finally { // 디스크 적었던 임시파일을 제거합니다. wb.dispose(); try { wb.close(); } catch(Exception ignore) {} } } /** * 월별 매출 통계 * @param searchVO * @param model * @return "/uss/ion/pay/payDayChart" * @throws Exception */ @RequestMapping(value= {"/uss/ion/pay/payMonthChart.do"}) public String selectPayMonthChart(@ModelAttribute("searchVO") MjonPayVO mjonPayVO, HttpServletRequest request, ModelMap model) throws Exception{ String pattern = (String) request.getAttribute( HandlerMapping.BEST_MATCHING_PATTERN_ATTRIBUTE) ; /** pageing */ /* PaginationInfo paginationInfo = new PaginationInfo(); paginationInfo.setCurrentPageNo(mjonPayVO.getPageIndex()); paginationInfo.setRecordCountPerPage(mjonPayVO.getPageUnit()); paginationInfo.setPageSize(mjonPayVO.getPageSize()); mjonPayVO.setFirstIndex(paginationInfo.getFirstRecordIndex()); mjonPayVO.setLastIndex(paginationInfo.getLastRecordIndex()); mjonPayVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage());*/ if("".equals(mjonPayVO.getNtceBgnde()) || mjonPayVO.getNtceBgnde() == null){ //최초조회시 현재년도 조회 LocalDate now = LocalDate.now(); int year = now.getYear(); mjonPayVO.setNtceBgnde(Integer.toString(year)); } if("".equals(mjonPayVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonPayVO.setSearchSortCnd("regDate"); mjonPayVO.setSearchSortOrd("asc"); } mjonPayVO.setFirstIndex(0); mjonPayVO.setRecordCountPerPage(100); List<MjonPayVO> resultList = mjonPayService.selectPayMonthChart(mjonPayVO); // Total long amtCardSumTot = 0; //전체 신용카드 결제 금액 long amtSpaySumTot = 0; //전체 간편결제 결제 금액 long amtCellSumTot = 0; //전체 휴대폰 결제 금액 long amtBankSumTot = 0; //전체 즉시이체 결제 금액 long amtVbankSumTot = 0; //전체 전용계좌 결제 금액 long amtAfterPaySumTot = 0; //전체 후불결제 금액 long amtRefundSumTot = 0; //전체 환불 금액 long amtTotSumTot = 0; //전체 결제 금액 총액 for(MjonPayVO item: resultList) { amtCardSumTot += Long.parseLong(item.getAmtCardSum()); amtSpaySumTot += Long.parseLong(item.getAmtSpaySum()); amtCellSumTot += Long.parseLong(item.getAmtCellSum()); amtBankSumTot += Long.parseLong(item.getAmtBankSum()); amtVbankSumTot += Long.parseLong(item.getAmtVbankSum()); amtAfterPaySumTot += Long.parseLong(item.getAmtAfterPaySum()); amtRefundSumTot += Long.parseLong(item.getAmtRefundSum()); amtTotSumTot += Long.parseLong(item.getAmtTotSum()); } mjonPayVO.setAmtCardSumTot(amtCardSumTot + ""); mjonPayVO.setAmtSpaySumTot(amtSpaySumTot + ""); mjonPayVO.setAmtCellSumTot(amtCellSumTot + ""); mjonPayVO.setAmtBankSumTot(amtBankSumTot + ""); mjonPayVO.setAmtVbankSumTot(amtVbankSumTot + ""); mjonPayVO.setAmtAfterPaySumTot(amtAfterPaySumTot + ""); mjonPayVO.setAmtRefundSumTot(amtRefundSumTot + ""); mjonPayVO.setAmtTotSumTot(amtTotSumTot + ""); model.addAttribute("mjonPayVO", mjonPayVO); model.addAttribute("resultList", resultList); PaginationInfo paginationInfo = new PaginationInfo(); paginationInfo.setTotalRecordCount(resultList.size() > 0 ? ((MjonPayVO)resultList.get(0)).getTotCnt() : 0); model.addAttribute("paginationInfo", paginationInfo); return "/uss/ion/pay/PayMonthChart"; } /* * 월별 매출 통계 엑셀 다운로드 * * */ @RequestMapping(value= {"/uss/ion/pay/payMonthChartExcelDownload.do"}) public void payMonthChartExcelDownload( MjonPayVO mjonPayVO, HttpServletRequest request, HttpServletResponse response , ModelMap model) throws Exception { mjonPayVO.setSearchSortCnd("regDate"); mjonPayVO.setSearchSortOrd("asc"); mjonPayVO.setRecordCountPerPage(20); mjonPayVO.setFirstIndex(0); LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); if(null != loginVO && !"super".equals(loginVO.getSiteId())){ mjonPayVO.setSiteId(loginVO.getSiteId()); } // 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다. SXSSFWorkbook wb = new SXSSFWorkbook(100); CellStyle style = wb.createCellStyle(); style.setBorderBottom(CellStyle.BORDER_THIN); //테두리 두껍게 style.setBorderLeft(CellStyle.BORDER_THIN); style.setBorderRight(CellStyle.BORDER_THIN); style.setBorderTop(CellStyle.BORDER_THIN); Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); //글씨 bold Cell cell = null; Row row = null; String fileName ="월별 매출 통계"; String sheetTitle = ""; try{ List<MjonPayVO> resultList = mjonPayService.selectPayMonthChart(mjonPayVO); { //화면 리스트 sheetTitle = "월별 매출 통계" ; //제목 Sheet sheet = wb.createSheet(sheetTitle); row = sheet.createRow(0); cell = row.createCell(0); cell.setCellValue("결제월"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("신용카드"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("휴대폰"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("즉시이체"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("전용계좌"); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("간편결제"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("후불결제"); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("환불"); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue("합계(VAT포함)"); cell.setCellStyle(style); long amtCardSumTot = 0; //전체 신용카드 결제 금액 long amtCellSumTot = 0; //전체 휴대폰 결제 금액 long amtBankSumTot = 0; //전체 즉시이체 결제 금액 long amtVbankSumTot = 0; //전체 전용계좌 결제 금액 long amtSpaySumTot = 0; //전체 간편결제 결제 금액 long amtAfterPaySumTot = 0; //전체 후불결제 금액 long amtRefundSumTot = 0; //전체 환불 금액 long amtTotSumTot = 0; //전체 결제 금액 총액 for(int i=0; i < resultList.size(); i++){ row = sheet.createRow(i+1); for(int j=0 ; j < 9 ; j++) { cell = row.createCell(j); cell.setCellStyle(style); String regDate = (resultList.get(i)).getRegDate(); String amtCardSum = (resultList.get(i)).getAmtCardSum(); String amtCellSum = (resultList.get(i)).getAmtCellSum(); String amtBankSum = (resultList.get(i)).getAmtBankSum(); String amtVbankSum = (resultList.get(i)).getAmtVbankSum(); String amtSpaySum = (resultList.get(i)).getAmtSpaySum(); String amtAfterPaySum = (resultList.get(i)).getAmtAfterPaySum(); String amtRefundSum = (resultList.get(i)).getAmtRefundSum(); String amtTotSum = (resultList.get(i)).getAmtTotSum(); if(j==0) cell.setCellValue(regDate); //결제월 if(j==1) { cell.setCellValue(amtCardSum); //신용카드 amtCardSumTot += Long.parseLong(amtCardSum); } if(j==2) { cell.setCellValue(amtCellSum); //휴대폰 amtCellSumTot += Long.parseLong(amtCellSum); } if(j==3) { cell.setCellValue(amtBankSum); //즉시이체 amtBankSumTot += Long.parseLong(amtBankSum); } if(j==4) { cell.setCellValue(amtVbankSum); //전용계좌 amtVbankSumTot += Long.parseLong(amtVbankSum); } if(j==5) { cell.setCellValue(amtSpaySum); //간편결제 amtSpaySumTot += Long.parseLong(amtSpaySum); } if(j==6) { cell.setCellValue(amtAfterPaySum); //후불결제 amtAfterPaySumTot += Long.parseLong(amtAfterPaySum); } if(j==7) { cell.setCellValue(amtRefundSum); //환불 amtRefundSumTot += Long.parseLong(amtRefundSum); } if(j==8) { cell.setCellValue(amtTotSum); //합계(VAT포함) amtTotSumTot += Long.parseLong(amtTotSum); } } } //총합계 행 추가 row = sheet.createRow(resultList.size()+1); cell = row.createCell(0); cell.setCellStyle(style); cell.setCellValue("합계"); //결제월 cell = row.createCell(1); cell.setCellStyle(style); cell.setCellValue(amtCardSumTot); //신용카드 cell = row.createCell(2); cell.setCellStyle(style); cell.setCellValue(amtCellSumTot); //휴대폰 cell = row.createCell(3); cell.setCellStyle(style); cell.setCellValue(amtBankSumTot); //즉시이체 cell = row.createCell(4); cell.setCellStyle(style); cell.setCellValue(amtVbankSumTot); //전용계좌 cell = row.createCell(5); cell.setCellStyle(style); cell.setCellValue(amtSpaySumTot); //간편결제 cell = row.createCell(6); cell.setCellStyle(style); cell.setCellValue(amtAfterPaySumTot); //후불결제 cell = row.createCell(7); cell.setCellStyle(style); cell.setCellValue(amtRefundSumTot); //환불 cell = row.createCell(8); cell.setCellStyle(style); cell.setCellValue(amtTotSumTot); //합계(VAT포함) } response.setHeader("Set-Cookie", "fileDownload=true; path=/"); SimpleDateFormat mSimpleDateFormat = new SimpleDateFormat ( "yyyy_MM_dd_HH_mm_ss", Locale.KOREA ); Date currentTime = new Date (); String mTime = mSimpleDateFormat.format ( currentTime ); fileName = fileName+"("+mTime+")"; response.setHeader("Content-Disposition", String.format("attachment; filename=\""+new String((fileName).getBytes("KSC5601"),"8859_1")+".xlsx")); wb.write(response.getOutputStream()); }catch(Exception e) { response.setHeader("Set-Cookie", "fileDownload=false; path=/"); response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate"); response.setHeader("Content-Type","text/html; charset=utf-8"); OutputStream out = null; try { out = response.getOutputStream(); byte[] data = new String("fail..").getBytes(); out.write(data, 0, data.length); } catch(Exception ignore) { ignore.printStackTrace(); } finally { if(out != null) try { out.close(); } catch(Exception ignore) {} if(wb != null) try { wb.dispose(); wb.close(); } catch(Exception ignore) {} } }finally { // 디스크 적었던 임시파일을 제거합니다. wb.dispose(); try { wb.close(); } catch(Exception ignore) {} } } /* @RequestMapping(value= {"/web/main/vacTest.do"}) public void vacTest() throws Exception { schdlrManageService.msgFailPayBack(); }*/ /** * * 캐시 , 포인트 이전 등록화면 * @param searchVO * @param model * @return "/uss/ion/pay/cashPointSendInsertView.do" * @throws Exception */ @RequestMapping(value = {"/uss/ion/pay/cashPointSendInsertView.do"}) public String cashPointSendInsertView( @ModelAttribute("searchVO") MjonPayVO mjonPayVO) throws Exception { return "/uss/ion/pay/cashPointSendRegist"; } /** * * 캐시 , 포인트 이전 등록 시 ID 존재 여부 확인 ajax * @param searchVO * @param model * @return "/uss/ion/pay/cashPointSendIdCheckAjax.do" * @throws Exception */ @RequestMapping(value = {"/uss/ion/pay/cashPointSendIdCheckAjax.do"}) public ModelAndView cashPointSendIdCheckAjax( @ModelAttribute("searchVO") MjonPayVO mjonPayVO) throws Exception { ModelAndView modelAndView = new ModelAndView(); modelAndView.setViewName("jsonView"); modelAndView.addObject("result", "success"); //받는, 보내는 아이디 존재하는지 체크 int recvIdCnt = 0; int sendIdCnt = 0; recvIdCnt = mjonPayService.selectCashPointSendIdCnt(mjonPayVO.getRecvUserId()); sendIdCnt = mjonPayService.selectCashPointSendIdCnt(mjonPayVO.getSendUserId()); if(recvIdCnt != 1) { modelAndView.addObject("result", "fail"); modelAndView.addObject("msg", "받는 아이디가 존재하지 않습니다."); }else if(sendIdCnt != 1) { modelAndView.addObject("result", "fail"); modelAndView.addObject("msg", "보내는 아이디가 존재하지 않습니다."); } //받는, 보내는 아이디가 있을 경우 보내는 아이디의 보유 캐시, 포인트 확인 if(recvIdCnt == 1 && sendIdCnt == 1) { MjonPayVO mjonUserPayVO = mjonPayService.selectCashPoint(mjonPayVO.getSendUserId()); if(mjonUserPayVO != null) { if(mjonPayVO.getCash() > mjonUserPayVO.getUserMoneyDouble()) { modelAndView.addObject("result", "fail"); modelAndView.addObject("msg", "보내려는 캐시가 보유 캐시보다 많습니다."); }else if(mjonPayVO.getPoint() > mjonUserPayVO.getUserPoint()) { modelAndView.addObject("result", "fail"); modelAndView.addObject("msg", "보내려는 포인트가 보유 포인트보다 많습니다."); } } } return modelAndView; } /** * * 캐시 , 포인트 이전 등록 * @param searchVO * @param model * @return "/uss/ion/pay/CashInsert.do" * @throws Exception */ @RequestMapping(value = {"/uss/ion/pay/insertCashPointSendAjax.do"}) public ModelAndView insertCashPointSendAjax( @ModelAttribute("searchVO") MjonPayVO mjonPayVO) throws Exception { ModelAndView modelAndView = new ModelAndView(); modelAndView.setViewName("jsonView"); modelAndView.addObject("result", "success"); modelAndView.addObject("msg", "저장되었습니다."); try { LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonPayVO.setAdmUserId(userId); mjonPayService.insertMjCashPointSend(mjonPayVO); }catch(Exception e) { modelAndView.addObject("result", "fail"); modelAndView.addObject("msg", "저장에 실패했습니다."); } return modelAndView; } /** * * 캐시 , 포인트 이전 목록화면 * @param searchVO * @param model * @return "/uss/ion/pay/selectCashPointSendList.do" * @throws Exception */ @RequestMapping(value = {"/uss/ion/pay/selectCashPointSendList.do"}) public String selectCashPointSendList( @ModelAttribute("searchVO") MjonPayVO mjonPayVO, ModelMap model) throws Exception { /** pageing */ PaginationInfo paginationInfo = new PaginationInfo(); paginationInfo.setCurrentPageNo(mjonPayVO.getPageIndex()); paginationInfo.setRecordCountPerPage(mjonPayVO.getPageUnit()); paginationInfo.setPageSize(mjonPayVO.getPageSize()); mjonPayVO.setFirstIndex(paginationInfo.getFirstRecordIndex()); mjonPayVO.setLastIndex(paginationInfo.getLastRecordIndex()); mjonPayVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage()); if("".equals(mjonPayVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonPayVO.setSearchSortCnd("sendNo"); mjonPayVO.setSearchSortOrd("desc"); } List<MjonPayVO> resultList = mjonPayService.selectCashPointSendList(mjonPayVO); model.addAttribute("resultList", resultList); paginationInfo.setTotalRecordCount(resultList.size() > 0 ? ((MjonPayVO)resultList.get(0)).getTotCnt() : 0); model.addAttribute("paginationInfo", paginationInfo); return "/uss/ion/pay/cashPointSendList"; } }