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.SIKeepHeader;
import jp.co.sint.basic.SILogin;
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;

public class UIKeepListCond extends SIBasic {
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  // 支店名
  private String companyName = "";
  
  // 支店コード
  private String companyCode = "";
  
  // 対応支店
  private String companyNameCdo = "";
  
  // 担当者名
  private String chargeName = "";
  
  // 担当者コード
  private String chargeCode = "";
  
  // 担当者
  private String chargeNameCdo = "";
  
  // キープ番号
  private String keepCodeTxt = "";
  
  // キープ番号 from
  private String keepCodeFromTxt = "";
  
  // キープ番号 to
  private String keepCodeToTxt = "";
  
  // キープ状態
  private String keepStatus = "0";
  
  // キープ作成日
  private String keepDate = "0";
  
  // ページ番号
  private String pageNumberTxt_list = "1";
  
  //在庫コード
  private String individualCodeTxt="";
  
  //メール送信フラグ
  private String sendMailCbo = "";
  
  //引当支店
  private String branchCodeTxt = "";
  
  //保護フラグ
  private String protectedFlgTxt = "";
  
  // 入力値チェックの結果、検索可能かどうか？
  private boolean searchFlg = true;
  
  public UIKeepListCond() {
    keepStatus = "0";
    keepDate = "0";
    
    // 条件文の設定
    SITableConditionManager lConditionMan = new SITableConditionManager();
    SITableConditionManager lConditionMan2 = new SITableConditionManager();
    lConditionMan.add(new SITableCondition("", "status", "2", SIConfig.SICONDITION_TYPE_NOT_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    lConditionMan2.add(new SITableCondition("a1", "status", "2", SIConfig.SICONDITION_TYPE_NOT_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    lConditionMan.add(new SITableCondition(" AND keepdate BETWEEN (now()-INTERVAL '7 days') AND now() "));
    lConditionMan2.add(new SITableCondition(" AND a1.keepdate BETWEEN (now()-INTERVAL '7 days') AND now() "));
    
    this.conditionSQL = lConditionMan.getCondtionSQL();
    this.conditionCSVSQL = lConditionMan2.getCondtionSQL();
  }
  
  public UIKeepListCond(String userCode) {
    keepStatus = "0";
    keepDate = "0";
    
    // 条件文の設定
    SITableConditionManager lConditionMan = new SITableConditionManager();
    SITableConditionManager lConditionMan2 = new SITableConditionManager();
    lConditionMan.add(new SITableCondition("", "status", "2", SIConfig.SICONDITION_TYPE_NOT_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    lConditionMan2.add(new SITableCondition("a1", "status", "2", SIConfig.SICONDITION_TYPE_NOT_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    lConditionMan.add(new SITableCondition(" AND keepdate BETWEEN (now()-INTERVAL '7 days') AND now() "));
    lConditionMan2.add(new SITableCondition(" AND a1.keepdate BETWEEN (now()-INTERVAL '7 days') AND now() "));
    if (userCode.equals("00777")) {
      lConditionMan.add(new SITableCondition("", "branchcode", "7", SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      lConditionMan2.add(new SITableCondition("a1", "branchcode", "7", SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      lConditionMan.add(new SITableCondition("", "chargecode", "00777", SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      lConditionMan2.add(new SITableCondition("a1", "chargecode", "00777", SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    this.conditionSQL = lConditionMan.getCondtionSQL();
    this.conditionCSVSQL = lConditionMan2.getCondtionSQL();
  }
  
  // setter of ページ番号
  public void setPageNumberTxt(String lPageNumberTxt) {
    if (SIUtil.isNull(lPageNumberTxt))
      lPageNumberTxt = "1";
    this.pageNumberTxt_list = lPageNumberTxt;
  }
  
  // getter of ページ番号
  public String getPageNumberTxt_list() {
    return this.pageNumberTxt_list;
  }
  
  // SQL検索のばあいに、条件文
  private String conditionSQL = "";
  
  // CSV出力用
  private String conditionCSVSQL = "";
  
  // setter of 支店名
  public void setCompanyName(String lCompanyName) {
    if (SIUtil.isNull(lCompanyName))
      lCompanyName = "";
    this.companyCode = SIUtil.changeTo(lCompanyName.trim(), this.encode);
  }
  
  // setter of 支店コード
  public void setCompanyNameCode(String lCompanyNameCode) {
    if (SIUtil.isNull(lCompanyNameCode))
      lCompanyNameCode = "";
    this.companyName = SIUtil.changeTo(lCompanyNameCode.trim(), this.encode);
  }
  
  // setter of 対応支店
  public void setCompanyNameCdo(String lCompanyNameCdo) {
    if (SIUtil.isNull(lCompanyNameCdo))
      lCompanyNameCdo = "";
    this.companyNameCdo = SIUtil.changeTo(lCompanyNameCdo.trim(), this.encode);
  }
  
  // setter of 担当者名
  public void setChargeName(String lChargeName) {
    if (SIUtil.isNull(lChargeName))
      lChargeName = "";
    this.chargeCode = SIUtil.changeTo(lChargeName.trim(), this.encode);
  }
  
  // setter of 担当者コード
  public void setChargeCode(String lChargeCode) {
    if (SIUtil.isNull(lChargeCode))
      lChargeCode = "";
    this.chargeName = SIUtil.changeTo(lChargeCode.trim(), this.encode);
  }
  
  // setter of 担当者
  public void setChargeNameCdo(String lChargeNameCdo) {
    if (SIUtil.isNull(lChargeNameCdo))
      lChargeNameCdo = "";
    this.chargeCode = SIUtil.changeTo(lChargeNameCdo.trim(), this.encode);
  }
  
  // setter of キープ番号
  public void setKeepCodeTxt(String lKeepCodeTxt) {
    if (SIUtil.isNull(lKeepCodeTxt))
      lKeepCodeTxt = "";
    this.keepCodeTxt = SIUtil.changeTo(lKeepCodeTxt.trim(), this.encode);
  }
  
  // setter of キープ番号from
  public void setKeepCodeFromTxt(String lKeepCodeFromTxt) {
    if (SIUtil.isNull(lKeepCodeFromTxt))
      lKeepCodeFromTxt = "";
    this.keepCodeFromTxt = SIUtil.changeTo(lKeepCodeFromTxt.trim(), this.encode);
  }
  
  // setter of キープ番号to
  public void setKeepCodeToTxt(String lKeepCodeToTxt) {
    if (SIUtil.isNull(lKeepCodeToTxt))
      lKeepCodeToTxt = "";
    this.keepCodeToTxt = SIUtil.changeTo(lKeepCodeToTxt.trim(), this.encode);
  }
  
  // setter of キープ状態
  public void setKeepStatus(String lKeepStatus) {
    if (SIUtil.isNull(lKeepStatus))
      lKeepStatus = "0";
    this.keepStatus = SIUtil.changeTo(lKeepStatus.trim(), this.encode);
  }
  
  // setter of キープ作成日
  public void setKeepDate(String lKeepDate) {
    if (SIUtil.isNull(lKeepDate))
      lKeepDate = "0";
    this.keepDate = SIUtil.changeTo(lKeepDate.trim(), this.encode);
  }
  
  // setter of 在庫コード
  public void setIndividualCodeTxt(String lIndividualCodeTxt) {
    if (SIUtil.isNull(lIndividualCodeTxt)) lIndividualCodeTxt = "";
    this.individualCodeTxt = SIUtil.changeTo(lIndividualCodeTxt.trim(), this.encode);
  }
  
  // setter of メール送信フラグ
  public void setSendMailCbo(String lSendMailCbo) {
    if (SIUtil.isNull(lSendMailCbo)) lSendMailCbo = "";
    this.sendMailCbo = SIUtil.changeTo(lSendMailCbo.trim(), this.encode);
  }
  
  // setter of 引当支店
  public void setBranchCodeTxt(String lBranchCodeTxt) {
    if (SIUtil.isNull(lBranchCodeTxt)) lBranchCodeTxt = "";
    this.branchCodeTxt = SIUtil.changeTo(lBranchCodeTxt.trim(), this.encode);
  }
  
  // getter of 担当者
  public String getChargeName() {
    return this.chargeName;
  }
  
  // getter of 担当者コード
  public String getChargeCode() {
    return this.chargeCode;
  }
  
  // getter of 担当者
  public String getChargeNameCdo() {
    return this.chargeNameCdo;
  }
  
  // getter of キープ作成日
  public String getKeepDate() {
    return this.keepDate;
  }
  
  // getter of 支店名
  public String getCompanyName() {
    return this.companyName;
  }
  
  // getter of 支店コード
  public String getCompanyCode() {
    return this.companyCode;
  }
  
  // getter of 対応支店
  public String getCompanyNameCdo() {
    return this.companyNameCdo;
  }
  
  // getter of キープ番号
  public String getKeepCodeTxt() {
    return this.keepCodeTxt;
  }
  
  // getter of キープ番号From
  public String getKeepCodeFromTxt() {
    return this.keepCodeFromTxt;
  }
  
  // getter of キープ番号To
  public String getKeepCodeToTxt() {
    return this.keepCodeToTxt;
  }
  
  // getter of キープ状態
  public String getKeepStatus() {
    return this.keepStatus;
  }
  
  // getter of 在庫コード
  public String getIndividualCodeTxt() {
    return this.individualCodeTxt;
  }
  
  // getter of メール送信フラグ
  public String getSendMailCbo() {
    return this.sendMailCbo;
  }
  
  // getter of 引当支店
  public String getBranchCodeTxt() {
    return this.branchCodeTxt;
  }
  
  // setter of 検索用ＳＱＬ文
  public void setConditionSQL(String lConditionSQL) {
    if (SIUtil.isNull(lConditionSQL))
      lConditionSQL = "";
    this.conditionSQL = SIUtil.changeTo(lConditionSQL.trim(), this.encode);
  }
  
  // getter of 条件文
  public String getCondtionSQL() {
    return this.conditionSQL;
  }
  
  // setter of CSVSQL
  public void setConditionCSVSQL(String lConditionCSVSQL) {
    if (SIUtil.isNull(lConditionCSVSQL))
      lConditionCSVSQL = "";
    this.conditionCSVSQL = SIUtil.changeTo(lConditionCSVSQL.trim(), this.encode);
  }
  
  // getter of CSVSQL
  public String getCondtionCSVSQL() {
    return this.conditionCSVSQL;
  }
  
  public String getProtectedFlgTxt() {
    return protectedFlgTxt;
  }
  
  public void setProtectedFlgTxt(String protectedFlgTxt) {
    if (SIUtil.isNull(protectedFlgTxt)) protectedFlgTxt = "";
    this.protectedFlgTxt = protectedFlgTxt;
  }
  
  // チェックボックス番号
  private String[] checkbx = new String[0];
  
  // チェック
  public void setCheck(String[] lCheck) {
    if (lCheck == null)
      lCheck = new String[0];
    this.checkbx = lCheck;
  }
  
  // チェック getter
  public String[] getCheck() {
    return this.checkbx;
  }
  
  public void init(HttpServletRequest lRequest, SIURLParameter lUrlParam) {
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    super.init(lRequest, lUrlParam);
    
    this.setCheck(lRequest.getParameterValues("checkbx"));
    this.setIndividualCodeTxt((String) lUrlParam.getParam("individualCodeTxt"));//在庫コード
    this.setCompanyName((String) lUrlParam.getParam("companyCode"));// 支店コード
    this.setChargeName((String) lUrlParam.getParam("chargeCode"));// 担当者
    this.setKeepCodeFromTxt((String) lUrlParam.getParam("keepCodeFromTxt"));// キープ番号From
    this.setKeepCodeToTxt((String) lUrlParam.getParam("keepCodeToTxt"));// キープ番号To
    this.setKeepDate((String) lUrlParam.getParam("keepDate"));// キープ作成日
    this.setKeepStatus((String) lUrlParam.getParam("keepStatus")); // キープ状態
    this.setSendMailCbo((String) lUrlParam.getParam("sendMailCbo"));//メール送信フラグ
    this.setBranchCodeTxt((String) lUrlParam.getParam("branchCodeTxt"));//引当支店
    this.setProtectedFlgTxt((String) lUrlParam.getParam("protectedFlgTxt"));//保護フラグ
    pageNumberTxt_list=(String) lUrlParam.getParam("pageNumberTxt_list");
  }
  
  /**
   * <b>validate</b> 入力したデータをチェックして、同時にSQLの条件文を作成します。
   * 
   * @param lRequest クライアントからのリクエスト
   * @return なし
   * @throws なし
   */
  public void validate(HttpServletRequest lRequest) {
    SICustomErrors errors = new SICustomErrors();
    SITableConditionManager lConditionMan = new SITableConditionManager();
    SITableConditionManager lConditionMan2 = new SITableConditionManager();
    // 支店名
    if (!companyCode.equals("XX")) {
      if (SIUtil.isNotNull(this.companyCode) && SICheckValid.checkValid(errors, "支店名", this.companyCode, SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)) {
        lConditionMan.add(new SITableCondition("", "branchCode", this.companyCode, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        lConditionMan2.add(new SITableCondition("a1", "branchCode", this.companyCode, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      }
    }
    if (!chargeCode.equals("XX")) {
      // 担当者
      if (SIUtil.isNotNull(this.chargeCode) && SICheckValid.checkValid(errors, "担当者", this.chargeCode, SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)) {
        lConditionMan.add(new SITableCondition("", "chargeCode", this.chargeCode, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        lConditionMan2.add(new SITableCondition("a1", "chargeCode", this.chargeCode, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      }
    }
    // キープ番号
    if (SIUtil.isNotNull(this.keepCodeFromTxt) && SICheckValid.checkValid(errors, "キープ番号", this.keepCodeFromTxt, SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)) {
      lConditionMan.add(new SITableCondition("cc", "keepnumber", this.keepCodeFromTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      lConditionMan2.add(new SITableCondition("a1", "keepnumber", this.keepCodeFromTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    /*
    // キープ番号From
    if (SIUtil.isNotNull(this.keepCodeFromTxt) && SICheckValid.checkValid(errors, "キープ番号From", this.keepCodeFromTxt, SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)) {
      lConditionMan.add(new SITableCondition("cc", "keepnumber", this.keepCodeFromTxt, SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      lConditionMan2.add(new SITableCondition("a1", "keepnumber", this.keepCodeFromTxt, SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // キープ番号To
    if (SIUtil.isNotNull(this.keepCodeToTxt) && SICheckValid.checkValid(errors, "キープ番号To", this.keepCodeToTxt, SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)) {
      lConditionMan.add(new SITableCondition("cc", "keepnumber", this.keepCodeToTxt, SIConfig.SICONDITION_TYPE_LESS_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      lConditionMan2.add(new SITableCondition("a1", "keepnumber", this.keepCodeToTxt, SIConfig.SICONDITION_TYPE_LESS_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // キープ番号大小
    if (errors.isEmpty() && SIUtil.isNotNull(keepCodeFromTxt) && SIUtil.isNotNull(keepCodeToTxt) && !keepCodeFromTxt.equals(keepCodeToTxt)) {
      SICheckValid.checkValid(errors, "受注番号From", "受注番号To", keepCodeFromTxt, keepCodeToTxt, SICheckDataConf.SICHECK_DATA_VALUE_LESS_TYPE);
    }
    */
    // キープ状態
    if (SIUtil.isNotNull(keepStatus)) {
      if (keepStatus.equals("0")) {
        lConditionMan.add(new SITableCondition("", "status", "2", SIConfig.SICONDITION_TYPE_NOT_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        lConditionMan2.add(new SITableCondition("a1", "status", "2", SIConfig.SICONDITION_TYPE_NOT_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      } else if (keepStatus.equals("1")) {
        lConditionMan.add(new SITableCondition("", "status", "2", SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        lConditionMan2.add(new SITableCondition("a1", "status", "2", SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      }
    }
    
    // 在庫コード
    if (SIUtil.isNotNull(individualCodeTxt)&&SICheckValid.checkValid(errors, "在庫コード", this.individualCodeTxt, SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE)) {
      lConditionMan.add(new SITableCondition("ee", "individualCode", individualCodeTxt, SIConfig.SICONDITION_TYPE_FRONTLIKE, SIConfig.SICONDITION_TYPE_AND));
      lConditionMan2.add(new SITableCondition("b1", "individualCode", individualCodeTxt, SIConfig.SICONDITION_TYPE_FRONTLIKE, SIConfig.SICONDITION_TYPE_AND));
    }
    
    //メール送信フラグ
    if (SIUtil.isNotNull(this.sendMailCbo)) {
      lConditionMan.add(new SITableCondition("", "keepmailflg", this.sendMailCbo, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      lConditionMan2.add(new SITableCondition("a1", "keepmailflg", this.sendMailCbo, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    //キープ作成日
    if (SIUtil.isNotNull(keepDate)){
      if (keepDate.equals("0")) {
        lConditionMan.add(new SITableCondition(" AND keepdate BETWEEN TO_CHAR(now() + '-7 days','yyyy-mm-dd')::date AND current_date "));
        lConditionMan2.add(new SITableCondition(" AND a1.keepdate BETWEEN TO_CHAR(now() + '-7 days','yyyy-mm-dd')::date AND current_date "));
      } else if (keepDate.equals("1")) {
        lConditionMan.add(new SITableCondition(" AND keepdate BETWEEN TO_CHAR(now() + '-21 days','yyyy-mm-dd')::date AND current_date "));
        lConditionMan2.add(new SITableCondition(" AND a1.keepdate BETWEEN TO_CHAR(now() + '-21 days','yyyy-mm-dd')::date AND current_date "));
      } else if (keepDate.equals("2")) {
        lConditionMan.add(new SITableCondition(" AND keepdate < TO_CHAR(now() + '-21days','yyyy-mm-dd')::date "));
        lConditionMan2.add(new SITableCondition(" AND a1.keepdate < TO_CHAR(now() + '-21 days','yyyy-mm-dd')::date "));
      } else if (keepDate.equals("4")) {
        lConditionMan.add(new SITableCondition(" AND CASE WHEN keepdate < TO_CHAR(now() + '-21days','yyyy-mm-dd')::date THEN true WHEN status=0 AND keepdate < TO_CHAR(now() + '-7days','yyyy-mm-dd')::date THEN true ELSE false END "));
        lConditionMan2.add(new SITableCondition(" AND CASE WHEN a1.keepdate < TO_CHAR(now() + '-21 days','yyyy-mm-dd')::date THEN true WHEN a1.status=0 AND a1.keepdate < TO_CHAR(now() + '-7days','yyyy-mm-dd')::date THEN true ELSE false END "));
        this.setProtectedFlgTxt("0");
        lConditionMan.add(new SITableCondition(" AND protectedFlg='0' "));
        lConditionMan2.add(new SITableCondition(" AND a1.protectedFlg='0' "));
      }
    }
    
    //引当支店
    if (SIUtil.isNotNull(branchCodeTxt)&&!branchCodeTxt.equals("XX")){
      lConditionMan.add(new SITableCondition(" AND cc.keepnumber IN (SELECT keepnumber FROM keepdetailtbl WHERE cmdtytype=0 AND branchcode="+branchCodeTxt+" ) "));
      lConditionMan2.add(new SITableCondition(" AND a1.keepnumber IN (SELECT keepnumber FROM keepdetailtbl WHERE cmdtytype=0 AND branchcode="+branchCodeTxt+" ) "));
    }
    
    // 保護フラグ
    if (SIUtil.isNotNull(protectedFlgTxt)) {
      lConditionMan.add(new SITableCondition("", "protectedFlg", protectedFlgTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      lConditionMan2.add(new SITableCondition("a1", "protectedFlg", protectedFlgTxt, 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());
      this.setConditionCSVSQL(lConditionMan2.getCondtionSQL());
    }
  }
  
  public Collection getCollection(Connection lConnection) throws SIDBAccessException {
    return getCollection(lConnection, new SILogin());
  }
  
  public Collection getCollection(Connection lConnection, SILogin lLogin) throws SIDBAccessException {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIKeepHeader lKeepOrder = new SIKeepHeader();
    StringBuffer lSqlBuf = new StringBuffer();
    Collection lKeepOrders = new ArrayList();
    // 検索条件にエラーがあると検索しない。
    if (!searchFlg) {
      return lKeepOrders;
    }
    
    // 基本のSQL
    lSqlBuf.append("SELECT DISTINCT cc.keepnumber,cc.branchname,dd.chargename,cc.keepdate,cc.status,cc.keepmailflg,cc.keepmaildatetime,cc.protectedflg ");
    lSqlBuf.append("FROM (KeepTbl aa left outer join (select branchcode as branch,branchname from branchtbl) bb on aa.branchcode=bb.branch) cc left outer join (select chargecode as charge,chargename from chargetbl) dd on cc.chargecode=dd.charge ");
    lSqlBuf.append(",(SELECT keepnumber,individualcode FROM keepdetailtbl) ee ");
    lSqlBuf.append("WHERE cc.keepnumber=ee.keepnumber ");
    
    // 検索の条件
    lSqlBuf.append(getCondtionSQL());
    
    // 出力順
    lSqlBuf.append(" ORDER BY keepnumber DESC");
    
    log.debug("lSqlBuf=" + lSqlBuf.toString());
    
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      
      // 商品レコードのセットの作成
      while (lResultSet.next()) {
        lKeepOrder = new SIKeepHeader();
        lKeepOrder.setKeepNumber(lResultSet.getString("keepnumber"));
        if (lResultSet.getString("branchname") == null) {
          lKeepOrder.setBranchCode("");
        } else {
          lKeepOrder.setBranchCode(lResultSet.getString("branchname"));
        }
        if (lResultSet.getString("chargename") == null) {
          lKeepOrder.setChargeCode("");
        } else {
          lKeepOrder.setChargeCode(lResultSet.getString("chargename"));
        }
        lKeepOrder.setStatus(lResultSet.getString("status"));
        lKeepOrder.setKeepdate(lResultSet.getString("keepdate"));
        lKeepOrder.setKeepMailFlg(lResultSet.getString("keepmailflg"));
        lKeepOrder.setKeepMailDateTime(SIDBUtil.getDateTime(lResultSet.getTimestamp("keepmaildatetime")));
        lKeepOrder.setProtectedFlg(lResultSet.getString("protectedFlg"));
        lKeepOrders.add(lKeepOrder);
      }
    } catch (Exception ex) {
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lKeepOrders;
  }
  
  /**
   * <b>initDelete</b> 入力したデータを基づいて、このbeansを設定します。
   * 
   * @param request クライアントからリクエスト
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  public void initDelete(HttpServletRequest lRequest, SIURLParameter lUrlParam) {
    super.init(lRequest, lUrlParam);
    this.setCheck(lRequest.getParameterValues("checkbx"));
  }
  
  public boolean validateDelete(HttpServletRequest lRequest){
    SICustomErrors errors=new SICustomErrors();
    
    //チェックボックスのチェック
    if(this.getCheck()==null || this.getCheck().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();
  }
}