VbzCart/pieces/catalog/building/2009 - Revision history

Woozle: Woozle moved page VbzCart/pieces/catalog/local/building/2009 to VbzCart/pieces/catalog/building/2009 over redirect: this actually applies to both local and supplier

2016-02-01T02:15:21Z

Woozle moved page VbzCart/pieces/catalog/local/building/2009 to VbzCart/pieces/catalog/building/2009 over redirect: this actually applies to both local and supplier

← Older revision	Revision as of 02:15, 1 February 2016
(No difference)

Woozle: Woozle moved page VbzCart/pieces/catalog/building/2009 to VbzCart/pieces/catalog/local/building/2009: disambiguation

2016-01-08T17:53:04Z

Woozle moved page VbzCart/pieces/catalog/building/2009 to VbzCart/pieces/catalog/local/building/2009: disambiguation

← Older revision	Revision as of 17:53, 8 January 2016
(No difference)

Woozle: Woozle moved page VbzCart/catalog/building/2009 to VbzCart/pieces/catalog/building/2009

2015-01-25T19:15:00Z

Woozle moved page VbzCart/catalog/building/2009 to VbzCart/pieces/catalog/building/2009

← Older revision	Revision as of 19:15, 25 January 2015
(No difference)

Woozle: link to 2010 version

2010-11-10T16:35:29Z

link to 2010 version

← Older revision		Revision as of 16:35, 10 November 2010
Line 3:		Line 3:
	** '''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-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...		** '''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''':		* '''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: additional UPDATE in stage 3

2009-12-13T14:33:37Z

additional UPDATE in stage 3

← Older revision		Revision as of 14:33, 13 December 2009
Line 1:		Line 1:
	==About==		==About==
	* '''Requires''': {{vbzcart/query\|qryCtg_src}}, {{vbzcart/query\|qryCtg_Items_forUpdJoin}}, {{vbzcart/query\|qryStk_items_remaining}}, {{vbzcart/query\|qryCtg_Upd_join}}, {{vbzcart/query\|qryCat_Titles}}
	* '''History''':		* '''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-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...
			* '''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
	==PHP code==		==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.		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.
Line 35:		Line 42:
	/* STAGE 3: Update cat_items */		/* STAGE 3: Update cat_items */
	/* -- replace sourced items in cat_items from CTG data (except for fields saved in ctg_upd2) */		/* -- replace sourced items in cat_items from CTG data (except for fields saved in ctg_upd2) */
	'3.1 update item status with calculated items'		'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		=> 'REPLACE INTO cat_items
	SELECT *		SELECT *
	FROM qryCtg_Upd_join;',		FROM qryCtg_Upd_join;',
	/* -- clear availability flags in any unused items */		/* -- clear availability flags in any unused items */
	'3.2 clear availability flags in unused items'		'3.3 clear availability flags in unused items'
	=> 'UPDATE cat_items AS i		=> 'UPDATE cat_items AS i
	LEFT JOIN qryCtg_Upd_join AS u		LEFT JOIN qryCtg_Upd_join AS u
	ON i.ID=u.ID		ON i.ID=u.ID
	SET i.isInPrint=NULL, i.isForSale=NULL WHERE u.ID IS NULL;',		SET i.isInPrint=NULL, i.isForSale=NULL WHERE u.ID IS NULL;',
	~~/* NOT FINISHED -- isn't there one more flag? */~~
	/* -- set stock and for-sale fields for all joinable cat_items from calculations done in ctg_upd2 */		/* -- set stock and for-sale fields for all joinable cat_items from calculations done in ctg_upd2 */
	'3.3 set stock status fields'		'3.4 set stock status fields'
	=> 'UPDATE cat_items AS i LEFT JOIN ctg_upd2 AS u ON i.ID=u.ID_Item		=> 'UPDATE cat_items AS i LEFT JOIN ctg_upd2 AS u ON i.ID=u.ID_Item
	SET		SET
	i.isForSale=u.isForSale;',		i.isForSale=u.isForSale;',
	/* -- update inactive catalog numbers where title's catnum has changed */		/* -- update inactive catalog numbers where title's catnum has changed */
	'3.4 update changed inactive catalog numbers'		'3.5 update changed inactive catalog numbers'
	=> 'UPDATE cat_items AS i LEFT JOIN qryCat_Titles AS t ON i.ID_Title=t.ID		=> 'UPDATE cat_items AS i LEFT JOIN qryCat_Titles AS t ON i.ID_Title=t.ID
	SET		SET
	i.CatNum=CONCAT_WS("-",t.CatNum,i.CatSfx)		i.CatNum=CONCAT_WS("-",t.CatNum,i.CatSfx)
	WHERE (LEFT(i.CatNum,LENGTH(t.CatNum)) != t.CatNum) AND NOT isPulled;',		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 */
	/* 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 data_tables
	SET WhenUpdated=NOW() WHERE Name="cat_items";'		SET WhenUpdated=NOW() WHERE Name="cat_items";'
	);</php>		);</php>

Woozle: doc: requirements

2009-12-11T17:25:54Z

doc: requirements

← Older revision		Revision as of 17:25, 11 December 2009
Line 1:		Line 1:
	==About==		==About==
			* '''Requires''': {{vbzcart/query\|qryCtg_src}}, {{vbzcart/query\|qryCtg_Items_forUpdJoin}}, {{vbzcart/query\|qryStk_items_remaining}}, {{vbzcart/query\|qryCtg_Upd_join}}, {{vbzcart/query\|qryCat_Titles}}
	* '''History''':		* '''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-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

Woozle: line numbering; line formatting

2009-12-10T23:00:36Z

line numbering; line formatting

← Older revision		Revision as of 23:00, 10 December 2009
Line 1:		Line 1:
	==About==		==About==
	* '''History''':		* '''History''':
	** '''2009-12-10''' Transcribed from development source; updated name of source query in STAGE 2		** '''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
	==PHP code==		==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.		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(		<php>$arSQL_CtgBuild = array(
	/* STAGE 1: Clear/refill the two temporary tables from which cat_items is updated. */		/* STAGE 1: Clear/refill the two temporary tables from which cat_items is updated. */
	'clear update table 1 of 2' => 'DELETE FROM ctg_upd1;',		'1.1 clear update table 1 of 2' => 'DELETE FROM ctg_upd1;',
	'clear update table 2 of 2' => 'DELETE FROM ctg_upd2;',		'1.2 clear update table 2 of 2' => 'DELETE FROM ctg_upd2;',
	/* == Fill temp tables == */		/* == Fill temp tables == */
	/* -- generated source data: */		/* -- generated source data: */
	'fill update table 1/2 with source data' => 'INSERT INTO ctg_upd1 SELECT		'1.3 fill update table 1/2 with source data'
	CatSfx, isCloseOut, ID_CTG_Title, ID_CTG_Item, ID_Title, ID_ItTyp, ID_ItOpt, ID_ShipCost, PriceBuy, PriceSell, PriceList,		=> 'INSERT INTO ctg_upd1 SELECT
	ItOpt_Descr_part, NameSng, GrpItmDescr, TitleGroupDescr, OptionDescr, ItOpt_Sort,		CatSfx, isCloseOut, ID_CTG_Title, ID_CTG_Item, ID_Title, ID_ItTyp, ID_ItOpt, ID_ShipCost, PriceBuy, PriceSell, PriceList,
	GrpCode, GrpDescr, GrpSort, IDS_Item, CatNum, ItOpt_Descr		ItOpt_Descr_part, NameSng, GrpItmDescr, TitleGroupDescr, OptionDescr, ItOpt_Sort,
	FROM qryCtg_src;',		GrpCode, GrpDescr, GrpSort, IDS_Item, CatNum, ItOpt_Descr
			FROM qryCtg_src;',
	/* -- existing catalog data indexed for JOINing: */		/* -- existing catalog data indexed for JOINing: */
	'fill update table 2/2 with pre-join data' => 'INSERT INTO ctg_upd2 SELECT *, 0 AS cntDups		'1.4 fill update table 2/2 with pre-join data'
	FROM qryCtg_Items_forUpdJoin		=> 'INSERT INTO ctg_upd2 SELECT *, 0 AS cntDups
	ON DUPLICATE KEY UPDATE cntDups=cntDups+1;',		FROM qryCtg_Items_forUpdJoin
			ON DUPLICATE KEY UPDATE cntDups=cntDups+1;',
	/* STAGE 2: Calculate stock numbers and set isForSale flag */		/* STAGE 2: Calculate stock numbers and set isForSale flag */
	/* -- calculate stock quantities; set for-sale flag if in stock */		/* -- calculate stock quantities; set for-sale flag if in stock */
	'calculate stock quantities and status' => 'UPDATE ctg_upd2 AS i LEFT JOIN qryStk_items_remaining AS s ON i.ID_Item=s.ID_Item		'2.1 calculate stock quantities and status'
	SET		=> 'UPDATE ctg_upd2 AS i LEFT JOIN qryStk_items_remaining AS s ON i.ID_Item=s.ID_Item
	i.qtyInStock=s.QtyForSale,		SET
	i.isForSale=(s.QtyForSale > 0);',		i.qtyInStock=s.QtyForSale,
			i.isForSale=(s.QtyForSale > 0);',
	/* -- also set for-sale flag if available from source */		/* -- also set for-sale flag if available from source */
	'also update status from catalog source' => 'UPDATE ctg_upd2 AS i LEFT JOIN ctg_upd1 AS u ON i.IDS_Item=u.IDS_Item		'2.2 also update status from catalog source'
	SET i.isForSale=i.isForSale OR (u.IDS_Item IS NOT NULL);',		=> '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 */		/* STAGE 3: Update cat_items */
	/* -- replace sourced items in cat_items from CTG data (except for fields saved in ctg_upd2) */		/* -- replace sourced items in cat_items from CTG data (except for fields saved in ctg_upd2) */
	'update item status with calculated items' => 'REPLACE INTO cat_items		'3.1 update item status with calculated items'
	SELECT *		=> 'REPLACE INTO cat_items
	FROM qryCtg_Upd_join;',		SELECT *
			FROM qryCtg_Upd_join;',
	/* -- clear availability flags in any unused items */		/* -- clear availability flags in any unused items */
	'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;',		'3.2 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;',
	/* NOT FINISHED -- isn't there one more flag? */		/* NOT FINISHED -- isn't there one more flag? */
	/* -- set stock and for-sale fields for all joinable cat_items from calculations done in ctg_upd2 */		/* -- set stock and for-sale fields for all joinable cat_items from calculations done in ctg_upd2 */
	'set stock status fields' => 'UPDATE cat_items AS i LEFT JOIN ctg_upd2 AS u ON i.ID=u.ID_Item		'3.3 set stock status fields'
	SET		=> 'UPDATE cat_items AS i LEFT JOIN ctg_upd2 AS u ON i.ID=u.ID_Item
	i.isForSale=u.isForSale;',		SET
			i.isForSale=u.isForSale;',
	/* -- update inactive catalog numbers where title's catnum has changed */		/* -- update inactive catalog numbers where title's catnum has changed */
	'update changed inactive catalog numbers' => 'UPDATE cat_items AS i LEFT JOIN qryCat_Titles AS t ON i.ID_Title=t.ID		'3.4 update changed inactive catalog numbers'
	SET		=> 'UPDATE cat_items AS i LEFT JOIN qryCat_Titles AS t ON i.ID_Title=t.ID
	i.CatNum=CONCAT_WS("-",t.CatNum,i.CatSfx)		SET
	WHERE (LEFT(i.CatNum,LENGTH(t.CatNum)) != t.CatNum) AND NOT isPulled;',		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 */		/* STAGE 4: Housekeeping - update timestamp of cat_items so dependent tables will be recalculated */
	'update cache timestamp' => 'UPDATE data_tables SET WhenUpdated=NOW() WHERE Name="cat_items";'		'4. update cache timestamp'
	);</php>		=> 'UPDATE data_tables
			SET WhenUpdated=NOW() WHERE Name="cat_items";'
			);</php>

Woozle: updated name of source query in STAGE 2; added about/history

2009-12-10T22:27:45Z

updated name of source query in STAGE 2; added about/history

← Older revision		Revision as of 22:27, 10 December 2009
Line 1:		Line 1:
			==About==
			* '''History''':
			** '''2009-12-10''' Transcribed from development source; updated name of source query in STAGE 2
	==PHP code==		==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.		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.
Line 18:		Line 21:
	/* STAGE 2: Calculate stock numbers and set isForSale flag */		/* STAGE 2: Calculate stock numbers and set isForSale flag */
	/* -- calculate stock quantities; set for-sale flag if in stock */		/* -- calculate stock quantities; set for-sale flag if in stock */
	'calculate stock quantities and status' => 'UPDATE ctg_upd2 AS i LEFT JOIN ~~v_stk_items_remaining~~ AS s ON i.ID_Item=s.ID_Item		'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		SET
	i.qtyInStock=s.QtyForSale,		i.qtyInStock=s.QtyForSale,

Woozle: copied from current development PHP file

2009-12-10T21:48:14Z

copied from current development PHP file

New page

==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. */
'clear update table 1 of 2' => 'DELETE FROM ctg_upd1;',
'clear update table 2 of 2' => 'DELETE FROM ctg_upd2;',
/* == Fill temp tables == */
/* -- generated source data: */
'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: */
'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 */
'calculate stock quantities and status' => 'UPDATE ctg_upd2 AS i LEFT JOIN v_stk_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 */
'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) */
'update item status with calculated items' => 'REPLACE INTO cat_items
SELECT *
FROM qryCtg_Upd_join;',
/* -- clear availability flags in any unused items */
'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;',
/* NOT FINISHED -- isn't there one more flag? */
/* -- set stock and for-sale fields for *all* joinable cat_items from calculations done in ctg_upd2 */
'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 */
'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 */
'update cache timestamp' => 'UPDATE data_tables SET WhenUpdated=NOW() WHERE Name="cat_items";'
);</php>