/**
 * 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.SICmdty;
import jp.co.sint.basic.SIConnCmdty;
import jp.co.sint.basic.SILogin;
import jp.co.sint.basic.SIUserInfo;
import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIFlagConf;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.database.SITableCondition;
import jp.co.sint.database.SITableConditionManager;
import jp.co.sint.tools.SICheckDataConf;
import jp.co.sint.tools.SICheckUtil;
import jp.co.sint.tools.SICheckValid;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIURLParameter;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;

/**
 * @version $Id: UIConnCmdtyListCond.java,v 1.0 2003/09/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 2003/09/29 11:07:13 Original
 */
public class UIConnCmdtyListCond extends SIBasic {
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  // 商品取得件数
  private static final int count = 30;
  private static final int mobileCount = 2;

  // ショップコード
  private String shopCode = "";

  // 商品コード
  private String cmdtyCode = "";

  // 商品名
  private String cmdtyName = "";

  // ショップコード
  private String shopCodeTxt = "";

  // 商品コード
  private String cmdtyCodeTxt = "";

  // 商品名
  private String cmdtyNameTxt = "";

  // 在庫コード
  private String individualCodeTxt = "";

  // ブランド
  private String makerCode = "";

  // 死活区分
  private String disableFlg = "0";

  // 商品種別
  private String cmdtySetTypeFlg = "";

  // フロント表示
  private String frontDispFlgRdo = "";

  // 仕入先
  private String stockNameCbo = "";

  // ページ
  private String pageNumberTxt = "1";

  // SQL検索のばあいに、条件文
  private String conditionSQL = "";

  // 並び順の文
  private String orderBySQL = " ORDER BY CmdtyCode DESC ";

  /**
   * UIConnCmdtyListCond コンストラクタ
   *
   * @param lRequest リクエスト
   * @return なし
   * @throws なし
   */
  public UIConnCmdtyListCond() {}

  // setter of ショップコード
  public void setShopCode(String lShopCode) {
    if (SIUtil.isNull(lShopCode)) lShopCode = "";
    this.shopCode = SIUtil.changeTo(lShopCode.trim(), this.encode);
  }

  // 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 ショップコード
  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 在庫コード
  public void setIndividualCodeTxt(String lIndividualCodeTxt) {
    if (SIUtil.isNull(lIndividualCodeTxt)) lIndividualCodeTxt = "";
    this.individualCodeTxt = SIUtil.changeTo(lIndividualCodeTxt.trim(), this.encode);
  }

  // setter of ブランド
  public void setMakerCode(String lMakerCode) {
    if (SIUtil.isNull(lMakerCode)) lMakerCode = "";
    this.makerCode = SIUtil.changeTo(lMakerCode.trim(), this.encode);
  }

  // setter of 死活区分
  public void setDisableFlg(String lDisableFlg) {
    if (SIUtil.isNull(lDisableFlg)) lDisableFlg = "";
    this.disableFlg = SIUtil.changeTo(lDisableFlg.trim(), this.encode);
  }

  // setter of 商品種別
  public void setCmdtySetTypeFlg(String cmdtySetTypeFlg) {
    this.cmdtySetTypeFlg = cmdtySetTypeFlg;
  }

  // setter of フロント表示
  public void setFrontDispFlgRdo(String frontDispFlgRdo) {
    this.frontDispFlgRdo = frontDispFlgRdo;
  }

  // setter of 仕入先
  public void setStockNameCbo(String stockNameCbo) {
    this.stockNameCbo = stockNameCbo;
  }

  // setter of ページ
  public void setPageNumberTxt(String lPageNumberTxt) {
    if (SIUtil.isNull(lPageNumberTxt)) lPageNumberTxt = "1";
    this.pageNumberTxt = SIUtil.changeTo(lPageNumberTxt.trim(), this.encode);
  }

  // setter of 条件文
  public void setConditionSQL(String lConditionSQL) {
    if (lConditionSQL == null) lConditionSQL = "";
    this.conditionSQL = lConditionSQL;
  }

  // setter of 並び順文
  public void setOrderBySQL(String lOrderBySQL) {
    if (SIUtil.isNull(lOrderBySQL)) lOrderBySQL = " ORDER BY CmdtyCode DESC ";
    this.orderBySQL = lOrderBySQL;
  }

  // getter of ショップコード
  public String getShopCode() {
    return this.shopCode;
  }

  // getter of 商品コード
  public String getCmdtyCode() {
    return this.cmdtyCode;
  }

  // getter of 商品名
  public String getCmdtyName() {
    return this.cmdtyName;
  }

  // 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 ページ
  public String getPageNumberTxt() {
    return this.pageNumberTxt;
  }

  // getter of 在庫コード
  public String getIndividualCodeTxt() {
    return this.individualCodeTxt;
  }

  // getter of ブランド
  public String getMakerCode() {
    return this.makerCode;
  }

  // getter of 死活区分
  public String getDisableFlg() {
    return this.disableFlg;
  }

  // getter of 条件文
  public String getCondtionSQL() {
    return this.conditionSQL;
  }

  // getter of 並び順文
  public String getOrderBySQL() {
    return this.orderBySQL;
  }
  // getter of 商品種別
  public String getCmdtySetTypeFlg() {
    return cmdtySetTypeFlg;
  }

  // getter of フロント表示
  public String getFrontDispFlgRdo() {
    return frontDispFlgRdo;
  }

  // getter of 仕入先
  public String getStockNameCbo() {
    return stockNameCbo;
  }

  /**
   * init 対象のショップコードと商品コードを取得し、入力した検索データを取得し、 対象の商品名を設定します。
   *
   * @param lRequest リクエスト
   * @param lConnection DBへのコネクション
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  public void init(HttpServletRequest lRequest, Connection lConnection, SIURLParameter lUrlParam) {// 7.1.1 ST0236 修正
    SILogin lLogin = SIHTMLUtil.getLogin(lRequest);

    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    super.init(lRequest, lUrlParam);// 7.1.1 ST0236 修正
    if (lLogin.isShop()) {
      this.setShopCodeTxt(lLogin.getMallShopCode());// 検索用のショップコード
    } else {
      this.setShopCodeTxt((String) lUrlParam.getParam("shopCodeTxt"));// 検索用のショップコード //7.1.1 ST0236 修正
    }
    // 7.1.1 ST0236 修正 ここから
    this.setCmdtyCodeTxt((String) lUrlParam.getParam("cmdtyCodeTxt"));// 検索用の商品コード
    this.setCmdtyNameTxt((String) lUrlParam.getParam("cmdtyNameTxt"));// 検索用の商品名

    this.setIndividualCodeTxt((String) lUrlParam.getParam("individualCodeTxt"));// 検索用の在庫コード
    this.setMakerCode((String) lUrlParam.getParam("makerCode"));// 検索用のブランドコード
    this.setDisableFlg((String) lUrlParam.getParam("disableFlg"));// 検索用の死活区分

    this.setShopCode((String) lUrlParam.getParam("shopCode"));// 対象のショップコード
    this.setCmdtyCode((String) lUrlParam.getParam("cmdtyCode"));// 対象の商品コード
    // 7.1.1 ST0236 修正 ここまで
    // 商品種別
    this.setCmdtySetTypeFlg((String) lUrlParam.getParam("cmdtySetTypeFlg"));
    // フロント表示
    this.setFrontDispFlgRdo((String) lUrlParam.getParam("frontDispFlgRdo"));
    // 仕入先
    this.setStockNameCbo((String) lUrlParam.getParam("stockNameCbo"));

    StringBuffer lSqlBuf = new StringBuffer();

    lSqlBuf.append("SELECT CmdtyName FROM CmdtyMTbl ");
    lSqlBuf.append("WHERE shopCode=").append(SIDBUtil.SQL2Str(getShopCode(), " "));
    lSqlBuf.append("AND cmdtyCode=").append(SIDBUtil.SQL2Str(getCmdtyCode(), " "));
    log.debug(" lSqlBuf=" + lSqlBuf.toString());
    try {
      this.setEncode(SIConfig.SIENCODE_NONE);
      this.setCmdtyName(SIDBUtil.getFirstData(lConnection, lSqlBuf.toString()));
    } catch (SIDBAccessException e) {
      e.printStackTrace();
    }
  }

  /**
   * validate 入力した検索データをチェックします。 対象の商品名を設定します。
   *
   * @param lRequest リクエスト
   * @return チェックの結果
   * @throws なし
   */
  public boolean validate(HttpServletRequest lRequest) {
    SICustomErrors errors = new SICustomErrors();
    SITableConditionManager lConditionMan = new SITableConditionManager();
    StringBuffer str = new StringBuffer();

    // 商品種別
    if (SIUtil.isNotNull(this.getCmdtySetTypeFlg())){
      SICheckValid.checkValid(errors, "商品種別", this.getCmdtySetTypeFlg(), SICheckDataConf.SICHECK_FLAG_TYPE, SIFlagConf.SIFLAG_CMDTY_SECTION_FLG_INX);

      if (getCmdtySetTypeFlg().equals("1")) {
        str.append(" AND ( ");
        str.append("   a.CmdtyCompositionFlg in (");
        str.append(SIDBUtil.SQL2Str(SIConfig.CMDTY_COMPOSITION_VARIATION_SET, ", "));
        str.append(SIDBUtil.SQL2Str(SIConfig.CMDTY_COMPOSITION_SELECTION_SET, ", "));
        str.append(SIDBUtil.SQL2Str(SIConfig.CMDTY_COMPOSITION_FIXED_SET, ", "));
        str.append(SIDBUtil.SQL2Str(SIConfig.CMDTY_COMPOSITION_SPECIFICATION, " "));
        str.append("   )");
        str.append(" )");
      } else if (getCmdtySetTypeFlg().equals("0")) {
        lConditionMan.add(new SITableCondition("", "a.CmdtyCompositionFlg", SIConfig.CMDTY_COMPOSITION_NORMAL, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      }
    }
    if(SIUtil.isNotNull(str.toString())){
      lConditionMan.add(new SITableCondition(str.toString()));
    }

    // ショップコード
    if (SIUtil.isNotNull(this.shopCodeTxt) && SICheckValid.checkValid(errors, "ショップコード", this.shopCodeTxt, SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE)) {
      lConditionMan.add(new SITableCondition("", "a.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("", "a.CmdtyCode", this.cmdtyCodeTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }

    // 商品名
    if (SIUtil.isNotNull(this.cmdtyNameTxt) && SICheckValid.checkValid(errors, "商品名", this.cmdtyNameTxt, SICheckDataConf.SICHECK_DATA_CMDTYNAME_TYPE)) {
      lConditionMan.add(new SITableCondition("", "a.CmdtyName", this.cmdtyNameTxt, SIConfig.SICONDITION_TYPE_LIKE, SIConfig.SICONDITION_TYPE_AND));
    }

    // ブランド
    if (SIUtil.isNotNull(this.makerCode)) {
      lConditionMan.add(new SITableCondition("", "a.MakerCode", this.makerCode, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }

    // 死活区分
    if (SIUtil.isNotNull(this.disableFlg)) {
      lConditionMan.add(new SITableCondition("", "a.DisableFlg", this.disableFlg, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }

    if (SIUtil.isNotNull(this.individualCodeTxt)||SIUtil.isNotNull(this.frontDispFlgRdo)||SIUtil.isNotNull(this.stockNameCbo)) {
      str = new StringBuffer();
      str.append(" AND cmdtycode IN (SELECT cmdtycode FROM individualtbl WHERE 1=1 ");
      // 在庫コード
      if (SIUtil.isNotNull(this.individualCodeTxt) && SICheckValid.checkValid(errors, "在庫コード", this.individualCodeTxt, SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE)) {
        str.append("AND individualcode LIKE '").append(SIDBUtil.SQL2Like(this.getIndividualCodeTxt())).append("%' ");
      }
      // フロント表示
      if (SIUtil.isNotNull(this.frontDispFlgRdo)) {
        str.append("AND frontdispflg = ").append(SIDBUtil.SQL2Str(this.getFrontDispFlgRdo()," "));
      }
      // 仕入先
      if (SIUtil.isNotNull(this.stockNameCbo)) {
        str.append("AND stockcode = ").append(SIDBUtil.SQL2Str(this.getStockNameCbo()," "));
      }
      str.append(") ");
      lConditionMan.add(new SITableCondition(str.toString()));
    }
    if (!errors.isEmpty()) lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
    else lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);

    // 条件文の設定
    this.setConditionSQL(lConditionMan.getCondtionSQL());

    return errors.isEmpty();
  }

  /**
   * <b>getCollection</b> 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。 結果として、関連商品マスタ（追加）画面に表示します。
   *
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws SIDBAccessException
   */
  public Collection getCollection(Connection lConnection) throws SIDBAccessException {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIConnCmdty lConnCmdty = new SIConnCmdty();
    StringBuffer lSqlBuf = new StringBuffer();
    StringBuffer lCountBuf = new StringBuffer();
    Collection lConnCmdtys = new ArrayList();

    // 基本のSQL
    lSqlBuf.append("SELECT DISTINCT a.ShopCode,a.CmdtyCode::numeric,a.CmdtyName,a.cmdtycompositionflg ");
    lSqlBuf.append(",CASE WHEN b.ConnCmdtyCode IS NULL THEN 0 ELSE 1 END AS IsConn ");
    lSqlBuf.append("FROM CmdtyMtbl a ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT ConnCmdtyCode FROM ConnCmdtyMTbl ");
    lSqlBuf.append("WHERE CmdtyCode = ").append(SIDBUtil.SQL2Str(getCmdtyCode(), ") b "));
    lSqlBuf.append("ON a.CmdtyCode = b.ConnCmdtyCode ");
    lSqlBuf.append("WHERE a.CmdtyCode != ").append(SIDBUtil.SQL2Str(getCmdtyCode(), " "));

    lCountBuf.append("SELECT COUNT(DISTINCT a.CmdtyCode) FROM CmdtyMtbl a ");
    lCountBuf.append("WHERE a.CmdtyCode != ").append(SIDBUtil.SQL2Str(getCmdtyCode(), " "));
    // 検索の条件
    lSqlBuf.append(this.conditionSQL);
    lCountBuf.append(this.conditionSQL);
    // 出力順
    lSqlBuf.append(this.getOrderBySQL());
    log.debug("lSqlBuf=" + lSqlBuf.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();
      lSqlBuf.append(" LIMIT ").append(lPageSize);
      if(lFromInx>0){
        lSqlBuf.append(" OFFSET ").append(lFromInx);
      }
      log.debug("getCollection:lSqlBuf=" + lSqlBuf.toString());
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());

      for (int jj = 0; jj < lFromInx; jj++)
        lConnCmdtys.add(null);

      int lIndex = 0;

      // 商品レコードのセットの作成
      while (lResultSet.next() && lIndex < lPageSize) {
        lConnCmdty = new SIConnCmdty();
        lConnCmdty.setEncode(SIConfig.SIENCODE_NONE);
        lConnCmdty.setConnShopCode(lResultSet.getString("ShopCode"));
        lConnCmdty.setConnCmdtyCode(lResultSet.getString("CmdtyCode"));
        lConnCmdty.setCmdtyName(lResultSet.getString("CmdtyName"));
        lConnCmdty.setIsConn(lResultSet.getString("IsConn"));
        lConnCmdty.setCmdtyCompositionFlg(lResultSet.getString("cmdtycompositionflg"));
        lConnCmdtys.add(lConnCmdty);
      }
      // 7.3.0 PI-NES0501 修正 ここまで
      for (int jj = lFromInx + lPageSize; jj < lRecordCount; jj++){
        lConnCmdtys.add(null);
      }
    } catch (Exception ex) {
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lConnCmdtys;
  }

  public static Collection getListCollection_preview(Connection lConnection, String lCmdtyCode, String lIndividualCode) throws SIDBAccessException {
    return getListCollection_front(lConnection,lCmdtyCode,lIndividualCode,false,true,null);
  }
  /**
   * getListConnection 対象の商品コードに対するすべての関連商品レコードを取得します。
   *
   * @param lConnection データベースへの接続コネクション
   * @param isMobile true:PC false:mobile
   * @return レコードのセット
   * @throws SIDBAccessException
   */
  public static Collection getListCollection_front(Connection lConnection, String lCmdtyCode, String lIndividualCode, boolean isMobile, HttpServletRequest request) throws SIDBAccessException {
    return getListCollection_front(lConnection,lCmdtyCode,lIndividualCode,isMobile,false,request);
  }
  public static Collection getListCollection_front(Connection lConnection, String lCmdtyCode, String lIndividualCode, boolean isMobile, boolean isPreview, HttpServletRequest request) throws SIDBAccessException {
    SIUserInfo lUserInfo = new SIUserInfo();
    if (request != null) lUserInfo = SIHTMLUtil.getUserInfo(request);

    Statement lStatement = null;
    ResultSet lResultSet = null;

    StringBuffer buff = new StringBuffer();
    SICmdty lCmdty = new SICmdty();
    Collection lCmdtys = new ArrayList();
    String cmdtyName = "";
    String colorName = "";
    int usedNewFlg = 0;
    String usedNewName = "";

    try {

      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 ? "," : ")");
        }
      }

      // 同一の親コードの商品取得
      buff.append("SELECT a.cmdtycode,a.cmdtyname,a.individualcode,a.description,a.unitprice,a.taxflg,a.memberdiscountflg,a.unitpricewithouttax");
      buff.append(",CASE WHEN comp.dispcarriagefree IS NOT NULL THEN comp.dispcarriagefree ");
      buff.append("      WHEN a.deliveryTypeCode='0' AND a.cmdtySize IN ('1','7') THEN 1 ");//送料無料（個別A/Gサイズ）
      buff.append("      WHEN a.deliveryTypeCode='1' AND a.unitPrice>=feerule.price THEN 1 ");//送料無料（小物1万円以上）
      buff.append(" ELSE 0 END AS freedeliveryflg ");
      buff.append(",a.bgpricedispflg,a.usednewflg,a.consigngoodsflg,b.colorname,0 AS disporder");
      buff.append(",COALESCE((SELECT taxrate FROM TaxVW),0) AS taxrate,0 AS sortorder ");
      /* ログイン必須フラグ
      buff.append(",CASE WHEN a.cmdtycode");
      buff.append(" IN(SELECT cc.cmdtycode FROM cmdtyctgrymtbl cc,ctgryvw ct WHERE cc.ctgrycode = ct.ctgrycode");
      buff.append(" AND (");
      for (int i=0;i<SIConfig.LOGINCTGRY.length;i++){
        if (i>0) buff.append(" OR ");
        buff.append(" (ct.branch LIKE '%~").append(SIDBUtil.SQL2Like(SIConfig.LOGINCTGRY[i])).append("%'");
        buff.append(" OR ct.ctgrycode = ").append(SIDBUtil.SQL2Str(SIConfig.LOGINCTGRY[i], ")"));
      }
      buff.append(")) THEN '1' ELSE '0' END AS loginflg ");
      */
      buff.append(",CASE WHEN maker.contractflg='0' THEN 0");
      if(SIUtil.isNotNull(makerString.toString())){
        buff.append(" WHEN maker.makercode IN ").append(makerString).append(" THEN 0");
      }
      buff.append(" ELSE 1 END AS contractflg ");
      // EDBTG003-00 nagayoshi add start
      buff.append(" ,a.cmdtyCompositionFlg ");
      // EDBTG003-00 nagayoshi add end
      buff.append("FROM cmdtyunittbl a ");
      buff.append("LEFT OUTER JOIN cmdtycompositionmtbl comp ");
      buff.append("ON a.cmdtycode=comp.cmdtycode AND a.individualcode=comp.individualcode ");
      buff.append(",colortbl b ,makertbl maker,feerulemtbl feerule ");
      buff.append("WHERE a.frontdispflg='1' AND a.cmdtycode IN (SELECT cmdtycode FROM cmdtyctgrymtbl) AND b.colorcode=a.colorcode ");
      buff.append("AND a.cmdtycode = ").append(SIDBUtil.SQL2Str(lCmdtyCode, " AND a.individualcode <> ")).append(SIDBUtil.SQL2Str(lIndividualCode, " "));
      buff.append("AND NOT(a.usednewflg!='1' AND a.usednewflg!='7' AND a.amountflg='1' AND (a.amount-a.bgpamount)='0' ");
      buff.append(" AND (a.rsrvenableflg='0' OR (a.rsrvamount IS NOT NULL AND a.rsrvamount='0')) ");
      buff.append(" AND (a.soldoutdate IS NULL OR a.soldoutdate+"+SIConfig.DEFAULT_SOLDOUT+" <= CURRENT_DATE)) ");
      buff.append("AND a.makercode = maker.makercode AND a.deliverytypecode = feerule.deliverytypecode ");
      // 関連商品の取得
      buff.append("UNION ");
      buff.append("SELECT a.cmdtycode,a.cmdtyname,a.individualcode,a.description,a.unitprice,a.taxflg,a.memberdiscountflg,a.unitpricewithouttax");
      buff.append(",CASE WHEN comp.dispcarriagefree IS NOT NULL THEN comp.dispcarriagefree ");
      buff.append("      WHEN a.deliveryTypeCode='0' AND a.cmdtySize IN ('1','7') THEN 1 ");//送料無料（個別A/Gサイズ）
      buff.append("      WHEN a.deliveryTypeCode='1' AND a.unitPrice>=feerule.price THEN 1 ");//送料無料（小物1万円以上）
      buff.append(" ELSE 0 END AS freedeliveryflg ");
      buff.append(",a.bgpricedispflg,a.usednewflg,a.consigngoodsflg,b.colorname,c.disporder");
      buff.append(",COALESCE((SELECT taxrate FROM TaxVW),0) AS taxrate,1 AS sortorder ");
      /* ログイン必須フラグ
      buff.append(",CASE WHEN a.cmdtycode");
      buff.append(" IN(SELECT cc.cmdtycode FROM cmdtyctgrymtbl cc,ctgryvw ct WHERE cc.ctgrycode = ct.ctgrycode");
      buff.append(" AND (");
      for (int i=0;i<SIConfig.LOGINCTGRY.length;i++){
        if (i>0) buff.append(" OR ");
        buff.append(" (ct.branch LIKE '%~").append(SIDBUtil.SQL2Like(SIConfig.LOGINCTGRY[i])).append("%'");
        buff.append(" OR ct.ctgrycode = ").append(SIDBUtil.SQL2Str(SIConfig.LOGINCTGRY[i], ")"));
      }
      buff.append(")) THEN '1' ELSE '0' END AS loginflg ");
      */
      buff.append(",CASE WHEN maker.contractflg='0' THEN 0");
      if(SIUtil.isNotNull(makerString.toString())){
        buff.append(" WHEN maker.makercode IN ").append(makerString).append(" THEN 0");
      }
      buff.append(" ELSE 1 END AS contractflg ");
// EDBTG003-00 nagayoshi add start
      buff.append(" ,a.cmdtyCompositionFlg ");
// EDBTG003-00 nagayoshi add end
      buff.append("FROM cmdtyunittbl a ");
      buff.append("LEFT OUTER JOIN cmdtycompositionmtbl comp ");
      buff.append("ON a.cmdtycode=comp.cmdtycode AND a.individualcode=comp.individualcode ");
      buff.append(",colortbl b,conncmdtymtbl c ,makertbl maker,feerulemtbl feerule ");
      buff.append("WHERE a.frontdispflg='1' AND a.cmdtycode IN (SELECT cmdtycode FROM cmdtyctgrymtbl) AND b.colorcode=a.colorcode ");
      buff.append("AND c.cmdtycode = ").append(SIDBUtil.SQL2Str(lCmdtyCode, " "));
      buff.append("AND a.cmdtycode = c.conncmdtycode ");
      buff.append("AND NOT(a.usednewflg!=1 AND a.usednewflg!='7' AND a.amountflg='1' AND (a.amount-a.bgpamount)='0' ");
      buff.append(" AND (a.rsrvenableflg='0' OR (a.rsrvamount IS NOT NULL AND a.rsrvamount='0')) ");
      buff.append(" AND (a.soldoutdate IS NULL OR a.soldoutdate+"+SIConfig.DEFAULT_SOLDOUT+" <= CURRENT_DATE)) ");
      buff.append("AND a.makercode = maker.makercode AND a.deliverytypecode = feerule.deliverytypecode ");

      buff.append("AND a.individualcode IN (SELECT i.individualcode ");
      buff.append(" FROM individualtbl i,cmdtyctgrymtbl cc,ctgryvw v,ctgrymtbl ct LEFT OUTER JOIN ctgrygrouptbl g ON ct.ctgrycode=g.ctgrycode ");
      buff.append(" WHERE cc.cmdtycode=i.cmdtycode AND cc.ctgrycode=v.ctgrycode ");
      buff.append(" AND (v.branch LIKE '%~'||ct.ctgrycode||'~%' OR v.branch LIKE '%~'||ct.ctgrycode OR ct.ctgrycode='/') ");
      buff.append(" AND g.ctgrygroup<>'eyelashgarage') ");

      buff.append("ORDER BY sortorder,disporder,individualcode LIMIT ");
      if (isMobile) {
        buff.append(mobileCount);
      } else {
        buff.append(count);
      }
      lStatement = lConnection.createStatement();
      log.debug("SQL=" + buff.toString());
      lResultSet = lStatement.executeQuery(buff.toString());
      while (lResultSet.next()) {
        lCmdty = new SICmdty();
        lCmdty.setEncode(SIConfig.SIENCODE_NONE);
        lCmdty.setShopCode("0");
        lCmdty.setCmdtyCode(lResultSet.getString("cmdtycode"));
        lCmdty.setIndividualCode(lResultSet.getString("individualcode"));
        lCmdty.setUsedNewFlg(lResultSet.getInt("usednewflg"));
        lCmdty.setDescription(lResultSet.getString("description"));
        lCmdty.setUnitPrice(lResultSet.getString("unitprice"));
        lCmdty.setUnitPriceWithoutTax(lResultSet.getString("unitpricewithouttax"));
        lCmdty.setCalcUnitPrice(lResultSet.getString("unitprice"));
        lCmdty.setTaxRate(lResultSet.getString("taxrate"));
        lCmdty.setTaxFlg(lResultSet.getString("taxflg"));
        lCmdty.setBGPriceDispFlg(lResultSet.getString("bgpricedispflg"));
        //lCmdty.setLoginFlg(lResultSet.getString("loginflg").equals("1"));// ログイン必須フラグ
        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.setMemberDiscountFlg(lResultSet.getString("memberDiscountFlg"));
        lCmdty.setMemberDiscountRate(lUserInfo.getDiscountRate());

        cmdtyName = lResultSet.getString("cmdtyname");
        colorName = lResultSet.getString("colorname");
        usedNewFlg = lCmdty.getUsedNewFlg();
        if (usedNewFlg == 1 || usedNewFlg == 6 || usedNewFlg == 7) {// 新品
          usedNewName = "新品";
        } else if (usedNewFlg == 2 || usedNewFlg == 4) {// 新古OUTLET
          usedNewName = "新古OUTLET";
        } else if (usedNewFlg == 3) {// BG認定中古
          usedNewName = "BG認定中古";
        } else {// 中古
          usedNewName = "中古";
        }
        //if (lResultSet.getInt("consigngoodsflg") == 1) usedNewName = usedNewName + "[委託]";

        if (SIUtil.isNotNull(colorName) && SIUtil.isNotNull(usedNewName)) {
          cmdtyName = cmdtyName + "(" + colorName + "・" + usedNewName + ")";
        } else if (SIUtil.isNotNull(colorName) || SIUtil.isNotNull(usedNewName)) {
          cmdtyName = cmdtyName + "(" + colorName + usedNewName + ")";
        }
        lCmdty.setCmdtyName(cmdtyName);
        lCmdty.setFreeDeliveryFlg(lResultSet.getString("freeDeliveryFlg"));
        // EDBTG003-00 nagayoshi add start
        lCmdty.setCmdtyCompositionFlg(lResultSet.getString("cmdtyCompositionFlg"));
        // EDBTG003-00 nagayoshi add end
        lCmdtys.add(lCmdty);
      }

    } catch (Exception ex) {
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lCmdtys;
  }

  /**
   * getListConnection 対象の商品コードに対するすべての関連商品レコードを取得します。
   *
   * @param lConnection データベースへの接続コネクション
   * @param siteFlg true:front false:back
   * @return レコードのセット
   * @throws SIDBAccessException
   */
  public Collection getListCollection_back(Connection lConnection) throws SIDBAccessException {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIConnCmdty lConnCmdty = new SIConnCmdty();
    StringBuffer lSqlBuf = new StringBuffer();
    Collection lConnCmdtys = new ArrayList();

    // 基本のSQL
    lSqlBuf.append("SELECT aa.*,bb.CmdtyName AS ConnCmdtyName ");
    lSqlBuf.append("FROM ConnCmdtyMTbl AS aa,");
    lSqlBuf.append("CmdtyMTbl AS bb ");
    lSqlBuf.append("WHERE aa.ShopCode=").append(SIDBUtil.SQL2Str(this.getShopCode(), " "));
    lSqlBuf.append("AND aa.CmdtyCode=").append(SIDBUtil.SQL2Str(this.getCmdtyCode(), " "));
    lSqlBuf.append("AND aa.ConnShopCode=bb.ShopCode ");
    lSqlBuf.append("AND aa.ConnCmdtyCode=bb.CmdtyCode ");
    lSqlBuf.append("ORDER BY DispOrder ");

    log.debug("lSqlBuf=" + lSqlBuf.toString());
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());

      // 商品レコードのセットの作成
      while (lResultSet.next()) {
        lConnCmdty = new SIConnCmdty();
        lConnCmdty.setEncode(SIConfig.SIENCODE_NONE);
        lConnCmdty.setConnShopCode(lResultSet.getString("ConnShopCode"));
        lConnCmdty.setConnCmdtyCode(lResultSet.getString("ConnCmdtyCode"));
        lConnCmdty.setConnCmdtyName(lResultSet.getString("ConnCmdtyName"));
        lConnCmdty.setDispOrder(lResultSet.getString("DispOrder"));
        lConnCmdtys.add(lConnCmdty);
      }
    } catch (Exception ex) {
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lConnCmdtys;
  }
}