VbzCart/queries

Navigation

VbzCart: data views

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).

Views

"Views" in MySQL are essentially identical to stored queries in MS Access.

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_.)

Catalog

qryCat_Depts

<mysql>CREATE OR REPLACE VIEW qryCat_Depts AS

 SELECT
   d.ID,
   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>

These will later reference cat_titles instead of titles, when that table is fully migrated.

v_titles

This version still depends on cached _depts table; eliminate this later.

<mysql>CREATE OR REPLACE VIEW v_titles AS SELECT

 ID_Title,
 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_Supp,
 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 _depts AS d ON t.ID_dept=d.ID GROUP BY i.ID_Title</mysql>

Old version which depends on 2 cached tables (trying to minimize usage of these):

<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,
   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>

• CatKey_Title is used in the catalog-building process (Enter Title Groups) when searching within a supplier for a particular title

Catalog 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.

qryCbx_Items

<mysql>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;

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;</mysql>

• 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

<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>

qryItems_ord_status

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.

<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.WhenOldestOrder) AS WhenOldestOrder,
 MIN(i.WhenSoonestDue) 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;</mysql>

qryItems_to_restock

<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>

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?)

<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>

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

Active order lines which have yet to be (completely) put into a package

<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>

qryOrders_Active

Show 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

<mysql>CREATE OR REPLACE VIEW qryOrders_Active AS SELECT * FROM core_orders WHERE ID_Pull IS NULL;</mysql>

qryOrdLines_PkgdQtys

<mysql>CREATE OR REPLACE VIEW qryOrdLines_PkgdQtys AS SELECT

 ol.ID,
 ol.ID_Order,
 ol.ID_Item,
 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;</mysql>

qryOrdLines_open

<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>

qryOrdLines_status_union

This is another one of those union queries that is "cancelled" while you're trying to create it... but in this case, it does get created.

<mysql>CREATE OR REPLACE VIEW qryOrdLines_status_union AS SELECT /* 1 */ CONCAT("oio-",oio.ID) AS Source, /* 2 */ oio.ID AS ID, /* 3 */ oio.ID_Order, /* 4 */ oio.ID_Item, /* 5 */ oio.QtyOrd, /* 6 */ oio.QtyDone, /* 7 */ 0 AS QtyOnRstk, /* 8 */ 0 AS QtyOnHand, /* 9 */ 0 AS QtyMin, /* 10 */ oio.WhenOldestOrder, /* 11 */ oio.WhenSoonestDue

   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 WhenOldestOrder, /* 11 */ NULL AS WhenSoonestDue

  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 WhenOldestOrder, /* 11 */ NULL AS WhenSoonestDue

 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 WhenOldestOrder, /* 11 */ NULL AS WhenSoonestDue

 FROM cat_items AS itm;</mysql>

Alternate version with every single quantity explicitly typecast (which only seems to have the effect of making all of those columns show up as Strings in Access, so don't use this version):

<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 */ CAST(oio.QtyOrd AS SIGNED), /* 6 */ CAST(oio.QtyDone AS SIGNED), /* 7 */ CAST(0 AS SIGNED) AS QtyOnRstk, /* 8 */ CAST(0 AS SIGNED) AS QtyOnHand, /* 9 */ CAST(0 AS SIGNED) AS QtyMin, /* 10 */ oio.WhenOldestOrder, /* 11 */ oio.WhenSoonestDue 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 */ CAST(0 AS SIGNED) AS QtyOrd, /* 6 */ CAST(0 AS SIGNED) AS QtyDone, /* 7 */ CAST(IF(rie.QtyExp > rie.QtyOrd,rie.QtyExp,rie.QtyOrd) AS SIGNED) AS QtyOnRstk, /* 8 */ CAST(0 AS SIGNED) AS QtyOnHand, /* 9 */ CAST(0 AS SIGNED) AS QtyMin, /* 10 */ NULL AS WhenOldestOrder, /* 11 */ NULL AS WhenSoonestDue

 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 */ CAST(0 AS SIGNED) AS QtyOrd, /* 6 */ CAST(0 AS SIGNED) AS QtyDone, /* 7 */ CAST(0 AS SIGNED) AS QtyOnRstk, /* 8 */ CAST(srs.QtyForShip AS SIGNED) AS QtyOnHand, /* 9 */ CAST(0 AS SIGNED) AS QtyMin, /* 10 */ NULL AS WhenOldestOrder, /* 11 */ NULL AS WhenSoonestDue

 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 */ CAST(0 AS SIGNED) AS QtyOrd, /* 6 */ CAST(0 AS SIGNED) AS QtyDone, /* 7 */ CAST(0 AS SIGNED) AS QtyOnRsk, /* 8 */ CAST(0 AS SIGNED) AS QtyOnHand, /* 9 */ CAST(QtyMin_Stk AS SIGNED) AS QtyMin, /* 10 */ NULL AS WhenOldestOrder, /* 11 */ NULL AS WhenSoonestDue

 FROM cat_items AS itm;</mysql>

Packages

qryPkgLines_byOrdLine_andItem

<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>

Restocks

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) 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>

qryRstkLines_en_route

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);</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_titles_remaining

<mysql>CREATE OR REPLACE VIEW v_stk_titles_remaining AS

 SELECT
   ID_Title,
   SUM(QtyForSale) AS QtyForSale,
   SUM(QtyForShip) AS QtyForShip,
   SUM(QtyExisting) AS QtyExisting
 FROM v_stk_lines_remaining AS s LEFT JOIN cat_items AS i ON s.ID_Item=i.ID
 GROUP BY i.ID_Title;</mysql>

v_stk_byItemAndBin

<mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin AS

 SELECT
   ID_Item,
   ID_Bin,
   SUM(QtyForSale) AS QtyForSale,
   SUM(QtyForShip) AS QtyForShip,
   SUM(QtyExisting) AS QtyExisting
 FROM v_stk_lines_remaining
 GROUP BY ID_Item, ID_Bin
 HAVING SUM(QtyExisting)>0;</mysql>

v_stk_byItemAndBin_wItemInfo

Is anything actually using this? Document! (I've tentatively removed the link in Access.)

<mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin_wItemInfo AS

 SELECT * FROM v_stk_byItemAndBin AS s LEFT JOIN qryCat_Items AS i ON s.ID_Item=i.ID;</mysql>

v_stk_byItemAndBin_wInfo

<mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin_wInfo AS

 SELECT
   s.*,
   i.*,
   b.ID_Place,
   b.Code AS BinCode,
   b.Descr AS BinDescr
 FROM
 (
   v_stk_byItemAndBin AS s LEFT JOIN qryCat_Items AS i ON s.ID_Item=i.ID
 ) LEFT JOIN stk_bins AS b ON s.ID_Bin=b.ID;</mysql>

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.

qryStock_byOpt_andType

This is used for displaying the "stock" area. The basic records are items, but they're only ones that are in stock, so "Stock" is shorthand for "items that are in stock" in this case.

<mysql>CREATE OR REPLACE VIEW qryStock_byOpt_andType AS SELECT

 i.ID_ItTyp,
 i.ID_ItOpt,
 SUM(i.qtyInStock) AS qtyInStock,
 it.NameSng AS It_NameSng,
 it.NamePlr AS It_NamePlr,
 io.CatKey AS Io_CatKey,
 io.Descr AS Io_Descr,
 IFNULL(it.Sort,'ZZZ') AS It_Sort

FROM (cat_items AS i LEFT JOIN cat_ioptns AS io ON i.ID_ItOpt=io.ID)

 LEFT JOIN cat_ittyps AS it ON i.ID_ItTyp=it.ID

GROUP BY i.ID_ItTyp, i.ID_ItOpt, io.Sort HAVING SUM(i.qtyInStock) ORDER BY It_Sort,io.Sort;</mysql>

qryStock_Titles_most_recent

This defines what the "latest stock" page shows; as given here, it displays the 100 titles most recently added to stock.

<mysql>CREATE OR REPLACE VIEW qryStock_Titles_most_recent AS SELECT

 i.ID_Title,
 s.QtyForSale AS QtyAdded,
 MAX(s.WhenAdded) AS WhenAdded,
 st.QtyForSale AS QtyAvail

FROM (v_stk_lines_remaining AS s LEFT JOIN cat_items AS i ON s.ID_Item=i.ID) LEFT JOIN v_stk_titles_remaining AS st ON i.ID_Title=st.ID_Title GROUP BY i.ID_Title HAVING QtyAdded ORDER BY WhenAdded DESC LIMIT 100;</mysql>

Caching

Caching should only be used for catalog display.

v_data_flow

<mysql> CREATE OR REPLACE VIEW v_data_flow AS

   SELECT
     df.ID_Srce,
     df.ID_Dest,
     df.ID_Proc,
     dfx.doesClear
   FROM data_flow AS df LEFT JOIN data_procs AS dfx ON df.ID_Proc=dfx.ID
   ORDER BY dfx.doesClear;</mysql>