View source for 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.
===Notes===
* Fields which specifically do ''not'' need to be (or should not be) always updated:
** '''ID_Title''' is a join key (used in {{vbzcart/query|qryCtg_Items_forUpdJoin}})
** '''CatSfx''' is a join key (used in {{vbzcart/query|qryCtg_Items_forUpdJoin}})
** '''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
{|
|-
| valign=top |
===Queries===
* {{vbzcart/query|qryCtg_src}}
* {{vbzcart/query|qryCtg_Items_forUpdJoin}}
* {{vbzcart/query|qryStk_items_remaining}}
* {{vbzcart/query|qryCtg_Upd_join}}
* {{vbzcart/query|qryCat_Titles}}
| valign=top |
===Tables===
* {{vbzcart|table|ctg_upd1}}
* {{vbzcart|table|ctg_upd2}}
* {{vbzcart|table|cat_items}}
* {{vbzcart|table|cache_tables}}
|}
===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>