/**
 * Copyright (c) 2003-2004 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.SILogin;
import jp.co.sint.basic.SIStoreStock;
import jp.co.sint.config.SIConfig;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.tools.SIBGUtil;
import jp.co.sint.tools.SICheckDataConf;
import jp.co.sint.tools.SICheckValid;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;

/**
 * @version $Id: SIStoreStock.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>
 *         T.yagi 2005/08/16 Original
 */
public class UICmdtyStock extends SIBasic {
  //ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  private String[] branchCodeTxt = null;
  
  private String[] branchNameTxt = null;
  
  private String[] amountTxt = null;
  
  private String[] priorityTxt = null;
  
  private String[] realTxt = null;
  
  private String[] keepTxt = null;
  
  private String[] orderTxt = null;
  
  private String[] reserveTxt = null;
  
  private String[] shipHistoryTxt = null;
  
  private int cnt = 0;
  
  /**
   * @return amountTxt を戻します。
   */
  public String[] getAmountTxt() {
    return amountTxt;
  }
  
  /**
   * @param amountTxt
   *          amountTxt を設定。
   */
  public void setAmountTxt(String[] amountTxt) {
    this.amountTxt = amountTxt;
  }
  
  /**
   * @return branchCodeTxt を戻します。
   */
  public String[] getBranchCodeTxt() {
    return branchCodeTxt;
  }
  
  /**
   * @param branchCodeTxt
   *          branchCodeTxt を設定。
   */
  public void setBranchCodeTxt(String[] branchCodeTxt) {
    this.branchCodeTxt = branchCodeTxt;
  }
  
  /**
   * @return branchNameTxt を戻します。
   */
  public String[] getBranchNameTxt() {
    return branchNameTxt;
  }
  
  /**
   * @param branchNameTxt
   *          branchNameTxt を設定。
   */
  public void setBranchNameTxt(String[] branchNameTxt) {
    this.branchNameTxt = branchNameTxt;
  }
  
  /**
   * @return priorityTxt を戻します。
   */
  public String[] getPriorityTxt() {
    return priorityTxt;
  }
  
  /**
   * @param priorityTxt
   *          priorityTxt を設定。
   */
  public void setPriorityTxt(String[] priorityTxt) {
    this.priorityTxt = priorityTxt;
  }
  
  public String[] getKeepTxt() {
    return keepTxt;
  }
  
  public String[] getOrderTxt() {
    return orderTxt;
  }
  
  public String[] getRealTxt() {
    return realTxt;
  }
  
  public String[] getReserveTxt() {
    return reserveTxt;
  }
  
  public String[] getShipHistoryTxt() {
    return shipHistoryTxt;
  }
  
  public void setKeepTxt(String[] keepTxt) {
    this.keepTxt = keepTxt;
  }
  
  public void setOrderTxt(String[] orderTxt) {
    this.orderTxt = orderTxt;
  }
  
  public void setRealTxt(String[] realTxt) {
    this.realTxt = realTxt;
  }
  
  public void setReserveTxt(String[] reserveTxt) {
    this.reserveTxt = reserveTxt;
  }
  
  public void setShipHistoryTxt(String[] shipHistoryTxt) {
    this.shipHistoryTxt = shipHistoryTxt;
  }
  
  /**
   * @return cnt を戻します。
   */
  public int getCnt() {
    return cnt;
  }
  
  /**
   * @param cnt cnt を設定。
   */
  public void setCnt(int cnt) {
    this.cnt = cnt;
  }
  
  /**
   * init 入力したデータを基づいて、このbeansを設定します。
   * 
   * @param request
   *          データベースへのコネンクション
   * @return void
   * @throws なし
   */
  public void init(HttpServletRequest lRequest,Connection conn) {
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    this.setBranchCodeTxt(lRequest.getParameterValues("branchCodeTxt"));
    this.setBranchNameTxt(lRequest.getParameterValues("branchNameTxt"));
    this.setAmountTxt(lRequest.getParameterValues("amountTxt"));
    this.setRealTxt(lRequest.getParameterValues("realTxt"));
    this.setKeepTxt(lRequest.getParameterValues("keepTxt"));
    this.setOrderTxt(lRequest.getParameterValues("orderTxt"));
    this.setReserveTxt(lRequest.getParameterValues("reserveTxt"));
    this.setShipHistoryTxt(lRequest.getParameterValues("shipHistoryTxt"));
    this.setPriorityTxt(lRequest.getParameterValues("priorityTxt"));
    int count = 0;
    count = this.createCnt(conn);
    for(int i=0;i<count;i++){
      this.priorityTxt[i]=SIBGUtil.convertAto1(this.priorityTxt[i]);
    }
  }
  
  /**
   * getCollection
   * 
   * @param Connection
   * @return Collection
   * @throws SIDBAccessException
   */
  public Collection getCollection(Connection dbConnection, String shopCode, String cmdtyCode,String individualCode) throws SIDBAccessException {
    Collection storeStockColl = new ArrayList();
    Statement statement = null;
    ResultSet resultSet = null;
    SIStoreStock storeStock = new SIStoreStock();
    StringBuffer sqlStatement = new StringBuffer();
    sqlStatement.append("SELECT aa.branchcode as branch,aa.branchname,aa.branchflg,bb.* ");
    sqlStatement.append("FROM  branchtbl aa ");
    sqlStatement.append("LEFT JOIN (");
    sqlStatement.append("SELECT * FROM storestocktbl WHERE shopcode=").append(SIDBUtil.SQL2Str(shopCode," "));
    sqlStatement.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(cmdtyCode," "));
    sqlStatement.append("AND individualcode=").append(SIDBUtil.SQL2Str(individualCode,") bb "));;
    sqlStatement.append("ON aa.branchcode = bb.branchcode ORDER BY aa.branchcode");
    //実行
    try {
      statement = dbConnection.createStatement();
      log.debug("SQL=" + sqlStatement.toString());
      resultSet = statement.executeQuery(sqlStatement.toString());
      
      if (branchCodeTxt == null) {
        
        //商品レコードのセットの作成
        while (resultSet.next()) {
          storeStock = new SIStoreStock();
          storeStock.setShopCode(resultSet.getString("shopcode"));
          storeStock.setCmdtyCode(resultSet.getString("cmdtycode"));
          storeStock.setIndividualCode(resultSet.getString("individualcode"));
          storeStock.setBranchCode(resultSet.getString("branch"));
          storeStock.setSalesPriorityFlg(resultSet.getString("salespriorityflg"));
          storeStock.setAmount(resultSet.getString("amount"));
          if (SIUtil.isNull(storeStock.getAmount())) {
            storeStock.setAmount("0");
          }
          storeStock.setInitDateTime(resultSet.getString("initdatetime"));
          storeStock.setUpdateDateTime(resultSet.getString("updatedatetime"));
          storeStock.setBranchName(resultSet.getString("branchname"));
          storeStock.setBranchFlg(resultSet.getString("branchflg"));
          
          storeStockColl.add(storeStock);
        }
      } else {
        //商品レコードのセットの作成
        int count=0;
        while (resultSet.next()) {
          storeStock = new SIStoreStock();
          storeStock.setShopCode(resultSet.getString("shopcode"));
          storeStock.setCmdtyCode(resultSet.getString("cmdtycode"));
          storeStock.setIndividualCode(resultSet.getString("individualcode"));
          storeStock.setBranchCode(resultSet.getString("branch"));
          storeStock.setSalesPriorityFlg(this.priorityTxt[count]);
          storeStock.setAmount(this.amountTxt[count]);
          
          if (SIUtil.isNull(storeStock.getAmount())) {
            storeStock.setAmount("0");
          }
          storeStock.setInitDateTime(resultSet.getString("initdatetime"));
          storeStock.setUpdateDateTime(resultSet.getString("updatedatetime"));
          storeStock.setBranchName(resultSet.getString("branchname"));
          storeStock.setBranchFlg(resultSet.getString("branchflg"));
          
          storeStockColl.add(storeStock);
          count++;
        }
      }
    } catch (Exception ex) {
      log.debug("exception sql = " + sqlStatement.toString());
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return storeStockColl;
  }
  
  /**
   * getStockCollection
   * 
   * @param Connection
   * @return Collection
   * @throws SIDBAccessException
   */
  public Collection getStockCollection(Connection dbConnection, String cmdtyCode,String individualCode) throws SIDBAccessException {
    return getStockCollection(dbConnection,cmdtyCode,individualCode,"1");
  }
  public Collection getStockCollection(Connection dbConnection, String cmdtyCode,String individualCode,String realAmountFlg) throws SIDBAccessException {
    Collection storeStockColl = new ArrayList();
    Statement statement = null;
    ResultSet resultSet = null;
    SIStoreStock storeStock = new SIStoreStock();
    StringBuffer sqlStatement = new StringBuffer();
    sqlStatement.append("SELECT aa.branchcode as branch,aa.branchname,aa.branchflg,bb.*,cc.salespriorityflg ");
    sqlStatement.append("FROM  branchtbl aa LEFT JOIN ");
    if ("1".equals(realAmountFlg)) {
      sqlStatement.append("(SELECT cmdtycode,individualcode,branchcode,realamount,ecamount,keepamount,orderamount,reserveamount,shiphistoryamount ");
      sqlStatement.append("FROM realstockvw WHERE cmdtycode=").append(SIDBUtil.SQL2Str(cmdtyCode," "));
      sqlStatement.append("AND individualcode=").append(SIDBUtil.SQL2Str(individualCode,") bb "));
    } else {
      sqlStatement.append("(SELECT cmdtycode,individualcode,branchcode,'' AS realamount,amount AS ecamount ");
      sqlStatement.append(",'' AS keepamount,'' AS orderamount,'' AS reserveamount,'' AS shiphistoryamount ");
      sqlStatement.append("FROM storestocktbl WHERE cmdtycode=").append(SIDBUtil.SQL2Str(cmdtyCode," "));
      sqlStatement.append("AND individualcode=").append(SIDBUtil.SQL2Str(individualCode,") bb "));
    }
    sqlStatement.append("ON aa.branchcode = bb.branchcode ");
    sqlStatement.append("LEFT JOIN (SELECT branchcode,salespriorityflg ");
    sqlStatement.append("FROM storestocktbl WHERE cmdtycode=").append(SIDBUtil.SQL2Str(cmdtyCode," "));
    sqlStatement.append("AND individualcode=").append(SIDBUtil.SQL2Str(individualCode,") cc "));
    sqlStatement.append("ON aa.branchcode = cc.branchcode ORDER BY aa.branchcode");
    //実行
    try {
      statement = dbConnection.createStatement();
      log.debug("SQL=" + sqlStatement.toString());
      resultSet = statement.executeQuery(sqlStatement.toString());
      
      if (branchCodeTxt == null) {
        
        //商品レコードのセットの作成
        while (resultSet.next()) {
          storeStock = new SIStoreStock();
          storeStock.setShopCode("0");
          storeStock.setCmdtyCode(resultSet.getString("cmdtycode"));
          storeStock.setIndividualCode(resultSet.getString("individualcode"));
          storeStock.setBranchCode(resultSet.getString("branch"));
          storeStock.setSalesPriorityFlg(resultSet.getString("salespriorityflg"));
          storeStock.setAmount(resultSet.getString("ecamount"));
          if(SIUtil.isNull(storeStock.getAmount())){
            storeStock.setAmount("0");
            storeStock.setRealAmount("0");
            storeStock.setKeepAmount("0");
            storeStock.setOrderAmount("0");
            storeStock.setReserveAmount("0");
            storeStock.setShipHistoryAmount("0");
          }else{
            storeStock.setRealAmount(resultSet.getString("realamount"));
            storeStock.setKeepAmount(resultSet.getString("keepamount"));
            storeStock.setOrderAmount(resultSet.getString("orderamount"));
            storeStock.setReserveAmount(resultSet.getString("reserveamount"));
            storeStock.setShipHistoryAmount(resultSet.getString("shiphistoryamount"));
          }
          storeStock.setBranchName(resultSet.getString("branchname"));
          storeStock.setBranchFlg(resultSet.getString("branchflg"));
          
          storeStockColl.add(storeStock);
        }
      } else {
        //商品レコードのセットの作成
        int count=0;
        while (resultSet.next()) {
          storeStock = new SIStoreStock();
          storeStock.setShopCode("0");
          storeStock.setCmdtyCode(resultSet.getString("cmdtycode"));
          storeStock.setIndividualCode(resultSet.getString("individualcode"));
          storeStock.setBranchCode(resultSet.getString("branch"));
          storeStock.setSalesPriorityFlg(this.priorityTxt[count]);
          storeStock.setAmount(resultSet.getString("ecamount"));
          if(SIUtil.isNull(storeStock.getAmount())){
            storeStock.setAmount("0");
            storeStock.setRealAmount("0");
            storeStock.setKeepAmount("0");
            storeStock.setOrderAmount("0");
            storeStock.setReserveAmount("0");
            storeStock.setShipHistoryAmount("0");
          }else{
            storeStock.setRealAmount(resultSet.getString("realamount"));
            storeStock.setKeepAmount(resultSet.getString("keepamount"));
            storeStock.setOrderAmount(resultSet.getString("orderamount"));
            storeStock.setReserveAmount(resultSet.getString("reserveamount"));
            storeStock.setShipHistoryAmount(resultSet.getString("shiphistoryamount"));
          }
          storeStock.setBranchName(resultSet.getString("branchname"));
          storeStock.setBranchFlg(resultSet.getString("branchflg"));
          
          storeStockColl.add(storeStock);
          count++;
        }
      }
    } catch (Exception ex) {
      log.debug("exception sql = " + sqlStatement.toString());
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return storeStockColl;
  }
  
  //EDBTG003-00 elecs-matsushima add start
  /**
   * getStockCollection
   *
   * @param Connection
   * @return Collection
   * @throws SIDBAccessException
   */
  public Collection getStockCollection2(Connection dbConnection, String cmdtyCode,String individualCode) throws SIDBAccessException {
    return getStockCollection(dbConnection,cmdtyCode,individualCode,"1");
  }
  public Collection getStockCollection2(Connection dbConnection, String cmdtyCode,String individualCode,String realAmountFlg) throws SIDBAccessException {
    Collection storeStockColl = new ArrayList();
    Statement statement = null;
    ResultSet resultSet = null;
    SIStoreStock storeStock = new SIStoreStock();
    StringBuffer sqlStatement = new StringBuffer();
    sqlStatement.append("SELECT aa.branchcode as branch,aa.branchname,aa.branchflg,bb.*,cc.salespriorityflg ");
    sqlStatement.append("FROM  branchtbl aa LEFT JOIN ");
    if ("1".equals(realAmountFlg)) {
      sqlStatement.append("(SELECT cmdtycode,individualcode,branchcode,realamount,ecamount,keepamount,orderamount,reserveamount,shiphistoryamount ");
      sqlStatement.append("FROM realstockvw ");
      sqlStatement.append("WHERE individualcode=").append(SIDBUtil.SQL2Str(individualCode," "));
      if (SIUtil.isNotNull(cmdtyCode)){
        sqlStatement.append(" AND cmdtycode=").append(SIDBUtil.SQL2Str(cmdtyCode," "));
      }
      sqlStatement.append(") bb ");
    } else {
      sqlStatement.append("(SELECT cmdtycode,individualcode,branchcode,'' AS realamount,amount AS ecamount ");
      sqlStatement.append(",'' AS keepamount,'' AS orderamount,'' AS reserveamount,'' AS shiphistoryamount ");
      sqlStatement.append("FROM storestocktbl ");
      sqlStatement.append("WHERE individualcode=").append(SIDBUtil.SQL2Str(individualCode," "));
      if (SIUtil.isNotNull(cmdtyCode)){
        sqlStatement.append(" AND cmdtycode=").append(SIDBUtil.SQL2Str(cmdtyCode," "));
      }
      sqlStatement.append(") bb ");
    }
    sqlStatement.append("ON aa.branchcode = bb.branchcode ");
    sqlStatement.append("LEFT JOIN (SELECT branchcode,salespriorityflg ");
    sqlStatement.append("FROM storestocktbl ");
    sqlStatement.append("WHERE individualcode=").append(SIDBUtil.SQL2Str(individualCode," "));
    if (SIUtil.isNotNull(cmdtyCode)){
      sqlStatement.append(" AND cmdtycode=").append(SIDBUtil.SQL2Str(cmdtyCode," "));
    }
    sqlStatement.append(") cc ");
    sqlStatement.append("ON aa.branchcode = cc.branchcode ORDER BY aa.branchcode");
    //実行
    try {
      statement = dbConnection.createStatement();
      log.debug("SQL=" + sqlStatement.toString());
      resultSet = statement.executeQuery(sqlStatement.toString());
      
      if (this.branchCodeTxt == null) {
        
        //商品レコードのセットの作成
        while (resultSet.next()) {
          storeStock = new SIStoreStock();
          storeStock.setShopCode("0");
          storeStock.setCmdtyCode(resultSet.getString("cmdtycode"));
          storeStock.setIndividualCode(resultSet.getString("individualcode"));
          storeStock.setBranchCode(resultSet.getString("branch"));
          storeStock.setSalesPriorityFlg(resultSet.getString("salespriorityflg"));
          storeStock.setAmount(resultSet.getString("ecamount"));
          if(SIUtil.isNull(storeStock.getAmount())){
            storeStock.setAmount("0");
            storeStock.setRealAmount("0");
            storeStock.setKeepAmount("0");
            storeStock.setOrderAmount("0");
            storeStock.setReserveAmount("0");
            storeStock.setShipHistoryAmount("0");
          }else{
            storeStock.setRealAmount(resultSet.getString("realamount"));
            storeStock.setKeepAmount(resultSet.getString("keepamount"));
            storeStock.setOrderAmount(resultSet.getString("orderamount"));
            storeStock.setReserveAmount(resultSet.getString("reserveamount"));
            storeStock.setShipHistoryAmount(resultSet.getString("shiphistoryamount"));
          }
          storeStock.setBranchName(resultSet.getString("branchname"));
          storeStock.setBranchFlg(resultSet.getString("branchflg"));
          
          storeStockColl.add(storeStock);
        }
      } else {
        //商品レコードのセットの作成
        int count=0;
        while (resultSet.next()) {
          storeStock = new SIStoreStock();
          storeStock.setShopCode("0");
          storeStock.setCmdtyCode(resultSet.getString("cmdtycode"));
          storeStock.setIndividualCode(resultSet.getString("individualcode"));
          storeStock.setBranchCode(resultSet.getString("branch"));
          storeStock.setSalesPriorityFlg(this.priorityTxt[count]);
          storeStock.setAmount(resultSet.getString("ecamount"));
          if(SIUtil.isNull(storeStock.getAmount())){
            storeStock.setAmount("0");
            storeStock.setRealAmount("0");
            storeStock.setKeepAmount("0");
            storeStock.setOrderAmount("0");
            storeStock.setReserveAmount("0");
            storeStock.setShipHistoryAmount("0");
          }else{
            storeStock.setRealAmount(resultSet.getString("realamount"));
            storeStock.setKeepAmount(resultSet.getString("keepamount"));
            storeStock.setOrderAmount(resultSet.getString("orderamount"));
            storeStock.setReserveAmount(resultSet.getString("reserveamount"));
            storeStock.setShipHistoryAmount(resultSet.getString("shiphistoryamount"));
          }
          storeStock.setBranchName(resultSet.getString("branchname"));
          storeStock.setBranchFlg(resultSet.getString("branchflg"));
          
          storeStockColl.add(storeStock);
          count++;
        }
      }
    } catch (Exception ex) {
      log.debug("exception sql = " + sqlStatement.toString());
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return storeStockColl;
  }
  //EDBTG003-00 elecs-matsushima add end
  
  public boolean validateStock(HttpServletRequest lRequest, Connection conn) {
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    SICustomErrors errors = new SICustomErrors();
    
    this.setCnt(this.createCnt(conn));
    
      for (int i = 0; i < this.getCnt(); i++) {
        SICheckValid.checkValid(errors, "在庫数量" + this.branchNameTxt[i], this.amountTxt[i],
            SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
        if(SIUtil.isNull(this.priorityTxt[i])) this.priorityTxt[i]="26";//不明ならZを代入
        SICheckValid.checkValid(errors, "販売優先順位" + this.branchNameTxt[i], this.priorityTxt[i],
            SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      }
      
    if (!errors.isEmpty())
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
    return errors.isEmpty();
    
  }
  
  public int createCnt(Connection conn) {
    ResultSet resultSet = null;
    StringBuffer sqlStatement = new StringBuffer();
    Statement statement = null;
    int count = 0;
    sqlStatement.append("SELECT count(*) as count FROM  branchtbl ");
    //実行
    try {
      statement = conn.createStatement();
      log.debug("SQL=" + sqlStatement.toString());
      resultSet = statement.executeQuery(sqlStatement.toString());
      while (resultSet.next()) {
        count = (resultSet.getInt("count"));
      }
    } catch (Exception ex) {
      log.debug("exception sql = " + sqlStatement.toString());
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return count;
  }
  

}