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

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

import javax.servlet.http.HttpServletRequest;

import jp.co.sint.basic.SIBasic;
import jp.co.sint.basic.SIPointIndividual;
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.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.SIUtil;
import jp.co.sint.tools.SIURLParameter;

import org.apache.log4j.Category;

public class UICmdtyPointListCond extends SIBasic {
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  // 商品名
  private String cmdtyNameTxt = "";
  
  // 在庫コード
  private String individualCodeTxt = "";
  
  // ボーナス適用区分
  private String bonusPointType = "";
  
  // 個別ポイント適用時期（過去）
  private String pointApplyPast = "";
  
  // 個別ポイント適用時期（未来）
  private String pointApplyFuture = "";
  
  // 現行ポイントレート
  private String pointRateFrom ="";
  
  // 現行ポイントレート
  private String pointRateTo ="";
  
  // ブランドコード
  private String makerCodeTxt = "";
  
  // カテゴリコード
  private String ctgryCodeTxt = "";
  
  // SQL検索時の条件文
  private String conditionSQL = "";
  
  private boolean searchFlg = true;
  
  /**
   * UICmdtyPointListCond コンストラクタ
   * 
   * @param なし
   * @return なし
   * @throws なし
   */
  public UICmdtyPointListCond() {
    this.bonusPointType = "0";
    
    // 条件文の設定
    SITableConditionManager lConditionMan = new SITableConditionManager();
    lConditionMan.add(new SITableCondition("in1", "rate2", "", SIConfig.SICONDITION_TYPE_IS_NOT_NULL, SIConfig.SICONDITION_TYPE_AND));
    
    this.conditionSQL = lConditionMan.getCondtionSQL();
  }
  
  /**
   * UICmdtyPointListCond コンストラクタ
   * 
   * @param lRequest リクエスト
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  public UICmdtyPointListCond(HttpServletRequest lRequest, SIURLParameter lUrlParam) {
    this.init(lRequest, lUrlParam);
  }
  
  //getter of cmdtyNameTxt
  public String getCmdtyNameTxt() {
    return this.cmdtyNameTxt;
  }
  
  //getter of individualCodeTxt
  public String getIndividualCodeTxt() {
    return this.individualCodeTxt;
  }
  
  // getter of conditionSQL
  public String getConditionSQL() {
    return this.conditionSQL;
  }
  
  //getter of bonusPointType
  public String getBonusPointType() {
    return this.bonusPointType;
  }
  
  //getter of pointApplyPast
  public String getPointApplyPast() {
    return this.pointApplyPast;
  }
  
  //getter of pointApplyFuture
  public String getPointApplyFuture() {
    return this.pointApplyFuture;
  }
  
  //getter of pointRateFrom
  public String getPointRateFrom() {
    return this.pointRateFrom;
  }
  
  //getter of pointRateTo
  public String getPointRateTo() {
    return this.pointRateTo;
  }
  
  //getter of ctgryCodeTxt
  public String getCtgryCodeTxt() {
    return this.ctgryCodeTxt;
  }
  
  //getter of makerCodeTxt
  public String getMakerCodeTxt() {
    return this.makerCodeTxt;
  }
  
  //setter of cmdtynameTxt
  public void setCmdtyNameTxt(String cmdtyNameTxt) {
    if (SIUtil.isNull(cmdtyNameTxt)) cmdtyNameTxt = "";
    this.cmdtyNameTxt = SIUtil.changeTo(cmdtyNameTxt.trim(), this.encode);
  }
  
  //setter of individualCodeTxt
  public void setIndividualCodeTxt(String individualCodeTxt) {
    if (SIUtil.isNull(individualCodeTxt)) individualCodeTxt = "";
    this.individualCodeTxt = SIUtil.changeTo(individualCodeTxt.trim(), this.encode);
  }
  
  // setter of conditionSQL
  public void setConditionSQL(String lConditionSQL) {
    if (lConditionSQL == null) lConditionSQL = "";
    this.conditionSQL = lConditionSQL;
  }
  
  //setter of bonusPointType
  public void setBonusPointType(String bonusPointType) {
    if (SIUtil.isNull(bonusPointType)) bonusPointType = "";
    this.bonusPointType = SIUtil.changeTo(bonusPointType.trim(), this.encode);
  }
  
  //setter of pointApplyPast
  public void setPointApplyPast(String pointApplyPast) {
    if (SIUtil.isNull(pointApplyPast)) pointApplyPast = "";
    this.pointApplyPast = SIUtil.changeTo(pointApplyPast.trim(), this.encode);
  }
  
  //setter of pointApplyFuture
  public void setPointApplyFuture(String pointApplyFuture) {
    if (SIUtil.isNull(pointApplyFuture)) pointApplyFuture = "";
    this.pointApplyFuture = SIUtil.changeTo(pointApplyFuture.trim(), this.encode);
  }
  
  //setter of pointRateFrom
  public void setPointRateFrom(String pointRateFrom) {
    if (SIUtil.isNull(pointRateFrom)) pointRateFrom = "";
    this.pointRateFrom = SIUtil.changeTo(pointRateFrom.trim(), this.encode);
  }
  
  //setter of pointRateTo
  public void setPointRateTo(String pointRateTo) {
    if (SIUtil.isNull(pointRateTo)) pointRateTo = "";
    this.pointRateTo = SIUtil.changeTo(pointRateTo.trim(), this.encode);
  }
  
  //setter of ctgryCodeTxt
  public void setCtgryCodeTxt(String ctgryCodeTxt) {
    if (SIUtil.isNull(ctgryCodeTxt)) ctgryCodeTxt = "";
    this.ctgryCodeTxt = SIUtil.changeTo(ctgryCodeTxt.trim(), this.encode);
  }
  
  //setter of makerCodeTxt
  public void setMakerCodeTxt(String makerCodeTxt) {
    if (SIUtil.isNull(makerCodeTxt)) makerCodeTxt = "";
    this.makerCodeTxt = SIUtil.changeTo(makerCodeTxt.trim(), this.encode);
  }
  
  /**
   * <b>init</b> 入力したデータを基づいて、このbeansを設定します。
   * 
   * @param request クライアントからリクエスト
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  public void init(HttpServletRequest lRequest, SIURLParameter lUrlParam) {
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    super.init(lRequest, lUrlParam);
    this.setCmdtyNameTxt((String) lUrlParam.getParam("cmdtyNameTxt"));
    this.setIndividualCodeTxt((String) lUrlParam.getParam("individualCodeTxt"));
    this.setBonusPointType((String) lUrlParam.getParam("bonusPointType"));
    this.setPointApplyPast((String) lUrlParam.getParam("pointApplyPast"));
    this.setPointApplyFuture((String) lUrlParam.getParam("pointApplyFuture"));
    this.setPointRateFrom((String) lUrlParam.getParam("pointRateFrom"));
    this.setPointRateTo((String) lUrlParam.getParam("pointRateTo"));
    this.setMakerCodeTxt((String) lUrlParam.getParam("makerCodeTxt"));
    this.setCtgryCodeTxt((String) lUrlParam.getParam("ctgryCodeTxt"));
  }
  
  /**
   * <b>getCollection</b> 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * 
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws SIDBAccessException
   */
  public Collection getCollection(Connection lConnection) throws SIDBAccessException {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIPointIndividual lRule = new SIPointIndividual();
    Collection lPointColl = new ArrayList();
    
    StringBuffer lPointBuf = new StringBuffer();
    StringBuffer lCountBuf = new StringBuffer();
    StringBuffer lCommonBuf = new StringBuffer();
    
    if (!this.searchFlg) { return lPointColl; }
    
    // 基本のSQL
    lPointBuf.append("SELECT cm.cmdtycode,cm.individualcode,cm.cmdtyname,ct.rate1,ct.from1,ct.to1,in1.rate2,in1.from2,in1.to2,in2.pastflg,in2.futureflg,pm.rate3,in1.username,mk.makername ");
    lCountBuf.append("SELECT count(cm.individualcode) ");
    
    lCommonBuf.append("FROM cmdtyunittbl cm ");
    lCommonBuf.append("LEFT OUTER JOIN (SELECT ctgrycode,bonuspointrate AS rate1,bonusfromdate AS from1,bonustodate AS to1 ");
    lCommonBuf.append("FROM bonuspointtbl WHERE bonusfromdate::date <= current_date AND bonustodate::date >= current_date) ct ");
    lCommonBuf.append("ON substring(cm.individualcode,0,3) = ct.ctgrycode ");
    lCommonBuf.append("LEFT OUTER JOIN (SELECT a.individualcode,a.bonuspointrate AS rate2,a.bonusfromdate AS from2,a.bonustodate AS to2,b.username ");
    lCommonBuf.append("FROM bonuspointindividualtbl a,mngusermtbl b WHERE a.insertuser=b.usercode AND bonusfromdate::date <= current_date AND bonustodate::date >= current_date) in1 ");
    lCommonBuf.append("ON cm.individualcode = in1.individualcode ");
    lCommonBuf.append("LEFT OUTER JOIN (SELECT individualcode ");
    lCommonBuf.append(",MAX(CASE WHEN bonustodate::date < current_date THEN 1 ELSE NULL END) AS pastFlg");
    lCommonBuf.append(",MAX(CASE WHEN bonusfromdate::date > current_date THEN 1 ELSE NULL END) AS futureFlg ");
    lCommonBuf.append("FROM bonuspointindividualtbl GROUP BY individualcode) in2 ");
    lCommonBuf.append("ON cm.individualcode = in2.individualcode ");
    lCommonBuf.append(",(SELECT CASE WHEN bonusfromdate::date <= current_date AND bonustodate::date >= current_date THEN bonuspointrate ELSE pointrate END AS rate3 ");
    lCommonBuf.append("FROM pointshopmtbl WHERE mallshopcode='0') pm ,makertbl mk WHERE cm.makercode=mk.makercode ");
    
    // 検索の条件
    lCommonBuf.append(this.conditionSQL);
    
    lPointBuf.append(lCommonBuf);
    lCountBuf.append(lCommonBuf);
    
    // 昇順に並べ替え
    lPointBuf.append(" ORDER BY cm.individualcode");
    
    log.debug("getCollection:lSqlBuf=" + lPointBuf.toString());
    // 実行
    try {
      int lRecordCount = Integer.parseInt(SIDBUtil.getFirstData(lConnection, lCountBuf.toString()));// レコード数の取得
      
      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:lCmdtySqlBuf=" + lPointBuf.toString());
      lStatement = lConnection.createStatement();
      lPointBuf.append(" LIMIT ").append(lPageSize);
      if(lFromInx>0) lPointBuf.append(" OFFSET ").append(lFromInx);
      lResultSet = lStatement.executeQuery(lPointBuf.toString());
      
      for (int jj = 0; jj < lFromInx; jj++)
        lPointColl.add(null);
      int lIndex = 0;
      
      // ポイントレコードのセットの作成
      while (lResultSet.next() && lIndex < lPageSize) {
        lRule = new SIPointIndividual();
        lRule.setEncode(SIConfig.SIENCODE_NONE);
        lRule.setCmdtyName(lResultSet.getString("cmdtyName"));
        lRule.setCmdtyCode(lResultSet.getString("cmdtyCode"));
        lRule.setIndividualCode(lResultSet.getString("individualCode"));
        lRule.setCtgryBonusPointRate(lResultSet.getString("rate1"));
        lRule.setCtgryBonusFromDate(lResultSet.getString("from1"));
        lRule.setCtgryBonusToDate(lResultSet.getString("to1"));
        lRule.setIndividualBonusPointRate(lResultSet.getString("rate2"));
        lRule.setIndividualBonusFromDate(lResultSet.getString("from2"));
        lRule.setIndividualBonusToDate(lResultSet.getString("to2"));
        lRule.setPastFlg(lResultSet.getString("pastflg"));
        lRule.setFutureFlg(lResultSet.getString("futureflg"));
        lRule.setBasicPointRate(lResultSet.getString("rate3"));
        lRule.setMakerName(lResultSet.getString("makerName"));
        lRule.setChargeName(lResultSet.getString("userName"));
        lPointColl.add(lRule);
        lIndex++;
      }
      for (int jj = lFromInx + lPageSize; jj < lRecordCount; jj++)
        lPointColl.add(null);
      
    } catch (Exception ex) {
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lPointColl;
  }
  
  /**
   * <b>validate</b> 入力したデータをチェックして、同時にSQLの条件文を作成します。
   * 
   * @param lRequest クライアントからのリクエスト
   * @return なし
   * @throws なし
   */
  public void validate(HttpServletRequest lRequest) {
    SICustomErrors errors = new SICustomErrors();
    SITableConditionManager lConditionMan = new SITableConditionManager();
    
    // 商品名（部分一致）
    if (SIUtil.isNotNull(getCmdtyNameTxt())) {
      lConditionMan.add(new SITableCondition("cm", "CmdtyName", this.getCmdtyNameTxt(), SIConfig.SICONDITION_TYPE_LIKE, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // 在庫コード（前方一致）
    if (SIUtil.isNotNull(getIndividualCodeTxt()) && SICheckValid.checkValid(errors, "在庫コード", getIndividualCodeTxt(), SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE)) {
      lConditionMan.add(new SITableCondition("cm", "IndividualCode", this.getIndividualCodeTxt(), SIConfig.SICONDITION_TYPE_FRONTLIKE, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // ボーナスポイント区分
    if (SIUtil.isNotNull(getBonusPointType()) && "0".equals(getBonusPointType())) {// 個別
      lConditionMan.add(new SITableCondition("in1", "rate2", "", SIConfig.SICONDITION_TYPE_IS_NOT_NULL, SIConfig.SICONDITION_TYPE_AND));
    } else if (SIUtil.isNotNull(getBonusPointType()) && "1".equals(getBonusPointType())) {// カテゴリ
      lConditionMan.add(new SITableCondition("in1", "rate2", "", SIConfig.SICONDITION_TYPE_IS_NULL, SIConfig.SICONDITION_TYPE_AND));
      lConditionMan.add(new SITableCondition("ct", "rate1", "", SIConfig.SICONDITION_TYPE_IS_NOT_NULL, SIConfig.SICONDITION_TYPE_AND));
    } else if (SIUtil.isNotNull(getBonusPointType()) && "2".equals(getBonusPointType())) {// 適用中
      lConditionMan.add(new SITableCondition(" AND CASE WHEN rate2 IS NOT NULL THEN true WHEN rate1 IS NOT NULL THEN true ELSE false END "));
    } else if (SIUtil.isNotNull(getBonusPointType()) && "3".equals(getBonusPointType())) {// なし
      lConditionMan.add(new SITableCondition("in1", "rate2", "", SIConfig.SICONDITION_TYPE_IS_NULL, SIConfig.SICONDITION_TYPE_AND));
      lConditionMan.add(new SITableCondition("ct", "rate1", "", SIConfig.SICONDITION_TYPE_IS_NULL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // 商品個別ボーナス終了分あり
    if (SIUtil.isNotNull(getPointApplyPast())) {
      lConditionMan.add(new SITableCondition("in2", "pastFlg", "", SIConfig.SICONDITION_TYPE_IS_NOT_NULL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // 商品個別ボーナス予約分あり
    if (SIUtil.isNotNull(getPointApplyFuture())) {
      lConditionMan.add(new SITableCondition("in2", "futureFlg", "", SIConfig.SICONDITION_TYPE_IS_NOT_NULL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    if (SIUtil.isNotNull(getPointRateFrom())){// 現行ポイントレートFrom
      lConditionMan.add(new SITableCondition(" AND CASE WHEN rate2 IS NOT NULL THEN rate2 WHEN rate1 IS NOT NULL THEN rate1 ELSE rate3 END >= " + getPointRateFrom()));
    }
    if (SIUtil.isNotNull(getPointRateTo())) {// 現行ポイントレートTo
      lConditionMan.add(new SITableCondition(" AND CASE WHEN rate2 IS NOT NULL THEN rate2 WHEN rate1 IS NOT NULL THEN rate1 ELSE rate3 END <= " + getPointRateTo()));
    }
    
    if (SIUtil.isNotNull(getMakerCodeTxt())){// ブランド
      lConditionMan.add(new SITableCondition("cm", "makerCode", getMakerCodeTxt(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    if(SIUtil.isNotNull(getCtgryCodeTxt())){// カテゴリ
      lConditionMan.add(new SITableCondition(" AND cm.cmdtycode IN (SELECT cmdtycode FROM cmdtyctgrymtbl WHERE ctgrycode ="+SIDBUtil.SQL2Str(this.ctgryCodeTxt)+") "));
    }
    
    // 現行レート
    SICheckValid.checkValid(errors, "現行ポイントレートFrom", getPointRateFrom(), SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
    SICheckValid.checkValid(errors, "現行ポイントレートTo", getPointRateTo(), SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
    
    // 現行レート大小
    try {
      if (errors.isEmpty() && !SICheckUtil.isEqual(getPointRateFrom(), getPointRateTo())) {
        SICheckValid.checkValid(errors, "現行ポイントレートFrom", "現行ポイントレートTo", getPointRateFrom(), getPointRateTo(), SICheckDataConf.SICHECK_DATA_VALUE_LESS_TYPE);
      }
    } catch (Exception e) {}
    
    // エラーをセッションに設定
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    if (!errors.isEmpty()) {
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
      this.searchFlg = false;
    } else {
      lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    }
    
    // 条件文の設定
    this.setConditionSQL(lConditionMan.getCondtionSQL());
  }
}