/*
 * 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.Date;
import java.util.List;

import jp.mosp.common.part.DatePart;
import jp.mosp.human.dao.MKihonDao;
import jp.mosp.payroll.base.PayrollConst;
import jp.mosp.payroll.base.PayrollDao;
import jp.mosp.payroll.dto.AdjustmentDto;

public class AdjustmentDao extends PayrollDao {
	
	// 列名
	public static final String COL_K_CODE_KIHON    = MKihonDao      .TABLE + "." + MKihonDao      .COL_KCODE         ;	// 社員コード
	public static final String COL_K_CODE_INC_TAX  = CmIncomeTaxDao  .TABLE + "." + CmIncomeTaxDao  .COL_K_CODE         ;	// 社員コード
	public static final String COL_K_CODE_MID_ENT  = CmMidwayEnterDao.TABLE + "." + CmMidwayEnterDao.COL_K_CODE         ;	// 社員コード
	public static final String COL_K_CODE_ADJUST   = CdAdjustmentDao .TABLE + "." + CdAdjustmentDao .COL_K_CODE         ;	// 社員コード
	
	public static final String COL_K_NAME          = MKihonDao.TABLE + "." + MKihonDao.COL_SIMEI         ;	// 社員氏名
	public static final String COL_K_KANA          = MKihonDao.TABLE + "." + MKihonDao.COL_FURIGANA         ;	// 社員ﾌﾘｶﾞﾅ
	public static final String COL_STATION_CODE    = MKihonDao.TABLE + "." + MKihonDao.COL_SCODE   ;	// 所属コード
	public static final String COL_POSITION_CODE   = MKihonDao.TABLE + "." + MKihonDao.COL_YCODE  ;	// 職位コード
//	public static final String COL_EMPLOYMENT_TYPE = MKihonDao.TABLE + "." + MKihonDao.COL_EMPLOYMENT_TYPE;	// 雇用区分
	public static final String COL_ENTRANCE_DATE   = MKihonDao.TABLE + "." + MKihonDao.COL_NYDATE  ;	// 入社日
	public static final String COL_RETIREMENT_DATE = MKihonDao.TABLE + "." + MKihonDao.COL_TSDATE;	// 退社日
	
	public static final String COL_INC_EXPECTED_DATE  = CmIncomeTaxDao.TABLE + "." + CmIncomeTaxDao.COL_INC_EXPECTED_DATE ;	// 所得税適用年
	public static final String COL_INCOME_TAX_TYPE    = CmIncomeTaxDao.TABLE + "." + CmIncomeTaxDao.COL_INCOME_TAX_TYPE   ;	// 課税区分
	public static final String COL_ADJUSTMENT_TYPE    = CmIncomeTaxDao.TABLE + "." + CmIncomeTaxDao.COL_ADJUSTMENT_TYPE   ;	// 年末調整区分
	public static final String COL_SELF_WIDOW_TYPE    = CmIncomeTaxDao.TABLE + "." + CmIncomeTaxDao.COL_SELF_WIDOW_TYPE   ;	// 本人寡婦区分
	public static final String COL_SELF_HANDICAP_TYPE = CmIncomeTaxDao.TABLE + "." + CmIncomeTaxDao.COL_SELF_HANDICAP_TYPE;	// 本人障害区分
	public static final String COL_SELF_STUDENT_TYPE  = CmIncomeTaxDao.TABLE + "." + CmIncomeTaxDao.COL_SELF_STUDENT_TYPE ;	// 本人学生区分
	public static final String COL_SPOUSE_INTEND_TYPE = CmIncomeTaxDao.TABLE + "." + CmIncomeTaxDao.COL_SPOUSE_INTEND_TYPE;	// 配偶者区分
	public static final String COL_GENERAL_DEPENDENT  = CmIncomeTaxDao.TABLE + "." + CmIncomeTaxDao.COL_GENERAL_DEPENDENT ;	// 一般扶養親族
	public static final String COL_SPECIFIC_DEPENDENT = CmIncomeTaxDao.TABLE + "." + CmIncomeTaxDao.COL_SPECIFIC_DEPENDENT;	// 特定扶養親族
	public static final String COL_ELDERLY_DEPENDENT  = CmIncomeTaxDao.TABLE + "." + CmIncomeTaxDao.COL_ELDERLY_DEPENDENT ;	// 老人扶養親族
	public static final String COL_LIVE_WITH_ELD_REL  = CmIncomeTaxDao.TABLE + "." + CmIncomeTaxDao.COL_LIVE_WITH_ELD_REL ;	// 同居老親等
	public static final String COL_LIVE_WITH_HANDI_GE = CmIncomeTaxDao.TABLE + "." + CmIncomeTaxDao.COL_LIVE_WITH_HANDI_GE;	// 同居特障一般
	public static final String COL_LIVE_WITH_HANDI_SP = CmIncomeTaxDao.TABLE + "." + CmIncomeTaxDao.COL_LIVE_WITH_HANDI_SP;	// 同居特障特定
	public static final String COL_LIVE_WITH_HANDI_EL = CmIncomeTaxDao.TABLE + "." + CmIncomeTaxDao.COL_LIVE_WITH_HANDI_EL;	// 同居特障老人
	public static final String COL_LIVE_WITH_HANDI_ER = CmIncomeTaxDao.TABLE + "." + CmIncomeTaxDao.COL_LIVE_WITH_HANDI_ER;	// 同居特障老親
	public static final String COL_GENERAL_HANDICAP   = CmIncomeTaxDao.TABLE + "." + CmIncomeTaxDao.COL_GENERAL_HANDICAP  ;	// 一般障害者
	public static final String COL_SPECIFIC_HANDICAP  = CmIncomeTaxDao.TABLE + "." + CmIncomeTaxDao.COL_SPECIFIC_HANDICAP ;	// 特別障害者
	public static final String COL_DEPENDENT_COUNT    = CmIncomeTaxDao.TABLE + "." + CmIncomeTaxDao.COL_DEPENDENT_COUNT   ;	// 扶養等の数
	
	public static final String COL_MIDWAY_TYPE      = CmMidwayEnterDao.TABLE + "." + CmMidwayEnterDao.COL_MIDWAY_TYPE     ;	// 中途入社区分
	public static final String COL_FORMER_INCOME    = CmMidwayEnterDao.TABLE + "." + CmMidwayEnterDao.COL_FORMER_INCOME   ;	// 前勤務先収入金額
	public static final String COL_FORMER_INSURANCE = CmMidwayEnterDao.TABLE + "." + CmMidwayEnterDao.COL_FORMER_INSURANCE;	// 前勤務先社会保険額
	public static final String COL_FORMER_TAX       = CmMidwayEnterDao.TABLE + "." + CmMidwayEnterDao.COL_FORMER_TAX      ;	// 前勤務先所得税額
	
	public static final String COL_ADJUST_YEAR          = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_ADJUST_YEAR         ;	// 調整年
	public static final String COL_ADJUST_STATUS        = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_ADJUST_STATUS       ;	// 年末調整計算状態
	public static final String COL_GENERAL_LIFE_INS     = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_GENERAL_LIFE_INS    ;	// 一般の生命保険料
	public static final String COL_PRIVATE_PENSION      = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_PRIVATE_PENSION     ;	// 個人年金保険料
	public static final String COL_LIFE_INS_DEDUCTION   = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_LIFE_INS_DEDUCTION  ;	// 生命保険料控除額
	public static final String COL_EARTHQUAKE_INS       = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_EARTHQUAKE_INS      ;	// 地震保険料
	public static final String COL_LONG_TERM_DAMAGE_INS = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_LONG_TERM_DAMAGE_INS;	// 旧長期損害保険料
	public static final String COL_DAMAGE_INS_DEDUCTION = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_DAMAGE_INS_DEDUCTION;	// 損害保険料控除額
	public static final String COL_SOCIAL_INS_DEDUCTION = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_SOCIAL_INS_DEDUCTION;	// 社会保険料控除額
	public static final String COL_MUTUAL_AID_DEDUCTION = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_MUTUAL_AID_DEDUCTION;	// 小規模企業共済等掛金控除額
	public static final String COL_SPOUSE_SUM_INCOME    = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_SPOUSE_SUM_INCOME   ;	// 配偶者の本年中の合計所得金額の見積額
	public static final String COL_SPOUSE_SP_DEDUCTION  = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_SPOUSE_SP_DEDUCTION ;	// 配偶者特別控除額
	public static final String COL_HOUSE_LOAN_DEDUCTION = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_HOUSE_LOAN_DEDUCTION;	// 住宅借入金等特別控除額
	public static final String COL_ALLOWANCE_ADJUSTMENT = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_ALLOWANCE_ADJUSTMENT;	// 支給調整額
	public static final String COL_TAX_FREE_ADJUSTMENT  = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_TAX_FREE_ADJUSTMENT ;	// 非課税調整額
	public static final String COL_SOC_INS_ADJUSTMENT   = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_SOC_INS_ADJUSTMENT  ;	// 社会保険調整額
	public static final String COL_TAX_INC_ADJUSTMENT   = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_TAX_INC_ADJUSTMENT  ;	// 所得税調整額
	public static final String COL_WITHHOLD_SLIP_NOTES  = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_WITHHOLD_SLIP_NOTES ;	// 源泉徴収票摘要欄
	public static final String COL_PAYMENT_ALLOWANCE    = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_PAYMENT_ALLOWANCE   ;	// 給与、手当等金額
	public static final String COL_PAYMENT_INCOME_TAX   = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_PAYMENT_INCOME_TAX  ;	// 給与、手当等税額
	public static final String COL_BONUS_ALLOWANCE      = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_BONUS_ALLOWANCE     ;	// 賞与等金額
	public static final String COL_BONUS_INCOME_TAX     = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_BONUS_INCOME_TAX    ;	// 賞与等税額
	public static final String COL_MIDWAY_ADJUST_INCOME = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_MIDWAY_ADJUST_INCOME;	// 中途、調整収入金額
	public static final String COL_MIDWAY_ADJUST_TAX    = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_MIDWAY_ADJUST_TAX   ;	// 中途、調整収入税額
	public static final String COL_AFTER_DEDUCT_INCOME  = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_AFTER_DEDUCT_INCOME ;	// 給与所得控除後の給与等の金額
	public static final String COL_PAY_INS_DEDUCTION    = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_PAY_INS_DEDUCTION   ;	// 社会保険料等給与等からの控除分
	public static final String COL_DEPENDENTS_DEDUCTION = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_DEPENDENTS_DEDUCTION;	// 配偶者控除額、扶養控除額、基礎控除額及び障害者等の控除額の合計額
	public static final String COL_CALC_TAXED_INCOME    = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_CALC_TAXED_INCOME   ;	// 差引課税給与所得金額
	public static final String COL_CALC_INCOME_TAX      = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_CALC_INCOME_TAX     ;	// 算出年税額
	public static final String COL_ADJUSTED_INCOME_TAX  = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_ADJUSTED_INCOME_TAX ;	// 年調年税額
	public static final String COL_DEDUCTED_INCOME_TAX  = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_DEDUCTED_INCOME_TAX ;	// 年調定率控除額税額
	public static final String COL_FINAL_INCOME_TAX     = CdAdjustmentDao.TABLE + "." + CdAdjustmentDao.COL_FINAL_INCOME_TAX    ;	// 年税額
	
	/**
	 * コンストラクタ
	 */
	public AdjustmentDao() {
		super();
		INSERT_DATE = "";
		INSERT_USER = "";
		UPDATE_DATE = "";
		UPDATE_USER = "";
	}
	
	/**
	 * 検索結果取得(DTOのList)
	 * @return List 検索リスト
	 * @throws SQLException
	 */
	private List<AdjustmentDto> mappingAll() throws SQLException {
		List<AdjustmentDto> all = new ArrayList<AdjustmentDto>();
		while (rs.next()) {
			all.add(mapping());
		}
		return all;
	}
	
	/**
	 * 検索結果取得(DTO)
	 * @return AdjustmentListDto 年末調整レコード
	 * @throws SQLException
	 */
	private AdjustmentDto mapping() throws SQLException {
		AdjustmentDto dto = new AdjustmentDto();
		dto.setKCode          (rs.getString(COL_K_CODE_KIHON   ));	// 社員コード
		
		dto.setKName          (rs.getString(COL_K_NAME         ));	// 社員氏名
		dto.setKKana          (rs.getString(COL_K_KANA         ));	// 社員ﾌﾘｶﾞﾅ
		dto.setStationCode    (rs.getString(COL_STATION_CODE   ));	// 所属コード
		dto.setPositionCode   (rs.getString(COL_POSITION_CODE  ));	// 職位コード
//		dto.setEmploymentType (rs.getString(COL_EMPLOYMENT_TYPE));	// 雇用区分
//		dto.setEntranceDate   (rs.getDate  (COL_ENTRANCE_DATE  ));	// 入社日
//		dto.setRetirementDate (rs.getDate  (COL_RETIREMENT_DATE));	// 退社日
		dto.setEntranceDate   (rs.getInt  (COL_ENTRANCE_DATE  ));	// 入社日
		dto.setRetirementDate (rs.getInt  (COL_RETIREMENT_DATE));	// 退社日
		
		dto.setIncExpectedDate  (rs.getDate  (COL_INC_EXPECTED_DATE  ));	// 所得税適用年
		dto.setIncomeTaxType    (rs.getString(COL_INCOME_TAX_TYPE    ));	// 課税区分
		dto.setAdjustmentType   (rs.getString(COL_ADJUSTMENT_TYPE    ));	// 年末調整区分
		dto.setSelfWidowType    (rs.getString(COL_SELF_WIDOW_TYPE    ));	// 本人寡婦区分
		dto.setSelfHandicapType (rs.getString(COL_SELF_HANDICAP_TYPE ));	// 本人障害区分
		dto.setSelfStudentType  (rs.getString(COL_SELF_STUDENT_TYPE  ));	// 本人学生区分
		dto.setSpouseIntendType (rs.getString(COL_SPOUSE_INTEND_TYPE ));	// 配偶者区分
		dto.setGeneralDependent (rs.getInt   (COL_GENERAL_DEPENDENT  ));	// 一般扶養親族
		dto.setSpecificDependent(rs.getInt   (COL_SPECIFIC_DEPENDENT ));	// 特定扶養親族
		dto.setElderlyDependent (rs.getInt   (COL_ELDERLY_DEPENDENT  ));	// 老人扶養親族
		dto.setLiveWithEldRel   (rs.getInt   (COL_LIVE_WITH_ELD_REL  ));	// 同居老親等
		dto.setLiveWithHandiGe  (rs.getInt   (COL_LIVE_WITH_HANDI_GE ));	// 同居特障一般
		dto.setLiveWithHandiSp  (rs.getInt   (COL_LIVE_WITH_HANDI_SP ));	// 同居特障特定
		dto.setLiveWithHandiEl  (rs.getInt   (COL_LIVE_WITH_HANDI_EL ));	// 同居特障老人
		dto.setLiveWithHandiEr  (rs.getInt   (COL_LIVE_WITH_HANDI_ER ));	// 同居特障老親
		dto.setGeneralHandicap  (rs.getInt   (COL_GENERAL_HANDICAP   ));	// 一般障害者
		dto.setSpecificHandicap (rs.getInt   (COL_SPECIFIC_HANDICAP  ));	// 特別障害者
		dto.setDependentCount   (rs.getInt   (COL_DEPENDENT_COUNT    ));	// 扶養等の数
		
		dto.setMidwayType     (rs.getString(COL_MIDWAY_TYPE     ));	// 中途入社区分
		dto.setFormerIncome   (rs.getInt   (COL_FORMER_INCOME   ));	// 前勤務先収入金額
		dto.setFormerInsurance(rs.getInt   (COL_FORMER_INSURANCE));	// 前勤務先社会保険額
		dto.setFormerTax      (rs.getInt   (COL_FORMER_TAX      ));	// 前勤務先所得税額
		
		dto.setAdjustYear         (rs.getInt   (COL_ADJUST_YEAR  ));	// 調整年
		dto.setAdjustStatus       (rs.getString(COL_ADJUST_STATUS));	// 年末調整計算状態
		dto.setGeneralLifeIns     (rs.getInt   (COL_GENERAL_LIFE_INS    ));	// 一般の生命保険料
		dto.setPrivatePension     (rs.getInt   (COL_PRIVATE_PENSION     ));	// 個人年金保険料
		dto.setLifeInsDeduction   (rs.getInt   (COL_LIFE_INS_DEDUCTION  ));	// 生命保険料控除額
		dto.setEarthquakeIns      (rs.getInt   (COL_EARTHQUAKE_INS      ));	// 地震保険料
		dto.setLongTermDamageIns  (rs.getInt   (COL_LONG_TERM_DAMAGE_INS));	// 旧長期損害保険料
		dto.setDamageInsDeduction (rs.getInt   (COL_DAMAGE_INS_DEDUCTION));	// 損害保険料控除額
		dto.setSocialInsDeduction (rs.getInt   (COL_SOCIAL_INS_DEDUCTION));	// 社会保険料控除額
		dto.setMutualAidDeduction (rs.getInt   (COL_MUTUAL_AID_DEDUCTION));	// 小規模企業共済等掛金控除額
		dto.setSpouseSumIncome    (rs.getInt   (COL_SPOUSE_SUM_INCOME   ));	// 配偶者の本年中の合計所得金額の見積額
		dto.setSpouseSpDeduction  (rs.getInt   (COL_SPOUSE_SP_DEDUCTION ));	// 配偶者特別控除額
		dto.setHouseLoanDeduction (rs.getInt   (COL_HOUSE_LOAN_DEDUCTION));	// 住宅借入金等特別控除額
		dto.setAllowanceAdjustment(rs.getInt   (COL_ALLOWANCE_ADJUSTMENT));	// 支給調整額
		dto.setTaxFreeAdjustment  (rs.getInt   (COL_TAX_FREE_ADJUSTMENT ));	// 非課税調整額
		dto.setSocInsAdjustment   (rs.getInt   (COL_SOC_INS_ADJUSTMENT  ));	// 社会保険調整額
		dto.setTaxIncAdjustment   (rs.getInt   (COL_TAX_INC_ADJUSTMENT  ));	// 所得税調整額
		dto.setWithholdSlipNotes  (rs.getString(COL_WITHHOLD_SLIP_NOTES ));	// 源泉徴収票摘要欄
		dto.setPaymentAllowance   (rs.getInt   (COL_PAYMENT_ALLOWANCE   ));	// 給与、手当等金額
		dto.setPaymentIncomeTax   (rs.getInt   (COL_PAYMENT_INCOME_TAX  ));	// 給与、手当等税額
		dto.setBonusAllowance     (rs.getInt   (COL_BONUS_ALLOWANCE     ));	// 賞与等金額
		dto.setBonusIncomeTax     (rs.getInt   (COL_BONUS_INCOME_TAX    ));	// 賞与等税額
		dto.setMidwayAdjustIncome (rs.getInt   (COL_MIDWAY_ADJUST_INCOME));	// 中途、調整収入金額
		dto.setMidwayAdjustTax    (rs.getInt   (COL_MIDWAY_ADJUST_TAX   ));	// 中途、調整収入税額
		dto.setAfterDeductIncome  (rs.getInt   (COL_AFTER_DEDUCT_INCOME ));	// 給与所得控除後の給与等の金額
		dto.setPayInsDeduction    (rs.getInt   (COL_PAY_INS_DEDUCTION   ));	// 社会保険料等給与等からの控除分
		dto.setDependentsDeduction(rs.getInt   (COL_DEPENDENTS_DEDUCTION));	// 配偶者控除額、扶養控除額、基礎控除額及び障害者等の控除額の合計額
		dto.setCalcTaxedIncome    (rs.getInt   (COL_CALC_TAXED_INCOME   ));	// 差引課税給与所得金額
		dto.setCalcIncomeTax      (rs.getInt   (COL_CALC_INCOME_TAX     ));	// 算出年税額
		dto.setAdjustedIncomeTax  (rs.getInt   (COL_ADJUSTED_INCOME_TAX ));	// 年調年税額
		dto.setDeductedIncomeTax  (rs.getInt   (COL_DEDUCTED_INCOME_TAX ));	// 年調定率控除額税額
		dto.setFinalIncomeTax     (rs.getInt   (COL_FINAL_INCOME_TAX    ));	// 年税額
		
		return dto;
	}
	
	/**
	 * 条件文による年末調整データ取得
	 * @param adjustYear 調整年
	 * @param lastDate   調整年最終日
	 * @param kCode      社員コード
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException
	 */
	public List<AdjustmentDto> findForCondition (
			int    adjustYear    ,
			Date   lastDate      ,
			String kCode         ,
			String kName         ,
			String kKana         ,
			String retirementFlag,
			String positionCode  ,
			String adjustmentType,
			String midwayType    ,
			String adjustStatus  
	) throws IllegalAccessException, SQLException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append("WHERE ");
		sb.append(COL_K_CODE_KIHON + " LIKE ? ");
		sb.append("AND ");
		sb.append(COL_K_NAME + " LIKE ? ");
		sb.append("AND ");
		sb.append(COL_K_KANA + " LIKE ? ");
		if (retirementFlag != null && retirementFlag.equals(String.valueOf(PayrollConst.RETIREMENT_FLAG_ON))) {
			sb.append("AND ");
			sb.append(COL_RETIREMENT_DATE + " <= ? ");
		} else if (retirementFlag != null && retirementFlag.equals(String.valueOf(PayrollConst.RETIREMENT_FLAG_OFF))) {
			sb.append("AND ");
			sb.append("( ");
			sb.append(COL_RETIREMENT_DATE + " = 0 ");
			sb.append("OR ");
			sb.append(COL_RETIREMENT_DATE + " > ? ");
			sb.append(") ");
		}
		if (positionCode != null && !positionCode.equals("")) {
			sb.append("AND ");
			sb.append(COL_POSITION_CODE + " = ? ");
		}
		if (adjustmentType != null && !adjustmentType.equals("")) {
			sb.append("AND ");
			sb.append(COL_ADJUSTMENT_TYPE + " = ? ");
		}
		if (midwayType != null && !midwayType.equals("")) {
			sb.append("AND ");
			sb.append(COL_MIDWAY_TYPE + " = ? ");
		}
		if (adjustStatus != null && adjustStatus.equals(PayrollConst.ADJU_STATUS_NON)) {
			sb.append("AND ");
			sb.append(COL_ADJUST_STATUS + " IS NULL ");
		} else if (adjustStatus != null && !adjustStatus.equals("")) {
			sb.append("AND ");
			sb.append(COL_ADJUST_STATUS + " = ? ");
		}
		try {
			prepareStatement(getSelectStatement(getClass())
					+ getAdjustmentTable() + sb.toString());
			setParam(index++, DatePart.convDateToInteger(lastDate));
			setParam(index++, lastDate   );
			setParam(index++, adjustYear );
			setParam(index++, "%" + kCode + "%");
			setParam(index++, "%" + kName + "%");
			setParam(index++, "%" + kKana + "%");
			if (retirementFlag != null && !retirementFlag.equals("")) {
				setParam(index++, DatePart.convDateToInteger(lastDate));
			}
			if (positionCode != null && !positionCode.equals("")) {
				setParam(index++, positionCode);
			}
			if (adjustmentType != null && !adjustmentType.equals("")) {
				setParam(index++, adjustmentType);
			}
			if (midwayType != null && !midwayType.equals("")) {
				setParam(index++, midwayType);
			}
			if (adjustStatus != null && !adjustStatus.equals("") &&
					!adjustStatus.equals(PayrollConst.ADJU_STATUS_NON)) {
				setParam(index++, adjustStatus);
			}
			executeQuery();
			return mappingAll();
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	/**
	 * 条件文による年末調整データ取得
	 * @param adjustDate 調整年
	 * @param endDate    調整年最終日
	 * @param kCode      社員コード
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException
	 */
	public AdjustmentDto findForEmployee (
			Date   adjustDate,
			Date   endDate   ,
			String kCode
	) throws IllegalAccessException, SQLException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append("WHERE ");
		sb.append(COL_K_CODE_KIHON + " = ? ");
		try {
			prepareStatement(getSelectStatement(getClass())
					+ getAdjustmentTable() + sb.toString());
			setParam(index++, endDate   );
			setParam(index++, adjustDate);
			setParam(index++, adjustDate);
			setParam(index++, kCode);
			executeQuery();
			AdjustmentDto dto = null;
			if (rs.next()) {
				dto = mapping();
			}
			return dto;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	/**
	 * 年末調整データビューSQL取得
	 * @return 年末調整データビュー取得SQL文字列
	 * @throws IllegalAccessException
	 */
	public String getAdjustmentTable() throws IllegalAccessException {
		MKihonPayrollDao      KihonPayrollDao      = new MKihonPayrollDao();
		CmIncomeTaxDao  cmIncomeTaxDao  = new CmIncomeTaxDao ();
		CdAdjustmentDao cdAdjustmentDao = new CdAdjustmentDao();
		StringBuffer sb = new StringBuffer();
		sb.append(" ");
		sb.append("FROM ");
		sb.append("(");
		sb.append( "(");
		sb.append(  "(");
		sb.append(   KihonPayrollDao.getHistoryTable() + " ");
		sb.append(   "LEFT JOIN ");
		sb.append(   cmIncomeTaxDao.getHistoryTableForDate() + " ");
		sb.append(   "ON " + COL_K_CODE_KIHON + " = " + COL_K_CODE_INC_TAX);
		sb.append(  ") ");
		sb.append(  "LEFT JOIN ");
		sb.append(  CmMidwayEnterDao.TABLE + " ");
		sb.append(  "ON " + COL_K_CODE_KIHON + " = " + COL_K_CODE_MID_ENT);
		sb.append( ") ");
		sb.append( "LEFT JOIN ");
		sb.append( cdAdjustmentDao.getTableForDate() + " ");
		sb.append( "ON " + COL_K_CODE_KIHON + " = " + COL_K_CODE_ADJUST);
		sb.append(") ");
		return sb.toString();
	}

}
