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 ctg_items 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);</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,
 CONCAT(ID_Title,IFNULL(CatSfx,)) AS IDS_Item

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

qryCtg_Upd_join

<mysql>CREATE OR REPLACE VIEW qryCtg_Upd_join AS SELECT uj.ID_Item, us.* FROM ctg_upd2 AS uj LEFT JOIN ctg_upd1 AS us ON uj.IDS_Item=us.IDS_Item;</mysql>

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>delimiter // CREATE PROCEDURE doCtgUpdate() MODIFIES SQL DATA AS 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(ID_Item,IDS_Item,isActive) SELECT ID, IDS_Item, FALSE AS isActive 
 FROM qryCtg_Items_forUpdJoin
 ON DUPLICATE KEY UPDATE cntDups=cntDups+1;

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

/* NOT USING THIS

 UPDATE cat_items AS i LEFT JOIN qryCtg_Upd_join AS u ON i.ID=u.ID_Item
 SET
   i.CatNum = u.CatNum,
   i.isForSale = TRUE,
   i.isInPrint = NOT u.isCloseOut,
   i.isCloseOut = u.isCloseOut,
   i.isPulled = FALSE,
   i.isDumped = FALSE,
   i.ID_ItTyp = u.ID_ItTyp,
   i.ID_ItOpt = u.ID_ItOpt,
   i.ItOpt_Descr = IFNULL(u.ItOpt_Descr,i.ItOpt_Descr), /* kluge */
   i.ItOpt_Sort = u.ItOpt_Sort,
   i.ID_ShipCost = u.ID_ShipCost,
   i.PriceBuy = u.PriceBuy,
   i.PriceSell = u.PriceSell,
   i.PriceList = u.PriceList,
   i.GrpCode = u.GrpCode,
   i.GrpDescr = u.GrpDescr,
   i.GrpSort = u.GrpSort,
   i.CatSfx = u.CatSfx
 WHERE u.IDS_Item IS NOT NULL;

• /

 REPLACE INTO cat_items AS i
 SELECT
   u.IDS_Item,
   u.CatNum,
   TRUE AS isForSale,
   NOT u.isCloseOut AS isInPrint,
   u.isCloseOut,
   FALSE AS isPulled,
   FALSE AS isDumped,
   u.ID_ItTyp,
   u.ID_ItOpt,
   u.ItOpt_Descr,
   u.ItOpt_Sort,
   u.ID_ShipCost,
   u.PriceBuy,
   u.PriceSell,
   u.PriceList,
   u.GrpCode,
   u.GrpDescr,
   u.GrpSort,
   u.CatSfx
 FROM qryCtg_Upd_join AS u
 WHERE u.IDS_Item IS NOT NULL;

END; // delimiter ;</mysql>

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