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
(→‎qryStock_forOpenOrders: should only show stock which can actually be shipped)
(→‎new queries: many more queries no longer in db)
 
(201 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==
 
−
"Views" in [[MySQL]] are essentially identical to stored queries in [[MS Access]].
 
  
 
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,
 
−
    d.CatKey,
 
−
    d.ID_Supplier,
 
−
    CONCAT_WS('-',s.CatKey,d.CatKey) AS CatNum
 
−
  FROM depts AS d LEFT JOIN suppliers AS s ON d.ID_Supplier=s.ID;</mysql>
 
−
===Titles===
 
−
These will later reference cat_titles instead of titles, when that table is fully migrated.
 
−
====v_titles====
 
−
<mysql>CREATE OR REPLACE VIEW v_titles AS
 
−
  SELECT
 
−
    t.*,
 
−
    tx.ID_Supp,
 
−
    tx.CatNum,
 
−
    tx.CatWeb,
 
−
    tx.cntForSale,
 
−
    tx.cntInPrint,
 
−
    tx.qtyInStock
 
−
      FROM titles AS t LEFT JOIN _titles AS tx on t.ID=tx.ID;</mysql>
 
−
====qryCat_Titles====
 
−
<mysql>CREATE OR REPLACE VIEW qryCat_Titles AS
 
−
  SELECT
 
−
    t.ID,
 
−
    t.Name,
 
−
    CONCAT_WS('-',d.CatNum,t.CatKey) AS CatNum,
 
−
    d.ID_Supplier,
 
−
    t.ID_Dept,
 
−
    t.DateAdded,
 
−
    t.RstkMin AS QtyMin_Rstk,
 
−
    t.Notes,
 
−
    t.Supplier_CatNum AS Supp_CatNum
 
−
  FROM titles AS t LEFT JOIN qryCat_Depts AS d ON t.ID_Dept=d.ID;</mysql>
 
−
===Items===
 
−
====v_items====
 
−
<mysql>CREATE OR REPLACE VIEW `v_items` AS
 
−
  SELECT
 
−
    i.ID,
 
−
    i.CatNum,
 
−
    i.isForSale,
 
−
    i.isMaster,
 
−
    i.qtyInStock,
 
−
    i.isInPrint,
 
−
    i.isCloseOut,
 
−
    i.isPulled,
 
−
    i.isDumped,
 
−
    i.ID_Title,
 
−
    i.ID_ItTyp,
 
−
    i.ID_ItOpt,
 
−
    i.ItOpt_Descr,
 
−
    i.ID_ShipCost,
 
−
    i.PriceBuy,
 
−
    i.PriceSell,
 
−
    i.PriceList,
 
−
    i.Supp_CatNum,
 
−
    io.Sort AS OptSort
 
−
  FROM (
 
−
    `cat_items` AS `i` LEFT JOIN
 
−
    `cat_ioptns` AS `io` ON
 
−
      ((`i`.`ID_ItOpt` = `io`.`ID`)))</mysql>
 
−
Used by '''clsItemsExt'''
 
−
====qryCat_Items====
 
−
<mysql>CREATE OR REPLACE VIEW qryCat_Items AS SELECT
 
−
  i.ID,
 
−
  i.ID_Title,
 
−
  (t.ID IS NOT NULL) AS TitleExists,
 
−
  t.ID_Supplier,
 
−
  i.PriceSell,
 
−
  i.PriceList,
 
−
  i.PriceBuy,
 
−
  i.CatNum,
 
−
  t.CatNum AS Title_CatNum,
 
−
  t.Name AS Title_Name,
 
−
  i.ID_ItTyp,
 
−
  i.ID_ItOpt,
 
−
  i.ItOpt_Descr,
 
−
  i.ItOpt_Sort,
 
−
  CONCAT(t.Name, IF
 
−
      (i.ItOpt_Descr IS NULL,'',CONCAT(
 
−
        ' (',i.ItOpt_Descr,')'
 
−
        )
 
−
      )
 
−
    ) AS Descr,
 
−
  i.Supp_CatNum,
 
−
  i.isForSale,
 
−
  i.isPulled,
 
−
  i.QtyMin_Stk,
 
−
  t.QtyMin_Rstk AS QtyMin_Rstk_Title
 
−
FROM cat_items AS i LEFT JOIN qryCat_Titles AS t on i.ID_Title=t.ID
 
−
ORDER BY t.ID IS NOT NULL, i.CatNum, i.ItOpt_Sort;</mysql>
 
  
−
* '''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.
+
===Catalog===
−
====qryCbx_Items====
+
* [[/qryCat_Depts]]
−
<mysql>CREATE OR REPLACE VIEW qryCbx_Items AS
+
* '''Title'''-centric:
−
SELECT ID, Descr
+
** [[/qryTitles_Item_info]]
−
FROM qryCbx_Items_data;
+
** [[/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====
 +
* [[/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]]
 +
* <s>[[/qryCtg_build_sub]]</s>
 +
* <s>[[/qryCtg_build]]</s>
 +
* '''[[VbzCart catalog building|building]] process''':
 +
** [[/qryCtg_Items_forUpdJoin]]
 +
** [[/qryCtg_Upd_join]]
 +
** [[/qryCtg_src_dups]]
 +
** [[/qryCtgCk_dup_keys]]
  
−
CREATE OR REPLACE VIEW qryCbx_Items_active AS
+
====Catalog Topics====
−
SELECT ID, Descr
+
* '''titles x topics''':
−
FROM qryCbx_Items_data
+
** [[/qryTitleTopic_Titles]]: more title information
−
WHERE NOT isPulled;
+
** [[/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]]
 +
* [[/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]]
  
−
CREATE OR REPLACE VIEW qryCbx_Items_data AS
+
====Restocks====
−
SELECT
+
* '''all restock requests''':
−
  i.ID,
+
** [[/qryRstks_info]]
−
  CAST(
+
** [[/qryRstkReq_Item_Rcd_status]]
−
    CONCAT(
+
** [[/qryRstkReq_Item_status]]
−
      if(i.isPulled,
+
*** [[/qryRstkReq_Item_status_Req_info]]
−
        '*P* ',
+
*** [[/qryRstkReq_Items_expected]]: show only expected items
−
        ''
+
** [[/qryCbx_RstkReq]]
−
      ),
+
*** [[/qryRstkReq_by_status]]
−
      if(i.CatNum IS NULL,
+
*** [[/qryRstkReq_by_PurchOrd]]
−
        CONCAT('#',i.ID,' (no cat#)')
+
* '''filtered by status''':
−
        ,i.CatNum),
+
** [[/qryRstks_active]]: not terminated = !(closed, orphaned or killed - see {{l/vc/term|restock/status}})
−
      if(i.Supp_CatNum IS NULL,
+
*** [[/qryRstks_unsent]]: created but not ordered yet
−
        '',
+
**** [[/qryRstkItms_unsent]]
−
        CONCAT(' [',i.Supp_CatNum,']')
+
***** [[/qryRstkItms_unsent_for_order]]
−
      ),
+
** [[/qryRstks_inactive]]: all the rest
−
      if(i.isForSale,'',' *n/a*'),
 
−
      ' ',
 
−
      if(i.Descr IS NULL,'no description',i.Descr)
 
−
    ) AS CHAR
 
−
  ) AS Descr,
 
−
  i.isPulled,
 
−
  i.isForSale,
 
−
  i.ID_Title,
 
−
  i.CatNum,
 
−
  i.Supp_CatNum,
 
−
  i.PriceSell,
 
−
  i.QtyMin_Stk
 
−
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
+
====Shipping====
−
* Added more fields for forms which want to display Descr from this query but also need those fields (e.g. Restock Items form):
+
* [[/qryPkgs_status]]
−
** '''ID_Title''', '''CatNum''', '''PriceSell''', '''QtyMin_Stk'''
 
−
* Added '''isPulled''' so qryCbx_Items_active can display only non-pulled items (less bandwidth = faster loading)
 
  
−
An abbreviated version for contexts where the Title is already known
+
===Stock===
−
<mysql>CREATE OR REPLACE VIEW qryCbx_Items_opt 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]]
−
        '*P* ',
 
−
        ''
 
−
      ),
 
−
      if(i.ID_ItOpt IS NULL,
 
−
        CONCAT('#',i.ID,' (no opt)')
 
−
        ,io.CatKey),
 
−
      if(i.isForSale,'',' *n/a*')
 
−
    ) AS CHAR
 
−
  ) AS Descr,
 
−
 
 
−
  i.CatNum
 
−
FROM cat_items AS i LEFT JOIN cat_ioptns AS io ON i.ID_ItOpt=io.ID;</mysql>
 
−
 
 
−
===Orders===
 
−
====qryCbx_Orders====
 
−
<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,
 
−
  ID_Pull,
 
−
  WhenNeeded
 
−
FROM core_orders
 
−
ORDER BY CONCAT(IFNULL(SortPfx,''),Number) DESC;</mysql>
 
−
 
 
−
* '''ID_Pull''' and '''WhenNeeded''' are needed for [[#qryStock_forOpenOrders]]
 
  
−
====qryOrderLines_notPkgd====
+
* [[/qryStk_items_remaining]]
−
Active order lines which have yet to be (completely) put into a package
+
* [[/qryStk_byItem_byBin]]
−
<mysql>CREATE OR REPLACE VIEW qryOrderLines_notPkgd AS
 
−
SELECT
 
−
  oi.ID,
 
−
  oi.ID_Order,
 
−
  oi.ID_Item,
 
−
  oi.CatNum,
 
−
  oi.Descr,
 
−
  oi.QtyOrd,
 
−
  oi.Price,
 
−
  oi.QtyOrd
 
−
    -SUM(
 
−
      IFNULL(pi.QtyShipped,0)
 
−
      +IFNULL(pi.QtyNotAvail,0)
 
−
      +IFNULL(pi.QtyKilled,0)
 
−
    ) AS QtyOpen,
 
−
  SUM(IFNULL(pi.QtyShipped,0)) AS QtyShipped,
 
−
  SUM(IFNULL(pi.QtyShipped,0)
 
−
    +IFNULL(pi.QtyNotAvail,0)
 
−
    +IFNULL(pi.QtyKilled,0)) AS QtyHandled
 
−
FROM
 
−
  ord_lines AS oi
 
−
    LEFT JOIN ord_pkg_lines pi ON pi.ID_OrdLine=oi.ID
 
−
GROUP BY
 
−
  oi.ID,
 
−
  oi.ID_Order,
 
−
  oi.ID_Item,
 
−
  oi.CatNum,
 
−
  oi.Descr,
 
−
  oi.QtyOrd,
 
−
  oi.Price
 
−
HAVING oi.QtyOrd-QtyHandled >0;</mysql>
 
−
====qryStock_forOpenOrders====
 
−
Show all stock available to fill open orders, along with helpful information about the Bin, Order, and Item:
 
−
<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.
+
* [[/qryStk_lines_Title_info]]
  
−
===Restocks===
+
====old queries====
−
====qryCbx_Restocks====
+
This was the first batch of queries I created, before I had decided to go with the qry prefix as in Access.
−
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
+
* [[/v_stk_titles_remaining]]
−
<mysql>CREATE OR REPLACE VIEW qryCbx_Restocks_byStatus AS
+
* [[/v_stk_byItemAndBin_wItemInfo]]
−
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) DESC;
 
−
 
 
−
CREATE OR REPLACE VIEW qryCbx_Restocks_byPurchOrd AS
 
−
SELECT
 
−
  r.ID,
 
−
  CAST(r.PurchOrdNum AS CHAR) AS Descr,
 
−
  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 COALESCE(r.WhenKilled,r.WhenReceived,r.WhenOrdered,r.WhenDebited,r.WhenCreated) DESC;
 
−
 
 
−
CREATE OR REPLACE VIEW qryCbx_Restocks AS
 
−
  SELECT * FROM qryCbx_Restocks_byStatus
 
−
UNION
 
−
  SELECT * FROM qryCbx_Restocks_byPurchOrd;</mysql>
 
−
 
 
−
====qryFrm_RestockLines====
 
−
<mysql>CREATE OR REPLACE VIEW qryFrm_RestockLines AS
 
−
SELECT
 
−
  ri.ID,
 
−
  ri.ID_Restock,
 
−
  ri.ID_Item,
 
−
  ci.CatNum,
 
−
  ci.Supp_CatNum,
 
−
  ci.PriceSell,
 
−
  ci.QtyMin_Stk,
 
−
  o.Number AS OrderName,
 
−
  ci.Descr AS ItemDescr,
 
−
  o.WhenNeeded,
 
−
  If(t.RstkMin = 1, '', t.RstkMin) AS RstkMinStr,
 
−
  ri.QtyOrd,
 
−
  ri.QtyNeed,
 
−
  ri.QtyExp,
 
−
  ri.InvcLineNo,
 
−
  ri.InvcQtyOrd,
 
−
  ri.InvcQtySent,
 
−
  ri.isGone,
 
−
  ri.QtyRecd,
 
−
  ri.QtyFiled,
 
−
  ri.CostExpPer,
 
−
  ri.CostInvPer,
 
−
  ri.CostInvTot,
 
−
  ri.CostActTot,
 
−
  ri.CostActBal,
 
−
  ri.Notes
 
−
FROM
 
−
(
 
−
  (rstk_lines AS ri
 
−
    LEFT JOIN core_orders AS o ON ri.ID_Order=o.ID)
 
−
  LEFT JOIN qryCbx_Items_data AS ci ON ri.ID_Item=ci.ID)
 
−
LEFT JOIN cat_titles AS t ON ci.ID_Title=t.ID;</mysql>
 
−
 
 
−
* A lot of these fields were added back when this query was still the data source for qryFrm_RestockLines_byItem; this is no longer the case. Probably the only customer for this query is the Restock Items entry form, so any fields not needed there can probably be done away with.
 
−
 
 
−
====qryFrm_RestockLines_byItem====
 
−
Basically the same as qryFrm_RestockLines, but grouped by ID_Item and with more Item-related fields
 
−
<mysql>CREATE OR REPLACE VIEW qryFrm_RestockLines_byItem AS
 
−
SELECT
 
−
  CONCAT(ri.ID_Item,'-',ri.ID_Restock) AS Key_RstkItem,
 
−
  ri.ID_Item,
 
−
  ri.ID_Restock,
 
−
  i.Descr AS ItemDescr,
 
−
  i.CatNum,
 
−
  i.Supp_CatNum,
 
−
  SUM(IFNULL(ri.QtyOrd,0)) AS QtyOrd,
 
−
  SUM(IFNULL(ri.QtyExp,0)) AS QtyExp,
 
−
  ri.CostExpPer,
 
−
  i.PriceSell,
 
−
  i.QtyMin_Stk
 
−
FROM rstk_lines AS ri LEFT JOIN qryCbx_Items_data AS i ON ri.ID_Item=i.ID
 
−
GROUP BY ri.ID_Restock, ri.ID_Item, i.CatNum, i.Supp_CatNum, ri.CostExpPer, i.PriceSell, i.QtyMin_Stk
 
−
ORDER BY i.CatNum;</mysql>
 
−
====v_rstk_lines_wItemInfo====
 
−
<mysql>CREATE OR REPLACE VIEW v_rstk_lines_wItemInfo AS
 
−
SELECT
 
−
  ri.*,
 
−
  i.ID_Title,
 
−
  IFNULL(r.WhenOrdered,r.WhenCreated) AS WhenDone
 
−
FROM
 
−
  (rstk_lines AS ri
 
−
  LEFT JOIN cat_items AS i ON ri.ID_Item=i.ID)
 
−
  LEFT JOIN core_restocks AS r ON ri.ID_Restock=r.ID;</mysql>
 
−
 
 
−
===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_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>
 
 
===Caching===
 
===Caching===
 
Caching should only be used for catalog display.
 
Caching should only be used for catalog display.
−
====v_data_flow====
+
* [[/qryCache_Flow_Procs]]
−
<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>
 

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"