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

import javax.servlet.http.*;

import java.sql.Connection;
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.*;
import jp.co.sint.tools.SIURLParameter;

/**
 * @version $Id : UIMngAccesslogListCond.java,v 1.0 Exp $
 * @author      : Abukawa
 * <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>
 * Abukawa   2005/04/28  Original
 */

public class UIMngAccesslogListCond extends SIBasic{
  //ログ用のインスタンスの生成
  private static Category log=Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  //ショップコード
  private String shopCodeTxt="";
  
  //管理者ID
  private String userCodeTxt="";
  
  //ユーザー名
  private String userNameTxt="";
  
  //受注日の年(To)
  private String initDateYearToCbo="";
  
  //受注日の月(To)
  private String initDateMonthToCbo="";
  
  //受注日の日(To)
  private String initDateDayToCbo="";
  
  //受注日の年(From)
  private String initDateYearFromCbo="";
  
  //受注日の月(From)
  private String initDateMonthFromCbo="";
  
  //受注日の日(From)
  private String initDateDayFromCbo="";
  
  //オペレーション
  private String userOperation="";
  
  //SQL検索のばあいに、条件文
  private String conditionSQL="";
  
  //並び順の文
  private String orderBySQL=" ORDER BY InitDateTime DESC";
  
  //検索するかどうか？
  private boolean searchFlg =true;
  
  /**
   * UICmdtyListCond
   * コンストラクタ
   * @param なし
   * @return なし
   * @throws なし
   */
  public UIMngAccesslogListCond(){}
  
  /**
   * UICmdtyListCond
   * コンストラクタ
   * @param lRequest リクエスト
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  public UIMngAccesslogListCond(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    this.init(lRequest,lUrlParam);
  }
  
  //setter of ショップコード
  public void setShopCodeTxt(String lShopCodeTxt){
    if (SIUtil.isNull(lShopCodeTxt)) lShopCodeTxt="";
    this.shopCodeTxt=SIUtil.changeTo(lShopCodeTxt.trim(),this.encode);
  }
  
  //setter of 管理者ID
  public void setUserCodeTxt(String lUserCodeTxt){
    if (SIUtil.isNull(lUserCodeTxt)) lUserCodeTxt="";
    this.userCodeTxt=SIUtil.changeTo(lUserCodeTxt.trim(),this.encode);
  }
  
  //setter of 名前
  public void setUserNameTxt(String luserNameTxt){
    if (SIUtil.isNull(luserNameTxt)) luserNameTxt="";
    this.userNameTxt=SIUtil.changeTo(luserNameTxt.trim(),this.encode);
  }
  
  //setter of 条件文
  public void setConditionSQL(String lConditionSQL){
    if (lConditionSQL==null)lConditionSQL="";
    this.conditionSQL=lConditionSQL;
  }
  
  //setter of 並び順文
  public void setOrderBySQL(String lOrderBySQL){
    if (lOrderBySQL==null)lOrderBySQL="";
    this.orderBySQL=lOrderBySQL;
  }
  
  //setter of 受注日の年(From)
  public void setInitDateYearFromCbo(String lInitDateYearFromCbo){
    if (SIUtil.isNull(lInitDateYearFromCbo)) lInitDateYearFromCbo="";
    this.initDateYearFromCbo=SIUtil.changeTo(lInitDateYearFromCbo.trim(),this.encode);
  }
  
  //setter of 受注日の月(From)
  public void setInitDateMonthFromCbo(String lInitDateMonthFromCbo){
    if (SIUtil.isNull(lInitDateMonthFromCbo)) lInitDateMonthFromCbo="";
    this.initDateMonthFromCbo=SIUtil.changeTo(lInitDateMonthFromCbo.trim(),this.encode);
  }
  
  //setter of 受注日の日(From)
  public void setInitDateDayFromCbo(String lInitDateDayFromCbo){
    if (SIUtil.isNull(lInitDateDayFromCbo)) lInitDateDayFromCbo="";
    this.initDateDayFromCbo=SIUtil.changeTo(lInitDateDayFromCbo.trim(),this.encode);
  }
  
  //setter of 受注日の年(To)
  public void setInitDateYearToCbo(String lInitDateYearToCbo){
    if (SIUtil.isNull(lInitDateYearToCbo)) lInitDateYearToCbo="";
    this.initDateYearToCbo=SIUtil.changeTo(lInitDateYearToCbo.trim(),this.encode);
  }
  
  //setter of 受注日の月(To)
  public void setInitDateMonthToCbo(String lInitDateMonthToCbo){
    if (SIUtil.isNull(lInitDateMonthToCbo)) lInitDateMonthToCbo="";
    this.initDateMonthToCbo=SIUtil.changeTo(lInitDateMonthToCbo.trim(),this.encode);
  }
  
  //setter of 受注日の日(To)
  public void setInitDateDayToCbo(String lInitDateDayToCbo){
    if (SIUtil.isNull(lInitDateDayToCbo)) lInitDateDayToCbo="";
    this.initDateDayToCbo=SIUtil.changeTo(lInitDateDayToCbo.trim(),this.encode);
  }
  
  //setter of オペレーション
  public void setUserOperation(String luserOperation) {
    this.userOperation = luserOperation;
  }
  
  //検索フラグ
  public void setSearchFlg(boolean newSearchFlg){
    this.searchFlg=newSearchFlg;
  }
  
  //getter of ショップコード
  public String getShopCodeTxt(){
    return this.shopCodeTxt;
  }
  
  //getter of 管理者ID
  public String getUserCodeTxt(){
    return this.userCodeTxt;
  }
  
  //getter of 名前
  public String getUserNameTxt(){
    return this.userNameTxt;
  }
  
  //getter of 条件文
  public String getCondtionSQL(){
    return this.conditionSQL;
  }
  
  //getter of 並び順文
  public String getOrderBySQL(){
    return this.orderBySQL;
  }
  
  //getter of 受注日の年(From)
  public String getInitDateYearFromCbo(){
    return this.initDateYearFromCbo;
  }
  
  //getter of 受注日の月(From)
  public String getInitDateMonthFromCbo(){
    return this.initDateMonthFromCbo;
  }
  
  //getter of 受注日の日(From)
  public String getInitDateDayFromCbo(){
    return this.initDateDayFromCbo;
  }
  
  //getter of 受注日(From)
  public String getInitDateFrom(){
    return SIDateTime.getDate(getInitDateYearFromCbo(),getInitDateMonthFromCbo(),getInitDateDayFromCbo());
  }
  
  //getter of 受注日の年(To)
  public String getInitDateYearToCbo(){
    return this.initDateYearToCbo;
  }
  
  //getter of 受注日の月(To)
  public String getInitDateMonthToCbo(){
    return this.initDateMonthToCbo;
  }
  
  //getter of 受注日の日(To)
  public String getInitDateDayToCbo(){
    return this.initDateDayToCbo;
  }
  
  //getter of 受注日(To)
  public String getInitDateTo(){
    return SIDateTime.getDate(getInitDateYearToCbo(),getInitDateMonthToCbo(),getInitDateDayToCbo());
  }
  
  //getter of オペレーション
  public String getUserOperation() {
    return this.userOperation;
  }
  
  //検索フラグ
  public boolean getSearchFlg(){
    return this.searchFlg;
  }
  
  /**
   * <b>init</b>
   * 入力したデータを基づいて、このbeansを設定します。
   * @param request クライアントからリクエスト
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  public void init(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    SILogin lLogin=SIHTMLUtil.getLogin(lRequest);
    
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    super.init(lRequest,lUrlParam);
    this.setUserCodeTxt((String)lUrlParam.getParam("userCodeTxt"));
    this.setUserNameTxt((String)lUrlParam.getParam("userNameTxt"));
    if (lLogin.isShop()){
      this.setShopCodeTxt(lLogin.getMallShopCode());
    }else{
      this.setShopCodeTxt((String)lUrlParam.getParam("shopCodeTxt"));
    }
    this.setUserOperation((String)lUrlParam.getParam("userOperation"));
    this.setInitDateYearFromCbo((String)lUrlParam.getParam("initDateYearFromCbo"));
    this.setInitDateMonthFromCbo((String)lUrlParam.getParam("initDateMonthFromCbo"));
    this.setInitDateDayFromCbo((String)lUrlParam.getParam("initDateDayFromCbo"));
    this.setInitDateYearToCbo((String)lUrlParam.getParam("initDateYearToCbo"));
    this.setInitDateMonthToCbo((String)lUrlParam.getParam("initDateMonthToCbo"));
    this.setInitDateDayToCbo((String)lUrlParam.getParam("initDateDayToCbo"));
  }
  
  /**
   * <b>getCollection</b>
   * 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * @param lConnection データベースへの接続コネクション
   * @param SILogin     ログイン者情報をセットしたBean
   * @return レコードのセット
   * @throws なし
   */
  public Collection getCollection(Connection lConnection,SILogin lLogin) throws SIDBAccessException{
    Statement lStatement=null;
    ResultSet lResultSet=null;
    SIMngAccesslog lAccessLog=new SIMngAccesslog();
    StringBuffer lCountSqlBuf=new StringBuffer();//レコード数を求める
    StringBuffer lAccessLogSqlBuf=new StringBuffer();//管理側アクセスログ情報リストを求める
    StringBuffer lCommonSqlBuf=new StringBuffer();//共通条件のSQL文
    
    Collection lCmdtys=new ArrayList();
    
    if(!this.searchFlg){
      return lCmdtys;
    }
    
    //基本のSQL
    lAccessLogSqlBuf.append("SELECT * ");
    lAccessLogSqlBuf.append("FROM MngAccessLogTbl ");
    
    lCountSqlBuf.append("SELECT Count(UserCode) ");
    lCountSqlBuf.append("FROM MngAccessLogTbl ");
    
    lCommonSqlBuf.append("WHERE 1=1 ");
    
    if (lLogin.isShop()){
      lCommonSqlBuf.append("AND MallShopCode="+SIDBUtil.SQL2Str(lLogin.getMallShopCode()," "));
    }
    //検索の条件
    lCommonSqlBuf.append(this.conditionSQL);
    lCountSqlBuf.append(lCommonSqlBuf);
    
    //出力順
    lCommonSqlBuf.append(this.getOrderBySQL());
    lAccessLogSqlBuf.append(lCommonSqlBuf);
    
    //実行
    try{
      int lRecordCount=Integer.parseInt(SIDBUtil.getFirstData(lConnection, lCountSqlBuf.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:lAccessLogSqlBuf="+lAccessLogSqlBuf.toString());
      lStatement=lConnection.createStatement();
      lAccessLogSqlBuf.append(" LIMIT ").append(lPageSize);
      if(lFromInx>0) lAccessLogSqlBuf.append(" OFFSET ").append(lFromInx);
      lResultSet=lStatement.executeQuery(lAccessLogSqlBuf.toString());
      
      for (int jj=0;jj<lFromInx;jj++)lCmdtys.add(null);
      
      //管理側アクセスログレコードのセットの作成
      int lIndex=0;
      
      //管理側アクセスログレコードのセットの作成
      while (lResultSet.next()&&lIndex<lPageSize){
        lAccessLog=new SIMngAccesslog();
        lAccessLog.setEncode(SIConfig.SIENCODE_NONE);
        lAccessLog.setLogCode(lResultSet.getString("MngLogCode"));
        lAccessLog.setUserCode(lResultSet.getString("UserCode"));
        lAccessLog.setUserName(lResultSet.getString("UserName"));
        lAccessLog.setIpAddress(lResultSet.getString("IpAddress"));
        lAccessLog.setOperation(lResultSet.getString("Operation"));
        lAccessLog.setInitDateTime(SIDBUtil.getDateTime(lResultSet.getTimestamp("InitDateTime")));
        lCmdtys.add(lAccessLog);
        lIndex++;
      }
      for (int jj=lFromInx+lPageSize;jj<lRecordCount;jj++)lCmdtys.add(null);
      
    }catch(SQLException ex){
      ex.printStackTrace();
      throw new SIDBAccessException(ex);
    }finally{
      SIDBUtil.close(lResultSet,lStatement);
    }
    return lCmdtys;
  }
  
  /**
   * <b>validate</b>
   * 入力したデータをチェックして、同時にSQLの条件文を作成します。
   * @param lRequest クライアントからのリクエスト
   * @return なし
   * @throws なし
   */
  public void validate(HttpServletRequest lRequest,Connection lConnection){//7.2.0 ST0339 修正
    SICustomErrors errors=new SICustomErrors();
    SITableConditionManager lConditionMan=new SITableConditionManager();
    
    //管理者ID
    if (SIUtil.isNotNull(this.userCodeTxt)&&SICheckValid.checkValid(errors,"管理者ID",this.userCodeTxt,SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE)){
      lConditionMan.add(new SITableCondition("","UserCode",this.userCodeTxt,SIConfig.SICONDITION_TYPE_FRONTLIKE,SIConfig.SICONDITION_TYPE_AND));
    }
    
    //名前
    if (SIUtil.isNotNull(this.getUserNameTxt())){
      lConditionMan.add(new SITableCondition("", "UserName", this.getUserNameTxt(), SIConfig.SICONDITION_TYPE_LIKE, SIConfig.SICONDITION_TYPE_AND));
    }
    
    //日時(From)
    if (SIUtil.isNotNull(getInitDateFrom())&&SICheckValid.checkValid(errors,"日時From",getInitDateFrom(),SICheckDataConf.SICHECK_DATA_DATE_TYPE)){
      if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){
        lConditionMan.add(new SITableCondition("","initDateTime",getInitDateFrom(),SIConfig.SICONDITION_TYPE_GREATER_EQUAL,SIConfig.SICONDITION_TYPE_AND));
      }else{
        lConditionMan.add(new SITableCondition("","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)){
      if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){
        lConditionMan.add(new SITableCondition("","initDateTime",getInitDateTo()+" 23:59:59",SIConfig.SICONDITION_TYPE_LESS_EQUAL,SIConfig.SICONDITION_TYPE_AND));
      }else{
        lConditionMan.add(new SITableCondition("","initDateTime",new SIDateTimeType(getInitDateTo()+" 23:59:59"),SIConfig.SICONDITION_TYPE_LESS_EQUAL,SIConfig.SICONDITION_TYPE_AND));
      }
    }
    
    //オペレーション
    if (SIUtil.isNotNull(this.getUserOperation()) && !this.getUserOperation().equals("0")){
      lConditionMan.add(new SITableCondition("", "Operation", this.getUserOperation(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    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());
  }
}