VbzCart/procs/Upd TitleIttyps fr CatItems Titles - Revision history

Woozle: better stock numbers

2011-01-24T01:53:54Z

better stock numbers

← Older revision		Revision as of 01:53, 24 January 2011
Line 1:		Line 1:
	==About==		==About==
	* '''Purpose''': fills {{vbzcart\|table\|_title_ittyps}}.		* '''Purpose''': fills {{vbzcart\|table\|_title_ittyps}}.
	* '''Input''': {{vbzcart\|table\|cat_items}}, {{vbzcart\|table\|cat_titles}}		* '''Input''': {{vbzcart\|table\|cat_items}}, {{vbzcart\|table\|cat_titles}}, {{vbzcart/query\|qryStk_items_remaining}}
	* '''Output''': {{vbzcart\|table\|_title_ittyps}} (replace)		* '''Output''': {{vbzcart\|table\|_title_ittyps}} (replace)
	* '''History''':		* '''History''':
Line 13:		Line 13:
	*** Added '''cntInPrint''', '''cntLines'''		*** Added '''cntInPrint''', '''cntLines'''
	*** Added (ID_Dept IS NOT NULL) AND (ID_ItTyp IS NOT NULL) to prevent those fields being null (not allowed in target table)		*** Added (ID_Dept IS NOT NULL) AND (ID_ItTyp IS NOT NULL) to prevent those fields being null (not allowed in target table)
			** '''2011-01-23''' fetching Stock data from {{vbzcart/query\|qryStk_items_remaining}} instead of relying on {{vbzcart\|table\|cat_items}} to be current
	* '''Notes''':		* '''Notes''':
	** This is a revised version which combines two earlier procedures.		** This is a revised version which combines two earlier procedures.
Line 52:		Line 53:
	SUM(IF(isInPrint,1,0)) AS cntInPrint,		SUM(IF(isInPrint,1,0)) AS cntInPrint,
	SUM(IF(isForSale,1,0)) AS cntForSale,		SUM(IF(isForSale,1,0)) AS cntForSale,
	SUM(IF(~~qtyIn_Stk~~>0,1,0)) AS cntInStock,		SUM(IF(iq.qtyForSale>0,1,0)) AS cntInStock,
	SUM(~~qtyIn_Stk~~) AS qtyInStock,		SUM(iq.qtyForSale) AS qtyInStock,
	MIN(PriceSell) AS currMinPrice,		MIN(PriceSell) AS currMinPrice,
	MAX(PriceSell) AS currMaxPrice		MAX(PriceSell) AS currMaxPrice
	FROM cat_items AS i		FROM cat_items AS i LEFT JOIN qryStk_items_remaining AS iq ON i.ID=iq.ID_Item
	GROUP BY ID_Title, ID_ItTyp		GROUP BY ID_Title, ID_ItTyp
	) AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID		) AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID
	WHERE (ID_Dept IS NOT NULL) AND (ID_ItTyp IS NOT NULL);</mysql>		WHERE (ID_Dept IS NOT NULL) AND (ID_ItTyp IS NOT NULL);</mysql>
	<section end=sql />		<section end=sql />

Woozle: conditions to prevent records with null ID_Dept and ID_ItTyp

2010-11-14T00:53:06Z

conditions to prevent records with null ID_Dept and ID_ItTyp

← Older revision		Revision as of 00:53, 14 November 2010
Line 12:		Line 12:
	** '''2010-11-13''' Source field '''qtyInStock''' was renamed to '''qtyIn_Stk''' some time ago...		** '''2010-11-13''' Source field '''qtyInStock''' was renamed to '''qtyIn_Stk''' some time ago...
	*** Added '''cntInPrint''', '''cntLines'''		*** Added '''cntInPrint''', '''cntLines'''
			*** Added (ID_Dept IS NOT NULL) AND (ID_ItTyp IS NOT NULL) to prevent those fields being null (not allowed in target table)
	* '''Notes''':		* '''Notes''':
	** This is a revised version which combines two earlier procedures.		** This is a revised version which combines two earlier procedures.
Line 57:		Line 58:
	FROM cat_items AS i		FROM cat_items AS i
	GROUP BY ID_Title, ID_ItTyp		GROUP BY ID_Title, ID_ItTyp
	) AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID;</mysql>		) AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID
			WHERE (ID_Dept IS NOT NULL) AND (ID_ItTyp IS NOT NULL);</mysql>
	<section end=sql />		<section end=sql />

Woozle: fixed missing target field

2010-11-14T00:17:37Z

fixed missing target field

← Older revision		Revision as of 00:17, 14 November 2010
Line 27:		Line 27:
	cntInPrint,		cntInPrint,
	cntForSale,		cntForSale,
			cntInStock,
	qtyInStock,		qtyInStock,
	currMinPrice,		currMinPrice,

Woozle: slight re-ordering

2010-11-13T23:59:07Z

slight re-ordering

← Older revision		Revision as of 23:59, 13 November 2010
Line 24:		Line 24:
	TitleName,		TitleName,
	cntLines,		cntLines,
			cntCurrent,
	cntInPrint,		cntInPrint,
	cntForSale,		cntForSale,
	~~cntCurrent,~~
	qtyInStock,		qtyInStock,
	currMinPrice,		currMinPrice,
Line 36:		Line 36:
	t.Name AS TitleName,		t.Name AS TitleName,
	i.cntLines,		i.cntLines,
			i.cntCurrent,
	i.cntInPrint,		i.cntInPrint,
	i.cntForSale,		i.cntForSale,
	~~i.cntCurrent,~~
	i.cntInStock,		i.cntInStock,
	i.qtyInStock,		i.qtyInStock,
Line 47:		Line 47:
	ID_Title, ID_ItTyp,		ID_Title, ID_ItTyp,
	COUNT(ID) AS cntLines,		COUNT(ID) AS cntLines,
			SUM(IF(isCurrent,1,0)) AS cntCurrent,
	SUM(IF(isInPrint,1,0)) AS cntInPrint,		SUM(IF(isInPrint,1,0)) AS cntInPrint,
	SUM(IF(isForSale,1,0)) AS cntForSale,		SUM(IF(isForSale,1,0)) AS cntForSale,
	~~SUM(IF(isCurrent,1,0)) AS cntCurrent,~~
	SUM(IF(qtyIn_Stk>0,1,0)) AS cntInStock,		SUM(IF(qtyIn_Stk>0,1,0)) AS cntInStock,
	SUM(qtyIn_Stk) AS qtyInStock,		SUM(qtyIn_Stk) AS qtyInStock,

Woozle: 2 new fields

2010-11-13T23:56:11Z

2 new fields

← Older revision		Revision as of 23:56, 13 November 2010
Line 10:		Line 10:
	*** Added DROP PROCEDURE for easier maintenance.		*** Added DROP PROCEDURE for easier maintenance.
	*** Actually created in database (procs did not get ported from L48 to Rizzo).		*** Actually created in database (procs did not get ported from L48 to Rizzo).
			** '''2010-11-13''' Source field '''qtyInStock''' was renamed to '''qtyIn_Stk''' some time ago...
			*** Added '''cntInPrint''', '''cntLines'''
	* '''Notes''':		* '''Notes''':
	** This is a revised version which combines two earlier procedures.		** This is a revised version which combines two earlier procedures.
Line 21:		Line 23:
	ID_Dept,		ID_Dept,
	TitleName,		TitleName,
			cntLines,
			cntInPrint,
	cntForSale,		cntForSale,
	~~cntInPrint,~~		cntCurrent,
	~~cntInStock~~,
	qtyInStock,		qtyInStock,
	currMinPrice,		currMinPrice,
Line 32:		Line 35:
	t.ID_Dept,		t.ID_Dept,
	t.Name AS TitleName,		t.Name AS TitleName,
			i.cntLines,
			i.cntInPrint,
	i.cntForSale,		i.cntForSale,
	i.~~cntInPrint~~,		i.cntCurrent,
	i.cntInStock,		i.cntInStock,
	i.qtyInStock,		i.qtyInStock,
Line 41:		Line 46:
	SELECT		SELECT
	ID_Title, ID_ItTyp,		ID_Title, ID_ItTyp,
			COUNT(ID) AS cntLines,
			SUM(IF(isInPrint,1,0)) AS cntInPrint,
	SUM(IF(isForSale,1,0)) AS cntForSale,		SUM(IF(isForSale,1,0)) AS cntForSale,
	SUM(IF(~~isInPrint~~,1,0)) AS ~~cntInPrint~~,		SUM(IF(isCurrent,1,0)) AS cntCurrent,
	SUM(IF(~~qtyInStock~~>0,1,0)) AS cntInStock,		SUM(IF(qtyIn_Stk>0,1,0)) AS cntInStock,
	SUM(~~qtyInStock~~) AS qtyInStock,		SUM(qtyIn_Stk) AS qtyInStock,
	MIN(PriceSell) AS currMinPrice,		MIN(PriceSell) AS currMinPrice,
	MAX(PriceSell) AS currMaxPrice		MAX(PriceSell) AS currMaxPrice

Woozle: created in db

2010-11-10T12:43:03Z

created in db

← Older revision		Revision as of 12:43, 10 November 2010
Line 7:		Line 7:
	*** Removed "WHERE i.isForSale" because this prevented the availability of discontinued items from being properly zeroed out		*** Removed "WHERE i.isForSale" because this prevented the availability of discontinued items from being properly zeroed out
	*** Added cntInStock calculation; see table schema		*** Added cntInStock calculation; see table schema
			** '''2010-11-10'''
			*** Added DROP PROCEDURE for easier maintenance.
			*** Actually created in database (procs did not get ported from L48 to Rizzo).
	* '''Notes''':		* '''Notes''':
	** This is a revised version which combines two earlier procedures.		** This is a revised version which combines two earlier procedures.
	** I had a note that "the ID_Dept field is informational, not a grouping", but I don't remember what that meant and it doesn't make sense.		** I had a note that "the ID_Dept field is informational, not a grouping", but I don't remember what that meant and it doesn't make sense.
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE PROCEDURE Upd_TitleIttyps_fr_CatItems_Titles()		<section begin=sql /><mysql>DROP PROCEDURE IF EXISTS Upd_TitleIttyps_fr_CatItems_Titles;
			CREATE PROCEDURE Upd_TitleIttyps_fr_CatItems_Titles()
	REPLACE INTO _title_ittyps(		REPLACE INTO _title_ittyps(
	ID_Title,		ID_Title,

Woozle: New page: ==About== * '''Purpose''': fills {{vbzcart|table|_title_ittyps}}. * '''Input''': {{vbzcart|table|cat_items}}, {{vbzcart|table|cat_titles}} * '''Output''': {{vbzcart|table|_title_ittyps}} (...

2009-03-08T19:57:51Z

New page: ==About== * '''Purpose''': fills {{vbzcart|table|_title_ittyps}}. * '''Input''': {{vbzcart|table|cat_items}}, {{vbzcart|table|cat_titles}} * '''Output''': {{vbzcart|table|_title_ittyps}} (...

New page

==About==
* '''Purpose''': fills {{vbzcart|table|_title_ittyps}}.
* '''Input''': {{vbzcart|table|cat_items}}, {{vbzcart|table|cat_titles}}
* '''Output''': {{vbzcart|table|_title_ittyps}} (replace)
* '''History''':
** '''2007-09-20'''
*** Removed "WHERE i.isForSale" because this prevented the availability of discontinued items from being properly zeroed out
*** Added cntInStock calculation; see table schema
* '''Notes''':
** This is a revised version which combines two earlier procedures.
** I had a note that "the ID_Dept field is informational, not a grouping", but I don't remember what that meant and it doesn't make sense.
==SQL==
<section begin=sql /><mysql>CREATE PROCEDURE Upd_TitleIttyps_fr_CatItems_Titles()
REPLACE INTO _title_ittyps(
ID_Title,
ID_ItTyp,
ID_Dept,
TitleName,
cntForSale,
cntInPrint,
cntInStock,
qtyInStock,
currMinPrice,
currMaxPrice)
SELECT
i.ID_Title,
i.ID_ItTyp,
t.ID_Dept,
t.Name AS TitleName,
i.cntForSale,
i.cntInPrint,
i.cntInStock,
i.qtyInStock,
i.currMinPrice,
i.currMaxPrice
FROM (
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
GROUP BY ID_Title, ID_ItTyp
) AS i LEFT JOIN cat_titles AS t ON i.ID_Title=t.ID;</mysql>
<section end=sql />