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.CtRetireDetailDto;

public class CtRetireDetailDao extends PayrollDao {
	
	// 僥乕僽儖柤媦傃僉乕楍柤
	public static final String	TABLE				= "CT_RETIRE_DETAIL";
	public static final String	KEY_1				= "CITY_CODE";			// 巗挰懞僐乕僪
	public static final String	KEY_2				= "DETAIL_MONTH";		// 戅怑柧嵶擭寧
																			
	// 楍柤
	public static final String	COL_CITY_CODE		= "CITY_CODE";			// 巗挰懞僐乕僪
	public static final String	COL_DETAIL_MONTH	= "DETAIL_MONTH";		// 戅怑柧嵶擭寧
	public static final String	COL_CITY_CHANGES	= "CITY_CHANGES";		// 堎摦桳柍
	public static final String	COL_RETIRE_COUNT	= "RETIRE_COUNT";		// 戅怑恖堳
	public static final String	COL_CITY_PAY_AMOUNT	= "CITY_PAY_AMOUNT";	// 巟暐嬥妟
	public static final String	COL_CITY_TAX		= "CITY_TAX";			// 巗挰懞惻
	public static final String	COL_PREFECTURE_TAX	= "PREFECTURE_TAX";	// 摴晎導惻
																			
	
	/**
	 * 僐儞僗僩儔僋僞
	 */
	public CtRetireDetailDao() {
		super();
	}
	
	/**
	 * 専嶕寢壥庢摼(DTO偺List)
	 * @return List 専嶕儕僗僩
	 * @throws SQLException
	 */
	private List<CtRetireDetailDto> mappingAll() throws SQLException {
		List<CtRetireDetailDto> all = new ArrayList<CtRetireDetailDto>();
		while (rs.next()) {
			all.add(mapping());
		}
		return all;
	}
	
	/**
	 * 専嶕寢壥庢摼(DTO)
	 * @return CmCityDto UD_PAYMENT儗僐乕僪
	 * @throws SQLException
	 */
	private CtRetireDetailDto mapping() throws SQLException {
		CtRetireDetailDto dto = new CtRetireDetailDto();
		dto.setCityCode(rs.getString(COL_CITY_CODE)); // 巗挰懞僐乕僪
		dto.setDetailMonth(rs.getDate(COL_DETAIL_MONTH)); // 戅怑柧嵶擭寧
		dto.setCityChanges(rs.getString(COL_CITY_CHANGES)); // 堎摦桳柍
		dto.setRetireCount(rs.getInt(COL_RETIRE_COUNT)); // 戅怑恖堳
		dto.setCityPayAmount(rs.getLong(COL_CITY_PAY_AMOUNT)); // 巟暐嬥妟
		dto.setCityTax(rs.getInt(COL_CITY_TAX)); // 巗挰懞惻
		dto.setPrefectureTax(rs.getInt(COL_PREFECTURE_TAX)); // 摴晎導惻
		mappingCommonInfo(dto);
		return dto;
	}
	
	/**
	 * 慡僨乕僞庢摼
	 * @return List 専嶕寢壥(DTO偺List)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public List<CtRetireDetailDto> findAll() throws SQLException, IllegalAccessException, NoSuchFieldException {
		try {
			prepareStatement(getSelectQuery(getClass()) + getOrderForKey(getClass()));
			executeQuery();
			List<CtRetireDetailDto> all = mappingAll();
			return all;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	/**
	 * 僉乕偵傛傞僨乕僞庢摼
	 * @param cityCode      巗嬫挰懞僐乕僪(専嶕僉乕)
	 * @param detailMonth   戅怑柧嵶擭寧(専嶕僉乕)
	 * @return CtRetireDetailDto 専嶕寢壥(DTO)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public CtRetireDetailDto findForKey(String cityCode, Date detailMonth) throws SQLException, IllegalAccessException,
			NoSuchFieldException {
		try {
			index = 1;
			prepareStatement(getSelectQuery(getClass()) + getConditionForKey(getClass()));
			setParam(index++, cityCode);
			setParam(index++, detailMonth);
			executeQuery();
			CtRetireDetailDto dto = null;
			if (rs.next()) {
				dto = mapping();
			}
			return dto;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	/**
	 * 僉乕偵傛傞峴儘僢僋庢摼
	 * @param cityCode      巗嬫挰懞僐乕僪(専嶕僉乕)
	 * @return CtRetireDetailDto 専嶕寢壥(DTO)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public CtRetireDetailDto findForUpdate(String cityCode, Date detailMonth) throws SQLException,
			IllegalAccessException, NoSuchFieldException {
		try {
			index = 1;
			prepareStatement(getSelectQuery(getClass()) + getConditionForKey(getClass()) + getForUpdate());
			setParam(index++, cityCode);
			setParam(index++, detailMonth);
			
			executeQuery();
			CtRetireDetailDto 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(CtRetireDetailDto 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(CtRetireDetailDto 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(CtRetireDetailDto dto) throws SQLException, IllegalAccessException, NoSuchFieldException,
			MospException {
		try {
			index = 1;
			prepareStatement(getUpdateQuery(getClass()));
			setParams(dto, false);
			setParam(index++, dto.getCityCode());
			setParam(index++, dto.getDetailMonth());
			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(CtRetireDetailDto dto) throws SQLException, IllegalAccessException, NoSuchFieldException,
			MospException {
		try {
			index = 1;
			prepareStatement(getDeleteQuery(getClass()));
			setParam(index++, dto.getCityCode());
			setParam(index++, dto.getDetailMonth());
			
			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(CtRetireDetailDto dto, boolean isInsert) throws SQLException {
		setParam(index++, dto.getCityCode()); // 巗挰懞僐乕僪
		setParam(index++, dto.getDetailMonth()); // 戅怑柧嵶擭寧
		setParam(index++, dto.getCityChanges()); // 堎摦桳柍
		setParam(index++, dto.getRetireCount()); // 戅怑恖堳
		setParam(index++, dto.getCityPayAmount()); // 巟暐嬥妟
		setParam(index++, dto.getCityTax()); // 巗挰懞惻
		setParam(index++, dto.getPrefectureTax()); // 摴晎導惻
		setCommonParams(isInsert);
	}
	
}
