package jp.mosp.payroll.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import jp.mosp.common.common.MospException;
import jp.mosp.payroll.base.PayrollDao;
import jp.mosp.payroll.dto.CdSocInsuranceDto;

public class CdSocInsuranceDao extends PayrollDao {
	// テーブル名及びキー列名
	public static final String TABLE = "CD_SOC_INSURANCE";
	public static final String KEY_1 = "K_CODE"   ;	// 社員コード
	public static final String KEY_2 = "CALC_DATE";	// 計算年月
	
	// 列名
	public static final String COL_K_CODE                  = "K_CODE";                  // 社員コード
	public static final String COL_K_NAME                  = "K_NAME";                  // 社員氏名
	public static final String COL_SCODE                   = "SCODE";                   // 所属コード
	public static final String COL_CALC_DATE               = "CALC_DATE";               // 計算年月
	public static final String COL_PAY_BASIC_MONTH1        = "PAY_BASIC_MONTH1";        // 支払基礎日数1(月)
	public static final String COL_PAY_BASIC_MONTH2        = "PAY_BASIC_MONTH2";        // 支払基礎日数2(月)
	public static final String COL_PAY_BASIC_MONTH3        = "PAY_BASIC_MONTH3";        // 支払基礎日数3(月)
	public static final String COL_PAY_BASIC_DAY1          = "PAY_BASIC_DAY1";          // 支払基礎日数1(日)
	public static final String COL_PAY_BASIC_DAY2          = "PAY_BASIC_DAY2";          // 支払基礎日数2(日)
	public static final String COL_PAY_BASIC_DAY3          = "PAY_BASIC_DAY3";          // 支払基礎日数3(日)
	public static final String COL_PAY_AMOUNT1             = "PAY_AMOUNT1";             // 金銭の額1
	public static final String COL_PAY_AMOUNT2             = "PAY_AMOUNT2";             // 金銭の額2
	public static final String COL_PAY_AMOUNT3             = "PAY_AMOUNT3";             // 金銭の額3
	public static final String COL_GOODS_AMOUNT1           = "GOODS_AMOUNT1";           // 現物の額1
	public static final String COL_GOODS_AMOUNT2           = "GOODS_AMOUNT2";           // 現物の額2
	public static final String COL_GOODS_AMOUNT3           = "GOODS_AMOUNT3";           // 現物の額3
	public static final String COL_REWARD_TOTAL1           = "REWARD_TOTAL1";           // 報酬合計1
	public static final String COL_REWARD_TOTAL2           = "REWARD_TOTAL2";           // 報酬合計2
	public static final String COL_REWARD_TOTAL3           = "REWARD_TOTAL3";           // 報酬合計3
	public static final String COL_REWARD_TOTAL_SUM        = "REWARD_TOTAL_SUM";        // 報酬総額
	public static final String COL_AVERAGE_THREE_MONTHS    = "AVERAGE_THREE_MONTHS";    // ３ヶ月平均
	public static final String COL_EDIT_AVERAGE            = "EDIT_AVERAGE";            // 修正平均
	public static final String COL_HEALTH_BEF_GRADE        = "HEALTH_BEF_GRADE";        // 健保従前等級
	public static final String COL_HEALTH_BEF_PREMIUM      = "HEALTH_BEF_PREMIUM";      // 健保従前保険料
	public static final String COL_PENSION_BEF_GRADE       = "PENSION_BEF_GRADE";       // 厚年従前等級
	public static final String COL_PENSION_BEF_PREMIUM     = "PENSION_BEF_PREMIUM";     // 厚年従前保険料
	public static final String COL_HEALTH_DEC_GRADE        = "HEALTH_DEC_GRADE";        // 健保決定等級
	public static final String COL_HEALTH_DEC_PREMIUM      = "HEALTH_DEC_PREMIUM";      // 健保決定保険料
	public static final String COL_PENSION_DEC_GRADE       = "PENSION_DEC_GRADE";       // 厚年決定等級
	public static final String COL_PENSION_DEC_PREMIUM     = "PENSION_DEC_PREMIUM";     // 厚年決定保険料
	public static final String COL_RETROACTIVE_PAYMENT     = "RETROACTIVE_PAYMENT";     // 遡及支払額
	public static final String COL_RAISE_DOWN_SALARY_DATE  = "RAISE_DOWN_SALARY_DATE";  // 昇降給年月
	public static final String COL_RAISE_DOWN_SALARY_BASIS = "RAISE_DOWN_SALARY_BASIS"; // 昇降給差の月額
	public static final String COL_EXPECTED_DATE           = "EXPECTED_DATE";           // 適用年月
	public static final String COL_CALC_RESULT             = "CALC_RESULT";             // 計算結果


	
	/**
	 * コンストラクタ
	 */
	public CdSocInsuranceDao() {
		super();
	}
	
	/**
	 * 検索結果取得(DTOのList)
	 * @return List 検索リスト
	 * @throws SQLException
	 */
	private List<CdSocInsuranceDto> mappingAll() throws SQLException {
		List<CdSocInsuranceDto> all = new ArrayList<CdSocInsuranceDto>();
		while (rs.next()) {
			all.add(mapping());
		}
		return all;
	}
	
	/**
	 * 検索結果取得(DTO)
	 * @return UdPaymentDto UD_PAYMENTレコード
	 * @throws SQLException
	 */
	private CdSocInsuranceDto mapping() throws SQLException {
		CdSocInsuranceDto dto = new CdSocInsuranceDto();
		dto.setKCode               (rs.getString(COL_K_CODE                  )); // 社員コード
		dto.setKName               (rs.getString(COL_K_NAME                  )); // 社員氏名
		dto.setSCode               (rs.getString(COL_SCODE                   )); // 所属コード
		dto.setCalcDate            (rs.getDate  (COL_CALC_DATE               )); // 計算年月
		dto.setPayBasicMonth1      (rs.getInt   (COL_PAY_BASIC_MONTH1        )); // 支払基礎日数1(月)
		dto.setPayBasicMonth2      (rs.getInt   (COL_PAY_BASIC_MONTH2        )); // 支払基礎日数2(月)
		dto.setPayBasicMonth3      (rs.getInt   (COL_PAY_BASIC_MONTH3        )); // 支払基礎日数3(月)
		dto.setPayBasicDay1        (rs.getInt   (COL_PAY_BASIC_DAY1          )); // 支払基礎日数1(日)
		dto.setPayBasicDay2        (rs.getInt   (COL_PAY_BASIC_DAY2          )); // 支払基礎日数2(日)
		dto.setPayBasicDay3        (rs.getInt   (COL_PAY_BASIC_DAY3          )); // 支払基礎日数3(日)
		dto.setPayAmount1          (rs.getInt   (COL_PAY_AMOUNT1             )); // 金銭の額1
		dto.setPayAmount2          (rs.getInt   (COL_PAY_AMOUNT2             )); // 金銭の額2
		dto.setPayAmount3          (rs.getInt   (COL_PAY_AMOUNT3             )); // 金銭の額3
		dto.setGoodsAmount1        (rs.getInt   (COL_GOODS_AMOUNT1           )); // 現物の額1
		dto.setGoodsAmount2        (rs.getInt   (COL_GOODS_AMOUNT2           )); // 現物の額2
		dto.setGoodsAmount3        (rs.getInt   (COL_GOODS_AMOUNT3           )); // 現物の額3
		dto.setRewardTotal1        (rs.getInt   (COL_REWARD_TOTAL1           )); // 報酬合計1
		dto.setRewardTotal2        (rs.getInt   (COL_REWARD_TOTAL2           )); // 報酬合計2
		dto.setRewardTotal3        (rs.getInt   (COL_REWARD_TOTAL3           )); // 報酬合計3
		dto.setRewardTotalSum      (rs.getInt   (COL_REWARD_TOTAL_SUM        )); // 報酬総額
		dto.setAverageThreeMonths  (rs.getInt   (COL_AVERAGE_THREE_MONTHS    )); // ３ヶ月平均
		dto.setEditAverage         (rs.getInt   (COL_EDIT_AVERAGE            )); // 修正平均
		dto.setHealthBefGrade      (rs.getInt   (COL_HEALTH_BEF_GRADE        )); // 健保従前等級
		dto.setHealthBefPremium    (rs.getInt   (COL_HEALTH_BEF_PREMIUM      )); // 健保従前保険料
		dto.setPensionBefGrade     (rs.getInt   (COL_PENSION_BEF_GRADE       )); // 厚年従前等級
		dto.setPensionBefPremium   (rs.getInt   (COL_PENSION_BEF_PREMIUM     )); // 厚年従前保険料
		dto.setHealthDecGrade      (rs.getInt   (COL_HEALTH_DEC_GRADE        )); // 健保決定等級
		dto.setHealthDecPremium    (rs.getInt   (COL_HEALTH_DEC_PREMIUM      )); // 健保決定保険料
		dto.setPensionDecGrade     (rs.getInt   (COL_PENSION_DEC_GRADE       )); // 厚年決定等級
		dto.setPensionDecPremium   (rs.getInt   (COL_PENSION_DEC_PREMIUM     )); // 厚年決定保険料
		dto.setRetroactivePayment  (rs.getInt   (COL_RETROACTIVE_PAYMENT     )); // 遡及支払額
		dto.setRaiseDownSalaryDate (rs.getDate  (COL_RAISE_DOWN_SALARY_DATE  )); // 昇降給年月
		dto.setRaiseDownSalaryBasis(rs.getInt   (COL_RAISE_DOWN_SALARY_BASIS )); // 昇降給差の月額
		dto.setExpectedDate        (rs.getDate  (COL_EXPECTED_DATE           )); // 適用年月
		dto.setCalcResult          (rs.getInt   (COL_CALC_RESULT             )); // 計算結果

		mappingCommonInfo(dto);
		return dto;
	}

	/**
	 * 全データ取得
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public List<CdSocInsuranceDto> findAll() throws SQLException, IllegalAccessException, NoSuchFieldException {
		try {
			prepareStatement(getSelectQuery(getClass()) + getOrderForKey(getClass()));
			executeQuery();
			List<CdSocInsuranceDto> all = mappingAll();
			return all;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	/**
	 * キーによるデータ取得
	 * @param kCode 社員コード(検索キー)
	 * @param calcDate 計算年月(検索キー)
	 * @return CdSocInsuranceDto 検索結果(DTO)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public CdSocInsuranceDto findForKey(
			String kCode,
			Date   calcDate
	) throws SQLException, IllegalAccessException, NoSuchFieldException {
		try {
			index = 1;
			prepareStatement(getSelectQuery(getClass()) + getConditionForKey(getClass()));
			setParam(index++, kCode);
			setParam(index++, calcDate);
			executeQuery();
			CdSocInsuranceDto dto = null;
			if (rs.next()) {
				dto = mapping();
			}
			return dto;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}

	/**
	 * キーによる行ロック取得
	 * @param kCode 社員コード(検索キー)
	 * @param calcDate 計算年月(検索キー)
	 * @return CdSocInsuranceDto 検索結果(DTO)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public CdSocInsuranceDto findForUpdate(
			String kCode,
			Date   calcDate
	) throws SQLException, IllegalAccessException, NoSuchFieldException {
		try {
			index = 1;
			prepareStatement(getSelectQuery(getClass()) + getConditionForKey(getClass()) + getForUpdate());
			setParam(index++, kCode);
			setParam(index++, calcDate);
			executeQuery();
			CdSocInsuranceDto dto = null;
			if (rs.next()) {
				dto = mapping();
			}
			return dto;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	/**
	 * 条件文によるデータ取得
	 * @param calcDate 検索計算年月
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public List<CdSocInsuranceDto> findForCondition(
			Date calcDate
	) throws SQLException, IllegalAccessException, NoSuchFieldException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append("WHERE ");
		sb.append(COL_CALC_DATE);
		sb.append(" = ? ");
		try {
			prepareStatement(getSelectQuery(getClass()) + sb.toString() + getOrderForKey(getClass()));
			setParam(index++, calcDate);
			executeQuery();
			List<CdSocInsuranceDto> list = mappingAll();
			return list;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	/**
	 * 条件文によるデータ削除
	 * @param calcDate 検索計算年月
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException
	 */
	public int deleteForCondition(
			Date calcDate
	) throws SQLException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append("DELETE FROM ");
		sb.append(TABLE + " ");
		sb.append("WHERE ");
		sb.append(COL_CALC_DATE);
		sb.append(" = ? ");
		try {
			prepareStatement(sb.toString());
			setParam(index++, calcDate);
			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 insert(CdSocInsuranceDto 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
	 * @param needLog ログ出力要否
	 * @return int 挿入結果(件数)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 * @throws MospException
	 */
	public int insert(CdSocInsuranceDto dto, boolean needLog) throws SQLException, IllegalAccessException, NoSuchFieldException, MospException {
		try {
			index = 1;
			prepareStatement(getInsertQuery(getClass()));
			setParams(dto, true);
			executeUpdate(needLog);
			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(CdSocInsuranceDto dto) throws SQLException, IllegalAccessException, NoSuchFieldException, MospException {
		try {
			index = 1;
			prepareStatement(getUpdateQuery(getClass()));
			setParams(dto, false);
			setParam(index++, dto.getKCode());
			setParam(index++, dto.getCalcDate());
			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(CdSocInsuranceDto dto) throws SQLException, IllegalAccessException, NoSuchFieldException, MospException {
		try {
			index = 1;
			prepareStatement(getDeleteQuery(getClass()));
			setParam(index++, dto.getKCode());
			setParam(index++, dto.getCalcDate());
			executeUpdate();
			chkDelete(1);
			return cnt;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	/**
	 * 条件文によるデータ取得(人事マスタ保守-社員一覧用)
	 * @param kCode          検索社員コード
	 * @param kName          検索社員名
	 * @param kKana          検索ﾌﾘｶﾞﾅ
	 * @param stationCode    検索所属コード
	 * @param positionCode   検索職位コード
	 * @param retirementFlag 検索退職フラグ
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException 
	 */
	public List<CdSocInsuranceDto> findForSocInsList(
			String clacResult,
			String kCode,
			String kName,
			String stationCode,
			Date   calcdDate
	) throws SQLException, IllegalAccessException, NoSuchFieldException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append("WHERE ");
		sb.append(COL_CALC_DATE + " =  ? ");
		if (
				kCode        != null && !kCode.equals       ("") ||
				kName        != null && !kName.equals       ("") ||
				stationCode  != null && !stationCode.equals ("") ||
				clacResult   != null && !clacResult.equals  ("")
			) {
				sb.append(" AND ");
		}		
		if (kCode != null && !kCode.equals("")) {
			sb.append(COL_K_CODE + " =  ? ");
			if (
					kName        != null && !kName.equals       ("") ||
					stationCode  != null && !stationCode.equals ("") ||
					clacResult   != null && !clacResult.equals  ("")
				) {
				sb.append("AND ");
			}		
		}
		if (kName != null && !kName.equals("")) {
			sb.append(COL_K_NAME + " =  ? ");
			if (
					
					stationCode  != null && !stationCode.equals ("") ||
					clacResult   != null && !clacResult.equals  ("")
				) {
				sb.append("AND ");
			}		
		}
		if (stationCode != null && !stationCode.equals("")) {
			sb.append(COL_SCODE + " =  ? ");
			if (
					clacResult   != null && !clacResult.equals  ("")
				) {
				sb.append("AND ");
			}		
		}		
		if (clacResult   != null && !clacResult.equals  ("")) {
			sb.append(COL_CALC_RESULT + " =  ? ");
		}
		try {
			prepareStatement(getSelectQuery(getClass()) + sb.toString() + getOrderForKey(getClass()));
			setParam(index++, calcdDate);

			if (kCode != null && !kCode.equals("")) {
				setParam(index++, kCode);
			}
			if (kName != null && !kName.equals("")) {
				setParam(index++, kName);
			}
			if (stationCode != null && !stationCode.equals("")) {
				setParam(index++, stationCode);
			}		
			if (clacResult != null && !clacResult.equals("")) {
				setParam(index++, clacResult);
			}
			// 実行
			executeQuery();
			List<CdSocInsuranceDto> list = mappingAll();
			return list;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}

	/**
	 * パラメータ設定
	 * @param dto 更新対象DTO
	 * @param isInsert 挿入文フラグ(true：挿入文、false：更新文)
	 * @throws SQLException
	 */
	private void setParams(CdSocInsuranceDto dto, boolean isInsert) throws SQLException {
		setParam(index++, dto.getKCode               ()); // 社員コード
		setParam(index++, dto.getKName               ()); // 社員氏名
		setParam(index++, dto.getSCode               ()); // 所属コード
		setParam(index++, dto.getCalcDate            ()); // 計算年月
		setParam(index++, dto.getPayBasicMonth1      ()); // 支払基礎日数1(月)
		setParam(index++, dto.getPayBasicMonth2      ()); // 支払基礎日数2(月)
		setParam(index++, dto.getPayBasicMonth3      ()); // 支払基礎日数3(月)
		setParam(index++, dto.getPayBasicDay1        ()); // 支払基礎日数1(日)
		setParam(index++, dto.getPayBasicDay2        ()); // 支払基礎日数2(日)
		setParam(index++, dto.getPayBasicDay3        ()); // 支払基礎日数3(日)
		setParam(index++, dto.getPayAmount1          ()); // 金銭の額1
		setParam(index++, dto.getPayAmount2          ()); // 金銭の額2
		setParam(index++, dto.getPayAmount3          ()); // 金銭の額3
		setParam(index++, dto.getGoodsAmount1        ()); // 現物の額1
		setParam(index++, dto.getGoodsAmount2        ()); // 現物の額2
		setParam(index++, dto.getGoodsAmount3        ()); // 現物の額3
		setParam(index++, dto.getRewardTotal1        ()); // 報酬合計1
		setParam(index++, dto.getRewardTotal2        ()); // 報酬合計2
		setParam(index++, dto.getRewardTotal3        ()); // 報酬合計3
		setParam(index++, dto.getRewardTotalSum      ()); // 報酬総額
		setParam(index++, dto.getAverageThreeMonths  ()); // ３ヶ月平均
		setParam(index++, dto.getEditAverage         ()); // 修正平均
		setParam(index++, dto.getHealthBefGrade      ()); // 健保従前等級
		setParam(index++, dto.getHealthBefPremium    ()); // 健保従前保険料
		setParam(index++, dto.getPensionBefGrade     ()); // 厚年従前等級
		setParam(index++, dto.getPensionBefPremium   ()); // 厚年従前保険料
		setParam(index++, dto.getHealthDecGrade      ()); // 健保決定等級
		setParam(index++, dto.getHealthDecPremium    ()); // 健保決定保険料
		setParam(index++, dto.getPensionDecGrade     ()); // 厚年決定等級
		setParam(index++, dto.getPensionDecPremium   ()); // 厚年決定保険料
		setParam(index++, dto.getRetroactivePayment  ()); // 遡及支払額
		setParam(index++, dto.getRaiseDownSalaryDate ()); // 昇降給年月
		setParam(index++, dto.getRaiseDownSalaryBasis()); // 昇降給差の月額
		setParam(index++, dto.getExpectedDate        ()); // 適用年月
		setParam(index++, dto.getCalcResult          ()); // 計算結果

		setCommonParams(isInsert);
	}

}
