/*
 * 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.kintai.report.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import jp.mosp.common.common.BaseDao;
import jp.mosp.common.common.MospConst;
import jp.mosp.kintai.common.dao.MKihonDao;
import jp.mosp.kintai.payroll.dao.KdWorkDao;
import jp.mosp.kintai.payroll.dao.MKintaiDao;
import jp.mosp.kintai.report.dto.AttendanceManagementDto;

/**
 * @author yoshida
 *
 */
public class AttendanceManagementDao extends BaseDao {
	
	// 列名
	/**
	 * KCODE
	 */
	public static final String	COL_KCODE		= MKintaiDao.COL_KCODE;
	/**
	 * SIKYUYM
	 */
	public static final String	COL_SIKYUYM		= MKintaiDao.COL_SIKYUYM;
	/**
	 * SIMEI
	 */
	public static final String	COL_SIMEI		= MKihonDao.COL_SIMEI;
	/**
	 * SNISSU
	 */
	public static final String	COL_SNISSU		= MKintaiDao.COL_SNISSU;
	/**
	 * KINMUJI
	 */
	public static final String	COL_KINMUJI		= MKintaiDao.COL_KINMUJI;
	/**
	 * KYUKEIJI
	 */
	public static final String	COL_KYUKEIJI	= MKintaiDao.COL_KYUKEIJI;
	/**
	 * YUKYU
	 */
	public static final String	COL_YUKYU		= MKintaiDao.TABLE + "." + MKintaiDao.COL_YUKYU;
	/**
	 * TOKUKYU
	 */
	public static final String	COL_TOKUKYU		= MKintaiDao.COL_TOKUKYU;
	/**
	 * KEKKIN
	 */
	public static final String	COL_KEKKIN		= MKintaiDao.COL_KEKKIN;
	/**
	 * SKEKKIN
	 */
	public static final String	COL_SKEKKIN		= MKintaiDao.COL_SKEKKIN;
	/**
	 * TISOKAI
	 */
	public static final String	COL_TISOKAI		= MKintaiDao.COL_TISOKAI;
	/**
	 * TIJIKAN
	 */
	public static final String	COL_TIJIKAN		= MKintaiDao.COL_TIJIKAN;
	/**
	 * ZAJIKAN
	 */
	public static final String	COL_ZAJIKAN		= MKintaiDao.COL_ZAJIKAN;
	/**
	 * KSJIKAN
	 */
	public static final String	COL_KSJIKAN		= MKintaiDao.COL_KSJIKAN;
	/**
	 * SNJIKAN
	 */
	public static final String	COL_SNJIKAN		= MKintaiDao.COL_SNJIKAN;
	/**
	 * KAIKIN
	 */
	public static final String	COL_KAIKIN		= MKintaiDao.COL_KAIKIN;
	/**
	 * SYOKUJIKAISU
	 */
	public static final String	COL_SYOKUJI		= MKintaiDao.COL_SYOKUJIKAISU;
	/**
	 * CYOKUKAISU1
	 */
	public static final String	COL_CYOKU1		= MKintaiDao.COL_CYOKUKAISU1;
	/**
	 * CYOKUKAISU2
	 */
	public static final String	COL_CYOKU2		= MKintaiDao.COL_CYOKUKAISU2;
	/**
	 * CYOKUKAISU3
	 */
	public static final String	COL_CYOKU3		= MKintaiDao.COL_CYOKUKAISU3;
	/**
	 * HOKAKAISU1
	 */
	public static final String	COL_HOKA1		= MKintaiDao.COL_HOKAKAISU1;
	/**
	 * HOKAKAISU2
	 */
	public static final String	COL_HOKA2		= MKintaiDao.COL_HOKAKAISU2;
	/**
	 * HOKAKAISU3
	 */
	public static final String	COL_HOKA3		= MKintaiDao.COL_HOKAKAISU3;
	/**
	 * HOKAKAISU4
	 */
	public static final String	COL_HOKA4		= MKintaiDao.COL_HOKAKAISU4;
	/**
	 * HOKAKAISU5
	 */
	public static final String	COL_HOKA5		= MKintaiDao.COL_HOKAKAISU5;
	
	/**
	 * 7
	 */
	public static final int		NUM_YUKYU		= 7;
	

	/**
	 * コンストラクタ
	 */
	public AttendanceManagementDao() {
		super();
	}
	
	/**
	 * 検索結果取得(DTOのList 2010年4月よりKD_WORKを使用)
	 * @param isMKintai true:M_KINTAI、false:KD_WORK
	 * @return List 検索リスト
	 * @throws SQLException SQL例外が発生した場合
	 */
	private List<AttendanceManagementDto> mappingAll(boolean isMKintai) throws SQLException {
		List<AttendanceManagementDto> all = new ArrayList<AttendanceManagementDto>();
		while (rs.next()) {
			if (isMKintai) {
				all.add(mapping());
			} else {
				all.add(mappingKdWork());
			}
		}
		return all;
	}
	
	/**
	 * 検索結果取得(M_KINTAI)
	 * @return M_KINTAIレコード
	 * @throws SQLException SQL例外が発生した場合
	 */
	private AttendanceManagementDto mapping() throws SQLException {
		AttendanceManagementDto dto = new AttendanceManagementDto();
		dto.setKCode(rs.getString(COL_KCODE));
		dto.setSikyuYM(rs.getString(COL_SIKYUYM));
		dto.setSimei(rs.getString(COL_SIMEI));
		dto.setSNissu(rs.getString(COL_SNISSU));
		dto.setKinmuJi(rs.getString(COL_KINMUJI));
		dto.setKyukeiJi(rs.getString(COL_KYUKEIJI));
		dto.setYukyu(rs.getString(NUM_YUKYU));
		dto.setTokuKyu(rs.getString(COL_TOKUKYU));
		dto.setKekkin(rs.getString(COL_KEKKIN));
		dto.setSKekkin(rs.getString(COL_SKEKKIN));
		dto.setTiSoKai(rs.getString(COL_TISOKAI));
		dto.setTiJikan(rs.getString(COL_TIJIKAN));
		dto.setZaJikan(rs.getString(COL_ZAJIKAN));
		dto.setKsJikan(rs.getString(COL_KSJIKAN));
		dto.setSnJikan(rs.getString(COL_SNJIKAN));
		dto.setKaikin(rs.getString(COL_KAIKIN));
		dto.setSyokujiKaisu(rs.getString(COL_SYOKUJI));
		dto.setCyokuKaisu1(rs.getString(COL_CYOKU1));
		dto.setCyokuKaisu2(rs.getString(COL_CYOKU2));
		dto.setCyokuKaisu3(rs.getString(COL_CYOKU3));
		dto.setHokaKaisu1(rs.getString(COL_HOKA1));
		dto.setHokaKaisu2(rs.getString(COL_HOKA2));
		dto.setHokaKaisu3(rs.getString(COL_HOKA3));
		dto.setHokaKaisu4(rs.getString(COL_HOKA4));
		dto.setHokaKaisu5(rs.getString(COL_HOKA5));
		return dto;
	}
	
	/**
	 * 検索結果取得(KD_WORK)
	 * @return KD_WORKレコード
	 * @throws SQLException SQL例外が発生した場合
	 */
	private AttendanceManagementDto mappingKdWork() throws SQLException {
		AttendanceManagementDto dto = new AttendanceManagementDto();
		dto.setKCode(rs.getString(KdWorkDao.COL_K_CODE));
		dto.setSikyuYM(rs.getString(KdWorkDao.COL_SIKYU_YM));
		dto.setSimei(rs.getString(COL_SIMEI));
		dto.setSNissu(rs.getString(KdWorkDao.COL_S_NISSU));
		dto.setKinmuJi(rs.getString(KdWorkDao.COL_KINMU_JI));
		dto.setKyukeiJi(rs.getString(KdWorkDao.COL_KYUKEI_JI));
		dto.setYukyu(rs.getString(NUM_YUKYU));
		dto.setTokuKyu(rs.getString(KdWorkDao.COL_TOKUKYU));
		dto.setKekkin(rs.getString(KdWorkDao.COL_KEKKIN));
		dto.setSKekkin(rs.getString(KdWorkDao.COL_S_KEKKIN));
		dto.setTiSoKai(rs.getString(KdWorkDao.COL_TI_SO_KAI));
		dto.setTiJikan(rs.getString(KdWorkDao.COL_TI_SO_JIKAN));
		dto.setZaJikan(rs.getString(KdWorkDao.COL_ZA_JIKAN));
		dto.setKsJikan(rs.getString(KdWorkDao.COL_KS_JIKAN));
		dto.setSnJikan(rs.getString(KdWorkDao.COL_SN_JIKAN));
		dto.setKaikin(rs.getString(KdWorkDao.COL_KAIKIN));
		dto.setSyokujiKaisu(rs.getString(KdWorkDao.COL_SYOKUJI_KAISU));
		dto.setCyokuKaisu1(rs.getString(KdWorkDao.COL_CYOKU_KAISU_1));
		dto.setCyokuKaisu2(rs.getString(KdWorkDao.COL_CYOKU_KAISU_2));
		dto.setCyokuKaisu3(rs.getString(KdWorkDao.COL_CYOKU_KAISU_3));
		dto.setHokaKaisu1(rs.getString(KdWorkDao.COL_HOKA_KAISU_1));
		dto.setHokaKaisu2(rs.getString(KdWorkDao.COL_HOKA_KAISU_2));
		dto.setHokaKaisu3(rs.getString(KdWorkDao.COL_HOKA_KAISU_3));
		dto.setHokaKaisu4(rs.getString(KdWorkDao.COL_HOKA_KAISU_4));
		dto.setHokaKaisu5(rs.getString(KdWorkDao.COL_HOKA_KAISU_5));
		dto.setMonthlyOverTime(rs.getString(KdWorkDao.COL_MONTHLY_OVER_TIME));
		dto.setMonthlyWeekHoliday(rs.getString(KdWorkDao.COL_MONTHLY_WEEK_HOLIDAY));
		dto.setSubstituteHoliday(rs.getString(KdWorkDao.COL_SUBSTITUTE_HOLIDAY));
		dto.setDaikyu(rs.getString(KdWorkDao.COL_DAIKYU));
		dto.setFurikae(rs.getString(KdWorkDao.COL_FURIKAE));
		dto.setRiyuu6(rs.getString(KdWorkDao.COL_KK_RIYUU_6));
		dto.setRiyuu7(rs.getString(KdWorkDao.COL_KK_RIYUU_7));
		dto.setRiyuu8(rs.getString(KdWorkDao.COL_KK_RIYUU_8));
		dto.setRiyuu9(rs.getString(KdWorkDao.COL_KK_RIYUU_9));
		return dto;
	}
	
	/**
	 * 条件文によるデータ取得
	 * @param sikyuym 選択年月
	 * @param sCode 所属コード
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException SQL例外が発生した場合
	 * @throws IllegalAccessException	メソッドにアクセスできない場合
	 * @throws NoSuchFieldException	フィールドが見つからなかった場合
	 */
	public List<AttendanceManagementDto> findForCondition(String sikyuym, String sCode) throws SQLException,
			IllegalAccessException, NoSuchFieldException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(MKintaiDao.TABLE + "." + COL_KCODE + MospConst.DATA_SEPARATOR);
		sb.append(COL_SIKYUYM + MospConst.DATA_SEPARATOR);
		sb.append(COL_SIMEI + MospConst.DATA_SEPARATOR);
		sb.append(COL_SNISSU + MospConst.DATA_SEPARATOR);
		sb.append(COL_KINMUJI + MospConst.DATA_SEPARATOR);
		sb.append(COL_KYUKEIJI + MospConst.DATA_SEPARATOR);
		sb.append(COL_YUKYU + MospConst.DATA_SEPARATOR);
		sb.append(COL_TOKUKYU + MospConst.DATA_SEPARATOR);
		sb.append(COL_KEKKIN + MospConst.DATA_SEPARATOR);
		sb.append(COL_SKEKKIN + MospConst.DATA_SEPARATOR);
		sb.append(COL_TISOKAI + MospConst.DATA_SEPARATOR);
		sb.append(COL_TIJIKAN + MospConst.DATA_SEPARATOR);
		sb.append(COL_ZAJIKAN + MospConst.DATA_SEPARATOR);
		sb.append(COL_KSJIKAN + MospConst.DATA_SEPARATOR);
		sb.append(COL_SNJIKAN + MospConst.DATA_SEPARATOR);
		sb.append(COL_KAIKIN + MospConst.DATA_SEPARATOR);
		sb.append(COL_SYOKUJI + MospConst.DATA_SEPARATOR);
		sb.append(COL_CYOKU1 + MospConst.DATA_SEPARATOR);
		sb.append(COL_CYOKU2 + MospConst.DATA_SEPARATOR);
		sb.append(COL_CYOKU3 + MospConst.DATA_SEPARATOR);
		sb.append(COL_HOKA1 + MospConst.DATA_SEPARATOR);
		sb.append(COL_HOKA2 + MospConst.DATA_SEPARATOR);
		sb.append(COL_HOKA3 + MospConst.DATA_SEPARATOR);
		sb.append(COL_HOKA4 + MospConst.DATA_SEPARATOR);
		sb.append(COL_HOKA5);
		sb.append(" FROM ");
		sb.append(MKihonDao.TABLE);
		sb.append(" LEFT JOIN ");
		sb.append(MKintaiDao.TABLE);
		sb.append(" ON ");
		sb.append(MKihonDao.TABLE + "." + MKihonDao.KEY_1);
		sb.append(" = ");
		sb.append(MKintaiDao.TABLE + "." + MKintaiDao.KEY_1);
		sb.append(" WHERE ");
		sb.append(MKintaiDao.TABLE + "." + MKintaiDao.COL_SIKYUYM);
		sb.append(" = ? ");
		if (!sCode.equals("")) {
			sb.append(" AND ");
			sb.append(MKihonDao.TABLE + "." + MKihonDao.COL_SCODE);
			sb.append(" = ? ");
		}
		sb.append(getOrderBy());
		sb.append(MKihonDao.TABLE + "." + MKihonDao.KEY_1);
		try {
			prepareStatement(sb.toString());
			setParam(index++, sikyuym);
			if (!sCode.equals("")) {
				setParam(index++, sCode);
			}
			executeQuery();
			return mappingAll(true);
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	/**
	 * 条件文によるデータ取得
	 * @param sikyuym 選択年月
	 * @param sCode 所属コード
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException SQL例外が発生した場合
	 * @throws IllegalAccessException	メソッドにアクセスできない場合
	 * @throws NoSuchFieldException	フィールドが見つからなかった場合
	 */
	public List<AttendanceManagementDto> findForConditionKdWork(String sikyuym, String sCode) throws SQLException,
			IllegalAccessException, NoSuchFieldException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_K_CODE + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_SIKYU_YM + MospConst.DATA_SEPARATOR);
		sb.append(COL_SIMEI + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_S_NISSU + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_KINMU_JI + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_KYUKEI_JI + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_YUKYU + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_TOKUKYU + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_KEKKIN + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_S_KEKKIN + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_TI_SO_KAI + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_TI_SO_JIKAN + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_ZA_JIKAN + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_KS_JIKAN + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_SN_JIKAN + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_KAIKIN + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_SYOKUJI_KAISU + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_CYOKU_KAISU_1 + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_CYOKU_KAISU_2 + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_CYOKU_KAISU_3 + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_HOKA_KAISU_1 + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_HOKA_KAISU_2 + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_HOKA_KAISU_3 + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_HOKA_KAISU_4 + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_HOKA_KAISU_5 + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_MONTHLY_OVER_TIME + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_MONTHLY_WEEK_HOLIDAY + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_SUBSTITUTE_HOLIDAY + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_DAIKYU + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_FURIKAE + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_KK_RIYUU_6 + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_KK_RIYUU_7 + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_KK_RIYUU_8 + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_KK_RIYUU_9);
		sb.append(" FROM ");
		sb.append(MKihonDao.TABLE);
		sb.append(" LEFT JOIN ");
		sb.append(KdWorkDao.TABLE);
		sb.append(" ON ");
		sb.append(MKihonDao.TABLE + "." + MKihonDao.KEY_1);
		sb.append(" = ");
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.KEY_1);
		sb.append(" WHERE ");
		sb.append(KdWorkDao.TABLE + "." + KdWorkDao.COL_SIKYU_YM);
		sb.append(" = ? ");
		if (!sCode.equals("")) {
			sb.append(" AND ");
			sb.append(MKihonDao.TABLE + "." + MKihonDao.COL_SCODE);
			sb.append(" = ? ");
		}
		sb.append(getOrderBy());
		sb.append(MKihonDao.TABLE + "." + MKihonDao.KEY_1);
		try {
			prepareStatement(sb.toString());
			setParam(index++, sikyuym);
			if (!sCode.equals("")) {
				setParam(index++, sCode);
			}
			executeQuery();
			return mappingAll(false);
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
}
