package com.aimluck.eip.book;

import jp.co.imtt.businesssystem.common.IMXlsWriter;

/**
 * 受注＋作業区分＋担当者別集計
 * @author k.kanehiro
 *
 */
public class BookWork3XlsWriter extends IMXlsWriter {
  
  public static final String FILE_NAME = "作業集計";
  private static final String SHEET_NAME = "受注＋作業区分＋担当者別集計";
  private static final String TEMPLATE_NAME  = "template_work";

  public BookWork3XlsWriter(){
    // テンプレートファイル名をセット
    setTemplateName(TEMPLATE_NAME);
    // シート名をセット
    setSheetName(SHEET_NAME);
    // 出力ファイル名をセット
    setFileName(FILE_NAME);
  }
  
  @Override
  protected String getSelectQuery(){
    String sql = "    SELECT"
        + "    TURBINE_USER.LAST_NAME || ' ' || TURBINE_USER.FIRST_NAME AS 担当者 "
        + "   ,IMW_T_JOB_ORDER.JOB_ORDER_NUMBER             AS 受注番号 "
        + "   ,IMW_T_JOB_ORDER.JOB_ORDER_NAME               AS 件名 "
        + "   ,H_JUCHUKUBUN.HANYOKUBUNNM                    AS 受注区分 "
        + "   ,H_WORK_CLASS.HANYOKUBUNNM                    AS 作業区分 "
        + " ,(cast(SUM(COALESCE(IMW_T_EXT_TIMECARD_DETAIL.WORKTIME_IN,0)) as real) + cast(SUM(COALESCE(IMW_T_EXT_TIMECARD_DETAIL.WORKTIME_OUT,0))as real)) / 60  AS 作業時間 "
        + " FROM EIP_T_EXT_TIMECARD "
        + "   INNER JOIN IMW_T_EXT_TIMECARD_DETAIL ON "
        + "           EIP_T_EXT_TIMECARD.TIMECARD_ID         = IMW_T_EXT_TIMECARD_DETAIL.TIMECARD_ID "
        + "   LEFT OUTER JOIN TURBINE_USER ON "
        + "           TURBINE_USER.USER_ID                   = EIP_T_EXT_TIMECARD.USER_ID "
        + "   LEFT OUTER JOIN IMW_T_JOB_ORDER ON "
        + "           IMW_T_EXT_TIMECARD_DETAIL.JOB_ORDER_ID = IMW_T_JOB_ORDER.JOB_ORDER_ID "
        + "   LEFT OUTER JOIN IMW_M_HANYO H_JUCHUKUBUN ON "
        + "           H_JUCHUKUBUN.PARENT_HANYO_ID           = '2' "
        + "       AND H_JUCHUKUBUN.VALUE                     = IMW_T_JOB_ORDER.JUCHUKUBUN "
        + "   LEFT OUTER JOIN IMW_M_HANYO H_WORK_CLASS ON "
        + "           H_WORK_CLASS.PARENT_HANYO_ID           = '11' "
        + "       AND H_WORK_CLASS.VALUE                     = IMW_T_EXT_TIMECARD_DETAIL.WORK_CLASS "
        + " WHERE " + "       EIP_T_EXT_TIMECARD.PUNCH_DATE >= '$ST' "
        + "   AND EIP_T_EXT_TIMECARD.PUNCH_DATE < '$ED' "
        + "    AND COALESCE(EIP_T_EXT_TIMECARD.PAID_HOLIDAY_TYPE,'') NOT IN ('1', '4', '5', '6', '7') "
        + " GROUP BY " + "    EIP_T_EXT_TIMECARD.USER_ID "
        + "   ,TURBINE_USER.LAST_NAME " + "   ,TURBINE_USER.FIRST_NAME "
        + "   ,IMW_T_JOB_ORDER.JOB_ORDER_NUMBER "
        + "   ,IMW_T_JOB_ORDER.JOB_ORDER_NAME "
        + "   ,H_JUCHUKUBUN.HANYOKUBUNNM " + "   ,H_WORK_CLASS.HANYOKUBUNNM "
        + "   ,H_WORK_CLASS.VALUE "
        + " ORDER BY " 
        + "   IMW_T_JOB_ORDER.JOB_ORDER_NUMBER "
        + "   ,H_WORK_CLASS.VALUE "
        + "   ,EIP_T_EXT_TIMECARD.USER_ID ";
    return sql;
  }
  
  @Override
  protected String[][] getXlsFormat(){
    String[][] format = 
      {
        {"受注番号","標準"},
        {"件名","標準"},
        {"受注区分","標準"},
        {"作業区分","標準"},
        {"担当者","標準"},
        {"作業時間","数値"}
       };
    return format;
  }
}
