/**
 * 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.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;

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

import jp.co.sint.basic.SICmdtyComposition;
import jp.co.sint.basic.SIKeepDetail;
import jp.co.sint.basic.SIKeepHeader;
import jp.co.sint.basic.SILogin;
import jp.co.sint.basic.SIStoreStock;
import jp.co.sint.beans.mallmgr.UICmdtyKeep;
import jp.co.sint.beans.mallmgr.UICmdtyStock;
import jp.co.sint.beans.mallmgr.UIRegNewKeep;
import jp.co.sint.beans.mallmgr.UIRegUpdateEstimate;
import jp.co.sint.beans.mallmgr.UIStoreStockListCond;
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.servlet.SIServlet;
import jp.co.sint.tools.SICheckUtil;
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.commons.lang.StringUtils;
import org.apache.log4j.Category;

/**
 * @version $Id: SIFreeKeepSrv.java,v 1.0 2008/03/25 Exp $
 * @author Naotaka Ohsugi
 * @Description: キープ情報登録、変更を行うServlet
 * @History
 * Author          Date        Reason
 * ==============  ==========  ===========================
 * Naotaka Ohsugi  2008/03/25  Original
 */
public class SIFreeKeepSrv extends SIServlet {
  // ログ用のインスタンスの生成
  private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME);

  /**
   * <b>doUpdate</b> HTTP リクエストの処理
   * 
   * @param なし
   * @return なし
   * @throws ServletException
   * @throws IOException
   */
  public void doUpdate(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    SILogin manLogin = SIHTMLUtil.getLogin(request);
    if(!manLogin.isLogin()){
      forwardKey(request, response, "webshop.jsp.manager.login");
      return;
    }
    
    HttpSession session = request.getSession(true); // セッションの取得
    SIDatabaseConnection databaseConnection = new SIDatabaseConnection(); // DBへのコネクションの作成
    SIURLParameter urlParam = new SIURLParameter(request);
    
    try{
      Connection conn = databaseConnection.getConnection();
      String actionName = this.getActionName(urlParam); // 画面からのアクション
      
      if(actionName.equals(SIConfig.SIACTION_LIST)){
        UIStoreStockListCond storeStock = new UIStoreStockListCond();
        storeStock.init(request, urlParam);
        storeStock.validate(request);
        session.setAttribute(SIConfig.SISESSION_MAN_ESTIMATE_STORESTOCK, storeStock);
        forwardKey(request, response, "webshop.jsp.manager.estimate.freekeep");
      }else if (actionName.equals("keep")){
        Collection oldColl = (Collection) session.getAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEKEEP_LIST);
        if(oldColl == null) oldColl = new ArrayList();
        
        UIRegNewKeep newKeep = new UIRegNewKeep();
        newKeep.initKeep(request, urlParam);
        if(newKeep.validateJan(request, conn) && newKeep.validateFree(request,conn,true)){
          SIKeepDetail keepDetail = newKeep.getKeepDetail();
          keepDetail = complement(conn, keepDetail, newKeep);
          keepDetail.setSetDetailFlg(SIConfig.SET_DETAIL_FLG_NORMAL); // 強制的に通常商品を設定 // EDBTG003-00 elecs-matsushima add
          
          SIKeepDetail keepDetail2;
          Collection newColl = new ArrayList();
          Iterator keepIte = oldColl.iterator();
          
          while(keepIte.hasNext()){
            keepDetail2 = (SIKeepDetail) keepIte.next();
            // EDBTG003-00 mng-paku mod start
//            if (keepDetail.getIndividualCode().equals(keepDetail2.getIndividualCode())
//                && keepDetail.getBranchCode().equals(keepDetail2.getBranchCode())) {
            if (keepDetail.getCmdtyCode().equals(keepDetail2.getCmdtyCode())
                && keepDetail.getIndividualCode().equals(keepDetail2.getIndividualCode())
                && keepDetail.getBranchCode().equals(keepDetail2.getBranchCode())
                && keepDetail.getSetCode().equals(keepDetail2.getSetCode())
                && keepDetail.getSetDetailCode().equals(keepDetail2.getSetDetailCode())) {
              // EDBTG003-00 mng-paku mod end
              keepDetail.setAmount(SIUtil.add(keepDetail.getAmount(), keepDetail2.getAmount()));
            } else {
              newColl.add(keepDetail2);
            }
          }
          newColl.add(keepDetail);
          session.setAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEKEEP_LIST, newColl);
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.insert"));
          forwardKey(request, response, "webshop.jsp.manager.estimate.freekeep");
        }else{
          forwardKey(request, response, "webshop.jsp.manager.estimate.freekeep");
        }
      }else if(actionName.equals(SIConfig.SIACTION_UPDATE)){
        Collection oldColl = (Collection) session.getAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEKEEP_LIST);
        if(oldColl == null) oldColl = new ArrayList();
        
        UIRegNewKeep newKeep = new UIRegNewKeep();
        newKeep.initUpdate(request, urlParam);
        if(newKeep.validateFree(request,conn,false)){
          SIKeepDetail keepDetail = newKeep.getKeepDetail();
          keepDetail = complementUpdate(conn, keepDetail);
          SIKeepDetail keepDetail2;
          Collection newColl = new ArrayList();
          Iterator keepIte = oldColl.iterator();
          
          while(keepIte.hasNext()){
            keepDetail2 = (SIKeepDetail) keepIte.next();
            // EDBTG003-00 mng-paku mod start
//            if (keepDetail.getIndividualCode().equals(keepDetail2.getIndividualCode())
//                && keepDetail.getBranchCode().equals(keepDetail2.getBranchCode())) {
            // セット品情報チェック
            if (SIUtil.isNotNull(keepDetail2.getSetDetailFlg()) && !SIConfig.SET_DETAIL_FLG_NORMAL.equals(keepDetail2.getSetDetailFlg())) {
              if (!this.isSet(conn, keepDetail2)) {
                continue;
              }
            }
            if (keepDetail.getCmdtyCode().equals(keepDetail2.getCmdtyCode())
                && keepDetail.getIndividualCode().equals(keepDetail2.getIndividualCode())
                && keepDetail.getBranchCode().equals(keepDetail2.getBranchCode())
                && keepDetail.getSetCode().equals(keepDetail2.getSetCode())
                && keepDetail.getSetDetailCode().equals(keepDetail2.getSetDetailCode())) {
            // EDBTG003-00 mng-paku mod end
              if(!keepDetail.getAmount().equals("0")) {
                keepDetail2.setAmount(keepDetail.getAmount());
                newColl.add(keepDetail2);
              }
            } else {
              newColl.add(keepDetail2);
            }
          }
          // EDBTG003-00 mng-paku add start
          Collection newSetDataColl = (Collection) session.getAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEKEEP_SET_LIST);
          Collection newColl2 = new ArrayList();
          keepIte = newColl.iterator();
          while(keepIte.hasNext()){
            keepDetail2 = (SIKeepDetail) keepIte.next();
            if (SIConfig.SET_DETAIL_FLG_APPENDED.equals(keepDetail2.getSetDetailFlg())) {
              boolean existInOldColl = false;
              if (keepDetail.getCmdtyCode().equals(keepDetail2.getCmdtyCode())
                  && keepDetail.getIndividualCode().equals(keepDetail2.getIndividualCode())
                  && keepDetail.getBranchCode().equals(keepDetail2.getBranchCode())
                  && keepDetail.getSetCode().equals(keepDetail2.getSetCode())
                  && keepDetail.getSetDetailCode().equals(keepDetail2.getSetDetailCode())) {
                existInOldColl = true;
              }
              // 添付品の添付数計算
              //int addSetAmount = this.getSetAmount(conn, newColl, keepDetail2, false);
              int addSetAmount = this.getSetAmount(conn, newColl, keepDetail2, false, newSetDataColl);
              if (!existInOldColl && addSetAmount > 0) {
                keepDetail2.setAmount(String.valueOf(addSetAmount));
                newColl2.add(keepDetail2);
              }
            } else {
              newColl2.add(keepDetail2);
            }
          }
          session.setAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEKEEP_LIST, newColl2);
          // EDBTG003-00 mng-paku add end
          // EDBTG003-00 elecs-matsushima mod start
          // セット品調整
          Collection setDataColl = (Collection) session.getAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEKEEP_SET_LIST);
          Collection setDataNewColl = new ArrayList();
          if (setDataColl != null) {
            for (Iterator setDataCollIte = setDataColl.iterator(); setDataCollIte.hasNext();) {
              HashMap setMap = (HashMap) setDataCollIte.next();
              boolean addFlg = false;
              
              for (Iterator setMapIte = setMap.keySet().iterator();setMapIte.hasNext(); ){
                String setCode = (String) setMapIte.next();
                for (Iterator newColl2Ite = newColl2.iterator(); newColl2Ite.hasNext();) {
                  SIKeepDetail chkKeepDatail = (SIKeepDetail) newColl2Ite.next();
                  if (setCode.equals(chkKeepDatail.getSetCode())) {
                    setDataNewColl.add(setMap);
                    addFlg = true;
                    break;
                  }
                }
                if (addFlg) break;
              }
            }
          }
//          session.setAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEKEEP_LIST, newColl);
          session.setAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEKEEP_SET_LIST, setDataNewColl);
          // EDBTG003-00 elecs-matsushima mod end
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.modify"));
          forwardKey(request, response, "webshop.jsp.manager.estimate.freekeep");
        }else{
          forwardKey(request, response, "webshop.jsp.manager.estimate.freekeep");
        }
      }else if(actionName.equals(SIConfig.SIACTION_RESET)){
        // EDBTG003-00 mng-paku add start
        String sumOfSetDiscountStr = (String)request.getParameter("sumOfSetDiscount");
        if (SIUtil.isNotNull(sumOfSetDiscountStr) && StringUtils.isNumeric(sumOfSetDiscountStr)) {
          session.setAttribute(SIConfig.SISESSION_MAN_COMPOSITIONOPT_SET_DISCOUNT, "-" + sumOfSetDiscountStr);
        }
        // EDBTG003-00 mng-paku add end
        session.removeAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEKEEP_HEADER);
        session.removeAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEKEEP_LIST);
        session.removeAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEKEEP_SET_LIST);// EDBTG003-00 add
        request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.delete"));
        forwardKey(request, response, "webshop.jsp.manager.estimate.freekeep");
      }else if(actionName.equals(SIConfig.SIACTION_NEXT)){
        UIRegNewKeep newKeep = (UIRegNewKeep) session.getAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEKEEP_HEADER);
        if(newKeep == null){
          newKeep = new UIRegNewKeep();
          newKeep.setBackURL("webshop.jsp.manager.keep.detail");//キープ明細からきた場合
        }
        newKeep.initKeep(request, urlParam);
        session.setAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEKEEP_HEADER, newKeep);
        session.removeAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEESTIMATE);
        forwardKey(request, response, "webshop.jsp.manager.estimate.freeestimate");
      }else if (actionName.equals(SIConfig.SIACTION_BACK)){
        UIStoreStockListCond storeStock = (UIStoreStockListCond)session.getAttribute(SIConfig.SISESSION_MAN_ESTIMATE_STORESTOCK);
        if(storeStock == null) {
          storeStock = new UIStoreStockListCond();
        } else {
          storeStock.setPageNumberTxt((String) urlParam.getParam("pageNumberTxt"));
          session.setAttribute(SIConfig.SISESSION_MAN_ESTIMATE_STORESTOCK,storeStock);
        }
        UIRegNewKeep newkeep = (UIRegNewKeep) session.getAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEKEEP_HEADER);
        if (newkeep != null && newkeep.getBackURL().equals("webshop.jsp.manager.estimate.freeestimateupdate")) {
          // EDBTG003-00 nagayoshi add start
          // セット値引金額設定再設定処理
          String setDiscountPrice = (String)urlParam.getParam("sumOfSetDiscount");
          UIRegUpdateEstimate estimateFull = (UIRegUpdateEstimate)session.getAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEESTIMATE_UPDATE);
          estimateFull.getEstimate().setSumOfSetDiscount(setDiscountPrice);
          // EDBTG003-00 nagayoshi add end
          forwardKey(request,response,newkeep.getBackURL());
        } else {
          forwardKey(request,response,"webshop.jsp.manager.estimate.storestock");
        }
        // EDBTG003-00 mng-paku add start
      } else if (SIConfig.SIACTION_COMPOSITIONOPT.equals(actionName)) {
        // 最大セット番号取得
        int maxSetCode = 1;
        Hashtable setListHash = new Hashtable();
        Collection newColl = new ArrayList();
        Collection oldColl = (Collection) session.getAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEKEEP_LIST);
        if (oldColl == null || oldColl.size() <= 0) session.removeAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEKEEP_SET_LIST);
        if (oldColl == null)
          oldColl = new ArrayList();
        Collection tmpColl = new ArrayList();
        Iterator iteOldColl = oldColl.iterator();
        while (iteOldColl.hasNext()) {
          SIKeepDetail keepDetailOld = (SIKeepDetail) iteOldColl.next();
          if (SIUtil.isNotNull(keepDetailOld.getSetCode()) && StringUtils.isNumeric(keepDetailOld.getSetCode())) {
            int setCode = Integer.parseInt(keepDetailOld.getSetCode());
            if (setCode >= maxSetCode) {
              maxSetCode = setCode + 1;
            }
          }
          tmpColl.add(keepDetailOld);
          
          // セット重複チェック用
          if (SIConfig.SET_DETAIL_FLG_COMPOSITION.equals(keepDetailOld.getSetDetailFlg()) || SIConfig.SET_DETAIL_FLG_OPTION.equals(keepDetailOld.getSetDetailFlg())) {
            Collection setList = new ArrayList();
            if (setListHash.containsKey(keepDetailOld.getSetCode())) {
              setList = (Collection) setListHash.get(keepDetailOld.getSetCode());
            }
            setList.add(keepDetailOld);
            setListHash.put(keepDetailOld.getSetCode(), setList);
          }
        }
        // 重複セット番号取得
        String existSetCode = this.getExistSetCode(session, setListHash);
        if (existSetCode != null && StringUtils.isNumeric(existSetCode)) {
          maxSetCode = Integer.parseInt(existSetCode);
        }
        
        // EDBTG003-00 elecs-matsushima add start
        //セット情報を設定する。
        String setAmountTmp = "";
        SICmdtyComposition addCompositionData = (SICmdtyComposition) session.getAttribute(SIConfig.SISESSION_MAN_COMPOSITIONOPT_DATA);
        Collection setDataColl = (Collection) session.getAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEKEEP_SET_LIST);
        Collection newSetDataColl = new ArrayList();
        boolean existCompositionFlg = false;
        if (addCompositionData != null) {
          if (setDataColl == null) setDataColl = new ArrayList();
          for (Iterator iteSetDataColl = setDataColl.iterator(); iteSetDataColl.hasNext(); ){
            HashMap setDataMap = (HashMap) iteSetDataColl.next();
            if (setDataMap == null) setDataMap = new HashMap();
            
            SICmdtyComposition compositionData = (SICmdtyComposition) setDataMap.get(existSetCode);
            if (compositionData != null && !existCompositionFlg){
              compositionData.setAmount(SIUtil.add_LL(compositionData.getAmount(), addCompositionData.getAmount()));
              setAmountTmp = compositionData.getAmount();
              setDataMap.put(existSetCode, compositionData);
              existCompositionFlg = true;
            }
            newSetDataColl.add(setDataMap);
          }
          
          if (!existCompositionFlg){
            HashMap setDataMap = new HashMap();
            setDataMap.put(String.valueOf(maxSetCode), addCompositionData);
            newSetDataColl.add(setDataMap);
          }
        }
        session.setAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEKEEP_SET_LIST, newSetDataColl);
        // EDBTG003-00 elecs-matsushima add end
        
        // 構成品
        int detailCode = 0;
        Collection groupColl = (Collection) session.getAttribute(SIConfig.SISESSION_MAN_COMPOSITIONOPT_GROUP_LIST);
        if (groupColl != null) {
          Iterator iteGroupColl = groupColl.iterator();
          while (iteGroupColl.hasNext()) {
            UIRegNewKeep newKeep = new UIRegNewKeep();
            newKeep.initKeep(request, urlParam);
            SIKeepDetail keepDetail = (SIKeepDetail) iteGroupColl.next();
            keepDetail.setSetCode(String.valueOf(maxSetCode));
            
            if (SIUtil.isNotNull(keepDetail.getSetDetailCode()) && Integer.parseInt(keepDetail.getSetDetailCode()) > detailCode){
              detailCode = Integer.parseInt(keepDetail.getSetDetailCode());
            }
            
            boolean existInOldColl = false;
            Iterator iteTmpColl = tmpColl.iterator();
            while (iteTmpColl.hasNext()) {
              SIKeepDetail keepDetailTmp = (SIKeepDetail) iteTmpColl.next();
              if (keepDetail.getSetDetailFlg().equals(keepDetailTmp.getSetDetailFlg())
                  && keepDetail.getIndividualCode().equals(keepDetailTmp.getIndividualCode())
                  && keepDetail.getBranchCode().equals(keepDetailTmp.getBranchCode())
                  && keepDetail.getSetCmdtyCode().equals(keepDetailTmp.getSetCmdtyCode())
                  && keepDetail.getSetIndividualCode().equals(keepDetailTmp.getSetIndividualCode())
                  && keepDetail.getSetCode().equals(keepDetailTmp.getSetCode())
                  && SIUtil.isNotNull(existSetCode)
                  && keepDetail.getSetDetailCode().equals(keepDetailTmp.getSetDetailCode())) {
                keepDetailTmp.setAmount(SIUtil.add(keepDetail.getAmount(), keepDetailTmp.getAmount()));
                if (SIUtil.isNotNull(setAmountTmp)) keepDetailTmp.setSetAmount(setAmountTmp);
                existInOldColl = true;
                break;
              }
            }
            if (!existInOldColl) {
              tmpColl.add(keepDetail);
            }
          }
        }
        
        // 添付品
        // EDBTG003-00 elecs-matsushima mod start
//        Collection appendedColl = (Collection) session.getAttribute(SIConfig.SISESSION_MAN_COMPOSITIONOPT_APPENDED_LIST);
//        if (appendedColl != null) {
//            Iterator iteColl = appendedColl.iterator();
//            while (iteColl.hasNext()) {
//              UIRegNewKeep newKeep = new UIRegNewKeep();
//              newKeep.initKeep(request, urlParam);
//              SIKeepDetail keepDetail = (SIKeepDetail) iteColl.next();
//              keepDetail.setSetCode(String.valueOf(maxSetCode));
//              
//              // 添付品の添付数計算
//              //int addSetAmount = this.getSetAmount(conn, oldColl, keepDetail, existSetCode == null);
//              int addSetAmount = this.getSetAmount(conn, oldColl, keepDetail, false, newSetDataColl);
//              
//              // 添付品の登録または除去
//              boolean existInOldColl = false;
//              Iterator iteTmpColl = tmpColl.iterator();
//              while (iteTmpColl.hasNext()) {
//                SIKeepDetail keepDetailTmp = (SIKeepDetail) iteTmpColl.next();
//                if (keepDetail.getSetDetailFlg().equals(keepDetailTmp.getSetDetailFlg())
//                    && keepDetail.getIndividualCode().equals(keepDetailTmp.getIndividualCode())
//                    && keepDetail.getBranchCode().equals(keepDetailTmp.getBranchCode())
//                    && keepDetail.getSetCmdtyCode().equals(keepDetailTmp.getSetCmdtyCode())
//                    && keepDetail.getSetIndividualCode().equals(keepDetailTmp.getSetIndividualCode())) {
//                  existInOldColl = true;
//                  keepDetailTmp.setAmount(String.valueOf(addSetAmount));
//                  if (addSetAmount == 0) {
//                    tmpColl.remove(keepDetailTmp);
//                  }
//                  break;
//                }
//              }
//              if (!existInOldColl && addSetAmount > 0) {
//                tmpColl.add(keepDetail);
//              }
//            }
//          }
        
        detailCode++;
        HashMap checkAppendedMap = new HashMap();
        Collection appendedColl = getCmdtyCompositionAppended(conn, addCompositionData.getShopCode(), addCompositionData.getCmdtyCode(), addCompositionData.getIndividualCode());
        if (appendedColl != null){
          Iterator iteColl = appendedColl.iterator();
          while (iteColl.hasNext()) {
            UIRegNewKeep newKeep = new UIRegNewKeep();
            newKeep.initKeep(request, urlParam);
            SIKeepDetail keepDetail = (SIKeepDetail) iteColl.next();
            keepDetail.setSetCode(String.valueOf(maxSetCode));
            int addSetAmount = this.getSetAmount(conn, oldColl, keepDetail, false, newSetDataColl);
            
            // 添付品の登録または除去
            boolean existInOldColl = false;
            Iterator iteTmpColl = tmpColl.iterator();
            while (iteTmpColl.hasNext()) {
              SIKeepDetail keepDetailTmp = (SIKeepDetail) iteTmpColl.next();
              if (keepDetail.getSetDetailFlg().equals(keepDetailTmp.getSetDetailFlg())
                  && keepDetail.getIndividualCode().equals(keepDetailTmp.getIndividualCode())
                  && keepDetail.getSetCmdtyCode().equals(keepDetailTmp.getSetCmdtyCode())
                  && keepDetail.getSetIndividualCode().equals(keepDetailTmp.getSetIndividualCode())
                  && !checkAppendedMap.containsKey(keepDetail.getIndividualCode())) {
                existInOldColl = true;
                keepDetailTmp.setAmount(String.valueOf(addSetAmount));
                if (addSetAmount == 0) {
                  tmpColl.remove(keepDetailTmp);
                } else {
                  //keepDetailTmp.setSetDetailCode(String.valueOf(detailCode++));
                  if (SIUtil.isNotNull(setAmountTmp)) keepDetailTmp.setSetAmount(setAmountTmp);
                  if (SIUtil.isNotNull(existSetCode) && existSetCode.equals(keepDetailTmp.getSetCode())) {
                    detailCode = Integer.parseInt(keepDetailTmp.getSetDetailCode());
                    detailCode++;
                  }
                }
                checkAppendedMap.put(keepDetail.getIndividualCode(), String.valueOf(maxSetCode));
                break;
              }
            }
            if (!existInOldColl && addSetAmount > 0) {
              keepDetail.setAmount(String.valueOf(addSetAmount));
              keepDetail.setSetDiscount(addCompositionData.getSetDiscount());
              keepDetail.setSetAmount(addCompositionData.getAmount());
              keepDetail.setSetDetailCode(String.valueOf(detailCode++));
              keepDetail.setSetCmdtyName(addCompositionData.getSetCmdtyName());
              tmpColl.add(keepDetail);
            }
          }
        }
        // EDBTG003-00 elecs-matsushima mod end
        
        // オプション
        Collection optionColl = (Collection) session.getAttribute(SIConfig.SISESSION_MAN_COMPOSITIONOPT_OPTION_LIST);
        if (optionColl != null) {
          Iterator iteColl = optionColl.iterator();
          while (iteColl.hasNext()) {
            UIRegNewKeep newKeep = new UIRegNewKeep();
            newKeep.initKeep(request, urlParam);
            SIKeepDetail keepDetail = (SIKeepDetail) iteColl.next();
            keepDetail.setSetCode(String.valueOf(maxSetCode));
            
            boolean existInOldColl = false;
            Iterator iteTmpColl = tmpColl.iterator();
            while (iteTmpColl.hasNext()) {
              SIKeepDetail keepDetailTmp = (SIKeepDetail) iteTmpColl.next();
              if (keepDetail.getSetDetailFlg().equals(keepDetailTmp.getSetDetailFlg())
                  && keepDetail.getIndividualCode().equals(keepDetailTmp.getIndividualCode())
                  && keepDetail.getBranchCode().equals(keepDetailTmp.getBranchCode())
                  && keepDetail.getSetCmdtyCode().equals(keepDetailTmp.getSetCmdtyCode())
                  && keepDetail.getSetIndividualCode().equals(keepDetailTmp.getSetIndividualCode())
                  && SIUtil.isNotNull(existSetCode)
                  && keepDetail.getSetCode().equals(keepDetailTmp.getSetCode())) {
                keepDetailTmp.setAmount(SIUtil.add(keepDetail.getAmount(), keepDetailTmp.getAmount()));
                keepDetailTmp.setSetDetailCode(String.valueOf(detailCode++));
                if (SIUtil.isNotNull(setAmountTmp)) keepDetailTmp.setSetAmount(setAmountTmp);
                existInOldColl = true;
                break;
              }
            }
            if (!existInOldColl) {
              keepDetail.setSetDetailCode(String.valueOf(detailCode++));
              tmpColl.add(keepDetail);
            }
          }
        }
        newColl.addAll(tmpColl);
        session.setAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEKEEP_LIST, newColl);
        request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.insert"));
        forwardKey(request, response, "webshop.jsp.manager.estimate.freekeep");
        // EDBTG003-00 mng-paku add end
      }else if(actionName.equals(SIConfig.SIACTION_COPY)){
        String type = (String)urlParam.getParam("typeSelect");
        String code = (String)urlParam.getParam("codeTxt");
        String newNum = this.checkCopy(manLogin, type, code, conn);
        if (SIUtil.isNotNull(newNum)&&this.makeCopyEstimate(manLogin, newNum, type, code, conn)){
          conn.commit();
          session.setAttribute(SIConfig.SISESSION_MAN_NAVIGATER_LIST_NAME, SIConfig.SINAVI_FREE_ESTIMATE);
          session.removeAttribute(SIConfig.SISESSION_MAN_ESTIMATE_FREEESTIMATE_LIST);
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.insert"));
          forwardKey(request, response, "webshop.jsp.manager.estimate.freeestimatelist");
        } else {
          conn.rollback();
          request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.freeword","エラーのため登録に失敗しました"));
          forwardKey(request, response, "webshop.jsp.manager.estimate.freekeep");
        }
      }else{
        forwardKey(request, response, "webshop.jsp.manager.estimate.newkeep");
      }
    }catch(SQLException e){
      e.printStackTrace();
      throw new ServletException();
    }catch(NamingException e){
      e.printStackTrace();
      throw new ServletException();
    }catch (SIDBAccessException e1){
      e1.printStackTrace();
    }finally{
      databaseConnection.close();
    }
  }
  
  private String checkCopy(SILogin manLogin,String type,String code,Connection lConnection){
    String resNum = "";
    
    try{
      if ((type.equals("order")||type.equals("estimate"))&&SICheckUtil.isDigit(code)) {
        String lSql = "SELECT * FROM orderlatestvw WHERE ordercode="+code;
        if (type.equals("estimate")) lSql = "SELECT * FROM estimatetbl WHERE estimatecode="+code;
        if (SIDBUtil.hasData(lConnection, lSql)){
          resNum = SIDBUtil.getFirstData(lConnection, SIDBUtil.getNextVal("freeestimate_seq"));
        }
        if (!SIDBUtil.hasData(lConnection, "SELECT chargecode FROM chargetbl WHERE belongingbranchcode<13 AND chargecode="+SIDBUtil.SQL2Str(manLogin.getUserCode()))) resNum="";
      }
    }catch (Exception e){
      resNum="";
    }
    return resNum;
  }
  
 private boolean makeCopyEstimate(SILogin manLogin,String newNum,String type,String code,Connection lConnection){
   boolean res = false;
   StringBuffer lSql = new StringBuffer();
   try {
     String charge = manLogin.getUserCode();
     String branch = SIDBUtil.getFirstData(lConnection, "SELECT belongingbranchcode FROM chargetbl WHERE chargecode="+SIDBUtil.SQL2Str(manLogin.getUserCode()));
     
     if (type.equals("order")) {
       lSql.append("INSERT INTO freeestimatetbl ");
       lSql.append("SELECT ").append(SIDBUtil.SQL2Str(newNum)).append(",o.custcode,o.custname,o.custpronname");
       lSql.append(",o.email,o.orderaddressee,o.postcode1,o.postcode2,o.address1,o.address2,o.address3,o.companyname");
       lSql.append(",o.tel,o.fax,CASE WHEN o.paymethodname='カード' THEN '店頭カード' ELSE o.paymethodname END");
       lSql.append(",now(),now(),o.paymentflg,o.fee,o.taxrate,o.sumofdiscount,o.sendmailflg,o.status,o.feetaxflg");
       lSql.append(",o.contactmsg,o.memo,o.pointenableflg,o.updatemallshopcode,o.updateusercode,o.job,o.orderroute");
       lSql.append(",").append(SIDBUtil.SQL2Str(branch)).append(",").append(SIDBUtil.SQL2Str(charge));
       lSql.append(",o.storetel,o.sumbypoint,o.discountfee,o.discountdeliveryfee,c.memberlevelcode,o.setdiscount ");
       lSql.append("FROM ordertbl o,custtbl c WHERE o.custcode=c.custcode ");
       lSql.append("AND o.enabledflg=1 AND o.ordercode=").append(SIDBUtil.SQL2Str(code));
       SIDBUtil.execSQL(lConnection, lSql.toString());
       
       lSql = new StringBuffer();
       lSql.append("INSERT INTO freeestimatedeliverytbl ");
       lSql.append("SELECT ").append(SIDBUtil.SQL2Str(newNum)).append(",d.deliverycode,d.deliveryname");
       lSql.append(",d.deliveryaddressee,d.email,d.postcode1,d.postcode2,d.address1,d.address2,d.address3,d.companyname,d.tel,d.fax");
       lSql.append(",d.deliveryfee,d.deliverytaxflg,d.taxrate,d.deliverytypename,d.custcode,d.addresscode,d.mallshopcode,d.deliverytypecode ");
       lSql.append("FROM ordertbl o,orderdeliverytbl d WHERE o.ordercode=d.ordercode AND o.orderbranchcode=d.orderbranchcode ");
       lSql.append("AND o.enabledflg=1 AND o.ordercode=").append(SIDBUtil.SQL2Str(code));
       SIDBUtil.execSQL(lConnection, lSql.toString());
       
       lSql = new StringBuffer();
       lSql.append("INSERT INTO freeestimatedetailtbl ");
       lSql.append("SELECT ").append(SIDBUtil.SQL2Str(newNum)).append(",d.deliverycode,d.detailcode");
       lSql.append(",d.shopcode,d.cmdtycode,d.individualcode,d.shopname,d.cmdtyname,d.amount,d.orgprice,d.price,d.taxflg,d.taxrate");
       lSql.append(",d.cmdtysize,d.deliveryfee,d.deliverydate,d.setdetailflg,d.setcode,d.setcmdtycode,d.setindividualcode,d.setdetailcode ");
       lSql.append("FROM ordertbl o,orderdetailtbl d WHERE o.ordercode=d.ordercode AND o.orderbranchcode=d.orderbranchcode ");
       lSql.append("AND o.enabledflg=1 AND o.ordercode=").append(SIDBUtil.SQL2Str(code));
       SIDBUtil.execSQL(lConnection, lSql.toString());
       
       lSql = new StringBuffer();
       lSql.append("INSERT INTO freeestimatesetcmdtytbl ");
       lSql.append("SELECT ").append(SIDBUtil.SQL2Str(newNum)).append(",c.setcode,c.setcmdtycode");
       lSql.append(",c.setindividualcode,c.cmdtyname,c.setdiscount,c.setamount ");
       lSql.append("FROM ordertbl o,ordersetcmdtytbl c WHERE o.ordercode=c.ordercode AND o.orderbranchcode=c.orderbranchcode ");
       lSql.append("AND o.enabledflg=1 AND o.ordercode=").append(SIDBUtil.SQL2Str(code));
       SIDBUtil.execSQL(lConnection, lSql.toString());
       res=true;
     } else if (type.equals("estimate")) {
       lSql.append("INSERT INTO freeestimatetbl ");
       lSql.append("SELECT ").append(SIDBUtil.SQL2Str(newNum)).append(",custcode,custname,custpronname");
       lSql.append(",email,estimateaddressee,postcode1,postcode2,address1,address2,address3,companyname,tel,fax");
       lSql.append(",paymethodname,now(),now(),paymentflg,fee,taxrate,sumofdiscount,sendmailflg,status,feetaxflg");
       lSql.append(",contactmsg,memo,pointenableflg,updatemallshopcode,updateusercode,businessform,orderroute");
       lSql.append(",").append(SIDBUtil.SQL2Str(branch)).append(",").append(SIDBUtil.SQL2Str(charge));
       lSql.append(",storetel,usepoint,discountfee,discountdeliveryfee,memberlevelcode,setdiscount ");
       lSql.append("FROM estimatetbl WHERE estimatecode=").append(SIDBUtil.SQL2Str(code));
       SIDBUtil.execSQL(lConnection, lSql.toString());
       
       lSql = new StringBuffer();
       lSql.append("INSERT INTO freeestimatedeliverytbl ");
       lSql.append("SELECT ").append(SIDBUtil.SQL2Str(newNum)).append(",deliverycode,deliveryname");
       lSql.append(",deliveryaddressee,email,postcode1,postcode2,address1,address2,address3,companyname,tel,fax");
       lSql.append(",deliveryfee,deliverytaxflg,taxrate,deliverytypename,custcode,addresscode,mallshopcode,deliverytypecode ");
       lSql.append("FROM estimatedeliverytbl WHERE estimatecode=").append(SIDBUtil.SQL2Str(code));
       SIDBUtil.execSQL(lConnection, lSql.toString());
       
       lSql = new StringBuffer();
       lSql.append("INSERT INTO freeestimatedetailtbl ");
       lSql.append("SELECT ").append(SIDBUtil.SQL2Str(newNum)).append(",deliverycode,detailcode");
       lSql.append(",shopcode,cmdtycode,individualcode,shopname,cmdtyname,amount,orgprice,price,taxflg,taxrate");
       lSql.append(",cmdtysize,deliveryfee,deliverydate,setdetailflg,setcode,setcmdtycode,setindividualcode,setdetailcode ");
       lSql.append("FROM estimatedetailtbl WHERE estimatecode=").append(SIDBUtil.SQL2Str(code));
       SIDBUtil.execSQL(lConnection, lSql.toString());
       
       lSql = new StringBuffer();
       lSql.append("INSERT INTO freeestimatesetcmdtytbl ");
       lSql.append("SELECT ").append(SIDBUtil.SQL2Str(newNum)).append(",setcode,setcmdtycode,setindividualcode,cmdtyname,setdiscount,setamount ");
       lSql.append("FROM estimatesetcmdtytbl WHERE estimatecode=").append(SIDBUtil.SQL2Str(code));
       SIDBUtil.execSQL(lConnection, lSql.toString());
       res=true;
     }
   } catch (Exception e) {
     res = false;
   }
   return res;
 }
  
  //EDBTG003-00 elecs-matsushima add start
  public Collection getCmdtyCompositionAppended(Connection lConnection, String shopCode, String cmdtyCode, String individualCode) throws SIDBAccessException {
    Collection rec = new ArrayList();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    // SQL生成
    StringBuffer sqlStr = new StringBuffer();
    sqlStr.append("SELECT a.*,b.amountFlg,b.janCodeIndividual,b.usedNewFlg,b.cmdtyName ");
    sqlStr.append("FROM appendedCmdtyTbl a,cmdtyUnitTbl b ");
    sqlStr.append("WHERE a.shopCode=b.shopCode ");
    sqlStr.append("AND a.detailCmdtyCode = b.cmdtyCode ");
    sqlStr.append("AND a.detailIndividualCode = b.individualCode ");
    sqlStr.append("AND a.shopCode = ").append(SIDBUtil.SQL2Str(shopCode," "));
    sqlStr.append("AND a.cmdtyCode  = ").append(SIDBUtil.SQL2Str(cmdtyCode," "));
    sqlStr.append("AND a.individualCode = ").append(SIDBUtil.SQL2Str(individualCode," "));
    sqlStr.append("ORDER BY a.dispOrder");
    
    try{
      lStatement = lConnection.createStatement();
      lResultSet = lStatement.executeQuery(sqlStr.toString());
      while(lResultSet.next()) {
        SIKeepDetail keepDetail = this.createKeepDetail(lConnection, lResultSet.getString("detailcmdtycode"), lResultSet.getString("detailindividualcode"));
        keepDetail.setSetCmdtyCode(cmdtyCode);
        keepDetail.setSetIndividualCode(individualCode);
        keepDetail.setCmdtyCode(lResultSet.getString("detailcmdtycode"));
        keepDetail.setIndividualCode(lResultSet.getString("detailindividualcode"));
        keepDetail.setUsedNewFlg(lResultSet.getString("usednewflg"));
        keepDetail.setCost("0");
        keepDetail.setPrice("0");
        keepDetail.setOrgPrice("0");
        keepDetail.setSetDetailFlg(SIConfig.SET_DETAIL_FLG_APPENDED);
        keepDetail.setCmdtyName(lResultSet.getString("cmdtyname"));
        rec.add(keepDetail);
      }
    } catch (SQLException e) {
      e.printStackTrace();
      throw new SIDBAccessException(e);
    } finally {
      SIDBUtil.close(lResultSet, lStatement);
    }
    return rec;
  }
  
  public SIKeepDetail createKeepDetail(Connection conn, String cmdtyCode, String individualCode) throws SIDBAccessException {
    SIKeepDetail keepDetail = new SIKeepDetail();
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT a.cmdtyname,a.cmdtysize,a.deliverytypecode,b.bgprice");
    sql.append(",CASE b.amountflg WHEN 0 THEN 1 ELSE 0 END AS cmdtyType ");
    sql.append(",CASE b.amountflg WHEN 0 THEN b.purchaseprice2 ELSE COALESCE(b.purchaseprice,0) END AS purchaseprice ");
    sql.append(",COALESCE(b.processingexpence,0) AS processingexpence ");
    sql.append("FROM cmdtymtbl a,individualtbl b ");
    sql.append("WHERE b.cmdtycode = ").append(SIDBUtil.SQL2Str(cmdtyCode, " "));
    sql.append("AND b.individualcode = ").append(SIDBUtil.SQL2Str(individualCode, " "));
    sql.append("AND b.cmdtycode = a.cmdtycode");
    
    try {
      lStatement = conn.createStatement();
      lResultSet = lStatement.executeQuery(sql.toString());
      while (lResultSet.next()) {
        keepDetail.setSize((String) lResultSet.getString("cmdtysize"));
        keepDetail.setDeliveryType((String) lResultSet.getString("deliverytypecode"));
        keepDetail.setOrgPrice((String) lResultSet.getString("bgprice"));
        keepDetail.setCmdtyType((String) lResultSet.getString("cmdtytype"));
        keepDetail.setPurchasePrice((String) lResultSet.getString("purchaseprice"));
        keepDetail.setProcessingExpence((String) lResultSet.getString("processingexpence"));
        keepDetail.setCost((String) lResultSet.getString("purchaseprice"));
      }
    } catch (Exception ex) {
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lStatement, lResultSet);
    }
    return keepDetail;
  }
  //EDBTG003-00 elecs-matsushima add end
  
  public void destroy(){
  }
  
  public int existKeep(Connection conn, SIKeepDetail keepDetail) throws SIDBAccessException {
    int amount = 0;
    Statement lStatement = null;
    ResultSet lResultSet = null;
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT amount FROM keepdetailtbl ");
    sql.append("WHERE keepnumber = ").append(SIDBUtil.SQL2Str(keepDetail.getKeepNumber()," "));
    sql.append("AND cmdtycode = ").append(SIDBUtil.SQL2Str(keepDetail.getCmdtyCode()," "));
    sql.append("AND individualcode = ").append(SIDBUtil.SQL2Str(keepDetail.getIndividualCode()," "));
    sql.append("AND branchcode = ").append(SIDBUtil.SQL2Str(keepDetail.getBranchCode()));
    try{
      lStatement = conn.createStatement();
      lResultSet = lStatement.executeQuery(sql.toString());
      while(lResultSet.next()){
        amount = lResultSet.getInt("amount");
      }
    }catch(Exception ex){
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lStatement, lResultSet);
    }
    return amount;
  }
  
  public int existKeep(Connection conn,UICmdtyKeep cmdtyKeep,UIRegNewKeep keepHeader) throws SIDBAccessException {
    int amount = 0;
    Statement lStatement = null;
    ResultSet lResultSet = null;
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT amount FROM keepdetailtbl ");
    sql.append("WHERE keepnumber = ").append(SIDBUtil.SQL2Str(keepHeader.getKeepNumber()," "));
    sql.append("AND cmdtycode = ").append(SIDBUtil.SQL2Str(cmdtyKeep.getCmdtyCode()," "));
    sql.append("AND individualcode = ").append(SIDBUtil.SQL2Str(cmdtyKeep.getIndividualCode()," "));
    sql.append("AND branchcode = ").append(SIDBUtil.SQL2Str(cmdtyKeep.getBranchCode()));
    try{
      lStatement = conn.createStatement();
      lResultSet = lStatement.executeQuery(sql.toString());
      while(lResultSet.next()){
        amount = lResultSet.getInt("amount");
      }
    }catch(Exception ex){
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lStatement, lResultSet);
    }
    return amount;
  }
  
  public SIKeepDetail complement(Connection conn, SIKeepDetail keepDetail,UIRegNewKeep newKeep) throws SIDBAccessException {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT a.cmdtyname,a.cmdtysize,a.deliverytypecode,b.bgprice");
    sql.append(",CASE b.amountflg WHEN 0 THEN 1 ELSE 0 END AS cmdtyType ");
    sql.append(",CASE b.amountflg WHEN 0 THEN b.purchaseprice2 ELSE COALESCE(b.purchaseprice,0) END AS purchaseprice ");
    sql.append(",COALESCE(b.processingexpence,0) AS processingexpence ");
    sql.append("FROM cmdtymtbl a,individualtbl b ");
    sql.append("WHERE b.cmdtycode = ").append(SIDBUtil.SQL2Str(keepDetail.getCmdtyCode()," "));
    sql.append("AND b.individualcode = ").append(SIDBUtil.SQL2Str(keepDetail.getIndividualCode()," "));
    sql.append("AND b.cmdtycode = a.cmdtycode");
    
    try{
      lStatement = conn.createStatement();
      lResultSet = lStatement.executeQuery(sql.toString());
      while (lResultSet.next()){
        keepDetail.setCmdtyName((String) lResultSet.getString("cmdtyname"));
        keepDetail.setSize((String) lResultSet.getString("cmdtysize"));
        keepDetail.setDeliveryType((String) lResultSet.getString("deliverytypecode"));
        keepDetail.setOrgPrice((String) lResultSet.getString("bgprice"));
        keepDetail.setPrice((String) lResultSet.getString("bgprice"));
        keepDetail.setCmdtyType((String) lResultSet.getString("cmdtytype"));
        keepDetail.setPurchasePrice((String) lResultSet.getString("purchaseprice"));
        keepDetail.setProcessingExpence((String) lResultSet.getString("processingexpence"));
        if(SIUtil.isVariableCode(keepDetail.getIndividualCode())&&newKeep.getVariableKeepPrice()!=null){
          keepDetail.setPrice(newKeep.getVariableKeepPrice());
        }
      }
    }catch(Exception ex){
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lStatement, lResultSet);
    }
    return keepDetail;
  }
  
  public SIKeepDetail complementUpdate(Connection conn, SIKeepDetail keepDetail) throws SIDBAccessException {
    Statement lStatement = null;
    ResultSet lResultSet = null;
    
    StringBuffer sql = new StringBuffer();
    sql.append("SELECT a.cmdtyname,a.cmdtysize,a.deliverytypecode,b.bgprice");
    sql.append(",CASE b.amountflg WHEN 0 THEN b.purchaseprice2 ELSE COALESCE(b.purchaseprice,0) END AS purchaseprice ");
    sql.append(",COALESCE(b.processingexpence,0) AS processingexpence ");
    sql.append("FROM cmdtymtbl a,individualtbl b ");
    sql.append("WHERE b.cmdtycode = ").append(SIDBUtil.SQL2Str(keepDetail.getCmdtyCode()," "));
    sql.append("AND b.individualcode = ").append(SIDBUtil.SQL2Str(keepDetail.getIndividualCode()," "));
    sql.append("AND b.cmdtycode = a.cmdtycode");
    
    try{
      lStatement = conn.createStatement();
      lResultSet = lStatement.executeQuery(sql.toString());
      while (lResultSet.next()){
        keepDetail.setCmdtyName((String) lResultSet.getString("cmdtyname"));
        keepDetail.setSize((String) lResultSet.getString("cmdtysize"));
        keepDetail.setDeliveryType((String) lResultSet.getString("deliverytypecode"));
        keepDetail.setPrice((String) lResultSet.getString("bgprice"));
        keepDetail.setPurchasePrice((String) lResultSet.getString("purchaseprice"));
        keepDetail.setProcessingExpence((String) lResultSet.getString("processingexpence"));
      }
    }catch(Exception ex){
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lStatement, lResultSet);
    }
    return keepDetail;
  }
  
  public void insertKeep(Connection conn, SIKeepDetail keepDetail) throws SIDuplicateKeyException, SIDBAccessException {
    if(keepDetail.getCmdtyType().equals("0")){//在庫管理するときのみ行ロックをかける
      try {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT * FROM storestocktbl ");
        sql.append("WHERE shopcode='0' ");
        sql.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(keepDetail.getCmdtyCode()," "));
        sql.append("AND individualcode=").append(SIDBUtil.SQL2Str(keepDetail.getIndividualCode()," "));
        sql.append("AND branchcode=").append(SIDBUtil.SQL2Str(keepDetail.getBranchCode()," "));
        sql.append("FOR UPDATE");
        Statement rsStatement = conn.createStatement();
        rsStatement.execute(sql.toString());
        if (rsStatement!=null)rsStatement.close();
      } catch (Exception ex) {
        throw new SIDBAccessException(ex);
      }
    }
    SISpcType lSpcType = new SISpcType();
    SIInsertRec lRec = new SIInsertRec("keepDetailtbl");
    lRec.add("keepnumber", keepDetail.getKeepNumber());
    lRec.add("cmdtycode", keepDetail.getCmdtyCode());
    lRec.add("individualcode", keepDetail.getIndividualCode());
    lRec.add("cmdtytype", keepDetail.getCmdtyType());
    lRec.add("cmdtyname", keepDetail.getCmdtyName());
    lRec.add("price", keepDetail.getPrice());
    lRec.add("cost", keepDetail.getCost());
    lRec.add("deliverytype", keepDetail.getDeliveryType());
    lRec.add("size", keepDetail.getSize());
    lRec.add("amount", keepDetail.getAmount());
    lRec.add("branchcode", keepDetail.getBranchCode());
    lRec.add("purchaseprice", keepDetail.getPurchasePrice());
    lRec.add("processingexpence", keepDetail.getProcessingExpence());
    lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
    lRec.add("InitDateTime", lSpcType);// 登録日時
    lRec.execute(conn);
    if(keepDetail.getCmdtyType().equals("0")){//在庫管理するときのみ在庫数量を更新する
      try{
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE storestocktbl SET amount=amount-" + keepDetail.getAmount());
        sql.append(" WHERE shopcode='0' ");
        sql.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(keepDetail.getCmdtyCode()," "));
        sql.append("AND individualcode=").append(SIDBUtil.SQL2Str(keepDetail.getIndividualCode()," "));
        sql.append("AND branchcode=").append(SIDBUtil.SQL2Str(keepDetail.getBranchCode()," "));
        Statement rsStatement2 = conn.createStatement();
        rsStatement2.execute(sql.toString());
        if (rsStatement2!=null)rsStatement2.close();
      }catch(Exception ex){
        throw new SIDBAccessException(ex);
      }
    }
  }
  
  public void insertKeep(Connection conn,UICmdtyKeep cmdtyKeep,UIRegNewKeep keepHeader) throws SIDuplicateKeyException, SIDBAccessException {
    SISpcType lSpcType = new SISpcType();
    SIInsertRec lRec = new SIInsertRec("keepDetailtbl");
    lRec = new SIInsertRec("keepDetailtbl");
    lRec.add("keepnumber", keepHeader.getKeepNumber());
    lRec.add("cmdtycode", cmdtyKeep.getCmdtyCode());
    lRec.add("individualcode", cmdtyKeep.getIndividualCode());
    lRec.add("cmdtytype", "1");//商品種別: 1:フリー商品
    lRec.add("cmdtyname", cmdtyKeep.getCmdtyName());
    lRec.add("price", cmdtyKeep.getBgPrice());//単価
    lRec.add("cost", cmdtyKeep.getPurchasePrice2());
    lRec.add("deliverytype", cmdtyKeep.getDeliveryTypeCode());//送料種別
    lRec.add("size", cmdtyKeep.getCmdtySize());
    lRec.add("amount", cmdtyKeep.getAmount());
    lRec.add("branchcode", cmdtyKeep.getBranchCode());//在庫管理しない
    lRec.add("purchasePrice", cmdtyKeep.getPurchasePrice2());//仕入価格
    lRec.add("processingexpence", "0");
    lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
    lRec.add("InitDateTime", lSpcType);// 登録日時
    lRec.execute(conn);
  }
  
  public void addKeep(Connection conn, SIKeepDetail keepDetail) throws SIDuplicateKeyException, SIDBAccessException {
    if(keepDetail.getCmdtyType().equals("0")){//在庫管理するときのみ行ロックをかける
      try{
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT * FROM storestocktbl ");
        sql.append("WHERE shopcode='0' ");
        sql.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(keepDetail.getCmdtyCode()," "));
        sql.append("AND individualcode=").append(SIDBUtil.SQL2Str(keepDetail.getIndividualCode()," "));
        sql.append("AND branchcode=").append(SIDBUtil.SQL2Str(keepDetail.getBranchCode()," "));
        sql.append("FOR UPDATE");
        Statement rsStatement = conn.createStatement();
        rsStatement.execute(sql.toString());
        if (rsStatement!=null)rsStatement.close();
      }catch(Exception ex){
        throw new SIDBAccessException(ex);
      }
    }
    
    try{
      SISpcType lSpc = new SISpcType();
      SISpcType lSpc2 = new SISpcType();
      lSpc = new SISpcType("amount+"+keepDetail.getAmount());
      lSpc2 = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
      SIModifyRec lRec = new SIModifyRec("keepdetailtbl");
      lRec.addCondition("cmdtycode",keepDetail.getCmdtyCode());
      lRec.addCondition("individualcode",keepDetail.getIndividualCode());
      lRec.addCondition("branchcode",keepDetail.getBranchCode());
      lRec.addCondition("keepnumber",keepDetail.getKeepNumber());
      lRec.add("amount",lSpc);
      lRec.add("initdatetime",lSpc2);
      lRec.execute(conn);
    }catch (Exception ex){
      throw new SIDBAccessException(ex);
    }
    
    if(keepDetail.getCmdtyType().equals("0")){//在庫管理するときのみ在庫数量を更新する
      try{
        SISpcType lSpc = new SISpcType();
        SISpcType lSpc2 = new SISpcType();
        lSpc = new SISpcType("amount-"+keepDetail.getAmount());
        lSpc2 = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
        SIModifyRec lRec = new SIModifyRec("storestocktbl");
        lRec.addCondition("shopcode","0");
        lRec.addCondition("cmdtycode",keepDetail.getCmdtyCode());
        lRec.addCondition("individualcode",keepDetail.getIndividualCode());
        lRec.addCondition("branchcode",keepDetail.getBranchCode());
        lRec.add("amount",lSpc);
        lRec.add("initdatetime",lSpc2);
        lRec.execute(conn);
      }catch(Exception ex){
        throw new SIDBAccessException(ex);
      }
    }
  }
  
  public void addKeep(Connection conn, UICmdtyKeep cmdtyKeep,UIRegNewKeep keepHeader) throws SIDuplicateKeyException, SIDBAccessException {
    try{
      SISpcType lSpc = new SISpcType();
      SISpcType lSpc2 = new SISpcType();
      lSpc = new SISpcType("amount+"+cmdtyKeep.getAmount());
      lSpc2 = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
      SIModifyRec lRec = new SIModifyRec("keepdetailtbl");
      lRec.addCondition("cmdtycode",cmdtyKeep.getCmdtyCode());
      lRec.addCondition("individualcode",cmdtyKeep.getIndividualCode());
      lRec.addCondition("branchcode",cmdtyKeep.getBranchCode());
      lRec.addCondition("keepnumber",keepHeader.getKeepNumber());
      lRec.add("amount",lSpc);
      lRec.add("initdatetime",lSpc2);
      lRec.execute(conn);
    }catch (Exception ex){
      throw new SIDBAccessException(ex);
    }
  }
  
  public void updateKeep(Connection conn, SIKeepDetail keepDetail, int amount) throws SIDuplicateKeyException, SIDBAccessException {
    if(keepDetail.getCmdtyType().equals("0")){//在庫管理するときのみ行ロックをかける
      try{
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT * FROM storestocktbl ");
        sql.append("WHERE shopcode='0' ");
        sql.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(keepDetail.getCmdtyCode()," "));
        sql.append("AND individualcode=").append(SIDBUtil.SQL2Str(keepDetail.getIndividualCode()," "));
        sql.append("AND branchcode=").append(SIDBUtil.SQL2Str(keepDetail.getBranchCode()," "));
        sql.append("FOR UPDATE");
        Statement rsStatement = conn.createStatement();
        rsStatement.execute(sql.toString());
        if (rsStatement!=null)rsStatement.close();
      }catch(Exception ex){
        throw new SIDBAccessException(ex);
      }
    }
    
    String sign = "";
    String diff = "";
    
    if(amount > Integer.parseInt(keepDetail.getAmount())){
      diff = Integer.toString(amount - Integer.parseInt(keepDetail.getAmount()));
      sign = "+";
    }else if(amount < Integer.parseInt(keepDetail.getAmount())){
      diff = Integer.toString(Integer.parseInt(keepDetail.getAmount()) - amount);
      sign = "-";
    }
    if(keepDetail.getAmount().equals("0")){
      try{
        SIDeleteRec lRec = new SIDeleteRec("keepdetailtbl");
        lRec.addCondition("cmdtycode",keepDetail.getCmdtyCode());
        lRec.addCondition("individualcode",keepDetail.getIndividualCode());
        lRec.addCondition("branchcode",keepDetail.getBranchCode());
        lRec.addCondition("keepnumber",keepDetail.getKeepNumber());
        lRec.execute(conn);
      }catch(Exception ex){
        throw new SIDBAccessException(ex);
      }
    }else{
      try{
        SISpcType lSpc2 = new SISpcType();
        lSpc2 = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
        SIModifyRec lRec = new SIModifyRec("keepdetailtbl");
        lRec.addCondition("cmdtycode",keepDetail.getCmdtyCode());
        lRec.addCondition("individualcode",keepDetail.getIndividualCode());
        lRec.addCondition("branchcode",keepDetail.getBranchCode());
        lRec.addCondition("keepnumber",keepDetail.getKeepNumber());
        lRec.add("amount",keepDetail.getAmount());
        lRec.add("initdatetime",lSpc2);
        lRec.execute(conn);
      }catch (Exception ex){
        throw new SIDBAccessException(ex);
      }
    }
    
    if(keepDetail.getCmdtyType().equals("0")){//在庫管理するときのみ在庫数量を更新する
      try{
        SISpcType lSpc = new SISpcType();
        lSpc = new SISpcType("amount"+sign+diff);
        SIModifyRec lRec = new SIModifyRec("storestocktbl");
        lRec.addCondition("shopcode","0");
        lRec.addCondition("cmdtycode",keepDetail.getCmdtyCode());
        lRec.addCondition("individualcode",keepDetail.getIndividualCode());
        lRec.addCondition("branchcode",keepDetail.getBranchCode());
        lRec.add("amount",lSpc);
        lRec.execute(conn);
      }catch (Exception ex){
        throw new SIDBAccessException(ex);
      }
    }
  }
  
  /*
  public boolean checkAmount(Connection conn, SIKeepDetail keepDetail) throws SIDBAccessException {
    StringBuffer sql = new StringBuffer();
    String amountFlg = "";
    Statement lStatement = null;
    ResultSet lResultSet = null;
    sql.append("SELECT amountflg FROM individualtbl ");
    sql.append("WHERE cmdtycode=").append(SIDBUtil.SQL2Str(keepDetail.getCmdtyCode()," "));
    sql.append("AND individualcode=").append(SIDBUtil.SQL2Str(keepDetail.getIndividualCode()));
    try {
      lStatement = conn.createStatement();
      lResultSet = lStatement.executeQuery(sql.toString());
      while (lResultSet.next()) {
        amountFlg = lResultSet.getString("amountflg");
      }
    }catch (Exception ex){
      throw new SIDBAccessException(ex);
    }
    if(amountFlg.equals("0")){
      return true;
    }else{
      StringBuffer sql2 = new StringBuffer();
      int amount = 0;
      Statement lStatement2 = null;
      ResultSet lResultSet2 = null;
      sql2.append("SELECT amount FROM storestocktbl ");
      sql2.append("WHERE shopcode='0' ");
      sql2.append("AND cmdtycode=").append(SIDBUtil.SQL2Str(keepDetail.getCmdtyCode()," "));
      sql2.append("AND individualcode=").append(SIDBUtil.SQL2Str(keepDetail.getIndividualCode()," "));
      sql2.append("AND branchcode=").append(SIDBUtil.SQL2Str(keepDetail.getBranchCode()));
      try{
        lStatement2 = conn.createStatement();
        lResultSet2 = lStatement2.executeQuery(sql2.toString());
        while(lResultSet2.next()){
          amount = lResultSet2.getInt("amount");
        }
      }catch(Exception ex){
        throw new SIDBAccessException(ex);
      }
      if(amount >= Integer.parseInt(keepDetail.getAmount())){
        return true;
      }else{
        return false;
      }
    }
  }
  */
  
  public String insertHeader(Connection conn, SIKeepHeader keepHeader) throws SIDuplicateKeyException, SIDBAccessException {
    SISpcType lSpcType = new SISpcType();
    
    String keepNumber = SIDBUtil.getFirstData(conn, SIDBUtil.getNextVal("keepnumber_seq"));
    keepHeader.setKeepNumber(keepNumber);
    SIInsertRec lRec = new SIInsertRec("keeptbl");
    lRec.add("keepnumber", keepHeader.getKeepNumber());
    lRec.add("status", keepHeader.getStatus());
    lRec.add("branchcode", keepHeader.getBranchCode());
    lRec.add("chargecode", keepHeader.getChargeCode());
    lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
    lRec.add("keepdate", lSpcType);// 登録日時
    lRec.execute(conn);
    return keepNumber;
  }
  
  public void setSoldOutFlg(Connection conn, SIKeepDetail keepDetail) throws SIDBAccessException {
    StringBuffer sql = new StringBuffer();
    String amountFlg = "";
    Statement lStatement = null;
    ResultSet lResultSet = null;
    sql.append("SELECT amountflg FROM individualtbl ");
    sql.append("WHERE cmdtycode=").append(SIDBUtil.SQL2Str(keepDetail.getCmdtyCode()," "));
    sql.append("AND individualcode=").append(SIDBUtil.SQL2Str(keepDetail.getIndividualCode()));
    try{
      lStatement = conn.createStatement();
      lResultSet = lStatement.executeQuery(sql.toString());
      while (lResultSet.next()){
        amountFlg = lResultSet.getString("amountflg");
      }
    }catch(Exception ex){
      throw new SIDBAccessException(ex);
    } finally {
      SIDBUtil.close(lStatement, lResultSet);
    }
    if(!amountFlg.equals("0")){
      StringBuffer sql2 = new StringBuffer();
      int amount = 0;
      Statement lStatement2 = null;
      ResultSet lResultSet2 = null;
      sql2.append("SELECT amount from totalstockvw ");
      sql2.append("WHERE cmdtycode=").append(SIDBUtil.SQL2Str(keepDetail.getCmdtyCode()," "));
      sql2.append("AND individualcode=").append(SIDBUtil.SQL2Str(keepDetail.getIndividualCode()));
      try{
        lStatement2 = conn.createStatement();
        lResultSet2 = lStatement2.executeQuery(sql2.toString());
        while(lResultSet2.next()){
          amount = lResultSet2.getInt("amount");
        }
        if(amount == 0){//在庫0になったら売切日を設定する
          SISpcType lSpcType = new SISpcType();
          lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
          SIModifyRec mRec = new SIModifyRec("individualtbl");
          mRec.addCondition("cmdtycode", keepDetail.getCmdtyCode());
          mRec.addCondition("individualcode", keepDetail.getIndividualCode());
          mRec.add("soldoutdate",lSpcType);
          mRec.execute(conn);
        }
      }catch(Exception ex){
        throw new SIDBAccessException(ex);
      } finally {
        SIDBUtil.close(lStatement2, lResultSet2);
      }
    }
  }
  
  /**
   * <b>insertTableData</b>
   * データベースにレコードを作成します。
   * @param lConnection DBへのコネクション
   * @param regCmdty 入力したデータ
   * @return なし
   * @throws SIDuplicateKeyException
   * @throws SIDBAccessException
   */
  public static void insertTableData(Connection lConnection,UICmdtyKeep cmdtyKeep,SIKeepHeader keepHeader) throws SIDuplicateKeyException,SIDBAccessException{
    SISpcType lSpcType=new SISpcType();
    //商品マスタインサート
    SIInsertRec lRec=new SIInsertRec("CmdtyMTbl");
    lRec.add("ShopCode",cmdtyKeep.getShopCode());//ショップコード
    lRec.add("CmdtyCode",cmdtyKeep.getCmdtyCode(lConnection));//親コード
    lRec.add("CmdtyName",cmdtyKeep.getCmdtyName());//商品名
    lRec.add("TaxFlg","2");//税区分:2:税込
    lRec.add("MakerCode",cmdtyKeep.getMakerCode(lConnection));//ブランド
    lRec.add("ColorCode",cmdtyKeep.getColorCode(lConnection));//色コード
    lRec.add("CmdtySize",cmdtyKeep.getCmdtySize());//商品サイズ
    lRec.add("DeliveryTypeCode",cmdtyKeep.getDeliveryTypeCode());//配送先コード
    lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
    lRec.add("InitDateTime",lSpcType);//登録日時
    //商品マスタテーブルの更新
    lRec.execute(lConnection);
    
    //商品マスタ（個別）インサート
    lRec=new SIInsertRec("individualtbl");
    lRec.add("shopCode",cmdtyKeep.getShopCode());//ショップコード
    lRec.add("cmdtyCode",cmdtyKeep.getCmdtyCode());//親コード
    lRec.add("individualCode",cmdtyKeep.getIndividualCode(lConnection));//在庫コード
    lRec.add("usedNewFlg",cmdtyKeep.getUsedNewFlg());//中古・新品区分
    lRec.add("arrivalDate",SIConfig.SIDATE_MIN_YEAR+"/"+"01/01");//入荷日
    lRec.add("processingExpence","0");//仕入価格
    lRec.add("tenureofuse","999");//使用年数
    lRec.add("salesSituationFlg","0");//販売状況:0:販売中
    lRec.add("soldOutDispFlg","0");//SOLDOUT後表示:0:表示しない
    lRec.add("campaignPriceFlg","0");//キャンペーン特価区分:0:通常価格
    lRec.add("inquiryFlg","0");//問合せ区分:0:通常商品
    lRec.add("frontDispFlg","0");//フロント表示:0:表示しない
    lRec.add("bgPriceDispFlg","0");//新品のBG特価フロント表示:0:常に表示
    lRec.add("consignGoodsFlg","0");//委託商品:0:委託でない商品
    lRec.add("bgPrice",cmdtyKeep.getBgPrice());//BG特価（本体価格）
    lRec.add("purchasePrice","0");//仕入価格
    lRec.add("purchasePrice2",cmdtyKeep.getPurchasePrice2());//仕入価格（在庫管理しない）
    lRec.add("recommendationFlg","0");//おすすめフラグ:0:通常
    lRec.add("memberDiscountFlg","0");//会員値引フラグ:0:しない
    lRec.add("endSellFlg","0");//販売終了日設定フラグ:0:設定しない
    lRec.add("rsrvEnableFlg","0");//予約販売フラグ:0:しない
    lRec.add("noStockFlg","0");//在庫無販売フラグ:0:しない
    lRec.add("amountFlg","0");//在庫数管理フラグ:0:管理しない
    lRec.add("wrappingFlg","0");//ラッピングフラグ:0:取扱不可
    lRec.add("discountFlg","0");//一括値引フラグ:0:しない
    lRec.add("salesDiscountFlg","0");//特価値引フラグ:0:しない
    lRec.add("spCampaignFlg","0");//キャンペーン設定フラグ:0:しない
    lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp");
    lRec.add("InitDateTime",lSpcType);//登録日時
    lRec.execute(lConnection);
    
    //関連商品Ｍ
    lRec=new SIInsertRec("cmdtyctgrymtbl");
    lRec.add("shopCode",cmdtyKeep.getShopCode());
    lRec.add("cmdtycode",cmdtyKeep.getCmdtyCode());//親コード
    lRec.add("ctgrycode",cmdtyKeep.getCtgryCode());//カテゴリコード
    lRec.execute(lConnection);
    
    //入庫履歴・出庫履歴
    SIDateTime lDateTime=new SIDateTime();
    
    lRec=new SIInsertRec("storehistorytbl");
    lRec.add("cmdtycode",cmdtyKeep.getCmdtyCode());
    lRec.add("individualcode",cmdtyKeep.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","4");
    lRec.add("initdatetime",lDateTime.getFullDate());
    lRec.execute(lConnection);

    lRec=new SIInsertRec("shiphistorytbl");
    lRec.add("cmdtycode",cmdtyKeep.getCmdtyCode());
    lRec.add("individualcode",cmdtyKeep.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","4");
    lRec.add("initdatetime",lDateTime.getFullDate());
    lRec.execute(lConnection);
    
    //STORESTOCKTBL
    UICmdtyStock stock = new UICmdtyStock();
    SIStoreStock storestock = new SIStoreStock();
    Collection cmdtyStockColl = stock.getCollection(lConnection,cmdtyKeep.getShopCode(),cmdtyKeep.getCmdtyCode(),cmdtyKeep.getIndividualCode());
    Iterator stockIta = cmdtyStockColl.iterator();
    while(stockIta.hasNext()){
      storestock = (SIStoreStock)stockIta.next();
      lSpcType=new SISpcType();
      lRec=new SIInsertRec("storestocktbl");
      lRec.add("shopCode",cmdtyKeep.getShopCode());//ショップコード
      lRec.add("cmdtyCode",cmdtyKeep.getCmdtyCode());//
      lRec.add("individualCode",cmdtyKeep.getIndividualCode());//
      lRec.add("branchcode",storestock.getBranchCode());//
      if(storestock.getBranchFlg().equals("1")){
        lRec.add("salespriorityflg","1");//
      }else{
        lRec.add("salespriorityflg","26");//
      }
      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);
      
      String season = lDateTime.getYearStr()+"01";//2007年以降の場合、1月の締処理をするまでは作らない（1月の締処理時に新規作成する）
      if(lDateTime.getYearStr().equals("2006")||SIDBUtil.hasData(lConnection,"SELECT * FROM inventorytbl WHERE season>='"+season+"'")){
        //締処理
        lRec=new SIInsertRec("stockrecordtbl");
        lRec.add("cmdtycode",cmdtyKeep.getCmdtyCode());
        lRec.add("individualcode",cmdtyKeep.getIndividualCode());
        lRec.add("branchcode",storestock.getBranchCode());
        lRec.add("stockyear",lDateTime.getYearStr());
        lRec.execute(lConnection);
        
        //棚卸在庫
        //lRec=new SIInsertRec("inventorystocktbl");
        //lRec.add("cmdtycode",cmdtyKeep.getCmdtyCode());
        //lRec.add("individualcode",cmdtyKeep.getIndividualCode());
        //lRec.add("branchcode",storestock.getBranchCode());
        //lRec.add("inventoryyear",lDateTime.getYearStr());
        //lRec.execute(lConnection);
      }
    }
  }
  
  // EDBTG003-00 mng-paku add start
  /**
   * 構成グループ情報を取得します。
   * @param conn
   * @param keepDetail
   * @return HashMap
   * @throws SIDBAccessException
   */
  public HashMap getCmdtyCompositionGroup(Connection conn, SIKeepDetail keepDetail) throws SIDBAccessException {
    StringBuffer sqlStatement = new StringBuffer();
    sqlStatement.append("SELECT ");
    sqlStatement.append(" aa.*, ");
    sqlStatement.append(" bb.detailcmdtycode, bb.detailindividualcode, bb.detailindividualname, bb.compositioncount, bb.disporder, bb.flagshipproductflg, ");
    sqlStatement.append(" cc.unitprice, cc.cmdtyname ");
    sqlStatement.append(" , cc.usednewflg, cc.taxflg, cc.amountflg, dd.purchaseprice ");
    sqlStatement.append("FROM ");
    sqlStatement.append(" compositiongroupmtbl aa, compositiongroupdetailtbl bb,  ");
    sqlStatement.append(" cmdtyunittbl cc  ");
    sqlStatement.append(" , individualtbl dd ");
    sqlStatement.append("WHERE aa.shopcode = '0' ");
    if (SIUtil.isNotNull(keepDetail.getSetCmdtyCode()))
      sqlStatement.append(" AND aa.cmdtyCode = ").append(SIDBUtil.SQL2Str(keepDetail.getSetCmdtyCode()));
    if (SIUtil.isNotNull(keepDetail.getSetIndividualCode()))
      sqlStatement.append(" AND aa.individualcode = ").append(SIDBUtil.SQL2Str(keepDetail.getSetIndividualCode()));
    if (SIUtil.isNotNull(keepDetail.getIndividualCode()))
      sqlStatement.append(" AND bb.detailindividualcode = ").append(SIDBUtil.SQL2Str(keepDetail.getIndividualCode()));
    sqlStatement.append(" AND aa.shopcode = bb.shopcode ");
    sqlStatement.append(" AND aa.cmdtycode = bb.cmdtycode ");
    sqlStatement.append(" AND aa.individualcode = bb.individualcode ");
    sqlStatement.append(" AND aa.groupcode = bb.groupcode ");
    sqlStatement.append(" AND bb.shopcode = cc.shopcode ");
    sqlStatement.append(" AND bb.detailcmdtycode = cc.cmdtycode ");
    sqlStatement.append(" AND bb.detailindividualcode = cc.individualcode ");
    sqlStatement.append(" AND bb.individualcode = dd.individualcode ");
    sqlStatement.append(" ORDER BY aa.groupcode, bb.disporder ");
    log.debug("SQL=" + sqlStatement.toString());
    return SIDBUtil.getOneRowData(conn, sqlStatement.toString());
  }
  
  /**
   * 添付商品情報を取得します。
   * @param conn
   * @param keepDetail
   * @return HashMap
   * @throws SIDBAccessException
   */
  public HashMap getCompositionAppended(Connection conn, SIKeepDetail keepDetail) throws SIDBAccessException {
    StringBuffer sqlStatement = new StringBuffer();
    sqlStatement.append("SELECT ");
    sqlStatement.append(" aa.*, ");
    sqlStatement.append(" bb.cmdtyname, bb.usednewflg, bb.amountflg ");
    sqlStatement.append("FROM appendedcmdtytbl aa, ");
    sqlStatement.append(" cmdtyunittbl bb ");
    sqlStatement.append("WHERE ");
    sqlStatement.append(" aa.shopcode = '0' ");
    if (SIUtil.isNotNull(keepDetail.getSetCmdtyCode()))
      sqlStatement.append(" AND aa.cmdtyCode = ").append(SIDBUtil.SQL2Str(keepDetail.getSetCmdtyCode()));
    if (SIUtil.isNotNull(keepDetail.getSetIndividualCode()))
      sqlStatement.append(" AND aa.individualcode = ").append(SIDBUtil.SQL2Str(keepDetail.getSetIndividualCode()));
    if (SIUtil.isNotNull(keepDetail.getIndividualCode()))
      sqlStatement.append(" AND aa.detailindividualcode = ").append(SIDBUtil.SQL2Str(keepDetail.getIndividualCode()));
    sqlStatement.append(" AND aa.shopcode = bb.shopcode ");
    sqlStatement.append(" AND aa.detailindividualcode = bb.individualcode ");
    sqlStatement.append(" ORDER BY aa.disporder ");
    log.debug("SQL=" + sqlStatement.toString());
    return SIDBUtil.getOneRowData(conn, sqlStatement.toString());
  }
  
  /**
   * オプション情報を取得します
   * @param conn
   * @param keepDetail
   * @return HashMap
   * @throws SIDBAccessException
   */
  public HashMap getCompositionOption(Connection conn, SIKeepDetail keepDetail) throws SIDBAccessException {
    StringBuffer sqlStatement = new StringBuffer();
    sqlStatement.append("SELECT ");
    sqlStatement.append(" aa.*, ");
    sqlStatement.append(" bb.cmdtyname, bb.unitprice, bb.usednewflg, bb.amountflg, bb.taxflg, cc.purchaseprice ");
    sqlStatement.append("FROM compositionoptiontbl aa, ");
    sqlStatement.append(" cmdtyunittbl bb, ");
    sqlStatement.append(" individualtbl cc ");
    sqlStatement.append("WHERE ");
    sqlStatement.append(" aa.shopcode = '0' ");
    if (SIUtil.isNotNull(keepDetail.getSetCmdtyCode()))
      sqlStatement.append(" AND aa.cmdtyCode = ").append(SIDBUtil.SQL2Str(keepDetail.getSetCmdtyCode()));
    if (SIUtil.isNotNull(keepDetail.getSetIndividualCode()))
      sqlStatement.append(" AND aa.individualcode = ").append(SIDBUtil.SQL2Str(keepDetail.getSetIndividualCode()));
    if (SIUtil.isNotNull(keepDetail.getIndividualCode()))
      sqlStatement.append(" AND aa.detailindividualcode = ").append(SIDBUtil.SQL2Str(keepDetail.getIndividualCode()));
    sqlStatement.append(" AND aa.shopcode = bb.shopcode ");
    sqlStatement.append(" AND aa.detailindividualcode = bb.individualcode ");
    sqlStatement.append(" AND aa.individualcode = cc.individualcode ");
    sqlStatement.append(" ORDER BY aa.disporder ");
    log.debug("SQL=" + sqlStatement.toString());
    return SIDBUtil.getOneRowData(conn, sqlStatement.toString());
  }
  
  /**
   * 添付品の添付数計算
   * @param conn
   * @param tmpColl
   * @param keepDetail
   * @param isAdd
   * @return setAmount
   * @throws SIDBAccessException
   */
  public int getSetAmount(Connection conn, Collection tmpColl, SIKeepDetail keepDetail, boolean isAdd, Collection setDataColl) throws SIDBAccessException {
    Iterator iteTmpColl = tmpColl.iterator();
    // 既存リストからセット品のキー作成
    Hashtable keyHash = new Hashtable();
    HashMap setCodeTmpMap = new HashMap();
    
    while (iteTmpColl.hasNext()) {
      SIKeepDetail keepDetailTmp = (SIKeepDetail) iteTmpColl.next();
      if ((SIConfig.SET_DETAIL_FLG_COMPOSITION.equals(keepDetailTmp.getSetDetailFlg()) || SIConfig.SET_DETAIL_FLG_OPTION.equals(keepDetailTmp.getSetDetailFlg()))
          && keepDetail.getBranchCode().equals(keepDetailTmp.getBranchCode())
          && keepDetail.getSetCmdtyCode().equals(keepDetailTmp.getSetCmdtyCode())
          && keepDetail.getSetIndividualCode().equals(keepDetailTmp.getSetIndividualCode())
          && !setCodeTmpMap.containsKey(keepDetailTmp.getSetCode())) {
        String[] checkKey = { keepDetailTmp.getSetCmdtyCode(), keepDetailTmp.getSetIndividualCode() };
        if (!keyHash.containsKey(keepDetailTmp.getSetCode())) {
          keyHash.put(keepDetailTmp.getSetCode(), checkKey);
          setCodeTmpMap.put(keepDetailTmp.getSetCode(), "");
        }
      }
    }
    // 添付品自身と同じセット品を数える
    int countSetCount = 0;
    String[] thisKey = { keepDetail.getSetCmdtyCode(), keepDetail.getSetIndividualCode() };
    Enumeration keys = keyHash.keys();
    while (keys.hasMoreElements()) {
      String keySetCode = (String) keys.nextElement();
      if (Arrays.equals((String[]) keyHash.get(keySetCode), thisKey)) {
        countSetCount++;
      }
    }
    HashMap compositionAppended = this.getCompositionAppended(conn, keepDetail);
    String strSetCount = (String) compositionAppended.get("setcount");
    String strAppendedCount = (String) compositionAppended.get("appendedcount");
    if (SIUtil.isNull(strSetCount) || SIUtil.isNull(strSetCount)) {
      return 0;
    }
    
    // セット数を取得
    String sumOfSetCountStr = "0";
    for(Iterator iteSetDataColl = setDataColl.iterator(); iteSetDataColl.hasNext();){
      HashMap setDataMap = (HashMap) iteSetDataColl.next();
      Iterator iteSetDataMap = setDataMap.keySet().iterator();
      while (iteSetDataMap.hasNext()) {
        SICmdtyComposition regComposition = (SICmdtyComposition) setDataMap.get(iteSetDataMap.next());
        if (keepDetail.getSetCmdtyCode().equals(regComposition.getCmdtyCode())
            && keepDetail.getSetIndividualCode().equals(regComposition.getIndividualCode())
        ){
          sumOfSetCountStr = SIUtil.add_LL(sumOfSetCountStr, regComposition.getAmount());
        }
      }
    }
    int sumOfSetCount = Integer.parseInt(sumOfSetCountStr);

    int setCount = Integer.parseInt(strSetCount);
    int appendedCount = Integer.parseInt(strAppendedCount);
    int addSetAmount = 0;
    int addMyself = isAdd ? 1 : 0;
    if (setCount != 0) {
      //addSetAmount = (int) ((countSetCount + addMyself) / setCount) * appendedCount;
      addSetAmount = (int) ((sumOfSetCount + addMyself) / setCount) * appendedCount;
    }
    return addSetAmount;
  }
  
  /**
   * セット商品かをチェック
   * @param conn
   * @param keepDetail
   * @return boolean
   * @throws SIDBAccessException
   */
  private boolean isSet(Connection conn, SIKeepDetail keepDetail) throws SIDBAccessException {
    if (SIConfig.SET_DETAIL_FLG_COMPOSITION.equals(keepDetail.getSetDetailFlg())) {
      HashMap composition = this.getCmdtyCompositionGroup(conn, keepDetail);
      if (!composition.isEmpty()) {
        return true;
      }
    } else if (SIConfig.SET_DETAIL_FLG_APPENDED.equals(keepDetail.getSetDetailFlg())) {
      HashMap composition = this.getCompositionAppended(conn, keepDetail);
      if (!composition.isEmpty()) {
        return true;
      }
    } else if (SIConfig.SET_DETAIL_FLG_OPTION.equals(keepDetail.getSetDetailFlg())) {
      HashMap composition = this.getCompositionOption(conn, keepDetail);
      if (!composition.isEmpty()) {
        return true;
      }
    }
    return false;
  }
  
  /**
   * 重複セット番号取得
   * @param session
   * @param setListHash
   * @return
   */
  public String getExistSetCode(HttpSession session, Hashtable setListHash) {
    Collection groupColl = (Collection) session.getAttribute(SIConfig.SISESSION_MAN_COMPOSITIONOPT_GROUP_LIST);
    Collection optionColl = (Collection) session.getAttribute(SIConfig.SISESSION_MAN_COMPOSITIONOPT_OPTION_LIST);
    int countSelfMember = groupColl.size() + optionColl.size();
    String existSetCode = null;
    Enumeration keys = setListHash.keys();
    while (keys.hasMoreElements()) { // セット重複チェック用リスト
      String keySetCode = (String) keys.nextElement();
      Collection setList = (Collection) setListHash.get(keySetCode);
      int countSetMember = setList.size();
      int countCompositionoptCollMember = 0;
      Iterator iteSetList = setList.iterator();
      while (iteSetList.hasNext()) { // セット重複チェック用リスト毎の商品リスト
        // 各セット毎の構成品
        SIKeepDetail keepDetailSet = (SIKeepDetail) iteSetList.next();
        // 構成品
        if (groupColl != null) {
          Iterator iteGroupColl = groupColl.iterator();
          while (iteGroupColl.hasNext()) {
            SIKeepDetail keepDetail = (SIKeepDetail) iteGroupColl.next();
            if (keepDetail.getSetDetailFlg().equals(keepDetailSet.getSetDetailFlg())
                && keepDetail.getIndividualCode().equals(keepDetailSet.getIndividualCode())
                && keepDetail.getBranchCode().equals(keepDetailSet.getBranchCode())
                && keepDetail.getSetCmdtyCode().equals(keepDetailSet.getSetCmdtyCode())
                && keepDetail.getSetIndividualCode().equals(keepDetailSet.getSetIndividualCode())) {
              ++countCompositionoptCollMember;
              break;
            }
          }
        }
        // オプション
        if (optionColl != null) {
          Iterator iteColl = optionColl.iterator();
          while (iteColl.hasNext()) {
            SIKeepDetail keepDetail = (SIKeepDetail) iteColl.next();
            if (keepDetail.getSetDetailFlg().equals(keepDetailSet.getSetDetailFlg())
                && keepDetail.getIndividualCode().equals(keepDetailSet.getIndividualCode())
                && keepDetail.getBranchCode().equals(keepDetailSet.getBranchCode())
                && keepDetail.getSetCmdtyCode().equals(keepDetailSet.getSetCmdtyCode())
                && keepDetail.getSetIndividualCode().equals(keepDetailSet.getSetIndividualCode())) {
              ++countCompositionoptCollMember;
              break;
            }
          }
        }
      }
      if (countCompositionoptCollMember == countSelfMember && countSetMember == countCompositionoptCollMember) {
        existSetCode = keySetCode;
        break;
      }
    }
    return existSetCode;
  }
  // EDBTG003-00 mng-paku add end
}