Difference between revisions of "VbzCart/queries"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
(→‎Catalog: qryTitles_Imageless)
(→‎by category: obsoleted 2 queries; moved some terminology closer to where it belongs)
Line 34: Line 34:
 
* [[/qryCat_pages]]: maps http path info to catalog entities
 
* [[/qryCat_pages]]: maps http path info to catalog entities
 
====Catalog Items====
 
====Catalog Items====
* [[/qryCat_Items]]
 
 
* [[/qryCbx_Items_data]]
 
* [[/qryCbx_Items_data]]
 
** [[/qryCbx_Items]]
 
** [[/qryCbx_Items]]
Line 61: Line 60:
 
** [[/qryTitleTopic_Topics]]: more topic information
 
** [[/qryTitleTopic_Topics]]: more topic information
 
** [[/qryTitleTopic_Title_avail]]: title availability information
 
** [[/qryTitleTopic_Title_avail]]: title availability information
 
 
===Ordering===
 
===Ordering===
 
====Carts====
 
====Carts====
Line 82: Line 80:
 
* [[/qryItms_to_restock_union]]
 
* [[/qryItms_to_restock_union]]
 
* [[/qryItms_to_restock]]
 
* [[/qryItms_to_restock]]
* [[/qryItms_to_restock_w_info]]
 
 
====Packages====
 
====Packages====
 
* [[/qryPkgLines_byOrdLine_andItem]]
 
* [[/qryPkgLines_byOrdLine_andItem]]
Line 106: Line 103:
 
*** [[/qryRstkReq_by_PurchOrd]]
 
*** [[/qryRstkReq_by_PurchOrd]]
 
* '''filtered by status''':
 
* '''filtered by status''':
** [[/qryRstks_active]]: not terminated = !(closed, orphaned or killed)
+
** [[/qryRstks_active]]: not terminated = !(closed, orphaned or killed - see {{l/vc/terms|restock/status}})
 
*** [[/qryRstkItms_active]]
 
*** [[/qryRstkItms_active]]
 
**** [[/qryRstkItms_expected]]
 
**** [[/qryRstkItms_expected]]
Line 114: Line 111:
 
***** [[/qryRstkItms_unsent_for_order]]
 
***** [[/qryRstkItms_unsent_for_order]]
 
** [[/qryRstks_inactive]]: all the rest
 
** [[/qryRstks_inactive]]: all the rest
 
'''terminology'''
 
* '''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"
 
'''phases''':
 
{| border=1
 
|-
 
| colspan=5 | Time →
 
|-
 
| '''Created''' || (Unsent) || '''Ordered''' || (Expected) || '''Closed''', '''Killed''', or '''Orphaned'''
 
|-
 
| colspan=4 align=center | --(Active)-- || align=center | --(Terminated/Inactive)--
 
|}
 
  
 
====Shipping====
 
====Shipping====

Revision as of 22:34, 2 March 2016

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

  1. REDIRECT Template:l/vc/table 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.