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: qryVb_Items_updates and dependencies)
m (updated template name)
 
(69 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.
 
−
# Update the list of all catalog-based items ([calc Items Updates saved])
 
−
#* Generate a list of all the items currently available from suppliers (query)
 
−
#* Add any new items to [calc Items Updates saved]
 
−
# 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]
 
−
# Update all data in cat_items from data stored in [calc Items Updates saved]
 
−
# In cat_items, clear all isForSale flags ''except'' for items either in stock or found in [calc Items Updates saved]
 
−
==SQL==
 
−
These are from the MS Access 97 version.
 
−
===qryCatNum_Depts===
 
−
<sql>SELECT
 
−
  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===
+
* {{l/sub|2017}} version - 2016 code apparently never really got finished, and the Items table needs some flag changes
−
v_titles has much the same information, but depends on _titles and so may not be up-to-date.
+
* {{l/sub|2016}} version - webified version of 2010, with rules finally documented
−
<sql>SELECT
+
* '''2010''' version: see {{l/vbzcart/code|maint/build-cat.php}}
−
  t.ID,
+
* {{l/sub|2009}} version - SQL statements executed directly from PHP code
−
  t.Name,
+
* {{l/sub|2008}} version - started in 2007 - a set of stored queries called from a stored procedure
−
  d.CatNum&"-"&t.CatKey AS CatNum,
+
* {{l/sub|2006}} version
−
  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>
 
  
−
===qryVb_Items_updates===
+
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:
−
<sql>SELECT
+
<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.
−
  BuildOption(g.Code,gt.GroupCode,o.CatKey) AS CatSfx,
+
</blockquote>
−
  t.ID&CatSfx AS IDS_Update,
+
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+).
−
  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>
 
−
 
 
−
===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>
 
−
===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>
 

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"