VbzCart/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.

Queries

 * valign=top |
 * valign=top |
 * valign=top |
 * valign=top |
 * valign=top |
 * valign=top |

Tables

 * }
 * }
 * }
 * }
 * }

History

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

Code
<?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.');