package itn.let.mjo.msgsent.web; import java.io.OutputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Date; import java.util.List; import java.util.Locale; import java.util.stream.Collectors; import javax.annotation.Resource; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; 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.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.stereotype.Controller; import org.springframework.ui.ModelMap; import org.springframework.web.bind.annotation.ModelAttribute; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.servlet.ModelAndView; import org.springframework.web.servlet.mvc.support.RedirectAttributes; import egovframework.rte.fdl.security.userdetails.util.EgovUserDetailsHelper; import egovframework.rte.ptl.mvc.tags.ui.pagination.PaginationInfo; import itn.com.cmm.EgovMessageSource; import itn.com.cmm.LoginVO; import itn.com.cmm.service.EgovFileMngService; import itn.com.cmm.service.EgovFileMngUtil; import itn.com.cmm.util.DateUtils; import itn.com.utl.fcc.service.EgovStringUtil; import itn.let.kakao.user.sent.service.KakaoSentService; import itn.let.mjo.addr.service.AddrGroupService; import itn.let.mjo.addr.service.AddrGroupVO; import itn.let.mjo.addr.service.AddrService; import itn.let.mjo.addr.service.AddrVO; import itn.let.mjo.apikey.service.ApiKeyMngService; import itn.let.mjo.apikey.service.ApiKeyVO; import itn.let.mjo.msgsent.service.MjonMsgSentCntVO; import itn.let.mjo.msgsent.service.MjonMsgSentService; import itn.let.mjo.msgsent.service.MjonMsgSentVO; @Controller public class MjonMsgSentController { @Resource(name = "MjonMsgSentService") private MjonMsgSentService mjonMsgSentService; @Resource (name = "AddrGroupService") private AddrGroupService addrGroupService; @Resource (name = "AddrService") private AddrService addrService; /** EgovMessageSource */ @Resource(name="egovMessageSource") EgovMessageSource egovMessageSource; @Resource(name="EgovFileMngUtil") private EgovFileMngUtil fileUtil; @Resource(name="EgovFileMngService") private EgovFileMngService fileMngService; @Resource(name = "KakaoSentService") private KakaoSentService kakaoSentService; //api key 정보 @Resource(name = "apiKeyMngService") private ApiKeyMngService apiKeyMngService; private static final Logger logger = LoggerFactory.getLogger(MjonMsgSentController.class); /** * 발송관리 화면 * @param searchVO * @param model * @return "/web/mjon/msgtxt/selectMsgTxtView.do" * @throws Exception */ @RequestMapping(value= {"/web/mjon/msgsent/selectMsgSentView.do"}) public String selectMsgSentView(@ModelAttribute("searchVO") MjonMsgSentVO mjonMsgSentVO, RedirectAttributes redirectAttributes, ModelMap model) throws Exception{ //로그인 권한정보 불러오기 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"; } mjonMsgSentVO.setUserId(userId); /* //전체 발송 건수 통계 불러오기 mjonMsgSentVO.setMsgType(""); List<MjonMsgSentVO> totalMsgCnt = mjonMsgSentService.selectDetailMsgSentCnt(mjonMsgSentVO); model.addAttribute("totalMsgCnt", totalMsgCnt); //단문 성공건, 실패건 불러오기 mjonMsgSentVO.setMsgType("4"); List<MjonMsgSentVO> smsMsgCnt = mjonMsgSentService.selectDetailMsgSentCnt(mjonMsgSentVO); model.addAttribute("smsMsgCnt", smsMsgCnt); //장문 성공건, 실패건 불러오기 mjonMsgSentVO.setMsgType("6"); mjonMsgSentVO.setFileCnt("0"); List<MjonMsgSentVO> lmsMsgCnt = mjonMsgSentService.selectDetailMsgSentCnt(mjonMsgSentVO); model.addAttribute("lmsMsgCnt", lmsMsgCnt); //그림문자 성공건, 실패건 불러오기 mjonMsgSentVO.setMsgType("6"); mjonMsgSentVO.setFileCnt("1"); List<MjonMsgSentVO> mmsMsgCnt = mjonMsgSentService.selectDetailMsgSentCnt(mjonMsgSentVO); model.addAttribute("mmsMsgCnt", mmsMsgCnt); */ /* //전체 발송 건수 통계 불러오기 mjonMsgSentVO.setMsgType(""); List<MjonMsgSentVO> totalMsgCnt = mjonMsgSentService.selectDetailMsgSentCntMix(mjonMsgSentVO); System.out.println("start"); // H:홈페이지, A:API 로 sms, lms, mms 나누는 영역 List<MjonMsgSentVO> H_totalMsgCnt = totalMsgCnt.stream().filter(t -> "H".equals(t.getSendKind())).collect(Collectors.toList()); List<MjonMsgSentVO> H_smsMsgCnt = new ArrayList<MjonMsgSentVO>(); List<MjonMsgSentVO> H_lmsMsgCnt = new ArrayList<MjonMsgSentVO>(); List<MjonMsgSentVO> H_mmsMsgCnt = new ArrayList<MjonMsgSentVO>(); System.out.println("start"); List<MjonMsgSentVO> A_totalMsgCnt = totalMsgCnt.stream().filter(t -> "A".equals(t.getSendKind())).collect(Collectors.toList()); List<MjonMsgSentVO> A_smsMsgCnt = new ArrayList<MjonMsgSentVO>(); List<MjonMsgSentVO> A_lmsMsgCnt = new ArrayList<MjonMsgSentVO>(); List<MjonMsgSentVO> A_mmsMsgCnt = new ArrayList<MjonMsgSentVO>(); System.out.println(" ::H_totalMsgCnt :: "+ H_totalMsgCnt.size()); System.out.println(" ::A_totalMsgCnt :: "+ A_totalMsgCnt.size()); H_totalMsgCnt.forEach(t->{ if (Integer.parseInt(t.getFilePath1())>0) { H_smsMsgCnt.add(t); } else if (Integer.parseInt(t.getFilePath2())>0) { H_lmsMsgCnt.add(t); } else if (Integer.parseInt(t.getFilePath3())>0) { H_mmsMsgCnt.add(t); } }); A_totalMsgCnt.forEach(t->{ if (Integer.parseInt(t.getFilePath1())>0) { A_smsMsgCnt.add(t); } else if (Integer.parseInt(t.getFilePath2())>0) { A_lmsMsgCnt.add(t); } else if (Integer.parseInt(t.getFilePath3())>0) { A_mmsMsgCnt.add(t); } }); //* 홈페이지에서 보낸 데이터 LIST //* SEND_KIND = "H" // 전체 영역 model.addAttribute("H_allSentCntVO", this.getResultCntProc(H_totalMsgCnt)); // 전체 단문(SMS) model.addAttribute("H_smsSentCntVO", this.getResultCntProc(H_smsMsgCnt)); // 전체 장문(LMS) model.addAttribute("H_lmsSentCntVO", this.getResultCntProc(H_lmsMsgCnt)); // 전체 장문(LMS) model.addAttribute("H_mmsSentCntVO", this.getResultCntProc(H_mmsMsgCnt)); // * 홈페이지에서 보낸 데이터 LIST //* SEND_KIND = "A" // 전체 영역 model.addAttribute("A_allSentCntVO", this.getResultCntProc(A_totalMsgCnt)); // 전체 단문(SMS) model.addAttribute("A_smsSentCntVO", this.getResultCntProc(A_smsMsgCnt)); // 전체 장문(LMS) model.addAttribute("A_lmsSentCntVO", this.getResultCntProc(A_lmsMsgCnt)); // 전체 장문(LMS) model.addAttribute("A_mmsSentCntVO", this.getResultCntProc(A_mmsMsgCnt)); */ /*<isEqual prepend="AND" property="searchCondition" compareValue="2"> a.mber_nm LIKE CONCAT('%',#searchKeyword#,'%') </isEqual> */ ApiKeyVO apiKeyVO = new ApiKeyVO(); apiKeyVO.setMberId(userId); model.addAttribute("appMgmt", apiKeyMngService.selectMberApiKeyChk(apiKeyVO) > 0 ? true : false); // 검색 리스트 불러오기 if(mjonMsgSentVO.getPageUnit() != 10) { mjonMsgSentVO.setPageUnit(mjonMsgSentVO.getPageUnit()); } //기본 내림차순 정렬 if(mjonMsgSentVO.getSearchSortOrd().equals("")) { mjonMsgSentVO.setSearchSortOrd("desc"); } //기본 등록일자 정렬 if(mjonMsgSentVO.getSearchSortCnd().equals("")) { mjonMsgSentVO.setSearchSortCnd("regdate"); } String startDate = mjonMsgSentVO.getStartDate(); String endDate = mjonMsgSentVO.getEndDate(); if(startDate == null && endDate == null ) { // // Calendar cal = Calendar.getInstance(); // Date now = new Date(); // // SimpleDateFormat format = new SimpleDateFormat("yyyy/MM/dd"); // // //종료일은 오늘날짜 // cal.setTime(now); // endDate = format.format(cal.getTime()); // // //시작일은 전날로 셋팅 // cal.add(Calendar.DATE, -1); // startDate = format.format(cal.getTime()); mjonMsgSentVO.setStartDate(DateUtils.getDateMonthsAgo(3)); mjonMsgSentVO.setEndDate(DateUtils.getCurrentDate()); } model.addAttribute("searchKeyword", mjonMsgSentVO.getSearchKeyword()); model.addAttribute("mjonMsgSentVO", mjonMsgSentVO); model.addAttribute("siteId", mjonMsgSentVO.getSiteId()); return "web/msgsent/MsgSentView"; } /** * 마이페이지 - 이용내역 - ajax * @param mjonMsgVO * @param model * @return "/web/member/pay/PayUserListAjax.do" * @throws Exception */ @RequestMapping(value="/web/msgsent/subcontent/MsgSentView_HA_allSentAjax.do") public String mberInfo_cash_pointAjax(@ModelAttribute("searchVO") MjonMsgSentVO mjonMsgSentVO, HttpServletRequest request, ModelMap model) throws Exception{ System.out.println("MsgSentView_HA_allSentAjax"); LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser(); String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgSentVO.setUserId(userId); //전체 발송 건수 통계 불러오기 mjonMsgSentVO.setMsgType(""); List<MjonMsgSentVO> totalMsgCnt = mjonMsgSentService.selectDetailMsgSentCntMix(mjonMsgSentVO); System.out.println("start"); // H:홈페이지, A:API 로 sms, lms, mms 나누는 영역 List<MjonMsgSentVO> H_totalMsgCnt = totalMsgCnt.stream().filter(t -> "H".equals(t.getSendKind())).collect(Collectors.toList()); List<MjonMsgSentVO> H_smsMsgCnt = new ArrayList<MjonMsgSentVO>(); List<MjonMsgSentVO> H_lmsMsgCnt = new ArrayList<MjonMsgSentVO>(); List<MjonMsgSentVO> H_mmsMsgCnt = new ArrayList<MjonMsgSentVO>(); System.out.println("start"); List<MjonMsgSentVO> A_totalMsgCnt = totalMsgCnt.stream().filter(t -> "A".equals(t.getSendKind())).collect(Collectors.toList()); List<MjonMsgSentVO> A_smsMsgCnt = new ArrayList<MjonMsgSentVO>(); List<MjonMsgSentVO> A_lmsMsgCnt = new ArrayList<MjonMsgSentVO>(); List<MjonMsgSentVO> A_mmsMsgCnt = new ArrayList<MjonMsgSentVO>(); System.out.println(" ::H_totalMsgCnt :: "+ H_totalMsgCnt.size()); System.out.println(" ::A_totalMsgCnt :: "+ A_totalMsgCnt.size()); H_totalMsgCnt.forEach(t->{ if (Integer.parseInt(t.getFilePath1())>0) { H_smsMsgCnt.add(t); } else if (Integer.parseInt(t.getFilePath2())>0) { H_lmsMsgCnt.add(t); } else if (Integer.parseInt(t.getFilePath3())>0) { H_mmsMsgCnt.add(t); } }); A_totalMsgCnt.forEach(t->{ if (Integer.parseInt(t.getFilePath1())>0) { A_smsMsgCnt.add(t); } else if (Integer.parseInt(t.getFilePath2())>0) { A_lmsMsgCnt.add(t); } else if (Integer.parseInt(t.getFilePath3())>0) { A_mmsMsgCnt.add(t); } }); //* 홈페이지에서 보낸 데이터 LIST //* SEND_KIND = "H" // 전체 영역 model.addAttribute("H_allSentCntVO", this.getResultCntProc(H_totalMsgCnt)); // 전체 단문(SMS) model.addAttribute("H_smsSentCntVO", this.getResultCntProc(H_smsMsgCnt)); // 전체 장문(LMS) model.addAttribute("H_lmsSentCntVO", this.getResultCntProc(H_lmsMsgCnt)); // 전체 장문(LMS) model.addAttribute("H_mmsSentCntVO", this.getResultCntProc(H_mmsMsgCnt)); // * 홈페이지에서 보낸 데이터 LIST //* SEND_KIND = "A" // 전체 영역 model.addAttribute("A_allSentCntVO", this.getResultCntProc(A_totalMsgCnt)); // 전체 단문(SMS) model.addAttribute("A_smsSentCntVO", this.getResultCntProc(A_smsMsgCnt)); // 전체 장문(LMS) model.addAttribute("A_lmsSentCntVO", this.getResultCntProc(A_lmsMsgCnt)); // 전체 장문(LMS) model.addAttribute("A_mmsSentCntVO", this.getResultCntProc(A_mmsMsgCnt)); /*<isEqual prepend="AND" property="searchCondition" compareValue="2"> a.mber_nm LIKE CONCAT('%',#searchKeyword#,'%') </isEqual> */ ApiKeyVO apiKeyVO = new ApiKeyVO(); apiKeyVO.setMberId(userId); model.addAttribute("appMgmt", apiKeyMngService.selectMberApiKeyChk(apiKeyVO) > 0 ? true : false); System.out.println("MsgSentView_HA_allSentAjax_end"); return "/web/msgsent/subcontent/MsgSentView_HA_allSentAjax"; } /** * @methodName : getResultCntProc * @author : 이호영 * @date : 2023.07.26 * @description : MsgSentView.jsp에서 JSTL로 계산하는 것을 JAVA로 분리 * @param msgCnt * @param smsMsgCnt * @param lmsMsgCnt * @param mmsMsgCnt * @return */ private MjonMsgSentCntVO getResultCntProc(List<MjonMsgSentVO> msgCnt) { // MjonMsgSentCntVO mjonSentCntVO = new MjonMsgSentCntVO(); MjonMsgSentCntVO cntVO = new MjonMsgSentCntVO(); /* * 전체 통계 * */ // 전체 대기 갯수 cntVO.setWaitCnt(msgCnt.stream() .filter(f->"W".equals(f.getMsgResultSts())) .mapToInt(t -> Integer.parseInt(t.getMsgResultCnt())).sum()); // 전체 성공 갯수 cntVO.setSuccCnt(msgCnt.stream() .filter(f->"S".equals(f.getMsgResultSts())) .mapToInt(t -> Integer.parseInt(t.getMsgResultCnt())).sum()); // 전체 실패 갯수 cntVO.setFailCnt(msgCnt.stream() .filter(f->"F".equals(f.getMsgResultSts())) .mapToInt(t -> Integer.parseInt(t.getMsgResultCnt())).sum()); // 전체 갯수 구하기 cntVO.setTotCnt(cntVO.getWaitCnt() + cntVO.getSuccCnt() + cntVO.getFailCnt()); return cntVO; } /** * 발송관리 탭 리스트 화면 * @param searchVO * @param model * @return "/web/mjon/reservmsg/selectReservMsgListViewAjax.do" * @throws Exception */ @RequestMapping(value= {"/web/mjon/msgsent/selectMsgSentListViewAjax.do"}) public String selectMsgSentListViewAjax(@ModelAttribute("searchVO") MjonMsgSentVO mjonMsgSentVO, ModelMap model) throws Exception{ //로그인 권한정보 불러오기 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgSentVO.setUserId(userId); // 검색 리스트 불러오기 if(mjonMsgSentVO.getPageUnit() != 10) { mjonMsgSentVO.setPageUnit(mjonMsgSentVO.getPageUnit()); } //기본 내림차순 정렬 if(mjonMsgSentVO.getSearchSortOrd().equals("")) { mjonMsgSentVO.setSearchSortOrd("desc"); mjonMsgSentVO.setSearchSortCnd("regdate"); } if(mjonMsgSentVO.getListType().equals("")) { mjonMsgSentVO.setListType("groupList"); } //선택 탭 정보 저장 //mjonResvMsgVO.setSearchMsgType(mjonResvMsgVO.getTabType()); /** pageing */ PaginationInfo paginationInfo = new PaginationInfo(); paginationInfo.setCurrentPageNo(mjonMsgSentVO.getPageIndex()); paginationInfo.setRecordCountPerPage(mjonMsgSentVO.getPageUnit()); paginationInfo.setPageSize(mjonMsgSentVO.getPageSize()); mjonMsgSentVO.setFirstIndex(paginationInfo.getFirstRecordIndex()); mjonMsgSentVO.setLastIndex(paginationInfo.getLastRecordIndex()); mjonMsgSentVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage()); if(!DateUtils.dateChkAndValueChk(mjonMsgSentVO.getStartDate(),mjonMsgSentVO.getEndDate(), 3 )) { mjonMsgSentVO.setStartDate(DateUtils.getDateMonthsAgo(3)); mjonMsgSentVO.setEndDate(DateUtils.getCurrentDate()); }; model.addAttribute("startDate", mjonMsgSentVO.getStartDate()); model.addAttribute("endDate", mjonMsgSentVO.getEndDate()); //전체 발송 리스트 불러오기 List<MjonMsgSentVO> resultAllSentList = mjonMsgSentService.selectAllMsgSentList(mjonMsgSentVO); model.addAttribute("resultAllSentList", resultAllSentList); model.addAttribute("resultAllSentCnt", resultAllSentList.size()); model.addAttribute("searchKeyword", mjonMsgSentVO.getSearchKeyword()); paginationInfo.setTotalRecordCount( resultAllSentList.size()> 0 ? (Integer.parseInt((resultAllSentList.get(0)).getTotMsgCnt())) : 0); model.addAttribute("paginationInfo", paginationInfo); model.addAttribute("totalRecordCount", paginationInfo.getTotalRecordCount()); //발송 결과 성공 실패 건수 리스트 불러오기 List<MjonMsgSentVO> resultMsgSucFailList = new ArrayList<MjonMsgSentVO>(); if(resultAllSentList.size() > 0) { resultMsgSucFailList = mjonMsgSentService.selectAllMsgSentSucFailList(resultAllSentList, mjonMsgSentVO); } model.addAttribute("resultMsgSucFailList", resultMsgSucFailList); model.addAttribute("mjonMsgSentVO", mjonMsgSentVO); String stateType = mjonMsgSentVO.getStateType(); String pageUrl = "web/msgsent/MsgSentAllListAjax"; if(stateType.equals("ready")) { pageUrl = "web/msgsent/MsgSentReadyListAjax"; }else if(stateType.equals("complete")) { pageUrl = "web/msgsent/MsgSentCompleteListAjax"; }else if(stateType.equals("fail")) { pageUrl = "web/msgsent/MsgSentFailListAjax"; } return pageUrl; } /** * 발송관리 건수별 상세 리스트 조회(전송건별) * @param searchVO * @param model * @return "/web/mjon/msgsent/selectMsgSFDetailListAjax.do" * @throws Exception */ @RequestMapping(value= {"/web/mjon/msgsent/selectMsgSFDetailListAjax.do"}) public String selectMsgSFDetailListAjax(@ModelAttribute("searchVO") MjonMsgSentVO mjonMsgSentVO, ModelMap model) throws Exception{ //로그인 권한정보 불러오기 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgSentVO.setUserId(userId); // 검색 리스트 불러오기 if(mjonMsgSentVO.getPageUnit() != 10) { mjonMsgSentVO.setPageUnit(mjonMsgSentVO.getPageUnit()); } //기본 내림차순 정렬 if(mjonMsgSentVO.getSearchSortOrd().equals("")) { mjonMsgSentVO.setSearchSortOrd("desc"); mjonMsgSentVO.setSearchSortCnd("regdate"); } mjonMsgSentVO.setListType("privateList"); /** pageing */ PaginationInfo paginationInfo = new PaginationInfo(); paginationInfo.setCurrentPageNo(mjonMsgSentVO.getPageIndex()); paginationInfo.setRecordCountPerPage(mjonMsgSentVO.getPageUnit()); paginationInfo.setPageSize(mjonMsgSentVO.getPageSize()); mjonMsgSentVO.setFirstIndex(paginationInfo.getFirstRecordIndex()); mjonMsgSentVO.setLastIndex(paginationInfo.getLastRecordIndex()); mjonMsgSentVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage()); //전체 발송 리스트 불러오기 List<MjonMsgSentVO> resultAllSentList = mjonMsgSentService.selectAllMsgSentDetailList(mjonMsgSentVO); model.addAttribute("resultAllSentList", resultAllSentList); model.addAttribute("resultAllSentCnt", resultAllSentList.size()); model.addAttribute("searchKeyword", mjonMsgSentVO.getSearchKeyword()); paginationInfo.setTotalRecordCount( resultAllSentList.size()> 0 ? (Integer.parseInt((resultAllSentList.get(0)).getTotMsgCnt())) : 0); model.addAttribute("paginationInfo", paginationInfo); model.addAttribute("totalRecordCount", paginationInfo.getTotalRecordCount()); // JSPark 2023.02.28 => 위 쿼리에 문자결과 항목추가로 아래 데이터 불필요 //발송 결과 성공 실패 건수 리스트 불러오기 //List<MjonMsgSentVO> resultMsgSucFailList = mjonMsgSentService.selectAllMsgSentSFDetailList(mjonMsgSentVO); //model.addAttribute("resultMsgSucFailList", resultMsgSucFailList); model.addAttribute("mjonMsgSentVO", mjonMsgSentVO); return "web/msgsent/MsgSentSFDetailListAjax"; } /** * 발송관리 문자 상세보기 내용 * @param searchVO * @param model * @return "/web/mjon/msgsent/selectMsgSentDetailDataAjax.do" * @throws Exception */ @RequestMapping(value= {"/web/mjon/msgsent/selectMsgSentDetailDataAjax.do"}) public String selectMsgSentDetailDataAjax(@ModelAttribute("searchVO") MjonMsgSentVO mjonMsgSentVO, ModelMap model) throws Exception{ //로그인 권한정보 불러오기 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgSentVO.setUserId(userId); //발송 관리 문자발송 내용 상세보기 팝업 => 문자내용(MJ_MSG_DATA) MjonMsgSentVO resultMsgDetail = mjonMsgSentService.selectMsgSentDetailDataAjax(mjonMsgSentVO); if(resultMsgDetail.getFileCnt() != "0") { if(resultMsgDetail.getFilePath1() != null) { String filePathId1 = resultMsgDetail.getFilePath1().substring(resultMsgDetail.getFilePath1().lastIndexOf("/")+1); int idx = filePathId1.lastIndexOf("."); String fileId = filePathId1.substring(0, idx); MjonMsgSentVO info = mjonMsgSentService.selectFileInfo(fileId); model.addAttribute("atchFileId1", info.getAtchFileId()); model.addAttribute("fileSn1", info.getFileSn()); } if(resultMsgDetail.getFilePath2() != null) { String filePathId2 = resultMsgDetail.getFilePath2().substring(resultMsgDetail.getFilePath2().lastIndexOf("/")+1); int idx = filePathId2.lastIndexOf("."); String fileId = filePathId2.substring(0, idx); MjonMsgSentVO info = mjonMsgSentService.selectFileInfo(fileId); model.addAttribute("atchFileId2", info.getAtchFileId()); model.addAttribute("fileSn2", info.getFileSn()); } if(resultMsgDetail.getFilePath3() != null) { String filePathId3 = resultMsgDetail.getFilePath3().substring(resultMsgDetail.getFilePath3().lastIndexOf("/")+1); int idx = filePathId3.lastIndexOf("."); String fileId = filePathId3.substring(0, idx); MjonMsgSentVO info = mjonMsgSentService.selectFileInfo(fileId); model.addAttribute("atchFileId3", info.getAtchFileId()); model.addAttribute("fileSn3", info.getFileSn()); } } model.addAttribute("resultMsgDetail", resultMsgDetail); return "web/msgsent/MsgSentDetailPopAjax"; } /** * 발송관리 문자 상세보기 내용 * @param searchVO * @param model * @return "/web/mjon/msgsent/selectMsgSentDetailDataAjax.do" * @throws Exception */ @RequestMapping(value= {"/web/mjon/msgsent/selectMsgSentDetailData2Ajax.do"}) public String selectMsgSentDetailData2Ajax(@ModelAttribute("searchVO") MjonMsgSentVO mjonMsgSentVO, ModelMap model) throws Exception{ //로그인 권한정보 불러오기 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgSentVO.setUserId(userId); //발송 관리 문자발송 내용 상세보기 팝업 => 문자내용(MJ_MSG_DATA) MjonMsgSentVO resultMsgDetail = mjonMsgSentService.selectMsgSentDetailDataAjax2(mjonMsgSentVO); if(resultMsgDetail.getFileCnt() != "0") { if(resultMsgDetail.getFilePath1() != null) { String filePathId1 = resultMsgDetail.getFilePath1().substring(resultMsgDetail.getFilePath1().lastIndexOf("/")+1); int idx = filePathId1.lastIndexOf("."); String fileId = filePathId1.substring(0, idx); MjonMsgSentVO info = mjonMsgSentService.selectFileInfo(fileId); model.addAttribute("atchFileId1", info.getAtchFileId()); model.addAttribute("fileSn1", info.getFileSn()); } if(resultMsgDetail.getFilePath2() != null) { String filePathId2 = resultMsgDetail.getFilePath2().substring(resultMsgDetail.getFilePath2().lastIndexOf("/")+1); int idx = filePathId2.lastIndexOf("."); String fileId = filePathId2.substring(0, idx); MjonMsgSentVO info = mjonMsgSentService.selectFileInfo(fileId); model.addAttribute("atchFileId2", info.getAtchFileId()); model.addAttribute("fileSn2", info.getFileSn()); } if(resultMsgDetail.getFilePath3() != null) { String filePathId3 = resultMsgDetail.getFilePath3().substring(resultMsgDetail.getFilePath3().lastIndexOf("/")+1); int idx = filePathId3.lastIndexOf("."); String fileId = filePathId3.substring(0, idx); MjonMsgSentVO info = mjonMsgSentService.selectFileInfo(fileId); model.addAttribute("atchFileId3", info.getAtchFileId()); model.addAttribute("fileSn3", info.getFileSn()); } } model.addAttribute("resultMsgDetail", resultMsgDetail); return "web/msgsent/MsgSentDetailPop2Ajax"; } /** * 발송 관리 문자 선택 삭제 기능 * @param searchVO * @param model * @return "web/mjon/msgsent/deleteMsgSentDataAjax.do" * @throws Exception */ @RequestMapping(value= {"/web/mjon/msgsent/deleteMsgSentDataAjax.do"}) public String deleteMsgSentDataAjax(@ModelAttribute("searchVO") MjonMsgSentVO mjonMsgSentVO, ModelMap model) throws Exception{ //로그인 권한정보 불러오기 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); mjonMsgSentVO.setUserId(userId); //선택 문자 삭제 처리 int resultCnt = mjonMsgSentService.deleteMsgSentDataAjax(mjonMsgSentVO); /* * * 리스트 화면 불러오기 * * */ // 검색 리스트 불러오기 if(mjonMsgSentVO.getPageUnit() != 10) { mjonMsgSentVO.setPageUnit(mjonMsgSentVO.getPageUnit()); } //기본 내림차순 정렬 if(mjonMsgSentVO.getSearchSortOrd().equals("")) { mjonMsgSentVO.setSearchSortOrd("desc"); } //기본 등록일자 정렬 if(mjonMsgSentVO.getSearchSortCnd().equals("")) { mjonMsgSentVO.setSearchSortCnd("regdate"); } /** pageing */ PaginationInfo paginationInfo = new PaginationInfo(); paginationInfo.setCurrentPageNo(mjonMsgSentVO.getPageIndex()); paginationInfo.setRecordCountPerPage(mjonMsgSentVO.getPageUnit()); paginationInfo.setPageSize(mjonMsgSentVO.getPageSize()); mjonMsgSentVO.setFirstIndex(paginationInfo.getFirstRecordIndex()); mjonMsgSentVO.setLastIndex(paginationInfo.getLastRecordIndex()); mjonMsgSentVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage()); //전체 발송 리스트 불러오기 List<MjonMsgSentVO> resultAllSentList = mjonMsgSentService.selectAllMsgSentList(mjonMsgSentVO); model.addAttribute("resultAllSentList", resultAllSentList); model.addAttribute("resultAllSentCnt", resultAllSentList.size()); model.addAttribute("searchKeyword", mjonMsgSentVO.getSearchKeyword()); paginationInfo.setTotalRecordCount( resultAllSentList.size()> 0 ? (Integer.parseInt((resultAllSentList.get(0)).getTotMsgCnt())) : 0); model.addAttribute("paginationInfo", paginationInfo); model.addAttribute("totalRecordCount", paginationInfo.getTotalRecordCount()); model.addAttribute("mjonMsgSentVO", mjonMsgSentVO); String stateType = mjonMsgSentVO.getStateType(); String pageUrl = "web/msgsent/MsgSentAllListAjax"; if(stateType.equals("ready")) { pageUrl = "web/msgsent/MsgSentReadyListAjax"; }else if(stateType.equals("complete")) { pageUrl = "web/msgsent/MsgSentCompleteListAjax"; }else if(stateType.equals("fail")) { pageUrl = "web/msgsent/MsgSentFailListAjax"; } return pageUrl; } /** * 발송문자 수신번호 주소록에서 삭제 하기 * * @param MjonMsgSentVO * @param * @param sessionVO * @param model * @return * @throws Exception */ @RequestMapping(value= {"/web/mjon/msgsent/deleteAddrNoDataAjax.do"}) public ModelAndView deleteAddrNoDataAjax( @ModelAttribute("searchVO") MjonMsgSentVO mjonMsgSentVO, ModelMap model) throws Exception { ModelAndView modelAndView = new ModelAndView(); modelAndView.setViewName("jsonView"); //로그인 권한정보 불러오기 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); if(userId.equals("")) { modelAndView.addObject("status", "loginFail"); modelAndView.addObject("message", "로그인이 필요합니다."); return modelAndView; }else { mjonMsgSentVO.setUserId(userId); } String listType = mjonMsgSentVO.getListType(); if(listType.equals("")) { mjonMsgSentVO.setListType("groupList"); } try { //문자 발송 테이블에서 수신자 목록 리스트 불러오기 List<String> resultCallToList = mjonMsgSentService.selectCallToListData(mjonMsgSentVO); MjonMsgSentVO mjonMsgCallListVO = new MjonMsgSentVO(); //디비에서 불러온 수신자 번호 리스트 저장 mjonMsgCallListVO.setCallToList(resultCallToList); //아이디 저장 mjonMsgCallListVO.setUserId(userId); //주소록 디비에서 연락처 정보를 delete 시킴 int resultCnt = mjonMsgSentService.deleteAddrPhoneNo(mjonMsgCallListVO); modelAndView.addObject("status", "success"); modelAndView.addObject("message", "총 " + resultCnt + "건의 주소록을 삭제하였습니다."); modelAndView.addObject("resultCnt", resultCnt); } catch (Exception e) { modelAndView.addObject("status", "fail"); modelAndView.addObject("message", "주소록 삭제 중 오류가 발생하였습니다."); } return modelAndView; } /** * 발송문자 수신거부번호 등록 하기 * * @param MjonMsgSentVO * @param * @param sessionVO * @param model * @return * @throws Exception */ @RequestMapping(value= {"/web/mjon/msgsent/insertAddBlockNoDataAjax.do"}) public ModelAndView insertAddBlockNoDataAjax( @ModelAttribute("searchVO") MjonMsgSentVO mjonMsgSentVO, ModelMap model) throws Exception { ModelAndView modelAndView = new ModelAndView(); modelAndView.setViewName("jsonView"); //로그인 권한정보 불러오기 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); if(userId.equals("")) { modelAndView.addObject("status", "loginFail"); modelAndView.addObject("message", "로그인이 필요합니다."); return modelAndView; }else { mjonMsgSentVO.setUserId(userId); } String listType = mjonMsgSentVO.getListType(); if(listType.equals("")) { mjonMsgSentVO.setListType("groupList"); } try { if("fail".equals(mjonMsgSentVO.getStateType())) { mjonMsgSentVO.setListType("privateList"); } //문자 발송 테이블에서 수신자 목록 리스트 불러오기 List<String> resultCallToList = mjonMsgSentService.selectCallToListData(mjonMsgSentVO); MjonMsgSentVO mjonMsgCallListVO = new MjonMsgSentVO(); //디비에서 불러온 수신자 번호 리스트 저장 mjonMsgCallListVO.setCallToList(resultCallToList); //아이디 저장 mjonMsgCallListVO.setUserId(userId); //주소록 디비에서 연락처 정보를 delete 시킴 int resultCnt = mjonMsgSentService.insertAddBlockNoDataAjax(mjonMsgCallListVO); modelAndView.addObject("status", "success"); modelAndView.addObject("message", "총 " + resultCnt + "건의 수신거부번호를 등록하였습니다."); modelAndView.addObject("resultCnt", resultCnt); } catch (Exception e) { modelAndView.addObject("status", "fail"); modelAndView.addObject("message", "수신거부번호 등록 중 오류가 발생하였습니다."); } return modelAndView; } /** * 발송문자 주소록 그룹 등록 하기 * * @param MjonMsgSentVO * @param * @param sessionVO * @param model * @return * @throws Exception */ @RequestMapping(value= {"/web/mjon/msgsent/insertAddAddrGrpDataAjax.do"}) public ModelAndView insertAddAddrGrpDataAjax( @ModelAttribute("searchVO") MjonMsgSentVO mjonMsgSentVO, ModelMap model) throws Exception { ModelAndView modelAndView = new ModelAndView(); modelAndView.setViewName("jsonView"); //로그인 권한정보 불러오기 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); if(userId.equals("")) { modelAndView.addObject("status", "loginFail"); modelAndView.addObject("message", "로그인이 필요합니다."); return modelAndView; }else { mjonMsgSentVO.setUserId(userId); } String listType = mjonMsgSentVO.getListType(); if(listType.equals("")) { mjonMsgSentVO.setListType("groupList"); } try { //문자 발송 테이블에서 수신자 목록 리스트 불러오기 List<String> resultCallToList = mjonMsgSentService.selectCallToListData(mjonMsgSentVO); MjonMsgSentVO mjonMsgCallListVO = new MjonMsgSentVO(); //디비에서 불러온 수신자 번호 리스트 저장 mjonMsgCallListVO.setCallToList(resultCallToList); //아이디 저장 mjonMsgCallListVO.setUserId(userId); int resultCnt = 0; AddrGroupVO addrGroupVO = new AddrGroupVO(); //신규 그룹 등록 addrGroupVO.setAddrGrpNm(mjonMsgSentVO.getAddrGrpNm()); addrGroupVO.setMberId(userId); addrGroupVO.setFrstRegisterId(userId); String addrGrpId = addrGroupService.insertAddrGroup(addrGroupVO); if(addrGrpId != null) { List<AddrVO> addrDataInfo = new ArrayList<AddrVO>(); for(String phone : resultCallToList) { AddrVO addrVO = new AddrVO(); addrVO.setAddrPhoneNo(phone); addrVO.setAddrGrpId(addrGrpId); addrVO.setBookmark("N"); //북마크 : N addrVO.setFrstRegisterId(userId); addrVO.setMberId(userId); addrDataInfo.add(addrVO); } resultCnt = addrService.insertAddrList(addrDataInfo); } //주소록 디비에서 연락처 정보를 delete 시킴 //int resultCnt = mjonMsgSentService.insertAddBlockNoDataAjax(mjonMsgCallListVO); modelAndView.addObject("status", "success"); modelAndView.addObject("message", "총 " + resultCnt + "건의 수신번호를 등록하였습니다."); modelAndView.addObject("resultCnt", resultCnt); } catch (Exception e) { modelAndView.addObject("status", "fail"); modelAndView.addObject("message", "주소록 등록 중 오류가 발생하였습니다."); } return modelAndView; } /** * 발송관리 출력하기 팝업화면 * * @param MjonMsgSentVO * @param * @param sessionVO * @param model * @return * @throws Exception */ @RequestMapping(value= {"/web/mjon/msgsent/printMsgSentDataAjax.do"}) public String printMsgSentDataAjax( @ModelAttribute("searchVO") MjonMsgSentVO mjonMsgSentVO, 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()); if(userId.equals("")) { /*modelAndView.addObject("status", "loginFail"); modelAndView.addObject("message", "로그인이 필요합니다."); return modelAndView;*/ }else { mjonMsgSentVO.setUserId(userId); } // 검색 리스트 불러오기 if(mjonMsgSentVO.getPageUnit() != 10) { mjonMsgSentVO.setPageUnit(mjonMsgSentVO.getPageUnit()); } //기본 내림차순 정렬 if(mjonMsgSentVO.getSearchSortOrd().equals("")) { mjonMsgSentVO.setSearchSortOrd("desc"); mjonMsgSentVO.setSearchSortCnd("regdate"); } if(mjonMsgSentVO.getListType().equals("")) { mjonMsgSentVO.setListType("groupList"); } //선택 탭 정보 저장 //mjonResvMsgVO.setSearchMsgType(mjonResvMsgVO.getTabType()); /** pageing */ PaginationInfo paginationInfo = new PaginationInfo(); paginationInfo.setCurrentPageNo(mjonMsgSentVO.getPageIndex()); paginationInfo.setRecordCountPerPage(mjonMsgSentVO.getPageUnit()); paginationInfo.setPageSize(mjonMsgSentVO.getPageSize()); mjonMsgSentVO.setFirstIndex(paginationInfo.getFirstRecordIndex()); mjonMsgSentVO.setLastIndex(paginationInfo.getLastRecordIndex()); mjonMsgSentVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage()); //전체 발송 리스트 불러오기 List<MjonMsgSentVO> resultAllSentList = mjonMsgSentService.selectAllMsgSentList(mjonMsgSentVO); model.addAttribute("resultAllSentList", resultAllSentList); model.addAttribute("resultAllSentCnt", resultAllSentList.size()); model.addAttribute("searchKeyword", mjonMsgSentVO.getSearchKeyword()); paginationInfo.setTotalRecordCount( resultAllSentList.size()> 0 ? (Integer.parseInt((resultAllSentList.get(0)).getTotMsgCnt())) : 0); model.addAttribute("paginationInfo", paginationInfo); model.addAttribute("totalRecordCount", paginationInfo.getTotalRecordCount()); //발송 결과 성공 실패 건수 리스트 불러오기 List<MjonMsgSentVO> resultMsgSucFailList = mjonMsgSentService.selectAllMsgSentSucFailList(resultAllSentList, mjonMsgSentVO); model.addAttribute("resultMsgSucFailList", resultMsgSucFailList); model.addAttribute("mjonMsgSentVO", mjonMsgSentVO); model.addAttribute("userNm", userNm); 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/msgsent/MsgSentPrintListPopUp"; } /** * 발송관리 엑셀다운로드 기능 * @param searchVO * @param model * @return "/web/mjon/msgsent/msgSentExcelDownLoadAjax.do" * @throws Exception */ @RequestMapping(value= {"/web/mjon/msgsent/msgSentExcelDownLoadAjax.do"}) public void msgSentExcelDownLoadAjax(MjonMsgSentVO mjonMsgSentVO, RedirectAttributes redirectAttributes, HttpServletRequest request, HttpServletResponse response , ModelMap model) throws Exception{ //로그인 권한정보 불러오기 LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null; String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId()); if(!userId.equals("")) { mjonMsgSentVO.setUserId(userId); }else { /*modelAndView.addObject("message", "로그인 후 이용이 가능합니다."); modelAndView.addObject("result", "fail"); return modelAndView;*/ } String stateType = mjonMsgSentVO.getStateType(); String tabType = mjonMsgSentVO.getTabType(); // 메모리에 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 String type = ""; String fCnt = ""; SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm"); try{ mjonMsgSentVO.setRecordCountPerPage(100000); mjonMsgSentVO.setFirstIndex(0); if("".equals(mjonMsgSentVO.getSearchSortCnd())){ //최초조회시 최신것 조회List mjonMsgSentVO.setSearchSortCnd("regdate"); mjonMsgSentVO.setSearchSortOrd("desc"); } //예약 관리 리스트 불러오기 List<MjonMsgSentVO> resultAllSentList = mjonMsgSentService.selectAllMsgSentList(mjonMsgSentVO); {//화면 리스트 row = sheet.createRow(0); sheet.setColumnWidth(1, 5000); // 발송일시 칼럼의 폭 조절 sheet.setColumnWidth(3, 10000); // 내용 칼럼의 폭 조절 sheet.setColumnWidth(4, 5000); // 받는사람 이름 칼럼의 폭 조절 sheet.setColumnWidth(5, 5000); // 받는사람 연락처 칼럼의 폭 조절 sheet.setColumnWidth(6, 5000); // 발신번호 칼럼의 폭 조절 sheet.setColumnWidth(7, 5000); // 발송상태 칼럼의 폭 조절 sheet.setColumnWidth(8, 5000); // 발송건수 칼럼의 폭 조절 //셀병합 처리 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)); //내용 세로 셀병합 sheet.addMergedRegion(new CellRangeAddress(0,1,4,4)); //받는사람 이름 셀병합 sheet.addMergedRegion(new CellRangeAddress(0,1,5,5)); //받는사람 연락처 셀병합 sheet.addMergedRegion(new CellRangeAddress(0,1,6,6)); //발신번호 세로 셀병합 sheet.addMergedRegion(new CellRangeAddress(0,1,7,7)); //발송상태 세로 셀병합 sheet.addMergedRegion(new CellRangeAddress(0,1,8,8)); //발송건수 세로 셀병합 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("발송건수"); 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); cell = row.createCell(11); cell.setCellValue("금액"); sheet.addMergedRegion(new CellRangeAddress(0,0,11,12)); // 발송결과 건수 가로 셀병합 cell.setCellStyle(style); cell = row.createCell(12); cell.setCellValue("예약취소"); 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.setCellStyle(style); cell = row.createCell(6); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellStyle(style); cell = row.createCell(9); cell.setCellValue("성공"); cell.setCellStyle(style); cell = row.createCell(10); cell.setCellValue("실패/대기"); cell.setCellStyle(style); cell = row.createCell(11); cell.setCellValue("과금"); cell.setCellStyle(style); cell = row.createCell(12); cell.setCellValue("비과금"); cell.setCellStyle(style); } for(int i=0; i < resultAllSentList.size(); i++) { String msgType = "단문"; if(resultAllSentList.get(i).getMsgType().equals("6") && resultAllSentList.get(i).getFileCnt().equals("0")) { msgType = "장문"; }else if(resultAllSentList.get(i).getMsgType().equals("6") && !resultAllSentList.get(i).getFileCnt().equals("0")) { msgType = "그림"; } int excelLen = 0; row = sheet.createRow(i+2); excelLen = 12; for(int j=0 ; j <= excelLen ; j++) { cell = row.createCell(j); cell.setCellStyle(style); if(j==0) cell.setCellValue(i+1); //번호 if(j==1) cell.setCellValue(sdf.format((resultAllSentList.get(i)).getReqdate())); //발송일자 if(j==2) { type = resultAllSentList.get(i).getMsgType(); fCnt = resultAllSentList.get(i).getFileCnt(); if(type.equals("4")) { cell.setCellValue("단문"); //형태 }else { if(fCnt.equals("0")) { cell.setCellValue("장문"); //형태 }else { cell.setCellValue("그림"); //형태 } } } if(j==3) cell.setCellValue((resultAllSentList.get(i)).getSmsTxt()); //내용 if(j==4) cell.setCellValue((resultAllSentList.get(i)).getAddrNm()); if(j==5) cell.setCellValue((resultAllSentList.get(i)).getCallToComma()); if(j==6) cell.setCellValue((resultAllSentList.get(i)).getCallFromComma()); //발신번호 if(j==7) { //발송상태 처리해주기 String resvCYn = resultAllSentList.get(i).getReserveCYn(); String curState = resultAllSentList.get(i).getCurState(); if(resvCYn.equals("Y")) { cell.setCellValue("예약 취소"); //발송상태 }else { if(curState.equals("0")) { cell.setCellValue("발송 대기"); //발송상태 }else if(curState.equals("1")) { cell.setCellValue("발송중"); //발송상태 }else if(curState.equals("2")) { cell.setCellValue("결과 대기"); //발송상태 }else if(curState.equals("3")) { cell.setCellValue("발송 완료"); //발송상태 } } } if(j==8) cell.setCellValue((resultAllSentList.get(i)).getMsgGroupCnt()); //발송건수 //발송결과 성공, 실패 처리 int resSucCnt = 0; int resFailCnt = 0; double resSucPrice = 0; double resFailPirce = 0; double eachPrice = Float.parseFloat(resultAllSentList.get(i).getEachPrice()); int resultSValue = 0; int resultWFValue = 0; if(resultAllSentList.get(i).getResultSValue() != null) { resultSValue = Integer.parseInt(resultAllSentList.get(i).getResultSValue()); }else { resultSValue = 1; } if(resultAllSentList.get(i).getResultWFValue() != null) { resultWFValue = Integer.parseInt(resultAllSentList.get(i).getResultWFValue()); }else { resultWFValue = 1; } if("S".equals(resultAllSentList.get(i).getMsgResult())) { resSucCnt = resultSValue; } else { resFailCnt = resultWFValue; } resSucPrice = eachPrice * resSucCnt; resFailPirce = eachPrice * resFailCnt; if(j==9) cell.setCellValue(resSucCnt); //발송결과 성공 if(j==10) cell.setCellValue(resFailCnt); //발송결과 실패 if(j==11) cell.setCellValue(resSucPrice); // 과금 금액 if(j==12) cell.setCellValue(resFailPirce); //비과금 금액 } } response.setHeader("Set-Cookie", "fileDownload=true; path=/"); SimpleDateFormat mSimpleDateFormat = new SimpleDateFormat ( "yyyyMMdd_HHmmss", 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) {} } } }