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 jp.co.sint.basic.SIBasic;
import jp.co.sint.basic.SIStock;
import jp.co.sint.config.SIConfig;
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.SIUtil;
import jp.co.sint.tools.SIURLParameter;

import org.apache.log4j.Category;


public class UIStockListCond extends SIBasic {
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  public UIStockListCond() {
  }
  
  //検索用項目
  private String stockCodeTxt = "";
  private String stockNameTxt = "";
  private String stockPronNameTxt = "";
  private String chargeCodeCbo = "";
  private String cmdtyNameTxt = "";
  private String individualCodeTxt = "";
  private String makerCodeTxt = "";
  
  //修正用項目
  private String stockCode = "";
  
  //検索条件
  private String conditionSQL = "";
  
  public String getChargeCodeCbo() {
    return chargeCodeCbo;
  }
  
  public void setChargeCodeCbo(String chargeCodeCbo) {
    if (SIUtil.isNull(chargeCodeCbo)) chargeCodeCbo = "";
    this.chargeCodeCbo = chargeCodeCbo;
  }
  
  public String getStockCodeTxt() {
    return stockCodeTxt;
  }
  
  public void setStockCodeTxt(String stockCodeTxt) {
    if (SIUtil.isNull(stockCodeTxt)) stockCodeTxt = "";
    this.stockCodeTxt = stockCodeTxt;
  }
  
  public String getStockNameTxt() {
    return stockNameTxt;
  }
  
  public void setStockNameTxt(String stockNameTxt) {
    if (SIUtil.isNull(stockNameTxt)) stockNameTxt = "";
    this.stockNameTxt = stockNameTxt;
  }
  
  public String getStockPronNameTxt() {
    return stockPronNameTxt;
  }
  
  public void setStockPronNameTxt(String stockPronNameTxt) {
    if (SIUtil.isNull(stockPronNameTxt)) stockPronNameTxt = "";
    this.stockPronNameTxt = stockPronNameTxt;
  }
  
  public String getStockCode() {
    return stockCode;
  }
  
  public void setStockCode(String stockCode) {
    if (SIUtil.isNull(stockCode)) stockCode = "";
    this.stockCode = stockCode;
  }
  
  public String getConditionSQL() {
    return conditionSQL;
  }
  
  public void setConditionSQL(String conditionSQL) {
    if (SIUtil.isNull(conditionSQL)) conditionSQL = "";
    this.conditionSQL = conditionSQL;
  }
  
  public String getCmdtyNameTxt() {
    return cmdtyNameTxt;
  }
  
  public String getIndividualCodeTxt() {
    return individualCodeTxt;
  }
  
  public String getMakerCodeTxt() {
    return makerCodeTxt;
  }
  
  public void setCmdtyNameTxt(String cmdtyNameTxt) {
    if (SIUtil.isNull(cmdtyNameTxt)) cmdtyNameTxt = "";
    this.cmdtyNameTxt = cmdtyNameTxt;
  }
  
  public void setIndividualCodeTxt(String individualCodeTxt) {
    if (SIUtil.isNull(individualCodeTxt)) individualCodeTxt = "";
    this.individualCodeTxt = individualCodeTxt;
  }
  
  public void setMakerCodeTxt(String makerCodeTxt) {
    if (SIUtil.isNull(makerCodeTxt)) makerCodeTxt = "";
    this.makerCodeTxt = makerCodeTxt;
  }
  
  /**
   * init 入力したデータから、このbeansを設定します。
   * 
   * @param HttpServletRequest
   * @param SIURLParameter
   * @return なし
   * @throws なし
   */
  public void init(HttpServletRequest lRequest, SIURLParameter lUrlParam) {
    super.init(lRequest, lUrlParam);
    this.setStockCode((String) lUrlParam.getParam("stockCode"));
    this.setStockCodeTxt((String) lUrlParam.getParam("stockCodeTxt"));
    this.setStockNameTxt((String) lUrlParam.getParam("stockNameTxt"));
    this.setStockPronNameTxt((String) lUrlParam.getParam("stockPronNameTxt"));
    this.setChargeCodeCbo((String) lUrlParam.getParam("chargeCodeCbo"));
    this.setCmdtyNameTxt((String) lUrlParam.getParam("cmdtyNameTxt"));
    this.setIndividualCodeTxt((String) lUrlParam.getParam("individualCodeTxt"));
    this.setMakerCodeTxt((String) lUrlParam.getParam("makerCodeTxt"));
  }
  
  
  /**
   * validate
   * 入力したデータをチェックします。
   * 不正なデータがある場合、エラーをオブジェクトに格納します。
   * そのオブジェクトは、エラーメッセージとして画面に表示されます。
   * @param HttpServletRequest ，Connection , SIURLParameter
   * @return true:エラーがない false:エラーが１つ以上ある
   * @throws なし
   */
  public boolean validate(HttpServletRequest lRequest) {
    SITableConditionManager lConditionMan = new SITableConditionManager();
    SICustomErrors errors = new SICustomErrors();
    boolean cmdtyFlg1=false;
    boolean cmdtyFlg2=false;
    boolean makerFlg=false;
    
    // 仕入先コード
    if (SIUtil.isNotNull(this.getStockCodeTxt()) && SICheckValid.checkValid(errors, "仕入先コード", this.getStockCodeTxt(), SICheckDataConf.SICHECK_DATA_DIGIT_TYPE)) {
      lConditionMan.add(new SITableCondition("a", "StockCode", this.getStockCodeTxt(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // 仕入先名
    if (SIUtil.isNotNull(this.getStockNameTxt()) && SICheckValid.checkValid(errors, "仕入先名", this.getStockNameTxt(), SICheckDataConf.SICHECK_DATA_CMDTYNAME_TYPE)) {
      lConditionMan.add(new SITableCondition("a", "StockName", this.getStockNameTxt(), SIConfig.SICONDITION_TYPE_LIKE, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // 仕入先名カナ
    if (SIUtil.isNotNull(this.getStockPronNameTxt()) && SICheckValid.checkValid(errors, "仕入先名カナ", this.getStockPronNameTxt(), SICheckDataConf.SICHECK_DATA_ZENKAKU_KANA_TYPE)) {
      lConditionMan.add(new SITableCondition("a", "StockPronName", this.getStockPronNameTxt(), SIConfig.SICONDITION_TYPE_LIKE, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // 仕入担当者
    if (SIUtil.isNotNull(this.getChargeCodeCbo())) {
      lConditionMan.add(new SITableCondition("a", "ChargeCode", this.getChargeCodeCbo(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // 在庫コード
    if (SIUtil.isNotNull(this.getIndividualCodeTxt()) && SICheckValid.checkValid(errors, "在庫コード", this.getStockCodeTxt(), SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE)) {
      cmdtyFlg1=true;
    }
    
    // 商品名
    if (SIUtil.isNotNull(this.getCmdtyNameTxt()) && SICheckValid.checkValid(errors, "商品名", this.getCmdtyNameTxt(), SICheckDataConf.SICHECK_DATA_CMDTYNAME_TYPE)) {
      cmdtyFlg2=true;
    }
    
    // ブランド
    if (SIUtil.isNotNull(this.getMakerCodeTxt())) {
      makerFlg=true;
    }
    if (cmdtyFlg1||cmdtyFlg2||makerFlg) {
      StringBuffer sql = new StringBuffer();
      sql.append(" AND a.stockcode IN (SELECT u.stockcode FROM cmdtyunittbl u ");
      sql.append(" WHERE 1=1 ");
      if (cmdtyFlg1) sql.append(" AND u.individualCode LIKE '").append(SIDBUtil.SQL2Like(this.getIndividualCodeTxt())).append("%' ");
      if (cmdtyFlg2) sql.append(" AND u.cmdtyName LIKE '%").append(SIDBUtil.SQL2Like(this.getCmdtyNameTxt())).append("%' ");
      if (makerFlg) sql.append(" AND u.makercode=").append(SIDBUtil.SQL2Str(this.getMakerCodeTxt(), " "));
      sql.append(") ");
      lConditionMan.add(new SITableCondition(sql.toString()));
    }
    this.setConditionSQL(lConditionMan.getCondtionSQL());
    if (!errors.isEmpty()) lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
    
    return errors.isEmpty();
  }
  
  /**
   * getCollection
   * 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * @param Connection , String , String , SILogin
   * @return Collection
   * @throws SIDBAccessException
   */
  public Collection getCollection(Connection lConnection) throws SIDBAccessException {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIStock lStock = new SIStock();
    StringBuffer lSqlBuf = new StringBuffer();
    StringBuffer lCountBuf = new StringBuffer();
    Collection lResultColl = new ArrayList();
    
    // 基本のSQL
    lSqlBuf.append("SELECT a.*,coalesce(b.cmdtyCount,0) AS cmdtyCount,c.chargeName FROM stockmtbl a ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT stockcode,COUNT(stockcode) as cmdtyCount FROM individualtbl GROUP BY stockcode) b ON a.stockcode = b.stockcode ");
    lSqlBuf.append("LEFT OUTER JOIN chargetbl c ON a.chargecode=c.chargecode ");
    lSqlBuf.append("WHERE 1=1 ").append(this.getConditionSQL());
    lSqlBuf.append("ORDER BY a.stockcode DESC");
    
    lCountBuf.append("SELECT count(*) FROM stockmtbl a ");
    lCountBuf.append("LEFT OUTER JOIN (SELECT stockcode,COUNT(stockcode) as cmdtyCount FROM individualtbl GROUP BY stockcode) b ON a.stockcode = b.stockcode ");
    lCountBuf.append("LEFT OUTER JOIN chargetbl c ON a.chargecode=c.chargecode ");
    lCountBuf.append("WHERE 1=1 ").append(this.getConditionSQL());
    
    log.debug("lSqlBuf=" + lSqlBuf.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;
      
      log.debug("getCollection:lFromInx=" + lFromInx + ",lToInx=" + lToInx + ",lRecordCount=" + lRecordCount);
      
      lStatement = lConnection.createStatement();
      lSqlBuf.append(" LIMIT ").append(lPageSize);
      if(lFromInx>0) lSqlBuf.append(" OFFSET ").append(lFromInx);
      log.debug("getCollection:lOrderBuf=" + lSqlBuf.toString());
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      
      for (int jj = 0; jj < lFromInx; jj++) lResultColl.add(null);
      int lIndex = 0;
      
      // レコードのセットの作成
      while (lResultSet.next() && lIndex < lPageSize) {
        lStock = new SIStock();
        lStock.setStockCode(lResultSet.getString("StockCode"));
        lStock.setStockName(lResultSet.getString("StockName"));
        lStock.setStockPronName(lResultSet.getString("StockPronName"));
        lStock.setPresidentName(lResultSet.getString("PresidentName"));
        lStock.setPresidentPronName(lResultSet.getString("PresidentPronName"));
        lStock.setAddress1(lResultSet.getString("Address1"));
        lStock.setAddress2(lResultSet.getString("Address2"));
        lStock.setAddress3(lResultSet.getString("Address3"));
        lStock.setPostCode1(lResultSet.getString("PostCode1"));
        lStock.setPostCode2(lResultSet.getString("PostCode2"));
        String emails = "";
        if (SIUtil.isNotNull(lResultSet.getString("Email"))){
          emails = lResultSet.getString("Email");
        }
        if (SIUtil.isNull(emails)&&SIUtil.isNotNull(lResultSet.getString("Email2"))){
          emails = lResultSet.getString("Email2");
        } else if (SIUtil.isNotNull(lResultSet.getString("Email2"))){
          emails = emails + "<br>" + lResultSet.getString("Email2");
        }
        if (SIUtil.isNull(emails)&&SIUtil.isNotNull(lResultSet.getString("Email3"))){
          emails = lResultSet.getString("Email3");
        } else if (SIUtil.isNotNull(lResultSet.getString("Email3"))){
          emails = emails + "<br>" + lResultSet.getString("Email3");
        }
        lStock.setEmail(emails);
        lStock.setFax(lResultSet.getString("Fax"));
        lStock.setTel(lResultSet.getString("Tel"));
        lStock.setChargeCode(lResultSet.getString("ChargeCode"));
        lStock.setChargeName(lResultSet.getString("ChargeName"));
        lStock.setDealerStation(lResultSet.getString("DealerStation"));
        lStock.setDealerStaff(lResultSet.getString("DealerStaff"));
        lStock.setCapital(lResultSet.getString("Capital"));
        if (SIUtil.isNotNull(lResultSet.getString("EstablishDate"))) {
          SIDateTime lDateTime = new SIDateTime(lResultSet.getTimestamp("EstablishDate"));
          lStock.setEstablishDateYear(Integer.toString(lDateTime.getYear()));
          lStock.setEstablishDateMonth(Integer.toString(lDateTime.getMonth()));
          lStock.setEstablishDateDay(Integer.toString(lDateTime.getDay()));
        }
        lStock.setAccountingPeriod(lResultSet.getString("AccountingPeriod"));
        lStock.setFinancialInst(lResultSet.getString("FinancialInst"));
        lStock.setFinancialBranch(lResultSet.getString("FinancialBranch"));
        lStock.setAccountHolder(lResultSet.getString("AccountHolder"));
        lStock.setAccountType(lResultSet.getString("AccountType"));
        lStock.setAccountNumber(lResultSet.getString("AccountNumber"));
        lStock.setCloseType(lResultSet.getString("CloseType"));
        lStock.setCloseLimit(lResultSet.getString("CloseLimit"));
        lStock.setPayType(lResultSet.getString("PayType"));
        lStock.setPayLimit(lResultSet.getString("PayLimit"));
        lStock.setEnableFlg(lResultSet.getString("EnableFlg"));
        lStock.setCmdtyCount(lResultSet.getString("cmdtyCount"));
        lResultColl.add(lStock);
        lIndex++;
      }
      for (int jj = lFromInx + lPageSize; jj < lRecordCount; jj++) lResultColl.add(null);
    } catch (Exception ex) {
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lResultColl;
  }
}