/**
*                                                (客先名) 殿
*                                             サブシステム名
*                           機能名(プログラム名)(プログラムID)
*
*                                                       作成:アイエムシンクタンク株式会社
*                                                       初版:年月日 by IMTT 作成者
*                                                       改版:年月日 by
*/
package com.aimluck.eip.exttimecard;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import org.apache.cayenne.DataRow;
import org.apache.cayenne.access.DataContext;
import org.apache.cayenne.query.SQLTemplate;

import com.aimluck.commons.field.ALDateTimeField;
import com.aimluck.commons.utils.ALDateUtil;
import com.aimluck.eip.cayenne.om.portlet.ImwTJobOrder;
import com.aimluck.eip.cayenne.om.portlet.ImwTOffice;
import com.aimluck.eip.cayenne.om.security.TurbineGroup;
import com.aimluck.eip.cayenne.om.security.TurbineUser;
import com.aimluck.eip.common.ALData;
import com.aimluck.eip.orm.DatabaseOrmService;
import com.aimluck.eip.util.ALEipUtils;

public class ExtTimecardResultDataNew implements ALData {

  // 所属
  private String belong;

  // 氏名
  private String user_name;

  // 業務名
  private String business_name;

  // 作業場所
  private String working_place;

  // 時間
  private Date work_time;

  // 詳細の一日
  private List day_list;

  private DataContext dataContext;

  public void initField() {
    // TODO 自動生成されたメソッド・スタブ
    dataContext = DatabaseOrmService.getInstance().getDataContext();
    belong = "";
    user_name = "";
    business_name = "";
    working_place = "";
    work_time = new Date();
    day_list = new ArrayList();
  }

  public void setThisData(String userId, ALDateTimeField viewMonth) {
    // 所属
    belong = getUserGroupByUserId(userId);

    // 氏名
    user_name = getUserById(userId);

    // 業務名
    business_name = setBusinessName(userId, work_time);

    // 作業場所
    working_place = getOfficeNameByUserId(userId);

    // 詳細の一日
    day_list = getDateListKeys(userId, viewMonth);
  }

  public List getDateListKeys(String userId, ALDateTimeField viewMonth) {
    try {
      List list = new ArrayList();

      /** 現在表示中の月の日数を取得 */
      Calendar cal = Calendar.getInstance();
      cal.setTime(viewMonth.getValue());
      int month = Integer.parseInt(viewMonth.getMonth()) - 1;

      /** ExtTimecardListResultDataのインスタンスをlistに投入。 */
      while (cal.get(Calendar.MONTH) == month) {
        Date date = cal.getTime();
        ExtTimecardDayResultData rd = new ExtTimecardDayResultData();
        rd.initField();
        rd.setDate(date);
        rd.setThisObject(userId);

        list.add(rd);

        cal.add(Calendar.DAY_OF_MONTH, 1);
      }

      return list;
    } catch (Exception e) {
      e.printStackTrace();
      return null;
    }
  }

  /**
   * 業務名
   * 
   * @param userId
   * @param date
   */
  public String setBusinessName(String userId, Date date) {
    String result = "";
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    if (dataContext == null) {
      dataContext = DatabaseOrmService.getInstance().getDataContext();
    }
    if (userId == null || date == null || userId.equals("")) {
      return result;
    }
    try {
      String query = "SELECT O.JOB_ORDER_NAME "
          + " FROM IMW_T_JOB_ORDER AS O "
          + " WHERE O.JOB_ORDER_ID="
          + " (SELECT D.JOB_ORDER_ID "
          + " FROM IMW_T_EXT_TIMECARD_DETAIL AS D,"
          + " EIP_T_EXT_TIMECARD AS T "
          + " WHERE T.TIMECARD_ID=D.TIMECARD_ID "
          + " AND T.USER_ID = "
          + userId
          + " AND (SELECT TO_DATE(DATE_PART('YEAR',T.PUNCH_DATE) || DATE_PART('MONTH',T.PUNCH_DATE),'YYYYMMDD')) = '"
          + sdf.format(date) + "'  "
          + " GROUP BY D.JOB_ORDER_ID ORDER BY COUNT(*) DESC LIMIT 1)";
      SQLTemplate rawSelect = new SQLTemplate(ImwTJobOrder.class, query, true);
      rawSelect.setFetchingDataRows(true);
      List list = dataContext.performQuery(rawSelect);
      SimpleDateFormat sdf1 = new SimpleDateFormat("HH:mm");
      String jobName = "";
      DataRow dataRow;
      if (list != null && list.size() != 0) {
        dataRow = (DataRow) list.get(0);
        jobName = (String) ALEipUtils.getObjFromDataRow(dataRow,
            ImwTJobOrder.JOB_ORDER_NAME_COLUMN);

      }
      result = jobName;
    } catch (RuntimeException e) {
      // TODO 自動生成された catch ブロック
      e.printStackTrace();
    }
    return result;
  }

  /**
   * 氏名
   * 
   * @param userId
   */
  public String getUserById(String userId) {
    String result = "";
    if (dataContext == null) {
      dataContext = DatabaseOrmService.getInstance().getDataContext();
    }
    if (userId == null || userId.equals("")) {
      return result;
    }
    try {
      String query = "SELECT * FROM TURBINE_USER WHERE USER_ID = " + userId;
      SQLTemplate rawSelect = new SQLTemplate(TurbineUser.class, query, true);
      rawSelect.setFetchingDataRows(true);
      List list = dataContext.performQuery(rawSelect);
      SimpleDateFormat sdf = new SimpleDateFormat("HH:mm");
      String userFirstName = "";
      String userLastName = "";
      DataRow dataRow;
      if (list != null && list.size() != 0) {
        dataRow = (DataRow) list.get(0);
        userFirstName = (String) ALEipUtils.getObjFromDataRow(dataRow,
            TurbineUser.FIRST_NAME_COLUMN);
        userLastName = (String) ALEipUtils.getObjFromDataRow(dataRow,
            TurbineUser.LAST_NAME_COLUMN);

      }
      result = userFirstName +" "+ userLastName;
    } catch (RuntimeException e) {
      // TODO 自動生成された catch ブロック
      e.printStackTrace();
    }
    return result;
  }

  /**
   * 所属
   * 
   * @param userId
   */
  public String getUserGroupByUserId(String userId) {
    String result = "";
    if (dataContext == null) {
      dataContext = DatabaseOrmService.getInstance().getDataContext();
    }
    if (userId == null || userId.equals("")) {
      return result;
    }
    try {
      String query = "SELECT G.GROUP_NAME FROM TURBINE_USER_GROUP_ROLE AS R , TURBINE_GROUP AS G   WHERE R.USER_ID= "
          + userId + " AND G.GROUP_ID = R.GROUP_ID ";
      SQLTemplate rawSelect = new SQLTemplate(TurbineGroup.class, query, true);
      rawSelect.setFetchingDataRows(true);
      List list = dataContext.performQuery(rawSelect);
      String groupName = "";
      DataRow dataRow;
      if (list != null && list.size() != 0) {
        dataRow = (DataRow) list.get(0);
        groupName = (String) ALEipUtils.getObjFromDataRow(dataRow,
            TurbineGroup.GROUP_NAME_COLUMN);
      }
      result = groupName;
    } catch (RuntimeException e) {
      // TODO 自動生成された catch ブロック
      e.printStackTrace();
    }
    return result;
  }

  /**
   * 作業場所
   * 
   * @param userId
   */
  public String getOfficeNameByUserId(String userId) {
    String result = "";
    if (dataContext == null) {
      dataContext = DatabaseOrmService.getInstance().getDataContext();
    }
    if (userId == null || userId.equals("")) {
      return result;
    }
    try {
      String query = "SELECT O.OFFICE_NAME FROM IMW_T_OFFICE AS O,IMW_T_OFFICE_CALENDAR_MAP AS C WHERE C.USER_ID= "
          + userId + " AND C.OFFICE_ID= O.OFFICE_ID ";
      SQLTemplate rawSelect = new SQLTemplate(ImwTOffice.class, query, true);
      rawSelect.setFetchingDataRows(true);
      List list = dataContext.performQuery(rawSelect);
      String officeName = "";
      DataRow dataRow;
      if (list != null && list.size() != 0) {
        dataRow = (DataRow) list.get(0);
        officeName = (String) ALEipUtils.getObjFromDataRow(dataRow,
            ImwTOffice.OFFICE_NAME_COLUMN);
      }
      result = officeName;
    } catch (RuntimeException e) {
      // TODO 自動生成された catch ブロック
      e.printStackTrace();
    }
    return result;
  }

  public String getBelong() {
    return belong;
  }

  public void setBelong(String belong) {
    this.belong = belong;
  }

  public String getUser_name() {
    return user_name;
  }

  public void setUser_name(String user_name) {
    this.user_name = user_name;
  }

  public String getBusiness_name() {
    return business_name;
  }

  public void setBusiness_name(String business_name) {
    this.business_name = business_name;
  }

  public String getWorking_place() {
    return working_place;
  }

  public void setWorking_place(String working_place) {
    this.working_place = working_place;
  }

  public Date getWork_time() {
    return work_time;
  }

  public void setWork_time(Date work_time) {
    this.work_time = work_time;
  }

  public List getDay_list() {
    return day_list;
  }

  public void setDay_list(List day_list) {
    this.day_list = day_list;
  }

}
