VbzCart/tables/cat items

About

• Purpose: catalog items within a title, i.e. varieties in which the title is (or was ever) available
• Fields:
  • ID_Supp ties the item directly back to its supplier. This should be set when new items are created during catalog building, and also when new "scratch" items are created during invoice entry.
  • Descr: Right now, this is just a holder for descriptions of directly-entered items. Eventually we may start storing the calculated description (from title + options) there. Until then, though, this field can be largely ignored.
  • CatSfx is basically whatever sets the item's catalog number apart from other items of the same title
  • GrpCode and GrpDescr are for differentiating within an item type, e.g. a short-sleeved t-shirt which is available in both tie-dye and one or more solid colors. GrpCode becomes part of the catalog number for items in that group, and GrpDesc is the description for that group. When displaying items for a particular title, sorting should be GrpSort,GrpCode,ItOptSort. See VbzCart catalog building for more.
  • isCurrent: If FALSE, display of item status should not make any assumptions about the correctness of the "isInPrint" flag. Instead of "out of print", display "availability unknown"; instead of "can probably get more", display "was available when last checked".
    • TO DO: How can we derive the date of the latest catalog showing this as available?
  • isPulled: TRUE = the record is being retired, but isn't ready to be recycled yet. This gives us a chance to see if the pulling was done in error. The item will still show up in listings by title. Pulling typically happens when duplicate items are created by mistake. When trying to decide which of two identical records to pull, pull the higher-numbered one if there are no other considerations.
  • isDumped: TRUE = the record is ready to be recycled.
  • QtyIn_Stk is updated by the catalog building process; later on, we will want to have a finer-grained process for updating this so the entire catalog build doesn't have to be run every time an item moves in/out of stock
  • cntCtgDup indicates which items were duplicates during the catalog building process. It may end up being unnecessary, but I put it in during debugging so I thought I should document it.
• History:
  • 2009-04-10 Tentatively eliminating qtyInStock field (adding " OLD" to name) because (a) can't find any code that uses it, and (b) it really should be calculated dynamically, otherwise it's not reliable.
  • 2009-11-19 Adding "isCurrent" field; removing "qtyInStock OLD"
  • 2009-12-11 Added "cntCtgDup"; restoring "QtyIn_Stk" field (formerly "qtyInStock") because:
    • it is used by existing code for displaying catalog pages
    • it doesn't make sense to have isForSale (which is calculated from (qtyInStock > 0) || isInPrint) and not qtyInStock; either isForSale should be eliminated, or qtyInStock should be restored
    • when we start having frequent stock adjustments it may become inefficient to recalculate qtyInStock for every stock item every time there is a change, but by that time we can write code to optimize this procedure -- i.e. only adjust qtyInStock for items which have actually been moved in or out of stock.
  • 2010-12-28 Added Descr field to allow adding item records directly from invoice data, without a title
  • 2010-12-29 Added ID_Supp field for much the same reason (need to be able to tie new Items to a Supplier without knowing Title or Department)

SQL

<mysql> DROP TABLE IF EXISTS `cat_items`;

CREATE TABLE `cat_items` (
  `ID`                INT NOT NULL AUTO_INCREMENT,
  `CatNum`    VARCHAR(64) COMMENT "catalog number: must be unique or null; can be changed",
  `isForSale`        BOOL COMMENT "available to be sold/ordered (either in stock or in print)",
  `isMaster`         BOOL COMMENT "contains multiple items; split apart when moving into stock",
  `isInPrint`        BOOL COMMENT "item is in current supplier catalog, and should be orderable",
  `isCloseOut`       BOOL COMMENT "item is available as a close-out only; limited quantities, may be gone at any moment",
  `isCurrent`        BOOL COMMENT "TRUE = isInPrint flag can be trusted; FALSE = don't assume out of print",
  `isPulled`         BOOL COMMENT "TRUE = record is garbage or a duplicate, and nothing should refer to it",
  `isDumped`         BOOL COMMENT "TRUE = pulled item which has been checked to make sure nobody is using it: ok to recycle",
  `ID_Supp`  INT NOT NULL COMMENT "cat_supp.ID",
  `ID_Title` INT NOT NULL COMMENT "cat_titles.ID",
  `ID_ItTyp`          INT COMMENT "cat_ittyps.ID: basic type of item",
  `ID_ItOpt`          INT COMMENT "cat_itopts.ID: item option (e.g. size)",
  `Descr`       VARCHAR(63) DEFAULT NULL COMMENT "item-specific description",
  `ItOpt_Descr` VARCHAR(63) DEFAULT NULL COMMENT "overrides cat_itopts.Descr if present",
  `ItOpt_Sort`  VARCHAR(63) DEFAULT NULL COMMENT "concatenation of IGroup, Option, and Item Type sorting indexes",
  `GrpCode`     VARCHAR(15) DEFAULT NULL COMMENT "grouping code - new header when this changes",
  `GrpDescr`    VARCHAR(63) DEFAULT NULL COMMENT "group description - show in header, add to item description as shown in cart",
  `GrpSort`     VARCHAR(7)  DEFAULT NULL COMMENT "sorting override (optional); sorting is by GrpSort+GrpCode",
  `CatSfx`      VARCHAR(31) DEFAULT NULL COMMENT "whatever is added to the title's catnum to make the item's catnum",
  `ID_ShipCost`         INT COMMENT "applicable shipping cost calculation data",
  `PriceBuy`   DECIMAL(9,2) COMMENT "wholesale price from supplier",
  `PriceSell`  DECIMAL(9,2) COMMENT "price to customer",
  `PriceList`  DECIMAL(9,2) COMMENT "supplier's retail list price (if available)",
  `Supp_CatNum` VARCHAR(32) COMMENT "supplier's catalog number, if available",
  `QtyIn_Stk`           INT DEFAULT NULL COMMENT "total quantity of this item found across all for-sale stock locations",
  `QtyMin_Stk`          INT COMMENT "minimum quantity to keep in stock",
  `cntCtgDup`           INT COMMENT "count of duplicates caused by catalog building",
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;</mysql>