package jp.co.sint.beans.front;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Collection;

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

import jp.co.sint.basic.SINameValue;
import jp.co.sint.basic.SINameValueForSP;
import jp.co.sint.config.SIConfig;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.tools.SIStringUtil;
import jp.co.sint.tools.SIUtil;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Category;

public class UICtgryGroup {
  //ログ用のインスタンスの生成
  private static Category log=Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  public static Collection getHairColl(Connection lConnection) {
    return getCtgryColl(lConnection,SIConfig.CTGRYGROUP_INX_HAIR);
  }
  
  public static Collection getHairCosmeColl(Connection lConnection) {
    return getCtgryColl(lConnection,SIConfig.CTGRYGROUP_INX_HAIRCOSME);
  }
  
  public static Collection getEstheColl(Connection lConnection) {
    return getCtgryColl(lConnection,SIConfig.CTGRYGROUP_INX_ESTHE);
  }
  
  public static Collection getBedColl(Connection lConnection) {
    return getCtgryColl(lConnection,SIConfig.CTGRYGROUP_INX_BED);
  }
  
  public static Collection getCosmeColl(Connection lConnection) {
    return getCtgryColl(lConnection,SIConfig.CTGRYGROUP_INX_COSME);
  }
  
  public static Collection getNailColl(Connection lConnection) {
    return getCtgryColl(lConnection,SIConfig.CTGRYGROUP_INX_NAIL);
  }
  
  public static Collection getBookColl(Connection lConnection) {
    return getCtgryColl(lConnection,SIConfig.CTGRYGROUP_INX_BOOK);
  }
  
  public static Collection getEyeLashColl(Connection lConnection) {
    return getCtgryColl(lConnection,SIConfig.CTGRYGROUP_INX_EYELASH);
  }
  
  private static Collection getCtgryColl(Connection lConnection,int group) {
    Collection res = new ArrayList();
    
    StringBuffer lSql = new StringBuffer();
    lSql.append("SELECT a.ctgryname,a.ctgrycode,a.abbvctgryname,b.disporder FROM ctgrymtbl a,ctgrygrouptbl b ");
    lSql.append("WHERE a.ctgrycode=b.ctgrycode ");
    for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
      lSql.append("AND a.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
    }
    if (group == SIConfig.CTGRYGROUP_INX_HAIR) {
      lSql.append("AND b.ctgrygroup='hair' ");
      lSql.append("UNION ALL SELECT c.ctgryname,c.ctgrycode,c.abbvctgryname,b.disporder FROM ctgrymtbl a,ctgrygrouptbl b,ctgrymtbl c ");
      lSql.append("WHERE a.ctgrycode=b.ctgrycode AND a.ctgrycode=c.parentctgrycode AND a.ctgrycode IN ('Z1','X1') ");//理美容小物、その他什器類は小カテ表示
      lSql.append("AND b.ctgrygroup='hair' ");
      for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
        lSql.append("AND c.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
      }
    }
    else if (group == SIConfig.CTGRYGROUP_INX_HAIRCOSME) {
      lSql.append("AND b.ctgrygroup='haircosme' ");
      lSql.append("UNION ALL SELECT c.ctgryname,c.ctgrycode,c.abbvctgryname,b.disporder FROM ctgrymtbl a,ctgrygrouptbl b,ctgrymtbl c ");
      lSql.append("WHERE a.ctgrycode=b.ctgrycode AND a.ctgrycode=c.parentctgrycode AND a.ctgrycode NOT IN ('category013','LM','LF') ");//エクステ、メイクアップ、ハーブティーは小カテ非表示
      lSql.append("AND b.ctgrygroup='haircosme' ");
      for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
        lSql.append("AND c.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
      }
    }
    else if (group == SIConfig.CTGRYGROUP_INX_ESTHE) {
      lSql.append("AND b.ctgrygroup='esthe' ");
      lSql.append("UNION ALL SELECT c.ctgryname,c.ctgrycode,c.abbvctgryname,b.disporder FROM ctgrymtbl a,ctgrygrouptbl b,ctgrymtbl c ");
      lSql.append("WHERE a.ctgrycode=b.ctgrycode AND a.ctgrycode=c.parentctgrycode ");//小カテ非表示
      lSql.append("AND b.ctgrygroup='esthe' ");
      for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
        lSql.append("AND c.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
      }
    }
    else if (group == SIConfig.CTGRYGROUP_INX_BOOK) {
      lSql.append("AND b.ctgrygroup='book' ");
      lSql.append("UNION ALL SELECT c.ctgryname,c.ctgrycode,c.abbvctgryname,b.disporder FROM ctgrymtbl a,ctgrygrouptbl b,ctgrymtbl c ");
      lSql.append("WHERE a.ctgrycode=b.ctgrycode AND a.ctgrycode=c.parentctgrycode ");//全て小カテ表示
      lSql.append("AND b.ctgrygroup='book' ");
      for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
        lSql.append("AND c.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
      }
    }
    else if (group == SIConfig.CTGRYGROUP_INX_EYELASH) {
      lSql.append("AND b.ctgrygroup='eyelash' ");
      lSql.append("UNION ALL SELECT c.ctgryname,c.ctgrycode,c.abbvctgryname,b.disporder FROM ctgrymtbl a,ctgrygrouptbl b,ctgrymtbl c ");
      lSql.append("WHERE a.ctgrycode=b.ctgrycode AND a.ctgrycode=c.parentctgrycode AND a.ctgrycode NOT IN ('LF','TU') ");
      lSql.append("AND b.ctgrygroup='eyelash' ");
      for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
        lSql.append("AND c.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
      }
    }
    else if (group == SIConfig.CTGRYGROUP_INX_BED) lSql.append("AND b.ctgrygroup='bed' ");
    else if (group == SIConfig.CTGRYGROUP_INX_COSME) lSql.append("AND b.ctgrygroup='cosme' ");
    else if (group == SIConfig.CTGRYGROUP_INX_NAIL) lSql.append("AND b.ctgrygroup='nail' ");
    
    else return res;
    
    lSql.append("ORDER BY disporder,abbvctgryname ");
    
    try {
      res = SIDBUtil.getCollection(lConnection, lSql.toString());
    } catch (Exception e) {
      e.printStackTrace();
      res = new ArrayList();
    }
    
    return res;
  }
  
  public static Collection getHairCollUsed(Connection lConnection) {
    return getCtgryCollUsed(lConnection,SIConfig.CTGRYGROUP_INX_HAIR);
  }
  
  public static Collection getEstheCollUsed(Connection lConnection) {
    return getCtgryCollUsed(lConnection,SIConfig.CTGRYGROUP_INX_ESTHE);
  }
  
  public static Collection getBedCollUsed(Connection lConnection) {
    return getCtgryCollUsed(lConnection,SIConfig.CTGRYGROUP_INX_BED);
  }
  private static Collection getCtgryCollUsed(Connection lConnection,int group) {
    Collection res = new ArrayList();
    
    StringBuffer lSql = new StringBuffer();
    lSql.append("SELECT a.ctgryname,a.ctgrycode FROM ctgrymtbl a,ctgrygrouptbl b ");
    lSql.append("WHERE a.ctgrycode=b.ctgrycode ");
    if (group == SIConfig.CTGRYGROUP_INX_HAIR) lSql.append("AND b.ctgrygroup='hair' ");
    else if (group == SIConfig.CTGRYGROUP_INX_HAIRCOSME) lSql.append("AND b.ctgrygroup='haircosme' ");
    else if (group == SIConfig.CTGRYGROUP_INX_ESTHE) lSql.append("AND b.ctgrygroup='esthe' ");
    else if (group == SIConfig.CTGRYGROUP_INX_BED) lSql.append("AND b.ctgrygroup='bed' ");
    else if (group == SIConfig.CTGRYGROUP_INX_COSME) lSql.append("AND b.ctgrygroup='cosme' ");
    else if (group == SIConfig.CTGRYGROUP_INX_NAIL) lSql.append("AND b.ctgrygroup='nail' ");
    else if (group == SIConfig.CTGRYGROUP_INX_BOOK) lSql.append("AND b.ctgrygroup='book' ");
    else if (group == SIConfig.CTGRYGROUP_INX_EYELASH) lSql.append("AND b.ctgrygroup='eyelash' ");
    else return res;
    lSql.append("AND a.ctgrycode NOT IN ('category013','category014') ");//エクステ、ネイルは対象外
    lSql.append("ORDER BY b.disporder ");
    
    try {
      res = SIDBUtil.getCollection(lConnection, lSql.toString());
    } catch (Exception e) {
      e.printStackTrace();
      res = new ArrayList();
    }
    
    return res;
  }
  
  public static String getHairCtgryTree(Connection lConnection,String ctgryCode,String url) {
    return getCtgryTree(lConnection,ctgryCode,url,SIConfig.CTGRYGROUP_INX_HAIR);
  }
  
  public static String getHairCosmeCtgryTree(Connection lConnection,String ctgryCode,String url) {
    return getCtgryTree(lConnection,ctgryCode,url,SIConfig.CTGRYGROUP_INX_HAIRCOSME);
  }
  
  public static String getEstheCtgryTree(Connection lConnection,String ctgryCode,String url) {
    return getCtgryTree(lConnection,ctgryCode,url,SIConfig.CTGRYGROUP_INX_ESTHE);
  }
  
  public static String getBedCtgryTree(Connection lConnection,String ctgryCode,String url) {
    return getCtgryTree(lConnection,ctgryCode,url,SIConfig.CTGRYGROUP_INX_BED);
  }
  
  public static String getCosmeCtgryTree(Connection lConnection,String ctgryCode,String url) {
    return getCtgryTree(lConnection,ctgryCode,url,SIConfig.CTGRYGROUP_INX_COSME);
  }
  
  public static String getNailCtgryTree(Connection lConnection,String ctgryCode,String url) {
    return getCtgryTree(lConnection,ctgryCode,url,SIConfig.CTGRYGROUP_INX_NAIL);
  }
  
  public static String getBookCtgryTree(Connection lConnection,String ctgryCode,String url) {
    return getCtgryTree(lConnection,ctgryCode,url,SIConfig.CTGRYGROUP_INX_BOOK);
  }
  
  public static String getEyeLashCtgryTree(Connection lConnection,String ctgryCode,String url) {
    return getCtgryTree(lConnection,ctgryCode,url,SIConfig.CTGRYGROUP_INX_EYELASH);
  }
  
  public static String getCtgryTree(Connection lConnection,String ctgryCode,String url,int group) {
    StringBuffer lSql = new StringBuffer();
    StringBuffer lRes = new StringBuffer();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    if (SIUtil.isNull(ctgryCode)) ctgryCode="";
    
    String ctgryGroup = "";
    if (group == SIConfig.CTGRYGROUP_INX_HAIR) ctgryGroup="hair";
    else if (group == SIConfig.CTGRYGROUP_INX_HAIRCOSME) ctgryGroup="haircosme";
    else if (group == SIConfig.CTGRYGROUP_INX_ESTHE) ctgryGroup="esthe";
    else if (group == SIConfig.CTGRYGROUP_INX_BED) ctgryGroup="bed";
    else if (group == SIConfig.CTGRYGROUP_INX_COSME) ctgryGroup="cosme";
    else if (group == SIConfig.CTGRYGROUP_INX_NAIL) ctgryGroup="nail";
    else if (group == SIConfig.CTGRYGROUP_INX_BOOK) ctgryGroup="book";
    else if (group == SIConfig.CTGRYGROUP_INX_EYELASH) ctgryGroup="eyelash";
    
    lSql.append("SELECT a.ctgryname,a.ctgrycode,a.cmdtyamount,b.disporder,a.abbvctgryname,0 AS inx ");
    lSql.append("FROM ctgrymtbl a,ctgrygrouptbl b ");
    lSql.append("WHERE a.ctgrycode=b.ctgrycode AND b.ctgrygroup=").append(SIDBUtil.SQL2Str(ctgryGroup," "));
    for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
      lSql.append("AND a.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
    }
    
    lSql.append("UNION SELECT a.ctgryname,a.ctgrycode,a.cmdtyamount,b.disporder,a.abbvctgryname,1 AS inx ");
    lSql.append("FROM ctgrymtbl a,ctgrygrouptbl b,ctgrymtbl c ");
    lSql.append("WHERE a.parentctgrycode=b.ctgrycode AND b.ctgrygroup=").append(SIDBUtil.SQL2Str(ctgryGroup," "));
    lSql.append("AND a.parentctgrycode IN (c.parentctgrycode,c.ctgrycode)  ");
    if (group==SIConfig.CTGRYGROUP_INX_BED || group==SIConfig.CTGRYGROUP_INX_COSME) {
      lSql.append("AND c.ctgrycode=").append(SIDBUtil.SQL2Str(ctgryCode," "));
    } else if (group==SIConfig.CTGRYGROUP_INX_HAIR) {
      if(SIUtil.isNull(ctgryCode)) lSql.append("AND c.ctgrycode IN ('Z1','X1') ");
      else lSql.append("AND c.ctgrycode IN ('Z1','X1',").append(SIDBUtil.SQL2Str(ctgryCode,") "));
    }else if (group==SIConfig.CTGRYGROUP_INX_NAIL||group==SIConfig.CTGRYGROUP_INX_EYELASH) {
      if (SIUtil.isNull(ctgryCode)||!ctgryCode.startsWith("LF")) lSql.append("AND b.ctgrycode<>'LF' ");
      if (SIUtil.isNull(ctgryCode)||!ctgryCode.startsWith("TU")) lSql.append("AND b.ctgrycode<>'TU' ");
    }
    
    for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
      lSql.append("AND a.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
    }
    lSql.append("ORDER BY disporder,abbvctgryname");
    
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      String lastIndex = "";
      while (lResultSet.next()) {
        if (SIUtil.isNull(lastIndex)) {
          if (group==SIConfig.CTGRYGROUP_INX_BOOK) {
            lRes.append("<ul class=\"catbig1\">\n");
          } else {
            lRes.append("<ul>\n");
          }
        } else if ("0".equals(lastIndex)&&"1".equals(lResultSet.getString("inx"))) {
          lRes.append("\n<ul>\n");
        } else if ("1".equals(lastIndex)&&"0".equals(lResultSet.getString("inx"))) {
          lRes.append("</li>\n</ul>\n</li>\n");
        } else {
          lRes.append("</li>\n");
        }
        if (lResultSet.getString("ctgryCode").equals(ctgryCode)) {
          if (group==SIConfig.CTGRYGROUP_INX_BOOK && "0".equals(lResultSet.getString("inx"))) {
            lRes.append("<li class=\"catbig2\">");
          } else {
            lRes.append("<li>");
          }
          lRes.append("<span><a href=\"").append(url).append("/").append(ctgryGroup).append("/ctgry/");
          lRes.append(lResultSet.getString("ctgryCode")).append("\">");
          lRes.append(lResultSet.getString("ctgryName")).append("(").append(lResultSet.getString("cmdtyAmount")).append(")</a></span>");
        } else {
          if (group==SIConfig.CTGRYGROUP_INX_BOOK && "0".equals(lResultSet.getString("inx"))) {
            lRes.append("<li class=\"catbig2\">");
          } else {
            lRes.append("<li>");
          }
          lRes.append("<a href=\"").append(url).append("/").append(ctgryGroup).append("/ctgry/");
          lRes.append(lResultSet.getString("ctgryCode")).append("\">");
          lRes.append(lResultSet.getString("ctgryName")).append("(").append(lResultSet.getString("cmdtyAmount")).append(")</a>");
        }
        lastIndex = lResultSet.getString("inx");
      }
      lRes.append("</li>\n</ul>\n");
      if ("1".equals(lastIndex)) lRes.append("</ul>\n");
    } catch (Exception e) {
      e.printStackTrace();
      lRes = new StringBuffer();
    } finally {
      SIDBUtil.close(lStatement,lResultSet);
    }
    return lRes.toString();
  }
  
  public static String getHomeCtgryTree(Connection lConnection,String url,int group){
    StringBuffer lSql = new StringBuffer();
    StringBuffer lRes = new StringBuffer();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    String ctgryGroup = "";
    if (group == SIConfig.CTGRYGROUP_INX_HAIR) ctgryGroup="hair";
    else if (group == SIConfig.CTGRYGROUP_INX_HAIRCOSME) ctgryGroup="haircosme";
    else if (group == SIConfig.CTGRYGROUP_INX_ESTHE) ctgryGroup="esthe";
    else if (group == SIConfig.CTGRYGROUP_INX_BED) ctgryGroup="bed";
    else if (group == SIConfig.CTGRYGROUP_INX_COSME) ctgryGroup="cosme";
    else if (group == SIConfig.CTGRYGROUP_INX_NAIL) ctgryGroup="nail";
    else if (group == SIConfig.CTGRYGROUP_INX_BOOK) ctgryGroup="book";
    else if (group == SIConfig.CTGRYGROUP_INX_EYELASH) ctgryGroup="eyelash";
    
    try {
      lRes.append("<div class=\"column4\">");
      lRes.append("<div class=\"").append(ctgryGroup).append("\">");
      lRes.append("<p class=\"top\"><a href=\"").append(url).append("/").append(ctgryGroup).append("\">").append(SIConfig.ctgryGroupName[group-1]).append("TOP</a></p>");
      lRes.append("<ul>");
      
      lSql.append("SELECT c.ctgrycode,c.ctgryname,c.abbvctgryname,g.disporder ");
      lSql.append("FROM ctgrymtbl c,ctgrygrouptbl g ");
      lSql.append("WHERE c.ctgrycode=g.ctgrycode ");
      lSql.append("AND g.ctgrygroup=").append(SIDBUtil.SQL2Str(ctgryGroup," "));
      for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
        lSql.append("AND c.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
      }
      if (group == SIConfig.CTGRYGROUP_INX_HAIR) {
        lSql.append("UNION ALL SELECT c.ctgrycode,c.ctgryname,c.abbvctgryname,b.disporder FROM ctgrymtbl a,ctgrygrouptbl b,ctgrymtbl c ");
        lSql.append("WHERE a.ctgrycode=b.ctgrycode AND a.ctgrycode=c.parentctgrycode AND a.ctgrycode IN ('Z1','X1') ");//理美容小物、その他什器類は小カテ表示
        lSql.append("AND b.ctgrygroup='hair' ");
        for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
          lSql.append("AND c.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
        }
      } else if (group == SIConfig.CTGRYGROUP_INX_HAIRCOSME) {
        lSql.append("UNION ALL SELECT c.ctgrycode,c.ctgryname,c.abbvctgryname,b.disporder FROM ctgrymtbl a,ctgrygrouptbl b,ctgrymtbl c ");
        lSql.append("WHERE a.ctgrycode=b.ctgrycode AND a.ctgrycode=c.parentctgrycode AND a.ctgrycode NOT IN ('category013','LM','LF') ");//エクステ、メイクアップ、ハーブティーは小カテ非表示
        lSql.append("AND b.ctgrygroup='haircosme' ");
        for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
          lSql.append("AND c.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
        }
      } else if (group == SIConfig.CTGRYGROUP_INX_ESTHE) {
        lSql.append("UNION ALL SELECT c.ctgrycode,c.ctgryname,c.abbvctgryname,b.disporder FROM ctgrymtbl a,ctgrygrouptbl b,ctgrymtbl c ");
        lSql.append("WHERE a.ctgrycode=b.ctgrycode AND a.ctgrycode=c.parentctgrycode ");//小カテ非表示
        lSql.append("AND b.ctgrygroup='esthe' ");
        for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
          lSql.append("AND c.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
        }
      } else if (group == SIConfig.CTGRYGROUP_INX_BOOK) {
        lSql.append("UNION ALL SELECT c.ctgrycode,c.ctgryname,c.abbvctgryname,b.disporder FROM ctgrymtbl a,ctgrygrouptbl b,ctgrymtbl c ");
        lSql.append("WHERE a.ctgrycode=b.ctgrycode AND a.ctgrycode=c.parentctgrycode ");//全て子カテ表示
        lSql.append("AND b.ctgrygroup='book' ");
        for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
          lSql.append("AND c.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
        }
      } else if (group == SIConfig.CTGRYGROUP_INX_EYELASH) {
        lSql.append("UNION ALL SELECT c.ctgrycode,c.ctgryname,c.abbvctgryname,b.disporder FROM ctgrymtbl a,ctgrygrouptbl b,ctgrymtbl c ");
        lSql.append("WHERE a.ctgrycode=b.ctgrycode AND a.ctgrycode=c.parentctgrycode AND a.ctgrycode NOT IN ('LF','TU') ");//LF,TU以外子カテ表示
        lSql.append("AND b.ctgrygroup='eyelash' ");
        for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
          lSql.append("AND c.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
        }
      }
      lSql.append("ORDER BY disporder,abbvctgryname");
      
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      while (lResultSet.next()) {
        lRes.append("<li><a href=\"").append(url).append("/").append(ctgryGroup).append("/ctgry/").append(lResultSet.getString("ctgrycode")).append("\">");
        lRes.append(lResultSet.getString("ctgryname")).append("</a></li>");
      }
      lRes.append("</ul>");
      lRes.append("</div>");
      lRes.append("</div>");
    } catch (Exception e) {
      e.printStackTrace();
      lRes = new StringBuffer();
    } finally {
      SIDBUtil.close(lStatement,lResultSet);
    }
    return lRes.toString();
  }
  
  public static String getHairCtgryTreeUsed(Connection lConnection,String url) {
    return getCtgryTreeUsed(lConnection,url,SIConfig.CTGRYGROUP_INX_HAIR);
  }
  
  public static String getEstheCtgryTreeUsed(Connection lConnection,String url) {
    return getCtgryTreeUsed(lConnection,url,SIConfig.CTGRYGROUP_INX_ESTHE);
  }
  
  public static String getBedCtgryTreeUsed(Connection lConnection,String url) {
    return getCtgryTreeUsed(lConnection,url,SIConfig.CTGRYGROUP_INX_BED);
  }
  
  public static String getCtgryTreeUsed(Connection lConnection,String url,int group) {
    StringBuffer lSql = new StringBuffer();
    StringBuffer lRes = new StringBuffer();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    String ctgryGroup = "";
    if (group == SIConfig.CTGRYGROUP_INX_HAIR) ctgryGroup="hair";
    else if (group == SIConfig.CTGRYGROUP_INX_HAIRCOSME) ctgryGroup="haircosme";
    else if (group == SIConfig.CTGRYGROUP_INX_ESTHE) ctgryGroup="esthe";
    else if (group == SIConfig.CTGRYGROUP_INX_BED) ctgryGroup="bed";
    else if (group == SIConfig.CTGRYGROUP_INX_COSME) ctgryGroup="cosme";
    else if (group == SIConfig.CTGRYGROUP_INX_NAIL) ctgryGroup="nail";
    else if (group == SIConfig.CTGRYGROUP_INX_BOOK) ctgryGroup="book";
    else if (group == SIConfig.CTGRYGROUP_INX_EYELASH) ctgryGroup="eyelash";
    
    lSql.append("SELECT a.ctgryname,a.ctgrycode,b.disporder ");
    lSql.append("FROM ctgrymtbl a,ctgrygrouptbl b ");
    lSql.append("WHERE a.ctgrycode=b.ctgrycode AND b.ctgrygroup=").append(SIDBUtil.SQL2Str(ctgryGroup," "));
    for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
      lSql.append("AND a.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
    }
    lSql.append("AND a.ctgrycode NOT IN ('category013','category014') ");//エクステ、ネイルは対象外
    lSql.append("ORDER BY disporder");
    
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      lRes.append("<ul>\n");
      while (lResultSet.next()) {
        lRes.append("<li><a href=\"").append(url).append("/").append(ctgryGroup).append("/actionNameTxt/list");
        lRes.append("/dispModeTxt/").append(SIConfig.DEFAULT_DISPMODE).append("/usedNewFlgTxt/used/ctc/");
        lRes.append(lResultSet.getString("ctgryCode")).append("\">");
        lRes.append(lResultSet.getString("ctgryName")).append("</a>");
      }
      lRes.append("</ul>\n");
    } catch (Exception e) {
      e.printStackTrace();
      lRes = new StringBuffer();
    } finally {
      SIDBUtil.close(lStatement,lResultSet);
    }
    return lRes.toString();
  }
  
  public static String getHomeCtgryTreeUsed(Connection lConnection,String url,int group){
    StringBuffer lSql = new StringBuffer();
    StringBuffer lRes = new StringBuffer();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    String ctgryGroup = "";
    if (group == SIConfig.CTGRYGROUP_INX_HAIR) ctgryGroup="hair";
    else if (group == SIConfig.CTGRYGROUP_INX_HAIRCOSME) ctgryGroup="haircosme";
    else if (group == SIConfig.CTGRYGROUP_INX_ESTHE) ctgryGroup="esthe";
    else if (group == SIConfig.CTGRYGROUP_INX_BED) ctgryGroup="bed";
    else if (group == SIConfig.CTGRYGROUP_INX_COSME) ctgryGroup="cosme";
    else if (group == SIConfig.CTGRYGROUP_INX_NAIL) ctgryGroup="nail";
    else if (group == SIConfig.CTGRYGROUP_INX_BOOK) ctgryGroup="book";
    else if (group == SIConfig.CTGRYGROUP_INX_EYELASH) ctgryGroup="eyelash";
    
    try {
      lRes.append("<ul class=\"used_").append(ctgryGroup).append("\">");
      lRes.append("<li class=\"top\">").append(SIConfig.ctgryGroupName[group-1]).append("</li>");
      
      lSql.append("SELECT c.ctgrycode,c.ctgryname ");
      lSql.append("FROM ctgrymtbl c,ctgrygrouptbl g ");
      lSql.append("WHERE c.ctgrycode=g.ctgrycode ");
      lSql.append("AND g.ctgrygroup=").append(SIDBUtil.SQL2Str(ctgryGroup," "));
      lSql.append("ORDER BY g.disporder");
      
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      while (lResultSet.next()) {
        lRes.append("<li><a href=\"").append(url).append("/").append(ctgryGroup).append("/a/list/dispModeTxt/1/usedNewFlgTxt/used/ctc/").append(lResultSet.getString("ctgrycode")).append("\">");
        lRes.append(lResultSet.getString("ctgryname")).append("</a></li>");
      }
      lRes.append("</ul>");
    } catch (Exception e) {
      e.printStackTrace();
      lRes = new StringBuffer();
    } finally {
      SIDBUtil.close(lStatement,lResultSet);
    }
    return lRes.toString();
  }
  
  public static String getHairCtgryPlate(Connection lConnection,String url) {
    return getCtgryPlate(lConnection,SIConfig.CTGRYGROUP_INX_HAIR,url);
  }
  
  public static String getHairCosmeCtgryPlate(Connection lConnection,String url) {
    return getCtgryPlate(lConnection,SIConfig.CTGRYGROUP_INX_HAIRCOSME,url);
  }
  
  public static String getEstheCtgryPlate(Connection lConnection,String url) {
    return getCtgryPlate(lConnection,SIConfig.CTGRYGROUP_INX_ESTHE,url);
  }
  
  public static String getBedCtgryPlate(Connection lConnection,String url) {
    return getCtgryPlate(lConnection,SIConfig.CTGRYGROUP_INX_BED,url);
  }
  
  public static String getCosmeCtgryPlate(Connection lConnection,String url) {
    return getCtgryPlate(lConnection,SIConfig.CTGRYGROUP_INX_COSME,url);
  }
  
  public static String getNailCtgryPlate(Connection lConnection,String url) {
    return getCtgryPlate(lConnection,SIConfig.CTGRYGROUP_INX_NAIL,url);
  }
  
  public static String getBookCtgryPlate(Connection lConnection,String url) {
    return getCtgryPlate(lConnection,SIConfig.CTGRYGROUP_INX_BOOK,url);
  }
  
  public static String getEyeLashCtgryPlate(Connection lConnection,String url) {
    return getCtgryPlate(lConnection,SIConfig.CTGRYGROUP_INX_EYELASH,url);
  }
  
  public static String getCtgryPlate(Connection lConnection,int group,String url) {
    StringBuffer lSql = new StringBuffer();
    StringBuffer lRes = new StringBuffer();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    DecimalFormat exFormat = new DecimalFormat("00");
    
    String ctgryGroup = "";
    if (group == SIConfig.CTGRYGROUP_INX_HAIR) ctgryGroup="hair";
    else if (group == SIConfig.CTGRYGROUP_INX_HAIRCOSME) ctgryGroup="haircosme";
    else if (group == SIConfig.CTGRYGROUP_INX_ESTHE) ctgryGroup="esthe";
    else if (group == SIConfig.CTGRYGROUP_INX_BED) ctgryGroup="bed";
    else if (group == SIConfig.CTGRYGROUP_INX_COSME) ctgryGroup="cosme";
    else if (group == SIConfig.CTGRYGROUP_INX_NAIL) ctgryGroup="nail";
    else if (group == SIConfig.CTGRYGROUP_INX_BOOK) ctgryGroup="book";
    else if (group == SIConfig.CTGRYGROUP_INX_EYELASH) ctgryGroup="eyelash";
    
    lSql.append("SELECT a.ctgryname,a.ctgrycode,b.disporder,a.abbvctgryname,0 AS inx ");
    lSql.append("FROM ctgrymtbl a,ctgrygrouptbl b ");
    lSql.append("WHERE a.ctgrycode=b.ctgrycode AND b.ctgrygroup=").append(SIDBUtil.SQL2Str(ctgryGroup," "));
    for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
      lSql.append("AND a.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
    }
    lSql.append("UNION SELECT a.ctgryname,a.ctgrycode,b.disporder,a.abbvctgryname,1 AS inx ");
    lSql.append("FROM ctgrymtbl a,ctgrygrouptbl b ");
    lSql.append("WHERE a.parentctgrycode=b.ctgrycode AND b.ctgrygroup=").append(SIDBUtil.SQL2Str(ctgryGroup," "));
    for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
      lSql.append("AND a.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
    }
    lSql.append("ORDER BY disporder,abbvctgryname");
    
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      int count = 0;
      String lastIndex = "";
      while (lResultSet.next()) {
        if ("0".equals(lResultSet.getString("inx"))) {//大カテゴリ頭
          count++;
          if (SIUtil.isNull(lastIndex)) {//初回
            lRes.append("<div id=\"block_category01\" class=\"wrapper\">\n");
          } else {
            if ("1".equals(lastIndex)) lRes.append("</ul>\n");
            lRes.append("</div>\n");
            if (count % 2 == 1) {
              lRes.append("</div>\n");
              lRes.append("<div class=\"wrapper\">\n");
            }
          }
          String ctgryNo = exFormat.format(count);
          lRes.append("<div class=\"box_category ").append(ctgryGroup).append("_box").append(ctgryNo).append("\">\n");
          lRes.append("<h3><a href=\"").append(url).append("/").append(ctgryGroup).append("/ctgry/");
          lRes.append(lResultSet.getString("ctgryCode")).append("\">");
          lRes.append(lResultSet.getString("ctgryName")).append("</a></h3>\n");
        } else if ("1".equals(lResultSet.getString("inx"))) {//小カテゴリ
          if ("0".equals(lastIndex)) {
            String ctgryNo = exFormat.format(count);
            lRes.append("<ul class=\"").append(ctgryGroup).append("_category").append(ctgryNo).append("\">\n");
          }
          lRes.append("<li><a href=\"").append(url).append("/").append(ctgryGroup).append("/ctgry/");
          lRes.append(lResultSet.getString("ctgryCode")).append("\">");
          lRes.append(lResultSet.getString("ctgryName")).append("</a></li>\n");
        }
        lastIndex = lResultSet.getString("inx");
      }
      if ("1".equals(lastIndex)) lRes.append("</ul>\n");
      lRes.append("</div>\n");
      lRes.append("</div>\n");
    } catch (Exception e) {
      e.printStackTrace();
      lRes = new StringBuffer();
    } finally {
      SIDBUtil.close(lStatement,lResultSet);
    }
    return lRes.toString();
  }
  
  public static String getHairCtgryPlateMobile(Connection lConnection,String path) {
    return getCtgryPlateMobile(lConnection,path,SIConfig.CTGRYGROUP_INX_HAIR);
  }
  
  public static String getHairCosmeCtgryPlateMobile(Connection lConnection,String path) {
    return getCtgryPlateMobile(lConnection,path,SIConfig.CTGRYGROUP_INX_HAIRCOSME);
  }
  
  public static String getEstheCtgryPlateMobile(Connection lConnection,String path) {
    return getCtgryPlateMobile(lConnection,path,SIConfig.CTGRYGROUP_INX_ESTHE);
  }
  
  public static String getBedCtgryPlateMobile(Connection lConnection,String path) {
    return getCtgryPlateMobile(lConnection,path,SIConfig.CTGRYGROUP_INX_BED);
  }
  
  public static String getCosmeCtgryPlateMobile(Connection lConnection,String path) {
    return getCtgryPlateMobile(lConnection,path,SIConfig.CTGRYGROUP_INX_COSME);
  }
  
  public static String getNailCtgryPlateMobile(Connection lConnection,String path) {
    return getCtgryPlateMobile(lConnection,path,SIConfig.CTGRYGROUP_INX_NAIL);
  }
  
  public static String getBookCtgryPlateMobile(Connection lConnection,String path) {
    return getCtgryPlateMobile(lConnection,path,SIConfig.CTGRYGROUP_INX_BOOK);
  }
  
  public static String getEyeLashCtgryPlateMobile(Connection lConnection,String path) {
    return getCtgryPlateMobile(lConnection,path,SIConfig.CTGRYGROUP_INX_EYELASH);
  }
  
  public static String getCtgryPlateMobile(Connection lConnection,String path,int group) {
    return getCtgryPlateMobile(lConnection,path,group,false);
  }
  
  public static String getHairCtgryPlateMobileS(Connection lConnection,String path) {
    return getCtgryPlateMobile(lConnection,path,SIConfig.CTGRYGROUP_INX_HAIR,true);
  }
  
  public static String getHairCosmeCtgryPlateMobileS(Connection lConnection,String path) {
    return getCtgryPlateMobile(lConnection,path,SIConfig.CTGRYGROUP_INX_HAIRCOSME,true);
  }
  
  public static String getEstheCtgryPlateMobileS(Connection lConnection,String path) {
    return getCtgryPlateMobile(lConnection,path,SIConfig.CTGRYGROUP_INX_ESTHE,true);
  }
  
  public static String getBedCtgryPlateMobileS(Connection lConnection,String path) {
    return getCtgryPlateMobile(lConnection,path,SIConfig.CTGRYGROUP_INX_BED,true);
  }
  
  public static String getCosmeCtgryPlateMobileS(Connection lConnection,String path) {
    return getCtgryPlateMobile(lConnection,path,SIConfig.CTGRYGROUP_INX_COSME,true);
  }
  
  public static String getNailCtgryPlateMobileS(Connection lConnection,String path) {
    return getCtgryPlateMobile(lConnection,path,SIConfig.CTGRYGROUP_INX_NAIL,true);
  }
  
  public static String getBookCtgryPlateMobileS(Connection lConnection,String path) {
    return getCtgryPlateMobile(lConnection,path,SIConfig.CTGRYGROUP_INX_BOOK,true);
  }
  
  public static String getEyeLashCtgryPlateMobileS(Connection lConnection,String path) {
    return getCtgryPlateMobile(lConnection,path,SIConfig.CTGRYGROUP_INX_EYELASH,true);
  }
  
  public static String getCtgryPlateMobile(Connection lConnection,String path,int group,boolean isSmartPhone) {
    StringBuffer lSql = new StringBuffer();
    StringBuffer lRes = new StringBuffer();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    String ctgryGroup = "";
    if (group == SIConfig.CTGRYGROUP_INX_HAIR) ctgryGroup="hair";
    else if (group == SIConfig.CTGRYGROUP_INX_HAIRCOSME) ctgryGroup="haircosme";
    else if (group == SIConfig.CTGRYGROUP_INX_ESTHE) ctgryGroup="esthe";
    else if (group == SIConfig.CTGRYGROUP_INX_BED) ctgryGroup="bed";
    else if (group == SIConfig.CTGRYGROUP_INX_COSME) ctgryGroup="cosme";
    else if (group == SIConfig.CTGRYGROUP_INX_NAIL) ctgryGroup="nail";
    else if (group == SIConfig.CTGRYGROUP_INX_BOOK) ctgryGroup="book";
    else if (group == SIConfig.CTGRYGROUP_INX_EYELASH) ctgryGroup="eyelash";
    
    lSql.append("SELECT a.ctgrycode,a.ctgryname ");
    lSql.append(",CASE WHEN b.parentctgrycode IS NULL THEN 1 ELSE 0 END AS childflg ");
    lSql.append("FROM ctgrygrouptbl g,ctgrymtbl a LEFT OUTER JOIN ");
    lSql.append("(SELECT parentctgrycode FROM ctgrymtbl GROUP BY parentctgrycode) b ");
    lSql.append("ON a.ctgrycode=b.parentctgrycode ");
    lSql.append("WHERE g.ctgrycode=a.ctgrycode AND g.ctgrygroup=").append(SIDBUtil.SQL2Str(ctgryGroup," "));
    for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
      lSql.append("AND a.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
    }
    lSql.append("ORDER BY g.disporder");
    
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      while (lResultSet.next()) {
        if (!lResultSet.isFirst() && !isSmartPhone) lRes.append(" | ");
        
        if(isSmartPhone){//add スマートフォンアコーディオン対応
          if ("0".equals(lResultSet.getString("childflg"))) {//大カテゴリ
            lRes.append("<div class=\"mcsp_m bg_cate-title\">").append(lResultSet.getString("ctgryName")).append("</div>");
            lRes.append("<div class=\"look-ahead\" style=\"display:none;\">");
            lRes.append("<div id=\"").append(lResultSet.getString("ctgryCode")).append("\">");
            lRes.append("<img src=\"../../img/mobile/sp/category/bg_loading.gif\"></div>");
            lRes.append("<div id=\"ajax_").append(lResultSet.getString("ctgryCode")).append("\"></div></div>");
          }else{
            lRes.append("<a class=\"bg_cate-title-link\" href=\"");
            lRes.append(path).append("?a=list&ctc=");
            lRes.append(lResultSet.getString("ctgryCode")).append("\">").append(lResultSet.getString("ctgryName")).append("</a>");
          }//小カテゴリ
        }else{
          if ("0".equals(lResultSet.getString("childflg"))) {//大カテゴリ
            lRes.append("<a href=\"");
            lRes.append(path).append("?a=ctgry&ctc=");
          }else{
            lRes.append("<a href=\"");
            lRes.append(path).append("?a=list&ctc=");
          }//小カテゴリ
          lRes.append(lResultSet.getString("ctgryCode")).append("\">");
          lRes.append(SIStringUtil.toHankakuKana(lResultSet.getString("ctgryName"))).append("</a>");
        }
        
      }
    } catch (Exception e) {
      e.printStackTrace();
      lRes = new StringBuffer();
    } finally {
      SIDBUtil.close(lStatement,lResultSet);
    }
    return lRes.toString();
  }
  public static Collection getCtgryListColl(Connection lConnection){
    return getCtgryListColl(lConnection,true);
  }
  
  public static Collection getCtgryListColl(Connection lConnection,boolean detailEnable){
    Collection res = new ArrayList();
    SINameValue val = new SINameValue("カテゴリ選択","/,advancedsearch");
    String selectHead = "";
    if (detailEnable) selectHead="◆";
    res.add(val);
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    StringBuffer lSql = new StringBuffer();
    lSql.append("SELECT b.ctgryname,a.ctgrycode,a.ctgrygroup,c.disporder AS check, ");
    lSql.append("CASE a.ctgrygroup WHEN 'hair' THEN 100 WHEN 'haircosme' THEN 200 WHEN 'esthe' THEN 300 WHEN 'bed' THEN 400 ");
    lSql.append("WHEN 'cosme' THEN 500 WHEN 'nail' THEN 600 WHEN 'eyelash' THEN 700 WHEN 'book' THEN 800 ELSE 900 END + a.disporder AS disporder,'0' AS abbvctgryname ");
    lSql.append("FROM ctgrymtbl b,ctgrygrouptbl a ");
    lSql.append("LEFT OUTER JOIN (SELECT max(disporder) AS disporder,ctgrygroup FROM ctgrygrouptbl GROUP BY ctgrygroup) c ");
    lSql.append("ON a.ctgrygroup=c.ctgrygroup AND a.disporder=c.disporder ");
    lSql.append("WHERE a.ctgrycode=b.ctgrycode ");
    lSql.append("AND a.ctgrygroup<>'eyelashgarage' ");
    if (detailEnable){
      lSql.append("UNION ALL ");
      lSql.append("SELECT b.ctgryname,b.ctgrycode,a.ctgrygroup,CASE WHEN c.abbvctgryname IS NOT NULL THEN 999 ELSE 0 END AS check, ");
      lSql.append("CASE a.ctgrygroup WHEN 'hair' THEN 100 WHEN 'haircosme' THEN 200 WHEN 'esthe' THEN 300 WHEN 'bed' THEN 400 ");
      lSql.append("WHEN 'cosme' THEN 500 WHEN 'nail' THEN 600 WHEN 'eyelash' THEN 700 WHEN 'book' THEN 800 ELSE 900 END + a.disporder AS disporder,b.abbvctgryname ");
      lSql.append("FROM ctgrygrouptbl a,ctgrymtbl b ");
      lSql.append("LEFT OUTER JOIN (SELECT MAX(abbvctgryname) AS abbvctgryname,parentctgrycode FROM ctgrymtbl WHERE parentctgrycode IS NOT NULL AND parentctgrycode<>'/' ");
      for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
        lSql.append("AND ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
      }
      lSql.append("GROUP BY parentctgrycode) c ");
      lSql.append("ON b.parentctgrycode=c.parentctgrycode AND b.abbvctgryname=c.abbvctgryname ");
      lSql.append("WHERE a.ctgrycode=b.parentctgrycode ");
      lSql.append("AND a.ctgrygroup<>'eyelashgarage' ");
      for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
        lSql.append("AND b.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
      }
    }
    lSql.append("ORDER BY disporder,abbvctgryname");
    
    String lastGroup = "";
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      
      while (lResultSet.next()) {
        if (!lastGroup.equals(lResultSet.getString("ctgrygroup"))) {
          val = new SINameValue("",",\" disabled=\"disabled");
          res.add(val);
          if ("hair".equals(lResultSet.getString("ctgrygroup"))) {
            val = new SINameValue("【"+SIConfig.ctgryGroupName[SIConfig.CTGRYGROUP_HAIR]+"】","/,hair");
            res.add(val);
          } else if ("haircosme".equals(lResultSet.getString("ctgrygroup"))) {
            val = new SINameValue("【"+SIConfig.ctgryGroupName[SIConfig.CTGRYGROUP_HAIRCOSME]+"】","/,haircosme");
            res.add(val);
          } else if ("esthe".equals(lResultSet.getString("ctgrygroup"))) {
            val = new SINameValue("【"+SIConfig.ctgryGroupName[SIConfig.CTGRYGROUP_ESTHE]+"】","/,esthe");
            res.add(val);
          } else if ("bed".equals(lResultSet.getString("ctgrygroup"))) {
            val = new SINameValue("【"+SIConfig.ctgryGroupName[SIConfig.CTGRYGROUP_BED]+"】","/,bed");
            res.add(val);
          } else if ("cosme".equals(lResultSet.getString("ctgrygroup"))) {
            val = new SINameValue("【"+SIConfig.ctgryGroupName[SIConfig.CTGRYGROUP_COSME]+"】","/,cosme");
            res.add(val);
          } else if ("nail".equals(lResultSet.getString("ctgrygroup"))) {
            val = new SINameValue("【"+SIConfig.ctgryGroupName[SIConfig.CTGRYGROUP_NAIL]+"】","/,nail");
            res.add(val);
          } else if ("book".equals(lResultSet.getString("ctgrygroup"))) {
            val = new SINameValue("【"+SIConfig.ctgryGroupName[SIConfig.CTGRYGROUP_BOOK]+"】","/,book");
            res.add(val);
          } else if ("eyelash".equals(lResultSet.getString("ctgrygroup"))) {
            val = new SINameValue("【"+SIConfig.ctgryGroupName[SIConfig.CTGRYGROUP_EYELASH]+"】","/,eyelash");
            res.add(val);
          }
        }
        if (SIUtil.isNull(lResultSet.getString("check"))) {
          val = new SINameValue(selectHead+lResultSet.getString("ctgryname"),lResultSet.getString("ctgrycode")+","+lResultSet.getString("ctgrygroup"));
        } else if ("0".equals(lResultSet.getString("check"))){
          val = new SINameValue("├"+lResultSet.getString("ctgryname"),lResultSet.getString("ctgrycode")+","+lResultSet.getString("ctgrygroup"));
        } else if ("999".equals(lResultSet.getString("check"))){
          val = new SINameValue("└"+lResultSet.getString("ctgryname"),lResultSet.getString("ctgrycode")+","+lResultSet.getString("ctgrygroup"));
        } else {
          val = new SINameValue(selectHead+lResultSet.getString("ctgryname"),lResultSet.getString("ctgrycode")+","+lResultSet.getString("ctgrygroup"));
        }
        res.add(val);
        lastGroup = lResultSet.getString("ctgrygroup");
      }
    } catch (Exception e) {
      e.printStackTrace();
      res = new ArrayList();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return res;
  }
  
  public static Collection getSpCtgryListCollForSearchCtgryInc(Connection lConnection){
    Collection res = getSpCtgryListColl(lConnection);

    // 下位カテゴリがない場合、リンクにするため
    Object[] objArray = res.toArray();
    for (int i = 0; i < res.size(); i++) {
      SINameValueForSP val = (SINameValueForSP)objArray[i];
      if (i != (res.size() - 1)) {
        if (val.getValue().indexOf("/2") != -1) {
          // カテゴリトップの場合、下位カテゴリがあるかチェックする
          SINameValueForSP kaiVal = (SINameValueForSP)objArray[i + 1];
          if (kaiVal.getValue().indexOf("/2") != -1 || kaiVal.getValue().indexOf("/,") != -1) {
            // 下位もカテゴリトップの場合、リンクにする
            val.setValue(StringUtils.replace(val.getValue(), "/2", "/9"));
          }
        } else if (val.getValue().indexOf("/9") != -1) {
          // リンク設定だが、下位カテゴリがある場合
          SINameValueForSP kaiVal = (SINameValueForSP)objArray[i + 1];
          if (kaiVal.getValue().indexOf("/3") != -1) {
            // 下位もカテゴリトップの場合、上位カテゴリにする
            val.setValue(StringUtils.replace(val.getValue(), "/9", "/2"));
          }
        }
      } else {
        if (val.getValue().indexOf("/2") != -1) {
          val.setValue(StringUtils.replace(val.getValue(), "/2", "/9"));
        }
      }
    }
    return res;
  }

  // EDBTG005-00 nagayoshi add start
  public static Collection getSpCtgryListColl(Connection lConnection){
    return getSpCtgryListColl(lConnection,false);
  }
  
  public static Collection getSpCtgryListColl(Connection lConnection,boolean isTop){
    Collection res = new ArrayList();
    // EDBTG005-00 kamata mod start
    SINameValueForSP val = null;
    // EDBTG005-00 kamata mod end
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    StringBuffer lSql = new StringBuffer();
    lSql.append("SELECT b.ctgryname,a.ctgrycode,a.ctgrygroup,c.disporder AS check, ");
    lSql.append("CASE a.ctgrygroup WHEN 'hair' THEN 100 WHEN 'haircosme' THEN 200 WHEN 'esthe' THEN 300 WHEN 'bed' THEN 400 ");
    lSql.append("WHEN 'cosme' THEN 500 WHEN 'nail' THEN 600 WHEN 'eyelash' THEN 700 WHEN 'book' THEN 800 ELSE 900 END + a.disporder AS disporder,'0' AS abbvctgryname ");
    // EDBTG005-00 kamata add start
    lSql.append(",b.cmdtyamount ");
    // EDBTG005-00 kamata add end
    lSql.append("FROM ctgrymtbl b,ctgrygrouptbl a ");
    lSql.append("LEFT OUTER JOIN (SELECT max(disporder) AS disporder,ctgrygroup FROM ctgrygrouptbl GROUP BY ctgrygroup) c ");
    lSql.append("ON a.ctgrygroup=c.ctgrygroup AND a.disporder=c.disporder ");
    lSql.append("WHERE a.ctgrycode=b.ctgrycode ");
    lSql.append("AND a.ctgrygroup<>'eyelashgarage' ");
    lSql.append("UNION ALL ");
    lSql.append("SELECT b.ctgryname,b.ctgrycode,a.ctgrygroup,CASE WHEN c.abbvctgryname IS NOT NULL THEN 999 ELSE 0 END AS check, ");
    lSql.append("CASE a.ctgrygroup WHEN 'hair' THEN 100 WHEN 'haircosme' THEN 200 WHEN 'esthe' THEN 300 WHEN 'bed' THEN 400 ");
    lSql.append("WHEN 'cosme' THEN 500 WHEN 'nail' THEN 600 WHEN 'eyelash' THEN 700 WHEN 'book' THEN 800 ELSE 900 END + a.disporder AS disporder,b.abbvctgryname ");
    // EDBTG005-00 kamata add start
    lSql.append(",b.cmdtyamount ");
    // EDBTG005-00 kamata add end
    lSql.append("FROM ctgrygrouptbl a,ctgrymtbl b ");
    lSql.append("LEFT OUTER JOIN (SELECT MAX(abbvctgryname) AS abbvctgryname,parentctgrycode FROM ctgrymtbl WHERE parentctgrycode IS NOT NULL AND parentctgrycode<>'/' ");
    for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
      lSql.append("AND ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
    }
    lSql.append("GROUP BY parentctgrycode) c ");
    lSql.append("ON b.parentctgrycode=c.parentctgrycode AND b.abbvctgryname=c.abbvctgryname ");
    lSql.append("WHERE a.ctgrycode=b.parentctgrycode ");
    if (isTop) {//トップから呼んだ時のみカテゴリのデフォルトオープンを反映する（デフォルトクローズの子は呼ばない）
      lSql.append("AND CASE WHEN a.ctgrygroup='hair' AND a.ctgrycode IN ('Z1','X1') THEN true ");
      lSql.append(" WHEN a.ctgrygroup='haircosme' AND a.ctgrycode IN ('HC','H01','H02','H03','H04','H05','category013','LM') THEN true ");
      lSql.append(" WHEN a.ctgrygroup='esthe' AND a.ctgrycode IN ('TU','T1') THEN true ");;
      lSql.append(" WHEN a.ctgrygroup='bed' THEN false ");
      lSql.append(" WHEN a.ctgrygroup='cosme' THEN false ");
      lSql.append(" WHEN a.ctgrygroup='nail' AND a.ctgrycode IN ('N01','N02','N03','N04','NS','category019') THEN true ");
      lSql.append(" WHEN a.ctgrygroup='eyelash' AND a.ctgrycode IN ('WB','WN','WI','WY','WO') THEN true ");
      lSql.append(" WHEN a.ctgrygroup='book' THEN true ");
      lSql.append(" ELSE false END ");
    }
    lSql.append("AND a.ctgrygroup<>'eyelashgarage' ");
    for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
      lSql.append("AND b.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
    }
    lSql.append("ORDER BY disporder,abbvctgryname");
    
    String lastGroup = "";
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      
      // EDBTG005-00 kamata mod start
      while (lResultSet.next()) {
        if (!lastGroup.equals(lResultSet.getString("ctgrygroup"))) {
          if ("hair".equals(lResultSet.getString("ctgrygroup"))) {
            val = new SINameValueForSP(SIConfig.ctgryGroupName[SIConfig.CTGRYGROUP_HAIR],"/,hair", lResultSet.getString("cmdtyamount"));
            res.add(val);
          } else if ("haircosme".equals(lResultSet.getString("ctgrygroup"))) {
            val = new SINameValueForSP(SIConfig.ctgryGroupName[SIConfig.CTGRYGROUP_HAIRCOSME],"/,haircosme", lResultSet.getString("cmdtyamount"));
            res.add(val);
          } else if ("esthe".equals(lResultSet.getString("ctgrygroup"))) {
            val = new SINameValueForSP(SIConfig.ctgryGroupName[SIConfig.CTGRYGROUP_ESTHE],"/,esthe", lResultSet.getString("cmdtyamount"));
            res.add(val);
          } else if ("bed".equals(lResultSet.getString("ctgrygroup"))) {
            val = new SINameValueForSP(SIConfig.ctgryGroupName[SIConfig.CTGRYGROUP_BED],"/,bed", lResultSet.getString("cmdtyamount"));
            res.add(val);
          } else if ("cosme".equals(lResultSet.getString("ctgrygroup"))) {
            val = new SINameValueForSP(SIConfig.ctgryGroupName[SIConfig.CTGRYGROUP_COSME],"/,cosme", lResultSet.getString("cmdtyamount"));
            res.add(val);
          } else if ("nail".equals(lResultSet.getString("ctgrygroup"))) {
            val = new SINameValueForSP(SIConfig.ctgryGroupName[SIConfig.CTGRYGROUP_NAIL],"/,nail", lResultSet.getString("cmdtyamount"));
            res.add(val);
          } else if ("book".equals(lResultSet.getString("ctgrygroup"))) {
            val = new SINameValueForSP(SIConfig.ctgryGroupName[SIConfig.CTGRYGROUP_BOOK],"/,book", lResultSet.getString("cmdtyamount"));
            res.add(val);
          } else if ("eyelash".equals(lResultSet.getString("ctgrygroup"))) {
            val = new SINameValueForSP(SIConfig.ctgryGroupName[SIConfig.CTGRYGROUP_EYELASH],"/,eyelash", lResultSet.getString("cmdtyamount"));
            res.add(val);
          }
        }
        if ("0".equals(lResultSet.getString("check"))||"999".equals(lResultSet.getString("check"))){
          val = new SINameValueForSP(lResultSet.getString("ctgryname"),"/3,"+lResultSet.getString("ctgrygroup")+","+lResultSet.getString("ctgrycode"), lResultSet.getString("cmdtyamount"));
          res.add(val);
        } else {
          val = new SINameValueForSP(lResultSet.getString("ctgryname"),"/2,"+lResultSet.getString("ctgrygroup")+","+lResultSet.getString("ctgrycode"), lResultSet.getString("cmdtyamount"));
          res.add(val);
        }
        /*
        if (SIUtil.isNull(lResultSet.getString("check"))) {
          val = new SINameValueForSP(lResultSet.getString("ctgryname"),"/2,"+lResultSet.getString("ctgrygroup")+","+lResultSet.getString("ctgrycode"), lResultSet.getString("cmdtyamount"));
          res.add(val);
        } else if ("0".equals(lResultSet.getString("check"))){
          val = new SINameValueForSP(lResultSet.getString("ctgryname"),"/3,"+lResultSet.getString("ctgrygroup")+","+lResultSet.getString("ctgrycode"), lResultSet.getString("cmdtyamount"));
          res.add(val);
        } else if ("999".equals(lResultSet.getString("check"))){
          val = new SINameValueForSP(lResultSet.getString("ctgryname"),"/4,"+lResultSet.getString("ctgrygroup")+","+lResultSet.getString("ctgrycode"), lResultSet.getString("cmdtyamount"));
          res.add(val);
        } else if (SIUtil.isNotNull(lResultSet.getString("check"))) {
           val = new SINameValueForSP(lResultSet.getString("ctgryname"),"/9,"+lResultSet.getString("ctgrygroup")+","+lResultSet.getString("ctgrycode"), lResultSet.getString("cmdtyamount"));
           res.add(val);
        } else {
          val = new SINameValueForSP(lResultSet.getString("ctgryname"),"/5,"+lResultSet.getString("ctgrygroup")+","+lResultSet.getString("ctgrycode"), lResultSet.getString("cmdtyamount"));
          res.add(val);
        }
        */
        lastGroup = lResultSet.getString("ctgrygroup");
      }
      // EDBTG005-00 kamata mod end
    } catch (Exception e) {
      e.printStackTrace();
      res = new ArrayList();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return res;
  }
  // EDBTG005-00 nagayoshi add end
  
  // EDBTG005-00 kamata add start
  public static String getCtgryTopCnt(Connection lConnection, String ctgrygroup) {
    StringBuffer lSql = new StringBuffer();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    String ctgryTopCnt = "";
    lSql.append("SELECT sum(b.cmdtyamount) as cnt ");
    lSql.append("FROM ctgrygrouptbl a ");
    lSql.append("INNER JOIN ctgrymtbl b on a.ctgrycode = b.ctgrycode ");
    lSql.append("WHERE ctgrygroup = " + SIDBUtil.SQL2Str(ctgrygroup));
    for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
      lSql.append("AND b.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
    }
    try {
        lStatement = lConnection.createStatement();
        lResultSet = lStatement.executeQuery(lSql.toString());
        
        lResultSet.next();
        ctgryTopCnt = String.valueOf(lResultSet.getInt("cnt"));
        
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return ctgryTopCnt;
            
  }
  // EDBTG005-00 kamata add end
  
  public static String getCanonicalURL(Connection lConnection,String individualCode,String webshopSite){
    StringBuffer res = new StringBuffer();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT g.ctgrygroup,u.cmdtycode,u.individualcode,c.ctgrycode ");
    sql.append(", CASE WHEN g.ctgrygroup=h1.ctgrygroup THEN 1000 ELSE 0 END ");
    sql.append("+ CASE WHEN substring(u.individualcode,0,2) = CASE WHEN h2.initial IS NULL THEN substring(c.ctgrycode,0,2) ELSE h2.initial END THEN 100 ELSE 0 END ");
    sql.append("+ c.level*10 + CASE WHEN substring(u.individualcode,0,3) = c.ctgrycode THEN 1 ELSE 0 END AS rank ");
    sql.append("FROM ctgrygrouptbl g,cmdtyctgrymtbl t,cmdtyunittbl u,ctgryhometbl h1,ctgrymtbl cc,ctgryvw c ");
    sql.append("LEFT OUTER JOIN ctgryhometbl h2 ON c.ctgrycode=h2.ctgrycode ");
    sql.append("WHERE u.cmdtycode=t.cmdtycode AND t.ctgrycode=cc.ctgrycode ");
    sql.append("AND (cc.ctgrycode=c.ctgrycode OR cc.parentctgrycode=c.ctgrycode) ");
    sql.append("AND (c.ctgrycode=g.ctgrycode OR c.parentctgrycode=g.ctgrycode) ");
    sql.append("AND substring(u.individualcode,0,2)=h1.initial ");
    for (int i=0;i<SIConfig.INVISIBLECTGRYS.length;i++){
      sql.append("AND c.ctgrycode<>").append(SIDBUtil.SQL2Str(SIConfig.INVISIBLECTGRYS[i]," "));
    }
    sql.append("AND u.individualcode=").append(SIDBUtil.SQL2Str(individualCode, " "));
    sql.append("ORDER BY rank DESC,c.ctgrycode LIMIT 1");
    try{
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(sql.toString());
      if (lResultSet.next()) {
        res.append("http://www.beautygarage.jp").append(webshopSite);
        res.append("/").append(lResultSet.getString("ctgryGroup")).append("_cmdty");
        res.append("/").append(lResultSet.getString("cmdtyCode"));
        res.append("/").append(lResultSet.getString("individualCode"));
        res.append("/").append(lResultSet.getString("ctgryCode"));
      }
    }catch (Exception e){
      e.printStackTrace();
    }finally{
      SIDBUtil.close(lResultSet, lStatement);
    }
    return res.toString();
  }
  
  public static String getParentCtgry(Connection lConnection,String ctgryCode, String parentCode,String ctgryGroup){
    StringBuffer res = new StringBuffer();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT c.ctgrycode,c.ctgryname FROM ctgrymtbl c,ctgrygrouptbl g ");
    sql.append("WHERE c.ctgrycode=g.ctgrycode AND g.ctgrygroup=").append(SIDBUtil.SQL2Str(ctgryGroup," "));
    sql.append("ORDER BY g.disporder");
    
    if (ctgryCode.equals("/")){
      res.append("<li><span>すべて</span></li>\r\n");
    } else {
      res.append("<li><a href=\"javascript:changeCtgry('/')\">すべて</a></li>\r\n");
    }
    try{
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(sql.toString());
      while (lResultSet.next()) {
        if (ctgryCode.equals(lResultSet.getString("ctgryCode"))||parentCode.equals(lResultSet.getString("ctgryCode"))) {
          res.append("<li><span>").append(lResultSet.getString("ctgryName")).append("</span></li>\r\n");
        } else {
          res.append("<li><a href=\"javascript:changeCtgry('").append(lResultSet.getString("ctgryCode")).append("')\">").append(lResultSet.getString("ctgryName")).append("</a></li>\r\n");
        }
      }
    }catch (Exception e){
      e.printStackTrace();
    }finally{
      SIDBUtil.close(lResultSet, lStatement);
    }
    return res.toString();
  }
}