1 package C4::Utils::DataTables;
3 # Copyright 2011 BibLibre
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along
17 # with Koha; if not, write to the Free Software Foundation, Inc.,
18 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
23 use vars qw($VERSION @ISA @EXPORT);
29 @EXPORT = qw(dt_build_orderby dt_build_having dt_get_params dt_build_query);
34 C4::Utils::DataTables - Utility subs for building query when DataTables source is AJAX
40 use C4::Utils::DataTables;
43 my $vars = $input->Vars;
46 SELECT surname, firstname
48 WHERE borrowernumber = ?
50 my ($having, $having_params) = dt_build_having($vars);
52 $query .= dt_build_orderby($vars);
53 $query .= " LIMIT ?,? ";
55 my $dbh = C4::Context->dbh;
56 my $sth = $dbh->prepare($query);
58 $vars->{'borrowernumber'},
60 $vars->{'iDisplayStart'},
61 $vars->{'iDisplayLength'}
67 This module provide two utility functions to build a part of the SQL query,
68 depending on DataTables parameters.
69 One function build the 'ORDER BY' part, and the other the 'HAVING' part.
75 =item dt_build_orderby
77 my $orderby = dt_build_orderby($dt_param);
79 This function takes a reference to a hash containing DataTables parameters
80 and build the corresponding 'ORDER BY' clause.
81 This hash must contains the following keys:
83 iSortCol_N, where N is a number from 0 to the number of columns to sort on minus 1
85 sSortDir_N is the sorting order ('asc' or 'desc) for the corresponding column
87 mDataProp_N is a mapping between the column index, and the name of a SQL field
91 sub dt_build_orderby {
97 while(exists $param->{'iSortCol_'.$i}){
98 my $iSortCol = $param->{'iSortCol_'.$i};
99 my $sSortDir = $param->{'sSortDir_'.$i};
100 my $mDataProp = $param->{'mDataProp_'.$iSortCol};
101 my @sort_fields = $param->{$mDataProp.'_sorton'}
102 ? split(' ', $param->{$mDataProp.'_sorton'})
104 if(@sort_fields > 0) {
105 push @orderbys, "$_ $sSortDir" foreach (@sort_fields);
107 push @orderbys, "$mDataProp $sSortDir";
112 $orderby = " ORDER BY " . join(',', @orderbys) . " " if @orderbys;
116 =item dt_build_having
118 my ($having, $having_params) = dt_build_having($dt_params)
120 This function takes a reference to a hash containing DataTables parameters
121 and build the corresponding 'HAVING' clause.
122 This hash must contains the following keys:
124 sSearch is the text entered in the global filter
126 iColumns is the number of columns
128 bSearchable_N is a boolean value that is true if the column is searchable
130 mDataProp_N is a mapping between the column index, and the name of a SQL field
132 sSearch_N is the text entered in individual filter for column N
138 sub dt_build_having {
145 if($param->{'sSearch'}) {
146 my $sSearch = $param->{'sSearch'};
150 while($i < $param->{'iColumns'}) {
151 if($param->{'bSearchable_'.$i} eq 'true') {
152 my $mDataProp = $param->{'mDataProp_'.$i};
153 my @filter_fields = $param->{$mDataProp.'_filteron'}
154 ? split(' ', $param->{$mDataProp.'_filteron'})
156 if(@filter_fields > 0) {
157 foreach my $field (@filter_fields) {
158 push @gFilters, " $field LIKE ? ";
159 push @gParams, "%$sSearch%";
162 push @gFilters, " $mDataProp LIKE ? ";
163 push @gParams, "%$sSearch%";
168 push @filters, " (" . join(" OR ", @gFilters) . ") ";
169 push @params, @gParams;
174 while($i < $param->{'iColumns'}) {
175 my $sSearch = $param->{'sSearch_'.$i};
177 my $mDataProp = $param->{'mDataProp_'.$i};
178 my @filter_fields = $param->{$mDataProp.'_filteron'}
179 ? split(' ', $param->{$mDataProp.'_filteron'})
181 if(@filter_fields > 0) {
183 foreach my $field (@filter_fields) {
184 push @localfilters, " $field LIKE ? ";
185 push @params, "%$sSearch%";
187 push @filters, " ( ". join(" OR ", @localfilters) ." ) ";
189 push @filters, " $mDataProp LIKE ? ";
190 push @params, "%$sSearch%";
196 return (\@filters, \@params);
201 my %dtparam = = dt_get_params( $input )
203 This function takes a reference to a new CGI object.
205 It prepares a hash containing Datatable parameters.
213 my $vars = $input->Vars;
215 foreach(qw/ iDisplayStart iDisplayLength iColumns sSearch bRegex iSortingCols sEcho /) {
216 $dtparam{$_} = $input->param($_);
218 foreach(grep /(?:_sorton|_filteron)$/, keys %$vars) {
219 $dtparam{$_} = $vars->{$_};
221 for(my $i=0; $i<$dtparam{'iColumns'}; $i++) {
222 foreach(qw/ bSearchable sSearch bRegex bSortable iSortCol mDataProp sSortDir /) {
223 my $key = $_ . '_' . $i;
224 $dtparam{$key} = $input->param($key) if defined $input->param($key);
230 =item dt_build_query_simple
232 my ( $query, $params )= dt_build_query_simple( $value, $field )
234 This function takes a value and a field (table.field).
236 It returns (undef, []) if not $value.
237 Else, returns a SQL where string and an arrayref containing parameters
238 for the execute method of the statement.
243 sub dt_build_query_simple {
244 my ( $value, $field ) = @_;
248 $query .= " AND $field = ? ";
249 push @params, $value;
251 return ( $query, \@params );
254 =item dt_build_query_dates
256 my ( $query, $params )= dt_build_query_dates( $datefrom, $dateto, $field)
258 This function takes a datefrom, dateto and a field (table.field).
260 It returns (undef, []) if not $value.
261 Else, returns a SQL where string and an arrayref containing parameters
262 for the execute method of the statement.
267 sub dt_build_query_dates {
268 my ( $datefrom, $dateto, $field ) = @_;
272 $query .= " AND $field >= ? ";
273 push @params, C4::Dates->new($datefrom)->output('iso');
276 $query .= " AND $field <= ? ";
277 push @params, C4::Dates->new($dateto)->output('iso');
279 return ( $query, \@params );
284 my ( $query, $filter ) = dt_build_query( $type, @params )
286 This function takes a value and a list of parameters.
288 It calls dt_build_query_dates or dt_build_query_simple fonction of $type.
290 $type can be 'simple' or 'rage_dates'.
296 my ( $type, @params ) = @_;
299 return dt_build_query_simple( @params );
301 when ( /range_dates/ ) {
302 return dt_build_query_dates( @params );