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

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;

import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIDBMultiConf;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.taglib.SITagUtil;// 7.3.0 ST2029 追加
import jp.co.sint.tools.SICheckUtil;
import jp.co.sint.tools.SIDateTime;
import jp.co.sint.tools.SIStringUtil;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;

/**
 * @version $Id: SICust.java,v 1.0 Exp $
 * @author Jinwang Chen <br>
 * Description: 顧客マスタに対するクラス
 * <p>
 * History
 * </p>
 * <p>
 * Author&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Reason
 * </p>
 * ============&nbsp;&nbsp;&nbsp;==========&nbsp;&nbsp;=========================== <br>
 * J.W.Chen 2003/06/25 Original
 */

public class SICust extends SIBasic {
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  // 文字コードの設定
  // ////protected String encode = SIConfig.SIENCODE_SHIFT_JIS;
  // 顧客コード
  private String custCode = "";
  
  // 会員レベル
  private String memberLevelCode = "0";
  
  // 個人／法人区分
  private String custCompanyFlg = "0";
  
  // 氏名
  private String custName = "";
  
  // フリガナ
  private String custPronName = "";
  
  // メールアドレス
  private String email = "";
  
  private String emailCon = "";// 7.3.0 PI-NES0501 追加
  
  private String password = "";
  
  private String passwordCon = "";
  
  // 7.2.0 ST0330 追加 ここから
  // 現パスワード
  private String current_password = "";
  
  // 7.2.0 ST0330 追加 ここまで
  
  // PWリマインダ質問
  private String pWQuestion = "";
  
  // PWリマインダ回答
  private String pWAnswer = "";
  
  // 郵便番号1
  private String postCode1 = "";
  
  // 郵便番号2
  private String postCode2 = "";
  
  // 住所1
  private String address1 = "";
  
  // 住所2
  private String address2 = "";
  
  // 住所3
  private String address3 = "";
  
  // サロン名
  private String companyName = "";
  
  // 会社名 7.3.0 PI-NES5051 追加
  private String corporationName = "";
  
  // 店舗電話番号 7.3.0 PI-NES5051 追加
  private String StoreTel = "";
  
  // 連絡先電話番号
  private String tel = "";
  
  // 連絡先FAX番号
  private String fax = "";
  
  private String payMethodCode = "";
  
  // お支払い方法
  private String payMethodName = "";
  
  // 生年月日
  private String birthDate = "";
  
  private String birthDateYear = "";
  
  private String birthDateMonth = "";
  
  private String birthDateDay = "";
  
  // 性別
  private String sex = "";
  
  // 職業
  private String job = "";
  
  // 趣味
  private String[] hobby = new String[0];
  
  // 契約済ブランド
  private String[] maker = new String[0];
  
  // マイカタログ
  private String[] myCatalog = new String[0];
  
  // サイトを知った場所
  private String howToKnow = "";
  
  // 新着メール
  private String newMailFlg = "1"; // 受信する
  
  private String memberLevelName = "";
  
  private String discountRate = "0";
  
  private String maxPoint = "0";
  
  // 7.3.0 ST2029 追加 ここから
  // 最終ログイン日時
  private String strLoginDateTime = "";
  
  // 初回ログインフラグ
  private String flg = "";
  
  // 7.3.0 ST2029 追加 ここまで
  
  private int intDelFlg = 0; // 7.3.0 ST2027 追加 削除フラグ
  
  private String strEMailOld = ""; // 7.3.0 ST2026 追加 退避用E-Mailアドレス
  
  private String strLoginErrCount = "0"; // 7.3.0 ST2028 追加 ログインエラー回数
  
  private String strLoginErrDate = ""; // 7.3.0 ST2028 追加 ログインエラー日付
  
  private String registRoute = ""; // 7.3.0 PI-NES0501 追加 登録経路
  
  private String otherMenu = ""; // その他の施術メニュー
  
  private String charge = ""; // 担当者
  
  private String salonUrl = ""; // サロンホームページURL
  
  private String pdfOutputDateTime = ""; // 登録通知書発行日時
  
  private String pdfOutputDateTime2 = ""; // ランク昇格通知書発行日時GOLD
  
  private String pdfOutputDateTime3 = ""; // ランク昇格通知書発行日時PLATINUM
  
  private String pdfOutputDateTime4 = ""; // ランク昇格通知書発行日時DIAMOND
  
  private String receivableFlg = "0"; // 売掛フラグ
  
  private String introductionMailFlg = "0";
  
  private String memberLevelFixedFlg = "0";
  
  private String noDeliveryFlg = "0";
  
  private String noPointFlg = "0";
  
  private String passwordMailDateTime = "";
  
  private String orderCount = "0";
  
  private String paymentPrice = "0";
  
  private String orderCountLastOneYear = "0";
  
  private String ccLastOrderCode = "";
  
  private String ccLastOrderDate = "";
  
  private String cardNumber1 = "";
  
  private String cardNumber2 = "";
  
  private String hairCosmeFlg = "0";
  
  private String orderFlg = "0";
  
  private String reserveFlg = "0";
  
  private String estimateFlg = "0";
  
  private String mailStatus = "";
  
  private String otherReason = "";
  
  private String deliveryDocument = "0";
  
  private String contractDateTime = "";
  
  private String noRequestFlg = "0";
  
  private String companyPronName = "";
  
  private String corporationPronName = "";
  
  private String openingFlg = "0";
  
  private String openingArea = "";
  
  private String openingPlanDateYear = "";
  
  private String openingPlanDateMonth = "";
  
  private String[] supportMenu = new String[0];
  
  public SICust() {}
  
  public SICust(String lCustCode) {
    setCustCode(lCustCode);
  }
  
  // 顧客コード of setter
  public void setCustCode(String val) {
    if (SIUtil.isNull(val)) val = "";
    val = SIUtil.changeTo(val.trim(), this.encode);
    this.custCode = val;
  }
  
  // 氏名 of setter
  public void setCustName(String val) {
    if (SIUtil.isNull(val)) val = "";
    this.custName = SIUtil.changeTo(val.trim(), this.encode);
  }
  
  // フリガナ of setter
  public void setCustPronName(String val) {
    if (SIUtil.isNull(val)) val = "";
    this.custPronName = SIUtil.changeTo(val.trim(), this.encode);
  }
  
  // メールアドレス of setter
  public void setEmail(String val) {
    if (SIUtil.isNull(val)) val = "";
    this.email = SIUtil.changeTo(val.trim(), this.encode);
  }
  
  // PWリマインダ質問 of setter
  public void setPWQuestion(String val) {
    if (SIUtil.isNull(val)) val = "";
    this.pWQuestion = SIUtil.changeTo(val.trim(), this.encode);
  }
  
  // PWリマインダ回答 of setter
  public void setPWAnswer(String val) {
    if (SIUtil.isNull(val)) val = "";
    this.pWAnswer = SIUtil.changeTo(val.trim(), this.encode);
  }
  
  // 郵便番号1 of setter
  public void setPostCode1(String val) {
    if (SIUtil.isNull(val)) val = "";
    val = SIUtil.changeTo(val.trim(), this.encode);
    this.postCode1 = val;
  }
  
  // 郵便番号2 of setter
  public void setPostCode2(String val) {
    if (SIUtil.isNull(val)) val = "";
    val = SIUtil.changeTo(val.trim(), this.encode);
    this.postCode2 = val;
  }
  
  // 住所1 of setter
  public void setAddress1(String val) {
    if (SIUtil.isNull(val)) val = "";
    this.address1 = SIUtil.changeTo(val.trim(), this.encode);
  }
  
  // 住所2 of setter
  public void setAddress2(String val) {
    if (SIUtil.isNull(val)) val = "";
    this.address2 = SIUtil.changeTo(val.trim(), this.encode);
  }
  
  // 住所3 of setter
  public void setAddress3(String val) {
    if (SIUtil.isNull(val)) val = "";
    this.address3 = SIUtil.changeTo(val.trim(), this.encode);
  }
  
  // 会社名 of setter
  public void setCompanyName(String val) {
    if (SIUtil.isNull(val)) val = "";
    this.companyName = SIUtil.changeTo(val.trim(), this.encode);
  }
  
  // 連絡先電話番号 of setter
  public void setTel(String val) {
    if (SIUtil.isNull(val)) val = "";
    val = SIUtil.changeTo(val.trim(), this.encode);
    this.tel = val;
  }
  
  // 連絡先FAX番号 of setter
  public void setFax(String val) {
    if (SIUtil.isNull(val)) val = "";
    val = SIUtil.changeTo(val.trim(), this.encode);
    this.fax = val;
  }
  
  public void setBirthDate(String val1, String val2, String val3) {
    setBirthDateYear(val1);
    setBirthDateMonth(val2);
    setBirthDateDay(val3);
  }
  
  // 生年月日 of setter
  public void setBirthDate(String val) {
    if (SIUtil.isNull(val)) val = "";
    else {
      try {
        SIDateTime lDateTime = new SIDateTime(val, SIConfig.SIDATE_FORMAT);
        setBirthDate(lDateTime.getYearStr(), lDateTime.getMonthStr(), lDateTime.getDayStr());
      } catch (ParseException e) {
        e.printStackTrace();
        if (SIUtil.isNotNull(val)) {
          setBirthDate(val, "", "");
        }
      }
    }
    this.birthDate = val;
    
  }
  
  // 職業 of setter
  public void setJob(String val) {
    if (SIUtil.isNull(val)) val = "";
    this.job = SIUtil.changeTo(val.trim(), this.encode);
  }
  
  // 趣味 of setter
  public void setHobby(String[] val) {
    if (val == null) val = new String[0];
    this.hobby = SIUtil.changeTo(val, this.encode);
  }
  
  // 契約済ブランド
  public void setMaker(String[] val) {
    if (val == null) val = new String[0];
    this.maker = SIUtil.changeTo(val, this.encode);
  }
  
  // マイカタログ
  public void setMyCatalog(String[] val) {
    if (val == null) val = new String[0];
    this.myCatalog = SIUtil.changeTo(val, this.encode);
  }
  
  // サイトを知った場所 of setter
  public void setHowToKnow(String val) {
    if (SIUtil.isNull(val)) val = "";
    this.howToKnow = SIUtil.changeTo(val.trim(), this.encode);
  }
  
  // 新着メール of setter
  public void setNewMailFlg(String val) {
    if (SIUtil.isNull(val)) val = "0";
    this.newMailFlg = val;
  }
  
  // 7.3.0 ST2029 追加 ここから
  // 最終ログイン日時
  public void setLoginDateTime(String val) {
    if (SIUtil.isNull(val)) val = "";
    this.strLoginDateTime = val;
  }
  
  // ログインフラグ
  public void setFlg(String val) {
    if (SIUtil.isNull(val)) val = "";
    this.flg = val;
  }
  
  // 7.3.0 ST2029 追加 ここまで
  
  public void setMailStatus(String val) {
    if (SIUtil.isNull(val)) val = "";
    this.mailStatus=val;
  }
  
  public void setOtherReason(String val) {
    if (SIUtil.isNull(val)) val = "";
    this.otherReason=val;
  }
  
  public void setDeliveryDocument(String val) {
    if (SIUtil.isNull(val)) val = "0";
    this.deliveryDocument=val;
  }
  
  // 顧客コード of getter
  public String getCustCode() {
    return this.custCode;
  }
  
  // 氏名 of getter
  public String getCustName() {
    return this.custName;
  }
  
  // フリガナ of getter
  public String getCustPronName() {
    return this.custPronName;
  }
  
  // メールアドレス of getter
  public String getEmail() {
    return this.email;
  }
  
  // PWリマインダ質問 of getter
  public String getPWQuestion() {
    return this.pWQuestion;
  }
  
  // PWリマインダ回答 of getter
  public String getPWAnswer() {
    return this.pWAnswer;
  }
  
  // 郵便番号1 of getter
  public String getPostCode1() {
    return this.postCode1;
  }
  
  // 郵便番号2 of getter
  public String getPostCode2() {
    return this.postCode2;
  }
  
  // getter of 郵便番号
  public String getPostCode() {
    if (SIUtil.isNotNull(getPostCode1()) && SIUtil.isNotNull(getPostCode2())) {
      return getPostCode1() + "-" + getPostCode2();
    } else if (SIUtil.isNotNull(getPostCode1()) || SIUtil.isNotNull(getPostCode2())) {
      return getPostCode1() + getPostCode2();
    } else return "";
  }
  
  // 住所1 of getter
  public String getAddress1() {
    return this.address1;
  }
  
  // 住所2 of getter
  public String getAddress2() {
    return this.address2;
  }
  
  // 住所3 of getter
  public String getAddress3() {
    return this.address3;
  }
  
  public String getAddress() {
    return getAddress1() + getAddress2() + getAddress3();
  }
  
  // 会社名 of getter
  public String getCompanyName() {
    return this.companyName;
  }
  
  // 連絡先電話番号 of getter
  public String getTel() {
    return this.tel;
  }
  
  // 連絡先FAX番号 of getter
  public String getFax() {
    return this.fax;
  }
  
  // 生年月日 of getter
  public String getBirthDate() {
    if (SIUtil.isNull(this.birthDateYear) && SIUtil.isNull(this.birthDateMonth) && SIUtil.isNull(this.birthDateDay)) return "";
    else return SIStringUtil.null2Space(this.birthDateYear) + "/" + SIStringUtil.null2Space(this.birthDateMonth) + "/" + SIStringUtil.null2Space(this.birthDateDay);
  }
  
  // 職業 of getter
  public String getJob() {
    return this.job;
  }
  
  // 趣味 of getter
  public String[] getHobby() {
    return this.hobby;
  }
  
  // 契約済ブランド
  public String[] getMaker() {
    return this.maker;
  }
  
  // マイカタログ
  public String[] getMycatalog() {
    return this.myCatalog;
  }
  
  // サイトを知った場所 of getter
  public String getHowToKnow() {
    return this.howToKnow;
  }
  
  // 新着メール of getter
  public String getNewMailFlg() {
    return this.newMailFlg;
  }
  
  // 7.3.0 ST2029 追加 ここから
  // 最終ログイン日時
  public String getLoginDateTime() {
    return this.strLoginDateTime;
  }
  
  // ログインフラグ
  public String getFlg() {
    return this.flg;
  }
  
  // 7.3.0 ST2029 追加 ここまで
  
  public String getMailStatus(){
    return this.mailStatus;
  }
  
  public String getOtherReason(){
    return this.otherReason;
  }
  
  public String getDeliveryDocument(){
    return this.deliveryDocument;
  }
  
  /**
   * @return
   */
  public Category getLog() {
    return log;
  }
  
  /**
   * @param category
   */
  public void setLog(Category category) {
    log = category;
  }
  
  /**
   * @return
   */
  public String getMemberLevelName() {
    return memberLevelName;
  }
  
  public String getDiscountRate() {
    return this.discountRate;
  }
  
  public String getMaxPoint() {
    return this.maxPoint;
  }
  
  /**
   * @param string
   */
  public void setMemberLevelName(String string) {
    if (SIUtil.isNull(string)) string = "";
    string = SIUtil.changeTo(string.trim(), this.encode);
    memberLevelName = string;
  }
  
  public void setDiscountRate(String lDiscountRate) {
    if (SIUtil.isNull(lDiscountRate)) lDiscountRate = "0";
    this.discountRate = lDiscountRate;
  }
  
  public void setMaxPoint(String lMaxPoint) {
    if (SIUtil.isNull(lMaxPoint)) lMaxPoint = "0";
    this.maxPoint = lMaxPoint;
  }
  
  /**
   * @return
   */
  public String getMemberLevelCode() {
    return memberLevelCode;
  }
  
  /**
   * @param string
   */
  public void setMemberLevelCode(String string) {
    memberLevelCode = string;
  }
  
  /**
   * @return
   */
  public String getSex() {
    return sex;
  }
  
  /**
   * @param string
   */
  public void setSex(String string) {
    sex = string;
  }
  
  /**
   * @return
   */
  public String getCustCompanyFlg() {
    return custCompanyFlg;
  }
  
  /**
   * @param string
   */
  public void setCustCompanyFlg(String string) {
    custCompanyFlg = string;
  }
  
  /**
   * @return
   */
  public String getPayMethodName() {
    return payMethodName;
  }
  
  /**
   * @param string
   */
  public void setPayMethodName(String string) {
    if (SIUtil.isNull(string)) string = "";
    string = SIUtil.changeTo(string.trim(), this.encode);
    payMethodName = string;
  }
  
  /**
   * @return
   */
  public String getBirthDateDay() {
    return birthDateDay;
  }
  
  /**
   * @return
   */
  public String getBirthDateMonth() {
    return birthDateMonth;
  }
  
  /**
   * @return
   */
  public String getBirthDateYear() {
    return birthDateYear;
  }
  
  /**
   * @param string
   */
  public void setBirthDateDay(String string) {
    if (SIUtil.isNull(string)) string = "";
    birthDateDay = SIUtil.changeTo(string.trim(), this.encode);
  }
  
  /**
   * @param string
   */
  public void setBirthDateMonth(String string) {
    if (SIUtil.isNull(string)) string = "";
    birthDateMonth = SIUtil.changeTo(string.trim(), this.encode);
  }
  
  /**
   * @param string
   */
  public void setBirthDateYear(String string) {
    if (SIUtil.isNull(string)) string = "";
    birthDateYear = SIUtil.changeTo(string.trim(), this.encode);
  }
  
  /**
   * @param string 生年月日を年,月,日と分けずに一括して取得した場合（DBやCSVより）
   */
  public String getCustBirthDate() {
    return this.birthDate;
  }
  
  /**
   * @return
   */
  public String getEncode() {
    return encode;
  }
  
  /**
   * @param string
   */
  public void setEncode(String string) {
    if (SIUtil.isNull(string)) string = "";
    this.encode = string.trim();
  }
  
  /**
   * @return
   */
  public String getPassword() {
    return password;
  }
  
  public String getPasswordCon() {
    return this.passwordCon;
  }
  
  // 7.2.0 ST0330 追加 ここから
  /**
   * @return
   */
  public String getCurrentPassword() {
    return this.current_password;
  }
  
  // 7.2.0 ST0330 追加 ここまで
  
  /**
   * @param string
   */
  public void setPassword(String string) {
    if (SIUtil.isNull(string)) string = "";
    string = SIUtil.changeTo(string.trim(), this.encode);
    password = string;
  }
  
  /**
   * @param string
   */
  public void setPasswordCon(String lPasswordCon) {
    if (SIUtil.isNull(lPasswordCon)) lPasswordCon = "";
    passwordCon = SIUtil.changeTo(lPasswordCon.trim(), this.encode);
  }
  
  // 7.2.0 ST0330 追加 ここから
  /**
   * @param string
   */
  public void setCurrentPassword(String lPassword) {
    if (SIUtil.isNull(lPassword)) lPassword = "";
    this.current_password = SIUtil.changeTo(lPassword.trim(), this.encode);
  }
  
  // 7.2.0 ST0330 追加 ここまで
  
  /**
   * @return
   */
  public String getPayMethodCode() {
    return payMethodCode;
  }
  
  /**
   * @param string
   */
  public void setPayMethodCode(String string) {
    if (SIUtil.isNull(string)) string = "";
    payMethodCode = string;
  }
  
  // 7.3.0 ST2027 追加 ここから
  // 削除フラグ Setter
  public void setDelFlg(int intDelFlg) {
    this.intDelFlg = intDelFlg;
  }
  
  // 削除フラグ Getter
  public int getDelFlg() {
    return this.intDelFlg;
  }
  
  // 7.3.0 ST2027 追加 ここまで
  // 7.3.0 ST2026 追加 ここから
  // 退避用E-mailアドレス Setter
  public void setEMailOld(String strEMailOld) {
    this.strEMailOld = strEMailOld;
  }
  
  // 退避用E-mailアドレス Getter
  public String getEMailOld() {
    return this.strEMailOld;
  }
  
  // 7.3.0 ST2026 追加 ここまで
  
  // 7.3.0 ST2028 追加 ここから
  
  // ログインエラー回数 Setter
  public void setLoginErrCount(String strLoginErrCount) {
    if (SIUtil.isNull(strLoginErrCount)) strLoginErrCount = "0";
    this.strLoginErrCount = SIUtil.changeTo(strLoginErrCount.trim(), this.encode);
  }
  
  // ログインエラー回数 Getter
  public String getLoginErrCount() {
    return this.strLoginErrCount;
  }
  
  // ログインエラー発生日時 Setter
  public void setLoginErrDate(String strLoginErrDate) {
    this.strLoginErrDate = strLoginErrDate;
  }
  
  // ログインエラー発生日時 Getter
  public String getLoginErrDate() {
    return this.strLoginErrDate;
  }
  
  // ログインエラー発生日時 Getter(日付のみ & -を/に置き換え)
  public String getLoginErrDateEdit() {
    if (!SIUtil.isNull(this.strLoginErrDate) && this.strLoginErrDate.length() >= 10) {
      return this.strLoginErrDate.replace('-', '/').substring(0, 10);
    } else {
      return new String("");
    }
  }
  
  // 7.3.0 ST2028 追加 ここまで
  
  /**
   * <b>reset </b> 顧客コードから顧客情報を取得するSQLを書き出す
   * 
   * @param Connection
   * @return なし
   * @throws なし
   */
  public void reset(Connection lConnection) throws SIDBAccessException {
    setEncode(SIConfig.SIENCODE_NONE);
    
    StringBuffer lSqlBuf = new StringBuffer("SELECT aa.*,bb.DiscountRate,bb.MemberLevelName,");
    lSqlBuf.append("(SELECT CASE WHEN SUM(PointOfIssue)<0 THEN 0 ELSE SUM(PointOfIssue) END AS Point ");
    lSqlBuf.append("FROM PointManMTbl WHERE EnableFlg=1 AND CustCode=aa.CustCode) AS MaxPoint,");
    lSqlBuf.append("(SELECT PayMethodName FROM PayMethodMTbl WHERE PayMethodCode=aa.PayMethodCode AND MallShopCode=");
    lSqlBuf.append(SIDBUtil.SQL2Str(SIConfig.SIMALL.getMallShopCode(), ") AS PayMethodName "));
    lSqlBuf.append("FROM CustTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa,");
    lSqlBuf.append("MemberLevelMTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bb ");
    lSqlBuf.append("WHERE aa.MemberLevelCode=bb.MemberLevelCode ");
    lSqlBuf.append("AND aa.CustCode=" + SIDBUtil.SQL2Str(getCustCode()));
    
    setup(lConnection, lSqlBuf.toString());
    resetMaker(lConnection);
    resetHobby(lConnection);
    resetMyCatalog(lConnection);
    resetMenu(lConnection);
  }
  
  /**
   * <b>reset </b> Eメールから顧客情報顧客情報を取得するSQLを書き出す
   * 
   * @param Connection
   * @param lMail Eメール
   * @return なし
   * @throws なし
   */
  public void reset(Connection lConnection, String lMail) {
    StringBuffer lSqlBuf = new StringBuffer("SELECT aa.*,bb.DiscountRate,bb.MemberLevelName,");
    lSqlBuf.append("(SELECT CASE WHEN SUM(PointOfIssue)<0 THEN 0 ELSE SUM(PointOfIssue) END AS Point ");
    lSqlBuf.append("FROM PointManMTbl WHERE EnableFlg=1 AND CustCode=aa.CustCode) AS MaxPoint,");
    lSqlBuf.append("(SELECT PayMethodName FROM PayMethodMTbl WHERE PayMethodCode=aa.PayMethodCode AND MallShopCode=");
    lSqlBuf.append(SIDBUtil.SQL2Str(SIConfig.SIMALL.getMallShopCode(), ") AS PayMethodName "));
    lSqlBuf.append("FROM CustTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa,");
    lSqlBuf.append("MemberLevelMTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bb ");
    lSqlBuf.append("WHERE aa.MemberLevelCode=bb.MemberLevelCode ");
    lSqlBuf.append("AND aa.EMail=" + SIDBUtil.SQL2Str(lMail));
    lSqlBuf.append(" AND aa.DelFlg != " + SIConfig.SIDEL_FLG_DELETE); // 7.3.0
    // ST2027
    // 追加
    
    setup(lConnection, lSqlBuf.toString());
    resetMaker(lConnection);
    resetHobby(lConnection);
    resetMyCatalog(lConnection);
    resetMenu(lConnection);
  }
  
  /**
   * <b>setup </b> resetで取得したSQLを実行し、顧客情報をセットする
   * 
   * @param Connection
   * @return なし
   * @throws なし
   */
  private void setup(Connection lConnection, String lSql) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    try {
      log.debug("setup:lSql=" + lSql);
      
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql);
      
      if (lResultSet.next()) {
        this.setEncode(SIConfig.SIENCODE_NONE);
        this.setCustCode(lResultSet.getString("custCode")); // 顧客コード
        this.setMemberLevelCode(lResultSet.getString("memberLevelCode")); // 会員種別コード
        this.setMemberLevelName(lResultSet.getString("memberLevelName")); // 会員種別コード
        this.setCustCompanyFlg(lResultSet.getString("custCompanyFlg")); // 法人フラグ
        this.setCustName(lResultSet.getString("custName")); // 氏名
        this.setCustPronName(lResultSet.getString("custPronName")); // フリガナ
        this.setEmail(lResultSet.getString("email")); // メールアドレス
        this.setPassword(lResultSet.getString("password")); // パスワード
        this.setPasswordCon(lResultSet.getString("password")); // パスワード
        this.setPWQuestion(lResultSet.getString("pWQuestion")); // ＰＷリマインダ質問
        this.setPWAnswer(lResultSet.getString("pWAnswer")); // ＰＷリマインダ回答
        this.setPostCode1(lResultSet.getString("postCode1")); // 郵便番号1
        this.setPostCode2(lResultSet.getString("postCode2")); // 郵便番号2
        this.setAddress1(lResultSet.getString("address1")); // 住所１
        this.setAddress2(lResultSet.getString("address2")); // 住所２
        this.setAddress3(lResultSet.getString("address3")); // 住所３
        this.setCompanyName(lResultSet.getString("companyName")); // サロン名
        this.setCorporationName(lResultSet.getString("corporationName")); // 会社名
        this.setTel(lResultSet.getString("tel")); // 連絡先電話番号
        this.setStoreTel(lResultSet.getString("StoreTel")); // 店舗電話番号//7.3.0 PI-NES0501 追加
        this.setFax(lResultSet.getString("fax")); // 連絡先FAX番号
        this.setPayMethodCode(lResultSet.getString("payMethodCode")); // お支払いコード
        this.setPayMethodName(lResultSet.getString("payMethodName")); // お支払い方法
        this.setBirthDate(lResultSet.getString("birthDate")); // 生年月日
        this.setSex(lResultSet.getString("sex")); // 性別
        this.setJob(lResultSet.getString("job")); // 職業
        this.setHowToKnow(lResultSet.getString("howToKnow")); // サイトを知った場所
        this.setNewMailFlg(lResultSet.getString("newMailFlg")); // 新着メール
        this.setDiscountRate(lResultSet.getString("DiscountRate")); // 値引き率
        this.setMaxPoint(lResultSet.getString("MaxPoint")); // ポイント
        this.setOtherMenu(lResultSet.getString("otherMenu")); // その他の施術メニュー
        this.setCharge(lResultSet.getString("chargeCode")); // 担当者
        this.setSalonUrl(lResultSet.getString("salonUrl")); // サロンホームページURL
        this.setPdfOutputDateTime(lResultSet.getString("pdfOutputDateTime")); // 登録通知書出力日時
        this.setPdfOutputDateTime2(lResultSet.getString("pdfOutputDateTime2")); // 登録通知書出力日時
        this.setPdfOutputDateTime3(lResultSet.getString("pdfOutputDateTime3")); // 登録通知書出力日時
        this.setPdfOutputDateTime4(lResultSet.getString("pdfOutputDateTime4")); // 登録通知書出力日時
        // 7.3.0 ST2029 追加 ここから
        // ※2回目以降の[ログイン直後]のみで、かつ項目が取得出来たときのみ表示(管理側登録時は日付をセットしないため)
        if ("0".equals(flg) && lResultSet.getString("loginDateTime") != null) {
          this.setLoginDateTime(SITagUtil.replace(lResultSet.getString("loginDateTime"), "-", "/").substring(0, 19)); // 最終ログイン日時を取得
        }
        setFlg("");
        // 7.3.0 ST2029 追加 ここまで
        this.setDelFlg(lResultSet.getInt("delflg")); // 削除フラグ 7.3.0 ST2027 追加
        this.setEMailOld(lResultSet.getString("email")); // メールアドレス（退避） 7.3.0
        // ST2026 追加
        this.setLoginErrCount(lResultSet.getString("loginerrorcount")); // ログインエラー回数
        // 7.3.0
        // ST2028追加
        this.setLoginErrDate(lResultSet.getString("loginerrordatetime")); // ログインエラー日時
        // 7.3.0
        // ST2028追加
        this.setReceivableFlg(lResultSet.getString("receivableFlg")); // 売掛フラグ
        this.setIntroductionMailFlg(lResultSet.getString("introductionMailFlg"));
        this.setMemberLevelFixedFlg(lResultSet.getString("memberLevelFixedFlg"));
        this.setNoDeliveryFlg(lResultSet.getString("noDeliveryFlg"));
        this.setNoPointFlg(lResultSet.getString("noPointFlg"));
        this.setPasswordMailDateTime(SIDBUtil.getDateTime(lResultSet.getTimestamp("passwordMailDateTime")));
        this.setCcLastOrderCode(lResultSet.getString("ccLastOrderCode"));
        this.setCcLastOrderDate(SIDBUtil.getDate(lResultSet.getTimestamp("ccLastOrderDate")));
        this.setCardNumber1(lResultSet.getString("cardNumber1"));
        this.setCardNumber2(lResultSet.getString("cardNumber2"));
        this.setHairCosmeFlg(lResultSet.getString("hairCosmeFlg"));
        this.setMailStatus(lResultSet.getString("mailStatus"));
        this.setOtherReason(lResultSet.getString("otherReason"));
        this.setDeliveryDocument(lResultSet.getString("deliveryDocument"));
        this.setContractDateTime(lResultSet.getString("contractDateTime"));
        this.setNoRequestFlg(lResultSet.getString("noRequestFlg"));
        this.setCompanyPronName(lResultSet.getString("companyPronName"));
        this.setCorporationPronName(lResultSet.getString("corporationPronName"));
        this.setOpeningFlg(lResultSet.getString("openingFlg"));
        try{
          SIDateTime OpeningPlanDate = new SIDateTime(lResultSet.getString("openingPlanDate"),SIConfig.SIDATE_FORMAT0);
          this.setOpeningPlanDateYear(OpeningPlanDate.getYearStr());
          this.setOpeningPlanDateMonth(OpeningPlanDate.getMonthStr());
        }catch(Exception e){}
        this.setOpeningArea(lResultSet.getString("openingArea"));
      }
    } catch (SQLException sqle) {
      sqle.printStackTrace();
    } finally {
      SIDBUtil.close(lStatement, lResultSet);
    }
  }
  
  /**
   * <b>resetHobby </b> 特定顧客の趣味を取得
   * 
   * @param Connection
   * @return なし
   * @throws なし
   */
  protected void resetHobby(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    Collection lHobbyColl = new ArrayList();
    String lSql = "SELECT HobbyName FROM CustHobbyTbl WHERE CustCode=" + SIDBUtil.SQL2Str(getCustCode());
    log.debug("resetHobby:lSql=" + lSql);
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql);
      while (lResultSet.next()) {
        lHobbyColl.add(lResultSet.getString("HobbyName"));
      }
      setEncode(SIConfig.SIENCODE_NONE);
      this.setHobby((String[]) lHobbyColl.toArray(new String[0]));
    } catch (SQLException sqle) {
      sqle.printStackTrace();
    } finally {
      SIDBUtil.close(lStatement, lResultSet);
    }
  }
  
  protected void resetMaker(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    Collection lMakerColl = new ArrayList();
    String lSql = "SELECT makercode FROM custmakertbl WHERE custcode=" + SIDBUtil.SQL2Str(getCustCode());
    log.debug("resetMaker:lSql=" + lSql);
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql);
      while (lResultSet.next()) {
        lMakerColl.add(lResultSet.getString("makercode"));
      }
      setEncode(SIConfig.SIENCODE_NONE);
      this.setMaker((String[]) lMakerColl.toArray(new String[0]));
    } catch (SQLException sqle) {
      sqle.printStackTrace();
    } finally {
      SIDBUtil.close(lStatement, lResultSet);
    }
  }
  
  public void resetMyCatalog(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    Collection lMyCatalogColl = new ArrayList();
    String lSql = "SELECT individualcode FROM mycatalogtbl WHERE custcode=" + SIDBUtil.SQL2Str(getCustCode());
    log.debug("resetMyCatalog:lSql=" + lSql);
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql);
      while (lResultSet.next()) {
        lMyCatalogColl.add(lResultSet.getString("individualcode"));
      }
      setEncode(SIConfig.SIENCODE_NONE);
      this.setMyCatalog((String[]) lMyCatalogColl.toArray(new String[0]));
    } catch (SQLException sqle) {
      sqle.printStackTrace();
    } finally {
      SIDBUtil.close(lStatement, lResultSet);
    }
  }
  
  protected void resetMenu(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    Collection lMenuColl = new ArrayList();
    String lSql = "SELECT menuname FROM custmenutbl WHERE custcode=" + SIDBUtil.SQL2Str(getCustCode());
    log.debug("resetMenu:lSql=" + lSql);
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql);
      while (lResultSet.next()) {
        lMenuColl.add(lResultSet.getString("menuname"));
      }
      setEncode(SIConfig.SIENCODE_NONE);
      this.setSupportMenu((String[]) lMenuColl.toArray(new String[0]));
    } catch (SQLException sqle) {
      sqle.printStackTrace();
    } finally {
      SIDBUtil.close(lStatement, lResultSet);
    }
  }
  
  /**
   * <b>getKeyList </b> 顧客コードをキーとしてEメールをHashMapに格納する
   * 
   * @param Connection
   * @return HashMap
   * @throws なし
   */
  public HashMap getKeyList(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    HashMap custCode = new HashMap();
    String lSql = "SELECT CustCode,email FROM CustTbl ";
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql);
      while (lResultSet.next()) {
        custCode.put(lResultSet.getString("CustCode"), lResultSet.getString("email"));
      }
    } catch (SQLException sqle) {
      sqle.printStackTrace();
    } finally {
      SIDBUtil.close(lStatement, lResultSet);
    }
    return custCode;
  }
  
  /**
   * @return storeTel を戻します。
   */
  public String getStoreTel() {
    return StoreTel;
  }
  
  /**
   * @param storeTel storeTel を設定。
   */
  public void setStoreTel(String storeTel) {
    StoreTel = storeTel;
  }
  
  /**
   * @return emailCon を戻します。
   */
  public String getEmailCon() {
    return emailCon;
  }
  
  /**
   * @param emailCon emailCon を設定。
   */
  public void setEmailCon(String emailCon) {
    this.emailCon = emailCon;
  }
  
  /**
   * @return corporationName を戻します。
   */
  public String getCorporationName() {
    return corporationName;
  }
  
  /**
   * @param corporationName corporationName を設定。
   */
  public void setCorporationName(String corporationName) {
    if (SIUtil.isNull(corporationName)) corporationName="";
    this.corporationName = corporationName;
  }
  
  public String getRegistRoute() {
    return registRoute;
  }
  
  public void setRegistRoute(String registRoute) {
    this.registRoute = registRoute;
  }
  
  //getter of otherMenu
  public String getOtherMenu() {
    return this.otherMenu;
  }
  
  //setter of otherMenu
  public void setOtherMenu(String otherMenu) {
    if (SIUtil.isNull(otherMenu)) otherMenu = "";
    this.otherMenu = SIUtil.changeTo(otherMenu.trim(), this.encode);
  }
  
  //getter of charge
  public String getCharge() {
    return this.charge;
  }
  
  //setter of charge
  public void setCharge(String charge) {
    if (SIUtil.isNull(charge)) charge = "";
    this.charge = SIUtil.changeTo(charge.trim(), this.encode);
  }
  
  public String getSalonUrl() {
    return salonUrl;
  }
  
  public void setSalonUrl(String salonUrl) {
    if (SIUtil.isNull(salonUrl)) salonUrl = "";
    this.salonUrl = salonUrl;
  }
  
  public String getPdfOutputDateTime() {
    return pdfOutputDateTime;
  }
  
  public void setPdfOutputDateTime(String pdfOutputDateTime) {
    if (SIUtil.isNull(pdfOutputDateTime)) pdfOutputDateTime = "";
    this.pdfOutputDateTime = pdfOutputDateTime;
  }
  
  public String getPdfOutputDateTime2() {
    return pdfOutputDateTime2;
  }
  
  public void setPdfOutputDateTime2(String pdfOutputDateTime2) {
    if (SIUtil.isNull(pdfOutputDateTime2)) pdfOutputDateTime2 = "";
    this.pdfOutputDateTime2 = pdfOutputDateTime2;
  }
  
  public String getPdfOutputDateTime3() {
    return pdfOutputDateTime3;
  }
  
  public void setPdfOutputDateTime3(String pdfOutputDateTime3) {
    if (SIUtil.isNull(pdfOutputDateTime3)) pdfOutputDateTime3 = "";
    this.pdfOutputDateTime3 = pdfOutputDateTime3;
  }
  
  public String getPdfOutputDateTime4() {
    return pdfOutputDateTime4;
  }
  
  public void setPdfOutputDateTime4(String pdfOutputDateTime4) {
    if (SIUtil.isNull(pdfOutputDateTime4)) pdfOutputDateTime4 = "";
    this.pdfOutputDateTime4 = pdfOutputDateTime4;
  }
  
  
  public String getReceivableFlg() {
    return receivableFlg;
  }
  
  public void setReceivableFlg(String receivableFlg) {
    if (SIUtil.isNull(receivableFlg)) receivableFlg = "0";
    this.receivableFlg = receivableFlg;
  }
  
  public String getIntroductionMailFlg() {
    return introductionMailFlg;
  }
  
  public void setIntroductionMailFlg(String introductionMailFlg) {
    if (SIUtil.isNull(introductionMailFlg)) introductionMailFlg = "0";
    this.introductionMailFlg = introductionMailFlg;
  }
  
  public String getMemberLevelFixedFlg() {
    return memberLevelFixedFlg;
  }
  
  public String getNoDeliveryFlg() {
    return noDeliveryFlg;
  }
  
  public String getNoPointFlg() {
    return noPointFlg;
  }
  
  public String getPasswordMailDateTime() {
    return passwordMailDateTime;
  }
  
  public void setMemberLevelFixedFlg(String memberLevelFixedFlg) {
    if (SIUtil.isNull(memberLevelFixedFlg)) memberLevelFixedFlg = "0";
    this.memberLevelFixedFlg = memberLevelFixedFlg;
  }
  
  public void setNoDeliveryFlg(String noDeliveryFlg) {
    if (SIUtil.isNull(noDeliveryFlg)) noDeliveryFlg = "0";
    this.noDeliveryFlg = noDeliveryFlg;
  }
  
  public void setNoPointFlg(String noPointFlg) {
    if (SIUtil.isNull(noPointFlg)) noPointFlg = "0";
    this.noPointFlg = noPointFlg;
  }
  
  public void setPasswordMailDateTime(String passwordMailDateTime) {
    if (SIUtil.isNull(passwordMailDateTime)) passwordMailDateTime = "";
    this.passwordMailDateTime = passwordMailDateTime;
  }
  
  public String getOrderCount() {
    return orderCount;
  }
  
  public String getPaymentPrice() {
    return paymentPrice;
  }
  
  public String getOrderCountLastOneYear() {
    return orderCountLastOneYear;
  }
  
  public void setOrderCount(String orderCount) {
    if (SIUtil.isNull(orderCount)) orderCount = "0";
    this.orderCount = orderCount;
  }
  
  public void setPaymentPrice(String paymentPrice) {
    if (SIUtil.isNull(paymentPrice)) paymentPrice = "0";
    this.paymentPrice = paymentPrice;
  }
  
  public void setOrderCountLastOneYear(String orderCount) {
    if (SIUtil.isNull(orderCount)) orderCount = "0";
    this.orderCountLastOneYear = orderCount;
  }
  
  public String getCardNumber1() {
    return cardNumber1;
  }
  
  public void setCardNumber1(String cardNumber1) {
    if (SIUtil.isNull(cardNumber1)) cardNumber1 = "";
    this.cardNumber1 = cardNumber1;
  }
  
  public String getCardNumber2() {
    return cardNumber2;
  }
  
  public void setCardNumber2(String cardNumber2) {
    if (SIUtil.isNull(cardNumber2)) cardNumber2 = "";
    this.cardNumber2 = cardNumber2;
  }
  
  public String getCcLastOrderCode() {
    return ccLastOrderCode;
  }
  
  public void setCcLastOrderCode(String ccLastOrderCode) {
    if (SIUtil.isNull(ccLastOrderCode)) ccLastOrderCode = "";
    this.ccLastOrderCode = ccLastOrderCode;
  }
  
  public String getCcLastOrderDate() {
    return ccLastOrderDate;
  }
  
  public void setCcLastOrderDate(String ccLastOrderDate) {
    if (SIUtil.isNull(ccLastOrderDate)) ccLastOrderDate = "";
    this.ccLastOrderDate = ccLastOrderDate;
  }
  
  public String getHairCosmeFlg() {
    return hairCosmeFlg;
  }
  
  public void setHairCosmeFlg(String hairCosmeFlg) {
    if (SIUtil.isNull(hairCosmeFlg)) hairCosmeFlg = "0";
    this.hairCosmeFlg = hairCosmeFlg;
  }
  
  public String getOrderFlg() {
    return orderFlg;
  }
  
  public String getReserveFlg() {
    return reserveFlg;
  }
  
  public String getEstimateFlg() {
    return estimateFlg;
  }

  public void setOrderFlg(String orderFlg) {
    if (SIUtil.isNull(orderFlg)) orderFlg = "0";
    this.orderFlg = orderFlg;
  }
  
  public void setReserveFlg(String reserveFlg) {
    if (SIUtil.isNull(reserveFlg)) reserveFlg = "0";
    this.reserveFlg = reserveFlg;
  }
  
  public void setEstimateFlg(String estimateFlg) {
    if (SIUtil.isNull(estimateFlg)) estimateFlg = "0";
    this.estimateFlg = estimateFlg;
  }
  
  public String getContractDateTime() {
    return contractDateTime;
  }
  
  public void setContractDateTime(String contractDateTime) {
    if (SIUtil.isNull(contractDateTime)) contractDateTime = "";
    this.contractDateTime = contractDateTime;
  }
  
  public String getCompanyPronName() {
    return companyPronName;
  }
  
  public void setCompanyPronName(String companyPronName) {
    if (SIUtil.isNull(companyPronName)) companyPronName = "";
    this.companyPronName = companyPronName;
  }
  
  public String getCorporationPronName() {
    return corporationPronName;
  }
  
  public void setCorporationPronName(String corporationPronName) {
    if (SIUtil.isNull(corporationPronName)) corporationPronName = "";
    this.corporationPronName = corporationPronName;
  }
  
  public String getOpeningArea() {
    return openingArea;
  }
  
  public void setOpeningArea(String openingArea) {
    if (SIUtil.isNull(openingArea)) openingArea = "";
    this.openingArea = openingArea;
  }
  
  public String getOpeningFlg() {
    return openingFlg;
  }
  
  public void setOpeningFlg(String openingFlg) {
    if (SIUtil.isNull(openingFlg)) openingFlg = "0";
    this.openingFlg = openingFlg;
  }
  
  public String getOpeningPlanDateYear() {
    return openingPlanDateYear;
  }
  
  public String getOpeningPlanDateMonth() {
    return openingPlanDateMonth;
  }
  
  public String getOpeningPlanDate() {
    return openingPlanDateYear + "-" +openingPlanDateMonth + "-01";
  }
  
  public void setOpeningPlanDateYear(String openingPlanDate) {
    if (SIUtil.isNull(openingPlanDate)) openingPlanDate = "";
    this.openingPlanDateYear = openingPlanDate;
  }
  
  public void setOpeningPlanDateMonth(String openingPlanDate) {
    if (SIUtil.isNull(openingPlanDate)) openingPlanDate = "";
    this.openingPlanDateMonth = openingPlanDate;
  }
  
  public String[] getSupportMenu() {
    return supportMenu;
  }
  
  public void setSupportMenu(String[] val) {
    if (val == null) val = new String[0];
    this.supportMenu = SIUtil.changeTo(val, this.encode);
  }
  
  public String getNoRequestFlg() {
    return noRequestFlg;
  }
  
  public void setNoRequestFlg(String noRequestFlg) {
    if (SIUtil.isNull(noRequestFlg)) noRequestFlg = "0";
    this.noRequestFlg = noRequestFlg;
  }
  
  public void setOrderHistry(Connection lConnection) {
    if (SIUtil.isNull(custCode)) return;
    StringBuffer lSql = new StringBuffer();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    try{
      lSql.append("SELECT o.custcode,count(*) AS orderCount ");
      lSql.append(",sum(o.totalofprice+o.totalofdeliveryfee+o.totaloffee+coalesce(o.discountdeliveryfee,0)+coalesce(o.discountfee,0)-o.sumofdiscount-o.setdiscount-o.sumbypoint) AS paymentPrice ");
      lSql.append(",sum(CASE WHEN o.initdatetime > current_timestamp + '-1 years'::interval THEN 1 ELSE 0 END) AS orderCountLastOneYear ");
      lSql.append("FROM ordersumvw o ");
      //lSql.append(",(SELECT ordercode,sum(CASE WHEN paymentdate IS NOT NULL THEN paymentprice ELSE 0 END) AS totalpaymentprice FROM paymenttbl WHERE status <> 1 GROUP BY ordercode) p ");
      lSql.append(",(SELECT p.ordercode,sum(CASE WHEN p.paymentdate IS NOT NULL THEN paymentprice ELSE 0 END) AS totalpaymentprice FROM paymenttbl p,orderlatestvw o WHERE p.status <> 1 AND p.ordercode=o.ordercode AND o.custcode=")
        .append(SIDBUtil.SQL2Str(custCode)).append(" GROUP BY p.ordercode) p ");
      lSql.append("WHERE o.ordercode=p.ordercode ");
      lSql.append("AND o.status=1 ");
      lSql.append("AND o.deliveryedflg+o.nodeliveryflg=1 ");
      lSql.append("AND o.totalofprice+o.totalofdeliveryfee+o.totaloffee+coalesce(o.discountdeliveryfee,0)+coalesce(o.discountfee,0)-o.sumofdiscount-o.setdiscount-o.sumbypoint <= p.totalpaymentprice ");
      lSql.append("AND o.custcode=").append(SIDBUtil.SQL2Str(custCode," "));
      lSql.append("GROUP BY o.custcode");
      
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      
      if(lResultSet.next()){
        this.setOrderCount(lResultSet.getString("orderCount"));
        this.setPaymentPrice(lResultSet.getString("paymentPrice"));
        this.setOrderCountLastOneYear(lResultSet.getString("orderCountLastOneYear"));
      }
    }catch(Exception e){
      e.printStackTrace();
    }finally{
      SIDBUtil.close(lResultSet, lStatement);
    }
  }
  
  public String getVipComment(){
    int msgFlg = 0;
    StringBuffer res = new StringBuffer();
    //ランク固定の場合
    if ("1".equals(getMemberLevelFixedFlg())){
      return "";
    //ダイヤモンド維持の場合
    }else if ("3".equals(getMemberLevelCode())){
      return "";
    //ダイヤモンド昇格の場合
    } else if (SICheckUtil.isGreater(getOrderCountLastOneYear(),"9")&&SICheckUtil.isGreater(getOrderCount(),"29")&&SICheckUtil.isGreater(getPaymentPrice(),"14999999")) {
      res.append("<span class=\"txt_bold\">次回の判定よりダイヤモンドVIP会員になります。</span>");
      msgFlg = 3;
    //プラチナ維持の場合
    } else if ("2".equals(getMemberLevelCode())){
      if (SICheckUtil.isGreater(getOrderCountLastOneYear(),"9")&&SICheckUtil.isGreater(getOrderCount(),"29")) {
        res.append("あと合計<span class=\"txt_bold\">￥").append(SIUtil.NumberFormat(SIUtil.sub("15000000", getPaymentPrice()))).append("以上</span>のご購入で翌月の会員ランク判定よりダイヤモンドVIP会員になります。<br>");
      } else if (SICheckUtil.isGreater(getOrderCountLastOneYear(),"9")&&SICheckUtil.isGreater(getPaymentPrice(),"14999999")) {
        res.append("あと<span class=\"txt_bold\">").append(SIUtil.sub("30", getOrderCount())).append("回以上</span>のご購入で翌月の会員ランク判定よりダイヤモンドVIP会員になります。<br>");
      } else if (SICheckUtil.isGreater(getOrderCount(),"29")&&SICheckUtil.isGreater(getPaymentPrice(),"14999999")) {
        res.append("あと<span class=\"txt_bold\">").append(SIUtil.sub("10", getOrderCountLastOneYear())).append("回以上</span>のご購入で翌月の会員ランク判定よりダイヤモンドVIP会員になります。<br>");
      } else {
        if (SICheckUtil.isGreater(SIUtil.sub("10", getOrderCountLastOneYear()),SIUtil.sub("30", getOrderCount()))) {
          res.append("あと<span class=\"txt_bold\">").append(SIUtil.sub("10", getOrderCountLastOneYear())).append("回以上</span>かつ");
        } else {
          res.append("あと<span class=\"txt_bold\">").append(SIUtil.sub("30", getOrderCount())).append("回以上</span>かつ");
        }
        res.append("合計<span class=\"txt_bold\">￥").append(SIUtil.NumberFormat(SIUtil.sub("15000000", getPaymentPrice()))).append("以上</span>のご購入で翌月の会員ランク判定よりダイヤモンドVIP会員になります。<br>");
      }
      msgFlg = 3;
    //プラチナ昇格の場合
    } else if (SICheckUtil.isGreater(getOrderCountLastOneYear(),"2")&&SICheckUtil.isGreater(getOrderCount(),"9")&&SICheckUtil.isGreater(getPaymentPrice(),"1499999")) {
      res.append("<span class=\"txt_bold\">次回の判定よりプラチナVIP会員になります。</span><br>");
      if (SICheckUtil.isGreater(getOrderCountLastOneYear(),"9")&&SICheckUtil.isGreater(getOrderCount(),"29")) {
        res.append("あと合計<span class=\"txt_bold\">￥").append(SIUtil.NumberFormat(SIUtil.sub("15000000", getPaymentPrice()))).append("以上</span>のご購入で翌月の会員ランク判定よりダイヤモンドVIP会員になります。<br>");
      } else if (SICheckUtil.isGreater(getOrderCountLastOneYear(),"9")&&SICheckUtil.isGreater(getPaymentPrice(),"14999999")) {
        res.append("あと<span class=\"txt_bold\">").append(SIUtil.sub("30", getOrderCount())).append("回以上</span>のご購入で翌月の会員ランク判定よりダイヤモンドVIP会員になります。<br>");
      } else if (SICheckUtil.isGreater(getOrderCount(),"29")&&SICheckUtil.isGreater(getPaymentPrice(),"14999999")) {
        res.append("あと<span class=\"txt_bold\">").append(SIUtil.sub("10", getOrderCountLastOneYear())).append("回以上</span>のご購入で翌月の会員ランク判定よりダイヤモンドVIP会員になります。<br>");
      } else {
        if (SICheckUtil.isGreater(SIUtil.sub("10", getOrderCountLastOneYear()),SIUtil.sub("30", getOrderCount()))) {
          res.append("あと<span class=\"txt_bold\">").append(SIUtil.sub("10", getOrderCountLastOneYear())).append("回以上</span>かつ");
        } else {
          res.append("あと<span class=\"txt_bold\">").append(SIUtil.sub("30", getOrderCount())).append("回以上</span>かつ");
        }
        res.append("合計<span class=\"txt_bold\">￥").append(SIUtil.NumberFormat(SIUtil.sub("15000000", getPaymentPrice()))).append("以上</span>のご購入で翌月の会員ランク判定よりダイヤモンドVIP会員になります。<br>");
      }
      msgFlg = 2;
    //ゴールド維持の場合
    } else if ("1".equals(getMemberLevelCode())){
      if (SICheckUtil.isGreater(getOrderCountLastOneYear(),"2")&&SICheckUtil.isGreater(getOrderCount(),"9")) {
        res.append("あと合計<span class=\"txt_bold\">￥").append(SIUtil.NumberFormat(SIUtil.sub("1500000", getPaymentPrice()))).append("以上</span>のご購入で翌月の会員ランク判定よりプラチナVIP会員になります。<br>");
      } else if (SICheckUtil.isGreater(getOrderCountLastOneYear(),"2")&&SICheckUtil.isGreater(getPaymentPrice(),"1499999")) {
        res.append("あと<span class=\"txt_bold\">").append(SIUtil.sub("10", getOrderCount())).append("回以上</span>のご購入で翌月の会員ランク判定よりプラチナVIP会員になります。<br>");
      } else if (SICheckUtil.isGreater(getOrderCount(),"9")&&SICheckUtil.isGreater(getPaymentPrice(),"1499999")) {
        res.append("あと<span class=\"txt_bold\">").append(SIUtil.sub("3", getOrderCountLastOneYear())).append("回以上</span>のご購入で翌月の会員ランク判定よりプラチナVIP会員になります。<br>");
      } else {
        if (SICheckUtil.isGreater(SIUtil.sub("3", getOrderCountLastOneYear()),SIUtil.sub("10", getOrderCount()))) {
          res.append("あと<span class=\"txt_bold\">").append(SIUtil.sub("3", getOrderCountLastOneYear())).append("回以上</span>かつ");
        } else {
          res.append("あと<span class=\"txt_bold\">").append(SIUtil.sub("10", getOrderCount())).append("回以上</span>かつ");
        }
        res.append("合計<span class=\"txt_bold\">￥").append(SIUtil.NumberFormat(SIUtil.sub("1500000", getPaymentPrice()))).append("以上</span>のご購入で翌月の会員ランク判定よりプラチナVIP会員になります。<br>");
      }
      msgFlg = 2;
    //ゴールド昇格の場合
    } else if (SICheckUtil.isGreater(getOrderCountLastOneYear(),"0")&&SICheckUtil.isGreater(getOrderCount(),"4")&&SICheckUtil.isGreater(getPaymentPrice(),"299999")) {
      res.append("<span class=\"txt_bold\">次回の判定よりゴールドVIP会員になります。</span><br>");
      if (SICheckUtil.isGreater(getOrderCountLastOneYear(),"2")&&SICheckUtil.isGreater(getOrderCount(),"9")) {
        res.append("あと合計<span class=\"txt_bold\">￥").append(SIUtil.NumberFormat(SIUtil.sub("1500000", getPaymentPrice()))).append("以上</span>のご購入で翌月の会員ランク判定よりプラチナVIP会員になります。<br>");
      } else if (SICheckUtil.isGreater(getOrderCountLastOneYear(),"2")&&SICheckUtil.isGreater(getPaymentPrice(),"1499999")) {
        res.append("あと<span class=\"txt_bold\">").append(SIUtil.sub("10", getOrderCount())).append("回以上</span>のご購入で翌月の会員ランク判定よりプラチナVIP会員になります。<br>");
      } else if (SICheckUtil.isGreater(getOrderCount(),"9")&&SICheckUtil.isGreater(getPaymentPrice(),"1499999")) {
        res.append("あと<span class=\"txt_bold\">").append(SIUtil.sub("3", getOrderCountLastOneYear())).append("回以上</span>のご購入で翌月の会員ランク判定よりプラチナVIP会員になります。<br>");
      } else {
        if (SICheckUtil.isGreater(SIUtil.sub("3", getOrderCountLastOneYear()),SIUtil.sub("10", getOrderCount()))) {
          res.append("あと<span class=\"txt_bold\">").append(SIUtil.sub("3", getOrderCountLastOneYear())).append("回以上</span>かつ");
        } else {
          res.append("あと<span class=\"txt_bold\">").append(SIUtil.sub("10", getOrderCount())).append("回以上</span>かつ");
        }
        res.append("合計<span class=\"txt_bold\">￥").append(SIUtil.NumberFormat(SIUtil.sub("1500000", getPaymentPrice()))).append("以上</span>のご購入で翌月の会員ランク判定よりプラチナVIP会員になります。<br>");
      }
      msgFlg = 1;
    //通常会員の場合（降格含む）
    } else {
      if (SICheckUtil.isGreater(getOrderCountLastOneYear(),"0")&&SICheckUtil.isGreater(getOrderCount(),"4")) {
        res.append("あと<span class=\"txt_bold\">合計￥").append(SIUtil.NumberFormat(SIUtil.sub("300000", getPaymentPrice()))).append("以上</span>のご購入で翌月の会員ランク判定よりゴールドVIP会員になります。<br>");
      } else if (SICheckUtil.isGreater(getOrderCountLastOneYear(),"0")&&SICheckUtil.isGreater(getPaymentPrice(),"299999")) {
        res.append("あと<span class=\"txt_bold\">").append(SIUtil.sub("5", getOrderCount())).append("回以上</span>のご購入で翌月の会員ランク判定よりゴールドVIP会員になります。<br>");
      } else if (SICheckUtil.isGreater(getOrderCount(),"4")&&SICheckUtil.isGreater(getPaymentPrice(),"299999")) {
        res.append("あと<span class=\"txt_bold\">1回以上</span>のご購入で翌月の会員ランク判定よりゴールドVIP会員になります。<br>");
      } else {
        res.append("あと<span class=\"txt_bold\">").append(SIUtil.sub("5", getOrderCount())).append("回以上</span>かつ");
        res.append("合計<span class=\"txt_bold\">￥").append(SIUtil.NumberFormat(SIUtil.sub("300000", getPaymentPrice()))).append("以上</span>のご購入で翌月の会員ランク判定よりゴールドVIP会員になります。<br>");
      }
      msgFlg = 1;
    }
    if (msgFlg == 3) {
      res.append("ダイヤモンドVIP会員になると、表示価格より全商品8％OFFなど、さらに豪華な特典が盛りだくさんです！");
    } else if (msgFlg == 2) {
      res.append("プラチナVIP会員になると、表示価格より全商品5％OFFなど、さらに豪華な特典が盛りだくさんです！");
    } else if (msgFlg == 1) {
      res.append("ゴールドVIP会員になると、表示価格より全商品2％OFFなど、豊富な特典が盛りだくさんです！");
    }
    
    return res.toString();
  }
  
  public boolean isReAuth(){
    boolean res = false;
    if (SIUtil.isNotNull(getCcLastOrderCode()) && SIUtil.isNotNull(getCcLastOrderDate())) {
      try{
        SIDateTime lastDate = new SIDateTime();
        lastDate.addYear(-1);
        res = SIUtil.isNotNull(getCardNumber1()) && SIUtil.isNotNull(getCardNumber2()) && SICheckUtil.dateGreater(getCcLastOrderDate(), lastDate.getFullDate());
      }catch(Exception e){
        e.printStackTrace();
        res=false;
      }
    }
    return res;
  }
}