From 1107f8e9e375c537614bfbae7ba8e10fb4417688 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Fri, 5 Dec 2014 17:30:02 +0100 Subject: [PATCH] Bug 13419: Add server-side processing for lists This patch adds DataTables using a server-side processing to display lists (virtual shelves). Test plan: 1/ Go on virtualshelves/shelves.p 2/ Play with the table functions (sort, filter, pagination, etc.) 3/ Go with the Public lists tab and verify the table is correctly filtered. --- C4/Utils/DataTables/VirtualShelves.pm | 194 ++++++++++++++++++++ C4/VirtualShelves/Page.pm | 9 +- .../prog/en/modules/virtualshelves/shelves.tt | 197 +++++++++------------ .../virtualshelves/tables/shelves_results.tt | 31 ++++ svc/virtualshelves/search | 88 +++++++++ 5 files changed, 402 insertions(+), 117 deletions(-) create mode 100644 C4/Utils/DataTables/VirtualShelves.pm create mode 100644 koha-tmpl/intranet-tmpl/prog/en/modules/virtualshelves/tables/shelves_results.tt create mode 100755 svc/virtualshelves/search diff --git a/C4/Utils/DataTables/VirtualShelves.pm b/C4/Utils/DataTables/VirtualShelves.pm new file mode 100644 index 0000000..56bb280 --- /dev/null +++ b/C4/Utils/DataTables/VirtualShelves.pm @@ -0,0 +1,194 @@ +package C4::Utils::DataTables::VirtualShelves; + +use Modern::Perl; +use C4::Branch qw/onlymine/; +use C4::Context; +use C4::Members qw/GetMemberIssuesAndFines/; +use C4::Utils::DataTables; + +sub search { + my ( $params ) = @_; + my $shelfname = $params->{shelfname}; + my $count = $params->{count}; + my $owner = $params->{owner}; + my $sortby = $params->{sortby}; + my $type = $params->{type}; + my $dt_params = $params->{dt_params}; + + # public is default + $type = 2 if not $type or $type != 1; + + # If not logged in user, be carreful and set the borrowernumber to 0 + # to prevent private lists lack + my $loggedinuser = C4::Context->userenv->{'number'} || 0; + + my ($iTotalRecords, $iTotalDisplayRecords); + + my $dbh = C4::Context->dbh; + + # FIXME refactore the following queries + # We should call C4::VirtualShelves::GetShelves and C4::VirtualShelves::GetAllShelves + # But the code is too dirty to refactor... + my $select = q| + SELECT vs.shelfnumber, vs.shelfname, vs.owner, vs.category AS type, + bo.surname, bo.firstname, vs.sortfield as sortby, + count(vc.biblionumber) as count + |; + + my $from_total = q| + FROM virtualshelves vs + LEFT JOIN borrowers bo ON vs.owner=bo.borrowernumber + |; + + my $from = $from_total . q| + LEFT JOIN virtualshelfcontents vc USING( shelfnumber ) + |; + + my @args; + # private + if ( $type == 1 ) { + my $join_vs .= q| + LEFT JOIN virtualshelfshares sh ON sh.shelfnumber = vs.shelfnumber + AND sh.borrowernumber = ? + |; + $from .= $join_vs; + $from_total .= $join_vs; + push @args, $loggedinuser; + + } + + my @where_strs; + + if ( defined $shelfname and $shelfname ne '' ) { + push @where_strs, 'shelfname LIKE ?'; + push @args, 'shelfname%'; + } + if ( defined $count and $count ne '' ) { + push @where_strs, 'count = ?'; + push @args, $count; + } + if ( defined $owner and $owner ne '' ) { + push @where_strs, 'owner LIKE ?'; + push @args, 'owner%'; + # FIXME search borronumber by name?? + # WHERE category=1 AND (vs.owner=? OR sh.borrowernumber=?); + } + if ( defined $sortby and $sortby ne '' ) { + push @where_strs, 'sortfield = ?'; + push @args, 'sortfield'; + } + + + push @where_strs, 'category = ?'; + push @args, $type; + + if ( $type == 1 ) { + push @where_strs, '(vs.owner = ? OR sh.borrowernumber = ?)'; + push @args, $loggedinuser, $loggedinuser; + } + + my $where; + $where = " WHERE " . join (" AND ", @where_strs) if @where_strs; + my $orderby = dt_build_orderby($dt_params); + $orderby =~ s|shelfnumber|vs.shelfnumber|; + + my $limit; + # If iDisplayLength == -1, we want to display all shelves + if ( $dt_params->{iDisplayLength} > -1 ) { + # In order to avoid sql injection + $dt_params->{iDisplayStart} =~ s/\D//g; + $dt_params->{iDisplayLength} =~ s/\D//g; + $dt_params->{iDisplayStart} //= 0; + $dt_params->{iDisplayLength} //= 20; + $limit = "LIMIT $dt_params->{iDisplayStart},$dt_params->{iDisplayLength}"; + } + + my $group_by = " GROUP BY vs.shelfnumber"; + + my $query = join( + " ", + $select, + $from, + ($where ? $where : ""), + $group_by, + ($orderby ? $orderby : ""), + ($limit ? $limit : "") + ); + my $sth = $dbh->prepare($query); + $sth->execute(@args); + + my $shelves = $sth->fetchall_arrayref({}); + + # Get the iTotalDisplayRecords DataTable variable + $query = "SELECT COUNT(vs.shelfnumber) " . $from_total . ($where ? $where : ""); + ($iTotalDisplayRecords) = $dbh->selectrow_array( $query, undef, @args ); + + # Get the iTotalRecords DataTable variable + $query = q|SELECT COUNT(vs.shelfnumber)| . $from_total . q| WHERE category = ?|; + $query .= q| AND (vs.owner = ? OR sh.borrowernumber = ?)| if $type == 1; + @args = $type == 1 ? ( $loggedinuser, $type, $loggedinuser, $loggedinuser ) : ( $type ); + ( $iTotalRecords ) = $dbh->selectrow_array( $query, undef, @args ); + + for my $shelf ( @$shelves ) { + $shelf->{can_manage_shelf} = C4::VirtualShelves::ShelfPossibleAction( $loggedinuser, $shelf->{shelfnumber}, 'manage' ); + } + return { + iTotalRecords => $iTotalRecords, + iTotalDisplayRecords => $iTotalDisplayRecords, + shelves => $shelves, + } +} + +1; +__END__ + +=head1 NAME + +C4::Utils::DataTables::VirtualShelves - module for using DataTables with virtual shelves + +=head1 SYNOPSIS + +This module provides routines used by the virtual shelves search + +=head2 FUNCTIONS + +=head3 search + + my $dt_infos = C4::Utils::DataTables::VirtualShelves->search($params); + +$params is a hashref with some keys: + +=over 4 + +=item shelfname + +=item count + +=item sortby + +=item type + +=item dt_params + +=cut + +=back + +=head1 LICENSE + +This file is part of Koha. + +Copyright 2014 BibLibre + +Koha is free software; you can redistribute it and/or modify it +under the terms of the GNU General Public License as published by +the Free Software Foundation; either version 3 of the License, or +(at your option) any later version. + +Koha is distributed in the hope that it will be useful, but +WITHOUT ANY WARRANTY; without even the implied warranty of +MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +GNU General Public License for more details. + +You should have received a copy of the GNU General Public License +along with Koha; if not, see . diff --git a/C4/VirtualShelves/Page.pm b/C4/VirtualShelves/Page.pm index 56518c2..e781d5f 100644 --- a/C4/VirtualShelves/Page.pm +++ b/C4/VirtualShelves/Page.pm @@ -472,11 +472,12 @@ sub shelfpage { "BiblioDefaultView" . C4::Context->preference("BiblioDefaultView") => 1, csv_profiles => GetCsvProfilesLoop('marc') ); - if ( $shelfnumber - or $shelves - or $edit ) { - $template->param( vseflag => 1 ); + + unless( $shelfnumber or $shelves or $edit ) { + # Only used for intranet + $template->param( op => 'list' ); } + if ($shelves or # note: this part looks duplicative, but is intentional $edit ) { diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/virtualshelves/shelves.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/virtualshelves/shelves.tt index 97e47a7..7460d1c 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/virtualshelves/shelves.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/virtualshelves/shelves.tt @@ -1,6 +1,8 @@ [% INCLUDE 'doc-head-open.inc' %] Koha › [% IF ( viewshelf ) %]Lists › Contents of [% shelfname | html %][% ELSE %]Lists[% END %][% IF ( shelves ) %] › Create new list[% END %][% IF ( edit ) %] › Edit list [% shelfname | html %][% END %] [% INCLUDE 'doc-head-close.inc' %] + +[% INCLUDE 'datatables.inc' %] [% IF ( viewshelf ) %] @@ -11,6 +13,66 @@ var MSG_NO_ITEM_SELECTED = _("Nothing is selected."); var MSG_REMOVE_FROM_LIST = _("Are you sure you want to remove these items from the list?"); var MSG_CONFIRM_DELETE_LIST = _("Are you sure you want to remove this list?"); + +[% IF op == 'list' %] +$(document).ready(function(){ + var type = 1; + var dtListResults = $("#listresultst").dataTable($.extend(true, {}, dataTablesDefaults, { + 'bServerSide': true, + 'sAjaxSource': "/cgi-bin/koha/svc/virtualshelves/search", + 'fnServerData': function(sSource, aoData, fnCallback) { + aoData.push({ + 'name': 'type', + 'value': type, + }, + { + 'name': 'template_path', + 'value': 'virtualshelves/tables/shelves_results.tt', + }); + $.ajax({ + 'dataType': 'json', + 'type': 'POST', + 'url': sSource, + 'data': aoData, + 'success': function(json){ + fnCallback(json); + } + }); + }, + 'aoColumns':[ + { 'mDataProp': 'dt_type' }, + { 'mDataProp': 'dt_shelfname' }, + { 'mDataProp': 'dt_count' }, + { 'mDataProp': 'dt_owner' }, + { 'mDataProp': 'dt_sortby' }, + { 'mDataProp': 'dt_action', 'bSortable': false } + ], + "aoColumnDefs": [ + { "bVisible": false, "aTargets": [ 'NoVisible' ] } + ], + 'bAutoWidth': false, + 'sPaginationType': 'full_numbers', + "bProcessing": true, + 'bFilter': false + })); + + dtListResults.fnAddFilters("filter", 750); + + var tabs = $("#tabs").tabs({ + activate: function(e, ui) { + var active = tabs.tabs("option", "active" ); + if ( active == 0 ) { + type = 1; // private + dtListResults.fnDraw(); + } else if ( active == 1 ) { + type = 2; // public + dtListResults.fnDraw(); + } + } + }); +}); +[% END %] + [% IF ( viewshelf ) %] $(document).ready(function(){ [% IF ( itemsloop ) %]$('#searchheader').fixFloat();[% END %] @@ -514,121 +576,30 @@ function placeHold () { [% END %] -[% UNLESS ( vseflag ) %] -

Lists

-
+[% IF op == 'list' %] +

Lists

+
- [% IF ( showprivateshelves ) %] -
- [% ELSE %] - - - [% IF ( showpublicshelves ) %] -
- [% ELSE %] - -
+ +
+ + + + + + + + + + + + +
TypeList nameContentsOwnerSort byActions
+
+
[% END %]
diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/virtualshelves/tables/shelves_results.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/virtualshelves/tables/shelves_results.tt new file mode 100644 index 0000000..6d4d730 --- /dev/null +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/virtualshelves/tables/shelves_results.tt @@ -0,0 +1,31 @@ +{ + "sEcho": [% sEcho %], + "iTotalRecords": [% iTotalRecords %], + "iTotalDisplayRecords": [% iTotalDisplayRecords %], + "aaData": [ + [% FOREACH data IN aaData %] + { + "dt_type": + "[% data.type %]", + "dt_shelfname": + "[% data.shelfname %]", + "dt_count": + "[% data.count %] item(s)", + "dt_owner": + "[% data.firstname %] [% data.surname %]", + "dt_sortby": + [% IF data.sortby == "author" %]"Author"[% ELSIF data.sortby == "copyrightdate" %]"Year"[% ELSIF data.sortby == "itemcallnumber" %]"Call number"[% ELSE %]"Title"[% END %], + "dt_action": + "[% PROCESS action_form shelfnumber=data.shelfnumber can_manage_shelf=data.can_manage_shelf %]" + }[% UNLESS loop.last %],[% END %] + [% END %] + ] +} + +[% BLOCK action_form -%] + [%- IF can_manage_shelf -%] +
+ [%- ELSE -%] + None + [%- END -%] +[%- END %] diff --git a/svc/virtualshelves/search b/svc/virtualshelves/search new file mode 100755 index 0000000..ef1448c --- /dev/null +++ b/svc/virtualshelves/search @@ -0,0 +1,88 @@ +#!/usr/bin/perl + +use Modern::Perl; +use CGI; + +use C4::Auth qw( get_template_and_user ); +use C4::Output qw( output_with_http_headers ); +use C4::Utils::DataTables qw( dt_get_params ); +use C4::Utils::DataTables::VirtualShelves qw( search ); + +my $input = new CGI; + +exit unless $input->param('template_path'); + +my ($template, $user, $cookie) = get_template_and_user({ + template_name => $input->param('template_path'), + query => $input, + type => "intranet", + authnotrequired => 0, + flagsrequired => { borrowers => 1 } +}); + +my $shelfname = $input->param('shelfname'); +my $count = $input->param('count'); +my $owner = $input->param('owner'); +my $type = $input->param('type'); +my $sortby = $input->param('sortby'); + +# variable information for DataTables (id) +my $sEcho = $input->param('sEcho'); + +my %dt_params = dt_get_params($input); +foreach (grep {$_ =~ /^mDataProp/} keys %dt_params) { + $dt_params{$_} =~ s/^dt_//; +} + +my $results = C4::Utils::DataTables::VirtualShelves::search( + { + shelfname => $shelfname, + count => $count, + owner => $owner, + type => $type, + sortby => $sortby, + dt_params => \%dt_params, + } +); + +$template->param( + sEcho => $sEcho, + iTotalRecords => $results->{iTotalRecords}, + iTotalDisplayRecords => $results->{iTotalDisplayRecords}, + aaData => $results->{shelves} +); + +output_with_http_headers $input, $cookie, $template->output, 'json'; + +__END__ + +=head1 NAME + +search - a search script for finding virtual shelves + +=head1 SYNOPSIS + +This script provides a service for template for virtual shelves search using DataTables + +=cut + +=back + +=head1 LICENSE + +Copyright 2014 BibLibre + +This file is part of Koha. + +Koha is free software; you can redistribute it and/or modify it under the +terms of the GNU General Public License as published by the Free Software +Foundation; either version 2 of the License, or (at your option) any later +version. + +Koha is distributed in the hope that it will be useful, but WITHOUT ANY +WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR +A PARTICULAR PURPOSE. See the GNU General Public License for more details. + +You should have received a copy of the GNU General Public License along +with Koha; if not, write to the Free Software Foundation, Inc., +51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. -- 2.1.0