package com.aimluck.eip.book;

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

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

  public BookWork6XlsWriter(){
    // テンプレートファイル名をセット
    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 担当者 "
      + "    ,COUNT (DISTINCT EIP_T_EXT_TIMECARD.PUNCH_DATE) 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 "
      + "    LEFT OUTER 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 "
      + " 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 "
      + " ORDER BY "
      + "    EIP_T_EXT_TIMECARD.USER_ID ";  
    return sql;
  }
  
  @Override
  protected String[][] getXlsFormat(){
    String[][] format = 
      {
        {"担当者","標準"},
        {"出席日数","数値"},
        {"作業時間","数値"}
       };
    return format;
  }
}
