1 package C4::Utils::DataTables::VirtualShelves;
5 use C4::Utils::DataTables qw( dt_build_orderby );
6 use Koha::Virtualshelves;
10 my $shelfname = $params->{shelfname};
11 my $count = $params->{count};
12 my $owner = $params->{owner};
13 my $sortby = $params->{sortby};
14 my $public = $params->{public} // 1;
15 $public = $public ? 1 : 0;
16 my $dt_params = $params->{dt_params};
18 # If not logged in user, be carreful and set the borrowernumber to 0
19 # to prevent private lists lack
20 my $loggedinuser = C4::Context->userenv->{'number'} || 0;
22 my ($iTotalRecords, $iTotalDisplayRecords);
24 my $dbh = C4::Context->dbh;
26 # FIXME refactore the following queries
27 # We should call Koha::Virtualshelves
29 SELECT vs.shelfnumber, vs.shelfname, vs.owner, vs.public AS public,
30 vs.created_on, vs.lastmodified as modification_time,
31 bo.surname, bo.firstname, vs.sortfield as sortby,
32 count(vc.biblionumber) as count
36 FROM virtualshelves vs
37 LEFT JOIN borrowers bo ON vs.owner=bo.borrowernumber
40 my $from = $from_total . q|
41 LEFT JOIN virtualshelfcontents vc USING( shelfnumber )
48 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber = vs.shelfnumber
49 AND sh.borrowernumber = ?
52 $from_total .= $join_vs;
53 push @args, $loggedinuser;
59 if ( defined $shelfname and $shelfname ne '' ) {
60 push @where_strs, 'shelfname LIKE ?';
61 push @args, "%$shelfname%";
63 if ( defined $owner and $owner ne '' ) {
64 push @where_strs, '( bo.firstname LIKE ? OR bo.surname LIKE ? )';
65 push @args, "%$owner%", "%$owner%";
67 if ( defined $sortby and $sortby ne '' ) {
68 push @where_strs, 'sortfield = ?';
72 push @where_strs, 'public = ?';
76 push @where_strs, '(vs.owner = ? OR sh.borrowernumber = ?)';
77 push @args, $loggedinuser, $loggedinuser;
81 $where = " WHERE " . join (" AND ", @where_strs) if @where_strs;
82 my $orderby = dt_build_orderby($dt_params);
83 $orderby =~ s|shelfnumber|vs.shelfnumber| if $orderby;
86 # If iDisplayLength == -1, we want to display all shelves
87 if ( $dt_params->{iDisplayLength} > -1 ) {
88 # In order to avoid sql injection
89 $dt_params->{iDisplayStart} =~ s/\D//g;
90 $dt_params->{iDisplayLength} =~ s/\D//g;
91 $dt_params->{iDisplayStart} //= 0;
92 $dt_params->{iDisplayLength} //= 20;
93 $limit = "LIMIT $dt_params->{iDisplayStart},$dt_params->{iDisplayLength}";
96 my $group_by = " GROUP BY vs.shelfnumber, vs.shelfname, vs.owner, vs.public,
97 vs.created_on, vs.lastmodified, bo.surname, bo.firstname, vs.sortfield ";
103 ($where ? $where : ""),
105 ($orderby ? $orderby : ""),
106 ($limit ? $limit : "")
108 my $shelves = $dbh->selectall_arrayref( $query, { Slice => {} }, @args );
110 # Get the iTotalDisplayRecords DataTable variable
111 $query = "SELECT COUNT(vs.shelfnumber) " . $from_total . ($where ? $where : "");
112 ($iTotalDisplayRecords) = $dbh->selectrow_array( $query, undef, @args );
114 # Get the iTotalRecords DataTable variable
115 $query = q|SELECT COUNT(vs.shelfnumber)| . $from_total . q| WHERE public = ?|;
116 $query .= q| AND (vs.owner = ? OR sh.borrowernumber = ?)| if !$public;
117 @args = !$public ? ( $loggedinuser, $public, $loggedinuser, $loggedinuser ) : ( $public );
118 ( $iTotalRecords ) = $dbh->selectrow_array( $query, undef, @args );
120 for my $shelf ( @$shelves ) {
121 my $s = Koha::Virtualshelves->find( $shelf->{shelfnumber} );
122 $shelf->{can_manage_shelf} = $s->can_be_managed( $loggedinuser );
123 $shelf->{can_delete_shelf} = $s->can_be_deleted( $loggedinuser );
124 $shelf->{is_shared} = $s->is_shared;
127 iTotalRecords => $iTotalRecords,
128 iTotalDisplayRecords => $iTotalDisplayRecords,
138 C4::Utils::DataTables::VirtualShelves - module for using DataTables with virtual shelves
142 This module provides routines used by the virtual shelves search
148 my $dt_infos = C4::Utils::DataTables::VirtualShelves->search($params);
150 $params is a hashref with some keys:
170 This file is part of Koha.
172 Copyright 2014 BibLibre
174 Koha is free software; you can redistribute it and/or modify it
175 under the terms of the GNU General Public License as published by
176 the Free Software Foundation; either version 3 of the License, or
177 (at your option) any later version.
179 Koha is distributed in the hope that it will be useful, but
180 WITHOUT ANY WARRANTY; without even the implied warranty of
181 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
182 GNU General Public License for more details.
184 You should have received a copy of the GNU General Public License
185 along with Koha; if not, see <http://www.gnu.org/licenses>.