package jp.co.sint.beans.mallmgr;

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

import javax.servlet.http.HttpServletRequest;

import jp.co.sint.basic.SIBasic;
import jp.co.sint.basic.SINameValue;
import jp.co.sint.basic.SISalesStatusData;
import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIFlagConf;
import jp.co.sint.database.SIDBAccessException;
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.SICustomErrors;
import jp.co.sint.tools.SIDateTime;
import jp.co.sint.tools.SIURLParameter;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;

public class UISalesStatus extends SIBasic {
  
  //ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  // 利益按分レート
  private final String USED_RATE  = "0.5";// 50%出荷支店に按分
  private final String USED_RATE2 = "0.6";// 60%出荷支店に按分（2009年8月以降のU,A商品）
  private final String NEW_RATE   = "0.2";// 20%出荷支店に按分
  
  //特別対応用代替支店データ
  private final String[] SP_STOCKCODE = {"1480","1481"};
  private final String[] SP_BRANCHCODE = {"9","9"};
  private final String[] SP_BRANCHNAME = {"福岡(WV1)","福岡(WV2)"};
  
  // 売上計上対象支店
  private final String[] BRANCH = {
    "1"// 東京
   ,"2"// さいたま
   ,"3"// 横浜
   ,"4"// 仙台
   ,"5"// 金沢
   ,"6"// 名古屋
   ,"7"// 大阪
   ,"8"// 広島
   ,"9"// 福岡
   ,"10"// 神戸
   ,"11"// 札幌
   ,"12"// 千葉
   ,"13"// 物流倉庫E
   ,"16"// BA
   ,"17"// EG東京
   ,"19"// 楽天
  };
  
  // 内部取引対象支店
  private final String[] BRANCH2 = {
   "1"// 東京
   ,"2"// さいたま
   ,"3"// 横浜
   ,"4"// 仙台
   ,"5"// 金沢
   ,"6"// 名古屋
   ,"7"// 大阪
   ,"8"// 広島
   ,"9"// 福岡
   ,"10"// 神戸
   ,"11"// 札幌
   ,"12"// 千葉
   //,"13"// 物流倉庫E
   ,"16"// BA
   ,"17"// EG東京
  };
  
  // 売上年
  private String year = "";
  
  // 売上月
  private String month = "";
  
  // 対象日FROM
  private String dayFrom = "";
  
  // 対象日TO
  private String dayTo = "";
  
  // 担当支店
  private String branchCode = "";
  
  // 担当者
  private String chargeCode = "";
  
  // 担当者名
  private String chargeName = "";
  
  // 受注経路
  private String orderRoute = "";
  
  // 通常売上リスト
  private Collection collection1 = new ArrayList();
  
  // 社内売上リスト
  private Collection collection2 = new ArrayList();
  
  // 社内仕入リスト
  private Collection collection3 = new ArrayList();
  
  // 支店名リスト
  private Collection branchNameColl = new ArrayList();
  
  // 通常売上合計
  private String[] sum1 = new String[14];
  
  // 通常売上全国集計
  private String[][] sumAll1 = new String[BRANCH.length][14];
  
  // 社内売上合計
  private String[] sum2 = new String[3];
  
  // 社内売上全国集計
  private String[][] sumAll2 = new String[BRANCH.length][3];
  
  // 社内仕入合計
  private String[] sum3 = new String[3];
  
  // 社内仕入全国集計
  private String[][] sumAll3 = new String[BRANCH.length][3];
  
  // 内部取引支店別集計
  private String[][] sum4 = new String[BRANCH.length][2];
  
  // 部門集計
  private String[][] sum5 = new String[3][6];
  
  // 集計分類
  private int type = 0;
  
  public UISalesStatus(){
    for (int a=0;a<sum1.length;a++) sum1[a] = "0";
    for (int b=0;b<sum2.length;b++) sum2[b] = "0";
    for (int c=0;c<sum3.length;c++) sum3[c] = "0";
    for (int d=0;d<BRANCH.length;d++) {
      sum4[d][0] = "0";
      sum4[d][1] = "0";
    }
    for (int e=0;e<6;e++) {
      sum5[0][e] = "0";
      sum5[1][e] = "0";
      sum5[2][e] = "0";
    }
  }
  public void resetAll(){
    for (int a=0;a<BRANCH.length;a++) {
      for (int b=0;b<sum1.length;b++) {
        sumAll1[a][b] = "0";
      }
    }
    for (int c=0;c<BRANCH.length;c++) {
      for (int d=0;d<sum2.length;d++) {
        sumAll2[c][d] = "0";
      }
    }
    for (int e=0;e<BRANCH.length;e++) {
      for (int f=0;f<sum3.length;f++) {
        sumAll3[e][f] = "0";
      }
    }
  }
  
  //出力用パラメータ設定
  
  public String getYear() {
    return year;
  }
  
  public String getMonth() {
    return month;
  }
  
  public String getDayFrom() {
    return dayFrom;
  }
  
  public String getDayTo() {
    return dayTo;
  }
  
  public String getBranchCode() {
    return branchCode;
  }
  
  public String getBranchName(String branchCode) {
    if (SIUtil.isNull(branchCode)) return "";
    String res = "";
    SINameValue name;
    Iterator branchIte = branchNameColl.iterator();
    while (branchIte.hasNext()){
      name = (SINameValue) branchIte.next();
      if (branchCode.equals(name.getValue())) {
        res = name.getName();
        break;
      }
    }
    return res;
  }
  
  public String getChargeCode() {
    return chargeCode;
  }
  
  public String getChargeName() {
    return chargeName;
  }
  
  public String getOrderRoute() {
    return orderRoute;
  }
  
  public void setYear(String year) {
    this.year = year;
  }
  
  public void setMonth(String month) {
    this.month = month;
  }
  
  public void setDayFrom(String dayFrom) {
    this.dayFrom = dayFrom;
  }
  
  public void setDayTo(String dayTo) {
    this.dayTo = dayTo;
  }
  
  public void setBranchCode(String branchCode) {
    this.branchCode = branchCode;
  }
  
  public void setChargeCode(String chargeCode) {
    this.chargeCode = chargeCode;
  }
  
  public void setChargeName(String chargeName) {
    this.chargeName = chargeName;
  }
  
  public void setOrderRoute(String orderRoute) {
    this.orderRoute = orderRoute;
  }
  
  public String[] getBRANCH() {
    return BRANCH;
  }
  
  public Collection getCollection1() {
    return collection1;
  }
  
  public Collection getCollection2() {
    return collection2;
  }
  
  public Collection getCollection3() {
    return collection3;
  }
  
  public String[] getSum1() {
    return sum1;
  }
  
  public String[][] getSumAll1() {
    return sumAll1;
  }
  
  public String[] getSum2() {
    return sum2;
  }
  
  public String[][] getSumAll2() {
    return sumAll2;
  }
  
  public String[] getSum3() {
    return sum3;
  }
  
  public String[][] getSumAll3() {
    return sumAll3;
  }
  
  public String[][] getSum4() {
    return sum4;
  }
  
  public String[][] getSum5() {
    return sum5;
  }
  
  public int getType() {
    return type;
  }
  public void setType(int type) {
    this.type = type;
  }
  
  public String[] getTotalOfSumAll1(){
    String[] val = new String[12];
    for (int i=0;i<12;i++){
      String price = "0";
      for (int j=0;j<BRANCH.length;j++){
        price = SIUtil.add(price, sumAll1[j][i]);
      }
      val[i] = price;
    }
    return val;
  }
  
  public String[] getTotalOfSumAll2(){
    String[] val = new String[3];
    for (int i=0;i<3;i++){
      String price = "0";
      for (int j=0;j<BRANCH.length;j++){
        price = SIUtil.add(price, sumAll2[j][i]);
      }
      val[i] = price;
    }
    return val;
  }
  
  public String[] getTotalOfSumAll3(){
    String[] val = new String[3];
    for (int i=0;i<3;i++){
      String price = "0";
      for (int j=0;j<BRANCH.length;j++){
        price = SIUtil.add(price, sumAll3[j][i]);
      }
      val[i] = price;
    }
    return val;
  }
  
  public String getTotalOfBranchSell() {
    String price = "0";
    for (int i=0;i<BRANCH.length;i++){
      price = SIUtil.add(price, sum4[i][0]);
    }
    return price;
  }
  
  public String getTotalOfBranchPurchase() {
    String price = "0";
    for (int i=0;i<BRANCH.length;i++){
      price = SIUtil.add(price, sum4[i][1]);
    }
    return price;
  }
  
  public String getTotalOfBiyoSegment1(){
    String price = "0";
    price = SIUtil.add(price, sum5[0][0]);
    price = SIUtil.add(price, sum5[0][1]);
    price = SIUtil.add(price, sum5[0][2]);
    return price;
  }
  
  public String getTotalOfBiyoSegment2(){
    String price = "0";
    price = SIUtil.add(price, sum5[1][0]);
    price = SIUtil.add(price, sum5[1][1]);
    price = SIUtil.add(price, sum5[1][2]);
    return price;
  }
  
  public String getTotalOfBiyoSegment3(){
    String price = "0";
    price = SIUtil.add(price, sum5[2][0]);
    price = SIUtil.add(price, sum5[2][1]);
    price = SIUtil.add(price, sum5[2][2]);
    return price;
  }
  
  public String getTotalOfEstheSegment1(){
    String price = "0";
    price = SIUtil.add(price, sum5[0][3]);
    price = SIUtil.add(price, sum5[0][4]);
    price = SIUtil.add(price, sum5[0][5]);
    return price;
  }
  
  public String getTotalOfEstheSegment2(){
    String price = "0";
    price = SIUtil.add(price, sum5[1][3]);
    price = SIUtil.add(price, sum5[1][4]);
    price = SIUtil.add(price, sum5[1][5]);
    return price;
  }
  
  public String getTotalOfEstheSegment3(){
    String price = "0";
    price = SIUtil.add(price, sum5[2][3]);
    price = SIUtil.add(price, sum5[2][4]);
    price = SIUtil.add(price, sum5[2][5]);
    return price;
  }
  
  public String getFee() {
    return sum1[12];
  }
  
  public String getDiscount() {
    return sum1[13];
  }
  
  public String getTotalOfSegment1() {
    String price = "0";
    price = SIUtil.add(price, sum5[0][0]);
    price = SIUtil.add(price, sum5[0][1]);
    price = SIUtil.add(price, sum5[0][2]);
    price = SIUtil.add(price, sum5[0][3]);
    price = SIUtil.add(price, sum5[0][4]);
    price = SIUtil.add(price, sum5[0][5]);
    price = SIUtil.add(price, getFee());
    price = SIUtil.add(price, getDiscount());
    return price;
  }
  
  public String getTotalOfSegment2() {
    String price = "0";
    price = SIUtil.add(price, sum5[1][0]);
    price = SIUtil.add(price, sum5[1][1]);
    price = SIUtil.add(price, sum5[1][2]);
    price = SIUtil.add(price, sum5[1][3]);
    price = SIUtil.add(price, sum5[1][4]);
    price = SIUtil.add(price, sum5[1][5]);
    return price;
  }
  
  public String getTotalOfSegment3() {
    String price = "0";
    price = SIUtil.add(price, sum5[2][0]);
    price = SIUtil.add(price, sum5[2][1]);
    price = SIUtil.add(price, sum5[2][2]);
    price = SIUtil.add(price, sum5[2][3]);
    price = SIUtil.add(price, sum5[2][4]);
    price = SIUtil.add(price, sum5[2][5]);
    return price;
  }
  
  private String getSeasonFrom(){
    if (SIUtil.isNotNull(this.dayFrom)) return year + "/" + month + "/" + dayFrom;
    else return year + "/" + month + "/01";
  }
  
  private String getSeasonTo(){
    if (SIUtil.isNotNull(this.dayTo)) return year + "/" + month + "/" + dayTo;
    String resDate = "";
    try {
      SIDateTime lDate = new SIDateTime(year + "/" + month + "/01",SIConfig.SIDATE_FORMAT);
      resDate = year + "/" + month + "/" + lDate.getLasyDay();
    }catch(ParseException e) {
      e.printStackTrace();
      resDate = year + "/" + month + "/01";
    }
    return resDate;
  }
  
  private boolean isOldRate(){
    boolean res = false;
    try{
      res = SICheckUtil.dateLess(year + "/" + month + "/01", "2009/08/01");
    }catch(Exception e){
      e.printStackTrace();
      res=false;
    }
    return res;
  }
  
  private void addSum1(SISalesStatusData SSD){
    try{
      sum1[0] = SIUtil.add(sum1[0], SSD.getTotalOfSellIncTax());
      sum1[1] = SIUtil.add(sum1[1], SSD.getFeeIncTax());
      sum1[2] = SIUtil.add(sum1[2], SSD.getSumOfDiscountIncTax());
      sum1[3] = SIUtil.add(sum1[3], SSD.getTotalOfPriceIncTax());
      sum1[4] = SIUtil.add(sum1[4], SSD.getTotalOfCostIncTax());
      sum1[5] = SIUtil.add(sum1[5], SSD.getTotalOfGainIncTax());
      sum1[6] = SIUtil.add(sum1[6], SSD.getTotalOfPrice());
      sum1[7] = SIUtil.add(sum1[7], SSD.getTotalOfCost());
      sum1[8] = SIUtil.add(sum1[8], SSD.getTotalOfGain());
      sum1[9] = SIUtil.add(sum1[9], SSD.getDeliveryFee());
      sum1[10] = SIUtil.add(sum1[10], SSD.getSumByPoint());
      sum1[11] = SIUtil.add(sum1[11], SSD.getAmount());
      sum1[12] = SIUtil.add(sum1[12], SSD.getFee());
      sum1[13] = SIUtil.add(sum1[13], SSD.getSumOfDiscount());
      addSegment1(SSD.getTotalOfPrice(),SSD.getSegment());
      addSegment2(SSD.getTotalOfCost(),SSD.getSegment());
    }catch (Exception e){}
  }
  
  private void addSumAll1(SISalesStatusData SSD){
    try{
      int bc = getBranchIndex(SSD.getOrderBranchCode());
      
      sumAll1[bc][0] = SIUtil.add(sumAll1[bc][0], SSD.getTotalOfSellIncTax());
      sumAll1[bc][1] = SIUtil.add(sumAll1[bc][1], SSD.getFeeIncTax());
      sumAll1[bc][2] = SIUtil.add(sumAll1[bc][2], SSD.getSumOfDiscountIncTax());
      sumAll1[bc][3] = SIUtil.add(sumAll1[bc][3], SSD.getTotalOfPriceIncTax());
      sumAll1[bc][4] = SIUtil.add(sumAll1[bc][4], SSD.getTotalOfCostIncTax());
      sumAll1[bc][5] = SIUtil.add(sumAll1[bc][5], SSD.getTotalOfGainIncTax());
      sumAll1[bc][6] = SIUtil.add(sumAll1[bc][6], SSD.getTotalOfPrice());
      sumAll1[bc][7] = SIUtil.add(sumAll1[bc][7], SSD.getTotalOfCost());
      sumAll1[bc][8] = SIUtil.add(sumAll1[bc][8], SSD.getTotalOfGain());
      sumAll1[bc][9] = SIUtil.add(sumAll1[bc][9], SSD.getDeliveryFee());
      sumAll1[bc][10] = SIUtil.add(sumAll1[bc][10], SSD.getSumByPoint());
      sumAll1[bc][11] = SIUtil.add(sumAll1[bc][11], SSD.getAmount());
      sum1[12] = SIUtil.add(sum1[12], SSD.getFee());
      sum1[13] = SIUtil.add(sum1[13], SSD.getSumOfDiscount());
      addSegment1(SSD.getTotalOfPrice(),SSD.getSegment());
      addSegment2(SSD.getTotalOfCost(),SSD.getSegment());
    }catch (Exception e){}
  }
  
  private void addSum2(SISalesStatusData SSD){
    try{
      // 粗利税込×按分レート
      String price1 = SIUtil.multi(SSD.getTotalOfGainIncTax(), SSD.getDivideRate());
      if (SSD.getOrderBranchCode().equals("17")) price1 = SIUtil.div(SIUtil.multi(price1,"10"),"3");//EG補正（÷0.3）
      // 粗利税抜×按分レート
      String price2 = SIUtil.multi(SSD.getTotalOfGain(), SSD.getDivideRate());
      if (SSD.getOrderBranchCode().equals("17")) price2 = SIUtil.div(SIUtil.multi(price2,"10"),"3");//EG補正（÷0.3）
      sum2[0] = SIUtil.add(sum2[0], price1);
      sum2[1] = SIUtil.add(sum2[1], price2);
      sum2[2] = SIUtil.add(sum2[2], SSD.getAmount());
      addBranch1(price2,SSD.getOrderBranchCode());
      addSegment3(price2,SSD.getSegment());
    }catch (Exception e){}
  }
  
  private void addSumAll2(SISalesStatusData SSD){
    try{
      // 粗利税込×按分レート
      String price1 = SIUtil.multi(SSD.getTotalOfGainIncTax(), SSD.getDivideRate());
      if (SSD.getOrderBranchCode().equals("17")) price1 = SIUtil.div(SIUtil.multi(price1,"10"),"3");//EG補正（÷0.3）
      // 粗利税抜×按分レート
      String price2 = SIUtil.multi(SSD.getTotalOfGain(), SSD.getDivideRate());
      if (SSD.getOrderBranchCode().equals("17")) price2 = SIUtil.div(SIUtil.multi(price2,"10"),"3");//EG補正（÷0.3）
      
      int bc = getBranchIndex(SSD.getShipBranchCode());// 社内売上を出荷支店ごとに集計
      
      sumAll2[bc][0] = SIUtil.add(sumAll2[bc][0], price1);
      sumAll2[bc][1] = SIUtil.add(sumAll2[bc][1], price2);
      sumAll2[bc][2] = SIUtil.add(sumAll2[bc][2], SSD.getAmount());
      addBranch1(price2,SSD.getShipBranchCode());
      addSegment3(price2,SSD.getSegment());
    }catch (Exception e){}
  }
  
  private void addSum3(SISalesStatusData SSD){
    try{
      // 粗利税込×按分レート
      String price1 = SIUtil.multi(SSD.getTotalOfGainIncTax(), SSD.getDivideRate());
      if (SSD.getOrderBranchCode().equals("17")) price1 = SIUtil.div(SIUtil.multi(price1,"10"),"3");//EG補正（÷0.3）
      // 粗利税抜×按分レート
      String price2 = SIUtil.multi(SSD.getTotalOfGain(), SSD.getDivideRate());
      if (SSD.getOrderBranchCode().equals("17")) price2 = SIUtil.div(SIUtil.multi(price2,"10"),"3");//EG補正（÷0.3）
      sum3[0] = SIUtil.add(sum3[0], price1);
      sum3[1] = SIUtil.add(sum3[1], price2);
      sum3[2] = SIUtil.add(sum3[2], SSD.getAmount());
      addBranch2(price2,SSD.getShipBranchCode());
      addSegment2(price2,SSD.getSegment());
    }catch (Exception e){}
  }
  
  private void addSumAll3(SISalesStatusData SSD){
    try{
      // 粗利税込×按分レート
      String price1 = SIUtil.multi(SSD.getTotalOfGainIncTax(), SSD.getDivideRate());
      if (SSD.getOrderBranchCode().equals("17")) price1 = SIUtil.div(SIUtil.multi(price1,"10"),"3");//EG補正（÷0.3）
      // 粗利税抜×按分レート
      String price2 = SIUtil.multi(SSD.getTotalOfGain(), SSD.getDivideRate());
      if (SSD.getOrderBranchCode().equals("17")) price2 = SIUtil.div(SIUtil.multi(price2,"10"),"3");//EG補正（÷0.3）
      
      int bc = getBranchIndex(SSD.getOrderBranchCode());// 社内仕入を受注支店ごとに集計
      
      sumAll3[bc][0] = SIUtil.add(sumAll3[bc][0], price1);
      sumAll3[bc][1] = SIUtil.add(sumAll3[bc][1], price2);
      sumAll3[bc][2] = SIUtil.add(sumAll3[bc][2], SSD.getAmount());
      addBranch2(price2,SSD.getOrderBranchCode());
      addSegment2(price2,SSD.getSegment());
    }catch (Exception e){}
  }
  
  private void addSegment1(String value,String code){
    addSegment(value,code,0);
  }
  
  private void addSegment2(String value,String code){
    addSegment(value,code,1);
  }
  
  private void addSegment3(String value,String code){
    addSegment(value,code,2);
  }
  
  private void addSegment(String value,String code,int index){
    if(type==0){
      if ("UD".equals(code)) sum5[index][0] = SIUtil.add(sum5[index][0], value);
      else if ("NB".equals(code)) sum5[index][1] = SIUtil.add(sum5[index][1], value);
      else if ("PB".equals(code)) sum5[index][2] = SIUtil.add(sum5[index][2], value);
      else if ("V".equals(code)) sum5[index][3] = SIUtil.add(sum5[index][3], value);
      else if ("H".equals(code)) sum5[index][3] = SIUtil.add(sum5[index][3], value);
      else if ("W".equals(code)) sum5[index][4] = SIUtil.add(sum5[index][4], value);
      else if ("L".equals(code)) sum5[index][4] = SIUtil.add(sum5[index][4], value);
    }else{
      if ("UD".equals(code)) sum5[index][0] = SIUtil.add(sum5[index][0], value);
      else if ("NB".equals(code)) sum5[index][1] = SIUtil.add(sum5[index][1], value);
      else if ("PB".equals(code)) sum5[index][2] = SIUtil.add(sum5[index][2], value);
      else if ("V".equals(code)) sum5[index][3] = SIUtil.add(sum5[index][3], value);
      else if ("H".equals(code)) sum5[index][3] = SIUtil.add(sum5[index][3], value);
      else if ("L".equals(code)) sum5[index][4] = SIUtil.add(sum5[index][4], value);
      else if ("N".equals(code)) sum5[index][5] = SIUtil.add(sum5[index][5], value);
      else if ("W".equals(code)) sum5[index][5] = SIUtil.add(sum5[index][5], value);
    }
  }
  
  private void addBranch1(String value,String code){
    addBranch(value,code,0);
  }
  
  private void addBranch2(String value,String code){
    addBranch(value,code,1);
  }
  
  private void addBranch(String value,String code,int index){
    for (int i=0;i<BRANCH.length;i++){
      if (BRANCH[i].equals(code)) {
        sum4[i][index] = SIUtil.add(sum4[i][index], value);
        break;
      }
    }
  }
  
  //内部取引対象かどうか？
  private boolean isBranch2(String code){
    boolean res = false;
    for (int i=0;i<BRANCH2.length;i++){
      if (BRANCH2[i].equals(code)) {
        res = true;
        break;
      }
    }
    return res;
  }
  
  private int getBranchIndex(String code){
    int index = 0;
    for (int i=0;i<BRANCH.length;i++){
      if (BRANCH[i].equals(code)) {
        index = i;
        break;
      }
    }
    return index;
  }
  
  /**
   * <b>init</b>
   * 入力したデータを基づいて、このbeansを設定します。
   * @param request クライアントからリクエスト
   * @param lUrlParam
   * @return なし
   * @throws なし
   */
  public void init(SIURLParameter lUrlParam){
    this.setBranchCode((String) lUrlParam.getParam("branchCode"));
    this.setChargeCode((String) lUrlParam.getParam("chargeCode"));
    this.setYear((String) lUrlParam.getParam("year"));
    this.setMonth((String) lUrlParam.getParam("month"));
    this.setDayFrom((String) lUrlParam.getParam("dayFrom"));
    this.setDayTo((String) lUrlParam.getParam("dayTo"));
    this.setOrderRoute((String) lUrlParam.getParam("orderRoute"));
  }
  
  public void validate(HttpServletRequest lRequest,SICustomErrors errors){
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    if (errors == null) errors=new SICustomErrors();
    
    SICheckValid.checkValid(errors, "担当支店", this.getBranchCode(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
    SICheckValid.checkValid(errors, "売上年", this.getYear(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
    SICheckValid.checkValid(errors, "売上月", this.getMonth(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
    SICheckValid.checkValid(errors, "売上日From", this.getSeasonFrom(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
    SICheckValid.checkValid(errors, "売上日To", this.getSeasonTo(), SICheckDataConf.SICHECK_DATA_DATE_TYPE);
    try {
      if (!SICheckUtil.dateEqual(this.getSeasonFrom(), this.getSeasonTo())) {
        SICheckValid.checkValid(errors, "売上日From", "売上日To", this.getSeasonFrom(), this.getSeasonTo(), SICheckDataConf.SICHECK_DATA_DATE_LESS_TYPE);
      }
    } catch (Exception e) {}
    
    if (!errors.isEmpty()) lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY,errors);
  }
  
  public void execute(Connection lConnection){
    try{
      SISalesStatusData SSD;
      collection1 = new ArrayList();
      collection2 = new ArrayList();
      collection3 = new ArrayList();
      
      log.debug("salesstatusSQL:"+getSQL());
      Statement lStatement = lConnection.createStatement();
      ResultSet lResultSet = lStatement.executeQuery(getSQL());
      
      while(lResultSet.next()) {
        SSD = new SISalesStatusData();
        SSD.setShippmentDate(lResultSet.getString("shippmentdate"));
        SSD.setInitDateTime(lResultSet.getString("initdatetime"));
        SSD.setShipBranchName(lResultSet.getString("shipbranch"));
        SSD.setOrderBranchName(lResultSet.getString("branchname"));
        SSD.setChargeName(lResultSet.getString("chargeName"));
        SSD.setOrderCode(lResultSet.getString("orderCode"));
        SSD.setCustName(lResultSet.getString("custName"));
        SSD.setIndividualCode(lResultSet.getString("individualCode"));
        SSD.setCmdtyName(lResultSet.getString("cmdtyName"));
        SSD.setUsedNewFlg(lResultSet.getString("usedNewFlg"));
        SSD.setOrderType(lResultSet.getString("orderType"));
        SSD.setAmount(lResultSet.getString("amount"));
        SSD.setUnitPriceIncTax(lResultSet.getString("price"));
        SSD.setTotalOfSellIncTax(lResultSet.getString("totalOfSellIncTax"));
        SSD.setDeliveryFee(lResultSet.getString("deliveryFee"));
        SSD.setFeeIncTax(lResultSet.getString("feeIncTax"));
        SSD.setFee(lResultSet.getString("fee"));
        SSD.setSumOfDiscountIncTax(lResultSet.getString("sumOfDiscountIncTax"));
        SSD.setSumOfDiscount(lResultSet.getString("sumOfDiscount"));
        SSD.setSumByPoint(lResultSet.getString("sumByPoint"));
        SSD.setTotalOfPriceIncTax(lResultSet.getString("totalOfPriceIncTax"));
        SSD.setUnitCostIncTax(lResultSet.getString("unitCostIncTax"));
        SSD.setTotalOfCostIncTax(lResultSet.getString("totalOfCostIncTax"));
        SSD.setTotalOfGainIncTax(lResultSet.getString("totalOfGainIncTax"));
        SSD.setTotalOfPrice(lResultSet.getString("totalOfPrice"));
        SSD.setTotalOfCost(lResultSet.getString("totalOfCost"));
        SSD.setTotalOfGain(lResultSet.getString("totalOfGain"));
        SSD.setGainRate(lResultSet.getString("gainRate"));
        SSD.setTaxRate(lResultSet.getString("taxRate"));
        if ("0.5".equals(SSD.getGainRate())){
          String gainRate = "0";
          try {
            gainRate = String.valueOf(Double.parseDouble(SSD.getTotalOfGain()) / Double.parseDouble(SSD.getTotalOfPrice()));
            gainRate = gainRate.substring(0,(gainRate.indexOf(".") + 4));
          } catch(Exception e) {
            gainRate = "0";
          }
          SSD.setGainRate(gainRate);
        }
        SSD.setOrderRoute(lResultSet.getString("orderRoute"));
        SSD.setPaymethodName(lResultSet.getString("paymethodName"));
        //SSD.setRepeat(lResultSet.getString("repeat"));
        SSD.setShipBranchCode(lResultSet.getString("shipBranchCode"));
        SSD.setOrderBranchCode(lResultSet.getString("orderBranchCode"));
        SSD.setDivideRate(lResultSet.getString("divideRate"));
        SSD.setSegment(lResultSet.getString("segment"));
        
        //受注支店が選択支店の場合
        if (this.getBranchCode().equals(SSD.getOrderBranchCode())) {
          collection1.add(SSD);
          addSum1(SSD);
          //受注支店が出荷支店でなく、受注支店と出荷支店が内部取引対象の場合
          if (!this.getBranchCode().equals(SSD.getShipBranchCode()) && isBranch2(SSD.getOrderBranchCode()) && isBranch2(SSD.getShipBranchCode())) {
            collection3.add(SSD);
            addSum3(SSD);
          }
        //出荷支店が選択支店、受注支店と出荷支店が内部取引対象の場合
        } else if (isBranch2(SSD.getOrderBranchCode()) && isBranch2(SSD.getShipBranchCode())){
          collection2.add(SSD);
          addSum2(SSD);
        }
      }
      
      branchNameColl = SIDBUtil.getCollection(lConnection, "SELECT branchname,branchcode FROM branchtbl");
      if (SIUtil.isNotNull(this.getChargeCode())) {
        this.setChargeName(SIDBUtil.getFirstData(lConnection, "SELECT chargename FROM chargetbl WHERE chargecode=" + SIDBUtil.SQL2Str(this.getChargeCode())));
      }
    }catch(SQLException sqle){
    }catch(SIDBAccessException e){}
  }

  
  public void executeAll(Connection lConnection){
    try{
      SISalesStatusData SSD;
      
      log.debug("salesstatusSQL:"+getSQLALL());
      Statement lStatement = lConnection.createStatement();
      ResultSet lResultSet = lStatement.executeQuery(getSQLALL());
      
      while(lResultSet.next()) {
        SSD = new SISalesStatusData();
        //SSD.setShippmentDate(lResultSet.getString("shippmentdate"));
        //SSD.setInitDateTime(lResultSet.getString("initdatetime"));
        //SSD.setShipBranchName(lResultSet.getString("shipbranch"));
        //SSD.setOrderBranchName(lResultSet.getString("branchname"));
        //SSD.setChargeName(lResultSet.getString("chargeName"));
        //SSD.setOrderCode(lResultSet.getString("orderCode"));
        //SSD.setCustName(lResultSet.getString("custName"));
        //SSD.setIndividualCode(lResultSet.getString("individualCode"));
        //SSD.setCmdtyName(lResultSet.getString("cmdtyName"));
        //SSD.setUsedNewFlg(lResultSet.getString("usedNewFlg"));
        //SSD.setOrderType(lResultSet.getString("orderType"));
        SSD.setAmount(lResultSet.getString("amount"));
        SSD.setUnitPriceIncTax(lResultSet.getString("price"));
        SSD.setTotalOfSellIncTax(lResultSet.getString("totalOfSellIncTax"));
        SSD.setDeliveryFee(lResultSet.getString("deliveryFee"));
        SSD.setFeeIncTax(lResultSet.getString("feeIncTax"));
        SSD.setFee(lResultSet.getString("fee"));
        SSD.setSumOfDiscountIncTax(lResultSet.getString("sumOfDiscountIncTax"));
        SSD.setSumOfDiscount(lResultSet.getString("sumOfDiscount"));
        SSD.setSumByPoint(lResultSet.getString("sumByPoint"));
        SSD.setTotalOfPriceIncTax(lResultSet.getString("totalOfPriceIncTax"));
        SSD.setUnitCostIncTax(lResultSet.getString("unitCostIncTax"));
        SSD.setTotalOfCostIncTax(lResultSet.getString("totalOfCostIncTax"));
        SSD.setTotalOfGainIncTax(lResultSet.getString("totalOfGainIncTax"));
        SSD.setTotalOfPrice(lResultSet.getString("totalOfPrice"));
        SSD.setTotalOfCost(lResultSet.getString("totalOfCost"));
        SSD.setTotalOfGain(lResultSet.getString("totalOfGain"));
        SSD.setGainRate(lResultSet.getString("gainRate"));
        SSD.setTaxRate(lResultSet.getString("taxRate"));
        if ("0.5".equals(SSD.getGainRate())){
          String gainRate = "0";
          try {
            gainRate = String.valueOf(Double.parseDouble(SSD.getTotalOfGain()) / Double.parseDouble(SSD.getTotalOfPrice()));
            gainRate = gainRate.substring(0,(gainRate.indexOf(".") + 4));
          } catch(Exception e) {
            gainRate = "0";
          }
          SSD.setGainRate(gainRate);
        }
        SSD.setOrderRoute(lResultSet.getString("orderRoute"));
        //SSD.setPaymethodName(lResultSet.getString("paymethodName"));
        SSD.setShipBranchCode(lResultSet.getString("shipBranchCode"));
        SSD.setOrderBranchCode(lResultSet.getString("orderBranchCode"));
        SSD.setDivideRate(lResultSet.getString("divideRate"));
        SSD.setSegment(lResultSet.getString("segment"));
        
        addSumAll1(SSD);
        if (!SSD.getOrderBranchCode().equals(SSD.getShipBranchCode()) && isBranch2(SSD.getOrderBranchCode()) && isBranch2(SSD.getShipBranchCode())) {
          addSumAll2(SSD);
          addSumAll3(SSD);
        }
      }
      
      branchNameColl = SIDBUtil.getCollection(lConnection, "SELECT branchname,branchcode FROM branchtbl");
      if (SIUtil.isNotNull(this.getChargeCode())) {
        this.setChargeName(SIDBUtil.getFirstData(lConnection, "SELECT chargename FROM chargetbl WHERE chargecode=" + SIDBUtil.SQL2Str(this.getChargeCode())));
      }
    }catch(SQLException sqle){
    }catch(SIDBAccessException e){}
  }
  
  private String getSQL(){
    StringBuffer sqlBuf = new StringBuffer();
    
    // 1.売上明細データ
    sqlBuf.append("SELECT detail.shippmentdate ");
    sqlBuf.append("     , oh.initdatetime::date ");
    sqlBuf.append("     , CASE ");
    for (int num1=0;num1<SP_STOCKCODE.length;num1++) {
      sqlBuf.append("       WHEN detail.branchname IS NULL AND id.stockcode='").append(SP_STOCKCODE[num1]).append("' THEN ").append(SIDBUtil.SQL2Str(SP_BRANCHNAME[num1]));
    }
    sqlBuf.append("       WHEN detail.branchname IS NULL THEN br.branchname ELSE detail.branchname END AS shipbranch ");
    sqlBuf.append("     , br.branchname ");
    sqlBuf.append("     , ch.chargename ");
    sqlBuf.append("     , oh.ordercode ");
    sqlBuf.append("     , cu.custname ");
    sqlBuf.append("     , detail.individualcode ");
    sqlBuf.append("     , detail.cmdtyname ");
    sqlBuf.append("     , CASE id.usednewflg WHEN '0' THEN '中古' WHEN '1' THEN '新品(NB)' WHEN '2' THEN '新古OUTLET' ");
    sqlBuf.append("                          WHEN '3' THEN 'BG認定中古' WHEN '4' THEN '展示品' WHEN '5' THEN '展示品(貸与品)' ");
    sqlBuf.append("                          WHEN '6' THEN 'キャンペーン' WHEN '7' THEN '新品(PB)' ELSE '中古' ");
    sqlBuf.append("       END AS usednewflg ");
    sqlBuf.append("     , '売上' AS ordertype ");
    /*
    sqlBuf.append("     , CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS amount ");
    sqlBuf.append("     , CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price END AS price ");
    sqlBuf.append("     , CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price END * ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofsellinctax ");
    */
    sqlBuf.append("     , detail.amount ");
    sqlBuf.append("     , detail.totalofpriceinctax / detail.amount AS price ");
    sqlBuf.append("     , detail.totalofpriceinctax AS totalofsellinctax ");
    
    sqlBuf.append("     , 0 AS deliveryfee ");
    sqlBuf.append("     , 0 AS feeinctax ");
    sqlBuf.append("     , 0 AS fee ");
    sqlBuf.append("     , 0 AS sumofdiscountinctax ");
    sqlBuf.append("     , 0 AS sumofdiscount ");
    sqlBuf.append("     , 0 AS sumbypoint ");
    /*
    sqlBuf.append("     , CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price END * ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofpriceinctax ");
    sqlBuf.append("     , CASE WHEN id.consigngoodsflg = '1' THEN 0 ELSE (coalesce(dt.purchaseprice,0) + coalesce(dt.processingexpence,0)) END AS unitcostinctax ");
    sqlBuf.append("     , CASE WHEN id.consigngoodsflg = '1' THEN 0 ELSE (coalesce(dt.purchaseprice,0) + coalesce(dt.processingexpence,0)) END *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofcostinctax ");
    sqlBuf.append("     , CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price - (coalesce(dt.purchaseprice,0) + coalesce(dt.processingexpence,0)) END *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofgaininctax ");
    sqlBuf.append("     , TRUNC(CASE WHEN id.consigngoodsflg='1' THEN dt.consigngoodsfee ELSE dt.price END * ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (oh.taxrate / 100))) AS totalofprice ");
    sqlBuf.append("     , CASE WHEN id.consigngoodsflg = '1' THEN 0 ELSE  ");
    sqlBuf.append("       TRUNC((coalesce(dt.purchaseprice,0) + coalesce(dt.processingexpence,0)) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (tax(dt.shippmentdate) / 100))) END AS totalofcost ");
    sqlBuf.append("     , TRUNC(CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price END * ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (oh.taxrate / 100))) ");
    sqlBuf.append("     - CASE WHEN id.consigngoodsflg = '1' THEN 0 ELSE  ");
    sqlBuf.append("       TRUNC((coalesce(dt.purchaseprice,0) + coalesce(dt.processingexpence,0)) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (tax(dt.shippmentdate) / 100))) END AS totalofgain ");
    */
    sqlBuf.append("     , detail.totalofpriceinctax ");
    sqlBuf.append("     , detail.totalofcostinctax / detail.amount AS unitcostinctax ");
    sqlBuf.append("     , detail.totalofcostinctax ");
    sqlBuf.append("     , detail.totalofgaininctax ");
    sqlBuf.append("     , detail.totalofprice ");
    sqlBuf.append("     , detail.totalofcost ");
    sqlBuf.append("     , detail.totalofgain ");
    
    sqlBuf.append("     , CASE WHEN id.consigngoodsflg = '1' THEN 1 WHEN detail.totalofprice = '0' THEN 0 ");
    sqlBuf.append("       ELSE 0.5 END AS gainrate ");
    sqlBuf.append("     , CASE oh.orderroute ");
    for (int i=0;i<SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length;i++) {
      sqlBuf.append(" WHEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][1]));
      sqlBuf.append(" THEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][0]));
    }
    sqlBuf.append(" ELSE ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length-1][0]));
    sqlBuf.append(" END AS orderroute ");
    /*
    sqlBuf.append("     , CASE oh.orderroute WHEN '0' THEN 'EC' WHEN '1' THEN '来店' WHEN '2' THEN '電話' ");
    sqlBuf.append("                          WHEN '3' THEN 'メール' WHEN '4' THEN 'FAX' WHEN '6' THEN '携帯' ");
    sqlBuf.append("                          WHEN '7' THEN '楽天' WHEN '8' THEN 'スマートフォン' WHEN '9' THEN 'イベント' ");
    sqlBuf.append("                          WHEN 'A' THEN 'イベントFAX' WHEN 'B' THEN 'ELG' WHEN 'C' THEN 'アプリ' ELSE 'その他' END AS orderroute ");
    */
    sqlBuf.append("     , oh.paymethodname ");
    sqlBuf.append("     , 1 AS sortkey ");
    sqlBuf.append("     , oh.taxRate ");
    sqlBuf.append("     , CASE ");
    for (int num2=0;num2<SP_STOCKCODE.length;num2++) {
      sqlBuf.append("       WHEN detail.branchcode IS NULL AND id.stockcode='").append(SP_STOCKCODE[num2]).append("' THEN ").append(SIDBUtil.SQL2Str(SP_BRANCHCODE[num2]));
    }
    sqlBuf.append("       WHEN detail.branchcode IS NULL THEN oh.branchcode ELSE detail.branchcode END AS shipbranchcode ");
    sqlBuf.append("     , oh.branchcode AS orderbranchcode ");
    if(isOldRate()) {//200907以前のレート
      sqlBuf.append("     , CASE WHEN id.consigngoodsflg='1' OR id.usednewflg IN ('0','2','3') THEN ").append(USED_RATE);
      sqlBuf.append("            ELSE ").append(NEW_RATE).append(" END AS dividerate ");
    }else{//200908以降のレート
      sqlBuf.append("     , CASE WHEN id.consigngoodsflg='1' THEN ").append(USED_RATE);
      sqlBuf.append("            WHEN id.usednewflg IN ('0','2','3') THEN ").append(USED_RATE2);
      sqlBuf.append("            ELSE ").append(NEW_RATE).append(" END AS dividerate ");
    }
    if(type==0) {
      sqlBuf.append("     , CASE WHEN substr(id.individualcode,0,2) IN ('L','V','W','H') THEN substr(id.individualcode,0,2)  ");
      sqlBuf.append("       WHEN id.usednewflg = '7' THEN 'PB' WHEN id.usednewflg IN ('1','6') THEN 'NB' ELSE 'UD' END AS segment ");
    } else {
      sqlBuf.append("     , CASE WHEN substr(id.individualcode,0,2) IN ('L','V','W','H','N') THEN substr(id.individualcode,0,2)  ");
      sqlBuf.append("       WHEN id.usednewflg = '7' THEN 'PB' WHEN id.usednewflg IN ('1','6') THEN 'NB' ELSE 'UD' END AS segment ");
    }
    /*
    sqlBuf.append("FROM orderdetailtbl dt INNER JOIN individualtbl id ON (dt.individualcode=id.individualcode) ");
    sqlBuf.append("LEFT OUTER JOIN  ");
    sqlBuf.append("(SELECT a.ordercode,a.detailcode,a.individualcode,b.branchname,a.orderallocationnumber,a.branchcode FROM orderallocationinfotbl a,branchtbl b WHERE a.branchcode=b.branchcode) al  ");
    sqlBuf.append("ON dt.ordercode=al.ordercode AND dt.individualcode=al.individualcode AND dt.detailcode=al.detailcode ");
    */
    sqlBuf.append("FROM ordertbl oh,statusdetailbgvw detail INNER JOIN individualtbl id ON (detail.individualcode=id.individualcode) ");
    sqlBuf.append(",custtbl cu,branchtbl br,chargetbl ch ");
    sqlBuf.append("WHERE detail.ordercode = oh.ordercode ");
    sqlBuf.append("  AND (oh.branchcode = ").append(SIDBUtil.SQL2Str(this.getBranchCode()));
    for (int num3=0;num3<SP_STOCKCODE.length;num3++) {
      if (this.getBranchCode().equals(SP_BRANCHCODE[num3])) {
        sqlBuf.append("   OR (detail.branchcode IS NULL AND id.stockcode = ").append(SIDBUtil.SQL2Str(SP_STOCKCODE[num3],")"));
      }
    }
    sqlBuf.append("   OR detail.branchcode = ").append(SIDBUtil.SQL2Str(this.getBranchCode(),")"));
    sqlBuf.append("  AND detail.shippmentdate >= ").append(SIDBUtil.SQL2Str(this.getSeasonFrom()));
    sqlBuf.append("  AND detail.shippmentdate <= ").append(SIDBUtil.SQL2Str(this.getSeasonTo()));
    if(SIUtil.isNotNull(this.getOrderRoute())){
      sqlBuf.append("  AND oh.orderroute = ").append(SIDBUtil.SQL2Str(this.getOrderRoute()));
    }
    if(SIUtil.isNotNull(this.getChargeCode())){
      sqlBuf.append("  AND oh.chargecode = ").append(SIDBUtil.SQL2Str(this.getChargeCode()));
    }
    sqlBuf.append("  AND oh.branchcode = br.branchcode ");
    sqlBuf.append("  AND oh.chargecode = ch.chargecode ");
    sqlBuf.append("  AND oh.custcode = cu.custcode ");
    sqlBuf.append("  AND oh.enabledflg = '1' ");
    
    // 2.訂正赤データ
    sqlBuf.append("UNION ALL ");
    sqlBuf.append("SELECT dc.initdatetime::date AS shippmentdate ");
    sqlBuf.append("     , oh.initdatetime::date ");
    sqlBuf.append("     , CASE ");
    for (int num4=0;num4<SP_STOCKCODE.length;num4++) {
      sqlBuf.append("       WHEN al.branchname IS NULL AND id.stockcode='").append(SP_STOCKCODE[num4]).append("' THEN ").append(SIDBUtil.SQL2Str(SP_BRANCHNAME[num4]));
    }
    sqlBuf.append("       WHEN al.branchname IS NULL THEN br.branchname ELSE al.branchname END AS shipbranch ");
    sqlBuf.append("     , br.branchname ");
    sqlBuf.append("     , ch.chargename ");
    sqlBuf.append("     , dt.ordercode ");
    sqlBuf.append("     , cu.custname ");
    sqlBuf.append("     , dt.individualcode ");
    sqlBuf.append("     , dt.cmdtyname ");
    sqlBuf.append("     , CASE id.usednewflg WHEN '0' THEN '中古' WHEN '1' THEN '新品(NB)' WHEN '2' THEN '新古OUTLET' ");
    sqlBuf.append("                          WHEN '3' THEN 'BG認定中古' WHEN '4' THEN '展示品' WHEN '5' THEN '展示品(貸与品)' ");
    sqlBuf.append("                          WHEN '6' THEN 'キャンペーン' WHEN '7' THEN '新品(PB)' ELSE '中古' ");
    sqlBuf.append("       END AS usednewflg ");
    sqlBuf.append("     , '訂正' AS ordertype ");
    sqlBuf.append("     , CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS amount ");
    sqlBuf.append("     , -1 * dt.price AS price ");
    sqlBuf.append("     , -1 * dt.price *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofsellinctax ");
    sqlBuf.append("     , 0 AS deliveryfee ");
    sqlBuf.append("     , 0 AS feeinctax ");
    sqlBuf.append("     , 0 AS fee ");
    sqlBuf.append("     , 0 AS sumofdiscountinctax ");
    sqlBuf.append("     , 0 AS sumofdiscount ");
    sqlBuf.append("     , 0 AS sumbypoint ");
    sqlBuf.append("     , -1 * dt.price *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofpriceinctax ");
    sqlBuf.append("     , -1 * (coalesce(dc.purchasepriceold,0) + coalesce(dt.processingexpence,0)) AS unitcostinctax ");
    sqlBuf.append("     , -1 * (coalesce(dc.purchasepriceold,0) + coalesce(dt.processingexpence,0)) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofcostinctax ");
    sqlBuf.append("     , -1 * (dt.price - (coalesce(dc.purchasepriceold,0) + coalesce(dt.processingexpence,0))) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofgaininctax ");
    sqlBuf.append("     , -1 * TRUNC(dt.price *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (oh.taxrate / 100))) AS totalofprice ");
    sqlBuf.append("     , -1 * TRUNC((coalesce(dc.purchasepriceold,0) + coalesce(dt.processingexpence,0)) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (tax(dt.shippmentdate) / 100))) AS totalofcost ");
    sqlBuf.append("     , -1 * (TRUNC(dt.price *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (oh.taxrate / 100)))  ");
    sqlBuf.append("     - TRUNC((coalesce(dc.purchasepriceold,0) + coalesce(dt.processingexpence,0)) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (tax(dt.shippmentdate) / 100)))) AS totalofgain ");
    sqlBuf.append("     , CASE WHEN dt.price = 0 THEN 0 ");
    sqlBuf.append("       ELSE 0.5 END AS gainrate ");
    sqlBuf.append("     , CASE oh.orderroute ");
    for (int i=0;i<SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length;i++) {
      sqlBuf.append(" WHEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][1]));
      sqlBuf.append(" THEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][0]));
    }
    sqlBuf.append(" ELSE ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length-1][0]));
    sqlBuf.append(" END AS orderroute ");
    /*
    sqlBuf.append("     , CASE oh.orderroute WHEN '0' THEN 'EC' WHEN '1' THEN '来店' WHEN '2' THEN '電話' ");
    sqlBuf.append("                          WHEN '3' THEN 'メール' WHEN '4' THEN 'FAX' WHEN '6' THEN '携帯' ");
    sqlBuf.append("                          WHEN '7' THEN '楽天' WHEN '8' THEN 'スマートフォン' WHEN '9' THEN 'イベント' ");
    sqlBuf.append("                          WHEN 'A' THEN 'イベントFAX' WHEN 'B' THEN 'ELG' WHEN 'C' THEN 'アプリ' ELSE 'その他' END AS orderroute ");
    */
    sqlBuf.append("     , oh.paymethodname ");
    sqlBuf.append("     , 2 AS sortkey ");
    sqlBuf.append("     , oh.taxRate ");
    sqlBuf.append("     , CASE ");
    for (int num5=0;num5<SP_STOCKCODE.length;num5++) {
      sqlBuf.append("       WHEN al.branchcode IS NULL AND id.stockcode='").append(SP_STOCKCODE[num5]).append("' THEN ").append(SIDBUtil.SQL2Str(SP_BRANCHCODE[num5]));
    }
    sqlBuf.append("       WHEN al.branchcode IS NULL THEN oh.branchcode ELSE al.branchcode END AS shipbranchcode ");
    sqlBuf.append("     , oh.branchcode AS orderbranchcode ");
    if(isOldRate()) {//200907以前のレート
      sqlBuf.append("     , CASE WHEN id.consigngoodsflg='1' OR id.usednewflg IN ('0','2','3') THEN ").append(USED_RATE);
      sqlBuf.append("            ELSE ").append(NEW_RATE).append(" END AS dividerate ");
    }else{//200908以降のレート
      sqlBuf.append("     , CASE WHEN id.consigngoodsflg='1' THEN ").append(USED_RATE);
      sqlBuf.append("            WHEN id.usednewflg IN ('0','2','3') THEN ").append(USED_RATE2);
      sqlBuf.append("            ELSE ").append(NEW_RATE).append(" END AS dividerate ");
    }
    if(type==0) {
      sqlBuf.append("     , CASE WHEN substr(id.individualcode,0,2) IN ('L','V','W','H') THEN substr(id.individualcode,0,2)  ");
      sqlBuf.append("       WHEN id.usednewflg = '7' THEN 'PB' WHEN id.usednewflg IN ('1','6') THEN 'NB' ELSE 'UD' END AS segment ");
    } else {
      sqlBuf.append("     , CASE WHEN substr(id.individualcode,0,2) IN ('L','V','W','H','N') THEN substr(id.individualcode,0,2)  ");
      sqlBuf.append("       WHEN id.usednewflg = '7' THEN 'PB' WHEN id.usednewflg IN ('1','6') THEN 'NB' ELSE 'UD' END AS segment ");
    }
    sqlBuf.append("FROM orderdetailtbl dt  ");
    sqlBuf.append("INNER JOIN individualtbl id  ");
    sqlBuf.append("ON (dt.cmdtycode=id.cmdtycode AND dt.individualcode=id.individualcode) ");
    sqlBuf.append("INNER JOIN orderdetailcosttbl dc  ");
    sqlBuf.append("ON (dt.ordercode=dc.ordercode AND dt.cmdtycode=dc.cmdtycode AND dt.individualcode=dc.individualcode) ");
    sqlBuf.append("LEFT OUTER JOIN  ");
    sqlBuf.append("(SELECT a.ordercode,a.detailcode,a.individualcode,b.branchname,a.orderallocationnumber,a.branchcode FROM orderallocationinfotbl a,branchtbl b WHERE a.branchcode=b.branchcode) al  ");
    sqlBuf.append("ON dt.ordercode=al.ordercode AND dt.individualcode=al.individualcode AND dt.detailcode=al.detailcode ");
    sqlBuf.append(",ordertbl oh ");
    sqlBuf.append(",custtbl cu,branchtbl br,chargetbl ch ");
    sqlBuf.append("WHERE dt.ordercode = oh.ordercode ");
    sqlBuf.append("  AND dt.orderbranchcode = oh.orderbranchcode  ");
    sqlBuf.append("  AND (oh.branchcode = ").append(SIDBUtil.SQL2Str(this.getBranchCode()));
    for (int num6=0;num6<SP_STOCKCODE.length;num6++) {
      if (this.getBranchCode().equals(SP_BRANCHCODE[num6])) {
        sqlBuf.append("   OR (al.branchcode IS NULL AND id.stockcode = ").append(SIDBUtil.SQL2Str(SP_STOCKCODE[num6],")"));
      }
    }
    sqlBuf.append("   OR al.branchcode = ").append(SIDBUtil.SQL2Str(this.getBranchCode(),")"));
    sqlBuf.append("  AND dc.initdatetime::date >= ").append(SIDBUtil.SQL2Str(this.getSeasonFrom()));
    sqlBuf.append("  AND dc.initdatetime::date <= ").append(SIDBUtil.SQL2Str(this.getSeasonTo()));
    if(SIUtil.isNotNull(this.getOrderRoute())){
      sqlBuf.append("  AND oh.orderroute = ").append(SIDBUtil.SQL2Str(this.getOrderRoute()));
    }
    if(SIUtil.isNotNull(this.getChargeCode())){
      sqlBuf.append("  AND oh.chargecode = ").append(SIDBUtil.SQL2Str(this.getChargeCode()));
    }
    sqlBuf.append("  AND oh.branchcode=br.branchcode ");
    sqlBuf.append("  AND oh.chargecode=ch.chargecode ");
    sqlBuf.append("  AND oh.custcode=cu.custcode ");
    sqlBuf.append("  AND oh.enabledflg = '1' ");
    sqlBuf.append("  AND id.consigngoodsflg = '0' ");
    
    //3.訂正黒データ
    sqlBuf.append("UNION ALL ");
    sqlBuf.append("SELECT dc.initdatetime::date AS shippmentdate ");
    sqlBuf.append("     , oh.initdatetime::date ");
    sqlBuf.append("     , CASE ");
    for (int num7=0;num7<SP_STOCKCODE.length;num7++) {
      sqlBuf.append("       WHEN al.branchname IS NULL AND id.stockcode='").append(SP_STOCKCODE[num7]).append("' THEN ").append(SIDBUtil.SQL2Str(SP_BRANCHNAME[num7]));
    }
    sqlBuf.append("       WHEN al.branchname IS NULL THEN br.branchname ELSE al.branchname END AS shipbranch ");
    sqlBuf.append("     , br.branchname ");
    sqlBuf.append("     , ch.chargename ");
    sqlBuf.append("     , dt.ordercode ");
    sqlBuf.append("     , cu.custname ");
    sqlBuf.append("     , dt.individualcode ");
    sqlBuf.append("     , dt.cmdtyname ");
    sqlBuf.append("     , CASE id.usednewflg WHEN '0' THEN '中古' WHEN '1' THEN '新品(NB)' WHEN '2' THEN '新古OUTLET' ");
    sqlBuf.append("                          WHEN '3' THEN 'BG認定中古' WHEN '4' THEN '展示品' WHEN '5' THEN '展示品(貸与品)' ");
    sqlBuf.append("                          WHEN '6' THEN 'キャンペーン' WHEN '7' THEN '新品(PB)' ELSE '中古' ");
    sqlBuf.append("       END AS usednewflg ");
    sqlBuf.append("     , '訂正' AS ordertype ");
    sqlBuf.append("     , CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS amount ");
    sqlBuf.append("     , dt.price ");
    sqlBuf.append("     , dt.price *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofsellinctax ");
    sqlBuf.append("     , 0 AS deliveryfee ");
    sqlBuf.append("     , 0 AS feeinctax ");
    sqlBuf.append("     , 0 AS fee ");
    sqlBuf.append("     , 0 AS sumofdiscountinctax ");
    sqlBuf.append("     , 0 AS sumofdiscount ");
    sqlBuf.append("     , 0 AS sumbypoint ");
    sqlBuf.append("     , dt.price *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofpriceinctax ");
    sqlBuf.append("     , (coalesce(dc.purchasepricenew,0) + coalesce(dt.processingexpence,0)) AS unitcostinctax ");
    sqlBuf.append("     , (coalesce(dc.purchasepricenew,0) + coalesce(dt.processingexpence,0)) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofcostinctax ");
    sqlBuf.append("     , (dt.price - (coalesce(dc.purchasepricenew,0) + coalesce(dt.processingexpence,0))) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofgaininctax ");
    sqlBuf.append("     , TRUNC(dt.price *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (oh.taxrate / 100))) AS totalofprice ");
    sqlBuf.append("     , TRUNC((coalesce(dc.purchasepricenew,0) + coalesce(dt.processingexpence,0)) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (tax(dt.shippmentdate) / 100))) AS totalofcost ");
    sqlBuf.append("     , TRUNC(dt.price *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (oh.taxrate / 100)))  ");
    sqlBuf.append("     - TRUNC((coalesce(dc.purchasepricenew,0) + coalesce(dt.processingexpence,0)) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (tax(dt.shippmentdate) / 100))) AS totalofgain ");
    sqlBuf.append("     , CASE WHEN dt.price = 0 THEN 0 ");
    sqlBuf.append("       ELSE 0.5 END AS gainrate ");
    sqlBuf.append("     , CASE oh.orderroute ");
    for (int i=0;i<SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length;i++) {
      sqlBuf.append(" WHEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][1]));
      sqlBuf.append(" THEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][0]));
    }
    sqlBuf.append(" ELSE ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length-1][0]));
    sqlBuf.append(" END AS orderroute ");
    /*
    sqlBuf.append("     , CASE oh.orderroute WHEN '0' THEN 'EC' WHEN '1' THEN '来店' WHEN '2' THEN '電話' ");
    sqlBuf.append("                          WHEN '3' THEN 'メール' WHEN '4' THEN 'FAX' WHEN '6' THEN '携帯' ");
    sqlBuf.append("                          WHEN '7' THEN '楽天' WHEN '8' THEN 'スマートフォン' WHEN '9' THEN 'イベント' ");
    sqlBuf.append("                          WHEN 'A' THEN 'イベントFAX' WHEN 'B' THEN 'ELG' WHEN 'C' THEN 'アプリ' ELSE 'その他' END AS orderroute ");
    */
    sqlBuf.append("     , oh.paymethodname ");
    sqlBuf.append("     , 3 AS sortkey ");
    sqlBuf.append("     , oh.taxRate ");
    sqlBuf.append("     , CASE ");
    for (int num8=0;num8<SP_STOCKCODE.length;num8++) {
      sqlBuf.append("       WHEN al.branchcode IS NULL AND id.stockcode='").append(SP_STOCKCODE[num8]).append("' THEN ").append(SIDBUtil.SQL2Str(SP_BRANCHCODE[num8]));
    }
    sqlBuf.append("       WHEN al.branchcode IS NULL THEN oh.branchcode ELSE al.branchcode END AS shipbranchcode ");
    sqlBuf.append("     , oh.branchcode AS orderbranchcode ");
    if(isOldRate()) {//200907以前のレート
      sqlBuf.append("     , CASE WHEN id.consigngoodsflg='1' OR id.usednewflg IN ('0','2','3') THEN ").append(USED_RATE);
      sqlBuf.append("            ELSE ").append(NEW_RATE).append(" END AS dividerate ");
    }else{//200908以降のレート
      sqlBuf.append("     , CASE WHEN id.consigngoodsflg='1' THEN ").append(USED_RATE);
      sqlBuf.append("            WHEN id.usednewflg IN ('0','2','3') THEN ").append(USED_RATE2);
      sqlBuf.append("            ELSE ").append(NEW_RATE).append(" END AS dividerate ");
    }
    if(type==0) {
      sqlBuf.append("     , CASE WHEN substr(id.individualcode,0,2) IN ('L','V','W','H') THEN substr(id.individualcode,0,2)  ");
      sqlBuf.append("       WHEN id.usednewflg = '7' THEN 'PB' WHEN id.usednewflg IN ('1','6') THEN 'NB' ELSE 'UD' END AS segment ");
    } else {
      sqlBuf.append("     , CASE WHEN substr(id.individualcode,0,2) IN ('L','V','W','H','N') THEN substr(id.individualcode,0,2)  ");
      sqlBuf.append("       WHEN id.usednewflg = '7' THEN 'PB' WHEN id.usednewflg IN ('1','6') THEN 'NB' ELSE 'UD' END AS segment ");
    }
    sqlBuf.append("FROM orderdetailtbl dt  ");
    sqlBuf.append("INNER JOIN individualtbl id  ");
    sqlBuf.append("ON (dt.cmdtycode=id.cmdtycode AND dt.individualcode=id.individualcode) ");
    sqlBuf.append("INNER JOIN orderdetailcosttbl dc  ");
    sqlBuf.append("ON (dt.ordercode=dc.ordercode AND dt.cmdtycode=dc.cmdtycode AND dt.individualcode=dc.individualcode) ");
    sqlBuf.append("LEFT OUTER JOIN  ");
    sqlBuf.append("(SELECT a.ordercode,a.detailcode,a.individualcode,b.branchname,a.orderallocationnumber,a.branchcode FROM orderallocationinfotbl a,branchtbl b WHERE a.branchcode=b.branchcode) al  ");
    sqlBuf.append("ON dt.ordercode=al.ordercode AND dt.individualcode=al.individualcode AND dt.detailcode=al.detailcode ");
    sqlBuf.append(",ordertbl oh ");
    sqlBuf.append(",custtbl cu,branchtbl br,chargetbl ch ");
    sqlBuf.append("WHERE dt.ordercode = oh.ordercode ");
    sqlBuf.append("  AND dt.orderbranchcode = oh.orderbranchcode  ");
    sqlBuf.append("  AND (oh.branchcode = ").append(SIDBUtil.SQL2Str(this.getBranchCode()));
    for (int num9=0;num9<SP_STOCKCODE.length;num9++) {
      if (this.getBranchCode().equals(SP_BRANCHCODE[num9])) {
        sqlBuf.append("   OR (al.branchcode IS NULL AND id.stockcode = ").append(SIDBUtil.SQL2Str(SP_STOCKCODE[num9],")"));
      }
    }
    sqlBuf.append("   OR al.branchcode = ").append(SIDBUtil.SQL2Str(this.getBranchCode(),")"));
    sqlBuf.append("  AND dc.initdatetime::date >= ").append(SIDBUtil.SQL2Str(this.getSeasonFrom()));
    sqlBuf.append("  AND dc.initdatetime::date <= ").append(SIDBUtil.SQL2Str(this.getSeasonTo()));
    if(SIUtil.isNotNull(this.getOrderRoute())){
      sqlBuf.append("  AND oh.orderroute = ").append(SIDBUtil.SQL2Str(this.getOrderRoute()));
    }
    if(SIUtil.isNotNull(this.getChargeCode())){
      sqlBuf.append("  AND oh.chargecode = ").append(SIDBUtil.SQL2Str(this.getChargeCode()));
    }
    sqlBuf.append("  AND oh.branchcode=br.branchcode ");
    sqlBuf.append("  AND oh.chargecode=ch.chargecode ");
    sqlBuf.append("  AND oh.custcode=cu.custcode ");
    sqlBuf.append("  AND oh.enabledflg = '1' ");
    sqlBuf.append("  AND id.consigngoodsflg = '0' ");
    
    // 4.売上送料データ
    sqlBuf.append("UNION ALL ");
    sqlBuf.append("SELECT dt.shippmentdate ");
    sqlBuf.append("     , oh.initdatetime::date ");
    sqlBuf.append("     , br.branchname AS shipbranch ");
    sqlBuf.append("     , br.branchname ");
    sqlBuf.append("     , ch.chargename ");
    sqlBuf.append("     , oh.ordercode ");
    sqlBuf.append("     , cu.custname ");
    sqlBuf.append("     , '' AS individualcode ");
    sqlBuf.append("     , '' AS cmdtyname ");
    sqlBuf.append("     , '' AS usednewflg ");
    sqlBuf.append("     , '売上' AS ordertype ");
    sqlBuf.append("     , 0 AS amount ");
    sqlBuf.append("     , 0 AS price ");
    sqlBuf.append("     , 0 AS totalofsellinctax ");
    sqlBuf.append("     , oh.totalofdeliveryfee + oh.discountdeliveryfee AS deliveryfee ");
    sqlBuf.append("     , oh.totaloffee + oh.discountfee AS feeinctax ");
    sqlBuf.append("     , TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) AS fee ");
    //EDBTG003-00 ohsugi mod start
//    sqlBuf.append("     , -1 * oh.sumofdiscount AS sumofdiscountinctax ");
    sqlBuf.append("     , -1 * (oh.sumofdiscount + oh.setdiscount) AS sumofdiscountinctax ");
//    sqlBuf.append("     , -1 * TRUNC(oh.sumofdiscount / (1 + (oh.taxrate / 100))) AS sumofdiscount ");
    sqlBuf.append("     , -1 * TRUNC((oh.sumofdiscount + oh.setdiscount) / (1 + (oh.taxrate / 100))) AS sumofdiscount ");
    //EDBTG003-00 ohsugi mod end
    sqlBuf.append("     , -1 * oh.sumbypoint AS sumbypoint ");
    //EDBTG003-00 ohsugi mod start
//    sqlBuf.append("     , oh.totaloffee + oh.discountfee - oh.sumofdiscount AS totalofpriceinctax ");
    sqlBuf.append("     , oh.totaloffee + oh.discountfee - (oh.sumofdiscount + oh.setdiscount) AS totalofpriceinctax ");
    //EDBTG003-00 ohsugi mod end
    sqlBuf.append("     , 0 AS unitcostinctax ");
    sqlBuf.append("     , 0 AS totalofcostinctax ");
    //EDBTG003-00 ohsugi mod start
//    sqlBuf.append("     , oh.totaloffee + oh.discountfee - oh.sumofdiscount AS totalofgaininctax ");
    sqlBuf.append("     , oh.totaloffee + oh.discountfee - (oh.sumofdiscount + oh.setdiscount) AS totalofgaininctax ");
//    sqlBuf.append("     , TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) - TRUNC(oh.sumofdiscount / (1 + (oh.taxrate / 100))) AS totalofprice ");
    sqlBuf.append("     , TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) - TRUNC((oh.sumofdiscount + oh.setdiscount) / (1 + (oh.taxrate / 100))) AS totalofprice ");
    //EDBTG003-00 ohsugi mod end
    sqlBuf.append("     , 0 AS totalofcost ");
    //EDBTG003-00 ohsugi mod start
//    sqlBuf.append("     , TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) - TRUNC(oh.sumofdiscount / (1 + (oh.taxrate / 100))) AS totalofgain ");
    sqlBuf.append("     , TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) - TRUNC((oh.sumofdiscount + oh.setdiscount) / (1 + (oh.taxrate / 100))) AS totalofgain ");
    //EDBTG003-00 ohsugi mod end
    sqlBuf.append("     , 1 AS gainrate ");
    sqlBuf.append("     , CASE oh.orderroute ");
    for (int i=0;i<SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length;i++) {
      sqlBuf.append(" WHEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][1]));
      sqlBuf.append(" THEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][0]));
    }
    sqlBuf.append(" ELSE ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length-1][0]));
    sqlBuf.append(" END AS orderroute ");
    /*
    sqlBuf.append("     , CASE oh.orderroute WHEN '0' THEN 'EC' WHEN '1' THEN '来店' WHEN '2' THEN '電話' ");
    sqlBuf.append("                          WHEN '3' THEN 'メール' WHEN '4' THEN 'FAX' WHEN '6' THEN '携帯' ");
    sqlBuf.append("                          WHEN '7' THEN '楽天' WHEN '8' THEN 'スマートフォン' WHEN '9' THEN 'イベント' ");
    sqlBuf.append("                          WHEN 'A' THEN 'イベントFAX' WHEN 'B' THEN 'ELG' WHEN 'C' THEN 'アプリ' ELSE 'その他' END AS orderroute ");
    */
    sqlBuf.append("     , oh.paymethodname ");
    sqlBuf.append("     , 4 AS sortkey ");
    sqlBuf.append("     , oh.taxRate ");
    sqlBuf.append("     , oh.branchcode::numeric AS shipbranchcode ");
    sqlBuf.append("     , oh.branchcode::numeric AS orderbranchcode ");
    sqlBuf.append("     , NULL::numeric AS dividerate ");
    sqlBuf.append("     , '' AS segment ");
    sqlBuf.append("FROM ordersumvw oh, ");
    sqlBuf.append("(SELECT ordercode,MAX(shippmentdate) AS shippmentdate FROM orderdetaillatestvw GROUP BY ordercode) dt ");
    sqlBuf.append(",custtbl cu,branchtbl br,chargetbl ch ");
    sqlBuf.append("WHERE oh.deliveryedflg + oh.nodeliveryflg = 1 ");
    sqlBuf.append("  AND oh.ordercode=dt.ordercode ");
    sqlBuf.append("  AND oh.branchcode = ").append(SIDBUtil.SQL2Str(this.getBranchCode()));
    sqlBuf.append("  AND dt.shippmentdate >= ").append(SIDBUtil.SQL2Str(this.getSeasonFrom()));
    sqlBuf.append("  AND dt.shippmentdate <= ").append(SIDBUtil.SQL2Str(this.getSeasonTo()));
    if(SIUtil.isNotNull(this.getOrderRoute())){
      sqlBuf.append("  AND oh.orderroute = ").append(SIDBUtil.SQL2Str(this.getOrderRoute()));
    }
    if(SIUtil.isNotNull(this.getChargeCode())){
      sqlBuf.append("  AND oh.chargecode = ").append(SIDBUtil.SQL2Str(this.getChargeCode()));
    }
    sqlBuf.append("  AND oh.branchcode=br.branchcode ");
    sqlBuf.append("  AND oh.chargecode=ch.chargecode ");
    sqlBuf.append("  AND oh.custcode=cu.custcode ");
    sqlBuf.append("  AND oh.branchcode<>'17' ");
    
    // 5.返品明細データ
    sqlBuf.append("UNION ALL ");
    sqlBuf.append("SELECT oh.updatedatetime::date AS shippmentdate ");
    sqlBuf.append("     , oh.initdatetime::date ");
    sqlBuf.append("     , CASE ");
    for (int num10=0;num10<SP_STOCKCODE.length;num10++) {
      sqlBuf.append("       WHEN detail.branchname IS NULL AND id.stockcode='").append(SP_STOCKCODE[num10]).append("' THEN ").append(SIDBUtil.SQL2Str(SP_BRANCHNAME[num10]));
    }
    sqlBuf.append("       WHEN detail.branchname IS NULL THEN br.branchname ELSE detail.branchname END AS shipbranch ");
    sqlBuf.append("     , br.branchname ");
    sqlBuf.append("     , ch.chargename ");
    sqlBuf.append("     , oh.ordercode ");
    sqlBuf.append("     , cu.custname ");
    sqlBuf.append("     , detail.individualcode ");
    sqlBuf.append("     , detail.cmdtyname ");
    sqlBuf.append("     , CASE id.usednewflg WHEN '0' THEN '中古' WHEN '1' THEN '新品(NB)' WHEN '2' THEN '新古OUTLET' ");
    sqlBuf.append("                          WHEN '3' THEN 'BG認定中古' WHEN '4' THEN '展示品' WHEN '5' THEN '展示品(貸与品)' ");
    sqlBuf.append("                          WHEN '6' THEN 'キャンペーン' WHEN '7' THEN '新品(PB)' ELSE '中古' ");
    sqlBuf.append("       END AS usednewflg ");
    sqlBuf.append("     , '返品' AS ordertype ");
    /*
    sqlBuf.append("     , CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS amount ");
    sqlBuf.append("     , -1 * CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price END AS price ");
    sqlBuf.append("     , -1 * CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price END *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofsellinctax ");
    */
    sqlBuf.append("     , detail.amount ");
    sqlBuf.append("     , -1 * detail.totalofpriceinctax / detail.amount AS price ");
    sqlBuf.append("     , -1 * detail.totalofpriceinctax AS totalofsellinctax ");
    
    sqlBuf.append("     , 0 AS deliveryfee ");
    sqlBuf.append("     , 0 AS feeinctax ");
    sqlBuf.append("     , 0 AS fee ");
    sqlBuf.append("     , 0 AS sumofdiscountinctax ");
    sqlBuf.append("     , 0 AS sumofdiscount ");
    sqlBuf.append("     , 0 AS sumbypoint ");
    /*
    sqlBuf.append("     , -1 * CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price END *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofpriceinctax ");
    sqlBuf.append("     , -1 * CASE WHEN id.consigngoodsflg = '1' THEN 0 ELSE (coalesce(coalesce(ct.purchasepricenew,dt.purchaseprice),0) + coalesce(dt.processingexpence,0)) END AS unitcostinctax ");
    sqlBuf.append("     , -1 * CASE WHEN id.consigngoodsflg = '1' THEN 0 ELSE (coalesce(coalesce(ct.purchasepricenew,dt.purchaseprice),0) + coalesce(dt.processingexpence,0)) END *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofcostinctax ");
    sqlBuf.append("     , -1 * CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee  ");
    sqlBuf.append("       ELSE dt.price - (coalesce(coalesce(ct.purchasepricenew,dt.purchaseprice),0) + coalesce(dt.processingexpence,0)) END *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofgaininctax ");
    sqlBuf.append("     , -1 * TRUNC(CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price END *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (oh.taxrate / 100))) AS totalofprice ");
    sqlBuf.append("     , CASE WHEN id.consigngoodsflg = '1' THEN 0 ELSE  ");
    sqlBuf.append("       -1 * TRUNC((coalesce(coalesce(ct.purchasepricenew,dt.purchaseprice),0) + coalesce(dt.processingexpence,0)) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (tax(dt.shippmentdate) / 100))) END AS totalofcost ");
    sqlBuf.append("     , -1 * (TRUNC(CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price END *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (oh.taxrate / 100))) ");
    sqlBuf.append("     - CASE WHEN id.consigngoodsflg = '1' THEN 0 ELSE  ");
    sqlBuf.append("       TRUNC((coalesce(coalesce(ct.purchasepricenew,dt.purchaseprice),0) + coalesce(dt.processingexpence,0)) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (tax(dt.shippmentdate) / 100))) END) AS totalofgain ");
    */
    sqlBuf.append("     , -1 * detail.totalofpriceinctax AS totalofpriceinctax ");
    sqlBuf.append("     , -1 * detail.totalofcostinctax / detail.amount AS unitcostinctax ");
    sqlBuf.append("     , -1 * detail.totalofcostinctax AS totalofcostinctax ");
    sqlBuf.append("     , -1 * detail.totalofgaininctax AS totalofgaininctax ");
    sqlBuf.append("     , -1 * detail.totalofprice AS totalofprice ");
    sqlBuf.append("     , -1 * detail.totalofcost AS totalofcost ");
    sqlBuf.append("     , -1 * detail.totalofgain AS totalofgain ");
    
    sqlBuf.append("     , CASE WHEN id.consigngoodsflg = '1' THEN 1 WHEN detail.totalofprice = 0 THEN 0 ");
    sqlBuf.append("       ELSE 0.5 END AS gainrate ");
    sqlBuf.append("     , CASE oh.orderroute ");
    for (int i=0;i<SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length;i++) {
      sqlBuf.append(" WHEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][1]));
      sqlBuf.append(" THEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][0]));
    }
    sqlBuf.append(" ELSE ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length-1][0]));
    sqlBuf.append(" END AS orderroute ");
    /*
    sqlBuf.append("     , CASE oh.orderroute WHEN '0' THEN 'EC' WHEN '1' THEN '来店' WHEN '2' THEN '電話' ");
    sqlBuf.append("                          WHEN '3' THEN 'メール' WHEN '4' THEN 'FAX' WHEN '6' THEN '携帯' ");
    sqlBuf.append("                          WHEN '7' THEN '楽天' WHEN '8' THEN 'スマートフォン' WHEN '9' THEN 'イベント' ");
    sqlBuf.append("                          WHEN 'A' THEN 'イベントFAX' WHEN 'B' THEN 'ELG' WHEN 'C' THEN 'アプリ' ELSE 'その他' END AS orderroute ");
    */
    sqlBuf.append("     , oh.paymethodname ");
    sqlBuf.append("     , 5 AS sortkey ");
    sqlBuf.append("     , oh.taxRate ");
    sqlBuf.append("     , CASE ");
    for (int num11=0;num11<SP_STOCKCODE.length;num11++) {
      sqlBuf.append("       WHEN detail.branchcode IS NULL AND id.stockcode='").append(SP_STOCKCODE[num11]).append("' THEN ").append(SIDBUtil.SQL2Str(SP_BRANCHCODE[num11]));
    }
    sqlBuf.append("       WHEN detail.branchcode IS NULL THEN oh.branchcode ELSE detail.branchcode END AS shipbranchcode ");
    sqlBuf.append("     , oh.branchcode AS orderbranchcode ");
    if(isOldRate()) {//200907以前のレート
      sqlBuf.append("     , CASE WHEN id.consigngoodsflg='1' OR id.usednewflg IN ('0','2','3') THEN ").append(USED_RATE);
      sqlBuf.append("            ELSE ").append(NEW_RATE).append(" END AS dividerate ");
    }else{//200908以降のレート
      sqlBuf.append("     , CASE WHEN id.consigngoodsflg='1' THEN ").append(USED_RATE);
      sqlBuf.append("            WHEN id.usednewflg IN ('0','2','3') THEN ").append(USED_RATE2);
      sqlBuf.append("            ELSE ").append(NEW_RATE).append(" END AS dividerate ");
    }
    if(type==0) {
      sqlBuf.append("     , CASE WHEN substr(id.individualcode,0,2) IN ('L','V','W','H') THEN substr(id.individualcode,0,2)  ");
      sqlBuf.append("       WHEN id.usednewflg = '7' THEN 'PB' WHEN id.usednewflg IN ('1','6') THEN 'NB' ELSE 'UD' END AS segment ");
    } else {
      sqlBuf.append("     , CASE WHEN substr(id.individualcode,0,2) IN ('L','V','W','H','N') THEN substr(id.individualcode,0,2)  ");
      sqlBuf.append("       WHEN id.usednewflg = '7' THEN 'PB' WHEN id.usednewflg IN ('1','6') THEN 'NB' ELSE 'UD' END AS segment ");
    }
    
    sqlBuf.append("FROM statusdetailbgvw detail INNER JOIN individualtbl id ON (detail.individualcode=id.individualcode) ");
    sqlBuf.append("LEFT OUTER JOIN ");
    sqlBuf.append(" (SELECT a.ordercode,a.individualcode,a.purchasepricenew FROM orderdetailcosttbl a,");
    sqlBuf.append("  (SELECT ordercode,individualcode,MAX(correctcount) AS correctcount FROM orderdetailcosttbl GROUP BY ordercode,individualcode) b");
    sqlBuf.append("  WHERE a.ordercode = b.ordercode AND a.individualcode = b.individualcode AND a.correctcount = b.correctcount) ct ");
    sqlBuf.append("ON detail.ordercode = ct.ordercode AND detail.individualcode = ct.individualcode ");
    /*
    sqlBuf.append("LEFT OUTER JOIN ");
    sqlBuf.append("(SELECT a.ordercode,a.detailcode,a.individualcode,b.branchname,a.orderallocationnumber,a.branchcode FROM orderallocationinfotbl a,branchtbl b WHERE a.branchcode=b.branchcode) al  ");
    sqlBuf.append("ON dt.ordercode=al.ordercode AND dt.individualcode=al.individualcode AND dt.detailcode=al.detailcode ");
    */
    sqlBuf.append(",ordertbl oh ");
    sqlBuf.append(",custtbl cu,branchtbl br,chargetbl ch ");
    sqlBuf.append("WHERE detail.ordercode = oh.ordercode ");
    sqlBuf.append("  AND (oh.branchcode = ").append(SIDBUtil.SQL2Str(this.getBranchCode()));
    for (int num12=0;num12<SP_STOCKCODE.length;num12++) {
      if (this.getBranchCode().equals(SP_BRANCHCODE[num12])) {
        sqlBuf.append("   OR (detail.branchcode IS NULL AND id.stockcode = ").append(SIDBUtil.SQL2Str(SP_STOCKCODE[num12],")"));
      }
    }
    sqlBuf.append("   OR detail.branchcode = ").append(SIDBUtil.SQL2Str(this.getBranchCode(),")"));
    sqlBuf.append("  AND oh.updatedatetime::date >= ").append(SIDBUtil.SQL2Str(this.getSeasonFrom()));
    sqlBuf.append("  AND oh.updatedatetime::date <= ").append(SIDBUtil.SQL2Str(this.getSeasonTo()));
    if(SIUtil.isNotNull(this.getOrderRoute())){
      sqlBuf.append("  AND oh.orderroute = ").append(SIDBUtil.SQL2Str(this.getOrderRoute()));
    }
    if(SIUtil.isNotNull(this.getChargeCode())){
      sqlBuf.append("  AND oh.chargecode = ").append(SIDBUtil.SQL2Str(this.getChargeCode()));
    }
    sqlBuf.append("  AND oh.branchcode=br.branchcode ");
    sqlBuf.append("  AND oh.chargecode=ch.chargecode ");
    sqlBuf.append("  AND oh.custcode=cu.custcode ");
    sqlBuf.append("  AND oh.enabledflg = '1' ");
    sqlBuf.append("  AND oh.status = '2' ");
    
    // 6.返品送料データ
    sqlBuf.append("UNION ALL ");
    sqlBuf.append("SELECT oh.updatedatetime::date AS shippmentdate ");
    sqlBuf.append("     , oh.initdatetime::date ");
    sqlBuf.append("     , br.branchname AS shipbranch ");
    sqlBuf.append("     , br.branchname ");
    sqlBuf.append("     , ch.chargename ");
    sqlBuf.append("     , oh.ordercode ");
    sqlBuf.append("     , cu.custname ");
    sqlBuf.append("     , '' AS individualcode ");
    sqlBuf.append("     , '' AS cmdtyname ");
    sqlBuf.append("     , '' AS usednewflg ");
    sqlBuf.append("     , '返品' AS ordertype ");
    sqlBuf.append("     , 0 AS amount ");
    sqlBuf.append("     , 0 AS price ");
    sqlBuf.append("     , 0 AS totalofsellinctax ");
    sqlBuf.append("     , -1 * (oh.totalofdeliveryfee + oh.discountdeliveryfee) AS deliveryfee ");
    sqlBuf.append("     , -1 * (oh.totaloffee + oh.discountfee) AS feeinctax ");
    sqlBuf.append("     , -1 * TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) AS fee ");
    //EDBTG003-00 ohsugi mod start
//    sqlBuf.append("     , oh.sumofdiscount AS sumofdiscountinctax ");
    sqlBuf.append("     , (oh.sumofdiscount + oh.setdiscount) AS sumofdiscountinctax ");
//    sqlBuf.append("     , TRUNC(oh.sumofdiscount / (1 + (oh.taxrate / 100))) AS sumofdiscount ");
    sqlBuf.append("     , TRUNC((oh.sumofdiscount + oh.setdiscount) / (1 + (oh.taxrate / 100))) AS sumofdiscount ");
    //EDBTG003-00 ohsugi mod end
    sqlBuf.append("     , oh.sumbypoint ");
    //EDBTG003-00 ohsugi mod start
//    sqlBuf.append("     , -1 * (oh.totaloffee + oh.discountfee - oh.sumofdiscount) AS totalofpriceinctax ");
    sqlBuf.append("     , -1 * (oh.totaloffee + oh.discountfee - (oh.sumofdiscount + oh.setdiscount)) AS totalofpriceinctax ");
    //EDBTG003-00 ohsugi mod end
    sqlBuf.append("     , 0 AS unitcostinctax ");
    sqlBuf.append("     , 0 AS totalofcostinctax ");
    //EDBTG003-00 ohsugi mod start
//    sqlBuf.append("     , -1 * (oh.totaloffee + oh.discountfee - oh.sumofdiscount) AS totalofgaininctax ");
    sqlBuf.append("     , -1 * (oh.totaloffee + oh.discountfee - (oh.sumofdiscount + oh.setdiscount)) AS totalofgaininctax ");
//    sqlBuf.append("     , -1 * (TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) - TRUNC(oh.sumofdiscount / (1 + (oh.taxrate / 100)))) AS totalofprice ");
    sqlBuf.append("     , -1 * (TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) - TRUNC((oh.sumofdiscount + oh.setdiscount) / (1 + (oh.taxrate / 100)))) AS totalofprice ");
    //EDBTG003-00 ohsugi mod end
    sqlBuf.append("     , 0 AS totalofcost ");
    //EDBTG003-00 ohsugi mod start
//    sqlBuf.append("     , -1 * (TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) - TRUNC(oh.sumofdiscount / (1 + (oh.taxrate / 100)))) AS totalofgain ");
    sqlBuf.append("     , -1 * (TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) - TRUNC((oh.sumofdiscount +oh.setdiscount) / (1 + (oh.taxrate / 100)))) AS totalofgain ");
    //EDBTG003-00 ohsugi mod end
    sqlBuf.append("     , 1 AS gainrate ");
    sqlBuf.append("     , CASE oh.orderroute ");
    for (int i=0;i<SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length;i++) {
      sqlBuf.append(" WHEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][1]));
      sqlBuf.append(" THEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][0]));
    }
    sqlBuf.append(" ELSE ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length-1][0]));
    sqlBuf.append(" END AS orderroute ");
    /*
    sqlBuf.append("     , CASE oh.orderroute WHEN '0' THEN 'EC' WHEN '1' THEN '来店' WHEN '2' THEN '電話' ");
    sqlBuf.append("                          WHEN '3' THEN 'メール' WHEN '4' THEN 'FAX' WHEN '6' THEN '携帯' ");
    sqlBuf.append("                          WHEN '7' THEN '楽天' WHEN '8' THEN 'スマートフォン' WHEN '9' THEN 'イベント' ");
    sqlBuf.append("                          WHEN 'A' THEN 'イベントFAX' WHEN 'B' THEN 'ELG' WHEN 'C' THEN 'アプリ' ELSE 'その他' END AS orderroute ");
    */
    sqlBuf.append("     , oh.paymethodname ");
    sqlBuf.append("     , 6 AS sortkey ");
    sqlBuf.append("     , oh.taxRate ");
    sqlBuf.append("     , oh.branchcode::numeric AS shipbranchcode ");
    sqlBuf.append("     , oh.branchcode::numeric AS orderbranchcode ");
    sqlBuf.append("     , NULL::numeric AS dividerate ");
    sqlBuf.append("     , '' AS segment ");
    sqlBuf.append("FROM ordersumvw oh,custtbl cu,branchtbl br,chargetbl ch ");
    sqlBuf.append("WHERE oh.status = '2' ");
    sqlBuf.append("  AND oh.branchcode = ").append(SIDBUtil.SQL2Str(this.getBranchCode()));
    sqlBuf.append("  AND oh.updatedatetime::date >= ").append(SIDBUtil.SQL2Str(this.getSeasonFrom()));
    sqlBuf.append("  AND oh.updatedatetime::date <= ").append(SIDBUtil.SQL2Str(this.getSeasonTo()));
    if(SIUtil.isNotNull(this.getOrderRoute())){
      sqlBuf.append("  AND oh.orderroute = ").append(SIDBUtil.SQL2Str(this.getOrderRoute()));
    }
    if(SIUtil.isNotNull(this.getChargeCode())){
      sqlBuf.append("  AND oh.chargecode = ").append(SIDBUtil.SQL2Str(this.getChargeCode()));
    }
    sqlBuf.append("  AND oh.branchcode=br.branchcode ");
    sqlBuf.append("  AND oh.chargecode=ch.chargecode ");
    sqlBuf.append("  AND oh.custcode=cu.custcode ");
    sqlBuf.append("  AND oh.branchcode<>'17' ");
    
    // 7.ソート
    sqlBuf.append("ORDER BY shippmentdate,ordercode,sortkey,individualcode,shipbranchcode; ");
    
    
    return sqlBuf.toString();
  }
  
  private String getSQLALL(){
    StringBuffer sqlBuf = new StringBuffer();
    
    // 1.売上明細データ
    sqlBuf.append("SELECT detail.shippmentdate ");
    sqlBuf.append("     , oh.initdatetime::date ");
    sqlBuf.append("     , CASE ");
    for (int num1=0;num1<SP_STOCKCODE.length;num1++) {
      sqlBuf.append("       WHEN detail.branchname IS NULL AND id.stockcode='").append(SP_STOCKCODE[num1]).append("' THEN ").append(SIDBUtil.SQL2Str(SP_BRANCHNAME[num1]));
    }
    sqlBuf.append("       WHEN detail.branchname IS NULL THEN br.branchname ELSE detail.branchname END AS shipbranch ");
    sqlBuf.append("     , br.branchname ");
    sqlBuf.append("     , ch.chargename ");
    sqlBuf.append("     , oh.ordercode ");
    sqlBuf.append("     , cu.custname ");
    sqlBuf.append("     , detail.individualcode ");
    sqlBuf.append("     , detail.cmdtyname ");
    sqlBuf.append("     , CASE id.usednewflg WHEN '0' THEN '中古' WHEN '1' THEN '新品(NB)' WHEN '2' THEN '新古OUTLET' ");
    sqlBuf.append("                          WHEN '3' THEN 'BG認定中古' WHEN '4' THEN '展示品' WHEN '5' THEN '展示品(貸与品)' ");
    sqlBuf.append("                          WHEN '6' THEN 'キャンペーン' WHEN '7' THEN '新品(PB)' ELSE '中古' ");
    sqlBuf.append("       END AS usednewflg ");
    sqlBuf.append("     , '売上' AS ordertype ");
    /*
    sqlBuf.append("     , CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS amount ");
    sqlBuf.append("     , CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price END AS price ");
    sqlBuf.append("     , CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price END *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofsellinctax ");
    */
    sqlBuf.append("     , detail.amount ");
    sqlBuf.append("     , detail.totalofpriceinctax / detail.amount AS price ");
    sqlBuf.append("     , detail.totalofpriceinctax AS totalofsellinctax ");
    
    sqlBuf.append("     , 0 AS deliveryfee ");
    sqlBuf.append("     , 0 AS feeinctax ");
    sqlBuf.append("     , 0 AS fee ");
    sqlBuf.append("     , 0 AS sumofdiscountinctax ");
    sqlBuf.append("     , 0 AS sumofdiscount ");
    sqlBuf.append("     , 0 AS sumbypoint ");
    /*
    sqlBuf.append("     , CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price END *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofpriceinctax ");
    sqlBuf.append("     , CASE WHEN id.consigngoodsflg = '1' THEN 0 ELSE (coalesce(dt.purchaseprice,0) + coalesce(dt.processingexpence,0)) END AS unitcostinctax ");
    sqlBuf.append("     , CASE WHEN id.consigngoodsflg = '1' THEN 0 ELSE (coalesce(dt.purchaseprice,0) + coalesce(dt.processingexpence,0)) END *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofcostinctax ");
    sqlBuf.append("     , CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price - (coalesce(dt.purchaseprice,0) + coalesce(dt.processingexpence,0)) END *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofgaininctax ");
    sqlBuf.append("     , TRUNC(CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price END *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (oh.taxrate / 100))) AS totalofprice ");
    sqlBuf.append("     , CASE WHEN id.consigngoodsflg = '1' THEN 0 ELSE  ");
    sqlBuf.append("       TRUNC((coalesce(dt.purchaseprice,0) + coalesce(dt.processingexpence,0)) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (tax(dt.shippmentdate) / 100))) END AS totalofcost ");
    sqlBuf.append("     , TRUNC(CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price END *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (oh.taxrate / 100))) ");
    sqlBuf.append("     - CASE WHEN id.consigngoodsflg = '1' THEN 0 ELSE  ");
    sqlBuf.append("       TRUNC((coalesce(dt.purchaseprice,0) + coalesce(dt.processingexpence,0)) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (tax(dt.shippmentdate) / 100))) END AS totalofgain ");
    */
    sqlBuf.append("     , detail.totalofpriceinctax ");
    sqlBuf.append("     , detail.totalofcostinctax / detail.amount AS unitcostinctax ");
    sqlBuf.append("     , detail.totalofcostinctax ");
    sqlBuf.append("     , detail.totalofgaininctax ");
    sqlBuf.append("     , detail.totalofprice ");
    sqlBuf.append("     , detail.totalofcost ");
    sqlBuf.append("     , detail.totalofgain ");
    
    sqlBuf.append("     , CASE WHEN id.consigngoodsflg = '1' THEN 1 WHEN detail.totalofprice = 0 THEN 0 ");
    sqlBuf.append("       ELSE 0.5 END AS gainrate ");
    sqlBuf.append("     , CASE oh.orderroute ");
    for (int i=0;i<SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length;i++) {
      sqlBuf.append(" WHEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][1]));
      sqlBuf.append(" THEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][0]));
    }
    sqlBuf.append(" ELSE ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length-1][0]));
    sqlBuf.append(" END AS orderroute ");
    /*
    sqlBuf.append("     , CASE oh.orderroute WHEN '0' THEN 'EC' WHEN '1' THEN '来店' WHEN '2' THEN '電話' ");
    sqlBuf.append("                          WHEN '3' THEN 'メール' WHEN '4' THEN 'FAX' WHEN '6' THEN '携帯' ");
    sqlBuf.append("                          WHEN '7' THEN '楽天' WHEN '8' THEN 'スマートフォン' WHEN '9' THEN 'イベント' ");
    sqlBuf.append("                          WHEN 'A' THEN 'イベントFAX' WHEN 'B' THEN 'ELG' WHEN 'C' THEN 'アプリ' ELSE 'その他' END AS orderroute ");
    */
    sqlBuf.append("     , oh.paymethodname ");
    sqlBuf.append("     , 1 AS sortkey ");
    sqlBuf.append("     , oh.taxRate ");
    sqlBuf.append("     , CASE ");
    for (int num2=0;num2<SP_STOCKCODE.length;num2++) {
      sqlBuf.append("       WHEN detail.branchcode IS NULL AND id.stockcode='").append(SP_STOCKCODE[num2]).append("' THEN ").append(SIDBUtil.SQL2Str(SP_BRANCHCODE[num2]));
    }
    sqlBuf.append("       WHEN detail.branchcode IS NULL THEN oh.branchcode ELSE detail.branchcode END AS shipbranchcode ");
    sqlBuf.append("     , oh.branchcode AS orderbranchcode ");
    if(isOldRate()) {//200907以前のレート
      sqlBuf.append("     , CASE WHEN id.consigngoodsflg='1' OR id.usednewflg IN ('0','2','3') THEN ").append(USED_RATE);
      sqlBuf.append("            ELSE ").append(NEW_RATE).append(" END AS dividerate ");
    }else{//200908以降のレート
      sqlBuf.append("     , CASE WHEN id.consigngoodsflg='1' THEN ").append(USED_RATE);
      sqlBuf.append("            WHEN id.usednewflg IN ('0','2','3') THEN ").append(USED_RATE2);
      sqlBuf.append("            ELSE ").append(NEW_RATE).append(" END AS dividerate ");
    }
    if(type==0) {
      sqlBuf.append("     , CASE WHEN substr(id.individualcode,0,2) IN ('L','V','W','H') THEN substr(id.individualcode,0,2)  ");
      sqlBuf.append("       WHEN id.usednewflg = '7' THEN 'PB' WHEN id.usednewflg IN ('1','6') THEN 'NB' ELSE 'UD' END AS segment ");
    } else {
      sqlBuf.append("     , CASE WHEN substr(id.individualcode,0,2) IN ('L','V','W','H','N') THEN substr(id.individualcode,0,2)  ");
      sqlBuf.append("       WHEN id.usednewflg = '7' THEN 'PB' WHEN id.usednewflg IN ('1','6') THEN 'NB' ELSE 'UD' END AS segment ");
    }
    sqlBuf.append("FROM statusdetailbgvw detail INNER JOIN individualtbl id ON (detail.individualcode=id.individualcode) ");
    sqlBuf.append(",ordertbl oh  ");
    sqlBuf.append(",custtbl cu,branchtbl br,chargetbl ch ");
    sqlBuf.append("WHERE detail.ordercode = oh.ordercode ");
    sqlBuf.append("  AND detail.shippmentdate >= ").append(SIDBUtil.SQL2Str(this.getSeasonFrom()));
    sqlBuf.append("  AND detail.shippmentdate <= ").append(SIDBUtil.SQL2Str(this.getSeasonTo()));
    if(SIUtil.isNotNull(this.getOrderRoute())){
      sqlBuf.append("  AND oh.orderroute = ").append(SIDBUtil.SQL2Str(this.getOrderRoute()));
    }
    sqlBuf.append("  AND oh.branchcode = br.branchcode ");
    sqlBuf.append("  AND oh.chargecode = ch.chargecode ");
    sqlBuf.append("  AND oh.custcode=cu.custcode ");
    sqlBuf.append("  AND oh.enabledflg = '1' ");
    
    // 2.訂正赤データ
    sqlBuf.append("UNION ALL ");
    sqlBuf.append("SELECT dc.initdatetime::date AS shippmentdate ");
    sqlBuf.append("     , oh.initdatetime::date ");
    sqlBuf.append("     , CASE ");
    for (int num3=0;num3<SP_STOCKCODE.length;num3++) {
      sqlBuf.append("       WHEN al.branchname IS NULL AND id.stockcode='").append(SP_STOCKCODE[num3]).append("' THEN ").append(SIDBUtil.SQL2Str(SP_BRANCHNAME[num3]));
    }
    sqlBuf.append("       WHEN al.branchname IS NULL THEN br.branchname ELSE al.branchname END AS shipbranch ");
    sqlBuf.append("     , br.branchname ");
    sqlBuf.append("     , ch.chargename ");
    sqlBuf.append("     , dt.ordercode ");
    sqlBuf.append("     , cu.custname ");
    sqlBuf.append("     , dt.individualcode ");
    sqlBuf.append("     , dt.cmdtyname ");
    sqlBuf.append("     , CASE id.usednewflg WHEN '0' THEN '中古' WHEN '1' THEN '新品(NB)' WHEN '2' THEN '新古OUTLET' ");
    sqlBuf.append("                          WHEN '3' THEN 'BG認定中古' WHEN '4' THEN '展示品' WHEN '5' THEN '展示品(貸与品)' ");
    sqlBuf.append("                          WHEN '6' THEN 'キャンペーン' WHEN '7' THEN '新品(PB)' ELSE '中古' ");
    sqlBuf.append("       END AS usednewflg ");
    sqlBuf.append("     , '訂正' AS ordertype ");
    sqlBuf.append("     , CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS amount ");
    sqlBuf.append("     , -1 * dt.price AS price ");
    sqlBuf.append("     , -1 * dt.price *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofsellinctax ");
    sqlBuf.append("     , 0 AS deliveryfee ");
    sqlBuf.append("     , 0 AS feeinctax ");
    sqlBuf.append("     , 0 AS fee ");
    sqlBuf.append("     , 0 AS sumofdiscountinctax ");
    sqlBuf.append("     , 0 AS sumofdiscount ");
    sqlBuf.append("     , 0 AS sumbypoint ");
    sqlBuf.append("     , -1 * dt.price *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofpriceinctax ");
    sqlBuf.append("     , -1 * (coalesce(dc.purchasepriceold,0) + coalesce(dt.processingexpence,0)) AS unitcostinctax ");
    sqlBuf.append("     , -1 * (coalesce(dc.purchasepriceold,0) + coalesce(dt.processingexpence,0)) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofcostinctax ");
    sqlBuf.append("     , -1 * (dt.price - (coalesce(dc.purchasepriceold,0) + coalesce(dt.processingexpence,0))) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofgaininctax ");
    sqlBuf.append("     , -1 * TRUNC(dt.price *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (oh.taxrate / 100))) AS totalofprice ");
    sqlBuf.append("     , -1 * TRUNC((coalesce(dc.purchasepriceold,0) + coalesce(dt.processingexpence,0)) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (tax(dt.shippmentdate) / 100))) AS totalofcost ");
    sqlBuf.append("     , -1 * (TRUNC(dt.price *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (oh.taxrate / 100)))  ");
    sqlBuf.append("     - TRUNC((coalesce(dc.purchasepriceold,0) + coalesce(dt.processingexpence,0)) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (tax(dt.shippmentdate) / 100)))) AS totalofgain ");
    sqlBuf.append("     , CASE WHEN dt.price = 0 THEN 0 ");
    sqlBuf.append("       ELSE 0.5 END AS gainrate ");
    sqlBuf.append("     , CASE oh.orderroute ");
    for (int i=0;i<SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length;i++) {
      sqlBuf.append(" WHEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][1]));
      sqlBuf.append(" THEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][0]));
    }
    sqlBuf.append(" ELSE ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length-1][0]));
    sqlBuf.append(" END AS orderroute ");
    /*
    sqlBuf.append("     , CASE oh.orderroute WHEN '0' THEN 'EC' WHEN '1' THEN '来店' WHEN '2' THEN '電話' ");
    sqlBuf.append("                          WHEN '3' THEN 'メール' WHEN '4' THEN 'FAX' WHEN '6' THEN '携帯' ");
    sqlBuf.append("                          WHEN '7' THEN '楽天' WHEN '8' THEN 'スマートフォン' WHEN '9' THEN 'イベント' ");
    sqlBuf.append("                          WHEN 'A' THEN 'イベントFAX' WHEN 'B' THEN 'ELG' WHEN 'C' THEN 'アプリ' ELSE 'その他' END AS orderroute ");
    */
    sqlBuf.append("     , oh.paymethodname ");
    sqlBuf.append("     , 2 AS sortkey ");
    sqlBuf.append("     , oh.taxRate ");
    sqlBuf.append("     , CASE ");
    for (int num4=0;num4<SP_STOCKCODE.length;num4++) {
      sqlBuf.append("       WHEN al.branchcode IS NULL AND id.stockcode='").append(SP_STOCKCODE[num4]).append("' THEN ").append(SIDBUtil.SQL2Str(SP_BRANCHCODE[num4]));
    }
    sqlBuf.append("       WHEN al.branchcode IS NULL THEN oh.branchcode ELSE al.branchcode END AS shipbranchcode ");
    sqlBuf.append("     , oh.branchcode AS orderbranchcode ");
    if(isOldRate()) {//200907以前のレート
      sqlBuf.append("     , CASE WHEN id.consigngoodsflg='1' OR id.usednewflg IN ('0','2','3') THEN ").append(USED_RATE);
      sqlBuf.append("            ELSE ").append(NEW_RATE).append(" END AS dividerate ");
    }else{//200908以降のレート
      sqlBuf.append("     , CASE WHEN id.consigngoodsflg='1' THEN ").append(USED_RATE);
      sqlBuf.append("            WHEN id.usednewflg IN ('0','2','3') THEN ").append(USED_RATE2);
      sqlBuf.append("            ELSE ").append(NEW_RATE).append(" END AS dividerate ");
    }
    if(type==0) {
      sqlBuf.append("     , CASE WHEN substr(id.individualcode,0,2) IN ('L','V','W','H') THEN substr(id.individualcode,0,2)  ");
      sqlBuf.append("       WHEN id.usednewflg = '7' THEN 'PB' WHEN id.usednewflg IN ('1','6') THEN 'NB' ELSE 'UD' END AS segment ");
    } else {
      sqlBuf.append("     , CASE WHEN substr(id.individualcode,0,2) IN ('L','V','W','H','N') THEN substr(id.individualcode,0,2)  ");
      sqlBuf.append("       WHEN id.usednewflg = '7' THEN 'PB' WHEN id.usednewflg IN ('1','6') THEN 'NB' ELSE 'UD' END AS segment ");
    }
    sqlBuf.append("FROM orderdetailtbl dt  ");
    sqlBuf.append("INNER JOIN individualtbl id  ");
    sqlBuf.append("ON (dt.cmdtycode=id.cmdtycode AND dt.individualcode=id.individualcode) ");
    sqlBuf.append("INNER JOIN orderdetailcosttbl dc  ");
    sqlBuf.append("ON (dt.ordercode=dc.ordercode AND dt.cmdtycode=dc.cmdtycode AND dt.individualcode=dc.individualcode) ");
    sqlBuf.append("LEFT OUTER JOIN  ");
    sqlBuf.append("(SELECT a.ordercode,a.detailcode,a.individualcode,b.branchname,a.orderallocationnumber,a.branchcode FROM orderallocationinfotbl a,branchtbl b WHERE a.branchcode=b.branchcode) al  ");
    sqlBuf.append("ON dt.ordercode=al.ordercode AND dt.individualcode=al.individualcode AND dt.detailcode=al.detailcode ");
    sqlBuf.append(",ordertbl oh ");
    sqlBuf.append(",custtbl cu,branchtbl br,chargetbl ch ");
    sqlBuf.append("WHERE dt.ordercode = oh.ordercode ");
    sqlBuf.append("  AND dt.orderbranchcode = oh.orderbranchcode  ");
    sqlBuf.append("  AND dc.initdatetime::date >= ").append(SIDBUtil.SQL2Str(this.getSeasonFrom()));
    sqlBuf.append("  AND dc.initdatetime::date <= ").append(SIDBUtil.SQL2Str(this.getSeasonTo()));
    if(SIUtil.isNotNull(this.getOrderRoute())){
      sqlBuf.append("  AND oh.orderroute = ").append(SIDBUtil.SQL2Str(this.getOrderRoute()));
    }
    sqlBuf.append("  AND oh.branchcode=br.branchcode ");
    sqlBuf.append("  AND oh.chargecode=ch.chargecode ");
    sqlBuf.append("  AND oh.custcode=cu.custcode ");
    sqlBuf.append("  AND oh.enabledflg = '1' ");
    sqlBuf.append("  AND id.consigngoodsflg = '0' ");
    
    //3.訂正黒データ
    sqlBuf.append("UNION ALL ");
    sqlBuf.append("SELECT dc.initdatetime::date AS shippmentdate ");
    sqlBuf.append("     , oh.initdatetime::date ");
    sqlBuf.append("     , CASE ");
    for (int num5=0;num5<SP_STOCKCODE.length;num5++) {
      sqlBuf.append("       WHEN al.branchname IS NULL AND id.stockcode='").append(SP_STOCKCODE[num5]).append("' THEN ").append(SIDBUtil.SQL2Str(SP_BRANCHNAME[num5]));
    }
    sqlBuf.append("       WHEN al.branchname IS NULL THEN br.branchname ELSE al.branchname END AS shipbranch ");
    sqlBuf.append("     , br.branchname ");
    sqlBuf.append("     , ch.chargename ");
    sqlBuf.append("     , dt.ordercode ");
    sqlBuf.append("     , cu.custname ");
    sqlBuf.append("     , dt.individualcode ");
    sqlBuf.append("     , dt.cmdtyname ");
    sqlBuf.append("     , CASE id.usednewflg WHEN '0' THEN '中古' WHEN '1' THEN '新品(NB)' WHEN '2' THEN '新古OUTLET' ");
    sqlBuf.append("                          WHEN '3' THEN 'BG認定中古' WHEN '4' THEN '展示品' WHEN '5' THEN '展示品(貸与品)' ");
    sqlBuf.append("                          WHEN '6' THEN 'キャンペーン' WHEN '7' THEN '新品(PB)' ELSE '中古' ");
    sqlBuf.append("       END AS usednewflg ");
    sqlBuf.append("     , '訂正' AS ordertype ");
    sqlBuf.append("     , CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS amount ");
    sqlBuf.append("     , dt.price ");
    sqlBuf.append("     , dt.price *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofsellinctax ");
    sqlBuf.append("     , 0 AS deliveryfee ");
    sqlBuf.append("     , 0 AS feeinctax ");
    sqlBuf.append("     , 0 AS fee ");
    sqlBuf.append("     , 0 AS sumofdiscountinctax ");
    sqlBuf.append("     , 0 AS sumofdiscount ");
    sqlBuf.append("     , 0 AS sumbypoint ");
    sqlBuf.append("     , dt.price *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofpriceinctax ");
    sqlBuf.append("     , (coalesce(dc.purchasepricenew,0) + coalesce(dt.processingexpence,0)) AS unitcostinctax ");
    sqlBuf.append("     , (coalesce(dc.purchasepricenew,0) + coalesce(dt.processingexpence,0)) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofcostinctax ");
    sqlBuf.append("     , (dt.price - (coalesce(dc.purchasepricenew,0) + coalesce(dt.processingexpence,0))) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofgaininctax ");
    sqlBuf.append("     , TRUNC(dt.price *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (oh.taxrate / 100))) AS totalofprice ");
    sqlBuf.append("     , TRUNC((coalesce(dc.purchasepricenew,0) + coalesce(dt.processingexpence,0)) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (tax(dt.shippmentdate) / 100))) AS totalofcost ");
    sqlBuf.append("     , TRUNC(dt.price *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (oh.taxrate / 100)))  ");
    sqlBuf.append("     - TRUNC((coalesce(dc.purchasepricenew,0) + coalesce(dt.processingexpence,0)) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (tax(dt.shippmentdate) / 100))) AS totalofgain ");
    sqlBuf.append("     , CASE WHEN dt.price = 0 THEN 0 ");
    sqlBuf.append("       ELSE 0.5 END AS gainrate ");
    sqlBuf.append("     , CASE oh.orderroute ");
    for (int i=0;i<SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length;i++) {
      sqlBuf.append(" WHEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][1]));
      sqlBuf.append(" THEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][0]));
    }
    sqlBuf.append(" ELSE ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length-1][0]));
    sqlBuf.append(" END AS orderroute ");
    /*
    sqlBuf.append("     , CASE oh.orderroute WHEN '0' THEN 'EC' WHEN '1' THEN '来店' WHEN '2' THEN '電話' ");
    sqlBuf.append("                          WHEN '3' THEN 'メール' WHEN '4' THEN 'FAX' WHEN '6' THEN '携帯' ");
    sqlBuf.append("                          WHEN '7' THEN '楽天' WHEN '8' THEN 'スマートフォン' WHEN '9' THEN 'イベント' ");
    sqlBuf.append("                          WHEN 'A' THEN 'イベントFAX' WHEN 'B' THEN 'ELG' WHEN 'C' THEN 'アプリ' ELSE 'その他' END AS orderroute ");
    */
    sqlBuf.append("     , oh.paymethodname ");
    sqlBuf.append("     , 3 AS sortkey ");
    sqlBuf.append("     , oh.taxRate ");
    sqlBuf.append("     , CASE ");
    for (int num6=0;num6<SP_STOCKCODE.length;num6++) {
      sqlBuf.append("       WHEN al.branchcode IS NULL AND id.stockcode='").append(SP_STOCKCODE[num6]).append("' THEN ").append(SIDBUtil.SQL2Str(SP_BRANCHCODE[num6]));
    }
    sqlBuf.append("       WHEN al.branchcode IS NULL THEN oh.branchcode ELSE al.branchcode END AS shipbranchcode ");
    sqlBuf.append("     , oh.branchcode AS orderbranchcode ");
    if(isOldRate()) {//200907以前のレート
      sqlBuf.append("     , CASE WHEN id.consigngoodsflg='1' OR id.usednewflg IN ('0','2','3') THEN ").append(USED_RATE);
      sqlBuf.append("            ELSE ").append(NEW_RATE).append(" END AS dividerate ");
    }else{//200908以降のレート
      sqlBuf.append("     , CASE WHEN id.consigngoodsflg='1' THEN ").append(USED_RATE);
      sqlBuf.append("            WHEN id.usednewflg IN ('0','2','3') THEN ").append(USED_RATE2);
      sqlBuf.append("            ELSE ").append(NEW_RATE).append(" END AS dividerate ");
    }
    if(type==0) {
      sqlBuf.append("     , CASE WHEN substr(id.individualcode,0,2) IN ('L','V','W','H') THEN substr(id.individualcode,0,2)  ");
      sqlBuf.append("       WHEN id.usednewflg = '7' THEN 'PB' WHEN id.usednewflg IN ('1','6') THEN 'NB' ELSE 'UD' END AS segment ");
    } else {
      sqlBuf.append("     , CASE WHEN substr(id.individualcode,0,2) IN ('L','V','W','H','N') THEN substr(id.individualcode,0,2)  ");
      sqlBuf.append("       WHEN id.usednewflg = '7' THEN 'PB' WHEN id.usednewflg IN ('1','6') THEN 'NB' ELSE 'UD' END AS segment ");
    }
    sqlBuf.append("FROM orderdetailtbl dt  ");
    sqlBuf.append("INNER JOIN individualtbl id  ");
    sqlBuf.append("ON (dt.cmdtycode=id.cmdtycode AND dt.individualcode=id.individualcode) ");
    sqlBuf.append("INNER JOIN orderdetailcosttbl dc  ");
    sqlBuf.append("ON (dt.ordercode=dc.ordercode AND dt.cmdtycode=dc.cmdtycode AND dt.individualcode=dc.individualcode) ");
    sqlBuf.append("LEFT OUTER JOIN  ");
    sqlBuf.append("(SELECT a.ordercode,a.detailcode,a.individualcode,b.branchname,a.orderallocationnumber,a.branchcode FROM orderallocationinfotbl a,branchtbl b WHERE a.branchcode=b.branchcode) al  ");
    sqlBuf.append("ON dt.ordercode=al.ordercode AND dt.individualcode=al.individualcode AND dt.detailcode=al.detailcode ");
    sqlBuf.append(",ordertbl oh ");
    sqlBuf.append(",custtbl cu,branchtbl br,chargetbl ch ");
    sqlBuf.append("WHERE dt.ordercode = oh.ordercode ");
    sqlBuf.append("  AND dt.orderbranchcode = oh.orderbranchcode  ");
    sqlBuf.append("  AND dc.initdatetime::date >= ").append(SIDBUtil.SQL2Str(this.getSeasonFrom()));
    sqlBuf.append("  AND dc.initdatetime::date <= ").append(SIDBUtil.SQL2Str(this.getSeasonTo()));
    if(SIUtil.isNotNull(this.getOrderRoute())){
      sqlBuf.append("  AND oh.orderroute = ").append(SIDBUtil.SQL2Str(this.getOrderRoute()));
    }
    sqlBuf.append("  AND oh.branchcode=br.branchcode ");
    sqlBuf.append("  AND oh.chargecode=ch.chargecode ");
    sqlBuf.append("  AND oh.custcode=cu.custcode ");
    sqlBuf.append("  AND oh.enabledflg = '1' ");
    sqlBuf.append("  AND id.consigngoodsflg = '0' ");
    
    // 4.売上送料データ
    sqlBuf.append("UNION ALL ");
    sqlBuf.append("SELECT dt.shippmentdate ");
    sqlBuf.append("     , oh.initdatetime::date ");
    sqlBuf.append("     , br.branchname AS shipbranch ");
    sqlBuf.append("     , br.branchname ");
    sqlBuf.append("     , ch.chargename ");
    sqlBuf.append("     , oh.ordercode ");
    sqlBuf.append("     , cu.custname ");
    sqlBuf.append("     , '' AS individualcode ");
    sqlBuf.append("     , '' AS cmdtyname ");
    sqlBuf.append("     , '' AS usednewflg ");
    sqlBuf.append("     , '売上' AS ordertype ");
    sqlBuf.append("     , 0 AS amount ");
    sqlBuf.append("     , 0 AS price ");
    sqlBuf.append("     , 0 AS totalofsellinctax ");
    sqlBuf.append("     , oh.totalofdeliveryfee + oh.discountdeliveryfee AS deliveryfee ");
    sqlBuf.append("     , oh.totaloffee + oh.discountfee AS feeinctax ");
    sqlBuf.append("     , TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) AS fee ");
    //EDBTG003-00 ohsugi mod start
//    sqlBuf.append("     , -1 * oh.sumofdiscount AS sumofdiscountinctax ");
    sqlBuf.append("     , -1 * (oh.sumofdiscount + oh.setdiscount) AS sumofdiscountinctax ");
//    sqlBuf.append("     , -1 * TRUNC(oh.sumofdiscount / (1 + (oh.taxrate / 100))) AS sumofdiscount ");
    sqlBuf.append("     , -1 * TRUNC((oh.sumofdiscount + oh.setdiscount) / (1 + (oh.taxrate / 100))) AS sumofdiscount ");
    //EDBTG003-00 ohsugi mod end
    sqlBuf.append("     , -1 * oh.sumbypoint AS sumbypoint ");
    //EDBTG003-00 ohsugi mod start
//    sqlBuf.append("     , oh.totaloffee + oh.discountfee - oh.sumofdiscount AS totalofpriceinctax ");
    sqlBuf.append("     , oh.totaloffee + oh.discountfee - (oh.sumofdiscount + oh.setdiscount) AS totalofpriceinctax ");
    //EDBTG003-00 ohsugi mod end
    sqlBuf.append("     , 0 AS unitcostinctax ");
    sqlBuf.append("     , 0 AS totalofcostinctax ");
    //EDBTG003-00 ohsugi mod start
//    sqlBuf.append("     , oh.totaloffee + oh.discountfee - oh.sumofdiscount AS totalofgaininctax ");
    sqlBuf.append("     , oh.totaloffee + oh.discountfee - (oh.sumofdiscount + oh.setdiscount) AS totalofgaininctax ");
//    sqlBuf.append("     , TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) - TRUNC(oh.sumofdiscount / (1 + (oh.taxrate / 100))) AS totalofprice ");
    sqlBuf.append("     , TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) - TRUNC((oh.sumofdiscount + oh.setdiscount) / (1 + (oh.taxrate / 100))) AS totalofprice ");
    //EDBTG003-00 ohsugi mod end
    sqlBuf.append("     , 0 AS totalofcost ");
    //EDBTG003-00 ohsugi mod start
//    sqlBuf.append("     , TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) - TRUNC(oh.sumofdiscount / (1 + (oh.taxrate / 100))) AS totalofgain ");
    sqlBuf.append("     , TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) - TRUNC((oh.sumofdiscount + oh.setdiscount) / (1 + (oh.taxrate / 100))) AS totalofgain ");
    //EDBTG003-00 ohsugi mod end
    sqlBuf.append("     , 1 AS gainrate ");
    sqlBuf.append("     , CASE oh.orderroute ");
    for (int i=0;i<SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length;i++) {
      sqlBuf.append(" WHEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][1]));
      sqlBuf.append(" THEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][0]));
    }
    sqlBuf.append(" ELSE ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length-1][0]));
    sqlBuf.append(" END AS orderroute ");
    /*
    sqlBuf.append("     , CASE oh.orderroute WHEN '0' THEN 'EC' WHEN '1' THEN '来店' WHEN '2' THEN '電話' ");
    sqlBuf.append("                          WHEN '3' THEN 'メール' WHEN '4' THEN 'FAX' WHEN '6' THEN '携帯' ");
    sqlBuf.append("                          WHEN '7' THEN '楽天' WHEN '8' THEN 'スマートフォン' WHEN '9' THEN 'イベント' ");
    sqlBuf.append("                          WHEN 'A' THEN 'イベントFAX' WHEN 'B' THEN 'ELG' WHEN 'C' THEN 'アプリ' ELSE 'その他' END AS orderroute ");
    */
    sqlBuf.append("     , oh.paymethodname ");
    sqlBuf.append("     , 4 AS sortkey ");
    sqlBuf.append("     , oh.taxRate ");
    sqlBuf.append("     , oh.branchcode::numeric AS shipbranchcode ");
    sqlBuf.append("     , oh.branchcode::numeric AS orderbranchcode ");
    sqlBuf.append("     , NULL::numeric AS dividerate ");
    sqlBuf.append("     , '' AS segment ");
    sqlBuf.append("FROM ordersumvw oh, ");
    sqlBuf.append("(SELECT ordercode,MAX(shippmentdate) AS shippmentdate FROM orderdetaillatestvw GROUP BY ordercode) dt ");
    sqlBuf.append(",custtbl cu,branchtbl br,chargetbl ch ");
    sqlBuf.append("WHERE oh.deliveryedflg + oh.nodeliveryflg = 1 ");
    sqlBuf.append("  AND oh.ordercode=dt.ordercode ");
    sqlBuf.append("  AND dt.shippmentdate >= ").append(SIDBUtil.SQL2Str(this.getSeasonFrom()));
    sqlBuf.append("  AND dt.shippmentdate <= ").append(SIDBUtil.SQL2Str(this.getSeasonTo()));
    if(SIUtil.isNotNull(this.getOrderRoute())){
      sqlBuf.append("  AND oh.orderroute = ").append(SIDBUtil.SQL2Str(this.getOrderRoute()));
    }
    sqlBuf.append("  AND oh.branchcode=br.branchcode ");
    sqlBuf.append("  AND oh.chargecode=ch.chargecode ");
    sqlBuf.append("  AND oh.custcode=cu.custcode ");
    sqlBuf.append("  AND oh.branchcode<>'17' ");
    
    // 5.返品明細データ
    sqlBuf.append("UNION ALL ");
    sqlBuf.append("SELECT oh.updatedatetime::date AS shippmentdate ");
    sqlBuf.append("     , oh.initdatetime::date ");
    sqlBuf.append("     , CASE ");
    for (int num7=0;num7<SP_STOCKCODE.length;num7++) {
      sqlBuf.append("       WHEN detail.branchname IS NULL AND id.stockcode='").append(SP_STOCKCODE[num7]).append("' THEN ").append(SIDBUtil.SQL2Str(SP_BRANCHNAME[num7]));
    }
    sqlBuf.append("       WHEN detail.branchname IS NULL THEN br.branchname ELSE detail.branchname END AS shipbranch ");
    sqlBuf.append("     , br.branchname ");
    sqlBuf.append("     , ch.chargename ");
    sqlBuf.append("     , oh.ordercode ");
    sqlBuf.append("     , cu.custname ");
    sqlBuf.append("     , detail.individualcode ");
    sqlBuf.append("     , detail.cmdtyname ");
    sqlBuf.append("     , CASE id.usednewflg WHEN '0' THEN '中古' WHEN '1' THEN '新品(NB)' WHEN '2' THEN '新古OUTLET' ");
    sqlBuf.append("                          WHEN '3' THEN 'BG認定中古' WHEN '4' THEN '展示品' WHEN '5' THEN '展示品(貸与品)' ");
    sqlBuf.append("                          WHEN '6' THEN 'キャンペーン' WHEN '7' THEN '新品(PB)' ELSE '中古' ");
    sqlBuf.append("       END AS usednewflg ");
    sqlBuf.append("     , '返品' AS ordertype ");
    /*
    sqlBuf.append("     , CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS amount ");
    sqlBuf.append("     , -1 * CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price END AS price ");
    sqlBuf.append("     , -1 * CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price END *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofsellinctax ");
    */
    sqlBuf.append("     , detail.amount ");
    sqlBuf.append("     , -1 * detail.totalofpriceinctax / detail.amount AS price ");
    sqlBuf.append("     , -1 * detail.totalofpriceinctax AS totalofsellinctax ");
    
    sqlBuf.append("     , 0 AS deliveryfee ");
    sqlBuf.append("     , 0 AS feeinctax ");
    sqlBuf.append("     , 0 AS fee ");
    sqlBuf.append("     , 0 AS sumofdiscountinctax ");
    sqlBuf.append("     , 0 AS sumofdiscount ");
    sqlBuf.append("     , 0 AS sumbypoint ");
    /*
    sqlBuf.append("     , -1 * CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price END *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofpriceinctax ");
    sqlBuf.append("     , -1 * CASE WHEN id.consigngoodsflg = '1' THEN 0 ELSE (coalesce(coalesce(ct.purchasepricenew,dt.purchaseprice),0) + coalesce(dt.processingexpence,0)) END AS unitcostinctax ");
    sqlBuf.append("     , -1 * CASE WHEN id.consigngoodsflg = '1' THEN 0 ELSE (coalesce(coalesce(ct.purchasepricenew,dt.purchaseprice),0) + coalesce(dt.processingexpence,0)) END *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofcostinctax ");
    sqlBuf.append("     , -1 * CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee  ");
    sqlBuf.append("       ELSE dt.price - (coalesce(coalesce(ct.purchasepricenew,dt.purchaseprice),0) + coalesce(dt.processingexpence,0)) END *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END AS totalofgaininctax ");
    sqlBuf.append("     , -1 * TRUNC(CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price END *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (oh.taxrate / 100))) AS totalofprice ");
    sqlBuf.append("     , CASE WHEN id.consigngoodsflg = '1' THEN 0 ELSE  ");
    sqlBuf.append("       -1 * TRUNC((coalesce(coalesce(ct.purchasepricenew,dt.purchaseprice),0) + coalesce(dt.processingexpence,0)) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (tax(dt.shippmentdate) / 100))) END AS totalofcost ");
    sqlBuf.append("     , -1 * (TRUNC(CASE WHEN id.consigngoodsflg = '1' THEN dt.consigngoodsfee ELSE dt.price END *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (oh.taxrate / 100))) ");
    sqlBuf.append("     - CASE WHEN id.consigngoodsflg = '1' THEN 0 ELSE  ");
    sqlBuf.append("       TRUNC((coalesce(coalesce(ct.purchasepricenew,dt.purchaseprice),0) + coalesce(dt.processingexpence,0)) *  ");
    sqlBuf.append("       CASE WHEN al.orderallocationnumber IS NULL THEN dt.amount ELSE al.orderallocationnumber END / (1 + (tax(dt.shippmentdate) / 100))) END) AS totalofgain ");
    */
    sqlBuf.append("     , -1 * detail.totalofpriceinctax AS totalofpriceinctax ");
    sqlBuf.append("     , -1 * detail.totalofcostinctax / detail.amount AS unitcostinctax ");
    sqlBuf.append("     , -1 * detail.totalofcostinctax AS totalofcostinctax ");
    sqlBuf.append("     , -1 * detail.totalofgaininctax AS totalofgaininctax ");
    sqlBuf.append("     , -1 * detail.totalofprice AS totalofprice ");
    sqlBuf.append("     , -1 * detail.totalofcost AS totalofcost ");
    sqlBuf.append("     , -1 * detail.totalofgain AS totalofgain ");
    
    sqlBuf.append("     , CASE WHEN id.consigngoodsflg = '1' THEN 1 WHEN detail.totalofprice = 0 THEN 0 ");
    sqlBuf.append("       ELSE 0.5 END AS gainrate ");
    sqlBuf.append("     , CASE oh.orderroute ");
    for (int i=0;i<SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length;i++) {
      sqlBuf.append(" WHEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][1]));
      sqlBuf.append(" THEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][0]));
    }
    sqlBuf.append(" ELSE ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length-1][0]));
    sqlBuf.append(" END AS orderroute ");
    /*
    sqlBuf.append("     , CASE oh.orderroute WHEN '0' THEN 'EC' WHEN '1' THEN '来店' WHEN '2' THEN '電話' ");
    sqlBuf.append("                          WHEN '3' THEN 'メール' WHEN '4' THEN 'FAX' WHEN '6' THEN '携帯' ");
    sqlBuf.append("                          WHEN '7' THEN '楽天' WHEN '8' THEN 'スマートフォン' WHEN '9' THEN 'イベント' ");
    sqlBuf.append("                          WHEN 'A' THEN 'イベントFAX' WHEN 'B' THEN 'ELG' WHEN 'C' THEN 'アプリ' ELSE 'その他' END AS orderroute ");
    */
    sqlBuf.append("     , oh.paymethodname ");
    sqlBuf.append("     , 5 AS sortkey ");
    sqlBuf.append("     , oh.taxRate ");
    sqlBuf.append("     , CASE ");
    for (int num8=0;num8<SP_STOCKCODE.length;num8++) {
      sqlBuf.append("       WHEN detail.branchcode IS NULL AND id.stockcode='").append(SP_STOCKCODE[num8]).append("' THEN ").append(SIDBUtil.SQL2Str(SP_BRANCHCODE[num8]));
    }
    sqlBuf.append("       WHEN detail.branchcode IS NULL THEN oh.branchcode ELSE detail.branchcode END AS shipbranchcode ");
    sqlBuf.append("     , oh.branchcode AS orderbranchcode ");
    if(isOldRate()) {//200907以前のレート
      sqlBuf.append("     , CASE WHEN id.consigngoodsflg='1' OR id.usednewflg IN ('0','2','3') THEN ").append(USED_RATE);
      sqlBuf.append("            ELSE ").append(NEW_RATE).append(" END AS dividerate ");
    }else{//200908以降のレート
      sqlBuf.append("     , CASE WHEN id.consigngoodsflg='1' THEN ").append(USED_RATE);
      sqlBuf.append("            WHEN id.usednewflg IN ('0','2','3') THEN ").append(USED_RATE2);
      sqlBuf.append("            ELSE ").append(NEW_RATE).append(" END AS dividerate ");
    }
    if(type==0) {
      sqlBuf.append("     , CASE WHEN substr(id.individualcode,0,2) IN ('L','V','W','H') THEN substr(id.individualcode,0,2)  ");
      sqlBuf.append("       WHEN id.usednewflg = '7' THEN 'PB' WHEN id.usednewflg IN ('1','6') THEN 'NB' ELSE 'UD' END AS segment ");
    } else {
      sqlBuf.append("     , CASE WHEN substr(id.individualcode,0,2) IN ('L','V','W','H','N') THEN substr(id.individualcode,0,2)  ");
      sqlBuf.append("       WHEN id.usednewflg = '7' THEN 'PB' WHEN id.usednewflg IN ('1','6') THEN 'NB' ELSE 'UD' END AS segment ");
    }
    sqlBuf.append("FROM statusdetailbgvw detail INNER JOIN individualtbl id ON (detail.individualcode=id.individualcode) ");
    sqlBuf.append("LEFT OUTER JOIN ");
    sqlBuf.append(" (SELECT a.ordercode,a.individualcode,a.purchasepricenew FROM orderdetailcosttbl a,");
    sqlBuf.append("  (SELECT ordercode,individualcode,MAX(correctcount) AS correctcount FROM orderdetailcosttbl GROUP BY ordercode,individualcode) b");
    sqlBuf.append("  WHERE a.ordercode = b.ordercode AND a.individualcode = b.individualcode AND a.correctcount = b.correctcount) ct ");
    sqlBuf.append("ON detail.ordercode = ct.ordercode AND detail.individualcode = ct.individualcode ");
    sqlBuf.append(",ordertbl oh ");
    sqlBuf.append(",custtbl cu,branchtbl br,chargetbl ch ");
    sqlBuf.append("WHERE detail.ordercode = oh.ordercode ");
    sqlBuf.append("  AND oh.updatedatetime::date >= ").append(SIDBUtil.SQL2Str(this.getSeasonFrom()));
    sqlBuf.append("  AND oh.updatedatetime::date <= ").append(SIDBUtil.SQL2Str(this.getSeasonTo()));
    if(SIUtil.isNotNull(this.getOrderRoute())){
      sqlBuf.append("  AND oh.orderroute = ").append(SIDBUtil.SQL2Str(this.getOrderRoute()));
    }
    sqlBuf.append("  AND oh.branchcode=br.branchcode ");
    sqlBuf.append("  AND oh.chargecode=ch.chargecode ");
    sqlBuf.append("  AND oh.custcode=cu.custcode ");
    sqlBuf.append("  AND oh.enabledflg = '1' ");
    sqlBuf.append("  AND oh.status = '2' ");
    
    // 6.返品送料データ
    sqlBuf.append("UNION ALL ");
    sqlBuf.append("SELECT oh.updatedatetime::date AS shippmentdate ");
    sqlBuf.append("     , oh.initdatetime::date ");
    sqlBuf.append("     , br.branchname AS shipbranch ");
    sqlBuf.append("     , br.branchname ");
    sqlBuf.append("     , ch.chargename ");
    sqlBuf.append("     , oh.ordercode ");
    sqlBuf.append("     , cu.custname ");
    sqlBuf.append("     , '' AS individualcode ");
    sqlBuf.append("     , '' AS cmdtyname ");
    sqlBuf.append("     , '' AS usednewflg ");
    sqlBuf.append("     , '返品' AS ordertype ");
    sqlBuf.append("     , 0 AS amount ");
    sqlBuf.append("     , 0 AS price ");
    sqlBuf.append("     , 0 AS totalofsellinctax ");
    sqlBuf.append("     , -1 * (oh.totalofdeliveryfee + oh.discountdeliveryfee) AS deliveryfee ");
    sqlBuf.append("     , -1 * (oh.totaloffee + oh.discountfee) AS feeinctax ");
    sqlBuf.append("     , -1 * TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) AS fee ");
    //EDBTG003-00 ohsugi mod start
//    sqlBuf.append("     , oh.sumofdiscount AS sumofdiscountinctax");
    sqlBuf.append("     , (oh.sumofdiscount + oh.setdiscount) AS sumofdiscountinctax");
//    sqlBuf.append("     , TRUNC(oh.sumofdiscount / (1 + (oh.taxrate / 100))) AS sumofdiscount ");
    sqlBuf.append("     , TRUNC((oh.sumofdiscount + oh.setdiscount) / (1 + (oh.taxrate / 100))) AS sumofdiscount ");
    //EDBTG003-00 ohsugi mod end
    sqlBuf.append("     , oh.sumbypoint  ");
    //EDBTG003-00 ohsugi mod start
//    sqlBuf.append("     , -1 * ((oh.totaloffee + oh.discountfee) - oh.sumofdiscount) AS totalofpriceinctax ");
    sqlBuf.append("     , -1 * ((oh.totaloffee + oh.discountfee) - (oh.sumofdiscount + oh.setdiscount)) AS totalofpriceinctax ");
    //EDBTG003-00 ohsugi mod end
    sqlBuf.append("     , 0 AS unitcostinctax ");
    sqlBuf.append("     , 0 AS totalofcostinctax ");
    //EDBTG003-00 ohsugi mod start
//    sqlBuf.append("     , -1 * ((oh.totaloffee + oh.discountfee) - oh.sumofdiscount) AS totalofgaininctax ");
    sqlBuf.append("     , -1 * ((oh.totaloffee + oh.discountfee) - (oh.sumofdiscount + oh.setdiscount)) AS totalofgaininctax ");
//    sqlBuf.append("     , -1 * (TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) - TRUNC(oh.sumofdiscount / (1 + (oh.taxrate / 100)))) AS totalofprice ");
    sqlBuf.append("     , -1 * (TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) - TRUNC((oh.sumofdiscount + oh.setdiscount) / (1 + (oh.taxrate / 100)))) AS totalofprice ");
    //EDBTG003-00 ohsugi mod end
    sqlBuf.append("     , 0 AS totalofcost ");
    //EDBTG003-00 ohsugi mod start
//    sqlBuf.append("     , -1 * (TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) - TRUNC(oh.sumofdiscount / (1 + (oh.taxrate / 100)))) AS totalofgain ");
    sqlBuf.append("     , -1 * (TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) - TRUNC((oh.sumofdiscount + oh.setdiscount) / (1 + (oh.taxrate / 100)))) AS totalofgain ");
    //EDBTG003-00 ohsugi mod end
    sqlBuf.append("     , 1 AS gainrate ");
    sqlBuf.append("     , CASE oh.orderroute ");
    for (int i=0;i<SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length;i++) {
      sqlBuf.append(" WHEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][1]));
      sqlBuf.append(" THEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][0]));
    }
    sqlBuf.append(" ELSE ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length-1][0]));
    sqlBuf.append(" END AS orderroute ");
    /*
    sqlBuf.append("     , CASE oh.orderroute WHEN '0' THEN 'EC' WHEN '1' THEN '来店' WHEN '2' THEN '電話' ");
    sqlBuf.append("                          WHEN '3' THEN 'メール' WHEN '4' THEN 'FAX' WHEN '6' THEN '携帯' ");
    sqlBuf.append("                          WHEN '7' THEN '楽天' WHEN '8' THEN 'スマートフォン' WHEN '9' THEN 'イベント' ");
    sqlBuf.append("                          WHEN 'A' THEN 'イベントFAX' WHEN 'B' THEN 'ELG' WHEN 'C' THEN 'アプリ' ELSE 'その他' END AS orderroute ");
    */
    sqlBuf.append("     , oh.paymethodname ");
    sqlBuf.append("     , 6 AS sortkey ");
    sqlBuf.append("     , oh.taxRate ");
    sqlBuf.append("     , oh.branchcode::numeric AS shipbranchcode ");
    sqlBuf.append("     , oh.branchcode::numeric AS orderbranchcode ");
    sqlBuf.append("     , NULL::numeric AS dividerate ");
    sqlBuf.append("     , '' AS segment ");
    sqlBuf.append("FROM ordersumvw oh,custtbl cu,branchtbl br,chargetbl ch ");
    sqlBuf.append("WHERE oh.status = '2' ");
    sqlBuf.append("  AND oh.updatedatetime::date >= ").append(SIDBUtil.SQL2Str(this.getSeasonFrom()));
    sqlBuf.append("  AND oh.updatedatetime::date <= ").append(SIDBUtil.SQL2Str(this.getSeasonTo()));
    if(SIUtil.isNotNull(this.getOrderRoute())){
      sqlBuf.append("  AND oh.orderroute = ").append(SIDBUtil.SQL2Str(this.getOrderRoute()));
    }
    sqlBuf.append("  AND oh.branchcode=br.branchcode ");
    sqlBuf.append("  AND oh.chargecode=ch.chargecode ");
    sqlBuf.append("  AND oh.custcode=cu.custcode ");
    sqlBuf.append("  AND oh.branchcode<>'17' ");
    
    // 7.ソート
    sqlBuf.append("ORDER BY shippmentdate,ordercode,sortkey,individualcode,shipbranchcode; ");
    
    return sqlBuf.toString();
  }
}