/**
 * Copyright (c) 2003-2006 System Integrator Corporation.
 *                 All Rights Reserved.
 */
package jp.co.sint.servlet.mallmgr;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Iterator;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import jp.co.sint.basic.SILogin;
import jp.co.sint.basic.SINameValue;
import jp.co.sint.basic.SIOrder;
import jp.co.sint.beans.mallmgr.UIIOExcelListCond;
import jp.co.sint.beans.mallmgr.UIInventory;
import jp.co.sint.beans.mallmgr.UIMarketingReport;
import jp.co.sint.beans.mallmgr.UISalesStatus;
import jp.co.sint.beans.mallmgr.UISegmentStatus;
import jp.co.sint.config.SICSVConf;
import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIFlagConf;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.database.SIDatabaseConnection;
import jp.co.sint.config.SIExcelConf;
import jp.co.sint.docs.SIExcelRWrite;
import jp.co.sint.tools.SIBGUtil;
import jp.co.sint.tools.SICSVWrite;
import jp.co.sint.tools.SIErrorFactory;
import jp.co.sint.tools.SIFatalException;
import jp.co.sint.tools.SIFlagUtil;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIUtil;
import org.apache.log4j.Category;
import jp.co.sint.tools.SIURLParameter;
import jp.co.sint.upload.SIUploadSrv;

/**
 * @version $Id: SIIOExcelSrv.java,v 1.0 2006/04/24 Exp $
 * @author  帳票出力を処理します。
 * <br>Description:
 * <p>History</p>
 * <p>Author&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Reason</p>
 *  ============&nbsp;&nbsp;&nbsp;==========&nbsp;&nbsp;===========================<br>
 * Hong.M.J        2006/04/24 12:20:42  Original
 */
public class SIIOExcelSrv extends SIUploadSrv{
  //ログ用のインスタンスの生成
  private static Category log=Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  /**
   * <b>doUpdate</b>
   * HTTP リクエストの処理
   * @param request リクエスト
   * @param response
   * @return なし
   * @throws ServletException
   * @throws IOException
   */
  public void doUpdate(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    SILogin manLogin=SIHTMLUtil.getLogin(request);
    if (!manLogin.isLogin()){
      forwardKey(request,response,"webshop.jsp.manager.login");
      return;
    }
    
    HttpSession session=request.getSession(true);//セッションの取得
    SIDatabaseConnection databaseConnection=new SIDatabaseConnection();//DBへのコネクションの作成
    SIURLParameter urlParam = new SIURLParameter(request);
    
    try{
      String actionName=this.getActionName(urlParam);//画面からのアクション
      String editMode = this.getEditMode(urlParam); //DBへの編集モード
      log.debug("actionName="+actionName+",editMode="+editMode);
      
      if(SIConfig.SIACTION_CSV.equalsIgnoreCase(actionName)){//帳票出力
        UIIOExcelListCond ioExcel=new UIIOExcelListCond();
        ioExcel.excelinit(request,urlParam);
        if(ioExcel.excelValidate(request)){
          if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_ORDER_NAME)){//受注一覧
            printOrderList(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_ORDER_DETAIL_NAME)){//受注明細
            printOrderDetail(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_INDIVIDUAL_NAME)){//在庫明細表
            printIndividual(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SALES_NAME)){//売上明細ステータス
            printSalse(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_INVENTORYSTOCK_NAME)){//棚卸差異表
            printInventory(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_INSENTIVE_NAME)){//インセンティブ対象売上一覧表
            printInsentive(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_STOREHISTORY_NAME)){//入出庫一覧表
            printStoreHistory(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_MARKETINGREPORT_NAME)){//マーケティングレポート
            printMarketingReport(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_TRENDREPORT_NAME)){//業績推移表
            printTrendReport(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SALES_STATUS_NAME)){//月次販売ステータス
            printSalesStatus(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SALES_STATUS_ALL_NAME)){//月次販売ステータス（全国集計）
            printSalesStatusAll(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SICSV_HACCHU_PAYMENT_NAME)) {
            produceHacchuPaymentCSVFile(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SEGMENT_STATUS_NAME)){//セグメント別集計表
            printSegmentStatus(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SICSV_HISTORY_DETAIL_NAME)) {
            produceHistoryDetailCSVFile(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SICSV_DEADSTOCK_LIST_NAME)) {
            produceDeadStockListCSVFile(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SICSV_ACTIVEUSER_LIST_NAME)) {
            produceActiveUserListCSVFile(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SICSV_NODELIVERY_LIST_NAME)) {
            produceNoDeliveryListCSVFile(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SICSV_NOPAYMENT_LIST_NAME)) {
            produceNoPaymentListCSVFile(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SALES_STATUS_NAME0)){//月次販売ステータス（支店集計）旧セグメント
            printSalesStatus0(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SALES_STATUS_ALL_NAME0)){//月次販売ステータス（全国集計）旧セグメント
            printSalesStatusAll0(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SEGMENT_STATUS_NAME0)){//セグメント別集計表 旧セグメント
            printSegmentStatus0(response,databaseConnection.getConnection(),ioExcel);
          }else if(ioExcel.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_MARKETINGREPORT_NAME0)){//マーケティングレポート 旧セグメント
            printMarketingReport0(response,databaseConnection.getConnection(),ioExcel);
          }
          if(!response.isCommitted()){//帳票出力失敗
            session.setAttribute(SIConfig.SISESSION_MAN_OUTPUT_EXCEL_NAME,ioExcel);
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("database.query.notexist","帳票データ"));
            forwardKey(request, response, "webshop.jsp.manager.output.excel"); 
          }else{
            //帳票出力成功時の処理を追加する
            //例えば、請求書発行時、DBに日付をアップデートする。
          }
        }else{
          session.setAttribute(SIConfig.SISESSION_MAN_OUTPUT_EXCEL_NAME,ioExcel);
          forwardKey(request, response, "webshop.jsp.manager.output.excel"); 
        }
      }else if(SIConfig.SIACTION_SEARCH.equalsIgnoreCase(actionName)){//検索項目表示
        UIIOExcelListCond ioExcel=new UIIOExcelListCond(urlParam);          
        session.setAttribute(SIConfig.SISESSION_MAN_OUTPUT_EXCEL_NAME,ioExcel);
        forwardKey(request, response, "webshop.jsp.manager.output.excel"); 
      }
    }catch (SQLException e){
      e.printStackTrace();
      throw new ServletException();
    }catch (NamingException e){
      e.printStackTrace();
      throw new ServletException();
    }finally{
      databaseConnection.close();
    }
  }
  
  /**
   * <b>printOrderList</b>
   * 受注一覧表を出力する
   * @param request リクエスト
   * @param response
   * @param lConnection DBへのコネクション
   * @param lItemList データリスト
   * @return なし
   * @throws なし
   */
  private void printOrderList(HttpServletResponse response,Connection lConnection,UIIOExcelListCond ioExcelList){
    String template = (String) this.getServletContext().getRealPath("/docs/order_list.xls");
    SIExcelRWrite lWrite = new SIExcelRWrite(SIExcelConf.SIEXCEL_ORDER_INX,template);
    SIOrder order = null;
    int lineNo = 0;
    int orderint = 0;
    int ENDLIST = 10;
    Collection orderColl= ioExcelList.getOrderListCollection(lConnection);
    Collection brachColl= ioExcelList.getBranchChargeNameCollection(lConnection);
    int brachCount = ioExcelList.getBranchCount(lConnection);
    Iterator it = orderColl.iterator();
    String orderSum[][] = new String[3][ENDLIST];
    String totalSum[] = new String[ENDLIST]; 
    String brachSum[][] = new String[brachCount][ENDLIST];
    
    if(orderColl==null||orderColl.size()==0)return;
    
    //取引区分関連変数
    for(int j=0;j<3;j++){
      for(int i=0;i<ENDLIST;i++){
        orderSum[j][i] = new String("0");
      }
    }
    //支店関連変数
    Iterator brachit =null;
    SINameValue nameValue = new SINameValue();
    if(SIUtil.isNotNull(ioExcelList.getBranchCode())){
      int count = Integer.parseInt(ioExcelList.getBranchCode().trim());
      for(int i=0;i<ENDLIST;i++){
        brachSum[count][i] = new String("0");
      }
    }else{
      brachit =brachColl.iterator();
      while(brachit.hasNext()){
        nameValue = (SINameValue) brachit.next();
        int count = Integer.parseInt(nameValue.getValue().trim());
        for(int i=0;i<ENDLIST;i++){
          brachSum[count][i] = new String("0");
        }
      }
    }
    //合計関連変数
    for(int i=0;i<ENDLIST;i++){
      totalSum[i] = new String("0");
    }
    
    lWrite.makeSheet(0,SIExcelConf.getTitleName(SIExcelConf.SIEXCEL_ORDER_INX),3);
    //ヘッダー部
    lWrite.writeHeaderItem(1,1,ioExcelList.getSeasonFromToCho(),SIExcelConf.FIELD_STRING_TYPE);//受注期間
    lWrite.writeHeaderItem(2,1,ioExcelList.getHeaderBranchCode(lConnection),SIExcelConf.FIELD_STRING_TYPE);//担当支店
    lWrite.writeHeaderItem(2,3,ioExcelList.getHeaderOrderStatusRdo(),SIExcelConf.FIELD_STRING_TYPE);//取引区分
    lWrite.writeHeaderItem(3,1,ioExcelList.getHeaderChargeNameCdo(lConnection),SIExcelConf.FIELD_STRING_TYPE);//担当者
    lWrite.writeHeaderItem(3,3,ioExcelList.getHeaderPayMethodNameCbo(),SIExcelConf.FIELD_STRING_TYPE);//支払区分
    lWrite.writeHeaderItem(4,1,ioExcelList.getHeaderDetailFlgRdo(),SIExcelConf.FIELD_STRING_TYPE);//明細
    lWrite.writeHeaderItem(4,3,ioExcelList.getHeaderOrderRoute(),SIExcelConf.FIELD_STRING_TYPE);//受注経路
    lWrite.writeHeaderItem(5,1,ioExcelList.getHeaderCustCode(),SIExcelConf.FIELD_STRING_TYPE);//顧客ｺｰﾄﾞ
    lWrite.writeHeaderItem(5,3,ioExcelList.getHeaderReceiptFlgRdo(),SIExcelConf.FIELD_STRING_TYPE);//入金状況
    if(ioExcelList.isDetailFlg()){
      //明細部
      while(it.hasNext()){
        order = (SIOrder)it.next();
        lWrite.makeLine(lineNo);
        orderint = Integer.parseInt(order.getOrderStatus());
        if(order.getOrderStatus().equals("0")){//通常
          lWrite.writeDetailItem(0,order.getInitDateTime(),SIExcelConf.FIELD_DATE_TYPE);//受注年月日
        }else{//キャンセル・返品
          lWrite.writeDetailItem(0,order.getUpdateDateTime(),SIExcelConf.FIELD_DATE_TYPE);//受注年月日
        }
        lWrite.writeDetailItem(1,order.getOrderCode(),SIExcelConf.FIELD_STRING_TYPE);//受注番号
        lWrite.writeDetailItem(2,order.getCustCode(),SIExcelConf.FIELD_STRING_TYPE);//顧客ｺｰﾄﾞ
        lWrite.writeDetailItem(3,order.getCustName(),SIExcelConf.FIELD_STRING_TYPE);//顧客名
        lWrite.writeDetailItem(4,order.getBranchName(),SIExcelConf.FIELD_STRING_TYPE);//担当支店
        lWrite.writeDetailItem(5,order.getChargeName(),SIExcelConf.FIELD_STRING_TYPE);//担当者
        lWrite.writeDetailItem(6,SIConfig.ORDERSTATUS[orderint],SIExcelConf.FIELD_STRING_TYPE);//取引区分
        lWrite.writeDetailItem(7,order.getPayMethodName(),SIExcelConf.FIELD_STRING_TYPE);//支払区分
        lWrite.writeDetailItem(8,"",SIExcelConf.FIELD_STRING_TYPE);//ｶｰﾄﾞ区分
        lWrite.writeDetailItem(9,order.getOrderRouteName(),SIExcelConf.FIELD_STRING_TYPE);//受注経路
        lWrite.writeDetailItem(10,order.getTotalOfPrice(),SIExcelConf.FIELD_NUMERIC_TYPE);//販売金額（税込）
        lWrite.writeDetailItem(11,order.getTotalOfDeliveryFee(),SIExcelConf.FIELD_NUMERIC_TYPE);//送料
        lWrite.writeDetailItem(12,order.getSalesPriceInTax(),SIExcelConf.FIELD_NUMERIC_TYPE);//売上金額（税込）
        lWrite.writeDetailItem(13,order.getSalesPrice(),SIExcelConf.FIELD_NUMERIC_TYPE);//売上金額（税抜）合計
        lWrite.writeDetailItem(14,order.getTaxOfPrice(),SIExcelConf.FIELD_NUMERIC_TYPE);//消費税
        lWrite.writeDetailItem(15,order.getTotalOfFee(),SIExcelConf.FIELD_NUMERIC_TYPE);//代引手数料
        lWrite.writeDetailItem(16,order.getSumOfDiscount(),SIExcelConf.FIELD_NUMERIC_TYPE);//値引金額
        lWrite.writeDetailItem(17,order.getSumByPoint(),SIExcelConf.FIELD_NUMERIC_TYPE);//ﾎﾟｲﾝﾄ使用額
        lWrite.writeDetailItem(18,order.getTotalPaymentPrice(),SIExcelConf.FIELD_NUMERIC_TYPE);//入金合計額
        lWrite.writeDetailItem(19,order.getTotalShippmentPrice(),SIExcelConf.FIELD_NUMERIC_TYPE);//出荷合計額
        //取引区分合計
        orderSum[orderint][0] = SIUtil.add(orderSum[orderint][0],order.getTotalOfPrice());
        orderSum[orderint][1] = SIUtil.add(orderSum[orderint][1],order.getTotalOfDeliveryFee());
        orderSum[orderint][2] = SIUtil.add(orderSum[orderint][2],order.getSalesPriceInTax());
        orderSum[orderint][3] = SIUtil.add(orderSum[orderint][3],order.getSalesPrice());
        orderSum[orderint][4] = SIUtil.add(orderSum[orderint][4],order.getTaxOfPrice());
        orderSum[orderint][5] = SIUtil.add(orderSum[orderint][5],order.getTotalOfFee());
        orderSum[orderint][6] = SIUtil.add(orderSum[orderint][6],order.getSumOfDiscount());
        orderSum[orderint][7] = SIUtil.add(orderSum[orderint][7],order.getSumByPoint());
        orderSum[orderint][8] = SIUtil.add(orderSum[orderint][8],order.getTotalPaymentPrice());
        orderSum[orderint][9] = SIUtil.add(orderSum[orderint][9],order.getTotalShippmentPrice());
        //支店別合計
        if(SIUtil.isNotNull(order.getBranchCode())){
          int count = Integer.parseInt(order.getBranchCode());
          brachSum[count][0] = SIUtil.add(brachSum[count][0],order.getTotalOfPrice());
          brachSum[count][1] = SIUtil.add(brachSum[count][1],order.getTotalOfDeliveryFee());
          brachSum[count][2] = SIUtil.add(brachSum[count][2],order.getSalesPriceInTax());
          brachSum[count][3] = SIUtil.add(brachSum[count][3],order.getSalesPrice());
          brachSum[count][4] = SIUtil.add(brachSum[count][4],order.getTaxOfPrice());
          brachSum[count][5] = SIUtil.add(brachSum[count][5],order.getTotalOfFee());
          brachSum[count][6] = SIUtil.add(brachSum[count][6],order.getSumOfDiscount());
          brachSum[count][7] = SIUtil.add(brachSum[count][7],order.getSumByPoint());
          brachSum[count][8] = SIUtil.add(brachSum[count][8],order.getTotalPaymentPrice());
          brachSum[count][9] = SIUtil.add(brachSum[count][9],order.getTotalShippmentPrice());
        }
        //合計
        totalSum[0] = SIUtil.add(totalSum[0],order.getTotalOfPrice());
        totalSum[1] = SIUtil.add(totalSum[1],order.getTotalOfDeliveryFee());
        totalSum[2] = SIUtil.add(totalSum[2],order.getSalesPriceInTax());
        totalSum[3] = SIUtil.add(totalSum[3],order.getSalesPrice());
        totalSum[4] = SIUtil.add(totalSum[4],order.getTaxOfPrice());
        totalSum[5] = SIUtil.add(totalSum[5],order.getTotalOfFee());
        totalSum[6] = SIUtil.add(totalSum[6],order.getSumOfDiscount());
        totalSum[7] = SIUtil.add(totalSum[7],order.getSumByPoint());
        totalSum[8] = SIUtil.add(totalSum[8],order.getTotalPaymentPrice());
        totalSum[9] = SIUtil.add(totalSum[9],order.getTotalShippmentPrice());
        lineNo = lineNo + 1;
      }
      lWrite.removeDetail(3);//ローを整理
    }else{//明細なし
      while(it.hasNext()){
        order = (SIOrder)it.next();
        orderint = Integer.parseInt(order.getOrderStatus());
        //取引区分合計
        orderSum[orderint][0] = SIUtil.add(orderSum[orderint][0],order.getTotalOfPrice());
        orderSum[orderint][1] = SIUtil.add(orderSum[orderint][1],order.getTotalOfDeliveryFee());
        orderSum[orderint][2] = SIUtil.add(orderSum[orderint][2],order.getSalesPriceInTax());
        orderSum[orderint][3] = SIUtil.add(orderSum[orderint][3],order.getSalesPrice());
        orderSum[orderint][4] = SIUtil.add(orderSum[orderint][4],order.getTaxOfPrice());
        orderSum[orderint][5] = SIUtil.add(orderSum[orderint][5],order.getTotalOfFee());
        orderSum[orderint][6] = SIUtil.add(orderSum[orderint][6],order.getSumOfDiscount());
        orderSum[orderint][7] = SIUtil.add(orderSum[orderint][7],order.getSumByPoint());
        orderSum[orderint][8] = SIUtil.add(orderSum[orderint][8],order.getTotalPaymentPrice());
        orderSum[orderint][9] = SIUtil.add(orderSum[orderint][9],order.getTotalShippmentPrice());
        //支店別合計
        if(SIUtil.isNotNull(order.getBranchCode())){
          int count = Integer.parseInt(order.getBranchCode());
          brachSum[count][0] = SIUtil.add(brachSum[count][0] ,order.getTotalOfPrice());
          brachSum[count][1] = SIUtil.add(brachSum[count][1],order.getTotalOfDeliveryFee());
          brachSum[count][2] = SIUtil.add(brachSum[count][2],order.getSalesPriceInTax());
          brachSum[count][3] = SIUtil.add(brachSum[count][3],order.getSalesPrice());
          brachSum[count][4] = SIUtil.add(brachSum[count][4] ,order.getTaxOfPrice());
          brachSum[count][5] = SIUtil.add(brachSum[count][5],order.getTotalOfFee());
          brachSum[count][6] = SIUtil.add(brachSum[count][6],order.getSumOfDiscount());
          brachSum[count][7] = SIUtil.add(brachSum[count][7],order.getSumByPoint());
          brachSum[count][8] = SIUtil.add(brachSum[count][8] ,order.getTotalPaymentPrice());
          brachSum[count][9] = SIUtil.add(brachSum[count][9] ,order.getTotalShippmentPrice());
        }
        //合計
        totalSum[0] = SIUtil.add(totalSum[0],order.getTotalOfPrice());
        totalSum[1] = SIUtil.add(totalSum[1],order.getTotalOfDeliveryFee());
        totalSum[2] = SIUtil.add(totalSum[2],order.getSalesPriceInTax());
        totalSum[3] = SIUtil.add(totalSum[3],order.getSalesPrice());
        totalSum[4] = SIUtil.add(totalSum[4] ,order.getTaxOfPrice());
        totalSum[5] = SIUtil.add(totalSum[5],order.getTotalOfFee());
        totalSum[6] = SIUtil.add(totalSum[6],order.getSumOfDiscount());
        totalSum[7] = SIUtil.add(totalSum[7],order.getSumByPoint());
        totalSum[8] = SIUtil.add(totalSum[8] ,order.getTotalPaymentPrice());
        totalSum[9] = SIUtil.add(totalSum[9] ,order.getTotalShippmentPrice());
      }
      lWrite.removeDetail(3,true);
    }
    //フッター部【取引区分別小計】
    lineNo = 0;
    if(SIUtil.isNotNull(ioExcelList.getOrderStatusRdo())){
      int j = Integer.parseInt(ioExcelList.getOrderStatusRdo());
      lWrite.makeFooterLine(1,lineNo);
      lWrite.writeFooterItem(0,lineNo,0,true);//【取引区分別小計】
      lWrite.writeFooterItem(6,SIConfig.ORDERSTATUS[j],SIExcelConf.FIELD_STRING_TYPE);//取引区分
      for(int i=0;i<ENDLIST;i++){
        lWrite.writeFooterItem(i+10,orderSum[j][i],SIExcelConf.FIELD_NUMERIC_TYPE);//取引区分別小計
      }
    }else{
      for(int j=0;j<3;j++){
        lWrite.makeFooterLine(1,lineNo);
        lWrite.writeFooterItem(0,lineNo,0,true);//【取引区分別小計】
        lWrite.writeFooterItem(6,SIConfig.ORDERSTATUS[j],SIExcelConf.FIELD_STRING_TYPE);//取引区分
        for(int i=0;i<ENDLIST;i++){
          lWrite.writeFooterItem(i+10,orderSum[j][i],SIExcelConf.FIELD_NUMERIC_TYPE);//取引区分別小計
        }
        lineNo = lineNo + 1;
      }
    }
    //フッター部【支店別小計】
    lineNo = 0;
    if(SIUtil.isNotNull(ioExcelList.getBranchCode())){
      lWrite.makeFooterLine(2,lineNo); 
      lWrite.writeFooterItem(0,lineNo,0,true);
      lWrite.writeFooterItem(4,SIUtil.getNameFromColl(brachColl,ioExcelList.getBranchCode()),SIExcelConf.FIELD_STRING_TYPE);
      int count = Integer.parseInt(ioExcelList.getBranchCode().trim());
      for(int i=0;i<ENDLIST;i++){
        lWrite.writeFooterItem(i+10,brachSum[count][i] ,SIExcelConf.FIELD_NUMERIC_TYPE);//合計
      }
    }else{
      brachit =brachColl.iterator();
      while(brachit.hasNext()){
        lWrite.makeFooterLine(2,lineNo);
        lWrite.writeFooterItem(0,lineNo,0,true);
        nameValue = (SINameValue) brachit.next();
        int count = Integer.parseInt(nameValue.getValue().trim());
        lWrite.writeFooterItem(4,nameValue.getName(),SIExcelConf.FIELD_STRING_TYPE);
        for(int i=0;i<ENDLIST;i++){
          lWrite.writeFooterItem(i+10,brachSum[count][i] ,SIExcelConf.FIELD_NUMERIC_TYPE);//合計
        }
        lineNo = lineNo + 1;
      }
    }
    //フッター部【合計】
    lineNo = 0;
    lWrite.makeFooterLine(3,lineNo);
    lWrite.writeFooterItem(0,lineNo,0,true);//【合計】
    for(int i=0;i<ENDLIST;i++){
      lWrite.writeFooterItem(i+10,totalSum[i] ,SIExcelConf.FIELD_NUMERIC_TYPE);//合計
    }
    
    lWrite.makePage();
    try{
      lWrite.close(response);
    }catch(IOException e){
      //e.printStackTrace();
    }
  }
  
  /**
   * <b>printOrderDetail</b>
   * 受注一覧表を出力する
   * @param request リクエスト
   * @param response
   * @param lConnection DBへのコネクション
   * @param lItemList データリスト
   * @return なし
   * @throws なし
   */
  private void printOrderDetail(HttpServletResponse response,Connection lConnection,UIIOExcelListCond ioExcelList){
    String template = (String) this.getServletContext().getRealPath("/docs/order_detail.xls");
    SIExcelRWrite lWrite = new SIExcelRWrite(SIExcelConf.SIEXCEL_ORDER_DETAIL_INX,template); 
    Collection orderDetailColl = ioExcelList.getOrderDetailCollection(lConnection);
    Collection brachColl= ioExcelList.getBranchChargeNameCollection(lConnection);
    int brachCount = ioExcelList.getBranchCount(lConnection);
    Iterator it = orderDetailColl.iterator();
    SIOrder order = null;
    int ENDLIST = 7;
    int lineNo = 0;
    int orderint = 0;
    String oldOrderCode = "";
    String oldStatus = "";
    String orderSum[][] = new String[3][ENDLIST];
    String totalSum[] = new String[ENDLIST];
    String brachSum[][] = new String[brachCount][ENDLIST];
    
    if(orderDetailColl==null||orderDetailColl.size()==0)return;
    
    //取引区分関連変数
    for(int j=0;j<3;j++){
      for(int i=0;i<ENDLIST;i++){
        orderSum[j][i] = new String("0");
      }
    }
    //支店関連変数
    Iterator brachit =null;
    SINameValue nameValue = new SINameValue();
    if(SIUtil.isNotNull(ioExcelList.getBranchCode())){
      int count = Integer.parseInt(ioExcelList.getBranchCode().trim());
      for(int i=0;i<ENDLIST;i++){
        brachSum[count][i] = new String("0");
      }
    }else{
      brachit =brachColl.iterator();
      while(brachit.hasNext()){
        nameValue = (SINameValue) brachit.next();
        int count = Integer.parseInt(nameValue.getValue().trim());
        for(int i=0;i<ENDLIST;i++){
          brachSum[count][i] = new String("0");
        }
      }
    }
    //合計関連変数
    for(int i=0;i<ENDLIST;i++){
      totalSum[i] = new String("0");
    }
    
    lWrite.makeSheet(0,SIExcelConf.getTitleName(SIExcelConf.SIEXCEL_ORDER_DETAIL_INX),3);
    //ヘッダー部
    lWrite.writeHeaderItem(1,1,ioExcelList.getSeasonFromToCho(),SIExcelConf.FIELD_STRING_TYPE);//受注期間
    lWrite.writeHeaderItem(2,1,ioExcelList.getHeaderBranchCode(lConnection),SIExcelConf.FIELD_STRING_TYPE);//担当支店
    lWrite.writeHeaderItem(2,3,ioExcelList.getHeaderShippmentFlgRdo(),SIExcelConf.FIELD_STRING_TYPE);//出荷状況
    lWrite.writeHeaderItem(3,1,ioExcelList.getHeaderChargeNameCdo(lConnection),SIExcelConf.FIELD_STRING_TYPE);//担当者
    lWrite.writeHeaderItem(3,3,ioExcelList.getHeaderOrderStatusRdo(),SIExcelConf.FIELD_STRING_TYPE);//取引区分
    lWrite.writeHeaderItem(4,1,ioExcelList.getHeaderDetailFlgRdo(),SIExcelConf.FIELD_STRING_TYPE);//明細
    lWrite.writeHeaderItem(4,3,ioExcelList.getHeaderUsedNewFlg(),SIExcelConf.FIELD_STRING_TYPE);//中古･新品区分
    lWrite.writeHeaderItem(5,1,ioExcelList.getHeaderCustCode(),SIExcelConf.FIELD_STRING_TYPE);//顧客ｺｰﾄﾞ
    lWrite.writeHeaderItem(5,3,ioExcelList.getHeaderOrderRoute(),SIExcelConf.FIELD_STRING_TYPE);//受注経路   
    
    if(ioExcelList.isDetailFlg()){
      //明細部
      while(it.hasNext()){
        order = (SIOrder)it.next();
        lWrite.makeLine(lineNo);
        orderint = Integer.parseInt(order.getOrderStatus());
        if(!oldOrderCode.equals(order.getOrderCode())||!oldStatus.equals(order.getOrderStatus())){//最初行
          if(order.getOrderStatus().equals("0")){//通常
            lWrite.writeDetailItem(0,order.getInitDateTime(),SIExcelConf.FIELD_DATE_TYPE);//受注年月日
          }else{//キャンセル・返品
            lWrite.writeDetailItem(0,order.getUpdateDateTime(),SIExcelConf.FIELD_DATE_TYPE);//受注年月日
          }
          lWrite.writeDetailItem(1,order.getOrderCode(),SIExcelConf.FIELD_STRING_TYPE);//受注番号
          lWrite.writeDetailItem(2,order.getCustCode(),SIExcelConf.FIELD_STRING_TYPE);//顧客ｺｰﾄﾞ
          lWrite.writeDetailItem(3,order.getCustName(),SIExcelConf.FIELD_STRING_TYPE);//顧客名
          lWrite.writeDetailItem(4);//在庫コード
          lWrite.writeDetailItem(5);//商品名
          lWrite.writeDetailItem(6);//中古･新品区分
          lWrite.writeDetailItem(7,order.getBranchName(),SIExcelConf.FIELD_STRING_TYPE);//担当支店
          lWrite.writeDetailItem(8,order.getChargeName(),SIExcelConf.FIELD_STRING_TYPE);//担当者
          lWrite.writeDetailItem(9,SIConfig.ORDERSTATUS[orderint],SIExcelConf.FIELD_STRING_TYPE);//取引区分
          lWrite.writeDetailItem(10,order.getOrderRouteName(),SIExcelConf.FIELD_STRING_TYPE);//受注経路
          lWrite.writeDetailItem(11);//出荷年月日
          lWrite.writeDetailItem(12);//商品単価
          lWrite.writeDetailItem(13);//数量
          lWrite.writeDetailItem(14);//売上金額（税込）
          lWrite.writeDetailItem(15);//売上金額（税抜）
          lWrite.writeDetailItem(16);//消費税
          lWrite.writeDetailItem(17,order.getTotalOfDeliveryFee(),SIExcelConf.FIELD_NUMERIC_TYPE);//送料
          lWrite.writeDetailItem(18,order.getTotalOfFee(),SIExcelConf.FIELD_NUMERIC_TYPE);//代引手数料
          lWrite.writeDetailItem(19,order.getSumOfDiscount(),SIExcelConf.FIELD_NUMERIC_TYPE);//値引金額
          lWrite.writeDetailItem(20,order.getSumByPoint(),SIExcelConf.FIELD_NUMERIC_TYPE);//ﾎﾟｲﾝﾄ使用額
          orderSum[orderint][3] = SIUtil.add(orderSum[orderint][3],order.getTotalOfDeliveryFee());
          orderSum[orderint][4] = SIUtil.add(orderSum[orderint][4],order.getTotalOfFee());
          orderSum[orderint][5] = SIUtil.add(orderSum[orderint][5],order.getSumOfDiscount());
          orderSum[orderint][6] = SIUtil.add(orderSum[orderint][6],order.getSumByPoint());
          //支店別合計
          if(SIUtil.isNotNull(order.getBranchCode())){
            int count = Integer.parseInt(order.getBranchCode());
            brachSum[count][3] = SIUtil.add(brachSum[count][3],order.getTotalOfDeliveryFee());
            brachSum[count][4] = SIUtil.add(brachSum[count][4],order.getTotalOfFee());
            brachSum[count][5] = SIUtil.add(brachSum[count][5],order.getSumOfDiscount());
            brachSum[count][6] = SIUtil.add(brachSum[count][6],order.getSumByPoint());
          }
          //合計
          totalSum[3] = SIUtil.add(totalSum[3],order.getTotalOfDeliveryFee());
          totalSum[4] = SIUtil.add(totalSum[4],order.getTotalOfFee());
          totalSum[5] = SIUtil.add(totalSum[5],order.getSumOfDiscount());
          totalSum[6] = SIUtil.add(totalSum[6],order.getSumByPoint());
          oldOrderCode = order.getOrderCode();
          oldStatus = order.getOrderStatus();
          lineNo = lineNo + 1;
          lWrite.makeLine(lineNo);
        }
        if(order.getOrderStatus().equals("0")){//通常
          lWrite.writeDetailItem(0,order.getInitDateTime(),SIExcelConf.FIELD_DATE_TYPE);//受注年月日
        }else{//キャンセル・返品
          lWrite.writeDetailItem(0,order.getUpdateDateTime(),SIExcelConf.FIELD_DATE_TYPE);//受注年月日
        }
        lWrite.writeDetailItem(1,order.getOrderCode(),SIExcelConf.FIELD_STRING_TYPE);//受注番号
        lWrite.writeDetailItem(2,order.getCustCode(),SIExcelConf.FIELD_STRING_TYPE);//顧客ｺｰﾄﾞ
        lWrite.writeDetailItem(3,order.getCustName(),SIExcelConf.FIELD_STRING_TYPE);//顧客名
        lWrite.writeDetailItem(4,order.getIndividualCode(),SIExcelConf.FIELD_STRING_TYPE);//在庫コード
        lWrite.writeDetailItem(5,order.getCmdtyName(),SIExcelConf.FIELD_STRING_TYPE);//商品名
        lWrite.writeDetailItem(6,order.getUsednewFlgName(),SIExcelConf.FIELD_STRING_TYPE);//中古･新品区分
        lWrite.writeDetailItem(7,order.getBranchName(),SIExcelConf.FIELD_STRING_TYPE);//担当支店
        lWrite.writeDetailItem(8,order.getChargeName(),SIExcelConf.FIELD_STRING_TYPE);//担当者
        lWrite.writeDetailItem(9,SIConfig.ORDERSTATUS[orderint],SIExcelConf.FIELD_STRING_TYPE);//取引区分
        lWrite.writeDetailItem(10,order.getOrderRouteName(),SIExcelConf.FIELD_STRING_TYPE);//受注経路
        lWrite.writeDetailItem(11,order.getShippmentDate(),SIExcelConf.FIELD_DATE_TYPE);//出荷年月日
        lWrite.writeDetailItem(12,order.getPrice(),SIExcelConf.FIELD_NUMERIC_TYPE);//商品単価
        lWrite.writeDetailItem(13,order.getAmount(),SIExcelConf.FIELD_NUMERIC_TYPE);//数量
        lWrite.writeDetailItem(14,order.getSalesPriceInTax(),SIExcelConf.FIELD_NUMERIC_TYPE);//売上金額（税込）
        lWrite.writeDetailItem(15,order.getSalesPrice(),SIExcelConf.FIELD_NUMERIC_TYPE);//売上金額（税抜）
        lWrite.writeDetailItem(16,order.getTaxOfPrice(),SIExcelConf.FIELD_NUMERIC_TYPE);//消費税
        lWrite.writeDetailItem(17);//送料
        lWrite.writeDetailItem(18);//代引手数料
        lWrite.writeDetailItem(19);//値引金額
        lWrite.writeDetailItem(20);//ﾎﾟｲﾝﾄ使用額
        orderSum[orderint][0] = SIUtil.add(orderSum[orderint][0],order.getSalesPriceInTax());
        orderSum[orderint][1] = SIUtil.add(orderSum[orderint][1],order.getSalesPrice());
        orderSum[orderint][2] = SIUtil.add(orderSum[orderint][2],order.getTaxOfPrice());
        //支店別合計
        if(SIUtil.isNotNull(order.getBranchCode())){
          int count = Integer.parseInt(order.getBranchCode());
          brachSum[count][0] = SIUtil.add(brachSum[count][0],order.getSalesPriceInTax());
          brachSum[count][1] = SIUtil.add(brachSum[count][1],order.getSalesPrice());
          brachSum[count][2] = SIUtil.add(brachSum[count][2],order.getTaxOfPrice());
        }
        totalSum[0] = SIUtil.add(totalSum[0],order.getSalesPriceInTax());
        totalSum[1] = SIUtil.add(totalSum[1],order.getSalesPrice());
        totalSum[2] = SIUtil.add(totalSum[2],order.getTaxOfPrice());
        lineNo = lineNo + 1;
      }
      lWrite.removeDetail(3);
    }else{//明細なし
      while(it.hasNext()){
        order = (SIOrder)it.next();
        orderint = Integer.parseInt(order.getOrderStatus());
        if(!oldOrderCode.equals(order.getOrderCode())||!oldStatus.equals(order.getStatus())){//最初行
          orderSum[orderint][3] = SIUtil.add(orderSum[orderint][3],order.getTotalOfDeliveryFee());
          orderSum[orderint][4] = SIUtil.add(orderSum[orderint][4],order.getTotalOfFee());
          orderSum[orderint][5] = SIUtil.add(orderSum[orderint][5],order.getSumOfDiscount());
          orderSum[orderint][6] = SIUtil.add(orderSum[orderint][6],order.getSumByPoint());
          
          //支店別合計
          if(SIUtil.isNotNull(order.getBranchCode())){
            int count = Integer.parseInt(order.getBranchCode());
            brachSum[count][3] = SIUtil.add(brachSum[count][3],order.getTotalOfDeliveryFee());
            brachSum[count][4] = SIUtil.add(brachSum[count][4],order.getTotalOfFee());
            brachSum[count][5] = SIUtil.add(brachSum[count][5],order.getSumOfDiscount());
            brachSum[count][6] = SIUtil.add(brachSum[count][6],order.getSumByPoint());
          }
          
          //合計
          totalSum[3] = SIUtil.add(totalSum[3],order.getTotalOfDeliveryFee());
          totalSum[4] = SIUtil.add(totalSum[4],order.getTotalOfFee());
          totalSum[5] = SIUtil.add(totalSum[5],order.getSumOfDiscount());
          totalSum[6] = SIUtil.add(totalSum[6],order.getSumByPoint());
          
          oldOrderCode = order.getOrderCode();
          oldStatus = order.getStatus();
        }
        orderSum[orderint][0] = SIUtil.add(orderSum[orderint][0],order.getSalesPriceInTax());
        orderSum[orderint][1] = SIUtil.add(orderSum[orderint][1],order.getSalesPrice());
        orderSum[orderint][2] = SIUtil.add(orderSum[orderint][2],order.getTaxOfPrice());
        //支店別合計
        if(SIUtil.isNotNull(order.getBranchCode())){//売上のみ合計
          int count = Integer.parseInt(order.getBranchCode());
          brachSum[count][0] = SIUtil.add(brachSum[count][0],order.getSalesPriceInTax());
          brachSum[count][1] = SIUtil.add(brachSum[count][1],order.getSalesPrice());
          brachSum[count][2] = SIUtil.add(brachSum[count][2],order.getTaxOfPrice());
        }
        totalSum[0] = SIUtil.add(totalSum[0],order.getSalesPriceInTax());
        totalSum[1] = SIUtil.add(totalSum[1],order.getSalesPrice());
        totalSum[2] = SIUtil.add(totalSum[2],order.getTaxOfPrice());
      }
      lWrite.removeDetail(3,true);
    }
    //フッター部【取引区分別小計】
    lineNo = 0;
    log.debug("受注一覧表：フッター部生成");
    if(SIUtil.isNotNull(ioExcelList.getOrderStatusRdo())){
      int j = Integer.parseInt(ioExcelList.getOrderStatusRdo());
      lWrite.makeFooterLine(1,lineNo);
      lWrite.writeFooterItem(0,lineNo,0,true);//【取引区分別小計】
      lWrite.writeFooterItem(9,SIConfig.ORDERSTATUS[j],SIExcelConf.FIELD_STRING_TYPE);//取引区分	
      for(int i=0;i<ENDLIST;i++){
        lWrite.writeFooterItem(i+14,orderSum[j][i],SIExcelConf.FIELD_NUMERIC_TYPE);//取引区分別小計
      }    
    }else{
      for(int j=0;j<3;j++){
        lWrite.makeFooterLine(1,lineNo);
        lWrite.writeFooterItem(0,lineNo,0,true);//【取引区分別小計】
        lWrite.writeFooterItem(9,SIConfig.ORDERSTATUS[j],SIExcelConf.FIELD_STRING_TYPE);//取引区分
        for(int i=0;i<ENDLIST;i++){
          lWrite.writeFooterItem(i+14,orderSum[j][i],SIExcelConf.FIELD_NUMERIC_TYPE);//取引区分別小計
        }
        lineNo = lineNo + 1;
      }
    }
    //フッター部【支店別小計】
    lineNo = 0;
    if(SIUtil.isNotNull(ioExcelList.getBranchCode())){
      lWrite.makeFooterLine(2,lineNo);
      lWrite.writeFooterItem(0,lineNo,0,true);
      lWrite.writeFooterItem(7,SIUtil.getNameFromColl(brachColl,ioExcelList.getBranchCode()),SIExcelConf.FIELD_STRING_TYPE);
      int count = Integer.parseInt(ioExcelList.getBranchCode().trim());
      for(int i=0;i<ENDLIST;i++){
        lWrite.writeFooterItem(i+14,brachSum[count][i] ,SIExcelConf.FIELD_NUMERIC_TYPE);//合計
      }
    }else{
      brachit =brachColl.iterator();
      while(brachit.hasNext()){
        lWrite.makeFooterLine(2,lineNo);
        lWrite.writeFooterItem(0,lineNo,0,true);
        nameValue = (SINameValue) brachit.next();
        int count = Integer.parseInt(nameValue.getValue().trim());
        lWrite.writeFooterItem(7,nameValue.getName(),SIExcelConf.FIELD_STRING_TYPE);
        for(int i=0;i<ENDLIST;i++){
          lWrite.writeFooterItem(i+14,brachSum[count][i] ,SIExcelConf.FIELD_NUMERIC_TYPE);//合計
        }
        lineNo = lineNo + 1;
      }
    }
    
    lineNo = 0;
    //フッター部【合計】
    lWrite.makeFooterLine(3,lineNo);
    lWrite.writeFooterItem(0,lineNo,0,true);//【合計】
    for(int i=0;i<ENDLIST;i++){
      lWrite.writeFooterItem(i+14,totalSum[i] ,SIExcelConf.FIELD_NUMERIC_TYPE);//合計
    }
    lWrite.makePage();
    try{
      lWrite.close(response);
    }catch(IOException e){
      //e.printStackTrace();
    }
  }
  
  /**
   * <b>printSalse</b>
   * 売上明細ステータスを出力する
   * @param request リクエスト
   * @param response
   * @param lConnection DBへのコネクション
   * @param lItemList データリスト
   * @return なし
   * @throws なし
   */
  private void printSalse(HttpServletResponse response,Connection lConnection,UIIOExcelListCond ioExcelList){
    String template = (String) this.getServletContext().getRealPath("/docs/sales.xls");
    SIExcelRWrite lWrite = new SIExcelRWrite(SIExcelConf.SIEXCEL_SALES_INX,template); 
    Collection salesColl = ioExcelList.getSalesCollection(lConnection);
    Collection brachColl= ioExcelList.getBranchChargeNameCollection(lConnection);
    Collection chargeColl= ioExcelList.getChargeBranchNameCollection(lConnection);
    int brachCount = ioExcelList.getBranchCount(lConnection);
    int chargeCount = ioExcelList.getChargeCount(lConnection);
    
    Iterator it = salesColl.iterator();
    SIOrder order = null;
    int lineNo = 0;
    int ENDLIST = 10;
    int orderint = 0;
    String oldOrderCode = "";
    String oldStatus = "";
    String divTemp = "0";
    String orderSum[][] = new String[2][ENDLIST];
    String totalSum[] = new String[ENDLIST];
    String brachSum[][] = new String[brachCount][ENDLIST];
    String chargeSum[][][] = new String[brachCount][chargeCount+1][ENDLIST];
    
    if(salesColl==null||salesColl.size()==0)return;
    
    //取引区分関連変数
    for(int j=0;j<2;j++){
      for(int i=0;i<ENDLIST;i++){
        orderSum[j][i] = new String("0");
      }
    }
    //支店関連変数
    Iterator brachit =null;
    SINameValue nameValue = new SINameValue();
    if(SIUtil.isNotNull(ioExcelList.getBranchCode())){
      int count = Integer.parseInt(ioExcelList.getBranchCode().trim());
      for(int i=0;i<ENDLIST;i++){
        brachSum[count][i] = new String("0");
      }
    }else{
      brachit =brachColl.iterator();
      while(brachit.hasNext()){
        nameValue = (SINameValue) brachit.next();
        int count = Integer.parseInt(nameValue.getValue().trim());
        for(int i=0;i<ENDLIST;i++){
          brachSum[count][i] = new String("0");
        }
      }
    }
    //担当者関連変数
    Iterator chargeit =null;
    if(SIUtil.isNotNull(ioExcelList.getChargeNameCdo())){
      int count = Integer.parseInt(ioExcelList.getChargeNameCdo().trim().substring(0,3));
      int count2 = Integer.parseInt(ioExcelList.getChargeNameCdo().trim().substring(3,5));
      for(int i=0;i<ENDLIST;i++){
        chargeSum[count][count2][i] = new String("0");
      }
    }else{
      chargeit =chargeColl.iterator();
      while(chargeit.hasNext()){
        nameValue = (SINameValue) chargeit.next();
        int count = Integer.parseInt(nameValue.getValue().trim().substring(0,3));
        int count2 = Integer.parseInt(nameValue.getValue().trim().substring(3,5));
        for(int i=0;i<ENDLIST;i++){
          chargeSum[count][count2][i] = new String("0");
        }
      }
    }
    //合計関連変数
    for(int i=0;i<ENDLIST;i++){
      totalSum[i] = new String("0");
    }
    lWrite.makeSheet(0,SIExcelConf.getTitleName(SIExcelConf.SIEXCEL_SALES_INX),4);
    //ヘッダー部
    lWrite.writeHeaderItem(1,1,ioExcelList.getShipmentDateFromToCho(),SIExcelConf.FIELD_STRING_TYPE);//出荷期間
    lWrite.writeHeaderItem(2,1,ioExcelList.getSeasonFromToCho(),SIExcelConf.FIELD_STRING_TYPE);//受注期間    
    lWrite.writeHeaderItem(3,1,ioExcelList.getHeaderBranchCode(lConnection),SIExcelConf.FIELD_STRING_TYPE);//担当支店
    lWrite.writeHeaderItem(3,3,ioExcelList.getHeaderIndividualCode(),SIExcelConf.FIELD_STRING_TYPE);//在庫コード
    lWrite.writeHeaderItem(4,1,ioExcelList.getHeaderChargeNameCdo(lConnection),SIExcelConf.FIELD_STRING_TYPE);//担当者
    lWrite.writeHeaderItem(4,3,ioExcelList.getHeaderSalesOrderStatusRdo(),SIExcelConf.FIELD_STRING_TYPE);//取引区分
    lWrite.writeHeaderItem(5,1,ioExcelList.getHeaderDetailFlgRdo(),SIExcelConf.FIELD_STRING_TYPE);//明細
    lWrite.writeHeaderItem(5,3,ioExcelList.getHeaderUsedNewFlg(),SIExcelConf.FIELD_STRING_TYPE);//中古･新品区分
    lWrite.writeHeaderItem(6,1,ioExcelList.getHeaderCustCode(),SIExcelConf.FIELD_STRING_TYPE);//顧客ｺｰﾄﾞ
    lWrite.writeHeaderItem(6,3,ioExcelList.getHeaderOrderRoute(),SIExcelConf.FIELD_STRING_TYPE);//受注経路
    if(ioExcelList.isDetailFlg()){//明細あり
      //明細部
      while(it.hasNext()){
        order = (SIOrder)it.next();
        lWrite.makeLine(lineNo);
        orderint = Integer.parseInt(order.getOrderStatus());
        boolean isShippmentMin = false;
        try{
          isShippmentMin = !SIDBUtil.hasData(lConnection,"SELECT * FROM orderdetaillatestvw WHERE ordercode="+SIDBUtil.SQL2Str(order.getOrderCode()," ")+"AND shippmentdate < "+SIDBUtil.SQL2Str(order.getShippmentDate()));
        }catch(Exception e){e.printStackTrace();}
        if((!oldOrderCode.equals(order.getOrderCode())||!oldStatus.equals(order.getOrderStatus()))
         &&(order.getOrderStatus().equals("1")||isShippmentMin)//最初行&&同一受注番号内での最初の出荷
         &&!order.getOrderStatus().equals("2")){//訂正は含まない
          lWrite.writeDetailItem(0,order.getShippmentDate(),SIExcelConf.FIELD_DATE_TYPE);//出荷年月日
//          if(order.getOrderStatus().equals("0")){//通常
//            lWrite.writeDetailItem(1,order.getInitDateTime(),SIExcelConf.FIELD_DATE_TYPE);//受注年月日
//          }else{//キャンセル・返品
//            lWrite.writeDetailItem(1,order.getUpdateDateTime(),SIExcelConf.FIELD_DATE_TYPE);//受注年月日
//          }
          lWrite.writeDetailItem(1,order.getInitDateTime(),SIExcelConf.FIELD_DATE_TYPE);//受注年月日
          
          lWrite.writeDetailItem(2,order.getOrderCode(),SIExcelConf.FIELD_STRING_TYPE);//受注番号
          lWrite.writeDetailItem(3,order.getCustCode(),SIExcelConf.FIELD_STRING_TYPE);//顧客ｺｰﾄﾞ
          lWrite.writeDetailItem(4,order.getCustName(),SIExcelConf.FIELD_STRING_TYPE);//顧客名
          lWrite.writeDetailItem(5);//在庫コード
          lWrite.writeDetailItem(6);//商品名
          lWrite.writeDetailItem(7);//中古･新品区分
          lWrite.writeDetailItem(8,SIConfig.SALESORDERSTATUS[orderint],SIExcelConf.FIELD_STRING_TYPE);//取引区分
          lWrite.writeDetailItem(9,order.getBranchName(),SIExcelConf.FIELD_STRING_TYPE);//担当支店
          lWrite.writeDetailItem(10,order.getChargeName(),SIExcelConf.FIELD_STRING_TYPE);//担当者
          lWrite.writeDetailItem(11);//単価
          lWrite.writeDetailItem(12);//数量
          lWrite.writeDetailItem(13);//売上金額（税込）
          lWrite.writeDetailItem(14);//売上金額（税抜）
          lWrite.writeDetailItem(15);//商品原価（税込）
          lWrite.writeDetailItem(16);//商品原価（税抜）
          lWrite.writeDetailItem(17);//粗利益粗
          lWrite.writeDetailItem(18);//利益率
          lWrite.writeDetailItem(19,order.getTotalOfDeliveryFee(),SIExcelConf.FIELD_NUMERIC_TYPE);//送料
          lWrite.writeDetailItem(20,order.getTotalOfFee(),SIExcelConf.FIELD_NUMERIC_TYPE);//代引手数料
          lWrite.writeDetailItem(21,order.getSumOfDiscount(),SIExcelConf.FIELD_NUMERIC_TYPE);//値引金額
          lWrite.writeDetailItem(22,order.getSumByPoint(),SIExcelConf.FIELD_NUMERIC_TYPE);//ﾎﾟｲﾝﾄ使用額
          orderSum[orderint][6] = SIUtil.add(orderSum[orderint][6],order.getTotalOfDeliveryFee());
          orderSum[orderint][7] = SIUtil.add(orderSum[orderint][7],order.getTotalOfFee());
          orderSum[orderint][8] = SIUtil.add(orderSum[orderint][8],order.getSumOfDiscount());
          orderSum[orderint][9] = SIUtil.add(orderSum[orderint][9],order.getSumByPoint());
          //支店別合計
          if(SIUtil.isNotNull(order.getBranchCode())){//売上のみ合計
            int count = Integer.parseInt(order.getBranchCode());
            brachSum[count][6] = SIUtil.add(brachSum[count][6],order.getTotalOfDeliveryFee());
            brachSum[count][7] = SIUtil.add(brachSum[count][7],order.getTotalOfFee());
            brachSum[count][8] = SIUtil.add(brachSum[count][8],order.getSumOfDiscount());
            brachSum[count][9] = SIUtil.add(brachSum[count][9],order.getSumByPoint());
          }
          //担当者別合計
          if(SIUtil.isNotNull(order.getChargeCode())){//売上のみ合計
            int count = Integer.parseInt(order.getChargeCode().trim().substring(0,3));
            int count2 = Integer.parseInt(order.getChargeCode().trim().substring(3,5));
            chargeSum[count][count2][6] = SIUtil.add(chargeSum[count][count2][6],order.getTotalOfDeliveryFee());
            chargeSum[count][count2][7] = SIUtil.add(chargeSum[count][count2][7],order.getTotalOfFee());
            chargeSum[count][count2][8] = SIUtil.add(chargeSum[count][count2][8],order.getSumOfDiscount());
            chargeSum[count][count2][9] = SIUtil.add(chargeSum[count][count2][9],order.getSumByPoint());
          }
          //合計
          totalSum[6] = SIUtil.add(totalSum[6],order.getTotalOfDeliveryFee());
          totalSum[7] = SIUtil.add(totalSum[7],order.getTotalOfFee());
          totalSum[8] = SIUtil.add(totalSum[8],order.getSumOfDiscount());
          totalSum[9] = SIUtil.add(totalSum[9],order.getSumByPoint());
          oldOrderCode = order.getOrderCode();
          oldStatus = order.getOrderStatus();
          lineNo = lineNo + 1;
          lWrite.makeLine(lineNo);
        }
        lWrite.writeDetailItem(0,order.getShippmentDate(),SIExcelConf.FIELD_DATE_TYPE);//出荷年月日
//        if(order.getOrderStatus().equals("0")){//通常
//          lWrite.writeDetailItem(1,order.getInitDateTime(),SIExcelConf.FIELD_DATE_TYPE);//受注年月日
//        }else{//キャンセル・返品
//          lWrite.writeDetailItem(1,order.getUpdateDateTime(),SIExcelConf.FIELD_DATE_TYPE);//受注年月日
//        }
        lWrite.writeDetailItem(1,order.getInitDateTime(),SIExcelConf.FIELD_DATE_TYPE);//受注年月日
        lWrite.writeDetailItem(2,order.getOrderCode(),SIExcelConf.FIELD_STRING_TYPE);//受注番号
        lWrite.writeDetailItem(3,order.getCustCode(),SIExcelConf.FIELD_STRING_TYPE);//顧客ｺｰﾄﾞ
        lWrite.writeDetailItem(4,order.getCustName(),SIExcelConf.FIELD_STRING_TYPE);//顧客名
        lWrite.writeDetailItem(5,order.getIndividualCode(),SIExcelConf.FIELD_STRING_TYPE);//在庫コード
        lWrite.writeDetailItem(6,order.getCmdtyName(),SIExcelConf.FIELD_STRING_TYPE);//商品名
        lWrite.writeDetailItem(7,order.getUsednewFlgName(),SIExcelConf.FIELD_STRING_TYPE);//中古･新品区分
        lWrite.writeDetailItem(8,SIConfig.SALESORDERSTATUS[orderint],SIExcelConf.FIELD_STRING_TYPE);//取引区分
        if(orderint == 2) orderint = 0;//訂正データは売上に計上する
        lWrite.writeDetailItem(9,order.getBranchName(),SIExcelConf.FIELD_STRING_TYPE);//担当支店
        lWrite.writeDetailItem(10,order.getChargeName(),SIExcelConf.FIELD_STRING_TYPE);//担当者
        lWrite.writeDetailItem(11,order.getPrice(),SIExcelConf.FIELD_NUMERIC_TYPE);//単価
        lWrite.writeDetailItem(12,order.getAmount(),SIExcelConf.FIELD_NUMERIC_TYPE);//数量
        lWrite.writeDetailItem(13,order.getSalesPriceInTax(),SIExcelConf.FIELD_NUMERIC_TYPE);//売上金額（税込）
        lWrite.writeDetailItem(14,order.getSalesPrice(),SIExcelConf.FIELD_NUMERIC_TYPE);//売上金額（税抜）
        lWrite.writeDetailItem(15,order.getCmdtyPriceInTax(),SIExcelConf.FIELD_NUMERIC_TYPE);//商品原価（税込）
        lWrite.writeDetailItem(16,order.getCmdtyPrice(),SIExcelConf.FIELD_NUMERIC_TYPE);//商品原価（税抜）
        lWrite.writeDetailItem(17,order.getProfitPrice(),SIExcelConf.FIELD_NUMERIC_TYPE);//粗利益粗
        divTemp = "0";//粗利益率
        if(SIUtil.isNotNull(order.getSalesPrice())&&!order.getSalesPrice().equals("0")&&SIUtil.isNotNull(order.getProfitPrice())&&!order.getProfitPrice().equals("0")){                  
          try{
            divTemp = String.valueOf((SIUtil.div_FL(order.getProfitPrice(),order.getSalesPrice())*100));
          }catch(SIFatalException e1){
            e1.printStackTrace();
          }
        }
        lWrite.writeDetailItem(18,divTemp,SIExcelConf.FIELD_NUMERIC_TYPE);//利益率
        lWrite.writeDetailItem(19);//送料
        lWrite.writeDetailItem(20);//代引手数料
        lWrite.writeDetailItem(21);//値引金額
        lWrite.writeDetailItem(22);//ﾎﾟｲﾝﾄ使用額 
        orderSum[orderint][0] = SIUtil.add(orderSum[orderint][0],order.getSalesPriceInTax());
        orderSum[orderint][1] = SIUtil.add(orderSum[orderint][1],order.getSalesPrice());
        orderSum[orderint][2] = SIUtil.add(orderSum[orderint][2],order.getCmdtyPriceInTax());
        orderSum[orderint][3] = SIUtil.add(orderSum[orderint][3],order.getCmdtyPrice());
        orderSum[orderint][4] = SIUtil.add(orderSum[orderint][4],order.getProfitPrice());
        //支店別合計
        if(SIUtil.isNotNull(order.getBranchCode())){//売上のみ合計
          int count = Integer.parseInt(order.getBranchCode());
          brachSum[count][0] = SIUtil.add(brachSum[count][0],order.getSalesPriceInTax());
          brachSum[count][1] = SIUtil.add(brachSum[count][1],order.getSalesPrice());
          brachSum[count][2] = SIUtil.add(brachSum[count][2],order.getCmdtyPriceInTax());
          brachSum[count][3] = SIUtil.add(brachSum[count][3],order.getCmdtyPrice());
          brachSum[count][4] = SIUtil.add(brachSum[count][4],order.getProfitPrice());
        }
        //担当者別合計
        if(SIUtil.isNotNull(order.getChargeCode())){//売上のみ合計
          int count = Integer.parseInt(order.getChargeCode().trim().substring(0,3));
          int count2 = Integer.parseInt(order.getChargeCode().trim().substring(3,5));
          chargeSum[count][count2][0] = SIUtil.add(chargeSum[count][count2][0],order.getSalesPriceInTax());
          chargeSum[count][count2][1] = SIUtil.add(chargeSum[count][count2][1],order.getSalesPrice());
          chargeSum[count][count2][2] = SIUtil.add(chargeSum[count][count2][2],order.getCmdtyPriceInTax());
          chargeSum[count][count2][3] = SIUtil.add(chargeSum[count][count2][3],order.getCmdtyPrice());
          chargeSum[count][count2][4] = SIUtil.add(chargeSum[count][count2][4],order.getProfitPrice());
        }
        //合計
        totalSum[0] = SIUtil.add(totalSum[0],order.getSalesPriceInTax());
        totalSum[1] = SIUtil.add(totalSum[1],order.getSalesPrice());
        totalSum[2] = SIUtil.add(totalSum[2],order.getCmdtyPriceInTax());
        totalSum[3] = SIUtil.add(totalSum[3],order.getCmdtyPrice());
        totalSum[4] = SIUtil.add(totalSum[4],order.getProfitPrice());
        lineNo = lineNo + 1;
      }
      lWrite.removeDetail(4);
    }else{//明細なし
      while(it.hasNext()){
        order = (SIOrder)it.next();
        if(!oldOrderCode.equals(order.getOrderCode())||!oldStatus.equals(order.getStatus())){//最初行
          orderSum[orderint][6] = SIUtil.add(orderSum[orderint][6],order.getTotalOfDeliveryFee());
          orderSum[orderint][7] = SIUtil.add(orderSum[orderint][7],order.getTotalOfFee());
          orderSum[orderint][8] = SIUtil.add(orderSum[orderint][8],order.getSumOfDiscount());
          orderSum[orderint][9] = SIUtil.add(orderSum[orderint][9],order.getSumByPoint());
          //支店別合計
          if(SIUtil.isNotNull(order.getBranchCode())){//売上のみ合計
            int count = Integer.parseInt(order.getBranchCode());
            brachSum[count][6] = SIUtil.add(brachSum[count][6],order.getTotalOfDeliveryFee());
            brachSum[count][7] = SIUtil.add(brachSum[count][7],order.getTotalOfFee());
            brachSum[count][8] = SIUtil.add(brachSum[count][8],order.getSumOfDiscount());
            brachSum[count][9] = SIUtil.add(brachSum[count][9],order.getSumByPoint());
          }
          //担当者別合計
          if(SIUtil.isNotNull(order.getChargeCode())){//売上のみ合計
            int count = Integer.parseInt(order.getChargeCode().trim().substring(0,3));
            int count2 = Integer.parseInt(order.getChargeCode().trim().substring(3,5));
            chargeSum[count][count2][6] = SIUtil.add(chargeSum[count][count2][6],order.getTotalOfDeliveryFee());
            chargeSum[count][count2][7] = SIUtil.add(chargeSum[count][count2][7],order.getTotalOfFee());
            chargeSum[count][count2][8] = SIUtil.add(chargeSum[count][count2][8],order.getSumOfDiscount());
            chargeSum[count][count2][9] = SIUtil.add(chargeSum[count][count2][9],order.getSumByPoint());
          }
          //合計
          totalSum[6] = SIUtil.add(totalSum[6],order.getTotalOfDeliveryFee());
          totalSum[7] = SIUtil.add(totalSum[7],order.getTotalOfFee());
          totalSum[8] = SIUtil.add(totalSum[8],order.getSumOfDiscount());
          totalSum[9] = SIUtil.add(totalSum[9],order.getSumByPoint());
          oldOrderCode = order.getOrderCode();
          oldStatus = order.getStatus();
        }
        orderSum[orderint][0] = SIUtil.add(orderSum[orderint][0],order.getSalesPriceInTax());
        orderSum[orderint][1] = SIUtil.add(orderSum[orderint][1],order.getSalesPrice());
        orderSum[orderint][2] = SIUtil.add(orderSum[orderint][2],order.getCmdtyPriceInTax());
        orderSum[orderint][3] = SIUtil.add(orderSum[orderint][3],order.getCmdtyPrice());
        orderSum[orderint][4] = SIUtil.add(orderSum[orderint][4],order.getProfitPrice());
        //支店別合計
        if(SIUtil.isNotNull(order.getBranchCode())){//売上のみ合計
          int count = Integer.parseInt(order.getBranchCode());
          brachSum[count][0] = SIUtil.add(brachSum[count][0],order.getSalesPriceInTax());
          brachSum[count][1] = SIUtil.add(brachSum[count][1],order.getSalesPrice());
          brachSum[count][2] = SIUtil.add(brachSum[count][2],order.getCmdtyPriceInTax());
          brachSum[count][3] = SIUtil.add(brachSum[count][3],order.getCmdtyPrice());
          brachSum[count][4] = SIUtil.add(brachSum[count][4],order.getProfitPrice());
        }
        //担当者別合計
        if(SIUtil.isNotNull(order.getChargeCode())){//売上のみ合計
          int count = Integer.parseInt(order.getChargeCode().trim().substring(0,3));
          int count2 = Integer.parseInt(order.getChargeCode().trim().substring(3,5));
          chargeSum[count][count2][0] = SIUtil.add(chargeSum[count][count2][0],order.getSalesPriceInTax());
          chargeSum[count][count2][1] = SIUtil.add(chargeSum[count][count2][1],order.getSalesPrice());
          chargeSum[count][count2][2] = SIUtil.add(chargeSum[count][count2][2],order.getCmdtyPriceInTax());
          chargeSum[count][count2][3] = SIUtil.add(chargeSum[count][count2][3],order.getCmdtyPrice());
          chargeSum[count][count2][4] = SIUtil.add(chargeSum[count][count2][4],order.getProfitPrice());
        }
        //合計
        totalSum[0] = SIUtil.add(totalSum[0],order.getSalesPriceInTax());
        totalSum[1] = SIUtil.add(totalSum[1],order.getSalesPrice());
        totalSum[2] = SIUtil.add(totalSum[2],order.getCmdtyPriceInTax());
        totalSum[3] = SIUtil.add(totalSum[3],order.getCmdtyPrice());
        totalSum[4] = SIUtil.add(totalSum[4],order.getProfitPrice());
      }
      lWrite.removeDetail(4,true);
    }
    //フッター部【取引区分別小計】
    lineNo = 0;
    log.debug("受注一覧表：フッター部生成");
    if(SIUtil.isNotNull(ioExcelList.getOrderStatusRdo())){
      int j = Integer.parseInt(ioExcelList.getOrderStatusRdo());
      lWrite.makeFooterLine(1,lineNo);
      lWrite.writeFooterItem(0,lineNo,0,true);//【取引区分別小計】
      lWrite.writeFooterItem(8,SIConfig.SALESORDERSTATUS[j],SIExcelConf.FIELD_STRING_TYPE);//取引区分
      for(int i=0;i<ENDLIST;i++){
        if(i==5){//粗利益率計算
          orderSum[j][5] = "0";//粗利益率
          if(SIUtil.isNotNull(orderSum[j][1])&&!orderSum[j][1].equals("0")&&SIUtil.isNotNull(orderSum[j][4])&&!orderSum[j][4].equals("0")){
            try{
              orderSum[j][5] = String.valueOf((SIUtil.div_FL(orderSum[j][4],orderSum[j][1])*100));
            }catch(SIFatalException e1){
              e1.printStackTrace();
            }
          }
        }
        lWrite.writeFooterItem(i+13,orderSum[j][i],SIExcelConf.FIELD_NUMERIC_TYPE);//取引区分別小計
      }
    }else{
      for(int j=0;j<2;j++){
        lWrite.makeFooterLine(1,lineNo);
        lWrite.writeFooterItem(0,lineNo,0,true);//【取引区分別小計】
        lWrite.writeFooterItem(8,SIConfig.SALESORDERSTATUS[j],SIExcelConf.FIELD_STRING_TYPE);//取引区分
        for(int i=0;i<ENDLIST;i++){
          if(i==5){//粗利益率計算
            orderSum[j][5] = "0";//粗利益率
            if(SIUtil.isNotNull(orderSum[j][1])&&!orderSum[j][1].equals("0")&&SIUtil.isNotNull(orderSum[j][4])&&!orderSum[j][4].equals("0")){
              try{
                orderSum[j][5] = String.valueOf((SIUtil.div_FL(orderSum[j][4],orderSum[j][1])*100));
              }catch(SIFatalException e1){
                e1.printStackTrace();
              }
            }
          }
          lWrite.writeFooterItem(i+13,orderSum[j][i],SIExcelConf.FIELD_NUMERIC_TYPE);//取引区分別小計
        }
        lineNo = lineNo + 1;
      }
    }
    //フッター部【支店別小計】
    lineNo = 0;
    if(SIUtil.isNotNull(ioExcelList.getBranchCode())){
      lWrite.makeFooterLine(2,lineNo);
      lWrite.writeFooterItem(0,lineNo,0,true);
      lWrite.writeFooterItem(9,SIUtil.getNameFromColl(brachColl,ioExcelList.getBranchCode()),SIExcelConf.FIELD_STRING_TYPE);
      int count = Integer.parseInt(ioExcelList.getBranchCode().trim());
      for(int i=0;i<ENDLIST;i++){
        if(i==5){//粗利益率計算
          brachSum[count][5] = "0";//粗利益率
          if(SIUtil.isNotNull(brachSum[count][1])&&!brachSum[count][1].equals("0")&&SIUtil.isNotNull(brachSum[count][4])&&!brachSum[count][4].equals("0")){
            try{
              brachSum[count][5] = String.valueOf((SIUtil.div_FL(brachSum[count][4],brachSum[count][1])*100));
            }catch(SIFatalException e1){
              e1.printStackTrace();
            }
          }
        }
        lWrite.writeFooterItem(i+13,brachSum[count][i] ,SIExcelConf.FIELD_NUMERIC_TYPE);//合計
      }
    }else{
      brachit =brachColl.iterator();
      while(brachit.hasNext()){
        lWrite.makeFooterLine(2,lineNo);
        lWrite.writeFooterItem(0,lineNo,0,true);
        nameValue = (SINameValue) brachit.next();
        int count = Integer.parseInt(nameValue.getValue().trim());
        lWrite.writeFooterItem(9,nameValue.getName(),SIExcelConf.FIELD_STRING_TYPE);
        for(int i=0;i<ENDLIST;i++){
          if(i==5){//粗利益率計算
            brachSum[count][5] = "0";//粗利益率
            if(SIUtil.isNotNull(brachSum[count][1])&&!brachSum[count][1].equals("0")&&SIUtil.isNotNull(brachSum[count][4])&&!brachSum[count][4].equals("0")){
              try{
                brachSum[count][5] = String.valueOf((SIUtil.div_FL(brachSum[count][4],brachSum[count][1])*100));
              }catch(SIFatalException e1){
                e1.printStackTrace();
              }
            }
          }
          lWrite.writeFooterItem(i+13,brachSum[count][i] ,SIExcelConf.FIELD_NUMERIC_TYPE);//合計
        }
        lineNo = lineNo + 1;
      }
    }
    //フッター部【担当者別小計】
    lineNo = 0;
    if(SIUtil.isNotNull(ioExcelList.getChargeNameCdo())){
      lWrite.makeFooterLine(3,lineNo);
      lWrite.writeFooterItem(0,lineNo,0,true);//【合計】
      int count = Integer.parseInt(ioExcelList.getChargeNameCdo().trim().substring(0,3));
      int count2 = Integer.parseInt(ioExcelList.getChargeNameCdo().trim().substring(3,5));
      lWrite.writeFooterItem(10,SIUtil.getNameFromColl(chargeColl,ioExcelList.getChargeNameCdo()),SIExcelConf.FIELD_STRING_TYPE);
      for(int i=0;i<ENDLIST;i++){
        if(i==5){//粗利益率計算
          chargeSum[count][count2][5] = "0";//粗利益率
          if(SIUtil.isNotNull(chargeSum[count][count2][1])&&!chargeSum[count][count2][1].equals("0")&&SIUtil.isNotNull(chargeSum[count][count2][4])&&!chargeSum[count][count2][4].equals("0")){
            try{
              chargeSum[count][count2][5] = String.valueOf((SIUtil.div_FL(chargeSum[count][count2][4],chargeSum[count][count2][1])*100));
            }catch(SIFatalException e1){
              e1.printStackTrace();
            }
          }
        }
        lWrite.writeFooterItem(i+13,chargeSum[count][count2][i],SIExcelConf.FIELD_NUMERIC_TYPE);//合計
      }
    }else{
      chargeit =chargeColl.iterator();
      while(chargeit.hasNext()){
        lWrite.makeFooterLine(3,lineNo);
        lWrite.writeFooterItem(0,lineNo,0,true);//【合計】
        nameValue = (SINameValue) chargeit.next();
        int count = Integer.parseInt(nameValue.getValue().trim().substring(0,3));
        int count2 = Integer.parseInt(nameValue.getValue().trim().substring(3,5));
        lWrite.writeFooterItem(10,nameValue.getName(),SIExcelConf.FIELD_STRING_TYPE);
        for(int i=0;i<ENDLIST;i++){
          if(i==5){//粗利益率計算
            chargeSum[count][count2][5] = "0";//粗利益率
            if(SIUtil.isNotNull(chargeSum[count][count2][1])&&!chargeSum[count][count2][1].equals("0")&&SIUtil.isNotNull(chargeSum[count][count2][4])&&!chargeSum[count][count2][4].equals("0")){
              try{
                chargeSum[count][count2][5] = String.valueOf((SIUtil.div_FL(chargeSum[count][count2][4],chargeSum[count][count2][1])*100));
              }catch(SIFatalException e1){
                e1.printStackTrace();
              }
            }
          }
          lWrite.writeFooterItem(i+13,chargeSum[count][count2][i],SIExcelConf.FIELD_NUMERIC_TYPE);//合計
        }
        lineNo = lineNo + 1;
      }
    }
    //フッター部【合計】
    lineNo = 0;
    lWrite.makeFooterLine(4,lineNo);
    lWrite.writeFooterItem(0,lineNo,0,true);//【合計】
    for(int i=0;i<ENDLIST;i++){
      if(i==5){//粗利益率計算
        totalSum[5] = "0";//粗利益率
        if(SIUtil.isNotNull(totalSum[1])&&!totalSum[1].equals("0")&&SIUtil.isNotNull(totalSum[4])&&!totalSum[4].equals("0")){
          try{
            totalSum[5] = String.valueOf((SIUtil.div_FL(totalSum[4],totalSum[1])*100));
          }catch(SIFatalException e1){
            e1.printStackTrace();
          }
        }
      }
      lWrite.writeFooterItem(i+13,totalSum[i] ,SIExcelConf.FIELD_NUMERIC_TYPE);//合計
    }
    lWrite.makePage();
    try{
      lWrite.close(response);
    }catch(IOException e){
      //e.printStackTrace();
    }
  }
  
  /**
   * <b>printInventory</b>
   * 棚卸差異表を出力する
   * @param request リクエスト
   * @param response
   * @param lConnection DBへのコネクション
   * @param lItemList  データリスト
   * @return なし
   * @throws なし
   */
  private void printInventory(HttpServletResponse response,Connection lConnection,UIIOExcelListCond ioExcelList){
    SIExcelRWrite lWrite = null;
    Iterator it = null;
    UIInventory lBasic=new UIInventory();
    String StoreRecordAmount = "0";
    String InventoryStockAmount = "0";
    String SumdifferenceAmount = "0";
    String StoreRecordPrice = "0";
    String InventoryStockPrice = "0";
    String differencePrice = "0";
    if(SIUtil.isNotNull(ioExcelList.getCtgryCode())||SIUtil.isNotNull(ioExcelList.getIndividualCode())){
      String categoryCode = "";
      if(SIUtil.isNotNull(ioExcelList.getCtgryCode())&&SIUtil.isNull(ioExcelList.getIndividualCode())){
        if(ioExcelList.getCtgryCode().equals(SIConfig.OTHERCATEGORYNAME)){
          categoryCode = SIConfig.OTHERCATEGORYCODE;
        }else{
          for(int i = 0; i<SIConfig.CATEGORY.length ;i++){
            if(ioExcelList.getCtgryCode().equalsIgnoreCase(SIConfig.CATEGORY[i][0])){
              categoryCode = SIConfig.CATEGORY[i][1];
              break;
            }
          }
        }
      }else if(SIUtil.isNull(ioExcelList.getCtgryCode())&&SIUtil.isNotNull(ioExcelList.getIndividualCode())){
        categoryCode = ioExcelList.getIndividualCode().substring(0,1);
        for(int i = 0; i<SIConfig.CATEGORY.length ;i++){
          if(categoryCode.equalsIgnoreCase(SIConfig.CATEGORY[i][1])){
            ioExcelList.setCtgryCode(SIConfig.CATEGORY[i][0]);
            break;
          }
        }
      }else if(SIUtil.isNotNull(ioExcelList.getCtgryCode())&&SIUtil.isNotNull(ioExcelList.getIndividualCode())){
        for(int i = 0; i<SIConfig.CATEGORY.length ;i++){
          if(ioExcelList.getCtgryCode().equalsIgnoreCase(SIConfig.CATEGORY[i][0])){
            categoryCode = SIConfig.CATEGORY[i][1];
            break;
          }
        }
        if(!categoryCode.equals(ioExcelList.getIndividualCode().substring(0,1)))return;
      }
      
      String template = (String) this.getServletContext().getRealPath("/docs/inventorystock2.xls");
      lWrite = new SIExcelRWrite(SIExcelConf.SIEXCEL_INVENTORYSTOCK_INX,template); 
      lWrite.makeSheet(0,ioExcelList.getCtgryCode(),1);
      Collection inventoryColl = ioExcelList.getInventoryCollection(lConnection,categoryCode);
      if(inventoryColl==null||inventoryColl.size()==0)return;
      it = inventoryColl.iterator();
      int lineNo = 0;
      
      //ヘッダー部
      lWrite.writeHeaderItem(1,1,ioExcelList.getHeaderSeasonCbo(),SIExcelConf.FIELD_STRING_TYPE);//対象年月
      lWrite.writeHeaderItem(2,1,ioExcelList.getHeaderBranchCode(lConnection),SIExcelConf.FIELD_STRING_TYPE);//支店
      lWrite.writeHeaderItem(3,1,ioExcelList.getHeaderInventoryFlg(),SIExcelConf.FIELD_STRING_TYPE);//出力対象
      lWrite.writeHeaderItem(4,1,ioExcelList.getHeaderCtgryCode(lConnection),SIExcelConf.FIELD_STRING_TYPE);//ｶﾃｺﾞﾘ
      lWrite.writeHeaderItem(5,1,ioExcelList.getHeaderInstockFlg(),SIExcelConf.FIELD_STRING_TYPE);//在庫管理
      lWrite.writeHeaderItem(6,1,ioExcelList.getHeaderIndividualCode(),SIExcelConf.FIELD_STRING_TYPE);//在庫コード
      //明細を作成する
      while(it.hasNext()){
        lBasic = (UIInventory)it.next();
        lWrite.makeLine(lineNo);
        lWrite.writeDetailItem(0,lBasic.getBranchName(),SIExcelConf.FIELD_STRING_TYPE);//支店
        lWrite.writeDetailItem(1,lBasic.getIndividualCode(),SIExcelConf.FIELD_STRING_TYPE);//在庫ｺｰﾄﾞ
        String cmdtyName = "";
        try{
          cmdtyName =SIBGUtil.getCmdtyFullName(lConnection,lBasic.getCmdtyCode(),lBasic.getIndividualCode());
        }catch(SIDBAccessException e){
          cmdtyName ="";
          e.printStackTrace();
        }
        lWrite.writeDetailItem(2,cmdtyName,SIExcelConf.FIELD_STRING_TYPE);//商品名
        lWrite.writeDetailItem(3,lBasic.getStoreRecordAmount(),SIExcelConf.FIELD_NUMERIC_TYPE);//ｼｽﾃﾑ在庫総数
        StoreRecordAmount = SIUtil.add(StoreRecordAmount,lBasic.getStoreRecordAmount());
        lWrite.writeDetailItem(4,lBasic.getInventoryStockAmount(),SIExcelConf.FIELD_NUMERIC_TYPE);//実地棚卸数
        InventoryStockAmount = SIUtil.add(InventoryStockAmount,lBasic.getInventoryStockAmount());
        lWrite.writeDetailItem(5,lBasic.getInventoryDifference(),SIExcelConf.FIELD_NUMERIC_TYPE);//差異数
        SumdifferenceAmount=SIUtil.add(SumdifferenceAmount,lBasic.getInventoryDifference());
        lWrite.writeDetailItem(6,lBasic.getCmdtyPrice(),SIExcelConf.FIELD_NUMERIC_TYPE);//在庫単価
        lWrite.writeDetailItem(7,SIUtil.multi(lBasic.getStoreRecordAmount(),lBasic.getCmdtyPrice()),SIExcelConf.FIELD_NUMERIC_TYPE);//ｼｽﾃﾑ在庫金額
        StoreRecordPrice=SIUtil.add(StoreRecordPrice,SIUtil.multi(lBasic.getStoreRecordAmount(),lBasic.getCmdtyPrice()));
        lWrite.writeDetailItem(8,SIUtil.multi(lBasic.getInventoryStockAmount(),lBasic.getCmdtyPrice()),SIExcelConf.FIELD_NUMERIC_TYPE);//実地棚卸金額
        InventoryStockPrice=SIUtil.add(InventoryStockPrice,SIUtil.multi(lBasic.getInventoryStockAmount(),lBasic.getCmdtyPrice()));
        lWrite.writeDetailItem(9,SIUtil.multi(lBasic.getInventoryDifference(),lBasic.getCmdtyPrice()),SIExcelConf.FIELD_NUMERIC_TYPE);//調整額
        differencePrice=SIUtil.add(differencePrice,SIUtil.multi(lBasic.getInventoryDifference(),lBasic.getCmdtyPrice()));
        lineNo = lineNo + 1;
      }
      lineNo = 0;
      lWrite.makeFooterLine(1,lineNo);
      lWrite.writeFooterItem(0,lineNo,0,true);
      lWrite.writeFooterItem(3,StoreRecordAmount,SIExcelConf.FIELD_NUMERIC_TYPE);//ｼｽﾃﾑ在庫総数
      lWrite.writeFooterItem(4,InventoryStockAmount,SIExcelConf.FIELD_NUMERIC_TYPE);//ｼｽﾃﾑ在庫総数
      lWrite.writeFooterItem(5,SumdifferenceAmount,SIExcelConf.FIELD_NUMERIC_TYPE);//差異数
      lWrite.writeFooterItem(7,StoreRecordPrice,SIExcelConf.FIELD_NUMERIC_TYPE);//ｼｽﾃﾑ在庫金額
      lWrite.writeFooterItem(8,InventoryStockPrice,SIExcelConf.FIELD_NUMERIC_TYPE);//実地棚卸金額
      lWrite.writeFooterItem(9,differencePrice,SIExcelConf.FIELD_NUMERIC_TYPE);//調整額
      lWrite.makePage();
    }else{//全カテゴリ出力
      boolean dataCount = false;//データが一件もない場合、ファイル出力しない
      String template = (String) this.getServletContext().getRealPath("/docs/inventorystock3.xls");
      lWrite = new SIExcelRWrite(SIExcelConf.SIEXCEL_INVENTORYSTOCK_INX,template);
      for(int i = 0; i<SIConfig.CATEGORY.length ;i++){
        lWrite.makeSheet(i,SIConfig.CATEGORY[i][0],1);
        Collection inventoryColl = ioExcelList.getInventoryCollection(lConnection,SIConfig.CATEGORY[i][1]);
        if(inventoryColl!=null&&inventoryColl.size()!=0)dataCount = true;//データが存在する
        it = inventoryColl.iterator();
        StoreRecordAmount = "0";
        InventoryStockAmount = "0";
        SumdifferenceAmount = "0";
        StoreRecordPrice = "0";
        InventoryStockPrice = "0";
        differencePrice = "0";
        int lineNo = 0;
        //ヘッダー部
        lWrite.writeHeaderItem(1,1,ioExcelList.getHeaderSeasonCbo(),SIExcelConf.FIELD_STRING_TYPE);//対象年月
        lWrite.writeHeaderItem(2,1,ioExcelList.getHeaderBranchCode(lConnection),SIExcelConf.FIELD_STRING_TYPE);//支店
        lWrite.writeHeaderItem(3,1,ioExcelList.getHeaderInventoryFlg(),SIExcelConf.FIELD_STRING_TYPE);//出力対象
        lWrite.writeHeaderItem(4,1,ioExcelList.getHeaderCtgryCode(lConnection),SIExcelConf.FIELD_STRING_TYPE);//ｶﾃｺﾞﾘ
        lWrite.writeHeaderItem(5,1,ioExcelList.getHeaderInstockFlg(),SIExcelConf.FIELD_STRING_TYPE);//在庫管理
        lWrite.writeHeaderItem(6,1,ioExcelList.getHeaderIndividualCode(),SIExcelConf.FIELD_STRING_TYPE);//在庫コード
        //明細を作成する
        while(it.hasNext()){
          lBasic = (UIInventory)it.next();
          lWrite.makeLine(lineNo);
          lWrite.writeDetailItem(0,lBasic.getBranchName(),SIExcelConf.FIELD_STRING_TYPE);//支店
          lWrite.writeDetailItem(1,lBasic.getIndividualCode(),SIExcelConf.FIELD_STRING_TYPE);//在庫ｺｰﾄﾞ
          String cmdtyName = "";
          try{
            cmdtyName =SIBGUtil.getCmdtyFullName(lConnection,lBasic.getCmdtyCode(),lBasic.getIndividualCode());
          }catch(SIDBAccessException e){
            cmdtyName ="";
            e.printStackTrace();
          }
          lWrite.writeDetailItem(2,cmdtyName,SIExcelConf.FIELD_STRING_TYPE);//商品名
          lWrite.writeDetailItem(3,lBasic.getStoreRecordAmount(),SIExcelConf.FIELD_NUMERIC_TYPE);//ｼｽﾃﾑ在庫総数
          StoreRecordAmount = SIUtil.add(StoreRecordAmount,lBasic.getStoreRecordAmount());
          lWrite.writeDetailItem(4,lBasic.getInventoryStockAmount(),SIExcelConf.FIELD_NUMERIC_TYPE);//実地棚卸数
          InventoryStockAmount = SIUtil.add(InventoryStockAmount,lBasic.getInventoryStockAmount());
          lWrite.writeDetailItem(5,lBasic.getInventoryDifference(),SIExcelConf.FIELD_NUMERIC_TYPE);//差異数
          SumdifferenceAmount=SIUtil.add(SumdifferenceAmount,lBasic.getInventoryDifference());
          lWrite.writeDetailItem(6,lBasic.getCmdtyPrice(),SIExcelConf.FIELD_NUMERIC_TYPE);//在庫単価
          lWrite.writeDetailItem(7,SIUtil.multi(lBasic.getStoreRecordAmount(),lBasic.getCmdtyPrice()),SIExcelConf.FIELD_NUMERIC_TYPE);//ｼｽﾃﾑ在庫金額
          StoreRecordPrice=SIUtil.add(StoreRecordPrice,SIUtil.multi(lBasic.getStoreRecordAmount(),lBasic.getCmdtyPrice()));
          lWrite.writeDetailItem(8,SIUtil.multi(lBasic.getInventoryStockAmount(),lBasic.getCmdtyPrice()),SIExcelConf.FIELD_NUMERIC_TYPE);//実地棚卸金額
          InventoryStockPrice=SIUtil.add(InventoryStockPrice,SIUtil.multi(lBasic.getInventoryStockAmount(),lBasic.getCmdtyPrice()));
          lWrite.writeDetailItem(9,SIUtil.multi(lBasic.getInventoryDifference(),lBasic.getCmdtyPrice()),SIExcelConf.FIELD_NUMERIC_TYPE);//調整額
          differencePrice=SIUtil.add(differencePrice,SIUtil.multi(lBasic.getInventoryDifference(),lBasic.getCmdtyPrice()));
          lineNo = lineNo + 1;
        }
        lineNo = 0;
        lWrite.makeFooterLine(1,lineNo);
        lWrite.writeFooterItem(0,lineNo,0,true);
        lWrite.writeFooterItem(3,StoreRecordAmount,SIExcelConf.FIELD_NUMERIC_TYPE);//ｼｽﾃﾑ在庫総数
        lWrite.writeFooterItem(4,InventoryStockAmount,SIExcelConf.FIELD_NUMERIC_TYPE);//ｼｽﾃﾑ在庫総数
        lWrite.writeFooterItem(5,SumdifferenceAmount,SIExcelConf.FIELD_NUMERIC_TYPE);//差異数
        lWrite.writeFooterItem(7,StoreRecordPrice,SIExcelConf.FIELD_NUMERIC_TYPE);//ｼｽﾃﾑ在庫金額
        lWrite.writeFooterItem(8,InventoryStockPrice,SIExcelConf.FIELD_NUMERIC_TYPE);//実地棚卸金額
        lWrite.writeFooterItem(9,differencePrice,SIExcelConf.FIELD_NUMERIC_TYPE);//調整額
        lWrite.makePage();
      }
      //その他の商品
      lWrite.makeSheet(12,SIConfig.OTHERCATEGORYCODE,1);
      Collection inventoryColl = ioExcelList.getInventoryCollection(lConnection,SIConfig.OTHERCATEGORYCODE);
      if(inventoryColl!=null&&inventoryColl.size()!=0) dataCount = true;//データが存在する
      it = inventoryColl.iterator();
      StoreRecordAmount = "0";
      InventoryStockAmount = "0";
      SumdifferenceAmount = "0";
      StoreRecordPrice = "0";
      InventoryStockPrice = "0";
      differencePrice = "0";
      int lineNo = 0;
      //ヘッダー部
      lWrite.writeHeaderItem(1,1,ioExcelList.getHeaderSeasonCbo(),SIExcelConf.FIELD_STRING_TYPE);//対象年月
      lWrite.writeHeaderItem(2,1,ioExcelList.getHeaderBranchCode(lConnection),SIExcelConf.FIELD_STRING_TYPE);//支店
      lWrite.writeHeaderItem(3,1,ioExcelList.getHeaderInventoryFlg(),SIExcelConf.FIELD_STRING_TYPE);//出力対象
      lWrite.writeHeaderItem(4,1,ioExcelList.getHeaderCtgryCode(lConnection),SIExcelConf.FIELD_STRING_TYPE);//ｶﾃｺﾞﾘ
      lWrite.writeHeaderItem(5,1,ioExcelList.getHeaderInstockFlg(),SIExcelConf.FIELD_STRING_TYPE);//在庫管理
      lWrite.writeHeaderItem(6,1,ioExcelList.getHeaderIndividualCode(),SIExcelConf.FIELD_STRING_TYPE);//在庫コード
      //明細を作成する
      while(it.hasNext()){
        lBasic = (UIInventory)it.next();
        lWrite.makeLine(lineNo);
        lWrite.writeDetailItem(0,lBasic.getBranchName(),SIExcelConf.FIELD_STRING_TYPE);//支店
        lWrite.writeDetailItem(1,lBasic.getIndividualCode(),SIExcelConf.FIELD_STRING_TYPE);//在庫ｺｰﾄﾞ
        String cmdtyName = "";
        try{
          cmdtyName =SIBGUtil.getCmdtyFullName(lConnection,lBasic.getCmdtyCode(),lBasic.getIndividualCode());
        }catch(SIDBAccessException e){
          cmdtyName ="";
          e.printStackTrace();
        }
        lWrite.writeDetailItem(2,cmdtyName,SIExcelConf.FIELD_STRING_TYPE);//商品名
        lWrite.writeDetailItem(3,lBasic.getStoreRecordAmount(),SIExcelConf.FIELD_NUMERIC_TYPE);//ｼｽﾃﾑ在庫総数
        StoreRecordAmount = SIUtil.add(StoreRecordAmount,lBasic.getStoreRecordAmount());
        lWrite.writeDetailItem(4,lBasic.getInventoryStockAmount(),SIExcelConf.FIELD_NUMERIC_TYPE);//実地棚卸数
        InventoryStockAmount = SIUtil.add(InventoryStockAmount,lBasic.getInventoryStockAmount());
        lWrite.writeDetailItem(5,lBasic.getInventoryDifference(),SIExcelConf.FIELD_NUMERIC_TYPE);//差異数
        SumdifferenceAmount=SIUtil.add(SumdifferenceAmount,lBasic.getInventoryDifference());
        lWrite.writeDetailItem(6,lBasic.getCmdtyPrice(),SIExcelConf.FIELD_NUMERIC_TYPE);//在庫単価
        lWrite.writeDetailItem(7,SIUtil.multi(lBasic.getStoreRecordAmount(),lBasic.getCmdtyPrice()),SIExcelConf.FIELD_NUMERIC_TYPE);//ｼｽﾃﾑ在庫金額
        StoreRecordPrice=SIUtil.add(StoreRecordPrice,SIUtil.multi(lBasic.getStoreRecordAmount(),lBasic.getCmdtyPrice()));
        lWrite.writeDetailItem(8,SIUtil.multi(lBasic.getInventoryStockAmount(),lBasic.getCmdtyPrice()),SIExcelConf.FIELD_NUMERIC_TYPE);//実地棚卸金額
        InventoryStockPrice=SIUtil.add(InventoryStockPrice,SIUtil.multi(lBasic.getInventoryStockAmount(),lBasic.getCmdtyPrice()));
        lWrite.writeDetailItem(9,SIUtil.multi(lBasic.getInventoryDifference(),lBasic.getCmdtyPrice()),SIExcelConf.FIELD_NUMERIC_TYPE);//調整額
        differencePrice=SIUtil.add(differencePrice,SIUtil.multi(lBasic.getInventoryDifference(),lBasic.getCmdtyPrice()));
        lineNo = lineNo + 1;
      }
      lineNo = 0;
      lWrite.makeFooterLine(1,lineNo);
      lWrite.writeFooterItem(0,lineNo,0,true);
      lWrite.writeFooterItem(3,StoreRecordAmount,SIExcelConf.FIELD_NUMERIC_TYPE);//ｼｽﾃﾑ在庫総数
      lWrite.writeFooterItem(4,InventoryStockAmount,SIExcelConf.FIELD_NUMERIC_TYPE);//実地棚卸数
      lWrite.writeFooterItem(5,SumdifferenceAmount,SIExcelConf.FIELD_NUMERIC_TYPE);//差異数
      lWrite.writeFooterItem(7,StoreRecordPrice,SIExcelConf.FIELD_NUMERIC_TYPE);//ｼｽﾃﾑ在庫金額
      lWrite.writeFooterItem(8,InventoryStockPrice,SIExcelConf.FIELD_NUMERIC_TYPE);//実地棚卸金額
      lWrite.writeFooterItem(9,differencePrice,SIExcelConf.FIELD_NUMERIC_TYPE);//調整額
      lWrite.makePage();
      if(!dataCount)return;
    }
    try{
      lWrite.close(response);
    }catch(IOException e){
      //e.printStackTrace();
    }
  }
  
  private void printIndividual(HttpServletResponse response,Connection lConnection,UIIOExcelListCond ioExcelList){
    String template = (String) this.getServletContext().getRealPath("/docs/individual.xls");
    SIExcelRWrite lWrite = new SIExcelRWrite(SIExcelConf.SIEXCEL_INDIVIDUAL_INX,template);
    Iterator it = null;
    UIInventory lBasic=new UIInventory();
    boolean decideFlg = ioExcelList.isDecidedInventory(lConnection);
    lWrite.makeSheet(0,SIExcelConf.getTitleName(SIExcelConf.SIEXCEL_INDIVIDUAL_INX),2);
    Collection individualColl = ioExcelList.getIndividualCollection(lConnection,decideFlg);
    it = individualColl.iterator();
    int lineNo = 0;
    if(individualColl==null||individualColl.size()==0)return;
    String oldCategory = new String();
    
    //カテゴリ関連変数
    String categorySum[][] = new String[SIConfig.CATEGORYORDER.length+1][5];
    for(int j=0;j<SIConfig.CATEGORYORDER.length+1;j++){
      for(int i=0;i<5;i++){
        categorySum[j][i] = new String("0");
      }
    }
    //合計
    String totalSum[] = new String[5];
    for(int i=0;i<5;i++){
      totalSum[i]= new String("0");
    }
    int ctgryCount = 0;
    //ヘッダー部
    lWrite.writeHeaderItem(1,1,ioExcelList.getHeaderSeasonCbo(),SIExcelConf.FIELD_STRING_TYPE);//対象年月
    lWrite.writeHeaderItem(2,1,ioExcelList.getHeaderIndividualFlg(),SIExcelConf.FIELD_STRING_TYPE);//出力対象
    lWrite.writeHeaderItem(3,1,ioExcelList.getHeaderInstockFlg(),SIExcelConf.FIELD_STRING_TYPE);//在庫管理
    lWrite.writeHeaderItem(4,1,ioExcelList.getHeaderDetailFlgRdo(),SIExcelConf.FIELD_STRING_TYPE);//明細
    lWrite.writeHeaderItem(5,1,ioExcelList.getHeaderCtgryCode(lConnection),SIExcelConf.FIELD_STRING_TYPE);//ｶﾃｺﾞﾘ
    lWrite.writeHeaderItem(6,1,ioExcelList.getHeaderBranchCode(lConnection),SIExcelConf.FIELD_STRING_TYPE);//支店
    lWrite.writeHeaderItem(7,1,ioExcelList.getHeaderIndividualCode(),SIExcelConf.FIELD_STRING_TYPE);//在庫ｺｰﾄﾞ
    if(decideFlg){
      lWrite.writeHeaderItem(3,5,"締処理済",SIExcelConf.FIELD_STRING_TYPE);
    }else{
      lWrite.writeHeaderItem(3,5,"締処理未済",SIExcelConf.FIELD_STRING_TYPE);
    }
    if(ioExcelList.isDetailFlg()){//明細あり
      //明細を作成する
      while(it.hasNext()){
        lBasic = (UIInventory)it.next();
        lWrite.makeLine(lineNo);
        if(!oldCategory.equals(lBasic.getCategoryCode())){
          ctgryCount = SIConfig.CATEGORYORDER.length;//カテゴリ存在しない商品
          for(int i = 0;i<SIConfig.CATEGORYORDER.length+1;i++){
            if(SIConfig.CATEGORYORDER[i][1].equals(lBasic.getCategoryCode())){
              ctgryCount = i;
              break;
            }
          }
          oldCategory = lBasic.getCategoryCode();
        }
        lWrite.writeDetailItem(0,lBasic.getIndividualCode(),SIExcelConf.FIELD_STRING_TYPE);//在庫ｺｰﾄﾞ
        String cmdtyName = "";
        try{
          cmdtyName =SIBGUtil.getCmdtyFullName(lConnection,lBasic.getCmdtyCode(),lBasic.getIndividualCode());
        }catch(SIDBAccessException e){
          cmdtyName ="";
          e.printStackTrace();
        }
        lWrite.writeDetailItem(1,cmdtyName,SIExcelConf.FIELD_STRING_TYPE);//商品名
        lWrite.writeDetailItem(2,lBasic.getStoreRecordAmount(),SIExcelConf.FIELD_NUMERIC_TYPE);//繰越在庫数
        categorySum[ctgryCount][0] = SIUtil.add(categorySum[ctgryCount][0],lBasic.getStoreRecordAmount());
        lWrite.writeDetailItem(3,lBasic.getStoreAmount(),SIExcelConf.FIELD_NUMERIC_TYPE);//入庫数
        categorySum[ctgryCount][1] = SIUtil.add(categorySum[ctgryCount][1],lBasic.getStoreAmount());
        lWrite.writeDetailItem(4,lBasic.getShipAmount(),SIExcelConf.FIELD_NUMERIC_TYPE);//出庫数
        categorySum[ctgryCount][2] = SIUtil.add(categorySum[ctgryCount][2],lBasic.getShipAmount());
        lWrite.writeDetailItem(5,lBasic.getSumAmount(),SIExcelConf.FIELD_NUMERIC_TYPE);//在庫総数
        categorySum[ctgryCount][3] = SIUtil.add(categorySum[ctgryCount][3],lBasic.getSumAmount());
        lWrite.writeDetailItem(6,lBasic.getCmdtyPrice(),SIExcelConf.FIELD_NUMERIC_TYPE);//在庫単価
        lWrite.writeDetailItem(7,lBasic.getSumPrice(),SIExcelConf.FIELD_NUMERIC_TYPE);//在庫金額
        categorySum[ctgryCount][4] = SIUtil.add(categorySum[ctgryCount][4],lBasic.getSumPrice());
        
        totalSum[0] = SIUtil.add(totalSum[0],lBasic.getStoreRecordAmount());
        totalSum[1] = SIUtil.add(totalSum[1],lBasic.getStoreAmount());
        totalSum[2] = SIUtil.add(totalSum[2],lBasic.getShipAmount());
        totalSum[3] = SIUtil.add(totalSum[3],lBasic.getSumAmount());
        totalSum[4] = SIUtil.add(totalSum[4],lBasic.getSumPrice());
        lineNo = lineNo + 1;
      }
      lWrite.removeDetail(2);
    }else{//明細なし
      while(it.hasNext()){
        lBasic = (UIInventory)it.next();
        ctgryCount = SIConfig.CATEGORYORDER.length;//カテゴリ存在しない商品
        for(int i = 0;i<SIConfig.CATEGORYORDER.length+1;i++){
          if(SIConfig.CATEGORYORDER[i][1].equals(lBasic.getCategoryCode())){
            ctgryCount = i;
            break;
          }
        }
        categorySum[ctgryCount][0] = SIUtil.add(categorySum[ctgryCount][0],lBasic.getStoreRecordAmount());
        categorySum[ctgryCount][1] = SIUtil.add(categorySum[ctgryCount][1],lBasic.getStoreAmount());
        categorySum[ctgryCount][2] = SIUtil.add(categorySum[ctgryCount][2],lBasic.getShipAmount());
        categorySum[ctgryCount][3] = SIUtil.add(categorySum[ctgryCount][3],lBasic.getSumAmount());
        categorySum[ctgryCount][4] = SIUtil.add(categorySum[ctgryCount][4],lBasic.getSumPrice());
        totalSum[0] = SIUtil.add(totalSum[0],lBasic.getStoreRecordAmount());
        totalSum[1] = SIUtil.add(totalSum[1],lBasic.getStoreAmount());
        totalSum[2] = SIUtil.add(totalSum[2],lBasic.getShipAmount());
        totalSum[3] = SIUtil.add(totalSum[3],lBasic.getSumAmount());
        totalSum[4] = SIUtil.add(totalSum[4],lBasic.getSumPrice());
        lineNo = lineNo + 1;
      }
      lWrite.removeDetail(2,true);
    }
    lineNo=0;
    String ctgryName="";
    //フッター部【カテゴリ別小計】
    if(SIUtil.isNotNull(ioExcelList.getCtgryCode())){
      ctgryCount = SIConfig.CATEGORYORDER.length;//カテゴリ存在しない商品
      try{
        ctgryName = SIBGUtil.getCtgryName(lConnection,ioExcelList.getCtgryCode());
      }catch(SIDBAccessException e1){
        e1.printStackTrace();
      }
      for(int i = 0;i<SIConfig.CATEGORYORDER.length+1;i++){
        if(SIConfig.CATEGORY[i][0].equals(ioExcelList.getCtgryCode())){
          for(int j = 0;j < SIConfig.CATEGORYORDER.length+1;j++){
            if(SIConfig.CATEGORYORDER[j][1].equals(SIConfig.CATEGORY[i][1])){
              ctgryCount = j;
              break;
            }
          }
          break;
        }
      }
      lWrite.makeFooterLine(1,lineNo);
      lWrite.writeFooterItem(0,lineNo,0,false);
      lWrite.writeFooterItem(1,ctgryName,SIExcelConf.FIELD_STRING_TYPE);//カテゴリ名
      lWrite.writeFooterItem(2,categorySum[ctgryCount][0],SIExcelConf.FIELD_NUMERIC_TYPE);//繰越在庫
      lWrite.writeFooterItem(3,categorySum[ctgryCount][1],SIExcelConf.FIELD_NUMERIC_TYPE);//入庫数 
      lWrite.writeFooterItem(4,categorySum[ctgryCount][2],SIExcelConf.FIELD_NUMERIC_TYPE);//出庫数 
      lWrite.writeFooterItem(5,categorySum[ctgryCount][3],SIExcelConf.FIELD_NUMERIC_TYPE);//在庫総数
      lWrite.writeFooterItem(7,categorySum[ctgryCount][4],SIExcelConf.FIELD_NUMERIC_TYPE);//在庫金額
    }else{
      for(int j=0;j<SIConfig.CATEGORYORDER.length+1;j++){
        if(j==SIConfig.CATEGORYORDER.length){
          ctgryName = SIConfig.OTHERCATEGORYNAME;
        }else{
          try{
            ctgryName = SIBGUtil.getCtgryName(lConnection,SIConfig.CATEGORY[j][0]);
          }catch(SIDBAccessException e1){
            e1.printStackTrace();
          }
        }
        lWrite.makeFooterLine(1,lineNo);
        lWrite.writeFooterItem(0,lineNo,0,false);
        lWrite.writeFooterItem(1,ctgryName,SIExcelConf.FIELD_STRING_TYPE);//カテゴリ名
        lWrite.writeFooterItem(2,categorySum[j][0],SIExcelConf.FIELD_NUMERIC_TYPE);//繰越在庫
        lWrite.writeFooterItem(3,categorySum[j][1],SIExcelConf.FIELD_NUMERIC_TYPE);//入庫数
        lWrite.writeFooterItem(4,categorySum[j][2],SIExcelConf.FIELD_NUMERIC_TYPE);//出庫数
        lWrite.writeFooterItem(5,categorySum[j][3],SIExcelConf.FIELD_NUMERIC_TYPE);//在庫総数
        lWrite.writeFooterItem(7,categorySum[j][4],SIExcelConf.FIELD_NUMERIC_TYPE);//在庫金額
        lineNo = lineNo + 1;
      }
    }
    
    //フッター部【合計】
    lineNo = 0;
    lWrite.makeFooterLine(2,lineNo);
    lWrite.writeFooterItem(0,lineNo,0,false);
    lWrite.writeFooterItem(2,totalSum[0],SIExcelConf.FIELD_NUMERIC_TYPE);
    lWrite.writeFooterItem(3,totalSum[1],SIExcelConf.FIELD_NUMERIC_TYPE);//入庫数
    lWrite.writeFooterItem(4,totalSum[2],SIExcelConf.FIELD_NUMERIC_TYPE);//出庫数
    lWrite.writeFooterItem(5,totalSum[3],SIExcelConf.FIELD_NUMERIC_TYPE);//在庫総数
    lWrite.writeFooterItem(7,totalSum[4],SIExcelConf.FIELD_NUMERIC_TYPE);//在庫金額
    lWrite.makePage();
    try{
      lWrite.close(response);
    }catch(IOException e) {
      //e.printStackTrace();
    }
  }
  
  /**
   * <b>printInsentive</b>
   * インセンティブ対象売上一覧表を出力する
   * @param request リクエスト
   * @param response
   * @param lConnection DBへのコネクション
   * @param lItemList  データリスト
   * @return なし
   * @throws なし
   */
  private void printInsentive(HttpServletResponse response,Connection lConnection,UIIOExcelListCond ioExcelList){
    String template = (String) this.getServletContext().getRealPath("/docs/insentive.xls");
    SIExcelRWrite lWrite = new SIExcelRWrite(SIExcelConf.SIEXCEL_INSENTIVE_INX,template); 
    Collection insentiveColl = ioExcelList.getIntensiveCollection(lConnection);
    Collection branchColl = ioExcelList.getBranchChargeNameCollection(lConnection);
    Collection chargeColl = ioExcelList.getChargeBranchNameCollection(lConnection);
    Collection branchNameColl = ioExcelList.getBranchNameCollection(lConnection);
    int brachCount = ioExcelList.getBranchCount(lConnection);
    int chargeCount = ioExcelList.getChargeCount(lConnection);
    Iterator it = insentiveColl.iterator();
    SIOrder order = null;
    int ENDLIST = 11;
    int lineNo = 0;
    //String oldOrderCode = "";
    //String oldStatus = "";
    int orderint = 0;
    String orderSum[][] = new String[3][ENDLIST];
    String totalSum[] = new String[ENDLIST]; 
    String brachSum[][] = new String[brachCount][ENDLIST];
    String chargeSum[][][] = new String[brachCount][chargeCount+1][ENDLIST];
    
    if(insentiveColl==null||insentiveColl.size()==0)return;
    
    //取引区分関連変数
    for(int j=0;j<3;j++){
      for(int i=0;i<ENDLIST;i++){
        orderSum[j][i] = new String("0");
      }
    }
    
    //支店関連変数
    Iterator branchIt =null;
    SINameValue nameValue = new SINameValue();
    if(SIUtil.isNotNull(ioExcelList.getBranchCode())){
      int count = Integer.parseInt(ioExcelList.getBranchCode().trim());
      for(int i=0;i<ENDLIST;i++){
        brachSum[count][i] = new String("0");
      }
    }else{
      branchIt =branchColl.iterator();
      while(branchIt.hasNext()){
        nameValue = (SINameValue) branchIt.next();
        int count = Integer.parseInt(nameValue.getValue().trim());
        for(int i=0;i<ENDLIST;i++){
          brachSum[count][i] = new String("0");
        }
      }
    }
    
    //担当者関連変数
    Iterator chargeit =null;
    if(SIUtil.isNotNull(ioExcelList.getChargeNameCdo())){
      int count = Integer.parseInt(ioExcelList.getChargeNameCdo().trim().substring(0,3));
      int count2 = Integer.parseInt(ioExcelList.getChargeNameCdo().trim().substring(3,5));
      for(int i=0;i<ENDLIST;i++){
        chargeSum[count][count2][i] = new String("0");
      }
    }else{
      chargeit =chargeColl.iterator();
      while(chargeit.hasNext()){
        nameValue = (SINameValue) chargeit.next();
        int count = Integer.parseInt(nameValue.getValue().trim().substring(0,3));
        int count2 = Integer.parseInt(nameValue.getValue().trim().substring(3,5));
        for(int i=0;i<ENDLIST;i++){
          chargeSum[count][count2][i] = new String("0");
        }
      }
    }
    
    //合計関連変数
    for(int i=0;i<ENDLIST;i++){
      totalSum[i] = new String("0");
    }
    
    lWrite.makeSheet(0,SIExcelConf.getTitleName(SIExcelConf.SIEXCEL_INSENTIVE_INX),4);
    //ヘッダー部
    lWrite.writeHeaderItem(1,1,ioExcelList.getSeasonFromToCho(),SIExcelConf.FIELD_STRING_TYPE);//受注期間
    lWrite.writeHeaderItem(2,1,ioExcelList.getShipmentDateFromToCho(),SIExcelConf.FIELD_STRING_TYPE);//出荷期間
    lWrite.writeHeaderItem(3,1,ioExcelList.getHeaderBranchCode(SIUtil.getNameFromColl(branchNameColl,ioExcelList.getBranchCode())),SIExcelConf.FIELD_STRING_TYPE);//担当支店
    lWrite.writeHeaderItem(3,3,ioExcelList.getHeaderIndividualCode(),SIExcelConf.FIELD_STRING_TYPE);//在庫ｺｰﾄﾞ
    lWrite.writeHeaderItem(4,1,ioExcelList.getHeaderChargeNameCdo(lConnection),SIExcelConf.FIELD_STRING_TYPE);//担当者
    lWrite.writeHeaderItem(4,3,ioExcelList.getHeaderOrderStatusRdo(),SIExcelConf.FIELD_STRING_TYPE);//取引区分
    lWrite.writeHeaderItem(5,1,ioExcelList.getHeaderDetailFlgRdo(),SIExcelConf.FIELD_STRING_TYPE);//明細
    lWrite.writeHeaderItem(5,3,ioExcelList.getHeaderUsedNewFlg(),SIExcelConf.FIELD_STRING_TYPE);//中古･新品区分
    lWrite.writeHeaderItem(6,1,ioExcelList.getHeaderCustCode(),SIExcelConf.FIELD_STRING_TYPE);//顧客ｺｰﾄﾞ
    lWrite.writeHeaderItem(6,3,ioExcelList.getHeaderOrderRoute(),SIExcelConf.FIELD_STRING_TYPE);//受注経路
    
    if(ioExcelList.isDetailFlg()){//明細あり
      //明細部
      while(it.hasNext()){
        order = (SIOrder)it.next();
        lWrite.makeLine(lineNo);
        orderint = Integer.parseInt(order.getOrderStatus());
        lWrite.writeDetailItem(0,order.getShippmentDate(),SIExcelConf.FIELD_DATE_TYPE);//出荷年月日
        if(order.getOrderStatus().equals("0")){//通常
          lWrite.writeDetailItem(1,order.getInitDateTime(),SIExcelConf.FIELD_DATE_TYPE);//受注年月日
        }else{//キャンセル・返品
          lWrite.writeDetailItem(1,order.getUpdateDateTime(),SIExcelConf.FIELD_DATE_TYPE);//受注年月日
        }
        lWrite.writeDetailItem(2,order.getOrderCode(),SIExcelConf.FIELD_STRING_TYPE);//受注番号
        lWrite.writeDetailItem(3,order.getCustCode(),SIExcelConf.FIELD_STRING_TYPE);//顧客ｺｰﾄﾞ
        lWrite.writeDetailItem(4,order.getCustName(),SIExcelConf.FIELD_STRING_TYPE);//顧客名
        lWrite.writeDetailItem(5,order.getIndividualCode(),SIExcelConf.FIELD_STRING_TYPE);//在庫コード
        lWrite.writeDetailItem(6,order.getCmdtyName(),SIExcelConf.FIELD_STRING_TYPE);//商品名
        lWrite.writeDetailItem(7,SIConfig.ORDERSTATUS[orderint],SIExcelConf.FIELD_STRING_TYPE);//取引区分
        lWrite.writeDetailItem(8,SIUtil.getNameFromColl(branchNameColl,order.getBranchCode()) ,SIExcelConf.FIELD_STRING_TYPE);//担当支店
        lWrite.writeDetailItem(9,order.getChargeName(),SIExcelConf.FIELD_STRING_TYPE);//担当者
        lWrite.writeDetailItem(10,SIUtil.getNameFromColl(branchNameColl,order.getShipBranch()) ,SIExcelConf.FIELD_STRING_TYPE);//出荷支店
        lWrite.writeDetailItem(11);//担当者
        lWrite.writeDetailItem(12,order.getPrice(),SIExcelConf.FIELD_NUMERIC_TYPE);//単価
        lWrite.writeDetailItem(13,order.getAmount(),SIExcelConf.FIELD_NUMERIC_TYPE);//数量 
        lWrite.writeDetailItem(14,order.getCmdtyPriceInTax(),SIExcelConf.FIELD_NUMERIC_TYPE);//商品金額（税込）
        lWrite.writeDetailItem(15,order.getTotalOfDeliveryFee(),SIExcelConf.FIELD_NUMERIC_TYPE);//送料
        lWrite.writeDetailItem(16,order.getSalesPriceInTax(),SIExcelConf.FIELD_NUMERIC_TYPE);//売上金額(税込）
        lWrite.writeDetailItem(17,order.getSalesPrice(),SIExcelConf.FIELD_NUMERIC_TYPE);//売上金額（税抜）
        lWrite.writeDetailItem(18,"0",SIExcelConf.FIELD_NUMERIC_TYPE);//担当支店粗利益
        lWrite.writeDetailItem(19,"0",SIExcelConf.FIELD_NUMERIC_TYPE);//出荷支店粗利益
        lWrite.writeDetailItem(20,"0",SIExcelConf.FIELD_NUMERIC_TYPE);//粗利益合計
        lWrite.writeDetailItem(21,"0",SIExcelConf.FIELD_NUMERIC_TYPE);//粗利益率
        lWrite.writeDetailItem(22,order.getTotalOfFee(),SIExcelConf.FIELD_NUMERIC_TYPE);//代引手数料
        lWrite.writeDetailItem(23,order.getSumOfDiscount(),SIExcelConf.FIELD_NUMERIC_TYPE);//値引金額 
        lWrite.writeDetailItem(24,order.getSumByPoint(),SIExcelConf.FIELD_NUMERIC_TYPE);//ﾎﾟｲﾝﾄ使用額 
        
        orderSum[orderint][0] = SIUtil.add(orderSum[orderint][0],order.getCmdtyPriceInTax());
        orderSum[orderint][1] = SIUtil.add(orderSum[orderint][1],order.getTotalOfDeliveryFee());
        orderSum[orderint][2] = SIUtil.add(orderSum[orderint][2],order.getSalesPriceInTax());
        orderSum[orderint][3] = SIUtil.add(orderSum[orderint][3],order.getSalesPrice());
        //orderSum[orderint][4] = SIUtil.add(orderSum[orderint][4],);
        //orderSum[orderint][5] = SIUtil.add(orderSum[orderint][5],);
        //orderSum[orderint][6] = SIUtil.add(orderSum[orderint][6],);
        //orderSum[orderint][7] = SIUtil.add(orderSum[orderint][7],);
        orderSum[orderint][8] = SIUtil.add(orderSum[orderint][8],order.getTotalOfFee());
        orderSum[orderint][9] = SIUtil.add(orderSum[orderint][9],order.getSumOfDiscount());
        orderSum[orderint][10] = SIUtil.add(orderSum[orderint][10],order.getSumByPoint());
        //支店別合計
        if(SIUtil.isNotNull(order.getBranchCode())){//売上のみ合計
          int count = Integer.parseInt(order.getBranchCode());
          brachSum[count][0] = SIUtil.add(brachSum[count][0],order.getCmdtyPriceInTax());
          brachSum[count][1] = SIUtil.add(brachSum[count][1],order.getTotalOfDeliveryFee());
          brachSum[count][2] = SIUtil.add(brachSum[count][2],order.getSalesPriceInTax());
          brachSum[count][3] = SIUtil.add(brachSum[count][3],order.getSalesPrice());
          //brachSum[count][4] = SIUtil.add(brachSum[count][4],);
          //brachSum[count][5] = SIUtil.add(brachSum[count][5],);
          //brachSum[count][6] = SIUtil.add(brachSum[count][6],);
          //brachSum[count][7] = SIUtil.add(brachSum[count][7],);
          brachSum[count][8] = SIUtil.add(brachSum[count][8],order.getTotalOfFee());
          brachSum[count][9] = SIUtil.add(brachSum[count][9],order.getSumOfDiscount());
          brachSum[count][10] = SIUtil.add(brachSum[count][10],order.getSumByPoint());
        }
        //担当者別合計
        if(SIUtil.isNotNull(order.getChargeCode())){//売上のみ合計
          int count = Integer.parseInt(order.getChargeCode().trim().substring(0,3));
          int count2 = Integer.parseInt(order.getChargeCode().trim().substring(3,5));
          chargeSum[count][count2][0] = SIUtil.add(chargeSum[count][count2][0],order.getCmdtyPriceInTax());
          chargeSum[count][count2][1] = SIUtil.add(chargeSum[count][count2][1],order.getTotalOfDeliveryFee());
          chargeSum[count][count2][2] = SIUtil.add(chargeSum[count][count2][2],order.getSalesPriceInTax());
          chargeSum[count][count2][3] = SIUtil.add(chargeSum[count][count2][3],order.getSalesPrice());
          //chargeSum[count][count2][4] = SIUtil.add(chargeSum[count][count2][4],);
          //chargeSum[count][count2][5] = SIUtil.add(chargeSum[count][count2][5],);
          //chargeSum[count][count2][6] = SIUtil.add(chargeSum[count][count2][6],);
          //chargeSum[count][count2][7] = SIUtil.add(chargeSum[count][count2][7],);
          chargeSum[count][count2][8] = SIUtil.add(chargeSum[count][count2][8],order.getTotalOfFee());
          chargeSum[count][count2][9] = SIUtil.add(chargeSum[count][count2][9],order.getSumOfDiscount());
          chargeSum[count][count2][10] = SIUtil.add(chargeSum[count][count2][10],order.getSumByPoint());
        }
        lineNo = lineNo + 1;
      }
      lWrite.removeDetail(4);
    }else{//明細なし
      while(it.hasNext()){
        order = (SIOrder)it.next();
        orderint = Integer.parseInt(order.getOrderStatus());
        orderSum[orderint][0] = SIUtil.add(orderSum[orderint][0],order.getCmdtyPriceInTax());
        orderSum[orderint][1] = SIUtil.add(orderSum[orderint][1],order.getTotalOfDeliveryFee());
        orderSum[orderint][2] = SIUtil.add(orderSum[orderint][2],order.getSalesPriceInTax());
        orderSum[orderint][3] = SIUtil.add(orderSum[orderint][3],order.getSalesPrice());
        //orderSum[orderint][4] = SIUtil.add(orderSum[orderint][4],);
        //orderSum[orderint][5] = SIUtil.add(orderSum[orderint][5],);
        //orderSum[orderint][6] = SIUtil.add(orderSum[orderint][6],);
        //orderSum[orderint][7] = SIUtil.add(orderSum[orderint][7],);
        orderSum[orderint][8] = SIUtil.add(orderSum[orderint][8],order.getTotalOfFee());
        orderSum[orderint][9] = SIUtil.add(orderSum[orderint][9],order.getSumOfDiscount());
        orderSum[orderint][10] = SIUtil.add(orderSum[orderint][10],order.getSumByPoint());
        //支店別合計
        if(SIUtil.isNotNull(order.getBranchCode())){//売上のみ合計
          int count = Integer.parseInt(order.getBranchCode());
          brachSum[count][0] = SIUtil.add(brachSum[count][0],order.getCmdtyPriceInTax());
          brachSum[count][1] = SIUtil.add(brachSum[count][1],order.getTotalOfDeliveryFee());
          brachSum[count][2] = SIUtil.add(brachSum[count][2],order.getSalesPriceInTax());
          brachSum[count][3] = SIUtil.add(brachSum[count][3],order.getSalesPrice());
          //brachSum[count][4] = SIUtil.add(brachSum[count][4],);
          //brachSum[count][5] = SIUtil.add(brachSum[count][5],);
          //brachSum[count][6] = SIUtil.add(brachSum[count][6],);
          //brachSum[count][7] = SIUtil.add(brachSum[count][7],);
          brachSum[count][8] = SIUtil.add(brachSum[count][8],order.getTotalOfFee());
          brachSum[count][9] = SIUtil.add(brachSum[count][9],order.getSumOfDiscount());
          brachSum[count][10] = SIUtil.add(brachSum[count][10],order.getSumByPoint());
        }
        //担当者別合計
        if(SIUtil.isNotNull(order.getChargeCode())){//売上のみ合計
          int count = Integer.parseInt(order.getChargeCode().trim().substring(0,3));
          int count2 = Integer.parseInt(order.getChargeCode().trim().substring(3,5));
          chargeSum[count][count2][0] = SIUtil.add(chargeSum[count][count2][0],order.getCmdtyPriceInTax());
          chargeSum[count][count2][1] = SIUtil.add(chargeSum[count][count2][1],order.getTotalOfDeliveryFee());
          chargeSum[count][count2][2] = SIUtil.add(chargeSum[count][count2][2],order.getSalesPriceInTax());
          chargeSum[count][count2][3] = SIUtil.add(chargeSum[count][count2][3],order.getSalesPrice());
          //chargeSum[count][count2][4] = SIUtil.add(chargeSum[count][count2][4],);
          //chargeSum[count][count2][5] = SIUtil.add(chargeSum[count][count2][5],);
          //chargeSum[count][count2][6] = SIUtil.add(chargeSum[count][count2][6],);
          //chargeSum[count][count2][7] = SIUtil.add(chargeSum[count][count2][7],);
          chargeSum[count][count2][8] = SIUtil.add(chargeSum[count][count2][8],order.getTotalOfFee());
          chargeSum[count][count2][9] = SIUtil.add(chargeSum[count][count2][9],order.getSumOfDiscount());
          chargeSum[count][count2][10] = SIUtil.add(chargeSum[count][count2][10],order.getSumByPoint());
        }
      }
      lWrite.removeDetail(4,true);
    }
    
    //フッター部【取引区分別小計】
    lineNo = 0;
    if(SIUtil.isNotNull(ioExcelList.getOrderStatusRdo())){
      int j = Integer.parseInt(ioExcelList.getOrderStatusRdo());
      lWrite.makeFooterLine(1,lineNo); 
      lWrite.writeFooterItem(0,lineNo,0,true);//【取引区分別小計】
      lWrite.writeFooterItem(7,SIConfig.ORDERSTATUS[j],SIExcelConf.FIELD_STRING_TYPE);//取引区分	
      for(int i=0;i<ENDLIST;i++){
        lWrite.writeFooterItem(i+14,orderSum[j][i],SIExcelConf.FIELD_NUMERIC_TYPE);//取引区分別小計
        totalSum[i] = SIUtil.add(totalSum[i] ,orderSum[j][i]);
      }
    }else{
      for(int j=0;j<3;j++){
        lWrite.makeFooterLine(1,lineNo);
        lWrite.writeFooterItem(0,lineNo,0,true);//【取引区分別小計】
        lWrite.writeFooterItem(7,SIConfig.ORDERSTATUS[j],SIExcelConf.FIELD_STRING_TYPE);//取引区分
        for(int i=0;i<ENDLIST;i++){
          lWrite.writeFooterItem(i+14,orderSum[j][i],SIExcelConf.FIELD_NUMERIC_TYPE);//取引区分別小計
          totalSum[i] = SIUtil.add(totalSum[i] ,orderSum[j][i]);
        }
        lineNo = lineNo + 1;
      }
    }
    
    //フッター部【支店別小計】
    lineNo = 0;      
    if(SIUtil.isNotNull(ioExcelList.getBranchCode())){
      lWrite.makeFooterLine(2,lineNo); 
      lWrite.writeFooterItem(0,lineNo,0,true);
      lWrite.writeFooterItem(8,SIUtil.getNameFromColl(branchColl,ioExcelList.getBranchCode()),SIExcelConf.FIELD_STRING_TYPE);
      int count = Integer.parseInt(ioExcelList.getBranchCode().trim());
      for(int i=0;i<ENDLIST;i++){
        lWrite.writeFooterItem(i+14,brachSum[count][i] ,SIExcelConf.FIELD_NUMERIC_TYPE);//合計
      }
    }else{
      branchIt =branchColl.iterator();
      while(branchIt.hasNext()){
        lWrite.makeFooterLine(2,lineNo);
        lWrite.writeFooterItem(0,lineNo,0,true);
        nameValue = (SINameValue) branchIt.next();
        int count = Integer.parseInt(nameValue.getValue().trim());
        lWrite.writeFooterItem(8,nameValue.getName(),SIExcelConf.FIELD_STRING_TYPE);
        for(int i=0;i<ENDLIST;i++){
          lWrite.writeFooterItem(i+14,brachSum[count][i] ,SIExcelConf.FIELD_NUMERIC_TYPE);//合計
        }
        lineNo = lineNo + 1;
      }
    }
    
    //フッター部【担当者別小計】
    lineNo = 0;
    if(SIUtil.isNotNull(ioExcelList.getChargeNameCdo())){
      lWrite.makeFooterLine(3,lineNo);
      lWrite.writeFooterItem(0,lineNo,0,true);//【合計】
      int count = Integer.parseInt(ioExcelList.getChargeNameCdo().trim().substring(0,3));
      int count2 = Integer.parseInt(ioExcelList.getChargeNameCdo().trim().substring(3,5));
      lWrite.writeFooterItem(9,SIUtil.getNameFromColl(chargeColl,ioExcelList.getChargeNameCdo()),SIExcelConf.FIELD_STRING_TYPE);
      for(int i=0;i<ENDLIST;i++){
        lWrite.writeFooterItem(i+14,chargeSum[count][count2][i],SIExcelConf.FIELD_NUMERIC_TYPE);//合計
      }
    }else{
      chargeit =chargeColl.iterator();
      while(chargeit.hasNext()){
        lWrite.makeFooterLine(3,lineNo);
        lWrite.writeFooterItem(0,lineNo,0,true);//【合計】
        nameValue = (SINameValue) chargeit.next();
        int count = Integer.parseInt(nameValue.getValue().trim().substring(0,3));
        int count2 = Integer.parseInt(nameValue.getValue().trim().substring(3,5));
        lWrite.writeFooterItem(9,nameValue.getName(),SIExcelConf.FIELD_STRING_TYPE);
        for(int i=0;i<ENDLIST;i++){
          lWrite.writeFooterItem(i+14,chargeSum[count][count2][i],SIExcelConf.FIELD_NUMERIC_TYPE);//合計
        }
        lineNo = lineNo + 1;
      }
    }
    
    //フッター部【合計】
    lineNo = 0;
    lWrite.makeFooterLine(4,lineNo);
    lWrite.writeFooterItem(0,lineNo,0,true);//【合計】
    for(int i=0;i<ENDLIST;i++){
      lWrite.writeFooterItem(i+14,totalSum[i] ,SIExcelConf.FIELD_NUMERIC_TYPE);//合計
    }
    lWrite.makePage();
    try{
      lWrite.close(response);
    }catch(IOException e) {
      //e.printStackTrace();
    }
  }
  
  private void printStoreHistory(HttpServletResponse response,Connection lConnection,UIIOExcelListCond ioExcelList){
    String template = (String) this.getServletContext().getRealPath("/docs/storehistory.xls");
    SIExcelRWrite lWrite = new SIExcelRWrite(SIExcelConf.SIEXCEL_STOREHISTORY_INX,template);
    Collection storeHistoryColl = ioExcelList.getStoreHistoryCollection(lConnection);
    Iterator it = storeHistoryColl.iterator();
    UIInventory lBasic=new UIInventory();
    int lineNo = 0;
    String oldIndividualCode = "";
    String cmdtyName = "";
    String sumAmount = "0";
    String costTotal = "";
    String costUnit = "";
    String avCostTotal = "0";
    String avCostUnit = "";
    String avStockCost = "0";
    String typeName = "";
    
    if(storeHistoryColl==null||storeHistoryColl.size()==0)return;
    
    lWrite.makeSheet(0,SIExcelConf.getTitleName(SIExcelConf.SIEXCEL_STOREHISTORY_INX),0);
    //ヘッダー部
    lWrite.writeHeaderItem(1,1,ioExcelList.getHeaderSeasonCbo(),SIExcelConf.FIELD_STRING_TYPE);//対象年月
    lWrite.writeHeaderItem(2,1,ioExcelList.getHeaderIndividualCode(),SIExcelConf.FIELD_STRING_TYPE);//在庫ｺｰﾄﾞ
    lWrite.writeHeaderItem(3,1,ioExcelList.getHeaderCtgryCode(lConnection),SIExcelConf.FIELD_STRING_TYPE);//ｶﾃｺﾞﾘ
    lWrite.writeHeaderItem(4,1,ioExcelList.getHeaderDetailFlgRdo(),SIExcelConf.FIELD_STRING_TYPE);//出力単位
    
    //明細部
    while(it.hasNext()){
      lBasic = (UIInventory)it.next();
      if(!oldIndividualCode.equals(lBasic.getIndividualCode())){//最初行
        if(SIUtil.isNotNull(oldIndividualCode)){
          lWrite.makeOverLine(lineNo);
          lineNo = lineNo + 1;//在庫コード別にSKIP
        }
        lWrite.makeLine(lineNo);
        lWrite.writeDetailItem(0,lBasic.getIndividualCode(),SIExcelConf.FIELD_STRING_TYPE);//在庫ｺｰﾄﾞ
        try{
          cmdtyName =SIBGUtil.getCmdtyFullName(lConnection,lBasic.getCmdtyCode(),lBasic.getIndividualCode());
        }catch(SIDBAccessException e){
          cmdtyName ="";
          e.printStackTrace();
        }
        lWrite.writeDetailItem(1,cmdtyName,SIExcelConf.FIELD_STRING_TYPE);//商品名
        oldIndividualCode = lBasic.getIndividualCode();
        sumAmount = lBasic.getStoreRecordAmount();
        costUnit = lBasic.getSumPrice();
        costTotal = SIUtil.multi(lBasic.getSumPrice(), sumAmount);
        if(SIUtil.isNull(costUnit)) costUnit = "0";
      }else{
        lWrite.makeLine(lineNo);
        lWrite.writeDetailItem(0);//在庫ｺｰﾄﾞ
        lWrite.writeDetailItem(1);//商品名
      }
      
      lWrite.writeDetailItem(2,lBasic.getInitdatetime(),SIExcelConf.FIELD_DATE_TYPE);//年月日
      lWrite.writeDetailItem(3,sumAmount,SIExcelConf.FIELD_NUMERIC_TYPE);//前回在庫数
      if(lBasic.getType().equals("5")){//調整数の場合
        lWrite.writeDetailItem(4,"0",SIExcelConf.FIELD_NUMERIC_TYPE);//入庫数
        lWrite.writeDetailItem(5,"0",SIExcelConf.FIELD_NUMERIC_TYPE);//出庫数
        lWrite.writeDetailItem(6,lBasic.getAmount(),SIExcelConf.FIELD_NUMERIC_TYPE);//調整数
        avCostTotal = SIUtil.add(lBasic.getCmdtyPrice(), costTotal);
        sumAmount = SIUtil.add(sumAmount,lBasic.getAmount());
        typeName = SIFlagUtil.getFlagName(SIFlagConf.SIFLAG_STORE_TYPE_FLG_INX,lBasic.getType());
      }else{
        if(lBasic.getStoreshipFlg().equals("0")){//入庫
          lWrite.writeDetailItem(4,lBasic.getAmount(),SIExcelConf.FIELD_NUMERIC_TYPE);//入庫数
          lWrite.writeDetailItem(5,"0",SIExcelConf.FIELD_NUMERIC_TYPE);//出庫数
          lWrite.writeDetailItem(6,"0",SIExcelConf.FIELD_NUMERIC_TYPE);//調整数
          avCostTotal = SIUtil.add(lBasic.getCmdtyPrice(), costTotal);
          sumAmount = SIUtil.add(sumAmount,lBasic.getAmount());
          typeName = SIFlagUtil.getFlagName(SIFlagConf.SIFLAG_STORE_TYPE_FLG_INX,lBasic.getType());
        }else if(lBasic.getStoreshipFlg().equals("1")){//出庫
          lWrite.writeDetailItem(4,"0",SIExcelConf.FIELD_NUMERIC_TYPE);//入庫数
          lWrite.writeDetailItem(5,lBasic.getAmount(),SIExcelConf.FIELD_NUMERIC_TYPE);//出庫数
          lWrite.writeDetailItem(6,"0",SIExcelConf.FIELD_NUMERIC_TYPE);//調整数
          avCostTotal = SIUtil.sub(costTotal, lBasic.getCmdtyPrice());
          sumAmount = SIUtil.sub(sumAmount,lBasic.getAmount());
          typeName = SIFlagUtil.getFlagName(SIFlagConf.SIFLAG_SHIP_TYPE_FLG_INX,lBasic.getType());
        }
        try{
          avCostUnit = SIUtil.div(avCostTotal,sumAmount);
          avStockCost = SIUtil.div(lBasic.getCmdtyPrice(),lBasic.getAmount());
        }catch(SIFatalException e){
          avCostUnit = "0";
          avStockCost = "0";
        }
      }
      lWrite.writeDetailItem(7,sumAmount,SIExcelConf.FIELD_NUMERIC_TYPE);//前回在庫数
      lWrite.writeDetailItem(8,costUnit,SIExcelConf.FIELD_NUMERIC_TYPE);//過去在庫原価
      lWrite.writeDetailItem(9,avStockCost,SIExcelConf.FIELD_NUMERIC_TYPE);//今回在庫原価
      lWrite.writeDetailItem(10,avCostUnit,SIExcelConf.FIELD_NUMERIC_TYPE);//平均在庫原価
      lWrite.writeDetailItem(11,typeName,SIExcelConf.FIELD_STRING_TYPE);
      lWrite.writeDetailItem(12,lBasic.getBranchName(),SIExcelConf.FIELD_STRING_TYPE);
      lWrite.writeDetailItem(13,lBasic.getChargeName(),SIExcelConf.FIELD_STRING_TYPE);
      lineNo = lineNo + 1;
      costTotal = avCostTotal;
      costUnit = avCostUnit;
    }
    //のこっているローを整理
    lWrite.removeDetail();
    
    lWrite.makePage();
    try{
      lWrite.close(response);
    }catch(IOException e){
      //e.printStackTrace();
    }
  }
  
  private void printMarketingReport(HttpServletResponse response,Connection lConnection,UIIOExcelListCond ioExcelList){
    String template = (String) this.getServletContext().getRealPath("/docs/marketingreport.xls");
    SIExcelRWrite lWrite = new SIExcelRWrite(SIExcelConf.SIEXCEL_MARKETINGREPORT_INX,template);
    lWrite.makeSheet(0,SIExcelConf.getTitleName(SIExcelConf.SIEXCEL_MARKETINGREPORT_INX),0); 
    UIMarketingReport MR = ioExcelList.getMR();
    MR.setType(1);
    MR.executeM(lConnection);
    
    lWrite.makeMarketingReport(MR);
    try{
      lWrite.close(response);
    }catch(IOException e){
      //e.printStackTrace();
    }
  }
  
  private void printMarketingReport0(HttpServletResponse response,Connection lConnection,UIIOExcelListCond ioExcelList){
    String template = (String) this.getServletContext().getRealPath("/docs/marketingreport.xls");
    SIExcelRWrite lWrite = new SIExcelRWrite(SIExcelConf.SIEXCEL_MARKETINGREPORT_INX,template);
    lWrite.makeSheet(0,SIExcelConf.getTitleName(SIExcelConf.SIEXCEL_MARKETINGREPORT_INX),0); 
    UIMarketingReport MR = ioExcelList.getMR();
    MR.setType(0);
    MR.executeM(lConnection);
    
    lWrite.makeMarketingReport(MR);
    try{
      lWrite.close(response);
    }catch(IOException e){
      //e.printStackTrace();
    }
  }
  
  private void printTrendReport(HttpServletResponse response,Connection lConnection,UIIOExcelListCond ioExcelList){
    String template = (String) this.getServletContext().getRealPath("/docs/trendreport.xls");
    SIExcelRWrite lWrite = new SIExcelRWrite(SIExcelConf.SIEXCEL_TRENDREPORT_INX,template);
    lWrite.makeSheet(0,SIExcelConf.getTitleName(SIExcelConf.SIEXCEL_TRENDREPORT_INX),0); 
    UIMarketingReport MR = ioExcelList.getMR();
    
    MR.executeT(lConnection);
    
    lWrite.makeTrendReport(MR);
    
    try{
      lWrite.close(response);
    }catch(IOException e){
      //e.printStackTrace();
    }
  }
  
  private void printSalesStatus(HttpServletResponse response,Connection lConnection,UIIOExcelListCond ioExcelList) {
    String template = (String) this.getServletContext().getRealPath("/docs/salesstatus.xls");;
    SIExcelRWrite lWrite = new SIExcelRWrite(SIExcelConf.SIEXCEL_SALES_STATUS_INX,template);
    UISalesStatus SS = ioExcelList.getSS();
    SS.setType(1);
    SS.execute(lConnection);
    
    lWrite.makeSalesStatus(SS);
    try{
      lWrite.close(response);
    }catch(IOException e){
      //e.printStackTrace();
    }
  }
  
  private void printSalesStatus0(HttpServletResponse response,Connection lConnection,UIIOExcelListCond ioExcelList) {
    String template = (String) this.getServletContext().getRealPath("/docs/salesstatus.xls");;
    SIExcelRWrite lWrite = new SIExcelRWrite(SIExcelConf.SIEXCEL_SALES_STATUS_INX,template);
    UISalesStatus SS = ioExcelList.getSS();
    SS.setType(0);
    SS.execute(lConnection);
    
    lWrite.makeSalesStatus(SS);
    try{
      lWrite.close(response);
    }catch(IOException e){
      //e.printStackTrace();
    }
  }
  
  private void printSalesStatusAll(HttpServletResponse response,Connection lConnection,UIIOExcelListCond ioExcelList) {
    String template = (String) this.getServletContext().getRealPath("/docs/salesstatus_all.xls");;
    SIExcelRWrite lWrite = new SIExcelRWrite(SIExcelConf.SIEXCEL_SALES_STATUS_ALL_INX,template);
    UISalesStatus SS = ioExcelList.getSS();
    SS.setType(1);
    SS.resetAll();
    SS.executeAll(lConnection);
    
    lWrite.makeSalesStatusAll(SS);
    try{
      lWrite.close(response);
    }catch(IOException e){
      //e.printStackTrace();
    }
  }
  
  private void printSalesStatusAll0(HttpServletResponse response,Connection lConnection,UIIOExcelListCond ioExcelList) {
    String template = (String) this.getServletContext().getRealPath("/docs/salesstatus_all.xls");;
    SIExcelRWrite lWrite = new SIExcelRWrite(SIExcelConf.SIEXCEL_SALES_STATUS_ALL_INX,template);
    UISalesStatus SS = ioExcelList.getSS();
    SS.setType(0);
    SS.resetAll();
    SS.executeAll(lConnection);
    
    lWrite.makeSalesStatusAll(SS);
    try{
      lWrite.close(response);
    }catch(IOException e){
      //e.printStackTrace();
    }
  }
  
  private void printSegmentStatus(HttpServletResponse response,Connection lConnection,UIIOExcelListCond ioExcelList) {
    String template = (String) this.getServletContext().getRealPath("/docs/segmentstatus.xls");;
    SIExcelRWrite lWrite = new SIExcelRWrite(SIExcelConf.SIEXCEL_SEGMENT_STATUS_INX,template);
    UISegmentStatus SE = ioExcelList.getSE();
    SE.setType(1);
    SE.execute(lConnection);
    
    lWrite.makeSegmentStatus(SE);
    try{
      lWrite.close(response);
    }catch(IOException e){
      //e.printStackTrace();
    }
  }
  
  private void printSegmentStatus0(HttpServletResponse response,Connection lConnection,UIIOExcelListCond ioExcelList) {
    String template = (String) this.getServletContext().getRealPath("/docs/segmentstatus.xls");;
    SIExcelRWrite lWrite = new SIExcelRWrite(SIExcelConf.SIEXCEL_SEGMENT_STATUS_INX,template);
    UISegmentStatus SE = ioExcelList.getSE();
    SE.setType(0);
    SE.execute(lConnection);
    
    lWrite.makeSegmentStatus(SE);
    try{
      lWrite.close(response);
    }catch(IOException e){
      //e.printStackTrace();
    }
  }
  
  private void produceHacchuPaymentCSVFile(HttpServletResponse response, Connection lConnection, UIIOExcelListCond ioExcel) {
    // CSVファイル名の作成
    String lCsvFileName = SICSVConf.getCsvFileName(SICSVConf.SICSV_HACCHU_STATUS_LIST_INX);
    SICSVWrite lCsv = new SICSVWrite(lCsvFileName);
    
    // SQL文の作成
    StringBuffer lSqlBuf = new StringBuffer();
    
    lSqlBuf.append("SELECT a.stockcode,s.stockname,a.shippmentdate AS paymentdate,a.hacchucode,b.branchname,c.chargename ");
    lSqlBuf.append(",a.individualcode,u.cmdtyname,m.makername,a.amount,a.branchname AS memo,a.p+a.m AS paymentpriceinctax ");
    lSqlBuf.append(",CASE WHEN a.amount IS NULL THEN 0 ELSE trunc((a.p+a.m)/a.amount) END AS purchaseprice ");
    lSqlBuf.append(",CASE WHEN a.amount IS NULL THEN a.p+a.m ELSE 0 END AS deliveryfee ");
    lSqlBuf.append(",trunc(a.p / (1 + (h.taxrate / 100))) +trunc(a.m / (1 + (h.taxrate / 100))) AS paymentprice,o.ordercode,o.custcode ");
    lSqlBuf.append(",h.taxrate ");
    lSqlBuf.append("FROM hacchupaymentvw a ");
    lSqlBuf.append(",(SELECT hacchucode,ordercode,taxrate FROM hacchutbl WHERE enabledflg=1) h ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT ordercode,custcode FROM ordertbl WHERE enabledflg=1) o ON h.ordercode=o.ordercode ");
    lSqlBuf.append(",stockmtbl s,chargetbl c,cmdtyunittbl u,branchtbl b,makertbl m ");
    lSqlBuf.append("WHERE a.hacchucode=h.hacchucode AND a.stockcode=s.stockcode AND a.chargecode=c.chargecode AND c.belongingbranchcode=b.branchcode ");
    lSqlBuf.append("AND a.individualcode=u.individualcode AND u.makercode=m.makercode ");
    lSqlBuf.append("AND CASE WHEN (a.branchcode=-1 AND a.p=0 AND a.m=0) THEN false ELSE true END ");
    
    lSqlBuf.append(ioExcel.getConditionSQL());
    lSqlBuf.append(SICSVConf.getOrderBySQL(SICSVConf.SICSV_HACCHU_STATUS_LIST_INX));
    
    // SQL文の設定
    lCsv.setSqlStatement(lSqlBuf.toString());
    
    // タイトルの設定
    lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_HACCHU_STATUS_LIST_INX));
    
    // CSVファイルの出力
    lCsv.execute(lConnection, response);
  }
  
  private void produceHistoryDetailCSVFile(HttpServletResponse response, Connection lConnection, UIIOExcelListCond ioExcel) {
    // CSVファイル名の作成
    String lCsvFileName = SICSVConf.getCsvFileName(SICSVConf.SICSV_HISTORY_DETAIL_LIST_INX);
    SICSVWrite lCsv = new SICSVWrite(lCsvFileName);
    
    boolean storeZeroFlg = false;
    boolean storeOtherFlg = false;
    boolean shipZeroFlg = false;
    boolean shipOtherFlg = false;
    StringBuffer storeTypeList = new StringBuffer();
    StringBuffer shipTypeList = new StringBuffer();
    
    // SQL文の作成
    StringBuffer lSqlBuf = new StringBuffer();
    if (ioExcel.getShipType() != null && ioExcel.getShipType().length > 0) {
      for (int j=0;j<ioExcel.getShipType().length;j++) {
        if (j>0) shipTypeList.append(",");
        shipTypeList.append(ioExcel.getShipType()[j]);
        if ("0".equals(ioExcel.getShipType()[j])) shipZeroFlg = true;
        else shipOtherFlg = true;
      }
      if (shipOtherFlg) {
        lSqlBuf.append("SELECT a.calcdatetime::date,c.chargename,b.branchname,a.individualcode,d.cmdtyname,e.colorname, ");
        lSqlBuf.append("CASE WHEN substr(d.individualcode,0,2) = 'L' THEN 'エステ化粧品' ");
        lSqlBuf.append("     WHEN substr(d.individualcode,0,2) IN ('H','V') THEN 'ヘア化粧品' ");
        lSqlBuf.append("     WHEN substr(d.individualcode,0,2) IN ('W','N') THEN 'ネイル・まつ毛材料' ");
        lSqlBuf.append("     WHEN d.usednewFlg IN (1,6) THEN '新品（NB）' ");
        lSqlBuf.append("     WHEN d.usednewFlg = 7 THEN '新品（PB）' ");
        lSqlBuf.append("     ELSE '中古・アウトレット' END AS segment, ");
        lSqlBuf.append("CASE a.shiptype ");
        for (int i=1;i<SIFlagConf.SIFLAG_SHIP_TYPE_FLG_NAME.length;i++) {
          lSqlBuf.append("WHEN  ").append(SIFlagConf.SIFLAG_SHIP_TYPE_FLG_NAME[i][1]).append(" THEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_SHIP_TYPE_FLG_NAME[i][0]," "));
        }
        lSqlBuf.append("ELSE 'その他' END AS type, ");
        lSqlBuf.append("a.shipnumber AS number, ");
        lSqlBuf.append("CASE a.delflg WHEN 3 THEN 1 ELSE -1 END * a.amount AS amount, ");
        lSqlBuf.append("CASE a.delflg WHEN 3 THEN 1 ELSE -1 END * a.purchaseprice AS purchaseprice, ");
        lSqlBuf.append("CASE a.delflg WHEN 3 THEN 1 ELSE -1 END * a.processingexpence AS processsingexpence, ");
        lSqlBuf.append("CASE a.delflg WHEN 3 THEN 1 ELSE -1 END * (a.purchaseprice+a.processingexpence) AS costinctax, ");
        lSqlBuf.append("CASE a.delflg WHEN 3 THEN 1 ELSE -1 END * trunc((a.purchaseprice+a.processingexpence)/(1 + (tax(a.calcdatetime::date) / 100))) AS cost, ");
        lSqlBuf.append("a.comment,'出庫' AS flg, ");
        lSqlBuf.append("CASE WHEN a.shiptype IN (0,13,16) THEN a.ordercode ELSE NULL END AS ordercode ");
        lSqlBuf.append("FROM shiphistorytbl a ");
        lSqlBuf.append("LEFT OUTER JOIN branchtbl b ON a.shipbranch=b.branchcode ");
        lSqlBuf.append("LEFT OUTER JOIN chargetbl c ON a.shipcharge=c.chargecode ");
        lSqlBuf.append(",cmdtyunittbl d,colortbl e ");
        lSqlBuf.append("WHERE a.individualcode=d.individualcode AND d.colorcode=e.colorcode ");
        lSqlBuf.append("AND a.delflg IN (1,2,3,4) AND a.shiptype<>0 ");
        lSqlBuf.append("AND a.shiptype IN (").append(shipTypeList).append(") ");
        lSqlBuf.append("AND a.calcdatetime::date >= ").append(SIDBUtil.SQL2Str(ioExcel.getSeasonFromCho()," "));
        lSqlBuf.append("AND a.calcdatetime::date <= ").append(SIDBUtil.SQL2Str(ioExcel.getSeasonToCho()," "));
      }
      if (shipZeroFlg) {
        if (shipOtherFlg) lSqlBuf.append("UNION ALL ");
        lSqlBuf.append("SELECT a.initdatetime AS calcdatetime,c.chargename,b.branchname,a.individualcode,d.cmdtyname,e.colorname, ");
        lSqlBuf.append("CASE WHEN substr(d.individualcode,0,2) = 'L' THEN 'エステ化粧品' ");
        lSqlBuf.append("     WHEN substr(d.individualcode,0,2) IN ('H','V') THEN 'ヘア化粧品' ");
        lSqlBuf.append("     WHEN substr(d.individualcode,0,2) IN ('W','N') THEN 'ネイル・まつ毛材料' ");
        lSqlBuf.append("     WHEN d.usednewFlg IN (1,6) THEN '新品（NB）' ");
        lSqlBuf.append("     WHEN d.usednewFlg = 7 THEN '新品（PB）' ");
        lSqlBuf.append("     ELSE '中古・アウトレット' END AS segment, ");
        lSqlBuf.append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_SHIP_TYPE_FLG_NAME[0][0])).append(" AS type, ");
        lSqlBuf.append("a.shipnumber AS number, ");
        lSqlBuf.append("CASE a.delflg WHEN 3 THEN 1 ELSE -1 END * a.amount AS amount, ");
        lSqlBuf.append("CASE a.delflg WHEN 3 THEN 1 ELSE -1 END * a.purchaseprice AS purchaseprice, ");
        lSqlBuf.append("CASE a.delflg WHEN 3 THEN 1 ELSE -1 END * a.processingexpence AS processsingexpence, ");
        lSqlBuf.append("CASE a.delflg WHEN 3 THEN 1 ELSE -1 END * (a.purchaseprice+a.processingexpence) AS costinctax, ");
        lSqlBuf.append("CASE a.delflg WHEN 3 THEN 1 ELSE -1 END * trunc((a.purchaseprice+a.processingexpence)/(1 + (tax(a.initdatetime) / 100))) AS cost, ");
        lSqlBuf.append("a.comment,'出庫' AS flg, ");
        lSqlBuf.append("CASE WHEN a.shiptype IN (0,13,16) THEN a.ordercode ELSE NULL END AS ordercode ");
        lSqlBuf.append("FROM shiphistorytbl a ");
        lSqlBuf.append("LEFT OUTER JOIN branchtbl b ON a.shipbranch=b.branchcode ");
        lSqlBuf.append("LEFT OUTER JOIN chargetbl c ON a.shipcharge=c.chargecode ");
        lSqlBuf.append(",cmdtyunittbl d,colortbl e ");
        lSqlBuf.append("WHERE a.individualcode=d.individualcode AND d.colorcode=e.colorcode ");
        lSqlBuf.append("AND a.delflg IN (1,2,3,4) AND a.shiptype=0 ");
        lSqlBuf.append("AND a.initdatetime >= ").append(SIDBUtil.SQL2Str(ioExcel.getSeasonFromCho()," "));
        lSqlBuf.append("AND a.initdatetime <= ").append(SIDBUtil.SQL2Str(ioExcel.getSeasonToCho()," "));
      }
      
      if (ioExcel.getStoreType() != null && ioExcel.getStoreType().length > 0) {
        lSqlBuf.append("UNION ALL ");
      }
    }
    if (ioExcel.getStoreType() != null && ioExcel.getStoreType().length > 0) {
      for (int j=0;j<ioExcel.getStoreType().length;j++) {
        if (j>0) storeTypeList.append(",");
        storeTypeList.append(ioExcel.getStoreType()[j]);
        if ("0".equals(ioExcel.getStoreType()[j])) storeZeroFlg = true;
        else storeOtherFlg = true;
      }
      if (storeOtherFlg) {
        lSqlBuf.append("SELECT a.calcdatetime::date,c.chargename,b.branchname,a.individualcode,d.cmdtyname,e.colorname, ");
        lSqlBuf.append("CASE WHEN substr(d.individualcode,0,2) = 'L' THEN 'エステ化粧品' ");
        lSqlBuf.append("     WHEN substr(d.individualcode,0,2) IN ('H','V') THEN 'ヘア化粧品' ");
        lSqlBuf.append("     WHEN substr(d.individualcode,0,2) IN ('W','N') THEN 'ネイル・まつ毛材料' ");
        lSqlBuf.append("     WHEN d.usednewFlg IN (1,6) THEN '新品（NB）' ");
        lSqlBuf.append("     WHEN d.usednewFlg = 7 THEN '新品（PB）' ");
        lSqlBuf.append("     ELSE '中古・アウトレット' END AS segment, ");
        lSqlBuf.append("CASE a.storetype ");
        for (int i=1;i<SIFlagConf.SIFLAG_STORE_TYPE_FLG_NAME.length;i++) {
          lSqlBuf.append("WHEN  ").append(SIFlagConf.SIFLAG_STORE_TYPE_FLG_NAME[i][1]).append(" THEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_STORE_TYPE_FLG_NAME[i][0]," "));
        }
        lSqlBuf.append("ELSE 'その他' END AS type, ");
        lSqlBuf.append("a.storenumber AS number, ");
        lSqlBuf.append("CASE a.delflg WHEN 3 THEN -1 ELSE 1 END * a.amount AS amount, ");
        lSqlBuf.append("CASE a.delflg WHEN 3 THEN -1 ELSE 1 END * a.purchaseprice AS purchaseprice, ");
        lSqlBuf.append("CASE a.delflg WHEN 3 THEN -1 ELSE 1 END * a.processingexpence AS processsingexpence, ");
        lSqlBuf.append("CASE a.delflg WHEN 3 THEN -1 ELSE 1 END * (a.purchaseprice+a.processingexpence) AS costinctax, ");
        lSqlBuf.append("CASE a.delflg WHEN 3 THEN -1 ELSE 1 END * trunc((a.purchaseprice+a.processingexpence)/(1 + (tax(a.calcdatetime::date) / 100))) AS cost, ");
        lSqlBuf.append("a.comment,'入庫' AS flg, ");
        lSqlBuf.append("CASE WHEN a.storetype=13 THEN a.ordercode ELSE NULL END AS ordercode ");
        lSqlBuf.append("FROM storehistorytbl a ");
        lSqlBuf.append("LEFT OUTER JOIN branchtbl b ON a.storebranch=b.branchcode ");
        lSqlBuf.append("LEFT OUTER JOIN chargetbl c ON a.storecharge=c.chargecode ");
        lSqlBuf.append(",cmdtyunittbl d,colortbl e ");
        lSqlBuf.append("WHERE a.individualcode=d.individualcode AND d.colorcode=e.colorcode ");
        lSqlBuf.append("AND a.delflg IN (1,2,3,4) AND a.storetype<>0 ");
        lSqlBuf.append("AND a.storetype IN (").append(storeTypeList).append(") ");
        lSqlBuf.append("AND a.calcdatetime::date >= ").append(SIDBUtil.SQL2Str(ioExcel.getSeasonFromCho()," "));
        lSqlBuf.append("AND a.calcdatetime::date <= ").append(SIDBUtil.SQL2Str(ioExcel.getSeasonToCho()," "));
      }
      if (storeZeroFlg) {
        if (storeOtherFlg) lSqlBuf.append("UNION ALL ");
        lSqlBuf.append("SELECT a.initdatetime AS calcdatetime,c.chargename,b.branchname,a.individualcode,d.cmdtyname,e.colorname, ");
        lSqlBuf.append("CASE WHEN substr(d.individualcode,0,2) = 'L' THEN 'エステ化粧品' ");
        lSqlBuf.append("     WHEN substr(d.individualcode,0,2) IN ('H','V') THEN 'ヘア化粧品' ");
        lSqlBuf.append("     WHEN substr(d.individualcode,0,2) IN ('W','N') THEN 'ネイル・まつ毛材料' ");
        lSqlBuf.append("     WHEN d.usednewFlg IN (1,6) THEN '新品（NB）' ");
        lSqlBuf.append("     WHEN d.usednewFlg = 7 THEN '新品（PB）' ");
        lSqlBuf.append("     ELSE '中古・アウトレット' END AS segment, ");
        lSqlBuf.append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_STORE_TYPE_FLG_NAME[0][0])).append(" AS type, ");
        lSqlBuf.append("a.storenumber AS number, ");
        lSqlBuf.append("CASE a.delflg WHEN 3 THEN -1 ELSE 1 END * a.amount AS amount, ");
        lSqlBuf.append("CASE a.delflg WHEN 3 THEN -1 ELSE 1 END * a.purchaseprice AS purchaseprice, ");
        lSqlBuf.append("CASE a.delflg WHEN 3 THEN -1 ELSE 1 END * a.processingexpence AS processsingexpence, ");
        lSqlBuf.append("CASE a.delflg WHEN 3 THEN -1 ELSE 1 END * (a.purchaseprice+a.processingexpence) AS costinctax, ");
        lSqlBuf.append("CASE a.delflg WHEN 3 THEN -1 ELSE 1 END * trunc((a.purchaseprice+a.processingexpence)/(1 + (tax(a.initdatetime) / 100))) AS cost, ");
        lSqlBuf.append("a.comment,'入庫' AS flg, ");
        lSqlBuf.append("CASE WHEN a.storetype=13 THEN a.ordercode ELSE NULL END AS ordercode ");
        lSqlBuf.append("FROM storehistorytbl a ");
        lSqlBuf.append("LEFT OUTER JOIN branchtbl b ON a.storebranch=b.branchcode ");
        lSqlBuf.append("LEFT OUTER JOIN chargetbl c ON a.storecharge=c.chargecode ");
        lSqlBuf.append(",cmdtyunittbl d,colortbl e ");
        lSqlBuf.append("WHERE a.individualcode=d.individualcode AND d.colorcode=e.colorcode ");
        lSqlBuf.append("AND a.delflg IN (1,2,3,4) AND a.storetype=0 ");
        lSqlBuf.append("AND a.initdatetime >= ").append(SIDBUtil.SQL2Str(ioExcel.getSeasonFromCho()," "));
        lSqlBuf.append("AND a.initdatetime <= ").append(SIDBUtil.SQL2Str(ioExcel.getSeasonToCho()," "));
      }
    }
    
    lSqlBuf.append(SICSVConf.getOrderBySQL(SICSVConf.SICSV_HISTORY_DETAIL_LIST_INX));
    
    // SQL文の設定
    lCsv.setSqlStatement(lSqlBuf.toString());
    
    // タイトルの設定
    lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_HISTORY_DETAIL_LIST_INX));
    
    // CSVファイルの出力
    lCsv.execute(lConnection, response);
  }
  
  private void produceDeadStockListCSVFile(HttpServletResponse response, Connection lConnection, UIIOExcelListCond ioExcel) {
    // CSVファイル名の作成
    String lCsvFileName = SICSVConf.getCsvFileName(SICSVConf.SICSV_DEADSTOCK_LIST_INX);
    SICSVWrite lCsv = new SICSVWrite(lCsvFileName);
    
    String targetDate = "";
    if ("04".equals(ioExcel.getSeasonMonthCbo())) {
      targetDate=ioExcel.getSeasonYearCbo() + "-04-30";
    } else {
      targetDate=ioExcel.getSeasonYearCbo() + "-"+ ioExcel.getSeasonMonthCbo() +"-31";
    }
    
    // SQL文の作成
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append("SELECT r.branchname");// 拠点名
    lSqlBuf.append(",a.individualcode");// 在庫コード
    lSqlBuf.append(",a.cmdtyname");// 商品名
    lSqlBuf.append(",s.amount");// 数量
    lSqlBuf.append(",i.purchaseprice+i.processingexpence AS cost");// 原価
    lSqlBuf.append(",(i.purchaseprice+i.processingexpence) * s.amount AS stockprice");// 帳簿価格（原価×数量）
    // セグメント
    lSqlBuf.append(",CASE WHEN substr(a.individualcode,0,2) IN ('L','W','H','V','N') THEN '化粧品類'");//L:化粧品,W:まつ毛,H:水物,V:エクステ,N:ネイル
    lSqlBuf.append(" WHEN i.usednewflg IN ('1','6','7') THEN '新品商品'");// 1:NB,6:キャンペーン,7:PB
    lSqlBuf.append(" WHEN i.usednewflg = '4' THEN '展示品'");// 4:展示品
    lSqlBuf.append(" ELSE '中古・アウトレット商品' END AS segment");// それ以外は中古アウトレット
    
    lSqlBuf.append(",vw.init");// 最終仕入日
    lSqlBuf.append(",vw.out");// 最終販売日
    lSqlBuf.append(",vw.init2");// 最終評価替え日
    lSqlBuf.append(",vw.latestdate");// 評価開始日
    lSqlBuf.append(",i.bgprice");// BG卸価
    lSqlBuf.append(",CASE a.frontdispflg WHEN 1 THEN '表示' ELSE '非表示' END AS frontdisp");// フロント表示
    lSqlBuf.append(",replace(replace(replace(replace(replace(replace(vw.interv2::text,' years','年'),' year','年'),' mons','ヶ月'),' mon','ヶ月'),' days','日'),' day','日') AS period");// 滞留期間
    // 対象日の評価替え予定
    lSqlBuf.append(",CASE WHEN substr(a.individualcode,0,2) IN ('H','L','W','V','N') AND vw.interv2 >= '3 years' AND vw.interv1 >= '1 years' THEN '廃棄対象商品'");// 化粧品＆総滞留期間３年以上＆未償却滞留期間１年以上
    lSqlBuf.append(" WHEN substr(a.individualcode,0,2) IN ('H','L','W','V','N') AND vw.interv1 >= '2 years' THEN '50%償却対象商品'");// 化粧品＆未償却滞留期間２年以上
    lSqlBuf.append(" WHEN i.usednewflg IN ('0','2','3') AND vw.interv2 >= '4 years' AND vw.interv1 >= '2 years' THEN '廃棄対象商品'");// 中古＆総滞留期間４年以上＆未償却滞留期間２年以上
    lSqlBuf.append(" WHEN i.usednewflg IN ('1','6','7') AND vw.interv2 >= '6 years' AND vw.interv1 >= '2 years' THEN '廃棄対象商品'");// 新品＆総滞留期間６年以上＆未償却滞留期間２年以上
    lSqlBuf.append(" WHEN i.usednewflg = '4' AND vw.interv2 >= '8 years' AND vw.interv1 >= '2 years' THEN '廃棄対象商品'");// 展示品＆総滞留期間８年以上＆未償却滞留期間２年以上
    lSqlBuf.append(" WHEN i.usednewflg != '4' AND vw.interv1 >= '4 years' THEN '51%償却対象商品'");// 展示品以外＆未償却滞留期間４年以上
    lSqlBuf.append(" WHEN vw.interv1 >= '6 years' THEN '51%償却対象商品'");// 未償却滞留期間６年以上
    lSqlBuf.append(" WHEN i.usednewflg != '4' AND vw.interv1 >= '2 years' THEN '30%償却対象商品'");// 展示品以外＆未償却滞留期間２年以上
    lSqlBuf.append(" WHEN i.usednewflg = '4' AND vw.interv2 >= '6 years' AND vw.interv1 >= '2 years' THEN '30%償却対象商品'");// 展示品＆総滞留期間６年以上＆未償却滞留期間２年以上
    lSqlBuf.append(" WHEN i.usednewflg = '4' AND vw.interv1 >= '4 years' THEN '30%償却対象商品'");// 展示品＆未償却滞留期間４年以上
    lSqlBuf.append(" ELSE '' END AS latest ");
    // 対象日の次の評価替え予定（上の予定と比べて期間指定を３ヶ月早めて計算する）
    lSqlBuf.append(",CASE WHEN substr(a.individualcode,0,2) IN ('H','L','W','V','N') AND vw.interv2 >= '2 years 9 months' AND vw.interv1 >= '9 months' AND vw.interv1 < '1 years' THEN '廃棄対象商品'");
    lSqlBuf.append(" WHEN substr(a.individualcode,0,2) IN ('H','L','W','V','N') AND vw.interv1 >= '1 years 9 months' AND vw.interv1 < '2 years' THEN '50%償却対象商品'");
    lSqlBuf.append(" WHEN i.usednewflg IN ('0','2','3') AND vw.interv2 >= '3 years 9 months' AND vw.interv1 >= '1 years 9 months' AND vw.interv1 < '2 years' THEN '廃棄対象商品'");
    lSqlBuf.append(" WHEN i.usednewflg IN ('1','6','7') AND vw.interv2 >= '5 years 9 months' AND vw.interv1 >= '1 years 9 months' AND vw.interv1 < '2 years' THEN '廃棄対象商品'");
    lSqlBuf.append(" WHEN i.usednewflg = '4' AND vw.interv2 >= '7 years 9months' AND vw.interv1 >= '1 years 9 months' AND vw.interv1 < '2 years' THEN '廃棄対象商品'");
    lSqlBuf.append(" WHEN i.usednewflg != '4' AND vw.interv1 >= '3 years 9 months' THEN '51%償却対象商品'");
    lSqlBuf.append(" WHEN vw.interv1 >= '5 years 9 months' THEN '51%償却対象商品'");
    lSqlBuf.append(" WHEN i.usednewflg != '4' AND vw.interv1 >= '1 years 9 months' AND vw.interv1 < '2 years' THEN '30%償却対象商品'");
    lSqlBuf.append(" WHEN i.usednewflg = '4' AND vw.interv2 >= '5 years 9 months' AND vw.interv1 >= '3 years 9 months' AND vw.interv1 < '2 years' THEN '30%償却対象商品'");
    lSqlBuf.append(" WHEN i.usednewflg = '4' AND vw.interv1 >= '3 years 9 months' THEN '30%償却対象商品'");
    lSqlBuf.append(" ELSE '' END AS nxt ");
    //  対象日の償却率（予定と同じ条件）
    lSqlBuf.append(",CASE WHEN substr(a.individualcode,0,2) IN ('H','L','W','V','N') AND vw.interv2 >= '3 years' AND vw.interv1 >= '1 years' THEN '100%'");
    lSqlBuf.append(" WHEN substr(a.individualcode,0,2) IN ('H','L','W','V','N') AND vw.interv1 >= '2 years' THEN '50%'");
    lSqlBuf.append(" WHEN i.usednewflg IN ('0','2','3') AND vw.interv2 >= '4 years' AND vw.interv1 >= '2 years' THEN '100%'");
    lSqlBuf.append(" WHEN i.usednewflg IN ('1','6','7') AND vw.interv2 >= '6 years' AND vw.interv1 >= '2 years' THEN '100%'");
    lSqlBuf.append(" WHEN i.usednewflg = '4' AND vw.interv2 >= '8 years' AND vw.interv1 >= '2 years' THEN '100%'");
    lSqlBuf.append(" WHEN i.usednewflg != '4' AND vw.interv1 >= '4 years' THEN '51%'");
    lSqlBuf.append(" WHEN vw.interv1 >= '6 years' THEN '51%'");
    lSqlBuf.append(" WHEN i.usednewflg != '4' AND vw.interv1 >= '2 years' THEN '30%'");
    lSqlBuf.append(" WHEN i.usednewflg = '4' AND vw.interv2 >= '6 years' AND vw.interv1 >= '2 years' THEN '30%'");
    lSqlBuf.append(" WHEN i.usednewflg = '4' AND vw.interv1 >= '4 years' THEN '30%'");
    lSqlBuf.append(" ELSE '0%' END AS depreciationrate ");
    //  税込評価額（帳簿価格−帳簿価格×対象日の償却率）
    lSqlBuf.append(",s.amount * ((i.purchaseprice+i.processingexpence) -");
    lSqlBuf.append(" TRUNC(CASE WHEN substr(a.individualcode,0,2) IN ('H','L','W','V','N') AND vw.interv2 >= '3 years' AND vw.interv1 >= '1 years' THEN 1.0");
    lSqlBuf.append(" WHEN substr(a.individualcode,0,2) IN ('H','L','W','V','N') AND vw.interv1 >= '2 years' THEN 0.5");
    lSqlBuf.append(" WHEN i.usednewflg IN ('0','2','3') AND vw.interv2 >= '4 years' AND vw.interv1 >= '2 years' THEN 1.0");
    lSqlBuf.append(" WHEN i.usednewflg IN ('1','6','7') AND vw.interv2 >= '6 years' AND vw.interv1 >= '2 years' THEN 1.0");
    lSqlBuf.append(" WHEN i.usednewflg = '4' AND vw.interv2 >= '8 years' AND vw.interv1 >= '2 years' THEN 1.0");
    lSqlBuf.append(" WHEN i.usednewflg != '4' AND vw.interv1 >= '4 years' THEN 0.51");
    lSqlBuf.append(" WHEN vw.interv1 >= '6 years' THEN 0.51");
    lSqlBuf.append(" WHEN i.usednewflg != '4' AND vw.interv1 >= '2 years' THEN 0.3");
    lSqlBuf.append(" WHEN i.usednewflg = '4' AND vw.interv2 >= '6 years' AND vw.interv1 >= '2 years' THEN 0.3");
    lSqlBuf.append(" WHEN i.usednewflg = '4' AND vw.interv1 >= '4 years' THEN 0.3");
    lSqlBuf.append(" ELSE 0 END * (i.purchaseprice+i.processingexpence))) AS appraisedvalueinctax ");
    // 税込評価損（帳簿価格×対象日の償却率）
    lSqlBuf.append(",s.amount * ");
    lSqlBuf.append(" (TRUNC(CASE WHEN substr(a.individualcode,0,2) IN ('H','L','W','V','N') AND vw.interv2 >= '3 years' AND vw.interv1 >= '1 years' THEN 1.0");
    lSqlBuf.append(" WHEN substr(a.individualcode,0,2) IN ('H','L','W','V','N') AND vw.interv1 >= '2 years' THEN 0.5");
    lSqlBuf.append(" WHEN i.usednewflg IN ('0','2','3') AND vw.interv2 >= '4 years' AND vw.interv1 >= '2 years' THEN 1.0");
    lSqlBuf.append(" WHEN i.usednewflg IN ('1','6','7') AND vw.interv2 >= '6 years' AND vw.interv1 >= '2 years' THEN 1.0");
    lSqlBuf.append(" WHEN i.usednewflg = '4' AND vw.interv2 >= '8 years' AND vw.interv1 >= '2 years' THEN 1.0");
    lSqlBuf.append(" WHEN i.usednewflg != '4' AND vw.interv1 >= '4 years' THEN 0.51");
    lSqlBuf.append(" WHEN vw.interv1 >= '6 years' THEN 0.51");
    lSqlBuf.append(" WHEN i.usednewflg != '4' AND vw.interv1 >= '2 years' THEN 0.3");
    lSqlBuf.append(" WHEN i.usednewflg = '4' AND vw.interv2 >= '6 years' AND vw.interv1 >= '2 years' THEN 0.3");
    lSqlBuf.append(" WHEN i.usednewflg = '4' AND vw.interv1 >= '4 years' THEN 0.3");
    lSqlBuf.append(" ELSE 0 END * (i.purchaseprice+i.processingexpence))) AS appraisedloseinctax ");
    // 評価損（税抜換算帳簿価格−税抜換算評価額）
    lSqlBuf.append(",TRUNC(s.amount * (i.purchaseprice+i.processingexpence) /(1 + (tax(").append(SIDBUtil.SQL2Str(targetDate,"::date")).append(") / 100))) - ");
    lSqlBuf.append(" TRUNC(s.amount * ((i.purchaseprice+i.processingexpence) - ");
    lSqlBuf.append(" TRUNC(CASE WHEN substr(a.individualcode,0,2) IN ('H','L','W','V','N') AND vw.interv2 >= '3 years' AND vw.interv1 >= '1 years' THEN 1.0");
    lSqlBuf.append(" WHEN substr(a.individualcode,0,2) IN ('H','L','W','V','N') AND vw.interv1 >= '2 years' THEN 0.5");
    lSqlBuf.append(" WHEN i.usednewflg IN ('0','2','3') AND vw.interv2 >= '4 years' AND vw.interv1 >= '2 years' THEN 1.0");
    lSqlBuf.append(" WHEN i.usednewflg IN ('1','6','7') AND vw.interv2 >= '6 years' AND vw.interv1 >= '2 years' THEN 1.0");
    lSqlBuf.append(" WHEN i.usednewflg = '4' AND vw.interv2 >= '8 years' AND vw.interv1 >= '2 years' THEN 1.0");
    lSqlBuf.append(" WHEN i.usednewflg != '4' AND vw.interv1 >= '4 years' THEN 0.51");
    lSqlBuf.append(" WHEN vw.interv1 >= '6 years' THEN 0.51");
    lSqlBuf.append(" WHEN i.usednewflg != '4' AND vw.interv1 >= '2 years' THEN 0.3");
    lSqlBuf.append(" WHEN i.usednewflg = '4' AND vw.interv2 >= '6 years' AND vw.interv1 >= '2 years' THEN 0.3");
    lSqlBuf.append(" WHEN i.usednewflg = '4' AND vw.interv1 >= '4 years' THEN 0.3");
    lSqlBuf.append(" ELSE 0 END * (i.purchaseprice+i.processingexpence)))/(1 + (tax(").append(SIDBUtil.SQL2Str(targetDate,"::date")).append(") / 100))) AS appraisedlose ");
    
    lSqlBuf.append("FROM cmdtyunittbl a ");
    lSqlBuf.append(",branchtbl r");
    lSqlBuf.append(",individualtbl i");
    lSqlBuf.append(",(SELECT individualcode,branchcode,realamount AS amount FROM realstockvw) s");
    lSqlBuf.append(",(SELECT store.individualcode,store.init,ship.out,store.init2,ship.out2");
    //最終仕入日、最終販売日、最終評価替え日ベース１２のうち一番大きい日付（最終評価替え日）
    lSqlBuf.append(" ,CASE WHEN store.init2 IS NOT NULL AND store.init<store.init2 AND (ship.out IS NULL OR store.init2>ship.out) THEN store.init2");
    lSqlBuf.append(" WHEN ship.out IS NOT NULL AND store.init<ship.out THEN ship.out");
    lSqlBuf.append(" ELSE store.init END AS latestdate");
    //対象日と最終評価替え日のインターバル（未償却滞留期間）
    lSqlBuf.append(",age(").append(SIDBUtil.SQL2Str(targetDate,"::date")).append(",CASE WHEN store.init2 IS NOT NULL AND store.init<store.init2 AND (ship.out IS NULL OR ship.out<store.init2) THEN store.init2");
    lSqlBuf.append(" WHEN ship.out IS NOT NULL AND store.init<ship.out THEN ship.out ELSE store.init END) AS interv1");
    //対象日と最終仕入日、最終販売日の遅い方とのインターバル（総滞留期間）
    lSqlBuf.append(",age(").append(SIDBUtil.SQL2Str(targetDate,"::date")).append(",CASE WHEN ship.out IS NOT NULL AND store.init<ship.out THEN ship.out ELSE store.init END) AS interv2");
    //仕入、買取、入庫番号１の有効データで一番大きい日付（最終仕入日）
    //仕入、買取、評価替え、入庫番号１の有効データで一番大きい日付（最終評価替え日ベース１）
    lSqlBuf.append(" FROM (SELECT individualcode,MAX(CASE WHEN storetype<>'10' THEN (CASE WHEN TO_CHAR(initdatetime,'dd')='01' THEN initdatetime+'1 days'::interval ELSE initdatetime END) ELSE NULL END) AS init,MAX(CASE WHEN TO_CHAR(initdatetime,'dd')='01' THEN initdatetime+'1 days'::interval ELSE initdatetime END) AS init2 FROM storehistorytbl WHERE storenumber='1' OR (storetype IN ('0','1','10') AND delflg IN('1','4')) GROUP BY individualcode) store");
    //販売区分の有効データで一番大きい日付（最終販売日）
    //販売、評価替えの有効データで一番大きい日付（最終評価替え日ベース２）
    lSqlBuf.append(" LEFT OUTER JOIN (SELECT individualcode,MAX(CASE WHEN shiptype='0' THEN (CASE WHEN TO_CHAR(initdatetime,'dd')='01' THEN initdatetime+'1 days'::interval ELSE initdatetime END) ELSE NULL END) AS out,MAX(CASE WHEN TO_CHAR(initdatetime,'dd')='01' THEN initdatetime+'1 days'::interval ELSE initdatetime END) AS out2 FROM shiphistorytbl WHERE shiptype IN ('0','10') AND delflg IN ('1','4') GROUP BY individualcode) ship");
    lSqlBuf.append(" ON store.individualcode=ship.individualcode) vw ");
    
    lSqlBuf.append("WHERE a.individualcode=i.individualcode AND a.individualcode=vw.individualcode AND i.individualcode=s.individualcode ");
    lSqlBuf.append(" AND r.branchcode=s.branchcode AND s.amount>0 AND a.consigngoodsflg='0' AND a.amountflg='1' ");
    if (SIUtil.isNotNull(ioExcel.getSegmentFlg())){
      if (ioExcel.getSegmentFlg().equals("0")) {//中古
        lSqlBuf.append(" AND substr(a.individualcode,0,2) NOT IN ('L','W','H','V','N') AND i.usednewflg NOT IN ('1','4','6','7') ");
      } else if (ioExcel.getSegmentFlg().equals("1")) {//新品
        lSqlBuf.append(" AND i.usednewflg IN ('1','6','7') ");
      } else if (ioExcel.getSegmentFlg().equals("2")) {//化粧品
        lSqlBuf.append(" AND substr(a.individualcode,0,2) IN ('L','W','H','V','N') ");
      } else if (ioExcel.getSegmentFlg().equals("3")) {//展示品
        lSqlBuf.append(" AND i.usednewflg = '4' ");
      }
    }
    if (SIUtil.isNotNull(ioExcel.getInterval())) lSqlBuf.append(" AND vw.interv2 >= ").append(SIDBUtil.SQL2Str(ioExcel.getInterval(),"::interval"));
    if (SIUtil.isNotNull(ioExcel.getBranchCode())) lSqlBuf.append(" AND r.branchcode=").append(SIDBUtil.SQL2Str(ioExcel.getBranchCode()));
    lSqlBuf.append(" ORDER BY s.branchcode,a.individualcode");
    
    // SQL文の設定
    lCsv.setSqlStatement(lSqlBuf.toString());
    
    // タイトルの設定
    lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_DEADSTOCK_LIST_INX));
    
    // CSVファイルの出力
    lCsv.execute(lConnection, response);
  }
  
  private void produceActiveUserListCSVFile(HttpServletResponse response, Connection lConnection, UIIOExcelListCond ioExcel) {
    // CSVファイル名の作成
    String lCsvFileName = SICSVConf.getCsvFileName(SICSVConf.SICSV_ACTIVEUSER_LIST_INX);
    SICSVWrite lCsv = new SICSVWrite(lCsvFileName);
    
    // SQL文の設定
    lCsv.setSqlStatement("SELECT * FROM activeuservw ORDER BY season");
    
    // タイトルの設定
    lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_ACTIVEUSER_LIST_INX));
    
    // CSVファイルの出力
    lCsv.execute(lConnection, response);
  }
  
  private void produceNoDeliveryListCSVFile(HttpServletResponse response, Connection lConnection, UIIOExcelListCond ioExcel) {
    // CSVファイル名の作成
    String lCsvFileName = SICSVConf.getCsvFileName(SICSVConf.SICSV_NODELIVERY_LIST_INX);
    SICSVWrite lCsv = new SICSVWrite(lCsvFileName);
    
    // SQL文の設定
    lCsv.setSqlStatement("SELECT * FROM nodeliverylistvw ORDER BY ordercode,individualcode");
    
    // タイトルの設定
    lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_NODELIVERY_LIST_INX));
    
    // CSVファイルの出力
    lCsv.execute(lConnection, response);
  }
  
  private void produceNoPaymentListCSVFile(HttpServletResponse response, Connection lConnection, UIIOExcelListCond ioExcel) {
    // CSVファイル名の作成
    String lCsvFileName = SICSVConf.getCsvFileName(SICSVConf.SICSV_NOPAYMENT_LIST_INX);
    SICSVWrite lCsv = new SICSVWrite(lCsvFileName);
    
    // SQL文の設定
    lCsv.setSqlStatement("SELECT * FROM nopaymentlistvw ORDER BY branchcode,ordercode");
    
    // タイトルの設定
    lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_NOPAYMENT_LIST_INX));
    
    // CSVファイルの出力
    lCsv.execute(lConnection, response);
  }
}