Difference between revisions of "VbzCart/queries/deprecated"

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
Jump to navigation Jump to search
(moved from single-page listing)
 
(moved qryCbx_Restosks* series to "discarded")
 
(8 intermediate revisions by the same user not shown)
Line 29: Line 29:
 
     tx.qtyInStock
 
     tx.qtyInStock
 
       FROM titles AS t LEFT JOIN _titles AS tx on t.ID=tx.ID;</mysql>
 
       FROM titles AS t LEFT JOIN _titles AS tx on t.ID=tx.ID;</mysql>
 +
==v_rstk_lines_wItemInfo==
 +
* '''Status''': deprecated (uses old restock tables); can probably be deleted, since nothing should be using those tables anymore.
 +
<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>
 +
 +
==qryFrm_RestockLines==
 +
===Details===
 +
* '''Status''': deprecated (uses old restock table); can probably be deleted, since nothing should be using those tables anymore.
 +
* '''Notes''': 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.
 +
===SQL===
 +
<mysql>CREATE OR REPLACE VIEW qryFrm_RestockLines AS
 +
SELECT
 +
  ri.ID,
 +
  ri.ID_Restock,
 +
  ri.ID_Item,
 +
  ci.CatNum,
 +
  ci.Supp_CatNum,
 +
  ci.PriceSell,
 +
  ci.QtyMin_Stk,
 +
  o.Number AS OrderName,
 +
  ci.Descr AS ItemDescr,
 +
  o.WhenNeeded,
 +
  If(t.RstkMin = 1, '', t.RstkMin) AS RstkMinStr,
 +
  ri.QtyOrd,
 +
  ri.QtyNeed,
 +
  ri.QtyExp,
 +
  ri.InvcLineNo,
 +
  ri.InvcQtyOrd,
 +
  ri.InvcQtySent,
 +
  ri.isGone,
 +
  ri.QtyRecd,
 +
  ri.QtyFiled,
 +
  ri.CostExpPer,
 +
  ri.CostInvPer,
 +
  ri.CostInvTot,
 +
  ri.CostActTot,
 +
  ri.CostActBal,
 +
  ri.Notes
 +
FROM
 +
(
 +
  (rstk_lines AS ri
 +
    LEFT JOIN core_orders AS o ON ri.ID_Order=o.ID)
 +
  LEFT JOIN qryCbx_Items_data AS ci ON ri.ID_Item=ci.ID)
 +
LEFT JOIN cat_titles AS t ON ci.ID_Title=t.ID;</mysql>
 +
<section end=sql />
 +
==qryFrm_RestockLines_byItem==
 +
===Details===
 +
* '''Notes''': Basically the same as [[#qryFrm_RestockLines]], but grouped by ID_Item and with more Item-related fields
 +
* '''Status''': deprecated (uses old restock table); can probably be deleted, since nothing should be using those tables anymore.
 +
===SQL===
 +
<mysql>CREATE OR REPLACE VIEW qryFrm_RestockLines_byItem AS
 +
SELECT
 +
  CONCAT(ri.ID_Item,'-',ri.ID_Restock) AS Key_RstkItem,
 +
  ri.ID_Item,
 +
  ri.ID_Restock,
 +
  i.Descr AS ItemDescr,
 +
  i.CatNum,
 +
  i.Supp_CatNum,
 +
  SUM(IFNULL(ri.QtyOrd,0)) AS QtyOrd,
 +
  SUM(IFNULL(ri.QtyExp,0)) AS QtyExp,
 +
  ri.CostExpPer,
 +
  i.PriceSell,
 +
  i.QtyMin_Stk
 +
FROM rstk_lines AS ri LEFT JOIN qryCbx_Items_data AS i ON ri.ID_Item=i.ID
 +
GROUP BY ri.ID_Restock, ri.ID_Item, i.CatNum, i.Supp_CatNum, ri.CostExpPer, i.PriceSell, i.QtyMin_Stk
 +
ORDER BY i.CatNum;</mysql>
 +
==qryRstkLines_en_route==
 +
===Details===
 +
* '''Deprecated''': this uses the old restock tables; looks like I edited it when I meant to be editing one of the newer ones, so it's not even accurate
 +
* '''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.
 +
* '''History''':
 +
** '''2008-12-04''' Dropped from database
 +
 +
===SQL===
 +
<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>
 +
==qryOrdLines_ord_status==
 +
===Details===
 +
* '''Status''': Deprecated -- qryRstkLines_en_route uses old restock table
 +
* '''Requires''': {{vbzcart/query|qryOrdLines_open}}, {{vbzcart/query|qryStk_lines_remaining_forSale}}, {{vbzcart/query|qryRstkLines_en_route}}
 +
* '''Used by''':
 +
===SQL===
 +
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryOrdLines_ord_status AS
 +
SELECT
 +
  i.ID AS ID_Item,
 +
  i.CatNum,
 +
  qoi.ID AS ID_OrdLine,
 +
  qoi.ID_Order,
 +
  qsr.ID AS ID_Stock,
 +
  qri.ID AS ID_RstkItem,
 +
  IFNULL(qoi.QtyOrd,0) AS QtyOrd,
 +
  IFNULL(qoi.QtyDone,0) AS QtyDone,
 +
  IFNULL(qri.QtyExp IS NULL,IFNULL(qri.QtyOrd,0)) AS QtyOnRstk,
 +
  IFNULL(qsr.QtyForShip,0) AS QtyOnHand,
 +
  i.QtyMin_Stk AS QtyMin,
 +
  qoi.WhenOldestOrder,
 +
  qoi.WhenSoonestDue
 +
FROM
 +
  (
 +
    (cat_items AS i LEFT JOIN qryOrdLines_open AS qoi ON qoi.ID_Item=i.ID)
 +
  LEFT JOIN qryStk_lines_remaining_forSale AS qsr ON qsr.ID_Item=i.ID)
 +
LEFT JOIN qryRstkLines_en_route AS qri ON qri.ID_Item=i.ID;</mysql>
 +
<section end=sql />
 +
==qryOrdLines_to_restock==
 +
===Details===
 +
* '''Status''': Obsolete -- depends on another obsolete query
 +
* '''Depends on''': [[#qryOrdLines_ord_status]]
 +
===SQL===
 +
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryOrdLines_to_restock AS
 +
SELECT
 +
  ID_Item,
 +
  i.ID_OrdLine,
 +
  i.ID_Order,
 +
  Sum(i.QtyOrd) AS QtyOrd,
 +
  Sum(i.QtyDone) AS QtyDone,
 +
  Sum(i.QtyOnRstk) AS QtyOnRstk,
 +
  Sum(i.QtyOnHand) AS QtyOnHand,
 +
  Max(i.QtyMin) AS QtyMin,
 +
  Max(IFNULL(i.QtyMin,0))+Sum(IFNULL(i.QtyOrd,0)-IFNULL(i.QtyOnRstk,0)-IFNULL(i.QtyOnHand,0)) AS QtyToGet,
 +
  Sum(IFNULL(i.QtyOrd,0)-IFNULL(i.QtyOnRstk,0)-IFNULL(i.QtyOnHand,0)) AS QtyShort,
 +
  Min(i.WhenOldestOrder) AS WhenOldestOrder,
 +
  Min(i.WhenSoonestDue) AS WhenSoonestDue,
 +
  Count(ID_RstkItem) AS RstkLines
 +
FROM qryOrdLines_ord_status AS i
 +
GROUP BY ID_Item, ID_OrdLine, ID_Order
 +
HAVING Max(IFNULL(i.QtyMin,0))+Sum(IFNULL(i.QtyOrd,0)-IFNULL(i.QtyOnRstk,0)-IFNULL(i.QtyOnHand,0))>0;</mysql>
 +
<section end=sql />
 +
==qryFrm_OrdLines_to_restock==
 +
===Details===
 +
* '''Status''': Obsolete -- depends on another obsolete query
 +
* '''Depends on''': [[#qryOrdLines_to_restock]]
 +
===SQL===
 +
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryFrm_OrdLines_to_restock AS
 +
SELECT
 +
  qi.*,
 +
  ci.CatNum,
 +
  ci.isForSale,
 +
  ci.ID_Supplier,
 +
  ci.PriceSell,
 +
  ci.PriceBuy
 +
FROM qryOrdLines_to_restock AS qi
 +
  LEFT JOIN qryCat_Items AS ci ON ci.ID=qi.ID_Item;</mysql>
 +
<section end=sql />
 +
==qryOrdLines_status_union==
 +
===Details===
 +
* '''Status''': Obsolete -- requires [[#qryRstkLines_en_route]], which is also obsolete
 +
* '''Requires''': {{vbzcart/query|qryOrdLines_open}}, [[#qryRstkLines_en_route]], {{vbzcart/query|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.
 +
* '''History''':
 +
** '''2008-11-02''' changed When field names (more accurate)
 +
===SQL===
 +
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryOrdLines_status_union AS
 +
SELECT
 +
/* 1 */  CONCAT("oio-",oio.ID) AS Source,
 +
/* 2 */  oio.ID AS ID,
 +
/* 3 */  oio.ID_Order,
 +
/* 4 */  oio.ID_Item,
 +
/* 5 */  oio.QtyOrd,
 +
/* 6 */  oio.QtyDone,
 +
/* 7 */  0 AS QtyOnRstk,
 +
/* 8 */  0 AS QtyOnHand,
 +
/* 9 */  0 AS QtyMin,
 +
/* 10 */ oio.WhenStarted,
 +
/* 11 */ oio.WhenNeeded
 +
    FROM qryOrdLines_open AS oio
 +
UNION
 +
SELECT
 +
/* 1 */ CONCAT("rie-",rie.ID) AS Source,
 +
/* 2 */ rie.ID AS ID,
 +
/* 3 */ rie.ID_Order,
 +
/* 4 */  rie.ID_Item,
 +
/* 5 */  0 AS QtyOrd,
 +
/* 6 */  0 AS QtyDone,
 +
/* 7 */  IF(rie.QtyExp > rie.QtyOrd,rie.QtyExp,rie.QtyOrd) AS QtyOnRstk,
 +
/* 8 */  0 AS QtyOnHand,
 +
/* 9 */  0 AS QtyMin,
 +
/* 10 */  NULL AS WhenStarted,
 +
/* 11 */  NULL AS WhenNeeded
 +
  FROM qryRstkLines_en_route AS rie
 +
UNION
 +
SELECT
 +
/* 1 */  CONCAT("srs-",srs.ID) AS Source,
 +
/* 2 */  0 AS ID,
 +
/* 3 */  NULL AS ID_Order,
 +
/* 4 */  srs.ID_Item,
 +
/* 5 */  0 AS QtyOrd,
 +
/* 6 */  0 AS QtyDone,
 +
/* 7 */  0 AS QtyOnRstk,
 +
/* 8 */  srs.QtyForShip AS QtyOnHand,
 +
/* 9 */  0 AS QtyMin,
 +
/* 10 */  NULL AS WhenStarted,
 +
/* 11 */  NULL AS WhenNeeded
 +
  FROM v_stk_lines_remaining AS srs
 +
UNION
 +
SELECT
 +
/* 1 */  CONCAT("itm-",itm.ID) AS Source,
 +
/* 2 */  NULL AS ID,
 +
/* 3 */  NULL AS ID_Order,
 +
/* 4 */  itm.ID AS ID_Item,
 +
/* 5 */  0 AS QtyOrd,
 +
/* 6 */  0 AS QtyDone,
 +
/* 7 */  0 AS QtyOnRsk,
 +
/* 8 */  0 AS QtyOnHand,
 +
/* 9 */  QtyMin_Stk AS QtyMin,
 +
/* 10 */  NULL AS WhenStarted,
 +
/* 11 */  NULL AS WhenNeeded
 +
  FROM cat_items AS itm;</mysql>
 +
<section end=sql />
 +
==qryItems_ord_status==
 +
===Details===
 +
* '''Status''': Obsolete -- requires [[#qryOrdLines_status_union]], which is also obsolete
 +
* '''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.
 +
* '''History''':
 +
** '''2008-11-02''' mods to accommodate rewritten source data
 +
===SQL===
 +
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItems_ord_status AS
 +
SELECT
 +
  i.ID_Item,
 +
  SUM(i.QtyOrd) AS QtyOrd,
 +
  SUM(i.QtyDone) AS QtyDone,
 +
  SUM(i.QtyOnRstk) AS QtyOnRstk,
 +
  SUM(i.QtyOnHand) AS QtyOnHand,
 +
  MAX(i.QtyMin) AS QtyMin,
 +
  SUM(
 +
    IFNULL(i.QtyOrd,0)-IFNULL(i.QtyOnRstk,0)-IFNULL(i.QtyOnHand,0)
 +
  )+IFNULL(MAX(i.QtyMin),0) AS QtyToGet,
 +
  MIN(i.WhenStarted) AS WhenOldestOrder,
 +
  MAX(i.WhenNeeded) AS WhenSoonestDue,
 +
  COUNT(i.Source) AS RecCount,
 +
  CAST(GROUP_CONCAT(DISTINCT i.Source SEPARATOR ' ') AS CHAR) AS Sources
 +
FROM qryOrdLines_status_union AS i
 +
GROUP BY i.ID_Item
 +
HAVING QtyToGet>0;</mysql>
 +
<section end=sql />
 +
==qryItems_to_restock==
 +
===Details===
 +
* '''Status''': Obsolete -- requires [[#qryItems_ord_status]], which is also obsolete
 +
* '''Depends on''': [[#qryItems_ord_status]]
 +
===SQL===
 +
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItems_to_restock AS
 +
SELECT
 +
  qi.*,
 +
  ci.CatNum,
 +
  ci.isForSale,
 +
  ci.ID_Supplier,
 +
  ci.QtyMin_Stk,
 +
  ci.QtyMin_Rstk_Title,
 +
  ci.PriceSell,
 +
  ci.PriceBuy
 +
FROM qryItems_ord_status AS qi LEFT JOIN qryCat_Items AS ci ON ci.ID=qi.ID_Item
 +
WHERE QtyToGet>0;</mysql>
 +
<section end=sql />

Latest revision as of 17:44, 22 December 2008

v_titles - latest

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

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

v_titles - older

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>

v_rstk_lines_wItemInfo

  • Status: deprecated (uses old restock tables); can probably be deleted, since nothing should be using those tables anymore.

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

qryFrm_RestockLines

Details

  • Status: deprecated (uses old restock table); can probably be deleted, since nothing should be using those tables anymore.
  • Notes: 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.

SQL

<mysql>CREATE OR REPLACE VIEW qryFrm_RestockLines AS SELECT 

 ri.ID,
 ri.ID_Restock,
 ri.ID_Item,
 ci.CatNum,
 ci.Supp_CatNum,
 ci.PriceSell,
 ci.QtyMin_Stk,
 o.Number AS OrderName,
 ci.Descr AS ItemDescr,
 o.WhenNeeded,
 If(t.RstkMin = 1, , t.RstkMin) AS RstkMinStr,
 ri.QtyOrd,
 ri.QtyNeed,
 ri.QtyExp,
 ri.InvcLineNo,
 ri.InvcQtyOrd,
 ri.InvcQtySent,
 ri.isGone,
 ri.QtyRecd,
 ri.QtyFiled,
 ri.CostExpPer,
 ri.CostInvPer,
 ri.CostInvTot,
 ri.CostActTot,
 ri.CostActBal,
 ri.Notes

FROM ( 

 (rstk_lines AS ri
   LEFT JOIN core_orders AS o ON ri.ID_Order=o.ID)
 LEFT JOIN qryCbx_Items_data AS ci ON ri.ID_Item=ci.ID)

LEFT JOIN cat_titles AS t ON ci.ID_Title=t.ID;</mysql>

qryFrm_RestockLines_byItem

Details

  • Notes: Basically the same as #qryFrm_RestockLines, but grouped by ID_Item and with more Item-related fields
  • Status: deprecated (uses old restock table); can probably be deleted, since nothing should be using those tables anymore.

SQL

<mysql>CREATE OR REPLACE VIEW qryFrm_RestockLines_byItem AS SELECT 

 CONCAT(ri.ID_Item,'-',ri.ID_Restock) AS Key_RstkItem,
 ri.ID_Item,
 ri.ID_Restock,
 i.Descr AS ItemDescr,
 i.CatNum,
 i.Supp_CatNum,
 SUM(IFNULL(ri.QtyOrd,0)) AS QtyOrd,
 SUM(IFNULL(ri.QtyExp,0)) AS QtyExp,
 ri.CostExpPer,
 i.PriceSell,
 i.QtyMin_Stk

FROM rstk_lines AS ri LEFT JOIN qryCbx_Items_data AS i ON ri.ID_Item=i.ID GROUP BY ri.ID_Restock, ri.ID_Item, i.CatNum, i.Supp_CatNum, ri.CostExpPer, i.PriceSell, i.QtyMin_Stk ORDER BY i.CatNum;</mysql>

qryRstkLines_en_route

Details

  • Deprecated: this uses the old restock tables; looks like I edited it when I meant to be editing one of the newer ones, so it's not even accurate
  • 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.
  • History:
    • 2008-12-04 Dropped from database

SQL

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

qryOrdLines_ord_status

Details

  • Status: Deprecated -- qryRstkLines_en_route uses old restock table
  • Requires:
  1. REDIRECT Template:l/vc/query, qryStk_lines_remaining_forSale, qryRstkLines_en_route
  • Used by:

SQL

<mysql>CREATE OR REPLACE VIEW qryOrdLines_ord_status AS SELECT 

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

FROM 

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

qryOrdLines_to_restock

Details

SQL

<mysql>CREATE OR REPLACE VIEW qryOrdLines_to_restock AS SELECT 

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

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

qryFrm_OrdLines_to_restock

Details

SQL

<mysql>CREATE OR REPLACE VIEW qryFrm_OrdLines_to_restock AS SELECT 

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

FROM qryOrdLines_to_restock AS qi 

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

qryOrdLines_status_union

Details

SQL

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

   FROM qryOrdLines_open AS oio

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

  FROM qryRstkLines_en_route AS rie

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

 FROM v_stk_lines_remaining AS srs

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

 FROM cat_items AS itm;</mysql>

qryItems_ord_status

Details

  • Status: Obsolete -- requires #qryOrdLines_status_union, which is also obsolete
  • 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.
  • History:
    • 2008-11-02 mods to accommodate rewritten source data

SQL

<mysql>CREATE OR REPLACE VIEW qryItems_ord_status AS SELECT 

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

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

qryItems_to_restock

Details

SQL

<mysql>CREATE OR REPLACE VIEW qryItems_to_restock AS SELECT 

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

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

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/queries/deprecated&oldid=11034"