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

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;

import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import jp.co.sint.basic.SIEstimate;
import jp.co.sint.basic.SIEstimateDelivery;
import jp.co.sint.basic.SIKeepDetail;
import jp.co.sint.basic.SILogin;
import jp.co.sint.basic.SIPDFUtil;
import jp.co.sint.basic.SIPointRule;
import jp.co.sint.basic.SITax;
import jp.co.sint.basic.SIUserInfo;
import jp.co.sint.beans.mallmgr.UIRegNewEstimate;
import jp.co.sint.beans.mallmgr.UIRegNewKeep;
import jp.co.sint.beans.mallmgr.UIRegUpdateEstimate;
import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIDBMultiConf;
import jp.co.sint.config.SIPDFConf;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.database.SIDatabaseConnection;
import jp.co.sint.database.SIDateTimeType;
import jp.co.sint.database.SIDeleteRec;
import jp.co.sint.database.SIDuplicateKeyException;
import jp.co.sint.database.SIInsertRec;
import jp.co.sint.database.SIModifyRec;
import jp.co.sint.database.SISpcType;
import jp.co.sint.docs.EstimatePdf;
import jp.co.sint.servlet.SIServlet;
import jp.co.sint.tools.SIBGUtil;
import jp.co.sint.tools.SICustomError;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIDateTime;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIStringUtil;
import jp.co.sint.tools.SIURLParameter;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;

import com.lowagie.text.DocumentException;

/**
 * @version $Id: SIRegIndividualSrv.java,v 1.0 2005/08/19 Exp $
 * @author Tsuyoshi Yagi <br>
 * Description: 見積情報修正を行うServlet
 * <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>
 * T.Yagi 2005/12/09 Original
 */
public class SIUpdateEstimateSrv extends SIServlet {
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  /**
   * <b>doUpdate</b> HTTP リクエストの処理
   * 
   * @param なし
   * @return なし
   * @throws ServletException
   * @throws IOException
   */
  public void doUpdate(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    SILogin manLogin = SIHTMLUtil.getLogin(request);
    if (!manLogin.isLogin()) {
      forwardKey(request, response, "webshop.jsp.manager.login");
      return;
    }
    
    HttpSession session = request.getSession(true); // セッションの取得
    SIDatabaseConnection databaseConnection = new SIDatabaseConnection(); // DBへのコネクションの作成
    SIURLParameter urlParam = new SIURLParameter(request);
    
    String actionName = this.getActionName(urlParam); // 画面からのアクション
    try {
      Connection conn = databaseConnection.getConnection();
      
      if (actionName.equals(SIConfig.SIACTION_CONFIRM)) {
        UIRegUpdateEstimate updateEstimate = new UIRegUpdateEstimate();
        updateEstimate.init(request, urlParam, conn);
        // EDBTG003-00 mng-paku add start
        Collection keepColl = UIRegNewKeep.getEstimateList(conn, updateEstimate.getEstimate().getKeepNumber(), updateEstimate.getEstimate().getMemberLevelCode());
        updateEstimate.initDelivery(request, urlParam, conn, keepColl);
        // EDBTG003-00 mng-paku add end
        if (cmdtyCheck(conn, updateEstimate.getEstimate().getKeepNumber())) {
          if (updateEstimate.validate(request)&&updateEstimate.validateVariablePrice(conn,request)) {
            session.setAttribute(SIConfig.SISESSION_MAN_UPDATE_ESTIMATE, updateEstimate);
            forwardKey(request, response, "webshop.jsp.manager.estimate.updateestimate.con");
          } else {
            session.setAttribute(SIConfig.SISESSION_MAN_UPDATE_ESTIMATE, updateEstimate);
            forwardKey(request, response, "webshop.jsp.manager.estimate.updateestimate");
          }
        } else {
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("manager.message.error.estimate.cmdty"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          session.setAttribute(SIConfig.SISESSION_MAN_UPDATE_ESTIMATE, updateEstimate);
          forwardKey(request, response, "webshop.jsp.manager.estimate.updateestimate");
        }
      } else if (actionName.equals(SIConfig.SIACTION_BACK)) {
        String backURL = (String) urlParam.getParam("backURLTxt");
        forwardKey(request, response, backURL);
      } else if (actionName.equals(SIConfig.SIACTION_REFRESH)) {
        UIRegUpdateEstimate updateEstimate = new UIRegUpdateEstimate();
        updateEstimate.init(request, urlParam, conn);
        updateEstimate.validate(request);
        updateEstimate.validateVariablePrice(conn,request);
        session.setAttribute(SIConfig.SISESSION_MAN_UPDATE_ESTIMATE, updateEstimate);
        forwardKey(request, response, "webshop.jsp.manager.estimate.updateestimate");
      } else if (actionName.equals(SIConfig.SIACTION_REGIST)) {
        UIRegUpdateEstimate updateEstimate = (UIRegUpdateEstimate) session.getAttribute(SIConfig.SISESSION_MAN_UPDATE_ESTIMATE);
        try {
          updateEstimateData(conn, updateEstimate);
          try {
            databaseConnection.getConnection().commit();
            
          } catch (SQLException sqle) {}
          session.setAttribute(SIConfig.SISESSION_MAN_UPDATE_ESTIMATE, updateEstimate);
          forwardKey(request, response, "webshop.jsp.manager.estimate.updateestimate.res");
        } catch (SIDuplicateKeyException sqle) {
          try {
            databaseConnection.getConnection().rollback();
          } catch (SQLException ee) {}
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("database.insert.duplicate"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          forwardKey(request, response, "webshop.jsp.manager.estimate.updateestimate");
        } catch (SIDBAccessException sqle) {
          try {
            databaseConnection.getConnection().rollback();
          } catch (SQLException ee) {}
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("webshop.jsp.manager.cmdty.individual"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          forwardKey(request, response, "webshop.jsp.manager.estimate.updateestimate");
        }
      } else if (actionName.equals(SIConfig.SIACTION_MODIFY)) {
        UIRegUpdateEstimate estimateFull = new UIRegUpdateEstimate();
        estimateFull.init(request, urlParam, conn);
        estimateFull.validate(request);
        SIEstimateDelivery estimateDelivery = estimateFull.getEstimateDelivery();
        estimateDelivery = SIEstimateDelivery.getDeliveryAddress(conn, estimateFull.getEstimate().getCustCode(), estimateDelivery.getAddressCode());
        estimateFull.setEstimateDelivery(estimateDelivery);
        session.setAttribute(SIConfig.SISESSION_MAN_UPDATE_ESTIMATE, estimateFull);
        forwardKey(request, response, "webshop.jsp.manager.estimate.updateestimate");
      } else if (actionName.equals("order_con")) {
        UIRegUpdateEstimate updateEstimate = new UIRegUpdateEstimate();
        updateEstimate.init(request, urlParam, conn);
        updateEstimate.setBackURL("webshop.jsp.manager.estimate.updateestimate");
        if (cmdtyCheck(conn, updateEstimate.getEstimate().getKeepNumber())) {
          if (!updateEstimate.orderValidate(request, conn)) {
            session.setAttribute(SIConfig.SISESSION_MAN_UPDATE_ESTIMATE, updateEstimate);
            forwardKey(request, response, "webshop.jsp.manager.estimate.updateestimate");
          } else {
            session.setAttribute(SIConfig.SISESSION_MAN_UPDATE_ESTIMATE, updateEstimate);
            forwardKey(request, response, "webshop.jsp.manager.estimate.orderestimate.con");
          }
        } else {
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("manager.message.error.estimate.cmdty"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          session.setAttribute(SIConfig.SISESSION_MAN_UPDATE_ESTIMATE, updateEstimate);
          forwardKey(request, response, "webshop.jsp.manager.estimate.updateestimate");
        }
      } else if (actionName.equals(SIConfig.SIACTION_RESET)) {
        UIRegUpdateEstimate updateEstimate = new UIRegUpdateEstimate();
        updateEstimate.init(request, urlParam, conn);
        updateEstimate.validate(request);
        session.setAttribute(SIConfig.SISESSION_MAN_UPDATE_ESTIMATE, updateEstimate);
        forwardKey(request, response, "webshop.jsp.manager.estimate.updateestimate");
      } else if (actionName.equals("order_reg")) {
        UIRegUpdateEstimate orderEstimate = (UIRegUpdateEstimate) session.getAttribute(SIConfig.SISESSION_MAN_UPDATE_ESTIMATE);
        try {
          if (SIUtil.isNull(orderEstimate.getEstimate().getEstimataCode())){
            UIRegNewEstimate estimateFull = (UIRegNewEstimate) session.getAttribute(SIConfig.SISESSION_MAN_ESTIMATE_NEW_ESTIMATE);
            orderEstimate.getEstimate().setEstimataCode(SIEstimateSrv.insertEstimate(conn, estimateFull));
            try {conn.commit();} catch (SQLException sqle) {}
          }
          String orderCode = insertOrder(conn, orderEstimate, request);
          SIHacchuSrv.createHacchuData(conn, orderCode);
          try {
            databaseConnection.getConnection().commit();
          } catch (SQLException sqle) {}
          orderEstimate.setOrderCode(orderCode);
          session.setAttribute(SIConfig.SISESSION_MAN_UPDATE_ESTIMATE, orderEstimate);
          forwardKey(request, response, "webshop.jsp.manager.estimate.orderestimate.res");
        } catch (SIDuplicateKeyException sqle) {
          try {
            databaseConnection.getConnection().rollback();
          } catch (SQLException ee) {}
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("database.insert.duplicate"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          forwardKey(request, response, "webshop.jsp.manager.estimate.updateestimate");
        } catch (SIDBAccessException sqle) {
          try {
            databaseConnection.getConnection().rollback();
          } catch (SQLException ee) {}
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("webshop.jsp.manager.cmdty.individual"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          forwardKey(request, response, "webshop.jsp.manager.estimate.updateestimate");
        }
      } else if (actionName.equals("mod_keep")) {
        UIRegUpdateEstimate updateEstimate = new UIRegUpdateEstimate();
        updateEstimate.init(request, urlParam, conn);
        session.setAttribute(SIConfig.SISESSION_MAN_UPDATE_ESTIMATE, updateEstimate);
        UIRegNewKeep newkeep = new UIRegNewKeep();
        newkeep.setBackURL("webshop.jsp.manager.estimate.updateestimate");
        newkeep.setKeepNumber(updateEstimate.getEstimate().getKeepNumber());
        session.setAttribute(SIConfig.SISESSION_MAN_ESTIMATE_NEW_KEEP, newkeep);
        forwardKey(request, response, "webshop.jsp.manager.estimate.newkeep");
      } else if (actionName.equals(SIConfig.SIACTION_PDF)) {
        UIRegUpdateEstimate updateEstimate = new UIRegUpdateEstimate();
        updateEstimate.init(request, urlParam, conn);
        updateEstimate.validate(request);
        session.setAttribute(SIConfig.SISESSION_MAN_UPDATE_ESTIMATE, updateEstimate);
        this.producePDFFile(response, conn, updateEstimate);
        if (!response.isCommitted()) forwardKey(request, response, "webshop.jsp.manager.estimate.updateestimate");
        
      } else if (actionName.equals(SIConfig.SIACTION_PDF2)) {
        UIRegUpdateEstimate updateEstimate = (UIRegUpdateEstimate) session.getAttribute(SIConfig.SISESSION_MAN_UPDATE_ESTIMATE);
        this.producePDFFile(response, conn, updateEstimate);
        if (!response.isCommitted()) forwardKey(request, response, "webshop.jsp.manager.estimate.updateestimate");
        
      } else if (actionName.equals("pdf_detail")) {
        UIRegUpdateEstimate updateEstimate = (UIRegUpdateEstimate) session.getAttribute(SIConfig.SISESSION_MAN_UPDATE_ESTIMATE);
        session.setAttribute(SIConfig.SISESSION_MAN_UPDATE_ESTIMATE, updateEstimate);
        this.producePDFFile(response, conn, updateEstimate);
        if (!response.isCommitted()) forwardKey(request, response, "webshop.jsp.manager.estimate.estimate.detail");
        
      } else {
        forwardKey(request, response, "webshop.jsp.manager.estimate.updateestimate");
      }
    } catch (SQLException e) {
      e.printStackTrace();
      throw new ServletException();
    } catch (NamingException e) {
      e.printStackTrace();
      throw new ServletException();
    } catch (SIDBAccessException e1) {
      e1.printStackTrace();
    } catch (DocumentException e) {
      e.printStackTrace();
      throw new ServletException();
    } finally {
      databaseConnection.close();
    }
  }

  public void updateEstimateData(Connection conn, UIRegUpdateEstimate estimateFull) throws SIDuplicateKeyException, SIDBAccessException {
    SIEstimate estimate = estimateFull.getEstimate();
    SIEstimateDelivery estimateDelivery = estimateFull.getEstimateDelivery();
    LinkedHashMap hash = estimateFull.getHash();

    String taxRate = SITax.getTaxRate(conn);
    SISpcType lSpcType = new SISpcType();
    SIInsertRec lRec = new SIInsertRec("estimatetbl");

    SIModifyRec mRec = new SIModifyRec("estimatetbl");
    mRec.addCondition("estimatecode", estimate.getEstimataCode());
    mRec.add("custCode", estimate.getCustCode());
    mRec.add("custName", estimate.getCustName());
    mRec.add("custpronname", estimate.getCustPronName());
    mRec.add("email", estimate.getEmail());
    mRec.add("estimateaddressee", estimate.getEstimateAddrressee());
    mRec.add("postcode1", estimate.getPostCode1());
    mRec.add("postcode2", estimate.getPostCode2());
    mRec.add("address1", estimate.getAddress1());
    mRec.add("address2", estimate.getAddress2());
    mRec.add("address3", estimate.getAddress3());
    mRec.add("companyname", estimate.getCompanyName());
    mRec.add("tel", estimate.getTel());
    mRec.add("fax", estimate.getFax());
    mRec.add("paymethodname", estimate.getPayMethodName());
    mRec.add("paymentflg", estimate.getPaymentFlg());
    if (SIUtil.isNotNull(estimate.getFeeIncTax())) {
      mRec.add("fee", estimate.getFeeIncTax());
    } else {
      mRec.add("fee", "0");
    }
    mRec.add("taxrate", taxRate);
    mRec.add("sumofdiscount", estimate.getSumOfDiscount());
    mRec.add("discountFee", estimate.getDiscountFee());
    mRec.add("discountDeliveryFee", estimate.getDiscountDeliveryFee());
    mRec.add("sendmailflg", estimate.getSendMailFlg());
    mRec.add("status", "1");
    mRec.add("feetaxflg", "0");
    mRec.add("contactmsg", estimate.getContactMsg());
    mRec.add("memo", estimate.getMemo());
    mRec.add("pointenableflg", estimate.getPointEnableFlg());
    mRec.add("updatemallshopcode", estimate.getUpdateMallShopCode());
    mRec.add("updateusercode", estimate.getUpdateUserCode());
    mRec.add("businessform", estimate.getBusinessForm());
    mRec.add("orderroute", estimate.getOrderRoute());
    mRec.add("storetel", estimate.getStoreTel());
    mRec.add("usepoint", estimate.getSumByPoint());
    lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
    mRec.add("updatedatetime", lSpcType);
    mRec.add("memberlevelcode", estimate.getMemberLevelCode());
    // EDBTG003-00 mng-paku add start
    mRec.add("setdiscount", estimate.getSumOfSetDiscount());
    // EDBTG003-00 mng-paku add end
    mRec.execute(conn);
    
    SIDeleteRec delRec = new SIDeleteRec("estimatedeliverytbl");
    delRec.addCondition("estimatecode", estimate.getEstimataCode());
    delRec.execute(conn);
    
    delRec = new SIDeleteRec("estimatedetailtbl");
    delRec.addCondition("estimatecode", estimate.getEstimataCode());
    delRec.execute(conn);
    
    String deliveryTaxFlg = SIDBUtil.getFirstData(conn, "select deliveryTaxFlg from mallshopmtbl");
    String deliveryName1 = SIDBUtil.getFirstData(conn, "select deliverytypename from deliverytypemtbl where deliverytypecode='0'");
    String deliveryName2 = SIDBUtil.getFirstData(conn, "select deliverytypename from deliverytypemtbl where deliverytypecode='1'");
    int deliveryNum = 0;
    if (!estimateFull.getCmdtyCnt1().equals("0")) {
      deliveryNum++;
      lRec = new SIInsertRec("estimatedeliverytbl");
      lRec.add("estimatecode", estimate.getEstimataCode());
      lRec.add("deliverycode", Integer.toString(deliveryNum));
      if (!estimateDelivery.getAddressCode().equals("0")) {
        String deliveryName = SIDBUtil.getFirstData(conn, "select deliveryName from custaddresstbl where custcode ='" + estimate.getCustCode() + "' and addresscode = '"
            + estimateDelivery.getAddressCode() + "'");
        lRec.add("deliveryname", deliveryName);
      } else {
        lRec.add("deliveryname", "本人");
      }
      lRec.add("deliveryaddressee", estimateDelivery.getDeliveryAddressee());
      lRec.add("email", estimateDelivery.getEmail());
      lRec.add("postcode1", estimateDelivery.getPostCode1());
      lRec.add("postcode2", estimateDelivery.getPostCode2());
      lRec.add("address1", estimateDelivery.getAddress1());
      lRec.add("address2", estimateDelivery.getAddress2());
      lRec.add("address3", estimateDelivery.getAddress3());
      lRec.add("companyname", estimateDelivery.getCompanyName());
      lRec.add("tel", estimateDelivery.getTel());
      lRec.add("fax", estimateDelivery.getFax());
      lRec.add("deliveryfee", estimateFull.getDeliveryFee1());
      lRec.add("deliverytaxflg", deliveryTaxFlg);
      lRec.add("taxrate", taxRate);
      lRec.add("deliverytypename", deliveryName1);
      lRec.add("custcode", estimate.getCustCode());
      lRec.add("addresscode", estimateDelivery.getAddressCode());
      lRec.add("mallshopcode", "0");
      lRec.add("deliverytypecode", "0");
      lRec.execute(conn);
      
    }
    if (!estimateFull.getCmdtyCnt2().equals("0")) {
      deliveryNum++;
      lRec = new SIInsertRec("estimatedeliverytbl");
      lRec.add("estimatecode", estimate.getEstimataCode());
      lRec.add("deliverycode", Integer.toString(deliveryNum));
      if (!estimateDelivery.getAddressCode().equals("0")) {
        String deliveryName = SIDBUtil.getFirstData(conn, "select deliveryName from custaddresstbl where custcode ='" + estimate.getCustCode() + "' and addresscode = '"
            + estimateDelivery.getAddressCode() + "'");
        lRec.add("deliveryname", deliveryName);
      } else {
        lRec.add("deliveryname", "本人");
      }
      lRec.add("deliveryaddressee", estimateDelivery.getDeliveryAddressee());
      lRec.add("email", estimateDelivery.getEmail());
      lRec.add("postcode1", estimateDelivery.getPostCode1());
      lRec.add("postcode2", estimateDelivery.getPostCode2());
      lRec.add("address1", estimateDelivery.getAddress1());
      lRec.add("address2", estimateDelivery.getAddress2());
      lRec.add("address3", estimateDelivery.getAddress3());
      lRec.add("companyname", estimateDelivery.getCompanyName());
      lRec.add("tel", estimateDelivery.getTel());
      lRec.add("fax", estimateDelivery.getFax());
      lRec.add("deliveryfee", estimateFull.getDeliveryFee1());
      lRec.add("deliverytaxflg", deliveryTaxFlg);
      lRec.add("taxrate", taxRate);
      lRec.add("deliverytypename", deliveryName2);
      lRec.add("custcode", estimate.getCustCode());
      lRec.add("addresscode", estimateDelivery.getAddressCode());
      lRec.add("mallshopcode", "0");
      lRec.add("deliverytypecode", "1");
      lRec.execute(conn);
    }
    Collection detailColl = UIRegNewKeep.getEstimateList(conn, estimate.getKeepNumber(), estimate.getMemberLevelCode());
    Iterator ite = detailColl.iterator();
    int detailNum = 0;
    String shopName = SIDBUtil.getFirstData(conn, "select mallshopname from mallshopmtbl");
    String deliveryFee = "";
    String deliveryDate = "";
    String deliveryTime = "";
    
    while (ite.hasNext()) {
      detailNum++;
      SIKeepDetail detail = (SIKeepDetail) ite.next();
      lRec = new SIInsertRec("estimatedetailtbl");
      lRec.add("estimatecode", estimate.getEstimataCode());
      // if (!estimateFull.getCmdtyCnt1().equals("0")) {
      // if (detail.getDeliveryType().equals("0")) {
      // lRec.add("deliverycode", "1");
      // } else if (detail.getDeliveryType().equals("1")) {
      // lRec.add("deliverycode", "2");
      // }
      // } else {
      // lRec.add("deliverycode", "1");
      // }
      if (detail.getDeliveryType().equals("1")) {
        lRec.add("deliverycode", "1");
      } else {
        lRec.add("deliverycode", "0");
      }
      // EDBTG003-00 nagayoshi mod start
//      deliveryFee = (String) hash.get("deliveryFee_" + detail.getCmdtyCode() + "_" + detail.getIndividualCode());
      deliveryFee = (String) hash.get("deliveryFee_" + detail.getCmdtyCode() + "_" + detail.getIndividualCode() + "_" + detail.getSetCode() + "_" + detail.getSetDetailCode());
      // EDBTG003-00 nagayoshi mod end
      // EDBTG003-00 ohsugi mod start
//      deliveryDate = (String) hash.get("deliveryDate_" + detail.getCmdtyCode() + "_" + detail.getIndividualCode());
      deliveryDate = (String) hash.get("deliveryDate_" + detail.getCmdtyCode() + "_" + detail.getIndividualCode() + "_" + detail.getSetCode() + "_" + detail.getSetDetailCode());
//      deliveryTime = (String) hash.get("deliveryTime_" + detail.getCmdtyCode() + "_" + detail.getIndividualCode());
      deliveryTime = (String) hash.get("deliveryTime_" + detail.getCmdtyCode() + "_" + detail.getIndividualCode() + "_" + detail.getSetCode() + "_" + detail.getSetDetailCode());
      // EDBTG003-00 ohsugi mod end
      lRec.add("detailcode", detailNum);
      lRec.add("shopcode", "0");
      lRec.add("cmdtycode", detail.getCmdtyCode());
      lRec.add("individualcode", detail.getIndividualCode());
      lRec.add("shopname", shopName);
      lRec.add("cmdtyname", detail.getCmdtyName());
      lRec.add("amount", detail.getAmount());
      lRec.add("orgprice", detail.getOrgPrice());
      lRec.add("price", detail.getPrice());
      String taxFlg = SIDBUtil.getFirstData(conn, "select taxflg from cmdtymtbl where cmdtycode = '" + detail.getCmdtyCode() + "'");
      lRec.add("taxflg", taxFlg);
      lRec.add("taxrate", taxRate);
      lRec.add("cmdtysize", detail.getSize());
      lRec.add("deliveryfee", deliveryFee);
      lRec.add("deliverydate", deliveryDate);
      lRec.add("deliverytime", deliveryTime);
      // EDBTG003-00 mng-paku add start
      lRec.add("setdetailflg", detail.getSetDetailFlg());
      if (SIUtil.isNotNull(detail.getSetDetailFlg()) && !SIConfig.SET_DETAIL_FLG_NORMAL.equals(detail.getSetDetailFlg())) {
        lRec.add("setcode", detail.getSetCode());
        lRec.add("setcmdtycode", detail.getSetCmdtyCode());
        lRec.add("setindividualcode", detail.getSetIndividualCode());
        lRec.add("setdetailcode", detail.getSetDetailCode());
      }
      // EDBTG003-00 mng-paku add end
      lRec.execute(conn);
    }
    
    // EDBTG003-00 mng-paku add start
    // 削除
    if (detailColl != null && detailColl.size() > 0) {
      delRec = new SIDeleteRec("estimatesetcmdtytbl");
      delRec.addCondition("estimatecode", estimate.getEstimataCode());
      delRec.execute(conn);
      // 挿入
      ite = detailColl.iterator();
      while (ite.hasNext()) {
        SIKeepDetail detail = (SIKeepDetail) ite.next();
        if (SIUtil.isNotNull(detail.getSetDetailFlg()) && !SIConfig.SET_DETAIL_FLG_NORMAL.equals(detail.getSetDetailFlg())) {
          // セット毎の登録チェック
          StringBuffer sql = new StringBuffer();
          sql.append("SELECT estimateCode ");
          sql.append("FROM estimatesetcmdtytbl ");
          sql.append("WHERE estimatecode = ").append(SIDBUtil.SQL2Str(estimate.getEstimataCode(), " "));
          sql.append("AND setcode = ").append(SIDBUtil.SQL2Str(detail.getSetCode(), " "));
          sql.append("AND setcmdtycode = ").append(SIDBUtil.SQL2Str(detail.getSetCmdtyCode(), " "));
          sql.append("AND setindividualcode = ").append(SIDBUtil.SQL2Str(detail.getSetIndividualCode(), " "));
          String existData = SIDBUtil.getFirstData(conn, sql.toString());
          
          if (SIUtil.isNull(existData)) {
            lRec = new SIInsertRec("estimatesetcmdtytbl");
            lRec.add("estimatecode", estimate.getEstimataCode());
            lRec.add("setcode", detail.getSetCode());
            lRec.add("setcmdtycode", detail.getSetCmdtyCode());
            lRec.add("setindividualcode", detail.getSetIndividualCode());
            lRec.add("cmdtyname", detail.getSetCmdtyName());
            lRec.add("setdiscount", detail.getSetDiscount());
            lRec.add("setamount", detail.getSetAmount());
            lRec.execute(conn);
          }
        }
      }
    }
    // EDBTG003-00 mng-paku add end
  }
  
  public String insertOrder(Connection conn, UIRegUpdateEstimate estimateFull, HttpServletRequest request) throws SIDuplicateKeyException, SIDBAccessException, SQLException {
    SIEstimate estimate = estimateFull.getEstimate();
    SIEstimateDelivery estimateDelivery = estimateFull.getEstimateDelivery();
    LinkedHashMap hash = estimateFull.getHash();
    SIDateTime time = new SIDateTime();
    StringBuffer individualCode = new StringBuffer();// ポイント計算用1
    StringBuffer cmdtyTotalPrice = new StringBuffer();// ポイント計算用2
    
    SIUserInfo lUserLogin = SIHTMLUtil.getUserInfo(request);
    
    String taxRate = SITax.getTaxRate(conn);
    SISpcType lSpcType = new SISpcType();
    SIInsertRec lRec = new SIInsertRec("ordertbl");
    String orderCode = SIDBUtil.getFirstData(conn, SIDBUtil.getNextVal("ordertbl_ordercd_seq"));
    lRec.add("ordercode", orderCode);
    lRec.add("custcode", estimate.getCustCode());
    lRec.add("custcompanyflg", "0");
    lRec.add("custName", estimate.getCustName());
    lRec.add("custpronname", estimate.getCustPronName());
    lRec.add("email", estimate.getEmail());
    lRec.add("orderaddressee", estimate.getEstimateAddrressee());
    lRec.add("postcode1", estimate.getPostCode1());
    lRec.add("postcode2", estimate.getPostCode2());
    lRec.add("address1", estimate.getAddress1());
    lRec.add("address2", estimate.getAddress2());
    lRec.add("address3", estimate.getAddress3());
    lRec.add("companyname", estimate.getCompanyName());
    lRec.add("tel", estimate.getTel());
    lRec.add("fax", estimate.getFax());
    lRec.add("paymethodname", estimate.getPayMethodName());
    lRec.add("paymentflg", estimate.getPaymentFlg());
    if (estimate.getPaymentFlg().equals("3")) lRec.add("RECEIPTDATE", new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp"));
    lRec.add("fee", estimate.getFee());
    lRec.add("taxrate", taxRate);
    lRec.add("sumofdiscount", estimate.getSumOfDiscount());
    
    lRec.add("discountFee", estimate.getDiscountFee());
    lRec.add("discountDeliveryFee", estimate.getDiscountDeliveryFee());
    
    lRec.add("sendmailflg", lUserLogin.getNewMailFlg());
    lRec.add("sumbypoint", estimate.getSumByPoint());
    lRec.add("status", "1");
    lRec.add("feetaxflg", "0");
    lRec.add("contactmsg", estimate.getContactMsg());
    lRec.add("memo", estimate.getMemo());
    lRec.add("receiptmailflg", "0");
    //
    lRec.add("pointenableflg", "1");
    lRec.add("orderbranchcode", "1");
    lRec.add("enabledflg", "1");
    lRec.add("status", "1");
    lRec.add("updatemallshopcode", estimate.getUpdateMallShopCode());
    lRec.add("updateusercode", estimate.getUpdateUserCode());
    //
    lRec.add("job", estimate.getBusinessForm());
    if (!estimate.getChargeCode().equals("admin")) {
      lRec.add("branchcode", estimate.getBrarnchCode());
      lRec.add("chargecode", estimate.getChargeCode());
    }
    lRec.add("storetel", estimate.getStoreTel());
    lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
    lRec.add("initdatetime", lSpcType);
    lRec.add("updatedatetime", lSpcType);
    lRec.add("orderroute", estimate.getOrderRoute());
    String keepmaildate = SIDBUtil.getFirstData(conn, "SELECT keepmaildatetime FROM keeptbl WHERE keepnumber='" + estimate.getKeepNumber() + "'");
    lRec.add("keepmaildate", keepmaildate);
    // EDBTG003-00 mng-paku add start
    lRec.add("setdiscount", estimate.getSumOfSetDiscount());
    // EDBTG003-00 mng-paku add end
    lRec.execute(conn);
    
    String deliveryTaxFlg = SIDBUtil.getFirstData(conn, "select deliveryTaxFlg from mallshopmtbl");
    String deliveryName1 = SIDBUtil.getFirstData(conn, "select deliverytypename from deliverytypemtbl where deliverytypecode='0'");
    String deliveryName2 = SIDBUtil.getFirstData(conn, "select deliverytypename from deliverytypemtbl where deliverytypecode='1'");
    int deliveryNum = 0;
    if (!estimateFull.getCmdtyCnt1().equals("0")) {
      deliveryNum++;
      lRec = new SIInsertRec("orderdeliverytbl");
      lRec.add("ordercode", orderCode);
      lRec.add("deliverycode", Integer.toString(deliveryNum));
      if (!estimateDelivery.getAddressCode().equals("0")) {
        String deliveryName = SIDBUtil.getFirstData(conn, "select deliveryName from custaddresstbl where custcode ='" + estimate.getCustCode() + "' and addresscode = '"
            + estimateDelivery.getAddressCode() + "'");
        lRec.add("deliveryname", deliveryName);
      } else {
        lRec.add("deliveryname", "本人");
      }
      
      lRec.add("deliveryaddressee", estimateDelivery.getDeliveryAddressee());
      if (estimateDelivery.getAddressCode().equals("0")) {
        lRec.add("homeflg", "0");
      } else {
        lRec.add("homeflg", "1");
      }
      lRec.add("custcompanyflg", "0");
      lRec.add("email", estimate.getEmail());
      lRec.add("postcode1", estimateDelivery.getPostCode1());
      lRec.add("postcode2", estimateDelivery.getPostCode2());
      lRec.add("address1", estimateDelivery.getAddress1());
      lRec.add("address2", estimateDelivery.getAddress2());
      lRec.add("address3", estimateDelivery.getAddress3());
      lRec.add("companyname", estimateDelivery.getCompanyName());
      lRec.add("tel", estimateDelivery.getTel());
      lRec.add("fax", estimateDelivery.getFax());
      lRec.add("deliveryfee", estimateFull.getDeliveryFee1());
      lRec.add("deliverytaxflg", deliveryTaxFlg);
      lRec.add("taxrate", taxRate);
      lRec.add("deliverytypename", deliveryName1);
      lRec.add("shippmentmailflg", "0");
      lRec.add("custcode", estimate.getCustCode());
      lRec.add("addresscode", estimateDelivery.getAddressCode());
      lRec.add("mallshopcode", "0");
      lRec.add("deliverytypecode", "0");
      lRec.execute(conn);
      
    }
    if (!estimateFull.getCmdtyCnt2().equals("0")) {
      deliveryNum++;
      lRec = new SIInsertRec("orderdeliverytbl");
      lRec.add("ordercode", orderCode);
      lRec.add("deliverycode", Integer.toString(deliveryNum));
      if (!estimateDelivery.getAddressCode().equals("0")) {
        String deliveryName = SIDBUtil.getFirstData(conn, "select deliveryName from custaddresstbl where custcode ='" + estimate.getCustCode() + "' and addresscode = '"
            + estimateDelivery.getAddressCode() + "'");
        lRec.add("deliveryname", deliveryName);
      } else {
        lRec.add("deliveryname", "本人");
      }
      
      lRec.add("deliveryaddressee", estimateDelivery.getDeliveryAddressee());
      if (estimateDelivery.getAddressCode().equals("0")) {
        lRec.add("homeflg", "0");
      } else {
        lRec.add("homeflg", "1");
      }
      lRec.add("email", estimate.getEmail());
      lRec.add("postcode1", estimateDelivery.getPostCode1());
      lRec.add("postcode2", estimateDelivery.getPostCode2());
      lRec.add("address1", estimateDelivery.getAddress1());
      lRec.add("address2", estimateDelivery.getAddress2());
      lRec.add("address3", estimateDelivery.getAddress3());
      lRec.add("companyname", estimateDelivery.getCompanyName());
      lRec.add("tel", estimateDelivery.getTel());
      lRec.add("fax", estimateDelivery.getFax());
      lRec.add("deliveryfee", estimateFull.getDeliveryFee2());
      lRec.add("deliverytaxflg", deliveryTaxFlg);
      lRec.add("taxrate", taxRate);
      lRec.add("deliverytypename", deliveryName2);
      lRec.add("shippmentmailflg", "0");
      lRec.add("custcode", estimate.getCustCode());
      lRec.add("addresscode", estimateDelivery.getAddressCode());
      lRec.add("mallshopcode", "0");
      lRec.add("deliverytypecode", "1");
      lRec.execute(conn);
    }
    Collection detailColl = UIRegNewKeep.getEstimateList(conn, estimate.getKeepNumber(), estimate.getMemberLevelCode());
    Iterator ite = detailColl.iterator();
    int detailNum1 = 0;
    int detailNum2 = 0;
    String shopName = SIConfig.SIMALL.getMallShopName();
    String deliveryFee = "";
    String deliveryDate = "";
    String deliveryTime = "";
    // String lPointPrice = "0";
    
    String totalAmount = "0";
    String totalPrice = "0";
    
    while (ite.hasNext()) {
      
      SIKeepDetail detail = (SIKeepDetail) ite.next();
      lRec = new SIInsertRec("orderdetailtbl");
      lRec.add("ordercode", orderCode);
      String deliveryCode = "";
      if (!estimateFull.getCmdtyCnt1().equals("0")) {
        if (detail.getDeliveryType().equals("0")) {
          lRec.add("deliverycode", "1");
          deliveryCode = "1";
        } else if (detail.getDeliveryType().equals("1")) {
          lRec.add("deliverycode", "2");
          deliveryCode = "2";
        }
      } else {
        lRec.add("deliverycode", "1");
        deliveryCode = "1";
      }
      // EDBTG003-00 nagayoshi mod start
//      deliveryFee = (String) hash.get("deliveryFee_" + detail.getCmdtyCode() + "_" + detail.getIndividualCode());
      deliveryFee = (String) hash.get("deliveryFee_" + detail.getCmdtyCode() + "_" + detail.getIndividualCode() + "_" + detail.getSetCode() + "_" + detail.getSetDetailCode());
      // EDBTG003-00 nagayoshi mod end
      // EDBTG003-00 ohsugi mod start
//      deliveryDate = (String) hash.get("deliveryDate_" + detail.getCmdtyCode() + "_" + detail.getIndividualCode());
      deliveryDate = (String) hash.get("deliveryDate_" + detail.getCmdtyCode() + "_" + detail.getIndividualCode() + "_" + detail.getSetCode() + "_" + detail.getSetDetailCode());
//      deliveryTime = (String) hash.get("deliveryTime_" + detail.getCmdtyCode() + "_" + detail.getIndividualCode());
      deliveryTime = (String) hash.get("deliveryTime_" + detail.getCmdtyCode() + "_" + detail.getIndividualCode() + "_" + detail.getSetCode() + "_" + detail.getSetDetailCode());
      // EDBTG003-00 ohsugi mod end
    if (deliveryCode.equals("1")) {
        detailNum1++;
        lRec.add("detailcode", detailNum1);
      } else if (deliveryCode.equals("2")) {
        detailNum2++;
        lRec.add("detailcode", detailNum2);
      }
      lRec.add("shopcode", "0");
      lRec.add("cmdtycode", detail.getCmdtyCode());
      lRec.add("individualcode", detail.getIndividualCode());
      lRec.add("shopname", shopName);
      lRec.add("cmdtyname", detail.getCmdtyName());
      lRec.add("amount", detail.getAmount());
      lRec.add("orgprice", detail.getOrgPrice());
      lRec.add("price", detail.getPrice());
      String taxFlg = SIDBUtil.getFirstData(conn, "select taxflg from cmdtymtbl where cmdtycode = '" + detail.getCmdtyCode() + "'");
      lRec.add("taxflg", taxFlg);
      lRec.add("taxrate", taxRate);
      lRec.add("cmdtysize", detail.getSize());
      lRec.add("deliveryfee", deliveryFee);
      lRec.add("deliverydate", deliveryDate);
      lRec.add("deliverytime", deliveryTime);
      lRec.add("wrappingTaxFlg", "0");
      lRec.add("purchaseprice", detail.getPurchasePrice());
      lRec.add("processingexpence", detail.getProcessingExpence());
      lRec.add("consigngoodsfee", detail.getConsignGoodsFee());
      // EDBTG003-00 mng-paku add start
      lRec.add("setdetailflg", detail.getSetDetailFlg());
      if (SIUtil.isNotNull(detail.getSetDetailFlg()) && !SIConfig.SET_DETAIL_FLG_NORMAL.equals(detail.getSetDetailFlg())) {
        lRec.add("setcode", detail.getSetCode());
        lRec.add("setcmdtycode", detail.getSetCmdtyCode());
        lRec.add("setindividualcode", detail.getSetIndividualCode());
        lRec.add("setdetailcode", detail.getSetDetailCode());
      }
      // EDBTG003-00 mng-paku add end
      lRec.execute(conn);
      
      totalAmount = SIUtil.add(totalAmount, detail.getAmount());
      totalPrice = SIUtil.add(totalPrice, SIUtil.multi(detail.getAmount(), detail.getPrice()));
      
      // EDBTG003-00 elecs-matsushima add start
//      individualCode.append(detail.getIndividualCode()).append(",");
      if (detail.getSetDetailFlg().equals(SIConfig.SET_DETAIL_FLG_NORMAL)) {
        individualCode.append(detail.getIndividualCode()).append(",");
      } else {
        individualCode.append(detail.getSetIndividualCode()).append(",");
      }
      // EDBTG003-00 elecs-matsushima add end
      
      String priceWithoutTax = SIUtil.getPriceWithoutTax(detail.getPrice(), taxRate);
      cmdtyTotalPrice.append(SIUtil.multi(priceWithoutTax, detail.getAmount())).append(",");
      // lPointPrice = SIUtil.add(lPointPrice,SIUtil.multi(detail.getPrice(),detail.getAmount()));
      
      Statement statement = null;
      ResultSet resultSet = null;
      StringBuffer sqlStatement = new StringBuffer();
      SIInsertRec insertRec = new SIInsertRec();
      sqlStatement.append("SELECT branchcode,amount,cmdtytype FROM keepdetailtbl ");
      sqlStatement.append("WHERE keepnumber='" + estimate.getKeepNumber() + "' ");
      sqlStatement.append(" and cmdtycode='" + detail.getCmdtyCode() + "' ");
      sqlStatement.append(" and individualcode='" + detail.getIndividualCode() + "' ");
      // EDBTG003-00 elecs-matsushima add start
      if (detail.getSetDetailFlg().equals(SIConfig.SET_DETAIL_FLG_NORMAL)){
        sqlStatement.append(" and setcode='-1' ");
      } else {
        sqlStatement.append(" and setcode='" + detail.getSetCode() + "' ");
        sqlStatement.append(" and setdetailcode='" + detail.getSetDetailCode() + "' ");
      }
      // EDBTG003-00 elecs-matsushima add start
      
      try {
        statement = conn.createStatement();
        resultSet = statement.executeQuery(sqlStatement.toString());
        
//        // EDBTG003-00 mng-paku mod start
//        int countDetailcode = 0;
//        // EDBTG003-00 mng-paku mod end
        while (resultSet.next()) {
//          // EDBTG003-00 mng-paku mod start
//          // 最大detailcodeのチェック
//          String sql = "select max(detailcode) from orderallocationinfotbl where ordercode = '" + orderCode + "' and branchcode = '" + resultSet.getString("branchcode") + "' and cmdtycode = '"
//              + detail.getCmdtyCode() + "' and individualcode = '" + detail.getIndividualCode() + "' ";
//          String maxDetailcode = SIDBUtil.getFirstData(conn, sql);
//          if (countDetailcode == 0 && SIUtil.isNotNull(maxDetailcode) && StringUtils.isNumeric(maxDetailcode)) {
//            countDetailcode = Integer.parseInt(maxDetailcode);
//          }
//          // EDBTG003-00 mng-paku mod end
          String cmdtytype = resultSet.getString("cmdtytype");
          if (cmdtytype.equals("0")) {// キープ商品が在庫管理するものの場合のみ引当情報作成
            String branch = resultSet.getString("branchcode");
            String allocate = resultSet.getString("amount");
            insertRec = new SIInsertRec("orderallocationinfotbl");
            insertRec.add("ordercode", orderCode);
            insertRec.add("deliverycode", deliveryCode);
            if (deliveryCode.equals("1")) {
              insertRec.add("detailcode", detailNum1);
            } else if (deliveryCode.equals("2")) {
              insertRec.add("detailcode", detailNum2);
            }
            // EDBTG003-00 mng-paku del start
//            insertRec.add("detailcode", ++countDetailcode);
            // EDBTG003-00 mng-paku del end
            insertRec.add("shopcode", "0");
            insertRec.add("orderbranchcode", "1");
            insertRec.add("cmdtycode", detail.getCmdtyCode());
            insertRec.add("individualcode", detail.getIndividualCode());
            insertRec.add("branchcode", branch);
            insertRec.add("orderallocationnumber", allocate);
            insertRec.add("initdatetime", time.getFullDate());
            insertRec.add("updatedatetime", time.getFullDate());
            insertRec.execute(conn);
          }
        }
      } catch (Exception ex) {
        throw new SIDBAccessException(ex);
      } finally{
        SIDBUtil.close(statement, resultSet);
      }
      
      SIModifyRec mRec = new SIModifyRec("estimatetbl");
      mRec.addCondition("estimatecode", estimate.getEstimataCode());
      mRec.add("status", 2);
      mRec.execute(conn);
      
      mRec = new SIModifyRec("keeptbl");
      mRec.addCondition("keepnumber", estimate.getKeepNumber());
      mRec.add("status", 2);
      mRec.execute(conn);
      
    }
    
    // EDBTG003-00 mng-paku add start
    ite = detailColl.iterator();
    while (ite.hasNext()) {
      SIKeepDetail detail = (SIKeepDetail) ite.next();
      if (SIUtil.isNotNull(detail.getSetDetailFlg()) && !SIConfig.SET_DETAIL_FLG_NORMAL.equals(detail.getSetDetailFlg())) {
        // セット毎の登録チェック
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ordercode ");
        sql.append("FROM ordersetcmdtytbl ");
        sql.append("WHERE orderbranchcode = '1' ");
        sql.append("AND ordercode = ").append(SIDBUtil.SQL2Str(orderCode, " "));
        sql.append("AND setcode = ").append(SIDBUtil.SQL2Str(detail.getSetCode(), " "));
        sql.append("AND setcmdtycode = ").append(SIDBUtil.SQL2Str(detail.getSetCmdtyCode(), " "));
        sql.append("AND setindividualcode = ").append(SIDBUtil.SQL2Str(detail.getSetIndividualCode(), " "));
        String existData = SIDBUtil.getFirstData(conn, sql.toString());
        if (SIUtil.isNull(existData)) {
          lRec = new SIInsertRec("ordersetcmdtytbl");
          lRec.add("ordercode", orderCode);
          lRec.add("orderbranchcode", "1");
          lRec.add("setcode", detail.getSetCode());
          lRec.add("setcmdtycode", detail.getSetCmdtyCode());
          lRec.add("setindividualcode", detail.getSetIndividualCode());
          lRec.add("cmdtyname", detail.getSetCmdtyName());
          lRec.add("setdiscount", detail.getSetDiscount());
          lRec.add("setamount", detail.getSetAmount());
          lRec.execute(conn);
        }
      }
    }
    // EDBTG003-00 mng-paku add end
    
    // ポイント管理
    insertPointTableData(conn, estimate, orderCode, request);
    
    // 付加ポイント = 本体価格 - (値引き + ポイント使用)
    String discount = "0";
    discount = SIUtil.add(estimate.getSumOfDiscount(), estimate.getSumByPoint());
    // lPointPrice = SIUtil.sub_LL(lPointPrice,discount);
    
    String[] ic = individualCode.toString().split(",");
    String[] ct = cmdtyTotalPrice.toString().split(",");
    String[][] pointData = new String[ic.length][2];
    for (int count = 0; count < ic.length; count++) {
      pointData[count][0] = ic[count];
      pointData[count][1] = ct[count];
    }
    // ポイントルールは常にモールを使用する。
    SIPointRule lPointRule = new SIPointRule(SIConfig.SIMALL.getMallShopCode());
    lPointRule.reset(conn);
    
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append("SELECT DISTINCT ORDERCODE FROM ").append(SIConfig.SIVIEW_ORDER_LATEST_NAME);
    lSqlBuf.append(" WHERE ORDERCODE<>").append(SIDBUtil.SQL2Str(orderCode, " "));
    lSqlBuf.append("   AND CUSTCODE=").append(SIDBUtil.SQL2Str(estimate.getCustCode(), " "));
    lSqlBuf.append("   AND STATUS='1'");// 初めて購入
    
    String lPoint = "0";
    try {
      if (SIUtil.isNotNull(SIDBUtil.getFirstData(conn, lSqlBuf.toString()))) {
        lPoint = lPointRule.calcPointInUpdateOrder(conn, pointData, discount, estimate.getCustCode(), false, time.getFullDateTime());
      } else {
        lPoint = lPointRule.calcPointInUpdateOrder(conn, pointData, discount, estimate.getCustCode(), true, time.getFullDateTime());
      }
    } catch (SIDBAccessException e1) {
      e1.printStackTrace();
    }
    
    lRec = new SIInsertRec("PointManMTBL");
    lRec.add("CUSTCODE", estimate.getCustCode()); // 顧客コード
    lRec.add("MallShopCODE", "0"); // 管理コード
    lRec.add("IssueFLG", "0"); // 発行種別フラグ
    lRec.add("PointOfIssue", lPoint); // 発行／使用ポイント数
    lRec.add("EnableFLG", "2"); // 有効フラグ(仮発行)
    lRec.add("ORDERCODE", orderCode); // 受注番号
    lRec.add("ISSUEDATETIME", time.getFullDateTime());// 更新日時
    lRec.execute(conn);
    
    //RFM分析テーブルの登録
    String rfmAnalyseCode = SIDBUtil.getFirstData(conn, SIDBUtil.getNextVal("RFMANALYSETBL_RFMANALYSECD_SEQ"));
    lRec = new SIInsertRec("rfmAnalyseTbl");
    lRec.add("rfmAnalyseCode", rfmAnalyseCode);// 受注番号
    lRec.add("custCode", estimate.getCustCode());// 顧客コード
    lRec.add("sumOfPrice", totalPrice);// 合計金額
    lRec.add("amount", totalAmount);// 数量
    lRec.add("address1", estimate.getAddress1());// 都道府県
    lRec.add("InitDateTime", lSpcType);// 登録日
    lRec.add("orderCode", orderCode);// 受注番号
    lRec.execute(conn);
    
    return orderCode;
    
  }
  
  public boolean cmdtyCheck(Connection conn, String keepNum) throws SIDBAccessException {
    String cmdtyCnt = SIDBUtil.getFirstData(conn, "select count(*) from keepdetailtbl where keepnumber = '" + keepNum + "'");
    if (Integer.parseInt(cmdtyCnt) > 0) {
      return true;
    } else {
      return false;
    }
    
  }
  
  public void producePDFFile(HttpServletResponse response, Connection conn, UIRegUpdateEstimate estimateFull) throws IOException, DocumentException, SIDBAccessException {
    File lOrderTempDir = (File) this.getServletContext().getAttribute("javax.servlet.context.tempdir");
    String template = (String) this.getServletContext().getRealPath("/docs/Estimate.pdf");
    producePDFFile(response, conn, estimateFull, lOrderTempDir, template, false);
  }
  
  public static void producePDFFile(HttpServletResponse response, Connection conn, UIRegUpdateEstimate estimateFull, File lOrderTempDir, String template, boolean orderFlg)
      throws IOException, DocumentException, SIDBAccessException {
    File lOrderTempFile = new File("");
    NumberFormat format = NumberFormat.getNumberInstance();
    
    try {
      lOrderTempFile = File.createTempFile(SIPDFConf.SIPDF_PREFIX_ESTIMATE, ".pdf", lOrderTempDir);
    } catch (IOException e1) {
      e1.printStackTrace();
    }
    String lFileName = lOrderTempFile.getAbsolutePath();
    EstimatePdf estimatePdf = new EstimatePdf();
    estimatePdf.initialize(template, lOrderTempFile.getPath());
    estimatePdf.fontSelect("Goth");
    
    String lDate = new SIDateTime().getFullDate();
    
    SIEstimate estimate = estimateFull.getEstimate();
    SIEstimateDelivery estimateDelivery = estimateFull.getEstimateDelivery();
    
    Collection keepColl = new ArrayList();
    if (orderFlg) keepColl = UIRegNewKeep.getOrderList(conn, estimateFull.getEstimate().getKeepNumber());
    else keepColl = UIRegNewKeep.getEstimateList(conn, estimateFull.getEstimate().getKeepNumber(), estimate.getMemberLevelCode());
    int cnt = keepColl.size();
    
    Iterator ite = keepColl.iterator();
    int totalCmdty = 0;
    int totalDeliveryFee = 0;
    int totalDiscountDiff = 0;
    LinkedHashMap hash = estimateFull.getHash();
    ArrayList array = new ArrayList();
    
    while (ite.hasNext()) {
      SIKeepDetail keep = (SIKeepDetail) ite.next();
      totalCmdty = totalCmdty + (Integer.parseInt(keep.getPrice()) * Integer.parseInt(keep.getAmount()));
      // EDBTG003-00 nagayoshi mod start
//      if (keep.getDeliveryType().equals("1")) {
      if (keep.getDeliveryType().equals("1") && !SIConfig.SET_DETAIL_FLG_APPENDED.equals(keep.getSetDetailFlg())) {
      // EDBTG003-00 nagayoshi mod end
        array.add(keep.getIndividualCode());
      }
    }
    totalCmdty = totalCmdty - Integer.parseInt(estimate.getSumOfSetDiscount()); // EDBTG003-00 elecs-tani add
    Object[] obj = array.toArray();
    java.util.Arrays.sort(obj);
    // EDBTG003-00 nagayoshi add start
    boolean komonoFeeFlg = false;
    // EDBTG003-00 nagayoshi add end
    // 送料計算
    int totalDeliveryFee1 = 0;
    int totalDeliveryFee2 = 0;
    String lPrefectureCode = SIBGUtil.getPrefectureCode(conn,estimateDelivery.getPostCode1(),estimateDelivery.getPostCode2());
    if (SIUtil.isNull(lPrefectureCode)) lPrefectureCode="13";
    ite = keepColl.iterator();
    
    // EDBTG003-00 cmt-okuda add start
    // セット値引き金額出力のための変数
    String code = ""; // キープ番号もしくは受注番号が入る
    String setIndividualCode = "";
    String setCode = "";
    cnt = 0;
    // EDBTG003-00 cmt-okuda add end
    
    while (ite.hasNext()) {
      SIKeepDetail keepPageTotal = (SIKeepDetail) ite.next();
      // EDBTG003-00 nagayoshi mod start
//      String deliveryFee = (String) hash.get("deliveryFee_" + keepPageTotal.getCmdtyCode() + "_" + keepPageTotal.getIndividualCode());
      String deliveryFee = (String) hash.get("deliveryFee_" + keepPageTotal.getCmdtyCode() + "_" + keepPageTotal.getIndividualCode()+"_"+keepPageTotal.getSetCode()+"_"+keepPageTotal.getSetDetailCode());
      // EDBTG003-00 nagayoshi mod end
      if (deliveryFee == null) {
        if (keepPageTotal.getDeliveryType().equals("0")) {
          deliveryFee = SIBGUtil.getDeliveryFee(conn, lPrefectureCode, keepPageTotal.getSize(), keepPageTotal.getDeliveryType());
        } else {
          // EDBTG003-00 nagayoshi mod start
//          if (keepPageTotal.getIndividualCode().equals(obj[0].toString())) {
          if (obj.length > 0 && keepPageTotal.getIndividualCode().equals(obj[0].toString()) && !komonoFeeFlg) {
          // EDBTG003-00 nagayoshi mod end
            deliveryFee = SIBGUtil.getDeliveryFee(conn, lPrefectureCode, keepPageTotal.getSize(), keepPageTotal.getDeliveryType());
            // EDBTG003-00 nagayoshi add start
            komonoFeeFlg = true;
            // EDBTG003-00 nagayoshi add end
          } else {
            deliveryFee = "0";
          }
        }
      }
      if (keepPageTotal.getDeliveryType().equals("0")) {
        totalDeliveryFee1 = totalDeliveryFee1 + Integer.parseInt(deliveryFee) * Integer.parseInt(keepPageTotal.getAmount());
      } else if (keepPageTotal.getDeliveryType().equals("1")) {
        totalDeliveryFee2 = totalDeliveryFee2 + Integer.parseInt(deliveryFee);
      }
      //cmt-okuda add start セット商品による件数数えなおし
      cnt ++; //通常で＋１
      if (!keepPageTotal.getSetDetailFlg().equals(SIConfig.SET_DETAIL_FLG_NORMAL) && keepPageTotal.getSetDetailCode().equals("1")) { // 通常品でないかつセット明細番号が1の場合
        cnt ++; //セットのタイトルで＋１
        
        if (orderFlg) {
          code = keepPageTotal.getOrderCode();
        } else {
          code = keepPageTotal.getKeepNumber();
        }
        setIndividualCode = keepPageTotal.getSetIndividualCode();
        setCode = keepPageTotal.getSetCode();
        
        // キープセット品テーブルor受注セット品テーブルからセット値引きと数量を取得
        StringBuffer setDiscountSql = new StringBuffer();
        if (orderFlg) {
          setDiscountSql.append("SELECT a.setdiscount,a.setamount ");
          setDiscountSql.append("FROM ordersetcmdtytbl a,ordertbl b ");
          setDiscountSql.append("WHERE a.orderCode = ").append(SIDBUtil.SQL2Str(code, " "));
          setDiscountSql.append("AND a.orderCode = b.ordercode ");
          setDiscountSql.append("AND a.orderbranchcode = b.orderbranchcode ");
          setDiscountSql.append("AND b.enabledflg = 1 ");
          setDiscountSql.append("AND a.setindividualcode = ").append(SIDBUtil.SQL2Str(setIndividualCode, " "));
          setDiscountSql.append("AND a.setcode = ").append(SIDBUtil.SQL2Str(setCode));
        } else {
          setDiscountSql.append("SELECT a.setdiscount,a.setamount ");
          setDiscountSql.append("FROM keepsetcmdtytbl a ");
          setDiscountSql.append("WHERE a.keepNumber = ").append(SIDBUtil.SQL2Str(code, " "));
          setDiscountSql.append("AND a.setindividualcode = ").append(SIDBUtil.SQL2Str(setIndividualCode, " "));
          setDiscountSql.append("AND a.setcode = ").append(SIDBUtil.SQL2Str(setCode));
          
          if (SIDBUtil.hasData(conn, "SELECT * FROM estimatesetcmdtytbl s,estimatetbl e WHERE s.estimatecode=e.estimatecode AND e.keepnumber="+code)) {
            setDiscountSql = new StringBuffer();
            setDiscountSql.append("SELECT s.setdiscount, s.setamount ");
            setDiscountSql.append("FROM estimatesetcmdtytbl s,estimatetbl e ");
            setDiscountSql.append("WHERE s.estimatecode=e.estimatecode ");
            setDiscountSql.append("AND e.keepNumber = ").append(SIDBUtil.SQL2Str(code," "));
            setDiscountSql.append("AND s.setindividualcode = ").append(SIDBUtil.SQL2Str(setIndividualCode," "));
            setDiscountSql.append("AND s.setcode = ").append(SIDBUtil.SQL2Str(setCode));
          }
        }
        HashMap lResMap = new HashMap();
        lResMap = SIDBUtil.getOneRowData(conn, setDiscountSql.toString());
        
        int setdiscount = 0;
        if (SIUtil.isNotNull((String) lResMap.get("setdiscount"))) {
          setdiscount = Integer.parseInt((String) lResMap.get("setdiscount"));
          if (setdiscount != 0) { // セット値引きが0でなければ出力
            cnt++; //セット値引きで＋１
          }
        }
      }
      //cmt-okuda add end
    }
    
    //cmt-okuda add start
    String strdetail[][] = new String[6][cnt];
    setIndividualCode = "";
    setCode = "";
    int rowCnt = 0;
    
    ite = keepColl.iterator();
    
    while (ite.hasNext()) {
      SIKeepDetail keep = (SIKeepDetail) ite.next();
      
      log.debug(format.format(rowCnt));
      
      strdetail[0][rowCnt] = "";
      strdetail[1][rowCnt] = "";
      strdetail[2][rowCnt] = "";
      strdetail[3][rowCnt] = "";
      strdetail[4][rowCnt] = "";
      strdetail[5][rowCnt] = "";
      
      if (SIUtil.isNotNull(setIndividualCode) && !setCode.equals(keep.getSetCode())) {
        // キープセット品テーブルor受注セット品テーブルからセット値引きと数量を取得
        StringBuffer setDiscountSql = new StringBuffer();
        if (orderFlg) {
          setDiscountSql.append("SELECT a.setdiscount,a.setamount ");
          setDiscountSql.append("FROM ordersetcmdtytbl a,ordertbl b ");
          setDiscountSql.append("WHERE a.orderCode = ").append(SIDBUtil.SQL2Str(code, " "));
          setDiscountSql.append("AND a.orderCode = b.ordercode ");
          setDiscountSql.append("AND a.orderbranchcode = b.orderbranchcode ");
          setDiscountSql.append("AND b.enabledflg = 1 ");
          setDiscountSql.append("AND a.setindividualcode = ").append(SIDBUtil.SQL2Str(setIndividualCode, " "));
          setDiscountSql.append("AND a.setcode = ").append(SIDBUtil.SQL2Str(setCode));
        } else {
          setDiscountSql.append("SELECT a.setdiscount,a.setamount ");
          setDiscountSql.append("FROM keepsetcmdtytbl a WHERE a.keepNumber = ").append(SIDBUtil.SQL2Str(code, " "));
          setDiscountSql.append("AND a.setindividualcode = ").append(SIDBUtil.SQL2Str(setIndividualCode, " "));
          setDiscountSql.append("AND a.setcode = ").append(SIDBUtil.SQL2Str(setCode));
          
          if (SIDBUtil.hasData(conn, "SELECT * FROM estimatesetcmdtytbl s,estimatetbl e WHERE s.estimatecode=e.estimatecode AND e.keepnumber="+code)) {
            setDiscountSql = new StringBuffer();
            setDiscountSql.append("SELECT s.setdiscount, s.setamount ");
            setDiscountSql.append("FROM estimatesetcmdtytbl s,estimatetbl e ");
            setDiscountSql.append("WHERE s.estimatecode=e.estimatecode ");
            setDiscountSql.append("AND e.keepNumber = ").append(SIDBUtil.SQL2Str(code," "));
            setDiscountSql.append("AND s.setindividualcode = ").append(SIDBUtil.SQL2Str(setIndividualCode," "));
            setDiscountSql.append("AND s.setcode = ").append(SIDBUtil.SQL2Str(setCode));
          }
        }
        HashMap lResMap = new HashMap();
        lResMap = SIDBUtil.getOneRowData(conn, setDiscountSql.toString());
        
        int setdiscount = 0;
        if (SIUtil.isNotNull((String) lResMap.get("setdiscount"))) {
          setdiscount = Integer.parseInt((String) lResMap.get("setdiscount"));
          setdiscount = -setdiscount;
          
          if (setdiscount != 0) { // セット値引きが0でなければ出力
            strdetail[0][rowCnt] = "";
            strdetail[1][rowCnt] = SIConfig.SET_DISCOUNT_NAME_PDF;
            strdetail[2][rowCnt] = "";
            strdetail[3][rowCnt] = format.format(setdiscount);
            strdetail[4][rowCnt] = format.format(Integer.parseInt((String) lResMap.get("setamount")));
            strdetail[5][rowCnt] = format.format(setdiscount * Integer.parseInt((String) lResMap.get("setamount")));
            rowCnt++;
          }
        }
      }
      // 添付品の場合テキストを設定
      String appendTxt = "";
      if (keep.getSetDetailFlg().equals(SIConfig.SET_DETAIL_FLG_APPENDED)) {
        appendTxt = SIConfig.SET_APPEND_NAME_PDF;
      }
      if (!keep.getSetDetailFlg().equals(SIConfig.SET_DETAIL_FLG_NORMAL) && keep.getSetDetailCode().equals("1")) { // 通常品でないかつセット明細番号が1の場合
        
        // まずセット品(親)を出力
        StringBuffer setNameSql = new StringBuffer();
        setNameSql.append("SELECT a.cmdtyname ");
        if (orderFlg) {
          setNameSql.append("FROM ordersetcmdtytbl a,ordertbl b ");
          setNameSql.append("WHERE a.orderCode = ").append(SIDBUtil.SQL2Str(code, " "));
          setNameSql.append("AND a.orderCode = b.ordercode ");
          setNameSql.append("AND a.orderbranchcode = b.orderbranchcode ");
          setNameSql.append("AND b.enabledflg = 1 ");
        } else {
          setNameSql.append("FROM keepsetcmdtytbl a ");
          setNameSql.append("WHERE a.keepNumber = ").append(SIDBUtil.SQL2Str(code, " "));
        }
        setNameSql.append("AND a.setCode = ").append(SIDBUtil.SQL2Str(keep.getSetCode(), " "));
        setNameSql.append("AND a.setindividualcode = ").append(SIDBUtil.SQL2Str(keep.getSetIndividualCode()));
        String setCmdtyName = SIDBUtil.getFirstData(conn, setNameSql.toString());
        
        strdetail[0][rowCnt] = keep.getSetIndividualCode();
        strdetail[1][rowCnt] = setCmdtyName;
        strdetail[2][rowCnt] = "";
        strdetail[3][rowCnt] = "";
        strdetail[4][rowCnt] = "";
        strdetail[5][rowCnt] = "";
        
        rowCnt++;
        
        strdetail[0][rowCnt] = "";
        String cmdtyName = "(" + keep.getIndividualCode() + ")" + appendTxt + keep.getCmdtyName();
        strdetail[1][rowCnt] = SIStringUtil.limitLength(cmdtyName, 27, "･･･");
      } else if (!keep.getSetDetailFlg().equals(SIConfig.SET_DETAIL_FLG_NORMAL) && !keep.getSetDetailCode().equals("1")) { // 通常品でないかつセット明細番号が1でない場合
        strdetail[0][rowCnt] = "";
        String cmdtyName = "(" + keep.getIndividualCode() + ")" + appendTxt + keep.getCmdtyName();
        strdetail[1][rowCnt] = SIStringUtil.limitLength(cmdtyName, 27, "･･･");
      } else { // 通常品の場合
        strdetail[0][rowCnt] = keep.getIndividualCode();
        strdetail[1][rowCnt] = SIStringUtil.limitLength(keep.getCmdtyName(),27,"･･･");
      }
      String colorSql = "SELECT colorname FROM colortbl co,cmdtymtbl cm WHERE cm.colorcode=co.colorcode AND cm.cmdtycode=" + SIDBUtil.SQL2Str(keep.getCmdtyCode());
      
      strdetail[2][rowCnt] = SIStringUtil.limitLength(SIDBUtil.getFirstData(conn, colorSql),10,"");
      strdetail[3][rowCnt] = format.format(Integer.parseInt(keep.getOrgPrice()));
      strdetail[4][rowCnt] = format.format(Integer.parseInt(keep.getAmount()));
      strdetail[5][rowCnt] = format.format(Integer.parseInt(keep.getOrgPrice()) * Integer.parseInt(keep.getAmount()));
      
      totalDiscountDiff = totalDiscountDiff + (Integer.parseInt(SIUtil.sub(keep.getOrgPrice(), keep.getPrice())) * Integer.parseInt(keep.getAmount()));
      setIndividualCode = keep.getSetIndividualCode();
      setCode = keep.getSetCode();
      rowCnt++;
    }
    
    if (SIUtil.isNotNull(setIndividualCode)) {
      // キープセット品テーブルor受注セット品テーブルからセット値引きと数量を取得
      StringBuffer setDiscountSql = new StringBuffer();
      if (orderFlg) {
        setDiscountSql.append("SELECT a.setdiscount, a.setamount ");
        setDiscountSql.append("FROM ordersetcmdtytbl a,ordertbl b ");
        setDiscountSql.append("WHERE a.orderCode = ").append(SIDBUtil.SQL2Str(code, " "));
        setDiscountSql.append("AND a.orderCode = b.ordercode ");
        setDiscountSql.append("AND a.orderbranchcode = b.orderbranchcode ");
        setDiscountSql.append("AND b.enabledflg = 1 ");
        setDiscountSql.append("AND a.setindividualcode = ").append(SIDBUtil.SQL2Str(setIndividualCode, " "));
        setDiscountSql.append("AND a.setcode = ").append(SIDBUtil.SQL2Str(setCode));
      } else {
        setDiscountSql.append("SELECT a.setdiscount, a.setamount ");
        setDiscountSql.append("FROM keepsetcmdtytbl a ");
        setDiscountSql.append("WHERE a.keepNumber = ").append(SIDBUtil.SQL2Str(code, " "));
        setDiscountSql.append("AND a.setindividualcode = ").append(SIDBUtil.SQL2Str(setIndividualCode, " "));
        setDiscountSql.append("AND a.setcode = ").append(SIDBUtil.SQL2Str(setCode));
        
        if (SIDBUtil.hasData(conn, "SELECT * FROM estimatesetcmdtytbl s,estimatetbl e WHERE s.estimatecode=e.estimatecode AND e.keepnumber="+code)) {
          setDiscountSql = new StringBuffer();
          setDiscountSql.append("SELECT s.setdiscount, s.setamount ");
          setDiscountSql.append("FROM estimatesetcmdtytbl s,estimatetbl e ");
          setDiscountSql.append("WHERE s.estimatecode=e.estimatecode ");
          setDiscountSql.append("AND e.keepNumber = ").append(SIDBUtil.SQL2Str(code," "));
          setDiscountSql.append("AND s.setindividualcode = ").append(SIDBUtil.SQL2Str(setIndividualCode," "));
          setDiscountSql.append("AND s.setcode = ").append(SIDBUtil.SQL2Str(setCode));
        }
      }
      HashMap lResMap = new HashMap();
      lResMap = SIDBUtil.getOneRowData(conn, setDiscountSql.toString());
      
      int setdiscount = 0;
      if (SIUtil.isNotNull((String) lResMap.get("setdiscount"))) {
        setdiscount = Integer.parseInt((String) lResMap.get("setdiscount"));
        setdiscount = -setdiscount;
        
        if (setdiscount != 0) { // セット値引きが0でなければ出力
          strdetail[0][rowCnt] = "";
          strdetail[1][rowCnt] = SIConfig.SET_DISCOUNT_NAME_PDF;
          strdetail[2][rowCnt] = "";
          strdetail[3][rowCnt] = format.format(setdiscount);
          strdetail[4][rowCnt] = format.format(Integer.parseInt((String) lResMap.get("setamount")));
          strdetail[5][rowCnt] = format.format(setdiscount * Integer.parseInt((String) lResMap.get("setamount")));
          rowCnt++;
        }
      }
    }
    //cmt-okuda add end
    
    totalDeliveryFee = totalDeliveryFee1 + totalDeliveryFee2;
    
    if (orderFlg) {
      String deliveryfeeSql = "SELECT totalofdeliveryfee FROM ordersumvw WHERE ordercode="+SIDBUtil.SQL2Str(estimateFull.getEstimate().getKeepNumber());
      totalDeliveryFee = Integer.parseInt(SIDBUtil.getFirstData(conn, deliveryfeeSql));
    }
    
    // 送料調整額計算
    if (SIUtil.isNotNull(estimate.getDiscountDeliveryFee())) {
      totalDeliveryFee = totalDeliveryFee + Integer.parseInt(estimate.getDiscountDeliveryFee());
    }
    
    // 使用ポイント額
    int sumByPoint = 0;
    if (SIUtil.isNotNull(estimate.getSumByPoint())) {
      sumByPoint = Integer.parseInt(estimate.getSumByPoint());
    }
    // 手数料
    int fee = 0;
    if (SIUtil.isNotNull(estimate.getFeeIncTax())) {
      fee = Integer.parseInt(estimate.getFeeIncTax());
    }
    // 手数料調整額計算
    if (SIUtil.isNotNull(estimate.getDiscountFee())) {
      fee = fee + Integer.parseInt(estimate.getDiscountFee());
    }
    
    int totalPrice = totalCmdty + totalDeliveryFee + fee - Integer.parseInt(estimate.getSumOfDiscount()) - sumByPoint;
    
    ite = keepColl.iterator();
    int arrcnt = 0;  //  EDBTG003-00 cmt-okuda add
    for (int i = 0; i < (cnt / 20) + 1; i++) {
      if (i >= 1) estimatePdf.addPage();
      
      estimatePdf.writeNo(estimate.getEstimataCode());
      if (!orderFlg) {
        estimatePdf.writeDate(lDate);
      } else {
        estimatePdf.writeDate(estimate.getInitDateTime());
      }
      estimatePdf.writeAddress1(estimate.getAddress1() + estimate.getAddress2());
      estimatePdf.writeAddress2(estimate.getAddress3());
      estimatePdf.writeTel(estimate.getStoreTel());
      estimatePdf.writeDeliveryAddress1(estimateDelivery.getAddress1() + estimateDelivery.getAddress2());
      estimatePdf.writeDeliveryAddress2(estimateDelivery.getAddress3());
      estimatePdf.writeDeliveryTel(estimateDelivery.getTel());
      estimatePdf.writeCompanyName(estimate.getCompanyName());
      estimatePdf.writeSalonName(estimateDelivery.getCompanyName());
      estimatePdf.writeCustName(estimate.getCustName());
      estimatePdf.writeTotalPrice1("￥" + format.format(totalPrice));
      // EDBTG003-00 cmt-okuda mod start
//    int rowCnt = 0;
    rowCnt = 0;
    // EDBTG003-00 cmt-okuda mod end

      // EDBTG003-00 cmt-okuda mod start
      // EDBTG003-00 elecs-tani add start
      // セット値引き金額出力のための変数
/*
      String code = ""; // キープ番号もしくは受注番号が入る
      String setIndividualCode = "";
      String setCode = "";
      */
      code = ""; // キープ番号もしくは受注番号が入る
      setIndividualCode = "";
      setCode = "";
      // EDBTG003-00 elecs-tani add end
      // EDBTG003-00 cmt-okuda mod end

      // EDBTG003-00 cmt-okuda mod start
//    while (ite.hasNext()) {
    while (cnt > arrcnt) {
        // EDBTG003-00 cmt-okuda mod end

      rowCnt++;
      /*       // EDBTG003-00 cmt-okuda del start

        SIKeepDetail keep = (SIKeepDetail) ite.next();

        // EDBTG003-00 elecs-tani add start
        if (orderFlg) {
          code = keep.getOrderCode();
        } else {
          code = keep.getKeepNumber();
        }
        // EDBTG003-00 elecs-tani add end

        // EDBTG003-00 elecs-tani mod start
//        if (SIUtil.isNotNull(setIndividualCode) && !setIndividualCode.equals(keep.getSetIndividualCode())) {
        if (SIUtil.isNotNull(setIndividualCode) && !setCode.equals(keep.getSetCode())) {
          // キープセット品テーブルor受注セット品テーブルからセット値引きと数量を取得
          StringBuffer setDiscountSql = new StringBuffer();
          setDiscountSql.append("select a.setdiscount,a.setamount ");
          if (orderFlg) {
            setDiscountSql.append("FROM ordersetcmdtytbl a,ordertbl b ");
            setDiscountSql.append("WHERE a.orderCode = ").append(SIDBUtil.SQL2Str(code, " "));
            setDiscountSql.append("AND a.orderCode = b.ordercode ");
            setDiscountSql.append("AND a.orderbranchcode = b.orderbranchcode ");
            setDiscountSql.append("AND b.enabledflg = 1 ");
          } else {
            setDiscountSql.append("FROM keepsetcmdtytbl a where keepNumber = ").append(SIDBUtil.SQL2Str(code));
          }
          setDiscountSql.append(" AND a.setindividualcode = ").append(SIDBUtil.SQL2Str(setIndividualCode));
          setDiscountSql.append(" AND a.setcode = ").append(SIDBUtil.SQL2Str(setCode));
          HashMap lResMap = new HashMap();
          lResMap = SIDBUtil.getOneRowData(conn, setDiscountSql.toString());

          int setdiscount = 0;
          if (SIUtil.isNotNull((String) lResMap.get("setdiscount"))) {
            setdiscount = Integer.parseInt((String) lResMap.get("setdiscount"));
            setdiscount = -setdiscount;

              if (setdiscount != 0) { // セット値引きが0でなければ出力
                  estimatePdf.writeCmdtyCode("", rowCnt);
                  estimatePdf.writeCmdtyName(SIConfig.SET_DISCOUNT_NAME_PDF, rowCnt);
                    estimatePdf.writeCmdtyPrice(format.format(setdiscount), rowCnt);
                    estimatePdf.writeAmount(format.format(Integer.parseInt((String) lResMap.get("setamount"))), rowCnt);
                    estimatePdf.writePrice2(format.format(setdiscount * Integer.parseInt((String) lResMap.get("setamount"))), rowCnt);

                    rowCnt++;
              }
          }
        }

//        estimatePdf.writeCmdtyCode(keep.getIndividualCode(), rowCnt);
//        estimatePdf.writeCmdtyName(SIStringUtil.limitLength(keep.getCmdtyName(),27,"･･･"), rowCnt);

        // 添付品の場合テキストを設定
        String appendTxt = "";
        if (keep.getSetDetailFlg().equals(SIConfig.SET_DETAIL_FLG_APPENDED)) {
          appendTxt = SIConfig.SET_APPEND_NAME_PDF;
        }

        if (!keep.getSetDetailFlg().equals(SIConfig.SET_DETAIL_FLG_NORMAL) && keep.getSetDetailCode().equals("1")) { // 通常品でないかつセット明細番号が1の場合
          // まずセット品(親)を出力
          StringBuffer setNameSql = new StringBuffer();
          setNameSql.append("select a.cmdtyname ");
          if (orderFlg) {
            setNameSql.append("FROM ordersetcmdtytbl a,ordertbl b ");
            setNameSql.append("WHERE a.orderCode = ").append(SIDBUtil.SQL2Str(code, " "));
            setNameSql.append("AND a.orderCode = b.ordercode ");
            setNameSql.append("AND a.orderbranchcode = b.orderbranchcode ");
            setNameSql.append("AND b.enabledflg = 1 ");
          } else {
            setNameSql.append("FROM keepsetcmdtytbl a where keepNumber = ").append(SIDBUtil.SQL2Str(code));
          }
          setNameSql.append(" AND a.setindividualcode = ").append(SIDBUtil.SQL2Str(keep.getSetIndividualCode()));
          setNameSql.append(" AND a.setcode = ").append(SIDBUtil.SQL2Str(keep.getSetCode()));
          
          String setCmdtyName = SIDBUtil.getFirstData(conn, setNameSql.toString());

          estimatePdf.writeCmdtyCode(keep.getSetIndividualCode(), rowCnt);
          estimatePdf.writeCmdtyName(setCmdtyName, rowCnt);

          rowCnt++;

          estimatePdf.writeCmdtyCode("", rowCnt);
          String cmdtyName = "(" + keep.getIndividualCode() + ")" + appendTxt + keep.getCmdtyName();
          estimatePdf.writeCmdtyName(SIStringUtil.limitLength(cmdtyName, 27, "･･･"), rowCnt);

        } else if (!keep.getSetDetailFlg().equals(SIConfig.SET_DETAIL_FLG_NORMAL) && !keep.getSetDetailCode().equals("1")) { // 通常品でないかつセット明細番号が1でない場合
          estimatePdf.writeCmdtyCode("", rowCnt);
          String cmdtyName = "(" + keep.getIndividualCode() + ")" + appendTxt + keep.getCmdtyName();
          estimatePdf.writeCmdtyName(SIStringUtil.limitLength(cmdtyName, 27, "･･･"), rowCnt);

        } else { // 通常品の場合
            estimatePdf.writeCmdtyCode(keep.getIndividualCode(), rowCnt);
            estimatePdf.writeCmdtyName(SIStringUtil.limitLength(keep.getCmdtyName(),27,"･･･"), rowCnt);
        }
        // EDBTG003-00 elecs-tani mod end

        String colorSql = "SELECT colorname FROM colortbl co,cmdtymtbl cm WHERE cm.colorcode=co.colorcode AND cm.cmdtycode=" + SIDBUtil.SQL2Str(keep.getCmdtyCode());
        estimatePdf.writeCmdtyColor(SIStringUtil.limitLength(SIDBUtil.getFirstData(conn, colorSql),10,""), rowCnt);
        estimatePdf.writeCmdtyPrice(format.format(Integer.parseInt(keep.getPrice())), rowCnt);
        estimatePdf.writeAmount(format.format(Integer.parseInt(keep.getAmount())), rowCnt);
        estimatePdf.writePrice2(format.format(Integer.parseInt(keep.getPrice()) * Integer.parseInt(keep.getAmount())), rowCnt);

        // EDBTG003-00 elecs-tani add start
//        if (orderFlg) {
//          code = keep.getOrderCode();
//        } else {
//            code = keep.getKeepNumber();
//        }
        setIndividualCode = keep.getSetIndividualCode();
        setCode = keep.getSetCode();
        // EDBTG003-00 elecs-tani add end
      // EDBTG003-00 cmt-okuda del end   */


          // EDBTG003-00 cmt-okuda add start
          estimatePdf.writeCmdtyCode(strdetail[0][arrcnt], rowCnt);
          estimatePdf.writeCmdtyName(strdetail[1][arrcnt], rowCnt);
          estimatePdf.writeCmdtyColor(strdetail[2][arrcnt], rowCnt);
          estimatePdf.writeCmdtyPrice(strdetail[3][arrcnt], rowCnt);
          estimatePdf.writeAmount(strdetail[4][arrcnt], rowCnt);
          estimatePdf.writePrice2(strdetail[5][arrcnt], rowCnt);
          arrcnt++;
          // EDBTG003-00 cmt-okuda add end
          
        if (rowCnt == 20) break;
      }
    /*        // EDBTG003-00 cmt-okuda del start
    
      // EDBTG003-00 elecs-tani add start
      if (SIUtil.isNotNull(setIndividualCode)) {
        rowCnt++;
      }
      // EDBTG003-00 elecs-tani add end
      
      estimatePdf.writePage(Integer.toString(i + 1), Integer.toString((cnt / 20) + 1));
      
      // EDBTG003-00 elecs-tani add start
      // 最終行がセット商品だった場合用
      if (SIUtil.isNotNull(setIndividualCode)) {
        // キープセット品テーブルor受注セット品テーブルからセット値引きと数量を取得
        StringBuffer setDiscountSql = new StringBuffer();
        setDiscountSql.append("select a.setdiscount,a.setamount ");
        if (orderFlg) {
          setDiscountSql.append("FROM ordersetcmdtytbl a,ordertbl b ");
          setDiscountSql.append("WHERE a.orderCode = ").append(SIDBUtil.SQL2Str(code, " "));
          setDiscountSql.append("AND a.orderCode = b.ordercode ");
          setDiscountSql.append("AND a.orderbranchcode = b.orderbranchcode ");
          setDiscountSql.append("AND b.enabledflg = 1 ");
        } else {
          setDiscountSql.append("FROM keepsetcmdtytbl a where keepNumber = ").append(SIDBUtil.SQL2Str(code));
        }
        setDiscountSql.append(" AND a.setindividualcode = ").append(SIDBUtil.SQL2Str(setIndividualCode));
        setDiscountSql.append(" AND a.setcode = ").append(SIDBUtil.SQL2Str(setCode));
        HashMap lResMap = new HashMap();
        lResMap = SIDBUtil.getOneRowData(conn, setDiscountSql.toString());
        
        int setdiscount = 0;
        if (SIUtil.isNotNull((String) lResMap.get("setdiscount"))) {
          setdiscount = Integer.parseInt((String) lResMap.get("setdiscount"));
          setdiscount = -setdiscount;
          
          if (setdiscount != 0) { // セット値引きが0でなければ出力
            estimatePdf.writeCmdtyCode("", rowCnt);
            estimatePdf.writeCmdtyName(SIConfig.SET_DISCOUNT_NAME_PDF, rowCnt);
            estimatePdf.writeCmdtyPrice(format.format(setdiscount), rowCnt);
            estimatePdf.writeAmount(format.format(Integer.parseInt((String) lResMap.get("setamount"))), rowCnt);
            estimatePdf.writePrice2(format.format(setdiscount * Integer.parseInt((String) lResMap.get("setamount"))), rowCnt);
          }
        }
      }
      // EDBTG003-00 elecs-tani add end
      // EDBTG003-00 cmt-okuda del end   */
    }
    estimatePdf.writeCmdtyTotalPrice(format.format(totalCmdty+totalDiscountDiff));
    estimatePdf.writeDeliveryPrice(format.format(totalDeliveryFee));
    
    // 値引き金額
    if (Integer.parseInt(estimate.getSumOfDiscount()) + totalDiscountDiff == 0) {
      estimatePdf.writeDiscount("");
    } else {
      estimatePdf.writeDiscountTitle("値引金額");
      if (SIUtil.isDigitNegative(estimate.getSumOfDiscount())) {
        estimatePdf.writeDiscount(format.format(totalDiscountDiff + (Integer.parseInt(estimate.getSumOfDiscount()) - 2 * Integer.parseInt(estimate.getSumOfDiscount()))));
      } else {
        estimatePdf.writeDiscount("-" + format.format(totalDiscountDiff + Integer.parseInt(estimate.getSumOfDiscount())));
      }
    }
    // ポイント使用額
    if (sumByPoint == 0) {
      estimatePdf.writeSumByPoint("");
    } else {
      estimatePdf.writeSumByPointTitle("ポイント使用額");
      estimatePdf.writeSumByPoint(format.format(sumByPoint));
    }
    // 手数料
    if (fee == 0) {
      estimatePdf.writeFee("");
    } else {
      estimatePdf.writeFeeTitle("手数料");
      estimatePdf.writeFee(format.format(fee));
    }
    estimatePdf.writeTotal(format.format(totalPrice));
    estimatePdf.close();
    SIPDFUtil lPdfUtil = new SIPDFUtil();
    String lOutputFileName = SIPDFConf.SIPDF_PREFIX_ESTIMATE + (new SIDateTime().getDateTimeString()) + "." + SIPDFConf.SIPDF_EXTENSION_NAME;
    lPdfUtil.execute(response, lFileName, lOutputFileName);
    
    // 臨時ファイルの削除
    lOrderTempFile.delete();
  }
  
  /**
   * @param connection
   * @param listCond
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public void insertPointTableData(Connection connection, SIEstimate estimate, String orderCode, HttpServletRequest request) throws SIDuplicateKeyException, SIDBAccessException {
    SILogin manLogin = SIHTMLUtil.getLogin(request);
    String now = (new SIDateTime()).getFullDateTime();
    String point = estimate.getSumByPoint();
    String custCode = estimate.getCustCode();
    
    if (SIUtil.isNotNull(point)) {
      SIInsertRec lInsertRec = new SIInsertRec();
      lInsertRec.setTableName("pointmanMtbl");
      String rfmAnalyseCode = SIDBUtil.getFirstData(connection, SIDBUtil.getNextVal("POINTMANMTBL_POINTMANCD_SEQ"));
      lInsertRec.add("pointmancode", rfmAnalyseCode);
      lInsertRec.add("custcode", custCode);
      lInsertRec.add("mallshopcode", manLogin.getMallShopCode());
      lInsertRec.add("Issueflg", "0");
      if (!point.equals("0")) lInsertRec.add("pointofissue", "-" + point);
      else lInsertRec.add("pointofissue", "0");
      lInsertRec.add("enableflg", "1");
      if (!point.equals("0")) lInsertRec.add("ordercode", orderCode);
      else lInsertRec.add("ordercode", "");
      lInsertRec.add("reviewcode", "");
      if (SIDBMultiConf.SIDB_CURRENT_INX == SIDBMultiConf.SIDB_POSTGRESQL_INX) {
        lInsertRec.add("issuedatetime", now);
      } else {
        SIDateTimeType lTimeType = new SIDateTimeType();
        lTimeType = new SIDateTimeType(now);
        lInsertRec.add("issuedatetime", lTimeType);
      }
      lInsertRec.execute(connection);
    }
  }
}