Difference between revisions of "VbzCart/queries/qryStk lines remaining"
Jump to navigation
Jump to search
(post-move update) |
(names of things that replaced it have changed) |
||
(4 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
==About== | ==About== | ||
+ | * '''OBSOLETE''': replaced by a number of things (search for query name to find them) | ||
* '''Returns''': {{vbzcart/table|stk_items}} actually in stock, which is actually different things: | * '''Returns''': {{vbzcart/table|stk_items}} actually in stock, which is actually different things: | ||
** "forSale": items which are visible as "in stock" to customers | ** "forSale": items which are visible as "in stock" to customers | ||
Line 5: | Line 6: | ||
** items neither for shipping nor for sale (probably some kind of recordkeeping purpose) | ** items neither for shipping nor for sale (probably some kind of recordkeeping purpose) | ||
* '''Requires''': {{vbzcart/table|stk_items}}, {{vbzcart/table|stk_places}} | * '''Requires''': {{vbzcart/table|stk_items}}, {{vbzcart/table|stk_places}} | ||
− | * '''Used by''': {{vbzcart/query|qryStk_lines_remaining_forSale}}, {{vbzcart/query|qryStk_lines_remaining_byBin}} | + | * '''Used by''': {{vbzcart/query|qryStk_lines_remaining_forSale}}, {{vbzcart/query|qryStk_lines_remaining_byBin}}, {{vbzcart/query|qryStk_items_remaining}}, {{vbzcart/query|qryStk_byItem_byBin}} |
* '''History''': | * '''History''': | ||
** '''2009-11-29''' renamed from '''v_stk_lines_remaining''' to '''qryStk_lines_remaining''' | ** '''2009-11-29''' renamed from '''v_stk_lines_remaining''' to '''qryStk_lines_remaining''' | ||
+ | ** '''2010-06-15''' added BinCode (sb.Code) -- needed for clsStkItems::List_forItem() | ||
==SQL== | ==SQL== | ||
− | + | <mysql>CREATE OR REPLACE VIEW qryStk_lines_remaining AS | |
SELECT | SELECT | ||
st.ID, | st.ID, | ||
Line 22: | Line 24: | ||
st.WhenCounted, | st.WhenCounted, | ||
st.Notes, | st.Notes, | ||
+ | sb.Code AS BinCode, | ||
sb.ID_Place, | sb.ID_Place, | ||
sp.Name AS WhName | sp.Name AS WhName | ||
Line 33: | Line 36: | ||
ON sb.ID_Place=sp.ID | ON sb.ID_Place=sp.ID | ||
WHERE (st.WhenRemoved IS NULL) AND (sb.WhenVoided IS NULL) AND (st.Qty <> 0);</mysql> | WHERE (st.WhenRemoved IS NULL) AND (sb.WhenVoided IS NULL) AND (st.Qty <> 0);</mysql> | ||
− |
Latest revision as of 19:19, 24 March 2017
About
- OBSOLETE: replaced by a number of things (search for query name to find them)
- Returns:
- REDIRECT Template:l/vc/table actually in stock, which is actually different things:
- "forSale": items which are visible as "in stock" to customers
- "forShip": items which are available for shipping
- items neither for shipping nor for sale (probably some kind of recordkeeping purpose)
- Requires: stk_items, stk_places
- Used by:
- REDIRECT Template:l/vc/query, qryStk_lines_remaining_byBin, qryStk_items_remaining, qryStk_byItem_byBin
- History:
- 2009-11-29 renamed from v_stk_lines_remaining to qryStk_lines_remaining
- 2010-06-15 added BinCode (sb.Code) -- needed for clsStkItems::List_forItem()
SQL
<mysql>CREATE OR REPLACE VIEW qryStk_lines_remaining AS
SELECT st.ID, st.ID_Bin, st.ID_Item, IF(sb.isForSale,st.Qty,0) AS QtyForSale, IF(sb.isForShip,st.Qty,0) AS QtyForShip, st.Qty AS QtyExisting, st.CatNum, st.WhenAdded, st.WhenChanged, st.WhenCounted, st.Notes, sb.Code AS BinCode, sb.ID_Place, sp.Name AS WhName FROM ( stk_items AS st LEFT JOIN stk_bins AS sb ON sb.ID=st.ID_Bin ) LEFT JOIN stk_places AS sp ON sb.ID_Place=sp.ID WHERE (st.WhenRemoved IS NULL) AND (sb.WhenVoided IS NULL) AND (st.Qty <> 0);</mysql>