File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
File name
Commit message
Commit date
2024-09-10
2024-11-14
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.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());*/
String startDate = mjonResvMsgVO.getStartDate();
String endDate = mjonResvMsgVO.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());
mjonResvMsgVO.setStartDate(startDate);
mjonResvMsgVO.setEndDate(endDate);
}
model.addAttribute("searchKeyword", mjonResvMsgVO.getSearchKeyword());
model.addAttribute("mjonResvMsgVO", mjonResvMsgVO);
model.addAttribute("siteId", mjonResvMsgVO.getSiteId());
return "web/reservedmsg/ReservedMsgView";
}
/**
* 마이페이지 - 이용내역 - ajax
* @param mjonMsgVO
* @param model
* @return "/web/member/pay/PayUserListAjax.do"
* @throws Exception
*/
@RequestMapping(value="/web/reservedmsg/subcontent/MsgSentView_HA_allSentAjax.do")
public String mberInfo_cash_pointAjax(@ModelAttribute("searchVO") MjonResvMsgVO mjonResvMsgVO,
HttpServletRequest request,
ModelMap model) throws Exception{
System.out.println("ReservedMsgSentView_HA_allSentAjax");
LoginVO loginVO = (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser();
String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId());
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);
System.out.println("ReservedMsgSentView_HA_allSentAjax_end");
return "/web/reservedmsg/subcontent/ReservedMsgSentView_HA_allSentAjax";
}
/**
* @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)).getCallFromComma()); //발신번호
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)).getCallFromComma()); //발신번호
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)).getCallFromComma()); //발신번호
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)).getCallFromComma()); //발신번호
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");
String startDate = kakaoSentVO.getStartDate();
String endDate = kakaoSentVO.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());
kakaoSentVO.setStartDate(startDate);
kakaoSentVO.setEndDate(endDate);
}
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)).getCallFromComma()); //발신번호
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)).getCallFromComma()); // 발신번호
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)).getCallFromComma()); // 발신번호
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)).getCallFromComma()); // 발신번호
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;
}
}