From a267c38a3f05e9ec9c6848f0e7b643fe529b946e Mon Sep 17 00:00:00 2001 From: Nick Clemens Date: Fri, 22 Dec 2023 13:35:06 +0000 Subject: [PATCH] Bug 35641: Reduce DB lookups when sending a list of barcodes to inventory This patch does three things: 1 - Removes a specific query for withdrawn status of each item scanned - we can use the withdrawn field 2 - Removes a specific query for checkouts on each item scanned - we can use the onloan field a - additionally we don't need to fetch the checkout as we check it in to the homebranch, this is likely incorrect - we should use the current branch, but I preserve behavior for now 3 - Fetches the items ahead of time and builds a hash based on barcode, reduces DB lookups, may raise memory usage To test: 1 - Checkout some items 2 - Withdraw some items 3 - Generate a lsit of barcodes including some checked out items and some withdrawn items 4 - Enter that list of barcodes into inventory tool 5 - Note your results 6 - Apply patch 7 - Issue the items again 8 - Repeat inventory 9 - Confirm results are the same as before patch Signed-off-by: Andrew Fuerste-Henry Signed-off-by: Jonathan Druart Signed-off-by: Katrin Fischer (cherry picked from commit 5ae5e4367b96cd5685c40197f391ca3c4063c407) Signed-off-by: Fridolin Somers (cherry picked from commit 41580a0a589b96f7edeb8ec24f7b2664508e16ae) Signed-off-by: Lucas Gass --- tools/inventory.pl | 52 +++++++++++++++++++++------------------------- 1 file changed, 24 insertions(+), 28 deletions(-) diff --git a/tools/inventory.pl b/tools/inventory.pl index ca52b8faba..af684ffae6 100755 --- a/tools/inventory.pl +++ b/tools/inventory.pl @@ -167,11 +167,6 @@ if ( ($uploadbarcodes && length($uploadbarcodes) > 0) || ($barcodelist && length my $date = $input->param('setdate'); my $date_dt = dt_from_string($date); - my $strsth = "select * from issues, items where items.itemnumber=issues.itemnumber and items.barcode =?"; - my $qonloan = $dbh->prepare($strsth); - $strsth="select * from items where items.barcode =? and items.withdrawn = 1"; - my $qwithdrawn = $dbh->prepare($strsth); - my @barcodes; my @uploadedbarcodes; @@ -218,33 +213,34 @@ if ( ($uploadbarcodes && length($uploadbarcodes) > 0) || ($barcodelist && length $template->param( err_length => $err_length, err_data => $err_data ); } + my @items = Koha::Items->search( { barcode => { -in => \@barcodes } } )->as_list; + my %items = map { $_->barcode => $_ } @items; foreach my $barcode (@barcodes) { - if ( $qwithdrawn->execute($barcode) && $qwithdrawn->rows ) { - push @errorloop, { 'barcode' => $barcode, 'ERR_WTHDRAWN' => 1 }; - } else { - my $item = Koha::Items->find({barcode => $barcode}); - if ( $item ) { - # Modify date last seen for scanned items, remove lost status - $item->set({ itemlost => 0, datelastseen => $date_dt })->store; - my $item_unblessed = $item->unblessed; - $moddatecount++; - unless ( $dont_checkin ) { - $qonloan->execute($barcode); - if ($qonloan->rows){ - my $data = $qonloan->fetchrow_hashref; - my ($doreturn, $messages, $iteminformation, $borrower) =AddReturn($barcode, $data->{homebranch}); - if( $doreturn ) { - $item_unblessed->{onloan} = undef; - $item_unblessed->{datelastseen} = dt_from_string; - } else { - push @errorloop, { barcode => $barcode, ERR_ONLOAN_NOT_RET => 1 }; - } + my $item = $items{ $barcode }; + if ( $item ) { + if ( $item->withdrawn ) { + push @errorloop, { 'barcode' => $barcode, 'ERR_WTHDRAWN' => 1 }; + next; + } + # Modify date last seen for scanned items, remove lost status + $item->set({ itemlost => 0, datelastseen => $date_dt })->store; + my $item_unblessed = $item->unblessed; + $moddatecount++; + unless ( $dont_checkin ) { + if ( $item->onloan ){ + #FIXME Is this correct? Shouldn't the item be checked in at the branch we are signed in at? + my ($doreturn, $messages, $iteminformation, $borrower) =AddReturn($barcode, $item->homebranch); + if( $doreturn ) { + $item_unblessed->{onloan} = undef; + $item_unblessed->{datelastseen} = dt_from_string; + } else { + push @errorloop, { barcode => $barcode, ERR_ONLOAN_NOT_RET => 1 }; } } - push @scanned_items, $item_unblessed; - } else { - push @errorloop, { barcode => $barcode, ERR_BARCODE => 1 }; } + push @scanned_items, $item_unblessed; + } else { + push @errorloop, { barcode => $barcode, ERR_BARCODE => 1 }; } } $template->param( date => $date ); -- 2.39.5