/*
 * MosP - Mind Open Source Project         http://www.mosp.jp/
 * Copyright (C) 1987-2008 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.MospConst;
import jp.mosp.common.common.MospException;

import jp.mosp.human.dao.MKihonDao;
import jp.mosp.kintai.dto.MSyukkinboDto;

public class MSyukkinboDao extends BaseDao {

	// テーブル名及びキー列名
	public static final String TABLE  = "M_SYUKKINBO";
	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 MSyukkinboDao() {
		super();
	}

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

	/**
	 * 検索結果取得(DTO)
	 * @return m_syukkinboDto m_syukkinboレコード
	 * @throws SQLException
	 */
	private MSyukkinboDto mapping() throws SQLException {
		MSyukkinboDto dto = new MSyukkinboDto();
		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));		//二時間年休
		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_syukkinboDto 検索結果(DTO)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public MSyukkinboDto findForKey(
			String kcode,
			String hizuke
	) throws SQLException, IllegalAccessException, NoSuchFieldException {
		try {
			index = 1;
			prepareStatement(getSelectQuery(getClass()) + getConditionForKey(getClass()));
			setParam(index++, kcode);
			setParam(index++, hizuke);
			executeQuery();
			MSyukkinboDto 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_syukkinboDto 検索結果(DTO)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public MSyukkinboDto 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();
			MSyukkinboDto 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<MSyukkinboDto> 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<MSyukkinboDto> 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<MSyukkinboDto> findForAttendanceList(
			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<MSyukkinboDto> list = mappingAll();
			return list;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	/**
	 * 条件文によるデータ取得
	 * @param date		日付
	 * @param kCode		検索社員コード
	 * @param kName		検索社員名称
	 * @param approval	承認状況
	 * @return m_syukkinboDto 検索結果(DTO)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public MSyukkinboDto findForDayApproval(
			String	date,
			String  kCode,
			String	kName,
			String 	approval
	) throws SQLException, IllegalAccessException, NoSuchFieldException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append("WHERE ");
		sb.append(COL_HIZUKE);
		sb.append(" = ? ");
		if (!kCode.equals("")) {
			sb.append(" AND ");
			sb.append(COL_KCODE);
			sb.append(" LIKE ? ");			
		}
		if (!kName.equals("")) {
			sb.append(" AND ");
			sb.append(	" ( ");
			sb.append(		COL_KCODE + " IN (" + MKihonDao.getSearchKNameSubQuery() + ") ");				
			sb.append(	" ) ");
		}
		if (!approval.equals("")) {
			sb.append(" AND ");
			sb.append(COL_SNRIYUU);
			sb.append(" LIKE ? ");			
		}
		try {
			prepareStatement(getSelectQuery(getClass()) + sb.toString() + getOrderForKey(getClass()));
			setParam(index++, date	);
			if (!kCode.equals("")) {
				setParam(index++, "%" + kCode + "%" );
			}
			if (!kName.equals("")) {
				setParam(index++,  "%" + kName + "%" );
			}
			if (!approval.equals("")) {
				setParam(index++,  approval );
			}
			executeQuery();
			MSyukkinboDto dto = null;
			if (rs.next()) {
				dto = mapping();
				mappingCommonInfo(dto);
			}
			return dto;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}

	/**
	 * sum値取得
	 * @param kcode 検索社員コード
	 * @param startdate 検索開始日付
	 * @param enddate 検索終了日付
	 * @return 合計時間
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public int findForSum(
			String kcode,
			String startdate,
			String enddate
	) throws SQLException, IllegalAccessException, NoSuchFieldException {
		try {
			int sumOverTime = 0;
			index = 1;
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT SUM(");
			sb.append(COL_ZAJIKAN);
			sb.append(") FROM ");
			sb.append(TABLE);
			sb.append(" WHERE ");
			sb.append(COL_KCODE);
			sb.append(" = ? AND ? <= ");
			sb.append(COL_HIZUKE);
			sb.append(" AND ");
			sb.append(COL_HIZUKE);
			sb.append(" <= ? ");
			prepareStatement(sb.toString());
			setParam(index++, kcode);
			setParam(index++, startdate);
			setParam(index++, enddate);
			executeQuery();
			if (rs.next()) {
				sumOverTime = rs.getInt(1);
			}
			return sumOverTime;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}

	/**
	 * データ挿入
	 * @param dto 挿入対象DTO
	 * @return int 挿入結果(件数)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 * @throws MospException
	 */
	public int insert(MSyukkinboDto 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 dto 挿入対象DTO
	 * @return int 挿入結果(件数)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 * @throws MospException
	 */
	public int insertStart(MSyukkinboDto dto) throws SQLException, IllegalAccessException, NoSuchFieldException, MospException {
		try {
			index = 1;
			StringBuffer sb = new StringBuffer();
			sb.append("INSERT INTO ");
			sb.append(TABLE);
			sb.append(" (");
			sb.append(COL_KCODE 	+ MospConst.DATA_SEPARATOR);
			sb.append(COL_HIZUKE 	+ MospConst.DATA_SEPARATOR);
			sb.append(COL_NEN 		+ MospConst.DATA_SEPARATOR);
			sb.append(COL_TUKI 		+ MospConst.DATA_SEPARATOR);
			sb.append(COL_HI 		+ MospConst.DATA_SEPARATOR);
			sb.append(COL_SYOZOKU	+ MospConst.DATA_SEPARATOR);
			sb.append(COL_SYJIKOKU 	+ MospConst.DATA_SEPARATOR);
			sb.append(COL_KINMU 	+ MospConst.DATA_SEPARATOR);
			sb.append(COL_SHANKYUKB + MospConst.DATA_SEPARATOR);
			sb.append(COL_KYUSYUKB 	+ MospConst.DATA_SEPARATOR);
			sb.append(COL_TIRIYUU 	+ MospConst.DATA_SEPARATOR);
			sb.append(COL_TICOMENT 	+ MospConst.DATA_SEPARATOR);
			sb.append(COL_TISYOUMEI + MospConst.DATA_SEPARATOR);
			sb.append(COL_TIJIKAN 	+ MospConst.DATA_SEPARATOR);
			sb.append(COL_SYTIMES 	+ MospConst.DATA_SEPARATOR);
			sb.append(INSERT_DATE 	+ MospConst.DATA_SEPARATOR);
			sb.append(INSERT_USER	+ MospConst.DATA_SEPARATOR);
			sb.append(UPDATE_DATE 	+ MospConst.DATA_SEPARATOR);
			sb.append(UPDATE_USER 	);
			sb.append(") VALUES(");
			for (int i = 0; i < 18; i++) {
				sb.append(" ?,");
			}
			sb.append(" ?)");		
			prepareStatement(sb.toString());
			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.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());
			setCommonParams(true);
			executeUpdate();
			chkInsert(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 insertHoliday(MSyukkinboDto dto) throws SQLException, IllegalAccessException, NoSuchFieldException, MospException {
		try {
			index = 1;
			StringBuffer sb = new StringBuffer();
			sb.append("INSERT INTO ");
			sb.append(TABLE);
			sb.append(" (");
			sb.append(COL_KCODE 		+ MospConst.DATA_SEPARATOR);
			sb.append(COL_HIZUKE 		+ MospConst.DATA_SEPARATOR);
			sb.append(COL_NEN 			+ MospConst.DATA_SEPARATOR);
			sb.append(COL_TUKI 			+ MospConst.DATA_SEPARATOR);
			sb.append(COL_HI 			+ MospConst.DATA_SEPARATOR);
			sb.append(COL_SYOZOKU		+ MospConst.DATA_SEPARATOR);
			sb.append(COL_KINMU 		+ MospConst.DATA_SEPARATOR);
			sb.append(COL_KKRIYUU 		+ MospConst.DATA_SEPARATOR);
			sb.append(COL_DAIKYUJYU 	+ MospConst.DATA_SEPARATOR);
			sb.append(COL_KKCOMENT 		+ MospConst.DATA_SEPARATOR);
			sb.append(COL_KKSYOUMEI 	+ MospConst.DATA_SEPARATOR);
			sb.append(COL_KKJIKAN 		+ MospConst.DATA_SEPARATOR);
			sb.append(COL_KKTIMES 		+ MospConst.DATA_SEPARATOR);
			sb.append(COL_SNRIYUU		+ MospConst.DATA_SEPARATOR);
			sb.append(COL_UPDATETIMES 	+ MospConst.DATA_SEPARATOR);	
			sb.append(INSERT_DATE 		+ MospConst.DATA_SEPARATOR);
			sb.append(INSERT_USER		+ MospConst.DATA_SEPARATOR);
			sb.append(UPDATE_DATE 		+ MospConst.DATA_SEPARATOR);
			sb.append(UPDATE_USER 		);
			sb.append(") VALUES(");
			for (int i = 0; i < 18; i++) {
				sb.append(" ?,");
			}
			sb.append(" ?)");		
			prepareStatement(sb.toString());
			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.getKinmu());
			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.getUpdtTimes());
			setCommonParams(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(MSyukkinboDto[] 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 updateStart(MSyukkinboDto dto) throws SQLException, IllegalAccessException, NoSuchFieldException, MospException {
		try {
			index = 1;
			StringBuffer sb = new StringBuffer();
			sb.append("UPDATE ");
			sb.append(TABLE);
			sb.append(" SET ");
			sb.append(COL_SYJIKOKU 	+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_KINMU 	+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_SHANKYUKB + " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_KYUSYUKB 	+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_TIRIYUU 	+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_TICOMENT 	+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_TISYOUMEI + " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_TIJIKAN 	+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_SYTIMES 	+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(UPDATE_DATE 	+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(UPDATE_USER 	+ " =?"	);
			sb.append(" WHERE ");
			sb.append(COL_KCODE + " =?");
			sb.append(" AND ");
			sb.append(COL_HIZUKE + " =?");
			prepareStatement(sb.toString());
			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());
			setCommonParams(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 updateQuit(MSyukkinboDto dto) throws SQLException, IllegalAccessException, NoSuchFieldException, MospException {
		try {
			index = 1;
			StringBuffer sb = new StringBuffer();
			sb.append("UPDATE ");
			sb.append(TABLE);
			sb.append(" SET ");
			sb.append(COL_TAJIKOKU 		+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_KINMUJI 		+ " =?" + MospConst.DATA_SEPARATOR);			
			sb.append(COL_KYUKEIJI 		+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_THANKYUKB 	+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_SORIYUU 		+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_SOCOMENT 		+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_SOSYOUMEI 	+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_SOJIKAN 		+ " =?" + 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_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 		+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_TATIMES 		+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_SNRIYUU 		+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_UPDATETIMES 	+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(UPDATE_DATE 		+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(UPDATE_USER 		+ " =?"	);
			sb.append(" WHERE ");
			sb.append(COL_KCODE + " =?");
			sb.append(" AND ");
			sb.append(COL_HIZUKE + " =?");
			prepareStatement(sb.toString());
			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());
			setParam(index++, dto.getCyoku2());
			setParam(index++, dto.getCyoku3());
			setParam(index++, dto.getHoka1());
			setParam(index++, dto.getHoka2());
			setParam(index++, dto.getHoka3());
			setParam(index++, dto.getHoka4());
			setParam(index++, dto.getHoka5());
			setParam(index++, dto.getTaTimes());
			setParam(index++, dto.getSnRiyuu());
			setParam(index++, dto.getUpdtTimes());
			setCommonParams(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 updateHoliday(MSyukkinboDto dto) throws SQLException, IllegalAccessException, NoSuchFieldException, MospException {
		try {
			index = 1;
			StringBuffer sb = new StringBuffer();
			sb.append("UPDATE ");
			sb.append(TABLE);
			sb.append(" SET ");
			sb.append(COL_KKRIYUU 		+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_DAIKYUJYU 	+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_KKCOMENT 		+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_KKSYOUMEI 	+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_KKJIKAN 		+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_KKTIMES 		+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(COL_UPDATETIMES 	+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(UPDATE_DATE 		+ " =?" + MospConst.DATA_SEPARATOR);
			sb.append(UPDATE_USER 		+ " =?"	);
			sb.append(" WHERE ");
			sb.append(COL_KCODE + " =?");
			sb.append(" AND ");
			sb.append(COL_HIZUKE + " =?");
			prepareStatement(sb.toString());
			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.getUpdtTimes());
			setCommonParams(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 update(MSyukkinboDto 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(MSyukkinboDto 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(MSyukkinboDto 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());	//二時間年休
		setCommonParams(isInsert);
	}

}
