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
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.
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.
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>.
23 use vars qw(@ISA @EXPORT);
28 @EXPORT = qw(dt_build_orderby dt_get_params);
33 C4::Utils::DataTables - Utility subs for building query when DataTables source is AJAX
39 use C4::Utils::DataTables;
42 my $vars = $input->Vars;
45 SELECT surname, firstname
47 WHERE borrowernumber = ?
49 $query .= dt_build_orderby($vars);
50 $query .= " LIMIT ?,? ";
52 my $dbh = C4::Context->dbh;
53 my $sth = $dbh->prepare($query);
55 $vars->{'borrowernumber'},
57 $vars->{'iDisplayStart'},
58 $vars->{'iDisplayLength'}
64 This module provide two utility functions to build a part of the SQL query,
65 depending on DataTables parameters.
66 One function build the 'ORDER BY' part, and the other the 'HAVING' part.
70 =head2 dt_build_orderby
72 my $orderby = dt_build_orderby($dt_param);
73 This function takes a reference to a hash containing DataTables parameters
74 and build the corresponding 'ORDER BY' clause.
75 This hash must contains the following keys:
76 iSortCol_N, where N is a number from 0 to the number of columns to sort on minus 1
77 sSortDir_N is the sorting order ('asc' or 'desc) for the corresponding column
78 mDataProp_N is a mapping between the column index, and the name of a SQL field
82 sub dt_build_orderby {
88 while(exists $param->{'iSortCol_'.$i}){
89 my $iSortCol = $param->{'iSortCol_'.$i};
90 my $sSortDir = $param->{'sSortDir_'.$i};
91 my $mDataProp = $param->{'mDataProp_'.$iSortCol};
92 my @sort_fields = $param->{$mDataProp.'_sorton'}
93 ? split(' ', $param->{$mDataProp.'_sorton'})
95 if(@sort_fields > 0) {
96 push @orderbys, "$_ $sSortDir" foreach (@sort_fields);
98 push @orderbys, "$mDataProp $sSortDir";
103 $orderby = " ORDER BY " . join(',', @orderbys) . " " if @orderbys;
109 my %dtparam = = dt_get_params( $input )
110 This function takes a reference to a new CGI object.
111 It prepares a hash containing Datatable parameters.
117 my $vars = $input->Vars;
119 foreach(qw/ iDisplayStart iDisplayLength iColumns sSearch bRegex iSortingCols sEcho /) {
120 $dtparam{$_} = $input->param($_);
122 foreach(grep /(?:_sorton|_filteron)$/, keys %$vars) {
123 $dtparam{$_} = $vars->{$_};
125 for(my $i=0; $i<$dtparam{'iColumns'}; $i++) {
126 foreach(qw/ bSearchable sSearch bRegex bSortable iSortCol mDataProp sSortDir /) {
127 my $key = $_ . '_' . $i;
128 $dtparam{$key} = $input->param($key) if defined $input->param($key);