Difference between revisions of "VbzCart/queries/deprecated"
(v_rstk_lines_wItemInfo) |
(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/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>
qryCbx_Restocks_byStatus
Details
- Status: deprecated (uses old restock tables); can probably be deleted, since nothing should be using those tables anymore.
- Requires:
- REDIRECT Template:l/vc/table, cat_supp
- Used by:
- REDIRECT Template:l/vc/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: 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>