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

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

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.SICmdty;
import jp.co.sint.basic.SICmdtyCompositionAppended;
import jp.co.sint.basic.SICmdtyCompositionGroupData;
import jp.co.sint.basic.SICmdtyCompositionGroupDetail;
import jp.co.sint.basic.SICmdtyCompositionOption;
import jp.co.sint.basic.SILogin;
import jp.co.sint.basic.SINameValue;
import jp.co.sint.beans.front.UICmdtyInfo;
import jp.co.sint.beans.mallmgr.UICmdtyListCond;
import jp.co.sint.beans.mallmgr.UICmdtyParent;
import jp.co.sint.beans.mallmgr.UIIndividual;
import jp.co.sint.beans.mallmgr.UIRegCmdty;
import jp.co.sint.beans.mallmgr.UIRegCmdtyComposition;
import jp.co.sint.beans.mallmgr.UIRegCmdtyPreview;
import jp.co.sint.beans.mallmgr.UIRegPointIndividual;
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.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.SITableCondition;
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.SIHTMLUtil;
import jp.co.sint.tools.SIURLParameter;
import jp.co.sint.tools.SIUtil;

import org.apache.log4j.Category;

/**
 * @version $Id: SIRegCmdtyCompositionSrv.java,v 1.0 2012/11/21 Exp $
 * @author SI matsushima <br>
 *         Description: 商品構成を登録．修正することなどを行うServlet
 *         <p>
 *         History
 *         </p>
 *         <p>
 *         Author&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Reason
 *         </p>
 *         ============&nbsp;&nbsp;&nbsp;==========&nbsp;&nbsp;=========================== <br>
 *         SI matsushima 2012/11/22 Original
 */
public class SIRegCmdtyCompositionSrv 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();// DBへのコネクションの作成
    SIURLParameter urlParam = new SIURLParameter(request);
    
    try {
      String actionName = this.getActionName(urlParam);// 画面からのアクション
      String editMode = this.getEditMode(urlParam);// DBへの編集モード
      
      UICmdtyListCond cmdtyList = new UICmdtyListCond();
      UIRegCmdty regCmdty = new UIRegCmdty();
      UIRegCmdtyComposition regCmdtyComposition = new UIRegCmdtyComposition();
      
      if (SIConfig.SIACTION_SEARCH.equalsIgnoreCase(actionName)) {  // セットコード検索
        cmdtyList = new UICmdtyListCond();
        cmdtyList.initSearch(request, urlParam);
        cmdtyList.setPageSizeSel(cmdtyList.getPrevSize());
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_LIST_NAME, cmdtyList);
        
        regCmdty = new UIRegCmdty();
        regCmdtyComposition = new UIRegCmdtyComposition();
        regCmdty.init(request, urlParam);
        regCmdtyComposition.init(request, urlParam, databaseConnection.getConnection());
        regCmdtyComposition.setFirstDispFlg("1");
        
        UIIndividual regIndividual = new UIIndividual();
        regIndividual.setIndividual1(regCmdtyComposition.getIndividual1());
        regIndividual.setIndividual2(regCmdtyComposition.getIndividual2());
        regIndividual.setIndividual3(regCmdtyComposition.getIndividual3());
        
        if (regIndividual.validateSearch(request, databaseConnection.getConnection())) {
          try {
            regCmdtyComposition.setIndividual2(SIRegIndividualSrv.getIndividualNext(databaseConnection.getConnection(), regCmdtyComposition.getIndividual1()));
            regCmdtyComposition.setCtgryName(SIDBUtil.getFirstData(databaseConnection.getConnection(), "SELECT ctgryname FROM ctgrymtbl WHERE ctgrycode="+SIDBUtil.SQL2Str(regIndividual.getIndividual1())));
            StringBuffer str = new StringBuffer();
            str.append("SELECT ctgrycode FROM ctgryvw WHERE ctgrycode = ").append(SIDBUtil.SQL2Str(regCmdtyComposition.getIndividual1()));
            str.append(" AND (");
            for (int i=0;i<SIConfig.LOGINCTGRY.length;i++){
              if (i>0) str.append(" OR ");
              str.append(" (branch LIKE ").append(SIDBUtil.SQL2Str("%~" + SIDBUtil.SQL2Like(SIConfig.LOGINCTGRY[i])+"~%"));
              str.append(" OR branch LIKE ").append(SIDBUtil.SQL2Str("%~" + SIDBUtil.SQL2Like(SIConfig.LOGINCTGRY[i]), ")"));
            }
            str.append(") ");
            regCmdty.setLoginFlg(SIDBUtil.hasData(databaseConnection.getConnection(), str.toString()));
          } catch (SIDBAccessException e) {
            e.printStackTrace();
          }
          regCmdtyComposition.setEditModeTxt(regCmdty.getEditModeTxt());
          session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty);
          session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION, regCmdtyComposition);
          forwardKey(request, response, "webshop.jsp.manager.cmdty.set.edit");
        } else {
          regCmdtyComposition.setEditModeTxt(regCmdty.getEditModeTxt());
          session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty);
          session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION, regCmdtyComposition);
          forwardKey(request, response, "webshop.jsp.manager.cmdty.set.edit");
        }
      } else if (SIConfig.SIACTION_COLOR_SELECT.equalsIgnoreCase(actionName)) {  // カラー選択時
        cmdtyList = new UICmdtyListCond();
        cmdtyList.initSearch(request, urlParam);
        cmdtyList.setPageSizeSel(cmdtyList.getPrevSize());
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_LIST_NAME, cmdtyList);
        
        regCmdty = new UIRegCmdty();
        regCmdtyComposition = new UIRegCmdtyComposition();
        regCmdty.init(request, urlParam);
        regCmdtyComposition.init(request, urlParam, databaseConnection.getConnection());
        regCmdtyComposition.setFirstDispFlg("1");
        
        // 構成グループ明細の制御を行う。
        try {
          regCmdtyComposition.getCmdtyCompositionGroupDetail(null, databaseConnection.getConnection());
        } catch (SIDBAccessException sqle) {
          sqle.printStackTrace();
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("database.execute.error"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          throw new ServletException();
        }
        
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty);
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION, regCmdtyComposition);
        forwardKey(request, response, "webshop.jsp.manager.cmdty.set.edit");
      } else if (SIConfig.SIACTION_SET_GROUP_DELETE.equalsIgnoreCase(actionName)) {  // 構成グループ削除
        cmdtyList = new UICmdtyListCond();
        cmdtyList.initSearch(request, urlParam);
        cmdtyList.setPageSizeSel(cmdtyList.getPrevSize());
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_LIST_NAME, cmdtyList);
        
        regCmdty = new UIRegCmdty();
        regCmdtyComposition = new UIRegCmdtyComposition();
        regCmdty.init(request, urlParam);
        regCmdtyComposition.init(request, urlParam, databaseConnection.getConnection());
        regCmdtyComposition.setFirstDispFlg("1");
        
        // 対象のグループを削除する。
        if (SIUtil.isNotNull(regCmdtyComposition.getActionGroup())){
          regCmdtyComposition.cmdtyGroupDel(regCmdtyComposition.getActionGroup());
        }
        
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty);
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION, regCmdtyComposition);
        forwardKey(request, response, "webshop.jsp.manager.cmdty.set.edit");
      } else if (SIConfig.SIACTION_SET_GROUP_ADD.equalsIgnoreCase(actionName)) {  // 構成グループ追加
        cmdtyList = new UICmdtyListCond();
        cmdtyList.initSearch(request, urlParam);
        cmdtyList.setPageSizeSel(cmdtyList.getPrevSize());
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_LIST_NAME, cmdtyList);
        
        regCmdty = new UIRegCmdty();
        regCmdtyComposition = new UIRegCmdtyComposition();
        regCmdty.init(request, urlParam);
        regCmdtyComposition.init(request, urlParam, databaseConnection.getConnection());
        regCmdtyComposition.setFirstDispFlg("1");
        regCmdtyComposition.cmdtyGroupAdd();
        
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty);
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION, regCmdtyComposition);
        forwardKey(request, response, "webshop.jsp.manager.cmdty.set.edit");
      } else if (SIConfig.SIACTION_SET_GROUP_DETAIL_DELETE.equalsIgnoreCase(actionName)) {  // 構成グループ明細削除
        cmdtyList = new UICmdtyListCond();
        cmdtyList.initSearch(request, urlParam);
        cmdtyList.setPageSizeSel(cmdtyList.getPrevSize());
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_LIST_NAME, cmdtyList);
        
        regCmdty = new UIRegCmdty();
        regCmdtyComposition = new UIRegCmdtyComposition();
        regCmdty.init(request, urlParam);
        regCmdtyComposition.init(request, urlParam, databaseConnection.getConnection());
        regCmdtyComposition.setFirstDispFlg("1");
        
        if (SIUtil.isNotNull(regCmdtyComposition.getActionGroup()) || SIUtil.isNotNull(regCmdtyComposition.getActionLen())){
          regCmdtyComposition.cmdtyGroupDetailDel(regCmdtyComposition.getActionGroup(), regCmdtyComposition.getActionLen());
        }
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty);
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION, regCmdtyComposition);
        forwardKey(request, response, "webshop.jsp.manager.cmdty.set.edit");
      } else if (SIConfig.SIACTION_SET_GROUP_DETAIL_ADD.equalsIgnoreCase(actionName)) {  // 構成グループ明細追加
        cmdtyList = new UICmdtyListCond();
        cmdtyList.initSearch(request, urlParam);
        cmdtyList.setPageSizeSel(cmdtyList.getPrevSize());
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_LIST_NAME, cmdtyList);
        
        regCmdty = new UIRegCmdty();
        regCmdtyComposition = new UIRegCmdtyComposition();
        regCmdty.init(request, urlParam);
        regCmdtyComposition.init(request, urlParam, databaseConnection.getConnection());
        regCmdtyComposition.setFirstDispFlg("1");
        
        if (SIUtil.isNotNull(regCmdtyComposition.getActionGroup())){
          regCmdtyComposition.cmdtyGroupDetailAdd(regCmdtyComposition.getActionGroup());
        }
        
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty);
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION, regCmdtyComposition);
        forwardKey(request, response, "webshop.jsp.manager.cmdty.set.edit");
      } else if (SIConfig.SIACTION_SET_OPTION_DELETE.equalsIgnoreCase(actionName)) {  // オプション明細削除
        cmdtyList = new UICmdtyListCond();
        cmdtyList.initSearch(request, urlParam);
        cmdtyList.setPageSizeSel(cmdtyList.getPrevSize());
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_LIST_NAME, cmdtyList);
        
        regCmdty = new UIRegCmdty();
        regCmdtyComposition = new UIRegCmdtyComposition();
        regCmdty.init(request, urlParam);
        regCmdtyComposition.init(request, urlParam, databaseConnection.getConnection());
        regCmdtyComposition.setFirstDispFlg("1");
        
        if (SIUtil.isNotNull(regCmdtyComposition.getActionLen())){
          regCmdtyComposition.cmdtyOptionDel(regCmdtyComposition.getActionLen());
        }
        
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty);
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION, regCmdtyComposition);
        forwardKey(request, response, "webshop.jsp.manager.cmdty.set.edit");
      } else if (SIConfig.SIACTION_SET_OPTION_ADD.equalsIgnoreCase(actionName)) {  // オプション明細追加
        cmdtyList = new UICmdtyListCond();
        cmdtyList.initSearch(request, urlParam);
        cmdtyList.setPageSizeSel(cmdtyList.getPrevSize());
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_LIST_NAME, cmdtyList);
        
        regCmdty = new UIRegCmdty();
        regCmdtyComposition = new UIRegCmdtyComposition();
        regCmdty.init(request, urlParam);
        regCmdtyComposition.init(request, urlParam, databaseConnection.getConnection());
        regCmdtyComposition.setFirstDispFlg("1");
        regCmdtyComposition.cmdtyOptionAdd();
        
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty);
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION, regCmdtyComposition);
        forwardKey(request, response, "webshop.jsp.manager.cmdty.set.edit");
      } else if (SIConfig.SIACTION_SET_APPENDED_DELETE.equalsIgnoreCase(actionName)) {  // 添付商品明細削除
        cmdtyList = new UICmdtyListCond();
        cmdtyList.initSearch(request, urlParam);
        cmdtyList.setPageSizeSel(cmdtyList.getPrevSize());
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_LIST_NAME, cmdtyList);
        
        regCmdty = new UIRegCmdty();
        regCmdtyComposition = new UIRegCmdtyComposition();
        regCmdty.init(request, urlParam);
        regCmdtyComposition.init(request, urlParam, databaseConnection.getConnection());
        regCmdtyComposition.setFirstDispFlg("1");
        
        if (SIUtil.isNotNull(regCmdtyComposition.getActionLen())){
          regCmdtyComposition.cmdtyAppendedDel(regCmdtyComposition.getActionLen());
        }
        
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty);
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION, regCmdtyComposition);
        forwardKey(request, response, "webshop.jsp.manager.cmdty.set.edit");
      } else if (SIConfig.SIACTION_SET_APPENDED_ADD.equalsIgnoreCase(actionName)) {  // 添付商品明細追加
        cmdtyList = new UICmdtyListCond();
        cmdtyList.initSearch(request, urlParam);
        cmdtyList.setPageSizeSel(cmdtyList.getPrevSize());
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_LIST_NAME, cmdtyList);
        
        regCmdty = new UIRegCmdty();
        regCmdtyComposition = new UIRegCmdtyComposition();
        regCmdty.init(request, urlParam);
        regCmdtyComposition.init(request, urlParam, databaseConnection.getConnection());
        regCmdtyComposition.setFirstDispFlg("1");
        regCmdtyComposition.cmdtyAppendedAdd();
        
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty);
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION, regCmdtyComposition);
        forwardKey(request, response, "webshop.jsp.manager.cmdty.set.edit");
      } else if (SIConfig.SIACTION_COPY.equalsIgnoreCase(actionName)) {
        regCmdty = new UIRegCmdty();
        regCmdtyComposition = new UIRegCmdtyComposition();
        regCmdty.init(request, urlParam);
        regCmdtyComposition.init(request, urlParam, databaseConnection.getConnection());
        if (regCmdtyComposition.validateIndividualSearch(request, databaseConnection.getConnection())) {
          try {
            regCmdtyComposition.individualCopy(databaseConnection.getConnection());
            regCmdtyComposition.setIndividual1("");
          } catch (Exception e) {
            e.printStackTrace();
          }
          regCmdtyComposition.setEditModeTxt(regCmdty.getEditModeTxt());
          session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty);
          session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION, regCmdtyComposition);
          forwardKey(request, response, "webshop.jsp.manager.cmdty.set.edit");
        } else {
          regCmdtyComposition.setEditModeTxt(regCmdty.getEditModeTxt());
          session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty);
          session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION, regCmdtyComposition);
          forwardKey(request, response, "webshop.jsp.manager.cmdty.set.edit");
        }
      } else if (SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)) {// 検索に戻る
        cmdtyList = new UICmdtyListCond();
        cmdtyList.initSearch(request, urlParam);
        cmdtyList.setPageSizeSel(cmdtyList.getPrevSize());
        cmdtyList.validate(request);
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_LIST_NAME, cmdtyList);
        forwardKey(request, response, "webshop.jsp.manager.cmdty.list");
      } else if (SIConfig.SIACTION_PREVIEW2.equalsIgnoreCase(actionName)) {// 構成商品確定
        cmdtyList = new UICmdtyListCond();
        cmdtyList.initSearch(request, urlParam);
        cmdtyList.setPageSizeSel(cmdtyList.getPrevSize());
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_LIST_NAME, cmdtyList);
        
        regCmdty = new UIRegCmdty();
        regCmdtyComposition = new UIRegCmdtyComposition();
        request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
        regCmdty.init(request, urlParam);
        regCmdtyComposition.init(request, urlParam, databaseConnection.getConnection());
        regCmdtyComposition.setFirstDispFlg("1");
        regCmdtyComposition.setEditModeTxt(regCmdty.getEditModeTxt());
        regCmdty.setShopInfo(databaseConnection.getConnection());
        
        UIRegCmdtyComposition regCmdtyCompositionTmp = regCmdtyComposition;
        
        try {
          // 各明細行を最新にする
          regCmdtyComposition.compositionReflesh(databaseConnection.getConnection());
          
          // 入力チェック
          if (regCmdtyComposition.cmdtyCompositionValidate(request, databaseConnection.getConnection())){
            
            // 再計算する
            regCmdtyComposition.compositionInfo();
            regCmdtyComposition.compositionCalc(databaseConnection.getConnection());
            
            session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION, regCmdtyComposition);
          } else {
            session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION, regCmdtyCompositionTmp);
          }
          session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty);
          forwardKey(request, response, "webshop.jsp.manager.cmdty.set.edit");
        } catch (SIDBAccessException e) {
          regCmdtyComposition.setFirstDispFlg("1");
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("database.execute.error"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty);
          session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION, regCmdtyCompositionTmp);
          forwardKey(request, response, "webshop.jsp.manager.cmdty.set.edit");
        }
      } else if (SIConfig.SIACTION_PREVIEW.equalsIgnoreCase(actionName)) {// プレビュー画面
        regCmdty = new UIRegCmdty();
        regCmdtyComposition = new UIRegCmdtyComposition();
        request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
        regCmdty.init(request, urlParam);
        regCmdtyComposition.init(request, urlParam, databaseConnection.getConnection());
        regCmdtyComposition.setFirstDispFlg("1");
        regCmdtyComposition.setEditModeTxt(regCmdty.getEditModeTxt());
        regCmdty.setShopInfo(databaseConnection.getConnection());
        
        // インスタンスの作成
        UICmdtyInfo cmdtyInfoID = new UICmdtyInfo();
        cmdtyInfoID.init(request, urlParam);
        UIRegCmdtyPreview regCmdtyPreview = new UIRegCmdtyPreview(cmdtyInfoID.getShopCodeTxt(), cmdtyInfoID.getCmdtyCodeTxt(), cmdtyInfoID.getIndividualCodeTxt());
        // データの取得
        regCmdtyPreview.reset(databaseConnection.getConnection());
        regCmdtyPreview.initSet(request, urlParam);
        
        // 各明細行を最新にする
        try {
          regCmdtyComposition.compositionReflesh(databaseConnection.getConnection());
       } catch (SIDBAccessException e) {
         e.printStackTrace();
         throw new ServletException();
       }
        // 再計算する
        String cmdtyCompositionFlgTmp = regCmdtyComposition.getCmdtyCompositionFlg();
        regCmdtyComposition.compositionInfo();
        if (regCmdtyComposition.getCmdtyCompositionFlg().equals("0")
            || regCmdtyComposition.getCmdtyCompositionFlg().equals("-1")){
          regCmdtyComposition.setCmdtyCompositionFlg(cmdtyCompositionFlgTmp);
        }
        
        // 各配列をソートする
        regCmdtyComposition.sortCompositionGroupData();
        regCmdtyComposition.sortCompositionAppended();
        regCmdtyComposition.sortCompositionOption();
        
        regCmdtyPreview.setCmdtyCompositionFlg(regCmdtyComposition.getCmdtyCompositionFlg());
        
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION_PREVIEW, regCmdtyComposition);
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_PREVIEW_NAME, regCmdtyPreview);
        forwardKey(request, response, "webshop.jsp.manager.cmdty.preview");
      } else if (SIConfig.SIACTION_CONFIRM.equalsIgnoreCase(actionName)) {// 確認画面へ
        cmdtyList = new UICmdtyListCond();
        cmdtyList.initSearch(request, urlParam);
        cmdtyList.setPageSizeSel(cmdtyList.getPrevSize());
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_LIST_NAME, cmdtyList);
        
        regCmdty = new UIRegCmdty();
        regCmdtyComposition = new UIRegCmdtyComposition();
        request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY);
        regCmdty.init(request, urlParam);
        regCmdtyComposition.init(request, urlParam, databaseConnection.getConnection());
        regCmdtyComposition.setEditModeTxt(regCmdty.getEditModeTxt());
        regCmdty.setShopInfo(databaseConnection.getConnection());
        
        UIRegCmdtyComposition regCmdtyCompositionTmp = regCmdtyComposition;
        
        try {
          boolean result = false;
          
          // 各明細行を最新にする
          regCmdtyComposition.compositionReflesh(databaseConnection.getConnection());
          
          // 入力チェック
          if (regCmdtyComposition.cmdtyCompositionValidate(request, databaseConnection.getConnection())
               && regCmdtyComposition.cmdtyValidate(request, databaseConnection.getConnection())){
            
            // 再計算する
            regCmdtyComposition.compositionInfo();
            regCmdtyComposition.compositionCalc(databaseConnection.getConnection());
            regCmdtyCompositionTmp = regCmdtyComposition;
            
            // 金額チェック
            if (regCmdtyComposition.calcValidate(request, databaseConnection.getConnection())) {
              result = true;
            }
          }
          
          if (!result){
            regCmdtyCompositionTmp.setFirstDispFlg("1");
            session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty);
            session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION, regCmdtyCompositionTmp);
            forwardKey(request, response, "webshop.jsp.manager.cmdty.set.edit");
          } else {
            regCmdtyComposition.updateSetFlagShipproductFlg();  // 代表構成フラグの初期化
            session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty);
            session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION, regCmdtyComposition);
            forwardKey(request, response, "webshop.jsp.manager.cmdty.set.confirm");
          }
        } catch (SIDBAccessException e) {
          regCmdtyComposition.setFirstDispFlg("1");
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("database.execute.error"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty);
          session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION, regCmdtyCompositionTmp);
          forwardKey(request, response, "webshop.jsp.manager.cmdty.set.edit");
        }
      } else if (SIConfig.SIACTION_REMODIFY.equalsIgnoreCase(actionName)) {// 再修正へ
        regCmdtyComposition = new UIRegCmdtyComposition();
        regCmdtyComposition = (UIRegCmdtyComposition) session.getAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION);
        regCmdtyComposition.setFirstDispFlg("1");
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION, regCmdtyComposition);
        forwardKey(request, response, "webshop.jsp.manager.cmdty.set.edit");
      } else if (SIConfig.SIACTION_REGIST.equalsIgnoreCase(actionName)) {// 登録
        regCmdty = new UIRegCmdty();
        regCmdtyComposition = new UIRegCmdtyComposition();
        regCmdty = (UIRegCmdty) session.getAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME);
        regCmdtyComposition = (UIRegCmdtyComposition) session.getAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_COMPOSITION);
        
        try {
          if (editMode.equalsIgnoreCase(SIConfig.SIEDIT_MODE_INSERT)) {
             // 商品情報の登録
            insertTableData(databaseConnection.getConnection(), regCmdtyComposition, manLogin);  // 商品構成マスタ
            insertTableDataCompositionGroup(databaseConnection.getConnection(), regCmdtyComposition, manLogin); // 商品構成グループマスタ・商品構成グループ明細
            insertTableDataAppendedCmdty(databaseConnection.getConnection(), regCmdtyComposition, manLogin); // 添付商品
            insertTableDataCmdtyOption(databaseConnection.getConnection(), regCmdtyComposition, manLogin);  // オプション
            
            //既存テーブル
            insertTableDataCmdty(databaseConnection.getConnection(), regCmdtyComposition); // 商品マスタ
            insertTableDataIndividual(databaseConnection.getConnection(), regCmdtyComposition); //商品在庫
            if (getIndividualCount(databaseConnection.getConnection(), regCmdtyComposition) == 0) {
              insertCmdtyctgrymtbl(databaseConnection.getConnection(), regCmdtyComposition); // 商品カテゴリマスタ
            }
            insertTableDataStock(databaseConnection.getConnection(), regCmdtyComposition); // 在庫
            setInsertLog(databaseConnection.getConnection(), regCmdtyComposition, manLogin); // ログ
            updateTableDataIndividual(databaseConnection.getConnection(), regCmdtyComposition); // 在庫更新
            createStoreHistory(databaseConnection.getConnection(), regCmdtyComposition); // 店舗ごと在庫
            updateTableDataBonusPointIndividual(databaseConnection.getConnection(), regCmdtyComposition, manLogin); // ポイント
            
            // 関連商品マスタ登録
            List compositionCmdtyList = regCmdtyComposition.getCompositionCmdtyInsertList();
            insertTableDataConnCmdty(databaseConnection.getConnection(), regCmdtyComposition, compositionCmdtyList);
            
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.insert"));
            try {databaseConnection.getConnection().commit();} catch (SQLException sqle) {}
          } else if (editMode.equalsIgnoreCase(SIConfig.SIEDIT_MODE_UPDATE)) {
            
            // 商品構成情報を取得し、削除する。
            List compositionCmdtyList = regCmdtyComposition.getCompositionCmdtyNowList(databaseConnection.getConnection());
            deleteTableDataConnCmdty(databaseConnection.getConnection(), regCmdtyComposition, compositionCmdtyList);
            
            // 商品情報の登録
            updateTableDataStock(databaseConnection.getConnection(), regCmdtyComposition); // 在庫
            setUpdateLog(databaseConnection.getConnection(), regCmdtyComposition, manLogin); // ログ
            updateTableDataCmdty(databaseConnection.getConnection(), regCmdtyComposition); // 商品マスタ
            updateTableDataIndividual(databaseConnection.getConnection(), regCmdtyComposition); // 商品在庫
            updateTableData(databaseConnection.getConnection(), regCmdtyComposition, manLogin); // 商品構成マスタ
            
            deleteTableDataCompositionGroup(databaseConnection.getConnection(), regCmdtyComposition, manLogin); // 商品構成グループ
            updateTableDataCompositionGroup(databaseConnection.getConnection(), regCmdtyComposition, manLogin);
            deleteTableDataAppendedCmdty(databaseConnection.getConnection(), regCmdtyComposition); // 添付品
            updateTableDataAppendedCmdty(databaseConnection.getConnection(), regCmdtyComposition, manLogin);
            deleteTableDataCmdtyOption(databaseConnection.getConnection(), regCmdtyComposition);// オプション
            updateTableDataCmdtyOption(databaseConnection.getConnection(), regCmdtyComposition, manLogin);
            
            updateTableDataBonusPointIndividual(databaseConnection.getConnection(), regCmdtyComposition, manLogin); // ポイント
            
            //関連商品マスタ登録
            compositionCmdtyList = regCmdtyComposition.getCompositionCmdtyInsertList();
            insertTableDataConnCmdty(databaseConnection.getConnection(), regCmdtyComposition, compositionCmdtyList);
            
            request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.modify"));
            try {databaseConnection.getConnection().commit();} catch (SQLException sqle) {}
          } else log.error("Found a not defined edit mode.editMode=" + editMode);
          // 結果画面への遷移
          forwardKey(request, response, "webshop.jsp.manager.cmdty.set.result");
        } catch (SIDuplicateKeyException sqle) {
          try {databaseConnection.getConnection().rollback();} catch (SQLException ee) {}
          SICustomErrors errors = new SICustomErrors();
          errors.addError(new SICustomError("database.insert.duplicate"));
          request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors);
          forwardKey(request, response, "webshop.jsp.manager.cmdty.set.edit");
        } catch (SIDBAccessException sqle) {
          try {databaseConnection.getConnection().rollback();} catch (SQLException ee) {}
          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.cmdty.set.edit");
        }
      } else { // どのアクションにも当てはまらない
        cmdtyList = new UICmdtyListCond();
        cmdtyList.initSearch(request, urlParam);
        cmdtyList.setPageSizeSel(cmdtyList.getPrevSize());
        cmdtyList.validate(request);
        session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_LIST_NAME, cmdtyList);
        forwardKey(request, response, "webshop.jsp.manager.cmdty.list");
      }
    } catch (SQLException e) {
      e.printStackTrace();
      throw new ServletException();
    } catch (NamingException e) {
      e.printStackTrace();
      throw new ServletException();
    } finally {
      databaseConnection.close();
    }
  }
  
  /**
   * 商品別ポイントデータを作成します
   * @param lConnection
   * @param regCmdtyComposition
   * @param manLogin
   * @throws SIDBAccessException
   */
  public static void insertTableDataBonusPointIndividual(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition, SILogin manLogin) throws SIDBAccessException {
    UIRegPointIndividual regPoint = new UIRegPointIndividual();
    regPoint.setCmdtyCode(regCmdtyComposition.getCmdtyCode());
    regPoint.setIndividualCode(regCmdtyComposition.getIndividualCode());
    regPoint.setBonusPointRate(regCmdtyComposition.getAddPointRate());
    
    SIDateTime lDateTime2 = new SIDateTime();
    //lDateTime2.addDay(1);
    
    regPoint.setBonusFromDateYear(lDateTime2.getYearStr());
    regPoint.setBonusFromDateMonth(lDateTime2.getMonthStr());
    regPoint.setBonusFromDateDay(lDateTime2.getDayStr());
    regPoint.setBonusFromDate(lDateTime2.getFullDate());
    regPoint.setBonusToDateYear("9999");
    regPoint.setBonusToDateMonth("01");
    regPoint.setBonusToDateDay("01");
    regPoint.setBonusToDate("9999/01/01");
    
    try {
      SIInsertRec lInsertRec = new SIInsertRec("BonusPointIndividualTbl");
      lInsertRec.add("CmdtyCode", regPoint.getCmdtyCode());
      lInsertRec.add("IndividualCode", regPoint.getIndividualCode());
      lInsertRec.add("BonusPointRate", regPoint.getBonusPointRate());
      lInsertRec.add("BonusFromDate", regPoint.getBonusFromDate());
      lInsertRec.add("BonusToDate", regPoint.getBonusToDate());
      lInsertRec.add("InsertUser", manLogin.getUserCode());
      lInsertRec.execute(lConnection);
    } catch (SIDuplicateKeyException e) {
      throw new SIDBAccessException(e);
    }
  }
  
  /**
   * 商品別ポイントデータを更新します
   * @param lConnection
   * @param regCmdtyComposition
   * @param manLogin
   * @throws SIDBAccessException
   */
  public static void updateTableDataBonusPointIndividual(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition, SILogin manLogin) throws SIDBAccessException {
    StringBuffer sqlBuf1 = new StringBuffer();
    StringBuffer sqlBuf2 = new StringBuffer();
    sqlBuf1.append("SELECT individualcode FROM bonuspointindividualtbl ");
    sqlBuf1.append("WHERE individualcode = ").append(SIDBUtil.SQL2Str(regCmdtyComposition.getIndividualCode()));
    sqlBuf1.append(" AND cmdtycode = ").append(SIDBUtil.SQL2Str(regCmdtyComposition.getCmdtyCode()));
    sqlBuf1.append(" AND bonusfromdate <= current_date AND bonustodate >= current_date");
    sqlBuf2.append(sqlBuf1);
    sqlBuf2.append(" AND bonuspointrate <> ").append(SIDBUtil.SQL2Str(regCmdtyComposition.getAddPointRate()));
    
    if (SIDBUtil.hasData(lConnection, sqlBuf2.toString())) {//今日を含むポイントレートの違うボーナス設定がある
      StringBuffer sqlBuf3 = new StringBuffer();
      sqlBuf3.append("SELECT individualcode FROM bonuspointindividualtbl ");
      sqlBuf3.append("WHERE individualcode = ").append(SIDBUtil.SQL2Str(regCmdtyComposition.getIndividualCode()));
      sqlBuf3.append(" AND cmdtycode = ").append(SIDBUtil.SQL2Str(regCmdtyComposition.getCmdtyCode()));
      sqlBuf3.append(" AND bonusfromdate < current_date AND bonustodate >= current_date");
      if (SIDBUtil.hasData(lConnection, sqlBuf3.toString())) {//昨日以前から始まってるボーナス設定がある
        sqlBuf3 = new StringBuffer();
        sqlBuf3.append("INSERT INTO bonuspointindividualtbl SELECT cmdtycode,individualcode,current_date,bonustodate,bonuspointrate,");
        sqlBuf3.append(SIDBUtil.SQL2Str(manLogin.getUserCode(), ",")).append(SIDBUtil.SQL2Str(manLogin.getUserCode(), " "));
        sqlBuf3.append("FROM bonuspointindividualtbl ");
        sqlBuf3.append("WHERE individualcode = ").append(SIDBUtil.SQL2Str(regCmdtyComposition.getIndividualCode()));
        sqlBuf3.append(" AND cmdtycode = ").append(SIDBUtil.SQL2Str(regCmdtyComposition.getCmdtyCode()));
        sqlBuf3.append(" AND bonusfromdate < current_date AND bonustodate >= current_date");
        SIDBUtil.execSQL(lConnection, sqlBuf3.toString());
        sqlBuf3 = new StringBuffer();
        sqlBuf3.append("UPDATE bonuspointindividualtbl SET bonustodate=current_date-1 ");
        sqlBuf3.append(",updateuser=").append(SIDBUtil.SQL2Str(manLogin.getUserCode(), " "));
        sqlBuf3.append("WHERE individualcode = ").append(SIDBUtil.SQL2Str(regCmdtyComposition.getIndividualCode()));
        sqlBuf3.append(" AND cmdtycode = ").append(SIDBUtil.SQL2Str(regCmdtyComposition.getCmdtyCode()));
        sqlBuf3.append(" AND bonusfromdate < current_date AND bonustodate >= current_date");
        SIDBUtil.execSQL(lConnection, sqlBuf3.toString());//開始〜昨日、今日〜終了に分割する
      }
      try {
        UIRegPointIndividual regPoint = new UIRegPointIndividual();
        regPoint.setCmdtyCode(regCmdtyComposition.getCmdtyCode());
        regPoint.setIndividualCode(regCmdtyComposition.getIndividualCode());
        regPoint.setBonusPointRate(regCmdtyComposition.getAddPointRate());
        SIModifyRec lRec = new SIModifyRec("bonuspointindividualtbl");
        lRec.addCondition("cmdtyCode", regPoint.getCmdtyCode());
        lRec.addCondition("individualCode", regPoint.getIndividualCode());
        lRec.addCondition(new SITableCondition(" AND bonusfromdate = current_date "));
        lRec.add("BonusPointRate", regPoint.getBonusPointRate());
        lRec.add("updateuser", manLogin.getUserCode());
        lRec.execute(lConnection);//今日〜終了のポイントレートを書き換え
      } catch (SIDuplicateKeyException e) {
        throw new SIDBAccessException(e);
      }
    } else if (!SIDBUtil.hasData(lConnection, sqlBuf1.toString())) {//今日を含むボーナス設定がない
      insertTableDataBonusPointIndividual(lConnection, regCmdtyComposition, manLogin);
    }
  }
  
  /**
   * 関連商品マスタに構成商品を登録します
   * @param lConnection
   * @param regCmdtyComposition
   * @throws SIDBAccessException
   */
  public static void insertTableDataConnCmdty(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition, List compositionCmdtyList) throws SIDBAccessException {
    if (compositionCmdtyList != null){
      for (int i = 0; i < compositionCmdtyList.size(); i++){
        SICmdty regCmdty = (SICmdty) compositionCmdtyList.get(i);
        
        try {
          SIInsertRec lRec = new SIInsertRec("conncmdtymtbl");
          lRec.add("shopCode", regCmdtyComposition.getShopCode());//
          lRec.add("cmdtyCode", regCmdtyComposition.getCmdtyCode());//
          lRec.add("connshopcode", regCmdty.getShopCode());//
          lRec.add("conncmdtycode", regCmdty.getCmdtyCode());//
          lRec.add("disporder", "999");//最大値を設定
          lRec.execute(lConnection);
          
          lRec = new SIInsertRec("conncmdtymtbl");
          lRec.add("shopCode", regCmdty.getShopCode());//
          lRec.add("cmdtyCode", regCmdty.getCmdtyCode());//
          lRec.add("connshopcode", regCmdtyComposition.getShopCode());//
          lRec.add("conncmdtycode", regCmdtyComposition.getCmdtyCode());//
          lRec.add("disporder", "999");//最大値を設定
          lRec.execute(lConnection);
        } catch (SIDuplicateKeyException e) {
          throw new SIDBAccessException(e);
        }
      }
    }
  }
  
  /**
   * 関連商品マスタに登録されているセット商品-旧構成商品情報を削除します。
   * @param lConnection
   * @param regCmdtyComposition
   * @throws SIDBAccessException
   */
  public static void deleteTableDataConnCmdty(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition, List compositionCmdtyList) throws SIDBAccessException {
    if (compositionCmdtyList != null){
      for (int i = 0; i < compositionCmdtyList.size(); i++){
        SICmdty regCmdty = (SICmdty) compositionCmdtyList.get(i);
        
        try {
          SIDeleteRec lRecDel = new SIDeleteRec("conncmdtymtbl");
          lRecDel.addCondition("shopCode", regCmdtyComposition.getShopCode());//
          lRecDel.addCondition("cmdtyCode", regCmdtyComposition.getCmdtyCode());//
          lRecDel.addCondition("connshopcode", regCmdty.getShopCode());//
          lRecDel.addCondition("conncmdtycode", regCmdty.getCmdtyCode());//
          lRecDel.execute(lConnection);
          
          lRecDel = new SIDeleteRec("conncmdtymtbl");
          lRecDel.addCondition("shopCode", regCmdty.getShopCode());//
          lRecDel.addCondition("cmdtyCode", regCmdty.getCmdtyCode());//
          lRecDel.addCondition("connshopcode", regCmdtyComposition.getShopCode());//
          lRecDel.addCondition("conncmdtycode", regCmdtyComposition.getCmdtyCode());//
          lRecDel.execute(lConnection);
        } catch (SIDuplicateKeyException e) {
          throw new SIDBAccessException(e);
        }
      }
    }
  }
  
  /**
   * <b>insertTableData</b> 商品構成マスタの生成
   *
   * @param lConnection DBへのコネクション
   * @param regCmdtyComposition 入力したデータ
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public static void insertTableData(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition, SILogin manLogin) throws SIDuplicateKeyException, SIDBAccessException {
    SISpcType lSpcType = new SISpcType();
    
    SIInsertRec lRec = new SIInsertRec("cmdtycompositionmtbl");
    lRec.add("shopcode", regCmdtyComposition.getShopCode());
    lRec.add("cmdtycode", regCmdtyComposition.getCmdtyCode());
    lRec.add("individualcode", regCmdtyComposition.getIndividualCode());
    lRec.add("cmdtysetflg", regCmdtyComposition.getCmdtySetFlg());
    lRec.add("unitquantity", regCmdtyComposition.getUnitQuantity());
    lRec.add("sectioncount", regCmdtyComposition.getSectionCount());
    lRec.add("appendedcmdtyflg", regCmdtyComposition.getAppendedCmdtyFlg());
    if (SIUtil.isNull(regCmdtyComposition.getSetDiscount())){
      lRec.add("setdiscount", "0");
      lRec.add("setdiscountwithouttax", "0");
    } else {
      lRec.add("setdiscount", regCmdtyComposition.getSetDiscount());
      lRec.add("setdiscountwithouttax", regCmdtyComposition.getSetDiscountWithoutTax());
    }
    if (SIUtil.isNull(regCmdtyComposition.getAddPointRate())) regCmdtyComposition.setAddPointRate("0");
    lRec.add("addpointrate", regCmdtyComposition.getAddPointRate());
    lRec.add("beforebgprice", regCmdtyComposition.getBeforeBgprice());
    lRec.add("dispcarriagefree", regCmdtyComposition.getDispCarriageFreeFlg());
    lRec.add("discountdispflg", regCmdtyComposition.getDiscountDispFlg());
    lRec.add("initusercode", manLogin.getUserCode());
    lRec.add("updateusercode", manLogin.getUserCode());
    lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
    lRec.add("initdatetime", lSpcType);
    lRec.add("updatedatetime", lSpcType);
    
    // 商品構成マスタテーブルの登録
    lRec.execute(lConnection);
  }
  
  /**
   * <b>updateTableData</b> 商品構成マスタの更新
   *
   * @param lConnection DBへのコネクション
   * @param regCmdtyComposition 入力したデータ
   * @return なし
   * @throws SIDBAccessException
   */
  public static void updateTableData(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition, SILogin manLogin) throws SIDBAccessException {
    try {
      SISpcType lSpcType = new SISpcType();
      
      SIModifyRec lRec = new SIModifyRec("cmdtycompositionmtbl");
      lRec.addCondition("shopCode", regCmdtyComposition.getShopCode());// ショップコード
      lRec.addCondition("cmdtyCode", regCmdtyComposition.getCmdtyCode());//
      lRec.addCondition("individualCode", regCmdtyComposition.getIndividualCode());//
      lRec.add("cmdtysetflg", regCmdtyComposition.getCmdtySetFlg());
      lRec.add("unitquantity", regCmdtyComposition.getUnitQuantity());
      lRec.add("sectioncount", regCmdtyComposition.getSectionCount());
      lRec.add("appendedcmdtyflg", regCmdtyComposition.getAppendedCmdtyFlg());
      if (SIUtil.isNull(regCmdtyComposition.getSetDiscount())){
        lRec.add("setdiscount", "0");
        lRec.add("setdiscountwithouttax", "0");
      } else {
        lRec.add("setdiscount", regCmdtyComposition.getSetDiscount());
        lRec.add("setdiscountwithouttax", regCmdtyComposition.getSetDiscountWithoutTax());
      }
      if (SIUtil.isNull(regCmdtyComposition.getAddPointRate())) regCmdtyComposition.setAddPointRate("0");
      lRec.add("addpointrate", regCmdtyComposition.getAddPointRate());
      lRec.add("beforebgprice", regCmdtyComposition.getBeforeBgprice());
      lRec.add("dispcarriagefree", regCmdtyComposition.getDispCarriageFreeFlg());
      lRec.add("discountdispflg", regCmdtyComposition.getDiscountDispFlg());
      lRec.add("initusercode", manLogin.getUserCode());
      lRec.add("updateusercode", manLogin.getUserCode());
      lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
      lRec.add("initdatetime", lSpcType);
      lRec.add("updatedatetime", lSpcType);
      
      // 商品構成マスタテーブルの登録
      lRec.execute(lConnection);
    } catch (SIDuplicateKeyException sqle) {
      throw new SIDBAccessException(sqle);
    }
  }
  
  /**
   * <b>insertTableDataCmdty</b> 商品マスタの生成
   *
   * @param lConnection
   * @param regCmdtyComposition
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public static void insertTableDataCmdty(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition) throws SIDuplicateKeyException, SIDBAccessException {
    UICmdtyParent cmdtyParent = new UICmdtyParent();
    cmdtyParent.setShopCode(regCmdtyComposition.getShopCode());
    cmdtyParent.setCmdtyCode(regCmdtyComposition.getCmdtyCode());
    cmdtyParent.setJanCode("");
    cmdtyParent.setCmdtyName(regCmdtyComposition.getCmdtyName());
    cmdtyParent.setTaxFlg(regCmdtyComposition.getTaxFlg());
    cmdtyParent.setMakerCode(regCmdtyComposition.getMakerCode());
    cmdtyParent.setColorCode("1"); // ブランク固定
    cmdtyParent.setNewFixedPrice(regCmdtyComposition.getNewFixedPrice());
    cmdtyParent.setSalonPrice(regCmdtyComposition.getSalonPrice());
    cmdtyParent.setBannerURL("");
    cmdtyParent.setSrchKeyName1("");
    cmdtyParent.setSrchKeyName2("");
    cmdtyParent.setSrchKeyName3("");
    cmdtyParent.setCmdtySize("1"); // ランクAを設定
    cmdtyParent.setDeliveryTypeCode("0"); // 個別を設定
    cmdtyParent.setDisableFlg(regCmdtyComposition.getDisableFlg());
    cmdtyParent.setOtherName("");
    cmdtyParent.setCmdtyCompositionFlg(regCmdtyComposition.getCmdtyCompositionFlg());
    cmdtyParent.setInitDateTime(regCmdtyComposition.getInitDatetime());
    
    SIRegCmdtySrv.insertTableDataBG(lConnection, cmdtyParent);
  }
  
  /**
   * <b>updateTableDataCmdty</b> 商品マスタの更新
   * @param lConnection
   * @param regCmdtyComposition
   * @throws SIDBAccessException
   */
  public static void updateTableDataCmdty(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition) throws SIDBAccessException {
    UICmdtyParent cmdtyParent = new UICmdtyParent();
    cmdtyParent.setShopCode(regCmdtyComposition.getShopCode());
    cmdtyParent.setCmdtyCode(regCmdtyComposition.getCmdtyCode());
    cmdtyParent.setJanCode("");
    cmdtyParent.setCmdtyName(regCmdtyComposition.getCmdtyName());
    cmdtyParent.setTaxFlg(regCmdtyComposition.getTaxFlg());
    cmdtyParent.setMakerCode(regCmdtyComposition.getMakerCode());
    cmdtyParent.setColorCode("1"); // ブランク固定
    cmdtyParent.setNewFixedPrice(regCmdtyComposition.getNewFixedPrice());
    cmdtyParent.setSalonPrice(regCmdtyComposition.getSalonPrice());
    cmdtyParent.setBannerURL("");
    cmdtyParent.setSrchKeyName1("");
    cmdtyParent.setSrchKeyName2("");
    cmdtyParent.setSrchKeyName3("");
    cmdtyParent.setCmdtySize("1"); // ランクAを設定
    cmdtyParent.setDeliveryTypeCode("0"); // 個別を設定
    cmdtyParent.setDisableFlg(regCmdtyComposition.getDisableFlg());
    cmdtyParent.setOtherName("");
    cmdtyParent.setCmdtyCompositionFlg(regCmdtyComposition.getCmdtyCompositionFlg());
    cmdtyParent.setInitDateTime(regCmdtyComposition.getInitDatetime());
    
    SIRegCmdtySrv.updateTableDataBG(lConnection, cmdtyParent);
  }
  
  /**
   * <b>insertTableDataIndividual</b> 商品在庫マスタの登録
   *
   * @param lConnection DBへのコネクション
   * @param regCmdtyComposition 入力したデータ
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public static void insertTableDataIndividual(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition) throws SIDuplicateKeyException, SIDBAccessException {
    UIIndividual regIndividual = new UIIndividual();
    
    regIndividual.setShopCode(regCmdtyComposition.getShopCode());
    regIndividual.setCmdtyCode(regCmdtyComposition.getCmdtyCode());
    regIndividual.setIndividualCode(regCmdtyComposition.getIndividualCode());
    regIndividual.setUsedNewFlg(regCmdtyComposition.getUsedNewFlg());
    SIDateTime lDate = new SIDateTime();
    regIndividual.setArrivalDate(SIDateTime.getDate(lDate.getYearStr(), lDate.getMonthStr(), lDate.getDayStr()));
    regIndividual.setProcessingExpence(regCmdtyComposition.getProcessingexpence());
    regIndividual.setTenureofuse(regCmdtyComposition.getTenureofuse());
    regIndividual.setSalesSituationFlg("0");
    regIndividual.setSoldOutDispFlg("0");
    regIndividual.setCampaignPriceFlg(regCmdtyComposition.getCampaignPriceFlg());
    regIndividual.setInquiryFlg(regCmdtyComposition.getInquiryFlg());
    regIndividual.setFrontDispFlg(regCmdtyComposition.getFrontDispFlg());
    regIndividual.setCustCode("");
    regIndividual.setCustName("");
    regIndividual.setComment("");
    regIndividual.setBgPriceDispFlg(regCmdtyComposition.getBgPriceDispFlg());
    regIndividual.setConsignGoodsFlg("0");
    regIndividual.setConsignmentMemo("");
    regIndividual.setCommodityExplanation(regCmdtyComposition.getCommodityExplanation());
    regIndividual.setStockCode("");
    regIndividual.setPurchasePrice(regCmdtyComposition.getPurchaseprice());
    regIndividual.setBgPrice(regCmdtyComposition.getBgPrice());
    regIndividual.setBgPriceWithoutTax(regCmdtyComposition.getBgPriceWithoutTax());
    regIndividual.setSpecialPrice("0");
    regIndividual.setRecommendationFlg("0");
    regIndividual.setCommodityBannerFile("");
    regIndividual.setMemberDiscountFlg(regCmdtyComposition.getMemberDiscountFlg());
    regIndividual.setSellFromDate("");
    regIndividual.setEndSellFlg("0");
    regIndividual.setSellToDate("");
    regIndividual.setSalesFromdate("");
    regIndividual.setSalesToDate("");
    regIndividual.setRsrvEnableFlg("0");
    regIndividual.setRsrvAmount("");
    regIndividual.setNoStockFlg("0");
    regIndividual.setAmountFlg("0");
    regIndividual.setStockStatusCode("");
    regIndividual.setSoldOutDate("");
    regIndividual.setGuaranteedTerm(regCmdtyComposition.getGuaranteedTerm());
    regIndividual.setWrappingFlg("0");
    regIndividual.setDiscountFlg("0");
    regIndividual.setSalesDiscountFlg("0");
    regIndividual.setAdviceDispOrder("");
    regIndividual.setImageName1(regCmdtyComposition.getImageName1());
    regIndividual.setImageName2(regCmdtyComposition.getImageName2());
    regIndividual.setImageName3(regCmdtyComposition.getImageName3());
    regIndividual.setImageName4(regCmdtyComposition.getImageName4());
    regIndividual.setImageName5(regCmdtyComposition.getImageName5());
    regIndividual.setImageName6(regCmdtyComposition.getImageName6());
    regIndividual.setImageName7(regCmdtyComposition.getImageName7());
    regIndividual.setImageName8(regCmdtyComposition.getImageName8());
    regIndividual.setImageName9(regCmdtyComposition.getImageName9());
    regIndividual.setImageName10(regCmdtyComposition.getImageName10());
    regIndividual.setImageName11(regCmdtyComposition.getImageName11());
    regIndividual.setRemarks(regCmdtyComposition.getRemarks());
    regIndividual.setInitDateTime(regCmdtyComposition.getInitDatetime());
    regIndividual.setSpCampaignFlg(regCmdtyComposition.getSpCampaignFlg());
    regIndividual.setNomalPrice(regCmdtyComposition.getNomalPrice());
    regIndividual.setAlertAmount("");
    regIndividual.setPurchasePrice2("0");
    regIndividual.setArrivalScheduleMsg("");
    regIndividual.setJanCodeIndividual("");
    regIndividual.setConsignGoodsFee("0");
    regIndividual.setPriceDownFlg(regCmdtyComposition.getPriceDownFlg());
    regIndividual.setExpansionComment1Title(regCmdtyComposition.getExpansionComment1Title());
    regIndividual.setExpansionComment1Text(regCmdtyComposition.getExpansionComment1Text());
    regIndividual.setExpansionComment2Title(regCmdtyComposition.getExpansionComment2Title());
    regIndividual.setExpansionComment2Text(regCmdtyComposition.getExpansionComment2Text());
    regIndividual.setExpansionComment3Title(regCmdtyComposition.getExpansionComment3Title());
    regIndividual.setExpansionComment3Text(regCmdtyComposition.getExpansionComment3Text());
    regIndividual.setExpansionComment4Title(regCmdtyComposition.getExpansionComment4Title());
    regIndividual.setExpansionComment4Text(regCmdtyComposition.getExpansionComment4Text());
    // EDBTG005-00 kamata add start
    regIndividual.setExpansionComment5Title(regCmdtyComposition.getExpansionComment5Title());
    regIndividual.setExpansionComment5Text(regCmdtyComposition.getExpansionComment5Text());
    // EDBTG005-00 kamata add end
    regIndividual.setSetItem1("");
    regIndividual.setSetItem2("");
    
    SIRegIndividualSrv.insertTableDataIndividual(lConnection, regIndividual);
  }
  
  /**
   * <b>updateTableDataIndividual</b> 商品在庫マスタの更新
   *
   * @param lConnection DBへのコネクション
   * @param regIndividual 入力したデータ
   * @return なし
   * @throws SIDBAccessException
   */
  public static void updateTableDataIndividual(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition) throws SIDBAccessException {
    UIIndividual regIndividual = new UIIndividual();
    
    regIndividual.setShopCode(regCmdtyComposition.getShopCode());
    regIndividual.setCmdtyCode(regCmdtyComposition.getCmdtyCode());
    regIndividual.setIndividualCode(regCmdtyComposition.getIndividualCode());
    regIndividual.setUsedNewFlg(regCmdtyComposition.getUsedNewFlg());
    SIDateTime lDate = new SIDateTime();
    regIndividual.setArrivalDate(SIDateTime.getDate(lDate.getYearStr(), lDate.getMonthStr(), lDate.getDayStr()));
    regIndividual.setProcessingExpence(regCmdtyComposition.getProcessingexpence());
    regIndividual.setTenureofuse(regCmdtyComposition.getTenureofuse());
    regIndividual.setSalesSituationFlg("0");
    regIndividual.setSoldOutDispFlg("0");
    regIndividual.setCampaignPriceFlg(regCmdtyComposition.getCampaignPriceFlg());
    regIndividual.setInquiryFlg(regCmdtyComposition.getInquiryFlg());
    regIndividual.setFrontDispFlg(regCmdtyComposition.getFrontDispFlg());
    regIndividual.setCustCode("");
    regIndividual.setCustName("");
    regIndividual.setComment("");
    regIndividual.setBgPriceDispFlg(regCmdtyComposition.getBgPriceDispFlg());
    regIndividual.setConsignGoodsFlg("0");
    regIndividual.setConsignmentMemo("");
    regIndividual.setCommodityExplanation(regCmdtyComposition.getCommodityExplanation());
    regIndividual.setStockCode("");
    regIndividual.setPurchasePrice(regCmdtyComposition.getPurchaseprice());
    regIndividual.setBgPrice(regCmdtyComposition.getBgPrice());
    regIndividual.setBgPriceWithoutTax(regCmdtyComposition.getBgPriceWithoutTax());
    regIndividual.setSpecialPrice("0");
    regIndividual.setRecommendationFlg("0");
    regIndividual.setCommodityBannerFile("");
    regIndividual.setMemberDiscountFlg(regCmdtyComposition.getMemberDiscountFlg());
    regIndividual.setSellFromDate("");
    regIndividual.setEndSellFlg("0");
    regIndividual.setSellToDate("");
    regIndividual.setSalesFromdate("");
    regIndividual.setSalesToDate("");
    regIndividual.setRsrvEnableFlg("0");
    regIndividual.setRsrvAmount("");
    regIndividual.setNoStockFlg("0");
    regIndividual.setAmountFlg("0");
    regIndividual.setStockStatusCode("");
    regIndividual.setSoldOutDate("");
    regIndividual.setGuaranteedTerm(regCmdtyComposition.getGuaranteedTerm());
    regIndividual.setWrappingFlg("0");
    regIndividual.setDiscountFlg("0");
    regIndividual.setSalesDiscountFlg("0");
    regIndividual.setAdviceDispOrder("");
    regIndividual.setImageName1(regCmdtyComposition.getImageName1());
    regIndividual.setImageName2(regCmdtyComposition.getImageName2());
    regIndividual.setImageName3(regCmdtyComposition.getImageName3());
    regIndividual.setImageName4(regCmdtyComposition.getImageName4());
    regIndividual.setImageName5(regCmdtyComposition.getImageName5());
    regIndividual.setImageName6(regCmdtyComposition.getImageName6());
    regIndividual.setImageName7(regCmdtyComposition.getImageName7());
    regIndividual.setImageName8(regCmdtyComposition.getImageName8());
    regIndividual.setImageName9(regCmdtyComposition.getImageName9());
    regIndividual.setImageName10(regCmdtyComposition.getImageName10());
    regIndividual.setImageName11(regCmdtyComposition.getImageName11());
    regIndividual.setRemarks(regCmdtyComposition.getRemarks());
    regIndividual.setInitDateTime(regCmdtyComposition.getInitDatetime());
    regIndividual.setSpCampaignFlg(regCmdtyComposition.getSpCampaignFlg());
    regIndividual.setNomalPrice(regCmdtyComposition.getNomalPrice());
    regIndividual.setAlertAmount("");
    regIndividual.setPurchasePrice2("0");
    regIndividual.setArrivalScheduleMsg("");
    regIndividual.setJanCodeIndividual("");
    regIndividual.setConsignGoodsFee("0");
    regIndividual.setPriceDownFlg(regCmdtyComposition.getPriceDownFlg());
    regIndividual.setExpansionComment1Title(regCmdtyComposition.getExpansionComment1Title());
    regIndividual.setExpansionComment1Text(regCmdtyComposition.getExpansionComment1Text());
    regIndividual.setExpansionComment2Title(regCmdtyComposition.getExpansionComment2Title());
    regIndividual.setExpansionComment2Text(regCmdtyComposition.getExpansionComment2Text());
    regIndividual.setExpansionComment3Title(regCmdtyComposition.getExpansionComment3Title());
    regIndividual.setExpansionComment3Text(regCmdtyComposition.getExpansionComment3Text());
    regIndividual.setExpansionComment4Title(regCmdtyComposition.getExpansionComment4Title());
    regIndividual.setExpansionComment4Text(regCmdtyComposition.getExpansionComment4Text());
    // EDBTG005-00 kamata add start
    regIndividual.setExpansionComment5Title(regCmdtyComposition.getExpansionComment5Title());
    regIndividual.setExpansionComment5Text(regCmdtyComposition.getExpansionComment5Text());
    // EDBTG005-00 kamata add end
    regIndividual.setSetItem1("");
    regIndividual.setSetItem2("");
    regIndividual.setCatchcopy(regCmdtyComposition.getCatchcopy());
    regIndividual.setSpecification(regCmdtyComposition.getSpecification());
    regIndividual.setActiveingredient(regCmdtyComposition.getActiveingredient());
    regIndividual.setComponent(regCmdtyComposition.getComponent());
    regIndividual.setPrecaution(regCmdtyComposition.getPrecaution());
    regIndividual.setOthernotice(regCmdtyComposition.getOthernotice());
    
    SIRegIndividualSrv.updateTableDataIndividual(lConnection, regIndividual);
  }
  
  /**
   * <b>insertTableDataCompositionGroup</b> 商品構成グループの生成
   *
   * @param lConnection
   * @param regCmdtyComposition
   * @param manLogin
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public static void insertTableDataCompositionGroup(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition, SILogin manLogin) throws SIDuplicateKeyException, SIDBAccessException {
    SISpcType lSpcType = new SISpcType();
    
    for (int i = 0; i < regCmdtyComposition.getCompositionGroupList().size(); i++){
      SICmdtyCompositionGroupData groupData = (SICmdtyCompositionGroupData) regCmdtyComposition.getCompositionGroupList().get(i);
      
      SIInsertRec lRec = new SIInsertRec("compositiongroupmtbl");
      lRec.add("shopcode", regCmdtyComposition.getShopCode());
      lRec.add("cmdtycode", regCmdtyComposition.getCmdtyCode());
      lRec.add("individualcode", regCmdtyComposition.getIndividualCode());
      lRec.add("groupcode", groupData.getGroupCode());
      lRec.add("specificationname", groupData.getSpecificationName());
      lRec.add("colorflg", groupData.getColorFlg());
      lRec.add("initusercode", manLogin.getUserCode());
      lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
      lRec.add("initdatetime", lSpcType);
      lRec.execute(lConnection);
      
      // ここで明細行も登録
      insertTableDataCompositionGroupDetail(lConnection, groupData, manLogin);
    }
  }
  
  /**
   * <b>updateTableDataCompositionGroup</b> 商品構成グループの更新
   *
   * @param lConnection DBへのコネクション
   * @param regIndividual 入力したデータ
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public static void updateTableDataCompositionGroup(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition, SILogin manLogin) throws SIDuplicateKeyException, SIDBAccessException {
    insertTableDataCompositionGroup(lConnection, regCmdtyComposition, manLogin);
  }
  
  /**
   * <b>updateTableDataCompositionGroup</b> 商品構成グループの削除
   *
   * @param lConnection
   * @param regCmdtyComposition
   * @param manLogin
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public static void deleteTableDataCompositionGroup(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition, SILogin manLogin) throws SIDBAccessException {
    try {
      // 商品構成マスタ削除
      SIDeleteRec lRecDel = new SIDeleteRec("compositiongroupmtbl");
      lRecDel.addCondition("shopCode", regCmdtyComposition.getShopCode());// ショップコード
      lRecDel.addCondition("cmdtyCode", regCmdtyComposition.getCmdtyCode());//
      lRecDel.addCondition("individualCode", regCmdtyComposition.getIndividualCode());//
      lRecDel.execute(lConnection);
      
      // 商品構成マスタ明細削除
      lRecDel = new SIDeleteRec("compositiongroupdetailtbl");
      lRecDel.addCondition("shopCode", regCmdtyComposition.getShopCode());// ショップコード
      lRecDel.addCondition("cmdtyCode", regCmdtyComposition.getCmdtyCode());//
      lRecDel.addCondition("individualCode", regCmdtyComposition.getIndividualCode());//
      lRecDel.execute(lConnection);
      
    } catch (SIDuplicateKeyException e) {
      throw new SIDBAccessException(e);
    }
  }
  
  /**
   * <b>insertTableDataCompositionGroupDetail</b> 商品構成グループ明細生成
   *
   * @param lConnection
   * @param groupDeta
   * @param manLogin
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public static void insertTableDataCompositionGroupDetail(Connection lConnection, SICmdtyCompositionGroupData groupDeta, SILogin manLogin) throws SIDuplicateKeyException, SIDBAccessException {
    SISpcType lSpcType = new SISpcType();
    
    for (int i = 0; i < groupDeta.getCompositionGroupDetail().size(); i++){
      SICmdtyCompositionGroupDetail groupDetail = (SICmdtyCompositionGroupDetail) groupDeta.getCompositionGroupDetail().get(i);
      
      SIInsertRec lRec = new SIInsertRec("compositiongroupdetailtbl");
      lRec.add("shopcode", groupDeta.getShopCode());
      lRec.add("cmdtycode", groupDeta.getCmdtyCode());
      lRec.add("individualcode", groupDeta.getIndividualCode());
      lRec.add("groupcode", groupDeta.getGroupCode());
      lRec.add("detailcmdtycode", groupDetail.getDetailCmdtyCode());
      lRec.add("detailindividualcode", groupDetail.getDetailIndividualCode());
      lRec.add("detailindividualname", groupDetail.getDetailIndividualName());
      lRec.add("compositioncount", groupDetail.getCompositionCount());
      lRec.add("disporder", groupDetail.getDispOrder());
      lRec.add("flagshipproductflg", groupDetail.getFlagShipproductFlg());
      lRec.add("initusercode", manLogin.getUserCode());
      lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
      lRec.add("initdatetime", lSpcType);
      lRec.execute(lConnection);
    }
  }

  /**
   * <b>insertTableDataAppendedCmdty</b> 添付商品生成
   *
   * @param lConnection
   * @param regCmdtyComposition
   * @param manLogin
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public static void insertTableDataAppendedCmdty(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition, SILogin manLogin) throws SIDuplicateKeyException, SIDBAccessException {
    SISpcType lSpcType = new SISpcType();
    
    for (int i = 0; i < regCmdtyComposition.getCompositionAppendedList().size(); i++){
      SICmdtyCompositionAppended appendedData = (SICmdtyCompositionAppended) regCmdtyComposition.getCompositionAppendedList().get(i);
      
      SIInsertRec lRec = new SIInsertRec("appendedcmdtytbl");
      
      lRec.add("shopCode", regCmdtyComposition.getShopCode());// ショップコード
      lRec.add("cmdtyCode", regCmdtyComposition.getCmdtyCode());//
      lRec.add("individualCode", regCmdtyComposition.getIndividualCode());//
      lRec.add("detailcmdtycode", appendedData.getDetailCmdtyCode());
      lRec.add("detailindividualcode", appendedData.getDetailIndividualCode());
      lRec.add("setcount", appendedData.getSetCount());
      lRec.add("appendedcount", appendedData.getAppendedCount());
      lRec.add("disporder", appendedData.getDispOrder());
      lRec.add("initusercode", manLogin.getUserCode());
      lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
      lRec.add("initdatetime", lSpcType);
      lRec.execute(lConnection);
    }
  }
  
  /**
   * <b>updateTableDataAppendedCmdty</b> 添付商品更新
   *
  * @param lConnection
  * @param regCmdtyComposition
  * @param manLogin
  * @throws SIDuplicateKeyException
  * @throws SIDBAccessException
  */
  public static void updateTableDataAppendedCmdty(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition, SILogin manLogin) throws SIDuplicateKeyException, SIDBAccessException {
    insertTableDataAppendedCmdty(lConnection, regCmdtyComposition, manLogin);
  }
  
  /**
   * <b>deleteTableDataAppendedCmdty</b> 添付商品削除
   *
   * @param lConnection
   * @param regCmdtyComposition
   * @throws SIDBAccessException
   */
  public static void deleteTableDataAppendedCmdty(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition) throws SIDBAccessException {
    try {
      SIDeleteRec lRecDel = new SIDeleteRec("appendedcmdtytbl");
      lRecDel.addCondition("shopCode", regCmdtyComposition.getShopCode());// ショップコード
      lRecDel.addCondition("cmdtyCode", regCmdtyComposition.getCmdtyCode());//
      lRecDel.addCondition("individualCode", regCmdtyComposition.getIndividualCode());//
      lRecDel.execute(lConnection);
    } catch (SIDuplicateKeyException e) {
      throw new SIDBAccessException(e);
    }
  }
  
  /**
   * <b>insertTableDataCmdtyOption</b> オプション生成
   *
   * @param lConnection
   * @param regCmdtyComposition
   * @param manLogin
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public static void insertTableDataCmdtyOption(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition, SILogin manLogin) throws SIDuplicateKeyException, SIDBAccessException {
    SISpcType lSpcType = new SISpcType();
    
    for (int i = 0; i < regCmdtyComposition.getCompositionOptionList().size(); i++){
      SICmdtyCompositionOption optionData = (SICmdtyCompositionOption) regCmdtyComposition.getCompositionOptionList().get(i);
      
      SIInsertRec lRec = new SIInsertRec("compositionoptiontbl");
      lRec.add("shopCode", regCmdtyComposition.getShopCode());// ショップコード
      lRec.add("cmdtyCode", regCmdtyComposition.getCmdtyCode());//
      lRec.add("individualCode", regCmdtyComposition.getIndividualCode());//
      lRec.add("detailcmdtycode", optionData.getDetailCmdtyCode());
      lRec.add("detailindividualcode", optionData.getDetailIndividualCode());
      lRec.add("compositioncount", optionData.getCompositionCount());
      lRec.add("disporder", optionData.getDispOrder());
      lRec.add("notices", optionData.getNotices());
      lRec.add("initusercode", manLogin.getUserCode());
      lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
      lRec.add("initdatetime", lSpcType);
      lRec.execute(lConnection);
    }
  }
  
  /**
   * <b>updateTableDataCmdtyOption</b> オプション更新
  *
  * @param lConnection
  * @param regCmdtyComposition
  * @param manLogin
  * @throws SIDuplicateKeyException
  * @throws SIDBAccessException
  */
  public static void updateTableDataCmdtyOption(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition, SILogin manLogin) throws SIDuplicateKeyException, SIDBAccessException {
    insertTableDataCmdtyOption(lConnection, regCmdtyComposition, manLogin);
  }
  
  /**
   * <b>updateTableDataCmdtyOption</b> オプション削除
   *
   * @param lConnection
   * @param regCmdtyComposition
   * @throws SIDBAccessException
   */
  public static void deleteTableDataCmdtyOption(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition) throws SIDBAccessException {
    try {
      SIDeleteRec lRecDel = new SIDeleteRec("compositionoptiontbl");
      lRecDel.addCondition("shopCode", regCmdtyComposition.getShopCode());// ショップコード
      lRecDel.addCondition("cmdtyCode", regCmdtyComposition.getCmdtyCode());//
      lRecDel.addCondition("individualCode", regCmdtyComposition.getIndividualCode());//
      lRecDel.execute(lConnection);
    } catch (SIDuplicateKeyException e) {
      throw new SIDBAccessException(e);
    }
  }
  
  /**
   * <b>insertCmdtyctgrymtbl</b> 商品カテゴリマスタ生成
   *
   * @param Connection DBへのコネクション
   * @param UIIndividual
   * @return なし
   * @throws SIDBAccessException
   * @throws SIDuplicateKeyException
   */
  public static void insertCmdtyctgrymtbl(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition) throws SIDuplicateKeyException, SIDBAccessException {
    SIInsertRec lRec = new SIInsertRec("cmdtyctgrymtbl");
    lRec.add("shopCode", regCmdtyComposition.getShopCode());
    lRec.add("cmdtycode", regCmdtyComposition.getCmdtyCode());
    lRec.add("ctgrycode", regCmdtyComposition.getIndividual1());
    lRec.execute(lConnection);
    if(regCmdtyComposition.getIndividual1().equals("DX")){
      lRec = new SIInsertRec("cmdtyctgrymtbl");
      lRec.add("shopCode", regCmdtyComposition.getShopCode());
      lRec.add("cmdtycode", regCmdtyComposition.getCmdtyCode());
      lRec.add("ctgrycode", "SF");
      lRec.execute(lConnection);
    }else if(regCmdtyComposition.getIndividual1().equals("MX")){
      lRec = new SIInsertRec("cmdtyctgrymtbl");
      lRec.add("shopCode", regCmdtyComposition.getShopCode());
      lRec.add("cmdtycode", regCmdtyComposition.getCmdtyCode());
      lRec.add("ctgrycode", "ZM");
      lRec.execute(lConnection);
    }
  }
  
  /**
   * <b>setInsertLog</b>
   *
   * @param lConnection
   * @param lIndividual
   * @param manLogin
   */
  public static void setInsertLog(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition, SILogin manLogin) {
    if (SIUtil.isNull(regCmdtyComposition.getCmdtyCode()) || SIUtil.isNull(regCmdtyComposition.getIndividualCode())) return;
    
    try {
      SIInsertRec lRec = new SIInsertRec("flagupdatelogtbl");
      lRec.add("cmdtycode", regCmdtyComposition.getCmdtyCode());
      lRec.add("individualcode", regCmdtyComposition.getIndividualCode());
      lRec.add("updateflagname", "bgprice-first");
      lRec.add("updateflagvalue", regCmdtyComposition.getBgPrice());
      lRec.add("updateuser", manLogin.getUserCode());
      lRec.add("initdatetime", new SIDateTime().getFullDateTime());
      lRec.execute(lConnection);
    } catch (Exception e) {
      e.printStackTrace();
      return;
    }
  }
  
  /**
   * <b>setUpdateLog</b>
   *
   * @param lConnection
   * @param regCmdtyComposition
   * @param manLogin
   */
  public static void setUpdateLog(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition, SILogin manLogin) {
    if (SIUtil.isNull(regCmdtyComposition.getCmdtyCode()) || SIUtil.isNull(regCmdtyComposition.getIndividualCode())) return;
    
    try {
      String bgPriceBefore = SIDBUtil.getFirstData(lConnection, "SELECT bgprice FROM individualtbl WHERE individualcode="+SIDBUtil.SQL2Str(regCmdtyComposition.getIndividualCode()));
      
      SIInsertRec lRec = new SIInsertRec("flagupdatelogtbl");
      lRec.add("cmdtycode", regCmdtyComposition.getCmdtyCode());
      lRec.add("individualcode", regCmdtyComposition.getIndividualCode());
      if (regCmdtyComposition.getBgPrice().equals(bgPriceBefore)) {
        lRec.add("updateflagname", "cmdtycompositionmtbl");
        lRec.add("updateflagvalue", "update");
      }else{
        lRec.add("updateflagname", "bgprice");
        lRec.add("updateflagvalue", regCmdtyComposition.getBgPrice());
      }
      lRec.add("updateuser", manLogin.getUserCode());
      lRec.add("initdatetime", new SIDateTime().getFullDateTime());
      lRec.execute(lConnection);
    } catch (Exception e) {
      e.printStackTrace();
      return;
    }
  }
  
  /**
   * <b>getIndividualCount</b> 商品カテゴリ関連Ｍに対する対象レコードの存在
   *
   * @param Connection
   * @param UIIndividual
   * @return 対象レコード数
   * @throws SIDBAccessException
   * @throws SQLException
   */
  public int getIndividualCount(Connection connection, UIRegCmdtyComposition regCmdtyComposition) throws SIDBAccessException, SQLException {
    Statement statement = null;
    ResultSet resultSet = null;
    StringBuffer countSqlBuf = new StringBuffer();// レコード数を求める
    int count = 0; // レコード数
    countSqlBuf.append("SELECT count(*) as cnt FROM cmdtyctgrymtbl WHERE shopCode = '0' ");
    countSqlBuf.append("and cmdtycode =" + SIDBUtil.SQL2Str(regCmdtyComposition.getCmdtyCode()));
    countSqlBuf.append("and ctgrycode =" + SIDBUtil.SQL2Str(regCmdtyComposition.getIndividual1()));
    try {
      statement = connection.createStatement();
      resultSet = statement.executeQuery(countSqlBuf.toString());
      while (resultSet.next()) {
        count = resultSet.getInt("cnt");
      }
    } finally {
      SIDBUtil.close(resultSet, statement);
    }
    return count;
  }
  
  /**
   * <b>insertTableDataStock</b> データベースにレコードを作成します。
   *
   * @param lConnection DBへのコネクション
   * @param individual 入力したデータ
   * @param Stock
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public static void insertTableDataStock(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition) throws SIDuplicateKeyException, SIDBAccessException {
    Collection branchColl = SIDBUtil.getCollection(lConnection, "SELECT branchcode,branchcode FROM branchtbl");
    Iterator branchIta = branchColl.iterator();
    
    while (branchIta.hasNext()) {
      SINameValue lNameValue = (SINameValue) branchIta.next();
      SISpcType lSpcType = new SISpcType();
      SIInsertRec lRec = new SIInsertRec("storestocktbl");
      lRec.add("shopCode", regCmdtyComposition.getShopCode());// ショップコード
      lRec.add("cmdtyCode", regCmdtyComposition.getCmdtyCode());//
      lRec.add("individualCode", regCmdtyComposition.getIndividualCode());//
      lRec.add("branchcode", lNameValue.getValue());//
      if (lNameValue.getValue().equals("18") || lNameValue.getValue().equals("999")){
        lRec.add("salespriorityflg", SIBGUtil.convertAto1("A"));//
      } else {
        lRec.add("salespriorityflg", SIBGUtil.convertAto1("Z"));//
      }
      lRec.add("amount", "0");//
      lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
      lRec.add("InitDateTime", lSpcType);// 登録日時
      lRec.add("UpdateDateTime", lSpcType);
      lRec.execute(lConnection);
    }
  }
  
  /**
   * <b>updateTableDataStock</b> データベースにレコードを作成します。
   *
   * @param lConnection DBへのコネクション
   * @param individual 入力したデータ
   * @param Stock
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public static void updateTableDataStock(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition) throws SIDuplicateKeyException, SIDBAccessException {
    Collection branchColl = SIDBUtil.getCollection(lConnection, "SELECT branchcode,branchcode FROM branchtbl");
    Iterator branchIta = branchColl.iterator();
    
    while (branchIta.hasNext()) {
      SINameValue lNameValue = (SINameValue) branchIta.next();
      
      SISpcType lSpcType = new SISpcType();
      SIModifyRec lRec = new SIModifyRec("storestocktbl");
      lRec.addCondition("shopCode", regCmdtyComposition.getShopCode());// ショップコード
      lRec.addCondition("cmdtyCode", regCmdtyComposition.getCmdtyCode());//
      lRec.addCondition("individualCode", regCmdtyComposition.getIndividualCode());//
      lRec.addCondition("branchcode", lNameValue.getValue());//
      if (lNameValue.getValue().equals("18") || lNameValue.getValue().equals("999")){
        lRec.add("salespriorityflg", SIBGUtil.convertAto1("A"));//
      } else {
        lRec.add("salespriorityflg", SIBGUtil.convertAto1("Z"));//
      }
      lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
      lRec.add("UpdateDateTime", lSpcType);
      // 商品マスタテーブルの更新
      lRec.execute(lConnection);
    }
  }
  
  /**
   * <b>createStoreHistory</b> データベースにレコードを作成します。(入庫履歴,出庫履歴,締処理,棚卸)
   *
   * @param Connection DBへのコネクション
   * @param UIIndividual
   * @return なし
   * @throws SIDBAccessException
   * @throws SIDuplicateKeyException
   */
  public static void createStoreHistory(Connection lConnection, UIRegCmdtyComposition regCmdtyComposition) throws SIDuplicateKeyException, SIDBAccessException {
    SIDateTime lDateTime = new SIDateTime();
    
    SIInsertRec lRec = new SIInsertRec("storehistorytbl");
    lRec.add("cmdtycode", regCmdtyComposition.getCmdtyCode());
    lRec.add("individualcode", regCmdtyComposition.getIndividualCode());
    lRec.add("storenumber", "1");
    lRec.add("storebranch", "999");
    lRec.add("storecharge", "");
    lRec.add("amount", "0");
    lRec.add("purchaseprice", "0");
    lRec.add("processingexpence", "0");
    lRec.add("storetype", "4");
    lRec.add("comment", "商品構成マスタ新規作成");
    lRec.add("delflg", "1");
    lRec.add("initdatetime", lDateTime.getFullDate());
    lRec.execute(lConnection);
    
    lRec = new SIInsertRec("shiphistorytbl");
    lRec.add("cmdtycode", regCmdtyComposition.getCmdtyCode());
    lRec.add("individualcode", regCmdtyComposition.getIndividualCode());
    lRec.add("shipnumber", "1");
    lRec.add("shipbranch", "999");
    lRec.add("shipcharge", "");
    lRec.add("amount", "0");
    lRec.add("purchaseprice", "0");
    lRec.add("processingexpence", "0");
    lRec.add("shiptype", "4");
    lRec.add("comment", "商品構成マスタ新規作成");
    lRec.add("delflg", "1");
    lRec.add("initdatetime", lDateTime.getFullDate());
    lRec.execute(lConnection);
    
    String season = lDateTime.getYearStr() + "01";// 2007年以降の場合、1月の締処理をするまでは作らない（1月の締処理時に新規作成する）
    if (lDateTime.getYearStr().equals("2006") || SIDBUtil.hasData(lConnection, "SELECT * FROM inventorytbl WHERE season>='" + season + "'")) {
      SINameValue branch = new SINameValue();
      Collection branchColl = SIDBUtil.getCollection(lConnection, "SELECT branchcode,branchcode FROM branchtbl");
      Iterator branchIta = branchColl.iterator();
      while (branchIta.hasNext()) {
        branch = (SINameValue) branchIta.next();
        
        // 締処理
        lRec = new SIInsertRec("stockrecordtbl");
        lRec.add("cmdtycode", regCmdtyComposition.getCmdtyCode());
        lRec.add("individualcode", regCmdtyComposition.getIndividualCode());
        lRec.add("branchcode", branch.getName());
        lRec.add("stockyear", lDateTime.getYearStr());
        lRec.execute(lConnection);
      }
    }
  }
}