VbzCart/procs/Upd CatItems fr StkItems StkBins

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

About

  • Action: updates availability from current stock
  • Input: stk_items, stk_bins
  • Output: cat_items
  • History:
    • 2011-01-24 Corrected i.QtyInStock to i.QtyIn_Stk
    • 2012-02-06 Added "AND (sb.isEnabled)" condition (isEnabled flag is new)

SQL

<mysql>DROP PROCEDURE IF EXISTS `Upd_CatItems_fr_StkItems_StkBins`; CREATE PROCEDURE Upd_CatItems_fr_StkItems_StkBins() 

   UPDATE cat_items AS i LEFT JOIN (
       SELECT
           si.ID_Item,
           SUM(si.Qty) AS QtyInStock
        FROM stk_items AS si LEFT JOIN stk_bins AS sb ON si.ID_Bin=sb.ID
        WHERE (sb.isForSale) AND (sb.isEnabled) AND ISNULL(sb.WhenVoided) AND ISNULL(si.WhenRemoved)
        GROUP BY ID_Item
       ) AS sig ON i.ID=sig.ID_Item
     SET
        i.QtyIn_Stk = sig.QtyInStock,
        i.isForSale = i.isInPrint OR (sig.QtyInStock > 0);</mysql>

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