PHP Datatables proxy to SQLServer

DataTables.php

<?php

namespace DataTables;

use PDO;
use PDOException;
use Exception;

/*
 * This script it is originated by datatables official exampla and has been
 * modified to work with SQLSERVER.
 * Helper functions for building a DataTables server-side processing SQL query
 *
 * The static functions in this class are just helper functions to help build
 * the SQL used in the DataTables demo server-side processing scripts. These
 * functions obviously do not represent all that can be done with server-side
 * processing, they are intentionally simple to show how it works. More complex
 * server-side processing operations will likely require a custom script.
 *
 * See http://datatables.net/usage/server-side for full details on the server-
 * side processing requirements of DataTables.
 *
 * @license MIT - http://datatables.net/license_mit
 */

class DataTables
{
    /**
     * @var PDO
     */
    protected $database;

    public function __construct(PDO $database)
    {
        $this->database = $database;
    }
    /**
     * Create the data output array for the DataTables rows
     *
     *  @param  array $columns Column information array
     *  @param  array $data    Data from the SQL get
     *  @return array          Formatted data in a row based format
     */
    public function dataOutput($columns, $data)
    {
        $out = array();

        for ($i=0, $ien=count($data); $i<$ien; $i++) {
            $row = array();

            for ($j=0, $jen=count($columns); $j<$jen; $j++) {
                $column = $columns[$j];

                // Is there a formatter?
                if (isset($column['formatter'])) {
                    $row[ $column['dt'] ] = $column['formatter']($data[$i][ $column['db'] ], $data[$i]);
                } else {
                    $row[ $column['dt'] ] = $data[$i][ $columns[$j]['db'] ];
                }
            }

            $out[] = $row;
        }

        return $out;
    }

    /**
     * Paging
     *
     * Construct the LIMIT clause for server-side processing SQL query
     *
     *  @param  array $request Data sent to server by DataTables
     *  @return string SQL limit clause
     */
    public function limit($request)
    {
        $limit = '';

        if (isset($request['start']) && $request['length'] != -1) {
            $limit = "LIMIT ".intval($request['start']).", ".intval($request['length']);
            $limit = "OFFSET " . intval($request['start']) . " ROWS FETCH NEXT "
                . intval($request['length']) . " ROWS ONLY";
        }

        return $limit;
    }


    /**
     * Ordering
     *
     * Construct the ORDER BY clause for server-side processing SQL query
     *
     *  @param  array $request Data sent to server by DataTables
     *  @param  array $columns Column information array
     *  @return string SQL order by clause
     */
    public function order($request, $columns)
    {
        $order = '';

        if (isset($request['order']) && count($request['order'])) {
            $orderBy = array();
            $dtColumns = $this->pluck($columns, 'dt');

            for ($i=0, $ien=count($request['order']); $i<$ien; $i++) {
                // Convert the column index into the column data property
                $columnIdx = intval($request['order'][$i]['column']);
                $requestColumn = $request['columns'][$columnIdx];

                $columnIdx = array_search($requestColumn['data'], $dtColumns);
                $column = $columns[ $columnIdx ];

                if ($requestColumn['orderable'] == 'true') {
                    $dir = $request['order'][$i]['dir'] === 'asc' ?
                        'ASC' :
                        'DESC';

                    $orderBy[] = '"'.$column['db'].'" '.$dir;
                }
            }

            $order = 'ORDER BY '.implode(', ', $orderBy);
        }

        return $order;
    }


    /**
     * Searching / Filtering
     *
     * Construct the WHERE clause for server-side processing SQL query.
     *
     * NOTE this does not match the built-in DataTables filtering which does it
     * word by word on any field. It's possible to do here performance on large
     * databases would be very poor
     *
     *  @param  array $request Data sent to server by DataTables
     *  @param  array $columns Column information array
     *  @param  array $bindings Array of values for PDO bindings, used in the
     *    sqlExec() function
     *  @return string SQL where clause
     */
    public function filter($request, $columns, &$bindings)
    {
        $globalSearch = array();
        $columnSearch = array();
        $dtColumns = $this->pluck($columns, 'dt');

        if (isset($request['search']) && $request['search']['value'] != '') {
            $this->filterSearchAll(
                $request['search']['value'],
                $columns,
                $request['columns'],
                $dtColumns,
                $bindings,
                $globalSearch
            );
        }

        // Individual column filtering
        if (isset($request['columns'])) {
            $this->filterSearchCol(
                $columns,
                $request['columns'],
                $dtColumns,
                $bindings,
                $columnSearch
            );
        }

        // Combine the filters into a single string
        $where = '';

        if (count($globalSearch)) {
            $where = '('.implode(' OR ', $globalSearch).')';
        }

        if (count($columnSearch)) {
            $where = $where === '' ?
                implode(' AND ', $columnSearch) :
                $where .' AND '. implode(' AND ', $columnSearch);
        }

        if ($where !== '') {
            $where = 'WHERE '.$where;
        }

        return $where;
    }


    private function filterSearchAll(
        string $searchVal,
        array $columns,
        array $requestColumns,
        array $dtColumns,
        &$bindings,
        array &$globalSearch
    ) {
        for ($i=0, $ien=count($requestColumns); $i<$ien; $i++) {
            $requestColumn = $requestColumns[$i];
            $columnIdx = array_search($requestColumn['data'], $dtColumns);
            $column = $columns[ $columnIdx ];

            if ($requestColumn['searchable'] == 'true') {
                $search = '%' . $searchVal . '%';
                $binding = $this->bind($bindings, $search, PDO::PARAM_STR);
                $globalSearch[] = "\"".$column['db']."\" LIKE ".$binding;
            }
        }
    }


    private function filterSearchCol(
        array $columns,
        array $requestColumns,
        array $dtColumns,
        &$bindings,
        array $columnSearch
    ) {
        for ($i=0, $ien=count($requestColumns); $i<$ien; $i++) {
            $requestColumn = $requestColumns[$i];
            $columnIdx = array_search($requestColumn['data'], $dtColumns);
            $column = $columns[ $columnIdx ];

            $str = $requestColumn['search']['value'];

            if ($requestColumn['searchable'] == 'true' && $str != '') {
                $binding = $this->bind($bindings, '%'.$str.'%', PDO::PARAM_STR);
                $columnSearch[] = "\"".$column['db']."\" LIKE ".$binding;
            }
        }
    }


    /**
     * Perform the SQL queries needed for an server-side processing requested,
     * utilising the helper functions of this class, limit(), order() and
     * filter() among others. The returned array is ready to be encoded as JSON
     * in response to an SSP request, or can be modified if needed before
     * sending back to the client.
     *
     *  @param  array $request Data sent to server by DataTables
     *  @param  string $table SQL table to query
     *  @param  string $primaryKey Primary key of the table
     *  @param  array $columns Column information array
     *  @return array          Server-side processing response array
     */
    public function simple($request, $table, $primaryKey, $columns)
    {
        $bindings = array();

        // Build the SQL query string from the request
        $limit = $this->limit($request);
        $order = $this->order($request, $columns);
        $where = $this->filter($request, $columns, $bindings);

        $getDataSql = "SELECT \""
            . implode("\",\"", $this->pluck($columns, 'db'))
            . "\" FROM {$table} $where $order $limit";

        // Main query to actually get the data
        $data = $this->sqlExec($bindings, $getDataSql);

        // Data set length after filtering
        $filterLengthSql = <<<EOF
SELECT COUNT("{$primaryKey}") FROM {$table} $where
EOF;
        $resFilterLength = $this->sqlExec($bindings, $filterLengthSql);
        $recordsFiltered = $resFilterLength[0][0];

        // Total data set length
        $totalLengthSql = "SELECT COUNT(\"{$primaryKey}\") FROM {$table}";
        $resTotalLength = $this->sqlExec($totalLengthSql);
        $recordsTotal = $resTotalLength[0][0];

        /*
         * Output
         */
        return array(
            "draw"            => $request['draw'] ?? 0,
            "recordsTotal"    => intval($recordsTotal),
            "recordsFiltered" => intval($recordsFiltered),
            "data"            => $this->dataOutput($columns, $data)
        );
    }


    /**
     * The difference between this method and the "simple" one, is that you can
     * apply additional "where" conditions to the SQL queries. These can be in
     * one of two forms:
     *
     * * 'Result condition' - This is applied to the result set, but not the
     *   overall paging information query - i.e. it will not effect the number
     *   of records that a user sees they can have access to. This should be
     *   used when you want apply a filtering condition that the user has sent.
     * * 'All condition' - This is applied to all queries that are made and
     *   reduces the number of records that the user can access. This should be
     *   used in conditions where you don't want the user to ever have access to
     *   particular records (for example, restricting by a login id).
     *
     *  @param  array $request Data sent to server by DataTables
     *  @param  string $table SQL table to query
     *  @param  string $primaryKey Primary key of the table
     *  @param  array $columns Column information array
     *  @param  string $whereResult WHERE condition to apply to the result set
     *  @param  string $whereAll WHERE condition to apply to all queries
     *  @return array          Server-side processing response array
     */
    public function complex(
        $request,
        $table,
        $primaryKey,
        $columns,
        $whereResult = null,
        $whereAll = null
    ) {
        $bindings = array();
        $whereAllSql = '';

        // Build the SQL query string from the request
        $limit = $this->limit($request);
        $order = $this->order($request, $columns);
        $where = $this->filter($request, $columns, $bindings);

        $whereResult = $this->flatten($whereResult);
        $whereAll = $this->flatten($whereAll);

        if ($whereResult) {
            $where = $where ?
                $where .' AND '.$whereResult :
                'WHERE '.$whereResult;
        }

        if ($whereAll) {
            $where = $where ?
                $where .' AND '.$whereAll :
                'WHERE '.$whereAll;

            $whereAllSql = 'WHERE '.$whereAll;
        }

        // Main query to actually get the data
        $data = $this->sqlExec(
            $bindings,
            "SELECT \"".implode("\",\"", $this->pluck($columns, 'db'))."\"
             FROM {$table}
             $where
             $order
             $limit"
        );

        // Data set length after filtering
        $resFilterLength = $this->sqlExec(
            $bindings,
            "SELECT COUNT(\"{$primaryKey}\")
             FROM {$table}
             $where"
        );
        $recordsFiltered = $resFilterLength[0][0];

        // Total data set length
        $resTotalLength = $this->sqlExec(
            $bindings,
            "SELECT COUNT(\"{$primaryKey}\")
             FROM {$table} ".
            $whereAllSql
        );
        $recordsTotal = $resTotalLength[0][0];

        /*
         * Output
         */
        return array(
            "draw"            => $request['draw'] ?? 0,
            "recordsTotal"    => intval($recordsTotal),
            "recordsFiltered" => intval($recordsFiltered),
            "data"            => $this->dataOutput($columns, $data)
        );
    }

    /**
     * Execute an SQL query on the database
     *
     * @param  array    $bindings Array of PDO binding values from bind() to be
     *   used for safely escaping strings. Note that this can be given as the
     *   SQL query string if no bindings are required.
     * @param  string   $sql SQL query to execute.
     * @return array         Result from the query (all rows)
     */
    public function sqlExec($bindings, $sql = null)
    {
        // Argument shifting
        if ($sql === null) {
            $sql = $bindings;
        }

        $stmt = $this->database->prepare($sql);
        //echo $sql;

        // Bind parameters
        if (is_array($bindings)) {
            for ($i=0, $ien=count($bindings); $i<$ien; $i++) {
                $binding = $bindings[$i];
                $stmt->bindValue($binding['key'], $binding['val'], $binding['type']);
            }
        }

        // Execute
        try {
            $stmt->execute();
        } catch (PDOException $e) {
            $this->fatal("An SQL error occurred: ".$e->getMessage());
        }

        // Return all
        return $stmt->fetchAll(PDO::FETCH_BOTH);
    }

    /**
     * Throw a fatal error.
     *
     * This writes out an error message in a JSON string which DataTables will
     * see and show to the user in the browser.
     *
     * @param  string $msg Message to send to the client
     */
    protected function fatal($msg)
    {
        throw new Exception('Error: ' . $msg);
    }

    /**
     * Create a PDO binding key which can be used for escaping variables safely
     * when executing a query with sqlExec()
     *
     * @param  array &$bindingsArray Array of bindings
     * @param  *     $val            Value to bind
     * @param  int   $type           PDO field type
     * @return string                Bound key to be used in the SQL where this
     *                               parameter would be used.
     */
    protected function bind(&$bindingsArray, $val, $type)
    {
        $key = ':binding_'.count($bindingsArray);

        $bindingsArray[] = array(
            'key' => $key,
            'val' => $val,
            'type' => $type
        );

        return $key;
    }


    /**
     * Pull a particular property from each assoc. array in a numeric array,
     * returning and array of the property values from each item.
     *
     *  @param  array  $a    Array to get data from
     *  @param  string $prop Property to read
     *  @return array        Array of property values
     */
    protected function pluck(array $data, $prop)
    {
        $out = array();

        for ($i=0, $len=count($data); $i<$len; $i++) {
            $out[] = $data[$i][$prop];
        }

        return $out;
    }


    /**
     * Return a string from an array or a string
     *
     * @param  array|string $arrayJoin Array to join
     * @param  string $join Glue for the concatenation
     * @return string Joined string
     */
    protected function flatten($arrayJoin, $join = ' AND ')
    {
        if (!$arrayJoin) {
            return '';
        }

        if (is_array($arrayJoin)) {
            return implode($join, $arrayJoin);
        }

        return $arrayJoin;
    }
}