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

import java.io.IOException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
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.SICust;
import jp.co.sint.basic.SILogin;
import jp.co.sint.basic.SIMailTemp;
import jp.co.sint.basic.SIReserveOrder;
import jp.co.sint.beans.front.UIReserve;
import jp.co.sint.beans.mallmgr.UIRegNewOrderCust;
import jp.co.sint.beans.mallmgr.UIReserveAssignListCond;
import jp.co.sint.beans.mallmgr.UIReserveListCond;
import jp.co.sint.config.SIConfig;
import jp.co.sint.config.SIDBMultiConf;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.database.SIDatabaseConnection;
import jp.co.sint.database.SIDateTimeType;
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.mail.SISendMail;
import jp.co.sint.servlet.SIServlet;
import jp.co.sint.tools.SIBGUtil;
import jp.co.sint.tools.SICustomError;
import jp.co.sint.tools.SICustomErrors;
import jp.co.sint.tools.SIDateTime;
import jp.co.sint.tools.SIErrorFactory;
import jp.co.sint.tools.SIFatalException;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;
import jp.co.sint.tools.SIURLParameter;

// 7.1.1 ST0236 追加

/**
 * @version $Id: SIRegReserveSrv.java,v 1.0 2004/01/29 Exp $
 * @author Jinwang Chen <br>
 * Description:
 * <p>
 * History
 * </p>
 * <p>
 * Author&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Reason
 * </p>
 * ============&nbsp;&nbsp;&nbsp;==========&nbsp;&nbsp;===========================<br>
 * Jinwang Chen 2004/01/29 12:21:31 Original
 */
public class SIRegReserveSrv 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);
    if (!manLogin.isLogin()) {
      forwardKey(request, response, "webshop.jsp.manager.login");
      return;
    }
    
    HttpSession session = request.getSession(true); // セッションの取得
    SIDatabaseConnection databaseConnection = new SIDatabaseConnection();
    SIURLParameter urlParam = new SIURLParameter(request);// 7.1.1 ST0236 追加
    // DBへのコネクションの作成
    try {
      String actionName = this.getActionName(urlParam);// 画面からのアクション //7.1.1 ST0236 修正
      // 7.3.0 PI-NES0601 追加 ここから
      if (this.getServletPath(request).equals("/mallmgr/RegNewReserveSrv")) {// 新規予約画面から
        if (SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName)) {// 新規予約登録(商品選択)
          UIReserveListCond listCond = new UIReserveListCond();
          listCond.initCmdty(request, urlParam);
          listCond.validateCmdty(request);
          session.removeAttribute(SIConfig.SISESSION_MAN_RESERVEORDER_UPDATE_NAME);
          session.setAttribute(SIConfig.SISESSION_MAN_RESERVEORDER_CMDTYLIST_NAME, listCond);
          forwardKey(request, response, "webshop.jsp.manager.reserve.cmdty.list");
        } else if (SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)) {// //予約リストに遷移する
          forwardKey(request, response, "webshop.jsp.manager.reserve.list");
        } else if (SIConfig.SIACTION_DETAIL.equalsIgnoreCase(actionName)) {// 商品を選択する
          UIReserve reserve = new UIReserve();
          reserve.initCmdty(request, urlParam);
          UIReserveListCond listCond = new UIReserveListCond();
          listCond.initCmdty(request, urlParam);
          listCond.validateCmdty(request);
          session.setAttribute(SIConfig.SISESSION_MAN_RESERVEORDER_CMDTYLIST_NAME, listCond);
          if (reserve.validateAmount(true, request, databaseConnection.getConnection())) {
            session.setAttribute(SIConfig.SISESSION_MAN_RESERVEORDER_UPDATE_NAME, reserve);
          }
          forwardKey(request, response, "webshop.jsp.manager.reserve.cmdty.list");
        } else if (SIConfig.SIACTION_REDO.equalsIgnoreCase(actionName)) {// 予約数量変更
          UIReserve reserve = (UIReserve) session.getAttribute(SIConfig.SISESSION_MAN_RESERVEORDER_UPDATE_NAME);
          reserve.initCmdtyAmount(request, urlParam);
          reserve.validateAmount(true, request, databaseConnection.getConnection());
          session.setAttribute(SIConfig.SISESSION_MAN_RESERVEORDER_UPDATE_NAME, reserve);
          forwardKey(request, response, "webshop.jsp.manager.reserve.cmdty.list");
        }
      } else if (this.getServletPath(request).equals("/mallmgr/RegNewCustReserveSrv")) {// 新規予約（顧客選択）
        if (SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName)) {
          UIRegNewOrderCust newOrderCust = new UIRegNewOrderCust();
          // データの取得とデータのチェック
          newOrderCust.init(request, urlParam);
          newOrderCust.initCustCode(urlParam);
          newOrderCust.validate(request);
          session.setAttribute(SIConfig.SISESSION_MAN_NEW_RESERVE_CUST_LIST_NAME, newOrderCust);
          forwardKey(request, response, "webshop.jsp.manager.reserve.cust.list");
        } else if (SIConfig.SIACTION_CMDTY.equalsIgnoreCase(actionName)) {// 商品選択画面に戻る
          forwardKey(request, response, "webshop.jsp.manager.reserve.cmdty.list");
        } else if (SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)) {// 新規顧客登録画面から新規予約（顧客選択）に戻る
          forwardKey(request, response, "webshop.jsp.manager.reserve.cust.list");
        } else if (SIConfig.SIACTION_REGIST.equalsIgnoreCase(actionName)) {// 予約登録
          UIReserve reserve = (UIReserve) session.getAttribute(SIConfig.SISESSION_MAN_RESERVEORDER_UPDATE_NAME);
          UIRegNewOrderCust newOrderCust = new UIRegNewOrderCust();
          newOrderCust.initCustCode(urlParam);
          SICust cust = null;
          String userName = manLogin.getUserName();
          try {
            cust = newOrderCust.reset(databaseConnection.getConnection());
            if (reserve != null && cust != null && SIUtil.isNotNull(cust.getCustCode())) {
              try {
                insertReserveOrderTbl(request, databaseConnection.getConnection(), reserve, cust, userName);
                try {
                  databaseConnection.getConnection().commit();
                } catch (SQLException sqle) {}
                forwardKey(request, response, "webshop.jsp.manager.reserve.res");
              } catch (SIDBAccessException e1) {
                try {
                  databaseConnection.getConnection().rollback();
                } catch (SQLException ee) {}
                e1.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.reserve.cust.list");
              }
            } else {
              SICustomErrors errors = new SICustomErrors();
              errors.addError(new SICustomError("input.data.check.require", "顧客"));
              request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
              forwardKey(request, response, "webshop.jsp.manager.reserve.cust.list");
            }
          } catch (SIDBAccessException e2) {
            e2.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.reserve.cust.list");
          }
        } else {// カート情報のクリア
          UIReserve reserve = (UIReserve) session.getAttribute(SIConfig.SISESSION_MAN_RESERVEORDER_UPDATE_NAME);
          if (reserve == null) {
            SICustomErrors errors = new SICustomErrors();
            errors.addError(new SICustomError("input.data.check.require", "商品"));
            request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
            forwardKey(request, response, "webshop.jsp.manager.reserve.cmdty.list");
          } else if (!reserve.validateAmount(true, request, databaseConnection.getConnection())) {
            forwardKey(request, response, "webshop.jsp.manager.reserve.cmdty.list");
          } else {
            UIRegNewOrderCust newOrderCust = new UIRegNewOrderCust();
            // データの取得とデータのチェック
            newOrderCust.init(request, urlParam);
            session.setAttribute(SIConfig.SISESSION_MAN_NEW_RESERVE_CUST_LIST_NAME, newOrderCust);
            forwardKey(request, response, "webshop.jsp.manager.reserve.cust.list");
          }
        }
        // 7.3.0 PI-NES0601 追加 ここまで
      } else {
        if (SIConfig.SIACTION_LIST.equalsIgnoreCase(actionName)) {// 予約リスト
          session.setAttribute(SIConfig.SISESSION_MAN_NAVIGATER_LIST_NAME, SIConfig.SINAVI_RESERVE_LIST);
          // 一覧と検索などの画面のレコードの表示
          UIReserveListCond listCond = new UIReserveListCond();
          // データの取得
          listCond = new UIReserveListCond(request, urlParam);// 7.1.1 ST0236 修正
          // データのチェック
          listCond.validate(request);
          session.setAttribute(SIConfig.SISESSION_MAN_RESERVEORDER_LIST_NAME, listCond);
          forwardKey(request, response, "webshop.jsp.manager.reserve.list");
        } else if (SIConfig.SIACTION_CANCEL.equalsIgnoreCase(actionName)) {// 予約の一括キャンセル
          UIReserveListCond listCond = (UIReserveListCond) session.getAttribute(SIConfig.SISESSION_MAN_RESERVEORDER_LIST_NAME);
          if (listCond == null) listCond = new UIReserveListCond();
          int check = reserveCancelAll(request, databaseConnection.getConnection(), listCond);
          if (check>=0) {// 現在の検索結果に対し期限切れを一括キャンセル
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.freeword","現在の検索結果に対し期限切れ一括キャンセルを行いました（対象："+check+"件）"));
            try {databaseConnection.getConnection().commit();} catch (SQLException sqle) {}
          } else {
            try {databaseConnection.getConnection().rollback();} catch (SQLException sqle) {}
          }
          forwardKey(request, response, "webshop.jsp.manager.reserve.list");
        } else if (SIConfig.SIACTION_ADD.equalsIgnoreCase(actionName)) {// 予約の一括割り当て
          UIReserveListCond listCond = (UIReserveListCond) session.getAttribute(SIConfig.SISESSION_MAN_RESERVEORDER_LIST_NAME);
          if (listCond == null) listCond = new UIReserveListCond();
          int check = reserveAssignAll(request, databaseConnection.getConnection(), listCond);
          if (check>=0) {// 現在の検索結果に対し割り当て可能な在庫を一括割り当て
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.freeword","現在の検索結果に対し予約商品一括割り当てを行いました（対象："+check+"件）"));
            try {databaseConnection.getConnection().commit();} catch (SQLException sqle) {}
          } else {
            try {databaseConnection.getConnection().rollback();} catch (SQLException sqle) {}
          }
          forwardKey(request, response, "webshop.jsp.manager.reserve.list");
        } else if (SIConfig.SIACTION_MAIL2.equalsIgnoreCase(actionName)) {// 予約の一括メール送信
          UIReserveListCond listCond = (UIReserveListCond) session.getAttribute(SIConfig.SISESSION_MAN_RESERVEORDER_LIST_NAME);
          if (listCond == null) listCond = new UIReserveListCond();
          int[] check = sendAssignMailAll(request, databaseConnection.getConnection(), listCond);
          if (check[0]>=0) {// 現在の検索結果に対し予約入荷メール一括送信
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.freeword","現在の検索結果に対し未送信分の予約割当メールを一括送信しました（対象："+check[0]+"件中"+check[1]+"件送信成功）"));
            try {databaseConnection.getConnection().commit();} catch (SQLException sqle) {}
          } else {
            try {databaseConnection.getConnection().rollback();} catch (SQLException sqle) {}
          }
          forwardKey(request, response, "webshop.jsp.manager.reserve.list");
        } else if (SIConfig.SIACTION_UPDATE.equalsIgnoreCase(actionName)) {// 新規予約登録(商品選択)に遷移する
          session.removeAttribute(SIConfig.SISESSION_MAN_RESERVEORDER_UPDATE_NAME);
          session.removeAttribute(SIConfig.SISESSION_MAN_RESERVEORDER_CMDTYLIST_NAME);
          forwardKey(request, response, "webshop.jsp.manager.reserve.cmdty.list");
        } else if (SIConfig.SIACTION_DETAIL.equalsIgnoreCase(actionName)) {// 予約詳細画面
          UIReserveAssignListCond listCond = new UIReserveAssignListCond();
          listCond.init(request, databaseConnection.getConnection(), urlParam);// 7.1.1 ST0236 修正
          session.setAttribute(SIConfig.SISESSION_MAN_RESERVE_ASSIGN_LIST_NAME, listCond);
          forwardKey(request, response, "webshop.jsp.manager.reserveassign.list");
        } else if (SIConfig.SIACTION_CLEAR.equalsIgnoreCase(actionName)) {// カート状態の一括解除
          UIReserveListCond listCond = (UIReserveListCond) session.getAttribute(SIConfig.SISESSION_MAN_RESERVEORDER_LIST_NAME);
          if (listCond == null) listCond = new UIReserveListCond();
          if (clearReserveStatus(request, databaseConnection.getConnection(), listCond)) {// 現在の検索結果に対しカート状態を一括解除
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.freeword","現在の検索結果に対しカート状態の解除を行いました"));
            try {
              databaseConnection.getConnection().commit();
            } catch (SQLException sqle) {}
          } else {
            try {
              databaseConnection.getConnection().rollback();
            } catch (SQLException sqle) {}
          }
          forwardKey(request, response, "webshop.jsp.manager.reserve.list");
        } else if (SIConfig.SIACTION_DELETE.equalsIgnoreCase(actionName)) {// 予約のキャンセル
          UIReserveAssignListCond listCond = new UIReserveAssignListCond();
          listCond.init(request, databaseConnection.getConnection(), urlParam);
          if (listCond.validateDelete(request, databaseConnection.getConnection())) {
            try {
              String fromMail = SIDBUtil.getFirstData(databaseConnection.getConnection(), "SELECT email FROM chargetbl WHERE chargecode="
                  + SIDBUtil.SQL2Str(manLogin.getUserCode()));
              deleteTableData(databaseConnection.getConnection(), listCond, fromMail);
              // 7.1.1 ST0155 削除 ここから
              // request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,
              // SIErrorFactory.getErrorMsg("manager.message.success.delete"));
              // 7.1.1 ST0155 削除 ここまで
              try {
                databaseConnection.getConnection().commit();
              } catch (SQLException sqle) {}
              listCond.initTotalOfAssignAmount(databaseConnection.getConnection());
              session.setAttribute(SIConfig.SISESSION_MAN_RESERVE_ASSIGN_LIST_NAME, listCond);
            } catch (SIDBAccessException sqle) {
              try {
                databaseConnection.getConnection().rollback();
              } catch (SQLException ee) {}
              request.removeAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME);// 7.1.1 ST0155 追加
              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.reserveassign.list");
        } else if (SIConfig.SIACTION_REGIST.equalsIgnoreCase(actionName)) {// 予約フラグの修正
          UIReserveAssignListCond listCond = new UIReserveAssignListCond();
          listCond.init(request, databaseConnection.getConnection(), urlParam);// 7.1.1 ST0236 修正
          if (listCond.validate(request, databaseConnection.getConnection())) {
            try {
              String fromMail = SIDBUtil.getFirstData(databaseConnection.getConnection(), "SELECT email FROM chargetbl WHERE chargecode="
                  + SIDBUtil.SQL2Str(manLogin.getUserCode()));
              updateTableData(databaseConnection.getConnection(), listCond, fromMail);
              request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.insert"));
              try {
                databaseConnection.getConnection().commit();
              } catch (SQLException sqle) {}
              listCond.initTotalOfAssignAmount(databaseConnection.getConnection());
              session.setAttribute(SIConfig.SISESSION_MAN_RESERVE_ASSIGN_LIST_NAME, listCond);
            } 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);
            } catch (SIFatalException fe) {
              try {
                databaseConnection.getConnection().rollback();
              } catch (SQLException ee) {}
              fe.printStackTrace();
              SICustomErrors errors = new SICustomErrors();
              errors.addError(new SICustomError("manager.app.exceed.amount", fe.getExceptionMessage()));
              request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
            }
          }
          forwardKey(request, response, "webshop.jsp.manager.reserveassign.list");
        } else if (SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)) { // 詳細から戻る
          forwardKey(request, response, "webshop.jsp.manager.reserve.list");
        } else if (SIConfig.SIACTION_MAIL.equalsIgnoreCase(actionName)) { // メール
          UIReserveAssignListCond listCond = new UIReserveAssignListCond();
          listCond.init(request, databaseConnection.getConnection(), urlParam);// 7.1.1 ST0236 修正
          if (listCond.validateMail(request, databaseConnection.getConnection())) {
            try {
              if (sendShipmentMail(request, databaseConnection.getConnection(), listCond)) {// メールの送信
                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 ee) {}
            } catch (SIDBAccessException e) {// DBへの更新エラー
              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, errors);
            }
          }
          forwardKey(request, response, "webshop.jsp.manager.reserveassign.list");
        }
      }
    } catch (SQLException e) {
      e.printStackTrace();
      throw new ServletException();
    } catch (NamingException e) {
      e.printStackTrace();
      throw new ServletException();
    } finally {
      databaseConnection.close();
    }
  }
  
  /**
   * <b>insertReserveOrderTbl</b> 予約商品を新規登録します。
   * 
   * @param lConnection DBへのコネクション
   * @param reserve 挿入するデータ
   * @param lUserInfo 顧客情報
   * @return なし
   * @throws SIDBAccessException
   */
  private void insertReserveOrderTbl(HttpServletRequest lRequest, Connection lConnection, UIReserve reserve, SICust cust, String userName) throws SIDBAccessException {
    String lReserveDateTime = (new SIDateTime()).getFullDateTime();
    SIURLParameter urlParam = new SIURLParameter(lRequest);
    // 予約番号の采番
    String lReserveOrderCode = SIDBUtil.getFirstData(lConnection, SIDBUtil.getNextVal("RESERVEORDERTBL_RESERVECD_SEQ"));
    // 予約登録成功画面に表示するため
    reserve.setReserveCodeTxt(lReserveOrderCode);
    reserve.setCustCode(cust.getCustCode());
    reserve.setCustName(cust.getCustName());
    SICartCmdty lCartCmdty = new SICartCmdty(reserve.getShopCode(), reserve.getRsrvCmdtyCodeTxt(), reserve.getRsrvIndividualCodeTxt(), reserve.getRsrvStndrdCodeTxt1(), reserve
        .getRsrvElementCodeTxt1(), reserve.getRsrvStndrdCodeTxt2(), reserve.getRsrvElementCodeTxt2());
    lCartCmdty.reset(lConnection);
    
    SIInsertRec lRec = new SIInsertRec("ReserveOrderTbl");
    lRec.add("ReserveCode", lReserveOrderCode);// 予約番号
    lRec.add("CustCode", cust.getCustCode());// 顧客コード
    lRec.add("CustName", cust.getCustName());// 顧客名
    lRec.add("CustPronName", cust.getCustPronName());// 顧客名カナ
    lRec.add("Email", cust.getEmail());// EMAIL
    lRec.add("ShopCode", reserve.getShopCode());// ショップコード
    lRec.add("ShopName", lCartCmdty.getFrontShopName());// ショップ名
    lRec.add("CmdtyCode", reserve.getRsrvCmdtyCodeTxt());// 商品コード
    lRec.add("IndividualCode", reserve.getRsrvIndividualCodeTxt());// 在庫コード 7.3.0 PI-NES0501 追加
    lRec.add("CmdtyName", lCartCmdty.getCmdtyName());// 商品名
    lRec.add("StndrdCode1", reserve.getRsrvStndrdCodeTxt1());// 規格コード１
    lRec.add("ElementCode1", reserve.getRsrvElementCodeTxt1());// エレメントコード１
    lRec.add("StndrdCode2", reserve.getRsrvStndrdCodeTxt2());// 規格コード２
    lRec.add("ElementCode2", reserve.getRsrvElementCodeTxt2());// エレメントコード２
    lRec.add("Amount", reserve.getAmountTxt());// 数量
    lRec.add("SplitFlg", reserve.getSplitFlgRdo());// 分割可能
    if (SIDBMultiConf.SIDB_CURRENT_INX == SIDBMultiConf.SIDB_POSTGRESQL_INX) {
      lRec.add("ReserveDateTime", lReserveDateTime);// 予約日時
    } else {
      lRec.add("ReserveDateTime", new SIDateTimeType(lReserveDateTime));// 予約日時
    }
    lRec.add("ReserveFlg", "0");// 予約状態フラグ
    lRec.add("InitUserName", userName);
    lRec.add("ArrivalScheduleMsg",lCartCmdty.getArrivalScheduleMsg());// 入荷時期コメント
    
    try {
      // 商品マスタテーブルの挿入
      lRec.execute(lConnection);
      String rsrvAmount = SIDBUtil.getFirstData(lConnection, "SELECT rsrvamount FROM individualtbl WHERE cmdtycode='" + reserve.getRsrvCmdtyCodeTxt() + "' AND individualcode='"
          + reserve.getRsrvIndividualCodeTxt() + "'");
      SIModifyRec lMod = new SIModifyRec("Individualtbl");
      if (SIUtil.isNotNull(rsrvAmount)) {
        lMod.addCondition("CmdtyCode", reserve.getRsrvCmdtyCodeTxt());// 商品コード
        lMod.addCondition("IndividualCode", reserve.getRsrvIndividualCodeTxt());// 在庫コード
        lMod.add("RsrvAmount", SIUtil.sub(rsrvAmount, reserve.getAmountTxt()));
        lMod.execute(lConnection);
      }
      reserve.setReserveCodeTxt(lReserveOrderCode);
    } catch (SIDuplicateKeyException e) {
      throw new SIDBAccessException(e);
    }
    
    // 予約受付メールを送信
    StringBuffer lMailBuf = new StringBuffer();
    SISendMail lSendMail = new SISendMail();
    try {
      lSendMail.setMailTemp(lConnection, reserve.getShopCode(), SIConfig.SIMAIL_TEMPLATE_RESERVE_COMFIRM);
    } catch (SIFatalException e2) {
      e2.printStackTrace();
    }
    
    lMailBuf.append("\n予約日時:" + lReserveDateTime);
    lMailBuf.append("\n予約番号:" + lReserveOrderCode);
    
    // メールの本文の作成(予約商品情報)
    lMailBuf.append("\n\nご予約商品");
    lMailBuf.append("\n=============");
    lMailBuf.append("\n商品コード:").append(lCartCmdty.getIndividualCode());
    lMailBuf.append("\n商品名:").append(lCartCmdty.getCmdtyName());
    lMailBuf.append("\n予約数:").append(reserve.getAmountTxt());
    lMailBuf.append("\n=============");
    
    // 14.メールの本文の作成(メールテンプレートの内容2)
    lMailBuf.append("\n");
    lSendMail.appendContent1(lMailBuf.toString());
    lMailBuf = new StringBuffer();
    // 18.メールの本文の作成(メールテンプレートの内容3)
    lSendMail.appendContent2(lMailBuf.toString());
    
    // 21.送信開始
    try {
      lSendMail.setToMailAddress(cust.getEmail());
      String mailSendFlg = (String) urlParam.getParam("mailSendFlg");
      lRequest.setAttribute("mailSendFlg", mailSendFlg);
      if (SIUtil.isNotNull(mailSendFlg) && mailSendFlg.equals("1")) {
        lSendMail.execute();
      }
    } catch (SIFatalException e) {
      e.printStackTrace();
    }
  }
  
  /**
   * <b>deleteTableData</b> データベースのレコードを強制的にキャンセル状態にします。
   * 
   * @param lConnection DBへのコネクション
   * @param lListCond 削除するデータ
   * @return なし
   * @throws SIDBAccessException
   */
  private void deleteTableData(Connection lConnection, UIReserveAssignListCond lListCond, String fromMail) throws SIDBAccessException {
    if (lListCond.getReserveCodeChk() == null) return;
    
    SIModifyRec lRec = new SIModifyRec();
    SIModifyRec lRec2 = new SIModifyRec();
    String lReserveCode = "";
    try {
      for (int ii = 0; ii < lListCond.getReserveCodeChk().length; ii++) {
        lReserveCode = lListCond.getReserveCodeChk()[ii];
        SIReserveOrder lReserveOrder = new SIReserveOrder(lReserveCode);
        lReserveOrder.reset(lConnection);
        if (SIUtil.isNotNull(lReserveCode) && !lReserveOrder.getReserveFlg().equals("3") && !lReserveOrder.getReserveFlg().equals("5")) {
          
          // 0.割当解除メールの送信
          SIBGUtil.sendReserveDeleteMail(lConnection, lReserveCode, fromMail);
          
          // 1.予約のレコードのキャンセル
          lRec = new SIModifyRec("ReserveOrderTbl");
          lRec.addCondition("ReserveCode", lReserveCode); // 予約コード
          lRec.add("ReserveFlg", "2");// 予約状態フラグ
          lRec.add("ReserveBranch", "");// 予約状態フラグ
          lRec.execute(lConnection);
          
          // 2.在庫数量管理かつ割当済みの場合には、割当済み数量を在庫に戻す
          
          StringBuffer lSqlBuf = new StringBuffer();
          lSqlBuf.append("SELECT AmountFlg FROM IndividualTbl ");
          lSqlBuf.append("WHERE CmdtyCode=").append(SIDBUtil.SQL2Str(lReserveOrder.getCmdtyCode(), " "));
          lSqlBuf.append("AND IndividualCode=").append(SIDBUtil.SQL2Str(lReserveOrder.getIndividualCode(), " "));
          String lAmountFlg = SIDBUtil.getFirstData(lConnection, lSqlBuf.toString());
          
          if (lAmountFlg.equals("1") && lReserveOrder.getReserveFlg().equals("1")) {
            SISpcType lSpc = new SISpcType("Amount+" + lReserveOrder.getAssignAmount());
            lRec2 = new SIModifyRec("StoreStockTbl");
            lRec2.addCondition("CmdtyCode", lReserveOrder.getCmdtyCode());
            lRec2.addCondition("IndividualCode", lReserveOrder.getIndividualCode());
            lRec2.addCondition("BranchCode", lReserveOrder.getReserveBranchCode());
            lRec2.add("Amount", lSpc);
            lRec2.execute(lConnection);
          }
          
          lSqlBuf = new StringBuffer();
          lSqlBuf.append("SELECT RsrvAmount FROM IndividualTbl ");
          lSqlBuf.append("WHERE CmdtyCode=").append(SIDBUtil.SQL2Str(lReserveOrder.getCmdtyCode(), " "));
          lSqlBuf.append("AND IndividualCode=").append(SIDBUtil.SQL2Str(lReserveOrder.getIndividualCode(), " "));
          String lRsrvAmount = SIDBUtil.getFirstData(lConnection, lSqlBuf.toString());
          
          // キャンセル→キャンセル時以外で予約可能数が無制限でない場合、予約可能数を商品マスタに引き戻す
          if (!lReserveOrder.getReserveFlg().equals("2") && SIUtil.isNotNull(lRsrvAmount)) {// キャンセル
            SISpcType lSpc = new SISpcType("RsrvAmount+" + lReserveOrder.getAmount());
            
            // 予約可能数量の更新
            lRec = new SIModifyRec("IndividualTbl");
            lRec.addCondition("CmdtyCode", lReserveOrder.getCmdtyCode());
            lRec.addCondition("IndividualCode", lReserveOrder.getIndividualCode());
            lRec.add("RsrvAmount", lSpc);
            lRec.execute(lConnection);
          }
        }
      }
    } catch (SIDuplicateKeyException e) {
      log.debug(e);
      throw new SIDBAccessException(e);
    } catch (SQLException sqle) {
      log.debug(sqle);
      throw new SIDBAccessException(sqle);
    }
  }
  
  private boolean reserveCancel(Connection lConnection, SIReserveOrder lReserveOrder, String fromMail) throws SIDBAccessException {
    if (SIUtil.isNull(lReserveOrder.getReserveCode())||!lReserveOrder.getReserveFlg().equals("1")) return false;
    SIModifyRec lRec = new SIModifyRec();
    SIModifyRec lRec2 = new SIModifyRec();
    try {
      // 1.割当解除メールの送信
      SIBGUtil.sendReserveDeleteMail(lConnection, lReserveOrder.getReserveCode(), fromMail);
      
      // 2.予約のレコードのキャンセル
      lRec = new SIModifyRec("ReserveOrderTbl");
      lRec.addCondition("ReserveCode", lReserveOrder.getReserveCode()); // 予約コード
      lRec.add("ReserveFlg", "2");// 予約状態フラグ
      lRec.add("ReserveBranch", "");// 予約状態フラグ
      lRec.execute(lConnection);
      
      // 3.割当済み数量を在庫に戻す
      SISpcType lSpc = new SISpcType("Amount+" + lReserveOrder.getAssignAmount());
      lRec2 = new SIModifyRec("StoreStockTbl");
      lRec2.addCondition("CmdtyCode", lReserveOrder.getCmdtyCode());
      lRec2.addCondition("IndividualCode", lReserveOrder.getIndividualCode());
      lRec2.addCondition("BranchCode", lReserveOrder.getReserveBranchCode());
      lRec2.add("Amount", lSpc);
      lRec2.execute(lConnection);
      
      lSpc = new SISpcType("RsrvAmount+" + lReserveOrder.getAmount());
      
      // 予約可能数量の更新
      lRec = new SIModifyRec("IndividualTbl");
      lRec.addCondition("CmdtyCode", lReserveOrder.getCmdtyCode());
      lRec.addCondition("IndividualCode", lReserveOrder.getIndividualCode());
      lRec.add("RsrvAmount", lSpc);
      lRec.execute(lConnection);
    } catch (SIDuplicateKeyException e) {
      log.debug(e);
      throw new SIDBAccessException(e);
    } catch (SQLException sqle) {
      log.debug(sqle);
      throw new SIDBAccessException(sqle);
    }
    return true;
  }
  
  private boolean reserveAssign(Connection lConnection,  SIReserveOrder lReserveOrder, String fromMail) throws SIDBAccessException, SIFatalException {
    if (SIUtil.isNull(lReserveOrder.getReserveCode())||!lReserveOrder.getReserveFlg().equals("0")) return false;
    
    SIModifyRec lRec = new SIModifyRec();
    SISpcType lSpc = new SISpcType();
    
    try {
      StringBuffer lSql = new StringBuffer();
      lSql.append("SELECT s.branchCode FROM storeStockTbl s,cmdtymtbl c ");
      lSql.append(" WHERE s.cmdtycode=c.cmdtycode ");
      lSql.append(" AND CASE c.deliverytypecode WHEN 1 THEN s.branchcode NOT IN ('1','2','3','4','5','6','7','8','9','10','11','12','16','19') ELSE s.branchcode NOT IN ('16','19') END ");
      lSql.append(" AND s.individualCode = ").append(SIDBUtil.SQL2Str(lReserveOrder.getIndividualCode()));
      lSql.append(" AND s.amount >= ").append(SIDBUtil.SQL2Str(lReserveOrder.getAmount()));
      lSql.append(" ORDER BY s.amount DESC");
      String branchCode = SIDBUtil.getFirstData(lConnection, lSql.toString());
      
      if (SIUtil.isNotNull(branchCode)) {
        // 1.レコードの予約フラグの更新
        lRec = new SIModifyRec("ReserveOrderTbl");
        lRec.addCondition("ReserveCode", lReserveOrder.getReserveCode());// 予約コード
        lRec.add("AssignAmount", lReserveOrder.getAmount());
        lRec.add("ReserveBranch", branchCode);
        lRec.add("ReserveFlg", "1");
        lRec.execute(lConnection);
        
        // 2.商品マスターの更新
        lRec = new SIModifyRec("StoreStockTbl");
        lRec.addCondition("ShopCode", lReserveOrder.getShopCode());
        lRec.addCondition("CmdtyCode", lReserveOrder.getCmdtyCode());
        lRec.addCondition("IndividualCode", lReserveOrder.getIndividualCode());
        lRec.addCondition("BranchCode", branchCode);
        lSpc = new SISpcType("Amount-" + lReserveOrder.getAmount());
        lRec.add("Amount", lSpc);
        lRec.execute(lConnection);
        
        // 3.取り置き依頼メールの送信
        SIBGUtil.sendReserveMail(lConnection, lReserveOrder.getReserveCode(), fromMail);
      }else{
        return false;
      }
    } catch (SIDuplicateKeyException e) {
      throw new SIDBAccessException(e);
    } catch (SQLException sqle) {
      log.debug(sqle);
      throw new SIDBAccessException(sqle);
    }
    return true;
  }
  
  private boolean sendAssignMail(Connection lConnection, SIReserveOrder lReserveOrder) throws SIDBAccessException {
    if (SIUtil.isNull(lReserveOrder.getReserveCode())||!lReserveOrder.getReserveFlg().equals("1")||SIUtil.isNotNull(lReserveOrder.getShipMailDateTime())) return false;
    boolean lMailResult = true;
    
    SIModifyRec lRec = new SIModifyRec();
    SISendMail lSendMail = new SISendMail();
    SIMailTemp lMailTemp = new SIMailTemp("0",SIConfig.SIMAIL_TEMPLATE_RESERVE_ARRIVE);
    
    try {
      Collection lMailList = new ArrayList();
      lMailList.add(lReserveOrder.getCurrentEmails());
      
      if (lReserveOrder.getShopName().equals("EYELASHGARAGE")) {
        lMailTemp.resetEG(lConnection);
      } else {
        lMailTemp.reset(lConnection);
      }
      lSendMail = new SISendMail(lReserveOrder.getCurrentEmails(), lMailTemp);
      
      lSendMail.appendContent1("\n予約日時:" + lReserveOrder.getReserveDateTime());
      lSendMail.appendContent1("\n予約番号:" + lReserveOrder.getReserveCode());
      lSendMail.appendContent1("\n\nご予約商品");
      lSendMail.appendContent1("\n===============");
      lSendMail.appendContent1("\n商品コード:" + lReserveOrder.getIndividualCode());
      lSendMail.appendContent1("\n商品名:" + lReserveOrder.getCmdtyName());
      lSendMail.appendContent1("\n予約数：" + lReserveOrder.getAmount());
      lSendMail.appendContent1("\n==============\n");
      try {
        lMailResult = lSendMail.execute();
      } catch (SIFatalException e) {
        lMailResult = false;
        e.printStackTrace();
      }
      if (lMailResult) {
        log.debug("sendShipmentMail:lReserveCode=" + lReserveOrder.getReserveCode());
        lRec = new SIModifyRec("ReserveOrderTbl");
        lRec.addCondition("ReserveCode", lReserveOrder.getReserveCode());
        lRec.add("ShipMailDateTime", (new SIDateTime()).getFullDateTime());
        lRec.execute(lConnection);
      }
    } catch (SIDuplicateKeyException e) {
      e.printStackTrace();
      throw new SIDBAccessException();
    }
    return lMailResult;
  }
  /**
   * <b>updateTableData</b> 予約テーブルに予約状況を更新します。
   * 
   * @param lConnection DBへのコネクション
   * @param lListCond 対象のデータレコード
   * @return なし
   * @throws SIDBAccessException
   * @throws SIFatalException
   */
  private void updateTableData(Connection lConnection, UIReserveAssignListCond lListCond, String fromMail) throws SIDBAccessException, SIFatalException {
    if (lListCond.getReserveCodeChk() == null) return;
    SIModifyRec lRec = new SIModifyRec();
    SIReserveOrder lReserveOrder = new SIReserveOrder();
    SISpcType lSpc = new SISpcType();
    // SITableConditionManager lManCondition = new SITableConditionManager();
    // SITableCondition lTableCondition = new SITableCondition();
    
    String lReserveCode = "";
    String lValue = "0";
    String lOldValue = "0";
    String lAddOrSub = "";
    String lNewBranch = "";
    String lOldBranch = "";
    // StringBuffer lSelBuf = new StringBuffer();
    SICartCmdty lCartCmdty = new SICartCmdty();
    try {
      log.debug("lListCond.getReserveCodeChk().length=" + lListCond.getReserveCodeChk().length);
      for (int ii = 0; ii < lListCond.getReserveCodeChk().length; ii++) {
        lReserveCode = lListCond.getReserveCodeChk()[ii];
        lNewBranch = lListCond.getReserveBranch()[ii];
        if (SIUtil.isNotNull(lReserveCode)) {
          lValue = lListCond.getAssignAmount(SIConfig.SIPREFIX_ASSIGN_AMOUNT_NAME + SIConfig.SIWEBSHOPPING_TOKEN + lReserveCode);
          lReserveOrder = new SIReserveOrder(lReserveCode);
          lReserveOrder.reset(lConnection);
          lOldValue = lReserveOrder.getAssignAmount();
          lOldBranch = lReserveOrder.getReserveBranchCode();
          
          if (!(lReserveOrder.getReserveFlg().equals("0") || lReserveOrder.getReserveFlg().equals("1"))) continue;
          
          // 0.取り置き解除メールの送信
          if (!lNewBranch.equals(lOldBranch)) SIBGUtil.sendReserveDeleteMail(lConnection, lReserveCode, fromMail);
          
          // 1.レコードの予約フラグの更新
          lRec = new SIModifyRec("ReserveOrderTbl");
          lRec.addCondition("ReserveCode", lReserveCode);// 予約コード
          lRec.add("AssignAmount", lValue);
          lRec.add("ReserveBranch", lNewBranch);
          
          if (Integer.parseInt(lValue) == 0) {
            lRec.add("ReserveFlg", "0");
          } else {
            lRec.add("ReserveFlg", "1");
          }
          lRec.execute(lConnection);
          lAddOrSub = getAddOrSub(lOldValue, lValue);
          
          // 2.商品マスターの更新
          lCartCmdty = new SICartCmdty(lReserveOrder.getShopCode(), lReserveOrder.getCmdtyCode());
          lCartCmdty.reset(lConnection);
          
          // lSelBuf = new StringBuffer();
          if (lNewBranch.equals(lOldBranch)) {
            if (lCartCmdty.getAmountFlg().equals("1") && SIUtil.isNotNull(lAddOrSub)) {
              lRec = new SIModifyRec("StoreStockTbl");// 7.3.0 PI-NES0501 修正
              lRec.addCondition("ShopCode", lReserveOrder.getShopCode());
              lRec.addCondition("CmdtyCode", lReserveOrder.getCmdtyCode());
              lRec.addCondition("IndividualCode", lReserveOrder.getIndividualCode());
              lRec.addCondition("BranchCode", lNewBranch);
              lSpc = new SISpcType("Amount" + lAddOrSub);
              lRec.add("Amount", lSpc);
              lRec.execute(lConnection);
            }
          } else {
            if (lCartCmdty.getAmountFlg().equals("1")) {
              lRec = new SIModifyRec("StoreStockTbl");
              lRec.addCondition("ShopCode", lReserveOrder.getShopCode());
              lRec.addCondition("CmdtyCode", lReserveOrder.getCmdtyCode());
              lRec.addCondition("IndividualCode", lReserveOrder.getIndividualCode());
              lRec.addCondition("BranchCode", lNewBranch);
              lSpc = new SISpcType("Amount-" + lValue);
              lRec.add("Amount", lSpc);
              lRec.execute(lConnection);
              
              lRec = new SIModifyRec("StoreStockTbl");
              lRec.addCondition("ShopCode", lReserveOrder.getShopCode());
              lRec.addCondition("CmdtyCode", lReserveOrder.getCmdtyCode());
              lRec.addCondition("IndividualCode", lReserveOrder.getIndividualCode());
              lRec.addCondition("BranchCode", lOldBranch);
              lSpc = new SISpcType("Amount+" + lOldValue);
              lRec.add("Amount", lSpc);
              lRec.execute(lConnection);
            }
          }
          
          // 3.取り置き依頼メールの送信
          if (!lNewBranch.equals(lOldBranch)) SIBGUtil.sendReserveMail(lConnection, lReserveCode, fromMail);
          
          /*
           * //数量検索 lSelBuf.append("SELECT Amount FROM CmdtyMVW ");//7.3.0 PI-NES0501 修正 lSelBuf.append("WHERE ShopCode=").append(SIDBUtil.SQL2Str(lReserveOrder.getShopCode(),"
           * ")); lSelBuf.append("AND CmdtyCode=").append(SIDBUtil.SQL2Str(lReserveOrder.getCmdtyCode()," ")); lSelBuf.append("AND
           * IndividualCode=").append(SIDBUtil.SQL2Str(lReserveOrder.getIndividualCode()," "));//7.3.0 PI-NES0501 追加 }else if
           * (lCartCmdty.getAmountFlg().equals("1")&&SIUtil.isNotNull(lAddOrSub)){ lRec=new SIModifyRec("StoreStockTbl");//7.3.0 PI-NES0501 修正
           * lRec.addCondition("ShopCode",lReserveOrder.getShopCode()); lRec.addCondition("CmdtyCode",lReserveOrder.getCmdtyCode());
           * lRec.addCondition("IndividualCode",lReserveOrder.getIndividualCode()); lRec.addCondition("BranchCode","999");//7.3.0 PI-NES0501 追加 //7.3.0 PI-NES0501 削除 lSpc=new
           * SISpcType("Amount"+lAddOrSub); lRec.add("Amount",lSpc); lRec.execute(lConnection); lManCondition=new SITableConditionManager(); lTableCondition=new
           * SITableCondition("","ShopCode",lReserveOrder.getShopCode(),SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_NOT); lTableCondition.setBlankEnable(true);
           * lManCondition.add(lTableCondition); lTableCondition=new
           * SITableCondition("","CmdtyCode",lReserveOrder.getCmdtyCode(),SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND); lTableCondition.setBlankEnable(true);
           * lManCondition.add(lTableCondition); //7.3.0 PI-NES0501 追加 ここから lTableCondition=new
           * SITableCondition("","IndividualCode",lReserveOrder.getIndividualCode(),SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND);
           * lTableCondition.setBlankEnable(true); lManCondition.add(lTableCondition); //7.3.0 PI-NES0501 追加 ここまで lTableCondition=new
           * SITableCondition("","ElementCode1",lReserveOrder.getElementCode1(),SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND);
           * lTableCondition.setBlankEnable(true); lManCondition.add(lTableCondition); lTableCondition=new
           * SITableCondition("","ElementCode2",lReserveOrder.getElementCode2(),SIConfig.SICONDITION_TYPE_EQUAL,SIConfig.SICONDITION_TYPE_AND);
           * lTableCondition.setBlankEnable(true); lManCondition.add(lTableCondition); lSelBuf.append("SELECT Amount FROM CmdtyStndrdMTbl WHERE ");//7.3.0 PI-NES0501 修正
           * lSelBuf.append(lManCondition.getCondtionSQL()); }
           */
          // 7.1.1 ST0152 削除 ここから
          // if(lCartCmdty.getAmountFlg().equals("1")&&SIUtil.isNotNull(lAddOrSub)&&lCartCmdty.getNoStockFlg().equals("0")&&SIUtil.isNotNull(lSelBuf.toString())){
          // String lLeftAmount=SIDBUtil.getFirstData(lConnection,lSelBuf.toString());
          // if (Long.parseLong(lLeftAmount)<0) throw new SIFatalException(lCartCmdty.getCartCmdtyName());
          // }
          // 7.1.1 ST0152 削除 ここまで
        }
      }
    } catch (SIDuplicateKeyException e) {
      throw new SIDBAccessException(e);
    } catch (SQLException sqle) {
      log.debug(sqle);
      throw new SIDBAccessException(sqle);
    }
  }
  
  /**
   * <b>sendShipmentMail</b> 出荷メールを更新します。
   * 
   * @param lRequest
   * @param lConnection DBへのコネクション
   * @param lListCond 対象のデータレコード
   * @return なし
   * @throws SIDBAccessException
   */
  private boolean sendShipmentMail(HttpServletRequest lRequest, Connection lConnection, UIReserveAssignListCond lListCond) throws SIDBAccessException {
    if (lListCond.getReserveCodeChk() == null) return true;
    
    // SILogin lLogin = SIHTMLUtil.getLogin(lRequest);
    boolean lResult = true;
    boolean lMailResult = true;
    String lReserveCode = "";
    // String lEmail = "";
    SIReserveOrder lReserveOrder = new SIReserveOrder();
    SIModifyRec lRec = new SIModifyRec();
    String lMailItemName = "";
    String lNow = (new SIDateTime()).getFullDateTime();
    
    if (lListCond.getMailTypeTxt().equals(SIConfig.SIMAIL_TEMPLATE_RESERVE_NO_ARRIVE)) {
      lMailItemName = "NShipMailDateTime";
    } else lMailItemName = "ShipMailDateTime";
    
    SISendMail lSendMail = new SISendMail();
    SIMailTemp lMailTemp = new SIMailTemp(lListCond.getShopCode(), lListCond.getMailTypeTxt());
    SIMailTemp lMailTempEG = new SIMailTemp(lListCond.getShopCode(), lListCond.getMailTypeTxt());
    try {
      lMailTemp.reset(lConnection);
      lMailTempEG.resetEG(lConnection);
    } catch (SIDBAccessException e1) {
      lResult = false;
      e1.printStackTrace();
    }
    Collection lMailList = new ArrayList();
    
    try {
      for (int ii = 0; ii < lListCond.getReserveCodeChk().length; ii++) {
        lReserveCode = lListCond.getReserveCodeChk()[ii];
        if (SIUtil.isNotNull(lReserveCode)) {
          log.debug("sendShipmentMail:lReserveCode=" + lReserveCode);
          lReserveOrder = new SIReserveOrder(lReserveCode);
          if (lReserveOrder.reset(lConnection) && (lReserveOrder.getReserveFlg().equals("0") || lReserveOrder.getReserveFlg().equals("1"))) {
            lRec = new SIModifyRec("ReserveOrderTbl");
            lRec.addCondition("ReserveCode", lReserveCode);
            if (SIDBMultiConf.SIDB_CURRENT_INX == SIDBMultiConf.SIDB_POSTGRESQL_INX) {
              lRec.add(lMailItemName, lNow);
            } else {
              lRec.add(lMailItemName, new SIDateTimeType(lNow));
            }
            lRec.execute(lConnection);
            
            lMailList.add(lReserveOrder.getCurrentEmails());// 7.2.0 ST1018 修正
            
            if (!lReserveOrder.getShopName().equals("EYELASHGARAGE")) {
              lSendMail = new SISendMail(lReserveOrder.getCurrentEmails(), lMailTemp);
            } else {
              lSendMail = new SISendMail(lReserveOrder.getCurrentEmails(), lMailTempEG);
            }
            
            lSendMail.appendContent1("\n予約日時:" + lReserveOrder.getReserveDateTime());
            lSendMail.appendContent1("\n予約番号:" + lReserveOrder.getReserveCode());
            lSendMail.appendContent1("\n\nご予約商品");
            lSendMail.appendContent1("\n===============");
            lSendMail.appendContent1("\n商品コード:" + lReserveOrder.getIndividualCode());
            lSendMail.appendContent1("\n商品名:" + lReserveOrder.getCmdtyName());
            lSendMail.appendContent1("\n予約数：" + lReserveOrder.getAmount());
            lSendMail.appendContent1("\n==============\n");
            try {
              lMailResult = lSendMail.execute();
            } catch (SIFatalException e) {
              lMailResult = false;
              e.printStackTrace();
            }
            if (lMailResult) {
              try {
                lConnection.commit();
              } catch (SQLException e2) {}
            } else {
              try {
                lConnection.rollback();
              } catch (SQLException e2) {}
            }
            lResult = lMailResult && lMailResult;
          } else {
            log.warn("not find lReserveCode=" + lReserveCode);
            lResult = false;
          }
        }
      }
    } catch (SIDuplicateKeyException e) {
      e.printStackTrace();
      throw new SIDBAccessException();
    }
    return lResult;
  }
  
  private String getAddOrSub(String lOldValue, String lNewValue) {
    if (Integer.parseInt(lOldValue) > Integer.parseInt(lNewValue)) {
      return "+" + SIUtil.sub(lOldValue, lNewValue);
    } else if (Integer.parseInt(lOldValue) < Integer.parseInt(lNewValue)) {
      return "-" + SIUtil.sub(lNewValue, lOldValue);
    } else return "";
  }
  
  private int reserveCancelAll(HttpServletRequest lRequest, Connection lConnection, UIReserveListCond lListCond) {
    SICustomErrors errors = new SICustomErrors();
    SIReserveOrder reserve = new SIReserveOrder();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    int result = 0;
    
    SILogin manLogin = SIHTMLUtil.getLogin(lRequest);
    
    try {
      String fromMail = SIDBUtil.getFirstData(lConnection, "SELECT email FROM chargeTbl WHERE chargeCode="+SIDBUtil.SQL2Str(manLogin.getUserCode()));
      if (SIUtil.isNull(fromMail)) fromMail = manLogin.getEmail();
      
      StringBuffer lSql = new StringBuffer();
      lSql.append("SELECT aa.reservecode FROM reserveordertbl aa,cmdtyunittbl bb ");
      lSql.append("WHERE aa.individualcode=bb.individualcode AND aa.reserveflg IN ('1','3') ");
      lSql.append("AND shipmaildatetime IS NOT NULL ");
      lSql.append("AND (nshipmaildatetime IS NULL OR shipmaildatetime>nshipmaildatetime) ");
      lSql.append("AND shipmaildatetime::date + '7 days'::interval < current_date ");
      lSql.append(lListCond.getCondtionSQL());
      
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      
      while (lResultSet.next()) {
        reserve = new SIReserveOrder(lResultSet.getString("reservecode"));
        reserve.reset(lConnection);
        if (reserveCancel(lConnection, reserve, fromMail)) result++;
      }
    } catch (SIDBAccessException ex1) {
      errors.addError(new SICustomError("database.execute.error"));
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
      result = -1;
    } catch (SQLException ex2) {
      errors.addError(new SICustomError("database.execute.error"));
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
      result = -1;
    }finally{
      SIDBUtil.close(lStatement, lResultSet);
    }
    return result;
  }
  
  private int reserveAssignAll(HttpServletRequest lRequest, Connection lConnection, UIReserveListCond lListCond) {
    SICustomErrors errors = new SICustomErrors();
    SIReserveOrder reserve = new SIReserveOrder();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    int result = 0;
    
    SILogin manLogin = SIHTMLUtil.getLogin(lRequest);
    
    try {
      String fromMail = SIDBUtil.getFirstData(lConnection, "SELECT email FROM chargeTbl WHERE chargeCode="+SIDBUtil.SQL2Str(manLogin.getUserCode()));
      if (SIUtil.isNull(fromMail)) fromMail = manLogin.getEmail();
      
      StringBuffer lSql = new StringBuffer();
      lSql.append("SELECT aa.reservecode FROM reserveordertbl aa,cmdtyunittbl bb ");
      lSql.append(",(SELECT s.individualcode,MAX(s.amount) AS amount FROM storestocktbl s,cmdtymtbl c ");
      lSql.append(" WHERE s.cmdtycode=c.cmdtycode AND s.amount<>0 ");
      lSql.append(" AND CASE c.deliverytypecode WHEN 1 THEN s.branchcode NOT IN ('1','2','3','4','5','6','7','8','9','10','11','12','16','19') ELSE s.branchcode NOT IN ('16','19') END GROUP BY s.individualcode) cc ");
      lSql.append("WHERE aa.individualcode=bb.individualcode AND aa.reserveflg='0' AND aa.assignamount=0 AND bb.amountflg='1' ");
      lSql.append("AND aa.individualcode=cc.individualcode AND aa.amount<=cc.amount ");
      lSql.append(lListCond.getCondtionSQL());
      lSql.append(" ORDER BY aa.reservecode");
      
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      
      while (lResultSet.next()) {
        reserve = new SIReserveOrder(lResultSet.getString("reservecode"));
        reserve.reset(lConnection);
        if(reserveAssign(lConnection, reserve, fromMail)) result++;
      }
    } catch (SIDBAccessException ex1) {
      errors.addError(new SICustomError("database.execute.error"));
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
      result = -1;
    } catch (SQLException ex2) {
      errors.addError(new SICustomError("database.execute.error"));
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
      result = -1;
    } catch (SIFatalException ex3) {
      errors.addError(new SICustomError("database.execute.error"));
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
      result = -1;
    }finally{
      SIDBUtil.close(lStatement, lResultSet);
    }
    return result;
  }
  
  private int[] sendAssignMailAll(HttpServletRequest lRequest, Connection lConnection, UIReserveListCond lListCond) {
    SICustomErrors errors = new SICustomErrors();
    SIReserveOrder reserve = new SIReserveOrder();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    int[] result = {0,0};
    
    try {
      StringBuffer lSql = new StringBuffer();
      lSql.append("SELECT aa.reservecode FROM reserveordertbl aa,cmdtyunittbl bb ");
      lSql.append("WHERE aa.individualcode=bb.individualcode AND aa.reserveflg IN ('1','3') ");
      lSql.append("AND (shipmaildatetime IS NULL OR shipmaildatetime<nshipmaildatetime) ");
      lSql.append(lListCond.getCondtionSQL());
      
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(lSql.toString());
      
      while (lResultSet.next()) {
        reserve = new SIReserveOrder(lResultSet.getString("reservecode"));
        reserve.reset(lConnection);
        result[0]++;
        if (sendAssignMail(lConnection, reserve)) result[1]++;
      }
    } catch (SIDBAccessException ex1) {
      errors.addError(new SICustomError("database.execute.error"));
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
      result[0] = -1;
      result[1] = -1;
    } catch (SQLException ex2) {
      errors.addError(new SICustomError("database.execute.error"));
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
      result[0] = -1;
      result[1] = -1;
    }finally{
      SIDBUtil.close(lStatement, lResultSet);
    }
    return result;
  }
  
  private boolean clearReserveStatus(HttpServletRequest lRequest, Connection lConnection, UIReserveListCond lListCond) {
    SICustomErrors errors = new SICustomErrors();
    SIReserveOrder reserve = new SIReserveOrder();
    SIModifyRec lRec = new SIModifyRec();
    try {
      Collection reserveList = lListCond.getCollection(lConnection);
      Iterator reserveIte = reserveList.iterator();
      
      if (!reserveIte.hasNext()) {
        errors.addError(new SICustomError("input.data.check.listData"));
        lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
        return false;
      }
      
      while (reserveIte.hasNext()) {
        reserve = (SIReserveOrder) reserveIte.next();
        lRec = new SIModifyRec("ReserveorderTbl");
        lRec.addCondition("CmdtyCode", reserve.getCmdtyCode());
        lRec.addCondition("IndividualCode", reserve.getIndividualCode());
        lRec.addCondition("ReserveFlg", "3");
        lRec.add("ReserveFlg", "1");
        lRec.execute(lConnection);
      }
      return true;
    } catch (SIDBAccessException ex1) {
      errors.addError(new SICustomError("database.execute.error"));
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
      return false;
    } catch (SIDuplicateKeyException ex2) {
      errors.addError(new SICustomError("database.execute.error"));
      lRequest.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
      return false;
    }
  }
}