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.SIHacchu;
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.SICustomErrors;
import jp.co.sint.tools.SIDateTime;
import jp.co.sint.tools.SIURLParameter;
import jp.co.sint.tools.SIUtil;

public class UIHacchuListCond extends SIBasic {
  
  private String hacchuCodeTxt = "";//発注番号
  
  private String orderCodeTxt = "";//受注番号
  
  private String stockCodeTxt = "";//仕入先
  
  private String branchCodeTxt = "";//発注支店
  
  private String chargeCodeTxt = "";//発注担当者
  
  private String statusTxt = "";//発注ステータス
  
  private String deliveryStatusTxt = "";//納品状況
  
  private String orderFlgTxt = "";//商品区分
  
  private String cmdtyNameTxt = "";//商品名
  
  private String individualCodeTxt = "";//在庫コード
  
  private String orderDateYearFromCbo = "";//注文日From（年）
  
  private String orderDateMonthFromCbo = "";//注文日From（月）
  
  private String orderDateDayFromCbo = "";//注文日From（日）
  
  private String orderDateYearToCbo = "";//注文日To（年）
  
  private String orderDateMonthToCbo = "";//注文日To（月）
  
  private String orderDateDayToCbo = "";//注文日To（日）
  
  private String initDateYearFromCbo = "";//登録日From（年）
  
  private String initDateMonthFromCbo = "";//登録日From（月）
  
  private String initDateDayFromCbo = "";//登録日From（日）
  
  private String initDateYearToCbo = "";//登録日To（年）
  
  private String initDateMonthToCbo = "";//登録日To（月）
  
  private String initDateDayToCbo = "";//登録日To（日）
  
  private boolean searchFlg = false;
  
  public UIHacchuListCond(){
    SIDateTime lDateTime = new SIDateTime();
    String Month = Integer.toString(lDateTime.getMonth());
    String Day = Integer.toString(lDateTime.getDay());
    
    if (Month.length() == 1) Month = "0" + Month;
    if (Day.length() == 1) Day = "0" + Day;
    
    // 受注日の年(To)
    initDateYearToCbo = Integer.toString(lDateTime.getYear());
    // 受注日の月(To)
    initDateMonthToCbo = Month;
    // 受注日の日(To)
    initDateDayToCbo = Day;
    
    // 二ヶ月前の日付
    lDateTime.addMonth(-2);
    Month = Integer.toString(lDateTime.getMonth());
    Day = Integer.toString(lDateTime.getDay());
    if (Month.length() == 1) Month = "0" + Month;
    if (Day.length() == 1) Day = "0" + Day;
    
    // 受注日の年(From)
    initDateYearFromCbo = Integer.toString(lDateTime.getYear());
    // 受注日の月(From)
    initDateMonthFromCbo = Month;
    // 受注日の日(From)
    initDateDayFromCbo = Day;
  }
  
  public UIHacchuListCond(String userCode){
    this.setChargeCodeTxt(userCode);
    SIDateTime lDateTime = new SIDateTime();
    String Month = Integer.toString(lDateTime.getMonth());
    String Day = Integer.toString(lDateTime.getDay());
    
    if (Month.length() == 1) Month = "0" + Month;
    if (Day.length() == 1) Day = "0" + Day;
    
    // 受注日の年(To)
    initDateYearToCbo = Integer.toString(lDateTime.getYear());
    // 受注日の月(To)
    initDateMonthToCbo = Month;
    // 受注日の日(To)
    initDateDayToCbo = Day;
    
    // 二ヶ月前の日付
    lDateTime.addMonth(-2);
    Month = Integer.toString(lDateTime.getMonth());
    Day = Integer.toString(lDateTime.getDay());
    if (Month.length() == 1) Month = "0" + Month;
    if (Day.length() == 1) Day = "0" + Day;
    
    // 受注日の年(From)
    initDateYearFromCbo = Integer.toString(lDateTime.getYear());
    // 受注日の月(From)
    initDateMonthFromCbo = Month;
    // 受注日の日(From)
    initDateDayFromCbo = Day;
  }
  
  public String getBranchCodeTxt() {
    return branchCodeTxt;
  }
  
  public String getChargeCodeTxt() {
    return chargeCodeTxt;
  }
  
  public String getDeliveryStatusTxt() {
    return deliveryStatusTxt;
  }
  
  public String getHacchuCodeTxt() {
    return hacchuCodeTxt;
  }
  
  public String getOrderCodeTxt() {
    return orderCodeTxt;
  }
  
  public String getOrderFlgTxt() {
    return orderFlgTxt;
  }
  
  public String getStatusTxt() {
    return statusTxt;
  }
  
  public String getStockCodeTxt() {
    return stockCodeTxt;
  }
  
  public void setBranchCodeTxt(String branchCodeTxt) {
    if (SIUtil.isNull(branchCodeTxt)) branchCodeTxt = "";
    this.branchCodeTxt = branchCodeTxt;
  }
  
  public void setChargeCodeTxt(String chargeCodeTxt) {
    if (SIUtil.isNull(chargeCodeTxt)) chargeCodeTxt = "";
    this.chargeCodeTxt = chargeCodeTxt;
  }
  
  public void setDeliveryStatusTxt(String deliveryStatusTxt) {
    if (SIUtil.isNull(deliveryStatusTxt)) deliveryStatusTxt = "";
    this.deliveryStatusTxt = deliveryStatusTxt;
  }
  
  public void setHacchuCodeTxt(String hacchuCodeTxt) {
    if (SIUtil.isNull(hacchuCodeTxt)) hacchuCodeTxt = "";
    this.hacchuCodeTxt = hacchuCodeTxt;
  }
  
  public void setOrderCodeTxt(String orderCodeTxt) {
    if (SIUtil.isNull(orderCodeTxt)) orderCodeTxt = "";
    this.orderCodeTxt = orderCodeTxt;
  }
  
  public void setOrderFlgTxt(String orderFlgTxt) {
    if (SIUtil.isNull(orderFlgTxt)) orderFlgTxt = "";
    this.orderFlgTxt = orderFlgTxt;
  }
  
  public void setStatusTxt(String statusTxt) {
    if (SIUtil.isNull(statusTxt)) statusTxt = "";
    this.statusTxt = statusTxt;
  }
  
  public void setStockCodeTxt(String stockCodeTxt) {
    if (SIUtil.isNull(stockCodeTxt)) stockCodeTxt = "";
    this.stockCodeTxt = stockCodeTxt;
  }
  
  public String getCmdtyNameTxt() {
    return cmdtyNameTxt;
  }
  
  public String getIndividualCodeTxt() {
    return individualCodeTxt;
  }
  
  public String getOrderDateYearFromCbo() {
    return this.orderDateYearFromCbo;
  }
  
  public String getOrderDateMonthFromCbo() {
    return this.orderDateMonthFromCbo;
  }
  
  public String getOrderDateDayFromCbo() {
    return this.orderDateDayFromCbo;
  }
  
  public String getOrderDateFrom() {
    return SIDateTime.getDate(getOrderDateYearFromCbo(), getOrderDateMonthFromCbo(), getOrderDateDayFromCbo());
  }
  
  public String getOrderDateYearToCbo() {
    return this.orderDateYearToCbo;
  }
  
  public String getOrderDateMonthToCbo() {
    return this.orderDateMonthToCbo;
  }
  
  public String getOrderDateDayToCbo() {
    return this.orderDateDayToCbo;
  }
  
  public String getOrderDateTo() {
    return SIDateTime.getDate(getOrderDateYearToCbo(), getOrderDateMonthToCbo(), getOrderDateDayToCbo());
  }
  
  public String getInitDateYearFromCbo() {
    return this.initDateYearFromCbo;
  }
  
  public String getInitDateMonthFromCbo() {
    return this.initDateMonthFromCbo;
  }
  
  public String getInitDateDayFromCbo() {
    return this.initDateDayFromCbo;
  }
  
  public String getInitDateFrom() {
    return SIDateTime.getDate(getInitDateYearFromCbo(), getInitDateMonthFromCbo(), getInitDateDayFromCbo());
  }
  
  public String getInitDateYearToCbo() {
    return this.initDateYearToCbo;
  }
  
  public String getInitDateMonthToCbo() {
    return this.initDateMonthToCbo;
  }
  
  public String getInitDateDayToCbo() {
    return this.initDateDayToCbo;
  }
  
  public String getInitDateTo() {
    return SIDateTime.getDate(getInitDateYearToCbo(), getInitDateMonthToCbo(), getInitDateDayToCbo());
  }
  
  public void setCmdtyNameTxt(String cmdtyNameTxt) {
    if (SIUtil.isNull(cmdtyNameTxt)) cmdtyNameTxt = "";
    this.cmdtyNameTxt = cmdtyNameTxt;
  }
  
  public void setIndividualCodeTxt(String individualCodeTxt) {
    if (SIUtil.isNull(individualCodeTxt)) individualCodeTxt = "";
    this.individualCodeTxt = individualCodeTxt;
  }
  
  public void setOrderDateYearFromCbo(String lOrderDateYearFromCbo) {
    if (SIUtil.isNull(lOrderDateYearFromCbo)) lOrderDateYearFromCbo = "";
    this.orderDateYearFromCbo = SIUtil.changeTo(lOrderDateYearFromCbo.trim(), this.encode);
  }
  
  public void setOrderDateMonthFromCbo(String lOrderDateMonthFromCbo) {
    if (SIUtil.isNull(lOrderDateMonthFromCbo)) lOrderDateMonthFromCbo = "";
    this.orderDateMonthFromCbo = SIUtil.changeTo(lOrderDateMonthFromCbo.trim(), this.encode);
  }
  
  public void setOrderDateDayFromCbo(String lOrderDateDayFromCbo) {
    if (SIUtil.isNull(lOrderDateDayFromCbo)) lOrderDateDayFromCbo = "";
    this.orderDateDayFromCbo = SIUtil.changeTo(lOrderDateDayFromCbo.trim(), this.encode);
  }
  
  public void setOrderDateYearToCbo(String lOrderDateYearToCbo) {
    if (SIUtil.isNull(lOrderDateYearToCbo)) lOrderDateYearToCbo = "";
    this.orderDateYearToCbo = SIUtil.changeTo(lOrderDateYearToCbo.trim(), this.encode);
  }
  
  public void setOrderDateMonthToCbo(String lOrderDateMonthToCbo) {
    if (SIUtil.isNull(lOrderDateMonthToCbo)) lOrderDateMonthToCbo = "";
    this.orderDateMonthToCbo = SIUtil.changeTo(lOrderDateMonthToCbo.trim(), this.encode);
  }
  
  public void setOrderDateDayToCbo(String lOrderDateDayToCbo) {
    if (SIUtil.isNull(lOrderDateDayToCbo)) lOrderDateDayToCbo = "";
    this.orderDateDayToCbo = SIUtil.changeTo(lOrderDateDayToCbo.trim(), this.encode);
  }
  
  public void setInitDateYearFromCbo(String lInitDateYearFromCbo) {
    if (SIUtil.isNull(lInitDateYearFromCbo)) lInitDateYearFromCbo = "";
    this.initDateYearFromCbo = SIUtil.changeTo(lInitDateYearFromCbo.trim(), this.encode);
  }
  
  public void setInitDateMonthFromCbo(String lInitDateMonthFromCbo) {
    if (SIUtil.isNull(lInitDateMonthFromCbo)) lInitDateMonthFromCbo = "";
    this.initDateMonthFromCbo = SIUtil.changeTo(lInitDateMonthFromCbo.trim(), this.encode);
  }
  
  public void setInitDateDayFromCbo(String lInitDateDayFromCbo) {
    if (SIUtil.isNull(lInitDateDayFromCbo)) lInitDateDayFromCbo = "";
    this.initDateDayFromCbo = SIUtil.changeTo(lInitDateDayFromCbo.trim(), this.encode);
  }
  
  public void setInitDateYearToCbo(String lInitDateYearToCbo) {
    if (SIUtil.isNull(lInitDateYearToCbo)) lInitDateYearToCbo = "";
    this.initDateYearToCbo = SIUtil.changeTo(lInitDateYearToCbo.trim(), this.encode);
  }
  
  public void setInitDateMonthToCbo(String lInitDateMonthToCbo) {
    if (SIUtil.isNull(lInitDateMonthToCbo)) lInitDateMonthToCbo = "";
    this.initDateMonthToCbo = SIUtil.changeTo(lInitDateMonthToCbo.trim(), this.encode);
  }
  
  public void setInitDateDayToCbo(String lInitDateDayToCbo) {
    if (SIUtil.isNull(lInitDateDayToCbo)) lInitDateDayToCbo = "";
    this.initDateDayToCbo = SIUtil.changeTo(lInitDateDayToCbo.trim(), this.encode);
  }
  
  public void init(HttpServletRequest lRequest,SIURLParameter lUrlParam){
    super.init(lRequest, lUrlParam);
    this.setHacchuCodeTxt((String) lUrlParam.getParam("hacchuCodeTxt"));
    this.setOrderCodeTxt((String) lUrlParam.getParam("orderCodeTxt"));
    this.setStockCodeTxt((String) lUrlParam.getParam("stockCodeTxt"));
    this.setBranchCodeTxt((String) lUrlParam.getParam("branchCodeTxt"));
    this.setChargeCodeTxt((String) lUrlParam.getParam("chargeCodeTxt"));
    this.setStatusTxt((String) lUrlParam.getParam("statusTxt"));
    this.setDeliveryStatusTxt((String) lUrlParam.getParam("deliveryStatusTxt"));
    this.setOrderFlgTxt((String) lUrlParam.getParam("orderFlgTxt"));
    this.setCmdtyNameTxt((String) lUrlParam.getParam("cmdtyNameTxt"));
    this.setIndividualCodeTxt((String) lUrlParam.getParam("individualCodeTxt"));
    this.setOrderDateYearFromCbo((String) lUrlParam.getParam("orderDateYearFromCbo"));
    this.setOrderDateMonthFromCbo((String) lUrlParam.getParam("orderDateMonthFromCbo"));
    this.setOrderDateDayFromCbo((String) lUrlParam.getParam("orderDateDayFromCbo"));
    this.setOrderDateYearToCbo((String) lUrlParam.getParam("orderDateYearToCbo"));
    this.setOrderDateMonthToCbo((String) lUrlParam.getParam("orderDateMonthToCbo"));
    this.setOrderDateDayToCbo((String) lUrlParam.getParam("orderDateDayToCbo"));
    this.setInitDateYearFromCbo((String) lUrlParam.getParam("initDateYearFromCbo"));
    this.setInitDateMonthFromCbo((String) lUrlParam.getParam("initDateMonthFromCbo"));
    this.setInitDateDayFromCbo((String) lUrlParam.getParam("initDateDayFromCbo"));
    this.setInitDateYearToCbo((String) lUrlParam.getParam("initDateYearToCbo"));
    this.setInitDateMonthToCbo((String) lUrlParam.getParam("initDateMonthToCbo"));
    this.setInitDateDayToCbo((String) lUrlParam.getParam("initDateDayToCbo"));
  }
  
  public boolean validate(HttpServletRequest lRequest) {
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    SICustomErrors errors = new SICustomErrors();
    
    SICheckValid.checkValid(errors, "発注番号", this.getHacchuCodeTxt(), SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
    SICheckValid.checkValid(errors, "発注番号", this.getHacchuCodeTxt(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 8);
    
    SICheckValid.checkValid(errors, "受注番号", this.getOrderCodeTxt(), SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
    SICheckValid.checkValid(errors, "受注番号", this.getOrderCodeTxt(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 8);
    SICheckValid.checkValid(errors, "商品名", this.getCmdtyNameTxt(), SICheckDataConf.SICHECK_DATA_CMDTYNAME_TYPE);
    SICheckValid.checkValid(errors, "商品名", this.getCmdtyNameTxt(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 128);
    SICheckValid.checkValid(errors, "在庫コード", this.getIndividualCodeTxt(), SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE);
    SICheckValid.checkValid(errors, "在庫コード", this.getIndividualCodeTxt(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE, 16);
    
    SICheckValid.checkValid(errors, "受注日From", getOrderDateFrom(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
    SICheckValid.checkValid(errors, "受注日To", getOrderDateTo(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
    
    SICheckValid.checkValid(errors, "登録日From", getInitDateFrom(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
    SICheckValid.checkValid(errors, "登録日To", getInitDateTo(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
    try {
      if (!SICheckUtil.dateEqual(this.getOrderDateFrom(), this.getOrderDateTo())) {
        SICheckValid.checkValid(errors, "受注日From", "受注日To", this.getOrderDateFrom(), this.getOrderDateTo(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
      }
      if (!SICheckUtil.dateEqual(this.getInitDateFrom(), this.getInitDateTo())) {
        SICheckValid.checkValid(errors, "登録日From", "登録日To", this.getInitDateFrom(), this.getInitDateTo(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
      }
    } catch (Exception e) {}
    
    if (!errors.isEmpty()) {
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
      this.searchFlg = false;
    } else {
      this.searchFlg = true;
    }
    
    return errors.isEmpty();
  }
  public String getCondtionSQL() {
    SITableConditionManager lConditionMan = new SITableConditionManager();
    
    SITableCondition DD = new SITableCondition("", "a.HacchuCode", this.hacchuCodeTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    DD.setBlankEnable(false);
    lConditionMan.add(DD);
    
    DD = new SITableCondition("", "a.orderCode", this.orderCodeTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    
    DD = new SITableCondition("", "a.branchCode", this.branchCodeTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    
    DD = new SITableCondition("", "a.chargeCode", this.chargeCodeTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    
    DD = new SITableCondition("", "a.stockCode", this.stockCodeTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    
    if (SIUtil.isNotNull(this.statusTxt) && this.statusTxt.equals("4")) {
      DD = new SITableCondition("", "a.status", "1", SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
      lConditionMan.add(DD);
      DD = new SITableCondition(" AND a.hacchuUpdateDateTime IS NULL ");
      lConditionMan.add(DD);
    } else if (SIUtil.isNotNull(this.statusTxt) && this.statusTxt.equals("5")) {
      DD = new SITableCondition("", "a.status", "1", SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
      lConditionMan.add(DD);
      DD = new SITableCondition(" AND a.hacchuUpdateDateTime IS NOT NULL ");
      lConditionMan.add(DD);
    } else {
      DD = new SITableCondition("", "a.status", this.statusTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
      lConditionMan.add(DD);
    }
    
    DD = new SITableCondition("", "b.deliveryflg+b.nodeliveryflg", this.deliveryStatusTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    
    DD = new SITableCondition("", "a.orderflg", this.orderFlgTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    
    DD = new SITableCondition("", "a.hacchuupdatedatetime::date", this.getOrderDateFrom(), SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    
    DD = new SITableCondition("", "a.hacchuupdatedatetime::date", this.getOrderDateTo(), SIConfig.SICONDITION_TYPE_LESS_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    
    DD = new SITableCondition("", "a.initdatetime::date", this.getInitDateFrom(), SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    
    DD = new SITableCondition("", "a.initdatetime::date", this.getInitDateTo(), SIConfig.SICONDITION_TYPE_LESS_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    
    if (SIUtil.isNotNull(this.getIndividualCodeTxt())||SIUtil.isNotNull(this.getCmdtyNameTxt())) {
      StringBuffer tmp = new StringBuffer();
      tmp.append(" AND a.hacchucode IN ( ");
      tmp.append("SELECT e1.hacchucode FROM hacchutbl e1,hacchudetailtbl e2 ");
      tmp.append("WHERE e1.hacchucode=e2.hacchucode AND e1.layercode=e2.layercode AND e1.enabledflg=1 ");
      if(SIUtil.isNotNull(this.getIndividualCodeTxt())) tmp.append("AND e2.individualcode LIKE '"+SIDBUtil.SQL2Like(this.getIndividualCodeTxt())+"%' ");
      if(SIUtil.isNotNull(this.getCmdtyNameTxt())) tmp.append("AND e2.cmdtyname LIKE '%"+SIDBUtil.SQL2Like(this.getCmdtyNameTxt())+"%' ");
      tmp.append(") ");
      lConditionMan.add(new SITableCondition(tmp.toString()));
    }
    
    return lConditionMan.getCondtionSQL();
  }
  
  public Collection getCollection(Connection lConnection){
    Collection resultColl = new ArrayList();
    StringBuffer lSqlBuf = new StringBuffer();
    StringBuffer lHacchuBuf = new StringBuffer();
    StringBuffer lCountBuf = new StringBuffer();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    if (!this.searchFlg) return resultColl;
    
    lHacchuBuf.append("SELECT a.hacchucode,a.ordercode,a.stockcode,a.stockname,b.deliveryflg+b.nodeliveryflg AS deliverystatus, ");
    lHacchuBuf.append("a.initdatetime,a.updatedatetime,a.branchcode,c.chargename,a.hacchumailflg,a.hacchuupdatedatetime, ");
    lHacchuBuf.append("b.total,a.status,a.arrivalplandate ");
    
    lCountBuf.append("SELECT count(*) ");
    
    lSqlBuf.append("FROM hacchutbl a LEFT OUTER JOIN chargetbl c ON a.chargecode=c.chargecode,");
    lSqlBuf.append("(SELECT h.hacchucode,");
    lSqlBuf.append("SUM(CASE d.hacchubranchcode WHEN '1' THEN d.importcost ELSE 0 END) ");
    lSqlBuf.append("+SUM(CASE d.hacchubranchcode WHEN '1' THEN d.deliveryfee ELSE 0 END) ");
    lSqlBuf.append("+SUM(CASE d.hacchubranchcode WHEN '1' THEN d.processingexpence ELSE 0 END) ");
    lSqlBuf.append("+SUM(d.purchaseprice*d.amount) AS total ");
    lSqlBuf.append(",CASE WHEN SUM(CASE WHEN d.shippmentdate IS NULL THEN 0 ELSE 1 END)=0 THEN 0 ELSE 1 END AS deliveryflg ");//1つ以上NOTNULLで1
    lSqlBuf.append(",CASE WHEN SUM(CASE WHEN d.shippmentdate IS NULL THEN 1 ELSE 0 END)=0 THEN 0 ELSE 2 END AS nodeliveryflg ");//1つ以上NULLで2
    lSqlBuf.append("FROM hacchuTbl h,hacchuDetailTbl d ");
    lSqlBuf.append("WHERE h.hacchucode=d.hacchucode AND h.layercode=d.layercode AND h.enabledflg='1' AND d.shipflg='0' ");
    
    
    SITableConditionManager lConditionMan = new SITableConditionManager();
    SITableCondition DD = new SITableCondition("", "h.HacchuCode", this.hacchuCodeTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    DD.setBlankEnable(false);
    lConditionMan.add(DD);
    DD = new SITableCondition("", "h.orderCode", this.orderCodeTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    DD = new SITableCondition("", "h.branchCode", this.branchCodeTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    DD = new SITableCondition("", "h.chargeCode", this.chargeCodeTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    DD = new SITableCondition("", "h.stockCode", this.stockCodeTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    
    if (SIUtil.isNotNull(this.statusTxt) && this.statusTxt.equals("4")) {
      DD = new SITableCondition("", "h.status", "1", SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
      lConditionMan.add(DD);
      DD = new SITableCondition(" AND h.hacchuUpdateDateTime IS NULL ");
      lConditionMan.add(DD);
    } else if (SIUtil.isNotNull(this.statusTxt) && this.statusTxt.equals("5")) {
      DD = new SITableCondition("", "h.status", "1", SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
      lConditionMan.add(DD);
      DD = new SITableCondition(" AND h.hacchuUpdateDateTime IS NOT NULL ");
      lConditionMan.add(DD);
    } else {
      DD = new SITableCondition("", "h.status", this.statusTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
      lConditionMan.add(DD);
    }
    DD = new SITableCondition("", "h.orderflg", this.orderFlgTxt, SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    DD = new SITableCondition("", "h.hacchuupdatedatetime::date", this.getOrderDateFrom(), SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    DD = new SITableCondition("", "h.hacchuupdatedatetime::date", this.getOrderDateTo(), SIConfig.SICONDITION_TYPE_LESS_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    DD = new SITableCondition("", "h.initdatetime::date", this.getInitDateFrom(), SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    DD = new SITableCondition("", "h.initdatetime::date", this.getInitDateTo(), SIConfig.SICONDITION_TYPE_LESS_EQUAL, SIConfig.SICONDITION_TYPE_AND);
    lConditionMan.add(DD);
    lSqlBuf.append(lConditionMan.getCondtionSQL());
    
    
    lSqlBuf.append("GROUP BY h.hacchucode) b ");
    lSqlBuf.append("WHERE a.hacchucode=b.hacchucode AND a.enabledflg='1' ");
    lSqlBuf.append(this.getCondtionSQL());
    
    lHacchuBuf.append(lSqlBuf);
    lCountBuf.append(lSqlBuf);
    
    lHacchuBuf.append("ORDER BY a.hacchucode DESC");
    
    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();
      lHacchuBuf.append(" LIMIT ").append(lPageSize);
      if(lFromInx>0) lHacchuBuf.append(" OFFSET ").append(lFromInx);
      lResultSet = lStatement.executeQuery(lHacchuBuf.toString());
      
      for (int jj = 0; jj < lFromInx; jj++)
        resultColl.add(null);
      
      int lIndex = 0;
      
      // レコードのセットの作成
      while (lResultSet.next() && lIndex < lPageSize) {
        SIHacchu lHacchu = new SIHacchu();
        lHacchu.setHacchuCode(lResultSet.getString("hacchuCode"));
        lHacchu.setStockCode(lResultSet.getString("stockCode"));
        lHacchu.setStockName(lResultSet.getString("stockName"));
        lHacchu.setOrderCode(lResultSet.getString("orderCode"));
        lHacchu.setInitDateTime(lResultSet.getString("initDateTime"));
        lHacchu.setUpdateDateTime(SIDBUtil.getDate(lResultSet.getTimestamp("updateDateTime")));
        lHacchu.setBranchCode(lResultSet.getString("branchCode"));
        lHacchu.setChargeName(lResultSet.getString("chargeName"));
        lHacchu.setHacchuMailFlg(lResultSet.getString("hacchuMailFlg"));
        lHacchu.setHacchuUpdateDateTime(SIDBUtil.getDate(lResultSet.getTimestamp("hacchuUpdateDateTime")));
        lHacchu.setTotalOfPrice(lResultSet.getString("total"));
        lHacchu.setStatus(lResultSet.getString("status"));
        lHacchu.setDeliveryStatus(lResultSet.getString("deliverystatus"));
        lHacchu.setArrivalPlanDate(SIDBUtil.getDate(lResultSet.getTimestamp("arrivalPlanDate")));
        resultColl.add(lHacchu);
        lIndex++;
      }
      
      for (int jj = lFromInx + lPageSize; jj < lRecordCount; jj++) resultColl.add(null);
      
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    
    return resultColl;
  }
  
  public String getDeliveryList(String lHacchuCode,Connection lConnection){
    StringBuffer str = new StringBuffer();
    StringBuffer sql = new StringBuffer();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    boolean firstFlg = true;
    
    sql.append("SELECT CASE WHEN a.branchcode=0 THEN '直送' ELSE b.branchname END AS branchname,a.amount,a.individualcode ");
    sql.append("FROM hacchudetailtbl a LEFT OUTER JOIN branchtbl b ON a.branchcode=b.branchcode,hacchutbl c ");
    sql.append("WHERE a.hacchucode=").append(SIDBUtil.SQL2Str(lHacchuCode," "));
    sql.append("AND a.shipflg='0' ");
    sql.append("AND a.hacchucode=c.hacchucode AND a.layercode=c.layercode AND c.enabledflg='1' ");
    sql.append("ORDER BY a.hacchuindividualcode,a.branchcode ");
    
    try{
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(sql.toString());
      
      while(lResultSet.next()){
        if (!firstFlg) str.append("\r\n");
        str.append(lResultSet.getString("individualcode")).append(" "+lResultSet.getString("branchname")).append(lResultSet.getString("amount"));
        firstFlg = false;
      }
    }catch(Exception e){
      e.printStackTrace();
      str = new StringBuffer("ERROR");
    }finally{
      SIDBUtil.close(lStatement, lResultSet);
    }
    return str.toString();
  }
}