/**
 * Copyright (c) 2004-2006 System Integrator Corporation.
 *                 All Rights Reserved.
 */
package jp.co.sint.beans.mallmgr;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;

import javax.servlet.http.HttpServletRequest;
import org.apache.log4j.Category;
import jp.co.sint.basic.SIBasic;
import jp.co.sint.basic.SIOrder;
import jp.co.sint.basic.SINameValue;
import jp.co.sint.basic.SIMarketingReportData;
import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIExcelConf;
import jp.co.sint.config.SIFlagConf;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.database.SITableCondition;
import jp.co.sint.database.SITableConditionManager;
import jp.co.sint.tools.SIBGUtil;
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.SIException;
import jp.co.sint.tools.SIURLParameter;
import jp.co.sint.tools.SIUtil;

/**
 * @version $Id: UIIOExcelListCond.java,v 1.0 2006/04/24 Exp $
 * @author 帳票出力を処理します。 <br>
 * Description:
 * <p>
 * History
 * </p>
 * <p>
 * Author&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Reason
 * </p>
 * ============&nbsp;&nbsp;&nbsp;==========&nbsp;&nbsp;===========================<br>
 * Hong.M.J 2006/04/24 12:20:42 Original
 */
public class UIIOExcelListCond extends SIBasic {
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  private String tblNameSel = "";
  // 対象支店
  private String branchCode = "";
  // 対象時期 年
  private String seasonYearCbo = "";
  // 対象時期月
  private String seasonMonthCbo = "";
  
  private String seasonYearFromCbo = "";
  
  private String seasonMonthFromCbo = "";
  
  private String seasonDayFromCbo = "";
  
  private String seasonYearToCbo = "";
  
  private String seasonMonthToCbo = "";
  
  private String seasonDayToCbo = "";
  // 受注経路
  private String orderRoute = "";
  // 取引区分
  private String orderStatusRdo = "";
  // 担当者
  private String chargeNameCdo = "";
  // 在庫コード
  private String individualCode = "";
  // 在庫管理
  private String noStockFlgRdo = "";
  // 顧客コード
  private String custCode = "";
  // カテゴリコード
  private String ctgryCode = "";
  // 支払区分
  private String payMethodNameCbo = "";
  // 明細有無
  private String detailFlgRdo = "0";
  // 入金状態
  private String receiptFlgRdo = "";
  // 出荷状態
  private String shippmentFlgRdo = "";
  // 新品・中古
  private String usedNewFlg = "";
  // 在庫管理
  private String instockFlg = "2";
  // 在庫有無
  private String individualFlg = "";
  
  private String inventoryFlg = "";
  // 出荷時期
  private String shipmentDateYearFromCbo = "";
  
  private String shipmentDateMonthFromCbo = "";
  
  private String shipmentDateDayFromCbo = "";
  
  private String shipmentDateYearToCbo = "";
  
  private String shipmentDateMonthToCbo = "";
  
  private String shipmentDateDayToCbo = "";
  
  private String interval = "";
  
  // 仕入先
  private String stockCodeCbo = "";
  
  // 入庫区分
  private String[] storeType = new String[0];
  
  // 出庫区分
  private String[] shipType = new String[0];
  
  // 明細有無
  private boolean detailFlg = true;
  
  private boolean print = true;
  
  // セグメント
  private String segmentFlg = "";
  
  // 検索用SQL
  private String conditionSQL = "";
  
  private String selectSQL = "";
  
  public UISalesStatus SS = new UISalesStatus();
  
  public UIMarketingReport MR = new UIMarketingReport();
  
  public UISegmentStatus SE = new UISegmentStatus();
  
  public UIIOExcelListCond() {}
  
  public UIIOExcelListCond(SIURLParameter lUrlParam) {
    init(lUrlParam);
  }
  
  /**
   * <b>init</b> 入力したデータを基づいて、このbeansを設定します。
   * 
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  public void init(SIURLParameter lUrlParam) {
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    this.setTblNameSel((String) lUrlParam.getParam("tblNameSel"));// 選択するテーブル名
    // 時期初期表示
    SIDateTime lDateTime = new SIDateTime();
    String Year = Integer.toString(lDateTime.getYear());
    String Month = Integer.toString(lDateTime.getMonth());
    String Day = Integer.toString(lDateTime.getLasyDay());
    
    if (Month.length() == 1) Month = "0" + Month;
    if (Day.length() == 1) Day = "0" + Day;
    if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_INVENTORYSTOCK_NAME)
        || this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_INDIVIDUAL_NAME)
        || this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_STOREHISTORY_NAME)) {
      this.setSeasonYearCbo(Year);
      this.setSeasonMonthCbo(Month);
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_INSENTIVE_NAME)) {
      // 受注期間の年(To)
      this.setSeasonYearToCbo(Year);
      // 受注期間の月(To)
      this.setSeasonMonthToCbo(Month);
      // 受注期間の日(To)
      this.setSeasonDayToCbo(Day);
      // 受注期間の年(From)
      this.setSeasonYearFromCbo(Year);
      // 受注期間の月(From)
      this.setSeasonMonthFromCbo(Month);
      // 受注期間の日(From)
      this.setSeasonDayFromCbo("01");
      // 出荷期間の年(To)
      this.setShipmentDateYearToCbo(Year);
      // 出荷期間の月(To)
      this.setShipmentDateMonthToCbo(Month);
      // 出荷期間の日(To)
      this.setShipmentDateDayToCbo(Day);
      // 出荷期間の年(From)
      this.setShipmentDateYearFromCbo(Year);
      // 出荷期間の月(From)
      this.setShipmentDateMonthFromCbo(Month);
      // 出荷期間の日(From)
      this.setShipmentDateDayFromCbo("01");
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SALES_NAME)) {
      // 出荷期間の年(To)
      this.setShipmentDateYearToCbo(Year);
      // 出荷期間の月(To)
      this.setShipmentDateMonthToCbo(Month);
      // 出荷期間の日(To)
      this.setShipmentDateDayToCbo(Day);
      // 出荷期間の年(From)
      this.setShipmentDateYearFromCbo(Year);
      // 出荷期間の月(From)
      this.setShipmentDateMonthFromCbo(Month);
      // 出荷期間の日(From)
      this.setShipmentDateDayFromCbo("01");
    } else {
      // 受注期間の年(To)
      this.setSeasonYearToCbo(Year);
      // 受注期間の月(To)
      this.setSeasonMonthToCbo(Month);
      // 受注期間の日(To)
      this.setSeasonDayToCbo(Day);
      // 受注期間の年(From)
      this.setSeasonYearFromCbo(Year);
      // 受注期間の月(From)
      this.setSeasonMonthFromCbo(Month);
      // 受注期間の日(From)
      this.setSeasonDayFromCbo("01");
    }
    this.setDetailFlgRdo("0");
  }
  
  public boolean validate(HttpServletRequest lRequest) {
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    SICustomErrors errors = new SICustomErrors();
    SICheckValid.checkValid(errors, "出力帳票名", this.getTblNameSel(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
    if (!errors.isEmpty()) lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
    return errors.isEmpty();
  }
  
  public void excelinit(HttpServletRequest lRequest,SIURLParameter lUrlParam) {
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    this.setTblNameSel((String) lUrlParam.getParam("tblNameSel"));// 選択するテーブル名
    if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_ORDER_NAME)) {
      // 受注期間
      this.setSeasonDayFromCbo((String) lUrlParam.getParam("seasonDayFromCbo"));
      this.setSeasonDayToCbo((String) lUrlParam.getParam("seasonDayToCbo"));
      this.setSeasonMonthFromCbo((String) lUrlParam.getParam("seasonMonthFromCbo"));
      this.setSeasonMonthToCbo((String) lUrlParam.getParam("seasonMonthToCbo"));
      this.setSeasonYearFromCbo((String) lUrlParam.getParam("seasonYearFromCbo"));
      this.setSeasonYearToCbo((String) lUrlParam.getParam("seasonYearToCbo"));
      // 担当支店
      this.setBranchCode((String) lUrlParam.getParam("branchCode"));
      // 担当者
      this.setChargeNameCdo((String) lUrlParam.getParam("chargeNameCdo"));
      // 明細
      this.setDetailFlgRdo((String) lUrlParam.getParam("detailFlgRdo"));
      // 顧客コード
      this.setCustCode((String) lUrlParam.getParam("custCode"));
      // 取引区分
      this.setOrderStatusRdo((String) lUrlParam.getParam("orderStatusRdo"));
      // 支払区分
      this.setPayMethodNameCbo((String) lUrlParam.getParam("payMethodNameCbo"));
      // 受注経路
      this.setOrderRoute((String) lUrlParam.getParam("orderRoute"));
      // 入金状況
      this.setReceiptFlgRdo((String) lUrlParam.getParam("receiptFlgRdo"));
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_ORDER_DETAIL_NAME)) {
      // 受注期間
      this.setSeasonDayFromCbo((String) lUrlParam.getParam("seasonDayFromCbo"));
      this.setSeasonDayToCbo((String) lUrlParam.getParam("seasonDayToCbo"));
      this.setSeasonMonthFromCbo((String) lUrlParam.getParam("seasonMonthFromCbo"));
      this.setSeasonMonthToCbo((String) lUrlParam.getParam("seasonMonthToCbo"));
      this.setSeasonYearFromCbo((String) lUrlParam.getParam("seasonYearFromCbo"));
      this.setSeasonYearToCbo((String) lUrlParam.getParam("seasonYearToCbo"));
      // 担当支店
      this.setBranchCode((String) lUrlParam.getParam("branchCode"));
      // 担当者
      this.setChargeNameCdo((String) lUrlParam.getParam("chargeNameCdo"));
      // 明細
      this.setDetailFlgRdo((String) lUrlParam.getParam("detailFlgRdo"));
      // 顧客コード
      this.setCustCode((String) lUrlParam.getParam("custCode"));
      // 出荷状況
      this.setShippmentFlgRdo((String) lUrlParam.getParam("shippmentFlgRdo"));
      // 取引区分
      this.setOrderStatusRdo((String) lUrlParam.getParam("orderStatusRdo"));
      // 中古・新品区分
      this.setUsedNewFlg((String) lUrlParam.getParam("usedNewFlg"));
      // 受注経路
      this.setOrderRoute((String) lUrlParam.getParam("orderRoute"));
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_INDIVIDUAL_NAME)) {
      // 対象年月
      this.setSeasonMonthCbo((String) lUrlParam.getParam("seasonMonthCbo"));
      this.setSeasonYearCbo((String) lUrlParam.getParam("seasonYearCbo"));
      // 出力対象
      this.setIndividualFlg((String) lUrlParam.getParam("individualFlg"));
      // 明細
      this.setDetailFlgRdo((String) lUrlParam.getParam("detailFlgRdo"));
      // 支店
      this.setBranchCode((String) lUrlParam.getParam("branchCode"));
      // 在庫管理
      this.setInstockFlg((String) lUrlParam.getParam("instockFlg"));
      // カテゴリ
      this.setCtgryCode((String) lUrlParam.getParam("ctgryCode"));
      // 在庫コード
      this.setIndividualCode((String) lUrlParam.getParam("individualCode"));
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SALES_NAME)) {
      // 受注期間
      this.setSeasonDayFromCbo((String) lUrlParam.getParam("seasonDayFromCbo"));
      this.setSeasonDayToCbo((String) lUrlParam.getParam("seasonDayToCbo"));
      this.setSeasonMonthFromCbo((String) lUrlParam.getParam("seasonMonthFromCbo"));
      this.setSeasonMonthToCbo((String) lUrlParam.getParam("seasonMonthToCbo"));
      this.setSeasonYearFromCbo((String) lUrlParam.getParam("seasonYearFromCbo"));
      this.setSeasonYearToCbo((String) lUrlParam.getParam("seasonYearToCbo"));
      // 出荷時期
      this.setShipmentDateDayFromCbo((String) lUrlParam.getParam("shipmentDateDayFromCbo"));
      this.setShipmentDateDayToCbo((String) lUrlParam.getParam("shipmentDateDayToCbo"));
      this.setShipmentDateMonthFromCbo((String) lUrlParam.getParam("shipmentDateMonthFromCbo"));
      this.setShipmentDateMonthToCbo((String) lUrlParam.getParam("shipmentDateMonthToCbo"));
      this.setShipmentDateYearFromCbo((String) lUrlParam.getParam("shipmentDateYearFromCbo"));
      this.setShipmentDateYearToCbo((String) lUrlParam.getParam("shipmentDateYearToCbo"));
      // 担当支店
      this.setBranchCode((String) lUrlParam.getParam("branchCode"));
      // 担当者
      this.setChargeNameCdo((String) lUrlParam.getParam("chargeNameCdo"));
      // 明細
      this.setDetailFlgRdo((String) lUrlParam.getParam("detailFlgRdo"));
      // 顧客コード
      this.setCustCode((String) lUrlParam.getParam("custCode"));
      // 在庫コード
      this.setIndividualCode((String) lUrlParam.getParam("individualCode"));
      // 取引区分
      this.setOrderStatusRdo((String) lUrlParam.getParam("orderStatusRdo"));
      // 中古・新品区分
      this.setUsedNewFlg((String) lUrlParam.getParam("usedNewFlg"));
      // 受注経路
      this.setOrderRoute((String) lUrlParam.getParam("orderRoute"));
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_INVENTORYSTOCK_NAME)) {
      // 対象年月
      this.setSeasonMonthCbo((String) lUrlParam.getParam("seasonMonthCbo"));
      this.setSeasonYearCbo((String) lUrlParam.getParam("seasonYearCbo"));
      // 支店
      this.setBranchCode((String) lUrlParam.getParam("branchCode"));
      // 出力対象
      this.setInventoryFlg((String) lUrlParam.getParam("inventoryFlg"));
      // 在庫管理
      this.setInstockFlg((String) lUrlParam.getParam("instockFlg"));
      // 在庫コード
      this.setIndividualCode((String) lUrlParam.getParam("individualCode"));
      // カテゴリ
      this.setCtgryCode((String) lUrlParam.getParam("ctgryCode"));
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_INSENTIVE_NAME)) {
      // 受注期間
      this.setSeasonDayFromCbo((String) lUrlParam.getParam("seasonDayFromCbo"));
      this.setSeasonDayToCbo((String) lUrlParam.getParam("seasonDayToCbo"));
      this.setSeasonMonthFromCbo((String) lUrlParam.getParam("seasonMonthFromCbo"));
      this.setSeasonMonthToCbo((String) lUrlParam.getParam("seasonMonthToCbo"));
      this.setSeasonYearFromCbo((String) lUrlParam.getParam("seasonYearFromCbo"));
      this.setSeasonYearToCbo((String) lUrlParam.getParam("seasonYearToCbo"));
      // 出荷時期
      this.setShipmentDateDayFromCbo((String) lUrlParam.getParam("shipmentDateDayFromCbo"));
      this.setShipmentDateDayToCbo((String) lUrlParam.getParam("shipmentDateDayToCbo"));
      this.setShipmentDateMonthFromCbo((String) lUrlParam.getParam("shipmentDateMonthFromCbo"));
      this.setShipmentDateMonthToCbo((String) lUrlParam.getParam("shipmentDateMonthToCbo"));
      this.setShipmentDateYearFromCbo((String) lUrlParam.getParam("shipmentDateYearFromCbo"));
      this.setShipmentDateYearToCbo((String) lUrlParam.getParam("shipmentDateYearToCbo"));
      // 担当支店
      this.setBranchCode((String) lUrlParam.getParam("branchCode"));
      // 担当者
      this.setChargeNameCdo((String) lUrlParam.getParam("chargeNameCdo"));
      // 明細
      this.setDetailFlgRdo((String) lUrlParam.getParam("detailFlgRdo"));
      // 顧客コード
      this.setCustCode((String) lUrlParam.getParam("custCode"));
      // 在庫コード
      this.setIndividualCode((String) lUrlParam.getParam("individualCode"));
      // 取引区分
      this.setOrderStatusRdo((String) lUrlParam.getParam("orderStatusRdo"));
      // 中古・新品区分
      this.setUsedNewFlg((String) lUrlParam.getParam("usedNewFlg"));
      // 受注経路
      this.setOrderRoute((String) lUrlParam.getParam("orderRoute"));
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_STOREHISTORY_NAME)) {
      // 対象年月
      this.setSeasonMonthCbo((String) lUrlParam.getParam("seasonMonthCbo"));
      this.setSeasonYearCbo((String) lUrlParam.getParam("seasonYearCbo"));
      // 在庫コード
      this.setIndividualCode((String) lUrlParam.getParam("individualCode"));
      // カテゴリ
      this.setCtgryCode((String) lUrlParam.getParam("ctgryCode"));
      // 出力単位
      this.setDetailFlgRdo((String) lUrlParam.getParam("detailFlgRdo"));
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_MARKETINGREPORT_NAME)
        || this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_MARKETINGREPORT_NAME0)
        || this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_TRENDREPORT_NAME)) {
      MR.init(lUrlParam);
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SALES_STATUS_NAME)  // エクセル販売ステータス出力
        || this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SALES_STATUS_NAME0)
        || this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SALES_STATUS_ALL_NAME)
        || this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SALES_STATUS_ALL_NAME0)) {
      SS.init(lUrlParam);
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SEGMENT_STATUS_NAME)||this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SEGMENT_STATUS_NAME0)) {// セグメント別集計表
      SE.init(lUrlParam);
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SICSV_HACCHU_PAYMENT_NAME)) {
      // 対象年月
      this.setSeasonMonthCbo((String) lUrlParam.getParam("seasonMonthCbo"));
      this.setSeasonYearCbo((String) lUrlParam.getParam("seasonYearCbo"));
      // 担当支店
      this.setBranchCode((String) lUrlParam.getParam("branchCode"));
      // 担当者
      this.setChargeNameCdo((String) lUrlParam.getParam("chargeNameCdo"));
      // 仕入先
      this.setStockCodeCbo((String) lUrlParam.getParam("stockCodeCbo"));
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SICSV_HISTORY_DETAIL_NAME)) {
      // 対象年月
      this.setSeasonDayFromCbo((String) lUrlParam.getParam("seasonDayFromCbo"));
      this.setSeasonDayToCbo((String) lUrlParam.getParam("seasonDayToCbo"));
      this.setSeasonMonthFromCbo((String) lUrlParam.getParam("seasonMonthFromCbo"));
      this.setSeasonMonthToCbo((String) lUrlParam.getParam("seasonMonthToCbo"));
      this.setSeasonYearFromCbo((String) lUrlParam.getParam("seasonYearFromCbo"));
      this.setSeasonYearToCbo((String) lUrlParam.getParam("seasonYearToCbo"));
      //入庫区分
      this.setStoreType(lRequest.getParameterValues("storeType"));
      //出庫区分
      this.setShipType(lRequest.getParameterValues("shipType"));
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SICSV_DEADSTOCK_LIST_NAME)) {
      // 対象年月
      this.setSeasonMonthCbo((String) lUrlParam.getParam("seasonMonthCbo"));
      this.setSeasonYearCbo((String) lUrlParam.getParam("seasonYearCbo"));
      // 対象支店
      this.setBranchCode((String) lUrlParam.getParam("branchCode"));
      // 滞留期間
      this.setInterval((String) lUrlParam.getParam("interval"));
      // セグメント
      this.setSegmentFlg((String) lUrlParam.getParam("segmentFlg"));
    }
  }
  
  public boolean excelValidate(HttpServletRequest lRequest) {
    SICustomErrors errors = new SICustomErrors();
    SITableConditionManager lConditionMan = new SITableConditionManager();
    String appendSQL = "";
    
    if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_ORDER_NAME)) {//受注一覧表
      // 受注期間
      SICheckValid.checkValid(errors, "受注期間[日]From", this.getSeasonDayFromCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "受注期間[月]From", this.getSeasonMonthFromCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "受注期間[年]From", this.getSeasonYearFromCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "受注期間[日]To", this.getSeasonDayToCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "受注期間[月]To", this.getSeasonMonthToCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "受注期間[年]To", this.getSeasonYearToCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      if (errors.isEmpty()) {
        SICheckValid.checkValid(errors, "受注期間From", this.getSeasonFromCho(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
        SICheckValid.checkValid(errors, "受注期間To", this.getSeasonToCho(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
      }
      try {
        if (errors.isEmpty() && !SICheckUtil.dateEqual(this.getSeasonFromCho(), this.getSeasonToCho())) {
          SICheckValid.checkValid(errors, "受注期間From", "受注期間To", this.getSeasonFromCho(), this.getSeasonToCho(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
        }
      } catch (ParseException e) {
        e.printStackTrace();
      } catch (SIException e) {
        e.printStackTrace();
      }
      // 顧客コード チェック
      SICheckValid.checkValid(errors, "顧客コード ", this.getCustCode(), SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
      
      if (errors.isEmpty()) {// 受注時期OK
        // 受注時期
        //lConditionMan.add(new SITableCondition("a", "InitDateTime", this.getSeasonFromCho(), SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        //lConditionMan.add(new SITableCondition("a", "InitDateTime", this.getSeasonToCho() + " 23:59:59", SIConfig.SICONDITION_TYPE_LESS_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        appendSQL = " AND ((a.status=1 AND a.InitDateTime > "+SIDBUtil.SQL2Str(this.getSeasonFromCho())+" AND a.InitDateTime <= "+SIDBUtil.SQL2Str(this.getSeasonToCho()+" 23:59:59")+") ";
        appendSQL += "OR (a.status!=1 AND a.UpdateDateTime > "+SIDBUtil.SQL2Str(this.getSeasonFromCho())+" AND a.UpdateDateTime <= "+SIDBUtil.SQL2Str(this.getSeasonToCho()+" 23:59:59")+")) ";
        // 担当支店
        if (SIUtil.isNotNull(this.getBranchCode())) {
          lConditionMan.add(new SITableCondition("a", "branchcode", this.getBranchCode(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 担当者
        if (SIUtil.isNotNull(this.getChargeNameCdo())) {
          lConditionMan.add(new SITableCondition("a", "chargecode", this.getChargeNameCdo(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 明細(0有0・無1)
        if (this.getDetailFlgRdo().equals("1")) {// 明細無
          this.setDetailFlg(false);// 出力部で処理
        }
        // 顧客コード
        if (SIUtil.isNotNull(this.getCustCode())) {
          lConditionMan.add(new SITableCondition("a", "custCode", this.getCustCode(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 取引区分(通常1・キャンセル0・返品2)
        if (this.getOrderStatusRdo().equals("1") || this.getOrderStatusRdo().equals("0") || this.getOrderStatusRdo().equals("2")) {// 売上のみ
          lConditionMan.add(new SITableCondition("a", "orderStatus", this.getOrderStatusRdo(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 支払区分
        if (SIUtil.isNotNull(this.getPayMethodNameCbo())) {
          lConditionMan.add(new SITableCondition("a", "payMethodName", this.getPayMethodNameCbo(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 受注経路
        if (SIUtil.isNotNull(this.getOrderRoute())) {
          lConditionMan.add(new SITableCondition("a", "orderRoute", this.getOrderRoute(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 入金状況
        // 検索部に追加
        appendSQL += " AND a.ordercode NOT IN (SELECT ordercode FROM otherordertbl WHERE ordercode IS NOT NULL) ";//その他販売の売上を除く
      }
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_ORDER_DETAIL_NAME)) {//受注明細表
      // 受注期間
      SICheckValid.checkValid(errors, "受注期間[日]From", this.getSeasonDayFromCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "受注期間[月]From", this.getSeasonMonthFromCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "受注期間[年]From", this.getSeasonYearFromCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "受注期間[日]To", this.getSeasonDayToCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "受注期間[月]To", this.getSeasonMonthToCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "受注期間[年]To", this.getSeasonYearToCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      if (errors.isEmpty()) {
        SICheckValid.checkValid(errors, "受注期間From", this.getSeasonFromCho(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
        SICheckValid.checkValid(errors, "受注期間To", this.getSeasonToCho(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
      }
      try {
        if (errors.isEmpty() && !SICheckUtil.dateEqual(this.getSeasonFromCho(), this.getSeasonToCho())) {
          SICheckValid.checkValid(errors, "受注期間From", "受注期間To", this.getSeasonFromCho(), this.getSeasonToCho(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
        }
      } catch (ParseException e) {
        e.printStackTrace();
      } catch (SIException e) {
        e.printStackTrace();
      }
      
      // 顧客コードチェック
      SICheckValid.checkValid(errors, "顧客コード ", this.getCustCode(), SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
      
      if (errors.isEmpty()) {// 受注時期OK
        // 受注時期
        //lConditionMan.add(new SITableCondition("a", "InitDateTime", this.getSeasonFromCho(), SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        //lConditionMan.add(new SITableCondition("a", "InitDateTime", this.getSeasonToCho() + " 23:59:59", SIConfig.SICONDITION_TYPE_LESS_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        appendSQL = " AND ((a.InitDateTime > "+SIDBUtil.SQL2Str(this.getSeasonFromCho())+" AND a.InitDateTime <= "+SIDBUtil.SQL2Str(this.getSeasonToCho()+" 23:59:59")+") ";
        appendSQL += "OR (a.status!='1' AND a.UpdateDateTime > "+SIDBUtil.SQL2Str(this.getSeasonFromCho())+" AND a.UpdateDateTime <= "+SIDBUtil.SQL2Str(this.getSeasonToCho()+" 23:59:59")+")) ";
        // 担当支店
        if (SIUtil.isNotNull(this.getBranchCode())) {
          lConditionMan.add(new SITableCondition("a", "branchcode", this.getBranchCode(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 担当者
        if (SIUtil.isNotNull(this.getChargeNameCdo())) {
          lConditionMan.add(new SITableCondition("a", "chargecode", this.getChargeNameCdo(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 明細(有0・無1)
        if (this.getDetailFlgRdo().equals("1")) {// 明細無
          this.setDetailFlg(false);// 出力部で処理
        }
        // 顧客コード
        if (SIUtil.isNotNull(this.getCustCode())) {
          lConditionMan.add(new SITableCondition("a", "custCode", this.getCustCode(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 取引区分(通常1・キャンセル0・返品2)
        if (this.getOrderStatusRdo().equals("1") || this.getOrderStatusRdo().equals("0") || this.getOrderStatusRdo().equals("2")) {// 売上のみ
          lConditionMan.add(new SITableCondition("a", "orderStatus", this.getOrderStatusRdo(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 受注経路
        if (SIUtil.isNotNull(this.getOrderRoute())) {
          lConditionMan.add(new SITableCondition("a", "orderRoute", this.getOrderRoute(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 出荷状況
        if (SIUtil.isNotNull(this.getShippmentFlgRdo())) {
          if (this.getShippmentFlgRdo().equals("0")) {
            lConditionMan.add(new SITableCondition("b", "shippmentDate", "", SIConfig.SICONDITION_TYPE_IS_NULL, SIConfig.SICONDITION_TYPE_AND));
          } else if (this.getShippmentFlgRdo().equals("1")) {
            lConditionMan.add(new SITableCondition("b", "shippmentDate", "", SIConfig.SICONDITION_TYPE_IS_NOT_NULL, SIConfig.SICONDITION_TYPE_AND));
          } else if (this.getShippmentFlgRdo().equals("2")) {
            lConditionMan.add(new SITableCondition("dd", "deliveryOutputFlg", "", SIConfig.SICONDITION_TYPE_IS_NULL, SIConfig.SICONDITION_TYPE_AND));
          } else if (this.getShippmentFlgRdo().equals("3")) {
            lConditionMan.add(new SITableCondition("dd", "shippmentMailFlg", "0", SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
          }
        }
        // 中古・新品区分
        if (SIUtil.isNotNull(this.getUsedNewFlg())) {
          lConditionMan.add(new SITableCondition("cc", "usednewflg", this.getUsedNewFlg(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        appendSQL += " AND a.ordercode NOT IN (SELECT ordercode FROM otherordertbl WHERE ordercode IS NOT NULL) ";//その他販売の売上を除く
      }
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_INDIVIDUAL_NAME)) {//在庫明細票
      // 対象年月
      SICheckValid.checkValid(errors, "対象年From", this.getSeasonYearCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象月From", this.getSeasonMonthCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      // 支店
      SICheckValid.checkValid(errors, "支店", this.getBranchCode(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      // 在庫コードチェック
      SICheckValid.checkValid(errors, "在庫コード", this.getIndividualCode(), SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE);
      if (errors.isEmpty()) {
        // 出力対象と在庫管理はSQL文に追加
        // 支店
        lConditionMan.add(new SITableCondition("aa", "branchcode", this.getBranchCode(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        // 明細(有0・無1)
        if (this.getDetailFlgRdo().equals("1")) {// 明細無
          this.setDetailFlg(false);// 出力部で処理
        }
        // カテゴリ
        if (SIUtil.isNotNull(this.getCtgryCode())) {
          boolean searchFlg = true;
          for (int i = 0; i < SIConfig.CATEGORY.length; i++) {
            if (this.getCtgryCode().equalsIgnoreCase(SIConfig.CATEGORY[i][0])) {
              searchFlg = false;
              lConditionMan.add(new SITableCondition("aa", "category", SIConfig.CATEGORY[i][1], SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
            }
          }
          if (searchFlg) {// その他の商品
            for (int i = 0; i < SIConfig.CATEGORY.length; i++) {
              lConditionMan.add(new SITableCondition("aa", "category", SIConfig.CATEGORY[i][1], SIConfig.SICONDITION_TYPE_NOT_EQUAL, SIConfig.SICONDITION_TYPE_AND));
            }
          }
        }
        // 在庫コード
        if (SIUtil.isNotNull(this.getIndividualCode())) {
          lConditionMan.add(new SITableCondition("aa", "individualcode", this.getIndividualCode(), SIConfig.SICONDITION_TYPE_FRONTLIKE, SIConfig.SICONDITION_TYPE_AND));
        }
      }
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SALES_NAME)) {//売上明細ステータス
      // 受注期間
      SICheckValid.checkValid(errors, "出荷期間[日]From", this.getShipmentDateDayFromCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "出荷期間[月]From", this.getShipmentDateMonthFromCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "出荷期間[年]From", this.getShipmentDateYearFromCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "出荷期間[日]To", this.getShipmentDateDayToCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "出荷期間[月]To", this.getShipmentDateMonthToCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "出荷期間[年]To", this.getShipmentDateYearToCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      // 在庫コードチェック
      SICheckValid.checkValid(errors, "在庫コード", this.getIndividualCode(), SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE);
      if (errors.isEmpty()) {// 受注時期OK
        SICheckValid.checkValid(errors, "出荷期間From", this.getShipmentDateFromCho(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
        SICheckValid.checkValid(errors, "出荷期間To", this.getShipmentDateToCho(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
        try {
          if (!SICheckUtil.dateEqual(this.getShipmentDateFromCho(), this.getShipmentDateToCho())) {
            SICheckValid.checkValid(errors, "出荷期間From", "出荷期間To", this.getShipmentDateFromCho(), this.getShipmentDateToCho(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
          }
        } catch (ParseException e) {
          e.printStackTrace();
        } catch (SIException e) {
          e.printStackTrace();
        }
        if (SIUtil.isNotNull(this.getSeasonFromCho())) {
          SICheckValid.checkValid(errors, "受注期間From", this.getSeasonFromCho(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
        }
        if (SIUtil.isNotNull(this.getSeasonToCho())) {
          SICheckValid.checkValid(errors, "受注期間To", this.getSeasonToCho(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
        }
        if (SIUtil.isNotNull(this.getSeasonFromCho()) && SIUtil.isNotNull(this.getSeasonToCho())) {
          try {
            if (!SICheckUtil.dateEqual(this.getSeasonFromCho(), this.getSeasonToCho())) {
              SICheckValid.checkValid(errors, "受注期間From", "受注期間To", this.getSeasonFromCho(), this.getSeasonToCho(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
            }
          } catch (ParseException e) {
            e.printStackTrace();
          } catch (SIException e) {
            e.printStackTrace();
          }
        }
        // 担当支店
        if (SIUtil.isNotNull(this.getBranchCode())) {
          lConditionMan.add(new SITableCondition("a", "branchcode", this.getBranchCode(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 担当者
        if (SIUtil.isNotNull(this.getChargeNameCdo())) {
          lConditionMan.add(new SITableCondition("a", "chargecode", this.getChargeNameCdo(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 明細(有0・無1)
        if (this.getDetailFlgRdo().equals("1")) {// 明細無
          this.setDetailFlg(false);// 出力部で処理
        }
        // 顧客コード
        if (SIUtil.isNotNull(this.getCustCode())) {
          lConditionMan.add(new SITableCondition("a", "custCode", this.getCustCode(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 取引区分(通常0・返品1)
        if (this.getOrderStatusRdo().equals("1") || this.getOrderStatusRdo().equals("0")) {// 売上のみ
          lConditionMan.add(new SITableCondition("a", "orderStatus", this.getOrderStatusRdo(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 受注経路
        if (SIUtil.isNotNull(this.getOrderRoute())) {
          lConditionMan.add(new SITableCondition("a", "orderRoute", this.getOrderRoute(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 中古・新品区分
        if (SIUtil.isNotNull(this.getUsedNewFlg())) {
          lConditionMan.add(new SITableCondition("cc", "usednewflg", this.getUsedNewFlg(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 在庫コード
        if (SIUtil.isNotNull(this.getIndividualCode())) {
          lConditionMan.add(new SITableCondition("b", "IndividualCode", this.getIndividualCode(), SIConfig.SICONDITION_TYPE_FRONTLIKE, SIConfig.SICONDITION_TYPE_AND));
        }
        appendSQL += " AND a.ordercode NOT IN (SELECT ordercode FROM otherordertbl WHERE ordercode IS NOT NULL) ";//その他販売の売上を除く
      }
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_INVENTORYSTOCK_NAME)) {//棚卸差異表
      // 対象年月
      SICheckValid.checkValid(errors, "対象年From", this.getSeasonYearCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象月From", this.getSeasonMonthCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      // 支店
      SICheckValid.checkValid(errors, "支店", this.getBranchCode(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      // 在庫コードチェック
      SICheckValid.checkValid(errors, "在庫コード", this.getIndividualCode(), SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE);
      // 棚卸はパフォーマンスのため、SQL文に直接検索ロジックを入れる
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_INSENTIVE_NAME)) {//インセンティブ対象売上一覧表
      // 受注期間
      SICheckValid.checkValid(errors, "受注期間[日]From", this.getSeasonDayFromCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "受注期間[月]From", this.getSeasonMonthFromCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "受注期間[年]From", this.getSeasonYearFromCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "受注期間[日]To", this.getSeasonDayToCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "受注期間[月]To", this.getSeasonMonthToCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "受注期間[年]To", this.getSeasonYearToCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      // 顧客コード チェック
      SICheckValid.checkValid(errors, "顧客コード ", this.getCustCode(), SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
      // 在庫コードチェック
      SICheckValid.checkValid(errors, "在庫コード", this.getIndividualCode(), SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE);
      if (errors.isEmpty()) {
        SICheckValid.checkValid(errors, "受注期間From", this.getSeasonFromCho(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
        SICheckValid.checkValid(errors, "受注期間To", this.getSeasonToCho(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
      }
      try {
        if (errors.isEmpty() && !SICheckUtil.dateEqual(this.getSeasonFromCho(), this.getSeasonToCho())) {
          SICheckValid.checkValid(errors, "受注期間From", "受注期間To", this.getSeasonFromCho(), this.getSeasonToCho(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
        }
      } catch (ParseException e) {
        e.printStackTrace();
      } catch (SIException e) {
        e.printStackTrace();
      }
      if (errors.isEmpty()) {
        SICheckValid.checkValid(errors, "出荷期間From", this.getShipmentDateFromCho(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
        SICheckValid.checkValid(errors, "出荷期間To", this.getShipmentDateToCho(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
      }
      try {
        if (errors.isEmpty() && !SICheckUtil.dateEqual(this.getShipmentDateFromCho(), this.getShipmentDateToCho())) {
          SICheckValid.checkValid(errors, "出荷期間From", "出荷期間To", this.getShipmentDateFromCho(), this.getShipmentDateToCho(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
        }
      } catch (ParseException e) {
        e.printStackTrace();
      } catch (SIException e) {
        e.printStackTrace();
      }
      if (errors.isEmpty()) {
        // 受注時期
        lConditionMan.add(new SITableCondition("a", "InitDateTime", this.getSeasonFromCho(), SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        lConditionMan.add(new SITableCondition("a", "InitDateTime", this.getSeasonToCho() + " 23:59:59", SIConfig.SICONDITION_TYPE_LESS_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        // 担当支店
        if (SIUtil.isNotNull(this.getBranchCode())) {
          lConditionMan.add(new SITableCondition("a", "branchcode", this.getBranchCode(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 担当者
        if (SIUtil.isNotNull(this.getChargeNameCdo())) {
          lConditionMan.add(new SITableCondition("a", "chargecode", this.getChargeNameCdo(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 明細(有0・無1)
        if (this.getDetailFlgRdo().equals("1")) {// 明細無
          this.setDetailFlg(false);// 出力部で処理
        }
        // 顧客コード
        if (SIUtil.isNotNull(this.getCustCode())) {
          lConditionMan.add(new SITableCondition("a", "custCode", this.getCustCode(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 在庫コード
        if (SIUtil.isNotNull(this.getIndividualCode())) {
          lConditionMan.add(new SITableCondition("b", "IndividualCode", this.getIndividualCode(), SIConfig.SICONDITION_TYPE_FRONTLIKE, SIConfig.SICONDITION_TYPE_AND));
        }
        // 取引区分(通常1・キャンセル0・返品2)
        if (this.getOrderStatusRdo().equals("1") || this.getOrderStatusRdo().equals("0") || this.getOrderStatusRdo().equals("2")) {// 売上のみ
          lConditionMan.add(new SITableCondition("a", "orderStatus", this.getOrderStatusRdo(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 受注経路
        if (SIUtil.isNotNull(this.getOrderRoute())) {
          lConditionMan.add(new SITableCondition("a", "orderRoute", this.getOrderRoute(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        // 中古・新品区分
        if (SIUtil.isNotNull(this.getUsedNewFlg())) {
          lConditionMan.add(new SITableCondition("cc", "usednewflg", this.getUsedNewFlg(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
        }
        appendSQL += " AND a.ordercode NOT IN (SELECT ordercode FROM otherordertbl WHERE ordercode IS NOT NULL) ";//その他販売の売上を除く
      }
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_STOREHISTORY_NAME)) {//入出庫一覧表
      // 対象年月
      SICheckValid.checkValid(errors, "対象年From", this.getSeasonYearCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象月From", this.getSeasonMonthCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      // 在庫コードチェック
      SICheckValid.checkValid(errors, "在庫コード", this.getIndividualCode(), SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE);
      if (errors.isEmpty()) {
        // カテゴリ
        if (SIUtil.isNotNull(this.getCtgryCode())) {
          boolean searchFlg = true;
          for (int i = 0; i < SIConfig.CATEGORY.length; i++) {
            if (this.getCtgryCode().equalsIgnoreCase(SIConfig.CATEGORY[i][0])) {
              searchFlg = false;
              lConditionMan.add(new SITableCondition("b", "category", SIConfig.CATEGORY[i][1], SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
            }
          }
          if (searchFlg) {// その他の商品
            for (int i = 0; i < SIConfig.CATEGORY.length; i++) {
              lConditionMan.add(new SITableCondition("b", "category", SIConfig.CATEGORY[i][1], SIConfig.SICONDITION_TYPE_NOT_EQUAL, SIConfig.SICONDITION_TYPE_AND));
            }
          }
        }
        if (SIUtil.isNotNull(this.getIndividualCode())) {
          lConditionMan.add(new SITableCondition("b", "individualcode", this.getIndividualCode(), SIConfig.SICONDITION_TYPE_FRONTLIKE, SIConfig.SICONDITION_TYPE_AND));
        }
      }
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_MARKETINGREPORT_NAME)||this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_MARKETINGREPORT_NAME0)||this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_TRENDREPORT_NAME)) {//マーケティングレポート・トレンドレポート
      MR.validate(lRequest, errors);
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SALES_STATUS_NAME)||this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SALES_STATUS_NAME0)) {
      SS.validate(lRequest, errors);
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SALES_STATUS_ALL_NAME)||this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SALES_STATUS_ALL_NAME0)) {
      SICheckValid.checkValid(errors, "対象年", SS.getYear(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象月", SS.getMonth(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SEGMENT_STATUS_NAME)||this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SIEXCEL_SEGMENT_STATUS_NAME0)) {
      SE.validate(lRequest, errors);
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SICSV_HACCHU_PAYMENT_NAME)) {
      // 対象年月
      SICheckValid.checkValid(errors, "対象年", this.getSeasonYearCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象月", this.getSeasonMonthCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      
      if (errors.isEmpty()) {
        try{
          SIDateTime lDateFrom = new SIDateTime(this.getSeasonYearCbo()+"/"+this.getSeasonMonthCbo()+"/01",SIConfig.SIDATE_FORMAT);
          SIDateTime lDateTo = new SIDateTime(this.getSeasonYearCbo()+"/"+this.getSeasonMonthCbo()+"/01",SIConfig.SIDATE_FORMAT);
          lDateTo.addMonth(1);
          lConditionMan.add(new SITableCondition("a", "shippmentdate", lDateFrom.getFullDate(), SIConfig.SICONDITION_TYPE_GREATER_EQUAL, SIConfig.SICONDITION_TYPE_AND));
          lConditionMan.add(new SITableCondition("a", "shippmentdate", lDateTo.getFullDate(), SIConfig.SICONDITION_TYPE_LESS, SIConfig.SICONDITION_TYPE_AND));
          
          if (SIUtil.isNotNull(this.getBranchCode())) {
            lConditionMan.add(new SITableCondition("c", "belongingbranchcode", this.getBranchCode(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
          }
          if (SIUtil.isNotNull(this.getChargeNameCdo())) {
            lConditionMan.add(new SITableCondition("c", "chargecode", this.getChargeNameCdo(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
          }
          if (SIUtil.isNotNull(this.getStockCodeCbo())) {
            lConditionMan.add(new SITableCondition("a", "stockcode", this.getStockCodeCbo(), SIConfig.SICONDITION_TYPE_EQUAL, SIConfig.SICONDITION_TYPE_AND));
          }
        }catch(Exception e){}
      }
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SICSV_HISTORY_DETAIL_NAME)) {
      // 対象年月
      //SICheckValid.checkValid(errors, "対象年", this.getSeasonYearCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      //SICheckValid.checkValid(errors, "対象月", this.getSeasonMonthCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      
      // 対象期間
      SICheckValid.checkValid(errors, "対象期間[日]From", this.getSeasonDayFromCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[月]From", this.getSeasonMonthFromCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[年]From", this.getSeasonYearFromCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[日]To", this.getSeasonDayToCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[月]To", this.getSeasonMonthToCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[年]To", this.getSeasonYearToCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      
      if (errors.isEmpty()) {
        SICheckValid.checkValid(errors, "対象期間From", this.getSeasonFromCho(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
        SICheckValid.checkValid(errors, "対象期間To", this.getSeasonToCho(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
      }
      try {
        if (errors.isEmpty() && !SICheckUtil.dateEqual(this.getSeasonFromCho(), this.getSeasonToCho())) {
          SICheckValid.checkValid(errors, "対象期間From", "対象期間To", this.getSeasonFromCho(), this.getSeasonToCho(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
        }
      } catch (ParseException e) {
        e.printStackTrace();
      } catch (SIException e) {
        e.printStackTrace();
      }
      
      //入庫区分・出庫区分
      if ((this.getStoreType() == null||this.getStoreType().length == 0) && (this.getShipType() == null||this.getShipType().length == 0)) {
        errors.addError(new SICustomError("manager.message.freeword","入庫区分か出庫区分を１つ以上選択してください。"));
      }else if (errors.isEmpty()){
        boolean saleFlg = false;
        SIDateTime date = new SIDateTime();
        for (int i=0;i<this.getShipType().length;i++) {
          saleFlg = saleFlg || this.getShipType()[i].equals("0");
        }
        if (saleFlg) {
          try{
            date = new SIDateTime(this.getSeasonYearFromCbo() + "/" + this.getSeasonMonthFromCbo() + "/" + this.getSeasonDayFromCbo(),SIConfig.SIDATE_FORMAT0);
            date.addMonth(3);
          }catch(Exception e){}
          SICheckValid.checkValid(errors, "対象期間To", "対象期間From＋3ヶ月", this.getSeasonToCho(), date.getFullDate(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
        } else {
          try{
            date = new SIDateTime(this.getSeasonYearFromCbo() + "/" + this.getSeasonMonthFromCbo() + "/" + this.getSeasonDayFromCbo(),SIConfig.SIDATE_FORMAT0);
            date.addYear(1);
          }catch(Exception e){}
          SICheckValid.checkValid(errors, "対象期間To", "対象期間From＋1年", this.getSeasonToCho(), date.getFullDate(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
        }
      }
    } else if (this.getTblNameSel().equalsIgnoreCase(SIExcelConf.SICSV_DEADSTOCK_LIST_NAME)) {
      // 対象年月
      SICheckValid.checkValid(errors, "対象年", this.getSeasonYearCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象月", this.getSeasonMonthCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
    }
    // 条件文の設定
    this.conditionSQL = lConditionMan.getCondtionSQL() + appendSQL;
    
    if (!errors.isEmpty()) lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
    return errors.isEmpty();
  }
  
  /**
   * <b>getOrderListCollection</b> 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * 
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws なし
   */
  public Collection getOrderListCollection(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIOrder lBasic = new SIOrder();
    Collection lResultColl = new ArrayList();
    StringBuffer lSqlBuf = new StringBuffer();
    StringBuffer lCommonBuf = new StringBuffer();
    
    if (SIUtil.isNotNull(this.getReceiptFlgRdo())) {
      if (this.getReceiptFlgRdo().equals("0")) {// 未入金
        lCommonBuf.append(" AND COALESCE(c.TOTALPAYMENTPRICE,0) = 0 ");
      } else if (this.getReceiptFlgRdo().equals("1")) {// 一部入金
        lCommonBuf.append(" AND COALESCE(c.TOTALPAYMENTPRICE,0) != 0 ");
        lCommonBuf.append(" AND COALESCE(c.TOTALPAYMENTPRICE,0) < ");
        lCommonBuf.append(" a.totalofprice + a.totalofdeliveryfee + a.totaloffee ");
        lCommonBuf.append(" + COALESCE(a.discountfee,0) + COALESCE(a.discountdeliveryfee,0) ");
        //EDBTG003-00 ohsugi mod start
//        lCommonBuf.append(" - a.sumofdiscount - a.sumbypoint ");
        lCommonBuf.append(" - a.sumofdiscount - a.setdiscount - a.sumbypoint ");
        //EDBTG003-00 ohsugi mod end
      } else if (this.getReceiptFlgRdo().equals("2")) {// 入金完了
        lCommonBuf.append(" AND COALESCE(c.TOTALPAYMENTPRICE,0) != 0 ");
        lCommonBuf.append(" AND COALESCE(c.TOTALPAYMENTPRICE,0) >= ");
        lCommonBuf.append(" a.totalofprice + a.totalofdeliveryfee + a.totaloffee ");
        lCommonBuf.append(" + COALESCE(a.discountfee,0) + COALESCE(a.discountdeliveryfee,0) ");
        //EDBTG003-00 ohsugi mod start
//        lCommonBuf.append(" - a.sumofdiscount - a.sumbypoint ");
        lCommonBuf.append(" - a.sumofdiscount - a.setdiscount - a.sumbypoint ");
        //EDBTG003-00 ohsugi mod end
      }
    }
    lSqlBuf.append("SELECT a.OrderCode");// 受注番号
    lSqlBuf.append(",a.CustCode");// 顧客コード
    lSqlBuf.append(",a.CustName");// 顧客名
    lSqlBuf.append(",a.PayMethodName");// 支払区分
    lSqlBuf.append(",(COALESCE(a.totaloffee,0) + COALESCE(a.DiscountFee,0)) AS Fee");// 代引手数料
    //EDBTG003-00 ohsugi mod start
//    lSqlBuf.append(",-(a.SumOfDiscount) AS SumOfDiscount");// 値引金額
    lSqlBuf.append(",-(a.SumOfDiscount + a.setdiscount) AS SumOfDiscount");// 値引金額
    //EDBTG003-00 ohsugi mod end
    lSqlBuf.append(",-(a.SumByPoint) AS SumByPoint");// ポイント使用額
    lSqlBuf.append(",a.orderstatus");
    lSqlBuf.append(",a.OrderRoute");// 受注経路
    lSqlBuf.append(",a.Status");
    lSqlBuf.append(",a.InitDateTime");// 受注年月日
    lSqlBuf.append(",a.UpdateDateTime");// 受注更新日
    lSqlBuf.append(",aa.BranchName");// 担当支店
    lSqlBuf.append(",a.branchcode");
    lSqlBuf.append(",bb.ChargeName");// 担当者
    lSqlBuf.append(",a.totalofprice");// 販売金額
    lSqlBuf.append(",a.pointenableflg");
    lSqlBuf.append(",COALESCE(a.shippmenttotal,0) AS totalShippmentPrice");// 出荷合計額
    lSqlBuf.append(",COALESCE(c.totalpaymentprice,0) AS totalPaymentPrice");// 入金合計額
    lSqlBuf.append(",(COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0)) AS totalOfDeliveryFee");
    lSqlBuf.append(",(COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0) + a.totalofprice) AS salesPriceInTax");
    lSqlBuf.append(",CEIL((COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0) + a.totalofprice) / (1.0 + a.taxrate / 100.0)) AS salesPrice");
    lSqlBuf.append(",((COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0) + a.totalofprice) - CEIL((COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0) + a.totalofprice) / (1.0 + a.taxrate / 100.0))) AS taxOfPrice ");
    lSqlBuf.append("FROM (SELECT sum.*,0 AS orderStatus FROM ordersumvw AS sum ");
    lSqlBuf.append("WHERE sum.InitDateTime >= ").append(SIDBUtil.SQL2Str(this.getSeasonFromCho(), " "));
    lSqlBuf.append("AND sum.InitDateTime <= ").append(SIDBUtil.SQL2Str(this.getSeasonToCho() + " 23:59:59", " ) AS a "));
    lSqlBuf.append("LEFT OUTER JOIN BranchTbl AS aa ON a.branchcode = aa.branchcode ");
    lSqlBuf.append("LEFT OUTER JOIN ChargeTbl AS bb ON a.chargecode = bb.chargecode ");
    lSqlBuf.append("LEFT OUTER JOIN (SELECT paymenttbl.ordercode, sum(paymenttbl.paymentprice) AS totalpaymentprice FROM paymenttbl ");
    lSqlBuf.append("WHERE paymenttbl.paymentdate IS NOT NULL AND paymenttbl.status <> 1::numeric GROUP BY paymenttbl.ordercode) AS c ");
    lSqlBuf.append("ON c.ordercode = a.ordercode ");
    lSqlBuf.append("WHERE 1=1 ");
    lSqlBuf.append(this.conditionSQL);// 検索項目
    if (SIUtil.isNotNull(this.getReceiptFlgRdo())) {// 入金状態
      lSqlBuf.append(lCommonBuf.toString());
    }
    StringBuffer lSqlBuf2 = new StringBuffer();
    lSqlBuf2.append(" UNION SELECT a.OrderCode");// 受注番号
    lSqlBuf2.append(",a.CustCode");// 顧客コード
    lSqlBuf2.append(",a.CustName");// 顧客名
    lSqlBuf2.append(",a.PayMethodName");// 支払区分
    lSqlBuf2.append(",(COALESCE(a.totaloffee,0) + COALESCE(a.DiscountFee,0)) AS Fee");// 代引手数料
    //EDBTG003-00 ohsugi mod start
//    lSqlBuf2.append(",(a.SumOfDiscount) AS SumOfDiscount");// 値引金額
    lSqlBuf2.append(",(a.SumOfDiscount + a.setdiscount) AS SumOfDiscount");// 値引金額
    //EDBTG003-00 ohsugi mod end
    lSqlBuf2.append(",(a.SumByPoint) AS SumByPoint");// ポイント使用額
    lSqlBuf2.append(",a.orderstatus");
    lSqlBuf2.append(",a.OrderRoute");// 受注経路
    lSqlBuf2.append(",a.Status");
    lSqlBuf2.append(",a.InitDateTime");// 受注年月日
    lSqlBuf2.append(",a.UpdateDateTime");// 受注更新日
    lSqlBuf2.append(",aa.BranchName");// 担当支店
    lSqlBuf2.append(",a.branchcode");
    lSqlBuf2.append(",bb.ChargeName");// 担当者
    lSqlBuf2.append(",-a.totalofprice");// 販売金額
    lSqlBuf2.append(",a.pointenableflg");
    lSqlBuf2.append(",-COALESCE(a.shippmenttotal,0) AS totalShippmentPrice");// 出荷合計額
    lSqlBuf2.append(",-COALESCE(c.totalpaymentprice,0) AS totalPaymentPrice");// 入金合計額
    lSqlBuf2.append(",-(COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0)) AS totalOfDeliveryFee");
    lSqlBuf2.append(",-(COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0) + a.totalofprice) AS salesPriceInTax");
    lSqlBuf2.append(",-(CEIL((COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0) + a.totalofprice) / (1.0 + a.taxrate / 100.0))) AS salesPrice");
    lSqlBuf2
        .append(",-((COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0) + a.totalofprice) - CEIL((COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0) + a.totalofprice) / (1.0 + a.taxrate / 100.0))) AS taxOfPrice ");
    lSqlBuf2.append("FROM (SELECT sum.*,(CASE WHEN sum.Status='0' THEN 1 WHEN sum.Status='2' THEN 2 END) AS orderStatus FROM ordersumvw AS sum ");
    lSqlBuf2.append("WHERE sum.UpdateDateTime >= ").append(SIDBUtil.SQL2Str(this.getSeasonFromCho(), " "));
    lSqlBuf2.append("AND sum.UpdateDateTime <= ").append(SIDBUtil.SQL2Str(this.getSeasonToCho() + " 23:59:59", " "));
    lSqlBuf2.append("AND sum.Status != '1') AS a ");
    lSqlBuf2.append("LEFT OUTER JOIN BranchTbl AS aa ON a.branchcode = aa.branchcode ");
    lSqlBuf2.append("LEFT OUTER JOIN ChargeTbl AS bb ON a.chargecode = bb.chargecode ");
    lSqlBuf2.append("LEFT OUTER JOIN (SELECT paymenttbl.ordercode,sum(paymenttbl.paymentprice) AS totalpaymentprice FROM paymenttbl ");
    lSqlBuf2.append("WHERE paymenttbl.paymentdate IS NOT NULL AND paymenttbl.status != '1' GROUP BY paymenttbl.ordercode) AS c ");
    lSqlBuf2.append("ON c.ordercode = a.ordercode ");
    lSqlBuf2.append("WHERE 1=1 ");
    lSqlBuf2.append(this.conditionSQL);// 検索項目
    if (SIUtil.isNotNull(this.getReceiptFlgRdo())) {// 入金状態
      lSqlBuf2.append(lCommonBuf.toString());
    }
    lSqlBuf2.append(" ORDER BY OrderCode,orderStatus");
    lSqlBuf.append(lSqlBuf2.toString());
    log.debug("lSqlBuf=" + lSqlBuf.toString());
    this.selectSQL = lSqlBuf.toString();
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      // 受注レコードセットの作成
      while (lResultSet.next()) {
        log.debug("受注一覧表：SQL実行");
        lBasic = new SIOrder();
        lBasic.setOrderCode(lResultSet.getString("orderCode"));// 受注番号
        lBasic.setCustCode(lResultSet.getString("custCode"));// 顧客コード
        lBasic.setCustName(lResultSet.getString("custName"));// 顧客名
        lBasic.setPayMethodName(lResultSet.getString("payMethodName"));
        lBasic.setTotalOfFee(lResultSet.getString("fee"));// 手数料(必須)
        lBasic.setSumOfDiscount(lResultSet.getString("SumOfDiscount"));
        lBasic.setSumByPoint(lResultSet.getString("SumByPoint"));
        lBasic.setPointEnableFlg(lResultSet.getString("pointenableflg"));
        lBasic.setOrderRoute(lResultSet.getString("OrderRoute"));
        lBasic.setStatus(lResultSet.getString("Status"));
        lBasic.setOrderStatus(lResultSet.getString("orderStatus"));
        lBasic.setInitDateTime(SIDBUtil.getDateTime(lResultSet.getTimestamp("InitDateTime")));
        lBasic.setUpdateDateTime(SIDBUtil.getDateTime(lResultSet.getTimestamp("UpdateDateTime")));
        lBasic.setBranchName(lResultSet.getString("BranchName"));
        lBasic.setBranchCode(lResultSet.getString("BranchCode"));
        lBasic.setChargeName(lResultSet.getString("ChargeName"));
        lBasic.setTotalOfPrice(lResultSet.getString("totalOfPrice"));
        lBasic.setTotalShippmentPrice(lResultSet.getString("totalShippmentPrice"));
        lBasic.setTotalPaymentPrice(lResultSet.getString("totalPaymentPrice"));
        lBasic.setTotalOfDeliveryFee(lResultSet.getString("totalOfDeliveryFee"));
        lBasic.setSalesPriceInTax(lResultSet.getString("salesPriceInTax"));
        lBasic.setSalesPrice(lResultSet.getString("salesPrice"));
        lBasic.setTaxOfPrice(lResultSet.getString("taxOfPrice"));
        lResultColl.add(lBasic);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lResultColl;
  }
  
  /**
   * <b>getOrderDetailCollection</b> 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * 
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws なし
   */
  public Collection getOrderDetailCollection(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIOrder lBasic = new SIOrder();
    StringBuffer lSqlBuf = new StringBuffer();
    Collection lResultColl = new ArrayList();
    lSqlBuf.append("SELECT a.OrderCode");// 受注番号
    lSqlBuf.append(",a.CustCode");// 顧客コード
    lSqlBuf.append(",a.CustName");// 顧客名
    lSqlBuf.append(",(COALESCE(a.totaloffee,0) + COALESCE(a.DiscountFee,0)) AS Fee");// 代引手数料
    //EDBTG003-00 ohsugi mod start
//    lSqlBuf.append(",-(a.SumOfDiscount) AS SumOfDiscount");// 値引金額
    lSqlBuf.append(",-(a.SumOfDiscount + a.setdiscount) AS SumOfDiscount");// 値引金額
    //EDBTG003-00 ohsugi mod end
    lSqlBuf.append(",-(a.SumByPoint) AS SumByPoint");// ポイント使用額
    lSqlBuf.append(",a.pointenableflg");
    lSqlBuf.append(",a.OrderRoute");// 受注経路
    lSqlBuf.append(",a.orderStatus");// 取引区分の順番のため
    lSqlBuf.append(",a.Status");
    lSqlBuf.append(",a.InitDateTime");// 受注年月日
    lSqlBuf.append(",a.UpdateDateTime");// 受注更新日
    lSqlBuf.append(",aa.BranchName");// 担当支店
    lSqlBuf.append(",a.branchcode");
    lSqlBuf.append(",bb.ChargeName");// 担当者
    lSqlBuf.append(",a.totalofprice");// 販売金額
    lSqlBuf.append(",b.IndividualCode");// 在庫コード
    lSqlBuf.append(",cc.usednewflg");
    lSqlBuf.append(",cc.consignGoodsFlg");
    lSqlBuf.append(",b.CmdtyName");// 商品名
    lSqlBuf.append(",b.CmdtyCode");// 親コード
    lSqlBuf.append(",b.ShippmentDate");
    lSqlBuf.append(",dd.deliveryOutputFlg");
    lSqlBuf.append(",dd.shippmentMailFlg");
    lSqlBuf.append(",b.Amount");// 数量
    lSqlBuf.append(",b.Price");// 購入価格
    lSqlBuf.append(",(COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0)) AS totalOfDeliveryFee");
    lSqlBuf.append(",b.cmdtyPriceInTax as salesPriceInTax");// 売上金額（税込）
    lSqlBuf.append(",CEIL(b.cmdtyPriceInTax / (1.0 + a.taxrate / 100.0)) as salesPrice");
    lSqlBuf.append(",(b.cmdtyPriceInTax -CEIL(b.cmdtyPriceInTax / (1.0 + a.taxrate / 100.0))) as taxPrice ");// 消費税
    lSqlBuf.append("FROM (SELECT sum.pointenableflg,sum.OrderCode,sum.CustCode,sum.CustName,sum.totaloffee,sum.DiscountFee");
    lSqlBuf.append(",sum.SumOfDiscount,sum.SumByPoint,sum.OrderRoute,sum.Status,sum.InitDateTime,sum.UpdateDateTime");
    lSqlBuf.append(",sum.branchcode,0 AS orderStatus,sum.totalofprice,sum.DiscountDeliveryFee,sum.totalOfDeliveryFee,sum.taxrate,sum.chargecode ");
    lSqlBuf.append(",sum.setdiscount ");//EDBTG003-00 ohsugi add
    lSqlBuf.append("FROM ordersumvw AS sum ");
    lSqlBuf.append("WHERE sum.InitDateTime >= ").append(SIDBUtil.SQL2Str(this.getSeasonFromCho(), " "));
    lSqlBuf.append("AND sum.InitDateTime <= ").append(SIDBUtil.SQL2Str(this.getSeasonToCho() + " 23:59:59", ") AS a"));
    lSqlBuf.append(",(SELECT detail.branchcode,detail.chargecode,detail.ordercode,detail.IndividualCode,detail.CmdtyName,detail.CmdtyCode");
    lSqlBuf.append(",detail.ShippmentDate,detail.Price,detail.amount,detail.Amount*detail.Price AS cmdtyPriceInTax,detail.deliveryCode ");
    lSqlBuf.append("FROM orderdetaillatestvw AS detail) AS b ");
    lSqlBuf.append("LEFT OUTER JOIN BranchTbl AS aa ON b.branchcode = aa.branchcode ");
    lSqlBuf.append("LEFT OUTER JOIN ChargeTbl AS bb ON b.chargecode = bb.chargecode ");
    lSqlBuf.append(",(SELECT i.consignGoodsFlg,i.individualCode,i.CmdtyCode,i.usednewflg FROM individualtbl AS i ) AS cc");
    lSqlBuf.append(",ORDERDELIVERYLATESTVW AS dd ");
    lSqlBuf.append("WHERE a.OrderCode = b.OrderCode ");
    lSqlBuf.append("AND b.individualCode = cc.individualCode ");
    lSqlBuf.append("AND b.CmdtyCode = cc.CmdtyCode ");
    lSqlBuf.append("AND b.OrderCode = dd.OrderCode AND b.deliveryCode = dd.deliveryCode ");
    // 検索条件
    lSqlBuf.append(conditionSQL);
    StringBuffer lSqlBuf2 = new StringBuffer();
    lSqlBuf2.append(" UNION SELECT a.OrderCode");// 受注番号
    lSqlBuf2.append(",a.CustCode");// 顧客コード
    lSqlBuf2.append(",a.CustName");// 顧客名
    lSqlBuf2.append(",-(COALESCE(a.totaloffee,0) + COALESCE(a.DiscountFee,0)) AS Fee");// 代引手数料
    //EDBTG003-00 ohsugi mod start
//    lSqlBuf2.append(",(a.SumOfDiscount) AS SumOfDiscount");// 値引金額
    lSqlBuf2.append(",(a.SumOfDiscount + a.setdiscount) AS SumOfDiscount");// 値引金額
    //EDBTG003-00 ohsugi mod end
    lSqlBuf2.append(",(a.SumByPoint) AS SumByPoint");// ポイント使用額
    lSqlBuf2.append(",a.pointenableflg");
    lSqlBuf2.append(",a.OrderRoute");// 受注経路
    lSqlBuf2.append(",a.orderStatus");// 取引区分の順番のため
    lSqlBuf2.append(",a.Status");
    lSqlBuf2.append(",a.InitDateTime");// 受注年月日
    lSqlBuf2.append(",a.UpdateDateTime");// 受注更新日
    lSqlBuf2.append(",aa.BranchName");// 担当支店
    lSqlBuf2.append(",a.branchcode");
    lSqlBuf2.append(",bb.ChargeName");// 担当者
    lSqlBuf2.append(",a.totalofprice");// 販売金額
    lSqlBuf2.append(",b.IndividualCode");// 在庫コード
    lSqlBuf2.append(",cc.usednewflg");
    lSqlBuf2.append(",cc.consignGoodsFlg");
    lSqlBuf2.append(",b.CmdtyName");// 商品名
    lSqlBuf2.append(",b.CmdtyCode");// 親コード
    lSqlBuf2.append(",b.ShippmentDate");
    lSqlBuf2.append(",dd.deliveryOutputFlg");
    lSqlBuf2.append(",dd.shippmentMailFlg");
    lSqlBuf2.append(",b.Amount");// 数量
    lSqlBuf2.append(",-b.Price");// 購入価格
    lSqlBuf2.append(",-(COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0)) AS totalOfDeliveryFee");
    lSqlBuf2.append(",-(b.cmdtyPriceInTax) AS salesPriceInTax");// 売上金額（税込）
    lSqlBuf2.append(",-CEIL(b.cmdtyPriceInTax / (1.0 + a.taxrate / 100.0)) AS salesPrice");
    lSqlBuf2.append(",-(b.cmdtyPriceInTax -CEIL(b.Amount*b.Price / (1.0 + a.taxrate / 100.0))) AS taxPrice ");// 消費税
    lSqlBuf2.append("FROM (SELECT sum.pointenableflg,sum.OrderCode,sum.CustCode,sum.CustName,sum.totaloffee,sum.DiscountFee");
    lSqlBuf2.append(",sum.SumOfDiscount,sum.SumByPoint,sum.OrderRoute,sum.Status,sum.InitDateTime,sum.UpdateDateTime,sum.branchcode");
    lSqlBuf2.append(",(CASE WHEN sum.Status='0' THEN 1 WHEN sum.Status='2' THEN 2 END) AS orderStatus");
    lSqlBuf2.append(",sum.totalofprice,sum.DiscountDeliveryFee,sum.totalOfDeliveryFee,sum.taxrate,sum.chargecode ");
    lSqlBuf2.append(",sum.setdiscount ");//EDBTG003-00 ohsugi add
    lSqlBuf2.append("FROM ordersumvw AS sum WHERE sum.UpdateDateTime >= ").append(SIDBUtil.SQL2Str(this.getSeasonFromCho(), " "));
    lSqlBuf2.append("AND sum.UpdateDateTime <= ").append(SIDBUtil.SQL2Str(this.getSeasonToCho() + " 23:59:59", " "));
    lSqlBuf2.append("AND sum.Status != '1' ) AS a, ");
    lSqlBuf2.append("(SELECT detail.branchcode,detail.chargecode,detail.ordercode,detail.IndividualCode,detail.CmdtyName,detail.CmdtyCode");
    lSqlBuf2.append(",detail.ShippmentDate,detail.Price,detail.amount,detail.Amount*detail.Price AS cmdtyPriceInTax,detail.deliveryCode FROM orderdetaillatestvw AS detail) AS b ");
    lSqlBuf2.append("LEFT OUTER JOIN BranchTbl AS aa ON b.branchcode = aa.branchcode ");
    lSqlBuf2.append("LEFT OUTER JOIN ChargeTbl AS bb ON b.chargecode = bb.chargecode ");
    lSqlBuf2.append(",(SELECT i.consignGoodsFlg,i.individualCode,i.CmdtyCode,i.usednewflg FROM individualtbl AS i ) AS cc");
    lSqlBuf2.append(",ORDERDELIVERYLATESTVW AS dd ");
    lSqlBuf2.append("WHERE a.OrderCode = b.OrderCode ");
    lSqlBuf2.append("AND b.individualCode = cc.individualCode ");
    lSqlBuf2.append("AND b.CmdtyCode = cc.CmdtyCode ");
    lSqlBuf2.append("AND b.OrderCode = dd.OrderCode AND b.DeliveryCode = dd.DeliveryCode ");
    lSqlBuf2.append(this.conditionSQL);
    // ソート
    lSqlBuf2.append(" ORDER BY OrderCode,orderStatus");
    lSqlBuf.append(lSqlBuf2.toString());
    log.debug("lSqlBuf=" + lSqlBuf.toString());
    this.selectSQL = lSqlBuf.toString();
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      // 受注レコードセットの作成
      while (lResultSet.next()) {
        lBasic = new SIOrder();
        lBasic.setOrderCode(lResultSet.getString("orderCode"));// 受注番号
        lBasic.setCustCode(lResultSet.getString("custCode"));// 顧客コード
        lBasic.setCustName(lResultSet.getString("custName"));// 顧客名
        lBasic.setTotalOfFee(lResultSet.getString("fee"));// 手数料(必須)
        lBasic.setSumOfDiscount(lResultSet.getString("SumOfDiscount"));
        lBasic.setSumByPoint(lResultSet.getString("SumByPoint"));
        lBasic.setPointEnableFlg(lResultSet.getString("pointEnableFlg"));
        lBasic.setOrderRoute(lResultSet.getString("OrderRoute"));
        lBasic.setStatus(lResultSet.getString("Status"));
        lBasic.setOrderStatus(lResultSet.getString("orderStatus"));
        lBasic.setInitDateTime(SIDBUtil.getDateTime(lResultSet.getTimestamp("InitDateTime")));
        lBasic.setUpdateDateTime(SIDBUtil.getDateTime(lResultSet.getTimestamp("UpdateDateTime")));
        lBasic.setBranchName(lResultSet.getString("BranchName"));
        lBasic.setBranchCode(lResultSet.getString("branchcode"));
        lBasic.setChargeName(lResultSet.getString("ChargeName"));
        lBasic.setIndividualCode(lResultSet.getString("individualCode"));
        lBasic.setUsednewFlg(lResultSet.getString("usednewflg"));
        lBasic.setConsignGoodsFlg(lResultSet.getString("consignGoodsFlg"));
        lBasic.setCmdtyCode(lResultSet.getString("cmdtyCode"));
        lBasic.setCmdtyName(lResultSet.getString("cmdtyName"));
        lBasic.setAmount(lResultSet.getString("amount"));
        lBasic.setPrice(lResultSet.getString("price"));
        lBasic.setShippmentDate(SIDBUtil.getDateTime(lResultSet.getTimestamp("shippmentDate")));
        lBasic.setTotalOfDeliveryFee(lResultSet.getString("totalOfDeliveryFee"));
        lBasic.setSalesPriceInTax(lResultSet.getString("salesPriceInTax"));
        lBasic.setSalesPrice(lResultSet.getString("salesPrice"));
        lBasic.setTaxOfPrice(lResultSet.getString("taxPrice"));
        lResultColl.add(lBasic);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lResultColl;
  }
  
  /**
   * <b>getSalesCollection</b> 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * 
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws なし
   */
  public Collection getSalesCollection(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIOrder lBasic = new SIOrder();
    StringBuffer lSqlBuf = new StringBuffer();
    Collection lResultColl = new ArrayList();
    lSqlBuf.append("SELECT a.OrderCode");// 受注番号
    lSqlBuf.append(",a.CustCode");// 顧客コード
    lSqlBuf.append(",a.CustName");// 顧客名
    lSqlBuf.append(",(COALESCE(a.totaloffee,0) + COALESCE(a.DiscountFee,0)) AS Fee");// 代引手数料
    //EDBTG003-00 ohsugi mod start
//    lSqlBuf.append(",-(a.SumOfDiscount) as SumOfDiscount");// 値引金額
    lSqlBuf.append(",-(a.SumOfDiscount + a.setdiscount) as SumOfDiscount");// 値引金額
    //EDBTG003-00 ohsugi mod end
    lSqlBuf.append(",-(a.SumByPoint) as SumByPoint");// ポイント使用額
    lSqlBuf.append(",a.pointenableflg");// ポイント使用区分
    lSqlBuf.append(",a.orderStatus");// 取引区分の順番のため
    lSqlBuf.append(",a.Status");// ステータス
    lSqlBuf.append(",a.InitDateTime");// 受注年月日
    lSqlBuf.append(",a.UpdateDateTime");// 受注更新日
    lSqlBuf.append(",aa.BranchName");// 担当支店
    lSqlBuf.append(",a.branchcode");// 担当支店
    lSqlBuf.append(",bb.ChargeName");// 担当者
    lSqlBuf.append(",bb.chargecode");// 担当者
    lSqlBuf.append(",a.totalofprice");// 販売金額
    lSqlBuf.append(",b.IndividualCode");// 在庫コード
    lSqlBuf.append(",cc.usednewflg");// 中古新品区分
    lSqlBuf.append(",cc.consignGoodsFlg");// 委託フラグ
    lSqlBuf.append(",b.CmdtyName");// 商品名
    lSqlBuf.append(",b.CmdtyCode");// 親コード
    lSqlBuf.append(",b.ShippmentDate");// 出荷日
    lSqlBuf.append(",b.Amount");// 数量
    lSqlBuf.append(",b.Price");// 単価
    lSqlBuf.append(",0 AS sortIndex");// ソート順
    lSqlBuf.append(",(b.cmdtyPriceMoto*b.Amount) as cmdtyPriceMotoInTax");// 商品原価（税込）
    lSqlBuf.append(",CEIL((b.cmdtyPriceMoto*b.Amount)/ (1.0 + a.taxrate / 100.0)) as cmdtyPriceMoto");// 商品原価（税抜）
    lSqlBuf.append(",(COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0)) as totalOfDeliveryFee");// 送料
    lSqlBuf.append(",b.cmdtyPriceInTax as salesPriceInTax");// 売上金額（税込)
    lSqlBuf.append(",CEIL(b.cmdtyPriceInTax / (1.0 + a.taxrate / 100.0)) as salesPrice");// 売上金額（税抜）
    lSqlBuf.append(",(CEIL(b.cmdtyPriceInTax / (1.0 + a.taxrate / 100.0)) - CEIL((b.cmdtyPriceMoto*b.Amount)/ (1.0 + a.taxrate / 100.0))) as profitPrice ");// 粗利益
    lSqlBuf.append("FROM (SELECT sum.pointenableflg,sum.OrderCode,sum.CustCode,sum.CustName,sum.totaloffee,sum.DiscountFee");
    lSqlBuf.append(",sum.SumOfDiscount,sum.SumByPoint,sum.OrderRoute,sum.Status,sum.InitDateTime,sum.UpdateDateTime,sum.branchcode,0 AS orderStatus");
    lSqlBuf.append(",sum.setdiscount");//EDBTG003-00 ohsugi add
    lSqlBuf.append(",sum.totalofprice,sum.DiscountDeliveryFee,sum.totalOfDeliveryFee,sum.taxrate,sum.chargecode FROM ordersumvw AS sum WHERE 1=1 ");
    if (SIUtil.isNotNull(this.getSeasonFromCho())) {
      lSqlBuf.append("AND sum.InitDateTime >= ").append(SIDBUtil.SQL2Str(this.getSeasonFromCho(), " "));
    }
    if (SIUtil.isNotNull(this.getSeasonToCho())) {
      lSqlBuf.append("AND sum.InitDateTime <= ").append(SIDBUtil.SQL2Str(this.getSeasonToCho() + " 23:59:59", " "));
    }
    lSqlBuf.append("AND sum.Status != '0' ");// キャンセルは外す
    lSqlBuf.append(") AS a ");
    
    lSqlBuf.append(",(SELECT detail.branchcode,detail.chargecode,detail.ordercode,detail.IndividualCode,detail.CmdtyName,detail.CmdtyCode,detail.ShippmentDate");
    lSqlBuf.append(",detail.Price,detail.amount,detail.Amount*detail.Price AS cmdtyPriceInTax,(detail.purchaseprice+detail.processingexpence) AS cmdtyPriceMoto ");
    lSqlBuf.append("FROM orderdetaillatestvw AS detail WHERE 1=1 ");
    if (SIUtil.isNotNull(this.getShipmentDateFromCho())) {
      lSqlBuf.append("AND detail.ShippmentDate >= ").append(SIDBUtil.SQL2Str(this.getShipmentDateFromCho(), " "));
    }
    if (SIUtil.isNotNull(this.getShipmentDateToCho())) {
      lSqlBuf.append(" AND detail.ShippmentDate <= ").append(SIDBUtil.SQL2Str(this.getShipmentDateToCho() + " 23:59:59", " "));
    }
    lSqlBuf.append(") AS b ");
    
    lSqlBuf.append("LEFT OUTER JOIN BranchTbl AS aa ON b.branchcode = aa.branchcode ");
    lSqlBuf.append("LEFT OUTER JOIN ChargeTbl AS bb ON b.chargecode = bb.chargecode ");
    lSqlBuf.append(",(SELECT i.consignGoodsFlg,i.individualCode,i.cmdtycode,i.usednewflg FROM individualtbl AS i ) AS cc ");
    lSqlBuf.append("WHERE a.OrderCode = b.OrderCode AND cc.individualCode = b.individualCode AND cc.cmdtycode = b.cmdtycode ");
    // 検索条件
    lSqlBuf.append(conditionSQL);
    
    StringBuffer lSqlBuf2 = new StringBuffer();
    lSqlBuf2.append(" UNION SELECT a.OrderCode");// 受注番号
    lSqlBuf2.append(",a.CustCode");// 顧客コード
    lSqlBuf2.append(",a.CustName");// 顧客名
    lSqlBuf2.append(",-(COALESCE(a.totaloffee,0) + COALESCE(a.DiscountFee,0)) AS Fee");// 代引手数料
    //EDBTG003-00 ohsugi mod start
//    lSqlBuf2.append(",(a.SumOfDiscount) as SumOfDiscount");// 値引金額
    lSqlBuf2.append(",(a.SumOfDiscount + a.setdiscount) as SumOfDiscount");// 値引金額
    //EDBTG003-00 ohsugi mod end
    lSqlBuf2.append(",(a.SumByPoint) as SumByPoint");// ポイント使用額
    lSqlBuf2.append(",a.pointenableflg");// ポイント使用区分
    lSqlBuf2.append(",a.orderStatus");// 取引区分の順番のため
    lSqlBuf2.append(",a.Status");// ステータス
    lSqlBuf2.append(",a.InitDateTime");// 受注年月日
    lSqlBuf2.append(",a.UpdateDateTime");// 受注更新日
    lSqlBuf2.append(",aa.BranchName");// 担当支店
    lSqlBuf2.append(",a.branchcode");// 担当支店
    lSqlBuf2.append(",bb.ChargeName");// 担当者
    lSqlBuf2.append(",bb.chargecode");// 担当者
    lSqlBuf2.append(",a.totalofprice");// 販売金額
    lSqlBuf2.append(",b.IndividualCode");// 在庫コード
    lSqlBuf2.append(",cc.usednewflg");// 中古新品区分
    lSqlBuf2.append(",cc.consignGoodsFlg");// 委託フラグ
    lSqlBuf2.append(",b.CmdtyName");// 商品名
    lSqlBuf2.append(",b.CmdtyCode");// 親コード
    lSqlBuf2.append(",a.UpdateDateTime as ShippmentDate");// 出荷日
    lSqlBuf2.append(",b.Amount");// 数量
    lSqlBuf2.append(",b.Price");// 単価
    lSqlBuf2.append(",100 AS sortIndex");// ソート順
    lSqlBuf2.append(",-(b.cmdtyPriceMoto*b.Amount) as cmdtyPriceMotoInTax");// 商品原価（税込）
    lSqlBuf2.append(",-CEIL((b.cmdtyPriceMoto*b.Amount)/ (1.0 + a.taxrate / 100.0)) as cmdtyPriceMoto");// 商品原価（税抜）
    lSqlBuf2.append(",-(COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0)) as totalOfDeliveryFee");// 送料
    lSqlBuf2.append(",-b.cmdtyPriceInTax as salesPriceInTax");// 売上金額（税込)
    lSqlBuf2.append(",-CEIL(b.cmdtyPriceInTax / (1.0 + a.taxrate / 100.0)) as salesPrice");// 売上金額（税抜）
    lSqlBuf2.append(",-(CEIL(b.cmdtyPriceInTax / (1.0 + a.taxrate / 100.0)) - CEIL((b.cmdtyPriceMoto*b.Amount) / (1.0 + a.taxrate / 100.0))) as profitPrice ");// 粗利益
    lSqlBuf2.append("FROM (SELECT sum.pointenableflg,sum.OrderCode,sum.CustCode,sum.CustName,sum.totaloffee,sum.DiscountFee");
    lSqlBuf2.append(",sum.SumOfDiscount,sum.SumByPoint,sum.OrderRoute,sum.Status,sum.InitDateTime,sum.UpdateDateTime,sum.branchcode,1 AS orderStatus");
    lSqlBuf2.append(",sum.setdiscount");//EDBTG003-00 ohsugi add
    lSqlBuf2.append(",sum.totalofprice,sum.DiscountDeliveryFee,sum.totalOfDeliveryFee,sum.taxrate,sum.chargecode FROM ordersumvw AS sum WHERE 1=1 ");
    if (SIUtil.isNotNull(this.getSeasonFromCho())) {
      lSqlBuf2.append("AND sum.InitDateTime >= " + SIDBUtil.SQL2Str(this.getSeasonFromCho(), " "));
    }
    if (SIUtil.isNotNull(this.getSeasonToCho())) {
      lSqlBuf2.append("AND sum.InitDateTime <= " + SIDBUtil.SQL2Str(this.getSeasonToCho() + " 23:59:59", " "));
    }
    lSqlBuf2.append("AND sum.Status = '2' ");// 返品のみ
    // 返品日を出荷日として扱う
    if (SIUtil.isNotNull(this.getShipmentDateFromCho())) {
      lSqlBuf2.append("AND sum.UpdateDateTime >= " + SIDBUtil.SQL2Str(this.getShipmentDateFromCho(), " "));
    }
    if (SIUtil.isNotNull(this.getShipmentDateToCho())) {
      lSqlBuf2.append("AND sum.UpdateDateTime <= " + SIDBUtil.SQL2Str(this.getShipmentDateToCho() + " 23:59:59", " "));
    }
    lSqlBuf2.append(") AS a, ");
    
    lSqlBuf2.append("(SELECT detail.branchcode,detail.chargecode,detail.ordercode,detail.IndividualCode,detail.CmdtyName,detail.CmdtyCode,detail.ShippmentDate");
    lSqlBuf2.append(",detail.Price,detail.amount,detail.Amount*detail.Price AS cmdtyPriceInTax");
    lSqlBuf2.append(",CASE WHEN correct.purchasepricenew IS NULL THEN (detail.purchaseprice+detail.processingexpence) ");
    lSqlBuf2.append("ELSE (correct.purchasepricenew+detail.processingexpence) END AS cmdtyPriceMoto ");
    lSqlBuf2.append("FROM orderdetaillatestvw AS detail ");
    lSqlBuf2.append("LEFT OUTER JOIN (SELECT aa.ordercode,aa.cmdtycode,aa.individualcode,aa.purchasepricenew FROM orderdetailcosttbl aa");
    lSqlBuf2.append(",(SELECT max(correctcount) AS count,ordercode,cmdtycode,individualcode FROM orderdetailcosttbl GROUP BY ordercode,cmdtycode,individualcode) bb ");
    lSqlBuf2.append("WHERE aa.ordercode = bb.ordercode AND aa.cmdtycode = bb.cmdtycode AND aa.individualcode = bb.individualcode AND aa.correctcount = bb.count) AS correct ");
    lSqlBuf2.append("ON detail.ordercode = correct.ordercode AND detail.cmdtycode = correct.cmdtycode AND detail.individualcode = correct.individualcode ");
    lSqlBuf2.append(" ) AS b ");
    lSqlBuf2.append("LEFT OUTER JOIN BranchTbl AS aa ON b.branchcode = aa.branchcode ");
    lSqlBuf2.append("LEFT OUTER JOIN ChargeTbl AS bb ON b.chargecode = bb.chargecode ");
    lSqlBuf2.append(",(SELECT i.consignGoodsFlg,i.individualCode,i.cmdtycode,i.usednewflg FROM individualtbl AS i ) AS cc ");
    lSqlBuf2.append("WHERE a.OrderCode = b.OrderCode AND cc.individualCode = b.individualCode AND cc.cmdtycode = b.cmdtycode ");
    lSqlBuf2.append(conditionSQL);
    
    lSqlBuf.append(lSqlBuf2.toString());
    
    StringBuffer lSqlBuf3 = new StringBuffer();
    lSqlBuf3.append(" UNION SELECT a.OrderCode");// 受注番号
    lSqlBuf3.append(",a.CustCode");// 顧客コード
    lSqlBuf3.append(",a.CustName");// 顧客名
    lSqlBuf3.append(",-(COALESCE(a.totaloffee,0) + COALESCE(a.DiscountFee,0)) AS Fee");// 代引手数料
    //EDBTG003-00 ohsugi mod start
//    lSqlBuf3.append(",(a.SumOfDiscount) as SumOfDiscount");// 値引金額
    lSqlBuf3.append(",(a.SumOfDiscount + a.setdiscount) as SumOfDiscount");// 値引金額
    //EDBTG003-00 ohsugi mod end
    lSqlBuf3.append(",(a.SumByPoint) as SumByPoint");// ポイント使用額
    lSqlBuf3.append(",a.pointenableflg");// ポイント使用区分
    lSqlBuf3.append(",a.orderStatus");// 取引区分の順番のため
    lSqlBuf3.append(",a.Status");// ステータス
    lSqlBuf3.append(",a.InitDateTime");// 受注年月日
    lSqlBuf3.append(",a.UpdateDateTime");// 受注更新日
    lSqlBuf3.append(",aa.BranchName");// 担当支店
    lSqlBuf3.append(",a.branchcode");// 担当支店
    lSqlBuf3.append(",bb.ChargeName");// 担当者
    lSqlBuf3.append(",bb.chargecode");// 担当者
    lSqlBuf3.append(",a.totalofprice");// 販売金額
    lSqlBuf3.append(",b.IndividualCode");// 在庫コード
    lSqlBuf3.append(",cc.usednewflg");// 中古新品区分
    lSqlBuf3.append(",cc.consignGoodsFlg");// 委託フラグ
    lSqlBuf3.append(",b.CmdtyName");// 商品名
    lSqlBuf3.append(",b.CmdtyCode");// 親コード
    lSqlBuf3.append(",b.initdatetime as ShippmentDate");// 出荷日
    lSqlBuf3.append(",b.Amount");// 数量
    lSqlBuf3.append(",b.Price");// 単価
    lSqlBuf3.append(",b.correctcount AS sortIndex");// ソート順
    lSqlBuf3.append(",-(b.cmdtyPriceMoto*b.Amount) as cmdtyPriceMotoInTax");// 商品原価（税込）
    lSqlBuf3.append(",-CEIL((b.cmdtyPriceMoto*b.Amount)/ (1.0 + a.taxrate / 100.0)) as cmdtyPriceMoto");// 商品原価（税抜）
    lSqlBuf3.append(",-(COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0)) as totalOfDeliveryFee");// 送料
    lSqlBuf3.append(",-b.cmdtyPriceInTax as salesPriceInTax");// 売上金額（税込)
    lSqlBuf3.append(",-CEIL(b.cmdtyPriceInTax / (1.0 + a.taxrate / 100.0)) as salesPrice");// 売上金額（税抜）
    lSqlBuf3.append(",-(CEIL(b.cmdtyPriceInTax / (1.0 + a.taxrate / 100.0)) - CEIL((b.cmdtyPriceMoto*b.Amount) / (1.0 + a.taxrate / 100.0))) as profitPrice ");// 粗利益
    lSqlBuf3.append("FROM (SELECT sum.pointenableflg,sum.OrderCode,sum.CustCode,sum.CustName,sum.totaloffee,sum.DiscountFee");
    lSqlBuf3.append(",sum.SumOfDiscount,sum.SumByPoint,sum.OrderRoute,sum.Status,sum.InitDateTime,sum.UpdateDateTime,sum.branchcode,2 AS orderStatus");
    lSqlBuf3.append(",sum.setdiscount");//EDBTG003-00 ohsugi add
    lSqlBuf3.append(",sum.totalofprice,sum.DiscountDeliveryFee,sum.totalOfDeliveryFee,sum.taxrate,sum.chargecode FROM ordersumvw AS sum WHERE 1=1 ");
    if (SIUtil.isNotNull(this.getSeasonFromCho())) {
      lSqlBuf3.append("AND sum.InitDateTime >= " + SIDBUtil.SQL2Str(this.getSeasonFromCho(), " "));
    }
    if (SIUtil.isNotNull(this.getSeasonToCho())) {
      lSqlBuf3.append("AND sum.InitDateTime <= " + SIDBUtil.SQL2Str(this.getSeasonToCho() + " 23:59:59", " "));
    }
    lSqlBuf3.append("AND sum.Status != '0' ");// キャンセル以外
    lSqlBuf3.append(") AS a, ");
    
    lSqlBuf3.append("(SELECT detail.branchcode,detail.chargecode,detail.ordercode,detail.IndividualCode,detail.CmdtyName,detail.CmdtyCode,detail.ShippmentDate");
    lSqlBuf3.append(",correct.initdatetime,correct.correctcount,detail.Price,detail.amount,detail.Amount*detail.Price AS cmdtyPriceInTax");
    lSqlBuf3.append(",(correct.purchasepriceold+detail.processingexpence) AS cmdtyPriceMoto ");
    lSqlBuf3.append("FROM orderdetaillatestvw AS detail ");
    lSqlBuf3.append(",(SELECT aa.ordercode,aa.cmdtycode,aa.individualcode,aa.purchasepriceold,aa.initdatetime,aa.correctcount FROM orderdetailcosttbl aa");
    lSqlBuf3.append(",(SELECT max(correctcount) AS count,ordercode,cmdtycode,individualcode FROM orderdetailcosttbl GROUP BY ordercode,cmdtycode,individualcode) bb ");
    lSqlBuf3.append("WHERE aa.ordercode = bb.ordercode AND aa.cmdtycode = bb.cmdtycode AND aa.individualcode = bb.individualcode AND aa.correctcount = bb.count) AS correct ");
    lSqlBuf3.append("WHERE detail.ordercode = correct.ordercode AND detail.cmdtycode = correct.cmdtycode AND detail.individualcode = correct.individualcode ");
    // 訂正日を出荷日として扱う
    if (SIUtil.isNotNull(this.getShipmentDateFromCho())) {
      lSqlBuf3.append("AND correct.initdatetime >= " + SIDBUtil.SQL2Str(this.getShipmentDateFromCho(), " "));
    }
    if (SIUtil.isNotNull(this.getShipmentDateToCho())) {
      lSqlBuf3.append("AND correct.initdatetime <= " + SIDBUtil.SQL2Str(this.getShipmentDateToCho() + " 23:59:59", " "));
    }
    lSqlBuf3.append(" ) AS b ");
    lSqlBuf3.append("LEFT OUTER JOIN BranchTbl AS aa ON b.branchcode = aa.branchcode ");
    lSqlBuf3.append("LEFT OUTER JOIN ChargeTbl AS bb ON b.chargecode = bb.chargecode ");
    lSqlBuf3.append(",(SELECT i.consignGoodsFlg,i.individualCode,i.cmdtycode,i.usednewflg FROM individualtbl AS i ) AS cc ");
    lSqlBuf3.append("WHERE a.OrderCode = b.OrderCode AND cc.individualCode = b.individualCode AND cc.cmdtycode = b.cmdtycode ");
    lSqlBuf3.append(conditionSQL);
    
    lSqlBuf.append(lSqlBuf3.toString());
    
    StringBuffer lSqlBuf4 = new StringBuffer();
    lSqlBuf4.append(" UNION SELECT a.OrderCode");// 受注番号
    lSqlBuf4.append(",a.CustCode");// 顧客コード
    lSqlBuf4.append(",a.CustName");// 顧客名
    lSqlBuf4.append(",(COALESCE(a.totaloffee,0) + COALESCE(a.DiscountFee,0)) AS Fee");// 代引手数料
    //EDBTG003-00 ohsugi mod start
//    lSqlBuf4.append(",-(a.SumOfDiscount) as SumOfDiscount");// 値引金額
    lSqlBuf4.append(",-(a.SumOfDiscount + a.setdiscount) as SumOfDiscount");// 値引金額
    //EDBTG003-00 ohsugi mod end
    lSqlBuf4.append(",-(a.SumByPoint) as SumByPoint");// ポイント使用額
    lSqlBuf4.append(",a.pointenableflg");// ポイント使用区分
    lSqlBuf4.append(",a.orderStatus");// 取引区分の順番のため
    lSqlBuf4.append(",a.Status");// ステータス
    lSqlBuf4.append(",a.InitDateTime");// 受注年月日
    lSqlBuf4.append(",a.UpdateDateTime");// 受注更新日
    lSqlBuf4.append(",aa.BranchName");// 担当支店
    lSqlBuf4.append(",a.branchcode");// 担当支店
    lSqlBuf4.append(",bb.ChargeName");// 担当者
    lSqlBuf4.append(",bb.chargecode");// 担当者
    lSqlBuf4.append(",a.totalofprice");// 販売金額
    lSqlBuf4.append(",b.IndividualCode");// 在庫コード
    lSqlBuf4.append(",cc.usednewflg");// 中古新品区分
    lSqlBuf4.append(",cc.consignGoodsFlg");// 委託フラグ
    lSqlBuf4.append(",b.CmdtyName");// 商品名
    lSqlBuf4.append(",b.CmdtyCode");// 親コード
    lSqlBuf4.append(",b.initdatetime as ShippmentDate");// 出荷日
    lSqlBuf4.append(",b.Amount");// 数量
    lSqlBuf4.append(",b.Price");// 単価
    lSqlBuf4.append(",b.correctcount AS sortIndex");// ソート順
    lSqlBuf4.append(",(b.cmdtyPriceMoto*b.Amount) as cmdtyPriceMotoInTax");// 商品原価（税込）
    lSqlBuf4.append(",CEIL((b.cmdtyPriceMoto*b.Amount)/ (1.0 + a.taxrate / 100.0)) as cmdtyPriceMoto");// 商品原価（税抜）
    lSqlBuf4.append(",(COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0)) as totalOfDeliveryFee");// 送料
    lSqlBuf4.append(",b.cmdtyPriceInTax as salesPriceInTax");// 売上金額（税込)
    lSqlBuf4.append(",CEIL(b.cmdtyPriceInTax / (1.0 + a.taxrate / 100.0)) as salesPrice");// 売上金額（税抜）
    lSqlBuf4.append(",(CEIL(b.cmdtyPriceInTax / (1.0 + a.taxrate / 100.0)) - CEIL((b.cmdtyPriceMoto*b.Amount) / (1.0 + a.taxrate / 100.0))) as profitPrice ");// 粗利益
    lSqlBuf4.append("FROM (SELECT sum.pointenableflg,sum.OrderCode,sum.CustCode,sum.CustName,sum.totaloffee,sum.DiscountFee");
    lSqlBuf4.append(",sum.SumOfDiscount,sum.SumByPoint,sum.OrderRoute,sum.Status,sum.InitDateTime,sum.UpdateDateTime,sum.branchcode,2 AS orderStatus");
    lSqlBuf4.append(",sum.setdiscount");//EDBTG003-00 ohsugi add
    lSqlBuf4.append(",sum.totalofprice,sum.DiscountDeliveryFee,sum.totalOfDeliveryFee,sum.taxrate,sum.chargecode FROM ordersumvw AS sum WHERE 1=1 ");
    if (SIUtil.isNotNull(this.getSeasonFromCho())) {
      lSqlBuf4.append("AND sum.InitDateTime >= " + SIDBUtil.SQL2Str(this.getSeasonFromCho(), " "));
    }
    if (SIUtil.isNotNull(this.getSeasonToCho())) {
      lSqlBuf4.append("AND sum.InitDateTime <= " + SIDBUtil.SQL2Str(this.getSeasonToCho() + " 23:59:59", " "));
    }
    lSqlBuf4.append("AND sum.Status != '0' ");// キャンセル以外
    lSqlBuf4.append(") AS a, ");
    
    lSqlBuf4.append("(SELECT detail.branchcode,detail.chargecode,detail.ordercode,detail.IndividualCode,detail.CmdtyName,detail.CmdtyCode,detail.ShippmentDate");
    lSqlBuf4.append(",correct.initdatetime,correct.correctcount,detail.Price,detail.amount,detail.Amount*detail.Price AS cmdtyPriceInTax");
    lSqlBuf4.append(",(correct.purchasepricenew+detail.processingexpence) AS cmdtyPriceMoto ");
    lSqlBuf4.append("FROM orderdetaillatestvw AS detail ");
    lSqlBuf4.append(",(SELECT aa.ordercode,aa.cmdtycode,aa.individualcode,aa.purchasepricenew,aa.initdatetime,aa.correctcount FROM orderdetailcosttbl aa");
    lSqlBuf4.append(",(SELECT max(correctcount) AS count,ordercode,cmdtycode,individualcode FROM orderdetailcosttbl GROUP BY ordercode,cmdtycode,individualcode) bb ");
    lSqlBuf4.append("WHERE aa.ordercode = bb.ordercode AND aa.cmdtycode = bb.cmdtycode AND aa.individualcode = bb.individualcode AND aa.correctcount = bb.count) AS correct ");
    lSqlBuf4.append("WHERE detail.ordercode = correct.ordercode AND detail.cmdtycode = correct.cmdtycode AND detail.individualcode = correct.individualcode ");
    // 訂正日を出荷日として扱う
    if (SIUtil.isNotNull(this.getShipmentDateFromCho())) {
      lSqlBuf4.append("AND correct.initdatetime >= " + SIDBUtil.SQL2Str(this.getShipmentDateFromCho(), " "));
    }
    if (SIUtil.isNotNull(this.getShipmentDateToCho())) {
      lSqlBuf4.append("AND correct.initdatetime <= " + SIDBUtil.SQL2Str(this.getShipmentDateToCho() + " 23:59:59", " "));
    }
    lSqlBuf4.append(" ) AS b ");
    lSqlBuf4.append("LEFT OUTER JOIN BranchTbl AS aa ON b.branchcode = aa.branchcode ");
    lSqlBuf4.append("LEFT OUTER JOIN ChargeTbl AS bb ON b.chargecode = bb.chargecode ");
    lSqlBuf4.append(",(SELECT i.consignGoodsFlg,i.individualCode,i.cmdtycode,i.usednewflg FROM individualtbl AS i ) AS cc ");
    lSqlBuf4.append("WHERE a.OrderCode = b.OrderCode AND cc.individualCode = b.individualCode AND cc.cmdtycode = b.cmdtycode ");
    lSqlBuf4.append(conditionSQL);
    // ソート
    lSqlBuf4.append(" ORDER BY ShippmentDate,OrderCode,SortIndex");
    lSqlBuf.append(lSqlBuf4.toString());
    
    log.debug("lSqlBuf=" + lSqlBuf.toString());
    this.selectSQL = lSqlBuf.toString();
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      
      // 売上レコードセットの作成
      while (lResultSet.next()) {
        lBasic = new SIOrder();
        lBasic.setOrderCode(lResultSet.getString("orderCode"));// 受注番号
        lBasic.setCustCode(lResultSet.getString("custCode"));// 顧客コード
        lBasic.setCustName(lResultSet.getString("custName"));// 顧客名
        lBasic.setTotalOfFee(lResultSet.getString("fee"));// 手数料(必須)
        lBasic.setSumOfDiscount(lResultSet.getString("SumOfDiscount"));
        lBasic.setSumByPoint(lResultSet.getString("SumByPoint"));
        lBasic.setPointEnableFlg(lResultSet.getString("PointEnableFlg"));
        lBasic.setStatus(lResultSet.getString("Status"));
        lBasic.setOrderStatus(lResultSet.getString("orderStatus"));
        lBasic.setInitDateTime(SIDBUtil.getDateTime(lResultSet.getTimestamp("InitDateTime")));
        lBasic.setUpdateDateTime(SIDBUtil.getDateTime(lResultSet.getTimestamp("UpdateDateTime")));
        lBasic.setBranchName(lResultSet.getString("BranchName"));
        lBasic.setBranchCode(lResultSet.getString("branchcode"));
        lBasic.setChargeName(lResultSet.getString("ChargeName"));
        lBasic.setChargeCode(lResultSet.getString("chargecode"));
        lBasic.setIndividualCode(lResultSet.getString("individualCode"));
        lBasic.setConsignGoodsFlg(lResultSet.getString("consignGoodsFlg"));
        lBasic.setUsednewFlg(lResultSet.getString("usednewflg"));
        lBasic.setCmdtyName(lResultSet.getString("cmdtyName"));
        lBasic.setCmdtyCode(lResultSet.getString("cmdtyCode"));
        lBasic.setPrice(lResultSet.getString("Price"));
        lBasic.setAmount(lResultSet.getString("amount"));
        lBasic.setShippmentDate(SIDBUtil.getDateTime(lResultSet.getTimestamp("shippmentDate")));
        lBasic.setTotalOfDeliveryFee(lResultSet.getString("totalOfDeliveryFee"));
        lBasic.setSalesPriceInTax(lResultSet.getString("salesPriceInTax"));
        lBasic.setSalesPrice(lResultSet.getString("salesPrice"));
        lBasic.setCmdtyPrice(lResultSet.getString("cmdtyPriceMoto"));
        lBasic.setCmdtyPriceInTax(lResultSet.getString("cmdtyPriceMotoInTax"));
        lBasic.setProfitPrice(lResultSet.getString("profitPrice"));
        lResultColl.add(lBasic);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lResultColl;
  }
  
  public boolean isDecidedInventory(Connection lConnection) {
    boolean result = false;
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append("SELECT status FROM inventorytbl ");
    lSqlBuf.append(" WHERE status = 2 ");
    lSqlBuf.append(" AND branchcode=").append(SIDBUtil.SQL2Str(this.getBranchCode()));
    lSqlBuf.append(" AND season=").append(SIDBUtil.SQL2Str(this.getSeasonCbo()));
    try {
      if (SIUtil.isNotNull(SIDBUtil.getFirstData(lConnection, lSqlBuf.toString()))) {
        result = true;
      }
    } catch (SIDBAccessException e) {
      e.printStackTrace();
    }
    return result;
  }
  
  /**
   * <b>getInventoryCollection</b> 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * 
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws なし
   */
  public Collection getIndividualCollection(Connection lConnection, boolean decideFlg) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    UIInventory lBasic = new UIInventory();
    StringBuffer lSqlBuf = new StringBuffer();
    Collection lResultColl = new ArrayList();
    
    lSqlBuf.append("SELECT aa.individualcode,aa.cmdtycode,");
    lSqlBuf.append("cc.stockAmount, ee.storeAmount, ff.shipAmount,aa.branchcode,aa.category, ");
    
    if (decideFlg) {// 当月締め処理済み
      lSqlBuf.append("realstock.amountflg,realstock.realamount,");
      lSqlBuf.append("dd.cmdtyPrice as cmdtyPrice,");
      lSqlBuf.append("dd.cmdtyPrice * (COALESCE(cc.stockAmount,0)+COALESCE(ee.storeAmount,0)-COALESCE(ff.shipAmount,0)) AS sumPrice,");
    } else {
      lSqlBuf.append("aa.amountflg,aa.realamount,");
      lSqlBuf.append("aa.cmdtyPrice as cmdtyPrice,");
      lSqlBuf.append("aa.cmdtyPrice*(COALESCE(cc.stockAmount,0)+COALESCE(ee.storeAmount,0)-COALESCE(ff.shipAmount,0)) AS sumPrice,");
    }
    lSqlBuf.append("(COALESCE(cc.stockAmount,0)+COALESCE(ee.storeAmount,0)-COALESCE(ff.shipAmount,0)) AS sumAmount ");
    if (decideFlg) {// 当月締め処理済み
      // 実際在庫数
      lSqlBuf.append("FROM (SELECT (COALESCE(rs.purchaseprice,0)+COALESCE(rs.processingexpence,0)) AS cmdtyPrice");
      lSqlBuf.append(",rs.amountflg,rs.realamount,rs.individualcode,rs.cmdtycode,rs.branchcode ");
      lSqlBuf.append("FROM realstockvw AS rs ");
      lSqlBuf.append("WHERE rs.branchcode = ").append(SIDBUtil.SQL2Str(this.getBranchCode()));
      if (SIUtil.isNotNull(this.getIndividualFlg())) {
        if (this.getIndividualFlg().equals("0")) {// 在庫あり
          lSqlBuf.append(" AND rs.realamount != 0 ");
        } else if (this.getIndividualFlg().equals("1")) {// 在庫なし
          lSqlBuf.append(" AND rs.realamount = 0 ");
        }
      }
      // 在庫管理
      if (SIUtil.isNotNull(this.getInstockFlg()) && !this.getInstockFlg().equals("2")) {
        lSqlBuf.append(" AND rs.amountflg = ").append(this.getInstockFlg());
      }
      lSqlBuf.append(") AS realstock,");
      // 在庫単価
      lSqlBuf.append(" (SELECT cost.cmdtycode,cost.individualcode,(COALESCE(cost.purchaseprice,0) + COALESCE(cost.processingexpence,0)) AS cmdtyPrice ");
      lSqlBuf.append(" FROM costhistorytbl AS cost ");
      lSqlBuf.append(" WHERE cost.season=").append(SIDBUtil.SQL2Str(this.getSeasonCbo())).append(" ) AS dd, ");
      // 締処理在庫
      lSqlBuf.append(" (SELECT substr(sr.individualcode,1,1) as category,sr.individualcode,sr.cmdtycode,sr.branchcode,sr.stockyear ");
      lSqlBuf.append(" FROM stockrecordtbl AS sr ");
      lSqlBuf.append(" WHERE sr.branchcode = ").append(this.getBranchCode());
      lSqlBuf.append(" AND sr.stockyear = ").append(SIDBUtil.SQL2Str(this.getSeasonYearCbo())).append(") AS aa ");
    } else {
      // 実際在庫数
      lSqlBuf.append(" FROM (SELECT substr(rs.individualcode,1,1) as category,(COALESCE(rs.purchaseprice,0) + COALESCE(rs.processingexpence,0)) AS cmdtyPrice");
      lSqlBuf.append(",rs.amountflg,rs.realamount,rs.individualcode,rs.cmdtycode,rs.branchcode ");
      lSqlBuf.append(" FROM realstockvw AS rs ");
      lSqlBuf.append(" WHERE rs.branchcode = ").append(SIDBUtil.SQL2Str(this.getBranchCode()));
      if (SIUtil.isNotNull(this.getIndividualFlg())) {
        if (this.getIndividualFlg().equals("0")) {// 在庫あり
          lSqlBuf.append(" AND rs.realamount != 0 ");
        } else if (this.getIndividualFlg().equals("1")) {// 在庫なし
          lSqlBuf.append(" AND rs.realamount = 0 ");
        }
      }
      if (SIUtil.isNotNull(this.getInstockFlg()) && !this.getInstockFlg().equals("2")) {
        lSqlBuf.append(" AND rs.amountflg = ").append(SIDBUtil.SQL2Str(this.getInstockFlg()));
      }
      lSqlBuf.append(") AS aa ");
    }
    
    // 繰越在庫数
    lSqlBuf.append(" LEFT OUTER JOIN ");
    lSqlBuf.append("(SELECT stock.cmdtycode,stock.individualcode,stock.branchcode,stock.").append(SIUtil.getPreAmountName(this.getSeasonCbo())).append(" AS stockAmount ");
    lSqlBuf.append("FROM stockrecordtbl AS stock ");
    lSqlBuf.append(" WHERE stock.stockyear = ").append(SIDBUtil.SQL2Str(SIUtil.getPreYearName(this.getSeasonCbo())));
    lSqlBuf.append(" AND stock.branchcode = ").append(SIDBUtil.SQL2Str(this.getBranchCode())).append(") AS cc ");
    lSqlBuf.append(" ON cc.cmdtycode=aa.cmdtycode AND cc.individualcode=aa.individualcode ");
    
    // 入庫数
    lSqlBuf.append("LEFT OUTER JOIN ");
    lSqlBuf.append("(SELECT sh.cmdtycode,sh.individualcode,SUM(sh.amount) AS storeAmount FROM storehistorytbl AS sh ");
    lSqlBuf.append(" WHERE sh.delflg IN ('1','4') ");
    lSqlBuf.append(" AND to_char(sh.initdatetime,'YYYYMM') = ").append(SIDBUtil.SQL2Str(this.getSeasonCbo()));
    lSqlBuf.append(" AND storebranch=").append(SIDBUtil.SQL2Str(this.getBranchCode()));
    lSqlBuf.append(" GROUP BY sh.cmdtycode,sh.individualcode) AS ee ");
    lSqlBuf.append(" ON ee.cmdtycode=aa.cmdtycode AND ee.individualcode=aa.individualcode ");
    
    // 出庫数
    lSqlBuf.append("LEFT OUTER JOIN ");
    lSqlBuf.append("(SELECT sp.cmdtycode,sp.individualcode,SUM(sp.amount) AS shipAmount FROM shiphistorytbl AS sp ");
    lSqlBuf.append(" WHERE sp.delflg IN ('1','4') ");
    lSqlBuf.append(" AND to_char(sp.initdatetime,'YYYYMM') = ").append(SIDBUtil.SQL2Str(this.getSeasonCbo()));
    lSqlBuf.append(" AND shipbranch=").append(SIDBUtil.SQL2Str(this.getBranchCode()));
    lSqlBuf.append(" GROUP BY sp.cmdtycode,sp.individualcode) AS ff ");
    lSqlBuf.append(" ON aa.cmdtycode=ff.cmdtycode AND aa.individualcode=ff.individualcode ");
    
    lSqlBuf.append(" WHERE NOT(COALESCE(ee.storeAmount,0) = 0 AND COALESCE(cc.stockAmount,0) = 0 AND COALESCE(ff.shipAmount,0) = 0) ");
    if (decideFlg) {
      lSqlBuf.append(" AND realstock.individualcode = aa.individualcode AND realstock.cmdtycode = aa.cmdtycode ");
      lSqlBuf.append(" AND aa.cmdtycode=dd.cmdtycode AND aa.individualcode=dd.individualcode ");
      lSqlBuf.append(" AND substr(realstock.individualcode,8,1) != ").append(SIDBUtil.SQL2Str(SIConfig.INDIVIDUALTAIL[SIConfig.INDIVIDUALTAIL_CONSIGN]," "));
    } else {
      lSqlBuf.append(" AND substr(aa.individualcode,8,1) != ").append(SIDBUtil.SQL2Str(SIConfig.INDIVIDUALTAIL[SIConfig.INDIVIDUALTAIL_CONSIGN]," "));
    }
    lSqlBuf.append(this.conditionSQL);
    lSqlBuf.append(" ORDER BY aa.individualcode");
    log.debug("lSqlBuf=" + lSqlBuf.toString());
    this.selectSQL = lSqlBuf.toString();
    
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      
      // 在庫レコードセットの作成
      while (lResultSet.next()) {
        lBasic = new UIInventory();
        lBasic.setIndividualCode(lResultSet.getString("individualcode"));
        lBasic.setCmdtyCode(lResultSet.getString("cmdtycode"));
        lBasic.setStoreRecordAmount(lResultSet.getString("stockAmount"));
        lBasic.setCmdtyPrice(lResultSet.getString("cmdtyPrice"));
        lBasic.setStoreAmount(lResultSet.getString("storeAmount"));
        lBasic.setShipAmount(lResultSet.getString("shipAmount"));
        lBasic.setSumAmount(lResultSet.getString("sumAmount"));
        lBasic.setSumPrice(lResultSet.getString("sumPrice"));
        lBasic.setCategoryCode(lResultSet.getString("category"));
        lResultColl.add(lBasic);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lResultColl;
  }
  
  /**
   * <b>getInventoryCollection</b> 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * 
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws なし
   */
  public Collection getInventoryCollection(Connection lConnection, String category) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    UIInventory lBasic = new UIInventory();
    StringBuffer lSqlBuf = new StringBuffer();
    Collection lResultColl = new ArrayList();
    
    lSqlBuf.append("SELECT aa.cmdtycode,aa.individualcode,aa.branchcode,cc.BranchName,aa.storeRecordAmount, ");
    lSqlBuf.append("ee.inventoryStockAmount,bb.cmdtyPrice ,COALESCE(ff.amount,0) AS differenceAmout ");
    lSqlBuf.append("FROM (SELECT stock.cmdtycode,substr(stock.individualcode,1,1) AS category ,");
    lSqlBuf.append("stock.individualcode,stock.branchcode, stock.").append(SIUtil.getAmountName(this.getSeasonCbo())).append(" AS storeRecordAmount ");
    lSqlBuf.append("FROM stockrecordtbl AS stock WHERE stock.stockyear=").append(SIDBUtil.SQL2Str(this.getSeasonYearCbo()));
    lSqlBuf.append(" AND stock.branchcode= ").append(SIDBUtil.SQL2Str(this.getBranchCode()));
    lSqlBuf.append(" AND stock.").append(SIUtil.getAmountName(this.getSeasonCbo())).append(" <> 0 ");
    if (category.equalsIgnoreCase(SIConfig.OTHERCATEGORYCODE)) {// その他の商品
      for (int i = 0; i < SIConfig.CATEGORY.length; i++) {
        lSqlBuf.append(" AND substr(stock.individualcode,1,1) != ").append(SIDBUtil.SQL2Str(SIConfig.CATEGORY[i][1]));
      }
    } else {
      lSqlBuf.append(" AND substr(stock.individualcode,1,1) = ").append(SIDBUtil.SQL2Str(category));
    }
    lSqlBuf.append(" AND substr(stock.individualcode,8,1) != ").append(SIDBUtil.SQL2Str(SIConfig.INDIVIDUALTAIL[SIConfig.INDIVIDUALTAIL_CONSIGN]," "));
    // 在庫コード
    if (SIUtil.isNotNull(this.getIndividualCode())) {
      lSqlBuf.append(" AND stock.IndividualCode LIKE ").append(SIDBUtil.SQL2Str(this.getIndividualCode() + "%"));
    }
    lSqlBuf.append(" ) AS aa ");
    // 在庫単価
    lSqlBuf.append("LEFT OUTER JOIN ");
    lSqlBuf.append("(SELECT cost.cmdtycode,cost.individualcode,(COALESCE(cost.purchaseprice,0)+COALESCE(cost.processingexpence,0)) AS cmdtyPrice FROM costhistorytbl AS cost ");
    lSqlBuf.append(" WHERE cost.season= ").append(SIDBUtil.SQL2Str(this.getSeasonCbo()));
    if (category.equalsIgnoreCase(SIConfig.OTHERCATEGORYCODE)) {// その他の商品
      for (int i = 0; i < SIConfig.CATEGORY.length; i++) {
        lSqlBuf.append(" AND substr(cost.individualcode,1,1) != ").append(SIDBUtil.SQL2Str(SIConfig.CATEGORY[i][1]));
      }
    } else {
      lSqlBuf.append(" AND substr(cost.individualcode,1,1) = ").append(SIDBUtil.SQL2Str(category));
    }
    // 在庫コード
    if (SIUtil.isNotNull(this.getIndividualCode())) {
      lSqlBuf.append(" AND cost.IndividualCode LIKE ").append(SIDBUtil.SQL2Str(this.getIndividualCode() + "%"));
    }
    lSqlBuf.append(" ) AS bb ");
    lSqlBuf.append(" ON bb.cmdtycode=aa.cmdtycode AND bb.individualcode=aa.individualcode ");
    // 支店名
    lSqlBuf.append("LEFT OUTER JOIN (SELECT branchcode,branchName FROM BranchTbl WHERE branchcode = ");
    lSqlBuf.append(this.getBranchCode()).append(") AS cc ON aa.branchcode = cc.branchcode ");
    // 棚卸数
    lSqlBuf.append(" LEFT OUTER JOIN ");
    lSqlBuf.append("(SELECT inventory.branchcode,inventory.cmdtycode,inventory.individualcode,inventory.").append(SIUtil.getAmountName(this.getSeasonCbo())).append(
        " AS inventoryStockAmount ").append(" FROM inventorystocktbl AS inventory ");
    lSqlBuf.append(" WHERE inventory.inventoryyear = ").append(SIDBUtil.SQL2Str(this.getSeasonYearCbo()));
    lSqlBuf.append(" AND inventory.branchcode = ").append(SIDBUtil.SQL2Str(this.getBranchCode()));
    if (category.equalsIgnoreCase(SIConfig.OTHERCATEGORYCODE)) {// その他の商品
      for (int i = 0; i < SIConfig.CATEGORY.length; i++) {
        lSqlBuf.append(" AND substr(inventory.individualcode,1,1) != ").append(SIDBUtil.SQL2Str(SIConfig.CATEGORY[i][1]));
      }
    } else {
      lSqlBuf.append(" AND substr(inventory.individualcode,1,1) = ").append(SIDBUtil.SQL2Str(category));
    }
    // 在庫コード
    if (SIUtil.isNotNull(this.getIndividualCode())) {
      lSqlBuf.append(" AND inventory.IndividualCode LIKE ").append(SIDBUtil.SQL2Str(this.getIndividualCode() + "%"));
    }
    lSqlBuf.append(" ) AS ee ");
    lSqlBuf.append(" ON ee.cmdtycode=aa.cmdtycode AND ee.individualcode=aa.individualcode AND ee.branchcode=aa.branchcode ");
    // 棚卸差異数
    lSqlBuf.append("LEFT OUTER JOIN ");
    lSqlBuf.append("(SELECT id.branchcode,id.cmdtycode,id.individualcode,id.amount ").append(" FROM inventorydifferencetbl AS id ");
    lSqlBuf.append(" WHERE id.season = ").append(SIDBUtil.SQL2Str(this.getSeasonCbo()));
    lSqlBuf.append(" AND id.branchcode = ").append(SIDBUtil.SQL2Str(this.getBranchCode()));
    if (category.equalsIgnoreCase(SIConfig.OTHERCATEGORYCODE)) {// その他の商品
      for (int i = 0; i < SIConfig.CATEGORY.length; i++) {
        lSqlBuf.append(" AND substr(id.individualcode,1,1) != ").append(SIDBUtil.SQL2Str(SIConfig.CATEGORY[i][1]));
      }
    } else {
      lSqlBuf.append(" AND substr(id.individualcode,1,1) = ").append(SIDBUtil.SQL2Str(category));
    }
    // 在庫コード
    if (SIUtil.isNotNull(this.getIndividualCode())) {
      lSqlBuf.append(" AND id.IndividualCode LIKE ").append(SIDBUtil.SQL2Str(this.getIndividualCode() + "%"));
    }
    lSqlBuf.append(" ) AS ff ");
    lSqlBuf.append(" ON ff.cmdtycode=aa.cmdtycode AND ff.individualcode=aa.individualcode AND ff.branchcode=aa.branchcode ");
    // 在庫管理
    if (SIUtil.isNotNull(this.getInstockFlg()) && !this.getInstockFlg().equals("2")) {
      lSqlBuf.append(",(SELECT i.cmdtycode,i.individualcode,i.amountflg from individualtbl as i WHERE i.amountflg=").append(this.getInstockFlg());
      if (category.equalsIgnoreCase(SIConfig.OTHERCATEGORYCODE)) {// その他の商品
        for (int i = 0; i < SIConfig.CATEGORY.length; i++) {
          lSqlBuf.append(" AND substr(i.individualcode,1,1) != ").append(SIDBUtil.SQL2Str(SIConfig.CATEGORY[i][1]));
        }
      } else {
        lSqlBuf.append(" AND substr(i.individualcode,1,1) = ").append(SIDBUtil.SQL2Str(category));
      }
      lSqlBuf.append(" ) as individual ");
    }
    lSqlBuf.append(" WHERE 1 =1 ");
    if (SIUtil.isNotNull(this.getInstockFlg()) && !this.getInstockFlg().equals("2")) {
      lSqlBuf.append(" AND individual.individualcode = aa.individualcode AND individual.cmdtycode = aa.cmdtycode ");
    }
    lSqlBuf.append(this.conditionSQL);
    // 出力対象
    if (SIUtil.isNotNull(this.getInventoryFlg())) {
      if (this.getInventoryFlg().equals("0")) {// 差異あり
        lSqlBuf.append(" AND ff.amount != 0 ");
      } else {
        lSqlBuf.append(" AND (ff.amount = 0 OR ff.amount IS NULL )");
      }
    }
    lSqlBuf.append(" ORDER BY aa.individualcode");
    log.debug("lSqlBuf=" + lSqlBuf.toString());
    
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      
      // 在庫レコードセットの作成
      while (lResultSet.next()) {
        lBasic = new UIInventory();
        lBasic.setBranchCode(lResultSet.getString("branchcode"));
        lBasic.setIndividualCode(lResultSet.getString("individualcode"));
        lBasic.setCmdtyCode(lResultSet.getString("cmdtycode"));
        lBasic.setBranchName(lResultSet.getString("BranchName"));
        lBasic.setStoreRecordAmount(lResultSet.getString("storeRecordAmount"));
        lBasic.setInventoryStockAmount(lResultSet.getString("inventoryStockAmount"));
        lBasic.setCmdtyPrice(lResultSet.getString("cmdtyPrice"));
        lBasic.setInventoryDifference(lResultSet.getString("differenceAmout"));
        lResultColl.add(lBasic);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lResultColl;
  }
  
  /**
   * <b>getIntensiveCollection</b> 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。
   * 
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws なし
   */
  public Collection getIntensiveCollection(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIOrder lBasic = new SIOrder();
    StringBuffer lSqlBuf = new StringBuffer();
    Collection lResultColl = new ArrayList();
    lSqlBuf.append("SELECT a.OrderCode");// 受注番号
    lSqlBuf.append(",a.CustCode");// 顧客コード
    lSqlBuf.append(",a.CustName");// 顧客名
    lSqlBuf.append(",(COALESCE(a.totaloffee,0) + COALESCE(a.DiscountFee,0)) AS Fee");// 代引手数料
    //EDBTG003-00 ohsugi mod start
//    lSqlBuf.append(",-(a.SumOfDiscount) AS SumOfDiscount");// 値引金額
    lSqlBuf.append(",-(a.SumOfDiscount + a.setdiscount) AS SumOfDiscount");// 値引金額
    //EDBTG003-00 ohsugi mod end
    lSqlBuf.append(",-(a.SumByPoint) AS SumByPoint");// ポイント使用額
    lSqlBuf.append(",a.pointEnableFlg");
    lSqlBuf.append(",a.orderStatus");// 取引区分の順番のため
    lSqlBuf.append(",a.Status");
    lSqlBuf.append(",a.InitDateTime");// 受注年月日
    lSqlBuf.append(",a.UpdateDateTime");// 受注更新日
    lSqlBuf.append(",b.branchcode");// 担当支店
    lSqlBuf.append(",bb.ChargeName");// 担当者
    lSqlBuf.append(",bb.chargecode");// 担当者
    lSqlBuf.append(",a.totalofprice");// 販売金額
    lSqlBuf.append(",b.IndividualCode");// 在庫コード
    lSqlBuf.append(",cc.usednewflg");
    lSqlBuf.append(",b.CmdtyName");// 商品名
    lSqlBuf.append(",b.CmdtyCode");// 親コード
    lSqlBuf.append(",b.ShippmentDate");
    lSqlBuf.append(",shippment.branchcode AS shipBrach");// 出荷店舗
    lSqlBuf.append(",shippment.orderallocationnumber AS shipAmount");// 出荷数量
    lSqlBuf.append(",CASE WHEN shippment.branchcode IS NULL THEN b.Amount ELSE shippment.orderallocationnumber END AS amount");
    lSqlBuf.append(",b.Price");// 商品単価
    lSqlBuf.append(",CASE WHEN shippment.branchcode IS NULL THEN b.cmdtyPriceInTax ELSE shippment.orderallocationnumber*b.Price END AS cmdtyPriceInTax");
    lSqlBuf.append(",(COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0)) AS totalOfDeliveryFee");
    lSqlBuf.append(",(CASE WHEN shippment.branchcode IS NULL THEN ((COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0))+b.cmdtyPriceInTax)");
    lSqlBuf.append(" ELSE ((COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0))+shippment.orderallocationnumber*b.Price) END ) AS salesPriceInTax");
    lSqlBuf.append(",(CASE WHEN shippment.branchcode IS NULL THEN CEIL(((COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0))+b.cmdtyPriceInTax)/(1.0 + a.taxrate / 100.0))");
    lSqlBuf.append(" ELSE CEIL(((COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0))+shippment.orderallocationnumber*b.Price)/(1.0 + a.taxrate / 100.0)) END ) AS salesPrice ");
    lSqlBuf.append("FROM (SELECT sum.pointEnableFlg,sum.OrderCode,sum.CustCode,sum.CustName,sum.totaloffee,sum.DiscountFee");
    lSqlBuf.append(",sum.SumOfDiscount,sum.SumByPoint,0 AS orderStatus,sum.OrderRoute,sum.Status,sum.InitDateTime,sum.UpdateDateTime");
    lSqlBuf.append(",sum.branchcode,sum.totalofprice,sum.DiscountDeliveryFee,sum.totalOfDeliveryFee,sum.taxrate,sum.chargecode ");
    lSqlBuf.append(",sum.setdiscount ");//EDBTG003-00 ohsugi add
    lSqlBuf.append("FROM ordersumvw AS sum WHERE sum.InitDateTime >= ").append(SIDBUtil.SQL2Str(this.getSeasonFromCho(), " "));
    lSqlBuf.append("AND sum.InitDateTime <= ").append(SIDBUtil.SQL2Str(this.getSeasonToCho() + " 23:59:59", ") AS a"));
    lSqlBuf.append(",(SELECT detail.branchcode,detail.chargecode,detail.ordercode,detail.IndividualCode,detail.CmdtyName,detail.CmdtyCode");
    lSqlBuf.append(",detail.ShippmentDate,detail.Price,detail.amount,detail.Amount*detail.Price AS cmdtyPriceInTax");
    lSqlBuf.append(",(detail.purchaseprice+detail.processingexpence) AS cmdtyPriceMoto FROM orderdetaillatestvw AS detail ");
    lSqlBuf.append("WHERE 1 = 1 ");
    if (SIUtil.isNotNull(this.getShipmentDateFromCho())) {
      lSqlBuf.append(" AND detail.ShippmentDate >= ").append(SIDBUtil.SQL2Str(this.getShipmentDateFromCho()));
    }
    if (SIUtil.isNotNull(this.getShipmentDateToCho())) {
      lSqlBuf.append(" AND detail.ShippmentDate <= ").append(SIDBUtil.SQL2Str(this.getShipmentDateToCho()));
    }
    lSqlBuf.append(") AS b ");
    lSqlBuf.append("LEFT OUTER JOIN ChargeTbl AS bb ON b.chargecode = bb.chargecode ");
    lSqlBuf.append("LEFT OUTER JOIN OrderAlLocationInfotbl AS shippment ON b.ordercode = shippment.ordercode AND b.IndividualCode = shippment.IndividualCode AND b.CmdtyCode = shippment.CmdtyCode");
    lSqlBuf.append(",(SELECT i.individualCode,i.cmdtycode,i.usednewflg FROM individualtbl AS i ) AS cc ");
    lSqlBuf.append("WHERE a.OrderCode = b.OrderCode AND cc.individualCode = b.individualCode AND cc.cmdtycode = b.cmdtycode ");
    // 検索条件
    lSqlBuf.append(conditionSQL);
    StringBuffer lSqlBuf2 = new StringBuffer();
    lSqlBuf2.append(" UNION SELECT a.OrderCode");// 受注番号
    lSqlBuf2.append(",a.CustCode");// 顧客コード
    lSqlBuf2.append(",a.CustName");// 顧客名
    lSqlBuf2.append(",-(COALESCE(a.totaloffee,0) + COALESCE(a.DiscountFee,0)) AS Fee");// 代引手数料
    //EDBTG003-00 ohsugi mod start
//    lSqlBuf2.append(",(a.SumOfDiscount) AS SumOfDiscount");// 値引金額
    lSqlBuf2.append(",(a.SumOfDiscount + a.setdiscount) AS SumOfDiscount");// 値引金額
    //EDBTG003-00 ohsugi mod end
    lSqlBuf2.append(",(a.SumByPoint) AS SumByPoint");// ポイント使用額
    lSqlBuf2.append(",a.pointEnableFlg");
    lSqlBuf2.append(",(CASE WHEN a.Status='0' THEN 1 WHEN a.Status='2' THEN 2 END) AS orderStatus");// 取引区分の順番のため
    lSqlBuf2.append(",a.Status");
    lSqlBuf2.append(",a.InitDateTime");// 受注年月日
    lSqlBuf2.append(",a.UpdateDateTime");// 受注更新日
    lSqlBuf2.append(",b.branchcode");// 担当支店
    lSqlBuf2.append(",bb.ChargeName");// 担当者
    lSqlBuf2.append(",bb.ChargeCode");// 担当者
    lSqlBuf2.append(",a.totalofprice");// 販売金額
    lSqlBuf2.append(",b.IndividualCode");// 在庫コード
    lSqlBuf2.append(",cc.usednewflg");
    lSqlBuf2.append(",b.CmdtyName");// 商品名
    lSqlBuf2.append(",b.CmdtyCode");// 親コード
    lSqlBuf2.append(",b.ShippmentDate");
    lSqlBuf2.append(",shippment.branchcode AS shipBrach");// 出荷店舗
    lSqlBuf2.append(",shippment.orderallocationnumber AS shipAmount");// 出荷数量
    lSqlBuf2.append(",(CASE WHEN shippment.branchcode IS NULL THEN b.Amount ELSE shippment.orderallocationnumber END) AS amount");
    lSqlBuf2.append(",b.Price");// 商品単価
    lSqlBuf2.append(",(CASE WHEN shippment.branchcode IS NULL THEN -b.cmdtyPriceInTax ELSE -shippment.orderallocationnumber*b.Price END) AS cmdtyPriceInTax");
    lSqlBuf2.append(",-(COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0)) AS totalOfDeliveryFee");
    lSqlBuf2.append(",(CASE WHEN shippment.branchcode IS NULL THEN -((COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0))+b.cmdtyPriceInTax)");
    lSqlBuf2.append(" ELSE -((COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0)) + shippment.orderallocationnumber*b.Price) END) AS salesPriceInTax");
    lSqlBuf2.append(",(CASE WHEN shippment.branchcode IS NULL THEN -CEIL(((COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0))+b.cmdtyPriceInTax)/(1.0 + a.taxrate / 100.0))");
    lSqlBuf2.append(" ELSE -CEIL(((COALESCE(a.DiscountDeliveryFee,0) + COALESCE(a.totalOfDeliveryFee,0)) + shippment.orderallocationnumber*b.Price)/(1.0 + a.taxrate / 100.0)) END) AS salesPrice ");
    lSqlBuf2.append("FROM (SELECT sum.pointEnableFlg,sum.OrderCode,sum.CustCode,sum.CustName,sum.totaloffee,sum.DiscountFee,sum.SumOfDiscount");
    lSqlBuf2.append(",sum.SumByPoint,(CASE WHEN sum.Status=0 THEN 1 WHEN sum.Status=2 THEN 2 END) AS orderStatus,sum.OrderRoute,sum.Status,sum.InitDateTime");
    lSqlBuf2.append(",sum.UpdateDateTime,sum.branchcode,sum.totalofprice,sum.DiscountDeliveryFee,sum.totalOfDeliveryFee,sum.taxrate,sum.chargecode ");
    lSqlBuf2.append(",sum.setdiscount ");//EDBTG003-00 ohsugi add
    lSqlBuf2.append("FROM ordersumvw AS sum WHERE sum.InitDateTime >= ").append(SIDBUtil.SQL2Str(this.getSeasonFromCho(), " "));
    lSqlBuf2.append("AND sum.InitDateTime <= ").append(SIDBUtil.SQL2Str(this.getSeasonToCho() + " 23:59:59", " "));
    lSqlBuf2.append("AND sum.Status != '1') AS a");
    lSqlBuf2.append(",(SELECT detail.branchcode,detail.chargecode,detail.ordercode,detail.IndividualCode,detail.CmdtyName,detail.CmdtyCode,detail.ShippmentDate,detail.Price");
    lSqlBuf2.append(",detail.amount,detail.Amount*detail.Price AS cmdtyPriceInTax,(detail.purchaseprice+detail.processingexpence) AS cmdtyPriceMoto FROM orderdetaillatestvw AS detail ");
    lSqlBuf2.append("WHERE 1 = 1 ");
    if (SIUtil.isNotNull(this.getShipmentDateFromCho())) {
      lSqlBuf2.append(" AND detail.ShippmentDate >= ").append(SIDBUtil.SQL2Str(this.getShipmentDateFromCho()));
    }
    if (SIUtil.isNotNull(this.getShipmentDateToCho())) {
      lSqlBuf2.append(" AND detail.ShippmentDate <= ").append(SIDBUtil.SQL2Str(this.getShipmentDateToCho()));
    }
    lSqlBuf2.append(") AS b ");
    lSqlBuf2.append("LEFT OUTER JOIN ChargeTbl AS bb ON b.chargecode = bb.chargecode ");
    lSqlBuf2.append("LEFT OUTER JOIN OrderAlLocationInfotbl AS shippment ON b.ordercode = shippment.ordercode AND b.IndividualCode = shippment.IndividualCode AND b.CmdtyCode = shippment.CmdtyCode");
    lSqlBuf2.append(",(SELECT i.individualCode,i.cmdtycode,i.usednewflg FROM individualtbl AS i ) AS cc ");
    lSqlBuf2.append("WHERE a.OrderCode = b.OrderCode AND cc.individualCode = b.individualCode AND cc.cmdtycode = b.cmdtycode ");
    lSqlBuf2.append(conditionSQL);
    // ソート
    lSqlBuf2.append(" ORDER BY OrderCode,orderStatus");
    lSqlBuf.append(lSqlBuf2.toString());
    log.debug("lSqlBuf=" + lSqlBuf.toString());
    this.selectSQL = lSqlBuf.toString();
    
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      
      // 売上レコードのセットの作成
      while (lResultSet.next()) {
        lBasic = new SIOrder();
        lBasic.setOrderCode(lResultSet.getString("orderCode"));// 受注番号
        lBasic.setCustCode(lResultSet.getString("custCode"));// 顧客コード
        lBasic.setCustName(lResultSet.getString("custName"));// 顧客名
        lBasic.setTotalOfFee(lResultSet.getString("fee"));// 手数料(必須)
        lBasic.setSumOfDiscount(lResultSet.getString("SumOfDiscount"));
        lBasic.setSumByPoint(lResultSet.getString("SumByPoint"));
        lBasic.setPointEnableFlg(lResultSet.getString("pointEnableFlg"));
        lBasic.setStatus(lResultSet.getString("Status"));
        lBasic.setOrderStatus(lResultSet.getString("orderStatus"));
        lBasic.setInitDateTime(SIDBUtil.getDateTime(lResultSet.getTimestamp("InitDateTime")));
        lBasic.setUpdateDateTime(SIDBUtil.getDateTime(lResultSet.getTimestamp("UpdateDateTime")));
        lBasic.setBranchCode(lResultSet.getString("branchCode"));
        lBasic.setChargeName(lResultSet.getString("ChargeName"));
        lBasic.setChargeCode(lResultSet.getString("ChargeCode"));
        lBasic.setIndividualCode(lResultSet.getString("individualCode"));
        lBasic.setUsednewFlg(lResultSet.getString("usednewflg"));
        lBasic.setCmdtyName(lResultSet.getString("cmdtyName"));
        lBasic.setCmdtyCode(lResultSet.getString("cmdtyCode"));
        lBasic.setAmount(lResultSet.getString("amount"));
        lBasic.setPrice(lResultSet.getString("price"));
        lBasic.setShippmentDate(SIDBUtil.getDateTime(lResultSet.getTimestamp("shippmentDate")));
        lBasic.setTotalOfDeliveryFee(lResultSet.getString("totalOfDeliveryFee"));
        lBasic.setSalesPriceInTax(lResultSet.getString("salesPriceInTax"));
        lBasic.setSalesPrice(lResultSet.getString("salesPrice"));
        lBasic.setCmdtyPriceInTax(lResultSet.getString("cmdtyPriceInTax"));
        lBasic.setShipBranch(lResultSet.getString("shipBrach"));
        lBasic.setShipAmount(lResultSet.getString("shipAmount"));
        // lBasic.setProfitPrice(lResultSet.getString("profitPrice"));
        lResultColl.add(lBasic);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lResultColl;
  }
  
  /**
   * <b>getStoreHistoryCollection</b> 条件に合ったレコードを検索して、結果のコネクションを作成して、戻します。[入出庫一覧表]
   * 
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws なし
   */
  public Collection getStoreHistoryCollection(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    UIInventory lBasic = new UIInventory();
    StringBuffer lSqlBuf = new StringBuffer();
    Collection lResultColl = new ArrayList();
    lSqlBuf.append("SELECT b.flg,b.initdatetime,b.cmdtycode ,b.individualcode,b.category,b.cmdtyPrice,b.type, ");
    lSqlBuf.append("b.amount,c.stockAmount,aa.branchName,bb.chargeName,costhistory.cost ");
    lSqlBuf.append("FROM ");
    lSqlBuf.append("(");
    // 入庫数
    lSqlBuf.append("SELECT 0 AS flg,sh.initdatetime,sh.cmdtycode,sh.individualcode,substr(sh.individualcode,1,1) AS category,sh.amount AS Amount, ");
    lSqlBuf.append("(sh.purchaseprice+sh.processingexpence) as cmdtyPrice, sh.storenumber AS keynumber, ");
    lSqlBuf.append("sh.storetype as type,sh.storebranch as branch, sh.storecharge as charge ");
    lSqlBuf.append("FROM storehistorytbl AS sh ");
    lSqlBuf.append("WHERE sh.delflg IN ('1','4') ");
    lSqlBuf.append("AND to_char(sh.initdatetime,'YYYYMM')=").append(SIDBUtil.SQL2Str(this.getSeasonCbo(), " "));
    lSqlBuf.append("AND (sh.storenumber != 1 OR sh.amount != 0 ) ");
    // 出庫数
    lSqlBuf.append("UNION ALL ");
    lSqlBuf.append("SELECT 1 AS flg, sp.initdatetime,sp.cmdtycode,sp.individualcode,substr(sp.individualcode,1,1) AS category ,sp.amount AS Amount, ");
    lSqlBuf.append("(sp.purchaseprice+sp.processingexpence) as cmdtyPrice , sp.shipnumber AS keynumber, ");
    lSqlBuf.append("sp.shiptype as type,sp.shipbranch as branch, sp.shipcharge as charge ");
    lSqlBuf.append("FROM shiphistorytbl AS sp ");
    lSqlBuf.append("WHERE sp.delflg IN ('1','4') ");
    lSqlBuf.append("AND to_char(sp.initdatetime,'YYYYMM')=").append(SIDBUtil.SQL2Str(this.getSeasonCbo(), " "));
    lSqlBuf.append("AND ( sp.shipnumber != 1 OR sp.amount != 0 ) ");
    lSqlBuf.append("ORDER BY cmdtycode,individualcode,initdatetime,flg,keynumber ");
    lSqlBuf.append(") AS b ");
    // 繰越在庫数
    lSqlBuf.append("LEFT OUTER JOIN ");
    lSqlBuf.append("(SELECT stock.cmdtycode,stock.individualcode,sum(stock.").append(SIUtil.getPreAmountName(this.getSeasonCbo())).append(" ) AS stockAmount ");
    lSqlBuf.append("FROM stockrecordtbl AS stock ");
    lSqlBuf.append("WHERE stock.stockyear = ").append(SIDBUtil.SQL2Str(SIUtil.getPreYearName(this.getSeasonCbo())," "));
    lSqlBuf.append("GROUP BY stock.cmdtycode,stock.individualcode ) AS c ");
    lSqlBuf.append("ON b.cmdtycode=c.cmdtycode AND b.individualcode=c.individualcode ");
    // 繰越原価
    lSqlBuf.append("LEFT OUTER JOIN ");
    lSqlBuf.append("(SELECT cmdtycode,individualcode,purchaseprice+processingexpence AS cost FROM CostHistoryTbl ");
    lSqlBuf.append("WHERE season = ").append(SIDBUtil.SQL2Str(SIUtil.getPreSeasonName(this.getSeasonCbo()), ") AS costhistory "));
    lSqlBuf.append("ON costhistory.cmdtycode = b.cmdtycode AND costhistory.individualcode=c.individualcode ");
    // 担当支店名
    lSqlBuf.append("LEFT OUTER JOIN ");
    lSqlBuf.append("BranchTbl AS aa ");
    lSqlBuf.append("ON b.branch = aa.branchcode ");
    // 担当者名
    lSqlBuf.append("LEFT OUTER JOIN ");
    lSqlBuf.append("ChargeTbl AS bb ");
    lSqlBuf.append("ON b.charge = bb.chargecode ");
    
    lSqlBuf.append("WHERE 1=1 ");
    lSqlBuf.append(this.conditionSQL);
    lSqlBuf.append(" ORDER BY individualcode,initdatetime ");
    
    log.debug("lSqlBuf=" + lSqlBuf.toString());
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      
      // レコードセットの作成
      while (lResultSet.next()) {
        lBasic = new UIInventory();
        lBasic.setIndividualCode(lResultSet.getString("individualcode"));
        lBasic.setCmdtyCode(lResultSet.getString("cmdtycode"));
        lBasic.setIndividualCode(lResultSet.getString("individualCode"));
        lBasic.setStoreRecordAmount(lResultSet.getString("stockAmount"));
        lBasic.setCmdtyPrice(lResultSet.getString("cmdtyPrice"));
        lBasic.setAmount(lResultSet.getString("Amount"));// 入庫数・出庫数・調整数
        lBasic.setBranchName(lResultSet.getString("branchName"));
        lBasic.setChargeName(lResultSet.getString("chargeName"));
        lBasic.setInitdatetime(SIDBUtil.getDateTime(lResultSet.getTimestamp("InitDateTime")));
        lBasic.setSumPrice(lResultSet.getString("cost"));
        lBasic.setStoreshipFlg(lResultSet.getString("flg"));
        lBasic.setType(lResultSet.getString("type"));
        lResultColl.add(lBasic);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lResultColl;
  }
  
  public HashMap getMarketingCollection(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIMarketingReportData lBasic = new SIMarketingReportData();
    StringBuffer lSqlBuf = new StringBuffer();
    HashMap lResult = new HashMap();
    // カテゴリ別集計（大カテゴリ文字）
    lSqlBuf.append("(SELECT 0 AS flg");
    lSqlBuf.append(",ROUND(sum(CASE i.consigngoodsflg WHEN '0' THEN a.price ELSE a.consigngoodsfee END * a.amount)/1000) AS totalofprice");
    lSqlBuf.append(",0::numeric AS custcount");
    lSqlBuf.append(",substr(a.individualcode,1,1)::text as keyword");
    lSqlBuf.append(",to_char(b.initdatetime,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM orderdetailtbl AS a,orderlatestvw AS b,individualtbl i ");
    lSqlBuf.append("WHERE a.ordercode = b.ordercode AND a.individualcode = i.individualcode AND b.status='1' AND a.shippmentdate IS NOT NULL ");
    lSqlBuf.append(this.conditionSQL);
    lSqlBuf.append(" AND a.orderbranchcode = b.orderbranchcode");
    lSqlBuf.append(" AND to_char(b.initdatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(this.getSeasonYearCbo() + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(b.initdatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(this.getSeasonYearCbo(), "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" GROUP BY substr(a.individualcode,1,1),to_char(b.initdatetime,'yyyymm')) ");
    // 中古新品集計
    lSqlBuf.append("UNION ");
    lSqlBuf.append("(SELECT 1 AS flg");
    lSqlBuf.append(",ROUND(sum(CASE i.consigngoodsflg WHEN '0' THEN a.price ELSE a.consigngoodsfee END * a.amount)/1000) AS totalofprice");
    lSqlBuf.append(",0::numeric AS custcount");
    lSqlBuf.append(",CASE WHEN i.usednewflg IN ('2','4') THEN 'A' WHEN i.usednewflg IN ('1','6') THEN 'N' WHEN i.usednewflg = '7' THEN 'P' ELSE 'U' END AS keyword");
    lSqlBuf.append(",to_char(b.initdatetime,'yyyymm') AS initdatetime ");
    lSqlBuf.append("FROM orderdetailtbl AS a,orderlatestvw AS b,individualtbl i ");
    lSqlBuf.append("WHERE a.ordercode = b.ordercode AND a.individualcode = i.individualcode AND b.status='1' AND a.shippmentdate IS NOT NULL ");
    lSqlBuf.append(this.conditionSQL);
    lSqlBuf.append(" AND a.orderbranchcode = b.orderbranchcode");
    lSqlBuf.append(" AND to_char(b.initdatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(this.getSeasonYearCbo() + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(b.initdatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(this.getSeasonYearCbo(), "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" GROUP BY CASE WHEN i.usednewflg IN ('2','4') THEN 'A' WHEN i.usednewflg IN ('1','6') THEN 'N' ");
    lSqlBuf.append(" WHEN i.usednewflg = '7' THEN 'P' ELSE 'U' END ");
    lSqlBuf.append(",to_char(b.initdatetime,'yyyymm')) ");
    // 受注経路別集計（携帯：6、それ以外：0）
    lSqlBuf.append("UNION ");
    lSqlBuf.append("(SELECT 2 AS flg");
    lSqlBuf.append(",ROUND(sum(CASE i.consigngoodsflg WHEN '0' THEN a.price ELSE a.consigngoodsfee END * a.amount)/1000) AS totalofprice");
    lSqlBuf.append(",count(distinct b.ordercode) AS custcount");
    lSqlBuf.append(",(CASE b.orderroute WHEN '6' THEN '0' ELSE b.orderroute END) as keyword");
    lSqlBuf.append(",to_char(b.initdatetime,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM orderdetailtbl AS a,orderlatestvw AS b,individualtbl i ");
    lSqlBuf.append("WHERE a.ordercode = b.ordercode AND a.individualcode = i.individualcode AND b.status='1' AND a.shippmentdate IS NOT NULL ");
    lSqlBuf.append(this.conditionSQL);
    lSqlBuf.append(" AND a.orderbranchcode = b.orderbranchcode");
    lSqlBuf.append(" AND to_char(b.initdatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(this.getSeasonYearCbo() + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(b.initdatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(this.getSeasonYearCbo(), "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" GROUP BY b.orderroute,to_char(b.initdatetime,'yyyymm')) ");
    // 営業形態別集計（営業形態）
    lSqlBuf.append("UNION ");
    lSqlBuf.append("(SELECT 3 AS flg");
    lSqlBuf.append(",ROUND(sum(CASE i.consigngoodsflg WHEN '0' THEN a.price ELSE a.consigngoodsfee END * a.amount)/1000) AS totalofprice");
    lSqlBuf.append(",count(distinct b.ordercode) AS custcount");
    lSqlBuf.append(",b.job as keyword");
    lSqlBuf.append(",to_char(b.initdatetime,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM orderdetailtbl AS a,orderlatestvw AS b,individualtbl i ");
    lSqlBuf.append("WHERE a.ordercode = b.ordercode AND a.individualcode = i.individualcode AND b.status='1' AND a.shippmentdate IS NOT NULL ");
    lSqlBuf.append(this.conditionSQL);
    lSqlBuf.append(" AND a.orderbranchcode = b.orderbranchcode");
    lSqlBuf.append(" AND to_char(b.initdatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(this.getSeasonYearCbo() + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(b.initdatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(this.getSeasonYearCbo(), "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" GROUP BY b.job,to_char(b.initdatetime,'yyyymm')) ");
    // 支払方法別集計（支払方法名称）
    lSqlBuf.append("UNION ");
    lSqlBuf.append("(SELECT 4 AS flg");
    lSqlBuf.append(",ROUND(sum(CASE i.consigngoodsflg WHEN '0' THEN a.price ELSE a.consigngoodsfee END * a.amount)/1000) AS totalofprice");
    lSqlBuf.append(",count(distinct b.ordercode) AS custcount");
    lSqlBuf.append(",b.paymethodname as keyword");
    lSqlBuf.append(",to_char(b.initdatetime,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM orderdetailtbl AS a,orderlatestvw AS b,individualtbl i ");
    lSqlBuf.append("WHERE a.ordercode = b.ordercode AND a.individualcode = i.individualcode AND b.status='1' AND a.shippmentdate IS NOT NULL ");
    lSqlBuf.append(this.conditionSQL);
    lSqlBuf.append(" AND a.orderbranchcode = b.orderbranchcode");
    lSqlBuf.append(" AND to_char(b.initdatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(this.getSeasonYearCbo() + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(b.initdatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(this.getSeasonYearCbo(), "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" GROUP BY b.paymethodname,to_char(b.initdatetime,'yyyymm'))");
    // 新顧客集計（new）
    lSqlBuf.append("UNION ");
    lSqlBuf.append("(SELECT 5 AS flg");
    lSqlBuf.append(",ROUND(sum(CASE i.consigngoodsflg WHEN '0' THEN a.price ELSE a.consigngoodsfee END * a.amount)/1000) AS totalofprice");
    lSqlBuf.append(",count(distinct b.ordercode) AS custcount");
    lSqlBuf.append(",'new' as keyword");
    lSqlBuf.append(",to_char(b.initdatetime,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM orderdetailtbl AS a,orderlatestvw AS b,individualtbl i,custaddresstbl c ");
    lSqlBuf.append("WHERE a.ordercode = b.ordercode AND a.individualcode = i.individualcode AND b.status='1' AND a.shippmentdate IS NOT NULL ");
    lSqlBuf.append(" AND b.custcode = c.custcode AND c.addresscode='0'");
    lSqlBuf.append(" AND to_char(b.initdatetime,'yyyymm') = to_char(c.initdatetime,'yyyymm') ");
    lSqlBuf.append(this.conditionSQL);
    lSqlBuf.append(" AND a.orderbranchcode = b.orderbranchcode");
    lSqlBuf.append(" AND to_char(b.initdatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(this.getSeasonYearCbo() + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(b.initdatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(this.getSeasonYearCbo(), "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" GROUP BY b.paymethodname,to_char(b.initdatetime,'yyyymm'))");
    // 旧顧客集計（old）
    lSqlBuf.append("UNION ");
    lSqlBuf.append("(SELECT 6 AS flg");
    lSqlBuf.append(",ROUND(sum(CASE i.consigngoodsflg WHEN '0' THEN a.price ELSE a.consigngoodsfee END * a.amount)/1000) AS totalofprice");
    lSqlBuf.append(",count(distinct b.ordercode) AS custcount");
    lSqlBuf.append(",'old' as keyword");
    lSqlBuf.append(",to_char(b.initdatetime,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM orderdetailtbl AS a,orderlatestvw AS b,individualtbl i,custaddresstbl c ");
    lSqlBuf.append("WHERE a.ordercode = b.ordercode AND a.individualcode = i.individualcode AND b.status='1' AND a.shippmentdate IS NOT NULL ");
    lSqlBuf.append(" AND b.custcode = c.custcode AND c.addresscode=0");
    lSqlBuf.append(" AND to_char(b.initdatetime,'yyyymm') > to_char(c.initdatetime,'yyyymm') ");
    lSqlBuf.append(this.conditionSQL);
    lSqlBuf.append(" AND a.orderbranchcode = b.orderbranchcode");
    lSqlBuf.append(" AND to_char(b.initdatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(this.getSeasonYearCbo() + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(b.initdatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(this.getSeasonYearCbo(), "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" GROUP BY b.paymethodname,to_char(b.initdatetime,'yyyymm'))");
    
    log.debug("lSqlBuf=" + lSqlBuf.toString());
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      while (lResultSet.next()) {
        lBasic = new SIMarketingReportData();
        lBasic.setReportFlg(lResultSet.getString("flg"));
        lBasic.setTotalOfPrice(lResultSet.getString("totalofprice"));
        lBasic.setCustCount(lResultSet.getString("custcount"));
        lBasic.setInitdatetime(lResultSet.getString("initdatetime"));
        lBasic.setKeyWord1(lResultSet.getString("keyword"));
        lResult.put(lBasic.getObjectKey(), lBasic);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lResult;
  }
  
  public String[][] getCustAttrList(Connection lConnection) {
    Collection result = new ArrayList();
    String[][] res = null;
    String countsql = "SELECT count(attrname) FROM custattrmtbl WHERE attrflag='0'";
    String sql = "SELECT attrname||'注文',attrname FROM custattrmtbl WHERE attrflag='0' ORDER BY attrcode";
    try {
      int count = Integer.parseInt(SIDBUtil.getFirstData(lConnection, countsql));
      result = SIDBUtil.getCollection(lConnection, sql);
      Iterator ite = result.iterator();
      res = new String[count][2];
      int index = 0;
      while(ite.hasNext()){
        SINameValue val = (SINameValue)ite.next();
        res[index][0] = val.getName();
        res[index][1] = val.getValue();
        index++;
      }
    }catch(Exception e){}
    if (res == null) res = new String[0][0];
    return res;
  }
  
  public HashMap getCustInfo(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    StringBuffer lSqlBuf = new StringBuffer();
    String[][] custAttr = getCustAttrList(lConnection);
    HashMap lResult = new HashMap();
    
    lSqlBuf.append("SELECT COUNT(cu.custcode) AS totalCust");
    
    for (int index=0;index<custAttr.length;index++) {
      lSqlBuf.append(",SUM(CASE WHEN job=").append(SIDBUtil.SQL2Str(custAttr[index][1])).append(" THEN 1 ELSE 0 END) AS total" + index);
    }
    lSqlBuf.append(",SUM(cu.newmailflg) AS totalInfoMail ");
    lSqlBuf.append(",to_char(ca.initdatetime,'yyyymm') AS initdatetime ");
    lSqlBuf.append("FROM custtbl AS cu,custaddresstbl ca ");
    lSqlBuf.append("WHERE to_char(ca.initdatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(this.getSeasonYearCbo() + SIConfig.SEASONFROM, " "));
    lSqlBuf.append("AND to_char(ca.initdatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(this.getSeasonYearCbo(), "1") + SIConfig.SEASONTO, " "));
    lSqlBuf.append("AND cu.custcode=ca.custcode AND ca.addresscode='0' AND cu.delflg='0' ");
    lSqlBuf.append("GROUP BY to_char(ca.initdatetime,'yyyymm') ");
    log.debug("lSqlBuf=" + lSqlBuf.toString());
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      while (lResultSet.next()) {
        for (int i=0;i<custAttr.length;i++) {
          lResult.put("total" + i + "_" + lResultSet.getString("initdatetime"), lResultSet.getString("total" + i));
        }
        lResult.put("total" + (custAttr.length + 1) + "_" + lResultSet.getString("initdatetime"), lResultSet.getString("totalInfoMail"));
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lResult;
  }
  
  public HashMap getPointInfo(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIMarketingReportData lBasic = new SIMarketingReportData();
    StringBuffer lSqlBuf = new StringBuffer();
    HashMap lResult = new HashMap();
    lSqlBuf.append("SELECT SUM(CASE WHEN enableflg = '0' THEN pointofissue ELSE 0 END) AS totalOfInvalid");
    lSqlBuf.append(",SUM(CASE WHEN pointofissue > 0 THEN pointofissue ELSE 0 END) AS totalOfTemporary");
    lSqlBuf.append(",SUM(CASE WHEN enableflg = '1' AND pointofissue > 0 THEN pointofissue ELSE 0 END) AS totalOfEffective");
    lSqlBuf.append(",SUM(CASE WHEN enableflg = '1' AND pointofissue < 0 THEN pointofissue ELSE 0 END) AS sumbypoint");
    lSqlBuf.append(",to_char(aa.IssueDateTime,'yyyymm') AS initdatetime ");
    lSqlBuf.append("FROM PointManMTbl AS aa ");
    lSqlBuf.append("WHERE to_char(aa.IssueDateTime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(this.getSeasonYearCbo() + SIConfig.SEASONFROM, " "));
    lSqlBuf.append("AND to_char(aa.IssueDateTime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(this.getSeasonYearCbo(), "1") + SIConfig.SEASONTO, " "));
    lSqlBuf.append("GROUP BY to_char(aa.IssueDateTime,'yyyymm') ");
    log.debug("lSqlBuf=" + lSqlBuf.toString());
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      while (lResultSet.next()) {
        lBasic = new SIMarketingReportData();
        lBasic.setTotalOfInvalid(lResultSet.getString("totalOfInvalid"));
        lBasic.setTotalOfTemporary(lResultSet.getString("totalOfTemporary"));
        lBasic.setTotalOfEffective(lResultSet.getString("totalOfEffective"));
        lBasic.setSumbypoint(lResultSet.getString("sumbypoint"));
        lBasic.setInitdatetime(lResultSet.getString("initdatetime"));
        lResult.put(lBasic.getInitdatetime(), lBasic);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lResult;
  }
  
  public HashMap getTrendCollection(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIMarketingReportData lBasic = new SIMarketingReportData();
    StringBuffer lSqlBuf = new StringBuffer();
    HashMap lResult = new HashMap();
    lSqlBuf.append("SELECT substr(a.individualcode,8,1) as keyword, ");
    // lSqlBuf.append(" SUM(CEIL((a.price * a.amount)/ (1.0 + a.taxrate / 100.0))) AS totalofprice , ");
    // lSqlBuf.append(" SUM(CEIL((a.price * a.amount)/ (1.0 + a.taxrate / 100.0))) - SUM(CEIL(((a.processingexpence+a.purchaseprice) * a.amount)/ (1.0 + a.taxrate / 100.0))) AS
    // totalOfCost,");
    lSqlBuf.append(" SUM(a.price * a.amount) AS totalofprice , ");
    lSqlBuf.append(" SUM(a.price * a.amount) - SUM((a.processingexpence+a.purchaseprice) * a.amount) AS totalOfCost,");
    lSqlBuf.append(" to_char(b.initdatetime,'yyyymm') as initdatetime ");
    lSqlBuf.append(" FROM orderdetailtbl AS a ,orderlatestvw AS b ");
    lSqlBuf.append(" WHERE a.ordercode = b.ordercode AND a.orderbranchcode = b.orderbranchcode ");
    lSqlBuf.append(" AND to_char(b.initdatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(this.getSeasonYearCbo() + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(b.initdatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(this.getSeasonYearCbo(), "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" AND substr(a.individualcode,8,1) != ").append(SIDBUtil.SQL2Str(SIConfig.INDIVIDUALTAIL[SIConfig.INDIVIDUALTAIL_CONSIGN]," "));
    lSqlBuf.append(this.conditionSQL);
    lSqlBuf.append(" GROUP BY substr(a.individualcode,8,1),to_char(b.initdatetime,'yyyymm') ");
    log.debug("lSqlBuf=" + lSqlBuf.toString());
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      while (lResultSet.next()) {
        lBasic = new SIMarketingReportData();
        lBasic.setTotalOfPrice(lResultSet.getString("totalofprice"));
        lBasic.setTotalOfCost(lResultSet.getString("totalOfCost"));
        lBasic.setInitdatetime(lResultSet.getString("initdatetime"));
        lBasic.setKeyWord1(lResultSet.getString("keyword"));
        lResult.put(lBasic.getInitdatetime() + lBasic.getKeyWord1(), lBasic);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lResult;
  }
  
  public HashMap getStoreHistory(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIMarketingReportData lBasic = new SIMarketingReportData();
    StringBuffer lSqlBuf = new StringBuffer();
    HashMap lResult = new HashMap();
    lSqlBuf.append(" SELECT to_char(sh.initdatetime,'yyyymm') as initdatetime, SUM(sh.purchaseprice+sh.processingexpence) AS count ");
    lSqlBuf.append(" FROM storehistorytbl AS sh ");
    lSqlBuf.append(" WHERE sh.delflg IN ('1','4') ");
    lSqlBuf.append(" AND sh.storetype = '1' ");
    lSqlBuf.append(" AND to_char(sh.initdatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(this.getSeasonYearCbo() + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(sh.initdatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(this.getSeasonYearCbo(), "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" GROUP BY to_char(sh.initdatetime,'yyyymm') ");
    
    log.debug("lSqlBuf=" + lSqlBuf.toString());
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      while (lResultSet.next()) {
        lBasic = new SIMarketingReportData();
        lBasic.setInitdatetime(lResultSet.getString("initdatetime"));
        lBasic.setCount(lResultSet.getString("count"));
        lResult.put(lBasic.getInitdatetime() + lBasic.getKeyWord1(), lBasic);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return lResult;
  }
  
  /**
   * <b>getCompanyCollection<b> 条件に合ったレコードを検索して、結果のコレクションを作成して戻します。
   * 
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws SIDBAccessException
   */
  public Collection getCompanyCollection(Connection lConnection) throws SIDBAccessException {
    String lSql = "SELECT BranchName,BranchCode FROM BranchTbl ORDER BY BranchCode ASC";
    Collection lResultColl = new ArrayList();
    
    log.debug("getCompanyCollection:lSqlBuf=" + lSql);
    try {
      lResultColl = SIDBUtil.getCollection(lConnection, lSql, false, true);
    } catch (SIDBAccessException e) {
      e.printStackTrace();
    }
    return lResultColl;
  }
  
  /**
   * <b>getChargeCollection<b> 条件に合ったレコードを検索して、結果のコレクションを作成して戻します。
   * 
   * @param lConnection データベースへの接続コネクション
   * @return レコードのセット
   * @throws SIDBAccessException
   */
  public Collection getChargeCollection(Connection lConnection) throws SIDBAccessException {
    String lSql = "SELECT ChargeName,ChargeCode FROM ChargeTbl ORDER BY ChargeCode ASC";
    Collection lResultColl = new ArrayList();
    
    log.debug("getChargeCollection:lSqlBuf=" + lSql);
    try {
      lResultColl = SIDBUtil.getCollection(lConnection, lSql, false, true);
    } catch (SIDBAccessException e) {
      e.printStackTrace();
    }
    return lResultColl;
  }
  
  /**
   * @return shipmentDateDayFromCbo を戻します。
   */
  public String getShipmentDateDayFromCbo() {
    return shipmentDateDayFromCbo;
  }
  
  /**
   * @param shipmentDateDayFromCbo shipmentDateDayFromCbo を設定。
   */
  public void setShipmentDateDayFromCbo(String shipmentDateDayFromCbo) {
    this.shipmentDateDayFromCbo = shipmentDateDayFromCbo;
  }
  
  /**
   * @return shipmentDateDayToCbo を戻します。
   */
  public String getShipmentDateDayToCbo() {
    return shipmentDateDayToCbo;
  }
  
  /**
   * @param shipmentDateDayToCbo shipmentDateDayToCbo を設定。
   */
  public void setShipmentDateDayToCbo(String shipmentDateDayToCbo) {
    this.shipmentDateDayToCbo = shipmentDateDayToCbo;
  }
  
  /**
   * @return shipmentDateMonthFromCbo を戻します。
   */
  public String getShipmentDateMonthFromCbo() {
    return shipmentDateMonthFromCbo;
  }
  
  /**
   * @param shipmentDateMonthFromCbo shipmentDateMonthFromCbo を設定。
   */
  public void setShipmentDateMonthFromCbo(String shipmentDateMonthFromCbo) {
    this.shipmentDateMonthFromCbo = shipmentDateMonthFromCbo;
  }
  
  /**
   * @return shipmentDateMonthToCbo を戻します。
   */
  public String getShipmentDateMonthToCbo() {
    return shipmentDateMonthToCbo;
  }
  
  /**
   * @param shipmentDateMonthToCbo shipmentDateMonthToCbo を設定。
   */
  public void setShipmentDateMonthToCbo(String shipmentDateMonthToCbo) {
    this.shipmentDateMonthToCbo = shipmentDateMonthToCbo;
  }
  
  /**
   * @return shipmentDateYearFromCbo を戻します。
   */
  public String getShipmentDateYearFromCbo() {
    return shipmentDateYearFromCbo;
  }
  
  /**
   * @param shipmentDateYearFromCbo shipmentDateYearFromCbo を設定。
   */
  public void setShipmentDateYearFromCbo(String shipmentDateYearFromCbo) {
    this.shipmentDateYearFromCbo = shipmentDateYearFromCbo;
  }
  
  /**
   * @return shipmentDateYearToCbo を戻します。
   */
  public String getShipmentDateYearToCbo() {
    return shipmentDateYearToCbo;
  }
  
  /**
   * @param shipmentDateYearToCbo shipmentDateYearToCbo を設定。
   */
  public void setShipmentDateYearToCbo(String shipmentDateYearToCbo) {
    this.shipmentDateYearToCbo = shipmentDateYearToCbo;
  }
  
  public String getShipmentDateFromCho() {
    if (SIUtil.isNotNull(this.getShipmentDateYearFromCbo()) || SIUtil.isNotNull(this.getShipmentDateMonthFromCbo()) || SIUtil.isNotNull(this.getShipmentDateDayFromCbo())) {
      return this.getShipmentDateYearFromCbo() + "/" + this.getShipmentDateMonthFromCbo() + "/" + this.getShipmentDateDayFromCbo();
    } else {
      return "";
    }
  }
  
  public String getShipmentDateToCho() {
    if (SIUtil.isNotNull(this.getShipmentDateYearToCbo()) || SIUtil.isNotNull(this.getShipmentDateMonthToCbo()) || SIUtil.isNotNull(this.getShipmentDateDayToCbo())) {
      return this.getShipmentDateYearToCbo() + "/" + this.getShipmentDateMonthToCbo() + "/" + this.getShipmentDateDayToCbo();
    } else {
      return "";
    }
  }
  
  /**
   * @return tblNameSel を戻します。
   */
  public String getTblNameSel() {
    return tblNameSel;
  }
  
  /**
   * @param tblNameSel tblNameSel を設定。
   */
  public void setTblNameSel(String tblNameSel) {
    this.tblNameSel = tblNameSel;
  }
  
  /**
   * @return branchCode を戻します。
   */
  public String getBranchCode() {
    return branchCode;
  }
  
  /**
   * @return branchCode を戻します。
   */
  public String getHeaderBranchCode(String branchName) {
    String result = "全選択";
    if (SIUtil.isNotNull(this.branchCode)) {
      result = branchName;
    }
    return result;
  }
  
  /**
   * @return branchCode を戻します。
   */
  public String getHeaderBranchCode(Connection lConnection) {
    String result = "全選択";
    if (SIUtil.isNotNull(this.branchCode)) {
      Collection branchColl = this.getBranchNameCollection(lConnection);
      result = SIUtil.getNameFromColl(branchColl, this.branchCode);
    }
    return result;
  }
  
  /**
   * @param branchCode branchCode を設定。
   */
  public void setBranchCode(String branchCode) {
    this.branchCode = branchCode;
  }
  
  /**
   * @return seasonMonthCbo を戻します。
   */
  public String getSeasonMonthCbo() {
    return seasonMonthCbo;
  }
  
  /**
   * @param seasonMonthCbo seasonMonthCbo を設定。
   */
  public void setSeasonMonthCbo(String seasonMonthCbo) {
    this.seasonMonthCbo = seasonMonthCbo;
  }
  
  /**
   * @return seasonYearCbo を戻します。
   */
  public String getSeasonYearCbo() {
    return seasonYearCbo;
  }
  
  /**
   * @param seasonYearCbo seasonYearCbo を設定。
   */
  public void setSeasonYearCbo(String seasonYearCbo) {
    this.seasonYearCbo = seasonYearCbo;
  }
  
  /**
   * @return print を戻します。
   */
  public boolean isPrint() {
    return print;
  }
  
  /**
   * @param print print を設定。
   */
  public void setPrint(boolean print) {
    this.print = print;
  }
  
  /**
   * @return seasonDayFromCbo を戻します。
   */
  public String getSeasonDayFromCbo() {
    return seasonDayFromCbo;
  }
  
  /**
   * @param seasonDayFromCbo seasonDayFromCbo を設定。
   */
  public void setSeasonDayFromCbo(String seasonDayFromCbo) {
    this.seasonDayFromCbo = seasonDayFromCbo;
  }
  
  /**
   * @return seasonDayToCbo を戻します。
   */
  public String getSeasonDayToCbo() {
    return seasonDayToCbo;
  }
  
  /**
   * @param seasonDayToCbo seasonDayToCbo を設定。
   */
  public void setSeasonDayToCbo(String seasonDayToCbo) {
    this.seasonDayToCbo = seasonDayToCbo;
  }
  
  /**
   * @return seasonMonthFromCbo を戻します。
   */
  public String getSeasonMonthFromCbo() {
    return seasonMonthFromCbo;
  }
  
  /**
   * @param seasonMonthFromCbo seasonMonthFromCbo を設定。
   */
  public void setSeasonMonthFromCbo(String seasonMonthFromCbo) {
    this.seasonMonthFromCbo = seasonMonthFromCbo;
  }
  
  /**
   * @return seasonMonthToCbo を戻します。
   */
  public String getSeasonMonthToCbo() {
    return seasonMonthToCbo;
  }
  
  /**
   * @param seasonMonthToCbo seasonMonthToCbo を設定。
   */
  public void setSeasonMonthToCbo(String seasonMonthToCbo) {
    this.seasonMonthToCbo = seasonMonthToCbo;
  }
  
  /**
   * @return seasonYearFromCbo を戻します。
   */
  public String getSeasonYearFromCbo() {
    return seasonYearFromCbo;
  }
  
  /**
   * @param seasonYearFromCbo seasonYearFromCbo を設定。
   */
  public void setSeasonYearFromCbo(String seasonYearFromCbo) {
    this.seasonYearFromCbo = seasonYearFromCbo;
  }
  
  public String getSeasonFromCho() {
    if (SIUtil.isNotNull(this.getSeasonYearFromCbo()) && SIUtil.isNotNull(this.getSeasonMonthFromCbo()) && SIUtil.isNotNull(this.getSeasonDayFromCbo())) {
      return this.getSeasonYearFromCbo() + "/" + this.getSeasonMonthFromCbo() + "/" + this.getSeasonDayFromCbo();
    } else {
      return "";
    }
  }
  
  /**
   * @return seasonYearToCbo を戻します。
   */
  public String getSeasonYearToCbo() {
    return seasonYearToCbo;
  }
  
  /**
   * @param seasonYearToCbo seasonYearToCbo を設定。
   */
  public void setSeasonYearToCbo(String seasonYearToCbo) {
    this.seasonYearToCbo = seasonYearToCbo;
  }
  
  public String getSeasonToCho() {
    if (SIUtil.isNotNull(this.getSeasonYearToCbo()) && SIUtil.isNotNull(this.getSeasonMonthToCbo()) && SIUtil.isNotNull(this.getSeasonDayToCbo())) {
      return this.getSeasonYearToCbo() + "/" + this.getSeasonMonthToCbo() + "/" + this.getSeasonDayToCbo();
    } else {
      return "";
    }
  }
  
  public String getSeasonFromToCho() {
    String reString = new String();
    if (SIUtil.isNotNull(this.getSeasonFromCho())) {
      reString = this.getSeasonFromCho() + "〜";
    }
    if (SIUtil.isNotNull(this.getSeasonToCho()) && SIUtil.isNotNull(this.getSeasonToCho())) {
      reString = reString + this.getSeasonToCho();
    } else if (SIUtil.isNull(this.getSeasonToCho()) && SIUtil.isNotNull(this.getSeasonToCho())) {
      reString = "〜" + this.getSeasonToCho();
    }
    return reString;
  }
  
  public String getShipmentDateFromToCho() {
    return this.getShipmentDateFromCho() + "〜" + this.getShipmentDateToCho();
  }
  
  /**
   * @return orderRoute を戻します。
   */
  public String getOrderRoute() {
    return orderRoute;
  }
  
  /**
   * @return orderRoute を戻します。
   */
  public String getHeaderOrderRoute() {
    String result = "全選択";
    if (SIUtil.isNotNull(this.getOrderRoute())) {
      for (int i = 0; i < SIFlagConf.SIFLAG_SEARCH_ORDER_ROUTE_NAME.length; i++) {
        if (SIFlagConf.SIFLAG_SEARCH_ORDER_ROUTE_NAME[i][1].equals(this.getOrderRoute())) result = SIFlagConf.SIFLAG_SEARCH_ORDER_ROUTE_NAME[i][0];
      }
    }
    return result;
  }
  
  /**
   * @param orderRoute orderRoute を設定。
   */
  public void setOrderRoute(String orderRoute) {
    this.orderRoute = orderRoute;
  }
  
  /**
   * @return orderStatusRdo を戻します。
   */
  public String getOrderStatusRdo() {
    return orderStatusRdo;
  }
  
  public String getHeaderOrderStatusRdo() {
    String result = "全選択";
    if (SIUtil.isNotNull(this.getOrderStatusRdo())) {
      result = SIFlagConf.SIFLAG_EXCEL_ORDERSTATUS_FLG_NAME[Integer.parseInt(this.getOrderStatusRdo())][0];
    }
    return result;
  }
  
  public String getHeaderSalesOrderStatusRdo() {
    String result = "全選択";
    if (SIUtil.isNotNull(this.getOrderStatusRdo())) {
      result = SIFlagConf.SIFLAG_EXCEL_SALESORDERSTATUS_FLG_NAME[Integer.parseInt(this.getOrderStatusRdo())][0];
    }
    return result;
  }
  
  /**
   * @param orderStatusRdo orderStatusRdo を設定。
   */
  public void setOrderStatusRdo(String orderStatusRdo) {
    this.orderStatusRdo = orderStatusRdo;
  }
  
  /**
   * @return chargeNameCdo を戻します。
   */
  public String getChargeNameCdo() {
    return chargeNameCdo;
  }
  
  public String getHeaderChargeNameCdo(Connection lConnection) {
    String result = "全選択";
    if (SIUtil.isNotNull(this.getChargeNameCdo())) {
      Collection chargeColl = UIChargeListCond.getSINameCollection(lConnection);
      result = SIUtil.getNameFromColl(chargeColl, this.getChargeNameCdo());
    }
    return result;
  }
  
  /**
   * @param chargeNameCdo chargeNameCdo を設定。
   */
  public void setChargeNameCdo(String chargeNameCdo) {
    this.chargeNameCdo = chargeNameCdo;
  }
  
  /**
   * @return individualCode を戻します。
   */
  public String getIndividualCode() {
    return individualCode;
  }
  
  /**
   * @return individualCode を戻します。
   */
  public String getHeaderIndividualCode() {
    if (SIUtil.isNotNull(individualCode)) {
      return individualCode;
    } else {
      return "全選択";
    }
  }
  
  /**
   * @param individualCode individualCode を設定。
   */
  public void setIndividualCode(String individualCode) {
    this.individualCode = individualCode;
  }
  
  /**
   * @return noStockFlgRdo を戻します。
   */
  public String getNoStockFlgRdo() {
    return noStockFlgRdo;
  }
  
  /**
   * @param noStockFlgRdo noStockFlgRdo を設定。
   */
  public void setNoStockFlgRdo(String noStockFlgRdo) {
    this.noStockFlgRdo = noStockFlgRdo;
  }
  
  /**
   * @return ctgryCode を戻します。
   */
  public String getCtgryCode() {
    return ctgryCode;
  }
  
  /**
   * @return ctgryCode を戻します。
   */
  public String getHeaderCtgryCode(Connection lConnection) {
    String result = "全選択";
    if (SIUtil.isNotNull(ctgryCode)) {
      try {
        result = SIBGUtil.getCtgryName(lConnection, ctgryCode);
      } catch (SIDBAccessException e) {
        e.printStackTrace();
      }
    }
    return result;
  }
  
  /**
   * @param ctgryCode ctgryCode を設定。
   */
  public void setCtgryCode(String ctgryCode) {
    this.ctgryCode = ctgryCode;
  }
  
  /**
   * @return custCode を戻します。
   */
  public String getCustCode() {
    return custCode;
  }
  
  /**
   * @return custCode を戻します。
   */
  public String getHeaderCustCode() {
    if (SIUtil.isNotNull(this.getCustCode())) {
      return custCode;
    } else {
      return "全選択";
    }
  }
  
  /**
   * @param custCode custCode を設定。
   */
  public void setCustCode(String custCode) {
    this.custCode = custCode;
  }
  
  public String getSeasonCbo() {
    return this.seasonYearCbo + this.seasonMonthCbo;
  }
  
  public String getHeaderSeasonCbo() {
    return this.seasonYearCbo + "/" + this.seasonMonthCbo;
  }
  
  public String getHeaderMarketYear() {
    return this.seasonYearCbo + "年";
  }
  
  public String getHeaderMarketYear2() {
    return SIUtil.add(this.seasonYearCbo, "1") + "年";
  }
  
  public String getSeasonFromDate() {
    return this.seasonYearCbo + "-" + this.seasonMonthCbo + "-01";
  }
  
  public String getSeasonToDate() {
    String year = this.seasonYearCbo;
    String month = this.seasonMonthCbo;
    
    if ("12".equals(month)){
      year = String.valueOf(Integer.parseInt(year) + 1);
      month = "01";
    }else{
      month = String.valueOf(Integer.parseInt(month) + 1);
      if (month.length()==1) month = "0" + month;
    }
    return year + "-" + month + "-01";
  }
  
  
  /**
   * @return payMethodNameCbo を戻します。
   */
  public String getPayMethodNameCbo() {
    return payMethodNameCbo;
  }
  
  /**
   * @return payMethodNameCbo を戻します。
   */
  public String getHeaderPayMethodNameCbo() {
    if (SIUtil.isNotNull(this.getPayMethodNameCbo())) {
      return payMethodNameCbo;
    } else {
      return "全選択";
    }
  }
  
  /**
   * @param payMethodNameCbo payMethodNameCbo を設定。
   */
  public void setPayMethodNameCbo(String payMethodNameCbo) {
    this.payMethodNameCbo = payMethodNameCbo;
  }
  
  /**
   * @return detailFlgRdo を戻します。
   */
  public String getDetailFlgRdo() {
    return detailFlgRdo;
  }
  
  /**
   * @return detailFlgRdo を戻します。
   */
  public String getHeaderDetailFlgRdo() {
    return SIFlagConf.SIFLAG_EXCEL_DETAIL_FLG_NAME[Integer.parseInt(this.getDetailFlgRdo())][0];
  }
  
  /**
   * @param detailFlgRdo detailFlgRdo を設定。
   */
  public void setDetailFlgRdo(String detailFlgRdo) {
    this.detailFlgRdo = detailFlgRdo;
  }
  
  /**
   * @return receiptFlgRdo を戻します。
   */
  public String getReceiptFlgRdo() {
    return receiptFlgRdo;
  }
  
  /**
   * @return receiptFlgRdo を戻します。
   */
  public String getHeaderReceiptFlgRdo() {
    String result = "全選択";
    if (SIUtil.isNotNull(this.getReceiptFlgRdo())) {
      result = SIFlagConf.SIFLAG_RECEIPT_PAYLIST_NAME[Integer.parseInt(this.getReceiptFlgRdo())][0];
    }
    return result;
  }
  
  /**
   * @param receiptFlgRdo receiptFlgRdo を設定。
   */
  public void setReceiptFlgRdo(String receiptFlgRdo) {
    this.receiptFlgRdo = receiptFlgRdo;
  }
  
  /**
   * @return usedNewFlg を戻します。
   */
  public String getUsedNewFlg() {
    return usedNewFlg;
  }
  
  /**
   * @return usedNewFlg を戻します。
   */
  public String getHeaderUsedNewFlg() {
    int lUsedNewFlg = 0;
    String lUsedNewName = "全選択";
    if (SIUtil.isNotNull(getUsedNewFlg())) {
      lUsedNewFlg = Integer.parseInt(getUsedNewFlg());
      lUsedNewName = SIFlagConf.SIFLAG_USED_NEW_FLG_LIST_NAME[lUsedNewFlg][0];
    }
    return lUsedNewName;
  }
  
  /**
   * @param usedNewFlg usedNewFlg を設定。
   */
  public void setUsedNewFlg(String usedNewFlg) {
    this.usedNewFlg = usedNewFlg;
  }
  
  /**
   * @return shippmentFlgRdo を戻します。
   */
  public String getShippmentFlgRdo() {
    return shippmentFlgRdo;
  }
  
  /**
   * @return shippmentFlgRdo を戻します。
   */
  public String getHeaderShippmentFlgRdo() {
    String result = "全選択";
    if (SIUtil.isNotNull(this.getShippmentFlgRdo())) {
      result = SIFlagConf.SIFLAG_SHIPPMENT_NAME[Integer.parseInt(this.getShippmentFlgRdo())][0];
    }
    return result;
  }
  
  /**
   * @param shippmentFlgRdo shippmentFlgRdo を設定。
   */
  public void setShippmentFlgRdo(String shippmentFlgRdo) {
    this.shippmentFlgRdo = shippmentFlgRdo;
  }
  
  /**
   * @return instockFlg を戻します。
   */
  public String getInstockFlg() {
    return instockFlg;
  }
  
  public String getHeaderInstockFlg() {
    String result = "全選択";
    if (SIUtil.isNotNull(instockFlg) && !instockFlg.equals("2")) {
      result = SIFlagConf.SIFLAG_INSTOCK_NAME[Integer.parseInt(instockFlg)][0];
    }
    return result;
  }
  
  /**
   * @param instockFlg instockFlg を設定。
   */
  public void setInstockFlg(String instockFlg) {
    if (SIUtil.isNull(instockFlg)) instockFlg = "2";
    this.instockFlg = instockFlg;
  }
  
  /**
   * @return individualFlg を戻します。
   */
  public String getIndividualFlg() {
    return individualFlg;
  }
  
  public String getHeaderIndividualFlg() {
    String result = "全選択";
    if (SIUtil.isNotNull(individualFlg)) {
      result = SIFlagConf.SIFLAG_EXCEL_INDIVIDUAL_FLG_NAME[Integer.parseInt(individualFlg)][0];
    }
    return result;
  }
  
  /**
   * @param individualFlg individualFlg を設定。
   */
  public void setIndividualFlg(String individualFlg) {
    this.individualFlg = individualFlg;
  }
  
  /**
   * @return detailFlg を戻します。
   */
  public boolean isDetailFlg() {
    return detailFlg;
  }
  
  /**
   * @param detailFlg detailFlg を設定。
   */
  public void setDetailFlg(boolean detailFlg) {
    this.detailFlg = detailFlg;
  }
  
  /**
   * @return inventoryFlg を戻します。
   */
  public String getInventoryFlg() {
    return inventoryFlg;
  }
  
  public String getHeaderInventoryFlg() {
    String result = "全選択";
    if (SIUtil.isNotNull(inventoryFlg)) {
      result = SIFlagConf.SIFLAG_EXCEL_INVENTORY_FLG_NAME[Integer.parseInt(inventoryFlg)][0];
    }
    return result;
  }
  
  /**
   * @param inventoryFlg inventoryFlg を設定。
   */
  public void setInventoryFlg(String inventoryFlg) {
    this.inventoryFlg = inventoryFlg;
  }
  
  public UISalesStatus getSS(){
    return SS;
  }
  
  public UIMarketingReport getMR(){
    return MR;
  }
  
  public UISegmentStatus getSE(){
    return SE;
  }
  
  public String getStockCodeCbo() {
    return stockCodeCbo;
  }
  
  public void setStockCodeCbo(String stockCode) {
    if (SIUtil.isNull(stockCode)) stockCode="";
    this.stockCodeCbo = stockCode;
  }
  
  public String[] getStoreType() {
    return storeType;
  }
  
  public String[] getShipType() {
    return shipType;
  }
  
  public void setStoreType(String[] storeType) {
    if (storeType == null) storeType = new String[0];
    this.storeType = storeType;
  }
  
  public void setShipType(String[] shipType) {
    if (shipType == null) shipType = new String[0];
    this.shipType = shipType;
  }
  
  public String getConditionSQL() {
    return this.conditionSQL;
  }
  
  public Collection getBranchNameCollection(Connection lConnection) {
    StringBuffer lSqlBuf = new StringBuffer("SELECT branchname,branchcode FROM branchtbl a ORDER BY branchcode ASC");
    Collection lResultColl = new ArrayList();
    try {
      lResultColl = SIDBUtil.getCollection(lConnection, lSqlBuf.toString(), false);
    } catch (SIDBAccessException e) {
      e.printStackTrace();
    }
    return lResultColl;
  }
  
  public int getBranchCount(Connection lConnection) {
    //EDBTG003-00 ohsugi mod start
//    StringBuffer lSqlBuf = new StringBuffer("SELECT count(branchcode) FROM branchtbl a ");
    StringBuffer lSqlBuf = new StringBuffer("SELECT MAX(substr(chargecode,1,4)::numeric) FROM chargetbl ");
    //EDBTG003-00 ohsugi mod end
    int result = 0;
    try {
      result = Integer.parseInt(SIDBUtil.getFirstData(lConnection, lSqlBuf.toString()));
    } catch (SIDBAccessException e) {
      e.printStackTrace();
    }
    return result;
  }
  
  public int getChargeCount(Connection lConnection) {
    StringBuffer lSqlBuf = new StringBuffer("SELECT Max(substr(chargecode,4,6)::numeric) as count FROM CHARGETBL a, branchtbl b ");
    lSqlBuf.append("WHERE b.branchcode = a.belongingbranchcode ");
    //EDBTG003-00 ohsugi del start
    /*
    lSqlBuf.append("GROUP BY b.branchcode,a.belongingbranchcode");
    */
    //EDBTG003-00 ohsugi del end
    int result = 0;
    try {
      result = Integer.parseInt(SIDBUtil.getFirstData(lConnection, lSqlBuf.toString()));
    } catch (SIDBAccessException e) {
      e.printStackTrace();
    }
    return result;
  }
  
  public Collection getBranchChargeNameCollection(Connection lConnection) {
    StringBuffer lSqlBuf = new StringBuffer("SELECT branchname,branchcode FROM branchtbl a ");
    lSqlBuf.append("WHERE EXISTS (SELECT * FROM CHARGETBL b ");
    lSqlBuf.append("WHERE a.branchcode = b.belongingbranchcode) ");
    lSqlBuf.append("ORDER BY branchcode ASC");
    Collection lResultColl = new ArrayList();
    try {
      lResultColl = SIDBUtil.getCollection(lConnection, lSqlBuf.toString(), false);
    } catch (SIDBAccessException e) {
      e.printStackTrace();
    }
    return lResultColl;
  }
  
  public Collection getChargeBranchNameCollection(Connection lConnection) {
    StringBuffer lSqlBuf = new StringBuffer("SELECT chargename,chargecode FROM CHARGETBL a ");
    lSqlBuf.append("WHERE EXISTS (SELECT * FROM branchtbl b ");
    lSqlBuf.append("WHERE b.branchcode = a.belongingbranchcode) ");
    lSqlBuf.append("ORDER BY chargecode ASC");
    Collection lResultColl = new ArrayList();
    try {
      lResultColl = SIDBUtil.getCollection(lConnection, lSqlBuf.toString(), false);
    } catch (SIDBAccessException e) {
      e.printStackTrace();
    }
    return lResultColl;
  }
  
  public String getInterval() {
    return interval;
  }
  
  public void setInterval(String interval) {
    if (SIUtil.isNull(interval)) interval = "";
    this.interval = interval;
  }
  
  public String getSegmentFlg() {
    return segmentFlg;
  }
  
  public void setSegmentFlg(String segmentFlg) {
    if (SIUtil.isNull(segmentFlg)) segmentFlg = "";
    this.segmentFlg = segmentFlg;
  }
}