1 package C4::Utils::DataTables::VirtualShelves;
5 use Koha::Virtualshelves;
9 my $shelfname = $params->{shelfname};
10 my $count = $params->{count};
11 my $owner = $params->{owner};
12 my $sortby = $params->{sortby};
13 my $public = $params->{public} // 1;
14 $public = $public ? 1 : 0;
15 my $order_by = $params->{order_by};
16 my $start = $params->{start};
17 my $length = $params->{length};
19 # If not logged in user, be carreful and set the borrowernumber to 0
20 # to prevent private lists lack
21 my $loggedinuser = C4::Context->userenv->{'number'} || 0;
23 my ($recordsTotal, $recordsFiltered);
25 my $dbh = C4::Context->dbh;
27 # FIXME refactore the following queries
28 # We should call Koha::Virtualshelves
30 SELECT vs.shelfnumber, vs.shelfname, vs.owner, vs.public AS public,
31 vs.created_on, vs.lastmodified as modification_time,
32 bo.surname, bo.firstname, vs.sortfield as sortby,
33 count(vc.biblionumber) as count
37 FROM virtualshelves vs
38 LEFT JOIN borrowers bo ON vs.owner=bo.borrowernumber
41 my $from = $from_total . q|
42 LEFT JOIN virtualshelfcontents vc USING( shelfnumber )
49 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber = vs.shelfnumber
50 AND sh.borrowernumber = ?
53 $from_total .= $join_vs;
54 push @args, $loggedinuser;
60 if ( defined $shelfname and $shelfname ne '' ) {
61 push @where_strs, 'shelfname LIKE ?';
62 push @args, "%$shelfname%";
64 if ( defined $owner and $owner ne '' ) {
65 push @where_strs, '( bo.firstname LIKE ? OR bo.surname LIKE ? )';
66 push @args, "%$owner%", "%$owner%";
68 if ( defined $sortby and $sortby ne '' ) {
69 push @where_strs, 'sortfield = ?';
73 push @where_strs, 'public = ?';
77 push @where_strs, '(vs.owner = ? OR sh.borrowernumber = ?)';
78 push @args, $loggedinuser, $loggedinuser;
82 $where = " WHERE " . join (" AND ", @where_strs) if @where_strs;
86 $order_by =~ s|shelfnumber|vs.shelfnumber|;
87 my @sanitized_orderbys;
88 for my $order ( split ',', $order_by ) {
89 my ( $identifier, $direction ) = split / /, $order, 2;
90 my ( $table, $column ) = split /\./, $identifier, 2;
91 my $sanitized_identifier = $dbh->quote_identifier( undef, $table, $column );
92 my $sanitized_direction = $direction eq 'asc' ? 'ASC' : 'DESC';
93 push @sanitized_orderbys, "$sanitized_identifier $sanitized_direction";
96 $order_by = ' ORDER BY ' . join( ',', @sanitized_orderbys );
100 # If length == -1, we want to display all shelves
101 if ( $length > -1 ) {
102 # In order to avoid sql injection
107 $limit = sprintf "LIMIT %s,%s", $start, $length;
110 my $group_by = " GROUP BY vs.shelfnumber, vs.shelfname, vs.owner, vs.public,
111 vs.created_on, vs.lastmodified, bo.surname, bo.firstname, vs.sortfield ";
117 ($where ? $where : ""),
119 ($order_by ? $order_by : ""),
120 ($limit ? $limit : "")
122 my $shelves = $dbh->selectall_arrayref( $query, { Slice => {} }, @args );
124 # Get the recordsFiltered DataTable variable
125 $query = "SELECT COUNT(vs.shelfnumber) " . $from_total . ($where ? $where : "");
126 ($recordsFiltered) = $dbh->selectrow_array( $query, undef, @args );
128 # Get the recordsTotal DataTable variable
129 $query = q|SELECT COUNT(vs.shelfnumber)| . $from_total . q| WHERE public = ?|;
130 $query .= q| AND (vs.owner = ? OR sh.borrowernumber = ?)| if !$public;
131 @args = !$public ? ( $loggedinuser, $public, $loggedinuser, $loggedinuser ) : ( $public );
132 ( $recordsTotal ) = $dbh->selectrow_array( $query, undef, @args );
134 for my $shelf ( @$shelves ) {
135 my $s = Koha::Virtualshelves->find( $shelf->{shelfnumber} );
136 $shelf->{can_manage_shelf} = $s->can_be_managed( $loggedinuser );
137 $shelf->{can_delete_shelf} = $s->can_be_deleted( $loggedinuser );
138 $shelf->{is_shared} = $s->is_shared;
141 recordsTotal => $recordsTotal,
142 recordsFiltered => $recordsFiltered,
152 C4::Utils::DataTables::VirtualShelves - module for using DataTables with virtual shelves
156 This module provides routines used by the virtual shelves search
162 my $dt_infos = C4::Utils::DataTables::VirtualShelves->search($params);
164 $params is a hashref with some keys:
184 This file is part of Koha.
186 Copyright 2014 BibLibre
188 Koha is free software; you can redistribute it and/or modify it
189 under the terms of the GNU General Public License as published by
190 the Free Software Foundation; either version 3 of the License, or
191 (at your option) any later version.
193 Koha is distributed in the hope that it will be useful, but
194 WITHOUT ANY WARRANTY; without even the implied warranty of
195 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
196 GNU General Public License for more details.
198 You should have received a copy of the GNU General Public License
199 along with Koha; if not, see <http://www.gnu.org/licenses>.