VbzCart/docs/queries/qryOrderLines notPkgd: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (New page: ==Details== * '''Returns''': Active order lines which have yet to be (completely) put into a package * '''Requires''': {{vbzcart/table|ord_lines}}, {{vbzcart/table|ord_pkg_lines}} * '''Use...) |
imported>Woozle (this is now obsolete) |
||
| Line 1: | Line 1: | ||
==Details== | ==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 | * '''Returns''': Active order lines which have yet to be (completely) put into a package | ||
* '''Requires''': {{vbzcart | * '''Requires''': {{vbzcart|table|ord_lines}}, {{vbzcart|table|ord_pkg_lines}}, {{vbzcart|table|core_orders}} | ||
* '''Used by''': | * '''Used by''': {{vbzcart/query|qryStock_forOpenOrders}} | ||
* '''History''': | |||
** '''2009-12-13''' Tried adding JOIN to {{vbzcart/table|core_orders}} 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== | ==SQL== | ||
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryOrderLines_notPkgd AS | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryOrderLines_notPkgd AS | ||
Revision as of 12:24, 14 December 2009
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: Template:Vbzcart, Template:Vbzcart, Template:Vbzcart
- Used by: Template:Vbzcart/query
- History:
- 2009-12-13 Tried adding JOIN to Template:Vbzcart/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
<section begin=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> <section end=sql />