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.SIWanted;
import jp.co.sint.config.SIConfig;
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.SICheckValid;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIUtil;
import jp.co.sint.tools.SIURLParameter;

import org.apache.log4j.Category;

public class UIWantedListCond extends SIBasic {
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  // ユーザ情報
  private String userCode = "";
  
  // 所属支店
  private String userBranch = "";
  
  // 掲載状態
  private String enableFlgCbo = "";
  
  // 商品名
  private String cmdtyNameTxt = "";
  
  // 掲載期間（年）From
  private String dispYearFrom = "";
  
  // 掲載期間（月）From
  private String dispMonthFrom = "";
  
  // 掲載期間（日）From
  private String dispDayFrom = "";
  
  // 掲載期間（年）To
  private String dispYearTo = "";
  
  // 掲載期間（月）To
  private String dispMonthTo = "";
  
  // 掲載期間（日）To
  private String dispDayTo = "";
  
  // SQL検索時の条件文
  private String conditionSQL = "";
  
  private boolean searchFlg = true;
  
  /**
   * UIWantedListCond コンストラクタ
   * 
   * @param lRequest リクエスト
   * @param lConnection コネクション
   * @return なし
   * @throws なし
   */
  public UIWantedListCond(HttpServletRequest lRequest, Connection lConnection) {
    this.setUserCode(SIHTMLUtil.getLogin(lRequest).getUserCode());
    try{
      String branch = SIDBUtil.getFirstData(lConnection, "SELECT belongingbranchcode FROM chargetbl WHERE chargecode="+SIDBUtil.SQL2Str(userCode));
      this.setUserBranch(branch);
    }catch(Exception e){
      e.printStackTrace();
    }
    this.setEnableFlgCbo("0");
    this.setConditionSQL(" AND enableFlg=1 ");
  }
  
  public String getUserCode() {
    return userCode;
  }
  
  public String getUserBranch() {
    return userBranch;
  }
  
  public String getEnableFlgCbo() {
    return enableFlgCbo;
  }
  
  public String getCmdtyNameTxt() {
    return cmdtyNameTxt;
  }
  
  public String getDispYearFrom() {
    return dispYearFrom;
  }
  
  public String getDispMonthFrom() {
    return dispMonthFrom;
  }
  
  public String getDispDayFrom() {
    return dispDayFrom;
  }
  
  public String getDispYearTo() {
    return dispYearTo;
  }
  
  public String getDispMonthTo() {
    return dispMonthTo;
  }
  
  public String getDispDayTo() {
    return dispDayTo;
  }
  
  public String getCondtionSQL() {
    return this.conditionSQL;
  }
  
  public void setUserCode(String userCode) {
    this.userCode = userCode;
  }
  
  public void setUserBranch(String userBranch) {
    this.userBranch = userBranch;
  }
  
  public void setEnableFlgCbo(String enableFlgCbo) {
    this.enableFlgCbo = enableFlgCbo;
  }
  
  public void setCmdtyNameTxt(String cmdtyNameTxt) {
    this.cmdtyNameTxt = cmdtyNameTxt;
  }
  
  public void setDispYearFrom(String dispYearFrom) {
    this.dispYearFrom = dispYearFrom;
  }
  
  public void setDispMonthFrom(String dispMonthFrom) {
    this.dispMonthFrom = dispMonthFrom;
  }
  
  public void setDispDayFrom(String dispDayFrom) {
    this.dispDayFrom = dispDayFrom;
  }
  
  public void setDispYearTo(String dispYearTo) {
    this.dispYearTo = dispYearTo;
  }
  
  public void setDispMonthTo(String dispMonthTo) {
    this.dispMonthTo = dispMonthTo;
  }
  
  public void setDispDayTo(String dispDayTo) {
    this.dispDayTo = dispDayTo;
  }
  
  public void setConditionSQL(String lConditionSQL) {
    if (lConditionSQL == null)
      lConditionSQL = "";
    this.conditionSQL = lConditionSQL;
  }
  
  private String getDispFrom() {
    if(SIUtil.isNull(dispYearFrom)&&SIUtil.isNull(dispMonthFrom)&&SIUtil.isNull(dispDayFrom)) return "";
    return dispYearFrom + "/" + dispMonthFrom + "/" + dispDayFrom;
  }
  
  private String getDispTo() {
    if(SIUtil.isNull(dispYearTo)&&SIUtil.isNull(dispMonthTo)&&SIUtil.isNull(dispDayTo)) return "";
    return dispYearTo + "/" + dispMonthTo + "/" + dispDayTo;
  }
  
  /**
   * <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.setEnableFlgCbo((String)lUrlParam.getParam("enableFlgCbo"));
    this.setDispYearFrom((String)lUrlParam.getParam("dispYearFrom"));
    this.setDispMonthFrom((String)lUrlParam.getParam("dispMonthFrom"));
    this.setDispDayFrom((String)lUrlParam.getParam("dispDayFrom"));
    this.setDispYearTo((String)lUrlParam.getParam("dispYearTo"));
    this.setDispMonthTo((String)lUrlParam.getParam("dispMonthTo"));
    this.setDispDayTo((String)lUrlParam.getParam("dispDayTo"));
    this.setCmdtyNameTxt((String)lUrlParam.getParam("cmdtyNameTxt"));
  }
  
  /**
   * <b>getCollection</b> 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * 
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws SIDBAccessException
   */
  public Collection getCollection(Connection lConnection) throws SIDBAccessException {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIWanted lWanted = new SIWanted();
    Collection lWantedListColl = new ArrayList();
    
    StringBuffer lSqlBuf = new StringBuffer();
    
    if (!this.searchFlg) {
      return lWantedListColl;
    }
    
    // 基本のSQL
    lSqlBuf.append("SELECT a.keyCode,a.keyBranch,a.cmdtyName,a.dispValue,a.memo,a.enableflg,");
    lSqlBuf.append("a.initDateTime,a.updateDateTime,a.initUser,a.updateUser,");
    lSqlBuf.append("a.branchCode,coalesce(b.branchName,'本部') AS branchName,");
    if (SIUtil.isNotNull(userBranch)&&"1".equals(userBranch)){//東京スタッフの場合
      lSqlBuf.append("CASE WHEN a.branchCode IS NULL THEN 1 WHEN a.branchCode = '1' THEN 1 ELSE 0 END AS deleteFlg ");
    }else if (SIUtil.isNotNull(userBranch)){//支店スタッフの場合
      lSqlBuf.append("CASE WHEN a.branchCode = ").append(userBranch).append(" THEN 1 ELSE 0 END AS deleteFlg ");
    }else{//その他
      lSqlBuf.append("1 AS deleteFlg ");
    }
    
    lSqlBuf.append("FROM wantedTbl a ");
    lSqlBuf.append("LEFT OUTER JOIN branchtbl b ON a.branchcode=b.branchcode ");
    
    lSqlBuf.append("WHERE 1=1 ");
    
    // 検索の条件
    lSqlBuf.append(this.conditionSQL);
    
    // 昇順に並べ替え
    lSqlBuf.append(" ORDER BY a.updateDateTime DESC,a.initDateTime DESC");
    
    log.debug("getCollection:lSqlBuf=" + lSqlBuf.toString());
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      
      // 色レコードのセットの作成
      while (lResultSet.next()) {
        lWanted = new SIWanted();
        lWanted.setEncode(SIConfig.SIENCODE_NONE);
        lWanted.setKeyCode(lResultSet.getString("keyCode"));
        lWanted.setKeyBranch(lResultSet.getString("keyBranch"));
        lWanted.setCmdtyName(lResultSet.getString("cmdtyName"));
        lWanted.setDispValue(lResultSet.getString("dispValue"));
        lWanted.setMemo(lResultSet.getString("memo"));
        lWanted.setEnableFlg(lResultSet.getString("enableFlg"));
        lWanted.setBranchCode(lResultSet.getString("branchCode"));
        lWanted.setBranchName(lResultSet.getString("branchName"));
        lWanted.setInitDateTime(lResultSet.getString("initDateTime"));
        lWanted.setUpdateDateTime(lResultSet.getString("updateDateTime"));
        lWanted.setInitUser(lResultSet.getString("initUser"));
        lWanted.setUpdateUser(lResultSet.getString("updateUser"));
        lWanted.setDeleteFlg(lResultSet.getString("deleteFlg"));
        lWantedListColl.add(lWanted);
      }
    } catch (Exception ex) {
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lWantedListColl;
  }
  
  /**
   * <b>validate</b> 入力したデータをチェックして、同時にSQLの条件文を作成します。
   * 
   * @param lRequest クライアントからのリクエスト
   * @return なし
   * @throws なし
   */
  public void validate(HttpServletRequest lRequest) {
    SICustomErrors errors = new SICustomErrors();
    SITableConditionManager lConditionMan = new SITableConditionManager();
    if("0".equals(this.enableFlgCbo)){
      lConditionMan.add(new SITableCondition("a", "enableFlg", "1", SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    } else {
      // 掲載状態
      lConditionMan.add(new SITableCondition("a", "enableFlg", "0", SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      
      // 掲載期間
      SICheckValid.checkDateValid(errors, "掲載期間From", getDispFrom());
      SICheckValid.checkDateValid(errors, "掲載期間To", getDispTo());
      if(errors.isEmpty()&&SIUtil.isNotNull(getDispFrom())&&SIUtil.isNotNull(getDispTo())){
        SICheckValid.checkDateGreaterValid(errors, "掲載期間To", "掲載期間From", getDispTo(), getDispFrom());
      }
      if(errors.isEmpty()){
        if(SIUtil.isNotNull(getDispFrom())){
          lConditionMan.add(new SITableCondition("a", "updatedatetime", getDispFrom(), SIConfig.SICONDITION_TYPE_GREATER, SIConfig.SICONDITION_TYPE_AND));
        }
        if(SIUtil.isNotNull(getDispTo())){
          lConditionMan.add(new SITableCondition("a", "updatedatetime", getDispFrom(), SIConfig.SICONDITION_TYPE_LESS, SIConfig.SICONDITION_TYPE_AND));
        }
      }
    }
    
    // 商品名
    if(SIUtil.isNotNull(getCmdtyNameTxt()) && SICheckValid.checkValid(errors, "商品名", getCmdtyNameTxt(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 128)){
      lConditionMan.add(new SITableCondition("a", "CmdtyName", this.getCmdtyNameTxt(), SIConfig.SICONDITION_TYPE_LIKE, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // エラーをセッションに設定
    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());
  }
}