/**
 * Copyright (c) 2003-2006 System Integrator Corporation.
 *                 All Rights Reserved.
 */
package jp.co.sint.beans.mallmgr;

import javax.servlet.http.*;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;
import java.sql.*;
import org.apache.log4j.Category;
import jp.co.sint.database.*;
import jp.co.sint.basic.*;
import jp.co.sint.config.*;
import jp.co.sint.tools.*;

/**
 * @version $Id : UIStockControl.java,v 1.0 Exp $
 * @author      : Naotaka Ohsugi
 * <br>Description :入庫管理・出庫管理の一覧画面に対するbeansクラスを作成します。
 * <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>
 * Naotaka Ohsugi 2006/03/30  Original
 */

public class UIStockControl extends SIBasic {
  
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  // ショップコード
  private String shopCodeTxt = "";
  
  // ショップ名
  private String shopNameTxt = "";
  
  // 商品コード
  private String cmdtyCodeTxt = "";
  
  // 在庫コード
  private String individualCodeTxt = "";
  
  // 商品名
  private String cmdtyNameTxt = "";
  
  // 在庫数量From
  private String totalFromTxt = "";
  
  // 在庫数量To
  private String totalToTxt = "";
  
  // おすすめ
  private String adviceFlgRdo = "";
  
  // 配送種別
  private String deliveryTypeCodeRdo = "";
  
  // 予約販売
  private String rsrvEnableFlgRdo = "";
  
  // 在庫管理
  private String amountFlgRdo = "";
  
  // カテゴリコード
  private String ctgryCodeTxt = "";
  
  // ブランド名
  private String makerNameTxt = "";
  
  // 販売状況表示
  private String salesSituationDisp = "";
  
  // 未承認フラグ
  private String neverApprovalFlgRdo = "0";
  
  // 選択支店
  private String selectedBranch = "";
  
  // SQL検索のばあいに、条件文
  private String conditionSQL = "";
  
  // 最終入出庫日の年(To)
  private String initDateYearToCbo = "";
  
  // 最終入出庫日の月(To)
  private String initDateMonthToCbo = "";
  
  // 最終入出庫日の日(To)
  private String initDateDayToCbo = "";
  
  // 最終入出庫日の年(From)
  private String initDateYearFromCbo = "";
  
  // 最終入出庫日の月(From)
  private String initDateMonthFromCbo = "";
  
  // 最終入出庫日の日(From)
  private String initDateDayFromCbo = "";
  
  // 預け先
  private String storageCode = "";
  
  // 検索フラグ
  private boolean searchFlg = false;
  
  // JANコード
  private String janCodeIndividual = "";
  
  // 死活フラグ
  private String disableFlg = "0";
  
  /**
   * UIStockControl コンストラクタ
   * 
   * @param なし
   * @return なし
   * @throws なし
   */
  public UIStockControl() {
    /*
    SIDateTime lDateTime = new SIDateTime();
    
    //一週間前の日付をゲットします
    lDateTime.addDay(-14);
    String Month=Integer.toString(lDateTime.getMonth());
    String Day=Integer.toString(lDateTime.getDay());
    if(Month.length()==1) Month="0"+Month;
    if(Day.length()==1) Day="0"+Day;
    
    //最終入出庫日の年(From)
    initDateYearFromCbo=Integer.toString(lDateTime.getYear());
    //最終入出庫日の月(From)
    initDateMonthFromCbo=Month;
    //最終入出庫日の日(From)
    initDateDayFromCbo=Day;
    
    //条件文の設定
    SITableConditionManager lConditionMan=new SITableConditionManager();
    lConditionMan.add(new SITableCondition("d","initDate",getInitDateFrom(),SIConfig.SICONDITION_TYPE_GREATER_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    this.conditionSQL=lConditionMan.getCondtionSQL();
    */
    this.searchFlg = false;
  }
  
  /**
   * UIStockControl コンストラクタ
   * 
   * @param lRequest リクエスト
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  public UIStockControl(HttpServletRequest lRequest, SIURLParameter lUrlParam) {
    this.init(lRequest, lUrlParam);
  }
  
  public UIStockControl(String branchCode) {
    this.setDisableFlg("");
    this.setAmountFlgRdo("1");
    this.setNeverApprovalFlgRdo("1");
    this.setSelectedBranch(branchCode);
  }
  // setter of ショップコード
  public void setShopCodeTxt(String lShopCodeTxt) {
    if (SIUtil.isNull(lShopCodeTxt)) lShopCodeTxt = "";
    this.shopCodeTxt = SIUtil.changeTo(lShopCodeTxt.trim(), this.encode);
  }
  
  // setter of ショップ名
  public void setShopNameTxt(String lShopNameTxt) {
    if (SIUtil.isNull(lShopNameTxt)) lShopNameTxt = "";
    this.shopNameTxt = SIUtil.changeTo(lShopNameTxt.trim(), this.encode);
  }
  
  // setter of 商品コード
  public void setCmdtyCodeTxt(String lCmdtyCode) {
    if (SIUtil.isNull(lCmdtyCode)) lCmdtyCode = "";
    this.cmdtyCodeTxt = SIUtil.changeTo(lCmdtyCode.trim(), this.encode);
  }
  
  // setter of 在庫コード
  public void setIndividualCodeTxt(String individualCodeTxt) {
    if (SIUtil.isNull(individualCodeTxt)) individualCodeTxt = "";
    this.individualCodeTxt = SIUtil.changeTo(individualCodeTxt.trim(), this.encode);
  }
  
  // setter of 商品名
  public void setCmdtyNameTxt(String lCmdtyNameTxt) {
    if (SIUtil.isNull(lCmdtyNameTxt)) lCmdtyNameTxt = "";
    this.cmdtyNameTxt = SIUtil.changeTo(lCmdtyNameTxt.trim(), this.encode);
  }
  
  // setter of 在庫数量From
  public void setTotalFromTxt(String lTotalFromTxt) {
    if (SIUtil.isNull(lTotalFromTxt)) lTotalFromTxt = "";
    this.totalFromTxt = SIUtil.changeTo(lTotalFromTxt.trim(), this.encode);
  }
  
  // setter of 在庫数量To
  public void setTotalToTxt(String lTotalToTxt) {
    if (SIUtil.isNull(lTotalToTxt)) lTotalToTxt = "";
    this.totalToTxt = SIUtil.changeTo(lTotalToTxt.trim(), this.encode);
  }
  
  // setter of おすすめ
  public void setAdviceFlgRdo(String lAdviceFlgRdo) {
    if (SIUtil.isNull(lAdviceFlgRdo)) lAdviceFlgRdo = "";
    this.adviceFlgRdo = lAdviceFlgRdo;
  }
  
  // setter of 予約販売
  public void setRsrvEnableFlgRdo(String lRsrvEnableFlgRdo) {
    if (SIUtil.isNull(lRsrvEnableFlgRdo)) lRsrvEnableFlgRdo = "";
    this.rsrvEnableFlgRdo = lRsrvEnableFlgRdo;
  }
  
  // setter of 在庫管理
  public void setAmountFlgRdo(String lAmountFlgRdo) {
    if (SIUtil.isNull(lAmountFlgRdo)) lAmountFlgRdo = "";
    this.amountFlgRdo = lAmountFlgRdo;
  }
  
  // setter of 送料種別
  public void setDeliveryTypeCode(String deliveryTypeCode) {
    this.deliveryTypeCodeRdo = deliveryTypeCode;
  }
  
  // setter of カテゴリコード
  public void setCtgryCodeTxt(String lCtgryCode) {
    if (lCtgryCode == null) lCtgryCode = "";
    this.ctgryCodeTxt = lCtgryCode;
  }
  
  // setter of ブランド名
  public void setMakerNameTxt(String lMakerName) {
    if (lMakerName == null) lMakerName = "";
    this.makerNameTxt = lMakerName;
  }
  
  // setter of 販売状況表示
  public void setSalesSituationDisp(String lSalesSituationDisp) {
    if (lSalesSituationDisp == null) lSalesSituationDisp = "";
    this.salesSituationDisp = lSalesSituationDisp;
  }
  
  // setter of 未承認フラグ
  public void setNeverApprovalFlgRdo(String lNeverApprovalFlg) {
    if (lNeverApprovalFlg == null) lNeverApprovalFlg = "";
    this.neverApprovalFlgRdo = lNeverApprovalFlg;
  }
  
  // setter of 選択支店
  public void setSelectedBranch(String lSelectedBranch) {
    if (lSelectedBranch == null) lSelectedBranch = "";
    this.selectedBranch = lSelectedBranch;
  }
  
  // setter of 最終入出庫日の年(From)
  public void setInitDateYearFromCbo(String lInitDateYearFromCbo) {
    if (SIUtil.isNull(lInitDateYearFromCbo)) lInitDateYearFromCbo = "";
    this.initDateYearFromCbo = SIUtil.changeTo(lInitDateYearFromCbo.trim(), this.encode);
  }
  
  // setter of 最終入出庫日の月(From)
  public void setInitDateMonthFromCbo(String lInitDateMonthFromCbo) {
    if (SIUtil.isNull(lInitDateMonthFromCbo)) lInitDateMonthFromCbo = "";
    this.initDateMonthFromCbo = SIUtil.changeTo(lInitDateMonthFromCbo.trim(), this.encode);
  }
  
  // setter of 最終入出庫日の日(From)
  public void setInitDateDayFromCbo(String lInitDateDayFromCbo) {
    if (SIUtil.isNull(lInitDateDayFromCbo)) lInitDateDayFromCbo = "";
    this.initDateDayFromCbo = SIUtil.changeTo(lInitDateDayFromCbo.trim(), this.encode);
  }
  
  // setter of 最終入出庫日の年(To)
  public void setInitDateYearToCbo(String lInitDateYearToCbo) {
    if (SIUtil.isNull(lInitDateYearToCbo)) lInitDateYearToCbo = "";
    this.initDateYearToCbo = SIUtil.changeTo(lInitDateYearToCbo.trim(), this.encode);
  }
  
  // setter of 最終入出庫日の月(To)
  public void setInitDateMonthToCbo(String lInitDateMonthToCbo) {
    if (SIUtil.isNull(lInitDateMonthToCbo)) lInitDateMonthToCbo = "";
    this.initDateMonthToCbo = SIUtil.changeTo(lInitDateMonthToCbo.trim(), this.encode);
  }
  
  // setter of 最終入出庫日の日(To)
  public void setInitDateDayToCbo(String lInitDateDayToCbo) {
    if (SIUtil.isNull(lInitDateDayToCbo)) lInitDateDayToCbo = "";
    this.initDateDayToCbo = SIUtil.changeTo(lInitDateDayToCbo.trim(), this.encode);
  }
  
  // setter of JANコード
  public void setJanCodeIndividual(String lJanCodeIndividual) {
    if (SIUtil.isNull(lJanCodeIndividual)) lJanCodeIndividual = "";
    this.janCodeIndividual = SIUtil.changeTo(lJanCodeIndividual.trim(), this.encode);
  }
  
  // setter of 死活フラグ
  public void setDisableFlg(String lDisableFlg) {
    if (SIUtil.isNull(lDisableFlg)) lDisableFlg = "";
    this.disableFlg = SIUtil.changeTo(lDisableFlg.trim(), this.encode);
  }
  
  // setter of 預け先
  public void setStorageCode(String storageCode) {
    if (SIUtil.isNull(storageCode)) storageCode = "";
    this.storageCode = SIUtil.changeTo(storageCode.trim(), this.encode);
  }
  
  // getter of ショップコード
  public String getShopCodeTxt() {
    return this.shopCodeTxt;
  }
  
  // getter of ショップ名
  public String getShopNameTxt() {
    return this.shopNameTxt;
  }
  
  // getter of 商品コード
  public String getCmdtyCodeTxt() {
    return this.cmdtyCodeTxt;
  }
  
  // getter of 在庫コード
  public String getIndividualCodeTxt() {
    return individualCodeTxt;
  }
  
  // getter of 商品名
  public String getCmdtyNameTxt() {
    return this.cmdtyNameTxt;
  }
  
  // getter of 在庫数量From
  public String getTotalFromTxt() {
    return this.totalFromTxt;
  }
  
  // getter of 在庫数量To
  public String getTotalToTxt() {
    return this.totalToTxt;
  }
  
  // getter of 条件文
  public String getCondtionSQL() {
    return this.conditionSQL;
  }
  
  // getter of おすすめフラグ
  public String getAdviceFlgRdo() {
    return this.adviceFlgRdo;
  }
  
  // getter of 予約販売
  public String getRsrvEnableFlgRdo() {
    return this.rsrvEnableFlgRdo;
  }
  
  // getter of 在庫管理
  public String getAmountFlgRdo() {
    return this.amountFlgRdo;
  }
  
  // getter of 送料種別
  public String getDeliveryTypeCode() {
    return deliveryTypeCodeRdo;
  }
  
  // getter of カテゴリコード
  public String getCtgryCodeTxt() {
    return this.ctgryCodeTxt;
  }
  
  // getter of ブランド名
  public String getMakerNameTxt() {
    return this.makerNameTxt;
  }
  
  // getter of 販売状況表示
  public String getSalesSituationDisp() {
    return this.salesSituationDisp;
  }
  
  // getter of 未承認フラグ
  public String getNeverApprovalFlgRdo() {
    return this.neverApprovalFlgRdo;
  }
  
  // getter of 選択支店
  public String getSelectedBranch() {
    return this.selectedBranch;
  }
  
  // getter of 最終入出庫日の年(From)
  public String getInitDateYearFromCbo() {
    return this.initDateYearFromCbo;
  }
  
  // getter of 最終入出庫日の月(From)
  public String getInitDateMonthFromCbo() {
    return this.initDateMonthFromCbo;
  }
  
  // getter of 最終入出庫日の日(From)
  public String getInitDateDayFromCbo() {
    return this.initDateDayFromCbo;
  }
  
  // getter of 最終入出庫日(From)
  public String getInitDateFrom() {
    return SIDateTime.getDate(getInitDateYearFromCbo(), getInitDateMonthFromCbo(), getInitDateDayFromCbo());
  }
  
  // getter of 最終入出庫日の年(To)
  public String getInitDateYearToCbo() {
    return this.initDateYearToCbo;
  }
  
  // getter of 最終入出庫日の月(To)
  public String getInitDateMonthToCbo() {
    return this.initDateMonthToCbo;
  }
  
  // getter of 最終入出庫日の日(To)
  public String getInitDateDayToCbo() {
    return this.initDateDayToCbo;
  }
  
  // getter of 最終入出庫日(To)
  public String getInitDateTo() {
    return SIDateTime.getDate(getInitDateYearToCbo(), getInitDateMonthToCbo(), getInitDateDayToCbo());
  }
  
  // getter of JANコード
  public String getJanCodeIndividual(){
    return this.janCodeIndividual;
  }
  
  // getter of 死活フラグ
  public String getDisableFlg(){
    return this.disableFlg;
  }
  
  // getter of 預け先
  public String getStorageCode(){
    return this.storageCode;
  }
  
  // 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 lRequest, SIURLParameter lUrlParam) {
    SILogin lLogin = SIHTMLUtil.getLogin(lRequest);
    
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    super.init(lRequest, lUrlParam);
    this.setIndividualCodeTxt((String) lUrlParam.getParam("individualCodeTxt"));
    this.setCmdtyNameTxt((String) lUrlParam.getParam("cmdtyNameTxt"));
    this.setTotalFromTxt((String) lUrlParam.getParam("totalFromTxt"));
    this.setTotalToTxt((String) lUrlParam.getParam("totalToTxt"));
    this.setMakerNameTxt((String) lUrlParam.getParam("makerNameTxt"));
    this.setCtgryCodeTxt((String) lUrlParam.getParam("ctgryCodeTxt"));
    if (lLogin.isShop()) {
      this.setShopCodeTxt(lLogin.getMallShopCode());
    } else {
      this.setShopCodeTxt((String) lUrlParam.getParam("shopCodeTxt"));
    }
    this.setAdviceFlgRdo((String) lUrlParam.getParam("adviceFlgRdo"));
    this.setRsrvEnableFlgRdo((String) lUrlParam.getParam("rsrvEnableFlgRdo"));
    this.setAmountFlgRdo((String) lUrlParam.getParam("amountFlgRdo"));
    this.setDeliveryTypeCode((String) lUrlParam.getParam("deliveryTypeCodeRdo"));
    this.setSalesSituationDisp((String) lUrlParam.getParam("salesSituationDisp"));
    this.setNeverApprovalFlgRdo((String) lUrlParam.getParam("neverApprovalFlgRdo"));
    this.setInitDateYearFromCbo((String) lUrlParam.getParam("initDateYearFromCbo"));
    this.setInitDateMonthFromCbo((String) lUrlParam.getParam("initDateMonthFromCbo"));
    this.setInitDateDayFromCbo((String) lUrlParam.getParam("initDateDayFromCbo"));
    this.setInitDateYearToCbo((String) lUrlParam.getParam("initDateYearToCbo"));
    this.setInitDateMonthToCbo((String) lUrlParam.getParam("initDateMonthToCbo"));
    this.setInitDateDayToCbo((String) lUrlParam.getParam("initDateDayToCbo"));
    if (this.neverApprovalFlgRdo.equals("1")) {
      this.setSelectedBranch((String) lUrlParam.getParam("selectedBranch"));
    } else {
      this.setSelectedBranch((String) lUrlParam.getParam("hiddenBranch"));
    }
    this.setJanCodeIndividual((String) lUrlParam.getParam("janCodeIndividual"));
    this.setDisableFlg((String) lUrlParam.getParam("disableFlg"));
    this.setStorageCode((String) lUrlParam.getParam("storageCode"));
    this.searchFlg = true;
  }
  
  /**
   * <b>getStoreCollection</b> 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * 
   * @param lConnection データベースへの接続コネクション
   * @param SILogin ログイン者情報をセットしたBean
   * @return レコードのセット
   * @throws なし
   */
  public Collection getStoreCollection(Connection lConnection) throws SIDBAccessException {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SICmdty lCmdty = new SICmdty();
    StringBuffer lCountSqlBuf = new StringBuffer();// レコード数を求める
    StringBuffer lCmdtySqlBuf = new StringBuffer();// 商品レコード情報リストを求める
    
    Collection lCmdtys = new ArrayList();
    if (!searchFlg) return lCmdtys;
    
    // 基本のSQL
    lCmdtySqlBuf.append("SELECT a.cmdtyname,a.cmdtycode,a.individualcode,a.amount,b.colorname,c.makername,d.initdate,d.count ");
    lCmdtySqlBuf.append("FROM cmdtyunittbl AS a, colortbl AS b, makertbl AS c, ");
    lCmdtySqlBuf.append("(SELECT cmdtycode,individualcode,MAX(CASE WHEN delflg IN ('1','4') OR storenumber='1' THEN initdatetime ELSE NULL END) AS initdate,SUM(CASE WHEN delflg='0' THEN 1 ELSE 0 END) AS count ");
    lCmdtySqlBuf.append("FROM storehistorytbl GROUP BY cmdtycode,individualcode) AS d ");
    lCmdtySqlBuf.append(",cmdtymtbl AS e ");
    // lCmdtySqlBuf.append(",realstocktotalvw AS f ");
    
    lCmdtySqlBuf.append("WHERE a.individualcode=d.individualcode AND a.colorcode=b.colorcode AND a.makercode=c.makercode AND a.cmdtycode=d.cmdtycode ");
    lCmdtySqlBuf.append("AND a.cmdtycode=e.cmdtycode ");
    // lCmdtySqlBuf.append("WHERE a.individualcode=f.individualcode AND a.individualcode=d.individualcode ");
    // lCmdtySqlBuf.append("AND a.colorcode=b.colorcode AND a.makercode=c.makercode AND a.cmdtycode=f.cmdtycode AND a.cmdtycode=d.cmdtycode ");
    
    lCountSqlBuf.append("SELECT Count(*) FROM cmdtyunittbl AS a, colortbl AS b ,makertbl AS c, ");
    lCountSqlBuf.append("(SELECT cmdtycode,individualcode,MAX(CASE WHEN delflg IN ('1','4') OR storenumber='1' THEN initdatetime ELSE NULL END) AS initdate,SUM(CASE WHEN delflg='0' THEN 1 ELSE 0 END) AS count ");
    lCountSqlBuf.append("FROM storehistorytbl GROUP BY cmdtycode,individualcode) AS d ");
    lCountSqlBuf.append(",cmdtymtbl AS e ");
    lCountSqlBuf.append("WHERE a.individualcode=d.individualcode AND a.colorcode=b.colorcode AND a.makercode=c.makercode AND a.cmdtycode=d.cmdtycode ");
    lCountSqlBuf.append("AND a.cmdtycode=e.cmdtycode ");
    
    // 検索の条件
    lCmdtySqlBuf.append(this.conditionSQL);
    lCountSqlBuf.append(this.conditionSQL);
    
    // ソート
    lCmdtySqlBuf.append("ORDER BY d.initdate DESC,a.individualcode ASC ");
    
    // 実行
    try {
      String rowCnt = SIDBUtil.getFirstData(lConnection, lCountSqlBuf.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);
      
      log.debug("getCollection:lCmdtySqlBuf=" + lCmdtySqlBuf.toString());
      lStatement = lConnection.createStatement();
      lCmdtySqlBuf.append(" LIMIT ").append(lPageSize);
      if(lFromInx>0) lCmdtySqlBuf.append(" OFFSET ").append(lFromInx);
      lResultSet = lStatement.executeQuery(lCmdtySqlBuf.toString());
      
      for (int jj = 0; jj < lFromInx; jj++)
        lCmdtys.add(null);
      
      // 商品レコードのセットの作成
      int lIndex = 0;
      
      // 商品レコードのセットの作成
      while (lResultSet.next() && lIndex < lPageSize) {
        lCmdty = new SICmdty();
        lCmdty.setEncode(SIConfig.SIENCODE_NONE);
        lCmdty.setCmdtyCode(lResultSet.getString("CmdtyCode"));
        lCmdty.setIndividualCode(lResultSet.getString("IndividualCode"));
        lCmdty.setSellFromDate(lResultSet.getString("initdate"));
        lCmdty.setCmdtyName(lResultSet.getString("CmdtyName"));
        lCmdty.setMakerName(lResultSet.getString("MakerName"));
        lCmdty.setColorName(lResultSet.getString("ColorName"));
        lCmdty.setAmount(lResultSet.getString("Amount"));
        lCmdty.setRsrvAmount("調整中");
        lCmdty.setDescription(lResultSet.getString("Count"));
        lCmdtys.add(lCmdty);
        lIndex++;
      }
      for (int jj = lFromInx + lPageSize; jj < lRecordCount; jj++)
        lCmdtys.add(null);
      
    } catch (SQLException ex) {
      ex.printStackTrace();
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lCmdtys;
  }
  
  /**
   * <b>getShipCollection</b> 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * 
   * @param lConnection データベースへの接続コネクション
   * @param SILogin ログイン者情報をセットしたBean
   * @return レコードのセット
   * @throws なし
   */
  public Collection getShipCollection(Connection lConnection) throws SIDBAccessException {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SICmdty lCmdty = new SICmdty();
    StringBuffer lCountSqlBuf = new StringBuffer();// レコード数を求める
    StringBuffer lCmdtySqlBuf = new StringBuffer();// 商品レコード情報リストを求める
    
    Collection lCmdtys = new ArrayList();
    if (!searchFlg) return lCmdtys;
    
    // 基本のSQL
    lCmdtySqlBuf.append("SELECT a.cmdtyname,a.cmdtycode,a.individualcode,a.amount,b.colorname,c.makername,d.initdate,d.count ");
    lCmdtySqlBuf.append("FROM cmdtyunittbl AS a, colortbl AS b, makertbl AS c, ");
    lCmdtySqlBuf.append("(SELECT cmdtycode,individualcode,MAX(CASE WHEN delflg IN ('1','4') OR shipnumber='1' THEN initdatetime ELSE NULL END) AS initdate,SUM(CASE WHEN delflg='0' THEN 1 ELSE 0 END) AS count ");
    lCmdtySqlBuf.append("FROM shiphistorytbl GROUP BY cmdtycode,individualcode) AS d ");
    lCmdtySqlBuf.append(",cmdtymtbl AS e ");
    //lCmdtySqlBuf.append(",realstocktotalvw AS f ");
    lCmdtySqlBuf.append("WHERE a.individualcode=d.individualcode AND a.colorcode=b.colorcode AND a.makercode=c.makercode AND a.cmdtycode=d.cmdtycode ");
    lCmdtySqlBuf.append("AND a.cmdtycode=e.cmdtycode ");
    //lCmdtySqlBuf.append("WHERE a.individualcode=f.individualcode AND a.individualcode=d.individualcode ");
    //lCmdtySqlBuf.append("AND a.colorcode=b.colorcode AND a.makercode=c.makercode AND a.cmdtycode=f.cmdtycode AND a.cmdtycode=d.cmdtycode ");
    
    lCountSqlBuf.append("SELECT Count(*) FROM cmdtyunittbl AS a, colortbl AS b, makertbl AS c, ");
    lCountSqlBuf.append("(SELECT cmdtycode,individualcode,MAX(CASE WHEN delflg IN ('1','4') OR shipnumber='1' THEN initdatetime ELSE NULL END) AS initdate,SUM(CASE WHEN delflg='0' THEN 1 ELSE 0 END) AS count ");
    lCountSqlBuf.append("FROM shiphistorytbl GROUP BY cmdtycode,individualcode) AS d ");
    lCountSqlBuf.append(",cmdtymtbl AS e ");
    lCountSqlBuf.append("WHERE d.individualcode=a.individualcode AND a.colorcode=b.colorcode AND a.makercode=c.makercode AND a.cmdtycode=d.cmdtycode ");
    lCountSqlBuf.append("AND a.cmdtycode=e.cmdtycode ");
    
    // 検索の条件
    lCmdtySqlBuf.append(this.conditionSQL);
    lCountSqlBuf.append(this.conditionSQL);
    
    // ソート
    lCmdtySqlBuf.append("ORDER BY d.initdate DESC,a.individualcode ASC ");
    
    // 実行
    try {
      String rowCnt = SIDBUtil.getFirstData(lConnection, lCountSqlBuf.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);
      
      log.debug("getCollection:lCmdtySqlBuf=" + lCmdtySqlBuf.toString());
      lStatement = lConnection.createStatement();
      lCmdtySqlBuf.append(" LIMIT ").append(lPageSize);
      if(lFromInx>0) lCmdtySqlBuf.append(" OFFSET ").append(lFromInx);
      lResultSet = lStatement.executeQuery(lCmdtySqlBuf.toString());
      
      for (int jj = 0; jj < lFromInx; jj++)
        lCmdtys.add(null);
      
      // 商品レコードのセットの作成
      int lIndex = 0;
      
      // 商品レコードのセットの作成
      while (lResultSet.next() && lIndex < lPageSize) {
        lCmdty = new SICmdty();
        lCmdty.setEncode(SIConfig.SIENCODE_NONE);
        lCmdty.setCmdtyCode(lResultSet.getString("CmdtyCode"));
        lCmdty.setIndividualCode(lResultSet.getString("IndividualCode"));
        lCmdty.setSellFromDate(lResultSet.getString("initdate"));
        lCmdty.setCmdtyName(lResultSet.getString("CmdtyName"));
        lCmdty.setMakerName(lResultSet.getString("MakerName"));
        lCmdty.setColorName(lResultSet.getString("ColorName"));
        lCmdty.setAmount(lResultSet.getString("Amount"));
        lCmdty.setRsrvAmount("調整中");
        lCmdty.setDescription(lResultSet.getString("Count"));
        lCmdtys.add(lCmdty);
        lIndex++;
      }
      for (int jj = lFromInx + lPageSize; jj < lRecordCount; jj++)
        lCmdtys.add(null);
      
    } catch (SQLException ex) {
      ex.printStackTrace();
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lCmdtys;
  }
  
  /**
   * <b>validate</b> 入力したデータをチェックして、同時にSQLの条件文を作成します。
   * 
   * @param lRequest クライアントからのリクエスト
   * @return なし
   * @throws なし
   */
  
  public void validateStoreList(HttpServletRequest lRequest) {
    validate(lRequest, false);
  }
  
  public void validateShipList(HttpServletRequest lRequest) {
    validate(lRequest, true);
  }
  
  public void validate(HttpServletRequest lRequest, boolean shipflg) {
    
    SICustomErrors errors = new SICustomErrors();
    SITableConditionManager lConditionMan = new SITableConditionManager();
    
    // 在庫コード
    if (SIUtil.isNotNull(this.individualCodeTxt) && SICheckValid.checkValid(errors, "在庫コード", this.individualCodeTxt, SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE)) {
      lConditionMan.add(new SITableCondition("a", "individualcode", this.individualCodeTxt, SIConfig.SICONDITION_TYPE_FRONTLIKE, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // 商品名
    if (SIUtil.isNotNull(this.cmdtyNameTxt) && SICheckValid.checkValid(errors, "商品名", this.cmdtyNameTxt, SICheckDataConf.SICHECK_DATA_ZENKAKU_TYPE)) {
      lConditionMan.add(new SITableCondition("a", "cmdtyname", this.cmdtyNameTxt, SIConfig.SICONDITION_TYPE_LIKE, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // 在庫数量
    if (SIUtil.isNotNull(this.totalFromTxt) && SICheckValid.checkValid(errors, "在庫数量From", this.totalFromTxt, SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)) {// 7.1.1 ST0162 修正
      lConditionMan.add(new SITableCondition("a", "amount", this.totalFromTxt, SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    if (SIUtil.isNotNull(this.totalToTxt) && SICheckValid.checkValid(errors, "在庫数量To", this.totalToTxt, SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)) {
      lConditionMan.add(new SITableCondition("a", "amount", this.totalToTxt, SIConfig.SICONDITION_TYPE_LESS_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // 在庫数量大小
    try {
      if (errors.isEmpty() && !SICheckUtil.isEqual(this.totalFromTxt, this.totalToTxt)) {
        SICheckValid.checkValid(errors, "在庫数量From", "在庫数量To", this.totalFromTxt, this.totalToTxt, SICheckDataConf.SICHECK_DATA_VALUE_LESS_TYPE);
      }
    } catch (Exception e) {}
    
    // ブランド
    if (SIUtil.isNotNull(this.makerNameTxt) && SICheckValid.checkValid(errors, "ブランド", this.makerNameTxt, SICheckDataConf.SICHECK_DATA_ZENKAKU_TYPE)) {
      lConditionMan.add(new SITableCondition("c", "makername", this.makerNameTxt, SIConfig.SICONDITION_TYPE_LIKE, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // おすすめ
    if (SIUtil.isNotNull(this.adviceFlgRdo)) {
      if (this.adviceFlgRdo.equals("0")) {
        lConditionMan.add(new SITableCondition("", "AdviceFlg", this.adviceFlgRdo, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      } else {
        lConditionMan.add(new SITableCondition("", "AdviceFlg", "0", SIConfig.SICONDITION_TYPE_GREATER, SIConfig.SICONDITION_TYPE_AND));
      }
    }
    
    // 送料種別
    if (SIUtil.isNotNull(this.deliveryTypeCodeRdo)) {
      if (this.deliveryTypeCodeRdo.equals("0")) {
        lConditionMan.add(new SITableCondition("", "DeliveryTypeCode", this.deliveryTypeCodeRdo, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      } else {
        lConditionMan.add(new SITableCondition("", "DeliveryTypeCode", "0", SIConfig.SICONDITION_TYPE_GREATER, SIConfig.SICONDITION_TYPE_AND));
      }
    }
    
    // 予約販売
    if (SIUtil.isNotNull(this.rsrvEnableFlgRdo)) {
      if (this.rsrvEnableFlgRdo.equals("0")) {
        lConditionMan.add(new SITableCondition("", "RsrvEnableFlg", this.rsrvEnableFlgRdo, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      } else {
        lConditionMan.add(new SITableCondition("", "RsrvEnableFlg", "0", SIConfig.SICONDITION_TYPE_GREATER, SIConfig.SICONDITION_TYPE_AND));
      }
    }
    
    // 在庫管理
    if (SIUtil.isNotNull(this.amountFlgRdo)) {
      if (this.amountFlgRdo.equals("0")) {
        lConditionMan.add(new SITableCondition("", "AmountFlg", this.amountFlgRdo, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      } else {
        lConditionMan.add(new SITableCondition("", "AmountFlg", "0", SIConfig.SICONDITION_TYPE_GREATER, SIConfig.SICONDITION_TYPE_AND));
      }
    }
    
    // 販売状況表示（入庫管理専用）
    String appendSQL1 = "";
    if (SIUtil.isNotNull(this.salesSituationDisp)) {
      if (this.salesSituationDisp.equals("0")) {// カート
        appendSQL1 = "AND a.frontdispflg='1' AND NOT a.inquiryflg='1' AND (a.amountflg='0' OR NOT a.amount='0') ";
      } else if (this.salesSituationDisp.equals("1")) {// お問い合わせ
        appendSQL1 = "AND a.frontdispflg='1' AND a.inquiryflg='1' ";
      } else if (this.salesSituationDisp.equals("2")) {// 予約
        appendSQL1 = "AND a.frontdispflg='1' AND NOT a.inquiryflg='1' AND a.amountflg='1' AND a.amount='0' AND a.rsrvenableflg='1' AND (a.rsrvamount IS NULL OR NOT a.rsrvamount='0') ";
      } else if (this.salesSituationDisp.equals("3")) {// SOLD OUT
        appendSQL1 = "AND a.frontdispflg='1' AND a.inquiryflg!='1' AND a.amountflg='1' AND a.amount='0' AND (a.rsrvenableflg='0' OR (a.rsrvamount IS NOT NULL AND a.rsrvamount='0')) AND ((a.soldoutdate IS NOT NULL AND a.soldoutdate + "+SIConfig.DEFAULT_SOLDOUT+" > current_date) OR a.usednewFlg IN ('1','7')) ";
      } else if (this.salesSituationDisp.equals("4")) {// フロント非表示
        appendSQL1 = "AND (a.frontdispflg='0' OR (a.usednewFlg!='1' AND a.usedNewFlg!='7' AND a.inquiryflg='1' AND a.amountflg='1' AND a.amount='0' AND (a.rsrvenableflg='0' OR (a.rsrvamount IS NOT NULL AND a.rsrvamount='0')) AND (a.soldoutdate IS NULL OR a.soldoutdate + "+SIConfig.DEFAULT_SOLDOUT+" <= current_date))) ";
      }
      lConditionMan.add(new SITableCondition(appendSQL1));
    }
    
    // 未承認フラグ
    String appendSQL2 = "";
    if (SIUtil.isNotNull(this.neverApprovalFlgRdo)) {
      if (shipflg) {
        if (this.neverApprovalFlgRdo.equals("1") && SIUtil.isNotNull(this.selectedBranch)) {
          appendSQL2 = "AND a.individualcode IN(SELECT individualcode FROM shiphistorytbl WHERE delflg=0 AND shipbranch=" + SIDBUtil.SQL2Str(this.selectedBranch,") ");
        } else if (this.neverApprovalFlgRdo.equals("1")) {
          appendSQL2 = "AND d.count > 0 ";
        }
      } else {
        if (this.neverApprovalFlgRdo.equals("1") && SIUtil.isNotNull(this.selectedBranch)) {
          appendSQL2 = "AND a.individualcode IN(SELECT individualcode FROM storehistorytbl WHERE delflg=0 AND storebranch=" + SIDBUtil.SQL2Str(this.selectedBranch,") ");
        } else if (this.neverApprovalFlgRdo.equals("1")) {
          appendSQL2 = "AND d.count > 0 ";
        }
      }
      lConditionMan.add(new SITableCondition(appendSQL2));
    }
    
    // 在庫管理
    if (SIUtil.isNotNull(this.amountFlgRdo)) {
      if (this.amountFlgRdo.equals("0")) {
        lConditionMan.add(new SITableCondition("", "AmountFlg", this.amountFlgRdo, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      } else {
        lConditionMan.add(new SITableCondition("", "AmountFlg", "0", SIConfig.SICONDITION_TYPE_GREATER, SIConfig.SICONDITION_TYPE_AND));
      }
    }
    
    // カテゴリ
    if (SIUtil.isNotNull(this.ctgryCodeTxt)) {
      lConditionMan.add(new SITableCondition(" AND a.cmdtycode IN (SELECT cmdtycode FROM cmdtyctgrymtbl WHERE ctgrycode =" + SIDBUtil.SQL2Str(this.ctgryCodeTxt,") ")));
    }
    
    // 最終入出庫日(From)
    if (SIUtil.isNotNull(getInitDateFrom()) && SICheckValid.checkValid(errors, "最終入出庫日From", getInitDateFrom(), SICheckDataConf.SICHECK_DATA_DATE_TYPE)) {// 7.1.1 ST0162 修正
      if (SIDBMultiConf.SIDB_CURRENT_INX == SIDBMultiConf.SIDB_POSTGRESQL_INX) {
        lConditionMan.add(new SITableCondition("d", "initDate", getInitDateFrom(), SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      } else {
        lConditionMan.add(new SITableCondition("d", "initDate", new SIDateTimeType(getInitDateFrom()), SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      }
    }
    
    // 最終入出庫日(To)
    if (SIUtil.isNotNull(getInitDateTo()) && SICheckValid.checkValid(errors, "最終入出庫日To", getInitDateTo(), SICheckDataConf.SICHECK_DATA_DATE_TYPE)) {// 7.1.1 ST0162 修正
      if (SIDBMultiConf.SIDB_CURRENT_INX == SIDBMultiConf.SIDB_POSTGRESQL_INX) {
        lConditionMan.add(new SITableCondition("d", "initDate", getInitDateTo() + " 23:59:59", SIConfig.SICONDITION_TYPE_LESS_EQUAL, SIConfig.SICONDITION_TYPE_AND));
      } else {
        lConditionMan.add(new SITableCondition("d", "initDate", new SIDateTimeType(getInitDateTo() + " 23:59:59"), SIConfig.SICONDITION_TYPE_LESS_EQUAL,
            SIConfig.SICONDITION_TYPE_AND));
      }
    }
    
    // 最終入出庫日大小
    try {
      if (!SICheckUtil.dateEqual(this.getInitDateFrom(), this.getInitDateTo())) {
        SICheckValid.checkValid(errors, "最終入出庫日From", "最終入出庫日To", this.getInitDateFrom(), this.getInitDateTo(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);// 7.1.1 ST0162 修正
      }
    } catch (Exception e) {}
    
    // JANコード
    if (SIUtil.isNotNull(this.janCodeIndividual) && SICheckValid.checkValid(errors, "JANコード", this.janCodeIndividual, SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE)) {
      lConditionMan.add(new SITableCondition("a", "janCodeIndividual", this.janCodeIndividual, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // 死活フラグ
    if (SIUtil.isNotNull(this.disableFlg)) {
      lConditionMan.add(new SITableCondition("a", "disableFlg", this.disableFlg, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // 預け先
    if (SIUtil.isNotNull(this.storageCode)) {
      lConditionMan.add(new SITableCondition("e", "storageCode", this.storageCode, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    if (!errors.isEmpty()) {
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
    } else {
      lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    }
    this.setConditionSQL(lConditionMan.getCondtionSQL());
  }
  
}