VbzCart/pieces/catalog/building
< VbzCart | pieces | catalog
Jump to navigation
Jump to search
Revision as of 01:28, 23 August 2007 by Woozle (talk | contribs) (→qryVb_Items_updates_new: step 1a)
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
<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
<sql>UPDATE
[calc Item Updates saved] AS s LEFT JOIN cat_items AS i ON (i.ID_Title=s.ID_Title) AND (i.ID_ItTyp=s.ID_ItTyp) AND (i.ID_ItOpt=s.ID_ItOpt) 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>