VbzCart/tables/stk history legacy
Jump to navigation
Jump to search
About
- Status Deprecated.
- This table is no longer needed, and is not really something that needs to be part of VbzCart proper. Its only use, other than dealing with imported data from older versions of VbzCart, would be an aid in importing from other systems -- and it would probably be better just to hand-craft any intermediate tables for that purpose.
- Purpose: For accommodating old stock history data (possibly imported from another application) where some rows don't meet the stricter requirements of
- REDIRECT Template:l/vc/table. This table should have all the same fields as that table, but some fields may have looser requirements and additional fields may be added.
- Usage: Feel free to NOT-NULLify any existing fields, or even add additional fields to preserve your old data. If you have no old data to deal with, this table is unnecessary; you can either leave it empty, or modify
- REDIRECT Template:l/vc/query not to require it.
- History:
- 2008-12-29 Created so that old data could be accommodated without compromising integrity of new data added to stk_history.
- 2016-03-02
- Found that there were no NULL values in my data for ID_StkBin, ID_StkLine, CH_OthType, IDS_OthCont, QtyAdded -- so made them NOT NULL
- Actually, QtyAdded was already NOT NULL in my actual table... just making that official, in this table which should be going away shortly.
- Found that there were no NULL values in my data for ID_StkBin, ID_StkLine, CH_OthType, IDS_OthCont, QtyAdded -- so made them NOT NULL
- 2016-03-03
- Preparing to merge with stk_history: set NULL ID_Item fields by looking up stk_lines.ID_Item from ID_StkLine. ID_Item can now be NOT NULL, as in stk_history.
- Merged with stk_history into updated (INNODB) stk_history table, and deprecated.
SQL
<mysql>CREATE TABLE `stk_history_legacy` (
`ID` INT NOT NULL AUTO_INCREMENT, `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 /*NOT NULL*/ COMMENT "[container type's table].ID of the Other place", `ID_OthLine` INT /*NOT NULL*/ COMMENT "[container type's line table].ID of of the Other place", `IDS_OthCont` VARCHAR(31) NOT NULL COMMENT "container.IDS of the Other place", `ID_Item` INT NOT NULL COMMENT "cat_items.ID of item being moved", `QtyBefore` INT /*NOT 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 /*NOT NULL*/ COMMENT "quantity remaining in this stock line after the move", `QtyBinBefore` INT /*NOT NULL*/ COMMENT "quantity of this ID_Item in the current bin before the move", `QtyBinAfter` INT /*NOT NULL*/ COMMENT "quantity of this ID_Item in the current bin after the move", `When` DATETIME /*NOT NULL*/ COMMENT "when the move happened", `What` VARCHAR(63) DEFAULT NULL COMMENT "brief automatic description of operation", `Notes` TINYTEXT DEFAULT NULL COMMENT "optional human-added explanatory notes",
/* fields specific to vbz.net data migration -- these may be omitted for normal usage */
`oIDS_ContSrce` VARCHAR(31), `oIDS_LineSrce` VARCHAR(31), `oIDS_ContDest` VARCHAR(31), `oIDS_LineDest` VARCHAR(31), `oID_StkBin` INT, `oID_StkLine` INT, `oQtyFnd` INT, `oQtyDone` INT, `oQtyLeft` INT, `wasSrce` BIT COMMENT "how the data was handled at migration: TRUE = stock is source", PRIMARY KEY(`ID`)
) ENGINE = MYISAM;</mysql>
Migration
This was used to copy stk_history_legacy data into stk_history_new, to be renamed stk_history: <mysql>INSERT INTO stk_history_new (
ID_StkBin, ID_StkLine, CH_OthType, ID_OthCont, ID_OthLine, IDS_OthCont, ID_Item, QtyBefore, QtyAdded, QtyAfter, QtyBinBefore, QtyBinAfter, WhenStarted, What, WhoAdmin, WhoSystem, WhoNetwork, Notes )
SELECT
ID_StkBin, ID_StkLine, CH_OthType, ID_OthCont, ID_OthLine, IDS_OthCont, ID_Item, QtyBefore, QtyAdded, QtyAfter, QtyBinBefore, QtyBinAfter, `When`, What, 'woozle', 'from stk_history_legacy', '50.111.67.243', Notes
FROM stk_history_legacy </mysql> Note that there were some NULL values in When, which were translated into "zero date" values in stk_history_new. At the moment, I can't think of any way to recover those timestamps.