/**
 * Copyright (c) 2003-2006 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.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.SITableCondition;
import jp.co.sint.database.SITableConditionManager;
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: UIInventoryDiffListCond.java,v 1.0 2006/05/29 Exp $
 * @author  Hong.M.J
 * <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>
 * Hong.M.J        2006/05/29 11:06:28  Original
 */
public class UIInventoryDiffListCond extends SIBasic {
  //ログ用のインスタンスの生成
  private static Category log=Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  //対象支店
  private String branchCode = "";
  
  //対象時期の年
  private String seasonYearCbo = "";
  
  //対象時期の月
  private String seasonMonthCbo = "";
  
  //登録状況
  private String updateFlgRdo="";
  
  //在庫コード
  private String individualCode = "";
  
  //対象時期
  private String season = "";
  
  //検索用のSQL文
  private String conditionSQL="";
  
  //ページ番号
  private String pageNumberTxt="1";
  
  private boolean searchFlg = true;
  
  public UIInventoryDiffListCond(){
    SIDateTime lDateTime = new SIDateTime();
    lDateTime.addMonth(-1);//当日日付の前月を初期値とする
    String Month=Integer.toString(lDateTime.getMonth());
    
    if(Month.length()==1) Month="0"+Month;
    
    //対象時期の年
    seasonYearCbo=Integer.toString(lDateTime.getYear());
    //対象時期の月
    seasonMonthCbo=Month;
    
    //条件文の設定
    SITableConditionManager lConditionMan=new SITableConditionManager();
    lConditionMan.add(new SITableCondition("aa","season",this.getSeasonCbo(),SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    this.conditionSQL=lConditionMan.getCondtionSQL();
  }
  
  public UIInventoryDiffListCond(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    init(lRequest,lUrlParam);
  }
  
  /**
   * <b>init</b>
   * 入力したデータを基づいて、このbeansを設定します。(検索項目)
   * @param lRequest
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  public void init(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    super.init(lRequest,lUrlParam);
    this.setBranchCode((String)lUrlParam.getParam("selectbranchCode"));
    this.setSeasonYearCbo((String)lUrlParam.getParam("seasonYearCbo"));
    this.setSeasonMonthCbo((String)lUrlParam.getParam("seasonMonthCbo"));
    this.setUpdateFlgRdo((String)lUrlParam.getParam("updateFlgRdo"));
  }
  
  /**
   * <b>updateinit</b>
   * 入力したデータを基づいて、このbeansを設定します。(選択し、差異理由を登録する画面に遷移する時)
   * @param lRequest
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  public void updateinit(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    super.init(lRequest,lUrlParam);
    this.setIndividualCode((String)lUrlParam.getParam("individualCode"));
    this.setBranchCode((String)lUrlParam.getParam("branchCode"));
    this.setSeason((String)lUrlParam.getParam("season"));
  }
  
  public UIInventoryDifference getInventoryDifference(Connection lConnection) throws SIDBAccessException{
    Statement lStatement=null;
    ResultSet lResultSet=null;
    UIInventoryDifference lBasic=new UIInventoryDifference();
    StringBuffer lSqlBuf=new StringBuffer();
    
    lSqlBuf.append("SELECT aa.individualCode,aa.branchCode,aa.season,aa.amount,aa.comment,aa.updateUser,cc.cmdtyName,cc.cmdtyCode ,");
    lSqlBuf.append("bb.").append(SIUtil.getAmountName(this.getSeason())).append(" AS inventoryStockAmount,");
    lSqlBuf.append("dd.").append(SIUtil.getAmountName(this.getSeason())).append(" AS stockRecordAmount,");
    lSqlBuf.append("ee.realamount AS realAmount, ee.ecamount AS ecstockAmount ");
    lSqlBuf.append("FROM ").append(SIConfig.SITABLE_INVENTORYDIFFERENCETBL_NAME).append(" aa, ");
    lSqlBuf.append(SIConfig.SITABLE_INVENTORYSTOCKTBL_NAME).append(" bb, ");//棚卸在庫
    lSqlBuf.append(SIConfig.SITABLE_STOCKRECORDTBL_NAME).append(" dd, ");//締処理在庫
    lSqlBuf.append(SIConfig.SIVIEW_REALSTOCKVW_NAME).append(" ee, ");//店舗在庫
    lSqlBuf.append("cmdtyunittbl AS cc " );
    lSqlBuf.append("WHERE aa.individualCode = cc.individualCode");
    lSqlBuf.append(" AND aa.cmdtycode = cc.cmdtycode");
    
    lSqlBuf.append(" AND aa.cmdtycode = bb.cmdtycode");
    lSqlBuf.append(" AND aa.individualCode = bb.individualCode");
    lSqlBuf.append(" AND aa.branchCode = bb.branchCode");
    lSqlBuf.append(" AND substr(aa.season,1,4) = bb.inventoryyear");
    
    lSqlBuf.append(" AND aa.cmdtycode = dd.cmdtycode");
    lSqlBuf.append(" AND aa.individualCode = dd.individualCode");
    lSqlBuf.append(" AND aa.branchCode = dd.branchCode");
    lSqlBuf.append(" AND substr(aa.season,1,4) = dd.stockyear");
    
    lSqlBuf.append(" AND aa.individualCode = ee.individualCode");
    lSqlBuf.append(" AND aa.branchCode = ee.branchCode");
    
    lSqlBuf.append(" AND aa.individualCode =").append(SIDBUtil.SQL2Str(this.getIndividualCode()));
    lSqlBuf.append(" AND aa.branchCode =").append(this.getBranchCode());
    lSqlBuf.append(" AND aa.season =").append(SIDBUtil.SQL2Str(this.getSeason()));
    
    //実行
    try{
      lStatement=lConnection.createStatement();
      lResultSet=lStatement.executeQuery(lSqlBuf.toString());
      
      //棚卸差異レコードのセットの作成
      if(lResultSet.next()){
        lBasic = new UIInventoryDifference();
        lBasic.setIndividualCode(lResultSet.getString("individualCode"));
        lBasic.setCmdtyCode(lResultSet.getString("cmdtyCode"));
        lBasic.setCmdtyName(lResultSet.getString("cmdtyName"));
        lBasic.setBranchCode(lResultSet.getString("branchCode"));
        lBasic.setSeason(lResultSet.getString("season"));
        lBasic.setAmount(lResultSet.getString("amount"));//棚卸差異数
        lBasic.setInventoryStockAmount(lResultSet.getString("inventoryStockAmount"));//棚卸在庫数
        lBasic.setStockRecordAmount(lResultSet.getString("stockRecordAmount"));//前月締在庫数
        lBasic.setRealAmount(lResultSet.getString("realAmount"));//在庫数量現在値の帳簿在庫
        lBasic.setEcstockAmount(lResultSet.getString("ecstockAmount"));//在庫数量現在値のEC販売可能在庫
        lBasic.setComment(lResultSet.getString("comment"));
        lBasic.setUpdateUser(lResultSet.getString("updateUser"));
        //棚卸差異数が正の時は「原価を手動入力」、負の時は「現在値を自動入力」をデフォルト値に持つ
        if(SIUtil.isDigitNegative(lBasic.getAmount())){//負
          lBasic.setPurchasePriceFlg("2");
        }else{
          lBasic.setPurchasePriceFlg("1");
        }
      }
    }catch(Exception ex){
      throw new SIDBAccessException(ex);
    }finally{
      SIDBUtil.close(lResultSet,lStatement);
    }
    return lBasic;
  }
  
  /**
   * <b>getCollection</b>
   * 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws なし
   */
  public Collection getCollection(Connection lConnection) throws SIDBAccessException{
    Statement lStatement=null;
    ResultSet lResultSet=null;
    UIInventoryDifference lBasic=new UIInventoryDifference();
    StringBuffer lSqlBuf=new StringBuffer();
    Collection lResultColl=new ArrayList();
    
    //検索条件にエラーがあると検索しない。
    if(!searchFlg){
      return lResultColl;
    }
    
    lSqlBuf.append("SELECT aa.individualCode,aa.branchCode,aa.season,aa.amount,aa.comment,aa.updateUser,cc.cmdtyname,bb.userName ");
    lSqlBuf.append("FROM ").append(SIConfig.SITABLE_INVENTORYDIFFERENCETBL_NAME).append(" aa ");
    lSqlBuf.append("LEFT JOIN (SELECT MNGUSERMTBL.userName AS userName , MNGUSERMTBL.UserCode AS UserCode FROM MNGUSERMTBL )as bb ON bb.UserCode = aa.updateUser,");
    lSqlBuf.append("cmdtyunittbl AS cc " );
    lSqlBuf.append("WHERE aa.individualCode = cc.individualCode");
    lSqlBuf.append(" AND aa.cmdtycode = cc.cmdtycode");
    lSqlBuf.append(this.conditionSQL);
    //出力順
    lSqlBuf.append(" ORDER BY aa.individualCode");
    log.debug("lSqlBuf="+lSqlBuf.toString());
    
    //実行
    try{
      lStatement=lConnection.createStatement();
      lResultSet=lStatement.executeQuery(lSqlBuf.toString());
      
      //棚卸差異レコードのセットの作成
      while (lResultSet.next()){
        lBasic = new UIInventoryDifference();
        lBasic.setIndividualCode(lResultSet.getString("individualCode"));
        lBasic.setCmdtyName(lResultSet.getString("cmdtyname"));
        lBasic.setBranchCode(lResultSet.getString("branchCode"));
        lBasic.setSeason(lResultSet.getString("season"));
        lBasic.setAmount(lResultSet.getString("amount"));
        lBasic.setComment(lResultSet.getString("comment"));
        lBasic.setUpdateUser(lResultSet.getString("updateUser"));
        lBasic.setUpdateUserName(lResultSet.getString("userName"));
        lResultColl.add(lBasic);
      }
    }catch(Exception ex){
      throw new SIDBAccessException(ex);
    }finally{
      SIDBUtil.close(lResultSet,lStatement);
    }
    return lResultColl;
  }
  
  /**
   * <b>getCompanyCollection<b>
   * 条件に合ったレコードを検索して、結果のコレクションを作成して戻します。
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws SIDBAccessException
   **/
  public Collection getCompanyCollection(Connection lConnection) throws SIDBAccessException{
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append("SELECT BranchName,BranchCode ");
    lSqlBuf.append("FROM BranchTbl ");
    lSqlBuf.append("ORDER BY BranchCode ASC");
    
    log.debug("getCompanyCollection:lSqlBuf="+lSqlBuf.toString());
    
    Collection lResultColl=new ArrayList();
    
    try {
      lResultColl=SIDBUtil.getCollection(lConnection,lSqlBuf.toString(),false,true);
    } catch (SIDBAccessException e) {
      e.printStackTrace();
    }
    return lResultColl;
  }
  
  /**
   * <b>validate</b>
   * 入力したデータをチェックして、同時にSQLの条件文を作成します。
   * @param lRequest クライアントからのリクエスト
   * @return なし
   * @throws なし
   */
  public void validate(HttpServletRequest lRequest){
    SICustomErrors errors=new SICustomErrors();
    SITableConditionManager lConditionMan=new SITableConditionManager();
    
    //支店コード
    if (SIUtil.isNotNull(this.getBranchCode())){
      lConditionMan.add(new SITableCondition("aa","branchCode",this.getBranchCode(),SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    }
    
    //対象年月
    if (SIUtil.isNotNull(this.getSeasonCbo())){
      if(this.getSeasonCbo().trim().length()!=6){
        errors.addError(new SICustomError("input.data.date","対象時期"));
      }else{
        lConditionMan.add(new SITableCondition("aa","season",this.getSeasonCbo(),SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
      }
    }
    
    //差異理由の登録状況
    if(SIUtil.isNotNull(this.getUpdateFlgRdo())){
      //0：未登録
      if(this.getUpdateFlgRdo().equals("0")){
        lConditionMan.add(new SITableCondition("aa","comment","",SIConfig.SICONDITION_TYPE_IS_NULL,SIConfig.SICONDITION_TYPE_AND));          
      //1：登録済み
      }else if(this.getUpdateFlgRdo().equals("1")){
        lConditionMan.add(new SITableCondition("aa","comment","",SIConfig.SICONDITION_TYPE_IS_NOT_NULL,SIConfig.SICONDITION_TYPE_AND));
      }
    }
    
    if (!errors.isEmpty()){
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY,errors);
      this.searchFlg = false;
    } else {
      lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    }
    //条件文の設定
    this.conditionSQL=lConditionMan.getCondtionSQL();
  }
  
  /**
   * @return seasonMonthCbo を戻します。
   */
  public String getSeasonMonthCbo() {
    return seasonMonthCbo;
  }
  
  /**
   * @param seasonMonthCbo seasonMonthCbo を設定。
   */
  public void setSeasonMonthCbo(String seasonMonthCbo) {
    this.seasonMonthCbo = seasonMonthCbo;
  }
  
  /**
   * @return seasonYearCbo を戻します。
   */
  public String getSeasonYearCbo() {
    return seasonYearCbo;
  }
  
  /**
   * @param seasonYearCbo seasonYearCbo を設定。
   */
  public void setSeasonYearCbo(String seasonYearCbo) {
    this.seasonYearCbo = seasonYearCbo;
  }
  
  /**
   * @return companyNameCdo を戻します。
   */
  public String getBranchCode() {
    return branchCode;
  }
  
  /**
   * @param companyNameCdo companyNameCdo を設定。
   */
  public void setBranchCode(String branchCode) {
    this.branchCode = branchCode;
  }
  
  /**
   * @return conditionSQL を戻します。
   */
  public String getConditionSQL() {
    return conditionSQL;
  }
  
  /**
   * @param conditionSQL conditionSQL を設定。
   */
  public void setConditionSQL(String conditionSQL) {
    this.conditionSQL = conditionSQL;
  }
  
  /**
   * @return updateFlgRdo を戻します。
   */
  public String getUpdateFlgRdo() {
    return updateFlgRdo;
  }
  
  /**
   * @param updateFlgRdo updateFlgRdo を設定。
   */
  public void setUpdateFlgRdo(String updateFlgRdo) {
    this.updateFlgRdo = updateFlgRdo;
  }
  
  /**
   * @return searchFlg を戻します。
   */
  public boolean isSearchFlg() {
    return searchFlg;
  }
  
  /**
   * @param searchFlg searchFlg を設定。
   */
  public void setSearchFlg(boolean searchFlg) {
    this.searchFlg = searchFlg;
  }
  
  public String getSeasonCbo(){
    return this.seasonYearCbo + this.seasonMonthCbo;
  }
  
  /**
   * @return individualCode を戻します。
   */
  public String getIndividualCode() {
    return individualCode;
  }
  
  /**
   * @param individualCode individualCode を設定。
   */
  public void setIndividualCode(String individualCode) {
    this.individualCode = individualCode;
  }
  
  /**
   * @return season を戻します。
   */
  public String getSeason() {
    return season;
  }
  
  /**
   * @param season season を設定。
   */
  public void setSeason(String season) {
    this.season = season;
  }
}