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

import java.io.IOException;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedHashMap;

import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import jp.co.sint.basic.SICart;
import jp.co.sint.basic.SICartCmdty;
import jp.co.sint.basic.SICartManager;
import jp.co.sint.basic.SICmdty;
import jp.co.sint.basic.SIUserInfo;
import jp.co.sint.beans.front.UICmdtyInfo;
import jp.co.sint.config.SIConfig;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.database.SIDatabaseConnection;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.servlet.SIServlet;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIURLMap;
import jp.co.sint.tools.SIUtil;
import jp.co.sint.tools.SIURLParameter;

import org.apache.log4j.Category;

/**
 * @version $Id: SICmdtySrv.java,v 1.0 2003/10/24 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/24 14:31:19 Original
 */
public class SICommoditySrv extends SIServlet {
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  /**
   * <b>doUpdate</b> HTTP リクエストの処理
   * 
   * @param request リクエスト
   * @param response
   * @return なし
   * @throws ServletException
   * @throws IOException
   */
  public void doUpdate(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    HttpSession session = request.getSession(true);// セッションの取得
    SIDatabaseConnection databaseConnection = new SIDatabaseConnection();// DBへのコネクションの作成
    SIURLParameter urlParam = new SIURLParameter(request);// 7.1.1 ST0236 追加
    
    try {
      String actionName = this.getActionName(urlParam);// 画面からのアクション//7.1.1 ST0236 修正
      String editMode = this.getEditMode(urlParam);// DBへの編集モード//7.1.1 ST0236 修正
      UICmdtyInfo cmdtyList = new UICmdtyInfo();
      
      if (SIUtil.isNull(actionName) || SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName)) {// 一覧と検索などの画面
        /* ログイン必須
        SIUserInfo lUserInfo = SIHTMLUtil.getUserInfo(request);
        String cmdtyCodeTxt = (String) urlParam.getParam("cmc");
        String individualCodeTxt = (String) urlParam.getParam("inc");
        String ctgryCodeTxt = (String) urlParam.getParam("ctc");
        String backURL = (String) urlParam.getParam("backURL");
        
        StringBuffer loginStr = new StringBuffer();
        
        loginStr.append("SELECT cmdtycode FROM cmdtyctgrymtbl WHERE ctgrycode IN (");
        loginStr.append("SELECT ctgrycode FROM ctgryvw ");
        loginStr.append(" WHERE (");
        for (int i=0;i<SIConfig.LOGINCTGRY.length;i++){
          if (i>0) loginStr.append(" OR ");
          loginStr.append(" (branch LIKE '%~").append(SIDBUtil.SQL2Like(SIConfig.LOGINCTGRY[i])).append("%'");
          loginStr.append(" OR ctgrycode = ").append(SIDBUtil.SQL2Str(SIConfig.LOGINCTGRY[i], ")"));
        }
        loginStr.append(")) ");
        loginStr.append(" cmdtycode = ").append(SIDBUtil.SQL2Str(cmdtyCodeTxt));
        
        if (lUserInfo.isNotLogin() && SIDBUtil.hasData(databaseConnection.getConnection(), loginStr.toString())) {
          StringBuffer lUrlStr = new StringBuffer();
          String lCtc = "ctc=" + SIHTMLUtil.URLEncode(ctgryCodeTxt);
          lUrlStr.append(SIURLMap.getUrl("webshop.servlet.front.user.login")).append("?").append(lCtc);
          lUrlStr.append("&nid=").append(SIURLMap.getUrl("webshop.servlet.front.commodity"));
          lUrlStr.append("/").append(lCtc).append("/shc=0/cmc=").append(cmdtyCodeTxt);
          lUrlStr.append("/inc=").append(individualCodeTxt).append("/backURL=").append(backURL);
          redirectHttps(request, response, lUrlStr.toString());
        } else {
        */
          // データの取得とデータのチェック
          cmdtyList = new UICmdtyInfo();
          cmdtyList.init(request, urlParam);
          session.setAttribute(SIConfig.SISESSION_CMDTY_LIST_NAME, cmdtyList);
          writeCmdtyHistory(request,response,cmdtyList.getIndividualCodeTxt());
          
          if (this.getServletPath(request).equals("/hair_cmdty")) {
            forwardKey(request, response, "webshop.jsp.front.commodity.hair");
          } else if (this.getServletPath(request).equals("/haircosme_cmdty")) {
            forwardKey(request, response, "webshop.jsp.front.commodity.haircosme");
          } else if (this.getServletPath(request).equals("/esthe_cmdty")) {
            forwardKey(request, response, "webshop.jsp.front.commodity.esthe");
          } else if (this.getServletPath(request).equals("/bed_cmdty")) {
            forwardKey(request, response, "webshop.jsp.front.commodity.bed");
          } else if (this.getServletPath(request).equals("/cosme_cmdty")) {
            forwardKey(request, response, "webshop.jsp.front.commodity.cosme");
          } else if (this.getServletPath(request).equals("/nail_cmdty")) {
            forwardKey(request, response, "webshop.jsp.front.commodity.nail");
          } else if (this.getServletPath(request).equals("/closedsale_cmdty")) {
            forwardKey(request, response, "webshop.jsp.front.commodity.closedsale");
          } else {
            forwardKey(request, response, "webshop.jsp.front.commodity.info");
          }
        //}
      } else if (SIConfig.SIACTION_LOGIN.equalsIgnoreCase(actionName)) {
        String cmdtyCodeTxt = (String) urlParam.getParam("cmc");
        String individualCodeTxt = (String) urlParam.getParam("inc");
        String ctgryCodeTxt = (String) urlParam.getParam("ctc");
        String backURL = (String) urlParam.getParam("backURL");
        
        StringBuffer lUrlStr = new StringBuffer();
        String lCtc = "ctc=" + SIHTMLUtil.URLEncode(ctgryCodeTxt);
        lUrlStr.append(SIURLMap.getUrl("webshop.servlet.front.user.login")).append("?").append(lCtc);
        lUrlStr.append("&nid=").append(SIURLMap.getUrl("webshop.servlet.front.commodity"));
        lUrlStr.append("/").append(lCtc).append("/shc=0/cmc=").append(cmdtyCodeTxt);
        lUrlStr.append("/inc=").append(individualCodeTxt).append("/backURL=").append(backURL);
        redirectHttps(request, response, lUrlStr.toString());
      } else if (SIConfig.SIACTION_CART.equalsIgnoreCase(actionName)) {
        cmdtyList = new UICmdtyInfo();
        cmdtyList.initCart(urlParam);
        // EDBTG003-00 nagayoshi add start
        if (!cmdtyList.getCmdtyCompositionFlg().equals(SIConfig.CMDTY_COMPOSITION_NORMAL)) {
          // カート投入商品が通常品以外の場合、画面パラメータよりセット品構成情報・オプション情報を取得
          cmdtyList.initSetCart(request, urlParam);
        }
        // EDBTG003-00 nagayoshi add end
        cmdtyList.addCart(request, databaseConnection.getConnection());
        if (this.getServletPath(request).equals("/hair_cmdty")) {
          forwardKey(request, response, "webshop.jsp.front.commodity.hair");
        } else if (this.getServletPath(request).equals("/haircosme_cmdty")) {
          forwardKey(request, response, "webshop.jsp.front.commodity.haircosme");
        } else if (this.getServletPath(request).equals("/esthe_cmdty")) {
          forwardKey(request, response, "webshop.jsp.front.commodity.esthe");
        } else if (this.getServletPath(request).equals("/bed_cmdty")) {
          forwardKey(request, response, "webshop.jsp.front.commodity.bed");
        } else if (this.getServletPath(request).equals("/cosme_cmdty")) {
          forwardKey(request, response, "webshop.jsp.front.commodity.cosme");
        } else if (this.getServletPath(request).equals("/nail_cmdty")) {
          forwardKey(request, response, "webshop.jsp.front.commodity.nail");
        } else if (this.getServletPath(request).equals("/closedsale_cmdty")) {
          forwardKey(request, response, "webshop.jsp.front.commodity.closedsale");
        } else {
          forwardKey(request, response, "webshop.jsp.front.commodity.info");
        }
      } else if (SIConfig.SIACTION_CART2.equalsIgnoreCase(actionName)) {
        cmdtyList = new UICmdtyInfo();
        cmdtyList.initCart(urlParam);
        if (!cmdtyList.getCmdtyCompositionFlg().equals(SIConfig.CMDTY_COMPOSITION_NORMAL)) {
          // カート投入商品が通常品以外の場合、画面パラメータよりセット品構成情報・オプション情報を取得
          cmdtyList.initSetCart(request, urlParam);
        }
        cmdtyList.addCart(request, databaseConnection.getConnection());
        forward(request, response, "/other/cart_detail.jsp");
      } else if (SIConfig.SIACTION_ADD.equalsIgnoreCase(actionName)) {//マイカタログ追加
        cmdtyList = new UICmdtyInfo();
        cmdtyList.initCart(urlParam);
        try {
          String custCode = SIHTMLUtil.getUserInfo(request).getCustCode();
          StringBuffer lSql = new StringBuffer();
          lSql.append("INSERT INTO mycatalogtbl ");
          lSql.append("SELECT ").append(custCode);
          lSql.append(",").append(SIDBUtil.SQL2Str(cmdtyList.getCartIndividualCodeTxt()));
          lSql.append(",coalesce(max(disporder),0)+1 ");
          lSql.append("FROM mycatalogtbl WHERE custcode=").append(SIDBUtil.SQL2Str(custCode));
          
          SIDBUtil.execSQL(databaseConnection.getConnection(), lSql.toString());
          try {databaseConnection.getConnection().commit();} catch (SQLException ee) {}
        } catch (Exception e) {
          try {databaseConnection.getConnection().rollback();} catch (SQLException ee) {}
          e.printStackTrace();
        }
        forwardKey(request, response, "webshop.jsp.front.mypage.mycatalog");
      } else if (SIConfig.SIACTION_ADD2.equalsIgnoreCase(actionName)) {//マイカタログ追加
        cmdtyList = new UICmdtyInfo();
        cmdtyList.initCart(urlParam);
        try {
          String custCode = SIHTMLUtil.getUserInfo(request).getCustCode();
          StringBuffer lSql = new StringBuffer();
          lSql.append("INSERT INTO mycatalogtbl ");
          lSql.append("SELECT ").append(custCode);
          lSql.append(",").append(SIDBUtil.SQL2Str(cmdtyList.getCartIndividualCodeTxt()));
          lSql.append(",coalesce(max(disporder),0)+1 ");
          lSql.append("FROM mycatalogtbl WHERE custcode=").append(SIDBUtil.SQL2Str(custCode));
          
          SIDBUtil.execSQL(databaseConnection.getConnection(), lSql.toString());
          try {databaseConnection.getConnection().commit();} catch (SQLException ee) {}
        } catch (Exception e) {
          try {databaseConnection.getConnection().rollback();} catch (SQLException ee) {}
          e.printStackTrace();
        }
      } else if (SIConfig.SIACTION_STNDRD.equalsIgnoreCase(actionName)) {// 規格画像の表示
        // データの取得とデータのチェック
        cmdtyList = new UICmdtyInfo();
        cmdtyList.init(request, urlParam);
        session.setAttribute(SIConfig.SISESSION_CMDTY_LIST_NAME, cmdtyList);
        forwardKey(request, response, "webshop.jsp.front.commodity.info");
      } else if (SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)) {
        cmdtyList = new UICmdtyInfo();
        cmdtyList.init(request, urlParam);
        session.setAttribute(SIConfig.SISESSION_CMDTY_LIST_NAME, cmdtyList);
        forwardKey(request, response, "webshop.jsp.front.commodity.info");
      } else if (SIConfig.SIACTION_PREVIEW.equalsIgnoreCase(actionName)) {
        cmdtyList = new UICmdtyInfo();
        cmdtyList.initPopup(request, databaseConnection.getConnection(), urlParam);
        session.setAttribute(SIConfig.SISESSION_CMDTY_POPUP_NAME, cmdtyList);
        forwardKey(request, response, "webshop.jsp.front.cmdty.popup");
      } else if (SIConfig.SIACTION_PREVIEW2.equalsIgnoreCase(actionName)) {
        cmdtyList = new UICmdtyInfo();
        cmdtyList.initPopup(request, databaseConnection.getConnection(), urlParam);
        session.setAttribute(SIConfig.SISESSION_CMDTY_POPUP_NAME, cmdtyList);
        forwardKey(request, response, "webshop.jsp.front.cmdty.popup2");
      } else {}
    } catch (SQLException e) {
      e.printStackTrace();
      throw new ServletException();
    } catch (NamingException e) {
      e.printStackTrace();
      throw new ServletException();
    } catch (SIDBAccessException e) {
      e.printStackTrace();
      throw new ServletException();
    } finally {
      databaseConnection.close();
    }
  }
  
  private void writeCmdtyHistory(HttpServletRequest request, HttpServletResponse response, String individualCodeTxt) throws IOException {
    if (SIUtil.isNull(individualCodeTxt)) return;
    Cookie[] cookies = request.getCookies();
    Cookie cmdtyCookieOrg = null;
    if (cookies != null) {
      for (int i = 0; i < cookies.length; i++) {
        if (cookies[i].getName().equals("ws_historyBG")) cmdtyCookieOrg = cookies[i];
      }
    }
    String value = "";
    if (cmdtyCookieOrg == null) {
      value = URLEncoder.encode(individualCodeTxt, "Shift_JIS");
    } else {
      String[] items = URLDecoder.decode(cmdtyCookieOrg.getValue(), "Shift_JIS").split(",");
      String data = individualCodeTxt;
      for (int i=0;i<items.length;i++) {
        if (SIUtil.isNotNull(individualCodeTxt)&&individualCodeTxt.equals(items[i])) continue;
        else data = data + "," + items[i];
        if (i>=100) break;
      }
      value = URLEncoder.encode(data, "Shift_JIS");
    }
    Cookie cmdtyCookie = new Cookie("ws_historyBG", value);
    cmdtyCookie.setMaxAge(60 * 60 * 24 * 30);
    cmdtyCookie.setPath(SIHTMLUtil.getRootPath(request));
    response.addCookie(cmdtyCookie);
  }
  
  public static Collection readCmdtyHistory(HttpServletRequest request,Connection lConnection) throws IOException {
    return readCmdtyHistory(request, lConnection,5,false);
  }
  public static Collection readCmdtyHistory(HttpServletRequest request,Connection lConnection,int max) throws IOException {
    return readCmdtyHistory(request, lConnection,max,false);
  }
  public static Collection readCmdtyHistory(HttpServletRequest request,Connection lConnection,int max,boolean isCart) throws IOException {
    SIUserInfo lUserInfo = new SIUserInfo();
    
    if (request != null) lUserInfo = SIHTMLUtil.getUserInfo(request);
    
    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 ? "," : ")");
      }
    }
    
    Cookie[] cookies = request.getCookies();
    Cookie cmdtyCookie = null;
    StringBuffer cookiesStr = new StringBuffer();
    if (cookies != null) {
      for (int i = 0; i < cookies.length; i++) {
        if (cookies[i].getName().equals("ws_historyBG")) cmdtyCookie = cookies[i];
      }
    }
    if (cmdtyCookie != null) {
      String[] value = URLDecoder.decode(cmdtyCookie.getValue(), "Shift_JIS").split(",");
      
      for (int j=0;j<value.length;j++) {
        if(j>0) cookiesStr.append(",");
        cookiesStr.append(SIDBUtil.SQL2Str(value[j]));
      }
    }else return new ArrayList();
    Collection res = new ArrayList();
    Collection res2 = new ArrayList();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    StringBuffer lSql = new StringBuffer();
    lSql.append("SELECT unit.cmdtyCode,unit.IndividualCode,unit.unitPrice,unit.newFixedPrice,unit.description,unit.memberDiscountFlg,unit.makercode ");
    lSql.append(",CASE WHEN unit.bgPriceDispFlg='2' THEN 2 ");
    if(!lUserInfo.isLogin()){
      lSql.append("    WHEN unit.bgPriceDispFlg='1' THEN 1 ");
    }
    lSql.append("      ELSE 0 END AS bgPriceDispFlg ");
    lSql.append(",CASE WHEN comp.dispcarriagefree IS NOT NULL AND comp.dispcarriagefree='1' THEN unit.cmdtyName||'【送料無料】' ");
    lSql.append("      WHEN comp.dispcarriagefree IS NOT NULL AND comp.dispcarriagefree='0' THEN unit.cmdtyName ");
    lSql.append("      WHEN unit.deliveryTypeCode='0' AND unit.cmdtySize IN ('1','7') THEN unit.cmdtyName||'【送料無料】' ");
    lSql.append("      WHEN unit.deliveryTypeCode='1' AND unit.unitPrice>=feerule.price THEN unit.cmdtyName||'【送料無料】' ");
    lSql.append("      ELSE unit.cmdtyName END AS cmdtyName ");
    lSql.append(",CASE WHEN maker.contractflg='0' THEN 0");
    if(SIUtil.isNotNull(makerString.toString())){
      lSql.append("    WHEN maker.makercode IN ").append(makerString).append(" THEN 0");
    }
    lSql.append("      ELSE 1 END AS contractFlg ");
    lSql.append("FROM cmdtyUnitTbl unit ");
    lSql.append("LEFT OUTER JOIN cmdtycompositionmtbl comp ");
    lSql.append("ON unit.cmdtycode=comp.cmdtycode AND unit.individualcode=comp.individualcode ");
    lSql.append(",makerTbl maker,feerulemtbl feerule ");
    lSql.append("WHERE unit.makerCode=maker.makerCode AND unit.frontDispFlg=1 AND usednewFlg IN('1','6','7') AND unit.deliverytypecode = feerule.deliverytypecode ");
    if (isCart) {
      lSql.append("AND comp.individualcode IS NULL ");
      lSql.append("AND NOT(unit.amountflg='1' AND unit.amount='0') AND unit.inquiryflg!='1' ");
    } else {
      lSql.append("AND NOT(unit.usedNewFlg!='1' AND unit.usedNewFlg!='7' AND (unit.rsrvenableflg='0' OR (unit.rsrvamount IS NOT NULL AND unit.rsrvamount='0')) AND unit.amountflg='1' AND unit.amount='0' AND (unit.soldoutdate IS NULL OR unit.soldoutdate + "+SIConfig.DEFAULT_SOLDOUT+" <= CURRENT_DATE)) ");
    }
    lSql.append("AND unit.individualcode IN (").append(cookiesStr.toString()).append(") ");
    
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      while(lResultSet.next()){
        SICmdty lCmdty = new SICmdty();
        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.setDescription(lResultSet.getString("description"));
        lCmdty.setNewFixedPrice(lResultSet.getString("newFixedPrice"));
        lCmdty.setBGPriceDispFlg(lResultSet.getString("bgPriceDispFlg"));
        lCmdty.setContractFlg("1".equals(lResultSet.getString("contractFlg")));
        lCmdty.setMemberDiscountFlg(lResultSet.getString("memberDiscountFlg"));
        lCmdty.setMemberDiscountRate(lUserInfo.getDiscountRate());
        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);
        }
        res.add(lCmdty);
      }
      String[] value = URLDecoder.decode(cmdtyCookie.getValue(), "Shift_JIS").split(",");
      for (int k=0;k<value.length;k++){
        Iterator ite = res.iterator();
        while (ite.hasNext()){
          SICmdty lCmdty = new SICmdty();
          lCmdty = (SICmdty)ite.next();
          if (lCmdty.getIndividualCode().equals(value[k])) {
            res2.add(lCmdty);
            break;
          }
        }
        if (res2.size()==max) break;
      }
    } catch (Exception e) {
      e.printStackTrace();
      res2 = new ArrayList();
    }
    return res2;
  }
  
  public static Collection readCartRecommend(HttpServletRequest request,Connection lConnection) throws IOException {
    SIUserInfo lUserInfo = new SIUserInfo();
    StringBuffer cartBuf = new StringBuffer();
    
    if (request != null) lUserInfo = SIHTMLUtil.getUserInfo(request);
    SICartManager cartManager = (SICartManager) request.getSession(true).getAttribute(SIConfig.SISESSION_CART_INFO_NAME);
    if (cartManager==null) cartManager=new SICartManager();
    Collection cartColl = cartManager.getCartCollection("0");
    if (cartColl!=null){
      Iterator it = cartColl.iterator();
      while (it.hasNext()) {
        SICart cartID = (SICart)it.next();
        if (cartID.getCmdty().isCmdtyComposition()) {
          LinkedHashMap workMap = cartID.getCmdty().getCartsCmdtyCompositionDetailMap();
          Collection discountPraiceColl = (Collection)workMap.get(cartID.getCmdty().getCmdtyCompositionKeySet());
          Iterator it2 = discountPraiceColl.iterator();
          while (it2.hasNext()) {
            SICartCmdty cartCmdtyID = (SICartCmdty)it2.next();
            cartBuf.append(SIDBUtil.SQL2Str(cartCmdtyID.getIndividualCode()));
            if (it.hasNext()||it2.hasNext()) cartBuf.append(",");
          }
        }else{
          cartBuf.append(SIDBUtil.SQL2Str(cartID.getCmdty().getIndividualCode()));
          if (it.hasNext()) cartBuf.append(",");
        }
      }
    }else return new ArrayList();
    log.debug("cartRecommend[codes]:"+cartBuf.toString());
    
    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 ? "," : ")");
      }
    }
    
    Collection res = new ArrayList();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    StringBuffer lSql = new StringBuffer();
    lSql.append("SELECT unit.cmdtyCode,unit.IndividualCode,unit.unitPrice,unit.newFixedPrice,unit.description,unit.memberDiscountFlg,unit.makercode ");
    lSql.append(",CASE WHEN unit.bgPriceDispFlg='2' THEN 2 ");
    if(!lUserInfo.isLogin()){
      lSql.append("    WHEN unit.bgPriceDispFlg='1' THEN 1 ");
    }
    lSql.append("      ELSE 0 END AS bgPriceDispFlg ");
    lSql.append(",CASE WHEN comp.dispcarriagefree IS NOT NULL AND comp.dispcarriagefree='1' THEN unit.cmdtyName||'【送料無料】' ");
    lSql.append("      WHEN comp.dispcarriagefree IS NOT NULL AND comp.dispcarriagefree='0' THEN unit.cmdtyName ");
    lSql.append("      WHEN unit.deliveryTypeCode='0' AND unit.cmdtySize IN ('1','7') THEN unit.cmdtyName||'【送料無料】' ");
    lSql.append("      WHEN unit.deliveryTypeCode='1' AND unit.unitPrice>=feerule.price THEN unit.cmdtyName||'【送料無料】' ");
    lSql.append("      ELSE unit.cmdtyName END AS cmdtyName ");
    lSql.append(",CASE WHEN maker.contractflg='0' THEN 0");
    if(SIUtil.isNotNull(makerString.toString())){
      lSql.append("    WHEN maker.makercode IN ").append(makerString).append(" THEN 0");
    }
    lSql.append("      ELSE 1 END AS contractFlg ");
    lSql.append(",cr.ordercount,cr.totalofprice ");
    lSql.append("FROM cmdtyUnitTbl unit ");
    lSql.append("LEFT OUTER JOIN cmdtycompositionmtbl comp ");
    lSql.append("ON unit.cmdtycode=comp.cmdtycode AND unit.individualcode=comp.individualcode ");
    lSql.append(",makerTbl maker,feerulemtbl feerule ");
    lSql.append(",(SELECT individualcode,sum(ordercount) AS ordercount,sum(totalofprice) AS totalofprice ");
    lSql.append(" FROM cartrecommendbasetbl ");
    lSql.append(" WHERE individualcode2 IN (").append(cartBuf.toString()).append(") ");
    lSql.append(" AND individualcode NOT IN (").append(cartBuf.toString()).append(") ");
    lSql.append(" GROUP BY individualcode,cmdtyname) cr ");
    lSql.append("WHERE unit.makerCode=maker.makerCode AND unit.frontDispFlg=1 AND usednewFlg IN('1','6','7') AND unit.deliverytypecode = feerule.deliverytypecode ");
    lSql.append("AND comp.individualcode IS NULL ");
    lSql.append("AND NOT(unit.amountflg='1' AND unit.amount='0') AND unit.inquiryflg!='1' ");
    lSql.append("AND unit.individualcode=cr.individualcode ");
    lSql.append("ORDER BY cr.ordercount DESC,cr.totalofprice DESC LIMIT 50 ");
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      while(lResultSet.next()){
        SICmdty lCmdty = new SICmdty();
        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.setDescription(lResultSet.getString("description"));
        lCmdty.setNewFixedPrice(lResultSet.getString("newFixedPrice"));
        lCmdty.setBGPriceDispFlg(lResultSet.getString("bgPriceDispFlg"));
        lCmdty.setContractFlg("1".equals(lResultSet.getString("contractFlg")));
        lCmdty.setMemberDiscountFlg(lResultSet.getString("memberDiscountFlg"));
        lCmdty.setMemberDiscountRate(lUserInfo.getDiscountRate());
        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);
        }
        res.add(lCmdty);
      }
    } catch (Exception e) {
      e.printStackTrace();
      res = new ArrayList();
    }
    return res;
  }
}