VbzCart/queries/deprecated

v_titles - latest
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;

v_titles - older
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;

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

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

Details

 * Notes: Basically the same as, 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
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;

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

Details

 * Status: Deprecated -- qryRstkLines_en_route uses old restock table
 * Requires:, ,
 * Used by:

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

Details

 * Status: Obsolete -- depends on another obsolete query
 * Depends on:

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

Details

 * Status: Obsolete -- depends on another obsolete query
 * Depends on:

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

Details

 * Status: Obsolete -- requires, which is also obsolete
 * Requires:, ,
 * 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
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;

Details

 * Status: Obsolete -- requires, which is also obsolete
 * Depends on:
 * Returns: Status with regard to ordering, i.e. quantities on hand, quantities needed, etc. Used by the "items needed (for restocking)" form, via . 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
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;

Details

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

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