VbzCart/docs/pieces/catalog/building

Navigation

VbzCart: 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.

1. 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]
2. 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]
3. Update all data in cat_items from data stored in [calc Items Updates saved]
4. 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>