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

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Enumeration;
import java.util.HashMap;

import javax.servlet.http.HttpServletRequest;

import jp.co.sint.basic.SIBasic;
import jp.co.sint.basic.SILogin;
import jp.co.sint.basic.SIReserveOrder;
import jp.co.sint.config.SIConfig;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.tools.SICheckDataConf;
import jp.co.sint.tools.SICheckUtil;
import jp.co.sint.tools.SICheckValid;
import jp.co.sint.tools.SICustomError;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIErrorFactory;//7.1.1 ST0155 追加
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIUtil;
import jp.co.sint.tools.SIURLParameter;//7.1.1 ST0236 追加

import org.apache.log4j.Category;

/**
 * @version $Id: UIReserveAssignListCond.java,v 1.0 2004/01/29 Exp $
 * @author  Jinwang Chen
 * <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>
 * Jinwang Chen   2004/01/29 15:09:02  Original
 */
public class UIReserveAssignListCond extends SIBasic {
  //ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  //商品コード
  private String cmdtyCode = "";
  
  //商品名
  private String cmdtyName = "";
  
  //在庫コード
  private String individualCode ="";//7.3.0 PI-NES0501 追加
  
  //エレメント1
  private String elementCode1 = "";
  
  //エレメント2
  private String elementCode2 = "";
  
  private String mailTypeTxt = "";
  
  private String[] reserveCodeChk = new String[0];
  
  private String[] reserveBranch = new String[0];
  
  private HashMap assignAmountMap = new HashMap();
  
  //ショップコード
  private String shopCode = "";
  
  //規格1
  private String stndrdCode1 = "";
  
  //規格2
  private String stndrdCode2 = "";
  
  //
  private String stockAmount="0";
  
  private String totalOfAssignAmount="0";
  
  //予約一覧ページ保存用
  private String pageNumberTxt_Reserve="1";
  
  private String reserveFlg = "";
  
  //登録ユーザ名
  private String initUserName = "";
  
  //予約番号指定
  private String reserveCodeTxt = "";
  
  public UIReserveAssignListCond() {
  }
  
  //getter of 商品コード
  public String getCmdtyCode() {
    return this.cmdtyCode;
  }
  
  //getter of 商品名
  public String getCmdtyName() {
    return this.cmdtyName;
  }
  
  //getter of エレメント1
  public String getElementCode1() {
    return this.elementCode1;
  }
  
  //getter of エレメント2
  public String getElementCode2() {
    return this.elementCode2;
  }
  
  //getter of エレメント2
  public String getMailTypeTxt() {
    return this.mailTypeTxt;
  }
  
  public String[] getReserveCodeChk() {
    return this.reserveCodeChk;
  }
  
  public String[] getReserveBranch() {
    return this.reserveBranch;
  }
  
  public String getAssignAmount(String lName) {
    return (String)assignAmountMap.get(lName);
  }
  
  //getter of ショップコード
  public String getShopCode() {
    return this.shopCode;
  }
  
  //getter of 規格1
  public String getStndrdCode1() {
    return this.stndrdCode1;
  }
  
  //getter of 規格2
  public String getStndrdCode2() {
    return this.stndrdCode2;
  }
  
  public String getStockAmount(){
    return this.stockAmount;
  }
  
  public String getTotalOfAssignAmount(){
    return this.totalOfAssignAmount;
  }
  
  public String getPageNumberTxt_Reserve(){
    return this.pageNumberTxt_Reserve;
  }
  
  public String getInitUserName(){
    return this.initUserName;
  }
  
  public String getReserveCodeTxt(){
    return this.reserveCodeTxt;
  }
  
  //7.3.0 PI-NES0501 追加 ここから
  //getter of 在庫コード
  public String getIndividualCode(){
    return this.individualCode;
  }
  
  //setter of 在庫コード
  public void setIndividualCode(String lIndividualCode) {
    if (SIUtil.isNull(lIndividualCode))lIndividualCode = "";
    this.individualCode = SIUtil.changeTo(lIndividualCode.trim(), this.encode);
  }
  //7.3.0 PI-NES0501 追加 ここまで
  
  //setter of 商品コード
  public void setCmdtyCode(String lCmdtyCode) {
    if (SIUtil.isNull(lCmdtyCode))lCmdtyCode = "";
    this.cmdtyCode = SIUtil.changeTo(lCmdtyCode.trim(), this.encode);
  }
  
  //setter of 商品名
  public void setCmdtyName(String lCmdtyName) {
    if (SIUtil.isNull(lCmdtyName))lCmdtyName = "";
    this.cmdtyName = SIUtil.changeTo(lCmdtyName.trim(), this.encode);
  }
  
  //setter of エレメント1
  public void setElementCode1(String lElementCode1) {
    if (SIUtil.isNull(lElementCode1))lElementCode1 = "";
    this.elementCode1 =SIUtil.changeTo(lElementCode1.trim(), this.encode);
  }
  
  //setter of エレメント2
  public void setElementCode2(String lElementCode2) {
    if (SIUtil.isNull(lElementCode2))lElementCode2 = "";
    this.elementCode2 =SIUtil.changeTo(lElementCode2.trim(), this.encode);
  }
  
  //setter of エレメント2
  public void setMailTypeTxt(String lMailTypeTxt) {
    if (SIUtil.isNull(lMailTypeTxt))lMailTypeTxt = "";
    this.mailTypeTxt = SIUtil.changeTo(lMailTypeTxt.trim(), this.encode);
  }
  
  public void setReserveCodeChk(String[] lReserveCodeChk) {
    if (lReserveCodeChk == null)lReserveCodeChk = new String[0];
    this.reserveCodeChk = lReserveCodeChk;
  }
  
  public void setReserveBranch(String[] lReserveBranch) {
    if (lReserveBranch == null)lReserveBranch = new String[0];
    this.reserveBranch = lReserveBranch;
  }
  
  public void setReserveBranch(String lReserveBranch,int index) {
    this.reserveBranch[index] = lReserveBranch;
  }
  
  public void setAssignAmount(String lName, String lValue) {
    if (SIUtil.isNull(lValue))lValue = "";
    else lValue = SIUtil.changeTo(lValue.trim(), this.encode);
    assignAmountMap.put(lName, lValue);
  }
  
  //setter of ショップコード
  public void setShopCode(String lShopCode) {
    if (SIUtil.isNull(lShopCode))lShopCode = "";
    this.shopCode = SIUtil.changeTo(lShopCode.trim(), this.encode);
  }
  
  //setter of 規格1
  public void setStndrdCode1(String lStndrdCode1) {
    if (SIUtil.isNull(lStndrdCode1))lStndrdCode1 = "";
    this.stndrdCode1 = SIUtil.changeTo(lStndrdCode1.trim(), this.encode);
  }
  
  //setter of 規格2
  public void setStndrdCode2(String lStndrdCode2) {
    if (SIUtil.isNull(lStndrdCode2))lStndrdCode2 = "";
    this.stndrdCode2 = SIUtil.changeTo(lStndrdCode2.trim(), this.encode);
  }
  
  public void setStockAmount(String lStockAmount) {
    if (SIUtil.isNull(lStockAmount))lStockAmount = "0";
    this.stockAmount = SIUtil.changeTo(lStockAmount.trim(), this.encode);
  }
  
  //
  public void setTotalOfAssignAmount(String lTotalOfAssignAmount) {
    if (SIUtil.isNull(lTotalOfAssignAmount))lTotalOfAssignAmount = "0";
    this.totalOfAssignAmount = SIUtil.changeTo(lTotalOfAssignAmount.trim(), this.encode);
  }
  
  public void setPageNumberTxt_Reserve(String PageNumberTxt_Reserve) {
    if (SIUtil.isNull(PageNumberTxt_Reserve))PageNumberTxt_Reserve = "1";
    this.pageNumberTxt_Reserve = SIUtil.changeTo(PageNumberTxt_Reserve.trim(), this.encode);
  }
  
  //getter of reserveFlg
  public String getReserveFlg() {
    return this.reserveFlg;
  }
  
  //setter of reserveFlg
  public void setReserveFlg(String reserveFlg) {
    if (SIUtil.isNull(reserveFlg)) reserveFlg = "";
    this.reserveFlg = SIUtil.changeTo(reserveFlg.trim(), this.encode);
  }
  
  public void setInitUserName(String initUserName) {
    if (SIUtil.isNull(initUserName)) initUserName = "";
    this.initUserName = SIUtil.changeTo(initUserName.trim(), this.encode);
  }
  
  public void setReserveCodeTxt(String reserveCodeTxt) {
    if (SIUtil.isNull(reserveCodeTxt)) reserveCodeTxt = "";
    this.reserveCodeTxt = SIUtil.changeTo(reserveCodeTxt.trim(), this.encode);
  }
  
  public void init(HttpServletRequest lRequest,Connection lConnection,SIURLParameter lUrlParam){//7.1.1 ST0236 修正
    SILogin lLogin=SIHTMLUtil.getLogin(lRequest);
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    //7.1.1 ST0236 修正 ここから
    super.init(lRequest,lUrlParam);
    if (lLogin.isShop()){
      this.setShopCode(lLogin.getMallShopCode());
    }else{
      this.setShopCode((String)lUrlParam.getParam("shopCode"));
    }
    this.setCmdtyCode((String)lUrlParam.getParam("cmdtyCode"));
    this.setIndividualCode((String)lUrlParam.getParam("individualCode"));//7.3.0 PI-NES0501 追加
    this.setStndrdCode1((String)lUrlParam.getParam("stndrdCode1"));
    this.setElementCode1((String)lUrlParam.getParam("elementCode1"));
    this.setStndrdCode2((String)lUrlParam.getParam("stndrdCode2"));
    this.setElementCode2((String)lUrlParam.getParam("elementCode2"));
    this.setMailTypeTxt((String)lUrlParam.getParam("mailTypeTxt"));
    this.setReserveCodeChk(lRequest.getParameterValues("reserveCodeChk"));
    this.setReserveFlg((String)lUrlParam.getParam("reserveFlg"));
    this.reserveBranch = new String[this.reserveCodeChk.length];
    for(int i=0;i<this.reserveCodeChk.length;i++){
      this.setReserveBranch((String)lUrlParam.getParam("reserveBranch"+this.reserveCodeChk[i]),i);
    }
    this.setInitUserName((String)lUrlParam.getParam("initUserName"));
    this.setReserveCodeTxt((String)lUrlParam.getParam("reserveCodeTxt"));
    this.setPageNumberTxt_Reserve((String)lUrlParam.getParam("pageNumberTxt_Reserve"));
    Enumeration lEnum = lRequest.getParameterNames();
    String lName ="";
    
    for (int ii=0;ii<getReserveCodeChk().length ;ii++){
      lName=SIConfig.SIPREFIX_ASSIGN_AMOUNT_NAME+SIConfig.SIWEBSHOPPING_TOKEN+getReserveCodeChk()[ii];
      setAssignAmount(lName,(String)lUrlParam.getParam(lName));
    }
    //7.1.1 ST0236 修正 ここまで
    initTotalOfAssignAmount(lConnection);
  }
  
  public boolean validate(HttpServletRequest lRequest,Connection lConnection){
    SICustomErrors errors=new SICustomErrors();
    SICustomError err;
    String lAssignValue="";
    SIReserveOrder lOrder=new SIReserveOrder();
    boolean hasData=false;
    String[][] stock=this.getStock(lConnection);
    int count=this.getStockCount(lConnection);
    
    if (getReserveCodeChk()==null || getReserveCodeChk().length ==0){
      errors.addError(new SICustomError("input.data.checkbox.require","登録"));
    }else{
      for (int ii=0;ii<getReserveCodeChk().length ;ii++){
        lAssignValue=getAssignAmount(SIConfig.SIPREFIX_ASSIGN_AMOUNT_NAME+SIConfig.SIWEBSHOPPING_TOKEN+getReserveCodeChk()[ii]);
        lOrder=new SIReserveOrder(getReserveCodeChk()[ii]);
        lOrder.reset(lConnection);
        if (lOrder.getReserveFlg().equals("0")||lOrder.getReserveFlg().equals("1")){
          hasData=true;
          if(SICheckValid.checkValid(errors,"予約番号「"+getReserveCodeChk()[ii]+"」の割当支店",getReserveBranch()[ii],SICheckDataConf.SICHECK_DATA_EMPTY_TYPE)){
            for (int jj=0;jj<count;jj++){
              if(stock[jj][0].equals(getReserveBranch()[ii])){
                stock[jj][1]=SIUtil.sub(stock[jj][1],lAssignValue);
                break;
              }
            }
          }
          /*
          //7.3.0 PI-NES0501 修正 ここから
          if (SICheckValid.checkValid(errors,"予約番号「"+getReserveCodeChk()[ii]+"」の割当数",lAssignValue,SICheckDataConf.SICHECK_DATA_EMPTY_TYPE))//必須チェック
          {
            //7.1.1 ST0162 修正 ここから
            SICheckValid.checkValid(errors,"予約番号「"+getReserveCodeChk()[ii]+"」の割当数",lAssignValue,SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
            if (SICheckUtil.isLess(lOrder.getAmount(),lAssignValue)){//予約数を上回る
              errors.addError(new SICustomError("input.data.less","予約番号「"+getReserveCodeChk()[ii]+"」の割当数","予約数"));
            }
            //7.1.1 ST0162 修正 ここまで
            if (lOrder.getAmountFlg().equals("1")){
              if (SICheckUtil.isLess(lOrder.getStockAmount(),lAssignValue)){//在庫数を上回る
                errors.addError(new SICustomError("input.data.less","予約番号「"+getReserveCodeChk()[ii]+"」の割当数","メーカー在庫数"));
              }
            }
          }//7.3.0 PI-NES0501 修正 ここまで
          */
        }
      }
      if (!hasData)errors.addError(new SICustomError("input.data.checkbox.require","登録"));
      if (hasData){
        for (int kk=0;kk<count;kk++){
          if(Integer.parseInt(stock[kk][1])<0) errors.addError(new SICustomError("manager.message.error.reservebranch",stock[kk][2]));
        }
      }
    }
    
    if (!errors.isEmpty()) lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY,errors);
    return errors.isEmpty();
  }
  
  /**
   * <b>validate</b>
   * 入力したデータをチェックして、同時にSQLの条件文を作成します。
   * @param lRequest クライアントからのリクエスト
   * @return なし
   * @throws なし
   */
  public boolean validateMail(HttpServletRequest lRequest,Connection lConnection){
    SICustomErrors errors=new SICustomErrors();
    
    String comment = "";
    if(this.getMailTypeTxt().equals(SIConfig.SIMAIL_TEMPLATE_RESERVE_ARRIVE)){
      comment = "入荷メールを送信";
    }else if(this.getMailTypeTxt().equals(SIConfig.SIMAIL_TEMPLATE_RESERVE_NO_ARRIVE)){
      comment = "未入荷メールを送信";
    }
    
    //チェックボックスのチェック
    if(this.getReserveCodeChk()==null || this.getReserveCodeChk().length==0){
      errors.addError(new SICustomError("input.data.checkbox.require",comment));
    }else{
      String lReserveCode="";
      SIReserveOrder lReserveOrder=new SIReserveOrder();
      boolean lResult=false;
      int ii=0;
      while (!lResult && ii<getReserveCodeChk().length ){
        lReserveCode=getReserveCodeChk()[ii];
        lReserveOrder=new SIReserveOrder(lReserveCode);
        lReserveOrder.reset(lConnection);
        if (lReserveOrder.getReserveFlg().equals("0")||lReserveOrder.getReserveFlg().equals("1"))lResult=true;
        ii++;
      }
      if (!lResult)errors.addError(new SICustomError("input.data.checkbox.require",comment));
    }
    
    if (!errors.isEmpty()) lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY,errors);
    else lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    
    return errors.isEmpty();
  }
  
  public boolean validateDelete(HttpServletRequest lRequest,Connection lConnection){
    SICustomErrors errors=new SICustomErrors();
    
    //チェックボックスのチェック
    if(this.getReserveCodeChk()==null || this.getReserveCodeChk().length==0){
      errors.addError(new SICustomError("input.data.checkbox.require","削除"));
    }else{
      String lReserveCode="";
      SIReserveOrder lReserveOrder=new SIReserveOrder();
      boolean lResult=false;
      int ii=0;
      while (!lResult && ii<getReserveCodeChk().length ){//7.1.1 ST0155 修正
        lReserveCode=getReserveCodeChk()[ii];
        lReserveOrder=new SIReserveOrder(lReserveCode);
        lReserveOrder.reset(lConnection);
        
        if(lReserveOrder.getReserveFlg().equals("2")
            ||lReserveOrder.getReserveFlg().equals("3")
            ||lReserveOrder.getReserveFlg().equals("5")) lResult=true;
        ii++;
      }
      //7.1.1 ST0155 修正 ここから
      if (lResult){
        errors.addError(new SICustomError("manager.message.error.reservecancel"));
      }else{
        lRequest.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.cancel"));
      }
      //7.1.1 ST0155 修正 ここまで
    }
    
    if (!errors.isEmpty()) lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY,errors);
    else lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    
    return errors.isEmpty();
  }
  
  /**
   * <b>initAssignAmount</b>
   * ある商品に対する割当される総数を計算します。
   * @param lConnection コネクション
   * @return なし
   * @throws なし
   */
  public void initTotalOfAssignAmount(Connection lConnection){
    StringBuffer lSqlBuf=new StringBuffer();
    lSqlBuf.append("SELECT SUM(AssignAmount) ");
    lSqlBuf.append("FROM ReserveOrderTbl ");
    lSqlBuf.append("WHERE ShopCode=").append(SIDBUtil.SQL2Str(getShopCode()));
    lSqlBuf.append(" AND CmdtyCode=").append(SIDBUtil.SQL2Str(getCmdtyCode()));
    lSqlBuf.append(" AND IndividualCode=").append(SIDBUtil.SQL2Str(getIndividualCode()));
    lSqlBuf.append(" AND ReserveFlg IN ('1','3') ");
    if (SIUtil.isNotNull(getStndrdCode1())){
      lSqlBuf.append(" AND StndrdCode1=").append(SIDBUtil.SQL2Str(getStndrdCode1()));
      lSqlBuf.append(" AND ElementCode1=").append(SIDBUtil.SQL2Str(getElementCode1()));
    }else{
      lSqlBuf.append(" AND StndrdCode1 IS NULL ");
      lSqlBuf.append(" AND ElementCode1 IS NULL ");
    }
    if (SIUtil.isNotNull(getStndrdCode2())){
      lSqlBuf.append(" AND StndrdCode2=").append(SIDBUtil.SQL2Str(getStndrdCode2()));
      lSqlBuf.append(" AND ElementCode2=").append(SIDBUtil.SQL2Str(getElementCode2()));
    }else{
      lSqlBuf.append(" AND StndrdCode2 IS NULL ");
      lSqlBuf.append(" AND ElementCode2 IS NULL ");
    }
    
    log.debug("init:lSqlBuf="+lSqlBuf.toString());
    try {
      setTotalOfAssignAmount(SIDBUtil.getFirstData(lConnection,lSqlBuf.toString()));
    } catch (SIDBAccessException e) {
      e.printStackTrace();
    }
  }
  
  public Collection getCollection(Connection lConnection, SILogin lLogin) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIReserveOrder lReserveOrder = new SIReserveOrder();
    StringBuffer lSqlBuf = new StringBuffer();
    Collection lReserveOrders = new ArrayList();
    
    //基本のSQL
    lSqlBuf.append("SELECT aa.*,cc.MemberLevelName,bb.CustCompanyFlg ");
    lSqlBuf.append(",bb.Email AS CurrentEmail,dd.BranchName,bb.address1 ");//7.2.0 ST1018 追加
    lSqlBuf.append(",CASE WHEN  reserveFlg IN ('1','3') AND shipmaildatetime IS NOT NULL ");
    lSqlBuf.append(" AND (nshipmaildatetime IS NULL OR shipmaildatetime>nshipmaildatetime) ");
    lSqlBuf.append(" AND shipmaildatetime::date + '7 days'::interval < current_date THEN 1 ELSE 0 END AS limitFlg ");
    lSqlBuf.append("FROM ReserveOrderTbl aa ");
    lSqlBuf.append("LEFT OUTER JOIN BranchTbl dd ");
    lSqlBuf.append("ON aa.ReserveBranch=dd.BranchCode, ");
    lSqlBuf.append("CustTbl bb,");
    lSqlBuf.append("MemberLevelMTbl cc ");
    if (lLogin.isShop()) {
      lSqlBuf.append("WHERE aa.ShopCode=").append(SIDBUtil.SQL2Str(lLogin.getMallShopCode(), " "));
    } else {
      lSqlBuf.append("WHERE aa.ShopCode=").append(SIDBUtil.SQL2Str(getShopCode(), " "));
    }
    lSqlBuf.append("AND aa.CmdtyCode=").append(SIDBUtil.SQL2Str(getCmdtyCode(), " "));
    lSqlBuf.append("AND aa.IndividualCode=").append(SIDBUtil.SQL2Str(getIndividualCode(), " "));//7.3.0 PI-NES0501 追加
    if (SIUtil.isNotNull(getStndrdCode1())&&SIUtil.isNotNull(getElementCode1())){
      lSqlBuf.append("AND aa.StndrdCode1=").append(SIDBUtil.SQL2Str(getStndrdCode1(), " "));
      lSqlBuf.append("AND aa.ElementCode1=").append(SIDBUtil.SQL2Str(getElementCode1(), " "));
    }
    
    if (SIUtil.isNotNull(getStndrdCode1())&&SIUtil.isNotNull(getElementCode2())){
      lSqlBuf.append("AND aa.StndrdCode2=").append(SIDBUtil.SQL2Str(getStndrdCode2(), " "));
      lSqlBuf.append("AND aa.ElementCode2=").append(SIDBUtil.SQL2Str(getElementCode2(), " "));
    }
    if (SIUtil.isNotNull(getReserveFlg())) {
      lSqlBuf.append("AND aa.ReserveFlg=").append(SIDBUtil.SQL2Str(getReserveFlg(), " "));
    }
    if (SIUtil.isNotNull(getInitUserName())) {
      lSqlBuf.append("AND aa.InitUserName=").append(SIDBUtil.SQL2Str(getInitUserName(), " "));
    }
    if (SIUtil.isNotNull(getReserveCodeTxt()) && SICheckUtil.isDigit(getReserveCodeTxt())) {
      lSqlBuf.append("AND aa.ReserveCode=").append(SIDBUtil.SQL2Str(getReserveCodeTxt(), " "));
    }
    lSqlBuf.append("AND aa.CustCode=bb.CustCode AND bb.MemberLevelCode=cc.MemberLevelCode ");
    //出力順
    lSqlBuf.append("ORDER BY ReserveCode DESC");//7.1.1 ST0047 修正
    
    log.debug("lSqlBuf=" + lSqlBuf.toString());
    //実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      
      //商品レコードのセットの作成
      while (lResultSet.next()) {
        lReserveOrder = new SIReserveOrder();
        lReserveOrder.setEncode(SIConfig.SIENCODE_NONE);
        lReserveOrder.setReserveCode(lResultSet.getString("ReserveCode"));
        lReserveOrder.setShopCode(lResultSet.getString("ShopCode"));
        lReserveOrder.setCmdtyCode(lResultSet.getString("CmdtyCode"));
        lReserveOrder.setIndividualCode(lResultSet.getString("IndividualCode"));//7.3.0 PI-NES0501 追加
        lReserveOrder.setCustCode(lResultSet.getString("CustCode"));
        lReserveOrder.setCustName(lResultSet.getString("CustName"));
        lReserveOrder.setEmail(lResultSet.getString("Email"));
        lReserveOrder.setCurrentEmail(lResultSet.getString("CurrentEmail"));//7.2.0 ST1018 追加
        lReserveOrder.setShipMailDateTime(SIDBUtil.getDateTime(lResultSet.getTimestamp("ShipMailDateTime")));
        lReserveOrder.setNshipMailDateTime(SIDBUtil.getDateTime(lResultSet.getTimestamp("NShipMailDateTime")));
        lReserveOrder.setCmdtyName(lResultSet.getString("CmdtyName"));
        lReserveOrder.setMemberLevelName(lResultSet.getString("MemberLevelName"));
        lReserveOrder.setCustCompanyFlg(lResultSet.getString("CustCompanyFlg"));
        lReserveOrder.setReserveFlg(lResultSet.getString("ReserveFlg"));
        lReserveOrder.setAmount(lResultSet.getString("Amount"));
        lReserveOrder.setAssignAmount(lResultSet.getString("AssignAmount"));
        lReserveOrder.setSplitFlg(lResultSet.getString("SplitFlg"));
        lReserveOrder.setReserveDateTime(SIDBUtil.getDateTime(lResultSet.getTimestamp("ReserveDateTime")));
        lReserveOrder.setReserveBranchCode(lResultSet.getString("ReserveBranch"));
        lReserveOrder.setReserveBranchName(lResultSet.getString("BranchName"));
        lReserveOrder.setAddress(lResultSet.getString("Address1"));
        lReserveOrder.setInitUserName(lResultSet.getString("InitUserName"));//登録ユーザ名
        lReserveOrder.setLimitFlg(lResultSet.getString("limitFlg"));
        lReserveOrder.setArrivalScheduleMsg(lResultSet.getString("arrivalScheduleMsg"));
        lReserveOrders.add(lReserveOrder);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lReserveOrders;
  }
  
  public String getReserveAmount(Connection lConnection){
    String result = "0";
    try{
      StringBuffer lSql = new StringBuffer();
      lSql.append("SELECT SUM(amount) ");
      lSql.append("FROM reserveordertbl ");
      lSql.append("WHERE reserveflg IN ('0','1','3','4') ");
      lSql.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(this.cmdtyCode," "));
      lSql.append("AND individualcode=").append(SIDBUtil.SQL2Str(this.individualCode));
      
      result = SIDBUtil.getFirstData(lConnection,lSql.toString());
      if(SIUtil.isNull(result)){
        result = "0";
      }
    }catch(Exception e){}
    return result;
  }
  
  public String getStockAmount(Connection lConnection){
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    try {
      String res = "";
      
      StringBuffer lSql = new StringBuffer();
      lSql.append("SELECT amount-bgpamount AS amount1,bgpamount AS amount2 ");
      lSql.append("FROM cmdtyunittbl ");
      lSql.append("WHERE cmdtycode=").append(SIDBUtil.SQL2Str(this.cmdtyCode," "));
      lSql.append("AND individualcode=").append(SIDBUtil.SQL2Str(this.individualCode));
      
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      
      if (lResultSet.next()) {
        res = lResultSet.getString("amount1");
        if (!"0".equals(lResultSet.getString("amount2"))) {
          res = res + "(" + lResultSet.getString("amount2") +")";
        }
      }
      return res;
    } catch(Exception e) {
      return "";
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
  }
  
  public String[][] getStock(Connection lConnection){
    Statement lStatement = null;
    ResultSet lResultSet = null;
    String[][] stock=new String[0][0];
    try{
      StringBuffer lSql = new StringBuffer();
      lSql.append("SELECT a.branchcode,a.amount,b.branchname ");
      lSql.append("FROM storestocktbl a,branchtbl b ");
      lSql.append("WHERE a.cmdtycode=").append(SIDBUtil.SQL2Str(this.cmdtyCode," "));
      lSql.append("AND a.individualcode=").append(SIDBUtil.SQL2Str(this.individualCode," "));
      lSql.append("AND a.branchcode=b.branchcode ");
      
      StringBuffer lSql2 = new StringBuffer();
      lSql2.append("SELECT COUNT(branchcode) ");
      lSql2.append("FROM storestocktbl ");
      lSql2.append("WHERE cmdtycode=").append(SIDBUtil.SQL2Str(this.cmdtyCode," "));
      lSql2.append("AND individualcode=").append(SIDBUtil.SQL2Str(this.individualCode));
      
      int Count=Integer.parseInt(SIDBUtil.getFirstData(lConnection,lSql2.toString()));
      
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      stock=new String[Count][3];
      int i=0;
      while(lResultSet.next()){
        stock[i][0]=lResultSet.getString("branchcode");
        stock[i][1]=lResultSet.getString("amount");
        stock[i][2]=lResultSet.getString("branchname");
        i++;
      }
    }catch(Exception e){
      e.printStackTrace();
    }finally{
      SIDBUtil.close(lResultSet, lStatement);
    }
    return stock;
  }
  
  public int getStockCount(Connection lConnection){
    int count=0;
    try{
      StringBuffer lSql = new StringBuffer();
      lSql.append("SELECT COUNT(branchcode) FROM storestocktbl ");
      lSql.append("WHERE cmdtycode=").append(SIDBUtil.SQL2Str(this.cmdtyCode," "));
      lSql.append("AND individualcode=").append(SIDBUtil.SQL2Str(this.individualCode));
      
      count=Integer.parseInt(SIDBUtil.getFirstData(lConnection,lSql.toString()));
    }catch(Exception e){
      e.printStackTrace();
    }
    return count;
  }
}