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
(→‎SQL - tables: the rest of the tables - first draft)
m (updated template name)
 
(45 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 - tables==
+
* {{l/sub|2017}} version - 2016 code apparently never really got finished, and the Items table needs some flag changes
−
===src_ctlgs===
+
* {{l/sub|2016}} version - webified version of 2010, with rules finally documented
−
'''Source Catalogs''' - a list of catalogs (past and present) each supplier has provided. A catalog indicates which items the supplier has available for sale at any given time. Any given catalog can be superceded by one other catalog; items in a superceded catalog are no longer available. Groups of items can also be discontinued individually.
+
* '''2010''' version: see {{l/vbzcart/code|maint/build-cat.php}}
−
<mysql>CREATE TABLE `src_ctlgs` (
+
* {{l/sub|2009}} version - SQL statements executed directly from PHP code
−
  `ID`        INT NOT NULL AUTO_INCREMENT,
+
* {{l/sub|2008}} version - started in 2007 - a set of stored queries called from a stored procedure
−
  `Name`      VARCHAR(63) COMMENT "name of catalog (for lists)",
+
* {{l/sub|2006}} version
−
  `Abbr`      VARCHAR(7) COMMENT "abbreviation",
 
−
  `ID_Supp`    INT NOT NULL COMMENT "supplier whose catalog this is",
 
−
  `ID_Repl`    INT DEFAULT NULL COMMENT "ID of replacement catalog",
 
−
  `WhenAvail`  DATETIME DEFAULT NULL "earliest active date",
 
−
  `isCloseOut` BOOL COMMENT "this is a close-out listing?",
 
−
  PRIMARY KEY(`ID`)
 
−
)
 
−
ENGINE = MYISAM;</mysql>
 
−
===src_groups===
 
−
'''Source Groups''': sort of like a set of options which are available for one or more titles, e.g. many suppliers will have a large number of their t-shirts all available in the same set of sizes, with the same set of prices; we define a src_group for that. They may also have a smaller number of those shirts available in a different set of sizes/prices, e.g. longsleeve; that becomes another src_group.
 
−
<mysql>CREATE TABLE `src_groups` (
 
−
  `ID`        INT NOT NULL AUTO_INCREMENT,
 
−
  `ID_Supp`    INT NOT NULL COMMENT "Supplier whose group this is",
 
−
  `Name`      VARCHAR(63) NOT NULL COMMENT "Name of group (internal use)",
 
−
  `Code`      VARCHAR(7) DEFAULT NULL COMMENT "Suffix code for catalog numbers",
 
−
  `SortGroup`  VARCHAR(31) DEFAULT NULL COMMENT "Sorting key withing group list",
 
−
  `ID_Redir`  INT DEFAULT NULL,
 
−
  PRIMARY KEY(`ID`)
 
−
)
 
−
ENGINE = MYISAM;</mysql>
 
−
* There was also a SortTitle field (formerly TitleSort) for "sorting key within list for title", but it was all NULL. I don't remember if this is something there was a specific need for, or something which seemed like a good idea at the time but never was needed.
 
−
* '''ID_Redir''' does have data, but I need to figure out if it's actually necessary or if there's a better way of doing what it was supposed to do.
 
−
===src_grp_items===
 
−
'''Source Group Items''' - defines the list of items which must be added to cat_items for each title in a group
 
−
<mysql>CREATE TABLE `src_groups` (
 
−
  `ID`        INT NOT NULL AUTO_INCREMENT,
 
−
  `ID_Group`  INT NOT NULL COMMENT "src_grp to which this definition applies",
 
−
  `isActive`  BOOL DEFAULT FALSE COMMENT "if FALSE, don't use this record to build items",
 
−
/* these fields are copied verbatim to cat_items: */
 
−
  `ID_ItTyp`  INT DEFAULT NULL COMMENT "cat_ittyps.ID: value for field when adding to cat_items",
 
−
  `ID_ItOpt`  INT DEFAULT NULL COMMENT "cat_ioptns.ID: value for field when adding to cat_items",
 
−
  `PriceBuy`  DECIMAL(9,2) COMMENT "wholesale price from supplier",
 
−
  `PriceSell`  DECIMAL(9,2) COMMENT "price to customer",
 
−
  `PriceList`  DECIMAL(9,2) COMMENT "supplier's retail list price (if available)",
 
−
  `ID_ShipCost
 
−
/* these fields are used as the basis for values in cat_items: */
 
−
  `Descr`      VARCHAR(63) COMMENT "descriptive text for this particular option within the group",
 
−
  PRIMARY KEY(`ID`)
 
−
)
 
−
ENGINE = MYISAM;</mysql>
 
−
* '''Descr''' is used in at least a couple of different ways when adding the item to cat_items; to be documented
 
−
===src_grp_titles===
 
−
'''Source Group Titles''' - defines the titles in each group (or groups in each title, if you prefer... many-to-many relationship).
 
−
<mysql>CREATE TABLE `src_groups` (
 
−
  `ID`        INT NOT NULL AUTO_INCREMENT,
 
−
  `ID_Group`  INT NOT NULL COMMENT "src_grp to which the title now belongs",
 
−
  `ID_Catalog` INT NOT NULL COMMENT "src_ctlgs.ID: catalog which lists this title as available with the given options",
 
−
  `isActive`  BOOL DEFAULT FALSE "if FALSE, don't use this group for this title",
 
−
  `WhenDiscon` DATETIME DEFAULT NULL "when this title was discontinued in this group",
 
−
  `ID_ItOpt`  INT DEFAULT NULL COMMENT "cat_ioptns.ID: value for field when adding to cat_items",
 
−
  `GroupCode`  VARCHAR(7) DEFAULT NULL COMMENT "catalog code extension for this title group (optional)",
 
−
  `GroupDescr` VARCHAR(63) DEFAULT NULL COMMENT "description extension for this title group (required if GroupCode is used)",
 
−
  `GroupSort`  VARCHAR(7) DEFAULT NULL COMMENT "sorting key; use GroupDescr by default",
 
−
  `Supp_CatNum` VARCHAR(63) DEFAULT NULL COMMENT "title catalog # for restock from supplier",
 
−
  PRIMARY KEY(`ID`)
 
−
)
 
−
ENGINE = MYISAM;</mysql>
 
−
* '''Supp_CatNum''': Some suppliers have different catalog numbers for different types of items, e.g. longsleeves, rather than just a different suffix on the same catalog number. This helps keep track of those variations a little bit, though it could probably be made better use of.
 
−
* There was also a '''Notes''' memo field, with occasional text in it; I'm thinking this is either obsolete or should be more like a log, with a timestamp. Wait to see if it's needed before re-implementing.
 
  
−
==SQL - queries==
+
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:
−
These are from the MS Access 97 version.
+
<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.
−
===qryCatNum_Depts===
+
</blockquote>
−
<sql>SELECT
+
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+).
−
  d.ID,
 
−
  d.name,
 
−
  d.sort,
 
−
  d.CatKey,
 
−
  ID_Supplier,
 
−
  s.CatKey&iif(d.CatKey IS NULL,"","-"&d.CatKey) AS CatNum,
 
−
  d.CatKey AS DeptCatKey
 
−
FROM Depts AS d LEFT JOIN Suppliers AS s ON d.ID_Supplier=s.ID;</sql>
 
−
 
 
−
===qryCatNum_Titles===
 
−
v_titles has much the same information, but depends on _titles and so may not be up-to-date.
 
−
<sql>SELECT
 
−
  t.ID,
 
−
  t.Name,
 
−
  d.CatNum&"-"&t.CatKey AS CatNum,
 
−
  d.DeptCatKey,
 
−
  t.CatKey AS TitleCatKey,
 
−
  ID_Dept,
 
−
  d.ID_Supplier
 
−
FROM
 
−
  Titles AS t
 
−
  LEFT JOIN qryCatNum_Depts AS d
 
−
    ON t.ID_dept=d.ID</sql>
 
−
 
 
−
===(Step 1aa) qryVb_Items_updates===
 
−
<sql>SELECT
 
−
  BuildOption(g.Code,gt.GroupCode,o.CatKey) AS CatSfx,
 
−
  t.ID&CatSfx AS IDS_Update,
 
−
  t.CatNum&CatSfx AS CatNum,
 
−
  t.ID AS ID_Title,
 
−
  g.ID AS ID_TGroup,
 
−
  gt.ID AS ID_TGTitle,
 
−
  gi.ID AS ID_TGItem,
 
−
  gi.ID_ItemType AS ID_ItTyp,
 
−
  gi.ID_Option AS ID_ItOpt
 
−
FROM
 
−
( ( ( ( qryCatNum_Titles AS t
 
−
        LEFT JOIN [TGroup Titles] AS gt
 
−
          ON gt.ID_Title=t.ID
 
−
      )
 
−
      LEFT JOIN TGroups AS g
 
−
        ON g.ID=gt.ID_TGroup
 
−
    )
 
−
    LEFT JOIN qryCatalogs_Active AS c
 
−
      ON gt.ID_Catalog=c.ID
 
−
  )
 
−
  LEFT JOIN [TGroup Items] AS gi
 
−
    ON gi.ID_TGroup=g.ID
 
−
)
 
−
LEFT JOIN Options AS o
 
−
  ON gi.ID_Option=o.ID
 
−
WHERE
 
−
  (c.ID IS NOT NULL)
 
−
  AND g.Active
 
−
  AND gt.Active
 
−
  AND ((gt.WhenDiscont IS NULL) OR (gt.WhenDiscont>Now))
 
−
  AND gi.Active;</sql>
 
−
 
 
−
===(Step 1a) qryVb_Items_updates_new===
 
−
<sql>SELECT c.*
 
−
  FROM qryVb_Items_Updates AS c
 
−
  LEFT JOIN [calc Item Updates saved] AS s
 
−
    ON c.IDS_Update=s.IDS_Update
 
−
    WHERE s.IDS_Update IS NULL;</sql>
 
−
 
 
−
===(Step 1) qryVb_Items_updates_append===
 
−
<sql>INSERT INTO [calc Item Updates saved] (
 
−
  ID_Item,
 
−
  IDS_Update,
 
−
  ID_Title,
 
−
  ID_TGroup,
 
−
  ID_TGTitle,
 
−
  ID_TGItem,
 
−
  ID_ItTyp,
 
−
  ID_ItOpt )
 
−
SELECT
 
−
  NULL,
 
−
  IDS_Update,
 
−
  ID_Title,
 
−
  ID_TGroup,
 
−
  ID_TGTitle,
 
−
  ID_TGItem,
 
−
  ID_ItTyp,
 
−
  ID_ItOpt
 
−
FROM qryVb_Items_Updates_new;</sql>
 
−
===(Step 1b) qryVb_Items_updates_copy_fields===
 
−
<sql>UPDATE
 
−
  [calc Item Updates saved] AS s
 
−
  LEFT JOIN qryVb_Items_updates AS u
 
−
    ON s.IDS_Update=u.IDS_Update
 
−
  SET
 
−
    s.ID_ItTyp = u.ID_ItTyp,
 
−
    s.ID_ItOpt = u.ID_ItOpt;</sql>
 
−
===(Step 2a) qryVb_Items_results===
 
−
<sql>SELECT
 
−
  s.ID_Item,
 
−
  s.CatNum,
 
−
  (
 
−
    tg.Active AND
 
−
    (
 
−
      (tgt.WhenDiscont Is Null)
 
−
    OR
 
−
      (tgt.WhenDiscont>Now())
 
−
    )
 
−
  AND tgt.Active AND tgi.Active AND (c.ID_Supercede Is Null)
 
−
  ) AS isForSale,
 
−
  isForSale AND NOT c.isCloseOut AS isInPrint,
 
−
  c.isCloseOut,
 
−
  s.ID_Title,
 
−
  tgi.ID_ItemType AS ID_ItTyp,
 
−
  tgi.ID_Option AS ID_ItOpt,
 
−
  tgi.ID_ShipCode AS ID_ShipCost,
 
−
  tgi.PriceBuy*100 AS PriceBuy,
 
−
  tgi.PriceSell*100 AS PriceSell,
 
−
  tgi.PriceList*100 AS PriceList,
 
−
  IIF(tgi.Descr Is Null,OptionJoin(tgt.GroupDescr,o.Descr," / "),tgi.Descr) AS ItOpt_Descr,
 
−
  s.CatSfx,
 
−
  tg.TitleSort & it.Sort & o.Sort AS ItOpt_Sort
 
−
FROM
 
−
( ( ( ( ( [calc Item Updates saved] AS s
 
−
          LEFT JOIN TGroups AS tg
 
−
            ON s.ID_TGroup = tg.ID
 
−
        ) LEFT JOIN [TGroup Titles] AS tgt
 
−
            ON s.ID_TGTitle = tgt.ID
 
−
      ) LEFT JOIN [TGroup Items] AS tgi
 
−
          ON s.ID_TGItem = tgi.ID
 
−
    ) LEFT JOIN [Supplier Catalogs] AS c
 
−
        ON tgt.ID_Catalog = c.ID
 
−
  ) LEFT JOIN Options AS o
 
−
      ON tgi.ID_Option = o.ID
 
−
) LEFT JOIN [Item Types] AS it
 
−
    ON tgi.ID_ItemType = it.ID;</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
 
−
  qryVb_Items_Results 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
 
−
  [calc Item Updates saved] AS s
 
−
  LEFT JOIN cat_items AS i
 
−
    ON i.CatNum=s.CatNum
 
−
  SET s.ID_Item = i.ID
 
−
  WHERE s.ID_Item IS NULL;</sql>
 
−
 
 
−
===(Step 4) qryVb_Items_results_update===
 
−
<sql>UPDATE
 
−
  qryVb_Items_Results 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
 
−
  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 qryVb_Items_Results 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 (s.QtyForSale > 0),
 
−
    i.qtyInStock = 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"