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.SIMaker;
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;

public class UIMakerListCond extends SIBasic {
  
  private String makerCodeTxt = "";//ブランドコード
  
  private String makerNameTxt = "";//ブランド名
  
  private String contractFlgTxt = "";//要契約フラグ
  
  private String sortOrderTxt = "";//ソート順
  
  private String regMakerCode = "";//編集用ブランドコード
  
  private String regFrontDispFlg = "";//編集用フロント表示
  
  private boolean searchFlg = true;
  
  public UIMakerListCond(){}
  

  public String getMakerCodeTxt() {
    return makerCodeTxt;
  }
  
  public String getMakerNameTxt() {
    return makerNameTxt;
  }
  
  public String getContractFlgTxt() {
    return contractFlgTxt;
  }
  
  public String getSortOrderTxt() {
    return sortOrderTxt;
  }
  
  public String getRegMakerCode() {
    return regMakerCode;
  }
  
  public String getRegFrontDispFlg() {
    return regFrontDispFlg;
  }
  
  public void setMakerCodeTxt(String makerCodeTxt) {
    if (SIUtil.isNull(makerCodeTxt)) makerCodeTxt = "";
    this.makerCodeTxt = makerCodeTxt;
  }
  
  public void setMakerNameTxt(String makerNameTxt) {
    if (SIUtil.isNull(makerNameTxt)) makerNameTxt = "";
    this.makerNameTxt = makerNameTxt;
  }
  
  public void setContractFlgTxt(String contractFlgTxt) {
    if (SIUtil.isNull(contractFlgTxt)) contractFlgTxt = "";
    this.contractFlgTxt = contractFlgTxt;
  }
  
  public void setSortOrderTxt(String sortOrderTxt) {
    if (SIUtil.isNull(sortOrderTxt)) sortOrderTxt = "";
    this.sortOrderTxt = sortOrderTxt;
  }
  
  public void setRegMakerCode(String regMakerCode) {
    if (SIUtil.isNull(regMakerCode)) regMakerCode = "";
    this.regMakerCode = regMakerCode;
  }
  
  public void setRegFrontDispFlg(String regFrontDispFlg) {
    if (SIUtil.isNull(regFrontDispFlg)) regFrontDispFlg = "";
    this.regFrontDispFlg = regFrontDispFlg;
  }
  
  public void init(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    super.init(lRequest, lUrlParam);
    this.setMakerCodeTxt((String) lUrlParam.getParam("makerCodeTxt"));
    this.setMakerNameTxt((String) lUrlParam.getParam("makerNameTxt"));
    this.setContractFlgTxt((String) lUrlParam.getParam("contractFlgTxt"));
    this.setSortOrderTxt((String) lUrlParam.getParam("sortOrderTxt"));
  }
  
  public void initModify(HttpServletRequest lRequest, SIURLParameter lUrlParam) {
    super.init(lRequest, lUrlParam);
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    this.setRegMakerCode((String) lUrlParam.getParam("regMakerCode"));// ブランドコード
    this.setRegFrontDispFlg((String) lUrlParam.getParam("frontDisp"));// フロント表示フラグ
  }
  
  public boolean validate(HttpServletRequest lRequest) {
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    SICustomErrors errors = new SICustomErrors();
    
    SICheckValid.checkValid(errors, "ブランドコード", this.getMakerCodeTxt(), SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
    SICheckValid.checkValid(errors, "ブランドコード", this.getMakerCodeTxt(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 4);
    SICheckValid.checkValid(errors, "ブランド名", this.getMakerNameTxt(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 128);
    if (!errors.isEmpty()) {
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
      this.searchFlg = false;
    }
    
    return errors.isEmpty();
  }
  public String getCondtionSQL() {
    SITableConditionManager lConditionMan = new SITableConditionManager();
    
    SITableCondition DD = new SITableCondition("a", "makerCode", this.makerCodeTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    DD.setBlankEnable(false);
    lConditionMan.add(DD);
    
    if (SIUtil.isNotNull(this.getContractFlgTxt())&&"0".equals(this.getContractFlgTxt())) {
      DD = new SITableCondition("a", "contractFlg", "0", SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
      lConditionMan.add(DD);
    }else if (SIUtil.isNotNull(this.getContractFlgTxt())&&"1".equals(this.getContractFlgTxt())) {
      DD = new SITableCondition("a", "contractFlg", "0", SIConfig.SICONDITION_TYPE_NOT_EQUAL, SIConfig.SICONDITION_TYPE_AND);
      lConditionMan.add(DD);
    }
    
    if (SIUtil.isNotNull(this.getSortOrderTxt())&&this.getSortOrderTxt().equals("1")) {
      lConditionMan.add(new SITableCondition(" AND sortOrder IS NOT NULL "));
    } else if (SIUtil.isNotNull(this.getSortOrderTxt())&&this.getSortOrderTxt().equals("0")) {
      lConditionMan.add(new SITableCondition(" AND sortOrder IS NULL "));
    }
    
    if (SIUtil.isNotNull(this.getMakerNameTxt())) {
      StringBuffer tmp = new StringBuffer();
      tmp.append(" AND (makerName LIKE '%").append(SIDBUtil.SQL2Like(this.makerNameTxt)).append("%' ");
      tmp.append(" OR otherName1 LIKE '%").append(SIDBUtil.SQL2Like(this.makerNameTxt)).append("%' ");
      tmp.append(" OR otherName2 LIKE '%").append(SIDBUtil.SQL2Like(this.makerNameTxt)).append("%') ");
      lConditionMan.add(new SITableCondition(tmp.toString()));
    }
    
    return lConditionMan.getCondtionSQL();
  }
  
  public Collection getCollection(Connection lConnection){
    Collection resultColl = new ArrayList();
    StringBuffer lSqlBuf = new StringBuffer();
    StringBuffer lMakerBuf = new StringBuffer();
    StringBuffer lCountBuf = new StringBuffer();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIMaker lMaker = new SIMaker();
    
    if (!this.searchFlg) return resultColl;
    
    lMakerBuf.append("SELECT a.*, CASE WHEN b.makercode IS NULL THEN 0 ELSE 1 END AS CmdtyCount ");
    
    lCountBuf.append("SELECT count(*) ");
    
    lSqlBuf.append("FROM MakerTbl a ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT makercode FROM cmdtymtbl GROUP BY makercode) b ");
    lSqlBuf.append("ON a.makercode=b.makercode WHERE 1=1 ");
    lSqlBuf.append(this.getCondtionSQL());
    
    lMakerBuf.append(lSqlBuf);
    lCountBuf.append(lSqlBuf);
    if (SIUtil.isNotNull(this.getSortOrderTxt())&&this.getSortOrderTxt().equals("1")) {
      lMakerBuf.append(" ORDER BY SortOrder ASC,a.MakerCode ASC");
    }else{
      lMakerBuf.append(" ORDER BY a.MakerCode DESC");
    }
    
    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();
      lMakerBuf.append(" LIMIT ").append(lPageSize);
      if(lFromInx>0) lMakerBuf.append(" OFFSET ").append(lFromInx);
      lResultSet = lStatement.executeQuery(lMakerBuf.toString());
      
      for (int jj = 0; jj < lFromInx; jj++)
        resultColl.add(null);
      
      int lIndex = 0;
      
      // レコードのセットの作成
      while (lResultSet.next() && lIndex < lPageSize) {
        lMaker = new SIMaker();
        lMaker.setEncode(SIConfig.SIENCODE_NONE);
        lMaker.setMakerCode(lResultSet.getString("MakerCode"));// ブランドコード
        lMaker.setMakerName(lResultSet.getString("MakerName"));// ブランド名
        lMaker.setOtherName1(lResultSet.getString("OtherName1"));//別名１
        lMaker.setOtherName2(lResultSet.getString("OtherName2"));//別名２
        lMaker.setDescription(lResultSet.getString("Memo"));// メモ
        lMaker.setTel(lResultSet.getString("Tel"));// 電話番号
        lMaker.setUrl(lResultSet.getString("Url"));// URL
        lMaker.setInitDateTime(lResultSet.getString("InitDateTime"));// 登録日時
        lMaker.setUpdateDateTime(lResultSet.getString("UpdateDateTime"));// 更新日時
        lMaker.setCmdtyCount(lResultSet.getString("CmdtyCount"));// 紐付き商品フラグ
        lMaker.setFrontDispFlg(lResultSet.getString("EnabledFlg"));// フロント表示フラグ
        lMaker.setContractFlg(lResultSet.getString("ContractFlg"));// 要契約フラグ
        lMaker.setSortOrder(lResultSet.getString("SortOrder"));//ソート順
        resultColl.add(lMaker);
        lIndex++;
      }
      
      for (int jj = lFromInx + lPageSize; jj < lRecordCount; jj++) resultColl.add(null);
      
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    
    return resultColl;
  }
}