/*
 * MosP - Mind Open Source Project         http://www.mosp.jp/
 * Copyright (C) 1987-2009 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.payroll.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.dto.ResidenceTaxDto;

public class ResidenceTaxDao 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_CITY_CODE            = CmCityDao.COL_CITY_CODE                 ;// 巗挰懞僐乕僪
	public static final String COL_CITY_NAME            = CmCityDao.COL_CITY_NAME                 ;// 巗挰懞柤徧
	public static final String COL_PAYMENT_CITY_CODE    = CmResidenceTaxDao.COL_PAYMENT_CITY_CODE ;// 擺晅愭
	public static final String COL_RES_EXPECTED_DATE    = CmResidenceTaxDao.COL_RES_EXPECTED_DATE ;// 擺晅愭揔梡擔
	public static final String COL_AMOUNT               = PdPayDeductionDao.COL_AMOUNT            ;// 廧柉惻
	public static final String COL_DETAIL_CODE_PD007    = "PD007";
	
	/**
	 * 僐儞僗僩儔僋僞
	 */
	public ResidenceTaxDao() {
		super();
		INSERT_DATE = "";
		INSERT_USER = "";
		UPDATE_DATE = "";
		UPDATE_USER = "";
	}
	
	/**
	 * 専嶕寢壥庢摼(DTO偺List)
	 * @return List 専嶕儕僗僩
	 * @throws SQLException
	 */
	private List<ResidenceTaxDto> mappingAll() throws SQLException {
		List<ResidenceTaxDto> all = new ArrayList<ResidenceTaxDto>();
		while (rs.next()) {
			all.add(mapping());
		}
		return all;
	}
	
	/**
	 * 専嶕寢壥庢摼(DTO)
	 * @return CmResidenceTaxDto 儗僐乕僪
	 * @throws SQLException
	 */
	private ResidenceTaxDto mapping() throws SQLException {
		ResidenceTaxDto dto = new ResidenceTaxDto();
		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.setResidenceTax     (rs.getInt(COL_AMOUNT         	     ));// 廧柉惻
		dto.setCityCode         (rs.getString(COL_CITY_CODE          ));// 巗挰懞僐乕僪
		dto.setCityName         (rs.getString(COL_CITY_NAME          ));// 巗挰懞柤徧
		dto.setPaymentCityCode  (rs.getString(COL_PAYMENT_CITY_CODE  ));// 擺晅愭
		return dto;
	}
	
	/**
	 * 忦審暥偵傛傞擭枛挷惍僨乕僞庢摼
	 * @param calcDate 懳徾擭寧 
	 * @return List 専嶕寢壥(DTO偺List)
	 * @throws SQLException
	 */
	public List<ResidenceTaxDto> findForCondition (
			Date   calcDate
	) throws  SQLException {
		index = 1;
		StringBuffer sb = new StringBuffer();
		setCityTax(sb);
		sb.append(" ORDER BY "                  );
		sb.append(" CMTAX."                     );
		sb.append(	COL_PAYMENT_CITY_CODE     	);
		sb.append(" , "                         );
		sb.append(" PHPAY."                     );
		sb.append(	COL_K_CODE   	            );
		
		try {
			prepareStatement(sb.toString());
			//媼梌偺応崌寁嶼寧傪愝掕
			setDateParam(index++, calcDate   );
			setDateParam(index++, calcDate   );
			setDateParam(index++, calcDate   );
			// 幚峴
			executeQuery();
			List<ResidenceTaxDto> list = mappingAll();
			return list;
		} catch (SQLException e) {
			throw e;
		} finally {
			releaseResultSet();
			releasePreparedStatement();
		}
	}
	
	private void setCityTax (StringBuffer sb) {
		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(", "                                     );
		sb.append(" PDDED."                                );
		sb.append(     	PdPayDeductionDao.COL_AMOUNT       );
		sb.append(", "                                     );
		sb.append(" CMTAX."                                );
		sb.append(    COL_PAYMENT_CITY_CODE                );
		sb.append(", "                                     );
		sb.append(" CMCITY."                               );
		sb.append(    COL_CITY_CODE                        );
		sb.append(", "                                     );
		sb.append(" CMCITY."                               );
		sb.append(    COL_CITY_NAME                        );
		sb.append(" FROM   "                               );
		sb.append(PhPaymentDao.TABLE +      " PHPAY"       );
		sb.append(", "                                     );
		sb.append(CmResidenceTaxDao.TABLE + " CMTAX"       );
		sb.append(", "                                     );
		sb.append(CmCityDao.TABLE +         " CMCITY"      );
		sb.append(", "                                     );
		sb.append(PdPayDeductionDao.TABLE + " PDDED"       );
		sb.append(", "                                     );
		sb.append(MKihonPayrollDao.TABLE +  " MKIHON"      );
		sb.append(" WHERE "                                );
		sb.append(" CMTAX."                                );
		sb.append(CmResidenceTaxDao.COL_RES_EXPECTED_DATE  );
		sb.append(" = "                                    );
		sb.append(" ( "                                    );
		sb.append(" SELECT "                               );
		sb.append(" MAX("                                  );
		sb.append("CMTAX2."                                );
		sb.append(CmResidenceTaxDao.COL_RES_EXPECTED_DATE  );
		sb.append(")"                                      );
		sb.append(" FROM   "                               );
		sb.append(CmResidenceTaxDao.TABLE + " CMTAX2"      );
		sb.append(" WHERE "                                );
		sb.append(" CMTAX."                                );
		sb.append(CmResidenceTaxDao.COL_K_CODE             );
		sb.append(" = "                                    );
		sb.append(" CMTAX2."                               );
		sb.append(CmResidenceTaxDao.COL_K_CODE             );
		sb.append(" AND "                                  );
		sb.append(" CMTAX2."                               );
		sb.append(CmResidenceTaxDao.COL_RES_EXPECTED_DATE  );
		sb.append(" <=? "                                  );
		sb.append(" ) "                                    );
		sb.append(" AND "                                  );
		sb.append(" PHPAY."                                );
		sb.append(PhPaymentDao.COL_KCODE                   );
		sb.append(" = "                                    );
		sb.append(" CMTAX."                                );
		sb.append(CmResidenceTaxDao.COL_K_CODE             );
		sb.append(" AND "                                  );
		sb.append(" CMTAX."                                );
		sb.append(CmResidenceTaxDao.COL_PAYMENT_CITY_CODE  );
		sb.append(" = "                                    );
		sb.append(" CMCITY."                               );
		sb.append(CmCityDao.COL_CITY_CODE                  );
		sb.append(" AND "                                  );
		sb.append(" PHPAY."                                );
		sb.append(PhPaymentDao.COL_CALC_MONTH              );
		sb.append(" =? "                                   );
		sb.append(" AND "                                  );
		sb.append(" PDDED."                                );
		sb.append(PdPayDeductionDao.COL_KCODE              );
		sb.append(" = "                                    );
		sb.append(" CMTAX."                                );
		sb.append(CmResidenceTaxDao.COL_K_CODE             );
		sb.append(" AND "                                  );
		sb.append(" PDDED."                                );
		sb.append(PdPayDeductionDao.COL_CALC_DATE          );
		sb.append(" =? "                                   );
		sb.append(" AND "                                  );
		sb.append(" PDDED."                                );
		sb.append(PdPayDeductionDao.COL_DETAIL_CODE        );
		sb.append(" = "                                    );
		sb.append("'" + COL_DETAIL_CODE_PD007 + "'"        );
		sb.append(" AND "                                  );
		sb.append(" MKIHON."                               );
		sb.append(  MKihonPayrollDao.COL_KCODE             );
		sb.append(" = "                                    );
		sb.append(" CMTAX."                                );
		sb.append(CmResidenceTaxDao.COL_K_CODE             );
	}
}
