C4/Utils/DataTables/VirtualShelves.pm

   1 package C4::Utils::DataTables::VirtualShelves;
   2
   3 use Modern::Perl;
   4 use C4::Context;
   5 use C4::Utils::DataTables;
   6 use Koha::Virtualshelves;
   7
   8 sub search {
   9     my ( $params ) = @_;
  10     my $shelfname = $params->{shelfname};
  11     my $count = $params->{count};
  12     my $owner = $params->{owner};
  13     my $sortby = $params->{sortby};
  14     my $type = $params->{type};
  15     my $dt_params = $params->{dt_params};
  16
  17     # public is default
  18     $type = 2 if not $type or $type != 1;
  19
  20     # If not logged in user, be carreful and set the borrowernumber to 0
  21     # to prevent private lists lack
  22     my $loggedinuser = C4::Context->userenv->{'number'} || 0;
  23
  24     my ($iTotalRecords, $iTotalDisplayRecords);
  25
  26     my $dbh = C4::Context->dbh;
  27
  28     # FIXME refactore the following queries
  29     # We should call Koha::Virtualshelves
  30     my $select = q|
  31         SELECT vs.shelfnumber, vs.shelfname, vs.owner, vs.category AS type,
  32         vs.created_on, vs.lastmodified as modification_time,
  33         bo.surname, bo.firstname, vs.sortfield as sortby,
  34         count(vc.biblionumber) as count
  35     |;
  36
  37     my $from_total = q|
  38         FROM virtualshelves vs
  39         LEFT JOIN borrowers bo ON vs.owner=bo.borrowernumber
  40     |;
  41
  42     my $from = $from_total . q|
  43         LEFT JOIN virtualshelfcontents vc USING( shelfnumber )
  44     |;
  45
  46     my @args;
  47     # private
  48     if ( $type == 1 ) {
  49         my $join_vs .= q|
  50             LEFT JOIN virtualshelfshares sh ON sh.shelfnumber = vs.shelfnumber
  51             AND sh.borrowernumber = ?
  52         |;
  53         $from .= $join_vs;
  54         $from_total .= $join_vs;
  55         push @args, $loggedinuser;
  56
  57     }
  58
  59     my @where_strs;
  60
  61     if ( defined $shelfname and $shelfname ne '' ) {
  62         push @where_strs, 'shelfname LIKE ?';
  63         push @args, "%$shelfname%";
  64     }
  65     if ( defined $owner and $owner ne '' ) {
  66         push @where_strs, '( bo.firstname LIKE ? OR bo.surname LIKE ? )';
  67         push @args, "%$owner%", "%$owner%";
  68     }
  69     if ( defined $sortby and $sortby ne '' ) {
  70         push @where_strs, 'sortfield = ?';
  71         push @args, $sortby;
  72     }
  73
  74     push @where_strs, 'category = ?';
  75     push @args, $type;
  76
  77     if ( $type == 1 ) {
  78         push @where_strs, '(vs.owner = ? OR sh.borrowernumber = ?)';
  79         push @args, $loggedinuser, $loggedinuser;
  80     }
  81
  82     my $where;
  83     $where = " WHERE " . join (" AND ", @where_strs) if @where_strs;
  84     my $orderby = dt_build_orderby($dt_params);
  85     $orderby =~ s|shelfnumber|vs.shelfnumber| if $orderby;
  86
  87     my $limit;
  88     # If iDisplayLength == -1, we want to display all shelves
  89     if ( $dt_params->{iDisplayLength} > -1 ) {
  90         # In order to avoid sql injection
  91         $dt_params->{iDisplayStart} =~ s/\D//g;
  92         $dt_params->{iDisplayLength} =~ s/\D//g;
  93         $dt_params->{iDisplayStart} //= 0;
  94         $dt_params->{iDisplayLength} //= 20;
  95         $limit = "LIMIT $dt_params->{iDisplayStart},$dt_params->{iDisplayLength}";
  96     }
  97
  98     my $group_by = " GROUP BY vs.shelfnumber, vs.shelfname, vs.owner, vs.category,
  99         vs.created_on, vs.lastmodified, bo.surname, bo.firstname, vs.sortfield ";
 100
 101     my $query = join(
 102         " ",
 103         $select,
 104         $from,
 105         ($where ? $where : ""),
 106         $group_by,
 107         ($orderby ? $orderby : ""),
 108         ($limit ? $limit : "")
 109     );
 110     my $shelves = $dbh->selectall_arrayref( $query, { Slice => {} }, @args );
 111
 112     # Get the iTotalDisplayRecords DataTable variable
 113     $query = "SELECT COUNT(vs.shelfnumber) " . $from_total . ($where ? $where : "");
 114     ($iTotalDisplayRecords) = $dbh->selectrow_array( $query, undef, @args );
 115
 116     # Get the iTotalRecords DataTable variable
 117     $query = q|SELECT COUNT(vs.shelfnumber)| . $from_total . q| WHERE category = ?|;
 118     $query .= q| AND (vs.owner = ? OR sh.borrowernumber = ?)| if $type == 1;
 119     @args = $type == 1 ? ( $loggedinuser, $type, $loggedinuser, $loggedinuser ) : ( $type );
 120     ( $iTotalRecords ) = $dbh->selectrow_array( $query, undef, @args );
 121
 122     for my $shelf ( @$shelves ) {
 123         my $s = Koha::Virtualshelves->find( $shelf->{shelfnumber} );
 124         $shelf->{can_manage_shelf} = $s->can_be_managed( $loggedinuser );
 125         $shelf->{can_delete_shelf} = $s->can_be_deleted( $loggedinuser );
 126         $shelf->{is_shared} = $s->is_shared;
 127     }
 128     return {
 129         iTotalRecords => $iTotalRecords,
 130         iTotalDisplayRecords => $iTotalDisplayRecords,
 131         shelves => $shelves,
 132     }
 133 }
 134
 135 1;
 136 __END__
 137
 138 =head1 NAME
 139
 140 C4::Utils::DataTables::VirtualShelves - module for using DataTables with virtual shelves
 141
 142 =head1 SYNOPSIS
 143
 144 This module provides routines used by the virtual shelves search
 145
 146 =head2 FUNCTIONS
 147
 148 =head3 search
 149
 150     my $dt_infos = C4::Utils::DataTables::VirtualShelves->search($params);
 151
 152 $params is a hashref with some keys:
 153
 154 =over 4
 155
 156 =item shelfname
 157
 158 =item count
 159
 160 =item sortby
 161
 162 =item type
 163
 164 =item dt_params
 165
 166 =cut
 167
 168 =back
 169
 170 =head1 LICENSE
 171
 172 This file is part of Koha.
 173
 174 Copyright 2014 BibLibre
 175
 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.
 180
 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.
 185
 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>.