/*
 * 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.report.action;

import java.io.File;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;

import org.jopendocument.dom.spreadsheet.Sheet;
import org.jopendocument.dom.spreadsheet.SpreadSheet;

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.MospUtility;
import jp.mosp.common.common.PoiUtility;
import jp.mosp.common.part.KintaiCommonPart;
import jp.mosp.common.part.StringPart;

import jp.mosp.kintai.dto.MSyukkinboDto;
import jp.mosp.kintai.vo.AttendanceListVo;

public class AttendanceTableAction extends AttendanceTotalAction {

	// 印刷プログラム制御変数
	private int r			= 0;	//Row(行) 0〜
	private int pageno		= 0;	//ページ数
	private int endpagel	= 72;	//1ページの最終行数
	private int gyol		= 2;	//明細の改行数
	private int titlel		= 3;	//1ページのタイトル印刷位置
	private int firstl		= 7;	//1ページの明細開始位置
	private int totall		= 69;	//1ページの合計印刷行
	private int printcnt	= 0;
	// エクセル・セル番号
	private int cc[]   	= {1,4,6,8,13,18,23,28,33,39,43,47,51,55,61};
	// 合計項目定義 0-14上段　15-29下段
	private int total[]    = new int[30];

	// OpenDocument用
	private static final int DETAIL_ROW		= 6;	// 1ページの明細開始位置
	private static final int TOTAL_ROW		= 68;	// 1ページの合計印刷行

	private static final String CELL_YEAR 	= "H3";		// 年
	private static final String CELL_MONTH 	= "M3";		// 月
	private static final String CELL_K_NAME 	= "Y3";		// 社員名
	private static final String CELL_SECTION 	= "AR3";	// 所属名

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

	/**
	 * アクション
	 */
	public void action() throws Exception {		
		// DBコネクション取得
		getConnection();
		// VOの取得
		AttendanceListVo vo = (AttendanceListVo) prepareVo();
		// VO及びフォワード先URLの設定
		setVo(vo);
		setUrl(vo.getRetUrl());
		// パラメータ取得
		vo.setParams(request);
		// 表示月
		Date targetMonth = util.getYearMonth(vo.getPltYear(), vo.getPltMonth());
		// データ取得
		String kCode = vo.getHidKCode();
		// 帳票設定
		StringBuffer fileName = new StringBuffer();
		fileName.append(util.convDateToStringYearMonth(targetMonth));
		fileName.append(CommonConst.FILE_NAME_SEPARATOR);
		fileName.append(kCode);
		fileName.append(CommonConst.FILE_NAME_SEPARATOR);
		// 帳票作成
		if (StringPart.isNotNull(cfg.getProperty(CommonConst.PPT_KINTAI_USE_ODF))) {
			// ODS
			request.setAttribute(MospConst.ATT_FILE_OBJECT,
					makeSpreadSheet(kCode, targetMonth)
			);
			// ファイル名
			fileName.append(CommonConst.FILE_ATTENDANCE_TABLE_ODS);
		} else {
			// XLS
			request.setAttribute(MospConst.ATT_FILE_OBJECT,
					makeWorkbook(kCode, targetMonth).getWorkbook()
			);
			// ファイル名
			fileName.append(CommonConst.FILE_ATTENDANCE_TABLE);
		}
		request.setAttribute(MospConst.ATT_FILE_NAME, fileName.toString());
	}

	protected BaseVo getSpecificVo() {
		return new AttendanceListVo();
	}

	/**
	 * 勤務表作成
	 * @param targetMonth       対象月
	 * @return PoiUtilityインスタンス
	 * @throws Exception 
	 */
	private PoiUtility makeWorkbook(String kCode, Date targetMonth) throws Exception {
		// 帳票作成準備
		PoiUtility poi = new PoiUtility();
		poi.crtNewDocR(0, cfg.getProperty(MospConst.PPT_APP_DOCBASE) + CommonConst.TEMPLATE_M_KINMUHYO);
		// ヘッダー情報設定
		prtTitle(kCode, targetMonth, poi);
		// 日集計データ配列作成
		makeAttendance(kCode, targetMonth);
		// データ情報設定
		prtDetail(poi);
		if (printcnt > 0) {
			// 総合計印刷
			prtTotal(poi);
		}				
		return poi;
	}

	/**
	 * タイトル印刷メソッド
	 * @param targetMonth
	 * @param poi
	 * @throws Exception
	 */
	private void prtTitle(String kCode, Date targetMonth, PoiUtility poi) throws Exception{
		// 改ページ
		pageno += 1;
		// タイトル印刷位置算出
		r = (pageno - 1) * endpagel + (titlel - 1);
		// 年月
		poi.prtItem(r,  7, MospUtility.getYear (targetMonth));
		poi.prtItem(r, 12, MospUtility.getMonth(targetMonth));
		// 氏名
		poi.prtItem(r, 24, getKName(kCode));
		// 所属
		poi.prtItem(r, 43, getSectionName(getBelongSectionCode(kCode)));
	}

	/**
	 * 明細印刷メソッド
	 * @param poi
	 * @throws Exception
	 */
	private void prtDetail(PoiUtility poi) throws Exception{
		// 明細印刷位置算出
		r = (pageno - 1) * endpagel + (firstl - 1 - gyol);
		// 改行
		r += gyol;
		// 上段項目設定
		for (int i = 0; i < aryAttendance.length; i++) {
			// セルの結合
			poi.addMergedRegion(r,  8, r, 12);
			poi.addMergedRegion(r, 13, r, 17);
			poi.addMergedRegion(r, 18, r, 22);
			poi.addMergedRegion(r, 23, r, 27);
			poi.addMergedRegion(r, 28, r, 32);
			poi.addMergedRegion(r, 33, r, 38);
			poi.addMergedRegion(r, 39, r, 42);
			poi.addMergedRegion(r, 43, r, 46);
			poi.addMergedRegion(r, 47, r, 50);
			poi.addMergedRegion(r, 51, r, 54);
			poi.addMergedRegion(r, 55, r, 60);
			poi.addMergedRegion(r, 61, r, 67);

			poi.addMergedRegion(r+1,  8, r+1, 12);
			poi.addMergedRegion(r+1, 13, r+1, 17);
			poi.addMergedRegion(r+1, 18, r+1, 22);
			poi.addMergedRegion(r+1, 23, r+1, 27);
			poi.addMergedRegion(r+1, 28, r+1, 32);
			poi.addMergedRegion(r+1, 33, r+1, 38);
			poi.addMergedRegion(r+1, 39, r+1, 42);
			poi.addMergedRegion(r+1, 43, r+1, 46);
			poi.addMergedRegion(r+1, 47, r+1, 50);
			poi.addMergedRegion(r+1, 51, r+1, 54);
			poi.addMergedRegion(r+1, 55, r+1, 60);
			poi.addMergedRegion(r+1, 61, r+1, 67);
			// 上段
			poi.prtItem(r, cc[0], aryAttendance[i][1]);
			poi.prtItem(r, cc[1], aryAttendance[i][2]);
			poi.prtItem(r, cc[2], aryAttendance[i][3]);
			if (aryAttendance[i][4].length() != 0) {
				poi.prtItem(r, cc[ 3], aryAttendance[i][ 4]);
				poi.prtItem(r, cc[ 4], aryAttendance[i][ 5]);
				poi.prtItem(r, cc[ 5], aryAttendance[i][ 6]);
				poi.prtItem(r, cc[ 6], aryAttendance[i][ 7]);
				poi.prtItem(r, cc[ 7], aryAttendance[i][ 8]);
				poi.prtItem(r, cc[ 8], aryAttendance[i][ 9]);
				poi.prtItem(r, cc[ 9], aryAttendance[i][10]);
				poi.prtItem(r, cc[10], aryAttendance[i][11]);
				poi.prtItem(r, cc[11], aryAttendance[i][12]);
				poi.prtItem(r, cc[12], aryAttendance[i][13]);
				poi.prtItem(r, cc[13], aryAttendance[i][14]);
				poi.prtItem(r, cc[14], aryAttendance[i][15]);
				// 下段
				poi.prtItem(r+1, cc[ 3], aryAttendance[i][16]);
				poi.prtItem(r+1, cc[ 4], aryAttendance[i][17]);
				poi.prtItem(r+1, cc[ 5], aryAttendance[i][18]);
				poi.prtItem(r+1, cc[ 7], aryAttendance[i][19]);
				poi.prtItem(r+1, cc[ 8], aryAttendance[i][20]);
				poi.prtItem(r+1, cc[ 9], aryAttendance[i][21]);
				poi.prtItem(r+1, cc[10], aryAttendance[i][22]);
				poi.prtItem(r+1, cc[11], aryAttendance[i][23]);
				poi.prtItem(r+1, cc[12], aryAttendance[i][24]);
				poi.prtItem(r+1, cc[13], aryAttendance[i][25]);
			}
			r += 2;
			// 明細印刷件数
			printcnt++;
		}
	}

	/**
	 * 総合計印刷メソッド
	 * @param poi
	 * @throws Exception
	 */
	private void prtTotal(PoiUtility poi) throws Exception{	
		// 合計印刷位置算出
		r = (pageno - 1) * endpagel + (totall - 1);
		poi.addMergedRegion(r,  8, r, 12);
		poi.addMergedRegion(r, 13, r, 17);
		poi.addMergedRegion(r, 18, r, 22);
		poi.addMergedRegion(r, 23, r, 27);
		poi.addMergedRegion(r, 28, r, 32);
		poi.addMergedRegion(r, 33, r, 38);
		poi.addMergedRegion(r, 39, r, 42);
		poi.addMergedRegion(r, 43, r, 46);
		poi.addMergedRegion(r, 47, r, 50);
		poi.addMergedRegion(r, 51, r, 54);
		poi.addMergedRegion(r, 55, r, 60);
		poi.addMergedRegion(r, 61, r, 67);
		poi.addMergedRegion(r+1,  8, r+1, 12);
		poi.addMergedRegion(r+1, 13, r+1, 17);
		poi.addMergedRegion(r+1, 18, r+1, 22);
		poi.addMergedRegion(r+1, 23, r+1, 27);
		poi.addMergedRegion(r+1, 28, r+1, 32);
		poi.addMergedRegion(r+1, 33, r+1, 38);
		poi.addMergedRegion(r+1, 39, r+1, 42);
		poi.addMergedRegion(r+1, 43, r+1, 46);
		poi.addMergedRegion(r+1, 47, r+1, 50);
		poi.addMergedRegion(r+1, 51, r+1, 54);
		poi.addMergedRegion(r+1, 55, r+1, 60);
		poi.addMergedRegion(r+1, 61, r+1, 67);
		// 上段項目
		poi.prtItem(r, cc[ 4], getHourString(util.convIntegerTimeToDoubleTime(total[4])));
		poi.prtItem(r, cc[ 5], getHourString(util.convIntegerTimeToDoubleTime(total[5])));
		poi.prtItem(r, cc[ 6], getHourString(util.convIntegerTimeToDoubleTime(total[6])));
		poi.prtItem(r, cc[ 7], getHourString(util.convIntegerTimeToDoubleTime(total[7])));
		poi.prtItem(r, cc[ 9], total[9]);
		poi.prtItem(r, cc[10], total[10]);
		poi.prtItem(r, cc[11], total[11]);
		poi.prtItem(r, cc[13], getHourString(util.convIntegerTimeToDoubleTime(total[13])));
		// 下段項目
		poi.prtItem(r+1, cc[ 4], getHourString(util.convIntegerTimeToDoubleTime(total[19])));
		poi.prtItem(r+1, cc[ 5], getHourString(util.convIntegerTimeToDoubleTime(total[20])));
		poi.prtItem(r+1, cc[ 7], getHourString(util.convIntegerTimeToDoubleTime(total[22])));
		poi.prtItem(r+1, cc[ 9], total[24]);
		poi.prtItem(r+1, cc[10], total[25]);
		poi.prtItem(r+1, cc[11], total[26]);
		// 明細印刷件数
		printcnt++;
	}


	/**
	 * 勤務表作成
	 * @param targetMonth       対象年月
	 * @return SpreadSheetインスタンス
	 * @throws Exception 
	 */
	private SpreadSheet makeSpreadSheet(String kCode, Date targetMonth) throws Exception {
		// テンプレートファイル
		StringBuffer sb = new StringBuffer();
		sb.append(cfg.getProperty(MospConst.PPT_APP_DOCBASE));
		sb.append(CommonConst.TEMPLATE_M_KINMUHYO_ODS);
		// 帳票作成
		SpreadSheet ss = SpreadSheet.createFromFile(new File(sb.toString()));
		// シート
		Sheet sheet = ss.getSheet(0);
		// 年
		sheet.getCellAt(CELL_YEAR).setValue(MospUtility.getYear(targetMonth));
		// 月
		sheet.getCellAt(CELL_MONTH).setValue(MospUtility.getMonth(targetMonth));
		// 社員名
		sheet.getCellAt(CELL_K_NAME).setValue(getKName(kCode));
		// 所属名
		sheet.getCellAt(CELL_SECTION).setValue(getSectionName(getBelongSectionCode(kCode)));
		// 日集計データ配列作成
		makeAttendance(kCode, targetMonth);
		// 明細印刷
		printDetail(sheet);
		// 総合計印刷
		printTotal(sheet);			
		return ss;
	}

	/**
	 * 明細印刷メソッド
	 * @param sheet
	 * @throws Exception
	 */
	private void printDetail(Sheet sheet) throws Exception{
		// 項目設定
		int row = DETAIL_ROW;
		for (int i = 0; i < aryAttendance.length; i++) {
			// 上段
			sheet.getCellAt(cc[0], row).setValue(aryAttendance[i][1]);
			sheet.getCellAt(cc[1], row).setValue(aryAttendance[i][2]);
			sheet.getCellAt(cc[2], row).setValue(aryAttendance[i][3]);
			if (aryAttendance[i][4].length() != 0) {
				sheet.getCellAt(cc[ 3], row).setValue(aryAttendance[i][ 4]);
				sheet.getCellAt(cc[ 4], row).setValue(aryAttendance[i][ 5]);
				sheet.getCellAt(cc[ 5], row).setValue(aryAttendance[i][ 6]);
				sheet.getCellAt(cc[ 6], row).setValue(aryAttendance[i][ 7]);
				sheet.getCellAt(cc[ 7], row).setValue(aryAttendance[i][ 8]);
				sheet.getCellAt(cc[ 8], row).setValue(aryAttendance[i][ 9]);
				sheet.getCellAt(cc[ 9], row).setValue(aryAttendance[i][10]);
				sheet.getCellAt(cc[10], row).setValue(aryAttendance[i][11]);
				sheet.getCellAt(cc[11], row).setValue(aryAttendance[i][12]);
				sheet.getCellAt(cc[12], row).setValue(aryAttendance[i][13]);
				sheet.getCellAt(cc[13], row).setValue(aryAttendance[i][14]);
				sheet.getCellAt(cc[14], row).setValue(aryAttendance[i][15]);
				// 下段
				int lower = row + 1;
				sheet.getCellAt(cc[ 3], lower).setValue(aryAttendance[i][16]);
				sheet.getCellAt(cc[ 4], lower).setValue(aryAttendance[i][17]);
				sheet.getCellAt(cc[ 5], lower).setValue(aryAttendance[i][18]);
				sheet.getCellAt(cc[ 7], lower).setValue(aryAttendance[i][19]);
				sheet.getCellAt(cc[ 8], lower).setValue(aryAttendance[i][20]);
				sheet.getCellAt(cc[ 9], lower).setValue(aryAttendance[i][21]);
				sheet.getCellAt(cc[10], lower).setValue(aryAttendance[i][22]);
				sheet.getCellAt(cc[11], lower).setValue(aryAttendance[i][23]);
				sheet.getCellAt(cc[12], lower).setValue(aryAttendance[i][24]);
				sheet.getCellAt(cc[13], lower).setValue(aryAttendance[i][25]);
			}
			row += 2;
		}
	}

	/**
	 * 総合計印刷メソッド
	 * @param sheet
	 * @throws Exception
	 */
	private void printTotal(Sheet sheet) throws Exception{	
		// 上段項目
		int upper = TOTAL_ROW;
		sheet.getCellAt(cc[ 4], upper).setValue(getHourString(util.convIntegerTimeToDoubleTime(total[4])));
		sheet.getCellAt(cc[ 5], upper).setValue(getHourString(util.convIntegerTimeToDoubleTime(total[5])));
		sheet.getCellAt(cc[ 6], upper).setValue(getHourString(util.convIntegerTimeToDoubleTime(total[6])));
		sheet.getCellAt(cc[ 7], upper).setValue(getHourString(util.convIntegerTimeToDoubleTime(total[7])));
		sheet.getCellAt(cc[ 9], upper).setValue(total[9]);
		sheet.getCellAt(cc[10], upper).setValue(total[10]);
		sheet.getCellAt(cc[11], upper).setValue(total[11]);
		sheet.getCellAt(cc[13], upper).setValue(getHourString(util.convIntegerTimeToDoubleTime(total[13])));
		// 下段項目
		int lower = upper +1;
		sheet.getCellAt(cc[ 4], lower).setValue(getHourString(util.convIntegerTimeToDoubleTime(total[19])));
		sheet.getCellAt(cc[ 5], lower).setValue(getHourString(util.convIntegerTimeToDoubleTime(total[20])));
		sheet.getCellAt(cc[ 7], lower).setValue(getHourString(util.convIntegerTimeToDoubleTime(total[22])));
		sheet.getCellAt(cc[ 9], lower).setValue(total[24]);
		sheet.getCellAt(cc[10], lower).setValue(total[25]);
		sheet.getCellAt(cc[11], lower).setValue(total[26]);
	}

	/**
	 * 日集計データ配列作成
	 * @param kCode			対象社員コード
	 * @param targetMonth	対象年月
	 * @throws Exception
	 */
	private void makeAttendance(String kCode, Date targetMonth) throws Exception {
		// 勤怠月一覧データ取得 
		getAttendanceList(kCode, targetMonth);
		// データ数取得
		Calendar cal = Calendar.getInstance();
		cal.setTime(MospUtility.getDate(startDate));
		int count = 0;
		while (MospUtility.getDate(endDate).compareTo(cal.getTime()) >= 0) {
			cal.add(Calendar.DAY_OF_MONTH, 1);
			count++;
		}
		// 配列初期化
		aryAttendance = new String[count][40];
		for (int i = 0; i < aryAttendance.length; i++) {
			for (int j = 0; j < aryAttendance[i].length; j++) {
				aryAttendance[i][j] = "";
			}
		}
		// 日付設定
		cal.setTime(MospUtility.getDate(startDate));
		for (int i = 0; i < aryAttendance.length; i++) {
			// 日付（検索用）
			aryAttendance[i][0] = MospUtility.getDateString(cal.getTime(), CommonConst.FORMAT_DATE);
			// 日付
			aryAttendance[i][1] = String.valueOf(cal.get(Calendar.DAY_OF_MONTH));
			// 曜日
			aryAttendance[i][2] = getDayOfWeek(cal.get(Calendar.DAY_OF_WEEK));
			cal.add(Calendar.DAY_OF_MONTH, 1);
		}
		// データの設定
		Iterator<MSyukkinboDto> it = attendanceList.iterator();
		while (it.hasNext()) {
			cal.setTime(MospUtility.getDate(startDate));
			MSyukkinboDto dto = it.next();
			Date attendanceDate = MospUtility.getDate(dto.getHizuke());
			for (int i = 0; i < aryAttendance.length; i++) {
				if (attendanceDate.compareTo(cal.getTime()) == 0) {
					// 勤務形態
					if (KintaiCommonPart.isAvailableWorkType(dto)) {
						aryAttendance[i][4 ] = dto.getSyJikoku();
						aryAttendance[i][5 ] = getHourString(util.convIntegerTimeToDoubleTime(dto.getKinmuJi()));
						aryAttendance[i][6 ] = getHourString(util.convIntegerTimeToDoubleTime(dto.getZaJikan()));
						aryAttendance[i][7 ] = getHourString(util.convIntegerTimeToDoubleTime(dto.getSnJikan()));
						aryAttendance[i][8 ] = getHourString(util.convIntegerTimeToDoubleTime(dto.getTiJikan()));
						aryAttendance[i][9 ] = util.getCodeName(dto.getTiRiyuu(), getMosPCodeArray(CommonConst.TID_LATE_REASON));
						aryAttendance[i][10] = String.valueOf(dto.getSyokuji());
						aryAttendance[i][11] = String.valueOf(dto.getCyoku2());
						aryAttendance[i][12] = String.valueOf(dto.getHoka1());
						aryAttendance[i][13] = dto.getSHankyuKb();
						aryAttendance[i][14] = getHourString(util.convIntegerTimeToDoubleTime(dto.getKKJikan()));
						aryAttendance[i][15] = dto.getKKRiyuu();
						aryAttendance[i][16] = dto.getTaJikoku();
						aryAttendance[i][17] = getHourString(util.convIntegerTimeToDoubleTime(dto.getKyukeiJi()));
						aryAttendance[i][18] = getHourString(util.convIntegerTimeToDoubleTime(dto.getKsJikan()));
						aryAttendance[i][19] = getHourString(util.convIntegerTimeToDoubleTime(dto.getSoJikan()));
						aryAttendance[i][20] = util.getCodeName(dto.getSoRiyuu(), getMosPCodeArray(CommonConst.TID_LEAVE_EARLY_REASON));
						aryAttendance[i][21] = String.valueOf(dto.getCyoku1());
						aryAttendance[i][22] = String.valueOf(dto.getCyoku3());
						aryAttendance[i][23] = String.valueOf(dto.getHoka2());
						aryAttendance[i][24] = dto.getTHankyuKb();
						aryAttendance[i][25] = dto.getDaiKyuJyu();
						total[4] 	+=  dto.getKinmuJi();
						total[5] 	+=  dto.getZaJikan();
						total[6] 	+=  dto.getSnJikan();
						total[7] 	+=  dto.getTiJikan();
						total[9] 	+=  dto.getSyokuji();
						total[10] 	+=  dto.getCyoku2();
						total[11] 	+=  dto.getHoka1();
						total[13] 	+=  dto.getKKJikan();
						total[19] 	+=  dto.getKyukeiJi();
						total[20] 	+=  dto.getKsJikan();
						total[22] 	+=  dto.getSoJikan();
						total[24] 	+=  dto.getCyoku1();
						total[25] 	+=  dto.getCyoku3();						
						total[26] 	+=  dto.getHoka2();
					}
					aryAttendance[i][3 ] = util.getCodeName(
							dto.getKinmu(),
							getWorkTypeArrayAllWithWeekHoliday()
					);
					break;
				}
				cal.add(Calendar.DAY_OF_MONTH, 1);
			}
		}
	}

}