Difference between revisions of "VbzCart/procs/Upd TitleIttyps fr CatItems Titles"

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 Jump to search
(created in db)
(better stock numbers)
 
(4 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 10: Line 10:
 
*** Added DROP PROCEDURE for easier maintenance.
 
*** Added DROP PROCEDURE for easier maintenance.
 
*** Actually created in database (procs did not get ported from L48 to Rizzo).
 
*** 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.
Line 21: Line 25:
 
       ID_Dept,
 
       ID_Dept,
 
       TitleName,
 
       TitleName,
 +
      cntLines,
 +
      cntCurrent,
 +
      cntInPrint,
 
       cntForSale,
 
       cntForSale,
−
      cntInPrint,
 
 
       cntInStock,
 
       cntInStock,
 
       qtyInStock,
 
       qtyInStock,
Line 32: 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.cntInPrint,
 
 
       i.cntInStock,
 
       i.cntInStock,
 
       i.qtyInStock,
 
       i.qtyInStock,
Line 41: 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(isInPrint,1,0)) AS cntInPrint,
+
         SUM(IF(iq.qtyForSale>0,1,0)) AS cntInStock,
−
        SUM(IF(qtyInStock>0,1,0)) AS cntInStock,
+
         SUM(iq.qtyForSale) AS qtyInStock,
−
         SUM(qtyInStock) AS qtyInStock,
 
 
         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

  1. 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
  • 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>

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/procs/Upd_TitleIttyps_fr_CatItems_Titles&oldid=14895"