package jp.mosp.payroll.bonus.dao;

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

import jp.mosp.payroll.base.PayrollDao;
import jp.mosp.payroll.bonus.BonusConst;
import jp.mosp.payroll.bonus.dto.PayrollLineupDto;

public class PayrollLineupDao extends PayrollDao {
	
	// テーブル名及びキー列名
	public static final String	TABLE1				= "UD_BONUS";
	public static final String	TABLE2				= "UH_BASIS";
	public static final String	TABLE3				= "UD_PAYMENT";
	public static final String	KEY_1				= "K_CODE";		// 社員コード
	public static final String	KEY_2				= "CALC_DATE";		// 計算年月
																		
	// 列名
	public static final String	COL_CALC_STATUS		= "CALC_STATUS";	// データステータス
	public static final String	COL_PAYMENT_TOTAL	= "PAYMENT_TOTAL";	// 賞与差引支給額
	public static final String	COL_CALC_DATE		= "CALC_DATE";		// 計算年月
	public static final String	COL_PAYDAY			= "PAYDAY";		// 賞与支給日
	public static final String	COL_K_CODE			= "K_CODE";		// 社員コード
	public static final String	COL_K_NAME			= "K_NAME";		// 社員氏名
	public static final String	COL_K_KANA			= "K_KANA";		// 社員フリガナ
	public static final String	COL_STATION_CODE	= "STATION_CODE";	// 所属コード
	public static final String	COL_POSITION_CODE	= "POSITION_CODE";	// 職位コード
	public static final String	COL_EXPECTED_DATE	= "EXPECTED_DATE";	// 適用年月
	public static final String	COL_APPLIED_DATE	= "APPLIED_DATE";	// 適用日
																		
	
	/**
	 * コンストラクタ
	 */
	public PayrollLineupDao() {
		super();
	}
	
	/**
	 * 検索結果取得(DTOのList)
	 * @return List 検索リスト
	 * @throws SQLException
	 */
	private List<PayrollLineupDto> mappingAll(boolean isNoData) throws SQLException {
		List<PayrollLineupDto> all = new ArrayList<PayrollLineupDto>();
		while (rs.next()) {
			all.add(mapping(isNoData));
		}
		return all;
	}
	
	/**
	 * 検索結果取得(DTO)
	 * @return PayrollLineupDto UD_PAYMENTレコード
	 * @throws SQLException
	 */
	private PayrollLineupDto mapping(boolean isNoData) throws SQLException {
		PayrollLineupDto dto = new PayrollLineupDto();
		if (isNoData) {
			dto.setPaymentTotal(0);
			dto.setCalcStatus(BonusConst.CALC_STATUS_NO);
		} else {
			dto.setPaymentTotal(rs.getInt(COL_PAYMENT_TOTAL));
			dto.setCalcStatus(rs.getString(COL_CALC_STATUS));
			dto.setCalcDate(rs.getDate(COL_CALC_DATE));
		}
		dto.setKCode(rs.getString(COL_K_CODE));
		dto.setKName(rs.getString(COL_K_NAME));
		dto.setStationCode(rs.getString(COL_STATION_CODE));
		dto.setPositionCode(rs.getString(COL_POSITION_CODE));
		return dto;
	}
	
	/**
	 * 検索結果取得(DTOのList)
	 * @return List 検索リスト
	 * @throws SQLException
	 */
	private List<PayrollLineupDto> mappingBonusAll(boolean isNoData) throws SQLException {
		List<PayrollLineupDto> all = new ArrayList<PayrollLineupDto>();
		while (rs.next()) {
			all.add(mappingBonus(isNoData));
		}
		return all;
	}
	
	/**
	 * 検索結果取得(DTO)
	 * @return PayrollLineupDto UD_PAYMENTレコード
	 * @throws SQLException
	 */
	private PayrollLineupDto mappingBonus(boolean isNoData) throws SQLException {
		PayrollLineupDto dto = new PayrollLineupDto();
		if (isNoData) {
			dto.setPaymentTotal(0);
			dto.setCalcStatus(BonusConst.CALC_STATUS_NO);
		} else {
			dto.setPaymentTotal(rs.getInt(COL_PAYMENT_TOTAL));
			dto.setCalcStatus(rs.getString(COL_CALC_STATUS));
			dto.setCalcDate(rs.getDate(COL_CALC_DATE));
			dto.setPayday(rs.getDate(COL_PAYDAY));
		}
		dto.setKCode(rs.getString(COL_K_CODE));
		dto.setKName(rs.getString(COL_K_NAME));
		dto.setStationCode(rs.getString(COL_STATION_CODE));
		dto.setPositionCode(rs.getString(COL_POSITION_CODE));
		return dto;
	}
	
	/**
	 * 条件文によるデータ取得(賞与)
	 * @param calcDate     検索計算年月
	 * @param statusData   検索計算ステータス
	 * @param kCode        検索社員コード
	 * @param kName        検索社員氏名
	 * @param kKana        検索社員フリガナ
	 * @param stationCode  検索所属
	 * @param positionCode 検索職位
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException
	 */
	public List<PayrollLineupDto> findForBonusCondition(Date payday, String statusData, String kCode, String kName,
			String kKana, String stationCode, String positionCode) throws SQLException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(" UDBO." + COL_PAYDAY);
		sb.append(", ");
		sb.append(" UDBO." + COL_CALC_DATE);
		sb.append(", ");
		sb.append(" UDBO." + COL_CALC_STATUS);
		sb.append(", ");
		sb.append(" UDBO." + COL_PAYMENT_TOTAL);
		sb.append(", ");
		sb.append(" UHBA." + COL_K_CODE);
		sb.append(", ");
		sb.append(" UHBA." + COL_K_NAME);
		sb.append(", ");
		sb.append(" UHBA." + COL_STATION_CODE);
		sb.append(", ");
		sb.append(" UHBA." + COL_POSITION_CODE);
		sb.append(" FROM (");
		sb.append(TABLE1 + " AS UDBO ");
		sb.append("  RIGHT JOIN  ");
		sb.append("(");
		sb.append(" SELECT ");
		sb.append(" * ");
		sb.append(" FROM " + TABLE2 + ",( ");
		sb.append(" SELECT ");
		sb.append(COL_K_CODE + " AS H_K_CODE, ");
		sb.append("MAX(" + COL_EXPECTED_DATE + ") AS H_EXPECTED_DATE ");
		sb.append("FROM " + TABLE2 + " ");
		sb.append("WHERE ");
		sb.append(COL_EXPECTED_DATE + " <= ? ");
		sb.append("AND ");
		sb.append(COL_APPLIED_DATE + " IS NOT NULL ");
		sb.append("GROUP BY " + COL_K_CODE + " ");
		sb.append(") HISTORY ");
		sb.append("WHERE ");
		sb.append(COL_K_CODE + " = HISTORY.H_K_CODE ");
		sb.append("AND ");
		sb.append(COL_EXPECTED_DATE + " = HISTORY.H_EXPECTED_DATE");
		sb.append(") " + " AS UHBA ");
		sb.append(" ON ");
		sb.append(" UDBO." + COL_K_CODE + " = " + " UHBA." + COL_K_CODE);
		sb.append(" ) ");
		sb.append("WHERE ");
		sb.append(" UDBO." + COL_K_CODE + " LIKE ? ");
		sb.append(" AND ");
		sb.append(" UDBO." + COL_PAYDAY + " = ? ");
		if (statusData != null && !statusData.equals("")) {
			sb.append("AND ");
			sb.append(" UDBO." + COL_CALC_STATUS + " = ? ");
		}
		if (kName != null && !kName.equals("")) {
			sb.append("AND ");
			sb.append(" UHBA." + COL_K_NAME + " LIKE ? ");
		}
		if (kKana != null && !kKana.equals("")) {
			sb.append("AND ");
			sb.append(" UHBA." + COL_K_KANA + " LIKE ? ");
		}
		if (stationCode != null && !stationCode.equals("")) {
			sb.append("AND ");
			sb.append(" UDBO." + COL_STATION_CODE + " = ? ");
		}
		if (positionCode != null && !positionCode.equals("")) {
			sb.append(" AND ");
			sb.append(" UHBA." + COL_POSITION_CODE + " = ? ");
		}
		try {
			prepareStatement(sb.toString());
			setParam(index++, payday);
			setParam(index++, kCode + "%");
			setParam(index++, payday);
			if (statusData != null && !statusData.equals("")) {
				setParam(index++, statusData);
			}
			if (kName != null && !kName.equals("")) {
				setParam(index++, "%" + kName + "%");
			}
			if (kKana != null && !kKana.equals("")) {
				setParam(index++, "%" + kKana + "%");
			}
			if (stationCode != null && !stationCode.equals("")) {
				setParam(index++, stationCode);
			}
			if (positionCode != null && !positionCode.equals("")) {
				setParam(index++, positionCode);
			}
			executeQuery();
			List<PayrollLineupDto> list = mappingBonusAll(false);
			return list;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	/**
	 * 条件文によるデータ取得(計算ステータス：データ未作成)
	 * @param calcDate     検索計算年月
	 * @param kCode        検索社員コード
	 * @param kName        検索社員氏名
	 * @param kKana        検索社員フリガナ
	 * @param stationCode  検索所属
	 * @param positionCode 検索職位
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 */
	public List<PayrollLineupDto> findForPayNoExCondition(Date calcDate, Date startDate, Date endDate, String kCode,
			String kName, String kKana, String stationCode, String positionCode) throws SQLException,
			IllegalAccessException {
		PhBasisDao dao = new PhBasisDao();
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(" UHBA." + COL_K_CODE);
		sb.append(", ");
		sb.append(" UHBA." + COL_K_NAME);
		sb.append(", ");
		sb.append(" UHBA." + COL_STATION_CODE);
		sb.append(", ");
		sb.append(" UHBA." + COL_POSITION_CODE);
		sb.append(" FROM ");
		sb.append("(");
		sb.append(dao.findForDataLineUp(stationCode));
		sb.append(") AS UHBA");
		sb.append(" WHERE ");
		sb.append(" NOT EXISTS ");
		sb.append("(");
		sb.append(" SELECT ");
		sb.append(" * ");
		sb.append(" FROM " + TABLE3);
		sb.append(" WHERE ");
		sb.append(COL_K_CODE + " = " + "UHBA." + COL_K_CODE);
		sb.append(" AND ");
		sb.append(COL_CALC_DATE);
		sb.append(" = ? ");
		sb.append(")");
		if (kCode != null && !kCode.equals("")) {
			sb.append(" AND ");
			sb.append(" UHBA." + COL_K_CODE + " LIKE ? ");
		}
		if (kName != null && !kName.equals("")) {
			sb.append(" AND ");
			sb.append(" UHBA." + COL_K_NAME + " LIKE ? ");
		}
		if (kKana != null && !kKana.equals("")) {
			sb.append(" AND ");
			sb.append(" UHBA." + COL_K_KANA + " LIKE ? ");
		}
		if (stationCode != null && !stationCode.equals("")) {
			sb.append(" AND ");
			sb.append(" UHBA." + COL_STATION_CODE + " = ? ");
		}
		if (positionCode != null && !positionCode.equals("")) {
			sb.append(" AND ");
			sb.append(" UHBA." + COL_POSITION_CODE + " = ? ");
		}
		
		try {
			prepareStatement(sb.toString());
			setParam(index++, endDate);
			if (stationCode != null && !stationCode.equals("")) {
				setParam(index++, stationCode);
			}
			setParam(index++, startDate);
			setParam(index++, calcDate);
			if (kCode != null && !kCode.equals("")) {
				setParam(index++, kCode + "%");
			}
			if (kName != null && !kName.equals("")) {
				setParam(index++, "%" + kName + "%");
			}
			if (kKana != null && !kKana.equals("")) {
				setParam(index++, "%" + kKana + "%");
			}
			if (stationCode != null && !stationCode.equals("")) {
				setParam(index++, stationCode);
			}
			if (positionCode != null && !positionCode.equals("")) {
				setParam(index++, positionCode);
			}
			executeQuery();
			List<PayrollLineupDto> list = mappingAll(true);
			return list;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	/**
	 * 条件文によるデータ取得(データ未作成以外)
	 * @param calcDate     検索計算年月
	 * @param statusData   検索計算ステータス
	 * @param kCode        検索社員コード
	 * @param kName        検索社員氏名
	 * @param kKana        検索社員フリガナ
	 * @param stationCode  検索所属
	 * @param positionCode 検索職位
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException
	 * @throws IllegalAccessException
	 */
	public List<PayrollLineupDto> findForPaymentCondition(Date calcDate, Date startDate, Date endDate,
			String statusData, String kCode, String kName, String kKana, String stationCode, String positionCode)
			throws SQLException, IllegalAccessException {
		PhBasisDao dao = new PhBasisDao();
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(" UDPA." + COL_CALC_DATE);
		sb.append(", ");
		sb.append(" UDPA." + COL_CALC_STATUS);
		sb.append(", ");
		sb.append(" UDPA." + COL_PAYMENT_TOTAL);
		sb.append(", ");
		sb.append(" UHBA." + COL_K_CODE);
		sb.append(", ");
		sb.append(" UHBA." + COL_K_NAME);
		sb.append(", ");
		sb.append(" UHBA." + COL_STATION_CODE);
		sb.append(", ");
		sb.append(" UHBA." + COL_POSITION_CODE);
		sb.append(" FROM (");
		sb.append("(");
		sb.append(dao.findForDataLineUp(stationCode));
		sb.append(") AS UHBA ");
		sb.append(" LEFT JOIN ");
		sb.append("(");
		sb.append("SELECT ");
		sb.append(" * ");
		sb.append(" FROM " + TABLE3);
		sb.append(" WHERE ");
		sb.append(COL_CALC_DATE);
		sb.append(" = ? ");
		sb.append(") AS UDPA");
		sb.append(" ON ");
		sb.append(" UHBA." + COL_K_CODE + " = " + "UDPA." + COL_K_CODE);
		sb.append(") ");
		sb.append("WHERE ");
		sb.append(" UHBA." + COL_K_CODE + " LIKE ? ");
		if (statusData != null && !statusData.equals("")) {
			sb.append("AND ");
			sb.append(" UDPA." + COL_CALC_STATUS + " = ? ");
		}
		if (kName != null && !kName.equals("")) {
			sb.append("AND ");
			sb.append(" UHBA." + COL_K_NAME + " LIKE ? ");
		}
		if (kKana != null && !kKana.equals("")) {
			sb.append("AND ");
			sb.append(" UHBA." + COL_K_KANA + " LIKE ? ");
		}
		if (stationCode != null && !stationCode.equals("")) {
			sb.append("AND ");
			sb.append(" UHBA." + COL_STATION_CODE + " = ? ");
		}
		if (positionCode != null && !positionCode.equals("")) {
			sb.append("AND ");
			sb.append(" UHBA." + COL_POSITION_CODE + " = ? ");
		}
		try {
			prepareStatement(sb.toString());
			setParam(index++, endDate);
			if (stationCode != null && !stationCode.equals("")) {
				setParam(index++, stationCode);
			}
			setParam(index++, startDate);
			setParam(index++, calcDate);
			setParam(index++, kCode + "%");
			if (statusData != null && !statusData.equals("")) {
				setParam(index++, statusData);
			}
			if (kName != null && !kName.equals("")) {
				setParam(index++, "%" + kName + "%");
			}
			if (kKana != null && !kKana.equals("")) {
				setParam(index++, "%" + kKana + "%");
			}
			if (stationCode != null && !stationCode.equals("")) {
				setParam(index++, stationCode);
			}
			if (positionCode != null && !positionCode.equals("")) {
				setParam(index++, positionCode);
			}
			executeQuery();
			List<PayrollLineupDto> list = mappingAll(false);
			return list;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
}
