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));
			
		}
	}
	
}
