VbzCart/docs/queries/qryItms to restock

From Woozle Writes Code
< VbzCart‎ | docs‎ | queries
Jump to navigation Jump to search

Details

  • Returns: List of items where we need more of them, either to fill orders or to meet minimum stock levels. Accounts for restock items already requested and items currently in stock.
  • Requires: Template:Vbzcart/query
  • Used by:
  • History:
    • 2008-11-18 created for new restocking process
    • 2008-11-19 added additional fields from revised union query
    • 2008-11-22 various quantities need to be SUMmed, else we got the wrong answer (probably just the first matching row)
  • Notes: It might be a good diagnostic to run this query without the final "HAVING" clause (or with QtyToGet<0) and look for anything that doesn't make sense. Hopefully most of the negative numbers are overstocked items and unclosed restock requests (they should be closed).

SQL

<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItms_to_restock AS SELECT 

 ID_Item,
 COUNT(ID_Item) AS DupCount,
 SUM(iru.QtyOpen) AS QtyOpen,
 SUM(iru.QtyForSale) AS QtyForSale,
 SUM(iru.QtyMin_Stk) AS QtyMin_Stk,
 SUM(iru.QtyOnOrder) AS QtyOnOrder,
 SUM(IFNULL(QtyOpen,0))
   -SUM(IFNULL(QtyForSale,0))
   +SUM(IFNULL(QtyMin_Stk,0))
   -SUM(IFNULL(QtyOnOrder,0))
   AS QtyToGet

FROM qryItms_to_restock_union AS iru GROUP BY ID_Item HAVING QtyToGet>0;</mysql> <section end=sql />

Retrieved from "https://wooz.dev/mw/index.php?title=VbzCart/docs/queries/qryItms_to_restock&oldid=6772"