VbzCart/docs/queries/qry PkgItem qtys byOrder

From Woozle Writes Code
< VbzCart‎ | docs‎ | queries
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: Template:Vbzcart/table, Template:Vbzcart/table
  • 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

<section begin=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> <section end=sql />

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