VbzCart/docs/queries/qryItms to restock union: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (New page: ==Details== * '''Returns''': List of all items relevant to calculating items-to-restock, along with the relevant numbers (quantity on hand, quantity needed for orders...) but not combined ...) |
m (Woozle moved page VbzCart/VbzCart/queries/qryItms to restock union to VbzCart/docs/queries/qryItms to restock union without leaving a redirect: part 2) |
||
| (One intermediate revision by one other user not shown) | |||
| Line 1: | Line 1: | ||
==Details== | ==Details== | ||
* '''Returns''': List of all items relevant to calculating items-to-restock, along with the relevant numbers (quantity on hand, quantity needed for orders...) but not combined yet (that happens in [[#qryItms_to_restock]]) | * '''Returns''': List of all items relevant to calculating items-to-restock, along with the relevant numbers (quantity on hand, quantity needed for orders...) but not combined yet (that happens in [[#qryItms_to_restock]]) | ||
* '''Requires''': {{vbzcart/query|qryItms_open}}, {{vbzcart/query|qryStkItms_for_sale}}, {{vbzcart/table|cat_items}}, {{vbzcart/query| | * '''Requires''': {{vbzcart/query|qryItms_open}}, {{vbzcart/query|qryStkItms_for_sale}}, {{vbzcart/table|cat_items}}, {{vbzcart/query|qryRstkItms_expected}} | ||
* '''Used by''': {{vbzcart/query|qryItms_to_restock}} | * '''Used by''': {{vbzcart/query|qryItms_to_restock}} | ||
* '''History''': | * '''History''': | ||
** '''2008-11-18''' created for new restocking process (under construction; need to add items-in-transit for restocks) | ** '''2008-11-18''' created for new restocking process (under construction; need to add items-in-transit for restocks) | ||
** '''2008-11-22''' in-transit items should never be negative; if we receive something not requested, it goes into stock | ** '''2008-11-22''' in-transit items should never be negative; if we receive something not requested, it goes into stock | ||
** '''2008-12-05''' Updated source query name: {{vbzcart/query|qryRstkItms_en_route}} → {{vbzcart/query|qryRstkItms_expected}} | |||
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItms_to_restock_union AS | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItms_to_restock_union AS | ||
| Line 38: | Line 39: | ||
NULL AS QtyMin_Stk, | NULL AS QtyMin_Stk, | ||
IF((IFNULL(QtyOrd,0)-IFNULL(QtyRecd,0))>0,IFNULL(QtyOrd,0)-IFNULL(QtyRecd,0),0) AS QtyOnOrder | IF((IFNULL(QtyOrd,0)-IFNULL(QtyRecd,0))>0,IFNULL(QtyOrd,0)-IFNULL(QtyRecd,0),0) AS QtyOnOrder | ||
FROM | FROM qryRstkItms_expected;</mysql> | ||
<section end=sql /> | <section end=sql /> | ||
Latest revision as of 01:55, 25 February 2024
Details
- Returns: List of all items relevant to calculating items-to-restock, along with the relevant numbers (quantity on hand, quantity needed for orders...) but not combined yet (that happens in #qryItms_to_restock)
- Requires: Template:Vbzcart/query, Template:Vbzcart/query, Template:Vbzcart/table, Template:Vbzcart/query
- Used by: Template:Vbzcart/query
- History:
- 2008-11-18 created for new restocking process (under construction; need to add items-in-transit for restocks)
- 2008-11-22 in-transit items should never be negative; if we receive something not requested, it goes into stock
- 2008-12-05 Updated source query name: Template:Vbzcart/query → Template:Vbzcart/query
SQL
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItms_to_restock_union AS SELECT
ID_Item, QtyOpen, NULL AS QtyForSale, NULL AS QtyMin_Stk, NULL AS QtyOnOrder
FROM qryItms_open UNION SELECT
ID_Item, NULL AS QtyOpen, QtyForSale, NULL AS QtyMin_Stk, NULL AS QtyOnOrder
FROM qryStkItms_for_sale UNION SELECT
ID AS ID_Item, NULL AS QtyOpen, NULL AS QtyForSale, QtyMin_Stk, NULL AS QtyOnOrder
FROM cat_items WHERE QtyMin_Stk>0 UNION SELECT
ID_Item, NULL AS QtyOpen, NULL AS QtyForSale, NULL AS QtyMin_Stk, IF((IFNULL(QtyOrd,0)-IFNULL(QtyRecd,0))>0,IFNULL(QtyOrd,0)-IFNULL(QtyRecd,0),0) AS QtyOnOrder
FROM qryRstkItms_expected;</mysql> <section end=sql />