Woozle: this is now obsolete

2009-12-14T12:24:22Z

this is now obsolete

← Older revision		Revision as of 12:24, 14 December 2009
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/table\|ord_lines}}, {{vbzcart/table\|ord_pkg_lines}}		* '''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

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

2008-12-03T21:07:04Z

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

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}}
* '''Used by''':
==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 />

VbzCart/queries/qryOrderLines notPkgd - Revision history

Woozle: this is now obsolete

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