C4/Utils/DataTables.pm

   1 package C4::Utils::DataTables;
   2
   3 # Copyright 2011 BibLibre
   4 #
   5 # This file is part of Koha.
   6 #
   7 # Koha is free software; you can redistribute it and/or modify it
   8 # under the terms of the GNU General Public License as published by
   9 # the Free Software Foundation; either version 3 of the License, or
  10 # (at your option) any later version.
  11 #
  12 # Koha is distributed in the hope that it will be useful, but
  13 # WITHOUT ANY WARRANTY; without even the implied warranty of
  14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  15 # GNU General Public License for more details.
  16 #
  17 # You should have received a copy of the GNU General Public License
  18 # along with Koha; if not, see <http://www.gnu.org/licenses>.
  19
  20 use Modern::Perl;
  21 require Exporter;
  22
  23 use vars qw(@ISA @EXPORT);
  24
  25 BEGIN {
  26
  27     @ISA        = qw(Exporter);
  28     @EXPORT     = qw(dt_build_orderby dt_build_having dt_get_params dt_build_query);
  29 }
  30
  31 =head1 NAME
  32
  33 C4::Utils::DataTables - Utility subs for building query when DataTables source is AJAX
  34
  35 =head1 SYNOPSYS
  36
  37     use CGI qw ( -utf8 );
  38     use C4::Context;
  39     use C4::Utils::DataTables;
  40
  41     my $input = new CGI;
  42     my $vars = $input->Vars;
  43
  44     my $query = qq{
  45         SELECT surname, firstname
  46         FROM borrowers
  47         WHERE borrowernumber = ?
  48     };
  49     my ($having, $having_params) = dt_build_having($vars);
  50     $query .= $having;
  51     $query .= dt_build_orderby($vars);
  52     $query .= " LIMIT ?,? ";
  53
  54     my $dbh = C4::Context->dbh;
  55     my $sth = $dbh->prepare($query);
  56     $sth->execute(
  57         $vars->{'borrowernumber'},
  58         @$having_params,
  59         $vars->{'iDisplayStart'},
  60         $vars->{'iDisplayLength'}
  61     );
  62     ...
  63
  64 =head1 DESCRIPTION
  65
  66     This module provide two utility functions to build a part of the SQL query,
  67     depending on DataTables parameters.
  68     One function build the 'ORDER BY' part, and the other the 'HAVING' part.
  69
  70 =head1 FUNCTIONS
  71
  72 =head2 dt_build_orderby
  73
  74     my $orderby = dt_build_orderby($dt_param);
  75     This function takes a reference to a hash containing DataTables parameters
  76     and build the corresponding 'ORDER BY' clause.
  77     This hash must contains the following keys:
  78         iSortCol_N, where N is a number from 0 to the number of columns to sort on minus 1
  79         sSortDir_N is the sorting order ('asc' or 'desc) for the corresponding column
  80         mDataProp_N is a mapping between the column index, and the name of a SQL field
  81
  82 =cut
  83
  84 sub dt_build_orderby {
  85     my $param = shift;
  86
  87     my $i = 0;
  88     my $orderby;
  89     my @orderbys;
  90     while(exists $param->{'iSortCol_'.$i}){
  91         my $iSortCol = $param->{'iSortCol_'.$i};
  92         my $sSortDir = $param->{'sSortDir_'.$i};
  93         my $mDataProp = $param->{'mDataProp_'.$iSortCol};
  94         my @sort_fields = $param->{$mDataProp.'_sorton'}
  95             ? split(' ', $param->{$mDataProp.'_sorton'})
  96             : ();
  97         if(@sort_fields > 0) {
  98             push @orderbys, "$_ $sSortDir" foreach (@sort_fields);
  99         } else {
 100             push @orderbys, "$mDataProp $sSortDir";
 101         }
 102         $i++;
 103     }
 104
 105     $orderby = " ORDER BY " . join(',', @orderbys) . " " if @orderbys;
 106     return $orderby;
 107 }
 108
 109 =head2 dt_build_having
 110
 111     my ($having, $having_params) = dt_build_having($dt_params)
 112
 113     This function takes a reference to a hash containing DataTables parameters
 114     and build the corresponding 'HAVING' clause.
 115     This hash must contains the following keys:
 116         sSearch is the text entered in the global filter
 117         iColumns is the number of columns
 118         bSearchable_N is a boolean value that is true if the column is searchable
 119         mDataProp_N is a mapping between the column index, and the name of a SQL field
 120         sSearch_N is the text entered in individual filter for column N
 121
 122 =cut
 123
 124 sub dt_build_having {
 125     my $param = shift;
 126
 127     my @filters;
 128     my @params;
 129
 130     # Global filter
 131     if($param->{'sSearch'}) {
 132         my $sSearch = $param->{'sSearch'};
 133         my $i = 0;
 134         my @gFilters;
 135         my @gParams;
 136         while($i < $param->{'iColumns'}) {
 137             if($param->{'bSearchable_'.$i} eq 'true') {
 138                 my $mDataProp = $param->{'mDataProp_'.$i};
 139                 my @filter_fields = $param->{$mDataProp.'_filteron'}
 140                     ? split(' ', $param->{$mDataProp.'_filteron'})
 141                     : ();
 142                 if(@filter_fields > 0) {
 143                     foreach my $field (@filter_fields) {
 144                         push @gFilters, " $field LIKE ? ";
 145                         push @gParams, "%$sSearch%";
 146                     }
 147                 } else {
 148                     push @gFilters, " $mDataProp LIKE ? ";
 149                     push @gParams, "%$sSearch%";
 150                 }
 151             }
 152             $i++;
 153         }
 154         push @filters, " (" . join(" OR ", @gFilters) . ") ";
 155         push @params, @gParams;
 156     }
 157
 158     # Individual filters
 159     my $i = 0;
 160     while($i < $param->{'iColumns'}) {
 161         my $sSearch = $param->{'sSearch_'.$i};
 162         if($sSearch) {
 163             my $mDataProp = $param->{'mDataProp_'.$i};
 164             my @filter_fields = $param->{$mDataProp.'_filteron'}
 165                 ? split(' ', $param->{$mDataProp.'_filteron'})
 166                 : ();
 167             if(@filter_fields > 0) {
 168                 my @localfilters;
 169                 foreach my $field (@filter_fields) {
 170                     push @localfilters, " $field LIKE ? ";
 171                     push @params, "%$sSearch%";
 172                 }
 173                 push @filters, " ( ". join(" OR ", @localfilters) ." ) ";
 174             } else {
 175                 push @filters, " $mDataProp LIKE ? ";
 176                 push @params, "%$sSearch%";
 177             }
 178         }
 179         $i++;
 180     }
 181
 182     return (\@filters, \@params);
 183 }
 184
 185 =head2 dt_get_params
 186
 187     my %dtparam = = dt_get_params( $input )
 188     This function takes a reference to a new CGI object.
 189     It prepares a hash containing Datatable parameters.
 190
 191 =cut
 192 sub dt_get_params {
 193     my $input = shift;
 194     my %dtparam;
 195     my $vars = $input->Vars;
 196
 197     foreach(qw/ iDisplayStart iDisplayLength iColumns sSearch bRegex iSortingCols sEcho /) {
 198         $dtparam{$_} = $input->param($_);
 199     }
 200     foreach(grep /(?:_sorton|_filteron)$/, keys %$vars) {
 201         $dtparam{$_} = $vars->{$_};
 202     }
 203     for(my $i=0; $i<$dtparam{'iColumns'}; $i++) {
 204         foreach(qw/ bSearchable sSearch bRegex bSortable iSortCol mDataProp sSortDir /) {
 205             my $key = $_ . '_' . $i;
 206             $dtparam{$key} = $input->param($key) if defined $input->param($key);
 207         }
 208     }
 209     return %dtparam;
 210 }
 211
 212 =head2 dt_build_query_simple
 213
 214     my ( $query, $params )= dt_build_query_simple( $value, $field )
 215
 216     This function takes a value and a field (table.field).
 217
 218     It returns (undef, []) if not $value.
 219     Else, returns a SQL where string and an arrayref containing parameters
 220     for the execute method of the statement.
 221
 222 =cut
 223 sub dt_build_query_simple {
 224     my ( $value, $field ) = @_;
 225     my $query;
 226     my @params;
 227     if( $value ) {
 228         $query .= " AND $field = ? ";
 229         push @params, $value;
 230     }
 231     return ( $query, \@params );
 232 }
 233
 234 =head2 dt_build_query_dates
 235
 236     my ( $query, $params )= dt_build_query_dates( $datefrom, $dateto, $field)
 237
 238     This function takes a datefrom, dateto and a field (table.field).
 239
 240     It returns (undef, []) if not $value.
 241     Else, returns a SQL where string and an arrayref containing parameters
 242     for the execute method of the statement.
 243
 244 =cut
 245 sub dt_build_query_dates {
 246     my ( $datefrom, $dateto, $field ) = @_;
 247     my $query;
 248     my @params;
 249     if ( $datefrom ) {
 250         $query .= " AND $field >= ? ";
 251         push @params, eval { output_pref( { dt => dt_from_string( $datefrom ), dateonly => 1, dateformat => 'iso' } ); };
 252     }
 253     if ( $dateto ) {
 254         $query .= " AND $field <= ? ";
 255         push @params, eval { output_pref( { dt => dt_from_string( $dateto ), dateonly => 1, dateformat => 'iso' } ); };
 256     }
 257     return ( $query, \@params );
 258 }
 259
 260 =head2 dt_build_query
 261
 262     my ( $query, $filter ) = dt_build_query( $type, @params )
 263
 264     This function takes a value and a list of parameters.
 265
 266     It calls dt_build_query_dates or dt_build_query_simple function of $type.
 267
 268     $type can contain 'simple' or 'range_dates'.
 269     if $type is not matched it returns undef
 270
 271 =cut
 272 sub dt_build_query {
 273     my ( $type, @params ) = @_;
 274     if ( $type =~ m/simple/ ) {
 275         return dt_build_query_simple(@params);
 276     }
 277     elsif ( $type =~ m/range_dates/ ) {
 278         return dt_build_query_dates(@params);
 279     }
 280     return;
 281 }
 282
 283 1;