VbzCart/queries/qryOrderLines notPkgd
Jump to navigation
Jump to search
Details
- Status: Appears to be OBSOLETE; it's much quicker to generate the list in code (maybe there's a way to optimize the SQL, but code gives you more control over how the summing is done and more options for doing it)
- Returns: Active order lines which have yet to be (completely) put into a package
- Requires: ord_lines, ord_pkg_lines, core_orders
- Used by:
- REDIRECT Template:l/vc/query
- History:
- 2009-12-13 Tried adding JOIN to
- REDIRECT Template:l/vc/table to eliminate looking through pulled orders, as a time-optimization, but it didn't seem to make much difference; reverted back to old version. Discovered that doing the summing in code was vastly better, so this query is now OBSOLETE.
SQL
<mysql>CREATE OR REPLACE VIEW qryOrderLines_notPkgd AS SELECT
oi.ID, oi.ID_Order, oi.ID_Item, oi.CatNum, oi.Descr, oi.QtyOrd, oi.Price, oi.QtyOrd -SUM( IFNULL(pi.QtyShipped,0) +IFNULL(pi.QtyNotAvail,0) +IFNULL(pi.QtyKilled,0) ) AS QtyOpen, SUM(IFNULL(pi.QtyShipped,0)) AS QtyShipped, SUM(IFNULL(pi.QtyShipped,0) +IFNULL(pi.QtyNotAvail,0) +IFNULL(pi.QtyKilled,0)) AS QtyHandled
FROM
ord_lines AS oi LEFT JOIN ord_pkg_lines pi ON pi.ID_OrdLine=oi.ID
GROUP BY
oi.ID, oi.ID_Order, oi.ID_Item, oi.CatNum, oi.Descr, oi.QtyOrd, oi.Price
HAVING oi.QtyOrd-QtyHandled >0;</mysql>