Difference between revisions of "VbzCart/procs/Upd TitleIttyps fr CatItems Titles"
Jump to navigation
Jump to search
(New page: ==About== * '''Purpose''': fills {{vbzcart|table|_title_ittyps}}. * '''Input''': {{vbzcart|table|cat_items}}, {{vbzcart|table|cat_titles}} * '''Output''': {{vbzcart|table|_title_ittyps}} (...) |
(better stock numbers) |
||
(5 intermediate revisions by the same user not shown) | |||
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 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). | ||
+ | ** '''2010-11-13''' Source field '''qtyInStock''' was renamed to '''qtyIn_Stk''' some time ago... | ||
+ | *** 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) | ||
+ | ** '''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. | ||
** 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, | ||
Line 17: | Line 25: | ||
ID_Dept, | ID_Dept, | ||
TitleName, | TitleName, | ||
+ | cntLines, | ||
+ | cntCurrent, | ||
+ | cntInPrint, | ||
cntForSale, | cntForSale, | ||
− | |||
cntInStock, | cntInStock, | ||
qtyInStock, | qtyInStock, | ||
Line 28: | Line 38: | ||
t.ID_Dept, | t.ID_Dept, | ||
t.Name AS TitleName, | t.Name AS TitleName, | ||
+ | i.cntLines, | ||
+ | i.cntCurrent, | ||
+ | i.cntInPrint, | ||
i.cntForSale, | i.cntForSale, | ||
− | |||
i.cntInStock, | i.cntInStock, | ||
i.qtyInStock, | i.qtyInStock, | ||
Line 37: | Line 49: | ||
SELECT | SELECT | ||
ID_Title, ID_ItTyp, | ID_Title, ID_ItTyp, | ||
+ | COUNT(ID) AS cntLines, | ||
+ | SUM(IF(isCurrent,1,0)) AS cntCurrent, | ||
+ | SUM(IF(isInPrint,1,0)) AS cntInPrint, | ||
SUM(IF(isForSale,1,0)) AS cntForSale, | SUM(IF(isForSale,1,0)) AS cntForSale, | ||
− | SUM(IF( | + | SUM(IF(iq.qtyForSale>0,1,0)) AS cntInStock, |
− | + | SUM(iq.qtyForSale) AS qtyInStock, | |
− | SUM( | ||
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;</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 /> |
Latest revision as of 01:53, 24 January 2011
About
- Purpose: fills _title_ittyps.
- Input: cat_items, cat_titles,
- REDIRECT Template:l/vc/query
- Output: _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
- 2010-11-10
- Added DROP PROCEDURE for easier maintenance.
- 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
- 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 qryStk_items_remaining instead of relying on cat_items to be current
- 2007-09-20
- 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
<mysql>DROP PROCEDURE IF EXISTS Upd_TitleIttyps_fr_CatItems_Titles; CREATE PROCEDURE Upd_TitleIttyps_fr_CatItems_Titles()
REPLACE INTO _title_ittyps( ID_Title, ID_ItTyp, ID_Dept, TitleName, cntLines, cntCurrent, cntInPrint, cntForSale, cntInStock, qtyInStock, currMinPrice, currMaxPrice) SELECT i.ID_Title, i.ID_ItTyp, t.ID_Dept, t.Name AS TitleName, i.cntLines, i.cntCurrent, i.cntInPrint, i.cntForSale, i.cntInStock, i.qtyInStock, i.currMinPrice, i.currMaxPrice FROM ( SELECT ID_Title, ID_ItTyp, COUNT(ID) AS cntLines, SUM(IF(isCurrent,1,0)) AS cntCurrent, SUM(IF(isInPrint,1,0)) AS cntInPrint, SUM(IF(isForSale,1,0)) AS cntForSale, SUM(IF(iq.qtyForSale>0,1,0)) AS cntInStock, SUM(iq.qtyForSale) AS qtyInStock, MIN(PriceSell) AS currMinPrice, MAX(PriceSell) AS currMaxPrice FROM cat_items AS i LEFT JOIN qryStk_items_remaining AS iq ON i.ID=iq.ID_Item GROUP BY ID_Title, ID_ItTyp ) 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>