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());
						
						if("S".equals(resultAllSentList.get(i).getMsgResult())) {
							resSucCnt = 1;
						} else {
							resFailCnt = 1;
						}
							
						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) {}
  		}
    	
		
	}

}