VbzCart/catalog/building/2008

History
For the 2009 version, the stored queries were scrapped in favor of executing their SQL directly from PHP code.

Process
This is only a draft, as the process is still being worked out. Right now this process does not "recycle" junked records in cat_items; perhaps that was a bad idea to begin with, or perhaps some other use can be found for junked records that isn't part of catalog-building. There probably should be some process which removes junked records from use so they aren't taking up space and CPU cycles.
 * 1) Update the list of all catalog-based items ([calc Items Updates saved])
 * 2) * -- Add any new items to [calc Items Updates saved]
 * 3) ** -- in-print items that are new
 * 4) *** -- all the items currently available from suppliers (in print)
 * 5) **
 * 6) For all items in [calc Items Updates saved] where ID_Item IS NULL, create new records in cat_items and update the corresponding records in [calc Items Updates saved]
 * 7) * -- adds new cat_items rows for any calculated items not already cross-referenced. It doesn't cross-reference them, however. That is done by the next query.
 * 8) **
 * 9) Fill in any blank ID_Item fields in [calc Items Updates saved] records:
 * 10) * -- plugs the Item ID back into the calculated temp table. It assumes (1) 1:1 correspondence between Item IDs and catalog numbers, and (2) catalog numbers won't be changed during the course of a build (though they can change in general).
 * 11) Copy calculated item data over to [cat_items]:
 * 12) *
 * 13) Final flag updates:
 * 14) *
 * 15) ** Clear isInPrint flag for cat_items not found in calculated in-print listing
 * 16) ** Set isForSale for cat_items with stock for sale
 * 17) ** Update stock quantities for all cat_items
 * 18) Housekeeping: mark table as updated so cached tables get recalculated
 * 19) *

SQL - queries - 2008
2008-03-15: Completely rewriting the building sequence again...


 * : the main building process - details are documented there too
 * : looks for duplicate IDS_Items
 * : the main building process - details are documented there too
 * : looks for duplicate IDS_Items
 * : the main building process - details are documented there too
 * : looks for duplicate IDS_Items
 * : looks for duplicate IDS_Items

SQL - queries
These are from the MS Access 97 version, gradually being honed down for migration to MySQL

(Step 1a) qryVb_Items_updates_new
SELECT c.* FROM qryCtg_Items_updates_joinable AS c LEFT JOIN ctg_updates AS s ON c.IDS_Item=s.IDS_Item WHERE s.IDS_Item IS NULL;

(Step 1) qryVb_Items_updates_append
INSERT INTO ctg_updates ( ID_Item,  IDS_Item,  CatNum,  CatSfx,  ID_Title,  ID_CTG_Group,  ID_CTG_Title,  ID_CTG_Item,  ID_ItTyp,  ID_ItOpt ) SELECT 0 AS ID_Item, IDS_Item, CatNum, CatSfx, ID_Title, ID_TGroup, ID_TGTitle, ID_TGItem, ID_ItTyp, ID_ItOpt FROM qryVb_Items_Updates_new;

(Step 2) qryVb_Items_results_append
This adds new cat_items rows for any calculated items not already cross-referenced. It doesn't cross-reference them, however. That is done by the next query. INSERT INTO cat_items ( CatNum,  isForSale,  isMaster,  isInPrint,  isCloseOut,  ID_ItTyp,  ID_ItOpt,  ItOpt_Descr,  ItOpt_Sort,  ID_ShipCost,  PriceBuy,  PriceSell,  PriceList ) SELECT r.CatNum, r.isForSale, FALSE AS isMaster, r.isInPrint, r.isCloseOut, r.ID_ItTyp, r.ID_ItOpt, r.ItOpt_Descr, r.ItOpt_Sort, r.ID_ShipCost, r.PriceBuy, r.PriceSell, r.PriceList FROM qryCtg_Items_updates AS r LEFT JOIN cat_items AS i ON r.ID_Item=i.ID WHERE i.ID IS NULL;

(Step 3) qryVb_Items_updates_index_new
This plugs the Item ID back into the calculated temp table. It assumes (1) 1:1 correspondence between Item IDs and catalog numbers, and (2) catalog numbers won't be changed during the course of a build (though they can change in general). UPDATE ctg_updates AS s LEFT JOIN cat_items AS i ON i.CatNum=s.CatNum SET s.ID_Item = i.ID, isForSale = FALSE, isInPrint = FALSE WHERE s.ID_Item IS NULL;

(Step 4) qryVb_Items_results_update
UPDATE qryCtg_build AS r LEFT JOIN cat_items AS i ON r.ID_Item=i.ID SET i.CatNum = r.CatNum, i.isForSale = r.isForSale, i.isInPrint = r.isInPrint, i.isCloseOut = r.isCloseOut, i.isPulled = FALSE, i.isDumped = FALSE, i.ID_ItTyp = r.ID_ItTyp, i.ID_ItOpt = r.ID_ItOpt, i.ItOpt_Descr = iif(r.ItOpt_Descr IS NULL,i.ItOpt_Descr,r.ItOpt_Descr), i.ItOpt_Sort = r.ItOpt_Sort, i.ID_ShipCost = r.ID_ShipCost, i.PriceBuy = r.PriceBuy, i.PriceSell = r.PriceSell, i.PriceList = r.PriceList, i.GrpCode = r.GrpCode, i.GrpDescr = r.GrpDescr, i.GrpSort = r.GrpSort, i.CatSfx = r.CatSfx WHERE r.ID_Item IS NOT NULL;

Changes:
 * Must clear isPulled and isDumped, in case pulled items are returned to service

(Step 5) qryVb_Items_update_final
UPDATE (cat_items AS i LEFT JOIN qryCtg_build AS r ON r.ID_Item=i.ID) LEFT JOIN v_stk_items_remaining AS s ON s.ID_Item=i.ID SET i.isInPrint = nz(r.isInPrint), i.isCloseOut = nz(r.isCloseOut), i.isForSale = nz(r.isInPrint) OR nz(r.isCloseOut) OR (nz(s.QtyForSale) > 0), i.qtyInStock = nz(s.QtyForSale);
 * Clear isInPrint flag for cat_items not found in calculated in-print listing
 * Set isForSale for cat_items with stock for sale
 * Update stock quantities for all cat_items

(Step 6) qryVb_Items_mark_updated
UPDATE data_tables SET WhenUpdated=Now WHERE Name="cat_items"