From 897704f5c109b5b15a0bde4efb73feddb5ca4976 Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Wed, 8 Sep 2021 11:59:25 -0400 Subject: [PATCH] Bug 28974: Add pagination to holds queue viewer We have a partner processing thousands of holds per day, per branch. At this number of holds, the script loads very slow if it manages to load at all. Paginating the results of the holds queue will allow this page more flexible and load more quickly. Test Plan 1) Generate a lot of holds in the holds queue 2) Apply this patch 3) Try out the new pagination bar 4) Ensure the limit and page features function correctly Signed-off-by: Emmi Takkinen --- C4/HoldsQueue.pm | 30 ++++++++++- circ/view_holdsqueue.pl | 51 +++++++++++++------ .../prog/en/modules/circ/view_holdsqueue.tt | 27 ++++++++++ t/db_dependent/HoldsQueue.t | 10 ++-- 4 files changed, 97 insertions(+), 21 deletions(-) diff --git a/C4/HoldsQueue.pm b/C4/HoldsQueue.pm index bc8df7060e..5a06ac5421 100644 --- a/C4/HoldsQueue.pm +++ b/C4/HoldsQueue.pm @@ -132,6 +132,11 @@ sub GetHoldsQueueItems { my $params = shift; my $dbh = C4::Context->dbh; + my $limit = $params->{limit} || 20; + my $page = $params->{page} || 1; + + my $offset = 0 + ( $limit * ( $page - 1 ) ); + my @bind_params = (); my $query = q/SELECT borrowers.surname, borrowers.othernames, @@ -169,6 +174,7 @@ sub GetHoldsQueueItems { push @bind_params, $params->{locationslimit}; } $query .= " ORDER BY ccode, location, cn_sort, author, title, pickbranch, reservedate"; + $query .= " LIMIT $limit OFFSET $offset"; my $sth = $dbh->prepare($query); $sth->execute(@bind_params); my $items = []; @@ -190,7 +196,29 @@ sub GetHoldsQueueItems { push @$items, $row; } - return $items; + + @bind_params = (); + my $total_query = "SELECT COUNT(*) FROM tmp_holdsqueue JOIN items USING (itemnumber) WHERE 1=1 "; + if ($params->{branchlimit}) { + $total_query .=" AND tmp_holdsqueue.holdingbranch = ?"; + push @bind_params, $params->{branchlimit}; + } + if( $params->{itemtypeslimit} ) { + $total_query .=" AND items.itype = ? "; + push @bind_params, $params->{itemtypeslimit}; + } + if( $params->{ccodeslimit} ) { + $total_query .=" AND items.ccode = ? "; + push @bind_params, $params->{ccodeslimit}; + } + if( $params->{locationslimit} ) { + $total_query .=" AND items.location = ? "; + push @bind_params, $params->{locationslimit}; + } + + my ($total_results) = $dbh->selectrow_array( $total_query, undef, @bind_params ); + + return ( $items, $total_results ); } =head2 CreateQueue diff --git a/circ/view_holdsqueue.pl b/circ/view_holdsqueue.pl index a126cf0baa..ba0a91e921 100755 --- a/circ/view_holdsqueue.pl +++ b/circ/view_holdsqueue.pl @@ -25,7 +25,7 @@ This script displays items in the tmp_holdsqueue table use Modern::Perl; use CGI qw ( -utf8 ); use C4::Auth qw( get_template_and_user ); -use C4::Output qw( output_html_with_http_headers ); +use C4::Output qw( output_html_with_http_headers pagination_bar ); use C4::HoldsQueue qw( GetHoldsQueueItems ); use Koha::BiblioFrameworks; use Koha::ItemTypes; @@ -44,25 +44,46 @@ my $params = $query->Vars; my $run_report = $params->{'run_report'}; my $branchlimit = $params->{'branchlimit'}; my $itemtypeslimit = $params->{'itemtypeslimit'}; -my $ccodeslimit = $params->{'ccodeslimit'}; +my $ccodeslimit = $params->{'ccodeslimit'}; my $locationslimit = $params->{'locationslimit'}; +my $limit = $params->{'limit'} || 20; +my $page = $params->{'page'} || 1; if ( $run_report ) { - my $items = GetHoldsQueueItems({ - branchlimit => $branchlimit, - itemtypeslimit => $itemtypeslimit, - ccodeslimit => $ccodeslimit, - locationslimit => $locationslimit - }); + my ( $items, $total ) = GetHoldsQueueItems( + { + branchlimit => $branchlimit, + itemtypeslimit => $itemtypeslimit, + ccodeslimit => $ccodeslimit, + locationslimit => $locationslimit, + limit => $limit, + page => $page, + } + ); + my $pages = int( $total / $limit ) + ( ( $total % $limit ) > 0 ? 1 : 0 ); $template->param( - branchlimit => $branchlimit, - itemtypeslimit => $itemtypeslimit, - ccodeslimit => $ccodeslimit, - locationslimit => $locationslimit, - total => scalar @$items, - itemsloop => $items, - run_report => $run_report, + branchlimit => $branchlimit, + itemtypeslimit => $itemtypeslimit, + ccodeslimit => $ccodeslimit, + locationslimit => $locationslimit, + total => scalar @$items, + itemsloop => $items, + run_report => $run_report, + page => $page, + limit => $limit, + pagination_bar => pagination_bar( + 'view_holdsqueue.pl', + $pages, $page, 'page', + { + branchlimit => $branchlimit, + itemtypeslimit => $itemtypeslimit, + ccodeslimit => $ccodeslimit, + locationslimit => $locationslimit, + limit => $limit, + run_report => 1, + } + ), ); } diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/circ/view_holdsqueue.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/circ/view_holdsqueue.tt index 0ca6163ef9..f8e2cb514c 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/circ/view_holdsqueue.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/circ/view_holdsqueue.tt @@ -69,6 +69,29 @@ [% IF ( ccodeslimit ) %] and collection code:([% AuthorisedValues.GetDescriptionByKohaField( kohafield => 'items.ccode' authorised_value = ccodeslimit ) | html %])[% END %] [% IF ( locationslimit ) %] and shelving location:([% AuthorisedValues.GetDescriptionByKohaField( kohafield => 'items.location' authorised_value = locationslimit ) | html %])[% END %] + +
+ + + + + + + + + +
+
[% pagination_bar | $raw %]
+ [% ELSE %]
No items found.
[% END %] @@ -278,6 +301,10 @@ $(document).ready(function() { var holdst; + $('#limit').change(function() { + $('#limitselect').submit(); + }); + // Setup filters before DataTables initialisation, in case some columns are // hidden by default var filterColumnTimeoutId; diff --git a/t/db_dependent/HoldsQueue.t b/t/db_dependent/HoldsQueue.t index ee1b7774c6..564bd6a0f9 100755 --- a/t/db_dependent/HoldsQueue.t +++ b/t/db_dependent/HoldsQueue.t @@ -163,7 +163,7 @@ $dbh->do("DELETE FROM items WHERE holdingbranch = '$borrower_branchcode'"); # Frst branch from StaticHoldsQueueWeight test_queue ('take from lowest cost branch', 0, $borrower_branchcode, $other_branches[0]); test_queue ('take from lowest cost branch', 1, $borrower_branchcode, $least_cost_branch_code); -my $queue = C4::HoldsQueue::GetHoldsQueueItems({ branchlmit => $least_cost_branch_code}) || []; +my ( $queue, $total ) = C4::HoldsQueue::GetHoldsQueueItems({ branchlmit => $least_cost_branch_code}); my $queue_item = $queue->[0]; ok( $queue_item && $queue_item->{pickbranch} eq $borrower_branchcode @@ -1785,16 +1785,16 @@ subtest "GetHoldsQueueItems" => sub { ($itemnumber_3,$biblionumber_3,'','','',42,'','') "); - my $queue_items = GetHoldsQueueItems(); + my ($queue_items) = GetHoldsQueueItems(); is( scalar @$queue_items, $count+3,'Three items added to queue'); - $queue_items = GetHoldsQueueItems({itemtypeslimit => $item_1->itype}); + ($queue_items) = GetHoldsQueueItems({itemtypeslimit => $item_1->itype}); is( scalar @$queue_items, 3,'Three items of same itemtype found when itemtypeslimit passed'); - $queue_items = GetHoldsQueueItems({itemtypeslimit => $item_1->itype, ccodeslimit => $item_2->ccode}); + ($queue_items) = GetHoldsQueueItems({itemtypeslimit => $item_1->itype, ccodeslimit => $item_2->ccode}); is( scalar @$queue_items, 2,'Two items of same collection found when ccodeslimit passed'); - @$queue_items = GetHoldsQueueItems({ + ($queue_items) = GetHoldsQueueItems({ itemtypeslimit => $item_1->itype, ccodeslimit => $item_2->ccode, locationslimit => $item_3->location -- 2.30.1 (Apple Git-130)