VbzCart/archive/code/maint/build-cat.php - Revision history

Woozle: Woozle moved page VbzCart/code/maint/build-cat.php to VbzCart/archive/code/maint/build-cat.php

2014-12-19T02:10:14Z

Woozle moved page VbzCart/code/maint/build-cat.php to VbzCart/archive/code/maint/build-cat.php

← Older revision	Revision as of 02:10, 19 December 2014
(No difference)

Woozle: bug

2010-11-15T18:43:46Z

bug

← Older revision		Revision as of 18:43, 15 November 2010
Line 1:		Line 1:
	==About==		==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.		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===		===Notes===
	* Fields which specifically do ''not'' need to be (or should not be) always updated:		* Fields which specifically do ''not'' need to be (or should not be) always updated:

Woozle: data_tables -> cache_tables

2010-11-10T17:00:11Z

data_tables -> cache_tables

← Older revision		Revision as of 17:00, 10 November 2010
Line 22:		Line 22:
	* {{vbzcart\|table\|ctg_upd2}}		* {{vbzcart\|table\|ctg_upd2}}
	* {{vbzcart\|table\|cat_items}}		* {{vbzcart\|table\|cat_items}}
	* {{vbzcart\|table\|~~data_tables~~}}		* {{vbzcart\|table\|cache_tables}}
	\|}		\|}
			===History===
			* '''2010-11-10''' "data_tables" has been renamed "cache_tables"
	==Code==		==Code==
	<php><?php		<php><?php
Line 33:		Line 35:
	VERSION:		VERSION:
	2010-06-27 Excerpting relevant code from SpecialVbzAdmin		2010-06-27 Excerpting relevant code from SpecialVbzAdmin
			2010-11-10 "data_tables" has been renamed "cache_tables"
	*/		*/
	//require_once('../../libmgr.php');		//require_once('../../libmgr.php');
Line 120:		Line 123:
	/* STAGE 4: Housekeeping - update timestamp of cat_items so dependent tables will be recalculated */		/* STAGE 4: Housekeeping - update timestamp of cat_items so dependent tables will be recalculated */
	'4. update cache timestamp'		'4. update cache timestamp'
	=> 'UPDATE ~~data_tables~~		=> 'UPDATE cache_tables
	SET WhenUpdated=NOW() WHERE Name="cat_items";'		SET WhenUpdated=NOW() WHERE Name="cat_items";'
	);		);

Woozle: Notes from 2009 version (still applicable)

2010-11-10T16:46:47Z

Notes from 2009 version (still applicable)

← Older revision		Revision as of 16:46, 10 November 2010
Line 1:		Line 1:
	==About==		==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.		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
	{\|		{\|
	\|-		\|-

Woozle: put queries and tables in columns

2010-11-06T19:37:45Z

put queries and tables in columns

← Older revision		Revision as of 19:37, 6 November 2010
Line 1:		Line 1:
	==About==		==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.		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.
			{\|
	Queries:		\|-
			\| valign=top \|
			===Queries===
	* {{vbzcart/query\|qryCtg_src}}		* {{vbzcart/query\|qryCtg_src}}
	* {{vbzcart/query\|qryCtg_Items_forUpdJoin}}		* {{vbzcart/query\|qryCtg_Items_forUpdJoin}}
Line 8:		Line 10:
	* {{vbzcart/query\|qryCtg_Upd_join}}		* {{vbzcart/query\|qryCtg_Upd_join}}
	* {{vbzcart/query\|qryCat_Titles}}		* {{vbzcart/query\|qryCat_Titles}}
			\| valign=top \|
	Tables:		===Tables===
	* {{vbzcart\|table\|ctg_upd1}}		* {{vbzcart\|table\|ctg_upd1}}
	* {{vbzcart\|table\|ctg_upd2}}		* {{vbzcart\|table\|ctg_upd2}}
	* {{vbzcart\|table\|cat_items}}		* {{vbzcart\|table\|cat_items}}
	* {{vbzcart\|table\|data_tables}}		* {{vbzcart\|table\|data_tables}}
			\|}
	==Code==		==Code==
	<php><?php		<php><?php

Woozle: queries and tables used

2010-11-06T19:30:52Z

queries and tables used

@@ Line 1: / Line 1: @@
 ==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.
 ==Code==
 <php><?php

Woozle: Created page with '==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. ==Code==
2010-11-06T19:23:38Z

Created page with '==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. ==Code== <php><?php…'

New page
==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.
==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
/
//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 data_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>