/*
 * 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.system.dao;

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.system.dto.MKaisyaDto;

public class MKaisyaDao extends BaseDao {

	// テーブル名及びキー列名
	public static final String TABLE  	= "M_KAISYA";
	public static final String KEY_1 		= "ID";			//事業所コード

	// 列名
	public static final String COL_ID 			= "ID";			//事業所コード
	public static final String COL_KAIMEI  		= "KAIMEI";		//会社名
	public static final String COL_KAIFURI 	 	= "KAIFURI";	//ふりがな
	public static final String COL_KJIGYONUSI  	= "KJIGYONUSI";	//事業主
	public static final String COL_KAIYUBIN  		= "KAIYUBIN";	//郵便番号
	public static final String COL_KAIJYUSYO  	= "KAIJYUSYO";	//住所
	public static final String COL_KAITEL  		= "KAITEL";		//電話番号
	public static final String COL_KAIFAX  		= "KAIFAX";		//ファックス番号
	public static final String COL_RODOUBI  		= "RODOUBI";	//月間所定労働日数
	public static final String COL_ROUDOUJI  		= "ROUDOUJI";	//月間所定労働時間数
	public static final String COL_ROUDOUJI1  	= "ROUDOUJI1";	//一日所定労働時間数
	public static final String COL_ZARITU  		= "ZARITU";		//普通残業割増率
	public static final String COL_KSRITU  		= "KSRITU";		//休日残業割増率
	public static final String COL_SNRITU 		= "SNRITU";		//深夜残業割増率
	public static final String COL_ZANTANKBN  	= "ZANTANKBN";	//残業単価自動計算
	public static final String COL_RITKOYOU  		= "RITKOYOU";	//雇用保険率
	public static final String COL_RITKKOYOU  	= "RITKKOYOU";	//雇用保険率会社
	public static final String COL_RITKENPO  		= "RITKENPO";	//健康保険料率
	public static final String COL_RITKKENPO  	= "RITKKENPO";	//健康保険料率会社
	public static final String COL_RITKAIGO  		= "RITKAIGO";	//介護保険料率
	public static final String COL_RITKKAIGO  	= "RITKKAIGO";	//介護保険料率会社
	public static final String COL_RITKOSEI  		= "RITKOSEI";	//厚生年金料率
	public static final String COL_RITKKOSEI  	= "RITKKOSEI";	//厚生年金料率会社
	public static final String COL_RITKOSEI3  	= "RITKOSEI3";	//厚生年金三種料率
	public static final String COL_RITKKOSEI3  	= "RITKKOSEI3";	//厚生年金三種料率
	public static final String COL_KOSEI3KBN  	= "KOSEI3KBN";	//厚生年金第三種
	public static final String COL_KIKINKBN  		= "KIKINKBN";	//厚生年金基金加入
	public static final String COL_RITKIKIN  		= "RITKIKIN";	//厚生年金料率基金
	public static final String COL_RITKKIKIN  	= "RITKKIKIN";	//厚生年金料率基金
	public static final String COL_RITKIKIN3  	= "RITKIKIN3";	//厚生年金三種料率基金
	public static final String COL_RITKKIKIN3  	= "RITKKIKIN3";	//厚生年金三種料率基金
	public static final String COL_SYAHOFUGO  	= "SYAHOFUGO";	//郡市区符号
	public static final String COL_SYAHOKIGO  	= "SYAHOKIGO";	//事業所記号
	public static final String COL_SYAHOBANGO  	= "SYAHOBANGO";	//事業所
	public static final String COL_KENPOJINO  	= "KENPOJINO";	//健康保険事業所番号
	public static final String COL_KIKINJINO  	= "KIKINJINO";	//厚生年金基金事業所番号
	public static final String COL_KAIBANK  		= "KAIBANK";	//取引銀行名
	public static final String COL_SERVER  		= "SERVER";		//サーバー名

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

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

	/**
	 * 検索結果取得(DTO)
	 * @return m_kaisyaDto m_kaisyaレコード
	 * @throws SQLException
	 */
	private MKaisyaDto mapping() throws SQLException {
		MKaisyaDto dto = new MKaisyaDto();
		dto.setId 			(rs.getString (COL_ID			));		//事業所コード
		dto.setKaiMei 		(rs.getString (COL_KAIMEI		));		//会社名
		dto.setKaiFuri 		(rs.getString (COL_KAIFURI		));		//ふりがな
		dto.setKJigyoNusi 	(rs.getString (COL_KJIGYONUSI	));		//事業主
		dto.setKaiYubin 	(rs.getString (COL_KAIYUBIN		));		//郵便番号
		dto.setKaiJyusyo 	(rs.getString (COL_KAIJYUSYO 	));		//住所
		dto.setKaiTel 		(rs.getString (COL_KAITEL		));		//電話番号
		dto.setKaiFax 		(rs.getString (COL_KAIFAX		));		//ファックス番号
		dto.setRodouBi 		(rs.getInt    (COL_RODOUBI		));		//月間所定労働日数
		dto.setRoudouJi 	(rs.getInt    (COL_ROUDOUJI		));		//月間所定労働時間数
		dto.setRoudouJi1 	(rs.getInt    (COL_ROUDOUJI1	));		//一日所定労働時間数
		dto.setZaRitu 		(rs.getInt    (COL_ZARITU		));		//普通残業割増率
		dto.setKsRitu 		(rs.getInt    (COL_KSRITU		));		//休日残業割増率
		dto.setSnRitu 		(rs.getInt    (COL_SNRITU		));		//深夜残業割増率
		dto.setZanTanKbn 	(rs.getString (COL_ZANTANKBN	));		//残業単価自動計算
		dto.setRitKoyou 	(rs.getDouble (COL_RITKOYOU		));		//雇用保険率
		dto.setRitKKoyou 	(rs.getDouble (COL_RITKKOYOU	));		//雇用保険率会社
		dto.setRitKenpo 	(rs.getDouble (COL_RITKENPO		));		//健康保険料率
		dto.setRitKKenpo 	(rs.getDouble (COL_RITKKENPO	));		//健康保険料率会社
		dto.setRitKaigo 	(rs.getDouble (COL_RITKAIGO		));		//介護保険料率
		dto.setRitKKaigo 	(rs.getDouble (COL_RITKKAIGO	));		//介護保険料率会社
		dto.setRitKosei 	(rs.getDouble (COL_RITKOSEI		));		//厚生年金料率
		dto.setRitKKosei 	(rs.getDouble (COL_RITKKOSEI	));		//厚生年金料率会社
		dto.setRitKosei3 	(rs.getDouble (COL_RITKOSEI3	));		//厚生年金三種料率
		dto.setRitKKosei3 	(rs.getDouble (COL_RITKKOSEI3	));		//厚生年金三種料率
		dto.setKosei3Kbn 	(rs.getString (COL_KOSEI3KBN	));		//厚生年金第三種
		dto.setKikinKbn 	(rs.getString (COL_KIKINKBN		));		//厚生年金基金加入
		dto.setRitKikin 	(rs.getDouble (COL_RITKIKIN		));		//厚生年金料率基金
		dto.setRitKKikin 	(rs.getDouble (COL_RITKKIKIN	));		//厚生年金料率基金
		dto.setRitKikin3 	(rs.getDouble (COL_RITKIKIN3	));		//厚生年金三種料率基金
		dto.setRitKKikin3 	(rs.getDouble (COL_RITKKIKIN3	));		//厚生年金三種料率基金
		dto.setSyahoFugo 	(rs.getString (COL_SYAHOFUGO	));		//郡市区符号
		dto.setSyahoKigo 	(rs.getString (COL_SYAHOKIGO	));		//事業所記号
		dto.setSyahoBango 	(rs.getString (COL_SYAHOBANGO	));		//事業所
		dto.setKenpoJiNo 	(rs.getString (COL_KENPOJINO	));		//健康保険事業所番号
		dto.setKikinJiNo 	(rs.getString (COL_KIKINJINO	));		//厚生年金基金事業所番号
		dto.setKaiBank 		(rs.getString (COL_KAIBANK		));		//取引銀行名
		dto.setServer 		(rs.getString (COL_SERVER		));		//サーバー名

		return dto;
	}

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

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

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

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

	/**
	 * 条件文によるデータ取得
	 * @param id 検索事業所コード
	 * @param kaimei 検索事業所名
	 * @return list 検索結果(DTOのList)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public List<MKaisyaDto> findForCondition(
			String id,
			String kaimei
	) throws SQLException, IllegalAccessException, NoSuchFieldException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append(" WHERE ");
		sb.append(COL_ID);
		sb.append(" LIKE ? AND ");
		sb.append(COL_KAIMEI);
		sb.append(" LIKE ?  ");
		try {
			prepareStatement(getSelectQuery(getClass()) + sb.toString() + getOrderForKey(getClass()));
			setParam(index++, id + "%");
			setParam(index++, "%" + kaimei + "%" );
			executeQuery();
			List<MKaisyaDto> 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(MKaisyaDto 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 update(MKaisyaDto dto) throws SQLException, IllegalAccessException, NoSuchFieldException, MospException {
		try {
			index = 1;
			prepareStatement(getUpdateQuery(getClass()));
			setParams(dto,false);
			setParam(index++, dto.getId());
			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(MKaisyaDto dto) throws SQLException, IllegalAccessException, NoSuchFieldException, MospException {
		try {
			index = 1;
			prepareStatement(getDeleteQuery(getClass()));
			setParam(index++, dto.getId());
			executeUpdate();
			chkDelete(1);
			return cnt;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}

	/**
	 * パラメータ設定
	 * @param dto 更新対象DTO
	 * @throws SQLException
	 */
	private void setParams(MKaisyaDto dto,boolean isInsert) throws SQLException {
		setParam(index++, dto.getId());			//事業所コード
		setParam(index++, dto.getKaiMei());		//会社名
		setParam(index++, dto.getKaiFuri());	//ふりがな
		setParam(index++, dto.getKJigyoNusi());	//事業主
		setParam(index++, dto.getKaiYubin());	//郵便番号
		setParam(index++, dto.getKaiJyusyo());	//住所
		setParam(index++, dto.getKaiTel());		//電話番号
		setParam(index++, dto.getKaiFax());		//ファックス番号
		setParam(index++, dto.getRodouBi());	//月間所定労働日数
		setParam(index++, dto.getRoudouJi());	//月間所定労働時間数
		setParam(index++, dto.getRoudouJi1());	//一日所定労働時間数
		setParam(index++, dto.getZaRitu());		//普通残業割増率
		setParam(index++, dto.getKsRitu());		//休日残業割増率
		setParam(index++, dto.getSnRitu());		//深夜残業割増率
		setParam(index++, dto.getZanTanKbn());	//残業単価自動計算
		setParam(index++, dto.getRitKoyou());	//雇用保険率
		setParam(index++, dto.getRitKKoyou());	//雇用保険率会社
		setParam(index++, dto.getRitKenpo());	//健康保険料率
		setParam(index++, dto.getRitKKenpo());	//健康保険料率会社
		setParam(index++, dto.getRitKaigo());	//介護保険料率
		setParam(index++, dto.getRitKKaigo());	//介護保険料率会社
		setParam(index++, dto.getRitKosei());	//厚生年金料率
		setParam(index++, dto.getRitKKosei());	//厚生年金料率会社
		setParam(index++, dto.getRitKosei3());	//厚生年金三種料率
		setParam(index++, dto.getRitKKosei3());	//厚生年金三種料率
		setParam(index++, dto.getKosei3Kbn());	//厚生年金第三種
		setParam(index++, dto.getKikinKbn());	//厚生年金基金加入
		setParam(index++, dto.getRitKikin());	//厚生年金料率基金
		setParam(index++, dto.getRitKKikin());	//厚生年金料率基金
		setParam(index++, dto.getRitKikin3());	//厚生年金三種料率基金
		setParam(index++, dto.getRitKKikin3());	//厚生年金三種料率基金
		setParam(index++, dto.getSyahoFugo());	//郡市区符号
		setParam(index++, dto.getSyahoKigo());	//事業所記号
		setParam(index++, dto.getSyahoBango());	//事業所
		setParam(index++, dto.getKenpoJiNo());	//健康保険事業所番号
		setParam(index++, dto.getKikinJiNo());	//厚生年金基金事業所番号
		setParam(index++, dto.getKaiBank());	//取引銀行名
		setParam(index++, dto.getServer());		//サーバー名
		setCommonParams(isInsert);
	}

}
