/**
 * 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 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.beans.front.UIReserve;
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.database.SITableCondition;
import jp.co.sint.database.SITableConditionManager;
import jp.co.sint.tools.SICheckDataConf;
import jp.co.sint.tools.SICheckValid;
import jp.co.sint.tools.SICustomErrors;
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: UIReserveListCond.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 13:49:32  Original
 */
public class UIReserveListCond extends SIBasic{
  //ログ用のインスタンスの生成
  private static Category log=Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  //ショップコード
  private String shopCodeTxt="";
  //商品コード
  private String cmdtyCodeTxt="";
  //商品名
  private String cmdtyNameTxt="";
  // 7.3.0 PI-NES0601 追加 ここから
  //在庫有無フラグ 
  private String stockRdo="0";
  //割当有無フラグ
  private String assignRdo="0";
  //在庫コード
  private String individualCode = "";
  //新品・中古
  private String usedNewFlg = "";
  //カテゴリ
  private String ctgryCode = "";
  //ブランド
  private String maker = "";
  //予約番号
  private String reserveCodeTxt="";
  //顧客名
  private String custNameTxt="";
  //顧客名かな
  private String custPronNameTxt="";
  //非表示フラグ
  private String invisibleFlg = "0";
  
  private String prevPage="";
  private String prevSize = "";
  //7.3.0 PI-NES0601 追加 ここまで
  private String cartFlg = "";
  
  private String custCodeTxt="";
  
  private String initUserName="";
  
  private String limitFlg = "";
  
  //SQL検索のばあいに、条件文
  private String conditionSQL="";
  
  private String cmdtyConditionSQL="";//7.3.0 PI-NES0601 追加
  
  public UIReserveListCond(){
    SITableConditionManager lConditionMan1=new SITableConditionManager();
    lConditionMan1.add(new SITableCondition(" AND aa.ReserveFlg IN (0,1,3,4) "));
    this.conditionSQL=lConditionMan1.getCondtionSQL();
    
    SITableConditionManager lConditionMan2=new SITableConditionManager();
    lConditionMan2.add(new SITableCondition("aa","amount","0",SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    this.cmdtyConditionSQL=lConditionMan2.getCondtionSQL();
  }
  
  public UIReserveListCond(HttpServletRequest lRequest,SIURLParameter lUrlParam){//7.1.1 ST0236 修正
    this.init(lRequest,lUrlParam);//7.1.1 ST0236 修正
  }
  
  //setter of ショップコード
  public  void setShopCodeTxt(String lShopCodeTxt){
    if (SIUtil.isNull(lShopCodeTxt)) lShopCodeTxt="";
    this.shopCodeTxt=SIUtil.changeTo(lShopCodeTxt.trim(),this.encode);
  }
  
  //setter of 商品コード
  public void setCmdtyCodeTxt(String lCmdtyCodeTxt){
    if (SIUtil.isNull(lCmdtyCodeTxt)) lCmdtyCodeTxt="";
    this.cmdtyCodeTxt=SIUtil.changeTo(lCmdtyCodeTxt.trim(),this.encode);
  }
  
  //setter of 商品名
  public void setCmdtyNameTxt(String lCmdtyNameTxt){
    if (SIUtil.isNull(lCmdtyNameTxt)) lCmdtyNameTxt="";
    this.cmdtyNameTxt=SIUtil.changeTo(lCmdtyNameTxt.trim(),this.encode);
  }
  
  //setter of 在庫有無フラグ 7.3.0 PI-NES0601 追加
  public void setStockRdo(String lStockRdo){
    if (SIUtil.isNull(lStockRdo)) lStockRdo="0";
    this.stockRdo=SIUtil.changeTo(lStockRdo.trim(),this.encode);
  }
  
  //setter of 割当有無フラグ 7.3.0 PI-NES0601 追加
  public void setAssignRdo(String lAssignRdo){
    if (SIUtil.isNull(lAssignRdo)) lAssignRdo="0";
    this.assignRdo=SIUtil.changeTo(lAssignRdo.trim(),this.encode);
  }
  
  //setter of 予約番号
  public void setReserveCodeTxt(String lReserveCodeTxt){
    if (SIUtil.isNull(lReserveCodeTxt)) lReserveCodeTxt="";
    this.reserveCodeTxt=SIUtil.changeTo(lReserveCodeTxt.trim(),this.encode);
  }
  
  //setter of 顧客名
  public void setCustNameTxt(String lCustNameTxt){
    if (SIUtil.isNull(lCustNameTxt)) lCustNameTxt="";
    this.custNameTxt=SIUtil.changeTo(lCustNameTxt.trim(),this.encode);
  }
  
  //setter of 顧客名かな
  public void setCustPronNameTxt(String lCustPronNameTxt){
    if (SIUtil.isNull(lCustPronNameTxt)) lCustPronNameTxt="";
    this.custPronNameTxt=SIUtil.changeTo(lCustPronNameTxt.trim(),this.encode);
  }
  
  //setter of 条件文
  public void setConditionSQL(String lConditionSQL){
    if (lConditionSQL==null)lConditionSQL="";
    this.conditionSQL=lConditionSQL;
  }
  
  //getter of ショップコード
  public String getShopCodeTxt(){
    return this.shopCodeTxt;
  }
  
  //getter of 商品コード
  public String getCmdtyCodeTxt(){
    return this.cmdtyCodeTxt;
  }
  
  //getter of 商品名
  public String getCmdtyNameTxt(){
    return this.cmdtyNameTxt;
  }
  
  //getter of 在庫有無フラグ 7.3.0 PI-NES0601 追加
  public String getStockRdo(){
    return this.stockRdo;
  }
  
  //getter of 割当有無フラグ 7.3.0 PI-NES0601 追加
  public String getAssignRdo(){
    return this.assignRdo;
  }
  
  //getter of 予約番号
  public String getReserveCodeTxt(){
    return this.reserveCodeTxt;
  }
  
  //getter of 顧客名
  public String getCustNameTxt(){
    return this.custNameTxt;
  }
  
  //getter of 顧客名かな
  public String getCustPronNameTxt(){
    return this.custPronNameTxt;
  }
  /**
   * @return ctgryCode を戻します。
   */
  public String getCtgryCode() {
    return ctgryCode;
  }
  /**
   * @param ctgryCode ctgryCode を設定。
   */
  public void setCtgryCode(String ctgryCode) {
    if (ctgryCode==null)ctgryCode="";
    this.ctgryCode = ctgryCode;     
  }
  /**
   * @return individualCode を戻します。
   */
  public String getIndividualCode() {
    return individualCode;
  }
  /**
   * @param individualCode individualCode を設定。
   */
  public void setIndividualCode(String individualCode) {
    if (individualCode==null)individualCode="";
    this.individualCode = individualCode;
  }
  /**
   * @return maker を戻します。
   */
  public String getMaker() {
    return maker;
  }
  /**
   * @param maker maker を設定。
   */
  public void setMaker(String maker) {
    if (maker==null)maker="";
    this.maker = maker;
  }
  /**
   * @return usedNewFlg を戻します。
   */
  public String getUsedNewFlg() {
    return usedNewFlg;
  }
  /**
   * @param usedNewFlg usedNewFlg を設定。
   */
  public void setUsedNewFlg(String usedNewFlg) {
    if (usedNewFlg==null)usedNewFlg="";
    this.usedNewFlg = usedNewFlg;
  } 
  /**
   * @return prevPage を戻します。
   */
  public String getPrevPage() {
    return prevPage;
  }
  /**
   * @param prevPage prevPage を設定。
   */
  public void setPrevPage(String prevPage) {
    this.prevPage = prevPage;
  }
  /**
   * @return prevSize を戻します。
   */
  public String getPrevSize() {
    return prevSize;
  }
  /**
   * @param prevSize prevSize を設定。
   */
  public void setPrevSize(String prevSize) {
    this.prevSize = prevSize;
  }
  
  public String getCartFlg() {
    return cartFlg;
  }
  
  public void setCartFlg(String cartFlg) {
    this.cartFlg = cartFlg;
  }
  
  public String getInvisibleFlg() {
    return invisibleFlg;
  }
  
  public void setInvisibleFlg(String invisibleFlg) {
    if (SIUtil.isNull(invisibleFlg)) invisibleFlg = "0";
    this.invisibleFlg = invisibleFlg;
  }
  
  public String getCustCodeTxt() {
    return custCodeTxt;
  }
  
  public void setCustCodeTxt(String custCodeTxt) {
    if (SIUtil.isNull(custCodeTxt)) custCodeTxt = "";
    this.custCodeTxt = custCodeTxt;
  }
  
  public String getInitUserName() {
    return initUserName;
  }
  
  public void setInitUserName(String initUserName) {
    if (SIUtil.isNull(initUserName)) initUserName = "";
    this.initUserName = initUserName;
  }
  
  public String getLimitFlg() {
    return limitFlg;
  }
  
  public void setLimitFlg(String limitFlg) {
    if (SIUtil.isNull(limitFlg)) limitFlg= "";
    this.limitFlg = limitFlg;
  }
  
  //getter of 条件文
  public String getCondtionSQL(){
    return this.conditionSQL;
  }
  
  //7.1.1 ST0236 修正 ここから
  public void init(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    SILogin lLogin=SIHTMLUtil.getLogin(lRequest);

    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    super.init(lRequest,lUrlParam);
    if (lLogin.isShop()){
      this.setShopCodeTxt(lLogin.getMallShopCode());
    }else{
      this.setShopCodeTxt((String)lUrlParam.getParam("shopCodeTxt"));
    }
    this.setCmdtyCodeTxt((String)lUrlParam.getParam("cmdtyCodeTxt"));
    this.setCmdtyNameTxt((String)lUrlParam.getParam("cmdtyNameTxt"));
    this.setStockRdo((String)lUrlParam.getParam("stockRdo"));//7.3.0 PI-NES0601 追加
    this.setAssignRdo((String)lUrlParam.getParam("assignRdo"));//7.3.0 PI-NES0601 追加
    this.setReserveCodeTxt((String)lUrlParam.getParam("reserveCodeTxt"));
    this.setCustCodeTxt((String)lUrlParam.getParam("custCodeTxt"));
    this.setCustNameTxt((String)lUrlParam.getParam("custNameTxt"));
    this.setCustPronNameTxt((String)lUrlParam.getParam("custPronNameTxt"));
    this.setInvisibleFlg((String)lUrlParam.getParam("invisibleFlg"));
    this.setInitUserName((String)lUrlParam.getParam("initUserName"));
    this.setLimitFlg((String)lUrlParam.getParam("limitFlg"));
  }
  //7.1.1 ST0236 修正 ここまで
  //7.3.0 PI-NES0601 追加 ここから
  public void initCmdty(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    SILogin lLogin=SIHTMLUtil.getLogin(lRequest);
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    super.init(lRequest,lUrlParam);
    if (lLogin.isShop()){
      this.setShopCodeTxt(lLogin.getMallShopCode());
    }else{
      this.setShopCodeTxt((String)lUrlParam.getParam("shopCodeTxt"));
    }
    this.setCmdtyCodeTxt((String)lUrlParam.getParam("cmdtyCodeTxt"));
    this.setCmdtyNameTxt((String)lUrlParam.getParam("cmdtyNameTxt"));
    this.setIndividualCode((String)lUrlParam.getParam("individualCode"));
    this.setUsedNewFlg((String)lUrlParam.getParam("usedNewFlg"));
    this.setCtgryCode((String)lUrlParam.getParam("ctgryCode"));
    this.setMaker((String)lUrlParam.getParam("maker"));
    this.setCartFlg((String)lUrlParam.getParam("cartFlg"));
    this.setPrevPage((String)lUrlParam.getParam("prevPage"));
    this.setPrevSize((String)lUrlParam.getParam("prevSize"));
  }
  
  public void validateCmdty(HttpServletRequest lRequest){
    SILogin lLogin=SIHTMLUtil.getLogin( lRequest);
    SICustomErrors errors=new SICustomErrors();
    SITableConditionManager lConditionMan=new SITableConditionManager();
    
    //ショップコード
    if (lLogin.isMall()&&SIUtil.isNotNull(this.shopCodeTxt)&&SICheckValid.checkValid(errors,"ショップコード",this.shopCodeTxt,SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE)){
      lConditionMan.add(new SITableCondition("aa","ShopCode",this.shopCodeTxt,SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    }
    //親コード
    if (SIUtil.isNotNull(this.cmdtyCodeTxt)&&SICheckValid.checkValid(errors,"親コード",this.cmdtyCodeTxt,SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE)){
      lConditionMan.add(new SITableCondition("aa","CmdtyCode",this.cmdtyCodeTxt,SIConfig.SICONDITION_TYPE_LIKE,SIConfig.SICONDITION_TYPE_AND));
    }
    //在庫コード
    if (SIUtil.isNotNull(this.individualCode)&&SICheckValid.checkValid(errors,"在庫コード",this.individualCode,SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE)){
      lConditionMan.add(new SITableCondition("aa","IndividualCode",this.individualCode,SIConfig.SICONDITION_TYPE_FRONTLIKE,SIConfig.SICONDITION_TYPE_AND));
    }
    //商品名
    if (SIUtil.isNotNull(this.cmdtyNameTxt) && SICheckValid.checkValid(errors,"商品名",this.cmdtyNameTxt,SICheckDataConf.SICHECK_DATA_ZENKAKU_TYPE)) {
      lConditionMan.add(new SITableCondition("aa","CmdtyName",this.cmdtyNameTxt,SIConfig.SICONDITION_TYPE_LIKE,SIConfig.SICONDITION_TYPE_AND));
    }
    //中古新品フラグ
    if (SIUtil.isNotNull(this.usedNewFlg)){
      lConditionMan.add(new SITableCondition("aa","usednewflg",this.usedNewFlg,SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    }
    //ブランドコード
    if (SIUtil.isNotNull(this.maker)){
      lConditionMan.add(new SITableCondition("aa","makerCode",this.maker,SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    }
    //フロント表示状態
    if (SIUtil.isNull(this.cartFlg)) {
      lConditionMan.add(new SITableCondition("aa","amount","0",SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    }
    
    if (!errors.isEmpty())lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY,errors);
    else lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    
    //条件文の設定
    this.cmdtyConditionSQL=lConditionMan.getCondtionSQL();
    
  }
  //7.3.0 PI-NES0601 追加 ここまで
 
  /**
   * <b>validate</b>
   * 入力したデータをチェックして、同時にSQLの条件文を作成します。
   * @param lRequest クライアントからのリクエスト
   * @return なし
   * @throws なし
   */
  public void validate(HttpServletRequest lRequest){
    SILogin lLogin=SIHTMLUtil.getLogin( lRequest);
    
    SICustomErrors errors=new SICustomErrors();
    SITableConditionManager lConditionMan=new SITableConditionManager();
    
    //ショップコード
    if (lLogin.isMall()&&SIUtil.isNotNull(this.shopCodeTxt)&&SICheckValid.checkValid(errors,"ショップコード",this.shopCodeTxt,SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE)){
      lConditionMan.add(new SITableCondition("aa","ShopCode",this.shopCodeTxt,SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    }
    
    //在庫コード
    if (SIUtil.isNotNull(this.cmdtyCodeTxt)&&SICheckValid.checkValid(errors,"在庫コード",this.cmdtyCodeTxt,SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE)){
      lConditionMan.add(new SITableCondition("aa","IndividualCode",this.cmdtyCodeTxt,SIConfig.SICONDITION_TYPE_FRONTLIKE,SIConfig.SICONDITION_TYPE_AND));
    }
    //商品名
    if (SIUtil.isNotNull(this.cmdtyNameTxt) && SICheckValid.checkValid(errors,"商品名",this.cmdtyNameTxt,SICheckDataConf.SICHECK_DATA_ZENKAKU_TYPE)) {
      lConditionMan.add(new SITableCondition("aa","CmdtyName",this.cmdtyNameTxt,SIConfig.SICONDITION_TYPE_LIKE,SIConfig.SICONDITION_TYPE_AND));
    }
    //予約番号
    if (SIUtil.isNotNull(this.reserveCodeTxt) && SICheckValid.checkValid(errors,"予約番号",this.reserveCodeTxt,SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)){
      lConditionMan.add(new SITableCondition("aa","reserveCode",this.reserveCodeTxt,SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    }
    //顧客コード
    if (SIUtil.isNotNull(this.custCodeTxt) && SICheckValid.checkValid(errors,"顧客コード",this.custCodeTxt,SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)) {
      lConditionMan.add(new SITableCondition("aa","custCode",this.custCodeTxt,SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    }
    //顧客名
    if (SIUtil.isNotNull(this.custNameTxt) && SICheckValid.checkValid(errors,"顧客名",this.custNameTxt,SICheckDataConf.SICHECK_DATA_ZENKAKU_TYPE)) {
      String[] custNameList = this.getCustNameTxt().split(" |　");
      for (int i=0;i<custNameList.length;i++) {
        lConditionMan.add(new SITableCondition("aa", "custName", custNameList[i], SIConfig.SICONDITION_TYPE_LIKE, SIConfig.SICONDITION_TYPE_AND));
      }
    }
    //顧客名かな
    if (SIUtil.isNotNull(this.custPronNameTxt) && SICheckValid.checkValid(errors,"顧客名カナ",this.custPronNameTxt,SICheckDataConf.SICHECK_DATA_ZENKAKU_KANA_TYPE)) {
      String[] custPronNameList = this.getCustPronNameTxt().split(" |　");
      for (int j=0;j<custPronNameList.length;j++) {
        lConditionMan.add(new SITableCondition("aa", "custPronName", custPronNameList[j], SIConfig.SICONDITION_TYPE_LIKE, SIConfig.SICONDITION_TYPE_AND));
      }
    }
    //担当者
    lConditionMan.add(new SITableCondition("aa","initUserName",this.initUserName,SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    
    //期限切れ、連絡未送信
    if (SIUtil.isNotNull(this.limitFlg) && "0".equals(this.limitFlg)) {
      StringBuffer lSqlStr = new StringBuffer();
      lSqlStr.append(" AND aa.cmdtycode||aa.individualcode IN (");
      lSqlStr.append(" SELECT cmdtycode||individualcode FROM reserveordertbl ");
      lSqlStr.append(" WHERE reserveFlg IN ('1','3') ");
      lSqlStr.append(" AND shipmaildatetime IS NOT NULL ");
      lSqlStr.append(" AND (nshipmaildatetime IS NULL OR shipmaildatetime>nshipmaildatetime) ");
      lSqlStr.append(" AND shipmaildatetime::date + '7 days'::interval < current_date) ");
      lConditionMan.add(new SITableCondition(lSqlStr.toString()));
    }else if(SIUtil.isNotNull(this.limitFlg) && "1".equals(this.limitFlg)){
      StringBuffer lSqlStr = new StringBuffer();
      lSqlStr.append(" AND aa.cmdtycode||aa.individualcode IN (");
      lSqlStr.append(" SELECT cmdtycode||individualcode FROM reserveordertbl ");
      lSqlStr.append(" WHERE reserveFlg IN ('1','3') ");
      lSqlStr.append(" AND shipmaildatetime IS NULL) ");
      lConditionMan.add(new SITableCondition(lSqlStr.toString()));
      
    }
    
    //非表示フラグ
    if (SIUtil.isNotNull(this.invisibleFlg) && this.invisibleFlg.equals("0")) {
      lConditionMan.add(new SITableCondition(" AND aa.ReserveFlg IN ('0','1','3','4') "));
    }
    
    //7.3.0 PI-NES0601 追加 ここから
    String lSql="";
    if(SIUtil.isNotNull(this.stockRdo)){
      if(this.stockRdo.equals("1")) lSql="AND NOT bb.amount='0' ";
      else if(this.stockRdo.equals("2")) lSql="AND bb.amount='0' ";
    }
    //7.3.0 PI-NES0601 追加 ここまで
    
    if (!errors.isEmpty())lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY,errors);
    else lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    
    //条件文の設定
    this.setConditionSQL(lConditionMan.getCondtionSQL()+lSql);//7.3.0 PI-NES0601 修正
  }
  
  public Collection getCollection(Connection lConnection) throws SIDBAccessException{
    Statement lStatement=null;
    ResultSet lResultSet=null;
    SIReserveOrder lReserveOrder=new SIReserveOrder();
    StringBuffer lSqlBuf=new StringBuffer();
    Collection lReserveOrders=new ArrayList();
    
    //基本のSQL
    lSqlBuf.append("SELECT aa.CmdtyCode,aa.IndividualCode,bb.CmdtyName,SUM(aa.Amount) AS Amount,bb.amount-bb.bgpamount AS stock1,bb.bgpamount AS stock2");
    lSqlBuf.append(",SUM(CASE WHEN aa.reserveflg='0' THEN 1 ELSE 0 END) AS count ");
    lSqlBuf.append(",SUM(CASE WHEN aa.reserveflg='0' THEN aa.amount ELSE 0 END) AS NoAssignAmount ");
    lSqlBuf.append("FROM ReserveOrderTbl aa ,CmdtyUnitTbl bb ");
    lSqlBuf.append("WHERE aa.CmdtyCode=bb.CmdtyCode AND aa.IndividualCode=bb.IndividualCode ");
    
    //検索の条件
    lSqlBuf.append(this.conditionSQL);
    //出力順
    lSqlBuf.append(" GROUP BY aa.CmdtyCode,aa.IndividualCode,bb.CmdtyName,bb.amount,bb.bgpamount ");
    if(SIUtil.isNotNull(this.assignRdo)){
      if(this.assignRdo.equals("1")) lSqlBuf.append(" HAVING SUM(CASE WHEN aa.reserveflg='0' THEN 1 ELSE 0 END) > 0 ");
      else if(this.assignRdo.equals("2")) lSqlBuf.append(" HAVING SUM(CASE WHEN aa.reserveflg='0' THEN 1 ELSE 0 END) = 0 ");
    }
    lSqlBuf.append(" ORDER BY aa.IndividualCode ");
    
    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.setShopCode("0");
        lReserveOrder.setCmdtyCode(lResultSet.getString("CmdtyCode"));
        lReserveOrder.setIndividualCode(lResultSet.getString("IndividualCode"));
        lReserveOrder.setAmount(lResultSet.getString("Amount"));
        lReserveOrder.setCmdtyName(lResultSet.getString("CmdtyName"));
        lReserveOrder.setRsrvAmount(lResultSet.getString("count"));
        lReserveOrder.setNoAssignAmount(lResultSet.getString("NoAssignAmount"));
        
        if("0".equals(lResultSet.getString("stock2"))){
          lReserveOrder.setStockAmount(lResultSet.getString("stock1"));
        }else{
          lReserveOrder.setStockAmount(lResultSet.getString("stock1")+"("+lResultSet.getString("stock2")+")");
        }
        //if (lResult)
        lReserveOrders.add(lReserveOrder);
      }
    }catch(Exception ex){
      throw new SIDBAccessException(ex);
    }finally{
      SIDBUtil.close(lResultSet,lStatement);
    }
    //7.3.0 PI-NES0501 修正 ここまで
    return lReserveOrders;
  }
  //7.3.0 PI-NES0601 修正 ここから
  public Collection getCmdtyCollection(Connection lConnection,SILogin lLogin) throws SIDBAccessException{
    Statement lStatement=null;
    ResultSet lResultSet=null;
    UIReserve reserve = new UIReserve();
    StringBuffer lSqlBuf=new StringBuffer();
    StringBuffer lItemBuf=new StringBuffer();
    StringBuffer lCountBuf=new StringBuffer();
    Collection lReserveOrders=new ArrayList();
    
    //基本のSQL
    lItemBuf.append("SELECT aa.ShopCode, aa.CmdtyCode,aa.IndividualCode,aa.usednewflg,aa.amountflg, ");
    lItemBuf.append("aa.unitprice,aa.frontdispflg,aa.CmdtyName,aa.makerCode,aa.rsrvamount ");
    lCountBuf.append("SELECT count(aa.*) ");
    lSqlBuf.append("FROM cmdtyunittbl aa ");
    lSqlBuf.append("WHERE aa.ShopCode='0' ");
    
    //カテゴリチェック
    if (SIUtil.isNotNull(this.ctgryCode)){
      lSqlBuf.append(" AND aa.cmdtycode IN (SELECT DISTINCT ct.cmdtycode FROM cmdtyctgrymtbl AS ct WHERE ct.ctgrycode =").append(SIDBUtil.SQL2Str(this.ctgryCode,") "));         
    }
    /*予約商品の条件
    １．フロント表示・表示
      （フロント非表示の時は商品が表示されない）※仕様指定品の一部として表示されている場合を除く
    ２．問合せ区分・通常商品
      （問合せ区分がお問い合わせ下さいの場合はお問い合わせ商品になり、在庫多数の場合は購買できる商品になる）
    ３．在庫管理し、在庫が０
      （在庫管理しない場合は購買できる商品になる、在庫管理し、在庫がある場合は購買できる商品になる）
    ４．予約販売し、予約可能数がある*/
    lSqlBuf.append("AND (aa.frontdispflg='1' ");
    lSqlBuf.append(" OR (aa.frontdispflg='0' AND aa.individualcode IN ");
    lSqlBuf.append("(SELECT d.detailindividualcode FROM compositiongroupdetailtbl d,cmdtyunittbl i ");
    lSqlBuf.append(" WHERE d.individualcode=i.individualcode AND i.cmdtycompositionflg='4' AND i.frontdispflg='1'))) ");
    lSqlBuf.append("AND aa.amountflg='1' ");
    lSqlBuf.append("AND aa.rsrvenableflg='1' AND (aa.rsrvamount > 0::numeric OR aa.rsrvamount IS NULL) ");//予約販売する
    lSqlBuf.append(this.cmdtyConditionSQL); 
    lCountBuf.append(lSqlBuf);
    lItemBuf.append(lSqlBuf);
    lItemBuf.append("ORDER BY aa.CmdtyCode, aa.individualcode ");
    
    log.debug("lItemBuf="+lItemBuf.toString());
    //実行
    try{
      String rowCnt = SIDBUtil.getFirstData(lConnection, lCountBuf.toString());
      if (rowCnt.equals("") || rowCnt == null) {
        rowCnt = "0";
      }
      int lRecordCount = Integer.parseInt(rowCnt);// レコード数の取得
      
      int lPageSize = this.getPageSize();// ページサイズ
      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;
      
      log.debug("getCollection:lFromInx=" + lFromInx + ",lToInx=" + lToInx + ",lRecordCount=" + lRecordCount);
      
      lStatement = lConnection.createStatement();
      lItemBuf.append(" LIMIT ").append(lPageSize);
      if(lFromInx>0) lItemBuf.append(" OFFSET ").append(lFromInx);
      log.debug("getCollection:lOrderBuf=" + lItemBuf.toString());
      lResultSet = lStatement.executeQuery(lItemBuf.toString());
      
      for (int jj = 0; jj < lFromInx; jj++) lReserveOrders.add(null);
      int lIndex = 0;
      
      // レコードのセットの作成
      while (lResultSet.next() && lIndex < lPageSize) {
        reserve=new UIReserve();
        reserve.setEncode(SIConfig.SIENCODE_NONE);
        reserve.setRsrvShopCodeTxt(lResultSet.getString("ShopCode"));
        reserve.setRsrvCmdtyCodeTxt(lResultSet.getString("CmdtyCode"));
        reserve.setRsrvIndividualCodeTxt(lResultSet.getString("IndividualCode"));
        if(SIUtil.isNotNull(lResultSet.getString("rsrvamount"))){
          reserve.setRsrvAmount(lResultSet.getString("rsrvamount"));
        }
        reserve.setCmdtyName(lResultSet.getString("CmdtyName"));
        reserve.setMaker(lResultSet.getString("makerCode"));
        reserve.setBgPrice(lResultSet.getString("unitprice"));
        reserve.setAmountTxt("default");//フロント側でDEFAULT値が１のため
        lReserveOrders.add(reserve);
        lIndex++;
      }
      for (int jj = lFromInx + lPageSize; jj < lRecordCount; jj++) lReserveOrders.add(null);
      
    }catch(Exception ex){
      throw new SIDBAccessException(ex);
    }finally{
      SIDBUtil.close(lResultSet,lStatement);
    }
    return lReserveOrders;
  }
  //7.3.0 PI-NES0601 追加 ここまで
}