VbzCart/queries/qryTitles ItTyps grpItems - Revision history

Woozle: corrected the used-by

2009-04-13T11:03:40Z

corrected the used-by

← Older revision		Revision as of 11:03, 13 April 2009
Line 2:		Line 2:
	* '''Purpose''': <s>This is a subquery for {{vbzcart/query\|qryTitles_ItTyps}}; all the summing across items within a Title-ItTyp pair is done here, then the results are joined back with Titles so we have all the information to display.</s>		* '''Purpose''': <s>This is a subquery for {{vbzcart/query\|qryTitles_ItTyps}}; all the summing across items within a Title-ItTyp pair is done here, then the results are joined back with Titles so we have all the information to display.</s>
	* '''Requires''': {{vbzcart/query\|qryCat_Items_Stock}}		* '''Requires''': {{vbzcart/query\|qryCat_Items_Stock}}
	* '''Used by''': {{vbzcart/query\|~~qryTitles_ItTyps~~}}		* '''Used by''': {{vbzcart/query\|qryTitles_ItTyps_Titles}}
	* '''Notes''':		* '''Notes''':
	** This is essentially the same query as {{vbzcart/query\|qryItTypsDepts_grpItems}} but grouped by ID_Title (and ID_ItTyp) instead of ID_Dept (and ID_ItTyp).		** This is essentially the same query as {{vbzcart/query\|qryItTypsDepts_grpItems}} but grouped by ID_Title (and ID_ItTyp) instead of ID_Dept (and ID_ItTyp).

Woozle: 4/13 update

2009-04-13T11:00:41Z

4/13 update

← Older revision		Revision as of 11:00, 13 April 2009
Line 1:		Line 1:
	==~~Details~~==		==About==
	This is a subquery for ~~[[..~~/~~qryTitles_ItTyps~~\|qryTitles_ItTyps]]; all the summing across items within a Title-ItTyp pair is done here, then the results are joined back with Titles so we have all the information to display.		* '''Purpose''': <s>This is a subquery for {{vbzcart/query\|qryTitles_ItTyps}}; all the summing across items within a Title-ItTyp pair is done here, then the results are joined back with Titles so we have all the information to display.</s>
			* '''Requires''': {{vbzcart/query\|qryCat_Items_Stock}}
			* '''Used by''': {{vbzcart/query\|qryTitles_ItTyps}}
	* '''Notes''':		* '''Notes''':
			** This is essentially the same query as {{vbzcart/query\|qryItTypsDepts_grpItems}} but grouped by ID_Title (and ID_ItTyp) instead of ID_Dept (and ID_ItTyp).
	** I originally had a '''HAVING cntInStock''' at the end of this, but that results in only stock items showing as available ''at all''. If this filtering is needed for something, it will have to be expressed in some other way.		** I originally had a '''HAVING cntInStock''' at the end of this, but that results in only stock items showing as available ''at all''. If this filtering is needed for something, it will have to be expressed in some other way.
	* '''History''':		* '''History''':
	** '''2008-04-04''' Grouping by GrpCode (and GrpDescr and GrpSort) is '''not''' necessary, and creates clutter in the title display if used. Certain items which have subtypes only distinguished by the group code (e.g. SD-NF-320 comes in both black and tie-dye) use these fields, but the page-generation code looks for different subtypes in the cat_items data and displays a new subtype header whenever GrpDescr changes. Maybe later on we'll include ID_CtgGroup in cat_items and pull GrpCode, GrpDescr, and GrpSort from the appropriate table(s?), but for now we store the values explicitly in cat_items and rely on proper sorting.		** '''2008-04-04''' Grouping by GrpCode (and GrpDescr and GrpSort) is '''not''' necessary, and creates clutter in the title display if used. Certain items which have subtypes only distinguished by the group code (e.g. SD-NF-320 comes in both black and tie-dye) use these fields, but the page-generation code looks for different subtypes in the cat_items data and displays a new subtype header whenever GrpDescr changes. Maybe later on we'll include ID_CtgGroup in cat_items and pull GrpCode, GrpDescr, and GrpSort from the appropriate table(s?), but for now we store the values explicitly in cat_items and rely on proper sorting.
	*** If subtypes are not being properly disambiguated within the title display, make sure the GrpDescr fields are being given a value (description). This is only necessary when the item type (ID_ItTyp) is the same.		*** If subtypes are not being properly disambiguated within the title display, make sure the GrpDescr fields are being given a value (description). This is only necessary when the item type (ID_ItTyp) is the same.
			** '''2008-04-13''' replaced cat_items with qryCat_Items_Stock because qtyForSale is no longer being cached in cat_items.qtyInStock
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryTitles_ItTyps_grpItems AS		<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryTitles_ItTyps_grpItems AS
Line 12:		Line 16:
	SUM(IF(isForSale,1,0)) AS cntForSale,		SUM(IF(isForSale,1,0)) AS cntForSale,
	SUM(IF(isInPrint,1,0)) AS cntInPrint,		SUM(IF(isInPrint,1,0)) AS cntInPrint,
	SUM(IF(~~qtyInStock~~>0,1,0)) AS ~~cntInStock~~,		SUM(IF(qtyForSale>0,1,0)) AS cntStkForSale,
	SUM(~~qtyInStock~~) AS ~~qtyInStock~~,		SUM(qtyForSale) AS qtyForSale,
	MIN(PriceSell) AS currMinPrice,		MIN(PriceSell) AS currMinPrice,
	MAX(PriceSell) AS currMaxPrice		MAX(PriceSell) AS currMaxPrice
	FROM ~~cat_items~~ AS i		FROM qryCat_Items_Stock AS i
	WHERE isForSale		WHERE isForSale
	GROUP BY ID_Title~~, ID_ItTyp~~;</mysql>		GROUP BY ID_ItTyp, ID_Title;</mysql>
	<section end=sql />		<section end=sql />

Woozle: moved from single-page listing

2008-11-30T18:34:28Z

moved from single-page listing

New page

==Details==
This is a subquery for [[../qryTitles_ItTyps|qryTitles_ItTyps]]; all the summing across items within a Title-ItTyp pair is done here, then the results are joined back with Titles so we have all the information to display.
* '''Notes''':
** I originally had a '''HAVING cntInStock''' at the end of this, but that results in only stock items showing as available ''at all''. If this filtering is needed for something, it will have to be expressed in some other way.
* '''History''':
** '''2008-04-04''' Grouping by GrpCode (and GrpDescr and GrpSort) is '''not''' necessary, and creates clutter in the title display if used. Certain items which have subtypes only distinguished by the group code (e.g. SD-NF-320 comes in both black and tie-dye) use these fields, but the page-generation code looks for different subtypes in the cat_items data and displays a new subtype header whenever GrpDescr changes. Maybe later on we'll include ID_CtgGroup in cat_items and pull GrpCode, GrpDescr, and GrpSort from the appropriate table(s?), but for now we store the values explicitly in cat_items and rely on proper sorting.
*** If subtypes are not being properly disambiguated within the title display, make sure the GrpDescr fields are being given a value (description). This is only necessary when the item type (ID_ItTyp) is the same.
==SQL==
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryTitles_ItTyps_grpItems AS
SELECT
ID_Title, ID_ItTyp,
SUM(IF(isForSale,1,0)) AS cntForSale,
SUM(IF(isInPrint,1,0)) AS cntInPrint,
SUM(IF(qtyInStock>0,1,0)) AS cntInStock,
SUM(qtyInStock) AS qtyInStock,
MIN(PriceSell) AS currMinPrice,
MAX(PriceSell) AS currMaxPrice
FROM cat_items AS i
WHERE isForSale
GROUP BY ID_Title, ID_ItTyp;</mysql>
<section end=sql />

← Older revision		Revision as of 11:03, 13 April 2009
Line 2:		Line 2:
	* '''Purpose''': <s>This is a subquery for {{vbzcart/query\|qryTitles_ItTyps}}; all the summing across items within a Title-ItTyp pair is done here, then the results are joined back with Titles so we have all the information to display.</s>		* '''Purpose''': <s>This is a subquery for {{vbzcart/query\|qryTitles_ItTyps}}; all the summing across items within a Title-ItTyp pair is done here, then the results are joined back with Titles so we have all the information to display.</s>
	* '''Requires''': {{vbzcart/query\|qryCat_Items_Stock}}		* '''Requires''': {{vbzcart/query\|qryCat_Items_Stock}}
	* '''Used by''': {{vbzcart/query\|~~qryTitles_ItTyps~~}}		* '''Used by''': {{vbzcart/query\|qryTitles_ItTyps_Titles}}
	* '''Notes''':		* '''Notes''':
	** This is essentially the same query as {{vbzcart/query\|qryItTypsDepts_grpItems}} but grouped by ID_Title (and ID_ItTyp) instead of ID_Dept (and ID_ItTyp).		** This is essentially the same query as {{vbzcart/query\|qryItTypsDepts_grpItems}} but grouped by ID_Title (and ID_ItTyp) instead of ID_Dept (and ID_ItTyp).