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 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.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;
    
    
    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);
    	model.addAttribute("totalMsgCnt", totalMsgCnt);
    	
    	//단문 성공건, 실패건 불러오기
    	mjonResvMsgVO.setMsgType("4");
    	List<MjonResvMsgVO> smsMsgCnt = mjonReservMsgService.selectDetailMsgCnt(mjonResvMsgVO);
    	model.addAttribute("smsMsgCnt", smsMsgCnt);
    	
    	//장문 성공건, 실패건 불러오기
    	mjonResvMsgVO.setMsgType("6");
    	mjonResvMsgVO.setFileCnt("0");
    	List<MjonResvMsgVO> lmsMsgCnt = mjonReservMsgService.selectDetailMsgCnt(mjonResvMsgVO);
    	model.addAttribute("lmsMsgCnt", lmsMsgCnt);
    	
    	//그림문자 성공건, 실패건 불러오기
    	mjonResvMsgVO.setMsgType("6");
    	mjonResvMsgVO.setFileCnt("1");
    	List<MjonResvMsgVO> mmsMsgCnt = mjonReservMsgService.selectDetailMsgCnt(mjonResvMsgVO);
    	model.addAttribute("mmsMsgCnt", mmsMsgCnt);
    	
    	// 검색 리스트 불러오기
		if(mjonResvMsgVO.getPageUnit() != 10) {
			mjonResvMsgVO.setPageUnit(mjonResvMsgVO.getPageUnit());
		}
		
    	//기본 내림차순 정렬
    	if(mjonResvMsgVO.getSearchSortOrd().equals("")) {
    		
    		mjonResvMsgVO.setSearchSortOrd("desc");
    		
    	}
    	
    	//기본 등록일자 정렬
    	if(mjonResvMsgVO.getSearchSortCnd().equals("")) {
    		
    		mjonResvMsgVO.setSearchSortCnd("regdate");
    		
    	}

		/** pageing */
		/*PaginationInfo paginationInfo = new PaginationInfo();
		paginationInfo.setCurrentPageNo(mjonResvMsgVO.getPageIndex());
		paginationInfo.setRecordCountPerPage(mjonResvMsgVO.getPageUnit());
		paginationInfo.setPageSize(mjonResvMsgVO.getPageSize());

		mjonResvMsgVO.setFirstIndex(paginationInfo.getFirstRecordIndex());
		mjonResvMsgVO.setLastIndex(paginationInfo.getLastRecordIndex());
		mjonResvMsgVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage());*/
    	
    	//전체 발송 리스트 불러오기
    	/*List<MjonResvMsgVO> resultAllResList = mjonReservMsgService.selectAllResvMsgList(mjonResvMsgVO);
    	model.addAttribute("resultAllResList", resultAllResList);
    	model.addAttribute("resultAllResCnt", resultAllResList.size());
    	
    	model.addAttribute("searchKeyword", mjonResvMsgVO.getSearchKeyword());
    	paginationInfo.setTotalRecordCount( resultAllResList.size()> 0 ? (Integer.parseInt((resultAllResList.get(0)).getTotMsgCnt())) : 0);
    	model.addAttribute("paginationInfo", paginationInfo);
    	model.addAttribute("totalRecordCount", paginationInfo.getTotalRecordCount());*/
    	
    	model.addAttribute("searchKeyword", mjonResvMsgVO.getSearchKeyword());
    	model.addAttribute("mjonResvMsgVO", mjonResvMsgVO);
    	model.addAttribute("siteId", mjonResvMsgVO.getSiteId());
		return "web/reservedmsg/ReservedMsgView";
	}
	
    /**
     * 예약관리 탭 리스트 화면 
     * @param searchVO
     * @param model
     * @return	"/web/mjon/reservmsg/selectReservMsgListViewAjax.do"
     * @throws Exception
     */
	@RequestMapping(value= {"/web/mjon/reservmsg/selectReservMsgListViewAjax.do"})
	public String selectReservMsgListViewAjax(@ModelAttribute("searchVO") MjonResvMsgVO mjonResvMsgVO, ModelMap model) throws Exception{
		
		//로그인 권한정보 불러오기
    	LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null;
    	String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId());
    	
    	mjonResvMsgVO.setUserId(userId);
    	
    	// 검색 리스트 불러오기
		if(mjonResvMsgVO.getPageUnit() != 10) {
			mjonResvMsgVO.setPageUnit(mjonResvMsgVO.getPageUnit());
		}
		
    	//기본 내림차순 정렬
    	if(mjonResvMsgVO.getSearchSortOrd().equals("")) {
    		
    		mjonResvMsgVO.setSearchSortOrd("desc");
    		mjonResvMsgVO.setSearchSortCnd("regdate");
    	}
    	
    	//선택 탭 정보 저장
    	//mjonResvMsgVO.setSearchMsgType(mjonResvMsgVO.getTabType());
    	
		/** pageing */
		PaginationInfo paginationInfo = new PaginationInfo();
		paginationInfo.setCurrentPageNo(mjonResvMsgVO.getPageIndex());
		paginationInfo.setRecordCountPerPage(mjonResvMsgVO.getPageUnit());
		paginationInfo.setPageSize(mjonResvMsgVO.getPageSize());

		mjonResvMsgVO.setFirstIndex(paginationInfo.getFirstRecordIndex());
		mjonResvMsgVO.setLastIndex(paginationInfo.getLastRecordIndex());
		mjonResvMsgVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage());
    	
    	//전체 발송 리스트 불러오기
    	List<MjonResvMsgVO> resultAllResList = mjonReservMsgService.selectAllResvMsgList(mjonResvMsgVO);
    	model.addAttribute("resultAllResList", resultAllResList);
    	model.addAttribute("resultAllResCnt", resultAllResList.size());
    	
    	model.addAttribute("searchKeyword", mjonResvMsgVO.getSearchKeyword());
    	paginationInfo.setTotalRecordCount( resultAllResList.size()> 0 ? (Integer.parseInt((resultAllResList.get(0)).getTotMsgCnt())) : 0);
    	model.addAttribute("paginationInfo", paginationInfo);
    	model.addAttribute("totalRecordCount", paginationInfo.getTotalRecordCount());
    	
    	//전체, 발송 완료 탭의 발송 결과 건수 리스트 불러오기
    	List<MjonResvMsgVO> resultMsgSucFailList = mjonReservMsgService.selectAllResvMsgSucFailList(resultAllResList);
    	model.addAttribute("resultMsgSucFailList", resultMsgSucFailList);
    	
    	model.addAttribute("mjonResvMsgVO", mjonResvMsgVO);
    	
    	String stateType = mjonResvMsgVO.getStateType();
    	String pageUrl = "web/reservedmsg/ReservedMsgAllListAjax";
    	
    	if(stateType.equals("ready")) {
    		
    		pageUrl = "web/reservedmsg/ReservedMsgReadyListAjax";
    		
    	}else if(stateType.equals("complete")) {
    		
    		pageUrl = "web/reservedmsg/ReservedMsgCompleteListAjax";
    		
    	}else if(stateType.equals("cancel")) {
    		
    		pageUrl = "web/reservedmsg/ReservedMsgCancelListAjax";
    		
    	}
		return pageUrl;
	}
	
	
    /**
     * 예약관리 문자 선택 삭제 기능 
     * @param searchVO
     * @param model
     * @return	"web/mjon/reservmsg/selectReservAllMsgListAjax.do"
     * @throws Exception
     */
	@RequestMapping(value= {"/web/mjon/reservmsg/deleteReservMsgDataAjax.do"})
	public String deleteReservMsgDataAjax(@ModelAttribute("searchVO") MjonResvMsgVO mjonResvMsgVO, ModelMap model) throws Exception{
		
		//로그인 권한정보 불러오기
    	LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null;
    	String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId());
    	
    	mjonResvMsgVO.setUserId(userId);
    	
    	//선택 문자 삭제 처리
    	int resultCnt = mjonReservMsgService.deleteReservMsgDataAjax(mjonResvMsgVO);
    	
    	/*
    	 * 
    	 * 리스트 화면 불러오기
    	 * 
    	 * */
    	
    	// 검색 리스트 불러오기
		if(mjonResvMsgVO.getPageUnit() != 10) {
			mjonResvMsgVO.setPageUnit(mjonResvMsgVO.getPageUnit());
		}
		
    	//기본 내림차순 정렬
    	if(mjonResvMsgVO.getSearchSortOrd().equals("")) {
    		
    		mjonResvMsgVO.setSearchSortOrd("desc");
    		
    	}
    	
    	//기본 등록일자 정렬
    	if(mjonResvMsgVO.getSearchSortCnd().equals("")) {
    		
    		mjonResvMsgVO.setSearchSortCnd("regdate");
    		
    	}

		/** pageing */
		PaginationInfo paginationInfo = new PaginationInfo();
		paginationInfo.setCurrentPageNo(mjonResvMsgVO.getPageIndex());
		paginationInfo.setRecordCountPerPage(mjonResvMsgVO.getPageUnit());
		paginationInfo.setPageSize(mjonResvMsgVO.getPageSize());

		mjonResvMsgVO.setFirstIndex(paginationInfo.getFirstRecordIndex());
		mjonResvMsgVO.setLastIndex(paginationInfo.getLastRecordIndex());
		mjonResvMsgVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage());
    	
    	//전체 발송 리스트 불러오기
    	List<MjonResvMsgVO> resultAllResList = mjonReservMsgService.selectAllResvMsgList(mjonResvMsgVO);
    	model.addAttribute("resultAllResList", resultAllResList);
    	model.addAttribute("resultAllResCnt", resultAllResList.size());
    	
    	model.addAttribute("searchKeyword", mjonResvMsgVO.getSearchKeyword());
    	paginationInfo.setTotalRecordCount( resultAllResList.size()> 0 ? (Integer.parseInt((resultAllResList.get(0)).getTotMsgCnt())) : 0);
    	model.addAttribute("paginationInfo", paginationInfo);
    	model.addAttribute("totalRecordCount", paginationInfo.getTotalRecordCount());
    	
    	model.addAttribute("mjonResvMsgVO", mjonResvMsgVO);
    	
		return "web/reservedmsg/ReservedMsgAllListAjax";
	}
	
	
		
    /**
     * 예약관리 문자 상세보기 내용 
     * @param searchVO
     * @param model
     * @return	"/web/mjon/reservmsg/selectReservMsgDetailDataAjax.do"
     * @throws Exception
     */
	@RequestMapping(value= {"/web/mjon/reservmsg/selectReservMsgDetailDataAjax.do"})
	public String selectReservMsgDetailDataAjax(@ModelAttribute("searchVO") MjonResvMsgVO mjonResvMsgVO, ModelMap model) throws Exception{
		
		//로그인 권한정보 불러오기
    	LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null;
    	String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId());
    	
    	mjonResvMsgVO.setUserId(userId);
    	
    	//문자 상세보기 처리
    	MjonResvMsgVO resultMsgDetail = mjonReservMsgService.selectReservMsgDetailDataAjax(mjonResvMsgVO);
    	model.addAttribute("resultMsgDetail", resultMsgDetail);
    	
    	//문자 상세보기 이미지 아이디 받아오기 
    	String atchFileId1 ="";
    	String atchFileId2 ="";
    	String atchFileId3 ="";
    	
    	String path1 = resultMsgDetail.getFilePath1();
    	String path2 = resultMsgDetail.getFilePath2();
    	String path3 = resultMsgDetail.getFilePath3();
    	
    	FileVO fileVO1 = new FileVO();
    	FileVO fileVO2 = new FileVO();
    	FileVO fileVO3 = new FileVO();
    	
    	if(StringUtil.isNotEmpty(path1)) {
    		
    		//파일경로 및 이름을 이용하여 atchFileId, fileExtsn 번호 받아오기
    		fileVO1 = getFilePathToAtchFileId(path1);
    		
    	}
    	
    	if(StringUtil.isNotEmpty(path2)) {
    		
    		//파일경로 및 이름을 이용하여 atchFileId, fileExtsn 번호 받아오기
    		fileVO2 = getFilePathToAtchFileId(path2);
    		
    	}
    	

		if(StringUtil.isNotEmpty(path3)) {
			
			//파일경로 및 이름을 이용하여 atchFileId, fileExtsn 번호 받아오기
			fileVO3 = getFilePathToAtchFileId(path3);
			
		}
    	
    	model.addAttribute("fileVO1", fileVO1);
    	model.addAttribute("fileVO2", fileVO2);
    	model.addAttribute("fileVO3", fileVO3);
		return "web/reservedmsg/ReservedMsgDetailPopAjax";
	}
	
	/**
     * 예약 문자 취소 처리 기능 
     * @param searchVO
     * @param model
     * @return	"/web/mjon/reservmsg/deleteReservMsgCancelDataAjax.do"
     * @throws Exception
     */
	@RequestMapping(value= {"/web/mjon/reservmsg/deleteReservMsgCancelDataAjax.do"})
	public ModelAndView deleteReservMsgCancelDataAjax(MjonResvMsgVO mjonResvMsgVO,
			RedirectAttributes redirectAttributes, 
			ModelMap model) throws Exception{
	
		ModelAndView modelAndView = new ModelAndView(); 
		modelAndView.setViewName("jsonView");
		
		//로그인 권한정보 불러오기
		LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null;
    	String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId());

    	if(!userId.equals("")) {
    		
    		mjonResvMsgVO.setUserId(userId);
    		
    	}else {
    		
    		modelAndView.addObject("message", "로그인 후 이용이 가능합니다.");
    		modelAndView.addObject("result", "fail");
    		
    		return modelAndView;
    		
    	}
    	
    	 // 디비에 문자 내용을 저장해 준다.
 		int resultSts = mjonReservMsgService.deleteReservMsgCancelDataAjax(mjonResvMsgVO);
		
		if(resultSts > 0) {
			
			modelAndView.addObject("message", "예약 발송이 정상적으로 취소 되었습니다.");
			modelAndView.addObject("result", "success");
			
		}else {
			
			modelAndView.addObject("message", "예약 발송 처리가 실패 되었습니다.");
			modelAndView.addObject("result", "fail");
			
		}
		
		return modelAndView;
		
	}
	
	/**
	 * 예약관리 출력하기 팝업화면 
	 *
	 * @param MjonMsgSentVO
	 * @param 
	 * @param sessionVO
	 * @param model
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value= {"/web/mjon/reservmsg/printReservMsgDataAjax.do"})
	public String printReservMsgDataAjax(
			@ModelAttribute("searchVO") MjonResvMsgVO mjonResvMsgVO, 
			ModelMap model) throws Exception {

		
		//로그인 권한정보 불러오기
    	LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null;
    	String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId());
    	String userNm = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getName());
    	
    	if(userId.equals("")) {
    		
    		/*modelAndView.addObject("status", "loginFail");
    		modelAndView.addObject("message", "로그인이 필요합니다.");
    		return modelAndView;*/
    		
    	}else {
    		
    		mjonResvMsgVO.setUserId(userId);
    		
    	}
    	
		
    	// 검색 리스트 불러오기
		if(mjonResvMsgVO.getPageUnit() != 10) {
			mjonResvMsgVO.setPageUnit(mjonResvMsgVO.getPageUnit());
		}
		
    	//기본 내림차순 정렬
    	if(mjonResvMsgVO.getSearchSortOrd().equals("")) {
    		
    		mjonResvMsgVO.setSearchSortOrd("desc");
    		mjonResvMsgVO.setSearchSortCnd("regdate");
    	}
    	
    	//선택 탭 정보 저장
    	//mjonResvMsgVO.setSearchMsgType(mjonResvMsgVO.getTabType());
    	
		/** pageing */
		PaginationInfo paginationInfo = new PaginationInfo();
		paginationInfo.setCurrentPageNo(mjonResvMsgVO.getPageIndex());
		paginationInfo.setRecordCountPerPage(mjonResvMsgVO.getPageUnit());
		paginationInfo.setPageSize(mjonResvMsgVO.getPageSize());

		mjonResvMsgVO.setFirstIndex(paginationInfo.getFirstRecordIndex());
		mjonResvMsgVO.setLastIndex(paginationInfo.getLastRecordIndex());
		mjonResvMsgVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage());
    	
    	//전체 발송 리스트 불러오기
    	List<MjonResvMsgVO> resultAllResList = mjonReservMsgService.selectAllResvMsgList(mjonResvMsgVO);
    	model.addAttribute("resultAllSentList", resultAllResList);
    	model.addAttribute("resultAllSentCnt", resultAllResList.size());
    	
    	model.addAttribute("searchKeyword", mjonResvMsgVO.getSearchKeyword());
    	paginationInfo.setTotalRecordCount( resultAllResList.size()> 0 ? (Integer.parseInt((resultAllResList.get(0)).getTotMsgCnt())) : 0);
    	model.addAttribute("paginationInfo", paginationInfo);
    	model.addAttribute("totalRecordCount", paginationInfo.getTotalRecordCount());
    	
    	//발송 결과 성공 실패 건수 리스트 불러오기
    	List<MjonResvMsgVO> resultMsgSucFailList = mjonReservMsgService.selectAllResvMsgSucFailList(resultAllResList);
    	model.addAttribute("resultMsgSucFailList", resultMsgSucFailList);
    	
    	model.addAttribute("mjonMsgSentVO", mjonResvMsgVO);
    	model.addAttribute("userNm", userNm);
    	
    	Calendar cal = Calendar.getInstance();

    	int year = cal.get(Calendar.YEAR);
    	int month = cal.get(Calendar.MONTH) + 1;
    	int day = cal.get(Calendar.DAY_OF_MONTH);
    	
    	model.addAttribute("year", year);
    	model.addAttribute("month", month);
    	model.addAttribute("day", day);
    	
		
		return "web/reservedmsg/ReservedMsgPrintListPopUp";
	}
	
	
	
	/**
     * 예약관리 엑셀다운로드 기능 
     * @param searchVO
     * @param model
     * @return	"/web/mjon/reservmsg/reservMsgExcelDownLoadAjax.do"
     * @throws Exception
     */
	@RequestMapping(value= {"/web/mjon/reservmsg/reservMsgExcelDownLoadAjax.do"})
	public void reservMsgExcelDownLoadAjax(MjonResvMsgVO mjonResvMsgVO,
			RedirectAttributes redirectAttributes, 
			HttpServletRequest request,
			HttpServletResponse response ,
			ModelMap model) throws Exception{
	
		//로그인 권한정보 불러오기
		LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null;
    	String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId());

    	if(!userId.equals("")) {
    		
    		mjonResvMsgVO.setUserId(userId);
    		
    	}else {
    		
    		/*modelAndView.addObject("message", "로그인 후 이용이 가능합니다.");
    		modelAndView.addObject("result", "fail");
    		
    		return modelAndView;*/
    		
    	}
    	
    	String stateType = mjonResvMsgVO.getStateType();
		String tabType = mjonResvMsgVO.getTabType();
    	
    	// 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다.
		SXSSFWorkbook wb = new SXSSFWorkbook(100);
		String fileName ="예약관리 엑셀 리스트";		// 저장 파일명
		String sheetTitle = "예약 발송 내역" ; 		// 셀 제목
		Sheet sheet = wb.createSheet(sheetTitle);
		Cell cell = null;
		Row row = null;
		
		CellStyle style = wb.createCellStyle();
		style.setBorderBottom(CellStyle.BORDER_THIN); //테두리 두껍게 
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setBorderTop(CellStyle.BORDER_THIN);
		
		// 정렬
		style.setAlignment(CellStyle.ALIGN_CENTER); //가운데 정렬
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //높이 가운데 정렬
		
		Font font = wb.createFont();
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);  //글씨 bold
		
		
		String type = "";
		String fCnt = "";
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
		
		try{ 
			
			
			mjonResvMsgVO.setRecordCountPerPage(100000);
			mjonResvMsgVO.setFirstIndex(0);
			
			if("".equals(mjonResvMsgVO.getSearchSortCnd())){ //최초조회시 최신것 조회List
				mjonResvMsgVO.setSearchSortCnd("regdate");
				mjonResvMsgVO.setSearchSortOrd("desc");
			}
			
			//예약 관리 리스트 불러오기
			List<MjonResvMsgVO> resultAllResList = mjonReservMsgService.selectAllResvMsgList(mjonResvMsgVO);
			
			//전체, 발송 완료 탭의 발송 결과 건수 리스트 불러오기
	    	List<MjonResvMsgVO> resultMsgSucFailList = mjonReservMsgService.selectAllResvMsgSucFailList(resultAllResList);
			int msgSFsize = resultMsgSucFailList.size();
			//발송 성공,실패 건수 불러오기
			
			{//화면 리스트
				
  				row = sheet.createRow(0);
  				
				if(stateType.equals("all")) {
					
					sheet.setColumnWidth(1, 5000); // 발송형태 칼럼의 폭 조절
					sheet.setColumnWidth(2, 5000); // 접수일자 칼럼의 폭 조절
					sheet.setColumnWidth(3, 5000); // 예약일자 칼럼의 폭 조절
					sheet.setColumnWidth(5, 7000); // 제목 칼럼의 폭 조절
					sheet.setColumnWidth(6, 10000); // 내용 칼럼의 폭 조절
					sheet.setColumnWidth(8, 5000); // 내용 칼럼의 폭 조절
					
					//셀병합 처리
					sheet.addMergedRegion(new CellRangeAddress(0,1,0,0)); //번호 세로 셀병합
	  				sheet.addMergedRegion(new CellRangeAddress(0,1,1,1)); //발송상태 세로 셀병합
	  				sheet.addMergedRegion(new CellRangeAddress(0,1,2,2)); //접수일자 세로 셀병합
	  				sheet.addMergedRegion(new CellRangeAddress(0,1,3,3)); //예약일자 세로 셀병합
	  				sheet.addMergedRegion(new CellRangeAddress(0,1,4,4)); //형태 세로 셀병합
	  				sheet.addMergedRegion(new CellRangeAddress(0,1,5,5)); //제목 세로 셀병합
	  				sheet.addMergedRegion(new CellRangeAddress(0,1,6,6)); //내용 세로 셀병합
	  				sheet.addMergedRegion(new CellRangeAddress(0,1,7,7)); //발송건수 세로 셀병합
	  				sheet.addMergedRegion(new CellRangeAddress(0,1,8,8)); //발신번호 셀병합
	  				sheet.addMergedRegion(new CellRangeAddress(0,1,9,9)); //금액 세로 세로 셀병합
	  				sheet.addMergedRegion(new CellRangeAddress(0,1,12,12)); //예약 취소 세로 셀병합
					
					cell = row.createCell(0);
					cell.setCellValue("번호");
					cell.setCellStyle(style);
					
					cell = row.createCell(1);
					cell.setCellValue("발송상태");
					cell.setCellStyle(style);
					
					cell = row.createCell(2);
					cell.setCellValue("접수일자");
					cell.setCellStyle(style);
					
					cell = row.createCell(3);
					cell.setCellValue("예약일자");
					cell.setCellStyle(style);
					
					cell = row.createCell(4);
					cell.setCellValue("형태");
					cell.setCellStyle(style);
					
					cell = row.createCell(5);
					cell.setCellValue("제목");
					cell.setCellStyle(style);
					
					cell = row.createCell(6);
					cell.setCellValue("내용");
					cell.setCellStyle(style);
					
					cell = row.createCell(7);
					cell.setCellValue("발송건수");
					cell.setCellStyle(style);
					
					cell = row.createCell(8);
					cell.setCellValue("발신번호");
					cell.setCellStyle(style);
					
					cell = row.createCell(9);
					cell.setCellValue("금액");
					cell.setCellStyle(style);
					
					cell = row.createCell(10);
					cell.setCellValue("발송결과 건수");
					sheet.addMergedRegion(new CellRangeAddress(0,0,10,11)); // 발송결과 건수 가로 셀병합
					cell.setCellStyle(style);
					
					cell = row.createCell(11);
					cell.setCellStyle(style);
					
					cell = row.createCell(12);
					cell.setCellValue("예약취소");
					cell.setCellStyle(style);
					
					row = sheet.createRow(1);
					
					cell = row.createCell(0);
					cell.setCellStyle(style);
					
					cell = row.createCell(1);
					cell.setCellStyle(style);
					
					cell = row.createCell(2);
					cell.setCellStyle(style);
					
					cell = row.createCell(3);
					cell.setCellStyle(style);
					
					cell = row.createCell(4);
					cell.setCellStyle(style);
					
					cell = row.createCell(5);
					cell.setCellStyle(style);
					
					cell = row.createCell(6);
					cell.setCellStyle(style);
					
					cell = row.createCell(7);
					cell.setCellStyle(style);
					
					cell = row.createCell(8);
					cell.setCellStyle(style);
					
					cell = row.createCell(9);
					cell.setCellStyle(style);
					
					cell = row.createCell(10);
					cell.setCellValue("성공");
					cell.setCellStyle(style);
					
					cell = row.createCell(11);
					cell.setCellValue("실패");
					cell.setCellStyle(style);
					
					cell = row.createCell(12);
					cell.setCellStyle(style);
					
				}else {
					
					if(stateType.equals("complete")) {
						
						sheet.setColumnWidth(1, 5000); // 접수일자 칼럼의 폭 조절
						sheet.setColumnWidth(2, 5000); // 예약일자 칼럼의 폭 조절
						sheet.setColumnWidth(4, 7000); // 제목 칼럼의 폭 조절
						sheet.setColumnWidth(5, 10000); // 내용 칼럼의 폭 조절
						sheet.setColumnWidth(7, 5000); // 내용 칼럼의 폭 조절
						
						sheet.addMergedRegion(new CellRangeAddress(0,1,0,0)); //번호 세로 셀병합
    	  				sheet.addMergedRegion(new CellRangeAddress(0,1,1,1)); //접수일자 세로 셀병합
    	  				sheet.addMergedRegion(new CellRangeAddress(0,1,2,2)); //예약일자 세로 셀병합
    	  				sheet.addMergedRegion(new CellRangeAddress(0,1,3,3)); //형태 세로 셀병합
    	  				sheet.addMergedRegion(new CellRangeAddress(0,1,4,4)); //제목 세로 셀병합
    	  				sheet.addMergedRegion(new CellRangeAddress(0,1,5,5)); //내용 세로 셀병합
    	  				sheet.addMergedRegion(new CellRangeAddress(0,1,6,6)); //발송건수 세로 셀병합
    	  				sheet.addMergedRegion(new CellRangeAddress(0,1,7,7)); //발신번호 세로 셀병합
    	  				sheet.addMergedRegion(new CellRangeAddress(0,1,8,8)); //금액 세로 셀병합
						
						cell = row.createCell(0);
    					cell.setCellValue("번호");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(1);
    					cell.setCellValue("접수일자");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(2);
    					cell.setCellValue("예약일자");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(3);
    					cell.setCellValue("형태");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(4);
    					cell.setCellValue("제목");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(5);
    					cell.setCellValue("내용");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(6);
    					cell.setCellValue("발송건수");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(7);
    					cell.setCellValue("발신번호");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(8);
    					cell.setCellValue("금액");
    					cell.setCellStyle(style);
						
						cell = row.createCell(9);
    					cell.setCellValue("발송결과 건수");
    					sheet.addMergedRegion(new CellRangeAddress(0,0,9,10)); //셀병합
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(10);
    					cell.setCellStyle(style);
    					
    					row = sheet.createRow(1);
    					
    					cell = row.createCell(0);
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(1);
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(2);
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(3);
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(4);
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(5);
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(6);
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(7);
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(8);
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(9);
    					cell.setCellValue("성공");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(10);
    					cell.setCellValue("실패");
    					cell.setCellStyle(style);
						
					}else if(stateType.equals("ready")) {
						
						sheet.setColumnWidth(1, 5000); // 접수일자 칼럼의 폭 조절
						sheet.setColumnWidth(2, 5000); // 예약일자 칼럼의 폭 조절
						sheet.setColumnWidth(4, 7000); // 제목 칼럼의 폭 조절
						sheet.setColumnWidth(5, 10000); // 내용 칼럼의 폭 조절
						sheet.setColumnWidth(7, 5000); // 내용 칼럼의 폭 조절
						
						cell = row.createCell(0);
    					cell.setCellValue("번호");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(1);
    					cell.setCellValue("접수일자");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(2);
    					cell.setCellValue("예약일자");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(3);
    					cell.setCellValue("형태");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(4);
    					cell.setCellValue("제목");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(5);
    					cell.setCellValue("내용");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(6);
    					cell.setCellValue("발송건수");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(7);
    					cell.setCellValue("발신번호");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(8);
    					cell.setCellValue("금액");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(9);
    					cell.setCellValue("예약취소");
    					cell.setCellStyle(style);
    					
						
					}else if(stateType.equals("cancel")) {
						
						sheet.setColumnWidth(1, 5000); // 접수일자 칼럼의 폭 조절
						sheet.setColumnWidth(2, 5000); // 예약일자 칼럼의 폭 조절
						sheet.setColumnWidth(4, 7000); // 제목 칼럼의 폭 조절
						sheet.setColumnWidth(5, 10000); // 내용 칼럼의 폭 조절
						sheet.setColumnWidth(7, 5000); // 내용 칼럼의 폭 조절
						
						cell = row.createCell(0);
    					cell.setCellValue("번호");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(1);
    					cell.setCellValue("접수일자");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(2);
    					cell.setCellValue("예약일자");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(3);
    					cell.setCellValue("형태");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(4);
    					cell.setCellValue("제목");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(5);
    					cell.setCellValue("내용");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(6);
    					cell.setCellValue("발송건수");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(7);
    					cell.setCellValue("발신번호");
    					cell.setCellStyle(style);
    					
    					cell = row.createCell(8);
    					cell.setCellValue("금액");
    					cell.setCellStyle(style);
    					
					}
					
				} 
				
				DecimalFormat formatter = new DecimalFormat("###,###");
				String totPriceStr = "";
					
				for(int i=0; i < resultAllResList.size(); i++) {
					totPriceStr = formatter.format((int)Double.parseDouble(((resultAllResList.get(i)).getTotPrice())));
					
					String msgType = "단문";
					if(resultAllResList.get(i).getMsgType().equals("6") && resultAllResList.get(i).getFileCnt().equals("0")) {
						msgType = "장문";
					}else if(resultAllResList.get(i).getMsgType().equals("6") && !resultAllResList.get(i).getFileCnt().equals("0")) {
						msgType = "그림";
					}
					
					
					int excelLen = 0;
					
					if(stateType.equals("all")) {
						
						row = sheet.createRow(i+2);
						excelLen = 13;
						
					}else if(stateType.equals("ready")) {
						
						row = sheet.createRow(i+2);
						excelLen = 10;
						
					}else if(stateType.equals("complete")) {
						
						row = sheet.createRow(i+2);
						excelLen = 11;
						
					}else {
						
						row = sheet.createRow(i+1);
						excelLen = 9;
					}
					
					for(int j=0 ; j < excelLen ; j++) {
						cell = row.createCell(j);
						cell.setCellStyle(style);
						
						
						if(stateType.equals("all")) {
							
							if(j==0) cell.setCellValue(i+1); //번호
							if(j==1) { //발송상태 처리해주기
								
								String resvCYn = resultAllResList.get(i).getReserveCYn();
								String curState = resultAllResList.get(i).getCurState();
								
								if(resvCYn.equals("Y")) {
									
									cell.setCellValue("예약 취소"); 		//발송상태
									
								}else {
									
									if(curState.equals("0")) {
										
										cell.setCellValue("발송 대기");		//발송상태
										
									}else if(curState.equals("1")) {
										
										cell.setCellValue("발송중"); 		//발송상태
										
									}else if(curState.equals("2")) {
										
										cell.setCellValue("결과 대기"); 	//발송상태
										
									}else if(curState.equals("3")) {
										
										cell.setCellValue("발송 완료"); 	//발송상태
										
									}
								}
								
							}
							if(j==2) cell.setCellValue(sdf.format((resultAllResList.get(i)).getRegdate()));		//접수일자
							if(j==3) cell.setCellValue(sdf.format((resultAllResList.get(i)).getReqdate())); 	//발송일자
							if(j==4) {
								
								type = resultAllResList.get(i).getMsgType();
								fCnt = resultAllResList.get(i).getFileCnt();
								
								if(type.equals("4")) {
									
									cell.setCellValue("단문"); 	//형태
									
								}else {
									
									if(fCnt.equals("0")) {
										
										cell.setCellValue("장문"); 	//형태
										
									}else {
										
										cell.setCellValue("그림"); 	//형태
										
									}
									
								}
								
							} 
							if(j==5) {
								
								if(resultAllResList.get(i).getSubject() == null) {
									
									cell.setCellValue("제목 없음"); 	//제목
									
								}else {
									
									cell.setCellValue((resultAllResList.get(i)).getSubject()); 	//제목
									
								}
								
							} 
							if(j==6) cell.setCellValue((resultAllResList.get(i)).getSmsTxt()); 		//내용
							if(j==7) cell.setCellValue((resultAllResList.get(i)).getMsgGroupCnt());	//발송건수
							if(j==8) cell.setCellValue((resultAllResList.get(i)).getCallFrom()); 	//발신번호
							if(j==9) cell.setCellValue(totPriceStr); 	//금액
							
							//발송결과 성공, 실패 처리
							String resSucCnt = "0";
							String resFailCnt = "0";
							for(int k=0; k < msgSFsize; k++) {
								
								if(resultAllResList.get(i).getMsgGroupId().equals(resultMsgSucFailList.get(k).getMsgGroupId())) {
									
									
									if(resultMsgSucFailList.get(k).getMsgResultSts().equals("S")) {//발송 성공 건수
										
										resSucCnt = resultMsgSucFailList.get(k).getMsgResultCnt();
										
									}else if(resultMsgSucFailList.get(k).getMsgResultSts().equals("W")){//발송 대기 건수
										
										resFailCnt = "0";
										
									}else {
										resFailCnt = resultMsgSucFailList.get(k).getMsgResultCnt();
									}
									
								}
								
							}
							if(j==10) cell.setCellValue(resSucCnt); 		//발송결과 성공
							if(j==11) cell.setCellValue(resFailCnt); 		//발송결과 실패
							if(j==12) cell.setCellValue((resultAllResList.get(i)).getReserveCYn()); //예약 취소
							
						}else if(stateType.equals("ready")) {
							
							if(j==0) cell.setCellValue(i+1); //번호
							if(j==1) cell.setCellValue(sdf.format((resultAllResList.get(i)).getRegdate()));		//접수일자
							if(j==2) cell.setCellValue(sdf.format((resultAllResList.get(i)).getReqdate())); 	//발송일자
							if(j==3) {
								
								type = resultAllResList.get(i).getMsgType();
								fCnt = resultAllResList.get(i).getFileCnt();
								
								if(type.equals("4")) {
									
									cell.setCellValue("단문"); 	//형태
									
								}else {
									
									if(fCnt.equals("0")) {
										
										cell.setCellValue("장문"); 	//형태
										
									}else {
										
										cell.setCellValue("그림"); 	//형태
										
									}
									
								}
								
							}	//형태
							if(j==4) {
								
								if(resultAllResList.get(i).getSubject() == null) {
									
									cell.setCellValue("제목 없음"); 	//제목
									
								}else {
									
									cell.setCellValue((resultAllResList.get(i)).getSubject()); 	//제목
									
								}
								
							}
							if(j==5) cell.setCellValue((resultAllResList.get(i)).getSmsTxt()); 		//내용
							if(j==6) cell.setCellValue((resultAllResList.get(i)).getMsgGroupCnt());	//발송건수	
							if(j==7) cell.setCellValue((resultAllResList.get(i)).getCallFrom()); 	//발신번호
							if(j==8) cell.setCellValue(totPriceStr); 	//금액
							if(j==9) cell.setCellValue((resultAllResList.get(i)).getReserveCYn()); 	//예약 취소
							
						}else if(stateType.equals("complete")) {
							
							if(j==0) cell.setCellValue(i+1); //번호
							if(j==1) cell.setCellValue(sdf.format((resultAllResList.get(i)).getRegdate()));		//접수일자
							if(j==2) cell.setCellValue(sdf.format((resultAllResList.get(i)).getReqdate())); 	//발송일자
							if(j==3) {
								
								type = resultAllResList.get(i).getMsgType();
								fCnt = resultAllResList.get(i).getFileCnt();
								
								if(type.equals("4")) {
									
									cell.setCellValue("단문"); 	//형태
									
								}else {
									
									if(fCnt.equals("0")) {
										
										cell.setCellValue("장문"); 	//형태
										
									}else {
										
										cell.setCellValue("그림"); 	//형태
										
									}
									
								}
								
							}
							if(j==4) {
								
								if(resultAllResList.get(i).getSubject() == null) {
									
									cell.setCellValue("제목 없음"); 	//제목
									
								}else {
									
									cell.setCellValue((resultAllResList.get(i)).getSubject()); 	//제목
									
								}
								
							}
							if(j==5) cell.setCellValue((resultAllResList.get(i)).getSmsTxt()); 		//내용
							if(j==6) cell.setCellValue((resultAllResList.get(i)).getMsgGroupCnt());	//발송건수
							if(j==7) cell.setCellValue((resultAllResList.get(i)).getCallFrom()); 	//발신번호
							if(j==8) cell.setCellValue(totPriceStr); 	//금액
							//발송결과 성공, 실패 처리
							String resSucCnt = "0";
							String resFailCnt = "0";
							for(int l=0; l < msgSFsize; l++) {
								
								if(resultAllResList.get(i).getMsgGroupId().equals(resultMsgSucFailList.get(l).getMsgGroupId())) {
									
									if(resultMsgSucFailList.get(l).getMsgResultSts().equals("S")) {//발송 성공 건수
										
										resSucCnt = resultMsgSucFailList.get(l).getMsgResultCnt();
										
									}else if(resultMsgSucFailList.get(l).getMsgResultSts().equals("W")){//발송 대기 건수
										
										resFailCnt = "0";
										
									}else {
										
										resFailCnt = resultMsgSucFailList.get(l).getMsgResultCnt();
									}
									
								}
								
							}
							if(j==9) cell.setCellValue(resSucCnt); 		//발송결과 성공
							if(j==10) cell.setCellValue(resFailCnt); 		//발송결과 실패
							
						}else { //예약 취소
							
							if(j==0) cell.setCellValue(i+1); //번호
							if(j==1) cell.setCellValue(sdf.format((resultAllResList.get(i)).getRegdate()));		//접수일자
							if(j==2) cell.setCellValue(sdf.format((resultAllResList.get(i)).getReqdate())); 	//발송일자
							if(j==3) {
								
								type = resultAllResList.get(i).getMsgType();
								fCnt = resultAllResList.get(i).getFileCnt();
								
								if(type.equals("4")) {
									
									cell.setCellValue("단문"); 	//형태
									
								}else {
									
									if(fCnt.equals("0")) {
										
										cell.setCellValue("장문"); 	//형태
										
									}else {
										
										cell.setCellValue("그림"); 	//형태
										
									}
									
								}
								
							}
							if(j==4) {
								
								if(resultAllResList.get(i).getSubject() == null) {
									
									cell.setCellValue("제목 없음"); 	//제목
									
								}else {
									
									cell.setCellValue((resultAllResList.get(i)).getSubject()); 	//제목
									
								}
								
							}
							if(j==5) cell.setCellValue((resultAllResList.get(i)).getSmsTxt()); 		//내용
							if(j==6) cell.setCellValue((resultAllResList.get(i)).getMsgGroupCnt());	//발송건수
							if(j==7) cell.setCellValue((resultAllResList.get(i)).getCallFrom()); 	//발신번호
							if(j==8) cell.setCellValue(totPriceStr); 	//금액
							
						}
						
  				 	}
				}
			}
			response.setHeader("Set-Cookie", "fileDownload=true; path=/");
			SimpleDateFormat mSimpleDateFormat = new SimpleDateFormat ( "yyyyMMdd_HHmmss", Locale.KOREA );
			Date currentTime = new Date ();
			String mTime = mSimpleDateFormat.format ( currentTime );
			fileName = fileName+"("+mTime+")";
			
			response.setHeader("Content-Disposition", String.format("attachment; filename=\""+new String((fileName).getBytes("KSC5601"),"8859_1")+".xlsx"));
			wb.write(response.getOutputStream());
		}catch(Exception e) {
  			response.setHeader("Set-Cookie", "fileDownload=false; path=/");
  			response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate");
  			response.setHeader("Content-Type","text/html; charset=utf-8");
  			OutputStream out = null;
  			try {
  				out = response.getOutputStream();
  				byte[] data = new String("fail..").getBytes();
  				out.write(data, 0, data.length);
  			} catch(Exception ignore) {
  				ignore.printStackTrace();
  			} finally {
  				if(out != null) try { out.close(); } catch(Exception ignore) {}
  			}
  		}finally {
  			// 디스크 적었던 임시파일을 제거합니다.
  			wb.dispose();
  			try { wb.close(); } catch(Exception ignore) {}
  		}
    	
		
	}
	
	/**
     * 예약관리 화면 - 카카오톡
     * @param searchVO
     * @param model
     * @return	"/web/mjon/reservmsg/selectReservKaKaoView.do"
     * @throws Exception
     */
	@RequestMapping(value= {"/web/mjon/reservmsg/selectReservKaKaoView.do"})
	public String selectReservKaKaoView(@ModelAttribute("searchVO") KakaoSentVO kakaoSentVO, 
			RedirectAttributes redirectAttributes,	ModelMap model) throws Exception{
		
		//로그인 권한정보 불러오기
    	LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null;
    	String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId());
    	if(loginVO == null) {
			return "redirect:/web/user/login/login.do";
		}
    	
		kakaoSentVO.setUserId(userId);
		
		//전체 발송 건수 통계 불러오기
		kakaoSentVO.setMsgType("");
		
		System.out.println("kakaoSentVO.getNtceBgnde() : "+ kakaoSentVO.getNtceBgnde());
		
		List<KakaoSentVO> totalMsgCnt = kakaoSentService.selectKakaoReservSentCntAll(kakaoSentVO);
		model.addAttribute("totalMsgCnt", totalMsgCnt);

		List<KakaoSentVO> atCnt = new ArrayList<KakaoSentVO>();
		List<KakaoSentVO> ftCnt = new ArrayList<KakaoSentVO>();
		

		totalMsgCnt.forEach(t->{
			if (Integer.parseInt(t.getFilePath1())>0) {
				atCnt.add(t);
			} else if (Integer.parseInt(t.getFilePath2())>0) {
				ftCnt.add(t);
			}
		});
		
		//알림톡 성공건, 실패건 불러오기    	
		model.addAttribute("atCnt", atCnt);
		
		//친구톡 성공건, 실패건 불러오기
		model.addAttribute("ftCnt", ftCnt);
		
		// 검색 리스트 불러오기
		if(kakaoSentVO.getPageUnit() != 10) kakaoSentVO.setPageUnit(kakaoSentVO.getPageUnit());
		
		//기본 내림차순 정렬
		if(kakaoSentVO.getSearchSortOrd().equals("")) kakaoSentVO.setSearchSortOrd("desc");
			
		//기본 등록일자 정렬
		if(kakaoSentVO.getSearchSortCnd().equals("")) kakaoSentVO.setSearchSortCnd("regdate");

		model.addAttribute("searchKeyword", kakaoSentVO.getSearchKeyword());
		model.addAttribute("kakaoSentVO", kakaoSentVO);
		model.addAttribute("siteId", "tabKakao");
    	
		return "web/reservedmsg/ReservedKakaoView";
	}
	
	/**
     * 예약관리 탭 리스트 화면 - 카카오톡
     * @param searchVO
     * @param model
     * @return	"/web/mjon/reservmsg/selectReservKakaoListViewAjax.do"
     * @throws Exception
     */
	@RequestMapping(value= {"/web/mjon/reservmsg/selectReservKakaoListViewAjax.do"})
	public String selectReservKakaoListViewAjax(
			@ModelAttribute("searchVO") MjonResvMsgVO mjonResvMsgVO
			, @ModelAttribute("kakaoVO") KakaoSentVO kakaoSentVO
			, ModelMap model
			, HttpServletResponse response) throws Exception{
		
		//로그인 권한정보 불러오기
    	LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null;
    	String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId());
    	if("".equals(userId)) {
    		PrintWriter out = response.getWriter();
    		response.setContentType("text/html; charset=UTF-8");
    		out.println("<script>alert('문자온 서비스는 로그인 후 이용 가능합니다.');  location.href='/web/user/login/login.do';</script>");
    		out.close(); 
         }else {
	    	mjonResvMsgVO.setUserId(userId);
	    	
	    	// 검색 리스트 불러오기
			if(kakaoSentVO.getPageUnit() != 10) {
				kakaoSentVO.setPageUnit(mjonResvMsgVO.getPageUnit());
			}
			
	    	//기본 내림차순 정렬
	    	if(kakaoSentVO.getSearchSortOrd().equals("")) {
	    		
	    		kakaoSentVO.setSearchSortOrd("desc");
	    		kakaoSentVO.setSearchSortCnd("regdate");
	    	}
	    	
	    	//선택 탭 정보 저장
	    	//mjonResvMsgVO.setSearchMsgType(mjonResvMsgVO.getTabType());
	    	
			/** pageing */
			PaginationInfo paginationInfo = new PaginationInfo();
			paginationInfo.setCurrentPageNo(kakaoSentVO.getPageIndex());
			paginationInfo.setRecordCountPerPage(kakaoSentVO.getPageUnit());
			paginationInfo.setPageSize(kakaoSentVO.getPageSize());
	
			kakaoSentVO.setFirstIndex(paginationInfo.getFirstRecordIndex());
			kakaoSentVO.setLastIndex(paginationInfo.getLastRecordIndex());
			kakaoSentVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage());
	    	
	    	//전체 발송 리스트 불러오기
	//    	List<MjonResvMsgVO> resultAllResList = mjonReservMsgService.selectAllResvMsgList(mjonResvMsgVO);
			kakaoSentVO.setUserId(userId);
			//예약관리는 그룹데이터로 조회
			kakaoSentVO.setListType("groupList");
			List<KakaoSentVO> resultAllResList = kakaoSentService.selectReservKakaoSentList(kakaoSentVO);
	    	model.addAttribute("resultAllResList", resultAllResList);
	    	model.addAttribute("resultAllResCnt", resultAllResList.size());
	    	
	    	model.addAttribute("searchKeyword", mjonResvMsgVO.getSearchKeyword());
	    	paginationInfo.setTotalRecordCount( resultAllResList.size()> 0 ? (Integer.parseInt((resultAllResList.get(0)).getTotMsgCnt())) : 0);
	    	model.addAttribute("paginationInfo", paginationInfo);
	    	model.addAttribute("totalRecordCount", paginationInfo.getTotalRecordCount());
	    	
	    	//전체, 발송 완료 탭의 발송 결과 건수 리스트 불러오기
	//    	List<MjonResvMsgVO> resultMsgSucFailList = mjonReservMsgService.selectAllResvMsgSucFailList(resultAllResList);
	//    	model.addAttribute("resultMsgSucFailList", resultMsgSucFailList);
	    	
	    	model.addAttribute("mjonResvMsgVO", mjonResvMsgVO);
	    	
	    	String stateType = mjonResvMsgVO.getStateType();
	    	//전체
	    	String pageUrl = "web/reservedmsg/ReservedKakaoAllListAjax";
	    	
	    	//발송대기
	    	if(stateType.equals("ready")) {
	    		pageUrl = "web/reservedmsg/ReservedKakaoReadyListAjax";
	    	}
	    	//발송완료
	    	else if(stateType.equals("complete")) {
	    		pageUrl = "web/reservedmsg/ReservedKakaoCompleteListAjax";
	    	}
	    	//예약취소
	    	else if(stateType.equals("cancel")) {
	    		pageUrl = "web/reservedmsg/ReservedKakaoCancelListAjax";
	    	}
	    	
			return pageUrl;
         }
    	return "web/reservedmsg/ReservedKakaoAllListAjax";
	}
	
	/**
	* @Method Name : deleteReservKakaoDataAjax
	* @작성일 : 2023. 4. 6.
	* @작성자 :  WYH
	* @Method 설명 : 카카오 예약관리 예약선택 취소
	*/
	@RequestMapping(value= {"/web/mjon/reservmsg/deleteReservKakaoDataAjax.do"})
	public String deleteReservKakaoDataAjax(
		@ModelAttribute("searchVO") MjonResvMsgVO mjonResvMsgVO
		, @ModelAttribute("kakaoVO") KakaoSentVO kakaoSentVO
		, ModelMap model
		, HttpServletResponse response) throws Exception{
		
		//로그인 권한정보 불러오기
		LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null;
		String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId());
		if("".equals(userId)) {
			PrintWriter out = response.getWriter();
			response.setContentType("text/html; charset=UTF-8");
			out.println("<script>alert('문자온 서비스는 로그인 후 이용 가능합니다.');  location.href='/web/user/login/login.do';</script>");
			out.close(); 
		}else {
			
			mjonResvMsgVO.setUserId(userId);
			
			//선택 문자 삭제 처리
			int resultCnt = mjonReservMsgService.deleteReservMsgDataAjax(mjonResvMsgVO);
			
			/*
			 * 
			 * 리스트 화면 불러오기
			 * 
			 * */
			
			// 검색 리스트 불러오기
			if(kakaoSentVO.getPageUnit() != 10) {
				kakaoSentVO.setPageUnit(mjonResvMsgVO.getPageUnit());
			}
			
			//기본 내림차순 정렬
			if(kakaoSentVO.getSearchSortOrd().equals("")) {
				
				kakaoSentVO.setSearchSortOrd("desc");
				kakaoSentVO.setSearchSortCnd("regdate");
			}
			
			/** pageing */
			PaginationInfo paginationInfo = new PaginationInfo();
			paginationInfo.setCurrentPageNo(kakaoSentVO.getPageIndex());
			paginationInfo.setRecordCountPerPage(kakaoSentVO.getPageUnit());
			paginationInfo.setPageSize(kakaoSentVO.getPageSize());
	
			kakaoSentVO.setFirstIndex(paginationInfo.getFirstRecordIndex());
			kakaoSentVO.setLastIndex(paginationInfo.getLastRecordIndex());
			kakaoSentVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage());
			
			//전체 발송 리스트 불러오기
			kakaoSentVO.setUserId(userId);
			kakaoSentVO.setListType("groupList");
			List<KakaoSentVO> resultAllResList = kakaoSentService.selectReservKakaoSentList(kakaoSentVO);
			model.addAttribute("resultAllResList", resultAllResList);
			model.addAttribute("resultAllResCnt", resultAllResList.size());
			
			model.addAttribute("searchKeyword", mjonResvMsgVO.getSearchKeyword());
			paginationInfo.setTotalRecordCount( resultAllResList.size()> 0 ? (Integer.parseInt((resultAllResList.get(0)).getTotMsgCnt())) : 0);
			model.addAttribute("paginationInfo", paginationInfo);
			model.addAttribute("totalRecordCount", paginationInfo.getTotalRecordCount());
			
			model.addAttribute("mjonResvMsgVO", mjonResvMsgVO);
		}
		
		return "web/reservedmsg/ReservedKakaoAllListAjax";
	}
	
	
	
	/**
     * 예약관리 엑셀다운로드 기능  - 카카오톡
     * @param searchVO
     * @param model
     * @return	"/web/mjon/reservmsg/reservKakaoExcelDownLoadAjax.do"
     * @throws Exception
     */
	@RequestMapping(value= {"/web/mjon/reservmsg/reservKakaoExcelDownLoadAjax.do"})
	public void reservKakaoExcelDownLoadAjax(
			KakaoSentVO kakaoSentVO,
			RedirectAttributes redirectAttributes, 
			HttpServletRequest request,
			HttpServletResponse response ,
			ModelMap model) throws Exception{
	
		LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null;
    	String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId());

    	if(!userId.equals("")) {
    		
		kakaoSentVO.setUserId(userId);
    		
		String stateType = kakaoSentVO.getStateType();
		String tabType = kakaoSentVO.getTabType();
    	
    	// 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다.
		SXSSFWorkbook wb = new SXSSFWorkbook(100);
		String fileName ="발송관리 엑셀 리스트";		// 저장 파일명
		String sheetTitle = "문자 발송 내역" ; 		// 셀 제목
		Sheet sheet = wb.createSheet(sheetTitle);
		Cell cell = null;
		Row row = null;
		
		CellStyle style = wb.createCellStyle();
		style.setBorderBottom(CellStyle.BORDER_THIN); //테두리 두껍게 
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setBorderTop(CellStyle.BORDER_THIN);
		
		CellStyle styleLastCell = wb.createCellStyle();
		styleLastCell.setBorderLeft(CellStyle.BORDER_THIN);
		
		// 정렬
		style.setAlignment(CellStyle.ALIGN_CENTER); //가운데 정렬
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //높이 가운데 정렬
		
		Font font = wb.createFont();
		font.setBoldweight(Font.BOLDWEIGHT_BOLD);  //글씨 bold
		
		
		String type = "";
		String fCnt = "";
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
		
		try{ 
			kakaoSentVO.setRecordCountPerPage(100000);
			kakaoSentVO.setFirstIndex(0);

			if("".equals(kakaoSentVO.getSearchSortCnd())){ //최초조회시 최신것 조회List
				kakaoSentVO.setSearchSortCnd("regdate");
				kakaoSentVO.setSearchSortOrd("desc");
			}
			
			//발송결과-카카오톡 리스트 불러오기
			//예약관리는 그룹데이터로 조회
			kakaoSentVO.setListType("groupList");
			kakaoSentVO.setReserveYn("Y");
			
			/*List<KakaoSentVO> resultAllSentList = kakaoSentService.selectAllKakaoSentList(kakaoSentVO);*/
			List<KakaoSentVO> resultAllSentList = kakaoSentService.selectReservKakaoSentList(kakaoSentVO);
			
			if("ready".equals(kakaoSentVO.getStateType())) {
				kakaoSentExcelReservReady(resultAllSentList, row, sheet, cell, style, styleLastCell, type, fCnt, sdf);
			}else if("complete".equals(kakaoSentVO.getStateType())) {
				kakaoSentExcelReservComplete(resultAllSentList, row, sheet, cell, style, styleLastCell, type, fCnt, sdf);
			}else if("cancel".equals(kakaoSentVO.getStateType())) {
				kakaoSentExcelReservCancel(resultAllSentList, row, sheet, cell, style, styleLastCell, type, fCnt, sdf);
			}else {
				kakaoSentExcelReservAll(resultAllSentList, row, sheet, cell, style, styleLastCell, type, fCnt, sdf);
			}
			
			response.setHeader("Set-Cookie", "fileDownload=true; path=/");
			SimpleDateFormat mSimpleDateFormat = new SimpleDateFormat ( "yyyyMMdd_HHmmss", Locale.KOREA );
			Date currentTime = new Date ();
			String mTime = mSimpleDateFormat.format ( currentTime );
			fileName = fileName+"("+mTime+")";
			
			response.setHeader("Content-Disposition", String.format("attachment; filename=\""+new String((fileName).getBytes("KSC5601"),"8859_1")+".xlsx"));
			wb.write(response.getOutputStream());
		}catch(Exception e) {
  			response.setHeader("Set-Cookie", "fileDownload=false; path=/");
  			response.setHeader("Cache-Control", "no-cache, no-store, must-revalidate");
  			response.setHeader("Content-Type","text/html; charset=utf-8");
  			OutputStream out = null;
  			try {
  				out = response.getOutputStream();
  				byte[] data = new String("fail..").getBytes();
  				out.write(data, 0, data.length);
  			} catch(Exception ignore) {
  				ignore.printStackTrace();
  			} finally {
  				if(out != null) try { out.close(); } catch(Exception ignore) {}
  			}
  		}finally {
  			// 디스크 적었던 임시파일을 제거합니다.
  			wb.dispose();
  			try { wb.close(); } catch(Exception ignore) {}
  		}
		
		}else {
    		response.sendRedirect("/web/kakao/sent/selectKakaoSentView.do");
    	}
	}
	
	private void kakaoSentExcelReservAll(List<KakaoSentVO> resultAllSentList
									, Row row
									,Sheet sheet
									,Cell cell
									,CellStyle style
									,CellStyle styleLastCell
									,String type
									,String fCnt
									,SimpleDateFormat sdf) {
		
		{//화면 리스트
			row = sheet.createRow(0);
			
			sheet.setColumnWidth(2, 5000); // 접수일자 칼럼의 폭 조절
			sheet.setColumnWidth(3, 5000); // 예약일자 칼럼의 폭 조절
			sheet.setColumnWidth(5, 10000); // 내용 칼럼의 폭 조절
			sheet.setColumnWidth(7, 5000); // 발신번호 칼럼의 폭 조절
			sheet.setColumnWidth(8, 5000); // 금액 칼럼의 폭 조절
			sheet.setColumnWidth(13, 5000); // 예약취소 칼럼의 폭 조절
			
			//셀병합 처리
			sheet.addMergedRegion(new CellRangeAddress(0,1,0,0)); //번호 세로 셀병합
			sheet.addMergedRegion(new CellRangeAddress(0,1,1,1)); //발송상태 세로 셀병합
			sheet.addMergedRegion(new CellRangeAddress(0,1,2,2)); //접수일자 세로 셀병합
			sheet.addMergedRegion(new CellRangeAddress(0,1,3,3)); //예약일자 세로 셀병합
			sheet.addMergedRegion(new CellRangeAddress(0,1,4,4)); //형태 셀병합
			sheet.addMergedRegion(new CellRangeAddress(0,1,5,5)); //내용 셀병합
			sheet.addMergedRegion(new CellRangeAddress(0,1,6,6)); //발송건수 셀병합
			sheet.addMergedRegion(new CellRangeAddress(0,1,7,7)); //발신번호 셀병합
			sheet.addMergedRegion(new CellRangeAddress(0,1,8,8)); //금액 셀병합
			sheet.addMergedRegion(new CellRangeAddress(0,1,13,13)); //예약취소 셀병합
			
			cell = row.createCell(0);
			cell.setCellValue("번호");
			cell.setCellStyle(style);
			
			cell = row.createCell(1);
			cell.setCellValue("발송상태");
			cell.setCellStyle(style);
			
			cell = row.createCell(2);
			cell.setCellValue("접수일자");
			cell.setCellStyle(style);
			
			cell = row.createCell(3);
			cell.setCellValue("예약일자");
			cell.setCellStyle(style);
			
			cell = row.createCell(4);
			cell.setCellValue("형태");
			cell.setCellStyle(style);
			
			cell = row.createCell(5);
			cell.setCellValue("내용");
			cell.setCellStyle(style);
			
			cell = row.createCell(6);
			cell.setCellValue("발송건수");
			cell.setCellStyle(style);
			
			cell = row.createCell(7);
			cell.setCellValue("발신번호");
			cell.setCellStyle(style);
			
			cell = row.createCell(8);
			cell.setCellValue("금액");
			cell.setCellStyle(style);
			
			cell = row.createCell(9);
			cell.setCellValue("발송결과 건수");
			sheet.addMergedRegion(new CellRangeAddress(0,0,9,10)); // 발송결과 건수 가로 셀병합
			cell.setCellStyle(style);
			
			cell = row.createCell(11);
			cell.setCellValue("대체문자 건수");
			sheet.addMergedRegion(new CellRangeAddress(0,0,11,12)); // 발송결과 건수 가로 셀병합
			cell.setCellStyle(style);
			
			cell = row.createCell(13);
			cell.setCellValue("예약취소");
			cell.setCellStyle(style);
			
			cell = row.createCell(14);
			cell.setCellStyle(styleLastCell);
			
			row = sheet.createRow(1);
			
			cell = row.createCell(0);
			cell.setCellStyle(style);
			
			cell = row.createCell(1);
			cell.setCellStyle(style);
			
			cell = row.createCell(2);
			cell.setCellStyle(style);
			
			cell = row.createCell(3);
			cell.setCellStyle(style);
			
			cell = row.createCell(4);
			cell.setCellStyle(style);
			
			cell = row.createCell(5);
			cell.setCellStyle(style);
			
			cell = row.createCell(6);
			cell.setCellStyle(style);
			
			cell = row.createCell(7);
			cell.setCellStyle(style);
			
			cell = row.createCell(8);
			cell.setCellStyle(style);
			
			cell = row.createCell(9);
			cell.setCellValue("성공");
			cell.setCellStyle(style);
			
			cell = row.createCell(10);
			cell.setCellValue("실패");
			cell.setCellStyle(style);
			
			cell = row.createCell(11);
			cell.setCellValue("성공");
			cell.setCellStyle(style);
			
			cell = row.createCell(12);
			cell.setCellValue("실패");
			cell.setCellStyle(style);
			
			cell = row.createCell(13);
			cell.setCellStyle(style);
			
			cell = row.createCell(14);
			cell.setCellStyle(styleLastCell);
		} 
		
		for(int i=0; i < resultAllSentList.size(); i++) {
			String msgType = "";
			if(resultAllSentList.get(i).getMsgType().equals("8") && resultAllSentList.get(i).getFileCnt().equals("0")) {
				msgType = "알림톡";
			}else if(resultAllSentList.get(i).getMsgType().equals("9") && !resultAllSentList.get(i).getFileCnt().equals("0")) {
				msgType = "친구톡";
			}
			int excelLen = 0;
			row = sheet.createRow(i+2);
			excelLen = 14;
				
			for(int j=0 ; j < excelLen ; j++) {
				cell = row.createCell(j);
				cell.setCellStyle(style);
				if(j==0) cell.setCellValue(i+1); //번호
				
				if(j==1) { //발송상태
					if("Y".equals(resultAllSentList.get(i).getReserveCYn())) {
						cell.setCellValue("예약취소");
					}else {
						if("0".equals(resultAllSentList.get(i).getCurState())) cell.setCellValue("발송대기");
						if("1".equals(resultAllSentList.get(i).getCurState())) cell.setCellValue("발송중");
						if("3".equals(resultAllSentList.get(i).getCurState())) cell.setCellValue("결과대기");
						if("4".equals(resultAllSentList.get(i).getCurState())) cell.setCellValue("발송완료");
					}
				}
				if(j==2) cell.setCellValue(sdf.format((resultAllSentList.get(i)).getRegdate())); 	//접수일자
				if(j==3) cell.setCellValue(sdf.format((resultAllSentList.get(i)).getReqdate())); 	//예약일자
				if(j==4) {
					cell.setCellValue(resultAllSentList.get(i).getMsgTypeName());
				} 
				if(j==5) cell.setCellValue((resultAllSentList.get(i)).getSmsTxt()); 		//내용
				if(j==6) cell.setCellValue((resultAllSentList.get(i)).getMsgGroupCnt());	//발송건수
				if(j==7) cell.setCellValue((resultAllSentList.get(i)).getCallFrom()); 		//발신번호
				if(j==8) cell.setCellValue((resultAllSentList.get(i)).getTotPrice()); 		//발신번호
					
				//발송결과 성공, 실패 처리
				if(j==9) cell.setCellValue(resultAllSentList.get(i).getSuccessCount()); 	//알림톡 발송결과 성공
				if(j==10) cell.setCellValue(resultAllSentList.get(i).getFailCount()); 		//알림톡 발송결과 실패
				
				if(j==11) cell.setCellValue(resultAllSentList.get(i).getKakaoResendSuccCount()); 		//대체문자 발송결과 대기
				if(j==12) cell.setCellValue(resultAllSentList.get(i).getKakaoResendFailCount()); 		//대체문자 발송결과 대기
				
				if(j==13) {
					if(resultAllSentList.get(i).getCancelDate() != null) {
						cell.setCellValue(sdf.format(resultAllSentList.get(i).getCancelDate())); 		//대체문자 발송결과 대기
					}else {
						cell.setCellValue("-"); 		//대체문자 발송결과 대기
					}
				}
			}
		}
	}
	
	private void kakaoSentExcelReservReady(List<KakaoSentVO> resultAllSentList
			, Row row
			,Sheet sheet
			,Cell cell
			,CellStyle style
			,CellStyle styleLastCell
			,String type
			,String fCnt
			,SimpleDateFormat sdf) {

		{// 화면 리스트
			row = sheet.createRow(0);

			sheet.setColumnWidth(1, 5000); // 접수일자 칼럼의 폭 조절
			sheet.setColumnWidth(2, 5000); // 예약일자 칼럼의 폭 조절
			sheet.setColumnWidth(4, 10000); // 내용 칼럼의 폭 조절
			sheet.setColumnWidth(6, 5000); // 발신번호 칼럼의 폭 조절
			sheet.setColumnWidth(7, 5000); // 금액 칼럼의 폭 조절
			sheet.setColumnWidth(8, 5000); // 예약취소 칼럼의 폭 조절

			cell = row.createCell(0);
			cell.setCellValue("번호");
			cell.setCellStyle(style);

			cell = row.createCell(1);
			cell.setCellValue("접수일자");
			cell.setCellStyle(style);

			cell = row.createCell(2);
			cell.setCellValue("예약일자");
			cell.setCellStyle(style);

			cell = row.createCell(3);
			cell.setCellValue("형태");
			cell.setCellStyle(style);

			cell = row.createCell(4);
			cell.setCellValue("내용");
			cell.setCellStyle(style);

			cell = row.createCell(5);
			cell.setCellValue("발송건수");
			cell.setCellStyle(style);

			cell = row.createCell(6);
			cell.setCellValue("발신번호");
			cell.setCellStyle(style);

			cell = row.createCell(7);
			cell.setCellValue("금액");
			cell.setCellStyle(style);

			cell = row.createCell(8);
			cell.setCellValue("예약취소");
			cell.setCellStyle(style);

		}

		for (int i = 0; i < resultAllSentList.size(); i++) {
			String msgType = "";
			if (resultAllSentList.get(i).getMsgType().equals("8")
					&& resultAllSentList.get(i).getFileCnt().equals("0")) {
				msgType = "알림톡";
			} else if (resultAllSentList.get(i).getMsgType().equals("9")
					&& !resultAllSentList.get(i).getFileCnt().equals("0")) {
				msgType = "친구톡";
			}
			int excelLen = 0;
			row = sheet.createRow(i + 1);
			excelLen = 9;

			for (int j = 0; j < excelLen; j++) {
				cell = row.createCell(j);
				cell.setCellStyle(style);
				if (j == 0)
					cell.setCellValue(i + 1); // 번호
				if (j == 1)
					cell.setCellValue(sdf.format((resultAllSentList.get(i)).getRegdate())); // 접수일자
				if (j == 2)
					cell.setCellValue(sdf.format((resultAllSentList.get(i)).getReqdate())); // 예약일자
				if (j == 3) {
					cell.setCellValue(resultAllSentList.get(i).getMsgTypeName());
				}
				if (j == 4)
					cell.setCellValue((resultAllSentList.get(i)).getSmsTxt()); // 내용
				if (j == 5)
					cell.setCellValue((resultAllSentList.get(i)).getMsgGroupCnt()); // 발송건수
				if (j == 6)
					cell.setCellValue((resultAllSentList.get(i)).getCallFrom()); // 발신번호
				if (j == 7)
					cell.setCellValue((resultAllSentList.get(i)).getTotPrice()); // 발신번호
				if (j == 8) {
					if (resultAllSentList.get(i).getCancelDate() != null) {
						cell.setCellValue(sdf.format(resultAllSentList.get(i).getCancelDate())); // 대체문자 발송결과 대기
					} else {
						cell.setCellValue("-"); // 대체문자 발송결과 대기
					}
				}
			}
		}
	}
	
	private void kakaoSentExcelReservComplete(List<KakaoSentVO> resultAllSentList
			, Row row
			,Sheet sheet
			,Cell cell
			,CellStyle style
			,CellStyle styleLastCell
			,String type
			,String fCnt
			,SimpleDateFormat sdf) {

		{// 화면 리스트
			row = sheet.createRow(0);

			sheet.setColumnWidth(2, 5000); // 접수일자 칼럼의 폭 조절
			sheet.setColumnWidth(3, 5000); // 예약일자 칼럼의 폭 조절
			sheet.setColumnWidth(5, 10000); // 내용 칼럼의 폭 조절
			sheet.setColumnWidth(7, 5000); // 발신번호 칼럼의 폭 조절
			sheet.setColumnWidth(8, 5000); // 금액 칼럼의 폭 조절
			sheet.setColumnWidth(13, 5000); // 예약취소 칼럼의 폭 조절

			//셀병합 처리
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0)); // 번호 세로 셀병합
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1)); // 발송상태 세로 셀병합
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 2, 2)); // 접수일자 세로 셀병합
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 3, 3)); // 예약일자 세로 셀병합
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 4, 4)); // 형태 셀병합
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 5, 5)); // 내용 셀병합
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 6, 6)); // 발송건수 셀병합
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 7, 7)); // 발신번호 셀병합
			sheet.addMergedRegion(new CellRangeAddress(0, 1, 8, 8)); // 금액 셀병합

			cell = row.createCell(0);
			cell.setCellValue("번호");
			cell.setCellStyle(style);

			cell = row.createCell(1);
			cell.setCellValue("발송상태");
			cell.setCellStyle(style);

			cell = row.createCell(2);
			cell.setCellValue("접수일자");
			cell.setCellStyle(style);

			cell = row.createCell(3);
			cell.setCellValue("예약일자");
			cell.setCellStyle(style);

			cell = row.createCell(4);
			cell.setCellValue("형태");
			cell.setCellStyle(style);

			cell = row.createCell(5);
			cell.setCellValue("내용");
			cell.setCellStyle(style);

			cell = row.createCell(6);
			cell.setCellValue("발송건수");
			cell.setCellStyle(style);

			cell = row.createCell(7);
			cell.setCellValue("발신번호");
			cell.setCellStyle(style);

			cell = row.createCell(8);
			cell.setCellValue("금액");
			cell.setCellStyle(style);

			cell = row.createCell(9);
			cell.setCellValue("발송결과 건수");
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 9, 10)); // 발송결과 건수 가로 셀병합
			cell.setCellStyle(style);

			cell = row.createCell(11);
			cell.setCellValue("대체문자 건수");
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 11, 12)); // 발송결과 건수 가로 셀병합
			cell.setCellStyle(style);

			cell = row.createCell(13);
			cell.setCellStyle(styleLastCell);

			row = sheet.createRow(1);

			cell = row.createCell(0);
			cell.setCellStyle(style);

			cell = row.createCell(1);
			cell.setCellStyle(style);

			cell = row.createCell(2);
			cell.setCellStyle(style);

			cell = row.createCell(3);
			cell.setCellStyle(style);

			cell = row.createCell(4);
			cell.setCellStyle(style);

			cell = row.createCell(5);
			cell.setCellStyle(style);

			cell = row.createCell(6);
			cell.setCellStyle(style);

			cell = row.createCell(7);
			cell.setCellStyle(style);

			cell = row.createCell(8);
			cell.setCellStyle(style);

			cell = row.createCell(9);
			cell.setCellValue("성공");
			cell.setCellStyle(style);

			cell = row.createCell(10);
			cell.setCellValue("실패");
			cell.setCellStyle(style);

			cell = row.createCell(11);
			cell.setCellValue("성공");
			cell.setCellStyle(style);

			cell = row.createCell(12);
			cell.setCellValue("실패");
			cell.setCellStyle(style);

			cell = row.createCell(13);
			cell.setCellStyle(styleLastCell);
		}

		for (int i = 0; i < resultAllSentList.size(); i++) {
			String msgType = "";
			if (resultAllSentList.get(i).getMsgType().equals("8")
					&& resultAllSentList.get(i).getFileCnt().equals("0")) {
				msgType = "알림톡";
			} else if (resultAllSentList.get(i).getMsgType().equals("9")
					&& !resultAllSentList.get(i).getFileCnt().equals("0")) {
				msgType = "친구톡";
			}
			int excelLen = 0;
			row = sheet.createRow(i + 2);
			excelLen = 13;

			for (int j = 0; j < excelLen; j++) {
				cell = row.createCell(j);
				cell.setCellStyle(style);
				if (j == 0)
					cell.setCellValue(i + 1); // 번호

				if (j == 1) { // 발송상태
					if ("Y".equals(resultAllSentList.get(i).getReserveCYn())) {
						cell.setCellValue("예약취소");
					} else {
						if ("0".equals(resultAllSentList.get(i).getCurState()))
							cell.setCellValue("발송대기");
						if ("1".equals(resultAllSentList.get(i).getCurState()))
							cell.setCellValue("발송중");
						if ("3".equals(resultAllSentList.get(i).getCurState()))
							cell.setCellValue("결과대기");
						if ("4".equals(resultAllSentList.get(i).getCurState()))
							cell.setCellValue("발송완료");
					}
				}
				if (j == 2)
					cell.setCellValue(sdf.format((resultAllSentList.get(i)).getRegdate())); // 접수일자
				if (j == 3)
					cell.setCellValue(sdf.format((resultAllSentList.get(i)).getReqdate())); // 예약일자
				if (j == 4) {
					cell.setCellValue(resultAllSentList.get(i).getMsgTypeName());
				}
				if (j == 5)
					cell.setCellValue((resultAllSentList.get(i)).getSmsTxt()); // 내용
				if (j == 6)
					cell.setCellValue((resultAllSentList.get(i)).getMsgGroupCnt()); // 발송건수
				if (j == 7)
					cell.setCellValue((resultAllSentList.get(i)).getCallFrom()); // 발신번호
				if (j == 8)
					cell.setCellValue((resultAllSentList.get(i)).getTotPrice()); // 발신번호

				//발송결과 성공, 실패 처리
				if (j == 9)
					cell.setCellValue(resultAllSentList.get(i).getSuccessCount()); // 알림톡 발송결과 성공
				if (j == 10)
					cell.setCellValue(resultAllSentList.get(i).getFailCount()); // 알림톡 발송결과 실패

				if (j == 11)
					cell.setCellValue(resultAllSentList.get(i).getKakaoResendSuccCount()); // 대체문자 발송결과 대기
				if (j == 12)
					cell.setCellValue(resultAllSentList.get(i).getKakaoResendFailCount()); // 대체문자 발송결과 대기
			}
		}
	}
	
	private void kakaoSentExcelReservCancel(List<KakaoSentVO> resultAllSentList
			, Row row
			,Sheet sheet
			,Cell cell
			,CellStyle style
			,CellStyle styleLastCell
			,String type
			,String fCnt
			,SimpleDateFormat sdf) {

		{// 화면 리스트
			row = sheet.createRow(0);

			sheet.setColumnWidth(1, 5000); // 접수일자 칼럼의 폭 조절
			sheet.setColumnWidth(2, 5000); // 예약일자 칼럼의 폭 조절
			sheet.setColumnWidth(3, 5000); // 취소일자 칼럼의 폭 조절
			sheet.setColumnWidth(5, 10000); // 내용 칼럼의 폭 조절
			sheet.setColumnWidth(6, 5000); // 발신번호 칼럼의 폭 조절
			sheet.setColumnWidth(7, 5000); // 금액 칼럼의 폭 조절

			cell = row.createCell(0);
			cell.setCellValue("번호");
			cell.setCellStyle(style);

			cell = row.createCell(1);
			cell.setCellValue("접수일자");
			cell.setCellStyle(style);

			cell = row.createCell(2);
			cell.setCellValue("예약일자");
			cell.setCellStyle(style);

			cell = row.createCell(3);
			cell.setCellValue("취소일자");
			cell.setCellStyle(style);
			
			cell = row.createCell(4);
			cell.setCellValue("형태");
			cell.setCellStyle(style);

			cell = row.createCell(5);
			cell.setCellValue("내용");
			cell.setCellStyle(style);

			cell = row.createCell(6);
			cell.setCellValue("발송건수");
			cell.setCellStyle(style);

			cell = row.createCell(7);
			cell.setCellValue("발신번호");
			cell.setCellStyle(style);

			cell = row.createCell(8);
			cell.setCellValue("금액");
			cell.setCellStyle(style);

		}

		for (int i = 0; i < resultAllSentList.size(); i++) {
			String msgType = "";
			if (resultAllSentList.get(i).getMsgType().equals("8")
					&& resultAllSentList.get(i).getFileCnt().equals("0")) {
				msgType = "알림톡";
			} else if (resultAllSentList.get(i).getMsgType().equals("9")
					&& !resultAllSentList.get(i).getFileCnt().equals("0")) {
				msgType = "친구톡";
			}
			int excelLen = 0;
			row = sheet.createRow(i + 1);
			excelLen = 9;

			for (int j = 0; j < excelLen; j++) {
				cell = row.createCell(j);
				cell.setCellStyle(style);
				if (j == 0)
					cell.setCellValue(i + 1); // 번호
				if (j == 1)
					cell.setCellValue(sdf.format((resultAllSentList.get(i)).getRegdate())); // 접수일자
				if (j == 2)
					cell.setCellValue(sdf.format((resultAllSentList.get(i)).getReqdate())); // 예약일자
				if (j == 3) {
					cell.setCellValue(sdf.format((resultAllSentList.get(i)).getCancelDate())); // 취소일자
				}
				if (j == 4) {
					cell.setCellValue(resultAllSentList.get(i).getMsgTypeName());
				}
				if (j == 5)
					cell.setCellValue((resultAllSentList.get(i)).getSmsTxt()); // 내용
				if (j == 6)
					cell.setCellValue((resultAllSentList.get(i)).getMsgGroupCnt()); // 발송건수
				if (j == 7)
					cell.setCellValue((resultAllSentList.get(i)).getCallFrom()); // 발신번호
				if (j == 8)
					cell.setCellValue((resultAllSentList.get(i)).getTotPrice()); // 발신번호
			}
		}
	}
	
	
	/**
	 * 예약관리 출력하기 팝업화면 
	 *
	 * @param MjonMsgSentVO
	 * @param 
	 * @param sessionVO
	 * @param model
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value= {"/web/mjon/reservmsg/printReservKakaoDataAjax.do"})
	public String printReservKakaoDataAjax(
			@ModelAttribute("searchVO") MjonResvMsgVO mjonResvMsgVO, KakaoSentVO kakaoSentVO,
			ModelMap model) throws Exception {

		
		//로그인 권한정보 불러오기
    	LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null;
    	String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId());
    	String userNm = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getName());
    	
    	if(userId.equals("")) {
    		
    		/*modelAndView.addObject("status", "loginFail");
    		modelAndView.addObject("message", "로그인이 필요합니다.");
    		return modelAndView;*/
    		
    	}else {
    		
    		kakaoSentVO.setUserId(userId);
    		
    	}
    	
		
    	// 검색 리스트 불러오기
		if(kakaoSentVO.getPageUnit() != 10) {
			kakaoSentVO.setPageUnit(mjonResvMsgVO.getPageUnit());
		}
		
    	//기본 내림차순 정렬
    	if(kakaoSentVO.getSearchSortOrd().equals("")) {
    		
    		kakaoSentVO.setSearchSortOrd("desc");
    		kakaoSentVO.setSearchSortCnd("regdate");
    	}
    	
    	//선택 탭 정보 저장
    	//mjonResvMsgVO.setSearchMsgType(mjonResvMsgVO.getTabType());
    	
		/** pageing */
		PaginationInfo paginationInfo = new PaginationInfo();
		paginationInfo.setCurrentPageNo(kakaoSentVO.getPageIndex());
		paginationInfo.setRecordCountPerPage(kakaoSentVO.getPageUnit());
		paginationInfo.setPageSize(kakaoSentVO.getPageSize());

		kakaoSentVO.setFirstIndex(paginationInfo.getFirstRecordIndex());
		kakaoSentVO.setLastIndex(paginationInfo.getLastRecordIndex());
		kakaoSentVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage());
    	
    	//전체 발송 리스트 불러오기
    	List<KakaoSentVO> resultAllResList = kakaoSentService.selectReservKakaoSentList(kakaoSentVO);
    	model.addAttribute("resultAllSentList", resultAllResList);
    	model.addAttribute("resultAllSentCnt", resultAllResList.size());
    	
    	model.addAttribute("searchKeyword", mjonResvMsgVO.getSearchKeyword());
    	paginationInfo.setTotalRecordCount( resultAllResList.size()> 0 ? (Integer.parseInt((resultAllResList.get(0)).getTotMsgCnt())) : 0);
    	model.addAttribute("paginationInfo", paginationInfo);
    	model.addAttribute("totalRecordCount", paginationInfo.getTotalRecordCount());
    	
    	
    	model.addAttribute("mjonMsgSentVO", mjonResvMsgVO);
    	model.addAttribute("userNm", userNm);
    	
    	Calendar cal = Calendar.getInstance();

    	int year = cal.get(Calendar.YEAR);
    	int month = cal.get(Calendar.MONTH) + 1;
    	int day = cal.get(Calendar.DAY_OF_MONTH);
    	
    	model.addAttribute("year", year);
    	model.addAttribute("month", month);
    	model.addAttribute("day", day);
    	
		
		return "web/reservedmsg/ReservedKakaoPrintListPopUp";
	}
	
	//파일 이름을 이용하여 첨부파일 아이디(atchFileId), 첨부파일 순번(fileExtsn)을 찾는 함수
	public FileVO getFilePathToAtchFileId(String filePath) throws Exception{
		
		//마지막 / 의 인덱스 값 +1을 기준으로 문자열을 짤라줌
		String strFilePath = filePath.substring(filePath.lastIndexOf("/")+1);
		
		FileVO returnFileVO = new FileVO();
		
		//파일 이름과 확장자를 분리해줌
		String[] arrFileNm = strFilePath.split("\\.");
		String fileNm = arrFileNm[0];
		String fileExtsn = arrFileNm[1];
		
		FileVO fileVO = new FileVO();
		fileVO.setStreFileNm(fileNm);
		fileVO.setFileExtsn(fileExtsn);
		returnFileVO = mjonReservMsgService.selectFileNmToAtchFileIdInfo(fileVO);
    			
		return returnFileVO;
		
	}
	
}