Difference between revisions of "VbzCart/queries/qryStk lines remaining"
Jump to navigation
Jump to search
(New page: ==Details== * '''Returns''': {{vbzcart/table|stk_items}} actually in stock, which is actually different things: ** "forSale": items which are visible as "in stock" to customers ** "forShip...) |
m (used by qryStk_lines_remaining_forSale) |
||
| Line 5: | Line 5: | ||
** 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''': | + | * '''Used by''': {{vbzcart/query|qryStk_lines_remaining_forSale}} |
==SQL== | ==SQL== | ||
<section begin=sql /><mysql>CREATE OR REPLACE VIEW v_stk_lines_remaining AS | <section begin=sql /><mysql>CREATE OR REPLACE VIEW v_stk_lines_remaining AS | ||
Revision as of 01:44, 8 February 2009
Details
- 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
SQL
<mysql>CREATE OR REPLACE VIEW v_stk_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.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>