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

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

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.SICartCmdty;
// import jp.co.sint.basic.SICmdty;
// import jp.co.sint.basic.SICmdtyStndrd;
// import jp.co.sint.basic.SICust;
import jp.co.sint.basic.SILogin;
import jp.co.sint.basic.SIMallShop;
import jp.co.sint.basic.SINameValue;
import jp.co.sint.basic.SIOrder;
import jp.co.sint.basic.SIPDFUtil;
import jp.co.sint.basic.SIPayMethod;
import jp.co.sint.basic.SIPayeeInfo;
// import jp.co.sint.basic.SIPayment;
import jp.co.sint.basic.SIPointMan;
import jp.co.sint.basic.SIMaker;
import jp.co.sint.basic.SITax;
import jp.co.sint.beans.mallmgr.UIOrderListCond;
import jp.co.sint.beans.mallmgr.UIPurchaseHistory;
import jp.co.sint.beans.mallmgr.UIRegOrder;
import jp.co.sint.beans.mallmgr.UIRegOrderDelivery;
import jp.co.sint.beans.mallmgr.UIRegOrderDetail;
import jp.co.sint.beans.mallmgr.UIRegUpdateEstimate;
import jp.co.sint.bscvsmdk.SIBSCVSMDKUtil;// 7.2.0 ST0300
import jp.co.sint.bscvsmdk.SIBSCVSMDKConfig;// 7.4.0 ST2057
import jp.co.sint.config.SICSVConf;
import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIDBMultiConf;
import jp.co.sint.config.SIExcelConf;
import jp.co.sint.config.SIFlagConf;
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.SIDateType;
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.database.SISpcType;
import jp.co.sint.database.SITableCondition;// 7.2.0 ST1104 追加
import jp.co.sint.docs.SIExcelRWrite;
import jp.co.sint.mail.SISendMail;
//import jp.co.sint.mdk.SIMDKAction;
import jp.co.sint.servlet.SIServlet;
import jp.co.sint.servlet.front.SIOrderHistorySrv;
import jp.co.sint.tools.SIBGUtil;
import jp.co.sint.tools.SICSVWrite;
import jp.co.sint.tools.SICheckDataConf;
import jp.co.sint.tools.SICheckValid;
import jp.co.sint.tools.SICustomError;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIDateTime;
import jp.co.sint.tools.SIErrorException;
import jp.co.sint.tools.SIErrorFactory;
import jp.co.sint.tools.SIFatalException;
import jp.co.sint.tools.SIFlagUtil;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIMismatchException;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;
import jp.co.sint.tools.SIURLParameter;// 7.1.1 ST0236 追加

import com.lowagie.text.BadElementException;
import com.lowagie.text.Cell;
import com.lowagie.text.Document;
import com.lowagie.text.DocumentException;
import com.lowagie.text.Element;
import com.lowagie.text.HeaderFooter;
import com.lowagie.text.Paragraph;
import com.lowagie.text.Phrase;
import com.lowagie.text.Rectangle;
import com.lowagie.text.Table;
import com.lowagie.text.pdf.PdfWriter;

/**
 * @version $Id: SIRegOrderSrv.java,v 1.0 2003/09/19 Exp $
 * @author 受注．送金関連を処理します。 <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>
 * jwchen 2003/09/19 12:20:42 Original
 */
public class SIRegOrderSrv extends SIServlet {
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);
  
  /**
   * <b>doUpdate</b> HTTP リクエストの処理
   * 
   * @param request リクエスト
   * @param response
   * @return なし
   * @throws ServletException
   * @throws IOException
   */
  public void doUpdate(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    SILogin manLogin = SIHTMLUtil.getLogin(request);
    SIRegMngAccesslogSrv siRegMngAccesslogSrv = new SIRegMngAccesslogSrv();// 7.3.0 ST2023 追加
    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);// 7.1.1 ST0236 追加
    
    try {
      // 7.1.1 ST0236 修正 ここから
      String actionName = this.getActionName(urlParam);// 画面からのアクション
      String editMode = this.getEditMode(urlParam); // DBへの編集モード
      String formName = this.getParameter(urlParam, "formName");// DBへの編集モード
      // 7.1.1 ST0236 修正 ここまで
      
      SILogin lLogin = SIHTMLUtil.getLogin(request);
      if (SIConfig.SIACTION_PDF.equalsIgnoreCase(actionName)) {// PDFファイルへの出力
        UIOrderListCond listCond = (UIOrderListCond) session.getAttribute(SIConfig.SISESSION_MAN_ORDER_LIST_NAME);
        if (listCond == null) listCond = new UIOrderListCond();
        // PDFデータの対象するレコード関連のチェック
        this.producePDFFile(response, databaseConnection.getConnection(), lLogin, listCond);
        if (!response.isCommitted()) forwardKey(request, response, "webshop.jsp.manager.order.list");
      } else if (SIConfig.SIACTION_CSV.equalsIgnoreCase(actionName)) {// CSVファイルへの出力
        UIOrderListCond listCond = new UIOrderListCond();
        // 取得するCSVデータの取得
        listCond = (UIOrderListCond) session.getAttribute(SIConfig.SISESSION_MAN_ORDER_LIST_NAME);
        if (listCond == null) listCond = new UIOrderListCond();
        listCond.initDelete(request, urlParam);// 7.1.1 ST0236 修正
        this.produceCSVFile(lLogin, response, databaseConnection.getConnection(), listCond);
        if (!response.isCommitted()) {
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("database.query.notexist", "CSVデータ"));
          forwardKey(request, response, "webshop.jsp.manager.order.list");
          // 7.3.0 ST2023 追加 ここから 管理ログ出力
        } else {
          try {
            siRegMngAccesslogSrv.insertTableData(databaseConnection.getConnection(), request, "9", null);
          } catch (SIDBAccessException e) {
            e.printStackTrace();
          }
          // 7.3.0 ST2023 追加 ここまで
        }
      } else if (SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName)) {// 一覧と検索などの画面のレコードの表示
        session.setAttribute(SIConfig.SISESSION_MAN_NAVIGATER_LIST_NAME, SIConfig.SINAVI_ORDER_LIST);
        UIOrderListCond listCond = new UIOrderListCond();
        // データの取得
        listCond = new UIOrderListCond(request, urlParam);// 7.1.1 ST0236 修正
        if (listCond.getBranchFlgCbo().equals("2")) listCond.setBranchListEnable(true);
        else listCond.setBranchListEnable(false);
        if (listCond.getChargeFlgCbo().equals("2")) listCond.setChargeListEnable(true);
        else listCond.setChargeListEnable(false);
        // データのチェック
        listCond.validate(request);
        session.setAttribute(SIConfig.SISESSION_MAN_ORDER_LIST_NAME, listCond);
        forwardKey(request, response, "webshop.jsp.manager.order.list");
      } else if (SIConfig.SIACTION_REFRESH.equalsIgnoreCase(actionName)) {
        UIOrderListCond listCond = new UIOrderListCond(request, urlParam);
        listCond.setBelongingBranchCode(listCond.getBranchCode());
        if (listCond.getBranchFlgCbo().equals("2")) listCond.setBranchListEnable(true);
        else listCond.setBranchListEnable(false);
        if (listCond.getChargeFlgCbo().equals("2")) listCond.setChargeListEnable(true);
        else listCond.setChargeListEnable(false);
        listCond.setChargeCode("");
        // データのチェック
        listCond.validate(request);
        session.setAttribute(SIConfig.SISESSION_MAN_ORDER_LIST_NAME, listCond);
        forwardKey(request, response, "webshop.jsp.manager.order.list");
      } else if (SIConfig.SIACTION_DELETE.equalsIgnoreCase(actionName)) {// 受注関連のレコードの削除
        UIOrderListCond listCond = new UIOrderListCond();
        // 削除するPKデータの取得
        listCond = new UIOrderListCond();
        listCond.initDelete(request, urlParam);// 7.1.1 ST0236 修正
        // 削除するレコード関連のチェック
        if (listCond.validateDelete(request, databaseConnection.getConnection())) {// 問題がなければ
          try {
            deleteTableData(databaseConnection.getConnection(), listCond);
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.delete"));
            try {
              databaseConnection.getConnection().commit();
            } catch (SQLException sqle) {}
          } catch (SIDBAccessException sqle) {
            try {
              databaseConnection.getConnection().rollback();
            } catch (SQLException ee) {}
            sqle.printStackTrace();
            SICustomErrors errors = new SICustomErrors();
            errors.addError(new SICustomError("database.execute.error"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK, errors);
          }
        }
        forwardKey(request, response, "webshop.jsp.manager.order.list");
      } else if (SIConfig.SIACTION_UPDATE.equalsIgnoreCase(actionName)) {// 入金日の設定
        UIOrderListCond orderCond = (UIOrderListCond) session.getAttribute(SIConfig.SISESSION_MAN_ORDER_LIST_NAME);
        if (orderCond == null) orderCond = new UIOrderListCond();
        // 入金日を設定するPKを設定
        orderCond.initReceipt(request, urlParam);// 7.1.1 ST0236 修正
        session.setAttribute(SIConfig.SISESSION_MAN_ORDER_LIST_NAME, orderCond);
        // 入金日を設定するレコード関連のチェック
        if (orderCond.validateReceipt(request, databaseConnection.getConnection())) {// 問題がなければ
          try {
            updateReceiptDate(databaseConnection.getConnection(), orderCond);
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.modify"));
            try {
              databaseConnection.getConnection().commit();
            } catch (SQLException sqle) {}
          } catch (SIDBAccessException sqle) {
            try {
              databaseConnection.getConnection().rollback();
            } catch (SQLException ee) {}
            sqle.printStackTrace();
            SICustomErrors errors = new SICustomErrors();
            errors.addError(new SICustomError("database.execute.error"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          }
        }
        forwardKey(request, response, "webshop.jsp.manager.order.list");
      } else if (SIConfig.SIACTION_MAIL.equalsIgnoreCase(actionName)) {// 入金督促メール
        UIOrderListCond orderCond = (UIOrderListCond) session.getAttribute(SIConfig.SISESSION_MAN_ORDER_LIST_NAME);
        if (orderCond == null) orderCond = new UIOrderListCond();
        // メールを送るPKを設定
        orderCond.initReceipt(request, urlParam);// 7.1.1 ST0236 修正
        session.setAttribute(SIConfig.SISESSION_MAN_ORDER_LIST_NAME, orderCond);
        if (orderCond.validateMail(request, databaseConnection.getConnection())) {// 問題がなければ
          try {
            if (sendMail(request, databaseConnection.getConnection(), orderCond)) {
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.mail"));
            } else {
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.failure.mail"));
            }
            try {
              databaseConnection.getConnection().commit();
            } catch (SQLException sqle) {}
          } catch (SIDBAccessException e) {// エラーがあったら
            e.printStackTrace();
            try {
              databaseConnection.getConnection().rollback();
            } catch (SQLException ee) {
              ee.printStackTrace();
            }
            SICustomErrors errors = new SICustomErrors();
            errors.addError(new SICustomError("database.execute.error"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          }
        }
        forwardKey(request, response, "webshop.jsp.manager.order.list");
      } else if (SIConfig.SIACTION_PURCHASE.equalsIgnoreCase(actionName)) {// 購買履歴
        UIPurchaseHistory purchaseHistory = new UIPurchaseHistory();
        purchaseHistory.setBackURL(this.getParameter(urlParam, "backUrl"));
        purchaseHistory.setPageNumberTxt("1");
        purchaseHistory.setPageSizeSel(String.valueOf(SIConfig.SIPAGE_SIZE_DEFAULT));
        if (purchaseHistory.orderReset(databaseConnection.getConnection(), this.getParameter(urlParam, "orderCode"))) {
          session.setAttribute(SIConfig.SISESSION_MAN_PURCHASE_HISATORY_NAME, purchaseHistory);
          forwardKey(request, response, "webshop.jsp.manager.cust.purchase");
        } else {
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("database.execute.error"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          forwardKey(request, response, "webshop.jsp.manager.order.list");
        }
      } else if (SIConfig.SIACTION_PDF2.equalsIgnoreCase(actionName)) {
        UIRegUpdateEstimate updateEstimate = new UIRegUpdateEstimate();
        try {
          updateEstimate.initializeOrder(databaseConnection.getConnection(), this.getParameter(urlParam, "orderCode"), manLogin);
          File lOrderTempDir = (File) this.getServletContext().getAttribute("javax.servlet.context.tempdir");
          String template = (String) this.getServletContext().getRealPath("/docs/Estimate.pdf");
          SIUpdateEstimateSrv.producePDFFile(response, databaseConnection.getConnection(), updateEstimate, lOrderTempDir, template, true);
        } catch (SIDBAccessException sqle) {
          sqle.printStackTrace();
        } catch (DocumentException de) {
          de.printStackTrace();
        }
        if (!response.isCommitted()) forwardKey(request, response, "webshop.jsp.manager.order.list");
      } else if (SIConfig.SIACTION_EXCEL.equalsIgnoreCase(actionName)) {
        String lOrderCode = this.getParameter(urlParam, "orderCode");
        String mailComment = this.getParameter(urlParam, "mailComment");
        this.shippmentRequestExcel(response, databaseConnection.getConnection(), lOrderCode, mailComment);
        if (!response.isCommitted()) forwardKey(request, response, "webshop.jsp.manager.order.list");
      } else if (SIConfig.SIACTION_MAIL2.equalsIgnoreCase(actionName)) {// 出荷指示メール
        String lOrderCode = this.getParameter(urlParam, "orderCode");
        String mailComment = this.getParameter(urlParam, "mailComment");
        boolean cashFlg = "1".equals(this.getParameter(urlParam, "cashFlg"));
        boolean cashError = false;
        boolean paymentError = false;
        String cashBranch = this.getParameter(urlParam, "cashBranch");
        String cashPrice = this.getParameter(urlParam, "cashPrice");
        String[] cashParam = new String[2];
        if (cashFlg) {
          SICustomErrors errors = new SICustomErrors();
          StringBuffer checkStr = new StringBuffer();
          checkStr.append("SELECT b.branchname FROM orderallocationinfotbl a,branchtbl b ");
          checkStr.append("WHERE a.branchcode=b.branchcode AND a.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode," "));
          checkStr.append("AND a.branchcode=").append(SIDBUtil.SQL2Str(cashBranch));
          SICheckValid.checkExist(errors, databaseConnection.getConnection(), "出荷店舗",checkStr.toString());
          SICheckValid.checkValid(errors, "代引金額", cashPrice, SICheckDataConf.SICHECK_DATA_DIGIT_TYPE);
          if (!errors.isEmpty()) {
            cashError = true;
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          }
          cashParam[0] = cashBranch;
          cashParam[1] = cashPrice;
        }else{//代引指示がない時
          SICustomErrors errors = new SICustomErrors();
          try {
            StringBuffer payStr = new StringBuffer();
            payStr.append("SELECT CASE WHEN p.ordertotal > p.paymenttotal THEN 0 ELSE 1 END AS payment");
            payStr.append(",p.paymethodname,c.receivableflg ");
            payStr.append("FROM orderpaymentvw p,orderlatestvw h,custtbl c ");
            payStr.append("WHERE p.ordercode=h.ordercode AND h.custcode=c.custcode ");
            payStr.append("AND p.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode));
            
            Statement lStatement = databaseConnection.getConnection().createStatement();
            ResultSet lResultSet = lStatement.executeQuery(payStr.toString());
            if (lResultSet.next()) {
              if ("代金引換".equals(lResultSet.getString("paymethodname"))) {
                errors.addError(new SICustomError("manager.message.freeword","代金引換の指示が指定されていないため出荷指示が行えません"));
              }else if ("現金".equals(lResultSet.getString("paymethodname")) || "銀行振込".equals(lResultSet.getString("paymethodname"))) {
                if ("0".equals(lResultSet.getString("payment"))&&"0".equals(lResultSet.getString("receivableflg"))){
                  errors.addError(new SICustomError("manager.message.freeword","入金が不足しているため出荷指示が行えません"));
                }
              }
            }
            SIDBUtil.close(lStatement, lResultSet);
          } catch (Exception e) {
            e.printStackTrace();
            errors.addError(new SICustomError("database.execute.error"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK, errors);
            paymentError=true;
          }
          if (!errors.isEmpty()) {
            paymentError = true;
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          }
        }
        if (cashFlg&&cashError||paymentError) {
        }else{
        try {
          String fromMail = SIDBUtil.getFirstData(databaseConnection.getConnection(), "SELECT email FROM chargetbl WHERE chargecode=" + SIDBUtil.SQL2Str(manLogin.getUserCode()));
          if (this.sendMail2(request, databaseConnection.getConnection(), lOrderCode, mailComment, fromMail,cashParam)) {
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.mail"));
          } else {
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.failure.mail"));
          }
        } catch (SIDBAccessException e) {
          try {
            databaseConnection.getConnection().rollback();
          } catch (SQLException ee) {}
          e.printStackTrace();
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("database.execute.error"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK, errors);
        } catch (SIDuplicateKeyException e) {
          try {
            databaseConnection.getConnection().rollback();
          } catch (SQLException ee) {}
          e.printStackTrace();
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("database.execute.error"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK, errors);
        } catch (SQLException e) {
          try {
            databaseConnection.getConnection().rollback();
          } catch (SQLException ee) {}
          e.printStackTrace();
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("database.execute.error"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK, errors);
        }
        refreshOrder(request, databaseConnection.getConnection(), urlParam);
        }
        if (editMode.equals("update")) forwardKey(request, response, "webshop.jsp.manager.order.update");
        else if (editMode.equals("edit")) forwardKey(request, response, "webshop.jsp.manager.order.edit");
        else forwardKey(request, response, "webshop.jsp.manager.order.list");
      } else if (SIConfig.SIACTION_KEEP_CMDTY.equalsIgnoreCase(actionName)) {// 取り置き依頼メール
        String ordercode = this.getParameter(urlParam, "orderCode");
        try {
          String fromMail = SIDBUtil.getFirstData(databaseConnection.getConnection(), "SELECT email FROM chargetbl WHERE chargecode=" + SIDBUtil.SQL2Str(manLogin.getUserCode()));
          if (sendOrderKeepMail(databaseConnection.getConnection(), ordercode, fromMail)) {
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.mail"));
          } else {
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.failure.mail"));
          }
          try {
            databaseConnection.getConnection().commit();
          } catch (SQLException sqle) {}
        } catch (SIDBAccessException e) {
          try {
            databaseConnection.getConnection().rollback();
          } catch (SQLException sqle) {}
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("manager.message.failure.mail"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK, errors);
        }
        refreshOrder(request, databaseConnection.getConnection(), urlParam);
        if (editMode.equals("update")) forwardKey(request, response, "webshop.jsp.manager.order.update");
        else if (editMode.equals("edit")) forwardKey(request, response, "webshop.jsp.manager.order.edit");
        else forwardKey(request, response, "webshop.jsp.manager.order.list");
      } else if (SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)) {// 詳細から戻る
        forwardKey(request, response, "webshop.jsp.manager.order.list");
      } else if (SIConfig.SIACTION_MODIFY.equalsIgnoreCase(actionName)) {// レコードの編集画面へ
        refreshOrder(request, databaseConnection.getConnection(), urlParam);// 7.1.1 ST0236 修正
        if (session.getAttribute(SIConfig.SISESSION_MAN_ORDER_EDIT_NAME) == null) {
          forwardKey(request, response, "webshop.jsp.manager.order.list");
        } else if (SIUtil.notMatch(SIConfig.SIADMIN_MS, lLogin.getAdminType())) {
          forwardKey(request, response, "webshop.jsp.manager.order.edit");
        } else {
          forwardKey(request, response, "webshop.jsp.manager.order.shop.edit");
        }
      } else if ("purchaseModify".equalsIgnoreCase(actionName)) {// 購買履歴メモ更新モード
        UIRegOrder lRegOrder = (UIRegOrder) session.getAttribute(SIConfig.SISESSION_MAN_ORDER_EDIT_NAME);
        lRegOrder.getMemoBean().reset(databaseConnection.getConnection(), lRegOrder.getCustCode(), (String)urlParam.getParam("commentNumber"));
        session.setAttribute(SIConfig.SISESSION_MAN_ORDER_EDIT_NAME,lRegOrder);
        forwardKey(request, response, "webshop.jsp.manager.order.edit");
      } else if ("purchaseReset".equalsIgnoreCase(actionName)) {// 購買履歴メモリセット
        UIRegOrder lRegOrder = (UIRegOrder) session.getAttribute(SIConfig.SISESSION_MAN_ORDER_EDIT_NAME);
        lRegOrder.resetMemo();
        session.setAttribute(SIConfig.SISESSION_MAN_ORDER_EDIT_NAME,lRegOrder);
        forwardKey(request, response, "webshop.jsp.manager.order.edit");
      } else if ("purchaseRegist".equalsIgnoreCase(actionName)) {// 購買履歴メモ登録
        UIRegOrder lRegOrder = (UIRegOrder) session.getAttribute(SIConfig.SISESSION_MAN_ORDER_EDIT_NAME);
        lRegOrder.initMemo(urlParam);
        lRegOrder.createMemo(databaseConnection.getConnection(), manLogin.getUserName());
        lRegOrder.resetMemo();
        session.setAttribute(SIConfig.SISESSION_MAN_ORDER_EDIT_NAME,lRegOrder);
        request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.insert"));
        forwardKey(request, response, "webshop.jsp.manager.order.edit");
      } else if ("purchaseDelete".equalsIgnoreCase(actionName)) {// 購買履歴メモ削除
        UIRegOrder lRegOrder = (UIRegOrder) session.getAttribute(SIConfig.SISESSION_MAN_ORDER_EDIT_NAME);
        lRegOrder.initMemo(urlParam);
        lRegOrder.deleteMemo(databaseConnection.getConnection());
        lRegOrder.resetMemo();
        session.setAttribute(SIConfig.SISESSION_MAN_ORDER_EDIT_NAME,lRegOrder);
        request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.delete"));
        forwardKey(request, response, "webshop.jsp.manager.order.edit");
      } else if (SIConfig.SIACTION_UPDATE2.equalsIgnoreCase(actionName)) {// DBへの修正or新規レコードの作成orレコードの削除
        UIRegOrder lRegOrder = (UIRegOrder) session.getAttribute(SIConfig.SISESSION_MAN_ORDER_EDIT_NAME);// 7.2.0 ST1035 修正（移動）
        try {
          String updateMode = (String) urlParam.getParam("updateMode");// 7.1.1 ST0236 修正
          if (updateMode.equals(SIConfig.SIORDER_STATUS_UPDATE)) {
            //lRegOrder.initUpdate(request, databaseConnection.getConnection(), urlParam);// 7.1.1 ST0236 修正
            lRegOrder.initVariableCmdty(request,urlParam);
            session.setAttribute(SIConfig.SISESSION_MAN_ORDER_EDIT_NAME, lRegOrder);
            
            //if (lRegOrder.validate(request, databaseConnection.getConnection(), urlParam) == true) {// データのチェック //7.1.1 ST0236 修正
            if (lRegOrder.validateVariableCmdty(request) == true) {// データのチェック
              String mailSendFlg = (String) urlParam.getParam("mailSendFlg");// 7.1.1 ST0236 修正
              //updateOrderTableData(request, databaseConnection.getConnection(), lRegOrder);
              lRegOrder.resetVariableCmdty(databaseConnection.getConnection());
              SIOrder.checkPrice(databaseConnection.getConnection(), lRegOrder.getOrderCode());
              try {
                databaseConnection.getConnection().commit();
              } catch (SQLException sqle) {}
              session.removeAttribute(SIConfig.SISESSION_MAN_ORDER_COMMON_NAME);// 成功すれば
              refreshOrder(request, databaseConnection.getConnection(), urlParam);// 7.1.1 ST0236 修正
              // 受信確認メールを再送します
              if (SIUtil.isNotNull(mailSendFlg) && mailSendFlg.equals("1")) {
                UIOrderListCond lListCond = new UIOrderListCond();
                String[] chk = { "0" };
                String[] orderCode = { lRegOrder.getOrderCode() };
                lListCond.setOrderCodeChk(chk);
                lListCond.setOrderCodeHid(orderCode);
                lListCond.setMailType(SIConfig.SIMAIL_TEMPLATE_ORDER_COMFIRM);
                if (sendMail(request, databaseConnection.getConnection(), lListCond)) {
                  request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.modify.mail"));
                } else {
                  request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.modify.mail2"));
                }
              } else {
                request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.modify"));
              }
            }
          } else if (updateMode.equals(SIConfig.SIORDER_STATUS_CANCEL)) {
            // Frontのキャンセルと同じ処理
            if (lRegOrder.validateCancel(request, databaseConnection.getConnection())) {// リロードされたときのチェック
              boolean sendflg = true;
              sendflg = (new SIOrderHistorySrv()).orderCancel(databaseConnection.getConnection(), lRegOrder.getOrderCode());
              try {
                databaseConnection.getConnection().commit();
              } catch (SQLException sqle) {}
              session.removeAttribute(SIConfig.SISESSION_MAN_ORDER_COMMON_NAME);// 成功すれば
              refreshOrder(request, databaseConnection.getConnection(), urlParam);// 7.1.1 ST0236 修正
              if (sendflg) {
                request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.cancel.mail"));
              } else {
                request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.cancel.mail2"));
              }
            }
          } else if (updateMode.equals(SIConfig.SIORDER_STATUS_RETURN)) {// リロードされたときのチェック
            if (lRegOrder.validateReturn(request, databaseConnection.getConnection())) {
              lRegOrder.initReturnItems(request, databaseConnection.getConnection(), urlParam);
              orderReturn(databaseConnection.getConnection(),manLogin.getUserCode(),lRegOrder);
              try {
                databaseConnection.getConnection().commit();
              } catch (SQLException sqle) {}
              session.removeAttribute(SIConfig.SISESSION_MAN_ORDER_COMMON_NAME);// 成功すれば
              refreshOrder(request, databaseConnection.getConnection(), urlParam);// 7.1.1 ST0236 修正
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.return.mail2"));
            }
          /* 通常返品とキープ返品を統合
          } else if (updateMode.equals(SIConfig.SIORDER_STATUS_KEEPRETURN)) {// リロードされたときのチェック
            if (lRegOrder.validateCancel(request, databaseConnection.getConnection())) {
              String keepNumber = orderReturn(databaseConnection.getConnection(), lRegOrder.getOrderCode(),manLogin.getUserCode(),true);
              try {
                databaseConnection.getConnection().commit();
              } catch (SQLException sqle) {}
              session.removeAttribute(SIConfig.SISESSION_MAN_ORDER_COMMON_NAME);
              refreshOrder(request, databaseConnection.getConnection(), urlParam);
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.return.keep",keepNumber));
            }
          */
          }
          // forwardKey(request,response,"webshop.jsp.manager.order.edit");//7.2.0 ST1035 削除
        } catch (SIDBAccessException e) {// エラーがあったら
          e.printStackTrace();
          try {
            databaseConnection.getConnection().rollback();
          } catch (SQLException ee) {
            ee.printStackTrace();
          }
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("database.execute.error"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          // forwardKey(request,response,"webshop.jsp.manager.order.edit");//7.2.0 ST1035 削除
        } catch (SIMismatchException e) {
          e.printStackTrace();
          try {
            databaseConnection.getConnection().rollback();
          } catch (SQLException ee) {
            ee.printStackTrace();
          }
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("manager.app.price.mismatch"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          // forwardKey(request,response,"webshop.jsp.manager.order.edit");//7.2.0 ST1035 削除
        } catch (SIErrorException erre) {
          try {
            databaseConnection.getConnection().rollback();
          } catch (SQLException ee) {}
          erre.printStackTrace();
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("manager.message.failure.cancel"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          // forwardKey(request,response,"webshop.jsp.manager.order.edit");//7.2.0 ST1035 削除
        }
        // 7.2.0 ST1035 追加ここから
        if (lRegOrder.getBackUrl().equals("webshop.jsp.manager.shippment.list")) {
          forwardKey(request, response, "webshop.jsp.manager.shippment.edit");
        } else {
          forwardKey(request, response, "webshop.jsp.manager.order.edit");
        }
        // 7.2.0 ST1035 追加ここまで
      }
    } catch (SQLException e) {
      e.printStackTrace();
      throw new ServletException();
    } catch (NamingException e) {
      e.printStackTrace();
      throw new ServletException();
    } finally {
      databaseConnection.close();
    }
  }
  
  /**
   * <b>deleteTableData</b> データベースにレコードを削除します。
   * 
   * @param lConnection DBへのコネクション
   * @param lListCond 削除するデータ
   * @return なし
   * @throws SIDBAccessException
   */
  private void deleteTableData(Connection lConnection, UIOrderListCond lListCond) throws SIDBAccessException {
    if (lListCond.getOrderCodeChk() == null) return;
    SIDeleteRec lRec = new SIDeleteRec();
    String lOrderCode = "";
    try {
      for (int ii = 0; ii < lListCond.getOrderCodeChk().length; ii++) {
        lOrderCode = lListCond.getOrderCodeHid()[Integer.parseInt(lListCond.getOrderCodeChk()[ii])];
        if (SIUtil.isNotNull(lOrderCode)) {
          // 1.受注ヘッダのレコードの削除
          lRec = new SIDeleteRec(SIConfig.SITABLE_ORDER_NAME);// 7.2.0 ST1030 修正
          lRec.addCondition("OrderCode", lOrderCode);// 受注コード
          lRec.execute(lConnection);
          
          // 2.受注配送先のレコードの削除
          lRec = new SIDeleteRec(SIConfig.SITABLE_ORDER_DELIVERY_NAME);// 7.2.0 ST1030 修正
          lRec.addCondition("OrderCode", lOrderCode);// 受注コード
          lRec.execute(lConnection);
          
          // 3.受注明細のレコードの削除
          lRec = new SIDeleteRec(SIConfig.SITABLE_ORDER_DETAIL_NAME);// 7.2.0 ST1030 修正
          lRec.addCondition("OrderCode", lOrderCode);// 受注コード
          lRec.execute(lConnection);
          
          // 4.ポイント管理のレコードの削除
          lRec = new SIDeleteRec("PointManMTbl");
          lRec.addCondition("OrderCode", lOrderCode);// 受注コード
          // 7.2.0 ST1104 追加 ここから
          SITableCondition lTableCon = new SITableCondition("", "ENABLEFLG", "1", SIConfig.SICONDITION_TYPE_NOT_EQUAL, SIConfig.SICONDITION_TYPE_AND);
          lRec.addCondition(lTableCon);
          // 7.2.0 ST1104 追加 ここまで
          lRec.execute(lConnection);
          
          // 5.RFM分析のレコードの削除
          lRec = new SIDeleteRec("RfmAnalyseTbl");
          lRec.addCondition("OrderCode", lOrderCode);// 受注コード
          lRec.execute(lConnection);
        }
      }
    } catch (SIDuplicateKeyException e) {
      throw new SIDBAccessException(e);
    }
  }
  
  /**
   * <b>updateReceiptDate</b> データベースにレコードを削除します。
   * 
   * @param lConnection DBへのコネクション
   * @param lListCond 削除するデータ
   * @return なし
   * @throws SIDBAccessException
   */
  private void updateReceiptDate(Connection lConnection, UIOrderListCond lListCond) throws SIDBAccessException {
    if (lListCond.getOrderCodeChk() == null) return;
    // SIOrder lOrder=new SIOrder();
    // SIModifyRec lRec=new SIModifyRec();
    // SIMDKAction lMDKAction=new SIMDKAction();
    
    String lOrderCode = "";
    try {
      for (int ii = 0; ii < lListCond.getOrderCodeChk().length; ii++) {
        lOrderCode = lListCond.getOrderCodeHid()[Integer.parseInt(lListCond.getOrderCodeChk()[ii])];
        this.updateReceiptDate(lConnection, lOrderCode, lListCond.getReceiptDate());
      }
    } catch (SIDuplicateKeyException e) {
      throw new SIDBAccessException(e);
    } catch (SIDBAccessException e) {
      throw new SIDBAccessException(e);
    } catch (SQLException e) {
      throw new SIDBAccessException(e);
    }
  }
  
  /**
   * <b>updateReceiptDate</b> データベースにレコードの修正をします。
   * 
   * @param lConnection DBへのコネクション
   * @param lOrderCode
   * @param lReceiptDate
   * @return なし
   * @throws SIDBAccessException
   */
  private void updateReceiptDate(Connection lConnection, String lOrderCode, String lReceiptDate) throws SIDuplicateKeyException, SIDBAccessException, SQLException {
    if (SIUtil.isNotNull(lOrderCode)) {
      // 1.受注ヘッダのレコードの修正
      SIModifyRec lRec = new SIModifyRec();
      lRec = new SIModifyRec(SIConfig.SITABLE_ORDER_NAME);// 7.2.0 ST1030 修正
      lRec.addCondition("OrderCode", lOrderCode);// 受注コード
      if (SIDBMultiConf.SIDB_CURRENT_INX == SIDBMultiConf.SIDB_POSTGRESQL_INX) {
        lRec.add("ReceiptDate", lReceiptDate);// 入金日
      } else {
        lRec.add("ReceiptDate", new SIDateType(lReceiptDate));// 入金日
      }
      lRec.add("Status", "1");// 入金日
      lRec.execute(lConnection);
      // ポイント使用区分の変更
      SIPointMan.changeEnableFlg(lConnection, lOrderCode);
    }
  }
  
  /**
   * <b>updateOrderTableData</b> データベースに受注明細のレコードを修正します。
   * 
   * @param lConnection DBへのコネクション
   * @param regOrder 修正する用の条件とデータ
   * @return なし
   * @throws SIDBAccessException
   */
  private void updateOrderTableData(HttpServletRequest request, Connection lConnection, UIRegOrder regOrder) throws SIDBAccessException {
    SIModifyRec lRec = new SIModifyRec(SIConfig.SITABLE_ORDER_NAME);// 7.2.0 ST1030 修正
    SIDateTime lDateTime = new SIDateTime();
    
    try {
      // 受注ヘッダの更新
      lRec.addCondition("OrderCode", regOrder.getOrderCode());// 受注番号
      
      lRec.add("CustCompanyFlg", regOrder.getCustCompanyFlg());// 法人フラグ
      lRec.add("CustName", regOrder.getCustName());// 顧客名
      lRec.add("CustPronName", regOrder.getCustPronName());// 顧客名カナ
      lRec.add("Email", regOrder.getEmail());// EMAIL
      lRec.add("OrderAddressee", regOrder.getOrderAddressee());// 請求書宛名
      lRec.add("CompanyName", regOrder.getCompanyName());// 会社名
      lRec.add("Tel", regOrder.getTel());// 電話番号
      lRec.add("Fax", regOrder.getFax());// FAX番号
      lRec.add("ContactMsg", regOrder.getContactMsg());// 連絡事項
      lRec.add("Memo", regOrder.getMemo());// 備考
      lRec.add("discountFee", regOrder.getDiscountFee());// 手数料調整額
      lRec.add("discountDeliveryFee", regOrder.getDiscountDeliveryFee());// 送料調整額
      
      // 7.2.0 ST1035 修正ここから
      if (regOrder.getBackUrl().equals("webshop.jsp.manager.order.list")) {
        lRec.add("sumOfDiscount", regOrder.getSumOfDiscount());// 値引き合計
      }
      // 7.2.0 ST1035 修正ここまで
      if (SIDBMultiConf.SIDB_CURRENT_INX == SIDBMultiConf.SIDB_POSTGRESQL_INX) {
        lRec.add("UpdateDateTime", lDateTime.getFullDateTime());
      } else {
        lRec.add("UpdateDateTime", new SIDateTimeType(lDateTime.getFullDateTime()));
      }
      // 7.3.0 ST2023 追加 ここから
      SILogin siLogin = SIHTMLUtil.getLogin(request);
      
      lRec.add("UpdateMallShopCode", siLogin.getMallShopCode()); // 更新ショップ（モール）コード
      lRec.add("UpdateUserCode", siLogin.getUserCode()); // 更新ユーザーコード
      // 7.3.0 ST2023 追加 ここまで
      
      lRec.execute(lConnection);
      
      if (regOrder.getDeliveryCode() != null && regOrder.getDeliveryCode().length != 0) {
        for (int i = 0; i < regOrder.getDeliveryCode().length; i++) {
          lRec = new SIModifyRec(SIConfig.SITABLE_ORDER_DELIVERY_NAME);// 7.2.0 ST1030 修正
          lRec.addCondition("OrderCode", regOrder.getOrderCode());// 受注番号
          lRec.addCondition("deliveryCode", regOrder.getDeliveryCode()[i]);// 受注番号
          lRec.add("deliveryAddressee", regOrder.getDeliveryAddressee()[i]);// 宛名
          lRec.add("CompanyName", regOrder.getDeliveryCompanyName()[i]);// 配送先会社名
          lRec.add("deliveryName", regOrder.getDeliveryName()[i]);// 配送先名
          lRec.add("Tel", regOrder.getDeliveryTel()[i]);// Tel
          lRec.add("Fax", regOrder.getDeliveryFax()[i]);// Fax
          lRec.execute(lConnection);
        }
      }
      // 配送先情報の更新
    } catch (SIDuplicateKeyException sqle) {
      throw new SIDBAccessException(sqle);
    }
  }
  
  /**
   * <b>sendMail</b> メール送信
   * 
   * @param request
   * @param lConnection DBへのコネクション
   * @param lListCond データ
   * @return なし
   * @throws SIDBAccessException
   */
  // 7.2.0 ST1030 修正(private > public)
  public boolean sendMail(HttpServletRequest request, Connection lConnection, UIOrderListCond lListCond) throws SIDBAccessException {
    SILogin lLogin = SIHTMLUtil.getLogin(request);
    boolean result = true;
    StringBuffer lMailBuf = new StringBuffer();
    SISendMail lSendMail = new SISendMail();
    
    UIRegOrder lRegOrder = new UIRegOrder();
    UIRegOrderDelivery lOrderDelivery = new UIRegOrderDelivery();
    UIRegOrderDetail lRegOrderDetail = new UIRegOrderDetail();
    
    Collection lOrderDeliveryColl = new ArrayList();
    Collection lOrderDetailColl = new ArrayList();
    
    Iterator lOrderDeliveryIta;
    Iterator lOrderDetailIta;
    
    String sumOfA = "0";// 商品の購入価格の合計(税込み)
    String sumOfB = "0";// 送料合計(税込み)
    String sumOfC = "0";// 手数料合計(税込み)
    String sumOfD = "0";// 小計(税込み)
    String sumOfE = "0";// ポイント使用値
    String sumOfF = "0";// 支払額
    String sumOfG = "0";// 値引き額
    String sumOfH = "0";// ポイント付加
    String taxFlgOfA = "0";
    String taxFlgOfB = "0";
    String taxFlgOfC = "0";
    String taxFlgOfD = "0";
    String taxFlgOfE = "0";
    String taxFlgOfF = "0";
    String taxFlgOfG = "0";
    String taxFlgOfH = "0";
    String discountFee = "0";// 手数料調整額
    String discountDeliveryFee = "0";// 送料調整額
    
    String lOrderCode = "";
    
    for (int ii = 0; ii < lListCond.getOrderCodeChk().length; ii++) {
      // 7.2.0 ST0284 追加 ここから
      sumOfA = "0";
      sumOfB = "0";
      sumOfC = "0";
      sumOfD = "0";
      sumOfE = "0";
      sumOfF = "0";
      sumOfG = "0";
      sumOfH = "0";
      taxFlgOfA = "0";
      taxFlgOfB = "0";
      taxFlgOfC = "0";
      taxFlgOfD = "0";
      taxFlgOfE = "0";
      taxFlgOfF = "0";
      taxFlgOfG = "0";
      taxFlgOfH = "0";
      // 7.2.0 ST0284 追加 ここまで
      
      lOrderCode = lListCond.getOrderCodeHid()[Integer.parseInt(lListCond.getOrderCodeChk()[ii])];
      
      try {
        lMailBuf = new StringBuffer();
        lSendMail = new SISendMail();
        boolean sendflg = true;
        // 受注情報取得
        lRegOrder = new UIRegOrder();
        lRegOrder.setOrderCode(lOrderCode);
        lRegOrder.reset(lConnection);
        if (!lRegOrder.getStatus().equals("1")) continue;
        
        lRegOrder.setMaxPoint(SIPointMan.getMaxPoint(lConnection, lRegOrder.getCustCode(), lLogin.getMallShopCode()));
        
        lOrderDeliveryColl = lRegOrder.getCollection(lConnection, lLogin);// 配送先リスト
        lOrderDeliveryIta = lOrderDeliveryColl.iterator();
        
        int lDeliveryInx = 0;
        sumOfC = lRegOrder.getFeeIncTax();
        if (taxFlgOfC.equals("0") && !lRegOrder.getFeeTaxFlg().equals("0")) taxFlgOfC = "2";
        
        sumOfE = lRegOrder.getSumByPoint();
        sumOfG = lRegOrder.getSumOfDiscount();
        
        discountFee = lRegOrder.getDiscountFee();
        discountDeliveryFee = lRegOrder.getDiscountDeliveryFee();
        
        // 1.入金確認メールで入金日が設定されていないときは設定する。
        if (lListCond.getMailType().equals(SIConfig.SIMAIL_TEMPLATE_RECEIPT_COMFIRM) && SIUtil.isNull(lRegOrder.getReceiptDate())) {
          this.updateReceiptDate(lConnection, lOrderCode, lListCond.getReceiptDate());
        }
        
        // 処理を行うショップコード
        String mallShopCode = (new SIOrder()).getMailShopCode(lConnection, lRegOrder.getOrderCode());
        
        try {
          lSendMail.setMailTemp(lConnection, mallShopCode, lListCond.getMailType());
        } catch (SIFatalException e2) {
          e2.printStackTrace();
        }
        
        // 2.メールの本文の作成(メールテンプレートの内容1)
        
        // 再受注で確認メールを送信する場合を除く
        if (lListCond.getMailType().equals(SIConfig.SIMAIL_TEMPLATE_ORDER_COMFIRM)) {
          lMailBuf.append("\n受注メールを再送致します。\n");
          lSendMail.insertContent1(lMailBuf.toString());
          lMailBuf = new StringBuffer();
        } else {
          lMailBuf.append("\n");
          lSendMail.appendContent1(lMailBuf.toString());
          lMailBuf = new StringBuffer();
        }
        
        // 3.メールの本文の作成(入金督促)
        if (lListCond.getMailType().equals(SIConfig.SIMAIL_TEMPLATE_RECEIPT_DEMAND) || lListCond.getMailType().equals(SIConfig.SIMAIL_TEMPLATE_ORDER_COMFIRM)) {
          SIPayMethod lPayMethod = new SIPayMethod();
          lPayMethod.setEncode(SIConfig.SIENCODE_NONE);
          lPayMethod.setMallShopCode(mallShopCode);
          lPayMethod.setPayMethodName(lRegOrder.getPayMethodName());
          Collection payeeInfoColl = lPayMethod.getCollection(lConnection);
          Iterator payeeInfoIte = payeeInfoColl.iterator();
          int jj = 0;
          if (payeeInfoIte.hasNext()) {
            lMailBuf.append("\n【お支払先金融機関】");
            while (payeeInfoIte.hasNext()) {
              SIPayeeInfo payeeInfo = (SIPayeeInfo) payeeInfoIte.next();
              jj++;
              lMailBuf.append("\n金融機関" + (jj));
              lMailBuf.append("\n金融機関コード:").append(payeeInfo.getBankCode());
              lMailBuf.append("/金融機関名:").append(payeeInfo.getBankName());
              lMailBuf.append("/金融機関名カナ:").append(payeeInfo.getBankNameKana());
              lMailBuf.append("\n支店コード:").append(payeeInfo.getSubBankCode());
              lMailBuf.append(" /支店名:").append(payeeInfo.getSubBankName());
              lMailBuf.append(" /支店名カナ:").append(payeeInfo.getSubBankNameKana());
              lMailBuf.append("\n口座種類:");
              if (SIUtil.isNotNull(payeeInfo.getAccountType())) {
                lMailBuf.append(SIFlagConf.SIFLAG_ACCOUNT_TYPE_NAME[Integer.parseInt(payeeInfo.getAccountType())][0]);// 口座種類
              }
              lMailBuf.append("/口座名義人カナ:").append(payeeInfo.getAccountName());// 口座名義人
              lMailBuf.append("/口座番号:").append(payeeInfo.getAccountNo());// 口座番号
            }
          }
        }
        
        lMailBuf.append("\n\n");
        if (lListCond.getMailType().equals(SIConfig.SIMAIL_TEMPLATE_ORDER_COMFIRM)) {
          lSendMail.insertContent3(lMailBuf.toString());
        } else {
          lSendMail.appendContent1(lMailBuf.toString());
        }
        lMailBuf = new StringBuffer();
        
        lMailBuf.append("\n\n=============");
        
        // 4.メールの本文の作成(お客様情報)
        lMailBuf.append("\n受注番号:").append(lRegOrder.getOrderCode());
        lMailBuf.append("\n\nお客さま情報");
        lMailBuf.append("\n氏名:").append(lRegOrder.getCustName()).append(" 様");
        lMailBuf.append("\nE-Mail:").append(lRegOrder.getEmail());
        lMailBuf.append("\n連絡先電話番号１:").append(lRegOrder.getStoreTel());// 7.3.0 PI-NES0501 追加
        lMailBuf.append("\n連絡先電話番号２:").append(lRegOrder.getTel());// 7.3.0 PI-NES0501 修正
        lMailBuf.append("\n住所:〒").append(lRegOrder.getPostCode()).append(" ");
        lMailBuf.append(lRegOrder.getAddress());
        lMailBuf.append("\n支払方法:").append(lRegOrder.getPayMethodName());
        if (SIUtil.isNotNull(lRegOrder.getContactMsg())) {
          lMailBuf.append("\n連絡事項:");
          lMailBuf.append("\n").append(lRegOrder.getContactMsg());
        }
        // 7.2.0 ST0300 追加 ここから
        // コンビニ決済使用時
        if (SIUtil.isNotNull(lRegOrder.getCvsTypeCode())) {
          lMailBuf.append("(").append(SIBSCVSMDKUtil.getCvsName(lRegOrder.getCvsTypeCode())).append(")");// コンビニ名称
        }
        // 7.2.0 ST0300 追加 ここまで
        // lMailBuf.append("/手数料:").append(NumberFormat.getNumberInstance().format(new Long(sumOfC))).append("円").append(lRegOrder.getFeeTaxName());//税込み //7.3.0 PI-NES0501 削除
        // 7.4.0 ST2057 追加 ここから
        if (SIUtil.isNotNull(lRegOrder.getCvsTypeCode())) {
          lMailBuf.append("\n受付番号:").append(lRegOrder.getCvsReceiptNo());// 受付番号
          if (SIUtil.isNotNull(lRegOrder.getCvsHaraikomiURL())
              && (lListCond.getMailType().equals(SIConfig.SIMAIL_TEMPLATE_ORDER_COMFIRM) || lListCond.getMailType().equals(SIConfig.SIMAIL_TEMPLATE_RECEIPT_DEMAND))) {
            lMailBuf.append("\n払込URL: ").append(lRegOrder.getCvsHaraikomiURL());// 払込URL
          }
        }
        // 7.4.0 ST2057 追加 ここまで
        lMailBuf.append("\n受注日時:").append(lRegOrder.getInitDateTime());
        // 7.4.0 ST2057 追加 ここから
        if (SIUtil.isNotNull(lRegOrder.getCvsTypeCode())
            && (lListCond.getMailType().equals(SIConfig.SIMAIL_TEMPLATE_ORDER_COMFIRM) || lListCond.getMailType().equals(SIConfig.SIMAIL_TEMPLATE_RECEIPT_DEMAND))) {
          lMailBuf.append("\n支払期限:").append(lRegOrder.getCvsShiharaiKigen());// 支払期限
        }
        // コンビニ用注意書き
        if (SIUtil.isNotNull(lRegOrder.getCvsTypeCode()) && lRegOrder.getCvsTypeCode().equals(SIBSCVSMDKConfig.MDK_CVS_TYPE_LAWSON)
            && (lListCond.getMailType().equals(SIConfig.SIMAIL_TEMPLATE_ORDER_COMFIRM) || lListCond.getMailType().equals(SIConfig.SIMAIL_TEMPLATE_RECEIPT_DEMAND))) {
          lMailBuf.append("\n\n※").append(SIBSCVSMDKConfig.MDK_CVS_COMMENT_01);
          if (SIUtil.isNotNull(lRegOrder.getCvsHaraikomiURL())) {
            lMailBuf.append("\n※").append(SIBSCVSMDKConfig.MDK_CVS_COMMENT_02);
          }
        }
        // 7.4.0 ST2057 追加 ここまで
        if (lOrderDeliveryIta.hasNext()) {// 7.3.0 PI-NES0501 修正
          lDeliveryInx++;
          lOrderDelivery = (UIRegOrderDelivery) lOrderDeliveryIta.next();
          // sumOfB=SIUtil.add(sumOfB,lOrderDelivery.getDeliveryFeeIncTax());//送料の合計
          if (taxFlgOfB.equals("0") && !lOrderDelivery.getDeliveryTaxFlg().equals("0")) taxFlgOfB = "2";
          
          // 5.メールの本文の作成(配送先の内容)
          lMailBuf.append("\n\n--------------");
          lMailBuf.append("\n配送先");// 7.3.0 PI-NES0501 修正
          lMailBuf.append("\n宛名:").append(lOrderDelivery.getDeliveryAddressee()).append(" 様");// 7.1.1 ST0186 修正//7.4.0 ST2057 修正(宛先→宛名)
          lMailBuf.append("\n住所:〒").append(lOrderDelivery.getPostCode()).append(" ");
          lMailBuf.append(lOrderDelivery.getAddress());
          // 7.3.0 PI-NES0501 削除 ここから
          /*
           * lMailBuf.append("\n送料:").append(NumberFormat.getNumberInstance().format(new
           * Long(lOrderDelivery.getDeliveryFeeIncTax()))).append("円").append(lOrderDelivery.getDeliveryTaxName());
           * lMailBuf.append("\n配送方法:").append(lOrderDelivery.getDeliveryTypeName()); if(SIUtil.isNotNull(lOrderDelivery.getDeliveryDate())){
           * lMailBuf.append("\n配送希望日時:").append(lOrderDelivery.getDeliveryDate());//7.2.0 ST1077 修正 }
           */
          lMailBuf.append("\nTEL:").append(lOrderDelivery.getTel());
          
          // 7.3.0 PI-NES0501 削除 ここまで
        } // 7.3.0 PI-NES0501 追加
        
        // 7.3.0 PI-NES0501 追加 ここから
        lMailBuf.append("\n--------------");
        lMailBuf.append("\n");
        lOrderDeliveryColl = lRegOrder.getCollection(lConnection, lLogin);// 配送先リスト
        lOrderDeliveryIta = lOrderDeliveryColl.iterator();
        while (lOrderDeliveryIta.hasNext()) {
          lOrderDelivery = (UIRegOrderDelivery) lOrderDeliveryIta.next();
          lOrderDetailColl = lOrderDelivery.getDetailCollection(lConnection);
          lOrderDetailIta = lOrderDetailColl.iterator();
          // String deliveryTypeCode = lOrderDelivery.getDeliveryTypeCode();//7.3.0 PI-NES0501 追加
          
          lMailBuf.append("\n「").append(lOrderDelivery.getDeliveryTypeName()).append("送料」対象商品");
          // 7.3.0 PI-NES0501 追加 ここまで
          
          while (lOrderDetailIta.hasNext()) {
            lRegOrderDetail = (UIRegOrderDetail) lOrderDetailIta.next();
            sumOfA = SIUtil.add(sumOfA, lRegOrderDetail.getPriceIncGift());
            if (taxFlgOfA.equals("0") && (!lRegOrderDetail.getTaxFlg().equals("0") || !lRegOrderDetail.getWrappingTaxFlg().equals("0"))) {
              taxFlgOfA = "2";
            }
            
            // 6.メールの本文の作成(受注明細の内容)
            lMailBuf.append("\n=============");
            lMailBuf.append("\n商品コード:").append(lRegOrderDetail.getIndividualCode());
            lMailBuf.append("\n商品名:").append(lRegOrderDetail.getCartCmdtyName());
            lMailBuf.append("\nカラー:").append(lRegOrderDetail.getColorName());
            lMailBuf.append("\n数量:").append(NumberFormat.getNumberInstance().format(new Long(lRegOrderDetail.getAmount())));
            lMailBuf.append("\n単価:").append(NumberFormat.getNumberInstance().format(new Long(lRegOrderDetail.getPriceIncTax()))).append("円").append(
                "(" + lRegOrderDetail.getCmdtyTaxName() + ")");
            lMailBuf.append("\n商品合計額:").append(NumberFormat.getNumberInstance().format(new Long(lRegOrderDetail.getPriceOfCmdty()))).append("円");
            // 7.3.0 PI-NES0501 修正 ここから
            // lMailBuf.append("\n個別送料:").append(NumberFormat.getNumberInstance().format(new Long(lRegOrderDetail.getDeliveryFee()))).append("円");
            lMailBuf.append("\n個別送料:").append(lRegOrderDetail.getDeliveryFee()).append("円");
            sumOfB = SIUtil.add(sumOfB, lRegOrderDetail.getDeliveryFee());
            lMailBuf.append("\n配送希望日:").append(lRegOrderDetail.getDeliveryDateTxt());
            if(SIUtil.isNotNull(lRegOrderDetail.getDeliveryTimeTxt())) {
              lMailBuf.append("\n希望時間帯:").append(lRegOrderDetail.getDeliveryTimeTxt());
            }
            lMailBuf.append("\n総合計金額:").append(
                NumberFormat.getNumberInstance().format(new Long(SIUtil.add_LL(lRegOrderDetail.getPriceIncGift(), lRegOrderDetail.getDeliveryFee())))).append("円");// 7.3.0
            // PI-NES0501 追加
            if (!lRegOrderDetail.getTaxFlg().equals("0") || !lRegOrderDetail.getWrappingTaxFlg().equals("0")) {
              lMailBuf.append(SITax.getFullTaxFlgName2("2", lRegOrderDetail.getPriceIncGift()));
            } else {
              lMailBuf.append(SITax.getFullTaxFlgName2("0", lRegOrderDetail.getPriceIncGift()));
            }
          }
          // 7.3.0 PI-NES0501 修正 ここまで
          // 7.3.0 PI-NES0501 削除 ここから
          /*
           * if (SIUtil.isNull(lRegOrderDetail.getWrappingName()))lMailBuf.append("\nギフト包装:なし"); else lMailBuf.append("\nギフト包装:").append(lRegOrderDetail.getWrappingName());
           * lMailBuf.append("\n包装代:").append(NumberFormat.getNumberInstance().format(new Long(lRegOrderDetail.getWrappingPriceOfTotal()))).append("円");
           * lMailBuf.append(SITax.getFullTaxFlgName2(lRegOrderDetail.getWrappingTaxFlg(),lRegOrderDetail.getWrappingPriceOfTotal()));
           */
          // 7.3.0 PI-NES0501 削除 ここまで
          lMailBuf.append("\n");
        }
        lMailBuf.append("\n=============");
        lMailBuf.append("\n");// 7.3.0 PI-NES0501 追加
        
        sumOfD = SIUtil.add(SIUtil.add(sumOfA, sumOfB, sumOfC), discountFee, discountDeliveryFee);
        if (!taxFlgOfA.equals("0") || !taxFlgOfB.equals("0") || !taxFlgOfC.equals("0")) taxFlgOfD = "2";
        
        // 7.メールの本文の作成(合計)
        lMailBuf.append("\n--------------");
        lMailBuf.append("\n合計\n商品合計(A):").append(NumberFormat.getNumberInstance().format(new Long(sumOfA))).append("円").append(SITax.getFullTaxFlgName2(taxFlgOfA, sumOfA));
        lMailBuf.append("\n送料合計(B):").append(NumberFormat.getNumberInstance().format(new Long(SIUtil.add(sumOfB, discountDeliveryFee)))).append("円").append(
            SITax.getFullTaxFlgName2(taxFlgOfB, sumOfB));
        lMailBuf.append("\n手数料合計(C):").append(NumberFormat.getNumberInstance().format(new Long(SIUtil.add(sumOfC, discountFee)))).append("円").append(
            SITax.getFullTaxFlgName2(taxFlgOfC, sumOfC));// 7.3.0 PI-NES0501 削除
        
        if (lRegOrder.getPointEnableFlg().equals("1")) {
          sumOfF = SIUtil.sub_LL(SIUtil.sub_LL(sumOfD, sumOfG), sumOfE);
          if (!taxFlgOfD.equals("0") || !taxFlgOfG.equals("0") || !taxFlgOfE.equals("0")) taxFlgOfF = "2";// お支払い合計額の税区分
            
          lMailBuf.append("\n小計(D=A+B+C):").append(NumberFormat.getNumberInstance().format(new Long(sumOfD))).append("円").append(SITax.getFullTaxFlgName2(taxFlgOfD, sumOfD));// 7.3.0
          // PI-NES0501
          // 修正
          lMailBuf.append("\nポイント使用額（ポイント残高 ").append(NumberFormat.getNumberInstance().format(new Long(lRegOrder.getMaxPoint()))).append("ポイント）(D):");// 7.3.0 PI-NES0501 修正
          lMailBuf.append(NumberFormat.getNumberInstance().format(new Long(sumOfE))).append("円");
          if (SIUtil.isNotNull(sumOfG) && Long.parseLong(sumOfG) > 0) {
            lMailBuf.append("\n値引金額(G)：").append(NumberFormat.getNumberInstance().format(new Long(sumOfG))).append("円");// 7.3.0 PI-NES0501 修正
            lMailBuf.append("\nお支払い合計額(F=D-E-G):").append(NumberFormat.getNumberInstance().format(new Long(sumOfF))).append("円")
                .append(SITax.getFullTaxFlgName2(taxFlgOfF, sumOfF));// 7.3.0 PI-NES0501 修正
          } else {
            lMailBuf.append("\nお支払い合計額(F=D-E):").append(NumberFormat.getNumberInstance().format(new Long(sumOfF))).append("円").append(SITax.getFullTaxFlgName2(taxFlgOfF, sumOfF));// 7.3.0
            // PI-NES0501
            // 修正
          }
        } else {
          sumOfF = SIUtil.sub_LL(sumOfD, sumOfG);
          if (!taxFlgOfD.equals("0") || !taxFlgOfG.equals("0")) taxFlgOfF = "2";// お支払い合計額の税区分
            
          if (SIUtil.isNotNull(sumOfG) && Long.parseLong(sumOfG) > 0) {
            lMailBuf.append("\n小計(D=A+B+C):").append(NumberFormat.getNumberInstance().format(new Long(sumOfD))).append("円").append(SITax.getFullTaxFlgName2(taxFlgOfD, sumOfD));// 7.3.0
            // PI-NES0501
            // 修正
            lMailBuf.append("\n値引金額(G)：").append(NumberFormat.getNumberInstance().format(new Long(sumOfG))).append("円");// 7.3.0 PI-NES0501 修正
            lMailBuf.append("\nお支払い合計額(F=D-G):").append(NumberFormat.getNumberInstance().format(new Long(sumOfF))).append("円").append(SITax.getFullTaxFlgName2(taxFlgOfF, sumOfF));// 7.3.0
            // PI-NES0501
            // 修正
          } else {
            lMailBuf.append("\nお支払い合計額(D=A+B+C):").append(NumberFormat.getNumberInstance().format(new Long(sumOfF))).append("円")
                .append(SITax.getFullTaxFlgName2(taxFlgOfF, sumOfF));// 7.3.0 PI-NES0501 修正
          }
        }
        
        // 8メールの本文の作成(メールテンプレートの内容3)
        if (lListCond.getMailType().equals(SIConfig.SIMAIL_TEMPLATE_ORDER_COMFIRM)) {
          String lPoint = "0";// ポイント付加
          Statement lStatement = null;
          ResultSet lResultSet = null;
          
          // 付加ポイント
          StringBuffer lSqlBuf = new StringBuffer();
          lSqlBuf.append("SELECT PointOfIssue FROM PointmanMTbl ");
          lSqlBuf.append("WHERE OrderCode = ").append(SIDBUtil.SQL2Str(lOrderCode, " "));
          lSqlBuf.append("AND EnableFlg<>0");
          
          try {
            lStatement = lConnection.createStatement();
            lResultSet = lStatement.executeQuery(lSqlBuf.toString());
            while (lResultSet.next()) {
              if (Integer.parseInt(lResultSet.getString("PointOfIssue")) > 0) {
                lPoint = SIUtil.add(lPoint, lResultSet.getString("PointOfIssue"));
              }
            }
          } catch (SQLException e) {
            e.printStackTrace();
          } finally {
            SIDBUtil.close(lStatement, lResultSet);
          }
          // 7.1.1 ST0161 修正 ここから
          if (lRegOrder.getPointEnableFlg().equals("1")) {
            lMailBuf.append("\n");
            lMailBuf.append("\n獲得予定ポイント数：").append(lPoint).append("ポイント");
          }
          // 7.1.1 ST0161 修正 ここまで
          lMailBuf.append("\n\n=============");
          lSendMail.insertContent2(lMailBuf.toString());
        } else {
          lMailBuf.append("\n\n=============");
          lSendMail.appendContent2(lMailBuf.toString());// 未入金督促メール
        }
        
        // 9.入金メール送信フラグを更新
        if (lListCond.getMailType().equals(SIConfig.SIMAIL_TEMPLATE_RECEIPT_COMFIRM)) {
          SIModifyRec lRec = new SIModifyRec(SIConfig.SITABLE_ORDER_NAME);// 7.2.0 ST1030 修正
          lRec.addCondition("orderCode", lOrderCode);
          lRec.add("receiptMailFlg", "1");
          lRec.execute(lConnection);
        }
        
        // 10.送信開始
        try {
          lSendMail.setToMailAddress(lRegOrder.getCurrentEmail());// 7.2.0 ST1018 修正
          // 今回送信失敗かどうか？
          sendflg = lSendMail.execute();
          // 一部のメールで失敗かどうか？
          result = sendflg && result;
        } catch (SIFatalException e) {
          result = false;
          e.printStackTrace();
        }
        
        // メールはロールバックできないので１件コミット
        if (sendflg) {
          try {
            lConnection.commit();
          } catch (SQLException sqle) {}
        } else {
          try {
            lConnection.rollback();
          } catch (SQLException sqle) {}
        }
      } catch (SIDBAccessException e) {
        throw new SIDBAccessException(e);
      } catch (SIDuplicateKeyException e) {
        throw new SIDBAccessException(e);
      } catch (SQLException e) {
        throw new SIDBAccessException(e);
      }
    }
    return result;
  }
  
  private void shippmentRequestExcel(HttpServletResponse lResponse, Connection lConnection, String lOrderCode, String mailComment) {
    String template = (String) this.getServletContext().getRealPath("/docs/shippment_request.xls");
    SIExcelRWrite lWrite = new SIExcelRWrite(SIExcelConf.SIEXCEL_SHIPPMENT_REQUEST_INX, template);
    // 受注ヘッダ情報を取得
    SIOrder order = new SIOrder(lOrderCode);
    order.reset(lConnection);
    String branch = "";
    String charge = "";
    String price = "";
    String paymethod = "";
    try {
      branch = SIBGUtil.getDBParameter(lConnection, "branchtbl", "branchname", "branchcode", order.getCompanyCode());
      charge = SIBGUtil.getDBParameter(lConnection, "chargetbl", "chargename", "chargecode", order.getChargeCode());
      price = SIDBUtil.getFirstData(lConnection, "SELECT totalofprice+totaloffee+coalesce(discountfee,0)+totalofdeliveryfee+coalesce(discountdeliveryfee,0)-sumofdiscount-setdiscount-sumbypoint FROM ordersumvw WHERE ordercode="+SIDBUtil.SQL2Str(order.getOrderCode()));
      paymethod = SIDBUtil.getFirstData(lConnection, "SELECT paymethodname FROM ordersumvw WHERE ordercode="+SIDBUtil.SQL2Str(order.getOrderCode()));
    } catch (SIDBAccessException e) {}
    
    // 配送先取得
    UIRegOrderDelivery lOrderDelivery = new UIRegOrderDelivery();
    UIRegOrder lRegOrder = new UIRegOrder();
    lRegOrder.setOrderCode(lOrderCode);
    lRegOrder.reset(lConnection);
    Collection lOrderDeliveryColl = lRegOrder.getCollection(lConnection);// 配送先リスト
    Iterator lOrderDeliveryIta = lOrderDeliveryColl.iterator();
    
    // 商品リスト
    Statement lStatement = null;
    ResultSet lResultSet = null;
    ResultSet lResultSet2 = null;
    ResultSet lResultSet3 = null;
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append("SELECT c.cmdtyname,a.individualcode,b.branchname,a.orderallocationnumber ");
    lSqlBuf.append(",CASE c.deliverydate WHEN NULL THEN '指定なし' WHEN '2000-01-01' THEN '最短到着日' ELSE c.deliverydate::text END AS deliverydate ");
    lSqlBuf.append(",c.price,c.purchaseprice,c.processingexpence,b.branchcode,a.cmdtycode,c.deliverytime ");
    lSqlBuf.append(", c.setdetailflg "); // EDBTG003-00 elecs-tani add
    lSqlBuf.append("FROM orderallocationinfotbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("a,");
    lSqlBuf.append("branchtbl ").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("b,");
    lSqlBuf.append(SIConfig.SIVIEW_ORDER_DETAIL_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("c ");
    lSqlBuf.append("WHERE a.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode)).append(" ");
    lSqlBuf.append("AND a.branchcode=b.branchcode ");
    lSqlBuf.append("AND a.ordercode=c.ordercode ");
    lSqlBuf.append("AND a.cmdtycode=c.cmdtycode ");
    lSqlBuf.append("AND a.individualcode=c.individualcode ");
    lSqlBuf.append("AND a.detailcode=c.detailcode "); // EDBTG003-00 elecs-matsushima add
    lSqlBuf.append("ORDER BY a.branchcode,a.individualcode ");
    
    StringBuffer lSqlBuf2 = new StringBuffer();
    lSqlBuf2.append("SELECT cmdtyname,individualcode,amount ");
    lSqlBuf2.append(",CASE deliverydate WHEN NULL THEN '指定なし' WHEN '2000-01-01' THEN '最短到着日' ELSE deliverydate::text END AS deliverydate ");
    lSqlBuf2.append(",price,purchaseprice,processingexpence,deliverytime ");
    lSqlBuf2.append("FROM ").append(SIConfig.SIVIEW_ORDER_DETAIL_LATEST_NAME).append(" ");
    lSqlBuf2.append("WHERE ordercode=").append(SIDBUtil.SQL2Str(lOrderCode, " "));
    lSqlBuf2.append("AND NOT (cmdtycode in (SELECT cmdtycode FROM orderallocationinfotbl WHERE ordercode=").append(SIDBUtil.SQL2Str(lOrderCode)).append(") ");
    // EDBTG003-00 elecs-matsushima add start
    //lSqlBuf2.append("AND individualcode in (SELECT individualcode FROM orderallocationinfotbl WHERE ordercode=").append(SIDBUtil.SQL2Str(lOrderCode)).append(")) ");
    lSqlBuf2.append("AND individualcode||'_'||detailcode in (SELECT individualcode||'_'||detailcode FROM orderallocationinfotbl WHERE ordercode=").append(SIDBUtil.SQL2Str(lOrderCode)).append(")) ");
    // EDBTG003-00 elecs-matsushima add end
    
    StringBuffer lSqlBuf3 = new StringBuffer();
    lSqlBuf3.append("SELECT a.reservecode,a.cmdtyname,a.individualcode,a.amount,c.orderallocationnumber,d.branchname ");
    lSqlBuf3.append("FROM reserveordertbl AS a,orderreservetbl AS b,orderallocationinfotbl AS c,branchtbl AS d ");
    lSqlBuf3.append("WHERE b.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode)).append(" AND b.ordercode=c.ordercode ");
    lSqlBuf3.append("AND a.reservecode=b.reservecode AND a.reservebranch=c.branchcode AND a.reservebranch=d.branchcode ");
    lSqlBuf3.append("AND a.individualcode=c.individualcode AND a.amount > c.orderallocationnumber");
    
    log.debug("ALLOCATESQL:" + lSqlBuf.toString());
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      
      String purchaseprice = "";
      String processingexpence = "";
      String cost = "";
      String fee = "";
      
      // ヘッダー作成
      lWrite.makeSheet(0, SIExcelConf.getTitleName(SIExcelConf.SIEXCEL_SHIPPMENT_REQUEST_INX), 3);
      lWrite.writeHeaderItem(2, 2, lOrderCode, SIExcelConf.FIELD_NUMERIC_TYPE);// 受注番号
      lWrite.writeHeaderItem(3, 2, order.getInitDateTime(), SIExcelConf.FIELD_STRING_TYPE);// 受注日時
      lWrite.writeHeaderItem(4, 2, branch, SIExcelConf.FIELD_STRING_TYPE);// 担当支店
      lWrite.writeHeaderItem(5, 2, charge, SIExcelConf.FIELD_STRING_TYPE);// 担当者
      lWrite.writeHeaderItem(6, 2, paymethod, SIExcelConf.FIELD_STRING_TYPE);// 支払い方法
      lWrite.writeHeaderItem(7, 2, price+"円", SIExcelConf.FIELD_STRING_TYPE);// 支払い総額
      
      if (lOrderDeliveryIta.hasNext()) {
        lOrderDelivery = (UIRegOrderDelivery) lOrderDeliveryIta.next();
        lWrite.writeHeaderItem(2, 4, lOrderDelivery.getDeliveryAddressee(), SIExcelConf.FIELD_STRING_TYPE);// 配送先宛名
        lWrite.writeHeaderItem(3, 4, lOrderDelivery.getCompanyName(), SIExcelConf.FIELD_STRING_TYPE);// 配送先サロン
        lWrite.writeHeaderItem(4, 4, lOrderDelivery.getPostCode() + " " + lOrderDelivery.getAddress(), SIExcelConf.FIELD_STRING_TYPE);// 配送先住所
        lWrite.writeHeaderItem(5, 4, lOrderDelivery.getTel(), SIExcelConf.FIELD_STRING_TYPE);// 配送先TEL
      }
      
      int currentInx = 13;
      int overInx = 0;
      
      while (lResultSet.next()) {
        currentInx++;
        if (SIUtil.isNull(lResultSet.getString(7))) purchaseprice = "0";
        else purchaseprice = lResultSet.getString(7);
        if (SIUtil.isNull(lResultSet.getString(8))) processingexpence = "0";
        else processingexpence = lResultSet.getString(8);
        cost = SIUtil.add(purchaseprice, processingexpence);
        
        if (currentInx > 43) {
          lWrite.copyRows(currentInx - 1, currentInx);
          lWrite.writeDetailItem(currentInx, 0, String.valueOf(currentInx - 13), SIExcelConf.FIELD_NUMERIC_TYPE, false);// No（31番以降）
        }
        lWrite.writeDetailItem(currentInx, 1, lResultSet.getString(2), SIExcelConf.FIELD_STRING_TYPE, false);// 在庫コード
        // EDBTG003-00 elecs-tani add start
        // 添付品の場合頭に文言を付ける
        String cmdtyName = lResultSet.getString(1);
        if (lResultSet.getString(12).equals(SIConfig.SET_DETAIL_FLG_APPENDED)) {
          cmdtyName = SIConfig.SET_APPEND_NAME_PDF + cmdtyName;
        }
        // EDBTG003-00 elecs-tani add end
        // EDBTG003-00 elecs-tani mod start
//        lWrite.writeDetailItem(currentInx, 2, lResultSet.getString(1), SIExcelConf.FIELD_STRING_TYPE, false);// 商品名
        lWrite.writeDetailItem(currentInx, 2, cmdtyName, SIExcelConf.FIELD_STRING_TYPE, false);// 商品名
        // EDBTG003-00 elecs-tani mod end
        lWrite.writeDetailItem(currentInx, 3, lResultSet.getString(3), SIExcelConf.FIELD_STRING_TYPE, false);// 出荷店舗
        lWrite.writeDetailItem(currentInx, 4, lResultSet.getString(4), SIExcelConf.FIELD_NUMERIC_TYPE, false);// 出荷数
        lWrite.writeDetailItem(currentInx, 5, lResultSet.getString(6), SIExcelConf.FIELD_NUMERIC_TYPE, false);// 商品単価
        lWrite.writeDetailItem(currentInx, 6, cost, SIExcelConf.FIELD_NUMERIC_TYPE, false);// 商品原価
        lWrite.writeDetailItem(currentInx, 7, lResultSet.getString(5), SIExcelConf.FIELD_STRING_TYPE, false);// 配送希望日
        if (SIBGUtil.isOrderReserve(lConnection, lOrderCode, lResultSet.getString(10), lResultSet.getString(2), lResultSet.getString(9))) {
          lWrite.writeDetailItem(currentInx, 8, "予約品　" + SIFlagUtil.getFlagName(SIFlagConf.SIFLAG_DELIVERYTIME_FLG_INX,lResultSet.getString(11)), SIExcelConf.FIELD_STRING_TYPE, false);// 予約商品
        }else{
          lWrite.writeDetailItem(currentInx, 8, SIFlagUtil.getFlagName(SIFlagConf.SIFLAG_DELIVERYTIME_FLG_INX,lResultSet.getString(11)), SIExcelConf.FIELD_STRING_TYPE, false);// 希望時間帯
        }
      }
      SIDBUtil.close(lStatement, lResultSet);
      
      log.debug("NOSTOCKSQL:" + lSqlBuf2.toString());
      lStatement = lConnection.createStatement();
      lResultSet2 = lStatement.executeQuery(lSqlBuf2.toString());
      
      while (lResultSet2.next()) {
        currentInx++;
        if (SIUtil.isNull(lResultSet2.getString(6))) purchaseprice = "0";
        else purchaseprice = lResultSet2.getString(6);
        if (SIUtil.isNull(lResultSet2.getString(7))) processingexpence = "0";
        else processingexpence = lResultSet2.getString(7);
        cost = SIUtil.add(purchaseprice, processingexpence);
        
        if (currentInx > 43) {
          lWrite.copyRows(currentInx - 1, currentInx);
          lWrite.writeDetailItem(currentInx, 0, String.valueOf(currentInx - 13), SIExcelConf.FIELD_NUMERIC_TYPE, false);// No（31番以降）
        }
        lWrite.writeDetailItem(currentInx, 1, lResultSet2.getString(2), SIExcelConf.FIELD_STRING_TYPE, false);// 在庫コード
        lWrite.writeDetailItem(currentInx, 2, lResultSet2.getString(1), SIExcelConf.FIELD_STRING_TYPE, false);// 商品名
        lWrite.writeDetailItem(currentInx, 3, "受注発注商品", SIExcelConf.FIELD_STRING_TYPE, false);// 出荷店舗
        lWrite.writeDetailItem(currentInx, 4, lResultSet2.getString(3), SIExcelConf.FIELD_NUMERIC_TYPE, false);// 出荷数
        lWrite.writeDetailItem(currentInx, 5, lResultSet2.getString(5), SIExcelConf.FIELD_NUMERIC_TYPE, false);// 商品単価
        lWrite.writeDetailItem(currentInx, 6, cost, SIExcelConf.FIELD_NUMERIC_TYPE, false);// 商品原価
        lWrite.writeDetailItem(currentInx, 7, lResultSet2.getString(4), SIExcelConf.FIELD_STRING_TYPE, false);// 配送希望日
        lWrite.writeDetailItem(currentInx, 8, SIFlagUtil.getFlagName(SIFlagConf.SIFLAG_DELIVERYTIME_FLG_INX,lResultSet2.getString(8)), SIExcelConf.FIELD_STRING_TYPE, false);// 希望時間帯
      }
      SIDBUtil.close(lStatement, lResultSet2);
      
      // 数量変更した予約商品がある場合
      if (currentInx < 43) currentInx = 43;
      else overInx = currentInx - 43;
      currentInx = currentInx + 3;
      lStatement = lConnection.createStatement();
      lResultSet3 = lStatement.executeQuery(lSqlBuf3.toString());
      while (lResultSet3.next()) {
        currentInx++;
        if (currentInx > (51 + overInx)) {
          lWrite.copyRows(currentInx - 1, currentInx);
          lWrite.writeDetailItem(currentInx, 0, String.valueOf(currentInx - (47 + overInx)), SIExcelConf.FIELD_NUMERIC_TYPE, false);// No（6番以降）
        }
        lWrite.writeDetailItem(currentInx, 1, lResultSet3.getString(1), SIExcelConf.FIELD_NUMERIC_TYPE, false);// 予約番号
        lWrite.writeDetailItem(currentInx, 2, lResultSet3.getString(2), SIExcelConf.FIELD_STRING_TYPE, false);// 商品名
        lWrite.writeDetailItem(currentInx, 3, lResultSet3.getString(6), SIExcelConf.FIELD_STRING_TYPE, false);// 出荷店舗
        lWrite.writeDetailItem(currentInx, 4, lResultSet3.getString(5), SIExcelConf.FIELD_NUMERIC_TYPE, false);// 出荷数
        lWrite.writeDetailItem(currentInx, 5, lResultSet3.getString(4), SIExcelConf.FIELD_NUMERIC_TYPE, false);// 予約数
      }
      SIDBUtil.close(lStatement, lResultSet3);
      
      if (currentInx < (51 + overInx)) currentInx = (51 + overInx);
      currentInx = currentInx + 6;
      // フッター作成
      lWrite.writeDetailItem(currentInx, 0, mailComment, SIExcelConf.FIELD_STRING_TYPE, false);// 備考
      lWrite.writeDetailItem(currentInx + 6, 2, order.getCustName(), SIExcelConf.FIELD_STRING_TYPE, false);// 氏名
      lWrite.writeDetailItem(currentInx + 7, 2, "〒" + order.getPostCode() + " " + order.getAddress(), SIExcelConf.FIELD_STRING_TYPE, false);// 住所
      lWrite.writeDetailItem(currentInx + 8, 2, order.getStoreTel(), SIExcelConf.FIELD_STRING_TYPE, false);// 連絡先電話番号１
      lWrite.writeDetailItem(currentInx + 9, 2, order.getTel(), SIExcelConf.FIELD_STRING_TYPE, false);// 連絡先電話番号２
      lWrite.writeDetailItem(currentInx + 10, 2, order.getEmail(), SIExcelConf.FIELD_STRING_TYPE, false);// メール
      if (SIUtil.isNotNull(lRegOrder.getDiscountFee()) && !lRegOrder.getDiscountFee().equals("0")) {// 手数料調整額がある場合：手数料と調整額表示
        fee = NumberFormat.getNumberInstance().format(new Long(lRegOrder.getFeeIncTax())) + "円＋" + NumberFormat.getNumberInstance().format(new Long(lRegOrder.getDiscountFee()))
            + "円（手数料）";
      } else {
        if (SIUtil.isNotNull(lRegOrder.getFeeIncTax()) && !lRegOrder.getFeeIncTax().equals("0")) {
          fee = NumberFormat.getNumberInstance().format(new Long(lRegOrder.getFeeIncTax())) + "円";
        } else {
          fee = "0円";
        }
      }
      lWrite.writeDetailItem(currentInx + 11, 2, fee, SIExcelConf.FIELD_STRING_TYPE, false);// 手数料
      
      lWrite.makePage(currentInx + 11);
      lWrite.close(lResponse);
    } catch (SQLException sqle) {
      sqle.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    } finally {
      try {
        if (lStatement != null) lStatement.close();
      } catch (SQLException sqle) {}
    }
  }
  
  /**
   * <b>sendMail2</b> 出荷指示メール送信
   * 
   * @param request
   * @param lConnection DBへのコネクション
   * @param lOrderCode 受注コード
   * @param mailComment コメント
   * @return なし
   * @throws SIDBAccessException
   */
  public boolean sendMail2(HttpServletRequest lRequest, Connection lConnection, String lOrderCode, String mailComment, String fromMail,String[] cashParam) throws SIDuplicateKeyException,
      SIDBAccessException, SQLException {
    boolean lRes = true;
    boolean cashFlg = SIUtil.isNotNull(cashParam[0]);
    log.debug("出荷指示メール送信：" + lOrderCode);
    DecimalFormat exFormat = new DecimalFormat("00");
    
    // ログイン情報取得
    SILogin manLogin = SIHTMLUtil.getLogin(lRequest);
    Collection lOrderDeliveryColl = new ArrayList();
    ShippmentRequestHeader lHeader = new ShippmentRequestHeader();
    lHeader.orderCode = lOrderCode;
    lHeader.memo = mailComment;
    lHeader.daibikiBranchCode = cashParam[0];
    lHeader.daibikiPrice = cashParam[1];
    
    Iterator lOrderDeliveryIta;
    int lDeliveryInx = 0;
    boolean sendflg = true;
    // 受注ヘッダ情報を取得
    SIOrder order = new SIOrder(lOrderCode);
    order.reset(lConnection);
    
    // 配送先取得
    UIRegOrder lRegOrder = new UIRegOrder();
    UIRegOrderDelivery lOrderDelivery = new UIRegOrderDelivery();
    lRegOrder.setOrderCode(lOrderCode);
    lRegOrder.reset(lConnection);
    
    // 送信先リストを取得
    String[][] requestList = SIBGUtil.getRequestList2(lConnection, lOrderCode);
    
    // 担当支店メールアドレスを取得
    String[] chargeAddress = SIBGUtil.getRequestCCList(lConnection, lOrderCode);//担当支店、担当者アドレス
    
    String branch = SIBGUtil.getDBParameter(lConnection, "branchtbl", "branchname", "branchcode", order.getCompanyCode());
    String charge = SIBGUtil.getDBParameter(lConnection, "chargetbl", "chargename", "chargecode", order.getChargeCode());
    String keepmail = SIBGUtil.getDBParameter(lConnection, "orderlatestvw", "keepmaildate", "ordercode", lOrderCode);
    String paymethod = SIBGUtil.getDBParameter(lConnection, "orderlatestvw", "paymethodname", "ordercode", lOrderCode);
    String keepmsg = "未送信";
    if (SIUtil.isNull(branch)) branch = "未定";
    if (SIUtil.isNotNull(keepmail) && keepmail.length() > 9) {
      keepmsg = "送信済　" + keepmail.substring(0, 4) + "年" + keepmail.substring(5, 7) + "月" + keepmail.substring(8, 10) + "日";
    }
    
    boolean reMailFlg = false;
    reMailFlg = SIDBUtil.hasData(lConnection, "SELECT * FROM ordertbl WHERE ordercode='" + order.getOrderCode() + "' AND shippmentmaildate IS NOT NULL");
    
    Statement rsStatement = null;
    try {
      //出荷依頼データレコードロック
      StringBuffer sql = new StringBuffer();
      sql.append("SELECT * FROM shippmentrequesttbl ");
      sql.append("WHERE ordercode=").append(SIDBUtil.SQL2Str(lHeader.orderCode," "));
      sql.append("FOR UPDATE");
      rsStatement = lConnection.createStatement();
      rsStatement.execute(sql.toString());
      rsStatement.close();
    } catch (Exception ex) {
      throw new SIDBAccessException(ex);
    }finally{
      try {if (rsStatement!=null) rsStatement.close();}catch(SQLException sqle){}
    }
    
    // 出荷依頼送信履歴を無効化
    SIModifyRec lModifyRec = new SIModifyRec("ShippmentRequestTbl");
    lModifyRec.addCondition("orderCode", lHeader.orderCode);
    lModifyRec.add("enabledFlg", "0");
    lModifyRec.execute(lConnection);
    
    for (int i=0;i<requestList.length;i++) {
      lOrderDeliveryColl = lRegOrder.getCollection(lConnection, manLogin);// 配送先リスト
      lOrderDeliveryIta = lOrderDeliveryColl.iterator();
      
    // 1.メールテンプレートを取得
    SISendMail lSendMail = new SISendMail();
    
    try {
      lSendMail.setMailTemp(lConnection, order.getMailShopCode(lConnection, lOrderCode), SIConfig.SIMAIL_TEMPLATE_SHIPPMENT_REQUEST);
    } catch (SIFatalException e2) {
      e2.printStackTrace();
    }
    String reservemsg = "";
    if (SIBGUtil.isOrderReserve(lConnection, lOrderCode,requestList[i][0])) reservemsg = "※予約商品含む";
    if (reMailFlg) {
      lSendMail.appendMailTitle("【再送】", "【受注番号：" + order.getOrderCode() + "】" + reservemsg);
    } else {
      lSendMail.appendMailTitle("", "【受注番号：" + order.getOrderCode() + "】" + reservemsg);
    }
    
    // 2.メールの本文の作成(受注ヘッダー)
    StringBuffer lMailBuf = new StringBuffer();
    StringBuffer lCommentBuf = new StringBuffer();
    lMailBuf.append("\n-------------");
    lMailBuf.append("\n受注番号:").append(order.getOrderCode());
    lMailBuf.append("\n担当者:").append(branch + " " + charge);
    lMailBuf.append("\n支払方法:").append(paymethod);
    lMailBuf.append("\n取り置き依頼:").append(keepmsg);
    lMailBuf.append("\n");
    lMailBuf.append("\n-------------");
    
    if (lOrderDeliveryIta.hasNext()) {
      lDeliveryInx++;
      lOrderDelivery = (UIRegOrderDelivery) lOrderDeliveryIta.next();
      lMailBuf.append("\n配送先");
      lMailBuf.append("\n宛名:").append(lOrderDelivery.getDeliveryAddressee());
      lMailBuf.append("\nサロン名:").append(lOrderDelivery.getCompanyName());
      lMailBuf.append("\n住所:〒").append(lOrderDelivery.getPostCode()).append(" ");
      lMailBuf.append(lOrderDelivery.getAddress());
      lMailBuf.append("\nTEL:").append(lOrderDelivery.getTel());
      
      lHeader.deliveryName = lOrderDelivery.getDeliveryName();
      lHeader.postCode1 = lOrderDelivery.getPostCode1();
      lHeader.postCode2 = lOrderDelivery.getPostCode2();
      lHeader.address1 = lOrderDelivery.getAddress1();
      lHeader.address2 = lOrderDelivery.getAddress2();
      lHeader.address3 = lOrderDelivery.getAddress3();
      lHeader.deliveryAddressee = lOrderDelivery.getDeliveryAddressee();
      lHeader.companyName = lOrderDelivery.getCompanyName();
      lHeader.tel = lOrderDelivery.getTel();
      lHeader.fax = lOrderDelivery.getFax();
    }
    if (SIUtil.isNotNull(mailComment)||(cashFlg&&cashParam[0].equals(requestList[i][0]))) {
      lCommentBuf.append("\n==============");
      lCommentBuf.append("\n備考:");
      lCommentBuf.append("\n").append(mailComment);
      if (cashFlg&&cashParam[0].equals(requestList[i][0])) {
        if (SIUtil.isNotNull(mailComment)) lCommentBuf.append("\n");
        lCommentBuf.append("\n").append("【代金引換依頼】");
        lCommentBuf.append("\n").append("対応支店：").append(requestList[i][1]);
        lCommentBuf.append("\n").append("代引金額：").append(NumberFormat.getNumberInstance().format(Integer.parseInt(cashParam[1]))).append("円");
      }
      lCommentBuf.append("\n==============");
      lMailBuf.append(lCommentBuf);
    }
    if (SIUtil.isNotNull(requestList[i][3])) {
    lMailBuf.append("\n（").append(requestList[i][3]).append("出荷分）");
    }else {
      lMailBuf.append("\n（").append(requestList[i][1]).append("出荷分）");
    }
    lSendMail.appendContent1(lMailBuf.toString());
    
    // 納期枝番リスト作成
    Statement lStatement = null;
    ResultSet lResultSet = null;
    ResultSet lResultSet2 = null;
    ResultSet lResultSet3 = null;
    
    StringBuffer appBuf = new StringBuffer();
    
    appBuf.append("SELECT DISTINCT CASE d.deliverydate WHEN '2000-01-01' THEN NULL ELSE d.deliverydate::text END AS dd ");
    appBuf.append(",CASE WHEN d.deliverydate='2000-01-01' OR d.deliverydate IS NULL THEN 0 ELSE 1 END AS inx ");
    appBuf.append("FROM orderdetaillatestvw d,orderallocationinfotbl a,cmdtymtbl c ");
    appBuf.append("WHERE d.ordercode=a.ordercode AND d.individualcode=a.individualcode AND a.cmdtycode=c.cmdtycode ");
    appBuf.append("AND d.detailcode = a.detailcode "); // EDBTG003-00 elecs-matsushima add
    appBuf.append("AND a.branchcode=").append(SIDBUtil.SQL2Str(requestList[i][0], " "));
    if (SIUtil.isNotNull(requestList[i][2])&&"999".equals(requestList[i][0])) {
      appBuf.append("AND c.storagecode=").append(SIDBUtil.SQL2Str(requestList[i][2], " "));
    }
    appBuf.append("AND d.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode, " "));
    appBuf.append("ORDER BY inx,dd");
    
    String[] appendData = new String[2];
    Collection appendDataList = new ArrayList();
    
    lStatement = lConnection.createStatement();
    lResultSet2 = lStatement.executeQuery(appBuf.toString());
    
    int apc=1;
    while (lResultSet2.next()) {
      appendData = new String[2];
      appendData[0] = lResultSet2.getString("dd");
      appendData[1] = String.valueOf(apc+1);
      appendDataList.add(appendData);
      apc++;
    }
    SIDBUtil.close(lStatement, lResultSet2);
    
    // 3.出荷商品リストを取得
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append("SELECT a.cmdtycode,a.individualcode,b.branchname,a.orderallocationnumber,c.detailcode ");
    lSqlBuf.append(",CASE c.deliverydate WHEN '2000-01-01' THEN '最短到着日' ELSE c.deliverydate::text END AS deliverydatetxt ");
    lSqlBuf.append(",c.price,c.purchaseprice,c.processingexpence,b.branchcode,e.makername ");
    lSqlBuf.append(",d.othername,f.setitem1,f.setitem2,c.deliverydate,c.deliverytime ");
    lSqlBuf.append("FROM orderallocationinfotbl AS a, ");
    lSqlBuf.append("branchtbl AS b, ");
    lSqlBuf.append("orderdetaillatestvw c ");
    //lSqlBuf.append(",cmdtymtbl d, makertbl e ");
    lSqlBuf.append(",cmdtymtbl d, makertbl e,individualtbl f ");
    lSqlBuf.append("WHERE a.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode)).append(" ");
    lSqlBuf.append("AND a.branchcode=").append(SIDBUtil.SQL2Str(requestList[i][0]," "));
    if (SIUtil.isNotNull(requestList[i][2])&&"999".equals(requestList[i][0])) {
      appBuf.append("AND d.storagecode=").append(SIDBUtil.SQL2Str(requestList[i][2], " "));
    }
    lSqlBuf.append("AND a.branchcode=b.branchcode ");
    lSqlBuf.append("AND a.ordercode=c.ordercode ");
    lSqlBuf.append("AND a.cmdtycode=c.cmdtycode ");
    lSqlBuf.append("AND a.individualcode=c.individualcode ");
    lSqlBuf.append("AND a.individualcode=f.individualcode ");
    lSqlBuf.append("AND a.cmdtycode=d.cmdtycode AND d.makercode = e.makercode ");
    // EDBTG003-00 elecs-matsushima add start
    lSqlBuf.append("AND a.detailcode=c.detailcode ");
    lSqlBuf.append("AND c.setdetailflg <> ").append(SIDBUtil.SQL2Str(SIConfig.SET_DETAIL_FLG_OPTION, " "));
    // EDBTG003-00 elecs-matsushima add end
    lSqlBuf.append("ORDER BY a.individualcode ");
    
    log.debug("ALLOCATESQL:" + lSqlBuf.toString());
    lStatement = lConnection.createStatement();
    lResultSet = lStatement.executeQuery(lSqlBuf.toString());
    
    // 4.メールの本文の作成(出荷商品情報)
    StringBuffer lMailBuf1 = new StringBuffer();
    
    lMailBuf1.append("\n-------------");
    lMailBuf1.append("\n出荷商品一覧 ").append("(対応支店:").append(branch).append(")");
    lMailBuf1.append("\n");
    while (lResultSet.next()) {
      lMailBuf1.append("\n出荷数量:").append(lResultSet.getString(4));
      if (SIBGUtil.isOrderReserve(lConnection, lOrderCode, lResultSet.getString(1), lResultSet.getString(2), lResultSet.getString(9))) {
        lMailBuf1.append("\n在庫コード:").append(lResultSet.getString(2)).append("  【予約商品】");
      } else {
        lMailBuf1.append("\n在庫コード:").append(lResultSet.getString(2));
      }
      String lCmdtyName = SIBGUtil.getCmdtyFullName(lConnection, lResultSet.getString(1), lResultSet.getString(2));
      lMailBuf1.append("\n商品名:").append(lCmdtyName);
      lMailBuf1.append("\nブランド:").append(lResultSet.getString("makername"));
      /* 型式他追加
      lMailBuf1.append("\n型式:");
      if (SIUtil.isNotNull(lResultSet.getString("othername"))) lMailBuf1.append(lResultSet.getString("othername"));
      lMailBuf1.append("\nピッキング時注意事項:");
      if (SIUtil.isNotNull(lResultSet.getString("setitem1"))) lMailBuf1.append(lResultSet.getString("setitem1"));
      lMailBuf1.append("\n同梱品情報:");
      if (SIUtil.isNotNull(lResultSet.getString("setitem2"))) lMailBuf1.append(lResultSet.getString("setitem2"));
      */
      lMailBuf1.append("\n商品単価:").append(NumberFormat.getNumberInstance().format(lResultSet.getInt("price")));
      if(SIUtil.isNotNull(lResultSet.getString("deliverydatetxt"))){
        lMailBuf1.append("\n配送希望日:").append(lResultSet.getString("deliverydatetxt"));
      }else{
        lMailBuf1.append("\n配送希望日:指定なし");
      }
      if(SIUtil.isNotNull(lResultSet.getString("deliverytime"))){
        lMailBuf1.append("\n希望時間帯:").append(SIFlagUtil.getFlagName(SIFlagConf.SIFLAG_DELIVERYTIME_FLG_INX, lResultSet.getString("deliverytime")));
      }else{
        lMailBuf1.append("\n希望時間帯:指定なし");
      }
      lMailBuf1.append("\n");
      
      ShippmentRequestDetail lDetail = new ShippmentRequestDetail();
      lDetail.branchCode = requestList[i][0];
      lDetail.individualCode = lResultSet.getString(2);
      lDetail.cmdtyName = lCmdtyName;
      lDetail.deliveryDate = lResultSet.getString("deliverydate");
      lDetail.deliveryTime = lResultSet.getString("deliverytime");
      lDetail.amount = lResultSet.getString(4);
      lDetail.setItem1 = lResultSet.getString("setItem1");
      lDetail.setItem2 = lResultSet.getString("setItem2");
      lDetail.detailCode = lResultSet.getString("detailcode");
      
      if (appendDataList.size()==1) {//単納期の時
        lDetail.outputCode = "01";
      } else if (SIUtil.isNull(lDetail.deliveryDate) || "2000-01-01".equals(lDetail.deliveryDate)) {//配送希望日NULLのとき、最短（2000-01-01）のとき
        lDetail.outputCode = "02";
      } else {//配送希望日がNULL、最短でないとき
        Iterator appendDataListIte = appendDataList.iterator();
        while(appendDataListIte.hasNext()){
          String[] data = (String[])appendDataListIte.next();
          if (lDetail.deliveryDate.equals(data[0])) 
            lDetail.outputCode = exFormat.format(Integer.parseInt(data[1]));
        }
      }
      
      lHeader.itemColl.add(lDetail);
    }
    SIDBUtil.close(lStatement, lResultSet);
    
    lMailBuf = new StringBuffer();
    
    lMailBuf.append("\n-------------");
    lMailBuf.append("\nお客さま情報");
    lMailBuf.append("\n氏名:").append(order.getCustName());
    lMailBuf.append("\n住所:〒").append(order.getPostCode()).append(" ");
    lMailBuf.append(order.getAddress());
    lMailBuf.append("\n連絡先電話番号１:").append(order.getStoreTel());
    lMailBuf.append("\n連絡先電話番号２:").append(order.getTel());
    lMailBuf.append("\nE-Mail:").append(order.getEmail());
    lMailBuf.append("\n受注日時:").append(order.getInitDateTime());
    if (SIUtil.isNotNull(lRegOrder.getDiscountFee()) && !lRegOrder.getDiscountFee().equals("0")) {// 手数料調整額がある場合：手数料と調整額表示
      lMailBuf.append("\n手数料:").append(NumberFormat.getNumberInstance().format(new Long(lRegOrder.getFeeIncTax()))).append(lRegOrder.getFeeTaxName()).append("円");
      lMailBuf.append("\n手数料調整額:").append(NumberFormat.getNumberInstance().format(new Long(lRegOrder.getDiscountFee()))).append(lRegOrder.getFeeTaxName()).append("円");
    } else {
      if (SIUtil.isNotNull(lRegOrder.getFeeIncTax()) && !lRegOrder.getFeeIncTax().equals("0")) {
        lMailBuf.append("\n手数料:").append(NumberFormat.getNumberInstance().format(new Long(lRegOrder.getFeeIncTax()))).append(lRegOrder.getFeeTaxName()).append("円");
      }
    }
    lMailBuf.append("\n");
    lMailBuf.append("\n-------------");
    
    StringBuffer lSqlBuf3 = new StringBuffer();
    lSqlBuf3.append("SELECT a.reservecode,a.cmdtycode,a.individualcode,a.amount,c.orderallocationnumber,d.branchname ");
    lSqlBuf3.append("FROM reserveordertbl AS a,orderreservetbl AS b,orderallocationinfotbl AS c,branchtbl AS d ");
    lSqlBuf3.append("WHERE b.ordercode=").append(SIDBUtil.SQL2Str(lOrderCode)).append(" AND b.ordercode=c.ordercode ");
    lSqlBuf3.append("AND a.reservecode=b.reservecode AND a.reservebranch=c.branchcode AND a.reservebranch=d.branchcode ");
    lSqlBuf3.append("AND a.reservebranch=").append(SIDBUtil.SQL2Str(requestList[i][0]," "));
    lSqlBuf3.append("AND a.individualcode=c.individualcode AND a.amount > c.orderallocationnumber");
    
    lStatement = lConnection.createStatement();
    lResultSet3 = lStatement.executeQuery(lSqlBuf3.toString());
    
    while (lResultSet3.next()) {
      if (lResultSet3.isFirst()) lMailBuf.append("\n★★★数量変更された予約商品が存在します★★★");
      lMailBuf.append("\n出荷数量:").append(lResultSet3.getString(5));
      lMailBuf.append("\n予約数量:").append(lResultSet3.getString(4));
      lMailBuf.append("\n予約番号:").append(lResultSet3.getString(1));
      lMailBuf.append("\n商品名:").append(SIBGUtil.getCmdtyFullName(lConnection, lResultSet3.getString(2), lResultSet3.getString(3)));
      lMailBuf.append("\n");
      if (lResultSet3.isLast()) lMailBuf.append("\n-------------");
    }
    SIDBUtil.close(lStatement, lResultSet3);
    
    lMailBuf.append("\n");
    lMailBuf1.append(lMailBuf);
    
    lSendMail.appendContent2(lMailBuf1.toString());
    
    // 5.出荷指示メール送信日時を更新
    SIDateTime lDateTime = new SIDateTime();
    SIModifyRec lRec = new SIModifyRec(SIConfig.SITABLE_ORDER_NAME);
    lRec.addCondition("orderCode", lOrderCode);// 受注コード
    lRec.addCondition("enabledflg", "1");
    lRec.add("shippmentMailDate", lDateTime.getFullDateTime());// 現在時刻
    lRec.execute(lConnection);
    
    lHeader.sendMailDatetime = lDateTime.getFullDateTime();
    
    // 6.出荷指示送信内容をDB登録
    Iterator requestDetailIte = lHeader.itemColl.iterator();
    while (requestDetailIte.hasNext()) {
      SIInsertRec lInsertRec = new SIInsertRec("ShippmentRequestTbl");
      ShippmentRequestDetail lDetail = (ShippmentRequestDetail) requestDetailIte.next();
      
      lInsertRec.add("orderCode", lHeader.orderCode);
      lInsertRec.add("sendmailDatetime", lHeader.sendMailDatetime);
      lInsertRec.add("deliveryName", lHeader.deliveryName);
      lInsertRec.add("deliveryAddressee", lHeader.deliveryAddressee);
      lInsertRec.add("postCode1", lHeader.postCode1);
      lInsertRec.add("postCode2", lHeader.postCode2);
      lInsertRec.add("address1", lHeader.address1);
      lInsertRec.add("address2", lHeader.address2);
      lInsertRec.add("address3", lHeader.address3);
      lInsertRec.add("companyName", lHeader.companyName);
      lInsertRec.add("tel", lHeader.tel);
      lInsertRec.add("fax", lHeader.fax);
      lInsertRec.add("memo", lHeader.memo);
      lInsertRec.add("daibikiBranchCode", lHeader.daibikiBranchCode);
      lInsertRec.add("daibikiPrice", lHeader.daibikiPrice);
      
      lInsertRec.add("branchCode", lDetail.branchCode);
      lInsertRec.add("individualCode", lDetail.individualCode);
      lInsertRec.add("cmdtyName", lDetail.cmdtyName);
      lInsertRec.add("amount", lDetail.amount);
      lInsertRec.add("deliverydate", lDetail.deliveryDate);
      lInsertRec.add("deliverytime", lDetail.deliveryTime);
      lInsertRec.add("setItem1", lDetail.setItem1);
      lInsertRec.add("setItem2", lDetail.setItem2);
      lInsertRec.add("outputorderCode", lHeader.orderCode + "-" + lDetail.outputCode);
      lInsertRec.add("detailCode", lDetail.detailCode);
      
      lInsertRec.execute(lConnection);
    }
    lHeader.itemColl = new ArrayList();
    // 8.送信開始
    try {
      String[] toAddress = new String[1];
      toAddress[0] = requestList[i][4];
      lSendMail.setFromMailAddress(fromMail);
      lSendMail.setToMailAddress(toAddress);
      lSendMail.setCCMailAddress(chargeAddress);
      sendflg = lSendMail.execute();
      lRes = sendflg && lRes;
    } catch (/*SIFatal*/Exception e) {
      lRes = false;
      e.printStackTrace();
    }
    //代引きメールの時は代引支店の時に受注メモ追記
    if (cashFlg&&cashParam[0].equals(requestList[i][0])){
      lRec = new SIModifyRec(SIConfig.SITABLE_ORDER_NAME);
      lRec.addCondition("orderCode", lOrderCode);
      lRec.addCondition("enabledflg", "1");
      lRec.add("memo", new SISpcType("coalesce(memo,'') || '\r\n出荷依頼送信："+lDateTime.getFullDateTime()+"' ||" + SIDBUtil.SQL2Str(lCommentBuf.toString())));
      lRec.execute(lConnection);
    //代引メールでないときは最初の送信時に受注メモ追記
    } else if (!cashFlg&&i==0){
      lRec = new SIModifyRec(SIConfig.SITABLE_ORDER_NAME);
      lRec.addCondition("orderCode", lOrderCode);
      lRec.addCondition("enabledflg", "1");
      if (SIUtil.isNull(lCommentBuf.toString())){
        lRec.add("memo", new SISpcType("coalesce(memo,'') || '\r\n出荷依頼送信："+lDateTime.getFullDateTime()+"'"));
      }else{
        lRec.add("memo", new SISpcType("coalesce(memo,'') || '\r\n出荷依頼送信："+lDateTime.getFullDateTime()+"' ||" + SIDBUtil.SQL2Str(lCommentBuf.toString())));
      }
      lRec.execute(lConnection);
    }
    }
    // メールはロールバックできないので１件コミット
    if (sendflg) {
      try {
        lConnection.commit();
      } catch (SQLException sqle) {
        throw new SIDBAccessException(sqle);
      }
    } else {
      try {
        lConnection.rollback();
      } catch (SQLException sqle) {
        throw new SIDBAccessException(sqle);
      }
    }
    log.debug("sendmail:" + lRes);
    return lRes;
  }
  
  /**
   * <b>produceCSVFile</b> CSVファイルを作成します。
   * 
   * @param response クライアントまでのresponse
   * @param lConnection DBへのコネクション
   * @param lListCond CSVファイルに出力するデータセット
   * @return なし
   * @throws なし
   */
  private void produceCSVFile(SILogin lLogin, HttpServletResponse response, Connection lConnection, UIOrderListCond lListCond) {
    // CSVファイル名の作成
    String lCsvFileName = SICSVConf.getCsvFileName(SICSVConf.SICSV_ORDER_INX);
    SICSVWrite lCsv = new SICSVWrite(lCsvFileName);
    
    // SQL文の作成
    StringBuffer lSqlBuf = new StringBuffer();
    lSqlBuf.append("SELECT aa.OrderCode,");
    lSqlBuf.append("bb.DeliveryCode,");
    lSqlBuf.append("cc.DetailCode,");
    lSqlBuf.append("cc.ShopCode,");
    lSqlBuf.append("cc.CmdtyCode,");
    lSqlBuf.append("cc.IndividualCode, ");
    lSqlBuf.append("cc.ShopName,");
    lSqlBuf.append("cc.CmdtyName,");
    lSqlBuf.append("cc.Amount,");
    lSqlBuf.append("cc.OrgPrice,");
    //lSqlBuf.append("cc.Price,");
    lSqlBuf.append("cc.Price*cc.Amount AS price,");
    lSqlBuf.append("(CASE cc.TaxFlg WHEN '0' THEN '" + SIFlagConf.SIFLAG_TAX_NAME[2][0] + "' ");
    lSqlBuf.append("WHEN '1' THEN '" + SIFlagConf.SIFLAG_TAX_NAME[1][0] + "' ");
    lSqlBuf.append("ELSE '" + SIFlagConf.SIFLAG_TAX_NAME[0][0] + "' END) AS TaxFlg,");
    lSqlBuf.append("cc.TaxRate AS c_TaxRate,");
    lSqlBuf.append("cc.CmdtySize,");
    lSqlBuf.append("cc.DeliveryFee, ");
    lSqlBuf.append("aa.DiscountDeliveryFee, ");
    lSqlBuf.append("cl.ColorCode, ");
    lSqlBuf.append("cl.ColorName, ");
    lSqlBuf.append("aa.CustCode,");
    lSqlBuf.append("(CASE WHEN aa.CustCompanyFlg='0' THEN '個人' ELSE '法人' END) AS a_CustCompanyFlg,");
    lSqlBuf.append("aa.CustName,");
    lSqlBuf.append("aa.CustPronName,");
    lSqlBuf.append("aa.Email,");
    lSqlBuf.append("aa.OrderAddressee,");
    lSqlBuf.append("aa.PostCode1||'-'||aa.PostCode2 AS a_PostCode,");
    lSqlBuf.append("aa.Address1 AS a_Address1,");
    lSqlBuf.append("aa.Address2 AS a_Address2,");
    lSqlBuf.append("aa.Address3 AS a_Address3,");
    lSqlBuf.append("aa.CompanyName AS a_CompanyName,");
    lSqlBuf.append("aa.Job, ");
    lSqlBuf.append("aa.StoreTel, ");
    lSqlBuf.append("aa.Tel AS a_Tel,");
    lSqlBuf.append("aa.Fax,");
    lSqlBuf.append("aa.PayMethodName,");
    lSqlBuf.append("(CASE aa.PaymentFlg WHEN '0' THEN '先払い' ");
    lSqlBuf.append("WHEN '1' THEN '後払い' ");
    lSqlBuf.append("WHEN '2' THEN '代金引換' ");
    lSqlBuf.append("WHEN '3' THEN '不要(全額Point等)' ");
    lSqlBuf.append("WHEN '4' THEN 'クレジット支払い' ");
    lSqlBuf.append("WHEN '5' THEN 'コンビニ決済' ");
    lSqlBuf.append("ELSE '' END) AS PaymentFlg,");
    lSqlBuf.append("aa.Fee, ");
    lSqlBuf.append("aa.DiscountFee, ");
    lSqlBuf.append("aa.SumOfDiscount +aa.SetDiscount AS sumofdiscount,");
    lSqlBuf.append("aa.SumByPoint,");
    lSqlBuf.append("(CASE aa.Status WHEN '0' THEN 'キャンセル' WHEN '2' THEN '返品' ELSE '' END) AS Status,");
    lSqlBuf.append("aa.ContactMsg,");
    lSqlBuf.append("aa.Memo,");
    lSqlBuf.append("bb.DeliveryName,");
    lSqlBuf.append("bb.DeliveryAddressee,");
    lSqlBuf.append("(CASE WHEN bb.HomeFlg='0' THEN '自宅以外' ELSE '自宅' END) AS HomeFlg,");
    lSqlBuf.append("(CASE WHEN bb.CustCompanyFlg='0' THEN '個人' ELSE '法人' END) AS b_CustCompanyFlg,");
    lSqlBuf.append("bb.PostCode1||'-'||bb.PostCode2 AS b_PostCode,");
    lSqlBuf.append("bb.Address1 AS b_Address1,");
    lSqlBuf.append("bb.Address2 AS b_Address2,");
    lSqlBuf.append("bb.Address3 AS b_Address3,");
    lSqlBuf.append("bb.CompanyName AS b_CompanyName,");
    lSqlBuf.append("bb.Tel AS b_Tel,");
    lSqlBuf.append("cc.DeliveryDate,");
    lSqlBuf.append("bb.DeliveryTimeFrom||'-'||bb.DeliveryTimeTo AS DeliveryTime,");
    lSqlBuf.append("bb.DeliveryTypeName,");
    lSqlBuf.append("aa.InitDateTime,");
    lSqlBuf.append("aa.UpdateDateTime,");
    lSqlBuf.append("aa.ReceiptDate,");
    lSqlBuf.append("cc.ShippmentDate, ");
    lSqlBuf.append("br.BranchCode, ");
    lSqlBuf.append("br.BranchName, ");
    lSqlBuf.append("ch.ChargeCode, ");
    lSqlBuf.append("ch.ChargeName, ");
    lSqlBuf.append("(CASE aa.OrderRoute ");
    for (int i=0;i<SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME.length;i++) {
      lSqlBuf.append(" WHEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][1]));
      lSqlBuf.append(" THEN ").append(SIDBUtil.SQL2Str(SIFlagConf.SIFLAG_ORDER_ROUTE_INX_NAME[i][0]));
    }
    lSqlBuf.append(" ELSE '' END) AS OrderRoute,");
    /*
    lSqlBuf.append("(CASE aa.OrderRoute WHEN '0' THEN 'EC' ");
    lSqlBuf.append(" WHEN '1' THEN '来店' ");
    lSqlBuf.append(" WHEN '2' THEN '電話' ");
    lSqlBuf.append(" WHEN '3' THEN 'メール' ");
    lSqlBuf.append(" WHEN '4' THEN 'FAX' ");
    lSqlBuf.append(" WHEN '5' THEN 'その他' ");
    lSqlBuf.append(" WHEN '6' THEN '携帯' ");
    lSqlBuf.append(" WHEN '7' THEN '楽天' ");
    lSqlBuf.append(" WHEN '8' THEN 'スマートフォン' ");
    lSqlBuf.append(" WHEN '9' THEN 'イベント' ");
    lSqlBuf.append(" WHEN 'A' THEN 'イベントFAX' ");
    lSqlBuf.append(" WHEN 'B' THEN 'ELG' ");
    lSqlBuf.append(" WHEN 'C' THEN 'アプリ' ");
    lSqlBuf.append(" ELSE '' END) AS OrderRoute,");
    */
    lSqlBuf.append("cc.ProcessingExpence, ");
    lSqlBuf.append("cc.PurchasePrice, ");
    lSqlBuf.append("aa.UpdateMallShopCode, ");
    lSqlBuf.append("aa.UpdateUserCode, ");
    lSqlBuf.append("aa.syoukei, ");
    lSqlBuf.append("aa.goukei ");
    lSqlBuf.append("FROM (SELECT a.*,b.deliveryedflg,b.nodeliveryflg,b.totalofprice,b.totalofdeliveryfee,b.totaloffee ");
    lSqlBuf.append(",b.totaloffee+b.totalofdeliveryfee+b.discountfee+b.discountdeliveryfee+b.totalofprice AS syoukei ");
    lSqlBuf.append(",b.totaloffee+b.totalofdeliveryfee+b.discountfee+b.discountdeliveryfee+b.totalofprice-b.sumofdiscount-b.setdiscount-b.sumbypoint AS goukei ");
    lSqlBuf.append(" FROM OrderLatestVW a,OrderSumVW b WHERE a.ordercode=b.ordercode) aa ");
    lSqlBuf.append("LEFT OUTER JOIN branchtbl br ON aa.branchcode=br.branchcode ");
    lSqlBuf.append("LEFT OUTER JOIN chargetbl ch ON aa.chargecode=ch.chargecode ");
    lSqlBuf.append("INNER JOIN paymentheadertbl pay ON aa.ordercode=pay.ordercode ");
    lSqlBuf.append(",orderdeliverytbl AS bb,orderdetailtbl AS cc,custtbl AS cust ");
    lSqlBuf.append(",(SELECT c.cmdtycode,c.colorcode,l.colorname FROM cmdtymtbl c,colortbl l WHERE c.colorcode=l.colorcode) cl ");
    
    if (lListCond.isShippmentFlg()) {
      lSqlBuf.append(",(SELECT DISTINCT ordercode FROM orderdetaillatestvw WHERE 1=1 ");
      if (SIUtil.isNotNull(lListCond.getShippmentDateFrom())) lSqlBuf.append("AND shippmentdate >= ").append(SIDBUtil.SQL2Str(lListCond.getShippmentDateFrom()," "));
      if (SIUtil.isNotNull(lListCond.getShippmentDateTo())) lSqlBuf.append("AND shippmentdate <= ").append(SIDBUtil.SQL2Str(lListCond.getShippmentDateTo() + " 23:59:59"," "));
      if (SIUtil.isNotNull(lListCond.getShippmentDateFrom())||SIUtil.isNotNull(lListCond.getShippmentDateTo())) lSqlBuf.append("AND shippmentdate IS NOT NULL ");
      if (SIUtil.isNull(lListCond.getDeliveryDateTypeFlg())) {
        if (SIUtil.isNotNull(lListCond.getDeliveryDateFrom())) lSqlBuf.append("AND deliverydate >= ").append(SIDBUtil.SQL2Str(lListCond.getDeliveryDateFrom()," "));
        if (SIUtil.isNotNull(lListCond.getDeliveryDateTo())) lSqlBuf.append("AND deliverydate <= ").append(SIDBUtil.SQL2Str(lListCond.getDeliveryDateTo() + " 23:59:59"," "));
        if (SIUtil.isNotNull(lListCond.getDeliveryDateFrom())||SIUtil.isNotNull(lListCond.getDeliveryDateTo())) lSqlBuf.append("AND deliverydate != '2000-01-01' AND deliverydate IS NOT NULL ");
      } else if (lListCond.getDeliveryDateTypeFlg().equals("1")) {//最短出荷
        lSqlBuf.append("AND deliverydate = '2000-01-01' ");
      } else if (lListCond.getDeliveryDateTypeFlg().equals("2")) {//指定なし
        lSqlBuf.append("AND deliverydate IS NULL ");
      }
      lSqlBuf.append(") AS ship ");
      lSqlBuf.append("WHERE aa.ordercode=bb.ordercode AND aa.ordercode=cc.ordercode AND ship.ordercode=aa.ordercode ");
    } else {
      lSqlBuf.append("WHERE aa.ordercode=bb.ordercode AND aa.ordercode=cc.ordercode ");
    }
    
    lSqlBuf.append("AND bb.deliverycode = cc.deliverycode AND aa.custcode=cust.custcode AND cc.cmdtycode=cl.cmdtycode ");
    lSqlBuf.append("AND aa.orderbranchcode=bb.orderbranchcode AND aa.orderbranchcode=cc.orderbranchcode ");
    if (lLogin.isShop()) {
      lSqlBuf.append("AND cc.ShopCode=" + SIDBUtil.SQL2Str(lLogin.getMallShopCode(), " "));
    }
    // 検索条件
    lSqlBuf.append(lListCond.getConditionSQL());
    // ソート
    lSqlBuf.append(" Order by aa.OrderCode,bb.DeliveryCode,cc.DetailCode ");
    
    // SQL文の設定
    lCsv.setSqlStatement(lSqlBuf.toString());
    // タイトルの設定
    lCsv.setCsvTitleAndField(SICSVConf.getTitleAndFieldName(SICSVConf.SICSV_ORDER_INX));
    if (SIDBMultiConf.SIDB_CURRENT_INX == SIDBMultiConf.SIDB_POSTGRESQL_INX) {} else {
      lCsv.setFieldType(SICSVConf.getFieldType(SICSVConf.SICSV_ORDER_INX));
    }
    // CSVファイルの出力
    lCsv.execute(lConnection, response);
  }
  
  private void refreshOrder(HttpServletRequest lRequest, Connection lConnection, SIURLParameter lUrlParam) {// 7.1.1 ST0236 修正
    SILogin lLogin = SIHTMLUtil.getLogin(lRequest);
    
    HttpSession session = lRequest.getSession(true);// セッションの取得
    
    // 再リセット
    UIRegOrder refreshRegOrder = new UIRegOrder();
    refreshRegOrder.init(lRequest, lUrlParam);// 7.1.1 ST0236 修正
    if (lLogin.isShop()) {
      // 7.2.0 ST1030 修正 ここから
      StringBuffer lSqlBuf = new StringBuffer("SELECT OrderCode FROM ").append(SIConfig.SIVIEW_ORDER_DETAIL_LATEST_NAME).append(" WHERE OrderCode=");
      // StringBuffer lSqlBuf=new StringBuffer("SELECT OrderCode FROM OrderDetailTbl WHERE OrderCode=");
      // 7.2.0 ST1030 修正 ここまで
      lSqlBuf.append(SIDBUtil.SQL2Str(refreshRegOrder.getOrderCode())).append(" AND ShopCode=");
      lSqlBuf.append(SIDBUtil.SQL2Str(lLogin.getMallShopCode()));
      try {
        if (SIDBUtil.hasData(lConnection, lSqlBuf.toString())) {
          refreshRegOrder.reset(lRequest, lConnection);
          session.setAttribute(SIConfig.SISESSION_MAN_ORDER_EDIT_NAME, refreshRegOrder);
        } else {
          session.removeAttribute(SIConfig.SISESSION_MAN_ORDER_EDIT_NAME);
        }
      } catch (SIDBAccessException e) {
        e.printStackTrace();
        session.removeAttribute(SIConfig.SISESSION_MAN_ORDER_EDIT_NAME);
      }
    } else if (lLogin.isMall()) {
      refreshRegOrder.reset(lRequest, lConnection);
      session.setAttribute(SIConfig.SISESSION_MAN_ORDER_EDIT_NAME, refreshRegOrder);
    }
  }
  
  /**
   * <b>changePageHeader</b> 選択した受注コードリストのPDFファイルを一括に作成します。
   * 
   * @param lDocument
   * @param maker
   * @param lChange
   * @return なし
   * @throws なし
   */
  
  private void changePageHeader(Document lDocument, SIMaker maker, boolean lChange) {
    try {
      SIDateTime lDateTime = new SIDateTime();
      if (!lChange) lDocument.newPage();
      
      // 1.発行日
      Table lPdfTbl = new Table(1);
      lPdfTbl.setAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultHorizontalAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultVerticalAlignment(Element.ALIGN_MIDDLE);
      lPdfTbl.setWidth(SIPDFConf.SISTOCK_TABLE_WIDTH_HEADER);
      lPdfTbl.setPadding(1);
      lPdfTbl.setBorder(Rectangle.NO_BORDER);
      
      Cell lCell;
      lCell = new Cell(new Paragraph("発行日:" + lDateTime.getFullDate(), SIPDFConf.SIFONT_GOTHIC7N));
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      lDocument.add(lPdfTbl);
      
      // 2.タイトル
      lPdfTbl = new Table(1);
      lPdfTbl.setAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultHorizontalAlignment(Element.ALIGN_CENTER);
      lPdfTbl.setDefaultVerticalAlignment(Element.ALIGN_MIDDLE);
      lPdfTbl.setWidth(SIPDFConf.SISTOCK_TABLE_WIDTH_NOR);
      lPdfTbl.setPadding(1);
      lPdfTbl.setBorder(Rectangle.NO_BORDER);
      
      lCell = new Cell(new Phrase(SIPDFConf.SIREPORT_PDF_STOCK_TITLE, SIPDFConf.SIFONT_GOTHIC20B));
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      
      lCell = new Cell(new Phrase("\r\n", SIPDFConf.SIFONT_GOTHIC20B));
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      lDocument.add(lPdfTbl);
      
      // 3.ブランドコード
      lPdfTbl = new Table(4);
      lPdfTbl.setWidths(SIPDFConf.SISTOCK_TABLE_WIDTHS_MAKER);
      lPdfTbl.setWidth(SIPDFConf.SISTOCK_TABLE_WIDTH_NOR);
      lPdfTbl.setAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultHorizontalAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultVerticalAlignment(Element.ALIGN_MIDDLE);
      lPdfTbl.setBorder(Rectangle.BOTTOM);
      lPdfTbl.setPadding(2);
      
      lCell = new Cell(new Phrase("ブランドコード：", SIPDFConf.SIFONT_GOTHIC11N));
      lCell.setHorizontalAlignment(Element.ALIGN_RIGHT);
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      
      lCell = new Cell(new Phrase(maker.getMakerCode(), SIPDFConf.SIFONT_GOTHIC11N));
      lCell.setHorizontalAlignment(Element.ALIGN_LEFT);
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      
      lCell = new Cell(new Phrase("ブランド名：", SIPDFConf.SIFONT_GOTHIC11N));
      lCell.setHorizontalAlignment(Element.ALIGN_RIGHT);
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      
      lCell = new Cell(new Phrase(maker.getMakerName(), SIPDFConf.SIFONT_GOTHIC11N));
      lCell.setHorizontalAlignment(Element.ALIGN_LEFT);
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      
      lCell = new Cell(new Phrase("TEL：", SIPDFConf.SIFONT_GOTHIC11N));
      lCell.setHorizontalAlignment(Element.ALIGN_RIGHT);
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      
      lCell = new Cell(new Phrase(maker.getTel(), SIPDFConf.SIFONT_GOTHIC11N));
      lCell.setHorizontalAlignment(Element.ALIGN_LEFT);
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      
      lCell = new Cell(new Phrase("URL：", SIPDFConf.SIFONT_GOTHIC11N));
      lCell.setHorizontalAlignment(Element.ALIGN_RIGHT);
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      
      lCell = new Cell(new Phrase(maker.getUrl(), SIPDFConf.SIFONT_GOTHIC11N));
      lCell.setHorizontalAlignment(Element.ALIGN_LEFT);
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      
      lDocument.add(lPdfTbl);
      
      lPdfTbl = new Table(2);
      lPdfTbl.setWidth(SIPDFConf.SISTOCK_TABLE_WIDTH_NOR);
      lPdfTbl.setWidths(SIPDFConf.SISTOCK_TABLE_WIDTHS_MEMO);
      lPdfTbl.setAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultHorizontalAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultVerticalAlignment(Element.ALIGN_MIDDLE);
      lPdfTbl.setPadding(2);
      lPdfTbl.setOffset(12);
      
      lCell = new Cell(new Paragraph("メモ：", SIPDFConf.SIFONT_GOTHIC11N));
      lCell.add(new Paragraph("　", SIPDFConf.SIFONT_GOTHIC11N));
      lCell.add(new Paragraph("　", SIPDFConf.SIFONT_GOTHIC11N));
      lCell.setHorizontalAlignment(Element.ALIGN_RIGHT);
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      
      lCell = new Cell(new Paragraph(maker.getDescription(), SIPDFConf.SIFONT_GOTHIC11N));
      lCell.setHorizontalAlignment(Element.ALIGN_LEFT);
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      
      lDocument.add(lPdfTbl);
      
      lPdfTbl = new Table(6);// 7.3.0 PI-NEX0501 修正
      lPdfTbl.setWidths(SIPDFConf.SISTOCK_TABLE_WIDTHS_CUST);
      lPdfTbl.setWidth(SIPDFConf.SISTOCK_TABLE_WIDTH_NOR);
      lPdfTbl.setAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultHorizontalAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultVerticalAlignment(Element.ALIGN_MIDDLE);
      lPdfTbl.setPadding(1);
      lPdfTbl.setBorder(Rectangle.NO_BORDER);
      
      lCell = new Cell(new Phrase("\r\n", SIPDFConf.SIFONT_GOTHIC11B));
      lCell.setBorder(Rectangle.NO_BORDER);
      lCell.setColspan(6);// 7.3.0 PI-NEX0501 修正
      lPdfTbl.addCell(lCell);
      
      //
      for (int jj = 0; jj < SIPDFConf.SISTOCK_DETAIL_TITLE_NAME.length; jj++) {
        lCell = new Cell(new Paragraph(SIPDFConf.SISTOCK_DETAIL_TITLE_NAME[jj], SIPDFConf.SIFONT_GOTHIC11B));
        lCell.setHeader(true);
        lCell.setBorder(Rectangle.BOTTOM);
        if (jj >= SIPDFConf.SISTOCK_DETAIL_TITLE_NAME.length - 2) lCell.setHorizontalAlignment(Element.ALIGN_RIGHT);// 7.3.0 PI-NEX0501 修正
        else lCell.setHorizontalAlignment(Element.ALIGN_LEFT);
        lPdfTbl.addCell(lCell);
      }
      lDocument.add(lPdfTbl);
      
    } catch (DocumentException e) {
      e.printStackTrace();
    }
  }
  
  /**
   * <b>addShopInfo</b> pdfにショップ情報を出力
   * 
   * @param lDocument
   * @param lConnection
   * @param ShopCode
   * @return なし
   * @throws なし
   */
  private void addShopInfo(Document lDocument, Connection lConnection, String ShopCode) {
    try {
      Table lPdfTbl = new Table(1);
      lPdfTbl.setAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultHorizontalAlignment(Element.ALIGN_CENTER);
      lPdfTbl.setDefaultVerticalAlignment(Element.ALIGN_MIDDLE);
      lPdfTbl.setWidth(SIPDFConf.SISTOCK_TABLE_WIDTH_NOR);
      lPdfTbl.setPadding(1);
      lPdfTbl.setBorder(Rectangle.NO_BORDER);
      
      Cell lCell = new Cell(new Phrase("\r\n", SIPDFConf.SIFONT_GOTHIC11B));
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      
      lDocument.add(lPdfTbl);
      
      SIMallShop mallshop = new SIMallShop(ShopCode);
      mallshop.reset(lConnection);
      
      // 会社名など
      lPdfTbl = new Table(1);
      lPdfTbl.setWidth(SIPDFConf.SISTOCK_TABLE_WIDTH_NOR);
      lPdfTbl.setAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultHorizontalAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultVerticalAlignment(Element.ALIGN_MIDDLE);
      lPdfTbl.setBorder(Rectangle.TOP);
      lPdfTbl.setPadding(2);
      
      lCell = new Cell(new Phrase("発行会社名：" + mallshop.getMallShopName(), SIPDFConf.SIFONT_GOTHIC9N));
      lCell.setHorizontalAlignment(Element.ALIGN_LEFT);
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      
      lCell = new Cell(new Phrase("住所：〒" + mallshop.getPostCode() + " " + mallshop.getAddress(), SIPDFConf.SIFONT_GOTHIC9N));
      lCell.setHorizontalAlignment(Element.ALIGN_LEFT);
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      
      lCell = new Cell(new Phrase("TEL：" + mallshop.getTel(), SIPDFConf.SIFONT_GOTHIC9N));
      lCell.setHorizontalAlignment(Element.ALIGN_LEFT);
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      
      lCell = new Cell(new Phrase("FAX：" + mallshop.getFax(), SIPDFConf.SIFONT_GOTHIC9N));
      lCell.setHorizontalAlignment(Element.ALIGN_LEFT);
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      
      lCell = new Cell(new Phrase("E-MAIL：" + mallshop.getEmail(), SIPDFConf.SIFONT_GOTHIC9N));
      lCell.setHorizontalAlignment(Element.ALIGN_LEFT);
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      
      lDocument.add(lPdfTbl);
      
    } catch (DocumentException e) {
      e.printStackTrace();
    }
  }
  
  /**
   * <b>producePDFFile</b> pdfファイルを作成
   * 
   * @param response
   * @param lConnection
   * @param lLogin
   * @param lOrderList
   * @return なし
   * @throws なし
   */
  private void producePDFFile(HttpServletResponse response, Connection lConnection, SILogin lLogin, UIOrderListCond lOrderList) {
    File lOrderTempDir = (File) this.getServletContext().getAttribute("javax.servlet.context.tempdir");
    File lOrderTempFile = new File("");
    int lCount = 0;
    boolean lFirst = true;
    String lAmount = "0";
    try {
      lOrderTempFile = File.createTempFile(SIPDFConf.SIPDF_PREFIX_STOCK_FILE_NAME, ".pdf", lOrderTempDir);
    } catch (IOException e1) {
      e1.printStackTrace();
    }
    String lFileName = lOrderTempFile.getAbsolutePath();
    
    Document document = new Document();
    
    try {
      PdfWriter writer = PdfWriter.getInstance(document, new FileOutputStream(lFileName));
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (DocumentException e) {
      e.printStackTrace();
    }
    HeaderFooter footer = new HeaderFooter(new Phrase("- "), new Phrase(" -"));
    footer.setAlignment(Element.ALIGN_RIGHT);
    footer.setBorder(Rectangle.NO_BORDER);
    document.setFooter(footer);
    
    document.open();
    
    StringBuffer lSqlBuf = new StringBuffer();
    // StringBuffer lSubSqlBuf=new StringBuffer();
    // SICmdty lCmdty=new SICmdty();
    // SICmdtyStndrd lCmdtyStndrd=new SICmdtyStndrd();
    SICartCmdty lCartCmdty = new SICartCmdty();
    
    lSqlBuf
        .append("SELECT bb.ShopCode,dd.MakerCode,dd.MakerName,dd.memo,dd.tel,dd.url,bb.CmdtyCode,bb.IndividualCode,bb.StndrdCode1,bb.ElementCode1,bb.StndrdCode2,bb.ElementCode2,sum(bb.Amount) AS Amount ");// 7.3.0
    // PI-NES0501
    // 修正
    lSqlBuf.append(",CASE WHEN ee.usednewFlg IN ('1','6','7') THEN '新品' ");
    lSqlBuf.append("WHEN ee.usednewFlg IN ('2','4') THEN '新古OUTLET' ");
    lSqlBuf.append("WHEN ee.usednewFlg = '3' THEN 'BG認定中古' ");
    lSqlBuf.append("ELSE '中古' END AS usednewFlg, ff.ColorName ");// 7.3.0
    // PI-NES0501 追加
    // 7.2.0 ST1030 修正 ここから
    lSqlBuf.append("FROM ").append(SIConfig.SIVIEW_ORDER_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa, ");
    lSqlBuf.append(SIConfig.SIVIEW_ORDER_DETAIL_LATEST_NAME).append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bb, ");
    // lSqlBuf.append("FROM OrderTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("aa, ");
    // lSqlBuf.append("OrderDetailTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("bb, ");
    // 7.2.0 ST1030 修正 ここまで
    lSqlBuf.append("CmdtyMTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("cc, ");
    lSqlBuf.append("MakerTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("dd, ");// 7.3.0 PI-NES0501 修正
    lSqlBuf.append("IndividualTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("ee, ");// 7.3.0 PI-NES0501 追加
    lSqlBuf.append("ColorTbl").append(SIDBMultiConf.SIALIAS_CURR_NAME).append("ff ");// 7.3.0 PI-NES0501 追加
    lSqlBuf.append(",custtbl cust ");
    lSqlBuf.append("WHERE aa.OrderCode=bb.OrderCode ");
    lSqlBuf.append("AND bb.ShopCode=cc.ShopCode ");
    lSqlBuf.append("AND bb.CmdtyCode=cc.CmdtyCode ");
    lSqlBuf.append("AND aa.status='1' AND aa.custcode=cust.custcode ");
    if (lLogin.isShop()) lSqlBuf.append("AND bb.ShopCode=").append(SIDBUtil.SQL2Str(lLogin.getMallShopCode(), " "));
    // lSqlBuf.append("AND bb.ShopCode=dd.ShopCode ");
    lSqlBuf.append("AND cc.MakerCode=dd.MakerCode ");// 7.3.0 PI-NES0501 修正
    // 7.3.0 PI-NES0501 追加 ここから
    lSqlBuf.append("AND bb.cmdtycode = ee.cmdtycode AND bb.shopcode = ee.shopcode AND bb.IndividualCode = ee.IndividualCode ");
    // lSqlBuf.append("AND ee.ShopCode=dd.ShopCode ");
    lSqlBuf.append("AND cc.CmdtyCode=ee.CmdtyCode ");
    lSqlBuf.append("AND cc.ShopCode=ee.ShopCode ");
    lSqlBuf.append("AND cc.ColorCode=ff.ColorCode ");
    // 7.3.0 PI-NES0501 追加 ここまで
    lSqlBuf.append(lOrderList.getConditionSQL());
    lSqlBuf
        .append("GROUP BY bb.ShopCode,dd.MakerCode,dd.MakerName,dd.memo,dd.tel,dd.url,bb.CmdtyCode,bb.IndividualCode,bb.StndrdCode1,bb.ElementCode1,bb.StndrdCode2,bb.ElementCode2, ee.usednewFlg, ff.ColorName");// 7.3.0
    // PI-NES0501
    // 修正
    log.debug("producePDFFile:lSqlBuf=" + lSqlBuf.toString());
    
    Statement lStatement = null;
    ResultSet lResultSet = null;
    Cell lCell;
    String lMakerCode = "";
    String lShopCode = "";
    
    try {
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSqlBuf.toString());
      while (lResultSet.next()) {
        lCount++;
        lCartCmdty = new SICartCmdty(lResultSet.getString("ShopCode"), lResultSet.getString("CmdtyCode"), lResultSet.getString("IndividualCode")// 7.3.0 PI-NES0501 追加
            , lResultSet.getString("StndrdCode1"), lResultSet.getString("ElementCode1"), lResultSet.getString("StndrdCode2"), lResultSet.getString("ElementCode2"));
        lCartCmdty.reset(lConnection);
        lAmount = SIUtil.add_LL(lCartCmdty.getAmount(), lResultSet.getString("Amount"));
        
        log.debug("producePDFFile:MakerCode=" + lResultSet.getString("MakerCode") + ",MakerName=" + lResultSet.getString("MakerName"));
        log.debug("producePDFFile:getAmount=" + lCartCmdty.getAmount() + ",Amount=" + lResultSet.getString("Amount") + ",getCartCmdtyName()=" + lCartCmdty.getCartCmdtyName());
        if (!lShopCode.equals(lResultSet.getString("ShopCode")) || !lMakerCode.equals(lResultSet.getString("MakerCode"))) {
          lCount = 1;
          SIMaker maker = new SIMaker();
          maker.setMakerCode(lResultSet.getString("MakerCode"));
          maker.setMakerName(lResultSet.getString("MakerName"));
          maker.setDescription(lResultSet.getString("Memo"));
          maker.setTel(lResultSet.getString("tel"));
          maker.setUrl(lResultSet.getString("url"));
          if (!lFirst) {
            addShopInfo(document, lConnection, lShopCode);
          }
          changePageHeader(document, maker, lFirst);
          lFirst = false;
          lShopCode = lResultSet.getString("ShopCode");
          lMakerCode = lResultSet.getString("MakerCode");
        }
        try {
          
          Table lPdfTbl = new Table(6);// 7.3.0 PI-NES0501 修正
          lPdfTbl.setWidths(SIPDFConf.SISTOCK_TABLE_WIDTHS_CUST);
          lPdfTbl.setWidth(SIPDFConf.SISTOCK_TABLE_WIDTH_NOR);
          lPdfTbl.setAlignment(Element.ALIGN_LEFT);
          lPdfTbl.setDefaultHorizontalAlignment(Element.ALIGN_LEFT);
          lPdfTbl.setDefaultVerticalAlignment(Element.ALIGN_MIDDLE);
          lPdfTbl.setPadding(1);
          lPdfTbl.setBorder(Rectangle.NO_BORDER);
          
          lCell = new Cell(new Paragraph(String.valueOf(lCount), SIPDFConf.SIFONT_GOTHIC9N));
          lCell.setHorizontalAlignment(Cell.ALIGN_LEFT);
          lCell.setBorder(Rectangle.NO_BORDER);
          lPdfTbl.addCell(lCell);
          
          lCell = new Cell(new Paragraph(lCartCmdty.getCmdtyCode(), SIPDFConf.SIFONT_GOTHIC9N));
          lCell.setHorizontalAlignment(Cell.ALIGN_LEFT);
          lCell.setBorder(Rectangle.NO_BORDER);
          lPdfTbl.addCell(lCell);
          
          // 7.3.0 PI-NES0501 追加 ここから
          lCell = new Cell(new Paragraph(lCartCmdty.getIndividualCode(), SIPDFConf.SIFONT_GOTHIC9N));
          lCell.setHorizontalAlignment(Cell.ALIGN_LEFT);
          lCell.setBorder(Rectangle.NO_BORDER);
          lPdfTbl.addCell(lCell);
          // 7.3.0 PI-NES0501 追加 ここまで
          
          lCell = new Cell(new Paragraph(lCartCmdty.getCartCmdtyName() + "　(" + lResultSet.getString("ColorName") + "・" + lResultSet.getString("usednewFlg") + ")",
              SIPDFConf.SIFONT_GOTHIC9N));// 7.3.0 PI-NES0501 修正
          lCell.setHorizontalAlignment(Cell.ALIGN_LEFT);
          lCell.setBorder(Rectangle.NO_BORDER);
          lPdfTbl.addCell(lCell);
          
          if (lCartCmdty.getAmountFlg().equals("1")) {
            lCell = new Cell(new Paragraph(NumberFormat.getNumberInstance().format(new Double(lAmount)), SIPDFConf.SIFONT_GOTHIC9N));
          } else {
            lCell = new Cell(new Paragraph(("--"), SIPDFConf.SIFONT_GOTHIC9N));
          }
          lCell.setHorizontalAlignment(Cell.ALIGN_RIGHT);
          lCell.setBorder(Rectangle.NO_BORDER);
          lPdfTbl.addCell(lCell);
          lCell = new Cell(new Paragraph(NumberFormat.getNumberInstance().format(new Double(lResultSet.getString("Amount"))), SIPDFConf.SIFONT_GOTHIC9N));
          lCell.setHorizontalAlignment(Cell.ALIGN_RIGHT);
          lCell.setBorder(Rectangle.NO_BORDER);
          lPdfTbl.addCell(lCell);
          
          // 7.3.0 PI-NES0501 削除 ここから
          /*
           * long lessAmount = Long.parseLong(lResultSet.getString("Amount"))-Long.parseLong(lAmount); if(lessAmount<0)lessAmount=0; if(lCartCmdty.getAmountFlg().equals("1")){
           * lCell=new Cell(new Paragraph(NumberFormat.getNumberInstance().format(new Long(lessAmount)),SIPDFConf.SIFONT_GOTHIC9N)); }else{ lCell=new Cell(new
           * Paragraph(("--"),SIPDFConf.SIFONT_GOTHIC9N)); } lCell.setHorizontalAlignment(Cell.ALIGN_RIGHT); lCell.setBorder(Rectangle.NO_BORDER); lPdfTbl.addCell(lCell); lCell=new
           * Cell(new Paragraph("_____",SIPDFConf.SIFONT_GOTHIC9N)); lCell.setHorizontalAlignment(Cell.ALIGN_RIGHT); lCell.setBorder(Rectangle.NO_BORDER); lPdfTbl.addCell(lCell);
           */
          // 7.3.0 PI-NES0501 削除 ここまで
          document.add(lPdfTbl);
        } catch (BadElementException e2) {
          e2.printStackTrace();
        } catch (DocumentException e2) {
          e2.printStackTrace();
        }
      }
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      SIDBUtil.close(lStatement, lResultSet);
    }
    // 最後のショップ情報を追加
    addShopInfo(document, lConnection, lShopCode);
    
    document.resetPageCount();
    if (!lFirst) {
      document.close();
      String lOutputFileName = SIPDFConf.SIPDF_PREFIX_STOCK_FILE_NAME + (new SIDateTime().getDateTimeString()) + "." + SIPDFConf.SIPDF_EXTENSION_NAME;
      SIPDFUtil lPdfUtil = new SIPDFUtil();
      lPdfUtil.execute(response, lFileName, lOutputFileName);
    }
    // 臨時ファイルの削除
    lOrderTempFile.delete();
  }
  
  private void producePDFFile1(HttpServletResponse response, Connection lConnection, SILogin lLogin, UIOrderListCond lOrderList) {
    File lOrderTempDir = (File) this.getServletContext().getAttribute("javax.servlet.context.tempdir");
    File lOrderTempFile = new File("");
    try {
      lOrderTempFile = File.createTempFile("order_", ".pdf", lOrderTempDir);
    } catch (IOException e1) {
      e1.printStackTrace();
    }
    String lFileName = lOrderTempFile.getAbsolutePath();
    
    Document document = new Document();
    
    try {
      PdfWriter writer = PdfWriter.getInstance(document, new FileOutputStream(lFileName));
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (DocumentException e) {
      e.printStackTrace();
    }
    HeaderFooter footer = new HeaderFooter(new Phrase("- "), new Phrase(" -"));
    footer.setAlignment(Element.ALIGN_RIGHT);
    footer.setBorder(Rectangle.NO_BORDER);
    document.setFooter(footer);
    
    document.open();
    
    if (lOrderList == null) {
      log.warn("not init ");
      return;
    }
    
    for (int ii = 0; ii < lOrderList.getOrderCodeChk().length; ii++) {
      if (SIUtil.isNotNull(lOrderList.getOrderCodeChk()[ii])) {
        producePDFFile(lConnection, lLogin, document, lOrderList.getOrderCodeChk()[ii]);
      }
    }
    
    if (lOrderList.getOrderCodeChk().length > 0) {
      document.close();
      String lOutputFileName = SIPDFConf.SIPDF_PREFIX_ORDER_FILE_NAME + (new SIDateTime().getDateTimeString()) + "." + SIPDFConf.SIPDF_EXTENSION_NAME;
      SIPDFUtil lPdfUtil = new SIPDFUtil();
      lPdfUtil.execute(response, lFileName, lOutputFileName);
    }
    // 臨時ファイルの削除
    lOrderTempFile.delete();
  }
  
  /**
   * <b>producePDFFile</b> ある受注コードの全て種類【納品書(注文主用)と納品書(配送先用)と納品書兼請求書と納品書兼領収書】 のPDFファイルを作成します。
   * 
   * @param lLogin ログイン情報
   * @param lConnection DBへのコネクション
   * @param lOrderCode 出力する受注コード
   * @return なし
   * @throws なし
   */
  private void producePDFFile(Connection lConnection, SILogin lLogin, Document document, String lOrderCode) {
    Collection lDeliveryColl;
    try {
      lDeliveryColl = SIDBUtil.getCollection(lConnection,
      // 7.2.0 ST1030 修正 ここから
          "SELECT DeliveryCode,DeliveryCode FROM " + SIConfig.SIVIEW_ORDER_DELIVERY_LATEST_NAME + " WHERE OrderCode="
          // "SELECT DeliveryCode,DeliveryCode FROM OrderDeliveryTbl WHERE OrderCode="
              // 7.2.0 ST1030 修正 ここまで
              + SIDBUtil.SQL2Str(lOrderCode));
    } catch (SIDBAccessException e) {
      e.printStackTrace();
      return;
    }
    
    SINameValue[] lNameValue = (SINameValue[]) lDeliveryColl.toArray(new SINameValue[0]);
    
    if (SIUtil.isNull(lOrderCode)) return;
    
    for (int ii = SIPDFConf.SIREPORT_PDF_INX; ii < SIPDFConf.SIREPORT_PDF_RECEPT_INX + 1; ii++) {
      if (ii == SIPDFConf.SIREPORT_PDF_DELIVERY_INX) {
        for (int jj = 0; jj < lNameValue.length; jj++) {
          producePDFFile(lConnection, lLogin, document, lOrderCode, ii, lNameValue[jj].getName());
        }
      } else {
        producePDFFile(lConnection, lLogin, document, lOrderCode, ii, "");
      }
    }
  }
  
  /**
   * <b>producePDFFile</b> ある受注コードのある種類のPDFファイルを作成します。
   * 
   * @param lLogin ログイン情報
   * @param lConnection DBへのコネクション
   * @param lOrderCode 出力する受注コード
   * @param lReportType 出力する種類タイプ
   * @param lDeliveryCode 出力するある配送先コード
   * @return なし
   * @throws なし
   */
  private void producePDFFile(Connection lConnection, SILogin lLogin, Document document, String lOrderCode, int lReportType, String lDeliveryCode) {
    UIRegOrder lRegOrder = new UIRegOrder(lOrderCode);
    if (!lRegOrder.reset(lConnection)) {
      log.debug("not find order record for ordercode " + lOrderCode);
      return;
    }
    
    SIDateTime lDateTime = new SIDateTime();
    
    String priceOfCmdty = "0";// 商品自身の価格
    String sumOfA = "0";// 商品合計(税込み)
    String sumOfB = "0";// 送料合計(税込み)
    String sumOfC = "0";// 手数料合計(税込み)
    String sumOfD = "0";// 小計(税込み)
    String sumOfE = "0";// ポイント使用値
    String sumOfF = "0";// 支払額
    String sumOfG = "0";// 値引き額
    String sumOfH = "0";// ポイント付加
    int offset = 10;
    
    Cell lCell;
    try {
      
      // 1.発行日など
      Table lPdfTbl = new Table(1);
      lPdfTbl.setAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultHorizontalAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultVerticalAlignment(Element.ALIGN_MIDDLE);
      lPdfTbl.setWidth(SIPDFConf.SIORDER_TABLE_WIDTH_HEADER);
      lPdfTbl.setPadding(1);
      lPdfTbl.setBorder(Rectangle.NO_BORDER);
      lCell = new Cell(new Paragraph("発行日:" + lDateTime.getFullDate(), SIPDFConf.SIFONT_GOTHIC7N));
      lCell.setBorder(Rectangle.BOTTOM);
      lPdfTbl.addCell(lCell);
      lCell = new Cell(new Paragraph("注文日:" + lRegOrder.getInitDateTime(), SIPDFConf.SIFONT_GOTHIC7N));
      lCell.setBorder(Rectangle.BOTTOM);
      lPdfTbl.addCell(lCell);
      lCell = new Cell(new Paragraph("注文No." + lOrderCode, SIPDFConf.SIFONT_GOTHIC7N));
      lCell.setBorder(Rectangle.BOTTOM);
      lPdfTbl.addCell(lCell);
      document.add(lPdfTbl);
      
      // 2.タイトル(固定1)
      lPdfTbl = new Table(1);
      lPdfTbl.setWidth(SIPDFConf.SIORDER_TABLE_WIDTH_NOR);
      lPdfTbl.setAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultHorizontalAlignment(Element.ALIGN_CENTER);
      lPdfTbl.setDefaultVerticalAlignment(Element.ALIGN_MIDDLE);
      lPdfTbl.setPadding(2);
      lPdfTbl.setBorder(Rectangle.NO_BORDER);
      lCell = new Cell(new Phrase(SIPDFConf.SIREPORT_PDF_ORDER_TITLE[lReportType], SIPDFConf.SIFONT_GOTHIC20B));
      lCell.add(new Paragraph(SIPDFConf.SIREPORT_PDF_ORDER_SUBTITLE[lReportType], SIPDFConf.SIFONT_GOTHIC11N));
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      lPdfTbl.setOffset(6);
      document.add(lPdfTbl);
      
      // 3.注文主氏名
      lPdfTbl = new Table(3);
      lPdfTbl.setWidths(SIPDFConf.SIORDER_TABLE_WIDTHS_CUST);
      lPdfTbl.setWidth(SIPDFConf.SIORDER_TABLE_WIDTH_NOR);
      lPdfTbl.setAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultHorizontalAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultVerticalAlignment(Element.ALIGN_MIDDLE);
      lPdfTbl.setPadding(3);
      lPdfTbl.setOffset(8);
      
      lCell = new Cell(new Paragraph("注文主氏名: " + lRegOrder.getCustName() + "  " + SIPDFConf.SICUST_TITLE_NAME[Integer.parseInt(lRegOrder.getCustCompanyFlg())],
          SIPDFConf.SIFONT_GOTHIC9N));
      lCell.setColspan(2);
      lPdfTbl.addCell(lCell);
      
      lCell = new Cell(new Paragraph("手数料: " + lRegOrder.getFeeIncTax() + "円", SIPDFConf.SIFONT_GOTHIC9N));
      lCell.setHorizontalAlignment(Cell.ALIGN_RIGHT);
      sumOfC = lRegOrder.getFeeIncTax();// 手数料
      lPdfTbl.addCell(lCell);
      
      lCell = new Cell(new Paragraph("注文主住所: 〒" + lRegOrder.getPostCode(), SIPDFConf.SIFONT_GOTHIC9N));
      lCell.add(new Paragraph(lRegOrder.getAddress(), SIPDFConf.SIFONT_GOTHIC9N));
      lCell.setColspan(3);
      lPdfTbl.addCell(lCell);
      document.add(lPdfTbl);
      sumOfE = lRegOrder.getSumByPoint();
      
      // 4.コメント
      lPdfTbl = new Table(1);
      lPdfTbl.setWidth(SIPDFConf.SIORDER_TABLE_WIDTH_NOR);
      lPdfTbl.setAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultHorizontalAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultVerticalAlignment(Element.ALIGN_MIDDLE);
      lPdfTbl.setPadding(3);
      lPdfTbl.setOffset(5);
      lCell = new Cell(new Paragraph(SIPDFConf.SIORDER_COMMENT1[lReportType], SIPDFConf.SIFONT_GOTHIC9N));
      lPdfTbl.addCell(lCell);
      
      document.add(lPdfTbl);
      
      // 5.中見出し
      lPdfTbl = new Table(1);
      lPdfTbl.setWidth(SIPDFConf.SIORDER_TABLE_WIDTH_NOR);
      lPdfTbl.setAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultHorizontalAlignment(Element.ALIGN_CENTER);
      lPdfTbl.setDefaultVerticalAlignment(Element.ALIGN_MIDDLE);
      lPdfTbl.setPadding(3);
      lPdfTbl.setOffset(15);
      lPdfTbl.setBorder(Rectangle.NO_BORDER);
      
      lCell = new Cell(new Paragraph(SIPDFConf.SIORDER_MIDDLE_TITLE_NAME[lReportType], SIPDFConf.SIFONT_GOTHIC10B));
      lCell.setBorder(Rectangle.NO_BORDER);
      lPdfTbl.addCell(lCell);
      
      document.add(lPdfTbl);
      
      Collection lOrderDetailColl = new ArrayList();
      UIRegOrderDetail lRegOrderDetail[] = new UIRegOrderDetail[0];
      UIRegOrderDelivery lOrderDelivery = new UIRegOrderDelivery();
      UIRegOrderDetail lOrderDetail = new UIRegOrderDetail();
      
      Collection lOrderDeliveryColl = new ArrayList();
      if (lReportType == SIPDFConf.SIREPORT_PDF_DELIVERY_INX) {
        lOrderDelivery = new UIRegOrderDelivery(lOrderCode, lDeliveryCode);
        lOrderDelivery.reset(lConnection);
        lOrderDeliveryColl.add(lOrderDelivery);
      } else {
        lOrderDeliveryColl = lRegOrder.getCollection(lConnection, lLogin);
      }
      UIRegOrderDelivery[] lRegOrderDelivery = (UIRegOrderDelivery[]) lOrderDeliveryColl.toArray(new UIRegOrderDelivery[0]);
      
      for (int ii = 0; ii < lRegOrderDelivery.length; ii++) {
        // 6.宛先氏名
        lPdfTbl = new Table(3);
        lPdfTbl.setTableFitsPage(true);
        lPdfTbl.setWidth(SIPDFConf.SIORDER_TABLE_WIDTH_NOR);
        lPdfTbl.setWidths(SIPDFConf.SIORDER_TABLE_WIDTHS_ADDRESSEE);
        lPdfTbl.setAlignment(Element.ALIGN_LEFT);
        lPdfTbl.setDefaultHorizontalAlignment(Element.ALIGN_LEFT);
        lPdfTbl.setDefaultVerticalAlignment(Element.ALIGN_MIDDLE);
        lPdfTbl.setBorderWidth(1);
        lPdfTbl.setPadding(1);
        lPdfTbl.setOffset(30);
        
        lOrderDelivery = lRegOrderDelivery[ii];
        if (lReportType == SIPDFConf.SIREPORT_PDF_DELIVERY_INX) {
          lCell = new Cell(new Phrase("宛先氏名: ", SIPDFConf.SIFONT_GOTHIC9N));// 配送先用
        } else {
          lCell = new Cell(new Phrase("宛先氏名" + (ii + 1) + ": ", SIPDFConf.SIFONT_GOTHIC9N));// その他
        }
        lCell.add(new Paragraph(lOrderDelivery.getDeliveryAddressee() + "  " + SIPDFConf.SICUST_TITLE_NAME[Integer.parseInt(lOrderDelivery.getCustCompanyFlg())],
            SIPDFConf.SIFONT_GOTHIC9N));
        lCell.setColspan(2);
        lCell.setBorder(Rectangle.NO_BORDER);
        lPdfTbl.addCell(lCell);
        
        lCell = new Cell(new Paragraph("配送先送料: " + lOrderDelivery.getDeliveryFeeIncTax() + "円", SIPDFConf.SIFONT_GOTHIC9N));
        lCell.setHorizontalAlignment(Cell.ALIGN_RIGHT);
        lCell.setBorder(Rectangle.NO_BORDER);
        sumOfB = SIUtil.add_LL(sumOfB, lOrderDelivery.getDeliveryFeeIncTax());// 送料の合計
        lCell.setHeader(true);
        lPdfTbl.addCell(lCell);
        lCell = new Cell(new Paragraph("宛先住所: 〒" + lOrderDelivery.getPostCode(), SIPDFConf.SIFONT_GOTHIC9N));
        lCell.add(new Paragraph(lOrderDelivery.getAddress(), SIPDFConf.SIFONT_GOTHIC9N));
        lCell.setColspan(3);
        lPdfTbl.addCell(lCell);
        
        document.add(lPdfTbl);
        
        lOrderDetailColl = lOrderDelivery.getDetailCollection(lConnection);
        lRegOrderDetail = (UIRegOrderDetail[]) lOrderDetailColl.toArray(new UIRegOrderDetail[0]);
        lPdfTbl = new Table(SIPDFConf.SIORDER_DETAIL_WIDTHS[lReportType].length);
        lPdfTbl.setTableFitsPage(true);
        lPdfTbl.setWidth(SIPDFConf.SIORDER_TABLE_WIDTH_NOR);
        lPdfTbl.setWidths(SIPDFConf.SIORDER_DETAIL_WIDTHS[lReportType]);
        lPdfTbl.setBorderWidth(1);
        lPdfTbl.setPadding(1);
        lPdfTbl.setOffset(4);
        log.debug("SIPDFConf.SIORDER_DETAIL_TITLE_COUNT[" + lReportType + "]=" + SIPDFConf.SIORDER_DETAIL_TITLE_COUNT[lReportType]);
        
        // 7.商品明細
        for (int jj = 0; jj < SIPDFConf.SIORDER_DETAIL_TITLE_COUNT[lReportType]; jj++) {
          lCell = new Cell(new Paragraph(SIPDFConf.SIORDER_DETAIL_TITLE_NAME[lReportType][jj], SIPDFConf.SIFONT_GOTHIC7B));
          lCell.setHeader(true);
          lCell.setBorderWidth(0.5f);
          lCell.setBorder(Rectangle.BOTTOM);
          if (jj >= 3) lCell.setHorizontalAlignment(Element.ALIGN_RIGHT);
          else lCell.setHorizontalAlignment(Element.ALIGN_LEFT);
          lPdfTbl.addCell(lCell);
        }
        
        for (int mm = 0; mm < lRegOrderDetail.length; mm++) {
          lOrderDetail = lRegOrderDetail[mm];
          lCell = new Cell(new Paragraph(lOrderDetail.getShopCode(), SIPDFConf.SIFONT_GOTHIC7N));
          lCell.setBorder(Rectangle.NO_BORDER);
          lCell.setHorizontalAlignment(Element.ALIGN_LEFT);
          lPdfTbl.addCell(lCell);
          
          lCell = new Cell(new Paragraph(lOrderDetail.getCmdtyCode(), SIPDFConf.SIFONT_GOTHIC7N));
          lCell.setBorder(Rectangle.NO_BORDER);
          lCell.setHorizontalAlignment(Element.ALIGN_LEFT);
          lPdfTbl.addCell(lCell);
          
          lCell = new Cell(new Paragraph(lOrderDetail.getCartCmdtyName(), SIPDFConf.SIFONT_GOTHIC7N));
          lCell.setBorder(Rectangle.NO_BORDER);
          lCell.setHorizontalAlignment(Element.ALIGN_LEFT);
          lPdfTbl.addCell(lCell);
          
          lCell = new Cell(new Paragraph(lOrderDetail.getAmount(), SIPDFConf.SIFONT_GOTHIC7N));
          lCell.setBorder(Rectangle.NO_BORDER);
          lCell.setHorizontalAlignment(Element.ALIGN_RIGHT);
          lPdfTbl.addCell(lCell);
          
          if (lReportType == SIPDFConf.SIREPORT_PDF_DELIVERY_INX) {} else {
            lCell = new Cell(new Paragraph(lOrderDetail.getPriceIncTax() + "円", SIPDFConf.SIFONT_GOTHIC7N));
            lCell.setBorder(Rectangle.NO_BORDER);
            lCell.setHorizontalAlignment(Element.ALIGN_RIGHT);
            lPdfTbl.addCell(lCell);
            
            priceOfCmdty = SIUtil.multi_LL(lOrderDetail.getAmount(), lOrderDetail.getPriceIncTax());
            sumOfA = SIUtil.add_LLL(sumOfA, priceOfCmdty, lOrderDetail.getWrappingPriceIncTax());// 商品の購入合計価格
            lCell = new Cell(new Paragraph(priceOfCmdty + "円", SIPDFConf.SIFONT_GOTHIC7N));
            lCell.setBorder(Rectangle.NO_BORDER);
            lCell.setHorizontalAlignment(Element.ALIGN_RIGHT);
            lPdfTbl.addCell(lCell);
          }
          lCell = new Cell(new Paragraph("", SIPDFConf.SIFONT_GOTHIC7N));
          lCell.setBorder(Rectangle.NO_BORDER);
          lCell.setHorizontalAlignment(Element.ALIGN_RIGHT);
          lPdfTbl.addCell(lCell);
        }
        document.add(lPdfTbl);
      }
      
      // 8.連絡事項
      if (lReportType == SIPDFConf.SIREPORT_PDF_DELIVERY_INX) {} else {
        lPdfTbl = new Table(2);
        lPdfTbl.setWidth(SIPDFConf.SIORDER_TABLE_WIDTH_NOR);
        lPdfTbl.setAlignment(Element.ALIGN_LEFT);
        lPdfTbl.setDefaultHorizontalAlignment(Element.ALIGN_LEFT);
        lPdfTbl.setDefaultVerticalAlignment(Element.ALIGN_MIDDLE);
        lPdfTbl.setPadding(2);
        lPdfTbl.setTableFitsPage(true);
        lPdfTbl.setOffset(15);
        
        lCell = new Cell(new Paragraph("連絡事項: ", SIPDFConf.SIFONT_GOTHIC9N));
        lCell.add(new Paragraph(lRegOrder.getContactMsg(), SIPDFConf.SIFONT_GOTHIC9N));
        lPdfTbl.addCell(lCell);
        
        sumOfD = SIUtil.add_LLL(sumOfA, sumOfB, sumOfC);
        sumOfF = SIUtil.sub_LL(SIUtil.sub_LL(sumOfD, sumOfG), sumOfE);
        lCell = new Cell(new Paragraph("合計", SIPDFConf.SIFONT_GOTHIC9B));
        
        lCell.add(new Phrase("商品合計金額(A): ", SIPDFConf.SIFONT_GOTHIC9B));
        lCell.add(new Paragraph(sumOfA + "円", SIPDFConf.SIFONT_GOTHIC9N));
        
        lCell.add(new Phrase("送料合計金額(B): ", SIPDFConf.SIFONT_GOTHIC9B));
        lCell.add(new Paragraph(sumOfB + "円", SIPDFConf.SIFONT_GOTHIC9N));
        
        lCell.add(new Phrase("手数料合計金額(C): ", SIPDFConf.SIFONT_GOTHIC9B));
        lCell.add(new Paragraph(sumOfC + "円", SIPDFConf.SIFONT_GOTHIC9N));
        
        lCell.add(new Phrase("ポイント使用可能金額(D=A+B+C): ", SIPDFConf.SIFONT_GOTHIC9B));
        lCell.add(new Paragraph(sumOfD + "円", SIPDFConf.SIFONT_GOTHIC9N));
        
        lCell.add(new Phrase("ポイント使用額(E): ", SIPDFConf.SIFONT_GOTHIC9B));
        lCell.add(new Paragraph(sumOfE + "円", SIPDFConf.SIFONT_GOTHIC9N));
        
        lCell.add(new Phrase("お支払い合計額(F=D-E): ", SIPDFConf.SIFONT_GOTHIC9B));
        lCell.add(new Paragraph(sumOfF + "円", SIPDFConf.SIFONT_GOTHIC9N));
        lPdfTbl.addCell(lCell);
        
        document.add(lPdfTbl);
      }
      
      // 9.フリースペース
      lPdfTbl = new Table(1);
      lPdfTbl.setWidth(SIPDFConf.SIORDER_TABLE_WIDTH_NOR);
      lPdfTbl.setAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultHorizontalAlignment(Element.ALIGN_LEFT);
      lPdfTbl.setDefaultVerticalAlignment(Element.ALIGN_MIDDLE);
      lPdfTbl.setPadding(3);
      
      if (lReportType == SIPDFConf.SIREPORT_PDF_BILL_INX) {
        lCell = new Cell(new Phrase("支払方法: ", SIPDFConf.SIFONT_GOTHIC9N));
        lCell.add(new Paragraph(lRegOrder.getPayMethodName(), SIPDFConf.SIFONT_GOTHIC9N));
        lPdfTbl.addCell(lCell);
      }
      
      if (lReportType == SIPDFConf.SIREPORT_PDF_RECEPT_INX) {
        lCell = new Cell(new Paragraph("上記料金を領収いたしました。", SIPDFConf.SIFONT_GOTHIC9N));
        lPdfTbl.addCell(lCell);
      }
      
      lCell = new Cell(new Paragraph(SIPDFConf.SIORDER_COMMENT2[lReportType], SIPDFConf.SIFONT_GOTHIC9N));
      lPdfTbl.addCell(lCell);
      
      lCell = new Cell(new Paragraph("発行者連絡先: 〒 " + lLogin.getPostCode(), SIPDFConf.SIFONT_GOTHIC9N));
      lCell.add(new Paragraph(lLogin.getAddress(), SIPDFConf.SIFONT_GOTHIC9N));
      lCell.add(new Paragraph("電話番号: " + lLogin.getTel(), SIPDFConf.SIFONT_GOTHIC9N));
      lCell.add(new Paragraph("FAX番号: " + lLogin.getFax(), SIPDFConf.SIFONT_GOTHIC9N));
      lPdfTbl.addCell(lCell);
      
      lCell = new Cell(new Paragraph("発行者: " + lLogin.getMallShopName(), SIPDFConf.SIFONT_GOTHIC11B));
      lPdfTbl.addCell(lCell);
      lPdfTbl.setOffset(3);
      document.add(lPdfTbl);
      
      document.resetPageCount();
      document.newPage();
    } catch (Exception e) {
      System.err.println(e.getMessage());
    }
  }
  
  /**
   * <b>orderReturn</b> 受注伝票を返品します。
   * 
   * @param lConnection DBへのコネクション
   * @param lOrderCode 対象の受注コード
   * @return なし
   * @throws SIDBAccessException
   */
  public String orderReturn(Connection lConnection, String user,UIRegOrder regOrder) throws SIDBAccessException, SIErrorException {
    String lRes = "";
    SIDateTime lDateTime = new SIDateTime();
    
    try {
      SIModifyRec lRec = new SIModifyRec(SIConfig.SITABLE_ORDER_NAME);// 7.2.0 ST1030 修正
      // SISpcType lSpcType = new SISpcType();
      
      if (SIUtil.isNotNull(regOrder.getOrderCode())) {
        /* 080530 返品時はカード決済を取り消さないように修正
        // 1.カード決済の取り消す
        SIOrder lOrder = new SIOrder(lOrderCode);
        lOrder.reset(lConnection);
        if (lOrder.getPaymentFlg().equals(SIFlagConf.SIFLAG_PAYMENTFLG_CARD)) {
          SIMDKAction lMDKAction = new SIMDKAction();
          lMDKAction.setOrderCode(lOrder.getOrderCode());
          lMDKAction.setPrice(lOrder.getPaymentTotal());
          if (!lMDKAction.cancelCaptureAction()) throw new SIErrorException("受注番号「" + lOrderCode + "」に対するキャンセルが失敗しました。");
        }
        // paymentflgが9の場合、カード決済取り消し処理を行わない（強制キャンセル）
        if (lOrder.getPaymentFlg().equals(SIFlagConf.SIFLAG_PAYMENTFLG_CARD) || lOrder.getPaymentFlg().equals("9")) {
          // 入金情報にマイナス金額をいれる
          SIInsertRec lCardPayRec = new SIInsertRec("paymenttbl");
          lCardPayRec.add("OrderCode", lOrder.getOrderCode());
          lCardPayRec.add("PaymentNumber", lOrder.getPaymentNumber(lConnection));
          lCardPayRec.add("PaymentPrice", SIUtil.setNegative(lOrder.getPaymentTotal()));
          lCardPayRec.add("PaymentFee", "0");
          lCardPayRec.add("PaymentType", SIConfig.PAYMETHOD_CARD);
          lCardPayRec.add("ExpectedPaymentDate", lDateTime.getFullDate());
          lCardPayRec.add("PaymentDate", lDateTime.getFullDate());
          lCardPayRec.add("Status", "0");
          lCardPayRec.execute(lConnection);
          
          lRec.add("PaymentFlg", SIFlagConf.SIFLAG_PAYMENTFLG_CARD);// 区分をカード決済に戻す
        }
        */
        // 2.ポイントを無効にする(獲得、使用)
        SIPointMan.pointInvalid(lConnection, regOrder.getOrderCode());
        
        // 3.RFM分析テーブルを削除する
        (new SIRegRfmOrderLogSrv()).deleteTableData(lConnection, regOrder.getOrderCode());
        
        // 4.受注のヘッダの修正、ステータスを返品状態("2")にする
        lRec.addCondition("OrderCode", regOrder.getOrderCode());// 受注番号
        lRec.add("Status", "2");
        lRec.add("updatedatetime", lDateTime.getFullDateTime());// PI-BTG0601
        lRec.execute(lConnection);
        
        if (regOrder.getKeepItems() != null && regOrder.getKeepItems().length>0) {// キープ件数１以上の場合キープヘッダを作成する
          String keepNumber = SIDBUtil.getFirstData(lConnection, SIDBUtil.getNextVal("keepnumber_seq"));
          String branch = SIDBUtil.getFirstData(lConnection, "SELECT belongingbranchcode FROM chargetbl WHERE chargecode="+SIDBUtil.SQL2Str(user));
          String charge = "";
          if (SIUtil.isNotNull(branch)) charge =user;
          lRes = keepNumber;
          
          SIInsertRec lRec2 = new SIInsertRec("keeptbl");
          lRec2.add("keepnumber", keepNumber);
          lRec2.add("status", "0");
          lRec2.add("branchcode", branch);
          lRec2.add("chargecode", charge);
          lRec2.add("keepdate", lDateTime.getFullDate());
          lRec2.execute(lConnection);
          
          // 5.返品戻り入庫データを作成する
          SIBGUtil.returnOrderAllocation(lConnection, regOrder, user, keepNumber); 
          
        }else{
          // 5.返品戻り入庫データを作成する（キープなしの場合）
          SIBGUtil.returnOrderAllocation(lConnection, regOrder, user); 
        }
        
        // 一部出荷の場合、出荷日を設定する
        lRec = new SIModifyRec(SIConfig.SITABLE_ORDER_DETAIL_NAME);
        lRec.addCondition("OrderCode", regOrder.getOrderCode());// 受注番号
        lRec.addCondition("Shippmentdate", "");
        lRec.add("Shippmentdate", lDateTime.getFullDateTime());
        lRec.execute(lConnection);
      }
    } catch (SIDuplicateKeyException e) {
      throw new SIDBAccessException(e);
    } catch (SIDBAccessException e) {
      throw new SIDBAccessException(e);
    } catch (SQLException e) {
      throw new SIDBAccessException(e);
    }
    return lRes;
  }
  
  private boolean sendOrderKeepMail(Connection lConnection, String ordercode, String fromMail) throws SIDBAccessException, SQLException {
    StringBuffer sendListStr = new StringBuffer();
    boolean sendFlg = true;
    boolean lRes = true;
    
    SISendMail lSendMail = new SISendMail();
    try {
      lSendMail.setMailTemp(lConnection, "0", SIConfig.SIMAIL_TEMPLATE_KEEP_INSERT);
    } catch (SIFatalException e2) {
      e2.printStackTrace();
    }
    StringBuffer lMailBuf = new StringBuffer();
    
    Statement lStatement = null;
    ResultSet lResultSet1 = null;
    ResultSet lResultSet2 = null;
    ResultSet lResultSet3 = null;
    
    if (SIDBUtil.hasData(lConnection, "SELECT * FROM orderlatestvw WHERE shippmentmaildate IS NOT NULL AND ordercode='" + ordercode + "'")) return false;
    String reservemsg = "";
    if (SIBGUtil.isOrderReserve(lConnection, ordercode)) reservemsg = "※予約商品含む";
    if (SIDBUtil.hasData(lConnection, "SELECT * FROM ordertbl WHERE ordercode='" + ordercode + "' AND keepmaildate IS NOT NULL")) lSendMail.appendMailTitle("【再送】", "【受注番号："
        + ordercode + "】" + reservemsg);
    else {
      lSendMail.appendMailTitle("", "【受注番号：" + ordercode + "】" + reservemsg);
    }
    StringBuffer lSqlBuf1 = new StringBuffer();
    lSqlBuf1.append("SELECT b.branchname,c.chargename,c.email,a.custname,a.initdatetime,a.paymethodname,a.amount ");
    lSqlBuf1.append("FROM (SELECT oh.ordercode,oh.branchcode,oh.chargecode,oh.custname,oh.initdatetime,oh.paymethodname,count(od.individualcode) AS amount ");
    lSqlBuf1.append("FROM orderlatestvw oh,orderdetaillatestvw od WHERE oh.ordercode=od.ordercode AND oh.ordercode='").append(ordercode).append("' ");
    lSqlBuf1.append(" AND od.setdetailflg <> ").append(SIDBUtil.SQL2Str(SIConfig.SET_DETAIL_FLG_OPTION, " ")); // EDBTG003-00 elecs-matsushima add
    lSqlBuf1.append("GROUP BY oh.ordercode,oh.branchcode,oh.chargecode,oh.custname,oh.initdatetime,oh.paymethodname) a ");
    lSqlBuf1.append("LEFT OUTER JOIN (SELECT chargecode,chargename,email FROM chargetbl) c ");
    lSqlBuf1.append("ON (a.chargecode=c.chargecode) ");
    lSqlBuf1.append("LEFT OUTER JOIN (SELECT branchcode,branchname FROM branchtbl) b ");
    lSqlBuf1.append("ON (a.branchcode=b.branchcode) ");
    
    lStatement = lConnection.createStatement();
    lResultSet1 = lStatement.executeQuery(lSqlBuf1.toString());
    
    String allocate = "";
    String branchlog = "";
    String branch = "";
    String charge = "";
    
    while (lResultSet1.next()) {
      lMailBuf.append("\n-------------");
      lMailBuf.append("\n受注番号:").append(ordercode);
      if (SIUtil.isNull(lResultSet1.getString("branchname"))) branch = "なし";
      else branch = lResultSet1.getString("branchname") + "支店";
      lMailBuf.append("\n担当支店:").append(branch);
      if (SIUtil.isNull(lResultSet1.getString("chargename"))) charge = "なし";
      else charge = lResultSet1.getString("chargename");
      lMailBuf.append("　担当者:").append(charge);
      lMailBuf.append("\n顧客名:").append(lResultSet1.getString("custname"));
      lMailBuf.append("\n支払方法:").append(lResultSet1.getString("paymethodname"));
      lMailBuf.append("\n取り置き商品数:").append(lResultSet1.getString("amount")).append("点");
      lMailBuf.append("\n受注日:").append(SIDBUtil.getDate(lResultSet1.getTimestamp("initdatetime")));
      if (SIUtil.isNotNull(lResultSet1.getString("email"))) {
        if (sendListStr.length() != 0) sendListStr.append(",");
        sendListStr.append(lResultSet1.getString("email"));
      }
    }
    SIDBUtil.close(lStatement, lResultSet1);
    
    StringBuffer lSqlBuf2 = new StringBuffer();
    // EDBTG003-00 elecs-matsushima mod start
//    lSqlBuf2.append("SELECT od.cmdtyname,od.individualcode,coalesce(oa.orderallocationnumber,od.amount) AS amount ");
    lSqlBuf2.append("SELECT od.cmdtyname,od.individualcode,SUM(coalesce(oa.orderallocationnumber,od.amount)) AS amount ");
    // EDBTG003-00 elecs-matsushima mod end
    lSqlBuf2.append(",db.email,db.branchname,oa.branchcode,od.cmdtycode,co.colorname ");
    lSqlBuf2.append("FROM orderdetaillatestvw od ");
    // EDBTG003-00 elecs-matsushima mod start
//    lSqlBuf2.append("LEFT OUTER JOIN (SELECT ordercode,branchcode,cmdtycode,individualcode,orderallocationnumber FROM orderallocationinfotbl) oa ");
//    lSqlBuf2.append("ON (od.ordercode=oa.ordercode AND od.cmdtycode=oa.cmdtycode AND od.individualcode=oa.individualcode) ");
    lSqlBuf2.append("LEFT OUTER JOIN (SELECT ordercode,detailcode,branchcode,cmdtycode,individualcode,orderallocationnumber FROM orderallocationinfotbl) oa ");
    lSqlBuf2.append("ON (od.ordercode=oa.ordercode AND od.cmdtycode=oa.cmdtycode AND od.individualcode=oa.individualcode ");
    lSqlBuf2.append("    AND od.detailcode = oa.detailcode) ");
    // EDBTG003-00 elecs-matsushima mod end
    //lSqlBuf2.append("LEFT OUTER JOIN (SELECT branchcode,branchname,email FROM branchtbl WHERE branchcode<>'18') db ");//物流センターに送らない
    lSqlBuf2.append("LEFT OUTER JOIN branchtbl db ");
    lSqlBuf2.append("ON (oa.branchcode=db.branchcode) ");
    lSqlBuf2.append(",colortbl co ,cmdtymtbl cm ");
    lSqlBuf2.append("WHERE od.ordercode='" + ordercode + "' ");
    lSqlBuf2.append("AND od.cmdtycode = cm.cmdtycode ");
    lSqlBuf2.append("AND co.colorcode = cm.colorcode ");
    // EDBTG003-00 elecs-matsushima add start
    lSqlBuf2.append("AND od.setdetailflg <> ").append(SIDBUtil.SQL2Str(SIConfig.SET_DETAIL_FLG_OPTION," "));
    lSqlBuf2.append(" GROUP BY od.cmdtyname, od.individualcode,db.email,db.branchname,oa.branchcode,od.cmdtycode,co.colorname, db.branchcode ");
    // EDBTG003-00 elecs-matsushima add end
    lSqlBuf2.append("ORDER BY db.branchcode ");
    
    lStatement = lConnection.createStatement();
    lResultSet2 = lStatement.executeQuery(lSqlBuf2.toString());
    
    while (lResultSet2.next()) {
      lMailBuf.append("\n-------------");
      lMailBuf.append("\n商品名:").append(lResultSet2.getString("cmdtyname") + " 【" + lResultSet2.getString("individualcode") + "】");
      if (SIBGUtil.isOrderReserve(lConnection, ordercode, lResultSet2.getString("cmdtycode"), lResultSet2.getString("individualcode"), lResultSet2.getString("branchcode"))) {
        lMailBuf.append("  【予約商品】");
        lMailBuf.append("\n予約番号:").append(
            SIBGUtil.getReserveCode(lConnection, ordercode, lResultSet2.getString("cmdtycode"), lResultSet2.getString("individualcode"), lResultSet2.getString("branchcode")));
      }
      if (SIUtil.isNull(lResultSet2.getString("branchname"))) allocate = "在庫管理なし";
      else allocate = lResultSet2.getString("branchname");
      lMailBuf.append("\nカラー:").append(lResultSet2.getString("colorname"));
      lMailBuf.append("\n取り置き店舗:").append(allocate);
      lMailBuf.append("\n取り置き数量:").append(lResultSet2.getString("amount"));
      if (SIBGUtil.isOrderReserve(lConnection, ordercode, lResultSet2.getString("cmdtycode"), lResultSet2.getString("individualcode"), lResultSet2.getString("branchcode"))) {
        lMailBuf.append("\n※予約割当て時に取り置き依頼済みの商品です。");
      }
      lMailBuf.append("\n");
      if (!allocate.equals(branchlog)&&!"18".equals(lResultSet2.getString("branchcode"))) {// 一つ前と同じ支店の場合はアドレスを追加しない
        branchlog = allocate;
        if (SIUtil.isNotNull(lResultSet2.getString("email"))) {
          if (sendListStr.length() != 0) sendListStr.append(",");
          sendListStr.append(lResultSet2.getString("email"));
        }
      }
    }
    SIDBUtil.close(lStatement, lResultSet2);
    lMailBuf.append("\n-------------");
    lMailBuf.append("\n");
    
    StringBuffer lSqlBuf3 = new StringBuffer();
    lSqlBuf3.append("SELECT a.reservecode,a.cmdtycode,a.individualcode,a.amount,c.orderallocationnumber,d.branchname ");
    lSqlBuf3.append("FROM reserveordertbl AS a,orderreservetbl AS b,orderallocationinfotbl AS c,branchtbl AS d ");
    lSqlBuf3.append("WHERE b.ordercode=").append(SIDBUtil.SQL2Str(ordercode)).append(" AND b.ordercode=c.ordercode ");
    lSqlBuf3.append("AND a.reservecode=b.reservecode AND a.reservebranch=c.branchcode AND a.reservebranch=d.branchcode ");
    lSqlBuf3.append("AND a.individualcode=c.individualcode AND a.amount > c.orderallocationnumber");
    
    lStatement = lConnection.createStatement();
    lResultSet3 = lStatement.executeQuery(lSqlBuf3.toString());
    
    while (lResultSet3.next()) {
      if (lResultSet3.isFirst()) lMailBuf.append("\n★★★数量変更された予約商品が存在します★★★");
      lMailBuf.append("\n予約番号:").append(lResultSet3.getString(1));
      lMailBuf.append("\n商品名:").append(SIBGUtil.getCmdtyFullName(lConnection, lResultSet3.getString(2), lResultSet3.getString(3)));
      lMailBuf.append("\n取り置き店舗:").append(lResultSet3.getString(6));
      lMailBuf.append("\n取り置き数量（予約）:").append(lResultSet3.getString(4));
      lMailBuf.append("\n取り置き数量（受注）:").append(lResultSet3.getString(5));
      lMailBuf.append("\n");
      if (lResultSet3.isLast()) lMailBuf.append("\n-------------");
    }
    SIDBUtil.close(lStatement, lResultSet3);
    lSendMail.appendContent1(lMailBuf.toString());
    
    try {
      lSendMail.setFromMailAddress(fromMail);
      lSendMail.setToMailAddress(sendListStr.toString().split(","));
      sendFlg = lSendMail.execute();
      lRes = lRes && sendFlg;
      lStatement.close();
      lResultSet1.close();
      lResultSet2.close();
      
      SIDateTime lDateTime = new SIDateTime();
      SIModifyRec lRec = new SIModifyRec("ordertbl");
      lRec.addCondition("ordercode", ordercode);
      lRec.addCondition("enabledflg", "1");
      lRec.add("keepmaildate", lDateTime.getFullDateTime());// 現在時刻
      lRec.execute(lConnection);
      
    } catch (SIFatalException e) {
      lRes = false;
      e.printStackTrace();
    } catch (SIDuplicateKeyException e) {
      lRes = false;
      e.printStackTrace();
    }
    
    if (sendFlg) {
      try {
        lConnection.commit();
      } catch (SQLException sqle) {
        throw new SIDBAccessException(sqle);
      }
    } else {
      try {
        lConnection.rollback();
      } catch (SQLException sqle) {
        throw new SIDBAccessException(sqle);
      }
    }
    log.debug("sendmail:" + lRes);
    return lRes;
  }
  
  private class ShippmentRequestHeader {
    String orderCode;
    String sendMailDatetime;
    String deliveryName;
    String deliveryAddressee;
    String postCode1;
    String postCode2;
    String address1;
    String address2;
    String address3;
    String companyName;
    String tel;
    String fax;
    String memo;
    String daibikiBranchCode;
    String daibikiPrice;
    Collection itemColl = new ArrayList();
  }
  
  private class ShippmentRequestDetail {
    String individualCode;
    String cmdtyName;
    String amount;
    String branchCode;
    String deliveryDate;
    String deliveryTime;
    String setItem1;
    String setItem2;
    String outputCode;
    String detailCode;
  }
}