/*
 * 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 jp.mosp.common.common.MospException;
import jp.mosp.payroll.base.PayrollDao;
import jp.mosp.payroll.dto.CmCompanyDto;

public class CmCompanyDao extends PayrollDao {
	
	// テーブル名及びキー列名
	public static final String TABLE = "CM_COMPANY"  ;
	public static final String KEY_1 = "COMPANY_CODE";	              // 会社コード
	
	// 列名
	public static final String COL_COMPANY_CODE          = "COMPANY_CODE"         ; // 会社コード
	public static final String COL_COMPANY_NAME          = "COMPANY_NAME"         ; // 会社名
	public static final String COL_COMPANY_KANA          = "COMPANY_KANA"         ; // 会社ﾌﾘｶﾞﾅ
	public static final String COL_COMPANY_FB_KANA       = "COMPANY_FB_KANA"         ; // 会社ﾌﾘｶﾞﾅ
	public static final String COL_ENTERPRENEUR          = "ENTERPRENEUR"         ; // 事業主名
	public static final String COL_COMPANY_POSTAL        = "COMPANY_POSTAL"       ; // 会社郵便番号
	public static final String COL_COMPANY_ADDRESS       = "COMPANY_ADDRESS"      ; // 会社住所
	public static final String COL_COMPANY_FB_ADDRESS    = "COMPANY_FB_ADDRESS"   ; // 会社住所
	public static final String COL_COMPANY_PHONE         = "COMPANY_PHONE"        ; // 会社電話番号
	public static final String COL_COM_HEALTH_PREMIUM_RATE   = "COM_HEALTH_PREMIUM_RATE"  ; // 健康保険料率
	public static final String COL_COM_NURSING_PREMIUM_RATE  = "COM_NURSING_PREMIUM_RATE" ; // 介護保険料率
	public static final String COL_COM_PENSION_PREMIUM_RATE  = "COM_PENSION_PREMIUM_RATE" ; // 厚生年金料率
	public static final String COL_COM_FUND_PREMIUM_RATE     = "COM_FUND_PREMIUM_RATE"    ; // 厚生年金基金料率
	public static final String COL_COM_UNEMPLOY_PREMIUM_RATE = "COM_UNEMPLOY_PREMIUM_RATE"; // 雇用保険料率
	public static final String COL_HEALTH_PREMIUM_RATE   = "HEALTH_PREMIUM_RATE"  ; // 健康保険料率
	public static final String COL_NURSING_PREMIUM_RATE  = "NURSING_PREMIUM_RATE" ; // 介護保険料率
	public static final String COL_PENSION_PREMIUM_RATE  = "PENSION_PREMIUM_RATE" ; // 厚生年金料率
	public static final String COL_FUND_PREMIUM_RATE     = "FUND_PREMIUM_RATE"    ; // 厚生年金基金料率
	public static final String COL_UNEMPLOY_PREMIUM_RATE = "UNEMPLOY_PREMIUM_RATE"; // 雇用保険料率
	public static final String COL_FUND_PARTICIPATION    = "FUND_PARTICIPATION"; // 厚生年金基金加入
	public static final String COL_STATION_NO            = "STATION_NO"; // 署番号
	public static final String COL_DISPOSE_NO            = "DISPOSE_NO"; // 整理番号


	
	/**
	 * コンストラクタ
	 */
	public CmCompanyDao() {
		super();
	}
	
	/**
	 * 検索結果取得(DTOのList)
	 * @return List 検索リスト
	 * @throws SQLException
	 */
/*	private List<CmCompanyDto> mappingAll() throws SQLException {
		List<CmCompanyDto> all = new ArrayList<CmCompanyDto>();
		while (rs.next()) {
			all.add(mapping());
		}
		return all;
	}
*/	
	/**
	 * 検索結果取得(DTO)
	 * @return CmCompanyDto レコード
	 * @throws SQLException
	 */
	private CmCompanyDto mapping() throws SQLException {
		CmCompanyDto dto = new CmCompanyDto();
		dto.setCompanyCode        (rs.getString(COL_COMPANY_CODE         )); // 会社コード
		dto.setCompanyName        (rs.getString(COL_COMPANY_NAME         )); // 会社名
		dto.setCompanyKana        (rs.getString(COL_COMPANY_KANA         )); // 会社ﾌﾘｶﾞﾅ
		dto.setCompanyFbKana      (rs.getString(COL_COMPANY_FB_KANA      ));
		dto.setEnterpreneur       (rs.getString(COL_ENTERPRENEUR         )); // 事業主名
		dto.setCompanyPostal      (rs.getString(COL_COMPANY_POSTAL       )); // 会社郵便番号
		dto.setCompanyAddress     (rs.getString(COL_COMPANY_ADDRESS      )); // 会社住所
		dto.setCompanyFbAddress     (rs.getString(COL_COMPANY_FB_ADDRESS      )); // 会社住所
		dto.setCompanyPhone       (rs.getString(COL_COMPANY_PHONE        )); // 会社電話番号
		dto.setComHealthPremiumRate  (rs.getFloat(COL_COM_HEALTH_PREMIUM_RATE   )); // 健康保険料率
		dto.setComNursingPremiumRate (rs.getFloat(COL_COM_NURSING_PREMIUM_RATE  )); // 介護保険料率
		dto.setComPensionPremiumRate (rs.getFloat(COL_COM_PENSION_PREMIUM_RATE  )); // 厚生年金料率
		dto.setComFundPremiumRate    (rs.getFloat(COL_COM_FUND_PREMIUM_RATE     )); // 厚生年金基金料率
		dto.setComUnemployPremiumRate(rs.getFloat(COL_COM_UNEMPLOY_PREMIUM_RATE )); // 雇用保険料率
		dto.setHealthPremiumRate  (rs.getFloat(COL_HEALTH_PREMIUM_RATE   )); // 健康保険料率
		dto.setNursingPremiumRate (rs.getFloat(COL_NURSING_PREMIUM_RATE  )); // 介護保険料率
		dto.setPensionPremiumRate (rs.getFloat(COL_PENSION_PREMIUM_RATE  )); // 厚生年金料率
		dto.setFundPremiumRate    (rs.getFloat(COL_FUND_PREMIUM_RATE     )); // 厚生年金基金料率
		dto.setUnemployPremiumRate(rs.getFloat(COL_UNEMPLOY_PREMIUM_RATE )); // 雇用保険料率
		dto.setFundParticipation  (rs.getString(COL_FUND_PARTICIPATION   )); // 厚生年金基金加入
		dto.setStationNo          (rs.getString(COL_STATION_NO           )); // 署番号
		dto.setDisposeNo          (rs.getString(COL_DISPOSE_NO           )); // 整理番号

		mappingCommonInfo(dto);
		return dto;
	}
	
	/**
	 * 全データ取得
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public CmCompanyDto findAll() throws SQLException, IllegalAccessException, NoSuchFieldException {
		try {
			StringBuffer sb = new StringBuffer();
			sb.append("WHERE ");
			sb.append(COL_COMPANY_CODE + " <> '' ");
			prepareStatement(getSelectQuery(getClass()) + sb.toString() + getOrderForKey(getClass()));
			executeQuery();
			CmCompanyDto dto = null;
			if (rs.next()) {
				dto = mapping();
			}
			return dto;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	
	
	/**
	 * キーによるデータ取得
	 * @param kCode 社員コード(検索キー)
	 * @return CmCompanyDto 検索結果(DTO)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public CmCompanyDto findForKey(
			String  kCode
	) throws SQLException, IllegalAccessException, NoSuchFieldException {
		try {
			index = 1;
			prepareStatement(getSelectQuery(getClass()) + getConditionForKey(getClass()));
			setParam(index++, kCode);
			executeQuery();
			CmCompanyDto dto = null;
			if (rs.next()) {
				dto = mapping();
			}
			return dto;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	

	

	
	/**
	 * 社員による行ロック取得
	 * @param kCode 社員コード(検索キー)
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public CmCompanyDto findForUpdate(
			String kCode
	) throws SQLException, IllegalAccessException, NoSuchFieldException {
		try {
			index = 1;
			StringBuffer sb = new StringBuffer();
			sb.append("WHERE ");
			sb.append(COL_COMPANY_CODE + " = ? ");
			prepareStatement(getSelectQuery(getClass()) + sb.toString() + getOrderForKey(getClass()) + getForUpdate());
			setParam(index++, kCode);
			executeQuery();
			CmCompanyDto dto = null;
			if (rs.next()) {
				dto = mapping();
			}
			return dto;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	/**
	 * データ挿入
	 * @param dto 挿入対象DTO
	 * @return int 挿入結果(件数)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 * @throws MospException
	 */
	public int insert(CmCompanyDto 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(CmCompanyDto dto) throws SQLException, IllegalAccessException, NoSuchFieldException, MospException {
		try {
			index = 1;
			prepareStatement(getUpdateQuery(getClass()));
			setParams(dto, false);
			setParam(index++, dto.getCompanyCode());
			executeUpdate();
			chkUpdate(1);
			return cnt;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	/**
	 * 社員によるデータ削除
	 * @param kCode 削除対象社員コード
	 * @return int 削除結果(件数)
	 * @throws SQLException
	 */
	public int deleteForEmployee(String kCode) throws SQLException {
		try {
			index = 1;
			StringBuffer sb = new StringBuffer();
			sb.append("DELETE FROM " + TABLE + " ");
			sb.append("WHERE ");
			sb.append(COL_COMPANY_CODE + " = ? ");
			prepareStatement(sb.toString());
			setParam(index++, kCode);
			executeUpdate();
			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(CmCompanyDto dto) throws SQLException, IllegalAccessException, NoSuchFieldException, MospException {
		try {
			index = 1;
			prepareStatement(getDeleteQuery(getClass()));
			setParam(index++, dto.getCompanyCode());
			executeUpdate();
			chkDelete(1);
			return cnt;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	/**
	 * パラメータ設定
	 * @param dto 更新対象DTO
	 * @param isInsert 挿入文フラグ(true：挿入文、false：更新文)
	 * @throws SQLException
	 */
	private void setParams(CmCompanyDto dto, boolean isInsert) throws SQLException {
		setParam(index++, dto.getCompanyCode        ()); // 会社コード
		setParam(index++, dto.getCompanyName        ()); // 会社名
		setParam(index++, dto.getCompanyKana        ()); // 会社ﾌﾘｶﾞﾅ
		setParam(index++, dto.getCompanyFbKana        ()); // 会社ﾌﾘｶﾞﾅ
		setParam(index++, dto.getEnterpreneur       ()); // 事業主名
		setParam(index++, dto.getCompanyPostal      ()); // 会社郵便番号
		setParam(index++, dto.getCompanyAddress     ()); // 会社住所
		setParam(index++, dto.getCompanyFbAddress     ()); // 会社住所
		setParam(index++, dto.getCompanyPhone       ()); // 会社電話番号
		setParam(index++, dto.getComHealthPremiumRate  ()); // 健康保険料率
		setParam(index++, dto.getComNursingPremiumRate ()); // 介護保険料率
		setParam(index++, dto.getComPensionPremiumRate ()); // 厚生年金料率
		setParam(index++, dto.getComFundPremiumRate    ()); // 厚生年金基金料率
		setParam(index++, dto.getComUnemployPremiumRate()); // 雇用保険料率
		setParam(index++, dto.getHealthPremiumRate  ());    // 健康保険料率
		setParam(index++, dto.getNursingPremiumRate ());    // 介護保険料率
		setParam(index++, dto.getPensionPremiumRate ());    // 厚生年金料率
		setParam(index++, dto.getFundPremiumRate    ());    // 厚生年金基金料率
		setParam(index++, dto.getUnemployPremiumRate());    // 雇用保険料率
		setParam(index++, dto.getFundParticipation  ());    // 厚生年金基金加入
		setParam(index++, dto.getStationNo          ());    // 署番号
		setParam(index++, dto.getDisposeNo          ());    // 整理番号
		
		setCommonParams(isInsert);
	}

}
