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>