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.SICmdtyAllocation;
import jp.co.sint.config.SIConfig;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.tools.SICheckDataConf;
import jp.co.sint.tools.SICheckValid;
import jp.co.sint.tools.SICustomError;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIURLParameter;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;

public class UICmdtyAllocationListCond extends SIBasic {
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  // mode0:件数取得クエリ
  private int COUNT_MODE = 0;
  
  // mode1:リスト取得クエリ
  private int LIST_MODE = 1;
  
  // 在庫保有支店
  private String branchCodeTxt = "";
  
  // 在庫コード
  private String individualCodeTxt = "";
  
  // ステータス
  private String[] statusChk = new String[0];
  
  // 商品名
  private String cmdtyNameTxt = "";
  
  // SQL検索時の条件文
  private String[] conditionSQL = new String[3];
  
  // SQK検索時の出力ブロック選択
  private String[] conditionBlock = new String[3];
  
  // 検索OKフラグ
  private boolean searchFlg = false;
  
  /**
   * UICmdtyAllocationListCond コンストラクタ
   * 
   * @param なし
   * @return なし
   * @throws なし
   */
  public UICmdtyAllocationListCond() {
    this.setStatusChk(new String[]{"0","1","2"});
  }
  
  // getter of 在庫保有支店
  public String getBranchCodeTxt() {
    return branchCodeTxt;
  }
  
  // getter of 在庫コード
  public String getIndividualCodeTxt() {
    return individualCodeTxt;
  }
  
  // getter of ステータス
  public String[] getStatusChk() {
    return statusChk;
  }
  
  // getter of 商品名
  public String getCmdtyNameTxt() {
    return cmdtyNameTxt;
  }
  
  // setter of 在庫保有支店
  public void setBranchCodeTxt(String branchCodeTxt) {
    this.branchCodeTxt = branchCodeTxt;
  }
  
  // setter of 在庫コード
  public void setIndividualCodeTxt(String individualCodeTxt) {
    this.individualCodeTxt = individualCodeTxt;
  }
  
  // setter of ステータス
  public void setStatusChk(String[] statusChk) {
    if (statusChk == null) statusChk = new String[0];
    this.statusChk = statusChk;
  }
  
  // setter of 商品名
  public void setCmdtyNameTxt(String cmdtyNameTxt) {
    this.cmdtyNameTxt = cmdtyNameTxt;
  }
  
  // getter of 条件文
  public String[] getConditionSQL() {
    return conditionSQL;
  }
  
  // setter of 条件文
  public void setConditionSQL(String[] lConditionSQL) {
    if (lConditionSQL == null) lConditionSQL = new String[3];
    this.conditionSQL = lConditionSQL;
  }
  
  // getter of 出力ブロック
  public String[] getConditionBlock() {
    return conditionBlock;
  }
  
  // setter of 条件文
  public void setConditionBlock(String[] lConditionBlock) {
    if (lConditionBlock == null) lConditionBlock = new String[3];
    this.conditionBlock = lConditionBlock;
  }
  
  /**
   * <b>init</b> 入力したデータを基づいて、このbeansを設定します。
   * 
   * @param request クライアントからリクエスト
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  public void init(HttpServletRequest request, SIURLParameter urlParam) {
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    super.init(request, urlParam);
    this.setBranchCodeTxt((String) urlParam.getParam("branchCodeTxt"));
    this.setIndividualCodeTxt((String) urlParam.getParam("individualCodeTxt"));
    this.setStatusChk((request.getParameterValues("statusChk")));
    this.setCmdtyNameTxt((String) urlParam.getParam("cmdtyNameTxt"));
  }
  
  /**
   * <b>validate</b 入力したデータをチェックします。
   * 
   * @param lRequest クライアントからのリクエスト
   * @return なし
   * @throws なし
   */
  
  public boolean validate(HttpServletRequest lRequest, Connection lConnection) {
    SICustomErrors errors = new SICustomErrors();
    StringBuffer lSqlBuf1 = new StringBuffer();
    StringBuffer lSqlBuf2 = new StringBuffer();
    StringBuffer lSqlBuf3 = new StringBuffer();
    
    // 在庫保有支店
    SICheckValid.checkValid(errors, "在庫保有支店", getBranchCodeTxt(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
    
    // 在庫コード
    SICheckValid.checkValid(errors, "在庫コード", getIndividualCodeTxt(), SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE2);
    SICheckValid.checkValid(errors, "在庫コード", getIndividualCodeTxt(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 16);
    
    // 商品名
    SICheckValid.checkValid(errors, "商品名", getCmdtyNameTxt(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 255);
    
    if(getStatusChk()==null || getStatusChk().length < 1){
      errors.addError(new SICustomError("input.data.select.require","検索対象"));
    }
    
    if (!errors.isEmpty()) {
      this.searchFlg = false;
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK, errors);
    } else {
      this.searchFlg = true;
      lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK);
      if (SIUtil.isNotNull(getBranchCodeTxt())) {
        lSqlBuf1.append("AND ro.reservebranch=").append(SIDBUtil.SQL2Str(getBranchCodeTxt(), " "));
        lSqlBuf2.append("AND kd.branchcode=").append(SIDBUtil.SQL2Str(getBranchCodeTxt(), " "));
        lSqlBuf3.append("AND al.branchcode=").append(SIDBUtil.SQL2Str(getBranchCodeTxt(), " "));
      }
      if (SIUtil.isNotNull(getIndividualCodeTxt())) {
        lSqlBuf1.append("AND ro.individualcode LIKE '").append(SIDBUtil.SQL2Like(getIndividualCodeTxt())).append("%' ");
        lSqlBuf2.append("AND kd.individualcode LIKE '").append(SIDBUtil.SQL2Like(getIndividualCodeTxt())).append("%' ");
        lSqlBuf3.append("AND od.individualcode LIKE '").append(SIDBUtil.SQL2Like(getIndividualCodeTxt())).append("%' ");
      }
      if (SIUtil.isNotNull(getCmdtyNameTxt())) {
        lSqlBuf1.append("AND ro.cmdtyname LIKE '%").append(SIDBUtil.SQL2Like(getCmdtyNameTxt())).append("%' ");
        lSqlBuf2.append("AND kd.cmdtyname LIKE '%").append(SIDBUtil.SQL2Like(getCmdtyNameTxt())).append("%' ");
        lSqlBuf3.append("AND od.cmdtyname LIKE '%").append(SIDBUtil.SQL2Like(getCmdtyNameTxt())).append("%' ");
      }
      this.setConditionSQL(new String[]{lSqlBuf1.toString(),lSqlBuf2.toString(),lSqlBuf3.toString()});
      for (int i=0;i<getStatusChk().length;i++){
        if(getStatusChk()[i].equals("0")) this.conditionBlock[0]="1";
        if(getStatusChk()[i].equals("1")) this.conditionBlock[1]="1";
        if(getStatusChk()[i].equals("2")) this.conditionBlock[2]="1";
      }
    }
    return errors.isEmpty();
  }
  
  public boolean validateCSV(){
    return searchFlg;
  }
  
  /**
   * <b>getCollection</b> 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * 
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws SIDBAccessException
   */
  public Collection getCollection(Connection lConnection) throws SIDBAccessException {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    Collection lResultColl = new ArrayList();
    if (!searchFlg) return lResultColl;
    
    // SQL
    String countSql = getSql(COUNT_MODE);
    String cmdtySql = getSql(LIST_MODE);
    
    // 実行
    try {
      String rowCnt = SIDBUtil.getFirstData(lConnection, countSql);
      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);
      
      log.debug("getCollection:lCmdtySqlBuf=" + cmdtySql);
      lStatement = lConnection.createStatement();
      cmdtySql = cmdtySql + " LIMIT " + lPageSize;
      if (lFromInx>0) cmdtySql = cmdtySql + " OFFSET " + lFromInx;
      lResultSet = lStatement.executeQuery(cmdtySql);
      
      for (int jj = 0; jj < lFromInx; jj++) lResultColl.add(null);
      
      int lIndex = 0;
      
      // レコードのセットの作成
      while (lResultSet.next() && lIndex < lPageSize) {
        SICmdtyAllocation lCmdty = new SICmdtyAllocation();
        lCmdty.setEncode(SIConfig.SIENCODE_NONE);
        lCmdty.setIndividualCode(lResultSet.getString("individualcode"));
        lCmdty.setCmdtyName(lResultSet.getString("cmdtyname"));
        lCmdty.setColorName(lResultSet.getString("colorname"));
        lCmdty.setStatus(lResultSet.getString("status"));
        lCmdty.setNumber(lResultSet.getString("number"));
        lCmdty.setCustName(lResultSet.getString("custname"));
        lCmdty.setDeliveryCustName(lResultSet.getString("deliveryaddressee"));
        lCmdty.setAddress1(lResultSet.getString("address"));
        lCmdty.setBranchName(lResultSet.getString("branch"));
        lCmdty.setChargeName(lResultSet.getString("charge"));
        lCmdty.setAmount(lResultSet.getString("amount"));
        lCmdty.setSendMailDateTime(lResultSet.getString("SendMailDateTime"));
        lResultColl.add(lCmdty);
        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;
  }
  
  public String getSql(int mode){
    StringBuffer lSqlBuf = new StringBuffer();
    if (mode == COUNT_MODE) {
      lSqlBuf.append("SELECT COUNT(countList.*) FROM (");
      if ("1".equals(this.conditionBlock[0])) {//予約
        lSqlBuf.append("SELECT 0 AS sort ");
        lSqlBuf.append("FROM reserveordertbl ro ");
        lSqlBuf.append("LEFT OUTER JOIN ");
        lSqlBuf.append("(SELECT br.branchname, ch.chargename, mu.username ");
        lSqlBuf.append(" FROM mngusermtbl mu, branchtbl br, chargetbl ch ");
        lSqlBuf.append(" WHERE mu.usercode=ch.chargecode ");
        lSqlBuf.append(" AND ch.belongingbranchcode=br.branchcode) ch ");
        lSqlBuf.append("ON ro.initusername=ch.username ");
        lSqlBuf.append(",custtbl cu ");
        lSqlBuf.append("WHERE ro.custcode=cu.custcode ");
        lSqlBuf.append("AND ro.reserveflg='1' ");
        lSqlBuf.append(getConditionSQL()[0]);
        if ("1".equals(this.conditionBlock[1]) || "1".equals(this.conditionBlock[2])) {
          lSqlBuf.append("UNION ALL ");
        }
      }
      if ("1".equals(this.conditionBlock[1])) {//キープ
        lSqlBuf.append("SELECT 1 AS sort ");
        lSqlBuf.append("FROM keeptbl kh, keepdetailtbl kd, branchtbl br, chargetbl ch ");
        lSqlBuf.append("WHERE kh.keepnumber=kd.keepnumber ");
        lSqlBuf.append("AND kh.branchcode=br.branchcode ");
        lSqlBuf.append("AND kh.chargecode=ch.chargecode ");
        lSqlBuf.append("AND kh.status!='2' ");
        lSqlBuf.append(getConditionSQL()[1]);
        if ("1".equals(this.conditionBlock[2])) {
          lSqlBuf.append("UNION ALL ");
        }
      }
      if ("1".equals(this.conditionBlock[2])) {//出荷待ち
        lSqlBuf.append("SELECT 2 AS sort ");
        lSqlBuf.append("FROM orderdetaillatestvw od, orderlatestvw oh, orderallocationinfotbl al ");
        lSqlBuf.append(",custtbl cu, branchtbl br, chargetbl ch ");
        lSqlBuf.append("WHERE od.ordercode=oh.ordercode ");
        lSqlBuf.append("AND oh.ordercode=al.ordercode ");
        lSqlBuf.append("AND od.individualcode=al.individualcode ");
        lSqlBuf.append("AND oh.custcode=cu.custcode ");
        lSqlBuf.append("AND oh.branchcode=br.branchcode ");
        lSqlBuf.append("AND oh.chargecode=ch.chargecode ");
        lSqlBuf.append("AND od.shippmentdate IS NULL ");
        lSqlBuf.append(getConditionSQL()[2]);
      }
      lSqlBuf.append(") countList ");
    } else if (mode == LIST_MODE) {
      if ("1".equals(this.conditionBlock[0])) {//予約
        lSqlBuf.append("SELECT ro.cmdtycode AS cmdtycode ");
        lSqlBuf.append(",ro.individualcode AS individualcode ");
        lSqlBuf.append(",ro.cmdtyname AS cmdtyname ");
        lSqlBuf.append(",co.colorname AS colorname ");
        lSqlBuf.append(",'予約' AS status,ro.reservecode AS number ");
        lSqlBuf.append(",cu.custname AS custname ");
        lSqlBuf.append(",'' AS deliveryaddressee ");
        lSqlBuf.append(",cu.address1 AS address ");
        lSqlBuf.append(",ch.branchname AS branch ");
        lSqlBuf.append(",ch.chargename AS charge ");
        lSqlBuf.append(",ro.assignamount AS amount ");
        lSqlBuf.append(",ro.shipmaildatetime AS SendMailDateTime ");
        lSqlBuf.append(",0 AS sort ");
        lSqlBuf.append("FROM reserveordertbl ro ");
        lSqlBuf.append("LEFT OUTER JOIN ");
        lSqlBuf.append("(SELECT br.branchname, ch.chargename, mu.username ");
        lSqlBuf.append(" FROM mngusermtbl mu, branchtbl br, chargetbl ch ");
        lSqlBuf.append(" WHERE mu.usercode=ch.chargecode ");
        lSqlBuf.append(" AND ch.belongingbranchcode=br.branchcode) ch ");
        lSqlBuf.append("ON ro.initusername=ch.username ");
        lSqlBuf.append(",custtbl cu ");
        lSqlBuf.append(",cmdtymtbl cm ");
        lSqlBuf.append(",colortbl co ");
        lSqlBuf.append("WHERE ro.custcode=cu.custcode ");
        lSqlBuf.append("AND ro.cmdtycode=cm.cmdtycode ");
        lSqlBuf.append("AND cm.colorcode=co.colorcode ");
        lSqlBuf.append("AND ro.reserveflg='1' ");
        lSqlBuf.append(getConditionSQL()[0]);
        if ("1".equals(this.conditionBlock[1]) || "1".equals(this.conditionBlock[2])) {
          lSqlBuf.append("UNION ALL ");
        }
      }
      if ("1".equals(this.conditionBlock[1])) {//キープ
        lSqlBuf.append("SELECT kd.cmdtycode AS cmdtycode ");
        lSqlBuf.append(",kd.individualcode AS individualcode ");
        lSqlBuf.append(",kd.cmdtyname AS cmdtyname ");
        lSqlBuf.append(",co.colorname AS colorname ");
        lSqlBuf.append(",'キープ' AS status ");
        lSqlBuf.append(",kh.keepnumber AS number ");
        lSqlBuf.append(",'' AS custname ");
        lSqlBuf.append(",'' AS deliveryaddressee ");
        lSqlBuf.append(",'' AS address ");
        lSqlBuf.append(",br.branchname AS branch ");
        lSqlBuf.append(",ch.chargename AS charge ");
        lSqlBuf.append(",kd.amount AS amount ");
        lSqlBuf.append(",null AS SendMailDateTime ");
        lSqlBuf.append(",1 AS sort ");
        lSqlBuf.append("FROM keeptbl kh ");
        lSqlBuf.append(",keepdetailtbl kd");
        lSqlBuf.append(",branchtbl br ");
        lSqlBuf.append(",chargetbl ch ");
        lSqlBuf.append(",cmdtymtbl cm ");
        lSqlBuf.append(",colortbl co ");
        lSqlBuf.append("WHERE kh.keepnumber=kd.keepnumber ");
        lSqlBuf.append("AND kh.branchcode=br.branchcode ");
        lSqlBuf.append("AND kh.chargecode=ch.chargecode ");
        lSqlBuf.append("AND kd.cmdtycode=cm.cmdtycode ");
        lSqlBuf.append("AND cm.colorcode=co.colorcode ");
        lSqlBuf.append("AND kh.status!='2' ");
        lSqlBuf.append(getConditionSQL()[1]);
        if ("1".equals(this.conditionBlock[2])) {
          lSqlBuf.append("UNION ALL ");
        }
      }
      if ("1".equals(this.conditionBlock[2])) {//出荷待ち
        lSqlBuf.append("SELECT od.cmdtycode AS cmdtycode ");
        lSqlBuf.append(",od.individualcode AS individualcode ");
        lSqlBuf.append(",od.cmdtyname AS cmdtyname ");
        lSqlBuf.append(",co.colorname AS colorname ");
        lSqlBuf.append(",'受注' AS status ");
        lSqlBuf.append(",oh.ordercode AS number ");
        lSqlBuf.append(",cu.custname AS custname ");
        lSqlBuf.append(",de.deliveryaddressee AS deliveryaddressee ");
        lSqlBuf.append(",cu.address1 AS address ");
        lSqlBuf.append(",br.branchname AS branch ");
        lSqlBuf.append(",ch.chargename AS charge ");
        lSqlBuf.append(",al.orderallocationnumber AS amount ");
        lSqlBuf.append(",null AS SendMailDateTime ");
        lSqlBuf.append(",2 AS sort ");
        lSqlBuf.append("FROM orderdetaillatestvw od ");
        lSqlBuf.append(",orderdeliverylatestvw de ");
        lSqlBuf.append(",orderlatestvw oh ");
        lSqlBuf.append(",orderallocationinfotbl al ");
        lSqlBuf.append(",custtbl cu ");
        lSqlBuf.append(",branchtbl br ");
        lSqlBuf.append(",chargetbl ch ");
        lSqlBuf.append(",cmdtymtbl cm ");
        lSqlBuf.append(",colortbl co ");
        lSqlBuf.append("WHERE od.ordercode=oh.ordercode ");
        lSqlBuf.append("AND oh.ordercode=al.ordercode ");
        lSqlBuf.append("AND oh.ordercode=de.ordercode ");
        lSqlBuf.append("AND de.deliverycode=od.deliverycode ");
        lSqlBuf.append("AND od.individualcode=al.individualcode ");
        lSqlBuf.append("AND oh.custcode=cu.custcode ");
        lSqlBuf.append("AND oh.branchcode=br.branchcode ");
        lSqlBuf.append("AND oh.chargecode=ch.chargecode ");
        lSqlBuf.append("AND od.cmdtycode=cm.cmdtycode ");
        lSqlBuf.append("AND cm.colorcode=co.colorcode ");
        lSqlBuf.append("AND od.shippmentdate IS NULL ");
        lSqlBuf.append(getConditionSQL()[2]);
      }
      lSqlBuf.append("ORDER BY individualcode,sort,number");
    }
    return lSqlBuf.toString();
  }
}