package jp.co.imtt.businesssystem.util;

import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.ArrayList;
import java.util.Iterator;

import org.apache.cayenne.DataRow;
import org.apache.cayenne.access.DataContext;
import org.apache.cayenne.exp.Expression;
import org.apache.cayenne.exp.ExpressionFactory;
import org.apache.cayenne.query.SelectQuery;
import org.apache.turbine.util.RunData;
import org.apache.velocity.context.Context;

import com.aimluck.commons.field.ALDateTimeField;
import com.aimluck.eip.cayenne.om.portlet.EipTExtTimecard;
import com.aimluck.eip.cayenne.om.portlet.EipTExtTimecardSystem;
import com.aimluck.eip.cayenne.om.portlet.EipTExtTimecardSystemMap;
import com.aimluck.eip.cayenne.om.portlet.ImwTDocumentFile;
import com.aimluck.eip.cayenne.om.portlet.ImwTExtTimecardSystemDetail;
import com.aimluck.eip.cayenne.om.portlet.ImwTExtTimecardDetail;
import com.aimluck.eip.cayenne.om.security.TurbineUser;
import com.aimluck.eip.orm.DatabaseOrmService;
import com.aimluck.eip.util.ALEipUtils;

import org.apache.jetspeed.services.logging.JetspeedLogFactoryService;
import org.apache.jetspeed.services.logging.JetspeedLogger;
import org.apache.cayenne.query.SQLTemplate;

/**
 * 社内業務システムのユーティリティクラスです。 <BR>
 * 
 */
public class BusinessSystemUtils {

  /** logger */
  private static final JetspeedLogger logger = JetspeedLogFactoryService
      .getLogger(BusinessSystemUtils.class.getName());
  
  /**
   * 出勤時間の丸め処理をします。 <BR>
   * 
   * @param targetDate
   * @param userId
   * @param systemId
   * @return
   */
  public static Calendar getStartTime(Date targetDate, Integer userId, Integer systemId) {

    DataContext dataContext = DatabaseOrmService.getInstance().getDataContext();
    String paidHolidayType = "";
    Integer worktimeMinute = 0;
    Integer changeHour = 0;
    ALDateTimeField alTargetDate = new ALDateTimeField("yyyy-MM-dd");
    alTargetDate.setValue(targetDate);

    // 戻り値の初期化
    Integer workTime = 0;
    Calendar returnTime = Calendar.getInstance();
    returnTime.set(Calendar.SECOND, 0);
    returnTime.set(Calendar.MILLISECOND, 0);

    // 現在時刻の取得
    Calendar cal = Calendar.getInstance();
    Integer nowTime = (cal.get(Calendar.HOUR_OF_DAY) * 60) + cal.get(Calendar.MINUTE);

    // 拡張タイムカードの読み込み
    SelectQuery query1 = new SelectQuery(EipTExtTimecard.class);
    Expression exp11 = ExpressionFactory.matchExp(EipTExtTimecard.USER_ID_PROPERTY, userId);
    query1.setQualifier(exp11);   // ユーザＩＤ
    Expression exp12 = ExpressionFactory.matchExp(EipTExtTimecard.PUNCH_DATE_PROPERTY, alTargetDate.getValue());
    query1.andQualifier(exp12);   // 対象日
    List eipTExtTimecardList = dataContext.performQuery(query1);
    if (eipTExtTimecardList != null && eipTExtTimecardList.size() > 0) {
      // レコードが存在する場合
      EipTExtTimecard eipTExtTimecard = (EipTExtTimecard) eipTExtTimecardList.get(0);
      if (eipTExtTimecard.getPaidHolidayType() != null) {
        paidHolidayType = eipTExtTimecard.getPaidHolidayType();
      }
    }

 /** Delete 2010/10/14 金廣 Start--- */
 // 課題63:午前休の日の午前中に出勤し作業実績を入力すると、出勤時間から昼休憩までの時間が欠勤時間として表示される問題の対応    
//    // 始業時間の取得
//    SelectQuery query0 = new SelectQuery(EipTExtTimecardSystem.class);
//    Expression exp01 = ExpressionFactory.matchDbExp(EipTExtTimecardSystem.SYSTEM_ID_PK_COLUMN, systemId);
//    query0.setQualifier(exp01);   // 勤務形態ＩＤ
//    if (paidHolidayType.equals("2")) {
//      // 半休（午前）の場合
//      Expression exp02 = ExpressionFactory.matchExp(
//          EipTExtTimecardSystem.IMW_TEXT_TIMECARD_SYSTEM_DETAIL_PROPERTY + "." +
//          ImwTExtTimecardSystemDetail.WORKTIME_STATUS_PROPERTY, "1");
//      query0.andQualifier(exp02);   // 勤務時間種別（0：所定内(午前)　1：所定内(午後)　2：普通残業　3：深夜残業）
//    } else {
//      // 半休（午前）以外の場合
//      Expression exp02 = ExpressionFactory.matchExp(
//          EipTExtTimecardSystem.IMW_TEXT_TIMECARD_SYSTEM_DETAIL_PROPERTY + "." +
//          ImwTExtTimecardSystemDetail.WORKSTART_FLAG_PROPERTY, "1");
//      query0.andQualifier(exp02);   // 始業時刻フラグ
//    }
//    List workstartList = dataContext.performQuery(query0);
//    Integer startTime = 0;
//    if (workstartList == null || workstartList.size() == 0) {
//      // 始業時間が見つからない場合
//      logger.debug("[ImwTExtTimecardSystemDetail] Empty ID & WORKSTART_FLAG...");
//      return null;
//    } else {
//      // 始業時間が存在する場合
//      EipTExtTimecardSystem eipTExtTimecardSystem = (EipTExtTimecardSystem) workstartList.get(0);
//      ImwTExtTimecardSystemDetail workstart = (ImwTExtTimecardSystemDetail) eipTExtTimecardSystem.getImwTExtTimecardSystemDetail().get(0);
//      startTime = (workstart.getStartHour() * 60) + workstart.getStartMinute();
//    }
/** Delete 2010/10/14 金廣 End----- */
    
    // 拡張タイムカード勤務形態の読み込み
    SelectQuery query2 = new SelectQuery(EipTExtTimecardSystem.class);
    Expression exp21 = ExpressionFactory.matchDbExp(EipTExtTimecardSystem.SYSTEM_ID_PK_COLUMN, systemId);
    query2.setQualifier(exp21);   // 勤務形態ＩＤ
    List eipTExtTimecardSystemList = dataContext.performQuery(query2);
    if (eipTExtTimecardSystemList == null || eipTExtTimecardSystemList.size() == 0) {
      // レコードが見つからない場合
      logger.debug("[EipTExtTimecardSystem] Empty ID...");
      return null;
    } else {
      // レコードが存在する場合
      EipTExtTimecardSystem eipTExtTimecardSystem = (EipTExtTimecardSystem) eipTExtTimecardSystemList.get(0);
      worktimeMinute = eipTExtTimecardSystem.getWorktimeMinute();
      changeHour = eipTExtTimecardSystem.getChangeHour();
      if ( changeHour > cal.get(Calendar.HOUR_OF_DAY)) {
        // 日付変更時刻以前の場合は、２４時間を加算する。
        nowTime = nowTime + (24 * 60);
      }
    }
    
    /** Delete 2010/10/14 金廣 Start--- */
    // 課題63:午前休の日の午前中に出勤し作業実績を入力すると、出勤時間から昼休憩までの時間が欠勤時間として表示される問題の対応    
//    // 始業時間以前の出社は始業時間に合わせる。
//    if (nowTime <= startTime) {
//      returnTime.set(Calendar.HOUR_OF_DAY, startTime / Integer.valueOf(60));
//      returnTime.set(Calendar.MINUTE, startTime % Integer.valueOf(60));
//      return returnTime;
//    }
    /** Delete 2010/10/14 金廣 End----- */
    
    // タイムテーブルの取得
    /** Update 2010/10/15 金廣 Start--- */
    // 課題63:午前休の日の午前中に出勤し作業実績を入力すると、出勤時間から昼休憩までの時間が欠勤時間として表示される問題の対応
    // List<TimeTableRow> timeTableList = getTimeTable(systemId, startTime);
    int paidHoliday = 0;
    try {
      paidHoliday = Integer.valueOf(paidHolidayType);
    } catch(NumberFormatException ex){
      logger.error(ex);
      paidHoliday = 0;
    }
    List<TimeTableRow> timeTableList = getTimeTable(systemId, changeHour * 60, (changeHour + 24) * 60, paidHoliday);
    Integer startTime = 0;
    // 午前休の場合は所定内(午後)、それ以外の場合は所定内（午前）の最初の開始時刻を始業時刻とする
    int startWorkStatus = 0;
    if (paidHolidayType.equals("2")){
      startWorkStatus = 1;
    }
    // 始業時刻をタイムテーブルから取得
    for (TimeTableRow row : timeTableList){
      if(row.TimeKind == startWorkStatus){
        startTime = row.StartMinutes;
        break;
      }
    }
    
    // 始業時間以前の出社は始業時間に合わせる。
    if (nowTime <= startTime) {
      returnTime.set(Calendar.HOUR_OF_DAY, startTime / Integer.valueOf(60));
      returnTime.set(Calendar.MINUTE, startTime % Integer.valueOf(60));
      return returnTime;
    }
    /** Update 2010/10/15 金廣 Start--- */
    
    int recordNum = timeTableList.size();
    for (int i = 0; i < recordNum; i++) {
      TimeTableRow timeTableRow = (TimeTableRow) timeTableList.get(i);
      if (timeTableRow.StartMinutes > nowTime) {
        // 休憩時間中は作業開始時刻に合わせる。
        workTime = timeTableRow.StartMinutes;
        break;
      } else if (timeTableRow.StartMinutes <= nowTime &&
                 timeTableRow.EndMinutes >= nowTime + worktimeMinute) {
        // 作業時間中は直近の作業時間単位に合わせる。
        Integer diffTime = nowTime - timeTableRow.StartMinutes;
        workTime = timeTableRow.StartMinutes + (diffTime / worktimeMinute) * worktimeMinute;
        if (diffTime % worktimeMinute != 0) {
          workTime = workTime + worktimeMinute;
        }
        break;
      }
    }

    // 戻り値の設定
    if ( changeHour > cal.get(Calendar.HOUR_OF_DAY)) {
      // 日付変更時刻以前の場合は、２４時間を減算する。
      workTime = workTime - (24 * 60);
    }
    returnTime.set(Calendar.HOUR_OF_DAY, workTime / Integer.valueOf(60));
    returnTime.set(Calendar.MINUTE, workTime % Integer.valueOf(60));
    return returnTime;
  }
  
  /**
   * 退勤時間の丸め処理をします。 <BR>
   * 
   * @param targetDate
   * @param userId
   * @param systemId
   * @return
   */
  public static Calendar getEndTime(Date targetDate, Integer userId, Integer systemId) {

    DataContext dataContext = DatabaseOrmService.getInstance().getDataContext();
    String paidHolidayType = "";
    Integer worktimeMinute = 0;
    Integer changeHour = 0;
    ALDateTimeField alTargetDate = new ALDateTimeField("yyyy-MM-dd");
    alTargetDate.setValue(targetDate);

    // 戻り値の初期化
    Integer workTime = 0;
    Calendar returnTime = Calendar.getInstance();
    returnTime.set(Calendar.SECOND, 0);
    returnTime.set(Calendar.MILLISECOND, 0);

    // 現在時刻の取得
    Calendar cal = Calendar.getInstance();
    Integer nowTime = (cal.get(Calendar.HOUR_OF_DAY) * 60) + cal.get(Calendar.MINUTE);

    // 拡張タイムカードの読み込み
    SelectQuery query1 = new SelectQuery(EipTExtTimecard.class);
    Expression exp11 = ExpressionFactory.matchExp(EipTExtTimecard.USER_ID_PROPERTY, userId);
    query1.setQualifier(exp11);   // ユーザＩＤ
    Expression exp12 = ExpressionFactory.matchExp(EipTExtTimecard.PUNCH_DATE_PROPERTY, alTargetDate.getValue());
    query1.andQualifier(exp12);   // 対象日
    List eipTExtTimecardList = dataContext.performQuery(query1);
    if (eipTExtTimecardList != null && eipTExtTimecardList.size() > 0) {
      // レコードが存在する場合
      EipTExtTimecard eipTExtTimecard = (EipTExtTimecard) eipTExtTimecardList.get(0);
      if (eipTExtTimecard.getPaidHolidayType() != null) {
        paidHolidayType = eipTExtTimecard.getPaidHolidayType();
      }
    }

    // 拡張タイムカード勤務形態の読み込み
    SelectQuery query2 = new SelectQuery(EipTExtTimecardSystem.class);
    Expression exp21 = ExpressionFactory.matchDbExp(EipTExtTimecardSystem.SYSTEM_ID_PK_COLUMN, systemId);
    query2.setQualifier(exp21);   // 勤務形態ＩＤ
    List eipTExtTimecardSystemList = dataContext.performQuery(query2);
    if (eipTExtTimecardSystemList == null || eipTExtTimecardSystemList.size() == 0) {
      // レコードが見つからない場合
      logger.debug("[EipTExtTimecardSystem] Empty ID...");
      return null;
    } else {
      // レコードが存在する場合
      EipTExtTimecardSystem eipTExtTimecardSystem = (EipTExtTimecardSystem) eipTExtTimecardSystemList.get(0);
      worktimeMinute = eipTExtTimecardSystem.getWorktimeMinute();
      changeHour = eipTExtTimecardSystem.getChangeHour();
      if ( changeHour > cal.get(Calendar.HOUR_OF_DAY)) {
        // 日付変更時刻以前の場合は、２４時間を加算する。
        nowTime = nowTime + (24 * 60);
      }
    }
    
    // タイムテーブルの取得
    /** Update 2010/10/15 金廣 Start--- */
    // 課題51:午後休の日に残業を行った場合、在席管理で退勤ボタンを押すと12時になる。休憩時間に午後休が考慮されない問題の対応
    // List<TimeTableRow> timeTableList = getTimeTable(systemId, changeHour * 60);
    int paidHoliday = 0;
    try {
      paidHoliday = Integer.valueOf(paidHolidayType);
    } catch(NumberFormatException ex){
      logger.error(ex);
      paidHoliday = 0;
    }
    List<TimeTableRow> timeTableList = getTimeTable(systemId, changeHour * 60, (changeHour + 24) * 60, paidHoliday);
    /** Update 2010/10/15 金廣 End----- */
    int recordNum = timeTableList.size();
    for (int i = recordNum - 1; i >= 0; i--) {
      TimeTableRow timeTableRow = (TimeTableRow) timeTableList.get(i);
      /** Delete 2010/10/14 金廣 Start--- */
      // 課題51:午後休の日に残業を行った場合、在席管理で退勤ボタンを押すと12時になる。休憩時間に午後休が考慮されない問題の対応
      // 午後休の場合に、午前の終了時刻に合わせる処理を除去
//      if (paidHolidayType.equals("3") && 
//          timeTableRow.TimeKind == 0 && 
//          timeTableRow.EndMinutes < nowTime) {
//        // 半休（午後）の場合、午前の終了時刻に合わせる。
//        workTime = timeTableRow.EndMinutes;
//        break;
//      }
//      
//      if (!paidHolidayType.equals("3") ||
//          (paidHolidayType.equals("3") && 
//           timeTableRow.TimeKind == 0 && 
//           timeTableRow.EndMinutes >= nowTime)) {
        /** Delete 2010/10/14 金廣 End----- */
        if (timeTableRow.EndMinutes <= nowTime) {
          // 休憩時間中は作業終了時刻に合わせる。
          workTime = timeTableRow.EndMinutes;
          break;
        } else if (timeTableRow.StartMinutes <= nowTime - worktimeMinute &&
                   timeTableRow.EndMinutes >= nowTime) {
          // 作業時間中は直近の作業時間単位に合わせる。
          Integer diffTime = nowTime - timeTableRow.StartMinutes;
          workTime = timeTableRow.StartMinutes + (diffTime / worktimeMinute) * worktimeMinute;
          break;
        }
      /** Delete 2010/10/14 金廣 Start--- */
      // 課題51:午後休の日に残業を行った場合、在席管理で退勤ボタンを押すと12時になる。休憩時間に午後休が考慮されない問題の対応
      //}
      /** Delete 2010/10/14 金廣 End----- */
    }

    // 戻り値の設定
    if ( changeHour > cal.get(Calendar.HOUR_OF_DAY)) {
      // 日付変更時刻以前の場合は、２４時間を減算する。
      workTime = workTime - (24 * 60);
    }
    returnTime.set(Calendar.HOUR_OF_DAY, workTime / Integer.valueOf(60));
    returnTime.set(Calendar.MINUTE, workTime % Integer.valueOf(60));
    return returnTime;
  }
  
  /**
   * 算出退勤時間を更新します。 <BR>
   * 
   * @param targetDate
   * @param userId
   * @param systemId
   * @return
   */
  public static boolean setCalClockOutTime(Date targetDate, Integer userId, Integer systemId) {
    boolean rtn = false;

    // 算出退勤時間の算出
    Calendar calClockOutTime = getCalClockOutTime(targetDate, userId, systemId);
    
    DataContext dataContext = DatabaseOrmService.getInstance().getDataContext();

    ALDateTimeField alTargetDate = new ALDateTimeField("yyyy-MM-dd");
    alTargetDate.setValue(targetDate);
    
    // 拡張タイムカードの読み込み
    Integer timecardId = 0;
    SelectQuery query1 = new SelectQuery(EipTExtTimecard.class);
    Expression exp11 = ExpressionFactory.matchExp(EipTExtTimecard.USER_ID_PROPERTY, userId);
    query1.setQualifier(exp11);   // ユーザＩＤ
    Expression exp12 = ExpressionFactory.matchExp(EipTExtTimecard.PUNCH_DATE_PROPERTY, alTargetDate.getValue());
    query1.andQualifier(exp12);   // 対象日
    List eipTExtTimecardList = dataContext.performQuery(query1);
    if (eipTExtTimecardList == null || eipTExtTimecardList.size() <= 0) {
      logger.debug("[EipTExtTimecard] Empty ID & PUNCH_DATE...");
      return rtn;
    } else {
      // オブジェクトの取得
      EipTExtTimecard eipTExtTimecard = (EipTExtTimecard) eipTExtTimecardList.get(0);
      if (calClockOutTime != null) {
        eipTExtTimecard.setCalClockOutTime(calClockOutTime.getTime());
      } else {
        eipTExtTimecard.setCalClockOutTime(null);
      }
      dataContext.commitChanges();
    }
    rtn = true;
    return rtn;
  }
  
  /**
   * 作業入力による退勤時間の算出処理をします。 <BR>
   * 
   * @param targetDate
   * @param userId
   * @param systemId
   * @return
   */
  public static Calendar getCalClockOutTime(Date targetDate, Integer userId, Integer systemId) {

    DataContext dataContext = DatabaseOrmService.getInstance().getDataContext();
    
    String paidHolidayType = "";
    int startTime = 0;
    int endTime = -1;
    int worktimeIn = 0;
    int worktimeOut = 0;
    int changeHour = 0;
    int teijiTime = 0;
    ALDateTimeField alTargetDate = new ALDateTimeField("yyyy-MM-dd");
    alTargetDate.setValue(targetDate);

    // 戻り値の初期化
    int workTime = 0;
    Calendar returnTime = Calendar.getInstance();
    returnTime.setTime(targetDate);

    // 拡張タイムカードの読み込み
    Integer timecardId = 0;
    SelectQuery query1 = new SelectQuery(EipTExtTimecard.class);
    Expression exp11 = ExpressionFactory.matchExp(EipTExtTimecard.USER_ID_PROPERTY, userId);
    query1.setQualifier(exp11);   // ユーザＩＤ
    Expression exp12 = ExpressionFactory.matchExp(EipTExtTimecard.PUNCH_DATE_PROPERTY, alTargetDate.getValue());
    query1.andQualifier(exp12);   // 対象日
    List eipTExtTimecardList = dataContext.performQuery(query1);
    if (eipTExtTimecardList == null || eipTExtTimecardList.size() <= 0) {
      logger.debug("[EipTExtTimecard] Empty ID & PUNCH_DATE...");
      return null;
    } else {
      // レコードが存在する場合
      EipTExtTimecard eipTExtTimecard = (EipTExtTimecard) eipTExtTimecardList.get(0);
      timecardId = eipTExtTimecard.getExtTimecardId();
      if (eipTExtTimecard.getClockInTime() == null) {
        logger.debug("[EipTExtTimecard] CLOCK_IN_TIME is null...");
        return null;
      } else {
        startTime = startTime + eipTExtTimecard.getClockInTime().getHours() * 60;
        startTime = startTime + eipTExtTimecard.getClockInTime().getMinutes();
      }
      if (eipTExtTimecard.getClockOutTime() != null) {
        endTime = eipTExtTimecard.getClockOutTime().getHours() * 60;
        endTime = endTime + eipTExtTimecard.getClockOutTime().getMinutes();
      }
      if (eipTExtTimecard.getPaidHolidayType() != null) {
        paidHolidayType = eipTExtTimecard.getPaidHolidayType();
      }
//      List rddtl = eipTExtTimecard.getImwTExtTimecardDetail();
//      String work = "";
//      if (rddtl != null) {
//        for(Object o : rddtl){
//          ImwTExtTimecardDetail detail = (ImwTExtTimecardDetail)o;
//          if (detail.getWorktimeIn() != null) {
//            worktimeIn = worktimeIn + detail.getWorktimeIn();
//          }
//          if (detail.getWorktimeOut() != null) {
//            worktimeOut = worktimeOut + detail.getWorktimeOut();
//          }
//        }
//      }
    }

    SelectQuery query3 = new SelectQuery(ImwTExtTimecardDetail.class);
    Expression exp31 = ExpressionFactory.matchDbExp(ImwTExtTimecardDetail.EIP_TEXT_TIMECARD_PROPERTY
        + "." + EipTExtTimecard.TIMECARD_ID_PK_COLUMN, timecardId);
    query3.setQualifier(exp31);   // タイムカードＩＤ
    Expression exp32 = ExpressionFactory.matchExp(ImwTExtTimecardDetail.DISABLED_PROPERTY, "F");
    query3.andQualifier(exp32);   // 有効
    List imwTExtTimecardDetailList = dataContext.performQuery(query3);
    if (imwTExtTimecardDetailList != null && imwTExtTimecardDetailList.size() > 0) {
      for (int i = 0; i < imwTExtTimecardDetailList.size(); i++) {
        ImwTExtTimecardDetail imwTExtTimecardDetail = (ImwTExtTimecardDetail) imwTExtTimecardDetailList.get(i);
        if (imwTExtTimecardDetail.getWorktimeIn() != null) {
          worktimeIn = worktimeIn + imwTExtTimecardDetail.getWorktimeIn();
        }
        if (imwTExtTimecardDetail.getWorktimeOut() != null) {
          worktimeOut = worktimeOut + imwTExtTimecardDetail.getWorktimeOut();
        }
      }
    }
    // 不正な作業入力は処理を中止
    if (worktimeIn == 0 && worktimeOut == 0) {
      return null;
    }
    
    // 拡張タイムカード勤務形態の読み込み
    SelectQuery query2 = new SelectQuery(EipTExtTimecardSystem.class);
    Expression exp21 = ExpressionFactory.matchDbExp(EipTExtTimecardSystem.SYSTEM_ID_PK_COLUMN, systemId);
    query2.setQualifier(exp21);   // 勤務形態ＩＤ
    List eipTExtTimecardSystemList = dataContext.performQuery(query2);
    if (eipTExtTimecardSystemList == null || eipTExtTimecardSystemList.size() == 0) {
      // レコードが見つからない場合
      logger.debug("[EipTExtTimecardSystem] Empty ID...");
      return null;
    } else {
      // レコードが存在する場合
      EipTExtTimecardSystem eipTExtTimecardSystem = (EipTExtTimecardSystem) eipTExtTimecardSystemList.get(0);
      changeHour = eipTExtTimecardSystem.getChangeHour();
    }
    
    // タイムテーブルの取得
    List<TimeTableRow> timeTableList = getTimeTable(systemId, changeHour * 60);
    int recordNum = timeTableList.size();
    for (int i = recordNum - 1; i >= 0; i--) {
      TimeTableRow timeTableRow = (TimeTableRow) timeTableList.get(i);
      if (timeTableRow.TimeKind == 0 || timeTableRow.TimeKind == 1) {
        teijiTime = timeTableRow.EndMinutes;
        break;
      }
    }
    for (int i = 0; i < recordNum; i++) {
      TimeTableRow timeTableRow = (TimeTableRow) timeTableList.get(i);
      if (timeTableRow.EndMinutes >= startTime &&
          (
            paidHolidayType.equals("") ||
            ((paidHolidayType.equals("1") || paidHolidayType.equals("2")) && timeTableRow.TimeKind != 0) ||
            ((paidHolidayType.equals("1") || paidHolidayType.equals("3")) && timeTableRow.TimeKind != 1)
          )
         ) {

        int pintStartTime = 0;
        if (timeTableRow.StartMinutes >= startTime) {
          pintStartTime = timeTableRow.StartMinutes;
        } else {
          pintStartTime = startTime;
        }

        int pintWorkTime = 0;
        if (timeTableRow.TimeKind == 0 || timeTableRow.TimeKind == 1) {
          pintWorkTime = worktimeIn;
        } else if (timeTableRow.TimeKind == 2 || timeTableRow.TimeKind == 3) {
          pintWorkTime = worktimeOut;
        }

        if (pintWorkTime > 0) {
          if (timeTableRow.EndMinutes - pintStartTime >= pintWorkTime) {
            workTime = pintStartTime + pintWorkTime;
            pintWorkTime = 0;
          } else {
            pintWorkTime = pintWorkTime - (timeTableRow.EndMinutes - pintStartTime);
          }
        }
        
        if (timeTableRow.TimeKind == 0 || timeTableRow.TimeKind == 1) {
          worktimeIn = pintWorkTime;
        } else if (timeTableRow.TimeKind == 2 || timeTableRow.TimeKind == 3) {
          worktimeOut = pintWorkTime;
        }

        if (worktimeIn == 0 && worktimeOut == 0) {
          break;
        }
        
      }
    }

    // 不正な作業入力は処理を中止
    if (worktimeIn != 0 || worktimeOut != 0) {
      return null;
    }
    
    // 定時以前の退勤はボタン押下時刻を使用する。
    if (teijiTime >= workTime && workTime < endTime && endTime != -1) {
      if (teijiTime < endTime) {
        workTime = teijiTime;
      } else {
        workTime = endTime;
      }
    }

    // 戻り値の設定
    if ( workTime > 24 * 60) {
      // 日付変更時刻以前の場合は、２４時間を減算する。
      workTime = workTime - (24 * 60);
      returnTime.add(Calendar.DAY_OF_MONTH, 1);
    }
    returnTime.set(Calendar.HOUR_OF_DAY, workTime / Integer.valueOf(60));
    returnTime.set(Calendar.MINUTE, workTime % Integer.valueOf(60));
    return returnTime;
  }

  /**
   * タイムテーブル作成用のSQL文を返却します。
   * 
   * @author 青柳
   * @param systemId    勤務形態ID
   * @param workInTime  出勤時刻（分）
   * @param wordOutTime 退勤時刻（分）
   * @param paidVacType 有給休暇タイプ
   * @return SQL文
   */
  public static String getTimeTableQuery(int systemId, int workInTime, int wordOutTime, int paidVacType){
    if(workInTime > wordOutTime) wordOutTime += 24 * 60;
    
    String sql = "SELECT " +
            " CASE WHEN @YK = 1 THEN '0' " +
            "      WHEN @YK = 2 AND kd.worktime_status = '0' THEN '0' " +
            "      WHEN @YK = 2 AND kd.worktime_status = '1' THEN '1' " +
            "      ELSE kd.workstart_flag END AS 始業フラグ , " +
            " kd.start_hour * 60 + kd.start_minute AS 開始, " +
            " kd.start_hour * 60 + kd.start_minute " +
            " + CASE WHEN @YK = 3 AND kd.worktime_status = '1' THEN 0" +
            "      ELSE kd.worktime END AS 終了, " +
            " CASE WHEN @YK = 3 AND kd.worktime_status = '1' THEN 0" +
            "      ELSE kd.worktime END AS 作業, " +
            " kd.worktime_status AS 区分 \n" +
            "FROM eip_t_ext_timecard_system AS k INNER JOIN " +
            " imw_t_ext_timecard_system_detail AS kd ON k.system_id = kd.system_id \n " +
            "WHERE k.system_id = @KID " +
            "  AND  k.change_hour * 60 < kd.start_hour * 60 + kd.start_minute + kd.worktime " +
            "  AND @ST < kd.start_hour * 60 + kd.start_minute + kd.worktime " +
            "  AND @ED > kd.start_hour * 60 + kd.start_minute \n " +
            /** Add 2010/10/14 金廣 Start--- */
            // 課題51:午後休の日に残業を行った場合、在席管理で退勤ボタンを押すと12時になる。休憩時間に午後休が考慮されない問題の対応
            // 課題63:午前休の日の午前中に出勤し作業実績を入力すると、出勤時間から昼休憩までの時間が欠勤時間として表示される問題の対応
            "  AND NOT (@YK = 2 AND kd.worktime_status = 0) " +
            "  AND NOT (@YK = 3 AND kd.worktime_status = 1) \n " +
            /** Add 2010/10/14 金廣 End----- */   
            "UNION \n " +
            "SELECT " +
            " CASE WHEN @YK = 1 THEN '0' " +
            "      WHEN @YK = 2 AND kd.worktime_status = '0' THEN '0' " +
            "      WHEN @YK = 2 AND kd.worktime_status = '1' THEN '1' " +
            "      ELSE kd.workstart_flag END AS 始業フラグ , " +
            " kd.start_hour * 60 + kd.start_minute + 60 * 24 AS 開始," +
            " kd.start_hour * 60 + kd.start_minute + 60 * 24 " +
            " + CASE WHEN @YK = 3 AND kd.worktime_status = '1' THEN 0" +
            "      ELSE kd.worktime END AS 終了, " +
            " CASE WHEN @YK = 3 AND kd.worktime_status = '1' THEN 0" +
            "      ELSE kd.worktime END AS 作業, " +
            " kd.worktime_status AS 区分 \n" +
            "FROM eip_t_ext_timecard_system AS k INNER JOIN" +
            " imw_t_ext_timecard_system_detail AS kd ON k.system_id = kd.system_id \n" +
            "WHERE k.system_id = @KID " +
            "  AND k.change_hour * 60 > kd.start_hour * 60 + kd.start_minute " +      
            /** Update 2010/10/14 金廣 Start--- */
            // 課題51:午後休の日に残業を行った場合、在席管理で退勤ボタンを押すと12時になる。休憩時間に午後休が考慮されない問題の対応
            // 課題63:午前休の日の午前中に出勤し作業実績を入力すると、出勤時間から昼休憩までの時間が欠勤時間として表示される問題の対応
            // "  AND @ED > kd.start_hour * 60 + kd.start_minute + 60 * 24";
            "  AND @ED > kd.start_hour * 60 + kd.start_minute + 60 * 24 \n " +  
            "  AND NOT (@YK = 2 AND kd.worktime_status = 0) " +
            "  AND NOT (@YK = 3 AND kd.worktime_status = 1)";
            /** Update 2010/10/14 金廣 End----- */   
    
    sql = sql.replaceAll("@KID", String.valueOf(systemId));
    sql = sql.replaceAll("@ST", String.valueOf(workInTime));
    sql = sql.replaceAll("@ED", String.valueOf(wordOutTime));
    sql = sql.replaceAll("@YK", String.valueOf(paidVacType));
    
    return sql;
  }
  
  /**
   * タイムテーブルを作成し返却します。
   * 
   * @author 青柳
   * @param systemId    勤務形態ID
   * @param workInTime  出勤時刻（分）
   * @param wordOutTime 退勤時刻（分）
   * @param paidVacType 有給休暇タイプ
   * 
   * @return List   結果テーブル
   */
  public static List<TimeTableRow> getTimeTable(int systemId, int workInTime, int wordOutTime, int paidVacType){
    try{
      DataContext dc = DatabaseOrmService.getInstance().getDataContext();
      String sql = getTimeTableQuery(systemId, workInTime, wordOutTime, paidVacType);
     
      SQLTemplate q = new SQLTemplate(ImwTExtTimecardSystemDetail.class, sql + " ORDER BY 開始 ASC;", true);
   
      List<DataRow> res;
      List<TimeTableRow> rows;
      
      q.setFetchingDataRows(true);
      res = dc.performQuery(q);
      rows = new ArrayList<TimeTableRow>();
      
      // 結果をオブジェクトに入れて返す。
      for(Iterator<DataRow> i = res.iterator();i.hasNext();){
        DataRow drow = i.next();
        TimeTableRow row = new TimeTableRow();
        
        row.StartFlg = Integer.parseInt(drow.get("始業フラグ").toString());
        row.StartMinutes =  Integer.parseInt(drow.get("開始").toString());
        row.EndMinutes =  Integer.parseInt(drow.get("終了").toString());
        row.TimeSpan =  Integer.parseInt(drow.get("作業").toString());
        row.TimeKind =  Integer.parseInt(drow.get("区分").toString());
        
        rows.add(row);
      }

      return rows;
      
    }catch(Exception e){
      logger.error("Exception", e);
      return null;
    }

  }

  /**
   * タイムテーブルを作成し返却します。
   * 有給設定を省略して通常勤務と想定してデータを取得します。
   * 
   * @author 青柳
   * @param systemId    勤務形態ID
   * @param workInTime  出勤時刻（分）
   * @param wordOutTime 退勤時刻（分）
   * @return List   結果テーブル
   */
  public static List<TimeTableRow> getTimeTable(int systemId, int workInTime, int wordOutTime){
    return getTimeTable(systemId, workInTime, wordOutTime, 0);
  }
  
  /**
   * タイムテーブルを作成し返却します。
   * 退勤時刻を省略して開始以降全データを取得します。
   * 
   * @author 青柳
   * @param systemId    勤務形態ID
   * @param workInTime  出勤時刻（分）
   * @return List   結果テーブル
   */
  public static List<TimeTableRow> getTimeTable(int systemId, int workInTime){
      return getTimeTable(systemId, workInTime, workInTime + 60 * 24, 0);
  }

  /**
   * タイムテーブル行クラス
   * タイムテーブル一行分のデータを保持します。
   * 
   * @author  青柳
   */
  public static class TimeTableRow{
    /**
     * 開始フラグ
     */
    public int StartFlg;
    
    /**
     * 勤務開始時間
     */
    public int StartMinutes;
    
    /**
     * 勤務終了時間
     */
    public int EndMinutes;
    
    /**
     * 作業時間
     */
    public int TimeSpan;
    
    /**
     * 作業時間区分
     */
    public int TimeKind;
  }

  /**
   * 特定ユーザーのEipTExtTimecardSystemを取得します。
   * 
   * @param user_id
   * @return String
   */
  public static Integer getsystemId(int user_id) {
    /** 勤務形態ＩＤ　**/
    Integer sysid = null;
    try {
      //拡張タイムカード勤務形態マップからログインユーザのデータを取得
      DataContext dataContext = DatabaseOrmService.getInstance().getDataContext();
      SelectQuery query = new SelectQuery(EipTExtTimecardSystemMap.class);
      Expression exp = ExpressionFactory.matchExp(
          EipTExtTimecardSystemMap.USER_ID_PROPERTY, Integer.valueOf(user_id));
      query.setQualifier(exp);
      List idlist = dataContext.performQuery(query);
      if(idlist == null || idlist.size() == 0){
        return null;
      }else{
      sysid = ((EipTExtTimecardSystemMap) idlist.get(0)).getEipTExtTimecardSystem().getSystemId();
      return sysid;
      }
    }catch (Exception ex) {
      logger.error("Exception", ex);
      return null;
    }
  }
  
  /**
   * 特定ユーザーの自家用車通勤フラグを取得する。
   * 
   * @param userid
   * @return Integer
   */
  public static String getcarcommutingflag(int userid) {
    String car_commuting_flag = null;
    try{
      DataContext dataContext = DatabaseOrmService.getInstance().getDataContext();
      SelectQuery query = new SelectQuery(TurbineUser.class);
      Expression exp = ExpressionFactory.matchDbExp(
          TurbineUser.USER_ID_PK_COLUMN, userid);
      query.setQualifier(exp);
      List slist = dataContext.performQuery(query);
      if(slist == null || slist.size() == 0){
        return null;
      }else{
        car_commuting_flag = String.valueOf(((TurbineUser) slist.get(0)).getCarCommutingFlag());
        return car_commuting_flag;
      }
    }catch (Exception ex) {
      logger.error("Exception", ex);
      return null;
    }
  }
  
/** Add 2010/10/15 金廣 Start--- */
//課題51:午後休の日に残業を行った場合、在席管理で退勤ボタンを押すと12時になる。休憩時間に午後休が考慮されない問題の対

 /**
   * 休憩時間補正（余分にカウントされた休憩時間）を返却します。
   * 
   * @author 金廣
   * @param systemId
   *            勤務形態ID
   * @param workInTime
   *            出勤時刻（分）           
   * @param workOutTime
   *            退勤時刻（分）
   * @param paidVacType
   *            有給休暇タイプ
   * 
   * @return Integer 休憩時間補正
   */
  public static Integer getBreakTimeRevision(int systemId,int workInTime, int workOutTime,
      int paidVacType) {
    try {
      if (paidVacType != 3) return 0;
      
      DataContext dc = DatabaseOrmService.getInstance().getDataContext();
      
      // 日付変更時刻の取得
      int changeHour = 0;
      SelectQuery sysQuery = new SelectQuery(EipTExtTimecardSystem.class);
      Expression exp = ExpressionFactory.matchDbExp(EipTExtTimecardSystem.SYSTEM_ID_PK_COLUMN, systemId);
      List sysList = dc.performQuery(sysQuery);
      if (sysList == null || sysList.size() == 0) {
        return 0;
      } else {
        changeHour = ((EipTExtTimecardSystem)sysList.get(0)).getChangeHour();
      }
      
      // 出勤時刻が日付変更時刻以前の場合、24時間プラスする。
      if(workInTime < changeHour * 60){
        workInTime += 24 * 60;
      }
      
      // 退出時刻が日付変更時刻以前の場合、24時間プラスする。
      if(workOutTime < changeHour * 60){
        workOutTime += 24 * 60;
      }
      
      // 休憩時間補正の算出
      // （午後休の場合、出勤時刻と退勤時刻の間に収まる午後の作業時間の合計を算出
      //  その他の場合、0 ）
      String sql = "SELECT "
          + " CASE WHEN $YK <> 3 THEN 0 "
          + " ELSE COALESCE(SUM(worktime), 0) END AS 休憩時間補正 "
          + " FROM "
          + "    imw_t_ext_timecard_system_detail "
          + " WHERE "
          + "    system_id = $KID "
          + "    AND worktime_status = 1 "
          + "    AND "
          + "       $ST < "
          + "       CASE WHEN $CH * 60 > start_hour * 60 + start_minute THEN (start_hour + 24) "
          + "       ELSE start_hour END "
          + "       * 60 + start_minute "
          + "    AND "
          + "       $ED > "
          + "       CASE WHEN $CH * 60 > end_hour * 60 + end_minute THEN (end_hour + 24) "
          + "       ELSE end_hour END "
          + "       * 60 + end_minute ";
      SQLTemplate q = new SQLTemplate(ImwTExtTimecardSystemDetail.class, sql,
          false);
      // 名前付きパラメータの設定
      HashMap map = new HashMap();
      map.put("YK", paidVacType);
      map.put("KID", systemId);
      map.put("ST", workInTime);
      map.put("ED", workOutTime);
      map.put("CH", changeHour);
      q.setParameters(map);
      q.setFetchingDataRows(true);
      List res = dc.performQuery(q);

      if (res == null || res.size() == 0) {
        return 0;
      } else {
        DataRow drow = (DataRow)res.get(0);
        return Integer.parseInt(drow.get("休憩時間補正").toString());
       }
    } catch (Exception e) {
      logger.error("Exception", e);
      return null;
    }

  }
/** Add 2010/10/15 金廣 End----- */
 
}