VbzCart/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. Right now this process does not "recycle" junked records in cat_items; perhaps that was a bad idea to begin with, or perhaps some other use can be found for junked records that isn't part of catalog-building. There probably should be some process which removes junked records from use so they aren't taking up space and CPU cycles.

1. Update the list of all catalog-based items ([calc Items Updates saved])
   • #(Step 1) qryVb_Items_updates_append -- Add any new items to [calc Items Updates saved]
     • #(Step 1a) qryVb_Items_updates_new -- in-print items that are new
       • #(Step 1aa) qryVb_Items_updates -- all the items currently available from suppliers (in print)
     • #(Step 1b) qryVb_Items_updates_copy_fields
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]
   • #(Step 2) qryVb_Items_results_append -- 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.
     • #(Step 2a) qryVb_Items_results
3. Fill in any blank ID_Item fields in [calc Items Updates saved] records:
   • #(Step 3) qryVb_Items_updates_index_new -- 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).
4. Copy calculated item data over to [cat_items]:
   • #(Step 4) qryVb_Items_results_update
5. Final flag updates:
   • #(Step 5) qryVb_Items_update_final
     • Clear isInPrint flag for cat_items not found in calculated in-print listing
     • Set isForSale for cat_items with stock for sale
     • Update stock quantities for all cat_items
6. Housekeeping: mark table as updated so cached tables get recalculated
   • #(Step 6) qryVb_Items_mark_updated

SQL - tables

src_ctlgs

Source Catalogs - a list of catalogs (past and present) each supplier has provided. A catalog indicates which items the supplier has available for sale at any given time. Any given catalog can be superceded by one other catalog; items in a superceded catalog are no longer available. Groups of items can also be discontinued individually. <mysql>CREATE TABLE `src_ctlgs` (

  `ID`         INT NOT NULL AUTO_INCREMENT,
  `Name`       VARCHAR(63) COMMENT "name of catalog (for lists)",
  `Abbr`       VARCHAR(7) COMMENT "abbreviation",
  `ID_Supp`    INT NOT NULL COMMENT "supplier whose catalog this is",
  `ID_Repl`    INT DEFAULT NULL COMMENT "ID of replacement catalog",
  `WhenAvail`  DATETIME DEFAULT NULL "earliest active date",
  `isCloseOut` BOOL COMMENT "this is a close-out listing?",
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;</mysql>

src_groups

Source Groups: sort of like a set of options which are available for one or more titles, e.g. many suppliers will have a large number of their t-shirts all available in the same set of sizes, with the same set of prices; we define a src_group for that. They may also have a smaller number of those shirts available in a different set of sizes/prices, e.g. longsleeve; that becomes another src_group. <mysql>CREATE TABLE `src_groups` (

  `ID`         INT NOT NULL AUTO_INCREMENT,
  `ID_Supp`    INT NOT NULL COMMENT "Supplier whose group this is",
  `Name`       VARCHAR(63) NOT NULL COMMENT "Name of group (internal use)",
  `Code`       VARCHAR(7) DEFAULT NULL COMMENT "Suffix code for catalog numbers",
  `SortGroup`  VARCHAR(31) DEFAULT NULL COMMENT "Sorting key withing group list",
  `ID_Redir`   INT DEFAULT NULL,
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;</mysql>

• There was also a SortTitle field (formerly TitleSort) for "sorting key within list for title", but it was all NULL. I don't remember if this is something there was a specific need for, or something which seemed like a good idea at the time but never was needed.
• ID_Redir does have data, but I need to figure out if it's actually necessary or if there's a better way of doing what it was supposed to do.

src_grp_items

Source Group Items - defines the list of items which must be added to cat_items for each title in a group <mysql>CREATE TABLE `src_groups` (

  `ID`         INT NOT NULL AUTO_INCREMENT,
  `ID_Group`   INT NOT NULL COMMENT "src_grp to which this definition applies",
  `isActive`   BOOL DEFAULT FALSE COMMENT "if FALSE, don't use this record to build items",

/* these fields are copied verbatim to cat_items: */

  `ID_ItTyp`   INT DEFAULT NULL COMMENT "cat_ittyps.ID: value for field when adding to cat_items",
  `ID_ItOpt`   INT DEFAULT NULL COMMENT "cat_ioptns.ID: value for field when adding to cat_items",
  `PriceBuy`   DECIMAL(9,2) COMMENT "wholesale price from supplier",
  `PriceSell`  DECIMAL(9,2) COMMENT "price to customer",
  `PriceList`  DECIMAL(9,2) COMMENT "supplier's retail list price (if available)",
  `ID_ShipCost 

/* these fields are used as the basis for values in cat_items: */

  `Descr`      VARCHAR(63) COMMENT "descriptive text for this particular option within the group",
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;</mysql>

• Descr is used in at least a couple of different ways when adding the item to cat_items; to be documented

src_grp_titles

Source Group Titles - defines the titles in each group (or groups in each title, if you prefer... many-to-many relationship). <mysql>CREATE TABLE `src_groups` (

  `ID`         INT NOT NULL AUTO_INCREMENT,
  `ID_Group`   INT NOT NULL COMMENT "src_grp to which the title now belongs",
  `ID_Catalog` INT NOT NULL COMMENT "src_ctlgs.ID: catalog which lists this title as available with the given options",
  `isActive`   BOOL DEFAULT FALSE "if FALSE, don't use this group for this title",
  `WhenDiscon` DATETIME DEFAULT NULL "when this title was discontinued in this group",
  `ID_ItOpt`   INT DEFAULT NULL COMMENT "cat_ioptns.ID: value for field when adding to cat_items",
  `GroupCode`  VARCHAR(7) DEFAULT NULL COMMENT "catalog code extension for this title group (optional)",
  `GroupDescr` VARCHAR(63) DEFAULT NULL COMMENT "description extension for this title group (required if GroupCode is used)",
  `GroupSort`  VARCHAR(7) DEFAULT NULL COMMENT "sorting key; use GroupDescr by default",
  `Supp_CatNum` VARCHAR(63) DEFAULT NULL COMMENT "title catalog # for restock from supplier",
  PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;</mysql>

• Supp_CatNum: Some suppliers have different catalog numbers for different types of items, e.g. longsleeves, rather than just a different suffix on the same catalog number. This helps keep track of those variations a little bit, though it could probably be made better use of.
• There was also a Notes memo field, with occasional text in it; I'm thinking this is either obsolete or should be more like a log, with a timestamp. Wait to see if it's needed before re-implementing.

SQL - queries

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.isPulled = FALSE,
   i.isDumped = FALSE,
   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>

Changes:

• Must clear isPulled and isDumped, in case pulled items are returned to service

(Step 5) qryVb_Items_update_final

• Clear isInPrint flag for cat_items not found in calculated in-print listing
• Set isForSale for cat_items with stock for sale
• Update stock quantities for all cat_items

<sql>UPDATE

 ( cat_items AS i
   LEFT JOIN qryVb_Items_Results AS r
     ON r.ID_Item=i.ID
  )
  LEFT JOIN v_stk_items_remaining AS s
    ON s.ID_Item=i.ID
 SET
   i.isInPrint = nz(r.isInPrint),
   i.isCloseOut = nz(r.isCloseOut),
   i.isForSale = nz(r.isInPrint)
     OR nz(r.isCloseOut)
     OR (s.QtyForSale > 0),
   i.qtyInStock = s.QtyForSale;</sql>

(Step 6) qryVb_Items_mark_updated

<sql>UPDATE

 data_tables

SET WhenUpdated=Now() WHERE Name="cat_items"</sql>