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

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import javax.servlet.http.HttpServletRequest;

import jp.co.sint.basic.SIBasic;
import jp.co.sint.basic.SIMaker;
import jp.co.sint.basic.SINameValue;
import jp.co.sint.config.SIConfig;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.tools.SICheckDataConf;
import jp.co.sint.tools.SICheckValid;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIURLParameter;
import jp.co.sint.tools.SIUtil;
import jp.co.sint.tools.SIStringUtil;

import org.apache.log4j.Category;

public class UIRegMaker extends SIBasic {
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  // ブランドコード
  private String regMakerCode = "";
  
  // ブランド名
  private String regMakerName = "";
  
  // メモ
  private String regDescription = "";
  
  // 電話番号
  private String regTel = "";
  
  // URL
  private String regUrl = "";
  
  // 登録日時
  private String regInitDateTime = "";
  
  // 更新日時
  private String regUpdateDateTime = "";
  
  // 検索用ブランドコード
  private String regMakerCodeReference = "";
  
  // フロント表示フラグ
  private String regFrontDispFlg = "0";
  
  // ソート順
  private String regSortOrder = "";
  
  // 要契約フラグ
  private String regContractFlg = "0";
  
  // ブランド名別名１
  private String regOtherName1 = "";
  
  // ブランド名別名２
  private String regOtherName2 = "";
  
  // 問い合わせ先部署名
  private String regReferencePost = "";
  
  // 問い合わせ先電話番号表記
  private String regReferenceNumber = "";
  
  // 問い合わせ先メールアドレス
  private String regReferenceMail = "";
  
  private boolean closeWindow = false;
  
  // コンストラクタ
  public UIRegMaker() {}
  
  public UIRegMaker(String makerCode) {
    setRegMakerCode(makerCode);
  }
  
  // getter of ブランドコード
  public String getRegMakerCode() {
    return this.regMakerCode;
  }
  
  // getter of ブランド名
  public String getRegMakerName() {
    return this.regMakerName;
  }
  
  // getter of メモ
  public String getRegDescription() {
    return this.regDescription;
  }
  
  // getter of 電話番号
  public String getRegTel() {
    return this.regTel;
  }
  
  // getter of URL
  public String getRegUrl() {
    return this.regUrl;
  }
  
  // getter of 登録日時
  public String getRegInitDateTime() {
    return this.regInitDateTime;
  }
  
  // getter of 更新日時
  public String getRegUpdateDateTime() {
    return this.regUpdateDateTime;
  }
  
  // getter of 検索用ブランドコード
  public String getRegMakerCodeReference() {
    return this.regMakerCodeReference;
  }
  
  // getter of フロント表示フラグ
  public String getRegFrontDispFlg() {
    return this.regFrontDispFlg;
  }
  
  // getter of ソート順
  public String getRegSortOrder() {
    return this.regSortOrder;
  }
  
  // getter of 要契約フラグ
  public String getRegContractFlg() {
    return this.regContractFlg;
  }
  
  // getter of ブランド名別名１
  public String getRegOtherName1() {
    return this.regOtherName1;
  }
  
  // getter of ブランド名別名２
  public String getRegOtherName2() {
    return this.regOtherName2;
  }
  
  public String getRegReferencePost() {
    return regReferencePost;
  }
  
  public String getRegReferenceNumber() {
    return regReferenceNumber;
  }
  
  public String getRegReferenceMail() {
    return regReferenceMail;
  }
  
  // setter of ブランドコード
  public void setRegMakerCode(String lRegMakerCode) {
    if (SIUtil.isNull(lRegMakerCode)) lRegMakerCode = "";
    this.regMakerCode = SIUtil.changeTo(lRegMakerCode.trim(), this.encode);
  }
  
  // setter of ブランド名
  public void setRegMakerName(String lRegMakerName) {
    if (SIUtil.isNull(lRegMakerName)) lRegMakerName = "";
    this.regMakerName = SIUtil.changeTo(lRegMakerName.trim(), this.encode);
  }
  
  // setter of メモ
  public void setRegDescription(String lRegDescription) {
    if (SIUtil.isNull(lRegDescription)) lRegDescription = "";
    this.regDescription = SIUtil.changeTo(lRegDescription.trim(), this.encode);
  }
  
  // setter of 電話番号
  public void setRegTel(String lRegTel) {
    if (SIUtil.isNull(lRegTel)) lRegTel = "";
    this.regTel = SIUtil.changeTo(lRegTel.trim(), this.encode);
  }
  
  // setter of URL
  public void setRegUrl(String lRegUrl) {
    if (SIUtil.isNull(lRegUrl)) lRegUrl = "";
    this.regUrl = SIUtil.changeTo(lRegUrl.trim(), this.encode);
  }
  
  // setter of 登録日時
  public void setRegInitDateTime(String lRegInitDateTime) {
    if (SIUtil.isNull(lRegInitDateTime)) lRegInitDateTime = "";
    this.regInitDateTime = SIUtil.changeTo(lRegInitDateTime.trim(), this.encode);
  }
  
  // setter of 更新日時
  public void setRegUpdateDateTime(String lRegUpdateDateTime) {
    if (SIUtil.isNull(lRegUpdateDateTime)) lRegUpdateDateTime = "";
    this.regUpdateDateTime = SIUtil.changeTo(lRegUpdateDateTime.trim(), this.encode);
  }
  
  // setter of 検索用ブランドコード
  public void setRegMakerCodeReference(String lRegMakerCodeReference) {
    if (SIUtil.isNull(lRegMakerCodeReference)) lRegMakerCodeReference = "";
    this.regMakerCodeReference = SIUtil.changeTo(lRegMakerCodeReference.trim(), this.encode);
  }
  
  // setter of フロント表示フラグ
  public void setRegFrontDispFlg(String lFrontDispFlg) {
    if (SIUtil.isNull(lFrontDispFlg)) lFrontDispFlg = "0";
    this.regFrontDispFlg = SIUtil.changeTo(lFrontDispFlg.trim(), this.encode);
  }
  
  // setter of ソート順
  public void setRegSortOrder(String lSortOrder) {
    if (SIUtil.isNull(lSortOrder)) lSortOrder = "";
    this.regSortOrder = SIUtil.changeTo(lSortOrder.trim(), this.encode);
  }
  
  // setter of 要契約フラグ
  public void setRegContractFlg(String lContractFlg) {
    if (SIUtil.isNull(lContractFlg)) lContractFlg = "0";
    this.regContractFlg = SIUtil.changeTo(lContractFlg.trim(), this.encode);
  }
  
  // setter of ブランド名別名１
  public void setRegOtherName1(String lRegOtherName1) {
    if (SIUtil.isNull(lRegOtherName1)) lRegOtherName1 = "";
    this.regOtherName1 = SIUtil.changeTo(lRegOtherName1.trim(), this.encode);
  }
  
  // setter of ブランド名別名２
  public void setRegOtherName2(String lRegOtherName2) {
    if (SIUtil.isNull(lRegOtherName2)) lRegOtherName2 = "";
    this.regOtherName2 = SIUtil.changeTo(lRegOtherName2.trim(), this.encode);
  }
  
  public void setRegReferencePost(String regReferencePost) {
    if (SIUtil.isNull(regReferencePost)) regReferencePost = "";
    this.regReferencePost = regReferencePost;
  }
  
  public void setRegReferenceNumber(String regReferenceNumber) {
    if (SIUtil.isNull(regReferenceNumber)) regReferenceNumber = "";
    this.regReferenceNumber = regReferenceNumber;
  }
  
  public void setRegReferenceMail(String regReferenceMail) {
    if (SIUtil.isNull(regReferenceMail)) regReferenceMail = "";
    this.regReferenceMail = regReferenceMail;
  }
  
  public boolean getCloseWindow() {
    return closeWindow;
  }
  
  public void setCloseWindow(boolean closeWindow) {
    this.closeWindow = closeWindow;
  }
  
  /**
   * <b>init</b> 入力したデータから、このbeansを設定します。
   * 
   * @param HttpServletRequest
   * @param SIURLParameter
   * @return なし
   * @throws なし
   */
  
  public void init(HttpServletRequest lRequest, SIURLParameter lUrlParam) {
    super.init(lRequest, lUrlParam);
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    this.setRegMakerCode((String) lUrlParam.getParam("makerCode"));// ブランドコード
    this.setRegMakerName((String) lUrlParam.getParam("makerName"));// ブランド名
    this.setRegOtherName1((String) lUrlParam.getParam("otherName1"));// ブランド名別名１
    this.setRegOtherName2((String) lUrlParam.getParam("otherName2"));// ブランド名別名２
    this.setRegDescription((String) lUrlParam.getParam("description"));// メモ
    this.setRegTel((String) lUrlParam.getParam("tel"));// 電話番号
    this.setRegUrl((String) lUrlParam.getParam("url"));// URL
    this.setRegFrontDispFlg((String) lUrlParam.getParam("frontDispFlg"));// フロント表示フラグ
    this.setRegContractFlg((String) lUrlParam.getParam("contractFlg"));// 要契約フラグ
    this.setRegSortOrder((String) lUrlParam.getParam("sortOrder"));// ソート順
    this.setRegReferencePost((String) lUrlParam.getParam("referencePost"));// 問い合わせ先部署
    this.setRegReferenceNumber((String) lUrlParam.getParam("referenceNumber"));// 問い合わせ先電話番号
    this.setRegReferenceMail((String) lUrlParam.getParam("referenceMail"));// 問い合わせ先メールアドレス
  }
  
  public void initModify(HttpServletRequest lRequest, SIURLParameter lUrlParam) {
    super.init(lRequest, lUrlParam);
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    this.setRegMakerCode((String) lUrlParam.getParam("makerCodeTxt"));// ブランドコード
    this.setRegFrontDispFlg((String) lUrlParam.getParam("frontDisp"));// フロント表示フラグ
  }
  
  /**
   * <b>validate</b> 入力したデータをチェックします。 不正なデータがある場合、エラーをオブジェクトに格納します。 そのオブジェクトは、エラーメッセージとして画面に表示されます。
   * 
   * @param HttpServletRequest
   * @param Connection
   * @return true:エラーがない false:エラーが１つ以上ある
   * @throws なし
   */
  
  public boolean validate(HttpServletRequest lRequest, Connection lConnection) {
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    SICustomErrors errors = new SICustomErrors();
    
    SICheckValid.checkValid(errors, "ブランドコード", this.getRegMakerCode(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE);
    SICheckValid.checkValid(errors, "ブランドコード", this.getRegMakerCode(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 8);
    
    if (SIConfig.SIEDIT_MODE_INSERT.equals(this.editModeTxt)) {
      SICheckValid.checkDuplicate(errors, lConnection, "ブランドコード", "SELECT makercode FROM makertbl WHERE makercode="+SIDBUtil.SQL2Str(regMakerCode));
    }
    
    SICheckValid.checkValid(errors, "ブランド名", this.getRegMakerName(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
    SICheckValid.checkValid(errors, "ブランド名", this.getRegMakerName(), SICheckDataConf.SICHECK_DATA_CMDTYNAME_TYPE);
    SICheckValid.checkValid(errors, "ブランド名", this.getRegMakerName(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 128);
    
    SICheckValid.checkValid(errors, "ブランド名別名１", this.getRegOtherName1(), SICheckDataConf.SICHECK_DATA_CMDTYNAME_TYPE);
    SICheckValid.checkValid(errors, "ブランド名別名２", this.getRegOtherName2(), SICheckDataConf.SICHECK_DATA_CMDTYNAME_TYPE);
    
    SICheckValid.checkValid(errors, "電話番号", this.getRegTel(), SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
    SICheckValid.checkValid(errors, "電話番号", this.getRegTel(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_TYPE, 9, 16);
    
    SICheckValid.checkValid(errors, "URL", this.getRegUrl(), SICheckDataConf.SICHECK_DATA_URL_TYPE, 128);
    
    SICheckValid.checkValid(errors, "メモ欄", this.getRegDescription(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 1024);
    
    SICheckValid.checkValid(errors, "ソート順", this.getRegSortOrder(), SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
    SICheckValid.checkValid(errors, "ソート順", this.getRegSortOrder(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_TYPE, 3);
    
    if (!"0".equals(this.getRegContractFlg())) {
      SICheckValid.checkValid(errors, "問合せ先部署", this.getRegReferencePost(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "問合せ先番号", this.getRegReferenceNumber(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "問合せ先番号", this.getRegReferenceNumber(), SICheckDataConf.SICHECK_DATA_TEL_TYPE);
      SICheckValid.checkValid(errors, "問合せ先メール", this.getRegReferenceMail(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "問合せ先メール", this.getRegReferenceMail(), SICheckDataConf.SICHECK_DATA_MAIL_TYPE);
    }
    
    if (!errors.isEmpty()) lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
    return errors.isEmpty();
  }
  
  /**
   * <b>getCollection1</b> 条件に合ったレコードを検索して、結果のコレクションを作成して、戻します。
   * 
   * @param Connection
   * @return Collection
   */
  public Collection getCollection1(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIMaker lMaker = new SIMaker();
    
    Collection lResultColl = new ArrayList();
    StringBuffer lSqlBuf = new StringBuffer();
    
    lSqlBuf.append("SELECT a.*, CASE WHEN b.makercode IS NULL THEN 0 ELSE 1 END AS CmdtyCount ");
    lSqlBuf.append("FROM MakerTbl a ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT makercode FROM cmdtymtbl GROUP BY makercode) b ");
    lSqlBuf.append("ON a.makercode=b.makercode ");
    
    // 昇順に並べ替え
    lSqlBuf.append(" ORDER BY MakerCode DESC");
    
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      while (lResultSet.next()) {
        lMaker = new SIMaker();
        lMaker.setEncode(SIConfig.SIENCODE_NONE);
        lMaker.setMakerCode(lResultSet.getString("MakerCode"));// ブランドコード
        lMaker.setMakerName(lResultSet.getString("MakerName"));// ブランド名
        lMaker.setDescription(lResultSet.getString("Memo"));// メモ
        lMaker.setTel(lResultSet.getString("Tel"));// 電話番号
        lMaker.setUrl(lResultSet.getString("Url"));// URL
        lMaker.setInitDateTime(lResultSet.getString("InitDateTime"));// 登録日時
        lMaker.setUpdateDateTime(lResultSet.getString("UpdateDateTime"));// 更新日時
        lMaker.setCmdtyCount(lResultSet.getString("CmdtyCount"));// 紐付き商品フラグ
        lMaker.setFrontDispFlg(lResultSet.getString("EnabledFlg"));// フロント表示フラグ
        lMaker.setContractFlg(lResultSet.getString("ContractFlg"));// 要契約フラグ
        lMaker.setSortOrder(lResultSet.getString("SortOrder"));//ソート順
        lMaker.setReferencePost(lResultSet.getString("referencepost"));// 問い合わせ先部署
        lMaker.setReferenceNumber(lResultSet.getString("referencenumber"));// 問い合わせ先電話番号
        lMaker.setReferenceMail(lResultSet.getString("referencemail"));// 問い合わせ先メールアドレス
        lResultColl.add(lMaker);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      SIDBUtil.close(lStatement, lResultSet);
    }
    return lResultColl;
  }
  
  /**
   * <b>getCollection2</b> 条件に合ったレコードを検索して、結果のbeanを作成して、戻します。
   * 
   * @param Connection
   * @param String
   * @return SIMaker
   */
  public static SIMaker getCollection2(Connection lConnection, String lMakerCode) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIMaker lMaker = new SIMaker();
    
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append("SELECT * FROM MakerTbl ");
    lSqlBuf.append("WHERE MakerCode=").append(SIDBUtil.SQL2Str(lMakerCode," "));
    lSqlBuf.append("ORDER BY MakerCode DESC");
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      if (lResultSet.next()) {
        lMaker = new SIMaker();
        lMaker.setEncode(SIConfig.SIENCODE_NONE);
        lMaker.setMakerCode(lResultSet.getString("MakerCode"));// ブランドコード
        lMaker.setMakerName(lResultSet.getString("MakerName"));// ブランド名
        lMaker.setDescription(lResultSet.getString("Memo"));// メモ
        lMaker.setTel(lResultSet.getString("Tel"));// 電話番号
        lMaker.setUrl(lResultSet.getString("Url"));// URL
        lMaker.setFrontDispFlg(lResultSet.getString("EnabledFlg"));// フロント表示フラグ
        lMaker.setContractFlg(lResultSet.getString("ContractFlg"));// 要契約フラグ
        lMaker.setSortOrder(lResultSet.getString("SortOrder"));//ソート順
        lMaker.setReferencePost(lResultSet.getString("referencepost"));// 問い合わせ先部署
        lMaker.setReferenceNumber(lResultSet.getString("referencenumber"));// 問い合わせ先電話番号
        lMaker.setReferenceMail(lResultSet.getString("referencemail"));// 問い合わせ先メールアドレス
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      SIDBUtil.close(lStatement, lResultSet);
    }
    return lMaker;
  }
  
  /**
   * <b>getCollectionList</b> 検索画面用Collection
   * 
   * @param Connection
   * @param String
   * @return Collection
   */
  public Collection getCollectionList(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIMaker lMaker = new SIMaker();
    Collection lResultColl = new ArrayList();
    
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append("SELECT * FROM MakerTbl ");
    if (SIUtil.isNotNull(this.getRegMakerCode()) || SIUtil.isNotNull(this.getRegMakerName())) {
      lSqlBuf.append("WHERE ");
      if (SIUtil.isNotNull(this.getRegMakerCode())) {
        lSqlBuf.append("makercode like '%" + this.getRegMakerCode() + "%'");
        if (SIUtil.isNotNull(this.getRegMakerName())) {
          lSqlBuf.append(" AND ");
        }
      }
      if (SIUtil.isNotNull(this.getRegMakerName())) {
        lSqlBuf.append("makername like '%" + this.getRegMakerName() + "%'");
      }
    }
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      while (lResultSet.next()) {
        lMaker = new SIMaker();
        lMaker.setEncode(SIConfig.SIENCODE_NONE);
        lMaker.setMakerCode(lResultSet.getString("MakerCode"));// ブランドコード
        lMaker.setMakerName(lResultSet.getString("MakerName"));// ブランド名
        lMaker.setDescription(lResultSet.getString("Memo"));// メモ
        lMaker.setTel(lResultSet.getString("Tel"));// 電話番号
        lMaker.setUrl(lResultSet.getString("Url"));// URL
        lResultColl.add(lMaker);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      SIDBUtil.close(lStatement, lResultSet);
    }
    return lResultColl;
  }
  
  /**
   * <b>validateList</b> 入力したデータをチェックします。 不正なデータがある場合、エラーをオブジェクトに格納します。 そのオブジェクトは、エラーメッセージとして画面に表示されます。
   * 
   * @param HttpServletRequest
   * @param Connection
   * @return true:エラーがない false:エラーが１つ以上ある
   * @throws なし
   */
  
  public boolean validateList(HttpServletRequest lRequest, Connection lConnection) {
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    SICustomErrors errors = new SICustomErrors();
    
    SICheckValid.checkValid(errors, "ブランドコード", this.getRegMakerCode(), SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE);
    SICheckValid.checkValid(errors, "ブランドコード", this.getRegMakerCode(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 8);
    SICheckValid.checkValid(errors, "ブランド名", this.getRegMakerName(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 128);
    if (!errors.isEmpty()) lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
    return errors.isEmpty();
  }
  
  public static Collection getSINameCollection(Connection conn) {
    return getSINameCollection(conn, true);
  }
  
  public static Collection getSINameCollection(Connection conn, boolean flag) {
    Collection coll = new ArrayList();
    Statement statement = null;
    ResultSet resultSet = null;
    StringBuffer sqlStatement = new StringBuffer();
    SINameValue charge = new SINameValue();
    
    charge.setValue("");
    if (flag) charge.setName("選択してください");
    else charge.setName("");
    coll.add(charge);
    sqlStatement = new StringBuffer();
    sqlStatement.append("SELECT * FROM makertbl ORDER BY makername");
    try {
      statement = conn.createStatement();
      resultSet = statement.executeQuery(sqlStatement.toString());
      while (resultSet.next()) {
        charge = new SINameValue();
        charge.setValue(resultSet.getString("makercode"));
        charge.setName(SIStringUtil.limitLength(SIHTMLUtil.HTMLEncode(resultSet.getString("makername")), 15, "..."));
        coll.add(charge);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    
    return coll;
  }
  
  public static Collection getFrontCollection(Connection conn) {
    Collection coll = new ArrayList();
    Statement statement = null;
    ResultSet resultSet = null;
    SINameValue charge = new SINameValue();
    
    charge.setValue("");
    charge.setName("選択してください");
    coll.add(charge);
    StringBuffer sqlStr = new StringBuffer();
    sqlStr.append("SELECT CASE WHEN makercode = '1230' THEN 'ポール･シェリーM-products' ");
    sqlStr.append("WHEN makercode = '1001' THEN 'その他' ELSE makername END AS makername,makercode ");
    sqlStr.append("FROM makertbl ");
    sqlStr.append("WHERE makercode != '1231' AND makercode != '1232' AND enabledflg = '1' ");
    sqlStr.append("ORDER BY CASE makercode WHEN '1001' THEN 1 ELSE 0 END,makername");
    try {
      statement = conn.createStatement();
      resultSet = statement.executeQuery(sqlStr.toString());
      while (resultSet.next()) {
        charge = new SINameValue();
        charge.setValue(resultSet.getString("makercode"));
        charge.setName(SIStringUtil.limitLength(SIHTMLUtil.HTMLEncode(resultSet.getString("makername")), 15, "..."));
        coll.add(charge);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return coll;
  }
  
  public static Collection getFrontSearchCollection(Connection conn,String ctgry) {
    return getFrontSearchCollection(conn,ctgry,SIConfig.CTGRYGROUP_INX_ALL);
  }
  
  public static Collection getFrontSearchCollectionHair(Connection conn) {
    return getFrontSearchCollection(conn,"",SIConfig.CTGRYGROUP_INX_HAIR);
  }
  
  public static Collection getFrontSearchCollectionHairCosme(Connection conn) {
    return getFrontSearchCollection(conn,"",SIConfig.CTGRYGROUP_INX_HAIRCOSME);
  }
  
  public static Collection getFrontSearchCollectionEsthe(Connection conn) {
    return getFrontSearchCollection(conn,"",SIConfig.CTGRYGROUP_INX_ESTHE);
  }
  
  public static Collection getFrontSearchCollectionBed(Connection conn) {
    return getFrontSearchCollection(conn,"",SIConfig.CTGRYGROUP_INX_BED);
  }
  
  public static Collection getFrontSearchCollectionCosme(Connection conn) {
    return getFrontSearchCollection(conn,"",SIConfig.CTGRYGROUP_INX_COSME);
  }
  
  public static Collection getFrontSearchCollectionNail(Connection conn) {
    return getFrontSearchCollection(conn,"",SIConfig.CTGRYGROUP_INX_NAIL);
  }
  
  public static Collection getFrontSearchCollectionBook(Connection conn) {
    return getFrontSearchCollection(conn,"",SIConfig.CTGRYGROUP_INX_BOOK);
  }
  
  public static Collection getFrontSearchCollectionEyeLash(Connection conn) {
    return getFrontSearchCollection(conn,"",SIConfig.CTGRYGROUP_INX_EYELASH);
  }
  
  public static Collection getFrontSearchCollection(Connection conn,String ctgry,int group) {
    Collection coll = new ArrayList();
    Statement statement = null;
    ResultSet resultSet = null;
    SINameValue maker = new SINameValue();
    
    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";
    
    maker.setValue("");
    maker.setName("ブランド選択");
    coll.add(maker);
    StringBuffer sqlStr = new StringBuffer();
    sqlStr.append("SELECT CASE WHEN makercode = '1230' THEN 'ポール･シェリーM-products' ");
    sqlStr.append("WHEN makercode = '1001' THEN 'その他' ELSE makername END AS makername,makercode ");
    sqlStr.append("FROM makertbl ");
    sqlStr.append("WHERE makercode != '1231' AND makercode != '1232' AND enabledflg = '1' ");
    if(SIUtil.isNotNull(ctgryGroup)&&(SIUtil.isNull(ctgry)||SIConfig.SICATEGORY_ROOT_CODE.equals(ctgry))){
      sqlStr.append("AND makercode IN (SELECT DISTINCT a.makercode ");
      sqlStr.append(" FROM cmdtyunittbl a,cmdtyctgrymtbl b,ctgryvw c,ctgrygrouptbl d ");
      sqlStr.append(" WHERE a.cmdtycode=b.cmdtycode AND b.ctgrycode=c.ctgrycode ");
      sqlStr.append(" AND (c.branch LIKE '%~'||d.ctgrycode||'~%' OR c.branch LIKE '%~'||d.ctgrycode) ");
      sqlStr.append(" AND a.frontdispflg='1' AND d.ctgrygroup=").append(SIDBUtil.SQL2Str(ctgryGroup,") "));
    }else{
      sqlStr.append("AND makercode IN (SELECT DISTINCT a.makercode ");
      sqlStr.append(" FROM cmdtyunittbl a,cmdtyctgrymtbl b,ctgryvw c ");
      sqlStr.append(" WHERE a.cmdtycode=b.cmdtycode AND b.ctgrycode=c.ctgrycode ");
      if (SIUtil.isNotNull(ctgry)&&!SIConfig.SICATEGORY_ROOT_CODE.equals(ctgry)) {
        sqlStr.append(" AND (c.branch LIKE '%~").append(ctgry).append("~%' OR c.branch LIKE '%~").append(ctgry).append("') ");
      }
      sqlStr.append(" AND a.frontdispflg='1') ");
    }
    sqlStr.append("ORDER BY CASE makercode WHEN '1001' THEN 1 ELSE 0 END,makername");
    try {
      statement = conn.createStatement();
      resultSet = statement.executeQuery(sqlStr.toString());
      while (resultSet.next()) {
        maker = new SINameValue();
        maker.setValue(resultSet.getString("makercode"));
        maker.setName(SIStringUtil.limitLength(SIHTMLUtil.HTMLEncode(resultSet.getString("makername")), 30, "..."));
        coll.add(maker);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return coll;
  }
  
  public static Collection getFrontSearchCollection_Used(Connection conn) {
    Collection coll = new ArrayList();
    Statement statement = null;
    ResultSet resultSet = null;
    SINameValue maker = new SINameValue();
    
    maker.setValue("");
    maker.setName("ブランド選択");
    coll.add(maker);
    StringBuffer sqlStr = new StringBuffer();
    sqlStr.append("SELECT CASE WHEN makercode = '1230' THEN 'ポール･シェリーM-products' ");
    sqlStr.append("WHEN makercode = '1001' THEN 'その他' ELSE makername END AS makername,makercode ");
    sqlStr.append("FROM makertbl ");
    sqlStr.append("WHERE makercode != '1231' AND makercode != '1232' AND enabledflg = '1' ");
    sqlStr.append("AND makercode IN (SELECT DISTINCT a.makercode ");
    sqlStr.append(" FROM cmdtyunittbl a,cmdtyctgrymtbl b,ctgryvw c ");
    sqlStr.append(" WHERE a.cmdtycode=b.cmdtycode AND b.ctgrycode=c.ctgrycode ");
    sqlStr.append(" AND a.usednewflg IN ('0','3') AND a.frontdispflg='1' AND a.amount!=0) ");
    
    sqlStr.append("ORDER BY CASE makercode WHEN '1001' THEN 1 ELSE 0 END,makername");
    try {
      statement = conn.createStatement();
      resultSet = statement.executeQuery(sqlStr.toString());
      while (resultSet.next()) {
        maker = new SINameValue();
        maker.setValue(resultSet.getString("makercode"));
        maker.setName(SIStringUtil.limitLength(SIHTMLUtil.HTMLEncode(resultSet.getString("makername")), 30, "..."));
        coll.add(maker);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return coll;
  }
  
  public static Collection getFrontSearchCollection_Ex(Connection conn,String ctgry) {
    Collection coll = new ArrayList();
    Statement statement = null;
    ResultSet resultSet = null;
    SINameValue maker = new SINameValue();
    
    maker.setValue("");
    maker.setName("すべて");
    coll.add(maker);
    StringBuffer sqlStr = new StringBuffer();
    sqlStr.append("SELECT CASE WHEN makercode = '1230' THEN 'ポール･シェリーM-products' ");
    sqlStr.append("WHEN makercode = '1001' THEN 'その他' ELSE makername END AS makername,makercode ");
    sqlStr.append("FROM makertbl ");
    sqlStr.append("WHERE makercode != '1231' AND makercode != '1232' AND enabledflg = '1' ");
    sqlStr.append("AND makercode IN ( ");
    sqlStr.append(" SELECT a.makercode ");
    sqlStr.append(" FROM cmdtyunittbl a,cmdtyctgrymtbl b,ctgryvw c ");
    sqlStr.append(" WHERE a.cmdtycode=b.cmdtycode AND b.ctgrycode=c.ctgrycode ");
    sqlStr.append(" AND (c.branch LIKE '%~").append(ctgry).append("~%' OR c.branch LIKE '%~").append(ctgry).append("') ");
    sqlStr.append(" AND a.frontdispflg='1') ");
    sqlStr.append("ORDER BY CASE makercode WHEN '1001' THEN 1 ELSE 0 END,sortorder,makername");
    try {
      statement = conn.createStatement();
      resultSet = statement.executeQuery(sqlStr.toString());
      while (resultSet.next()) {
        maker = new SINameValue();
        maker.setValue(resultSet.getString("makercode"));
        maker.setName(SIStringUtil.limitLength(SIHTMLUtil.HTMLEncode(resultSet.getString("makername")), 30, "..."));
        coll.add(maker);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return coll;
  }
  
  public static Collection getFrontSearchCollection_Cosme(Connection conn,String ctgryCode) {
    Collection coll = new ArrayList();
    Statement statement = null;
    ResultSet resultSet = null;
    SINameValue maker = new SINameValue();
    
    maker.setValue("");
    maker.setName("すべて");
    coll.add(maker);
    StringBuffer sqlStr = new StringBuffer();
    sqlStr.append("SELECT CASE WHEN makercode = '1230' THEN 'ポール･シェリーM-products' ");
    sqlStr.append("WHEN makercode = '1001' THEN 'その他' ELSE makername END AS makername,makercode ");
    sqlStr.append("FROM makertbl ");
    sqlStr.append("WHERE makercode != '1231' AND makercode != '1232' AND enabledflg = '1' ");
    sqlStr.append("AND makercode IN ( ");
    sqlStr.append(" SELECT a.makercode  ");
    if (SIUtil.isNotNull(ctgryCode)&&!SIConfig.SICATEGORY_ROOT_CODE.equals(ctgryCode)) {
      sqlStr.append(" FROM cmdtyunittbl a,cmdtyctgrymtbl b,ctgryvw c ");
      sqlStr.append(" WHERE a.cmdtycode=b.cmdtycode AND b.ctgrycode=c.ctgrycode ");
      sqlStr.append(" AND (c.branch LIKE '%~").append(ctgryCode).append("~%' OR c.branch LIKE '%~").append(ctgryCode).append("') ");
      sqlStr.append(" AND a.frontdispflg='1' ");
    }else{
      sqlStr.append(" FROM cmdtyunittbl a,cmdtyctgrymtbl b,ctgryvw c,ctgrygrouptbl d ");
      sqlStr.append(" WHERE a.cmdtycode=b.cmdtycode AND b.ctgrycode=c.ctgrycode ");
      sqlStr.append(" AND (c.branch LIKE '%~'||d.ctgrycode||'~%' OR c.branch LIKE '%~'||d.ctgrycode) ");
      sqlStr.append(" AND d.ctgrygroup='cosme' AND a.frontdispflg='1' ");
    }
    sqlStr.append(")");
    sqlStr.append("ORDER BY CASE makercode WHEN '1001' THEN 1 ELSE 0 END,sortorder,makername");
    try {
      statement = conn.createStatement();
      resultSet = statement.executeQuery(sqlStr.toString());
      while (resultSet.next()) {
        maker = new SINameValue();
        maker.setValue(resultSet.getString("makercode"));
        maker.setName(SIStringUtil.limitLength(SIHTMLUtil.HTMLEncode(resultSet.getString("makername")), 30, "..."));
        coll.add(maker);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return coll;
  }
  
  public static Collection getFrontSearchCollection_Nail(Connection conn,String ctgryCode) {
    Collection coll = new ArrayList();
    Statement statement = null;
    ResultSet resultSet = null;
    SINameValue maker = new SINameValue();
    
    maker.setValue("");
    maker.setName("すべて");
    coll.add(maker);
    StringBuffer sqlStr = new StringBuffer();
    sqlStr.append("SELECT CASE WHEN makercode = '1230' THEN 'ポール･シェリーM-products' ");
    sqlStr.append("WHEN makercode = '1001' THEN 'その他' ELSE makername END AS makername,makercode ");
    sqlStr.append("FROM makertbl ");
    sqlStr.append("WHERE makercode != '1231' AND makercode != '1232' AND enabledflg = '1' ");
    sqlStr.append("AND makercode IN ( ");
    sqlStr.append(" SELECT a.makercode  ");
    sqlStr.append(" FROM cmdtyunittbl a,cmdtyctgrymtbl b,ctgryvw c,ctgrygrouptbl d ");
    sqlStr.append(" WHERE a.cmdtycode=b.cmdtycode AND b.ctgrycode=c.ctgrycode ");
    sqlStr.append(" AND (c.branch LIKE '%~'||d.ctgrycode||'~%' OR c.branch LIKE '%~'||d.ctgrycode) ");
    sqlStr.append(" AND d.ctgrygroup='nail' AND a.frontdispflg='1' ");
    if (SIUtil.isNotNull(ctgryCode)&&!SIConfig.SICATEGORY_ROOT_CODE.equals(ctgryCode)) {
      sqlStr.append(" AND d.ctgrycode=").append(SIDBUtil.SQL2Str(ctgryCode," "));
    }
    sqlStr.append(")");
    sqlStr.append("ORDER BY CASE makercode WHEN '1001' THEN 1 ELSE 0 END,sortorder,makername");
    try {
      statement = conn.createStatement();
      resultSet = statement.executeQuery(sqlStr.toString());
      while (resultSet.next()) {
        maker = new SINameValue();
        maker.setValue(resultSet.getString("makercode"));
        maker.setName(SIStringUtil.limitLength(SIHTMLUtil.HTMLEncode(resultSet.getString("makername")), 30, "..."));
        coll.add(maker);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return coll;
  }
  
  public static Collection getFrontSearchCollection_HairCosme(Connection conn,String ctgryCode) {
    Collection coll = new ArrayList();
    Statement statement = null;
    ResultSet resultSet = null;
    SINameValue maker = new SINameValue();
    
    maker.setValue("");
    maker.setName("すべて");
    coll.add(maker);
    StringBuffer sqlStr = new StringBuffer();
    sqlStr.append("SELECT CASE WHEN makercode = '1230' THEN 'ポール･シェリーM-products' ");
    sqlStr.append("WHEN makercode = '1001' THEN 'その他' ELSE makername END AS makername,makercode ");
    sqlStr.append("FROM makertbl ");
    sqlStr.append("WHERE makercode != '1231' AND makercode != '1232' AND enabledflg = '1' ");
    sqlStr.append("AND makercode IN ( ");
    sqlStr.append(" SELECT a.makercode  ");
    sqlStr.append(" FROM cmdtyunittbl a,cmdtyctgrymtbl b,ctgryvw c,ctgrygrouptbl d ");
    sqlStr.append(" WHERE a.cmdtycode=b.cmdtycode AND b.ctgrycode=c.ctgrycode ");
    sqlStr.append(" AND (c.branch LIKE '%~'||d.ctgrycode||'~%' OR c.branch LIKE '%~'||d.ctgrycode) ");
    sqlStr.append(" AND d.ctgrygroup='haircosme' AND a.frontdispflg='1' ");
    if (SIUtil.isNotNull(ctgryCode)&&!SIConfig.SICATEGORY_ROOT_CODE.equals(ctgryCode)) {
      sqlStr.append(" AND d.ctgrycode=").append(SIDBUtil.SQL2Str(ctgryCode," "));
    }
    sqlStr.append(")");
    sqlStr.append("ORDER BY CASE makercode WHEN '1001' THEN 1 ELSE 0 END,sortorder,makername");
    try {
      statement = conn.createStatement();
      resultSet = statement.executeQuery(sqlStr.toString());
      while (resultSet.next()) {
        maker = new SINameValue();
        maker.setValue(resultSet.getString("makercode"));
        maker.setName(SIStringUtil.limitLength(SIHTMLUtil.HTMLEncode(resultSet.getString("makername")), 30, "..."));
        coll.add(maker);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return coll;
  }
  
  public static Collection getFrontTreeCollection_Nail(Connection conn) {
    Collection coll = new ArrayList();
    Statement statement = null;
    ResultSet resultSet = null;
    SINameValue maker = new SINameValue();
    StringBuffer sqlStr = new StringBuffer();
    sqlStr.append("SELECT CASE WHEN makercode = '1230' THEN 'ポール･シェリーM-products' ");
    sqlStr.append("WHEN makercode = '1001' THEN 'その他' ELSE makername END AS makername,makercode ");
    sqlStr.append("FROM makertbl ");
    sqlStr.append("WHERE makercode != '1231' AND makercode != '1232' AND enabledflg = '1' ");
    sqlStr.append("AND makercode IN ( ");
    sqlStr.append(" SELECT a.makercode  ");
    sqlStr.append(" FROM cmdtyunittbl a,cmdtyctgrymtbl b,ctgryvw c,ctgrygrouptbl d ");
    sqlStr.append(" WHERE a.cmdtycode=b.cmdtycode AND b.ctgrycode=c.ctgrycode ");
    sqlStr.append(" AND (c.branch LIKE '%~'||d.ctgrycode||'~%' OR c.branch LIKE '%~'||d.ctgrycode) ");
    sqlStr.append(" AND d.ctgrygroup='nail' AND a.frontdispflg='1' ");
    sqlStr.append(" AND d.ctgrycode NOT LIKE 'L%' AND d.ctgrycode NOT LIKE 'X%' AND d.ctgrycode NOT LIKE 'F%' ) ");
    sqlStr.append("ORDER BY CASE makercode WHEN '1001' THEN 1 ELSE 0 END,sortorder,makername");
    try {
      statement = conn.createStatement();
      resultSet = statement.executeQuery(sqlStr.toString());
      while (resultSet.next()) {
        maker = new SINameValue();
        maker.setValue(resultSet.getString("makercode"));
        maker.setName(SIStringUtil.limitLength(SIHTMLUtil.HTMLEncode(resultSet.getString("makername")), 30, "..."));
        coll.add(maker);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return coll;
  }
  
  public static Collection getFrontTreeCollection_HairCosme(Connection conn) {
    Collection coll = new ArrayList();
    Statement statement = null;
    ResultSet resultSet = null;
    SINameValue maker = new SINameValue();
    StringBuffer sqlStr = new StringBuffer();
    sqlStr.append("SELECT CASE WHEN makercode = '1230' THEN 'ポール･シェリーM-products' ");
    sqlStr.append("WHEN makercode = '1001' THEN 'その他' ELSE makername END AS makername,makercode ");
    sqlStr.append("FROM makertbl ");
    sqlStr.append("WHERE makercode != '1231' AND makercode != '1232' AND enabledflg = '1' ");
    sqlStr.append("AND makercode IN ( ");
    sqlStr.append(" SELECT a.makercode  ");
    sqlStr.append(" FROM cmdtyunittbl a,cmdtyctgrymtbl b,ctgryvw c,ctgrygrouptbl d ");
    sqlStr.append(" WHERE a.cmdtycode=b.cmdtycode AND b.ctgrycode=c.ctgrycode ");
    sqlStr.append(" AND (c.branch LIKE '%~'||d.ctgrycode||'~%' OR c.branch LIKE '%~'||d.ctgrycode) ");
    sqlStr.append(" AND d.ctgrygroup='haircosme' AND a.frontdispflg='1' ");
    sqlStr.append(" AND d.ctgrycode!='LM') ");
    sqlStr.append("ORDER BY CASE makercode WHEN '1001' THEN 1 ELSE 0 END,sortorder,makername");
    try {
      statement = conn.createStatement();
      resultSet = statement.executeQuery(sqlStr.toString());
      while (resultSet.next()) {
        maker = new SINameValue();
        maker.setValue(resultSet.getString("makercode"));
        maker.setName(SIStringUtil.limitLength(SIHTMLUtil.HTMLEncode(resultSet.getString("makername")), 30, "..."));
        coll.add(maker);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return coll;
  }
  
  public static Collection getFrontSearchCollectionAlpha(Connection conn) {
    Collection coll = new ArrayList();
    Statement statement = null;
    ResultSet resultSet = null;
    SINameValue charge = new SINameValue();
    
    StringBuffer sqlStr = new StringBuffer();
    sqlStr.append("SELECT makername,makercode ");
    sqlStr.append("FROM makertbl ");
    sqlStr.append("WHERE makercode != '1231' AND makercode != '1232' AND enabledflg = '1' ");
    sqlStr.append("AND makercode IN ( ");
    sqlStr.append(" SELECT a.makercode  ");
    sqlStr.append(" FROM cmdtyunittbl a,cmdtyctgrymtbl b,ctgryvw c,ctgrygrouptbl d ");
    sqlStr.append(" WHERE a.cmdtycode=b.cmdtycode AND b.ctgrycode=c.ctgrycode ");
    sqlStr.append(" AND (c.branch LIKE '%~'||d.ctgrycode||'~%' OR c.branch LIKE '%~'||d.ctgrycode) ");
    sqlStr.append(" AND d.ctgrygroup='eyelashgarage' AND a.frontdispflg='1') ");
    sqlStr.append("AND makername < 'あ' ");
    sqlStr.append("ORDER BY CASE makercode WHEN '1001' THEN 1 ELSE 0 END,makername");
    try {
      statement = conn.createStatement();
      resultSet = statement.executeQuery(sqlStr.toString());
      while (resultSet.next()) {
        charge = new SINameValue();
        charge.setValue(resultSet.getString("makercode"));
        charge.setName(SIStringUtil.limitLength(SIHTMLUtil.HTMLEncode(resultSet.getString("makername")), 30, "..."));
        coll.add(charge);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return coll;
  }
  
  public static Collection getFrontSearchCollectionKana(Connection conn) {
    Collection coll = new ArrayList();
    Statement statement = null;
    ResultSet resultSet = null;
    SINameValue charge = new SINameValue();
    
    StringBuffer sqlStr = new StringBuffer();
    sqlStr.append("SELECT CASE WHEN makercode = '1230' THEN 'ポール･シェリーM-products' ");
    sqlStr.append("WHEN makercode = '1001' THEN 'その他' ELSE makername END AS makername,makercode ");
    sqlStr.append("FROM makertbl ");
    sqlStr.append("WHERE makercode != '1231' AND makercode != '1232' AND enabledflg = '1' ");
    sqlStr.append("AND makercode IN ( ");
    sqlStr.append(" SELECT a.makercode  ");
    sqlStr.append(" FROM cmdtyunittbl a,cmdtyctgrymtbl b,ctgryvw c,ctgrygrouptbl d ");
    sqlStr.append(" WHERE a.cmdtycode=b.cmdtycode AND b.ctgrycode=c.ctgrycode ");
    sqlStr.append(" AND (c.branch LIKE '%~'||d.ctgrycode||'~%' OR c.branch LIKE '%~'||d.ctgrycode) ");
    sqlStr.append(" AND d.ctgrygroup='eyelashgarage' AND a.frontdispflg='1') ");
    sqlStr.append("AND makername >= 'あ' ");
    sqlStr.append("ORDER BY CASE makercode WHEN '1001' THEN 1 ELSE 0 END,makername");
    try {
      statement = conn.createStatement();
      resultSet = statement.executeQuery(sqlStr.toString());
      while (resultSet.next()) {
        charge = new SINameValue();
        charge.setValue(resultSet.getString("makercode"));
        charge.setName(SIStringUtil.limitLength(SIHTMLUtil.HTMLEncode(resultSet.getString("makername")), 30, "..."));
        coll.add(charge);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return coll;
  }
  
  public static Collection getContractedMakerCollection(Connection conn) {
    Collection coll = new ArrayList();
    Statement statement = null;
    ResultSet resultSet = null;
    StringBuffer sqlStatement = new StringBuffer();
    SINameValue charge = new SINameValue();
    
    sqlStatement = new StringBuffer();
    sqlStatement.append("SELECT makercode,makername FROM makertbl WHERE contractflg != '0' ORDER BY makername");
    try {
      statement = conn.createStatement();
      resultSet = statement.executeQuery(sqlStatement.toString());
      while (resultSet.next()) {
        charge = new SINameValue();
        charge.setValue(resultSet.getString("makercode"));
        charge.setName(SIStringUtil.limitLength(SIHTMLUtil.HTMLEncode(resultSet.getString("makername")), 15, "..."));
        coll.add(charge);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return coll;
  }
  
  public boolean reset(Connection lConnection) {
    if (SIUtil.isNull(regMakerCode)) return false;
    boolean res = false;
    Statement lStatement = null;
    ResultSet lResultSet = null;
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery("SELECT * FROM makertbl WHERE makercode="+SIDBUtil.SQL2Str(regMakerCode));
      if (lResultSet.next()) {
        this.setRegMakerCode(lResultSet.getString("MakerCode"));// ブランドコード
        this.setRegMakerName(lResultSet.getString("MakerName"));// ブランド名
        this.setRegOtherName1(lResultSet.getString("OtherName1"));//別名１
        this.setRegOtherName2(lResultSet.getString("OtherName2"));//別名２
        this.setRegDescription(lResultSet.getString("Memo"));// メモ
        this.setRegTel(lResultSet.getString("Tel"));// 電話番号
        this.setRegUrl(lResultSet.getString("Url"));// URL
        this.setRegInitDateTime(lResultSet.getString("InitDateTime"));// 登録日時
        this.setRegUpdateDateTime(lResultSet.getString("UpdateDateTime"));// 更新日時
        this.setRegFrontDispFlg(lResultSet.getString("EnabledFlg"));// フロント表示フラグ
        this.setRegContractFlg(lResultSet.getString("ContractFlg"));// 要契約フラグ
        this.setRegSortOrder(lResultSet.getString("SortOrder"));//ソート順
        this.setRegReferencePost(lResultSet.getString("referencepost"));
        this.setRegReferenceNumber(lResultSet.getString("referencenumber"));
        this.setRegReferenceMail(lResultSet.getString("referencemail"));
        this.setEditModeTxt(SIConfig.SIEDIT_MODE_UPDATE);
        res=true;
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    }finally{
      SIDBUtil.close(lResultSet, lStatement);
    }
    return res;
  }
}