VbzCart data views

From HTYP, the free directory anyone can edit

Contents 1 Navigation 2 Overview 3 Views 3.1 Catalog 3.1.1 qryCat_Depts 3.1.2 v_titles 3.1.3 qryTitles_ItTyps_grpItems 3.1.4 qryTitles_ItTyps_ItTyps 3.1.5 qryTitles_ItTyps_Titles 3.1.6 qryItTypsDepts_grpItems 3.1.7 qryItTypsDepts_ItTyps 3.1.8 qryCat_Titles 3.1.9 qryCat_Titles_Item_stats 3.1.10 qryCat_Titles_web 3.1.11 qryCbx_Titles 3.2 Catalog Items 3.2.1 v_items 3.2.2 qryCat_Items 3.2.3 qryCbx_Items 3.2.4 qryItems_prices 3.3 Catalog Sources 3.3.1 qryCtg_Sources_active 3.3.2 qryCtg_Items_updates 3.3.3 qryCtg_Items_updates_joinable 3.3.4 qryCtg_Items_active 3.3.5 qryCtg_Titles_active 3.3.6 qryCtg_build_sub 3.3.7 qryCtg_build 3.4 Orders 3.4.1 qryCbx_Orders 3.4.2 qryOrderLines_notPkgd 3.4.3 qryOrders_Active 3.5 Customers 3.5.1 qryCustAddrs 3.6 Packages 3.6.1 qryPkgLines_byOrdLine_andItem 3.6.2 qryOrdLines_PkgdQtys 3.6.3 qryOrdLines_open 3.7 Restocks 3.7.1 qryCbx_Restocks 3.7.2 qryFrm_RestockLines 3.7.3 qryFrm_RestockLines_byItem 3.7.4 qryRstkLines_en_route 3.7.5 v_rstk_lines_wItemInfo 3.8 Stock 3.8.1 v_stk_lines_remaining 3.8.2 v_stk_items_remaining 3.8.3 v_stk_titles_remaining 3.8.4 v_stk_byItemAndBin 3.8.5 v_stk_byItemAndBin_wItemInfo 3.8.6 v_stk_byItemAndBin_wInfo 3.8.7 qryStk_lines_remaining_forSale 3.8.8 qryOrdLines_ord_status 3.8.9 qryOrdLines_to_restock 3.8.10 qryFrm_OrdLines_to_restock 3.8.11 qryStk_lines_Title_info 3.8.12 qryOrdLines_status_union 3.8.13 qryItems_ord_status 3.8.14 qryItems_to_restock 3.8.15 qryStock_forOpenOrders 3.8.16 qryStock_byOpt_andType 3.8.17 qryStock_Titles_most_recent 3.9 Caching 3.9.1 v_data_flow

[edit] Navigation

VbzCart: data views

[edit] 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).

[edit] 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_.)

[edit] Catalog

[edit] qryCat_Depts

CREATE OR REPLACE VIEW qryCat_Depts AS
  SELECT
    d.ID,
    d.Name,
    d.Sort,
    d.CatKey,
    UPPER(IFNULL(d.PageKey,d.CatKey)) AS CatKey_def,
    d.ID_Supplier,
    UPPER(CONCAT_WS('-',s.CatKey,d.CatKey)) AS CatNum,
    LOWER(CONCAT_WS("/",s.CatKey,IFNULL(d.PageKey,d.CatKey))) AS CatWeb_Dept,
    LOWER(CONCAT_WS("/",s.CatKey,d.CatKey)) AS CatWeb_Title
  FROM cat_depts AS d LEFT JOIN cat_supp AS s ON d.ID_Supplier=s.ID;

[edit] v_titles

Deprecated -- use #qryCat_Titles_Item_stats instead -- this query still depends on cached _depts table. To be eliminated later.

CREATE OR REPLACE VIEW v_titles AS
SELECT
  ID_Title,
  ID_Dept,
  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;

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

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;

[edit] qryTitles_ItTyps_grpItems

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.

CREATE OR REPLACE VIEW qryTitles_ItTyps_grpItems AS
SELECT
  ID_Title, ID_ItTyp,
  SUM(IF(isForSale,1,0)) AS cntForSale,
  SUM(IF(isInPrint,1,0)) AS cntInPrint,
  SUM(IF(qtyInStock>0,1,0)) AS cntInStock,
  SUM(qtyInStock) AS qtyInStock,
  MIN(PriceSell) AS currMinPrice,
  MAX(PriceSell) AS currMaxPrice
FROM cat_items AS i
WHERE isForSale
GROUP BY ID_Title, ID_ItTyp;

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

[edit] qryTitles_ItTyps_ItTyps

CREATE OR REPLACE VIEW qryTitles_ItTyps_ItTyps AS
SELECT
  i.*,
  it.Sort                       AS ItTyp_Sort,
  it.NameSng                    AS ItTyp_Sng,
  IFNULL(it.NamePlr,it.NameSng) AS ItTyp_Plr
FROM 
qryTitles_ItTyps_grpItems AS i LEFT JOIN cat_ittyps AS it ON i.ID_ItTyp=it.ID;

[edit] qryTitles_ItTyps_Titles

CREATE OR REPLACE VIEW qryTitles_ItTyps_Titles AS
SELECT
  ti.*,
  t.Name,
  t.ID_Dept,
  t.CatNum,
  t.CatWeb
FROM qryTitles_ItTyps_grpItems AS ti LEFT JOIN v_titles AS t ON ti.ID_Title=t.ID_Title;

[edit] qryItTypsDepts_grpItems

CREATE OR REPLACE VIEW qryItTypsDepts_grpItems AS
SELECT
  ID_ItTyp, ID_Dept,
 
  SUM(IF(isForSale,1,0)) AS cntForSale,
  SUM(IF(isInPrint,1,0)) AS cntInPrint,
  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;

[edit] qryItTypsDepts_ItTyps

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;

[edit] qryCat_Titles

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;

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

[edit] qryCat_Titles_Item_stats

This query replaces v_titles and is used by qryCat_Titles_web.

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;

[edit] 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.

CREATE OR REPLACE VIEW qryCat_Titles_web AS
SELECT
  *,
  CAST(CONCAT("“",Name,"“: ",currMinSell,IF(currMinSell=currMaxSell,"",CONCAT(" – ",currMaxSell))) AS CHAR) AS ImgText
FROM qryCat_Titles_Item_stats;

[edit] qryCbx_Titles

CREATE OR REPLACE VIEW qryCbx_Titles AS
SELECT
  ID,
  CONCAT_WS(' ',CatNum,Name) AS Descr
FROM qryCat_Titles
ORDER BY CatNum;

[edit] Catalog Items

[edit] v_items

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

Used by clsItemsExt

[edit] qryCat_Items

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

[edit] qryCbx_Items

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;

• 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

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;

[edit] 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?)

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;

[edit] Catalog Sources

[edit] qryCtg_Sources_active

CREATE OR REPLACE VIEW qryCtg_Sources_active AS
  SELECT *
  FROM ctg_sources AS sc
  WHERE (sc.DateAvail <= NOW()) AND (sc.ID_Supercede IS NULL);

[edit] qryCtg_Items_updates

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;

[edit] qryCtg_Items_updates_joinable

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;

[edit] 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.

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

[edit] 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.

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

[edit] qryCtg_build_sub

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;

[edit] qryCtg_build

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

[edit] Orders

[edit] qryCbx_Orders

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;

• ID_Pull and WhenNeeded are needed for #qryStock_forOpenOrders

[edit] qryOrderLines_notPkgd

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

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;

[edit] 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

CREATE OR REPLACE VIEW qryOrders_Active AS
SELECT * FROM core_orders WHERE ID_Pull IS NULL;

[edit] Customers

[edit] qryCustAddrs

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

CREATE OR REPLACE VIEW qryCustAddrs AS
SELECT
  *,
  NOT ((IFNULL(WhenAct,NOW()) > NOW()) OR (IFNULL(WhenExp,NOW()) < NOW())) AS isActive
 FROM cust_addrs;

[edit] Packages

[edit] qryPkgLines_byOrdLine_andItem

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;

[edit] qryOrdLines_PkgdQtys

Depends on: qryPkgLines_byOrdLine_andItem

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;

[edit] qryOrdLines_open

Depends on: qryOrdLines_PkgdQtys

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;

[edit] Restocks

[edit] 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

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;

[edit] qryFrm_RestockLines

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;

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

[edit] qryFrm_RestockLines_byItem

Basically the same as qryFrm_RestockLines, but grouped by ID_Item and with more Item-related fields

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;

[edit] 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.

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

[edit] v_rstk_lines_wItemInfo

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;

[edit] Stock

[edit] 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)

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

[edit] v_stk_items_remaining

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;

[edit] v_stk_titles_remaining

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;

[edit] v_stk_byItemAndBin

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;

[edit] v_stk_byItemAndBin_wItemInfo

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

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;

[edit] v_stk_byItemAndBin_wInfo

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;

[edit] qryStk_lines_remaining_forSale

CREATE OR REPLACE VIEW qryStk_lines_remaining_forSale AS
  SELECT *
  FROM v_stk_lines_remaining
  WHERE QtyForSale>0;

[edit] qryOrdLines_ord_status

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

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;

[edit] qryOrdLines_to_restock

• Depends on: qryItems_ord_status

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;

[edit] qryFrm_OrdLines_to_restock

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;

[edit] qryStk_lines_Title_info

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;

[edit] qryOrdLines_status_union

• Depends on: qryOrdLines_open, qryRstkLines_en_route, v_stk_lines_remaining

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.

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;

[edit] qryItems_ord_status

Depends on: qryItems_ord_status_union 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.

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;

[edit] qryItems_to_restock

Depends on: qryItems_ord_status

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;

[edit] qryStock_forOpenOrders

Show all stock available to fill open orders, along with helpful information about the Bin, Order, and Item:

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

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

[edit] 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.

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;

[edit] 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.

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;

[edit] Caching

Caching should only be used for catalog display.

[edit] v_data_flow

  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;