VbzCart/queries/qry PkgItem qtys byOrder

from HTYP, the free directory anyone can edit if they can prove to me that they're not a spambot
< VbzCart‎ | queries
Revision as of 21:17, 3 December 2008 by Woozle (talk | contribs) (New page: ==Details== * '''Returns''': data for items packaged for each order -- totals actually put into non-voided packages ("QtySent") and totals "handled" ("QtyDone" - sent ''or'' marked as n/a ...)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

Details

  • Returns: data for items packaged for each order -- totals actually put into non-voided packages ("QtySent") and totals "handled" ("QtyDone" - sent or marked as n/a or cancelled)
  • Requires:
  1. REDIRECT Template:l/vc/table, ord_pkgs
  • Used by:
  • History:
    • 2008-11-16 created for new restocking process
  • Fields:
    • QtyExtra quantities can't be included in the total for QtyDone or else freebies will cause problems (open order lines dues to "overfulfillment", plus they shouldn't count against ordered items)

SQL

<mysql>CREATE OR REPLACE VIEW qry_PkgItem_qtys_byOrder AS SELECT 

 COUNT(opl.ID) AS PkgCount,
 opl.ID_Item,
 opl.ID_OrdLine,
 op.ID_Order,
 SUM(IFNULL(QtyShipped,0)+IFNULL(QtyExtra,0)) AS QtySent,
 SUM(IFNULL(QtyShipped,0)+IFNULL(QtyKilled,0)+IFNULL(QtyNotAvail,0)) AS QtyDone

FROM ord_pkg_lines AS opl LEFT JOIN ord_pkgs AS op ON opl.ID_Pkg=op.ID WHERE (op.ID IS NOT NULL) AND (op.WhenVoided IS NULL) GROUP BY ID_Item, opl.ID_OrdLine, op.ID_Order ORDER BY ID_Order, ID_Item;</mysql>

Retrieved from "https://htyp.org/mw/index.php?title=VbzCart/queries/qry_PkgItem_qtys_byOrder&oldid=10914"