/*
 * MosP - Mind Open Source Project    http://www.mosp.jp/
 * Copyright (C) MIND Co., Ltd.       http://www.e-mind.co.jp/
 * 
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 */
package jp.mosp.payroll.bonus.action;

import java.io.File;
import java.sql.SQLException;
import java.util.Date;

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.MospUtility;
import jp.mosp.common.common.PoiUtility;
import jp.mosp.common.utils.StringUtil;
import jp.mosp.payroll.base.Bonus;
import jp.mosp.payroll.base.Employee;
import jp.mosp.payroll.base.PayrollAction;
import jp.mosp.payroll.base.PayrollConst;
import jp.mosp.payroll.bonus.dao.PdBonAllowanceDao;
import jp.mosp.payroll.bonus.dao.PdBonDeductionDao;
import jp.mosp.payroll.bonus.dao.PdBonSumDao;
import jp.mosp.payroll.bonus.dao.PhBounsDao;
import jp.mosp.payroll.bonus.dao.PmDetailBaDao;
import jp.mosp.payroll.bonus.dao.PmDetailBdDao;
import jp.mosp.payroll.bonus.dao.PmDetailBsDao;
import jp.mosp.payroll.bonus.dto.PhBounsDto;
import jp.mosp.payroll.bonus.vo.BonusCardVo;
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_REPORT}
 * </li></ul>
 */
public class BonusSlipOutputAction extends PayrollAction {
	
	/**
	 * 給与明細出力コマンド。<br><br>
	 * 条件により、給与明細をExcelで出力する。<br>
	 */
	public static final String	CMD_REPORT	= "P2026";
	
	// doSubmit,doSubmitFileKey判断用
	private String				kcode;
	private String				ymd;
	

	/**
	 * Actionの初期化を行う。<br><br>
	 * 以下の処理を行う。
	 * <ul><li>
	 * {@link PayrollAction#PayrollAction()}
	 * </li></ul>
	 */
	public BonusSlipOutputAction() {
		super();
		kcode = "";
		ymd = "";
		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_REPORT)) {
			// 給与明細出力
			if (StringUtil.isNotNull(cfg.getProperty(CommonConst.PPT_KINTAI_USE_ODF))) {
				report();
			} else {
				reportXls();
			}
		} else {
			throw new MospException(MospConst.EX_CMD_INVALID);
		}
	}
	
	/**
	 * 帳票出力処理を行う。<br><br>
	 * @throws Exception 
	 */
	private void report() throws Exception {
		// コネクション取得
		getConnection();
		// doSubmitFileKeyかdoSubmitの判定
		if (key1.equals("") && key2.equals("")) {
			BonusCardVo vo = new BonusCardVo();
			vo.setParams(request);
			kcode = vo.getEscHidKCode();
			ymd = vo.getEscHidTargetYear() + "/" + vo.getEscHidTargetMonth() + "/" + vo.getEscHidTargetDay();
		} else {
			kcode = key1;
			ymd = key2;
		}
		// 社員情報取得
		Employee employee = getEmployee(kcode);
		// 明細情報取得
		Bonus bonus = getBonus(kcode, MospUtility.getDate(ymd));
		// 帳票作成
		SpreadSheet ss = SpreadSheet.createFromFile(new File(cfg.getProperty(MospConst.PPT_APP_DOCBASE)
				+ "/template/samp_bonus_slip.ods"));
		Sheet sheet = ss.getSheet(0);
		// ヘッダー項目
		int row = 4;
		int col = 1;
		sheet.getCellAt(col, row).setValue(employee.getSectionCode()); // 所属コード
		sheet.getCellAt(col + 10, row).setValue(employee.getKCode()); // 社員コード
		sheet.getCellAt(col + 20, row).setValue(employee.getKName()); // 社員名
		sheet.getCellAt(col + 66, row).setValue(bonus.getPayDate()); // 支給日
		
		// 支給項目
		row = 6;
		col = 4;
		String[][] aryAllowance = bonus.getAllowanceArray(true);
		for (int i = 0; i < aryAllowance.length; i++) {
			if (i % 10 == 0 && i != 0) {
				row += 2;
				col = 4;
			} else if (i != 0) {
				col += 9;
			}
			sheet.getCellAt(col, row).setValue(aryAllowance[i][1]);
			setSheetAmount(col, row + 1, aryAllowance[i][2], sheet);
		}
		
		// 控除項目
		row = 17;
		col = 4;
		String[][] aryDeduction = bonus.getDeductionArray(true);
		for (int i = 0; i < aryDeduction.length; i++) {
			if (i % 10 == 0 && i != 0) {
				row += 2;
				col = 4;
			} else if (i != 0) {
				col += 9;
			}
			sheet.getCellAt(col, row).setValue(aryDeduction[i][1]);
			setSheetAmount(col, row + 1, aryDeduction[i][2], sheet);
		}
		// 合計項目
		row = 28;
		col = 4;
		String[][] arySum = bonus.getSumArray(true);
		for (int i = 0; i < arySum.length; i++) {
			if (i % 10 == 0 && i != 0) {
				row += 2;
				col = 4;
			} else if (i != 0) {
				col += 9;
			}
			sheet.getCellAt(col, row).setValue(arySum[i][1]);
			setSheetAmount(col, row + 1, arySum[i][2], sheet);
		}
		// 出力内容設定
		String fileName = MospUtility.getYear(bonus.getHeader().getPayDate()) + PayrollConst.FILE_NAME_SEPARATOR
				+ MospUtility.getMonth(bonus.getHeader().getPayDate()) + PayrollConst.FILE_NAME_SEPARATOR
				+ MospUtility.getDay(bonus.getHeader().getPayDate()) + PayrollConst.FILE_NAME_SEPARATOR
				+ bonus.getKCode() + PayrollConst.FILE_NAME_SEPARATOR + PayrollConst.FILE_BONUS_SLIP_OO;
		request.setAttribute(MospConst.ATT_FILE_NAME, fileName);
		request.setAttribute(MospConst.ATT_FILE_OBJECT, ss);
	}
	
	/**
	 * 帳票出力処理を行う。<br><br>
	 * @throws Exception 
	 */
	private void reportXls() throws Exception {
		// コネクション取得
		getConnection();
		// doSubmitFileKeyかdoSubmitの判定
		if (key1.equals("") && key2.equals("")) {
			BonusCardVo vo = new BonusCardVo();
			vo.setParams(request);
			kcode = vo.getEscHidKCode();
			ymd = vo.getEscHidTargetYear() + "/" + vo.getEscHidTargetMonth() + "/" + vo.getEscHidTargetDay();
		} else {
			kcode = key1;
			ymd = key2;
		}
		// 社員情報取得
		Employee employee = getEmployee(kcode);
		// 明細情報取得
		Bonus bonus = getBonus(kcode, MospUtility.getDate(ymd));
		// 帳票作成
		PoiUtility poi = new PoiUtility();
		poi.crtNewDocR(0, cfg.getProperty(MospConst.PPT_APP_DOCBASE) + "/template/samp_bonus_slip.xls");
		poi.prtItem(4, 1, employee.getSectionCode()); // 所属コード
		poi.prtItem(4, 11, employee.getKCode()); // 社員コード
		poi.prtItem(4, 21, employee.getKName()); // 社員名
		poi.prtItem(4, 67, bonus.getPayDate()); // 支給日
		
		// 支給項目
		int row = 6;
		int col = 4;
		String[][] aryAllowance = bonus.getAllowanceArray(true);
		for (int i = 0; i < aryAllowance.length; i++) {
			if (i % 10 == 0 && i != 0) {
				row += 2;
				col = 4;
			} else if (i != 0) {
				col += 9;
			}
			poi.prtItem(row, col, aryAllowance[i][1]);
			poi.prtItem(row + 1, col, Integer.parseInt(aryAllowance[i][2]));
		}
		// 控除項目
		row = 17;
		col = 4;
		String[][] aryDeduction = bonus.getDeductionArray(true);
		for (int i = 0; i < aryDeduction.length; i++) {
			if (i % 10 == 0 && i != 0) {
				row += 2;
				col = 4;
			} else if (i != 0) {
				col += 9;
			}
			poi.prtItem(row, col, aryDeduction[i][1]);
			poi.prtItem(row + 1, col, Integer.parseInt(aryDeduction[i][2]));
		}
		// 合計項目
		row = 28;
		col = 4;
		String[][] arySum = bonus.getSumArray(true);
		for (int i = 0; i < arySum.length; i++) {
			if (i % 10 == 0 && i != 0) {
				row += 2;
				col = 4;
			} else if (i != 0) {
				col += 9;
			}
			poi.prtItem(row, col, arySum[i][1]);
			poi.prtItem(row + 1, col, Integer.parseInt(arySum[i][2]));
		}
		
		// 出力内容設定
		String fileName = MospUtility.getYear(bonus.getCalcMonth()) + PayrollConst.FILE_NAME_SEPARATOR
				+ MospUtility.getMonth(bonus.getCalcMonth()) + PayrollConst.FILE_NAME_SEPARATOR + bonus.getKCode()
				+ PayrollConst.FILE_NAME_SEPARATOR + PayrollConst.FILE_BONUS_SLIP;
		request.setAttribute(MospConst.ATT_FILE_NAME, fileName);
		request.setAttribute(MospConst.ATT_FILE_OBJECT, poi.getWorkbook());
	}
	
	/**
	 * 賞与伝票インスタンスを取得、設定する。<br><br>
	 * kCode、calcDateをキーとして給与情報を取得し、{@link #bonus}に格納する。
	 * @param kCode     社員コード
	 * @param calcMonth 計算年月
	 * @return 給与伝票
	 * @throws NoSuchFieldException 
	 * @throws IllegalAccessException 
	 * @throws SQLException 
	 */
	protected Bonus getBonus(String kCode, Date payDate) throws SQLException, IllegalAccessException,
			NoSuchFieldException {
		PhBounsDto header = new PhBounsDao(cfg, cmd, aspUser, user, conn).findForKey(kCode, payDate);
		if (header == null) {
			return null;
		} else {
			return new Bonus(header,
					new PmDetailBaDao(cfg, cmd, aspUser, user, conn).findForSlip(header.getSlipCode()),
					new PmDetailBdDao(cfg, cmd, aspUser, user, conn).findForSlip(header.getSlipCode()),
					new PmDetailBsDao(cfg, cmd, aspUser, user, conn).findForSlip(header.getSlipCode()),
					new PdBonAllowanceDao(cfg, cmd, aspUser, user, conn).find(kCode, payDate), new PdBonDeductionDao(
							cfg, cmd, aspUser, user, conn).find(kCode, payDate), new PdBonSumDao(cfg, cmd, aspUser,
							user, conn).find(kCode, payDate));
			
		}
	}
	
}
