VbzCart/docs/procs/Upd CatItems fr StkItems StkBins: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (New page: ==About== * '''Action''': updates availability from current stock ==SQL== <section begin=sql /><mysql>CREATE PROCEDURE Upd_CatItems_fr_StkItems_StkBins() UPDATE cat_items AS i LEFT JOI...) |
m (Woozle moved page VbzCart/VbzCart/procs/Upd CatItems fr StkItems StkBins to VbzCart/docs/procs/Upd CatItems fr StkItems StkBins without leaving a redirect: part 2) |
||
| (3 intermediate revisions by one other user not shown) | |||
| Line 1: | Line 1: | ||
==About== | ==About== | ||
* '''Action''': updates availability from current stock | * '''Action''': updates availability from current stock | ||
* '''Input''': {{vbzcart|table|stk_items}}, {{vbzcart|table|stk_bins}} | |||
* '''Output''': {{vbzcart|table|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== | ==SQL== | ||
<section begin=sql /><mysql>CREATE PROCEDURE Upd_CatItems_fr_StkItems_StkBins() | <section begin=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 ( | UPDATE cat_items AS i LEFT JOIN ( | ||
SELECT | SELECT | ||
| Line 8: | Line 14: | ||
SUM(si.Qty) AS QtyInStock | SUM(si.Qty) AS QtyInStock | ||
FROM stk_items AS si LEFT JOIN stk_bins AS sb ON si.ID_Bin=sb.ID | FROM stk_items AS si LEFT JOIN stk_bins AS sb ON si.ID_Bin=sb.ID | ||
WHERE (sb.isForSale) AND ISNULL(sb.WhenVoided) AND ISNULL(si.WhenRemoved) | WHERE (sb.isForSale) AND (sb.isEnabled) AND ISNULL(sb.WhenVoided) AND ISNULL(si.WhenRemoved) | ||
GROUP BY ID_Item | GROUP BY ID_Item | ||
) AS sig ON i.ID=sig.ID_Item | ) AS sig ON i.ID=sig.ID_Item | ||
SET | SET | ||
i. | i.QtyIn_Stk = sig.QtyInStock, | ||
i.isForSale = i.isInPrint OR (sig.QtyInStock > 0);</mysql> | i.isForSale = i.isInPrint OR (sig.QtyInStock > 0);</mysql> | ||
<section end=sql /> | <section end=sql /> | ||
Latest revision as of 01:55, 25 February 2024
About
- Action: updates availability from current stock
- Input: Template:Vbzcart, Template:Vbzcart
- Output: Template:Vbzcart
- History:
- 2011-01-24 Corrected i.QtyInStock to i.QtyIn_Stk
- 2012-02-06 Added "AND (sb.isEnabled)" condition (isEnabled flag is new)
SQL
<section begin=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>
<section end=sql />