/*
 * MosP - Mind Open Source Project    http://www.mosp.jp/
 * Copyright (C) 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.kintai.report.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import jp.mosp.common.CommonConst;
import jp.mosp.common.common.BaseDao;
import jp.mosp.common.common.MospConst;
import jp.mosp.common.dao.MCodeDao;
import jp.mosp.kintai.common.dao.MKaisyaDao;
import jp.mosp.kintai.common.dao.MKihonDao;
import jp.mosp.kintai.common.dao.MSyozokuDao;
import jp.mosp.kintai.common.dao.MYakusyokuDao;
import jp.mosp.kintai.dao.MSyukkinboDao;
import jp.mosp.kintai.dao.MYukyuDao;
import jp.mosp.kintai.payroll.dao.KdWorkDao;
import jp.mosp.payroll.dao.MKintaiDao;

/**
 * @author yoshida
 *
 */
public class ExportDataDao extends BaseDao {
	
	/**
	 * コンストラクタ
	 */
	public ExportDataDao() {
		super();
	}
	
	/**
	 * 条件文によるデータ取得
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException SQL例外が発生した場合
	 * @throws IllegalAccessException	メソッドにアクセスできない場合
	 * @throws NoSuchFieldException	フィールドが見つからなかった場合
	 */
	public ResultSet findForConditionKihon() throws SQLException, IllegalAccessException, NoSuchFieldException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(MKihonDao.TABLE + ".*" + MospConst.DATA_SEPARATOR);
		sb.append(MKaisyaDao.COL_KAIMEI + MospConst.DATA_SEPARATOR);
		sb.append(MSyozokuDao.COL_SNAME + MospConst.DATA_SEPARATOR);
		sb.append(MYakusyokuDao.COL_YNAME + MospConst.DATA_SEPARATOR);
		sb.append(MCodeDao.COL_TNAME);
		sb.append(" FROM");
		sb.append("(");
		sb.append("(");
		sb.append("(");
		sb.append("(");
		sb.append(MKihonDao.TABLE);
		sb.append(getLeftJoinCommonSQL());
		sb.append(")");
		try {
			prepareStatement(sb.toString());
			executeQuery();
			return rs;
		} catch (SQLException e) {
			throw e;
		}
	}
	
	/**
	 * 条件文によるデータ取得
	 * @param sikyuym 支給年月
	 * @return rs 検索結果
	 * @throws SQLException SQL例外が発生した場合
	 * @throws IllegalAccessException	メソッドにアクセスできない場合
	 * @throws NoSuchFieldException	フィールドが見つからなかった場合
	 */
	public ResultSet findForConditionKintai(String sikyuym) throws SQLException, IllegalAccessException,
			NoSuchFieldException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(MKihonDao.TABLE + ".*" + MospConst.DATA_SEPARATOR);
		sb.append(MKintaiDao.TABLE + ".*" + MospConst.DATA_SEPARATOR);
		sb.append(MKaisyaDao.COL_KAIMEI + MospConst.DATA_SEPARATOR);
		sb.append(MSyozokuDao.COL_SNAME + MospConst.DATA_SEPARATOR);
		sb.append(MYakusyokuDao.COL_YNAME + MospConst.DATA_SEPARATOR);
		sb.append(MCodeDao.COL_TNAME);
		sb.append(" FROM");
		sb.append("(");
		sb.append("(");
		sb.append("(");
		sb.append("(");
		sb.append("(");
		sb.append(MKihonDao.TABLE);
		sb.append(" LEFT JOIN " + MKintaiDao.TABLE);
		sb.append(" ON " + MKihonDao.TABLE + "." + MKihonDao.COL_KCODE);
		sb.append(" = " + MKintaiDao.TABLE + "." + MKintaiDao.COL_KCODE);
		sb.append(" AND " + MKintaiDao.TABLE + "." + MKintaiDao.COL_SIKYUYM);
		sb.append(" = ?");
		sb.append(")");
		sb.append(getLeftJoinCommonSQL());
		sb.append(")");
		try {
			prepareStatement(sb.toString());
			setParam(index++, sikyuym);
			executeQuery();
			return rs;
		} catch (SQLException e) {
			throw e;
		}
	}
	
	/**
	 * 条件文によるデータ取得
	 * @param sikyuym 支給年月
	 * @return rs 検索結果
	 * @throws SQLException SQL例外が発生した場合
	 * @throws IllegalAccessException	メソッドにアクセスできない場合
	 * @throws NoSuchFieldException	フィールドが見つからなかった場合
	 */
	public ResultSet findForConditionKdWork(String sikyuym) throws SQLException, IllegalAccessException,
			NoSuchFieldException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(MKihonDao.TABLE + ".*" + MospConst.DATA_SEPARATOR);
		sb.append(KdWorkDao.TABLE + ".*" + MospConst.DATA_SEPARATOR);
		sb.append(MKaisyaDao.COL_KAIMEI + MospConst.DATA_SEPARATOR);
		sb.append(MSyozokuDao.COL_SNAME + MospConst.DATA_SEPARATOR);
		sb.append(MYakusyokuDao.COL_YNAME + MospConst.DATA_SEPARATOR);
		sb.append(MCodeDao.COL_TNAME);
		sb.append(" FROM");
		sb.append("(");
		sb.append("(");
		sb.append("(");
		sb.append("(");
		sb.append("(");
		sb.append(MKihonDao.TABLE);
		sb.append(" LEFT JOIN " + KdWorkDao.TABLE);
		sb.append(" ON " + MKihonDao.TABLE + "." + MKihonDao.COL_KCODE);
		sb.append(" = " + KdWorkDao.TABLE + "." + KdWorkDao.COL_K_CODE);
		sb.append(" AND " + KdWorkDao.TABLE + "." + KdWorkDao.COL_SIKYU_YM);
		sb.append(" = ?");
		sb.append(")");
		sb.append(getLeftJoinCommonSQL());
		sb.append(")");
		try {
			prepareStatement(sb.toString());
			setParam(index++, sikyuym);
			executeQuery();
			return rs;
		} catch (SQLException e) {
			throw e;
		}
	}
	
	/**
	 * 条件文によるデータ取得
	 * @param	kyuyoymdfrom	支給指定開始年月
	 * @param	kyuyoymdto		支給指定終了年月
	 * @return rs 検索結果
	 * @throws SQLException SQL例外が発生した場合
	 * @throws IllegalAccessException	メソッドにアクセスできない場合
	 * @throws NoSuchFieldException	フィールドが見つからなかった場合
	 */
	public ResultSet findForConditionYukyu(int kyuyoymdfrom, int kyuyoymdto) throws SQLException,
			IllegalAccessException, NoSuchFieldException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(MKihonDao.TABLE + ".*" + MospConst.DATA_SEPARATOR);
		sb.append(MYukyuDao.TABLE + ".*" + MospConst.DATA_SEPARATOR);
		sb.append(MKaisyaDao.COL_KAIMEI + MospConst.DATA_SEPARATOR);
		sb.append(MSyozokuDao.COL_SNAME + MospConst.DATA_SEPARATOR);
		sb.append(MYakusyokuDao.COL_YNAME + MospConst.DATA_SEPARATOR);
		sb.append(MCodeDao.COL_TNAME);
		sb.append(" FROM");
		sb.append("(");
		sb.append("(");
		sb.append("(");
		sb.append("(");
		sb.append("(");
		sb.append(MKihonDao.TABLE);
		sb.append(" LEFT JOIN " + MYukyuDao.TABLE);
		sb.append(" ON " + MKihonDao.TABLE + "." + MKihonDao.COL_KCODE);
		sb.append(" = " + MYukyuDao.TABLE + "." + MKintaiDao.COL_KCODE);
		sb.append(" AND " + MYukyuDao.TABLE + "." + MYukyuDao.COL_KYUYOYMD);
		sb.append(" > ?");
		sb.append(" AND " + MYukyuDao.TABLE + "." + MYukyuDao.COL_KYUYOYMD);
		sb.append(" < ?");
		sb.append(")");
		sb.append(getLeftJoinCommonSQL());
		sb.append(")");
		try {
			prepareStatement(sb.toString());
			setParam(index++, kyuyoymdfrom);
			setParam(index++, kyuyoymdto);
			executeQuery();
			return rs;
		} catch (SQLException e) {
			throw e;
		}
	}
	
	/**
	 * 条件文によるデータ取得
	 * @param	kyuyoymdfrom	支給指定開始年月
	 * @param	kyuyoymdto		支給指定終了年月
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException SQL例外が発生した場合
	 * @throws IllegalAccessException	メソッドにアクセスできない場合
	 * @throws NoSuchFieldException	フィールドが見つからなかった場合
	 */
	public ResultSet findForConditionSyukkinbo(String kyuyoymdfrom, String kyuyoymdto) throws SQLException,
			IllegalAccessException, NoSuchFieldException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT ");
		sb.append(MKihonDao.TABLE + ".*" + MospConst.DATA_SEPARATOR);
		sb.append(MSyukkinboDao.TABLE + ".*" + MospConst.DATA_SEPARATOR);
		sb.append(MKaisyaDao.COL_KAIMEI + MospConst.DATA_SEPARATOR);
		sb.append(MSyozokuDao.COL_SNAME + MospConst.DATA_SEPARATOR);
		sb.append(MYakusyokuDao.COL_YNAME + MospConst.DATA_SEPARATOR);
		sb.append(MCodeDao.COL_TNAME);
		sb.append(" FROM");
		sb.append("(");
		sb.append("(");
		sb.append("(");
		sb.append("(");
		sb.append("(");
		sb.append(MKihonDao.TABLE);
		sb.append(" LEFT JOIN " + MSyukkinboDao.TABLE);
		sb.append(" ON " + MKihonDao.TABLE + "." + MKihonDao.COL_KCODE);
		sb.append(" = " + MSyukkinboDao.TABLE + "." + MSyukkinboDao.COL_KCODE);
		sb.append(" AND " + MSyukkinboDao.TABLE + "." + MSyukkinboDao.COL_HIZUKE);
		sb.append(" >= ?");
		sb.append(" AND " + MSyukkinboDao.TABLE + "." + MSyukkinboDao.COL_HIZUKE);
		sb.append(" <= ?");
		sb.append(")");
		sb.append(getLeftJoinCommonSQL());
		sb.append(")");
		try {
			prepareStatement(sb.toString());
			setParam(index++, kyuyoymdfrom);
			setParam(index++, kyuyoymdto);
			executeQuery();
			return rs;
		} catch (SQLException e) {
			throw e;
		}
	}
	
	/**
	 * 終了処理
	 * @throws SQLException SQL例外が発生した場合
	 * @throws IllegalAccessException	メソッドにアクセスできない場合
	 * @throws NoSuchFieldException	フィールドが見つからなかった場合
	 */
	public void closers() throws SQLException, IllegalAccessException, NoSuchFieldException {
		try {
			releaseResultSet();
			releasePreparedStatement();
		} catch (SQLException e) {
			throw e;
		}
	}
	
	/**
	 * 共通項目SQLクエリ発行
	 * @return	共通項目SQLクエリ
	 */
	protected String getLeftJoinCommonSQL() {
		StringBuffer sb = new StringBuffer();
		sb.append(" LEFT JOIN " + MKaisyaDao.TABLE);
		sb.append(" ON " + MKihonDao.TABLE + "." + MKihonDao.COL_JCODE);
		sb.append(" = " + MKaisyaDao.TABLE + "." + MKaisyaDao.COL_ID);
		sb.append(")");
		sb.append(" LEFT JOIN " + MSyozokuDao.TABLE);
		sb.append(" ON " + MKihonDao.TABLE + "." + MKihonDao.COL_SCODE);
		sb.append(" = " + MSyozokuDao.TABLE + "." + MSyozokuDao.COL_SCODE);
		sb.append(")");
		sb.append(" LEFT JOIN " + MYakusyokuDao.TABLE);
		sb.append(" ON " + MKihonDao.TABLE + "." + MKihonDao.COL_YCODE);
		sb.append(" = " + MYakusyokuDao.TABLE + "." + MYakusyokuDao.COL_YCODE);
		sb.append(" AND " + MKihonDao.TABLE + "." + MKihonDao.COL_TOKYU);
		sb.append(" = " + MYakusyokuDao.TABLE + "." + MYakusyokuDao.COL_TOKYU);
		sb.append(")");
		sb.append(" LEFT JOIN " + MCodeDao.TABLE);
		sb.append(" ON " + MKihonDao.TABLE + "." + MKihonDao.COL_KNKUBUN);
		sb.append(" = " + MCodeDao.TABLE + "." + MCodeDao.COL_TCODE);
		sb.append(" AND " + MCodeDao.TABLE + "." + MCodeDao.COL_TID);
		sb.append(" = '" + CommonConst.TID_SALARY + "'");
		return sb.toString();
	}
	
}
