|
|
| Line 72: |
Line 72: |
| | | | |
| | ===Stock=== | | ===Stock=== |
| − | ====v_stk_lines_remaining====
| + | This was the first batch of queries I created, before I had decided to go with the qry prefix as in Access. |
| − | View listing stk_items actually in stock, which is actually different things:
| + | * [[/v_stk_lines_remaining]] |
| − | * "forSale": items which are visible as "in stock" to customers
| + | * [[/v_stk_items_remaining]] |
| − | * "forShip": items which are available for shipping
| + | * [[/v_stk_titles_remaining]] |
| − | * items neither for shipping nor for sale (probably some kind of recordkeeping purpose)
| + | * [[/v_stk_byItemAndBin]] |
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW v_stk_lines_remaining AS
| + | * [[/v_stk_byItemAndBin_wItemInfo]] |
| − | SELECT
| + | * [[/v_stk_byItemAndBin_wInfo]] |
| − | st.ID,
| + | * [[/qryStk_lines_remaining_forSale]] |
| − | st.ID_Bin,
| + | * [[/qryStkItms_for_sale]] |
| − | st.ID_Item,
| + | * [[/qryStkItms_for_sale_wItem_data]] |
| − | IF(sb.isForSale,st.Qty,0) AS QtyForSale,
| + | * [[/qryStk_lines_Title_info]] |
| − | IF(sb.isForShip,st.Qty,0) AS QtyForShip,
| + | * [[/qryStock_forOpenOrders]] |
| − | st.Qty AS QtyExisting,
| + | * [[/qryStock_byOpt_andType]] |
| − | st.CatNum,
| + | * [[/qryStock_Titles_most_recent]] |
| − | 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>
| |
| − | <section end=sql />
| |
| − | ====v_stk_items_remaining====
| |
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW v_stk_items_remaining AS
| |
| − | SELECT
| |
| − | ID_Item,
| |
| − | SUM(QtyForSale) AS QtyForSale,
| |
| − | SUM(QtyForShip) AS QtyForShip,
| |
| − | SUM(QtyExisting) AS QtyExisting
| |
| − | FROM v_stk_lines_remaining
| |
| − | GROUP BY ID_Item;</mysql>
| |
| − | <section end=sql />
| |
| − | ====v_stk_titles_remaining====
| |
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW v_stk_titles_remaining AS
| |
| − | SELECT
| |
| − | ID_Title,
| |
| − | SUM(QtyForSale) AS QtyForSale,
| |
| − | SUM(QtyForShip) AS QtyForShip,
| |
| − | SUM(QtyExisting) AS QtyExisting
| |
| − | FROM v_stk_lines_remaining AS s LEFT JOIN cat_items AS i ON s.ID_Item=i.ID
| |
| − | GROUP BY i.ID_Title;</mysql>
| |
| − | <section end=sql />
| |
| − | | |
| − | ====v_stk_byItemAndBin====
| |
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin AS
| |
| − | SELECT
| |
| − | ID_Item,
| |
| − | ID_Bin,
| |
| − | SUM(QtyForSale) AS QtyForSale,
| |
| − | SUM(QtyForShip) AS QtyForShip,
| |
| − | SUM(QtyExisting) AS QtyExisting
| |
| − | FROM v_stk_lines_remaining
| |
| − | GROUP BY ID_Item, ID_Bin
| |
| − | HAVING SUM(QtyExisting)>0;</mysql>
| |
| − | <section end=sql />
| |
| − | ====v_stk_byItemAndBin_wItemInfo====
| |
| − | Is anything actually using this? '''Document!''' (I've tentatively removed the link in Access.)
| |
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin_wItemInfo AS
| |
| − | SELECT * FROM v_stk_byItemAndBin AS s LEFT JOIN qryCat_Items AS i ON s.ID_Item=i.ID;</mysql>
| |
| − | <section end=sql />
| |
| − | | |
| − | ====v_stk_byItemAndBin_wInfo====
| |
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW v_stk_byItemAndBin_wInfo AS
| |
| − | SELECT
| |
| − | s.*,
| |
| − | i.*,
| |
| − | b.ID_Place,
| |
| − | b.Code AS BinCode,
| |
| − | b.Descr AS BinDescr
| |
| − | FROM
| |
| − | (
| |
| − | v_stk_byItemAndBin AS s LEFT JOIN qryCat_Items AS i ON s.ID_Item=i.ID
| |
| − | ) LEFT JOIN stk_bins AS b ON s.ID_Bin=b.ID;</mysql>
| |
| − | <section end=sql />
| |
| − | ====qryStk_lines_remaining_forSale====
| |
| − | * '''Requires''': [[#v_stk_lines_remaining]]
| |
| − | * '''Used by''': [[#qryStkItms_for_sale]] | |
| − | * '''Note''': Eventually rename as "qryStkLines_for_sale" | |
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStk_lines_remaining_forSale AS
| |
| − | SELECT *
| |
| − | FROM v_stk_lines_remaining
| |
| − | WHERE QtyForSale>0;</mysql>
| |
| − | <section end=sql />
| |
| − | | |
| − | ====qryStkItms_for_sale====
| |
| − | * '''Requires''': [[#qryStk_lines_remaining_forSale]]
| |
| − | * '''Used by''': [[#qryStkItms_for_sale_wItem_data]] | |
| − | * '''History''': | |
| − | ** '''2008-11-17''' Created for revised restocking process
| |
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStkItms_for_sale AS
| |
| − | SELECT
| |
| − | ID_Item,
| |
| − | SUM(qsl.QtyForSale) AS QtyForSale,
| |
| − | SUM(qsl.QtyForShip) AS QtyForShip,
| |
| − | SUM(qsl.QtyExisting) AS QtyExisting
| |
| − | FROM qryStk_lines_remaining_forSale AS qsl
| |
| − | GROUP BY ID_Item;</mysql>
| |
| − | <section end=sql />
| |
| − | | |
| − | ====qryStkItms_for_sale_wItem_data====
| |
| − | * '''Requires''': [[#qryStkItms_for_sale]]
| |
| − | * '''Note''': this one may be redundant; the time when we need the item data is *after* all the sources of items-to-restock have been UNIONed together and GROUPed by item | |
| − | * '''History''':
| |
| − | ** '''2008-11-17''' Created for revised restocking process
| |
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStkItms_for_sale_wItem_data AS
| |
| − | SELECT
| |
| − | qsi.*,
| |
| − | i.*
| |
| − | FROM qryStkItms_for_sale AS qsi LEFT JOIN cat_items AS i ON qsi.ID_Item=i.ID;</mysql>
| |
| − | <section end=sql />
| |
| − | | |
| − | ====qryOrdLines_ord_status====
| |
| − | * '''Depends on''': [[#qryOrdLines_open]], [[#qryStk_lines_remaining_forSale]], [[#qryRstkLines_en_route]]
| |
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryOrdLines_ord_status AS
| |
| − | SELECT
| |
| − | i.ID AS ID_Item,
| |
| − | i.CatNum,
| |
| − | qoi.ID AS ID_OrdLine,
| |
| − | qoi.ID_Order,
| |
| − | qsr.ID AS ID_Stock,
| |
| − | qri.ID AS ID_RstkItem,
| |
| − | IFNULL(qoi.QtyOrd,0) AS QtyOrd,
| |
| − | IFNULL(qoi.QtyDone,0) AS QtyDone,
| |
| − | IFNULL(qri.QtyExp IS NULL,IFNULL(qri.QtyOrd,0)) AS QtyOnRstk,
| |
| − | IFNULL(qsr.QtyForShip,0) AS QtyOnHand,
| |
| − | i.QtyMin_Stk AS QtyMin,
| |
| − | qoi.WhenOldestOrder,
| |
| − | qoi.WhenSoonestDue
| |
| − | FROM
| |
| − | (
| |
| − | (cat_items AS i LEFT JOIN qryOrdLines_open AS qoi ON qoi.ID_Item=i.ID)
| |
| − | LEFT JOIN qryStk_lines_remaining_forSale AS qsr ON qsr.ID_Item=i.ID)
| |
| − | LEFT JOIN qryRstkLines_en_route AS qri ON qri.ID_Item=i.ID;</mysql>
| |
| − | <section end=sql />
| |
| − | | |
| − | ====qryOrdLines_to_restock====
| |
| − | * '''Depends on''': [[#qryOrdLines_ord_status]] | |
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryOrdLines_to_restock AS
| |
| − | SELECT
| |
| − | ID_Item,
| |
| − | i.ID_OrdLine,
| |
| − | i.ID_Order,
| |
| − | Sum(i.QtyOrd) AS QtyOrd,
| |
| − | Sum(i.QtyDone) AS QtyDone,
| |
| − | Sum(i.QtyOnRstk) AS QtyOnRstk,
| |
| − | Sum(i.QtyOnHand) AS QtyOnHand,
| |
| − | Max(i.QtyMin) AS QtyMin,
| |
| − | Max(IFNULL(i.QtyMin,0))+Sum(IFNULL(i.QtyOrd,0)-IFNULL(i.QtyOnRstk,0)-IFNULL(i.QtyOnHand,0)) AS QtyToGet,
| |
| − | Sum(IFNULL(i.QtyOrd,0)-IFNULL(i.QtyOnRstk,0)-IFNULL(i.QtyOnHand,0)) AS QtyShort,
| |
| − | Min(i.WhenOldestOrder) AS WhenOldestOrder,
| |
| − | Min(i.WhenSoonestDue) AS WhenSoonestDue,
| |
| − | Count(ID_RstkItem) AS RstkLines
| |
| − | FROM qryOrdLines_ord_status AS i
| |
| − | GROUP BY ID_Item, ID_OrdLine, ID_Order
| |
| − | HAVING Max(IFNULL(i.QtyMin,0))+Sum(IFNULL(i.QtyOrd,0)-IFNULL(i.QtyOnRstk,0)-IFNULL(i.QtyOnHand,0))>0;</mysql>
| |
| − | <section end=sql />
| |
| − | | |
| − | ====qryFrm_OrdLines_to_restock====
| |
| − | '''Depends on''': [[#qryOrdLines_to_restock]]
| |
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryFrm_OrdLines_to_restock AS
| |
| − | SELECT
| |
| − | qi.*,
| |
| − | ci.CatNum,
| |
| − | ci.isForSale,
| |
| − | ci.ID_Supplier,
| |
| − | ci.PriceSell,
| |
| − | ci.PriceBuy
| |
| − | FROM qryOrdLines_to_restock AS qi
| |
| − | LEFT JOIN qryCat_Items AS ci ON ci.ID=qi.ID_Item;</mysql>
| |
| − | <section end=sql />
| |
| − | | |
| − | ====qryStk_lines_Title_info====
| |
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStk_lines_Title_info AS
| |
| − | SELECT
| |
| − | s.*,
| |
| − | i.ID_Title
| |
| − | FROM stk_items AS s LEFT JOIN cat_items AS i ON s.ID_Item=i.ID;</mysql>
| |
| − | <section end=sql />
| |
| − | ====qryOrdLines_status_union====
| |
| − | * '''Requires''': [[#qryOrdLines_open]], [[#qryRstkLines_en_route]], [[#v_stk_lines_remaining]]
| |
| − | * '''Notes''': This is another one of those union queries that is "cancelled" while you're trying to create it... but in this case, it does get created.
| |
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryOrdLines_status_union AS
| |
| − | SELECT
| |
| − | /* 1 */ CONCAT("oio-",oio.ID) AS Source,
| |
| − | /* 2 */ oio.ID AS ID,
| |
| − | /* 3 */ oio.ID_Order,
| |
| − | /* 4 */ oio.ID_Item,
| |
| − | /* 5 */ oio.QtyOrd,
| |
| − | /* 6 */ oio.QtyDone,
| |
| − | /* 7 */ 0 AS QtyOnRstk,
| |
| − | /* 8 */ 0 AS QtyOnHand,
| |
| − | /* 9 */ 0 AS QtyMin,
| |
| − | /* 10 */ oio.WhenStarted,
| |
| − | /* 11 */ oio.WhenNeeded
| |
| − | FROM qryOrdLines_open AS oio
| |
| − | UNION
| |
| − | SELECT
| |
| − | /* 1 */ CONCAT("rie-",rie.ID) AS Source,
| |
| − | /* 2 */ rie.ID AS ID,
| |
| − | /* 3 */ rie.ID_Order,
| |
| − | /* 4 */ rie.ID_Item,
| |
| − | /* 5 */ 0 AS QtyOrd,
| |
| − | /* 6 */ 0 AS QtyDone,
| |
| − | /* 7 */ IF(rie.QtyExp > rie.QtyOrd,rie.QtyExp,rie.QtyOrd) AS QtyOnRstk,
| |
| − | /* 8 */ 0 AS QtyOnHand,
| |
| − | /* 9 */ 0 AS QtyMin,
| |
| − | /* 10 */ NULL AS WhenStarted,
| |
| − | /* 11 */ NULL AS WhenNeeded
| |
| − | FROM qryRstkLines_en_route AS rie
| |
| − | UNION
| |
| − | SELECT
| |
| − | /* 1 */ CONCAT("srs-",srs.ID) AS Source,
| |
| − | /* 2 */ 0 AS ID,
| |
| − | /* 3 */ NULL AS ID_Order,
| |
| − | /* 4 */ srs.ID_Item,
| |
| − | /* 5 */ 0 AS QtyOrd,
| |
| − | /* 6 */ 0 AS QtyDone,
| |
| − | /* 7 */ 0 AS QtyOnRstk,
| |
| − | /* 8 */ srs.QtyForShip AS QtyOnHand,
| |
| − | /* 9 */ 0 AS QtyMin,
| |
| − | /* 10 */ NULL AS WhenStarted,
| |
| − | /* 11 */ NULL AS WhenNeeded
| |
| − | FROM v_stk_lines_remaining AS srs
| |
| − | UNION
| |
| − | SELECT
| |
| − | /* 1 */ CONCAT("itm-",itm.ID) AS Source,
| |
| − | /* 2 */ NULL AS ID,
| |
| − | /* 3 */ NULL AS ID_Order,
| |
| − | /* 4 */ itm.ID AS ID_Item,
| |
| − | /* 5 */ 0 AS QtyOrd,
| |
| − | /* 6 */ 0 AS QtyDone,
| |
| − | /* 7 */ 0 AS QtyOnRsk,
| |
| − | /* 8 */ 0 AS QtyOnHand,
| |
| − | /* 9 */ QtyMin_Stk AS QtyMin,
| |
| − | /* 10 */ NULL AS WhenStarted,
| |
| − | /* 11 */ NULL AS WhenNeeded
| |
| − | FROM cat_items AS itm;</mysql>
| |
| − | <section end=sql />
| |
| − | * '''History''':
| |
| − | ** '''2008-11-02''' changed When field names (more accurate)
| |
| − | | |
| − | ====qryItems_ord_status====
| |
| − | * '''Depends on''': [[#qryOrdLines_status_union]]
| |
| − | * '''Returns''': Status with regard to ordering, i.e. quantities on hand, quantities needed, etc. Used by the "items needed (for restocking)" form, via [[#qryItems_to_restock]]. This was originally in a different section, but it has to be created after qryOrdLines_status_union. | |
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItems_ord_status AS
| |
| − | SELECT
| |
| − | i.ID_Item,
| |
| − | SUM(i.QtyOrd) AS QtyOrd,
| |
| − | SUM(i.QtyDone) AS QtyDone,
| |
| − | SUM(i.QtyOnRstk) AS QtyOnRstk,
| |
| − | SUM(i.QtyOnHand) AS QtyOnHand,
| |
| − | MAX(i.QtyMin) AS QtyMin,
| |
| − | SUM(
| |
| − | IFNULL(i.QtyOrd,0)-IFNULL(i.QtyOnRstk,0)-IFNULL(i.QtyOnHand,0)
| |
| − | )+IFNULL(MAX(i.QtyMin),0) AS QtyToGet,
| |
| − | MIN(i.WhenStarted) AS WhenOldestOrder,
| |
| − | MAX(i.WhenNeeded) AS WhenSoonestDue,
| |
| − | COUNT(i.Source) AS RecCount,
| |
| − | CAST(GROUP_CONCAT(DISTINCT i.Source SEPARATOR ' ') AS CHAR) AS Sources
| |
| − | FROM qryOrdLines_status_union AS i
| |
| − | GROUP BY i.ID_Item
| |
| − | HAVING QtyToGet>0;</mysql>
| |
| − | <section end=sql />
| |
| − | * '''History''': | |
| − | ** '''2008-11-02''' mods to accommodate rewritten source data
| |
| − | | |
| − | ====qryItems_to_restock====
| |
| − | '''Depends on''': [[#qryItems_ord_status]]
| |
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryItems_to_restock AS
| |
| − | SELECT
| |
| − | qi.*,
| |
| − | ci.CatNum,
| |
| − | ci.isForSale,
| |
| − | ci.ID_Supplier,
| |
| − | ci.QtyMin_Stk,
| |
| − | ci.QtyMin_Rstk_Title,
| |
| − | ci.PriceSell,
| |
| − | ci.PriceBuy
| |
| − | FROM qryItems_ord_status AS qi LEFT JOIN qryCat_Items AS ci ON ci.ID=qi.ID_Item
| |
| − | WHERE QtyToGet>0;</mysql>
| |
| − | <section end=sql />
| |
| − | | |
| − | ====qryStock_forOpenOrders====
| |
| − | * '''Action''': Show all stock available to fill open orders, along with helpful information about the Bin, Order, and Item
| |
| − | * '''Depends on''': [[#v_stk_byItemAndBin_wInfo]], [[#qryOrderLines_notPkgd]], [[#qryCbx_Orders]]
| |
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_forOpenOrders AS
| |
| − | SELECT
| |
| − | s.ID_Bin,
| |
| − | s.ID_Item,
| |
| − | oi.ID_Order,
| |
| − | oi.QtyOpen,
| |
| − | s.QtyForSale,
| |
| − | s.QtyForShip,
| |
| − | o.WhenNeeded,
| |
| − | o.Descr AS OrdText,
| |
| − | o.ID_Pull,
| |
| − | CONCAT_WS(' ',s.CatNum,s.Descr) AS ItemText,
| |
| − | /* s.BinCode,
| |
| − | s.BinDescr, are these ever needed? */
| |
| − | CONCAT_WS(' ',s.BinCode,s.BinDescr) AS BinText
| |
| − | FROM (v_stk_byItemAndBin_wInfo AS s LEFT JOIN qryOrderLines_notPkgd AS oi ON s.ID_Item=oi.ID_Item) LEFT JOIN qryCbx_Orders AS o ON oi.ID_Order=o.ID
| |
| − | WHERE (NOT s.isPulled) AND (o.ID_Pull IS NULL) AND (QtyOpen) AND (QtyForShip);</mysql>
| |
| − | <section end=sql />
| |
| − | * There's a possibility this query will return wrong information when there are multiple stock lines corresponding to the items needed; further testing would be a good idea.
| |
| − | | |
| − | ====qryStock_byOpt_andType====
| |
| − | This is used for displaying the "stock" area. The basic records are items, but they're only ones that are in stock, so "Stock" is shorthand for "items that are in stock" in this case.
| |
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_byOpt_andType AS
| |
| − | SELECT
| |
| − | i.ID_ItTyp,
| |
| − | i.ID_ItOpt,
| |
| − | SUM(i.qtyInStock) AS qtyInStock,
| |
| − | it.NameSng AS It_NameSng,
| |
| − | it.NamePlr AS It_NamePlr,
| |
| − | io.CatKey AS Io_CatKey,
| |
| − | io.Descr AS Io_Descr,
| |
| − | IFNULL(it.Sort,'ZZZ') AS It_Sort
| |
| − | FROM (cat_items AS i LEFT JOIN cat_ioptns AS io ON i.ID_ItOpt=io.ID)
| |
| − | LEFT JOIN cat_ittyps AS it ON i.ID_ItTyp=it.ID
| |
| − | GROUP BY i.ID_ItTyp, i.ID_ItOpt, io.Sort
| |
| − | HAVING SUM(i.qtyInStock)
| |
| − | ORDER BY It_Sort,io.Sort;</mysql>
| |
| − | <section end=sql />
| |
| − | ====qryStock_Titles_most_recent====
| |
| − | This defines what the "latest stock" page shows; as given here, it displays the 100 titles most recently added to stock.
| |
| − | <section begin=sql /><mysql>CREATE OR REPLACE VIEW qryStock_Titles_most_recent AS
| |
| − | SELECT
| |
| − | i.ID_Title,
| |
| − | s.QtyForSale AS QtyAdded,
| |
| − | MAX(s.WhenAdded) AS WhenAdded,
| |
| − | st.QtyForSale AS QtyAvail
| |
| − | FROM (v_stk_lines_remaining AS s LEFT JOIN cat_items AS i ON s.ID_Item=i.ID) LEFT JOIN v_stk_titles_remaining AS st ON i.ID_Title=st.ID_Title
| |
| − | GROUP BY i.ID_Title
| |
| − | HAVING QtyAdded
| |
| − | ORDER BY WhenAdded DESC
| |
| − | LIMIT 100;</mysql>
| |
| − | <section end=sql />
| |
| | | | |
| | ===Caching=== | | ===Caching=== |