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

import java.text.ParseException;

import javax.servlet.http.HttpServletRequest;

import jp.co.sint.basic.SIBasic;
import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIFlagConf;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.tools.SICheckDataConf;
import jp.co.sint.tools.SICheckUtil;
import jp.co.sint.tools.SICheckValid;
import jp.co.sint.tools.SICustomError;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIDateTime;
import jp.co.sint.tools.SIException;
import jp.co.sint.tools.SIUtil;
import jp.co.sint.tools.SIURLParameter;// 7.1.1 ST0236 追加

import org.apache.log4j.Category;

/**
 * @version $Id: UIIOCsv.java,v 1.0 2003/12/16 Exp $
 * @author  Jinwang Chen
 * <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>
 * Jinwang Chen   2003/12/16 11:00:31  Original
 */
public class UIIOCsv extends SIBasic {
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  private String csvTblNameSel = "";
  
  private String salesYearFrom = "";
  private String salesYearTo = "";
  private String salesMonthFrom = "";
  private String salesMonthTo = "";
  private String salesDayFrom = "";
  private String salesDayTo = "";
  private String execBase = "0";
  private String motherCtgry = "";
  private String individualCode = "";
  private String makerName = "";
  private String disableFlg = "";
  
  private String cmdtyName = "";
  private String stockCode = "";
  
  private String execSpan = "";
  private String makerCode = "";
  private String branchCode = "";
  private String amountFlg = "";
  private String stockName = "";
  
  private String orderCode = "";
  private String hacchuCode = "";
  private String checkFlg = "0";
  private String deliveryType = "";
  private String custCode = "";
  private String memoStatus = "all";
  private String opinionFlg = "0";
  
  private String lastUpdate = "1";
  private String[] usednewFlgC = new String[SIFlagConf.SIFLAG_USED_NEW_FLG_LIST_NAME.length];
  private String[] disableFlgC = new String[SIFlagConf.SIFLAG_DISABLE_FLG_NAME.length];
  
  private String[] salesSituationC = new String[SIFlagConf.SIFLAG_SALES_SITUATION_DISP_NAME.length];
  
  /** コンストラクタ **/
  public UIIOCsv() {}
  
  public UIIOCsv(HttpServletRequest lRequest,SIURLParameter lUrlParam) {
    init(lRequest,lUrlParam);
  }
  
  public void setCsvTblNameSel(String lCsvTblNameSel) {
    if (SIUtil.isNull(lCsvTblNameSel))
      lCsvTblNameSel = "";
    this.csvTblNameSel = SIUtil.changeTo(lCsvTblNameSel.trim(), this.encode);
  }
  
  public String getCsvTblNameSel() {
    return this.csvTblNameSel;
  }
  
  /**
   * <b>init</b> 入力したデータを基づいて、このbeansを設定します。
   * 
   * @param lUrlParam
   * @return true:不正なデータがある false:ない
   * @throws なし
   */
  // 7.1.1 ST0236 修正 ここから
  public void init(HttpServletRequest lRequest, SIURLParameter lUrlParam) {
    this.setEncode(SIConfig.SIENCODE_SHIFT_JIS);
    this.setCsvTblNameSel((String) lUrlParam.getParam("csvTblNameSel"));// 選択するテーブル名
    if (SIConfig.SITABLE_SALESDATA_NAME.equals(this.getCsvTblNameSel())){
      this.setSalesYearFrom((String) lUrlParam.getParam("salesYearFrom"));
      this.setSalesYearTo((String) lUrlParam.getParam("salesYearTo"));
      this.setSalesMonthFrom((String) lUrlParam.getParam("salesMonthFrom"));
      this.setSalesMonthTo((String) lUrlParam.getParam("salesMonthTo"));
      this.setExecBase((String) lUrlParam.getParam("execBase"));
      this.setMotherCtgry((String) lUrlParam.getParam("motherCtgry"));
      this.setIndividualCode((String) lUrlParam.getParam("individualCode"));
      this.setMakerName((String) lUrlParam.getParam("makerName"));
      this.setDisableFlg((String) lUrlParam.getParam("disableFlg"));
    } else if(SIConfig.SIVIEW_LACK_LIST_NAME.equals(this.getCsvTblNameSel())) {
      this.setBranchCode((String) lUrlParam.getParam("branchCode"));
      this.setAmountFlg((String) lUrlParam.getParam("amountFlg"));
      this.setMakerCode((String) lUrlParam.getParam("makerCode"));
      this.setMakerName((String) lUrlParam.getParam("makerName"));
      this.setStockCode((String) lUrlParam.getParam("stockCode"));
      this.setStockName((String) lUrlParam.getParam("stockName"));
      this.setMotherCtgry((String) lUrlParam.getParam("motherCtgry"));
    } else if(SIConfig.SIVIEW_ORDER_ROUTE_NAME.equals(this.getCsvTblNameSel())
           || SIConfig.SIVIEW_URIAGEBASE_LIST_NAME.equals(this.getCsvTblNameSel())
           || SIConfig.SITABLE_POINTRECORD_NAME.equals(this.getCsvTblNameSel())) {
      this.setSalesYearFrom((String) lUrlParam.getParam("salesYearFrom"));
      this.setSalesYearTo((String) lUrlParam.getParam("salesYearTo"));
      this.setSalesMonthFrom((String) lUrlParam.getParam("salesMonthFrom"));
      this.setSalesMonthTo((String) lUrlParam.getParam("salesMonthTo"));
    } else if (SIConfig.SITABLE_CMDTYDATA_NAME.equals(this.getCsvTblNameSel())){
      this.setCmdtyName((String) lUrlParam.getParam("cmdtyName"));
      this.setIndividualCode((String) lUrlParam.getParam("individualCode"));
      this.setMakerCode((String) lUrlParam.getParam("makerCode"));
      this.setStockCode((String) lUrlParam.getParam("stockCode"));
      this.setAmountFlg((String) lUrlParam.getParam("amountFlg"));
      this.setDisableFlg((String) lUrlParam.getParam("disableFlg"));
      this.setBranchCode((String) lUrlParam.getParam("branchCode"));
      this.setSalesSituationC(lRequest.getParameterValues("salesSituationC"));
    } else if (SIConfig.SIVIEW_FLAGUPDATELOG_LIST_NAME.equals(this.getCsvTblNameSel())){
      this.setSalesYearFrom((String) lUrlParam.getParam("salesYearFrom"));
      this.setSalesYearTo((String) lUrlParam.getParam("salesYearTo"));
      this.setSalesMonthFrom((String) lUrlParam.getParam("salesMonthFrom"));
      this.setSalesMonthTo((String) lUrlParam.getParam("salesMonthTo"));
      this.setIndividualCode((String) lUrlParam.getParam("individualCode"));
    } else if (SIConfig.SITABLE_OPINION_NAME.equals(this.getCsvTblNameSel())) {
      this.setSalesYearFrom((String) lUrlParam.getParam("salesYearFrom"));
      this.setSalesYearTo((String) lUrlParam.getParam("salesYearTo"));
      this.setSalesMonthFrom((String) lUrlParam.getParam("salesMonthFrom"));
      this.setSalesMonthTo((String) lUrlParam.getParam("salesMonthTo"));
    }else if (SIConfig.SIVIEW_WAREHOUSE_CMDTY_LIST_NAME.equals(this.getCsvTblNameSel())) {
      this.setIndividualCode((String) lUrlParam.getParam("individualCode"));
      this.setUsednewFlgC(lRequest.getParameterValues("usednewFlgC"));
      this.setDisableFlgC(lRequest.getParameterValues("disableFlgC"));
      this.setLastUpdate((String) lUrlParam.getParam("lastUpdate"));
    }else if (SIConfig.SIVIEW_WAREHOUSE_STORE_LIST_NAME.equals(this.getCsvTblNameSel())) {
      this.setSalesYearFrom((String) lUrlParam.getParam("salesYearFrom"));
      this.setSalesYearTo((String) lUrlParam.getParam("salesYearTo"));
      this.setSalesMonthFrom((String) lUrlParam.getParam("salesMonthFrom"));
      this.setSalesMonthTo((String) lUrlParam.getParam("salesMonthTo"));
      this.setSalesDayFrom((String) lUrlParam.getParam("salesDayFrom"));
      this.setSalesDayTo((String) lUrlParam.getParam("salesDayTo"));
      this.setHacchuCode((String) lUrlParam.getParam("hacchuCode"));
      this.setBranchCode((String) lUrlParam.getParam("branchCode"));
    }else if (SIConfig.SIVIEW_WAREHOUSE_SHIP_LIST_NAME.equals(this.getCsvTblNameSel())) {
      this.setSalesYearFrom((String) lUrlParam.getParam("salesYearFrom"));
      this.setSalesYearTo((String) lUrlParam.getParam("salesYearTo"));
      this.setSalesMonthFrom((String) lUrlParam.getParam("salesMonthFrom"));
      this.setSalesMonthTo((String) lUrlParam.getParam("salesMonthTo"));
      this.setSalesDayFrom((String) lUrlParam.getParam("salesDayFrom"));
      this.setSalesDayTo((String) lUrlParam.getParam("salesDayTo"));
      this.setOrderCode((String) lUrlParam.getParam("orderCode"));
      this.setBranchCode((String) lUrlParam.getParam("branchCode"));
      this.setCheckFlg((String) lUrlParam.getParam("checkFlg"));
    }else if (SIConfig.SIVIEW_BRANCH_DEADSTOCK_LIST_NAME.equals(this.getCsvTblNameSel())) {
      this.setBranchCode((String) lUrlParam.getParam("branchCode"));
      this.setIndividualCode((String) lUrlParam.getParam("individualCode"));
      this.setExecSpan((String) lUrlParam.getParam("execSpan"));
      this.setDeliveryType((String) lUrlParam.getParam("deliveryType"));
      this.setMakerCode((String) lUrlParam.getParam("makerCode"));
      this.setUsednewFlgC(lRequest.getParameterValues("usednewFlgC"));
      this.setDisableFlgC(lRequest.getParameterValues("disableFlgC"));
    }else if (SIConfig.SIVIEW_REPEAT_SALES_LIST_NAME.equals(this.getCsvTblNameSel())) {
      this.setIndividualCode((String) lUrlParam.getParam("individualCode"));
      this.setMakerCode((String) lUrlParam.getParam("makerCode"));
      this.setMakerName((String) lUrlParam.getParam("makerName"));
      this.setStockName((String) lUrlParam.getParam("stockName"));
    }else if (SIConfig.SIVIEW_EG_POINT_LIST_NAME.equals(this.getCsvTblNameSel())) {
      this.setSalesYearFrom((String) lUrlParam.getParam("salesYearFrom"));
      this.setSalesYearTo((String) lUrlParam.getParam("salesYearTo"));
      this.setSalesMonthFrom((String) lUrlParam.getParam("salesMonthFrom"));
      this.setSalesMonthTo((String) lUrlParam.getParam("salesMonthTo"));
    }else if (SIConfig.SIVIEW_EYELASH_SALES_LIST_NAME.equals(this.getCsvTblNameSel())) {
      this.setSalesYearFrom((String) lUrlParam.getParam("salesYearFrom"));
      this.setSalesYearTo((String) lUrlParam.getParam("salesYearTo"));
      this.setSalesMonthFrom((String) lUrlParam.getParam("salesMonthFrom"));
      this.setSalesMonthTo((String) lUrlParam.getParam("salesMonthTo"));
    }else if (SIConfig.SITABLE_CUSTSALES_NAME.equals(this.getCsvTblNameSel())) {
      this.setSalesYearFrom((String) lUrlParam.getParam("salesYearFrom"));
      this.setSalesYearTo((String) lUrlParam.getParam("salesYearTo"));
      this.setSalesMonthFrom((String) lUrlParam.getParam("salesMonthFrom"));
      this.setSalesMonthTo((String) lUrlParam.getParam("salesMonthTo"));
      this.setCustCode((String) lUrlParam.getParam("custCode"));
    }else if (SIConfig.SITABLE_PURCHASECOMMENT_NAME.equals(this.getCsvTblNameSel())){
      this.setSalesYearFrom((String) lUrlParam.getParam("salesYearFrom"));
      this.setSalesYearTo((String) lUrlParam.getParam("salesYearTo"));
      this.setSalesMonthFrom((String) lUrlParam.getParam("salesMonthFrom"));
      this.setSalesMonthTo((String) lUrlParam.getParam("salesMonthTo"));
      this.setSalesDayFrom((String) lUrlParam.getParam("salesDayFrom"));
      this.setSalesDayTo((String) lUrlParam.getParam("salesDayTo"));
      this.setMemoStatus((String) lUrlParam.getParam("memoStatus"));
      this.setBranchCode((String) lUrlParam.getParam("branchCode"));
      this.setCustCode((String) lUrlParam.getParam("custCode"));
      this.setOpinionFlg((String) lUrlParam.getParam("opinionFlg"));
    }
  }
  
  public void reset() {
    SIDateTime lDateTime = new SIDateTime();
    String Year = Integer.toString(lDateTime.getYear());
    String Month = Integer.toString(lDateTime.getMonth());
    String Day = Integer.toString(lDateTime.getDay());
    if (Month.length() == 1) Month = "0" + Month;
    if (Day.length() == 1) Day = "0" + Day;
    if (SIConfig.SITABLE_CMDTYDATA_NAME.equals(this.getCsvTblNameSel())) {
      String[] salesSituation = new String[SIFlagConf.SIFLAG_SALES_SITUATION_DISP_NAME.length];
      for (int i=0;i<SIFlagConf.SIFLAG_SALES_SITUATION_DISP_NAME.length;i++){
        salesSituation[i] = SIFlagConf.SIFLAG_SALES_SITUATION_DISP_NAME[i][1];
      }
      this.setSalesSituationC(salesSituation);
    }else if (SIConfig.SIVIEW_WAREHOUSE_CMDTY_LIST_NAME.equals(this.getCsvTblNameSel())) {
      String[] usedNew = new String[SIFlagConf.SIFLAG_USED_NEW_FLG_LIST_NAME.length];
      for (int i=0;i<SIFlagConf.SIFLAG_USED_NEW_FLG_LIST_NAME.length;i++){
        usedNew[i] = SIFlagConf.SIFLAG_USED_NEW_FLG_LIST_NAME[i][1];
      }
      String[] disable = new String[SIFlagConf.SIFLAG_DISABLE_FLG_NAME.length];
      for (int j=0;j<SIFlagConf.SIFLAG_DISABLE_FLG_NAME.length;j++){
        disable[j] = SIFlagConf.SIFLAG_DISABLE_FLG_NAME[j][1];
      }
      this.setUsednewFlgC(usedNew);
      this.setDisableFlgC(disable);
      this.setLastUpdate("1");
    }else if (SIConfig.SIVIEW_WAREHOUSE_STORE_LIST_NAME.equals(this.getCsvTblNameSel())) {
      this.setSalesYearFrom(Year);
      this.setSalesYearTo(Year);
      this.setSalesMonthFrom(Month);
      this.setSalesMonthTo(Month);
      this.setSalesDayFrom(Day);
      this.setSalesDayTo(Day);
      this.setBranchCode("18");
      this.setHacchuCode("");
    }else if (SIConfig.SIVIEW_WAREHOUSE_SHIP_LIST_NAME.equals(this.getCsvTblNameSel())) {
      this.setSalesYearFrom(Year);
      this.setSalesYearTo(Year);
      this.setSalesMonthFrom(Month);
      this.setSalesMonthTo(Month);
      this.setSalesDayFrom(Day);
      this.setSalesDayTo(Day);
      this.setBranchCode("18");
      this.setCheckFlg("0");
      this.setOrderCode("");
    }else if (SIConfig.SIVIEW_BRANCH_DEADSTOCK_LIST_NAME.equals(this.getCsvTblNameSel())) {
      this.setBranchCode("");
      this.setIndividualCode("");
      this.setExecSpan("");
      this.setDeliveryType("");
      this.setMakerCode("");
      String[] usedNew = new String[SIFlagConf.SIFLAG_USED_NEW_FLG_LIST_NAME.length];
      for (int i=0;i<SIFlagConf.SIFLAG_USED_NEW_FLG_LIST_NAME.length;i++){
        usedNew[i] = SIFlagConf.SIFLAG_USED_NEW_FLG_LIST_NAME[i][1];
      }
      String[] disable = new String[SIFlagConf.SIFLAG_DISABLE_FLG_NAME.length];
      for (int j=0;j<SIFlagConf.SIFLAG_DISABLE_FLG_NAME.length;j++){
        disable[j] = SIFlagConf.SIFLAG_DISABLE_FLG_NAME[j][1];
      }
      this.setUsednewFlgC(usedNew);
      this.setDisableFlgC(disable);
    }else if (SIConfig.SITABLE_PURCHASECOMMENT_NAME.equals(this.getCsvTblNameSel())) {
      this.setSalesYearFrom(Year);
      this.setSalesYearTo(Year);
      this.setSalesMonthFrom(Month);
      this.setSalesMonthTo(Month);
      this.setSalesDayFrom(Day);
      this.setSalesDayTo(Day);
      this.setMemoStatus("all");
      this.setBranchCode("");
      this.setCustCode("");
      this.setOpinionFlg("0");
    }else{
      this.setSalesYearFrom("");
      this.setSalesYearTo("");
      this.setSalesMonthFrom("");
      this.setSalesMonthTo("");
      this.setSalesDayFrom("");
      this.setSalesDayTo("");
      this.setExecBase("0");
      this.setExecSpan("");
      this.setOrderCode("");
      this.setHacchuCode("");
      this.setIndividualCode("");
      this.setCmdtyName("");
      this.setMakerCode("");
      this.setStockCode("");
      this.setMakerName("");
      this.setAmountFlg("1");
      this.setDisableFlg("0");
      this.setBranchCode("");
      this.setMotherCtgry("");
      this.setUsednewFlgC(null);
      this.setDisableFlgC(null);
      this.setLastUpdate("1");
      this.setDeliveryType("");
      this.setCustCode("");
    }
  }
  
  // 7.1.1 ST0236 修正 ここまで
  
  public boolean validate(HttpServletRequest lRequest) {
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    SICustomErrors errors = new SICustomErrors();
    
    SICheckValid.checkValid(errors, "出力テーブル名", this.getCsvTblNameSel(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
    
    if (SIConfig.SITABLE_SALESDATA_NAME.equals(this.getCsvTblNameSel())){
      SICheckValid.checkValid(errors, "カテゴリ", this.getMotherCtgry(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "在庫コード", this.getIndividualCode(), SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE);
      SICheckValid.checkValid(errors, "ブランド", this.getMotherCtgry(), SICheckDataConf.SICHECK_DATA_BYTE_LEN_WITHIN_TYPE,128);
      SICheckValid.checkValid(errors, "対象期間[月]From", this.getSalesMonthFrom(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[年]From", this.getSalesYearFrom(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[月]To", this.getSalesMonthTo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[年]To", this.getSalesYearTo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      if (errors.isEmpty()) {
        SICheckValid.checkValid(errors, "対象期間From", this.getSalesFrom(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
        SICheckValid.checkValid(errors, "対象期間To", this.getSalesTo(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
      }
      try {
        if (errors.isEmpty() && !SICheckUtil.dateEqual(this.getSalesFrom(), this.getSalesTo())) {
          SICheckValid.checkValid(errors, "対象期間From", "対象期間To", this.getSalesFrom(), this.getSalesTo(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
          SICheckValid.checkValid(errors, "対象期間To", "対象期間From＋2ヶ月", this.getSalesTo(), this.getSalesFrom2(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
          //errors.addError(new SICustomError("manager.message.freeword","対象期間は1ヶ月のみ選択可能です"));
        }
      } catch (ParseException e) {
        e.printStackTrace();
      } catch (SIException e) {
        e.printStackTrace();
      }
    } else if (SIConfig.SIVIEW_ORDER_ROUTE_NAME.equals(this.getCsvTblNameSel())) {
      SICheckValid.checkValid(errors, "対象期間[月]From", this.getSalesMonthFrom(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[年]From", this.getSalesYearFrom(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[月]To", this.getSalesMonthTo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[年]To", this.getSalesYearTo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      if (errors.isEmpty()) {
        SICheckValid.checkValid(errors, "対象期間From", this.getSalesFrom(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
        SICheckValid.checkValid(errors, "対象期間To", this.getSalesTo(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
      }
      try {
        if (errors.isEmpty() && !SICheckUtil.dateEqual(this.getSalesFrom(), this.getSalesTo())) {
          SICheckValid.checkValid(errors, "対象期間From", "対象期間To", this.getSalesFrom(), this.getSalesTo(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
          SICheckValid.checkValid(errors, "対象期間To", "対象期間From＋2ヶ月", this.getSalesTo(), this.getSalesFrom2(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
          //errors.addError(new SICustomError("manager.message.freeword","対象期間は1ヶ月のみ選択可能です"));
        }
      } catch (ParseException e) {
        e.printStackTrace();
      } catch (SIException e) {
        e.printStackTrace();
      }
    } else if (SIConfig.SIVIEW_URIAGEBASE_LIST_NAME.equals(this.getCsvTblNameSel())) {
      SICheckValid.checkValid(errors, "対象期間[月]From", this.getSalesMonthFrom(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[年]From", this.getSalesYearFrom(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[月]To", this.getSalesMonthTo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[年]To", this.getSalesYearTo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      if (errors.isEmpty()) {
        SICheckValid.checkValid(errors, "対象期間From", this.getSalesFrom(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
        SICheckValid.checkValid(errors, "対象期間To", this.getSalesTo(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
      }
      try {
        if (errors.isEmpty() && !SICheckUtil.dateEqual(this.getSalesFrom(), this.getSalesTo())) {
          SICheckValid.checkValid(errors, "対象期間From", "対象期間To", this.getSalesFrom(), this.getSalesTo(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
          SICheckValid.checkValid(errors, "対象期間To", "対象期間From＋2ヶ月", this.getSalesTo(), this.getSalesFrom2(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
          //errors.addError(new SICustomError("manager.message.freeword","対象期間は1ヶ月のみ選択可能です"));
        }
      } catch (ParseException e) {
        e.printStackTrace();
      } catch (SIException e) {
        e.printStackTrace();
      }
    } else if (SIConfig.SITABLE_POINTRECORD_NAME.equals(this.getCsvTblNameSel())) {
      SICheckValid.checkValid(errors, "対象期間[月]From", this.getSalesMonthFrom(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[年]From", this.getSalesYearFrom(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[月]To", this.getSalesMonthTo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[年]To", this.getSalesYearTo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      if (errors.isEmpty()) {
        SICheckValid.checkValid(errors, "対象期間From", this.getSalesFrom(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
        SICheckValid.checkValid(errors, "対象期間To", this.getSalesTo(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
      }
      try {
        if (errors.isEmpty() && !SICheckUtil.dateEqual(this.getSalesFrom(), this.getSalesTo())) {
          SICheckValid.checkValid(errors, "対象期間From", "対象期間To", this.getSalesFrom(), this.getSalesTo(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
          SICheckValid.checkValid(errors, "対象期間To", "対象期間From＋2ヶ月", this.getSalesTo(), this.getSalesFrom2(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
          //errors.addError(new SICustomError("manager.message.freeword","対象期間は1ヶ月のみ選択可能です"));
        }
      } catch (ParseException e) {
        e.printStackTrace();
      } catch (SIException e) {
        e.printStackTrace();
      }
    } else if (SIConfig.SITABLE_CMDTYDATA_NAME.equals(this.getCsvTblNameSel())) {
      SICheckValid.checkValid(errors, "在庫コード", this.getIndividualCode(), SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE);
      SICheckValid.checkValid(errors, "商品名", this.getIndividualCode(), SICheckDataConf.SICHECK_DATA_CMDTYNAME_TYPE);
    } else if (SIConfig.SIVIEW_FLAGUPDATELOG_LIST_NAME.equals(this.getCsvTblNameSel())) {
      SICheckValid.checkValid(errors, "在庫コード", this.getIndividualCode(), SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE);
      SICheckValid.checkValid(errors, "対象期間[月]From", this.getSalesMonthFrom(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[年]From", this.getSalesYearFrom(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[月]To", this.getSalesMonthTo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[年]To", this.getSalesYearTo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      if (errors.isEmpty()) {
        SICheckValid.checkValid(errors, "対象期間From", this.getSalesFrom(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
        SICheckValid.checkValid(errors, "対象期間To", this.getSalesTo(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
      }
      try {
        if (errors.isEmpty() && !SICheckUtil.dateEqual(this.getSalesFrom(), this.getSalesTo())) {
          SICheckValid.checkValid(errors, "対象期間From", "対象期間To", this.getSalesFrom(), this.getSalesTo(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
        }
      } catch (ParseException e) {
        e.printStackTrace();
      } catch (SIException e) {
        e.printStackTrace();
      }
    } else if (SIConfig.SITABLE_OPINION_NAME.equals(this.getCsvTblNameSel())) {
      SICheckValid.checkValid(errors, "対象期間[月]From", this.getSalesMonthFrom(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[年]From", this.getSalesYearFrom(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[月]To", this.getSalesMonthTo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[年]To", this.getSalesYearTo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      if (errors.isEmpty()) {
        SICheckValid.checkValid(errors, "対象期間From", this.getSalesFrom(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
        SICheckValid.checkValid(errors, "対象期間To", this.getSalesTo(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
      }
      try {
        if (errors.isEmpty() && !SICheckUtil.dateEqual(this.getSalesFrom(), this.getSalesTo())) {
          SICheckValid.checkValid(errors, "対象期間From", "対象期間To", this.getSalesFrom(), this.getSalesTo(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
        }
      } catch (ParseException e) {
        e.printStackTrace();
      } catch (SIException e) {
        e.printStackTrace();
      }
    } else if (SIConfig.SIVIEW_WAREHOUSE_CMDTY_LIST_NAME.equals(this.getCsvTblNameSel())) {
      SICheckValid.checkValid(errors, "在庫コード", this.getIndividualCode(), SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE);
      SICheckValid.checkValid(errors, "最終更新日", this.getLastUpdate(), SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
    } else if (SIConfig.SIVIEW_WAREHOUSE_STORE_LIST_NAME.equals(this.getCsvTblNameSel())) {
      SICheckValid.checkValid(errors, "対象期間From", this.getSalesDateFrom(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
      SICheckValid.checkValid(errors, "対象期間To", this.getSalesDateTo(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
      try {
        if (errors.isEmpty() && !SICheckUtil.dateEqual(this.getSalesDateFrom(), this.getSalesDateTo())) {
          SICheckValid.checkValid(errors, "対象期間From", "対象期間To", this.getSalesDateFrom(), this.getSalesDateTo(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
        }
      } catch (ParseException e) {
        e.printStackTrace();
      } catch (SIException e) {
        e.printStackTrace();
      }
    } else if (SIConfig.SIVIEW_WAREHOUSE_SHIP_LIST_NAME.equals(this.getCsvTblNameSel())) {
      SICheckValid.checkValid(errors, "対象期間From", this.getSalesDateFrom(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
      SICheckValid.checkValid(errors, "対象期間To", this.getSalesDateTo(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
      try {
        if (errors.isEmpty() && !SICheckUtil.dateEqual(this.getSalesDateFrom(), this.getSalesDateTo())) {
          SICheckValid.checkValid(errors, "対象期間From", "対象期間To", this.getSalesDateFrom(), this.getSalesDateTo(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
        }
      } catch (ParseException e) {
        e.printStackTrace();
      } catch (SIException e) {
        e.printStackTrace();
      }
    } else if (SIConfig.SIVIEW_LACK_LIST_NAME.equals(this.getCsvTblNameSel())) {
      SICheckValid.checkValid(errors, "対象支店", this.getBranchCode(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
    }else if (SIConfig.SIVIEW_BRANCH_DEADSTOCK_LIST_NAME.equals(this.getCsvTblNameSel())) {
      SICheckValid.checkValid(errors, "対象支店", this.getBranchCode(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "在庫コード", this.getIndividualCode(), SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE);
    }else if (SIConfig.SIVIEW_REPEAT_SALES_LIST_NAME.equals(this.getCsvTblNameSel())) {
      SICheckValid.checkValid(errors, "在庫コード", this.getIndividualCode(), SICheckDataConf.SICHECK_DATA_ALPHA_DIGIT_TYPE);
      if (SIUtil.isNull(this.getIndividualCode())&&SIUtil.isNull(this.getMakerCode())&&SIUtil.isNull(this.getMakerName())&&SIUtil.isNull(this.getStockName())) {
        errors.addError(new SICustomError("manager.message.freeword","絞り込み条件を1つ以上設定して下さい"));
      }
    } else if (SIConfig.SIVIEW_EG_POINT_LIST_NAME.equals(this.getCsvTblNameSel())) {
      SICheckValid.checkValid(errors, "対象期間[月]From", this.getSalesMonthFrom(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[年]From", this.getSalesYearFrom(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[月]To", this.getSalesMonthTo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[年]To", this.getSalesYearTo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      if (errors.isEmpty()) {
        SICheckValid.checkValid(errors, "対象期間From", this.getSalesFrom(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
        SICheckValid.checkValid(errors, "対象期間To", this.getSalesTo(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
      }
      try {
        if (errors.isEmpty() && !SICheckUtil.dateEqual(this.getSalesFrom(), this.getSalesTo())) {
          SICheckValid.checkValid(errors, "対象期間From", "対象期間To", this.getSalesFrom(), this.getSalesTo(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
          SICheckValid.checkValid(errors, "対象期間To", "対象期間From＋2ヶ月", this.getSalesTo(), this.getSalesFrom2(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
          //errors.addError(new SICustomError("manager.message.freeword","対象期間は1ヶ月のみ選択可能です"));
        }
      } catch (ParseException e) {
        e.printStackTrace();
      } catch (SIException e) {
        e.printStackTrace();
      }
    } else if (SIConfig.SIVIEW_EYELASH_SALES_LIST_NAME.equals(this.getCsvTblNameSel())) {
      SICheckValid.checkValid(errors, "対象期間[月]From", this.getSalesMonthFrom(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[年]From", this.getSalesYearFrom(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[月]To", this.getSalesMonthTo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[年]To", this.getSalesYearTo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      if (errors.isEmpty()) {
        SICheckValid.checkValid(errors, "対象期間From", this.getSalesFrom(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
        SICheckValid.checkValid(errors, "対象期間To", this.getSalesTo(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
      }
      try {
        if (errors.isEmpty() && !SICheckUtil.dateEqual(this.getSalesFrom(), this.getSalesTo())) {
          SICheckValid.checkValid(errors, "対象期間From", "対象期間To", this.getSalesFrom(), this.getSalesTo(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
          SICheckValid.checkValid(errors, "対象期間To", "対象期間From＋2ヶ月", this.getSalesTo(), this.getSalesFrom2(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
          //errors.addError(new SICustomError("manager.message.freeword","対象期間は1ヶ月のみ選択可能です"));
        }
      } catch (ParseException e) {
        e.printStackTrace();
      } catch (SIException e) {
        e.printStackTrace();
      }
    } else if (SIConfig.SITABLE_CUSTSALES_NAME.equals(this.getCsvTblNameSel())) {
      SICheckValid.checkValid(errors, "顧客コード", this.getCustCode(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "顧客コード", this.getCustCode(), SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
      SICheckValid.checkValid(errors, "対象期間[月]From", this.getSalesMonthFrom(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[年]From", this.getSalesYearFrom(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[月]To", this.getSalesMonthTo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      SICheckValid.checkValid(errors, "対象期間[年]To", this.getSalesYearTo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
      if (errors.isEmpty()) {
        SICheckValid.checkValid(errors, "対象期間From", this.getSalesFrom(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
        SICheckValid.checkValid(errors, "対象期間To", this.getSalesTo(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
      }
      try {
        if (errors.isEmpty() && !SICheckUtil.dateEqual(this.getSalesFrom(), this.getSalesTo())) {
          SICheckValid.checkValid(errors, "対象期間From", "対象期間To", this.getSalesFrom(), this.getSalesTo(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
          SICheckValid.checkValid(errors, "対象期間To", "対象期間From＋2ヶ月", this.getSalesTo(), this.getSalesFrom2(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
        }
      } catch (ParseException e) {
        e.printStackTrace();
      } catch (SIException e) {
        e.printStackTrace();
      }
    } else if (SIConfig.SITABLE_PURCHASECOMMENT_NAME.equals(this.getCsvTblNameSel())) {
      SICheckValid.checkValid(errors, "対象期間From", this.getSalesDateFrom(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
      SICheckValid.checkValid(errors, "対象期間To", this.getSalesDateTo(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
      try {
        if (errors.isEmpty() && !SICheckUtil.dateEqual(this.getSalesDateFrom(), this.getSalesDateTo())) {
          SICheckValid.checkValid(errors, "対象期間From", "対象期間To", this.getSalesDateFrom(), this.getSalesDateTo(), 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())
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
    return errors.isEmpty();
  }
  
  public String getSalesSql(){
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT ct.ctgryname,cm.cmdtycode,cm.individualcode,cm.cmdtyname,cl.colorname,mk.makername ");
    sql.append(",coalesce(list1.amount,0)-coalesce(list2.amount,0) AS amount ");
    sql.append(",coalesce(list1.price,0)-coalesce(list2.price,0) AS price ");
    sql.append(",(coalesce(list1.price,0)-coalesce(list2.price,0)) - (coalesce(list1.cost,0)-coalesce(list2.cost,0)) AS gain ");
    sql.append(",CASE WHEN coalesce(list1.price,0)-coalesce(list2.price,0) =0 THEN '-' ");
    sql.append("ELSE round(((coalesce(list1.price,0)-coalesce(list2.price,0)) - (coalesce(list1.cost,0)-coalesce(list2.cost,0))) ");
    sql.append(" / (coalesce(list1.price,0)-coalesce(list2.price,0)),4)*100 || '%' END AS gainrate ");
    sql.append("FROM ctgrymtbl ct,colortbl cl,makertbl mk,cmdtyunittbl cm ");
    sql.append("LEFT OUTER JOIN ");
    
    if ("0".equals(execBase)) {//出荷ベース
      sql.append("(SELECT a.cmdtycode,a.individualcode,sum(a.amount) AS amount,sum(a.amount*trunc(CASE c.consigngoodsflg WHEN 1 THEN a.consigngoodsfee ELSE a.price END / (1 + (b.taxrate / 100)))) AS price ");
      sql.append(",sum(a.amount*trunc((a.purchaseprice+a.processingexpence) / (1 + (b.taxrate / 100)))) AS cost ");
      sql.append("FROM orderdetaillatestvw a,orderlatestvw b,individualtbl c WHERE a.ordercode=b.ordercode AND a.individualcode=c.individualcode AND b.status IN ('1','2') ");
      sql.append("AND a.shippmentdate>=").append(SIDBUtil.SQL2Str(getSalesFrom()," "));
      sql.append("AND a.shippmentdate<").append(SIDBUtil.SQL2Str(getSalesTo2()," "));
      sql.append("GROUP BY a.cmdtycode,a.individualcode) list1 ");
    } else {//受注ベース
      sql.append("(SELECT a.cmdtycode,a.individualcode,sum(a.amount) AS amount,sum(a.amount*trunc(CASE c.consigngoodsflg WHEN 1 THEN a.consigngoodsfee ELSE a.price END / (1 + (b.taxrate / 100)))) AS price ");
      sql.append(",sum(a.amount*trunc((a.purchaseprice+a.processingexpence) / (1 + (b.taxrate / 100)))) AS cost ");
      sql.append("FROM orderdetaillatestvw a,orderlatestvw b,individualtbl c WHERE a.ordercode=b.ordercode AND a.individualcode=c.individualcode AND b.status IN ('1','2') ");
      sql.append("AND b.initdatetime>=").append(SIDBUtil.SQL2Str(getSalesFrom()," "));
      sql.append("AND b.initdatetime<").append(SIDBUtil.SQL2Str(getSalesTo2()," "));
      sql.append("GROUP BY a.cmdtycode,a.individualcode) list1 ");
    }
    sql.append("ON cm.cmdtycode=list1.cmdtycode AND cm.individualcode=list1.individualcode ");
    sql.append("LEFT OUTER JOIN ");
    sql.append("(SELECT a.cmdtycode,a.individualcode,sum(a.amount) AS amount,sum(a.amount*trunc(CASE c.consigngoodsflg WHEN 1 THEN a.consigngoodsfee ELSE a.price END / (1 + (b.taxrate / 100)))) AS price ");
    sql.append(",sum(a.amount*trunc((a.purchaseprice+a.processingexpence) / (1 + (b.taxrate / 100)))) AS cost ");
    sql.append("FROM orderdetaillatestvw a,orderlatestvw b,individualtbl c WHERE a.ordercode=b.ordercode AND a.individualcode=c.individualcode AND b.status=2 ");
    sql.append("AND b.updatedatetime>=").append(SIDBUtil.SQL2Str(getSalesFrom()," "));
    sql.append("AND b.updatedatetime<").append(SIDBUtil.SQL2Str(getSalesTo2()," "));
    sql.append("GROUP BY a.cmdtycode,a.individualcode) list2 ");
    sql.append("ON cm.cmdtycode=list2.cmdtycode AND cm.individualcode=list2.individualcode ");
    sql.append("WHERE CASE WHEN list1.individualcode IS NULL AND list2.individualcode IS NULL THEN false ELSE true END ");
    sql.append("AND substr(cm.individualcode,0,2)=").append(SIDBUtil.SQL2Str(getCtgryHead()," "));
    sql.append("AND substr(cm.individualcode,0,3)=ct.ctgrycode ");
    sql.append("AND cm.colorcode=cl.colorcode ");
    sql.append("AND cm.makercode=mk.makercode ");
    if (SIUtil.isNotNull(individualCode)) {
      sql.append("AND cm.individualcode LIKE ").append(SIDBUtil.SQL2Str("%" + SIDBUtil.SQL2Like(individualCode) + "%"," "));
    }
    if (SIUtil.isNotNull(makerName)) {
      sql.append("AND mk.makername LIKE ").append(SIDBUtil.SQL2Str("%" + SIDBUtil.SQL2Like(makerName) + "%"," "));
    }
    if (SIUtil.isNotNull(disableFlg)) {
      sql.append("AND cm.disableflg=").append(SIDBUtil.sqlEncode(disableFlg)).append(" ");
    }
    sql.append("ORDER BY cm.individualcode");
    return sql.toString();
  }
  
  public String getLackSql(){
    StringBuffer sql = new StringBuffer();
    
    sql.append("SELECT cmdtylist.individualcode ");
    sql.append(",cmdtylist.cmdtyname ");
    sql.append(",maker.makername ");
    sql.append(",color.colorname ");
    sql.append(",stock.stockname ");
    sql.append(",coalesce(orderlist.order0,0) AS order0 ");
    sql.append(",coalesce(orderlist.order1,0) AS order1 ");
    sql.append(",coalesce(orderlist.order2,0) AS order2 ");
    sql.append(",coalesce(orderlist.order3,0) AS order3 ");
    sql.append(",coalesce(keeplist.keepamount,0) AS keepamount ");
    sql.append(",coalesce(reservelist.reserveamount,0) AS reserveamount ");
    sql.append(",coalesce(hacchulist.hacchuamount,0) AS hacchuamount ");
    sql.append(",coalesce(hacchulist.hacchuamountall,0) AS hacchuamountall ");
    sql.append(",amountlist.realamount ");
    sql.append(",amountlist.ecamount ");
    sql.append(",amountlist.targetamount ");
    sql.append(",CASE WHEN cmdtylist.amountflg='1' AND coalesce(orderlist.order1,0) > 0 ");
    sql.append(" THEN round(amountlist.targetamount/coalesce(orderlist.order1,0),2)::text ");
    sql.append(" WHEN cmdtylist.amountflg='0' THEN '受発注商品' ");
    sql.append(" ELSE '過去1ヶ月注文なし' END AS stockrate ");
    sql.append(",cmdtylist.arrivalschedulemsg ");
    sql.append(",cmdtylist.unitprice ");
    sql.append(",individual.purchaseprice+individual.processingexpence AS purchaseprice ");
    sql.append(",individual.purchaseprice2 ");
    sql.append(",CASE WHEN cmdtylist.frontdispflg=0 THEN 'フロント非表示' ");
    sql.append(" WHEN cmdtylist.inquiryflg=1 THEN 'お問い合わせ' ");
    sql.append(" WHEN amountlist.ecamount>0 THEN 'カート' ");
    sql.append(" WHEN cmdtylist.rsrvenableflg=1 AND cmdtylist.rsrvamount IS NULL THEN '予約' ");
    sql.append(" WHEN cmdtylist.rsrvenableflg=1 AND cmdtylist.rsrvamount<>0 THEN '予約' ");
    sql.append(" WHEN cmdtylist.usednewflg IN ('1','7') THEN 'SOLDOUT' ");
    sql.append(" WHEN cmdtylist.soldoutdate+").append(SIConfig.DEFAULT_SOLDOUT).append(" <= current_date THEN 'SOLDOUT' ");
    sql.append(" ELSE 'フロント非表示' END AS frontdisp ");
    sql.append(",CASE cmdtylist.amountflg WHEN '0' THEN 'しない' WHEN '1' THEN 'する' ELSE '' END AS amountflg ");
    sql.append(",CASE cmdtylist.disableflg WHEN '0' THEN '通常' WHEN '1' THEN '廃盤予定' WHEN '9' THEN '廃盤' ELSE '販売保留' END AS disableflg ");
    sql.append("FROM individualtbl individual,cmdtyunittbl cmdtylist ");
    sql.append("LEFT OUTER JOIN ");
    sql.append("(SELECT d.individualcode,sum(d.amount+d.amountadd) AS hacchuamountall ");
    sql.append(" ,sum(CASE WHEN h.hacchuoutputdatetime IS NOT NULL THEN d.amount+d.amountadd ELSE 0 END) AS hacchuamount ");
    sql.append(" FROM hacchudetailtbl d,hacchutbl h ");
    sql.append(" WHERE h.hacchucode=d.hacchucode AND h.layercode=d.layercode AND h.enabledflg='1' AND h.status='1' AND d.shippmentdate IS NULL AND d.branchcode<>'19' ");
    sql.append(" GROUP BY d.individualcode) hacchulist ");
    sql.append("ON cmdtylist.individualcode=hacchulist.individualcode ");
    sql.append("LEFT OUTER JOIN ");
    sql.append("(SELECT d.individualcode ");
    sql.append(" ,SUM(CASE WHEN h.initdatetime>=(current_date+'-7 days'::interval) AND h.initdatetime<=current_date THEN d.amount ELSE 0 END) AS order0 ");
    sql.append(" ,SUM(CASE WHEN h.initdatetime>=(current_date+'-1 months'::interval) AND h.initdatetime<=current_date THEN d.amount ELSE 0 END) AS order1 ");
    sql.append(" ,SUM(CASE WHEN h.initdatetime>=(current_date+'-2 months'::interval) AND h.initdatetime<=(current_date+'-1 months'::interval) THEN d.amount ELSE 0 END) AS order2 ");
    sql.append(" ,SUM(CASE WHEN h.initdatetime>=(current_date+'-3 months'::interval) AND h.initdatetime<=(current_date+'-2 months'::interval) THEN d.amount ELSE 0 END) AS order3 ");
    sql.append(" FROM ordertbl h,orderdetailtbl d ");
    sql.append(" WHERE h.ordercode=d.ordercode AND h.orderbranchcode=d.orderbranchcode AND h.enabledflg='1' ");
    sql.append(" AND h.status<>'0' AND h.initdatetime>=(current_date+'-3 months'::interval) ");
    sql.append(" GROUP BY d.individualcode) orderlist ");
    sql.append("ON cmdtylist.individualcode=orderlist.individualcode ");
    sql.append("LEFT OUTER JOIN ");
    sql.append("(SELECT kd.individualcode,sum(kd.amount) AS keepamount ");
    sql.append(" FROM keepdetailtbl kd,keeptbl kh WHERE kh.keepnumber=kd.keepnumber AND kh.status<>'2' AND kd.branchcode<>'19' ");
    sql.append(" GROUP BY individualcode) keeplist ");
    sql.append("ON cmdtylist.individualcode=keeplist.individualcode ");
    sql.append("LEFT OUTER JOIN ");
    sql.append("(SELECT individualcode,sum(amount) AS reserveamount ");
    sql.append(" FROM reserveordertbl WHERE reserveflg=0 GROUP BY individualcode) reservelist ");
    sql.append("ON cmdtylist.individualcode=reservelist.individualcode ");
    sql.append("LEFT OUTER JOIN stockmtbl stock ON cmdtylist.stockcode=stock.stockcode ");
    sql.append(",makertbl maker,colortbl color ");
    sql.append(",(SELECT individualcode ");
    sql.append(" ,sum(CASE WHEN branchcode <> '19' THEN realamount ELSE 0 END) AS realamount ");
    sql.append(" ,sum(CASE WHEN branchcode <> '19' THEN ecamount ELSE 0 END) AS ecamount ");
    sql.append(" ,sum(CASE WHEN branchcode = ").append(SIDBUtil.SQL2Str(this.branchCode)).append(" THEN ecamount ELSE 0 END) AS targetamount ");
    sql.append(" FROM realstockvw GROUP BY individualcode) amountlist ");
    sql.append("WHERE cmdtylist.usednewflg NOT IN ('0','3') ");
    sql.append("AND cmdtylist.individualcode=amountlist.individualcode ");
    sql.append("AND cmdtylist.colorcode=color.colorcode ");
    sql.append("AND cmdtylist.makercode=maker.makercode ");
    sql.append("AND cmdtylist.individualcode=individual.individualcode ");
    if (SIUtil.isNotNull(this.amountFlg)) {
      sql.append("AND cmdtylist.amountflg = ").append(SIDBUtil.SQL2Str(this.amountFlg," "));
    }
    if (SIUtil.isNotNull(motherCtgry)) {
      sql.append("AND substr(cmdtyList.individualcode,0,2)=").append(SIDBUtil.SQL2Str(getCtgryHead()," "));
    }
    if (SIUtil.isNotNull(makerCode)) {
      sql.append("AND cmdtyList.makercode = ").append(SIDBUtil.SQL2Str(this.getMakerCode()," "));
    }
    if (SIUtil.isNotNull(stockCode)) {
      sql.append("AND cmdtyList.stockcode = ").append(SIDBUtil.SQL2Str(this.getStockCode()," "));
    }
    if (SIUtil.isNotNull(makerName)) {
      sql.append("AND maker.makername LIKE '%").append(SIDBUtil.SQL2Like(this.getMakerName())).append("%' ");
    }
    if (SIUtil.isNotNull(stockName)) {
      sql.append("AND stock.stockname LIKE '%").append(SIDBUtil.SQL2Like(this.getStockName())).append("%' ");
    }
    sql.append("AND coalesce(orderlist.order1,0)+coalesce(orderlist.order2,0)+coalesce(orderlist.order3,0)+amountlist.realamount>0 ");
    sql.append("ORDER BY cmdtylist.individualcode");
    
    return sql.toString();
  }
  
  public String getCmdtySql(){
    return getCmdtySql("");
  }
  
  public String getCmdtySql(String usednew){
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT u.individualcode,u.cmdtyname,m.makername,c.colorname,s.stockname");
    sql.append(",CASE WHEN i.guaranteedterm IS NULL OR i.guaranteedterm = 0 THEN '' ");
    sql.append("WHEN i.guaranteedterm < 12 THEN i.guaranteedterm % 12 ||'ヶ月' ");
    sql.append("WHEN i.guaranteedterm % 12 = 0 THEN trunc(i.guaranteedterm / 12) ||'年' ");
    sql.append("ELSE trunc(i.guaranteedterm / 12) ||'年'||i.guaranteedterm % 12 ||'ヶ月' END AS guaranteedterm ");
    sql.append(",CASE WHEN u.deliverytypecode = '1' THEN '通常' ");
    sql.append(" WHEN u.cmdtysize = '1' THEN '無料' WHEN u.cmdtysize = '2' THEN 'B' WHEN u.cmdtysize = '3' THEN 'C' ");
    sql.append(" WHEN u.cmdtysize = '4' THEN 'D' WHEN u.cmdtysize = '5' THEN 'E' ELSE 'F' END AS cmdtysize ");
    sql.append(",i.bgprice,i.purchaseprice,i.processingexpence,i.purchaseprice2,u.newfixedprice,i.bgpricewithouttax");
    sql.append(",CASE u.amountflg WHEN 0 THEN 'しない' ELSE 'する' END AS amountflg ");
    sql.append(",CASE u.frontdispflg WHEN 0 THEN '非表示' ELSE '表示' END AS frontdispflg ");
    sql.append(",CASE u.disableflg WHEN 0 THEN '通常' WHEN 1 THEN '廃盤予定' WHEN 2 THEN '販売保留' ELSE '廃盤' END AS disableflg ");
    sql.append(",cm.othername,i.setitem1,i.setitem2");
    if (SIUtil.isNotNull(this.getBranchCode())) sql.append(",b.amount ");
    else sql.append(",u.amount ");
    sql.append(",CASE u.rsrvenableflg WHEN 0 THEN 'しない' ELSE 'する' END AS rsrvenableflg ");
    sql.append(",CASE WHEN u.rsrvamount IS NULL THEN '無制限' ELSE u.rsrvamount::text END AS rsrvamount ");
    sql.append(",CASE u.inquiryflg WHEN 0 THEN '通常' WHEN 1 THEN 'お問い合わせ'ELSE '在庫多数' END AS inquiryflg ");
    sql.append(",u.bgpamount ");
    sql.append(",CASE WHEN m.contractflg<>0 THEN '要契約ブランド' WHEN substring(u.individualcode,0,2) IN ('V','H','L') THEN '化粧品' ELSE '契約不要' END AS contractflg ");
    sql.append(",CASE WHEN u.frontdispflg=0 THEN 'フロント非表示' ");
    sql.append(" WHEN u.inquiryflg=1 THEN 'お問い合わせ' ");
    sql.append(" WHEN u.amountflg=1 AND u.amount-u.bgpamount=0 AND u.rsrvenableflg=1 AND (u.rsrvamount IS NULL OR u.rsrvamount>0) THEN '予約' ");
    sql.append(" WHEN u.amountflg=1 AND u.amount-u.bgpamount=0 AND (u.usednewflg IN ('1','7') OR u.soldoutdate + ").append(SIConfig.DEFAULT_SOLDOUT).append(" > CURRENT_DATE) THEN 'SOLDOUT' ");
    sql.append(" WHEN u.amountflg=0 THEN 'カート' ");
    sql.append(" WHEN u.amount-coalesce(u.bgpamount,0)>0 THEN 'カート' ");
    sql.append("ELSE 'フロント非表示' END AS salessituation ");
    sql.append("FROM makertbl m,colortbl c,cmdtymtbl cm,individualtbl i ");
    sql.append("LEFT OUTER JOIN (SELECT individualcode,sum(CASE WHEN branchcode=").append(SIDBUtil.SQL2Str(this.getBranchCode()," THEN amount ELSE 0 END) AS amount "));
    sql.append("FROM storestocktbl WHERE amount>0 GROUP BY individualcode) b ON i.individualcode=b.individualcode ");
    sql.append(",cmdtyunittbl u LEFT OUTER JOIN stockmtbl s ON u.stockcode=s.stockcode ");
    sql.append("WHERE u.individualcode=i.individualcode AND u.colorcode=c.colorcode ");
    sql.append("AND u.makercode=m.makercode AND u.cmdtycode=cm.cmdtycode ");
    if (SIUtil.isNotNull(usednew)) {
      if ("used".equals(usednew)) sql.append("AND u.usednewflg='0' ");
      else if ("bgused".equals(usednew)) sql.append("AND u.usednewflg='3' ");
      else if ("new".equals(usednew)) sql.append("AND u.usednewflg IN ('1','6','7') ");
      else if ("outlet".equals(usednew)) sql.append("AND u.usednewflg IN ('2','4') ");
    }
    if (SIUtil.isNotNull(this.branchCode)) {
      sql.append("AND b.amount>0 ");
    }
    if (SIUtil.isNotNull(this.getIndividualCode())) {
      sql.append("AND u.individualcode LIKE '").append(SIDBUtil.SQL2Like(this.getIndividualCode())).append("%' ");
    }
    if (SIUtil.isNotNull(this.getCmdtyName())) {
      sql.append("AND u.cmdtyname LIKE '%").append(SIDBUtil.SQL2Like(this.getCmdtyName())).append("%' ");
    }
    if (SIUtil.isNotNull(this.getMakerCode())) {
      sql.append("AND u.makercode =").append(SIDBUtil.SQL2Str(this.getMakerCode()," "));
    }
    if (SIUtil.isNotNull(this.getStockCode())) {
      sql.append("AND u.stockcode =").append(SIDBUtil.SQL2Str(this.getStockCode()," "));
    }
    if (!"2".equals(this.getAmountFlg())) {
      sql.append("AND u.amountflg =").append(SIDBUtil.SQL2Str(this.getAmountFlg()," "));
    }
    if (SIUtil.isNotNull(this.getDisableFlg())) {
      sql.append("AND u.disableflg =").append(SIDBUtil.SQL2Str(this.getDisableFlg()," "));
    }
    if (getSalesSituationC()!=null && getSalesSituationC().length>0 && getSalesSituationC().length<SIFlagConf.SIFLAG_SALES_SITUATION_DISP_NAME.length){
      sql.append("AND CASE WHEN u.frontdispflg=0 THEN 4 ");
      sql.append(" WHEN u.inquiryflg=1 THEN 1 ");
      sql.append(" WHEN u.amountflg=1 AND u.amount-u.bgpamount=0 AND u.rsrvenableflg=1 AND (u.rsrvamount IS NULL OR u.rsrvamount>0) THEN 2 ");
      sql.append(" WHEN u.amountflg=1 AND u.amount-u.bgpamount=0 AND (u.usednewflg IN ('1','7') OR u.soldoutdate + 7 > CURRENT_DATE) THEN 3 ");
      sql.append(" WHEN u.amountflg=0 THEN 0 WHEN u.amount-u.bgpamount>0 THEN 0 ELSE 4 END IN (");
      for (int i=0;i<getSalesSituationC().length;i++) {
        if (i>0) sql.append(",");
        sql.append(SIDBUtil.SQL2Str(getSalesSituationC()[i]));
      }
      sql.append(") ");
    }else if (getSalesSituationC()==null || getSalesSituationC().length==0){
      sql.append(" AND false ");
    }
    sql.append("ORDER BY u.individualcode");
    
    return sql.toString();
  }
  
  public String getWarehouseStoreSql(){
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT h.hacchucode || TO_CHAR(now(),'-MMDD') AS column1 ");
    sql.append(",NULL AS column2 ");
    sql.append(",h.initdatetime::timestamp(0) AS column3 ");
    sql.append(",h.arrivalplandate AS column4 ");
    sql.append(",h.hacchuupdatedatetime AS column5 ");
    sql.append(",a.arrivalplandate AS column6 ");
    sql.append(",'99' AS column7 ");
    sql.append(",NULL AS column8 ");
    sql.append(",NULL AS column9 ");
    sql.append(",NULL AS column10 ");
    sql.append(",NULL AS column11 ");
    sql.append(",NULL AS column12 ");
    sql.append(",NULL AS column13 ");
    sql.append(",NULL AS column14 ");
    sql.append(",NULL AS column15 ");
    sql.append(",NULL AS column16 ");
    sql.append(",NULL AS column17 ");
    sql.append(",NULL AS column18 ");
    sql.append(",substring(h.stockcode||'：'||translate(h.stockname,e'\\',',e'’、'),0,41) AS column19 ");
    sql.append(",NULL AS column20 ");
    sql.append(",NULL AS column21 ");
    sql.append(",NULL AS column22 ");
    sql.append(",NULL AS column23 ");
    sql.append(",NULL AS column24 ");
    sql.append(",CASE WHEN i.jancodeindividual IS NULL OR i.jancodeindividual='' THEN i.individualcode ELSE i.jancodeindividual END AS column25 ");
    sql.append(",NULL AS column26 ");
    sql.append(",substring(translate(d.cmdtyname,e'\\',',e'’、'),0,31) AS column27 ");
    sql.append(",substring(translate(d.cmdtyname,e'\\',',e'’、'),31,30) AS column28 ");
    sql.append(",NULL AS column29 ");
    sql.append(",NULL AS column30 ");
    sql.append(",d.amount+d.amountadd AS column31 ");
    sql.append(",NULL AS column32 ");
    sql.append(",NULL AS column33 ");
    sql.append(",NULL AS column34 ");
    sql.append(",NULL AS column35 ");
    sql.append(",NULL AS column36 ");
    sql.append(",NULL AS column37 ");
    sql.append(",NULL AS column38 ");
    sql.append(",substring(coalesce(translate(i.setitem1,e'\\',',e'’、'),'')||coalesce(translate(i.setitem2,e'\\',',e'’、'),''),0,21) AS column39 ");
    sql.append(",NULL AS column40 ");
    sql.append(",NULL AS column41 ");
    sql.append(",NULL AS column42 ");
    sql.append(",NULL AS column43 ");
    sql.append("FROM hacchutbl h LEFT OUTER JOIN hacchutbl a ON h.hacchucode=a.hacchucode AND h.layercode=a.layercode+1 AND h.arrivalplandate<>a.arrivalplandate ");
    sql.append(",hacchudetailtbl d,individualtbl i ");
    sql.append("WHERE h.hacchucode=d.hacchucode AND h.layercode=d.layercode AND h.enabledflg=1 ");
    sql.append("AND h.orderflg=0 AND d.individualcode=i.individualcode AND d.shippmentdate IS NULL ");
    if (SIUtil.isNotNull(getSalesDateFrom())) {
      sql.append("AND h.arrivalplandate>=").append(SIDBUtil.SQL2Str(getSalesDateFrom()," "));
    }
    if (SIUtil.isNotNull(getSalesDateTo())) {
      sql.append("AND h.arrivalplandate<=").append(SIDBUtil.SQL2Str(getSalesDateTo()," "));
    }
    if (SIUtil.isNotNull(getHacchuCode())) {
      sql.append("AND h.hacchucode=").append(SIDBUtil.SQL2Str(getHacchuCode()," "));
    }
    if (SIUtil.isNotNull(getBranchCode())) {
      sql.append("AND d.branchcode=").append(SIDBUtil.SQL2Str(getBranchCode()," "));
    }
    sql.append("ORDER BY h.hacchucode,d.branchcode,d.individualcode");
    return sql.toString();
  }
  
  public String getWarehouseShipSql(String dateTime) {
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT s.outputordercode AS column1 ");
    sql.append(",NULL AS column2 ");
    sql.append(",s.sendmaildatetime AS column3 ");
    sql.append(",CASE s.deliverydate WHEN '2000-01-01' THEN NULL ELSE s.deliverydate END AS column4 ");
    sql.append(",NULL AS column5 ");
    sql.append(",NULL AS column6 ");
    sql.append(",NULL AS column7 ");
    sql.append(",'99' AS column8 ");
    sql.append(",NULL AS column9 ");
    sql.append(",NULL AS column10 ");
    sql.append(",NULL AS column11 ");
    sql.append(",NULL AS column12 ");
    sql.append(",NULL AS column13 ");
    sql.append(",NULL AS column14 ");
    sql.append(",NULL AS column15 ");
    sql.append(",NULL AS column16 ");
    sql.append(",NULL AS column17 ");
    sql.append(",NULL AS column18 ");
    sql.append(",NULL AS column19 ");
    sql.append(",NULL AS column20 ");
    sql.append(",NULL AS column21 ");
    sql.append(",NULL AS column22 ");
    sql.append(",substring(coalesce(translate(s.companyname,e'\\',',e'’、'),'')||translate(s.deliveryaddressee,e'\\',',e'’、'),0,31) AS column23 ");
    sql.append(",substring(coalesce(translate(s.companyname,e'\\',',e'’、'),'')||translate(s.deliveryaddressee,e'\\',',e'’、'),31,30) AS column24 ");
    sql.append(",CASE WHEN s.branchcode='15' ");
    sql.append("THEN ('西濃運輸' || CASE WHEN s.daibikibranchcode = 15 AND (substring(s.outputordercode,position('-' in s.outputordercode)) = '-01' OR substring(s.outputordercode,position('-' in s.outputordercode)) = '-02') THEN '/東配/'||s.daibikiprice||'/' ");
    sql.append(" ELSE '/東配//' END || coalesce(s.deliverytime,'') || CASE WHEN o.branchcode='17' THEN '/EG' ELSE '/BG' END) ");
    sql.append("ELSE ('佐川急便' || CASE WHEN s.daibikibranchcode = 18 AND (substring(s.outputordercode,position('-' in s.outputordercode)) = '-01' OR substring(s.outputordercode,position('-' in s.outputordercode)) = '-02') THEN '/物流センター/'||s.daibikiprice||'/' ");
    sql.append(" ELSE '/物流センター//' END || coalesce(s.deliverytime,'') || CASE WHEN o.branchcode='17' THEN '/EG' ELSE '/BG' END) END AS column25 ");
    sql.append(",NULL AS column26 ");
    sql.append(",substring(translate(s.deliveryaddressee,e'\\',',e'’、'),0,21) AS column27 ");
    sql.append(",'様' AS column28 ");
    sql.append(",s.postcode1||'-'||s.postcode2 AS column29 ");
    sql.append(",substring(s.address1||translate(s.address2,e'\\',',e'’、')||coalesce(translate(s.address3,e'\\',',e'’、'),''),0,41) AS column30 ");
    sql.append(",substring(s.address1||translate(s.address2,e'\\',',e'’、')||coalesce(translate(s.address3,e'\\',',e'’、'),''),41,40) AS column31 ");
    sql.append(",s.tel AS column32 ");
    sql.append(",s.fax AS column33 ");
    sql.append(",NULL AS column34 ");
    sql.append(",NULL AS column35 ");
    sql.append(",NULL AS column36 ");
    sql.append(",o.email AS column37 ");//e-mail
    sql.append(",NULL AS column38 ");
    sql.append(",NULL AS column39 ");
    sql.append(",NULL AS column40 ");
    sql.append(",NULL AS column41 ");
    sql.append(",NULL AS column42 ");
    sql.append(",NULL AS column43 ");
    sql.append(",NULL AS column44 ");
    sql.append(",NULL AS column45 ");
    sql.append(",coalesce(substring(translate(translate(s.memo,e'\\r\\n',''),e'\\',',e'’、'),0,41),'')||'/'||coalesce(o.branchcode::text,'') AS column46 ");
    sql.append(",NULL AS column47 ");
    sql.append(",NULL AS column48 ");
    sql.append(",NULL AS column49 ");
    sql.append(",NULL AS column50 ");
    sql.append(",NULL AS column51 ");
    sql.append(",CASE WHEN i.jancodeindividual IS NULL OR i.jancodeindividual='' THEN i.individualcode ELSE i.jancodeindividual END AS column52 ");
    sql.append(",NULL AS column53 ");
    sql.append(",substring(translate(s.cmdtyname,e'\\',',e'’、'),0,31) AS column54 ");
    sql.append(",substring(translate(s.cmdtyname,e'\\',',e'’、'),31,30) AS column55 ");
    sql.append(",NULL AS column56 ");
    sql.append(",NULL AS column57 ");
    sql.append(",s.amount AS column58 ");
    sql.append(",NULL AS column59 ");
    sql.append(",NULL AS column60 ");
    sql.append(",NULL AS column61 ");
    sql.append(",NULL AS column62 ");
    sql.append(",NULL AS column63 ");
    sql.append(",NULL AS column64 ");
    sql.append(",NULL AS column65 ");
    sql.append(",substring(coalesce(translate(s.setitem1,e'\\',',e'’、'),'')||coalesce(translate(s.setitem2,e'\\',',e'’、'),''),0,21) AS column66 ");
    sql.append(",NULL AS column67 ");
    sql.append(",NULL AS column68 ");
    sql.append(",NULL AS column69 ");
    sql.append(",NULL AS column70 ");
    sql.append("FROM shippmentrequesttbl s,individualtbl i,ordertbl o ");
    sql.append("WHERE s.individualcode=i.individualcode AND s.enabledflg=1 AND s.ordercode=o.ordercode AND o.enabledflg=1 ");
    if (SIUtil.isNotNull(getSalesDateFrom())) {
      sql.append(" AND s.sendmaildatetime::date>=").append(SIDBUtil.SQL2Str(getSalesDateFrom()," "));
    }
    if (SIUtil.isNotNull(getSalesDateTo())) {
      sql.append(" AND s.sendmaildatetime::date<=").append(SIDBUtil.SQL2Str(getSalesDateTo()," "));
    }
    if (SIUtil.isNotNull(getOrderCode())) {
      sql.append(" AND s.ordercode=").append(SIDBUtil.SQL2Str(getOrderCode()," "));
    }
    if (SIUtil.isNotNull(getBranchCode())) {
      sql.append(" AND s.branchcode=").append(SIDBUtil.SQL2Str(getBranchCode()," "));
    }
    if (SIUtil.isNotNull(getCheckFlg())&&!"2".equals(getCheckFlg())) {
      sql.append(" AND s.checkflg=").append(SIDBUtil.SQL2Str(getCheckFlg()," "));
      if ("0".equals(getCheckFlg())) sql.append(" AND s.sendmaildatetime<").append(SIDBUtil.SQL2Str(dateTime," "));
    }
    sql.append("ORDER BY s.sendmaildatetime,s.outputordercode,s.branchcode ");
    sql.append(",CASE s.deliverytime WHEN '01' THEN 1 WHEN '12' THEN 2 WHEN '14' THEN 3 WHEN '16' THEN 4 WHEN '04' THEN 5 ELSE 6 END ");
    sql.append(",s.individualcode");
    return sql.toString();
  }
  
  public String getWarehouseCmdtySql() {
    StringBuffer sql = new StringBuffer();
    sql.append(" SELECT CASE WHEN i.jancodeindividual IS NULL OR i.jancodeindividual = '' THEN i.individualcode ELSE i.jancodeindividual END AS column1 ");
    sql.append(",i.individualcode AS column2 ");
    sql.append(",substring(translate(c.cmdtyname,e'\\',',e'’、'),0,31) AS column3 ");
    sql.append(",substring(translate(c.cmdtyname,e'\\',',e'’、'),31,30) AS column4 ");
    sql.append(",'*' AS column5 ");
    sql.append(",NULL AS column6 ");
    sql.append(",NULL AS column7 ");
    sql.append(",'*' AS column8 ");
    sql.append(",NULL AS column9 ");
    sql.append(",NULL AS column10 ");
    sql.append(",i.bgprice AS column11 ");
    sql.append(",i.purchaseprice + i.processingexpence AS column12 ");
    sql.append(",'*' AS column13 ");
    sql.append(",NULL AS column14 ");
    sql.append(",NULL AS column15 ");
    sql.append(",NULL AS column16 ");
    sql.append("FROM individualtbl i, cmdtymtbl c ");
    sql.append("WHERE i.cmdtycode = c.cmdtycode AND i.amountflg = '1' ");
    if (SIUtil.isNotNull(getIndividualCode())) {
      sql.append("AND i.individualcode LIKE '").append(SIDBUtil.SQL2Like(this.getIndividualCode())).append("%' ");
    }
    if (SIUtil.isNotNull(getLastUpdate()) && SICheckUtil.isDigit(getLastUpdate())) {
      sql.append(" AND i.initdatetime >= current_date + ' -").append(getLastUpdate()).append(" days'::interval ");
    }
    
    if (getUsednewFlgC()!=null && getUsednewFlgC().length>0){
      sql.append(" AND i.usednewFlg IN (");
      for (int i=0;i<getUsednewFlgC().length;i++) {
        if (i>0) sql.append(",");
        sql.append(SIDBUtil.SQL2Str(getUsednewFlgC()[i]));
      }
      sql.append(") ");
    }else{
      sql.append(" AND false ");
    }
    
    if (getDisableFlgC()!=null && getDisableFlgC().length>0){
      sql.append(" AND c.disableFlg IN (");
      for (int i=0;i<getDisableFlgC().length;i++) {
        if (i>0) sql.append(",");
        sql.append(SIDBUtil.SQL2Str(getDisableFlgC()[i]));
      }
      sql.append(") ");
    }else{
      sql.append(" AND false ");
    }
    sql.append("ORDER BY CASE WHEN i.jancodeindividual IS NULL OR i.jancodeindividual = '' THEN i.individualcode ELSE i.jancodeindividual END");
    return sql.toString();
  }
  
  public String getBranchDeadStockSql(){
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT b.branchname,u.individualcode,u.cmdtyname,s.amount,store.laststore ");
    sql.append(",replace(replace(replace(replace(replace(replace(age(current_date,store.laststore)::text,' years','年'),' year','年'),' mons','ヶ月'),' mon','ヶ月'),' days','日'),' day','日') AS period ");
    sql.append(",CASE u.deliverytypecode WHEN 0 THEN '個別' WHEN 1 THEN '通常' ELSE '' END AS deliverytype ");
    sql.append(",CASE u.disableflg WHEN 0 THEN '通常' WHEN 1 THEN '廃盤予定' WHEN 2 THEN '販売保留' WHEN 9 THEN '廃盤' ELSE '' END AS disableflg ");
    sql.append("FROM cmdtyunittbl u,branchtbl b,storestocktbl s ");
    sql.append("LEFT OUTER JOIN ");
    sql.append("(SELECT individualcode,storebranch,max(initdatetime) AS laststore FROM storehistorytbl ");
    sql.append("WHERE delflg IN (1,4) AND storetype NOT IN (2,5,6,10,11) GROUP BY individualcode,storebranch) store ");
    sql.append("ON s.individualcode=store.individualcode AND s.branchcode=store.storebranch ");
    sql.append("WHERE s.individualcode=u.individualcode AND s.branchcode=b.branchcode ");
    sql.append("AND u.amountflg=1 AND s.amount<>0 AND b.branchcode=").append(SIDBUtil.SQL2Str(this.getBranchCode()," "));
    if (SIUtil.isNotNull(this.getIndividualCode())) {
      sql.append("AND u.individualcode LIKE '").append(SIDBUtil.SQL2Like(this.getIndividualCode())).append("%' ");
    }
    if (SIUtil.isNotNull(this.getMakerCode())) {
      sql.append("AND u.makercode=").append(SIDBUtil.SQL2Str(this.getMakerCode()," "));
    }
    if (SIUtil.isNotNull(this.getDeliveryType())) {
      sql.append("AND u.deliverytypecode=").append(SIDBUtil.SQL2Str(this.getDeliveryType()," "));
    }
    if (SIUtil.isNotNull(this.getExecSpan())) {
      sql.append("AND CASE WHEN store.laststore IS NULL THEN true ");
      sql.append("ELSE store.laststore + '").append(this.execSpan).append("'::interval < current_date END ");
    }
    if (getUsednewFlgC()!=null && getUsednewFlgC().length>0){
      sql.append(" AND u.usednewFlg IN (");
      for (int i=0;i<getUsednewFlgC().length;i++) {
        if (i>0) sql.append(",");
        sql.append(SIDBUtil.SQL2Str(getUsednewFlgC()[i]));
      }
      sql.append(") ");
    }else{
      sql.append(" AND false ");
    }
    if (getDisableFlgC()!=null && getDisableFlgC().length>0){
      sql.append(" AND u.disableFlg IN (");
      for (int i=0;i<getDisableFlgC().length;i++) {
        if (i>0) sql.append(",");
        sql.append(SIDBUtil.SQL2Str(getDisableFlgC()[i]));
      }
      sql.append(") ");
    }else{
      sql.append(" AND false ");
    }
    sql.append("ORDER BY b.branchcode,store.laststore,u.individualcode");
    
    return sql.toString();
  }
  
  public String getRepeatSalesDataSql(){
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT u.individualcode,u.cmdtyname,m.makername,c.colorname,s.stockname, ");
    sql.append("l.new01,l.rep01,l.new12,l.rep12,l.new23,l.rep23,l.new34,l.rep34,l.new45,l.rep45,l.new56,l.rep56,l.new,l.rep ");
    sql.append("FROM cmdtyunittbl u LEFT OUTER JOIN stockmtbl s ON u.stockcode=s.stockcode ");
    sql.append(",makertbl m,colortbl c ");
    sql.append(",(SELECT d.individualcode ");
    sql.append(",sum(CASE WHEN h.initdatetime >= current_date + '-1 month'::interval AND list.ordercode IS NOT NULL THEN 1 ELSE 0 END) AS new01 ");
    sql.append(",sum(CASE WHEN h.initdatetime >= current_date + '-1 month'::interval AND list.ordercode IS NULL THEN 1 ELSE 0 END) AS rep01 ");
    sql.append(",sum(CASE WHEN h.initdatetime >= current_date + '-2 month'::interval AND h.initdatetime <= current_date + '-1 month'::interval AND list.ordercode IS NOT NULL THEN 1 ELSE 0 END) AS new12 ");
    sql.append(",sum(CASE WHEN h.initdatetime >= current_date + '-2 month'::interval AND h.initdatetime <= current_date + '-1 month'::interval AND list.ordercode IS NULL THEN 1 ELSE 0 END) AS rep12 ");
    sql.append(",sum(CASE WHEN h.initdatetime >= current_date + '-3 month'::interval AND h.initdatetime <= current_date + '-2 month'::interval AND list.ordercode IS NOT NULL THEN 1 ELSE 0 END) AS new23 ");
    sql.append(",sum(CASE WHEN h.initdatetime >= current_date + '-3 month'::interval AND h.initdatetime <= current_date + '-2 month'::interval AND list.ordercode IS NULL THEN 1 ELSE 0 END) AS rep23 ");
    sql.append(",sum(CASE WHEN h.initdatetime >= current_date + '-4 month'::interval AND h.initdatetime <= current_date + '-3 month'::interval AND list.ordercode IS NOT NULL THEN 1 ELSE 0 END) AS new34 ");
    sql.append(",sum(CASE WHEN h.initdatetime >= current_date + '-4 month'::interval AND h.initdatetime <= current_date + '-3 month'::interval AND list.ordercode IS NULL THEN 1 ELSE 0 END) AS rep34 ");
    sql.append(",sum(CASE WHEN h.initdatetime >= current_date + '-5 month'::interval AND h.initdatetime <= current_date + '-4 month'::interval AND list.ordercode IS NOT NULL THEN 1 ELSE 0 END) AS new45 ");
    sql.append(",sum(CASE WHEN h.initdatetime >= current_date + '-5 month'::interval AND h.initdatetime <= current_date + '-4 month'::interval AND list.ordercode IS NULL THEN 1 ELSE 0 END) AS rep45 ");
    sql.append(",sum(CASE WHEN h.initdatetime >= current_date + '-6 month'::interval AND h.initdatetime <= current_date + '-5 month'::interval AND list.ordercode IS NOT NULL THEN 1 ELSE 0 END) AS new56 ");
    sql.append(",sum(CASE WHEN h.initdatetime >= current_date + '-7 month'::interval AND h.initdatetime <= current_date + '-5 month'::interval AND list.ordercode IS NULL THEN 1 ELSE 0 END) AS rep56 ");
    sql.append(",sum(CASE WHEN list.ordercode IS NOT NULL THEN 1 ELSE 0 END) AS new ");
    sql.append(",sum(CASE WHEN list.ordercode IS NULL THEN 1 ELSE 0 END) AS rep ");
    sql.append("FROM orderlatestvw h,(SELECT DISTINCT ordercode,individualcode,orderbranchcode FROM orderdetailtbl) d ");
    sql.append("LEFT OUTER JOIN ");
    sql.append("(SELECT d.individualcode,h.custcode,min(h.ordercode) AS ordercode FROM orderlatestvw h,orderdetaillatestvw d WHERE h.ordercode=d.ordercode AND h.status=1 GROUP BY h.custcode,d.individualcode) list ");
    sql.append("ON list.ordercode=d.ordercode AND list.individualcode=d.individualcode ");
    sql.append("WHERE h.status=1 AND h.ordercode=d.ordercode AND h.orderbranchcode=d.orderbranchcode ");
    sql.append("GROUP BY d.individualcode) l ");
    sql.append("WHERE u.makercode=m.makercode AND u.colorcode=c.colorcode AND u.individualcode=l.individualcode ");
    if (SIUtil.isNotNull(this.getIndividualCode())) {
      sql.append("AND u.individualcode LIKE '").append(SIDBUtil.SQL2Like(this.getIndividualCode())).append("%' ");
    }
    if (SIUtil.isNotNull(this.getMakerName())) {
      sql.append("AND m.makername LIKE '%").append(SIDBUtil.SQL2Like(this.getMakerName())).append("%' ");
    }
    if (SIUtil.isNotNull(this.getMakerCode())) {
      sql.append("AND u.makercode=").append(SIDBUtil.SQL2Str(this.getMakerCode()," "));
    }
    if (SIUtil.isNotNull(this.getStockName())) {
      sql.append("AND s.stockname LIKE '%").append(SIDBUtil.SQL2Like(this.getStockName())).append("%' ");
    }
    sql.append("ORDER BY u.individualcode");
    
    return sql.toString();
  }
  
  public String getCustSalesDataSql(){
    StringBuffer sql = new StringBuffer();
    
    sql.append("SELECT oh.ordercode,oh.custcode,oh.custname,dl.deliveryname,dl.address1,dl.address2,dl.address3,dt.shippmentdate,'' AS individualcode,'' AS cmdtyname,NULL::numeric AS price,NULL::numeric AS amount,dt.totalprice,'商品売上' AS type ");
    sql.append("FROM orderlatestvw oh ");
    sql.append(",(SELECT ordercode,deliveryname,address1,address2,address3 FROM orderdeliverylatestvw WHERE deliverycode=1) dl ");
    sql.append(",(SELECT o.ordercode,o.custcode,d.shippmentdate,sum(d.price*d.amount) AS totalprice FROM ordertbl o,orderdetailtbl d ");
    sql.append("WHERE o.ordercode=d.ordercode AND o.orderbranchcode=d.orderbranchcode AND o.enabledflg=1 AND o.status<>0 AND d.shippmentdate IS NOT NULL ");
    sql.append("AND o.custcode=").append(SIDBUtil.SQL2Str(this.getCustCode()," "));
    sql.append("AND d.shippmentdate>=").append(SIDBUtil.SQL2Str(this.getSalesFrom()," "));
    sql.append("AND d.shippmentdate<").append(SIDBUtil.SQL2Str(this.getSalesTo2()," "));
    sql.append("GROUP BY o.ordercode,o.custcode,d.shippmentdate) dt ");
    sql.append("WHERE oh.ordercode=dl.ordercode AND oh.ordercode=dt.ordercode ");
    sql.append("UNION ALL ");
    sql.append("SELECT oh.ordercode,oh.custcode,oh.custname,dl.deliveryname,dl.address1,dl.address2,dl.address3,oh.updatedatetime::date AS shippmentdate,'' AS individualcode,'' AS cmdtyname,NULL::numeric AS price,NULL::numeric AS amount,dt.totalprice,'商品返品分' AS type ");
    sql.append("FROM orderlatestvw oh ");
    sql.append(",(SELECT ordercode,deliveryname,address1,address2,address3 FROM orderdeliverylatestvw WHERE deliverycode=1) dl ");
    sql.append(",(SELECT o.ordercode,o.custcode,-1*sum(d.price*d.amount) AS totalprice FROM ordertbl o,orderdetailtbl d ");
    sql.append("WHERE o.ordercode=d.ordercode AND o.orderbranchcode=d.orderbranchcode AND o.enabledflg=1 AND o.status=2 ");
    sql.append("AND o.custcode=").append(SIDBUtil.SQL2Str(this.getCustCode()," "));
    sql.append("GROUP BY o.ordercode,o.custcode) dt ");
    sql.append("WHERE oh.ordercode=dl.ordercode AND oh.ordercode=dt.ordercode AND oh.status=2 ");
    sql.append("AND oh.updatedatetime>=").append(SIDBUtil.SQL2Str(this.getSalesFrom()," "));
    sql.append("AND oh.updatedatetime<").append(SIDBUtil.SQL2Str(this.getSalesTo2()," "));
    sql.append("UNION ALL ");
    sql.append("SELECT oh.ordercode,oh.custcode,oh.custname,dl.deliveryname,dl.address1,dl.address2,dl.address3,dt.shippmentdate,'' AS individualcode,'' AS cmdtyname,NULL::numeric AS price,NULL::numeric AS amount ");
    sql.append(",dl.deliveryfee+oh.discountdeliveryfee+oh.fee+oh.discountfee-oh.sumbypoint-oh.sumofdiscount-oh.setdiscount AS totalprice,'送料等' AS type ");
    sql.append("FROM orderlatestvw oh ");
    sql.append(",(SELECT d.ordercode,d.deliveryname,d.address1,d.address2,d.address3,sum(d.deliveryfee) AS deliveryfee ");
    sql.append("FROM orderdeliverytbl d,ordertbl o ");
    sql.append("WHERE o.ordercode=d.ordercode AND o.status<>0 AND o.orderbranchcode=d.orderbranchcode AND o.enabledflg=1 ");
    sql.append("AND o.custcode=").append(SIDBUtil.SQL2Str(this.getCustCode()," "));
    sql.append("GROUP BY d.ordercode,d.deliveryname,d.address1,d.address2,d.address3) dl ");
    sql.append(",(SELECT d.ordercode,max(d.shippmentdate) AS shippmentdate FROM ordertbl o,orderdetailtbl d ");
    sql.append("WHERE o.ordercode=d.ordercode AND o.orderbranchcode=d.orderbranchcode AND o.enabledflg=1 ");
    sql.append("AND o.custcode=").append(SIDBUtil.SQL2Str(this.getCustCode()," "));
    sql.append("GROUP BY d.ordercode HAVING sum(CASE WHEN d.shippmentdate IS NULL THEN 0 ELSE 1 END)=count(*) ");
    sql.append("AND max(d.shippmentdate)>=").append(SIDBUtil.SQL2Str(this.getSalesFrom()," "));
    sql.append("AND max(d.shippmentdate)<").append(SIDBUtil.SQL2Str(this.getSalesTo2(),")dt "));
    sql.append("WHERE oh.ordercode=dl.ordercode AND oh.ordercode=dt.ordercode ");
    sql.append("UNION ALL ");
    sql.append("SELECT oh.ordercode,oh.custcode,oh.custname,dl.deliveryname,dl.address1,dl.address2,dl.address3,oh.updatedatetime::date AS shippmentdate,'' AS individualcode,'' AS cmdtyname,NULL::numeric AS price,NULL::numeric AS amount ");
    sql.append(",-1 * (dl.deliveryfee+oh.discountdeliveryfee+oh.fee+oh.discountfee-oh.sumbypoint-oh.sumofdiscount-oh.setdiscount) AS totalprice,'送料等返品分' AS type ");
    sql.append("FROM orderlatestvw oh ");
    sql.append(",(SELECT d.ordercode,d.deliveryname,d.address1,d.address2,d.address3,sum(d.deliveryfee) AS deliveryfee ");
    sql.append("FROM orderdeliverytbl d,ordertbl o ");
    sql.append("WHERE o.ordercode=d.ordercode AND o.status=2 AND o.orderbranchcode=d.orderbranchcode AND o.enabledflg=1 ");
    sql.append("AND o.custcode=").append(SIDBUtil.SQL2Str(this.getCustCode()," "));
    sql.append("GROUP BY d.ordercode,d.deliveryname,d.address1,d.address2,d.address3) dl ");
    sql.append("WHERE oh.ordercode=dl.ordercode AND oh.status=2 ");
    sql.append("AND oh.updatedatetime>=").append(SIDBUtil.SQL2Str(this.getSalesFrom()," "));
    sql.append("AND oh.updatedatetime<").append(SIDBUtil.SQL2Str(this.getSalesTo2()," "));
    sql.append("UNION ALL ");
    sql.append("SELECT oh.ordercode,oh.custcode,oh.custname,dl.deliveryname,dl.address1,dl.address2,dl.address3,dt.shippmentdate,dt.individualcode,dt.cmdtyname,dt.price,dt.amount,(dt.price*dt.amount) AS totalprice,'商品売上（明細）' AS type ");
    sql.append("FROM ordertbl oh,orderdeliverytbl dl,orderdetailtbl dt ");
    sql.append("WHERE oh.ordercode=dl.ordercode AND oh.ordercode=dt.ordercode AND oh.orderbranchcode=dl.orderbranchcode AND oh.orderbranchcode=dt.orderbranchcode ");
    sql.append("AND dl.deliverycode=dt.deliverycode AND oh.enabledflg=1 AND oh.status<>0 AND dt.shippmentdate IS NOT NULL ");
    sql.append("AND oh.custcode=").append(SIDBUtil.SQL2Str(this.getCustCode()," "));
    sql.append("AND dt.shippmentdate>=").append(SIDBUtil.SQL2Str(this.getSalesFrom()," "));
    sql.append("AND dt.shippmentdate<").append(SIDBUtil.SQL2Str(this.getSalesTo2()," "));
    sql.append("UNION ALL ");
    sql.append("SELECT oh.ordercode,oh.custcode,oh.custname,'' AS deliveryname,'' AS address1,'' AS address2,'' AS address3,oh.updatedatetime::date AS shippmentdate,dt.individualcode,dt.cmdtyname,-1 * dt.price AS price,-1 * dt.amount AS amount,-1 * (dt.price*dt.amount) AS totalprice,'商品返品分' AS type ");
    sql.append("FROM ordertbl oh,orderdeliverytbl dl,orderdetailtbl dt ");
    sql.append("WHERE oh.ordercode=dl.ordercode AND oh.ordercode=dt.ordercode AND oh.orderbranchcode=dl.orderbranchcode AND oh.orderbranchcode=dt.orderbranchcode ");
    sql.append("AND dl.deliverycode=dt.deliverycode AND oh.enabledflg=1 AND oh.status=2 AND dt.shippmentdate IS NOT NULL ");
    sql.append("AND oh.custcode=").append(SIDBUtil.SQL2Str(this.getCustCode()," "));
    sql.append("AND oh.updatedatetime>=").append(SIDBUtil.SQL2Str(this.getSalesFrom()," "));
    sql.append("AND oh.updatedatetime<").append(SIDBUtil.SQL2Str(this.getSalesTo2()," "));
    sql.append("ORDER BY shippmentdate,ordercode,type ");
    
    return sql.toString();
  }
  
  public String getPurchaseCommentSql() {
    StringBuffer sql = new StringBuffer();
    
    sql.append("SELECT p.initdatetime,p.updatedatetime,p.custcode,c.custname,CASE p.memostatus::text ");
    for (int i=0;i<SIFlagConf.SIFLAG_PURCHASECOMMENT_STATUS_FLG_NAME.length; i++){
      sql.append("WHEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_PURCHASECOMMENT_STATUS_FLG_NAME[i][1]," "));
      sql.append("THEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_PURCHASECOMMENT_STATUS_FLG_NAME[i][0]," "));
    }
    sql.append("ELSE '未分類' END AS memostatus,p.username,p.lastupdateusername,p.comment ");
    sql.append("FROM purchasecommenttbl p,custtbl c ");
    sql.append("WHERE p.custcode=c.custcode ");
    sql.append("AND p.initdatetime::date>=").append(SIDBUtil.SQL2Str(getSalesDateFrom()," "));
    sql.append("AND p.initdatetime::date<=").append(SIDBUtil.SQL2Str(getSalesDateTo()," "));
    if (SIUtil.isNotNull(this.getMemoStatus())&&!"all".equals(this.getMemoStatus())) {
      sql.append("AND p.memostatus = ").append(SIDBUtil.SQL2Str(this.getMemoStatus(), " "));
    }else if (SIUtil.isNull(this.getMemoStatus())) {
      sql.append("AND p.memostatus IS NULL ");
    }
    if (SIUtil.isNotNull(this.getBranchCode())) {
      sql.append("AND p.username IN (SELECT chargename FROM chargetbl WHERE enableflg=1 AND belongingbranchcode =").append(SIDBUtil.SQL2Str(this.getBranchCode(), " UNION SELECT 'ご意見BOX') "));
    }
    if (SIUtil.isNotNull(this.getCustCode())) {
      sql.append("AND p.custcode = ").append(SIDBUtil.SQL2Str(this.getCustCode(), " "));
    }
    if (SIUtil.isNotNull(this.getOpinionFlg())&&"0".equals(this.getOpinionFlg())) {
      sql.append("AND p.username <> 'ご意見BOX' ");
    }
    
    return sql.toString();
  }
  
  public String getConditionOrderRoute(){
    StringBuffer sql = new StringBuffer();
    sql.append(" AND initdate>=").append(SIDBUtil.SQL2Str(getSalesFrom()));
    sql.append(" AND initdate<").append(SIDBUtil.SQL2Str(getSalesTo2()," "));
    return sql.toString();
  }
  
  public String getConditionUriageBase(){
    StringBuffer sql = new StringBuffer();
    sql.append(" AND ship>=").append(SIDBUtil.SQL2Str(getSalesFrom()));
    sql.append(" AND ship<").append(SIDBUtil.SQL2Str(getSalesTo2()," "));
    return sql.toString();
  }
  
  public String getConditionPointRecord(){
    StringBuffer sql = new StringBuffer();
    sql.append(" AND initdatetime>=").append(SIDBUtil.SQL2Str(getSalesFrom()));
    sql.append(" AND initdatetime<").append(SIDBUtil.SQL2Str(getSalesTo2()," "));
    return sql.toString();
  }
  
  public String getConditionFlagUpdateLog(){
    StringBuffer sql = new StringBuffer();
    if (SIUtil.isNotNull(this.getIndividualCode())) {
      sql.append("AND individualcode LIKE '").append(SIDBUtil.SQL2Like(this.getIndividualCode())).append("%' ");
    }
    sql.append(" AND initdatetime>=").append(SIDBUtil.SQL2Str(getSalesFrom()));
    sql.append(" AND initdatetime<").append(SIDBUtil.SQL2Str(getSalesTo2()," "));
    return sql.toString();
  }
  
  public String getConditionOpinionList(){
    StringBuffer sql = new StringBuffer();
    sql.append(" AND initdatetime>=").append(SIDBUtil.SQL2Str(getSalesFrom()));
    sql.append(" AND initdatetime<").append(SIDBUtil.SQL2Str(getSalesTo2()," "));
    return sql.toString();
  }
  
  public String getConditionEgPoint(){
    StringBuffer sql = new StringBuffer();
    sql.append(" AND shippmentdate>=").append(SIDBUtil.SQL2Str(getSalesFrom()));
    sql.append(" AND shippmentdate<").append(SIDBUtil.SQL2Str(getSalesTo2()," "));
    return sql.toString();
  }
  
  public String getConditionEyelashSales(){
    StringBuffer sql = new StringBuffer();
    sql.append(" AND shippmentdate>=").append(SIDBUtil.SQL2Str(getSalesFrom()));
    sql.append(" AND shippmentdate<").append(SIDBUtil.SQL2Str(getSalesTo2()," "));
    return sql.toString();
  }
  
  private String getCtgryHead(){
    String res = "";
    for (int i=0;i<SIConfig.CATEGORY.length;i++) {
      if (SIConfig.CATEGORY[i][0].equals(motherCtgry)) {
        res = SIConfig.CATEGORY[i][1];
        break;
      }
    }
    return res;
  }
  
  public String getDisableFlg() {
    return disableFlg;
  }
  
  public void setDisableFlg(String disableFlg) {
    this.disableFlg = disableFlg;
  }
  
  public String getExecBase() {
    return execBase;
  }
  
  public void setExecBase(String execBase) {
    if (SIUtil.isNull(execBase)) execBase="0";
    this.execBase = execBase;
  }
  
  public String getIndividualCode() {
    return individualCode;
  }
  
  public void setIndividualCode(String individualCode) {
    this.individualCode = individualCode;
  }
  
  public String getMakerName() {
    return makerName;
  }
  
  public void setMakerName(String makerName) {
    this.makerName = makerName;
  }
  
  public String getSalesMonthFrom() {
    return salesMonthFrom;
  }
  
  public void setSalesMonthFrom(String monthFrom) {
    this.salesMonthFrom = monthFrom;
  }
  
  public String getSalesMonthTo() {
    return salesMonthTo;
  }
  
  public void setSalesMonthTo(String monthTo) {
    this.salesMonthTo = monthTo;
  }
  
  public String getSalesDayTo() {
    return salesDayTo;
  }
  
  public void setSalesDayTo(String dayTo) {
    this.salesDayTo = dayTo;
  }
  
  public String getSalesDayFrom() {
    return salesDayFrom;
  }
  
  public void setSalesDayFrom(String dayFrom) {
    this.salesDayFrom = dayFrom;
  }
  
  public String getSalesFrom() {
    return this.salesYearFrom + "/" + this.salesMonthFrom + "/" + "01";
  }
  
  public String getSalesDateFrom() {
    if(SIUtil.isNull(this.salesYearFrom)&&SIUtil.isNull(this.salesMonthFrom)&&SIUtil.isNull(this.salesDayFrom)) return "";
    return this.salesYearFrom + "/" + this.salesMonthFrom + "/" + this.salesDayFrom;
  }
  
  public String getSalesFrom2() {//チェック用
    SIDateTime date = new SIDateTime();
    try{
      date = new SIDateTime(this.salesYearFrom + "/" + this.salesMonthFrom + "/" + "01",SIConfig.SIDATE_FORMAT0);
      date.addMonth(2);
    }catch(Exception e){}
    return date.getFullDate();
  }
  
  public String getSalesTo() {
    return this.salesYearTo + "/" + this.salesMonthTo + "/" + "01";
  }
  
  public String getSalesDateTo() {
    if(SIUtil.isNull(this.salesYearTo)&&SIUtil.isNull(this.salesMonthTo)&&SIUtil.isNull(this.salesDayTo)) return "";
    return this.salesYearTo + "/" + this.salesMonthTo + "/" + this.salesDayTo;
  }
  
  public String getSalesTo2() {//計算用
    SIDateTime date = new SIDateTime();
    try{
      date = new SIDateTime(this.salesYearTo + "/" + this.salesMonthTo + "/" + "01",SIConfig.SIDATE_FORMAT0);
      date.addMonth(1);
    }catch(Exception e){}
    return date.getFullDate();
  }
  
  public String getMotherCtgry() {
    return motherCtgry;
  }
  
  public void setMotherCtgry(String motherCtgry) {
    this.motherCtgry = motherCtgry;
  }
  
  public String getSalesYearFrom() {
    return salesYearFrom;
  }
  
  public void setSalesYearFrom(String yearFrom) {
    this.salesYearFrom = yearFrom;
  }
  
  public String getSalesYearTo() {
    return salesYearTo;
  }
  
  public void setSalesYearTo(String yearTo) {
    this.salesYearTo = yearTo;
  }
  
  public String getBranchCode() {
    return branchCode;
  }
  
  public void setBranchCode(String branchCode) {
    this.branchCode = branchCode;
  }
  
  public String getExecSpan() {
    return execSpan;
  }
  
  public void setExecSpan(String execSpan) {
    this.execSpan = execSpan;
  }
  
  public String getMakerCode() {
    return makerCode;
  }
  
  public void setMakerCode(String makerCode) {
    this.makerCode = makerCode;
  }
  
  public String getAmountFlg() {
    return amountFlg;
  }
  
  public void setAmountFlg(String amountFlg) {
    if (SIUtil.isNull(amountFlg)) amountFlg = "";
    this.amountFlg = amountFlg;
  }
  
  public String getCmdtyName() {
    return cmdtyName;
  }
  
  public void setCmdtyName(String cmdtyName) {
    if (SIUtil.isNull(cmdtyName)) cmdtyName = "";
    this.cmdtyName = cmdtyName;
  }
  
  public String getStockCode() {
    return stockCode;
  }
  
  public void setStockCode(String stockCode) {
    if (SIUtil.isNull(stockCode)) stockCode = "";
    this.stockCode = stockCode;
  }
  
  public String getOrderCode() {
    return orderCode;
  }
  
  public void setOrderCode(String orderCode) {
    if (SIUtil.isNull(orderCode)) orderCode = "";
    this.orderCode = orderCode;
  }
  
  public String getHacchuCode() {
    return hacchuCode;
  }
  
  public void setHacchuCode(String hacchuCode) {
    if (SIUtil.isNull(hacchuCode)) hacchuCode = "";
    this.hacchuCode = hacchuCode;
  }
  
  public String getCheckFlg() {
    return checkFlg;
  }
  
  public void setCheckFlg(String checkFlg) {
    if (SIUtil.isNull(checkFlg)) checkFlg = "0";
    this.checkFlg = checkFlg;
  }
  
  public String getLastUpdate() {
    return lastUpdate;
  }
  
  public String[] getUsednewFlgC() {
    return usednewFlgC;
  }
  
  public void setLastUpdate(String lastUpdate) {
    if (SIUtil.isNull(lastUpdate)) lastUpdate = "";
    this.lastUpdate = lastUpdate;
  }
  
  public void setUsednewFlgC(String[] usednewFlgC) {
    if (usednewFlgC == null) usednewFlgC = new String[SIFlagConf.SIFLAG_USED_NEW_FLG_LIST_NAME.length];
    this.usednewFlgC = usednewFlgC;
  }
  
  public String[] getDisableFlgC() {
    return disableFlgC;
  }
  
  public void setDisableFlgC(String[] disableFlgC) {
    if (disableFlgC == null) disableFlgC = new String[SIFlagConf.SIFLAG_DISABLE_FLG_NAME.length];
    this.disableFlgC = disableFlgC;
  }
  
  public String getStockName() {
    return stockName;
  }
  
  public void setStockName(String stockName) {
    if (SIUtil.isNull(stockName)) stockName = "";
    this.stockName = stockName;
  }
  
  public String[] getSalesSituationC() {
    return salesSituationC;
  }
  
  public void setSalesSituationC(String[] salesSituationC) {
    if (salesSituationC == null) salesSituationC = new String[0];
    this.salesSituationC = salesSituationC;
  }
  
  public String getDeliveryType() {
    return deliveryType;
  }
  
  public void setDeliveryType(String deliveryType) {
    if (SIUtil.isNull(deliveryType)) deliveryType = "";
    this.deliveryType = deliveryType;
  }
  
  public String getCustCode() {
    return custCode;
  }
  
  public void setCustCode(String custCode) {
    if (SIUtil.isNull(custCode)) custCode = "";
    this.custCode = custCode;
  }
  
  public String getMemoStatus() {
    return memoStatus;
  }
  
  public void setMemoStatus(String memoStatus) {
    if (SIUtil.isNull(memoStatus)) memoStatus = "";
    this.memoStatus = memoStatus;
  }
  
  public String getOpinionFlg() {
    return opinionFlg;
  }
  
  public void setOpinionFlg(String opinionFlg) {
    if (SIUtil.isNull(opinionFlg)) opinionFlg = "";
    this.opinionFlg = opinionFlg;
  }
}