package jp.co.sint.beans.mallmgr;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

import javax.servlet.http.HttpServletRequest;

import jp.co.sint.basic.SIBasic;
import jp.co.sint.basic.SILogin;
import jp.co.sint.basic.SIStock;
import jp.co.sint.basic.SIStockHistory;
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.SICustomError;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIDateTime;
import jp.co.sint.tools.SIFatalException;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIUtil;
import jp.co.sint.tools.SIURLParameter;

import org.apache.log4j.Category;


public class UIStockDecide extends SIBasic {
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  public UIStockDecide() {
    this.storeTypeChk = new String[] {"1","3","8","11","12","13","14","15","16","18"};
  }
  
  //検索用項目
  private String individualCodeTxt = "";
  private String cmdtyNameTxt = "";
  private String branchCodeCbo = "";
  private String checkAuthority = "";
  private String[] storeTypeChk = new String[11];
  private boolean isStore = true;
  private Collection itemData = new ArrayList();
  
  //検索条件
  private String conditionSQL = "";
  
  public String getIndividualCodeTxt() {
    return individualCodeTxt;
  }
  
  public String getCmdtyNameTxt() {
    return cmdtyNameTxt;
  }
  
  public String getBranchCodeCbo() {
    return branchCodeCbo;
  }
  
  public String getCheckAuthority() {
    return checkAuthority;
  }
  
  public String[] getStoreTypeChk() {
    return storeTypeChk;
  }
  
  public boolean isStore() {
    return isStore;
  }
  
  public Collection getItemData() {
    return itemData;
  }
  
  public String getConditionSQL() {
    return conditionSQL;
  }
  
  public void setIndividualCodeTxt(String individualCodeTxt) {
    if (SIUtil.isNull(individualCodeTxt)) individualCodeTxt = "";
    this.individualCodeTxt = individualCodeTxt;
  }
  
  public void setCmdtyNameTxt(String cmdtyNameTxt) {
    if (SIUtil.isNull(cmdtyNameTxt)) cmdtyNameTxt = "";
    this.cmdtyNameTxt = cmdtyNameTxt;
  }
  
  public void setBranchCodeCbo(String branchCodeCbo) {
    if (SIUtil.isNull(branchCodeCbo)) branchCodeCbo = "";
    this.branchCodeCbo = branchCodeCbo;
  }
  
  public void setCheckAuthority(String checkAuthority) {
    if (SIUtil.isNull(checkAuthority)) checkAuthority = "";
    this.checkAuthority = checkAuthority;
  }
  
  public void setStoreTypeChk(String[] storeTypeChk) {
    if (storeTypeChk==null) storeTypeChk = new String[10];
    this.storeTypeChk = storeTypeChk;
  }
  
  public void setStore(boolean isStore) {
    this.isStore = isStore;
  }
  
  public void setConditionSQL(String conditionSQL) {
    if (SIUtil.isNull(conditionSQL)) conditionSQL = "";
    this.conditionSQL = conditionSQL;
  }
  
  
  /**
   * init 入力したデータから、このbeansを設定します。
   * 
   * @param HttpServletRequest
   * @param SIURLParameter
   * @return なし
   * @throws なし
   */
  public void init(HttpServletRequest lRequest, SIURLParameter lUrlParam) {
    super.init(lRequest, lUrlParam);
    this.setIndividualCodeTxt((String) lUrlParam.getParam("individualCodeTxt"));
    this.setCmdtyNameTxt((String) lUrlParam.getParam("cmdtyNameTxt"));
    this.setBranchCodeCbo((String) lUrlParam.getParam("branchCodeCbo"));
    this.setCheckAuthority((String) lUrlParam.getParam("checkAuthority"));
    this.setStoreTypeChk(lRequest.getParameterValues("storeTypeChk"));
    this.setStore("1".equals((String)lUrlParam.getParam("isStore")));
  }
  
  public void initDecideAll(HttpServletRequest lRequest,boolean isApprove){
    Collection resColl = new ArrayList();
    String[] checkedData = lRequest.getParameterValues("checkbox_decide");
    if (checkedData!=null&&checkedData.length>0){
      int i=0;
      while (checkedData.length>i) {
        UIStockHistory data = new UIStockHistory();
        data.setCmdtyCode(checkedData[i].split("~")[0]);
        data.setIndividualCode(checkedData[i].split("~")[1]);
        if (isApprove && checkedData[i].split("~")[3].startsWith("1")) {
          data.setNumber(checkedData[i].split("~")[2]);
        } else if (!isApprove && checkedData[i].split("~")[3].endsWith("1")) {
          data.setNumber(checkedData[i].split("~")[2]);
        }
        resColl.add(data);
        i++;
      }
    }
    this.itemData=resColl;
  }
  
  public boolean validateDecideAll(HttpServletRequest lRequest){
    SICustomErrors errors = new SICustomErrors();
    if (this.itemData==null||this.itemData.size()<1) {
      errors.addError(new SICustomError("manager.message.freeword","決裁対象データを選択してください"));
    }else{
      Iterator itemIte = itemData.iterator();
      while(itemIte.hasNext()) {
        UIStockHistory data = (UIStockHistory) itemIte.next();
        if (SIUtil.isNull(data.getCmdtyCode())||SIUtil.isNull(data.getIndividualCode())||SIUtil.isNull(data.getNumber())) {
          errors.addError(new SICustomError("manager.message.freeword","決裁できないデータが選択されていたため処理をキャンセルしました"));
          break;
        }
      }
    }
    
    if (!errors.isEmpty()) lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
    return errors.isEmpty();
  }
  
  /**
   * validate
   * 入力したデータをチェックします。
   * 不正なデータがある場合、エラーをオブジェクトに格納します。
   * そのオブジェクトは、エラーメッセージとして画面に表示されます。
   * @param HttpServletRequest ，Connection , SIURLParameter
   * @return true:エラーがない false:エラーが１つ以上ある
   * @throws なし
   */
  public boolean validate(HttpServletRequest lRequest) {
    SITableConditionManager lConditionMan = new SITableConditionManager();
    SICustomErrors errors = new SICustomErrors();
    StringBuffer lSqlStr = new StringBuffer();
    
    // 在庫コード
    if (SIUtil.isNotNull(this.getIndividualCodeTxt()) && SICheckValid.checkValid(errors, "在庫コード", this.getIndividualCodeTxt(), SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE)) {
      lConditionMan.add(new SITableCondition("s", "IndividualCode", this.getIndividualCodeTxt(), SIConfig.SICONDITION_TYPE_FRONTLIKE, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // 商品名
    if (SIUtil.isNotNull(this.getCmdtyNameTxt()) && SICheckValid.checkValid(errors, "商品名", this.getCmdtyNameTxt(), SICheckDataConf.SICHECK_DATA_CMDTYNAME_TYPE)) {
      lConditionMan.add(new SITableCondition("c", "CmdtyName", this.getCmdtyNameTxt(), SIConfig.SICONDITION_TYPE_LIKE, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // 対象支店
    if (SIUtil.isNotNull(this.getBranchCodeCbo())&&this.isStore) {
      lConditionMan.add(new SITableCondition("s", "StoreBranch", this.getBranchCodeCbo(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }else if (SIUtil.isNotNull(this.getBranchCodeCbo())){
      lConditionMan.add(new SITableCondition("s", "ShipBranch", this.getBranchCodeCbo(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
    }
    
    // 権限チェック
    if (SIUtil.isNotNull(this.getCheckAuthority())&&"1".equals(this.getCheckAuthority())) {
      SILogin manLogin=SIHTMLUtil.getLogin(lRequest);
      lSqlStr = new StringBuffer();
      boolean isSPUSER=false;
      isSPUSER = isSPUSER||this.SPUSER1.equals(manLogin.getUserCode());
      isSPUSER = isSPUSER||this.SPUSER2.equals(manLogin.getUserCode());
      isSPUSER = isSPUSER||this.SPUSER3.equals(manLogin.getUserCode());
      isSPUSER = isSPUSER||this.SPUSER4.equals(manLogin.getUserCode());
      isSPUSER = isSPUSER||this.SPUSER5.equals(manLogin.getUserCode());
      isSPUSER = isSPUSER||this.SPUSER6.equals(manLogin.getUserCode());
      isSPUSER = isSPUSER||this.SPUSER7.equals(manLogin.getUserCode());
      
      if (isSPUSER){
        if (this.isStore) {//自支店対象の入庫
          lSqlStr.append(" AND CASE WHEN s.storeBranch = (SELECT belongingbranchcode FROM chargetbl WHERE chargecode=").append(SIDBUtil.SQL2Str(manLogin.getUserCode(),") THEN true "));
          lSqlStr.append(" ELSE false END ");
          lConditionMan.add(new SITableCondition(lSqlStr.toString()));
        } else {//自支店対象の移動、コード替え、販売保留以外の出庫（承認のみ）
          lSqlStr.append(" AND CASE WHEN s.shipType NOT IN ('3','12','18') AND s.shipBranch = (SELECT belongingbranchcode FROM chargetbl WHERE chargecode=").append(SIDBUtil.SQL2Str(manLogin.getUserCode(),") THEN true "));
          lSqlStr.append(" ELSE false END ");
          lConditionMan.add(new SITableCondition(lSqlStr.toString()));
        }
      } else if ("3".equals(manLogin.getMngCmdtyLevel())){
        if (!this.isStore) {//移動出庫以外
          lConditionMan.add(new SITableCondition(" AND s.shipType <> '3' "));
        }
      } else if ("2".equals(manLogin.getMngCmdtyLevel())){
        if (this.isStore) {//自支店対象の移動、保証戻り（承認のみ）と自支店担当者のコード替え
          lSqlStr.append(" AND CASE WHEN s.storeType IN ('3','13') AND s.storeBranch = (SELECT belongingbranchcode FROM chargetbl WHERE chargecode=").append(SIDBUtil.SQL2Str(manLogin.getUserCode(),") THEN true "));
          lSqlStr.append(" WHEN s.storeType = '12' AND s.storeCharge IN (SELECT a.chargecode FROM chargetbl a,chargetbl b WHERE a.belongingbranchcode=b.belongingbranchcode AND b.chargecode=").append(SIDBUtil.SQL2Str(manLogin.getUserCode(),") THEN true "));
          lSqlStr.append(" ELSE false END ");
          lConditionMan.add(new SITableCondition(lSqlStr.toString()));
        } else {//自支店対象の講習会、サンプル、運送事故、販売保留（却下のみ）と自支店担当者のコード替え、返品、保証対応（却下のみ）
          lSqlStr.append(" AND CASE WHEN s.shipType IN ('14','15','16','18') AND s.shipBranch = (SELECT belongingbranchcode FROM chargetbl WHERE chargecode=").append(SIDBUtil.SQL2Str(manLogin.getUserCode(),") THEN true "));
          lSqlStr.append(" WHEN s.shipType IN ('8','12','13') AND s.shipCharge IN (SELECT a.chargecode FROM chargetbl a,chargetbl b WHERE a.belongingbranchcode=b.belongingbranchcode AND b.chargecode=").append(SIDBUtil.SQL2Str(manLogin.getUserCode(),") THEN true "));
          lSqlStr.append(" ELSE false END ");
          lConditionMan.add(new SITableCondition(lSqlStr.toString()));
        }
      } else if ("1".equals(manLogin.getMngCmdtyLevel())){
        if (this.isStore) {//自支店対象の移動、保証戻り（承認のみ）
          lSqlStr.append(" AND s.storeType IN ('3','13') ");
          lSqlStr.append(" AND s.storeBranch = (SELECT belongingbranchcode FROM chargetbl WHERE chargecode=").append(SIDBUtil.SQL2Str(manLogin.getUserCode(),") "));
          lConditionMan.add(new SITableCondition(lSqlStr.toString()));
        } else {//出庫決裁不可
          lConditionMan.add(new SITableCondition(" AND false "));
        }
      } else {
        lConditionMan.add(new SITableCondition(" AND false "));
      }
    }
    
    //処理対象
    if (this.getStoreTypeChk()!=null&&this.getStoreTypeChk().length>0){
      if (this.isStore) lSqlStr = new StringBuffer(" AND s.storeType IN (");
      else lSqlStr = new StringBuffer(" AND s.shipType IN (");
      for (int i=0;i<this.getStoreTypeChk().length;i++){
        if (i>0) lSqlStr.append(",");
        lSqlStr.append(SIDBUtil.SQL2Str(this.getStoreTypeChk()[i]));
      }
      lSqlStr.append(") ");
      lConditionMan.add(new SITableCondition(lSqlStr.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;
    SIStockHistory lStockHistory = new SIStockHistory();
    StringBuffer lSqlBuf = new StringBuffer();
    StringBuffer lCountBuf = new StringBuffer();
    Collection lResultColl = new ArrayList();
    
    // 基本のSQL
    if (this.isStore) {
      lSqlBuf.append("SELECT s.cmdtycode,s.individualcode,c.cmdtyname,s.initdatetime,br.branchname,ch.chargename,s.amount ");
      lSqlBuf.append(",CASE WHEN s.amount<=1 THEN s.purchaseprice ELSE trunc(s.purchaseprice/s.amount) END AS purchaseprice,s.processingexpence ");
      lSqlBuf.append(",s.storebranch AS branchcode,s.storetype AS type,s.comment,s.storenumber AS number ");
      lSqlBuf.append("FROM storehistorytbl s,branchtbl br,chargetbl ch,cmdtymtbl c ");
      lSqlBuf.append("WHERE s.cmdtycode=c.cmdtycode AND s.storebranch=br.branchcode AND s.storecharge=ch.chargecode ");
      lSqlBuf.append("AND s.delflg=0 AND s.storetype IN ('3','11','12','13') ").append(this.getConditionSQL());
      lSqlBuf.append("ORDER BY s.individualcode,s.storenumber DESC");
      
      lCountBuf.append("SELECT count(*) FROM storehistorytbl s,cmdtymtbl c ");
      lCountBuf.append("WHERE s.cmdtycode=c.cmdtycode AND s.delflg=0 AND s.storetype IN ('3','11','12','13') ");
      lCountBuf.append(this.getConditionSQL());
    } else {
      lSqlBuf.append("SELECT s.cmdtycode,s.individualcode,c.cmdtyname,s.initdatetime,br.branchname,ch.chargename,s.amount ");
      lSqlBuf.append(",CASE WHEN s.amount<=1 THEN s.purchaseprice ELSE trunc(s.purchaseprice/s.amount) END AS purchaseprice,s.processingexpence ");
      lSqlBuf.append(",s.shipbranch AS branchcode,s.shiptype AS type,s.comment,s.shipnumber AS number ");
      lSqlBuf.append("FROM shiphistorytbl s,branchtbl br,chargetbl ch,cmdtymtbl c ");
      lSqlBuf.append("WHERE s.cmdtycode=c.cmdtycode AND s.shipbranch=br.branchcode AND s.shipcharge=ch.chargecode ");
      lSqlBuf.append("AND s.delflg=0 AND s.shiptype IN ('1','3','8','11','12','13','14','15','16','18') ").append(this.getConditionSQL());
      lSqlBuf.append("ORDER BY s.individualcode,s.shipnumber DESC");
      
      lCountBuf.append("SELECT count(*) FROM shiphistorytbl s,cmdtymtbl c ");
      lCountBuf.append("WHERE s.cmdtycode=c.cmdtycode AND s.delflg=0 AND s.shiptype IN ('1','3','8','11','12','13','14','15','16','18') ");
      lCountBuf.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) {
        lStockHistory = new SIStockHistory();
        lStockHistory.setEncode(SIConfig.SIENCODE_NONE);
        lStockHistory.setCmdtyCode(lResultSet.getString("CmdtyCode"));
        lStockHistory.setIndividualCode(lResultSet.getString("IndividualCode"));
        lStockHistory.setCmdtyName(lResultSet.getString("CmdtyName"));
        lStockHistory.setNumber(lResultSet.getString("Number"));
        lStockHistory.setType(lResultSet.getString("Type"));
        lStockHistory.setBranchCode(lResultSet.getString("BranchCode"));
        lStockHistory.setProcessingExpence(lResultSet.getString("ProcessingExpence"));
        lStockHistory.setPurchasePrice(lResultSet.getString("PurchasePrice"));
        lStockHistory.setBranch(lResultSet.getString("BranchName"));
        lStockHistory.setCharge(lResultSet.getString("ChargeName"));
        lStockHistory.setAmount(lResultSet.getString("Amount"));
        lStockHistory.setComment(lResultSet.getString("Comment"));
        lStockHistory.setDelFlg("0");
        lStockHistory.setInitDate(lResultSet.getString("InitDateTime"));
        lResultColl.add(lStockHistory);
        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;
  }
}