VbzCart/queries

Navigation
VbzCart: data views

Overview
What MS Access calls "queries" are called "views" in MySQL, i.e. they pull data from existing tables and are themselves usable as data sources in much the same way that tables are (and in which result sets from functions are not).

Some common prefixes:
 * qryCbx_: queries used for filling comboboxes. The unique ID will always be the first field, and the text to display will always be the second field; additional fields may be provided for use in further qryCbx_ queries which show a subset of the results.
 * qryCat_: queries which build catalog numbers or information by joining multiple tables. (In Access, the convention was qryCatNum_.)

Inactive

 * /deprecated: queries we're trying to get rid of, once we're sure nothing uses them
 * /discarded: queries apparently no longer in use

Catalog

 * /qryCat_Depts
 * Title-centric:
 * /qryTitles_Item_info
 * /qryTitles_ItTyps_grpItems
 * /qryTitles_ItTyps_ItTyps
 * /qryTitles_ItTyps_Titles
 * /qryTitles_Imageless - titles with no active images
 * /qryCat_Titles
 * /qryCat_Titles_Item_stats -- item/stock statistics
 * /qryCat_Titles_Item_count -- simpler query for maintenance
 * /qryCat_Titles_web
 * /qryCbx_Titles
 * Item-centric:
 * /qryCat_Items_Stock: cat_items with stock info
 * ItTyp-centric:
 * /qryItTypsDepts_grpItems
 * /qryItTypsDepts_ItTyps
 * Image-centric:
 * /qryImgs_byTitle: Image info by Title
 * /qryCat_pages: maps http path info to catalog entities

Catalog Items

 * /qryCat_Items
 * /qryCbx_Items_data
 * /qryCbx_Items
 * /qryCbx_Items_active
 * /qryCbx_Items_for_sale
 * /qryCbx_Items_opt: abbreviated version for contexts where Title is already known
 * /qryItems_prices: what uses this?

Catalog Sources

 * /qryCtg_Sources_active
 * /qryCtg_Items_updates
 * /qryCtg_Items_updates_joinable
 * /qryCtg_Items_active
 * /qryCtg_Titles_active
 * /qryCtg_build_sub
 * /qryCtg_build
 * building process:
 * /qryCtg_src
 * /qryCtg_src_sub
 * /qryCtg_Items_forUpdJoin
 * /qryCtg_Upd_join
 * /qryCtg_src_dups
 * /qryCtgCk_dup_keys

Catalog Topics

 * titles x topics:
 * /qryTitleTopic_Titles: more title information
 * /qryTitleTopic_Topics: more topic information
 * /qryTitleTopic_Title_avail: title availability information

Carts

 * /qryCarts_info
 * /qrySub_Carts_info_data
 * /qrySub_Carts_info_items

Customers

 * /qryCustAddrs
 * /qryCbx_CustNames

Orders

 * /qryCbx_Orders
 * /qryOrderLines_notPkgd
 * /qryOrders_Active
 * /qryOrderLines_Active
 * /qtyOrderItems_Active
 * /qry_PkgItem_qtys_byOrder
 * /qryOrdItms_Pkg_qtys
 * /qryOrdItms_open
 * /qryItms_open
 * /qryItms_to_restock_union
 * /qryItms_to_restock
 * /qryItms_to_restock_w_info

Packages

 * /qryPkgLines_byOrdLine_andItem
 * /qryOrdLines_PkgdQtys
 * /qryOrdLines_open
 * /qryOrders_Pulled
 * /qryPkgs_Pull_status
 * for reports:
 * /qryRpt_Pkg_Lines
 * /qryPkgLines_qtys_done
 * /qryPkgLines_qtys_done_ord_sum - not used
 * /qryRpt_Pkg_Trx

Restocks

 * all restock requests:
 * /qryRstks_info
 * /qryRstkReq_Item_Rcd_status
 * /qryRstkReq_Item_status
 * /qryRstkReq_Item_status_Req_info
 * /qryRstkReq_Items_expected: show only expected items
 * /qryCbx_RstkReq
 * /qryRstkReq_by_status
 * /qryRstkReq_by_PurchOrd
 * filtered by status:
 * /qryRstks_active: not terminated = !(closed, orphaned or killed)
 * /qryRstkItms_active
 * /qryRstkItms_expected
 * /qryRstkItms_expected_byItem - grouped by ID_Item
 * /qryRstks_unsent: created but not ordered yet
 * /qryRstkItms_unsent
 * /qryRstkItms_unsent_for_order
 * /qryRstks_inactive: all the rest

terminology phases:
 * Active = not "terminated", i.e. not "closed", "killed", or "orphaned" (may or may not be "expected" yet)
 * Closed = received from supplier, nothing remaining on backorder
 * Expected = placed with supplier, but not yet received
 * Killed = canceled with supplier after having been placed
 * Orphaned = we don't have records that anything was received, but nothing further is expected (usually old data)
 * Terminated = closed, orphaned, or killed
 * Unsent = created but not yet placed with supplier, i.e. not "expected"

Shipping

 * /qryPkgs_status

new queries

 * /qryStk_Bins_w_info
 * /qryStk_lines_remaining
 * /qryStk_lines_remaining_byBin
 * /qryStk_lines_remaining_forSale
 * /qryStkItms_for_sale
 * /qryItems_needed_forStock
 * /qryStk_items_remaining
 * /qryStk_byItem_byBin
 * /qryStk_byItem_byBin_wInfo


 * /qryStk_lines_Title_info
 * /qryStkItms_for_sale_wItem_data
 * /qryStock_forOpenOrders
 * /qryStock_byOpt_andType
 * /qryStock_by_Opt_Type
 * /qryStock_by_Supp_Type_Opt - unused (and should be Supp_Opt_Type)
 * /qryStock_Titles_most_recent
 * queries:
 * /qryStock_containers - generates IDS codes
 * /qryStk_History - includes data

old queries
This was the first batch of queries I created, before I had decided to go with the qry prefix as in Access.
 * /v_stk_titles_remaining
 * /v_stk_byItemAndBin_wItemInfo

Caching
Caching should only be used for catalog display.
 * /qryCache_Flow_Procs