VbzCart/docs/queries/deprecated
v_titles - latest
Deprecated -- use VbzCart/docs/queries/qryCat_Titles_Item_stats instead -- this query still depends on cached _depts table. To be eliminated later. <section begin=sql /><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> <section end=sql />
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>
qryCbx_Restocks_byStatus
Details
- Status: deprecated (uses old restock tables); can probably be deleted, since nothing should be using those tables anymore.
- Requires: Template:Vbzcart/table, Template:Vbzcart/table
- Used by: Template:Vbzcart/query
SQL
<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;</mysql>
qryCbx_Restocks_byPurchOrd
Details
- Status: deprecated (uses old restock table); can probably be deleted, since nothing should be using those tables anymore.
- Requires: Template:Vbzcart/table
- Used by: #qryCbx_Restocks
SQL
<mysql>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;</mysql>
qryCbx_Restocks
Details
- Status: deprecated (ultimately depends on old restock table); can probably be deleted, since nothing should be using those tables anymore.
- Notes: MySQL gives a "query cancelled" error when running this SQL to create the view, but actually it creates it just fine.
SQL
<mysql>CREATE OR REPLACE VIEW qryCbx_Restocks AS
SELECT * FROM qryCbx_Restocks_byStatus
UNION
SELECT * FROM qryCbx_Restocks_byPurchOrd;</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.
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>