package jp.mosp.payroll.common.action;


import java.io.File;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.csv.CSVParser;

import jp.mosp.common.CommonConst;
import jp.mosp.common.action.AttendanceTotalAction;
import jp.mosp.common.common.BaseVo;
import jp.mosp.common.common.MospConst;
import jp.mosp.common.common.MospException;
import jp.mosp.common.common.MospUtility;
import jp.mosp.human.dao.MKihonDao;
import jp.mosp.human.dto.MKihonDto;
import jp.mosp.payroll.base.PayrollConst;
import jp.mosp.payroll.common.vo.PayrollImportCardVo;
import jp.mosp.payroll.dao.MImportFieldDao;
import jp.mosp.payroll.dao.PiPaymentDao;
import jp.mosp.payroll.dao.PmDetailPaDao;
import jp.mosp.payroll.dao.PmDetailPbDao;
import jp.mosp.payroll.dao.PmDetailPdDao;
import jp.mosp.payroll.dao.PmDetailPsDao;
import jp.mosp.payroll.dao.PmDetailPtDao;
import jp.mosp.payroll.dto.DetailMoneyDto;
import jp.mosp.payroll.dto.DetailPaDto;
import jp.mosp.payroll.dto.DetailPbDto;
import jp.mosp.payroll.dto.DetailPdDto;
import jp.mosp.payroll.dto.DetailTimeDto;
import jp.mosp.payroll.dto.MImportFieldDto;

public class PayrollImportCardAction extends AttendanceTotalAction {

	// コマンド
	private static final String CMD_SHOW		= "P9040";
	private static final String CMD_IMPORT	= "P9041";

	// フィールド
	protected MKihonDao					mKihonDao;
	protected MKihonDto					mKihonDto;

	/**
	 * コンストラクタ
	 */
	public PayrollImportCardAction() {
		super();
		setNeedProcSeq(false);

	}

	/* (non-Javadoc)
	 * @see jp.mosp.common.common.BaseAction#getSpecificVo()
	 */
	@Override
	protected BaseVo getSpecificVo() {
		return new PayrollImportCardVo();
	}

	/**
	 * アクション
	 */
	public void action() throws Exception {
		// DBコネクション取得
		getConnection();
		// VO取得及び設定
		prepareVo();
		// 各表示項目取得
		setPulldownList();
		// コマンド毎の処理

		if (cmd.equals(CMD_SHOW)) {
			show();
		} else if (cmd.equals(CMD_IMPORT)) {
			importFile();
		} else {
			throw new MospException(MospConst.EX_CMD_INVALID);
		}
	}

	private void show() throws Exception {
		// VO取得
		PayrollImportCardVo vo = (PayrollImportCardVo)getVo();
		vo.aryImportData		= new String[0][0];
		vo.aryImportHeaderItem  = new String[0];
		vo.setSelectICode(key1);
		vo.aryImportHeaderItem = getTemplateItemArray();
		vo.setLblHeader(makeTemplateCSV().toString());
	}

	/**
	 * インポート
	 * @throws Exception
	 */
	private void importFile() throws Exception {
		// VO取得
		PayrollImportCardVo vo = (PayrollImportCardVo)getVo();
		vo.aryImportHeaderItem = getTemplateItemArray();
		vo.aryImportHeaderCode = getTemplateCodeArray();

		// ファイル情報設定
		vo.setImportFile(multipartFieldList);
		// インポート
		importData();
		vo.initListInfo();
	}

	/**
	 * 表示用データ配列
	 * @throws Exception
	 */
	private void setPulldownList() throws Exception {
		// VO取得
		PayrollImportCardVo vo = (PayrollImportCardVo)getVo();
		vo.aryWorkType			= getWorkTypeArrayNotDeleted();
		vo.aryHolidayReason		= getReasonCodeArray(CommonConst.TID_LEAVE_REASON);
		vo.aryLateReason		= getReasonCodeArray(CommonConst.TID_LATE_REASON);
		vo.aryLeaveEarlyReason	= getReasonCodeArray(CommonConst.TID_LEAVE_EARLY_REASON);
	}

	/**
	 * テンプレート項目配列
	 * @return aryItem
	 * @throws Exception
	 */
	private String[] getTemplateItemArray() throws Exception {
		// VO取得
		PayrollImportCardVo vo = (PayrollImportCardVo)getVo();
		// DAOの準備
		MImportFieldDao dao = new MImportFieldDao();
		dao.initDao(cfg, cmd, aspUser, user, conn);
		List<MImportFieldDto> list = dao.findForCondition(vo.getSelectICode());
		String[] aryItem = new String[list.size()];
		for (int i = 0; i < list.size(); i++) {
			aryItem[i] = util.getCodeName(list.get(i).getIField(), getImportFieldArray());
		}
		return aryItem;
	}

	
	/**
	 * テンプレート項目配列
	 * @return aryItem
	 * @throws Exception
	 */
	private String[] getTemplateCodeArray() throws Exception {
		// VO取得
		PayrollImportCardVo vo = (PayrollImportCardVo)getVo();
		// DAOの準備
		MImportFieldDao dao = new MImportFieldDao();
		dao.initDao(cfg, cmd, aspUser, user, conn);
		List<MImportFieldDto> list = dao.findForCondition(vo.getSelectICode());
		String[] aryItem = new String[list.size()];
		for (int i = 0; i < list.size(); i++) {
			aryItem[i] = list.get(i).getIField();
		}
		return aryItem;
	}
	
	/**
	 * テンプレートCSV出力内容
	 * @return sb テンプレートCSV
	 */
	private StringBuffer makeTemplateCSV() {
		// VO取得
		PayrollImportCardVo vo = (PayrollImportCardVo)getVo();
		// 帳票作成準備
		StringBuffer sb = new StringBuffer();
		// ヘッダー情報設定
		for (int i = 0; i < vo.aryImportHeaderItem.length; i++) {
			// ヘッダー情報設定
			sb.append(getCsvData(vo.aryImportHeaderItem[i]));
			// セパレータ設定
			if (i == vo.aryImportHeaderItem.length - 1) {
				sb.append(CommonConst.STR_CSV_NEW_LINE);
			} else {
				sb.append(CommonConst.STR_CSV_SEPARATOR);
			}
		}
		return sb;
	}

	/**
	 * インポートフィールドプルダウン取得
	 * @param table インポートテーブルコード
	 * @return インポートフィールドプルダウン用文字列配列
	 * @throws NoSuchFieldException 
	 * @throws IllegalAccessException 
	 * @throws SQLException 
	 */
	private String[][] getImportFieldArray() throws SQLException, IllegalAccessException, NoSuchFieldException {
		List<DetailPaDto>		detailPaDto_lst;
		List<DetailPbDto>		detailPbDto_lst;
		List<DetailPdDto>		detailPdDto_lst;
		List<DetailMoneyDto>	detailPsDto_lst;
		List<DetailTimeDto>		detailPtDto_lst;

		String[][] aryImportField	= new String[100][2];
		int data_cout , tmo_count = 0;
		
		// DAOの準備
		PmDetailPaDao pmDetailPaDao = new PmDetailPaDao(cfg, cmd, aspUser, user, conn);
		PmDetailPbDao pmDetailPbDao = new PmDetailPbDao(cfg, cmd, aspUser, user, conn);
		PmDetailPdDao pmDetailPdDao = new PmDetailPdDao(cfg, cmd, aspUser, user, conn);
		PmDetailPsDao pmDetailPsDao = new PmDetailPsDao(cfg, cmd, aspUser, user, conn);
		PmDetailPtDao pmDetailPtDao = new PmDetailPtDao(cfg, cmd, aspUser, user, conn);
		
		detailPaDto_lst = pmDetailPaDao.findNotDelete();
		detailPbDto_lst = pmDetailPbDao.findNotDelete();
		detailPdDto_lst = pmDetailPdDao.findNotDelete();
		detailPsDto_lst = pmDetailPsDao.findNotDelete();
		detailPtDto_lst = pmDetailPtDao.findNotDelete();

		aryImportField[0][0]		= PayrollConst.CODE_IMPORT_FIELD_KCODE;
		aryImportField[0][1]		= PayrollConst.NAM_IMPORT_FIELD_KCODE;
		aryImportField[1][0]		= PayrollConst.DTL_SNEN;
		aryImportField[1][1]		= PayrollConst.NAM_IMPORT_FIELD_DTL_SNEN;
		aryImportField[2][0]		= PayrollConst.DTL_STUKI;
		aryImportField[2][1]		= PayrollConst.NAM_IMPORT_FIELD_DTL_STUKI;
		aryImportField[3][0]		= PayrollConst.DTL_SHI;
		aryImportField[3][1]		= PayrollConst.NAM_IMPORT_FIELD_DTL_DTL_SHI;
		
		data_cout = 4;
		
		for (Iterator<DetailPaDto> it = detailPaDto_lst.iterator(); it.hasNext();) {
			DetailPaDto dto = it.next();
			if( dto.getInputType() == PayrollConst.DETAIL_IN_UPLOAD ){
				aryImportField[data_cout][0] = dto.getDetailCode();
				aryImportField[data_cout][1] = dto.getDetailName();
				data_cout++;
			}
		}

		for (Iterator<DetailPbDto> it = detailPbDto_lst.iterator(); it.hasNext();) {
			DetailPbDto dto = it.next();
			if( dto.getInputType() == PayrollConst.DETAIL_IN_UPLOAD ){
				aryImportField[data_cout][0] = dto.getDetailCode();
				aryImportField[data_cout][1] = dto.getDetailName();
				data_cout++;
			}
		}

		for (Iterator<DetailPdDto> it = detailPdDto_lst.iterator(); it.hasNext();) {
			DetailPdDto dto = it.next();
			if( dto.getInputType() == PayrollConst.DETAIL_IN_UPLOAD ){
				aryImportField[data_cout][0] = dto.getDetailCode();
				aryImportField[data_cout][1] = dto.getDetailName();
				data_cout++;
			}
		}
		
		for (Iterator<DetailMoneyDto> it = detailPsDto_lst.iterator(); it.hasNext();) {
			DetailMoneyDto dto = it.next();
			if( dto.getInputType() == PayrollConst.DETAIL_IN_UPLOAD ){
				aryImportField[data_cout][0] = dto.getDetailCode();
				aryImportField[data_cout][1] = dto.getDetailName();
				data_cout++;
			}
		}

		for (Iterator<DetailTimeDto> it = detailPtDto_lst.iterator(); it.hasNext();) {
			DetailTimeDto dto = it.next();
			if( dto.getInputType() == PayrollConst.DETAIL_IN_UPLOAD ){
				aryImportField[data_cout][0] = dto.getDetailCode();
				aryImportField[data_cout][1] = dto.getDetailName();
				data_cout++;
				if(dto.getDetailCode().equals( PayrollConst.DTL_WARIMASI1 )){
					tmo_count = 1;
				}
			}
		}
		
		if(tmo_count == 0){
			aryImportField[data_cout][0] = PayrollConst.DTL_WARIMASI1;
			aryImportField[data_cout][1] = PayrollConst.NAM_IMPORT_FIELD_NZAJIKAN;
			
		}

		return aryImportField;
	}
	
	/**
	 * インポート
	 * @throws Exception
	 */
	private void importData() throws Exception {
		// VO取得
		PayrollImportCardVo vo = (PayrollImportCardVo)getVo();
		// ファイル取得
		File file = getUploadFile(vo.getFilImportFile());
		String[] aryHeader;
		String[][] aryImportData = new String[0][0];
		// CSVファイル
		if (vo.getFilImportFile().getName().matches(".*" + ".csv" + ".*")) {
			// CSVパースファイル取得
			CSVParser csvp = parseCSVData(file);
			aryHeader = getCSVHeadData(csvp);
			aryImportData = csvp.getAllValues();
			br.close();
		} else {
			vo.addErrMessage(MospUtility.getMessage(msg, CommonConst.MSG_NO_CSV_DATA, ""));
			throw new MospException(MospConst.EX_PARAMS_INVALID);
		}
		if(aryImportData == null){
			aryImportData = new String[0][0];
		}
		// 検証済みリストの作成
		getImportDataList(aryHeader, aryImportData);
		vo.aryImportData = new String[aryImportData.length][aryHeader.length];
		for(int i = 0; i < aryImportData.length; i++){
			for(int j = 0; j < aryHeader.length; j++){
				vo.aryImportData[i][j] = aryImportData[i][j];
			}
		}
	}

	/**
	 * 検証済みインポート情報リスト作成
	 * @param aryHeader				ヘッダー配列
	 * @param aryImportData			インポート情報配列
	 * @return listData				検証済み出勤簿情報リスト
	 * @throws Exception
	 */
	private void getImportDataList(
			String[] aryHeader,
			String[][] aryImportData
	) throws Exception {
		int regCount = 0;
		DetailTimeDto dto;
		// VO取得
		PayrollImportCardVo vo = (PayrollImportCardVo)getVo();
		if(aryImportData == null){
			vo.addErrMessage(MospUtility.getMessage(msg, CommonConst.MSG_NO_CSV_DATA, ""));
			aryImportData = new String[0][0];
		}
		// ヘッダ確認
		if (util.checkArraysEquals(aryHeader, vo.aryImportHeaderItem)) {
			for (int i = 0; i < aryImportData.length; i++) {
				List<DetailTimeDto> listData = new ArrayList<DetailTimeDto>();
				String In_Kcode      = "00000";
				String In_Calc_Nen   = "1972";
				String In_Calc_Thuki = "01";
				String In_Calc_hi    = "01";
				String In_Pay_Nen    = "1972";
				String In_Pay_Thuki  = "01";
				String In_In_Pay_hi  = "01";
				Date targetMonth;
				for (int j = 0; j < vo.aryImportHeaderItem.length; j++) {
					if (vo.aryImportHeaderItem[j].equals(PayrollConst.NAM_IMPORT_FIELD_KCODE)) {
						In_Kcode      = util.toHankakuKana(aryImportData[i][j]);
					} else if (vo.aryImportHeaderItem[j].equals(PayrollConst.NAM_IMPORT_FIELD_DTL_SNEN)) {
						In_Pay_Nen    = util.toHankakuKana(aryImportData[i][j]);
						In_Calc_Nen   = In_Pay_Nen;
					} else if (vo.aryImportHeaderItem[j].equals(PayrollConst.NAM_IMPORT_FIELD_DTL_STUKI)) {
						In_Pay_Thuki  =  util.toHankakuKana(aryImportData[i][j]);
						In_Calc_Thuki = In_Pay_Thuki;
					} else if (vo.aryImportHeaderItem[j].equals(PayrollConst.NAM_IMPORT_FIELD_DTL_DTL_SHI)) {
						In_In_Pay_hi  =  util.toHankakuKana(aryImportData[i][j]);
					}else{
						for(int k = 0; k < vo.aryImportHeaderItem.length; k++){
							if(vo.aryImportHeaderItem[j].equals(vo.aryImportHeaderItem[k])){
								dto = new DetailTimeDto();
								dto.setDetailCode(vo.aryImportHeaderCode[j]);
								dto.setAmount( Float.valueOf(util.toHankakuKana(aryImportData[i][j])));
								listData.add(dto);
								break;
							}
						}
					}
				}
				PiPaymentDao piPaymentDao = new PiPaymentDao(cfg, cmd, aspUser, user, conn);
				piPaymentDao.initDao(cfg, cmd, aspUser, user, conn);
				targetMonth = MospUtility.getDate( In_Calc_Nen   + '/' +  In_Calc_Thuki  + '/' + In_Calc_hi);
				for (Iterator<DetailTimeDto> it = listData.iterator(); it.hasNext();) {
					dto = it.next();
					dto.setKCode(In_Kcode);
					dto.setCalcDate(targetMonth);
					piPaymentDao.delete(In_Kcode , targetMonth);
				}

				dto = new DetailTimeDto();
				dto.setKCode(In_Kcode);
				dto.setCalcDate(targetMonth);
				dto.setDetailCode( PayrollConst.DTL_SNEN );
				dto.setAmount( Float.valueOf( In_Pay_Nen ));
				listData.add(dto);

				dto = new DetailTimeDto();
				dto.setKCode(In_Kcode);
				dto.setCalcDate(targetMonth);
				dto.setDetailCode( PayrollConst.DTL_STUKI );
				dto.setAmount( Float.valueOf( In_Pay_Thuki ));
				listData.add(dto);
				
				dto = new DetailTimeDto();
				dto.setKCode(In_Kcode);
				dto.setCalcDate(targetMonth);
				dto.setDetailCode( PayrollConst.DTL_SHI );
				dto.setAmount( Float.valueOf( In_In_Pay_hi ));
				listData.add(dto);
				
				piPaymentDao.insert(listData);
				commit();
				regCount++;
			}
			if (aryImportData.length == 0) {
				vo.addErrMessage(MospUtility.getMessage(msg, CommonConst.MSG_NO_CSV_DATA, ""));
			}else{
				// メッセージの設定
				vo.setMessage(MospUtility.getMessage(msg, "IP9040", String.valueOf(regCount)));
			}
		} else {
			vo.addErrMessage(MospUtility.getMessage(msg, CommonConst.MSG_INVALID_HEAD_TYPE, ""));
		}

	}

	
	/**
	 * 人事基本情報取得
	 * @param kCode       社員コード
	 * @param isForUpdate アップデート用フラグ
	 * @throws NoSuchFieldException
	 * @throws IllegalAccessException
	 * @throws SQLException
	 */
	protected void findHumanBasisInfo(
			String kCode,
			boolean isForUpdate
	) throws SQLException, IllegalAccessException, NoSuchFieldException {
		// 人事基本情報用DAO準備
		prepareHumanBasisDao();
		// 現在の情報を取得
		if (isForUpdate) {
			mKihonDto      = mKihonDao.findForUpdate(kCode);
		} else {
			mKihonDto      = mKihonDao.findForKey(kCode);
		}
	}
	
	/**
	 * 人事基本情報用DAO準備
	 */
	protected void prepareHumanBasisDao() {
		// DAO準備
		if (mKihonDao != null) {
			return;
		}
		mKihonDao = new MKihonDao();
		mKihonDao.initDao(cfg, cmd, aspUser, user, conn);
	}
	
}
