VbzCart/docs/queries/qryStk lines remaining: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (added BinCode) |
imported>Woozle (obsolete) |
||
| Line 1: | Line 1: | ||
==About== | ==About== | ||
* '''Obsolete''': use '''VCT_StkLines::SQL_forRemaining()''' instead | |||
* '''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 10: | Line 11: | ||
** '''2010-06-15''' added BinCode (sb.Code) -- needed for clsStkItems::List_forItem() | ** '''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 35: | 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> | ||
Revision as of 01:34, 31 December 2015
About
- Obsolete: use VCT_StkLines::SQL_forRemaining() instead
- Returns: Template:Vbzcart/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: Template:Vbzcart/table, Template:Vbzcart/table
- Used by: Template:Vbzcart/query, Template:Vbzcart/query, Template:Vbzcart/query, Template:Vbzcart/query
- 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>