VbzCart/procs/Upd SupplierIttyps

About

• Input: cat_supp, _titles, _title_ittyps, cat_ittyps
• Output: _supplier_ittyps (delete/insert)
• Notes: Later we might refine this into a way to view unavailable suppliers as well... but that's very low priority, and it might be better to have a separate table for it anyway (keep "active" operations fast).
• History:
  • 2010-11-10
    • Added DROP PROCEDURE for easier maintenance.
    • Created in database (procs were not ported from L48 to Rizzo) (why did this one work without any DELIMITER commands?).
  • 2011-02-02
    • Added WHERE tc.cntForSale>0 so it wouldn't count titles with nothing for sale (duh).
    • Added AND (it.ID IS NOT NULL) -- input data evidently includes some rows where Item Type is NULL. This needs to be fixed.

SQL

<mysql>DROP PROCEDURE IF EXISTS Upd_SupplierIttyps; CREATE PROCEDURE Upd_SupplierIttyps()

   BEGIN
     DELETE FROM _supplier_ittyps;
     INSERT INTO _supplier_ittyps
     SELECT
       s.ID,
       if(Count(ti.ID_Title)=1,it.NameSng,it.NamePlr) AS ItemType,
       Count(ti.ID_Title) AS ItemCount,
       s.Name, s.CatKey
     FROM (
       (cat_supp AS s LEFT JOIN _titles AS tc ON tc.ID_Supp=s.ID)
       LEFT JOIN _title_ittyps AS ti ON ti.ID_title=tc.ID)
       LEFT JOIN cat_ittyps AS it ON ti.ID_ItTyp=it.ID
     WHERE (tc.cntForSale>0) AND (it.ID IS NOT NULL)
     GROUP BY s.ID, s.Name, s.CatKey, it.NameSng, it.NamePlr, ID_Parent
     HAVING SUM(tc.cntForSale)
     ORDER BY s.Name, SUM(tc.cntForSale) DESC;
   END</mysql>