/**
 * 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.commons.lang.StringUtils;
import org.apache.log4j.Category;

/**
 * @version $Id : UIEstimateListCond.java,v 1.0 Exp $
 * @author : Tsuyoshi Yagi <br>
 *         Description :見積一覧検索用Bean
 *         <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>
 *         Tsuyoshi Yagi 2005/11/28 Original
 */
public class UIEstimateListCond  extends SIBasic {
  
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  //ソート条件
  private String sort="";
  //SQLの条件文
  private String conditionSQL = "";
  //昇順、降順
  private String firstOder = "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 = "";
  //キープ番号From
  private String keepCodeFrom = "";
  //キープ番号To
  private String keepCodeTo = "";
  //販売経路
  private String orderRoute = "";
  //見積番号From
  private String estimateCodeFrom = "";
  //見積番号To
  private String estimateCodeTo = "";
  //見積作成日
  private String estimateDateFlg = "0";
  //見積状態
  private String estimateStatusFlg = "0";
  //削除用チェックフラグ
  private String[] estimateCheck = null;
  //詳細
  private String estimateCodeTxt = "";
  //取り置きメール送信フラグ
  private String sendMailCbo = "";
  //ページ番号
  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 getFirstOder() {
    return firstOder;
  }
  
  /*
   * setter of firstOder
   */
  public void setFirstOder(String firstOder) {
    if (SIUtil.isNull(firstOder)) firstOder="0";
    this.firstOder = firstOder;
  }
  
  /*
   * 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 estimateDateFlg
   */
  public String getEstimateDateFlg() {
    return estimateDateFlg;
  }
  
  /*
   * setter of estimateDateFlg
   */
  public void setEstimateDateFlg(String estimateDateFlg) {
    if (SIUtil.isNull(estimateDateFlg)) estimateDateFlg="0";
    this.estimateDateFlg = estimateDateFlg;
  }
  
  /*
   * getter of estimateStatusFlg
   */
  public String getEstimateStatusFlg() {
    return estimateStatusFlg;
  }
  
  /*
   * setter of estimateStatusFlg
   */
  public void setEstimateStatusFlg(String estimateStatusFlg) {
    if (SIUtil.isNull(estimateStatusFlg)) estimateStatusFlg="0";
    this.estimateStatusFlg = estimateStatusFlg;
  }
  
  /*
   * getter of keepCodeFrom
   */
  public String getKeepCodeFrom() {
    return keepCodeFrom;
  }
  
  /*
   * setter of keepCodeFrom
   */
  public void setKeepCodeFrom(String keepCodeFrom) {
    if (SIUtil.isNull(keepCodeFrom)) keepCodeFrom="";
    this.keepCodeFrom = keepCodeFrom;
  }
  
  /*
   * getter of keepCodeTo
   */
  public String getKeepCodeTo() {
    return keepCodeTo;
  }
  
  /*
   * setter of keepCodeTo
   */
  public void setKeepCodeTo(String keepCodeTo) {
    if (SIUtil.isNull(keepCodeTo)) keepCodeTo="";
    this.keepCodeTo = keepCodeTo;
  }
  
  /*
   * 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 sendMailCbo
   */
  public String getSendMailCbo() {
    return sendMailCbo;
  }
  
  /*
   * setter of sendMailCbo
   */
  public void setSendMailCbo(String sendMailCbo) {
    if (SIUtil.isNull(sendMailCbo)) sendMailCbo="";
    this.sendMailCbo = sendMailCbo;
  }
  
  /*
   * 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.setFirstOder((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.setKeepCodeFrom((String) lUrlParam.getParam("keepCodeFrom"));
    this.setKeepCodeTo((String) lUrlParam.getParam("keepCodeTo"));
    this.setOrderRoute((String) lUrlParam.getParam("orderRoute"));
    this.setEstimateCodeFrom((String) lUrlParam.getParam("estimateCodeFrom"));
    this.setEstimateCodeTo((String) lUrlParam.getParam("estimateCodeTo"));
    this.setEstimateDateFlg((String) lUrlParam.getParam("estimateDateFlg"));
    this.setEstimateStatusFlg((String) lUrlParam.getParam("estimateStatusFlg"));
    this.setEstimateCodeTxt((String) lUrlParam.getParam("detailTxt"));
    this.setSendMailCbo((String) lUrlParam.getParam("sendMailCbo"));
  }
  
  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));
      }
    }
    //サロン名
    String conditionSQL2="";
    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)){
      conditionSQL2+=" AND (a.companyName like'%"+this.getCompanyName()+"%' OR f.companyName_d like'%"+this.getCompanyName()+"%') ";
    }
    //メール
    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));
    }
    //電話番号
    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()+"%' OR f.tel 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(keepCodeFrom) && SICheckValid.checkValid(errors,"キープ番号",keepCodeFrom,SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)
        && SICheckValid.checkValid(errors, "キープ番号", keepCodeFrom, SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 8)){
      lConditionMan.add(new SITableCondition("a","keepnumber",keepCodeFrom,SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    }
    /*
    //キープ番号From
    if (SIUtil.isNotNull(keepCodeFrom) && SICheckValid.checkValid(errors,"キープ番号From",keepCodeFrom,SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)
        && SICheckValid.checkValid(errors, "キープ番号From", keepCodeFrom, SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 8)){
      lConditionMan.add(new SITableCondition("a","keepnumber",keepCodeFrom,SIConfig.SICONDITION_TYPE_GREATER_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    }
    //キープ番号To
    if (SIUtil.isNotNull(keepCodeTo) && SICheckValid.checkValid(errors,"キープ番号To",keepCodeTo,SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)
        && SICheckValid.checkValid(errors, "キープ番号To", keepCodeTo, SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 8)){
      lConditionMan.add(new SITableCondition("a","keepnumber",keepCodeTo,SIConfig.SICONDITION_TYPE_LESS_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    }
    */
    //見積番号From
    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));
    }
    
    //見積状態
    if(estimateStatusFlg.equals("0")){
      lConditionMan.add(new SITableCondition("a","status","1",SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    }else if(estimateStatusFlg.equals("1")){
      lConditionMan.add(new SITableCondition("a","status","2",SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    }
    
    //取り置きメール送信フラグ
    if (SIUtil.isNotNull(this.sendMailCbo)) {
      lConditionMan.add(new SITableCondition("d", "keepmailflg", this.sendMailCbo, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    /*
    try{
      //キープ番号大小
      if(Integer.parseInt(keepCodeFrom)!=Integer.parseInt(keepCodeTo)){
        SICheckValid.checkValid(errors,"キープ番号From","キープ番号To",keepCodeFrom,keepCodeTo,SICheckDataConf.SICHECK_DATA_VALUE_LESS_TYPE);
      }
      //見積番号大小
      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;
    }
  }
  
  // EDBTG003-00 mng-paku mod start
  /**
   * 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 sqlBuf = new StringBuffer();
    StringBuffer countBuf = new StringBuffer();
    StringBuffer bodyBuf = new StringBuffer();
    //出力項目
    sqlBuf.append("SELECT DISTINCT a.estimatecode,a.keepnumber,a.initdatetime ");
    sqlBuf.append(",coalesce(a.email::text,''::text) AS email ");
    sqlBuf.append(",coalesce(a.custname::text,''::text) AS custname ");
    sqlBuf.append(",coalesce(a.memberlevelcode::numeric,0::numeric) AS memberlevelcode ");
    sqlBuf.append(",(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 ");
    sqlBuf.append(",status,d.keepmailflg,d.keepmaildatetime,coalesce(e.receivableflg,0) AS receivableflg ");
    countBuf.append("SELECT COUNT(DISTINCT a.estimatecode) "); 
    bodyBuf.append("FROM estimatetbl a LEFT OUTER JOIN custtbl e ON a.custcode = e.custcode ");
    bodyBuf.append(",(SELECT keepnumber FROM keepdetailtbl) c ");
    bodyBuf.append(",(SELECT keepnumber AS keepnum,keepmailflg,keepmaildatetime FROM keeptbl) d ");
    bodyBuf.append(",(SELECT estimatecode,companyname AS companyname_d,tel FROM estimatedeliverytbl WHERE deliverycode=1) AS f ");
    bodyBuf.append("WHERE a.keepnumber = c.keepnumber ");
    bodyBuf.append("AND a.keepnumber = d.keepnum ");
    bodyBuf.append("AND a.estimatecode = f.estimatecode ");
    //見積作成日
    if(estimateDateFlg.equals("0")){
      bodyBuf.append("AND initdatetime BETWEEN (now()-interval '7 days') AND now() ");
    }else if(estimateDateFlg.equals("1")){
      bodyBuf.append("AND initdatetime BETWEEN (now()-interval '30 days') AND now() ");
    }else if(estimateDateFlg.equals("2")){
      bodyBuf.append("AND initdatetime < (now()-interval '30 days') ");
    }
    bodyBuf.append(conditionSQL);
    
    if(conditionSQL.equals("")&&estimateStatusFlg=="0") bodyBuf.append("AND status='1' ");
    if(sort==null || sort.equals("")) sort="initDateTime";
    
    sqlBuf.append(bodyBuf);
    countBuf.append(bodyBuf);
    
    sqlBuf.append("ORDER BY ").append(sort);
    if(firstOder.equals("0")){
      sqlBuf.append(" DESC");
      if(sort.equals("initDateTime")) sqlBuf.append(",estimatecode DESC");
    }else{
      sqlBuf.append(" ASC");
    }
    //実行
    
    try {
      String rowCnt = SIDBUtil.getFirstData(dbConnection, countBuf.toString());
      if (rowCnt.equals("") || rowCnt == null) {
        rowCnt = "0";
      }
      int lRecordCount = Integer.parseInt(rowCnt);// レコード数の取得
      
      int lPageSize = this.getPageSize();// ページサイズ
      int lPageNumber = Integer.parseInt(this.getPageNumberTxt_list());// ページ番号
      
      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);
      
      statement = dbConnection.createStatement();
      sqlBuf.append(" LIMIT ").append(lPageSize);
      if(lFromInx>0) sqlBuf.append(" OFFSET ").append(lFromInx);
      log.debug("getCollection:lOrderBuf=" + sqlBuf.toString());
      resultSet = statement.executeQuery(sqlBuf.toString());
      
      for (int jj = 0; jj < lFromInx; jj++) estimateColl.add(null);
      int lIndex = 0;
      
      // レコードのセットの作成
      while (resultSet.next() && lIndex < lPageSize) {
        estimateList = new SIEstimateList();
        estimateList.setEstimateCode(resultSet.getString("estimatecode"));
        estimateList.setKeepNumber(resultSet.getString("keepnumber"));
        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"));
        }
        // EDBTG003-00 mng-paku add start
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT sum(price * amount) + SUM(CASE WHEN deliverycode='0' THEN deliveryfee * amount ELSE deliveryfee END) AS total ");
        sql.append("FROM estimatedetailtbl ");
        sql.append("WHERE estimatecode = " + SIDBUtil.SQL2Str(resultSet.getString("estimatecode"), " "));
        sql.append("GROUP BY estimatecode ");
        String total = SIDBUtil.getFirstData(dbConnection, sql.toString());
        if (SIUtil.isNotNull(total) && StringUtils.isNumeric(total)) {
          estimateList.setTotal(SIUtil.add(estimateList.getTotal(), total));
        }
        // EDBTG003-00 mng-paku add end
        estimateList.setStatus(resultSet.getString("status"));
        estimateList.setKeepMailFlg(resultSet.getString("keepmailflg"));
        estimateList.setKeepMailDateTime(SIDBUtil.getDateTime(resultSet.getTimestamp("keepmaildatetime")));
        estimateList.setReceivableFlg(resultSet.getString("receivableFlg"));
        estimateColl.add(estimateList);
        lIndex++;
      }
      for (int jj = lFromInx + lPageSize; jj < lRecordCount; jj++) estimateColl.add(null);
    } catch (Exception ex) {
      log.debug("exception sql = " + sqlBuf.toString());
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(resultSet, statement);
    }    
    return estimateColl;
  } 
  // EDBTG003-00 mng-paku mod end
}