VbzCart/pieces/catalog/building

Navigation

VbzCart: catalog building

Version

This is being revised yet again for the 2007 VbzCart-MySQL migration project; the previous version is here: VbzCart catalog building 2006.

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])
   • #(Step 1) qryVb_Items_updates_append -- Add any new items to [calc Items Updates saved]
     • #(Step 1a) qryVb_Items_updates_new -- in-print items that are new
       • #(Step 1aa) qryVb_Items_updates -- all the items currently available from suppliers (in print)
     • #(Step 1b) qryVb_Items_updates_copy_fields
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]
   • #(Step 2) qryVb_Items_results_append -- 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.
     • #(Step 2a) qryVb_Items_results
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]:
   • #(Step 4) qryVb_Items_results_update
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
   • #(Step 6) qryVb_Items_mark_updated

SQL - queries - 2008

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

qryCtg_src_sub

<mysql>CREATE OR REPLACE VIEW qryCtg_src_sub AS SELECT

   NULLIF(CONCAT_WS('/',g.Code,gt.GroupCode,o.CatKey),) AS CatSfx,
   c.isCloseOut,
   gt.ID AS ID_CTG_Title,
   gi.ID AS ID_CTG_Item,
   gt.ID_Group AS ID_CTG_Group,   /* mainly for debugging */
   gt.ID_Source AS ID_CTG_Source, /* mainly for debugging */
   gt.ID_Title,
   gi.ID_ItTyp,
   gi.ID_ItOpt,
   gi.ID_ShipCost,
   gi.PriceBuy,
   gi.PriceSell,
   gi.PriceList,
   IFNULL(gi.Descr,CONCAT_WS(' / ',gt.GroupDescr,o.Descr)) AS ItOpt_Descr_part,
   it.NameSng,
   gi.Descr AS GrpItmDescr,
   gt.GroupDescr AS TitleGroupDescr,
   o.Descr AS OptionDescr,
   CONCAT(g.Sort,it.Sort,o.Sort) AS ItOpt_Sort,
   gt.GroupCode AS GrpCode,
   gt.GroupDescr AS GrpDescr,
   gt.GroupSort AS GrpSort
 FROM
 (
   (
     (
       (
         qryCtg_Titles_active AS gt LEFT JOIN ctg_groups AS g ON g.ID=gt.ID_Group
        ) LEFT JOIN qryCtg_Sources_active AS c ON gt.ID_Source=c.ID
      ) LEFT JOIN qryCtg_Items_active AS gi ON gi.ID_Group=g.ID
    ) LEFT JOIN cat_ioptns AS o ON gi.ID_ItOpt=o.ID
  ) LEFT JOIN cat_ittyps AS it ON gi.ID_ItTyp = it.ID

WHERE (c.ID IS NOT NULL) AND (gi.ID IS NOT NULL);</mysql>

qryCtg_src

<mysql>CREATE OR REPLACE VIEW qryCtg_src AS SELECT

 b.*,
 CONCAT_WS('-',b.ID_Title,b.CatSfx) AS IDS_Item,
 CONCAT_WS('-',t.CatNum,b.CatSfx) AS CatNum,
 IFNULL(ItOpt_Descr_part,NameSng) AS ItOpt_Descr

FROM qryCtg_src_sub AS b LEFT JOIN qryCat_Titles AS t ON b.ID_Title=t.ID;</mysql>

qryCtg_Items_forUpdJoin

<mysql>CREATE OR REPLACE VIEW qryCtg_Items_forUpdJoin AS SELECT

 ID AS ID_Item,
 CONCAT_WS('-',ID_Title,CatSfx) AS IDS_Item,
 FALSE AS isForSale,
 0 AS QtyInStock,
 CatNum,
 Supp_CatNum,
 QtyMin_Stk

FROM cat_items WHERE ID_Title IS NOT NULL;</mysql>

qryCtg_Upd_join

<mysql>CREATE OR REPLACE VIEW qryCtg_Upd_join AS SELECT

 c.ID_Item AS ID,
 IFNULL(s.CatNum,c.CatNum) AS CatNum,
 c.isForSale AS isForSale,
 FALSE AS isMaster,
 c.qtyInStock,
 NOT s.isCloseOut AS isInPrint,
 s.isCloseOut,
 FALSE AS isPulled,
 FALSE AS isDumped,
 s.ID_Title,
 s.ID_ItTyp,
 s.ID_ItOpt,
 s.ItOpt_Descr,
 s.ItOpt_Sort,
 s.GrpCode,
 s.GrpDescr,
 s.GrpSort,
 s.CatSfx,
 s.ID_ShipCost,
 s.PriceBuy,
 s.PriceSell,
 s.PriceList,
 c.Supp_CatNum,
 c.QtyMin_Stock

FROM ctg_upd1 AS s LEFT JOIN ctg_upd2 AS c ON s.IDS_Item=c.IDS_Item;</mysql>

• Having CatNum available from both the original data and the generated CTG data prevents existing CatNums from being overwritten with NULLs, which should never happen. However, it did happen during development, so this prevents it from causing damage to the data which will have to be repaired manually. Eventually, we need to figure out how the CTG CatNum could possibly be NULL, and either test for it before writing, or fix the problem.

qryCtg_src_dups

Returns all duplicate records (each IDS_Item must be unique) in the generated source data. This should be performed as a check on data integrity before writing to cat_items; any duplicates may indicate a problem (typically, entering items from a new catalog but forgetting to mark the old one as superceded), and should be inspected. (Not sure if conflicts will prevent the update from completing; to be determined later.)

<mysql>CREATE OR REPLACE VIEW qryCtg_src_dups AS SELECT IDS_Item, COUNT(IDS_Item) AS cntRows FROM qryCtg_src GROUP BY IDS_Item HAVING cntRows>1;</mysql>

ctg_upd* data building

(Under development) <mysql>DROP PROCEDURE IF EXISTS `doCtgUpdate`; CREATE PROCEDURE doCtgUpdate() MODIFIES SQL DATA BEGIN

/* PART 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, 0 AS cntDups 
 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;

/* PART 2: Calculate stock numbers and set isForSale flag */

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

/* PART 3: Update existing items in cat_items. */

 REPLACE INTO cat_items
 SELECT *
 FROM qryCtg_Upd_join;

 UPDATE cat_items AS i LEFT JOIN ctg_upd2 AS u ON i.ID=u.ID_Item SET i.isForSale=u.isForSale;

/* PART 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>

• 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.

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>