VbzCart/docs/archive/stored procedures: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | archive
Jump to navigation Jump to search
imported>Woozle
(New page: ==Navigation== computing: software: web: shopping carts: VbzCart: stored procedures ==Overview== This ...)
 
imported>Woozle
(→‎Procedures: StkItem_LogMove() (not tested))
Line 6: Line 6:
===Inventory===
===Inventory===
...i.e. maintaining accurate stock records
...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====
====(re)count stock item in bin====
* '''Action''': Log 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.
* '''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''':
* '''Input''':
** '''iQty''': quantity counted or previously recorded
** '''iQty''': quantity counted or previously recorded
Line 15: Line 42:
*** FALSE = iQty is the (new) quantity found during this inventory count
*** FALSE = iQty is the (new) quantity found during this inventory count
<mysql>CREATE PROCEDURE StkItem_Count(IN iQty INT, IN iItem INT, iIsOld BOOL)
<mysql>CREATE PROCEDURE StkItem_Count(IN iQty INT, IN iItem INT, iIsOld BOOL)
MODIFIES SQL DATA
BEGIN
BEGIN


END</mysql>
END</mysql>
''writing in progress''
''writing in progress''

Revision as of 12:35, 2 July 2007

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

  • 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