/**
 * 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.basic.SICompany;
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.SICheckDataConf;
import jp.co.sint.tools.SICheckValid;
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: UIInventoryListCond.java,v 1.0 2006/05/30 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/30 11:06:28  Original
 */
public class UIInventoryListCond extends SIBasic {
  //ログ用のインスタンスの生成
  private static Category log=Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  //対象支店
  private String branchCode = "";
  
  private String branchName= "";
  //対象時期の年
  private String seasonYearCbo = "";
  //対象時期の月
  private String seasonMonthCbo = "";
  //対象年度
  private String season = ""; 
  
  //検索用のＳＱＬ文
  private String conditionSQL="";
  
  private boolean searchFlg = true;
  
  public UIInventoryListCond(){
    SIDateTime lDateTime = new SIDateTime();
    if(lDateTime.getMonth() < java.util.Calendar.MAY) lDateTime.addYear(-1);
    //対象時期の年
    seasonYearCbo=Integer.toString(lDateTime.getYear());
  }

  public UIInventoryListCond(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("branchCode"));
    this.setSeasonYearCbo((String)lUrlParam.getParam("seasonYearCbo"));
  }
  
  public void deleteinit(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    super.init(lRequest,lUrlParam);
    this.setBranchCode((String)lUrlParam.getParam("deletebranchCode"));
    this.setSeason((String)lUrlParam.getParam("deleteseason"));
    this.setSeasonYearCbo((String)lUrlParam.getParam("deleteseasonYearCbo"));
  }
  
  public void printinit(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    super.init(lRequest,lUrlParam);
    this.setBranchCode((String)lUrlParam.getParam("printbranchCode"));
    this.setSeasonYearCbo((String)lUrlParam.getParam("printseasonYear"));
    this.setSeasonMonthCbo((String)lUrlParam.getParam("printseasonMonth"));
  }
  
  /**
   * <b>getCollection</b>
   * 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws なし
   */
  public Collection getCollection(Connection lConnection,String branchcode) throws SIDBAccessException{
    Statement lStatement=null;
    ResultSet lResultSet=null;
    UIInventory lBasic=new UIInventory();
    StringBuffer lSqlBuf=new StringBuffer();
    Collection lResultColl=new ArrayList();
    String seasonFrom = this.getSeasonYearCbo()+"04";
    String seasonTo = String.valueOf(Integer.parseInt(this.getSeasonYearCbo())+1)+"05";
    
    lSqlBuf.append("select i.branchcode as branchcode , i.season, i.status, ");
    lSqlBuf.append("TO_CHAR(i.initdatetime,'yy/mm/dd') AS initdatetime, ");
    lSqlBuf.append("mtbl.seasonMonth AS seasonMonth,substr(i.season,1,4) as seasonYear  ");
    lSqlBuf.append(" from (select t.* from inventorytbl as t where branchcode=").append(branchcode);
    lSqlBuf.append(" and t.season > ").append(SIDBUtil.SQL2Str(seasonFrom));
    lSqlBuf.append(" and t.season < ").append(SIDBUtil.SQL2Str(seasonTo)).append(") AS i ");
    lSqlBuf.append(" RIGHT JOIN inventoryMonthtbl as mtbl on mtbl.seasonmonth = substr(i.season,5,6) ");
    lSqlBuf.append(" ORDER BY mtbl.priority");
    
    log.debug("lSqlBuf="+lSqlBuf.toString());
    
    //実行
    try{
      
      lStatement=lConnection.createStatement();
      lResultSet=lStatement.executeQuery(lSqlBuf.toString());
      
      //棚卸レコードのセットの作成
      while (lResultSet.next()){
        lBasic = new UIInventory();
        lBasic.setBranchCode(lResultSet.getString("branchCode"));
        lBasic.setSeason(lResultSet.getString("season"));
        lBasic.setStatus(lResultSet.getString("status"));
        lBasic.setInitdatetime(lResultSet.getString("initdatetime"));
        lBasic.setSeasonMonthCbo(lResultSet.getString("seasonMonth"));
        lBasic.setSeasonYearCbo(lResultSet.getString("seasonYear"));
        lResultColl.add(lBasic);
      }
    }catch(Exception ex){
      throw new SIDBAccessException(ex);
    }finally{
      SIDBUtil.close(lResultSet,lStatement);
    }
    return lResultColl;
  }
  
  /**
   * <b>getCollection</b>
   * 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws なし
   */
  public Collection getPrintCollection(Connection lConnection,String category) throws SIDBAccessException{
    Statement lStatement=null;
    ResultSet lResultSet=null;
    UIInventory lBasic=new UIInventory();
    StringBuffer lSqlBuf=new StringBuffer();
    Collection lResultColl=new ArrayList();
    
    lSqlBuf.append("select aa.cmdtycode,aa.individualcode,aa.branchcode,bb.BranchName,cc.cmdtyname,aa.").append(SIUtil.getAmountName(this.getSeasonCbo())).append(" AS AMOUNT ");
    lSqlBuf.append(" FROM ").append(SIConfig.SITABLE_STOCKRECORDTBL_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa ");
    lSqlBuf.append("LEFT JOIN BranchTbl AS bb ON aa.branchcode = bb.branchcode, ");
    lSqlBuf.append(SIConfig.SITABLE_CMDTY_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("cc ");
    lSqlBuf.append(" WHERE aa.branchcode = ").append(this.getBranchCode());
    lSqlBuf.append(" AND aa.cmdtycode = cc.cmdtycode ");
    lSqlBuf.append(" AND aa.stockyear = ").append(SIDBUtil.SQL2Str(this.getSeasonYearCbo()));
    lSqlBuf.append(" AND substr(aa.individualcode,1,1) = ").append(SIDBUtil.SQL2Str(category));
    lSqlBuf.append(" AND aa.").append(SIUtil.getAmountName(this.getSeasonCbo())).append(" <> 0");
    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 UIInventory();
        lBasic.setBranchCode(lResultSet.getString("branchcode"));
        lBasic.setIndividualCode(lResultSet.getString("individualcode"));
        lBasic.setCmdtyCode(lResultSet.getString("cmdtycode"));
        lBasic.setAmount(lResultSet.getString("amount"));
        lBasic.setBranchName(lResultSet.getString("BranchName"));
        lBasic.setCmdtyName(lResultSet.getString("cmdtyname"));
        lResultColl.add(lBasic);
      }
    }catch(Exception ex){
      throw new SIDBAccessException(ex);
    }finally{
      SIDBUtil.close(lResultSet,lStatement);
    }
    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.getSeasonYearCbo())){
      lConditionMan.add(new SITableCondition("aa","BranchCode",this.getBranchCode(),SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    }
    //対象年度
    SICheckValid.checkValid(errors, "対象年度", this.getSeasonYearCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
    
    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();
  }
  
  /**
   * <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>getCompanyCollection<b>
   * 条件に合ったレコードを検索して、結果のコレクションを作成して戻します。
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws SIDBAccessException
  **/
  public Collection getCompanyCollection2(Connection lConnection) throws SIDBAccessException{
    Statement lStatement=null;
    ResultSet lResultSet=null;
    Collection lResultColl=new ArrayList();
    StringBuffer lSqlBuf = new StringBuffer();
    SICompany lBasic = null;
    
    //検索条件にエラーがあると検索しない。
    if(!searchFlg){
      return lResultColl;
    }
    lSqlBuf.append("SELECT aa.BranchName,aa.BranchCode ");
    lSqlBuf.append("FROM BranchTbl AS aa ");
    lSqlBuf.append("WHERE 1=1 ");
    lSqlBuf.append(this.conditionSQL);
    lSqlBuf.append("ORDER BY aa.BranchCode ASC");
    
    log.debug("getCompanyCollection:lSqlBuf="+lSqlBuf.toString());
    
    //実行
    try{
      lStatement=lConnection.createStatement();
      lResultSet=lStatement.executeQuery(lSqlBuf.toString());
      
      //棚卸レコードのセットの作成
      while (lResultSet.next()){
        lBasic = new SICompany();
        lBasic.setCompanyName(lResultSet.getString("BranchName"));
        lBasic.setCompanyCode(lResultSet.getString("branchCode"));
        lResultColl.add(lBasic);
      }
    }catch(Exception ex){
      throw new SIDBAccessException(ex);
    }finally{
      SIDBUtil.close(lResultSet,lStatement);
    }
    return lResultColl;
  }
  
  /**
   * @return branchCode を戻します。
   */
  public String getBranchCode() {
    return branchCode;
  }
  
  /**
   * @param branchCode branchCode を設定。
   */
  public void setBranchCode(String branchCode) {
    this.branchCode = branchCode;
  }
  
  public String getSeasonCbo(){
    return this.seasonYearCbo + this.seasonMonthCbo;
  }
  
  public String getSeasonCboSH(){
    return this.seasonYearCbo + "/" + this.seasonMonthCbo;
  }
  
  /**
   * @return season を戻します。
   */
  public String getSeason() {
    return season;
  }
  
  /**
   * @param season season を設定。
   */
  public void setSeason(String season) {
    this.season = season;
  }
  
  /**
   * @return seasonYearCbo を戻します。
   */
  public String getSeasonYearCbo() {
    return seasonYearCbo;
  }
  
  /**
   * @param seasonYearCbo seasonYearCbo を設定。
   */
  public void setSeasonYearCbo(String seasonYearCbo) {
    this.seasonYearCbo = seasonYearCbo;
  }
  
  /**
   * @return seasonMonthCbo を戻します。
   */
  public String getSeasonMonthCbo() {
    return seasonMonthCbo;
  }
  
  /**
   * @param seasonMonthCbo seasonMonthCbo を設定。
   */
  public void setSeasonMonthCbo(String seasonMonthCbo) {
    this.seasonMonthCbo = seasonMonthCbo;
  }
  
  /**
   * @return branchName を戻します。
   */
  public String getBranchName(Connection lConnection) {
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append("SELECT BranchName FROM BranchTbl ");
    lSqlBuf.append("WHERE BranchCode="+SIDBUtil.SQL2Str(this.getBranchCode()," "));
    try {
      this.branchName= SIDBUtil.getFirstData(lConnection,lSqlBuf.toString());
    } catch (SIDBAccessException e) {
      e.printStackTrace();
    }
    return branchName;
  }
  
  /**
   * @param branchName branchName を設定。
   */
  public void setBranchName(String branchName) {
    this.branchName = branchName;
  }
}