/**
 * @version $Id : UIShopListCond.java,v 1.0 Exp $
 * @author : xxxx xx <br>
 *         Description :顧客購入履歴の条件部のbeansクラス
 *         <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>
 *         2003/08/05 Original
 */
package jp.co.sint.beans.mallmgr;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
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.config.SIConfig;
import jp.co.sint.config.SIDBMultiConf;
import jp.co.sint.config.SIFlagConf;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.database.SIDateTimeType;
import jp.co.sint.database.SITableCondition;
import jp.co.sint.database.SITableConditionManager;
import jp.co.sint.tools.SICheckDataConf;
import jp.co.sint.tools.SICheckUtil;
import jp.co.sint.tools.SICheckValid;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIDateTime;
import jp.co.sint.tools.SIFlagUtil;
import jp.co.sint.tools.SIURLParameter;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;

public class UIPurchaseHistory extends SIBasic {
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  // 顧客コード
  private String custCode = "";
  
  // 顧客名
  private String custName = "";
  
  // 顧客名カナ
  private String custKana = "";
  
  // 店舗電話番号
  private String storeTel = "";
  
  // 電話番号
  private String telNo = "";
  
  // 登録経路
  private String registRoute = "";
  
  // 担当者
  private String charge = "";
  
  // メールアドレス
  private String mail = "";
  
  // 住所
  private String address = "";
  
  // 顧客属性１
  private String job = "";
  
  // 顧客属性２
  private String howToKnow = "";
  
  // 顧客属性３
  private String[] hobby = new String[0];
  
  // 契約済ブランド
  private String[] maker = new String[0];
  
  private String otherMenu = "";
  
  // 購入日From（年）
  private String purchaseDateFromYear = "";
  
  // 購入日From（月）
  private String purchaseDateFromMonth = "";
  
  // 購入日From（日）
  private String purchaseDateFromDay = "";
  
  // 購入日To（年）
  private String purchaseDateToYear = "";
  
  // 購入日To（月）
  private String purchaseDateToMonth = "";
  
  // 購入日To（日）
  private String purchaseDateToDay = "";
  
  // 総合計金額
  private String total = "";
  
  // 戻るURL
  private String backURL = "";
  
  // SQLの条件文
  private String conditionSQL = "";
  
  // 会員種別固定フラグ
  private String memberLevelFixedFlg = "0";
  
  // 会員種別コード
  private String memberLevelCode = "0";
  
  // 会員種別名称
  private String memberLevelName = "";
  
  // コンストラクタ
  public UIPurchaseHistory(HttpServletRequest lRequest, SIURLParameter lUrlParam) {
    init(lRequest, lUrlParam);
    if (!SICheckUtil.isDate(getInitDateFrom())) {
      SIDateTime lDate = new SIDateTime();
      lDate.addYear(-1);
      this.setPurchaseDateFromYear(lDate.getYearStr());
      this.setPurchaseDateFromMonth(lDate.getMonthStr());
      this.setPurchaseDateFromDay(lDate.getDayStr());
      // 条件文の設定
      SITableConditionManager lConditionMan = new SITableConditionManager();
      lConditionMan.add(new SITableCondition("a", "initDateTime", getInitDateFrom(), SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      this.conditionSQL = lConditionMan.getCondtionSQL();
    }
  }
  
  public UIPurchaseHistory() {
    SIDateTime lDate = new SIDateTime();
    lDate.addYear(-1);
    this.setPurchaseDateFromYear(lDate.getYearStr());
    this.setPurchaseDateFromMonth(lDate.getMonthStr());
    this.setPurchaseDateFromDay(lDate.getDayStr());
    // 条件文の設定
    SITableConditionManager lConditionMan = new SITableConditionManager();
    lConditionMan.add(new SITableCondition("a", "initDateTime", getInitDateFrom(), SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    this.conditionSQL = lConditionMan.getCondtionSQL();
  }
  
  public void init(HttpServletRequest lRequest, SIURLParameter lUrlParam) {
    super.init(lRequest, lUrlParam);
    this.initUrl(lUrlParam);
    
    this.setPurchaseDateFromYear((String) lUrlParam.getParam("purchaseDateFromYear"));
    this.setPurchaseDateFromMonth((String) lUrlParam.getParam("purchaseDateFromMonth"));
    this.setPurchaseDateFromDay((String) lUrlParam.getParam("purchaseDateFromDay"));
    this.setPurchaseDateToYear((String) lUrlParam.getParam("purchaseDateToYear"));
    this.setPurchaseDateToMonth((String) lUrlParam.getParam("purchaseDateToMonth"));
    this.setPurchaseDateToDay((String) lUrlParam.getParam("purchaseDateToDay"));
  }
  
  public void initUrl(SIURLParameter lUrlParam) {
    this.setBackURL((String) lUrlParam.getParam("backURL"));
  }
  
  /**
   * getCollection
   * 
   * @param Connection
   * @return Collection
   * @throws SIDBAccessException
   */
  public Collection getCollection(Connection lConnection) throws SIDBAccessException {
    Collection purchase = new ArrayList();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    UIPurchaseHistoryCond purchaseCond = new UIPurchaseHistoryCond();
    StringBuffer sqlStatement = new StringBuffer();
    StringBuffer lCountBuf = new StringBuffer();
    StringBuffer lItemBuf = new StringBuffer();
    StringBuffer lTotalBuf = new StringBuffer();
    this.total = "0";
    // 出力項目
    lItemBuf.append("SELECT a.initdatetime,a.status,a.ordercode,a.paymethodName,a.memo ");
    lItemBuf.append(",b.cmdtycode,b.individualcode,b.cmdtyName,b.price,b.amount,b.shippmentdate,b.deliverycode,b.detailcode ");
    lItemBuf.append(",b.amount*b.price as total ");
    lItemBuf.append(",c.usednewFlg");
    lItemBuf.append(",CASE a.orderRoute ");
    for (int i=0;i<SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length;i++) {
      lItemBuf.append(" WHEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][1]));
      lItemBuf.append(" THEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][0]));
    }
    lItemBuf.append(" ELSE ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length-1][0]));
    lItemBuf.append(" END AS orderRoute ");
    /*
    lItemBuf.append(",CASE a.orderRoute WHEN '0' THEN 'EC' WHEN '1' THEN '来店' WHEN '2' THEN '電話' WHEN '3' THEN 'メール' WHEN '4' THEN 'FAX' ");
    lItemBuf.append("WHEN '5' THEN 'その他' WHEN '6' THEN '携帯' WHEN '7' THEN '楽天' WHEN '8' THEN 'スマートフォン' WHEN '9' THEN 'イベント' ");
    lItemBuf.append("WHEN 'A' THEN 'イベントFAX' WHEN 'B' THEN 'ELG' WHEN 'C' THEN 'アプリ' ELSE 'その他' END AS orderRoute ");
    */
    lItemBuf.append(",CASE WHEN a.status != '1' THEN 1 WHEN deliveryedflg<>'0' THEN 1 ELSE 0 END AS updateFlg ");
    lItemBuf.append(",d.colorName,e.chargeName ");
    lCountBuf.append("SELECT count(a.*) ");
    lTotalBuf.append("SELECT SUM(b.price*b.amount) ");
    sqlStatement.append("FROM ordersumvw a LEFT OUTER JOIN chargetbl e ON a.chargecode = e.chargecode ");
    sqlStatement.append(",orderdetailtbl b, cmdtyunittbl c, colortbl d ");
    sqlStatement.append("WHERE a.custcode =").append(SIDBUtil.SQL2Str(this.custCode," "));
    sqlStatement.append(this.conditionSQL);
    sqlStatement.append("AND a.ordercode = b.ordercode AND a.orderbranchcode = b.orderbranchcode ");
    sqlStatement.append("AND b.shopcode = c.shopcode AND b.cmdtycode = c.cmdtycode AND b.individualcode = c.individualcode ");
    sqlStatement.append("AND c.colorcode = d.colorcode ");
    lCountBuf.append(sqlStatement);
    lTotalBuf.append(sqlStatement);
    lItemBuf.append(sqlStatement);
    lItemBuf.append("ORDER BY a.initdatetime DESC,b.deliverycode DESC,b.detailcode DESC");
    
    // 実行
    try {
      String rowCnt = SIDBUtil.getFirstData(lConnection, lCountBuf.toString());
      
      this.setTotal(SIDBUtil.getFirstData(lConnection, lTotalBuf.toString()));
      if (rowCnt.equals("") || rowCnt == null) {
        rowCnt = "0";
      }
      int lRecordCount = Integer.parseInt(rowCnt);// レコード数の取得
      
      int lPageSize = this.getPageSize();// ページサイズ
      int lPageNumber = this.getPageNumer();// ページ番号
      
      int lMaxPage = lRecordCount / lPageSize;
      if (lRecordCount % lPageSize > 0) {
        lMaxPage++;
      }
      if (lPageNumber > lMaxPage) lPageNumber = lMaxPage;
      
      // 開始レコードの番号の設定
      int lFromInx = (lPageNumber - 1) * lPageSize;
      // 終止のレコード番号の設定
      int lToInx = lFromInx + lPageSize - 1;
      if (lFromInx < 0) lFromInx = 0;
      if (lToInx < 0) lToInx = 0;
      
      log.debug("getCollection:lFromInx=" + lFromInx + ",lToInx=" + lToInx + ",lRecordCount=" + lRecordCount);
      
      log.debug("getCollection:lSqlBuf=" + lItemBuf.toString());
      lStatement = lConnection.createStatement();
      lItemBuf.append(" LIMIT ").append(lPageSize);
      if(lFromInx>0) lItemBuf.append(" OFFSET ").append(lFromInx);
      lResultSet = lStatement.executeQuery(lItemBuf.toString());
      
      for (int jj = 0; jj < lFromInx; jj++)
        purchase.add(null);
      
      int lIndex = 0;
      
      //コレクションの作成
      while (lResultSet.next() && lIndex < lPageSize) {
        purchaseCond = new UIPurchaseHistoryCond();
        purchaseCond.setPurchaseDay(SIDBUtil.getDate(lResultSet.getTimestamp("initdatetime")));
        purchaseCond.setShippmentDate(SIDBUtil.getDate(lResultSet.getTimestamp("shippmentdate")));
        purchaseCond.setOrderCode(lResultSet.getString("ordercode"));
        purchaseCond.setOrderRoute(lResultSet.getString("orderroute"));
        purchaseCond.setChargeName(lResultSet.getString("chargename"));
        purchaseCond.setStatus(lResultSet.getString("status"));
        purchaseCond.setStockCode(lResultSet.getString("individualcode"));
        purchaseCond.setCmdtyName(lResultSet.getString("cmdtyname"));
        purchaseCond.setSalesUnitPrice(lResultSet.getString("price"));
        purchaseCond.setAmount(lResultSet.getString("amount"));
        purchaseCond.setTotalMoney(lResultSet.getString("total"));
        purchaseCond.setUsedNewName(lResultSet.getString("usednewFlg"));
        purchaseCond.setColorName(lResultSet.getString("colorName"));
        purchaseCond.setPaymethodName(lResultSet.getString("paymethodName"));
        purchaseCond.setUpdateFlg(lResultSet.getString("updateFlg"));
        purchaseCond.setMemo(lResultSet.getString("memo"));
        if ("1".equals(lResultSet.getString("deliverycode")) &&"1".equals(lResultSet.getString("detailcode"))){
          purchaseCond.setMemoFlg("1");
        }
        purchase.add(purchaseCond);
        lIndex++;
      }
      for (int jj = lFromInx + lPageSize; jj < lRecordCount; jj++) purchase.add(null);
    } catch (Exception ex) {
      log.debug("exception sql = " + sqlStatement.toString());
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return purchase;
  }
  
  public void initDetail(SIURLParameter urlParam,Connection lConnection) {
    this.setCustCode((String) urlParam.getParam("custCode"));
    Statement lStatement = null;
    ResultSet lResultSet = null;
    StringBuffer lSql = new StringBuffer();
    
    lSql.append("SELECT a.custName,a.custPronName,a.tel,a.storeTel,a.registRoute,b.chargeName FROM custtbl a ");
    lSql.append("LEFT OUTER JOIN chargetbl b ON a.chargecode=b.chargecode ");
    lSql.append("WHERE a.custCode = ").append(SIDBUtil.SQL2Str(this.custCode));
    
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      
      if (lResultSet.next()){
        this.setCustName(lResultSet.getString("custName"));
        this.setCustKana(lResultSet.getString("custPronName"));
        this.setTelNo(lResultSet.getString("tel"));
        this.setStoreTel(lResultSet.getString("storeTel"));
        this.setRegistRoute(SIFlagUtil.getFlagName(SIFlagConf.SIFLAG_ORDER_ROUTE_INX,lResultSet.getString("registRoute")));
        this.setCharge(lResultSet.getString("chargeName"));
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      SIDBUtil.close(lStatement, lResultSet);
    }
  }
  
  public boolean orderReset(Connection lConnection, String lOrderCode) {
    boolean res = true;
    Statement statement = null;
    ResultSet resultSet = null;
    StringBuffer lSqlBuf = new StringBuffer();
    if (!SICheckUtil.isDigit(lOrderCode)) return false;
    lSqlBuf.append("SELECT a.custcode,a.custname,a.custpronname,a.storetel,a.tel,c.chargeName ");
    lSqlBuf.append(",a.email,a.postcode1,a.postcode2,a.address1,a.address2,a.address3,a.job,a.howtoknow,a.otherMenu ");
    lSqlBuf.append(",CASE a.registRoute ");
    for (int i=0;i<SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length;i++) {
      lSqlBuf.append(" WHEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][1]));
      lSqlBuf.append(" THEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][0]));
    }
    lSqlBuf.append(" ELSE ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length-1][0]));
    lSqlBuf.append(" END AS registRoute ");
    /*
    lSqlBuf.append(",CASE a.registRoute WHEN '0' THEN 'EC' WHEN '1' THEN '来店' WHEN '2' THEN '電話' WHEN '3' THEN 'メール' ");
    lSqlBuf.append(" WHEN '4' THEN 'FAX' WHEN '5' THEN 'その他' WHEN '6' THEN '携帯' ");
    lSqlBuf.append(" WHEN '7' THEN '楽天' WHEN '8' THEN 'スマートフォン' WHEN '9' THEN 'イベント' ");
    lSqlBuf.append(" WHEN 'A' THEN 'イベントFAX' WHEN 'B' THEN 'ELG' WHEN 'C' THEN 'アプリ' ELSE 'その他' END AS registRoute ");
    */
    lSqlBuf.append(",a.memberlevelcode,a.memberlevelfixedflg,d.memberlevelname ");
    lSqlBuf.append("FROM custtbl AS a ");
    lSqlBuf.append("LEFT OUTER JOIN chargetbl c ON a.chargecode=c.chargecode ");
    lSqlBuf.append(",orderlatestvw b,memberlevelmtbl d ");
    lSqlBuf.append("WHERE b.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode, " "));
    lSqlBuf.append("AND a.custcode=b.custcode AND a.memberlevelcode=d.memberlevelcode ");
    try {
      statement = lConnection.createStatement();
      log.debug("CustSQL=" + lSqlBuf.toString());
      resultSet = statement.executeQuery(lSqlBuf.toString());
      if (resultSet.next()) {
        this.setCustCode(resultSet.getString("custcode"));
        this.setCustName(resultSet.getString("custname"));
        this.setCustKana(resultSet.getString("custpronname"));
        this.setTelNo(resultSet.getString("tel"));
        this.setStoreTel(resultSet.getString("storetel"));
        this.setRegistRoute(resultSet.getString("registRoute"));
        this.setCharge(resultSet.getString("chargeName"));
        this.setMail(resultSet.getString("email"));
        this.setAddress("〒" + resultSet.getString("postcode1") + "-" + resultSet.getString("postcode2") + resultSet.getString("address1"));
        if (SIUtil.isNotNull(resultSet.getString("address2"))) this.setAddress(this.getAddress() + resultSet.getString("address2"));
        if (SIUtil.isNotNull(resultSet.getString("address3"))) this.setAddress(this.getAddress() + resultSet.getString("address3"));
        this.setJob(resultSet.getString("job"));
        this.setHowToKnow(resultSet.getString("howToKnow"));
        this.setOtherMenu(resultSet.getString("otherMenu"));
        this.setMemberLevelCode(resultSet.getString("memberLevelCode"));
        this.setMemberLevelName(resultSet.getString("memberLevelName"));
        this.setMemberLevelFixedFlg(resultSet.getString("memberLevelFixedFlg"));
        this.resetHobby(lConnection);
        this.resetMaker(lConnection);
        res = true;
      } else {
        res = false;
      }
    } catch (Exception e) {
      e.printStackTrace();
      res = false;
    } finally {
      SIDBUtil.close(statement, resultSet);
    }
    return res;
  }
  
  public boolean custReset(Connection lConnection, String lCustCode) {
    boolean res = true;
    Statement statement = null;
    ResultSet resultSet = null;
    StringBuffer lSqlBuf = new StringBuffer();
    if (!SICheckUtil.isDigit(lCustCode)) return false;
    lSqlBuf.append("SELECT a.custcode,a.custname,a.custpronname,a.storetel,a.tel,c.chargeName,a.memberlevelfixedflg,a.memberlevelcode,b.memberlevelname ");
    lSqlBuf.append(",a.email,a.postcode1,a.postcode2,a.address1,a.address2,a.address3,a.job,a.howtoknow,a.otherMenu ");
    lSqlBuf.append(",CASE a.registRoute ");
    for (int i=0;i<SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length;i++) {
      lSqlBuf.append(" WHEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][1]));
      lSqlBuf.append(" THEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][0]));
    }
    lSqlBuf.append(" ELSE ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length-1][0]));
    lSqlBuf.append(" END AS registRoute ");
    /*
    lSqlBuf.append(",CASE a.registRoute WHEN '0' THEN 'EC' WHEN '1' THEN '来店' WHEN '2' THEN '電話' WHEN '3' THEN 'メール' ");
    lSqlBuf.append(" WHEN '4' THEN 'FAX' WHEN '5' THEN 'その他' WHEN '6' THEN '携帯' ");
    lSqlBuf.append(" WHEN '7' THEN '楽天' WHEN '8' THEN 'スマートフォン' WHEN '9' THEN 'イベント' ");
    lSqlBuf.append(" WHEN 'A' THEN 'イベントFAX' WHEN 'B' THEN 'ELG' WHEN 'C' THEN 'アプリ' ELSE 'その他' END AS registRoute ");
    */
    lSqlBuf.append("FROM custtbl AS a ");
    lSqlBuf.append("LEFT OUTER JOIN chargetbl c ON a.chargecode=c.chargecode ");
    lSqlBuf.append(",memberlevelmtbl b ");
    lSqlBuf.append("WHERE a.custcode=").append(SIDBUtil.SQL2Str(lCustCode, " "));
    lSqlBuf.append("AND a.memberlevelcode=b.memberlevelcode ");
    try {
      statement = lConnection.createStatement();
      log.debug("CustSQL=" + lSqlBuf.toString());
      resultSet = statement.executeQuery(lSqlBuf.toString());
      if (resultSet.next()) {
        this.setCustCode(resultSet.getString("custcode"));
        this.setCustName(resultSet.getString("custname"));
        this.setCustKana(resultSet.getString("custpronname"));
        this.setTelNo(resultSet.getString("tel"));
        this.setStoreTel(resultSet.getString("storetel"));
        this.setRegistRoute(resultSet.getString("registRoute"));
        this.setCharge(resultSet.getString("chargeName"));
        this.setMail(resultSet.getString("email"));
        this.setAddress("〒" + resultSet.getString("postcode1") + "-" + resultSet.getString("postcode2") + resultSet.getString("address1"));
        if (SIUtil.isNotNull(resultSet.getString("address2"))) this.setAddress(this.getAddress() + resultSet.getString("address2"));
        if (SIUtil.isNotNull(resultSet.getString("address3"))) this.setAddress(this.getAddress() + resultSet.getString("address3"));
        this.setJob(resultSet.getString("job"));
        this.setHowToKnow(resultSet.getString("howToKnow"));
        this.setOtherMenu(resultSet.getString("otherMenu"));
        this.setMemberLevelFixedFlg(resultSet.getString("memberLevelFixedFlg"));
        this.setMemberLevelCode(resultSet.getString("memberLevelCode"));
        this.setMemberLevelName(resultSet.getString("memberLevelName"));
        this.resetHobby(lConnection);
        this.resetMaker(lConnection);
        res = true;
      } else {
        res = false;
      }
    } catch (Exception e) {
      e.printStackTrace();
      res = false;
    } finally {
      SIDBUtil.close(statement, resultSet);
    }
    return res;
  }
  
  public void validate(HttpServletRequest lRequest) {
    SICustomErrors errors = new SICustomErrors();
    SITableConditionManager lConditionMan = new SITableConditionManager();
    
    // 購入日(From)
    if (SIUtil.isNotNull(getInitDateFrom()) && SICheckValid.checkValid(errors, "購入日From", getInitDateFrom(), SICheckDataConf.SICHECK_DATA_DATE_TYPE)) {// 7.1.1 ST0162 修正
      if (SIDBMultiConf.SIDB_CURRENT_INX == SIDBMultiConf.SIDB_POSTGRESQL_INX) {
        lConditionMan.add(new SITableCondition("a", "initDateTime", getInitDateFrom(), SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      } else {
        lConditionMan.add(new SITableCondition("a", "initDateTime", new SIDateTimeType(getInitDateFrom()), SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      }
    }
    
    // 購入日(To)
    if (SIUtil.isNotNull(getInitDateTo()) && SICheckValid.checkValid(errors, "購入日To", getInitDateTo(), SICheckDataConf.SICHECK_DATA_DATE_TYPE)) {// 7.1.1 ST0162 修正
      if (SIDBMultiConf.SIDB_CURRENT_INX == SIDBMultiConf.SIDB_POSTGRESQL_INX) {
        lConditionMan.add(new SITableCondition("a", "initDateTime", getInitDateTo() + " 23:59:59", SIConfig.SICONDITION_TYPE_LESS_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      } else {
        lConditionMan.add(new SITableCondition("a", "initDateTime", new SIDateTimeType(getInitDateTo() + " 23:59:59"), SIConfig.SICONDITION_TYPE_LESS_EQUAL,
            SIConfig.SICONDITION_TYPE_AND));
      }
    }
    
    // 購入日大小
    try {
      if (!SICheckUtil.dateEqual(this.getInitDateFrom(), this.getInitDateTo())) {
        SICheckValid.checkValid(errors, "受注日From", "受注日To", this.getInitDateFrom(), this.getInitDateTo(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);// 7.1.1 ST0162 修正
      }
    } catch (Exception e) {}
    if (!errors.isEmpty()) {
      this.conditionSQL = "";
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
    } else {
      lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    }
    // 条件文の設定
    this.conditionSQL = lConditionMan.getCondtionSQL();
  }
  
  public Collection getMemoCollection(Connection lConnection) throws SIDBAccessException {
    Statement statement = null;
    ResultSet resultSet = null;
    StringBuffer str = new StringBuffer();
    Collection returnColl = new ArrayList();
    if (SIUtil.isNull(this.getCustCode())) return returnColl;
    UIPurchaseHistoryMemo lMemo = null;
    
    str.append("SELECT * FROM purchaseCommentTbl WHERE custCode = ").append(SIDBUtil.SQL2Str(this.getCustCode()," "));
    str.append("ORDER BY initdatetime DESC");
    try {
      statement = lConnection.createStatement();
      log.debug("MemoSQL=" + str.toString());
      resultSet = statement.executeQuery(str.toString());
      while (resultSet.next()) {
        lMemo = new UIPurchaseHistoryMemo();
        lMemo.setCommentNumber(resultSet.getString("commentNumber"));
        lMemo.setInitDateTime(SIDBUtil.getDateTime(resultSet.getTimestamp("initDateTime")));
        lMemo.setUpdateDateTime(SIDBUtil.getDateTime(resultSet.getTimestamp("updateDateTime")));
        lMemo.setUserName(resultSet.getString("userName"));
        lMemo.setMemo(resultSet.getString("comment"));
        lMemo.setMemoStatus(resultSet.getString("memoStatus"));
        lMemo.setLastUpdateUserName(resultSet.getString("lastUpdateUserName"));
        returnColl.add(lMemo);
      }
    } catch (Exception e) {
      e.printStackTrace();
      returnColl = new ArrayList();
    } finally {
      SIDBUtil.close(statement,resultSet);
    }
    return returnColl;
  }

  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 b.MakerName FROM CustMakerTbl a,MakerTbl b WHERE a.MakerCode=b.MakerCode AND a.CustCode=" + SIDBUtil.SQL2Str(getCustCode());
    log.debug("resetMaker:lSql=" + lSql);
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql);
      while (lResultSet.next()) {
        lMakerColl.add(lResultSet.getString("makerName"));
      }
      setEncode(SIConfig.SIENCODE_NONE);
      this.setMaker((String[]) lMakerColl.toArray(new String[0]));
    } catch (SQLException sqle) {
      sqle.printStackTrace();
    } finally {
      SIDBUtil.close(lStatement, lResultSet);
    }
  }
  /**
   * @return
   */
  public String getInitDateFrom() {
    return SIDateTime.getDate(getPurchaseDateFromYear(), getPurchaseDateFromMonth(), getPurchaseDateFromDay());
  }
  
  /**
   * @return
   */
  public String getInitDateTo() {
    return SIDateTime.getDate(getPurchaseDateToYear(), getPurchaseDateToMonth(), getPurchaseDateToDay());
  }
  
  /**
   * @return custCode を戻します。
   */
  public String getCustCode() {
    return custCode;
  }
  
  /**
   * @param custCode custCode を設定。
   */
  public void setCustCode(String custCode) {
    this.custCode = custCode;
  }
  
  /**
   * @return custKana を戻します。
   */
  public String getCustKana() {
    return custKana;
  }
  
  /**
   * @param custKana custKana を設定。
   */
  public void setCustKana(String custKana) {
    this.custKana = custKana;
  }
  
  /**
   * @return custName を戻します。
   */
  public String getCustName() {
    return custName;
  }
  
  /**
   * @param custName custName を設定。
   */
  public void setCustName(String custName) {
    this.custName = custName;
  }
  
  /**
   * @return purchaseDayFromDay を戻します。
   */
  public String getPurchaseDateFromDay() {
    return purchaseDateFromDay;
  }
  
  /**
   * @param purchaseDayFromDay purchaseDayFromDay を設定。
   */
  public void setPurchaseDateFromDay(String purchaseDayFromDay) {
    this.purchaseDateFromDay = purchaseDayFromDay;
  }
  
  /**
   * @return purchaseDayFromMonth を戻します。
   */
  public String getPurchaseDateFromMonth() {
    return purchaseDateFromMonth;
  }
  
  /**
   * @param purchaseDayFromMonth purchaseDayFromMonth を設定。
   */
  public void setPurchaseDateFromMonth(String purchaseDayFromMonth) {
    this.purchaseDateFromMonth = purchaseDayFromMonth;
  }
  
  /**
   * @return purchaseDayFromYear を戻します。
   */
  public String getPurchaseDateFromYear() {
    return purchaseDateFromYear;
  }
  
  /**
   * @param purchaseDayFromYear purchaseDayFromYear を設定。
   */
  public void setPurchaseDateFromYear(String purchaseDayFromYear) {
    this.purchaseDateFromYear = purchaseDayFromYear;
  }
  
  /**
   * @return purchaseDayToDay を戻します。
   */
  public String getPurchaseDateToDay() {
    return purchaseDateToDay;
  }
  
  /**
   * @param purchaseDayToDay purchaseDayToDay を設定。
   */
  public void setPurchaseDateToDay(String purchaseDayToDay) {
    this.purchaseDateToDay = purchaseDayToDay;
  }
  
  /**
   * @return purchaseDayToMonth を戻します。
   */
  public String getPurchaseDateToMonth() {
    return purchaseDateToMonth;
  }
  
  /**
   * @param purchaseDayToMonth purchaseDayToMonth を設定。
   */
  public void setPurchaseDateToMonth(String purchaseDayToMonth) {
    this.purchaseDateToMonth = purchaseDayToMonth;
  }
  
  /**
   * @return purchaseDayToYear を戻します。
   */
  public String getPurchaseDateToYear() {
    return purchaseDateToYear;
  }
  
  /**
   * @param purchaseDayToYear purchaseDayToYear を設定。
   */
  public void setPurchaseDateToYear(String purchaseDayToYear) {
    this.purchaseDateToYear = purchaseDayToYear;
  }
  
  /**
   * @return telNo を戻します。
   */
  public String getTelNo() {
    return telNo;
  }
  
  /**
   * @param telNo telNo を設定。
   */
  public void setTelNo(String telNo) {
    this.telNo = telNo;
  }
  
  public void setStoreTel(String lStoreTel) {
    this.storeTel = lStoreTel;
  }
  
  public String getStoreTel() {
    return storeTel;
  }
  
  /**
   * @return backURL を戻します。
   */
  public String getBackURL() {
    return backURL;
  }
  
  /**
   * @param backURL backURL を設定。
   */
  public void setBackURL(String backURL) {
    this.backURL = backURL;
  }
  
  /**
   * @return conditionSQL を戻します。
   */
  public String getConditionSQL() {
    return conditionSQL;
  }
  
  /**
   * @param conditionSQL conditionSQL を設定。
   */
  public void setConditionSQL(String conditionSQL) {
    this.conditionSQL = conditionSQL;
  }
  
  /**
   * @return total を戻します。
   */
  public String getTotal() {
    return total;
  }
  
  /**
   * @param total total を設定。
   */
  public void setTotal(String total) {
    this.total = total;
  }
  
  // getter of registRoute
  public String getRegistRoute() {
    return this.registRoute;
  }
  
  // setter of registRoute
  public void setRegistRoute(String registRoute) {
    if (SIUtil.isNull(registRoute)) registRoute = "";
    this.registRoute = SIUtil.changeTo(registRoute.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 getAddress() {
    return address;
  }

  public void setAddress(String address) {
    if (SIUtil.isNull(address)) address = "";
    this.address = SIUtil.changeTo(address.trim(), this.encode);
  }
  
  public String getMail() {
    return mail;
  }
  
  public void setMail(String mail) {
    if (SIUtil.isNull(mail)) mail = "";
    this.mail = SIUtil.changeTo(mail.trim(), this.encode);
  }
  
  public String getJob() {
    return job;
  }
  
  public void setJob(String job) {
    if (SIUtil.isNull(job)) job = "";
    this.job = SIUtil.changeTo(job.trim(), this.encode);
  }
  
  public String getHowToKnow() {
    return howToKnow;
  }
  
  public void setHowToKnow(String howToKnow) {
    if (SIUtil.isNull(howToKnow)) howToKnow = "";
    this.howToKnow = SIUtil.changeTo(howToKnow.trim(), this.encode);
  }
  
  public String[] getHobby() {
    return hobby;
  }
  
  public void setHobby(String[] hobby) {
    if (hobby == null) hobby = new String[0];
    this.hobby = hobby;
  }
  
  public String[] getMaker() {
    return maker;
  }
  
  public void setMaker(String[] maker) {
    if (maker == null) maker = new String[0];
    this.maker = maker;
  }
  
  public String getOtherMenu() {
    return otherMenu;
  }
  
  public void setOtherMenu(String otherMenu) {
    if (SIUtil.isNull(otherMenu)) otherMenu = "";
    this.otherMenu = SIUtil.changeTo(otherMenu.trim(), this.encode);
  }
  
  public String getMemberLevelFixedFlg() {
    return memberLevelFixedFlg;
  }
  
  public void setMemberLevelFixedFlg(String memberLevelFixedFlg) {
    if (SIUtil.isNull(memberLevelFixedFlg)) memberLevelFixedFlg = "0";
    this.memberLevelFixedFlg = memberLevelFixedFlg;
  }
  
  public String getMemberLevelCode() {
    return memberLevelCode;
  }
  
  public void setMemberLevelCode(String memberLevelCode) {
    if (SIUtil.isNull(memberLevelCode)) memberLevelCode = "0";
    this.memberLevelCode = memberLevelCode;
  }
  
  public String getMemberLevelName() {
    return memberLevelName;
  }
  
  public void setMemberLevelName(String memberLevelName) {
    if (SIUtil.isNull(memberLevelName)) memberLevelName = "";
    this.memberLevelName = memberLevelName;
  }
  
  
  
  
  
  public String getVipComment(Connection lConnection){
    if (SIUtil.isNull(custCode)) return "";
    StringBuffer lSql = new StringBuffer();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    String orderCount = "0";
    String paymentPrice = "0";
    String lastOrderDate = "";
    String orderCountLastOneYear = "0";
    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(",max(o.initdatetime) AS lastOrderDate ");
      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()){
        orderCount = lResultSet.getString("orderCount");
        paymentPrice = lResultSet.getString("paymentPrice");
        orderCountLastOneYear = lResultSet.getString("orderCountLastOneYear");
        lastOrderDate = SIDBUtil.getDateTime(lResultSet.getTimestamp("lastOrderDate"));
      }
      
      /*
      orderCount = "0";
      paymentPrice = "0";
      lastOrderDate = "";
      orderCountLastOneYear = "0";
      
      lSql.append("SELECT o.custcode,o.ordercode,p.paymentnumber ");
      lSql.append(",o.totalofprice+o.totalofdeliveryfee+o.totaloffee+coalesce(o.discountdeliveryfee,0)+coalesce(o.discountfee,0)-o.sumofdiscount-o.setdiscount-o.sumbypoint AS paymentPrice ");
      lSql.append(",CASE WHEN o.initdatetime > current_timestamp + '-1 years'::interval THEN 1 ELSE 0 END AS orderCountLastOneYear ");
      lSql.append(",o.initdatetime,p.paymentprice AS pprice ");
      lSql.append("FROM ordersumvw o,paymenttbl 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.ordercode=p.ordercode ");
      lSql.append("AND p.status<>1 ");
      lSql.append("AND p.paymentdate IS NOT NULL ");
      lSql.append("AND o.custcode=").append(SIDBUtil.SQL2Str(custCode," "));
      lSql.append("ORDER BY o.ordercode,p.paymentnumber ");
      
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      String lastOrder = "";;
      String orderTotal = "0";
      String orderPayment = "0";
      String initDate = "";
      
      while(lResultSet.next()){
        if (!lastOrder.equals(lResultSet.getString("orderCode"))) {
          if (!SICheckUtil.isGreater(orderTotal, orderPayment)&&!lResultSet.isFirst()){
            orderCount = SIUtil.add(orderCount, "1");
            orderCountLastOneYear = SIUtil.add(orderCountLastOneYear, lResultSet.getString("orderCountLastOneYear"));
            paymentPrice = SIUtil.add(paymentPrice, orderTotal);
          }
          orderTotal=lResultSet.getString("paymentPrice");
          orderPayment="0";
          initDate = SIDBUtil.getDateTime(lResultSet.getTimestamp("initDateTime"));
          if (lResultSet.isFirst()||SICheckUtil.dateGreater(initDate,lastOrderDate)) lastOrderDate=initDate;
        }
        orderPayment=SIUtil.add(orderPayment, lResultSet.getString("pprice"));
        
        if (lResultSet.isLast()&&!SICheckUtil.isGreater(orderTotal, orderPayment)){
          orderCount = SIUtil.add(orderCount, "1");
          orderCountLastOneYear = SIUtil.add(orderCountLastOneYear, lResultSet.getString("orderCountLastOneYear"));
          paymentPrice = SIUtil.add(paymentPrice, orderTotal);
        }
      }
      */
    }catch(Exception e){
      e.printStackTrace();
    }finally{
      SIDBUtil.close(lResultSet, lStatement);
    }
    
    StringBuffer res = new StringBuffer();
    res.append("現在の会員種別は").append(getMemberLevelName()).append("です。");
    res.append("\r\n【有効購入回数】").append(orderCount).append("回（1年以内に").append(orderCountLastOneYear).append("回）");
    res.append("\r\n【有効支払総額】￥").append(paymentPrice);
    res.append("\r\n【最終購入日時】").append(lastOrderDate);
    
    //ランク固定の場合
    if ("1".equals(getMemberLevelFixedFlg())){
      return res.toString();
    //ダイヤモンド維持の場合
    }else if ("3".equals(getMemberLevelCode())){
      return res.toString();
    //ダイヤモンド昇格の場合
    } else if (SICheckUtil.isGreater(orderCountLastOneYear,"9")&&SICheckUtil.isGreater(orderCount,"29")&&SICheckUtil.isGreater(paymentPrice,"14999999")) {
      res.append("\r\n\r\n次回の判定よりダイヤモンドVIP会員になります。");
    //プラチナ維持の場合
    } else if ("2".equals(getMemberLevelCode())){
      if (SICheckUtil.isGreater(orderCountLastOneYear,"9")&&SICheckUtil.isGreater(orderCount,"29")) {
        res.append("\r\n\r\nあと合計￥").append(SIUtil.NumberFormat(SIUtil.sub("15000000", paymentPrice))).append("以上のご購入で翌月の会員ランク判定以降よりダイヤモンドVIP会員になります。");
      } else if (SICheckUtil.isGreater(orderCountLastOneYear,"9")&&SICheckUtil.isGreater(paymentPrice,"14999999")) {
        res.append("\r\n\r\nあと").append(SIUtil.sub("30", orderCount)).append("回以上のご購入で翌月の会員ランク判定以降よりダイヤモンドVIP会員になります。");
      } else if (SICheckUtil.isGreater(orderCount,"29")&&SICheckUtil.isGreater(paymentPrice,"14999999")) {
        res.append("\r\n\r\nあと").append(SIUtil.sub("10", orderCountLastOneYear)).append("回以上のご購入で翌月の会員ランク判定以降よりダイヤモンドVIP会員になります。");
      } else {
        if (SICheckUtil.isGreater(SIUtil.sub("10", orderCountLastOneYear),SIUtil.sub("30", orderCount))) {
          res.append("\r\n\r\nあと").append(SIUtil.sub("10", orderCountLastOneYear)).append("回以上かつ");
        } else {
          res.append("\r\n\r\nあと").append(SIUtil.sub("30", orderCount)).append("回以上かつ");
        }
        res.append("合計￥").append(SIUtil.NumberFormat(SIUtil.sub("15000000", paymentPrice))).append("以上のご購入で翌月の会員ランク判定以降よりダイヤモンドVIP会員になります。");
      }
    //プラチナ昇格の場合
    } else if (SICheckUtil.isGreater(orderCountLastOneYear,"2")&&SICheckUtil.isGreater(orderCount,"9")&&SICheckUtil.isGreater(paymentPrice,"1499999")) {
      res.append("\r\n\r\n次回の判定よりプラチナVIP会員になります。");
      if (SICheckUtil.isGreater(orderCountLastOneYear,"9")&&SICheckUtil.isGreater(orderCount,"29")) {
        res.append("\r\nあと合計￥").append(SIUtil.NumberFormat(SIUtil.sub("15000000", paymentPrice))).append("以上のご購入で翌月の会員ランク判定以降よりダイヤモンドVIP会員になります。");
      } else if (SICheckUtil.isGreater(orderCountLastOneYear,"9")&&SICheckUtil.isGreater(paymentPrice,"14999999")) {
        res.append("\r\nあと").append(SIUtil.sub("30", orderCount)).append("回以上のご購入で翌月の会員ランク判定以降よりダイヤモンドVIP会員になります。");
      } else if (SICheckUtil.isGreater(orderCount,"29")&&SICheckUtil.isGreater(paymentPrice,"14999999")) {
        res.append("\r\nあと").append(SIUtil.sub("10", orderCountLastOneYear)).append("回以上のご購入で翌月の会員ランク判定以降よりダイヤモンドVIP会員になります。");
      } else {
        if (SICheckUtil.isGreater(SIUtil.sub("10", orderCountLastOneYear),SIUtil.sub("30", orderCount))) {
          res.append("\r\nあと").append(SIUtil.sub("10", orderCountLastOneYear)).append("回以上かつ");
        } else {
          res.append("\r\nあと").append(SIUtil.sub("30", orderCount)).append("回以上かつ");
        }
        res.append("合計￥").append(SIUtil.NumberFormat(SIUtil.sub("15000000", paymentPrice))).append("以上のご購入で翌月の会員ランク判定以降よりダイヤモンドVIP会員になります。");
      }
    //ゴールド維持の場合
    } else if ("1".equals(getMemberLevelCode())){
      if (SICheckUtil.isGreater(orderCountLastOneYear,"2")&&SICheckUtil.isGreater(orderCount,"9")) {
        res.append("\r\n\r\nあと合計￥").append(SIUtil.NumberFormat(SIUtil.sub("1500000", paymentPrice))).append("以上のご購入で翌月の会員ランク判定以降よりプラチナVIP会員になります。");
      } else if (SICheckUtil.isGreater(orderCountLastOneYear,"2")&&SICheckUtil.isGreater(paymentPrice,"1499999")) {
        res.append("\r\n\r\nあと").append(SIUtil.sub("10", orderCount)).append("回以上のご購入で翌月の会員ランク判定以降よりプラチナVIP会員になります。");
      } else if (SICheckUtil.isGreater(orderCount,"9")&&SICheckUtil.isGreater(paymentPrice,"1499999")) {
        res.append("\r\n\r\nあと").append(SIUtil.sub("3", orderCountLastOneYear)).append("回以上のご購入で翌月の会員ランク判定以降よりプラチナVIP会員になります。");
      } else {
        if (SICheckUtil.isGreater(SIUtil.sub("3", orderCountLastOneYear),SIUtil.sub("10", orderCount))) {
          res.append("\r\n\r\nあと").append(SIUtil.sub("3", orderCountLastOneYear)).append("回以上かつ");
        } else {
          res.append("\r\n\r\nあと").append(SIUtil.sub("10", orderCount)).append("回以上かつ");
        }
        res.append("合計￥").append(SIUtil.NumberFormat(SIUtil.sub("1500000", paymentPrice))).append("以上のご購入で翌月の会員ランク判定以降よりプラチナVIP会員になります。");
      }
    //ゴールド昇格の場合
    } else if (SICheckUtil.isGreater(orderCountLastOneYear,"0")&&SICheckUtil.isGreater(orderCount,"4")&&SICheckUtil.isGreater(paymentPrice,"299999")) {
      res.append("\r\n\r\n次回の判定よりゴールドVIP会員になります。");
      if (SICheckUtil.isGreater(orderCountLastOneYear,"2")&&SICheckUtil.isGreater(orderCount,"9")) {
        res.append("\r\nあと合計￥").append(SIUtil.NumberFormat(SIUtil.sub("1500000", paymentPrice))).append("以上のご購入で翌月の会員ランク判定以降よりプラチナVIP会員になります。");
      } else if (SICheckUtil.isGreater(orderCountLastOneYear,"2")&&SICheckUtil.isGreater(paymentPrice,"1499999")) {
        res.append("\r\nあと").append(SIUtil.sub("10", orderCount)).append("回以上のご購入で翌月の会員ランク判定以降よりプラチナVIP会員になります。");
      } else if (SICheckUtil.isGreater(orderCount,"9")&&SICheckUtil.isGreater(paymentPrice,"1499999")) {
        res.append("\r\nあと").append(SIUtil.sub("3", orderCountLastOneYear)).append("回以上のご購入で翌月の会員ランク判定以降よりプラチナVIP会員になります。");
      } else {
        if (SICheckUtil.isGreater(SIUtil.sub("3", orderCountLastOneYear),SIUtil.sub("10", orderCount))) {
          res.append("\r\nあと").append(SIUtil.sub("3", orderCountLastOneYear)).append("回以上かつ");
        } else {
          res.append("\r\nあと").append(SIUtil.sub("10", orderCount)).append("回以上かつ");
        }
        res.append("合計￥").append(SIUtil.NumberFormat(SIUtil.sub("1500000", paymentPrice))).append("以上のご購入で翌月の会員ランク判定以降よりプラチナVIP会員になります。");
      }
    //通常会員の場合（降格含む）
    } else {
      if (SICheckUtil.isGreater(orderCountLastOneYear,"0")&&SICheckUtil.isGreater(orderCount,"4")) {
        res.append("\r\n\r\nあと合計￥").append(SIUtil.NumberFormat(SIUtil.sub("300000", paymentPrice))).append("以上のご購入で翌月の会員ランク判定以降よりゴールドVIP会員になります。");
      } else if (SICheckUtil.isGreater(orderCountLastOneYear,"0")&&SICheckUtil.isGreater(paymentPrice,"299999")) {
        res.append("\r\n\r\nあと").append(SIUtil.sub("5", orderCount)).append("回以上のご購入で翌月の会員ランク判定以降よりゴールドVIP会員になります。");
      } else if (SICheckUtil.isGreater(orderCount,"4")&&SICheckUtil.isGreater(paymentPrice,"299999")) {
        res.append("\r\n\r\nあと1回以上のご購入で翌月の会員ランク判定以降よりゴールドVIP会員になります。");
      } else {
        res.append("\r\n\r\nあと").append(SIUtil.sub("5", orderCount)).append("回以上かつ");
        res.append("合計￥").append(SIUtil.NumberFormat(SIUtil.sub("300000", paymentPrice))).append("以上のご購入で翌月の会員ランク判定以降よりゴールドVIP会員になります。");
      }
    }
    
    return res.toString();
  }
  
  public String[] getTradeStatus(Connection lConnection) {
    String[] ts = new String[3];
    ts[0]="0";
    ts[1]="0";
    ts[2]="0";
    
    String orderCheck = "SELECT h.ordercode FROM ordertbl h,orderdetailtbl d WHERE h.ordercode=d.ordercode AND h.orderbranchcode=d.orderbranchcode AND h.status='1' AND d.shippmentdate IS NULL AND h.custcode="+SIDBUtil.SQL2Str(custCode)+" LIMIT 1";
    String reserveCheck = "SELECT reservecode FROM reserveordertbl WHERE reserveflg IN ('0','1','3') AND custcode="+SIDBUtil.SQL2Str(custCode)+" LIMIT 1";
    String estimateCheck = "SELECT e.estimatecode FROM estimatetbl e,keeptbl k WHERE e.keepnumber=k.keepnumber AND e.status=1 AND e.custcode="+SIDBUtil.SQL2Str(custCode)+" LIMIT 1";
    
    try{
     if(SIDBUtil.hasData(lConnection, orderCheck)) ts[0] = "1";
     if(SIDBUtil.hasData(lConnection, reserveCheck)) ts[1] = "1";
     if(SIDBUtil.hasData(lConnection, estimateCheck)) ts[2] = "1";
    }catch(Exception e){
      e.printStackTrace();
    }
    return ts;
  }
  
}