/*
 * MosP - Mind Open Source Project         http://www.mosp.jp/
 * Copyright (C) 1987-2009 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.dao;

import java.lang.reflect.Field;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import jp.mosp.common.common.BaseDao;
import jp.mosp.common.common.MospException;
import jp.mosp.kintai.dto.MScheduleDto;

public class MScheduleDao extends BaseDao {

	// テーブル名及びキー列名
	public static final String TABLE	= "M_SCHEDULE";
	public static final String KEY_1	= "KCODE";				//社員コード
	public static final String KEY_2	= "HIZUKE";				//日付

	// 列名
	public static final String COL_KCODE  			= "KCODE";		//社員コード
	public static final String COL_HIZUKE  		= "HIZUKE";		//日付
	public static final String COL_NEN  			= "NEN";		//年
	public static final String COL_TUKI  			= "TUKI";		//月
	public static final String COL_HI  			= "HI";			//日
	public static final String COL_SYOZOKU  		= "SYOZOKU";	//所属コード
	public static final String COL_NINSYO  		= "NINSYO";		//認証者
	public static final String COL_SYJIKOKU  		= "SYJIKOKU";	//出勤時刻
	public static final String COL_KINMU  			= "KINMU";		//勤務形態
	public static final String COL_SHANKYUKB  		= "SHANKYUKB";	//半休区分
	public static final String COL_KYUSYUKB  		= "KYUSYUKB";	//休出区分
	public static final String COL_TIRIYUU  		= "TIRIYUU";	//遅刻理由
	public static final String COL_TICOMENT  		= "TICOMENT";	//遅刻コメント
	public static final String COL_TISYOUMEI  		= "TISYOUMEI";	//遅刻証明書
	public static final String COL_TIJIKAN  		= "TIJIKAN";	//遅刻時間
	public static final String COL_SYTIMES  		= "SYTIMES";	//出勤入力日時
	public static final String COL_TAJIKOKU 		= "TAJIKOKU";	//退社時刻
	public static final String COL_KINMUJI  		= "KINMUJI";	//勤務時間
	public static final String COL_KYUKEIJI  		= "KYUKEIJI";	//休憩時間
	public static final String COL_THANKYUKB  		= "THANKYUKB";	//半休区分
	public static final String COL_SORIYUU  		= "SORIYUU";	//早退理由
	public static final String COL_SOCOMENT  		= "SOCOMENT";	//早退コメント
	public static final String COL_SOSYOUMEI  		= "SOSYOUMEI";	//早退証明書
	public static final String COL_SOJIKAN  		= "SOJIKAN";	//早退時間
	public static final String COL_ZAJIKAN  		= "ZAJIKAN";	//残業時間
	public static final String COL_KSJIKAN  		= "KSJIKAN";	//休出時間
	public static final String COL_SNJIKAN  		= "SNJIKAN";	//深夜時間
	public static final String COL_SYOKUJI  		= "SYOKUJI";	//食事手当支給
	public static final String COL_CYOKU1  		= "CYOKU1";		//1直手当支給
	public static final String COL_CYOKU2  		= "CYOKU2";		//2直手当支給
	public static final String COL_CYOKU3  		= "CYOKU3";		//3直手当支給
	public static final String COL_HOKA1  			= "HOKA1";		//その他支給1
	public static final String COL_HOKA2  			= "HOKA2";		//その他支給2
	public static final String COL_HOKA3  			= "HOKA3";		//その他支給3
	public static final String COL_HOKA4  			= "HOKA4";		//その他支給4
	public static final String COL_HOKA5  			= "HOKA5";		//その他支給5
	public static final String COL_TATIMES  		= "TATIMES";	//退社入力日時
	public static final String COL_KKRIYUU  		= "KKRIYUU";	//休暇理由
	public static final String COL_DAIKYUJYU  		= "DAIKYUJYU";	//代休充当日
	public static final String COL_KKCOMENT  		= "KKCOMENT";	//休暇コメント
	public static final String COL_KKSYOUMEI  		= "KKSYOUMEI";	//休暇証明書
	public static final String COL_KKJIKAN  		= "KKJIKAN";	//休暇時間
	public static final String COL_KKTIMES  		= "KKTIMES";	//休暇入力日時
	public static final String COL_SNRIYUU 		= "SNRIYUU";	//承認理由
	public static final String COL_SNCOMENT 		= "SNCOMENT";	//承認コメント
	public static final String COL_SNTIMES  		= "SNTIMES";	//承認入力日時
	public static final String COL_UPDATETIMES  	= "UPDTTIMES";	//更新日時
	public static final String COL_NIJIKYU  		= "NIJIKYU";	//二時間年休
	public static final String COL_WARIMASI1 		= "WARIMASI1";	//1.00割増
	public static final String COL_ZKINMU 			= "ZKINMU";		//前回勤務形態

	/**
	 * コンストラクタ
	 */
	public MScheduleDao() {
		super();
	}

	/**
	 * 検索結果取得(DTOのList)
	 * @return List 検索リスト
	 * @throws SQLException
	 */
	private List<MScheduleDto> mappingAll() throws SQLException {
		List<MScheduleDto> all = new ArrayList<MScheduleDto>();
		while (rs.next()) {
			all.add(mapping());
		}
		return all;
	}

	/**
	 * 検索結果取得(DTO)
	 * @return m_syukkinyoteiboDto m_syukkinyoteiboレコード
	 * @throws SQLException
	 */
	private MScheduleDto mapping() throws SQLException {
		MScheduleDto dto = new MScheduleDto();
		dto.setKCode 		(rs.getString (COL_KCODE));			//社員コード
		dto.setHizuke 		(rs.getString (COL_HIZUKE));		//日付
		dto.setNen 			(rs.getInt    (COL_NEN));			//年
		dto.setTuki 		(rs.getInt    (COL_TUKI));			//月
		dto.setHi 			(rs.getInt    (COL_HI));			//日
		dto.setSyozoku 		(rs.getString (COL_SYOZOKU));		//所属コード
		dto.setNinsyo 		(rs.getString (COL_NINSYO));		//認証者
		dto.setSyJikoku 	(rs.getString (COL_SYJIKOKU));		//出勤時刻
		dto.setKinmu 		(rs.getString (COL_KINMU));			//勤務形態
		dto.setSHankyuKb 	(rs.getString (COL_SHANKYUKB));		//半休区分
		dto.setKyusyuKb 	(rs.getString (COL_KYUSYUKB));		//休出区分
		dto.setTiRiyuu 		(rs.getString (COL_TIRIYUU));		//遅刻理由
		dto.setTiComent 	(rs.getString (COL_TICOMENT));		//遅刻コメント
		dto.setTiSyoumei 	(rs.getString (COL_TISYOUMEI));		//遅刻証明書
		dto.setTiJikan 		(rs.getInt    (COL_TIJIKAN));		//遅刻時間
		dto.setSyTimes 		(rs.getString (COL_SYTIMES));		//出勤入力日時
		dto.setTaJikoku 	(rs.getString (COL_TAJIKOKU));		//退社時刻
		dto.setKinmuJi 		(rs.getInt    (COL_KINMUJI));		//勤務時間
		dto.setKyukeiJi 	(rs.getInt    (COL_KYUKEIJI));		//休憩時間
		dto.setTHankyuKb 	(rs.getString (COL_THANKYUKB));		//半休区分
		dto.setSoRiyuu 		(rs.getString (COL_SORIYUU));		//早退理由
		dto.setSoComent 	(rs.getString (COL_SOCOMENT));		//早退コメント
		dto.setSoSyoumei 	(rs.getString (COL_SOSYOUMEI));		//早退証明書
		dto.setSoJikan 		(rs.getInt    (COL_SOJIKAN));		//早退時間
		dto.setZaJikan 		(rs.getInt    (COL_ZAJIKAN));		//残業時間
		dto.setKsJikan 		(rs.getInt    (COL_KSJIKAN));		//休出時間
		dto.setSnJikan 		(rs.getInt    (COL_SNJIKAN));		//深夜時間
		dto.setSyokuji 		(rs.getInt    (COL_SYOKUJI));		//食事手当支給
		dto.setCyoku1 		(rs.getInt    (COL_CYOKU1));		//1直手当支給
		dto.setCyoku2 		(rs.getInt    (COL_CYOKU2));		//2直手当支給
		dto.setCyoku3 		(rs.getInt    (COL_CYOKU3));		//3直手当支給
		dto.setHoka1 		(rs.getInt    (COL_HOKA1));			//その他支給1
		dto.setHoka2 		(rs.getInt    (COL_HOKA2));			//その他支給2
		dto.setHoka3 		(rs.getInt    (COL_HOKA3));			//その他支給3
		dto.setHoka4 		(rs.getInt    (COL_HOKA4));			//その他支給4
		dto.setHoka5 		(rs.getInt    (COL_HOKA5));			//その他支給5
		dto.setTaTimes 		(rs.getString (COL_TATIMES));		//退社入力日時
		dto.setKKRiyuu 		(rs.getString (COL_KKRIYUU));		//休暇理由
		dto.setDaiKyuJyu 	(rs.getString (COL_DAIKYUJYU));		//代休充当日
		dto.setKKComent 	(rs.getString (COL_KKCOMENT));		//休暇コメント
		dto.setKKSyoumei 	(rs.getString (COL_KKSYOUMEI));		//休暇証明書
		dto.setKKJikan 		(rs.getInt    (COL_KKJIKAN));		//休暇時間
		dto.setKKTimes 		(rs.getString (COL_KKTIMES));		//休暇入力日時
		dto.setSnRiyuu 		(rs.getString (COL_SNRIYUU));		//承認理由
		dto.setSnComent 	(rs.getString (COL_SNCOMENT));		//承認コメント
		dto.setSnTimes 		(rs.getString (COL_SNTIMES));		//承認入力日時
		dto.setUpdtTimes 	(rs.getString (COL_UPDATETIMES));	//更新日時
		dto.setNijiKyu		(rs.getInt	  (COL_NIJIKYU));		//二時間年休
		dto.setWarimasi1	(rs.getInt	  (COL_WARIMASI1));		//1.00割増
		dto.setZKinmu		(rs.getString (COL_ZKINMU));		//前回勤務形態
		return dto;
	}

	/**
	 * 全データ取得
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public List findAll() throws SQLException, IllegalAccessException, NoSuchFieldException {
		try {
			prepareStatement(getSelectQuery(getClass()) + getOrderForKey(getClass()));
			executeQuery();
			List all = mappingAll();
			return all;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}

	/**
	 * キーによるデータ取得
	 * @param kcode 社員コード(検索キー)
	 * @param hizuke 日付(検索キー)
	 * @return m_syukkinyoteiboDto 検索結果(DTO)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public MScheduleDto findForKey(
			String kcode,
			String hizuke
	) throws SQLException, IllegalAccessException, NoSuchFieldException {
		try {
			index = 1;
			prepareStatement(getSelectQuery(getClass()) + getConditionForKey(getClass()));
			setParam(index++, kcode);
			setParam(index++, hizuke);
			executeQuery();
			MScheduleDto dto = null;
			if (rs.next()) {
				dto = mapping();
				mappingCommonInfo(dto);
			}
			return dto;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}

	/**
	 * キーによる行ロック取得
	 * @param kcode 社員コード(検索キー)
	 * @param hizuke 日付(検索キー)
	 * @return m_syukkinyoteiboDto 検索結果(DTO)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public MScheduleDto findForUpdate(
			String kcode,
			String hizuke
	) throws SQLException, IllegalAccessException, NoSuchFieldException {
		try {
			index = 1;
			prepareStatement(getSelectQuery(getClass()) + getConditionForKey(getClass()) + getForUpdate());
			setParam(index++, kcode);
			setParam(index++, hizuke);
			executeQuery();
			MScheduleDto dto = null;
			if (rs.next()) {
				dto = mapping();
				mappingCommonInfo(dto);
			}
			return dto;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}

	/**
	 * 条件文によるデータ取得
	 * @param kcode 検索社員コード
	 * @param hizuke 検索日付
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public List<MScheduleDto> findForCondition(
			String kcode,
			String hizuke
	) throws SQLException, IllegalAccessException, NoSuchFieldException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append("WHERE ");
		sb.append(COL_KCODE);
		sb.append(" = ? AND ");
		sb.append(COL_HIZUKE);
		sb.append(" = ? ");
		try {
			prepareStatement(getSelectQuery(getClass()) + sb.toString() + getOrderForKey(getClass()));
			setParam(index++,   kcode  );
			setParam(index++,   hizuke   + "%");
			executeQuery();
			List<MScheduleDto> list = mappingAll();
			return list;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}

	/**
	 * 条件文によるデータ取得
	 * @param kcode 検索社員コード
	 * @param startDate 検索開始日付
	 * @param endDate 検索終了日付
	 * @return list 検索結果(DTOのList)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public List<MScheduleDto> findForScheduleList(
			String kcode,
			String startDate,
			String endDate
	) throws SQLException, IllegalAccessException, NoSuchFieldException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append("WHERE ");
		sb.append(COL_KCODE);
		sb.append(" = ? AND ");
		sb.append(COL_HIZUKE);
		sb.append(" >= ? AND ");
		sb.append(COL_HIZUKE);
		sb.append(" <= ? ");
		try {
			prepareStatement(getSelectQuery(getClass()) + sb.toString() + getOrderForKey(getClass()));
			setParam(index++,	kcode);
			setParam(index++,	startDate);
			setParam(index++,	endDate);
			executeQuery();
			List<MScheduleDto> list = mappingAll();
			return list;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}

	/**
	 * データ挿入
	 * @param dto 挿入対象DTO
	 * @return int 挿入結果(件数)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 * @throws MospException
	 */
	public int insert(MScheduleDto dto) throws SQLException, IllegalAccessException, NoSuchFieldException, MospException {
		try {
			index = 1;
			prepareStatement(getInsertQuery(getClass()));
			setParams(dto,true);
			executeUpdate();
			chkInsert(1);
			return cnt;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}

	/**
	 * データ挿入
	 * @param 	aryDto 挿入対象DTO
	 * @return int 挿入結果(件数)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 * @throws MospException
	 */
	public int insert(MScheduleDto[] aryDto) throws SQLException, IllegalAccessException, NoSuchFieldException, MospException {
		try {
			index = 1;
			StringBuffer sb = new StringBuffer();
			sb.append("INSERT INTO ");
			sb.append(TABLE);
			sb.append("(");
			Field[] fields = getClass().getFields();
			for (int i = 0; i < fields.length; i++) {
				Field field = fields[i];
				String name = field.getName();
				if (name.indexOf("COL_") == 0) {
					sb.append(field.get(null));
					sb.append(", ");
				}
			}
			if (!INSERT_DATE.equals("")) {
				sb.append(INSERT_DATE + ", ");
			}
			if (!INSERT_USER.equals("")) {
				sb.append(INSERT_USER + ", ");
			}
			if (!UPDATE_DATE.equals("")) {
				sb.append(UPDATE_DATE + ", ");
			}
			if (!UPDATE_USER.equals("")) {
				sb.append(UPDATE_USER + ", ");
			}
			sb.delete(sb.length() - 2, sb.length() - 1);
			sb.append(") VALUES");
			for (int a = 0; a < aryDto.length; a++) {
				sb.append("(");
				for (int i = 0; i < fields.length; i++) {
					Field field = fields[i];
					String name = field.getName();
					if (name.indexOf("COL_") == 0) {
						sb.append("?, ");
					}
				}
				if (!INSERT_DATE.equals("")) {
					sb.append("?, ");
				}
				if (!INSERT_USER.equals("")) {
					sb.append("?, ");
				}
				if (!UPDATE_DATE.equals("")) {
					sb.append("?, ");
				}
				if (!UPDATE_USER.equals("")) {
					sb.append("?, ");
				}
				sb.delete(sb.length() - 2, sb.length());
				sb.append("),");
			}
			sb.delete(sb.length() - 1, sb.length());
			prepareStatement(sb.toString());
			for (int d = 0; d < aryDto.length; d++) {
				setParams(aryDto[d], true);
			}
			executeUpdate();
			chkInsert(aryDto.length);
			return cnt;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}

	/**
	 * データ更新
	 * @param dto 更新対象DTO
	 * @return int 更新結果(件数)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 * @throws MospException
	 */
	public int update(MScheduleDto dto) throws SQLException, IllegalAccessException, NoSuchFieldException, MospException {
		try {
			index = 1;
			prepareStatement(getUpdateQuery(getClass()));
			setParams(dto,false);
			setParam(index++, dto.getKCode());
			setParam(index++, dto.getHizuke());
			executeUpdate();
			chkUpdate(1);
			return cnt;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}

	/**
	 * データ削除
	 * @param dto 更新対象DTO
	 * @return int 削除結果(件数)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 * @throws MospException
	 */
	public int delete(MScheduleDto dto) throws SQLException, IllegalAccessException, NoSuchFieldException, MospException {
		try {
			index = 1;
			prepareStatement(getDeleteQuery(getClass()));
			setParam(index++, dto.getKCode());
			setParam(index++, dto.getHizuke());
			executeUpdate();
			chkDelete(1);
			return cnt;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}

	/**
	 * パラメータ設定
	 * @param dto 更新対象DTO
	 * @throws SQLException
	 */
	private void setParams(MScheduleDto dto, boolean isInsert) throws SQLException {
		setParam(index++, dto.getKCode());		//社員コード
		setParam(index++, dto.getHizuke());		//日付
		setParam(index++, dto.getNen());		//年
		setParam(index++, dto.getTuki());		//月
		setParam(index++, dto.getHi());			//日
		setParam(index++, dto.getSyozoku());	//所属コード
		setParam(index++, dto.getNinsyo());		//認証者
		setParam(index++, dto.getSyJikoku());	//出勤時刻
		setParam(index++, dto.getKinmu());		//勤務形態
		setParam(index++, dto.getSHankyuKb());	//半休区分
		setParam(index++, dto.getKyusyuKb());	//休出区分
		setParam(index++, dto.getTiRiyuu());	//遅刻理由
		setParam(index++, dto.getTiComent());	//遅刻コメント
		setParam(index++, dto.getTiSyoumei());	//遅刻証明書
		setParam(index++, dto.getTiJikan());	//遅刻時間
		setParam(index++, dto.getSyTimes());	//出勤入力日時
		setParam(index++, dto.getTaJikoku());	//退社時刻
		setParam(index++, dto.getKinmuJi());	//勤務時間
		setParam(index++, dto.getKyukeiJi());	//休憩時間
		setParam(index++, dto.getTHankyuKb());	//半休区分
		setParam(index++, dto.getSoRiyuu());	//早退理由
		setParam(index++, dto.getSoComent());	//早退コメント
		setParam(index++, dto.getSoSyoumei());	//早退証明書
		setParam(index++, dto.getSoJikan());	//早退時間
		setParam(index++, dto.getZaJikan());	//残業時間
		setParam(index++, dto.getKsJikan());	//休出時間
		setParam(index++, dto.getSnJikan());	//深夜時間
		setParam(index++, dto.getSyokuji());	//食事手当支給
		setParam(index++, dto.getCyoku1());		//1直手当支給
		setParam(index++, dto.getCyoku2());		//2直手当支給
		setParam(index++, dto.getCyoku3());		//3直手当支給
		setParam(index++, dto.getHoka1());		//その他支給1
		setParam(index++, dto.getHoka2());		//その他支給2
		setParam(index++, dto.getHoka3());		//その他支給3
		setParam(index++, dto.getHoka4());		//その他支給4
		setParam(index++, dto.getHoka5());		//その他支給5
		setParam(index++, dto.getTaTimes());	//退社入力日時
		setParam(index++, dto.getKKRiyuu());	//休暇理由
		setParam(index++, dto.getDaiKyuJyu());	//代休充当日
		setParam(index++, dto.getKKComent());	//休暇コメント
		setParam(index++, dto.getKKSyoumei());	//休暇証明書
		setParam(index++, dto.getKKJikan());	//休暇時間
		setParam(index++, dto.getKKTimes());	//休暇入力日時
		setParam(index++, dto.getSnRiyuu());	//承認理由
		setParam(index++, dto.getSnComent());	//承認コメント
		setParam(index++, dto.getSnTimes());	//承認入力日時
		setParam(index++, dto.getUpdtTimes());	//更新日時
		setParam(index++, dto.getNijiKyu());	//二時間年休
		setParam(index++, dto.getWarimasi1());	//1.00割増
		setParam(index++, dto.getZKinmu());		//前回勤務形態
		setCommonParams(isInsert);
	}

}
