VbzCart/procs/Upd SupplierIttyps

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
< VbzCart‎ | procs
Jump to: navigation, search

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>