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

import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;

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

import java.text.*;
import jp.co.sint.basic.SIMailTemp;
import jp.co.sint.basic.SIMallShop;
import jp.co.sint.basic.SIUserInfo;
import jp.co.sint.beans.front.UIMain;
import jp.co.sint.beans.front.UISearch;
import jp.co.sint.beans.mallmgr.UIRegSearch;
import jp.co.sint.config.SIConfig;
import jp.co.sint.database.SIDBAccessException;
import jp.co.sint.database.SIDBUtil;
import jp.co.sint.database.SIDatabaseConnection;
import jp.co.sint.database.SIDuplicateKeyException;
import jp.co.sint.database.SIInsertRec;
import jp.co.sint.database.SIModifyRec;
import jp.co.sint.mail.SISendMail;
import jp.co.sint.servlet.SIServlet;
import jp.co.sint.tools.SIDateTime;
import jp.co.sint.tools.SIFatalException;
import jp.co.sint.tools.SIHTMLUtil;
import jp.co.sint.tools.SIURLMap;
import jp.co.sint.tools.SIUtil;
import jp.co.sint.tools.SIURLParameter;

import org.apache.log4j.Category;

/**
 * @version $Id: SISearchSrv.java,v 1.0 2005/08/26 Exp $
 * @author Fujita Jun <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>
 * Fujita Jun 2005/08/30 18:59:45 Original
 */
public class SISearchSrv 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 {
    HttpSession session = request.getSession(true);// セッションの取得
    SIDatabaseConnection databaseConnection = new SIDatabaseConnection();// DBへのコネクションの作成
    SIURLParameter urlParam = new SIURLParameter(request);
    try {
      Connection lConnection = databaseConnection.getConnection();
      
      String actionName = this.getActionName(urlParam);// 画面からのアクション
      
      if (this.getServletPath(request).equals("/search")) {// 探して欲しい入力シート
        if (SIUtil.isNull(actionName)) {
          UISearch search = new UISearch();
          search.init(request, urlParam);
          session.setAttribute(SIConfig.SISESSION_SEARCH_EDIT_NAME, search);
          forwardKey(request, response, "webshop.jsp.front.search.edit1");
        } else if (SIConfig.SIACTION_RESET.equalsIgnoreCase(actionName)) { // リセット
          session.removeAttribute(SIConfig.SISESSION_SEARCH_EDIT_NAME);
          forwardKey(request, response, "webshop.jsp.front.search.edit1");
        } else if (actionName.equalsIgnoreCase(SIConfig.SIACTION_BACK)) {
          forwardKey(request, response, "webshop.jsp.front.search.edit1");
        }
      } else if (this.getServletPath(request).equals("/search2")) {// 探して欲しい お客様情報入力
        if (actionName.equalsIgnoreCase(SIConfig.SIACTION_NEXT)) {
          forwardKey(request, response, "webshop.jsp.front.search.edit2");
        } else if (actionName.equalsIgnoreCase(SIConfig.SIACTION_RESET)) { // リセット
          session.removeAttribute(SIConfig.SISESSION_SEARCH_ADDRESS_NAME);
          forwardKey(request, response, "webshop.jsp.front.search.edit2");
        } else if (actionName.equalsIgnoreCase(SIConfig.SIACTION_BACK)) {
          forwardKey(request, response, "webshop.jsp.front.search.edit2");
        } else if (actionName.equalsIgnoreCase(SIConfig.SIACTION_ZIPDIC)) {// 郵便番号辞書ソフトから県名などを検索
          UISearch search = new UISearch();
          search.init(request, urlParam);
          session.setAttribute(SIConfig.SISESSION_SEARCH_ADDRESS_NAME, search);
          forwardKey(request, response, "webshop.jsp.front.search.edit2");
        }
      } else if (this.getServletPath(request).equals("/searchcon")) {
        if (actionName.equalsIgnoreCase(SIConfig.SIACTION_REGIST)) {
          SIUserInfo userLogin = SIHTMLUtil.getUserInfo(request);
          if (userLogin.isLogin(request)) {
            UISearch search = new UISearch();
            search.init(request, urlParam);
            session.setAttribute(SIConfig.SISESSION_SEARCH_EDIT_NAME, search);
            
            search.getRegSearch().setCustCode(SIHTMLUtil.getUserInfo(request).getCustCode());
            search.reset(lConnection);
            session.setAttribute(SIConfig.SISESSION_SEARCH_ADDRESS_NAME, search);
            
            if (search.validate1(request, lConnection)) {
              forwardKey(request, response, "webshop.jsp.front.search.con");
            } else {
              forwardKey(request, response, "webshop.jsp.front.search.edit1");
            }
          } else {
            UISearch search = new UISearch();
            search.init(request, urlParam);
            session.setAttribute(SIConfig.SISESSION_SEARCH_EDIT_NAME, search);
            if (search.validate1(request, lConnection)) {
              redirectHttps(request, response, SIURLMap.getUrl("webshop.servlet.front.user.login") + "?nid=" + SIURLMap.getUrl("webshop.servlet.front.search.con"));
            } else {
              forwardKey(request, response, "webshop.jsp.front.search.edit1");
            }
          }
        } else if (actionName.equalsIgnoreCase(SIConfig.SIACTION_CONFIRM)) {
          UISearch search = new UISearch();
          search.init(request, urlParam);
          session.setAttribute(SIConfig.SISESSION_SEARCH_ADDRESS_NAME, search);
          if (search.validate2(request, lConnection)) {
            forwardKey(request, response, "webshop.jsp.front.search.con");
          } else {
            forwardKey(request, response, "webshop.jsp.front.search.edit2");
          }
        } else if (actionName.equalsIgnoreCase(SIConfig.SIACTION_CON)) {
          UISearch search = (UISearch) session.getAttribute(SIConfig.SISESSION_SEARCH_EDIT_NAME);
          UISearch searchAddress = (UISearch) session.getAttribute(SIConfig.SISESSION_SEARCH_ADDRESS_NAME);
          
          UIRegSearch regSearch = search.getRegSearch();
          UIRegSearch regSearchAddress = searchAddress.getRegSearch();
          
          this.sendMail(request, lConnection, regSearch, regSearchAddress);
          this.insertUpdateLookForTableData(lConnection, regSearch, regSearchAddress);
          forwardKey(request, response, "webshop.jsp.front.search.res");
        } else if (SIUtil.isNull(actionName)) {
          UISearch search = new UISearch();
          search.getRegSearch().setCustCode(SIHTMLUtil.getUserInfo(request).getCustCode());
          search.reset(lConnection);
          session.setAttribute(SIConfig.SISESSION_SEARCH_ADDRESS_NAME, search);
          forwardKey(request, response, "webshop.jsp.front.search.con");
        }
      }
    } catch (Exception e) {
      e.printStackTrace();
      throw new ServletException();
    } finally {
      databaseConnection.close();
    }
  }
  
  /**
   * <b>sendMail</b> ショップにメールを送信
   * 
   * @param request リクエスト
   * @param lConnection DBへのコネクション
   * @param regSearch 入力したデータ(商品）
   * @param regSearchAddress 入力したデータ(顧客情報）
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public boolean sendMail(HttpServletRequest request, Connection connection, UIRegSearch regSearch, UIRegSearch regSearchAddress) throws Exception {
    SISendMail lSendMail = new SISendMail();
    StringBuffer lMailBuf = null;
    String lReserveChangeDateTime = null;
    
    // 送信先メールアドレス取得
    SIMallShop mall = new SIMallShop();
    mall.resetMall(connection);
    lSendMail.setToMailAddress(mall.getEmail());
    
    // メールのテンプレートをセットする
    SIMailTemp mailTemp = new SIMailTemp();
    mailTemp.setEncode(SIConfig.SIENCODE_NONE);
    mailTemp.setTitle("探して欲しい希望メールです。");
    mailTemp.setFromMail(regSearchAddress.getEmail());
    lMailBuf = new StringBuffer();
    lMailBuf.append("\n<><><><><><><><><><><><><><><><><><><><><><><><><>");
    lMailBuf.append("\n     BEAUTY GARAGE ： 探して欲しい希望");
    lMailBuf.append("\n<><><><><><><><><><><><><><><><><><><><><><><><><>");
    lMailBuf.append("\n").append(regSearchAddress.getCustName()).append("様より");
    lReserveChangeDateTime = (new SIDateTime()).getFullDateTime();
    lMailBuf.append("\n" + lReserveChangeDateTime);
    lMailBuf.append("\n下記の内容でお問い合わせをお受けいたしました。");
    lMailBuf.append("\n");
    lMailBuf.append("\n--------------------------------------------------");
    lMailBuf.append("\n■探して欲しい希望商品");
    if (SIUtil.isNotNull(regSearch.getHopeCmdtyName1())) {
      lMailBuf.append("\n[1]");
      lMailBuf.append("\n 商品カテゴリ　：").append(UISearch.getCtgryName(connection, regSearch.getHopeProduct1()));
      lMailBuf.append("\n 商品名　　　　：").append(regSearch.getHopeCmdtyName1());
      lMailBuf.append("\n 数量　　　　　：").append(regSearch.getHopeAmount1());
      lMailBuf.append("\n 希望納品日　　：").append(regSearch.getHopeDeliveryDate1());
      lMailBuf.append("\n 詳細コメント　：");
      lMailBuf.append("\n").append(regSearch.getClientComment1());
      lMailBuf.append("\n");
    }
    if (SIUtil.isNotNull(regSearch.getHopeCmdtyName2())) {
      lMailBuf.append("\n[2]");
      lMailBuf.append("\n 商品カテゴリ　：").append(UISearch.getCtgryName(connection, regSearch.getHopeProduct2()));
      lMailBuf.append("\n 商品名　　　　：").append(regSearch.getHopeCmdtyName2());
      lMailBuf.append("\n 数量　　　　　：").append(regSearch.getHopeAmount2());
      lMailBuf.append("\n 希望納品日　　：").append(regSearch.getHopeDeliveryDate2());
      lMailBuf.append("\n 詳細コメント　：");
      lMailBuf.append("\n").append(regSearch.getClientComment2());
      lMailBuf.append("\n");
    }
    if (SIUtil.isNotNull(regSearch.getHopeCmdtyName3())) {
      lMailBuf.append("\n[3]");
      lMailBuf.append("\n 商品カテゴリ　：").append(UISearch.getCtgryName(connection, regSearch.getHopeProduct3()));
      lMailBuf.append("\n 商品名　　　　：").append(regSearch.getHopeCmdtyName3());
      lMailBuf.append("\n 数量　　　　　：").append(regSearch.getHopeAmount3());
      lMailBuf.append("\n 希望納品日　　：").append(regSearch.getHopeDeliveryDate3());
      lMailBuf.append("\n 詳細コメント　：");
      lMailBuf.append("\n").append(regSearch.getClientComment3());
      lMailBuf.append("\n");
    }
    if (SIUtil.isNotNull(regSearch.getHopeCmdtyName4())) {
      lMailBuf.append("\n[4]");
      lMailBuf.append("\n 商品カテゴリ　：").append(UISearch.getCtgryName(connection, regSearch.getHopeProduct4()));
      lMailBuf.append("\n 商品名　　　　：").append(regSearch.getHopeCmdtyName4());
      lMailBuf.append("\n 数量　　　　　：").append(regSearch.getHopeAmount4());
      lMailBuf.append("\n 希望納品日　　：").append(regSearch.getHopeDeliveryDate4());
      lMailBuf.append("\n 詳細コメント　：");
      lMailBuf.append("\n").append(regSearch.getClientComment4());
      lMailBuf.append("\n");
    }
    if (SIUtil.isNotNull(regSearch.getHopeCmdtyName5())) {
      lMailBuf.append("\n[5]");
      lMailBuf.append("\n 商品カテゴリ　：").append(UISearch.getCtgryName(connection, regSearch.getHopeProduct5()));
      lMailBuf.append("\n 商品名　　　　：").append(regSearch.getHopeCmdtyName5());
      lMailBuf.append("\n 数量　　　　　：").append(regSearch.getHopeAmount5());
      lMailBuf.append("\n 希望納品日　　：").append(regSearch.getHopeDeliveryDate5());
      lMailBuf.append("\n 詳細コメント　：");
      lMailBuf.append("\n").append(regSearch.getClientComment5());
      lMailBuf.append("\n");
    }
    
    lMailBuf.append("\n");
    lMailBuf.append("\n■お名前(漢字)　　　：").append(regSearchAddress.getCustName());
    lMailBuf.append("\n■お名前(フリガナ)　：").append(regSearchAddress.getCustPronName());
    lMailBuf.append("\n■郵便番号　　　　　：").append(regSearchAddress.getPostCode1()).append(" - ").append(regSearchAddress.getPostCode2());
    lMailBuf.append("\n■都道府県　　　　　：").append(regSearchAddress.getAddress1());
    lMailBuf.append("\n■ご住所　　　　　　：").append(regSearchAddress.getAddress2()).append("　").append(regSearchAddress.getAddress3());
    lMailBuf.append("\n■連絡先電話番号１　：").append(regSearchAddress.getStoreTel());
    lMailBuf.append("\n■連絡先電話番号２　：").append(regSearchAddress.getTel());
    lMailBuf.append("\n■連絡先FAX番号　　 ：").append(regSearchAddress.getFax());
    lMailBuf.append("\n■E-mail　　　　　　：").append(regSearchAddress.getEmail());
    lMailBuf.append("\n");
    
    lSendMail.appendContent1(lMailBuf.toString());
    lSendMail.setMailTemp(mailTemp);
    
    // メール送信
    try {
      return lSendMail.execute();
    } catch (SIFatalException e) {
      return false;
    }
  }
  
  /**
   * <b>insertUpdateLookForTableData</b> 探して欲しいテーブルにデータ登録
   * 
   * @param lConnection DBへのコネクション
   * @param regSearch 入力したデータ(商品）
   * @param regSearchAddress 入力したデータ(顧客情報）
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public void insertUpdateLookForTableData(Connection lConnection, UIRegSearch regSearch, UIRegSearch regSearchAddress) throws SIDBAccessException, SIDuplicateKeyException {
    SIDateTime lDateTime = new SIDateTime();
    int lNowYear = lDateTime.getYear();
    String year = lDateTime.getYearStr();
    String lNow = lDateTime.getFullDate();
    
    // 採番テーブル修正・追加処理
    StringBuffer lSqlBuf = new StringBuffer("SELECT Count(*) AS Cnt FROM NumberingTbl ");
    lSqlBuf.append("WHERE Functional_Type='0' AND Year=" + lNowYear + "");
    if (!SIDBUtil.hasData(lConnection, lSqlBuf.toString())) {
      // 新規レコードの作成
      SIInsertRec lRec = new SIInsertRec("NumberingTbl");
      lRec.add("CuFunctional_TypestCode", "0");// 機能区分
      lRec.add("Year", lNowYear);// 年
      lRec.add("Management_SbNo", 0);// 採番
      lRec.add("InitDateTime", lNow);// 登録日
      
      lRec.execute(lConnection);
    }
    
    // 探して欲しいテーブル追加処理
    if (SIUtil.isNotNull(regSearch.getHopeCmdtyName1())) {
      // 現管理番号取得
      StringBuffer lSqlBuf2 = new StringBuffer("SELECT Management_SbNo FROM NumberingTbl ");
      lSqlBuf2.append("WHERE Functional_Type='0' AND Year=" + lNowYear + "");
      
      String managementSbNo = SIDBUtil.getFirstData(lConnection, lSqlBuf2.toString());
      DecimalFormat exFormat1 = new DecimalFormat("000000");
      DecimalFormat exFormat2 = new DecimalFormat("00");
      String managementNo = exFormat2.format(Integer.parseInt(year.substring(2, 4))) + "_" + exFormat1.format(Integer.parseInt(managementSbNo));
      
      // 日付設定
      String deliveryDate1 = regSearch.getHopeDeliveryDate1();
      String day1 = "";
      if (deliveryDate1.indexOf("上旬") != -1) {
        day1 = "01";
      } else if (deliveryDate1.indexOf("中旬") != -1) {
        day1 = "10";
      } else {
        day1 = "20";
      }
      deliveryDate1 = regSearch.getHopeDeliveryDate1().substring(0, 4) + "-" + regSearch.getHopeDeliveryDate1().substring(5, 7) + "-" + day1;
      
      SIInsertRec lRec = new SIInsertRec("LookForTbl");
      lRec.add("MANAGEMENTNUMBER", managementNo);
      lRec.add("HOPEPRODUCT", regSearch.getHopeProduct1());
      lRec.add("HOPECOMMODITYNAME", regSearch.getHopeCmdtyName1());
      lRec.add("HOPEAMOUNT", regSearch.getHopeAmount1());
      lRec.add("HOPEDELIVERYDATE", deliveryDate1);
      lRec.add("CLIENTCOMMENT", regSearch.getClientComment1());
      lRec.add("METHODFORHOPEFLG", 0);
      lRec.add("CUSTNAME", regSearchAddress.getCustName());
      lRec.add("CUSTPRONNAME", regSearchAddress.getCustPronName());
      lRec.add("EMAIL", regSearchAddress.getEmail());
      lRec.add("POSTCODE1", regSearchAddress.getPostCode1());
      lRec.add("POSTCODE2", regSearchAddress.getPostCode2());
      lRec.add("ADDRESS1", regSearchAddress.getAddress1());
      lRec.add("ADDRESS2", regSearchAddress.getAddress2());
      lRec.add("ADDRESS3", regSearchAddress.getAddress3());
      lRec.add("STORETEL", regSearchAddress.getStoreTel());
      lRec.add("TEL", regSearchAddress.getTel());
      lRec.add("FAX", regSearchAddress.getFax());
      lRec.add("SALONNAME", regSearchAddress.getSalonName());
      lRec.add("InitDateTime", lNow);
      lRec.add("CUSTCODE", regSearchAddress.getCustCode());
      
      lRec.add("INQUIRYFORMFLG", 0);
      lRec.add("CORRESPONDINGFLG", 0);
      lRec.add("PRESENTATIONCOMMODITYFLG", 0);
      
      lRec.execute(lConnection);
      
      int nextManagementSbNo = Integer.parseInt(managementSbNo) + 1;
      
      SIModifyRec lRec2 = new SIModifyRec("NumberingTbl");
      lRec2.addCondition("Functional_Type", "0");// 機能区分
      lRec2.addCondition("Year", lNowYear);// 年
      lRec2.add("Management_SbNo", nextManagementSbNo);// 採番
      lRec2.add("InitDateTime", lNow);// 登録日
      lRec2.execute(lConnection);
      
    }
    
    if (SIUtil.isNotNull(regSearch.getHopeCmdtyName2())) {
      // 現管理番号取得
      StringBuffer lSqlBuf2 = new StringBuffer("SELECT Management_SbNo FROM NumberingTbl ");
      lSqlBuf2.append("WHERE Functional_Type='0' AND Year=" + lNowYear + "");
      
      String managementSbNo = SIDBUtil.getFirstData(lConnection, lSqlBuf2.toString());
      DecimalFormat exFormat1 = new DecimalFormat("000000");
      DecimalFormat exFormat2 = new DecimalFormat("00");
      String managementNo = exFormat2.format(Integer.parseInt(year.substring(2, 4))) + "_" + exFormat1.format(Integer.parseInt(managementSbNo));
      
      // 日付設定
      String deliveryDate2 = regSearch.getHopeDeliveryDate2();
      String day2 = "";
      if (deliveryDate2.indexOf("上旬") != -1) {
        day2 = "01";
      } else if (deliveryDate2.indexOf("中旬") != -1) {
        day2 = "10";
      } else {
        day2 = "20";
      }
      deliveryDate2 = regSearch.getHopeDeliveryDate2().substring(0, 4) + "-" + regSearch.getHopeDeliveryDate2().substring(5, 7) + "-" + day2;
      
      SIInsertRec lRec = new SIInsertRec("LookForTbl");
      lRec.add("MANAGEMENTNUMBER", managementNo);
      lRec.add("HOPEPRODUCT", regSearch.getHopeProduct2());
      lRec.add("HOPECOMMODITYNAME", regSearch.getHopeCmdtyName2());
      lRec.add("HOPEAMOUNT", regSearch.getHopeAmount2());
      lRec.add("HOPEDELIVERYDATE", deliveryDate2);
      lRec.add("CLIENTCOMMENT", regSearch.getClientComment2());
      lRec.add("METHODFORHOPEFLG", 0);
      lRec.add("CUSTNAME", regSearchAddress.getCustName());
      lRec.add("CUSTPRONNAME", regSearchAddress.getCustPronName());
      lRec.add("EMAIL", regSearchAddress.getEmail());
      lRec.add("POSTCODE1", regSearchAddress.getPostCode1());
      lRec.add("POSTCODE2", regSearchAddress.getPostCode2());
      lRec.add("ADDRESS1", regSearchAddress.getAddress1());
      lRec.add("ADDRESS2", regSearchAddress.getAddress2());
      lRec.add("ADDRESS3", regSearchAddress.getAddress3());
      lRec.add("STORETEL", regSearchAddress.getStoreTel());
      lRec.add("TEL", regSearchAddress.getTel());
      lRec.add("FAX", regSearchAddress.getFax());
      lRec.add("SALONNAME", regSearchAddress.getSalonName());
      lRec.add("InitDateTime", lNow);
      lRec.add("CUSTCODE", regSearchAddress.getCustCode());
      
      lRec.add("INQUIRYFORMFLG", 0);
      lRec.add("CORRESPONDINGFLG", 0);
      lRec.add("PRESENTATIONCOMMODITYFLG", 0);
      
      lRec.execute(lConnection);
      
      int nextManagementSbNo = Integer.parseInt(managementSbNo) + 1;
      
      SIModifyRec lRec2 = new SIModifyRec("NumberingTbl");
      lRec2.addCondition("Functional_Type", "0");// 機能区分
      lRec2.addCondition("Year", lNowYear);// 年
      lRec2.add("Management_SbNo", nextManagementSbNo);// 採番
      lRec2.add("InitDateTime", lNow);// 登録日
      lRec2.execute(lConnection);
      
    }
    
    if (SIUtil.isNotNull(regSearch.getHopeCmdtyName3())) {
      // 現管理番号取得
      StringBuffer lSqlBuf2 = new StringBuffer("SELECT Management_SbNo FROM NumberingTbl ");
      lSqlBuf2.append("WHERE Functional_Type='0' AND Year=" + lNowYear + "");
      
      String managementSbNo = SIDBUtil.getFirstData(lConnection, lSqlBuf2.toString());
      DecimalFormat exFormat1 = new DecimalFormat("000000");
      DecimalFormat exFormat2 = new DecimalFormat("00");
      String managementNo = exFormat2.format(Integer.parseInt(year.substring(2, 4))) + "_" + exFormat1.format(Integer.parseInt(managementSbNo));
      
      // 日付設定
      String deliveryDate3 = regSearch.getHopeDeliveryDate3();
      String day3 = "";
      if (deliveryDate3.indexOf("上旬") != -1) {
        day3 = "01";
      } else if (deliveryDate3.indexOf("中旬") != -1) {
        day3 = "10";
      } else {
        day3 = "20";
      }
      deliveryDate3 = regSearch.getHopeDeliveryDate3().substring(0, 4) + "-" + regSearch.getHopeDeliveryDate3().substring(5, 7) + "-" + day3;
      
      SIInsertRec lRec = new SIInsertRec("LookForTbl");
      lRec.add("MANAGEMENTNUMBER", managementNo);
      lRec.add("HOPEPRODUCT", regSearch.getHopeProduct3());
      lRec.add("HOPECOMMODITYNAME", regSearch.getHopeCmdtyName3());
      lRec.add("HOPEAMOUNT", regSearch.getHopeAmount3());
      lRec.add("HOPEDELIVERYDATE", deliveryDate3);
      lRec.add("CLIENTCOMMENT", regSearch.getClientComment3());
      lRec.add("METHODFORHOPEFLG", 0);
      lRec.add("CUSTNAME", regSearchAddress.getCustName());
      lRec.add("CUSTPRONNAME", regSearchAddress.getCustPronName());
      lRec.add("EMAIL", regSearchAddress.getEmail());
      lRec.add("POSTCODE1", regSearchAddress.getPostCode1());
      lRec.add("POSTCODE2", regSearchAddress.getPostCode2());
      lRec.add("ADDRESS1", regSearchAddress.getAddress1());
      lRec.add("ADDRESS2", regSearchAddress.getAddress2());
      lRec.add("ADDRESS3", regSearchAddress.getAddress3());
      lRec.add("STORETEL", regSearchAddress.getStoreTel());
      lRec.add("TEL", regSearchAddress.getTel());
      lRec.add("FAX", regSearchAddress.getFax());
      lRec.add("SALONNAME", regSearchAddress.getSalonName());
      lRec.add("InitDateTime", lNow);
      lRec.add("CUSTCODE", regSearchAddress.getCustCode());
      
      lRec.add("INQUIRYFORMFLG", 0);
      lRec.add("CORRESPONDINGFLG", 0);
      lRec.add("PRESENTATIONCOMMODITYFLG", 0);
      
      lRec.execute(lConnection);
      
      int nextManagementSbNo = Integer.parseInt(managementSbNo) + 1;
      
      SIModifyRec lRec2 = new SIModifyRec("NumberingTbl");
      lRec2.addCondition("Functional_Type", "0");// 機能区分
      lRec2.addCondition("Year", lNowYear);// 年
      lRec2.add("Management_SbNo", nextManagementSbNo);// 採番
      lRec2.add("InitDateTime", lNow);// 登録日
      lRec2.execute(lConnection);
      
    }
    
    if (SIUtil.isNotNull(regSearch.getHopeCmdtyName4())) {
      // 現管理番号取得
      StringBuffer lSqlBuf2 = new StringBuffer("SELECT Management_SbNo FROM NumberingTbl ");
      lSqlBuf2.append("WHERE Functional_Type='0' AND Year=" + lNowYear + "");
      
      String managementSbNo = SIDBUtil.getFirstData(lConnection, lSqlBuf2.toString());
      DecimalFormat exFormat1 = new DecimalFormat("000000");
      DecimalFormat exFormat2 = new DecimalFormat("00");
      String managementNo = exFormat2.format(Integer.parseInt(year.substring(2, 4))) + "_" + exFormat1.format(Integer.parseInt(managementSbNo));
      
      // 日付設定
      String deliveryDate4 = regSearch.getHopeDeliveryDate4();
      String day4 = "";
      if (deliveryDate4.indexOf("上旬") != -1) {
        day4 = "01";
      } else if (deliveryDate4.indexOf("中旬") != -1) {
        day4 = "10";
      } else {
        day4 = "20";
      }
      deliveryDate4 = regSearch.getHopeDeliveryDate4().substring(0, 4) + "-" + regSearch.getHopeDeliveryDate4().substring(5, 7) + "-" + day4;
      
      SIInsertRec lRec = new SIInsertRec("LookForTbl");
      lRec.add("MANAGEMENTNUMBER", managementNo);
      lRec.add("HOPEPRODUCT", regSearch.getHopeProduct4());
      lRec.add("HOPECOMMODITYNAME", regSearch.getHopeCmdtyName4());
      lRec.add("HOPEAMOUNT", regSearch.getHopeAmount4());
      lRec.add("HOPEDELIVERYDATE", deliveryDate4);
      lRec.add("CLIENTCOMMENT", regSearch.getClientComment4());
      lRec.add("METHODFORHOPEFLG", 0);
      lRec.add("CUSTNAME", regSearchAddress.getCustName());
      lRec.add("CUSTPRONNAME", regSearchAddress.getCustPronName());
      lRec.add("EMAIL", regSearchAddress.getEmail());
      lRec.add("POSTCODE1", regSearchAddress.getPostCode1());
      lRec.add("POSTCODE2", regSearchAddress.getPostCode2());
      lRec.add("ADDRESS1", regSearchAddress.getAddress1());
      lRec.add("ADDRESS2", regSearchAddress.getAddress2());
      lRec.add("ADDRESS3", regSearchAddress.getAddress3());
      lRec.add("STORETEL", regSearchAddress.getStoreTel());
      lRec.add("TEL", regSearchAddress.getTel());
      lRec.add("FAX", regSearchAddress.getFax());
      lRec.add("SALONNAME", regSearchAddress.getSalonName());
      lRec.add("InitDateTime", lNow);
      lRec.add("CUSTCODE", regSearchAddress.getCustCode());
      
      lRec.add("INQUIRYFORMFLG", 0);
      lRec.add("CORRESPONDINGFLG", 0);
      lRec.add("PRESENTATIONCOMMODITYFLG", 0);
      
      lRec.execute(lConnection);
      
      int nextManagementSbNo = Integer.parseInt(managementSbNo) + 1;
      
      SIModifyRec lRec2 = new SIModifyRec("NumberingTbl");
      lRec2.addCondition("Functional_Type", "0");// 機能区分
      lRec2.addCondition("Year", lNowYear);// 年
      lRec2.add("Management_SbNo", nextManagementSbNo);// 採番
      lRec2.add("InitDateTime", lNow);// 登録日
      lRec2.execute(lConnection);
      
    }
    
    if (SIUtil.isNotNull(regSearch.getHopeCmdtyName5())) {
      // 現管理番号取得
      StringBuffer lSqlBuf2 = new StringBuffer("SELECT Management_SbNo FROM NumberingTbl ");
      lSqlBuf2.append("WHERE Functional_Type='0' AND Year=" + lNowYear + "");
      
      String managementSbNo = SIDBUtil.getFirstData(lConnection, lSqlBuf2.toString());
      DecimalFormat exFormat1 = new DecimalFormat("000000");
      DecimalFormat exFormat2 = new DecimalFormat("00");
      String managementNo = exFormat2.format(Integer.parseInt(year.substring(2, 4))) + "_" + exFormat1.format(Integer.parseInt(managementSbNo));
      
      // 日付設定
      String deliveryDate5 = regSearch.getHopeDeliveryDate5();
      String day5 = "";
      if (deliveryDate5.indexOf("上旬") != -1) {
        day5 = "01";
      } else if (deliveryDate5.indexOf("中旬") != -1) {
        day5 = "10";
      } else {
        day5 = "20";
      }
      deliveryDate5 = regSearch.getHopeDeliveryDate5().substring(0, 4) + "-" + regSearch.getHopeDeliveryDate5().substring(5, 7) + "-" + day5;
      
      SIInsertRec lRec = new SIInsertRec("LookForTbl");
      lRec.add("MANAGEMENTNUMBER", managementNo);
      lRec.add("HOPEPRODUCT", regSearch.getHopeProduct5());
      lRec.add("HOPECOMMODITYNAME", regSearch.getHopeCmdtyName5());
      lRec.add("HOPEAMOUNT", regSearch.getHopeAmount5());
      lRec.add("HOPEDELIVERYDATE", deliveryDate5);
      lRec.add("CLIENTCOMMENT", regSearch.getClientComment5());
      lRec.add("METHODFORHOPEFLG", 0);
      lRec.add("CUSTNAME", regSearchAddress.getCustName());
      lRec.add("CUSTPRONNAME", regSearchAddress.getCustPronName());
      lRec.add("EMAIL", regSearchAddress.getEmail());
      lRec.add("POSTCODE1", regSearchAddress.getPostCode1());
      lRec.add("POSTCODE2", regSearchAddress.getPostCode2());
      lRec.add("ADDRESS1", regSearchAddress.getAddress1());
      lRec.add("ADDRESS2", regSearchAddress.getAddress2());
      lRec.add("ADDRESS3", regSearchAddress.getAddress3());
      lRec.add("STORETEL", regSearchAddress.getStoreTel());
      lRec.add("TEL", regSearchAddress.getTel());
      lRec.add("FAX", regSearchAddress.getFax());
      lRec.add("SALONNAME", regSearchAddress.getSalonName());
      lRec.add("InitDateTime", lNow);
      lRec.add("CUSTCODE", regSearchAddress.getCustCode());
      
      lRec.add("INQUIRYFORMFLG", 0);
      lRec.add("CORRESPONDINGFLG", 0);
      lRec.add("PRESENTATIONCOMMODITYFLG", 0);
      
      lRec.execute(lConnection);
      
      int nextManagementSbNo = Integer.parseInt(managementSbNo) + 1;
      
      SIModifyRec lRec2 = new SIModifyRec("NumberingTbl");
      lRec2.addCondition("Functional_Type", "0");// 機能区分
      lRec2.addCondition("Year", lNowYear);// 年
      lRec2.add("Management_SbNo", nextManagementSbNo);// 採番
      lRec2.add("InitDateTime", lNow);// 登録日
      lRec2.execute(lConnection);
      
    }
    
    try {
      lConnection.commit();
    } catch (SQLException sqle) {
      throw new SIDBAccessException("delete data error.");
    }
    
  }
}