VbzCart/procs/doCtgUpdate - Revision history

Woozle: superceded

2010-11-10T17:01:48Z

superceded

← Older revision		Revision as of 17:01, 10 November 2010
Line 1:		Line 1:
	==About==		==About==
			* '''Status''': not used (not in database, and function has been replaced by [[VbzCart/code/maint/build-cat.php\|a standalone script]].
	* '''Purpose''': building of ctg_upd* table contents		* '''Purpose''': building of ctg_upd* table contents
	* '''Requires''':		* '''Requires''':

Woozle: PHP code to replace PROCEDURE

2009-11-19T14:55:46Z

PHP code to replace PROCEDURE

@@ Line 11: / Line 11: @@
 ** '''2009-07-06''' {{vbzcart|table|cat_items}} no longer has qtyInStock field, so removing that from the update
 *** TO DO: remove this field from scratch tables
 ===Status===
 This is mostly working. A key issue seems to be the correct setting of isForSale.
@@ Line 28: / Line 29: @@
 ** '''Update''' 2009-05-03: It looks like ctg_upd2.cntDups is incremented whenever there's a duplicate, so all we have to do is check for rows with cntDups>1
 * There should also be a check for catnums-needing-update but lacking CatSfx
 ==SQL==
 <mysql>DROP PROCEDURE IF EXISTS `doCtgUpdate`;
 CREATE PROCEDURE doCtgUpdate() MODIFIES SQL DATA

Woozle: cat_items no longer has qtyForSale

2009-07-06T19:59:36Z

cat_items no longer has qtyForSale

← Older revision		Revision as of 19:59, 6 July 2009
Line 9:		Line 9:
	** '''2008-12-05''' Added update of inactive catalog numbers (title's catalog number must match start of item's catalog number)		** '''2008-12-05''' Added update of inactive catalog numbers (title's catalog number must match start of item's catalog number)
	** '''2009-05-04''' Added "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;" as step 2 in stage 3. This prevents items having isInPrint still set even though isForSale is not set.		** '''2009-05-04''' Added "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;" as step 2 in stage 3. This prevents items having isInPrint still set even though isForSale is not set.
			** '''2009-07-06''' {{vbzcart\|table\|cat_items}} no longer has qtyInStock field, so removing that from the update
			*** TO DO: remove this field from scratch tables
	===Status===		===Status===
	This is mostly working. A key issue seems to be the correct setting of isForSale.		This is mostly working. A key issue seems to be the correct setting of isForSale.
Line 73:		Line 75:
	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;
	~~i.qtyInStock=u.qtyInStock~~;

	/* -- update inactive catalog numbers where title's catnum has changed */		/* -- update inactive catalog numbers where title's catnum has changed */

Woozle: noted 5/4 addition in history

2009-07-06T19:49:41Z

noted 5/4 addition in history

← Older revision		Revision as of 19:49, 6 July 2009
Line 8:		Line 8:
	** '''2008-03-15''' Building sequence completely rewritten again, almost certainly including this procedure.		** '''2008-03-15''' Building sequence completely rewritten again, almost certainly including this procedure.
	** '''2008-12-05''' Added update of inactive catalog numbers (title's catalog number must match start of item's catalog number)		** '''2008-12-05''' Added update of inactive catalog numbers (title's catalog number must match start of item's catalog number)
			** '''2009-05-04''' Added "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;" as step 2 in stage 3. This prevents items having isInPrint still set even though isForSale is not set.
	===Status===		===Status===
	This is mostly working. A key issue seems to be the correct setting of isForSale.		This is mostly working. A key issue seems to be the correct setting of isForSale.
Line 25:		Line 26:
	** '''Update''' 2009-05-03: It looks like ctg_upd2.cntDups is incremented whenever there's a duplicate, so all we have to do is check for rows with cntDups>1		** '''Update''' 2009-05-03: It looks like ctg_upd2.cntDups is incremented whenever there's a duplicate, so all we have to do is check for rows with cntDups>1
	* There should also be a check for catnums-needing-update but lacking CatSfx		* There should also be a check for catnums-needing-update but lacking CatSfx

	~~'''NEED TO DOCUMENT the addition of this step''': UPDATE cat_items AS i LEFT JOIN qryCtg_Upd_join AS u ON i.ID=u.ID SET i.isInPrint=NULL WHERE u.ID IS NULL;~~

	~~Otherwise we have items with isInPrint still set even though isForSale is not set.~~

	==SQL==		==SQL==
	<mysql>DROP PROCEDURE IF EXISTS `doCtgUpdate`;		<mysql>DROP PROCEDURE IF EXISTS `doCtgUpdate`;

Woozle: listed all tables written to & all sources

2009-07-06T19:45:27Z

listed all tables written to & all sources

← Older revision		Revision as of 19:45, 6 July 2009
Line 1:		Line 1:
	==About==		==About==
	* '''Purpose''': building of ctg_upd* table contents		* '''Purpose''': building of ctg_upd* table contents
			* '''Requires''':
			** '''Reads''': {{vbzcart/query\|qryCtg_src}}, {{vbzcart/query\|qryCtg_Items_forUpdJoin}}, {{vbzcart/query\|v_stk_items_remaining}}, {{vbzcart/query\|qryCtg_Upd_join}}, {{vbzcart/query\|qryCat_Titles}}
			** '''Writes''': {{vbzcart\|table\|cat_items}}, {{vbzcart\|table\|data_tables}}
			** '''Scratch''': {{vbzcart\|table\|ctg_upd1}}, {{vbzcart\|table\|ctg_upd2}}
	* '''History''':		* '''History''':
	** '''2008-03-15''' Building sequence completely rewritten again, almost certainly including this procedure.		** '''2008-03-15''' Building sequence completely rewritten again, almost certainly including this procedure.

Woozle: new step: clear flags in cat_items

2009-05-04T11:37:57Z

new step: clear flags in cat_items

← Older revision		Revision as of 11:37, 4 May 2009
Line 22:		Line 22:
	* There should also be a check for catnums-needing-update but lacking CatSfx		* There should also be a check for catnums-needing-update but lacking CatSfx

	'''NEED TO ~~ADD~~ this step''': UPDATE cat_items AS i LEFT JOIN qryCtg_Upd_join AS u ON i.ID=u.ID SET i.isInPrint=NULL WHERE u.ID IS NULL;		'''NEED TO DOCUMENT the addition of this step''': UPDATE cat_items AS i LEFT JOIN qryCtg_Upd_join AS u ON i.ID=u.ID SET i.isInPrint=NULL WHERE u.ID IS NULL;

	Otherwise we have items with isInPrint still set even though isForSale is not set.		Otherwise we have items with isInPrint still set even though isForSale is not set.

	~~Probably wouldn't hurt to clear isForSale and that other flag I can't think of at the moment.~~

	==SQL==		==SQL==
Line 69:		Line 67:
	FROM qryCtg_Upd_join;		FROM qryCtg_Upd_join;

			/* -- clear availability flags in any 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 and for-sale fields for all joinable cat_items from calculations done in ctg_upd2 */
	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

Woozle: /* To Do */ additional step needed

2009-05-04T01:42:25Z

To Do: additional step needed

← Older revision		Revision as of 01:42, 4 May 2009
Line 21:		Line 21:
	** '''Update''' 2009-05-03: It looks like ctg_upd2.cntDups is incremented whenever there's a duplicate, so all we have to do is check for rows with cntDups>1		** '''Update''' 2009-05-03: It looks like ctg_upd2.cntDups is incremented whenever there's a duplicate, so all we have to do is check for rows with cntDups>1
	* There should also be a check for catnums-needing-update but lacking CatSfx		* There should also be a check for catnums-needing-update but lacking CatSfx

			'''NEED TO ADD this step''': UPDATE cat_items AS i LEFT JOIN qryCtg_Upd_join AS u ON i.ID=u.ID SET i.isInPrint=NULL WHERE u.ID IS NULL;

			Otherwise we have items with isInPrint still set even though isForSale is not set.

			Probably wouldn't hurt to clear isForSale and that other flag I can't think of at the moment.

	==SQL==		==SQL==

Woozle: /* To Do */ update 5/3

2009-05-04T00:44:04Z

To Do: update 5/3

← Older revision		Revision as of 00:44, 4 May 2009
Line 19:		Line 19:
	===To Do===		===To Do===
	* at some point, we need a way to test for duplicate IDS_Item keys before the data gets put into ctg_upd1. Duplicate keys will probably cause this procedure to fail at that point, but I don't know if it will show an informative message if this happens or just fail silently (as it is, even when it completes successfully it says "query cancelled").		* at some point, we need a way to test for duplicate IDS_Item keys before the data gets put into ctg_upd1. Duplicate keys will probably cause this procedure to fail at that point, but I don't know if it will show an informative message if this happens or just fail silently (as it is, even when it completes successfully it says "query cancelled").
			** '''Update''' 2009-05-03: It looks like ctg_upd2.cntDups is incremented whenever there's a duplicate, so all we have to do is check for rows with cntDups>1
	* There should also be a check for catnums-needing-update but lacking CatSfx		* There should also be a check for catnums-needing-update but lacking CatSfx

	==SQL==		==SQL==
	<mysql>DROP PROCEDURE IF EXISTS `doCtgUpdate`;		<mysql>DROP PROCEDURE IF EXISTS `doCtgUpdate`;

Woozle: extracted from catalog building page

2009-05-03T22:15:24Z

extracted from catalog building page

New page

==About==
* '''Purpose''': building of ctg_upd* table contents
* '''History''':
** '''2008-03-15''' Building sequence completely rewritten again, almost certainly including this procedure.
** '''2008-12-05''' Added update of inactive catalog numbers (title's catalog number must match start of item's catalog number)
===Status===
This is mostly working. A key issue seems to be the correct setting of isForSale.

'''When we get to Stage 2''':
* '''{{vbzcart|table|ctg_upd1}}''' has all the generated data from active sources; these items are all for sale
* '''{{vbzcart|table|ctg_upd2}}''' has joinable data from cat_items, i.e. anything which might be used (or revived) as a saleable item, i.e. anything where ID_Title is set. This includes a large number of items which will ''not'' be used. However, only items present in ctg_upd1 will cause a direct overwrite of cat_items, so we only need to save fields from cat_items which ''don't'' get set directly from ctg_upd1 (i.e. sourced items); items that are discontinued but in stock will not be overwritten, they will just have their stock status updated. This happens by setting those fields properly in ctg_upd2 (clear + calculate), then directly overwriting those fields in cat_items from the values in ctg_upd2. (Thus removing the need to clear those fields in cat_items even temporarily. This is the only reason we need ctg_upd2.)

The duplicate key count on ctg_upd2 should indicate how many different items share the same calculated IDS_Item. Ultimately, none of these should be items that are also for sale; perhaps a query should reality-check this after the update (for-sale items using duplicate IDSs), and warn the user if any records are found -- which would indicate a bug in the generation process.

'''During Stage 2''', we first calculate (from scratch) quantities in stock and set the "for sale" flag for any items in stock (these go in ctg_upd2). Then we also set the "for sale" flag for any items which happen to have active sources, i.e. are in ctg_upd1.
===Notes===
* If this doesn't work as part of a script, check the actual syntax by editing the procedure in MySQL Query Browser.
* REPLACE INTO is [[MySQL]]-specific syntax. The same thing could be accomplished in other more standard ways, but in the interest of time this seemed like the best way to get things working.
===To Do===
* at some point, we need a way to test for duplicate IDS_Item keys before the data gets put into ctg_upd1. Duplicate keys will probably cause this procedure to fail at that point, but I don't know if it will show an informative message if this happens or just fail silently (as it is, even when it completes successfully it says "query cancelled").
* There should also be a check for catnums-needing-update but lacking CatSfx
==SQL==
<mysql>DROP PROCEDURE IF EXISTS `doCtgUpdate`;
CREATE PROCEDURE doCtgUpdate() MODIFIES SQL DATA
BEGIN

/* STAGE 1: Clear/refill the two temporary tables from which cat_items is updated. */

/* == Clear temp tables == */
DELETE FROM ctg_upd1;
DELETE FROM ctg_upd2;

/* == Fill temp tables == */
/* -- generated 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: */
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 */
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 */
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) */
REPLACE INTO cat_items
SELECT *
FROM qryCtg_Upd_join;

/* -- set stock and for-sale fields for *all* joinable cat_items from calculations done in ctg_upd2 */
UPDATE cat_items AS i LEFT JOIN ctg_upd2 AS u ON i.ID=u.ID_Item
SET
i.isForSale=u.isForSale,
i.qtyInStock=u.qtyInStock;

/* -- update inactive catalog numbers where title's catnum has changed */
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 data_tables SET WhenUpdated=NOW() WHERE Name="cat_items";
END;
</mysql>
<section end=sql />