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
(→‎qryItms_to_restock: QtyOnOrder should never be negative)
(→‎new queries: many more queries no longer in db)
 
(65 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_'''.)
 +
==by category==
 +
===Inactive===
 +
* [[/deprecated]]: queries we're trying to [[/discarded|get rid of]], once we're sure nothing uses them
 +
* [[/discarded]]: queries apparently no longer in use
 +
 
===Catalog===
 
===Catalog===
−
====qryCat_Depts====
+
* [[/qryCat_Depts]]
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCat_Depts AS
+
* '''Title'''-centric:
−
  SELECT
+
** [[/qryTitles_Item_info]]
−
    d.ID,
+
** [[/qryTitles_ItTyps_grpItems]]
−
    d.Name,
+
** [[/qryTitles_ItTyps_ItTyps]]
−
    d.Sort,
+
** [[/qryTitles_ItTyps_Titles]]
−
    d.CatKey,
+
** [[/qryTitles_Imageless]] - titles with no active images
−
    UPPER(IFNULL(d.PageKey,d.CatKey)) AS CatKey_def,
+
** [[/qryCat_Titles]]
−
    d.ID_Supplier,
+
** [[/qryCat_Titles_Item_stats]] -- item/stock statistics
−
    UPPER(CONCAT_WS('-',s.CatKey,d.CatKey)) AS CatNum,
+
** [[/qryCat_Titles_Item_count]] -- simpler query for maintenance
−
    LOWER(CONCAT_WS("/",s.CatKey,IFNULL(d.PageKey,d.CatKey))) AS CatWeb_Dept,
+
** [[/qryCat_Titles_web]]
−
    LOWER(CONCAT_WS("/",s.CatKey,d.CatKey)) AS CatWeb_Title
+
** [[/qryCbx_Titles]]
−
  FROM cat_depts AS d LEFT JOIN cat_supp AS s ON d.ID_Supplier=s.ID;</mysql>
+
* '''Item'''-centric:
−
<section end=sql />
+
** [[/qryCat_Items_Stock]]: cat_items with stock info
−
 
+
* '''ItTyp'''-centric:
−
====v_titles====
+
** [[/qryItTypsDepts_grpItems]]
−
'''Deprecated''' -- use [[#qryCat_Titles_Item_stats]] instead -- this query still depends on cached _depts table. To be eliminated later.
+
** [[/qryItTypsDepts_ItTyps]]
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW v_titles AS
+
* '''Image'''-centric:
−
SELECT
+
** [[/qryImgs_byTitle]]: Image info by Title
−
  ID_Title,
+
* [[/qryCat_pages]]: maps http path info to catalog entities
−
  ID_Dept,
+
====Catalog Items====
−
  SUM(IF(i.isForSale,1,0)) AS cntForSale,
+
* [[/qryCbx_Items_data]]
−
  SUM(IF(i.isInPrint,1,0)) AS cntInPrint,
+
** [[/qryCbx_Items]]
−
  SUM(i.qtyInStock) AS qtyInStock,
+
** [[/qryCbx_Items_active]]
−
  MIN(i.PriceSell) AS currMinSell,
+
** [[/qryCbx_Items_for_sale]]
−
  MAX(i.PriceSell) AS currMaxSell,
+
** [[/qryCbx_Items_opt]]: abbreviated version for contexts where Title is already known
−
  d.ID_Supp,
+
* [[/qryItems_prices]]: what uses this?
−
  UPPER(CONCAT_WS("-",d.CatNum,t.CatKey)) AS CatNum,
+
====Catalog Sources====
−
  LOWER(CONCAT_WS("/",d.CatWeb_Title,t.CatKey)) AS CatWeb,
+
* [[/qryCtg_Sources_active]]
−
  t.Name
+
* [[/qryCtg_Items_updates]]
−
FROM (cat_items AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID) LEFT JOIN _depts AS d ON t.ID_dept=d.ID
+
* [[/qryCtg_Items_updates_joinable]]
−
GROUP BY i.ID_Title;</mysql>
+
* [[/qryCtg_Items_active]]
−
<section end=sql />
+
* <s>[[/qryCtg_build_sub]]</s>
−
 
+
* <s>[[/qryCtg_build]]</s>
−
Old version which depends on 2 cached tables (trying to minimize usage of these):
+
* '''[[VbzCart catalog building|building]] process''':
−
<mysql>CREATE OR REPLACE VIEW v_titles AS
+
** [[/qryCtg_Items_forUpdJoin]]
−
  SELECT
+
** [[/qryCtg_Upd_join]]
−
    t.*,
+
** [[/qryCtg_src_dups]]
−
    tx.ID_Supp,
+
** [[/qryCtgCk_dup_keys]]
−
    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>
 
  
−
====qryTitles_ItTyps_grpItems====
+
====Catalog Topics====
−
This is a subquery for qryTitles_ItTyps; all the summing across items within a Title-ItTyp pair is done here, then the results are joined back with Titles so we have all the information to display.
+
* '''titles x topics''':
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryTitles_ItTyps_grpItems AS
+
** [[/qryTitleTopic_Titles]]: more title information
−
SELECT
+
** [[/qryTitleTopic_Topics]]: more topic information
−
  ID_Title, ID_ItTyp,
+
** [[/qryTitleTopic_Title_avail]]: title availability information
−
  SUM(IF(isForSale,1,0)) AS cntForSale,
+
===Ordering===
−
  SUM(IF(isInPrint,1,0)) AS cntInPrint,
+
====Carts====
−
  SUM(IF(qtyInStock>0,1,0)) AS cntInStock,
+
* [[/qryCarts_info]]
−
  SUM(qtyInStock) AS qtyInStock,
+
** [[/qrySub_Carts_info_data]]
−
  MIN(PriceSell) AS currMinPrice,
+
** [[/qrySub_Carts_info_items]]
−
  MAX(PriceSell) AS currMaxPrice
+
====Customers====
−
FROM cat_items AS i
+
* [[/qryCustAddrs]]
−
WHERE isForSale
+
* [[/qryCbx_CustNames]]
−
GROUP BY ID_Title, ID_ItTyp;</mysql>
+
====Orders====
−
<section end=sql />
+
* [[/qryCbx_Orders]]
−
* I originally had a '''HAVING cntInStock''' at the end of this, but that results in only stock items showing as available ''at all''. If this filtering is needed for something, it will have to be expressed in some other way.
+
* [[/qryOrderLines_notPkgd]]
−
* '''2008-04-04''' Grouping by GrpCode (and GrpDescr and GrpSort) is '''not''' necessary, and creates clutter in the title display if used. Certain items which have subtypes only distinguished by the group code (e.g. SD-NF-320 comes in both black and tie-dye) use these fields, but the page-generation code looks for different subtypes in the cat_items data and displays a new subtype header whenever GrpDescr changes. Maybe later on we'll include ID_CtgGroup in cat_items and pull GrpCode, GrpDescr, and GrpSort from the appropriate table(s?), but for now we store the values explicitly in cat_items and rely on proper sorting.
+
* [[/qryOrders_Active]]
−
** If subtypes are not being properly disambiguated within the title display, make sure the GrpDescr fields are being given a value (description). This is only necessary when the item type (ID_ItTyp) is the same.
+
* [[/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]]
  
−
====qryTitles_ItTyps_ItTyps====
+
====Restocks====
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryTitles_ItTyps_ItTyps AS
+
* '''all restock requests''':
−
SELECT
+
** [[/qryRstks_info]]
−
  i.*,
+
** [[/qryRstkReq_Item_Rcd_status]]
−
  it.Sort                      AS ItTyp_Sort,
+
** [[/qryRstkReq_Item_status]]
−
  it.NameSng                    AS ItTyp_Sng,
+
*** [[/qryRstkReq_Item_status_Req_info]]
−
  IFNULL(it.NamePlr,it.NameSng) AS ItTyp_Plr
+
*** [[/qryRstkReq_Items_expected]]: show only expected items
−
FROM
+
** [[/qryCbx_RstkReq]]
−
qryTitles_ItTyps_grpItems AS i LEFT JOIN cat_ittyps AS it ON i.ID_ItTyp=it.ID;</mysql>
+
*** [[/qryRstkReq_by_status]]
−
<section end=sql />
+
*** [[/qryRstkReq_by_PurchOrd]]
−
====qryTitles_ItTyps_Titles====
+
* '''filtered by status''':
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryTitles_ItTyps_Titles AS
+
** [[/qryRstks_active]]: not terminated = !(closed, orphaned or killed - see {{l/vc/term|restock/status}})
−
SELECT
+
*** [[/qryRstks_unsent]]: created but not ordered yet
−
  ti.*,
+
**** [[/qryRstkItms_unsent]]
−
  t.Name,
+
***** [[/qryRstkItms_unsent_for_order]]
−
  t.ID_Dept,
+
** [[/qryRstks_inactive]]: all the rest
−
  t.CatNum,
 
−
  t.CatWeb
 
−
FROM qryTitles_ItTyps_grpItems AS ti LEFT JOIN v_titles AS t ON ti.ID_Title=t.ID_Title;</mysql>
 
−
<section end=sql />
 
−
====qryItTypsDepts_grpItems====
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS
 
−
SELECT
 
−
  ID_ItTyp, ID_Dept,
 
  
−
  SUM(IF(isForSale,1,0)) AS cntForSale,
+
====Shipping====
−
  SUM(IF(isInPrint,1,0)) AS cntInPrint,
+
* [[/qryPkgs_status]]
−
  SUM(IF(qtyInStock>0,1,0)) AS cntInStock,
 
−
  SUM(qtyInStock) AS qtyInStock
 
−
FROM cat_items AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID
 
−
GROUP BY ID_ItTyp, ID_Dept
 
−
HAVING cntForSale;</mysql>
 
−
<section end=sql />
 
−
====qryItTypsDepts_ItTyps====
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItTypsDepts_ItTyps AS
 
−
SELECT
 
−
  m.*,
 
−
  it.NameSng AS ItTyp_Sng,
 
−
  it.NamePlr AS ItTyp_Plr
 
−
FROM qryItTypsDepts_grpItems AS m LEFT JOIN cat_ittyps AS it ON m.ID_ItTyp=it.ID;</mysql>
 
−
<section end=sql />
 
−
 
 
−
====qryCat_Titles====
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCat_Titles AS
 
−
  SELECT
 
−
    t.ID,
 
−
    t.Name,
 
−
    UPPER(CONCAT_WS("-",d.CatNum,t.CatKey)) AS CatNum,
 
−
    LOWER(CONCAT_WS("/",d.CatWeb_Title,t.CatKey)) AS CatWeb,
 
−
    t.CatKey AS CatKey_Title,
 
−
    d.ID_Supplier,
 
−
    t.ID_Dept,
 
−
    t.DateAdded,
 
−
    t.RstkMin AS QtyMin_Rstk,
 
−
    t.Notes,
 
−
    t.Supplier_CatNum AS Supp_CatNum
 
−
  FROM cat_titles AS t LEFT JOIN qryCat_Depts AS d ON t.ID_Dept=d.ID;</mysql>
 
−
<section end=sql />
 
−
* '''CatKey_Title''' is used in the catalog-building process (Enter Title Groups) when searching within a supplier for a particular title
 
−
====qryCat_Titles_Item_stats====
 
−
This query replaces v_titles and is used by qryCat_Titles_web.
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCat_Titles_Item_stats AS
 
−
SELECT
 
−
  ID_Title,
 
−
  ID_Dept,
 
−
  DateAdded AS WhenAdded,
 
−
  SUM(IF(i.isForSale,1,0)) AS cntForSale,
 
−
  SUM(IF(i.isInPrint,1,0)) AS cntInPrint,
 
−
  SUM(i.qtyInStock) AS qtyInStock,
 
−
  MIN(i.PriceSell) AS currMinSell,
 
−
  MAX(i.PriceSell) AS currMaxSell,
 
−
  d.ID_Supplier,
 
−
  UPPER(CONCAT_WS("-",d.CatNum,t.CatKey)) AS CatNum,
 
−
  LOWER(CONCAT_WS("/",d.CatWeb_Title,t.CatKey)) AS CatWeb,
 
−
  t.Name
 
−
FROM (cat_items AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID) LEFT JOIN qryCat_Depts AS d ON t.ID_dept=d.ID
 
−
GROUP BY i.ID_Title;</mysql>
 
−
<section end=sql />
 
−
 
 
−
====qryCat_Titles_web====
 
−
As of 2008-05-23, used only for generating old-style titles.lst file, but it may eventually be used by active code in the rewrite.
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCat_Titles_web AS
 
−
SELECT
 
−
  *,
 
−
  CAST(CONCAT("&ldquo;",Name,"&ldquo;: ",currMinSell,IF(currMinSell=currMaxSell,"",CONCAT(" &ndash; ",currMaxSell))) AS CHAR) AS ImgText
 
−
FROM qryCat_Titles_Item_stats;</mysql>
 
−
<section end=sql />
 
−
 
 
−
====qryCbx_Titles====
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCbx_Titles AS
 
−
SELECT
 
−
  ID,
 
−
  CONCAT_WS(' ',CatNum,Name) AS Descr
 
−
FROM qryCat_Titles
 
−
ORDER BY CatNum;</mysql>
 
−
<section end=sql />
 
−
 
 
−
===Catalog Items===
 
−
====v_items====
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
Used by '''clsItemsExt'''
 
−
 
 
−
====qryCat_Items====
 
−
* '''Requires''': cat_items, [[#qryCat_Titles]]
 
−
* '''Used by''': [[#qryItms_to_restock_w_info]] and others
 
−
* '''History''':
 
−
** '''2008-11-22''' Added isInPrint so we could use this in [[#qryItms_to_restock_w_info]]
 
−
* '''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.
 
−
<section begin=sql /><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.isInPrint,
 
−
  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>
 
−
<section end=sql />
 
−
 
 
−
====qryCbx_Items====
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCbx_Items_data AS
 
−
SELECT
 
−
  i.ID,
 
−
  CAST(
 
−
    CONCAT(
 
−
      if(i.isPulled,
 
−
        '*P* ',
 
−
        ''
 
−
      ),
 
−
      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.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;
 
−
 
 
−
CREATE OR REPLACE VIEW qryCbx_Items AS
 
−
SELECT ID, Descr
 
−
FROM qryCbx_Items_data;
 
−
 
 
−
CREATE OR REPLACE VIEW qryCbx_Items_active AS
 
−
SELECT ID, Descr
 
−
FROM qryCbx_Items_data
 
−
WHERE (NOT isPulled) AND (CatNum IS NOT NULL)
 
−
ORDER BY CatNum;
 
−
 
 
−
CREATE OR REPLACE VIEW qryCbx_Items_for_sale AS
 
−
SELECT ID, Descr
 
−
FROM qryCbx_Items_data
 
−
WHERE isForSale
 
−
ORDER BY CatNum;</mysql>
 
−
<section end=sql />
 
−
 
 
−
* Future refinement: no need to display CatNum, Supp_CatNum, i.Descr if i.isPulled
 
−
* Added more fields for forms which want to display Descr from this query but also need those fields (e.g. Restock Items form):
 
−
** '''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
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCbx_Items_opt AS
 
−
SELECT
 
−
  i.ID,
 
−
  CAST(
 
−
    CONCAT(
 
−
      if(i.isPulled,
 
−
        '*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>
 
−
<section end=sql />
 
−
 
 
−
====qryItems_prices====
 
−
This will eventually contain shipping prices as well, but the shipping costs table hasn't been migrated yet. It also needs to build a description from it.NameSng and i.ItOpt_Descr -- or perhaps there is a better way to do it (haven't we solved this problem elsewhere in a view?)
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItems_prices AS
 
−
SELECT
 
−
  i.ID,
 
−
  i.CatNum,
 
−
  i.ID_Title,
 
−
  i.ID_ItTyp,
 
−
  i.ID_ShipCost,
 
−
  i.PriceSell,
 
−
  i.PriceList,
 
−
  i.ItOpt_Descr,
 
−
  i.isInPrint,
 
−
  it.NameSng,
 
−
  it.NamePlr,
 
−
  it.Descr as ItTyp_Descr
 
−
FROM (cat_items AS i LEFT JOIN cat_ittyps AS it ON i.ID_ItTyp=it.ID)
 
−
WHERE i.isForSale AND (NULLIF(i.isPulled,0) IS NULL) AND (i.ID_ShipCost IS NOT NULL) AND (i.PriceSell IS NOT NULL)
 
−
ORDER BY i.CatNum;</mysql>
 
−
<section end=sql />
 
−
===Catalog Sources===
 
−
====qryCtg_Sources_active====
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_Sources_active AS
 
−
  SELECT *
 
−
  FROM ctg_sources AS sc
 
−
  WHERE (sc.DateAvail <= NOW()) AND (sc.ID_Supercede IS NULL);</mysql>
 
−
<section end=sql />
 
−
====qryCtg_Items_updates====
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_Items_updates AS
 
−
  SELECT
 
−
    NULLIF(CONCAT_WS('/',g.Code,gt.GroupCode,o.CatKey),'') AS CatSfx,
 
−
    gt.ID_Title,
 
−
    g.ID AS ID_TGroup,
 
−
    gt.ID AS ID_TGTitle,
 
−
    gi.ID AS ID_TGItem,
 
−
    gt.ID_Source AS ID_TGSource,
 
−
    gi.ID_ItTyp, gi.ID_ItOpt
 
−
  FROM
 
−
  (
 
−
    (
 
−
      (
 
−
        ctg_titles AS gt LEFT JOIN ctg_groups AS g ON g.ID=gt.ID_Group
 
−
      ) LEFT JOIN qryCtg_Sources_active AS c ON gt.ID_Source=c.ID
 
−
    ) LEFT JOIN ctg_items AS gi ON gi.ID_Group=g.ID
 
−
  ) LEFT JOIN cat_ioptns AS o ON gi.ID_ItOpt=o.ID
 
−
  WHERE
 
−
    (c.ID IS NOT NULL)
 
−
      AND g.isActive
 
−
      AND gt.isActive
 
−
      AND ((gt.WhenDiscont IS NULL) OR (gt.WhenDiscont>NOW()))
 
−
      AND gi.isActive;</mysql>
 
−
<section end=sql />
 
−
 
 
−
====qryCtg_Items_updates_joinable====
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_Items_updates_joinable AS
 
−
  SELECT
 
−
    CONCAT_WS('-',u.ID_Title,u.CatSfx) AS IDS_Item,
 
−
    CONCAT_WS('-',t.CatNum,u.CatSfx) AS CatNum,
 
−
    u.*
 
−
  FROM qryCtg_Items_updates AS u LEFT JOIN qryCat_Titles AS t ON u.ID_Title=t.ID;</mysql>
 
−
<section end=sql />
 
−
====qryCtg_Items_active====
 
−
Brings up ''only'' ctg_items records which are active, simplifying the final massive JOIN query a bit and hopefully avoiding any errors similar to the one mentioned above.
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_Items_active AS
 
−
  SELECT
 
−
    *
 
−
  FROM ctg_items AS tgi
 
−
  WHERE tgi.isActive AND (tgi.PriceSell IS NOT NULL) AND (tgi.ID_ShipCost IS NOT NULL);</mysql>
 
−
<section end=sql />
 
−
====qryCtg_Titles_active====
 
−
Brings up ''only'' ctg_titles records which are active, making it impossible for an inactive catalog to make an active title look inactive.
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_Titles_active AS
 
−
  SELECT
 
−
    *
 
−
  FROM ctg_titles AS tgt
 
−
  WHERE
 
−
    tgt.isActive
 
−
    AND
 
−
    ( ( tgt.WhenDiscont Is Null
 
−
      ) Or (
 
−
        tgt.WhenDiscont>Now()
 
−
      )
 
−
    );</mysql>
 
−
<section end=sql />
 
−
 
 
−
====qryCtg_build_sub====
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_build_sub AS
 
−
  SELECT
 
−
    s.ID_Item,
 
−
    s.CatNum,
 
−
    (
 
−
      s.isActive
 
−
      AND tg.isActive
 
−
      AND tgi.isActive
 
−
      AND (c.ID_Supercede Is Null)
 
−
      ) AS isForSale,
 
−
    c.isCloseOut,
 
−
    s.isActive,
 
−
    s.ID_Title,
 
−
    tgi.ID_ItTyp,
 
−
    tgi.ID_ItOpt,
 
−
    tgi.ID_ShipCost,
 
−
    tgi.PriceBuy,
 
−
    tgi.PriceSell,
 
−
    tgi.PriceList,
 
−
    IFNULL(tgi.Descr,CONCAT_WS(' / ',tgt.GroupDescr,o.Descr)) AS ItOpt_Descr_part,
 
−
    it.NameSng,
 
−
    s.ID_CTG_Title,
 
−
    s.ID_CTG_Item,
 
−
    tgi.Descr AS GrpItmDescr,
 
−
    tgt.GroupDescr AS TitleGroupDescr,
 
−
    o.Descr AS OptionDescr,
 
−
    s.CatSfx,
 
−
    CONCAT(tg.Sort,it.Sort,o.Sort) AS ItOpt_Sort,
 
−
    tgt.GroupCode AS GrpCode,
 
−
    tgt.GroupDescr AS GrpDescr,
 
−
    tgt.GroupSort AS GrpSort
 
−
  FROM
 
−
  ( ( ( ( (ctg_updates AS s LEFT JOIN ctg_groups AS tg ON s.ID_CTG_Group = tg.ID
 
−
          ) LEFT JOIN qry_ctg_titles_active AS tgt ON s.ID_CTG_Title = tgt.ID
 
−
        ) LEFT JOIN ctg_items AS tgi ON s.ID_CTG_Item = tgi.ID
 
−
      ) LEFT JOIN ctg_sources AS c ON tgt.ID_Source = c.ID
 
−
    ) LEFT JOIN cat_ioptns AS o ON tgi.ID_ItOpt = o.ID
 
−
  ) LEFT JOIN cat_ittyps AS it ON tgi.ID_ItTyp = it.ID;</mysql>
 
−
<section end=sql />
 
−
 
 
−
====qryCtg_build====
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCtg_build AS
 
−
  SELECT
 
−
    *,
 
−
    isForSale And Not IFNULL(isCloseOut,FALSE) AS isInPrint,
 
−
    IFNULL(ItOpt_Descr_part,NameSng) AS ItOpt_Descr
 
−
  FROM qryCtg_build_sub</mysql>
 
−
<section end=sql />
 
−
 
 
−
===Orders===
 
−
====qryCbx_Orders====
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
 
 
−
* '''ID_Pull''' and '''WhenNeeded''' are needed for [[#qryStock_forOpenOrders]]
 
−
 
 
−
====qryOrderLines_notPkgd====
 
−
Active order lines which have yet to be (completely) put into a package
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
====qryOrders_Active====
 
−
* '''Action''': Return all orders which are currently active. For now, this just shows orders which haven't been pulled , but later it will look at the order state. As simple as it is, it's still useful to have this as a separate query because:
 
−
** Removing unneeded records before sending data helps to reduce bandwidth a little bit when called from a remote process
 
−
** Serves as a reference which can be used unaltered even after the method of determining whether an order is "active" changes
 
−
* '''Requires''': core_orders
 
−
* '''History''':
 
−
** '''2008-11-20''' Added requirement that WhenClosed (new field) must also be NULL
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryOrders_Active AS
 
−
SELECT * FROM core_orders
 
−
WHERE
 
−
  (ID_Pull IS NULL) AND
 
−
  (WhenClosed IS NULL);</mysql>
 
−
<section end=sql />
 
−
=====alternate version=====
 
−
* '''Purpose''': This version ignores the WhenClosed field, so we can check that no orders have been marked closed when they still have open items.
 
−
* '''History''':
 
−
** '''2008-11-21''' Created because it looks like the Massive Order Closing closed ''all'' orders that were open.
 
−
<mysql>CREATE OR REPLACE VIEW qryOrders_Active AS
 
−
SELECT * FROM core_orders
 
−
WHERE
 
−
  (ID_Pull IS NULL);</mysql>
 
−
 
 
−
====qryOrderLines_Active====
 
−
* '''Action''': Return all order lines for orders which are currently active as determined by [[#qryOrders_Active]].
 
−
* '''Requires''': ord_lines
 
−
* '''Used by''': [[#qryOrdItms_Pkg_qtys]]
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryOrderLines_Active AS
 
−
SELECT ol.* FROM ord_lines AS ol LEFT JOIN qryOrders_Active AS o ON ol.ID_Order=o.ID
 
−
WHERE o.ID IS NOT NULL;</mysql>
 
−
<section end=sql />
 
−
* '''History''':
 
−
** '''2008-11-16''' created for new restocking process
 
−
 
 
−
====qtyOrderItems_Active====
 
−
* '''Action''': Same as [[#qryOrderLines_Active]], but by Item instead of OrdLine (and leaving out extra info about items which might return multiple records due to slight differences)
 
−
* '''Requires''': [[#qryOrderLines_Active]]
 
−
* In theory, the same item should never appear on more than one line in an order -- but current data has two older records in which this actually did take place. This query provides order-line data consolidated by item, in case it happens again.
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryOrderItems_Active AS
 
−
SELECT
 
−
  ID_Order,
 
−
  ID_Item,
 
−
  SUM(QtyOrd) AS QtyOrd,
 
−
  COUNT(ID) AS CountLines
 
−
FROM qryOrderLines_Active GROUP BY ID_Order, ID_Item;
 
−
</mysql>
 
−
<section end=sql />
 
−
* '''History''':
 
−
** '''2008-11-16''' created for new restocking process
 
−
 
 
−
====qry_PkgItem_qtys_byOrder====
 
−
* '''Action''': Returns data for items packaged for each order -- totals actually put into non-voided packages ("QtySent") and totals "handled" ("QtyDone" - sent ''or'' marked as n/a or cancelled)
 
−
* '''Requires''': ord_pkg_lines, ord_pkgs
 
−
* '''Used by''':
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qry_PkgItem_qtys_byOrder AS
 
−
SELECT
 
−
  COUNT(opl.ID) AS PkgCount,
 
−
  opl.ID_Item,
 
−
  opl.ID_OrdLine,
 
−
  op.ID_Order,
 
−
  SUM(IFNULL(QtyShipped,0)+IFNULL(QtyExtra,0)) AS QtySent,
 
−
  SUM(IFNULL(QtyShipped,0)+IFNULL(QtyKilled,0)+IFNULL(QtyNotAvail,0)) AS QtyDone
 
−
FROM ord_pkg_lines AS opl LEFT JOIN ord_pkgs AS op ON opl.ID_Pkg=op.ID
 
−
WHERE (op.ID IS NOT NULL) AND (op.WhenVoided IS NULL)
 
−
GROUP BY ID_Item, opl.ID_OrdLine, op.ID_Order
 
−
ORDER BY ID_Order, ID_Item;</mysql>
 
−
<section end=sql />
 
−
* '''History''':
 
−
** '''2008-11-16''' created for new restocking process
 
−
* '''QtyExtra''' can't be included in QtyDone or else freebies will cause problems (open order lines dues to "overfulfillment", plus they shouldn't count against ordered items
 
−
 
 
−
====qryOrdItms_Pkg_qtys====
 
−
* '''Requires''': [[#qryOrderLines_Active]], [[#qry_PkgItem_qtys_byOrder]]
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryOrdItms_Pkg_qtys AS
 
−
SELECT
 
−
  oi.ID_Item,
 
−
  oi.ID_Order,
 
−
  oi.QtyOrd,
 
−
  piq.QtySent,
 
−
  piq.QtyDone,
 
−
  oi.QtyOrd-IFNULL(piq.QtyDone,0) AS QtyOpen
 
−
FROM qryOrderItems_Active AS oi
 
−
  LEFT JOIN qry_PkgItem_qtys_byOrder AS piq
 
−
    ON (piq.ID_Item=oi.ID_Item) AND (piq.ID_Order=oi.ID_Order)
 
−
ORDER BY oi.ID_Item, oi.ID_Order;</mysql>
 
−
<section end=sql />
 
−
* '''History''':
 
−
** '''2008-11-16''' created for new restocking process
 
−
 
 
−
====qryOrdItms_open====
 
−
* '''Requires''': [[#qryOrdItms_Pkg_qtys]]
 
−
* '''History''':
 
−
** '''2008-11-16''' created for new restocking process
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryOrdItms_open AS
 
−
SELECT * FROM qryOrdItms_Pkg_qtys WHERE QtyOpen;</mysql>
 
−
<section end=sql />
 
−
====qryItms_open====
 
−
* '''Requires''': [[#qryOrdItms_open]]
 
−
* '''Returns''': Same rows as qryOrdItms_open but GROUPed by ID_Item
 
−
* '''History''':
 
−
** '''2008-11-18''' created for new restocking process
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItms_open AS
 
−
SELECT
 
−
  ID_Item,
 
−
  SUM(oio.QtyOrd) AS QtyOrd,
 
−
  SUM(oio.QtySent) AS QtySent,
 
−
  SUM(oio.QtyDone) AS QtyDone,
 
−
  SUM(oio.QtyOpen) AS QtyOpen
 
−
FROM qryOrdItms_open AS oio
 
−
GROUP BY ID_Item;</mysql>
 
−
<section end=sql />
 
−
====qryItms_to_restock_union====
 
−
* '''Returns''': List of all items relevant to calculating items-to-restock, along with the relevant numbers (quantity on hand, quantity needed for orders...) but not combined yet (that happens in [[#qryItms_to_restock]])
 
−
* '''Requires''': [[#qryItms_open]], [[#qryStkItms_for_sale]]
 
−
* '''Used by''': [[#qryItms_to_restock]]
 
−
* '''History''':
 
−
** '''2008-11-18''' created for new restocking process (under construction; need to add items-in-transit for restocks)
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItms_to_restock_union AS
 
−
SELECT
 
−
  ID_Item,
 
−
  QtyOpen,
 
−
  NULL AS QtyForSale,
 
−
  NULL AS QtyMin_Stk,
 
−
  NULL AS QtyOnOrder
 
−
FROM qryItms_open
 
−
UNION
 
−
SELECT
 
−
  ID_Item,
 
−
  NULL AS QtyOpen,
 
−
  QtyForSale,
 
−
  NULL AS QtyMin_Stk,
 
−
  NULL AS QtyOnOrder
 
−
FROM qryStkItms_for_sale
 
−
UNION
 
−
SELECT
 
−
  ID AS ID_Item,
 
−
  NULL AS QtyOpen,
 
−
  NULL AS QtyForSale,
 
−
  QtyMin_Stk,
 
−
  NULL AS QtyOnOrder
 
−
FROM cat_items WHERE QtyMin_Stk>0
 
−
UNION
 
−
SELECT
 
−
  ID_Item,
 
−
  NULL AS QtyOpen,
 
−
  NULL AS QtyForSale,
 
−
  NULL AS QtyMin_Stk,
 
−
  IFNULL(QtyOrd,0)-IFNULL(QtyRecd,0) AS QtyOnOrder
 
−
FROM qryRstkItms_en_route;</mysql>
 
−
<section end=sql />
 
−
 
 
−
====qryItms_to_restock====
 
−
* '''Returns''': List of items where we need more of them, either to fill orders or to meet minimum stock levels. Accounts for restock items already requested and items currently in stock.
 
−
* '''Requires''': [[#qryItms_to_restock_union]]
 
−
* '''History''':
 
−
** '''2008-11-18''' created for new restocking process
 
−
** '''2008-11-19''' added additional fields from revised union query
 
−
** '''2008-11-22''' QtyOnOrder should never be negative for any particular requested item; if we receive something we didn't order, it goes into stock
 
−
* '''Notes''': It might be a good diagnostic to run this query without the final "HAVING" clause and look for anything that doesn't make sense. Hopefully most of the negative numbers are overstocked items and unclosed restock requests (they should be closed).
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItms_to_restock AS
 
−
SELECT
 
−
  ID_Item,
 
−
  COUNT(ID_Item) AS DupCount,
 
−
  SUM(iru.QtyOpen) AS QtyOpen,
 
−
  SUM(iru.QtyForSale) AS QtyForSale,
 
−
  SUM(iru.QtyMin_Stk) AS QtyMin_Stk,
 
−
  SUM(IF(iru.QtyOnOrder>0,iru.QtyOnOrder,0)) AS QtyOnOrder,
 
−
  IFNULL(QtyOpen,0)
 
−
    -IFNULL(QtyForSale,0)
 
−
    +IFNULL(QtyMin_Stk,0)
 
−
    -IFNULL(QtyOnOrder,0)
 
−
    AS QtyToGet
 
−
FROM qryItms_to_restock_union AS iru
 
−
GROUP BY ID_Item
 
−
HAVING QtyToGet>0;</mysql>
 
−
<section end=sql />
 
−
 
 
−
====qryItms_to_restock_w_info====
 
−
* '''Returns''': Same data as [[#qryItms_to_restock]] but with additional info as needed for creating a restock request
 
−
* '''History''':
 
−
** '''2008-11-20''' created for new restock process
 
−
** '''2008-11-22''' +ID_Supplier; using qryCat_Items instead of cat_items + cat_titles (+cat_depts would also have been needed to get ID_Supplier)
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItms_to_restock_w_info AS
 
−
SELECT
 
−
  qir.*,
 
−
  i.ID_Supplier,
 
−
  i.isInPrint,
 
−
  i.PriceBuy,
 
−
  i.PriceSell,
 
−
  i.QtyMin_Rstk_Title
 
−
FROM
 
−
    qryItms_to_restock AS qir
 
−
  LEFT JOIN qryCat_Items AS i ON qir.ID_Item=i.ID;</mysql>
 
−
<section end=sql />
 
−
===Customers===
 
−
====qryCustAddrs====
 
−
Any basic simple calculations not involving other tables can be added to this query as needed.
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryCustAddrs AS
 
−
SELECT
 
−
  *,
 
−
  NOT ((IFNULL(WhenAct,Now()) > Now()) OR (IFNULL(WhenExp,Now()) < Now())) AS isActive
 
−
FROM cust_addrs;</mysql>
 
−
<section end=sql />
 
−
 
 
−
===Packages===
 
−
====qryPkgLines_byOrdLine_andItem====
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryPkgLines_byOrdLine_andItem AS
 
−
SELECT
 
−
  ID_OrdLine,
 
−
  ID_Item,
 
−
  Sum(
 
−
    IFNULL(pl.QtyShipped,0)+
 
−
    IFNULL(pl.QtyNotAvail,0)+
 
−
    IFNULL(pl.QtyKilled,0)
 
−
    ) AS QtyDone,
 
−
  Sum(pl.QtyShipped) AS QtyShipped,
 
−
  Sum(pl.QtyNotAvail) AS QtyNotAvail,
 
−
  Sum(pl.QtyKilled) AS QtyKilled,
 
−
  Sum(pl.QtyExtra) AS QtyExtra
 
−
FROM ord_pkg_lines AS pl
 
−
GROUP BY ID_OrdLine, ID_Item;</mysql>
 
−
<section end=sql />
 
−
====qryOrdLines_PkgdQtys====
 
−
* '''Depends on''': [[#qryPkgLines_byOrdLine_andItem]]
 
−
* '''Used by''': order detail in Item table
 
−
* '''History''':
 
−
** '''2008-11-21''': Added CatNum, Descr, OrdNum, WhenStarted, ID_Pull fields
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryOrdLines_PkgdQtys AS
 
−
SELECT
 
−
  ol.ID,
 
−
  ol.ID_Order,
 
−
  o.Number AS OrdNum,
 
−
  o.WhenStarted AS OrdDate,
 
−
  o.ID_Pull,
 
−
  ol.ID_Item,
 
−
  ol.CatNum,
 
−
  ol.Descr,
 
−
  ol.QtyOrd,
 
−
  pl.QtyDone,
 
−
  pl.QtyShipped,
 
−
  pl.QtyExtra,
 
−
  pl.QtyNotAvail,
 
−
  pl.QtyKilled
 
−
FROM
 
−
      (ord_lines AS ol
 
−
    LEFT JOIN qryPkgLines_byOrdLine_andItem AS pl ON pl.ID_OrdLine=ol.ID)
 
−
  LEFT JOIN core_orders AS o ON ol.ID_Order=o.ID;</mysql>
 
−
<section end=sql />
 
−
 
 
−
====qryOrdLines_open====
 
−
=====old version=====
 
−
'''Depends on''': [[#qryOrdLines_PkgdQtys]]
 
−
 
 
−
Very slow -- about takes 40 seconds
 
−
<mysql>CREATE OR REPLACE VIEW qryOrdLines_open AS
 
−
SELECT
 
−
  ol.ID,
 
−
  o.ID AS ID_Order,
 
−
  ol.ID_Item,
 
−
  Sum(ol.QtyOrd) AS QtyOrd,
 
−
  Sum(ol.QtyDone) AS QtyDone,
 
−
  Min(WhenOpened) AS WhenOldestOrder,
 
−
  Min(WhenNeeded) AS WhenSoonestDue
 
−
FROM qryOrders_Active AS o LEFT JOIN qryOrdLines_PkgdQtys AS ol ON ol.ID_Order=o.ID
 
−
WHERE IFNULL(ol.QtyOrd,0)-IFNULL(ol.QtyDone,0)<>0
 
−
GROUP BY ol.ID, o.ID, ol.ID_Item;</mysql>
 
−
=====new version=====
 
−
* '''Requires''': [[#qryOrders_Active]], [[#qryPkgLines_byOrdLine_andItem]]
 
−
* '''Notes''': Much faster -- about 7 seconds
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryOrdLines_open AS
 
−
SELECT
 
−
  ol.*,
 
−
  QtyDone,
 
−
  o.Number,
 
−
  WhenNeeded,
 
−
  WhenStarted
 
−
FROM
 
−
  (ord_lines AS ol
 
−
    LEFT JOIN qryOrders_Active AS o
 
−
    ON ol.ID_Order=o.ID)
 
−
  LEFT JOIN qryPkgLines_byOrdLine_andItem AS pl
 
−
  ON pl.ID_OrdLine=ol.ID
 
−
WHERE (o.ID IS NOT NULL) AND (IFNULL(QtyDone,0) <> QtyOrd)
 
−
ORDER BY Number, ID_Item;</mysql>
 
−
<section end=sql />
 
−
* '''History''':
 
−
** '''2008-11-02''' +WhenNeeded, +WhenStarted
 
−
 
 
−
===Restocks===
 
−
====qryCbx_Restocks====
 
−
''' Notes''': MySQL gives a "query cancelled" error when running this SQL to create the view, but actually it creates it just fine.
 
−
<section begin=sql /><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) 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>
 
−
<section end=sql />
 
−
 
 
−
====qryFrm_RestockLines====
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
 
 
−
* 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
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
====qryRstks_active====
 
−
* '''Returns''': List of restock requests for which we are currently expecting one or more shipments
 
−
* '''History''':
 
−
** '''2008-11-19''' Created for new restock process
 
−
** '''2008-11-20''' Including WhenClosed (new field) in the filter
 
−
* '''Notes''':
 
−
** We don't want to exclude requests just because a shipment has been received, because that shipment might not have included everything in the request. (We may need to add a WhenClosed field so we can indicate that no more shipments are expected.)
 
−
** The join with rstk_rcd isn't actually necessary, but it seems to make the query run faster. [[WTF]]? It can be removed at any time, since it doesn't affect the data downstream.
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstks_active AS
 
−
  SELECT rq.* FROM rstk_req AS rq
 
−
    LEFT JOIN rstk_rcd AS rc ON rc.ID_Restock=rq.ID
 
−
    WHERE (rq.WhenClosed IS NULL) AND (rq.WhenKilled IS NULL) AND (rq.WhenOrphaned IS NULL);</mysql>
 
−
<section end=sql />
 
−
 
 
−
====qryRstkLines_en_route====
 
−
* '''Deprecated''': this uses the old restock tables
 
−
* '''Returns''': Lines from restocks which exist but have not yet received or cancelled. It's assumed that any restocks just sitting around (not ordered but not cancelled) are being prepared and will be used for an actual order.
 
−
<mysql>CREATE OR REPLACE VIEW qryRstkLines_en_route AS
 
−
SELECT ri.*
 
−
FROM rstk_lines AS ri LEFT JOIN core_restocks AS r ON ri.ID_Restock=r.ID
 
−
WHERE (r.WhenReceived IS NULL) AND (r.WhenKilled IS NULL) AND (rq.WhenOrphaned IS NULL);</mysql>
 
−
====qryRstkItms_active====
 
−
* '''Returns''': Data about items which are in active restocks, i.e. on restock orders but not yet received (or cancelled).
 
−
* '''Note''': This is different from qryRstkLines_en_route (deprecated):
 
−
** Grouped by Item instead of Restock Line
 
−
** Uses new Restock tables
 
−
* '''History''':
 
−
** '''2008-11-18''' Created for new restock process
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkItms_active AS
 
−
SELECT
 
−
  rrq.ID_Restock,
 
−
  rrq.ID_Item,
 
−
  SUM(rrq.QtyOrd) AS QtyOrd
 
−
FROM
 
−
      rstk_req_item AS rrq
 
−
    LEFT JOIN qryRstks_active AS rq ON rrq.ID_Restock=rq.ID
 
−
WHERE rq.ID IS NOT NULL
 
−
GROUP BY rrq.ID_Restock, rrq.ID_Item;</mysql>
 
−
<section end=sql />
 
−
====qryRstkItms_en_route====
 
−
* '''Returns''': Restock items requested, not yet received, but possibly expected because the restock request hasn't been closed or orphaned.
 
−
* '''History''':
 
−
** '''2008-11-19''' Created for new restock process
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkItms_en_route AS
 
−
SELECT
 
−
  rqi.ID_Restock,
 
−
  rqi.ID_Item,
 
−
  SUM(rqi.QtyOrd) AS QtyOrd,
 
−
  SUM(rcl.QtyRecd) AS QtyRecd
 
−
FROM
 
−
  (rstk_rcd_line AS rcl
 
−
    LEFT JOIN rstk_rcd AS rc ON rcl.ID_RstkRcd=rc.ID)
 
−
    LEFT JOIN qryRstkItms_active AS rqi ON (rc.ID_Restock=rqi.ID_Restock) AND (rcl.ID_Item=rqi.ID_Item)
 
−
GROUP BY rqi.ID_Restock, rqi.ID_Item
 
−
HAVING IFNULL(QtyOrd,0)-IFNULL(QtyRecd,0);</mysql>
 
−
<section end=sql />
 
−
 
 
−
====v_rstk_lines_wItemInfo====
 
−
* '''Status''': deprecated (uses old restock tables)
 
−
<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>
 
−
====qryRstkReq_Item_Rcd_status====
 
−
* '''Returns''': Information about received shipments for a given Item in a given Restock Request
 
−
* '''History''':
 
−
** '''2008-11-21''' Created for new restock process
 
−
* '''Requires''': rstk_rcd_line, rstk_rcd, rstk_req
 
−
* '''Used by''': [[#qryRstkReq_Item_status]]
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkReq_Item_Rcd_status AS
 
−
SELECT
 
−
  rq.ID AS ID_RstkReq,
 
−
  MAX(rq.WhenOrdered) AS WhenOrdered,
 
−
  rcl.ID_Item,
 
−
  SUM(IFNULL(rcl.QtyFiled,rcl.QtyRecd)) AS QtyRecd
 
−
FROM
 
−
      (rstk_rcd_line AS rcl
 
−
    LEFT JOIN rstk_rcd AS rc ON rcl.ID_RstkRcd=rc.ID)
 
−
  LEFT JOIN rstk_req AS rq ON rc.ID_Restock=rq.ID
 
−
GROUP BY rq.ID, rcl.ID_Item;</mysql>
 
−
<section end=sql />
 
−
====qryRstkReq_Item_status====
 
−
* '''Returns''': Information about items Restock Requested and how many have been Received
 
−
* '''History''':
 
−
** '''2008-11-21''' Created for new restock process
 
−
* '''Requires''': [[#qryRstkReq_Item_Rcd_status]]
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRstkReq_Item_status AS
 
−
SELECT
 
−
  rci.*,
 
−
  rqi.QtyOrd,
 
−
  rqi.Notes
 
−
FROM
 
−
    rstk_req_item AS rqi
 
−
  LEFT JOIN qryRstkReq_Item_Rcd_status AS rci ON (rqi.ID_Restock=rci.ID_RstkReq) AND (rqi.ID_Item=rci.ID_Item)
 
−
WHERE rci.ID_RstkReq IS NOT NULL
 
−
ORDER BY rci.ID_RstkReq, rci.ID_Item;</mysql>
 
−
<section end=sql />
 
−
====qryRstkReq_wItem_info====
 
−
* '''Status''': not being used
 
−
* '''History''':
 
−
** '''2008-11-21''' Created for new restock process
 
−
<mysql>CREATE OR REPLACE VIEW qryRstkReq_wItem_info AS
 
−
SELECT
 
−
  rqi.*,
 
−
  i.ID_Title,
 
−
  r.WhenCreated,
 
−
  r.WhenOrdered
 
−
FROM
 
−
  (rstk_req_item AS rqi
 
−
  LEFT JOIN cat_items AS i ON rqi.ID_Itme=i.ID)
 
−
  LEFT JOIN rstk_req AS rq ON rqi.ID_Restock=rq.ID;</mysql>
 
  
 
===Stock===
 
===Stock===
−
====v_stk_lines_remaining====
+
====new queries====
−
View listing stk_items actually in stock, which is actually different things:
+
* [[/qryStk_Bins_w_info]]
−
* "forSale": items which are visible as "in stock" to customers
+
* [[/qryStk_lines_remaining]]
−
* "forShip": items which are available for shipping
+
** [[/qryStk_lines_remaining_byBin]]
−
* items neither for shipping nor for sale (probably some kind of recordkeeping purpose)
+
** [[/qryStk_lines_remaining_forSale]]
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW v_stk_lines_remaining AS
+
*** [[/qryStkItms_for_sale]]
−
  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>
 
−
<section end=sql />
 
−
====v_stk_items_remaining====
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
====v_stk_titles_remaining====
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
 
 
−
====v_stk_byItemAndBin====
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
====v_stk_byItemAndBin_wItemInfo====
 
−
Is anything actually using this? '''Document!''' (I've tentatively removed the link in Access.)
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
 
 
−
====v_stk_byItemAndBin_wInfo====
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
====qryStk_lines_remaining_forSale====
 
−
* '''Requires''': [[#v_stk_lines_remaining]]
 
−
* '''Used by''': [[#qryStkItms_for_sale]]
 
−
* '''Note''': Eventually rename as "qryStkLines_for_sale"
 
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStk_lines_remaining_forSale AS
 
−
  SELECT *
 
−
  FROM v_stk_lines_remaining
 
−
  WHERE QtyForSale>0;</mysql>
 
−
<section end=sql />
 
−
 
 
−
====qryStkItms_for_sale====
 
−
* '''Requires''': [[#qryStk_lines_remaining_forSale]]
 
−
* '''Used by''': [[#qryStkItms_for_sale_wItem_data]]
 
−
* '''History''':
 
−
** '''2008-11-17''' Created for revised restocking process
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
 
 
−
====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
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
 
 
−
====qryOrdLines_ord_status====
 
−
* '''Depends on''': [[#qryOrdLines_open]], [[#qryStk_lines_remaining_forSale]], [[#qryRstkLines_en_route]]
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
 
 
−
====qryOrdLines_to_restock====
 
−
* '''Depends on''': [[#qryOrdLines_ord_status]]
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
 
 
−
====qryFrm_OrdLines_to_restock====
 
−
'''Depends on''': [[#qryOrdLines_to_restock]]
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
 
 
−
====qryStk_lines_Title_info====
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
====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.
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
* '''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.
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
* '''History''':
 
−
** '''2008-11-02''' mods to accommodate rewritten source data
 
  
−
====qryItems_to_restock====
+
* [[/qryStk_items_remaining]]
−
'''Depends on''': [[#qryItems_ord_status]]
+
* [[/qryStk_byItem_byBin]]
−
<section begin=sql /><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>
 
−
<section end=sql />
 
  
−
====qryStock_forOpenOrders====
+
* [[/qryStk_lines_Title_info]]
−
* '''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]]
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
−
* 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====
+
====old queries====
−
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.
+
This was the first batch of queries I created, before I had decided to go with the qry prefix as in Access.
−
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_byOpt_andType AS
+
* [[/v_stk_titles_remaining]]
−
SELECT
+
* [[/v_stk_byItemAndBin_wItemInfo]]
−
  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>
 
−
<section end=sql />
 
−
====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.
 
−
<section begin=sql /><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>
 
−
<section end=sql />
 
  
 
===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 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"