VbzCart stored procedures

From HTYP, the free directory anyone can edit

Contents 1 Navigation 2 Overview 3 Procedures 3.1 Inventory 3.1.1 move stock item 3.1.2 (re)count stock item in bin

[edit] Navigation

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

[edit] 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.

[edit] Procedures

[edit] Inventory

...i.e. maintaining accurate stock records

[edit] move stock item

• NOT TESTED - see next stored proc; wrote this much before figuring out that I didn't know how to call it from Access
• 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

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

[edit] (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

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

writing in progress