File Coverage

File:C4/Utils/DataTables.pm
Coverage:3.0%

linestmtbrancondsubtimecode
1package 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 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
10# version.
11#
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.
15#
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.
19
20
2
1
1
2363
1242
8
use Modern::Perl;
21require Exporter;
22
23
0
0
0
0
0
0
use vars qw($VERSION @ISA @EXPORT);
24
25BEGIN {
26
0
0
    $VERSION = 3.04,
27
28    @ISA = qw(Exporter);
29
0
0
    @EXPORT = qw(dt_build_orderby dt_build_having dt_get_params dt_build_query);
30}
31
32 - 83
=head1 NAME

C4::Utils::DataTables - Utility subs for building query when DataTables source is AJAX

=head1 SYNOPSYS

    use CGI;
    use C4::Context;
    use C4::Utils::DataTables;

    my $input = new CGI;
    my $vars = $input->Vars;

    my $query = qq{
        SELECT surname, firstname
        FROM borrowers
        WHERE borrowernumber = ?
    };
    my ($having, $having_params) = dt_build_having($vars);
    $query .= $having;
    $query .= dt_build_orderby($vars);
    $query .= " LIMIT ?,? ";

    my $dbh = C4::Context->dbh;
    my $sth = $dbh->prepare($query);
    $sth->execute(
        $vars->{'borrowernumber'},
        @$having_params,
        $vars->{'iDisplayStart'},
        $vars->{'iDisplayLength'}
    );
    ...

=head1 DESCRIPTION

    This module provide two utility functions to build a part of the SQL query,
    depending on DataTables parameters.
    One function build the 'ORDER BY' part, and the other the 'HAVING' part.

=head1 FUNCTIONS

=head2 dt_build_orderby

    my $orderby = dt_build_orderby($dt_param);
    This function takes a reference to a hash containing DataTables parameters
    and build the corresponding 'ORDER BY' clause.
    This hash must contains the following keys:
        iSortCol_N, where N is a number from 0 to the number of columns to sort on minus 1
        sSortDir_N is the sorting order ('asc' or 'desc) for the corresponding column
        mDataProp_N is a mapping between the column index, and the name of a SQL field

=cut
84
85sub dt_build_orderby {
86
0
0
    my $param = shift;
87
88
0
0
    my $i = 0;
89
0
0
    my $orderby;
90
0
0
    my @orderbys;
91
0
0
    while(exists $param->{'iSortCol_'.$i}){
92
0
0
        my $iSortCol = $param->{'iSortCol_'.$i};
93
0
0
        my $sSortDir = $param->{'sSortDir_'.$i};
94
0
0
        my $mDataProp = $param->{'mDataProp_'.$iSortCol};
95
0
0
        my @sort_fields = $param->{$mDataProp.'_sorton'}
96            ? split(' ', $param->{$mDataProp.'_sorton'})
97            : ();
98
0
0
        if(@sort_fields > 0) {
99
0
0
0
0
            push @orderbys, "$_ $sSortDir" foreach (@sort_fields);
100        } else {
101
0
0
            push @orderbys, "$mDataProp $sSortDir";
102        }
103
0
0
        $i++;
104    }
105
106
0
0
    $orderby = " ORDER BY " . join(',', @orderbys) . " " if @orderbys;
107
0
0
    return $orderby;
108}
109
110 - 123
=head2 dt_build_having

    my ($having, $having_params) = dt_build_having($dt_params)

    This function takes a reference to a hash containing DataTables parameters
    and build the corresponding 'HAVING' clause.
    This hash must contains the following keys:
        sSearch is the text entered in the global filter
        iColumns is the number of columns
        bSearchable_N is a boolean value that is true if the column is searchable
        mDataProp_N is a mapping between the column index, and the name of a SQL field
        sSearch_N is the text entered in individual filter for column N

=cut
124
125sub dt_build_having {
126
0
0
    my $param = shift;
127
128
0
0
    my @filters;
129
0
0
    my @params;
130
131    # Global filter
132
0
0
    if($param->{'sSearch'}) {
133
0
0
        my $sSearch = $param->{'sSearch'};
134
0
0
        my $i = 0;
135
0
0
        my @gFilters;
136
0
0
        my @gParams;
137
0
0
        while($i < $param->{'iColumns'}) {
138
0
0
            if($param->{'bSearchable_'.$i} eq 'true') {
139
0
0
                my $mDataProp = $param->{'mDataProp_'.$i};
140
0
0
                my @filter_fields = $param->{$mDataProp.'_filteron'}
141                    ? split(' ', $param->{$mDataProp.'_filteron'})
142                    : ();
143
0
0
                if(@filter_fields > 0) {
144
0
0
                    foreach my $field (@filter_fields) {
145
0
0
                        push @gFilters, " $field LIKE ? ";
146
0
0
                        push @gParams, "%$sSearch%";
147                    }
148                } else {
149
0
0
                    push @gFilters, " $mDataProp LIKE ? ";
150
0
0
                    push @gParams, "%$sSearch%";
151                }
152            }
153
0
0
            $i++;
154        }
155
0
0
        push @filters, " (" . join(" OR ", @gFilters) . ") ";
156
0
0
        push @params, @gParams;
157    }
158
159    # Individual filters
160
0
0
    my $i = 0;
161
0
0
    while($i < $param->{'iColumns'}) {
162
0
0
        my $sSearch = $param->{'sSearch_'.$i};
163
0
0
        if($sSearch) {
164
0
0
            my $mDataProp = $param->{'mDataProp_'.$i};
165
0
0
            my @filter_fields = $param->{$mDataProp.'_filteron'}
166                ? split(' ', $param->{$mDataProp.'_filteron'})
167                : ();
168
0
0
            if(@filter_fields > 0) {
169
0
0
                my @localfilters;
170
0
0
                foreach my $field (@filter_fields) {
171
0
0
                    push @localfilters, " $field LIKE ? ";
172
0
0
                    push @params, "%$sSearch%";
173                }
174
0
0
                push @filters, " ( ". join(" OR ", @localfilters) ." ) ";
175            } else {
176
0
0
                push @filters, " $mDataProp LIKE ? ";
177
0
0
                push @params, "%$sSearch%";
178            }
179        }
180
0
0
        $i++;
181    }
182
183
0
0
    return (\@filters, \@params);
184}
185
186 - 192
=head2 dt_get_params

    my %dtparam = = dt_get_params( $input )
    This function takes a reference to a new CGI object.
    It prepares a hash containing Datatable parameters.

=cut
193sub dt_get_params {
194
0
0
    my $input = shift;
195
0
0
    my %dtparam;
196
0
0
    my $vars = $input->Vars;
197
198
0
0
    foreach(qw/ iDisplayStart iDisplayLength iColumns sSearch bRegex iSortingCols sEcho /) {
199
0
0
        $dtparam{$_} = $input->param($_);
200    }
201
0
0
    foreach(grep /(?:_sorton|_filteron)$/, keys %$vars) {
202
0
0
        $dtparam{$_} = $vars->{$_};
203    }
204    for(my $i=0; $i<$dtparam{'iColumns'}; $i++) {
205
0
0
        foreach(qw/ bSearchable sSearch bRegex bSortable iSortCol mDataProp sSortDir /) {
206
0
0
            my $key = $_ . '_' . $i;
207
0
0
            $dtparam{$key} = $input->param($key) if defined $input->param($key);
208        }
209
0
0
    }
210
0
0
    return %dtparam;
211}
212
213 - 223
=head2 dt_build_query_simple

    my ( $query, $params )= dt_build_query_simple( $value, $field )

    This function takes a value and a field (table.field).

    It returns (undef, []) if not $value.
    Else, returns a SQL where string and an arrayref containing parameters
    for the execute method of the statement.

=cut
224sub dt_build_query_simple {
225
0
0
    my ( $value, $field ) = @_;
226
0
0
    my $query;
227
0
0
    my @params;
228
0
0
    if( $value ) {
229
0
0
        $query .= " AND $field = ? ";
230
0
0
        push @params, $value;
231    }
232
0
0
    return ( $query, \@params );
233}
234
235 - 245
=head2 dt_build_query_dates

    my ( $query, $params )= dt_build_query_dates( $datefrom, $dateto, $field)

    This function takes a datefrom, dateto and a field (table.field).

    It returns (undef, []) if not $value.
    Else, returns a SQL where string and an arrayref containing parameters
    for the execute method of the statement.

=cut
246sub dt_build_query_dates {
247
0
0
    my ( $datefrom, $dateto, $field ) = @_;
248
0
0
    my $query;
249
0
0
    my @params;
250
0
0
    if ( $datefrom ) {
251
0
0
        $query .= " AND $field >= ? ";
252
0
0
        push @params, C4::Dates->new($datefrom)->output('iso');
253    }
254
0
0
    if ( $dateto ) {
255
0
0
        $query .= " AND $field <= ? ";
256
0
0
        push @params, C4::Dates->new($dateto)->output('iso');
257    }
258
0
0
    return ( $query, \@params );
259}
260
261 - 271
=head2 dt_build_query

    my ( $query, $filter ) = dt_build_query( $type, @params )

    This function takes a value and a list of parameters.

    It calls dt_build_query_dates or dt_build_query_simple fonction of $type.

    $type can be 'simple' or 'rage_dates'.

=cut
272sub dt_build_query {
273
0
0
    my ( $type, @params ) = @_;
274
0
0
    given ( $type ) {
275
0
0
        when ( /simple/ ) {
276
0
0
            return dt_build_query_simple( @params );
277        }
278
0
0
        when ( /range_dates/ ) {
279
0
0
            return dt_build_query_dates( @params );
280        }
281    }
282}
283
2841;