- Purpose: log of all stock movement
- Refers to: qryStock_containers, cat_items
- 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.
- CH_OthType: appropriate type code -- see qryStock_containers for codes
- IDS_OthCont: type code plus separator (period) plus ID unique to that type
- ID_Event: where known, this is the ID of the corresponding event in event_log
- This is a sort of first step towards making this log part of the unified event log.
- WhoSystem: is optional and possibly redundant, but seems to be useful for marking imported/migrated records.
- Rename QtyBefore and QtyAfter to QtyLineBefore and QtyLineAfter.
- 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)
- 2008-12-27 More field name changes, to show grouping of local ("stock") and external ("other") locations:
- ID_Stock → ID_StkLine
- ID_Bin → ID_StkBin
- ID_Cont → ID_OthCont
- ID_Line → ID_OthLine
- 2008-12-29 Final revisions to match what I ended up with in Access for the migration:
- Created stk_history_legacy to handle legacy data properly; turned on all NOT NULLs
- 2009-12-20 Added fields WhoAdmin, WhoSystem, WhoNetwork
- 2009-12-22 Renamed When -> WhenStarted; added WhenFinished; QtyAfter and QtyBinAfter can now be NULL so we can record them when the event is complete.
- 2010-12-01 Added ID_Event field -- working towards including this in unified event logging
- 2014-05-26 Made fields DEFAULT NULL instead of NOT NULL so we don't have to know all of them before the event is started: ID_StkLine, ID_OthCont, ID_OthLine, IDS_OthCont
- Left these as NOT NULL: ID_StkBin, CH_OthType -- Bins must be explicitly created by an administrator, and we should always know what type of container the Other is.
- 2014-06-26 Lengthened What from 63 chars to 255 because descriptions were being cut off.
- 2016-02-24 Apparently I never changed the actual DB; this time, lengthened What from 63 chars to TEXT.
- No NULL values found in ID_StkLine (or in legacy data), so changing it to NOT NULL.
- Setting additional NOT NULL fields to DEFAULT NULL in order to accommodate stk_history_legacy data: QtyBefore, QtyBinBefore
- I decided it was better to tolerate incomplete event-record data than to risk losing the record altogether when an event-write attempt doesn't include all the required fields, since there's currently no guarantee that a failed write will generate an alert anywhere.
- Officially changing engine to INNODB. Migrated data from stk_history_legacy and then (what is now) `stk_history OLD 2`.
- 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).
CREATE TABLE `stk_history` ( `ID` INT NOT NULL AUTO_INCREMENT, `ID_Event` INT DEFAULT NULL COMMENT "ID of corresponding event in unified log", `ID_StkBin` INT NOT NULL COMMENT "stock item is being moved to or from this bin", `ID_StkLine` INT NOT NULL COMMENT "stk_items.ID of item in this bin being moved", `CH_OthType` CHAR NOT NULL COMMENT "type for 'other' container & line", `ID_OthCont` INT DEFAULT NULL COMMENT "[container type's table].ID of the Other place", `ID_OthLine` INT DEFAULT NULL COMMENT "[container type's line table].ID of of the Other place", `IDS_OthCont` VARCHAR(31) DEFAULT NULL COMMENT "container.IDS of the Other place", `ID_Item` INT NOT NULL COMMENT "cat_items.ID of item being moved", `QtyBefore` INT DEFAULT NULL COMMENT "quantity found in this stock line before the move", `QtyAdded` INT NOT NULL COMMENT "quantity moved (Qty fields at targ/dest may change value later)", `QtyAfter` INT DEFAULT NULL COMMENT "quantity remaining in this stock line after the move", `QtyBinBefore` INT DEFAULT NULL COMMENT "quantity of this ID_Item in the current bin before the move", `QtyBinAfter` INT DEFAULT NULL COMMENT "quantity of this ID_Item in the current bin after the move", `WhenStarted` DATETIME NOT NULL COMMENT "when the move was started", `WhenFinished` DATETIME DEFAULT NULL COMMENT "when the move was completed", `What` TEXT DEFAULT NULL COMMENT "automatic, succinct description of operation", `WhoAdmin` VARCHAR(127) NOT NULL COMMENT "VbzCart admin username", `WhoSystem` VARCHAR(127) DEFAULT NULL COMMENT "who logged into the operating system, if using non-web client app", `WhoNetwork` VARCHAR(64) NOT NULL COMMENT "network name or IP address of client machine", `Notes` TINYTEXT COMMENT "optional human-added explanatory notes", PRIMARY KEY(`ID`) ) ENGINE = INNODB;
The following was used to migrate records from what is now "stk_history OLD 2":
INSERT INTO stk_history_new ( ID_Event, ID_StkBin, ID_StkLine, CH_OthType, ID_OthCont, ID_OthLine, IDS_OthCont, ID_Item, QtyBefore, QtyAdded, QtyAfter, QtyBinBefore, QtyBinAfter, WhenStarted, WhenFinished, What, WhoAdmin, WhoSystem, WhoNetwork, Notes ) SELECT ID_Event, ID_StkBin, ID_StkLine, CH_OthType, ID_OthCont, ID_OthLine, IDS_OthCont, ID_Item, QtyBefore, QtyAdded, QtyAfter, QtyBinBefore, QtyBinAfter, WhenStarted, WhenFinished, What, WhoAdmin, 'from old stk_history', WhoNetwork, Notes FROM stk_history
There were no errors or warnings.