/*
 * 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 Affero General Public License
 * as published by the Free Software Foundation, either version 3
 * 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 Affero General Public License for more details.
 * 
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
/**
 * 
 */
package jp.mosp.time.dao.settings.impl;

import java.sql.ResultSet;
import java.util.Date;
import java.util.List;

import jp.mosp.framework.base.BaseDto;
import jp.mosp.framework.base.BaseDtoInterface;
import jp.mosp.framework.base.MospException;
import jp.mosp.platform.base.PlatformDao;
import jp.mosp.platform.dao.human.HumanDaoInterface;
import jp.mosp.platform.dao.workflow.WorkflowDaoInterface;
import jp.mosp.time.dao.settings.ExportDataDaoInterface;

/**
 * エクスポートデータDAOクラス。
 */
public class ExportDataDao extends PlatformDao implements ExportDataDaoInterface {
	
	private WorkflowDaoInterface	workflowDao;
	private HumanDaoInterface		humanDao;
	

	/**
	 * コンストラクタ。
	 */
	public ExportDataDao() {
		// 処理無し
	}
	
	@Override
	public void initDao() throws MospException {
		workflowDao = (WorkflowDaoInterface)loadInstance(WorkflowDaoInterface.class);
		humanDao = (HumanDaoInterface)loadInstance(HumanDaoInterface.class);
	}
	
	@Override
	public BaseDto mapping() {
		// 処理なし
		return null;
	}
	
	@Override
	public List<?> mappingAll() {
		// 処理なし
		return null;
	}
	
	@Override
	public ResultSet findForAttendance(Date startDate, Date endDate, String cutoffCode, String workPlaceCode,
			String employmentCode, String sectionCode, String positionCode) throws MospException {
		try {
			index = 1;
			StringBuffer sb = new StringBuffer();
			sb.append(select());
			sb.append("*");
			sb.append(from(TmdAttendanceDao.TABLE));
			sb.append(where());
			sb.append(deleteFlagOff());
			sb.append(and());
			sb.append(greaterEqual(TmdAttendanceDao.COL_WORK_DATE));
			sb.append(and());
			sb.append(lessEqual(TmdAttendanceDao.COL_WORK_DATE));
			sb.append(and());
			sb.append(TmdAttendanceDao.COL_WORKFLOW);
			sb.append(in());
			sb.append(leftParenthesis());
			sb.append(workflowDao.getSubQueryForNotEqualDraft());
			sb.append(rightParenthesis());
			sb.append(and());
			sb.append(TmdAttendanceDao.COL_WORKFLOW);
			sb.append(in());
			sb.append(leftParenthesis());
			sb.append(workflowDao.getSubQueryForNotEqualWithdrawn());
			sb.append(rightParenthesis());
			if (!workPlaceCode.isEmpty()) {
				sb.append(and());
				sb.append(TmdAttendanceDao.COL_PERSONAL_ID);
				sb.append(in());
				sb.append(leftParenthesis());
				sb.append(humanDao.getQueryForWorkPlaceCode());
				sb.append(rightParenthesis());
			}
			if (!employmentCode.isEmpty()) {
				sb.append(and());
				sb.append(TmdAttendanceDao.COL_PERSONAL_ID);
				sb.append(in());
				sb.append(leftParenthesis());
				sb.append(humanDao.getQueryForEmploymentContractCode());
				sb.append(rightParenthesis());
			}
			if (!sectionCode.isEmpty()) {
				sb.append(and());
				sb.append(TmdAttendanceDao.COL_PERSONAL_ID);
				sb.append(in());
				sb.append(leftParenthesis());
				sb.append(humanDao.getQueryForSectionCode());
				sb.append(rightParenthesis());
			}
			if (!positionCode.isEmpty()) {
				sb.append(and());
				sb.append(TmdAttendanceDao.COL_PERSONAL_ID);
				sb.append(in());
				sb.append(leftParenthesis());
				sb.append(humanDao.getQueryForPositionCode());
				sb.append(rightParenthesis());
			}
			sb.append(getOrderByColumns(TmdAttendanceDao.COL_PERSONAL_ID, TmdAttendanceDao.COL_WORK_DATE,
					TmdAttendanceDao.COL_TIMES_WORK));
			prepareStatement(sb.toString());
			setParam(index++, startDate);
			setParam(index++, endDate);
			if (!workPlaceCode.isEmpty()) {
				setParam(index++, startDate);
				setParam(index++, workPlaceCode);
			}
			if (!employmentCode.isEmpty()) {
				setParam(index++, startDate);
				setParam(index++, employmentCode);
			}
			if (!sectionCode.isEmpty()) {
				setParam(index++, startDate);
				setParam(index++, sectionCode);
			}
			if (!positionCode.isEmpty()) {
				setParam(index++, startDate);
				setParam(index++, positionCode);
			}
			executeQuery();
			return rs;
		} catch (Throwable e) {
			throw new MospException(e);
		}
	}
	
	@Override
	public ResultSet findForTotalTime(Date startDate, Date endDate, String cutoffCode, String workPlaceCode,
			String employmentCode, String sectionCode, String positionCode) throws MospException {
		try {
			index = 1;
			StringBuffer sb = new StringBuffer();
			sb.append(select());
			sb.append("*");
			sb.append(from(TmdTotalTimeDao.TABLE));
			sb.append(where());
			sb.append(deleteFlagOff());
			sb.append(and());
			sb.append(greaterEqual(TmdTotalTimeDao.COL_CALCULATION_DATE));
			sb.append(and());
			sb.append(lessEqual(TmdTotalTimeDao.COL_CALCULATION_DATE));
			if (!workPlaceCode.isEmpty()) {
				sb.append(and());
				sb.append(TmdTotalTimeDao.COL_PERSONAL_ID);
				sb.append(in());
				sb.append(leftParenthesis());
				sb.append(humanDao.getQueryForWorkPlaceCode());
				sb.append(rightParenthesis());
			}
			if (!employmentCode.isEmpty()) {
				sb.append(and());
				sb.append(TmdTotalTimeDao.COL_PERSONAL_ID);
				sb.append(in());
				sb.append(leftParenthesis());
				sb.append(humanDao.getQueryForEmploymentContractCode());
				sb.append(rightParenthesis());
			}
			if (!sectionCode.isEmpty()) {
				sb.append(and());
				sb.append(TmdTotalTimeDao.COL_PERSONAL_ID);
				sb.append(in());
				sb.append(leftParenthesis());
				sb.append(humanDao.getQueryForSectionCode());
				sb.append(rightParenthesis());
			}
			if (!positionCode.isEmpty()) {
				sb.append(and());
				sb.append(TmdTotalTimeDao.COL_PERSONAL_ID);
				sb.append(in());
				sb.append(leftParenthesis());
				sb.append(humanDao.getQueryForPositionCode());
				sb.append(rightParenthesis());
			}
			sb.append(getOrderByColumns(TmdTotalTimeDao.COL_PERSONAL_ID, TmdTotalTimeDao.COL_CALCULATION_DATE));
			prepareStatement(sb.toString());
			setParam(index++, startDate);
			setParam(index++, endDate);
			if (!workPlaceCode.isEmpty()) {
				setParam(index++, startDate);
				setParam(index++, workPlaceCode);
			}
			if (!employmentCode.isEmpty()) {
				setParam(index++, startDate);
				setParam(index++, employmentCode);
			}
			if (!sectionCode.isEmpty()) {
				setParam(index++, startDate);
				setParam(index++, sectionCode);
			}
			if (!positionCode.isEmpty()) {
				setParam(index++, startDate);
				setParam(index++, positionCode);
			}
			executeQuery();
			return rs;
		} catch (Throwable e) {
			throw new MospException(e);
		}
	}
	
	@Override
	public ResultSet findForPaidHoliday(Date startDate, Date endDate, String cutoffCode, String workPlaceCode,
			String employmentCode, String sectionCode, String positionCode) throws MospException {
		try {
			index = 1;
			StringBuffer sb = new StringBuffer();
			sb.append(select());
			sb.append("*");
			sb.append(from(TmdPaidHolidayDao.TABLE));
			sb.append(where());
			sb.append(deleteFlagOff());
			sb.append(and());
			sb.append(greaterEqual(TmdPaidHolidayDao.COL_ACTIVATE_DATE));
			sb.append(and());
			sb.append(lessEqual(TmdPaidHolidayDao.COL_ACTIVATE_DATE));
			if (!workPlaceCode.isEmpty()) {
				sb.append(and());
				sb.append(TmdPaidHolidayDao.COL_PERSONAL_ID);
				sb.append(in());
				sb.append(leftParenthesis());
				sb.append(humanDao.getQueryForWorkPlaceCode());
				sb.append(rightParenthesis());
			}
			if (!employmentCode.isEmpty()) {
				sb.append(and());
				sb.append(TmdPaidHolidayDao.COL_PERSONAL_ID);
				sb.append(in());
				sb.append(leftParenthesis());
				sb.append(humanDao.getQueryForEmploymentContractCode());
				sb.append(rightParenthesis());
			}
			if (!sectionCode.isEmpty()) {
				sb.append(and());
				sb.append(TmdPaidHolidayDao.COL_PERSONAL_ID);
				sb.append(in());
				sb.append(leftParenthesis());
				sb.append(humanDao.getQueryForSectionCode());
				sb.append(rightParenthesis());
			}
			if (!positionCode.isEmpty()) {
				sb.append(and());
				sb.append(TmdPaidHolidayDao.COL_PERSONAL_ID);
				sb.append(in());
				sb.append(leftParenthesis());
				sb.append(humanDao.getQueryForPositionCode());
				sb.append(rightParenthesis());
			}
			sb.append(getOrderByColumns(TmdPaidHolidayDao.COL_PERSONAL_ID, TmdPaidHolidayDao.COL_ACQUISITION_DATE));
			prepareStatement(sb.toString());
			setParam(index++, startDate);
			setParam(index++, endDate);
			if (!workPlaceCode.isEmpty()) {
				setParam(index++, startDate);
				setParam(index++, workPlaceCode);
			}
			if (!employmentCode.isEmpty()) {
				setParam(index++, startDate);
				setParam(index++, employmentCode);
			}
			if (!sectionCode.isEmpty()) {
				setParam(index++, startDate);
				setParam(index++, sectionCode);
			}
			if (!positionCode.isEmpty()) {
				setParam(index++, startDate);
				setParam(index++, positionCode);
			}
			executeQuery();
			return rs;
		} catch (Throwable e) {
			throw new MospException(e);
		}
	}
	
	@Override
	public ResultSet findForStockHoliday(Date startDate, Date endDate, String cutoffCode, String workPlaceCode,
			String employmentCode, String sectionCode, String positionCode) throws MospException {
		try {
			index = 1;
			StringBuffer sb = new StringBuffer();
			sb.append(select());
			sb.append("*");
			sb.append(from(TmdStockHolidayDao.TABLE));
			sb.append(where());
			sb.append(deleteFlagOff());
			sb.append(and());
			sb.append(greaterEqual(TmdStockHolidayDao.COL_ACTIVATE_DATE));
			sb.append(and());
			sb.append(lessEqual(TmdStockHolidayDao.COL_ACTIVATE_DATE));
			if (!workPlaceCode.isEmpty()) {
				sb.append(and());
				sb.append(TmdStockHolidayDao.COL_PERSONAL_ID);
				sb.append(in());
				sb.append(leftParenthesis());
				sb.append(humanDao.getQueryForWorkPlaceCode());
				sb.append(rightParenthesis());
			}
			if (!employmentCode.isEmpty()) {
				sb.append(and());
				sb.append(TmdStockHolidayDao.COL_PERSONAL_ID);
				sb.append(in());
				sb.append(leftParenthesis());
				sb.append(humanDao.getQueryForEmploymentContractCode());
				sb.append(rightParenthesis());
			}
			if (!sectionCode.isEmpty()) {
				sb.append(and());
				sb.append(TmdStockHolidayDao.COL_PERSONAL_ID);
				sb.append(in());
				sb.append(leftParenthesis());
				sb.append(humanDao.getQueryForSectionCode());
				sb.append(rightParenthesis());
			}
			if (!positionCode.isEmpty()) {
				sb.append(and());
				sb.append(TmdStockHolidayDao.COL_PERSONAL_ID);
				sb.append(in());
				sb.append(leftParenthesis());
				sb.append(humanDao.getQueryForPositionCode());
				sb.append(rightParenthesis());
			}
			sb.append(getOrderByColumns(TmdStockHolidayDao.COL_PERSONAL_ID, TmdStockHolidayDao.COL_ACQUISITION_DATE));
			prepareStatement(sb.toString());
			setParam(index++, startDate);
			setParam(index++, endDate);
			if (!workPlaceCode.isEmpty()) {
				setParam(index++, startDate);
				setParam(index++, workPlaceCode);
			}
			if (!employmentCode.isEmpty()) {
				setParam(index++, startDate);
				setParam(index++, employmentCode);
			}
			if (!sectionCode.isEmpty()) {
				setParam(index++, startDate);
				setParam(index++, sectionCode);
			}
			if (!positionCode.isEmpty()) {
				setParam(index++, startDate);
				setParam(index++, positionCode);
			}
			executeQuery();
			return rs;
		} catch (Throwable e) {
			throw new MospException(e);
		}
	}
	
	@Override
	public ResultSet findForTimelyPaidHoliday(Date startDate, Date endDate, String cutoffCode, String workPlaceCode,
			String employmentCode, String sectionCode, String positionCode) throws MospException {
		try {
			index = 1;
			StringBuffer sb = new StringBuffer();
			sb.append(select());
			sb.append("*");
			sb.append(from(TmdTimelyPaidHolidayDao.TABLE));
			sb.append(where());
			sb.append(deleteFlagOff());
			sb.append(and());
			sb.append(greaterEqual(TmdTimelyPaidHolidayDao.COL_ACTIVATE_DATE));
			sb.append(and());
			sb.append(lessEqual(TmdTimelyPaidHolidayDao.COL_ACTIVATE_DATE));
			if (!workPlaceCode.isEmpty()) {
				sb.append(and());
				sb.append(TmdTimelyPaidHolidayDao.COL_PERSONAL_ID);
				sb.append(in());
				sb.append(leftParenthesis());
				sb.append(humanDao.getQueryForWorkPlaceCode());
				sb.append(rightParenthesis());
			}
			if (!employmentCode.isEmpty()) {
				sb.append(and());
				sb.append(TmdTimelyPaidHolidayDao.COL_PERSONAL_ID);
				sb.append(in());
				sb.append(leftParenthesis());
				sb.append(humanDao.getQueryForEmploymentContractCode());
				sb.append(rightParenthesis());
			}
			if (!sectionCode.isEmpty()) {
				sb.append(and());
				sb.append(TmdTimelyPaidHolidayDao.COL_PERSONAL_ID);
				sb.append(in());
				sb.append(leftParenthesis());
				sb.append(humanDao.getQueryForSectionCode());
				sb.append(rightParenthesis());
			}
			if (!positionCode.isEmpty()) {
				sb.append(and());
				sb.append(TmdTimelyPaidHolidayDao.COL_PERSONAL_ID);
				sb.append(in());
				sb.append(leftParenthesis());
				sb.append(humanDao.getQueryForPositionCode());
				sb.append(rightParenthesis());
			}
			sb.append(getOrderByColumns(TmdTimelyPaidHolidayDao.COL_PERSONAL_ID,
					TmdTimelyPaidHolidayDao.COL_ACQUISITION_DATE));
			prepareStatement(sb.toString());
			setParam(index++, startDate);
			setParam(index++, endDate);
			if (!workPlaceCode.isEmpty()) {
				setParam(index++, workPlaceCode);
			}
			if (!employmentCode.isEmpty()) {
				setParam(index++, employmentCode);
			}
			if (!sectionCode.isEmpty()) {
				setParam(index++, sectionCode);
			}
			if (!positionCode.isEmpty()) {
				setParam(index++, positionCode);
			}
			executeQuery();
			return rs;
		} catch (Throwable e) {
			throw new MospException(e);
		}
	}
	
	@Override
	public void closers() throws MospException {
		releaseResultSet();
		releasePreparedStatement();
	}
	
	@Override
	public int update(BaseDtoInterface baseDto) {
		// 処理なし
		return 0;
	}
	
	@Override
	public int delete(BaseDtoInterface baseDto) {
		// 処理なし
		return 0;
	}
	
	@Override
	public void setParams(BaseDtoInterface baseDto, boolean isInsert) {
		// 処理なし
	}
	
}
