VbzCart/docs/pieces/catalog/building/2008

From Woozle Writes Code
< VbzCart‎ | docs‎ | pieces‎ | catalog‎ | building
Jump to navigation Jump to search

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. 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]
  3. Fill in any blank ID_Item fields in [calc Items Updates saved] records:
    • #(Step 3) qryVb_Items_updates_index_new -- 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).
  4. Copy calculated item data over to [cat_items]:
  5. Final flag updates:
    • #(Step 5) qryVb_Items_update_final
      • 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
  6. Housekeeping: mark table as updated so cached tables get recalculated

SQL - queries - 2008

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

SQL - queries

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

(Step 1a) qryVb_Items_updates_new

<sql>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;</sql>

(Step 1) qryVb_Items_updates_append

<sql>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; </sql>

(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. <sql>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; </sql>

(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). <sql>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;

</sql>

(Step 4) qryVb_Items_results_update

<sql>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; </sql>

Changes:

  • Must clear isPulled and isDumped, in case pulled items are returned to service

(Step 5) qryVb_Items_update_final

  • 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

<sql>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);

</sql>

(Step 6) qryVb_Items_mark_updated

<sql>UPDATE 

 data_tables

SET WhenUpdated=Now() WHERE Name="cat_items"</sql>

Retrieved from "https://wooz.dev/mw/index.php?title=VbzCart/docs/pieces/catalog/building/2008&oldid=6667"