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

import jp.co.sint.basic.SIBasic;
import jp.co.sint.basic.SIStoreStockList;
import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIDBMultiConf;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.database.SIDateTimeType;
import jp.co.sint.database.SITableCondition;
import jp.co.sint.database.SITableConditionManager;
import jp.co.sint.tools.SICheckDataConf;
import jp.co.sint.tools.SICheckUtil;
import jp.co.sint.tools.SICheckValid;
import jp.co.sint.tools.SICustomError;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIDateTime;
import jp.co.sint.tools.SIURLParameter;
import jp.co.sint.tools.SIUtil;

/**
 * @version $Id : SIBGPdfTool.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 UIStoreStockListCond extends SIBasic {
  
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  // 支店コード
  private String branchCode = "";
  
  // 商品名
  private String cmdtyName = "";
  
  // 親コード
  private String cmdtyCode = "";
  
  // 在庫コード
  private String individualCode = "";
  
  // 中古・新品フラグ
  private String usedNewFlg = "";
  
  // 在庫管理フラグ
  private String instockFlg = "2";
  
  // カテゴリーコード
  private String ctgryCode = "";
  
  // フロント表示フラグ
  private String frontDispFlg = "2";
  
  // 入荷日From（年）
  private String arrivalDateFromYear = "";
  
  // 入荷日From（月）
  private String arrivalDateFromMonth = "";
  
  // 入荷日From（日）
  private String arrivalDateFromDay = "";
  
  // 入荷日To（年）
  private String arrivalDateToYear = "";
  
  // 入荷日To（月）
  private String arrivalDateToMonth = "";
  
  // 入荷日To（日）
  private String arrivalDateToDay = "";
  
  // ソート条件
  private String sort = "arrivaldate";
  
  // SQLの条件文
  private String conditionSQL = "";
  
  // 昇順、降順
  private String firstOrder = "0";
  
  // ブランド
  private String maker = "";
  
  // JANコード
  private String janCodeIndividual = "";
  
  // 死活フラグ
  private String disableFlg = "0";
  
  // 預け先
  private String storageCode = "";
  
  // 検索フラグ
  private boolean searchFlg = false;
  
  // EDBTG003-00 mng-paku add start
  // 商品種別フラグ
  private String cmdtySetTypeFlg = "";
  // EDBTG003-00 mng-paku add end
  
  /**
   * UIStoreStockListCond コンストラクタ
   * 
   * @param なし
   * @return なし
   * @throws なし
   */
  public UIStoreStockListCond() {
    
    this.searchFlg = false;
  }
  
  /*
   * getter of sort.
   */
  public String getSort() {
    return sort;
  }
  
  /*
   * setter of sort
   */
  public void setSort(String sort) {
    if (SIUtil.isNull(sort)) sort = "arrivaldate";
    this.sort = sort;
  }
  
  /*
   * getter of arrivalDateFromDay.
   */
  public String getArrivalDateFromDay() {
    return arrivalDateFromDay;
  }
  
  /*
   * setter of arrivalDateFromDay
   */
  public void setArrivalDateFromDay(String arrivalDateFromDay) {
    this.arrivalDateFromDay = arrivalDateFromDay;
  }
  
  /*
   * getter of arrivalDateFromMonth.
   */
  public String getArrivalDateFromMonth() {
    return arrivalDateFromMonth;
  }
  
  /*
   * setter of arrivalDateFromMonth
   */
  public void setArrivalDateFromMonth(String arrivalDateFromMonth) {
    this.arrivalDateFromMonth = arrivalDateFromMonth;
  }
  
  /*
   * getter of arrivalDateFromYear.
   */
  public String getArrivalDateFromYear() {
    return arrivalDateFromYear;
  }
  
  /*
   * setter of arrivalDateFromYear
   */
  public void setArrivalDateFromYear(String arrivalDateFromYear) {
    this.arrivalDateFromYear = arrivalDateFromYear;
  }
  
  /*
   * getter of arrivalDateToDay.
   */
  public String getArrivalDateToDay() {
    return arrivalDateToDay;
  }
  
  /*
   * setter of arrivalDateToDay
   */
  public void setArrivalDateToDay(String arrivalDateToDay) {
    this.arrivalDateToDay = arrivalDateToDay;
  }
  
  /*
   * getter of arrivalDateToMonth.
   */
  public String getArrivalDateToMonth() {
    return arrivalDateToMonth;
  }
  
  /*
   * setter of arrivalDateToMonth
   */
  public void setArrivalDateToMonth(String arrivalDateToMonth) {
    this.arrivalDateToMonth = arrivalDateToMonth;
  }
  
  /*
   * getter of arrivalDateToYear.
   */
  public String getArrivalDateToYear() {
    return arrivalDateToYear;
  }
  
  /*
   * setter of arrivalDateToYear
   */
  public void setArrivalDateToYear(String arrivalDateToYear) {
    this.arrivalDateToYear = arrivalDateToYear;
  }
  
  /*
   * getter of brachCode.
   */
  public String getBranchCode() {
    return branchCode;
  }
  
  /*
   * setter of brachCode
   */
  public void setBranchCode(String branchCode) {
    this.branchCode = branchCode;
  }
  
  /*
   * getter of cmdtyCode.
   */
  public String getCmdtyCode() {
    return cmdtyCode;
  }
  
  /*
   * setter of cmdtyCode
   */
  public void setCmdtyCode(String cmdtyCode) {
    this.cmdtyCode = cmdtyCode;
  }
  
  /*
   * getter of cmdtyName.
   */
  public String getCmdtyName() {
    return cmdtyName;
  }
  
  /*
   * setter of cmdtyName
   */
  public void setCmdtyName(String cmdtyName) {
    this.cmdtyName = cmdtyName;
  }
  
  /*
   * getter of ctgryCode.
   */
  public String getCtgryCode() {
    return ctgryCode;
  }
  
  /*
   * setter of ctgryCode
   */
  public void setCtgryCode(String ctgryCode) {
    this.ctgryCode = ctgryCode;
  }
  
  /*
   * getter of flontDispFlg.
   */
  public String getFrontDispFlg() {
    return frontDispFlg;
  }
  
  /*
   * setter of flontDispFlg
   */
  public void setFrontDispFlg(String flontDispFlg) {
    this.frontDispFlg = flontDispFlg;
  }
  
  /*
   * getter of individualCode.
   */
  public String getIndividualCode() {
    return individualCode;
  }
  
  /*
   * setter of individualCode
   */
  public void setIndividualCode(String individualCode) {
    this.individualCode = individualCode;
  }
  
  /*
   * getter of instockFlg.
   */
  public String getInstockFlg() {
    return instockFlg;
  }
  
  /*
   * setter of instockFlg
   */
  public void setInstockFlg(String instockFlg) {
    this.instockFlg = instockFlg;
  }
  
  /*
   * getter of usedNewFlg.
   */
  public String getUsedNewFlg() {
    return usedNewFlg;
  }
  
  /*
   * setter of usedNewFlg
   */
  public void setUsedNewFlg(String usedNewFlg) {
    this.usedNewFlg = usedNewFlg;
  }
  
  /*
   * getter of firstOrder.
   */
  public String getFirstOrder() {
    return firstOrder;
  }
  
  /*
   * setter of firstOrder
   */
  public void setFirstOrder(String firstOrder) {
    this.firstOrder = firstOrder;
  }
  
  /*
   * getter of maker.
   */
  public String getMaker() {
    return maker;
  }
  
  /*
   * setter of maker
   */
  public void setMaker(String maker) {
    this.maker = maker;
  }
  
  /**
   * @return
   */
  public String getArrivalDateFrom() {
    return SIDateTime.getDate(getArrivalDateFromYear(), getArrivalDateFromMonth(), getArrivalDateFromDay());
  }
  
  /**
   * @return
   */
  public String getArrivalDateTo() {
    return SIDateTime.getDate(getArrivalDateToYear(), getArrivalDateToMonth(), getArrivalDateToDay());
  }
  
  //getter of janCodeIndividual
  public String getJanCodeIndividual() {
    return this.janCodeIndividual;
  }
  
  //setter of janCodeIndividual
  public void setJanCodeIndividual(String janCodeIndividual) {
    if (SIUtil.isNull(janCodeIndividual)) janCodeIndividual = "";
    this.janCodeIndividual = SIUtil.changeTo(janCodeIndividual.trim(), this.encode);
  }
  
  //getter of disableFlg
  public String getDisableFlg() {
    return this.disableFlg;
  }
  
  //setter of disableFlg
  public void setDisableFlg(String disableFlg) {
    if (SIUtil.isNull(disableFlg)) disableFlg = "";
    this.disableFlg = SIUtil.changeTo(disableFlg.trim(), this.encode);
  }
  
  // EDBTG003-00 mng-paku add start
  /**
   * @return the cmdtySetTypeFlg
   */
  public String getCmdtySetTypeFlg() {
    return cmdtySetTypeFlg;
  }
  
  /**
   * @param cmdtySetTypeFlg the cmdtySetTypeFlg to set
   */
  public void setCmdtySetTypeFlg(String cmdtySetTypeFlg) {
    this.cmdtySetTypeFlg = cmdtySetTypeFlg;
  }
  // EDBTG003-00 mng-paku add end
  
  public String getStorageCode() {
    return storageCode;
  }
  
  public void setStorageCode(String storageCode) {
    if (SIUtil.isNull(storageCode)) storageCode = "";
    this.storageCode = storageCode;
  }
  
  public void init(HttpServletRequest lRequest, SIURLParameter lUrlParam) {
    super.init(lRequest, lUrlParam);
    this.setBranchCode((String) lUrlParam.getParam("branchCode"));
    this.setCmdtyName((String) lUrlParam.getParam("cmdtyName"));
    this.setCmdtyCode((String) lUrlParam.getParam("cmdtyCode"));
    this.setIndividualCode((String) lUrlParam.getParam("individualCode"));
    this.setUsedNewFlg((String) lUrlParam.getParam("usedNewFlg"));
    this.setInstockFlg((String) lUrlParam.getParam("instockFlg"));
    this.setCtgryCode((String) lUrlParam.getParam("ctgryCode"));
    this.setFrontDispFlg((String) lUrlParam.getParam("frontDispFlg"));
    this.setArrivalDateFromYear((String) lUrlParam.getParam("arrivalDateFromYear"));
    this.setArrivalDateFromMonth((String) lUrlParam.getParam("arrivalDateFromMonth"));
    this.setArrivalDateFromDay((String) lUrlParam.getParam("arrivalDateFromDay"));
    this.setArrivalDateToYear((String) lUrlParam.getParam("arrivalDateToYear"));
    this.setArrivalDateToMonth((String) lUrlParam.getParam("arrivalDateToMonth"));
    this.setArrivalDateToDay((String) lUrlParam.getParam("arrivalDateToDay"));
    this.setSort((String) lUrlParam.getParam("sort"));
    this.setFirstOrder((String) lUrlParam.getParam("firstOrderTxt"));
    this.setMaker((String) lUrlParam.getParam("maker"));
    this.setJanCodeIndividual((String) lUrlParam.getParam("janCodeIndividual"));
    this.setDisableFlg((String) lUrlParam.getParam("disableFlg"));
    if(SIUtil.isNotNull(this.branchCode)&&!this.branchCode.equals("XX")){
      this.setInstockFlg("1");
    }
    // EDBTG003-00 mng-paku add start
    this.setCmdtySetTypeFlg((String) lUrlParam.getParam("cmdtySetTypeFlg"));
    // EDBTG003-00 mng-paku add end
    this.setStorageCode((String) lUrlParam.getParam("storageCode"));
  }
  
  /**
   * <b>validate</b> 入力したデータをチェックして、同時にSQLの条件文を作成します。
   * 
   * @param lRequest クライアントからのリクエスト
   */
  public void validate(HttpServletRequest lRequest) {
    SICustomErrors errors = new SICustomErrors();
    SITableConditionManager lConditionMan = new SITableConditionManager();
    
    // 支店名
    if (SIUtil.isNotNull(branchCode) && !branchCode.equals("XX")) {
      lConditionMan.add(new SITableCondition("c", "branchCode", branchCode, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    // 商品名
    if (SIUtil.isNotNull(cmdtyName)) {
      StringBuffer cmdtyNameSql = new StringBuffer();
      String[] codes = cmdtyName.trim().split(" |　");
      for (int i = 0; i < codes.length; i++) {
        if (SIUtil.isNotNull(codes[i])) {
          if (SIUtil.isNotNull(cmdtyNameSql.toString())) cmdtyNameSql.append("OR");
          cmdtyNameSql.append(" a.cmdtyName LIKE '%").append(SIDBUtil.SQL2Like(codes[i])).append("%' ");
        }
      }
      if (SIUtil.isNotNull(cmdtyNameSql.toString())) {
        lConditionMan.add(new SITableCondition(" AND (" + cmdtyNameSql.toString() + ") "));
      }
    }
    // 親コード
    if (SIUtil.isNotNull(cmdtyCode) && SICheckValid.checkValid(errors, "親コード", cmdtyCode, SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE)
        && SICheckValid.checkValid(errors, "親コード", cmdtyCode, SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 13)) {
      lConditionMan.add(new SITableCondition("a", "cmdtyCode", cmdtyCode, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    // 在庫コード
    if (SIUtil.isNotNull(individualCode)) {
      StringBuffer individualSql = new StringBuffer();
      String[] codes = individualCode.trim().split(" |　");
      for (int i = 0; i < codes.length; i++) {
        if (!SICheckUtil.isAlphaDigit(codes[i])) {
          errors.addError(new SICustomError("input.data.alphadigit", "在庫コード"));
          break;
        }
        if (SIUtil.isNotNull(codes[i])) {
          if (SIUtil.isNotNull(individualSql.toString())) individualSql.append("OR");
          individualSql.append(" b.individualcode LIKE '").append(SIDBUtil.SQL2Like(codes[i])).append("%' ");
        }
      }
      if (SIUtil.isNotNull(individualSql.toString())) {
        lConditionMan.add(new SITableCondition(" AND (" + individualSql.toString() + ") "));
      }
    }
    
    // 新品中古フラグ
    if (SIUtil.isNotNull(usedNewFlg)) {
      lConditionMan.add(new SITableCondition("b", "usedNewFlg", usedNewFlg, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    // 在庫管理
    if (!"2".equals(instockFlg)) {
      lConditionMan.add(new SITableCondition("b", "amountflg", instockFlg, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    // EC表示
    if (!"2".equals(frontDispFlg)) {
      lConditionMan.add(new SITableCondition("b", "frontDispFlg", frontDispFlg, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    if (maker != null) {
      lConditionMan.add(new SITableCondition("a", "makercode", maker, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    // JANコード
    if (SIUtil.isNotNull(janCodeIndividual) && SICheckValid.checkValid(errors, "JANコード", janCodeIndividual, SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE)
        && SICheckValid.checkValid(errors, "JANコード", janCodeIndividual, SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 16)) {
      lConditionMan.add(new SITableCondition("b", "janCodeIndividual", janCodeIndividual, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // 死活フラグ
    if (SIUtil.isNotNull(disableFlg)) {
      lConditionMan.add(new SITableCondition("a", "disableFlg", disableFlg, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // 入荷日(From)
    if (SIUtil.isNotNull(getArrivalDateFrom()) && SICheckValid.checkValid(errors, "入荷日From", getArrivalDateFrom(), SICheckDataConf.SICHECK_DATA_DATE_TYPE)) {
      if (SIDBMultiConf.SIDB_CURRENT_INX == SIDBMultiConf.SIDB_POSTGRESQL_INX) {
        lConditionMan.add(new SITableCondition("b", "arrivaldate", getArrivalDateFrom(), SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      } else {
        lConditionMan
            .add(new SITableCondition("b", "arrivaldate", new SIDateTimeType(getArrivalDateFrom()), SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      }
    }
    
    if(SIUtil.isNotNull(ctgryCode)){
      lConditionMan.add(new SITableCondition(" AND a.cmdtycode IN (SELECT cmdtycode FROM cmdtyctgrymtbl WHERE ctgrycode ="+SIDBUtil.SQL2Str(this.ctgryCode)+") "));
    }
    
    // 入荷日(To)
    if (SIUtil.isNotNull(getArrivalDateTo()) && SICheckValid.checkValid(errors, "入荷日To", getArrivalDateTo(), SICheckDataConf.SICHECK_DATA_DATE_TYPE)) {
      if (SIDBMultiConf.SIDB_CURRENT_INX == SIDBMultiConf.SIDB_POSTGRESQL_INX) {
        lConditionMan.add(new SITableCondition("b", "arrivaldate", getArrivalDateTo() + " 23:59:59", SIConfig.SICONDITION_TYPE_LESS_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      } else {
        lConditionMan.add(new SITableCondition("b", "arrivaldate", new SIDateTimeType(getArrivalDateTo() + " 23:59:59"), SIConfig.SICONDITION_TYPE_LESS_EQUAL,
            SIConfig.SICONDITION_TYPE_AND));
      }
    }
    
    // EDBTG003-00 mng-paku add start
    // 商品種別
    if (SIUtil.isNotNull(cmdtySetTypeFlg)) {
      if (SIConfig.SET_DETAIL_FLG_NORMAL.equals(cmdtySetTypeFlg)) {
        lConditionMan.add(new SITableCondition("a", "cmdtycompositionflg", SIConfig.SET_DETAIL_FLG_NORMAL, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      } else {
        lConditionMan.add(new SITableCondition("a", "cmdtycompositionflg", SIConfig.SET_DETAIL_FLG_NORMAL, SIConfig.SICONDITION_TYPE_NOT_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      }
    }
    // EDBTG003-00 mng-paku add end
    
    // 入荷日大小
    try {
      if (!SICheckUtil.dateEqual(this.getArrivalDateFrom(), this.getArrivalDateTo())) {
        SICheckValid.checkValid(errors, "受注日From", "受注日To", this.getArrivalDateFrom(), this.getArrivalDateTo(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
      }
    } catch (Exception e) {}
    
    if(SIUtil.isNotNull(this.getStorageCode())){
      lConditionMan.add(new SITableCondition("a", "storagecode", this.getStorageCode(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    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();
    }
    
    this.searchFlg = true;
  }
  
  /**
   * getCollection
   * 
   * @param Connection
   * @return Collection
   * @throws SIDBAccessException
   */
  public Collection getCollection(Connection dbConnection) throws SIDBAccessException {
    Collection storeStockColl = new ArrayList();
    if (!searchFlg) return storeStockColl;
    
    Statement statement = null;
    ResultSet resultSet = null;
    SIStoreStockList storeStockList = new SIStoreStockList();
    StringBuffer mainSql = new StringBuffer();
    StringBuffer countSql = new StringBuffer();
    StringBuffer commonSql = new StringBuffer();
    
    // 出力項目
    mainSql.append("SELECT a.cmdtycode,b.individualcode,f.makername,c.branchcode,c.amount,d.branchname,b.frontdispflg ");
    mainSql.append(",CASE b.amountflg WHEN 0 THEN COALESCE(b.purchaseprice2,0) ELSE COALESCE(b.purchaseprice,0) END AS purchaseprice ");
    mainSql.append(",COALESCE(b.processingexpence,0) AS processingexpence,b.bgprice,b.amountflg ");
    // EDBTG003-00 elecs-matsushima add start
    mainSql.append(",a.shopcode, a.cmdtycompositionflg ");
    // EDBTG003-00 elecs-matsushima add end
    
    countSql.append("SELECT count(b.individualCode) ");
    
    commonSql.append("FROM cmdtymtbl a,individualtbl b,storestocktbl c,branchtbl d ,makertbl f ");
    commonSql.append("WHERE b.cmdtycode = a.cmdtycode ");
    commonSql.append("AND b.cmdtycode = c.cmdtycode ");
    commonSql.append("AND b.individualcode = c.individualcode ");
    commonSql.append("AND c.branchcode = d.branchcode ");
    commonSql.append("AND a.makercode = f.makercode ");
    commonSql.append("AND c.branchcode NOT IN ('16','19') ");
    commonSql.append("AND CASE WHEN b.amountflg = 0 AND c.branchcode ='999' THEN true WHEN b.amountflg = '1' AND c.amount > 0 THEN true ELSE false END ");
    //commonSql.append("AND CASE WHEN b.individualcode LIKE 'HG%' THEN false WHEN b.individualcode LIKE 'AF%' THEN false WHEN b.individualcode LIKE 'OT%' THEN false ELSE true END ");
    commonSql.append(conditionSQL);
    
    countSql.append(commonSql);
    mainSql.append(commonSql);
    
    mainSql.append(" ORDER BY ").append(this.getSort());
    if (firstOrder.equals("0")) {
      mainSql.append(" DESC");
    } else {
      mainSql.append(" ASC");
    }
    // 実行
    try {
      int lRecordCount = Integer.parseInt(SIDBUtil.getFirstData(dbConnection, countSql.toString()));// レコード数の取得
      
      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);
      
      log.debug("getCollection:lCmdtySqlBuf=" + mainSql.toString());
      statement = dbConnection.createStatement();
      mainSql.append(" LIMIT ").append(lPageSize);
      if(lFromInx>0) mainSql.append(" OFFSET ").append(lFromInx);
      resultSet = statement.executeQuery(mainSql.toString());
      
      
      for (int jj = 0; jj < lFromInx; jj++) storeStockColl.add(null);
      int lIndex = 0;
      
      // 商品レコードのセットの作成
      while (resultSet.next()) {
        storeStockList = new SIStoreStockList();
        storeStockList.setCmdtyCode(resultSet.getString("cmdtycode"));
        storeStockList.setIndividualCode(resultSet.getString("individualcode"));
        storeStockList.setAmount(resultSet.getString("amount"));
        storeStockList.setBranch(resultSet.getString("branchname"));
        storeStockList.setBranchCode(resultSet.getString("branchcode"));
        storeStockList.setFrontDispFlg(resultSet.getString("frontdispflg"));
        storeStockList.setAmountflg(resultSet.getString("amountflg"));
        storeStockList.setPurchasePrice(resultSet.getString("purchaseprice"));
        storeStockList.setProcessiongExpence(resultSet.getString("processingexpence"));
        storeStockList.setBgPrice(resultSet.getString("bgprice"));
        storeStockList.setMaker(resultSet.getString("makername"));
        // EDBTG003-00 elecs-matsushima add start
        storeStockList.setCmdtyCompositionFlg(resultSet.getString("cmdtycompositionflg"));
        storeStockList.setShopCode(resultSet.getString("shopcode"));
        // EDBTG003-00 elecs-matsushima add end
        storeStockColl.add(storeStockList);
        lIndex++;
      }
      for (int jj = lFromInx + lPageSize; jj < lRecordCount; jj++) storeStockColl.add(null);
      
    } catch (Exception ex) {
      log.debug("exception sql = " + mainSql.toString());
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return storeStockColl;
  }
  
  public Collection getFreeKeepCollection(Connection dbConnection) throws SIDBAccessException {
    Collection storeStockColl = new ArrayList();
    if (!searchFlg) return storeStockColl;
    
    Statement statement = null;
    ResultSet resultSet = null;
    SIStoreStockList storeStockList = new SIStoreStockList();
    StringBuffer mainSql = new StringBuffer();
    StringBuffer countSql = new StringBuffer();
    StringBuffer commonSql = new StringBuffer();
    
    // 出力項目
    mainSql.append("SELECT a.cmdtycode,b.individualcode,f.makername,c.branchcode,c.amount,d.branchname,b.frontdispflg ");
    mainSql.append(",CASE b.amountflg WHEN 0 THEN COALESCE(b.purchaseprice2,0) ELSE COALESCE(b.purchaseprice,0) END AS purchaseprice ");
    mainSql.append(",COALESCE(b.processingexpence,0) AS processingexpence,b.bgprice,b.amountflg ");
    // EDBTG003-00 mng-paku add start
    mainSql.append(",a.cmdtycompositionflg, a.shopcode ");
    // EDBTG003-00 mng-paku add end
    
    countSql.append("SELECT count(b.individualCode) ");
    
    commonSql.append("FROM cmdtymtbl a,individualtbl b,storestocktbl c,branchtbl d ,makertbl f ");
    commonSql.append("WHERE b.cmdtycode = a.cmdtycode ");
    commonSql.append("AND b.cmdtycode = c.cmdtycode ");
    commonSql.append("AND b.individualcode = c.individualcode ");
    commonSql.append("AND c.branchcode = d.branchcode ");
    commonSql.append("AND a.makercode = f.makercode ");
    commonSql.append("AND c.branchcode = '999' ");
    commonSql.append(conditionSQL);
    
    countSql.append(commonSql);
    mainSql.append(commonSql);
    
    mainSql.append(" ORDER BY ").append(this.getSort());
    if (firstOrder.equals("0")) {
      mainSql.append(" DESC");
    } else {
      mainSql.append(" ASC");
    }
    // 実行
    try {
      int lRecordCount = Integer.parseInt(SIDBUtil.getFirstData(dbConnection, countSql.toString()));// レコード数の取得
      
      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);
      
      log.debug("getCollection:lCmdtySqlBuf=" + mainSql.toString());
      statement = dbConnection.createStatement();
      mainSql.append(" LIMIT ").append(lPageSize);
      if(lFromInx>0) mainSql.append(" OFFSET ").append(lFromInx);
      resultSet = statement.executeQuery(mainSql.toString());
      
      for (int jj = 0; jj < lFromInx; jj++) storeStockColl.add(null);
      int lIndex = 0;
      
      // 商品レコードのセットの作成
      while (resultSet.next()) {
        storeStockList = new SIStoreStockList();
        storeStockList.setCmdtyCode(resultSet.getString("cmdtycode"));
        storeStockList.setIndividualCode(resultSet.getString("individualcode"));
        storeStockList.setAmount(resultSet.getString("amount"));
        storeStockList.setBranch(resultSet.getString("branchname"));
        storeStockList.setBranchCode(resultSet.getString("branchcode"));
        storeStockList.setFrontDispFlg(resultSet.getString("frontdispflg"));
        storeStockList.setAmountflg(resultSet.getString("amountflg"));
        storeStockList.setPurchasePrice(resultSet.getString("purchaseprice"));
        storeStockList.setProcessiongExpence(resultSet.getString("processingexpence"));
        storeStockList.setBgPrice(resultSet.getString("bgprice"));
        storeStockList.setMaker(resultSet.getString("makername"));
        // EDBTG003-00 mng-paku add start
        storeStockList.setCmdtyCompositionFlg(resultSet.getString("cmdtycompositionflg"));
        storeStockList.setShopCode(resultSet.getString("shopcode"));
        // EDBTG003-00 mng-paku add end
        storeStockColl.add(storeStockList);
        lIndex++;
      }
      for (int jj = lFromInx + lPageSize; jj < lRecordCount; jj++) storeStockColl.add(null);
      
    } catch (Exception ex) {
      log.debug("exception sql = " + mainSql.toString());
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return storeStockColl;
  }
}