Difference between revisions of "VbzCart/queries/qryRpt Pkg Lines"
Jump to navigation
Jump to search
(returns data now; accuracy not tested) |
(calculated field) |
||
| (One intermediate revision by the same user not shown) | |||
| Line 3: | Line 3: | ||
* '''History''': | * '''History''': | ||
** '''2011-05-31''' ported from MS Access so I can print packing slips from web interface (final major piece?) | ** '''2011-05-31''' ported from MS Access so I can print packing slips from web interface (final major piece?) | ||
| + | ** '''2011-06-01''' added p.ID_Item so we can pull in the results from {{vbzcart/query|qryPkgLines_qtys_done}}; added PriceShipped so I don't have to implement expressions in w3tpl yet | ||
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRpt_Pkg_Lines AS | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRpt_Pkg_Lines AS | ||
| Line 17: | Line 18: | ||
p.QtyKilled, | p.QtyKilled, | ||
o.Price, | o.Price, | ||
| + | o.Price * p.QtyShipped AS PriceShipped, | ||
o.ShipItm, | o.ShipItm, | ||
o.ShipPkg, | o.ShipPkg, | ||
| + | p.ID_Item, | ||
i.ID_Title | i.ID_Title | ||
FROM ( | FROM ( | ||
Latest revision as of 18:43, 1 June 2011
About
- Requires: ord_pkg_lines, ord_lines,
- REDIRECT Template:l/vc/query
- History:
- 2011-05-31 ported from MS Access so I can print packing slips from web interface (final major piece?)
- 2011-06-01 added p.ID_Item so we can pull in the results from qryPkgLines_qtys_done; added PriceShipped so I don't have to implement expressions in w3tpl yet
SQL
<mysql>CREATE OR REPLACE VIEW qryRpt_Pkg_Lines AS SELECT
p.ID AS ID, p.ID_Pkg, o.Seq, IFNULL(o.CatNum,i.CatNum) AS CatNum, IFNULL(o.Descr,i.Descr) AS Descr, o.QtyOrd, p.QtyShipped, p.QtyExtra, p.QtyNotAvail, p.QtyKilled, o.Price, o.Price * p.QtyShipped AS PriceShipped, o.ShipItm, o.ShipPkg, p.ID_Item, i.ID_Title
FROM (
ord_pkg_lines AS p LEFT JOIN ord_lines AS o ON p.ID_OrdLine=o.ID) LEFT JOIN qryCat_Items AS i ON p.ID_Item=i.ID;</mysql>