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

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import jp.co.sint.config.SIConfig;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;
/**
 * @version $Id: SICtgry.java,v 1.0 Exp $
 * @author  Tsuyoshi Yagi
 * <br>Description: 店舗用在庫一覧
 * <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/29  Original
 */
public class SIStoreStockList extends SIBasic{
  //ログ用のインスタンスの生成
  private static Category log=Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  //親コード
  private String cmdtyCode= "";
  //在庫コード
  private String individualCode = "";
  //商品名
  private String cmdtyName = "";
  //数量
  private String amount = "";
  //支店
  private String branch = "";
  //支店コード
  private String branchCode = "";
  //カテゴリ
  private String ctgry = "";
  //フロント表示
  private String frontDispFlg = "";
  //販売状況
  private String salesSituationFlg = "";
  //仕入価格
  private String purchasePrice = "";
  //加工費
  private String processiongExpence = "";
  //単価
  private String bgPrice = "";
  //メーカ
  private String maker = "";
  
  // EDBTG003-00 mng-paku add start
  // ショップコード
  private String shopCode = "";
  
  // 商品構成フラグ
  private String cmdtyCompositionFlg = "";
  // EDBTG003-00 mng-paku add end
  
  /**
   * @return amountflg を戻します。
   */
  public String getAmountflg() {
    return amountflg;
  }
  
  /**
   * @param amountflg amountflg を設定。
   */
  public void setAmountflg(String amountflg) {
    this.amountflg = amountflg;
  }
  
  private String amountflg = "";
  
  /*
   * getter of amount.
   */
  public String getAmount() {
    return amount;
  }
  
  /*
   * setter of amount
   */
  public void setAmount(String amount) {
    this.amount = amount;
  }
  
  /*
   * getter of bgPrice.
   */
  public String getBgPrice() {
    return bgPrice;
  }
  
  /*
   * setter of bgPrice
   */
  public void setBgPrice(String bgPrice) {
    this.bgPrice = bgPrice;
  }
  
  /*
   * getter of branch.
   */
  public String getBranch() {
    return branch;
  }
  
  /*
   * setter of branch
   */
  public void setBranch(String branch) {
    this.branch = branch;
  }
  
  /*
   * 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 ctgry.
   */
  public String getCtgry() {
    return ctgry;
  }
  
  /*
   * setter of ctgry
   */
  public void setCtgry(String ctgry) {
    this.ctgry = ctgry;
  }
  
  /*
   * getter of frontDispFlg.
   */
  public String getFrontDispFlg() {
    return frontDispFlg;
  }
  
  /*
   * setter of frontDispFlg
   */
  public void setFrontDispFlg(String frontDispFlg) {
    this.frontDispFlg = frontDispFlg;
  }
  
  /*
   * getter of individualCode.
   */
  public String getIndividualCode() {
    return individualCode;
  }
  
  /*
   * setter of individualCode
   */
  public void setIndividualCode(String individualCode) {
    this.individualCode = individualCode;
  }
  
  /*
   * getter of processiongExpence.
   */
  public String getProcessiongExpence() {
    return processiongExpence;
  }
  
  /*
   * setter of processiongExpence
   */
  public void setProcessiongExpence(String processiongExpence) {
    this.processiongExpence = processiongExpence;
  }
  
  /*
   * getter of purchasePrice.
   */
  public String getPurchasePrice() {
    return purchasePrice;
  }
  
  /*
   * setter of purchasePrice
   */
  public void setPurchasePrice(String purchasePrice) {
    this.purchasePrice = purchasePrice;
  }
  
  /*
   * getter of salesSituationFlg.
   */
  public String getSalesSituationFlg() {
    return salesSituationFlg;
  }
  
  /*
   * setter of salesSituationFlg
   */
  public void setSalesSituationFlg(String salesSituationFlg) {
    this.salesSituationFlg = salesSituationFlg;
  }
  
  /*
   * getter of branchCode.
   */
  public String getBranchCode() {
    return branchCode;
  }
  
  /*
   * setter of branchCode
   */
  public void setBranchCode(String branchCode) {
    this.branchCode = branchCode;
  }
  
  /*
   * getter of maker.
   */
  public String getMaker() {
    return maker;
  }
  
  /*
   * setter of maker
   */
  public void setMaker(String maker) {
    this.maker = maker;
  }
  
  // EDBTG003-00 mng-paku add start
  /**
   * @return the shopCode
   */
  public String getShopCode() {
    return shopCode;
  }
  
  /**
   * @param shopCode the shopCode to set
   */
  public void setShopCode(String shopCode) {
    this.shopCode = shopCode;
  }
  
  /**
   * @return the cmdtyCompositionFlg
   */
  public String getCmdtyCompositionFlg() {
    return cmdtyCompositionFlg;
  }
  
  /**
   * @param cmdtyCompositionFlg the cmdtyCompositionFlg to set
   */
  public void setCmdtyCompositionFlg(String cmdtyCompositionFlg) {
    this.cmdtyCompositionFlg = cmdtyCompositionFlg;
  }
  // EDBTG003-00 mng-paku add end
  
  // EDBTG003-00 elecs-matsushima add start
  public void getSetCost(Connection lConnection) {
    String totalPurchaseprice = "0";
    String totalProcessingexpence = "0";
    
    Collection lCol = getFlagShipproductIndividual(lConnection);
    
    // 構成品グループ毎にグループ明細情報を取得
    Iterator ite = lCol.iterator();
    while(ite.hasNext()) {
      Map map = (Map)ite.next();
      String lResPurchaseprice = (String)map.get("purchaseprice");
      String lResProcessingexpence = (String)map.get("processingexpence");
      String lResCount = (String)map.get("compositioncount");
      
      totalPurchaseprice = SIUtil.add_LL(totalPurchaseprice, SIUtil.multi_LL(lResPurchaseprice, lResCount));
      totalProcessingexpence = SIUtil.add_LL(totalProcessingexpence, SIUtil.multi_LL(lResProcessingexpence, lResCount));
    }
    
    setPurchasePrice(totalPurchaseprice);
    setProcessiongExpence(totalProcessingexpence);
  }
  
  public Collection getFlagShipproductIndividual(Connection lConnection) {
    StringBuffer sqlStr = new StringBuffer();
    sqlStr.append("SELECT");
    sqlStr.append("  B.GROUPCODE");
    sqlStr.append(" ,CASE D.AMOUNTFLG WHEN 0 THEN COALESCE(D.PURCHASEPRICE2, 0) ELSE COALESCE(D.PURCHASEPRICE,0) END AS PURCHASEPRICE ");
    sqlStr.append(" ,COALESCE(D.processingexpence,0) AS processingexpence ");
    sqlStr.append(" ,C.compositioncount ");
    sqlStr.append("FROM");
    sqlStr.append("  CMDTYCOMPOSITIONMTBL A");
    sqlStr.append("  INNER JOIN COMPOSITIONGROUPMTBL B");
    sqlStr.append("  ON  A.SHOPCODE = B.SHOPCODE");
    sqlStr.append("  AND A.CMDTYCODE = B.CMDTYCODE");
    sqlStr.append("  AND A.INDIVIDUALCODE = B.INDIVIDUALCODE");
    sqlStr.append("  INNER JOIN COMPOSITIONGROUPDETAILTBL C");
    sqlStr.append("  ON  B.SHOPCODE = C.SHOPCODE");
    sqlStr.append("  AND B.CMDTYCODE = C.CMDTYCODE");
    sqlStr.append("  AND B.INDIVIDUALCODE = C.INDIVIDUALCODE");
    sqlStr.append("  AND B.GROUPCODE = C.GROUPCODE");
    sqlStr.append("  INNER JOIN individualtbl D");
    sqlStr.append("  ON  C.SHOPCODE = D.SHOPCODE");
    sqlStr.append("  AND C.DETAILCMDTYCODE = D.CMDTYCODE");
    sqlStr.append("  AND C.DETAILINDIVIDUALCODE = D.INDIVIDUALCODE ");
    sqlStr.append("WHERE");
    sqlStr.append("    A.SHOPCODE = ? ");
    sqlStr.append("AND A.CMDTYCODE = ? ");
    sqlStr.append("AND A.INDIVIDUALCODE = ? ");
    sqlStr.append("AND C.FLAGSHIPPRODUCTFLG = 1 ");
    // Statement作成
    PreparedStatement statement = null;
    // ResultSet変数
    ResultSet lResult = null;
    // 結果セットCollection
    Collection rec = new ArrayList();
    try{
      // statement生成
      statement = lConnection.prepareStatement(sqlStr.toString());
      // パラメータ設定
      statement.setString(1, getShopCode());
      statement.setString(2, getCmdtyCode());
      statement.setString(3, getIndividualCode());
      // SQL発行
      log.debug(sqlStr.toString());
      log.debug("shopcode="+getShopCode()+",cmdtycode="+getCmdtyCode()+",individualcode="+getIndividualCode());
      lResult = statement.executeQuery();
      // 代表構成商品の最新単価で、セット品のBG卸価を算出する
      while(lResult.next()) {
        // 構成グループ毎の代表構成品情報取得
        Map resMap = new HashMap();
        resMap.put("groupCode", lResult.getString("GROUPCODE"));
        resMap.put("purchaseprice", lResult.getString("PURCHASEPRICE"));
        resMap.put("compositioncount", lResult.getString("compositioncount"));
        resMap.put("processingexpence", lResult.getString("processingexpence"));
        rec.add(resMap);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      SIDBUtil.close(lResult, statement);
    }
    return rec;
  }
  
  public Collection getCmdtyCompositionAppendedIndividual(Connection lConnection) {
    // 返却クラスの初期化
    Collection rec = new ArrayList();
    // SQL生成
    StringBuffer sqlStr = new StringBuffer();
    sqlStr.append("SELECT ");
    sqlStr.append("  D.individualcode");
    sqlStr.append(" ,CASE D.AMOUNTFLG WHEN 0 THEN COALESCE(D.PURCHASEPRICE2, 0) ELSE COALESCE(D.PURCHASEPRICE,0) END AS PURCHASEPRICE ");
    sqlStr.append(" ,COALESCE(D.processingexpence,0) AS processingexpence ");
    sqlStr.append(" ,A.appendedcount ");
    sqlStr.append("FROM ");
    sqlStr.append("  APPENDEDCMDTYTBL A ");
    sqlStr.append("  INNER JOIN individualtbl D");
    sqlStr.append("  ON  A.SHOPCODE = D.SHOPCODE");
    sqlStr.append("  AND A.DETAILCMDTYCODE = D.CMDTYCODE");
    sqlStr.append("  AND A.DETAILINDIVIDUALCODE = D.INDIVIDUALCODE ");
    sqlStr.append("WHERE ");
    sqlStr.append("    A.SHOPCODE = ? ");
    sqlStr.append("AND A.CMDTYCODE  = ? ");
    sqlStr.append("AND A.INDIVIDUALCODE = ? ");
    // Statement作成
    PreparedStatement statement = null;
    // ResultSet変数
    ResultSet lResult = null;
    try{
      // statement生成
      statement = lConnection.prepareStatement(sqlStr.toString());
      // パラメータ設定
      statement.setString(1, this.getShopCode());
      statement.setString(2, this.getCmdtyCode());
      statement.setString(3, this.getIndividualCode());
      // SQL発行
      log.debug(sqlStr.toString());
      log.debug("shopcode="+getShopCode()+",cmdtycode="+getCmdtyCode()+",individualcode="+getIndividualCode());
      lResult = statement.executeQuery();
      while(lResult.next()) {
        Map resMap = new HashMap();
        resMap.put("individualcode", lResult.getString("individualcode"));
        resMap.put("purchaseprice", lResult.getString("PURCHASEPRICE"));
        resMap.put("compositioncount", lResult.getString("appendedcount"));
        resMap.put("processingexpence", lResult.getString("processingexpence"));
        rec.add(resMap);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      SIDBUtil.close(lResult, statement);
    }
    return rec;
  }
  
  public Collection getCmdtyCompositionOption(Connection lConnection) {
    // 返却クラスの初期化
    Collection rec = new ArrayList();
    // SQL生成
    StringBuffer sqlStr = new StringBuffer();
    sqlStr.append("SELECT ");
    sqlStr.append("  D.individualcode");
    sqlStr.append(" ,CASE D.AMOUNTFLG WHEN 0 THEN COALESCE(D.PURCHASEPRICE2, 0) ELSE COALESCE(D.PURCHASEPRICE,0) END AS PURCHASEPRICE ");
    sqlStr.append(" ,COALESCE(D.processingexpence,0) AS processingexpence ");
    sqlStr.append(" ,A.compositioncount ");
    sqlStr.append("FROM ");
    sqlStr.append("  COMPOSITIONOPTIONTBL A ");
    sqlStr.append("  INNER JOIN individualtbl D");
    sqlStr.append("  ON  A.SHOPCODE = D.SHOPCODE");
    sqlStr.append("  AND A.DETAILCMDTYCODE = D.CMDTYCODE");
    sqlStr.append("  AND A.DETAILINDIVIDUALCODE = D.INDIVIDUALCODE ");
    sqlStr.append("WHERE ");
    sqlStr.append("    A.SHOPCODE = ? ");
    sqlStr.append("AND A.CMDTYCODE = ? ");
    sqlStr.append("AND A.INDIVIDUALCODE = ? ");
    // Statement作成
    PreparedStatement statement = null;
    // ResultSet変数
    ResultSet lResult = null;
    try{
      // statement生成
      statement = lConnection.prepareStatement(sqlStr.toString());
      // パラメータ設定
      statement.setString(1, this.getShopCode());
      statement.setString(2, this.getCmdtyCode());
      statement.setString(3, this.getIndividualCode());
      // SQL発行
      log.debug(sqlStr.toString());
      log.debug("shopcode="+getShopCode()+",cmdtycode="+getCmdtyCode()+",individualcode="+getIndividualCode());
      lResult = statement.executeQuery();
      while(lResult.next()) {
        Map resMap = new HashMap();
        resMap.put("individualcode", lResult.getString("individualcode"));
        resMap.put("purchaseprice", lResult.getString("PURCHASEPRICE"));
        resMap.put("compositioncount", lResult.getString("compositioncount"));
        resMap.put("processingexpence", lResult.getString("processingexpence"));
        rec.add(resMap);
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      SIDBUtil.close(lResult, statement);
    }
    return rec;
  }
  // EDBTG003-00 elecs-matsushima add end
}