VbzCart/docs/queries/qryStock by Opt Type: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (New page: ==About== * '''Returns''': Stock totals by itemoption+itemtype * '''Requires''': {{vbzcart/table|cat_items}}, {{vbzcart/table|cat_ittyps}}, {{vbzcart/query|qryStkItms_for_sale}} * '''Used ...) |
imported>Woozle (+WHERE QtyForSale) |
||
Line 5: | Line 5: | ||
* '''History''': | * '''History''': | ||
** '''2008-12-10''' Written almost from scratch in order to get stock index page working again | ** '''2008-12-10''' Written almost from scratch in order to get stock index page working again | ||
** '''2008-12-18''' added "WHERE QtyForSale" so removed stock wouldn't be included in TypeCount | |||
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_by_Opt_Type AS | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_by_Opt_Type AS | ||
Line 21: | Line 22: | ||
LEFT JOIN cat_ittyps AS it ON i.ID_ItTyp=it.ID) | LEFT JOIN cat_ittyps AS it ON i.ID_ItTyp=it.ID) | ||
LEFT JOIN qryStkItms_for_sale AS stk ON i.ID=stk.ID_Item | LEFT JOIN qryStkItms_for_sale AS stk ON i.ID=stk.ID_Item | ||
WHERE QtyForSale | |||
GROUP BY it.ID, io.ID, io.CatKey, io.Sort, it.NamePlr, it.NameSng | GROUP BY it.ID, io.ID, io.CatKey, io.Sort, it.NamePlr, it.NameSng | ||
HAVING QtyForSale | HAVING QtyForSale | ||
ORDER BY io.Sort;</mysql> | ORDER BY io.Sort;</mysql> | ||
<section end=sql /> | <section end=sql /> |
Revision as of 13:17, 18 December 2008
About
- Returns: Stock totals by itemoption+itemtype
- Requires: Template:Vbzcart/table, Template:Vbzcart/table, Template:Vbzcart/query
- Used by: stock index page (static version; may also be used for dynamic version, but that will probably work better with cached data)
- History:
- 2008-12-10 Written almost from scratch in order to get stock index page working again
- 2008-12-18 added "WHERE QtyForSale" so removed stock wouldn't be included in TypeCount
SQL
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_by_Opt_Type AS SELECT
io.ID AS ID_Opt, it.ID AS ID_Typ, io.CatKey, io.Sort, it.NameSng AS TypeSing, IFNULL(it.NamePlr,it.NameSng) AS TypePlur, SUM(stk.QtyForSale) AS QtyForSale, Count(it.ID) AS TypeCount
FROM
((cat_ioptns AS io LEFT JOIN cat_items AS i ON i.ID_ItOpt=io.ID) LEFT JOIN cat_ittyps AS it ON i.ID_ItTyp=it.ID) LEFT JOIN qryStkItms_for_sale AS stk ON i.ID=stk.ID_Item
WHERE QtyForSale GROUP BY it.ID, io.ID, io.CatKey, io.Sort, it.NamePlr, it.NameSng HAVING QtyForSale ORDER BY io.Sort;</mysql> <section end=sql />