Difference between revisions of "VbzCart/archive/stored procedures"
Jump to navigation
Jump to search
(New page: ==Navigation== computing: software: web: shopping carts: VbzCart: stored procedures ==Overview== This ...) |
(→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''': | + | * '''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
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