/**
 * Copyright (c) 2003-2005 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.SIEstimateList;
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.SICustomError;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIURLParameter;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;

/**
 * @version $Id : UIFreeEstimateListCond.java,v 1.0 Exp $
 * @author : Naotaka Ohsugi <br>
 * Description :見積一覧検索用Bean
 * <p>
 * History
 * </p>
 * Author          Date        Reason
 * ==============  ==========  ===========================<br>
 * Naotaka Ohsugi  2008/03/27  Original
 */
public class UIFreeEstimateListCond extends SIBasic {
  
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  // ソート条件
  private String sort = "";
  // SQLの条件文
  private String conditionSQL = "";
  // 昇順、降順
  private String firstOrder = "0";
  //顧客名
  private String custName = "";
  // 顧客名カナ
  private String custPronName = "";
  // サロン名
  private String companyName = "";
  // 顧客コード
  private String custCode = "";
  // 電話番号
  private String tel = "";
  //メールアドレス
  private String email = "";
  // 支店名
  private String branchCode = "";
  // 担当者名
  private String chargeCode = "";
  // 販売経路
  private String orderRoute = "";
  // 見積番号From
  private String estimateCodeFrom = "";
  // 見積番号To
  private String estimateCodeTo = "";
  // 削除用チェックフラグ
  private String[] estimateCheck = null;
  // 詳細
  private String estimateCodeTxt = "";
  // ページ番号
  private String pageNumberTxt_list = "1";
  
  /*
   * getter of sort.
   */
  public String getSort() {
    return sort;
  }
  
  /*
   * setter of sort
   */
  public void setSort(String sort) {
    if (SIUtil.isNull(sort)) sort="";
    this.sort = sort;
  }
  
  /*
   * getter of firstOder
   */
  public String getFirstOrder() {
    return firstOrder;
  }
  
  /*
   * setter of firstOrder
   */
  public void setFirstOrder(String firstOrder) {
    if (SIUtil.isNull(firstOrder)) firstOrder="0";
    this.firstOrder = firstOrder;
  }
  
  /*
   * getter of branchCode
   */
  public String getBranchCode() {
    return branchCode;
  }
  
  /*
   * setter of branchCode
   */
  public void setBranchCode(String branchCode) {
    if (SIUtil.isNull(branchCode)) branchCode="";
    this.branchCode = branchCode;
  }
  
  /*
   * getter of chargeCode
   */
  public String getChargeCode() {
    return chargeCode;
  }
  
  /*
   * setter of chargeCode
   */
  public void setChargeCode(String chargeCode) {
    if (SIUtil.isNull(chargeCode)) chargeCode="";
    this.chargeCode = chargeCode;
  }
  
  /*
   * getter of companyName
   */
  public String getCompanyName() {
    return companyName;
  }
  
  /*
   * setter of companyName
   */
  public void setCompanyName(String companyName) {
    if (SIUtil.isNull(companyName)) companyName="";
    this.companyName = companyName;
  }
  
  /*
   * getter of custCode
   */
  public String getCustCode() {
    return custCode;
  }
  
  /*
   * setter of custCode
   */
  public void setCustCode(String custCode) {
    if (SIUtil.isNull(custCode)) custCode="";
    this.custCode = custCode;
  }
  
  /*
   * getter of custPronName
   */
  public String getCustPronName() {
    return custPronName;
  }
  
  /*
   * setter of custPronName
   */
  public void setCustPronName(String custPronName) {
    if (SIUtil.isNull(custPronName)) custPronName="";
    this.custPronName = custPronName;
  }
  
  /*
   * getter of estimateCodeFrom
   */
  public String getEstimateCodeFrom() {
    return estimateCodeFrom;
  }
  
  /*
   * setter of estimateCodeFrom
   */
  public void setEstimateCodeFrom(String estimateCodeFrom) {
    if (SIUtil.isNull(estimateCodeFrom)) estimateCodeFrom="";
    this.estimateCodeFrom = estimateCodeFrom;
  }
  
  /*
   * getter of estimateCodeTo
   */
  public String getEstimateCodeTo() {
    return estimateCodeTo;
  }
  
  /*
   * setter of estimateCodeTo
   */
  public void setEstimateCodeTo(String estimateCodeTo) {
    if (SIUtil.isNull(estimateCodeTo)) estimateCodeTo="";
    this.estimateCodeTo = estimateCodeTo;
  }
  
  /*
   * getter of orderRoute
   */
  public String getOrderRoute() {
    return orderRoute;
  }
  
  /*
   * setter of orderRoute
   */
  public void setOrderRoute(String orderRoute) {
    if (SIUtil.isNull(orderRoute)) orderRoute="";
    this.orderRoute = orderRoute;
  }
  
  /*
   * getter of tel
   */
  public String getTel() {
    return tel;
  }
  
  /*
   * setter of tel
   */
  public void setTel(String tel) {
    if (SIUtil.isNull(tel)) tel="";
    this.tel = tel;
  }
  
  /*
   * getter of estimateCheck
   */
  public String[] getEstimateCheck() {
    return estimateCheck;
  }
  
  /*
   * setter of estimateCheck
   */
  public void setEstimateCheck(String[] estimateCheck) {
    if (estimateCheck==null) estimateCheck=new String[0];
    this.estimateCheck = estimateCheck;
  }
  
  /*
   * getter of estimateCodeTxt
   */
  public String getEstimateCodeTxt() {
    return estimateCodeTxt;
  }
  
  /*
   * setter of estimateCodeTxt
   */
  public void setEstimateCodeTxt(String estimateCodeTxt) {
    if (SIUtil.isNull(estimateCodeTxt)) estimateCodeTxt="";
    this.estimateCodeTxt = estimateCodeTxt;
  }
  
  /*
   * getter of pageNumberTxt_list
   */
  public String getPageNumberTxt_list() {
    return pageNumberTxt_list;
  }
  
  /*
   * setter of pageNumberTxt_list
   */
  public void setPageNumberTxt_list(String pageNumberTxt_list) {
    if (SIUtil.isNull(pageNumberTxt_list)) pageNumberTxt_list="1";
    this.pageNumberTxt_list = pageNumberTxt_list;
  }
  
  public String getCustName() {
    return custName;
  }
  
  public String getEmail() {
    return email;
  }
  
  public void setCustName(String custName) {
    if (SIUtil.isNull(custName)) custName = "";
    this.custName = custName;
  }
  
  public void setEmail(String email) {
    if (SIUtil.isNull(email)) email = "";
    this.email = email;
  }
  
  public void init(HttpServletRequest lRequest, SIURLParameter lUrlParam) {
    super.init(lRequest, lUrlParam);
    
    this.setSort((String) lUrlParam.getParam("sort"));
    this.setFirstOrder((String) lUrlParam.getParam("firstOrderTxt"));
    this.setPageNumberTxt_list((String) lUrlParam.getParam("pageNumberTxt_list"));
    this.setCustName((String) lUrlParam.getParam("custName"));
    this.setCustPronName((String) lUrlParam.getParam("custPronName"));
    this.setCompanyName((String) lUrlParam.getParam("companyName"));
    this.setCustCode((String) lUrlParam.getParam("custCode"));
    this.setEmail((String) lUrlParam.getParam("email"));
    this.setTel((String) lUrlParam.getParam("tel"));
    this.setBranchCode((String) lUrlParam.getParam("branchCode"));
    this.setChargeCode((String) lUrlParam.getParam("chargeCode"));
    this.setOrderRoute((String) lUrlParam.getParam("orderRoute"));
    this.setEstimateCodeFrom((String) lUrlParam.getParam("estimateCodeFrom"));
    this.setEstimateCodeTo((String) lUrlParam.getParam("estimateCodeTo"));
    this.setEstimateCodeTxt((String) lUrlParam.getParam("detailTxt"));
  }
  
  public void initDel(HttpServletRequest lRequest, SIURLParameter lUrlParam) {
    super.init(lRequest, lUrlParam);
    this.setEstimateCheck(lRequest.getParameterValues("estimateChk"));
  }
  
  public boolean validateDelete(HttpServletRequest lRequest) {
    SICustomErrors errors = new SICustomErrors();
    
    // チェックボックスのチェック
    if (this.getEstimateCheck() == null || this.getEstimateCheck().length == 0) {
      errors.addError(new SICustomError("input.data.checkbox.require", "削除"));
    }
    if (!errors.isEmpty()) lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
    else lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    
    return errors.isEmpty();
  }
  
  /**
   * <b>validate</b> 入力したデータをチェックして、同時にSQLの条件文を作成します。
   * 
   * @param lRequest クライアントからのリクエスト
   */
  public void validate(HttpServletRequest lRequest) {
    SICustomErrors errors = new SICustomErrors();
    SITableConditionManager lConditionMan = new SITableConditionManager();
    
    //顧客名
    if (SIUtil.isNotNull(custName) && SICheckValid.checkValid(errors,"顧客名",custName,SICheckDataConf.SICHECK_DATA_ZENKAKU_TYPE)
        && SICheckValid.checkValid(errors, "顧客名", custName, SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 50)){
      String[] custNameList = this.getCustName().split(" |　");
      for (int i=0;i<custNameList.length;i++) {
        lConditionMan.add(new SITableCondition("a", "custName", custNameList[i], SIConfig.SICONDITION_TYPE_LIKE, SIConfig.SICONDITION_TYPE_AND));
      }
    }
    
    //顧客名カナ
    if (SIUtil.isNotNull(custPronName) && SICheckValid.checkValid(errors,"顧客名カナ",custPronName,SICheckDataConf.SICHECK_DATA_ZENKAKU_KANA_TYPE)
        && SICheckValid.checkValid(errors, "顧客名カナ", custPronName, SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 50)){
      String[] custPronNameList = this.getCustPronName().split(" |　");
      for (int j=0;j<custPronNameList.length;j++) {
        lConditionMan.add(new SITableCondition("a", "custPronName", custPronNameList[j], SIConfig.SICONDITION_TYPE_LIKE, SIConfig.SICONDITION_TYPE_AND));
      }
    }
    // サロン名
    if (SIUtil.isNotNull(companyName) && SICheckValid.checkValid(errors, "サロン名", companyName, SICheckDataConf.SICHECK_DATA_ZENKAKU_TYPE)
        && SICheckValid.checkValid(errors, "サロン名", companyName, SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 100)) {
      lConditionMan.add(new SITableCondition("a", "companyName", companyName, SIConfig.SICONDITION_TYPE_LIKE, SIConfig.SICONDITION_TYPE_AND));
    }
    //メール
    if (SIUtil.isNotNull(email) && SICheckValid.checkValid(errors,"メールアドレス",custCode,SICheckDataConf.SICHECK_DATA_MAIL_TYPE)
        && SICheckValid.checkValid(errors, "メールアドレス", email, SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 256)){
      lConditionMan.add(new SITableCondition("a","email",email,SIConfig.SICONDITION_TYPE_LIKE,SIConfig.SICONDITION_TYPE_AND));
    }
    // 顧客コード
    if (SIUtil.isNotNull(custCode) && SICheckValid.checkValid(errors, "顧客コード", custCode, SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)
        && SICheckValid.checkValid(errors, "顧客コード", custCode, SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 10)) {
      lConditionMan.add(new SITableCondition("a", "custCode", custCode, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    // 電話番号
    String conditionSQL2 = "";
    if (SIUtil.isNotNull(this.getTel()) && SICheckValid.checkValid(errors, "電話番号", this.getTel(), SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)) {
      conditionSQL2 += " AND (a.tel like'%" + this.getTel() + "%' OR a.storetel like'%" + this.getTel() + "%') ";
    }
    // 支店名
    if (SIUtil.isNotNull(branchCode) && !branchCode.equals("XX")) {
      lConditionMan.add(new SITableCondition("a", "branchCode", branchCode, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    // 担当者名
    if (SIUtil.isNotNull(chargeCode) && !branchCode.equals("XX")) {
      lConditionMan.add(new SITableCondition("a", "chargeCode", chargeCode, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    // 見積番号
    if (SIUtil.isNotNull(estimateCodeFrom) && SICheckValid.checkValid(errors, "見積番号", estimateCodeFrom, SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)
        && SICheckValid.checkValid(errors, "見積番号", estimateCodeFrom, SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 8)) {
      lConditionMan.add(new SITableCondition("a", "estimateCode", estimateCodeFrom, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    /*
    // 見積番号From
    if (SIUtil.isNotNull(estimateCodeFrom) && SICheckValid.checkValid(errors, "見積番号From", estimateCodeFrom, SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)
        && SICheckValid.checkValid(errors, "見積番号From", estimateCodeFrom, SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 8)) {
      lConditionMan.add(new SITableCondition("a", "estimateCode", estimateCodeFrom, SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    // 見積番号To
    if (SIUtil.isNotNull(estimateCodeTo) && SICheckValid.checkValid(errors, "見積番号To", estimateCodeTo, SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)
        && SICheckValid.checkValid(errors, "見積番号To", estimateCodeTo, SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 8)) {
      lConditionMan.add(new SITableCondition("a", "estimateCode", estimateCodeTo, SIConfig.SICONDITION_TYPE_LESS_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    */
    // 販売経路
    if (SIUtil.isNotNull(orderRoute)) {// 「EC」追加にあわせて修正
      lConditionMan.add(new SITableCondition("a", "orderRoute", orderRoute, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    /*
    try {
      // 見積番号大小
      if (Integer.parseInt(estimateCodeFrom) != Integer.parseInt(estimateCodeTo)) {
        SICheckValid.checkValid(errors, "見積番号From", "見積番号To", estimateCodeFrom, estimateCodeTo, SICheckDataConf.SICHECK_DATA_VALUE_LESS_TYPE);
      }
    } catch (NumberFormatException e) {}
    */
    if (!errors.isEmpty()) {
      this.conditionSQL = "";
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
    } else {
      lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
      // 条件文の設定
      this.conditionSQL = lConditionMan.getCondtionSQL() + conditionSQL2;
    }
  }
  
  /**
   * getCollection
   * 
   * @param Connection
   * @return Collection
   * @throws SIDBAccessException
   */
  public Collection getCollection(Connection dbConnection) throws SIDBAccessException {
    Collection estimateColl = new ArrayList();
    Statement statement = null;
    ResultSet resultSet = null;
    SIEstimateList estimateList = new SIEstimateList();
    StringBuffer sqlStatement = new StringBuffer();
    // 出力項目
    sqlStatement.append("SELECT a.estimatecode,a.initdatetime,c.chargename ");
    sqlStatement.append(",coalesce(a.email::text,''::text) AS email ");
    sqlStatement.append(",coalesce(a.custname::text,''::text) AS custname ");
    sqlStatement.append(",coalesce(a.memberlevelcode::numeric,0::numeric) AS memberlevelcode ");
    // EDBTG003-00 mng-paku mod start
//    sqlStatement.append(",(b.total + b.totaldeliveryfee + COALESCE(a.discountdeliveryfee,0) + COALESCE(a.fee,0) + COALESCE(a.discountFee,0) - COALESCE(a.sumofdiscount,0) - COALESCE(a.usepoint,0)) AS totalprice ");
    sqlStatement.append(",(b.total + b.totaldeliveryfee + COALESCE(a.discountdeliveryfee,0) + COALESCE(a.fee,0) + COALESCE(a.discountFee,0) - COALESCE(a.sumofdiscount,0) - COALESCE(a.usepoint,0) - COALESCE(a.setdiscount,0)) AS totalprice ");
    // EDBTG003-00 mng-paku mod end
    sqlStatement.append("FROM freeestimatetbl a ");
    sqlStatement.append("LEFT OUTER JOIN chargetbl c ON a.chargecode=c.chargecode ");
    sqlStatement.append(",(SELECT estimatecode,SUM(price*amount) AS total ");
    sqlStatement.append("  ,SUM(CASE WHEN deliverycode='0' THEN deliveryfee*amount ELSE deliveryfee END) AS totaldeliveryfee ");
    sqlStatement.append(" FROM freeestimatedetailtbl GROUP BY estimatecode) b ");
    sqlStatement.append("WHERE a.estimatecode = b.estimatecode ");
    sqlStatement.append(conditionSQL);
    
    if (sort == null || sort.equals("")) sort = "initDateTime";
    
    sqlStatement.append("ORDER BY ").append(sort);
    if (firstOrder.equals("0")) {
      sqlStatement.append(" DESC");
      if (sort.equals("initDateTime")) sqlStatement.append(",estimatecode DESC");
    } else {
      sqlStatement.append(" ASC");
    }
    // 実行
    try {
      statement = dbConnection.createStatement();
      log.debug("SQL=" + sqlStatement.toString());
      resultSet = statement.executeQuery(sqlStatement.toString());
      
      // 商品レコードのセットの作成
      while (resultSet.next()) {
        estimateList = new SIEstimateList();
        estimateList.setEstimateCode(resultSet.getString("estimatecode"));
        estimateList.setEmail(resultSet.getString("email"));
        estimateList.setChargeName(resultSet.getString("chargeName"));
        estimateList.setEmail(resultSet.getString("email"));
        estimateList.setCustName(resultSet.getString("custname"));
        estimateList.setMemberLevelCode(resultSet.getString("memberLevelCode"));
        estimateList.setInitDateTime(resultSet.getString("initdatetime"));
        
        if (SIUtil.isNotNull(resultSet.getString("totalprice"))) {// 商品合計
          estimateList.setTotal(resultSet.getString("totalprice"));
        }
        estimateColl.add(estimateList);
      }
    } catch (Exception ex) {
      log.debug("exception sql = " + sqlStatement.toString());
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return estimateColl;
  }
}