/**
 * 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.SICharge;
import jp.co.sint.basic.SINameValue;
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.SIURLParameter;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;

public class UIChargeListCond extends SIBasic {
  //ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  //担当者コード
  private String chargeCodeTxt = "";
  
  //担当者名
  private String chargeNameTxt = "";
  
  //メールアドレス
  private String emailTxt = "";
  
  //所属支店コード
  private String belongingBranchCodeTxt = "";
  
  //所属支店コード
  private String belongingBranchNameTxt = "";
  
  //担当者コード（検索条件）
  private String chargeCode = "";
  
  //担当者名（検索条件）
  private String chargeName = "";
  
  //所属支店コード（検索条件）
  private String belongingBranchCode = "";
  
  //SQL検索時の条件文
  private String conditionSQL = "";
  
  /**
   * UICompanyListCond
   * コンストラクタ
   * @param なし
   * @return なし
   * @throws なし
   */
  public UIChargeListCond() {}
  
  /**
   * UICompanyListCond
   * コンストラクタ
   * @param lRequest リクエスト
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  public UIChargeListCond(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    this.init(lRequest,lUrlParam);
  }
  
  //getter of 担当者コード
  public String getChargeCodeTxt() {
    return chargeCodeTxt;
  }
  
  //getter of 担当者名
  public String getChargeNameTxt() {
    return chargeNameTxt;
  }
  
  //getter of メールアドレス
  public String getEmailTxt() {
    return emailTxt;
  }
  
  //getter of 所属支店コード
  public String getBelongingBranchCodeTxt() {
    return belongingBranchCodeTxt;
  }
  
  //getter of 所属支店名
  public String getBelongingBranchNameTxt() {
    return belongingBranchNameTxt;
  }
  
  //getter of 担当者コード（検索条件）
  public String getChargeCode() {
    return chargeCode;
  }
  
  //getter of 担当者名（検索条件）
  public String getChargeName() {
    return chargeName;
  }
  
  //getter of 所属支店コード（検索条件）
  public String getBelongingBranchCode() {
    return belongingBranchCode;
  }
  
  //getter of 条件文
  public String getCondtionSQL(){
    return this.conditionSQL;
  }
  
  //setter of 担当者コード
  public void setChargeCodeTxt(String lChargeCodeTxt) {
    if (SIUtil.isNull(lChargeCodeTxt)) lChargeCodeTxt = "";
    this.chargeCodeTxt=SIUtil.changeTo(lChargeCodeTxt.trim(), this.encode);
  }
  
  //setter of 担当者名
  public void setChargeNameTxt(String lChargeNameTxt) {
    if (SIUtil.isNull(lChargeNameTxt)) lChargeNameTxt = "";
    this.chargeNameTxt=SIUtil.changeTo(lChargeNameTxt.trim(), this.encode);
  }
  
  //setter of メールアドレス
  public void setEmailTxt(String lEmailTxt) {
    if (SIUtil.isNull(lEmailTxt)) lEmailTxt = "";
    this.emailTxt=SIUtil.changeTo(lEmailTxt.trim(), this.encode);
  }
  
  //setter of 所属支店コード
  public void setBelongingBranchCodeTxt(String lBelongingBranchCodeTxt) {
    if (SIUtil.isNull(lBelongingBranchCodeTxt)) lBelongingBranchCodeTxt = "";
    this.belongingBranchCodeTxt=SIUtil.changeTo(lBelongingBranchCodeTxt.trim(), this.encode);
  }
  
  //setter of 所属支店名
  public void setBelongingBranchNameTxt(String lBelongingBranchNameTxt) {
    if (SIUtil.isNull(lBelongingBranchNameTxt)) lBelongingBranchNameTxt = "";
    this.belongingBranchNameTxt=SIUtil.changeTo(lBelongingBranchNameTxt.trim(), this.encode);
  }
  
  //setter of 担当者コード（検索条件）
  public void setChargeCode(String lChargeCode) {
    if (SIUtil.isNull(lChargeCode)) lChargeCode = "";
    this.chargeCode=SIUtil.changeTo(lChargeCode.trim(), this.encode);
  }
  
  //setter of 担当者名（検索条件）
  public void setChargeName(String lChargeName) {
    if (SIUtil.isNull(lChargeName)) lChargeName = "";
    this.chargeName=SIUtil.changeTo(lChargeName.trim(), this.encode);
  }
  
  //setter of 所属支店コード（検索条件）
  public void setBelongingBranchCode(String lBelongingBranchCode) {
    if (SIUtil.isNull(lBelongingBranchCode)) lBelongingBranchCode = "";
    this.belongingBranchCode=SIUtil.changeTo(lBelongingBranchCode.trim(), this.encode);
  }
  
  //setter of 条件文
  public void setConditionSQL(String lConditionSQL){
    if (lConditionSQL==null)lConditionSQL="";
    this.conditionSQL=lConditionSQL;
  }
  
  /**
   * <b>init</b>
   * 入力したデータを基づいて、このbeansを設定します。
   * @param request クライアントからリクエスト
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  public void init(HttpServletRequest request,SIURLParameter urlParam){
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    super.init(request,urlParam);
    this.setChargeCode((String) urlParam.getParam("chargeCode"));//担当者コード
    this.setChargeName((String) urlParam.getParam("chargeName"));//担当者名
    this.setBelongingBranchCode((String) urlParam.getParam("belongingBranch"));//所属支店コード
  }
  
  public void initInsert(HttpServletRequest request,SIURLParameter urlParam){
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    super.init(request,urlParam);
    this.setChargeCodeTxt((String) urlParam.getParam("chargeCodeTxt"));//担当者コード
    this.setChargeNameTxt((String) urlParam.getParam("chargeNameTxt"));//担当者名
    this.setEmailTxt((String) urlParam.getParam("emailTxt"));//メールアドレス
    this.setBelongingBranchNameTxt((String) urlParam.getParam("belongingBranchNameTxt"));//所属支店コード
  }
  
  /**
   * <b>validate</b
   * 入力したデータをチェックします。
   * @param lRequest クライアントからのリクエスト
   * @return なし
   * @throws なし
   */
  
  public boolean validate(HttpServletRequest lRequest,Connection lConnection){
    SICustomErrors errors=new SICustomErrors();
    StringBuffer lSqlBuf = new StringBuffer();
    
    //担当者コード
    SICheckValid.checkValid(errors,"担当者コード",getChargeCode(),SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE);
    SICheckValid.checkValid(errors,"担当者コード",getChargeCode(),SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 5);
    
    //担当者名
    SICheckValid.checkValid(errors,"担当者名",getChargeName(),SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 24);
    
    if (!errors.isEmpty()) lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK,errors);
    else {
      lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK);
      SITableConditionManager lConditionMan = new SITableConditionManager();
      SITableCondition DD = new SITableCondition("", "a.chargeCode", this.getChargeCode(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
      DD.setBlankEnable(false);
      lConditionMan.add(DD);
      DD = new SITableCondition("", "a.chargeName", this.getChargeName(), SIConfig.SICONDITION_TYPE_LIKE, SIConfig.SICONDITION_TYPE_AND);
      lConditionMan.add(DD);
      DD = new SITableCondition("", "a.belongingBranchCode", this.getBelongingBranchCode(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
      lConditionMan.add(DD);
      this.setConditionSQL(lConditionMan.getCondtionSQL());
    }
    return errors.isEmpty();
  }
  
  public Collection getBranchCollection(Connection lConnection) throws SIDBAccessException{
    return getBranchCollection(lConnection,false);
  }
  
  /**
   * <b>getBranchCollection</b>
   * 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws SIDBAccessException
   */
  public Collection getBranchCollection(Connection lConnection,boolean lAddBlank) throws SIDBAccessException{
    Collection lResultColl=new ArrayList();
    String lSql = "SELECT BranchName, BranchCode FROM BranchTbl WHERE BranchFlg='0' ORDER BY BranchCode ASC";
    
    try {
      lResultColl=SIDBUtil.getCollection(lConnection,lSql,true,lAddBlank);
    } catch (SIDBAccessException e) {
      e.printStackTrace();
    }
    return lResultColl;
  }
  
  /**
   * <b>getCollection</b>
   * 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws SIDBAccessException
   */
  public Collection getCollection(Connection lConnection) throws SIDBAccessException{
    Statement lStatement=null;
    ResultSet lResultSet=null;
    Collection lResultColl = new ArrayList();
    StringBuffer lSqlBuf = new StringBuffer();
    StringBuffer lCountBuf = new StringBuffer();
    
    //SQL
    lSqlBuf.append("SELECT a.chargecode,a.chargename,a.email,b.branchcode,b.branchname, ");
    lSqlBuf.append("CASE WHEN c.chargecode IS NULL AND d.chargecode IS NULL AND e.chargecode IS NULL AND f.chargecode IS NULL ");
    lSqlBuf.append("      AND g.storecharge IS NULL AND h.shipcharge IS NULL AND i.chargecode IS NULL THEN 0 ELSE 1 END AS DetailCount, ");
    lSqlBuf.append("a.enableflg,a.otherAddrFlg ");
    lSqlBuf.append("FROM branchtbl b,chargeTbl a ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT chargecode FROM ordertbl WHERE enabledflg='1' GROUP BY chargecode) c ");
    lSqlBuf.append("ON a.chargecode=c.chargecode ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT chargecode FROM estimatetbl GROUP BY chargecode) d ");
    lSqlBuf.append("ON a.chargecode=d.chargecode ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT chargecode FROM freeestimatetbl GROUP BY chargecode) e ");
    lSqlBuf.append("ON a.chargecode=e.chargecode ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT chargecode FROM keeptbl GROUP BY chargecode) f ");
    lSqlBuf.append("ON a.chargecode=f.chargecode ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT storecharge FROM storehistorytbl GROUP BY storecharge) g ");
    lSqlBuf.append("ON a.chargecode=g.storecharge ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT shipcharge FROM shiphistorytbl GROUP BY shipcharge) h ");
    lSqlBuf.append("ON a.chargecode=h.shipcharge ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT chargecode FROM custtbl GROUP BY chargecode) i ");
    lSqlBuf.append("ON a.chargecode=i.chargecode ");
    lSqlBuf.append("WHERE a.belongingbranchcode=b.branchcode ");
    lSqlBuf.append(this.getCondtionSQL());
    lSqlBuf.append("ORDER BY a.BelongingBranchCode ASC ,a.ChargeCode ASC");
    
    lCountBuf.append("SELECT count(*) FROM chargetbl a,branchtbl b WHERE a.belongingbranchcode=b.branchcode ").append(this.getCondtionSQL());
    
    //実行
    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);
      
      lStatement = lConnection.createStatement();
      lSqlBuf.append(" LIMIT ").append(lPageSize);
      if(lFromInx>0) lSqlBuf.append(" OFFSET ").append(lFromInx);
      log.debug("getCollection:lChargeBuf=" + lSqlBuf.toString());
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      
      for (int jj = 0; jj < lFromInx; jj++) lResultColl.add(null);
      int lIndex = 0;
      
      // レコードのセットの作成
      while (lResultSet.next() && lIndex < lPageSize) {
        SICharge lCharge = new SICharge();
        lCharge.setEncode(SIConfig.SIENCODE_NONE);
        lCharge.setChargeCode(lResultSet.getString("chargecode"));
        lCharge.setChargeName(lResultSet.getString("chargename"));
        lCharge.setEmail(lResultSet.getString("email"));
        lCharge.setBelongingBranchCode(lResultSet.getString("branchcode"));
        lCharge.setBelongingBranchName(lResultSet.getString("branchname"));
        lCharge.setDetailCount(lResultSet.getString("DetailCount"));
        lCharge.setEnableFlg(lResultSet.getString("EnableFlg"));
        lCharge.setOtherAddrFlg(lResultSet.getString("OtherAddrFlg"));
        lResultColl.add(lCharge);
        lIndex++;
      }
      for (int jj = lFromInx + lPageSize; jj < lRecordCount; jj++) lResultColl.add(null);
    }catch(Exception ex){
      throw new SIDBAccessException(ex);
    }finally{
      SIDBUtil.close(lResultSet,lStatement);
    }
    return lResultColl;
  }
  
  public static Collection getNameCollection(Connection conn) {
    Collection coll = new ArrayList();
    Statement statement = null;
    ResultSet resultSet = null;
    
    String lSql = "SELECT * FROM chargetbl WHERE enableflg='1' ORDER BY belongingbranchcode,chargecode";
    try {
      statement = conn.createStatement();
      resultSet = statement.executeQuery(lSql);
      while (resultSet.next()) {
        SICharge charge = new SICharge();
        charge.setChargeCode(resultSet.getString("chargecode"));
        charge.setChargeName(resultSet.getString("chargename"));
        charge.setBelongingBranchCode(resultSet.getString("belongingbranchcode"));
        coll.add(charge);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(statement, resultSet);
    }
    return coll;
  }
  
  public static Collection getSINameCollection(Connection conn) {
    Collection coll = new ArrayList();
    Statement statement = null;
    ResultSet resultSet = null;
    StringBuffer sqlStatement = new StringBuffer();
    SINameValue charge = new SINameValue();
    
    charge.setValue("XX");
    charge.setName("選択してください");
    coll.add(charge);
    sqlStatement = new StringBuffer();
    sqlStatement.append("SELECT * FROM chargetbl ORDER BY belongingbranchcode,chargecode");
    try {
      statement = conn.createStatement();
      resultSet = statement.executeQuery(sqlStatement.toString());
      while (resultSet.next()) {
        charge = new SINameValue();
        charge.setValue(resultSet.getString("chargecode"));
        charge.setName(resultSet.getString("chargename"));
        coll.add(charge);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(statement, resultSet);
    }
    return coll;
  }
}