/**
 * Copyright (c) 2003-2004 System Integrator Corporation.
 *                 All Rights Reserved.
 */
package jp.co.sint.beans.front;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import javax.servlet.http.HttpServletRequest;

import jp.co.sint.basic.SIFrontBasic;
import jp.co.sint.basic.SIOrderHistory;
import jp.co.sint.basic.SIUserInfo;
import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIDBMultiConf;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.tools.SICheckDataConf;
import jp.co.sint.tools.SICheckValid;
import jp.co.sint.tools.SICustomError;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIURLMap;
import jp.co.sint.tools.SIUtil;
import jp.co.sint.tools.SIURLParameter;//7.1.1 ST0236 追加

import org.apache.log4j.Category;

/**
 * @version $Id: UIShippmentListCond.java,v 1.0 2003/09/19 Exp $
 * @author  asakura
 * <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>
 * asakura        2003/09/19 11:06:28  Original
 */
public class UIOrderHistory extends SIFrontBasic{
  //ログ用のインスタンスの生成
  private static Category log=Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  //ショップコード
  private String shopCode ="";
  
  //受注番号(キャンセル用)
  private String cancelOrderCodeHid = "";
  
  //ページ番号
  private String pageNumberTxt="1";
  
  public UIOrderHistory(){}
  
  public UIOrderHistory(HttpServletRequest lRequest,SIURLParameter lUrlParam){//7.1.1 ST0236 修正
    init(lRequest,lUrlParam);//7.1.1 ST0236 修正
  }
  
  //setter of ショップコード
  public void setShopCode(String lShopCode){
    if (SIUtil.isNull(lShopCode)) lShopCode="";
    this.shopCode=SIUtil.changeTo(lShopCode.trim(),this.encode);
  }
  
  //setter of 受注番号(キャンセル用)
  public void setCancelOrderCodeHid(String lCancelOrderCodeHid){
    if (SIUtil.isNull(lCancelOrderCodeHid)) lCancelOrderCodeHid="";
    this.cancelOrderCodeHid=SIUtil.changeTo(lCancelOrderCodeHid.trim(),this.encode);
  }
  
  //setter of ページ番号
  public void setPageNumberTxt(String lPageNumberTxt){
    if (SIUtil.isNull(lPageNumberTxt))lPageNumberTxt="1";
    this.pageNumberTxt=lPageNumberTxt;
  }
  
  //getter of ショップコード
  public String getShopCode(){
    return this.shopCode;
  }
  
  //getter of 受注番号(キャンセル用)
  public String getCancelOrderCodeHid(){
    return this.cancelOrderCodeHid;
  }
  
  //getter of ページ番号
  public String getPageNumberTxt(){
    return this.pageNumberTxt;
  }
  
  /**
   * <b>init</b>
   * 入力したデータを基づいて、このbeansを設定します。
   * @param request クライアントからリクエスト
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  //7.1.1 ST0236 修正 ここから
  public void init(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    super.init(lRequest,lUrlParam);
    this.setShopCode((String)lUrlParam.getParam("shc"));
  }
  //7.1.1 ST0236 修正 ここまで
  
  /**
   * <b>initCancel</b>
   * 入力したデータを基づいて、このbeansを設定します。
   * @param request クライアントからリクエスト
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  //7.1.1 ST0236 修正 ここから
  public void initCancel(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    super.init(lRequest,lUrlParam);
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    this.setCancelOrderCodeHid((String)lUrlParam.getParam("cancelOrderCodeHid"));
  }
  //7.1.1 ST0236 修正 ここまで
  
  /**
   * <b>validate</b>
   * 入力したデータをチェックして、同時にSQLの条件文を作成します。
   * @param lRequest クライアントからのリクエスト
   * @return なし
   * @throws なし
   */
  public boolean validateCancel(HttpServletRequest lRequest,Connection lConnection){
    SICustomErrors errors=new SICustomErrors();
    StringBuffer lSqlBuf=new StringBuffer();
    
    //ご注文番号
    SICheckValid.checkValid(errors,"ご注文番号",this.getCancelOrderCodeHid(),SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
    if (SIUtil.isNotNull(getCancelOrderCodeHid())){
      SIUserInfo lUserInfo=SIHTMLUtil.getUserInfo(lRequest);//ログイン情報の取得
      //7.2.0 ST1030 修正 ここから
      lSqlBuf.append("SELECT aa.OrderCode,(SELECT Count(*) FROM ").append(SIConfig.SIVIEW_ORDER_DELIVERY_LATEST_NAME).append(" WHERE ShippmentDate IS NOT NULL AND OrderCode=aa.OrderCode) AS DeliveryCount ");
      lSqlBuf.append("FROM ").append(SIConfig.SIVIEW_ORDER_LATEST_NAME).append(" aa WHERE aa.OrderCode=").append(SIDBUtil.SQL2Str(getCancelOrderCodeHid()," "));
      //lSqlBuf.append("SELECT aa.OrderCode,(SELECT Count(*) FROM OrderDeliveryTbl WHERE ShippmentDate IS NOT NULL AND OrderCode=aa.OrderCode) AS DeliveryCount ");
      //lSqlBuf.append("FROM OrderTbl aa WHERE aa.OrderCode=").append(SIDBUtil.SQL2Str(getCancelOrderCodeHid()," "));
      //7.2.0 ST1030 修正 ここまで
      lSqlBuf.append("AND aa.CustCode=").append(SIDBUtil.SQL2Str(lUserInfo.getCustCode()," "));
      lSqlBuf.append("AND aa.Status=1 ");//1:受注
      log.debug("validateCancel:lSqlBuf="+lSqlBuf.toString());
      
      Statement lStatement=null;
      ResultSet lResultSet=null;
      
      try {
        lStatement=lConnection.createStatement();
        lResultSet=lStatement.executeQuery(lSqlBuf.toString());
        
        if (lResultSet.next()){
          String lDeliveryCount=lResultSet.getString("DeliveryCount");
          if (SIUtil.isNotNull(lDeliveryCount)&&Integer.parseInt(lDeliveryCount)>0){//出荷した
            errors.addError(new SICustomError("manager.app.access.disable"));
          }
        }else{
          errors.addError(new SICustomError("manager.app.access.disable"));
        }
      } catch (SQLException e1) {
        e1.printStackTrace();
        errors.addError(new SICustomError("manager.app.access.disable"));
      }finally{
        SIDBUtil.close(lStatement,lResultSet);
      }
    }
    
    if (!errors.isEmpty()) lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY,errors);
    else lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    
    return errors.isEmpty();
  }

  /**
   * <b>getCollection</b>
   * 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws なし
   */
  public Collection getCollection(Connection lConnection , SIUserInfo lUserInfo, HttpServletRequest request) throws SIDBAccessException{
    // EDBTG005-00 kamata mod start
    Collection lResultColl=null;
    if (SIURLMap.isSmartPhone(request)) {
        lResultColl = getCollectionForSP(lConnection ,lUserInfo ,0, request);
    } else {
        lResultColl = getCollection(lConnection ,lUserInfo ,0, request);
    }
    return lResultColl;
    // EDBTG005-00 kamata mod end
  }
  //EDBTG005-00 kamata add start
  public Collection getCollectionForSP(Connection lConnection , SIUserInfo lUserInfo , int Limit, HttpServletRequest request)  throws SIDBAccessException{
    Statement lStatement=null;
    ResultSet lResultSet=null;
    SIOrderHistory lBasic=new SIOrderHistory();
    StringBuffer lSqlBuf=new StringBuffer();
    Collection lResultColl=new ArrayList();
    boolean noDiscountDispFlg = false;
    
    StringBuffer lSqlBufBodySqlBuf=new StringBuffer();
    
    if (!lUserInfo.isLogin(request)){
        //ログイン情報が無い場合は検索をしない。
        return null;
    }else{
      if (SIConfig.NO_DISCOUNT_DISP_USERS!=null&&SIConfig.NO_DISCOUNT_DISP_USERS.length>0) {
        for (int i=0;i<SIConfig.NO_DISCOUNT_DISP_USERS.length;i++) {
          if (lUserInfo.getCustCode().equals(SIConfig.NO_DISCOUNT_DISP_USERS[i])) {
            noDiscountDispFlg=true;
            break;
          }
        }
      }
    }
    //基本のSQL
    lSqlBuf.append("SELECT ord.OrderCode, ord.Status, ord.InitDateTime, ord.totalOfPrice ");
    lSqlBuf.append(", ord.PayMethodName, ord.PayMentFlg, ord.ReceiptDate, ord.deliveryStatus, ord.paymentprice, ord.cmdtycode, ord.individualcode, trunc(sum(ord.amount) / count(ord.individualcode) ,0) as amount, c.cmdtyname ");
    lSqlBuf.append(", ord.SumOfDiscount ");
    lSqlBuf.append("  FROM ( ");
    lSqlBuf.append("SELECT a.OrderCode AS OrderCode ");
    lSqlBuf.append("     , a.Status AS Status ");
    lSqlBuf.append("     , a.InitDateTime AS InitDateTime ");
    lSqlBuf.append("     , ((a.fee + a.TotalOfPrice + a.TotalOfDeliveryFee) - a.setDiscount - a.SumOfDiscount - a.SumByPoint + COALESCE(a.discountFee,0) + COALESCE(a.discountDeliveryFee,0)) AS totalOfPrice ");
    lSqlBuf.append("     , a.PayMethodName AS PayMethodName ");
    lSqlBuf.append("     , a.PayMentFlg AS PayMentFlg ");
    lSqlBuf.append("     , p.ReceiptDate AS ReceiptDate ");
    lSqlBuf.append("     , a.nodeliveryflg + a.deliveryedflg AS deliveryStatus ");
    lSqlBuf.append("     , p.paymentprice ");
    lSqlBuf.append("     , b.amount ");
    lSqlBuf.append("     , a.sumofdiscount ");
    lSqlBuf.append("     , CASE WHEN setcmdtycode is null then b.cmdtycode ELSE b.setcmdtycode END as cmdtycode ");
    lSqlBuf.append("     , CASE WHEN setcmdtycode is null then b.individualcode ELSE b.setindividualcode END as individualcode ");
    
    // 結合条件等のSQL
    lSqlBufBodySqlBuf.append("FROM ");
    lSqlBufBodySqlBuf.append(" (SELECT aa.ordercode, aa.paymethodname, aa.initdatetime, aa.paymentflg, aa.fee, aa.sumofdiscount, aa.sumbypoint, aa.status, aa.custcode, aa.discountfee, aa.discountdeliveryfee, aa.setdiscount ");
    lSqlBufBodySqlBuf.append(" , (SELECT sum(dt.price * dt.amount) AS sum FROM orderdetailtbl dt WHERE dt.ordercode = aa.ordercode AND dt.orderbranchcode = aa.orderbranchcode) AS totalofprice ");
    lSqlBufBodySqlBuf.append(" , (SELECT sum(dv.deliveryfee) AS sum FROM orderdeliverytbl dv WHERE dv.ordercode = aa.ordercode AND dv.orderbranchcode = aa.orderbranchcode) AS totalofdeliveryfee ");
    lSqlBufBodySqlBuf.append(" , CASE WHEN (EXISTS ( SELECT gg.ordercode FROM orderdetailtbl gg WHERE gg.ordercode = aa.ordercode AND gg.orderbranchcode = aa.orderbranchcode AND gg.shippmentdate IS NOT NULL AND gg.shippmentdate<=current_date)) THEN 1 ");
    lSqlBufBodySqlBuf.append("   ELSE 0 END AS deliveryedflg ");
    lSqlBufBodySqlBuf.append(" , CASE WHEN (EXISTS ( SELECT gg.ordercode FROM orderdetailtbl gg WHERE gg.ordercode = aa.ordercode AND gg.orderbranchcode = aa.orderbranchcode AND (gg.shippmentdate IS NULL OR gg.shippmentdate>current_date))) THEN 2 ");
    lSqlBufBodySqlBuf.append("   ELSE 0 END AS nodeliveryflg ");
    lSqlBufBodySqlBuf.append(" , aa.branchcode ");
    lSqlBufBodySqlBuf.append(" FROM orderlatestvw aa) AS a ");
    lSqlBufBodySqlBuf.append(" LEFT OUTER JOIN (");
    lSqlBufBodySqlBuf.append("   SELECT p.OrderCode,sum(p.paymentprice) AS paymentprice,max(p.paymentdate) AS ReceiptDate ");
    lSqlBufBodySqlBuf.append("   FROM paymenttbl p,orderlatestvw h ");
    lSqlBufBodySqlBuf.append("   WHERE p.status=0 AND p.paymentdate IS NOT NULL AND p.paymentdate<=current_date AND h.OrderCode=p.OrderCode ");
    lSqlBufBodySqlBuf.append("   AND h.CustCode=").append(SIDBUtil.SQL2Str(lUserInfo.getCustCode()," "));
    lSqlBufBodySqlBuf.append("   GROUP BY p.OrderCode) p ");
    lSqlBufBodySqlBuf.append("ON a.ordercode=p.ordercode ");
    lSqlBufBodySqlBuf.append("INNER JOIN orderdetailvw b ON a.ordercode = b.ordercode ");
    
    String feeRulePrice1 = SIDBUtil.getFirstData(lConnection, "SELECT price FROM feerulemtbl WHERE deliverytypecode='1'");
    if(SIUtil.isNull(feeRulePrice1)) feeRulePrice1="0";
    lSqlBufBodySqlBuf.append("WHERE a.CustCode=").append(SIDBUtil.SQL2Str(lUserInfo.getCustCode()," "));
    lSqlBufBodySqlBuf.append("AND (a.BranchCode<>'17' OR a.BranchCode IS NULL) ");
    
    // 商品の数を求める
    StringBuffer lCountSqlBuf = new StringBuffer("SELECT Count(*) ");
    lCountSqlBuf.append(" FROM ( ");
    lCountSqlBuf.append("SELECT distinct a.OrderCode AS OrderCode ");
    lCountSqlBuf.append("      , case when setcmdtycode is null then b.cmdtycode else b.setcmdtycode end as cmdtycode ");
    lCountSqlBuf.append("      , case when setcmdtycode is null then b.individualcode else b.setindividualcode end as individualcode ");
    lCountSqlBuf.append(lSqlBufBodySqlBuf);
    lCountSqlBuf.append(" ) cnt ");
    log.debug("lCountSqlBuf="+lCountSqlBuf.toString());
    
    int lRecordCount = Integer.parseInt(SIDBUtil.getFirstData(lConnection, lCountSqlBuf.toString()));
    int lPageSize = SIConfig.SIPAGE_SIZE_MOBILE;// ページサイズ
    int lPageNumber = this.getPageNumer();// ページ番号
    int lMaxPage = lRecordCount / lPageSize;
    if (lRecordCount % lPageSize > 0) lMaxPage++;
    if (lPageNumber > lMaxPage) lPageNumber = lMaxPage;
    
    // 開始レコードの番号の設定
    int lFromInx = (lPageNumber - 1) * lPageSize;
    // 終止のレコード番号の設定
    int lToInx = lFromInx + lPageSize - 1;
    if (lFromInx < 0) lFromInx = 0;
    if (lToInx < 0) lToInx = 0;
    
    lSqlBufBodySqlBuf.append("ORDER BY a.InitDateTime DESC ");
    
    lSqlBuf.append(lSqlBufBodySqlBuf);
    lSqlBuf.append(") AS ord ");
    lSqlBuf.append("LEFT OUTER JOIN cmdtyunittbl c ON ord.cmdtycode = c.cmdtycode AND ord.individualcode = c.individualcode ");
    lSqlBuf.append("GROUP BY ord.OrderCode, ord.Status, ord.InitDateTime, ord.totalOfPrice , ord.PayMethodName, ord.PayMentFlg");
    lSqlBuf.append(", ord.ReceiptDate, ord.deliveryStatus, ord.paymentprice, ord.cmdtycode, ord.individualcode, c.cmdtyname, ord.SumOfDiscount  ");
    
    lSqlBuf.append("ORDER BY ord.InitDateTime DESC ");
    lSqlBuf.append(" LIMIT ").append(lPageSize);
    if (lFromInx>0) lSqlBuf.append(" OFFSET ").append(lFromInx);
    
    log.debug("lSqlBuf="+lSqlBuf.toString());
    
    //実行
    try{
      lStatement=lConnection.createStatement();
      lResultSet=lStatement.executeQuery(lSqlBuf.toString());
      
      for (int jj = 0; jj < lFromInx; jj++)
        lResultColl.add(null);
      
      //商品レコードのセットの作成
      while (lResultSet.next()){
        lBasic=new SIOrderHistory();
        lBasic.setEncode(SIConfig.SIENCODE_NONE);
        lBasic.setOrderCode(lResultSet.getString("orderCode"));//受注番号
        lBasic.setPayMethodName(lResultSet.getString("payMethodName"));//支払方法名称
        lBasic.setInitDateTime(SIDBUtil.getDate(lResultSet.getTimestamp("initDateTime")));//受注日時
        lBasic.setPaymentFlg(lResultSet.getString("paymentFlg"));//先後払いフラグ
        lBasic.setTotalOfPrice(lResultSet.getString("totalOfPrice"));//合計価格
        if (lBasic.getTotalOfPrice().equals(lResultSet.getString("paymentprice")))
          lBasic.setReceiptDate(SIDBUtil.getDate(lResultSet.getTimestamp("receiptDate")));//入金日
        lBasic.setStatus(lResultSet.getString("status"));//受注状態
        //出荷状況
        String deliveryStatus = lResultSet.getString("deliveryStatus");
        
        if("1".equals(deliveryStatus)){
          lBasic.setShippmentStatus("2");//出荷済み
        }else if("2".equals(deliveryStatus)){
          lBasic.setShippmentStatus("0");//未出荷
        }else{
          lBasic.setShippmentStatus("1");//一部出荷済み
        }
        
        lBasic.setCmdtyCode(lResultSet.getString("cmdtycode"));//商品コード
        lBasic.setIndividualCode(lResultSet.getString("individualcode"));//在庫コード
        lBasic.setCmdtyname(lResultSet.getString("cmdtyname"));//商品名
        lBasic.setAmount(lResultSet.getString("amount"));//受注数
        
        if (noDiscountDispFlg) {
          lBasic.setTotalOfPrice(SIUtil.add(lResultSet.getString("totalOfPrice"), lResultSet.getString("sumOfDiscount")));
        }
        
        lResultColl.add(lBasic);
      }
      
      for (int jj = lFromInx + lPageSize; jj < lRecordCount; jj++)
        lResultColl.add(null);
      
    }catch(Exception ex){
      throw new SIDBAccessException(ex);
    }finally{
      SIDBUtil.close(lResultSet,lStatement);
    }
    return lResultColl;
  }
  //EDBTG005-00 kamata add end
  public Collection getCollection(Connection lConnection , SIUserInfo lUserInfo , int Limit, HttpServletRequest request)  throws SIDBAccessException{
    Statement lStatement=null;
    ResultSet lResultSet=null;
    SIOrderHistory lBasic=new SIOrderHistory();
    StringBuffer lSqlBuf=new StringBuffer();
    Collection lResultColl=new ArrayList();
    boolean noDiscountDispFlg = false;
    
    //基本のSQL
    if (lUserInfo.isLogin(request)){
      if (SIConfig.NO_DISCOUNT_DISP_USERS!=null&&SIConfig.NO_DISCOUNT_DISP_USERS.length>0) {
        for (int i=0;i<SIConfig.NO_DISCOUNT_DISP_USERS.length;i++) {
          if (lUserInfo.getCustCode().equals(SIConfig.NO_DISCOUNT_DISP_USERS[i])) {
            noDiscountDispFlg=true;
            break;
          }
        }
      }
      lSqlBuf.append("SELECT a.OrderCode AS OrderCode ");
      lSqlBuf.append("      , a.Status AS Status ");
      lSqlBuf.append("      , a.InitDateTime AS InitDateTime ");
      lSqlBuf.append("      , ((a.fee + a.TotalOfPrice + a.TotalOfDeliveryFee) - a.setDiscount - a.SumOfDiscount - a.SumByPoint + COALESCE(a.discountFee,0) + COALESCE(a.discountDeliveryFee,0)) AS totalOfPrice ");
      lSqlBuf.append("      , a.PayMethodName AS PayMethodName ");
      lSqlBuf.append("      , a.PayMentFlg AS PayMentFlg ");
      lSqlBuf.append("      , p.ReceiptDate AS ReceiptDate ");
      lSqlBuf.append("      , a.nodeliveryflg + a.deliveryedflg AS deliveryStatus ");
      lSqlBuf.append("      , p.paymentprice ");
      lSqlBuf.append("      , a.sumofdiscount ");
      lSqlBuf.append("FROM ");
      lSqlBuf.append(" (SELECT aa.ordercode, aa.paymethodname, aa.initdatetime, aa.paymentflg, aa.fee, aa.sumofdiscount, aa.sumbypoint, aa.status, aa.custcode, aa.discountfee, aa.discountdeliveryfee, aa.setdiscount ");
      lSqlBuf.append(" , (SELECT sum(dt.price * dt.amount) AS sum FROM orderdetailtbl dt WHERE dt.ordercode = aa.ordercode AND dt.orderbranchcode = aa.orderbranchcode) AS totalofprice ");
      lSqlBuf.append(" , (SELECT sum(dv.deliveryfee) AS sum FROM orderdeliverytbl dv WHERE dv.ordercode = aa.ordercode AND dv.orderbranchcode = aa.orderbranchcode) AS totalofdeliveryfee ");
      lSqlBuf.append(" , CASE WHEN (EXISTS ( SELECT gg.ordercode FROM orderdetailtbl gg WHERE gg.ordercode = aa.ordercode AND gg.orderbranchcode = aa.orderbranchcode AND gg.shippmentdate IS NOT NULL AND gg.shippmentdate<=current_date)) THEN 1 ");
      lSqlBuf.append("   ELSE 0 END AS deliveryedflg ");
      lSqlBuf.append(" , CASE WHEN (EXISTS ( SELECT gg.ordercode FROM orderdetailtbl gg WHERE gg.ordercode = aa.ordercode AND gg.orderbranchcode = aa.orderbranchcode AND (gg.shippmentdate IS NULL OR gg.shippmentdate>current_date))) THEN 2 ");
      lSqlBuf.append("   ELSE 0 END AS nodeliveryflg ");
      lSqlBuf.append(" , aa.branchcode ");
      lSqlBuf.append(" FROM orderlatestvw aa) AS a ");
      lSqlBuf.append(" LEFT OUTER JOIN (");
      lSqlBuf.append("   SELECT p.OrderCode,sum(p.paymentprice) AS paymentprice,max(p.paymentdate) AS ReceiptDate ");
      lSqlBuf.append("   FROM paymenttbl p,orderlatestvw h ");
      lSqlBuf.append("   WHERE p.status=0 AND p.paymentdate IS NOT NULL AND p.paymentdate<=current_date AND h.OrderCode=p.OrderCode ");
      lSqlBuf.append("   AND h.CustCode=").append(SIDBUtil.SQL2Str(lUserInfo.getCustCode()," "));
      lSqlBuf.append("   GROUP BY p.OrderCode) p ");
      lSqlBuf.append("ON a.ordercode=p.ordercode ");
      lSqlBuf.append("WHERE a.CustCode=").append(SIDBUtil.SQL2Str(lUserInfo.getCustCode()," "));
      lSqlBuf.append("AND (a.BranchCode<>'17' OR a.BranchCode IS NULL) ");
      lSqlBuf.append("ORDER BY a.InitDateTime DESC ");
      if (Limit>0) {
        lSqlBuf.append("LIMIT ").append(Limit);
      }
    }else{
      //ログイン情報が無い場合は検索をしない。
      return null;
    }
    
    log.debug("lSqlBuf="+lSqlBuf.toString());
    //実行
    try{
      lStatement=lConnection.createStatement();
      lResultSet=lStatement.executeQuery(lSqlBuf.toString());
      
      //商品レコードのセットの作成
      while (lResultSet.next()){
        lBasic=new SIOrderHistory();
        lBasic.setEncode(SIConfig.SIENCODE_NONE);
        lBasic.setOrderCode(lResultSet.getString("orderCode"));//受注番号
        lBasic.setPayMethodName(lResultSet.getString("payMethodName"));//支払方法名称
        lBasic.setInitDateTime(SIDBUtil.getDateTime(lResultSet.getTimestamp("initDateTime")));//受注日時
        lBasic.setPaymentFlg(lResultSet.getString("paymentFlg"));//先後払いフラグ
        lBasic.setTotalOfPrice(lResultSet.getString("totalOfPrice"));//合計価格
        if (lBasic.getTotalOfPrice().equals(lResultSet.getString("paymentprice")))
          lBasic.setReceiptDate(SIDBUtil.getDate(lResultSet.getTimestamp("receiptDate")));//入金日
        lBasic.setStatus(lResultSet.getString("status"));//受注状態
        //出荷状況
        String deliveryStatus = lResultSet.getString("deliveryStatus");
        
        if("1".equals(deliveryStatus)){
          lBasic.setShippmentStatus("2");//出荷済み
        }else if("2".equals(deliveryStatus)){
          lBasic.setShippmentStatus("0");//未出荷
        }else{
          lBasic.setShippmentStatus("1");//一部出荷済み
        }
        
        if (noDiscountDispFlg) {
          lBasic.setTotalOfPrice(SIUtil.add(lResultSet.getString("totalOfPrice"), lResultSet.getString("sumOfDiscount")));
        }
        
        lResultColl.add(lBasic);
      }
    }catch(Exception ex){
      throw new SIDBAccessException(ex);
    }finally{
      SIDBUtil.close(lResultSet,lStatement);
    }
    return lResultColl;
  }
  
  /*
  public Collection getCollection(Connection lConnection , SIUserInfo lUserInfo , int Limit)  throws SIDBAccessException{
    Statement lStatement=null;
    ResultSet lResultSet=null;
    SIOrderHistory lBasic=new SIOrderHistory();
    StringBuffer lSqlBuf=new StringBuffer();
    Collection lResultColl=new ArrayList();
    
    //基本のSQL
    if (lUserInfo.isLogin()){
      lSqlBuf.append("SELECT a.OrderCode AS OrderCode ");
      lSqlBuf.append("     , a.Status AS Status ");
      lSqlBuf.append("     , a.InitDateTime AS InitDateTime ");
      lSqlBuf.append("     , (a.TotalOfFee + a.TotalOfPrice + a.TotalOfDeliveryFee - a.SumOfDiscount - a.SumByPoint + COALESCE(a.discountFee,0) + COALESCE(a.discountDeliveryFee,0)) AS totalOfPrice ");
      lSqlBuf.append("     , a.PayMethodName AS PayMethodName ");
      lSqlBuf.append("     , a.PayMentFlg AS PayMentFlg ");
      lSqlBuf.append("     , p.ReceiptDate AS ReceiptDate ");
      lSqlBuf.append("     , a.nodeliveryflg + a.deliveryedflg AS deliveryStatus ");
      lSqlBuf.append("     , p.paymentprice ");
      lSqlBuf.append("FROM OrderSumVW AS a ");
      lSqlBuf.append("LEFT OUTER JOIN (");
      lSqlBuf.append("  SELECT p.OrderCode ");
      lSqlBuf.append("       ,sum(p.paymentprice) AS paymentprice");
      lSqlBuf.append("       ,max(p.paymentdate) AS ReceiptDate ");
      lSqlBuf.append("  FROM paymenttbl p,orderlatestvw h ");
      lSqlBuf.append("  WHERE p.status=0 ");
      lSqlBuf.append("    AND p.paymentdate IS NOT NULL ");
      lSqlBuf.append("    AND p.paymentdate<=current_date ");
      lSqlBuf.append("    AND h.OrderCode=p.OrderCode ");
      lSqlBuf.append("    AND h.CustCode=").append(SIDBUtil.SQL2Str(lUserInfo.getCustCode()," "));
      lSqlBuf.append("  GROUP BY p.OrderCode) p ");
      lSqlBuf.append("ON a.ordercode=p.ordercode ");
      lSqlBuf.append("WHERE a.CustCode=").append(SIDBUtil.SQL2Str(lUserInfo.getCustCode()," "));
      lSqlBuf.append("ORDER BY a.InitDateTime DESC ");
      lSqlBuf.append("LIMIT ").append(Limit);
    }else{
      //ログイン情報が無い場合は検索をしない。
      return null;
    }
    
    log.debug("lSqlBuf="+lSqlBuf.toString());
    //実行
    try{
      lStatement=lConnection.createStatement();
      lResultSet=lStatement.executeQuery(lSqlBuf.toString());
      
      //商品レコードのセットの作成
      while (lResultSet.next()){
        lBasic=new SIOrderHistory();
        lBasic.setEncode(SIConfig.SIENCODE_NONE);
        lBasic.setOrderCode(lResultSet.getString("orderCode"));//受注番号
        lBasic.setPayMethodName(lResultSet.getString("payMethodName"));//支払方法名称
        lBasic.setInitDateTime(SIDBUtil.getDateTime(lResultSet.getTimestamp("initDateTime")));//受注日時
        lBasic.setPaymentFlg(lResultSet.getString("paymentFlg"));//先後払いフラグ
        lBasic.setTotalOfPrice(lResultSet.getString("totalOfPrice"));//合計価格
        if (lBasic.getTotalOfPrice().equals(lResultSet.getString("paymentprice")))
          lBasic.setReceiptDate(SIDBUtil.getDate(lResultSet.getTimestamp("receiptDate")));//入金日
        lBasic.setStatus(lResultSet.getString("status"));//受注状態
        //出荷状況
        String deliveryStatus = lResultSet.getString("deliveryStatus");
        
        if("1".equals(deliveryStatus)){
          lBasic.setShippmentStatus("2");//出荷済み
        }else if("2".equals(deliveryStatus)){
          lBasic.setShippmentStatus("0");//未出荷
        }else{
          lBasic.setShippmentStatus("1");//一部出荷済み
        }
        
        lResultColl.add(lBasic);
      }
    }catch(Exception ex){
      throw new SIDBAccessException(ex);
    }finally{
      SIDBUtil.close(lResultSet,lStatement);
    }
    return lResultColl;
  }
  */
}