Difference between revisions of "VbzCart/pieces/catalog/building"

Latest revision as of 11:07, 12 June 2017

Version

This is a tricky process which has been revised multiple times.

2017 version - 2016 code apparently never really got finished, and the Items table needs some flag changes
2016 version - webified version of 2010, with rules finally documented
2010 version: see maint/build-cat.php
2009 version - SQL statements executed directly from PHP code
2008 version - started in 2007 - a set of stored queries called from a stored procedure
2006 version

It's interesting to note that I originally thought of SCM Groups as groups of Titles -- an explanation from the VbzCart/tables page on 2008-02-17 (and only removed on 2016-02-01) says:

The key concept here is "catalog title groups" (CTGs). A CTG is a set of titles which are all available with the same set of options (e.g. sizes) at the same prices (e.g. $10 S, $11 M-XL, $12 2XL). The final vbz catalog (cat_items) is the result of a sort of vector multiplication of Titles (cat_titles) and CTGs plus any items in stock.

This conceptualization no longer works, as a SCM Title may have one or more SCM Groups -- e.g. if it has a set of standard sizes (like S-3X) but is also available in super-large sizes (4X+).

Difference between revisions of "VbzCart/pieces/catalog/building"

Latest revision as of 11:07, 12 June 2017

Version

Navigation menu

Search

@@ Line 1: / Line 1: @@
-==Navigation==
-[[VbzCart]]: [[VbzCart catalog building|catalog building]]
 ==Version==
-This is being revised yet again for the 2007 VbzCart-MySQL migration project; the previous version is here: [[vbzwiki:VbzCart catalog building 2006|VbzCart catalog building 2006]].
+This is a tricky process which has been revised multiple times.
-==Process==
-This is only a draft, as the process is still being worked out. Right now this process does not "recycle" junked records in cat_items; perhaps that was a bad idea to begin with, or perhaps some other use can be found for junked records that isn't part of catalog-building. There probably should be some process which removes junked records from use so they aren't taking up space and CPU cycles.
-# Update the list of all catalog-based items ([calc Items Updates saved])
-#* [[#(Step 1) qryVb_Items_updates_append]] -- Add any new items to [calc Items Updates saved]
-#** [[#(Step 1a) qryVb_Items_updates_new]] -- in-print items that are new
-#*** [[#(Step 1aa) qryVb_Items_updates]] -- all the items currently available from suppliers (in print)
-#** [[#(Step 1b) qryVb_Items_updates_copy_fields]]
-# For all items in [calc Items Updates saved] where ID_Item IS NULL, create new records in cat_items and update the corresponding records in [calc Items Updates saved]
-#* [[#(Step 2) qryVb_Items_results_append]] -- adds new cat_items rows for any calculated items not already cross-referenced. It doesn't cross-reference them, however. That is done by the next query.
-#** [[#(Step 2a) qryVb_Items_results]]
-# Fill in any blank ID_Item fields in [calc Items Updates saved] records:
-#* [[#(Step 3) qryVb_Items_updates_index_new]] -- plugs the Item ID back into the calculated temp table. It assumes (1) 1:1 correspondence between Item IDs and catalog numbers, and (2) catalog numbers won't be changed during the course of a build (though they can change in general).
-# Copy calculated item data over to [cat_items]:
-#* [[#(Step 4) qryVb_Items_results_update]]
-# Final flag updates:
-#* [[#(Step 5) qryVb_Items_update_final]]
-#** Clear isInPrint flag for cat_items not found in calculated in-print listing
-#** Set isForSale for cat_items with stock for sale
-#** Update stock quantities for all cat_items
-# Housekeeping: mark table as updated so cached tables get recalculated
-#* [[#(Step 6) qryVb_Items_mark_updated]]
-==SQL - queries - 2008==
+* {{l/sub|2017}} version - 2016 code apparently never really got finished, and the Items table needs some flag changes
--03-15: Completely rewriting the building sequence again...
+* {{l/sub|2016}} version - webified version of 2010, with rules finally documented
-===qryCtg_src_sub===
+* '''2010''' version: see {{l/vbzcart/code|maint/build-cat.php}}
-<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_src_sub AS
+* {{l/sub|2009}} version - SQL statements executed directly from PHP code
-SELECT
+* {{l/sub|2008}} version - started in 2007 - a set of stored queries called from a stored procedure
-    NULLIF(CONCAT_WS('/',g.Code,gt.GroupCode,o.CatKey),'') AS CatSfx,
+* {{l/sub|2006}} version
-    c.isCloseOut,
-    gt.ID AS ID_CTG_Title,
-    gi.ID AS ID_CTG_Item,
-    gt.ID_Group AS ID_CTG_Group,   /* mainly for debugging */
-    gt.ID_Source AS ID_CTG_Source, /* mainly for debugging */
-    gt.ID_Title,
-    gi.ID_ItTyp,
-    gi.ID_ItOpt,
-    gi.ID_ShipCost,
-    gi.PriceBuy,
-    gi.PriceSell,
-    gi.PriceList,
-    IFNULL(gi.Descr,CONCAT_WS(' / ',gt.GroupDescr,o.Descr)) AS ItOpt_Descr_part,
-    it.NameSng,
-    gi.Descr AS GrpItmDescr,
-    gt.GroupDescr AS TitleGroupDescr,
-    o.Descr AS OptionDescr,
-    CONCAT(g.Sort,it.Sort,o.Sort) AS ItOpt_Sort,
-    gt.GroupCode AS GrpCode,
-    gt.GroupDescr AS GrpDescr,
-    gt.GroupSort AS GrpSort
-  FROM
-  (
-    (
-      (
-        (
-          qryCtg_Titles_active AS gt LEFT JOIN ctg_groups AS g ON g.ID=gt.ID_Group
-         ) LEFT JOIN qryCtg_Sources_active AS c ON gt.ID_Source=c.ID
-       ) LEFT JOIN qryCtg_Items_active AS gi ON gi.ID_Group=g.ID
-     ) LEFT JOIN cat_ioptns AS o ON gi.ID_ItOpt=o.ID
-   ) LEFT JOIN cat_ittyps AS it ON gi.ID_ItTyp = it.ID
-WHERE (c.ID IS NOT NULL) AND (gi.ID IS NOT NULL);</mysql>
-<section end=sql />
-===qryCtg_src===
+It's interesting to note that I originally thought of SCM Groups as groups ''of Titles'' -- an explanation from the [[VbzCart/tables]] page on 2008-02-17 (and only removed on 2016-02-01) says:
-<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_src AS
+<blockquote>The key concept here is "catalog title groups" (CTGs). A CTG is a set of titles which are all available with the same set of options (e.g. sizes) at the same prices (e.g. $10 S, $11 M-XL, $12 2XL). The final vbz catalog (cat_items) is the result of a sort of vector multiplication of Titles (cat_titles) and CTGs plus any items in stock.
-SELECT
+</blockquote>
-  b.*,
+This conceptualization no longer works, as a SCM Title may have one or more SCM Groups -- e.g. if it has a set of standard sizes (like S-3X) but is also available in super-large sizes (4X+).
-  CONCAT_WS('-',b.ID_Title,b.CatSfx) AS IDS_Item,
-  CONCAT_WS('-',t.CatNum,b.CatSfx) AS CatNum,
-  IFNULL(NULLIF(b.ItOpt_Descr_part,""),b.NameSng) AS ItOpt_Descr
-FROM qryCtg_src_sub AS b LEFT JOIN qryCat_Titles AS t ON b.ID_Title=t.ID;</mysql>
-<section end=sql />
-* (2008-04-02) '''b.ItOpt_Descr_part''' apparently often comes up as "" rather than NULL, so we have to convert blanks to nulls before testing for null.
-===qryCtg_Items_forUpdJoin===
-<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_Items_forUpdJoin AS
-SELECT
-  ID AS ID_Item,
-  CONCAT_WS('-',ID_Title,CatSfx) AS IDS_Item,
-  FALSE AS isForSale,
-AS QtyInStock,   /* this will be calculated shortly */
-  Supp_CatNum,       /* PRESERVE */
-  QtyMin_Stk         /* PRESERVE */
-FROM cat_items WHERE (NOT isDumped) AND (ID_Title IS NOT NULL);</mysql>
-<section end=sql />
-===qryCtg_Upd_join===
-This recordset represents all the catalog items for which there is currently an active source (in print or closeout, but not stock). It should not need to preserve anything from the current record in cat_items EXCEPT Supp_CatNum and QtyMin_Stock. (Maybe these should later be moved to a separate table.)
-<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_Upd_join AS
-SELECT
-  c.ID_Item AS ID,
-  s.CatNum AS CatNum,
-  TRUE AS isForSale,  /* has catalog source, so is for sale */
-  FALSE AS isMaster,  /* master items are entered manually */
-  c.qtyInStock,       /* this has just been recalculated from stock */
-  NOT s.isCloseOut AS isInPrint,
-  s.isCloseOut,
-  FALSE AS isPulled,
-  FALSE AS isDumped,
-  s.ID_Title,
-  s.ID_ItTyp,
-  s.ID_ItOpt,
-  s.ItOpt_Descr,
-  s.ItOpt_Sort,
-  s.GrpCode,
-  s.GrpDescr,
-  s.GrpSort,
-  s.CatSfx,
-  s.ID_ShipCost,
-  s.PriceBuy,
-  s.PriceSell,
-  s.PriceList,
-  c.Supp_CatNum,
-  c.QtyMin_Stock
-FROM ctg_upd1 AS s LEFT JOIN ctg_upd2 AS c ON s.IDS_Item=c.IDS_Item;</mysql>
-<section end=sql />
-* The CTG CatNum (s.CatNum) should never be NULL; this indicates something is wrong in the sourcing process, because how can you have an active source with no title CatNum?
-===qryCtg_src_dups===
-Returns all duplicate records (each IDS_Item must be unique) in the generated source data. This should be performed as a check on data integrity before writing to cat_items; any duplicates may indicate a problem (typically, entering items from a new catalog but forgetting to mark the old one as superceded), and should be inspected. (Not sure if conflicts will prevent the update from completing; to be determined later.)
-<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_src_dups AS
-SELECT IDS_Item, COUNT(IDS_Item) AS cntRows FROM qryCtg_src GROUP BY IDS_Item HAVING cntRows>1;</mysql>
-<section end=sql />
-===ctg_upd* data building===
-This is mostly working. A key issue seems to be the correct setting of isForSale. '''When we get to Stage 2''':
-* '''ctg_upd1''' has all the generated data from active sources; these items are all for sale
-* '''ctg_upd2''' has joinable data from cat_items, i.e. anything which might be used (or revived) as a saleable item, i.e. anything where ID_Title is set. This includes a large number of items which will ''not'' be used. However, only items present in ctg_upd1 will cause a direct overwrite of cat_items, so we only need to save fields from cat_items which ''don't'' get set directly from ctg_upd1 (i.e. sourced items); items that are discontinued but in stock will not be overwritten, they will just have their stock status updated. This happens by setting those fields properly in ctg_upd2 (clear + calculate), then directly overwriting those fields in cat_items from the values in ctg_upd2. (Thus removing the need to clear those fields in cat_items even temporarily. This is the only reason we need ctg_upd2.)
-The duplicate key count on ctg_upd2 should indicate how many different items share the same calculated IDS_Item. Ultimately, none of these should be items that are also for sale; perhaps a query should reality-check this after the update (for-sale items using duplicate IDSs), and warn the user if any records are found -- which would indicate a bug in the generation process.
-'''During Stage 2''', we first calculate (from scratch) quantities in stock and set the "for sale" flag for any items in stock (these go in ctg_upd2). Then we also set the "for sale" flag for any items which happen to have active sources, i.e. are in ctg_upd1.
-'''TO DO''': at some point, we need a way to test for duplicate IDS_Item keys before the data gets put into ctg_upd1. Duplicate keys will probably cause this procedure to fail at that point, but I don't know if it will show an informative message if this happens or just fail silently (as it is, even when it completes successfully it says "query cancelled").
-<mysql>DROP PROCEDURE IF EXISTS `doCtgUpdate`;
-CREATE PROCEDURE doCtgUpdate() MODIFIES SQL DATA
-BEGIN
-/* STAGE 1: Clear/refill the two temporary tables from which cat_items is updated. */
-  /* == Clear temp tables == */
-  DELETE FROM ctg_upd1;
-  DELETE FROM ctg_upd2;
-  /* == Fill temp tables == */
-    /* -- generated source data: */
-  INSERT INTO ctg_upd1 SELECT
-    CatSfx, isCloseOut, ID_CTG_Title, ID_CTG_Item, ID_Title, ID_ItTyp, ID_ItOpt, ID_ShipCost, PriceBuy, PriceSell, PriceList,
-    ItOpt_Descr_part, NameSng, GrpItmDescr, TitleGroupDescr, OptionDescr, ItOpt_Sort,
-    GrpCode, GrpDescr, GrpSort, IDS_Item, CatNum, ItOpt_Descr
-  FROM qryCtg_src;
-    /* -- existing catalog data indexed for JOINing: */
-  INSERT INTO ctg_upd2 SELECT *, 0 AS cntDups
-  FROM qryCtg_Items_forUpdJoin
-  ON DUPLICATE KEY UPDATE cntDups=cntDups+1;
-/* STAGE 2: Calculate stock numbers and set isForSale flag */
-   /* -- calculate stock quantities; set for-sale flag if in stock */
-  UPDATE ctg_upd2 AS i LEFT JOIN v_stk_items_remaining AS s ON i.ID_Item=s.ID_Item
-  SET
-    i.qtyInStock=s.QtyForSale,
-    i.isForSale=(s.QtyForSale > 0);
-   /* -- also set for-sale flag if available from source */
-  UPDATE ctg_upd2 AS i LEFT JOIN ctg_upd1 AS u ON i.IDS_Item=u.IDS_Item
-  SET i.isForSale=i.isForSale OR (u.IDS_Item IS NOT NULL);
-/* STAGE 3: Update cat_items */
-   /* -- replace sourced items in cat_items from CTG data (except for fields saved in ctg_upd2) */
-  REPLACE INTO cat_items
-  SELECT *
-  FROM qryCtg_Upd_join;
-   /* -- set stock and for-sale fields for *all* joinable cat_items from calculations done in ctg_upd2 */
-  UPDATE cat_items AS i LEFT JOIN ctg_upd2 AS u ON i.ID=u.ID_Item
-  SET
-    i.isForSale=u.isForSale,
-    i.qtyInStock=u.qtyInStock;
-/* STAGE 4: Housekeeping - update timestamp of cat_items so dependent tables will be recalculated */
-  UPDATE data_tables SET WhenUpdated=NOW() WHERE Name="cat_items";
-END;
-</mysql>
-<section end=sql />
-* If this doesn't work as part of a script, check the actual syntax by editing the procedure in MySQL Query Browser.
-* REPLACE INTO is MySQL-specific syntax. The same thing could be accomplished in other more standard ways, but in the interest of time this seemed like the best way to get things working.
-===ctg build verifying===
-This displays any duplicate IDS_Items (ID_Title + CatSfx) found in currently active titles in cat_items. This is probably due to old records whose CatSfx fields weren't set (or weren't set properly) being (re)activated by being found in stock.
-<mysql>CREATE OR REPLACE VIEW qryCtgCk_dup_keys AS
-SELECT
-  i.CatNum,
-  i.isInPrint,
-  u.*
-FROM ctg_upd2 AS u LEFT JOIN cat_items AS i ON u.ID_Item=i.ID WHERE cntDups and u.isForSale;</mysql>
-==SQL - queries==
-These are from the MS Access 97 version, gradually being honed down for migration to MySQL
-===(Step 1a) qryVb_Items_updates_new===
-<sql>SELECT c.*
-FROM qryCtg_Items_updates_joinable AS c LEFT JOIN ctg_updates AS s
-ON c.IDS_Item=s.IDS_Item
-WHERE s.IDS_Item IS NULL;</sql>
-===(Step 1) qryVb_Items_updates_append===
-<sql>INSERT INTO ctg_updates (
-  ID_Item,
-  IDS_Item,
-  CatNum,
-  CatSfx,
-  ID_Title,
-  ID_CTG_Group,
-  ID_CTG_Title,
-  ID_CTG_Item,
-  ID_ItTyp,
-  ID_ItOpt )
-SELECT
-AS ID_Item,
-  IDS_Item,
-  CatNum,
-  CatSfx,
-  ID_Title,
-  ID_TGroup,
-  ID_TGTitle,
-  ID_TGItem,
-  ID_ItTyp,
-  ID_ItOpt
-FROM qryVb_Items_Updates_new;
-</sql>
-===(Step 2) qryVb_Items_results_append===
-This adds new cat_items rows for any calculated items not already cross-referenced. It doesn't cross-reference them, however. That is done by the next query.
-<sql>INSERT INTO cat_items (
-  CatNum,
-  isForSale,
-  isMaster,
-  isInPrint,
-  isCloseOut,
-  ID_ItTyp,
-  ID_ItOpt,
-  ItOpt_Descr,
-  ItOpt_Sort,
-  ID_ShipCost,
-  PriceBuy,
-  PriceSell,
-  PriceList )
-SELECT
-  r.CatNum,
-  r.isForSale,
-  FALSE AS isMaster,
-  r.isInPrint,
-  r.isCloseOut,
-  r.ID_ItTyp,
-  r.ID_ItOpt,
-  r.ItOpt_Descr,
-  r.ItOpt_Sort,
-  r.ID_ShipCost,
-  r.PriceBuy,
-  r.PriceSell,
-  r.PriceList
-FROM qryCtg_Items_updates AS r LEFT JOIN cat_items AS i ON r.ID_Item=i.ID
-WHERE i.ID IS NULL;
-</sql>
-===(Step 3) qryVb_Items_updates_index_new===
-This plugs the Item ID back into the calculated temp table. It assumes (1) 1:1 correspondence between Item IDs and catalog numbers, and (2) catalog numbers won't be changed during the course of a build (though they can change in general).
-<sql>UPDATE
-  ctg_updates AS s LEFT JOIN
-  cat_items AS i
-ON i.CatNum=s.CatNum
-SET
-  s.ID_Item = i.ID,
-  isForSale = FALSE,
-  isInPrint = FALSE
-WHERE
-  s.ID_Item IS NULL;
-</sql>
-===(Step 4) qryVb_Items_results_update===
-<sql>UPDATE
-  qryCtg_build AS r LEFT JOIN
-  cat_items AS i
-ON
-  r.ID_Item=i.ID
-SET
-  i.CatNum = r.CatNum,
-  i.isForSale = r.isForSale,
-  i.isInPrint = r.isInPrint,
-  i.isCloseOut = r.isCloseOut,
-  i.isPulled = FALSE,
-  i.isDumped = FALSE,
-  i.ID_ItTyp = r.ID_ItTyp,
-  i.ID_ItOpt = r.ID_ItOpt,
-  i.ItOpt_Descr = iif(r.ItOpt_Descr IS NULL,i.ItOpt_Descr,r.ItOpt_Descr),
-  i.ItOpt_Sort = r.ItOpt_Sort,
-  i.ID_ShipCost = r.ID_ShipCost,
-  i.PriceBuy = r.PriceBuy,
-  i.PriceSell = r.PriceSell,
-  i.PriceList = r.PriceList,
-  i.GrpCode = r.GrpCode,
-  i.GrpDescr = r.GrpDescr,
-  i.GrpSort = r.GrpSort,
-  i.CatSfx = r.CatSfx
-WHERE r.ID_Item IS NOT NULL;
-</sql>
-Changes:
-* Must clear isPulled and isDumped, in case pulled items are returned to service
-===(Step 5) qryVb_Items_update_final===
-* Clear isInPrint flag for cat_items not found in calculated in-print listing
-* Set isForSale for cat_items with stock for sale
-* Update stock quantities for all cat_items
-<sql>UPDATE
-  (cat_items AS i LEFT JOIN qryCtg_build AS r ON r.ID_Item=i.ID)
-  LEFT JOIN v_stk_items_remaining AS s ON s.ID_Item=i.ID
-SET
-  i.isInPrint = nz(r.isInPrint),
-  i.isCloseOut = nz(r.isCloseOut),
-  i.isForSale = nz(r.isInPrint)
-      OR nz(r.isCloseOut)
-      OR (nz(s.QtyForSale) > 0),
-  i.qtyInStock = nz(s.QtyForSale);
-</sql>
-===(Step 6) qryVb_Items_mark_updated===
-<sql>UPDATE
-  data_tables
-SET WhenUpdated=Now()
-WHERE Name="cat_items"</sql>