/*
 * 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.payroll.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.payroll.dto.CompileAmendmentDto;

public class CompileAmendmentDao 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  		= TABLE + ".KCODE";	
	public static final String COL_KNAME  		= "SIMEI";
	public static final String COL_SCODE	 		= "SCODE";
	public static final String COL_SNISSU  		= "SNISSU";
	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_KINMUJI  		= "KINMUJI";		
	public static final String COL_KYUKEIJI  		= "KYUKEIJI";	
	public static final String COL_TISOJI  		= "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_SIKYUYM		= "SIKYUYM";

	public static final int NUM_KCODE				= 1;
	public static final int NUM_YUKYU				= 5;

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

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

	/**
	 * 検索結果取得(DTO)
	 * @return m_kintaiDto m_kintaiレコード
	 * @throws SQLException
	 */
	private CompileAmendmentDto mapping() throws SQLException {
		CompileAmendmentDto dto = new CompileAmendmentDto();
		dto.setKCode	(rs.getString 	(NUM_KCODE		));
		dto.setKName 	(rs.getString 	(COL_KNAME  	));
		dto.setSCode	(rs.getString 	(COL_SCODE		));
		dto.setSNissu 	(rs.getDouble 	(COL_SNISSU  	));	
		dto.setYukyu 	(rs.getDouble 	(NUM_YUKYU		));
		dto.setTokuKyu 	(rs.getDouble 	(COL_TOKUKYU  	));	
		dto.setKekkin 	(rs.getDouble 	(COL_KEKKIN  	));
		dto.setKinmuJi 	(rs.getInt 		(COL_KINMUJI 	));
		dto.setKyukeiJi (rs.getInt 		(COL_KYUKEIJI 	));	
		dto.setTisoJi	(rs.getInt 		(COL_TISOJI  	));	
		dto.setZaJikan 	(rs.getInt 		(COL_ZAJIKAN  	));
		dto.setKsJikan 	(rs.getInt 		(COL_KSJIKAN	));	
		dto.setSnJikan 	(rs.getInt 		(COL_SNJIKAN	));
		return dto;
	}

	/**
	 * 条件文によるデータ取得
	 * @param sikyuym 選択年月
	 * @param sCode 所属コード
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public List findForCondition(
			String sikyuym,
			String sCode
	) throws SQLException, IllegalAccessException, NoSuchFieldException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT ");
		sb.append(COL_KCODE		+ MospConst.DATA_SEPARATOR	);
		sb.append(COL_KNAME		+ MospConst.DATA_SEPARATOR	);
		sb.append(COL_SCODE		+ MospConst.DATA_SEPARATOR	);
		sb.append(COL_SNISSU	+ 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_KINMUJI	+ MospConst.DATA_SEPARATOR	);
		sb.append(COL_KYUKEIJI	+ MospConst.DATA_SEPARATOR	);
		sb.append(COL_TISOJI	+ MospConst.DATA_SEPARATOR	);
		sb.append(COL_ZAJIKAN	+ MospConst.DATA_SEPARATOR	);
		sb.append(COL_KSJIKAN 	+ MospConst.DATA_SEPARATOR	);
		sb.append(COL_SNJIKAN								);
		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 + "." + COL_SCODE);
			sb.append(" = ? ");
		}
		try {
			prepareStatement(sb.toString());
			setParam(index++,		sikyuym);
			if (!sCode.equals("")) {
				setParam(index++,	sCode);				
			}
			executeQuery();
			List list = mappingAll();
			return list;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}

	/**
	 * 条件文によるデータ取得
	 * @param kCode 選択社員コード
	 * @param sikyuym 選択年月
	 * @param sCode 所属コード
	 * @return DTO 検索結果
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public CompileAmendmentDto findForCondition(
			String kCode,
			String sikyuym,
			String sCode
	) throws SQLException, IllegalAccessException, NoSuchFieldException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT ");
		sb.append(COL_KCODE		+ MospConst.DATA_SEPARATOR	);
		sb.append(COL_KNAME		+ MospConst.DATA_SEPARATOR	);
		sb.append(COL_SCODE		+ MospConst.DATA_SEPARATOR	);
		sb.append(COL_SNISSU	+ 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_KINMUJI	+ MospConst.DATA_SEPARATOR	);
		sb.append(COL_KYUKEIJI	+ MospConst.DATA_SEPARATOR	);
		sb.append(COL_TISOJI	+ MospConst.DATA_SEPARATOR	);
		sb.append(COL_ZAJIKAN	+ MospConst.DATA_SEPARATOR	);
		sb.append(COL_KSJIKAN 	+ MospConst.DATA_SEPARATOR	);
		sb.append(COL_SNJIKAN								);
		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 + "." + COL_SCODE);
			sb.append(" = ? ");
		}
		if (!kCode.equals("")) {
			sb.append(" AND ");
			sb.append(TABLE + "." + KEY1);
			sb.append(" = ? ");
		}
		try {
			prepareStatement(sb.toString());
			setParam(index++,		sikyuym);
			if (!sCode.equals("")) {
				setParam(index++,	sCode);				
			}
			if (!kCode.equals("")) {
				setParam(index++,	kCode);				
			}
			executeQuery();
			CompileAmendmentDto dto = null;
			if (rs.next()) {
				dto = mapping();
			}
			return dto;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}

}
