package jp.co.sint.beans.mallmgr;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.http.HttpServletRequest;

import jp.co.sint.basic.SIBasic;
import jp.co.sint.basic.SIMarketingReportData;
import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIFlagConf;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.tools.SICheckDataConf;
import jp.co.sint.tools.SICheckValid;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIURLParameter;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;

public class UIMarketingReport extends SIBasic {
  //ログ用のインスタンスの生成
  private static Category log=Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  // セグメント
  private final String[][] SEGMENT_M = {
    { "0", "UD"}
  , { "1", "AU"}
  , { "2", "NB"}
  , { "3", "PB"}
  /*
  , { "4", "H"}//エクステ・ヘア水物
  , { "5", "L"}//エステ水物
  , { "6", "N"}//ネイル・まつげ商材
  */
  , { "4", "UD2"}//水物・化粧品中古
  , { "5", "NB2"}//水物・化粧品NB
  , { "6", "PB2"}//水物・化粧品PB
  };
  
  private final String[][] SEGMENT_T = {
      { "0", "UD"}
    , { "1", "AU"}
    , { "2", "NB"}
    , { "3", "PB"}
    , { "4", "H"}//エクステ・ヘア水物
    , { "5", "L"}//エステ水物
    , { "6", "N"}//ネイル・まつげ商材
    };
  
  // 営業形態
  private final String[][] JOB = {
    { "0", "美容室" }
  , { "1", "エステサロン・SPA" }
  , { "2", "マッサージ・リラクゼーション・アロマ" }
  , { "3", "理容室" }
  , { "4", "ネイルサロン" }
  , { "5", "まつ毛サロン" }
  , { "6", "アートメイク" }
  , { "7", "クリニック・病院" }
  , { "8", "学校・スクール" }
  , { "9", "美容学生" }
  , { "10", "内装・設備関連会社" }
  , { "11", "問屋・ディーラー" }
  , { "12", "鍼灸院・接骨院" }
  , { "13", "楽天" }
  , { "14", "複合サロン/その他" }
  };
  
  private final String[][] ROUTE = {
      { "0", "0" }//EC
    , { "1", "1" }//来店
    , { "2", "2" }//電話
    , { "3", "3" }//メール
    , { "4", "4" }//FAX
    , { "5", "6" }//携帯
    , { "6", "8" }//スマートフォン
    , { "7", "9" }//イベント
    , { "8", "A" }//イベントFAX
    , { "9", "B" }//ELG
    , { "10", "C" }//アプリ
    , { "11", "7" }//楽天
    , { "12", "5" }//その他
  };
  
  // 支払い方法
  private final String[][] PAYMENT = {
    { "0","銀行振込"}
  , { "1","現金"}
  , { "2","カード"}
  , { "3","代金引換"}
  , { "4","店頭カード"}
  , { "5","ローン"}
  , { "6","リース"}
  , { "7","ポイント全額"}
  , { "8","楽天カード"}
  , { "9","楽天バンク"}
  , { "10","楽天代引"}
  , { "11","楽天銀振"}
  };
  
  // 月次インデックス
  private final String[][] MONTHINDEX = {
     { "0", "05" }
   , { "1", "06" }
   , { "2", "07" }
   , { "3", "08" }
   , { "4", "09" }
   , { "5", "10" }
   , { "6", "11" }
   , { "7", "12" }
   , { "8", "01" }
   , { "9", "02" }
   , { "10", "03" }
   , { "11", "04" }
  };
  
  String[][] ctgrySales = new String[13][SIConfig.CATEGORYORDER.length];
  String[][] ctgryGain = new String[13][SIConfig.CATEGORYORDER.length];
  String[][] segmentSales = new String[13][SEGMENT_M.length];
  String[][] segmentGain = new String[13][SEGMENT_M.length];
  String[][] segmentSalesSum = new String[13][2];
  String[][] segmentGainSum = new String[13][2];
  String[] feeSum = new String[13];
  String[] discountSum = new String[13];
  String[] custCount = new String[13];
  String[][] salesSum = new String[13][3];
  String[][] gainSum = new String[13][3];
  String[][] orderRouteSales = new String[13][ROUTE.length];
  String[][] orderRouteCust = new String[13][ROUTE.length];
  String[][] jobSales = new String[13][JOB.length];
  String[][] jobCust = new String[13][JOB.length];
  String[][] paymentSales = new String[13][PAYMENT.length];
  String[][] paymentCust = new String[13][PAYMENT.length];
  String[] newCustSales = new String[13];
  String[] newCustCount = new String[13];
  String[] oldCustSales = new String[13];
  String[] oldCustCount = new String[13];
  String[] memberCount = new String[13];
  String[][] attrCount = new String[13][JOB.length];
  String[] infoMailCount = new String[13];
  
  String[] storeHistoryData = new String[13];
  
  private String seasonYearCbo = "";
  
  private String branchCode = "";
  
  private String branchName = "";
  
  // 集計分類
  private int type = 0;
  
  public UIMarketingReport() {
    for (int a=0;a<13;a++) {
      for(int b=0;b<SIConfig.CATEGORYORDER.length;b++) {
        ctgrySales[a][b] = "0";
        ctgryGain[a][b] = "0";
      }
      for(int c=0;c<SEGMENT_M.length;c++) {
        segmentSales[a][c] = "0";
        segmentGain[a][c] = "0";
      }
      segmentSalesSum[a][0] = "0";
      segmentSalesSum[a][1] = "0";
      segmentGainSum[a][0] = "0";
      segmentGainSum[a][1] = "0";
      feeSum[a] = "0";
      discountSum[a] = "0";
      custCount[a] = "0";
      for(int d=0;d<3;d++) {
        salesSum[a][d] = "0";
        gainSum[a][d] = "0";
      }
      for(int e=0;e<ROUTE.length;e++){
        orderRouteSales[a][e] = "0";
        orderRouteCust[a][e] = "0";
      }
      for(int f=0;f<JOB.length;f++){
        jobSales[a][f] = "0";
        jobCust[a][f] = "0";
        attrCount[a][f] = "0";
      }
      for(int g=0;g<PAYMENT.length;g++){
        paymentSales[a][g] = "0";
        paymentCust[a][g] = "0";
      }
      newCustSales[a] = "0";
      newCustCount[a] = "0";
      oldCustSales[a] = "0";
      oldCustCount[a] = "0";
      memberCount[a] = "0";
      infoMailCount[a] = "0";
      storeHistoryData[a] = "0";
    }
  }
  
  public String[][] getCtgrySales() {
    return ctgrySales;
  }
  
  public String[][] getCtgryGain() {
    return ctgryGain;
  }
  
  public String[][] getSegmentSales() {
    return segmentSales;
  }
  
  public String[][] getSegmentGain() {
    return segmentGain;
  }
  
  public String[][] getSegmentSalesSum() {
    return segmentSalesSum;
  }
  
  public String[][] getSegmentGainSum() {
    return segmentGainSum;
  }
  
  public String[] getCustCount() {
    return custCount;
  }
  
  public String[][] getSalesSum() {
    return salesSum;
  }
  
  public String[][] getGainSum() {
    return gainSum;
  }
  
  public String[][] getOrderRouteSales() {
    return orderRouteSales;
  }
  
  public String[][] getOrderRouteCust() {
    return orderRouteCust;
  }
  
  public String[][] getJobSales() {
    return jobSales;
  }
  
  public String[][] getJobCust() {
    return jobCust;
  }
  
  public String[][] getPaymentSales() {
    return paymentSales;
  }
  
  public String[][] getPaymentCust() {
    return paymentCust;
  }
  
  public String[] getNewCustSales() {
    return newCustSales;
  }
  
  public String[] getNewCustCount() {
    return newCustCount;
  }
  
  public String[] getOldCustSales() {
    return oldCustSales;
  }
  
  public String[] getOldCustCount() {
    return oldCustCount;
  }
  
  public String[] getMemberCount() {
    return memberCount;
  }
  
  public String[][] getAttrCount() {
    return attrCount;
  }
  
  public String[] getInfoMailCount() {
    return infoMailCount;
  }
  
  public String[] getStoreHistoryData() {
    return storeHistoryData;
  }
  
  public String[] getFeeSum() {
    return feeSum;
  }
  
  public String[] getDiscountSum() {
    return discountSum;
  }
  
  public String getBranchCode() {
    return branchCode;
  }
  
  public void setBranchCode(String branchCode) {
    if (SIUtil.isNull(branchCode))
      branchCode = "";
    this.branchCode = branchCode;
  }
  
  public String getBranchName() {
    return branchName;
  }
  
  public void setBranchName(String branchName) {
    if (SIUtil.isNull(branchName))
      branchName = "";
    this.branchName = branchName;
  }
  
  public String getSeasonYearCbo() {
    return seasonYearCbo;
  }
  
  public void setSeasonYearCbo(String seasonYearCbo) {
    if (SIUtil.isNull(seasonYearCbo))
      seasonYearCbo = "";
    this.seasonYearCbo = seasonYearCbo;
  }
  
  public int getType() {
    return type;
  }
  
  public void setType(int type) {
    this.type = type;
  }
  
  public void init(SIURLParameter lUrlParam) {
    this.setSeasonYearCbo((String) lUrlParam.getParam("seasonYearCbo"));
    this.setBranchCode((String) lUrlParam.getParam("branchCode"));
  }
  
  public void validate(HttpServletRequest lRequest,SICustomErrors errors){
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    if (errors == null) errors=new SICustomErrors();
    
    SICheckValid.checkValid(errors, "年度", this.getSeasonYearCbo(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
    
    if (!errors.isEmpty()) lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY,errors);
  }
  
  private void setDetailData(String season,String keyWord1,String keyWord2,String value){
    String month = season.substring(4);
    for (int i=0;i<MONTHINDEX.length;i++){
      if(MONTHINDEX[i][1].equals(month)) {
        salesSum[i][0] = SIUtil.add(salesSum[i][0], value);
        salesSum[12][0] = SIUtil.add(salesSum[12][0], value);
        setCtgryData(i,keyWord1,value);
        setSegmentData(i,keyWord2,value);
        break;
      }
    }
  }
  
  private void setDetailGainData(String season,String keyWord1,String keyWord2,String value){
    String month = season.substring(4);
    for (int i=0;i<MONTHINDEX.length;i++){
      if(MONTHINDEX[i][1].equals(month)) {
        gainSum[i][0] = SIUtil.add(gainSum[i][0], value);
        gainSum[12][0] = SIUtil.add(gainSum[12][0], value);
        setCtgryGainData(i,keyWord1,value);
        setSegmentGainData(i,keyWord2,value);
        break;
      }
    }
  }
  
  private void setCtgryData(int index,String keyWord,String value){
    for (int i=0;i<SIConfig.CATEGORYORDER.length;i++){
      if ("U".equals(keyWord)) keyWord = "J";//エステジャンク品（U）はジャンク品（J）に統合する
      if (SIConfig.CATEGORYORDER[i][1].equals(keyWord)) {
        ctgrySales[index][Integer.parseInt(SIConfig.CATEGORYORDER[i][0])] = SIUtil.add(ctgrySales[index][Integer.parseInt(SIConfig.CATEGORYORDER[i][0])], value);
        ctgrySales[12][Integer.parseInt(SIConfig.CATEGORYORDER[i][0])] = SIUtil.add(ctgrySales[12][Integer.parseInt(SIConfig.CATEGORYORDER[i][0])], value);
        break;
      }
    }
  }
  
  private void setCtgryGainData(int index,String keyWord,String value){
    for (int i=0;i<SIConfig.CATEGORYORDER.length;i++){
      if ("U".equals(keyWord)) keyWord = "J";//エステジャンク品（U）はジャンク品（J）に統合する
      if (SIConfig.CATEGORYORDER[i][1].equals(keyWord)) {
        ctgryGain[index][Integer.parseInt(SIConfig.CATEGORYORDER[i][0])] = SIUtil.add(ctgryGain[index][Integer.parseInt(SIConfig.CATEGORYORDER[i][0])], value);
        ctgryGain[12][Integer.parseInt(SIConfig.CATEGORYORDER[i][0])] = SIUtil.add(ctgryGain[12][Integer.parseInt(SIConfig.CATEGORYORDER[i][0])], value);
        break;
      }
    }
  }
  
  private void setSegmentData(int index,String keyWord,String value){
    for (int i=0;i<SEGMENT_M.length;i++){
      if (SEGMENT_M[i][1].equals(keyWord)) {
        segmentSales[index][Integer.parseInt(SEGMENT_M[i][0])] = SIUtil.add(segmentSales[index][Integer.parseInt(SEGMENT_M[i][0])], value);
        segmentSales[12][Integer.parseInt(SEGMENT_M[i][0])] = SIUtil.add(segmentSales[12][Integer.parseInt(SEGMENT_M[i][0])], value);
        if (Integer.parseInt(SEGMENT_M[i][0]) > 3) {
          segmentSalesSum[index][1] = SIUtil.add(segmentSalesSum[index][1], value);
          segmentSalesSum[12][1] = SIUtil.add(segmentSalesSum[12][1], value);
        } else {
          segmentSalesSum[index][0] = SIUtil.add(segmentSalesSum[index][0], value);
          segmentSalesSum[12][0] = SIUtil.add(segmentSalesSum[12][0], value);
        }
        break;
      }
    }
  }
  
  private void setSegmentGainData(int index,String keyWord,String value){
    for (int i=0;i<SEGMENT_M.length;i++){
      if (SEGMENT_M[i][1].equals(keyWord)) {
        segmentGain[index][Integer.parseInt(SEGMENT_M[i][0])] = SIUtil.add(segmentGain[index][Integer.parseInt(SEGMENT_M[i][0])], value);
        segmentGain[12][Integer.parseInt(SEGMENT_M[i][0])] = SIUtil.add(segmentGain[12][Integer.parseInt(SEGMENT_M[i][0])], value);
        if (Integer.parseInt(SEGMENT_M[i][0]) > 3) {
          segmentGainSum[index][1] = SIUtil.add(segmentGainSum[index][1], value);
          segmentGainSum[12][1] = SIUtil.add(segmentGainSum[12][1], value);
        } else {
          segmentGainSum[index][0] = SIUtil.add(segmentGainSum[index][0], value);
          segmentGainSum[12][0] = SIUtil.add(segmentGainSum[12][0], value);
        }
        break;
      }
    }
  }
  
  private void setHeaderData(String season,String keyWord1,String keyWord2,String keyWord3,String value,String count){
    String month = season.substring(4);
    for (int i=0;i<MONTHINDEX.length;i++){
      if(MONTHINDEX[i][1].equals(month)) {
        salesSum[i][1] = SIUtil.add(salesSum[i][1], value);
        salesSum[12][1] = SIUtil.add(salesSum[12][1], value);
        custCount[i] = SIUtil.add(custCount[i], count);
        custCount[12] = SIUtil.add(custCount[12], count);
        setOrderRouteData(i,keyWord1,value,count);
        setJobData(i,keyWord2,value,count);
        setPaymentData(i,keyWord3,value,count);
      }
    }
  }
  
  private void setHeaderDataFee(String season,String keyWord1,String keyWord2,String keyWord3,String value,String count){
    String month = season.substring(4);
    for (int i=0;i<MONTHINDEX.length;i++){
      if(MONTHINDEX[i][1].equals(month)) {
        salesSum[i][1] = SIUtil.add(salesSum[i][1], value);
        salesSum[12][1] = SIUtil.add(salesSum[12][1], value);
        custCount[i] = SIUtil.add(custCount[i], count);
        custCount[12] = SIUtil.add(custCount[12], count);
        feeSum[i] = SIUtil.add(feeSum[i], value);
        feeSum[12] = SIUtil.add(feeSum[12], value);
        setOrderRouteData(i,keyWord1,value,count);
        setJobData(i,keyWord2,value,count);
        setPaymentData(i,keyWord3,value,count);
      }
    }
  }
  
  private void setHeaderDataDiscount(String season,String keyWord1,String keyWord2,String keyWord3,String value,String count){
    String month = season.substring(4);
    for (int i=0;i<MONTHINDEX.length;i++){
      if(MONTHINDEX[i][1].equals(month)) {
        salesSum[i][1] = SIUtil.add(salesSum[i][1], value);
        salesSum[12][1] = SIUtil.add(salesSum[12][1], value);
        custCount[i] = SIUtil.add(custCount[i], count);
        custCount[12] = SIUtil.add(custCount[12], count);
        discountSum[i] = SIUtil.add(discountSum[i], value);
        discountSum[12] = SIUtil.add(discountSum[12], value);
        setOrderRouteData(i,keyWord1,value,count);
        setJobData(i,keyWord2,value,count);
        setPaymentData(i,keyWord3,value,count);
      }
    }
  }
  
  private void setOrderRouteData(int index,String keyWord,String value,String count){
    for (int i=0;i<ROUTE.length;i++){
      if (ROUTE[i][1].equals(keyWord)) {
        orderRouteSales[index][Integer.parseInt(ROUTE[i][0])] = SIUtil.add(orderRouteSales[index][Integer.parseInt(ROUTE[i][0])], value);
        orderRouteSales[12][Integer.parseInt(ROUTE[i][0])] = SIUtil.add(orderRouteSales[12][Integer.parseInt(ROUTE[i][0])], value);
        orderRouteCust[index][Integer.parseInt(ROUTE[i][0])] = SIUtil.add(orderRouteCust[index][Integer.parseInt(ROUTE[i][0])], count);
        orderRouteCust[12][Integer.parseInt(ROUTE[i][0])] = SIUtil.add(orderRouteCust[12][Integer.parseInt(ROUTE[i][0])], count);
      }
    }
  }
  
  private void setJobData(int index,String keyWord,String value,String count){
    for (int i=0;i<JOB.length;i++){
      if (JOB[i][1].equals(keyWord)) {
        jobSales[index][Integer.parseInt(JOB[i][0])] = SIUtil.add(jobSales[index][Integer.parseInt(JOB[i][0])], value);
        jobSales[12][Integer.parseInt(JOB[i][0])] = SIUtil.add(jobSales[12][Integer.parseInt(JOB[i][0])], value);
        jobCust[index][Integer.parseInt(JOB[i][0])] = SIUtil.add(jobCust[index][Integer.parseInt(JOB[i][0])], count);
        jobCust[12][Integer.parseInt(JOB[i][0])] = SIUtil.add(jobCust[12][Integer.parseInt(JOB[i][0])], count);
      }
    }
  }
  
  private void setPaymentData(int index,String keyWord,String value,String count){
    for (int i=0;i<PAYMENT.length;i++){
      if (PAYMENT[i][1].equals(keyWord)) {
        paymentSales[index][Integer.parseInt(PAYMENT[i][0])] = SIUtil.add(paymentSales[index][Integer.parseInt(PAYMENT[i][0])], value);
        paymentSales[12][Integer.parseInt(PAYMENT[i][0])] = SIUtil.add(paymentSales[12][Integer.parseInt(PAYMENT[i][0])], value);
        paymentCust[index][Integer.parseInt(PAYMENT[i][0])] = SIUtil.add(paymentCust[index][Integer.parseInt(PAYMENT[i][0])], count);
        paymentCust[12][Integer.parseInt(PAYMENT[i][0])] = SIUtil.add(paymentCust[12][Integer.parseInt(PAYMENT[i][0])], count);
      }
    }
  }
  
  private void setCustData(String season,String keyWord,String value,String count){
    String month = season.substring(4);
    for (int i=0;i<MONTHINDEX.length;i++){
      if(MONTHINDEX[i][1].equals(month)) {
        salesSum[i][2] = SIUtil.add(salesSum[i][2], value);
        salesSum[12][2] = SIUtil.add(salesSum[12][2], value);
        if ("old".equals(keyWord)) {
          oldCustSales[i] = SIUtil.add(oldCustSales[i], value);
          oldCustSales[12] = SIUtil.add(oldCustSales[12], value);
          oldCustCount[i] = SIUtil.add(oldCustCount[i], count);
          oldCustCount[12] = SIUtil.add(oldCustCount[12], count);
        } else if ("new".equals(keyWord)) {
          newCustSales[i] = SIUtil.add(newCustSales[i], value);
          newCustSales[12] = SIUtil.add(newCustSales[12], value);
          newCustCount[i] = SIUtil.add(newCustCount[i], count);
          newCustCount[12] = SIUtil.add(newCustCount[12], count);
        }
      }
    }
  }
  
  private void setNewCustDataAll(String season,String value) {
    String month = season.substring(4);
    for (int i=0;i<MONTHINDEX.length;i++){
      if(MONTHINDEX[i][1].equals(month)) {
        memberCount[i] = SIUtil.add(memberCount[i], value);
        memberCount[12] = SIUtil.add(memberCount[12], value);
      }
    }
  }
  
  private void setNewCustDataAttr(String season,int index,String value) {
    String month = season.substring(4);
    for (int i=0;i<MONTHINDEX.length;i++){
      if(MONTHINDEX[i][1].equals(month)) {
        attrCount[i][index] = SIUtil.add(attrCount[i][index], value);
        attrCount[12][index] = SIUtil.add(attrCount[12][index], value);
      }
    }
  }
  
  private void setNewCustDataInfoMail(String season,String value) {
    String month = season.substring(4);
    for (int i=0;i<MONTHINDEX.length;i++){
      if(MONTHINDEX[i][1].equals(month)) {
        infoMailCount[i] = SIUtil.add(infoMailCount[i], value);
        infoMailCount[12] = SIUtil.add(infoMailCount[12], value);
      }
    }
  }
  
  private void setTrendData(String season,String keyWord,String value1,String value2) {
    String month = season.substring(4);
    for (int i=0;i<MONTHINDEX.length;i++){
      if (MONTHINDEX[i][1].equals(month)) {
        for (int j=0;j<SEGMENT_T.length;j++) {
          if ("discount".equals(keyWord)) {
            discountSum[i] = SIUtil.add(discountSum[i], value1, true);
            break;
          } else if ("fee".equals(keyWord)) {
            feeSum[i] = SIUtil.add(feeSum[i], value1, true);
            break;
          } else if (SEGMENT_T[j][1].equals(keyWord)) {
            segmentSales[i][j] = SIUtil.add(segmentSales[i][j], value1, true);
            segmentGain[i][j] = SIUtil.add(segmentGain[i][j], SIUtil.sub(value1, value2, true), true);
            if (Integer.parseInt(SEGMENT_T[j][0]) > 3) {
              segmentSalesSum[i][1] = SIUtil.add(segmentSalesSum[i][1], value1);
              segmentGainSum[i][1] = SIUtil.add(segmentGainSum[i][1], SIUtil.sub(value1, value2, true), true);
            } else {
              segmentSalesSum[i][0] = SIUtil.add(segmentSalesSum[i][0], value1);
              segmentGainSum[i][0] = SIUtil.add(segmentGainSum[i][0], SIUtil.sub(value1, value2, true), true);
            }
            break;
          }
        }
        break;
      }
    }
  }
  
  private void setStoreHistoryData(String season,String value) {
    String month = season.substring(4);
    for (int i=0;i<MONTHINDEX.length;i++){
      if (MONTHINDEX[i][1].equals(month)) {
        storeHistoryData[i] = SIUtil.add(storeHistoryData[i], value, true);
        break;
      }
    }
  }
  
  public String getCtgryRate(int index1,int index2) {
    String total = salesSum[index1][0];
    String part = ctgrySales[index1][index2];
    if (SIUtil.isNull(total) || total.equals("0")) return "0";
    double res = Double.parseDouble(part) / Double.parseDouble(total);
    return String.valueOf(res);
  }
  
  public String getCtgryGainRate(int index1,int index2) {
    String total = gainSum[index1][0];
    String part = ctgryGain[index1][index2];
    if (SIUtil.isNull(total) || total.equals("0")) return "0";
    double res = Double.parseDouble(part) / Double.parseDouble(total);
    return String.valueOf(res);
  }
  
  public String getSegmentRate(int index1,int index2) {
    String total = salesSum[index1][0];
    String part = segmentSales[index1][index2];
    if (SIUtil.isNull(total) || total.equals("0")) return "0";
    double res = Double.parseDouble(part) / Double.parseDouble(total);
    return String.valueOf(res);
  }
  
  public String getSegmentGainRate(int index1,int index2) {
    String total = gainSum[index1][0];
    String part = segmentGain[index1][index2];
    if (SIUtil.isNull(total) || total.equals("0")) return "0";
    double res = Double.parseDouble(part) / Double.parseDouble(total);
    return String.valueOf(res);
  }
  
  public String getCustUnitPrice(int index) {
    String price = SIUtil.add(SIUtil.add(getDiscountSum()[index], getFeeSum()[index]), getSalesSum()[index][0]);
    String cust = custCount[index];
    if (SIUtil.isNull(cust) || cust.equals("0")) return "0";
    double res = Double.parseDouble(price) / Double.parseDouble(cust);
    return String.valueOf(res);
  }
  
  public String getCustUnitGain(int index) {
    String price = SIUtil.add(SIUtil.add(getDiscountSum()[index], getFeeSum()[index]), getGainSum()[index][0]);
    String cust = custCount[index];
    if (SIUtil.isNull(cust) || cust.equals("0")) return "0";
    double res = Double.parseDouble(price) / Double.parseDouble(cust);
    return String.valueOf(res);
  }
  
  public String getOrderRouteSalesRate(int index1,int index2) {
    String total = salesSum[index1][1];
    String part = orderRouteSales[index1][index2];
    if (SIUtil.isNull(total) || total.equals("0")) return "0";
    double res = Double.parseDouble(part) / Double.parseDouble(total);
    return String.valueOf(res);
  }
  
  public String getOrderRouteCustRate(int index1,int index2) {
    String total = custCount[index1];
    String part = orderRouteCust[index1][index2];
    if (SIUtil.isNull(total) || total.equals("0")) return "0";
    double res = Double.parseDouble(part) / Double.parseDouble(total);
    return String.valueOf(res);
  }
  
  public String getOrderRouteCustUnitPrice(int index1,int index2) {
    String price = orderRouteSales[index1][index2];
    String cust = orderRouteCust[index1][index2];
    if (SIUtil.isNull(cust) || cust.equals("0")) return "0";
    double res = Double.parseDouble(price) / Double.parseDouble(cust);
    return String.valueOf(res);
  }
  
  public String getJobSalesRate(int index1,int index2) {
    String total = salesSum[index1][1];
    String part = jobSales[index1][index2];
    if (SIUtil.isNull(total) || total.equals("0")) return "0";
    double res = Double.parseDouble(part) / Double.parseDouble(total);
    return String.valueOf(res);
  }
  
  public String getJobCustRate(int index1,int index2) {
    String total = custCount[index1];
    String part = jobCust[index1][index2];
    if (SIUtil.isNull(total) || total.equals("0")) return "0";
    double res = Double.parseDouble(part) / Double.parseDouble(total);
    return String.valueOf(res);
  }
  
  public String getJobCustUnitPrice(int index1,int index2) {
    String price = jobSales[index1][index2];
    String cust = jobCust[index1][index2];
    if (SIUtil.isNull(cust) || cust.equals("0")) return "0";
    double res = Double.parseDouble(price) / Double.parseDouble(cust);
    return String.valueOf(res);
  }
  
  public String getPaymentSalesRate(int index1,int index2) {
    String total = salesSum[index1][1];
    String part = paymentSales[index1][index2];
    if (SIUtil.isNull(total) || total.equals("0")) return "0";
    double res = Double.parseDouble(part) / Double.parseDouble(total);
    return String.valueOf(res);
  }
  
  public String getPaymentCustRate(int index1,int index2) {
    String total = custCount[index1];
    String part = paymentCust[index1][index2];
    if (SIUtil.isNull(total) || total.equals("0")) return "0";
    double res = Double.parseDouble(part) / Double.parseDouble(total);
    return String.valueOf(res);
  }
  
  public String getPaymentCustUnitPrice(int index1,int index2) {
    String price = paymentSales[index1][index2];
    String cust = paymentCust[index1][index2];
    if (SIUtil.isNull(cust) || cust.equals("0")) return "0";
    double res = Double.parseDouble(price) / Double.parseDouble(cust);
    return String.valueOf(res);
  }
  
  public String getRepeatRate(int index) {
    String total = SIUtil.add(newCustCount[index], oldCustCount[index]);
    String part = oldCustCount[index];
    if (SIUtil.isNull(total) || total.equals("0")) return "0";
    double res = Double.parseDouble(part) / Double.parseDouble(total);
    return String.valueOf(res);
  }
  
  public String getNewCustUnitPrice(int index) {
    String price = newCustSales[index];
    String cust = newCustCount[index];
    if (SIUtil.isNull(cust) || cust.equals("0")) return "0";
    double res = Double.parseDouble(price) / Double.parseDouble(cust);
    return String.valueOf(res);
  }
  
  public String getOldCustUnitPrice(int index) {
    String price = oldCustSales[index];
    String cust = oldCustCount[index];
    if (SIUtil.isNull(cust) || cust.equals("0")) return "0";
    double res = Double.parseDouble(price) / Double.parseDouble(cust);
    return String.valueOf(res);
  }
  
  public void execMarketingData(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    SIMarketingReportData lBasic = new SIMarketingReportData();
    StringBuffer lSqlBuf = new StringBuffer();
    
    // 明細単位集計（大カテゴリ文字、中古新品）
    lSqlBuf.append("(SELECT 0 AS flg");
    lSqlBuf.append(",ROUND(sum(a.totalofprice) / 1000) AS totalofprice");
    lSqlBuf.append(",ROUND(sum(a.totalofgain) / 1000) AS totalofgain");
    lSqlBuf.append(",0::numeric AS custcount");
    lSqlBuf.append(",substr(a.individualcode,1,1)::text as keyword1");
    if (type==0) {
      lSqlBuf.append(",CASE WHEN substr(a.individualcode,1,1) IN ('H','V','L','W') AND i.usednewflg IN ('1','6') THEN 'NB2'");
      lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('H','V','L','W') AND i.usednewflg = '7' THEN 'PB2'");
      lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('H','V','L','W') THEN 'UD2'");
    } else {
      lSqlBuf.append(",CASE WHEN substr(a.individualcode,1,1) IN ('H','V','L','W','N') AND i.usednewflg IN ('1','6') THEN 'NB2'");
      lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('H','V','L','W','N') AND i.usednewflg = '7' THEN 'PB2'");
      lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('H','V','L','W','N') THEN 'UD2'");
    }
    lSqlBuf.append(" WHEN i.usednewflg IN ('2','4') THEN 'AU'");// アウトレット
    lSqlBuf.append(" WHEN i.usednewflg IN ('1','6') THEN 'NB'");// ナチュラルブランド
    lSqlBuf.append(" WHEN i.usednewflg = '7' THEN 'PB'");// プライベートブランド
    lSqlBuf.append(" ELSE 'UD' END AS keyword2");// 中古
    lSqlBuf.append(",'' AS keyword3");
    lSqlBuf.append(",to_char(a.shippmentdate::timestamp,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM reportdetailbgvw AS a,individualtbl i ");
    lSqlBuf.append("WHERE a.individualcode = i.individualcode ");
    if(SIUtil.isNotNull(branchCode)&&branchCode.equals("tokyo")){
      lSqlBuf.append("AND a.branchcode IN ('1','2','3') ");
    }else if(SIUtil.isNotNull(branchCode)){
      lSqlBuf.append("AND a.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
    }
    lSqlBuf.append("AND to_char(a.shippmentdate::timestamp,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM," "));
    lSqlBuf.append("AND to_char(a.shippmentdate::timestamp,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO," "));
    if (type==0) {
      lSqlBuf.append("GROUP BY CASE WHEN substr(a.individualcode,1,1) IN ('H','V','L','W') AND i.usednewflg IN ('1','6') THEN 'NB2'");
      lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('H','V','L','W') AND i.usednewflg = '7' THEN 'PB2'");
      lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('H','V','L','W') THEN 'UD2'");
    } else {
      lSqlBuf.append("GROUP BY CASE WHEN substr(a.individualcode,1,1) IN ('H','V','L','W','N') AND i.usednewflg IN ('1','6') THEN 'NB2'");
      lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('H','V','L','W','N') AND i.usednewflg = '7' THEN 'PB2'");
      lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('H','V','L','W','N') THEN 'UD2'");
    }
    lSqlBuf.append(" WHEN i.usednewflg IN ('2','4') THEN 'AU'");// アウトレット
    lSqlBuf.append(" WHEN i.usednewflg IN ('1','6') THEN 'NB'");// ナチュラルブランド
    lSqlBuf.append(" WHEN i.usednewflg = '7' THEN 'PB'");// プライベートブランド
    lSqlBuf.append(" ELSE 'UD' END ");// 中古
    lSqlBuf.append(",substr(a.individualcode,1,1),to_char(a.shippmentdate::timestamp,'yyyymm')) ");
    
    lSqlBuf.append("UNION ALL ");
    lSqlBuf.append("(SELECT 0 AS flg");
    lSqlBuf.append(",-1 * ROUND(sum(a.totalofprice) / 1000) AS totalofprice");
    lSqlBuf.append(",-1 * ROUND(sum(a.totalofgain) / 1000) AS totalofgain");
    lSqlBuf.append(",0::numeric AS custcount");
    lSqlBuf.append(",substr(a.individualcode,1,1)::text as keyword1");
    if (type==0) {
      lSqlBuf.append(",CASE WHEN substr(a.individualcode,1,1) IN ('H','V','L','W') AND i.usednewflg IN ('1','6') THEN 'NB2'");
      lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('H','V','L','W') AND i.usednewflg = '7' THEN 'PB2'");
      lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('H','V','L','W') THEN 'UD2'");
    } else {
      lSqlBuf.append(",CASE WHEN substr(a.individualcode,1,1) IN ('H','V','L','W','N') AND i.usednewflg IN ('1','6') THEN 'NB2'");
      lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('H','V','L','W','N') AND i.usednewflg = '7' THEN 'PB2'");
      lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('H','V','L','W','N') THEN 'UD2'");
    }
    lSqlBuf.append(" WHEN i.usednewflg IN ('2','4') THEN 'AU'");// アウトレット
    lSqlBuf.append(" WHEN i.usednewflg IN ('1','6') THEN 'NB'");// ナチュラルブランド
    lSqlBuf.append(" WHEN i.usednewflg = '7' THEN 'PB'");// プライベートブランド
    lSqlBuf.append(" ELSE 'UD' END AS keyword2");// 中古
    lSqlBuf.append(",'' AS keyword3");
    lSqlBuf.append(",to_char(a.updatedatetime,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM reportdetailbgvw AS a,individualtbl i ");
    lSqlBuf.append("WHERE a.individualcode = i.individualcode AND a.status = '2' ");
    if(SIUtil.isNotNull(branchCode)&&branchCode.equals("tokyo")){
      lSqlBuf.append("AND a.branchcode IN ('1','2','3') ");
    }else if(SIUtil.isNotNull(branchCode)){
      lSqlBuf.append("AND a.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
    }
    lSqlBuf.append("AND to_char(a.updatedatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM," "));
    lSqlBuf.append("AND to_char(a.updatedatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO," "));
    if (type==0) {
      lSqlBuf.append("GROUP BY CASE WHEN substr(a.individualcode,1,1) IN ('H','V','L','W') AND i.usednewflg IN ('1','6') THEN 'NB2'");
      lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('H','V','L','W') AND i.usednewflg = '7' THEN 'PB2'");
      lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('H','V','L','W') THEN 'UD2'");
    } else {
      lSqlBuf.append("GROUP BY CASE WHEN substr(a.individualcode,1,1) IN ('H','V','L','W','N') AND i.usednewflg IN ('1','6') THEN 'NB2'");
      lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('H','V','L','W','N') AND i.usednewflg = '7' THEN 'PB2'");
      lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('H','V','L','W','N') THEN 'UD2'");
    }
    lSqlBuf.append(" WHEN i.usednewflg IN ('2','4') THEN 'AU'");// アウトレット
    lSqlBuf.append(" WHEN i.usednewflg IN ('1','6') THEN 'NB'");// ナチュラルブランド
    lSqlBuf.append(" WHEN i.usednewflg = '7' THEN 'PB'");// プライベートブランド
    lSqlBuf.append(" ELSE 'UD' END ");// 中古
    lSqlBuf.append(",substr(a.individualcode,1,1),to_char(a.updatedatetime,'yyyymm')) ");
    // 受注単位集計（経路、営業形態、支払方法）
    lSqlBuf.append("UNION ");
    lSqlBuf.append("(SELECT 1 AS flg");
    lSqlBuf.append(",ROUND(sum(a.totalofprice) / 1000) AS totalofprice");
    lSqlBuf.append(",ROUND(sum(a.totalofgain) / 1000) AS totalofgain");
    lSqlBuf.append(",count(distinct a.ordercode) AS custcount");
    lSqlBuf.append(",a.orderroute as keyword1");
    lSqlBuf.append(",coalesce(a.job,'その他') as keyword2");
    lSqlBuf.append(",a.paymethodname as keyword3");
    lSqlBuf.append(",to_char(a.shippmentdate::timestamp,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM reportdetailbgvw AS a,individualtbl i ");
    lSqlBuf.append("WHERE a.individualcode = i.individualcode ");
    if(SIUtil.isNotNull(branchCode)&&branchCode.equals("tokyo")){
      lSqlBuf.append("AND a.branchcode IN ('1','2','3') ");
    }else if(SIUtil.isNotNull(branchCode)){
      lSqlBuf.append("AND a.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
    }
    lSqlBuf.append("AND to_char(a.shippmentdate::timestamp,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM," "));
    lSqlBuf.append("AND to_char(a.shippmentdate::timestamp,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO," "));
    lSqlBuf.append("GROUP BY a.orderroute,a.job,a.paymethodname,to_char(a.shippmentdate::timestamp,'yyyymm')) ");
    
    lSqlBuf.append("UNION ALL ");
    lSqlBuf.append("(SELECT 1 AS flg");
    lSqlBuf.append(",-1 * ROUND(sum(a.totalofprice) / 1000) AS totalofprice");
    lSqlBuf.append(",-1 * ROUND(sum(a.totalofgain) / 1000) AS totalofgain");
    lSqlBuf.append(",-1 * count(distinct a.ordercode) AS custcount");
    lSqlBuf.append(",a.orderroute as keyword1");
    lSqlBuf.append(",coalesce(a.job,'その他') as keyword2");
    lSqlBuf.append(",a.paymethodname as keyword3");
    lSqlBuf.append(",to_char(a.updatedatetime,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM reportdetailbgvw AS a,individualtbl i ");
    lSqlBuf.append("WHERE a.individualcode = i.individualcode AND a.status = '2' ");
    if(SIUtil.isNotNull(branchCode)&&branchCode.equals("tokyo")){
      lSqlBuf.append("AND a.branchcode IN ('1','2','3') ");
    }else if(SIUtil.isNotNull(branchCode)){
      lSqlBuf.append("AND a.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
    }
    lSqlBuf.append(" AND to_char(a.updatedatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(a.updatedatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" GROUP BY a.orderroute,a.job,a.paymethodname,to_char(a.updatedatetime,'yyyymm')) ");
    // 新顧客集計（new）当月内登録顧客＝新規
    lSqlBuf.append("UNION ");
    lSqlBuf.append("(SELECT 2 AS flg");
    lSqlBuf.append(",ROUND(sum(a.totalofprice) / 1000) AS totalofprice");
    lSqlBuf.append(",ROUND(sum(a.totalofgain) / 1000) AS totalofgain");
    lSqlBuf.append(",count(distinct a.ordercode) AS custcount");
    lSqlBuf.append(",'new' as keyword1");
    lSqlBuf.append(",'' as keyword2");
    lSqlBuf.append(",'' as keyword3");
    lSqlBuf.append(",to_char(a.shippmentdate::timestamp,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM reportdetailbgvw AS a,individualtbl i,custinitdatevw c ");
    lSqlBuf.append("WHERE a.individualcode = i.individualcode ");
    lSqlBuf.append("AND a.custcode = c.custcode ");
    lSqlBuf.append("AND to_char(a.shippmentdate::timestamp,'yyyymm') = to_char(c.initdatetime,'yyyymm') ");
    if(SIUtil.isNotNull(branchCode)&&branchCode.equals("tokyo")){
      lSqlBuf.append("AND a.branchcode IN ('1','2','3') ");
    }else if(SIUtil.isNotNull(branchCode)){
      lSqlBuf.append("AND a.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
    }
    lSqlBuf.append(" AND to_char(a.shippmentdate::timestamp,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(a.shippmentdate::timestamp,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" GROUP BY a.paymethodname,to_char(a.shippmentdate::timestamp,'yyyymm'))");
    
    lSqlBuf.append("UNION ALL ");
    lSqlBuf.append("(SELECT 2 AS flg");
    lSqlBuf.append(",-1 * ROUND(sum(a.totalofprice) / 1000) AS totalofprice");
    lSqlBuf.append(",-1 * ROUND(sum(a.totalofgain) / 1000) AS totalofgain");
    lSqlBuf.append(",-1 * count(distinct a.ordercode) AS custcount");
    lSqlBuf.append(",'new' as keyword");
    lSqlBuf.append(",'' as keyword2");
    lSqlBuf.append(",'' as keyword3");
    lSqlBuf.append(",to_char(a.updatedatetime,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM reportdetailbgvw AS a,individualtbl i,custinitdatevw c ");
    lSqlBuf.append("WHERE a.individualcode = i.individualcode AND a.status = '2' ");
    lSqlBuf.append("AND a.custcode = c.custcode ");
    lSqlBuf.append("AND to_char(a.updatedatetime,'yyyymm') = to_char(c.initdatetime,'yyyymm') ");
    if(SIUtil.isNotNull(branchCode)&&branchCode.equals("tokyo")){
      lSqlBuf.append("AND a.branchcode IN ('1','2','3') ");
    }else if(SIUtil.isNotNull(branchCode)){
      lSqlBuf.append("AND a.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
    }
    lSqlBuf.append(" AND to_char(a.updatedatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(a.updatedatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" GROUP BY a.paymethodname,to_char(a.updatedatetime,'yyyymm'))");
    // 旧顧客集計（old）当月以前登録顧客＝既存
    lSqlBuf.append("UNION ALL ");
    lSqlBuf.append("(SELECT 2 AS flg");
    lSqlBuf.append(",ROUND(sum(a.totalofprice) / 1000) AS totalofprice");
    lSqlBuf.append(",ROUND(sum(a.totalofgain) / 1000) AS totalofgain");
    lSqlBuf.append(",count(distinct a.ordercode) AS custcount");
    lSqlBuf.append(",'old' as keyword");
    lSqlBuf.append(",'' as keyword2");
    lSqlBuf.append(",'' as keyword3");
    lSqlBuf.append(",to_char(a.shippmentdate::timestamp,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM reportdetailbgvw AS a,individualtbl i,custinitdatevw c ");
    lSqlBuf.append("WHERE a.individualcode = i.individualcode ");
    lSqlBuf.append("AND a.custcode = c.custcode ");
    lSqlBuf.append("AND to_char(a.shippmentdate::timestamp,'yyyymm') > to_char(c.initdatetime,'yyyymm') ");
    if(SIUtil.isNotNull(branchCode)&&branchCode.equals("tokyo")){
      lSqlBuf.append("AND a.branchcode IN ('1','2','3') ");
    }else if(SIUtil.isNotNull(branchCode)){
      lSqlBuf.append("AND a.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
    }
    lSqlBuf.append("AND to_char(a.shippmentdate::timestamp,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM," "));
    lSqlBuf.append("AND to_char(a.shippmentdate::timestamp,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO," "));
    lSqlBuf.append("GROUP BY a.paymethodname,to_char(a.shippmentdate::timestamp,'yyyymm')) ");
    
    lSqlBuf.append("UNION ALL ");
    lSqlBuf.append("(SELECT 2 AS flg");
    lSqlBuf.append(",-1 * ROUND(sum(a.totalofprice) / 1000) AS totalofprice");
    lSqlBuf.append(",-1 * ROUND(sum(a.totalofgain) / 1000) AS totalofgain");
    lSqlBuf.append(",-1 * count(distinct a.ordercode) AS custcount");
    lSqlBuf.append(",'old' as keyword");
    lSqlBuf.append(",'' as keyword2");
    lSqlBuf.append(",'' as keyword3");
    lSqlBuf.append(",to_char(a.updatedatetime,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM reportdetailbgvw AS a,individualtbl i,custinitdatevw c ");
    lSqlBuf.append("WHERE a.individualcode = i.individualcode AND a.status = '2' ");
    lSqlBuf.append("AND a.custcode = c.custcode ");
    lSqlBuf.append("AND to_char(a.updatedatetime,'yyyymm') > to_char(c.initdatetime,'yyyymm') ");
    if(SIUtil.isNotNull(branchCode)&&branchCode.equals("tokyo")){
      lSqlBuf.append("AND a.branchcode IN ('1','2','3') ");
    }else if(SIUtil.isNotNull(branchCode)){
      lSqlBuf.append("AND a.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
    }
    lSqlBuf.append("AND to_char(a.updatedatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM," "));
    lSqlBuf.append("AND to_char(a.updatedatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO," "));
    lSqlBuf.append("GROUP BY a.paymethodname,to_char(a.updatedatetime,'yyyymm')) ");
    // 手数料集計
    lSqlBuf.append("UNION ALL ");
    lSqlBuf.append("(SELECT 3 AS flg");
    lSqlBuf.append(",ROUND(sum(TRUNC((b.fee + coalesce(b.discountfee,0)) / (1 + (b.taxrate / 100)))) / 1000) AS totalofprice");
    lSqlBuf.append(",ROUND(sum(TRUNC((b.fee + coalesce(b.discountfee,0)) / (1 + (b.taxrate / 100)))) / 1000) AS totalofgain");
    lSqlBuf.append(",0 AS custcount");
    lSqlBuf.append(",b.orderroute as keyword1");
    lSqlBuf.append(",coalesce(b.job,'その他') as keyword2");
    lSqlBuf.append(",b.paymethodname as keyword3");
    lSqlBuf.append(",to_char(a.shippmentdate::timestamp,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM (SELECT ordercode,orderbranchcode,MAX(shippmentdate) AS shippmentdate FROM orderdetailtbl GROUP BY ordercode,orderbranchcode) AS a,");
    lSqlBuf.append("ordersumvw AS b ");
    lSqlBuf.append("WHERE a.ordercode = b.ordercode AND b.status IN('1','2') AND b.deliveryedflg+b.nodeliveryflg=1 ");
    if(SIUtil.isNotNull(branchCode)&&branchCode.equals("tokyo")){
      lSqlBuf.append("AND b.branchcode IN ('1','2','3') ");
    }else if(SIUtil.isNotNull(branchCode)){
      lSqlBuf.append("AND b.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
    }
    lSqlBuf.append("AND a.orderbranchcode = b.orderbranchcode ");
    lSqlBuf.append("AND b.branchcode <> '17' ");
    lSqlBuf.append("AND b.chargecode IS NOT NULL ");
    lSqlBuf.append("AND to_char(a.shippmentdate::timestamp,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM," "));
    lSqlBuf.append("AND to_char(a.shippmentdate::timestamp,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO," "));
    lSqlBuf.append("GROUP BY b.orderroute,b.job,b.paymethodname,to_char(a.shippmentdate::timestamp,'yyyymm')) ");
    lSqlBuf.append("UNION ALL ");
    lSqlBuf.append("(SELECT 3 AS flg");
    lSqlBuf.append(",-1 * ROUND(sum(TRUNC((b.fee + coalesce(b.discountfee,0)) / (1 + (b.taxrate / 100)))) / 1000) AS totalofprice");
    lSqlBuf.append(",-1 * ROUND(sum(TRUNC((b.fee + coalesce(b.discountfee,0)) / (1 + (b.taxrate / 100)))) / 1000) AS totalofgain");
    lSqlBuf.append(",0 AS custcount");
    lSqlBuf.append(",b.orderroute as keyword1");
    lSqlBuf.append(",coalesce(b.job,'その他') as keyword2");
    lSqlBuf.append(",b.paymethodname as keyword3");
    lSqlBuf.append(",to_char(b.updatedatetime,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM ordersumvw AS b ");
    lSqlBuf.append("WHERE b.status='2' ");
    if(SIUtil.isNotNull(branchCode)&&branchCode.equals("tokyo")){
      lSqlBuf.append("AND b.branchcode IN ('1','2','3') ");
    }else if(SIUtil.isNotNull(branchCode)){
      lSqlBuf.append("AND b.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
    }
    lSqlBuf.append("AND b.branchcode <> '17' ");
    lSqlBuf.append("AND b.chargecode IS NOT NULL ");
    lSqlBuf.append("AND to_char(b.updatedatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM," "));
    lSqlBuf.append("AND to_char(b.updatedatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO," "));
    lSqlBuf.append("GROUP BY b.orderroute,b.job,b.paymethodname,to_char(b.updatedatetime,'yyyymm')) ");
    // 値引き集計
    lSqlBuf.append("UNION ALL ");
    lSqlBuf.append("(SELECT 4 AS flg");
    //EDBTG003-00 ohsugi mod start
//    lSqlBuf.append(",-1 * ROUND(sum(TRUNC(b.sumofdiscount / (1 + (b.taxrate / 100)))) / 1000) AS totalofprice");
    lSqlBuf.append(",-1 * ROUND(sum(TRUNC((b.sumofdiscount + b.setdiscount) / (1 + (b.taxrate / 100)))) / 1000) AS totalofprice");
//    lSqlBuf.append(",-1 * ROUND(sum(TRUNC(b.sumofdiscount / (1 + (b.taxrate / 100)))) / 1000) AS totalofgain");
    lSqlBuf.append(",-1 * ROUND(sum(TRUNC((b.sumofdiscount + b.setdiscount) / (1 + (b.taxrate / 100)))) / 1000) AS totalofgain");
    //EDBTG003-00 ohsugi mod end
    lSqlBuf.append(",0 AS custcount");
    lSqlBuf.append(",b.orderroute as keyword1");
    lSqlBuf.append(",coalesce(b.job,'その他') as keyword2");
    lSqlBuf.append(",b.paymethodname as keyword3");
    lSqlBuf.append(",to_char(a.shippmentdate::timestamp,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM (SELECT ordercode,orderbranchcode,MAX(shippmentdate) AS shippmentdate FROM orderdetailtbl GROUP BY ordercode,orderbranchcode) AS a,");
    lSqlBuf.append("ordersumvw AS b ");
    lSqlBuf.append("WHERE a.ordercode = b.ordercode AND b.status IN('1','2') AND b.deliveryedflg+b.nodeliveryflg=1 ");
    if(SIUtil.isNotNull(branchCode)&&branchCode.equals("tokyo")){
      lSqlBuf.append("AND b.branchcode IN ('1','2','3') ");
    }else if(SIUtil.isNotNull(branchCode)){
      lSqlBuf.append("AND b.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
    }
    lSqlBuf.append("AND a.orderbranchcode = b.orderbranchcode ");
    lSqlBuf.append("AND b.branchcode <> '17' ");
    lSqlBuf.append("AND b.chargecode IS NOT NULL ");
    lSqlBuf.append("AND to_char(a.shippmentdate::timestamp,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM," "));
    lSqlBuf.append("AND to_char(a.shippmentdate::timestamp,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO," "));
    lSqlBuf.append("GROUP BY b.orderroute,b.job,b.paymethodname,to_char(a.shippmentdate::timestamp,'yyyymm')) ");
    lSqlBuf.append("UNION ALL ");
    lSqlBuf.append("(SELECT 4 AS flg");
    //EDBTG003-00 ohsugi mod start
//    lSqlBuf.append(",ROUND(sum(TRUNC(b.sumofdiscount / (1 + (b.taxrate / 100)))) / 1000) AS totalofprice");
    lSqlBuf.append(",ROUND(sum(TRUNC((b.sumofdiscount + b.setdiscount) / (1 + (b.taxrate / 100)))) / 1000) AS totalofprice");
//    lSqlBuf.append(",ROUND(sum(TRUNC(b.sumofdiscount / (1 + (b.taxrate / 100)))) / 1000) AS totalofgain");
    lSqlBuf.append(",ROUND(sum(TRUNC((b.sumofdiscount + b.setdiscount) / (1 + (b.taxrate / 100)))) / 1000) AS totalofgain");
    //EDBTG003-00 ohsugi mod end
    lSqlBuf.append(",0 AS custcount");
    lSqlBuf.append(",b.orderroute as keyword1");
    lSqlBuf.append(",coalesce(b.job,'その他') as keyword2");
    lSqlBuf.append(",b.paymethodname as keyword3");
    lSqlBuf.append(",to_char(b.updatedatetime,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM ordersumvw AS b ");
    lSqlBuf.append("WHERE b.status='2' ");
    if(SIUtil.isNotNull(branchCode)&&branchCode.equals("tokyo")){
      lSqlBuf.append("AND b.branchcode IN ('1','2','3') ");
    }else if(SIUtil.isNotNull(branchCode)){
      lSqlBuf.append("AND b.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
    }
    lSqlBuf.append("AND b.branchcode <> '17' ");
    lSqlBuf.append("AND b.chargecode IS NOT NULL ");
    lSqlBuf.append("AND to_char(b.updatedatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM," "));
    lSqlBuf.append("AND to_char(b.updatedatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO," "));
    lSqlBuf.append("GROUP BY b.orderroute,b.job,b.paymethodname,to_char(b.updatedatetime,'yyyymm')) ");
    
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      while (lResultSet.next()) {
        lBasic = new SIMarketingReportData();
        lBasic.setReportFlg(lResultSet.getString("flg"));
        lBasic.setTotalOfPrice(lResultSet.getString("totalofprice"));
        lBasic.setTotalOfGain(lResultSet.getString("totalofgain"));
        lBasic.setCustCount(lResultSet.getString("custcount"));
        lBasic.setInitdatetime(lResultSet.getString("initdatetime"));
        lBasic.setKeyWord1(lResultSet.getString("keyword1"));
        lBasic.setKeyWord2(lResultSet.getString("keyword2"));
        lBasic.setKeyWord3(lResultSet.getString("keyword3"));
        if("0".equals(lBasic.getReportFlg())){
          setDetailData(lBasic.getInitdatetime(),lBasic.getKeyWord1(),lBasic.getKeyWord2(),lBasic.getTotalOfPrice());
          setDetailGainData(lBasic.getInitdatetime(),lBasic.getKeyWord1(),lBasic.getKeyWord2(),lBasic.getTotalOfGain());
        } else if ("1".equals(lBasic.getReportFlg())){
          setHeaderData(lBasic.getInitdatetime(),lBasic.getKeyWord1(),lBasic.getKeyWord2(),lBasic.getKeyWord3(),lBasic.getTotalOfPrice(),lBasic.getCustCount());
        } else if ("2".equals(lBasic.getReportFlg())){
          setCustData(lBasic.getInitdatetime(),lBasic.getKeyWord1(),lBasic.getTotalOfPrice(),lBasic.getCustCount());
        } else if ("3".equals(lBasic.getReportFlg())){
          setHeaderDataFee(lBasic.getInitdatetime(),lBasic.getKeyWord1(),lBasic.getKeyWord2(),lBasic.getKeyWord3(),lBasic.getTotalOfPrice(),lBasic.getCustCount());
        } else if ("4".equals(lBasic.getReportFlg())){
          setHeaderDataDiscount(lBasic.getInitdatetime(),lBasic.getKeyWord1(),lBasic.getKeyWord2(),lBasic.getKeyWord3(),lBasic.getTotalOfPrice(),lBasic.getCustCount());
        }
      }
      setBranchName("全選択");
      if(SIUtil.isNotNull(branchCode)&&branchCode.equals("tokyo")){
        setBranchName("東京総合");
      }else if (SIUtil.isNotNull(branchCode)){
        setBranchName(SIDBUtil.getFirstData(lConnection, "SELECT branchname FROM branchtbl WHERE branchcode="+SIDBUtil.SQL2Str(branchCode)));
      }
      
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
  }
  
  public void execCustData(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    StringBuffer lSqlBuf = new StringBuffer();
    
    lSqlBuf.append("SELECT COUNT(cu.custcode) AS totalCust");
    
    for (int index=0;index<JOB.length;index++) {
      lSqlBuf.append(",SUM(CASE WHEN job=").append(SIDBUtil.SQL2Str(JOB[index][1])).append(" THEN 1 ELSE 0 END) AS total" + index);
    }
    lSqlBuf.append(",SUM(cu.newmailflg) AS totalInfoMail ");
    lSqlBuf.append(",to_char(ca.initdatetime,'yyyymm') AS initdatetime ");
    lSqlBuf.append("FROM custtbl AS cu,custaddresstbl ca ");
    lSqlBuf.append("WHERE to_char(ca.initdatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM, " "));
    lSqlBuf.append("AND to_char(ca.initdatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO, " "));
    lSqlBuf.append("AND cu.custcode = ca.custcode AND ca.addresscode = '0' AND cu.delflg = '0' ");
    lSqlBuf.append("GROUP BY to_char(ca.initdatetime,'yyyymm') ");
    
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      while (lResultSet.next()) {
        setNewCustDataAll(lResultSet.getString("initdatetime"),lResultSet.getString("totalCust"));
        for (int i=0;i<JOB.length;i++) {
          setNewCustDataAttr(lResultSet.getString("initdatetime"), i, lResultSet.getString("total" + i));
        }
        setNewCustDataInfoMail(lResultSet.getString("initdatetime"),lResultSet.getString("totalInfoMail"));
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
  }
  
  private void execTrendData(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    StringBuffer lSqlBuf = new StringBuffer();
    
    // セグメント別集計（売上・原価）
    lSqlBuf.append("(SELECT (sum(a.totalofprice) /1000) AS totalofprice");
    lSqlBuf.append(",(sum(a.totalofcost) /1000) AS totalofcost");
    lSqlBuf.append(",CASE WHEN substr(a.individualcode,1,1) IN ('H','V') THEN 'H'");// エクステ・ヘア水物
    lSqlBuf.append(" WHEN substr(a.individualcode,1,1) = 'L' THEN 'L'");// エステ水物
    lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('N','W') THEN 'N'");// ネイル・まつ毛材料
    lSqlBuf.append(" WHEN i.usednewflg IN ('2','4') THEN 'AU'");// アウトレット
    lSqlBuf.append(" WHEN i.usednewflg IN ('1','6') THEN 'NB'");// ナチュラルブランド
    lSqlBuf.append(" WHEN i.usednewflg = '7' THEN 'PB'");// プライベートブランド
    lSqlBuf.append(" ELSE 'UD' END AS keyword");// 中古
    lSqlBuf.append(",to_char(a.shippmentdate::timestamp,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM reportdetailbgvw AS a,individualtbl i ");
    lSqlBuf.append("WHERE a.individualcode = i.individualcode ");
    if(SIUtil.isNotNull(branchCode)){
      lSqlBuf.append("AND a.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
    }
    lSqlBuf.append(" AND to_char(a.shippmentdate::timestamp,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(a.shippmentdate::timestamp,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" GROUP BY CASE WHEN substr(a.individualcode,1,1) IN ('H','V') THEN 'H'");// エクステ・ヘア水物
    lSqlBuf.append(" WHEN substr(a.individualcode,1,1) = 'L' THEN 'L'");// エステ水物
    lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('N','W') THEN 'N'");// ネイル・まつ毛材料
    lSqlBuf.append(" WHEN i.usednewflg IN ('2','4') THEN 'AU'");// アウトレット
    lSqlBuf.append(" WHEN i.usednewflg IN ('1','6') THEN 'NB'");// ナチュラルブランド
    lSqlBuf.append(" WHEN i.usednewflg = '7' THEN 'PB'");// プライベートブランド
    lSqlBuf.append(" ELSE 'UD' END ");// 中古
    lSqlBuf.append(" ,substr(a.individualcode,1,1),to_char(a.shippmentdate::timestamp,'yyyymm')) ");
    // 返品
    lSqlBuf.append("UNION ALL ");
    lSqlBuf.append("(SELECT -1 * (sum(a.totalofprice) /1000) AS totalofprice");
    lSqlBuf.append(",-1 * (sum(a.totalofcost) /1000) AS totalofcost");
    lSqlBuf.append(",CASE WHEN substr(a.individualcode,1,1) IN ('H','V') THEN 'H'");// エクステ・ヘア水物
    lSqlBuf.append(" WHEN substr(a.individualcode,1,1) = 'L' THEN 'L'");// エステ水物
    lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('N','W') THEN 'N'");// ネイル・まつ毛材料
    lSqlBuf.append(" WHEN i.usednewflg IN ('2','4') THEN 'AU'");// アウトレット
    lSqlBuf.append(" WHEN i.usednewflg IN ('1','6') THEN 'NB'");// ナチュラルブランド
    lSqlBuf.append(" WHEN i.usednewflg = '7' THEN 'PB'");// プライベートブランド
    lSqlBuf.append(" ELSE 'UD' END AS keyword");// 中古
    lSqlBuf.append(",to_char(a.updatedatetime,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM reportdetailbgvw AS a,individualtbl i ");
    lSqlBuf.append("WHERE a.individualcode = i.individualcode AND a.status = '2' ");
    if(SIUtil.isNotNull(branchCode)){
      lSqlBuf.append("AND a.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
    }
    lSqlBuf.append(" AND to_char(a.updatedatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(a.updatedatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" GROUP BY CASE WHEN substr(a.individualcode,1,1) IN ('H','V') THEN 'H'");// エクステ・ヘア水物
    lSqlBuf.append(" WHEN substr(a.individualcode,1,1) = 'L' THEN 'L'");// エステ水物
    lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('N','W') THEN 'N'");// ネイル・まつ毛材料
    lSqlBuf.append(" WHEN i.usednewflg IN ('2','4') THEN 'AU'");// アウトレット
    lSqlBuf.append(" WHEN i.usednewflg IN ('1','6') THEN 'NB'");// ナチュラルブランド
    lSqlBuf.append(" WHEN i.usednewflg = '7' THEN 'PB'");// プライベートブランド
    lSqlBuf.append(" ELSE 'UD' END ");// 中古
    lSqlBuf.append(" ,substr(a.individualcode,1,1),to_char(a.updatedatetime,'yyyymm')) ");
    // 訂正黒
    lSqlBuf.append("UNION ALL ");
    lSqlBuf.append("(SELECT 0 AS totalofprice");
    lSqlBuf.append(",sum(TRUNC((c.purchasepricenew - c.purchasepriceold) * a.amount / (1 + (tax(a.shippmentdate) / 100)))) /1000 AS totalofcost");
    lSqlBuf.append(",CASE WHEN substr(a.individualcode,1,1) IN ('H','V') THEN 'H'");// エクステ・ヘア水物
    lSqlBuf.append(" WHEN substr(a.individualcode,1,1) = 'L' THEN 'L'");// エステ水物
    lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('N','W') THEN 'N'");// ネイル・まつ毛材料
    lSqlBuf.append(" WHEN i.usednewflg IN ('2','4') THEN 'AU'");// アウトレット
    lSqlBuf.append(" WHEN i.usednewflg IN ('1','6') THEN 'NB'");// ナチュラルブランド
    lSqlBuf.append(" WHEN i.usednewflg = '7' THEN 'PB'");// プライベートブランド
    lSqlBuf.append(" ELSE 'UD' END AS keyword");// 中古
    lSqlBuf.append(",to_char(c.initdatetime,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM orderdetailtbl AS a,orderlatestvw AS b,individualtbl i,orderdetailcosttbl c ");
    lSqlBuf.append("WHERE a.ordercode = b.ordercode AND a.individualcode = i.individualcode ");
    lSqlBuf.append(" AND a.ordercode=c.ordercode AND a.individualcode = c.individualcode ");
    if(SIUtil.isNotNull(branchCode)){
      lSqlBuf.append("AND b.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
    }
    lSqlBuf.append(" AND a.orderbranchcode = b.orderbranchcode");
    lSqlBuf.append(" AND b.branchcode IS NOT NULL");
    lSqlBuf.append(" AND b.chargecode IS NOT NULL");
    lSqlBuf.append(" AND to_char(c.initdatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(c.initdatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" GROUP BY CASE WHEN substr(a.individualcode,1,1) IN ('H','V') THEN 'H'");// エクステ・ヘア水物
    lSqlBuf.append(" WHEN substr(a.individualcode,1,1) = 'L' THEN 'L'");// エステ水物
    lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('N','W') THEN 'N'");// ネイル・まつ毛材料
    lSqlBuf.append(" WHEN i.usednewflg IN ('2','4') THEN 'AU'");// アウトレット
    lSqlBuf.append(" WHEN i.usednewflg IN ('1','6') THEN 'NB'");// ナチュラルブランド
    lSqlBuf.append(" WHEN i.usednewflg = '7' THEN 'PB'");// プライベートブランド
    lSqlBuf.append(" ELSE 'UD' END ");// 中古
    lSqlBuf.append(" ,substr(a.individualcode,1,1),to_char(c.initdatetime,'yyyymm')) ");
    // 訂正赤
    lSqlBuf.append("UNION ALL ");
    lSqlBuf.append("(SELECT 0 AS totalofprice");
    lSqlBuf.append(",-1 * sum(TRUNC((c.purchasepricenew - c.purchasepriceold) * a.amount / (1 + (tax(a.shippmentdate) / 100)))) /1000 AS totalofcost");
    lSqlBuf.append(",CASE WHEN substr(a.individualcode,1,1) IN ('H','V') THEN 'H'");// エクステ・ヘア水物
    lSqlBuf.append(" WHEN substr(a.individualcode,1,1) = 'L' THEN 'L'");// エステ水物
    lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('N','W') THEN 'N'");// ネイル・まつ毛材料
    lSqlBuf.append(" WHEN i.usednewflg IN ('2','4') THEN 'AU'");// アウトレット
    lSqlBuf.append(" WHEN i.usednewflg IN ('1','6') THEN 'NB'");// ナチュラルブランド
    lSqlBuf.append(" WHEN i.usednewflg = '7' THEN 'PB'");// プライベートブランド
    lSqlBuf.append(" ELSE 'UD' END AS keyword");// 中古
    lSqlBuf.append(",to_char(b.updatedatetime,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM orderdetailtbl AS a,orderlatestvw AS b,individualtbl i,orderdetailcosttbl c ");
    lSqlBuf.append("WHERE a.ordercode = b.ordercode AND a.individualcode = i.individualcode ");
    lSqlBuf.append(" AND a.ordercode=c.ordercode AND a.individualcode = c.individualcode ");
    if(SIUtil.isNotNull(branchCode)){
      lSqlBuf.append("AND b.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
    }
    lSqlBuf.append(" AND a.orderbranchcode = b.orderbranchcode");
    lSqlBuf.append(" AND b.status='2' ");
    lSqlBuf.append(" AND b.branchcode IS NOT NULL");
    lSqlBuf.append(" AND b.chargecode IS NOT NULL");
    lSqlBuf.append(" AND to_char(b.updatedatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(b.updatedatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" GROUP BY CASE WHEN substr(a.individualcode,1,1) IN ('H','V') THEN 'H'");// エクステ・ヘア水物
    lSqlBuf.append(" WHEN substr(a.individualcode,1,1) = 'L' THEN 'L'");// エステ水物
    lSqlBuf.append(" WHEN substr(a.individualcode,1,1) IN ('N','W') THEN 'N'");// ネイル・まつ毛材料
    lSqlBuf.append(" WHEN i.usednewflg IN ('2','4') THEN 'AU'");// アウトレット
    lSqlBuf.append(" WHEN i.usednewflg IN ('1','6') THEN 'NB'");// ナチュラルブランド
    lSqlBuf.append(" WHEN i.usednewflg = '7' THEN 'PB'");// プライベートブランド
    lSqlBuf.append(" ELSE 'UD' END ");// 中古
    lSqlBuf.append(" ,substr(a.individualcode,1,1),to_char(b.updatedatetime,'yyyymm')) ");
    // 手数料集計
    lSqlBuf.append("UNION ALL ");
    lSqlBuf.append("(SELECT (sum(TRUNC((b.fee + coalesce(b.discountfee,0)) / (1 + (b.taxrate / 100))))/1000) as totalofprice");
    lSqlBuf.append(",0 as totalofcost");
    lSqlBuf.append(",'fee' as keyword");
    lSqlBuf.append(",to_char(a.shippmentdate::timestamp,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM (SELECT ordercode,orderbranchcode,MAX(shippmentdate) as shippmentdate FROM orderdetailtbl GROUP BY ordercode,orderbranchcode) a,");
    lSqlBuf.append("ordersumvw AS b ");
    lSqlBuf.append("WHERE a.ordercode = b.ordercode AND b.status IN('1','2') AND b.deliveryedflg+b.nodeliveryflg=1 ");
    if(SIUtil.isNotNull(branchCode)){
      lSqlBuf.append("AND b.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
    }
    lSqlBuf.append(" AND a.orderbranchcode = b.orderbranchcode");
    lSqlBuf.append(" AND b.branchcode <> '17'");
    lSqlBuf.append(" AND b.chargecode IS NOT NULL");
    lSqlBuf.append(" AND to_char(a.shippmentdate::timestamp,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(a.shippmentdate::timestamp,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" GROUP BY to_char(a.shippmentdate::timestamp,'yyyymm'))");
    lSqlBuf.append("UNION ALL ");
    lSqlBuf.append("(SELECT -1 * (sum(TRUNC((b.fee + coalesce(b.discountfee,0)) / (1 + (b.taxrate / 100))))/1000) as totalofprice");
    lSqlBuf.append(",0 as totalofcost");
    lSqlBuf.append(",'fee' as keyword");
    lSqlBuf.append(",to_char(b.updatedatetime,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM ordersumvw as b ");
    lSqlBuf.append("WHERE b.status='2' ");
    if(SIUtil.isNotNull(branchCode)){
      lSqlBuf.append("AND b.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
    }
    lSqlBuf.append(" AND b.branchcode <> '17'");
    lSqlBuf.append(" AND b.chargecode IS NOT NULL");
    lSqlBuf.append(" AND to_char(b.updatedatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(b.updatedatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" GROUP BY to_char(b.updatedatetime,'yyyymm'))");
    // 値引き集計
    lSqlBuf.append("UNION ALL ");
    //EDBTG003-00 ohsugi mod start
//    lSqlBuf.append("(SELECT -1 * (sum(TRUNC(b.sumofdiscount / (1 + (b.taxrate / 100)))) /1000) as totalofprice");
    lSqlBuf.append("(SELECT -1 * (sum(TRUNC((b.sumofdiscount + b.setdiscount) / (1 + (b.taxrate / 100)))) /1000) as totalofprice");
    //EDBTG003-00 ohsugi mod end
    lSqlBuf.append(",0 as totalofcost");
    lSqlBuf.append(",'discount' as keyword");
    lSqlBuf.append(",to_char(a.shippmentdate::timestamp,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM (SELECT ordercode,orderbranchcode,MAX(shippmentdate) as shippmentdate FROM orderdetailtbl GROUP BY ordercode,orderbranchcode) a,");
    lSqlBuf.append("ordersumvw b ");
    lSqlBuf.append("WHERE a.ordercode = b.ordercode AND b.status IN('1','2') AND b.deliveryedflg+b.nodeliveryflg=1 ");
    if(SIUtil.isNotNull(branchCode)){
      lSqlBuf.append("AND b.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
    }
    lSqlBuf.append(" AND a.orderbranchcode = b.orderbranchcode");
    lSqlBuf.append(" AND b.branchcode <> '17'");
    lSqlBuf.append(" AND b.chargecode IS NOT NULL");
    lSqlBuf.append(" AND to_char(a.shippmentdate::timestamp,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(a.shippmentdate::timestamp,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" GROUP BY to_char(a.shippmentdate::timestamp,'yyyymm'))");
    lSqlBuf.append("UNION ALL ");
    //EDBTG003-00 ohsugi mod start
//    lSqlBuf.append("(SELECT (sum(TRUNC(b.sumofdiscount / (1 + (b.taxrate / 100)))) /1000) as totalofprice");
    lSqlBuf.append("(SELECT (sum(TRUNC((b.sumofdiscount + b.setdiscount) / (1 + (b.taxrate / 100)))) /1000) as totalofprice");
    //EDBTG003-00 ohsugi mod end
    lSqlBuf.append(",0 as totalofcost");
    lSqlBuf.append(",'discount' as keyword");
    lSqlBuf.append(",to_char(b.updatedatetime,'yyyymm') as initdatetime ");
    lSqlBuf.append("FROM ordersumvw b ");
    lSqlBuf.append("WHERE b.status='2' ");
    if(SIUtil.isNotNull(branchCode)){
      lSqlBuf.append("AND b.branchcode = ").append(SIDBUtil.SQL2Str(branchCode," "));
    }
    lSqlBuf.append(" AND b.branchcode <> '17'");
    lSqlBuf.append(" AND b.chargecode IS NOT NULL");
    lSqlBuf.append(" AND to_char(b.updatedatetime,'yyyymm') >= ").append(SIDBUtil.SQL2Str(seasonYearCbo + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(b.updatedatetime,'yyyymm') <= ").append(SIDBUtil.SQL2Str(SIUtil.add(seasonYearCbo, "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" GROUP BY to_char(b.updatedatetime,'yyyymm'))");
    
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      while (lResultSet.next()) {
        setTrendData(lResultSet.getString("initdatetime"),lResultSet.getString("keyword"),lResultSet.getString("totalofprice"),lResultSet.getString("totalofcost"));
      }
      
      setBranchName("全選択");
      if (SIUtil.isNotNull(branchCode)){
        setBranchName(SIDBUtil.getFirstData(lConnection, "SELECT branchname FROM branchtbl WHERE branchcode="+SIDBUtil.SQL2Str(branchCode)));
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
  }
  
  private void execStoreHistory(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append(" SELECT to_char(sh.initdatetime,'yyyymm') as initdatetime, SUM(sh.purchaseprice+sh.processingexpence) / 1000 AS count ");
    lSqlBuf.append(" FROM storehistorytbl AS sh ");
    lSqlBuf.append(" WHERE sh.delflg IN ('1','4') ");
    lSqlBuf.append(" AND sh.storetype = '1' ");
    lSqlBuf.append(" AND to_char(sh.initdatetime,'yyyymm') >= " + SIDBUtil.SQL2Str(this.getSeasonYearCbo() + SIConfig.SEASONFROM));
    lSqlBuf.append(" AND to_char(sh.initdatetime,'yyyymm') <= " + SIDBUtil.SQL2Str(SIUtil.add(this.getSeasonYearCbo(), "1") + SIConfig.SEASONTO));
    lSqlBuf.append(" GROUP BY to_char(sh.initdatetime,'yyyymm') ");
    
    // 実行
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      while (lResultSet.next()) {
        setStoreHistoryData(lResultSet.getString("initdatetime"),lResultSet.getString("count"));
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
  }
  
  public void executeM(Connection lConnection){
    execMarketingData(lConnection);
    execCustData(lConnection);
  }
  
  public void executeT(Connection lConnection){
    execTrendData(lConnection);
    execStoreHistory(lConnection);
  }
}