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
+* '''2010''' version: see {{l/vbzcart/code|maint/build-cat.php}}
+* {{l/sub|2009}} version - SQL statements executed directly from PHP code
+* {{l/sub|2008}} version - started in 2007 - a set of stored queries called from a stored procedure
+* {{l/sub|2006}} version
-* {{vbzcart/query|qryCtg_src_sub}}
+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:
-* {{vbzcart/query|qryCtg_src}}
+<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.
-* {{vbzcart/query|qryCtg_Items_forUpdJoin}}
+</blockquote>
-* {{vbzcart/query|qryCtg_Upd_join}}
+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+).
-===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").
-** There should also be a check for catnums-needing-update but lacking CatSfx
-* '''History''':
-** '''2008-12-05''' Added update of inactive catalog numbers (title's catalog number must match start of item's catalog number)
-<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;
-   /* -- update inactive catalog numbers where title's catnum has changed */
-  UPDATE cat_items AS i LEFT JOIN qryCat_Titles AS t ON i.ID_Title=t.ID
-  SET
-    i.CatNum=CONCAT_WS("-",t.CatNum,i.CatSfx)
-  WHERE (LEFT(i.CatNum,LENGTH(t.CatNum)) != t.CatNum) AND NOT isPulled;
-/* 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>