/**
 * @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 javax.servlet.http.*;
import java.util.*;
import java.sql.*;

import org.apache.log4j.Category;
import jp.co.sint.database.*;
import jp.co.sint.basic.*;
import jp.co.sint.config.*;
import jp.co.sint.tools.*;

public class UICustPointListCond extends SIBasic {
  //ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  private String custCode = "";
  private String memberLevelCodeUI = "";
  private String custName = "";
  private String custPronName = "";
  private String tel = "";
  private String[] custCodeTxt = null;
  private String[] addPointTxt = null;
  private String[] totalOfEffective =null;
  private String orderCodeTxt = null;
  private String addReasonTxt = null;
  private String commentTxt = null;
  //SQL検索のばあいに、条件文
  private String conditionSQL=" AND a.delflg < " + SIConfig.SIDEL_FLG_DELETE + " "; // 7.3.0 ST2027 修正  
  private int intDelFlg = Integer.parseInt(SIConfig.SIDEL_FLG_NORMAL);  //削除フラグ 初期値は0:  7.3.0 ST2027 追加
  private boolean searchFlg = false;
  
  public UICustPointListCond() {
      this.searchFlg = false;
  }
  
  /**
    * init
    * 入力したデータを基づいて、このbeansを設定します。
    * @param request
    * @param lUrlParam
    * @return void
    * @throws なし
    */
  //7.1.1 ST0236 修正 ここから
  public void init(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    super.init(lRequest,lUrlParam);
    this.setCustCode((String)lUrlParam.getParam("custCode_S"));
    this.setMemberLevelCodeUI((String)lUrlParam.getParam("memberLevelCodeUI_S"));
    this.setCustName((String)lUrlParam.getParam("custName_S"));
    this.setCustPronName((String)lUrlParam.getParam("custPronName_S"));
    this.setDelFlg(Integer.parseInt((String)lUrlParam.getParam("intDelFlg")));  //7.3.0 ST2027 追加
    this.setTel((String)lUrlParam.getParam("tel"));
    this.searchFlg = true;
  }
  //7.1.1 ST0236 修正 ここまで
  
  /**
    * init
    * 入力したデータを基づいて、このbeansを設定します。
    * @param request データベースへのコネンクション
    * @return void
    * @throws なし
    */
  public void initModify(HttpServletRequest lRequest) {
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    this.setCustCodeTxt(lRequest.getParameterValues("custCodeTxt"));
    this.setAddPointTxt(lRequest.getParameterValues("addPointTxt"));
    this.setTotalOfEffective(lRequest.getParameterValues("totalOfEffective"));
    this.setOrderCode((String)lRequest.getParameter("ordercode"));
    log.debug("ORDERCODE1="+this.getOrderCode()+" ORDERCODE2="+(String)lRequest.getParameter("ordercode"));
    this.setAddReason((String)lRequest.getParameter("reason"));
    log.debug("REASON1="+this.getAddReason()+" REASON2="+(String)lRequest.getParameter("reason"));
    this.setComment((String)lRequest.getParameter("comment"));
    log.debug("COMMENT1="+this.getComment()+" COMMENT2="+(String)lRequest.getParameter("comment"));
  }
  
  /**
     * validate
     * 入力チェックを行います。
     * @param request データベースへのコネンクション
     * @return true:不正なデータがない false:不正なデータがある
     * @throws なし
     */
  public void validate(HttpServletRequest lRequest, Connection lConnection) {
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    SITableConditionManager lConditionMan=new SITableConditionManager();
    SICustomErrors errors = new SICustomErrors();
    
    //顧客コード
    if(SIUtil.isNotNull(this.getCustCode())
     &&SICheckValid.checkValid(errors, "顧客コード", this.getCustCode(), SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)
     &&SICheckValid.checkValid(errors, "顧客コード", this.getCustCode(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 16)){
        lConditionMan.add(new SITableCondition("", "a.CustCode", this.custCode, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    //会員種別
    if(SIUtil.isNotNull(this.getMemberLevelCodeUI())){
        lConditionMan.add(new SITableCondition("", "a.memberlevelcode", this.memberLevelCodeUI, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    //顧客名
    if(SIUtil.isNotNull(this.getCustName())
     && SICheckValid.checkValid(errors, "顧客名", this.getCustName(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 40)){//7.2.0 ST0670 修正
        lConditionMan.add(new SITableCondition("", "a.custName", this.custName, SIConfig.SICONDITION_TYPE_LIKE, SIConfig.SICONDITION_TYPE_AND));
    }
    
    //顧客名カナ
    if(SIUtil.isNotNull(this.getCustPronName())
    && SICheckValid.checkValid(errors, "顧客名カナ", this.getCustPronName(), SICheckDataConf.SICHECK_DATA_ZENKAKU_KANA_TYPE)
    && SICheckValid.checkValid(errors, "顧客名カナ", this.getCustPronName(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 50)){
        lConditionMan.add(new SITableCondition("", "a.custPronName", this.custPronName, SIConfig.SICONDITION_TYPE_LIKE, SIConfig.SICONDITION_TYPE_AND));
    }
    //7.3.0 ST2027 追加 ここから
    // 削除フラグ
    if ( this.intDelFlg == Integer.parseInt(SIConfig.SIDEL_FLG_NORMAL) ) {  // 通常 ( DELETE 未満 )
      lConditionMan.add( new SITableCondition("", "a.delflg", new Integer(SIConfig.SIDEL_FLG_DELETE), SIConfig.SICONDITION_TYPE_LESS, SIConfig.SICONDITION_TYPE_AND) );
    } else if ( this.intDelFlg == Integer.parseInt(SIConfig.SIDEL_FLG_DELETE) ){ // 削除済み ( = 削除 )
      lConditionMan.add( new SITableCondition("", "a.delflg ", new Integer(SIConfig.SIDEL_FLG_DELETE), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND) );
    }
    //7.3.0 ST2027 追加 ここまで
    
    //電話番号
    if(SIUtil.isNotNull(this.getTel())
     &&SICheckValid.checkValid(errors, "電話番号", this.getCustCode(), SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)
     &&SICheckValid.checkValid(errors, "電話番号", this.getCustCode(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 16)){
        lConditionMan.add(new SITableCondition("", "a.storetel", this.tel, SIConfig.SICONDITION_TYPE_LIKE, SIConfig.SICONDITION_TYPE_AND));
    }
    if (!errors.isEmpty()){
        lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
        lConditionMan.add(new SITableCondition(" AND 1=2 "));
    }
    //条件文の設定
    this.setConditionSQL(lConditionMan.getCondtionSQL());
  }
  
  /**
   * validate
   * 入力チェックを行います。
   * @param request データベースへのコネンクション
   * @return true:不正なデータがない false:不正なデータがある
   * @throws なし
   */
  public boolean validateModify(HttpServletRequest lRequest,UICustPointListCond listCond,Connection lConnection) {
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    SICustomErrors errors = new SICustomErrors();
    StringBuffer lSqlBuf = new StringBuffer();
    
    lSqlBuf.append("SELECT ordercode FROM ordertbl ");
    lSqlBuf.append("WHERE enabledflg='1' ");
    lSqlBuf.append("AND ordercode=").append(this.getOrderCode());
  
    if("5".equals(this.getAddReason())){
       SICheckValid.checkValid(errors, "付与理由", this.getComment(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
    }else if ("3".equals(this.getAddReason())){
       SICheckValid.checkValid(errors, "返品手数料", this.getComment(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
       SICheckValid.checkValid(errors, "受注番号", this.getOrderCode(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
       SICheckValid.checkValid(errors, "受注番号", this.getOrderCode(), SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
    }else if ("1".equals(this.getAddReason()) || "2".equals(this.getAddReason()) ){
       SICheckValid.checkValid(errors, "受注番号", this.getOrderCode(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
       SICheckValid.checkValid(errors, "受注番号", this.getOrderCode(), SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
    }
    
    if(!"4".equals(this.getAddReason()) && SIUtil.isNotNull(this.getOrderCode())){
      try{ 
        if(!SIDBUtil.hasData(lConnection,lSqlBuf.toString())){
          errors.addError(new SICustomError("database.query.notexist","受注番号"));
         }
      }catch(Exception e){
        e.printStackTrace();
      }
    }
    boolean positivecheck = true;
    boolean negativecheck = true;
    boolean bitcheck = true;
    boolean digitcheck = true;
    if(this.getCustCodeTxt()!=null && this.getCustCodeTxt().length>0){
      for(int i=0;i<this.getAddPointTxt().length;i++){
        if(SIUtil.isNull(getAddPointTxt()[i])||!SICheckUtil.isDigitNegative(getAddPointTxt()[i])){
          digitcheck = false;
        }else if (!"0".equals(getAddPointTxt()[i])&&SICheckUtil.isDigit(getAddPointTxt()[i])){
          negativecheck = false;
          if (Integer.parseInt(getAddPointTxt()[i])>99999) bitcheck = false;
        }else if (!"0".equals(getAddPointTxt()[i])){
          positivecheck = false;
          if (Integer.parseInt(getAddPointTxt()[i])<-99999) bitcheck = false;
        }
      }
    }else{
      errors.addError(new SICustomError("input.data.check.listData"));
    }
    if(!digitcheck){
      errors.addError(new SICustomError("input.data.digit","追加ポイント"));//7.1.1 ST0162 修正
    }
    if(!bitcheck){
      errors.addError(new SICustomError("input.data.len.within.bit","追加ポイント","5"));
    }
    
    if("1".equals(this.getAddReason())){
      if(!positivecheck) errors.addError(new SICustomError("input.data.forbidden","返金入力にマイナスの値"));
    }else if("2".equals(this.getAddReason())){
      if(!positivecheck) errors.addError(new SICustomError("input.data.forbidden","返金入力にマイナスの値"));
    }else if("3".equals(this.getAddReason())){
      if(!positivecheck) errors.addError(new SICustomError("input.data.forbidden","返金入力にマイナスの値"));
    }else if("4".equals(this.getAddReason())){
      if (!negativecheck) errors.addError(new SICustomError("input.data.forbidden","付与済ポイントの訂正にプラスの値"));
    }else if("5".equals(this.getAddReason())){
    }
    
    for(int i=0;i<listCond.getCustCodeTxt().length;i++){
      if(SIUtil.isNotNull(listCond.getAddPointTxt()[i]) && !listCond.getAddPointTxt()[i].equals("0")&&SICheckUtil.isDigitNegative(listCond.getAddPointTxt()[i])){
        //有効ポイントがマイナスになってしまう場合はエラーを返す
        if(Long.parseLong(SIUtil.add(listCond.getAddPointTxt()[i],listCond.getTotalOfEffective()[i]))<0){
          errors.addError(new SICustomError("input.data.point.check",listCond.getCustCodeTxt()[i]));
        }
      }
    }
    
    if (!errors.isEmpty()) lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
    
    return errors.isEmpty();
  }
  
  /**
   * getCollection
   *
   * @param Connection
   * @return Collection
   * @throws SIDBAccessException
   */
  public Collection getCollection(Connection lConnection,SILogin lLogin) throws SIDBAccessException {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SICustPoint currCustomer = new SICustPoint();
    StringBuffer lSqlBuf = new StringBuffer();
    Collection lResultColl = new ArrayList();
    StringBuffer lCountBuf = new StringBuffer();
    
    if(!searchFlg) return lResultColl;
    
    //出力項目
    lSqlBuf.append("SELECT a.CustCode,a.custName,a.custPronName,a.email,a.storetel,");
    lSqlBuf.append("b.totalOfInvalid,b.totalOfTemporary,b.totalOfEffective ");
    lSqlBuf.append("FROM CustTbl a ");
    lSqlBuf.append("LEFT OUTER JOIN ");
    lSqlBuf.append("(SELECT custcode, ");
    lSqlBuf.append("SUM(CASE WHEN enableflg=0 THEN pointofissue ELSE 0 END) AS totalOfInvalid, ");
    lSqlBuf.append("SUM(CASE WHEN enableflg=2 THEN pointofissue ELSE 0 END) AS totalOfTemporary, ");
    lSqlBuf.append("SUM(CASE WHEN enableflg=1 THEN pointofissue ELSE 0 END) AS totalOfEffective ");
    lSqlBuf.append("FROM pointmanmtbl ");
    lSqlBuf.append("GROUP BY custcode) b ");
    lSqlBuf.append("ON a.custcode=b.custcode  ");
    lSqlBuf.append(" WHERE 1=1 ");
    
    lCountBuf.append("SELECT count(a.CustCode) ");
    lCountBuf.append("FROM CustTbl a ");
    lCountBuf.append(" WHERE 1=1 ");
    
    //結合の条件
    lSqlBuf.append(this.getCondtionSQL());
    lCountBuf.append(this.getCondtionSQL());
    
    log.debug("conditionSQL=" + conditionSQL);
    
    //出力順
    lSqlBuf.append(" ORDER BY a.custcode DESC ");
    
    //実行
    try {
      String rowCnt = SIDBUtil.getFirstData(lConnection, lCountBuf.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:lCmdtySqlBuf="+lSqlBuf.toString());
      lStatement=lConnection.createStatement();
      lSqlBuf.append(" LIMIT ").append(lPageSize);
      if(lFromInx>0) lSqlBuf.append(" OFFSET ").append(lFromInx);
      lResultSet=lStatement.executeQuery(lSqlBuf.toString());
      
      for (int jj=0;jj<lFromInx;jj++) lResultColl.add(null);
      
      int lIndex=0;
      
      //レコードのセットの作成
      while (lResultSet.next()&&lIndex<lPageSize){
        currCustomer = new SICustPoint();
        currCustomer.setEncode(SIConfig.SIENCODE_NONE);
        currCustomer.setCustCode(lResultSet.getString("CustCode"));
        currCustomer.setCustName(lResultSet.getString("custName"));
        currCustomer.setCustPronName(lResultSet.getString("custPronName"));
        currCustomer.setEmail(lResultSet.getString("email"));
        currCustomer.setTotalOfInvalid(lResultSet.getString("totalOfInvalid"));
        currCustomer.setTotalOfTemporary(lResultSet.getString("totalOfTemporary"));
        currCustomer.setTotalOfEffective(lResultSet.getString("totalOfEffective"));
        currCustomer.setTel(lResultSet.getString("storetel"));
        lResultColl.add(currCustomer);
        lIndex++;
      }
      for (int jj=lFromInx+lPageSize;jj<lRecordCount;jj++)lResultColl.add(null);
      
    } catch (Exception ex) {
      log.debug("exception sql = " + lSqlBuf.toString());
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lResultSet,lStatement);
    }
    return lResultColl;
  }
  
  /**
   * getCustPoint
   *
   * @param Connection
   * @return Collection
   * @throws SIDBAccessException
   */
  public String getCustPoint(Connection dbConnection,SILogin lLogin, String custCode) throws SIDBAccessException {
    Statement statement = null;
    ResultSet resultSet = null;
    StringBuffer lSqlBuf = new StringBuffer();
    String custPoint = new String("0");
    
    //出力項目
    lSqlBuf.append("SELECT "); 
    lSqlBuf.append("SUM(CASE WHEN enableflg='1' THEN pointofissue ELSE 0 END) AS totalOfEffective ");
    lSqlBuf.append("FROM pointmanmtbl ");
    lSqlBuf.append("GROUP BY custcode ");
    lSqlBuf.append("HAVING custcode=").append(SIDBUtil.SQL2Str(custCode));
    log.debug("conditionSQL=" + conditionSQL);
    //実行
    try {
      statement = dbConnection.createStatement();
      resultSet = statement.executeQuery(lSqlBuf.toString());
      
      //商品レコードのセットの作成
      while (resultSet.next()) {
        custPoint = resultSet.getString("totalOfEffective");
      }
    } catch (Exception ex) {
      log.debug("exception sql = " + lSqlBuf.toString());
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return custPoint;
  }
  
  //getter of 顧客コード
  public String getCustCode() {
    return custCode;
  }
  
  //getter of 顧客名
  public String getCustName() {
    return custName;
  }
  
  //getter of 顧客名カナ
  public String getCustPronName() {
    return custPronName;
  }
  
  //getter of 会員種別
  public String getMemberLevelCodeUI() {
    return memberLevelCodeUI;
  }
  
  //getter of 顧客コード
  public String[] getCustCodeTxt() {
    return custCodeTxt;
  }
  
  //getter of 顧客コード
  public String[] getAddPointTxt() {
    return addPointTxt;
  }
  
  //getter of 条件文
  public String getCondtionSQL(){
    return this.conditionSQL;
  }
  
  //getter of 有効ポイント
  public String[] getTotalOfEffective() {
    return totalOfEffective;
  }
  
  //getter of 受注番号
  public String getOrderCode() {
    return orderCodeTxt;
  }

  //getter of 付与理由
  public String getAddReason() {
    return addReasonTxt;
  }

  //getter of 付与理由（別の理由）
  public String getComment() {
    return commentTxt;
  }
  
  //setter of 顧客コード
  public void setCustCode(String string) {
    if (string == null) string = "";
    custCode = SIUtil.changeTo(string.trim(), this.encode);
  }
  
  //setter of 顧客名
  public void setCustName(String string) {
    if (string == null) string = "";
    custName = SIUtil.changeTo(string.trim(), this.encode);
  }
  
  //setter of 顧客名カナ
  public void setCustPronName(String string) {
    if (string == null) string = "";
    custPronName = SIUtil.changeTo(string.trim(), this.encode);
  }
  
  //setter of 会員種別
  public void setMemberLevelCodeUI(String string) {
    if (string == null) string = "";
    memberLevelCodeUI = SIUtil.changeTo(string.trim(), this.encode);
  }
  
  //setter of 顧客コード
  public void setCustCodeTxt(String[] string) {
      this.custCodeTxt=SIUtil.changeTo(string,this.encode);
  }
  
  //setter of 追加ポイント数
  public void setAddPointTxt(String[] string) {
    this.addPointTxt=SIUtil.changeTo(string,this.encode);
  }
  
  //setter of 条件文
  public void setConditionSQL(String string){
    if (string == null) string = "";
    conditionSQL = string;
  }
  
  //setter of 有効ポイント数
  public void setTotalOfEffective(String[] strings) {
    totalOfEffective = SIUtil.changeTo(strings,this.encode);;
  }
  
  //setter of 受注番号
  public void setOrderCode(String string) {
    if (SIUtil.isNull(string)) string="";
    orderCodeTxt = SIUtil.changeTo(string,this.encode);;
  }

  //setter of 付与理由
  public void setAddReason(String string) {
    if (SIUtil.isNull(string)) string="";
    addReasonTxt = SIUtil.changeTo(string,this.encode);;
  }

  //setter of 付与理由(別の付与理由)
  public void setComment(String string) {
    if (SIUtil.isNull(string)) string="";
    commentTxt = SIUtil.changeTo(string,this.encode);;
  }

  //7.3.0 ST2027 追加 ここから
  //削除フラグ Setter
  public void setDelFlg(int intDelFlg) {
    this.intDelFlg = intDelFlg;
  }
  
  //削除フラグ Getter
  public int getDelFlg() {
    return this.intDelFlg;
  }
  
  //削除フラグ String型でのGetter
  public String getDelFlgConvStr() {
    return new Integer(this.intDelFlg).toString();
  }
  //7.3.0 ST2027 追加 ここまで
  
  /*
   * getter of tel.
   */
  public String getTel() {
    return tel;
  }
  
  /*
   * setter of tel
   */
  public void setTel(String tel) {
    this.tel = tel;
  }
}