Attachment 125371 Details for Bug 28974 – Bug 28974: Add pagination to holds queue viewer

[patch] Bug 28974: Add pagination to holds queue viewer

Bug-28974-Add-pagination-to-holds-queue-viewer.patch (text/plain), 7.11 KB, created by Kyle M Hall (khall) on 2021-09-27 18:09:58 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Kyle M Hall (khall)

Created: 2021-09-27 18:09:58 UTC

Size: 7.11 KB

patch

obsolete

>From 29b55e74bdd9330ada17109c29dd210474e6b5d9 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] 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                       | 34 ++++++++++++++-----
> .../prog/en/modules/circ/view_holdsqueue.tt   | 27 +++++++++++++++
> t/db_dependent/HoldsQueue.t                   |  2 +-
> 4 files changed, 74 insertions(+), 11 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..1d9235aaec 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,34 @@ 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 );
>     $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,
>+                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 4a29d5b903..0600c829eb 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,29 @@
>         <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="itemtypeslimit" value="[% itemtypeslimit | html %]"/>
>+            <input type="hidden" name="ccodeslimit" value="[% ccodeslimit | html %]"/>
>+            <input type="hidden" name="locationslimit" value="[% locationslimit | 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> 
>+        <div class="pages">[% pagination_bar | $raw %]</div>
>+
>     [% ELSE %]
>         <div class="dialog message">No items found.</div>
>     [% END %]
>@@ -237,6 +260,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 5ab204ee12..db9f7a5c35 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($least_cost_branch_code) || [];
>+my ( $queue, $total ) = C4::HoldsQueue::GetHoldsQueueItems($least_cost_branch_code) || [];
> my $queue_item = $queue->[0];
> ok( $queue_item
>  && $queue_item->{pickbranch} eq $borrower_branchcode
>-- 
>2.30.1 (Apple Git-130)

Actions: View | Diff | Splinter Review

Attachments on bug 28974: 124660 | 124864 | 124874 | 124875 | 125291 | 125292 | 125295 | 125296 | 125297 | 125371 | 125372 | 125373 | 125399 | 125400 | 125401 | 125414 | 125415 | 125416 | 125424 | 125425 | 125426 | 126277 | 168355