package itn.let.mjo.tax.web;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Map;

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.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
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.ComDefaultCodeVO;
import itn.com.cmm.EgovMessageSource;
import itn.com.cmm.LoginVO;
import itn.com.cmm.service.EgovCmmUseService;
import itn.com.cmm.service.EgovFileMngService;
import itn.com.cmm.service.EgovFileMngUtil;
import itn.com.cmm.service.FileVO;
import itn.com.cmm.util.RedirectUrlMaker;
import itn.com.utl.fcc.service.EgovStringUtil;
import itn.let.mjo.tax.service.TaxService;
import itn.let.mjo.tax.service.TaxVO;
import itn.let.utl.fcc.service.EgovCryptoUtil;

@Controller
public class TaxController {
		
	@Resource(name="taxService")
    private TaxService taxService;
	
	@Resource(name = "egovCryptoUtil")
	EgovCryptoUtil egovCryptoUtil;
	
	@Resource(name = "EgovCmmUseService")
	private EgovCmmUseService cmmUseService;
	
	@Resource(name="EgovFileMngUtil")
	private EgovFileMngUtil fileUtil;
	 
	@Resource(name="EgovFileMngService")
    private EgovFileMngService fileMngService;
	
	@Resource(name = "EgovFileMngService")
	private EgovFileMngService fileService;
	
	/** 사업자번호 */
	@Value("#{globalSettings['Globals.Business.number']}")
	private String businessNumber;
	
	/** 나이스페이 아이디 */ 
	@Value("#{globalSettings['Globals.Nicepay.Id']}")
	private String nicepayId;
	
    @Resource(name="egovMessageSource")
    EgovMessageSource egovMessageSource;
	
	//세금계산서 목록 조회
	@RequestMapping("/let/mjo/tax/selectTaxReceiptList.do")
	public String selectTaxReceiptList(@ModelAttribute("searchVO") TaxVO taxVO, ModelMap model) throws Exception{
		
		// 결제방식 코드조회
		ComDefaultCodeVO voComCode = new ComDefaultCodeVO();
		voComCode.setCodeId("ITN036");
		model.addAttribute("payCode", cmmUseService.selectCmmCodeDetail(voComCode));
		
		/** pageing */
		PaginationInfo paginationInfo = new PaginationInfo();
		paginationInfo.setCurrentPageNo(taxVO.getPageIndex());
		paginationInfo.setRecordCountPerPage(taxVO.getPageUnit());
		paginationInfo.setPageSize(taxVO.getPageSize());

		taxVO.setFirstIndex(paginationInfo.getFirstRecordIndex());
		taxVO.setLastIndex(paginationInfo.getLastRecordIndex());
		taxVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage());
		
		//세금계산서 타입 9
		taxVO.setSearchRcptType("9");
		if("".equals(taxVO.getSearchSortCnd())){ //최초조회시 최신것 조회List
			taxVO.setSearchSortCnd("sortfrstRegistPnttm");
			taxVO.setSearchSortOrd("desc");
		}
		//최초 조회 시 접수 처리 미완료만 조회
		if("".equals(taxVO.getSearchConfirmYn())) {
			//taxVO.setSearchConfirmYn("N");
		}
		if("all".equals(taxVO.getSearchConfirmYn())) {
			taxVO.setSearchConfirmYn("");
		}
		
		List<TaxVO> resultList = taxService.selectTaxList(taxVO);
		model.addAttribute("resultList", resultList);

		int totCnt = 0;
    	if(resultList.size() > 0) totCnt = resultList.get(0).getTotCnt();
		paginationInfo.setTotalRecordCount(totCnt);
		
		model.addAttribute("paginationInfo", paginationInfo);
		model.addAttribute("taxVO", taxVO);
		return "uss/ion/tax/taxReceiptList";
	}
	
	//세금계산서, 현금영수증 상세 조회
	@RequestMapping(value= {"/let/mjo/tax/selectTaxReceiptDetail.do","/let/mjo/tax/selectCashReceiptDetail.do"})
	public String selectTaxReceiptDetail(@ModelAttribute("searchVO") TaxVO taxVO, HttpServletRequest request, ModelMap model) throws Exception{
		
		TaxVO result = taxService.selectTaxInfo(taxVO);
		model.addAttribute("result", result);

		String returnUrl = "";
		if(request.getServletPath().equals("/let/mjo/tax/selectTaxReceiptDetail.do")) {
			returnUrl = "uss/ion/tax/taxReceiptDetail";
		} else if (request.getServletPath().equals("/let/mjo/tax/selectCashReceiptDetail.do")) {
			returnUrl = "uss/ion/tax/cashReceiptDetail";
		}
		
		return returnUrl;
	}
	
	//간이영수증 목록 조회
	@RequestMapping("/let/mjo/tax/selectSimpReceiptList.do")
	public String selectSimpReceiptList(@ModelAttribute("searchVO") TaxVO taxVO, ModelMap model) throws Exception{
		
		// 결제방식 코드조회
		ComDefaultCodeVO voComCode = new ComDefaultCodeVO();
		voComCode.setCodeId("ITN036");
		model.addAttribute("payCode", cmmUseService.selectCmmCodeDetail(voComCode));
		
		/** pageing */
		PaginationInfo paginationInfo = new PaginationInfo();
		paginationInfo.setCurrentPageNo(taxVO.getPageIndex());
		paginationInfo.setRecordCountPerPage(taxVO.getPageUnit());
		paginationInfo.setPageSize(taxVO.getPageSize());
		
		taxVO.setFirstIndex(paginationInfo.getFirstRecordIndex());
		taxVO.setLastIndex(paginationInfo.getLastRecordIndex());
		taxVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage());
		
		//간이영수증 타입 : 0
		taxVO.setSearchRcptType("0");
		if("".equals(taxVO.getSearchSortCnd())){ //최초조회시 최신것 조회List
			taxVO.setSearchSortCnd("sortfrstRegistPnttm");
			taxVO.setSearchSortOrd("desc");
		}

		List<TaxVO> resultList = taxService.selectTaxList(taxVO);
		model.addAttribute("resultList", resultList);
		
		int totCnt = 0;
    	if(resultList.size() > 0) totCnt = resultList.get(0).getTotCnt();
		paginationInfo.setTotalRecordCount(totCnt);
		
		model.addAttribute("paginationInfo", paginationInfo);
		model.addAttribute("taxVO", taxVO);
		return "uss/ion/tax/simpReceiptList";
	}
	
	//현금영수증 목록 조회
	@RequestMapping("/let/mjo/tax/selectCashReceiptList.do")
	public String selectCashReceiptList(@ModelAttribute("searchVO") TaxVO taxVO, ModelMap model) throws Exception{
		
		// 결제방식 코드조회
		ComDefaultCodeVO voComCode = new ComDefaultCodeVO();
		voComCode.setCodeId("ITN036");
		model.addAttribute("payCode", cmmUseService.selectCmmCodeDetail(voComCode));
		
		/** pageing */
		PaginationInfo paginationInfo = new PaginationInfo();
		paginationInfo.setCurrentPageNo(taxVO.getPageIndex());
		paginationInfo.setRecordCountPerPage(taxVO.getPageUnit());
		paginationInfo.setPageSize(taxVO.getPageSize());
		
		taxVO.setFirstIndex(paginationInfo.getFirstRecordIndex());
		taxVO.setLastIndex(paginationInfo.getLastRecordIndex());
		taxVO.setRecordCountPerPage(paginationInfo.getRecordCountPerPage());
		
		//현금영수증 타입 1,2
		taxVO.setRcptTypes(new String[]{"1","2","3","5"});
		if("".equals(taxVO.getSearchSortCnd())){ //최초조회시 최신것 조회List
			taxVO.setSearchSortCnd("sortfrstRegistPnttm");
			taxVO.setSearchSortOrd("desc");
		}
		//최초 조회 시 접수 처리 미완료만 조회
		if("".equals(taxVO.getSearchConfirmYn())) {
			taxVO.setSearchConfirmYn("N");
		}
		if("all".equals(taxVO.getSearchConfirmYn())) {
			taxVO.setSearchConfirmYn("");
		}
		//현금영수증 조회쇠 - 쿼리스트링으로 배열을 넘겨준 후 where절에 in 처리
		if(taxVO.getRcptTypes() != null) {
			taxVO.setRcptTypesStr(String.join(",",taxVO.getRcptTypes()));
		}
		
		List<TaxVO> resultList = taxService.selectTaxList(taxVO);
		model.addAttribute("resultList", resultList);
		
		int totCnt = 0;
    	if(resultList.size() > 0) totCnt = resultList.get(0).getTotCnt();
		paginationInfo.setTotalRecordCount(totCnt);
		
		model.addAttribute("paginationInfo", paginationInfo);
		model.addAttribute("taxVO", taxVO);
		return "uss/ion/tax/cashReceiptList";
	}
	
	//세금계산서 접수 처리
	@RequestMapping(value = {"/let/mjo/tax/updateReceiptTax.do"
							, "/let/mjo/tax/updateReceiptCash.do"
							, "/let/mjo/tax/updatePartialCancelTax.do"
							, "/let/mjo/tax/updatePartialCancelCash.do"})
	public String updateReceiptTax(TaxVO taxVO, HttpServletRequest request, RedirectAttributes redirectAttributes, String[] checkField) throws Exception{
		
		LoginVO loginVO = EgovUserDetailsHelper.isAuthenticated()? (LoginVO)EgovUserDetailsHelper.getAuthenticatedUser():null;
    	String userId = loginVO == null ? "" : EgovStringUtil.isNullToString(loginVO.getId());
    	
    	taxVO.setLastUpdusrId(userId);
    	
		try {
			taxService.updateTax(taxVO);
			redirectAttributes.addFlashAttribute("message", "접수처리 완료하였습니다.");
		}catch(Exception e){
       		redirectAttributes.addFlashAttribute("message", "접수처리 실패하였습니다.");
       	}
		
		String redirectUrl = "";
		if(request.getServletPath().equals("/let/mjo/tax/updateReceiptTax.do") || request.getServletPath().equals("/let/mjo/tax/updatePartialCancelTax.do")) { //세금계산서
			redirectUrl = "/let/mjo/tax/selectTaxReceiptList.do";
		} else if(request.getServletPath().equals("/let/mjo/tax/updateReceiptCash.do") || request.getServletPath().equals("/let/mjo/tax/updatePartialCancelCash.do")) { //현금영수증
			redirectUrl = "/let/mjo/tax/selectCashReceiptList.do";
		}
		RedirectUrlMaker redirectUrlMaker = new RedirectUrlMaker(redirectUrl);
		
    	redirectAttributes.addAttribute("rcptType", taxVO.getRcptType());
    	redirectAttributes.addAttribute("rcptTypesStr", taxVO.getRcptTypesStr());
       	return redirectUrlMaker.getRedirectUrl();
	}
	
	//세금계산서, 현금영수증 발행 - 사용자 결제내역 화면
	@RequestMapping("/web/let/mjo/tax/insertTaxBill.do")
	public String insertCashBill(TaxVO taxVO, RedirectAttributes redirectAttributes) throws Exception{
		//주민등록번호 암호화 - 개인회원 세금계산서(후보자)
		if("p".equals(taxVO.getDept()) && "9".equals(taxVO.getRcptType()) && "C".equals(taxVO.getTaxTrgt())) {
			taxVO.setRegNo(egovCryptoUtil.encrypt(taxVO.getRegNo()));
		}
		taxService.insertCashBill(taxVO);
		redirectAttributes.addFlashAttribute("message", "발행신청 완료하였습니다.");
		RedirectUrlMaker redirectUrlMaker = new RedirectUrlMaker("/web/member/pay/PayList.do");
		return redirectUrlMaker.getRedirectUrl();
	}

	//세금계산서, 현금영수증 발행 - 관리자 결제리스트 화면
	@RequestMapping("/let/mjo/tax/insertTaxBillAdm.do")
	public String insertTaxBillAdm(TaxVO taxVO, RedirectAttributes redirectAttributes) throws Exception{
		//주민등록번호 암호화 - 개인회원 세금계산서(후보자)
		if("p".equals(taxVO.getDept()) && "9".equals(taxVO.getRcptType()) && "C".equals(taxVO.getTaxTrgt())) {
			taxVO.setRegNo(egovCryptoUtil.encrypt(taxVO.getRegNo()));
		}
		taxVO.setConfirmYn("N");
		
		taxService.insertCashBill(taxVO);
		redirectAttributes.addFlashAttribute("message", "발행신청 완료하였습니다.");
		RedirectUrlMaker redirectUrlMaker = new RedirectUrlMaker("/uss/ion/pay/PayList.do");
		return redirectUrlMaker.getRedirectUrl();
	}
	
	// 세금계산서, 현금영수증 발행 - 관리자 결제리스트 화면 Ajax => Multi
	@RequestMapping("/uss/umt/user/insertTaxBillAdmMultiAjax.do")
	public ModelAndView insertTaxBillAdmMultiAjax(HttpServletRequest request
            , @RequestParam(value="moidList[]") List<String> moidList
            , @RequestParam(value="userList[]") List<String> userList
            , @RequestParam(value="regNo") String regNo	
            , @RequestParam(value="rcptType") String rcptType
		) throws Exception {
		
		ModelAndView modelAndView = new ModelAndView();
		modelAndView.setViewName("jsonView");

		boolean isSuccess = true;
		String msg = "";
		
		try {
			if (moidList.size() == userList.size()) {
				for (int i = 0; i < moidList.size(); i++) {
					TaxVO taxVO = new TaxVO();
					taxVO.setRegNo(regNo);
					taxVO.setRcptType(rcptType);
					taxVO.setConfirmYn("N");
					taxVO.setMoid(moidList.get(i));
					taxVO.setFrstRegisterId(userList.get(i));
					taxService.insertCashBill(taxVO);					
				}			
			}
			else {
				isSuccess = false;
				msg = "현금영수증 신청중 오류가 발생했습니다.";				
			}
		}
		catch(Exception e) {
			isSuccess = false;
			msg = e.getMessage();
		}		

		modelAndView.addObject("isSuccess", isSuccess);
		modelAndView.addObject("msg", msg);
			
		return modelAndView;
	}	
	
	
	/* 서류 삭제  */
    @RequestMapping(value = {"/let/mjo/tax/deleteTaxReceipt.do", "/let/mjo/tax/deleteCashReceipt.do", "/let/mjo/tax/deleteSimpReceipt.do"})
    public String deleteReceiptTax(TaxVO taxVO, 
			RedirectAttributes redirectAttributes, HttpServletRequest request,
			Model model) throws Exception {
		
		// 미인증 사용자에 대한 보안처리
		Boolean isAuthenticated = EgovUserDetailsHelper.isAuthenticated();
    	if(!isAuthenticated) {
    		model.addAttribute("message", egovMessageSource.getMessage("fail.common.login"));
        	return "uat/uia/EgovLoginUsr";
    	}

    	try {
			taxService.deleteReceipts(taxVO);
			redirectAttributes.addFlashAttribute("message", "삭제 완료하였습니다.");
		}catch(Exception e){
       		redirectAttributes.addFlashAttribute("message", "삭제 실패하였습니다.");
       	}
    	
    	String redirectUrl = "";
    	
    	if(request.getServletPath().equals("/let/mjo/tax/deleteTaxReceipt.do")) { //세금계산서
			redirectUrl = "/let/mjo/tax/selectTaxReceiptList.do";
		} else if(request.getServletPath().equals("/let/mjo/tax/deleteCashReceipt.do")) { //현금영수증
			redirectUrl = "/let/mjo/tax/selectCashReceiptList.do";
		} else if(request.getServletPath().equals("/let/mjo/tax/deleteSimpReceipt.do")) { //간이영수증
			redirectUrl = "/let/mjo/tax/selectSimpReceiptList.do";
		}
    	
    	RedirectUrlMaker redirectUrlMaker = new RedirectUrlMaker(redirectUrl);
    	
		redirectAttributes.addFlashAttribute("message", egovMessageSource.getMessage("success.common.delete"));

		return redirectUrlMaker.getRedirectUrl();
	}
	
	
  //배열 정의{"컬럼순차번호, 컬럼이름, 컬럼내용, 컬럼이름에 붙여야할 내용(엑셀코드양식다운로드시 필요)"}
	//세금계산서 엑셀 다운로드
	@RequestMapping(value="/let/mjo/tax/taxExcelDownload.do")
  	public void userExcelDownload(TaxVO taxVO, 
  			HttpServletRequest request, 
  			HttpServletResponse response , 
  			ModelMap model) throws Exception {
  		
		taxVO.setRecordCountPerPage(100000);
		taxVO.setFirstIndex(0);
		taxVO.setDownloadType("taxExcel");
		
		List<TaxVO> taxList = taxService.selectTaxList(taxVO);
	
		// 메모리에 100개의 행을 유지합니다. 행의 수가 넘으면 디스크에 적습니다.
		SXSSFWorkbook wb = new SXSSFWorkbook(100);
		String qestnrSj = "세금계산서 신청 목록" ; //제목
		Sheet sheet = wb.createSheet(qestnrSj);
		Cell cell = null;
		Row row = null;
		
		String fileName ="세금계산서 신청 목록";
		//현재 일시 구하기
		SimpleDateFormat dateForm = new SimpleDateFormat("yyyyMMdd");
        Calendar today = Calendar.getInstance();
        String now = dateForm.format(today.getTime());

		
		try {
			row = sheet.createRow(6);
			
			cell = row.createCell(0);
	    	cell.setCellValue("전자(세금)계산서 종류(01일반, 02:영세율)");
	    	
	    	cell = row.createCell(1);
	    	cell.setCellValue("작성일자");
	    	
	    	cell = row.createCell(2);
	    	cell.setCellValue("공급자 등록번호('-'없이 입력)");
	    	
	    	cell = row.createCell(3);
	    	cell.setCellValue("공급자 종사업장번호");
	    	
	    	cell = row.createCell(4);
	    	cell.setCellValue("공급자 상호");
	    	
	    	cell = row.createCell(5);
	    	cell.setCellValue("공급자 성명");
	    	
	    	cell = row.createCell(6);
	    	cell.setCellValue("공급자 사업장주소");
	    	
	    	cell = row.createCell(7);
	    	cell.setCellValue("공급자 업태");
	    	
	    	cell = row.createCell(8);
	    	cell.setCellValue("공급자 종목");
	    	
	    	cell = row.createCell(9);
	    	cell.setCellValue("공급자 이메일");
	    	
	    	cell = row.createCell(10);
	    	cell.setCellValue("공급받는자 등록번호('-' 없이 입력)");
	    	
	    	cell = row.createCell(11);
	    	cell.setCellValue("공급받는자 종사업장번호");
	    	
	    	cell = row.createCell(12);
	    	cell.setCellValue("공급받는자 상호");
	    	
	    	cell = row.createCell(13);
	    	cell.setCellValue("공급받는자 성명");
	    	
	    	cell = row.createCell(14);
	    	cell.setCellValue("공급받는자 사업장주소");
	    	
	    	cell = row.createCell(15);
	    	cell.setCellValue("공급받는자 업태");
	    	
	    	cell = row.createCell(16);
	    	cell.setCellValue("공급받는자 종목");
	    	
	    	cell = row.createCell(17);
	    	cell.setCellValue("공급받는자 이메일1");
	    	
	    	cell = row.createCell(18);
	    	cell.setCellValue("공급받는자 이메일2");
	    	
	    	cell = row.createCell(19);
	    	cell.setCellValue("공급가액");
	    	
	    	cell = row.createCell(20);
	    	cell.setCellValue("세액");
	    	
	    	cell = row.createCell(21);
	    	cell.setCellValue("비고");
	    	
	    	cell = row.createCell(22);
	    	cell.setCellValue("일자1(2자리, 작성년월 제외)");
	    	
	    	cell = row.createCell(23);
	    	cell.setCellValue("품목1");
	    	
	    	cell = row.createCell(24);
	    	cell.setCellValue("규격1");
	    	
	    	cell = row.createCell(25);
	    	cell.setCellValue("수량1");
	    	
	    	cell = row.createCell(26);
	    	cell.setCellValue("단가1");
	    	
	    	cell = row.createCell(27);
	    	cell.setCellValue("공급가액1");
	    	
	    	cell = row.createCell(28);
	    	cell.setCellValue("세액1");
	    	
	    	cell = row.createCell(29);
	    	cell.setCellValue("품목비고1");
	    	
	    	cell = row.createCell(30);
	    	cell.setCellValue("일자2(2자리, 작성년월 제외)");
	    	
	    	cell = row.createCell(31);
	    	cell.setCellValue("품목2");
	    	
	    	cell = row.createCell(32);
	    	cell.setCellValue("규격2");
	    	
	    	cell = row.createCell(33);
	    	cell.setCellValue("수량2");
	    	
	    	cell = row.createCell(34);
	    	cell.setCellValue("단가2");
	    	
	    	cell = row.createCell(35);
	    	cell.setCellValue("공급가액2");
	    	
	    	cell = row.createCell(36);
	    	cell.setCellValue("세액2");
	    	
	    	cell = row.createCell(37);
	    	cell.setCellValue("품목비고2");
	    	
	    	cell = row.createCell(38);
	    	cell.setCellValue("일자3(2자리, 작성년월 제외)");
	    	
	    	cell = row.createCell(39);
	    	cell.setCellValue("품목3");
	    	
	    	cell = row.createCell(40);
	    	cell.setCellValue("규격3");
	    	
	    	cell = row.createCell(41);
	    	cell.setCellValue("수량3");
	    	
	    	cell = row.createCell(42);
	    	cell.setCellValue("단가3");
	    	
	    	cell = row.createCell(43);
	    	cell.setCellValue("공급가액3");
	    	
	    	cell = row.createCell(44);
	    	cell.setCellValue("세액3");
	    	
	    	cell = row.createCell(45);
	    	cell.setCellValue("품목비고3");
	    	
	    	cell = row.createCell(46);
	    	cell.setCellValue("일자4(2자리, 작성년월 제외)");
	    	
	    	cell = row.createCell(47);
	    	cell.setCellValue("품목4");
	    	
	    	cell = row.createCell(48);
	    	cell.setCellValue("규격4");
	    	
	    	cell = row.createCell(49);
	    	cell.setCellValue("수량4");
	    	
	    	cell = row.createCell(50);
	    	cell.setCellValue("단가4");
	    	
	    	cell = row.createCell(51);
	    	cell.setCellValue("공급가액4");
	    	
	    	cell = row.createCell(52);
	    	cell.setCellValue("세액4");
	    	
	    	cell = row.createCell(53);
	    	cell.setCellValue("품목비고4");	    	
	    	
	    	cell = row.createCell(54);
	    	cell.setCellValue("현금");	 
	    	
	    	cell = row.createCell(55);
	    	cell.setCellValue("수표");	 
	    	
	    	cell = row.createCell(56);
	    	cell.setCellValue("어음");	 
	    	
	    	cell = row.createCell(57);
	    	cell.setCellValue("외상미수금");	 
	    	
	    	cell = row.createCell(58);
	    	cell.setCellValue("영수(01),청구(02)");	 
	    	
	    for(int i=0; i < taxList.size(); i++) {
	    	row = sheet.createRow(7+i);
	    	cell = row.createCell(0);
	    	cell.setCellValue("01");
	    	cell = row.createCell(1);
	    	cell.setCellValue(now);
	    	cell = row.createCell(2);
	    	cell.setCellValue("6538700858");
	    	cell = row.createCell(4);
	    	cell.setCellValue("문자온");
	    	cell = row.createCell(5);
	    	cell.setCellValue("유인식");
	    	cell = row.createCell(6);
	    	cell.setCellValue("(12248) 경기도 남양주시 다산순환로 20, A동 735호(다산동, 현대프리미어캠퍼스)");
	    	cell = row.createCell(7);
	    	cell.setCellValue("서비스업");
	    	cell = row.createCell(8);
	    	cell.setCellValue("소프트웨어 개발, 자문 및 공급");
	    	cell = row.createCell(9);
	    	cell.setCellValue("tax@iten.co.kr");
	    	cell = row.createCell(10);
	    	if(taxList.get(i).getRegNo() != null) {
	    		if(taxList.get(i).getRegNo().length() == 10) { //사업자등록번호
		    		cell.setCellValue(taxList.get(i).getRegNo()); //공급받는자 등록번호
		    	}else { //주민등록번호 - 복호화
		    		cell.setCellValue(egovCryptoUtil.decrypt(taxList.get(i).getRegNo())); //공급받는자 등록번호
		    	}
	    	}else {
	    		cell.setCellValue("공급번호");
	    	}
	    	cell = row.createCell(12);
	    	cell.setCellValue(taxList.get(i).getCompanyName());
	    	cell = row.createCell(13);
	    	cell.setCellValue(taxList.get(i).getRepName());
	    	cell = row.createCell(14);
	    	cell.setCellValue(taxList.get(i).getAddr());   	
	    	cell = row.createCell(15);
	    	cell.setCellValue(taxList.get(i).getBtype());   	
	    	cell = row.createCell(16);
	    	cell.setCellValue(taxList.get(i).getBitem());   	
	    	cell = row.createCell(17);
	    	cell.setCellValue(taxList.get(i).getEmail());   	
	    	
	    	int cash = Integer.parseInt( taxList.get(i).getCash());
	    	int VAT = (int)Math.round((((cash/1.1)*0.1)));//세액
	    	int price = cash - VAT;//공급가액
	    	cell = row.createCell(19);
	    	cell.setCellValue(price);//공급가액   	
	    	cell = row.createCell(20);
	    	cell.setCellValue(VAT);//세액
	    	
	    	cell = row.createCell(22);
	    	cell.setCellValue(now.substring(now.length()-2, now.length()));   	
	    	cell = row.createCell(23);
	    	cell.setCellValue("문자온-이용료");   	
	    	cell = row.createCell(27);
	    	cell.setCellValue(price);   	
	    	cell = row.createCell(28);
	    	cell.setCellValue(VAT);   	
	    	cell = row.createCell(58);
	    	cell.setCellValue("01");   	
	    }
	    
	    
	    response.setHeader("Set-Cookie", "fileDownload=true; path=/");
		SimpleDateFormat mSimpleDateFormat = new SimpleDateFormat ( "yyyy_MM_dd_HH_mm_ss", 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) {}
		}
	}
	
	
	@RequestMapping(value = "/let/mjo/tax/taxTextDownload.do")
	public void taxTextDownload(
			HttpServletResponse response
			, HttpServletRequest request) throws Exception {

		try {

			LocalDate now = LocalDate.now(); // 현재 날짜 구하기
			DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd"); // 포맷 설정
			String formatedNow = now.format(formatter); // 포맷 적용
			String dFile = nicepayId+"_"+formatedNow+"_001.txt";
			File file = new File(dFile);
			
			String userAgent = request.getHeader("User-Agent");
			boolean ie = userAgent.indexOf("MSIE") > -1 || userAgent.indexOf("rv:11") > -1;
			String fileName = null;
			
			if (ie) {
				fileName = URLEncoder.encode(file.getName(), "utf-8");
			} else {
				fileName = new String(file.getName().getBytes("iso-8859-1"),"utf-8");
			}
			
			response.setContentType("application/octet-stream");
			response.setHeader("Content-Disposition","attachment;filename=\"" +fileName+"\";");
			
			BufferedOutputStream out=new BufferedOutputStream(response.getOutputStream());
			
			TaxVO taxVO = new TaxVO();
			
			//접수처리 미완료만 조회
			taxVO.setConfirmYn("N");
			taxVO.setSearchConfirmYn("N");
			
			taxVO.setRecordCountPerPage(100000);
			taxVO.setFirstIndex(0);
			
			//현금영수증 조회쇠 - 쿼리스트링으로 배열을 넘겨준 후 where절에 in 처리
			if(taxVO.getRcptTypes() != null) {
				taxVO.setRcptTypesStr(String.join(",",taxVO.getRcptTypes()));
			}
			
			taxVO.setDownloadType("cashTxt");
			
			List<TaxVO> resultList = taxService.selectTaxList(taxVO);
			
			String downInfo = "";
			
			for(int i=0; i < resultList.size(); i++) {
				resultList.get(i).getCash();		// 발행금액, 공급가액
				resultList.get(i).getRepName();		// 구매자명
				resultList.get(i).getEmail();		// 구매자 이메일 주소
				resultList.get(i).getPhone();		// 구매자 전화번호
				resultList.get(i).getRegNo();		// 발행번호(사업자번호, 휴대폰번호)
				resultList.get(i).getRcptType();	// 용도(1:소득공제용, 2:휴대폰 번호)
				
				if(i != 0) {
					downInfo += "\n";
				}
				downInfo += businessNumber+",충전하기,"+resultList.get(i).getCash()+","+resultList.get(i).getCash()+",0,0,0,"+resultList.get(i).getRepName()+","+resultList.get(i).getEmail()+","+resultList.get(i).getPhone()+","+resultList.get(i).getRegNo()+","+resultList.get(i).getRcptType();
			}
			
			
			try {
				
				
				out.write(downInfo.getBytes());
				out.flush();
				
			} finally {
				if(out!=null) out.close();
			}
		} catch (Exception e) {
			
		}
	}
	
	
	@RequestMapping("/let/mjo/tax/taxReceiptFileUpload.do")
	public String taxReceiptFileUpload(
			@ModelAttribute("searchVO") TaxVO taxVO
			, Model model) throws Exception {
		
		TaxVO info = taxService.selectTaxInfo(taxVO); 
		FileVO fileVO = new FileVO();
		fileVO.setAtchFileId(info.getAtchFileId());
		List<FileVO> fileList = fileService.selectFileInfs(fileVO);
		model.addAttribute("fileList", fileList);
		
		
		model.addAttribute("info", info);
		return "/uss/ion/tax/TaxFileUpload";
	}
	
	
	@RequestMapping("/let/mjo/tax/updateTaxReceiptFileAjax.do")
	public ModelAndView updateTaxReceiptFileAjax(
			final MultipartHttpServletRequest multiRequest,
			TaxVO taxVO
		)throws Exception  {
		ModelAndView modelAndView = new ModelAndView();
		modelAndView.setViewName("jsonView");
		
		String orignlFileNm = "";
		
		String atchFileId = taxVO.getAtchFileId();
		final Map<String, MultipartFile> files = multiRequest.getFileMap();
		if(!files.isEmpty()) {
			if ("".equals(atchFileId)) {
				List<FileVO> result = fileUtil.parseFileInf(files, "TAX_", 0, atchFileId, "", "");
				atchFileId = fileMngService.insertFileInfs(result);
				taxVO.setAtchFileId(atchFileId);
			}else {
				FileVO fvo = new FileVO();
				fvo.setAtchFileId(atchFileId);
				int cnt = fileMngService.getMaxFileSN(fvo);
				List<FileVO> result = fileUtil.parseFileInf(files, "TAX_", cnt, atchFileId, "", "");
				fileMngService.updateFileInfs(result);
			}
		}
		
		taxService.updateTaxReceiptFile(taxVO);
		modelAndView.addObject("result", "success");
		return modelAndView;
	}
	
}