VbzCart/docs/pieces/catalog/building/2009

About

• History:
  • 2009-12-10 Transcribed from development source; updated name of source query in STAGE 2; added statement numbers for debugging purposes and changed line formatting for better readability
  • 2009-12-13 Additional UPDATE in stage 3 -- has not been tested yet! Maybe unnecessary? Things weren't working, and then by the time I rewrote them the data did somehow get updated...
  • 2010-06-27 This has been turned into a standalone script -- see VbzCart/code/maint/build-cat.php
• Notes:
  • Fields which specifically do not need to be (or should not be) always updated:
    • ID_Title is a join key (used in Template:Vbzcart/query)
    • CatSfx is a join key (used in Template:Vbzcart/query)
    • QtyMin_Stk is a permanent field; it was formerly copied over to the temp table and then copied back
    • Supp_CatNum is also a permanent field
    • PriceList may be set for individual items, and should not be reset to NULL

PHP code

This defines the array which stores the SQL commands to be executed, in order. The key to each array element is the description to be shown to the administrator when executing that command. <php>$arSQL_CtgBuild = array( /* STAGE 1: Clear/refill the two temporary tables from which cat_items is updated. */

 '1.1 clear update table 1 of 2'	=> 'DELETE FROM ctg_upd1;',
 '1.2 clear update table 2 of 2'	=> 'DELETE FROM ctg_upd2;',
 /* == Fill temp tables == */
   /* -- generated source data: */
 '1.3 fill update table 1/2 with 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: */
 '1.4 fill update table 2/2 with pre-join data'
   => '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 */
 '2.1 calculate stock quantities and status'
   => 'UPDATE ctg_upd2 AS i LEFT JOIN qryStk_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 */
 '2.2 also update status from catalog 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) */
 '3.1 update existing item status with calculated items'
   => 'UPDATE cat_items AS i
     LEFT JOIN qryCtg_Upd_join AS iu
     ON i.ID=iu.ID
     SET
       i.cntCtgDup	= i.cntCtgDup+1,
       i.CatNum	= iu.CatNum,
       i.isForSale	= iu.isForSale,
       i.isMaster	= iu.isMaster,
       i.isInPrint	= iu.isInPrint,
       i.isCloseOut	= iu.isCloseOut,
       i.isCurrent	= NULL, /* TO DO */
       i.isPulled	= FALSE, /* would this ever get set to anything else? */
       i.isDumped	= FALSE, /* same ^ */
       i.ID_ItTyp	= iu.ID_ItTyp,
       i.ID_ItOpt	= iu.ID_ItOpt,
       i.ItOpt_Descr	= iu.ItOpt_Descr,
       i.ItOpt_Sort	= iu.ItOpt_Sort,
       i.GrpCode	= iu.GrpCode,

i.GrpDescr = iu.GrpDescr, i.GrpSort = iu.GrpSort, i.ID_ShipCost = iu.ID_ShipCost, i.PriceBuy = iu.PriceBuy, i.PriceSell = iu.PriceSell, i.PriceList = IFNULL(iu.PriceList,i.PriceList), i.QtyIn_Stk = iu.QtyIn_Stk',

 '3.2 add any new calculated items'
   => 'REPLACE INTO cat_items
     SELECT *
     FROM qryCtg_Upd_join;',
  /* -- clear availability flags in any unused items */
 '3.3 clear availability flags in unused items'
   => 'UPDATE cat_items AS i
     LEFT JOIN qryCtg_Upd_join AS u

ON i.ID=u.ID SET i.isInPrint=NULL, i.isForSale=NULL WHERE u.ID IS NULL;',

  /* -- set stock and for-sale fields for *all* joinable cat_items from calculations done in ctg_upd2 */
 '3.4 set stock status fields'
   => 'UPDATE cat_items AS i LEFT JOIN ctg_upd2 AS u ON i.ID=u.ID_Item
     SET

i.isForSale=u.isForSale;',

  /* -- update inactive catalog numbers where title's catnum has changed */
 '3.5 update changed inactive catalog numbers'
   => '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 */
 '4. update cache timestamp'
   => 'UPDATE data_tables
     SET WhenUpdated=NOW() WHERE Name="cat_items";'

);</php>