VbzCart/pieces/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.
- 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>
(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.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>
(Step 5) qryVb_Items_clear_unlisted
Clears the isInPrint flag for cat_items not found in the calculated in-print listing <sql>UPDATE
cat_items AS i LEFT JOIN qryVb_Items_Results AS r ON r.ID_Item=i.ID SET i.isInPrint = FALSE WHERE r.ID_Item IS NULL;</sql>
(Step 6) qryVb_Items_update_stock
Updates the stock status for all cat_items <sql>UPDATE
cat_items AS i LEFT JOIN /* need a view which gives in-stock quantities by Item */
</sql>
(Step 7) qryVb_Items_update_forsale
Updates the isForSale flag: TRUE if item either is inPrint or isCloseOut or has stock, else FALSE.