VbzCart/docs/queries/qryPkgLines qtys done: Difference between revisions

From Woozle Writes Code
< VbzCart‎ | docs‎ | queries
Jump to navigation Jump to search
imported>Woozle
(Created page with "==About== * '''Requires''': {{vbzcart|table|ord_pkgs}} * '''Returns''': sum of "done" quantities for all packages which have been checked in each order ** In other words, you get...")
 
m (Woozle moved page VbzCart/VbzCart/queries/qryPkgLines qtys done to VbzCart/docs/queries/qryPkgLines qtys done without leaving a redirect: part 2)
 
(2 intermediate revisions by one other user not shown)
Line 1: Line 1:
==About==
==About==
* '''Requires''': {{vbzcart|table|ord_pkgs}}
* '''Requires''': {{vbzcart|table|ord_pkgs}}
* '''Returns''': sum of "done" quantities for all packages which have been checked in each order
* '''Returns''': "done" quantity for every line of all packages which have been checked in each order
** In other words, you get one record for each item in an order; that record gives the sum of quantities "done" for that item across all "checked" packages. This lets you know how many of each item remain open (to be filled).
** In order to return a current quantity status for each item in an order, this query should be filtered for that order ID (ID_Order) and then summed in code; summing in SQL and then filtering doesn't speed things up. (There's probably a way to do this in MySQL using advanced syntax I haven't dug into yet.)
** There is also PHP code somewhere which does this, but I needed to be able to pull these numbers up in w3tpl.
* '''History''':
* '''History''':
** '''2011-05-31''' created for packing slips
** '''2011-05-31''' created for packing slips
** '''2011-06-01''' added p.Seq so code can filter for packages ''before'' the current one -- so that the results will be the same (at least in theory) even after other packages have been created
==SQL==
==SQL==
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryPkgLines_qtys_done AS
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryPkgLines_qtys_done AS
SELECT
SELECT
  p.Seq,
   p.ID_Order,
   p.ID_Order,
   pl.ID_OrdLine,
   pl.ID_OrdLine,
   pl.ID_Item,
   pl.ID_Item,
   SUM(pl.QtyShipped + pl.QtyKilled + pl.QtyNotAvail) AS QtyDone
   (IFNULL(pl.QtyShipped,0) + IFNULL(pl.QtyKilled,0) + IFNULL(pl.QtyNotAvail,0)) AS QtyDone
FROM
FROM
   ord_pkgs AS p LEFT JOIN
   ord_pkgs AS p LEFT JOIN
   ord_pkg_lines as pl ON pl.ID_Pkg=p.ID
   ord_pkg_lines as pl ON pl.ID_Pkg=p.ID
WHERE (WhenChecked IS NOT NULL)
WHERE (WhenChecked IS NOT NULL);</mysql>
GROUP BY ID_Order, ID_OrdLine, ID_Item;</mysql>
<section end=sql />
<section end=sql />

Latest revision as of 01:55, 25 February 2024

About

  • Requires: Template:Vbzcart
  • Returns: "done" quantity for every line of all packages which have been checked in each order
    • In order to return a current quantity status for each item in an order, this query should be filtered for that order ID (ID_Order) and then summed in code; summing in SQL and then filtering doesn't speed things up. (There's probably a way to do this in MySQL using advanced syntax I haven't dug into yet.)
  • History:
    • 2011-05-31 created for packing slips
    • 2011-06-01 added p.Seq so code can filter for packages before the current one -- so that the results will be the same (at least in theory) even after other packages have been created

SQL

<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryPkgLines_qtys_done AS SELECT 

 p.Seq,
 p.ID_Order,
 pl.ID_OrdLine,
 pl.ID_Item,
 (IFNULL(pl.QtyShipped,0) + IFNULL(pl.QtyKilled,0) + IFNULL(pl.QtyNotAvail,0)) AS QtyDone

FROM 

 ord_pkgs AS p LEFT JOIN
 ord_pkg_lines as pl ON pl.ID_Pkg=p.ID

WHERE (WhenChecked IS NOT NULL);</mysql> <section end=sql />

Retrieved from "https://wooz.dev/mw/index.php?title=VbzCart/docs/queries/qryPkgLines_qtys_done&oldid=6784"