/** * 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.Collection; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; 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.SICmdtyCompositionAppended; 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.SIBGUtil; import jp.co.sint.tools.SICustomError; import jp.co.sint.tools.SICustomErrors; import jp.co.sint.tools.SIDateTime; import jp.co.sint.tools.SIErrorFactory; import jp.co.sint.tools.SIFatalException; import jp.co.sint.tools.SIHTMLUtil; import jp.co.sint.tools.SIStringUtil; import jp.co.sint.tools.SIURLParameter; import jp.co.sint.tools.SIUtil; import org.apache.log4j.Category; /** * @version $Id: SIRegIndividualSrv.java,v 1.0 2005/08/19 Exp $ * @author Tsuyoshi Yagi
* Description: キープ情報登録、変更を行うServlet *

* History *

* Author Date Reason *

* ============ ========== =========================== *
* T.Yagi 2005/12/05 Original */ public class SIKeepSrv extends SIServlet { // ログ用のインスタンスの生成 private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME); /** * doUpdate 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.newkeep"); }else if (actionName.equals("keep")){ UIRegNewKeep newKeep = (UIRegNewKeep) session.getAttribute(SIConfig.SISESSION_MAN_ESTIMATE_NEW_KEEP); if(newKeep == null){ newKeep = new UIRegNewKeep(); } newKeep.initKeep(request, urlParam); if(newKeep.validateJan(request, conn) && newKeep.validate(request,conn)){ SIKeepDetail keepDetail = newKeep.getKeepDetail(); keepDetail = complement(conn, keepDetail,newKeep); if(checkAmount(conn, keepDetail)){ try{ if(newKeep.getKeepNumber().equals("")){ String keepNum = ""; SIKeepHeader keepheader = new SIKeepHeader(); keepheader.setBranchCode(newKeep.getBelongBranchCode()); keepheader.setChargeCode(newKeep.getChargeCode()); keepheader.setStatus("0"); // EDBTG003-00 elecs-matsushima add start keepheader.setSetDiscount("0"); // EDBTG003-00 elecs-matsushima add end keepNum = insertHeader(conn, keepheader); newKeep.setKeepNumber(keepNum); session.setAttribute(SIConfig.SISESSION_MAN_ESTIMATE_NEW_KEEP, newKeep); } keepDetail.setKeepNumber(newKeep.getKeepNumber()); // EDBTG003-00 elecs-matsushima add start keepDetail.setSetDetailFlg(SIConfig.SET_DETAIL_FLG_NORMAL); keepDetail.setSetCode("-1"); keepDetail.setSetDetailCode("0"); // EDBTG003-00 elecs-matsushima add end int amount = existKeep(conn, keepDetail); if(amount == 0){ insertKeep(conn, keepDetail); setSoldOutFlg(conn, keepDetail); }else{ addKeep(conn, keepDetail); setSoldOutFlg(conn, keepDetail); } try{ databaseConnection.getConnection().commit(); }catch(SQLException sqle){} request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.keep")); forwardKey(request, response, "webshop.jsp.manager.estimate.newkeep"); }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.estimate.newkeep"); }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.estimate.newkeep"); } }else{ SICustomErrors errors = new SICustomErrors(); errors.addError(new SICustomError("manager.message.error.keep")); request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors); forwardKey(request, response, "webshop.jsp.manager.estimate.newkeep"); } }else{ forwardKey(request, response, "webshop.jsp.manager.estimate.newkeep"); } } else if (SIConfig.SIACTION_COMPOSITIONOPT.equals(actionName)) { // 構成品キープ Collection groupColl = (Collection) session.getAttribute(SIConfig.SISESSION_MAN_COMPOSITIONOPT_GROUP_LIST); if (groupColl == null) groupColl = new ArrayList(); Collection appendedColl = (Collection) session.getAttribute(SIConfig.SISESSION_MAN_COMPOSITIONOPT_APPENDED_LIST); if (appendedColl == null) appendedColl = new ArrayList(); Collection optionColl = (Collection) session.getAttribute(SIConfig.SISESSION_MAN_COMPOSITIONOPT_OPTION_LIST); if (optionColl == null) optionColl = new ArrayList(); SICmdtyComposition regComposition = (SICmdtyComposition) session.getAttribute(SIConfig.SISESSION_MAN_COMPOSITIONOPT_DATA); session.removeAttribute(SIConfig.SISESSION_MAN_COMPOSITIONOPT_GROUP_LIST); session.removeAttribute(SIConfig.SISESSION_MAN_COMPOSITIONOPT_APPENDED_LIST); session.removeAttribute(SIConfig.SISESSION_MAN_COMPOSITIONOPT_OPTION_LIST); session.removeAttribute(SIConfig.SISESSION_MAN_COMPOSITIONOPT_DATA); UIRegNewKeep newKeep = (UIRegNewKeep) session.getAttribute(SIConfig.SISESSION_MAN_ESTIMATE_NEW_KEEP); if(newKeep == null){ newKeep = new UIRegNewKeep(); } newKeep.initKeep(request, urlParam); // 現在キープしている情報を取得 Map keepSetMap = new HashMap(); if (SIUtil.isNotNull(newKeep.getKeepNumber())) { Collection keepDbColl = UIRegNewKeep.getSetKeepList(conn, newKeep.getKeepNumber(), regComposition.getIndividualCode()); if (keepDbColl != null){ Iterator itekeepDbColl = keepDbColl.iterator(); while(itekeepDbColl.hasNext()){ SIKeepDetail keepDetailDbTmp = (SIKeepDetail) itekeepDbColl.next(); if (keepDetailDbTmp.getSetCode() != "-1" && (keepDetailDbTmp.getSetDetailFlg().equals(SIConfig.SET_DETAIL_FLG_COMPOSITION) || keepDetailDbTmp.getSetDetailFlg().equals(SIConfig.SET_DETAIL_FLG_OPTION)) ){ String mapKey = keepDetailDbTmp.getKeepNumber() + "~" + keepDetailDbTmp.getSetCode() + "~" + keepDetailDbTmp.getSetCmdtyCode() + "~" + keepDetailDbTmp.getSetIndividualCode(); Collection keepSetCol = (Collection) keepSetMap.get(mapKey); if (keepSetCol == null) keepSetCol = new ArrayList(); keepSetCol.add(keepDetailDbTmp); keepSetMap.put(mapKey, keepSetCol); } } } } // チェック及び配列作成 Collection insertColl = new ArrayList(); insertColl.addAll(groupColl); insertColl.addAll(optionColl); Map resultMap = validateSetAndAllocation(request, conn, newKeep, insertColl); SICustomErrors errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY); if (errors == null || errors.isEmpty()) { groupColl = (Collection) resultMap.get("group"); optionColl = (Collection) resultMap.get("option"); /* キープ商品重複チェックここから ****************/ String dbSetCode = ""; Map setDetailInfo = new HashMap(); if (keepSetMap.size() > 0) { // DBに登録されている場合 // DBに構成品が存在している場合、構成品の重複チェックを行う Iterator iteKeepSetMap = keepSetMap.keySet().iterator(); while (iteKeepSetMap.hasNext()){ String key = (String) iteKeepSetMap.next(); String[] keySplit = SIStringUtil.split(key, "~"); // 同じ構成商品の場合 if (keySplit[3].equals(regComposition.getIndividualCode())) { List keepDetailDbListTmp = (List) keepSetMap.get(key); Iterator iteKeepDetailDbListTmp = keepDetailDbListTmp.iterator(); setDetailInfo = new HashMap(); boolean resultChkFlg = true; int groupCnt = 0; int optionCnt = 0; while (iteKeepDetailDbListTmp.hasNext()){ boolean checkFlg = false; SIKeepDetail keepDetailDbTmp = (SIKeepDetail) iteKeepDetailDbListTmp.next(); String mapkey = keepDetailDbTmp.getIndividualCode() + "~" + keepDetailDbTmp.getSetDetailFlg(); setDetailInfo.put(mapkey, keepDetailDbTmp.getSetDetailCode()); if (keepDetailDbTmp.getSetDetailFlg().equals(SIConfig.SET_DETAIL_FLG_COMPOSITION)) {// 構成グループ groupCnt++; Iterator iteGroupColl = groupColl.iterator(); while (iteGroupColl.hasNext()){ SIKeepDetail keepDetailTmp = (SIKeepDetail) iteGroupColl.next(); if (keepDetaEquals(keepDetailDbTmp, keepDetailTmp)) { checkFlg = true; break; } } } else if (keepDetailDbTmp.getSetDetailFlg().equals(SIConfig.SET_DETAIL_FLG_OPTION)) {// オプション optionCnt++; Iterator iteOptionColl = optionColl.iterator(); while (iteOptionColl.hasNext()){ SIKeepDetail keepDetailTmp = (SIKeepDetail) iteOptionColl.next(); if (keepDetaEquals(keepDetailDbTmp, keepDetailTmp)) { checkFlg = true; break; } } } else if (keepDetailDbTmp.getSetDetailFlg().equals(SIConfig.SET_DETAIL_FLG_APPENDED)) {// 添付商品 // 添付品は比較しない checkFlg = true; } if (!checkFlg) { resultChkFlg = false; break; } } if (!resultChkFlg) continue; if (groupCnt != groupColl.size()) continue; if (optionCnt != optionColl.size()) continue; // 最後まで処理が通れば同一構成品とする。 dbSetCode = keySplit[1]; break; } } } /* キープ商品重複チェックここまで ****************/ /* 登録更新処理ここから ****************/ try{ if (SIUtil.isNotNull(dbSetCode) && setDetailInfo.size() > 0) { //重複の場合、値をサマリし、更新する。 // 添付品を一度削除する Collection appendListDb = getAppendList(conn, newKeep, regComposition); for (Iterator iteAppendListDb = appendListDb.iterator(); iteAppendListDb.hasNext();){ SIKeepDetail delKeepDetail = (SIKeepDetail) iteAppendListDb.next(); delKeepDetail.setAmount("0"); updateKeep(conn, delKeepDetail, 0); } Collection dbInsColl = new ArrayList(); dbInsColl.addAll(groupColl); dbInsColl.addAll(optionColl); Iterator iteInsertColl = dbInsColl.iterator(); while (iteInsertColl.hasNext()){ SIKeepDetail keepDetail = (SIKeepDetail) iteInsertColl.next(); String mapKey = keepDetail.getIndividualCode() + "~" + keepDetail.getSetDetailFlg(); keepDetail.setKeepNumber(newKeep.getKeepNumber()); keepDetail.setSetCode(dbSetCode); keepDetail.setSetDetailCode((String) setDetailInfo.get(mapKey)); addSetDetailKeep(conn, keepDetail); setSoldOutFlg(conn, keepDetail); } // セットテーブルのupdate addKeepSet(conn, regComposition, newKeep.getKeepNumber(), dbSetCode); deleteKeepDetaMarge(conn, newKeep.getKeepNumber()); // 添付品判定 Collection addAppendedColl = getAppendedList(conn, regComposition, newKeep); Map resultApedMap = validateSetAndAllocation(request, conn, newKeep, addAppendedColl); errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY); if (errors == null || errors.isEmpty()) { appendedColl = (Collection) resultApedMap.get("appended"); Iterator iteAppendedColl = appendedColl.iterator(); while (iteAppendedColl.hasNext()){ SIKeepDetail keepDetail = (SIKeepDetail) iteAppendedColl.next(); keepDetail.setPrice("0"); keepDetail.setCost("0"); keepDetail.setPurchasePrice("0"); keepDetail.setProcessingExpence("0"); keepDetail.setConsignGoodsFee("0"); keepDetail.setSetDetailFlg(SIConfig.SET_DETAIL_FLG_APPENDED); keepDetail.setKeepNumber(newKeep.getKeepNumber()); keepDetail.setSetCode(dbSetCode); keepDetail.setSetCmdtyCode(regComposition.getCmdtyCode()); keepDetail.setSetIndividualCode(regComposition.getIndividualCode()); keepDetail.setSetDetailCode(getMaxSetDetailCode(conn, newKeep, dbSetCode)); insertSetDetailKeep(conn, keepDetail); setSoldOutFlg(conn, keepDetail); } } addSetDiscountUpdate(conn, newKeep.getKeepNumber()); try{ databaseConnection.getConnection().commit(); }catch(SQLException sqle){} request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.keep")); forwardKey(request, response, "webshop.jsp.manager.estimate.newkeep"); } else { // 重複でない場合、通常登録を行う。 String setcode = "1"; if(newKeep.getKeepNumber().equals("")){ String keepNum = ""; SIKeepHeader keepheader = new SIKeepHeader(); keepheader.setBranchCode(newKeep.getBelongBranchCode()); keepheader.setChargeCode(newKeep.getChargeCode()); keepheader.setStatus("0"); String setDiscountSum = (String) session.getAttribute(SIConfig.SISESSION_MAN_COMPOSITIONOPT_SET_DISCOUNT); if (SIUtil.isNull(setDiscountSum)) setDiscountSum = "0"; keepheader.setSetDiscount(setDiscountSum); keepNum = insertHeader(conn, keepheader); newKeep.setKeepNumber(keepNum); session.setAttribute(SIConfig.SISESSION_MAN_ESTIMATE_NEW_KEEP, newKeep); } else { setcode = getSetCodeMax(newKeep.getKeepNumber(), conn); // 添付品を一度削除する Collection appendListDb = getAppendList(conn, newKeep, regComposition); for (Iterator iteAppendListDb = appendListDb.iterator(); iteAppendListDb.hasNext();){ SIKeepDetail delKeepDetail = (SIKeepDetail) iteAppendListDb.next(); delKeepDetail.setAmount("0"); updateKeep(conn, delKeepDetail, 0); } } Collection dbInsColl = new ArrayList(); dbInsColl.addAll(groupColl); dbInsColl.addAll(optionColl); Iterator iteInsertColl = dbInsColl.iterator(); while (iteInsertColl.hasNext()){ SIKeepDetail keepDetail = (SIKeepDetail) iteInsertColl.next(); keepDetail.setSetCode(setcode); keepDetail.setKeepNumber(newKeep.getKeepNumber()); insertSetDetailKeep(conn, keepDetail); setSoldOutFlg(conn, keepDetail); } // セットテーブルのinsert insertKeepSet(conn, regComposition, newKeep.getKeepNumber(), setcode); deleteKeepDetaMarge(conn, newKeep.getKeepNumber()); //添付品判定 Collection addAppendedColl = getAppendedList(conn, regComposition, newKeep); Map resultApedMap = validateSetAndAllocation(request, conn, newKeep, addAppendedColl); errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY); if (errors == null || errors.isEmpty()) { appendedColl = (Collection) resultApedMap.get("appended"); Iterator iteAppendedColl = appendedColl.iterator(); while (iteAppendedColl.hasNext()){ SIKeepDetail keepDetail = (SIKeepDetail) iteAppendedColl.next(); keepDetail.setPrice("0"); keepDetail.setCost("0"); keepDetail.setPurchasePrice("0"); keepDetail.setProcessingExpence("0"); keepDetail.setConsignGoodsFee("0"); keepDetail.setSetDetailFlg(SIConfig.SET_DETAIL_FLG_APPENDED); keepDetail.setKeepNumber(newKeep.getKeepNumber()); keepDetail.setSetCode(setcode); keepDetail.setSetCmdtyCode(regComposition.getCmdtyCode()); keepDetail.setSetIndividualCode(regComposition.getIndividualCode()); keepDetail.setSetDetailCode(getMaxSetDetailCode(conn, newKeep, setcode)); insertSetDetailKeep(conn, keepDetail); setSoldOutFlg(conn, keepDetail); } } addSetDiscountUpdate(conn, newKeep.getKeepNumber()); try{ databaseConnection.getConnection().commit(); }catch(SQLException sqle){} request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.keep")); forwardKey(request, response, "webshop.jsp.manager.estimate.newkeep"); } }catch(SIDuplicateKeyException sqle){ try{ databaseConnection.getConnection().rollback(); }catch(SQLException ee){} errors = new SICustomErrors(); errors.addError(new SICustomError("database.insert.duplicate")); request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors); forwardKey(request, response, "webshop.jsp.manager.estimate.newkeep"); }catch(SIDBAccessException sqle){ try{ databaseConnection.getConnection().rollback(); }catch(SQLException ee){} errors = new SICustomErrors(); errors.addError(new SICustomError("database.execute.error")); request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors); forwardKey(request, response, "webshop.jsp.manager.estimate.newkeep"); } catch (SIFatalException e) { try{ databaseConnection.getConnection().rollback(); }catch(SQLException ee){} errors = new SICustomErrors(); errors.addError(new SICustomError("database.execute.error")); request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors); forwardKey(request, response, "webshop.jsp.manager.estimate.newkeep"); } /* 登録更新処理ここまで ****************/ } else { forwardKey(request, response, "webshop.jsp.manager.estimate.newkeep"); } }else if(actionName.equals(SIConfig.SIACTION_KEEP_CMDTY)){//フリー商品登録 UICmdtyKeep cmdtyKeep = new UICmdtyKeep();//フリー商品 UIRegNewKeep newKeep = null; cmdtyKeep.init(request, urlParam); if(cmdtyKeep.validate(request,conn)){ try{ newKeep = (UIRegNewKeep) session.getAttribute(SIConfig.SISESSION_MAN_ESTIMATE_NEW_KEEP); if(newKeep == null){ newKeep = new UIRegNewKeep(); } SIKeepHeader keepheader = new SIKeepHeader(); newKeep.initKeep(request, urlParam); //フリー商品を登録する・キープ登録 insertTableData(conn,cmdtyKeep,keepheader);//データ登録 if(newKeep.getKeepNumber().equals("")){ String keepNum = ""; keepheader = new SIKeepHeader(); keepheader.setBranchCode(newKeep.getBelongBranchCode()); keepheader.setChargeCode(newKeep.getChargeCode()); keepheader.setStatus("0"); keepNum = insertHeader(conn, keepheader); newKeep.setKeepNumber(keepNum); session.setAttribute(SIConfig.SISESSION_MAN_ESTIMATE_NEW_KEEP, newKeep); } int amount = existKeep(conn,cmdtyKeep,newKeep); if(amount == 0) insertKeep(conn,cmdtyKeep,newKeep); else addKeep(conn,cmdtyKeep,newKeep); try{databaseConnection.getConnection().commit();}catch(SQLException sqle){} }catch(SIDuplicateKeyException e2){ 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.estimate.newkeep"); } session.removeAttribute(SIConfig.SISESSION_MAN_ESTIMATE_CMDTY_KEEP); request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.keep")); forwardKey(request, response, "webshop.jsp.manager.estimate.newkeep"); }else{//エラーがある場合 session.setAttribute(SIConfig.SISESSION_MAN_ESTIMATE_CMDTY_KEEP, cmdtyKeep); forwardKey(request, response, "webshop.jsp.manager.estimate.newkeep"); } }else if(actionName.equals(SIConfig.SIACTION_UPDATE)){ UIRegNewKeep newKeep = (UIRegNewKeep) session.getAttribute(SIConfig.SISESSION_MAN_ESTIMATE_NEW_KEEP); if(newKeep == null){ newKeep = new UIRegNewKeep(); } newKeep.initUpdate(request, urlParam); if(newKeep.validate(request,conn,false)){ SIKeepDetail keepDetail = newKeep.getKeepDetail(); keepDetail = complementUpdate(conn, keepDetail); keepDetail.setKeepNumber(newKeep.getKeepNumber()); int amount = existKeep(conn, keepDetail); try{ updateKeep(conn, keepDetail, amount); setSoldOutFlg(conn, keepDetail); deleteKeepDetaMarge(conn, newKeep.getKeepNumber()); // EDBTG003-00 elecs-matsushima add addSetDiscountUpdate(conn, newKeep.getKeepNumber()); // EDBTG003-00 elecs-matsushima add try{databaseConnection.getConnection().commit();}catch(SQLException sqle){} request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.modify")); forwardKey(request, response, "webshop.jsp.manager.estimate.newkeep"); }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.estimate.newkeep"); }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.estimate.newkeep"); } }else{ forwardKey(request, response, "webshop.jsp.manager.estimate.newkeep"); } }else if(actionName.equals(SIConfig.SIACTION_NEXT)){ UIRegNewKeep newKeep = (UIRegNewKeep) session.getAttribute(SIConfig.SISESSION_MAN_ESTIMATE_NEW_KEEP); if(newKeep == null){ newKeep = new UIRegNewKeep(); newKeep.setBackURL("webshop.jsp.manager.keep.detail");//キープ明細からきた場合 } newKeep.initKeep(request, urlParam); session.setAttribute(SIConfig.SISESSION_MAN_ESTIMATE_NEW_KEEP, newKeep); session.removeAttribute(SIConfig.SISESSION_MAN_ESTIMATE_NEW_ESTIMATE); forwardKey(request, response, "webshop.jsp.manager.estimate.newestimate"); }else if (actionName.equals(SIConfig.SIACTION_BACK)){ UIRegNewKeep newKeep = (UIRegNewKeep) session.getAttribute(SIConfig.SISESSION_MAN_ESTIMATE_NEW_KEEP); session.removeAttribute(SIConfig.SISESSION_MAN_ESTIMATE_NEW_KEEP); if(newKeep==null){ forwardKey(request,response,"webshop.jsp.manager.login"); }else{ 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); } // EDBTG003-00 nagayoshi add start if (newKeep.getBackURL().equals("webshop.jsp.manager.estimate.updateestimate")) { // セット値引金額設定再設定処理 String setDiscountPrice = (String)urlParam.getParam("setDiscount"); UIRegUpdateEstimate estimateFull = (UIRegUpdateEstimate)session.getAttribute(SIConfig.SISESSION_MAN_UPDATE_ESTIMATE); setDiscountPrice = UIRegNewKeep.getSetDiscount(conn, estimateFull.getEstimate().getKeepNumber(), estimateFull.getEstimate().getMemberLevelCode()); estimateFull.getEstimate().setSumOfSetDiscount(setDiscountPrice); } // EDBTG003-00 nagayoshi add end forwardKey(request,response,newKeep.getBackURL()); } }else if(actionName.equals(SIConfig.SIACTION_MOVE)){ UIRegNewKeep newKeep = new UIRegNewKeep(); newKeep.initKeep(request, urlParam); if (newKeep.validateMove(request,conn)) { try{ createMoveData(newKeep, conn); try{databaseConnection.getConnection().commit();}catch(SQLException sqle){} request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.freeword","移動データを作成しました")); forwardKey(request, response, "webshop.jsp.manager.keep.list"); }catch(SIDBAccessException e){ try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){} SICustomErrors errors=new SICustomErrors(); errors.addError(new SICustomError("database.execute.error")); request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK,errors); forwardKey(request, response, "webshop.jsp.manager.keep.detail"); }catch(SIDuplicateKeyException e){ try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){} SICustomErrors errors=new SICustomErrors(); errors.addError(new SICustomError("database.execute.error")); request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK,errors); forwardKey(request, response, "webshop.jsp.manager.keep.detail"); } } else { forwardKey(request, response, "webshop.jsp.manager.keep.detail"); } }else if (actionName.equals(SIConfig.SIACTION_MAIL)){ String keepnum = this.getParameter(urlParam,"keepNumberTxt"); String mailComment=this.getParameter(urlParam,"mailComment"); try{ String fromMail = SIDBUtil.getFirstData(databaseConnection.getConnection(), "SELECT email FROM chargetbl WHERE chargecode=" + SIDBUtil.SQL2Str(manLogin.getUserCode())); SIBGUtil.sendKeepMail(databaseConnection.getConnection(),keepnum,mailComment,fromMail); request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME,SIErrorFactory.getErrorMsg("manager.message.success.mail")); try{databaseConnection.getConnection().commit();}catch(SQLException sqle){} forwardKey(request, response, "webshop.jsp.manager.estimate.newkeep"); }catch(SIDBAccessException e){ try{databaseConnection.getConnection().rollback();}catch(SQLException sqle){} SICustomErrors errors=new SICustomErrors(); errors.addError(new SICustomError("manager.message.failure.mail")); request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY_BAK,errors); forwardKey(request, response, "webshop.jsp.manager.estimate.newkeep"); } }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(); } } //EDBTG003-00 elecs-matsushima add start public String getMaxSetDetailCode(Connection lConnection, UIRegNewKeep newKeep, String setCode) throws SIDBAccessException { StringBuffer sql = new StringBuffer(); sql.append("SELECT MAX(setdetailcode) AS setdetailcode FROM keepdetailtbl "); sql.append("WHERE keepnumber = ").append(SIDBUtil.SQL2Str(newKeep.getKeepNumber()," ")); sql.append(" AND setcode = ").append(SIDBUtil.SQL2Str(setCode," ")); String setDetailCode = SIDBUtil.getFirstData(lConnection, sql.toString()); if (SIUtil.isNotNull(setDetailCode) && !setDetailCode.equals("-1")) { return SIUtil.add_LL(setDetailCode, "1"); } return "1"; } public Collection getAppendedList(Connection lConnection, SICmdtyComposition regComposition, UIRegNewKeep newKeep ) throws SIDBAccessException, SIDuplicateKeyException, SIFatalException{ Collection appendedDbColl = getCmdtyCompositionAppended(lConnection, regComposition.getShopCode(), regComposition.getCmdtyCode(), regComposition.getIndividualCode()); String setAmount = getSetAmount(lConnection, newKeep.getKeepNumber(), regComposition.getIndividualCode()); Collection appendedList = new ArrayList(); for (Iterator iteAppendedDbColl = appendedDbColl.iterator(); iteAppendedDbColl.hasNext();){ SICmdtyCompositionAppended appended = (SICmdtyCompositionAppended) iteAppendedDbColl.next(); String appendedCount = SIUtil.div(setAmount, appended.getSetCount()); appendedCount = SIUtil.multi_LL(appendedCount, appended.getAppendedCount()); int appendedCnt = 0; try { appendedCnt = Integer.parseInt(appendedCount); } catch (NumberFormatException e) { e.printStackTrace(); } if (appendedCnt > 0) { SIKeepDetail keepDetail = new SIKeepDetail(); // 支店及び、数量を更新 keepDetail.setCmdtyCode(appended.getDetailCmdtyCode()); keepDetail.setIndividualCode(appended.getDetailIndividualCode()); keepDetail.setBranchCode("999"); keepDetail.setAmount(appendedCount); keepDetail.setKeepNumber(newKeep.getKeepNumber()); keepDetail.setSetCmdtyCode(regComposition.getCmdtyCode()); keepDetail.setSetIndividualCode(regComposition.getIndividualCode()); if (appended.getAmountFlg().equals("0")) keepDetail.setCmdtyType("1"); else keepDetail.setCmdtyType("0"); keepDetail.setSetDetailFlg(SIConfig.SET_DETAIL_FLG_APPENDED); appendedList.add(keepDetail); } } return appendedList; } public Collection getCmdtyCompositionAppended(Connection lConnection, String shopCode, String cmdtyCode, String individualCode) { Collection rec = new ArrayList(); Statement lStatement = null; ResultSet lResultSet = null; // SQL生成 StringBuffer sqlStr = new StringBuffer(); sqlStr.append("SELECT a.*, b.amountFlg, B.janCodeIndividual "); 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()) { SICmdtyCompositionAppended cmdtyCompositionAppended = new SICmdtyCompositionAppended(); cmdtyCompositionAppended.setShopCode(lResultSet.getString("shopCode")); cmdtyCompositionAppended.setCmdtyCode(lResultSet.getString("cmdtyCode")); cmdtyCompositionAppended.setIndividualCode(lResultSet.getString("individualCode")); cmdtyCompositionAppended.setDetailCmdtyCode(lResultSet.getString("detailCmdtyCode")); cmdtyCompositionAppended.setDetailIndividualCode(lResultSet.getString("detailIndividualCode")); cmdtyCompositionAppended.setSetCount(lResultSet.getString("setCount")); cmdtyCompositionAppended.setAppendedCount(lResultSet.getString("appendedCount")); cmdtyCompositionAppended.setDispOrder(lResultSet.getString("dispOrder")); cmdtyCompositionAppended.setAmountFlg(lResultSet.getString("amountFlg")); cmdtyCompositionAppended.setJanCodeIndividual(lResultSet.getString("janCodeIndividual")); rec.add(cmdtyCompositionAppended); } } catch (SQLException e) { e.printStackTrace(); } finally { SIDBUtil.close(lResultSet, lStatement); } return rec; } public List keepAllocation(Connection conn, SIKeepDetail keepDetail, Map keepsAmount) throws SIDBAccessException { Statement statement = null; ResultSet resultSet = null; List keepDetailColl = new ArrayList(); String branch = ""; int num = Integer.parseInt(keepDetail.getAmount()); int amount = 0; int allocate = 0; boolean chkBranchStockFlg = false; if(SIUtil.isNull(keepDetail.getBranchCode())) { keepDetail.setBranchCode("999"); } if(!keepDetail.getCmdtyType().equals("0")){ // 在庫管理しない商品は、そのまま返却 keepDetailColl.add(keepDetail); return keepDetailColl; } // 支店毎の在庫数を取得する StringBuffer sqlStatement = new StringBuffer(); sqlStatement.append("SELECT b.branchcode,b.branchname, a.amount FROM storestocktbl a,branchtbl b "); sqlStatement.append("WHERE a.cmdtycode = ").append(SIDBUtil.SQL2Str(keepDetail.getCmdtyCode()," ")); sqlStatement.append("AND a.individualcode = ").append(SIDBUtil.SQL2Str(keepDetail.getIndividualCode()," ")); sqlStatement.append("AND a.branchcode=b.branchcode "); sqlStatement.append("ORDER BY a.salespriorityflg,b.prioritybase,a.branchcode"); // 実行 try { statement = conn.createStatement(); resultSet = statement.executeQuery(sqlStatement.toString()); while (resultSet.next()) { branch = resultSet.getString("branchcode"); amount = resultSet.getInt("amount"); String branchName = resultSet.getString("branchname"); // Keepしている数を取得 int keepAmount = 0; String keepsBranchAmount = (String) keepsAmount.get(getKeepsAmountMapKey(keepDetail.getCmdtyCode(), keepDetail.getIndividualCode(), branch)); if (SIUtil.isNotNull(keepsBranchAmount)) keepAmount = Integer.parseInt(keepsBranchAmount); amount = amount - keepAmount; SIKeepDetail keepAllocateDetail = new SIKeepDetail(); if (amount <= 0) { continue; } else if (num > amount) { num = num - amount; allocate = amount; amount = 0; // 支店及び、数量を更新 keepAllocateDetail.setKeepNumber(keepDetail.getKeepNumber()); keepAllocateDetail.setCmdtyCode(keepDetail.getCmdtyCode()); keepAllocateDetail.setIndividualCode(keepDetail.getIndividualCode()); keepAllocateDetail.setCmdtyType(keepDetail.getCmdtyType()); keepAllocateDetail.setCmdtyName(keepDetail.getCmdtyName()); keepAllocateDetail.setPrice(keepDetail.getPrice()); keepAllocateDetail.setCost(keepDetail.getCost()); keepAllocateDetail.setDeliveryType(keepDetail.getDeliveryType()); keepAllocateDetail.setSize(keepDetail.getSize()); keepAllocateDetail.setAmount(String.valueOf(allocate)); keepAllocateDetail.setBranchCode(branch); keepAllocateDetail.setBranchName(branchName); keepAllocateDetail.setPurchasePrice(keepDetail.getPurchasePrice()); keepAllocateDetail.setProcessingExpence(keepDetail.getProcessingExpence()); keepAllocateDetail.setConsignGoodsFee(keepDetail.getConsignGoodsFee()); if (SIUtil.isNull(keepAllocateDetail.getConsignGoodsFee())) keepAllocateDetail.setConsignGoodsFee("0"); keepAllocateDetail.setOrgPrice(keepDetail.getOrgPrice()); keepAllocateDetail.setSetDetailFlg(keepDetail.getSetDetailFlg()); keepAllocateDetail.setSetCode(keepDetail.getSetCode()); keepAllocateDetail.setSetCmdtyCode(keepDetail.getSetCmdtyCode()); keepAllocateDetail.setSetIndividualCode(keepDetail.getSetIndividualCode()); keepAllocateDetail.setSetDetailCode(keepDetail.getSetDetailCode()); keepAllocateDetail.setSetDiscount(keepDetail.getSetDiscount()); keepAllocateDetail.setSetAmount(keepDetail.getSetAmount()); keepAllocateDetail.setIndividualName(keepDetail.getIndividualName()); keepAllocateDetail.setUsedNewFlg(keepDetail.getUsedNewFlg()); keepAllocateDetail.setDetailCode(keepDetail.getDetailCode()); keepAllocateDetail.setSetCmdtyName(keepDetail.getSetCmdtyName()); keepsAmount.put(getKeepsAmountMapKey(keepAllocateDetail), String.valueOf(keepAmount + allocate)); } else if (num <= amount) { amount = amount - num; allocate = num; num = 0; // 支店及び、数量を更新 keepAllocateDetail.setKeepNumber(keepDetail.getKeepNumber()); keepAllocateDetail.setCmdtyCode(keepDetail.getCmdtyCode()); keepAllocateDetail.setIndividualCode(keepDetail.getIndividualCode()); keepAllocateDetail.setCmdtyType(keepDetail.getCmdtyType()); if (SIUtil.isNull(keepAllocateDetail.getConsignGoodsFee())) keepAllocateDetail.setConsignGoodsFee("0"); keepAllocateDetail.setCmdtyName(keepDetail.getCmdtyName()); keepAllocateDetail.setPrice(keepDetail.getPrice()); keepAllocateDetail.setCost(keepDetail.getCost()); keepAllocateDetail.setDeliveryType(keepDetail.getDeliveryType()); keepAllocateDetail.setSize(keepDetail.getSize()); keepAllocateDetail.setAmount(String.valueOf(allocate)); keepAllocateDetail.setBranchCode(branch); keepAllocateDetail.setBranchName(branchName); keepAllocateDetail.setPurchasePrice(keepDetail.getPurchasePrice()); keepAllocateDetail.setProcessingExpence(keepDetail.getProcessingExpence()); keepAllocateDetail.setConsignGoodsFee(keepDetail.getConsignGoodsFee()); keepAllocateDetail.setOrgPrice(keepDetail.getOrgPrice()); keepAllocateDetail.setSetDetailFlg(keepDetail.getSetDetailFlg()); keepAllocateDetail.setSetCode(keepDetail.getSetCode()); keepAllocateDetail.setSetCmdtyCode(keepDetail.getSetCmdtyCode()); keepAllocateDetail.setSetIndividualCode(keepDetail.getSetIndividualCode()); keepAllocateDetail.setSetDetailCode(keepDetail.getSetDetailCode()); keepAllocateDetail.setSetDiscount(keepDetail.getSetDiscount()); keepAllocateDetail.setSetAmount(keepDetail.getSetAmount()); keepAllocateDetail.setIndividualName(keepDetail.getIndividualName()); keepAllocateDetail.setUsedNewFlg(keepDetail.getUsedNewFlg()); keepAllocateDetail.setDetailCode(keepDetail.getDetailCode()); keepAllocateDetail.setSetCmdtyName(keepDetail.getSetCmdtyName()); keepsAmount.put(getKeepsAmountMapKey(keepAllocateDetail), String.valueOf(keepAmount + allocate)); } if (num == 0) { keepDetailColl.add(keepAllocateDetail); chkBranchStockFlg = true; break; } keepDetailColl.add(keepAllocateDetail); } } catch (Exception ex) { throw new SIDBAccessException(ex); }finally{ SIDBUtil.close(resultSet, statement); } if (!chkBranchStockFlg) return null; return keepDetailColl; } /** * 同一のキープ商品かどうかチェックする * * @param keepDetail1 * @param keepDetail2 * @return */ public boolean keepDetaEquals(SIKeepDetail keepDetail1, SIKeepDetail keepDetail2) { if (keepDetail1.getIndividualCode().equals(keepDetail2.getIndividualCode()) && keepDetail1.getBranchCode().equals(keepDetail2.getBranchCode()) && keepDetail1.getCmdtyCode().equals(keepDetail2.getCmdtyCode()) && keepDetail1.getIndividualCode().equals(keepDetail2.getIndividualCode()) && keepDetail1.getSetCmdtyCode().equals(keepDetail2.getSetCmdtyCode()) && keepDetail1.getSetIndividualCode().equals(keepDetail2.getSetIndividualCode()) ){ return true; } return false; } /** * セット番号を採番します * @param keepNo * @param lConnection * @return * @throws SIDBAccessException */ public String getSetCodeMax(String keepNo, Connection lConnection) throws SIDBAccessException{ StringBuffer sql = new StringBuffer(); if (SIUtil.isNotNull(keepNo)) { sql.append("SELECT MAX(setcode) AS setcode FROM keepdetailtbl "); sql.append("WHERE keepnumber = ").append(SIDBUtil.SQL2Str(keepNo," ")); String setcode = SIDBUtil.getFirstData(lConnection, sql.toString()); if (SIUtil.isNotNull(setcode) && !setcode.equals("-1")) { return SIUtil.add_LL(setcode, "1"); } } return "1"; } //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()," ")); // EDBTG003-00 elecs-matsushima add start sql.append("AND setcode = ").append(SIDBUtil.SQL2Str(keepDetail.getSetCode()," ")); sql.append("AND setdetailcode = ").append(SIDBUtil.SQL2Str(keepDetail.getSetDetailCode())); // EDBTG003-00 elecs-matsushima add start 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(lResultSet, lStatement); } 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(lResultSet, lStatement); } 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,b.consigngoodsfee"); 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.setPrice((String) lResultSet.getString("bgprice")); keepDetail.setCmdtyType((String) lResultSet.getString("cmdtytype")); keepDetail.setPurchasePrice((String) lResultSet.getString("purchaseprice")); keepDetail.setProcessingExpence((String) lResultSet.getString("processingexpence")); keepDetail.setConsignGoodsFee((String) lResultSet.getString("consignGoodsFee")); if(SIUtil.isVariableCode(keepDetail.getIndividualCode()) &&newKeep.getVariableKeepPrice()!=null&&newKeep.getVariableKeepPurchase()!=null){ keepDetail.setPrice(newKeep.getVariableKeepPrice()); keepDetail.setPurchasePrice(newKeep.getVariableKeepPurchase()); keepDetail.setCost(newKeep.getVariableKeepPurchase()); } } }catch(Exception ex){ throw new SIDBAccessException(ex); }finally{ SIDBUtil.close(lResultSet, lStatement); } 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 "); // EDBTG003-00 mng-paku mod start // 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"); sql.append(", CASE b.amountflg WHEN 0 THEN 1 ELSE 0 END AS cmdtyType "); sql.append(", c.cmdtycode, c.individualcode, c.amount, c.setdetailflg, c.setcode, c.setcmdtycode, c.setindividualcode, c.setdetailcode "); sql.append("FROM cmdtymtbl a, individualtbl b, keepdetailtbl c "); sql.append("WHERE b.cmdtycode = c.cmdtycode "); sql.append("AND b.individualcode = c.individualcode "); sql.append("AND b.cmdtycode = a.cmdtycode "); sql.append("AND c.keepnumber = ").append(SIDBUtil.SQL2Str(keepDetail.getKeepNumber(), " ")); sql.append("AND c.cmdtycode = ").append(SIDBUtil.SQL2Str(keepDetail.getCmdtyCode(), " ")); sql.append("AND c.individualcode = ").append(SIDBUtil.SQL2Str(keepDetail.getIndividualCode(), " ")); // EDBTG003-00 mng-paku mod end 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")); // EDBTG003-00 mng-paku add start keepDetail.setCmdtyType((String) lResultSet.getString("cmdtyType")); keepDetail.setCmdtyCode((String) lResultSet.getString("cmdtycode")); keepDetail.setIndividualCode((String) lResultSet.getString("individualcode")); keepDetail.setSetDetailFlg((String) lResultSet.getString("setdetailflg")); if (SIUtil.isNotNull(keepDetail.getSetDetailFlg()) && !SIConfig.SET_DETAIL_FLG_NORMAL.equals(keepDetail.getSetDetailFlg())) { keepDetail.setSetCode((String) lResultSet.getString("setcode")); keepDetail.setSetCmdtyCode((String) lResultSet.getString("setcmdtycode")); keepDetail.setSetIndividualCode((String) lResultSet.getString("setindividualcode")); keepDetail.setSetDetailCode((String) lResultSet.getString("setdetailcode")); } // EDBTG003-00 mng-paku add end } }catch(Exception ex){ throw new SIDBAccessException(ex); }finally{ SIDBUtil.close(lResultSet, lStatement); } 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()); lRec.add("consigngoodsfee", keepDetail.getConsignGoodsFee()); 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(); SIBGUtil.sendAlertMail(conn, keepDetail.getCmdtyCode(), keepDetail.getIndividualCode()); }catch(Exception ex){ throw new SIDBAccessException(ex); } } } //EDBTG003-00 elecs-matsushima add start /** * セット商品時の構成商品情報を登録する * @param conn * @param keepDetail * @throws SIDuplicateKeyException * @throws SIDBAccessException */ public void insertSetDetailKeep(Connection conn, SIKeepDetail keepDetail) throws SIDuplicateKeyException, SIDBAccessException { Statement lStatement1 = null; Statement lStatement2 = null; 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"); lStatement1 = conn.createStatement(); lStatement1.execute(sql.toString()); } catch (Exception ex) { throw new SIDBAccessException(ex); } finally{ try{if(lStatement1!=null)lStatement1.close();}catch(SQLException e){e.printStackTrace();} } } 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()); if (SIUtil.isNull(keepDetail.getConsignGoodsFee())) { lRec.add("consigngoodsfee", "0"); } else { lRec.add("consigngoodsfee", keepDetail.getConsignGoodsFee()); } lRec.add("setdetailflg", keepDetail.getSetDetailFlg()); lRec.add("setcode", keepDetail.getSetCode()); lRec.add("setcmdtycode", keepDetail.getSetCmdtyCode()); lRec.add("setindividualcode", keepDetail.getSetIndividualCode()); lRec.add("setdetailcode", keepDetail.getSetDetailCode()); 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()," ")); lStatement2 = conn.createStatement(); lStatement2.execute(sql.toString()); SIBGUtil.sendAlertMail(conn, keepDetail.getCmdtyCode(), keepDetail.getIndividualCode()); }catch(Exception ex){ throw new SIDBAccessException(ex); } finally { try{if (lStatement2!=null)lStatement2.close();}catch(SQLException e){e.printStackTrace();} } } } public String getKeepsAmountMapKey(String cmdtyCode, String individualCode, String branch){ SIKeepDetail keepDetail = new SIKeepDetail(); keepDetail.setCmdtyCode(cmdtyCode); keepDetail.setIndividualCode(individualCode); keepDetail.setBranchCode(branch); return getKeepsAmountMapKey(keepDetail); } public String getKeepsAmountMapKey(SIKeepDetail keepDetail){ if (keepDetail == null) return ""; String mapKeepsKey = keepDetail.getCmdtyCode() + "~" + keepDetail.getIndividualCode() + "~" + keepDetail.getBranchCode(); return mapKeepsKey; } /** * セット品時のチェック及び、支店在庫分配処理 * * @param request * @param conn * @param newKeep * @param insertColl * @return * @throws SIDBAccessException */ public Map validateSetAndAllocation(HttpServletRequest request, Connection conn, UIRegNewKeep newKeep, Collection insertColl) throws SIDBAccessException{ request.removeAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY); SICustomErrors errors = new SICustomErrors(); Collection insertGroupList = new ArrayList(); Collection insertOptionList = new ArrayList(); Collection insertAppendedList = new ArrayList(); Map keepAmountChkMap = new HashMap(); Map keepAmounts = new HashMap(); boolean errorFlg = false; for (Iterator iteInsertColl = insertColl.iterator(); iteInsertColl.hasNext();){ Collection insertDataList = new ArrayList(); UIRegNewKeep chkNewKeep = newKeep; chkNewKeep.setKeepDetail((SIKeepDetail) iteInsertColl.next()); if (chkNewKeep.validateJan(request, conn)) { if (SIUtil.isNull(chkNewKeep.getJanCodeIndividual())) { // 分割する Collection keepAllocationCol = keepAllocation(conn,chkNewKeep.getKeepDetail(), keepAmounts); if (keepAllocationCol == null) { // キープできず errors.addError(new SICustomError("manager.message.error.keep.individual",chkNewKeep.getKeepDetail().getIndividualCode())); errorFlg = true; break; } insertDataList.addAll(keepAllocationCol); } else { // 支店及び、数量を更新 SIKeepDetail keepAllocateDetail = new SIKeepDetail(); keepAllocateDetail.setKeepNumber(chkNewKeep.getKeepDetail().getKeepNumber()); keepAllocateDetail.setCmdtyCode(chkNewKeep.getKeepDetail().getCmdtyCode()); keepAllocateDetail.setIndividualCode(chkNewKeep.getKeepDetail().getIndividualCode()); keepAllocateDetail.setCmdtyType(chkNewKeep.getKeepDetail().getCmdtyType()); keepAllocateDetail.setCmdtyName(chkNewKeep.getKeepDetail().getCmdtyName()); keepAllocateDetail.setPrice(chkNewKeep.getKeepDetail().getPrice()); keepAllocateDetail.setCost(chkNewKeep.getKeepDetail().getCost()); keepAllocateDetail.setDeliveryType(chkNewKeep.getKeepDetail().getDeliveryType()); keepAllocateDetail.setSize(chkNewKeep.getKeepDetail().getSize()); keepAllocateDetail.setAmount(chkNewKeep.getKeepDetail().getAmount()); if (SIUtil.isNull(chkNewKeep.getKeepDetail().getBranchCode())) chkNewKeep.getKeepDetail().setBranchCode("999"); keepAllocateDetail.setBranchCode(chkNewKeep.getKeepDetail().getBranchCode()); keepAllocateDetail.setBranchName(chkNewKeep.getKeepDetail().getBranchName()); keepAllocateDetail.setPurchasePrice(chkNewKeep.getKeepDetail().getPurchasePrice()); keepAllocateDetail.setProcessingExpence(chkNewKeep.getKeepDetail().getProcessingExpence()); keepAllocateDetail.setConsignGoodsFee(chkNewKeep.getKeepDetail().getConsignGoodsFee()); if (SIUtil.isNull(keepAllocateDetail.getConsignGoodsFee())) keepAllocateDetail.setConsignGoodsFee("0"); keepAllocateDetail.setOrgPrice(chkNewKeep.getKeepDetail().getOrgPrice()); keepAllocateDetail.setSetDetailFlg(chkNewKeep.getKeepDetail().getSetDetailFlg()); keepAllocateDetail.setSetCode(chkNewKeep.getKeepDetail().getSetCode()); keepAllocateDetail.setSetCmdtyCode(chkNewKeep.getKeepDetail().getSetCmdtyCode()); keepAllocateDetail.setSetIndividualCode(chkNewKeep.getKeepDetail().getSetIndividualCode()); keepAllocateDetail.setSetDetailCode(chkNewKeep.getKeepDetail().getSetDetailCode()); keepAllocateDetail.setSetDiscount(chkNewKeep.getKeepDetail().getSetDiscount()); keepAllocateDetail.setSetAmount(chkNewKeep.getKeepDetail().getSetAmount()); keepAllocateDetail.setIndividualName(chkNewKeep.getKeepDetail().getIndividualName()); keepAllocateDetail.setUsedNewFlg(chkNewKeep.getKeepDetail().getUsedNewFlg()); keepAllocateDetail.setDetailCode(chkNewKeep.getKeepDetail().getDetailCode()); keepAllocateDetail.setSetCmdtyName(chkNewKeep.getKeepDetail().getSetCmdtyName()); insertDataList.add(keepAllocateDetail); String keepsBranchAmount = (String) keepAmounts.get(getKeepsAmountMapKey(keepAllocateDetail)); keepsBranchAmount = SIUtil.add_LL(keepsBranchAmount, keepAllocateDetail.getAmount()); keepAmounts.put(getKeepsAmountMapKey(keepAllocateDetail), keepsBranchAmount); } // エラーチェック for (Iterator iteInsertDataList = insertDataList.iterator(); iteInsertDataList.hasNext();){ UIRegNewKeep chkNewKeep2 = newKeep; chkNewKeep2.setKeepDetail((SIKeepDetail) iteInsertDataList.next()); String keepSum = (String) keepAmountChkMap.get(getKeepsAmountMapKey(chkNewKeep2.getKeepDetail())); int keepSumAmount = 0; if (SIUtil.isNotNull(keepSum)) keepSumAmount = Integer.parseInt(keepSum); if (chkNewKeep2.validateSet(request,conn,true, keepSumAmount)){ SIKeepDetail keepDetailBr = chkNewKeep2.getKeepDetail(); keepDetailBr = complement(conn, keepDetailBr, chkNewKeep2); Map amountResMap = checkAmountForKeeps(conn, keepDetailBr, String.valueOf(keepSumAmount)); String result = (String) amountResMap.get("result"); if (result.equals("0")){ String keepAmountMap = (String) amountResMap.get("keepAmount"); keepAmountChkMap.put(getKeepsAmountMapKey(keepDetailBr), keepAmountMap); // 配列の再作成 if (keepDetailBr.getSetDetailFlg().equals(SIConfig.SET_DETAIL_FLG_COMPOSITION)) {// 構成グループ insertGroupList.add(keepDetailBr); } else if (keepDetailBr.getSetDetailFlg().equals(SIConfig.SET_DETAIL_FLG_OPTION)) {// オプション insertOptionList.add(keepDetailBr); } else if (keepDetailBr.getSetDetailFlg().equals(SIConfig.SET_DETAIL_FLG_APPENDED)) {// 添付商品 insertAppendedList.add(keepDetailBr); } } else { errors.addError(new SICustomError("manager.message.error.keep.individual",keepDetailBr.getIndividualCode())); errorFlg = true; break; } } else { errorFlg = true; break; } } if (errorFlg) break; } else { break; } } if (!errors.isEmpty()) { request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors); } else { errors = (SICustomErrors) request.getAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY); } if (errors != null && !errors.isEmpty()) { return null; } Map resultMap = new HashMap(); resultMap.put("group", insertGroupList); resultMap.put("option", insertOptionList); resultMap.put("appended", insertAppendedList); return resultMap; } //EDBTG003-00 elecs-matsushima add end 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()); //EDBTG003-00 elecs-matsushima add start lRec.addCondition("setcode",keepDetail.getSetCode()); lRec.addCondition("setcmdtycode",keepDetail.getSetCmdtyCode()); lRec.addCondition("setindividualcode",keepDetail.getSetIndividualCode()); lRec.addCondition("setdetailcode",keepDetail.getSetDetailCode()); //EDBTG003-00 elecs-matsushima add end 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); SIBGUtil.sendAlertMail(conn, keepDetail.getCmdtyCode(), keepDetail.getIndividualCode()); }catch(Exception ex){ throw new SIDBAccessException(ex); } } } //EDBTG003-00 elecs-matsushima add start /** * セット商品キープ時のセット商品情報登録 * @param lConnection * @param regComposition * @param keepNo * @param setCode * @throws SIDBAccessException */ public void deleteKeepDetaMarge(Connection lConnection, String keepNo) throws SIDBAccessException{ Statement lStatement = null; ResultSet lResultSet = null; StringBuffer sqlSbf = new StringBuffer(); try{ sqlSbf.append("SELECT a.* FROM keepsetcmdtytbl a "); sqlSbf.append("WHERE keepnumber = ").append(SIDBUtil.SQL2Str(keepNo, " ")); sqlSbf.append(" AND SETCODE NOT IN ("); sqlSbf.append(" SELECT b.setcode FROM keepdetailtbl b "); sqlSbf.append(" WHERE b.keepnumber = ").append(SIDBUtil.SQL2Str(keepNo, " ")); sqlSbf.append(" )"); lStatement = lConnection.createStatement(); log.debug(sqlSbf.toString()); lResultSet = lStatement.executeQuery(sqlSbf.toString()); while(lResultSet.next()){ SIDeleteRec DRec = new SIDeleteRec("keepsetcmdtytbl"); DRec.addCondition("keepnumber", keepNo); DRec.addCondition("setcode", lResultSet.getString("setcode")); DRec.execute(lConnection); } }catch(Exception ex){ throw new SIDBAccessException(ex); } finally { SIDBUtil.close(lStatement, lResultSet); } } /** * セット商品のヘッダのセット値引きを更新 * @param conn * @param keepNo * @return * @throws SIDBAccessException * @throws SIDuplicateKeyException */ public String addSetDiscountUpdate(Connection conn, String keepNo) throws SIDBAccessException, SIDuplicateKeyException{ StringBuffer sql = new StringBuffer(); sql.append("SELECT SUM(setdiscount * setamount) FROM keepsetcmdtytbl "); sql.append("WHERE keepnumber = ").append(SIDBUtil.SQL2Str(keepNo," ")); String discount = SIDBUtil.getFirstData(conn, sql.toString()); if (SIUtil.isNull(discount)) discount = "0"; SISpcType lSpcType = new SISpcType(); SIModifyRec lRec = new SIModifyRec("keeptbl"); lRec.addCondition("keepnumber", keepNo); lRec.add("setdiscount", discount); lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp"); lRec.add("keepdate", lSpcType);// 登録日時 lRec.execute(conn); return discount; } /** * セット商品のヘッダのセット値引きを更新 * @param conn * @param keepNo * @return * @throws SIDBAccessException * @throws SIDuplicateKeyException */ public String getSetAmount(Connection conn, String keepNo, String individualCode) throws SIDBAccessException, SIDuplicateKeyException{ StringBuffer sql = new StringBuffer(); sql.append("SELECT SUM(setamount) FROM keepsetcmdtytbl "); sql.append("WHERE keepnumber = ").append(SIDBUtil.SQL2Str(keepNo," ")); sql.append(" AND setindividualcode = ").append(SIDBUtil.SQL2Str(individualCode," ")); String setAmount = SIDBUtil.getFirstData(conn, sql.toString()); return setAmount; } /** * セット商品時の構成商品のキープ情報更新 * @param conn * @param keepDetail * @throws SIDBAccessException */ public void addSetDetailKeep(Connection conn, SIKeepDetail keepDetail) throws 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.addCondition("setcode",keepDetail.getSetCode()); lRec.addCondition("setcmdtycode",keepDetail.getSetCmdtyCode()); lRec.addCondition("setindividualcode",keepDetail.getSetIndividualCode()); lRec.addCondition("setdetailcode",keepDetail.getSetDetailCode()); 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); SIBGUtil.sendAlertMail(conn, keepDetail.getCmdtyCode(), keepDetail.getIndividualCode()); }catch(Exception ex){ throw new SIDBAccessException(ex); } } } /** * セット商品キープ時のセット商品情報登録 * @param lConnection * @param regComposition * @param keepNo * @param setCode * @throws SIDBAccessException */ public void insertKeepSet(Connection lConnection, SICmdtyComposition regComposition, String keepNo, String setCode) throws SIDBAccessException{ try{ SIInsertRec lRec = new SIInsertRec("keepsetcmdtytbl"); // EDBTG003-00 mng-paku add start if (SIUtil.isNotNull(setCode) && Integer.parseInt(setCode) > 0) { // セットの登録チェック String sql = "select keepnumber from keepsetcmdtytbl where keepnumber = '" + keepNo + "' and setcode = '" + setCode + "' "; String existData = SIDBUtil.getFirstData(lConnection, sql); if (SIUtil.isNotNull(existData)) { // セット品情報の削除 SIDeleteRec DRec = new SIDeleteRec("keepsetcmdtytbl"); DRec.addCondition("keepnumber", keepNo); DRec.addCondition("setcode", setCode); DRec.execute(lConnection); } } // EDBTG003-00 mng-paku add end lRec.add("keepnumber",keepNo); lRec.add("setcode",setCode); lRec.add("setcmdtycode",regComposition.getCmdtyCode()); lRec.add("setindividualcode",regComposition.getIndividualCode()); lRec.add("cmdtyname",regComposition.getSetCmdtyName()); lRec.add("setdiscount",regComposition.getSetDiscount()); lRec.add("setamount",regComposition.getAmount()); lRec.execute(lConnection); }catch (Exception ex){ throw new SIDBAccessException(ex); } } /** * セット商品キープ変更時のセット商品情報登録 * @param lConnection * @param regComposition * @param keepNo * @param setCode * @throws SIDBAccessException */ public void addKeepSet(Connection lConnection, SICmdtyComposition regComposition, String keepNo, String setCode) throws SIDBAccessException{ try{ SIModifyRec lRec = new SIModifyRec("keepsetcmdtytbl"); lRec.addCondition("keepnumber",keepNo); lRec.addCondition("setcode",setCode); lRec.addCondition("setcmdtycode",regComposition.getCmdtyCode()); lRec.addCondition("setindividualcode",regComposition.getIndividualCode()); SISpcType lSpc = new SISpcType(); lSpc = new SISpcType("setamount+"+regComposition.getAmount()); lRec.add("cmdtyname",regComposition.getSetCmdtyName()); lRec.add("setdiscount",regComposition.getSetDiscount()); lRec.add("setamount",lSpc); lRec.execute(lConnection); }catch (Exception ex){ throw new SIDBAccessException(ex); } } //EDBTG003-00 elecs-matsushima add end 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()); // EDBTG003-00 elecs-matsushima add start // EDBTG003-00 mng-paku mod start // lRec.addCondition("setcode",keepDetail.getSetCode()); // lRec.addCondition("setcmdtycode",keepDetail.getSetCmdtyCode()); // lRec.addCondition("setindividualcode",keepDetail.getSetIndividualCode()); // lRec.addCondition("setdetailcode",keepDetail.getSetDetailCode()); if (SIUtil.isNotNull(keepDetail.getSetDetailFlg()) && !SIConfig.SET_DETAIL_FLG_NORMAL.equals(keepDetail.getSetDetailFlg())) { lRec.addCondition("setcode", keepDetail.getSetCode()); lRec.addCondition("setcmdtycode", keepDetail.getSetCmdtyCode()); lRec.addCondition("setindividualcode", keepDetail.getSetIndividualCode()); lRec.addCondition("setdetailcode", keepDetail.getSetDetailCode()); } // EDBTG003-00 mng-paku mod end // EDBTG003-00 elecs-matsushima add end 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()); // EDBTG003-00 elecs-matsushima add start lRec.addCondition("setcode",keepDetail.getSetCode()); lRec.addCondition("setdetailcode",keepDetail.getSetDetailCode()); // EDBTG003-00 elecs-matsushima add end 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); SIBGUtil.sendAlertMail(conn, keepDetail.getCmdtyCode(), keepDetail.getIndividualCode()); }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); } finally { SIDBUtil.close(lStatement, lResultSet); } 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); } finally { SIDBUtil.close(lStatement2, lResultSet2); } if(amount >= Integer.parseInt(keepDetail.getAmount())){ return true; }else{ return false; } } } //EDBTG003-00 elecs-matsushima add start /** * 在庫数を確認します。 * * @param conn * @param keepDetail * @param keepAmount * @return * @throws SIDBAccessException */ public Map checkAmountForKeeps(Connection conn, SIKeepDetail keepDetail, String keepAmount) throws SIDBAccessException { Map resMap = new HashMap(); resMap.put("result", "-1"); resMap.put("keepAmount", keepAmount); 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")){ resMap.put("result", "0"); return resMap; } 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"); } amount = amount - Integer.parseInt(keepAmount); // 現在のKEEP数を在庫数より減算する }catch(Exception ex){ throw new SIDBAccessException(ex); } finally { SIDBUtil.close(lStatement2, lResultSet2); } if(amount >= Integer.parseInt(keepDetail.getAmount())){ int keepSum = Integer.parseInt(keepDetail.getAmount()) + Integer.parseInt(keepAmount); resMap.put("result", "0"); resMap.put("keepAmount", String.valueOf(keepSum)); return resMap; } return resMap; } //EDBTG003-00 elecs-matsushima add end 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()); // EDBTG003-00 elecs-matsushima add start lRec.add("setdiscount",keepHeader.getSetDiscount()); // EDBTG003-00 elecs-matsushima add end 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); } } } /** * insertTableData * データベースにレコードを作成します。 * @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","1");//会員値引フラグ:1:する 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:しない lRec.add("stockCode",cmdtyKeep.getStockCode());//仕入先 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")||"18".equals(storestock.getBranchCode())){//物流A、物流センターはA 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); } } } private void createMoveData(UIRegNewKeep keep,Connection lConnection) throws SIDuplicateKeyException,SIDBAccessException,SQLException{ Statement lStatement = null; ResultSet lResultSet = null; SIInsertRec iRec = null; SIDeleteRec dRec = null; SIDateTime lDate = new SIDateTime(); String shipBranchName = ""; String storeNumber = ""; String shipNumber = ""; shipBranchName = SIDBUtil.getFirstData(lConnection, "SELECT branchname FROM branchtbl WHERE branchcode="+SIDBUtil.SQL2Str(keep.getMoveBranchCode())); lStatement = lConnection.createStatement(); lResultSet = lStatement.executeQuery("SELECT d.*,b.branchname FROM keepdetailtbl d,branchtbl b WHERE b.branchcode=d.branchcode AND d.keepnumber="+SIDBUtil.SQL2Str(keep.getKeepNumber())); while (lResultSet.next()) { storeNumber = SIDBUtil.getFirstData(lConnection, "SELECT max(storenumber)+1 FROM storehistorytbl WHERE individualcode="+SIDBUtil.SQL2Str(lResultSet.getString("individualcode"))); shipNumber = SIDBUtil.getFirstData(lConnection, "SELECT max(shipnumber)+1 FROM shiphistorytbl WHERE individualcode="+SIDBUtil.SQL2Str(lResultSet.getString("individualcode"))); iRec = new SIInsertRec("shiphistorytbl"); iRec.add("shipnumber", shipNumber); iRec.add("cmdtycode", lResultSet.getString("cmdtycode")); iRec.add("individualcode", lResultSet.getString("individualcode")); iRec.add("shipbranch", lResultSet.getString("branchcode")); iRec.add("shipcharge", keep.getChargeCode()); iRec.add("amount", lResultSet.getString("amount")); iRec.add("purchaseprice", "0"); iRec.add("processingexpence", "0"); iRec.add("shiptype", "3"); iRec.add("comment", shipBranchName+"ヘ出庫"); iRec.add("delflg", "0"); iRec.add("initdatetime", lDate.getFullDate()); iRec.execute(lConnection); iRec = new SIInsertRec("storehistorytbl"); iRec.add("storenumber", storeNumber); iRec.add("cmdtycode", lResultSet.getString("cmdtycode")); iRec.add("individualcode", lResultSet.getString("individualcode")); iRec.add("storebranch", keep.getMoveBranchCode()); iRec.add("storecharge", keep.getChargeCode()); iRec.add("amount", lResultSet.getString("amount")); iRec.add("purchaseprice", "0"); iRec.add("processingexpence", "0"); iRec.add("storetype", "3"); iRec.add("comment", lResultSet.getString("branchname")+"から入庫"); iRec.add("ordercode", shipNumber); iRec.add("delflg", "0"); iRec.add("initdatetime", lDate.getFullDate()); iRec.execute(lConnection); } SIDBUtil.close(lStatement, lResultSet); //在庫数量調整（キープ物理削除） dRec = new SIDeleteRec("keeptbl"); dRec.addCondition("keepnumber", keep.getKeepNumber()); dRec.execute(lConnection); dRec = new SIDeleteRec("keepdetailtbl"); dRec.addCondition("keepnumber", keep.getKeepNumber()); dRec.execute(lConnection); } //EDBTG003-00 elecs-matsushima add start private Collection getAppendList(Connection lConnection, UIRegNewKeep newKeep, SICmdtyComposition regComposition) throws SIDBAccessException{ Statement statement = null; ResultSet resultSet = null; StringBuffer sqlSbf = new StringBuffer(); Collection delApendedList = new ArrayList(); sqlSbf.append("SELECT aa.* FROM keepdetailtbl aa "); sqlSbf.append("WHERE aa.keepnumber = ").append(SIDBUtil.SQL2Str(newKeep.getKeepNumber()," ")); sqlSbf.append(" AND aa.setcmdtycode = ").append(SIDBUtil.SQL2Str(regComposition.getCmdtyCode()," ")); sqlSbf.append(" AND aa.setindividualcode = ").append(SIDBUtil.SQL2Str(regComposition.getIndividualCode()," ")); sqlSbf.append(" AND aa.setdetailflg = ").append(SIDBUtil.SQL2Str(SIConfig.SET_DETAIL_FLG_APPENDED," ")); try { statement = lConnection.createStatement(); resultSet = statement.executeQuery(sqlSbf.toString()); while (resultSet.next()) { SIKeepDetail keepDetail = new SIKeepDetail(); // 支店及び、数量を更新 keepDetail.setKeepNumber(resultSet.getString("keepnumber")); keepDetail.setCmdtyCode(resultSet.getString("cmdtycode")); keepDetail.setIndividualCode(resultSet.getString("individualcode")); keepDetail.setBranchCode(resultSet.getString("branchcode")); keepDetail.setCmdtyType(resultSet.getString("cmdtytype")); keepDetail.setCmdtyName(resultSet.getString("cmdtyname")); keepDetail.setPrice(resultSet.getString("price")); keepDetail.setCost(resultSet.getString("cost")); keepDetail.setDeliveryType(resultSet.getString("deliverytype")); keepDetail.setSize(resultSet.getString("size")); keepDetail.setAmount(resultSet.getString("amount")); keepDetail.setPurchasePrice(resultSet.getString("purchaseprice")); keepDetail.setProcessingExpence(resultSet.getString("processingexpence")); keepDetail.setConsignGoodsFee(resultSet.getString("consigngoodsfee")); keepDetail.setSetDetailFlg(resultSet.getString("setdetailflg")); keepDetail.setSetCode(resultSet.getString("setcode")); keepDetail.setSetCmdtyCode(resultSet.getString("setcmdtycode")); keepDetail.setSetIndividualCode(resultSet.getString("setindividualcode")); keepDetail.setSetDetailCode(resultSet.getString("setdetailcode")); delApendedList.add(keepDetail); } } catch (SQLException e) { e.printStackTrace(); throw new SIDBAccessException(e); } finally { SIDBUtil.close(resultSet, statement); } return delApendedList; } //EDBTG003-00 elecs-matsushima add end }