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.SIHacchuPayment;
import jp.co.sint.config.SIConfig;
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.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.SIURLParameter;
import jp.co.sint.tools.SIUtil;

public class UIHacchuPaymentListCond extends SIBasic {
  
  private String stockCodeTxt = "";//仕入先
  
  private String stockCodeCbo = "";//仕入先
  
  private String stockPronNameTxt = "";
  
  private String chargeCodeTxt = "";//仕入担当者
  
  private String lastCloseYearCbo = "";
  
  private String lastCloseMonthCbo = "";
  
  private String lastCloseDayCbo = "";
  
  private String closeDateYearCbo = "";
  
  private String closeDateMonthCbo = "";
  
  private String totalPriceCbo = "0";
  
  private boolean searchFlg = true;
  
  public UIHacchuPaymentListCond(){}
  
  public String getStockCodeTxt() {
    return stockCodeTxt;
  }
  
  public String getStockCodeCbo() {
    return stockCodeCbo;
  }
  
  public String getStockPronNameTxt() {
    return stockPronNameTxt;
  }
  
  public String getChargeCodeTxt() {
    return chargeCodeTxt;
  }
  
  public String getCloseDateYearCbo() {
    return closeDateYearCbo;
  }
  
  public String getCloseDateMonthCbo() {
    return closeDateMonthCbo;
  }
  
  public String getCloseDate(){
    SIDateTime lDate;
    try {
      lDate = new SIDateTime(getCloseDateYearCbo()+"/"+getCloseDateMonthCbo()+"/01",SIConfig.SIDATE_FORMAT);
      lDate.addMonth(1);
      lDate.addDay(-1);
    }catch(Exception e) {
      return "";
    }
    return lDate.getFullDate();
  }
  
  public String getCloseNextDate(){
    SIDateTime lDate;
    try {
      lDate = new SIDateTime(getCloseDateYearCbo()+"/"+getCloseDateMonthCbo()+"/01",SIConfig.SIDATE_FORMAT);
      lDate.addMonth(1);
    }catch(Exception e) {
      return "";
    }
    return lDate.getFullDate();
  }
  
  public String getLastCloseYearCbo() {
    return lastCloseYearCbo;
  }
  
  public String getLastCloseMonthCbo() {
    return lastCloseMonthCbo;
  }
  
  public String getLastCloseDayCbo() {
    return lastCloseDayCbo;
  }
  
  public String getLastClose(){
    return SIDateTime.getDate(getLastCloseYearCbo(), getLastCloseMonthCbo(), getLastCloseDayCbo());
  }
  
  public String getTotalPriceCbo() {
    return totalPriceCbo;
  }
  
  public void setStockCodeTxt(String stockCodeTxt) {
    if (SIUtil.isNull(stockCodeTxt)) stockCodeTxt = "";
    this.stockCodeTxt = stockCodeTxt;
  }
  
  public void setStockCodeCbo(String stockCodeCbo) {
    if (SIUtil.isNull(stockCodeCbo)) stockCodeCbo = "";
    this.stockCodeCbo = stockCodeCbo;
  }
  
  public void setStockPronNameTxt(String stockPronNameTxt) {
    if (SIUtil.isNull(stockPronNameTxt)) stockPronNameTxt = "";
    this.stockPronNameTxt = stockPronNameTxt;
  }
  
  public void setChargeCodeTxt(String chargeCodeTxt) {
    if (SIUtil.isNull(chargeCodeTxt)) chargeCodeTxt = "";
    this.chargeCodeTxt = chargeCodeTxt;
  }
  
  public void setCloseDateYearCbo(String closeDateYearCbo) {
    if (SIUtil.isNull(closeDateYearCbo)) closeDateYearCbo = "";
    this.closeDateYearCbo = closeDateYearCbo;
  }
  
  public void setCloseDateMonthCbo(String closeDateMonthCbo) {
    if (SIUtil.isNull(closeDateMonthCbo)) closeDateMonthCbo = "";
    this.closeDateMonthCbo = closeDateMonthCbo;
  }
  
  public void setLastCloseYearCbo(String lastCloseYearCbo) {
    if (SIUtil.isNull(lastCloseYearCbo)) lastCloseYearCbo = "";
    this.lastCloseYearCbo = lastCloseYearCbo;
  }
  
  public void setLastCloseMonthCbo(String lastCloseMonthCbo) {
    if (SIUtil.isNull(lastCloseMonthCbo)) lastCloseMonthCbo = "";
    this.lastCloseMonthCbo = lastCloseMonthCbo;
  }
  
  public void setLastCloseDayCbo(String lastCloseDayCbo) {
    if (SIUtil.isNull(lastCloseDayCbo)) lastCloseDayCbo = "";
    this.lastCloseDayCbo = lastCloseDayCbo;
  }
  
  public void setTotalPriceCbo(String totalPriceCbo) {
    if (SIUtil.isNull(totalPriceCbo)) totalPriceCbo = "";
    this.totalPriceCbo = totalPriceCbo;
  }
  
  public void init(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    super.init(lRequest, lUrlParam);
    this.setStockCodeTxt((String) lUrlParam.getParam("stockCodeTxt"));
    this.setStockCodeCbo((String) lUrlParam.getParam("stockCodeCbo"));
    this.setStockPronNameTxt((String) lUrlParam.getParam("stockPronNameTxt"));
    this.setChargeCodeTxt((String) lUrlParam.getParam("chargeCodeTxt"));
    this.setLastCloseYearCbo((String) lUrlParam.getParam("lastCloseYearCbo"));
    this.setLastCloseMonthCbo((String) lUrlParam.getParam("lastCloseMonthCbo"));
    this.setLastCloseDayCbo((String) lUrlParam.getParam("lastCloseDayCbo"));
    this.setTotalPriceCbo((String) lUrlParam.getParam("totalPriceCbo"));
  }
  
  public void initExecute(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    this.setCloseDateYearCbo((String) lUrlParam.getParam("closeDateYearCbo"));
    this.setCloseDateMonthCbo((String) lUrlParam.getParam("closeDateMonthCbo"));
  }
  
  public boolean validate(HttpServletRequest lRequest) {
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    SICustomErrors errors = new SICustomErrors();
    
    SICheckValid.checkValid(errors, "仕入先コード", this.getStockCodeTxt(), SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
    SICheckValid.checkValid(errors, "仕入先コード", this.getStockCodeTxt(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 16);
    
    SICheckValid.checkValid(errors, "仕入先名カナ", this.getStockPronNameTxt(), SICheckDataConf.SICHECK_DATA_ZENKAKU_KANA_TYPE);
    SICheckValid.checkValid(errors, "仕入先名カナ", this.getStockPronNameTxt(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 100);
    
    SICheckValid.checkValid(errors, "最終締め日", this.getLastClose(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
    
    
    if (!errors.isEmpty()) {
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
      this.searchFlg = false;
    }
    
    return errors.isEmpty();
  }
  
  public boolean validateExecute(HttpServletRequest lRequest,Connection lConnection) {
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    SICustomErrors errors = new SICustomErrors();
    SICheckValid.checkValid(errors, "締処理対象日付（年）", this.getCloseDateYearCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
    SICheckValid.checkValid(errors, "締処理対象日付（月）", this.getCloseDateMonthCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
    
    if (SIUtil.isNotNull(this.getCloseDate())&&SICheckValid.checkValid(errors, "締処理対象月", this.getCloseDate(), SICheckDataConf.SICHECK_DATA_DATE_TYPE)) {
      
      try {
        if (SICheckUtil.dateGreaterEqual(this.getCloseDate(), new SIDateTime().getFullDate())) {
          errors.addError(new SICustomError("manager.message.freeword","過去日付以外での締処理はできません"));
        }
        
        StringBuffer lSql = new StringBuffer();
        lSql.append("SELECT a.stockcode FROM stockmtbl a ");
        lSql.append("LEFT OUTER JOIN (SELECT max(initdate) AS initdate,stockcode FROM hacchurecordtbl b GROUP BY stockcode) b ");
        lSql.append("ON a.stockcode=b.stockcode ");
        lSql.append("WHERE b.initdate IS NULL OR b.initdate<").append(SIDBUtil.SQL2Str(this.getCloseDate()));
        
        if (!SIDBUtil.hasData(lConnection, lSql.toString())) {
          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 String getCondtionSQL() {
    SITableConditionManager lConditionMan = new SITableConditionManager();
    
    SITableCondition DD = new SITableCondition("", "s.stockCode", this.stockCodeTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    DD.setBlankEnable(false);
    lConditionMan.add(DD);
    
    DD = new SITableCondition("", "s.stockCode", this.stockCodeCbo, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    
    DD = new SITableCondition("", "s.stockPronName", this.stockPronNameTxt, SIConfig.SICONDITION_TYPE_LIKE, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    
    DD = new SITableCondition("", "s.chargeCode", this.chargeCodeTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    
    if(SIUtil.isNotNull(this.getLastClose())) {
      DD = new SITableCondition(" AND (r.lastclose IS NULL OR r.lastclose<="+SIDBUtil.SQL2Str(this.getLastClose())+")");
      lConditionMan.add(DD);
    }
    
    if (SIUtil.isNotNull(this.totalPriceCbo) && this.totalPriceCbo.equals("0")) {
      DD = new SITableCondition("", "coalesce(p2.price1,0)+coalesce(p1.price2,0)+coalesce(p3.price2x,0)-coalesce(p1.price3,0)-coalesce(p3.price3x,0)", "0", SIConfig.SICONDITION_TYPE_NOT_EQUAL, SIConfig.SICONDITION_TYPE_AND);
      lConditionMan.add(DD);
    } else if (SIUtil.isNotNull(this.totalPriceCbo) && this.totalPriceCbo.equals("1")) {
      DD = new SITableCondition("", "coalesce(p2.price1,0)+coalesce(p1.price2,0)+coalesce(p3.price2x,0)-coalesce(p1.price3,0)-coalesce(p3.price3x,0)", "0", SIConfig.SICONDITION_TYPE_GREATER, SIConfig.SICONDITION_TYPE_AND);
      lConditionMan.add(DD);
    } else if (SIUtil.isNotNull(this.totalPriceCbo) && this.totalPriceCbo.equals("2")) {
      DD = new SITableCondition("", "coalesce(p2.price1,0)+coalesce(p1.price2,0)+coalesce(p3.price2x,0)-coalesce(p1.price3,0)-coalesce(p3.price3x,0)", "0", SIConfig.SICONDITION_TYPE_LESS, SIConfig.SICONDITION_TYPE_AND);
      lConditionMan.add(DD);
    }
    
    return lConditionMan.getCondtionSQL();
  }
  
  public Collection getCollection(Connection lConnection){
    Collection resultColl = new ArrayList();
    StringBuffer lSqlBuf = new StringBuffer();
    StringBuffer lStockBuf = new StringBuffer();
    StringBuffer lCountBuf = new StringBuffer();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    if (!this.searchFlg) return resultColl;
    
    lStockBuf.append("SELECT s.stockcode,s.stockname,c.chargename,coalesce(p2.price1,0) AS price1");
    lStockBuf.append(",coalesce(p1.price2,0)+coalesce(p3.price2x,0) AS price2");
    lStockBuf.append(",coalesce(p1.price3,0)+coalesce(p3.price3x,0) AS price3");
    lStockBuf.append(",coalesce(p1.price2,0)+coalesce(p3.price2x,0)-coalesce(p1.price3,0)-coalesce(p3.price3x,0) AS price4");
    lStockBuf.append(",r.lastclose ");
    
    lCountBuf.append("SELECT count(*) ");
    
    lSqlBuf.append("FROM stockmtbl s ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT stockcode,max(initdate) AS lastclose FROM hacchurecordtbl GROUP BY stockcode) r ");
    lSqlBuf.append("ON s.stockcode=r.stockcode ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT a.stockcode,sum(a.p) AS price2,-1 * sum(a.m) AS price3 FROM hacchupaymentvw a ");
    lSqlBuf.append(" LEFT OUTER JOIN (SELECT stockcode,max(initdate) AS lastclose FROM hacchurecordtbl GROUP BY stockcode) b ");
    lSqlBuf.append(" ON a.stockcode=b.stockcode ");
    lSqlBuf.append(" WHERE CASE WHEN b.lastclose IS NULL THEN true ELSE a.shippmentdate > b.lastclose END GROUP BY a.stockcode) p1 ");
    lSqlBuf.append("ON s.stockcode=p1.stockcode ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT stockcode,-1 * paymentprice AS price1,paymentdate FROM hacchupaymenttbl WHERE index=0 AND enabledflg=1) p2 ");
    lSqlBuf.append("ON r.lastclose + '1 days'::interval = p2.paymentdate AND s.stockcode=p2.stockcode ");
    lSqlBuf.append("LEFT OUTER JOIN (");
    lSqlBuf.append("SELECT pp.stockcode,sum(CASE WHEN pp.paymentprice < 0 THEN -1 * pp.paymentprice ELSE 0 END) AS price2x ");
    lSqlBuf.append(",sum(CASE WHEN pp.paymentprice > 0 THEN pp.paymentprice ELSE 0 END) AS price3x ");
    lSqlBuf.append("FROM hacchupaymenttbl pp LEFT OUTER JOIN (SELECT stockcode,max(initdate) AS lastclose FROM hacchurecordtbl GROUP BY stockcode) pr ");
    lSqlBuf.append("ON pp.stockcode=pr.stockcode ");
    lSqlBuf.append("WHERE index!='0' AND enabledflg='1' AND CASE WHEN pr.lastclose IS NULL THEN true ELSE pp.paymentdate > pr.lastclose END GROUP BY pp.stockcode) p3 ");
    lSqlBuf.append("ON s.stockcode=p3.stockcode ");
    lSqlBuf.append(",chargetbl c ");
    lSqlBuf.append("WHERE s.chargecode=c.chargecode ");
    lSqlBuf.append(this.getCondtionSQL());
    
    lStockBuf.append(lSqlBuf);
    lCountBuf.append(lSqlBuf);
    
    lStockBuf.append("ORDER BY stockcode ASC ");
    
    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;
      
      lStatement = lConnection.createStatement();
      lStockBuf.append(" LIMIT ").append(lPageSize);
      if(lFromInx>0) lStockBuf.append(" OFFSET ").append(lFromInx);
      lResultSet = lStatement.executeQuery(lStockBuf.toString());
      
      for (int jj = 0; jj < lFromInx; jj++)
        resultColl.add(null);
      
      int lIndex = 0;
      
      // レコードのセットの作成
      while (lResultSet.next() && lIndex < lPageSize) {
        SIHacchuPayment lData = new SIHacchuPayment();
        lData.setStockCode(lResultSet.getString("stockCode"));
        lData.setStockName(lResultSet.getString("stockName"));
        lData.setChargeName(lResultSet.getString("chargeName"));
        lData.setPaymentPriceEx(lResultSet.getString("price1"));
        lData.setPaymentPriceP(lResultSet.getString("price2"));
        lData.setPaymentPriceM(lResultSet.getString("price3"));
        lData.setLastClose(lResultSet.getString("lastClose"));
        resultColl.add(lData);
        lIndex++;
      }
      
      for (int jj = lFromInx + lPageSize; jj < lRecordCount; jj++) resultColl.add(null);
      
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    
    return resultColl;
  }
}