Lines 2-8
package C4::Utils::DataTables::VirtualShelves;
Link Here
|
2 |
|
2 |
|
3 |
use Modern::Perl; |
3 |
use Modern::Perl; |
4 |
use C4::Context; |
4 |
use C4::Context; |
5 |
use C4::Utils::DataTables qw( dt_build_orderby ); |
|
|
6 |
use Koha::Virtualshelves; |
5 |
use Koha::Virtualshelves; |
7 |
|
6 |
|
8 |
sub search { |
7 |
sub search { |
Lines 13-25
sub search {
Link Here
|
13 |
my $sortby = $params->{sortby}; |
12 |
my $sortby = $params->{sortby}; |
14 |
my $public = $params->{public} // 1; |
13 |
my $public = $params->{public} // 1; |
15 |
$public = $public ? 1 : 0; |
14 |
$public = $public ? 1 : 0; |
16 |
my $dt_params = $params->{dt_params}; |
15 |
my $order_by = $params->{order_by}; |
|
|
16 |
my $start = $params->{start}; |
17 |
my $length = $params->{length}; |
17 |
|
18 |
|
18 |
# If not logged in user, be carreful and set the borrowernumber to 0 |
19 |
# If not logged in user, be carreful and set the borrowernumber to 0 |
19 |
# to prevent private lists lack |
20 |
# to prevent private lists lack |
20 |
my $loggedinuser = C4::Context->userenv->{'number'} || 0; |
21 |
my $loggedinuser = C4::Context->userenv->{'number'} || 0; |
21 |
|
22 |
|
22 |
my ($iTotalRecords, $iTotalDisplayRecords); |
23 |
my ($recordsTotal, $recordsFiltered); |
23 |
|
24 |
|
24 |
my $dbh = C4::Context->dbh; |
25 |
my $dbh = C4::Context->dbh; |
25 |
|
26 |
|
Lines 62-69
sub search {
Link Here
|
62 |
} |
63 |
} |
63 |
if ( defined $owner and $owner ne '' ) { |
64 |
if ( defined $owner and $owner ne '' ) { |
64 |
push @where_strs, '( bo.firstname LIKE ? OR bo.surname LIKE ? )'; |
65 |
push @where_strs, '( bo.firstname LIKE ? OR bo.surname LIKE ? )'; |
65 |
push @args, "%$owner%", "%$owner%"; |
66 |
push @args, "%$owner%", "%$owner%"; |
66 |
} |
67 |
} |
67 |
if ( defined $sortby and $sortby ne '' ) { |
68 |
if ( defined $sortby and $sortby ne '' ) { |
68 |
push @where_strs, 'sortfield = ?'; |
69 |
push @where_strs, 'sortfield = ?'; |
69 |
push @args, $sortby; |
70 |
push @args, $sortby; |
Lines 79-96
sub search {
Link Here
|
79 |
|
80 |
|
80 |
my $where; |
81 |
my $where; |
81 |
$where = " WHERE " . join (" AND ", @where_strs) if @where_strs; |
82 |
$where = " WHERE " . join (" AND ", @where_strs) if @where_strs; |
82 |
my $orderby = dt_build_orderby($dt_params); |
83 |
|
83 |
$orderby =~ s|shelfnumber|vs.shelfnumber| if $orderby; |
84 |
|
|
|
85 |
if ($order_by) { |
86 |
my @order_by; |
87 |
$order_by =~ s|shelfnumber|vs.shelfnumber|; |
88 |
my @sanitized_orderbys; |
89 |
for my $order ( split ',', $order_by ) { |
90 |
my ( $identifier, $direction ) = split / /, $order, 2; |
91 |
my ( $table, $column ) = split /\./, $identifier, 2; |
92 |
my $sanitized_identifier = $dbh->quote_identifier( undef, $table, $column ); |
93 |
my $sanitized_direction = $direction eq 'asc' ? 'ASC' : 'DESC'; |
94 |
push @sanitized_orderbys, "$sanitized_identifier $sanitized_direction"; |
95 |
} |
96 |
|
97 |
$order_by = ' ORDER BY ' . join( ',', @sanitized_orderbys ); |
98 |
} |
84 |
|
99 |
|
85 |
my $limit; |
100 |
my $limit; |
86 |
# If iDisplayLength == -1, we want to display all shelves |
101 |
# If length == -1, we want to display all shelves |
87 |
if ( $dt_params->{iDisplayLength} > -1 ) { |
102 |
if ( $length > -1 ) { |
88 |
# In order to avoid sql injection |
103 |
# In order to avoid sql injection |
89 |
$dt_params->{iDisplayStart} =~ s/\D//g; |
104 |
$start =~ s/\D//g; |
90 |
$dt_params->{iDisplayLength} =~ s/\D//g; |
105 |
$length =~ s/\D//g; |
91 |
$dt_params->{iDisplayStart} //= 0; |
106 |
$start //= 0; |
92 |
$dt_params->{iDisplayLength} //= 20; |
107 |
$length //= 20; |
93 |
$limit = "LIMIT $dt_params->{iDisplayStart},$dt_params->{iDisplayLength}"; |
108 |
$limit = sprintf "LIMIT %s,%s", $start, $length; |
94 |
} |
109 |
} |
95 |
|
110 |
|
96 |
my $group_by = " GROUP BY vs.shelfnumber, vs.shelfname, vs.owner, vs.public, |
111 |
my $group_by = " GROUP BY vs.shelfnumber, vs.shelfname, vs.owner, vs.public, |
Lines 102-121
sub search {
Link Here
|
102 |
$from, |
117 |
$from, |
103 |
($where ? $where : ""), |
118 |
($where ? $where : ""), |
104 |
$group_by, |
119 |
$group_by, |
105 |
($orderby ? $orderby : ""), |
120 |
($order_by ? $order_by : ""), |
106 |
($limit ? $limit : "") |
121 |
($limit ? $limit : "") |
107 |
); |
122 |
); |
108 |
my $shelves = $dbh->selectall_arrayref( $query, { Slice => {} }, @args ); |
123 |
my $shelves = $dbh->selectall_arrayref( $query, { Slice => {} }, @args ); |
109 |
|
124 |
|
110 |
# Get the iTotalDisplayRecords DataTable variable |
125 |
# Get the recordsFiltered DataTable variable |
111 |
$query = "SELECT COUNT(vs.shelfnumber) " . $from_total . ($where ? $where : ""); |
126 |
$query = "SELECT COUNT(vs.shelfnumber) " . $from_total . ($where ? $where : ""); |
112 |
($iTotalDisplayRecords) = $dbh->selectrow_array( $query, undef, @args ); |
127 |
($recordsFiltered) = $dbh->selectrow_array( $query, undef, @args ); |
113 |
|
128 |
|
114 |
# Get the iTotalRecords DataTable variable |
129 |
# Get the recordsTotal DataTable variable |
115 |
$query = q|SELECT COUNT(vs.shelfnumber)| . $from_total . q| WHERE public = ?|; |
130 |
$query = q|SELECT COUNT(vs.shelfnumber)| . $from_total . q| WHERE public = ?|; |
116 |
$query .= q| AND (vs.owner = ? OR sh.borrowernumber = ?)| if !$public; |
131 |
$query .= q| AND (vs.owner = ? OR sh.borrowernumber = ?)| if !$public; |
117 |
@args = !$public ? ( $loggedinuser, $public, $loggedinuser, $loggedinuser ) : ( $public ); |
132 |
@args = !$public ? ( $loggedinuser, $public, $loggedinuser, $loggedinuser ) : ( $public ); |
118 |
( $iTotalRecords ) = $dbh->selectrow_array( $query, undef, @args ); |
133 |
( $recordsTotal ) = $dbh->selectrow_array( $query, undef, @args ); |
119 |
|
134 |
|
120 |
for my $shelf ( @$shelves ) { |
135 |
for my $shelf ( @$shelves ) { |
121 |
my $s = Koha::Virtualshelves->find( $shelf->{shelfnumber} ); |
136 |
my $s = Koha::Virtualshelves->find( $shelf->{shelfnumber} ); |
Lines 124-131
sub search {
Link Here
|
124 |
$shelf->{is_shared} = $s->is_shared; |
139 |
$shelf->{is_shared} = $s->is_shared; |
125 |
} |
140 |
} |
126 |
return { |
141 |
return { |
127 |
iTotalRecords => $iTotalRecords, |
142 |
recordsTotal => $recordsTotal, |
128 |
iTotalDisplayRecords => $iTotalDisplayRecords, |
143 |
recordsFiltered => $recordsFiltered, |
129 |
shelves => $shelves, |
144 |
shelves => $shelves, |
130 |
} |
145 |
} |
131 |
} |
146 |
} |