package jp.mosp.payroll.dao;

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

import jp.mosp.payroll.base.PayrollConst;
import jp.mosp.payroll.base.PayrollDao;
import jp.mosp.payroll.dto.BankFrikomListDto;

/**
 * @author koike
 *
 */
public class BankFrikomListDao extends PayrollDao {
	
//	 列名
	/**
	 * 社員コード
	 */
	public static final String	COL_K_CODE				= PhPaymentDao.COL_KCODE;
	/**
	 * 計算年月
	 */
	public static final String	COL_CALC_DATE			= PhPaymentDao.COL_CALC_MONTH;
	/**
	 * 給与支給日
	 */
	public static final String	COL_PAYDAY				= PhPaymentDao.COL_PAY_DATE;
	/**
	 * 社員氏名
	 */
	public static final String	COL_K_NAME				= MKihonPayrollDao.COL_SIMEI;
	/**
	 * 支給方法連番
	 */
	public static final String	COL_MEANS_SEQUENCE		= CmPaymentMeansDao.COL_MEANS_SEQUENCE;
	/**
	 * 支給区分
	 */
	public static final String	COL_ALLOWANCE_TYPE		= CmPaymentMeansDao.COL_ALLOWANCE_TYPE;
	/**
	 * 会社銀行コード
	 */
	public static final String	COL_CO_BANK_CODE		= CmPaymentMeansDao.COL_CO_BANK_CODE;
	/**
	 * 会社銀行名
	 */
	public static final String	COL_CO_BANK_NAME		= CmBankDao.COL_BANK_NAME;
	/**
	 * 会社銀行名ﾌﾘｶﾞﾅ
	 */
	public static final String	COL_CO_BANK_KANA		= CmBankDao.COL_BANK_KANA;
	/**
	 * 振込銀行コード
	 */
	public static final String	COL_PAID_BANK_CODE		= CmPaymentMeansDao.COL_PAID_BANK_CODE;
	/**
	 * 振込銀行名
	 */
	public static final String	COL_PAID_BANK_NAME		= CmBankDao.COL_BANK_NAME;
	/**
	 * 振込銀行名ﾌﾘｶﾞﾅ
	 */
	public static final String	COL_PAID_BANK_KANA		= CmBankDao.COL_BANK_KANA;
	/**
	 * 振込銀行支店コード
	 */
	public static final String	COL_PAID_BRANCH_CODE	= CmPaymentMeansDao.COL_PAID_BRANCH_CODE;
	/**
	 * 振込銀行支店名
	 */
	public static final String	COL_PAID_BRANCH_NAME	= CmBranchDao.COL_BRANCH_NAME;
	/**
	 * 振込銀行支店名ﾌﾘｶﾞﾅ
	 */
	public static final String	COL_PAID_BRANCH_KANA	= CmBranchDao.COL_BRANCH_KANA;
	/**
	 * 預金区分
	 */
	public static final String	COL_ACCOUNT_TYPE		= CmPaymentMeansDao.COL_ACCOUNT_TYPE;
	/**
	 * 口座番号
	 */
	public static final String	COL_ACCOUNT_NO			= CmPaymentMeansDao.COL_ACCOUNT_NO;
	/**
	 * 口座名義
	 */
	public static final String	COL_HOLDER_NAME			= CmPaymentMeansDao.COL_HOLDER_NAME;
	/**
	 * 口座名義ﾌﾘｶﾞﾅ
	 */
	public static final String	COL_HOLDER_KANA			= CmPaymentMeansDao.COL_HOLDER_KANA;
	/**
	 * 支給方法
	 */
	public static final String	COL_PAYMENT_MEANS		= CmPaymentMeansDao.COL_PAYMENT_MEANS;
	/**
	 * 金額区分
	 */
	public static final String	COL_AMOUNT_TYPE			= CmPaymentMeansDao.COL_AMOUNT_TYPE;
	/**
	 * 会社銀行コード
	 */
	public static final String	COL_CO_BANK_CODE_CO		= CmCoBankDao.COL_CO_BANK_CODE;
	
	/**
	 * 
	 */
	public static final String	COL_BANK_PAY_AMOUNT		= "BANK_PAY_AMOUNT";
	/**
	 * 
	 */
	public static final String	COL_BANK_CD				= "BANK_CODE";
	/**
	 * 
	 */
	public static final String	COL_BRANCH_CD			= "BRANCH_CODE";
	/**
	 * 
	 */
	public static final String	CM_CODE_TABLE			= "CM_CODE";
	/**
	 * 
	 */
	public static final String	COL_T_CODE				= "T_CODE";
	/**
	 * 
	 */
	public static final String	COL_T_NAME				= "T_NAME";
	/**
	 * 
	 */
	public static final String	COL_DETAIL_CODE_PS011	= "PS011";
	/**
	 * 
	 */
	public static final String	COL_DETAIL_CODE_PS012	= "PS012";
	

	/**
	 * コンストラクタ
	 */
	public BankFrikomListDao() {
		super();
		INSERT_DATE = "";
		INSERT_USER = "";
		UPDATE_DATE = "";
		UPDATE_USER = "";
	}
	
	/**
	 * 検索結果取得(DTOのList)
	 * @return List 検索リスト
	 * @throws SQLException SQL例外が発生した場合
	 */
	private List<BankFrikomListDto> mappingAll() throws SQLException {
		List<BankFrikomListDto> all = new ArrayList<BankFrikomListDto>();
		while (rs.next()) {
			all.add(mapping());
		}
		return all;
	}
	
	/**
	 * 検索結果取得(DTO)
	 * @return AdjustmentListDto 年末調整レコード
	 * @throws SQLException SQL例外が発生した場合
	 */
	private BankFrikomListDto mapping() throws SQLException {
		BankFrikomListDto dto = new BankFrikomListDto();
		dto.setKCode(rs.getString(COL_K_CODE)); // 社員コード
		dto.setCalcDate(rs.getDate(COL_CALC_DATE)); // 計算年月
		dto.setPayday(rs.getDate(COL_PAYDAY)); // 給与支給日
		dto.setKName(rs.getString(COL_K_NAME)); // 社員氏名
		dto.setBankPayAmount(rs.getInt(COL_BANK_PAY_AMOUNT)); // 銀行振込額
		dto.setAllowanceType(rs.getString(COL_ALLOWANCE_TYPE)); // 支給区分
		dto.setCoBankCode(rs.getString(COL_CO_BANK_CODE)); // 会社銀行コード
		dto.setBankCode(rs.getString("COBANKCODE")); // 会社銀行の銀行コード
		dto.setCoBankName(rs.getString("COBANKNAME")); // 会社銀行名
		dto.setCoBankKana(rs.getString("COBANKKANA")); // 会社銀行ﾌﾘｶﾞﾅ
		dto.setBranchCode(rs.getString("COBRANCHCODE")); // 会社銀行の支店コード
		dto.setCoBranchName(rs.getString("COBRANCHNAME")); // 会社支店名
		dto.setCoBranchKana(rs.getString("COBRANCHKANA")); // 会社支店ﾌﾘｶﾞﾅ
		dto.setPaidBankCode(rs.getString(COL_PAID_BANK_CODE)); // 振込銀行コード
		dto.setPaidBankName(rs.getString("PAIDBANKNAME")); // 振込銀行名
		dto.setPaidBankKana(rs.getString("PAIDBANKKANA")); // 振込銀行ﾌﾘｶﾞﾅ
		dto.setPaidBranchCode(rs.getString(COL_PAID_BRANCH_CODE)); // 振込銀行支店コード
		dto.setPaidBranchName(rs.getString("PAIDBRANCHNAME")); // 振込銀行支店名
		dto.setPaidBranchKana(rs.getString("PAIDBRANCHKANA")); // 振込銀行支店ﾌﾘｶﾞﾅ
		dto.setMeansSequence(rs.getInt(COL_MEANS_SEQUENCE)); // 支給方法連番
		dto.setAccountType(rs.getString(COL_ACCOUNT_TYPE)); // 預金区分
		dto.setAccountName(rs.getString(COL_T_NAME)); // 預金区分名称
		dto.setCoAccountName(rs.getString("CO_T_NAME")); // 預金区分名称(会社銀行)
		dto.setAccountNo(rs.getString("ACCOUNT_NO")); // 口座番号
		dto.setCoAccountNo(rs.getString("CO_ACCOUNT_NO")); // 口座番号(会社銀行)
		dto.setHolderName(rs.getString(COL_HOLDER_NAME)); // 口座名義
		dto.setHolderKana(rs.getString(COL_HOLDER_KANA)); // 口座名義ﾌﾘｶﾞﾅ
		dto.setPaymentMeans(rs.getString(COL_PAYMENT_MEANS)); // 支給方法
		dto.setAmountType(rs.getString(COL_AMOUNT_TYPE)); // 金額区分
		return dto;
	}
	
	/**
	 * 条件文による年末調整データ取得
	 * @param calcDate 対象年月
	 * @return List 検索結果(DTOのList)
	 * @throws SQLException SQL例外が発生した場合
	 */
	public List<BankFrikomListDto> findForCondition(Date calcDate) throws SQLException {
		try {
			index = 1;
			StringBuffer sb = new StringBuffer();
			setBankPayment(sb, 1);
			sb.append(" UNION ALL ");
			setBankPayment(sb, 2);
			sb.append(" ");
			sb.append(getOrderBy());
			sb.append(COL_CO_BANK_CODE);
			sb.append(" , ");
			sb.append(COL_PAID_BANK_CODE);
			sb.append(", ");
			sb.append(COL_PAID_BRANCH_CODE);
			sb.append(", ");
			sb.append(COL_K_CODE);
			prepareStatement(sb.toString());
			//給与の場合計算月を設定
			setDateParam(index++, calcDate);
			setDateParam(index++, calcDate);
			setDateParam(index++, calcDate);
			setDateParam(index++, calcDate);
			setDateParam(index++, calcDate);
			setDateParam(index++, calcDate);
			// 実行
			executeQuery();
			List<BankFrikomListDto> list = mappingAll();
			return list;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	private void setBankPayment(StringBuffer sb, int bankPayAmount) {
		sb.append("SELECT ");
		sb.append(" PHPAY.");
		sb.append(COL_K_CODE);
		sb.append(", ");
		sb.append(" PHPAY.");
		sb.append(COL_CALC_DATE);
		sb.append(", ");
		sb.append(" PHPAY.");
		sb.append(COL_PAYDAY);
		sb.append(", ");
		sb.append(" MKIHON.");
		sb.append(COL_K_NAME);
		sb.append(", ");
		if (bankPayAmount == 1) {
			sb.append(" PDPAY.");
			sb.append(PdPaySumDao.COL_AMOUNT + " AS " + COL_BANK_PAY_AMOUNT);
			
		} else {
			sb.append(" PDPAY.");
			sb.append(PdPaySumDao.COL_AMOUNT + " AS " + COL_BANK_PAY_AMOUNT);
		}
		sb.append(", ");
		sb.append(" CMPAY.");
		sb.append(COL_MEANS_SEQUENCE);
		sb.append(", ");
		sb.append(" CMPAY.");
		sb.append(COL_ALLOWANCE_TYPE);
		sb.append(", ");
		sb.append(" CMPAY.");
		sb.append(COL_CO_BANK_CODE);
		sb.append(", ");
		sb.append(" COCMBANK.");
		sb.append(COL_BANK_CD + " AS COBANKCODE ");
		sb.append(", ");
		sb.append(" COCMBANK.");
		sb.append(COL_CO_BANK_NAME + " AS COBANKNAME ");
		sb.append(", ");
		sb.append(" COCMBANK.");
		sb.append(COL_CO_BANK_KANA + " AS COBANKKANA ");
		sb.append(", ");
		sb.append(" COCMBRANCH.");
		sb.append(COL_BRANCH_CD + " AS COBRANCHCODE ");
		sb.append(", ");
		sb.append(" COCMBRANCH.");
		sb.append(COL_PAID_BRANCH_NAME + " AS COBRANCHNAME ");
		sb.append(", ");
		sb.append(" COCMBRANCH.");
		sb.append(COL_PAID_BRANCH_KANA + " AS COBRANCHKANA ");
		sb.append(", ");
		sb.append(" CMPAY.");
		sb.append(COL_PAID_BANK_CODE);
		sb.append(", ");
		sb.append(" CMBANK.");
		sb.append(COL_PAID_BANK_NAME + " AS PAIDBANKNAME ");
		sb.append(", ");
		sb.append(" CMBANK.");
		sb.append(COL_PAID_BANK_KANA + " AS PAIDBANKKANA ");
		sb.append(", ");
		sb.append(" CMPAY.");
		sb.append(COL_PAID_BRANCH_CODE);
		sb.append(", ");
		sb.append(" CMBRANCH.");
		sb.append(COL_PAID_BRANCH_NAME + " AS PAIDBRANCHNAME ");
		sb.append(", ");
		sb.append(" CMBRANCH.");
		sb.append(COL_PAID_BRANCH_KANA + " AS PAIDBRANCHKANA ");
		sb.append(", ");
		sb.append(" CMPAY.");
		sb.append(COL_ACCOUNT_TYPE);
		sb.append(", ");
		sb.append(" CMCODE.");
		sb.append(COL_T_NAME + " AS T_NAME ");
		sb.append(", ");
		sb.append(" COCMCODE.");
		sb.append(COL_T_NAME + " AS CO_T_NAME ");
		sb.append(", ");
		sb.append(" CMPAY.");
		sb.append(COL_ACCOUNT_NO + " AS ACCOUNT_NO ");
		sb.append(", ");
		sb.append(" COBANK3.");
		sb.append(COL_ACCOUNT_NO + " AS CO_ACCOUNT_NO ");
		sb.append(", ");
		sb.append(" CMPAY.");
		sb.append(COL_HOLDER_NAME);
		sb.append(", ");
		sb.append(" CMPAY.");
		sb.append(COL_HOLDER_KANA);
		sb.append(", ");
		sb.append(" CMPAY.");
		sb.append(COL_PAYMENT_MEANS);
		sb.append(", ");
		sb.append(" CMPAY.");
		sb.append(COL_AMOUNT_TYPE);
		sb.append(" FROM ");
		sb.append(CmPaymentMeansDao.TABLE + " CMPAY");
		sb.append(" INNER JOIN " + PdPaySumDao.TABLE + " PDPAY");
		sb.append(" ON PDPAY.KCODE = CMPAY.K_CODE ");
		sb.append(" INNER JOIN " + MKihonPayrollDao.TABLE + " MKIHON");
		sb.append(" ON MKIHON.KCODE = CMPAY.K_CODE ");
		
		sb.append(" INNER JOIN " + PhPaymentDao.TABLE + " PHPAY");
		sb.append(" ON PHPAY.KCODE = CMPAY.K_CODE AND PHPAY.CALC_MONTH = ? ");
		
		sb.append(" LEFT JOIN " + CmBankDao.TABLE + " COCMBANK");
		sb.append(" ON COCMBANK.BANK_CODE");
		sb.append(" = ");
		sb.append(" (SELECT ");
		sb.append(" COBANK. ");
		sb.append(COL_BANK_CD);
		sb.append(" FROM ");
		sb.append(CmCoBankDao.TABLE + " COBANK ");
		sb.append(" WHERE ");
		sb.append(" COBANK.CO_BANK_CODE = CMPAY.CO_BANK_CODE) ");
		
		sb.append(" LEFT JOIN " + CmBranchDao.TABLE + " COCMBRANCH");
		sb.append(" ON COCMBRANCH.BANK_CODE ");
		sb.append(" = ");
		sb
			.append(" (SELECT  COBANK. BANK_CODE FROM CM_CO_BANK COBANK  WHERE  COBANK.CO_BANK_CODE = CMPAY.CO_BANK_CODE) ");
		sb.append(" AND ");
		sb.append(" COCMBRANCH.BRANCH_CODE ");
		sb.append(" = ");
		sb
			.append(" (SELECT  COBRANCH.BRANCH_CODE FROM CM_CO_BANK COBRANCH  WHERE COBRANCH.CO_BANK_CODE = CMPAY.CO_BANK_CODE) ");
		
		sb.append(" LEFT JOIN " + CmBankDao.TABLE + " CMBANK ");
		sb.append(" ON CMBANK.BANK_CODE = CMPAY.PAID_BANK_CODE ");
		sb.append(" LEFT JOIN " + CmBranchDao.TABLE + " CMBRANCH ");
		sb.append(" ON CMBRANCH.BANK_CODE = CMPAY.PAID_BANK_CODE AND CMBRANCH.BRANCH_CODE = CMPAY.PAID_BRANCH_CODE ");
		sb.append(" LEFT JOIN " + CM_CODE_TABLE + " CMCODE ");
		sb.append(" ON CMCODE.T_ID = '" + "ACCOUNT' AND CMCODE.T_CODE = CMPAY.ACCOUNT_TYPE ");
		sb.append(" LEFT JOIN " + CM_CODE_TABLE + " COCMCODE ");
		sb.append(" ON COCMCODE.T_ID = '" + "ACCOUNT' AND COCMCODE.T_CODE ");
		sb.append(" = ");
		sb.append(" (SELECT ");
		sb.append(" COBANK2. ");
		sb.append(" ACCOUNT_TYPE ");
		sb.append(" FROM ");
		sb.append(CmCoBankDao.TABLE + " COBANK2 ");
		sb.append(" WHERE ");
		sb.append(" COBANK2.CO_BANK_CODE = CMPAY.CO_BANK_CODE) ");
		sb.append(" LEFT JOIN " + CmCoBankDao.TABLE + " COBANK3 ");
		sb.append(" ON COBANK3.CO_BANK_CODE = CMPAY.CO_BANK_CODE");
		sb.append(" WHERE ");
		sb.append(" PHPAY.");
		sb.append(COL_CALC_DATE);
		sb.append(" = ? ");
		sb.append(" AND ");
		sb.append(" CMPAY.");
		sb.append(COL_MEANS_SEQUENCE);
		sb.append(" = ");
		if (bankPayAmount == 1) {
			sb.append("'" + PayrollConst.PAYMENT_MEANS_SEQ_1 + "'");
		} else {
			sb.append("'" + PayrollConst.PAYMENT_MEANS_SEQ_2 + "'");
			
		}
		sb.append(" AND ");
		sb.append(" CMPAY.");
		sb.append(CmPaymentMeansDao.COL_PAYMENT_MEANS);
		sb.append(" = ");
		sb.append("'" + PayrollConst.PAYMENT_MEANS_ACCOUNT + "'");
		sb.append(" AND ");
		sb.append(" PDPAY.");
		sb.append("CALC_DATE");
		sb.append(" = ? ");
		sb.append(" AND ");
		if (bankPayAmount == 1) {
			sb.append(" PDPAY.");
			sb.append(PdPaySumDao.COL_DETAIL_CODE);
			sb.append(" = ");
			sb.append("'" + COL_DETAIL_CODE_PS011 + "'");
		} else {
			sb.append(" PDPAY.");
			sb.append(PdPaySumDao.COL_DETAIL_CODE);
			sb.append(" = ");
			sb.append("'" + COL_DETAIL_CODE_PS012 + "'");
			
		}
		
		sb.append(" AND ");
		if (bankPayAmount == 1) {
			sb.append(" PDPAY.");
			sb.append(PdPaySumDao.COL_AMOUNT);
			sb.append(" <> ");
			sb.append(PayrollConst.AMOUNT_ZERO);
		} else {
			sb.append(" PDPAY.");
			sb.append(PdPaySumDao.COL_AMOUNT);
			sb.append(" <> ");
			sb.append(PayrollConst.AMOUNT_ZERO);
			
		}
	}
}
