WebSVN – lars-tiefland.ci – /htdocs/backend/js/DataTables-1.10.9/examples/server_side/scripts/ssp.class.php

<?php

/*
 * 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
 */


// REMOVE THIS BLOCK - used for DataTables test environment only!
$file = $_SERVER['DOCUMENT_ROOT'].'/datatables/mysql.php';
if ( is_file( $file ) ) {
        include( $file );
}


class SSP {
        /**
         * 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
         */
        static function data_output ( $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;
        }


        /**
         * Database connection
         *
         * Obtain an PHP PDO connection from a connection details array
         *
         *  @param  array $conn SQL connection details. The array should have
         *    the following properties
         *     * host - host name
         *     * db   - database name
         *     * user - user name
         *     * pass - user password
         *  @return resource PDO connection
         */
        static function db ( $conn )
        {
                if ( is_array( $conn ) ) {
                        return self::sql_connect( $conn );
                }

                return $conn;
        }


        /**
         * Paging
         *
         * Construct the LIMIT 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 limit clause
         */
        static function limit ( $request, $columns )
        {
                $limit = '';

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

                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
         */
        static function order ( $request, $columns )
        {
                $order = '';

                if ( isset($request['order']) && count($request['order']) ) {
                        $orderBy = array();
                        $dtColumns = self::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
         *    sql_exec() function
         *  @return string SQL where clause
         */
        static function filter ( $request, $columns, &$bindings )
        {
                $globalSearch = array();
                $columnSearch = array();
                $dtColumns = self::pluck( $columns, 'dt' );

                if ( isset($request['search']) && $request['search']['value'] != '' ) {
                        $str = $request['search']['value'];

                        for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
                                $requestColumn = $request['columns'][$i];
                                $columnIdx = array_search( $requestColumn['data'], $dtColumns );
                                $column = $columns[ $columnIdx ];

                                if ( $requestColumn['searchable'] == 'true' ) {
                                        $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                                        $globalSearch[] = "`".$column['db']."` LIKE ".$binding;
                                }
                        }
                }

                // Individual column filtering
                for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
                        $requestColumn = $request['columns'][$i];
                        $columnIdx = array_search( $requestColumn['data'], $dtColumns );
                        $column = $columns[ $columnIdx ];

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

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

                // 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;
        }


        /**
         * 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  array|PDO $conn PDO connection resource or connection parameters array
         *  @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
         */
        static function simple ( $request, $conn, $table, $primaryKey, $columns )
        {
                $bindings = array();
                $db = self::db( $conn );

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

                // Main query to actually get the data
                $data = self::sql_exec( $db, $bindings,
                        "SELECT SQL_CALC_FOUND_ROWS `".implode("`, `", self::pluck($columns, 'db'))."`
                         FROM `$table`
                         $where
                         $order
                         $limit"
                );

                // Data set length after filtering
                $resFilterLength = self::sql_exec( $db,
                        "SELECT FOUND_ROWS()"
                );
                $recordsFiltered = $resFilterLength[0][0];

                // Total data set length
                $resTotalLength = self::sql_exec( $db,
                        "SELECT COUNT(`{$primaryKey}`)
                         FROM   `$table`"
                );
                $recordsTotal = $resTotalLength[0][0];


                /*
                 * Output
                 */
                return array(
                        "draw"            => intval( $request['draw'] ),
                        "recordsTotal"    => intval( $recordsTotal ),
                        "recordsFiltered" => intval( $recordsFiltered ),
                        "data"            => self::data_output( $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  array|PDO $conn PDO connection resource or connection parameters array
         *  @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
         */
        static function complex ( $request, $conn, $table, $primaryKey, $columns, $whereResult=null, $whereAll=null )
        {
                $bindings = array();
                $db = self::db( $conn );
                $localWhereResult = array();
                $localWhereAll = array();
                $whereAllSql = '';

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

                $whereResult = self::_flatten( $whereResult );
                $whereAll = self::_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 = self::sql_exec( $db, $bindings,
                        "SELECT SQL_CALC_FOUND_ROWS `".implode("`, `", self::pluck($columns, 'db'))."`
                         FROM `$table`
                         $where
                         $order
                         $limit"
                );

                // Data set length after filtering
                $resFilterLength = self::sql_exec( $db,
                        "SELECT FOUND_ROWS()"
                );
                $recordsFiltered = $resFilterLength[0][0];

                // Total data set length
                $resTotalLength = self::sql_exec( $db, $bindings,
                        "SELECT COUNT(`{$primaryKey}`)
                         FROM   `$table` ".
                        $whereAllSql
                );
                $recordsTotal = $resTotalLength[0][0];

                /*
                 * Output
                 */
                return array(
                        "draw"            => intval( $request['draw'] ),
                        "recordsTotal"    => intval( $recordsTotal ),
                        "recordsFiltered" => intval( $recordsFiltered ),
                        "data"            => self::data_output( $columns, $data )
                );
        }


        /**
         * Connect to the database
         *
         * @param  array $sql_details SQL server connection details array, with the
         *   properties:
         *     * host - host name
         *     * db   - database name
         *     * user - user name
         *     * pass - user password
         * @return resource Database connection handle
         */
        static function sql_connect ( $sql_details )
        {
                try {
                        $db = @new PDO(
                                "mysql:host={$sql_details['host']};dbname={$sql_details['db']}",
                                $sql_details['user'],
                                $sql_details['pass'],
                                array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION )
                        );
                }
                catch (PDOException $e) {
                        self::fatal(
                                "An error occurred while connecting to the database. ".
                                "The error reported by the server was: ".$e->getMessage()
                        );
                }

                return $db;
        }


        /**
         * Execute an SQL query on the database
         *
         * @param  resource $db  Database handler
         * @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)
         */
        static function sql_exec ( $db, $bindings, $sql=null )
        {
                // Argument shifting
                if ( $sql === null ) {
                        $sql = $bindings;
                }

                $stmt = $db->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) {
                        self::fatal( "An SQL error occurred: ".$e->getMessage() );
                }

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


        /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
         * Internal methods
         */

        /**
         * 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
         */
        static function fatal ( $msg )
        {
                echo json_encode( array( 
                        "error" => $msg
                ) );

                exit(0);
        }

        /**
         * Create a PDO binding key which can be used for escaping variables safely
         * when executing a query with sql_exec()
         *
         * @param  array &$a    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.
         */
        static function bind ( &$a, $val, $type )
        {
                $key = ':binding_'.count( $a );

                $a[] = 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
         */
        static function pluck ( $a, $prop )
        {
                $out = array();

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

                return $out;
        }


        /**
         * Return a string from an array or a string
         *
         * @param  array|string $a Array to join
         * @param  string $join Glue for the concatenation
         * @return string Joined string
         */
        static function _flatten ( $a, $join = ' AND ' )
        {
                if ( ! $a ) {
                        return '';
                }
                else if ( $a && is_array($a) ) {
                        return implode( $join, $a );
                }
                return $a;
        }
}
Subversion-Projekte lars-tiefland.ci

(root)/htdocs/backend/js/DataTables-1.10.9/examples/server_side/scripts/ssp.class.php @ 2001 – Revision 875
