package jp.mosp.payroll.dao;

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

import jp.mosp.payroll.base.PayrollDao;
import jp.mosp.payroll.dto.ResideTransferFBDataDto;

/**
 * @author koike
 *
 */
public class ResideTransferDao extends PayrollDao {
	
	// 列名
	/**
	 * 社員コード
	 */
	public static final String	COL_K_CODE				= PdPayDeductionDao.COL_KCODE;
	
	/**
	 * 計算年月
	 */
	public static final String	COL_CALC_DATE			= PdPayDeductionDao.COL_CALC_DATE;
	
	/**
	 * 明細コード
	 */
	public static final String	COL_DETAIL_CODE			= PdPayDeductionDao.COL_DETAIL_CODE;
	
	/**
	 * 社員コード
	 */
	public static final String	COL_RES_K_CODE			= CmResidenceTaxDao.COL_K_CODE;
	
	/**
	 * 住民税合計
	 */
	public static final String	COL_SUM_RESIDENCE_TAX	= "SUM(" + "PAY." + PdPayDeductionDao.COL_AMOUNT + ")";
	
	/**
	 * 住民税件数
	 */
	public static final String	COL_COUNT_RESIDENCE_TAX	= "COUNT(" + "PAY." + PdPayDeductionDao.COL_AMOUNT + ")";
	
	/**
	 * 明細コード 住民税
	 */
	public static final String	COL_DETAIL_CODE_PD007	= "PD007";
	

	/**
	 * コンストラクタ
	 */
	public ResideTransferDao() {
		super();
		INSERT_DATE = "";
		INSERT_USER = "";
		UPDATE_DATE = "";
		UPDATE_USER = "";
	}
	
	/**
	 * 検索結果取得(DTO)
	 * @return ResideTransferFBDataDto 住民税FBレコード
	 * @throws SQLException SQL例外が発生した場合
	 */
	private ResideTransferFBDataDto mapping() throws SQLException {
		ResideTransferFBDataDto dto = new ResideTransferFBDataDto();
		// 合計件数
		dto.setTotalCount(rs.getInt(COL_COUNT_RESIDENCE_TAX));
		// 合計税額
		dto.setTotalTax(rs.getInt(COL_SUM_RESIDENCE_TAX));
		mappingCommonInfo(dto);
		return dto;
	}
	
	/**
	 * 条件文によるデータ取得
	 * @param cityCode 市町村コード
	 * @param calcDate 検索計算年月
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException SQL例外が発生した場合
	 * @throws IllegalAccessException メソッドにアクセスできない場合
	 */
	public ResideTransferFBDataDto findForFirmBanking(String cityCode, Date calcDate) throws SQLException,
			IllegalAccessException {
		index = 1;
		CmResidenceTaxDao cmResidenceTaxDao = new CmResidenceTaxDao();
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(COL_SUM_RESIDENCE_TAX);
		sb.append(", ");
		sb.append(COL_COUNT_RESIDENCE_TAX);
		sb.append(" FROM ");
		sb.append(PdPayDeductionDao.TABLE + " PAY");
		sb.append(" , ");
		sb.append(cmResidenceTaxDao.getHistoryTableForFB());
		sb.append(" RES ");
		sb.append(" WHERE ");
		sb.append("PAY." + COL_CALC_DATE);
		sb.append(" = ? ");
		sb.append(" AND ");
		sb.append("PAY." + COL_DETAIL_CODE);
		sb.append(" = ");
		sb.append("'" + COL_DETAIL_CODE_PD007 + "'");
		sb.append(" AND ");
		sb.append("PAY.");
		sb.append(COL_K_CODE);
		sb.append(" =  ");
		sb.append("RES.");
		sb.append(COL_RES_K_CODE);
		
		try {
			prepareStatement(sb.toString());
			setParam(index++, calcDate);
			setParam(index++, cityCode);
			setParam(index++, calcDate);
			executeQuery();
			ResideTransferFBDataDto dto = null;
			if (rs.next()) {
				dto = mapping();
			}
			return dto;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
}
