VbzCart/queries/deprecated

The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

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:

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

Status: Obsolete -- depends on another obsolete query
Depends on: #qryOrdLines_ord_status

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

Status: Obsolete -- depends on another obsolete query
Depends on: #qryOrdLines_to_restock

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

Status: Obsolete -- requires #qryRstkLines_en_route, which is also obsolete
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.
History:
- 2008-11-02 changed When field names (more accurate)

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

Status: Obsolete -- requires #qryItems_ord_status, which is also obsolete
Depends on: #qryItems_ord_status

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>

VbzCart/queries/deprecated

Contents

v_titles - latest

v_titles - older

v_rstk_lines_wItemInfo

qryFrm_RestockLines

Details

SQL

qryFrm_RestockLines_byItem

Details

SQL

qryRstkLines_en_route

Details

SQL

qryOrdLines_ord_status

Details

SQL

qryOrdLines_to_restock

Details

SQL

qryFrm_OrdLines_to_restock

Details

SQL

qryOrdLines_status_union

Details

SQL

qryItems_ord_status

Details

SQL

qryItems_to_restock

Details

SQL

Navigation menu

Search