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.SIHacchuPayment;
import jp.co.sint.basic.SILogin;
import jp.co.sint.basic.SINameValue;
import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIFlagConf;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.tools.SICheckDataConf;
import jp.co.sint.tools.SICheckUtil;
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.SIFlagUtil;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIUtil;
import jp.co.sint.tools.SIURLParameter;

public class UIRegHacchuPayment extends SIHacchuPayment {
  
  private String paymentPriceTxt = "";
  
  private String paymentDateYearCbo = "";
  
  private String paymentDateMonthCbo = "";
  
  private String paymentDateDayCbo = "";
  
  private String paymentDateCSV = "";
  
  private String paymentMemoTxt = "";
  
  private String count = "0";
  
  public UIRegHacchuPayment(){
    SIDateTime lDate = new SIDateTime();
    this.setPaymentDateYearCbo(lDate.getYearStr());
    this.setPaymentDateMonthCbo(lDate.getMonthStr());
    this.setPaymentDateDayCbo(lDate.getDayStr());
  }
  
  public UIRegHacchuPayment(String lStockCode) {
    SIDateTime lDate = new SIDateTime();
    this.setPaymentDateYearCbo(lDate.getYearStr());
    this.setPaymentDateMonthCbo(lDate.getMonthStr());
    this.setPaymentDateDayCbo(lDate.getDayStr());
    this.setStockCode(lStockCode);
  }
  
  public String getCount() {
    return count;
  }
  
  public String getPaymentDateDayCbo() {
    return paymentDateDayCbo;
  }
  
  public String getPaymentDateMonthCbo() {
    return paymentDateMonthCbo;
  }
  
  public String getPaymentDateYearCbo() {
    return paymentDateYearCbo;
  }
  
  public String getPaymentDate() {
    return SIDateTime.getDate(getPaymentDateYearCbo(), getPaymentDateMonthCbo(), getPaymentDateDayCbo());
  }
  
  public String getPaymentDateCSV() {
    return paymentDateCSV;
  }
  
  public String getPaymentMemoTxt() {
    return paymentMemoTxt;
  }
  
  public String getPaymentPriceTxt() {
    return paymentPriceTxt;
  }
  
  public void setCount(String count) {
    if (SIUtil.isNull(count)) count = "0";
    this.count = count;
  }
  
  public void setPaymentDateDayCbo(String paymentDateDayCbo) {
    if (SIUtil.isNull(paymentDateDayCbo)) paymentDateDayCbo = "";
    this.paymentDateDayCbo = paymentDateDayCbo;
  }
  
  public void setPaymentDateMonthCbo(String paymentDateMonthCbo) {
    if (SIUtil.isNull(paymentDateMonthCbo)) paymentDateMonthCbo = "";
    this.paymentDateMonthCbo = paymentDateMonthCbo;
  }
  
  public void setPaymentDateYearCbo(String paymentDateYearCbo) {
    if (SIUtil.isNull(paymentDateYearCbo)) paymentDateYearCbo = "";
    this.paymentDateYearCbo = paymentDateYearCbo;
  }
  
  public void setPaymentDateCSV(String paymentDateCSV) {
    if (SIUtil.isNull(paymentDateCSV)) paymentDateCSV = "";
    this.paymentDateCSV = paymentDateCSV;
  }
  
  public void setPaymentMemoTxt(String paymentMemoTxt) {
    if (SIUtil.isNull(paymentMemoTxt)) paymentMemoTxt = "";
    this.paymentMemoTxt = paymentMemoTxt;
  }
  
  public void setPaymentPriceTxt(String paymentPriceTxt) {
    if (SIUtil.isNull(paymentPriceTxt)) paymentPriceTxt = "";
    this.paymentPriceTxt = paymentPriceTxt;
  }
  
  public void init(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    super.init(lRequest, lUrlParam);
    this.setPaymentPriceTxt((String)lUrlParam.getParam("paymentPriceTxt"));
    this.setPaymentDateYearCbo((String)lUrlParam.getParam("paymentDateYearCbo"));
    this.setPaymentDateMonthCbo((String)lUrlParam.getParam("paymentDateMonthCbo"));
    this.setPaymentDateDayCbo((String)lUrlParam.getParam("paymentDateDayCbo"));
    this.setPaymentMemoTxt((String)lUrlParam.getParam("paymentMemoTxt"));
  }
  
  public void initDelete(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    super.init(lRequest, lUrlParam);
    this.setPaymentPriceTxt((String)lUrlParam.getParam("paymentPriceTxt"));
    this.setPaymentDateYearCbo((String)lUrlParam.getParam("paymentDateYearCbo"));
    this.setPaymentDateMonthCbo((String)lUrlParam.getParam("paymentDateMonthCbo"));
    this.setPaymentDateDayCbo((String)lUrlParam.getParam("paymentDateDayCbo"));
    this.setPaymentMemoTxt((String)lUrlParam.getParam("paymentMemoTxt"));
    this.setPaymentCode((String)lUrlParam.getParam("paymentCode"));
  }
  
  public boolean validateInsert(HttpServletRequest lRequest,Connection lConnection) {
    return validateInsert(lRequest,lConnection,false);
  }
  
  public boolean validateInsertCSV(HttpServletRequest lRequest,Connection lConnection) {
    return validateInsert(lRequest,lConnection,true);
  }
  
  private boolean validateInsert(HttpServletRequest lRequest,Connection lConnection,boolean isCSV) {
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    SICustomErrors errors = new SICustomErrors();
    String date = "";
    if (isCSV) date = getPaymentDateCSV();
    else date = getPaymentDate();
    SICheckValid.checkValid(errors, "支払日", date, SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
    
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append("SELECT initdate FROM hacchurecordtbl ");
    lSqlBuf.append("WHERE stockcode=").append(SIDBUtil.SQL2Str(getStockCode()," "));
    lSqlBuf.append("AND initdate>=").append(SIDBUtil.SQL2Str(date));
    
    if(errors.isEmpty()&&SICheckValid.checkValid(errors, "支払日", date, SICheckDataConf.SICHECK_DATA_DATE_TYPE)) {
      try{
        if (SIDBUtil.hasData(lConnection, lSqlBuf.toString())) {
          errors.addError(new SICustomError("manager.message.freeword","締処理以前の日付では登録できません"));
        }
      } catch (Exception e) {
        e.printStackTrace();
        errors.addError(new SICustomError("database.execute.error"));
      }
    }
    SICheckValid.checkValid(errors, "支払金額", getPaymentPriceTxt(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
    SICheckValid.checkValid(errors, "支払金額", getPaymentPriceTxt(), SICheckDataConf.SICHECK_DATA_DIGIT_NEGATIVE_TYPE);
    
    if (isCSV) {
      try{
        if (!SIDBUtil.hasData(lConnection, "SELECT * FROM stockmtbl WHERE stockcode="+SIDBUtil.SQL2Str(getStockCode()))) {
          errors.addError(new SICustomError("database.query.notexist","仕入先マスタ"));
        }
      } catch (Exception e) {
        e.printStackTrace();
        errors.addError(new SICustomError("database.execute.error"));
      }
    }
    
    if (!errors.isEmpty()) {
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
    }
    
    return errors.isEmpty();
  }
  
  public boolean validateExecute(HttpServletRequest lRequest,Connection lConnection) {
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    SICustomErrors errors = new SICustomErrors();
    
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append("SELECT initdate FROM hacchurecordtbl ");
    lSqlBuf.append("WHERE stockcode=").append(SIDBUtil.SQL2Str(this.getStockCode()," "));
    lSqlBuf.append("AND initdate>=").append(SIDBUtil.SQL2Str(this.getPaymentDate()));
    
    if(SICheckValid.checkValid(errors, "締め処理日", getPaymentDate(), SICheckDataConf.SICHECK_DATA_DATE_TYPE)) {
      try{
        if (SIDBUtil.hasData(lConnection, lSqlBuf.toString())) {
          errors.addError(new SICustomError("manager.message.freeword","締処理以前の日付では登録できません"));
        }
        if (SICheckUtil.dateGreaterEqual(getPaymentDate(), new SIDateTime().getFullDate())) {
          errors.addError(new SICustomError("manager.message.freeword","過去日付以外での締処理はできません"));
        }
      } catch (Exception e) {
        e.printStackTrace();
        errors.addError(new SICustomError("database.execute.error"));
      }
    }
    
    if (!errors.isEmpty()) {
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
    }
    
    return errors.isEmpty();
  }
  
  public boolean validateDelete(HttpServletRequest lRequest,Connection lConnection) {
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    SICustomErrors errors = new SICustomErrors();
    SILogin manLogin = SIHTMLUtil.getLogin(lRequest);
    
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append("SELECT a.initusercode FROM hacchupaymenttbl a");
    lSqlBuf.append(",(SELECT stockcode,max(initdate) AS maxdate FROM hacchurecordtbl GROUP BY stockcode) b ");
    lSqlBuf.append("WHERE a.stockcode=b.stockcode ");
    lSqlBuf.append("AND a.paymentdate>b.maxdate ");
    lSqlBuf.append("AND a.enabledflg='1' ");
    lSqlBuf.append("AND a.stockcode=").append(SIDBUtil.SQL2Str(this.getStockCode()," "));
    lSqlBuf.append("AND a.paymentcode=").append(SIDBUtil.SQL2Str(this.getPaymentCode()));
    
    try{
      if (SIDBUtil.hasData(lConnection, lSqlBuf.toString())) {
        if (!manLogin.isAdmin() && !manLogin.getUserCode().equals(SIDBUtil.getFirstData(lConnection, lSqlBuf.toString())))
          errors.addError(new SICustomError("database.query.notexist","入金データ"));
      } else {
        errors.addError(new SICustomError("database.query.notexist","入金データ"));
      }
    } catch (Exception e) {
      e.printStackTrace();
      errors.addError(new SICustomError("database.execute.error"));
    }
    
    if (!errors.isEmpty()) {
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
    }
    return errors.isEmpty();
  }
  
  public void reset(Connection lConnection) {
    if (SIUtil.isNull(this.getStockCode())) return;
    
    Statement lStatement =null;
    ResultSet lResultSet =null;
    
    StringBuffer lSql = new StringBuffer();
    lSql.append("SELECT s.stockname,c.chargename,s.closelimit,s.closetype ");
    lSql.append("FROM stockmtbl s,chargetbl c ");
    lSql.append("WHERE s.chargecode=c.chargecode ");
    lSql.append("AND s.stockcode=").append(SIDBUtil.SQL2Str(this.getStockCode()));
    
    try {
      lStatement=lConnection.createStatement();
      lResultSet=lStatement.executeQuery(lSql.toString());
      if (lResultSet.next()){
        this.setStockName(lResultSet.getString("stockname"));
        this.setChargeName(lResultSet.getString("chargename"));
        String closeLimit = lResultSet.getString("closelimit");
        String closeType = lResultSet.getString("closetype");
        if ("31".equals(closeLimit)) closeLimit="末";
        if (SIUtil.isNotNull(closeType)) closeType=SIFlagUtil.getFlagName(SIFlagConf.SIFLAG_STOCK_PAYCLOSE_FLG_INX, closeType);
        if (SIUtil.isNotNull(closeType)&&SIUtil.isNotNull(closeLimit)) {
          this.setMemo(closeType+closeLimit+"日締め");
        } else {
          this.setMemo("締め日未設定");
        }
      }
      this.setLastClose("");
    }catch(Exception ex){
      ex.printStackTrace();
    }finally{
      SIDBUtil.close(lStatement,lResultSet);
    }
  }
  
  public Collection getCollection(Connection lConnection) {
    Collection paymentColl = new ArrayList();
    
    Statement lStatement =null;
    ResultSet lResultSet =null;
    
    StringBuffer lSql = new StringBuffer();
    lSql.append("SELECT a.hacchucode,a.p,-1*a.m AS m,a.shippmentdate AS paymentdate,a.individualcode||a.branchname||coalesce(a.amount::varchar,'') AS memo");
    lSql.append(",a.inx,a.individualcode,c.chargename,a.chargecode AS usercode,null::numeric AS paymentcode ");
    lSql.append("FROM hacchupaymentvw a ");
    lSql.append("LEFT OUTER JOIN (SELECT max(initdate) AS closeDate,stockcode FROM hacchurecordtbl WHERE countflg='1' ");
    if(SIUtil.isNotNull(this.getLastClose())) {
      lSql.append("AND initdate<").append(SIDBUtil.SQL2Str(this.getLastClose()," "));
    }
    lSql.append("GROUP BY stockcode) b ON a.stockcode=b.stockcode ");
    lSql.append("LEFT OUTER JOIN chargetbl c ON a.chargecode=c.chargecode ");
    lSql.append("WHERE a.stockcode=").append(SIDBUtil.SQL2Str(this.getStockCode()," "));
    lSql.append("AND CASE WHEN (a.branchcode=-1 AND a.p=0 AND a.m=0) THEN false ELSE true END ");
    lSql.append("AND CASE WHEN b.closeDate IS NULL THEN true ELSE a.shippmentDate>b.closeDate END ");
    if(SIUtil.isNotNull(this.getLastClose())) {
      lSql.append("AND a.shippmentdate<=").append(SIDBUtil.SQL2Str(this.getLastClose()," "));
    }
    lSql.append("UNION ALL ");
    lSql.append("SELECT null::numeric,CASE WHEN a.paymentprice<0 THEN -1*a.paymentprice ELSE 0 END AS p,CASE WHEN a.paymentprice>0 THEN a.paymentprice ELSE 0 END AS m ");
    lSql.append(",a.paymentdate,a.memo,a.index AS inx,'' AS individualcode,c.chargename,a.initusercode AS usercode,a.paymentcode ");
    lSql.append("FROM hacchupaymenttbl a ");
    lSql.append("LEFT OUTER JOIN (SELECT max(initdate) AS closeDate,stockcode FROM hacchurecordtbl WHERE countflg='1' ");
    if(SIUtil.isNotNull(this.getLastClose())) {
      lSql.append("AND initdate<").append(SIDBUtil.SQL2Str(this.getLastClose()," "));
    }
    lSql.append("GROUP BY stockcode) b ON a.stockcode=b.stockcode ");
    lSql.append("LEFT OUTER JOIN chargetbl c ON a.initusercode=c.chargecode ");
    lSql.append("WHERE a.enabledflg=1 AND a.stockcode=").append(SIDBUtil.SQL2Str(this.getStockCode()));
    lSql.append("AND CASE WHEN b.closeDate IS NULL THEN true ELSE a.paymentDate>b.closeDate END ");
    if(SIUtil.isNotNull(this.getLastClose())) {
      lSql.append("AND a.paymentdate<=").append(SIDBUtil.SQL2Str(this.getLastClose()," "));
    }
    lSql.append("ORDER BY paymentdate,inx,hacchucode,individualcode");
    
    try {
      lStatement=lConnection.createStatement();
      lResultSet=lStatement.executeQuery(lSql.toString());
      this.setCount("0");
      while (lResultSet.next()){
        SIHacchuPayment lPayment = new SIHacchuPayment();
        lPayment.setPaymentCode(lResultSet.getString("paymentCode"));
        lPayment.setHacchuCode(lResultSet.getString("hacchuCode"));
        lPayment.setPaymentPriceP(lResultSet.getString("p"));
        lPayment.setPaymentPriceM(lResultSet.getString("m"));
        lPayment.setPaymentDate(lResultSet.getString("paymentDate"));
        lPayment.setMemo(lResultSet.getString("memo"));
        lPayment.setChargeCode(lResultSet.getString("userCode"));
        lPayment.setChargeName(lResultSet.getString("chargeName"));
        lPayment.setIndex(lResultSet.getString("inx"));
        paymentColl.add(lPayment);
        this.setCount(SIUtil.add(this.count, "1"));
      }
    }catch(Exception ex){
      ex.printStackTrace();
    }finally{
      SIDBUtil.close(lStatement,lResultSet);
    }
    
    return paymentColl;
  }
  
  public Collection getCloseCollection(Connection lConnection) {
    Collection closeColl = new ArrayList();
    closeColl.add(new SINameValue("最新",""));
    if (SIUtil.isNull(this.getStockCode())) return closeColl;
    
    Statement lStatement =null;
    ResultSet lResultSet =null;
    SINameValue lNameValue=new SINameValue();
    try {
      lStatement=lConnection.createStatement();
      lResultSet=lStatement.executeQuery("SELECT DISTINCT initdate FROM hacchurecordtbl WHERE countflg='1' AND stockcode="+SIDBUtil.SQL2Str(this.getStockCode()," ORDER BY initdate DESC"));
      while (lResultSet.next()){
        lNameValue=new SINameValue(lResultSet.getString(1)+"締め",lResultSet.getString(1));
        closeColl.add(lNameValue);
      }
    }catch(Exception ex){
      ex.printStackTrace();
    }finally{
      SIDBUtil.close(lStatement,lResultSet);
    }
    
    return closeColl;
  }
}