From 328684de9564097ac967ce66d66eda79f20ffa53 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Thu, 30 Jul 2020 14:16:09 +0200 Subject: [PATCH] Bug 26090: Fix "item types" report This report was broken with SQL strict mode, but also without. DBD::mysql::st execute failed: 'koha_kohadev.itemtypes.itemtype' isn't in GROUP BY [for Statement " and, if no library was selected: DBD::mysql::st execute failed: Unknown column 'biblioitems.itemtype' in 'field list' [for Statement " This patch does the minimum to make it usable, but it would need more love. Test plan: To test you will need to try the 4 combinaisons: item-level_itypes = item or biblio With or without a library selected in the dropdown list Signed-off-by: Owen Leonard Signed-off-by: Martin Renvoize --- reports/itemtypes.plugin | 72 ++++++++++++++-------------------------- 1 file changed, 24 insertions(+), 48 deletions(-) diff --git a/reports/itemtypes.plugin b/reports/itemtypes.plugin index 6884369ce2..ec2c535182 100755 --- a/reports/itemtypes.plugin +++ b/reports/itemtypes.plugin @@ -40,57 +40,33 @@ sub calculate { my $branch = @$parameters[0]; my $dbh = C4::Context->dbh; my $sth; - if ($branch) { - if (C4::Context->preference('item-level_itypes')) { - $sth = $dbh->prepare(" - SELECT itemtype, description, items.itype as itemtype, COUNT(*) AS total - FROM itemtypes,items - WHERE items.itype=itemtypes.itemtype - AND items.holdingbranch=? - GROUP BY items.itype - ORDER BY itemtypes.description"); - - } - else { - $sth = $dbh->prepare(" - SELECT biblioitems.itemtype, description, biblioitems.itemtype, COUNT(*) AS total - FROM itemtypes, biblioitems, items - WHERE biblioitems.itemtype=itemtypes.itemtype - AND items.biblioitemnumber=biblioitems.biblioitemnumber - AND items.holdingbranch=? - GROUP BY biblioitems.itemtype - ORDER BY itemtypes.description"); - } - $sth->execute($branch); - } else { - if (C4::Context->preference('item-level_itypes')) { - $sth = $dbh->prepare(" - SELECT biblioitems.itemtype, description,items.itype AS itemtype, COUNT(*) AS total - FROM itemtypes,items - WHERE items.itype=itemtypes.itemtype - GROUP BY items.itype - ORDER BY itemtypes.description"); - } - else { - $sth = $dbh->prepare("SELECT itemtype, description, biblioitems.itemtype, COUNT(*) AS total - FROM itemtypes, biblioitems,items - WHERE biblioitems.itemtype=itemtypes.itemtype - AND biblioitems.biblioitemnumber = items.biblioitemnumber - GROUP BY biblioitems.itemtype - ORDER BY itemtypes.description"); - } - $sth->execute; - } - my ($itemtype, $description,$biblioitems,$total); + if ( C4::Context->preference('item-level_itypes') ) { + $sth = $dbh->prepare( q| + SELECT itemtypes.itemtype, description, COUNT(*) AS total + FROM itemtypes, items + WHERE items.itype=itemtypes.itemtype + | . ( $branch ? q| AND items.holdingbranch=? | : () ) . q| + GROUP BY itemtypes.itemtype, description, items.itype + ORDER BY itemtypes.description + |); + } + else { + $sth = $dbh->prepare( q| + SELECT itemtypes.itemtype, description, COUNT(*) AS total + FROM itemtypes, biblioitems, items + WHERE biblioitems.itemtype=itemtypes.itemtype + AND items.biblioitemnumber=biblioitems.biblioitemnumber + | . ( $branch ? q| AND items.holdingbranch=? | : () ) . q| + GROUP BY itemtypes.itemtype, description + ORDER BY itemtypes.description + |); + } + $sth->execute($branch || ()); + my ($itemtype, $description,$total); my $grantotal = 0; my $count = 0; - while (($itemtype, $description,$biblioitems,$total) = $sth->fetchrow) { + while (($itemtype, $description,$total) = $sth->fetchrow) { my %line; - if($count % 2){ - $line{toggle} = 1; - } else { - $line{toggle} = 0; - } $line{itemtype} = $itemtype; $line{count} = $total; $grantotal += $total; -- 2.20.1