File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
package itn.let.mjo.reservmsg.web;
import java.io.OutputStream;
import java.io.PrintWriter;
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 com.ibm.icu.text.DecimalFormat;
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.service.FileVO;
import itn.com.cmm.util.StringUtil;
import itn.com.utl.fcc.service.EgovStringUtil;
import itn.let.kakao.user.sent.service.KakaoSentService;
import itn.let.kakao.user.sent.service.KakaoSentVO;
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.MjonMsgSentVO;
import itn.let.mjo.reservmsg.service.MjonMsgReservCntVO;
import itn.let.mjo.reservmsg.service.MjonReservMsgService;
import itn.let.mjo.reservmsg.service.MjonResvMsgVO;
@Controller
public class MjonReservMsgController {
@Resource(name = "MjonReservMsgService")
private MjonReservMsgService mjonReservMsgService;
/** 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(MjonReservMsgController.class);
/**
* 예약관리 화면
* @param searchVO
* @param model
* @return "/web/mjon/reservmsg/selectReservMsgView.do"
* @throws Exception
*/
@RequestMapping(value= {"/web/mjon/reservmsg/selectReservMsgView.do"})
public String selectReservMsgView(@ModelAttribute("searchVO") MjonResvMsgVO mjonResvMsgVO,
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";
}
mjonResvMsgVO.setUserId(userId);
//전체 발송 건수 통계 불러오기
mjonResvMsgVO.setMsgType("");
List<MjonResvMsgVO> totalMsgCnt = mjonReservMsgService.selectDetailMsgCnt(mjonResvMsgVO);//mjonReservMsgService.selectTotalMsgCnt(mjonResvMsgVO);
List<MjonResvMsgVO> H_totalMsgCnt = totalMsgCnt.stream().filter(t -> "H".equals(t.getSendKind())).collect(Collectors.toList());
List<MjonResvMsgVO> A_totalMsgCnt = totalMsgCnt.stream().filter(t -> "A".equals(t.getSendKind())).collect(Collectors.toList());
model.addAttribute("totalMsgCnt", totalMsgCnt);
//단문 성공건, 실패건 불러오기
mjonResvMsgVO.setMsgType("4");
List<MjonResvMsgVO> smsMsgCnt = mjonReservMsgService.selectDetailMsgCnt(mjonResvMsgVO);
List<MjonResvMsgVO> H_smsMsgCnt = smsMsgCnt.stream().filter(t -> "H".equals(t.getSendKind())).collect(Collectors.toList());
List<MjonResvMsgVO> A_smsMsgCnt = smsMsgCnt.stream().filter(t -> "A".equals(t.getSendKind())).collect(Collectors.toList());
model.addAttribute("smsMsgCnt", smsMsgCnt);
//장문 성공건, 실패건 불러오기
mjonResvMsgVO.setMsgType("6");
mjonResvMsgVO.setFileCnt("0");
List<MjonResvMsgVO> lmsMsgCnt = mjonReservMsgService.selectDetailMsgCnt(mjonResvMsgVO);
List<MjonResvMsgVO> H_lmsMsgCnt = lmsMsgCnt.stream().filter(t -> "H".equals(t.getSendKind())).collect(Collectors.toList());
List<MjonResvMsgVO> A_lmsMsgCnt = lmsMsgCnt.stream().filter(t -> "A".equals(t.getSendKind())).collect(Collectors.toList());
model.addAttribute("lmsMsgCnt", lmsMsgCnt);
//그림문자 성공건, 실패건 불러오기
mjonResvMsgVO.setMsgType("6");
mjonResvMsgVO.setFileCnt("1");
List<MjonResvMsgVO> mmsMsgCnt = mjonReservMsgService.selectDetailMsgCnt(mjonResvMsgVO);
List<MjonResvMsgVO> H_mmsMsgCnt = mmsMsgCnt.stream().filter(t -> "H".equals(t.getSendKind())).collect(Collectors.toList());
List<MjonResvMsgVO> A_mmsMsgCnt = mmsMsgCnt.stream().filter(t -> "A".equals(t.getSendKind())).collect(Collectors.toList());
model.addAttribute("mmsMsgCnt", mmsMsgCnt);
/*
* 홈페이지에서 보낸 데이터 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));
ApiKeyVO apiKeyVO = new ApiKeyVO();
apiKeyVO.setMberId(userId);
model.addAttribute("appMgmt", apiKeyMngService.selectMberApiKeyChk(apiKeyVO) > 0 ? true : false);
// model.addAttribute("mmsMsgCnt", mmsMsgCnt);
// 검색 리스트 불러오기
if(mjonResvMsgVO.getPageUnit() != 10) {
mjonResvMsgVO.setPageUnit(mjonResvMsgVO.getPageUnit());
}
//기본 내림차순 정렬
if(mjonResvMsgVO.getSearchSortOrd().equals("")) {
mjonResvMsgVO.setSearchSortOrd("desc");
}
//기본 등록일자 정렬
if(mjonResvMsgVO.getSearchSortCnd().equals("")) {
mjonResvMsgVO.setSearchSortCnd("regdate");
}
/** pageing */
/*PaginationInfo paginationInfo = new PaginationInfo();
paginationInfo.setCurrentPageNo(mjonResvMsgVO.getPageIndex());
paginationInfo.setRecordCountPerPage(mjonResvMsgVO.getPageUnit());
paginationInfo.setPageSize(mjonResvMsgVO.getPageSize());
mjonResvMsgVO.setFirstIndex(paginationInfo.getFirstRecordIndex());
mjonResvMsgVO.setLastIndex(paginationInfo.getLastRecordIndex());
mjonResvMsgVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage());*/
//전체 발송 리스트 불러오기
/*List<MjonResvMsgVO> resultAllResList = mjonReservMsgService.selectAllResvMsgList(mjonResvMsgVO);
model.addAttribute("resultAllResList", resultAllResList);
model.addAttribute("resultAllResCnt", resultAllResList.size());
model.addAttribute("searchKeyword", mjonResvMsgVO.getSearchKeyword());
paginationInfo.setTotalRecordCount( resultAllResList.size()> 0 ? (Integer.parseInt((resultAllResList.get(0)).getTotMsgCnt())) : 0);
model.addAttribute("paginationInfo", paginationInfo);
model.addAttribute("totalRecordCount", paginationInfo.getTotalRecordCount());*/
model.addAttribute("searchKeyword", mjonResvMsgVO.getSearchKeyword());
model.addAttribute("mjonResvMsgVO", mjonResvMsgVO);
model.addAttribute("siteId", mjonResvMsgVO.getSiteId());
return "web/reservedmsg/ReservedMsgView";
}
/**
* @methodName : getResultCntProc
* @author : 이호영
* @date : 2023.07.26
* @description : MsgSentView.jsp에서 JSTL로 계산하는 것을 JAVA로 분리
* @param msgCnt
* @param smsMsgCnt
* @param lmsMsgCnt
* @param mmsMsgCnt
* @return
*/
private MjonMsgReservCntVO getResultCntProc(List<MjonResvMsgVO> msgCnt) {
// MjonMsgSentCntVO mjonSentCntVO = new MjonMsgSentCntVO();
MjonMsgReservCntVO cntVO = new MjonMsgReservCntVO();
/*
* 전체 통계
* */
// 대기 갯수
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.setCnclCnt(msgCnt.stream()
.filter(f->"C".equals(f.getMsgResultSts()))
.mapToInt(t -> Integer.parseInt(t.getMsgResultCnt())).sum());
// 전체 갯수 구하기
cntVO.setTotCnt(cntVO.getWaitCnt() + cntVO.getSuccCnt() + cntVO.getFailCnt() + cntVO.getCnclCnt());
return cntVO;
}
/**
* 예약관리 탭 리스트 화면
* @param searchVO
* @param model
* @return "/web/mjon/reservmsg/selectReservMsgListViewAjax.do"
* @throws Exception
*/
@RequestMapping(value= {"/web/mjon/reservmsg/selectReservMsgListViewAjax.do"})
public String selectReservMsgListViewAjax(@ModelAttribute("searchVO") MjonResvMsgVO mjonResvMsgVO, ModelMap model) throws Exception{
//로그인 권한정보 불러오기
LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null;
String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId());
mjonResvMsgVO.setUserId(userId);
// 검색 리스트 불러오기
if(mjonResvMsgVO.getPageUnit() != 10) {
mjonResvMsgVO.setPageUnit(mjonResvMsgVO.getPageUnit());
}
//기본 내림차순 정렬
if(mjonResvMsgVO.getSearchSortOrd().equals("")) {
mjonResvMsgVO.setSearchSortOrd("desc");
mjonResvMsgVO.setSearchSortCnd("regdate");
}
//선택 탭 정보 저장
//mjonResvMsgVO.setSearchMsgType(mjonResvMsgVO.getTabType());
/** pageing */
PaginationInfo paginationInfo = new PaginationInfo();
paginationInfo.setCurrentPageNo(mjonResvMsgVO.getPageIndex());
paginationInfo.setRecordCountPerPage(mjonResvMsgVO.getPageUnit());
paginationInfo.setPageSize(mjonResvMsgVO.getPageSize());
mjonResvMsgVO.setFirstIndex(paginationInfo.getFirstRecordIndex());
mjonResvMsgVO.setLastIndex(paginationInfo.getLastRecordIndex());
mjonResvMsgVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage());
//전체 발송 리스트 불러오기
List<MjonResvMsgVO> resultAllResList = mjonReservMsgService.selectAllResvMsgList(mjonResvMsgVO);
model.addAttribute("resultAllResList", resultAllResList);
model.addAttribute("resultAllResCnt", resultAllResList.size());
model.addAttribute("searchKeyword", mjonResvMsgVO.getSearchKeyword());
paginationInfo.setTotalRecordCount( resultAllResList.size()> 0 ? (Integer.parseInt((resultAllResList.get(0)).getTotMsgCnt())) : 0);
model.addAttribute("paginationInfo", paginationInfo);
model.addAttribute("totalRecordCount", paginationInfo.getTotalRecordCount());
//전체, 발송 완료 탭의 발송 결과 건수 리스트 불러오기
List<MjonResvMsgVO> resultMsgSucFailList = mjonReservMsgService.selectAllResvMsgSucFailList(resultAllResList);
model.addAttribute("resultMsgSucFailList", resultMsgSucFailList);
model.addAttribute("mjonResvMsgVO", mjonResvMsgVO);
String stateType = mjonResvMsgVO.getStateType();
String pageUrl = "web/reservedmsg/ReservedMsgAllListAjax";
if(stateType.equals("ready")) {
pageUrl = "web/reservedmsg/ReservedMsgReadyListAjax";
}else if(stateType.equals("complete")) {
pageUrl = "web/reservedmsg/ReservedMsgCompleteListAjax";
}else if(stateType.equals("cancel")) {
pageUrl = "web/reservedmsg/ReservedMsgCancelListAjax";
}
return pageUrl;
}
/**
* 예약관리 문자 선택 삭제 기능
* @param searchVO
* @param model
* @return "web/mjon/reservmsg/selectReservAllMsgListAjax.do"
* @throws Exception
*/
@RequestMapping(value= {"/web/mjon/reservmsg/deleteReservMsgDataAjax.do"})
public String deleteReservMsgDataAjax(@ModelAttribute("searchVO") MjonResvMsgVO mjonResvMsgVO, ModelMap model) throws Exception{
//로그인 권한정보 불러오기
LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null;
String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId());
mjonResvMsgVO.setUserId(userId);
//선택 문자 삭제 처리
int resultCnt = mjonReservMsgService.deleteReservMsgDataAjax(mjonResvMsgVO);
/*
*
* 리스트 화면 불러오기
*
* */
// 검색 리스트 불러오기
if(mjonResvMsgVO.getPageUnit() != 10) {
mjonResvMsgVO.setPageUnit(mjonResvMsgVO.getPageUnit());
}
//기본 내림차순 정렬
if(mjonResvMsgVO.getSearchSortOrd().equals("")) {
mjonResvMsgVO.setSearchSortOrd("desc");
}
//기본 등록일자 정렬
if(mjonResvMsgVO.getSearchSortCnd().equals("")) {
mjonResvMsgVO.setSearchSortCnd("regdate");
}
/** pageing */
PaginationInfo paginationInfo = new PaginationInfo();
paginationInfo.setCurrentPageNo(mjonResvMsgVO.getPageIndex());
paginationInfo.setRecordCountPerPage(mjonResvMsgVO.getPageUnit());
paginationInfo.setPageSize(mjonResvMsgVO.getPageSize());
mjonResvMsgVO.setFirstIndex(paginationInfo.getFirstRecordIndex());
mjonResvMsgVO.setLastIndex(paginationInfo.getLastRecordIndex());
mjonResvMsgVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage());
//전체 발송 리스트 불러오기
List<MjonResvMsgVO> resultAllResList = mjonReservMsgService.selectAllResvMsgList(mjonResvMsgVO);
model.addAttribute("resultAllResList", resultAllResList);
model.addAttribute("resultAllResCnt", resultAllResList.size());
model.addAttribute("searchKeyword", mjonResvMsgVO.getSearchKeyword());
paginationInfo.setTotalRecordCount( resultAllResList.size()> 0 ? (Integer.parseInt((resultAllResList.get(0)).getTotMsgCnt())) : 0);
model.addAttribute("paginationInfo", paginationInfo);
model.addAttribute("totalRecordCount", paginationInfo.getTotalRecordCount());
model.addAttribute("mjonResvMsgVO", mjonResvMsgVO);
return "web/reservedmsg/ReservedMsgAllListAjax";
}
/**
* 예약관리 문자 상세보기 내용
* @param searchVO
* @param model
* @return "/web/mjon/reservmsg/selectReservMsgDetailDataAjax.do"
* @throws Exception
*/
@RequestMapping(value= {"/web/mjon/reservmsg/selectReservMsgDetailDataAjax.do"})
public String selectReservMsgDetailDataAjax(@ModelAttribute("searchVO") MjonResvMsgVO mjonResvMsgVO, ModelMap model) throws Exception{
//로그인 권한정보 불러오기
LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null;
String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId());
mjonResvMsgVO.setUserId(userId);
//문자 상세보기 처리
MjonResvMsgVO resultMsgDetail = mjonReservMsgService.selectReservMsgDetailDataAjax(mjonResvMsgVO);
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/reservedmsg/ReservedMsgDetailPopAjax";
}
/**
* 예약 문자 취소 처리 기능
* @param searchVO
* @param model
* @return "/web/mjon/reservmsg/deleteReservMsgCancelDataAjax.do"
* @throws Exception
*/
@RequestMapping(value= {"/web/mjon/reservmsg/deleteReservMsgCancelDataAjax.do"})
public ModelAndView deleteReservMsgCancelDataAjax(MjonResvMsgVO mjonResvMsgVO,
RedirectAttributes redirectAttributes,
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("")) {
mjonResvMsgVO.setUserId(userId);
}else {
modelAndView.addObject("message", "로그인 후 이용이 가능합니다.");
modelAndView.addObject("result", "fail");
return modelAndView;
}
// 디비에 문자 내용을 저장해 준다.
int resultSts = mjonReservMsgService.deleteReservMsgCancelDataAjax(mjonResvMsgVO);
if(resultSts > 0) {
modelAndView.addObject("message", "예약 발송이 정상적으로 취소 되었습니다.");
modelAndView.addObject("result", "success");
}else {
modelAndView.addObject("message", "예약 발송 처리가 실패 되었습니다.");
modelAndView.addObject("result", "fail");
}
return modelAndView;
}
/**
* 예약관리 출력하기 팝업화면
*
* @param MjonMsgSentVO
* @param
* @param sessionVO
* @param model
* @return
* @throws Exception
*/
@RequestMapping(value= {"/web/mjon/reservmsg/printReservMsgDataAjax.do"})
public String printReservMsgDataAjax(
@ModelAttribute("searchVO") MjonResvMsgVO mjonResvMsgVO,
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 {
mjonResvMsgVO.setUserId(userId);
}
// 검색 리스트 불러오기
if(mjonResvMsgVO.getPageUnit() != 10) {
mjonResvMsgVO.setPageUnit(mjonResvMsgVO.getPageUnit());
}
//기본 내림차순 정렬
if(mjonResvMsgVO.getSearchSortOrd().equals("")) {
mjonResvMsgVO.setSearchSortOrd("desc");
mjonResvMsgVO.setSearchSortCnd("regdate");
}
//선택 탭 정보 저장
//mjonResvMsgVO.setSearchMsgType(mjonResvMsgVO.getTabType());
/** pageing */
PaginationInfo paginationInfo = new PaginationInfo();
paginationInfo.setCurrentPageNo(mjonResvMsgVO.getPageIndex());
paginationInfo.setRecordCountPerPage(mjonResvMsgVO.getPageUnit());
paginationInfo.setPageSize(mjonResvMsgVO.getPageSize());
mjonResvMsgVO.setFirstIndex(paginationInfo.getFirstRecordIndex());
mjonResvMsgVO.setLastIndex(paginationInfo.getLastRecordIndex());
mjonResvMsgVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage());
//전체 발송 리스트 불러오기
List<MjonResvMsgVO> resultAllResList = mjonReservMsgService.selectAllResvMsgList(mjonResvMsgVO);
model.addAttribute("resultAllSentList", resultAllResList);
model.addAttribute("resultAllSentCnt", resultAllResList.size());
model.addAttribute("searchKeyword", mjonResvMsgVO.getSearchKeyword());
paginationInfo.setTotalRecordCount( resultAllResList.size()> 0 ? (Integer.parseInt((resultAllResList.get(0)).getTotMsgCnt())) : 0);
model.addAttribute("paginationInfo", paginationInfo);
model.addAttribute("totalRecordCount", paginationInfo.getTotalRecordCount());
//발송 결과 성공 실패 건수 리스트 불러오기
List<MjonResvMsgVO> resultMsgSucFailList = mjonReservMsgService.selectAllResvMsgSucFailList(resultAllResList);
model.addAttribute("resultMsgSucFailList", resultMsgSucFailList);
model.addAttribute("mjonMsgSentVO", mjonResvMsgVO);
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/reservedmsg/ReservedMsgPrintListPopUp";
}
/**
* 예약관리 엑셀다운로드 기능
* @param searchVO
* @param model
* @return "/web/mjon/reservmsg/reservMsgExcelDownLoadAjax.do"
* @throws Exception
*/
@RequestMapping(value= {"/web/mjon/reservmsg/reservMsgExcelDownLoadAjax.do"})
public void reservMsgExcelDownLoadAjax(MjonResvMsgVO mjonResvMsgVO,
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("")) {
mjonResvMsgVO.setUserId(userId);
}else {
/*modelAndView.addObject("message", "로그인 후 이용이 가능합니다.");
modelAndView.addObject("result", "fail");
return modelAndView;*/
}
String stateType = mjonResvMsgVO.getStateType();
String tabType = mjonResvMsgVO.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{
mjonResvMsgVO.setRecordCountPerPage(100000);
mjonResvMsgVO.setFirstIndex(0);
if("".equals(mjonResvMsgVO.getSearchSortCnd())){ //최초조회시 최신것 조회List
mjonResvMsgVO.setSearchSortCnd("regdate");
mjonResvMsgVO.setSearchSortOrd("desc");
}
//예약 관리 리스트 불러오기
List<MjonResvMsgVO> resultAllResList = mjonReservMsgService.selectAllResvMsgList(mjonResvMsgVO);
//전체, 발송 완료 탭의 발송 결과 건수 리스트 불러오기
List<MjonResvMsgVO> resultMsgSucFailList = mjonReservMsgService.selectAllResvMsgSucFailList(resultAllResList);
int msgSFsize = resultMsgSucFailList.size();
//발송 성공,실패 건수 불러오기
{//화면 리스트
row = sheet.createRow(0);
if(stateType.equals("all")) {
sheet.setColumnWidth(1, 5000); // 발송형태 칼럼의 폭 조절
sheet.setColumnWidth(2, 5000); // 접수일자 칼럼의 폭 조절
sheet.setColumnWidth(3, 5000); // 예약일자 칼럼의 폭 조절
sheet.setColumnWidth(5, 7000); // 제목 칼럼의 폭 조절
sheet.setColumnWidth(6, 10000); // 내용 칼럼의 폭 조절
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)); //발신번호 셀병합
sheet.addMergedRegion(new CellRangeAddress(0,1,9,9)); //금액 세로 세로 셀병합
sheet.addMergedRegion(new CellRangeAddress(0,1,12,12)); //예약 취소 세로 셀병합
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("금액");
cell.setCellStyle(style);
cell = row.createCell(10);
cell.setCellValue("발송결과 건수");
sheet.addMergedRegion(new CellRangeAddress(0,0,10,11)); // 발송결과 건수 가로 셀병합
cell.setCellStyle(style);
cell = row.createCell(11);
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.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.setCellStyle(style);
}else {
if(stateType.equals("complete")) {
sheet.setColumnWidth(1, 5000); // 접수일자 칼럼의 폭 조절
sheet.setColumnWidth(2, 5000); // 예약일자 칼럼의 폭 조절
sheet.setColumnWidth(4, 7000); // 제목 칼럼의 폭 조절
sheet.setColumnWidth(5, 10000); // 내용 칼럼의 폭 조절
sheet.setColumnWidth(7, 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);
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);
}else if(stateType.equals("ready")) {
sheet.setColumnWidth(1, 5000); // 접수일자 칼럼의 폭 조절
sheet.setColumnWidth(2, 5000); // 예약일자 칼럼의 폭 조절
sheet.setColumnWidth(4, 7000); // 제목 칼럼의 폭 조절
sheet.setColumnWidth(5, 10000); // 내용 칼럼의 폭 조절
sheet.setColumnWidth(7, 5000); // 내용 칼럼의 폭 조절
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("예약취소");
cell.setCellStyle(style);
}else if(stateType.equals("cancel")) {
sheet.setColumnWidth(1, 5000); // 접수일자 칼럼의 폭 조절
sheet.setColumnWidth(2, 5000); // 예약일자 칼럼의 폭 조절
sheet.setColumnWidth(4, 7000); // 제목 칼럼의 폭 조절
sheet.setColumnWidth(5, 10000); // 내용 칼럼의 폭 조절
sheet.setColumnWidth(7, 5000); // 내용 칼럼의 폭 조절
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);
}
}
DecimalFormat formatter = new DecimalFormat("###,###");
String totPriceStr = "";
for(int i=0; i < resultAllResList.size(); i++) {
totPriceStr = formatter.format((int)Double.parseDouble(((resultAllResList.get(i)).getTotPrice())));
String msgType = "단문";
if(resultAllResList.get(i).getMsgType().equals("6") && resultAllResList.get(i).getFileCnt().equals("0")) {
msgType = "장문";
}else if(resultAllResList.get(i).getMsgType().equals("6") && !resultAllResList.get(i).getFileCnt().equals("0")) {
msgType = "그림";
}
int excelLen = 0;
if(stateType.equals("all")) {
row = sheet.createRow(i+2);
excelLen = 13;
}else if(stateType.equals("ready")) {
row = sheet.createRow(i+2);
excelLen = 10;
}else if(stateType.equals("complete")) {
row = sheet.createRow(i+2);
excelLen = 11;
}else {
row = sheet.createRow(i+1);
excelLen = 9;
}
for(int j=0 ; j < excelLen ; j++) {
cell = row.createCell(j);
cell.setCellStyle(style);
if(stateType.equals("all")) {
if(j==0) cell.setCellValue(i+1); //번호
if(j==1) { //발송상태 처리해주기
String resvCYn = resultAllResList.get(i).getReserveCYn();
String curState = resultAllResList.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==2) cell.setCellValue(sdf.format((resultAllResList.get(i)).getRegdate())); //접수일자
if(j==3) cell.setCellValue(sdf.format((resultAllResList.get(i)).getReqdate())); //발송일자
if(j==4) {
type = resultAllResList.get(i).getMsgType();
fCnt = resultAllResList.get(i).getFileCnt();
if(type.equals("4")) {
cell.setCellValue("단문"); //형태
}else {
if(fCnt.equals("0")) {
cell.setCellValue("장문"); //형태
}else {
cell.setCellValue("그림"); //형태
}
}
}
if(j==5) {
if(resultAllResList.get(i).getSubject() == null) {
cell.setCellValue("제목 없음"); //제목
}else {
cell.setCellValue((resultAllResList.get(i)).getSubject()); //제목
}
}
if(j==6) cell.setCellValue((resultAllResList.get(i)).getSmsTxt()); //내용
if(j==7) cell.setCellValue((resultAllResList.get(i)).getMsgGroupCnt()); //발송건수
if(j==8) cell.setCellValue((resultAllResList.get(i)).getCallFrom()); //발신번호
if(j==9) cell.setCellValue(totPriceStr); //금액
//발송결과 성공, 실패 처리
String resSucCnt = "0";
String resFailCnt = "0";
for(int k=0; k < msgSFsize; k++) {
if(resultAllResList.get(i).getMsgGroupId().equals(resultMsgSucFailList.get(k).getMsgGroupId())) {
if(resultMsgSucFailList.get(k).getMsgResultSts().equals("S")) {//발송 성공 건수
resSucCnt = resultMsgSucFailList.get(k).getMsgResultCnt();
}else if(resultMsgSucFailList.get(k).getMsgResultSts().equals("W")){//발송 대기 건수
resFailCnt = "0";
}else {
resFailCnt = resultMsgSucFailList.get(k).getMsgResultCnt();
}
}
}
if(j==10) cell.setCellValue(resSucCnt); //발송결과 성공
if(j==11) cell.setCellValue(resFailCnt); //발송결과 실패
if(j==12) cell.setCellValue((resultAllResList.get(i)).getReserveCYn()); //예약 취소
}else if(stateType.equals("ready")) {
if(j==0) cell.setCellValue(i+1); //번호
if(j==1) cell.setCellValue(sdf.format((resultAllResList.get(i)).getRegdate())); //접수일자
if(j==2) cell.setCellValue(sdf.format((resultAllResList.get(i)).getReqdate())); //발송일자
if(j==3) {
type = resultAllResList.get(i).getMsgType();
fCnt = resultAllResList.get(i).getFileCnt();
if(type.equals("4")) {
cell.setCellValue("단문"); //형태
}else {
if(fCnt.equals("0")) {
cell.setCellValue("장문"); //형태
}else {
cell.setCellValue("그림"); //형태
}
}
} //형태
if(j==4) {
if(resultAllResList.get(i).getSubject() == null) {
cell.setCellValue("제목 없음"); //제목
}else {
cell.setCellValue((resultAllResList.get(i)).getSubject()); //제목
}
}
if(j==5) cell.setCellValue((resultAllResList.get(i)).getSmsTxt()); //내용
if(j==6) cell.setCellValue((resultAllResList.get(i)).getMsgGroupCnt()); //발송건수
if(j==7) cell.setCellValue((resultAllResList.get(i)).getCallFrom()); //발신번호
if(j==8) cell.setCellValue(totPriceStr); //금액
if(j==9) cell.setCellValue((resultAllResList.get(i)).getReserveCYn()); //예약 취소
}else if(stateType.equals("complete")) {
if(j==0) cell.setCellValue(i+1); //번호
if(j==1) cell.setCellValue(sdf.format((resultAllResList.get(i)).getRegdate())); //접수일자
if(j==2) cell.setCellValue(sdf.format((resultAllResList.get(i)).getReqdate())); //발송일자
if(j==3) {
type = resultAllResList.get(i).getMsgType();
fCnt = resultAllResList.get(i).getFileCnt();
if(type.equals("4")) {
cell.setCellValue("단문"); //형태
}else {
if(fCnt.equals("0")) {
cell.setCellValue("장문"); //형태
}else {
cell.setCellValue("그림"); //형태
}
}
}
if(j==4) {
if(resultAllResList.get(i).getSubject() == null) {
cell.setCellValue("제목 없음"); //제목
}else {
cell.setCellValue((resultAllResList.get(i)).getSubject()); //제목
}
}
if(j==5) cell.setCellValue((resultAllResList.get(i)).getSmsTxt()); //내용
if(j==6) cell.setCellValue((resultAllResList.get(i)).getMsgGroupCnt()); //발송건수
if(j==7) cell.setCellValue((resultAllResList.get(i)).getCallFrom()); //발신번호
if(j==8) cell.setCellValue(totPriceStr); //금액
//발송결과 성공, 실패 처리
String resSucCnt = "0";
String resFailCnt = "0";
for(int l=0; l < msgSFsize; l++) {
if(resultAllResList.get(i).getMsgGroupId().equals(resultMsgSucFailList.get(l).getMsgGroupId())) {
if(resultMsgSucFailList.get(l).getMsgResultSts().equals("S")) {//발송 성공 건수
resSucCnt = resultMsgSucFailList.get(l).getMsgResultCnt();
}else if(resultMsgSucFailList.get(l).getMsgResultSts().equals("W")){//발송 대기 건수
resFailCnt = "0";
}else {
resFailCnt = resultMsgSucFailList.get(l).getMsgResultCnt();
}
}
}
if(j==9) cell.setCellValue(resSucCnt); //발송결과 성공
if(j==10) cell.setCellValue(resFailCnt); //발송결과 실패
}else { //예약 취소
if(j==0) cell.setCellValue(i+1); //번호
if(j==1) cell.setCellValue(sdf.format((resultAllResList.get(i)).getRegdate())); //접수일자
if(j==2) cell.setCellValue(sdf.format((resultAllResList.get(i)).getReqdate())); //발송일자
if(j==3) {
type = resultAllResList.get(i).getMsgType();
fCnt = resultAllResList.get(i).getFileCnt();
if(type.equals("4")) {
cell.setCellValue("단문"); //형태
}else {
if(fCnt.equals("0")) {
cell.setCellValue("장문"); //형태
}else {
cell.setCellValue("그림"); //형태
}
}
}
if(j==4) {
if(resultAllResList.get(i).getSubject() == null) {
cell.setCellValue("제목 없음"); //제목
}else {
cell.setCellValue((resultAllResList.get(i)).getSubject()); //제목
}
}
if(j==5) cell.setCellValue((resultAllResList.get(i)).getSmsTxt()); //내용
if(j==6) cell.setCellValue((resultAllResList.get(i)).getMsgGroupCnt()); //발송건수
if(j==7) cell.setCellValue((resultAllResList.get(i)).getCallFrom()); //발신번호
if(j==8) cell.setCellValue(totPriceStr); //금액
}
}
}
}
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) {}
}
}
/**
* 예약관리 화면 - 카카오톡
* @param searchVO
* @param model
* @return "/web/mjon/reservmsg/selectReservKaKaoView.do"
* @throws Exception
*/
@RequestMapping(value= {"/web/mjon/reservmsg/selectReservKaKaoView.do"})
public String selectReservKaKaoView(@ModelAttribute("searchVO") KakaoSentVO kakaoSentVO,
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) {
return "redirect:/web/user/login/login.do";
}
kakaoSentVO.setUserId(userId);
//전체 발송 건수 통계 불러오기
kakaoSentVO.setMsgType("");
System.out.println("kakaoSentVO.getNtceBgnde() : "+ kakaoSentVO.getNtceBgnde());
List<KakaoSentVO> totalMsgCnt = kakaoSentService.selectKakaoReservSentCntAll(kakaoSentVO);
model.addAttribute("totalMsgCnt", totalMsgCnt);
List<KakaoSentVO> atCnt = new ArrayList<KakaoSentVO>();
List<KakaoSentVO> ftCnt = new ArrayList<KakaoSentVO>();
totalMsgCnt.forEach(t->{
if (Integer.parseInt(t.getFilePath1())>0) {
atCnt.add(t);
} else if (Integer.parseInt(t.getFilePath2())>0) {
ftCnt.add(t);
}
});
//알림톡 성공건, 실패건 불러오기
model.addAttribute("atCnt", atCnt);
//친구톡 성공건, 실패건 불러오기
model.addAttribute("ftCnt", ftCnt);
// 검색 리스트 불러오기
if(kakaoSentVO.getPageUnit() != 10) kakaoSentVO.setPageUnit(kakaoSentVO.getPageUnit());
//기본 내림차순 정렬
if(kakaoSentVO.getSearchSortOrd().equals("")) kakaoSentVO.setSearchSortOrd("desc");
//기본 등록일자 정렬
if(kakaoSentVO.getSearchSortCnd().equals("")) kakaoSentVO.setSearchSortCnd("regdate");
model.addAttribute("searchKeyword", kakaoSentVO.getSearchKeyword());
model.addAttribute("kakaoSentVO", kakaoSentVO);
model.addAttribute("siteId", "tabKakao");
return "web/reservedmsg/ReservedKakaoView";
}
/**
* 예약관리 탭 리스트 화면 - 카카오톡
* @param searchVO
* @param model
* @return "/web/mjon/reservmsg/selectReservKakaoListViewAjax.do"
* @throws Exception
*/
@RequestMapping(value= {"/web/mjon/reservmsg/selectReservKakaoListViewAjax.do"})
public String selectReservKakaoListViewAjax(
@ModelAttribute("searchVO") MjonResvMsgVO mjonResvMsgVO
, @ModelAttribute("kakaoVO") KakaoSentVO kakaoSentVO
, ModelMap model
, HttpServletResponse response) throws Exception{
//로그인 권한정보 불러오기
LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null;
String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId());
if("".equals(userId)) {
PrintWriter out = response.getWriter();
response.setContentType("text/html; charset=UTF-8");
out.println("<script>alert('문자온 서비스는 로그인 후 이용 가능합니다.'); location.href='/web/user/login/login.do';</script>");
out.close();
}else {
mjonResvMsgVO.setUserId(userId);
// 검색 리스트 불러오기
if(kakaoSentVO.getPageUnit() != 10) {
kakaoSentVO.setPageUnit(mjonResvMsgVO.getPageUnit());
}
//기본 내림차순 정렬
if(kakaoSentVO.getSearchSortOrd().equals("")) {
kakaoSentVO.setSearchSortOrd("desc");
kakaoSentVO.setSearchSortCnd("regdate");
}
//선택 탭 정보 저장
//mjonResvMsgVO.setSearchMsgType(mjonResvMsgVO.getTabType());
/** pageing */
PaginationInfo paginationInfo = new PaginationInfo();
paginationInfo.setCurrentPageNo(kakaoSentVO.getPageIndex());
paginationInfo.setRecordCountPerPage(kakaoSentVO.getPageUnit());
paginationInfo.setPageSize(kakaoSentVO.getPageSize());
kakaoSentVO.setFirstIndex(paginationInfo.getFirstRecordIndex());
kakaoSentVO.setLastIndex(paginationInfo.getLastRecordIndex());
kakaoSentVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage());
//전체 발송 리스트 불러오기
// List<MjonResvMsgVO> resultAllResList = mjonReservMsgService.selectAllResvMsgList(mjonResvMsgVO);
kakaoSentVO.setUserId(userId);
//예약관리는 그룹데이터로 조회
kakaoSentVO.setListType("groupList");
List<KakaoSentVO> resultAllResList = kakaoSentService.selectReservKakaoSentList(kakaoSentVO);
model.addAttribute("resultAllResList", resultAllResList);
model.addAttribute("resultAllResCnt", resultAllResList.size());
model.addAttribute("searchKeyword", mjonResvMsgVO.getSearchKeyword());
paginationInfo.setTotalRecordCount( resultAllResList.size()> 0 ? (Integer.parseInt((resultAllResList.get(0)).getTotMsgCnt())) : 0);
model.addAttribute("paginationInfo", paginationInfo);
model.addAttribute("totalRecordCount", paginationInfo.getTotalRecordCount());
//전체, 발송 완료 탭의 발송 결과 건수 리스트 불러오기
// List<MjonResvMsgVO> resultMsgSucFailList = mjonReservMsgService.selectAllResvMsgSucFailList(resultAllResList);
// model.addAttribute("resultMsgSucFailList", resultMsgSucFailList);
model.addAttribute("mjonResvMsgVO", mjonResvMsgVO);
String stateType = mjonResvMsgVO.getStateType();
//전체
String pageUrl = "web/reservedmsg/ReservedKakaoAllListAjax";
//발송대기
if(stateType.equals("ready")) {
pageUrl = "web/reservedmsg/ReservedKakaoReadyListAjax";
}
//발송완료
else if(stateType.equals("complete")) {
pageUrl = "web/reservedmsg/ReservedKakaoCompleteListAjax";
}
//예약취소
else if(stateType.equals("cancel")) {
pageUrl = "web/reservedmsg/ReservedKakaoCancelListAjax";
}
return pageUrl;
}
return "web/reservedmsg/ReservedKakaoAllListAjax";
}
/**
* @Method Name : deleteReservKakaoDataAjax
* @작성일 : 2023. 4. 6.
* @작성자 : WYH
* @Method 설명 : 카카오 예약관리 예약선택 취소
*/
@RequestMapping(value= {"/web/mjon/reservmsg/deleteReservKakaoDataAjax.do"})
public String deleteReservKakaoDataAjax(
@ModelAttribute("searchVO") MjonResvMsgVO mjonResvMsgVO
, @ModelAttribute("kakaoVO") KakaoSentVO kakaoSentVO
, ModelMap model
, HttpServletResponse response) throws Exception{
//로그인 권한정보 불러오기
LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null;
String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId());
if("".equals(userId)) {
PrintWriter out = response.getWriter();
response.setContentType("text/html; charset=UTF-8");
out.println("<script>alert('문자온 서비스는 로그인 후 이용 가능합니다.'); location.href='/web/user/login/login.do';</script>");
out.close();
}else {
mjonResvMsgVO.setUserId(userId);
//선택 문자 삭제 처리
int resultCnt = mjonReservMsgService.deleteReservMsgDataAjax(mjonResvMsgVO);
/*
*
* 리스트 화면 불러오기
*
* */
// 검색 리스트 불러오기
if(kakaoSentVO.getPageUnit() != 10) {
kakaoSentVO.setPageUnit(mjonResvMsgVO.getPageUnit());
}
//기본 내림차순 정렬
if(kakaoSentVO.getSearchSortOrd().equals("")) {
kakaoSentVO.setSearchSortOrd("desc");
kakaoSentVO.setSearchSortCnd("regdate");
}
/** pageing */
PaginationInfo paginationInfo = new PaginationInfo();
paginationInfo.setCurrentPageNo(kakaoSentVO.getPageIndex());
paginationInfo.setRecordCountPerPage(kakaoSentVO.getPageUnit());
paginationInfo.setPageSize(kakaoSentVO.getPageSize());
kakaoSentVO.setFirstIndex(paginationInfo.getFirstRecordIndex());
kakaoSentVO.setLastIndex(paginationInfo.getLastRecordIndex());
kakaoSentVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage());
//전체 발송 리스트 불러오기
kakaoSentVO.setUserId(userId);
kakaoSentVO.setListType("groupList");
List<KakaoSentVO> resultAllResList = kakaoSentService.selectReservKakaoSentList(kakaoSentVO);
model.addAttribute("resultAllResList", resultAllResList);
model.addAttribute("resultAllResCnt", resultAllResList.size());
model.addAttribute("searchKeyword", mjonResvMsgVO.getSearchKeyword());
paginationInfo.setTotalRecordCount( resultAllResList.size()> 0 ? (Integer.parseInt((resultAllResList.get(0)).getTotMsgCnt())) : 0);
model.addAttribute("paginationInfo", paginationInfo);
model.addAttribute("totalRecordCount", paginationInfo.getTotalRecordCount());
model.addAttribute("mjonResvMsgVO", mjonResvMsgVO);
}
return "web/reservedmsg/ReservedKakaoAllListAjax";
}
/**
* 예약관리 엑셀다운로드 기능 - 카카오톡
* @param searchVO
* @param model
* @return "/web/mjon/reservmsg/reservKakaoExcelDownLoadAjax.do"
* @throws Exception
*/
@RequestMapping(value= {"/web/mjon/reservmsg/reservKakaoExcelDownLoadAjax.do"})
public void reservKakaoExcelDownLoadAjax(
KakaoSentVO kakaoSentVO,
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("")) {
kakaoSentVO.setUserId(userId);
String stateType = kakaoSentVO.getStateType();
String tabType = kakaoSentVO.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);
CellStyle styleLastCell = wb.createCellStyle();
styleLastCell.setBorderLeft(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{
kakaoSentVO.setRecordCountPerPage(100000);
kakaoSentVO.setFirstIndex(0);
if("".equals(kakaoSentVO.getSearchSortCnd())){ //최초조회시 최신것 조회List
kakaoSentVO.setSearchSortCnd("regdate");
kakaoSentVO.setSearchSortOrd("desc");
}
//발송결과-카카오톡 리스트 불러오기
//예약관리는 그룹데이터로 조회
kakaoSentVO.setListType("groupList");
kakaoSentVO.setReserveYn("Y");
/*List<KakaoSentVO> resultAllSentList = kakaoSentService.selectAllKakaoSentList(kakaoSentVO);*/
List<KakaoSentVO> resultAllSentList = kakaoSentService.selectReservKakaoSentList(kakaoSentVO);
if("ready".equals(kakaoSentVO.getStateType())) {
kakaoSentExcelReservReady(resultAllSentList, row, sheet, cell, style, styleLastCell, type, fCnt, sdf);
}else if("complete".equals(kakaoSentVO.getStateType())) {
kakaoSentExcelReservComplete(resultAllSentList, row, sheet, cell, style, styleLastCell, type, fCnt, sdf);
}else if("cancel".equals(kakaoSentVO.getStateType())) {
kakaoSentExcelReservCancel(resultAllSentList, row, sheet, cell, style, styleLastCell, type, fCnt, sdf);
}else {
kakaoSentExcelReservAll(resultAllSentList, row, sheet, cell, style, styleLastCell, type, fCnt, sdf);
}
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) {}
}
}else {
response.sendRedirect("/web/kakao/sent/selectKakaoSentView.do");
}
}
private void kakaoSentExcelReservAll(List<KakaoSentVO> resultAllSentList
, Row row
,Sheet sheet
,Cell cell
,CellStyle style
,CellStyle styleLastCell
,String type
,String fCnt
,SimpleDateFormat sdf) {
{//화면 리스트
row = sheet.createRow(0);
sheet.setColumnWidth(2, 5000); // 접수일자 칼럼의 폭 조절
sheet.setColumnWidth(3, 5000); // 예약일자 칼럼의 폭 조절
sheet.setColumnWidth(5, 10000); // 내용 칼럼의 폭 조절
sheet.setColumnWidth(7, 5000); // 발신번호 칼럼의 폭 조절
sheet.setColumnWidth(8, 5000); // 금액 칼럼의 폭 조절
sheet.setColumnWidth(13, 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)); //금액 셀병합
sheet.addMergedRegion(new CellRangeAddress(0,1,13,13)); //예약취소 셀병합
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(11);
cell.setCellValue("대체문자 건수");
sheet.addMergedRegion(new CellRangeAddress(0,0,11,12)); // 발송결과 건수 가로 셀병합
cell.setCellStyle(style);
cell = row.createCell(13);
cell.setCellValue("예약취소");
cell.setCellStyle(style);
cell = row.createCell(14);
cell.setCellStyle(styleLastCell);
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);
cell = row.createCell(13);
cell.setCellStyle(style);
cell = row.createCell(14);
cell.setCellStyle(styleLastCell);
}
for(int i=0; i < resultAllSentList.size(); i++) {
String msgType = "";
if(resultAllSentList.get(i).getMsgType().equals("8") && resultAllSentList.get(i).getFileCnt().equals("0")) {
msgType = "알림톡";
}else if(resultAllSentList.get(i).getMsgType().equals("9") && !resultAllSentList.get(i).getFileCnt().equals("0")) {
msgType = "친구톡";
}
int excelLen = 0;
row = sheet.createRow(i+2);
excelLen = 14;
for(int j=0 ; j < excelLen ; j++) {
cell = row.createCell(j);
cell.setCellStyle(style);
if(j==0) cell.setCellValue(i+1); //번호
if(j==1) { //발송상태
if("Y".equals(resultAllSentList.get(i).getReserveCYn())) {
cell.setCellValue("예약취소");
}else {
if("0".equals(resultAllSentList.get(i).getCurState())) cell.setCellValue("발송대기");
if("1".equals(resultAllSentList.get(i).getCurState())) cell.setCellValue("발송중");
if("2".equals(resultAllSentList.get(i).getCurState())) cell.setCellValue("결과대기");
if("3".equals(resultAllSentList.get(i).getCurState())) cell.setCellValue("발송완료");
}
}
if(j==2) cell.setCellValue(sdf.format((resultAllSentList.get(i)).getRegdate())); //접수일자
if(j==3) cell.setCellValue(sdf.format((resultAllSentList.get(i)).getReqdate())); //예약일자
if(j==4) {
cell.setCellValue(resultAllSentList.get(i).getMsgTypeName());
}
if(j==5) cell.setCellValue((resultAllSentList.get(i)).getSmsTxt()); //내용
if(j==6) cell.setCellValue((resultAllSentList.get(i)).getMsgGroupCnt()); //발송건수
if(j==7) cell.setCellValue((resultAllSentList.get(i)).getCallFrom()); //발신번호
if(j==8) cell.setCellValue((resultAllSentList.get(i)).getTotPrice()); //발신번호
//발송결과 성공, 실패 처리
if(j==9) cell.setCellValue(resultAllSentList.get(i).getSuccessCount()); //알림톡 발송결과 성공
if(j==10) cell.setCellValue(resultAllSentList.get(i).getFailCount()); //알림톡 발송결과 실패
if(j==11) cell.setCellValue(resultAllSentList.get(i).getKakaoResendSuccCount()); //대체문자 발송결과 대기
if(j==12) cell.setCellValue(resultAllSentList.get(i).getKakaoResendFailCount()); //대체문자 발송결과 대기
if(j==13) {
if(resultAllSentList.get(i).getCancelDate() != null) {
cell.setCellValue(sdf.format(resultAllSentList.get(i).getCancelDate())); //대체문자 발송결과 대기
}else {
cell.setCellValue("-"); //대체문자 발송결과 대기
}
}
}
}
}
private void kakaoSentExcelReservReady(List<KakaoSentVO> resultAllSentList
, Row row
,Sheet sheet
,Cell cell
,CellStyle style
,CellStyle styleLastCell
,String type
,String fCnt
,SimpleDateFormat sdf) {
{// 화면 리스트
row = sheet.createRow(0);
sheet.setColumnWidth(1, 5000); // 접수일자 칼럼의 폭 조절
sheet.setColumnWidth(2, 5000); // 예약일자 칼럼의 폭 조절
sheet.setColumnWidth(4, 10000); // 내용 칼럼의 폭 조절
sheet.setColumnWidth(6, 5000); // 발신번호 칼럼의 폭 조절
sheet.setColumnWidth(7, 5000); // 금액 칼럼의 폭 조절
sheet.setColumnWidth(8, 5000); // 예약취소 칼럼의 폭 조절
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);
}
for (int i = 0; i < resultAllSentList.size(); i++) {
String msgType = "";
if (resultAllSentList.get(i).getMsgType().equals("8")
&& resultAllSentList.get(i).getFileCnt().equals("0")) {
msgType = "알림톡";
} else if (resultAllSentList.get(i).getMsgType().equals("9")
&& !resultAllSentList.get(i).getFileCnt().equals("0")) {
msgType = "친구톡";
}
int excelLen = 0;
row = sheet.createRow(i + 1);
excelLen = 9;
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)).getRegdate())); // 접수일자
if (j == 2)
cell.setCellValue(sdf.format((resultAllSentList.get(i)).getReqdate())); // 예약일자
if (j == 3) {
cell.setCellValue(resultAllSentList.get(i).getMsgTypeName());
}
if (j == 4)
cell.setCellValue((resultAllSentList.get(i)).getSmsTxt()); // 내용
if (j == 5)
cell.setCellValue((resultAllSentList.get(i)).getMsgGroupCnt()); // 발송건수
if (j == 6)
cell.setCellValue((resultAllSentList.get(i)).getCallFrom()); // 발신번호
if (j == 7)
cell.setCellValue((resultAllSentList.get(i)).getTotPrice()); // 발신번호
if (j == 8) {
if (resultAllSentList.get(i).getCancelDate() != null) {
cell.setCellValue(sdf.format(resultAllSentList.get(i).getCancelDate())); // 대체문자 발송결과 대기
} else {
cell.setCellValue("-"); // 대체문자 발송결과 대기
}
}
}
}
}
private void kakaoSentExcelReservComplete(List<KakaoSentVO> resultAllSentList
, Row row
,Sheet sheet
,Cell cell
,CellStyle style
,CellStyle styleLastCell
,String type
,String fCnt
,SimpleDateFormat sdf) {
{// 화면 리스트
row = sheet.createRow(0);
sheet.setColumnWidth(2, 5000); // 접수일자 칼럼의 폭 조절
sheet.setColumnWidth(3, 5000); // 예약일자 칼럼의 폭 조절
sheet.setColumnWidth(5, 10000); // 내용 칼럼의 폭 조절
sheet.setColumnWidth(7, 5000); // 발신번호 칼럼의 폭 조절
sheet.setColumnWidth(8, 5000); // 금액 칼럼의 폭 조절
sheet.setColumnWidth(13, 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(11);
cell.setCellValue("대체문자 건수");
sheet.addMergedRegion(new CellRangeAddress(0, 0, 11, 12)); // 발송결과 건수 가로 셀병합
cell.setCellStyle(style);
cell = row.createCell(13);
cell.setCellStyle(styleLastCell);
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);
cell = row.createCell(13);
cell.setCellStyle(styleLastCell);
}
for (int i = 0; i < resultAllSentList.size(); i++) {
String msgType = "";
if (resultAllSentList.get(i).getMsgType().equals("8")
&& resultAllSentList.get(i).getFileCnt().equals("0")) {
msgType = "알림톡";
} else if (resultAllSentList.get(i).getMsgType().equals("9")
&& !resultAllSentList.get(i).getFileCnt().equals("0")) {
msgType = "친구톡";
}
int excelLen = 0;
row = sheet.createRow(i + 2);
excelLen = 13;
for (int j = 0; j < excelLen; j++) {
cell = row.createCell(j);
cell.setCellStyle(style);
if (j == 0)
cell.setCellValue(i + 1); // 번호
if (j == 1) { // 발송상태
if ("Y".equals(resultAllSentList.get(i).getReserveCYn())) {
cell.setCellValue("예약취소");
} else {
if ("0".equals(resultAllSentList.get(i).getCurState()))
cell.setCellValue("발송대기");
if ("1".equals(resultAllSentList.get(i).getCurState()))
cell.setCellValue("발송중");
if ("3".equals(resultAllSentList.get(i).getCurState()))
cell.setCellValue("결과대기");
if ("4".equals(resultAllSentList.get(i).getCurState()))
cell.setCellValue("발송완료");
}
}
if (j == 2)
cell.setCellValue(sdf.format((resultAllSentList.get(i)).getRegdate())); // 접수일자
if (j == 3)
cell.setCellValue(sdf.format((resultAllSentList.get(i)).getReqdate())); // 예약일자
if (j == 4) {
cell.setCellValue(resultAllSentList.get(i).getMsgTypeName());
}
if (j == 5)
cell.setCellValue((resultAllSentList.get(i)).getSmsTxt()); // 내용
if (j == 6)
cell.setCellValue((resultAllSentList.get(i)).getMsgGroupCnt()); // 발송건수
if (j == 7)
cell.setCellValue((resultAllSentList.get(i)).getCallFrom()); // 발신번호
if (j == 8)
cell.setCellValue((resultAllSentList.get(i)).getTotPrice()); // 발신번호
//발송결과 성공, 실패 처리
if (j == 9)
cell.setCellValue(resultAllSentList.get(i).getSuccessCount()); // 알림톡 발송결과 성공
if (j == 10)
cell.setCellValue(resultAllSentList.get(i).getFailCount()); // 알림톡 발송결과 실패
if (j == 11)
cell.setCellValue(resultAllSentList.get(i).getKakaoResendSuccCount()); // 대체문자 발송결과 대기
if (j == 12)
cell.setCellValue(resultAllSentList.get(i).getKakaoResendFailCount()); // 대체문자 발송결과 대기
}
}
}
private void kakaoSentExcelReservCancel(List<KakaoSentVO> resultAllSentList
, Row row
,Sheet sheet
,Cell cell
,CellStyle style
,CellStyle styleLastCell
,String type
,String fCnt
,SimpleDateFormat sdf) {
{// 화면 리스트
row = sheet.createRow(0);
sheet.setColumnWidth(1, 5000); // 접수일자 칼럼의 폭 조절
sheet.setColumnWidth(2, 5000); // 예약일자 칼럼의 폭 조절
sheet.setColumnWidth(3, 5000); // 취소일자 칼럼의 폭 조절
sheet.setColumnWidth(5, 10000); // 내용 칼럼의 폭 조절
sheet.setColumnWidth(6, 5000); // 발신번호 칼럼의 폭 조절
sheet.setColumnWidth(7, 5000); // 금액 칼럼의 폭 조절
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);
}
for (int i = 0; i < resultAllSentList.size(); i++) {
String msgType = "";
if (resultAllSentList.get(i).getMsgType().equals("8")
&& resultAllSentList.get(i).getFileCnt().equals("0")) {
msgType = "알림톡";
} else if (resultAllSentList.get(i).getMsgType().equals("9")
&& !resultAllSentList.get(i).getFileCnt().equals("0")) {
msgType = "친구톡";
}
int excelLen = 0;
row = sheet.createRow(i + 1);
excelLen = 9;
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)).getRegdate())); // 접수일자
if (j == 2)
cell.setCellValue(sdf.format((resultAllSentList.get(i)).getReqdate())); // 예약일자
if (j == 3) {
cell.setCellValue(sdf.format((resultAllSentList.get(i)).getCancelDate())); // 취소일자
}
if (j == 4) {
cell.setCellValue(resultAllSentList.get(i).getMsgTypeName());
}
if (j == 5)
cell.setCellValue((resultAllSentList.get(i)).getSmsTxt()); // 내용
if (j == 6)
cell.setCellValue((resultAllSentList.get(i)).getMsgGroupCnt()); // 발송건수
if (j == 7)
cell.setCellValue((resultAllSentList.get(i)).getCallFrom()); // 발신번호
if (j == 8)
cell.setCellValue((resultAllSentList.get(i)).getTotPrice()); // 발신번호
}
}
}
/**
* 예약관리 출력하기 팝업화면
*
* @param MjonMsgSentVO
* @param
* @param sessionVO
* @param model
* @return
* @throws Exception
*/
@RequestMapping(value= {"/web/mjon/reservmsg/printReservKakaoDataAjax.do"})
public String printReservKakaoDataAjax(
@ModelAttribute("searchVO") MjonResvMsgVO mjonResvMsgVO, KakaoSentVO kakaoSentVO,
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 {
kakaoSentVO.setUserId(userId);
}
// 검색 리스트 불러오기
if(kakaoSentVO.getPageUnit() != 10) {
kakaoSentVO.setPageUnit(mjonResvMsgVO.getPageUnit());
}
//기본 내림차순 정렬
if(kakaoSentVO.getSearchSortOrd().equals("")) {
kakaoSentVO.setSearchSortOrd("desc");
kakaoSentVO.setSearchSortCnd("regdate");
}
//선택 탭 정보 저장
//mjonResvMsgVO.setSearchMsgType(mjonResvMsgVO.getTabType());
/** pageing */
PaginationInfo paginationInfo = new PaginationInfo();
paginationInfo.setCurrentPageNo(kakaoSentVO.getPageIndex());
paginationInfo.setRecordCountPerPage(kakaoSentVO.getPageUnit());
paginationInfo.setPageSize(kakaoSentVO.getPageSize());
kakaoSentVO.setFirstIndex(paginationInfo.getFirstRecordIndex());
kakaoSentVO.setLastIndex(paginationInfo.getLastRecordIndex());
kakaoSentVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage());
//전체 발송 리스트 불러오기
List<KakaoSentVO> resultAllResList = kakaoSentService.selectReservKakaoSentList(kakaoSentVO);
model.addAttribute("resultAllSentList", resultAllResList);
model.addAttribute("resultAllSentCnt", resultAllResList.size());
model.addAttribute("searchKeyword", mjonResvMsgVO.getSearchKeyword());
paginationInfo.setTotalRecordCount( resultAllResList.size()> 0 ? (Integer.parseInt((resultAllResList.get(0)).getTotMsgCnt())) : 0);
model.addAttribute("paginationInfo", paginationInfo);
model.addAttribute("totalRecordCount", paginationInfo.getTotalRecordCount());
model.addAttribute("mjonMsgSentVO", mjonResvMsgVO);
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/reservedmsg/ReservedKakaoPrintListPopUp";
}
//파일 이름을 이용하여 첨부파일 아이디(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;
}
}