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.bonus.BonusConst;
import jp.mosp.payroll.base.PayrollDao;
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
	 * @throws IllegalAccessException
	 * @throws NoSuchFieldException
	 */
	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
	 * @throws NoSuchFieldException
	 */
	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
	 * @throws NoSuchFieldException
	 */
	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();
		}
	}

}
