/*
 * Aipo is a groupware program developed by Aimluck,Inc.
 * Copyright (C) 2004-2008 Aimluck,Inc.
 * http://aipostyle.com/
 *
 * 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 3 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, see <http://www.gnu.org/licenses/>.
 */
package com.aimluck.eip.exttimecard.util;

import java.io.File;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

//Add 20110707 小濵 Start ---
import org.apache.cayenne.DataObjectUtils;
//Add 20110707 小濵 End -----
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.SQLTemplate;
import org.apache.cayenne.query.SelectQuery;
import org.apache.jetspeed.services.logging.JetspeedLogFactoryService;
import org.apache.jetspeed.services.logging.JetspeedLogger;
import org.apache.jetspeed.services.resources.JetspeedResources;
import org.apache.turbine.util.RunData;
import org.apache.velocity.context.Context;

import com.aimluck.eip.cayenne.om.portlet.EipTExtTimecard;
import com.aimluck.eip.cayenne.om.portlet.EipTExtTimecardSystem;
import com.aimluck.eip.cayenne.om.portlet.ImwTExtTimecardSystemDetail;
import com.aimluck.eip.cayenne.om.portlet.EipTExtTimecardSystemMap;
import com.aimluck.eip.cayenne.om.portlet.ImwMClient;
import com.aimluck.eip.cayenne.om.portlet.ImwMHanyo;
import com.aimluck.eip.cayenne.om.portlet.ImwTExtTimecardDetail;
import com.aimluck.eip.cayenne.om.portlet.ImwTJobOrder;
import com.aimluck.eip.cayenne.om.portlet.ImwTOffice;
import com.aimluck.eip.common.ALEipConstants;
import com.aimluck.eip.exttimecard.ExtTimecardWorkResultData;
import com.aimluck.eip.orm.DatabaseOrmService;
import com.aimluck.eip.util.ALEipUtils;

/**
 * タイムカードのユーティリティクラスです。 <BR>
 * 
 */
public class ExtTimecardUtils {

  /** logger */
  private static final JetspeedLogger logger = JetspeedLogFactoryService
      .getLogger(ExtTimecardUtils.class.getName());

  /** <code>TARGET_GROUP_NAME</code> グループによる表示切り替え用変数の識別子 */
  public static final String TARGET_GROUP_NAME = "target_group_name";

  /** <code>TARGET_USER_ID</code> ユーザによる表示切り替え用変数の識別子 */
  public static final String TARGET_USER_ID = "target_user_id";

  /** 出退勤フラグ（出勤） */
  public static final String WORK_FLG_ON = "1";

  /** 出退勤フラグ（退勤） */
  public static final String WORK_FLG_OFF = "0";

  /** 出退勤フラグ（ダミー） */
  public static final String WORK_FLG_DUMMY = "-1";

  /** タイムカードファイルを一時保管するディレクトリの指定 */
  public static final String FOLDER_TMP_FOR_TIMECARD_FILES = JetspeedResources
      .getString("aipo.tmp.timecard.directory", "");

  /**
   * ExtTimecard オブジェクトモデルを取得します。 <BR>
   * 
   * @param rundata
   * @param context
   * @param isJoin
   *            カテゴリテーブルをJOINするかどうか
   * @return
   */
  public static EipTExtTimecard getEipTExtTimecard(RunData rundata,
      Context context) {
    String timecardid = ALEipUtils.getTemp(rundata, context,
        ALEipConstants.ENTITY_ID);
    try {
      if (timecardid == null || Integer.valueOf(timecardid) == null) {
        // Todo IDが空の場合
        logger.debug("[Timecard] Empty ID...");
        return null;
      }

      DataContext dataContext = DatabaseOrmService.getInstance()
          .getDataContext();
      SelectQuery query = new SelectQuery(EipTExtTimecard.class);
      Expression exp11 = ExpressionFactory.matchDbExp(
          EipTExtTimecard.TIMECARD_ID_PK_COLUMN, timecardid);
      query.setQualifier(exp11);
      
     // エンティティIDで判断しているにも関わらずユーザーまで限定しようとしているので、以下は不要
     // Change 2010/8/23 青柳
     /* Expression exp21 = ExpressionFactory.matchExp(
          EipTExtTimecard.USER_ID_PROPERTY, Integer.valueOf(ALEipUtils
              .getUserId(rundata)));
      query.andQualifier(exp21);
      */
      
      List timecards = dataContext.performQuery(query);
      if (timecards == null || timecards.size() == 0) {
        // 指定したTimecard IDのレコードが見つからない場合
        logger.debug("[Timecard] Not found ID...");
        return null;
      }
      return ((EipTExtTimecard) timecards.get(0));
    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;
    }
  }

  /**
   * ExtTimecard オブジェクトモデルを取得します。 <BR>
   * 
   * @param rundata
   * @param context
   * @param isJoin
   *          カテゴリテーブルをJOINするかどうか
   * @return
   */
  public static EipTExtTimecard getEipTExtTimecardForUser(RunData rundata,
      Context context) {
    String viewMonth = ALEipUtils.getTemp(rundata, context, "view_date");
    try {
      if (viewMonth == null){// || Integer.valueOf(viewMonth) == null) {
        // Todo viewMonthが空の場合
        logger.debug("[Timecard] Empty ID...");
        return null;
      }

      DataContext dataContext = DatabaseOrmService.getInstance()
          .getDataContext();
      SelectQuery query = new SelectQuery(EipTExtTimecard.class);
      Expression exp11 = ExpressionFactory.matchExp(
          EipTExtTimecard.USER_ID_PROPERTY, Integer.valueOf(ALEipUtils
              .getUserId(rundata)));
      query.setQualifier(exp11);
      Expression exp21 = ExpressionFactory.matchExp(
          EipTExtTimecard.PUNCH_DATE_PROPERTY, viewMonth);
      query.andQualifier(exp21);
      List timecards = dataContext.performQuery(query);
      if (timecards == null || timecards.size() == 0) {
        // 指定したTimecard IDのレコードが見つからない場合
        logger.debug("[Timecard] Not found ID...");
        return null;
      }
      return ((EipTExtTimecard) timecards.get(0));
    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;
    }
  }

  /**
   * 現在ログイン中のユーザーのEipTExtTimecardSystemを取得します。
   * 
   * @param rundata
   * @param context
   * @return
   */
  public static EipTExtTimecardSystem getEipTExtTimecardSystemCurrentUserId(
      RunData rundata, Context context) {
    int user_id = Integer.valueOf(ALEipUtils.getUserId(rundata));
    return getEipTExtTimecardSystemByUserId(user_id);
  }

  /**
   * 特定ユーザーのEipTExtTimecardSystemを取得します。
   * 
   * @param rundata
   * @param context
   * @return
   */
  public static EipTExtTimecardSystem getEipTExtTimecardSystemByUserId(
      int user_id) {
    try {
      DataContext dataContext = DatabaseOrmService.getInstance()
          .getDataContext();
      SelectQuery query = new SelectQuery(EipTExtTimecardSystemMap.class);
      Expression exp1 = ExpressionFactory.matchExp(
          EipTExtTimecardSystemMap.USER_ID_PROPERTY, Integer.valueOf(user_id));
      query.setQualifier(exp1);
      List slist = dataContext.performQuery(query);
      if (slist == null || slist.size() == 0) {
        return getEipTExtTimecardSystemById(1);
      }
      return ((EipTExtTimecardSystemMap) slist.get(0))
          .getEipTExtTimecardSystem();
    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;
    }
  }

  public static EipTExtTimecardSystem getEipTExtTimecardSystemById(int system_id) {
    try {
      DataContext dataContext = DatabaseOrmService.getInstance()
          .getDataContext();
      SelectQuery query = new SelectQuery(EipTExtTimecardSystem.class);
      Expression exp1 = ExpressionFactory.matchDbExp(
          EipTExtTimecardSystem.SYSTEM_ID_PK_COLUMN, system_id);
      query.setQualifier(exp1);
      List slist = dataContext.performQuery(query);
      if (slist == null || slist.size() == 0) {
        return null;
      }
      return (EipTExtTimecardSystem) slist.get(0);
    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;
    }
  }

  /**
   * 
   * @return
   */
  public static List getAllEipTExtTimecardSystem() {
    try {
      DataContext dataContext = DatabaseOrmService.getInstance()
          .getDataContext();
      SelectQuery query = new SelectQuery(EipTExtTimecardSystem.class);
      List slist = dataContext.performQuery(query);
      if (slist == null || slist.size() == 0) {
        // 指定したSetting IDのレコードが見つからない場合
        logger.debug("[ExtTimecardUtils] Not found ID...");
        return null;
      }
      return slist;
    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;
    }
  }

  /**
   * 
   * @return
   */
  public static EipTExtTimecardSystem getEipTExtTimecardSystem(RunData rundata,
      Context context) {
    String systemid = ALEipUtils.getTemp(rundata, context,
        ALEipConstants.ENTITY_ID);
    try {
      if (systemid == null || Integer.valueOf(systemid) == null) {
        // Setting IDが空の場合
        logger.debug("[ExtTimecardUtils] Empty ID...");
        return null;
      }

      DataContext dataContext = DatabaseOrmService.getInstance()
          .getDataContext();
      SelectQuery query = new SelectQuery(EipTExtTimecardSystem.class);
      Expression exp1 = ExpressionFactory.matchDbExp(
          EipTExtTimecardSystem.SYSTEM_ID_PK_COLUMN, systemid);

      query.setQualifier(exp1);
      //Del 20110708 小濵 Start ---
//      query.addOrdering(EipTExtTimecardSystem.IMW_TEXT_TIMECARD_SYSTEM_DETAIL_PROPERTY +
//          "." + ImwTExtTimecardSystemDetail.START_HOUR_PROPERTY, true);
//      query.addOrdering(EipTExtTimecardSystem.IMW_TEXT_TIMECARD_SYSTEM_DETAIL_PROPERTY +
//          "." + ImwTExtTimecardSystemDetail.START_MINUTE_PROPERTY, true);
      //Del 20110708 小濵 End -----
      List slist = dataContext.performQuery(query);
      if (slist == null || slist.size() == 0) {
        // 指定したSetting IDのレコードが見つからない場合
        logger.debug("[ExtTimecardUtils] Not found ID...");
        return null;
      }

      return (EipTExtTimecardSystem) slist.get(0);
    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;
    }
  }

  /**
   * 指定した2つの日付を比較する．
   * 
   * @param date1
   * @param date2
   * @param checkTime
   *            時間まで比較する場合，true．
   * @return 等しい場合，0. date1>date2の場合, 1. date1 <date2の場合, 2.
   */
  public static boolean sameDay(Date date1, Date date2) {
    Calendar cal1 = Calendar.getInstance();
    Calendar cal2 = Calendar.getInstance();
    cal1.setTime(date1);
    cal2.setTime(date2);

    int date1Year = cal1.get(Calendar.YEAR);
    int date1Month = cal1.get(Calendar.MONTH) + 1;
    int date1Day = cal1.get(Calendar.DATE);
    int date2Year = cal2.get(Calendar.YEAR);
    int date2Month = cal2.get(Calendar.MONTH) + 1;
    int date2Day = cal2.get(Calendar.DATE);

    if (date1Year == date2Year && date1Month == date2Month
        && date1Day == date2Day) {
      return true;
    }
    return false;
  }

  /**
   * 一時ファイル保存先のユーザのルートフォルダ
   * 
   * @param org_id
   * @param userId
   * @return
   */
  public static File getRootFolder(String org_id, int userId) {
    String rootPath = ExtTimecardUtils.FOLDER_TMP_FOR_TIMECARD_FILES
        + File.separator + org_id + File.separator + userId + File.separator;
    File folder = new File(rootPath);
    if (!folder.exists()) {
      if (!folder.mkdirs()) {
        return null;
      }
    }
    return folder;
  }

  /**
   * 現在時刻とEipTTimecardSettingsの情報から update文を発行すべきかどうかを判断します。
   * 
   * @param org_id
   * @param userId
   * @return
   */
  public static EipTExtTimecard getUpdateEipTExtTimecard(RunData rundata,
      Context context) {
    EipTExtTimecardSystem system = ExtTimecardUtils
        .getEipTExtTimecardSystemCurrentUserId(rundata, context);

    Calendar calendar_now = Calendar.getInstance();
    Calendar from_calendar = Calendar.getInstance();

    int hour = system.getChangeHour().intValue();
    from_calendar.set(Calendar.HOUR_OF_DAY, hour);
    from_calendar.set(Calendar.MINUTE, 0);
    from_calendar.set(Calendar.SECOND, 0);
    if (calendar_now.before(from_calendar)) {
      from_calendar.add(Calendar.DAY_OF_MONTH, -1);
    }
    from_calendar.set(Calendar.HOUR_OF_DAY, 0);

    DataContext dataContext = DatabaseOrmService.getInstance().getDataContext();
    SelectQuery query = new SelectQuery(EipTExtTimecard.class);
    Expression exp1 = ExpressionFactory.matchExp(
        EipTExtTimecard.USER_ID_PROPERTY, Integer.valueOf(ALEipUtils
            .getUserId(rundata)));
    Expression exp2 = ExpressionFactory.matchExp(
        EipTExtTimecard.PUNCH_DATE_PROPERTY, from_calendar.getTime());
    query.setQualifier(exp1.andExp(exp2));
    List slist = dataContext.performQuery(query);

    // データが無かった
    if (slist == null || slist.size() == 0) {
      return null;
    }

    return (EipTExtTimecard) slist.get(0);
  }

  /**
   * 集計した時間を丸めます。
   * 
   * @return
   */
  public static float roundHour(float time) {
    time *= 10;
    time = Math.round(time);
    time /= 10;
    return time;
  }

  public static JetspeedLogger getLogger() {
    return logger;
  }

  /** Add 2010/08/14 チョホン　Start--- */

  /**
   * セッションに格納されているIDを用いて、拡張タイムカード明細を取得します。
   * 
   * @param rundata
   * @param context
   * @return
   */
  public static ImwTExtTimecardDetail getImwTExtTimecardDetail(RunData rundata,
      Context context) {
    ImwTExtTimecardDetail result = null;
    String id = ALEipUtils.getTemp(rundata, context, ALEipConstants.ENTITY_ID);
    try {
      if (id == null || Integer.valueOf(id) == null) {
        logger.debug("Empty ID...");
        return result;
      }

      DataContext dataContext = DatabaseOrmService.getInstance()
          .getDataContext();
      Expression exp = ExpressionFactory.matchDbExp(
          ImwTExtTimecardDetail.DETAIL_ID_PK_COLUMN, Integer.valueOf(id));
      SelectQuery query = new SelectQuery(ImwTExtTimecardDetail.class, exp);
      List list = dataContext.performQuery(query);
      if (list == null || list.size() == 0) {
        logger.debug("Not found ID...");
        return result;
      }
      result = (ImwTExtTimecardDetail) list.get(0);
    } catch (Exception ex) {
      logger.error("Exception", ex);
    }
    return result;
  }

  /**
   * 
   * 件名を取得します
   * 
   */
  public static List getJobOrderNameList() {
    try {
      List jobOrderNameList = new ArrayList();

      DataContext dataContext = DatabaseOrmService.getInstance()
          .getDataContext();
      SelectQuery query = new SelectQuery(ImwTJobOrder.class);
      //Expression exp = ExpressionFactory.matchDbExp(
      //    ImwTJobOrder.COMPLETION_CLASS_COLUMN, 0);
      Expression exp = ExpressionFactory.matchExp(
          ImwTJobOrder.COMPLETION_CLASS_PROPERTY, 0);

      query.setQualifier(exp);
      
      /** Add 2010/10/06 金廣 Start--- */
      // 課題10:受注のレコードを削除（論理削除）しても、作業入力の件名のコンボボックスに表示される問題の対応
      Expression exp2 = ExpressionFactory.matchExp(
          ImwTJobOrder.DISABLED_PROPERTY, "F");
      query.andQualifier(exp2);
      /** Add 2010/10/06 金廣 End----- */
      
      List aList = dataContext.performQuery(query);

      int size = aList.size();
      for (int i = 0; i < size; i++) {
        ImwTJobOrder record = (ImwTJobOrder) aList.get(i);
        ExtTimecardWorkResultData rd = new ExtTimecardWorkResultData();
        rd.initField();
        rd.setJobOrderId(record.getJobOrderId());
        rd.setJobOrderName(record.getJobOrderName());
        jobOrderNameList.add(rd);
      }
      return jobOrderNameList;
    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;
    }
  }
  


  /**
   * 
   * 作業区分を取得します
   * 
   */
  public static List getWorkClassList() {
    try {
      List workClassList = new ArrayList();

      DataContext dataContext = DatabaseOrmService.getInstance()
          .getDataContext();
      SelectQuery query = new SelectQuery(ImwMHanyo.class);
      Expression exp = ExpressionFactory.matchExp(
          ImwMHanyo.PARENT_HANYO_ID_PROPERTY, 11);

      query.setQualifier(exp);
      List aList = dataContext.performQuery(query);

      int size = aList.size();
      for (int i = 0; i < size; i++) {
        ImwMHanyo record = (ImwMHanyo) aList.get(i);
        ExtTimecardWorkResultData rd = new ExtTimecardWorkResultData();
        rd.initField();
        rd.setHanyokubunnm(record.getHanyokubunnm());
        rd.setValue(record.getValue());
        workClassList.add(rd);
      }
      return workClassList;
    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;
    }
  }

  /**
   * 
   * 訪／来を取得します
   * 
   */
  public static List getWorkPlaceList() {
    try {
      List workPlaceList = new ArrayList();

      DataContext dataContext = DatabaseOrmService.getInstance()
          .getDataContext();
      SelectQuery query = new SelectQuery(ImwMHanyo.class);
      Expression exp = ExpressionFactory.matchExp(
          ImwMHanyo.PARENT_HANYO_ID_PROPERTY, 12);

      query.setQualifier(exp);
      List aList = dataContext.performQuery(query);

      int size = aList.size();
      for (int i = 0; i < size; i++) {
        ImwMHanyo record = (ImwMHanyo) aList.get(i);
        ExtTimecardWorkResultData rd = new ExtTimecardWorkResultData();
        rd.initField();
        rd.setHanyokubunnm(record.getHanyokubunnm());
        rd.setValue(record.getValue());
        workPlaceList.add(rd);
      }
      return workPlaceList;
    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;
    }
  }

  /**
   * 
   * 得意先を取得します
   * 
   */
  public static String getClientList(int jobOrderId) {
    try {
      DataContext dataContext = DatabaseOrmService.getInstance()
          .getDataContext();
      StringBuffer statement = new StringBuffer();
      statement.append("SELECT DISTINCT ");
      statement.append("  B.CLIENT_NAME ");
      statement.append("FROM IMW_T_JOB_ORDER as A, ");
      statement.append(" IMW_M_CLIENT as B ");
      statement.append("WHERE A.CLIENT_ID = B.CLIENT_ID ");
      statement.append(" AND A.JOB_ORDER_ID = ").append(jobOrderId).append("");
      String query = statement.toString();

      SQLTemplate rawSelect = new SQLTemplate(ImwMClient.class, query, true);
      rawSelect.setFetchingDataRows(true);
      List list = dataContext.performQuery(rawSelect);

      if (list == null || list.size() == 0) {
        return "";
      }
      DataRow dataRow;
      String record;
      dataRow = (DataRow) list.get(0);
      record = (String) ALEipUtils.getObjFromDataRow(dataRow,
          ImwMClient.CLIENT_NAME_COLUMN);
      return record;
    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;
    }
  }

  /**
   * 
   * システム区分１を取得します
   * 
   */
  public static String getSystemClass1List(int jobOrderId) {
    try {
      StringBuffer statement = new StringBuffer();
      statement.append("SELECT DISTINCT ");
      statement.append("  B.HANYOKUBUNNM ");
      statement.append("FROM IMW_T_JOB_ORDER as A, ");
      statement.append(" IMW_M_HANYO as B ");
      statement.append("WHERE B.PARENT_HANYO_ID = 3 ");
      statement.append(" AND A.SYSTEM_CLASS1 = B.VALUE ");
      statement.append(" AND A.JOB_ORDER_ID = ").append(jobOrderId).append("");
      String query = statement.toString();

      DataContext dataContext = DatabaseOrmService.getInstance()
          .getDataContext();
      SQLTemplate rawSelect = new SQLTemplate(ImwMHanyo.class, query, true);
      rawSelect.setFetchingDataRows(true);
      List list = dataContext.performQuery(rawSelect);

      if (list == null || list.size() == 0) {
        return "";
      }
      DataRow dataRow;
      String record;
      dataRow = (DataRow) list.get(0);
      record = (String) ALEipUtils.getObjFromDataRow(dataRow,
          ImwMHanyo.HANYOKUBUNNM_COLUMN);
      return record;

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

  /**
   * 
   * システム区分2を取得します
   * 
   */
  public static String getSystemClass2List(int jobOrderId) {
    try {
      DataContext dataContext = DatabaseOrmService.getInstance()
          .getDataContext();
      StringBuffer statement = new StringBuffer();
      statement.append("SELECT DISTINCT ");
      statement.append("  B.HANYOKUBUNNM ");
      statement.append("FROM IMW_T_JOB_ORDER as A, ");
      statement.append(" IMW_M_HANYO as B ");
      statement.append("WHERE B.PARENT_HANYO_ID = 4 ");
      statement.append(" AND A.SYSTEM_CLASS2 = B.VALUE ");
      statement.append(" AND A.JOB_ORDER_ID = ").append(jobOrderId).append("");
      String query = statement.toString();
      SQLTemplate rawSelect = new SQLTemplate(ImwMHanyo.class, query, true);
      rawSelect.setFetchingDataRows(true);

      List list = dataContext.performQuery(rawSelect);

      if (list == null || list.size() == 0) {
        return "";
      }
      DataRow dataRow;
      String record;
      dataRow = (DataRow) list.get(0);
      record = (String) ALEipUtils.getObjFromDataRow(dataRow,
          ImwMHanyo.HANYOKUBUNNM_COLUMN);

      return record;
    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;
    }
  }

  /**
   * 
   * 納期を取得します
   * 
   */
  public static Date getPeriodList(int jobOrderId) {
    try {
      DataContext dataContext = DatabaseOrmService.getInstance()
          .getDataContext();
      Expression exp1 = ExpressionFactory.matchDbExp(
          ImwTJobOrder.JOB_ORDER_ID_PK_COLUMN, jobOrderId);
      SelectQuery query = new SelectQuery(ImwTJobOrder.class);
      query.andQualifier(exp1);
      List list = dataContext.performQuery(query);
      if (list == null || list.size() == 0) {
        return null;
      }
      ImwTJobOrder result = (ImwTJobOrder) list.get(0);

      return result.getPeriod();
    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;
    }
  }
  
  
  public static EipTExtTimecard getEipTExtTimecardTime(int userId,String punchDate) {
    try {
      DataContext dataContext = DatabaseOrmService.getInstance()
          .getDataContext();
      StringBuffer statement = new StringBuffer();
      statement.append("SELECT DISTINCT ");
      statement.append("a.timecard_id, a.system_id ,a.clock_in_time,a.clock_out_time ");
      statement.append("from EIP_T_EXT_TIMECARD as a ");
      statement.append("WHERE  a.user_id =  ").append(userId).append("");
      statement.append(" AND a.punch_date = '").append(punchDate).append("'");
      String query = statement.toString();
      SQLTemplate rawSelect = new SQLTemplate(EipTExtTimecard.class, query, true);
      rawSelect.setFetchingDataRows(true);
      
      List list = dataContext.performQuery(rawSelect);
      if (list == null || list.size() == 0) {
        return null;
      }     
      DataRow dataRow;
      EipTExtTimecard record;
      record = new EipTExtTimecard();
      dataRow = (DataRow) list.get(0);
      
      record.setSystemId((Integer)ALEipUtils.getObjFromDataRow(dataRow, EipTExtTimecard.SYSTEM_ID_COLUMN));
      record.setClockInTime((Date)ALEipUtils.getObjFromDataRow(dataRow, EipTExtTimecard.CLOCK_IN_TIME_COLUMN));
      record.setClockOutTime((Date)ALEipUtils.getObjFromDataRow(dataRow, EipTExtTimecard.CLOCK_OUT_TIME_COLUMN));
      record.setExtTimecardId(String.valueOf(ALEipUtils.getObjFromDataRow(dataRow, EipTExtTimecard.TIMECARD_ID_COLUMN)));
      return record;
    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;
    }
  }
  /**
   * 勤務形態 その時と終了を開始取得
   * 
   * @param entity
   */

  public static  ImwTExtTimecardSystemDetail getDetailDate(EipTExtTimecard entity) {
    String systemId = null;
    DataContext dataContext = DatabaseOrmService.getInstance()
    .getDataContext();
    if (entity == null) {
      return null;
    }
    if (entity.getExtTimecardId() == null
        || entity.getExtTimecardId().toString().equals("")) {
      return null;
    } else {
      systemId = entity.getExtTimecardId().toString();
    }
    try {
      String query = "SELECT SD.SYSTEM_DETAIL_ID";
      query += ",SD.SYSTEM_ID";
      query += ",SD.WORKSTART_FLAG";
      query += ",SD.START_HOUR";
      query += ",SD.START_MINUTE";
      query += ",SD.END_HOUR";
      query += ",SD.END_MINUTE";
      query += ",SD.WORKTIME";
      query += ",SD.WORKTIME_STATUS ";
      query += "FROM IMW_T_EXT_TIMECARD_SYSTEM_DETAIL AS SD";
      query += ",EIP_T_EXT_TIMECARD_SYSTEM AS S ";
      query += "WHERE S.SYSTEM_ID =" + systemId + "";
      query += " AND SD.SYSTEM_ID= S.SYSTEM_ID";
      SQLTemplate rawSelect = new SQLTemplate(
          ImwTExtTimecardSystemDetail.class, query, true);
      rawSelect.setFetchingDataRows(true);
      List list = dataContext.performQuery(rawSelect);
      DataRow dataRow;
      if (list != null && list.size() != 0) {
        dataRow = (DataRow) list.get(0);
        ImwTExtTimecardSystemDetail detailEntity = new ImwTExtTimecardSystemDetail();
        detailEntity.setSystemId((Integer) ALEipUtils.getObjFromDataRow(
            dataRow, ImwTExtTimecardSystemDetail.SYSTEM_ID_COLUMN));
        detailEntity.setDetailId(String.valueOf(ALEipUtils.getObjFromDataRow(dataRow,
            ImwTExtTimecardSystemDetail.SYSTEM_DETAIL_ID_PK_COLUMN)));
        detailEntity.setStartHour((Integer) ALEipUtils.getObjFromDataRow(
            dataRow, ImwTExtTimecardSystemDetail.START_HOUR_COLUMN));

        detailEntity.setStartMinute((Integer) ALEipUtils.getObjFromDataRow(
            dataRow, ImwTExtTimecardSystemDetail.START_MINUTE_COLUMN));

        detailEntity.setEndHour((Integer) ALEipUtils.getObjFromDataRow(dataRow,
            ImwTExtTimecardSystemDetail.END_HOUR_COLUMN));

        detailEntity.setEndMinute((Integer) ALEipUtils.getObjFromDataRow(
            dataRow, ImwTExtTimecardSystemDetail.END_MINUTE_COLUMN));

        detailEntity.setWorktime((Integer) ALEipUtils.getObjFromDataRow(
            dataRow, ImwTExtTimecardSystemDetail.WORKTIME_COLUMN));

        detailEntity.setWorktimeStatus((String) ALEipUtils.getObjFromDataRow(
            dataRow, ImwTExtTimecardSystemDetail.WORKTIME_STATUS_COLUMN));
        return detailEntity;
      } else {
        return null;
      }
    } catch (RuntimeException e) {
      // TODO 自動生成された catch ブロック
      return null;
    }
  }
  
  /** Add 2010/08/14 チョホン　End--- */
  
  /** Add 2010/08/23 チョホン　Start--- */
  public static int getWorktimeMinute(int timecardId) {
    try {
      DataContext dataContext = DatabaseOrmService.getInstance()
          .getDataContext();
      StringBuffer statement = new StringBuffer();
      statement.append("SELECT DISTINCT ");
      statement.append(" B.worktime_minute ");
      statement.append("FROM eip_t_ext_timecard as A, ");
      statement.append(" eip_t_ext_timecard_system as B ");
      statement.append("WHERE A.timecard_id = ").append(timecardId).append("");
      statement.append(" AND A.system_id = B.system_id");
     
      String query = statement.toString();
      SQLTemplate rawSelect = new SQLTemplate(EipTExtTimecardSystem.class, query, true);
      rawSelect.setFetchingDataRows(true);

      List list = dataContext.performQuery(rawSelect);

      if (list == null || list.size() == 0) {
        return 0;
      }
      DataRow dataRow;
      int record;
      dataRow = (DataRow) list.get(0);
      record = (Integer) ALEipUtils.getObjFromDataRow(dataRow,
          EipTExtTimecardSystem.WORKTIME_MINUTE_COLMN);

      return record;
    } catch (Exception ex) {
      logger.error("Exception", ex);
      return 0;
    }
  }

  /** Add 2010/08/23 チョホン　End--- */
  /* Add　2010/8/24 小濵 Start--- */
  /**
   * 
   * @return
   */
  public static List getAllImwTOffice() {
    try {
      DataContext dataContext = DatabaseOrmService.getInstance()
          .getDataContext();
      SelectQuery query = new SelectQuery(ImwTOffice.class);
      /** Add 2010/10/07 金廣 Start--- */
      // 課題57:タイムカード編集画面のセレクトボックスに削除された勤務先が表示される問題の対応
      Expression exp = ExpressionFactory.matchExp(ImwTOffice.DISABLED_PROPERTY, "F");
      query.andQualifier(exp);
      /** Add 2010/10/07 金廣 End----- */
      List slist = dataContext.performQuery(query);
      if (slist == null || slist.size() == 0) {
        // 指定したSetting IDのレコードが見つからない場合
        logger.debug("[ExtTimecardUtils] Not found ID...");
        return null;
      }
      return slist;
    } catch (Exception ex) {
      logger.error("Exception", ex);
      return null;
    }
  }
  /* Add　2010/8/24 小濵 End--- */

  /* Add　2010/8/30 小濵 Start--- */
  /**
   * 特殊文字をエスケープします。
   */
  public static String escapeSpecial(String word, String escape){
    try{
      if ("\\".equals(escape)){
        // バックスラッシュをLIKE句中で普通の文字として検索するには
        // SQL文中でバックスラッシュが4個必要
        word = word.replace("\\", "\\\\\\\\");
        // エスケープ文字はSQL文中でバックスラッシュ2個
        escape = "\\\\";
      } else {
        word = word.replace(escape, escape + escape);
        // エスケープ文字をバックスラッシュ以外にした場合
        // SQL文中でバックスラッシュは2個でよい
        word = word.replace("\\", "\\\\");
      }
      
      word = word.replaceAll("'", "''");      
      word = word.replace("_", escape + "_");
      word = word.replace("%", escape + "%");
      return word;
    } catch (Exception ex){
      logger.error("Exception", ex);
      return "";
    }
  }
  
  /**
   * 特殊文字をエスケープします。
   */
  public static String escapeSpecial(String word){
    return escapeSpecial(word, "\\");
  }
  /* Add　2010/8/30 小濵 End--- */
  
  /** Add 20110706 小濵 Start --- */
  public static EipTExtTimecardSystem getEipTExtTimecardSystem(
      DataContext dataContext, Long system_id) {
      // TODO 自動生成されたメソッド・スタブ
    try {
      EipTExtTimecardSystem system = (EipTExtTimecardSystem)DataObjectUtils
          .objectForPK(dataContext, EipTExtTimecardSystem.class,system_id);
      return system;
    }catch (Exception ex) {
      logger.error("Exception", ex);
      return null;
    }
  }

  public static ImwTExtTimecardSystemDetail getImwTExtTimecardSystemDetail(
      RunData rundata, Context context) {
  // TODO 自動生成されたメソッド・スタブ
    String id = ALEipUtils.getTemp(rundata, context,
              ALEipConstants.ENTITY_ID);
    try {
           if (id == null || Integer.valueOf(id) == null) {
             // Setting IDが空の場合
             logger.debug("[ExtTimecardUtils] Empty ID...");
             return null;
           }

        DataContext dataContext = DatabaseOrmService.getInstance()
             .getDataContext();
        Expression exp = ExpressionFactory.matchDbExp(
             ImwTExtTimecardSystemDetail.SYSTEM_DETAIL_ID_PK_COLUMN, id); 
        SelectQuery query = new SelectQuery(ImwTExtTimecardSystemDetail.class, exp);
        List slist = dataContext.performQuery(query);
        if (slist == null || slist.size() == 0) {
              // 指定したSetting IDのレコードが見つからない場合
              logger.debug("[ExtTimecardUtils] Not found ID...");
              return null;
            }
        
        return (ImwTExtTimecardSystemDetail) slist.get(0);
    }catch (Exception ex) {
        logger.error("Exception", ex);
        return null;
    }
  }
  /** Add 20110706 小濵 End ----- */

}
