VbzCart/queries/qryOrderLines notPkgd

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:

1. REDIRECT Template:l/vc/query

• History:
  • 2009-12-13 Tried adding JOIN to

1. 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>