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.config.SIConfig;
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.SIURLParameter;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;

public class UISegmentStatus extends SIBasic {
  
  //ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  private final int US_INX = 0;// 中古・アウトレット
  private final int NB_INX = 1;// 新品NB
  private final int PB_INX = 2;// 新品PB
  private final int EX_INX = 3;// エクステ・ヘア水物
  private final int ES_INX = 4;// エステ水物
  private final int NE_INX = 5;// ネイル・まつ毛材料
  
  // 売上年
  private String year = "";
  
  // 売上月
  private String month = "";
  
  // 商品売上高
  private String[] salesTotal = new String[6];
  
  // 代引手数料
  private String[] feeTotal = new String[6];
  
  // 売上割引高
  private String[] discountTotal = new String[6];
  
  // 期首帳簿棚卸高（通常在庫）
  private String[] totalStockHead1 = new String[6];
  
  // 期首帳簿棚卸高（加工修理未承認入庫）
  private String[] totalStockHead2 = new String[6];
  
  // 期首帳簿棚卸高（貸出品未承認入庫）
  private String[] totalStockHead3 = new String[6];
  
  // 期首帳簿棚卸高（在庫移動未承認入庫）
  private String[] totalStockHead4 = new String[6];
  
  // 期首帳簿棚卸高（棚卸差異入庫分）
  private String[] totalStockHead5 = new String[6];
  
  // 期首帳簿棚卸高（棚卸差異出庫分）
  private String[] totalStockHead6 = new String[6];
  
  // 期中仕入高入庫分（仕入）
  private String[] totalStockMiddleIn1 = new String[6];
  
  // 期中仕入高入庫分（買取）
  private String[] totalStockMiddleIn2 = new String[6];
  
  // 期中仕入高入庫分（加工）
  private String[] totalStockMiddleIn3 = new String[6];
  
  // 期中仕入高入庫分（コード替）
  private String[] totalStockMiddleIn4 = new String[6];
  
  // 期中仕入高入庫分（金沢弥生）
  private String[] totalStockMiddleIn5 = new String[6];
  
  // 期中仕入高入庫分（訂正）
  private String[] totalStockMiddleIn6 = new String[6];
  
  // 期中仕入高入庫分（その他）
  private String[] totalStockMiddleIn7 = new String[6];
  
  // 期中仕入高入庫分（保証戻り）
  private String[] totalStockMiddleIn8 = new String[6];
  
  // 期中仕入高出庫分（廃棄）
  private String[] totalStockMiddleOut1 = new String[6];
  
  // 期中仕入高出庫分（運送事故）
  private String[] totalStockMiddleOut2 = new String[6];
  
  // 期中仕入高出庫分（保証対応）
  private String[] totalStockMiddleOut3 = new String[6];
  
  // 期中仕入高出庫分（講習会）
  private String[] totalStockMiddleOut4 = new String[6];
  
  // 期中仕入高出庫分（サンプル）
  private String[] totalStockMiddleOut5 = new String[6];
  
  // 期中仕入高出庫分（コード替）
  private String[] totalStockMiddleOut6 = new String[6];
  
  // 期中仕入高出庫分（メーカー返品）
  private String[] totalStockMiddleOut7 = new String[6];
  
  // 期中仕入高出庫分（金沢弥生）
  private String[] totalStockMiddleOut8 = new String[6];
  
  // 期中仕入高出庫分（訂正）
  private String[] totalStockMiddleOut9 = new String[6];
  
  // 期中仕入高出庫分（その他）
  private String[] totalStockMiddleOut10 = new String[6];
  
  // スポット在庫調整入庫分
  private String[] stockAdjustmentIn = new String[6];
  
  // スポット在庫調整出庫分
  private String[] stockAdjustmentOut = new String[6];
  
  // 商品評価損入庫分
  private String[] changeRatingIn = new String[6];
  
  // 商品評価損出庫分
  private String[] changeRatingOut = new String[6];
  
  // 期末棚卸高（通常在庫）
  private String[] totalStockTail1 = new String[6];
  
  // 期末棚卸高（加工修理未承認入庫）
  private String[] totalStockTail2 = new String[6];
  
  // 期末棚卸高（貸出品未承認入庫）
  private String[] totalStockTail3 = new String[6];
  
  // 期末棚卸高（在庫移動未承認入庫）
  private String[] totalStockTail4 = new String[6];
  
  // 集計タイプ
  private int type = 0;
  
  public UISegmentStatus(){
    for (int i=0;i<6;i++) {
      salesTotal[i] = "0";
      feeTotal[i] = "0";
      discountTotal[i] = "0";
      totalStockHead1[i] = "0";
      totalStockHead2[i] = "0";
      totalStockHead3[i] = "0";
      totalStockHead4[i] = "0";
      totalStockHead5[i] = "0";
      totalStockHead6[i] = "0";
      totalStockMiddleIn1[i] = "0";
      totalStockMiddleIn2[i] = "0";
      totalStockMiddleIn3[i] = "0";
      totalStockMiddleIn4[i] = "0";
      totalStockMiddleIn5[i] = "0";
      totalStockMiddleIn6[i] = "0";
      totalStockMiddleIn7[i] = "0";
      totalStockMiddleIn8[i] = "0";
      totalStockMiddleOut1[i] = "0";
      totalStockMiddleOut2[i] = "0";
      totalStockMiddleOut3[i] = "0";
      totalStockMiddleOut4[i] = "0";
      totalStockMiddleOut5[i] = "0";
      totalStockMiddleOut6[i] = "0";
      totalStockMiddleOut7[i] = "0";
      totalStockMiddleOut8[i] = "0";
      totalStockMiddleOut9[i] = "0";
      totalStockMiddleOut10[i] = "0";
      stockAdjustmentIn[i] = "0";
      stockAdjustmentOut[i] = "0";
      changeRatingIn[i] = "0";
      changeRatingOut[i] = "0";
      totalStockTail1[i] = "0";
      totalStockTail2[i] = "0";
      totalStockTail3[i] = "0";
      totalStockTail4[i] = "0";
    }
  }
  
  public String getYear() {
    return year;
  }
  
  public String getMonth() {
    return month;
  }
  
  public String[] getSalesTotal() {
    return salesTotal;
  }
  
  public String[] getFeeTotal() {
    return feeTotal;
  }
  
  public String[] getDiscountTotal() {
    return discountTotal;
  }
  
  public String[] getTotalStockHead1() {
    return totalStockHead1;
  }
  
  public String[] getTotalStockHead2() {
    return totalStockHead2;
  }
  
  public String[] getTotalStockHead3() {
    return totalStockHead3;
  }
  
  public String[] getTotalStockHead4() {
    return totalStockHead4;
  }
  
  public String[] getTotalStockHead5() {
    return totalStockHead5;
  }
  
  public String[] getTotalStockHead6() {
    return totalStockHead6;
  }
  
  public String[] getTotalStockMiddleIn1() {
    return totalStockMiddleIn1;
  }
  
  public String[] getTotalStockMiddleIn2() {
    return totalStockMiddleIn2;
  }
  
  public String[] getTotalStockMiddleIn3() {
    return totalStockMiddleIn3;
  }
  
  public String[] getTotalStockMiddleIn4() {
    return totalStockMiddleIn4;
  }
  
  public String[] getTotalStockMiddleIn5() {
    return totalStockMiddleIn5;
  }
  
  public String[] getTotalStockMiddleIn6() {
    return totalStockMiddleIn6;
  }
  
  public String[] getTotalStockMiddleIn7() {
    return totalStockMiddleIn7;
  }
  
  public String[] getTotalStockMiddleIn8() {
    return totalStockMiddleIn8;
  }
  
  public String[] getTotalStockMiddleOut1() {
    return totalStockMiddleOut1;
  }
  
  public String[] getTotalStockMiddleOut2() {
    return totalStockMiddleOut2;
  }
  
  public String[] getTotalStockMiddleOut3() {
    return totalStockMiddleOut3;
  }
  
  public String[] getTotalStockMiddleOut4() {
    return totalStockMiddleOut4;
  }
  
  public String[] getTotalStockMiddleOut5() {
    return totalStockMiddleOut5;
  }
  
  public String[] getTotalStockMiddleOut6() {
    return totalStockMiddleOut6;
  }
  
  public String[] getTotalStockMiddleOut7() {
    return totalStockMiddleOut7;
  }
  
  public String[] getTotalStockMiddleOut8() {
    return totalStockMiddleOut8;
  }
  
  public String[] getTotalStockMiddleOut9() {
    return totalStockMiddleOut9;
  }
  
  public String[] getTotalStockMiddleOut10() {
    return totalStockMiddleOut10;
  }
  
  public String[] getStockAdjustmentIn() {
    return stockAdjustmentIn;
  }
  
  public String[] getStockAdjustmentOut() {
    return stockAdjustmentOut;
  }
  
  public String[] getChangeRatingIn() {
    return changeRatingIn;
  }
  
  public String[] getChangeRatingOut() {
    return changeRatingOut;
  }
  
  public String[] getTotalStockTail1() {
    return totalStockTail1;
  }
  
  public String[] getTotalStockTail2() {
    return totalStockTail2;
  }
  
  public String[] getTotalStockTail3() {
    return totalStockTail3;
  }
  
  public String[] getTotalStockTail4() {
    return totalStockTail4;
  }
  
  public void setYear(String year) {
    if (SIUtil.isNull(year)) year = "";
    this.year = year;
  }
  
  public void setMonth(String month) {
    if (SIUtil.isNull(month)) month = "";
    this.month = month;
  }
  
  public void setSalesTotal(String[] salesTotal) {
    this.salesTotal = salesTotal;
  }
  
  public void setFeeTotal(String[] feeTotal) {
    this.feeTotal = feeTotal;
  }
  
  public void setDiscountTotal(String[] discountTotal) {
    this.discountTotal = discountTotal;
  }
  
  public void setTotalStockHead1(String[] totalStockHead1) {
    this.totalStockHead1 = totalStockHead1;
  }
  
  public void setTotalStockHead2(String[] totalStockHead2) {
    this.totalStockHead2 = totalStockHead2;
  }
  
  public void setTotalStockHead3(String[] totalStockHead3) {
    this.totalStockHead3 = totalStockHead3;
  }
  
  public void setTotalStockHead4(String[] totalStockHead4) {
    this.totalStockHead4 = totalStockHead4;
  }
  
  public void setTotalStockHead5(String[] totalStockHead5) {
    this.totalStockHead5 = totalStockHead5;
  }
  
  public void setTotalStockHead6(String[] totalStockHead6) {
    this.totalStockHead6 = totalStockHead6;
  }
  
  public void setTotalStockMiddleIn1(String[] totalStockMiddleIn1) {
    this.totalStockMiddleIn1 = totalStockMiddleIn1;
  }
  
  public void setTotalStockMiddleIn2(String[] totalStockMiddleIn2) {
    this.totalStockMiddleIn2 = totalStockMiddleIn2;
  }
  
  public void setTotalStockMiddleIn3(String[] totalStockMiddleIn3) {
    this.totalStockMiddleIn3 = totalStockMiddleIn3;
  }
  
  public void setTotalStockMiddleIn4(String[] totalStockMiddleIn4) {
    this.totalStockMiddleIn4 = totalStockMiddleIn4;
  }
  
  public void setTotalStockMiddleIn5(String[] totalStockMiddleIn5) {
    this.totalStockMiddleIn5 = totalStockMiddleIn5;
  }
  
  public void setTotalStockMiddleIn6(String[] totalStockMiddleIn6) {
    this.totalStockMiddleIn6 = totalStockMiddleIn6;
  }
  
  public void setTotalStockMiddleIn7(String[] totalStockMiddleIn7) {
    this.totalStockMiddleIn7 = totalStockMiddleIn7;
  }
  
  public void setTotalStockMiddleIn8(String[] totalStockMiddleIn8) {
    this.totalStockMiddleIn8 = totalStockMiddleIn8;
  }
  
  public void setTotalStockMiddleOut1(String[] totalStockMiddleOut1) {
    this.totalStockMiddleOut1 = totalStockMiddleOut1;
  }
  
  public void setTotalStockMiddleOut2(String[] totalStockMiddleOut2) {
    this.totalStockMiddleOut2 = totalStockMiddleOut2;
  }
  
  public void setTotalStockMiddleOut3(String[] totalStockMiddleOut3) {
    this.totalStockMiddleOut3 = totalStockMiddleOut3;
  }
  
  public void setTotalStockMiddleOut4(String[] totalStockMiddleOut4) {
    this.totalStockMiddleOut4 = totalStockMiddleOut4;
  }
  
  public void setTotalStockMiddleOut5(String[] totalStockMiddleOut5) {
    this.totalStockMiddleOut5 = totalStockMiddleOut5;
  }
  
  public void setTotalStockMiddleOut6(String[] totalStockMiddleOut6) {
    this.totalStockMiddleOut6 = totalStockMiddleOut6;
  }
  
  public void setTotalStockMiddleOut7(String[] totalStockMiddleOut7) {
    this.totalStockMiddleOut7 = totalStockMiddleOut7;
  }
  
  public void setTotalStockMiddleOut8(String[] totalStockMiddleOut8) {
    this.totalStockMiddleOut8 = totalStockMiddleOut8;
  }
  
  public void setTotalStockMiddleOut9(String[] totalStockMiddleOut9) {
    this.totalStockMiddleOut9 = totalStockMiddleOut9;
  }
  
  public void setTotalStockMiddleOut10(String[] totalStockMiddleOut10) {
    this.totalStockMiddleOut10 = totalStockMiddleOut10;
  }
  
  public void setStockAdjustmentIn(String[] stockAdjustmentIn) {
    this.stockAdjustmentIn = stockAdjustmentIn;
  }
  
  public void setStockAdjustmentOut(String[] stockAdjustmentOut) {
    this.stockAdjustmentOut = stockAdjustmentOut;
  }
  
  public void setChangeRatingIn(String[] changeRatingIn) {
    this.changeRatingIn = changeRatingIn;
  }
  
  public void setChangeRatingOut(String[] changeRatingOut) {
    this.changeRatingOut = changeRatingOut;
  }
  
  public void setTotalStockTail1(String[] totalStockTail1) {
    this.totalStockTail1 = totalStockTail1;
  }
  
  public void setTotalStockTail2(String[] totalStockTail2) {
    this.totalStockTail2 = totalStockTail2;
  }
  
  public void setTotalStockTail3(String[] totalStockTail3) {
    this.totalStockTail3 = totalStockTail3;
  }
  
  public void setTotalStockTail4(String[] totalStockTail4) {
    this.totalStockTail4 = totalStockTail4;
  }
  
  public int getType() {
    return type;
  }
  
  public void setType(int type) {
    this.type = type;
  }
  
  private String getSeasonFrom(){
    return year + "-" + month + "-01";
  }
  
  private String getSeasonTo(){
    String year2;
    String month2;
    
    if ("12".equals(this.month)) {
      year2 = SIUtil.add(year, "1");
      month2 = "01";
    } else {
      year2 = year;
      month2 = SIUtil.add(month, "1");
      if (month2.length() == 1) month2 = "0" + month2;
    }
    return year2 + "-" + month2 + "-01";
  }
  
  private String[] getRCNames1(){
    String year1;
    String month1;
    String[] params = new String[3];
    
    if ("01".equals(this.month)) {
      year1 = SIUtil.sub(year, "1");
      month1 = "12";
    } else {
      year1 = year;
      month1 = SIUtil.sub(month, "1");
    }
    params[0] = "amount" + String.valueOf(Integer.parseInt(month1));
    params[1] = year1;
    if (month1.length() == 1) month1 = "0" + month1;
    params[2] = year1 + month1;
    return params;
  }
  
  private String[] getRCNames2(){
    String[] params = new String[3];
    params[0] = "amount" + String.valueOf(Integer.parseInt(month));
    params[1] = year;
    params[2] = year + month;
    return params;
  }
  
  private int getSegment(String code){
    int res = US_INX;
    if (type==0) {
      if ("UD".equals(code)) res = US_INX;
      else if ("NB".equals(code)) res = NB_INX;
      else if ("PB".equals(code)) res = PB_INX;
      else if ("V".equals(code)) res = EX_INX;
      else if ("H".equals(code)) res = EX_INX;
      else if ("W".equals(code)) res = ES_INX;
      else if ("L".equals(code)) res = ES_INX;
    } else {
      if ("UD".equals(code)) res = US_INX;
      else if ("NB".equals(code)) res = NB_INX;
      else if ("PB".equals(code)) res = PB_INX;
      else if ("V".equals(code)) res = EX_INX;
      else if ("H".equals(code)) res = EX_INX;
      else if ("L".equals(code)) res = ES_INX;
      else if ("N".equals(code)) res = NE_INX;
      else if ("W".equals(code)) res = NE_INX;
    }
    return res;
  }
  
  public void init(SIURLParameter lUrlParam){
    this.setYear((String) lUrlParam.getParam("year"));
    this.setMonth((String) lUrlParam.getParam("month"));
  }
  
  public void validate(HttpServletRequest lRequest,SICustomErrors errors){
    lRequest.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
    if (errors == null) errors=new SICustomErrors();
    
    SICheckValid.checkValid(errors, "売上年", this.getYear(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
    SICheckValid.checkValid(errors, "売上月", this.getMonth(), SICheckDataConf.SICHECK_DATA_EMPTY_TYPE);
    
    if (!errors.isEmpty()) lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY,errors);
  }
  
  public void execute(Connection lConnection) {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    try {
      lStatement = lConnection.createStatement();
      
      /** 売上高計算 **/
      lResultSet = lStatement.executeQuery(getSalesSQL());
      String total1 = "0";
      String total2 = "0";
      String total3 = "0";
      while (lResultSet.next()) {
        salesTotal[getSegment(lResultSet.getString("segment"))] = SIUtil.add(salesTotal[getSegment(lResultSet.getString("segment"))], lResultSet.getString("totalofprice"));
        feeTotal[0] = SIUtil.add(feeTotal[0], lResultSet.getString("fee"));
        discountTotal[0] = SIUtil.add(discountTotal[0], lResultSet.getString("sumofdiscount"));
        total1 = SIUtil.add(total1, lResultSet.getString("totalofprice"));
        total2 = SIUtil.add(total2, lResultSet.getString("fee"));
        total3 = SIUtil.add(total3, lResultSet.getString("sumofdiscount"));
      }
      
      // 手数料・値引きの按分
      for (int i=1;i<6;i++) {
        if (i==5 && SICheckUtil.dateLess(this.getSeasonFrom(), "2013-02-28", SIConfig.SIDATE_FORMAT0)) break;
        feeTotal[i] = String.valueOf(Math.round(Long.parseLong(total2) * (Double.parseDouble(salesTotal[i]) / Double.parseDouble(total1))));
        feeTotal[0] = SIUtil.sub(feeTotal[0], feeTotal[i]);
        discountTotal[i] = String.valueOf(Math.round(Long.parseLong(total3) * (Double.parseDouble(salesTotal[i]) / Double.parseDouble(total1))));
        discountTotal[0] = SIUtil.sub(discountTotal[0], discountTotal[i]);
      }
      
      /** 期首帳簿棚卸高計算 **/
      lResultSet = lStatement.executeQuery(getStockRecordSQL(false));
      while (lResultSet.next()) {
        // 期首帳簿棚卸高（通常在庫）
        totalStockHead1[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockHead1[getSegment(lResultSet.getString("segment"))], lResultSet.getString("price"));
        
        // 期首帳簿棚卸高（加工修理未承認入庫）
        totalStockHead2[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockHead2[getSegment(lResultSet.getString("segment"))], lResultSet.getString("process"));
        
        // 期首帳簿棚卸高（貸出品未承認入庫）
        totalStockHead3[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockHead3[getSegment(lResultSet.getString("segment"))], lResultSet.getString("rental"));
        
        // 期首帳簿棚卸高（在庫移動未承認入庫）
        totalStockHead4[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockHead4[getSegment(lResultSet.getString("segment"))], lResultSet.getString("move"));
      }
      
      /** 棚卸差異・期中仕入高・スポット在庫調整・商品評価損計算 **/
      lResultSet = lStatement.executeQuery(getStoreSQL());
      while (lResultSet.next()) {
        // 期首帳簿棚卸高（棚卸差異入庫分）
        totalStockHead5[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockHead5[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockHead5"));
        
        // 期首帳簿棚卸高（棚卸差異出庫分）
        totalStockHead6[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockHead6[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockHead6"));
        
        // 期中仕入高入庫分（仕入）
        totalStockMiddleIn1[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockMiddleIn1[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockMiddleIn1"));
        
        // 期中仕入高入庫分（買取）
        totalStockMiddleIn2[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockMiddleIn2[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockMiddleIn2"));
        
        // 期中仕入高入庫分（加工）
        totalStockMiddleIn3[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockMiddleIn3[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockMiddleIn3"));
        
        // 期中仕入高入庫分（コード替）
        totalStockMiddleIn4[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockMiddleIn4[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockMiddleIn4"));
        
        // 期中仕入高入庫分（金沢弥生）
        totalStockMiddleIn5[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockMiddleIn5[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockMiddleIn5"));
        
        // 期中仕入高入庫分（訂正）
        totalStockMiddleIn6[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockMiddleIn6[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockMiddleIn6"));
        
        // 期中仕入高入庫分（その他）
        totalStockMiddleIn7[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockMiddleIn7[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockMiddleIn7"));
        
        // 期中仕入高入庫分（保証戻り）
        totalStockMiddleIn8[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockMiddleIn8[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockMiddleIn8"));
        
        // 期中仕入高出庫分（廃棄）
        totalStockMiddleOut1[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockMiddleOut1[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockMiddleOut1"));
        
        // 期中仕入高出庫分（運送事故）
        totalStockMiddleOut2[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockMiddleOut2[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockMiddleOut2"));
        
        // 期中仕入高出庫分（保証対応）
        totalStockMiddleOut3[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockMiddleOut3[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockMiddleOut3"));
        
        // 期中仕入高出庫分（講習会）
        totalStockMiddleOut4[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockMiddleOut4[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockMiddleOut4"));
        
        // 期中仕入高出庫分（サンプル）
        totalStockMiddleOut5[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockMiddleOut5[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockMiddleOut5"));
        
        // 期中仕入高出庫分（コード替）
        totalStockMiddleOut6[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockMiddleOut6[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockMiddleOut6"));
        
        // 期中仕入高出庫分（メーカー返品）
        totalStockMiddleOut7[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockMiddleOut7[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockMiddleOut7"));
        
        // 期中仕入高出庫分（金沢弥生）
        totalStockMiddleOut8[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockMiddleOut8[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockMiddleOut8"));
        
        // 期中仕入高出庫分（訂正）
        totalStockMiddleOut9[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockMiddleOut9[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockMiddleOut9"));
        
        // 期中仕入高出庫分（その他）
        totalStockMiddleOut10[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockMiddleOut10[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockMiddleOut10"));
        
        // スポット在庫調整入庫分
        stockAdjustmentIn[getSegment(lResultSet.getString("segment"))] = SIUtil.add(stockAdjustmentIn[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockAdjustmentIn"));
        
        // スポット在庫調整出庫分
        stockAdjustmentOut[getSegment(lResultSet.getString("segment"))] = SIUtil.add(stockAdjustmentOut[getSegment(lResultSet.getString("segment"))], lResultSet.getString("stockAdjustmentOut"));
        
        // 商品評価損入庫分
        changeRatingIn[getSegment(lResultSet.getString("segment"))] = SIUtil.add(changeRatingIn[getSegment(lResultSet.getString("segment"))], lResultSet.getString("changeRatingIn"));
        
        // 商品評価損出庫分
        changeRatingOut[getSegment(lResultSet.getString("segment"))] = SIUtil.add(changeRatingOut[getSegment(lResultSet.getString("segment"))], lResultSet.getString("changeRatingOut"));
      }
      
      /** 期末棚卸高計算 **/
      lResultSet = lStatement.executeQuery(getStockRecordSQL(true));
      while (lResultSet.next()) {
        // 期末帳簿棚卸高（通常在庫）
        totalStockTail1[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockTail1[getSegment(lResultSet.getString("segment"))], lResultSet.getString("price"));
        
        // 期末帳簿棚卸高（加工修理未承認入庫）
        totalStockTail2[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockTail2[getSegment(lResultSet.getString("segment"))], lResultSet.getString("process"));
        
        // 期末帳簿棚卸高（貸出品未承認入庫）
        totalStockTail3[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockTail3[getSegment(lResultSet.getString("segment"))], lResultSet.getString("rental"));
        
        // 期末帳簿棚卸高（在庫移動未承認入庫）
        totalStockTail4[getSegment(lResultSet.getString("segment"))] = SIUtil.add(totalStockTail4[getSegment(lResultSet.getString("segment"))], lResultSet.getString("move"));
      }
    } catch(Exception e) {
      e.printStackTrace();
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
  }
  
  private String getSalesSQL(){
    StringBuffer sqlBuf = new StringBuffer();
    
    // 1.商品明細データ
    sqlBuf.append("SELECT dt.totalofprice ");
    sqlBuf.append("     , 0 AS fee ");
    sqlBuf.append("     , 0 AS sumofdiscount ");
    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 dt INNER JOIN individualtbl id ON (dt.individualcode=id.individualcode) ");
    sqlBuf.append(",ordertbl oh ");
    sqlBuf.append("WHERE dt.ordercode = oh.ordercode ");
    sqlBuf.append("  AND dt.shippmentdate >= ").append(SIDBUtil.SQL2Str(this.getSeasonFrom()));
    sqlBuf.append("  AND dt.shippmentdate < ").append(SIDBUtil.SQL2Str(this.getSeasonTo()));
    sqlBuf.append("  AND oh.enabledflg = '1' ");
    
    // 2.商品明細データ（返品）
    sqlBuf.append("UNION ALL ");
    sqlBuf.append("SELECT -1 * dt.totalofprice AS totalofprice ");
    sqlBuf.append("     , 0 AS fee ");
    sqlBuf.append("     , 0 AS sumofdiscount ");
    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 dt INNER JOIN individualtbl id ON (dt.individualcode=id.individualcode) ");
    sqlBuf.append(",ordertbl oh ");
    sqlBuf.append("WHERE dt.ordercode = oh.ordercode ");
    sqlBuf.append("  AND oh.updatedatetime >= ").append(SIDBUtil.SQL2Str(this.getSeasonFrom()));
    sqlBuf.append("  AND oh.updatedatetime < ").append(SIDBUtil.SQL2Str(this.getSeasonTo()));
    sqlBuf.append("  AND oh.enabledflg = '1' ");
    sqlBuf.append("  AND oh.status = '2' ");
    
    // 3.値引き・手数料データ
    sqlBuf.append("UNION ALL ");
    sqlBuf.append("SELECT 0 AS totalofprice ");
    sqlBuf.append("     , TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) AS fee ");
    //EDBTG003-00 ohsugi mod start
//    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("     , '' AS segment ");
    sqlBuf.append("FROM ordersumvw oh, ");
    sqlBuf.append("(SELECT ordercode,MAX(shippmentdate) AS shippmentdate FROM orderdetaillatestvw GROUP BY ordercode) dt ");
    sqlBuf.append("WHERE oh.ordercode=dt.ordercode ");
    sqlBuf.append("  AND dt.shippmentdate >= ").append(SIDBUtil.SQL2Str(this.getSeasonFrom()));
    sqlBuf.append("  AND dt.shippmentdate < ").append(SIDBUtil.SQL2Str(this.getSeasonTo()));
    sqlBuf.append("  AND oh.deliveryedflg + oh.nodeliveryflg = 1 ");
    sqlBuf.append("  AND oh.branchcode <> '17' ");
    
    // 4.値引き・手数料データ（返品）
    sqlBuf.append("UNION ALL ");
    sqlBuf.append("SELECT 0 AS totalofprice ");
    sqlBuf.append("     , -1 * TRUNC((oh.totaloffee + oh.discountfee) / (1 + (oh.taxrate / 100))) AS fee ");
    //EDBTG003-00 ohsugi mod start
//    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("     , '' AS segment ");
    sqlBuf.append("FROM ordersumvw oh ");
    sqlBuf.append("WHERE oh.status = '2' ");
    sqlBuf.append("  AND oh.updatedatetime >= ").append(SIDBUtil.SQL2Str(this.getSeasonFrom()));
    sqlBuf.append("  AND oh.updatedatetime < ").append(SIDBUtil.SQL2Str(this.getSeasonTo()));
    sqlBuf.append("  AND oh.branchcode <> '17' ");
    
    return sqlBuf.toString();
  }
  
  private String getStockRecordSQL(boolean tailFlg){
    StringBuffer sqlBuf = new StringBuffer();
    String[] params;
    String initDate;
    if (!tailFlg) {
      params = getRCNames1();
      initDate = getSeasonFrom();
    } else {
      params = getRCNames2();
      initDate = getSeasonTo();
      
    }
    
    // 1.期首／期末棚卸データ
    sqlBuf.append("SELECT rc.").append(params[0]).append(" * TRUNC ((co.purchaseprice+co.processingexpence) / (1 + (tax.taxrate / 100))) AS price ");
    sqlBuf.append("     , 0 AS process ");
    sqlBuf.append("     , 0 AS rental ");
    sqlBuf.append("     , 0 AS move ");
    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 stockrecordtbl rc INNER JOIN individualtbl id ON (rc.individualcode=id.individualcode) ");
    sqlBuf.append("INNER JOIN costhistorytbl co ON (rc.individualcode=co.individualcode) ");
    sqlBuf.append(",(SELECT tax AS taxrate FROM taxmtbl WHERE startdate = (SELECT max(startdate) FROM taxmtbl WHERE startdate <=").append(SIDBUtil.SQL2Str(initDate)).append("::date - 1 )) tax ");
    sqlBuf.append("WHERE rc.stockyear = ").append(SIDBUtil.SQL2Str(params[1]));
    sqlBuf.append("  AND co.season = ").append(SIDBUtil.SQL2Str(params[2]));
    sqlBuf.append("  AND rc.").append(params[0]).append(" != 0 ");
    sqlBuf.append("  AND (co.purchaseprice+co.processingexpence) != 0 ");
    
    // 2.期首／期末未承認入庫データ
    boolean taxCheckFlg = false;
    try{
      if (SICheckUtil.dateGreater(initDate.replaceAll("-", "/"), "2010/02/01")) taxCheckFlg=true;
    }catch(Exception e){
      taxCheckFlg = false;
    }
    
    sqlBuf.append("UNION ALL ");
    sqlBuf.append("SELECT 0 AS price ");
    if (taxCheckFlg) {
      sqlBuf.append("     , CASE WHEN st.storetype IN ('2','7') THEN TRUNC ((st.purchaseprice+st.processingexpence) / (1 + (tax(st.initdatetime) / 100))) ELSE 0 END AS process ");
      sqlBuf.append("     , CASE WHEN st.storetype = '9' THEN TRUNC ((st.purchaseprice+st.processingexpence) / (1 + (tax(st.initdatetime) / 100))) ELSE 0 END AS rental ");
      sqlBuf.append("     , CASE WHEN st.storetype = '3' THEN TRUNC ((st.purchaseprice+st.processingexpence) / (1 + (tax(st.initdatetime) / 100))) ELSE 0 END AS move ");
    } else {
      sqlBuf.append("     , CASE WHEN st.storetype IN ('2','7') THEN st.purchaseprice+st.processingexpence ELSE 0 END AS process ");
      sqlBuf.append("     , CASE WHEN st.storetype = '9' THEN st.purchaseprice+st.processingexpence ELSE 0 END AS rental ");
      sqlBuf.append("     , CASE WHEN st.storetype = '3' THEN st.purchaseprice+st.processingexpence ELSE 0 END AS move ");
    }
    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 storehistoryrecordtbl st INNER JOIN individualtbl id ON (st.individualcode=id.individualcode) ");
    sqlBuf.append("WHERE st.initdatetime = ").append(SIDBUtil.SQL2Str(initDate));
    sqlBuf.append("  AND (st.purchaseprice+st.processingexpence) != 0 ");
    
    return sqlBuf.toString();
  }
  
  private String getStoreSQL(){
    StringBuffer sqlBuf = new StringBuffer();
    
    // 1.期中仕入高＋その他の入庫データ（仕入入庫データを除く）
    sqlBuf.append("SELECT CASE WHEN st.storetype='5' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.storetype='5' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockHead5");
    sqlBuf.append("     , 0 AS stockHead6");
    sqlBuf.append("     , 0 AS stockMiddleIn1");
    sqlBuf.append("     , CASE WHEN st.storetype='1' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.storetype='1' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockMiddleIn2");
    sqlBuf.append("     , CASE WHEN st.storetype='2' AND st.delflg IN ('1','2','4') AND st.amount=0 THEN TRUNC(st.processingexpence / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.storetype='2' AND st.delflg='3' AND st.amount=0 THEN -1*TRUNC(st.processingexpence / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            ELSE 0 END AS stockMiddleIn3");
    sqlBuf.append("     , CASE WHEN st.storetype='12' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.storetype='12' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockMiddleIn4");
    sqlBuf.append("     , CASE WHEN st.storetype='17' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.storetype='17' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockMiddleIn5");
    sqlBuf.append("     , CASE WHEN st.storetype='6' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.storetype='6' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockMiddleIn6");
    sqlBuf.append("     , CASE WHEN st.storetype='4' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.storetype='4' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockMiddleIn7");
    sqlBuf.append("     , CASE WHEN st.storetype='13' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.storetype='13' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockMiddleIn8");
    sqlBuf.append("     , 0 AS stockMiddleOut1");
    sqlBuf.append("     , 0 AS stockMiddleOut2");
    sqlBuf.append("     , 0 AS stockMiddleOut3");
    sqlBuf.append("     , 0 AS stockMiddleOut4");
    sqlBuf.append("     , 0 AS stockMiddleOut5");
    sqlBuf.append("     , 0 AS stockMiddleOut6");
    sqlBuf.append("     , 0 AS stockMiddleOut7");
    sqlBuf.append("     , 0 AS stockMiddleOut8");
    sqlBuf.append("     , 0 AS stockMiddleOut9");
    sqlBuf.append("     , 0 AS stockMiddleOut10");
    sqlBuf.append("     , CASE WHEN st.storetype='11' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.storetype='11' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockAdjustmentIn");
    sqlBuf.append("     , 0 AS stockAdjustmentOut");
    sqlBuf.append("     , CASE WHEN st.storetype='10' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.storetype='10' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS changeRatingIn");
    sqlBuf.append("     , 0 AS changeRatingOut");
    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 storehistorytbl st INNER JOIN individualtbl id ON (st.individualcode=id.individualcode) ");
    sqlBuf.append("WHERE st.calcdatetime >= ").append(SIDBUtil.SQL2Str(this.getSeasonFrom()));
    sqlBuf.append("  AND st.calcdatetime < ").append(SIDBUtil.SQL2Str(this.getSeasonTo()));
    sqlBuf.append("  AND st.storetype!='0' ");
    
    // 2.受発注仕入入庫データ
    sqlBuf.append("UNION ALL ");
    sqlBuf.append("SELECT 0 AS stockHead5");
    sqlBuf.append("     , 0 AS stockHead6");
    sqlBuf.append("     , CASE WHEN st.storetype='0' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.initdatetime) / 100))) ");
    sqlBuf.append("            WHEN st.storetype='0' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.initdatetime) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockMiddleIn1");
    sqlBuf.append("     , 0 AS stockMiddleIn2");
    sqlBuf.append("     , 0 AS stockMiddleIn3");
    sqlBuf.append("     , 0 AS stockMiddleIn4");
    sqlBuf.append("     , 0 AS stockMiddleIn5");
    sqlBuf.append("     , 0 AS stockMiddleIn6");
    sqlBuf.append("     , 0 AS stockMiddleIn7");
    sqlBuf.append("     , 0 AS stockMiddleIn8");
    sqlBuf.append("     , 0 AS stockMiddleOut1");
    sqlBuf.append("     , 0 AS stockMiddleOut2");
    sqlBuf.append("     , 0 AS stockMiddleOut3");
    sqlBuf.append("     , 0 AS stockMiddleOut4");
    sqlBuf.append("     , 0 AS stockMiddleOut5");
    sqlBuf.append("     , 0 AS stockMiddleOut6");
    sqlBuf.append("     , 0 AS stockMiddleOut7");
    sqlBuf.append("     , 0 AS stockMiddleOut8");
    sqlBuf.append("     , 0 AS stockMiddleOut9");
    sqlBuf.append("     , 0 AS stockMiddleOut10");
    sqlBuf.append("     , 0 AS stockAdjustmentIn");
    sqlBuf.append("     , 0 AS stockAdjustmentOut");
    sqlBuf.append("     , 0 AS changeRatingIn");
    sqlBuf.append("     , 0 AS changeRatingOut");
    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 storehistorytbl st INNER JOIN individualtbl id ON (st.individualcode=id.individualcode) ");
    sqlBuf.append("WHERE st.initdatetime >= ").append(SIDBUtil.SQL2Str(this.getSeasonFrom()));
    sqlBuf.append("  AND st.initdatetime < ").append(SIDBUtil.SQL2Str(this.getSeasonTo()));
    sqlBuf.append("  AND st.storetype='0' ");
    
    // 3.期中仕入高＋その他の出庫データ
    sqlBuf.append("UNION ALL ");
    sqlBuf.append("SELECT 0 AS stockHead5 ");
    sqlBuf.append("     , CASE WHEN st.shiptype='5' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.shiptype='5' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockHead6 ");
    sqlBuf.append("     , 0 AS stockMiddleIn1 ");
    sqlBuf.append("     , 0 AS stockMiddleIn2 ");
    sqlBuf.append("     , 0 AS stockMiddleIn3 ");
    sqlBuf.append("     , 0 AS stockMiddleIn4 ");
    sqlBuf.append("     , 0 AS stockMiddleIn5 ");
    sqlBuf.append("     , 0 AS stockMiddleIn6 ");
    sqlBuf.append("     , 0 AS stockMiddleIn7 ");
    sqlBuf.append("     , 0 AS stockMiddleIn8 ");
    sqlBuf.append("     , CASE WHEN st.shiptype='1' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.shiptype='1' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockMiddleOut1 ");
    sqlBuf.append("     , CASE WHEN st.shiptype='16' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.shiptype='16' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockMiddleOut2 ");
    sqlBuf.append("     , CASE WHEN st.shiptype='13' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.shiptype='13' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockMiddleOut3 ");
    sqlBuf.append("     , CASE WHEN st.shiptype='14' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.shiptype='14' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockMiddleOut4 ");
    sqlBuf.append("     , CASE WHEN st.shiptype='15' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.shiptype='15' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockMiddleOut5 ");
    sqlBuf.append("     , CASE WHEN st.shiptype='12' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.shiptype='12' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockMiddleOut6 ");
    sqlBuf.append("     , CASE WHEN st.shiptype='8' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.shiptype='8' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockMiddleOut7 ");
    sqlBuf.append("     , CASE WHEN st.shiptype='17' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.shiptype='17' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockMiddleOut8 ");
    sqlBuf.append("     , CASE WHEN st.shiptype='6' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.shiptype='6' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockMiddleOut9 ");
    sqlBuf.append("     , CASE WHEN st.shiptype='4' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.shiptype='4' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockMiddleOut10 ");
    sqlBuf.append("     , 0 AS stockAdjustmentIn ");
    sqlBuf.append("     , CASE WHEN st.shiptype='11' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.shiptype='11' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS stockAdjustmentOut ");
    sqlBuf.append("     , 0 AS changeRatingIn ");
    sqlBuf.append("     , CASE WHEN st.shiptype='10' AND st.delflg IN ('1','2','4') THEN TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100))) ");
    sqlBuf.append("            WHEN st.shiptype='10' AND st.delflg='3' THEN -1 * (TRUNC((st.purchaseprice+st.processingexpence) / (1 + (tax(st.calcdatetime::date) / 100)))) ");
    sqlBuf.append("            ELSE 0 END AS changeRatingOut ");
    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 shiphistorytbl st INNER JOIN individualtbl id ON (st.individualcode=id.individualcode) ");
    sqlBuf.append("WHERE st.calcdatetime >= ").append(SIDBUtil.SQL2Str(this.getSeasonFrom()));
    sqlBuf.append("  AND st.calcdatetime < ").append(SIDBUtil.SQL2Str(this.getSeasonTo()));
    
    return sqlBuf.toString();
  }
}