VbzCart/docs/queries/qryStk lines remaining: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | queries
Jump to navigation Jump to search
imported>Woozle
(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 (Woozle moved page VbzCart/VbzCart/queries/qryStk lines remaining to VbzCart/docs/queries/qryStk lines remaining without leaving a redirect: part 3/5)
 
(9 intermediate revisions by one other user not shown)
Line 1: Line 1:
==Details==
==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''':
* '''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''':
** '''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==
<section begin=sql /><mysql>CREATE OR REPLACE VIEW v_stk_lines_remaining AS
<mysql>CREATE OR REPLACE VIEW qryStk_lines_remaining AS
   SELECT
   SELECT
     st.ID,
     st.ID,
Line 20: 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 31: 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>
<section end=sql />

Latest revision as of 01:56, 25 February 2024

About

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>
Retrieved from "https://wooz.dev/mw/index.php?title=VbzCart/docs/queries/qryStk_lines_remaining&oldid=6817"