From 5851d5b46959bb4a2e2492952fd5ebcd03e08d38 Mon Sep 17 00:00:00 2001 From: root Date: Fri, 1 Feb 2019 14:11:23 +0000 Subject: [PATCH] Bug 22260: Incorrect GROUP BY in spent and ordered pages in Acquisitions To test: 1 - Have some budgets and orders 2 - Visit acqui/acqui-home.pl 3 - looks fine 4 - click on either the spent or ordered column for a fund 5 - Internal server error 6 - Apply patch (restart all the things) 7 - Both links now work 8 - Verify totals are correct --- acqui/ordered.pl | 10 +++++----- acqui/spent.pl | 10 +++++----- 2 files changed, 10 insertions(+), 10 deletions(-) diff --git a/acqui/ordered.pl b/acqui/ordered.pl index 75291d9f5c..68a2e64b32 100755 --- a/acqui/ordered.pl +++ b/acqui/ordered.pl @@ -62,10 +62,11 @@ SELECT FROM (aqorders, aqbasket) LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber -LEFT JOIN aqorders_items ON - aqorders.ordernumber=aqorders_items.ordernumber -LEFT JOIN items ON - items.itemnumber=aqorders_items.itemnumber +LEFT JOIN + (SELECT ordernumber, GROUP_CONCAT(DISTINCT itype) as itype + FROM aqorders_items + LEFT JOIN items USING (itemnumber) + GROUP BY ordernumber) aqitems USING (ordernumber) LEFT JOIN aqbooksellers ON aqbasket.booksellerid = aqbooksellers.id WHERE @@ -74,7 +75,6 @@ WHERE (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') AND (quantity > quantityreceived OR quantityreceived IS NULL) - GROUP BY aqorders.ordernumber EOQ my $sth = $dbh->prepare($query); diff --git a/acqui/spent.pl b/acqui/spent.pl index 69a25379f9..bd4a8a0234 100755 --- a/acqui/spent.pl +++ b/acqui/spent.pl @@ -69,10 +69,11 @@ SELECT FROM (aqorders, aqbasket) LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber -LEFT JOIN aqorders_items ON - aqorders.ordernumber = aqorders_items.ordernumber -LEFT JOIN items ON - aqorders_items.itemnumber = items.itemnumber +LEFT JOIN + (SELECT ordernumber, GROUP_CONCAT(DISTINCT itype) as itype + FROM aqorders_items + LEFT JOIN items USING (itemnumber) + GROUP BY ordernumber) aqitems USING (ordernumber) LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid LEFT JOIN aqbooksellers ON @@ -83,7 +84,6 @@ WHERE (datecancellationprinted IS NULL OR datecancellationprinted='0000-00-00') AND datereceived IS NOT NULL - GROUP BY aqorders.ordernumber EOQ my $sth = $dbh->prepare($query); $sth->execute($bookfund); -- 2.11.0