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

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;

import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIDBMultiConf;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;

/**
 * @version $Id: SIDeliveryFee.java,v 1.0 2003/11/21 Exp $
 * @author  Jinwang Chen
 * <br>Description:
 * <p>History</p>
 * <p>Author&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Reason</p>
 *  ============&nbsp;&nbsp;&nbsp;==========&nbsp;&nbsp;===========================<br>
 * Jinwang Chen   2003/11/21 14:16:22  Original
 */
public class SIDeliveryFee extends SIBasic{
  //ログ用のインスタンスの生成
  private static Category log=Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  //管理コード
  private String mallShopCode="";
  
  //配送種別コード
  private String deliveryTypeCode="";
  
  //都道府県コード
  private String prefectureCode="";
  
  //都道府県名
  private String prefectureName="";
  
  //サイズ1
  private String size1="0";
  
  //サイズ2
  private String size2="0";
  
  //サイズ3
  private String size3="0";
  
  //サイズ4
  private String size4="0";
  
  //サイズ5
  private String size5="0";
  
  //サイズ6
  private String size6="0";
  
  //サイズ7
  private String size7="0";
  
  //サイズ8
  private String size8="0";
  
  //サイズ9
  private String size9="0";
  
  //サイズ10
  private String size10="0";
  
  public SIDeliveryFee(){}
  
  public SIDeliveryFee(String lMallShopCode,String lDeliveryTypeCode,String lPrefectureCode){
    setMallShopCode(lMallShopCode);
    setDeliveryTypeCode(lDeliveryTypeCode);
    setPrefectureCode(lPrefectureCode);
  }
  
  //setter of 管理コード
  public void setMallShopCode(String lMallShopCode){
    if (SIUtil.isNull(lMallShopCode)) lMallShopCode="";
    this.mallShopCode=SIUtil.changeTo(lMallShopCode.trim(),this.encode);
  }
  
  //setter of 配送種別コード
  public void setDeliveryTypeCode(String lDeliveryTypeCode){
    if (SIUtil.isNull(lDeliveryTypeCode)) lDeliveryTypeCode="";
    this.deliveryTypeCode=SIUtil.changeTo(lDeliveryTypeCode.trim(),this.encode);
  }
  
  //setter of 都道府県コード
  public void setPrefectureCode(String lPrefectureCode){
    if (SIUtil.isNull(lPrefectureCode)) lPrefectureCode="";
    this.prefectureCode=SIUtil.changeTo(lPrefectureCode.trim(),this.encode);
  }
  
  //setter of 都道府県名
  public void setPrefectureName(String lPrefectureName){
    if (SIUtil.isNull(lPrefectureName)) lPrefectureName="";
    this.prefectureName=SIUtil.changeTo(lPrefectureName.trim(),this.encode);
  }
  
  //setter of サイズ1
  public void setSize1(String lSize1){
    if (SIUtil.isNull(lSize1)) lSize1="0";
    this.size1=SIUtil.changeTo(lSize1.trim(),this.encode);
  }
  
  //setter of サイズ2
  public void setSize2(String lSize2){
    if (SIUtil.isNull(lSize2)) lSize2="0";
    this.size2=SIUtil.changeTo(lSize2.trim(),this.encode);
  }
  
  //setter of サイズ3
  public void setSize3(String lSize3){
    if (SIUtil.isNull(lSize3)) lSize3="0";
    this.size3=SIUtil.changeTo(lSize3.trim(),this.encode);
  }
  
  //setter of サイズ4
  public void setSize4(String lSize4){
    if (SIUtil.isNull(lSize4)) lSize4="0";
    this.size4=SIUtil.changeTo(lSize4.trim(),this.encode);
  }
  
  //setter of サイズ5
  public void setSize5(String lSize5){
    if (SIUtil.isNull(lSize5)) lSize5="0";
    this.size5=SIUtil.changeTo(lSize5.trim(),this.encode);
  }
  
  //setter of サイズ6
  public void setSize6(String lSize6){
    if (SIUtil.isNull(lSize6)) lSize6="0";
    this.size6=SIUtil.changeTo(lSize6.trim(),this.encode);
  }
  
  //setter of サイズ7
  public void setSize7(String lSize7){
    if (SIUtil.isNull(lSize7)) lSize7="0";
    this.size7=SIUtil.changeTo(lSize7.trim(),this.encode);
  }
  
  //setter of サイズ8
  public void setSize8(String lSize8){
    if (SIUtil.isNull(lSize8)) lSize8="0";
    this.size8=SIUtil.changeTo(lSize8.trim(),this.encode);
  }
  
  //setter of サイズ9
  public void setSize9(String lSize9){
    if (SIUtil.isNull(lSize9)) lSize9="0";
    this.size9=SIUtil.changeTo(lSize9.trim(),this.encode);
  }
  
  //setter of サイズ10
  public void setSize10(String lSize10){
    if (SIUtil.isNull(lSize10)) lSize10="0";
    this.size10=SIUtil.changeTo(lSize10.trim(),this.encode);
  }
  
  //getter of 管理コード
  public String getMallShopCode(){
    return this.mallShopCode;
  }
  
  //getter of 配送種別コード
  public String getDeliveryTypeCode(){
    return this.deliveryTypeCode;
  }
  
  //getter of 都道府県コード
  public String getPrefectureCode(){
    return this.prefectureCode;
  }
  
  //getter of 都道府県名
  public String getPrefectureName(){
    return this.prefectureName;
  }
  
  //getter of サイズ1
  public String getSize1(){
    return this.size1;
  }
  
  //getter of サイズ2
  public String getSize2(){
    return this.size2;
  }
  
  //getter of サイズ3
  public String getSize3(){
    return this.size3;
  }
  
  //getter of サイズ4
  public String getSize4(){
    return this.size4;
  }
  
  //getter of サイズ5
  public String getSize5(){
    return this.size5;
  }
  
  //getter of サイズ6
  public String getSize6(){
    return this.size6;
  }
  
  //getter of サイズ7
  public String getSize7(){
    return this.size7;
  }
  
  //getter of サイズ8
  public String getSize8(){
    return this.size8;
  }
  
  //getter of サイズ9
  public String getSize9(){
    return this.size9;
  }
  
  //getter of サイズ10
  public String getSize10(){
    return this.size10;
  }
  
  /**
   * <b>reset</b>
   * 送料を取得
   * @param Connection
   * @return boolean
   * @throws なし
   */
  public boolean reset(Connection lConnection){
    StringBuffer lSqlBuf=new StringBuffer();
    lSqlBuf.append("SELECT * FROM DeliveryFeeMTbl " );
    lSqlBuf.append("WHERE MallShopCode="+SIDBUtil.SQL2Str(getMallShopCode()," "));
    lSqlBuf.append("AND DeliveryTypeCode="+SIDBUtil.SQL2Str(getDeliveryTypeCode()," "));
    lSqlBuf.append("AND PrefectureCode="+SIDBUtil.SQL2Str(getPrefectureCode()," "));
    return reset(lConnection,lSqlBuf.toString());
  }
  
  /**
   * <b>reset</b>
   * SQLから送料を取得
   * @param  Connection
   * @param  lSql        SQL
   * @return boolean
   * @throws なし
   */
  public boolean reset(Connection lConnection,String lSql){
    boolean lRes=false;
    Statement lStatement=null;
    ResultSet lResultSet=null;
    
    log.debug("reset:lSql="+lSql);
    try {
      lStatement=lConnection.createStatement();
      lResultSet=lStatement.executeQuery(lSql);
      
      if (lResultSet.next()){
        this.setEncode(SIConfig.SIENCODE_NONE);
        this.setMallShopCode(lResultSet.getString("mallShopCode"));//管理コード
        this.setDeliveryTypeCode(lResultSet.getString("deliveryTypeCode"));//配送種別コード
        this.setPrefectureCode(lResultSet.getString("prefectureCode"));//都道府県コード
        this.setPrefectureName(lResultSet.getString("prefectureName"));//都道府県名
        this.setSize1(lResultSet.getString("size1"));//サイズ1
        this.setSize2(lResultSet.getString("size2"));//サイズ2
        this.setSize3(lResultSet.getString("size3"));//サイズ3
        this.setSize4(lResultSet.getString("size4"));//サイズ4
        this.setSize5(lResultSet.getString("size5"));//サイズ5
        this.setSize6(lResultSet.getString("size6"));//サイズ6
        this.setSize7(lResultSet.getString("size7"));//サイズ7
        this.setSize8(lResultSet.getString("size8"));//サイズ8
        this.setSize9(lResultSet.getString("size9"));//サイズ9
        this.setSize10(lResultSet.getString("size10"));//サイズ10
        lRes=true;
      //7.1.1 ST0204 修正 ここから
      }//else {
        //特別地域の検索と普通都道府県の検索に際しては、呼び出す元により、エラーメッセージを出力する、
        //log.error("not find record for lSql="+lSql);
      //}
      //7.1.1 ST0204 修正 ここまで
    }catch(SQLException sqle){
      sqle.printStackTrace();
    }finally{
      SIDBUtil.close(lStatement,lResultSet);
    }
    return lRes;
  }
  
  public static String calcDeliveryFee(Connection lConnection,String lMallShopCode,String lOrderCode,String lDeliveryCode,boolean lEnable)throws SIDBAccessException{
    SIOrderDelivery lOrderDelivery=new SIOrderDelivery(lOrderCode,lDeliveryCode);
    lOrderDelivery.reset(lConnection);
    return calcDeliveryFee(lConnection,lMallShopCode,lOrderDelivery,lEnable);
  }
  
  public static String calcDeliveryFee(Connection lConnection,String lMallShopCode,String lOrderCode,String lDeliveryCode)throws SIDBAccessException{
    return calcDeliveryFee(lConnection,lMallShopCode,lOrderCode,lDeliveryCode,false);
  }
  
  public static String calcDeliveryFee(Connection lConnection,String lMallShopCode,SIOrderDelivery lOrderDelivery)throws SIDBAccessException{
    return calcDeliveryFee(lConnection,lMallShopCode,lOrderDelivery,false);
  }
  
  public static String calcDeliveryFee(Connection lConnection,String lMallShopCode,SIOrderDelivery lOrderDelivery,boolean lEnable)throws SIDBAccessException{
    Collection lOrderDetailColl=lOrderDelivery.getDetailCollection(lConnection);
    return calcDeliveryFee(lConnection,lMallShopCode,lOrderDelivery,lOrderDetailColl,lEnable);
  }
  
  public static String calcDeliveryFee(Connection lConnection,String lMallShopCode,SIOrderDelivery lOrderDelivery,Collection lCollection){
    try {
      return calcDeliveryFee(lConnection,lMallShopCode,lOrderDelivery,lCollection,false);
    } catch (SIDBAccessException e) {
      e.printStackTrace();
      return "0";
    }
  }
  
  /**
   * <b>calcDeliveryFee</b>
   * 商品サイズ、配送先などから送料を計算します
   * @param  Connection
   * @param  MallShopCode    ショップコード
   * @param  lOrderDelivery  受注配送先
   * @param  Collection      受注明細リスト
   * @return String          送料
   * @throws なし
   */
  public static String calcDeliveryFee(Connection lConnection,
                                       String lMallShopCode,
                                       SIOrderDelivery lOrderDelivery,
                                       Collection lCollection,
                                       boolean lEnable)throws SIDBAccessException{
    
    String lResFee="0";
    
    //送料ルールのデータの取得
    StringBuffer lSqlBuf=new StringBuffer("SELECT aa.* FROM FeeRuleMTbl aa,");
    lSqlBuf.append("DeliveryTypeMTbl bb ");
    lSqlBuf.append("WHERE aa.MallShopCode=bb.MallShopCode ");
    lSqlBuf.append("AND aa.DeliveryTypeCode=bb.DeliveryTypeCode ");
    lSqlBuf.append("AND bb.DeliveryTypeName=").append(SIDBUtil.SQL2Str(lOrderDelivery.getDeliveryTypeName()," "));
    lSqlBuf.append("AND aa.MallShopCode=").append(SIDBUtil.SQL2Str(lMallShopCode));
    
    SIFeeRule lFeeRule=new SIFeeRule();
    lFeeRule.reset(lConnection,lSqlBuf.toString());
    
      //受注明細レコードリスト
    SIOrderDetail[] lOrderDetailArr=(SIOrderDetail[])lCollection.toArray(new SIOrderDetail[0]);
    log.debug("lOrderDetailArr.size="+lOrderDetailArr.length);
    SIOrderDetail lOrderDetail=new SIOrderDetail();
    String lPriceByAmount="0";
    String lActTotalOfPrice="0";
    String lSizeFee="0";
    String lMaxFee="0";
    
    //一配送先での合計金額
    for (int ii=0;ii<lOrderDetailArr.length ;ii++){
      // EDBTG003-00 nagayoshi add start
      String price = lOrderDetailArr[ii].getPrice();
      if (lOrderDetailArr[ii].getSetDetailFlg().equals(SIConfig.SET_DETAIL_FLG_APPENDED)) {
        price = "0";
      }
      // EDBTG003-00 nagayoshi add end
      // EDBTG003-00 nagayoshi mod start
//      lPriceByAmount=SIUtil.multi_LL(lOrderDetailArr[ii].getPrice(),lOrderDetailArr[ii].getAmount());//商品価格＝数量*単価
      lPriceByAmount=SIUtil.multi_LL(price,lOrderDetailArr[ii].getAmount());//商品価格＝数量*単価
      // EDBTG003-00 nagayoshi mod end
      lPriceByAmount=SIUtil.add_LL(lPriceByAmount,lOrderDetailArr[ii].getWrappingPrice());//商品価格＋ラッピング価格
      if (lOrderDetailArr[ii].getTaxFlg().equals("1")){//税加算
        lPriceByAmount=SITax.calcPriceIncTax(lPriceByAmount,lOrderDetailArr[ii].getTaxRate());
      }
      lActTotalOfPrice=SIUtil.add_LL(lActTotalOfPrice,lPriceByAmount);
    }
    
    //配送先送料、まず特別の郵便番号の検索
    lSqlBuf=new StringBuffer("SELECT aa.* ");
    lSqlBuf.append("FROM DeliveryFeeMTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa,");
    lSqlBuf.append("SpcDeliveryMTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bb,");
    lSqlBuf.append("DeliveryTypeMTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("cc ");
    lSqlBuf.append("WHERE aa.MallShopCode=bb.MallShopCode ");
    lSqlBuf.append("AND aa.PrefectureCode=bb.PrefectureCode ");
    lSqlBuf.append("AND aa.MallShopCode=cc.MallShopCode ");
    lSqlBuf.append("AND cc.MallShopCode=").append(SIDBUtil.SQL2Str(lMallShopCode," "));
    lSqlBuf.append("AND cc.DeliveryTypeName=").append(SIDBUtil.SQL2Str(lOrderDelivery.getDeliveryTypeName()," "));
    lSqlBuf.append("AND aa.DeliveryTypeCode=cc.DeliveryTypeCode ");
    lSqlBuf.append("AND bb.DeliveryTypeCode=cc.DeliveryTypeCode ");
    lSqlBuf.append("AND bb.PostCode1=").append(SIDBUtil.SQL2Str(lOrderDelivery.getPostCode1()," "));
    lSqlBuf.append("AND bb.PostCode2=").append(SIDBUtil.SQL2Str(lOrderDelivery.getPostCode2()," "));
    
    SIDeliveryFee lDeliveryFee=new SIDeliveryFee();
    if (lDeliveryFee.reset(lConnection,lSqlBuf.toString())==false){//普通の都道府県
      
      //送料無料設定
      if (lFeeRule.getFreeFlg().equals("1")&&Long.parseLong(lActTotalOfPrice)>=Long.parseLong(lFeeRule.getPrice())){
        log.debug("送料無料:Long.parseLong(lActTotalOfPrice)="+Long.parseLong(lActTotalOfPrice)+",Long.parseLong(lFeeRule.getPrice())="+Long.parseLong(lFeeRule.getPrice()));
        return lResFee="0";//送料無料
      }
      log.debug("lResFee="+lResFee);
      
      lDeliveryFee=new SIDeliveryFee();
      lSqlBuf=new StringBuffer("SELECT aa.* FROM DeliveryFeeMTbl aa,");
      lSqlBuf.append("DeliveryTypeMTbl bb ");
      lSqlBuf.append("WHERE aa.MallShopCode=bb.MallShopCode ");
      lSqlBuf.append("AND aa.DeliveryTypeCode=bb.DeliveryTypeCode ");
      lSqlBuf.append("AND bb.MallShopCode=").append(SIDBUtil.SQL2Str(lMallShopCode," "));
      lSqlBuf.append("AND bb.DeliveryTypeName=").append(SIDBUtil.SQL2Str(lOrderDelivery.getDeliveryTypeName()," "));
      lSqlBuf.append("AND aa.PrefectureName=").append(SIDBUtil.SQL2Str(lOrderDelivery.getAddress1()," "));
      if (lDeliveryFee.reset(lConnection,lSqlBuf.toString())==false){
        log.error("not find delivery fee for "+lOrderDelivery.getAddress1()+",lSqlBuf="+lSqlBuf.toString());//7.1.1 ST0204 修正
        return lResFee;
      }
    } else if (lFeeRule.getFreeFlg().equals("1")){
      if (lDeliveryFee.getPrefectureCode().equals("50")&&Long.parseLong(lActTotalOfPrice)>=Long.parseLong(lFeeRule.getSpPrice1())){
        log.debug("送料無料:Long.parseLong(lActTotalOfPrice)="+Long.parseLong(lActTotalOfPrice)+",Long.parseLong(lFeeRule.getSpPrice1())="+Long.parseLong(lFeeRule.getSpPrice1()));
        return lResFee="0";//送料無料
      }
      if (lDeliveryFee.getPrefectureCode().equals("51")&&Long.parseLong(lActTotalOfPrice)>=Long.parseLong(lFeeRule.getSpPrice2())){
        log.debug("送料無料:Long.parseLong(lActTotalOfPrice)="+Long.parseLong(lActTotalOfPrice)+",Long.parseLong(lFeeRule.getSpPrice2())="+Long.parseLong(lFeeRule.getSpPrice2()));
        return lResFee="0";//送料無料
      }
    }
    log.debug("lCollection.size="+lCollection.size());
    
    //送料の計算
    for (int ii=0;ii<lOrderDetailArr.length ;ii++){
      // EDBTG003-00 nagayoshi add start
      if (lOrderDetailArr[ii].getSetDetailFlg().equals(SIConfig.SET_DETAIL_FLG_APPENDED)) {
        continue;
      }
      // EDBTG003-00 nagayoshi add end
      //サイズにより送料を変更
      if (lOrderDetailArr[ii].getCmdtySize().equals("1"))lSizeFee=lDeliveryFee.getSize1();
      else if (lOrderDetailArr[ii].getCmdtySize().equals("2"))lSizeFee=lDeliveryFee.getSize2();
      else if (lOrderDetailArr[ii].getCmdtySize().equals("3"))lSizeFee=lDeliveryFee.getSize3();
      else if (lOrderDetailArr[ii].getCmdtySize().equals("4"))lSizeFee=lDeliveryFee.getSize4();
      else if (lOrderDetailArr[ii].getCmdtySize().equals("5"))lSizeFee=lDeliveryFee.getSize5();
      else if (lOrderDetailArr[ii].getCmdtySize().equals("6"))lSizeFee=lDeliveryFee.getSize6();
      else if (lOrderDetailArr[ii].getCmdtySize().equals("7"))lSizeFee=lDeliveryFee.getSize7();
      else if (lOrderDetailArr[ii].getCmdtySize().equals("8"))lSizeFee=lDeliveryFee.getSize8();
      else if (lOrderDetailArr[ii].getCmdtySize().equals("9"))lSizeFee=lDeliveryFee.getSize9();
      else if (lOrderDetailArr[ii].getCmdtySize().equals("10"))lSizeFee=lDeliveryFee.getSize10();
      else lSizeFee="0";
      
      if (Long.parseLong(lSizeFee)>Long.parseLong(lMaxFee)){
        lMaxFee=lSizeFee;
      }
      if (lFeeRule.getMultiBuyFlg().equals("1"))lResFee=SIUtil.add_LL(lResFee,SIUtil.multi_LL(lFeeRule.getUnitPrice(),lOrderDetailArr[ii].getAmount()));
      else lResFee=SIUtil.add_LL(lResFee,SIUtil.multi_LL(lSizeFee,lOrderDetailArr[ii].getAmount()));
      
      log.debug("lOrderDetailArr["+ii+"]="+lOrderDetailArr[ii].getDeliveryCode()+",lResFee="+lResFee+",lMaxFee="+lMaxFee);
    }
    log.debug("lResFee="+lResFee
           +",lMaxFee="+lMaxFee
           +",lFeeRule.getUnitPrice()="+lFeeRule.getUnitPrice()
           +",lActTotalOfPrice="+lActTotalOfPrice
           +",lFeeRule.getPrice()="+lFeeRule.getPrice()
           +",lFeeRule.getFreeFlg()="+lFeeRule.getFreeFlg()
           +",lFeeRule.getMultiBuyFlg()="+lFeeRule.getMultiBuyFlg());
    
    if (lFeeRule.getMultiBuyFlg().equals("1")&&lOrderDetailArr.length>0){
      lResFee=SIUtil.sub_LL(SIUtil.add_LL(lResFee,lMaxFee),lFeeRule.getUnitPrice());
    }
    
//7.2.0 ST1030 削除（ソース整理） ここから
//    if (lEnable){
//      lSqlBuf=new StringBuffer("UPDATE OrderDeliveryTbl SET DeliveryFee=");
//      lSqlBuf.append(SIDBUtil.SQL2Str(lResFee," "));
//      lSqlBuf.append("WHERE OrderCode=").append(SIDBUtil.SQL2Str(lOrderDelivery.getOrderCode()," "));
//      lSqlBuf.append("AND DeliveryCode=").append(SIDBUtil.SQL2Str(lOrderDelivery.getDeliveryCode()));
//      SIDBUtil.execSQL(lConnection, lSqlBuf.toString());
//    }
    return lResFee;
  }
//7.2.0 ST1030 削除（ソース整理） ここまで
  
  /**
   * <b>calcDeliveryFee</b>
   * 商品サイズ、配送先などから送料を計算します
   * @param  Connection
   * @param  MallShopCode    ショップコード
   * @param  lOrderDelivery  受注配送先
   * @param  Collection      受注明細リスト
   * @return String          送料
   * @throws なし
   */
  public static String calcDeliveryFee(Connection lConnection,String lOrderCode) throws SIDBAccessException{
    String lResFee="0";
    StringBuffer deliverySql = new StringBuffer();
    deliverySql.append("SELECT deliveryCode,deliveryTypeName,postCode1,postCode2,address1 FROM orderdeliverylatestvw ");
    deliverySql.append("WHERE ordercode=").append(SIDBUtil.SQL2Str(lOrderCode));
    
    try {
      Statement lStatement = lConnection.createStatement();
      ResultSet lResultSet = lStatement.executeQuery(deliverySql.toString());
      
      while(lResultSet.next()) {
        
        //パラメータ取得
        String lDeliveryCode = lResultSet.getString("deliveryCode");
        String lDeliveryTypeName = lResultSet.getString("deliveryTypeName");
        String lPostCode1 = lResultSet.getString("postCode1");
        String lPostCode2 = lResultSet.getString("postCode2");
        String lAddress1 = lResultSet.getString("address1");
        SIOrderDelivery lOrderDelivery = new SIOrderDelivery(lOrderCode,lDeliveryCode);
        Collection lOrderDetailColl=lOrderDelivery.getDetailCollection(lConnection);
        
        //送料ルールのデータの取得
        StringBuffer lSqlBuf=new StringBuffer("SELECT aa.* FROM FeeRuleMTbl aa,");
        lSqlBuf.append("DeliveryTypeMTbl bb ");
        lSqlBuf.append("WHERE aa.MallShopCode=bb.MallShopCode ");
        lSqlBuf.append("AND aa.DeliveryTypeCode=bb.DeliveryTypeCode ");
        lSqlBuf.append("AND aa.MallShopCode='0' ");
        lSqlBuf.append("AND bb.DeliveryTypeName=").append(SIDBUtil.SQL2Str(lDeliveryTypeName));
        
        SIFeeRule lFeeRule=new SIFeeRule();
        lFeeRule.reset(lConnection,lSqlBuf.toString());
        
        //受注明細レコードリスト
        SIOrderDetail[] lOrderDetailArr=(SIOrderDetail[])lOrderDetailColl.toArray(new SIOrderDetail[0]);
        String lPriceByAmount="0";
        String lActTotalOfPrice="0";
        String lSizeFee="0";
        String lMaxFee="0";
        
        //一配送先での合計金額
        for (int ii=0;ii<lOrderDetailArr.length ;ii++){
          lPriceByAmount=SIUtil.multi_LL(lOrderDetailArr[ii].getPrice(),lOrderDetailArr[ii].getAmount());//商品価格＝数量*単価
          lPriceByAmount=SIUtil.add_LL(lPriceByAmount,lOrderDetailArr[ii].getWrappingPrice());//商品価格＋ラッピング価格
          if (lOrderDetailArr[ii].getTaxFlg().equals("1")){//税加算
            lPriceByAmount=SITax.calcPriceIncTax(lPriceByAmount,lOrderDetailArr[ii].getTaxRate());
          }
          lActTotalOfPrice=SIUtil.add_LL(lActTotalOfPrice,lPriceByAmount);
        }
        
        //配送先送料、まず特別の郵便番号の検索
        lSqlBuf=new StringBuffer("SELECT aa.* ");
        lSqlBuf.append("FROM DeliveryFeeMTbl aa, SpcDeliveryMTbl bb, DeliveryTypeMTbl cc ");
        lSqlBuf.append("WHERE cc.MallShopCode='0' ");
        lSqlBuf.append("AND aa.MallShopCode=bb.MallShopCode ");
        lSqlBuf.append("AND aa.PrefectureCode=bb.PrefectureCode ");
        lSqlBuf.append("AND aa.MallShopCode=cc.MallShopCode ");
        lSqlBuf.append("AND cc.DeliveryTypeName=").append(SIDBUtil.SQL2Str(lDeliveryTypeName," "));
        lSqlBuf.append("AND aa.DeliveryTypeCode=cc.DeliveryTypeCode ");
        lSqlBuf.append("AND bb.DeliveryTypeCode=cc.DeliveryTypeCode ");
        lSqlBuf.append("AND bb.PostCode1=").append(SIDBUtil.SQL2Str(lPostCode1," "));
        lSqlBuf.append("AND bb.PostCode2=").append(SIDBUtil.SQL2Str(lPostCode2," "));
        
        SIDeliveryFee lDeliveryFee=new SIDeliveryFee();
        if (lDeliveryFee.reset(lConnection,lSqlBuf.toString())==false){//普通の都道府県
          
          //送料無料設定
          if (lFeeRule.getFreeFlg().equals("1")&&Long.parseLong(lActTotalOfPrice)>=Long.parseLong(lFeeRule.getPrice())){
            log.debug("送料無料:Long.parseLong(lActTotalOfPrice)="+Long.parseLong(lActTotalOfPrice)+",Long.parseLong(lFeeRule.getPrice())="+Long.parseLong(lFeeRule.getPrice()));
            continue;//送料無料
          }
          
          lDeliveryFee=new SIDeliveryFee();
          lSqlBuf=new StringBuffer("SELECT aa.* FROM DeliveryFeeMTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa,");
          lSqlBuf.append("DeliveryTypeMTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bb ");
          lSqlBuf.append("WHERE aa.MallShopCode=bb.MallShopCode ");
          lSqlBuf.append("AND aa.DeliveryTypeCode=bb.DeliveryTypeCode ");
          lSqlBuf.append("AND bb.MallShopCode='0' ");
          lSqlBuf.append("AND bb.DeliveryTypeName=").append(SIDBUtil.SQL2Str(lDeliveryTypeName," "));
          lSqlBuf.append("AND aa.PrefectureName=").append(SIDBUtil.SQL2Str(lAddress1," "));
          if (lDeliveryFee.reset(lConnection,lSqlBuf.toString())==false){
            log.error("not find delivery fee for "+lAddress1+",lSqlBuf="+lSqlBuf.toString());
            continue;
          }
        }else if (lFeeRule.getFreeFlg().equals("1")){
          if(lDeliveryFee.getPrefectureCode().equals("50")&&Long.parseLong(lActTotalOfPrice)>=Long.parseLong(lFeeRule.getSpPrice1())){
            log.debug("送料無料:Long.parseLong(lActTotalOfPrice)="+Long.parseLong(lActTotalOfPrice)+",Long.parseLong(lFeeRule.getSpPrice1())="+Long.parseLong(lFeeRule.getSpPrice1()));
            continue;//送料無料
          }
          if(lDeliveryFee.getPrefectureCode().equals("51")&&Long.parseLong(lActTotalOfPrice)>=Long.parseLong(lFeeRule.getSpPrice2())){
            log.debug("送料無料:Long.parseLong(lActTotalOfPrice)="+Long.parseLong(lActTotalOfPrice)+",Long.parseLong(lFeeRule.getSpPrice2())="+Long.parseLong(lFeeRule.getSpPrice2()));
            continue;//送料無料
          }
        }
        
        //送料の計算
        for (int ii=0;ii<lOrderDetailArr.length ;ii++){
          
          //サイズにより送料を変更
          if (lOrderDetailArr[ii].getCmdtySize().equals("1"))lSizeFee=lDeliveryFee.getSize1();
          else if (lOrderDetailArr[ii].getCmdtySize().equals("2"))lSizeFee=lDeliveryFee.getSize2();
          else if (lOrderDetailArr[ii].getCmdtySize().equals("3"))lSizeFee=lDeliveryFee.getSize3();
          else if (lOrderDetailArr[ii].getCmdtySize().equals("4"))lSizeFee=lDeliveryFee.getSize4();
          else if (lOrderDetailArr[ii].getCmdtySize().equals("5"))lSizeFee=lDeliveryFee.getSize5();
          else if (lOrderDetailArr[ii].getCmdtySize().equals("6"))lSizeFee=lDeliveryFee.getSize6();
          else if (lOrderDetailArr[ii].getCmdtySize().equals("7"))lSizeFee=lDeliveryFee.getSize7();
          else if (lOrderDetailArr[ii].getCmdtySize().equals("8"))lSizeFee=lDeliveryFee.getSize8();
          else if (lOrderDetailArr[ii].getCmdtySize().equals("9"))lSizeFee=lDeliveryFee.getSize9();
          else if (lOrderDetailArr[ii].getCmdtySize().equals("10"))lSizeFee=lDeliveryFee.getSize10();
          else lSizeFee="0";
          
          if (Long.parseLong(lSizeFee)>Long.parseLong(lMaxFee)){
            lMaxFee=lSizeFee;
          }
          if (lFeeRule.getMultiBuyFlg().equals("1")) {
            lResFee = SIUtil.add_LL(lResFee,SIUtil.multi_LL(lFeeRule.getUnitPrice(),lOrderDetailArr[ii].getAmount()));
          } else {
            lResFee = SIUtil.add_LL(lResFee,SIUtil.multi_LL(lSizeFee,lOrderDetailArr[ii].getAmount()));
          }
        }
        
        if (lFeeRule.getMultiBuyFlg().equals("1")&&lOrderDetailArr.length>0){
          lResFee=SIUtil.sub_LL(SIUtil.add_LL(lResFee,lMaxFee),lFeeRule.getUnitPrice());
        }
      }
    }catch(SQLException e){
      e.printStackTrace();
      throw new SIDBAccessException();
    }
    
    return lResFee;
  }
  
  /**
   * <b>calcDeliveryFee</b>
   * 商品サイズ、配送先などから送料を計算します
   * @param  Connection
   * @param  MallShopCode    ショップコード
   * @param  lOrderDelivery  受注配送先
   * @param  Collection      受注明細リスト
   * @return String          送料
   * @throws なし
   */
  public static String calcDeliveryFeeIndividual(Connection lConnection,
                                       String lMallShopCode,
                                       SIOrderDelivery lOrderDelivery,
                                       SIOrderDetail lOrderDetail,
                                       String total)throws SIDBAccessException{
    
    String lResFee="0";
    
    // EDBTG003-00 nagayoshi add start
    if (lOrderDetail.getSetDetailFlg().equals(SIConfig.SET_DETAIL_FLG_APPENDED)) {
      log.debug("添付品送料無料");
      return lResFee="0";//送料無料
    }
    //EDBTG003-00 nagayoshi add end
    
    //送料ルールのデータの取得
    StringBuffer lSqlBuf=new StringBuffer("SELECT aa.* FROM FeeRuleMTbl aa,");
    lSqlBuf.append("DeliveryTypeMTbl bb ");
    lSqlBuf.append("WHERE aa.MallShopCode=bb.MallShopCode ");
    lSqlBuf.append("AND aa.DeliveryTypeCode=bb.DeliveryTypeCode ");
    lSqlBuf.append("AND bb.DeliveryTypeName=").append(SIDBUtil.SQL2Str(lOrderDelivery.getDeliveryTypeName()," "));
    lSqlBuf.append("AND aa.MallShopCode=").append(SIDBUtil.SQL2Str(lMallShopCode));
    
    SIFeeRule lFeeRule=new SIFeeRule();
    lFeeRule.reset(lConnection,lSqlBuf.toString());
    
    String lPriceByAmount="0";
    String lActTotalOfPrice="0";
    String lSizeFee="0";
    String lMaxFee="0";
    
    //配送先送料、まず特別の郵便番号の検索
    lSqlBuf=new StringBuffer("SELECT aa.* ");
    lSqlBuf.append("FROM DeliveryFeeMTbl aa,");
    lSqlBuf.append("SpcDeliveryMTbl bb,");
    lSqlBuf.append("DeliveryTypeMTbl cc ");
    lSqlBuf.append("WHERE aa.MallShopCode=bb.MallShopCode ");
    lSqlBuf.append("AND aa.PrefectureCode=bb.PrefectureCode ");
    lSqlBuf.append("AND aa.MallShopCode=cc.MallShopCode ");
    lSqlBuf.append("AND cc.MallShopCode=").append(SIDBUtil.SQL2Str(lMallShopCode," "));
    lSqlBuf.append("AND cc.DeliveryTypeName=").append(SIDBUtil.SQL2Str(lOrderDelivery.getDeliveryTypeName()," "));
    lSqlBuf.append("AND aa.DeliveryTypeCode=cc.DeliveryTypeCode ");
    lSqlBuf.append("AND bb.DeliveryTypeCode=cc.DeliveryTypeCode ");
    lSqlBuf.append("AND bb.PostCode1=").append(SIDBUtil.SQL2Str(lOrderDelivery.getPostCode1()," "));
    lSqlBuf.append("AND bb.PostCode2=").append(SIDBUtil.SQL2Str(lOrderDelivery.getPostCode2()," "));
    
    SIDeliveryFee lDeliveryFee=new SIDeliveryFee();
    if (lDeliveryFee.reset(lConnection,lSqlBuf.toString())==false){//普通の都道府県
      lActTotalOfPrice=total;
      //送料無料設定
      if (lFeeRule.getFreeFlg().equals("1")&&Long.parseLong(lActTotalOfPrice)>=Long.parseLong(lFeeRule.getPrice())&&"1".equals(lOrderDelivery.getDeliveryTypeCode())){
        log.debug("送料無料:Long.parseLong(lActTotalOfPrice)="+Long.parseLong(lActTotalOfPrice)+",Long.parseLong(lFeeRule.getPrice())="+Long.parseLong(lFeeRule.getPrice()));
        return lResFee="0";//送料無料
      }
      log.debug("lResFee="+lResFee);
      
      lDeliveryFee=new SIDeliveryFee();
      lSqlBuf=new StringBuffer("SELECT aa.* FROM DeliveryFeeMTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa,");
      lSqlBuf.append("DeliveryTypeMTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bb ");
      lSqlBuf.append("WHERE aa.MallShopCode=bb.MallShopCode ");
      lSqlBuf.append("AND aa.DeliveryTypeCode=bb.DeliveryTypeCode ");
      lSqlBuf.append("AND bb.MallShopCode=").append(SIDBUtil.SQL2Str(lMallShopCode," "));
      lSqlBuf.append("AND bb.DeliveryTypeName=").append(SIDBUtil.SQL2Str(lOrderDelivery.getDeliveryTypeName()," "));
      lSqlBuf.append("AND aa.PrefectureName=").append(SIDBUtil.SQL2Str(lOrderDelivery.getAddress1()," "));
      if (lDeliveryFee.reset(lConnection,lSqlBuf.toString())==false){
        log.error("not find delivery fee for "+lOrderDelivery.getAddress1()+",lSqlBuf="+lSqlBuf.toString());//7.1.1 ST0204 修正
        return lResFee;
      }
    } else if (lFeeRule.getFreeFlg().equals("1")){
      if(lDeliveryFee.getPrefectureCode().equals("50")&&Long.parseLong(total)>=Long.parseLong(lFeeRule.getSpPrice1())&&"1".equals(lOrderDelivery.getDeliveryTypeCode())){
        log.debug("送料無料:Long.parseLong(lActTotalOfPrice)="+Long.parseLong(total)+",Long.parseLong(lFeeRule.getSpPrice1())="+Long.parseLong(lFeeRule.getSpPrice1()));
        return lResFee="0";//送料無料
      }
      if(lDeliveryFee.getPrefectureCode().equals("51")&&Long.parseLong(total)>=Long.parseLong(lFeeRule.getSpPrice2())&&"1".equals(lOrderDelivery.getDeliveryTypeCode())){
        log.debug("送料無料:Long.parseLong(lActTotalOfPrice)="+Long.parseLong(total)+",Long.parseLong(lFeeRule.getSpPrice2())="+Long.parseLong(lFeeRule.getSpPrice2()));
        return lResFee="0";//送料無料
      }
    }
    
    //送料の計算
    //サイズにより送料を変更
    if (lOrderDetail.getCmdtySize().equals("1"))lSizeFee=lDeliveryFee.getSize1();
    else if (lOrderDetail.getCmdtySize().equals("2"))lSizeFee=lDeliveryFee.getSize2();
    else if (lOrderDetail.getCmdtySize().equals("3"))lSizeFee=lDeliveryFee.getSize3();
    else if (lOrderDetail.getCmdtySize().equals("4"))lSizeFee=lDeliveryFee.getSize4();
    else if (lOrderDetail.getCmdtySize().equals("5"))lSizeFee=lDeliveryFee.getSize5();
    else if (lOrderDetail.getCmdtySize().equals("6"))lSizeFee=lDeliveryFee.getSize6();
    else if (lOrderDetail.getCmdtySize().equals("7"))lSizeFee=lDeliveryFee.getSize7();
    else if (lOrderDetail.getCmdtySize().equals("8"))lSizeFee=lDeliveryFee.getSize8();
    else if (lOrderDetail.getCmdtySize().equals("9"))lSizeFee=lDeliveryFee.getSize9();
    else if (lOrderDetail.getCmdtySize().equals("10"))lSizeFee=lDeliveryFee.getSize10();
    else lSizeFee="0";
    
    if (Long.parseLong(lSizeFee)>Long.parseLong(lMaxFee)){
      lMaxFee=lSizeFee;
    }
    if (lFeeRule.getMultiBuyFlg().equals("1"))lResFee=SIUtil.add_LL(lResFee,SIUtil.multi_LL(lFeeRule.getUnitPrice(),lOrderDetail.getAmount()));
    else lResFee=SIUtil.add_LL(lResFee,lSizeFee);
    
    log.debug("lOrderDetail="+lOrderDetail.getDeliveryCode()+",lResFee="+lResFee+",lMaxFee="+lMaxFee);
    
    log.debug("lResFee="+lResFee
           +",lMaxFee="+lMaxFee
           +",lFeeRule.getUnitPrice()="+lFeeRule.getUnitPrice()
           +",lActTotalOfPrice="+lActTotalOfPrice
           +",lFeeRule.getPrice()="+lFeeRule.getPrice()
           +",lFeeRule.getFreeFlg()="+lFeeRule.getFreeFlg()
           +",lFeeRule.getMultiBuyFlg()="+lFeeRule.getMultiBuyFlg());
    
    if (lFeeRule.getMultiBuyFlg().equals("1")){
      lResFee=SIUtil.sub_LL(SIUtil.add_LL(lResFee,lMaxFee),lFeeRule.getUnitPrice());
    }
    
    if(lOrderDelivery.getDeliveryTaxFlg().equals("1")){
      lResFee = SITax.calcPriceIncTax(lResFee,lOrderDelivery.getTaxRate()); 
    }
//7.2.0 ST1030 削除（ソース整理） ここから
//    if (lEnable){
//      lSqlBuf=new StringBuffer("UPDATE OrderDeliveryTbl SET DeliveryFee=");
//      lSqlBuf.append(SIDBUtil.SQL2Str(lResFee," "));
//      lSqlBuf.append("WHERE OrderCode=").append(SIDBUtil.SQL2Str(lOrderDelivery.getOrderCode()," "));
//      lSqlBuf.append("AND DeliveryCode=").append(SIDBUtil.SQL2Str(lOrderDelivery.getDeliveryCode()));
//      SIDBUtil.execSQL(lConnection, lSqlBuf.toString());
//    }
    return lResFee;
  }
}