VbzCart/docs/archive/stored procedures

Navigation

computing: software: web: shopping carts: VbzCart: stored procedures

Overview

This page is about stored procedures for accomplishing particular tasks within VbzCart. For stored procedures which are solely for maintaining cached data, see VbzCart tables.

Procedures

Inventory

...i.e. maintaining accurate stock records

move stock item

• Action: Record (in the stock log) the fact that a stock item has been moved from one place to another.
• Usage: The actual move must be accomplished by the calling routine; this just logs it. Do the move before calling this routine, as this routine looks up certain information in stk_items for the record.
• Input:
  • iStockItem: stk_items.ID of stock line item being moved (stock must always be source or destination for a move)
  • iSrceCont: _stk_containers.IDS of container FROM which stock is being moved
  • iDestCont: _stk_containers.IDS of container TO which stock is being moved
  • iNotes: any descriptive info about this move

<mysql>CREATE PROCEDURE StkItem_LogMove(IN iStockItem, IN iCatItem, IN iSrceCont, IN iDestCont, IN iNotes) MODIFIES SQL DATA BEGIN

 DECLARE idItem,intQty INT;

/* look up some things in stk_items */

 SELECT ID_Item,Qty INTO idItem,intQty FROM stk_items AS si WHERE si.ID=@iStockItem;

/* add record to the log */

 INSERT INTO stk_history(ID_Stock,ID_Item,Qty,When,IDS_ContSrce,IDS_ContDest,Notes)
 VALUES(
   iStockItem,
   @idItem,
   @intQty,
   NOW(),
   iSrceCont,
   iDestCont,
   iNotes);

END</mysql>

(re)count stock item in bin

• NOT WRITTEN YET; I ended up having to do it in Access/VBA code for now because I wasn't sure how to pass procedural calls through to MySQL from Access without using a Recordset object, and there was no point in writing it in MySQL if I couldn't use it.
• Action: Record the fact that inventory is being counted; use the stock log, and record the event as an item of stock being moved from one place back into the same place.
• Input:
  • iQty: quantity counted or previously recorded
  • iItem: ID of stock record whose count is being verified/updated
  • iIsOld:
    • TRUE = iQty is the quantity previously recorded before counting
    • FALSE = iQty is the (new) quantity found during this inventory count

<mysql>CREATE PROCEDURE StkItem_Count(IN iQty INT, IN iItem INT, iIsOld BOOL) MODIFIES SQL DATA BEGIN

END</mysql> writing in progress