/**
 * 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.SICmdty;
import jp.co.sint.basic.SIFrontBasic;
import jp.co.sint.basic.SINaviplusSearch;
import jp.co.sint.basic.SISrchKey;
import jp.co.sint.basic.SITax;
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.database.SIOrderBy;
import jp.co.sint.database.SITableConditionManager;
import jp.co.sint.tools.SICheckUtil;
import jp.co.sint.tools.SIFatalException;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIURLMap;
import jp.co.sint.tools.SIURLParameter;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;

/**
 * @version $Id: UIMain.java,v 1.0 2003/10/21 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 2003/10/21 17:55:19 Original
 */
public class UIMain extends SIFrontBasic {
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  // 商品順
  private String cmdtySortSel = "2";// 新しい順or一致度順
  
  // 一サムネイル画面に表示するレコード数
  private String thumPageSizeSel = "3";
  
  // 中古新品フラグ
  private String usedNewFlgTxt = "all";
  
  // 並び順
  private String orderBySQL = " ";// 7.1.1 ST0163 修正
  
  private String memberDiscountRate = "0";// 7.1.1 ST0219 追加 会員値引き
  
  // ブランド
  private String maker = "";
  
  // アクティブパラメータ
  private String active = "";
  
  // BG特殊設定パラメータ
  private String listType = "";
  
  // 価格帯下限
  private String priceFrom = "";
  
  // 価格帯上限
  private String priceTo = "";
  
  // 在庫コード直接指定
  private String codes = "";
  
  // 検索履歴設定
  private String searchbox = "1";
  
  // 検索エンジン拒否
  private String nosearch = "0";
  
  // 検索除外ワード
  private String excludeWords = "";
  
  //EDBTG005-00 kamata add start
  // 選択した中カテゴリ
  private String selectTyuCtgry = "";
  // 選択したブランド
  private String selectedBrand = "";
  // 選択した価格
  private String priceSelectFlg = "";
  //EDBTG005-00 kamata add end
  
  public UIMain() {
    this.setPageSizeSel("40");
  }
  
  //EDBTG005-00 kamata add start

  public String getSelectTyuCtgry() {
    return selectTyuCtgry;
  }

  public void setSelectTyuCtgry(String selectTyuCtgry) {
    if (SIUtil.isNull(selectTyuCtgry)) selectTyuCtgry = "";
    this.selectTyuCtgry = selectTyuCtgry;
  }

  public String getSelectedBrand() {
    return selectedBrand;
  }

  public void setSelectedBrand(String selectedBrand) {
    if (SIUtil.isNull(selectedBrand)) selectedBrand = "";
    this.selectedBrand = selectedBrand;
  }

  public String getPriceSelectFlg() {
    return priceSelectFlg;
  }

  public void setPriceSelectFlg(String priceSelectFlg) {
    if (SIUtil.isNull(priceSelectFlg)) priceSelectFlg = "";
    this.priceSelectFlg = priceSelectFlg;
  }
  //EDBTG005-00 kamata add end

  public void setPageSizeSel(String value) {
    if (SIUtil.isNull(value)) value = "40";
    this.pageSizeSel = value;
  }
  
  // setter of 商品順
  public void setCmdtySortSel(String lCmdtySordSel) {
    if (SIUtil.isNull(lCmdtySordSel)) lCmdtySordSel = "2";// 新しい順or一致度順
    this.cmdtySortSel = SIUtil.changeTo(lCmdtySordSel.trim(), this.encode);
  }
  
  // setter of 商品コード
  public void setThumPageSizeSel(String lThumPageSizeSel) {
    if (SIUtil.isNull(lThumPageSizeSel)) lThumPageSizeSel = "3";
    this.thumPageSizeSel = SIUtil.changeTo(lThumPageSizeSel.trim(), this.encode);
  }
  
  // setter of 中古新品フラグ
  public void setUsedNewFlgTxt(String lUsedNewFlgTxt) {
    if (SIUtil.isNull(lUsedNewFlgTxt)) this.usedNewFlgTxt = "0";
    this.usedNewFlgTxt = lUsedNewFlgTxt;
  }
  
  // setter of 並び順
  public void setOrderBySQL(String lOrderBySQL) {
    if (SIUtil.isNull(lOrderBySQL)) this.makeOrderBySQL();// 7.1.1 ST0163 修正
    this.orderBySQL = lOrderBySQL;
  }
  
  // getter of 商品順
  public String getCmdtySortSel() {
    return this.cmdtySortSel;
  }
  
  // 7.1.1 ST0219 追加 ここから
  // setter of 会員値引き
  public void setMemberDiscountRate(String lMemberDiscountRate) {
    if (SIUtil.isNull(lMemberDiscountRate)) lMemberDiscountRate = "0";
    this.memberDiscountRate = lMemberDiscountRate;
  }
  
  // 7.1.1 ST0219 追加 ここまで
  
  // getter of 商品コード
  public String getThumPageSizeSel() {
    return this.thumPageSizeSel;
  }
  
  // getter of 中古新品フラグ
  public String getUsedNewFlgTxt() {
    return this.usedNewFlgTxt;
  }
  
  // getter of 並び順
  public String getOrderBySQL() {
    return this.orderBySQL;
  }
  
  // 7.1.1 ST0219 追加 ここから
  // getter of 会員値引き
  public String getMemberDiscountRate() {
    return this.memberDiscountRate;
  }
  
  // 7.1.1 ST0219 追加 ここまで
  // 7.3.0 PI-NES0501 追加 ここから
  public String dispCtgryCode = "";
  public String dispCtgryName = "";
  
  /**
   * @return dispCtgry を戻します。
   */
  public String getDispCtgryCode() {
    return dispCtgryCode;
  }
  
  /**
   * @return dispCtgry を戻します。
   */
  public String getDispCtgryName() {
    return dispCtgryName;
  }
  
  /**
   * @param dispCtgry dispCtgry を設定。
   */
  public void setDispCtgryCode(String dispCtgryCode) {
    this.dispCtgryCode = dispCtgryCode;
  }
  
  /**
   * @param dispCtgry dispCtgry を設定。
   */
  public void setDispCtgryName(String dispCtgryName) {
    this.dispCtgryName = dispCtgryName;
  }
  
  // 7.3.0 PI-NES0501 追加 ここまで
  
  // getter of maker
  public String getMaker() {
    return maker;
  }
  
  public String getPriceFrom() {
    return priceFrom;
  }
  
  public String getPriceTo() {
    return priceTo;
  }
  
  // setter of maker
  public void setMaker(String maker) {
    if (SIUtil.isNull(maker)) maker = "";
    this.maker = SIUtil.changeTo(maker.trim(), this.encode);
  }
  
  // getter of active
  public String getActive() {
    return this.active;
  }
  
  // setter of active
  public void setActive(String active) {
    if (SIUtil.isNull(active)) active = "";
    this.active = SIUtil.changeTo(active.trim(), this.encode);
  }
  
  //getter of listType
  public String getListType() {
    return this.listType;
  }
  
  //setter of listType
  public void setListType(String listType) {
    if (SIUtil.isNull(listType)) listType = "";
    this.listType = SIUtil.changeTo(listType.trim(), this.encode);
  }
  
  public void setPriceFrom(String priceFrom) {
    if (SIUtil.isNull(priceFrom)) priceFrom = "";
    this.priceFrom = SIUtil.changeTo(priceFrom.trim(), this.encode);
  }
  
  public void setPriceTo(String priceTo) {
    if (SIUtil.isNull(priceTo)) priceTo = "";
    this.priceTo = SIUtil.changeTo(priceTo.trim(), this.encode);
  }
  
  public String getCodes() {
    return codes;
  }
  
  public void setCodes(String codes) {
    if (SIUtil.isNull(codes)) codes = "";
    this.codes = codes;
  }
  
  public String getSearchbox() {
    return searchbox;
  }
  
  public void setSearchbox(String searchbox) {
    if (SIUtil.isNull(searchbox)) searchbox = "1";
    this.searchbox = searchbox;
  }
  
  public String getNosearch() {
    return nosearch;
  }
  
  public void setNosearch(String nosearch) {
    if (SIUtil.isNull(nosearch)) nosearch = "0";
    this.nosearch = nosearch;
  }
  
  public String getExcludeWords() {
    return excludeWords;
  }
  
  public void setExcludeWords(String excludeWords) {
    if (SIUtil.isNull(excludeWords)) excludeWords = "";
    this.excludeWords = excludeWords;
  }
  
  public void clear() {
    super.clear();
    this.setCmdtySortSel("");
    this.setThumPageSizeSel("");
  }
  
  /**
   * <b>init</b> トップ画面に、顧客さんが選択データを取得して、設定します。
   * 
   * @param request クライアントからリクエスト
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  public void init(HttpServletRequest lRequest, SIURLParameter lUrlParam) {// 7.1.1 ST0236 修正
    SITableConditionManager lConditionMan = new SITableConditionManager();
    
    if (SIURLMap.isAUPost(lRequest)) {
      this.setEncode(SIConfig.SIENCODE_NONE);
      this.srchKey.setEncode(SIConfig.SIENCODE_NONE);
    } else {
      this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
      this.srchKey.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    }
    
    this.setCmdtySortSel((String) lUrlParam.getParam("cmdtySortSel"));// 検索結果の順位の設定//7.1.1 ST0236 修正
    this.setThumPageSizeSel((String) lUrlParam.getParam("thumPageSizeSel"));// 7.1.1 ST0236 修正
    String appendSQL = "";
    super.init(lRequest, lConditionMan, "a1", lUrlParam);// 7.1.1 ST0236 修正
    this.setUsedNewFlgTxt((String) lUrlParam.getParam("usedNewFlgTxt"));
    if (SIUtil.isNull(this.usedNewFlgTxt)) {
      this.setUsedNewFlgTxt("all");
    } else if (this.usedNewFlgTxt.equals("new")) {
      appendSQL += "AND a1.usednewflg IN ('1','6','7') ";
    } else if (this.usedNewFlgTxt.equals("outlet")) {
      appendSQL += "AND a1.usednewflg IN ('2','4') ";
    } else if (this.usedNewFlgTxt.equals("used")) {
      appendSQL += "AND a1.usednewflg IN ('0','3','5') ";
    } else if (this.usedNewFlgTxt.equals("spused")) {
      appendSQL += "AND a1.usednewflg = '3' ";
    }
    this.setMaker((String) lUrlParam.getParam("maker"));
    if (SIUtil.isNotNull(this.maker) && this.maker.equals("1230")) {//ポールシェリーM-productの場合は1230〜1232を選択
      appendSQL += "AND a1.makercode IN ('1230','1231','1232') ";
    } else if (SIUtil.isNotNull(this.maker)) {
      appendSQL += "AND a1.makercode = " + SIDBUtil.SQL2Str(this.maker," ");
    }
    this.setActive((String) lUrlParam.getParam("active"));
    this.setListType((String) lUrlParam.getParam("listtype"));
    this.setPriceFrom((String) lUrlParam.getParam("priceFrom"));
    this.setPriceTo((String) lUrlParam.getParam("priceTo"));
    this.setCodes((String) lUrlParam.getParam("codes"));
    if (SICheckUtil.isDigit(this.getPriceFrom())){
      if (SIUtil.isNotNull(this.getPriceFrom())) {
        appendSQL += "AND a1.unitprice >= "+SIDBUtil.SQL2Str(this.getPriceFrom()," ");
      }
    } else {
      this.setPriceFrom("");
    }
    if (SICheckUtil.isDigit(this.getPriceTo())){
      if (SIUtil.isNotNull(this.getPriceTo())) {
        appendSQL += "AND a1.unitprice <= "+SIDBUtil.SQL2Str(this.getPriceTo()," ");
      }
    } else {
      this.setPriceTo("");
    }
    if (SIUtil.isNotNull(this.getCodes())) {
      String[] individualCodes = this.getCodes().split(",");
      appendSQL += "AND a1.individualcode IN(";
      for (int i=0;i<individualCodes.length;i++) {
        if (i>0) appendSQL += ",";
        appendSQL += SIDBUtil.SQL2Str(individualCodes[i]);
      }
      appendSQL += ") ";
    }
    this.setCmdtyFlagTxt("sr");
    this.setSearchbox((String)lUrlParam.getParam("searchbox"));
    this.setNosearch((String)lUrlParam.getParam("nosearch"));
    this.setExcludeWords((String)lUrlParam.getParam("excludeWords"));
    
    // 条件文の設定
    this.setConditionSQL(lConditionMan.getCondtionSQL() + appendSQL);
    // 並び順文の作成
    this.makeOrderBySQL();
    
    // EDBTG005-00 kamata add start
    this.setSelectTyuCtgry((String)lUrlParam.getParam("selectTyuCtgry"));
    this.setSelectedBrand((String)lUrlParam.getParam("selectedBrand"));
    this.setPriceSelectFlg((String)lUrlParam.getParam("priceSelectFlg"));
    // EDBTG005-00 kamata add end
  }
  
  /**
   * <b>initCtgry</b> カテゴリーをクリックしたとき
   * 
   * @param request クライアントからリクエスト
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  public void initCtgry(HttpServletRequest lRequest, SIURLParameter lUrlParam) {// 7.1.1 ST0236 修正
    SITableConditionManager lConditionMan = new SITableConditionManager();
    this.srchKey = new SISrchKey();
    this.srchKey.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    this.setDispModeTxt(SIConfig.DEFAULT_DISPMODE);
    this.setCtgryCodeTxt((String) lUrlParam.getParam("ctc"));// 7.1.1 ST0236 修正
    this.setCmdtyFlagTxt("sr");
    this.setActionNameTxt(SIConfig.SIACTION_CTGRY);
    if (SICheckUtil.isDigit((String) lUrlParam.getParam("pageNumberTxt"))) {
      this.setPageNumberTxt((String) lUrlParam.getParam("pageNumberTxt"));
    }
    super.initSrchKey(lRequest, lConditionMan, "a1", lUrlParam);// 7.1.1 ST0236 修正
    // 条件文の設定
    this.setConditionSQL(lConditionMan.getCondtionSQL());
  }
  
  /**
   * <b>initSrch</b> カテゴリー,検索をクリックしたとき
   * 
   * @param request クライアントからリクエスト
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  public void initSrch(HttpServletRequest lRequest, SIURLParameter lUrlParam) {
    SITableConditionManager lConditionMan = new SITableConditionManager();
    super.initSrchKey(lRequest, lConditionMan, "a1", lUrlParam);
    this.setPageNumberTxt("1");
    this.setDispModeTxt(SIConfig.DEFAULT_DISPMODE);
    this.setCtgryCodeTxt("/");
    this.setCmdtyFlagTxt(SIConfig.SICMDTY_FLAG_SRCH);
    this.setEditModeTxt(SIConfig.SIEDIT_MODE_SEARCH);
    // 条件文の設定
    this.setConditionSQL(lConditionMan.getCondtionSQL());
  }
  
  /**
   * <b>makeOrderBySQL</b> 並び順文の作成
   * 
   * @param なし
   * @return なし
   * @throws なし
   */
  public void makeOrderBySQL() {
    SIOrderBy lOrderBy = new SIOrderBy();
    if (getCmdtySortSel().equals("0")) lOrderBy = new SIOrderBy("UnitPrice", "0");
    else if (getCmdtySortSel().equals("1")) lOrderBy = new SIOrderBy("UnitPrice", "1");
    else if (getCmdtySortSel().equals("2")) lOrderBy = new SIOrderBy("COALESCE(TO_CHAR(a1.SellFromDate,'yyyy/mm/dd'),' ')", "1");
    else if (getCmdtySortSel().equals("3")) lOrderBy = new SIOrderBy("Individualcode", "0");
    else if (getCmdtySortSel().equals("4")) lOrderBy = new SIOrderBy("CmdtyName", "0");
    else if (getCmdtySortSel().equals("6")) {
      if (getCtgryCodeTxt().equals("category011")||getCtgryCodeTxt().equals("category013")||getCtgryCodeTxt().equals("category014")||getCtgryCodeTxt().equals("category015")
        ||getCtgryCodeTxt().equals("category016")||getCtgryCodeTxt().equals("category018")||getCtgryCodeTxt().equals("category020")
        ||getCtgryCodeTxt().startsWith("Z")||getCtgryCodeTxt().startsWith("V")||getCtgryCodeTxt().startsWith("N")||getCtgryCodeTxt().startsWith("T")
        ||getCtgryCodeTxt().startsWith("L")||getCtgryCodeTxt().startsWith("W")||getCtgryCodeTxt().startsWith("H")) {
        lOrderBy = new SIOrderBy("CmdtyName", "0");
      } else {
        lOrderBy = new SIOrderBy("COALESCE(TO_CHAR(a1.SellFromDate,'yyyy/mm/dd'),' ')", "1");
      }
    } else {
      lOrderBy = new SIOrderBy("rankingPrice", "1");
    }
    lOrderBy.addSort("a1", "InitDateTime", "1");
    lOrderBy.addSort("a1", "CmdtyCode::numeric", "1");
    lOrderBy.addSort("a1", "Individualcode", "1");
    this.setOrderBySQL(lOrderBy.getOrderBySQL());
  }
  
  // 7.2.0 ST0301 追加 ここから
  /**
   * <b>getTopCmdtyFlag</b> 登録されているカテゴリトップの商品初期表示を取得
   * 
   * @param lConnection
   * @return topCmdtyFlag
   * @throws なし
   */
  public String getTopCmdtyFlag(Connection lConnection) {
    String topCmdtyFlag = "";
    String sqlStr = "SELECT CMDTYFLAG FROM CTGRYMTBL WHERE CTGRYCODE='/'";
    try {
      topCmdtyFlag = SIDBUtil.getFirstData(lConnection, sqlStr);
    } catch (SIDBAccessException e) {
      topCmdtyFlag = SIConfig.SICMDTY_FLAG_ADVICE;
    }
    return topCmdtyFlag;
  }
  
  // 7.2.0 ST0301 追加 ここまで
  // 7.3.0 PI-NES0501 追加
  /**
   * <b>getDispCtgry</b> 第一階層のカテゴリコードを取得
   * 
   * @param lConnection
   * @return ctgryCode
   * @throws なし
   */
  public void setDispCtgry(Connection lConnection) {
    String ctgryCode = "";
    String ctgryName = "";
    StringBuffer sqlStr = new StringBuffer();
    sqlStr.append("SELECT branch FROM ctgryvw WHERE CtgryCode=").append(SIDBUtil.SQL2Str(this.ctgryCodeTxt));
    try {
      ctgryCode = SIDBUtil.getFirstData(lConnection, sqlStr.toString());
      if (ctgryCode.length() < 13) {
        ctgryCode = "";
      } else if (SIUtil.isNotNull(ctgryCode)) {
        ctgryCode = ctgryCode.substring(2, 13);
        ctgryName = SIDBUtil.getFirstData(lConnection, "SELECT ctgryname FROM ctgrymtbl WHERE CtgryCode=" + SIDBUtil.SQL2Str(ctgryCode));
      }
    } catch (SIDBAccessException e) {
      ctgryCode = "";
    }
    this.setDispCtgryCode(ctgryCode);
    this.setDispCtgryName(ctgryName);
    
  }
  
  /**
   * <b>getDispCtgryName</b> SEO対策用にカテゴリ名を取得
   * 
   * @param lConnection
   * @return ctgryCode
   * @throws なし
   */
  public String getDispCtgryName(Connection lConnection) {
    String ctgryName = "";
    StringBuffer sqlStr = new StringBuffer();
    if (SIUtil.isNull(this.ctgryCodeTxt) || this.ctgryCodeTxt.equalsIgnoreCase("/")) return ctgryName;
    sqlStr.append("SELECT ctgryname||',' FROM ctgrynamevw WHERE CtgryCode=").append(SIDBUtil.SQL2Str(this.ctgryCodeTxt));
    try {
      ctgryName = SIDBUtil.getFirstData(lConnection, sqlStr.toString());
      if (SIUtil.isNull(ctgryName)) ctgryName = "";
    } catch (Exception e) {
      ctgryName = "";
    }
    return ctgryName;
  }
  
  /**
   * <b>getCollection</b> 条件を満足する商品一覧リストを取得します。
   * 
   * @param request クライアントからのリクエスト
   * @return 販売可能な商品一覧リスト
   * @throws なし
   */
  public Collection getCollection(Connection lConnection, HttpServletRequest request) throws SIDBAccessException {
    SIUserInfo lUserInfo = new SIUserInfo();
    if (request != null) lUserInfo = SIHTMLUtil.getUserInfo(request);
    // EDBTG005-00 kamata add start
    if (SIURLMap.isSmartPhone(request)) {
        this.setPageSizeSel("20");
    }
    // EDBTG005-00 kamata add end
    boolean naviplusSearch = true;
    naviplusSearch=naviplusSearch||SIUtil.isNotNull(this.getSrchWordTxt());
    naviplusSearch=naviplusSearch&&!"1".equals(this.getNosearch());
    if (naviplusSearch) {
      if (SICheckUtil.isGreater(SIUtil.multi(this.getPageNumberTxt(), this.getPageSizeSel()),"10000")){
        try{
          this.setPageNumberTxt(SIUtil.div("10000", this.getPageSizeSel()));
        }catch (SIFatalException e){
          e.printStackTrace();
          throw new SIDBAccessException();
        }
      }
      SINaviplusSearch lSearch = new SINaviplusSearch();
      lSearch.setQ(this.getSrchWordTxt());
      lSearch.setExq(this.getExcludeWords());
      if (SIUtil.isNotNull(this.getCodes())&&this.getCodes().split(",").length<21) lSearch.setI(this.getCodes().replaceAll(",", ";"));
      lSearch.setPl(this.getPriceFrom());
      lSearch.setPh(this.getPriceTo());
      lSearch.setS(3,0,this.getMaker());
      lSearch.setS(4,0,this.getCtgryCodeTxt());
      if (SIUtil.isNotNull(this.getUsedNewFlgTxt())||SIUtil.isNotNull(this.srchKey.getRadioButton())){
        if ("used".equals(this.getUsedNewFlgTxt())||"used".equals(srchKey.getRadioButton())) {
          lSearch.setS(5,0,"0");
          lSearch.setS(5,1,"3");
          lSearch.setSo(5,"1");
        } else if ("spused".equals(this.getUsedNewFlgTxt())||"spused".equals(srchKey.getRadioButton())) {
          lSearch.setS(5,0,"3");
        } else if ("new".equals(this.getUsedNewFlgTxt())||"new".equals(srchKey.getRadioButton())) {
          lSearch.setS(5,0,"1");
          lSearch.setS(5,1,"6");
          lSearch.setS(5,2,"7");
          lSearch.setSo(5,"1");
        } else if ("outlet".equals(this.getUsedNewFlgTxt())||"outlet".equals(srchKey.getRadioButton())) {
          lSearch.setS(5,0,"2");
          lSearch.setS(5,1,"4");
          lSearch.setSo(5,"1");
        }
      }
      if (SIUtil.isNotNull(this.getActive())&&!"advancedsearch".equals(this.getActive())&&!"eyelashgarage".equals(this.getActive())) {
        lSearch.setS(6,0,this.getActive());
      } else {//eyelashgarageを含まない
        for (int i=0;i<SIConfig.ctgryGroupCode.length;i++) {
          lSearch.setS(6,i,SIConfig.ctgryGroupCode[i]);
        }
        lSearch.setSo(6, "1");
      }
      if ("0".equals(this.getCmdtySortSel())) {
        lSearch.setSort("price");
      } else if("1".equals(this.getCmdtySortSel())) {
        lSearch.setSort("Price");
      } else if("2".equals(this.getCmdtySortSel())) {
        if (SIUtil.isNotNull(this.getSrchWordTxt())) {
          lSearch.setSort("Score");
        } else {
          lSearch.setSort("Number1");
        }
      } else if("4".equals(this.getCmdtySortSel())) {
        lSearch.setSort("number20");
      } else if("5".equals(this.getCmdtySortSel())) {
        lSearch.setSort("Number2");
      } else {
        lSearch.setSort("Score");
      }
      lSearch.setLimit(this.getPageSizeSel());
      lSearch.setO(SIUtil.multi(SIUtil.sub(this.getPageNumberTxt(),"1"),this.getPageSizeSel()));
      lSearch.setSearchbox(this.getSearchbox());
      return lSearch.getCollection(lUserInfo,SITax.getTaxRate(lConnection));
    }
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SICmdty lCmdty = new SICmdty();
    Collection lCmdtys = new ArrayList();
    StringBuffer makerString = new StringBuffer();
    if (lUserInfo.getMaker().length > 0) {
      makerString = new StringBuffer("(");
      for (int i = 0; i < lUserInfo.getMaker().length; i++) {
        makerString.append(SIDBUtil.SQL2Str(lUserInfo.getMaker()[i]));
        makerString.append(i < lUserInfo.getMaker().length - 1 ? "," : ")");
      }
    }
    
    StringBuffer lCtrgySqlBuf = new StringBuffer();// 対象カテゴリに所属する商品レコードのセット
    lCtrgySqlBuf.append(",(SELECT a2.shopCode,a2.CmdtyCode ");
    lCtrgySqlBuf.append("FROM CmdtyCtgryMTbl a2 ");
    /*
    if (SIUtil.isNotNull(getCtgryConditionSQL())) {
      lCtrgySqlBuf.append(",CtgryVW c2 ");
      lCtrgySqlBuf.append("WHERE a2.CtgryCode=c2.CtgryCode AND ");
      lCtrgySqlBuf.append(getCtgryConditionSQL());
    }*/
    lCtrgySqlBuf.append("GROUP BY a2.shopCode,a2.CmdtyCode) b1");
    
    StringBuffer lCountSqlBuf = new StringBuffer("SELECT Count(*) ");// 商品の数を求める
    
    StringBuffer lCmdtySqlBuf = new StringBuffer();// 商品レコードの情報を求める
    lCmdtySqlBuf.append("SELECT a1.*, color.colorname,COALESCE((SELECT taxrate FROM taxvw),0) AS TaxRate, ");
    lCmdtySqlBuf.append("CASE WHEN a1.InitDateTime + (SELECT daysofnew FROM MallShopMTbl) >= CURRENT_DATE THEN '1' ");
    lCmdtySqlBuf.append("ELSE '0' END AS NewArrivalFlg ");
    /*
    lCmdtySqlBuf.append(",CASE WHEN a1.cmdtycode");
    lCmdtySqlBuf.append(" IN(SELECT cc.cmdtycode FROM cmdtyctgrymtbl cc,ctgryvw ct WHERE cc.ctgrycode = ct.ctgrycode");
    lCmdtySqlBuf.append(" AND (");
    for (int i=0;i<SIConfig.LOGINCTGRY.length;i++){
      if (i>0) lCmdtySqlBuf.append(" OR ");
      lCmdtySqlBuf.append(" (ct.branch LIKE '%~").append(SIDBUtil.SQL2Like(SIConfig.LOGINCTGRY[i])).append("%'");
      lCmdtySqlBuf.append(" OR ct.ctgrycode = ").append(SIDBUtil.SQL2Str(SIConfig.LOGINCTGRY[i], ")"));
    }
    lCmdtySqlBuf.append(")) THEN '1' ELSE '0' END AS loginflg ");
    */
    lCmdtySqlBuf.append(",CASE WHEN a1.contractflg = 0 THEN 0");
    if(SIUtil.isNotNull(makerString.toString())){
      lCmdtySqlBuf.append(" WHEN a1.makercode IN ").append(makerString).append(" THEN 0");
    }
    lCmdtySqlBuf.append(" ELSE 1 END AS contractflg ");
    lCmdtySqlBuf.append(",(a1.amount-a1.bgpamount) AS amountR ");//rakutenAppend
    lCmdtySqlBuf.append(",CASE WHEN point2.BonusPointRate IS NOT NULL THEN point2.BonusPointRate WHEN point.BonusPointRate IS NOT NULL THEN point.BonusPointRate ELSE NULL END AS pointRate ");
    lCmdtySqlBuf.append(",(SELECT CASE WHEN bonusfromdate <= current_date AND bonustodate >= current_date THEN bonuspointrate ELSE pointrate END FROM pointshopmtbl) AS nomalPointRate ");
    lCmdtySqlBuf.append(",(SELECT CASE WHEN bonusfromdate <= current_date AND bonustodate >= current_date THEN specialrate ELSE 1.0 END FROM pointshopmtbl) AS specialRate ");
    if (getCmdtySortSel().equals("5")) lCmdtySqlBuf.append(",coalesce(ranking.price,0) AS rankingprice ");
    if (lUserInfo.isLogin(request)&&SIUtil.isNotNull(lUserInfo.getCustCode())) {
      lCmdtySqlBuf.append(",(SELECT 1 FROM mycatalogtbl ");
      lCmdtySqlBuf.append(" WHERE custcode=").append(SIDBUtil.SQL2Str(lUserInfo.getCustCode()));
      lCmdtySqlBuf.append(" AND individualcode=a1.individualcode) AS mycatalogflg ");
    } else {
      lCmdtySqlBuf.append(",'' AS mycatalogFlg ");
    }
    
    StringBuffer countBodySqlBuf = new StringBuffer();
    countBodySqlBuf.append(" FROM ");
    countBodySqlBuf.append(" (SELECT unit.*,cmdty.expansioncomment1text,cmdty.expansioncomment2text,cmdty.expansioncomment3text ");
    countBodySqlBuf.append(" ,maker.makername,maker.othername1,maker.othername2");
    countBodySqlBuf.append(" ,CASE WHEN unit.deliveryTypeCode='0' AND unit.cmdtySize IN ('1','7') THEN 1 ");//送料無料（個別AGサイズ）
    countBodySqlBuf.append("       WHEN unit.deliveryTypeCode='1' AND unit.unitPrice>=feerule.price THEN 1 ");//送料無料（小物1万円以上）
    countBodySqlBuf.append("  ELSE 0 END AS freedeliveryflg ");
    countBodySqlBuf.append(" ,CASE WHEN unit.deliveryTypeCode='0' AND unit.cmdtySize IN ('1','7') THEN unit.cmdtyName||'【送料無料】' ");
    countBodySqlBuf.append("       WHEN unit.deliveryTypeCode='1' AND unit.unitPrice>=feerule.price THEN unit.cmdtyName||'【送料無料】' ");
    countBodySqlBuf.append("  ELSE unit.cmdtyname END AS cmdtynamewithfree ");
    countBodySqlBuf.append("  FROM cmdtyunittbl unit,individualtbl cmdty,feerulemtbl feerule");
    countBodySqlBuf.append(" ,(SELECT CASE WHEN makercode IN ('1230','1231','1232') THEN 'ポール･シェリーM-products' ELSE makername END AS makername,othername1,othername2,makercode,contractflg FROM makertbl) maker ");
    countBodySqlBuf.append("  WHERE unit.cmdtycode=cmdty.cmdtycode AND unit.individualcode=cmdty.individualcode AND unit.makercode=maker.makercode AND unit.deliverytypecode=feerule.deliverytypecode) a1 ");
    countBodySqlBuf.append(" ,colortbl color");
    countBodySqlBuf.append(lCtrgySqlBuf.toString());
    countBodySqlBuf.append(" WHERE " + getCmdtyFlagSQL("a1"));
    countBodySqlBuf.append(" AND a1.CmdtyCode=b1.CmdtyCode AND a1.ShopCode=b1.ShopCode ");
    countBodySqlBuf.append(" AND a1.ColorCode=color.ColorCode ");
    countBodySqlBuf.append(" AND a1.frontdispflg='1' ");
    //if(!lUserInfo.isLogin()) countBodySqlBuf.append(" AND a1.contractflg=0 ");
    countBodySqlBuf.append(" AND NOT(a1.usedNewFlg!='1' AND a1.usedNewFlg!='7' AND a1.amountflg='1' AND a1.amount='0' ");
    countBodySqlBuf.append("  AND (a1.rsrvenableflg='0' OR (a1.rsrvamount IS NOT NULL AND a1.rsrvamount='0')) ");
    countBodySqlBuf.append("  AND (a1.soldoutdate IS NULL OR a1.soldoutdate + "+SIConfig.DEFAULT_SOLDOUT+" <= CURRENT_DATE)) ");
    countBodySqlBuf.append(" AND a1.individualcode IN ");
    countBodySqlBuf.append("(SELECT i.individualcode ");
    countBodySqlBuf.append(" FROM individualtbl i,cmdtyctgrymtbl cc,ctgryvw v,ctgrymtbl ct LEFT OUTER JOIN ctgrygrouptbl g ON ct.ctgrycode=g.ctgrycode ");
    countBodySqlBuf.append(" WHERE cc.cmdtycode=i.cmdtycode AND cc.ctgrycode=v.ctgrycode ");
    countBodySqlBuf.append(" AND (v.branch LIKE '%~'||ct.ctgrycode||'~%' OR v.branch LIKE '%~'||ct.ctgrycode OR ct.ctgrycode='/') ");
    countBodySqlBuf.append(" AND g.ctgrygroup<>'eyelashgarage') ");
    
    StringBuffer searchBodySqlBuf = new StringBuffer();
    searchBodySqlBuf.append(" FROM ");
    searchBodySqlBuf.append(" (SELECT unit.*,cmdty.expansioncomment1text,cmdty.expansioncomment2text,cmdty.expansioncomment3text ");
    searchBodySqlBuf.append(" ,CASE WHEN unit.deliveryTypeCode='0' AND unit.cmdtySize IN ('1','7') THEN 1 ");//送料無料（個別AGサイズ）
    searchBodySqlBuf.append("       WHEN unit.deliveryTypeCode='1' AND unit.unitPrice>=feerule.price THEN 1 ");//送料無料（小物1万円以上）
    searchBodySqlBuf.append("  ELSE 0 END AS freedeliveryflg ");
    searchBodySqlBuf.append(" ,CASE WHEN unit.deliveryTypeCode='0' AND unit.cmdtySize IN ('1','7') THEN unit.cmdtyName||'【送料無料】' ");
    searchBodySqlBuf.append("       WHEN unit.deliveryTypeCode='1' AND unit.unitPrice>=feerule.price THEN unit.cmdtyName||'【送料無料】' ");
    searchBodySqlBuf.append("  ELSE unit.cmdtyname END AS cmdtynamewithfree ");
    searchBodySqlBuf.append(" ,maker.makername,maker.othername1,maker.othername2,maker.contractflg");
    searchBodySqlBuf.append("  FROM cmdtyunittbl unit,individualtbl cmdty,feerulemtbl feerule ");
    searchBodySqlBuf.append(" ,(SELECT CASE WHEN makercode IN ('1230','1231','1232') THEN 'ポール･シェリーM-products' ELSE makername END AS makername,othername1,othername2,makercode,contractflg FROM makertbl) maker ");
    searchBodySqlBuf.append("  WHERE unit.cmdtycode=cmdty.cmdtycode AND unit.individualcode=cmdty.individualcode AND unit.makercode=maker.makercode AND unit.deliverytypecode=feerule.deliverytypecode) a1 ");
    if (getCmdtySortSel().equals("5")) searchBodySqlBuf.append(" LEFT OUTER JOIN rankingbasetbl ranking ON a1.individualcode=ranking.individualcode ");
    searchBodySqlBuf.append(" LEFT OUTER JOIN (SELECT bonuspointrate,ctgrycode FROM bonuspointtbl WHERE bonusfromdate <= current_date AND bonustodate >= current_date ) point ");
    searchBodySqlBuf.append(" ON(substring(a1.individualcode,0,3) = point.ctgrycode) ");
    searchBodySqlBuf.append(" LEFT OUTER JOIN (SELECT bonuspointrate,cmdtycode,individualcode FROM bonuspointindividualtbl WHERE bonusfromdate <= current_date AND bonustodate >= current_date ) point2 ");
    searchBodySqlBuf.append(" ON(a1.cmdtycode = point2.cmdtycode AND a1.individualcode = point2.individualcode) ");
    searchBodySqlBuf.append(" ,colortbl color");
    //searchBodySqlBuf.append(" ,(SELECT individualcode,amount FROM storestocktbl WHERE branchcode='19') rakuten ");//rakutenAppend
    searchBodySqlBuf.append(lCtrgySqlBuf.toString());
    searchBodySqlBuf.append(" WHERE " + getCmdtyFlagSQL("a1"));
    searchBodySqlBuf.append(" AND a1.CmdtyCode=b1.CmdtyCode AND a1.ShopCode=b1.ShopCode ");
    searchBodySqlBuf.append(" AND a1.ColorCode=color.ColorCode ");
    searchBodySqlBuf.append(" AND a1.frontdispflg='1' ");
    //searchBodySqlBuf.append(" AND a1.individualcode=rakuten.individualcode ");//rakutenAppend
    //if(!lUserInfo.isLogin()) searchBodySqlBuf.append(" AND a1.contractflg=0 ");
    searchBodySqlBuf.append(" AND NOT(a1.usedNewFlg!='1' AND a1.usedNewFlg!='7' AND a1.amountflg='1' AND a1.amount='0' ");
    searchBodySqlBuf.append("  AND (a1.rsrvenableflg='0' OR (a1.rsrvamount IS NOT NULL AND a1.rsrvamount='0')) ");
    searchBodySqlBuf.append("  AND (a1.soldoutdate IS NULL OR a1.soldoutdate + "+SIConfig.DEFAULT_SOLDOUT+" <= CURRENT_DATE)) ");
    searchBodySqlBuf.append(" AND a1.individualcode IN ");
    searchBodySqlBuf.append("(SELECT i.individualcode ");
    searchBodySqlBuf.append(" FROM individualtbl i,cmdtyctgrymtbl cc,ctgryvw v,ctgrymtbl ct LEFT OUTER JOIN ctgrygrouptbl g ON ct.ctgrycode=g.ctgrycode ");
    searchBodySqlBuf.append(" WHERE cc.cmdtycode=i.cmdtycode AND cc.ctgrycode=v.ctgrycode ");
    searchBodySqlBuf.append(" AND (v.branch LIKE '%~'||ct.ctgrycode||'~%' OR v.branch LIKE '%~'||ct.ctgrycode OR ct.ctgrycode='/') ");
    searchBodySqlBuf.append(" AND g.ctgrygroup<>'eyelashgarage') ");
    
    // 検索の条件
    countBodySqlBuf.append(this.getConditionSQL());
    lCountSqlBuf.append(countBodySqlBuf);
    
    // 出力順
    searchBodySqlBuf.append(this.getConditionSQL()).append(this.getOrderBySQL());
    lCmdtySqlBuf.append(searchBodySqlBuf);
    // 実行
    try {
      int lRecordCount = Integer.parseInt(SIDBUtil.getFirstData(lConnection, lCountSqlBuf.toString()));
      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);
      log.debug("getCollection:lCmdtySqlBuf=" + lCmdtySqlBuf.toString());
      
      lStatement = lConnection.createStatement();
      lCmdtySqlBuf.append(" LIMIT ").append(lPageSize);
      if (lFromInx>0) lCmdtySqlBuf.append(" OFFSET ").append(lFromInx);
      lResultSet = lStatement.executeQuery(lCmdtySqlBuf.toString());
      
      for (int jj = 0; jj < lFromInx; jj++)
        lCmdtys.add(null);
      
      // 商品レコードのセットの作成
      int lIndex = 0;
      while (lResultSet.next() && lIndex < lPageSize) {
        lCmdty = new SICmdty();
        lCmdty.setEncode(SIConfig.SIENCODE_NONE);
        lCmdty.setMemberDiscountFlg(lResultSet.getString("MemberDiscountFlg"));
        lCmdty.setMemberDiscountRate(lUserInfo.getDiscountRate());
        //lCmdty.setShopCode(lResultSet.getString("ShopCode"));
        //lCmdty.setFrontShopName(lResultSet.getString("FrontShopName"));
        lCmdty.setCmdtyCode(lResultSet.getString("CmdtyCode"));
        lCmdty.setIndividualCode(lResultSet.getString("IndividualCode"));
        lCmdty.setCmdtyName(lResultSet.getString("CmdtyName"));
        //lCmdty.setDescription(lResultSet.getString("Description"));
        lCmdty.setUnitPrice(lResultSet.getString("UnitPrice"));
        lCmdty.setUnitPriceWithoutTax(lResultSet.getString("UnitPriceWithoutTax"));
        lCmdty.setTaxFlg(lResultSet.getString("taxFlg"));// 税区分
        //lCmdty.setAdviceFlg(lResultSet.getString("AdviceFlg"));
        lCmdty.setCalcUnitPrice(lResultSet.getString("UnitPrice"));
        lCmdty.setAmount(lResultSet.getString("AmountR"));// 在庫数量
        lCmdty.setColorCode(lResultSet.getString("ColorCode"));// 色コード
        lCmdty.setMakerCode(lResultSet.getString("MakerCode"));// ブランドコード
        lCmdty.setTenureOfUse(lResultSet.getInt("TenureOfUse"));// 使用年数（月単位）
        lCmdty.setGuaranteedTerm(lResultSet.getInt("GuaranteedTerm"));// 保証期間（月単位）
        lCmdty.setNewFixedPrice(lResultSet.getString("NewFixedPrice"));// M価
        lCmdty.setBGPriceDispFlg(lResultSet.getString("BGPriceDispFlg"));// BG卸価フロント表示フラグ
        lCmdty.setUsedNewFlg(lResultSet.getInt("UsedNewFlg"));// 中古・新品フラグ
        //lCmdty.setSalesSituationFlg(lResultSet.getString("SalesSituationFlg"));// 販売状況
        //lCmdty.setSoldoutDispFlg(lResultSet.getString("SoldoutDispFlg"));// SOLDOUT後表示
        lCmdty.setCampaignPriceFlg(lResultSet.getString("CampaignPriceFlg"));// キャンペーン特価区分
        lCmdty.setNewArrivalFlg(lResultSet.getString("NewArrivalFlg"));// 新着フラグ
        lCmdty.setInquiryFlg(lResultSet.getString("InquiryFlg"));// 問合せ区分
        lCmdty.setColorName(lResultSet.getString("ColorName"));// 色名
        lCmdty.setMakerName(lResultSet.getString("MakerName"));// ブランド名
        lCmdty.setTaxRate(lResultSet.getString("TaxRate"));// 税率
        lCmdty.setRsrvAmount(lResultSet.getString("rsrvAmount"));// 予約可能数量
        lCmdty.setRsrvEnableFlg(lResultSet.getString("rsrvEnableFlg"));// 予約販売フラグ
        lCmdty.setAmountFlg(lResultSet.getString("AmountFlg"));// 在庫管理フラグ
        lCmdty.setArrivalScheduleMsg(lResultSet.getString("ArrivalScheduleMsg"));
        //lCmdty.setLoginFlg(lResultSet.getString("loginflg").equals("1"));
        lCmdty.setContractFlg(lResultSet.getString("contractflg").equals("1"));
        lCmdty.setPointRate(lResultSet.getString("pointRate"));// ボーナスポイントレート
        lCmdty.setNomalPointRate(lResultSet.getString("nomalPointRate"));// 通常ポイントレート
        lCmdty.setSpecialRate(lResultSet.getString("specialRate"));//ボーナス倍率
        lCmdty.setFreeDeliveryFlg(lResultSet.getString("freeDeliveryFlg"));
        lCmdty.setPriceDownFlg(lResultSet.getString("priceDownFlg"));
        lCmdty.setMycatalogFlg(lResultSet.getString("mycatalogFlg"));
        if ("H".equals(lResultSet.getString("IndividualCode").substring(0,1))||"V".equals(lResultSet.getString("IndividualCode").substring(0,1))||"L".equals(lResultSet.getString("IndividualCode").substring(0,1))){
          if ("1".equals(lUserInfo.getHairCosmeFlg())) {
            lCmdty.setHairCosmeFlg(false);
          }else{
            lCmdty.setHairCosmeFlg(true);
            lCmdty.setContractFlg(true);
          }
        }else{
          lCmdty.setHairCosmeFlg(false);
        }
        // EDBTG003-00 nagayoshi add start
        lCmdty.setCmdtyCompositionFlg(lResultSet.getString("cmdtyCompositionFlg"));
        // EDBTG003-00 nagayoshi add end
        lCmdtys.add(lCmdty);
        lIndex++;
      }
      for (int jj = lFromInx + lPageSize; jj < lRecordCount; jj++)
        lCmdtys.add(null);
    } catch (Exception ex) {
      ex.printStackTrace();
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lCmdtys;
  }
  
  public Collection getAdviceCmdty(Connection lConnection, HttpServletRequest request) throws SIDBAccessException {
    return getAdviceCmdty(lConnection, request, 2);
  }
  public Collection getAdviceCmdty(Connection lConnection, HttpServletRequest request, int limit) throws SIDBAccessException {
    SIUserInfo lUserInfo = new SIUserInfo();
    if (request != null) lUserInfo = SIHTMLUtil.getUserInfo(request);
    
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SICmdty lCmdty = new SICmdty();
    Collection lCmdtys = new ArrayList();
    StringBuffer lSqlBuf = new StringBuffer();
    StringBuffer makerString = new StringBuffer();
    if (lUserInfo.getMaker().length > 0) {
      makerString = new StringBuffer("(");
      for (int i = 0; i < lUserInfo.getMaker().length; i++) {
        makerString.append(SIDBUtil.SQL2Str(lUserInfo.getMaker()[i]));
        makerString.append(i < lUserInfo.getMaker().length - 1 ? "," : ")");
      }
    }
    lSqlBuf.append("SELECT a.shopcode,a.cmdtycode,a.individualcode,a.cmdtyname,a.adviceflg,a.usednewflg,a.memberdiscountflg");
    // EDBTG003-00 nagayoshi add start
    lSqlBuf.append(",a.cmdtyCompositionFlg");
    // EDBTG003-00 nagayoshi add end
    lSqlBuf.append(",a.unitprice,a.taxflg,a.newfixedprice,a.bgpricedispflg,COALESCE((SELECT taxrate FROM taxvw),0) AS TaxRate,a.unitpricewithouttax ");
    lSqlBuf.append(",CASE WHEN maker.contractflg='0' THEN 0");
    if(SIUtil.isNotNull(makerString.toString())){
      lSqlBuf.append(" WHEN maker.makercode IN ").append(makerString).append(" THEN 0");
    }
    lSqlBuf.append(" ELSE 1 END AS contractflg ");
    lSqlBuf.append(" ,CASE WHEN a.deliveryTypeCode='0' AND a.cmdtySize IN ('1','7') THEN 1 ");//送料無料（個別AGサイズ）
    lSqlBuf.append("       WHEN a.deliveryTypeCode='1' AND a.unitPrice>=feerule.price THEN 1 ");//送料無料（小物1万円以上）
    lSqlBuf.append("  ELSE 0 END AS freedeliveryflg ");
    lSqlBuf.append("FROM cmdtyunittbl a,makertbl maker,recommenditemtbl rec,feerulemtbl feerule ");
    lSqlBuf.append("WHERE rec.ctgrygroup='top' AND a.frontdispflg='1' AND a.makercode=maker.makercode AND a.individualcode=rec.individualcode ");
    lSqlBuf.append("AND NOT(a.usednewflg!='1' AND a.usednewflg!='7' AND a.amountflg='1' AND (a.amount-a.bgpamount)='0' ");
    lSqlBuf.append("AND a.deliverytypecode = feerule.deliverytypecode ");
    lSqlBuf.append(" AND (a.rsrvenableflg='0' OR (a.rsrvamount IS NOT NULL AND a.rsrvamount='0')) ");
    lSqlBuf.append(" AND (a.soldoutdate IS NULL OR a.soldoutdate + "+SIConfig.DEFAULT_SOLDOUT+" <= CURRENT_DATE)) ");
    lSqlBuf.append("ORDER BY random() LIMIT ").append(String.valueOf(limit));
    
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      // 商品レコードのセットの作成
      while (lResultSet.next()) {
        lCmdty = new SICmdty();
        lCmdty.setEncode(SIConfig.SIENCODE_NONE);
        lCmdty.setShopCode(lResultSet.getString("ShopCode"));
        lCmdty.setCmdtyCode(lResultSet.getString("CmdtyCode"));
        lCmdty.setIndividualCode(lResultSet.getString("IndividualCode"));
        lCmdty.setCmdtyName(lResultSet.getString("CmdtyName"));
        lCmdty.setUnitPrice(lResultSet.getString("UnitPrice"));
        lCmdty.setUnitPriceWithoutTax(lResultSet.getString("UnitPriceWithoutTax"));
        lCmdty.setTaxFlg(lResultSet.getString("taxFlg"));// 税区分
        lCmdty.setAdviceFlg(lResultSet.getString("AdviceFlg"));
        lCmdty.setCalcUnitPrice(lResultSet.getString("UnitPrice"));
        lCmdty.setNewFixedPrice(lResultSet.getString("NewFixedPrice"));// M価
        lCmdty.setBGPriceDispFlg(lResultSet.getString("BGPriceDispFlg"));// BG卸価フロント表示フラグ
        lCmdty.setUsedNewFlg(lResultSet.getInt("UsedNewFlg"));// 中古・新品フラグ
        lCmdty.setTaxRate(lResultSet.getString("TaxRate"));// 税率
        lCmdty.setContractFlg(lResultSet.getString("contractFlg").equals("1"));
        if ("H".equals(lResultSet.getString("IndividualCode").substring(0,1))||"V".equals(lResultSet.getString("IndividualCode").substring(0,1))||"L".equals(lResultSet.getString("IndividualCode").substring(0,1))){
          if ("1".equals(lUserInfo.getHairCosmeFlg())) {
            lCmdty.setHairCosmeFlg(false);
          }else{
            lCmdty.setHairCosmeFlg(true);
            lCmdty.setContractFlg(true);
          }
        }else{
          lCmdty.setHairCosmeFlg(false);
        }
        lCmdty.setFreeDeliveryFlg(lResultSet.getString("freeDeliveryFlg"));
        lCmdty.setMemberDiscountFlg(lResultSet.getString("memberDiscountFlg"));
        lCmdty.setMemberDiscountRate(lUserInfo.getDiscountRate());
        // EDBTG003-00 nagayoshi add start
        lCmdty.setCmdtyCompositionFlg(lResultSet.getString("cmdtyCompositionFlg"));
        // EDBTG003-00 nagayoshi add end
        lCmdtys.add(lCmdty);
      }
    } catch (SQLException e) {
      e.printStackTrace();
      throw new SIDBAccessException(e);
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lCmdtys;
  }
  
  public boolean loginCheck(Connection lConnection) {
    return loginCheck(lConnection, this.ctgryCodeTxt);
  }
  
  public boolean loginCheck(Connection lConnection, String lCtgryCode) {
    StringBuffer lSqlStr = new StringBuffer();
    lSqlStr.append("SELECT ctgrycode FROM ctgryvw WHERE ctgrycode = ").append(SIDBUtil.SQL2Str(lCtgryCode));
    lSqlStr.append(" AND (");
    for (int i=0;i<SIConfig.LOGINCTGRY.length;i++){
      if (i>0) lSqlStr.append(" OR ");
      lSqlStr.append(" (branch LIKE '%~").append(SIDBUtil.SQL2Like(SIConfig.LOGINCTGRY[i])).append("%'");
      lSqlStr.append(" OR branch LIKE '%~").append(SIDBUtil.SQL2Like(SIConfig.LOGINCTGRY[i])).append("')");
    }
    lSqlStr.append(") ");
    try {
      return !SIDBUtil.hasData(lConnection, lSqlStr.toString());
    } catch (Exception e) {
      e.printStackTrace();
      return false;
    }
  }
  
  public int loginPageCount(Connection lConnection) {
    return loginPageCount(lConnection, this.ctgryCodeTxt);
  }
  
  public int loginPageCount(Connection lConnection, String lCtgryCode) {
    StringBuffer lSqlStr = new StringBuffer();
    lSqlStr.append("SELECT CASE g.ctgrygroup WHEN 'cosme' THEN 1 WHEN 'nail' THEN 2 WHEN 'haircosme' THEN 3 ELSE 0 END AS cnt ");
    lSqlStr.append("FROM ctgrymtbl c,ctgrygrouptbl g ");
    lSqlStr.append("WHERE c.ctgrycode = ").append(SIDBUtil.SQL2Str(lCtgryCode," "));
    lSqlStr.append("AND (c.ctgrycode = g.ctgrycode OR c.parentctgrycode=g.ctgrycode) ");
    lSqlStr.append("AND CASE WHEN g.ctgrygroup='haircosme' AND g.ctgrycode='LM' THEN false ");//ヘア水物のメイクアップは除外
    lSqlStr.append("WHEN g.ctgrygroup='cosme' AND g.ctgrycode='category018' THEN false ");//エステ化粧品のまつ毛商材は除外
    lSqlStr.append("ELSE true END ");
    lSqlStr.append("ORDER BY cnt DESC ");
    try {
      String resStr = SIDBUtil.getFirstData(lConnection, lSqlStr.toString());
      return Integer.parseInt(resStr);
    } catch (Exception e) {
      e.printStackTrace();
      return 0;
    }
  }
  
  public String getTitleCtgry(Connection lConnection,String path) throws SIDBAccessException {
    String res = "";
    String ctgryGroup = "";
    if (SIUtil.isNotNull(path)&&path.equals("/hair")) ctgryGroup = "hair";
    else if (SIUtil.isNotNull(path)&&path.equals("/esthe")) ctgryGroup = "esthe";
    else if (SIUtil.isNotNull(path)&&path.equals("/bed")) ctgryGroup = "bed";
    else if (SIUtil.isNotNull(path)&&path.equals("/cosme")) ctgryGroup = "cosme";
    else if (SIUtil.isNotNull(path)&&path.equals("/nail")) ctgryGroup = "nail";
    else if (SIUtil.isNotNull(path)&&path.equals("/haircosme")) ctgryGroup = "haircosme";
    else if (SIUtil.isNotNull(path)&&path.equals("/book")) ctgryGroup = "book";
    else if (SIUtil.isNotNull(path)&&path.equals("/eyelash")) ctgryGroup = "eyelash";
    
    res = SIDBUtil.getFirstData(lConnection, "SELECT a.ctgrycode FROM ctgrymtbl a,ctgrygrouptbl b WHERE a.ctgrycode=b.ctgrycode AND b.ctgrygroup="+SIDBUtil.SQL2Str(ctgryGroup)+" AND a.ctgrycode="+SIDBUtil.SQL2Str(getCtgryCodeTxt()));
    if (SIUtil.isNotNull(res)) return res;
    res = SIDBUtil.getFirstData(lConnection, "SELECT a.ctgrycode FROM ctgrymtbl a,ctgrygrouptbl b,ctgrymtbl c WHERE a.ctgrycode=b.ctgrycode AND c.parentctgrycode=b.ctgrycode AND b.ctgrygroup="+SIDBUtil.SQL2Str(ctgryGroup)+" AND c.ctgrycode="+SIDBUtil.SQL2Str(getCtgryCodeTxt()));
    if (SIUtil.isNotNull(res)) return res;
    return SIConfig.SICATEGORY_ROOT_CODE;
  }
  
  public Collection getChildCtgry(Connection lConnection,String path) throws SIDBAccessException {
    Collection res;
    String ctgryGroup = "";
    if (SIUtil.isNotNull(path)&&path.equals("/hair")) ctgryGroup = "hair";
    else if (SIUtil.isNotNull(path)&&path.equals("/esthe")) ctgryGroup = "esthe";
    else if (SIUtil.isNotNull(path)&&path.equals("/bed")) ctgryGroup = "bed";
    else if (SIUtil.isNotNull(path)&&path.equals("/cosme")) ctgryGroup = "cosme";
    else if (SIUtil.isNotNull(path)&&path.equals("/nail")) ctgryGroup = "nail";
    else if (SIUtil.isNotNull(path)&&path.equals("/haircosme")) ctgryGroup = "haircosme";
    else if (SIUtil.isNotNull(path)&&path.equals("/book")) ctgryGroup = "book";
    else if (SIUtil.isNotNull(path)&&path.equals("/eyelash")) ctgryGroup = "eyelash";
    
    StringBuffer lSql1 = new StringBuffer();
    lSql1.append("SELECT 'すべて',ctgrycode,'' AS abbvctgryname FROM ctgrymtbl WHERE ctgrycode=").append(SIDBUtil.SQL2Str(getCtgryCodeTxt()," UNION "));
    lSql1.append("SELECT ctgryname,ctgrycode,abbvctgryname FROM ctgrymtbl ");
    lSql1.append("WHERE parentctgrycode IN (SELECT ctgrycode FROM ctgrygrouptbl WHERE ctgrygroup=").append(SIDBUtil.SQL2Str(ctgryGroup,") "));
    lSql1.append("AND parentctgrycode=").append(SIDBUtil.SQL2Str(getCtgryCodeTxt()," "));
    for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
      lSql1.append("AND ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
    }
    lSql1.append("ORDER BY abbvctgryname ");
    
    StringBuffer lSql2 = new StringBuffer();
    lSql2.append("SELECT 'すべて',parentctgrycode,'' AS abbvctgryname FROM ctgrymtbl WHERE ctgrycode=").append(SIDBUtil.SQL2Str(getCtgryCodeTxt()," UNION "));
    lSql2.append("SELECT a.ctgryname,a.ctgrycode,a.abbvctgryname FROM ctgrymtbl a,ctgrymtbl b ");
    lSql2.append("WHERE a.parentctgrycode IN (SELECT ctgrycode FROM ctgrygrouptbl WHERE ctgrygroup=").append(SIDBUtil.SQL2Str(ctgryGroup,") "));
    lSql2.append("AND a.parentctgrycode=b.parentctgrycode ");
    lSql2.append("AND b.ctgrycode=").append(SIDBUtil.SQL2Str(getCtgryCodeTxt()," "));
    for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
      lSql2.append("AND a.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
    }
    lSql2.append("ORDER BY abbvctgryname ");
    
    res = SIDBUtil.getCollection(lConnection, lSql1.toString());
    if (res==null||res.size()<=1) {
      res = SIDBUtil.getCollection(lConnection, lSql2.toString());
    }
    if (res==null||res.size()<=1) res = new ArrayList();
    
    return res;
  }
  
  //ネイル・まつ毛・エクステ
  public boolean isExCtgry(Connection lConnection) {
    boolean res = "category013".equals(getCtgryCodeTxt())||"category014".equals(getCtgryCodeTxt())||"category018".equals(getCtgryCodeTxt());
    if (res) return res;
    try{
      //小カテ
      res = SIDBUtil.hasData(lConnection, "SELECT * FROM ctgrymtbl WHERE parentctgrycode IN('category013','category014','category018') AND ctgrycode="+SIDBUtil.SQL2Str(getCtgryCodeTxt()));
      //孫カテ
      res = res || SIDBUtil.hasData(lConnection, "SELECT c1.* FROM ctgrymtbl c1,ctgrymtbl c2 WHERE c1.parentctgrycode=c2.ctgrycode AND c2.parentctgrycode IN('category013','category014','category018') AND c1.ctgrycode="+SIDBUtil.SQL2Str(getCtgryCodeTxt()));
    }catch(Exception e){
      e.printStackTrace();
      res = false;
    }
    return res;
  }
  
  public void setCtgryConditionSQL(String lAliasName, String lFromCtgryCode,String path) {
    if (SIUtil.isNull(lFromCtgryCode)) lFromCtgryCode = SIConfig.SICATEGORY_ROOT_CODE;
    if (SIUtil.isNull(lAliasName)) lAliasName = "";
    else lAliasName = lAliasName + ".";
    String ctgryGroup = "";
    if (SIUtil.isNotNull(path)&&path.equals("/hair")) ctgryGroup = "hair";
    else if (SIUtil.isNotNull(path)&&path.equals("/esthe")) ctgryGroup = "esthe";
    else if (SIUtil.isNotNull(path)&&path.equals("/bed")) ctgryGroup = "bed";
    else if (SIUtil.isNotNull(path)&&path.equals("/cosme")) ctgryGroup = "cosme";
    else if (SIUtil.isNotNull(path)&&path.equals("/nail")) ctgryGroup = "nail";
    else if (SIUtil.isNotNull(path)&&path.equals("/haircosme")) ctgryGroup = "haircosme";
    else if (SIUtil.isNotNull(path)&&path.equals("/book")) ctgryGroup = "book";
    else if (SIUtil.isNotNull(path)&&path.equals("/eyelash")) ctgryGroup = "eyelash";
    
    StringBuffer lResBuf = new StringBuffer("(");
    if (lFromCtgryCode.equals(SIConfig.SICATEGORY_ROOT_CODE)) {
      lResBuf.append(lAliasName);
      lResBuf.append("ctgrycode IN ");
      lResBuf.append("(SELECT a.ctgrycode FROM ctgryvw a,ctgrygrouptbl b ");
      lResBuf.append("WHERE (a.branch LIKE '%~'||b.ctgrycode||'~%' OR a.branch LIKE '%~'||b.ctgrycode) ");
      lResBuf.append("AND b.ctgrygroup=").append(SIDBUtil.SQL2Str(ctgryGroup,")) "));
    } else {
      lResBuf.append(lAliasName);
      lResBuf.append("branch LIKE '%~" + lFromCtgryCode + "~%' ");
      lResBuf.append(" OR " + lAliasName);
      lResBuf.append("branch LIKE '%~" + lFromCtgryCode + "') ");
    }
    this.setCtgryConditionSQL(lResBuf.toString());
  }
  
  public void setCtgryConditionSQLL(String lAliasName, String lFromCtgryCode) {
    if (SIUtil.isNull(lFromCtgryCode)) lFromCtgryCode = SIConfig.SICATEGORY_ROOT_CODE;
    if (lFromCtgryCode.equals(SIConfig.SICATEGORY_ROOT_CODE)) {
      this.setCtgryConditionSQL("");
      return;
    }
    
    if (SIUtil.isNull(lAliasName)) lAliasName = "";
    else lAliasName = lAliasName + ".";
    
    StringBuffer lResBuf = new StringBuffer("(");
    if (lFromCtgryCode.equals(SIConfig.SICATEGORY_ROOT_CODE)) {
      lResBuf.append(lAliasName);
      lResBuf.append("branch LIKE '" + lFromCtgryCode + "%') ");
    } else {
      lResBuf.append(lAliasName);
      lResBuf.append("branch LIKE '%~" + lFromCtgryCode + "~%' ");
      lResBuf.append(" OR " + lAliasName);
      lResBuf.append("branch LIKE '%~" + lFromCtgryCode + "') ");
    }
    this.setCtgryConditionSQL(lResBuf.toString());
  }
}