Advertisement
Guest User

ssp.class.php - custom

a guest
Jul 26th, 2016
1,934
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 16.95 KB | None | 0 0
  1. <?php
  2.  
  3. /*
  4.  * Helper functions for building a DataTables server-side processing SQL query
  5.  *
  6.  * The static functions in this class are just helper functions to help build
  7.  * the SQL used in the DataTables demo server-side processing scripts. These
  8.  * functions obviously do not represent all that can be done with server-side
  9.  * processing, they are intentionally simple to show how it works. More complex
  10.  * server-side processing operations will likely require a custom script.
  11.  *
  12.  * See http://datatables.net/usage/server-side for full details on the server-
  13.  * side processing requirements of DataTables.
  14.  *
  15.  * @license MIT - http://datatables.net/license_mit
  16.  * @editedby (SilverX) Chris R. McLeod
  17.  *
  18.  */
  19.  
  20. class SSP {
  21.     /**
  22.      * Create the data output array for the DataTables rows
  23.      *
  24.      *  @param  array $columns Column information array
  25.      *  @param  array $data    Data from the SQL get
  26.      *  @return array          Formatted data in a row based format
  27.      */
  28.     private static function data_output ( $options, $data ) {
  29.         $out = array();
  30.         $columns = $options['columns'];
  31.         for ( $i=0, $ien=count($data) ; $i<$ien ; $i++ ) {
  32.             $row = array();
  33.             for ( $j=0, $jen=count($columns) ; $j<$jen ; $j++ ) {
  34.                 $column = $columns[$j];
  35.                 // Is there a formatter?
  36.                 if ( isset( $column['formatter'] ) ) {
  37.                     $row[ $column['dt'] ] = $column['formatter']( $data[$i][ self::column_name_out($column) ], $data[$i] );
  38.                 }
  39.                 else {
  40.                     $row[ $column['dt'] ] = $data[$i][ self::column_name_out($column) ];
  41.                 }
  42.             }
  43.             $out[] = $row;
  44.         }
  45.         return $out;
  46.     }
  47.  
  48.     /**
  49.      * Perform the SQL queries needed for an server-side processing requested,
  50.      * utilising the helper functions of this class, limit(), order() and
  51.      * filter() among others. The returned array is ready to be encoded as JSON
  52.      * in response to an SSP request, or can be modified if needed before
  53.      * sending back to the client.
  54.      *
  55.      *  @param  array $request Data sent to server by DataTables
  56.      *  @param  array|PDO $conn PDO connection resource or connection parameters array
  57.      *  @param  string $table SQL table to query
  58.      *  @param  string $primaryKey Primary key of the table
  59.      *  @param  array $columns Column information array
  60.      *  @return array          Server-side processing response array
  61.      */
  62.     static function process ( $request, $conn, $options ) {
  63.         $bindings = array();
  64.         $db = self::db( $conn );
  65.  
  66.         if (!isset($options['alias']))
  67.             $options['alias'] = $options['table'][0];
  68.  
  69.         // Build the SQL query string from the request
  70.         $limitSql = self::limit( $request );
  71.         $orderSql = self::order( $request, $options );
  72.         $whereSql = self::filter( $request, $options, $bindings );
  73.         $joinSql  = self::table_join( $options );
  74.        
  75.         $whereAllSql = '';
  76.        
  77.         if (isset($options['whereResult'])) {
  78.             $optWhere = self::where_options( $options['whereResult'], $options['alias'], $bindings );
  79.  
  80.             $whereSql = $whereSql ?
  81.                 $whereSql .' AND '.$optWhere :
  82.                 'WHERE '.$optWhere;
  83.         }
  84.        
  85.         if (isset($options['where'])) {
  86.             $optWhere = self::where_options( $options['where'], $options['alias'], $bindings );
  87.  
  88.             $whereSql = $whereSql ?
  89.                 $whereSql .' AND '.$optWhere :
  90.                 'WHERE '.$optWhere;
  91.            
  92.             $whereAllSql = 'WHERE '.$optWhere;
  93.         }
  94.        
  95.         $query = "SELECT ".implode(", ", self::column_names( $options ))."
  96.             FROM `{$options['table']}` {$options['alias']}
  97.             $joinSql
  98.             $whereSql
  99.             $orderSql
  100.             $limitSql";
  101.  
  102.         // Main query to actually get the data
  103.         $data = self::sql_exec( $db, $bindings, $query);
  104.  
  105.         // Data set length after filtering
  106.         $resFilterLength = self::sql_exec( $db, $bindings,
  107.             "SELECT COUNT({$options['alias']}.`{$options['primaryKey']}`)
  108.              FROM `{$options['table']}` {$options['alias']}
  109.              $joinSql
  110.              $whereSql"
  111.         );
  112.         $recordsFiltered = $resFilterLength[0][0];
  113.  
  114.         // Total data set length
  115.         $resTotalLength = self::sql_exec( $db, $bindings,
  116.             "SELECT COUNT({$options['alias']}.`{$options['primaryKey']}`)
  117.              FROM `{$options['table']}` {$options['alias']}
  118.              $joinSql
  119.              $whereAllSql"
  120.         );
  121.         $recordsTotal = $resTotalLength[0][0];
  122.  
  123.         /*
  124.          * Output
  125.          */
  126.         return array(
  127.             "draw"            => isset ( $request['draw'] ) ? intval( $request['draw'] ) : 0,
  128.             "recordsTotal"    => intval( $recordsTotal ),
  129.             "recordsFiltered" => intval( $recordsFiltered ),
  130.             "data"            => self::data_output( $options, $data )
  131.         );
  132.     }
  133.  
  134.     /**
  135.      * Database connection
  136.      *
  137.      * Obtain an PHP PDO connection from a connection details array
  138.      *
  139.      *  @param  array $conn SQL connection details. The array should have
  140.      *    the following properties
  141.      *     * host - host name
  142.      *     * db   - database name
  143.      *     * user - user name
  144.      *     * pass - user password
  145.      *  @return resource PDO connection
  146.      */
  147.     private static function db ( $conn ) {
  148.         if ( is_array( $conn ) ) {
  149.             return self::sql_connect( $conn );
  150.         }
  151.  
  152.         return $conn;
  153.     }
  154.  
  155.  
  156.     /**
  157.      * Paging
  158.      *
  159.      * Construct the LIMIT clause for server-side processing SQL query
  160.      *
  161.      *  @param  array $request Data sent to server by DataTables
  162.      *  @param  array $columns Column information array
  163.      *  @return string SQL limit clause
  164.      */
  165.     private static function limit ( $request ) {
  166.         $limit = '';
  167.  
  168.         if ( isset($request['start']) && $request['length'] != -1 ) {
  169.             $limit = "LIMIT ".intval($request['start']).", ".intval($request['length']);
  170.         }
  171.  
  172.         return $limit;
  173.     }
  174.  
  175.  
  176.     /**
  177.      * Ordering
  178.      *
  179.      * Construct the ORDER BY clause for server-side processing SQL query
  180.      *
  181.      *  @param  array $request Data sent to server by DataTables
  182.      *  @param  array $columns Column information array
  183.      *  @return string SQL order by clause
  184.      */
  185.     private static function order ( $request, $options ) {
  186.         $order = '';
  187.         $columns = $options['columns'];
  188.        
  189.         if ( isset($request['order']) && count($request['order']) ) {
  190.             $orderBy = array();
  191.             $dtColumns = self::pluck( $columns, 'dt' );
  192.  
  193.             for ( $i=0, $ien=count($request['order']) ; $i<$ien ; $i++ ) {
  194.                 // Convert the column index into the column data property
  195.                 $columnIdx = intval($request['order'][$i]['column']);
  196.                 $requestColumn = $request['columns'][$columnIdx];
  197.  
  198.                 $columnIdx = array_search( $requestColumn['data'], $dtColumns );
  199.                 $column = $columns[ $columnIdx ];
  200.  
  201.                 if ( $requestColumn['orderable'] == 'true' ) {
  202.                     $dir = $request['order'][$i]['dir'] === 'asc' ?
  203.                         'ASC' :
  204.                         'DESC';
  205.  
  206.                     $orderBy[] = self::column_name_ref($column, $options['alias']).' '.$dir;
  207.                 }
  208.             }
  209.  
  210.             $order = 'ORDER BY '.implode(', ', $orderBy);
  211.         }
  212.  
  213.         return $order;
  214.     }
  215.  
  216.  
  217.     /**
  218.      * Searching / Filtering
  219.      *
  220.      * Construct the WHERE clause for server-side processing SQL query.
  221.      *
  222.      * NOTE this does not match the built-in DataTables filtering which does it
  223.      * word by word on any field. It's possible to do here performance on large
  224.      * databases would be very poor
  225.      *
  226.      *  @param  array $request Data sent to server by DataTables
  227.      *  @param  array $columns Column information array
  228.      *  @param  array $bindings Array of values for PDO bindings, used in the
  229.      *    sql_exec() function
  230.      *  @return string SQL where clause
  231.      */
  232.     private static function filter ( $request, $options, &$bindings ) {
  233.         $globalSearch = array();
  234.         $columnSearch = array();
  235.        
  236.         $columns = $options['columns'];
  237.         $dtColumns = self::pluck( $columns, 'dt' );
  238.  
  239.         if ( isset($request['search']) && $request['search']['value'] != '' ) {
  240.             $str = $request['search']['value'];
  241.  
  242.             for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
  243.                 $requestColumn = $request['columns'][$i];
  244.                 $columnIdx = array_search( $requestColumn['data'], $dtColumns );
  245.                 $column = $columns[ $columnIdx ];
  246.  
  247.                 if ( $requestColumn['searchable'] == 'true' ) {
  248.                     $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
  249.                     $globalSearch[] = self::column_name_ref($column, $options['alias'])." LIKE ".$binding;
  250.                 }
  251.             }
  252.         }
  253.  
  254.         // Individual column filtering
  255.         if ( isset( $request['columns'] ) ) {
  256.             for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
  257.                 $requestColumn = $request['columns'][$i];
  258.                 $columnIdx = array_search( $requestColumn['data'], $dtColumns );
  259.                 $column = $columns[ $columnIdx ];
  260.  
  261.                 $str = $requestColumn['search']['value'];
  262.  
  263.                 if ( $requestColumn['searchable'] == 'true' &&
  264.                  $str != '' ) {
  265.                     $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
  266.                     $columnSearch[] = self::column_name_ref($column, $options['alias'])." LIKE ".$binding;
  267.                 }
  268.             }
  269.         }
  270.  
  271.         // Combine the filters into a single string
  272.         $where = '';
  273.  
  274.         if ( count( $globalSearch ) ) {
  275.             $where = '('.implode(' OR ', $globalSearch).')';
  276.         }
  277.  
  278.         if ( count( $columnSearch ) ) {
  279.             $where = $where === '' ?
  280.                 implode(' AND ', $columnSearch) :
  281.                 $where .' AND '. implode(' AND ', $columnSearch);
  282.         }
  283.  
  284.         if ( $where !== '' ) {
  285.             $where = 'WHERE '.$where;
  286.         }
  287.  
  288.         return $where;
  289.     }
  290.    
  291.     /**
  292.      * Retrieves additional WHERE parameters from the supplied DataTable options.
  293.      * Used to globally filter results by fields not supplied by a DataTable request.
  294.      *  @param  array  $where_opts  The options to build the clause from. Two types exist, "where" and "whereResult".
  295.      *  @param  string $tableAlias  The alias of the table in 'FROM' statement.
  296.      *  @return string              WHERE clause SQL to use in select query.
  297.      */
  298.     private static function where_options ( $where_opts, $tableAlias, &$bindings ) {
  299.         $wheres = [];
  300.         foreach($where_opts as $i => $where) {
  301.             $alias = isset($where['alias']) ? $where['alias'] : $tableAlias;
  302.             $binding = self::bind($bindings, $where['value']);
  303.            
  304.             $wheres[] = "$alias.`{$where['db']}` {$where['op']} $binding";
  305.         }
  306.        
  307.         return implode(', AND ', $wheres);
  308.     }
  309.  
  310.     /**
  311.      * Retrieves a column name as it would appear in a select statement.
  312.      * This includes any "as" parameters applied to the column.
  313.      *  @param  array  $options     The column to retrieve information from.
  314.      *  @param  string $tableAlias  The alias of the table in 'FROM' statement.
  315.      *  @return string              JOIN statement(s) SQL to use in select query.
  316.      */
  317.     private static function table_join( $options ) {
  318.         $joins = [];
  319.         $columns = $options['columns'];
  320.         for ($i = 0; $i < count($columns); $i++) {
  321.             $col = $columns[$i];
  322.             if (isset($col['join'])) {
  323.                 $join = $col['join'];
  324.                 $table = $join['table'];
  325.                 $alias = isset($join['alias']) ? $join['alias'] : $table[0];
  326.                 $joins[$alias] = "JOIN `$table` $alias ON ($alias.`{$join['on']}` = {$options['alias']}.`{$col['db']}`)";
  327.             }
  328.         }
  329.         return implode(' ', $joins);
  330.     }
  331.    
  332.     /**
  333.      * Retrieves a list of column names as they would appear in a select statement.
  334.      * This includes any "as" parameters applied to the column.
  335.      *  @param  array $options Array of DataTable options to retrieve information from.
  336.      *  @return array          List of column name strings to use in SQL select query.
  337.      */
  338.     private static function column_names ( $options ) {
  339.         $names = [];
  340.         $columns = $options['columns'];
  341.         for ($i = 0; $i < count($columns); $i++) {
  342.             $names[] = self::column_name($columns[$i], $options['alias']);
  343.         }
  344.         return $names;
  345.     }
  346.    
  347.     /**
  348.      * Retrieves a column name as it would appear in a select statement.
  349.      * This includes any "as" parameters applied to the column.
  350.      *  @param  array  $column      The column to retrieve information from.
  351.      *  @param  string $tableAlias  The alias of the table in 'FROM' statement.
  352.      *  @return string              Column name string to use in SQL select query.
  353.      */
  354.     private static function column_name ( $column, $tableAlias ) {
  355.         if (isset($column['join'])) {
  356.             $join = $column['join'];
  357.             $join['alias'] = isset($join['alias']) ? $join['alias'] : $join['table'][0];
  358.             return "{$join['alias']}.`{$join['select']}`".(isset($join['as']) ? " AS '{$join['as']}'" : '');
  359.         }
  360.         return "$tableAlias.`{$column['db']}`".(isset($column['as']) ? " AS '{$column['as']}'" : '');
  361.     }
  362.  
  363.     /**
  364.      * Retrieves a column name as it would appear in a clause (WHERE, GROUP BY, ORDER BY).
  365.      * This is typically the column's original name unless joined with an alias or using "as".
  366.      *  @param  array  $column      The column to retrieve information from.
  367.      *  @param  string $tableAlias  The alias of the table in 'FROM' statement.
  368.      *  @return string              Column name string to use in SQL clause.
  369.      */
  370.     private static function column_name_ref ( $column, $tableAlias ) {
  371.         if (isset($column['join'])) {
  372.             $join = $column['join'];
  373.             $join['alias'] = isset($join['alias']) ? $join['alias'] : $join['table'][0];
  374.             return isset($join['as']) ? "`{$join['as']}`" : "{$join['alias']}.`{$join['select']}`";
  375.         }
  376.         return isset($column['as']) ? "`{$column['as']}`" : "$tableAlias.`{$column['db']}`";
  377.     }
  378.    
  379.     /**
  380.      * Retrieves a column name as it would appear in the final resultset.
  381.      * Results have the alias' removed and will refer to it's "as", if any.
  382.      *  @param  array  $column  The column to retrieve information from.
  383.      *  @return string          Column name string that will appear in the results.
  384.      */
  385.     private static function column_name_out ( $column ) {
  386.         if (isset($column['join'])) {
  387.             $join = $column['join'];
  388.             return isset($join['as']) ? $join['as'] : $join['select'];
  389.         }
  390.         return isset($column['as']) ? $column['as'] : $column['db'];
  391.     }
  392.  
  393.     /**
  394.      * Connect to the database
  395.      *
  396.      * @param  array $sql_details SQL server connection details array, with the
  397.      *   properties:
  398.      *     * host - host name
  399.      *     * db   - database name
  400.      *     * user - user name
  401.      *     * pass - user password
  402.      * @return resource Database connection handle
  403.      */
  404.     private static function sql_connect ( $sql_details )
  405.     {
  406.         try {
  407.             $db = @new PDO(
  408.                 "mysql:host={$sql_details['host']};dbname={$sql_details['db']}",
  409.                 $sql_details['user'],
  410.                 $sql_details['pass'],
  411.                 array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION )
  412.             );
  413.         }
  414.         catch (PDOException $e) {
  415.             self::fatal(
  416.                 "An error occurred while connecting to the database. ".
  417.                 "The error reported by the server was: ".$e->getMessage()
  418.             );
  419.         }
  420.  
  421.         return $db;
  422.     }
  423.  
  424.  
  425.     /**
  426.      * Execute an SQL query on the database
  427.      *
  428.      * @param  resource $db  Database handler
  429.      * @param  array    $bindings Array of PDO binding values from bind() to be
  430.      *   used for safely escaping strings. Note that this can be given as the
  431.      *   SQL query string if no bindings are required.
  432.      * @param  string   $sql SQL query to execute.
  433.      * @return array         Result from the query (all rows)
  434.      */
  435.     private static function sql_exec ( $db, $bindings, $sql=null )
  436.     {
  437.         // Argument shifting
  438.         if ( $sql === null ) {
  439.             $sql = $bindings;
  440.         }
  441.  
  442.         $stmt = $db->prepare( $sql );
  443.         //echo $sql;
  444.  
  445.         // Bind parameters
  446.         if ( is_array( $bindings ) ) {
  447.             for ( $i=0, $ien=count($bindings) ; $i<$ien ; $i++ ) {
  448.                 $binding = $bindings[$i];
  449.                 $stmt->bindValue( $binding['key'], $binding['val'], $binding['type'] );
  450.             }
  451.         }
  452.  
  453.         // Execute
  454.         try {
  455.             $stmt->execute();
  456.         }
  457.         catch (PDOException $e) {
  458.             self::fatal( "An SQL error occurred: ".$e->getMessage() );
  459.         }
  460.  
  461.         // Return all
  462.         return $stmt->fetchAll( PDO::FETCH_BOTH );
  463.     }
  464.  
  465.  
  466.     /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
  467.      * Internal methods
  468.      */
  469.  
  470.     /**
  471.      * Throw a fatal error.
  472.      *
  473.      * This writes out an error message in a JSON string which DataTables will
  474.      * see and show to the user in the browser.
  475.      *
  476.      * @param  string $msg Message to send to the client
  477.      */
  478.     private static function fatal ( $msg )
  479.     {
  480.         echo json_encode( array(
  481.             "error" => $msg
  482.         ) );
  483.  
  484.         exit(0);
  485.     }
  486.  
  487.     /**
  488.      * Create a PDO binding key which can be used for escaping variables safely
  489.      * when executing a query with sql_exec()
  490.      *
  491.      * @param  array &$a    Array of bindings
  492.      * @param  *      $val  Value to bind
  493.      * @param  int    $type PDO field type
  494.      * @return string       Bound key to be used in the SQL where this parameter
  495.      *   would be used.
  496.      */
  497.     private static function bind ( &$a, $val, $type = NULL )
  498.     {
  499.         $key = ':binding_'.count( $a );
  500.  
  501.         $a[] = array(
  502.             'key' => $key,
  503.             'val' => $val,
  504.             'type' => isset($type) ? $type : (is_numeric($val) ? PDO::PARAM_INT : PDO::PARAM_STR)
  505.         );
  506.  
  507.         return $key;
  508.     }
  509.  
  510.  
  511.     /**
  512.      * Pull a particular property from each assoc. array in a numeric array,
  513.      * returning and array of the property values from each item.
  514.      *
  515.      *  @param  array  $a    Array to get data from
  516.      *  @param  string $prop Property to read
  517.      *  @return array        Array of property values
  518.      */
  519.     private static function pluck ( $a, $prop )
  520.     {
  521.         $out = array();
  522.  
  523.         for ( $i=0, $len=count($a) ; $i<$len ; $i++ ) {
  524.             $out[] = $a[$i][$prop];
  525.         }
  526.  
  527.         return $out;
  528.     }
  529.  
  530.  
  531.     /**
  532.      * Return a string from an array or a string
  533.      *
  534.      * @param  array|string $a Array to join
  535.      * @param  string $join Glue for the concatenation
  536.      * @return string Joined string
  537.      */
  538.     private static function _flatten ( $a, $join = ' AND ' )
  539.     {
  540.         if ( ! $a ) {
  541.             return '';
  542.         }
  543.         else if ( $a && is_array($a) ) {
  544.             return implode( $join, $a );
  545.         }
  546.         return $a;
  547.     }
  548. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement