package jp.mosp.payroll.payment.action;

import java.io.File;
import java.util.Date;
import java.util.List;

import jp.mosp.common.CommonConst;
import jp.mosp.common.common.BaseVo;
import jp.mosp.common.common.MospConst;
import jp.mosp.common.common.MospException;
import jp.mosp.common.common.PoiUtility;
import jp.mosp.common.utils.StringUtil;
import jp.mosp.payroll.base.PayrollAction;
import jp.mosp.payroll.base.PayrollConst;
import jp.mosp.payroll.dao.BankFrikomListDao;
import jp.mosp.payroll.dao.CashKindListDao;
import jp.mosp.payroll.dao.CmCompanyDao;
import jp.mosp.payroll.dto.BankFrikomListDto;
import jp.mosp.payroll.dto.CashKindListDto;
import jp.mosp.payroll.dto.CmCompanyDto;
import jp.mosp.payroll.payment.vo.PaymentReportVo;

import org.jopendocument.dom.spreadsheet.Sheet;
import org.jopendocument.dom.spreadsheet.SpreadSheet;

/**
 * 給与帳票出力(銀行振込及び金種一覧)画面における処理を行う。<br><br>
 * 以下のコマンドを扱う。
 * <ul><li>
 * {@link #CMD_BANK_PERSON}
 * {@link #CMD_BANK_COMPANY}
 * {@link #CMD_CASH_KIND}
 * </li></ul>
 */
public class PaymentReportOutputAction extends PayrollAction {
	
	/**
	 * 画面表示コマンド。<br><br>
	 * 給与計算中の年月を取得、表示する。<br>
	 * もし給与計算中の年月がなかった場合は、システム年月を表示する。<br>
	 */
	public static final String	CMD_BANK_PERSON		= "P9014";
	/**
	 * 
	 */
	public static final String	CMD_BANK_COMPANY	= "P9015";
	/**
	 * 
	 */
	public static final String	CMD_CASH_KIND		= "P9012";
	

	/**
	 * Actionの初期化を行う。<br><br>
	 * 以下の処理を行う。
	 * <ul><li>
	 * {@link PaymentReportOutputAction#PaymentReportOutputAction()}
	 * </li></ul>
	 */
	public PaymentReportOutputAction() {
		super();
		setNeedProcSeq(false);
	}
	
	/* (non-Javadoc)
	 * @see jp.mosp.common.common.BaseAction#getSpecificVo()
	 */
	@Override
	protected BaseVo getSpecificVo() {
		return new PaymentReportVo();
	}
	
	/**
	 * アクション。<br>
	 * 詳細は、クラス説明を参照。<br>
	 */
	public void action() throws Exception {
		//VO取得及び設定
		prepareVo();
		if (cmd.equals(CMD_CASH_KIND)) {
			// 金種一覧表出力
			cashKind();
		} else if (cmd.equals(CMD_BANK_PERSON)) {
			//銀行振込一覧表(個人別)出力
			bankParson();
		} else if (cmd.equals(CMD_BANK_COMPANY)) {
			//銀行振込一覧表(会社銀行別)出力
			bankCompany();
		} else {
			throw new MospException(MospConst.EX_CMD_INVALID);
		}
	}
	
	/**
	 * 給与金種一覧表出力処理を行う。<br><br>
	 * @throws Exception 
	 */
	private void cashKind() throws Exception {
		//コネクション取得
		getConnection();
		// VO取得
		PaymentReportVo vo = (PaymentReportVo)getVo();
		//パラメータ取得
		vo.setParams(request);
		//対象年月取得
		Date date = vo.getDate(String.valueOf(vo.getPltOutYear()), String.valueOf(vo.getPltOutMonth()), String
			.valueOf(PayrollConst.TIME_DEFAULT_DAY));
		//データの取得
		CashKindListDao cashKindList = new CashKindListDao();
		cashKindList.initDao(cfg, cmd, aspUser, user, conn);
		List<CashKindListDto> list = cashKindList.findForCondition(date);
		//DAO準備
		CmCompanyDao dao = new CmCompanyDao();
		dao.initDao(cfg, cmd, aspUser, user, conn);
		// DTO準備
		CmCompanyDto dto;
		dto = dao.findAll();
		if (StringUtil.isNotNull(cfg.getProperty(CommonConst.PPT_KINTAI_USE_ODF))) {
			//ODS
			makeSpreadSheet(list, vo, date, dto);
		} else {
			//XLS
			makeCashKindBook(list, vo, date, dto);
		}
	}
	
	/**
	 * 銀行振込一覧表(個人別)出力処理を行う。<br><br>
	 * @throws Exception 
	 */
	private void bankParson() throws Exception {
		
		//コネクション取得
		getConnection();
		// VO取得
		PaymentReportVo vo = (PaymentReportVo)getVo();
		//パラメータ取得
		vo.setParams(request);
		//対象年月取得
		Date date = vo.getDate(String.valueOf(vo.getPltOutYear()), String.valueOf(vo.getPltOutMonth()), String
			.valueOf(PayrollConst.TIME_DEFAULT_DAY));
		//データの取得
		//振込データ取得
		BankFrikomListDao bankFrikomListDao = new BankFrikomListDao();
		bankFrikomListDao.initDao(cfg, cmd, aspUser, user, conn);
		List<BankFrikomListDto> list = bankFrikomListDao.findForCondition(date);
		//DAO準備
		CmCompanyDao dao = new CmCompanyDao();
		dao.initDao(cfg, cmd, aspUser, user, conn);
		// DTO準備
		CmCompanyDto dto;
		dto = dao.findAll();
		
		if (StringUtil.isNotNull(cfg.getProperty(CommonConst.PPT_KINTAI_USE_ODF))) {
			//ODS
			makeBankSpreadSheet(list, vo, date, dto);
		} else {
			//XLS
			makeAttendanceBook(list, vo, date, dto);
		}
		
	}
	
	/**
	 * 銀行振込一覧表(会社別)出力処理を行う。<br><br>
	 * @throws Exception 
	 */
	private void bankCompany() throws Exception {
		//コネクション取得
		getConnection();
		// VO取得
		PaymentReportVo vo = (PaymentReportVo)getVo();
		//パラメータ取得
		vo.setParams(request);
		//対象年月取得
		Date date = vo.getDate(String.valueOf(vo.getPltOutYear()), String.valueOf(vo.getPltOutMonth()), String
			.valueOf(PayrollConst.TIME_DEFAULT_DAY));
		//データの取得
		//振込データ取得
		BankFrikomListDao bankFrikomListDao = new BankFrikomListDao();
		bankFrikomListDao.initDao(cfg, cmd, aspUser, user, conn);
		List<BankFrikomListDto> list = bankFrikomListDao.findForCondition(date);
		//DAO準備
		CmCompanyDao dao = new CmCompanyDao();
		dao.initDao(cfg, cmd, aspUser, user, conn);
		// DTO準備
		CmCompanyDto dto;
		dto = dao.findAll();
		if (StringUtil.isNotNull(cfg.getProperty(CommonConst.PPT_KINTAI_USE_ODF))) {
			//ODS
			makeCoBankSpreadSheet(list, vo, date, dto);
		} else {
			//XLS
			makeCompanyBankBook(list, vo, date, dto);
		}
		
	}
	
	/**
	 * 銀行振込一覧表作成(個人別給与)XLS
	 * @param dtolist 
	 * @param vo 対象VO
	 * @param date 
	 * @param companyDto 
	 * @throws Exception 
	 */
	private void makeAttendanceBook(List<BankFrikomListDto> dtolist, PaymentReportVo vo, Date date,
			CmCompanyDto companyDto) throws Exception {
		int printDataRow = 7;
		int sumAmount = 0;
		int subTotalCnt = 0;
		int totalAmount = 0;
		int totalCnt = 0;
		String coBankCdNew = "";
		String coBankCdOld = "";
		//帳票作成準備
		PoiUtility poi = new PoiUtility();
		poi.crtNewDocR(0, cfg.getProperty(MospConst.PPT_APP_DOCBASE) + PayrollConst.TEMPLATE_BANK_FRKOM_LIST);
		//フォント設定の為の初期化処理
		poi.createCellStyleAndFont();
		setStyle(poi, true, false);
		//ヘッダー情報設定
		poi.prtItem(1, 0, jpnYM(date, vo) + PayrollConst.NAM_PAYMENT_TARGET);
		poi.prtItem(3, 1, companyDto.getCompanyName());
		Date sysDate = getSystemDate();
		poi.prtItem(3, 13, jpnYMD(sysDate, vo));
		for (int i = 0; i < dtolist.size(); i++) {
			
			BankFrikomListDto dto = dtolist.get(i);
			coBankCdNew = dto.getCoBankCode();
			if (!coBankCdNew.equals(coBankCdOld)) {
				if (i != 0) {
					printDataRow++;
					poi.prtItem(printDataRow, 3, PayrollConst.BRACKET_LEFT + PayrollConst.NMA_SUBTOTAL + "  "
							+ subTotalCnt + PayrollConst.NMA_SHARE + PayrollConst.BRACKET_RIGHT);
					setStyle(poi, false, true);
					totalCnt = totalCnt + subTotalCnt;
					printDataRow++;
				}
				
				poi.prtItem(printDataRow, 5, dto.getCoBankKana());
				setStyle(poi, false, true);
				
				poi.prtItem(printDataRow, 7, dto.getCoBranchKana());
				setStyle(poi, false, true);
				
				printDataRow++;
				
				poi.prtItem(printDataRow, 3, PayrollConst.BRACKET_LEFT + PayrollConst.NAM_CO_BANK_CODE
						+ dto.getCoBankCode() + PayrollConst.BRACKET_RIGHT);
				setStyle(poi, false, true);
				
				poi
					.prtItem(printDataRow, 4, dto.getBankCode() + PayrollConst.FILE_NAME_SEPARATOR
							+ dto.getBranchCode());
				setStyle(poi, false, true);
				
				poi.prtItem(printDataRow, 5, dto.getCoBankName());
				setStyle(poi, false, true);
				
				poi.prtItem(printDataRow, 7, dto.getCoBranchName());
				setStyle(poi, false, true);
				
				poi.prtItem(printDataRow, 9, dto.getCoAccountName().replace(PayrollConst.NMA_YOKIN, ""));
				setStyle(poi, false, true);
				
				poi.prtItem(printDataRow, 11, dto.getCoAccountNo());
				setStyle(poi, false, true);
				
				printDataRow++;
				subTotalCnt = 0;
			}
			
			poi.prtItem(printDataRow, 1, dto.getKCode());
			setStyle(poi, false, true);
			
			poi.prtItem(printDataRow, 3, dto.getHolderKana());
			setStyle(poi, false, true);
			
			poi.prtItem(printDataRow, 5, dto.getPaidBankKana());
			setStyle(poi, false, true);
			
			poi.prtItem(printDataRow, 7, dto.getPaidBranchKana());
			setStyle(poi, false, true);
			
			printDataRow++;
			poi.createCellStyle();
			poi.prtItem(printDataRow, 3, dto.getHolderName());
			setStyle(poi, false, true);
			
			poi.prtItem(printDataRow, 4, dto.getPaidBankCode() + PayrollConst.FILE_NAME_SEPARATOR
					+ dto.getPaidBranchCode());
			setStyle(poi, false, true);
			
			poi.prtItem(printDataRow, 5, dto.getPaidBankName());
			setStyle(poi, false, true);
			
			poi.prtItem(printDataRow, 7, dto.getPaidBranchName());
			setStyle(poi, false, true);
			
			poi.prtItem(printDataRow, 9, dto.getAccountName().replace(PayrollConst.NMA_YOKIN, ""));
			setStyle(poi, false, true);
			
			poi.prtItem(printDataRow, 11, dto.getAccountNo());
			setStyle(poi, false, true);
			
			printDataRow++;
			subTotalCnt++;
			coBankCdOld = coBankCdNew;
			
		}
		if (dtolist.size() != 0) {
			totalCnt = totalCnt + subTotalCnt;
			printDataRow++;
			poi.prtItem(printDataRow, 3, PayrollConst.BRACKET_LEFT + PayrollConst.NMA_SUBTOTAL + "  " + subTotalCnt
					+ PayrollConst.NMA_SHARE + PayrollConst.BRACKET_RIGHT);
			setStyle(poi, false, true);
			printDataRow = printDataRow + 2;
			poi.prtItem(printDataRow, 3, PayrollConst.BRACKET_LEFT + PayrollConst.NAM_COMMON_SUM + "  " + totalCnt
					+ PayrollConst.NMA_SHARE + PayrollConst.BRACKET_RIGHT);
			setStyle(poi, false, true);
			
		}
		
		//金額の設定
		setStyle(poi, true, false);
		printDataRow = 10;
		for (int i = 0; i < dtolist.size(); i++) {
			BankFrikomListDto dto = dtolist.get(i);
			coBankCdNew = dto.getCoBankCode();
			if (!coBankCdNew.equals(coBankCdOld)) {
				if (i != 0) {
					//銀行の合計金額
					poi.prtBorderTop(printDataRow, 13);
					poi.prtItem(printDataRow, 13, insertComma(String.valueOf(sumAmount)));
					setStyle(poi, false, false);
					printDataRow = printDataRow + 4;
					setStyle(poi, true, false);
					totalAmount = totalAmount + sumAmount;
					sumAmount = 0;
				}
			}
			poi.prtItem(printDataRow, 13, insertComma(String.valueOf(dto.getBankPayAmount())));
			setStyle(poi, false, false);
			sumAmount = sumAmount + dto.getBankPayAmount();
			printDataRow = printDataRow + 2;
			coBankCdOld = coBankCdNew;
			
		}
		if (dtolist.size() != 0) {
			poi.prtBorderTop(printDataRow, 13);
			poi.prtItem(printDataRow, 13, insertComma(String.valueOf(sumAmount)));
			setStyle(poi, false, false);
			totalAmount = totalAmount + sumAmount;
			//総合計
			printDataRow = printDataRow + 2;
			poi.prtBorderTop(printDataRow, 13);
			poi.prtItem(printDataRow, 13, insertComma(String.valueOf(totalAmount)));
			setStyle(poi, false, false);
		}
		
		//XLS
		String fileName = vo.getPltOutYear() + PayrollConst.FILE_NAME_SEPARATOR + vo.getPltOutMonth()
				+ PayrollConst.FILE_NAME_SEPARATOR + PayrollConst.FILE_BANK_FRKOM_LIST;
		request.setAttribute(MospConst.ATT_FILE_NAME, fileName);
		request.setAttribute(MospConst.ATT_FILE_OBJECT, poi.getWorkbook());
	}
	
	/**
	 * 銀行振込一覧表作成(個人別給与)ODS
	 * @param dtolist 
	 * @param vo 対象VO
	 * @param date 
	 * @param companyDto 
	 * @throws Exception 
	 */
	private void makeBankSpreadSheet(List<BankFrikomListDto> dtolist, PaymentReportVo vo, Date date,
			CmCompanyDto companyDto) throws Exception {
		int printDataRow = 7;
		int sumAmount = 0;
		int subTotalCnt = 0;
		int totalAmount = 0;
		int totalCnt = 0;
		String coBankCdNew = "";
		String coBankCdOld = "";
		
		//テンプレートファイル
		StringBuffer sb = new StringBuffer();
		sb.append(cfg.getProperty(MospConst.PPT_APP_DOCBASE));
		sb.append(PayrollConst.TEMPLATE_BANK_FRKOM_LIST_ODS);
		//帳票作成
		SpreadSheet ss = SpreadSheet.createFromFile(new File(sb.toString()));
		//シート
		Sheet sheet = ss.getSheet(0);
		//ヘッダー情報設定
		sheet.getCellAt(0, 1).setValue(jpnYM(date, vo) + PayrollConst.NAM_PAYMENT_TARGET);
		sheet.getCellAt(1, 3).setValue(companyDto.getCompanyName());
		Date sysDate = getSystemDate();
		sheet.getCellAt(12, 3).setValue(jpnYMD(sysDate, vo));
		//罫線スタイルコードの取得
		String styleCodeBorderBotom = sheet.getCellAt(4, 3).getStyleName();
		//明細データの設定
		sheet.insertDuplicatedRows(sheet.getRowCount() - 1, 6000);
		for (int i = 0; i < dtolist.size(); i++) {
			BankFrikomListDto dto = dtolist.get(i);
			//金額以外の部位の印字
			coBankCdNew = dto.getCoBankCode();
			if (!coBankCdNew.equals(coBankCdOld)) {
				//会社銀行コードが変わったら
				if (i != 0) {
					//小計項目を表示
					printDataRow = printDataRow + 2;
					sheet.getCellAt(3, printDataRow).setValue(
							PayrollConst.BRACKET_LEFT + PayrollConst.NMA_SUBTOTAL + "  " + subTotalCnt
									+ PayrollConst.NMA_SHARE + PayrollConst.BRACKET_RIGHT);
					totalCnt = totalCnt + subTotalCnt;
					printDataRow++;
				}
				//新しい会社銀行を印字する
				sheet.getCellAt(5, printDataRow).setValue(dto.getCoBankKana());
				sheet.getCellAt(7, printDataRow).setValue(dto.getCoBranchKana());
				printDataRow++;
				sheet.getCellAt(3, printDataRow).setValue(
						PayrollConst.BRACKET_LEFT + PayrollConst.NAM_CO_BANK_CODE + dto.getCoBankCode()
								+ PayrollConst.BRACKET_RIGHT);
				sheet.getCellAt(4, printDataRow).setValue(
						dto.getBankCode() + PayrollConst.FILE_NAME_SEPARATOR + dto.getBranchCode());
				sheet.getCellAt(5, printDataRow).setValue(dto.getCoBankName());
				sheet.getCellAt(7, printDataRow).setValue(dto.getCoBranchName());
				sheet.getCellAt(9, printDataRow).setValue(dto.getCoAccountName().replace(PayrollConst.NMA_YOKIN, ""));
				sheet.getCellAt(11, printDataRow).setValue(dto.getCoAccountNo());
				printDataRow++;
				subTotalCnt = 0;
			}
			//明細部分の印字
			sheet.getCellAt(1, printDataRow).setValue(dto.getKCode());
			sheet.getCellAt(3, printDataRow).setValue(dto.getHolderKana());
			sheet.getCellAt(5, printDataRow).setValue(dto.getPaidBankKana());
			sheet.getCellAt(7, printDataRow).setValue(dto.getPaidBranchKana());
			printDataRow++;
			sheet.getCellAt(3, printDataRow).setValue(dto.getHolderName());
			sheet.getCellAt(4, printDataRow).setValue(
					dto.getPaidBankCode() + PayrollConst.FILE_NAME_SEPARATOR + dto.getPaidBranchCode());
			sheet.getCellAt(5, printDataRow).setValue(dto.getPaidBankName());
			sheet.getCellAt(7, printDataRow).setValue(dto.getPaidBranchName());
			sheet.getCellAt(9, printDataRow).setValue(dto.getAccountName().replace(PayrollConst.NMA_YOKIN, ""));
			sheet.getCellAt(11, printDataRow).setValue(dto.getAccountNo());
			printDataRow++;
			subTotalCnt++;
			coBankCdOld = coBankCdNew;
		}
		
		if (dtolist.size() != 0) {
			
			//最後のデータを印字
			totalCnt = totalCnt + subTotalCnt;
			printDataRow = printDataRow + 2;
			sheet.getCellAt(3, printDataRow).setValue(
					PayrollConst.BRACKET_LEFT + PayrollConst.NMA_SUBTOTAL + "  " + subTotalCnt + PayrollConst.NMA_SHARE
							+ PayrollConst.BRACKET_RIGHT);
			printDataRow = printDataRow + 2;
			sheet.getCellAt(3, printDataRow).setValue(
					PayrollConst.BRACKET_LEFT + PayrollConst.NAM_COMMON_SUM + "  " + totalCnt + PayrollConst.NMA_SHARE
							+ PayrollConst.BRACKET_RIGHT);
		}
		
		//金額の設定	
		printDataRow = 10;
		
		for (int i = 0; i < dtolist.size(); i++) {
			BankFrikomListDto dto = dtolist.get(i);
			coBankCdNew = dto.getCoBankCode();
			if (!coBankCdNew.equals(coBankCdOld)) {
				//会社銀行コードが変わったら
				if (i != 0) {
					//銀行の合計金額
					sheet.getCellAt(13, printDataRow).setStyleName(styleCodeBorderBotom);
					printDataRow++;
					sheet.getCellAt(13, printDataRow).setValue(insertComma(String.valueOf(sumAmount)));
					printDataRow = printDataRow + 4;
					totalAmount = totalAmount + sumAmount;
					sumAmount = 0;
				}
			}
			sheet.getCellAt(13, printDataRow).setValue(insertComma(String.valueOf(dto.getBankPayAmount())));
			sumAmount = sumAmount + dto.getBankPayAmount();
			printDataRow = printDataRow + 2;
			coBankCdOld = coBankCdNew;
		}
		
		if (dtolist.size() != 0) {
			//合計値罫線スタイルの設定
			sheet.getCellAt(13, printDataRow).setStyleName(styleCodeBorderBotom);
			printDataRow++;
			sheet.getCellAt(13, printDataRow).setValue(insertComma(String.valueOf(sumAmount)));
			totalAmount = totalAmount + sumAmount;
			//総合計
			printDataRow++;
			sheet.getCellAt(13, printDataRow).setStyleName(styleCodeBorderBotom);
			printDataRow++;
			sheet.getCellAt(13, printDataRow).setValue(insertComma(String.valueOf(totalAmount)));
		}
		
		String fileName = vo.getPltOutYear() + PayrollConst.FILE_NAME_SEPARATOR + vo.getPltOutMonth()
				+ PayrollConst.FILE_NAME_SEPARATOR + PayrollConst.FILE_BANK_FRKOM_LIST_ODS;
		request.setAttribute(MospConst.ATT_FILE_NAME, fileName);
		request.setAttribute(MospConst.ATT_FILE_OBJECT, ss);
	}
	
	/**
	 * 銀行振込一覧表(会社銀行別)作成(給与)XLS
	 * @param vo 対象VO
	 * @throws Exception 
	 */
	private void makeCompanyBankBook(List<BankFrikomListDto> dtolist, PaymentReportVo vo, Date date,
			CmCompanyDto companyDto) throws Exception {
		int printDataRow = 7;
		int sumAmount = 0;
		int totalAmount = 0;
		int subTotalCnt = 0;
		int totalCnt = 0;
		String coBankCdNew = "";
		String coBankCdOld = "";
		//帳票作成準備
		PoiUtility poi = new PoiUtility();
		poi.crtNewDocR(0, cfg.getProperty(MospConst.PPT_APP_DOCBASE) + PayrollConst.TEMPLATE_COMPANY_BANK_FRKOM_LIST);
		//フォント設定の為の初期化処理
		poi.createCellStyleAndFont();
		setStyle(poi, true, false);
		//ヘッダー情報設定
		poi.prtItem(1, 0, jpnYM(date, vo) + PayrollConst.NAM_PAYMENT_TARGET);
		poi.prtItem(3, 1, companyDto.getCompanyName());
		Date sysDate = getSystemDate();
		poi.prtItem(3, 13, jpnYMD(sysDate, vo));
		for (int i = 0; i < dtolist.size(); i++) {
			BankFrikomListDto dto = dtolist.get(i);
			coBankCdNew = dto.getCoBankCode();
			if (!coBankCdNew.equals(coBankCdOld)) {
				if (i != 0) {
					printDataRow++;
					poi.prtItem(printDataRow, 3, PayrollConst.BRACKET_LEFT + PayrollConst.NMA_SUBTOTAL + "  "
							+ subTotalCnt + PayrollConst.NMA_SHARE + PayrollConst.BRACKET_RIGHT);
					setStyle(poi, false, true);
					printDataRow++;
					//総合計の計算(口座数)
					totalCnt = totalCnt + subTotalCnt;
				}
				poi.prtItem(printDataRow, 5, dto.getCoBankKana());
				setStyle(poi, false, true);
				
				poi.prtItem(printDataRow, 7, dto.getCoBranchKana());
				setStyle(poi, false, true);
				
				printDataRow++;
				
				poi.prtItem(printDataRow, 3, PayrollConst.BRACKET_LEFT + PayrollConst.NAM_CO_BANK_CODE
						+ dto.getCoBankCode() + PayrollConst.BRACKET_RIGHT);
				setStyle(poi, false, true);
				
				poi
					.prtItem(printDataRow, 4, dto.getBankCode() + PayrollConst.FILE_NAME_SEPARATOR
							+ dto.getBranchCode());
				setStyle(poi, false, true);
				
				poi.prtItem(printDataRow, 5, dto.getCoBankName());
				setStyle(poi, false, true);
				
				poi.prtItem(printDataRow, 7, dto.getCoBranchName());
				setStyle(poi, false, true);
				
				poi.prtItem(printDataRow, 9, dto.getCoAccountName().replace(PayrollConst.NMA_YOKIN, ""));
				setStyle(poi, false, true);
				
				poi.prtItem(printDataRow, 11, dto.getCoAccountNo());
				setStyle(poi, false, true);
				
				printDataRow++;
				subTotalCnt = 0;
			}
			subTotalCnt++;
			coBankCdOld = coBankCdNew;
			
		}
		if (dtolist.size() != 0) {
			printDataRow++;
			poi.prtItem(printDataRow, 3, PayrollConst.BRACKET_LEFT + PayrollConst.NMA_SUBTOTAL + "  " + subTotalCnt
					+ PayrollConst.NMA_SHARE + PayrollConst.BRACKET_RIGHT);
			setStyle(poi, false, true);
			
			printDataRow++;
			
			//総合計の計算(口座数)
			totalCnt = totalCnt + subTotalCnt;
			
			//最後に総合計をプリントする。
			printDataRow++;
			
			poi.prtItem(printDataRow, 3, PayrollConst.BRACKET_LEFT + PayrollConst.NAM_COMMON_SUM + "  " + totalCnt
					+ PayrollConst.NMA_SHARE + PayrollConst.BRACKET_RIGHT);
			setStyle(poi, false, true);
			
		}
		//金額の設定
		setStyle(poi, true, false);
		printDataRow = 10;
		for (int i = 0; i < dtolist.size(); i++) {
			BankFrikomListDto dto = dtolist.get(i);
			coBankCdNew = dto.getCoBankCode();
			if (!coBankCdNew.equals(coBankCdOld)) {
				if (i != 0) {
					poi.prtItem(printDataRow, 13, insertComma(String.valueOf(sumAmount)));
					setStyle(poi, false, false);
					printDataRow = printDataRow + 4;
					//総合計の計算(金額)
					totalAmount = totalAmount + sumAmount;
				}
				sumAmount = 0;
				coBankCdOld = coBankCdNew;
			}
			
			//小計の計算
			sumAmount = sumAmount + dto.getBankPayAmount();
			
		}
		if (dtolist.size() != 0) {
			poi.prtItem(printDataRow, 13, insertComma(String.valueOf(sumAmount)));
			setStyle(poi, false, false);
			//総合計の計算(金額)
			totalAmount = totalAmount + sumAmount;
			//最後に総合計をプリントする。
			printDataRow = printDataRow + 2;
			poi.prtBorderTop(printDataRow, 13);
			poi.prtItem(printDataRow, 13, insertComma(String.valueOf(totalAmount)));
			setStyle(poi, false, false);
			
		}
		
		//XLS
		String fileName = vo.getPltOutYear() + PayrollConst.FILE_NAME_SEPARATOR + vo.getPltOutMonth()
				+ PayrollConst.FILE_NAME_SEPARATOR + PayrollConst.FILE_COMPANY_BANK_FRKOM_LIST;
		request.setAttribute(MospConst.ATT_FILE_NAME, fileName);
		request.setAttribute(MospConst.ATT_FILE_OBJECT, poi.getWorkbook());
	}
	
	/**
	 * 銀行振込一覧表作成(会社別給与)ODS
	 * @param dtolist 
	 * @param vo 対象VO
	 * @param date 
	 * @param companyDto 
	 * @throws Exception 
	 */
	private void makeCoBankSpreadSheet(List<BankFrikomListDto> dtolist, PaymentReportVo vo, Date date,
			CmCompanyDto companyDto) throws Exception {
		int printDataRow = 7;
		int sumAmount = 0;
		int subTotalCnt = 0;
		int totalAmount = 0;
		int totalCnt = 0;
		String coBankCdNew = "";
		String coBankCdOld = "";
		
		//テンプレートファイル
		StringBuffer sb = new StringBuffer();
		sb.append(cfg.getProperty(MospConst.PPT_APP_DOCBASE));
		sb.append(PayrollConst.TEMPLATE_COMPANY_BANK_FRKOM_LIST_ODS);
		//帳票作成
		SpreadSheet ss = SpreadSheet.createFromFile(new File(sb.toString()));
		//シート
		Sheet sheet = ss.getSheet(0);
		//ヘッダー情報設定
		sheet.getCellAt(0, 1).setValue(jpnYM(date, vo) + PayrollConst.NAM_PAYMENT_TARGET);
		sheet.getCellAt(1, 3).setValue(companyDto.getCompanyName());
		Date sysDate = getSystemDate();
		sheet.getCellAt(12, 3).setValue(jpnYMD(sysDate, vo));
		//罫線スタイルコードの取得
		String styleCodeBorderBotom = sheet.getCellAt(4, 3).getStyleName();
		//明細データの設定
		sheet.insertDuplicatedRows(sheet.getRowCount() - 1, 6000);
		for (int i = 0; i < dtolist.size(); i++) {
			BankFrikomListDto dto = dtolist.get(i);
			//金額以外の部位の印字
			coBankCdNew = dto.getCoBankCode();
			if (!coBankCdNew.equals(coBankCdOld)) {
				//会社銀行コードが変わったら
				if (i != 0) {
					//小計項目を表示
					printDataRow = printDataRow + 2;
					sheet.getCellAt(3, printDataRow).setValue(
							PayrollConst.BRACKET_LEFT + PayrollConst.NMA_SUBTOTAL + "  " + subTotalCnt
									+ PayrollConst.NMA_SHARE + PayrollConst.BRACKET_RIGHT);
					totalCnt = totalCnt + subTotalCnt;
					printDataRow++;
				}
				//新しい会社銀行を印字する
				sheet.getCellAt(5, printDataRow).setValue(dto.getCoBankKana());
				sheet.getCellAt(7, printDataRow).setValue(dto.getCoBranchKana());
				printDataRow++;
				sheet.getCellAt(3, printDataRow).setValue(
						PayrollConst.BRACKET_LEFT + PayrollConst.NAM_CO_BANK_CODE + dto.getCoBankCode()
								+ PayrollConst.BRACKET_RIGHT);
				sheet.getCellAt(4, printDataRow).setValue(
						dto.getBankCode() + PayrollConst.FILE_NAME_SEPARATOR + dto.getBranchCode());
				sheet.getCellAt(5, printDataRow).setValue(dto.getCoBankName());
				sheet.getCellAt(7, printDataRow).setValue(dto.getCoBranchName());
				sheet.getCellAt(9, printDataRow).setValue(dto.getCoAccountName().replace(PayrollConst.NMA_YOKIN, ""));
				sheet.getCellAt(11, printDataRow).setValue(dto.getCoAccountNo());
				printDataRow++;
				subTotalCnt = 0;
			}
			//名細部出力編集
			subTotalCnt++;
			coBankCdOld = coBankCdNew;
		}
		if (dtolist.size() != 0) {
			//最後のデータを印字
			totalCnt = totalCnt + subTotalCnt;
			printDataRow = printDataRow + 2;
			sheet.getCellAt(3, printDataRow).setValue(
					PayrollConst.BRACKET_LEFT + PayrollConst.NMA_SUBTOTAL + "  " + subTotalCnt + PayrollConst.NMA_SHARE
							+ PayrollConst.BRACKET_RIGHT);
			printDataRow = printDataRow + 2;
			sheet.getCellAt(3, printDataRow).setValue(
					PayrollConst.BRACKET_LEFT + PayrollConst.NAM_COMMON_SUM + "  " + totalCnt + PayrollConst.NMA_SHARE
							+ PayrollConst.BRACKET_RIGHT);
		}
		
		//金額の設定	
		printDataRow = 10;
		for (int i = 0; i < dtolist.size(); i++) {
			BankFrikomListDto dto = dtolist.get(i);
			coBankCdNew = dto.getCoBankCode();
			if (!coBankCdNew.equals(coBankCdOld)) {
				//会社銀行コードが変わったら
				if (i != 0) {
					//銀行の合計金額
					sheet.getCellAt(13, printDataRow).setStyleName(styleCodeBorderBotom);
					printDataRow++;
					sheet.getCellAt(13, printDataRow).setValue(insertComma(String.valueOf(sumAmount)));
					printDataRow = printDataRow + 4;
					totalAmount = totalAmount + sumAmount;
				}
				sumAmount = 0;
				coBankCdOld = coBankCdNew;
			}
			//小計の計算
			sumAmount = sumAmount + dto.getBankPayAmount();
		}
		if (dtolist.size() != 0) {
			//合計値罫線スタイルの設定
			sheet.getCellAt(13, printDataRow).setStyleName(styleCodeBorderBotom);
			printDataRow++;
			sheet.getCellAt(13, printDataRow).setValue(insertComma(String.valueOf(sumAmount)));
			totalAmount = totalAmount + sumAmount;
			//総合計
			printDataRow++;
			sheet.getCellAt(13, printDataRow).setStyleName(styleCodeBorderBotom);
			printDataRow++;
			sheet.getCellAt(13, printDataRow).setValue(insertComma(String.valueOf(totalAmount)));
		}
		
		String fileName = vo.getPltOutYear() + PayrollConst.FILE_NAME_SEPARATOR + vo.getPltOutMonth()
				+ PayrollConst.FILE_NAME_SEPARATOR + PayrollConst.FILE_COMPANY_BANK_FRKOM_LIST_ODS;
		request.setAttribute(MospConst.ATT_FILE_NAME, fileName);
		request.setAttribute(MospConst.ATT_FILE_OBJECT, ss);
	}
	
	/**
	 * 現金支給金種一覧表作成(給与)XLS
	 * @param dtolist 
	 * @param vo 対象VO
	 * @param date 
	 * @param companyDto 
	 * @throws Exception 
	 */
	private void makeCashKindBook(List<CashKindListDto> dtolist, PaymentReportVo vo, Date date, CmCompanyDto companyDto)
			throws Exception {
		int printDataRow = 7;
		int[] cashKind;
		int totalAmount = 0;
		int totalCnt = 0;
		int totalCollect = 0;
		int totalCollectAmount = 0;
		int totalMan = 0;
		int total5Sen = 0;
		int totalSen = 0;
		int total5Hnd = 0;
		int totalHnd = 0;
		int total5Jyu = 0;
		int totalJyu = 0;
		int total5En = 0;
		int total1En = 0;
		
		//帳票作成準備
		PoiUtility poi = new PoiUtility();
		poi.crtNewDocR(0, cfg.getProperty(MospConst.PPT_APP_DOCBASE) + PayrollConst.TEMPLATE_CASH_KIND_LIST);
		//フォント設定の為の初期化処理
		poi.createCellStyleAndFont();
		setStyle(poi, true, false);
		//ヘッダー情報設定
		poi.prtItem(1, 0, jpnYM(date, vo) + PayrollConst.NAM_PAYMENT_TARGET);
		poi.prtItem(3, 1, companyDto.getCompanyName());
		Date sysDate = getSystemDate();
		poi.prtItem(3, 22, jpnYMD(sysDate, vo));
		for (int i = 0; i < dtolist.size(); i++) {
			
			CashKindListDto dto = dtolist.get(i);
			//氏名・社員番号の印字
			poi.prtItem(printDataRow, 1, dto.getKCode());
			setStyle(poi, false, true);
			poi.prtItem(printDataRow, 3, dto.getKName());
			setStyle(poi, false, true);
			if (dto.getCashAmount() > 0) {
				//支払い金額がプラス
				totalCnt++;
			} else {
				//支払い金額がマイナス
				totalCollect++;
			}
			printDataRow++;
			
		}
		if (dtolist.size() != 0) {
			
			printDataRow++;
			poi.prtItem(printDataRow, 3, PayrollConst.BRACKET_LEFT + PayrollConst.NAM_COMMON_SUM + "  " + totalCnt
					+ PayrollConst.NAM_COMMON_NAME + PayrollConst.BRACKET_RIGHT);
			setStyle(poi, false, true);
			if (totalCollect != 0) {
				printDataRow++;
				poi.prtItem(printDataRow, 3, PayrollConst.BRACKET_LEFT + PayrollConst.NMA_COMMON_COLLECT + "  "
						+ totalCollect + PayrollConst.NAM_COMMON_NAME + PayrollConst.BRACKET_RIGHT);
				setStyle(poi, false, true);
			}
			
		}
		
		setStyle(poi, true, false);
		printDataRow = 7;
		for (int i = 0; i < dtolist.size(); i++) {
			CashKindListDto dto = dtolist.get(i);
			//金種判定
			if (dto.getCashAmount() > 0) {
				cashKind = cashKind(dto.getCashAmount());
				
				//金種枚数設定(一万円)
				poi.prtItem(printDataRow, 4, cashKind[0]);
				setStyle(poi, false, false);
				//金種枚数合計値(一万円)
				totalMan = totalMan + cashKind[0];
				
				//金種枚数設定(五千円)
				poi.prtItem(printDataRow, 6, cashKind[1]);
				setStyle(poi, false, false);
				//金種枚数合計値(五千円)
				total5Sen = total5Sen + cashKind[1];
				
				//金種枚数設定(千円)
				poi.prtItem(printDataRow, 8, cashKind[2]);
				setStyle(poi, false, false);
				//金種枚数合計値(千円)
				totalSen = totalSen + cashKind[2];
				
				//金種枚数設定(五百円)
				poi.prtItem(printDataRow, 10, cashKind[3]);
				setStyle(poi, false, false);
				//金種枚数合計値(五百円)
				total5Hnd = total5Hnd + cashKind[3];
				
				//金種枚数設定(百円)
				poi.prtItem(printDataRow, 12, cashKind[4]);
				setStyle(poi, false, false);
				//金種枚数合計値(百円)
				totalHnd = totalHnd + cashKind[4];
				
				//金種枚数設定(五十円)
				poi.prtItem(printDataRow, 14, cashKind[5]);
				setStyle(poi, false, false);
				//金種枚数合計値(五十円)
				total5Jyu = total5Jyu + cashKind[5];
				
				//金種枚数設定(十円)
				poi.prtItem(printDataRow, 16, cashKind[6]);
				setStyle(poi, false, false);
				//金種枚数合計値(十円)
				totalJyu = totalJyu + cashKind[6];
				
				//金種枚数設定(五円)
				poi.prtItem(printDataRow, 18, cashKind[7]);
				setStyle(poi, false, false);
				//金種枚数合計値(五円)
				total5En = total5En + cashKind[7];
				
				//金種枚数設定(一円)
				poi.prtItem(printDataRow, 20, cashKind[8]);
				setStyle(poi, false, false);
				//金種枚数合計値(一円)
				total1En = total1En + cashKind[8];
				//金額合計加算
				totalAmount = totalAmount + dto.getCashAmount();
			} else {
				//金額がマイナスの合算処理
				totalCollectAmount = totalCollectAmount + dto.getCashAmount();
			}
			//金種枚数設定(金額)
			poi.prtItem(printDataRow, 22, insertComma(String.valueOf(dto.getCashAmount())));
			setStyle(poi, false, false);
			printDataRow++;
			
		}
		if (dtolist.size() != 0) {
			printDataRow++;
			poi.prtBorderTop(printDataRow, 4);
			poi.prtBorderTop(printDataRow, 5);
			poi.prtBorderTop(printDataRow, 6);
			poi.prtBorderTop(printDataRow, 7);
			poi.prtBorderTop(printDataRow, 8);
			poi.prtBorderTop(printDataRow, 9);
			poi.prtBorderTop(printDataRow, 10);
			poi.prtBorderTop(printDataRow, 11);
			poi.prtBorderTop(printDataRow, 12);
			poi.prtBorderTop(printDataRow, 13);
			poi.prtBorderTop(printDataRow, 14);
			poi.prtBorderTop(printDataRow, 15);
			poi.prtBorderTop(printDataRow, 16);
			poi.prtBorderTop(printDataRow, 17);
			poi.prtBorderTop(printDataRow, 18);
			poi.prtBorderTop(printDataRow, 19);
			poi.prtBorderTop(printDataRow, 20);
			poi.prtBorderTop(printDataRow, 21);
			poi.prtBorderTop(printDataRow, 22);
			poi.prtBorderTop(printDataRow, 23);
			
			//金種合計枚数(一万円)
			poi.prtItem(printDataRow, 4, totalMan);
			setStyle(poi, false, false);
			//金種合計枚数(五千円)
			poi.prtItem(printDataRow, 6, total5Sen);
			setStyle(poi, false, false);
			//金種合計枚数(千円)
			poi.prtItem(printDataRow, 8, totalSen);
			setStyle(poi, false, false);
			//金種合計枚数(五百円)
			poi.prtItem(printDataRow, 10, total5Hnd);
			setStyle(poi, false, false);
			//金種合計枚数(百円)
			poi.prtItem(printDataRow, 12, totalHnd);
			setStyle(poi, false, false);
			//金種合計枚数(五十円)
			poi.prtItem(printDataRow, 14, total5Jyu);
			setStyle(poi, false, false);
			//金種合計枚数(十円)
			poi.prtItem(printDataRow, 16, totalJyu);
			setStyle(poi, false, false);
			//金種合計枚数(五円)
			poi.prtItem(printDataRow, 18, total5En);
			setStyle(poi, false, false);
			//金種合計枚数(一円)
			poi.prtItem(printDataRow, 20, total1En);
			setStyle(poi, false, false);
			//金額合計
			poi.prtItem(printDataRow, 22, insertComma(String.valueOf(totalAmount)));
			setStyle(poi, false, false);
			if (totalCollect != 0) {
				printDataRow++;
				poi.prtBorderTop(printDataRow, 4);
				poi.prtBorderTop(printDataRow, 5);
				poi.prtBorderTop(printDataRow, 6);
				poi.prtBorderTop(printDataRow, 7);
				poi.prtBorderTop(printDataRow, 8);
				poi.prtBorderTop(printDataRow, 9);
				poi.prtBorderTop(printDataRow, 10);
				poi.prtBorderTop(printDataRow, 11);
				poi.prtBorderTop(printDataRow, 12);
				poi.prtBorderTop(printDataRow, 13);
				poi.prtBorderTop(printDataRow, 14);
				poi.prtBorderTop(printDataRow, 15);
				poi.prtBorderTop(printDataRow, 16);
				poi.prtBorderTop(printDataRow, 17);
				poi.prtBorderTop(printDataRow, 18);
				poi.prtBorderTop(printDataRow, 19);
				poi.prtBorderTop(printDataRow, 20);
				poi.prtBorderTop(printDataRow, 21);
				poi.prtBorderTop(printDataRow, 22);
				poi.prtBorderTop(printDataRow, 23);
				//徴収金額合計(マイナスをプラスに変換する)
				totalCollectAmount = totalCollectAmount * -1;
				poi.prtItem(printDataRow, 22, insertComma(String.valueOf(totalCollectAmount)));
				setStyle(poi, false, false);
			}
		}
		
		//XLS
		String fileName = vo.getPltOutYear() + PayrollConst.FILE_NAME_SEPARATOR + vo.getPltOutMonth()
				+ PayrollConst.FILE_NAME_SEPARATOR + PayrollConst.FILE_CASH_KIND_LIST;
		request.setAttribute(MospConst.ATT_FILE_NAME, fileName);
		request.setAttribute(MospConst.ATT_FILE_OBJECT, poi.getWorkbook());
		
	}
	
	/**
	 * 現金支給金種一覧表作成(給与)ODS
	 * @param dtolist 
	 * @param vo 対象VO
	 * @param date 
	 * @param companyDto 
	 * @throws Exception 
	 */
	private void makeSpreadSheet(List<CashKindListDto> dtolist, PaymentReportVo vo, Date date, CmCompanyDto companyDto)
			throws Exception {
		int printDataRow = 7;
		int totalCnt = 0;
		int totalCollect = 0;
		int[] cashKind;
		int totalAmount = 0;
		int totalCollectAmount = 0;
		int totalMan = 0;
		int total5Sen = 0;
		int totalSen = 0;
		int total5Hnd = 0;
		int totalHnd = 0;
		int total5Jyu = 0;
		int totalJyu = 0;
		int total5En = 0;
		int total1En = 0;
		
		//テンプレートファイル
		StringBuffer sb = new StringBuffer();
		sb.append(cfg.getProperty(MospConst.PPT_APP_DOCBASE));
		sb.append(PayrollConst.TEMPLATE_CASH_KIND_LIST_ODS);
		//帳票作成
		SpreadSheet ss = SpreadSheet.createFromFile(new File(sb.toString()));
		//シート
		Sheet sheet = ss.getSheet(0);
		//ヘッダー情報設定
		sheet.getCellAt(0, 1).setValue(jpnYM(date, vo) + PayrollConst.NAM_PAYMENT_TARGET);
		sheet.getCellAt(1, 3).setValue(companyDto.getCompanyName());
		Date sysDate = getSystemDate();
		sheet.getCellAt(22, 3).setValue(jpnYMD(sysDate, vo));
		//明細データの設定
		sheet.insertDuplicatedRows(sheet.getRowCount() - 1, 6000);
		for (int i = 0; i < dtolist.size(); i++) {
			CashKindListDto dto = dtolist.get(i);
			//氏名・社員番号の印字
			sheet.getCellAt(1, printDataRow).setValue(dto.getKCode());
			sheet.getCellAt(3, printDataRow).setValue(dto.getKName());
			if (dto.getCashAmount() > 0) {
				//支払い金額がプラス
				totalCnt++;
			} else {
				//支払い金額がマイナス
				totalCollect++;
			}
			printDataRow++;
			
		}
		if (dtolist.size() != 0) {
			//最後のデータを印字
			printDataRow = printDataRow + 2;
			sheet.getCellAt(3, printDataRow).setValue(
					PayrollConst.BRACKET_LEFT + PayrollConst.NAM_COMMON_SUM + "  " + totalCnt
							+ PayrollConst.NAM_COMMON_NAME + PayrollConst.BRACKET_RIGHT);
			if (totalCollect != 0) {
				printDataRow = printDataRow + 2;
				sheet.getCellAt(3, printDataRow).setValue(
						PayrollConst.BRACKET_LEFT + PayrollConst.NMA_COMMON_COLLECT + "  " + totalCollect
								+ PayrollConst.NAM_COMMON_NAME + PayrollConst.BRACKET_RIGHT);
			}
			
		}
		
		printDataRow = 7;
		for (int i = 0; i < dtolist.size(); i++) {
			CashKindListDto dto = dtolist.get(i);
			//金種判定
			if (dto.getCashAmount() > 0) {
				cashKind = cashKind(dto.getCashAmount());
				//金種枚数設定(一万円)
				sheet.getCellAt(4, printDataRow).setValue(cashKind[0]);
				//金種枚数合計値(一万円)
				totalMan = totalMan + cashKind[0];
				
				//金種枚数設定(五千円)
				sheet.getCellAt(6, printDataRow).setValue(cashKind[1]);
				//金種枚数合計値(五千円)
				total5Sen = total5Sen + cashKind[1];
				
				//金種枚数設定(千円)
				sheet.getCellAt(8, printDataRow).setValue(cashKind[2]);
				//金種枚数合計値(千円)
				totalSen = totalSen + cashKind[2];
				
				//金種枚数設定(五百円)
				sheet.getCellAt(10, printDataRow).setValue(cashKind[3]);
				//金種枚数合計値(五百円)
				total5Hnd = total5Hnd + cashKind[3];
				
				//金種枚数設定(百円)
				sheet.getCellAt(12, printDataRow).setValue(cashKind[4]);
				//金種枚数合計値(百円)
				totalHnd = totalHnd + cashKind[4];
				
				//金種枚数設定(五十円)
				sheet.getCellAt(14, printDataRow).setValue(cashKind[5]);
				//金種枚数合計値(五十円)
				total5Jyu = total5Jyu + cashKind[5];
				
				//金種枚数設定(十円)
				sheet.getCellAt(16, printDataRow).setValue(cashKind[6]);
				//金種枚数合計値(十円)
				totalJyu = totalJyu + cashKind[6];
				
				//金種枚数設定(五円)
				sheet.getCellAt(18, printDataRow).setValue(cashKind[7]);
				//金種枚数合計値(五円)
				total5En = total5En + cashKind[7];
				
				//金種枚数設定(一円)
				sheet.getCellAt(20, printDataRow).setValue(cashKind[8]);
				//金種枚数合計値(一円)
				total1En = total1En + cashKind[8];
				//金額合計加算
				totalAmount = totalAmount + dto.getCashAmount();
			} else {
				//金額がマイナスの合算処理
				totalCollectAmount = totalCollectAmount + dto.getCashAmount();
			}
			//金種枚数設定(金額)
			sheet.getCellAt(22, printDataRow).setValue(insertComma(String.valueOf(dto.getCashAmount())));
			printDataRow++;
			
		}
		
		if (dtolist.size() != 0) {
			printDataRow++;
			String styleCodeBorderBotom = sheet.getCellAt(0, 3).getStyleName();
			sheet.getCellAt(4, printDataRow).setStyleName(styleCodeBorderBotom);
			sheet.getCellAt(5, printDataRow).setStyleName(styleCodeBorderBotom);
			sheet.getCellAt(6, printDataRow).setStyleName(styleCodeBorderBotom);
			sheet.getCellAt(7, printDataRow).setStyleName(styleCodeBorderBotom);
			sheet.getCellAt(8, printDataRow).setStyleName(styleCodeBorderBotom);
			sheet.getCellAt(9, printDataRow).setStyleName(styleCodeBorderBotom);
			sheet.getCellAt(10, printDataRow).setStyleName(styleCodeBorderBotom);
			sheet.getCellAt(11, printDataRow).setStyleName(styleCodeBorderBotom);
			sheet.getCellAt(12, printDataRow).setStyleName(styleCodeBorderBotom);
			sheet.getCellAt(13, printDataRow).setStyleName(styleCodeBorderBotom);
			sheet.getCellAt(14, printDataRow).setStyleName(styleCodeBorderBotom);
			sheet.getCellAt(15, printDataRow).setStyleName(styleCodeBorderBotom);
			sheet.getCellAt(16, printDataRow).setStyleName(styleCodeBorderBotom);
			sheet.getCellAt(17, printDataRow).setStyleName(styleCodeBorderBotom);
			sheet.getCellAt(18, printDataRow).setStyleName(styleCodeBorderBotom);
			sheet.getCellAt(19, printDataRow).setStyleName(styleCodeBorderBotom);
			sheet.getCellAt(20, printDataRow).setStyleName(styleCodeBorderBotom);
			sheet.getCellAt(21, printDataRow).setStyleName(styleCodeBorderBotom);
			sheet.getCellAt(22, printDataRow).setStyleName(styleCodeBorderBotom);
			sheet.getCellAt(23, printDataRow).setStyleName(styleCodeBorderBotom);
			printDataRow++;
			//金種合計枚数(一万円)
			sheet.getCellAt(4, printDataRow).setValue(totalMan);
			//金種合計枚数(五千円)
			sheet.getCellAt(6, printDataRow).setValue(total5Sen);
			//金種合計枚数(千円)
			sheet.getCellAt(8, printDataRow).setValue(totalSen);
			//金種合計枚数(五百円)
			sheet.getCellAt(10, printDataRow).setValue(total5Hnd);
			//金種合計枚数(百円)
			sheet.getCellAt(12, printDataRow).setValue(totalHnd);
			//金種合計枚数(五十円)
			sheet.getCellAt(14, printDataRow).setValue(total5Jyu);
			//金種合計枚数(十円)
			sheet.getCellAt(16, printDataRow).setValue(totalJyu);
			//金種合計枚数(五円)
			sheet.getCellAt(18, printDataRow).setValue(total5En);
			//金種合計枚数(一円)
			sheet.getCellAt(20, printDataRow).setValue(total1En);
			//金額合計
			sheet.getCellAt(22, printDataRow).setValue(insertComma(String.valueOf(totalAmount)));
			if (totalCollect != 0) {
				printDataRow++;
				sheet.getCellAt(4, printDataRow).setStyleName(styleCodeBorderBotom);
				sheet.getCellAt(5, printDataRow).setStyleName(styleCodeBorderBotom);
				sheet.getCellAt(6, printDataRow).setStyleName(styleCodeBorderBotom);
				sheet.getCellAt(7, printDataRow).setStyleName(styleCodeBorderBotom);
				sheet.getCellAt(8, printDataRow).setStyleName(styleCodeBorderBotom);
				sheet.getCellAt(9, printDataRow).setStyleName(styleCodeBorderBotom);
				sheet.getCellAt(10, printDataRow).setStyleName(styleCodeBorderBotom);
				sheet.getCellAt(11, printDataRow).setStyleName(styleCodeBorderBotom);
				sheet.getCellAt(12, printDataRow).setStyleName(styleCodeBorderBotom);
				sheet.getCellAt(13, printDataRow).setStyleName(styleCodeBorderBotom);
				sheet.getCellAt(14, printDataRow).setStyleName(styleCodeBorderBotom);
				sheet.getCellAt(15, printDataRow).setStyleName(styleCodeBorderBotom);
				sheet.getCellAt(16, printDataRow).setStyleName(styleCodeBorderBotom);
				sheet.getCellAt(17, printDataRow).setStyleName(styleCodeBorderBotom);
				sheet.getCellAt(18, printDataRow).setStyleName(styleCodeBorderBotom);
				sheet.getCellAt(19, printDataRow).setStyleName(styleCodeBorderBotom);
				sheet.getCellAt(20, printDataRow).setStyleName(styleCodeBorderBotom);
				sheet.getCellAt(21, printDataRow).setStyleName(styleCodeBorderBotom);
				sheet.getCellAt(22, printDataRow).setStyleName(styleCodeBorderBotom);
				sheet.getCellAt(23, printDataRow).setStyleName(styleCodeBorderBotom);
				//徴収金額合計(マイナスをプラスに変換する)
				totalCollectAmount = totalCollectAmount * -1;
				printDataRow++;
				sheet.getCellAt(22, printDataRow).setValue(insertComma(String.valueOf(totalCollectAmount)));
			}
		}
		String fileName = vo.getPltOutYear() + PayrollConst.FILE_NAME_SEPARATOR + vo.getPltOutMonth()
				+ PayrollConst.FILE_NAME_SEPARATOR + PayrollConst.FILE_CASH_KIND_LIST_ODS;
		request.setAttribute(MospConst.ATT_FILE_NAME, fileName);
		request.setAttribute(MospConst.ATT_FILE_OBJECT, ss);
	}
	
	/**
	 * 現金支給金種判定
	 * @param cashAmount 現金金額
	 * @return cashKind 金種判定結果 
	 */
	private int[] cashKind(int cashAmount) {
		int[] cashKind = new int[9];
		int remainder = 0;
		remainder = cashAmount;
		//万円の位を計算
		cashKind[0] = remainder / 10000;
		remainder = remainder % 10000;
		//五千円の位を計算
		cashKind[1] = remainder / 5000;
		remainder = remainder % 5000;
		//千円の位を計算
		cashKind[2] = remainder / 1000;
		remainder = remainder % 1000;
		//五百円の位を計算
		cashKind[3] = remainder / 500;
		remainder = remainder % 500;
		//百円の位を計算
		cashKind[4] = remainder / 100;
		remainder = remainder % 100;
		//五十円の位を計算
		cashKind[5] = remainder / 50;
		remainder = remainder % 50;
		//十円の位を計算
		cashKind[6] = remainder / 10;
		remainder = remainder % 10;
		//五円の位を計算
		cashKind[7] = remainder / 5;
		remainder = remainder % 5;
		//一円の位を計算
		cashKind[8] = remainder / 1;
		remainder = remainder % 1;
		
		return cashKind;
	}
	
	/**
	 * セルフォント・スタイル設定 
	 * @param poi PoiUtility
	 * @param styleFlg スタイル初期化フラグ
	 * @param halignFlg 左右揃え判定フラグ
	 */
	private void setStyle(PoiUtility poi, boolean styleFlg, boolean halignFlg) {
		int fontSize = 13;
		//フォント設定の為の初期化処理
		if (styleFlg == true) {
			poi.createCellStyle();
		} else {
			poi.setFontType(PoiUtility.MS_MINTYO);
			poi.setFontSize(fontSize);
			if (halignFlg == true) {
				poi.setFontHAlignLeft();
			} else {
				poi.setFontHAlignRight();
			}
			poi.reflectCellStyle();
		}
		
	}
}
