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

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;

import javax.servlet.http.HttpServletRequest;

import jp.co.sint.basic.SIBasic;
import jp.co.sint.basic.SICmdty;
import jp.co.sint.basic.SICmdtyStndrd;
import jp.co.sint.basic.SICmdtyStndrdUtil;
import jp.co.sint.basic.SILogin;
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.tools.SIHTMLUtil;
import jp.co.sint.tools.SIUtil;
import oracle.jdbc.OracleTypes;
import jp.co.sint.tools.SIURLParameter;//7.1.1 ST0236 追加

import org.apache.log4j.Category;

/**
 * @version $Id: SICmdtyStndrdListCond.java,v 1.0 2003/09/12 Exp $
 * @author  Jinwang Chen
 * <br>Description:商品規格入力画面に規格1と規格2の選択の部分を処理します。
 * <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>
 * jwchen        2003/09/12 13:23:51  Original
 */
public class UIRegNewOrderStndrd extends SIBasic{
  //ログ用のインスタンスの生成
  private static Category log=Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  //ショップコード
  private String shopCode="";
  
  //商品コード
  private String cmdtyCode="";
  
  //商品名
  private String cmdtyName="";
  
  //規格コード1
  private String stndrdCodeSel1="";
  
  //規格名
  private String stndrdName1="";
  
  //規格コード2
  private String stndrdCodeSel2="";
  
  //規格名2
  private String stndrdName2="";
  
  /**
   * UICmdtyStndrdListCond
   * コンストラクタ
   * @param なし
   * @return なし
   * @throws なし
   */
  public UIRegNewOrderStndrd(){}
  
  //setter of ショップコード1
  public void setShopCode(String lShopCode){
    if (SIUtil.isNull(lShopCode)) lShopCode="";
    this.shopCode=SIUtil.changeTo(lShopCode.trim(),this.encode);
  }
  
  //setter of 商品コード
  public void setCmdtyCode(String lCmdtyCode){
    if (SIUtil.isNull(lCmdtyCode)) lCmdtyCode="";
    this.cmdtyCode=SIUtil.changeTo(lCmdtyCode.trim(),this.encode);
  }
  
  //setter of 商品名
  public void setCmdtyName(String lCmdtyName){
    if (SIUtil.isNull(lCmdtyName)) lCmdtyName="";
    this.cmdtyName=SIUtil.changeTo(lCmdtyName.trim(),this.encode);
  }
  
  //setter of 規格コード1
  public void setStndrdCodeSel1(String lStndrdCodeSel1){
    if (SIUtil.isNull(lStndrdCodeSel1)) lStndrdCodeSel1="";
    this.stndrdCodeSel1=SIUtil.changeTo(lStndrdCodeSel1.trim(),this.encode);
  }
  
  //setter of 規格名1
  public void setStndrdName1(String lStndrdName1){
    if (SIUtil.isNull(lStndrdName1)) lStndrdName1="";
    this.stndrdName1=SIUtil.changeTo(lStndrdName1.trim(),this.encode);
  }
  
  //setter of 規格コード2
  public void setStndrdCodeSel2(String lStndrdCodeSel2){
    if (SIUtil.isNull(lStndrdCodeSel2)) lStndrdCodeSel2="";
    this.stndrdCodeSel2=SIUtil.changeTo(lStndrdCodeSel2.trim(),this.encode);
  }
  
  //setter of 規格名2
  public void setStndrdName2(String lStndrdName2){
    if (SIUtil.isNull(lStndrdName2)) lStndrdName2="";
    this.stndrdName2=SIUtil.changeTo(lStndrdName2.trim(),this.encode);
  }
  
  //getter of ショップコード1
  public String getShopCode(){
    return this.shopCode;
  }
  
  //getter of ショップコード1
  public String getCmdtyCode(){
    return this.cmdtyCode;
  }
  
  //getter of 商品名
  public String getCmdtyName(){
    return this.cmdtyName;
  }
  
  //getter of 規格コード1
  public String getStndrdCodeSel1(){
    return this.stndrdCodeSel1;
  }
  
  //getter of 規格名1
  public String getStndrdName1(){
    return this.stndrdName1;
  }
  
  //getter of 規格コード2
  public String getStndrdCodeSel2(){
    return this.stndrdCodeSel2;
  }
  
  //getter of 規格名2
  public String getStndrdName2(){
    return this.stndrdName2;
  }
  
  /**
   * <b>init</b>
   * 画面からのデータを取得して設定します。
   * @param lRequest  クライアントからリクエスト
   * @param lConnection データベースへのコネンクション
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  public void init(HttpServletRequest lRequest,Connection lConnection,SIURLParameter lUrlParam){//7.1.1 ST0236 修正
    SILogin lLogin=SIHTMLUtil.getLogin(lRequest);
    
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    //7.1.1 ST0236 修正 ここから
    super.init(lRequest,lUrlParam);
    if (lLogin.isShop()){
      this.setShopCode(lLogin.getMallShopCode());
    }else{
      this.setShopCode((String)lUrlParam.getParam("shopCode"));
    }
    this.setCmdtyCode((String)lUrlParam.getParam("cmdtyCode"));
    this.setActionNameTxt((String)lUrlParam.getParam("actionNameTxt"));
    //7.1.1 ST0236 修正 ここまで
    
    //規格コードと規格名をセット
    this.setEncode(SIConfig.SIENCODE_NONE);
    try {
      StringBuffer lSqlBuf=new StringBuffer();
      lSqlBuf.append("SELECT DISTINCT StndrdCode1,StndrdCode2 FROM CmdtyStndrdMTbl ");
      lSqlBuf.append("WHERE ShopCode=").append(SIDBUtil.SQL2Str(this.shopCode," "));
      lSqlBuf.append("AND CmdtyCode=").append(SIDBUtil.SQL2Str(this.cmdtyCode," "));
      
      HashMap lResultMap=SIDBUtil.getOneRowData(lConnection,lSqlBuf.toString());
      if (lResultMap!=null){
      this.setStndrdCodeSel1((String)lResultMap.get("stndrdcode1"));
      this.setStndrdCodeSel2((String)lResultMap.get("stndrdcode2"));
      }
      lSqlBuf=new StringBuffer();
      if (SIUtil.isNotNull(stndrdCodeSel1)){
      lSqlBuf.append("SELECT StndrdName FROM StndrdNameMTbl " );
      lSqlBuf.append("WHERE ShopCode=").append(SIDBUtil.SQL2Str(this.getShopCode()," "));
      lSqlBuf.append("AND StndrdCode=").append(SIDBUtil.SQL2Str(this.getStndrdCodeSel1()," "));
        this.setStndrdName1(SIDBUtil.getFirstData(lConnection,lSqlBuf.toString()));
      }
      lSqlBuf=new StringBuffer();
      if (SIUtil.isNotNull(stndrdCodeSel2)){
      lSqlBuf.append("SELECT StndrdName FROM StndrdNameMTbl " );
      lSqlBuf.append("WHERE ShopCode=").append(SIDBUtil.SQL2Str(this.getShopCode()," "));
      lSqlBuf.append("AND   StndrdCode=").append(SIDBUtil.SQL2Str(this.getStndrdCodeSel2()," "));
        this.setStndrdName2(SIDBUtil.getFirstData(lConnection,lSqlBuf.toString()));
      }
    } catch (SIDBAccessException e) {
      e.printStackTrace();
    }
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
  }
  
  //7.2.0 ST1030 追加 ここから
  /**
   * <b>initUpdate</b>
   * 画面からのデータを取得して設定します。(バックの修正時に商品を追加するとき使用)
   * @param lRequest  クライアントからリクエスト
   * @param lConnection データベースへのコネンクション
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  public void initUpdate(HttpServletRequest lRequest,Connection lConnection,SIURLParameter lUrlParam) {
    SILogin lLogin=SIHTMLUtil.getLogin(lRequest);
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    super.init(lRequest, lUrlParam);
    
    if (lLogin.isShop()) {
      this.setShopCode(lLogin.getMallShopCode());
    } else {
      this.setShopCode((String)lUrlParam.getParam("newCmdtyShopCodeTxt"));
    }
    this.setCmdtyCode((String)lUrlParam.getParam("newCmdtyCodeTxt"));
    this.setActionNameTxt((String)lUrlParam.getParam("actionNameTxt"));
    
    //規格コードと規格名をセット
    this.setEncode(SIConfig.SIENCODE_NONE);
    try {
      StringBuffer lSqlBuf=new StringBuffer();
      lSqlBuf.append("SELECT DISTINCT StndrdCode1,StndrdCode2 FROM CmdtyStndrdMTbl ");
      lSqlBuf.append("WHERE ShopCode=").append(SIDBUtil.SQL2Str(this.shopCode," "));
      lSqlBuf.append("AND CmdtyCode=").append(SIDBUtil.SQL2Str(this.cmdtyCode," "));
      
      HashMap lResultMap=SIDBUtil.getOneRowData(lConnection,lSqlBuf.toString());
      if (lResultMap!=null){
        this.setStndrdCodeSel1((String)lResultMap.get("stndrdcode1"));
        this.setStndrdCodeSel2((String)lResultMap.get("stndrdcode2"));
      }
      lSqlBuf=new StringBuffer();
      if (SIUtil.isNotNull(stndrdCodeSel1)){
        lSqlBuf.append("SELECT StndrdName FROM StndrdNameMTbl " );
        lSqlBuf.append("WHERE ShopCode=").append(SIDBUtil.SQL2Str(this.getShopCode()," "));
        lSqlBuf.append("AND StndrdCode=").append(SIDBUtil.SQL2Str(this.getStndrdCodeSel1()," "));
        this.setStndrdName1(SIDBUtil.getFirstData(lConnection,lSqlBuf.toString()));
      }
      lSqlBuf=new StringBuffer();
      if (SIUtil.isNotNull(stndrdCodeSel2)){
        lSqlBuf.append("SELECT StndrdName FROM StndrdNameMTbl " );
        lSqlBuf.append("WHERE ShopCode=").append(SIDBUtil.SQL2Str(this.getShopCode()," "));
        lSqlBuf.append("AND   StndrdCode=").append(SIDBUtil.SQL2Str(this.getStndrdCodeSel2()," "));
        this.setStndrdName2(SIDBUtil.getFirstData(lConnection,lSqlBuf.toString()));
      }
    } catch (SIDBAccessException e) {
      e.printStackTrace();
    }
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
  }
  //7.2.0 ST1030 追加 ここまで
  
  public SICmdty getCmdtyInfo(Connection lConnection) throws SIDBAccessException{
    Statement lStatement=null;
    ResultSet lResultSet=null;
    SICmdty lCmdty=new SICmdty();
    
    StringBuffer sqlStr = new StringBuffer();
    sqlStr.append("SELECT a1.*,COALESCE((SELECT TaxRate FROM TaxVW),0) AS TaxRate ");
    sqlStr.append("FROM CmdtyVW a1 ");
    sqlStr.append("WHERE a1.shopCode=").append(SIDBUtil.SQL2Str(this.getShopCode()," "));
    sqlStr.append("AND a1.cmdtyCode=").append(SIDBUtil.SQL2Str(this.getCmdtyCode()," "));
    sqlStr.append("ORDER BY shopCode,cmdtyCode");
    
    //出力順
    log.debug("lSqlBuf="+sqlStr.toString());
    //実行
    try{
      lStatement=lConnection.createStatement();
      lResultSet=lStatement.executeQuery(sqlStr.toString());
      
      //商品レコードのセットの作成
      if(lResultSet.next()){
        lCmdty.setEncode(SIConfig.SIENCODE_NONE);
        lCmdty.setShopCode(lResultSet.getString("ShopCode"));
        lCmdty.setFrontShopName(lResultSet.getString("FrontShopName"));
        lCmdty.setCmdtyCode(lResultSet.getString("CmdtyCode"));
        lCmdty.setCmdtyName(lResultSet.getString("CmdtyName"));
        lCmdty.setDescription(lResultSet.getString("Description"));
        lCmdty.setUnitPrice(lResultSet.getString("UnitPrice"));
        lCmdty.setSalesUnitPrice(lResultSet.getString("SalesUnitPrice"));
        lCmdty.setStockUnitPrice(lResultSet.getString("StockUnitPrice"));
        lCmdty.setTaxFlg(lResultSet.getString("taxFlg"));//税区分
        lCmdty.setAmount(lResultSet.getString("Amount"));
        lCmdty.setSubTotal(lResultSet.getString("SubTotal"));
        lCmdty.setTotal(lResultSet.getString("Total"));
        lCmdty.setAdviceFlg(lResultSet.getString("AdviceFlg"));
        lCmdty.setNewFlg(lResultSet.getString("NewFlg"));
        lCmdty.setCommentOfStock(lResultSet.getString("CommentOfStock"));
        lCmdty.setSalesFlg(lResultSet.getString("SalesFlg"));
        lCmdty.setCalcUnitPrice(lResultSet.getString("CalcUnitPrice"));
        lCmdty.setHasStndrd(lResultSet.getString("HasStndrd"));
        lCmdty.setMemberDiscountFlg(lResultSet.getString("memberDiscountFlg"));//会員値引フラグ
        //7.2.0 ST1051 追加 ここから
        lCmdty.setDiscountFlg(lResultSet.getString("DISCOUNTFLG"));//一括値引フラグ
        lCmdty.setSalesDiscountFlg(lResultSet.getString("SALESDISCOUNTFLG"));//特価値引フラグ
        //7.2.0 ST1051 追加 ここまで
        lCmdty.setTaxRate(lResultSet.getString("taxRate"));//税率
        lCmdty.setDiscountFromDate(SIDBUtil.getDate(lResultSet.getTimestamp("discountfromdate")));//値引き期間From
        lCmdty.setDiscountToDate(SIDBUtil.getDate(lResultSet.getTimestamp("discounttodate")));//値引き期間To
        lCmdty.setDiscountRate(lResultSet.getString("discountRate"));//値引率
        lCmdty.setSellFromDate(SIDBUtil.getDate(lResultSet.getTimestamp("sellFromDate")));//販売開始日
        lCmdty.setRsrvEnableFlg(lResultSet.getString("rsrvEnableFlg"));//予約販売フラグ
        lCmdty.setAmount(lResultSet.getString("amount"));//在庫数量
        lCmdty.setNoStockFlg(lResultSet.getString("NoStockFlg"));//在庫無し販買フラグ
      }
    }catch(Exception ex){
      throw new SIDBAccessException(ex);
    }finally{
      SIDBUtil.close(lResultSet,lStatement);
    }
    return lCmdty;
  }
  
  /**
   * <b>getConnection</b>
   * matrixのデータを生成します。
   * @param lConnection データベースへのコネンクション
   * @return なし
   * @throws なし
   */
  public Collection getCollection(Connection lConnection){
    Statement lStatement=null;
    ResultSet lResultSet=null;
    CallableStatement cstmt=null;
    SICmdtyStndrd lCmdtyStndrd=new SICmdtyStndrd();
    String lSql=SICmdtyStndrdUtil.makeCmdtyStndrdSQL(shopCode,cmdtyCode,stndrdCodeSel1,stndrdCodeSel2);
    log.debug("getCollection:lSql="+lSql);
    if (SIUtil.isNull(lSql))return new ArrayList();
    
    Collection lCmdtyStndrdes=new ArrayList();
    
    //実行
    try {
      if (SIDBMultiConf.SIDB_CURRENT_INX ==SIDBMultiConf.SIDB_POSTGRESQL_INX){
        lStatement=lConnection.createStatement();
        lResultSet=lStatement.executeQuery(lSql);
      }else{
        cstmt = lConnection.prepareCall(lSql);
        cstmt.registerOutParameter(1,OracleTypes.CURSOR);
        cstmt.execute();
        lResultSet =(ResultSet)cstmt.getObject(1);
      }
      while (lResultSet.next()){
        if (SIUtil.isNull(lResultSet.getString("CmdtyStndrdCode")))continue;
        lCmdtyStndrd=new SICmdtyStndrd();
        lCmdtyStndrd.setEncode(SIConfig.SIENCODE_NONE);
        lCmdtyStndrd.setShopCode(lResultSet.getString("ShopCode"));
        lCmdtyStndrd.setCmdtyCode(lResultSet.getString("CmdtyCode"));
        lCmdtyStndrd.setCmdtyStndrdCode(lResultSet.getString("CmdtyStndrdCode"));
        lCmdtyStndrd.setStndrdCode1(lResultSet.getString("StndrdCode1"));
        lCmdtyStndrd.setElementCode1(lResultSet.getString("ElementCode1"));
        lCmdtyStndrd.setElementName1(lResultSet.getString("ElementName1"));
        lCmdtyStndrd.setStndrdCode2(lResultSet.getString("StndrdCode2"));
        lCmdtyStndrd.setElementCode2(lResultSet.getString("ElementCode2"));
        lCmdtyStndrd.setElementName2(lResultSet.getString("ElementName2"));
        lCmdtyStndrd.setAmount(lResultSet.getString("Amount"));
        lCmdtyStndrdes.add(lCmdtyStndrd);
      }
    } catch (SQLException e) {
      e.printStackTrace();
      return new ArrayList();
    }finally{
      if (cstmt!=null)try {cstmt.close();} catch (SQLException sqle){}
      SIDBUtil.close(lResultSet,lStatement);
    }
    return lCmdtyStndrdes;
  }
}