VbzCart/docs/queries/qryPkgs status: Difference between revisions
Jump to navigation
Jump to search
imported>Woozle (New page: ==About== * '''Requires''': {{vbzcart|table|ord_pkg_lines}}, {{vbzcart|table|ord_pkgs}}, {{vbzcart|table|ord_shipmt}}, {{vbzcart|table|ord_lines}}, {{vbzcart|table|core_orders}} * '''Group...) |
m (Woozle moved page VbzCart/VbzCart/queries/qryPkgs status to VbzCart/docs/queries/qryPkgs status without leaving a redirect: part 2) |
(No difference)
| |
Latest revision as of 01:55, 25 February 2024
About
- Requires: Template:Vbzcart, Template:Vbzcart, Template:Vbzcart, Template:Vbzcart, Template:Vbzcart
- Grouped by: Package
- Used by: VbzAdmin shipment detail display
- History:
- 2009-07-09 Adapted from MS Access version
SQL
<section begin=sql /><mysql>CREATE OR REPLACE VIEW qryPkgs_status AS SELECT
p.ID_Order,
p.ID AS ID_Package,
CONCAT("(",s.ID,") ",IF(s.Abbr="",s.WhenShipped,s.Abbr)) AS Shipment,
p.ID_Shipment,
CONCAT(o.Number,"-",p.Seq) AS PackageName,
p.WhenStarted,
p.WhenFinished,
p.ShipCost,
p.PkgCost,
s.WhenShipped,
Sum(oi.QtyOrd) AS Ordered,
Sum(pl.QtyShipped) AS Shipped,
Sum(pl.QtyNotAvail) AS Deleted,
Sum(pl.QtyKilled) AS Cancelled,
Sum(oi.QtyOrd-IFNULL(pl.QtyShipped,0)-IFNULL(pl.QtyNotAvail,0)-IFNULL(pl.QtyKilled,0)) AS Remaining,
WhenChecked
FROM
(
(
(ord_pkg_lines AS pl RIGHT JOIN ord_pkgs AS p ON pl.ID_Pkg = p.ID)
LEFT JOIN ord_shipmt AS s ON p.ID_Shipment = s.ID)
LEFT JOIN ord_lines AS oi ON pl.ID_OrdLine=oi.ID)
LEFT JOIN core_orders AS o ON p.ID_Order=o.ID
GROUP BY p.ID, p.ID_Order, p.ID_Shipment, p.ShipCost, p.PkgCost, s.Abbr, o.Number, p.Seq, p.WhenStarted, p.WhenFinished, s.WhenShipped, s.ID, WhenChecked ORDER BY o.Number, p.Seq;</mysql> <section end=sql />