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

import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletRequest;

import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.sql.*;
import org.apache.log4j.Category;
import jp.co.sint.database.*;
import jp.co.sint.tools.SICheckUtil;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIURLParameter;
import jp.co.sint.tools.SIUtil;
import jp.co.sint.basic.*;
import jp.co.sint.config.*;

/**
 * @version $Id : UICampaignListCond.java,v 1.0 Exp $
 * @author : Naotaka Ohsugi <br>
 * Description :特別キャンペーン画面に対するbeansクラスです。
 * <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>
 * Naotaka Ohsugi 2006/03/01 Original
 */

public class UICampaign extends SIFrontBasic {
  
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  private String listIndex = "";
  
  /**
   * UICampaignListCond コンストラクタ
   * 
   * @param なし
   * @return なし
   * @throws なし
   */
  public UICampaign() {}
  
  public String getListIndex() {
    return listIndex;
  }
  
  public void setListIndex(String listIndex) {
    if (SIUtil.isNull(listIndex)) listIndex = "";
    this.listIndex = listIndex;
  }
  
  public void init(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    this.setListIndex((String)lUrlParam.getParam("listIndex"));
    super.init(lRequest, lUrlParam);
  }
  
  /**
   * <b>getCollection</b> 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * 
   * @param lConnection データベースへの接続コネクション
   * @param SILogin ログイン者情報をセットしたBean
   * @return レコードのセット
   * @throws なし
   */
  public Collection getCollection(Connection lConnection, HttpServletRequest request) throws SIDBAccessException {
    return getCollection(lConnection, request, "1");
  }
  
  public Collection getCollection(Connection lConnection, HttpServletRequest request, String index) throws SIDBAccessException {
    SIUserInfo lUserInfo = new SIUserInfo();
    if (request != null) lUserInfo = SIHTMLUtil.getUserInfo(request);
    //indexが0以外の数値でない場合、空のリストを返す。
    if (SIUtil.isNull(index) || !SICheckUtil.isDigit(index) || "0".equals(index)) return new ArrayList();
    
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SICmdty lCmdty = new SICmdty();
    StringBuffer lCmdtySqlBuf = new StringBuffer();
    StringBuffer lCountSqlBuf = new StringBuffer();
    StringBuffer lCommonSqlBuf = new StringBuffer();
    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 ? "," : ")");
      }
    }
    
    lCmdtySqlBuf.append("SELECT a.cmdtycode,a.individualcode,a.cmdtyname,a.nomalprice,a.unitprice,a.taxflg,a.bgpricedispflg,a.amount,a.memberdiscountflg,a.unitpricewithouttax");
    //  EDBTG001-02 2013/03/11 nagayoshi add start
    lCmdtySqlBuf.append(",a.cmdtyCompositionFlg");
    //  EDBTG001-02 2013/03/11 nagayoshi add end;
    lCmdtySqlBuf.append(",CASE WHEN comp.dispcarriagefree IS NOT NULL THEN comp.dispcarriagefree ");
    lCmdtySqlBuf.append("      WHEN a.deliveryTypeCode='0' AND a.cmdtySize IN ('1','7') THEN 1 ");//送料無料（個別AGサイズ）
    lCmdtySqlBuf.append("      WHEN a.deliveryTypeCode='1' AND a.unitPrice>=feerule.price THEN 1 ");//送料無料（小物1万円以上）
    lCmdtySqlBuf.append(" ELSE 0 END AS freedeliveryflg ");
    lCmdtySqlBuf.append(",CASE WHEN maker.contractflg='0' THEN 0");
    if(SIUtil.isNotNull(makerString.toString())){
      lCmdtySqlBuf.append(" WHEN maker.makercode IN ").append(makerString).append(" THEN 0");
    }
    lCmdtySqlBuf.append(" ELSE 1 END AS contractflg ");
    lCmdtySqlBuf.append(",COALESCE((SELECT taxrate FROM TaxVW),0) AS taxrate ");
    lCountSqlBuf.append("SELECT count(a.individualcode) ");
    
    lCommonSqlBuf.append("FROM cmdtyunittbl a ");
    lCommonSqlBuf.append("LEFT OUTER JOIN cmdtycompositionmtbl comp ");
    lCommonSqlBuf.append("ON a.cmdtycode=comp.cmdtycode AND a.individualcode=comp.individualcode ");
    lCommonSqlBuf.append(",makertbl maker ,feerulemtbl feerule ");
    lCommonSqlBuf.append("WHERE a.frontdispflg='1' AND a.spcampaignflg=").append(SIDBUtil.SQL2Str(index, " "));
    lCommonSqlBuf.append("AND NOT(a.rsrvenableflg='0' AND a.amountflg='1' AND (a.amount-a.bgpamount)='0') ");
    lCommonSqlBuf.append("AND a.makercode = maker.makercode AND a.deliverytypecode = feerule.deliverytypecode ");
    lCmdtySqlBuf.append(lCommonSqlBuf);
    if ("1".equals(index)) {
      lCmdtySqlBuf.append("ORDER BY unitprice");
    } else {
      lCmdtySqlBuf.append("ORDER BY individualcode");
    }
    lCountSqlBuf.append(lCommonSqlBuf);
    
    // 実行
    try {
      String rowCnt = SIDBUtil.getFirstData(lConnection, lCountSqlBuf.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:lSqlBuf=" + 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.setCmdtyCode(lResultSet.getString("CmdtyCode"));
        lCmdty.setIndividualCode(lResultSet.getString("IndividualCode"));
        lCmdty.setCmdtyName(lResultSet.getString("CmdtyName"));
        lCmdty.setUnitPrice(lResultSet.getString("UnitPrice"));
        lCmdty.setCalcUnitPrice(lResultSet.getString("UnitPrice"));
        lCmdty.setUnitPriceWithoutTax(lResultSet.getString("UnitPriceWithoutTax"));
        lCmdty.setTaxRate(lResultSet.getString("taxrate"));
        lCmdty.setTaxFlg(lResultSet.getString("TaxFlg"));
        lCmdty.setBGPriceDispFlg(lResultSet.getString("BGpriceDispFlg"));
        lCmdty.setNewFixedPrice(lResultSet.getString("NomalPrice"));// 新品価格（通常価格）
        lCmdty.setAmount(lResultSet.getString("Amount"));
        lCmdty.setContractFlg(lResultSet.getString("contractFlg").equals("1"));
        lCmdty.setFreeDeliveryFlg(lResultSet.getString("freeDeliveryFlg"));
        lCmdty.setMemberDiscountFlg(lResultSet.getString("memberDiscountFlg"));
        // EDBTG001-02 2013/03/11 nagayoshi add start
        lCmdty.setCmdtyCompositionFlg(lResultSet.getString("cmdtyCompositionFlg"));
        // EDBTG001-02 2013/03/11 nagayoshi add end
        lCmdty.setMemberDiscountRate(lUserInfo.getDiscountRate());
        lCmdtys.add(lCmdty);
        lIndex++;
      }
      for (int jj = lFromInx + lPageSize; jj < lRecordCount; jj++)
        lCmdtys.add(null);
      
    } catch (SQLException ex) {
      ex.printStackTrace();
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lCmdtys;
  }
  
  public Collection getClosedCollection(Connection lConnection, HttpServletRequest request, String index) throws SIDBAccessException {
    SIUserInfo lUserInfo = new SIUserInfo();
    if (request != null) lUserInfo = SIHTMLUtil.getUserInfo(request);
    //indexが0以外の数値でない場合、空のリストを返す。
    if (SIUtil.isNull(index) || !SICheckUtil.isDigit(index) || "0".equals(index)) return new ArrayList();
    
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SICmdty lCmdty = new SICmdty();
    StringBuffer lCmdtySqlBuf = new StringBuffer();
    StringBuffer lCountSqlBuf = new StringBuffer();
    StringBuffer lCommonSqlBuf = new StringBuffer();
    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 ? "," : ")");
      }
    }
    
    // EDBTG003-00 nagayoshi mod start
//    lCmdtySqlBuf.append("SELECT a.cmdtycode,a.individualcode,a.cmdtyname,a.nomalprice,a.unitprice,a.taxflg,a.bgpricedispflg,a.amount,a.memberdiscountflg,a.amountflg,a.makercode");
    lCmdtySqlBuf.append("SELECT a.cmdtycode,a.individualcode,a.cmdtyname,a.nomalprice,a.unitprice,a.taxflg,a.bgpricedispflg,a.amount,a.memberdiscountflg,a.amountflg,a.makercode,a.cmdtyCompositionFlg,a.unitpricewithouttax");
    // EDBTG003-00 nagayoshi mod end
    
    lCmdtySqlBuf.append(",CASE WHEN comp.dispcarriagefree IS NOT NULL THEN comp.dispcarriagefree ");
    lCmdtySqlBuf.append("      WHEN a.deliveryTypeCode='0' AND a.cmdtySize IN ('1','7') THEN 1 ");//送料無料（個別AGサイズ）
    lCmdtySqlBuf.append("      WHEN a.deliveryTypeCode='1' AND a.unitPrice>=feerule.price THEN 1 ");//送料無料（小物1万円以上）
    lCmdtySqlBuf.append(" ELSE 0 END AS freedeliveryflg ");
    lCmdtySqlBuf.append(",CASE WHEN maker.contractflg='0' THEN 0");
    if(SIUtil.isNotNull(makerString.toString())){
      lCmdtySqlBuf.append(" WHEN maker.makercode IN ").append(makerString).append(" THEN 0");
    }
    lCmdtySqlBuf.append(" ELSE 1 END AS contractflg ");
    lCmdtySqlBuf.append(",COALESCE((SELECT taxrate FROM TaxVW),0) AS taxrate ");
    lCountSqlBuf.append("SELECT count(a.individualcode) ");
    
    lCommonSqlBuf.append("FROM cmdtyunittbl a ");
    lCommonSqlBuf.append("LEFT OUTER JOIN cmdtycompositionmtbl comp ");
    lCommonSqlBuf.append("ON a.cmdtycode=comp.cmdtycode AND a.individualcode=comp.individualcode ");
    lCommonSqlBuf.append(",closedsaleitemtbl c,makertbl maker,feerulemtbl feerule ");
    lCommonSqlBuf.append("WHERE a.frontdispflg='0' AND c.salecode='1' ");
    lCommonSqlBuf.append("AND c.salebranchcode=").append(SIDBUtil.SQL2Str(index, " "));
    lCommonSqlBuf.append("AND a.individualcode=c.individualcode AND a.makercode = maker.makercode AND a.deliverytypecode = feerule.deliverytypecode ");
    lCmdtySqlBuf.append(lCommonSqlBuf).append("ORDER BY c.disporder,a.individualcode");
    lCountSqlBuf.append(lCommonSqlBuf);
    
    // 実行
    try {
      String rowCnt = SIDBUtil.getFirstData(lConnection, lCountSqlBuf.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:lSqlBuf=" + 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.setCmdtyCode(lResultSet.getString("CmdtyCode"));
        lCmdty.setIndividualCode(lResultSet.getString("IndividualCode"));
        lCmdty.setCmdtyName(lResultSet.getString("CmdtyName"));
        lCmdty.setMakerCode(lResultSet.getString("MakerCode"));
        lCmdty.setUnitPrice(lResultSet.getString("UnitPrice"));
        lCmdty.setCalcUnitPrice(lResultSet.getString("UnitPrice"));
        lCmdty.setUnitPriceWithoutTax(lResultSet.getString("UnitPriceWithoutTax"));
        lCmdty.setTaxRate(lResultSet.getString("taxrate"));
        lCmdty.setTaxFlg(lResultSet.getString("TaxFlg"));
        lCmdty.setNewFixedPrice(lResultSet.getString("NomalPrice"));// 新品価格（通常価格）
        lCmdty.setAmount(lResultSet.getString("Amount"));
        lCmdty.setContractFlg(lResultSet.getString("contractFlg").equals("1"));
        lCmdty.setFreeDeliveryFlg(lResultSet.getString("freeDeliveryFlg"));
        lCmdty.setMemberDiscountFlg(lResultSet.getString("memberDiscountFlg"));
        lCmdty.setMemberDiscountRate(lUserInfo.getDiscountRate());
        lCmdty.setAmountFlg(lResultSet.getString("AmountFlg"));
        // 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 (SQLException ex) {
      ex.printStackTrace();
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lCmdtys;
  }
  
  public String getDiscountRate(String lNomalPrice, String lSalePrice) {
    double np = Integer.parseInt(lNomalPrice);
    double sp = Integer.parseInt(lSalePrice);
    long dr = 0;
    dr = Math.round((1.0 - sp / np) * 100);
    return String.valueOf(dr);
  }
  
  public String[][] getClosedSaleList(Connection lConnection) {
    String[][] res = new String[0][0];
    
    Statement lStatement = null;
    ResultSet lResultSet = null;
    try {
      StringBuffer lSql = new StringBuffer();
      lSql.append("SELECT a.sale1name,a.sale2name,a.sale3name ");
      lSql.append(",b.sale1amount,b.sale2amount,b.sale3amount ");
      lSql.append("FROM closedsaletbl a ");
      lSql.append(",(SELECT salecode,sum(CASE salebranchcode WHEN 1 THEN 1 ELSE 0 END) AS sale1amount ");
      lSql.append(" ,sum(CASE salebranchcode WHEN 2 THEN 1 ELSE 0 END) AS sale2amount ");
      lSql.append(" ,sum(CASE salebranchcode WHEN 3 THEN 1 ELSE 0 END) AS sale3amount ");
      lSql.append(" FROM closedsaleitemtbl GROUP BY salecode) b ");
      lSql.append("WHERE a.salecode=b.salecode AND a.salecode=1 ");
      
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      
      if (lResultSet.next()) {
        res = new String[3][2];
        res[0][0] = lResultSet.getString("sale1name");
        res[0][1] = lResultSet.getString("sale1amount");
        res[1][0] = lResultSet.getString("sale2name");
        res[1][1] = lResultSet.getString("sale2amount");
        res[2][0] = lResultSet.getString("sale3name");
        res[2][1] = lResultSet.getString("sale3amount");
      }
    } catch(Exception e) {
      e.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return res;
  }
  
  //EDBTG005-00 kamata mod start
  public static Collection getMyCatalog(Connection lConnection,HttpServletRequest request,int max)  throws SIDBAccessException {
    // PCの場合
    return getMyCatalog(lConnection, request, max, "");
  }
  public static Collection getMyCatalogForSP(Connection lConnection,HttpServletRequest request,int max)  throws SIDBAccessException {
    // スマートフォンの場合
    String individualcode = "";
    
    // 未ログインの場合
    // cookieからお気に入り情報を取得
    Cookie[] cookies = request.getCookies();
    Cookie cmdtyCookieOrg = null;
    if (cookies != null) {
      for (int i = 0; i < cookies.length; i++) {
        if (cookies[i].getName().equals("ws_favoriteBG")){
          cmdtyCookieOrg = cookies[i];
          break;
        }
      }
    }
    
    if (cmdtyCookieOrg == null) {
      return new ArrayList();
    }
    String[] items;
    try {
      items = URLDecoder.decode(cmdtyCookieOrg.getValue(), "Shift_JIS").split(",");
    } catch (UnsupportedEncodingException e) {
      e.printStackTrace();
      throw new SIDBAccessException(e);
    }
    for (int i = 0; i < items.length; i++) {
      if (i != 0) {
        individualcode += ",";
      }
      individualcode += SIDBUtil.SQL2Str(items[i]);
    }
    return getMyCatalog(lConnection, request, max, individualcode);
  }
  
  public static Collection getMyCatalog(Connection lConnection,HttpServletRequest request,int max, String individualcode)  throws SIDBAccessException {
    SIUserInfo lUserInfo = new SIUserInfo();
    if (request != null) lUserInfo = SIHTMLUtil.getUserInfo(request);
    else return new ArrayList();
    
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SICmdty lCmdty = new SICmdty();
    StringBuffer lCmdtySqlBuf = new StringBuffer();
    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 ? "," : ")");
      }
    }
    
    String feeRulePrice1 = SIDBUtil.getFirstData(lConnection, "SELECT price FROM feerulemtbl WHERE deliverytypecode='1'");
    if(SIUtil.isNull(feeRulePrice1)) feeRulePrice1="0";
    
    lCmdtySqlBuf.append("SELECT a1.*, color.colorname,maker.makername,COALESCE((SELECT taxrate FROM taxvw),0) AS TaxRate, ");
    lCmdtySqlBuf.append("CASE WHEN a1.InitDateTime + (SELECT daysofnew FROM MallShopMTbl) >= CURRENT_DATE THEN '1' ELSE '0' END AS NewArrivalFlg ");
    lCmdtySqlBuf.append(",CASE WHEN maker.contractflg = 0 THEN 0");
    if(SIUtil.isNotNull(makerString.toString())){
      lCmdtySqlBuf.append(" WHEN maker.makercode IN ").append(makerString).append(" THEN 0");
    }
    lCmdtySqlBuf.append(" ELSE 1 END AS contractflg ");
    lCmdtySqlBuf.append(",CASE WHEN a1.frontdispflg='0' ");
    lCmdtySqlBuf.append(" OR (a1.usedNewFlg!='1' AND a1.usedNewFlg!='7' AND a1.amountflg='1' AND (a1.amount-a1.bgpamount)='0' ");
    lCmdtySqlBuf.append("AND (a1.rsrvenableflg='0' OR (a1.rsrvamount IS NOT NULL AND a1.rsrvamount='0')) ");
    lCmdtySqlBuf.append("AND (a1.soldoutdate IS NULL OR a1.soldoutdate + "+SIConfig.DEFAULT_SOLDOUT+" <= CURRENT_DATE)) THEN '' ELSE a1.cmdtyname END AS cmdtyname2 ");
    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(" FROM ");
    lCmdtySqlBuf.append(" (SELECT unit.*,cmdty.expansioncomment1text,cmdty.expansioncomment2text,cmdty.expansioncomment3text ");
    lCmdtySqlBuf.append(",CASE WHEN comp.dispcarriagefree IS NOT NULL THEN comp.dispcarriagefree ");
    lCmdtySqlBuf.append("       WHEN unit.deliveryTypeCode='0' AND unit.cmdtySize IN ('1','7') THEN 1 ");//送料無料（個別AGサイズ）
    lCmdtySqlBuf.append("       WHEN unit.deliveryTypeCode='1' AND unit.unitPrice>=").append(SIDBUtil.SQL2Str(feeRulePrice1)).append(" THEN 1 ");//送料無料（小物5000円以上）
    lCmdtySqlBuf.append("  ELSE 0 END AS freedeliveryflg ");
    lCmdtySqlBuf.append(",CASE WHEN comp.dispcarriagefree IS NOT NULL AND comp.dispcarriagefree='1' THEN unit.cmdtyName||'【送料無料】' ");
    lCmdtySqlBuf.append("       WHEN comp.dispcarriagefree IS NOT NULL AND comp.dispcarriagefree='0' THEN unit.cmdtyName ");
    lCmdtySqlBuf.append("       WHEN unit.deliveryTypeCode='0' AND unit.cmdtySize IN ('1','7') THEN unit.cmdtyName||'【送料無料】' ");
    lCmdtySqlBuf.append("       WHEN unit.deliveryTypeCode='1' AND unit.unitPrice>=").append(SIDBUtil.SQL2Str(feeRulePrice1)).append(" THEN unit.cmdtyName||'【送料無料】' ");
    lCmdtySqlBuf.append("  ELSE unit.cmdtyname END AS cmdtynamewithfree ");
    lCmdtySqlBuf.append("  FROM cmdtyunittbl unit ");
    lCmdtySqlBuf.append("LEFT OUTER JOIN cmdtycompositionmtbl comp ");
    lCmdtySqlBuf.append("ON unit.cmdtycode=comp.cmdtycode AND unit.individualcode=comp.individualcode ");
    lCmdtySqlBuf.append(",individualtbl cmdty ");
    lCmdtySqlBuf.append("  WHERE unit.cmdtycode=cmdty.cmdtycode AND unit.individualcode=cmdty.individualcode) a1 ");
    lCmdtySqlBuf.append(" LEFT OUTER JOIN (SELECT bonuspointrate,ctgrycode FROM bonuspointtbl WHERE bonusfromdate <= current_date AND bonustodate >= current_date ) point ");
    lCmdtySqlBuf.append(" ON(substring(a1.individualcode,0,3) = point.ctgrycode) ");
    lCmdtySqlBuf.append(" LEFT OUTER JOIN (SELECT bonuspointrate,cmdtycode,individualcode FROM bonuspointindividualtbl WHERE bonusfromdate <= current_date AND bonustodate >= current_date ) point2 ");
    lCmdtySqlBuf.append(" ON(a1.cmdtycode = point2.cmdtycode AND a1.individualcode = point2.individualcode) ");
    
    if (lUserInfo.isLogin(request)) {
        //PCの場合
        lCmdtySqlBuf.append(" ,colortbl color,mycatalogtbl catalog ");
        lCmdtySqlBuf.append(" ,(SELECT CASE WHEN makercode IN ('1230','1231','1232') THEN 'ポール･シェリーM-products' ELSE makername END AS makername,makercode,contractflg FROM makertbl) maker ");
        lCmdtySqlBuf.append(" WHERE a1.ColorCode=color.ColorCode ");
        lCmdtySqlBuf.append(" AND a1.MakerCode=maker.MakerCode ");
        lCmdtySqlBuf.append(" AND a1.individualcode=catalog.individualcode ");
        lCmdtySqlBuf.append(" AND catalog.custcode=").append(SIDBUtil.SQL2Str(lUserInfo.getCustCode(), " "));
        lCmdtySqlBuf.append("ORDER BY catalog.disporder,catalog.individualcode ");
    } else if (SIUtil.isNotNull(individualcode)){
        // スマートフォンの場合
        lCmdtySqlBuf.append(" ,colortbl color ");
        lCmdtySqlBuf.append(" ,(SELECT CASE WHEN makercode IN ('1230','1231','1232') THEN 'ポール･シェリーM-products' ELSE makername END AS makername,makercode,contractflg FROM makertbl) maker ");
        lCmdtySqlBuf.append(" WHERE a1.ColorCode=color.ColorCode ");
        lCmdtySqlBuf.append(" AND a1.MakerCode=maker.MakerCode ");
        lCmdtySqlBuf.append(" AND a1.individualcode IN ( " + individualcode + ") ");
    } else {
      return lCmdtys;
    }
    
    if(max>0) lCmdtySqlBuf.append("LIMIT ").append(max);
    
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lCmdtySqlBuf.toString());
      
      while (lResultSet.next()) {
        lCmdty = new SICmdty();
        lCmdty.setEncode(SIConfig.SIENCODE_NONE);
        lCmdty.setMemberDiscountFlg(lResultSet.getString("MemberDiscountFlg"));
        lCmdty.setMemberDiscountRate(lUserInfo.getDiscountRate());
        lCmdty.setCmdtyCode(lResultSet.getString("CmdtyCode"));
        lCmdty.setIndividualCode(lResultSet.getString("IndividualCode"));
        lCmdty.setCmdtyName(lResultSet.getString("CmdtyName2"));
        lCmdty.setUnitPrice(lResultSet.getString("UnitPrice"));
        lCmdty.setUnitPriceWithoutTax(lResultSet.getString("UnitPriceWithoutTax"));
        lCmdty.setTaxFlg(lResultSet.getString("taxFlg"));// 税区分
        lCmdty.setCalcUnitPrice(lResultSet.getString("UnitPrice"));
        lCmdty.setAmount(lResultSet.getString("Amount"));// 在庫数量
        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.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.setContractFlg(lResultSet.getString("contractflg").equals("1"));
        if ("H".equals(lResultSet.getString("IndividualCode").substring(0,1))||"V".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.setPointRate(lResultSet.getString("pointRate"));// ボーナスポイントレート
        lCmdty.setNomalPointRate(lResultSet.getString("nomalPointRate"));// 通常ポイントレート
        lCmdty.setFreeDeliveryFlg(lResultSet.getString("freeDeliveryFlg"));
        lCmdty.setPriceDownFlg(lResultSet.getString("priceDownFlg"));
        // EDBTG003-00 nagayoshi add start
        lCmdty.setCmdtyCompositionFlg(lResultSet.getString("cmdtyCompositionFlg"));
        // EDBTG003-00 nagayoshi add end
        lCmdtys.add(lCmdty);
      }
    
    } catch (SQLException ex) {
      ex.printStackTrace();
      throw new SIDBAccessException(ex);
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lCmdtys;
  }
  //EDBTG005-00 kamata mod end
  
  public static Collection getFavoriteItems(Connection lConnection,HttpServletRequest request,int max)  throws SIDBAccessException {
    SIUserInfo lUserInfo = new SIUserInfo();
    if (request != null) lUserInfo = SIHTMLUtil.getUserInfo(request);
    else return new ArrayList();
    
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SICmdty lCmdty = new SICmdty();
    StringBuffer lCmdtySqlBuf = new StringBuffer();
    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 ? "," : ")");
      }
    }
    
    lCmdtySqlBuf.append("SELECT a1.*, color.colorname,maker.makername,COALESCE((SELECT taxrate FROM taxvw),0) AS TaxRate, ");
    lCmdtySqlBuf.append("CASE WHEN a1.InitDateTime + (SELECT daysofnew FROM MallShopMTbl) >= CURRENT_DATE THEN '1' ELSE '0' END AS NewArrivalFlg ");
    lCmdtySqlBuf.append(",CASE WHEN maker.contractflg = 0 THEN 0");
    if(SIUtil.isNotNull(makerString.toString())){
      lCmdtySqlBuf.append(" WHEN maker.makercode IN ").append(makerString).append(" THEN 0");
    }
    lCmdtySqlBuf.append(" ELSE 1 END AS contractflg ");
    lCmdtySqlBuf.append(",a1.cmdtyname ");
    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(" FROM ");
    lCmdtySqlBuf.append(" (SELECT unit.*,cmdty.expansioncomment1text,cmdty.expansioncomment2text,cmdty.expansioncomment3text ");
    lCmdtySqlBuf.append(",CASE WHEN comp.dispcarriagefree IS NOT NULL THEN comp.dispcarriagefree ");
    lCmdtySqlBuf.append("       WHEN unit.deliveryTypeCode='0' AND unit.cmdtySize IN ('1','7') THEN 1 ");//送料無料（個別AGサイズ）
    lCmdtySqlBuf.append("       WHEN unit.deliveryTypeCode='1' AND unit.unitPrice>=feerule.price THEN 1 ");//送料無料（小物5000円以上）
    lCmdtySqlBuf.append("  ELSE 0 END AS freedeliveryflg ");
    lCmdtySqlBuf.append(",CASE WHEN comp.dispcarriagefree IS NOT NULL AND comp.dispcarriagefree='1' THEN unit.cmdtyName||'【送料無料】' ");
    lCmdtySqlBuf.append("       WHEN comp.dispcarriagefree IS NOT NULL AND comp.dispcarriagefree='0' THEN unit.cmdtyName ");
    lCmdtySqlBuf.append("       WHEN unit.deliveryTypeCode='0' AND unit.cmdtySize IN ('1','7') THEN unit.cmdtyName||'【送料無料】' ");
    lCmdtySqlBuf.append("       WHEN unit.deliveryTypeCode='1' AND unit.unitPrice>=feerule.price THEN unit.cmdtyName||'【送料無料】' ");
    lCmdtySqlBuf.append("  ELSE unit.cmdtyname END AS cmdtynamewithfree ");
    lCmdtySqlBuf.append("  FROM cmdtyunittbl unit ");
    lCmdtySqlBuf.append("LEFT OUTER JOIN cmdtycompositionmtbl comp ");
    lCmdtySqlBuf.append("ON unit.cmdtycode=comp.cmdtycode AND unit.individualcode=comp.individualcode ");
    lCmdtySqlBuf.append(",individualtbl cmdty,feerulemtbl feerule ");
    lCmdtySqlBuf.append("  WHERE unit.cmdtycode=cmdty.cmdtycode AND unit.individualcode=cmdty.individualcode AND unit.deliverytypecode = feerule.deliverytypecode) a1 ");
    lCmdtySqlBuf.append(" LEFT OUTER JOIN (SELECT bonuspointrate,ctgrycode FROM bonuspointtbl WHERE bonusfromdate <= current_date AND bonustodate >= current_date ) point ");
    lCmdtySqlBuf.append(" ON(substring(a1.individualcode,0,3) = point.ctgrycode) ");
    lCmdtySqlBuf.append(" LEFT OUTER JOIN (SELECT bonuspointrate,cmdtycode,individualcode FROM bonuspointindividualtbl WHERE bonusfromdate <= current_date AND bonustodate >= current_date ) point2 ");
    lCmdtySqlBuf.append(" ON(a1.cmdtycode = point2.cmdtycode AND a1.individualcode = point2.individualcode) ");
    lCmdtySqlBuf.append(" ,colortbl color ");
    lCmdtySqlBuf.append(" ,(SELECT d.individualcode,sum(d.amount) AS totalcount,round(avg(orgprice)) AS unitprice FROM ordertbl h,orderdetailtbl d ");
    lCmdtySqlBuf.append(" WHERE h.ordercode=d.ordercode AND h.orderbranchcode=d.orderbranchcode AND h.enabledflg=1 AND h.status=1 AND d.setindividualcode IS NULL ");
    lCmdtySqlBuf.append(" AND h.custcode=").append(SIDBUtil.SQL2Str(lUserInfo.getCustCode()));
    lCmdtySqlBuf.append(" GROUP BY individualcode ");
    lCmdtySqlBuf.append(" UNION ALL ");
    lCmdtySqlBuf.append(" SELECT s.setindividualcode AS individualcode,sum(s.setamount) AS totalcount,0 AS unitprice FROM ordertbl h,ordersetcmdtytbl s ");
    lCmdtySqlBuf.append(" WHERE h.ordercode=s.ordercode AND h.orderbranchcode=s.orderbranchcode AND h.enabledflg=1 AND h.status=1 ");
    lCmdtySqlBuf.append(" AND h.custcode=").append(SIDBUtil.SQL2Str(lUserInfo.getCustCode()));
    lCmdtySqlBuf.append(" GROUP BY s.setindividualcode ");
    lCmdtySqlBuf.append(") favoritelist ");
    lCmdtySqlBuf.append(" ,(SELECT CASE WHEN makercode IN ('1230','1231','1232') THEN 'ポール･シェリーM-products' ELSE makername END AS makername,makercode,contractflg FROM makertbl) maker ");
    lCmdtySqlBuf.append(" WHERE a1.ColorCode=color.ColorCode ");
    lCmdtySqlBuf.append(" AND a1.MakerCode=maker.MakerCode ");
    lCmdtySqlBuf.append(" AND a1.individualcode=favoritelist.individualcode ");
    lCmdtySqlBuf.append(" AND CASE WHEN a1.frontdispflg='0' THEN false ");
    lCmdtySqlBuf.append(" WHEN a1.amountflg='0' THEN true ");
    lCmdtySqlBuf.append(" WHEN (a1.amount-a1.bgpamount)>'0' THEN true ");
    lCmdtySqlBuf.append(" WHEN a1.usedNewFlg IN('1','7') THEN true ");
    lCmdtySqlBuf.append(" WHEN a1.rsrvenableflg='1' AND a1.rsrvamount<>'0' THEN true ");
    lCmdtySqlBuf.append(" WHEN a1.soldoutdate + "+SIConfig.DEFAULT_SOLDOUT+" >= CURRENT_DATE THEN true ");
    lCmdtySqlBuf.append(" ELSE false END "); 
    lCmdtySqlBuf.append("ORDER BY favoritelist.totalcount DESC,favoritelist.unitprice DESC,favoritelist.individualcode ");
    if(max>0) lCmdtySqlBuf.append("LIMIT ").append(max);
    
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lCmdtySqlBuf.toString());
      
      while (lResultSet.next()) {
        lCmdty = new SICmdty();
        lCmdty.setEncode(SIConfig.SIENCODE_NONE);
        lCmdty.setMemberDiscountFlg(lResultSet.getString("MemberDiscountFlg"));
        lCmdty.setMemberDiscountRate(lUserInfo.getDiscountRate());
        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.setCalcUnitPrice(lResultSet.getString("UnitPrice"));
        lCmdty.setAmount(lResultSet.getString("Amount"));// 在庫数量
        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.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.setContractFlg(lResultSet.getString("contractflg").equals("1"));
        if ("H".equals(lResultSet.getString("IndividualCode").substring(0,1))||"V".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.setPointRate(lResultSet.getString("pointRate"));// ボーナスポイントレート
        lCmdty.setNomalPointRate(lResultSet.getString("nomalPointRate"));// 通常ポイントレート
        lCmdty.setFreeDeliveryFlg(lResultSet.getString("freeDeliveryFlg"));
        lCmdty.setPriceDownFlg(lResultSet.getString("priceDownFlg"));
        // EDBTG003-00 nagayoshi add start
        lCmdty.setCmdtyCompositionFlg(lResultSet.getString("cmdtyCompositionFlg"));
        // EDBTG003-00 nagayoshi add end
        lCmdtys.add(lCmdty);
      }
    
    } catch (SQLException ex) {
      ex.printStackTrace();
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lCmdtys;
  }
  
  public Collection getCampaignList(Connection lConnection,HttpServletRequest request,String itemList,boolean soldOutDisp) throws SIDBAccessException {
    SIUserInfo lUserInfo = new SIUserInfo();
    if (request != null) lUserInfo = SIHTMLUtil.getUserInfo(request);
    String[] itemList2 = itemList.split(",");
    if (itemList2==null || itemList2.length==0) return new ArrayList();
    
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SICmdty lCmdty = new SICmdty();
    StringBuffer lCmdtySqlBuf = new StringBuffer();
    StringBuffer lCountSqlBuf = new StringBuffer();
    StringBuffer lCommonSqlBuf = new StringBuffer();
    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 ? "," : ")");
      }
    }
    
    lCmdtySqlBuf.append("SELECT a.cmdtycode,a.individualcode,a.cmdtyname,a.nomalprice,a.unitprice,a.taxflg,a.bgpricedispflg,a.amount,a.memberdiscountflg");
    lCmdtySqlBuf.append(",a.cmdtyCompositionFlg,a.usednewflg,a.amountflg,a.unitpricewithouttax");
    lCmdtySqlBuf.append(",CASE WHEN comp.dispcarriagefree IS NOT NULL THEN comp.dispcarriagefree ");
    lCmdtySqlBuf.append("      WHEN a.deliveryTypeCode='0' AND a.cmdtySize IN ('1','7') THEN 1 ");//送料無料（個別AGサイズ）
    lCmdtySqlBuf.append("      WHEN a.deliveryTypeCode='1' AND a.unitPrice>=feerule.price THEN 1 ");//送料無料（小物）
    lCmdtySqlBuf.append(" ELSE 0 END AS freedeliveryflg ");
    lCmdtySqlBuf.append(",CASE WHEN maker.contractflg='0' THEN 0");
    if(SIUtil.isNotNull(makerString.toString())){
      lCmdtySqlBuf.append(" WHEN maker.makercode IN ").append(makerString).append(" THEN 0");
    }
    lCmdtySqlBuf.append(" ELSE 1 END AS contractflg ");
    lCmdtySqlBuf.append(",COALESCE((SELECT taxrate FROM TaxVW),0) AS taxrate ");
    lCmdtySqlBuf.append(",CASE a.individualcode ");
    for (int j=0;j<itemList2.length;j++){
      lCmdtySqlBuf.append("WHEN ").append(SIDBUtil.SQL2Str(itemList2[j])).append(" THEN ").append(String.valueOf(j));
      lCmdtySqlBuf.append(j < itemList2.length - 1 ? " " : " ELSE 10000 END AS sortindex ");
    }
    
    lCountSqlBuf.append("SELECT count(a.individualcode) ");
    
    lCommonSqlBuf.append("FROM cmdtyunittbl a ");
    lCommonSqlBuf.append("LEFT OUTER JOIN cmdtycompositionmtbl comp ");
    lCommonSqlBuf.append("ON a.cmdtycode=comp.cmdtycode AND a.individualcode=comp.individualcode ");
    lCommonSqlBuf.append(",makertbl maker ,feerulemtbl feerule ");
    lCommonSqlBuf.append("WHERE a.individualcode IN (");
    for (int k=0;k<itemList2.length;k++){
      lCommonSqlBuf.append(SIDBUtil.SQL2Str(itemList2[k]));
      lCommonSqlBuf.append(k < itemList2.length - 1 ? "," : ")");
    }
    lCommonSqlBuf.append("AND a.frontdispflg='1' ");
    if (!soldOutDisp) {
      lCommonSqlBuf.append("AND NOT (a.amountflg='1' AND (a.amount-a.bgpamount)='0') ");
    }
    lCommonSqlBuf.append("AND NOT (a.amountflg='1' AND (a.amount-a.bgpamount)='0' AND a.rsrvenableflg='1') AND a.inquiryflg='0' ");//予約、問い合わせは出さない
    lCommonSqlBuf.append("AND a.makercode = maker.makercode AND a.deliverytypecode = feerule.deliverytypecode ");
    lCmdtySqlBuf.append(lCommonSqlBuf);
    lCmdtySqlBuf.append("ORDER BY sortindex ");
    lCountSqlBuf.append(lCommonSqlBuf);
    
    // 実行
    try {
      String rowCnt = SIDBUtil.getFirstData(lConnection, lCountSqlBuf.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:lSqlBuf=" + 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.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.setCalcUnitPrice(lResultSet.getString("UnitPrice"));
        lCmdty.setTaxRate(lResultSet.getString("taxrate"));
        lCmdty.setTaxFlg(lResultSet.getString("TaxFlg"));
        lCmdty.setBGPriceDispFlg(lResultSet.getString("BGpriceDispFlg"));
        lCmdty.setNewFixedPrice(lResultSet.getString("NomalPrice"));// 通常価格
        lCmdty.setAmount(lResultSet.getString("Amount"));
        lCmdty.setAmountFlg(lResultSet.getString("AmountFlg"));
        lCmdty.setUsedNewFlg(lResultSet.getInt("UsedNewFlg"));
        lCmdty.setContractFlg(lResultSet.getString("contractFlg").equals("1"));
        if ("H".equals(lResultSet.getString("IndividualCode").substring(0,1))||"V".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.setCmdtyCompositionFlg(lResultSet.getString("cmdtyCompositionFlg"));
        lCmdty.setMemberDiscountRate(lUserInfo.getDiscountRate());
        lCmdtys.add(lCmdty);
        lIndex++;
      }
      for (int jj = lFromInx + lPageSize; jj < lRecordCount; jj++)
        lCmdtys.add(null);
      
    } catch (SQLException ex) {
      ex.printStackTrace();
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lCmdtys;
  }
  /*
  public Collection getCampaignListMatrix(Connection lConnection,HttpServletRequest request,String itemList,boolean soldOutDisp) throws SIDBAccessException {
    SIUserInfo lUserInfo = new SIUserInfo();
    if (request != null) lUserInfo = SIHTMLUtil.getUserInfo(request);
    String[] itemList2 = itemList.split(",");
    if (itemList2==null || itemList2.length==0) return new ArrayList();
    
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SICmdty lCmdty = new SICmdty();
    StringBuffer lCmdtySqlBuf = new StringBuffer();
    StringBuffer lCountSqlBuf = new StringBuffer();
    StringBuffer lCommonSqlBuf = new StringBuffer();
    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 ? "," : ")");
      }
    }
    
    lCmdtySqlBuf.append("SELECT a.cmdtycode,a.individualcode,a.cmdtyname,a.nomalprice,a.unitprice,a.taxflg,a.bgpricedispflg,a.amount,a.memberdiscountflg");
    lCmdtySqlBuf.append(",a.cmdtyCompositionFlg,a.usednewflg,a.amountflg,a.unitpricewithouttax");
    lCmdtySqlBuf.append(",CASE WHEN comp.dispcarriagefree IS NOT NULL THEN comp.dispcarriagefree ");
    lCmdtySqlBuf.append("      WHEN a.deliveryTypeCode='0' AND a.cmdtySize IN ('1','7') THEN 1 ");//送料無料（個別AGサイズ）
    lCmdtySqlBuf.append("      WHEN a.deliveryTypeCode='1' AND a.unitPrice>=feerule.price THEN 1 ");//送料無料（小物）
    lCmdtySqlBuf.append(" ELSE 0 END AS freedeliveryflg ");
    lCmdtySqlBuf.append(",CASE WHEN maker.contractflg='0' THEN 0");
    if(SIUtil.isNotNull(makerString.toString())){
      lCmdtySqlBuf.append(" WHEN maker.makercode IN ").append(makerString).append(" THEN 0");
    }
    lCmdtySqlBuf.append(" ELSE 1 END AS contractflg ");
    lCmdtySqlBuf.append(",COALESCE((SELECT taxrate FROM TaxVW),0) AS taxrate ");
    lCmdtySqlBuf.append(",CASE a.individualcode ");
    for (int j=0;j<itemList2.length;j++){
      if (SIUtil.isNull(itemList2[j])) continue;
      lCmdtySqlBuf.append("WHEN ").append(SIDBUtil.SQL2Str(itemList2[j])).append(" THEN ").append(String.valueOf(j));
      lCmdtySqlBuf.append(j < itemList2.length - 1 ? " " : " ELSE 10000 END AS sortindex ");
    }
    
    lCountSqlBuf.append("SELECT count(a.individualcode) ");
    
    lCommonSqlBuf.append("FROM cmdtyunittbl a ");
    lCommonSqlBuf.append("LEFT OUTER JOIN cmdtycompositionmtbl comp ");
    lCommonSqlBuf.append("ON a.cmdtycode=comp.cmdtycode AND a.individualcode=comp.individualcode ");
    lCommonSqlBuf.append(",makertbl maker ,feerulemtbl feerule ");
    lCommonSqlBuf.append("WHERE a.individualcode IN (");
    for (int k=0;k<itemList2.length;k++){
      if (SIUtil.isNull(itemList2[k])) continue;
      lCommonSqlBuf.append(SIDBUtil.SQL2Str(itemList2[k]));
      lCommonSqlBuf.append(k < itemList2.length - 1 ? "," : ")");
    }
    lCommonSqlBuf.append("AND a.frontdispflg='1' ");
    if (!soldOutDisp) {
      lCommonSqlBuf.append("AND NOT (a.amountflg='1' AND (a.amount-a.bgpamount)='0') ");
    }
    lCommonSqlBuf.append("AND NOT (a.amountflg='1' AND (a.amount-a.bgpamount)='0' AND a.rsrvenableflg='1') AND a.inquiryflg='0' ");//予約、問い合わせは出さない
    lCommonSqlBuf.append("AND a.makercode = maker.makercode AND a.deliverytypecode = feerule.deliverytypecode ");
    lCmdtySqlBuf.append(lCommonSqlBuf);
    lCmdtySqlBuf.append("ORDER BY sortindex ");
    lCountSqlBuf.append(lCommonSqlBuf);
    
    // 実行
    try {
      // 商品レコードのセットの作成
      for (int k=0;k<itemList2.length;k++){
        if (SIUtil.isNull(itemList2[k])) {//リストのブランクはnewで埋める
          lCmdtys.add(new SICmdty());
        } else {//リストのコード指定部はnext()実行
          if (lResultSet.next()) {
            lCmdty = new SICmdty();
            lCmdty.setEncode(SIConfig.SIENCODE_NONE);
            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.setCalcUnitPrice(lResultSet.getString("UnitPrice"));
            lCmdty.setTaxRate(lResultSet.getString("taxrate"));
            lCmdty.setTaxFlg(lResultSet.getString("TaxFlg"));
            lCmdty.setBGPriceDispFlg(lResultSet.getString("BGpriceDispFlg"));
            lCmdty.setNewFixedPrice(lResultSet.getString("NomalPrice"));// 通常価格
            lCmdty.setAmount(lResultSet.getString("Amount"));
            lCmdty.setAmountFlg(lResultSet.getString("AmountFlg"));
            lCmdty.setUsedNewFlg(lResultSet.getInt("UsedNewFlg"));
            lCmdty.setContractFlg(lResultSet.getString("contractFlg").equals("1"));
            if ("H".equals(lResultSet.getString("IndividualCode").substring(0,1))||"V".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.setCmdtyCompositionFlg(lResultSet.getString("cmdtyCompositionFlg"));
            lCmdty.setMemberDiscountRate(lUserInfo.getDiscountRate());
            lCmdtys.add(lCmdty);
          }else{//例外として存在しないコードが指定されていた時は埋め
            lCmdtys.add(new SICmdty());
          }
        }
      }
    } catch (SQLException ex) {
      ex.printStackTrace();
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lCmdtys;
  }
  */
}