package jp.co.sint.beans.front;

import java.util.ArrayList;
import jp.co.sint.tools.SIDateTime;
import java.util.Collection;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;

import javax.servlet.http.HttpServletRequest;

import jp.co.sint.basic.SICmdty;
import jp.co.sint.basic.SIUserInfo;
import jp.co.sint.config.SIConfig;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;

public class UICmdtyRanking {
  //ログ用のインスタンスの生成
  private static Category log=Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  public static Collection getRecommendColl(Connection lConnection,HttpServletRequest request) {
    return getCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_ALL,SIConfig.RECOMMEND_TOP_ITEMS);
  }
  public static Collection getRecommendHairColl(Connection lConnection,HttpServletRequest request) {
    return getCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_HAIR,SIConfig.RECOMMEND_HAIR_ITEMS);
  }
  public static Collection getRecommendHairCosmeColl(Connection lConnection,HttpServletRequest request) {
    return getCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_HAIRCOSME,SIConfig.RECOMMEND_HAIRCOSME_ITEMS);
  }
  public static Collection getRecommendHairCosmeColl2(Connection lConnection,HttpServletRequest request) {
    return getCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_HAIRCOSME2,SIConfig.RECOMMEND_HAIRCOSME_ITEMS);
  }
  public static Collection getRecommendEstheColl(Connection lConnection,HttpServletRequest request) {
    return getCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_ESTHE,SIConfig.RECOMMEND_ESTHE_ITEMS);
  }
  public static Collection getRecommendBedColl(Connection lConnection,HttpServletRequest request) {
    return getCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_BED,SIConfig.RECOMMEND_BED_ITEMS);
  }
  public static Collection getRecommendCosmeColl(Connection lConnection,HttpServletRequest request) {
    return getCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_COSME,SIConfig.RECOMMEND_COSME_ITEMS);
  }
  public static Collection getRecommendCosmeColl2(Connection lConnection,HttpServletRequest request) {
    return getCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_COSME2,SIConfig.RECOMMEND_COSME_ITEMS);
  }
  public static Collection getRecommendNailColl(Connection lConnection,HttpServletRequest request) {
    return getCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_NAIL,SIConfig.RECOMMEND_NAIL_ITEMS);
  }
  public static Collection getRecommendNailColl2(Connection lConnection,HttpServletRequest request) {
    return getCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_NAIL2,SIConfig.RECOMMEND_NAIL_ITEMS);
  }
  public static Collection getRecommendBookColl(Connection lConnection,HttpServletRequest request) {
    return getCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_BOOK,SIConfig.RECOMMEND_BOOK_ITEMS);
  }
  public static Collection getRecommendMagazineColl(Connection lConnection,HttpServletRequest request) {
    return getCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_BOOK2,SIConfig.RECOMMEND_BOOK_ITEMS);
  }
  public static Collection getRecommendDVDColl(Connection lConnection,HttpServletRequest request) {
    return getCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_BOOK3,SIConfig.RECOMMEND_BOOK_ITEMS);
  }
  public static Collection getRecommendCDColl(Connection lConnection,HttpServletRequest request) {
    return getCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_BOOK4,SIConfig.RECOMMEND_BOOK_ITEMS);
  }
  public static Collection getRecommendEyeLashColl(Connection lConnection,HttpServletRequest request) {
    return getCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_EYELASH,SIConfig.RECOMMEND_EYELASH_ITEMS);
  }
  public static Collection getRecommendEyeLashColl2(Connection lConnection,HttpServletRequest request) {
    return getCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_EYELASH2,SIConfig.RECOMMEND_EYELASH_ITEMS);
  }
  
  public static Collection getCollection(Connection lConnection,HttpServletRequest request,int index,int limit) {
    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 ? "," : ")");
      }
    }
    
    String ctgryGroup = "top";
    for (int i=0;i<SIConfig.reccomendGroup.length;i++) {
      if (SIConfig.reccomendGroup[i][0].equals(String.valueOf(index))){
        ctgryGroup=SIConfig.reccomendGroup[i][2];
        break;
      }
    }
    
    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.memberDiscountFlg,unit.unitPriceWithoutTax ");
    //  EDBTG001-02 2013/03/01 nagayoshi add start
    lSql.append(",unit.cmdtyCompositionFlg ");
    // EDBTG001-02 2013/03/01 nagayoshi add end
    lSql.append(",CASE WHEN unit.bgPriceDispFlg='2' THEN 2 ");
    if(!lUserInfo.isLogin(request)){
      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,COALESCE((SELECT taxrate FROM taxvw),0) AS TaxRate ");
    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,recommendItemTbl rec,feerulemtbl feerule,bgdisptbl bg ");
    lSql.append("WHERE unit.makerCode=maker.makerCode AND unit.frontDispFlg='1' ");
    lSql.append("AND unit.individualCode=rec.individualCode AND unit.deliverytypecode = feerule.deliverytypecode ");
    lSql.append("AND rec.ctgryGroup=").append(SIDBUtil.SQL2Str(ctgryGroup," "));
    lSql.append("AND NOT(unit.usedNewFlg!='1' AND unit.usedNewFlg!='7' AND unit.amountflg='1' AND (unit.amount-unit.bgpamount)='0' ");
    lSql.append(" AND (unit.rsrvenableflg='0' OR (unit.rsrvamount IS NOT NULL AND unit.rsrvamount='0')) ");
    lSql.append(" AND (unit.soldoutdate IS NULL OR unit.soldoutdate + "+SIConfig.DEFAULT_SOLDOUT+" <= CURRENT_DATE)) ");
    lSql.append("AND unit.individualcode=bg.individualcode ");
    lSql.append("ORDER BY rec.dispOrder,unit.InitDateTime ");
    // EDBTG006-00 2014/12/12 agl-kamata mod start
    if (limit != 0) {
        lSql.append("LIMIT ").append(limit);
    }
    // EDBTG006-00 2014/12/12 agl-kamata mod end
    
    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.setUnitPrice(lResultSet.getString("unitPrice"));
        lCmdty.setUnitPriceWithoutTax(lResultSet.getString("unitPriceWithoutTax"));
        lCmdty.setCalcUnitPrice(lResultSet.getString("unitPrice"));
        lCmdty.setTaxRate(lResultSet.getString("TaxRate"));
        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());
        // EDBTG001-02 2013/03/01 nagayoshi add start
        lCmdty.setCmdtyCompositionFlg(lResultSet.getString("cmdtyCompositionFlg"));
        // EDBTG001-02 2013/03/01 nagayoshi add end
        res.add(lCmdty);
      }
      
    } catch (Exception e) {
      e.printStackTrace();
      res = new ArrayList();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return res;
  }
  
  public static String getLastUpdateTop(Connection lConnection) {
    return getLastUpdate(lConnection,SIConfig.CTGRYGROUP_INX_ALL);
  }
  public static String getLastUpdateHair(Connection lConnection) {
    return getLastUpdate(lConnection,SIConfig.CTGRYGROUP_INX_HAIR);
  }
  public static String getLastUpdateHairCosme(Connection lConnection) {
    return getLastUpdate(lConnection,SIConfig.CTGRYGROUP_INX_HAIRCOSME);
  }
  public static String getLastUpdateHairCosme2(Connection lConnection) {
    return getLastUpdate(lConnection,SIConfig.CTGRYGROUP_INX_HAIRCOSME2);
  }
  public static String getLastUpdateEsthe(Connection lConnection) {
    return getLastUpdate(lConnection,SIConfig.CTGRYGROUP_INX_ESTHE);
  }
  public static String getLastUpdateBed(Connection lConnection) {
    return getLastUpdate(lConnection,SIConfig.CTGRYGROUP_INX_BED);
  }
  public static String getLastUpdateCosme(Connection lConnection) {
    return getLastUpdate(lConnection,SIConfig.CTGRYGROUP_INX_COSME);
  }
  public static String getLastUpdateCosme2(Connection lConnection) {
    return getLastUpdate(lConnection,SIConfig.CTGRYGROUP_INX_COSME2);
  }
  public static String getLastUpdateNail(Connection lConnection) {
    return getLastUpdate(lConnection,SIConfig.CTGRYGROUP_INX_NAIL);
  }
  public static String getLastUpdateNail2(Connection lConnection) {
    return getLastUpdate(lConnection,SIConfig.CTGRYGROUP_INX_NAIL2);
  }
  public static String getLastUpdateBook(Connection lConnection) {
    return getLastUpdate(lConnection,SIConfig.CTGRYGROUP_INX_BOOK);
  }
  public static String getLastUpdateMagazine(Connection lConnection) {
    return getLastUpdate(lConnection,SIConfig.CTGRYGROUP_INX_BOOK2);
  }
  public static String getLastUpdateDVD(Connection lConnection) {
    return getLastUpdate(lConnection,SIConfig.CTGRYGROUP_INX_BOOK3);
  }
  public static String getLastUpdateCD(Connection lConnection) {
    return getLastUpdate(lConnection,SIConfig.CTGRYGROUP_INX_BOOK4);
  }
  public static String getLastUpdateEyeLash(Connection lConnection) {
    return getLastUpdate(lConnection,SIConfig.CTGRYGROUP_INX_EYELASH);
  }
  public static String getLastUpdateEyeLash2(Connection lConnection) {
    return getLastUpdate(lConnection,SIConfig.CTGRYGROUP_INX_EYELASH2);
  }
  
  public static String getLastUpdate(Connection lConnection,int index) {
    String res = "";
    String ctgryGroup = "top";
    if (index == SIConfig.CTGRYGROUP_INX_HAIR) ctgryGroup="hair";
    else if (index == SIConfig.CTGRYGROUP_INX_HAIRCOSME) ctgryGroup="haircosme";
    else if (index == SIConfig.CTGRYGROUP_INX_HAIRCOSME2) ctgryGroup="haircosme2";
    else if (index == SIConfig.CTGRYGROUP_INX_ESTHE) ctgryGroup="esthe";
    else if (index == SIConfig.CTGRYGROUP_INX_BED) ctgryGroup="bed";
    else if (index == SIConfig.CTGRYGROUP_INX_COSME) ctgryGroup="cosme";
    else if (index == SIConfig.CTGRYGROUP_INX_COSME2) ctgryGroup="cosme2";
    else if (index == SIConfig.CTGRYGROUP_INX_NAIL) ctgryGroup="nail";
    else if (index == SIConfig.CTGRYGROUP_INX_NAIL2) ctgryGroup="nail2";
    else if (index == SIConfig.CTGRYGROUP_INX_BOOK) ctgryGroup="book";
    else if (index == SIConfig.CTGRYGROUP_INX_BOOK2) ctgryGroup="book2";
    else if (index == SIConfig.CTGRYGROUP_INX_BOOK3) ctgryGroup="book3";
    else if (index == SIConfig.CTGRYGROUP_INX_BOOK4) ctgryGroup="book4";
    else if (index == SIConfig.CTGRYGROUP_INX_EYELASH) ctgryGroup="eyelash";
    else if (index == SIConfig.CTGRYGROUP_INX_EYELASH2) ctgryGroup="eyelash2";
    
    StringBuffer lSql = new StringBuffer();
    lSql.append("SELECT MAX(initDateTime) FROM recommendItemTbl ");
    lSql.append("WHERE ctgryGroup=").append(SIDBUtil.SQL2Str(ctgryGroup));
    
    try {
      SIDateTime lDate = new SIDateTime(SIDBUtil.getFirstData(lConnection, lSql.toString()),SIConfig.SIDATE_FORMAT0);
      res = lDate.getFullDate() + " 更新";
    } catch (Exception e) {
      e.printStackTrace();
    }
    
    return res;
  }
  
  public static Collection getNewItemCollectionHair(Connection lConnection,HttpServletRequest request) {
    return getItemCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_HAIR,1,8);
  }
  
  public static Collection getNewItemCollectionEsthe(Connection lConnection,HttpServletRequest request) {
    return getItemCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_ESTHE,1,8);
  }
  
  public static Collection getNewItemCollectionBed(Connection lConnection,HttpServletRequest request) {
    return getItemCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_BED,1,8);
  }
  
  public static Collection getNewItemCollectionCosme(Connection lConnection,HttpServletRequest request) {
    return getItemCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_COSME,1,12);
  }
  
  public static Collection getNewItemCollectionNail(Connection lConnection,HttpServletRequest request) {
    return getItemCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_NAIL,1,12);
  }
  
  public static Collection getNewItemCollectionHairCosme(Connection lConnection,HttpServletRequest request) {
    return getItemCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_HAIRCOSME,1,12);
  }
  
  public static Collection getNewItemCollectionBook(Connection lConnection,HttpServletRequest request) {
    return getItemCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_BOOK,1,12);
  }
  
  public static Collection getNewItemCollectionEyeLash(Connection lConnection,HttpServletRequest request) {
    return getItemCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_EYELASH,1,12);
  }
  
  public static Collection getUsedItemCollectionHair(Connection lConnection,HttpServletRequest request) {
    return getItemCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_HAIR,2,8);
  }
  
  public static Collection getUsedItemCollectionEsthe(Connection lConnection,HttpServletRequest request) {
    return getItemCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_ESTHE,2,8);
  }
  
  public static Collection getUsedItemCollectionBed(Connection lConnection,HttpServletRequest request) {
    return getItemCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_BED,2,8);
  }
  
  public static Collection getUsedItemCollectionCosme(Connection lConnection,HttpServletRequest request) {
    return getItemCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_COSME,2,12);
  }
  
  public static Collection getUsedItemCollectionNail(Connection lConnection,HttpServletRequest request) {
    return getItemCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_NAIL,2,12);
  }
  
  public static Collection getUsedItemCollectionHairCosme(Connection lConnection,HttpServletRequest request) {
    return getItemCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_HAIRCOSME,2,12);
  }
  
  public static Collection getUsedItemCollectionBook(Connection lConnection,HttpServletRequest request) {
    return getItemCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_BOOK,2,12);
  }
  
  public static Collection getUsedItemCollectionEyeLash(Connection lConnection,HttpServletRequest request) {
    return getItemCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_HAIRCOSME,2,12);
  }
  
  public static Collection getOutletItemCollection(Connection lConnection,HttpServletRequest request) {
    return getItemCollection(lConnection,request,SIConfig.CTGRYGROUP_INX_ALL,3,8);
  }
  
  public static Collection getItemCollection(Connection lConnection,HttpServletRequest request,int index,int mode,int limit) {
    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 ? "," : ")");
      }
    }
    
    String ctgryGroup = "";
    if (index == SIConfig.CTGRYGROUP_INX_HAIR) ctgryGroup="hair";
    else if (index == SIConfig.CTGRYGROUP_INX_HAIRCOSME) ctgryGroup="haircosme";
    else if (index == SIConfig.CTGRYGROUP_INX_ESTHE) ctgryGroup="esthe";
    else if (index == SIConfig.CTGRYGROUP_INX_BED) ctgryGroup="bed";
    else if (index == SIConfig.CTGRYGROUP_INX_COSME) ctgryGroup="cosme";
    else if (index == SIConfig.CTGRYGROUP_INX_NAIL) ctgryGroup="nail";
    else if (index == SIConfig.CTGRYGROUP_INX_BOOK) ctgryGroup="book";
    else if (index == SIConfig.CTGRYGROUP_INX_EYELASH) ctgryGroup="eyelash";
    
    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.initDateTime,unit.memberDiscountFlg,unit.unitPriceWithoutTax ");
    // EDBTG001-02 2013/03/01 nagayoshi add start
    lSql.append(",unit.cmdtyCompositionFlg ");
    // EDBTG001-02 2013/03/01 nagayoshi add end
    lSql.append(",CASE WHEN unit.bgPriceDispFlg='2' THEN 2 ");
    if(!lUserInfo.isLogin(request)){
      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,COALESCE((SELECT taxrate FROM taxvw),0) AS TaxRate ");
    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,bgdisptbl bg ");
    lSql.append(",(SELECT individualcode,initdatetime FROM storehistorytbl WHERE storenumber=1) store ");
    lSql.append("WHERE unit.makerCode=maker.makerCode AND unit.frontDispFlg=1 AND unit.inquiryflg!='1' AND unit.deliverytypecode = feerule.deliverytypecode ");
    lSql.append("AND unit.individualcode=store.individualcode AND unit.individualcode=bg.individualcode ");
    if (mode==1) {
      lSql.append("AND unit.usedNewFlg IN ('1','6','7') ");
      lSql.append("AND NOT(unit.amountflg='1' AND (unit.amount-unit.bgpamount)='0') ");
    } else if (mode==2) {
      lSql.append("AND unit.usedNewFlg IN ('0','3') ");
      lSql.append("AND NOT(unit.amountflg='1' AND (unit.amount-unit.bgpamount)='0') ");
    } else if (mode==3) {
      lSql.append("AND unit.usedNewFlg IN ('2','4') ");
      lSql.append("AND NOT(unit.amountflg='1' AND (unit.amount-unit.bgpamount)='0') ");
    } else {
      lSql.append("AND NOT(unit.usedNewFlg!='1' AND unit.usedNewFlg!='7' AND unit.amountflg='1' AND (unit.amount-unit.bgpamount)='0' AND (unit.rsrvenableflg='0' OR (unit.rsrvamount IS NOT NULL AND unit.rsrvamount='0')) AND (unit.soldoutdate IS NULL OR unit.soldoutdate + "+SIConfig.DEFAULT_SOLDOUT+" <= CURRENT_DATE)) ");
    }
    if (SIUtil.isNotNull(ctgryGroup)) {
      //カテゴリ関連付け、各館共通カテゴリを無視して本籍の商品のみ表示する
      lSql.append("AND substr(unit.individualcode,0,3) IN (SELECT a.ctgrycode FROM ctgryvw a,ctgrygrouptbl b WHERE (a.branch LIKE '%~'||b.ctgrycode||'~%' OR a.branch LIKE '%~'||b.ctgrycode) ");
      lSql.append("AND b.ctgrygroup=").append(SIDBUtil.SQL2Str(ctgryGroup,") "));
      if ("hair".equals(ctgryGroup)) {
        lSql.append("AND substr(unit.individualcode,0,2) IN ('S','C','K','B','P','R','M','X','Z') ");
      } else if ("haircosme".equals(ctgryGroup)) {
        lSql.append("AND substr(unit.individualcode,0,2) IN ('V','H') ");
      } else if ("esthe".equals(ctgryGroup)) {
        lSql.append("AND substr(unit.individualcode,0,2) IN ('E','T') ");
      } else if ("bed".equals(ctgryGroup)) {
        lSql.append("AND substr(unit.individualcode,0,2) IN ('D') ");
      } else if ("cosme".equals(ctgryGroup)) {
        lSql.append("AND substr(unit.individualcode,0,2) IN ('L') ");
      } else if ("nail".equals(ctgryGroup)) {
        lSql.append("AND substr(unit.individualcode,0,2) IN ('N','A') ");
      } else if ("book".equals(ctgryGroup)) {
        lSql.append("AND substr(unit.individualcode,0,2) IN ('F') ");
      } else if ("eyelash".equals(ctgryGroup)) {
        lSql.append("AND substr(unit.individualcode,0,2) IN ('W') ");
      }
    }
    if (mode==1 || mode==2) {//新品、アウトレットでは登録順に表示
      lSql.append("ORDER BY store.InitDateTime DESC,unit.InitDateTime DESC,unit.CmdtyCode::numeric DESC,unit.IndividualCode DESC ");
    } else {
      lSql.append("ORDER BY unit.InitDateTime DESC,unit.CmdtyCode::numeric DESC,unit.IndividualCode DESC ");
    }
    lSql.append("LIMIT ").append(limit);
    
    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.setUnitPrice(lResultSet.getString("unitPrice"));
        lCmdty.setUnitPriceWithoutTax(lResultSet.getString("unitPriceWithoutTax"));
        lCmdty.setCalcUnitPrice(lResultSet.getString("unitPrice"));
        lCmdty.setTaxRate(lResultSet.getString("TaxRate"));
        lCmdty.setNewFixedPrice(lResultSet.getString("newFixedPrice"));
        lCmdty.setBGPriceDispFlg(lResultSet.getString("bgPriceDispFlg"));
        lCmdty.setContractFlg("1".equals(lResultSet.getString("contractFlg")));
        lCmdty.setInitDateTime(SIDBUtil.getDate(lResultSet.getTimestamp("initDateTime")));
        lCmdty.setMemberDiscountFlg(lResultSet.getString("memberDiscountFlg"));
        lCmdty.setMemberDiscountRate(lUserInfo.getDiscountRate());
        // EDBTG001-02 2013/03/01 nagayoshi add start
        lCmdty.setCmdtyCompositionFlg(lResultSet.getString("cmdtyCompositionFlg"));
        // EDBTG001-02 2013/03/01 nagayoshi add end
        res.add(lCmdty);
      }
      
    } catch (Exception e) {
      e.printStackTrace();
      res = new ArrayList();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return res;
  }
  
  public static int RANKING_ALL_INX = 0;
  public static int RANKING_CHAIR_INX = RANKING_ALL_INX + 1;
  public static int RANKING_HAIRTOOL_INX = RANKING_ALL_INX + 2;
  public static int RANKING_HAIRITEM_INX = RANKING_ALL_INX + 3;
  public static int RANKING_HAIRCOSME_INX = RANKING_ALL_INX + 4;
  public static int RANKING_HAIRCARE_INX = RANKING_ALL_INX + 5;
  public static int RANKING_HAIRCOLOR_INX = RANKING_ALL_INX + 6;
  public static int RANKING_PERMANENT_INX = RANKING_ALL_INX + 7;
  public static int RANKING_OUTBATH_INX = RANKING_ALL_INX + 8;
  public static int RANKING_STYLING_INX = RANKING_ALL_INX + 9;
  public static int RANKING_EXTENTION_INX = RANKING_ALL_INX + 10;
  public static int RANKING_ESTHETOOL_INX = RANKING_ALL_INX + 11;
  public static int RANKING_ESTHEITEM_INX = RANKING_ALL_INX + 12;
  public static int RANKING_TOWEL_INX = RANKING_ALL_INX + 13;
  public static int RANKING_WAGON_INX = RANKING_ALL_INX + 14;
  public static int RANKING_BED_INX = RANKING_ALL_INX + 15;
  public static int RANKING_EBED_INX = RANKING_ALL_INX + 16;
  public static int RANKING_MBED_INX = RANKING_ALL_INX + 17;
  public static int RANKING_PBED_INX = RANKING_ALL_INX + 18;
  public static int RANKING_COSME_INX = RANKING_ALL_INX + 19;
  public static int RANKING_OIL_INX = RANKING_ALL_INX + 20;
  public static int RANKING_CLEANSING_INX = RANKING_ALL_INX + 21;
  public static int RANKING_LOTION_INX = RANKING_ALL_INX + 22;
  public static int RANKING_ESSENCE_INX = RANKING_ALL_INX + 23;
  public static int RANKING_CREAM_INX = RANKING_ALL_INX + 24;
  public static int RANKING_PACK_INX = RANKING_ALL_INX + 25;
  public static int RANKING_HARBTEA_INX = RANKING_ALL_INX + 26;
  public static int RANKING_NAILITEM_INX = RANKING_ALL_INX + 27;
  public static int RANKING_NAILTOOL_INX = RANKING_ALL_INX + 28;
  public static int RANKING_EYELASH_INX = RANKING_ALL_INX + 29;
  public static int RANKING_EYELASHPERMANENT_INX = RANKING_ALL_INX + 30;
  public static int RANKING_EYELASHESSENCE_INX = RANKING_ALL_INX + 31;
  public static int RANKING_MASCARA_INX = RANKING_ALL_INX + 32;
  public static int RANKING_BOOK_INX = RANKING_ALL_INX + 33;
  public static int RANKING_MAGAZINE_INX = RANKING_ALL_INX + 34;
  public static int RANKING_DVD_INX = RANKING_ALL_INX + 35;
  public static int RANKING_CD_INX = RANKING_ALL_INX + 36;
  
  public static String getRankingTitle(int inx){
    String[] rankingTitle = {
        "ランキング一覧",
        "スタイリングチェア/セットイス",
        "その他理美容器具",
        "理美容用品",
        "ヘアケア製品総合",
        "シャンプー/トリートメント",
        "ヘアカラー",
        "パーマ剤",
        "アウトバストリートメント",
        "スタイリング剤",
        "ヘアエクステンション",
        "エステ機器",
        "エステ用品",
        "タオル/シーツ/ガウン類",
        "ワゴン/タオルウォーマー/什器類",
        "エステベッド/マッサージベッド総合",
        "エステベッド",
        "マッサージベッド",
        "ポータブルベッド",
        "エステ化粧品総合",
        "オイル関連",
        "クレンジング",
        "化粧水",
        "美容液",
        "クリーム",
        "パック",
        "ハーブティー/ドリンク/サプリ",
        "ネイル用品",
        "ネイル機器",
        "まつげ商材",
        "まつげカール液/グルー/ロット",
        "まつげ美容液",
        "マスカラ/アイライナー",
        "本/書籍",
        "雑誌",
        "DVD",
        "CD",
      };
    if(inx > rankingTitle.length) inx = 0;
    
    return rankingTitle[inx];
  }
  
  public static final int rankingListCount = 36;
  
  public static Collection getChairColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_CHAIR_INX,limit);
  }
  
  public static Collection getHairToolColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_HAIRTOOL_INX,limit);
  }
  
  public static Collection getHairItemColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_HAIRITEM_INX,limit);
  }
  
  public static Collection getHairCosmeColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_HAIRCOSME_INX,limit);
  }
  
  public static Collection getHairCareColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_HAIRCARE_INX,limit);
  }
  
  public static Collection getHairColorColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_HAIRCOLOR_INX,limit);
  }
  
  public static Collection getPermanentColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_PERMANENT_INX,limit);
  }
  
  public static Collection getOutBathColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_OUTBATH_INX,limit);
  }
  
  public static Collection getStylingColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_STYLING_INX,limit);
  }
  
  public static Collection getExtentionColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_EXTENTION_INX,limit);
  }
  
  public static Collection getEstheToolColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_ESTHETOOL_INX,limit);
  }
  
  public static Collection getEstheItemColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_ESTHEITEM_INX,limit);
  }
  
  public static Collection getTowelColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_TOWEL_INX,limit);
  }
  
  public static Collection getWagonColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_WAGON_INX,limit);
  }
  
  public static Collection getBedColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_BED_INX,limit);
  }
  
  public static Collection getEBedColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_EBED_INX,limit);
  }
  
  public static Collection getMBedColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_MBED_INX,limit);
  }
  
  public static Collection getPBedColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_PBED_INX,limit);
  }
  
  public static Collection getCosmeticColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_COSME_INX,limit);
  }
  
  public static Collection getOilColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_OIL_INX,limit);
  }
  
  public static Collection getCleansingColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_CLEANSING_INX,limit);
  }
  
  public static Collection getLotionColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_LOTION_INX,limit);
  }
  
  public static Collection getEssenceColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_ESSENCE_INX,limit);
  }
  
  public static Collection getCreamColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_CREAM_INX,limit);
  }
  
  public static Collection getPackColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_PACK_INX,limit);
  }
  
  public static Collection getHarbTeaColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_HARBTEA_INX,limit);
  }
  
  public static Collection getNailItemColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_NAILITEM_INX,limit);
  }
  
  public static Collection getNailToolColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_NAILTOOL_INX,limit);
  }
  
  public static Collection getEyelashColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_EYELASH_INX,limit);
  }
  
  public static Collection getEyelashPermanentColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_EYELASHPERMANENT_INX,limit);
  }
  
  public static Collection getEyelashEssenceColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_EYELASHESSENCE_INX,limit);
  }
  
  public static Collection getMascaraColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_MASCARA_INX,limit);
  }
  
  public static Collection getBookColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_BOOK_INX,limit);
  }
  
  public static Collection getMagazineColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_MAGAZINE_INX,limit);
  }
  
  public static Collection getDvdColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_DVD_INX,limit);
  }
  
  public static Collection getCdColl(Connection lConnection,HttpServletRequest request,int limit) {
    return getRankingCollection(lConnection,request,RANKING_CD_INX,limit);
  }

  public static Collection getRankingCollection(Connection lConnection,HttpServletRequest request,int index,int limit) {
    SIUserInfo lUserInfo = new SIUserInfo();
    //if (limit < 4) limit = 4;
    
    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 ? "," : ")");
      }
    }
    
    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.unitPriceWithoutTax ");
    // EDBTG001-02 2013/03/01 nagayoshi add start
    lSql.append(",unit.cmdtyCompositionFlg ");
    // EDBTG001-02 2013/03/01 nagayoshi add end
    lSql.append(",CASE WHEN unit.bgPriceDispFlg='2' THEN 2 ");
    if(!lUserInfo.isLogin(request)){
      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,COALESCE((SELECT taxrate FROM taxvw),0) AS TaxRate ");
    lSql.append(",detail.amount ");
    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,rankingBaseTbl detail,feerulemtbl feerule ");
    lSql.append("WHERE unit.makerCode=maker.makerCode AND unit.frontDispFlg=1 AND unit.individualcode=detail.individualcode AND usednewFlg IN('1','6','7') AND unit.deliverytypecode = feerule.deliverytypecode ");
    if(index==RANKING_CHAIR_INX){
      lSql.append("AND substr(unit.individualcode,0,3) = 'SE' ");
    }else if(index==RANKING_HAIRTOOL_INX){
      lSql.append("AND substr(unit.individualcode,0,2) IN ('C','K','B','P','R','M','Z') ");
      lSql.append("AND unit.deliverytypecode='0' ");
    }else if(index==RANKING_HAIRITEM_INX){
      lSql.append("AND substr(unit.individualcode,0,2) IN ('C','K','B','P','R','M','Z') ");
      lSql.append("AND unit.deliverytypecode='1' ");
    }else if(index==RANKING_HAIRCOSME_INX){
      lSql.append("AND substr(unit.individualcode,0,2) = 'H' ");
    }else if(index==RANKING_EXTENTION_INX){
      lSql.append("AND substr(unit.individualcode,0,2) = 'V' ");
    }else if(index==RANKING_HAIRCARE_INX){
      lSql.append("AND substr(unit.individualcode,0,3) IN ('HS','HT') ");
    }else if(index==RANKING_HAIRCOLOR_INX){
      lSql.append("AND substr(unit.individualcode,0,3) IN ('HC','HB','HD','HM') ");
    }else if(index==RANKING_PERMANENT_INX){
      lSql.append("AND substr(unit.individualcode,0,3) IN ('HE','HG','HH','HI') ");
    }else if(index==RANKING_OUTBATH_INX){
      lSql.append("AND substr(unit.individualcode,0,3) = 'HO' ");
    }else if(index==RANKING_STYLING_INX){
      lSql.append("AND substr(unit.individualcode,0,3) IN ('HK','HF','HJ','HW','HL') ");
    }else if(index==RANKING_ESTHETOOL_INX){
      lSql.append("AND substr(unit.individualcode,0,2) = 'E' ");
    }else if(index==RANKING_ESTHEITEM_INX){
      lSql.append("AND substr(unit.individualcode,0,2) = 'T' AND substr(unit.individualcode,0,3) NOT IN ('TB','TG') ");
    }else if(index==RANKING_TOWEL_INX){
      lSql.append("AND substr(unit.individualcode,0,3) IN ('TB','TG') ");
    }else if(index==RANKING_WAGON_INX){
      lSql.append("AND substr(unit.individualcode,0,2) = 'X' ");
    }else if(index==RANKING_BED_INX){
      lSql.append("AND substr(unit.individualcode,0,2) = 'D' ");
    }else if(index==RANKING_EBED_INX){
      lSql.append("AND substr(unit.individualcode,0,3) = 'DE' ");
    }else if(index==RANKING_MBED_INX){
      lSql.append("AND substr(unit.individualcode,0,3) = 'DM' ");
    }else if(index==RANKING_PBED_INX){
      lSql.append("AND substr(unit.individualcode,0,3) = 'DP' ");
    }else if(index==RANKING_COSME_INX){
      lSql.append("AND substr(unit.individualcode,0,2) = 'L' AND substr(unit.individualcode,0,3) NOT IN ('LE','LO','LF') ");
    }else if(index==RANKING_OIL_INX){
      lSql.append("AND substr(unit.individualcode,0,3) IN ('LE','LO') ");
    }else if(index==RANKING_CLEANSING_INX){
      lSql.append("AND substr(unit.individualcode,0,3) = 'LC' ");
    }else if(index==RANKING_LOTION_INX){
      lSql.append("AND substr(unit.individualcode,0,3) = 'LW' ");
    }else if(index==RANKING_ESSENCE_INX){
      lSql.append("AND substr(unit.individualcode,0,3) = 'LB' ");
    }else if(index==RANKING_CREAM_INX){
      lSql.append("AND substr(unit.individualcode,0,3) = 'LN' ");
    }else if(index==RANKING_PACK_INX){
      lSql.append("AND substr(unit.individualcode,0,3) = 'LP' ");
    }else if(index==RANKING_HARBTEA_INX){
      lSql.append("AND substr(unit.individualcode,0,3) = 'LF' ");
    }else if(index==RANKING_NAILITEM_INX){
      lSql.append("AND substr(unit.individualcode,0,2) = 'N' ");
    }else if(index==RANKING_NAILTOOL_INX){
      lSql.append("AND substr(unit.individualcode,0,2) = 'A' ");
    }else if(index==RANKING_EYELASH_INX){
      lSql.append("AND substr(unit.individualcode,0,2) = 'W' ");
    }else if(index==RANKING_EYELASHPERMANENT_INX){
      lSql.append("AND substr(unit.individualcode,0,3) = 'WR' ");
    }else if(index==RANKING_EYELASHESSENCE_INX){
      lSql.append("AND substr(unit.individualcode,0,3) = 'WL' ");
    }else if(index==RANKING_MASCARA_INX){
      lSql.append("AND substr(unit.individualcode,0,3) = 'WM' ");
    }else if(index==RANKING_BOOK_INX){
      lSql.append("AND substr(unit.individualcode,0,3) = 'FP' ");
    }else if(index==RANKING_MAGAZINE_INX){
      lSql.append("AND substr(unit.individualcode,0,3) = 'FQ' ");
    }else if(index==RANKING_DVD_INX){
      lSql.append("AND substr(unit.individualcode,0,3) = 'FR' ");
    }else if(index==RANKING_CD_INX){
      lSql.append("AND substr(unit.individualcode,0,3) = 'FS' ");
    }
    lSql.append(" AND detail.bgflg='1' ");
    //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-unit.bgpamount)='0' AND (unit.soldoutdate IS NULL OR unit.soldoutdate + "+SIConfig.DEFAULT_SOLDOUT+" <= CURRENT_DATE)) ");
    lSql.append(" AND CASE WHEN unit.amountflg=0 THEN true ");
    lSql.append(" WHEN unit.amount-unit.bgpamount>0 THEN true ");
    //lSql.append(" WHEN unit.rsrvenableflg='1' AND coalesce(unit.rsrvamount,1)>0 THEN true ");
    lSql.append(" ELSE false END ");
    lSql.append("ORDER BY detail.price DESC ");
    lSql.append("LIMIT ").append(limit);
    
    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.setUnitPrice(lResultSet.getString("unitPrice"));
        lCmdty.setUnitPriceWithoutTax(lResultSet.getString("unitPriceWithoutTax"));
        lCmdty.setCalcUnitPrice(lResultSet.getString("unitPrice"));
        lCmdty.setTaxRate(lResultSet.getString("TaxRate"));
        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))||"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);
        }
        // EDBTG001-02 2013/03/01 nagayoshi add start
        lCmdty.setCmdtyCompositionFlg(lResultSet.getString("cmdtyCompositionFlg"));
        // EDBTG001-02 2013/03/01 nagayoshi add end
        res.add(lCmdty);
      }
    } catch (Exception e) {
      e.printStackTrace();
      res = new ArrayList();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return res;
  }
  
  public static Collection getRankingCollection(Connection lConnection,HttpServletRequest request,String ctgryCode,int limit) {
    String[] ctgrys = {ctgryCode};
    return getRankingCollection(lConnection,request,ctgrys,limit);
  }
  
  public static Collection getRankingCollection(Connection lConnection,HttpServletRequest request,String[] ctgryCode,int limit) {
    SIUserInfo lUserInfo = new SIUserInfo();
    
    //if (limit < 4) limit = 4;
    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 ? "," : ")");
      }
    }
    
    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.unitPriceWithoutTax ");
    // EDBTG001-02 2013/03/01 nagayoshi add start
    lSql.append(",unit.cmdtyCompositionFlg ");
    // EDBTG001-02 2013/03/01 nagayoshi add end
    lSql.append(",CASE WHEN unit.bgPriceDispFlg='2' THEN 2 ");
    if(!lUserInfo.isLogin(request)){
      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,COALESCE((SELECT taxrate FROM taxvw),0) AS TaxRate ");
    lSql.append(",detail.amount ");
    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,rankingBaseTbl detail,feerulemtbl feerule ");
    lSql.append("WHERE unit.makerCode=maker.makerCode AND unit.frontDispFlg=1 AND unit.individualcode=detail.individualcode AND usednewFlg IN('1','6','7') AND unit.deliverytypecode = feerule.deliverytypecode ");
    for (int i=0;i<ctgryCode.length;i++) {
      if (i==0) lSql.append("AND substr(unit.individualcode,0,3) IN (");
      else lSql.append(",");
      lSql.append(SIDBUtil.SQL2Str(ctgryCode[i]));
    }
    lSql.append(") ");
    lSql.append("AND detail.bgflg='1' ");
    //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-unit.bgpamount)='0' AND (unit.soldoutdate IS NULL OR unit.soldoutdate + "+SIConfig.DEFAULT_SOLDOUT+" <= CURRENT_DATE)) ");
    lSql.append(" AND CASE WHEN unit.amountflg=0 THEN true ");
    lSql.append(" WHEN unit.amount-unit.bgpamount>0 THEN true ");
    //lSql.append(" WHEN unit.rsrvenableflg='1' AND coalesce(unit.rsrvamount,1)>0 THEN true ");
    lSql.append(" ELSE false END ");
    lSql.append("ORDER BY detail.price DESC ");
    lSql.append("LIMIT ").append(limit);
    
    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.setUnitPrice(lResultSet.getString("unitPrice"));
        lCmdty.setUnitPriceWithoutTax(lResultSet.getString("unitPriceWithoutTax"));
        lCmdty.setCalcUnitPrice(lResultSet.getString("unitPrice"));
        lCmdty.setTaxRate(lResultSet.getString("TaxRate"));
        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))||"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);
        }
        // EDBTG001-02 2013/03/01 nagayoshi add start
        lCmdty.setCmdtyCompositionFlg(lResultSet.getString("cmdtyCompositionFlg"));
        // EDBTG001-02 2013/03/01 nagayoshi add end
        res.add(lCmdty);
      }
    } catch (Exception e) {
      e.printStackTrace();
      res = new ArrayList();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return res;
  }
  
  // 指定したカテゴリに所属する全商品のランキングを取得する。
  public static Collection getCategoryRankingCollection(Connection lConnection,HttpServletRequest request,String ctgryCode, int limit) {
    return getCategoryRankingCollection(lConnection,request,ctgryCode,"", limit);
  }
  
  public static Collection getCategoryRankingCollection(Connection lConnection,HttpServletRequest request,String ctgryCode,String makerCode, int limit) {
    
    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 ? "," : ")");
      }
    }
    
    StringBuffer lSql = new StringBuffer();
    
    // 指定されたカテゴリコードに対する全商品のランキング上位５件を取得する
    lSql.append("SELECT unit.cmdtyCode,unit.IndividualCode,unit.unitPrice,unit.newFixedPrice,unit.description,unit.memberDiscountFlg,unit.unitPriceWithoutTax ");
    lSql.append(",unit.cmdtyCompositionFlg ");
    lSql.append(",CASE WHEN unit.bgPriceDispFlg='2' THEN 2 ");
    if (!lUserInfo.isLogin(request)) {
      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,COALESCE((SELECT taxrate FROM taxvw),0) AS TaxRate ");
    lSql.append(",detail.amount ");
    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(",(SELECT DISTINCT cc.CmdtyCode ");
    lSql.append("FROM CmdtyCtgryMTbl cc ,CtgryVW cv ");
    lSql.append("WHERE (cv.branch LIKE '" + ctgryCode + "%' OR cv.branch LIKE '%" + ctgryCode + "%') AND cc.ctgryCode = cv.ctgryCode");
    lSql.append(") ct");
    lSql.append(",makerTbl maker,rankingBaseTbl detail,feerulemtbl feerule ");
    lSql.append("WHERE unit.makerCode=maker.makerCode AND unit.frontDispFlg=1 AND unit.individualcode=detail.individualcode AND usednewFlg IN('1','6','7') AND unit.deliverytypecode = feerule.deliverytypecode ");
    lSql.append("AND unit.cmdtyCode=ct.CmdtyCode ");
    if (SIUtil.isNotNull(makerCode)) lSql.append("AND unit.makerCode=").append(SIDBUtil.SQL2Str(makerCode," "));
    lSql.append("AND detail.bgflg='1' ");
    //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-unit.bgpamount)='0' AND (unit.soldoutdate IS NULL OR unit.soldoutdate + "+SIConfig.DEFAULT_SOLDOUT+" <= CURRENT_DATE)) ");
    lSql.append(" AND CASE WHEN unit.amountflg=0 THEN true ");
    lSql.append(" WHEN unit.amount-unit.bgpamount>0 THEN true ");
    //lSql.append(" WHEN unit.rsrvenableflg='1' AND coalesce(unit.rsrvamount,1)>0 THEN true ");
    lSql.append(" ELSE false END ");
    lSql.append("ORDER BY detail.price DESC ");
    lSql.append("LIMIT " + limit);
    
    Collection res = new ArrayList();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    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.setUnitPrice(lResultSet.getString("unitPrice"));
        lCmdty.setUnitPriceWithoutTax(lResultSet.getString("unitPriceWithoutTax"));
        lCmdty.setCalcUnitPrice(lResultSet.getString("unitPrice"));
        lCmdty.setTaxRate(lResultSet.getString("TaxRate"));
        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))||"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.setCmdtyCompositionFlg(lResultSet.getString("cmdtyCompositionFlg"));
        res.add(lCmdty);
      }
    } catch (Exception e) {
      e.printStackTrace();
      res = new ArrayList();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return res;
  }
  
  public static Collection getCmdtyRankingCollection(Connection lConnection,HttpServletRequest request,String individualCode, int limit) {
    Collection res = new ArrayList();
    if(SIUtil.isNull(individualCode)||individualCode.length()<2) return res;
    
    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 ? "," : ")");
      }
    }
    
    StringBuffer lSql = new StringBuffer();
    
    // 指定された在庫コードと同一カテゴリのランキング上位X件を取得する
    lSql.append("SELECT unit.cmdtyCode,unit.IndividualCode,unit.unitPrice,unit.newFixedPrice,unit.description,unit.memberDiscountFlg,unit.unitPriceWithoutTax ");
    lSql.append(",unit.cmdtyCompositionFlg ");
    lSql.append(",CASE WHEN unit.bgPriceDispFlg='2' THEN 2 ");
    if (!lUserInfo.isLogin(request)) {
      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,COALESCE((SELECT taxrate FROM taxvw),0) AS TaxRate ");
    lSql.append(",detail.amount ");
    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,rankingBaseTbl detail,feerulemtbl feerule ");
    lSql.append("WHERE unit.makerCode=maker.makerCode AND unit.frontDispFlg=1 AND unit.individualcode=detail.individualcode AND usednewFlg IN('1','6','7') AND unit.deliverytypecode = feerule.deliverytypecode ");
    lSql.append("AND unit.individualCode LIKE '").append(SIDBUtil.SQL2Like(individualCode.substring(0,2))).append("%' ");
    lSql.append("AND unit.individualCode <> ").append(SIDBUtil.SQL2Str(individualCode," "));
    lSql.append("AND detail.bgflg='1' ");
    //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-unit.bgpamount)='0' AND (unit.soldoutdate IS NULL OR unit.soldoutdate + "+SIConfig.DEFAULT_SOLDOUT+" <= CURRENT_DATE)) ");
    lSql.append(" AND CASE WHEN unit.amountflg=0 THEN true ");
    lSql.append(" WHEN unit.amount-unit.bgpamount>0 THEN true ");
    //lSql.append(" WHEN unit.rsrvenableflg='1' AND coalesce(unit.rsrvamount,1)>0 THEN true ");
    lSql.append(" ELSE false END ");
    lSql.append("ORDER BY detail.price DESC ");
    lSql.append("LIMIT " + limit);
    
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    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.setUnitPrice(lResultSet.getString("unitPrice"));
        lCmdty.setUnitPriceWithoutTax(lResultSet.getString("unitPriceWithoutTax"));
        lCmdty.setCalcUnitPrice(lResultSet.getString("unitPrice"));
        lCmdty.setTaxRate(lResultSet.getString("TaxRate"));
        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))||"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.setCmdtyCompositionFlg(lResultSet.getString("cmdtyCompositionFlg"));
        res.add(lCmdty);
      }
    } catch (Exception e) {
      e.printStackTrace();
      res = new ArrayList();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return res;
  }
  
  public static String getCmdtyRankingName(Connection lConnection,String individualCode){
    String categoryName = "";
    try{
      categoryName = SIDBUtil.getFirstData(lConnection,"SELECT ctgryname FROM ctgrymtbl WHERE ctgrycode="+SIDBUtil.SQL2Str(individualCode.substring(0,2)));
    }catch(Exception e){}
    if (SIUtil.isNull(categoryName)) categoryName="商品";
    return categoryName;
  }
}