File Coverage

=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

=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

=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

=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

=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

=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