VbzCart/docs/queries/qryStock by Opt Type

From Woozle Writes Code
< VbzCart‎ | docs‎ | queries
Jump to navigation Jump to search

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 />

Retrieved from "https://wooz.dev/mw/index.php?title=VbzCart/docs/queries/qryStock_by_Opt_Type&oldid=6822"