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