package jp.co.sint.beans.mallmgr;

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

import javax.servlet.http.HttpServletRequest;

import jp.co.sint.basic.SICmdty;
import jp.co.sint.config.SIConfig;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.database.SIDeleteRec;
import jp.co.sint.database.SIDuplicateKeyException;
import jp.co.sint.database.SIInsertRec;
import jp.co.sint.database.SIModifyRec;
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 UIRegRecommendItem {
  //ログ用のインスタンスの生成
  private static Category log=Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  private String ctgryGroupTxt = "top";
  
  private String individualCodeTxt = "";
  
  private String individualCodeDel = "";
  
  private String dispOrderTxt = "";
  
  private String[] individualCodeList = new String[0];
  
  private String[] dispOrderList = new String[0];
  
  public UIRegRecommendItem(){}
  
  public UIRegRecommendItem(String ctgryGroup){
    this.setCtgryGroupTxt(ctgryGroup);
  }
  
  public String getCtgryGroupTxt() {
    return ctgryGroupTxt;
  }
  
  public String[] getDispOrderList() {
    return dispOrderList;
  }
  
  public String getDispOrderTxt() {
    return dispOrderTxt;
  }
  
  public String[] getIndividualCodeList() {
    return individualCodeList;
  }
  
  public String getIndividualCodeTxt() {
    return individualCodeTxt;
  }
  
  public String getIndividualCodeDel() {
    return individualCodeDel;
  }
  
  public void setCtgryGroupTxt(String ctgryGroupTxt) {
    if (SIUtil.isNull(ctgryGroupTxt)) ctgryGroupTxt = "top";
    this.ctgryGroupTxt = ctgryGroupTxt;
  }
  
  public void setDispOrderList(String[] dispOrderList) {
    this.dispOrderList = dispOrderList;
  }
  
  public void setDispOrderTxt(String dispOrderTxt) {
    if (SIUtil.isNull(dispOrderTxt)) dispOrderTxt = "";
    this.dispOrderTxt = dispOrderTxt;
  }
  
  public void setIndividualCodeList(String[] individualCodeList) {
    this.individualCodeList = individualCodeList;
  }
  
  public void setIndividualCodeTxt(String individualCodeTxt) {
    if (SIUtil.isNull(individualCodeTxt)) individualCodeTxt = "";
    this.individualCodeTxt = individualCodeTxt;
  }
  
  public void setIndividualCodeDel(String individualCodeDel) {
    if (SIUtil.isNull(individualCodeDel)) individualCodeDel = "";
    this.individualCodeDel = individualCodeDel;
  }
  
  public Collection getCollection(Connection lConnection) {
    Collection res = new ArrayList();
    String ctgryGroup = this.getCtgryGroupTxt();
    
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    StringBuffer lSql = new StringBuffer();
    lSql.append("SELECT unit.cmdtyCode,unit.IndividualCode,unit.cmdtyName,rec.dispOrder,col.colorName ");
    lSql.append(",CASE ");
    lSql.append(" WHEN unit.frontDispFlg='0' THEN 0 ");//非表示
    lSql.append(" WHEN unit.inquiryFlg='1' THEN 3 ");//お問い合わせ
    lSql.append(" WHEN unit.amountFlg='0' THEN 1 ");//カート
    lSql.append(" WHEN unit.amount>0 THEN 1 ");//カート
    lSql.append(" WHEN unit.rsrvenableflg='1' AND (unit.rsrvamount IS NOT NULL AND unit.rsrvamount='0') THEN 4 ");//SOLDOUT
    lSql.append(" WHEN unit.rsrvenableflg='1' THEN 2 ");//予約
    lSql.append(" WHEN unit.usedNewFlg IN('1','7') OR (unit.soldoutdate IS NULL OR unit.soldoutdate + "+SIConfig.DEFAULT_SOLDOUT+" <= CURRENT_DATE) THEN 4 ");//SOLDOUT
    lSql.append(" ELSE 0 END AS situation ");//非表示
    lSql.append(" ,CASE WHEN unit.individualcode IN (SELECT i.individualcode ");
    lSql.append(" FROM individualtbl i,cmdtyctgrymtbl cc,ctgryvw v,ctgrymtbl ct LEFT OUTER JOIN ctgrygrouptbl g ON ct.ctgrycode=g.ctgrycode ");
    lSql.append(" WHERE cc.cmdtycode=i.cmdtycode AND cc.ctgrycode=v.ctgrycode ");
    lSql.append(" AND (v.branch LIKE '%~'||ct.ctgrycode||'~%' OR v.branch LIKE '%~'||ct.ctgrycode OR ct.ctgrycode='/') ");
    lSql.append(" AND g.ctgrygroup='eyelashgarage') THEN 1 ELSE 0 END AS egflg ");
    lSql.append(" ,CASE WHEN unit.individualcode IN (SELECT i.individualcode ");
    lSql.append(" FROM individualtbl i,cmdtyctgrymtbl cc,ctgryvw v,ctgrymtbl ct LEFT OUTER JOIN ctgrygrouptbl g ON ct.ctgrycode=g.ctgrycode ");
    lSql.append(" WHERE cc.cmdtycode=i.cmdtycode AND cc.ctgrycode=v.ctgrycode ");
    lSql.append(" AND (v.branch LIKE '%~'||ct.ctgrycode||'~%' OR v.branch LIKE '%~'||ct.ctgrycode OR ct.ctgrycode='/') ");
    lSql.append(" AND g.ctgrygroup<>'eyelashgarage') THEN 1 ELSE 0 END AS bgflg ");
    lSql.append("FROM cmdtyUnitTbl unit,recommendItemTbl rec,colortbl col ");
    lSql.append("WHERE unit.individualCode=rec.individualCode ");
    lSql.append("AND unit.colorCode=col.colorCode ");
    lSql.append("AND rec.ctgryGroup=").append(SIDBUtil.SQL2Str(ctgryGroup," "));
    lSql.append("ORDER BY rec.dispOrder,unit.InitDateTime ");
    
    log.debug("recommendlistSQL:"+lSql.toString());
    
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      while(lResultSet.next()){
        SICmdty lCmdty = new SICmdty();
        lCmdty.setCmdtyCode(lResultSet.getString("cmdtyCode"));
        lCmdty.setIndividualCode(lResultSet.getString("individualCode"));
        lCmdty.setCmdtyName(lResultSet.getString("cmdtyName"));
        lCmdty.setColorName(lResultSet.getString("colorName"));
        lCmdty.setAdviceDispOrder(lResultSet.getString("dispOrder"));
        lCmdty.setSalesSituationFlg(lResultSet.getString("situation"));
        if ((ctgryGroup.equals("eyelashgarage")||ctgryGroup.equals("eyelashgarage2"))&&"0".equals(lResultSet.getString("egflg"))) lCmdty.setSalesSituationFlg("5");
        if (!ctgryGroup.equals("eyelashgarage")&&!ctgryGroup.equals("eyelashgarage2")&&"0".equals(lResultSet.getString("bgflg"))) lCmdty.setSalesSituationFlg("6");
        res.add(lCmdty);
      }
      
    } catch (Exception e) {
      e.printStackTrace();
      res = new ArrayList();
    }
    return res;
  }
  
  public void initInsert(SIURLParameter lUrlParam) {
    this.setIndividualCodeTxt((String) lUrlParam.getParam("individualCodeTxt"));
    this.setCtgryGroupTxt((String) lUrlParam.getParam("ctgryGroupTxt"));
  }
  
  public void initDelete(SIURLParameter lUrlParam) {
    this.setIndividualCodeDel((String) lUrlParam.getParam("individualCodeDel"));
    this.setCtgryGroupTxt((String) lUrlParam.getParam("ctgryGroupTxt"));
  }
  
  public void initModify(HttpServletRequest lRequest,SIURLParameter lUrlParam) {
    this.setIndividualCodeList(lRequest.getParameterValues("individualCode"));
    this.setCtgryGroupTxt((String) lUrlParam.getParam("ctgryGroupTxt"));
    this.setDispOrderList(lRequest.getParameterValues("dispOrder"));
  }
  
  public boolean validateInsert(HttpServletRequest lRequest,Connection lConnection){
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    SICustomErrors errors = new SICustomErrors();
    StringBuffer lSqlBuf;
    
    SICheckValid.checkValid(errors, "在庫コード", this.getIndividualCodeTxt(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE);
    // EDBTG003-00 elecs-tani mod start
    //SICheckValid.checkValid(errors, "在庫コード", this.getIndividualCodeTxt(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 8);
    SICheckValid.checkValid(errors, "在庫コード", this.getIndividualCodeTxt(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 10);
    // EDBTG003-00 elecs-tani mod end
    
    if (SIUtil.isNotNull(this.getIndividualCodeTxt())) {
      lSqlBuf = new StringBuffer();
      // 重複レコードの存在をチェックします
      lSqlBuf.append("SELECT individualcode FROM recommenditemtbl ");
      lSqlBuf.append("WHERE ctgryGroup=").append(SIDBUtil.SQL2Str(this.getCtgryGroupTxt(), " "));
      lSqlBuf.append("AND individualcode =").append(SIDBUtil.SQL2Str(this.getIndividualCodeTxt(), " "));
      SICheckValid.checkDuplicate(errors, lConnection, "在庫コード", lSqlBuf.toString());
      
      lSqlBuf = new StringBuffer();
      // 在庫コードの存在をチェックします
      lSqlBuf.append("SELECT individualcode FROM individualtbl ");
      lSqlBuf.append("WHERE individualcode =").append(SIDBUtil.SQL2Str(this.getIndividualCodeTxt(), " "));
      SICheckValid.checkExist(errors, lConnection, "在庫コード", lSqlBuf.toString());
    }
    if (!errors.isEmpty())lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY,errors);
    else lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    
    return errors.isEmpty();
  }
  
  public boolean validateModify(HttpServletRequest lRequest,Connection lConnection){
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    SICustomErrors errors = new SICustomErrors();
    
    for (int i=0;i<this.getIndividualCodeList().length;i++) {
      SICheckValid.checkValid(errors, "表示順"+(i+1)+"："+ this.individualCodeList[i], this.getDispOrderList()[i], SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
    }
    
    if (!errors.isEmpty())lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY,errors);
    else lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    
    return errors.isEmpty();
  }
  
  public void insertItem(Connection lConnection) throws SIDBAccessException,SIDuplicateKeyException {
    SIDateTime lDate = new SIDateTime();
    SIInsertRec lRec = new SIInsertRec("RecommendItemTbl");
    lRec.add("individualCode", this.getIndividualCodeTxt());
    lRec.add("ctgryGroup", this.getCtgryGroupTxt());
    lRec.add("dispOrder", "999");
    lRec.add("initDateTime", lDate.getFullDate());
    lRec.execute(lConnection);
    this.setIndividualCodeTxt("");
  }
  
  public void deleteItem(Connection lConnection) throws SIDBAccessException,SIDuplicateKeyException {
    SIDeleteRec lRec = new SIDeleteRec("RecommendItemTbl");
    lRec.addCondition("individualCode", this.getIndividualCodeDel());
    lRec.addCondition("ctgryGroup", this.getCtgryGroupTxt());
    lRec.execute(lConnection);
  }
  
  public void modifyItem(Connection lConnection) throws SIDBAccessException,SIDuplicateKeyException {
    SIDateTime lDate = new SIDateTime();
    for (int i=0;i<this.getIndividualCodeList().length;i++){
      if (Integer.parseInt(this.getDispOrderList()[i]) > 0) {
        SIModifyRec lRec = new SIModifyRec("RecommendItemTbl");
        lRec.addCondition("individualCode", this.getIndividualCodeList()[i]);
        lRec.addCondition("ctgryGroup", this.getCtgryGroupTxt());
        lRec.add("dispOrder", this.getDispOrderList()[i]);
        lRec.add("initDateTime", lDate.getFullDate());
        lRec.execute(lConnection);
      } else {
        SIDeleteRec lRec = new SIDeleteRec("RecommendItemTbl");
        lRec.addCondition("individualCode", this.getIndividualCodeList()[i]);
        lRec.addCondition("ctgryGroup", this.getCtgryGroupTxt());
        lRec.execute(lConnection);
      }
    }
  }
}