/**
 * 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.SICmdty;
import jp.co.sint.config.SIConfig;
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.SIURLParameter;
import jp.co.sint.tools.SIUtil;

/**
 * @version $Id: UIProcessCmdtyListCond.java,v 1.0 2006/07/31 Exp $
 * @author  加工商品一覧画面のbean
 * <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>
 *  Naotaka        2006/07/31  Original
 */
public class UIProcessCmdtyListCond extends SIBasic {
  
  //ログ用のインスタンスの生成
  private static Category log=Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  //在庫コード
  private String individualCode="";
  
  //商品名
  private String cmdtyName="";
  
  //中古新品区分
  private String usedNewFlg="";
  
  //販売状況
  private String salesSituationFlg="";
  
  //検索用SQL
  private String conditionSQL="";
  
  //検索フラグ
  private boolean searchFlg = true;
  
  //コンストラクタ
  public UIProcessCmdtyListCond(){
    searchFlg=false;
  }
  
  public UIProcessCmdtyListCond(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    init(lRequest,lUrlParam);
  }
  
  /** getter **/
  public String getIndividualCode(){
    return individualCode;
  }
  
  public String getCmdtyName(){
    return cmdtyName;
  }
  
  public String getUsedNewFlg(){
    return usedNewFlg;
  }
  
  public String getSalesSituationFlg(){
    return salesSituationFlg;
  }
  
  public String getConditionSQL(){
    return conditionSQL;
  }
  
  /** setter **/
  public void setIndividualCode(String individualCode){
    this.individualCode = individualCode;
  }
  
  public void setCmdtyName(String cmdtyName){
    this.cmdtyName = cmdtyName;
  }
  
  public void setUsedNewFlg(String usedNewFlg) {
    this.usedNewFlg = usedNewFlg;
  }
  
  public void setSalesSituationFlg(String salesSituationFlg) {
    this.salesSituationFlg = salesSituationFlg;
  }
  
  public void setConditionSQL(String conditionSQL){
  	this.conditionSQL = conditionSQL;
  }
  
  public void init(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    super.init(lRequest,lUrlParam);
    this.setIndividualCode((String)lUrlParam.getParam("individualCode"));//在庫コード
    this.setCmdtyName((String)lUrlParam.getParam("cmdtyName"));//商品名
    this.setUsedNewFlg((String)lUrlParam.getParam("usedNewFlg"));//中古新品区分
    this.setSalesSituationFlg((String)lUrlParam.getParam("salesSituationFlg"));//販売状況
  }
  
  public void validate(HttpServletRequest lRequest){
    SICustomErrors errors=new SICustomErrors();
    SITableConditionManager lConditionMan=new SITableConditionManager();
    
    //在庫コード
    if (SIUtil.isNotNull(this.individualCode)&&SICheckValid.checkValid(errors,"在庫コード",this.individualCode,SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE)){
      lConditionMan.add(new SITableCondition("id","individualcode",this.individualCode,SIConfig.SICONDITION_TYPE_FRONTLIKE,SIConfig.SICONDITION_TYPE_AND));
    }
    //商品名
    if (SIUtil.isNotNull(this.cmdtyName) && SICheckValid.checkValid(errors,"商品名",this.cmdtyName,SICheckDataConf.SICHECK_DATA_ZENKAKU_TYPE)) {
      lConditionMan.add(new SITableCondition("cm","cmdtyName",this.cmdtyName,SIConfig.SICONDITION_TYPE_LIKE,SIConfig.SICONDITION_TYPE_AND));
    }
    //中古新品区分
    if (SIUtil.isNotNull(this.usedNewFlg)){
      lConditionMan.add(new SITableCondition("id","usedNewFlg",this.usedNewFlg,SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND));
    }
    //販売状況表示
    String appendSQL ="";
    if (SIUtil.isNotNull(this.salesSituationFlg)){
      if(this.salesSituationFlg.equals("0")){//カート
        appendSQL="AND id.frontdispflg='1' AND NOT id.inquiryflg='1' AND (id.amountflg='0' OR NOT cm.amount='0') ";
      } else if(this.salesSituationFlg.equals("1")){//お問い合わせ
        appendSQL="AND id.frontdispflg='1' AND id.inquiryflg='1' ";
      } else if(this.salesSituationFlg.equals("2")){//予約
        appendSQL="AND id.frontdispflg='1' AND NOT id.inquiryflg='1' AND id.amountflg='1' AND cm.amount='0' AND id.rsrvenableflg='1' AND (id.rsrvamount IS NULL OR NOT id.rsrvamount='0') ";
      } else if(this.salesSituationFlg.equals("3")){//SOLD OUT
        appendSQL="AND id.frontdispflg='1' AND NOT id.inquiryflg='1' AND id.amountflg='1' AND cm.amount='0' AND (id.rsrvenableflg='0' OR id.rsrvamount='0') ";
      } else if(this.salesSituationFlg.equals("4")){//フロント非表示
        appendSQL="AND id.frontdispflg='0' ";
      }
    }
    
    if (!errors.isEmpty()){
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY,errors);
      searchFlg=false;
    }else{
      lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
      searchFlg=true;
    }
    //条件文の設定
    this.setConditionSQL(lConditionMan.getCondtionSQL()+appendSQL);
  }
  
  public Collection getCollection(Connection lConnection){
    Statement lStatement=null;
    ResultSet lResultSet=null;
    StringBuffer lCmdtyBuf=new StringBuffer();
    StringBuffer lCountBuf=new StringBuffer();
    StringBuffer lCommonBuf=new StringBuffer();
    Collection lResultColl=new ArrayList();
    if(!searchFlg){
      return lResultColl;
    }
    
    lCountBuf.append("SELECT count(id.individualCode) ");
    lCmdtyBuf.append("SELECT id.cmdtyCode,id.individualCode,cm.cmdtyName,id.usedNewFlg,id.bgPrice,id.purchasePrice,id.processingExpence,cm.amount,re.total ");
    lCommonBuf.append("FROM individualTbl id,cmdtyUnitTbl cm,realstockTotalVW re ");
    lCommonBuf.append("WHERE cm.individualCode=id.individualCode AND cm.individualCode=re.individualCode AND id.individualCode=re.individualCode ");
    lCommonBuf.append("AND cm.cmdtyCode=id.cmdtyCode AND cm.cmdtyCode=re.cmdtyCode AND id.cmdtyCode=re.cmdtyCode ");
    
     //検索の条件
    lCommonBuf.append(this.getConditionSQL());
    lCountBuf.append(lCommonBuf);
    lCmdtyBuf.append(lCommonBuf);
    
    //出力順
    lCmdtyBuf.append(" ORDER BY id.individualCode ");
    log.debug("lSqlBuf="+lCmdtyBuf.toString());
    
    //実行
    try{
      String rowCnt = SIDBUtil.getFirstData(lConnection, lCountBuf.toString());
      if(rowCnt.equals("") || rowCnt==null){
        rowCnt="0";
      }
      int lRecordCount=Integer.parseInt(rowCnt);//レコード数の取得
      
      int lPageSize=this.getPageSize();//ページサイズ
      int lPageNumber=this.getPageNumer();//ページ番号
      
      int lMaxPage = lRecordCount/lPageSize;
      if (lRecordCount % lPageSize > 0){
        lMaxPage++;
      }
      if (lPageNumber>lMaxPage)lPageNumber=lMaxPage;
      
      //開始レコードの番号の設定
      int lFromInx=(lPageNumber-1)*lPageSize;
      //終止のレコード番号の設定
      int lToInx=lFromInx+lPageSize-1;
      if (lFromInx<0)lFromInx=0;
      if (lToInx<0)lToInx=0;
      
      lStatement=lConnection.createStatement();
      lCmdtyBuf.append(" LIMIT ").append(lPageSize);
      if(lFromInx>0) lCmdtyBuf.append(" OFFSET ").append(lFromInx);
      lResultSet=lStatement.executeQuery(lCmdtyBuf.toString());
      
      for (int jj=0;jj<lFromInx;jj++)lResultColl.add(null);
      int lIndex=0;
      
      //商品レコードセットの作成
      while (lResultSet.next()&&lIndex<lPageSize){
        SICmdty cmdty= new SICmdty();
        cmdty.setCmdtyCode(lResultSet.getString("cmdtyCode"));//親コード
        cmdty.setIndividualCode(lResultSet.getString("individualCode"));//在庫コード
        cmdty.setCmdtyName(lResultSet.getString("cmdtyName"));//商品名
        cmdty.setUsedNewFlg(lResultSet.getInt("usedNewFlg"));//中古新品区分
        cmdty.setUnitPrice(lResultSet.getString("bgPrice"));//BG特価
        cmdty.setPurchasePrice(lResultSet.getString("purchasePrice"));//仕入価格
        cmdty.setProcessingExpence(lResultSet.getString("processingExpence"));//加工価格
        cmdty.setAmount(lResultSet.getString("amount"));//EC販売可能数
        cmdty.setRealAmount(lResultSet.getString("total"));//帳簿在庫数
        lResultColl.add(cmdty);
        lIndex++;
      }
      for (int jj=lFromInx+lPageSize;jj<lRecordCount;jj++)lResultColl.add(null);
    }catch(Exception ex){
      ex.printStackTrace();
    }finally{
      SIDBUtil.close(lResultSet,lStatement);
    }
    return lResultColl;
  }
}