/*
 * 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.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.report.dto.AttendanceManagementDto;

public class AttendanceManagementDao extends BaseDao {

	//	 テーブル名及びキー列名
	public static final String TABLE  	= "M_KIHON"		;
	public static final String TABLE2  	= "M_KINTAI"	;
	public static final String KEY1  		= "KCODE"		;

	// 列名
	public static final String COL_KCODE  		= "KCODE";
	public static final String COL_SIKYUYM  		= "SIKYUYM";		
	public static final String COL_SIMEI  		= "SIMEI";	
	public static final String COL_SNISSU  		= "SNISSU";
	public static final String COL_KINMUJI  		= "KINMUJI";		
	public static final String COL_KYUKEIJI  		= "KYUKEIJI";	
	public static final String COL_YUKYU  		= TABLE2 + ".YUKYU";	
	public static final String COL_TOKUKYU  		= "TOKUKYU";		
	public static final String COL_KEKKIN  		= "KEKKIN";	
	public static final String COL_SKEKKIN  		= "SKEKKIN";	
	public static final String COL_TISOKAI  		= "TISOKAI";		
	public static final String COL_TIJIKAN  		= "TIJIKAN";	
	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_KAIKIN  		= "KAIKIN";	
	public static final String COL_SYOKUJI 		= "SYOKUJIKAISU";		
	public static final String COL_CYOKU1  		= "CYOKUKAISU1";	
	public static final String COL_CYOKU2  		= "CYOKUKAISU2";	
	public static final String COL_CYOKU3  		= "CYOKUKAISU3";		
	public static final String COL_HOKA1  		= "HOKAKAISU1";	
	public static final String COL_HOKA2  		= "HOKAKAISU2";	
	public static final String COL_HOKA3  		= "HOKAKAISU3";	
	public static final String COL_HOKA4  		= "HOKAKAISU4";		
	public static final String COL_HOKA5  		= "HOKAKAISU5";	

	public static final int NUM_YUKYU				= 6;

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

	/**
	 * 検索結果取得(DTOのList)
	 * @return List 検索リスト
	 * @throws SQLException
	 */
	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
	 */
	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
	 * @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( TABLE );
		sb.append(" LEFT JOIN ");
		sb.append( TABLE2 );
		sb.append(" ON ");
		sb.append( TABLE + "."+ KEY1 );
		sb.append(" = ");
		sb.append( TABLE2 + "." + KEY1 );
		sb.append(" WHERE ");
		sb.append(TABLE2 + "." + COL_SIKYUYM);
		sb.append(" = ? ");
		if (!sCode.equals("")) {
			sb.append(" AND ");
			sb.append(TABLE + "." + "scode");
			sb.append(" = ? ");
		}
		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();
		}
	}

}
