Difference between revisions of "VbzCart/queries"
(→Packages: moved queries into separate pages) |
(→Restocks: moved deprecated queries to deprecated page, and the others to separate pages) |
||
Line 63: | Line 63: | ||
===Restocks=== | ===Restocks=== | ||
− | + | * [[/qryRstks_info]] | |
− | + | * [[/qryRstks_active]] | |
− | + | * [[/qryRstkItms_active]] | |
− | + | * [[/qryRstkItms_en_route]] | |
− | + | * [[/qryRstkReq_Item_Rcd_status]] | |
− | + | * [[/qryRstkReq_Item_status]] | |
− | + | * [[/qryRstkReq_Item_status_Req_info]] | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | * | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | * | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | * | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | * | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | * | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | * | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | * | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
===Stock=== | ===Stock=== |
Revision as of 00:36, 4 December 2008
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
- /qryTitles_ItTyps_grpItems
- /qryTitles_ItTyps_ItTyps
- /qryTitles_ItTyps_Titles
- /qryItTypsDepts_grpItems
- /qryItTypsDepts_ItTyps
- /qryCat_Titles
- /qryCat_Titles_Item_stats
- /qryCat_Titles_web
- /qryCbx_Titles
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
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
Customers
Packages
Restocks
- /qryRstks_info
- /qryRstks_active
- /qryRstkItms_active
- /qryRstkItms_en_route
- /qryRstkReq_Item_Rcd_status
- /qryRstkReq_Item_status
- /qryRstkReq_Item_status_Req_info
Stock
v_stk_lines_remaining
View listing stk_items actually in stock, which is actually different things:
- "forSale": items which are visible as "in stock" to customers
- "forShip": items which are available for shipping
- items neither for shipping nor for sale (probably some kind of recordkeeping purpose)
<mysql>CREATE OR REPLACE VIEW v_stk_lines_remaining AS
SELECT st.ID, st.ID_Bin, st.ID_Item, IF(sb.isForSale,st.Qty,0) AS QtyForSale, IF(sb.isForShip,st.Qty,0) AS QtyForShip, st.Qty AS QtyExisting, st.CatNum, st.WhenAdded, st.WhenChanged, st.WhenCounted, st.Notes, sb.ID_Place, sp.Name AS WhName FROM ( stk_items AS st LEFT JOIN stk_bins AS sb ON sb.ID=st.ID_Bin ) LEFT JOIN stk_places AS sp ON sb.ID_Place=sp.ID WHERE (st.WhenRemoved IS NULL) AND (sb.WhenVoided IS NULL) AND (st.Qty <> 0);</mysql>
v_stk_items_remaining
<mysql>CREATE OR REPLACE VIEW v_stk_items_remaining AS
SELECT ID_Item, SUM(QtyForSale) AS QtyForSale, SUM(QtyForShip) AS QtyForShip, SUM(QtyExisting) AS QtyExisting FROM v_stk_lines_remaining GROUP BY ID_Item;</mysql>
v_stk_titles_remaining
<mysql>CREATE OR REPLACE VIEW v_stk_titles_remaining AS
SELECT ID_Title, SUM(QtyForSale) AS QtyForSale, SUM(QtyForShip) AS QtyForShip, SUM(QtyExisting) AS QtyExisting FROM v_stk_lines_remaining AS s LEFT JOIN cat_items AS i ON s.ID_Item=i.ID GROUP BY i.ID_Title;</mysql>
v_stk_byItemAndBin
<mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin AS
SELECT ID_Item, ID_Bin, SUM(QtyForSale) AS QtyForSale, SUM(QtyForShip) AS QtyForShip, SUM(QtyExisting) AS QtyExisting FROM v_stk_lines_remaining GROUP BY ID_Item, ID_Bin HAVING SUM(QtyExisting)>0;</mysql>
v_stk_byItemAndBin_wItemInfo
Is anything actually using this? Document! (I've tentatively removed the link in Access.)
<mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin_wItemInfo AS
SELECT * FROM v_stk_byItemAndBin AS s LEFT JOIN qryCat_Items AS i ON s.ID_Item=i.ID;</mysql>
v_stk_byItemAndBin_wInfo
<mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin_wInfo AS
SELECT s.*, i.*, b.ID_Place, b.Code AS BinCode, b.Descr AS BinDescr FROM ( v_stk_byItemAndBin AS s LEFT JOIN qryCat_Items AS i ON s.ID_Item=i.ID ) LEFT JOIN stk_bins AS b ON s.ID_Bin=b.ID;</mysql>
qryStk_lines_remaining_forSale
- Requires: #v_stk_lines_remaining
- Used by: #qryStkItms_for_sale
- Note: Eventually rename as "qryStkLines_for_sale"
<mysql>CREATE OR REPLACE VIEW qryStk_lines_remaining_forSale AS
SELECT * FROM v_stk_lines_remaining WHERE QtyForSale>0;</mysql>
qryStkItms_for_sale
- Requires: #qryStk_lines_remaining_forSale
- Used by: #qryStkItms_for_sale_wItem_data
- History:
- 2008-11-17 Created for revised restocking process
<mysql>CREATE OR REPLACE VIEW qryStkItms_for_sale AS SELECT
ID_Item, SUM(qsl.QtyForSale) AS QtyForSale, SUM(qsl.QtyForShip) AS QtyForShip, SUM(qsl.QtyExisting) AS QtyExisting
FROM qryStk_lines_remaining_forSale AS qsl GROUP BY ID_Item;</mysql>
qryStkItms_for_sale_wItem_data
- Requires: #qryStkItms_for_sale
- Note: this one may be redundant; the time when we need the item data is *after* all the sources of items-to-restock have been UNIONed together and GROUPed by item
- History:
- 2008-11-17 Created for revised restocking process
<mysql>CREATE OR REPLACE VIEW qryStkItms_for_sale_wItem_data AS SELECT
qsi.*, i.*
FROM qryStkItms_for_sale AS qsi LEFT JOIN cat_items AS i ON qsi.ID_Item=i.ID;</mysql>
qryOrdLines_ord_status
<mysql>CREATE OR REPLACE VIEW qryOrdLines_ord_status AS SELECT
i.ID AS ID_Item, i.CatNum, qoi.ID AS ID_OrdLine, qoi.ID_Order, qsr.ID AS ID_Stock, qri.ID AS ID_RstkItem, IFNULL(qoi.QtyOrd,0) AS QtyOrd, IFNULL(qoi.QtyDone,0) AS QtyDone, IFNULL(qri.QtyExp IS NULL,IFNULL(qri.QtyOrd,0)) AS QtyOnRstk, IFNULL(qsr.QtyForShip,0) AS QtyOnHand, i.QtyMin_Stk AS QtyMin, qoi.WhenOldestOrder, qoi.WhenSoonestDue
FROM
( (cat_items AS i LEFT JOIN qryOrdLines_open AS qoi ON qoi.ID_Item=i.ID) LEFT JOIN qryStk_lines_remaining_forSale AS qsr ON qsr.ID_Item=i.ID) LEFT JOIN qryRstkLines_en_route AS qri ON qri.ID_Item=i.ID;</mysql>
qryOrdLines_to_restock
- Depends on: #qryOrdLines_ord_status
<mysql>CREATE OR REPLACE VIEW qryOrdLines_to_restock AS SELECT
ID_Item, i.ID_OrdLine, i.ID_Order, Sum(i.QtyOrd) AS QtyOrd, Sum(i.QtyDone) AS QtyDone, Sum(i.QtyOnRstk) AS QtyOnRstk, Sum(i.QtyOnHand) AS QtyOnHand, Max(i.QtyMin) AS QtyMin, Max(IFNULL(i.QtyMin,0))+Sum(IFNULL(i.QtyOrd,0)-IFNULL(i.QtyOnRstk,0)-IFNULL(i.QtyOnHand,0)) AS QtyToGet, Sum(IFNULL(i.QtyOrd,0)-IFNULL(i.QtyOnRstk,0)-IFNULL(i.QtyOnHand,0)) AS QtyShort, Min(i.WhenOldestOrder) AS WhenOldestOrder, Min(i.WhenSoonestDue) AS WhenSoonestDue, Count(ID_RstkItem) AS RstkLines
FROM qryOrdLines_ord_status AS i GROUP BY ID_Item, ID_OrdLine, ID_Order HAVING Max(IFNULL(i.QtyMin,0))+Sum(IFNULL(i.QtyOrd,0)-IFNULL(i.QtyOnRstk,0)-IFNULL(i.QtyOnHand,0))>0;</mysql>
qryFrm_OrdLines_to_restock
Depends on: #qryOrdLines_to_restock
<mysql>CREATE OR REPLACE VIEW qryFrm_OrdLines_to_restock AS SELECT
qi.*, ci.CatNum, ci.isForSale, ci.ID_Supplier, ci.PriceSell, ci.PriceBuy
FROM qryOrdLines_to_restock AS qi
LEFT JOIN qryCat_Items AS ci ON ci.ID=qi.ID_Item;</mysql>
qryStk_lines_Title_info
<mysql>CREATE OR REPLACE VIEW qryStk_lines_Title_info AS SELECT
s.*, i.ID_Title
FROM stk_items AS s LEFT JOIN cat_items AS i ON s.ID_Item=i.ID;</mysql>
qryOrdLines_status_union
- Requires: #qryOrdLines_open, #qryRstkLines_en_route, #v_stk_lines_remaining
- Notes: This is another one of those union queries that is "cancelled" while you're trying to create it... but in this case, it does get created.
<mysql>CREATE OR REPLACE VIEW qryOrdLines_status_union AS SELECT /* 1 */ CONCAT("oio-",oio.ID) AS Source, /* 2 */ oio.ID AS ID, /* 3 */ oio.ID_Order, /* 4 */ oio.ID_Item, /* 5 */ oio.QtyOrd, /* 6 */ oio.QtyDone, /* 7 */ 0 AS QtyOnRstk, /* 8 */ 0 AS QtyOnHand, /* 9 */ 0 AS QtyMin, /* 10 */ oio.WhenStarted, /* 11 */ oio.WhenNeeded
FROM qryOrdLines_open AS oio
UNION SELECT /* 1 */ CONCAT("rie-",rie.ID) AS Source, /* 2 */ rie.ID AS ID, /* 3 */ rie.ID_Order, /* 4 */ rie.ID_Item, /* 5 */ 0 AS QtyOrd, /* 6 */ 0 AS QtyDone, /* 7 */ IF(rie.QtyExp > rie.QtyOrd,rie.QtyExp,rie.QtyOrd) AS QtyOnRstk, /* 8 */ 0 AS QtyOnHand, /* 9 */ 0 AS QtyMin, /* 10 */ NULL AS WhenStarted, /* 11 */ NULL AS WhenNeeded
FROM qryRstkLines_en_route AS rie
UNION SELECT /* 1 */ CONCAT("srs-",srs.ID) AS Source, /* 2 */ 0 AS ID, /* 3 */ NULL AS ID_Order, /* 4 */ srs.ID_Item, /* 5 */ 0 AS QtyOrd, /* 6 */ 0 AS QtyDone, /* 7 */ 0 AS QtyOnRstk, /* 8 */ srs.QtyForShip AS QtyOnHand, /* 9 */ 0 AS QtyMin, /* 10 */ NULL AS WhenStarted, /* 11 */ NULL AS WhenNeeded
FROM v_stk_lines_remaining AS srs
UNION SELECT /* 1 */ CONCAT("itm-",itm.ID) AS Source, /* 2 */ NULL AS ID, /* 3 */ NULL AS ID_Order, /* 4 */ itm.ID AS ID_Item, /* 5 */ 0 AS QtyOrd, /* 6 */ 0 AS QtyDone, /* 7 */ 0 AS QtyOnRsk, /* 8 */ 0 AS QtyOnHand, /* 9 */ QtyMin_Stk AS QtyMin, /* 10 */ NULL AS WhenStarted, /* 11 */ NULL AS WhenNeeded
FROM cat_items AS itm;</mysql>
- History:
- 2008-11-02 changed When field names (more accurate)
qryItems_ord_status
- Depends on: #qryOrdLines_status_union
- Returns: Status with regard to ordering, i.e. quantities on hand, quantities needed, etc. Used by the "items needed (for restocking)" form, via #qryItems_to_restock. This was originally in a different section, but it has to be created after qryOrdLines_status_union.
<mysql>CREATE OR REPLACE VIEW qryItems_ord_status AS SELECT
i.ID_Item, SUM(i.QtyOrd) AS QtyOrd, SUM(i.QtyDone) AS QtyDone, SUM(i.QtyOnRstk) AS QtyOnRstk, SUM(i.QtyOnHand) AS QtyOnHand, MAX(i.QtyMin) AS QtyMin, SUM( IFNULL(i.QtyOrd,0)-IFNULL(i.QtyOnRstk,0)-IFNULL(i.QtyOnHand,0) )+IFNULL(MAX(i.QtyMin),0) AS QtyToGet, MIN(i.WhenStarted) AS WhenOldestOrder, MAX(i.WhenNeeded) AS WhenSoonestDue, COUNT(i.Source) AS RecCount, CAST(GROUP_CONCAT(DISTINCT i.Source SEPARATOR ' ') AS CHAR) AS Sources
FROM qryOrdLines_status_union AS i GROUP BY i.ID_Item HAVING QtyToGet>0;</mysql>
- History:
- 2008-11-02 mods to accommodate rewritten source data
qryItems_to_restock
Depends on: #qryItems_ord_status
<mysql>CREATE OR REPLACE VIEW qryItems_to_restock AS SELECT
qi.*, ci.CatNum, ci.isForSale, ci.ID_Supplier, ci.QtyMin_Stk, ci.QtyMin_Rstk_Title, ci.PriceSell, ci.PriceBuy
FROM qryItems_ord_status AS qi LEFT JOIN qryCat_Items AS ci ON ci.ID=qi.ID_Item WHERE QtyToGet>0;</mysql>
qryStock_forOpenOrders
- Action: Show all stock available to fill open orders, along with helpful information about the Bin, Order, and Item
- Depends on: #v_stk_byItemAndBin_wInfo, #qryOrderLines_notPkgd, #qryCbx_Orders
<mysql>CREATE OR REPLACE VIEW qryStock_forOpenOrders AS SELECT
s.ID_Bin, s.ID_Item, oi.ID_Order, oi.QtyOpen, s.QtyForSale, s.QtyForShip, o.WhenNeeded, o.Descr AS OrdText, o.ID_Pull, CONCAT_WS(' ',s.CatNum,s.Descr) AS ItemText,
/* s.BinCode,
s.BinDescr, are these ever needed? */ CONCAT_WS(' ',s.BinCode,s.BinDescr) AS BinText
FROM (v_stk_byItemAndBin_wInfo AS s LEFT JOIN qryOrderLines_notPkgd AS oi ON s.ID_Item=oi.ID_Item) LEFT JOIN qryCbx_Orders AS o ON oi.ID_Order=o.ID WHERE (NOT s.isPulled) AND (o.ID_Pull IS NULL) AND (QtyOpen) AND (QtyForShip);</mysql>
- There's a possibility this query will return wrong information when there are multiple stock lines corresponding to the items needed; further testing would be a good idea.
qryStock_byOpt_andType
This is used for displaying the "stock" area. The basic records are items, but they're only ones that are in stock, so "Stock" is shorthand for "items that are in stock" in this case.
<mysql>CREATE OR REPLACE VIEW qryStock_byOpt_andType AS SELECT
i.ID_ItTyp, i.ID_ItOpt, SUM(i.qtyInStock) AS qtyInStock, it.NameSng AS It_NameSng, it.NamePlr AS It_NamePlr, io.CatKey AS Io_CatKey, io.Descr AS Io_Descr, IFNULL(it.Sort,'ZZZ') AS It_Sort
FROM (cat_items AS i LEFT JOIN cat_ioptns AS io ON i.ID_ItOpt=io.ID)
LEFT JOIN cat_ittyps AS it ON i.ID_ItTyp=it.ID
GROUP BY i.ID_ItTyp, i.ID_ItOpt, io.Sort HAVING SUM(i.qtyInStock) ORDER BY It_Sort,io.Sort;</mysql>
qryStock_Titles_most_recent
This defines what the "latest stock" page shows; as given here, it displays the 100 titles most recently added to stock.
<mysql>CREATE OR REPLACE VIEW qryStock_Titles_most_recent AS SELECT
i.ID_Title, s.QtyForSale AS QtyAdded, MAX(s.WhenAdded) AS WhenAdded, st.QtyForSale AS QtyAvail
FROM (v_stk_lines_remaining AS s LEFT JOIN cat_items AS i ON s.ID_Item=i.ID) LEFT JOIN v_stk_titles_remaining AS st ON i.ID_Title=st.ID_Title GROUP BY i.ID_Title HAVING QtyAdded ORDER BY WhenAdded DESC LIMIT 100;</mysql>
Caching
Caching should only be used for catalog display.
v_data_flow
<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>