Difference between revisions of "VbzCart/tables/stk history"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
(saving current version)
 
(these changes not yet complete or implemented; just saving)
Line 3: Line 3:
 
* '''Refers to''': {{vbzcart/table|stk_containers}}, {{vbzcart/table|cat_items}}
 
* '''Refers to''': {{vbzcart/table|stk_containers}}, {{vbzcart/table|cat_items}}
 
* '''History''':
 
* '''History''':
−
** '''2008-12-26''' (saving current version to separate page)
+
** '''2008-12-26''' Changed field names, added new fields:
 +
*** QtyFound → QtyBefore – quantity in this stock line ''before'' moving
 +
*** QtyDone → QtyAdded – quantity added to this stock line
 +
*** QtyLeft → QtyAfter – quantity in this stock line ''after'' moving
 +
*** + QtyTotBefore – total of item in this bin ''before'' move)
 +
*** + QtyTotAfter – total of item in this bin ''after'' move)
 +
* '''Rules''':
 +
** Stock items must always be moved ''to'' or ''from'' a stock line. (The source/destination can be either another stock line or a line from a package or received restock.)
 +
** The sign of QtyAdded indicates whether the movement was ''to'' or ''from''.
 
* '''Notes''':
 
* '''Notes''':
 
** The IDS_Line* fields are somewhat redundant, but I decided that redundancy was a goal: one of the purposes of history data (including stk_history) is to help reconstruct what happened when something goes wrong. Maintenance of the _stk_containers table is also somewhat redundant, but should make displaying meaningful history reports quicker (i.e. it's basically a sort of cache, as are all the _* tables).
 
** The IDS_Line* fields are somewhat redundant, but I decided that redundancy was a goal: one of the purposes of history data (including stk_history) is to help reconstruct what happened when something goes wrong. Maintenance of the _stk_containers table is also somewhat redundant, but should make displaying meaningful history reports quicker (i.e. it's basically a sort of cache, as are all the _* tables).
Line 14: Line 22:
 
   `ID_Stock` INT NOT NULL COMMENT "stk_items.ID of item being moved",
 
   `ID_Stock` INT NOT NULL COMMENT "stk_items.ID of item being moved",
 
   `ID_Item` INT /* NOT NULL */ COMMENT "cat_items.ID of item being moved",
 
   `ID_Item` INT /* NOT NULL */ COMMENT "cat_items.ID of item being moved",
−
   `QtyFound` INT /* NOT NULL */ COMMENT "number of items found in this record before the move",
+
   `QtyBefore` INT /* NOT NULL */ COMMENT "number of items found in this record before the move",
−
   `QtyDone` INT NOT NULL COMMENT "number of items moved (Qty fields at targ/dest may change value later)",
+
   `QtyAdded` INT NOT NULL COMMENT "number of items moved (Qty fields at targ/dest may change value later)",
−
   `QtyLeft` INT NOT NULL COMMENT "number of items remaining after the move",
+
   `QtyAfter` INT NOT NULL COMMENT "number of items remaining after the move",
 +
  `QtyTotBefore` INT /* NOT NULL */
 
   `When` DATETIME NOT NULL COMMENT "when the move happened",
 
   `When` DATETIME NOT NULL COMMENT "when the move happened",
 
   `IDS_ContSrce` varchar(31) /* NOT NULL */ COMMENT "_stk_containers.IDS of where the item was moved from",
 
   `IDS_ContSrce` varchar(31) /* NOT NULL */ COMMENT "_stk_containers.IDS of where the item was moved from",

Revision as of 03:19, 27 December 2008

About

  • Purpose: log of all stock movement
  • Refers to:
  1. REDIRECT Template:l/vc/table, cat_items
  • History:
    • 2008-12-26 Changed field names, added new fields:
      • QtyFound → QtyBefore – quantity in this stock line before moving
      • QtyDone → QtyAdded – quantity added to this stock line
      • QtyLeft → QtyAfter – quantity in this stock line after moving
      • + QtyTotBefore – total of item in this bin before move)
      • + QtyTotAfter – total of item in this bin after move)
  • Rules:
    • Stock items must always be moved to or from a stock line. (The source/destination can be either another stock line or a line from a package or received restock.)
    • The sign of QtyAdded indicates whether the movement was to or from.
  • Notes:
    • The IDS_Line* fields are somewhat redundant, but I decided that redundancy was a goal: one of the purposes of history data (including stk_history) is to help reconstruct what happened when something goes wrong. Maintenance of the _stk_containers table is also somewhat redundant, but should make displaying meaningful history reports quicker (i.e. it's basically a sort of cache, as are all the _* tables).
    • Commented-out bits can be included if you have no incomplete legacy data to deal with.

SQL

<mysql>DROP TABLE IF EXISTS `stk_history`;

CREATE TABLE `stk_history` ( 

 `ID` INT  NOT NULL AUTO_INCREMENT,
 `ID_Stock` INT NOT NULL COMMENT "stk_items.ID of item being moved",
 `ID_Item` INT /* NOT NULL */ COMMENT "cat_items.ID of item being moved",
 `QtyBefore` INT /* NOT NULL */ COMMENT "number of items found in this record before the move",
 `QtyAdded` INT NOT NULL COMMENT "number of items moved (Qty fields at targ/dest may change value later)",
 `QtyAfter` INT NOT NULL COMMENT "number of items remaining after the move",
 `QtyTotBefore` INT /* NOT NULL */ 
 `When` DATETIME NOT NULL COMMENT "when the move happened",
 `IDS_ContSrce` varchar(31) /* NOT NULL */ COMMENT "_stk_containers.IDS of where the item was moved from",
 `IDS_ContDest` varchar(31) /* NOT NULL */ COMMENT "_stk_containers.IDS of where the item was moved to",
 `IDS_LineSrce` varchar(31) /* NOT NULL */ COMMENT "hypothetical _stk_lines.IDS of where item came from",
 `IDS_LineDest` varchar(31) /* NOT NULL */ COMMENT "hypothetical _stk_lines.IDS of where item was moved to",
 `Notes` varchar(255) DEFAULT NULL COMMENT "optional explanatory notes",
 PRIMARY KEY(`ID`)

) ENGINE = MYISAM;</mysql>

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/tables/stk_history&oldid=11055"