/** * Copyright (c) 2003-2005 System Integrator Corporation. * All Rights Reserved. */ package jp.co.sint.servlet.mallmgr; import java.io.IOException; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.naming.NamingException; import javax.servlet.ServletException; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import javax.servlet.http.HttpSession; import java.text.DecimalFormat; import java.util.Collection; import java.util.Iterator; import jp.co.sint.basic.SILogin; import jp.co.sint.basic.SIStoreStock; import jp.co.sint.basic.SINameValue; import jp.co.sint.beans.front.UICmdtyInfo; import jp.co.sint.beans.mallmgr.UICmdtyStock; import jp.co.sint.beans.mallmgr.UIIndividual; import jp.co.sint.beans.mallmgr.UIRegCmdty; import jp.co.sint.beans.mallmgr.UIRegCmdtyPreview; import jp.co.sint.config.SIConfig; import jp.co.sint.database.SIDBAccessException; import jp.co.sint.database.SIDBUtil; import jp.co.sint.database.SIDatabaseConnection; import jp.co.sint.database.SIDuplicateKeyException; import jp.co.sint.database.SIInsertRec; import jp.co.sint.database.SIModifyRec; import jp.co.sint.database.SISpcType; import jp.co.sint.servlet.SIServlet; import jp.co.sint.tools.SICustomError; import jp.co.sint.tools.SICustomErrors; import jp.co.sint.tools.SIDateTime; import jp.co.sint.tools.SIErrorFactory; import jp.co.sint.tools.SIHTMLUtil; import jp.co.sint.tools.SIURLParameter; import jp.co.sint.tools.SIUtil; import org.apache.log4j.Category; /** * @version $Id: SIRegIndividualSrv.java,v 1.0 2005/08/19 Exp $ * @author Tsuyoshi Yagi
* Description: 商品（個別）を登録．修正することなどを行うServlet *

* History *

*

* Author         Date        Reason *

* ============   ==========  ===========================
* T.Yagi 2005/08/17 Original */ public class SIRegIndividualSrv extends SIServlet { // ログ用のインスタンスの生成 private static Category log = Category.getInstance(SIConfig.SILOG4J_WEBSHOP_CATEGORY_NAME); /** * doUpdate HTTP リクエストの処理 * * @param request リクエスト * @param response * @return なし * @throws ServletException * @throws IOException */ public void doUpdate(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { SILogin manLogin = SIHTMLUtil.getLogin(request); if (!manLogin.isLogin()) { forwardKey(request, response, "webshop.jsp.manager.login"); return; } HttpSession session = request.getSession(true);// セッションの取得 SIDatabaseConnection databaseConnection = new SIDatabaseConnection();// DBへのコネクションの作成 SIURLParameter urlParam = new SIURLParameter(request);// 7.1.1 ST0236 追加 try { String actionName = this.getActionName(urlParam);// 画面からのアクション //7.1.1 ST0236 修正 String editMode = this.getEditMode(urlParam);// DBへの編集モード //7.1.1 ST0236 修正 UIRegCmdty regCmdty = new UIRegCmdty(); UIIndividual regIndividual = new UIIndividual(); UICmdtyStock regStock = new UICmdtyStock(); if (SIConfig.SIACTION_CONFIRM.equalsIgnoreCase(actionName)) { regCmdty = new UIRegCmdty(); regIndividual = new UIIndividual(); regCmdty.init(request, urlParam); regIndividual.init(request, urlParam); regIndividual.setFirstDispFlg("1"); regStock.init(request, databaseConnection.getConnection()); regIndividual.setEditModeTxt(regCmdty.getEditModeTxt()); session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty); session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_INDIVIDUAL, regIndividual); session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_STORE_STOCK, regStock); if (!regIndividual.validateIndividual(request, databaseConnection.getConnection()) || !regStock.validateStock(request, databaseConnection.getConnection())) { forwardKey(request, response, "webshop.jsp.manager.cmdty.indivdual"); } else { forwardKey(request, response, "webshop.jsp.manager.cmdty.individual.confirm"); } }else if (SIConfig.SIACTION_REFRESH.equalsIgnoreCase(actionName)) { regCmdty = new UIRegCmdty(); regIndividual = new UIIndividual(); regCmdty.init(request, urlParam); regIndividual.init(request, urlParam); regIndividual.setFirstDispFlg("1"); regStock.init(request, databaseConnection.getConnection()); regIndividual.setEditModeTxt(regCmdty.getEditModeTxt()); session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty); session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_INDIVIDUAL, regIndividual); session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_STORE_STOCK, regStock); forwardKey(request, response, "webshop.jsp.manager.cmdty.indivdual"); } else if (SIConfig.SIACTION_SEARCH.equalsIgnoreCase(actionName)) { regCmdty = new UIRegCmdty(); regIndividual = new UIIndividual(); regCmdty.init(request, urlParam); regIndividual.init(request, urlParam); if (regIndividual.validateSearch(request, databaseConnection.getConnection())) { try { regIndividual.setIndividual2(getIndividualNext(databaseConnection.getConnection(), regIndividual.getIndividual1())); regIndividual.setCtgryName(SIDBUtil.getFirstData(databaseConnection.getConnection(), "SELECT ctgryname FROM ctgrymtbl WHERE ctgrycode="+SIDBUtil.SQL2Str(regIndividual.getIndividual1()))); StringBuffer str = new StringBuffer(); str.append("SELECT ctgrycode FROM ctgryvw WHERE ctgrycode = ").append(SIDBUtil.SQL2Str(regIndividual.getIndividual1())); str.append(" AND ("); for (int i=0;i0) str.append(" OR "); str.append(" (branch LIKE ").append(SIDBUtil.SQL2Str("%~" + SIDBUtil.SQL2Like(SIConfig.LOGINCTGRY[i])+"~%")); str.append(" OR branch LIKE ").append(SIDBUtil.SQL2Str("%~" + SIDBUtil.SQL2Like(SIConfig.LOGINCTGRY[i]), ")")); } str.append(") "); regCmdty.setLoginFlg(SIDBUtil.hasData(databaseConnection.getConnection(), str.toString())); } catch (SIDBAccessException e) { e.printStackTrace(); } regStock.init(request, databaseConnection.getConnection()); regIndividual.setEditModeTxt(regCmdty.getEditModeTxt()); session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty); session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_INDIVIDUAL, regIndividual); session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_STORE_STOCK, regStock); forwardKey(request, response, "webshop.jsp.manager.cmdty.indivdual"); } else { regStock.init(request, databaseConnection.getConnection()); regIndividual.setEditModeTxt(regCmdty.getEditModeTxt()); session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty); session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_INDIVIDUAL, regIndividual); session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_STORE_STOCK, regStock); forwardKey(request, response, "webshop.jsp.manager.cmdty.indivdual"); } } else if (SIConfig.SIACTION_COPY.equalsIgnoreCase(actionName)) { regCmdty = new UIRegCmdty(); regIndividual = new UIIndividual(); regCmdty.init(request, urlParam); regIndividual.init(request, urlParam); if (regIndividual.validateIndividualSearch(request, databaseConnection.getConnection())) { try { regIndividual.individualCopy(databaseConnection.getConnection()); regIndividual.setIndividual1(""); /* regIndividual.setIndividual2(getIndividualNext(databaseConnection.getConnection(), regIndividual.getIndividual1())); StringBuffer str = new StringBuffer(); str.append("SELECT ctgrycode FROM ctgryvw WHERE ctgrycode = ").append(SIDBUtil.SQL2Str(regIndividual.getIndividual1())); str.append(" AND ("); for (int i=0;i0) str.append(" OR "); str.append(" (branch LIKE ").append(SIDBUtil.SQL2Str("%~" + SIDBUtil.SQL2Like(SIConfig.LOGINCTGRY[i])+"~%")); str.append(" OR branch LIKE ").append(SIDBUtil.SQL2Str("%~" + SIDBUtil.SQL2Like(SIConfig.LOGINCTGRY[i]), ")")); } str.append(") "); regCmdty.setLoginFlg(SIDBUtil.hasData(databaseConnection.getConnection(), str.toString())); */ } catch (Exception e) { e.printStackTrace(); } regStock.init(request, databaseConnection.getConnection()); regIndividual.setEditModeTxt(regCmdty.getEditModeTxt()); session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty); session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_INDIVIDUAL, regIndividual); session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_STORE_STOCK, regStock); forwardKey(request, response, "webshop.jsp.manager.cmdty.indivdual"); } else { regStock.init(request, databaseConnection.getConnection()); regIndividual.setEditModeTxt(regCmdty.getEditModeTxt()); session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty); session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_INDIVIDUAL, regIndividual); session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_STORE_STOCK, regStock); forwardKey(request, response, "webshop.jsp.manager.cmdty.indivdual"); } } else if (SIConfig.SIACTION_CMDTY.equalsIgnoreCase(actionName)) {// 商品明細に戻る regCmdty.setEditModeTxt(SIConfig.SIEDIT_MODE_UPDATE); session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME, regCmdty); forwardKey(request, response, "webshop.jsp.manager.cmdty.edit"); } else if (SIConfig.SIACTION_BACK2.equalsIgnoreCase(actionName)) {// 商品一覧に戻る forwardKey(request, response, "webshop.jsp.manager.cmdty.list"); } else if (SIConfig.SIACTION_BACK.equalsIgnoreCase(actionName)) {// 商品在庫に戻る forwardKey(request, response, "webshop.jsp.manager.cmdty.indivdual"); } else if (SIConfig.SIACTION_PREVIEW.equalsIgnoreCase(actionName)) {// プレビュー画面 // インスタンスの作成 UICmdtyInfo cmdtyInfoID = new UICmdtyInfo(); cmdtyInfoID.init(request, urlParam);// 7.1.1 ST0236 修正 UIRegCmdtyPreview regCmdtyPreview = new UIRegCmdtyPreview(cmdtyInfoID.getShopCodeTxt(), cmdtyInfoID.getCmdtyCodeTxt(), cmdtyInfoID.getIndividualCodeTxt()); // データの取得 regCmdtyPreview.reset(databaseConnection.getConnection()); regStock.init(request, databaseConnection.getConnection()); regCmdtyPreview.setBranchStockTxt(regStock, ""); regCmdtyPreview.setStockAmountTxt(regStock); regCmdtyPreview.init(request, urlParam);// 7.3.0 PI-NES0501 修正 session.setAttribute(SIConfig.SISESSION_MAN_CMDTY_PREVIEW_NAME, regCmdtyPreview); forwardKey(request, response, "webshop.jsp.manager.cmdty.preview"); } else {// DBへのデータ登録 regCmdty = new UIRegCmdty(); regIndividual = new UIIndividual(); regCmdty = (UIRegCmdty) session.getAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_NAME); regIndividual = (UIIndividual) session.getAttribute(SIConfig.SISESSION_MAN_CMDTY_EDIT_INDIVIDUAL); regStock = (UICmdtyStock) session.getAttribute(SIConfig.SISESSION_MAN_CMDTY_STORE_STOCK); try { if (editMode.equalsIgnoreCase(SIConfig.SIEDIT_MODE_INSERT)) { insertTableDataIndividual(databaseConnection.getConnection(), regIndividual); if (getIndividualCount(databaseConnection.getConnection(), regIndividual) == 0) { insertCmdtyctgrymtbl(databaseConnection.getConnection(), regIndividual);// 7.3.0 PI-NES0501 } insertTableDataStock(databaseConnection.getConnection(), regStock, regIndividual); setInsertLog(databaseConnection.getConnection(), regIndividual, manLogin); updateTableDataIndividual(databaseConnection.getConnection(), regIndividual); createStoreHistory(databaseConnection.getConnection(), regIndividual); request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.insert")); try { databaseConnection.getConnection().commit(); } catch (SQLException sqle) {} } else if (editMode.equalsIgnoreCase(SIConfig.SIEDIT_MODE_UPDATE)) { updateTableDataStock(databaseConnection.getConnection(), regStock, regIndividual); setUpdateLog(databaseConnection.getConnection(), regIndividual, manLogin); updateTableDataIndividual(databaseConnection.getConnection(), regIndividual); request.setAttribute(SIConfig.SIMESSAGE_ATTRIBUTE_RESULT_NAME, SIErrorFactory.getErrorMsg("manager.message.success.modify")); try { databaseConnection.getConnection().commit(); } catch (SQLException sqle) {} } else log.error("Found a not defined edit mode.editMode=" + editMode); // 結果画面への遷移 forwardKey(request, response, "webshop.jsp.manager.cmdty.individual.result"); } catch (SIDuplicateKeyException sqle) { try { databaseConnection.getConnection().rollback(); } catch (SQLException ee) {} SICustomErrors errors = new SICustomErrors(); errors.addError(new SICustomError("database.insert.duplicate")); request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors); forwardKey(request, response, "webshop.jsp.manager.cmdty.edit"); } catch (SIDBAccessException sqle) { try { databaseConnection.getConnection().rollback(); } catch (SQLException ee) {} SICustomErrors errors = new SICustomErrors(); errors.addError(new SICustomError("webshop.jsp.manager.cmdty.individual")); request.setAttribute(SIConfig.SIERROR_ATTRIBUTE_MESSAGE_KEY, errors); forwardKey(request, response, "webshop.jsp.manager.cmdty.indivdual"); } } } catch (SQLException e) { e.printStackTrace(); throw new ServletException(); } catch (NamingException e) { e.printStackTrace(); throw new ServletException(); } finally { databaseConnection.close(); } } /** * insertTableDataIndividual データベースにレコードを作成します。 * * @param lConnection DBへのコネクション * @param regIndividual 入力したデータ * @return なし * @throws SIDuplicateKeyException * @throws SIDBAccessException */ public static void insertTableDataIndividual(Connection lConnection, UIIndividual individual) throws SIDuplicateKeyException, SIDBAccessException { SISpcType lSpcType = new SISpcType(); SIInsertRec lRec = new SIInsertRec("individualtbl"); lRec.add("shopCode", individual.getShopCode());// ショップコード lRec.add("cmdtyCode", individual.getCmdtyCode());// lRec.add("individualCode", individual.getIndividualCode());// lRec.add("usedNewFlg", individual.getUsedNewFlg());// lRec.add("arrivalDate", individual.getArrivalDate());// lRec.add("processingExpence", individual.getProcessingExpence());// lRec.add("tenureofuse", individual.getTenureofuse());// lRec.add("salesSituationFlg", individual.getSalesSituationFlg());// lRec.add("soldOutDispFlg", individual.getSoldOutDispFlg());// lRec.add("campaignPriceFlg", individual.getCampaignPriceFlg());// lRec.add("inquiryFlg", individual.getInquiryFlg());// lRec.add("frontDispFlg", individual.getFrontDispFlg());// lRec.add("custCode", individual.getCustCode());// lRec.add("custName", individual.getCustName());// lRec.add("comment", individual.getComment());// lRec.add("bgPriceDispFlg", individual.getBgPriceDispFlg());// lRec.add("consignGoodsFlg", individual.getConsignGoodsFlg());// lRec.add("consignmentMemo", individual.getConsignmentMemo());// lRec.add("commodityExplanation", individual.getCommodityExplanation());// lRec.add("stockCode", individual.getStockCode());// lRec.add("purchasePrice", individual.getPurchasePrice());// lRec.add("bgPrice", individual.getBgPrice());// lRec.add("bgPriceWithoutTax", individual.getBgPriceWithoutTax());// lRec.add("specialPrice", individual.getSpecialPrice());// lRec.add("recommendationFlg", individual.getRecommendationFlg());// lRec.add("commodityBannerFile", individual.getCommodityBannerFile());// lRec.add("memberDiscountFlg", individual.getMemberDiscountFlg());// lRec.add("sellFromDate", individual.getSellFromDate());// lRec.add("endSellFlg", individual.getEndSellFlg());// lRec.add("sellToDate", individual.getSellToDate());// lRec.add("salesFromdate", individual.getSalesFromdate());// lRec.add("salesToDate", individual.getSalesToDate());// lRec.add("rsrvEnableFlg", individual.getRsrvEnableFlg());// lRec.add("rsrvAmount", individual.getRsrvAmount());// lRec.add("noStockFlg", individual.getNoStockFlg());// lRec.add("amountFlg", individual.getAmountFlg());// lRec.add("stockStatusCode", individual.getStockStatusCode());// lRec.add("soldOutDate", individual.getSoldOutDate());// lRec.add("guaranteedTerm", individual.getGuaranteedTerm());// lRec.add("wrappingFlg", individual.getWrappingFlg());// lRec.add("discountFlg", individual.getDiscountFlg());// lRec.add("salesDiscountFlg", individual.getSalesDiscountFlg());// lRec.add("adviceDispOrder", individual.getAdviceDispOrder());// lRec.add("imageName1", individual.getImageName1());// lRec.add("imageName2", individual.getImageName2());// lRec.add("imageName3", individual.getImageName3());// lRec.add("imageName4", individual.getImageName4());// lRec.add("imageName5", individual.getImageName5());// lRec.add("remarks", individual.getRemarks());// lRec.add("spcampaignflg", individual.getSpCampaignFlg());// lRec.add("nomalprice", individual.getNomalPrice());// lRec.add("alertamount", individual.getAlertAmount());// lRec.add("purchasePrice2", individual.getPurchasePrice2());// lRec.add("arrivalScheduleMsg", individual.getArrivalScheduleMsg());// lRec.add("janCodeIndividual", individual.getJanCodeIndividual()); lRec.add("consignGoodsFee", individual.getConsignGoodsFee()); lRec.add("priceDownFlg", individual.getPriceDownFlg()); lRec.add("expansionComment1Title", individual.getExpansionComment1Title()); lRec.add("expansionComment1Text", individual.getExpansionComment1Text()); lRec.add("expansionComment2Title", individual.getExpansionComment2Title()); lRec.add("expansionComment2Text", individual.getExpansionComment2Text()); lRec.add("expansionComment3Title", individual.getExpansionComment3Title()); lRec.add("expansionComment3Text", individual.getExpansionComment3Text()); lRec.add("expansionComment4Title", individual.getExpansionComment4Title()); lRec.add("expansionComment4Text", individual.getExpansionComment4Text()); // EDBTG005-00 kamata add start lRec.add("expansionComment5Title", individual.getExpansionComment5Title()); lRec.add("expansionComment5Text", individual.getExpansionComment5Text()); // EDBTG005-00 kamata add end lRec.add("setitem1", individual.getSetItem1()); lRec.add("setitem2", individual.getSetItem2()); // EDBTG003-00 elecs-matsushima add start lRec.add("imageName6", individual.getImageName6());// lRec.add("imageName7", individual.getImageName7());// lRec.add("imageName8", individual.getImageName8());// lRec.add("imageName9", individual.getImageName9());// lRec.add("imageName10", individual.getImageName10());// lRec.add("imageName11", individual.getImageName11());// // EDBTG003-00 elecs-matsushima add end lRec.add("catchcopy", individual.getCatchcopy()); // キャッチコピー lRec.add("specification", individual.getSpecification()); // 仕様 lRec.add("activeingredient", individual.getActiveingredient()); // 有効成分 lRec.add("component", individual.getComponent()); // 全成分 lRec.add("precaution", individual.getPrecaution()); // 注意事項 lRec.add("othernotice", individual.getOthernotice()); // その他 lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp"); lRec.add("InitDateTime", lSpcType);// 登録日時 // 商品マスタテーブルの更新 lRec.execute(lConnection); } /** * updateTableDataBG データベースにレコードを修正します。 * * @param lConnection DBへのコネクション * @param regCmdty 入力したデータ * @return なし * @throws SIDuplicateKeyException * @throws SIDBAccessException */ public static void updateTableDataIndividual(Connection lConnection, UIIndividual individual) throws SIDBAccessException { SIModifyRec lRec = new SIModifyRec("individualtbl"); try { lRec.addCondition("shopCode", individual.getShopCode());// ショップコード lRec.addCondition("cmdtyCode", individual.getCmdtyCode());// lRec.addCondition("individualCode", individual.getIndividualCode());// lRec.add("usedNewFlg", individual.getUsedNewFlg());// lRec.add("arrivalDate", individual.getArrivalDate());// lRec.add("processingExpence", individual.getProcessingExpence());// lRec.add("tenureofuse", individual.getTenureofuse());// lRec.add("salesSituationFlg", individual.getSalesSituationFlg());// lRec.add("soldOutDispFlg", individual.getSoldOutDispFlg());// lRec.add("campaignPriceFlg", individual.getCampaignPriceFlg());// lRec.add("inquiryFlg", individual.getInquiryFlg());// lRec.add("frontDispFlg", individual.getFrontDispFlg());// lRec.add("custCode", individual.getCustCode());// lRec.add("custName", individual.getCustName());// lRec.add("comment", individual.getComment());// lRec.add("bgPriceDispFlg", individual.getBgPriceDispFlg());// lRec.add("consignGoodsFlg", individual.getConsignGoodsFlg());// lRec.add("consignmentMemo", individual.getConsignmentMemo());// lRec.add("commodityExplanation", individual.getCommodityExplanation());// lRec.add("stockCode", individual.getStockCode());// lRec.add("purchasePrice", individual.getPurchasePrice());// lRec.add("bgPrice", individual.getBgPrice());// lRec.add("bgPriceWithoutTax", individual.getBgPriceWithoutTax());// lRec.add("specialPrice", individual.getSpecialPrice());// lRec.add("recommendationFlg", individual.getRecommendationFlg());// lRec.add("commodityBannerFile", individual.getCommodityBannerFile());// lRec.add("memberDiscountFlg", individual.getMemberDiscountFlg());// lRec.add("sellFromDate", individual.getSellFromDate());// lRec.add("endSellFlg", individual.getEndSellFlg());// lRec.add("sellToDate", individual.getSellToDate());// lRec.add("salesFromdate", individual.getSalesFromdate());// lRec.add("salesToDate", individual.getSalesToDate());// lRec.add("rsrvEnableFlg", individual.getRsrvEnableFlg());// lRec.add("rsrvAmount", individual.getRsrvAmount());// lRec.add("noStockFlg", individual.getNoStockFlg());// lRec.add("amountFlg", individual.getAmountFlg());// lRec.add("stockStatusCode", individual.getStockStatusCode());// lRec.add("soldOutDate", individual.getSoldOutDate());// lRec.add("guaranteedTerm", individual.getGuaranteedTerm());// lRec.add("wrappingFlg", individual.getWrappingFlg());// lRec.add("discountFlg", individual.getDiscountFlg());// lRec.add("salesDiscountFlg", individual.getSalesDiscountFlg());// lRec.add("adviceDispOrder", individual.getAdviceDispOrder());// lRec.add("remarks", individual.getRemarks());// lRec.add("spcampaignflg", individual.getSpCampaignFlg());// lRec.add("nomalprice", individual.getNomalPrice());// lRec.add("alertamount", individual.getAlertAmount());// lRec.add("purchasePrice2", individual.getPurchasePrice2());// lRec.add("arrivalScheduleMsg", individual.getArrivalScheduleMsg());// lRec.add("janCodeIndividual", individual.getJanCodeIndividual()); lRec.add("consignGoodsFee", individual.getConsignGoodsFee()); lRec.add("priceDownFlg", individual.getPriceDownFlg()); lRec.add("expansionComment1Title", individual.getExpansionComment1Title()); lRec.add("expansionComment1Text", individual.getExpansionComment1Text()); lRec.add("expansionComment2Title", individual.getExpansionComment2Title()); lRec.add("expansionComment2Text", individual.getExpansionComment2Text()); lRec.add("expansionComment3Title", individual.getExpansionComment3Title()); lRec.add("expansionComment3Text", individual.getExpansionComment3Text()); lRec.add("expansionComment4Title", individual.getExpansionComment4Title()); lRec.add("expansionComment4Text", individual.getExpansionComment4Text()); // EDBTG005-00 kamata add start lRec.add("expansionComment5Title", individual.getExpansionComment5Title()); lRec.add("expansionComment5Text", individual.getExpansionComment5Text()); // EDBTG005-00 kamata add end lRec.add("setitem1", individual.getSetItem1()); lRec.add("setitem2", individual.getSetItem2()); lRec.add("catchcopy", individual.getCatchcopy()); // キャッチコピー lRec.add("specification", individual.getSpecification()); // 仕様 lRec.add("activeingredient", individual.getActiveingredient()); // 有効成分 lRec.add("component", individual.getComponent()); // 全成分 lRec.add("precaution", individual.getPrecaution()); // 注意事項 lRec.add("othernotice", individual.getOthernotice()); // その他 SISpcType lSpcType = new SISpcType(); lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp"); lRec.add("InitDateTime", lSpcType);// 登録日時 // 商品マスタの更新 lRec.execute(lConnection); } catch (SIDuplicateKeyException sqle) { throw new SIDBAccessException(sqle); } } /** * insertTableDataStock データベースにレコードを作成します。 * * @param lConnection DBへのコネクション * @param individual 入力したデータ * @param Stock * @return なし * @throws SIDuplicateKeyException * @throws SIDBAccessException */ public static void insertTableDataStock(Connection lConnection, UICmdtyStock stock, UIIndividual individual) throws SIDuplicateKeyException, SIDBAccessException { int count = stock.createCnt(lConnection); for (int i = 0; i < count; i++) { SISpcType lSpcType = new SISpcType(); SIInsertRec lRec = new SIInsertRec("storestocktbl"); lRec.add("shopCode", individual.getShopCode());// ショップコード lRec.add("cmdtyCode", individual.getCmdtyCode());// lRec.add("individualCode", individual.getIndividualCode());// lRec.add("branchcode", stock.getBranchCodeTxt()[i]);// lRec.add("salespriorityflg", stock.getPriorityTxt()[i]);// lRec.add("amount", stock.getAmountTxt()[i]);// lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp"); lRec.add("InitDateTime", lSpcType);// 登録日時 lRec.add("UpdateDateTime", lSpcType); // 商品マスタテーブルの更新 lRec.execute(lConnection); } } /** * updateTableDataStock データベースにレコードを作成します。 * * @param lConnection DBへのコネクション * @param individual 入力したデータ * @param Stock * @return なし * @throws SIDuplicateKeyException * @throws SIDBAccessException */ public static void updateTableDataStock(Connection lConnection, UICmdtyStock stock, UIIndividual individual) throws SIDuplicateKeyException, SIDBAccessException { int count = stock.createCnt(lConnection); for (int i = 0; i < count; i++) { SISpcType lSpcType = new SISpcType(); SIModifyRec lRec = new SIModifyRec("storestocktbl"); lRec.addCondition("shopCode", individual.getShopCode());// ショップコード lRec.addCondition("cmdtyCode", individual.getCmdtyCode());// lRec.addCondition("individualCode", individual.getIndividualCode());// lRec.addCondition("branchcode", stock.getBranchCodeTxt()[i]);// lRec.add("salespriorityflg", stock.getPriorityTxt()[i]);// //lRec.add("amount", stock.getAmountTxt()[i]);// lSpcType = new SISpcType("TO_CHAR(now(),'YYYY/MM/DD HH24:MI:SS')::timestamp"); lRec.add("UpdateDateTime", lSpcType); // 商品マスタテーブルの更新 lRec.execute(lConnection); } } /** * insertTableDataStock データベースにレコードを作成します。 * * @param lConnection DBへのコネクション * @param Stock * @return なし * @throws SIDuplicateKeyException * @throws SIDBAccessException */ public static void insertTableDataStock(Connection lConnection, SIStoreStock stock) throws SIDuplicateKeyException, SIDBAccessException { SIInsertRec lRec = new SIInsertRec("storestocktbl"); lRec.add("shopCode", stock.getShopCode()); lRec.add("cmdtyCode", stock.getCmdtyCode()); lRec.add("individualCode", stock.getIndividualCode()); lRec.add("branchcode", stock.getBranchCode()); lRec.add("salespriorityflg", stock.getSalesPriorityFlg()); lRec.add("amount", stock.getAmount()); lRec.add("InitDateTime", stock.getInitDateTime()); lRec.add("UpdateDateTime", stock.getUpdateDateTime()); lRec.execute(lConnection); } /** * updateTableDataStock データベースにレコードを作成します。 * * @param lConnection DBへのコネクション * @param Stock * @return なし * @throws SIDuplicateKeyException * @throws SIDBAccessException */ public static void updateTableDataStock(Connection lConnection, SIStoreStock stock) throws SIDuplicateKeyException, SIDBAccessException { SIModifyRec lRec = new SIModifyRec("storestocktbl"); lRec.addCondition("shopCode", stock.getShopCode()); lRec.addCondition("cmdtyCode", stock.getCmdtyCode()); lRec.addCondition("individualCode", stock.getIndividualCode()); lRec.addCondition("branchcode", stock.getBranchCode()); lRec.add("salespriorityflg", stock.getSalesPriorityFlg()); //lRec.add("amount", stock.getAmount()); lRec.add("InitDateTime", stock.getInitDateTime()); lRec.add("UpdateDateTime", stock.getUpdateDateTime()); lRec.execute(lConnection); } public static String getIndividualNext(Connection lConnection, String header) throws SIDBAccessException { String lSql = "SELECT substr(individualcode,4,4) as num FROM individualtbl WHERE individualcode LIKE '" + header + "%' ORDER BY num DESC"; DecimalFormat exFormat = new DecimalFormat("0000"); String num = SIDBUtil.getFirstData(lConnection, lSql); String individual = ""; if (num.equals("9998") || SIUtil.isNull(num)) individual = "0000"; else individual = exFormat.format(Integer.parseInt(num) + 1); return individual; } /** * insertCmdtyctgrymtbl//7.3.0 PI-NES0501 データベースにレコードを作成します。(商品カテゴリ関連Ｍ) * * @param Connection DBへのコネクション * @param UIIndividual * @return なし * @throws SIDBAccessException * @throws SIDuplicateKeyException */ public static void insertCmdtyctgrymtbl(Connection lConnection, UIIndividual individual) throws SIDuplicateKeyException, SIDBAccessException { SIInsertRec lRec = new SIInsertRec("cmdtyctgrymtbl"); lRec.add("shopCode", individual.getShopCode()); lRec.add("cmdtycode", individual.getCmdtyCode()); lRec.add("ctgrycode", individual.getIndividual1()); lRec.execute(lConnection); if(individual.getIndividual1().startsWith("X")){// 理美容・エステ共通カテゴリの場合 //lRec = new SIInsertRec("cmdtyctgrymtbl"); //lRec.add("shopCode", individual.getShopCode()); //lRec.add("cmdtycode", individual.getCmdtyCode()); //lRec.add("ctgrycode", individual.getIndividual1()+"E"); //lRec.execute(lConnection); }else if(individual.getIndividual1().equals("DX")){ lRec = new SIInsertRec("cmdtyctgrymtbl"); lRec.add("shopCode", individual.getShopCode()); lRec.add("cmdtycode", individual.getCmdtyCode()); lRec.add("ctgrycode", "SF"); lRec.execute(lConnection); }else if(individual.getIndividual1().equals("MX")){ lRec = new SIInsertRec("cmdtyctgrymtbl"); lRec.add("shopCode", individual.getShopCode()); lRec.add("cmdtycode", individual.getCmdtyCode()); lRec.add("ctgrycode", "ZM"); lRec.execute(lConnection); } } /** * getIndividualCount//7.3.0 PI-NES0501 商品カテゴリ関連Ｍに対する対象レコードの存在 * * @param Connection * @param UIIndividual * @return 対象レコード数 * @throws SIDBAccessException * @throws SQLException */ public int getIndividualCount(Connection connection, UIIndividual individual) throws SIDBAccessException, SQLException { Statement statement = null; ResultSet resultSet = null; StringBuffer countSqlBuf = new StringBuffer();// レコード数を求める int count = 0; // レコード数 countSqlBuf.append("SELECT count(*) as cnt FROM cmdtyctgrymtbl WHERE shopCode = '0' "); countSqlBuf.append("and cmdtycode =" + SIDBUtil.SQL2Str(individual.getCmdtyCode())); countSqlBuf.append("and ctgrycode =" + SIDBUtil.SQL2Str(individual.getIndividual1())); try { statement = connection.createStatement(); resultSet = statement.executeQuery(countSqlBuf.toString()); while (resultSet.next()) { count = resultSet.getInt("cnt"); } } finally { SIDBUtil.close(resultSet, statement); } return count; } /** * createStoreHistory//7.3.0 PI-NES0601 データベースにレコードを作成します。(入庫履歴,出庫履歴,締処理,棚卸) * * @param Connection DBへのコネクション * @param UIIndividual * @return なし * @throws SIDBAccessException * @throws SIDuplicateKeyException */ public static void createStoreHistory(Connection lConnection, UIIndividual individual) throws SIDuplicateKeyException, SIDBAccessException { SIDateTime lDateTime = new SIDateTime(); SIInsertRec lRec = new SIInsertRec("storehistorytbl"); lRec.add("cmdtycode", individual.getCmdtyCode()); lRec.add("individualcode", individual.getIndividualCode()); lRec.add("storenumber", "1"); lRec.add("storebranch", "999"); lRec.add("storecharge", ""); lRec.add("amount", "0"); lRec.add("purchaseprice", "0"); lRec.add("processingexpence", "0"); lRec.add("storetype", "4"); lRec.add("comment", "商品マスタ新規作成"); lRec.add("delflg", "1"); lRec.add("initdatetime", lDateTime.getFullDate()); lRec.execute(lConnection); lRec = new SIInsertRec("shiphistorytbl"); lRec.add("cmdtycode", individual.getCmdtyCode()); lRec.add("individualcode", individual.getIndividualCode()); lRec.add("shipnumber", "1"); lRec.add("shipbranch", "999"); lRec.add("shipcharge", ""); lRec.add("amount", "0"); lRec.add("purchaseprice", "0"); lRec.add("processingexpence", "0"); lRec.add("shiptype", "4"); lRec.add("comment", "商品マスタ新規作成"); lRec.add("delflg", "1"); lRec.add("initdatetime", lDateTime.getFullDate()); lRec.execute(lConnection); String season = lDateTime.getYearStr() + "01";// 2007年以降の場合、1月の締処理をするまでは作らない（1月の締処理時に新規作成する） if (lDateTime.getYearStr().equals("2006") || SIDBUtil.hasData(lConnection, "SELECT * FROM inventorytbl WHERE season>='" + season + "'")) { SINameValue branch = new SINameValue(); Collection branchColl = SIDBUtil.getCollection(lConnection, "SELECT branchcode,branchcode FROM branchtbl"); Iterator branchIta = branchColl.iterator(); while (branchIta.hasNext()) { branch = (SINameValue) branchIta.next(); // 締処理 lRec = new SIInsertRec("stockrecordtbl"); lRec.add("cmdtycode", individual.getCmdtyCode()); lRec.add("individualcode", individual.getIndividualCode()); lRec.add("branchcode", branch.getName()); lRec.add("stockyear", lDateTime.getYearStr()); lRec.execute(lConnection); // 棚卸在庫 /* lRec = new SIInsertRec("inventorystocktbl"); lRec.add("cmdtycode", individual.getCmdtyCode()); lRec.add("individualcode", individual.getIndividualCode()); lRec.add("branchcode", branch.getName()); lRec.add("inventoryyear", lDateTime.getYearStr()); lRec.execute(lConnection); */ } } } public static void setInsertLog(Connection lConnection, UIIndividual lIndividual, SILogin manLogin) { if (SIUtil.isNull(lIndividual.getCmdtyCode()) || SIUtil.isNull(lIndividual.getIndividualCode())) return; try { SIInsertRec lRec = new SIInsertRec("flagupdatelogtbl"); /* lRec.add("cmdtycode", lIndividual.getCmdtyCode()); lRec.add("individualcode", lIndividual.getIndividualCode()); lRec.add("updateflagname", "individualtbl"); lRec.add("updateflagvalue", "insert"); lRec.add("updateuser", manLogin.getUserCode()); lRec.add("initdatetime", new SIDateTime().getFullDateTime()); lRec.execute(lConnection); lRec = new SIInsertRec("flagupdatelogtbl"); */ lRec.add("cmdtycode", lIndividual.getCmdtyCode()); lRec.add("individualcode", lIndividual.getIndividualCode()); lRec.add("updateflagname", "bgprice-first"); lRec.add("updateflagvalue", lIndividual.getBgPrice()); lRec.add("updateuser", manLogin.getUserCode()); lRec.add("initdatetime", new SIDateTime().getFullDateTime()); lRec.execute(lConnection); } catch (Exception e) { e.printStackTrace(); return; } } public static void setUpdateLog(Connection lConnection, UIIndividual lIndividual, SILogin manLogin) { if (SIUtil.isNull(lIndividual.getCmdtyCode()) || SIUtil.isNull(lIndividual.getIndividualCode())) return; try { String bgPriceBefore = SIDBUtil.getFirstData(lConnection, "SELECT bgprice FROM individualtbl WHERE individualcode="+SIDBUtil.SQL2Str(lIndividual.getIndividualCode())); SIInsertRec lRec = new SIInsertRec("flagupdatelogtbl"); lRec.add("cmdtycode", lIndividual.getCmdtyCode()); lRec.add("individualcode", lIndividual.getIndividualCode()); if (lIndividual.getBgPrice().equals(bgPriceBefore)) { lRec.add("updateflagname", "individualtbl"); lRec.add("updateflagvalue", "update"); }else{ lRec.add("updateflagname", "bgprice"); lRec.add("updateflagvalue", lIndividual.getBgPrice()); } lRec.add("updateuser", manLogin.getUserCode()); lRec.add("initdatetime", new SIDateTime().getFullDateTime()); lRec.execute(lConnection); } catch (Exception e) { e.printStackTrace(); return; } } }