package jp.mosp.payroll.bonus.action;

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

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

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.part.StringPart;
import jp.mosp.payroll.base.PayrollAction;
import jp.mosp.payroll.base.PayrollConst;
import jp.mosp.payroll.bonus.BonusConst;
import jp.mosp.payroll.bonus.vo.BonusCalcVo;
import jp.mosp.payroll.bonus.dao.CashKindListDao;
import jp.mosp.payroll.bonus.dto.CashKindListDto;
import jp.mosp.payroll.bonus.dao.BankFrikomListDao;
import jp.mosp.payroll.bonus.dto.BankFrikomListDto;
import jp.mosp.payroll.dao.CmCompanyDao;
import jp.mosp.payroll.dto.CmCompanyDto;


/**
 * 給与帳票出力(銀行振込及び金種一覧)画面における処理を行う。<br><br>
 * 以下のコマンドを扱う。
 * <ul><li>
 * {@link #CMD_BANK_PERSON}
 * {@link #CMD_BANK_COMPANY}
 * {@link #CMD_CASH_KIND}
 * </li></ul>
 */
public class BounsReportOutputAction extends PayrollAction {
	/**
	 * 画面表示コマンド。<br><br>
	 * 給与計算中の年月を取得、表示する。<br>
	 * もし給与計算中の年月がなかった場合は、システム年月を表示する。<br>
	 */
	public static final String CMD_BANK_PERSON = "P9016";
	public static final String CMD_BANK_COMPANY = "P9017";
	public static final String CMD_CASH_KIND = "P9013";
	
	/**
	 * Actionの初期化を行う。<br><br>
	 * 以下の処理を行う。
	 * <ul><li>
	 * {@link PaymentReportOutputAction#PaymentReportOutputAction()}
	 * </li></ul>
	 */
	public BounsReportOutputAction() {
		super();
		setNeedProcSeq(false);
	}
	
	/* (non-Javadoc)
	 * @see jp.mosp.common.common.BaseAction#getSpecificVo()
	 */
	@Override
	protected BaseVo getSpecificVo() {
		return new BonusCalcVo();
	}
	
	/**
	 * アクション。<br>
	 * 詳細は、クラス説明を参照。<br>
	 */
	public void action() throws Exception {
		//VO取得及び設定
		prepareVo();
		if (this.cmd.equals(CMD_CASH_KIND)) {
			// 画面表示
			cashKind();
		}else if (this.cmd.equals(CMD_BANK_PERSON)) {
			//銀行振込一覧表(個人別)出力
			bankParson();
		} else if (this.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取得
		BonusCalcVo vo = (BonusCalcVo)getVo();
		//パラメータ取得
		vo.setYearParams(request);
		//対象年月取得
		Date date = vo.getDate(String.valueOf(vo.getPltYear()), String.valueOf(vo.getPltMonth()), String.valueOf(vo.getPltDay()));
		//データの取得
		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 (StringPart.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取得
		BonusCalcVo vo = (BonusCalcVo)getVo();
		//パラメータ取得
		vo.setYearParams(request);
		//対象年月取得
		Date date = vo.getDate(String.valueOf(vo.getPltYear()), String.valueOf(vo.getPltMonth()), String.valueOf(vo.getPltDay()));
			//データの取得
		//振込データ取得
		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 (StringPart.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取得
		BonusCalcVo vo = (BonusCalcVo)getVo();
		//パラメータ取得
		vo.setYearParams(request);
		//対象年月取得
		Date date = vo.getDate(String.valueOf(vo.getPltYear()), String.valueOf(vo.getPltMonth()), String.valueOf(vo.getPltDay()));
			//データの取得
		//振込データ取得
		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 (StringPart.isNotNull(cfg.getProperty(CommonConst.PPT_KINTAI_USE_ODF))) {
			//ODS
			makeCoBankSpreadSheet(list, vo, date, dto);
		} else {
			//XLS
			makeCompanyBankBook(list, vo, date, dto);
		}
		
	}
	
	/**
	 * 銀行振込一覧表作成(個人別賞与)XLS
	 * @param vo 対象VO
	 * @return PoiUtilityインスタンス
	 * @throws Exception 
	 */
	private void makeAttendanceBook(List<BankFrikomListDto> dtolist, BonusCalcVo 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) + BonusConst.TEMPLATE_BANK_FRKOM_LIST);
		//フォント設定の為の初期化処理
		poi.createCellStyleAndFont();
		setStyle(poi, true, false);
		//ヘッダー情報設定
		poi.prtItem(1, 0, jpnYM(date, vo) + BonusConst.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, BonusConst.BRACKET_LEFT + BonusConst.NMA_SUBTOTAL + "  "  + subTotalCnt + BonusConst.NMA_SHARE + BonusConst.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, BonusConst.BRACKET_LEFT + BonusConst.NAM_CO_BANK_CODE + dto.getCoBankCode() + BonusConst.BRACKET_RIGHT);
				setStyle(poi, false, true);
				
				poi.prtItem(printDataRow, 4, dto.getBankCode() + BonusConst.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(BonusConst.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() + BonusConst.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(BonusConst.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, BonusConst.BRACKET_LEFT + BonusConst.NMA_SUBTOTAL + "  "  + subTotalCnt + BonusConst.NMA_SHARE + BonusConst.BRACKET_RIGHT);
			setStyle(poi, false, true);
			printDataRow = printDataRow + 2;
			poi.prtItem(printDataRow, 3, BonusConst.BRACKET_LEFT + BonusConst.NAM_COMMON_SUM + "  "  + totalCnt + BonusConst.NMA_SHARE + BonusConst.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.getPltYear() + BonusConst.FILE_NAME_SEPARATOR + 
			vo.getPltMonth() + BonusConst.FILE_NAME_SEPARATOR + 
			vo.getPltDay() + BonusConst.FILE_NAME_SEPARATOR +
			BonusConst.FILE_BANK_FRKOM_LIST;
			request.setAttribute(MospConst.ATT_FILE_NAME, fileName);
			request.setAttribute(MospConst.ATT_FILE_OBJECT, poi.getWorkbook());
	}
	
	
	/**
	 * 銀行振込一覧表作成(個人別給与)ODS
	 * @param vo 対象VO
	 * @return PoiUtilityインスタンス
	 * @throws Exception 
	 */
	private void makeBankSpreadSheet(List<BankFrikomListDto> dtolist, BonusCalcVo 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(jpnYMD(date, vo) + BonusConst.NAM_BONUS_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.getPltYear() + PayrollConst.FILE_NAME_SEPARATOR + 
		vo.getPltMonth() + 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
	 * @return PoiUtilityインスタンス
	 * @throws Exception 
	 */
	private void makeCompanyBankBook(List<BankFrikomListDto> dtolist, BonusCalcVo 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) + BonusConst.TEMPLATE_COMPANY_BANK_FRKOM_LIST);
		//フォント設定の為の初期化処理
		poi.createCellStyleAndFont();
		setStyle(poi, true, false);
		//ヘッダー情報設定
		poi.prtItem(1, 0, jpnYM(date, vo) + BonusConst.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, BonusConst.BRACKET_LEFT + BonusConst.NMA_SUBTOTAL + "  "  + subTotalCnt + BonusConst.NMA_SHARE + BonusConst.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, BonusConst.BRACKET_LEFT + BonusConst.NAM_CO_BANK_CODE + dto.getCoBankCode() + BonusConst.BRACKET_RIGHT);
				setStyle(poi, false, true);
				
				poi.prtItem(printDataRow, 4, dto.getBankCode() + BonusConst.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(BonusConst.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, BonusConst.BRACKET_LEFT + BonusConst.NMA_SUBTOTAL + "  "  + subTotalCnt + BonusConst.NMA_SHARE + BonusConst.BRACKET_RIGHT);
			setStyle(poi, false, true);
			
			printDataRow++;
			
			//総合計の計算(口座数)
			totalCnt = totalCnt + subTotalCnt;
			
			//最後に総合計をプリントする。
			printDataRow++;
			
			poi.prtItem(printDataRow, 3, BonusConst.BRACKET_LEFT + BonusConst.NAM_COMMON_SUM + "  "  + totalCnt + BonusConst.NMA_SHARE + BonusConst.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.getPltYear() + BonusConst.FILE_NAME_SEPARATOR + 
			vo.getPltMonth() + BonusConst.FILE_NAME_SEPARATOR + 
			vo.getPltDay() + BonusConst.FILE_NAME_SEPARATOR +
			BonusConst.FILE_COMPANY_BANK_FRKOM_LIST;
			request.setAttribute(MospConst.ATT_FILE_NAME, fileName);
			request.setAttribute(MospConst.ATT_FILE_OBJECT, poi.getWorkbook());
	}
	
	/**
	 * 銀行振込一覧表作成(会社別給与)ODS
	 * @param vo 対象VO
	 * @return PoiUtilityインスタンス
	 * @throws Exception 
	 */
	private void makeCoBankSpreadSheet(List<BankFrikomListDto> dtolist, BonusCalcVo 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(jpnYMD(date, vo) + BonusConst.NAM_BONUS_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.getPltYear() + PayrollConst.FILE_NAME_SEPARATOR + 
		vo.getPltMonth() + 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 vo 対象VO
	 * @return PoiUtilityインスタンス
	 * @throws Exception 
	 */
	private void makeCashKindBook(List<CashKindListDto> dtolist, BonusCalcVo 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) + BonusConst.TEMPLATE_CASH_KIND_LIST);
		//フォント設定の為の初期化処理
		poi.createCellStyleAndFont();
		setStyle(poi, true, false);
		//ヘッダー情報設定
		poi.prtItem(1, 0, jpnYMD(date, vo) + BonusConst.NAM_BONUS_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, BonusConst.BRACKET_LEFT + BonusConst.NAM_COMMON_SUM + "  "  + totalCnt + BonusConst.NAM_COMMON_NAME + BonusConst.BRACKET_RIGHT);
			setStyle(poi, false, true);
			if (totalCollect != 0) {
				printDataRow++;
				poi.prtItem(printDataRow, 3, BonusConst.BRACKET_LEFT + BonusConst.NMA_COMMON_COLLECT + "  "  + totalCollect + BonusConst.NAM_COMMON_NAME + BonusConst.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.getPltYear() + BonusConst.FILE_NAME_SEPARATOR + 
			vo.getPltMonth() + BonusConst.FILE_NAME_SEPARATOR +
			vo.getPltDay() + BonusConst.FILE_NAME_SEPARATOR +
			BonusConst.FILE_CASH_KIND_LIST;
			request.setAttribute(MospConst.ATT_FILE_NAME, fileName);
			request.setAttribute(MospConst.ATT_FILE_OBJECT, poi.getWorkbook());
		
	}
	
	/**
	 * 現金支給金種一覧表作成(給与)ODS
	 * @param vo 対象VO
	 * @return PoiUtilityインスタンス
	 * @throws Exception 
	 */
	private void makeSpreadSheet(List<CashKindListDto> dtolist, BonusCalcVo 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(BonusConst.TEMPLATE_CASH_KIND_LIST_ODS);
		//帳票作成
		SpreadSheet ss = SpreadSheet.createFromFile(new File(sb.toString()));
		//シート
		Sheet sheet = ss.getSheet(0);
		//ヘッダー情報設定
		sheet.getCellAt(0, 1).setValue(jpnYMD(date, vo) + BonusConst.NAM_BONUS_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(BonusConst.BRACKET_LEFT + BonusConst.NAM_COMMON_SUM + "  "  + totalCnt + BonusConst.NAM_COMMON_NAME + BonusConst.BRACKET_RIGHT);
			if (totalCollect != 0) {
				printDataRow = printDataRow + 2;
				sheet.getCellAt(3, printDataRow).setValue(BonusConst.BRACKET_LEFT + BonusConst.NMA_COMMON_COLLECT + "  "  + totalCollect + BonusConst.NAM_COMMON_NAME + BonusConst.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.getPltYear() + BonusConst.FILE_NAME_SEPARATOR + 
		vo.getPltMonth() + BonusConst.FILE_NAME_SEPARATOR + 
		vo.getPltDay() + BonusConst.FILE_NAME_SEPARATOR +
		BonusConst.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();
		}
	}

}
