/*
 * 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.report.dto.AttendanceManagementDto;
import jp.mosp.payroll.dao.MKintaiDao;

/**
 * @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;
	
	/**
	 * 6
	 */
	public static final int		NUM_YUKYU		= 6;
	

	/**
	 * コンストラクタ
	 */
	public AttendanceManagementDao() {
		super();
	}
	
	/**
	 * 検索結果取得(DTOのList)
	 * @return List 検索リスト
	 * @throws SQLException SQL例外が発生した場合
	 */
	private List<AttendanceManagementDto> mappingAll() throws SQLException {
		List<AttendanceManagementDto> all = new ArrayList<AttendanceManagementDto>();
		while (rs.next()) {
			all.add(mapping());
		}
		return all;
	}
	
	/**
	 * 検索結果取得(DTO)
	 * @return m_kintaiDto m_kintaiレコード
	 * @throws SQLException SQL例外が発生した場合
	 */
	private AttendanceManagementDto mapping() throws SQLException {
		AttendanceManagementDto dto = new AttendanceManagementDto();
		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;
	}
	
	/**
	 * 条件文によるデータ取得
	 * @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(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();
			List<AttendanceManagementDto> list = mappingAll();
			return list;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
}
