VbzCart/docs/queries: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs
Jump to navigation Jump to search
imported>Woozle
(→‎Restocks: more queries, plus some reorg due to refined terminology; added terminology listing)
m (Woozle moved page VbzCart/VbzCart/queries to VbzCart/docs/queries without leaving a redirect: part 2)
 
(41 intermediate revisions by one other user not shown)
Line 7: Line 7:
* '''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.
* '''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_'''.)
* '''qryCat_''': queries which build catalog numbers or information by joining multiple tables. (In Access, the convention was '''qryCatNum_'''.)
==by category==
===Inactive===
===Inactive===
* [[/deprecated]]: queries we're trying to [[/discarded|get rid of]], once we're sure nothing uses them
* [[/deprecated]]: queries we're trying to [[/discarded|get rid of]], once we're sure nothing uses them
Line 13: Line 14:
===Catalog===
===Catalog===
* [[/qryCat_Depts]]
* [[/qryCat_Depts]]
* [[/qryTitles_ItTyps_grpItems]]
* '''Title'''-centric:
* [[/qryTitles_ItTyps_ItTyps]]
** [[/qryTitles_Item_info]]
* [[/qryTitles_ItTyps_Titles]]
** [[/qryTitles_ItTyps_grpItems]]
* [[/qryItTypsDepts_grpItems]]
** [[/qryTitles_ItTyps_ItTyps]]
* [[/qryItTypsDepts_ItTyps]]
** [[/qryTitles_ItTyps_Titles]]
* [[/qryCat_Titles]]
** [[/qryTitles_Imageless]] - titles with no active images
* [[/qryCat_Titles_Item_stats]]
** [[/qryCat_Titles]]
* [[/qryCat_Titles_web]]
** [[/qryCat_Titles_Item_stats]] -- item/stock statistics
* [[/qryCbx_Titles]]
** [[/qryCat_Titles_Item_count]] -- simpler query for maintenance
===Catalog Items===
** [[/qryCat_Titles_web]]
* [[/qryCat_Items]]
** [[/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====
* [[/qryCbx_Items_data]]
* [[/qryCbx_Items_data]]
** [[/qryCbx_Items]]
** [[/qryCbx_Items]]
Line 30: Line 40:
** [[/qryCbx_Items_opt]]: abbreviated version for contexts where Title is already known
** [[/qryCbx_Items_opt]]: abbreviated version for contexts where Title is already known
* [[/qryItems_prices]]: what uses this?
* [[/qryItems_prices]]: what uses this?
 
====Catalog Sources====
===Catalog Sources===
* [[/qryCtg_Sources_active]]
* [[/qryCtg_Sources_active]]
* [[/qryCtg_Items_updates]]
* [[/qryCtg_Items_updates]]
* [[/qryCtg_Items_updates_joinable]]
* [[/qryCtg_Items_updates_joinable]]
* [[/qryCtg_Items_active]]
* [[/qryCtg_Items_active]]
* [[/qryCtg_Titles_active]]
* <s>[[/qryCtg_build_sub]]</s>
* [[/qryCtg_build_sub]]
* <s>[[/qryCtg_build]]</s>
* [[/qryCtg_build]]
* '''[[VbzCart catalog building|building]] process''':
** [[/qryCtg_Items_forUpdJoin]]
** [[/qryCtg_Upd_join]]
** [[/qryCtg_src_dups]]
** [[/qryCtgCk_dup_keys]]


===Orders===
====Catalog Topics====
* '''titles x topics''':
** [[/qryTitleTopic_Titles]]: more title information
** [[/qryTitleTopic_Topics]]: more topic information
** [[/qryTitleTopic_Title_avail]]: title availability information
===Ordering===
====Carts====
* [[/qryCarts_info]]
** [[/qrySub_Carts_info_data]]
** [[/qrySub_Carts_info_items]]
====Customers====
* [[/qryCustAddrs]]
* [[/qryCbx_CustNames]]
====Orders====
* [[/qryCbx_Orders]]
* [[/qryCbx_Orders]]
* [[/qryOrderLines_notPkgd]]
* [[/qryOrderLines_notPkgd]]
Line 52: Line 78:
* [[/qryItms_to_restock_union]]
* [[/qryItms_to_restock_union]]
* [[/qryItms_to_restock]]
* [[/qryItms_to_restock]]
* [[/qryItms_to_restock_w_info]]
====Packages====
 
===Customers===
* [[/qryCustAddrs]]
 
===Packages===
* [[/qryPkgLines_byOrdLine_andItem]]
* [[/qryPkgLines_byOrdLine_andItem]]
* [[/qryOrdLines_PkgdQtys]]
* [[/qryOrdLines_PkgdQtys]]
* [[/qryOrdLines_open]]
* [[/qryOrdLines_open]]
* [[/qryOrders_Pulled]]
* [[/qryPkgs_Pull_status]]
* for reports:
** [[/qryRpt_Pkg_Lines]]
*** [[/qryPkgLines_qtys_done]]
*** <s>[[/qryPkgLines_qtys_done_ord_sum]]</s> - not used
** [[/qryRpt_Pkg_Trx]]


===Restocks===
====Restocks====
* '''all restocks''':
* '''all restock requests''':
** [[/qryRstks_info]]
** [[/qryRstks_info]]
** [[/qryRstkReq_Item_Rcd_status]]
** [[/qryRstkReq_Item_Rcd_status]]
** [[/qryRstkReq_Item_status]]
** [[/qryRstkReq_Item_status]]
** [[/qryRstkReq_Item_status_Req_info]]
*** [[/qryRstkReq_Item_status_Req_info]]
* '''active restocks''':
*** [[/qryRstkReq_Items_expected]]: show only expected items
** [[/qryRstks_active]]: not terminated = !(closed, orphaned or killed)
** [[/qryCbx_RstkReq]]
** [[/qryRstkItms_active]]
*** [[/qryRstkReq_by_status]]
** [[/qryRstks_unsent]]: created but not ordered yet
*** [[/qryRstkReq_by_PurchOrd]]
** [[/qryRstkItms_unsent]]
* '''filtered by status''':
** [[/qryRstkItms_unsent_for_order]]
** [[/qryRstks_active]]: not terminated = !(closed, orphaned or killed - see {{l/vc/term|restock/status}})
** [[/qryRstkItms_expected]]
*** [[/qryRstks_unsent]]: created but not ordered yet
====terminology====
**** [[/qryRstkItms_unsent]]
* '''Active''' = not "terminated", i.e. not "closed", "killed", or "orphaned" (may or may not be "expected" yet)
***** [[/qryRstkItms_unsent_for_order]]
* '''Closed''' = received from supplier, nothing remaining on backorder
** [[/qryRstks_inactive]]: all the rest
* '''Expected''' = placed with supplier, but not yet received
 
* '''Killed''' = canceled with supplier after having been placed
====Shipping====
* '''Orphaned''' = we don't have records that anything was received, but nothing further is expected (usually old data)
* [[/qryPkgs_status]]
* '''Terminated''' = closed, orphaned, or killed
* '''Unsent''' = created but not yet placed with supplier, i.e. not "expected"
Phases:
{| border=1
|-
| colspan=5 | Time &rarr;
|-
| '''Created''' || (Unsent) || '''Ordered''' || (Expected) || '''Closed''', '''Killed''', or '''Orphaned'''
|-
| colspan=4 align=center | --(Active)-- || align=center | --(Terminated)--
|}


===Stock===
===Stock===
====new queries====
* [[/qryStk_Bins_w_info]]
* [[/qryStk_lines_remaining]]
** [[/qryStk_lines_remaining_byBin]]
** [[/qryStk_lines_remaining_forSale]]
*** [[/qryStkItms_for_sale]]
* [[/qryStk_items_remaining]]
* [[/qryStk_byItem_byBin]]
* [[/qryStk_lines_Title_info]]
====old queries====
This was the first batch of queries I created, before I had decided to go with the qry prefix as in Access.
This was the first batch of queries I created, before I had decided to go with the qry prefix as in Access.
* [[/v_stk_lines_remaining]]
* [[/v_stk_items_remaining]]
* [[/v_stk_titles_remaining]]
* [[/v_stk_titles_remaining]]
* [[/v_stk_byItemAndBin]]
* [[/v_stk_byItemAndBin_wItemInfo]]
* [[/v_stk_byItemAndBin_wItemInfo]]
* [[/v_stk_byItemAndBin_wInfo]]
* [[/qryStk_lines_remaining_forSale]]
* [[/qryStkItms_for_sale]]
* [[/qryStkItms_for_sale_wItem_data]]
* [[/qryStk_lines_Title_info]]
* [[/qryStock_forOpenOrders]]
* [[/qryStock_byOpt_andType]]
* [[/qryStock_Titles_most_recent]]


===Caching===
===Caching===
Caching should only be used for catalog display.
Caching should only be used for catalog display.
====v_data_flow====
* [[/qryCache_Flow_Procs]]
<section begin=sql /><mysql>  CREATE OR REPLACE VIEW v_data_flow AS
    SELECT
      df.ID_Srce,
      df.ID_Dest,
      df.ID_Proc,
      dfx.doesClear
    FROM data_flow AS df LEFT JOIN data_procs AS dfx ON df.ID_Proc=dfx.ID
    ORDER BY dfx.doesClear;</mysql>
<section end=sql />

Latest revision as of 01:55, 25 February 2024

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_.)

by category

Inactive

Catalog

Catalog Items

Catalog Sources

Catalog Topics

Ordering

Carts

Customers

Orders

Packages

Restocks

Shipping

Stock

new queries

old queries

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

Caching

Caching should only be used for catalog display.

Retrieved from "https://wooz.dev/mw/index.php?title=VbzCart/docs/queries&oldid=6723"