VbzCart/docs/queries/qryStock containers: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (in progress) |
imported>Woozle (working) |
||
| Line 6: | Line 6: | ||
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_containers AS | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_containers AS | ||
SELECT | SELECT | ||
CONCAT("P.",p.ID) AS IDS, | |||
"P" AS Type, | |||
p.ID AS ID_forType, | |||
CONCAT(o.Number,"-",p.Seq) AS Name, | |||
s.WhenShipped AS WhenMade | |||
FROM (ord_pkgs AS p LEFT JOIN core_orders AS o ON p.ID_Order=o.ID) LEFT JOIN ord_shipmt AS s ON p.ID_Shipment=s.ID | |||
UNION | |||
SELECT | |||
CONCAT("L.",l.ID) AS IDS, | |||
"L" AS Type, | |||
l.ID AS ID_forType, | |||
l.Code AS Name, | |||
l.WhenCreated AS WhenMade | |||
FROM stk_bins AS l | |||
UNION | |||
SELECT | |||
CONCAT("R.",r.ID) AS IDS, | |||
"R" AS Type, | |||
r.ID AS ID_forType, | |||
CONCAT(rq.PurchOrdNum,"-",r.ID," (inv",SuppInvcNum,")") AS Name, | |||
IFNULL(WhenReceived,IFNULL(WhenDebited,WhenShipped)) AS WhenMade | |||
FROM rstk_rcd AS r LEFT JOIN rstk_req AS rq ON r.ID_Restock=rq.ID | |||
UNION | |||
SELECT | |||
CONCAT("M.",i.ID) AS IDS, | |||
"M" AS Type, | |||
i.ID AS ID_forType, | |||
i.CatNum AS Name, | |||
NULL AS WhenMade | |||
FROM cat_items AS i WHERE i.IsMaster;</mysql> | |||
<section end=sql /> | <section end=sql /> | ||
Revision as of 18:42, 27 December 2008
About
- Purpose: Stock codes used by Template:Vbzcart/table, and by anything that needs to look up what those codes refer to
- History:
- 2008-12-27 Adapting from stored procedure which didn't include packages or restocks because they hadn't been migrated yet
SQL
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_containers AS SELECT
CONCAT("P.",p.ID) AS IDS,
"P" AS Type,
p.ID AS ID_forType,
CONCAT(o.Number,"-",p.Seq) AS Name,
s.WhenShipped AS WhenMade
FROM (ord_pkgs AS p LEFT JOIN core_orders AS o ON p.ID_Order=o.ID) LEFT JOIN ord_shipmt AS s ON p.ID_Shipment=s.ID UNION SELECT
CONCAT("L.",l.ID) AS IDS,
"L" AS Type,
l.ID AS ID_forType,
l.Code AS Name,
l.WhenCreated AS WhenMade
FROM stk_bins AS l UNION SELECT
CONCAT("R.",r.ID) AS IDS,
"R" AS Type,
r.ID AS ID_forType,
CONCAT(rq.PurchOrdNum,"-",r.ID," (inv",SuppInvcNum,")") AS Name,
IFNULL(WhenReceived,IFNULL(WhenDebited,WhenShipped)) AS WhenMade
FROM rstk_rcd AS r LEFT JOIN rstk_req AS rq ON r.ID_Restock=rq.ID UNION SELECT
CONCAT("M.",i.ID) AS IDS,
"M" AS Type,
i.ID AS ID_forType,
i.CatNum AS Name,
NULL AS WhenMade
FROM cat_items AS i WHERE i.IsMaster;</mysql> <section end=sql />