VbzCart/queries

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

Inactive

• /deprecated: queries we're trying to get rid of, once we're sure nothing uses them
• /discarded: queries apparently no longer in use

Catalog

• /qryCat_Depts
• /qryTitles_ItTyps_grpItems
• /qryTitles_ItTyps_ItTyps
• /qryTitles_ItTyps_Titles
• /qryItTypsDepts_grpItems
• /qryItTypsDepts_ItTyps
• /qryCat_Titles
• /qryCat_Titles_Item_stats
• /qryCat_Titles_web
• /qryCbx_Titles

Catalog Items

• /qryCat_Items
• /qryCbx_Items_data
  • /qryCbx_Items
  • /qryCbx_Items_active
  • /qryCbx_Items_for_sale
  • /qryCbx_Items_opt: abbreviated version for contexts where Title is already known
• /qryItems_prices: what uses this?

Catalog Sources

qryCtg_Sources_active

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

qryCtg_Items_updates

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

qryCtg_Items_updates_joinable

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

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.

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

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.

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

qryCtg_build_sub

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

qryCtg_build

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

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

• 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

<mysql>CREATE OR REPLACE VIEW qryOrders_Active AS SELECT * FROM core_orders WHERE

 (ID_Pull IS NULL) AND
 (WhenClosed IS NULL);</mysql>

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

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

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

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

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

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

• 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

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

• History:
  • 2008-11-16 created for new restocking process

qryOrdItms_open

• Requires: #qryOrdItms_Pkg_qtys
• History:
  • 2008-11-16 created for new restocking process

<mysql>CREATE OR REPLACE VIEW qryOrdItms_open AS SELECT * FROM qryOrdItms_Pkg_qtys WHERE QtyOpen;</mysql>

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

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

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, cat_items, #qryRstkItms_en_route
• Used by: #qryItms_to_restock
• History:
  • 2008-11-18 created for new restocking process (under construction; need to add items-in-transit for restocks)
  • 2008-11-22 in-transit items should never be negative; if we receive something not requested, it goes into stock

<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,
 IF((IFNULL(QtyOrd,0)-IFNULL(QtyRecd,0))>0,IFNULL(QtyOrd,0)-IFNULL(QtyRecd,0),0) AS QtyOnOrder

FROM qryRstkItms_en_route;</mysql>

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 various quantities need to be SUMmed, else we got the wrong answer (probably just the first matching row)
• 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).

<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(iru.QtyOnOrder) AS QtyOnOrder,
 SUM(IFNULL(QtyOpen,0))
   -SUM(IFNULL(QtyForSale,0))
   +SUM(IFNULL(QtyMin_Stk,0))
   -SUM(IFNULL(QtyOnOrder,0))
   AS QtyToGet

FROM qryItms_to_restock_union AS iru GROUP BY ID_Item HAVING QtyToGet>0;</mysql>

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)

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

Customers

qryCustAddrs

Any basic simple calculations not involving other tables can be added to this query as needed.

<mysql>CREATE OR REPLACE VIEW qryCustAddrs AS SELECT

 *,
 NOT ((IFNULL(WhenAct,Now()) > Now()) OR (IFNULL(WhenExp,Now()) < Now())) AS isActive
FROM cust_addrs;</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>

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

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

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

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

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

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

qryRstks_info

• Returns: Information about restock requests and receipts, one line per receipt (or request, if nothing received)
• History:
  • 2008-11-30 Created for new restock process
• Notes:
  • Creates one list by left-joining received restocks with their requests, another list of requests with no receipts, and then UNIONs them together
• Requires: rstk_req, rstk_rcd
• Used by: (MS Access) sfrm_SuppRstks

<mysql>CREATE OR REPLACE VIEW qryRstks_info AS SELECT

 rq.ID AS ID_Req,
 rq.ID_Supplier,
 rq.PurchOrdNum,
 rq.SuppOrdNum,
 rq.WhenCreated,
 rq.WhenOrdered,
 rq.WhenKilled,
 rq.WhenOrphaned,
 rq.WhenClosed,
 rq.Notes AS NotesReq,
 rc.ID AS ID_Rcd,
 rc.SuppInvcNum,
 rc.WhenReceived,
 rc.TotalInvMerch,
 rc.TotalInvFinal,
 rc.Notes AS NotesRcd

FROM

 rstk_rcd AS rc LEFT JOIN rstk_req AS rq ON rc.ID_Restock=rq.ID

UNION SELECT

 rq.ID AS ID_Req,
 rq.ID_Supplier,
 rq.PurchOrdNum,
 rq.SuppOrdNum,
 rq.WhenCreated,
 rq.WhenOrdered,
 rq.WhenKilled,
 rq.WhenOrphaned,
 rq.WhenClosed,
 rq.Notes AS NotesReq,
 rc.ID AS ID_Rcd,
 rc.SuppInvcNum,
 rc.WhenReceived,
 rc.TotalInvMerch,
 rc.TotalInvFinal,
 rc.Notes AS NotesRcd

FROM

 rstk_req AS rq LEFT JOIN rstk_rcd AS rc ON rc.ID_Restock=rq.ID
 WHERE rc.ID IS NULL;</mysql>

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.)
  • At one point, doing a JOIN with rstk_rcd seemed to make the query run faster, but this may have been an illusion due to caching by MySQL and not enough testing.

<mysql>CREATE OR REPLACE VIEW qryRstks_active AS

 SELECT rq.* FROM rstk_req AS rq
   WHERE (rq.WhenClosed IS NULL) AND (rq.WhenKilled IS NULL) AND (rq.WhenOrphaned IS NULL);</mysql>

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
• Requires: rstk_req_item, #qryRstks_active
• History:
  • 2008-11-18 Created for new restock process

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

qryRstkItms_en_route

• Returns: Restock items requested, not yet received, but possibly expected because the restock request hasn't been closed or orphaned.
• Requires: rstk_rcd_line, rstk_rcd, #qryRstkItms_active
• History:
  • 2008-11-19 Created for new restock process
  • 2008-11-24 QtyRecd falls back on QtyFiled if null

<mysql>CREATE OR REPLACE VIEW qryRstkItms_en_route AS SELECT

 rqi.ID_Restock,
 rqi.ID_Item,
 SUM(rqi.QtyOrd) AS QtyOrd,
 SUM(IFNULL(rcl.QtyRecd,rcl.QtyFiled)) 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>

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
  • 2008-11-23 WhenOrdered -> WhenFirstOrder (MIN), WhenFinalOrder (MAX)
• Requires: rstk_rcd_line, rstk_rcd, rstk_req
• Used by: #qryRstkReq_Item_status
• Notes:
  • WhenFirstOrder is the timestamp of the earliest open customer order for this item. This is so we know the longest time that anyone has been waiting for this item, and hence how urgent the restock is.
  • WhenFinalOrder is the timestamp of the latest open customer order for this item. This is so we know if there have been further requests for this item, adding further urgency.

<mysql>CREATE OR REPLACE VIEW qryRstkReq_Item_Rcd_status AS SELECT

 rq.ID AS ID_RstkReq,
 MIN(rq.WhenOrdered) AS WhenFirstOrder,
 MAX(rq.WhenOrdered) AS WhenFinalOrder,
 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>

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
• Notes:
  • WhenFirstOrder / WhenFinalorder are timestamps of customer orders for this item
• Used by: #qryRstkReq_Item_status_Req_info

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

qryRstkReq_Item_status_Req_info

• Returns: Data from #qryRstkReq_Item_status with some additional info about the request
• History:
  • 2008-11-23 Created for new restock process
• Requires: #qryRstkReq_Item_status, rstk_req
• Used by: sfrmItem_Rstks

<mysql>CREATE OR REPLACE VIEW qryRstkReq_Item_status_Req_info AS SELECT

 rqis.*,
 IFNULL(rq.WhenOrdered,rq.WhenCreated) AS WhenMade,
 rq.WhenClosed,
 rq.WhenKilled

FROM qryRstkReq_Item_status AS rqis LEFT JOIN rstk_req AS rq ON rqis.ID_RstkReq=rq.ID ORDER BY WhenMade,ID_RstkReq;</mysql>

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

v_stk_lines_remaining

View listing stk_items actually in stock, which is actually different things:

• "forSale": items which are visible as "in stock" to customers
• "forShip": items which are available for shipping
• items neither for shipping nor for sale (probably some kind of recordkeeping purpose)

<mysql>CREATE OR REPLACE VIEW v_stk_lines_remaining AS

 SELECT
   st.ID,
   st.ID_Bin,
   st.ID_Item,
   IF(sb.isForSale,st.Qty,0) AS QtyForSale,
   IF(sb.isForShip,st.Qty,0) AS QtyForShip,
   st.Qty AS QtyExisting,
   st.CatNum,
   st.WhenAdded,
   st.WhenChanged,
   st.WhenCounted,
   st.Notes,
   sb.ID_Place,
   sp.Name AS WhName
   FROM
     (
       stk_items AS st
       LEFT JOIN stk_bins AS sb
         ON sb.ID=st.ID_Bin
      )
      LEFT JOIN stk_places AS sp
        ON sb.ID_Place=sp.ID
   WHERE (st.WhenRemoved IS NULL) AND (sb.WhenVoided IS NULL) AND (st.Qty <> 0);</mysql>

v_stk_items_remaining

<mysql>CREATE OR REPLACE VIEW v_stk_items_remaining AS

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

v_stk_titles_remaining

<mysql>CREATE OR REPLACE VIEW v_stk_titles_remaining AS

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

v_stk_byItemAndBin

<mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin AS

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

v_stk_byItemAndBin_wItemInfo

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

<mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin_wItemInfo AS

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

v_stk_byItemAndBin_wInfo

<mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin_wInfo AS

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

qryStk_lines_remaining_forSale

• Requires: #v_stk_lines_remaining
• Used by: #qryStkItms_for_sale
• Note: Eventually rename as "qryStkLines_for_sale"

<mysql>CREATE OR REPLACE VIEW qryStk_lines_remaining_forSale AS

 SELECT *
 FROM v_stk_lines_remaining
 WHERE QtyForSale>0;</mysql>

qryStkItms_for_sale

• Requires: #qryStk_lines_remaining_forSale
• Used by: #qryStkItms_for_sale_wItem_data
• History:
  • 2008-11-17 Created for revised restocking process

<mysql>CREATE OR REPLACE VIEW qryStkItms_for_sale AS SELECT

 ID_Item,
 SUM(qsl.QtyForSale) AS QtyForSale,
 SUM(qsl.QtyForShip) AS QtyForShip,
 SUM(qsl.QtyExisting) AS QtyExisting

FROM qryStk_lines_remaining_forSale AS qsl GROUP BY ID_Item;</mysql>

qryStkItms_for_sale_wItem_data

• Requires: #qryStkItms_for_sale
• Note: this one may be redundant; the time when we need the item data is *after* all the sources of items-to-restock have been UNIONed together and GROUPed by item
• History:
  • 2008-11-17 Created for revised restocking process

<mysql>CREATE OR REPLACE VIEW qryStkItms_for_sale_wItem_data AS SELECT

 qsi.*,
 i.*

FROM qryStkItms_for_sale AS qsi LEFT JOIN cat_items AS i ON qsi.ID_Item=i.ID;</mysql>

qryOrdLines_ord_status

• Depends on: #qryOrdLines_open, #qryStk_lines_remaining_forSale, #qryRstkLines_en_route

<mysql>CREATE OR REPLACE VIEW qryOrdLines_ord_status AS SELECT

 i.ID AS ID_Item,
 i.CatNum,
 qoi.ID AS ID_OrdLine,
 qoi.ID_Order,
 qsr.ID AS ID_Stock,
 qri.ID AS ID_RstkItem,
 IFNULL(qoi.QtyOrd,0) AS QtyOrd,
 IFNULL(qoi.QtyDone,0) AS QtyDone,
 IFNULL(qri.QtyExp IS NULL,IFNULL(qri.QtyOrd,0)) AS QtyOnRstk,
 IFNULL(qsr.QtyForShip,0) AS QtyOnHand,
 i.QtyMin_Stk AS QtyMin,
 qoi.WhenOldestOrder,
 qoi.WhenSoonestDue

FROM

 (
   (cat_items AS i LEFT JOIN qryOrdLines_open AS qoi ON qoi.ID_Item=i.ID)
  LEFT JOIN qryStk_lines_remaining_forSale AS qsr ON qsr.ID_Item=i.ID)
LEFT JOIN qryRstkLines_en_route AS qri ON qri.ID_Item=i.ID;</mysql>

qryOrdLines_to_restock

• Depends on: #qryOrdLines_ord_status

<mysql>CREATE OR REPLACE VIEW qryOrdLines_to_restock AS SELECT

 ID_Item,
 i.ID_OrdLine,
 i.ID_Order,
 Sum(i.QtyOrd) AS QtyOrd,
 Sum(i.QtyDone) AS QtyDone,
 Sum(i.QtyOnRstk) AS QtyOnRstk,
 Sum(i.QtyOnHand) AS QtyOnHand,
 Max(i.QtyMin) AS QtyMin,
 Max(IFNULL(i.QtyMin,0))+Sum(IFNULL(i.QtyOrd,0)-IFNULL(i.QtyOnRstk,0)-IFNULL(i.QtyOnHand,0)) AS QtyToGet,
 Sum(IFNULL(i.QtyOrd,0)-IFNULL(i.QtyOnRstk,0)-IFNULL(i.QtyOnHand,0)) AS QtyShort,
 Min(i.WhenOldestOrder) AS WhenOldestOrder,
 Min(i.WhenSoonestDue) AS WhenSoonestDue,
 Count(ID_RstkItem) AS RstkLines

FROM qryOrdLines_ord_status AS i GROUP BY ID_Item, ID_OrdLine, ID_Order HAVING Max(IFNULL(i.QtyMin,0))+Sum(IFNULL(i.QtyOrd,0)-IFNULL(i.QtyOnRstk,0)-IFNULL(i.QtyOnHand,0))>0;</mysql>

qryFrm_OrdLines_to_restock

Depends on: #qryOrdLines_to_restock

<mysql>CREATE OR REPLACE VIEW qryFrm_OrdLines_to_restock AS SELECT

 qi.*,
 ci.CatNum,
 ci.isForSale,
 ci.ID_Supplier,
 ci.PriceSell,
 ci.PriceBuy

FROM qryOrdLines_to_restock AS qi

 LEFT JOIN qryCat_Items AS ci ON ci.ID=qi.ID_Item;</mysql>

qryStk_lines_Title_info

<mysql>CREATE OR REPLACE VIEW qryStk_lines_Title_info AS SELECT

 s.*,
 i.ID_Title

FROM stk_items AS s LEFT JOIN cat_items AS i ON s.ID_Item=i.ID;</mysql>

qryOrdLines_status_union

• Requires: #qryOrdLines_open, #qryRstkLines_en_route, #v_stk_lines_remaining
• Notes: This is another one of those union queries that is "cancelled" while you're trying to create it... but in this case, it does get created.

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

   FROM qryOrdLines_open AS oio

UNION SELECT /* 1 */ CONCAT("rie-",rie.ID) AS Source, /* 2 */ rie.ID AS ID, /* 3 */ rie.ID_Order, /* 4 */ rie.ID_Item, /* 5 */ 0 AS QtyOrd, /* 6 */ 0 AS QtyDone, /* 7 */ IF(rie.QtyExp > rie.QtyOrd,rie.QtyExp,rie.QtyOrd) AS QtyOnRstk, /* 8 */ 0 AS QtyOnHand, /* 9 */ 0 AS QtyMin, /* 10 */ NULL AS WhenStarted, /* 11 */ NULL AS WhenNeeded

  FROM qryRstkLines_en_route AS rie

UNION SELECT /* 1 */ CONCAT("srs-",srs.ID) AS Source, /* 2 */ 0 AS ID, /* 3 */ NULL AS ID_Order, /* 4 */ srs.ID_Item, /* 5 */ 0 AS QtyOrd, /* 6 */ 0 AS QtyDone, /* 7 */ 0 AS QtyOnRstk, /* 8 */ srs.QtyForShip AS QtyOnHand, /* 9 */ 0 AS QtyMin, /* 10 */ NULL AS WhenStarted, /* 11 */ NULL AS WhenNeeded

 FROM v_stk_lines_remaining AS srs

UNION SELECT /* 1 */ CONCAT("itm-",itm.ID) AS Source, /* 2 */ NULL AS ID, /* 3 */ NULL AS ID_Order, /* 4 */ itm.ID AS ID_Item, /* 5 */ 0 AS QtyOrd, /* 6 */ 0 AS QtyDone, /* 7 */ 0 AS QtyOnRsk, /* 8 */ 0 AS QtyOnHand, /* 9 */ QtyMin_Stk AS QtyMin, /* 10 */ NULL AS WhenStarted, /* 11 */ NULL AS WhenNeeded

 FROM cat_items AS itm;</mysql>

• History:
  • 2008-11-02 changed When field names (more accurate)

qryItems_ord_status

• Depends on: #qryOrdLines_status_union
• Returns: Status with regard to ordering, i.e. quantities on hand, quantities needed, etc. Used by the "items needed (for restocking)" form, via #qryItems_to_restock. This was originally in a different section, but it has to be created after qryOrdLines_status_union.

<mysql>CREATE OR REPLACE VIEW qryItems_ord_status AS SELECT

 i.ID_Item,
 SUM(i.QtyOrd) AS QtyOrd,
 SUM(i.QtyDone) AS QtyDone,
 SUM(i.QtyOnRstk) AS QtyOnRstk,
 SUM(i.QtyOnHand) AS QtyOnHand,
 MAX(i.QtyMin) AS QtyMin,
 SUM(
   IFNULL(i.QtyOrd,0)-IFNULL(i.QtyOnRstk,0)-IFNULL(i.QtyOnHand,0)
 )+IFNULL(MAX(i.QtyMin),0) AS QtyToGet,
 MIN(i.WhenStarted) AS WhenOldestOrder,
 MAX(i.WhenNeeded) AS WhenSoonestDue,
 COUNT(i.Source) AS RecCount,
 CAST(GROUP_CONCAT(DISTINCT i.Source SEPARATOR ' ') AS CHAR) AS Sources

FROM qryOrdLines_status_union AS i GROUP BY i.ID_Item HAVING QtyToGet>0;</mysql>

• History:
  • 2008-11-02 mods to accommodate rewritten source data

qryItems_to_restock

Depends on: #qryItems_ord_status

<mysql>CREATE OR REPLACE VIEW qryItems_to_restock AS SELECT

 qi.*,
 ci.CatNum,
 ci.isForSale,
 ci.ID_Supplier,
 ci.QtyMin_Stk,
 ci.QtyMin_Rstk_Title,
 ci.PriceSell,
 ci.PriceBuy

FROM qryItems_ord_status AS qi LEFT JOIN qryCat_Items AS ci ON ci.ID=qi.ID_Item WHERE QtyToGet>0;</mysql>

qryStock_forOpenOrders

• Action: Show all stock available to fill open orders, along with helpful information about the Bin, Order, and Item
• Depends on: #v_stk_byItemAndBin_wInfo, #qryOrderLines_notPkgd, #qryCbx_Orders

<mysql>CREATE OR REPLACE VIEW qryStock_forOpenOrders AS SELECT

 s.ID_Bin,
 s.ID_Item,
 oi.ID_Order,
 oi.QtyOpen,
 s.QtyForSale,
 s.QtyForShip,
 o.WhenNeeded,
 o.Descr AS OrdText,
 o.ID_Pull,
 CONCAT_WS(' ',s.CatNum,s.Descr) AS ItemText,

/* s.BinCode,

 s.BinDescr,  are these ever needed? */
 CONCAT_WS(' ',s.BinCode,s.BinDescr) AS BinText

FROM (v_stk_byItemAndBin_wInfo AS s LEFT JOIN qryOrderLines_notPkgd AS oi ON s.ID_Item=oi.ID_Item) LEFT JOIN qryCbx_Orders AS o ON oi.ID_Order=o.ID WHERE (NOT s.isPulled) AND (o.ID_Pull IS NULL) AND (QtyOpen) AND (QtyForShip);</mysql>

• There's a possibility this query will return wrong information when there are multiple stock lines corresponding to the items needed; further testing would be a good idea.

qryStock_byOpt_andType

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

<mysql>CREATE OR REPLACE VIEW qryStock_byOpt_andType AS SELECT

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

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

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

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

qryStock_Titles_most_recent

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

<mysql>CREATE OR REPLACE VIEW qryStock_Titles_most_recent AS SELECT

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

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

Caching

Caching should only be used for catalog display.

v_data_flow

<mysql> CREATE OR REPLACE VIEW v_data_flow AS

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