From 246bbc0fb2d17985a8a418f75c90da93331a0a40 Mon Sep 17 00:00:00 2001
From: Kyle M Hall <kyle@bywatersolutions.com>
Date: Wed, 8 Sep 2021 11:59:25 -0400
Subject: [PATCH 1/2] 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 <emmi.takkinen@koha-suomi.fi>
---
 C4/HoldsQueue.pm                              | 22 ++++++++++++--
 circ/view_holdsqueue.pl                       | 29 ++++++++++++++-----
 .../prog/en/modules/circ/view_holdsqueue.tt   | 24 +++++++++++++++
 3 files changed, 65 insertions(+), 10 deletions(-)

diff --git a/C4/HoldsQueue.pm b/C4/HoldsQueue.pm
index 13c6f8e2ab..404ad69ce8 100644
--- a/C4/HoldsQueue.pm
+++ b/C4/HoldsQueue.pm
@@ -129,7 +129,13 @@ Returns hold queue for a holding branch. If branch is omitted, then whole queue
 =cut
 
 sub GetHoldsQueueItems {
-    my ($branchlimit) = @_;
+    my ($branchlimit, $limit, $page) = @_;
+
+    $limit ||= 20;
+    $page  ||= 1;
+
+    my $offset = 0 + ( $limit * ( $page - 1 ) );
+
     my $dbh   = C4::Context->dbh;
 
     my @bind_params = ();
@@ -156,6 +162,7 @@ sub GetHoldsQueueItems {
         push @bind_params, $branchlimit;
     }
     $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 = [];
@@ -177,7 +184,18 @@ sub GetHoldsQueueItems {
 
         push @$items, $row;
     }
-    return $items;
+
+
+    @bind_params = ();
+    my $total_query = "SELECT COUNT(*) FROM tmp_holdsqueue";
+    if ($branchlimit) {
+        $total_query .=" WHERE tmp_holdsqueue.holdingbranch = ?";
+        push @bind_params, $branchlimit;
+    }
+
+    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 71650371ca..bd7f3b6886 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;
@@ -43,16 +43,29 @@ my ( $template, $loggedinuser, $cookie, $flags ) = get_template_and_user(
 my $params = $query->Vars;
 my $run_report     = $params->{'run_report'};
 my $branchlimit    = $params->{'branchlimit'};
-my $itemtypeslimit = $params->{'itemtypeslimit'};
+my $limit          = $params->{'limit'} || 20;
+my $page           = $params->{'page'}  || 1;
 
 if ( $run_report ) {
-    # XXX GetHoldsQueueItems() does not support $itemtypeslimit!
-    my $items = GetHoldsQueueItems($branchlimit, $itemtypeslimit);
+    my ( $items, $total ) = GetHoldsQueueItems( $branchlimit, $limit, $page );
+
+    my $pages = int( $total / $limit ) + ( ( $total % $limit ) > 0 ? 1 : 0 );
+    warn "LIMIT: $limit";
+    warn "PAGES: $pages";
     $template->param(
-        branchlimit     => $branchlimit,
-        total      => scalar @$items,
-        itemsloop  => $items,
-        run_report => $run_report,
+        branchlimit    => $branchlimit,
+        total          => $total,
+        itemsloop      => $items,
+        run_report     => $run_report,
+        page           => $page,
+        limit          => $limit,
+        pagination_bar => pagination_bar(
+            'view_holdsqueue.pl',
+            $pages,
+            $page,
+            'page',
+            { branchlimit => $branchlimit, 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 4a29d5b903..e7f6742163 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
@@ -66,6 +66,26 @@
         <div class="results">[% total | html %] items found for
             [% IF ( branchlimit ) %][% Branches.GetName( branchlimit ) | html %][% ELSE %]All libraries[% END %]
         </div>
+
+		<form action="view_holdsqueue.pl" method="get" id="limitselect">
+			<input type="hidden" name="page" value="[% page | html %]"/>
+            <input type="hidden" name="branchlimit" value="[% branchlimit | html %]"/>
+			<input type="hidden" name="run_report" value="1"/>
+
+			<label for="limit">Rows per page: </label>
+			<select name="limit" id="limit">
+				[% limits = [ 10, 20, 50, 100, 200, 300, 400, 500, 1000 ] %]
+				[% FOREACH l IN limits %]
+					[% IF l == limit %]
+						<option value="[% l | html %]" selected="selected">[% l | html %]</option>
+					[% ELSE %]
+						<option value="[% l | html %]">[% l | html %]</option>
+					[% END %]
+				[% END %]
+			</select>
+		</form> <!-- /#limitselect -->
+        <div class="pages">[% pagination_bar | $raw %]</div>
+
     [% ELSE %]
         <div class="dialog message">No items found.</div>
     [% END %]
@@ -237,6 +257,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;
-- 
2.25.1