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 ! DEPRECATED - This module is deprecated, the REST API route and REST API Datatables wrapper must be used instead!
35 C4::Utils::DataTables - Utility subs for building query when DataTables source is AJAX
41 use C4::Utils::DataTables;
44 my $vars = $input->Vars;
47 SELECT surname, firstname
49 WHERE borrowernumber = ?
51 $query .= dt_build_orderby($vars);
52 $query .= " LIMIT ?,? ";
54 my $dbh = C4::Context->dbh;
55 my $sth = $dbh->prepare($query);
57 $vars->{'borrowernumber'},
59 $vars->{'iDisplayStart'},
60 $vars->{'iDisplayLength'}
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.
72 =head2 dt_build_orderby
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
84 sub dt_build_orderby {
89 my $dbh = C4::Context->dbh;
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'})
97 if(@sort_fields > 0) {
98 push @orderbys, "$_ $sSortDir" foreach (@sort_fields);
100 push @orderbys, "$mDataProp $sSortDir";
105 return unless @orderbys;
107 # Must be "branches.branchname asc", "borrowers.firstname desc", etc.
108 @orderbys = grep { /^\w+\.\w+ (asc|desc)$/ } @orderbys;
110 my @sanitized_orderbys;
111 for my $orderby (@orderbys) {
112 my ($identifier, $direction) = split / /, $orderby, 2;
113 my ($table, $column) = split /\./, $identifier, 2;
114 my $sanitized_identifier = $dbh->quote_identifier(undef, $table, $column);
115 my $sanitized_direction = $direction eq 'asc' ? 'ASC' : 'DESC';
116 push @sanitized_orderbys, "$sanitized_identifier $sanitized_direction";
119 my $orderby = " ORDER BY " . join(',', @sanitized_orderbys) . " " if @sanitized_orderbys;
125 my %dtparam = = dt_get_params( $input )
126 This function takes a reference to a new CGI object.
127 It prepares a hash containing Datatable parameters.
133 my $vars = $input->Vars;
135 foreach(qw/ iDisplayStart iDisplayLength iColumns sSearch bRegex iSortingCols sEcho /) {
136 $dtparam{$_} = $input->param($_);
138 foreach(grep /(?:_sorton|_filteron)$/, keys %$vars) {
139 $dtparam{$_} = $vars->{$_};
141 for(my $i=0; $i<$dtparam{'iColumns'}; $i++) {
142 foreach(qw/ bSearchable sSearch bRegex bSortable iSortCol mDataProp sSortDir /) {
143 my $key = $_ . '_' . $i;
144 $dtparam{$key} = $input->param($key) if defined $input->param($key);