/**
 * Created on 2003/08/27
 *
 * To change the template for this generated file go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */
package jp.co.sint.beans.mallmgr;

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

import javax.servlet.http.HttpServletRequest;

import jp.co.sint.basic.SIBasic;
import jp.co.sint.basic.SILogin;
import jp.co.sint.basic.SIRanking;
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.tools.SICheckDataConf;
import jp.co.sint.tools.SICheckValid;
import jp.co.sint.tools.SICustomError;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIUtil;
import jp.co.sint.tools.SIURLParameter;//7.1.1 ST0236 追加

import org.apache.log4j.Category;
/**
 * @author arai
 *
 * To change the template for this generated type comment go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */
public class UIRankingList extends SIBasic {
  //ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  private String memberLevelCode = "";
  private String memberLevel = "";
  private String memberLevelName = "";
  private String discountRate = "";
  
  public UIRankingList() {}
  
  /**
   * init
   * 入力したデータから、このbeansを設定します。
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  //7.1.1 ST0236 修正ここから
  public void init(SIURLParameter lUrlParam) {
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    this.setMemberLevelCode((String)lUrlParam.getParam("memberLevelCode"));
    this.setMemberLevel((String)lUrlParam.getParam("memberLevel"));
    this.setMemberLevelName((String)lUrlParam.getParam("memberLevelName"));
    this.setDiscountRate((String)lUrlParam.getParam("discountRate"));
  }
  //7.1.1 ST0236 修正ここまで
  
  /**
   * validate
   * 入力したデータをチェックします。
   * 不正なデータがある場合、エラーをオブジェクトに格納します。
   * そのオブジェクトは、エラーメッセージとして画面に表示されます。
   * @param HttpServletRequest ，Connection , SIURLParameter
   * @return true:エラーがない false:エラーが１つ以上ある
   * @throws なし
   */
  public boolean validate(HttpServletRequest lRequest, Connection lConnection,SIURLParameter lUrlParam){//7.1.1 ST0236 修正
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    SICustomErrors errors = new SICustomErrors();
    SICustomError err;
    
    if (!((String)lUrlParam.getParam("editModeTxt")).equalsIgnoreCase(SIConfig.SIEDIT_MODE_INSERT)) {//7.1.1 ST0236 修正
      SICheckValid.checkValid(errors, "会員レベルコード", this.getMemberLevelCode(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
      SICheckValid.checkValid(errors, "会員レベルコード", this.getMemberLevelCode(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 6);
    }
    
    if (!((String)lUrlParam.getParam("editModeTxt")).equalsIgnoreCase(SIConfig.SIEDIT_MODE_DELETE)) {//7.1.1 ST0236 修正
      SICheckValid.checkValid(errors, "会員レベル", this.getMemberLevel(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
      SICheckValid.checkValid(errors, "会員レベル", this.getMemberLevel(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 2);
      
      SICheckValid.checkValid(errors, "会員種別名", this.getMemberLevelName(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "会員種別名", this.getMemberLevelName(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 50);
      
      //7.1.1 ST0162 修正 ここから
      SICheckValid.checkValid(errors, "値引率", this.getDiscountRate(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
      SICheckValid.checkValid(errors, "値引率", this.getDiscountRate(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 3);//7.2.0 ST0538 修正
      //7.1.1 ST0162 修正 ここまで
      SICheckValid.checkValid(errors, "値引率", this.getDiscountRate(), SICheckDataConf.SICHECK_DATA_NUMBER_BETWEEN_TYPE, 0 , 100);//7.2.0 ST0538 追加
    }
    
    if (!errors.isEmpty()) lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
    return errors.isEmpty();
  }
  
  /**
   * validateData
   * 対象コードでデータ削除可能かどうか調べます。
   *  @param HttpServletRequest ，Connection
   * @return true:データがある false:データがない
   * @throws なし
   */
  public boolean validateData(Connection lConnection) throws SIDBAccessException{
    StringBuffer lSqlBuf=new StringBuffer();
    lSqlBuf.append(" SELECT a.* , b.membercount FROM MemberLevelMtbl a ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT memberlevelcode,COUNT(custcode) as membercount FROM custtbl GROUP BY memberlevelcode ) b ");
    lSqlBuf.append("ON (a.memberlevelcode = b.memberlevelcode) WHERE membercount IS NULL ");
    lSqlBuf.append(" AND a.memberlevelcode=").append(this.getMemberLevelCode());
    lSqlBuf.append(" ORDER BY memberlevel ASC");
    
    return SIDBUtil.hasData(lConnection, lSqlBuf.toString());
  }
  
  /**
  * validateData
  * 対象コードがあるかどうか調べます。
  *  @param HttpServletRequest ，Connection
  * @return true:データがある false:データがない
  * @throws なし
  */
  public boolean validateDataExist(Connection lConnection) throws SIDBAccessException{
    StringBuffer lSqlBuf=new StringBuffer();
    lSqlBuf.append("SELECT a.memberlevelcode  FROM MemberLevelMtbl a");
    lSqlBuf.append("  WHERE a.memberlevelcode=").append(this.getMemberLevelCode());
    return SIDBUtil.hasData(lConnection, lSqlBuf.toString());
  }
  
  /**
   * getFirstColl
   * 編集対象のレコードを取得する。
   * @param
   * @return SIRanking
   * @throws
   */
  public SIRanking getFirstColl() {
    SIRanking lranking = new SIRanking();
    lranking.setEncode(SIConfig.SIENCODE_NONE);
    lranking.setMemberLevelCode(this.getMemberLevelCode());
    lranking.setMemberLevel(this.getMemberLevel());
    lranking.setMemberLevelName(this.getMemberLevelName());
    lranking.setDiscountRate(this.getDiscountRate());
    lranking.setActionNameTxt(this.getActionNameTxt());
    lranking.setEditModeTxt(this.getEditModeTxt());
    return lranking;
  }
  
  /**
   * getFirstColl
   * 編集対象のレコードを取得する。
   * @param Connection , String
   * @return SIRanking
   * @throws SIDBAccessException
   */
  public SIRanking getFirstColl(Connection lConnection, String pMemberLevelCode,SILogin lLogin) throws SIDBAccessException {
    //初期表示時(該当レコードがない場合)
    if (SIUtil.isNull(pMemberLevelCode)) {
      SIRanking ranking = new SIRanking();
      return ranking;
      //選択表示時(該当レコードがある場合)
    } else {
      return (SIRanking) getCollection(lConnection, pMemberLevelCode,lLogin).iterator().next();
    }
  }
  
  /**
   * getCollection
   * 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * @param Connection , String
   * @return Collection
   * @throws SIDBAccessException
   */
  public Collection getCollection(Connection lConnection, String pMemberLevelCode,SILogin lLogin) throws SIDBAccessException {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIRanking lRanking = new SIRanking();
    StringBuffer lSqlBuf = new StringBuffer();
    Collection ranking = new ArrayList();
    
    //基本のSQL
    lSqlBuf.append(" SELECT a.* , b.membercount ");
    lSqlBuf.append(" FROM MemberLevelMtbl a ");
    lSqlBuf.append(" LEFT OUTER JOIN ");
    lSqlBuf.append(" (SELECT aa.memberlevelcode,COUNT(aa.custcode) as membercount ");
    lSqlBuf.append(" FROM custtbl aa ");
    if(lLogin.isShop()){
      lSqlBuf.append(" ,ShopCustConnVW bb ");
      lSqlBuf.append(" WHERE bb.custcode=aa.custCode ");
      lSqlBuf.append(" AND bb.shopcode="+SIDBUtil.SQL2Str(lLogin.getMallShopCode()," "));
    //7.3.0 ST2027 追加 ここから
      lSqlBuf.append(" AND aa.delflg<"+new Integer(SIConfig.SIDEL_FLG_DELETE));
    }else{
      lSqlBuf.append(" WHERE aa.delflg<"+new Integer(SIConfig.SIDEL_FLG_DELETE));
    }
    //7.3.0 ST2027 追加 ここまで
    
    lSqlBuf.append(" GROUP BY memberlevelcode ) b ");
    lSqlBuf.append("ON (a.memberlevelcode = b.memberlevelcode) WHERE 1=1 ");
    if (pMemberLevelCode != null) {
      lSqlBuf.append(" AND a.memberlevelcode=" + pMemberLevelCode);
    }
    lSqlBuf.append(" ORDER BY memberlevel ASC");
    
    log.debug("lSqlBuf=" + lSqlBuf.toString());
    
    //実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      
      //商品レコードのセットの作成
      while (lResultSet.next()) {
        lRanking = new SIRanking();
        lRanking.setEncode(SIConfig.SIENCODE_NONE);
        lRanking.setMemberLevelCode(lResultSet.getString("memberlevelcode"));
        lRanking.setMemberLevel(lResultSet.getString("memberlevel"));
        lRanking.setMemberLevelName(lResultSet.getString("memberlevelname"));
        lRanking.setDiscountRate(lResultSet.getString("discountRate"));
        lRanking.setUpdateDateTime(SIDBUtil.getDateTime(lResultSet.getTimestamp("updatedatetime")));
        lRanking.setMemberCount(lResultSet.getString("memberCount"));
        ranking.add(lRanking);
      }
    } catch (Exception ex) {
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return ranking;
  }
  
  /**
   * @return
   */
  public String getMemberLevel() {
    return memberLevel;
  }
  
  /**
   * @return
   */
  public String getDiscountRate() {
    return discountRate;
  }
  
  /**
   * @return
   */
  public Category getLog() {
    return log;
  }
  
  /**
   * @return
   */
  public String getMemberLevelName() {
    return memberLevelName;
  }
  
  /**
   * @param string
   */
  public void setMemberLevel(String string) {
    if (SIUtil.isNull(string)) string = "";
    this.memberLevel = SIUtil.changeTo(string.trim(), this.encode);
  }
  
  /**
   * @param string
   */
  public void setDiscountRate(String string) {
    if (SIUtil.isNull(string)) string = "";
    this.discountRate = SIUtil.changeTo(string.trim(), this.encode);
  }
  
  /**
   * @param category
   */
  public void setLog(Category category) {
    log = category;
  }
  
  /**
   * @param string
   */
  public void setMemberLevelName(String string) {
    if (SIUtil.isNull(string)) string = "";
    this.memberLevelName = SIUtil.changeTo(string.trim(), this.encode);
  }
  
  /**
   * @return
   */
  public String getMemberLevelCode() {
    return memberLevelCode;
  }
  
  /**
   * @param string
   */
  public void setMemberLevelCode(String string) {
    if (SIUtil.isNull(string)) string = "";
    this.memberLevelCode = SIUtil.changeTo(string.trim(), this.encode);
  }
}