VbzCart/docs/archive/code/maint/build-cat.php

About

This code works best when run interactively. There's probably a way to do this through a web browser, but for now I just run it from a command line.

Bugs

• 2010-11-15 New items do get created by the script, but status fields (notably isInPrint and isForSale) only get set when you run the process a second time. It's as if Step 3.2 isn't setting those fields, so they only get set by 3.1 after the item records are created. Need to try running this using a dummy target table instead of the real cat_items.

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

Queries Template:Vbzcart/query Template:Vbzcart/query Template:Vbzcart/query Template:Vbzcart/query Template:Vbzcart/query

Tables Template:Vbzcart Template:Vbzcart Template:Vbzcart Template:Vbzcart

History

• 2010-11-10 "data_tables" has been renamed "cache_tables"

Code

<php><?php /*

NAME: build-cat
PURPOSE: maintenance script for building catalog from scources
AUTHOR: Woozle (Nick) Staddon
REQUIRES: data.php, site.php, store.php
VERSION:
 2010-06-27 Excerpting relevant code from SpecialVbzAdmin
 2010-11-10 "data_tables" has been renamed "cache_tables"

• /

//require_once('../../libmgr.php'); require_once('../site.php'); require_once('../../data.php'); //require_once('../store.php'); //require_once('../../datamgr.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

     WHERE iu.ID IS NOT NULL',
 '3.2 add any new calculated items'
   => 'REPLACE INTO cat_items
     SELECT *,NULL AS cntCtgDup
     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 cache_tables
     SET WhenUpdated=NOW() WHERE Name="cat_items";'
 );

function VbzDb() {

 static $objDb;

   if (!isset($objDb)) {

$objDb = new clsDatabase(KS_DB_VBZCART); $objDb->Open();

   }
   return $objDb;

}

function Write($iText) {

   echo $iText;

} function WriteLn($iText) {

   echo $iText."\n";

}

function doCatBuild() {

   global $wgOut;
   global $arSQL_CtgBuild;

   $objDB = VbzDb();

   $intLine = 0;
   foreach($arSQL_CtgBuild as $descr => $sql) {

$intLine++; WriteLn($intLine.') '.$descr); $ok = $objDB->Exec($sql); if ($ok) { $intRows = $objDB->RowsAffected(); $strStat = $intRows.' row'.Pluralize($intRows).' affected'; WriteLn(' - OK - '.$strStat); } else { WriteLn(' - ERROR: '.$objDB->getError()); $objDB->ClearError(); }

   }

}

WriteLn('Building catalog...'); doCatBuild(); WriteLn('End of build.');</php>