VbzCart/pieces/catalog/building
Jump to navigation
Jump to search
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.
- 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
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>
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>
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>