VbzCart/docs/queries/deprecated: Difference between revisions
imported>Woozle (v_rstk_lines_wItemInfo) |
imported>Woozle (qryCbx queries based on (obsolete) core_restocks) |
||
Line 40: | Line 40: | ||
LEFT JOIN cat_items AS i ON ri.ID_Item=i.ID) | 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> | 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''': {{vbzcart/table|core_restocks}}, {{vbzcart/table|cat_supp}} | |||
* '''Used by''': {{vbzcart/query|qryCbx_Restocks}} | |||
===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''': {{vbzcart/table|core_restocks}} | |||
* '''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> |
Revision as of 00:05, 4 December 2008
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>