VbzCart/queries/qryPkgLines qtys done - Revision history

Woozle: added p.Seq

2011-06-01T15:46:28Z

added p.Seq

← Older revision		Revision as of 15:46, 1 June 2011
Line 5:		Line 5:
	* '''History''':		* '''History''':
	** '''2011-05-31''' created for packing slips		** '''2011-05-31''' created for packing slips
			** '''2011-06-01''' added p.Seq so code can filter for packages ''before'' the current one -- so that the results will be the same (at least in theory) even after other packages have been created
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryPkgLines_qtys_done AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryPkgLines_qtys_done AS
	SELECT		SELECT
			p.Seq,
	p.ID_Order,		p.ID_Order,
	pl.ID_OrdLine,		pl.ID_OrdLine,

Woozle: fixed math for QtyDone; taking out grouping to speed things up

2011-06-01T01:58:22Z

fixed math for QtyDone; taking out grouping to speed things up

← Older revision		Revision as of 01:58, 1 June 2011
Line 1:		Line 1:
	==About==		==About==
	* '''Requires''': {{vbzcart\|table\|ord_pkgs}}		* '''Requires''': {{vbzcart\|table\|ord_pkgs}}
	* '''Returns''': ~~sum of~~ "done" ~~quantities~~ for all packages which have been checked in each order		* '''Returns''': "done" quantity for every line of all packages which have been checked in each order
	** In ~~other words, you get one record~~ for each item in an order~~; that record gives the sum of quantities "done"~~ for that ~~item across all "checked" packages. This lets you know how many of each item remain open~~ (~~to be filled~~).		** In order to return a current quantity status for each item in an order, this query should be filtered for that order ID (ID_Order) and then summed in code; summing in SQL and then filtering doesn't speed things up. (There's probably a way to do this in MySQL using advanced syntax I haven't dug into yet.)
	** There ~~is also PHP code somewhere which does~~ this~~, but~~ I ~~needed to be able to pull these numbers up in w3tpl~~.
	* '''History''':		* '''History''':
	** '''2011-05-31''' created for packing slips		** '''2011-05-31''' created for packing slips
Line 12:		Line 11:
	pl.ID_OrdLine,		pl.ID_OrdLine,
	pl.ID_Item,		pl.ID_Item,
	~~SUM~~(pl.QtyShipped + pl.QtyKilled + pl.QtyNotAvail) AS QtyDone		(IFNULL(pl.QtyShipped,0) + IFNULL(pl.QtyKilled,0) + IFNULL(pl.QtyNotAvail,0)) AS QtyDone
	FROM		FROM
	ord_pkgs AS p LEFT JOIN		ord_pkgs AS p LEFT JOIN
	ord_pkg_lines as pl ON pl.ID_Pkg=p.ID		ord_pkg_lines as pl ON pl.ID_Pkg=p.ID
	WHERE (WhenChecked IS NOT NULL)		WHERE (WhenChecked IS NOT NULL);</mysql>
	~~GROUP BY ID_Order, ID_OrdLine, ID_Item~~;</mysql>
	<section end=sql />		<section end=sql />

Woozle: Created page with "==About== * '''Requires''': {{vbzcart|table|ord_pkgs}} * '''Returns''': sum of "done" quantities for all packages which have been checked in each order ** In other words, you get..."

2011-06-01T01:31:46Z

Created page with "==About== * '''Requires''': {{vbzcart|table|ord_pkgs}} * '''Returns''': sum of "done" quantities for all packages which have been checked in each order ** In other words, you get..."

New page

==About==
* '''Requires''': {{vbzcart|table|ord_pkgs}}
* '''Returns''': sum of "done" quantities for all packages which have been checked in each order
** In other words, you get one record for each item in an order; that record gives the sum of quantities "done" for that item across all "checked" packages. This lets you know how many of each item remain open (to be filled).
** There is also PHP code somewhere which does this, but I needed to be able to pull these numbers up in w3tpl.
* '''History''':
** '''2011-05-31''' created for packing slips
==SQL==
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryPkgLines_qtys_done AS
SELECT
p.ID_Order,
pl.ID_OrdLine,
pl.ID_Item,
SUM(pl.QtyShipped + pl.QtyKilled + pl.QtyNotAvail) AS QtyDone
FROM
ord_pkgs AS p LEFT JOIN
ord_pkg_lines as pl ON pl.ID_Pkg=p.ID
WHERE (WhenChecked IS NOT NULL)
GROUP BY ID_Order, ID_OrdLine, ID_Item;</mysql>
<section end=sql />