1 package C4::Utils::DataTables::VirtualShelves;
4 use C4::Branch qw/onlymine/;
6 use C4::Members qw/GetMemberIssuesAndFines/;
7 use C4::Utils::DataTables;
8 use C4::VirtualShelves;
12 my $shelfname = $params->{shelfname};
13 my $count = $params->{count};
14 my $owner = $params->{owner};
15 my $sortby = $params->{sortby};
16 my $type = $params->{type};
17 my $dt_params = $params->{dt_params};
20 $type = 2 if not $type or $type != 1;
22 # If not logged in user, be carreful and set the borrowernumber to 0
23 # to prevent private lists lack
24 my $loggedinuser = C4::Context->userenv->{'number'} || 0;
26 my ($iTotalRecords, $iTotalDisplayRecords);
28 my $dbh = C4::Context->dbh;
30 # FIXME refactore the following queries
31 # We should call C4::VirtualShelves::GetShelves and C4::VirtualShelves::GetAllShelves
32 # But the code is too dirty to refactor...
34 SELECT vs.shelfnumber, vs.shelfname, vs.owner, vs.category AS type,
35 vs.creation_time, vs.lastmodified as modification_time,
36 bo.surname, bo.firstname, vs.sortfield as sortby,
37 count(vc.biblionumber) as count
41 FROM virtualshelves vs
42 LEFT JOIN borrowers bo ON vs.owner=bo.borrowernumber
45 my $from = $from_total . q|
46 LEFT JOIN virtualshelfcontents vc USING( shelfnumber )
53 LEFT JOIN virtualshelfshares sh ON sh.shelfnumber = vs.shelfnumber
54 AND sh.borrowernumber = ?
57 $from_total .= $join_vs;
58 push @args, $loggedinuser;
64 if ( defined $shelfname and $shelfname ne '' ) {
65 push @where_strs, 'shelfname LIKE ?';
66 push @args, "%$shelfname%";
68 if ( defined $owner and $owner ne '' ) {
69 push @where_strs, '( bo.firstname LIKE ? OR bo.surname LIKE ? )';
70 push @args, "%$owner%", "%$owner%";
72 if ( defined $sortby and $sortby ne '' ) {
73 push @where_strs, 'sortfield = ?';
77 push @where_strs, 'category = ?';
81 push @where_strs, '(vs.owner = ? OR sh.borrowernumber = ?)';
82 push @args, $loggedinuser, $loggedinuser;
86 $where = " WHERE " . join (" AND ", @where_strs) if @where_strs;
87 my $orderby = dt_build_orderby($dt_params);
88 $orderby =~ s|shelfnumber|vs.shelfnumber| if $orderby;
91 # If iDisplayLength == -1, we want to display all shelves
92 if ( $dt_params->{iDisplayLength} > -1 ) {
93 # In order to avoid sql injection
94 $dt_params->{iDisplayStart} =~ s/\D//g;
95 $dt_params->{iDisplayLength} =~ s/\D//g;
96 $dt_params->{iDisplayStart} //= 0;
97 $dt_params->{iDisplayLength} //= 20;
98 $limit = "LIMIT $dt_params->{iDisplayStart},$dt_params->{iDisplayLength}";
101 my $group_by = " GROUP BY vs.shelfnumber";
107 ($where ? $where : ""),
109 ($orderby ? $orderby : ""),
110 ($limit ? $limit : "")
112 my $shelves = $dbh->selectall_arrayref( $query, { Slice => {} }, @args );
114 # Get the iTotalDisplayRecords DataTable variable
115 $query = "SELECT COUNT(vs.shelfnumber) " . $from_total . ($where ? $where : "");
116 ($iTotalDisplayRecords) = $dbh->selectrow_array( $query, undef, @args );
118 # Get the iTotalRecords DataTable variable
119 $query = q|SELECT COUNT(vs.shelfnumber)| . $from_total . q| WHERE category = ?|;
120 $query .= q| AND (vs.owner = ? OR sh.borrowernumber = ?)| if $type == 1;
121 @args = $type == 1 ? ( $loggedinuser, $type, $loggedinuser, $loggedinuser ) : ( $type );
122 ( $iTotalRecords ) = $dbh->selectrow_array( $query, undef, @args );
124 for my $shelf ( @$shelves ) {
125 $shelf->{can_manage_shelf} = C4::VirtualShelves::ShelfPossibleAction( $loggedinuser, $shelf->{shelfnumber}, 'manage' );
126 $shelf->{can_delete_shelf} = C4::VirtualShelves::ShelfPossibleAction( $loggedinuser, $shelf->{shelfnumber}, 'delete_shelf' );
129 iTotalRecords => $iTotalRecords,
130 iTotalDisplayRecords => $iTotalDisplayRecords,
140 C4::Utils::DataTables::VirtualShelves - module for using DataTables with virtual shelves
144 This module provides routines used by the virtual shelves search
150 my $dt_infos = C4::Utils::DataTables::VirtualShelves->search($params);
152 $params is a hashref with some keys:
172 This file is part of Koha.
174 Copyright 2014 BibLibre
176 Koha is free software; you can redistribute it and/or modify it
177 under the terms of the GNU General Public License as published by
178 the Free Software Foundation; either version 3 of the License, or
179 (at your option) any later version.
181 Koha is distributed in the hope that it will be useful, but
182 WITHOUT ANY WARRANTY; without even the implied warranty of
183 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
184 GNU General Public License for more details.
186 You should have received a copy of the GNU General Public License
187 along with Koha; if not, see <http://www.gnu.org/licenses>.