VbzCart/docs/tables/stk history legacy

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 Template:Vbzcart/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 Template:Vbzcart/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 Template:Vbzcart/table.
  • 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.
  • 2016-03-03
    • Preparing to merge with Template:L/vc/table: 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.