package jp.mosp.payroll.dao;

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

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

public class CmCityDao extends PayrollDao {
	
	// テーブル名及びキー列名
	public static final String	TABLE					= "CM_CITY";
	public static final String	KEY_1					= "CITY_CODE";			// 市町村コード
																				
	// 列名
	public static final String	COL_CITY_CODE			= "CITY_CODE";			// 市町村コード
	public static final String	COL_CITY_NAME			= "CITY_NAME";			// 市町村名
	public static final String	COL_CITY_KANA			= "CITY_KANA";			// 市町村カナ
	public static final String	COL_CITY_ACCOUNT		= "CITY_ACCOUNT";		// 市町村口座番号
	public static final String	COL_CITY_ACCOUNT_NAME1	= "CITY_ACCOUNT_NAME1"; // 加入者1
	public static final String	COL_CITY_ACCOUNT_NAME2	= "CITY_ACCOUNT_NAME2"; // 加入者2
	public static final String	COL_DESIGNATED_NUMBER	= "DESIGNATED_NUMBER";	// 指定番号
	public static final String	COL_CO_BANK_CODE		= "CO_BANK_CODE";		// 会社銀行コード
	public static final String	COL_MASTER_OFFICE		= "MASTER_OFFICE";		// 取りまとめ局
	public static final String	COL_CITY_POSTAL_CODE	= "CITY_POSTAL_CODE";	// 市町村郵便番号
	public static final String	COL_CITY_ADDRESS1		= "CITY_ADDRESS1";		// 市町村住所1
	public static final String	COL_CITY_ADDRESS2		= "CITY_ADDRESS2";		// 市町村住所2
	public static final String	COL_CITY_OFFICE1		= "CITY_OFFICE1";		// 役場名1
	public static final String	COL_CITY_OFFICE2		= "CITY_OFFICE2";		// 役場名2
	public static final String	COL_CITY_PHONE			= "CITY_PHONE";		// 市町村電話番号
																				
	
	/**
	 * コンストラクタ
	 */
	public CmCityDao() {
		super();
	}
	
	/**
	 * 検索結果取得(DTOのList)
	 * @return List 検索リスト
	 * @throws SQLException
	 */
	private List<CmCityDto> mappingAll() throws SQLException {
		List<CmCityDto> all = new ArrayList<CmCityDto>();
		while (rs.next()) {
			all.add(mapping());
		}
		return all;
	}
	
	/**
	 * 検索結果取得(DTO)
	 * @return CmCityDto UD_PAYMENTレコード
	 * @throws SQLException
	 */
	private CmCityDto mapping() throws SQLException {
		CmCityDto dto = new CmCityDto();
		dto.setCityCode(rs.getString(COL_CITY_CODE)); // 市町村コード
		dto.setCityName(rs.getString(COL_CITY_NAME)); // 市町村名
		dto.setCityKana(rs.getString(COL_CITY_KANA)); // 市町村カナ
		dto.setCityAccount(rs.getString(COL_CITY_ACCOUNT)); // 市町村口座番号
		dto.setCityAccountName1(rs.getString(COL_CITY_ACCOUNT_NAME1)); // 加入者1
		dto.setCityAccountName2(rs.getString(COL_CITY_ACCOUNT_NAME2)); // 加入者2
		dto.setDesignatedNumber(rs.getString(COL_DESIGNATED_NUMBER)); // 指定番号
		dto.setCoBankCode(rs.getString(COL_CO_BANK_CODE)); // 会社銀行コード
		dto.setMasterOffice(rs.getString(COL_MASTER_OFFICE)); // 取りまとめ局
		dto.setCityPostalCode(rs.getString(COL_CITY_POSTAL_CODE)); // 市町村郵便番号
		dto.setCityAddress1(rs.getString(COL_CITY_ADDRESS1)); // 市町村住所1
		dto.setCityAddress2(rs.getString(COL_CITY_ADDRESS2)); // 市町村住所2
		dto.setCityOffice1(rs.getString(COL_CITY_OFFICE1)); // 役場名1
		dto.setCityOffice2(rs.getString(COL_CITY_OFFICE2)); // 役場名2
		dto.setCityPhone(rs.getString(COL_CITY_PHONE)); // 市町村電話番号
		mappingCommonInfo(dto);
		return dto;
	}
	
	/**
	 * 全データ取得
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public List<CmCityDto> findAll() throws SQLException, IllegalAccessException, NoSuchFieldException {
		try {
			prepareStatement(getSelectQuery(getClass()) + getOrderForKey(getClass()));
			executeQuery();
			List<CmCityDto> all = mappingAll();
			return all;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	/**
	 * キーによるデータ取得
	 * @param cityCode      市区町村コード(検索キー)
	 * @return CmCityDto 検索結果(DTO)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public CmCityDto findForKey(String cityCode) throws SQLException, IllegalAccessException, NoSuchFieldException {
		try {
			index = 1;
			prepareStatement(getSelectQuery(getClass()) + getConditionForKey(getClass()));
			setParam(index++, cityCode);
			executeQuery();
			CmCityDto dto = null;
			if (rs.next()) {
				dto = mapping();
			}
			return dto;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	/**
	 * キーによる行ロック取得
	 * @param cityCode      市区町村コード(検索キー)
	 * @return CmCityDto 検索結果(DTO)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public CmCityDto findForUpdate(String cityCode) throws SQLException, IllegalAccessException, NoSuchFieldException {
		try {
			index = 1;
			prepareStatement(getSelectQuery(getClass()) + getConditionForKey(getClass()) + getForUpdate());
			setParam(index++, cityCode);
			executeQuery();
			CmCityDto dto = null;
			if (rs.next()) {
				dto = mapping();
			}
			return dto;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	/**
	 * 条件文によるデータ取得
	 * @param cityCode         市町村コード(検索キー)
	 * @param cityName         市町村名(検索キー)
	 * @param cityKana         ﾌﾘｶﾞﾅ(検索キー)
	 * @param designatedNumber 整理番号(検索キー)
	 * @param coBankCode       会社銀行コード(検索キー)
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public List<CmCityDto> findForCondition(String cityCode, String cityName, String cityKana, String designatedNumber,
			String coBankCode

	) throws SQLException, IllegalAccessException, NoSuchFieldException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		if (cityCode != null && !cityCode.equals("") || cityName != null && !cityName.equals("") || cityKana != null
				&& !cityKana.equals("") || designatedNumber != null && !designatedNumber.equals("")
				|| coBankCode != null && !coBankCode.equals("")) {
			sb.append("WHERE ");
		}
		if (cityCode != null && !cityCode.equals("")) {
			sb.append(COL_CITY_CODE + "  like  ? ");
			if (cityName != null && !cityName.equals("") || cityKana != null && !cityKana.equals("")
					|| designatedNumber != null && !designatedNumber.equals("") || coBankCode != null
					&& !coBankCode.equals("")) {
				sb.append("AND ");
				
			}
		}
		if (cityName != null && !cityName.equals("")) {
			sb.append(COL_CITY_NAME + "  like  ? ");
			if (cityKana != null && !cityKana.equals("") || designatedNumber != null && !designatedNumber.equals("")
					|| coBankCode != null && !coBankCode.equals("")) {
				sb.append("AND ");
				
			}
		}
		if (cityKana != null && !cityKana.equals("")) {
			sb.append(COL_CITY_KANA + "  like  ? ");
			if (designatedNumber != null && !designatedNumber.equals("") || coBankCode != null
					&& !coBankCode.equals("")) {
				sb.append("AND ");
				
			}
		}
		if (designatedNumber != null && !designatedNumber.equals("")) {
			sb.append(COL_DESIGNATED_NUMBER + "  like  ? ");
			if (coBankCode != null && !coBankCode.equals("")) {
				sb.append("AND ");
				
			}
		}
		if (coBankCode != null && !coBankCode.equals("")) {
			sb.append(COL_CO_BANK_CODE + "  =  ? ");
		}
		
		try {
			prepareStatement(getSelectQuery(getClass()) + sb.toString() + getOrderForKey(getClass()));
			if (cityCode != null && !cityCode.equals("")) {
				setParam(index++, cityCode + "%");
			}
			if (cityName != null && !cityName.equals("")) {
				setParam(index++, "%" + cityName + "%");
			}
			if (cityKana != null && !cityKana.equals("")) {
				setParam(index++, "%" + cityKana + "%");
			}
			if (designatedNumber != null && !designatedNumber.equals("")) {
				setParam(index++, designatedNumber + "%");
			}
			if (coBankCode != null && !coBankCode.equals("")) {
				setParam(index++, coBankCode);
			}
			
			executeQuery();
			List<CmCityDto> list = mappingAll();
			return list;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	/**
	 * 条件文によるデータ取得
	 * @param coBankCode 会社銀行コード(検索キー)
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	public List<CmCityDto> findForFirmBanking(String coBankCode) throws SQLException, IllegalAccessException,
			NoSuchFieldException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append("WHERE ");
		sb.append(COL_CO_BANK_CODE + "  =  ? ");
		try {
			prepareStatement(getSelectQuery(getClass()) + sb.toString() + getOrderForKey(getClass()));
			setParam(index++, coBankCode);
			executeQuery();
			List<CmCityDto> 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(CmCityDto 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(CmCityDto 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(CmCityDto dto) throws SQLException, IllegalAccessException, NoSuchFieldException, MospException {
		try {
			index = 1;
			prepareStatement(getUpdateQuery(getClass()));
			setParams(dto, false);
			setParam(index++, dto.getCityCode());
			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(CmCityDto dto) throws SQLException, IllegalAccessException, NoSuchFieldException, MospException {
		try {
			index = 1;
			prepareStatement(getDeleteQuery(getClass()));
			setParam(index++, dto.getCityCode());
			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(CmCityDto dto, boolean isInsert) throws SQLException {
		setParam(index++, dto.getCityCode()); // 市町村コード
		setParam(index++, dto.getCityName()); // 市町村名
		setParam(index++, dto.getCityKana()); // 市町村カナ
		setParam(index++, dto.getCityAccount()); // 市町村口座番号
		setParam(index++, dto.getCityAccountName1()); // 加入者1
		setParam(index++, dto.getCityAccountName2()); // 加入者2
		setParam(index++, dto.getDesignatedNumber()); // 指定番号
		setParam(index++, dto.getCoBankCode()); // 会社銀行コード
		setParam(index++, dto.getMasterOffice()); // 取りまとめ局
		setParam(index++, dto.getCityPostalCode()); // 市町村郵便番号
		setParam(index++, dto.getCityAddress1()); // 市町村住所1
		setParam(index++, dto.getCityAddress2()); // 市町村住所2
		setParam(index++, dto.getCityOffice1()); // 役場名1
		setParam(index++, dto.getCityOffice2()); // 役場名2
		setParam(index++, dto.getCityPhone()); // 市町村電話番号
		
		setCommonParams(isInsert);
	}
	
}
