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
(→‎qryCbx_Items: working version (refinements later))
(→‎new queries: many more queries no longer in db)
 
(228 intermediate revisions by 2 users not shown)
Line 2: Line 2:
 
[[VbzCart]]: [[VbzCart data views|data views]]
 
[[VbzCart]]: [[VbzCart data views|data views]]
 
==Overview==
 
==Overview==
−
"Views" in [[MySQL]] are like SELECT "queries" in [[MS Access]], 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).
+
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).
−
==Views==
+
 
 
Some common prefixes:
 
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.
 
* '''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_'''.)
−
===qryCat_Depts===
+
==by category==
−
<mysql>CREATE OR REPLACE VIEW qryCat_Depts AS
+
===Inactive===
−
  SELECT
+
* [[/deprecated]]: queries we're trying to [[/discarded|get rid of]], once we're sure nothing uses them
−
    d.ID,
+
* [[/discarded]]: queries apparently no longer in use
−
    d.Name,
+
 
−
    d.Sort,
+
===Catalog===
−
    d.CatKey,
+
* [[/qryCat_Depts]]
−
    d.ID_Supplier,
+
* '''Title'''-centric:
−
    CONCAT_WS('-',s.CatKey,d.CatKey) AS CatNum
+
** [[/qryTitles_Item_info]]
−
  FROM depts AS d LEFT JOIN suppliers AS s ON d.ID_Supplier=s.ID;</mysql>
+
** [[/qryTitles_ItTyps_grpItems]]
−
===qryCat_Titles===
+
** [[/qryTitles_ItTyps_ItTyps]]
−
<mysql>CREATE OR REPLACE VIEW qryCat_Titles AS
+
** [[/qryTitles_ItTyps_Titles]]
−
  SELECT
+
** [[/qryTitles_Imageless]] - titles with no active images
−
    t.ID,
+
** [[/qryCat_Titles]]
−
    t.Name,
+
** [[/qryCat_Titles_Item_stats]] -- item/stock statistics
−
    CONCAT_WS('-',d.CatNum,t.CatKey) AS CatNum,
+
** [[/qryCat_Titles_Item_count]] -- simpler query for maintenance
−
    d.ID_Supplier,
+
** [[/qryCat_Titles_web]]
−
    t.ID_Dept,
+
** [[/qryCbx_Titles]]
−
    t.DateAdded,
+
* '''Item'''-centric:
−
    t.RstkMin AS QtyMin_Rstk,
+
** [[/qryCat_Items_Stock]]: cat_items with stock info
−
    t.Notes,
+
* '''ItTyp'''-centric:
−
    t.Supplier_CatNum AS Supp_CatNum
+
** [[/qryItTypsDepts_grpItems]]
−
  FROM titles AS t LEFT JOIN qryCat_Depts AS d ON t.ID_Dept=d.ID;</mysql>
+
** [[/qryItTypsDepts_ItTyps]]
−
===qryCat_Items===
+
* '''Image'''-centric:
−
<mysql>CREATE OR REPLACE VIEW qryCat_Items AS SELECT
+
** [[/qryImgs_byTitle]]: Image info by Title
−
  i.ID,
+
* [[/qryCat_pages]]: maps http path info to catalog entities
−
  i.ID_Title,
+
====Catalog Items====
−
  (t.ID IS NOT NULL) AS TitleExists,
+
* [[/qryCbx_Items_data]]
−
  t.ID_Supplier,
+
** [[/qryCbx_Items]]
−
  i.PriceSell,
+
** [[/qryCbx_Items_active]]
−
  i.PriceList,
+
** [[/qryCbx_Items_for_sale]]
−
  i.PriceBuy,
+
** [[/qryCbx_Items_opt]]: abbreviated version for contexts where Title is already known
−
  i.CatNum,
+
* [[/qryItems_prices]]: what uses this?
−
  t.CatNum AS Title_CatNum,
+
====Catalog Sources====
−
  t.Name AS Title_Name,
+
* [[/qryCtg_Sources_active]]
−
  i.ID_ItTyp,
+
* [[/qryCtg_Items_updates]]
−
  i.ID_ItOpt,
+
* [[/qryCtg_Items_updates_joinable]]
−
  i.ItOpt_Descr,
+
* [[/qryCtg_Items_active]]
−
  i.ItOpt_Sort,
+
* <s>[[/qryCtg_build_sub]]</s>
−
  CONCAT(t.Name, IF
+
* <s>[[/qryCtg_build]]</s>
−
      (i.ItOpt_Descr IS NULL,'',CONCAT(
+
* '''[[VbzCart catalog building|building]] process''':
−
        ' (',i.ItOpt_Descr,')'
+
** [[/qryCtg_Items_forUpdJoin]]
−
        )
+
** [[/qryCtg_Upd_join]]
−
      )
+
** [[/qryCtg_src_dups]]
−
    ) AS Descr,
+
** [[/qryCtgCk_dup_keys]]
−
  i.Supp_CatNum,
+
 
−
  i.isForSale,
+
====Catalog Topics====
−
  i.isPulled,
+
* '''titles x topics''':
−
  i.QtyMin_Stk,
+
** [[/qryTitleTopic_Titles]]: more title information
−
  t.QtyMin_Rstk AS QtyMin_Rstk_Title
+
** [[/qryTitleTopic_Topics]]: more topic information
−
FROM cat_items AS i LEFT JOIN qryCat_Titles AS t on i.ID_Title=t.ID
+
** [[/qryTitleTopic_Title_avail]]: title availability information
−
ORDER BY t.ID IS NOT NULL, i.CatNum, i.ItOpt_Sort;</mysql>
+
===Ordering===
 +
====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]]
 +
====Packages====
 +
* [[/qryPkgLines_byOrdLine_andItem]]
 +
* [[/qryOrdLines_PkgdQtys]]
 +
* [[/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====
 +
* '''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 - see {{l/vc/term|restock/status}})
 +
*** [[/qryRstks_unsent]]: created but not ordered yet
 +
**** [[/qryRstkItms_unsent]]
 +
***** [[/qryRstkItms_unsent_for_order]]
 +
** [[/qryRstks_inactive]]: all the rest
  
−
* '''MySQL note''': Functions (or CONCAT, anyway) apparently do not like being separated from their opening parenthesis; it seems to resolve the function name as a field name, and (generally) will report that there is no such field.
+
====Shipping====
 +
* [[/qryPkgs_status]]
  
−
===qryCbx_Items===
+
===Stock===
−
<mysql>CREATE OR REPLACE VIEW qryCbx_Items AS
+
====new queries====
−
SELECT
+
* [[/qryStk_Bins_w_info]]
−
  i.ID,
+
* [[/qryStk_lines_remaining]]
−
  CAST(
+
** [[/qryStk_lines_remaining_byBin]]
−
    CONCAT(
+
** [[/qryStk_lines_remaining_forSale]]
−
      if(i.isPulled,
+
*** [[/qryStkItms_for_sale]]
−
        CONCAT('!X '),
 
−
        ''
 
−
      ),
 
−
      if(i.CatNum IS NULL,
 
−
        CONCAT('#',i.ID,' (no cat#)')
 
−
        ,i.CatNum),
 
−
      if(i.Supp_CatNum IS NULL,
 
−
        '',
 
−
        CONCAT(' [',i.Supp_CatNum,']')
 
−
      ),
 
−
      if(i.isForSale,'',' *n/a*'),
 
−
      ' ',
 
−
      if(i.Descr IS NULL,'no description',i.Descr)
 
−
    ) AS CHAR
 
−
  ) AS Descr,
 
−
  i.isForSale
 
−
FROM qryCat_Items AS i
 
−
ORDER BY NOT i.isPulled, i.isForSale, i.CatNum;</mysql>
 
  
−
* Future refinement: no need to display CatNum, Supp_CatNum, i.Descr if i.isPulled
+
* [[/qryStk_items_remaining]]
 +
* [[/qryStk_byItem_byBin]]
  
−
===qryCbx_Orders===
+
* [[/qryStk_lines_Title_info]]
−
<mysql>CREATE OR REPLACE VIEW qryCbx_Orders AS
 
−
SELECT
 
−
  ID,
 
−
  CONCAT_WS(' due ',CAST(Number AS CHAR),DATE_FORMAT(WhenNeeded,'%Y-%m-%d')) AS Descr
 
−
FROM core_orders
 
−
ORDER BY Number DESC;</mysql>
 
−
===qryCbx_Restocks===
 
−
I don't know [[WTF]] is going on here. MySQL wouldn't let me create this as one big query (no error message, just "query cancelled"), so I tried to split it into two parts and union them -- but I get the same error message. For now, I'm just defining the subqueries in MySQL and the UNION in Access. :-P
 
−
<mysql>CREATE OR REPLACE VIEW qryCbx_Restocks_byStatus AS
 
−
SELECT
 
−
  r.ID,
 
−
  CAST(
 
−
    CONCAT(
 
−
      CAST(COALESCE(
 
−
        CONCAT('X ',DATE_FORMAT(r.WhenKilled,'%Y-%m-%d')),
 
−
        CONCAT('R ',DATE_FORMAT(r.WhenReceived,'%Y-%m-%d')),
 
−
        CONCAT('O ',DATE_FORMAT(r.WhenOrdered,'%Y-%m-%d')),
 
−
        CONCAT('D ',DATE_FORMAT(r.WhenDebited,'%Y-%m-%d')),
 
−
        CONCAT('C ',DATE_FORMAT(r.WhenCreated,'%Y-%m-%d'))
 
−
      ) AS BINARY),
 
−
      ' ',
 
−
      s.CatKey,
 
−
      IFNULL(CONCAT(' [',r.PurchOrdNum,']'),'')
 
−
    ) AS CHAR
 
−
  ) AS Descr,
 
−
  NOT IsLocked AS IsOpen,
 
−
  r.WhenOrdered,
 
−
  r.WhenReceived,
 
−
  r.WhenConfirmed,
 
−
  r.ID_Supplier
 
−
FROM core_restocks AS r LEFT JOIN cat_supp AS s ON r.ID_Supplier=s.ID
 
−
ORDER BY COALESCE(r.WhenKilled,r.WhenReceived,r.WhenOrdered,r.WhenDebited,r.WhenCreated);
 
  
−
CREATE OR REPLACE VIEW qryCbx_Restocks_byPurchOrd AS
+
====old queries====
−
SELECT
+
This was the first batch of queries I created, before I had decided to go with the qry prefix as in Access.
−
  r.ID,
+
* [[/v_stk_titles_remaining]]
−
  CAST(r.PurchOrdNum AS CHAR) AS Descr,
+
* [[/v_stk_byItemAndBin_wItemInfo]]
−
  NOT IsLocked AS IsOpen,
 
−
  r.WhenOrdered,
 
−
  r.WhenReceived,
 
−
  r.WhenConfirmed,
 
−
  r.ID_Supplier
 
−
FROM core_restocks AS r WHERE r.PurchOrdNum IS NOT NULL
 
−
ORDER BY r.PurchOrdNum;
 
  
−
CREATE OR REPLACE VIEW qryCbx_Restocks AS
+
===Caching===
−
  SELECT * FROM qryCbx_Restocks_byStatus
+
Caching should only be used for catalog display.
−
UNION
+
* [[/qryCache_Flow_Procs]]
−
  SELECT * FROM qryCbx_Restocks_byPurchOrd;</mysql>
 

Latest revision as of 23:12, 3 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

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://htyp.org/mw/index.php?title=VbzCart/queries&oldid=21772"