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 return unless @orderbys;
105 # Must be "branches.branchname asc", "borrowers.firstname desc", etc.
106 @orderbys = grep { /^\w+\.\w+\s(asc|desc)$/ } @orderbys;
108 $orderby = " ORDER BY " . join(',', @orderbys) . " " if @orderbys;
114 my %dtparam = = dt_get_params( $input )
115 This function takes a reference to a new CGI object.
116 It prepares a hash containing Datatable parameters.
122 my $vars = $input->Vars;
124 foreach(qw/ iDisplayStart iDisplayLength iColumns sSearch bRegex iSortingCols sEcho /) {
125 $dtparam{$_} = $input->param($_);
127 foreach(grep /(?:_sorton|_filteron)$/, keys %$vars) {
128 $dtparam{$_} = $vars->{$_};
130 for(my $i=0; $i<$dtparam{'iColumns'}; $i++) {
131 foreach(qw/ bSearchable sSearch bRegex bSortable iSortCol mDataProp sSortDir /) {
132 my $key = $_ . '_' . $i;
133 $dtparam{$key} = $input->param($key) if defined $input->param($key);