VbzCart/queries/qryRpt Pkg Lines - Revision history

Woozle: calculated field

2011-06-01T18:43:37Z

calculated field

← Older revision		Revision as of 18:43, 1 June 2011
Line 3:		Line 3:
	* '''History''':		* '''History''':
	** '''2011-05-31''' ported from MS Access so I can print packing slips from web interface (final major piece?)		** '''2011-05-31''' ported from MS Access so I can print packing slips from web interface (final major piece?)
	** '''2011-06-01''' added p.ID_Item so we can pull in the results from {{vbzcart/query\|qryPkgLines_qtys_done}}.		** '''2011-06-01''' added p.ID_Item so we can pull in the results from {{vbzcart/query\|qryPkgLines_qtys_done}}; added PriceShipped so I don't have to implement expressions in w3tpl yet
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRpt_Pkg_Lines AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRpt_Pkg_Lines AS
Line 18:		Line 18:
	p.QtyKilled,		p.QtyKilled,
	o.Price,		o.Price,
			o.Price * p.QtyShipped AS PriceShipped,
	o.ShipItm,		o.ShipItm,
	o.ShipPkg,		o.ShipPkg,

Woozle: adding p.Item

2011-06-01T16:54:36Z

adding p.Item

← Older revision		Revision as of 16:54, 1 June 2011
Line 3:		Line 3:
	* '''History''':		* '''History''':
	** '''2011-05-31''' ported from MS Access so I can print packing slips from web interface (final major piece?)		** '''2011-05-31''' ported from MS Access so I can print packing slips from web interface (final major piece?)
			** '''2011-06-01''' added p.ID_Item so we can pull in the results from {{vbzcart/query\|qryPkgLines_qtys_done}}.
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRpt_Pkg_Lines AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRpt_Pkg_Lines AS
Line 19:		Line 20:
	o.ShipItm,		o.ShipItm,
	o.ShipPkg,		o.ShipPkg,
			p.ID_Item,
	i.ID_Title		i.ID_Title
	FROM (		FROM (

Woozle: returns data now; accuracy not tested

2011-05-31T16:43:15Z

returns data now; accuracy not tested

← Older revision		Revision as of 16:43, 31 May 2011
Line 9:		Line 9:
	p.ID_Pkg,		p.ID_Pkg,
	o.Seq,		o.Seq,
	~~iif~~(o.CatNum ~~IS NULL~~,i~~.CatNum,o~~.CatNum) AS CatNum,		IFNULL(o.CatNum,i.CatNum) AS CatNum,
	~~iif~~(o.Descr ~~IS NULL~~,i~~.Descr,o~~.Descr) AS Descr,		IFNULL(o.Descr,i.Descr) AS Descr,
	o.QtyOrd,		o.QtyOrd,
	p.QtyShipped,		p.QtyShipped,
Line 25:		Line 25:
	qryCat_Items AS i ON p.ID_Item=i.ID;</mysql>		qryCat_Items AS i ON p.ID_Item=i.ID;</mysql>
	<section end=sql />		<section end=sql />


	~~This has not yet been modified to work with MySQL.~~

Woozle: copied, not tested

2011-05-31T16:33:55Z

copied, not tested

New page

==About==
* '''Requires''': {{vbzcart|table|ord_pkg_lines}}, {{vbzcart|table|ord_lines}}, {{vbzcart/query|qryCat_Items}}
* '''History''':
** '''2011-05-31''' ported from MS Access so I can print packing slips from web interface (final major piece?)
==SQL==
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryRpt_Pkg_Lines AS
SELECT
p.ID AS ID,
p.ID_Pkg,
o.Seq,
iif(o.CatNum IS NULL,i.CatNum,o.CatNum) AS CatNum,
iif(o.Descr IS NULL,i.Descr,o.Descr) AS Descr,
o.QtyOrd,
p.QtyShipped,
p.QtyExtra,
p.QtyNotAvail,
p.QtyKilled,
o.Price,
o.ShipItm,
o.ShipPkg,
i.ID_Title
FROM (
ord_pkg_lines AS p LEFT JOIN
ord_lines AS o ON p.ID_OrdLine=o.ID) LEFT JOIN
qryCat_Items AS i ON p.ID_Item=i.ID;</mysql>
<section end=sql />

This has not yet been modified to work with MySQL.