package com.aimluck.eip.book;

import java.io.FileInputStream;
import java.util.HashMap;
import java.util.List;
import java.util.jar.Attributes;

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

import org.apache.cayenne.DataRow;
import org.apache.cayenne.access.DataContext;
import org.apache.cayenne.query.SQLTemplate;
import org.apache.jetspeed.services.logging.JetspeedLogFactoryService;
import org.apache.jetspeed.services.logging.JetspeedLogger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.turbine.util.RunData;
import org.apache.velocity.context.Context;

import com.aimluck.eip.cayenne.om.portlet.ImwTExtTimecardDetail;
import com.aimluck.eip.common.ALDBErrorException;
import com.aimluck.eip.common.ALPageNotFoundException;
import com.aimluck.eip.modules.screens.BookXlsExportScreen;
import com.aimluck.eip.orm.DatabaseOrmService;

/**
 * 受注＋担当者別集計
 * @author k.kanehiro
 *
 */
public class BookWork1XlsWriter extends IMXlsWriter {

  /** logger */
  private static final JetspeedLogger logger = JetspeedLogFactoryService
      .getLogger(BookWork1XlsWriter.class.getName());
  
  public static final String FILE_NAME = "作業集計";
  private static final String SHEET_NAME = "受注＋担当者別集計";
  private static final String TEMPLATE_NAME  = "template_work";
  
  public BookWork1XlsWriter(){
    // テンプレートファイル名をセット
    setTemplateName(TEMPLATE_NAME);
    // シート名をセット
    setSheetName(SHEET_NAME);
    // 出力ファイル名をセット
    setFileName(FILE_NAME);
  }
  
  @Override
  protected String getSelectQuery(){
    String sql = "SELECT "
      + " IMW_T_JOB_ORDER.JOB_ORDER_NUMBER             AS 受注番号 "
      + " ,IMW_T_JOB_ORDER.JOB_ORDER_NAME               AS 件名 "
      + " ,H_JUCHUKUBUN.HANYOKUBUNNM                    AS 受注区分 "
      + " ,TURBINE_USER.LAST_NAME || ' ' || TURBINE_USER.FIRST_NAME 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 作業時間 "
      + " ,(TOTAL_WORKTIME.WORKTIME / 60)               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) / (TOTAL_WORKTIME.WORKTIME / 60) * 100 AS 担当者負荷構成比［％］"
      //Add 20110119 KOHAMA Start
      + " ,((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) / (TOTAL_JOBTIME.WORKTIME / 60) * 100 AS 作業割合"
      //Add 20110119 KOHAMA End
      + " 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 ( "
      + "    SELECT "
      + "         EIP_T_EXT_TIMECARD.USER_ID "
      + "        ,(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)) AS WORKTIME "
      + "    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 "
      + "    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 "
      + " ) TOTAL_WORKTIME ON "
      + "        TOTAL_WORKTIME.USER_ID                 = EIP_T_EXT_TIMECARD.USER_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 "
      //Add 20110119 KOHAMA Start
      + " LEFT OUTER JOIN ( "
      + "    SELECT "
      + "         IMW_T_JOB_ORDER.JOB_ORDER_NUMBER "
      + "        ,(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)) AS WORKTIME "
      + "    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 "
      + "        INNER JOIN IMW_T_JOB_ORDER ON"
      + "                IMW_T_JOB_ORDER.JOB_ORDER_ID = IMW_T_EXT_TIMECARD_DETAIL.JOB_ORDER_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 "
      + "         IMW_T_JOB_ORDER.JOB_ORDER_NUMBER "
      + " ) TOTAL_JOBTIME ON "
      + "        TOTAL_JOBTIME.JOB_ORDER_NUMBER = IMW_T_JOB_ORDER.JOB_ORDER_NUMBER "
      //Add 20110119 KOHAMA End
      + " LEFT OUTER JOIN IMW_M_HANYO H_JUCHUKUBUN ON "
      + "        H_JUCHUKUBUN.PARENT_HANYO_ID           = '2' "
      + "    AND H_JUCHUKUBUN.VALUE                     = IMW_T_JOB_ORDER.JUCHUKUBUN "
      + " 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 " + "    ,TOTAL_WORKTIME.WORKTIME "
      //Add 20110119 KOHAMA Start
      + "    ,TOTAL_JOBTIME.WORKTIME "
      //Add 20110119 KOHAMA End
      + " ORDER BY "
      //Chg 20110119 KOHAMA Start
      //+ "     IMW_T_JOB_ORDER.JOB_ORDER_NUMBER "
      + "     IMW_T_JOB_ORDER.JOB_ORDER_NUMBER DESC "
      //Chg 20110119 KOHAMA End
     + "    ,EIP_T_EXT_TIMECARD.USER_ID ";

    return sql;
  }

  @Override
  protected String[][] getXlsFormat(){
    String[][] format = 
      {
        {"受注番号","標準"},
        {"件名","標準"},
        {"受注区分","標準"},
        {"担当者","標準"},
        {"作業時間","数値"},
        {"担当者計","数値"},
        {"担当者負荷構成比［％］","数値"}
        //Add 20110119 KOHAMA Start
        ,{"作業割合","数値"}
        ,{"売上","通貨"}
        ,{"外注費","通貨"}
        ,{"原価","通貨"}
        //Add 20110119 KOHAMA End
       };
    return format;
  }  

//  @Override
//  public boolean write(RunData rundata, Context context, HSSFWorkbook wb) {
//    try {
//      HSSFCell cel;
//      short baseRowNumberOfMatrix = 1;
//      short baseColNumberOfMatrix;
//      Object value;
//      
//      List<DataRow> list = selectList(rundata, context);
//      if (list == null || list.size() == 0){
//        return true; 
//      }
//      
//      HSSFSheet sheet = wb.getSheet(SHEET_NAME);     
//
//      //標準フォーマット
//      HSSFDataFormat cdf = wb.createDataFormat();
//      HSSFCellStyle  ccss0 = wb.createCellStyle();
//      ccss0.setDataFormat(cdf.getFormat("@"));
//      ccss0.setWrapText(true);
//      ccss0.setBorderTop(HSSFCellStyle.BORDER_THIN);
//      ccss0.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//      ccss0.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//      ccss0.setBorderRight(HSSFCellStyle.BORDER_THIN);
//      
//      //数値フォーマット
//      HSSFCellStyle  ccss1 = wb.createCellStyle();
//      //ccss1.setDataFormat(cdf.getFormat("0.00"));
//      ccss1.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
//      ccss1.setBorderTop(HSSFCellStyle.BORDER_THIN);
//      ccss1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//      ccss1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//      ccss1.setBorderRight(HSSFCellStyle.BORDER_THIN);
//      
//      //マーク用フォーマット
//      HSSFCellStyle  ccss2 = wb.createCellStyle();
//      
//      for(DataRow drow : list){
//        HSSFRow mRow = sheet.createRow(baseRowNumberOfMatrix ++);
//        baseColNumberOfMatrix = 0;
//        
//        cel = mRow.createCell(baseColNumberOfMatrix ++);
//        cel.setCellStyle(ccss0);
//        cel.setEncoding(HSSFCell.ENCODING_UTF_16);
//        value = drow.get("受注番号");
//        if(value!=null) cel.setCellValue(value.toString());
//
//        cel = mRow.createCell(baseColNumberOfMatrix ++);
//        cel.setCellStyle(ccss0);
//        cel.setEncoding(HSSFCell.ENCODING_UTF_16);
//        value = drow.get("件名");
//        if(value!=null) cel.setCellValue(value.toString());
//        
//        cel = mRow.createCell(baseColNumberOfMatrix ++);
//        cel.setCellStyle(ccss0);
//        cel.setEncoding(HSSFCell.ENCODING_UTF_16);
//        value = drow.get("受注区分");
//        if(value!=null) cel.setCellValue(value.toString());
//        
//        cel = mRow.createCell(baseColNumberOfMatrix ++);
//        cel.setCellStyle(ccss0);
//        cel.setEncoding(HSSFCell.ENCODING_UTF_16);
//        value = drow.get("担当者");
//        if(value!=null) cel.setCellValue(value.toString());
//        
//        cel = mRow.createCell(baseColNumberOfMatrix ++);
//        cel.setCellStyle(ccss1);
//        cel.setEncoding(HSSFCell.ENCODING_UTF_16);
//        value = drow.get("作業時間");
//        if(value!=null) cel.setCellValue(Float.parseFloat(value.toString()));
//        
//        cel = mRow.createCell(baseColNumberOfMatrix ++);
//        cel.setCellStyle(ccss1);
//        cel.setEncoding(HSSFCell.ENCODING_UTF_16);
//        value = drow.get("担当者計");
//        if(value!=null) cel.setCellValue(Float.parseFloat(value.toString()));
//        
//        cel = mRow.createCell(baseColNumberOfMatrix ++);
//        cel.setCellStyle(ccss1);
//        cel.setEncoding(HSSFCell.ENCODING_UTF_16);
//        value = drow.get("担当者負荷構成比［％］");
//        if(value!=null) cel.setCellValue(Float.parseFloat(value.toString()));
//      }
//      
//      return true;
//    } catch (Exception e){
//      logger.error("Exception", e);
//      return false;
//    }
//  }

}
