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.SICustomError;
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 UIRegClosedSaleItem {
  //ログ用のインスタンスの生成
  private static Category log=Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  private String saleIndex = "";
  
  private String individualCodeTxt = "";
  
  private String individualCode1Txt = "";
  
  private String individualCode2Txt = "";
  
  private String individualCode3Txt = "";
  
  private String individualCodeDel = "";
  
  private String dispOrderTxt = "";
  
  private String startDateTimeYearTxt = "";
  
  private String startDateTimeMonthTxt = "";
  
  private String startDateTimeDayTxt = "";
  
  private String startDateTimeHourTxt = "";
  
  private String endDateTimeYearTxt = "";
  
  private String endDateTimeMonthTxt = "";
  
  private String endDateTimeDayTxt = "";
  
  private String endDateTimeHourTxt = "";
  
  private String sale1Name = "";
  
  private String sale2Name = "";
  
  private String sale3Name = "";
  
  private String targetFlgTxt = "";
  
  private String[] individualCodeList1 = new String[0];
  
  private String[] individualCodeList2 = new String[0];
  
  private String[] individualCodeList3 = new String[0];
  
  private String[] dispOrderList1 = new String[0];
  
  private String[] dispOrderList2 = new String[0];
  
  private String[] dispOrderList3 = new String[0];
  
  private String openFlg = "0";
  
  public UIRegClosedSaleItem(){}
  
  public void reset(Connection connection){
    SIDateTime lDate = new SIDateTime();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    StringBuffer lSql = new StringBuffer();
    lSql.append("SELECT to_char(startdatetime,'yyyy/MM/dd HH24:mm:ss') AS startdatetime ");
    lSql.append(",to_char(enddatetime,'yyyy/MM/dd HH24:mm:ss') AS enddatetime ");
    lSql.append(",sale1name,sale2name,sale3name,targetflg ");
    lSql.append(",CASE WHEN startdatetime < current_timestamp AND current_timestamp < enddatetime THEN 1 ELSE 0 END AS openflg ");
    lSql.append("FROM closedsaletbl WHERE salecode='1'");
    
    try{
      lStatement = connection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      
      if (lResultSet.next()) {
        String startDateTime = lResultSet.getString("startdatetime");
        if (SIUtil.isNotNull(startDateTime)){
          lDate = new SIDateTime(startDateTime);
          this.setStartDateTimeYearTxt(lDate.getYearStr());
          this.setStartDateTimeMonthTxt(lDate.getMonthStr());
          this.setStartDateTimeDayTxt(lDate.getDayStr());
          this.setStartDateTimeHourTxt(lDate.getHourStr());
        }
        String endDateTime = lResultSet.getString("enddatetime");
        if (SIUtil.isNotNull(endDateTime)){
          lDate = new SIDateTime(endDateTime);
          this.setEndDateTimeYearTxt(lDate.getYearStr());
          this.setEndDateTimeMonthTxt(lDate.getMonthStr());
          this.setEndDateTimeDayTxt(lDate.getDayStr());
          this.setEndDateTimeHourTxt(lDate.getHourStr());
        }
        this.setSale1Name(lResultSet.getString("sale1Name"));
        this.setSale2Name(lResultSet.getString("sale2Name"));
        this.setSale3Name(lResultSet.getString("sale3Name"));
        this.setTargetFlgTxt(lResultSet.getString("targetFlg"));
        this.setOpenFlg(lResultSet.getString("openFlg"));
      }
    }catch(Exception e){
      e.printStackTrace();
    }finally{
      SIDBUtil.close(lResultSet, lStatement);
    }
  }
  
  public String getSaleIndex() {
    return saleIndex;
  }
  
  public String[] getDispOrderList1() {
    return dispOrderList1;
  }
  
  public String[] getDispOrderList2() {
    return dispOrderList2;
  }
  
  public String[] getDispOrderList3() {
    return dispOrderList3;
  }
  
  public String getDispOrderTxt() {
    return dispOrderTxt;
  }
  
  public String[] getIndividualCodeList1() {
    return individualCodeList1;
  }
  
  public String[] getIndividualCodeList2() {
    return individualCodeList2;
  }
  
  public String[] getIndividualCodeList3() {
    return individualCodeList3;
  }
  
  public String getIndividualCodeTxt() {
    return individualCodeTxt;
  }
  
  public String getIndividualCode1Txt() {
    return individualCode1Txt;
  }
  
  public String getIndividualCode2Txt() {
    return individualCode2Txt;
  }
  
  public String getIndividualCode3Txt() {
    return individualCode3Txt;
  }
  
  public String getIndividualCodeDel() {
    return individualCodeDel;
  }
  
  public String getStartDateTimeYearTxt() {
    return startDateTimeYearTxt;
  }
  
  public String getStartDateTimeMonthTxt() {
    return startDateTimeMonthTxt;
  }
  
  public String getStartDateTimeDayTxt() {
    return startDateTimeDayTxt;
  }
  
  public String getStartDateTimeHourTxt() {
    return startDateTimeHourTxt;
  }
  
  public String getStartDateTime() {
    String startTimeStamp = "";
    if (SIUtil.isNotNull(startDateTimeYearTxt)||SIUtil.isNotNull(startDateTimeMonthTxt)||SIUtil.isNotNull(startDateTimeDayTxt)||SIUtil.isNotNull(startDateTimeHourTxt)) {
      startTimeStamp = startDateTimeYearTxt + "/" + startDateTimeMonthTxt + "/" + startDateTimeDayTxt + " " + startDateTimeHourTxt + ":00:00";
    }
    return startTimeStamp;
  }
  
  public String getEndDateTimeYearTxt() {
    return endDateTimeYearTxt;
  }
  
  public String getEndDateTimeMonthTxt() {
    return endDateTimeMonthTxt;
  }
  
  public String getEndDateTimeDayTxt() {
    return endDateTimeDayTxt;
  }
  
  public String getEndDateTimeHourTxt() {
    return endDateTimeHourTxt;
  }
  
  public String getEndDateTime() {
    String endTimeStamp = "";
    if (SIUtil.isNotNull(endDateTimeYearTxt)||SIUtil.isNotNull(endDateTimeMonthTxt)||SIUtil.isNotNull(endDateTimeDayTxt)||SIUtil.isNotNull(endDateTimeHourTxt)) {
      endTimeStamp = endDateTimeYearTxt + "/" + endDateTimeMonthTxt + "/" + endDateTimeDayTxt + " " + endDateTimeHourTxt + ":00:00";
    }
    return endTimeStamp;
  }
  
  public String getSale1Name() {
    return sale1Name;
  }
  
  public String getSale2Name() {
    return sale2Name;
  }
  
  public String getSale3Name() {
    return sale3Name;
  }
  
  public String getTargetFlgTxt() {
    return targetFlgTxt;
  }
  
  public String getOpenFlg() {
    return openFlg;
  }
  
  public void setSaleIndex(String saleIndex) {
    if (SIUtil.isNull(saleIndex)) saleIndex = "top";
    this.saleIndex = saleIndex;
  }
  
  public void setDispOrderList1(String[] dispOrderList1) {
    this.dispOrderList1 = dispOrderList1;
  }
  
  public void setDispOrderList2(String[] dispOrderList2) {
    this.dispOrderList2 = dispOrderList2;
  }
  
  public void setDispOrderList3(String[] dispOrderList3) {
    this.dispOrderList3 = dispOrderList3;
  }
  
  public void setDispOrderTxt(String dispOrderTxt) {
    if (SIUtil.isNull(dispOrderTxt)) dispOrderTxt = "";
    this.dispOrderTxt = dispOrderTxt;
  }
  
  public void setIndividualCodeList1(String[] individualCodeList1) {
    this.individualCodeList1 = individualCodeList1;
  }
  
  public void setIndividualCodeList2(String[] individualCodeList2) {
    this.individualCodeList2 = individualCodeList2;
  }
  
  public void setIndividualCodeList3(String[] individualCodeList3) {
    this.individualCodeList3 = individualCodeList3;
  }
  
  public void setIndividualCodeTxt(String individualCodeTxt) {
    if (SIUtil.isNull(individualCodeTxt)) individualCodeTxt = "";
    this.individualCodeTxt = individualCodeTxt;
  }
  
  public void setIndividualCode1Txt(String individualCode1Txt) {
    if (SIUtil.isNull(individualCode1Txt)) individualCode1Txt = "";
    this.individualCode1Txt = individualCode1Txt;
  }
  
  public void setIndividualCode2Txt(String individualCode2Txt) {
    if (SIUtil.isNull(individualCode2Txt)) individualCode2Txt = "";
    this.individualCode2Txt = individualCode2Txt;
  }
  
  public void setIndividualCode3Txt(String individualCode3Txt) {
    if (SIUtil.isNull(individualCode3Txt)) individualCode3Txt = "";
    this.individualCode3Txt = individualCode3Txt;
  }
  
  public void setIndividualCodeDel(String individualCodeDel) {
    if (SIUtil.isNull(individualCodeDel)) individualCodeDel = "";
    this.individualCodeDel = individualCodeDel;
  }
  
  public void setStartDateTimeYearTxt(String startDateTimeYearTxt) {
    if (SIUtil.isNull(startDateTimeYearTxt))
      startDateTimeYearTxt = "";
    this.startDateTimeYearTxt = startDateTimeYearTxt;
  }
  
  public void setStartDateTimeMonthTxt(String startDateTimeMonthTxt) {
    if (SIUtil.isNull(startDateTimeMonthTxt))
      startDateTimeMonthTxt = "";
    this.startDateTimeMonthTxt = startDateTimeMonthTxt;
  }
  
  public void setStartDateTimeDayTxt(String startDateTimeDayTxt) {
    if (SIUtil.isNull(startDateTimeDayTxt))
      startDateTimeDayTxt = "";
    this.startDateTimeDayTxt = startDateTimeDayTxt;
  }
  
  public void setStartDateTimeHourTxt(String startDateTimeHourTxt) {
    if (SIUtil.isNull(startDateTimeHourTxt))
      startDateTimeHourTxt = "";
    this.startDateTimeHourTxt = startDateTimeHourTxt;
  }
  
  public void setEndDateTimeYearTxt(String endDateTimeYearTxt) {
    if (SIUtil.isNull(endDateTimeYearTxt))
      endDateTimeYearTxt = "";
    this.endDateTimeYearTxt = endDateTimeYearTxt;
  }
  
  public void setEndDateTimeMonthTxt(String endDateTimeMonthTxt) {
    if (SIUtil.isNull(endDateTimeMonthTxt))
      endDateTimeMonthTxt = "";
    this.endDateTimeMonthTxt = endDateTimeMonthTxt;
  }
  
  public void setEndDateTimeDayTxt(String endDateTimeDayTxt) {
    if (SIUtil.isNull(endDateTimeDayTxt))
      endDateTimeDayTxt = "";
    this.endDateTimeDayTxt = endDateTimeDayTxt;
  }
  
  public void setEndDateTimeHourTxt(String endDateTimeHourTxt) {
    if (SIUtil.isNull(endDateTimeHourTxt))
      endDateTimeHourTxt = "";
    this.endDateTimeHourTxt = endDateTimeHourTxt;
  }
  
  public void setSale1Name(String sale1Name) {
    if (SIUtil.isNull(sale1Name)) sale1Name = "";
    this.sale1Name = sale1Name;
  }
  
  public void setSale2Name(String sale2Name) {
    if (SIUtil.isNull(sale2Name)) sale2Name = "";
    this.sale2Name = sale2Name;
  }
  
  public void setSale3Name(String sale3Name) {
    if (SIUtil.isNull(sale3Name)) sale3Name = "";
    this.sale3Name = sale3Name;
  }
  
  public void setTargetFlgTxt(String targetFlgTxt) {
    if (SIUtil.isNull(targetFlgTxt)) targetFlgTxt = "";
    this.targetFlgTxt = targetFlgTxt;
  }
  
  public void setOpenFlg(String openFlg) {
    if (SIUtil.isNull(openFlg)) openFlg = "0";
    this.openFlg = openFlg;
  }
  
  public Collection getCollection(Connection lConnection,String saleList) {
    Collection res = new ArrayList();
    
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    StringBuffer lSql = new StringBuffer();
    lSql.append("SELECT unit.cmdtyCode,unit.IndividualCode,unit.cmdtyName,sel.dispOrder,col.colorName,unit.amountFlg,unit.amount ");
    lSql.append(",CASE ");
    lSql.append(" WHEN unit.frontDispFlg='1' THEN 0 ");//非表示
    lSql.append(" WHEN unit.amountFlg='0' OR unit.amount>0 THEN 1 ");//カート
    lSql.append(" ELSE 2 END AS situation ");//SOLD OUT
    lSql.append("FROM cmdtyUnitTbl unit,closedSaleItemTbl sel,colortbl col ");
    lSql.append("WHERE unit.individualCode=sel.individualCode ");
    lSql.append("AND unit.colorCode=col.colorCode ");
    lSql.append("AND sel.saleBranchCode=").append(SIDBUtil.SQL2Str(saleList," "));
    lSql.append("ORDER BY sel.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"));
        lCmdty.setAmountFlg(lResultSet.getString("amountFlg"));
        lCmdty.setAmount(lResultSet.getString("amount"));
        res.add(lCmdty);
      }
      
    } catch (Exception e) {
      e.printStackTrace();
      res = new ArrayList();
    }
    return res;
  }
  public void init(HttpServletRequest lRequest,SIURLParameter lUrlParam) {
    this.setSaleIndex((String) lUrlParam.getParam("saleIndex"));
    this.setStartDateTimeYearTxt((String) lUrlParam.getParam("startDateTimeYearTxt"));
    this.setStartDateTimeMonthTxt((String) lUrlParam.getParam("startDateTimeMonthTxt"));
    this.setStartDateTimeDayTxt((String) lUrlParam.getParam("startDateTimeDayTxt"));
    this.setStartDateTimeHourTxt((String) lUrlParam.getParam("startDateTimeHourTxt"));
    this.setEndDateTimeYearTxt((String) lUrlParam.getParam("endDateTimeYearTxt"));
    this.setEndDateTimeMonthTxt((String) lUrlParam.getParam("endDateTimeMonthTxt"));
    this.setEndDateTimeDayTxt((String) lUrlParam.getParam("endDateTimeDayTxt"));
    this.setEndDateTimeHourTxt((String) lUrlParam.getParam("endDateTimeHourTxt"));
    this.setTargetFlgTxt((String) lUrlParam.getParam("targetFlgTxt"));
    this.setSale1Name((String) lUrlParam.getParam("sale1Name"));
    this.setSale2Name((String) lUrlParam.getParam("sale2Name"));
    this.setSale3Name((String) lUrlParam.getParam("sale3Name"));
    this.setIndividualCode1Txt((String) lUrlParam.getParam("individualCode1Txt"));
    this.setIndividualCode2Txt((String) lUrlParam.getParam("individualCode2Txt"));
    this.setIndividualCode3Txt((String) lUrlParam.getParam("individualCode3Txt"));
    if (SIUtil.isNotNull(this.getSaleIndex())&&"1".equals(this.saleIndex)) {
      this.setIndividualCodeTxt((String) lUrlParam.getParam("individualCode1Txt"));
    } else if (SIUtil.isNotNull(this.getSaleIndex())&&"2".equals(this.saleIndex)) {
      this.setIndividualCodeTxt((String) lUrlParam.getParam("individualCode2Txt"));
    } else if (SIUtil.isNotNull(this.getSaleIndex())&&"3".equals(this.saleIndex)) {
      this.setIndividualCodeTxt((String) lUrlParam.getParam("individualCode3Txt"));
    }
    this.setIndividualCodeList1(lRequest.getParameterValues("individualCode1"));
    this.setIndividualCodeList2(lRequest.getParameterValues("individualCode2"));
    this.setIndividualCodeList3(lRequest.getParameterValues("individualCode3"));
    this.setDispOrderList1(lRequest.getParameterValues("dispOrder1"));
    this.setDispOrderList2(lRequest.getParameterValues("dispOrder2"));
    this.setDispOrderList3(lRequest.getParameterValues("dispOrder3"));
    this.setIndividualCodeDel((String) lUrlParam.getParam("individualCodeDel"));
    this.setOpenFlg((String) lUrlParam.getParam("openFlg"));
  }
  
  public boolean validateAdd(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 nagayoshi add 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 nagayoshi add end
    if (SIUtil.isNotNull(this.getIndividualCodeTxt())) {
      lSqlBuf = new StringBuffer();
      // 重複レコードの存在をチェックします
      lSqlBuf.append("SELECT individualcode FROM ClosedSaleItemTbl ");
      lSqlBuf.append("WHERE saleBranchCode=").append(SIDBUtil.SQL2Str(this.getSaleIndex(), " "));
      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) {
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    SICustomErrors errors = new SICustomErrors();
    if (SIUtil.isNotNull(this.getSaleIndex())&&this.getSaleIndex().equals("0")) {
      if (this.getOpenFlg().equals("1")) {
        SICheckValid.checkValid(errors, "終了日時", this.getEndDateTime(), SICheckDataConf.SICHECK_DATA_DATE_TIME_TYPE + SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
        SICheckValid.checkDateTimeGreaterValid(errors, "終了日時", "現在時刻", this.getEndDateTime(), new SIDateTime().getFullDateTime());
      } else {
        SICheckValid.checkValid(errors, "開催日時", this.getStartDateTime(), SICheckDataConf.SICHECK_DATA_DATE_TIME_TYPE);
        SICheckValid.checkDateTimeGreaterValid(errors, "開催日時", "現在時刻", this.getStartDateTime(), new SIDateTime().getFullDateTime());
        SICheckValid.checkValid(errors, "終了日時", this.getEndDateTime(), SICheckDataConf.SICHECK_DATA_DATE_TIME_TYPE);
        SICheckValid.checkDateTimeGreaterValid(errors, "終了日時", "開催日時", this.getEndDateTime(), this.getStartDateTime());
      }
    } else if (SIUtil.isNotNull(this.getSaleIndex())&&this.getSaleIndex().equals("1")) {
      SICheckValid.checkValid(errors, "セール１タイトル", this.getSale1Name(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 128);
    } else if (SIUtil.isNotNull(this.getSaleIndex())&&this.getSaleIndex().equals("2")) {
      SICheckValid.checkValid(errors, "セール２タイトル", this.getSale2Name(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 128);
    } else if (SIUtil.isNotNull(this.getSaleIndex())&&this.getSaleIndex().equals("3")) {
      SICheckValid.checkValid(errors, "セール３タイトル", this.getSale3Name(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 128);
    } else {
      errors.addError(new SICustomError("database.execute.error"));
    }
    
    if (!errors.isEmpty())lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY,errors);
    else lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    
    return errors.isEmpty();
  }
  public boolean validateRefresh(HttpServletRequest lRequest,Connection lConnection){
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    SICustomErrors errors = new SICustomErrors();
    
    if (this.getIndividualCodeList1()!=null && this.getIndividualCodeList1().length>0) {
      for (int i=0;i<this.getIndividualCodeList1().length;i++) {
        SICheckValid.checkValid(errors, "表示順"+(i+1)+"："+ this.individualCodeList1[i], this.getDispOrderList1()[i], SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
      }
    }
    
    if (this.getIndividualCodeList2()!=null && this.getIndividualCodeList2().length>0) {
      for (int j=0;j<this.getIndividualCodeList2().length;j++) {
        SICheckValid.checkValid(errors, "表示順"+(j+1)+"："+ this.individualCodeList2[j], this.getDispOrderList2()[j], SICheckDataConf.SICHECK_DATA_EMPTY_TYPE + SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
      }
    }
    
    if (this.getIndividualCodeList3()!=null && this.getIndividualCodeList3().length>0) {
      for (int k=0;k<this.getIndividualCodeList3().length;k++) {
        SICheckValid.checkValid(errors, "表示順"+(k+1)+"："+ this.individualCodeList3[k], this.getDispOrderList3()[k], 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 executeAdd(Connection lConnection) throws SIDBAccessException,SIDuplicateKeyException {
    SIInsertRec lRec = new SIInsertRec("ClosedSaleItemTbl");
    lRec.add("saleCode", "1");
    lRec.add("saleBranchCode", this.getSaleIndex());
    lRec.add("individualCode", this.getIndividualCodeTxt());
    lRec.add("dispOrder", "999");
    lRec.execute(lConnection);
  }
  
  public void executeDelete(Connection lConnection) throws SIDBAccessException,SIDuplicateKeyException {
    SIDeleteRec lRec = new SIDeleteRec("ClosedSaleItemTbl");
    lRec.addCondition("saleBranchCode", this.getSaleIndex());
    lRec.addCondition("individualCode", this.getIndividualCodeDel());
    lRec.execute(lConnection);
  }
  
  public void executeModify(Connection lConnection) throws SIDBAccessException,SIDuplicateKeyException {
    SIModifyRec lRec = new SIModifyRec("ClosedSaleTbl");
    lRec.addCondition("saleCode", "1");
    if (saleIndex.equals("0")) {
      if (openFlg.equals("0")) {
        lRec.add("startDateTime", this.getStartDateTime());
      }
      lRec.add("endDateTime", this.getEndDateTime());
      lRec.add("targetFlg", this.getTargetFlgTxt());
    } else if (saleIndex.equals("1")) {
      lRec.add("sale1Name", this.getSale1Name());
    } else if (saleIndex.equals("2")) {
      lRec.add("sale2Name", this.getSale2Name());
    } else if (saleIndex.equals("3")) {
      lRec.add("sale3Name", this.getSale3Name());
    }
    lRec.execute(lConnection);
  }
  
  public void executeClear(Connection lConnection) throws SIDBAccessException,SIDuplicateKeyException {
    SIModifyRec lRec = new SIModifyRec("ClosedSaleTbl");
    lRec.addCondition("saleCode", "1");
    lRec.add("startDateTime", "");
    lRec.add("endDateTime", "");
    lRec.add("targetFlg", "");
    lRec.execute(lConnection);
  }
  
  public void executeReset(Connection lConnection) throws SIDBAccessException,SIDuplicateKeyException {
    SIModifyRec lRec1 = new SIModifyRec("ClosedSaleTbl");
    lRec1.addCondition("saleCode", "1");
    lRec1.add("startDateTime", "");
    lRec1.add("endDateTime", "");
    lRec1.add("targetFlg", "");
    lRec1.add("sale1Name", "セール1名称");
    lRec1.add("sale2Name", "セール2名称");
    lRec1.add("sale3Name", "セール3名称");
    lRec1.execute(lConnection);
    
    SIDeleteRec lRec2 = new SIDeleteRec("ClosedSaleItemTbl");
    lRec2.addCondition("saleCode", "1");
    lRec2.execute(lConnection);
  }
  
  public void executeRefresh(Connection lConnection) throws SIDBAccessException,SIDuplicateKeyException {
    if (this.getIndividualCodeList1()!=null && this.getIndividualCodeList1().length>0) {
      for (int i=0;i<this.getIndividualCodeList1().length;i++){
        if (Integer.parseInt(this.getDispOrderList1()[i]) > 0) {
          SIModifyRec lRec = new SIModifyRec("ClosedSaleItemTbl");
          lRec.addCondition("individualCode", this.getIndividualCodeList1()[i]);
          lRec.addCondition("saleBranchCode", "1");
          lRec.add("dispOrder", this.getDispOrderList1()[i]);
          lRec.execute(lConnection);
        } else {
          SIDeleteRec lRec = new SIDeleteRec("ClosedSaleItemTbl");
          lRec.addCondition("individualCode", this.getIndividualCodeList1()[i]);
          lRec.addCondition("saleBranchCode", "1");
          lRec.execute(lConnection);
        }
      }
    }
    
    if (this.getIndividualCodeList2()!=null && this.getIndividualCodeList2().length>0) {
      for (int j=0;j<this.getIndividualCodeList2().length;j++){
        if (Integer.parseInt(this.getDispOrderList2()[j]) > 0) {
          SIModifyRec lRec = new SIModifyRec("ClosedSaleItemTbl");
          lRec.addCondition("individualCode", this.getIndividualCodeList2()[j]);
          lRec.addCondition("saleBranchCode", "2");
          lRec.add("dispOrder", this.getDispOrderList2()[j]);
          lRec.execute(lConnection);
        } else {
          SIDeleteRec lRec = new SIDeleteRec("ClosedSaleItemTbl");
          lRec.addCondition("individualCode", this.getIndividualCodeList2()[j]);
          lRec.addCondition("saleBranchCode", "2");
          lRec.execute(lConnection);
        }
      }
    }
    
    if (this.getIndividualCodeList3()!=null && this.getIndividualCodeList3().length>0) {
      for (int k=0;k<this.getIndividualCodeList3().length;k++){
        if (Integer.parseInt(this.getDispOrderList3()[k]) > 0) {
          SIModifyRec lRec = new SIModifyRec("ClosedSaleItemTbl");
          lRec.addCondition("individualCode", this.getIndividualCodeList3()[k]);
          lRec.addCondition("saleBranchCode", "3");
          lRec.add("dispOrder", this.getDispOrderList3()[k]);
          lRec.execute(lConnection);
        } else {
          SIDeleteRec lRec = new SIDeleteRec("ClosedSaleItemTbl");
          lRec.addCondition("individualCode", this.getIndividualCodeList3()[k]);
          lRec.addCondition("saleBranchCode", "3");
          lRec.execute(lConnection);
        }
      }
    }
  }
  
  public static boolean isDispEnable(Connection lConnection,String individualCode,String memberLevel) {
    boolean res = false;
    if (SIUtil.isNull(memberLevel)) return res;
    try {
      StringBuffer lSql = new StringBuffer();
      lSql.append("SELECT a.targetFlg ");
      lSql.append("FROM ClosedSaleTbl a,ClosedSaleItemTbl b,IndividualTbl i ");
      lSql.append("WHERE a.saleCode=b.saleCode AND a.saleCode='1' ");//セール対象商品
      lSql.append("AND b.individualCode=i.individualCode AND i.frontDispFlg='0' ");//フロント非表示
      lSql.append("AND a.startDateTime < current_timestamp AND a.endDateTime > current_timestamp ");//セール中
      lSql.append("AND b.individualCode=").append(SIDBUtil.SQL2Str(individualCode));
      
      String targetFlg = SIDBUtil.getFirstData(lConnection, lSql.toString());
      
      if (SIUtil.isNull(targetFlg)) res = false;
      else if ("0".equals(targetFlg)&&"1".equals(memberLevel)) res = true;//ゴールドのみ
      else if ("1".equals(targetFlg)&&"2".equals(memberLevel)) res = true;//プラチナのみ
      else if ("2".equals(targetFlg)&&("1".equals(memberLevel)||"2".equals(memberLevel))) res = true;//ゴールドかプラチナ
      else res = false;
    } catch (Exception e) {
      e.printStackTrace();
      res = false;
    }
    return res;
  }
  
  public static String isDisp(Connection lConnection,String memberLevel) {
    String res = "2";
    if (SIUtil.isNull(memberLevel)) return res;
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    StringBuffer lSql = new StringBuffer();
    lSql.append("SELECT targetFlg ");
    lSql.append(",CASE WHEN startDateTime < current_timestamp AND endDateTime > current_timestamp THEN 1 ");
    lSql.append(" WHEN startDateTime IS NULL OR endDateTime IS NULL OR startDateTime > current_timestamp THEN 2 ");
    lSql.append(" WHEN endDateTime < current_timestamp THEN 3 ELSE 2 END AS saleFlg ");
    lSql.append("FROM ClosedSaleTbl WHERE saleCode='1' ");
    
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      
      if (lResultSet.next()) {
        String targetFlg = lResultSet.getString("targetFlg");
        String saleFlg = lResultSet.getString("saleFlg");
        if (SIUtil.isNull(targetFlg)) return "0";
        else if ("0".equals(targetFlg)&&"1".equals(memberLevel)) res = "1";//ゴールドのみ
        else if ("1".equals(targetFlg)&&"2".equals(memberLevel)) res = "1";//プラチナのみ
        else if ("2".equals(targetFlg)&&("1".equals(memberLevel)||"2".equals(memberLevel))) res = "1";//ゴールドかプラチナ
        else return "0";
        
        res = saleFlg;
        
      }
    } catch (Exception e) {
      e.printStackTrace();
      res = "2";
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    
    return res;
  }
  
  public static boolean isEnd(Connection lConnection) {
    boolean res = false;
    
    StringBuffer lSql = new StringBuffer();
    lSql.append("SELECT CASE WHEN startDateTime < current_timestamp AND endDateTime > current_timestamp THEN 1 ");
    lSql.append(" WHEN startDateTime IS NULL OR endDateTime IS NULL OR startDateTime > current_timestamp THEN 2 ");
    lSql.append(" WHEN startDateTime > current_timestamp + '3 days' THEN 3 ");
    lSql.append(" WHEN endDateTime < current_timestamp THEN 3 ELSE 2 END AS saleFlg ");
    lSql.append("FROM ClosedSaleTbl WHERE saleCode='1' ");
    
    try {
      String data = SIDBUtil.getFirstData(lConnection, lSql.toString());
      if (SIUtil.isNull(data)||"3".equals(data)) res = true;
    } catch (Exception e) {
      e.printStackTrace();
    }
    
    return res;
  }
}