VbzCart/docs/pieces/catalog/building/2008

From Woozle Writes Code
< VbzCart‎ | docs‎ | pieces‎ | catalog‎ | building
Revision as of 01:53, 25 February 2024 by Woozle (talk | contribs) (Woozle moved page VbzCart/VbzCart/pieces/catalog/building/2008 to VbzCart/docs/pieces/catalog/building/2008 without leaving a redirect: correct naming (was no way to import directly to this name))
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
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"