Difference between revisions of "VbzCart/pieces/catalog/building"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
< VbzCart‎ | pieces‎ | catalog
Jump to navigation Jump to search
(→‎ctg_upd* data building: more revisions, but not working yet)
m (updated template name)
 
(35 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
==Navigation==
 
−
[[VbzCart]]: [[VbzCart catalog building|catalog building]]
 
 
==Version==
 
==Version==
−
This is being revised yet again for the 2007 VbzCart-MySQL migration project; the previous version is here: [[vbzwiki:VbzCart catalog building 2006|VbzCart catalog building 2006]].
+
This is a tricky process which has been revised multiple times.
−
==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.
 
−
# 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]]
 
−
# 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]]
 
−
# 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).
 
−
# Copy calculated item data over to [cat_items]:
 
−
#* [[#(Step 4) qryVb_Items_results_update]]
 
−
# 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
 
−
# Housekeeping: mark table as updated so cached tables get recalculated
 
−
#* [[#(Step 6) qryVb_Items_mark_updated]]
 
  
−
==SQL - queries - 2008==
+
* {{l/sub|2017}} version - 2016 code apparently never really got finished, and the Items table needs some flag changes
−
2008-03-15: Completely rewriting the building sequence again...
+
* {{l/sub|2016}} version - webified version of 2010, with rules finally documented
−
===qryCtg_src_sub===
+
* '''2010''' version: see {{l/vbzcart/code|maint/build-cat.php}}
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_src_sub AS
+
* {{l/sub|2009}} version - SQL statements executed directly from PHP code
−
SELECT
+
* {{l/sub|2008}} version - started in 2007 - a set of stored queries called from a stored procedure
−
    NULLIF(CONCAT_WS('/',g.Code,gt.GroupCode,o.CatKey),'') AS CatSfx,
+
* {{l/sub|2006}} version
−
    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>
 
−
<section end=sql />
 
−
===qryCtg_src===
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
===qryCtg_Items_forUpdJoin===
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
====qryCtg_Upd_join====
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
====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.)
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
====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. */
+
It's interesting to note that I originally thought of SCM Groups as groups ''of Titles'' -- an explanation from the [[VbzCart/tables]] page on 2008-02-17 (and only removed on 2016-02-01) says:
−
 
+
<blockquote>The key concept here is "catalog title groups" (CTGs). A CTG is a set of titles which are all available with the same set of options (e.g. sizes) at the same prices (e.g. $10 S, $11 M-XL, $12 2XL). The final vbz catalog (cat_items) is the result of a sort of vector multiplication of Titles (cat_titles) and CTGs plus any items in stock.
−
  /* Clear temp tables: */
+
</blockquote>
−
  DELETE FROM ctg_upd1;
+
This conceptualization no longer works, as a SCM Title may have one or more SCM Groups -- e.g. if it has a set of standard sizes (like S-3X) but is also available in super-large sizes (4X+).
−
  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>
 
−
<section end=sql />
 
−
* 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>
 

Latest revision as of 11:07, 12 June 2017

Version

This is a tricky process which has been revised multiple times.

  • 2017 version - 2016 code apparently never really got finished, and the Items table needs some flag changes
  • 2016 version - webified version of 2010, with rules finally documented
  • 2010 version: see maint/build-cat.php
  • 2009 version - SQL statements executed directly from PHP code
  • 2008 version - started in 2007 - a set of stored queries called from a stored procedure
  • 2006 version

It's interesting to note that I originally thought of SCM Groups as groups of Titles -- an explanation from the VbzCart/tables page on 2008-02-17 (and only removed on 2016-02-01) says:

The key concept here is "catalog title groups" (CTGs). A CTG is a set of titles which are all available with the same set of options (e.g. sizes) at the same prices (e.g. $10 S, $11 M-XL, $12 2XL). The final vbz catalog (cat_items) is the result of a sort of vector multiplication of Titles (cat_titles) and CTGs plus any items in stock.

This conceptualization no longer works, as a SCM Title may have one or more SCM Groups -- e.g. if it has a set of standard sizes (like S-3X) but is also available in super-large sizes (4X+).

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/pieces/catalog/building&oldid=22764"