6Ԟ_jf}nj7EgK!CXy  ^HK|%烛s~'uH/jh-v8?YH~>ڪ;  yG1cx@Ecf';Y1fR5ԹX*TM;=`W6hdxsi$ҚgY3k[졁v|FCK7kY*փ_>hX !g.29/۬R? 7Is5 ~@Jo*?QR!_D `T[X0**mtj,xzfAF~M̭d0 Tj A`Y+7iIh-Koo@h!;T&c01ben6(ŵRDq$Ka<a\_^0fp=l *Jb52A`kme e~y͕ WoH S#j{w(&Sl܏jvX |>Q-*\yWl~O&c3rP TA I0n!i*n"f\_;HeAjS]촤+ˇ!G]Ejs`1mFwUN U.#BEUfsf]1Wx/ Yn-0HI[Y*,s5:b>S[VRs95 , e v: ƹmtRiJEdc>AEɛݪ_DPWg29^Ǿ< ?\Fո]im5hUWC~xiA (#g'.SpE`/PIj`SM.}ͱ}>tK3#vLkk.9à@rfl7G*3~UkEY:sEǨ ͱ`A$IQ>sQ*8xk|W (o^qWtI,& 8Wdiy)&TBFQ#( 'P#VzfWT(S{I+[R 3K{WbrAO^`z޾!o+lo2X*QX"8mG|ZPޑ]o5[;Azh+W Ѽ tW"!+bV"މ;9 :뜽ghL>hֺc7P 9n5q Km`CI\V. zf؋E<2W>Ȼ<ߧ㝃kdĉ' 6,pH"Ctg}LmA{%TSzrE*UmOʣ !"׵8/} QykiR O%nbiDx|SaD=m%a?΢|=#"V.r2Tk`[+,S`t~ ĢQ$3I`iUn =EB䌾 ĢQ$30cݿ'YjvZh2ʣH(T|'+&/XފX7m(t ĢQ$3׸dc ĢQ$3A< YY5Y/k$8ic\*ZL^l nT<3f{pñB;Nmݍzt_\#|6:Y^_`t +!/'2A)EuӐ i?71vM: v*AvK'5]qS9,vݵ= ƔU c Df ĢQ$3Lv n^\bM9}2ӂ%֞AcMS|Pes³( \ST&0Fj\kk &3!Qŭ35sAO׋_a^ Ou:_+:`I4Z)(՘H@e]r7NES-fJ DhFKJ{0RDwi&O3/NT@kKȪ)ZSv,r_IZq?(۔WIa6DDŽD<.|KS/~?΢F;ѧC0}|SMpZNG ܣFlx51M=b3DD6 4</!XJNƛKH84 1F%I 矣t 9R nƓSAQdJB7RI<$YOggk xϼ$?I(QlObKo$=gm3eW3lҕ!A5+xH,Z. 3@ypfqa=%)gGK}&v_Ud{p$hn1Ҵs箍.^FјS@CbAXf_T\9A$t!ZȕPUD]Ĩ39Vx9)P0鹟4~&DaŅ`-8PP]㟵\hOwIٕ?yL$L|))Jh< 愇dܼ쟤'G^!{<; / ܢ,PI%PU_HICIvf O;kq?N3@:?Pcrmi[Bʦ(˸FZ$l6ßNa> SZ>r lseؿ꧂g'Swbcg\9y1)1vy>Smy }JsНc)ZE,RW׳BFӍH%⑳6v Z,nj[83SYEY:sEǨ ͱ`A$IQ>sQ*8xk|W KTsoXjq40#}TP~.$^ %)P0鹟4~&DaŅ?2C~;({dsAxNjworgmfKŤ3Vkr7kO3/NT>mmٛZcrmi| WZzeu4dS /:8({9>+;Jz8t mbl>e ض%dhE9pC龩6Frw+/p@s׺$D]6 keoeq7>lD hpfwZhx>}MFf-*G~OXN] (dnΨ`; '|.N|+J._lg4WQVs|bem6UrTw/jR'PxH^hykiR 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; } } if (count($orderBy)) { $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') { if (!empty($column['db'])) { $binding = self::bind($bindings, '%' . $str . '%', PDO::PARAM_STR); $globalSearch[] = "`" . $column['db'] . "` LIKE " . $binding; } } } } // Individual column filtering if (isset($request['columns'])) { 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 != '' ) { if (!empty($column['db'])) { $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, $whereAll = null, $searchFilter = array()) { $bindings = array(); $db = self::db($conn); $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); $whereAll = self::_flatten($whereAll); if (!empty($searchFilter['filter'])) { $sqlWhere = (strpos($where, 'WHERE') !== false) ? " AND " : " WHERE "; $whereAd = ''; $i = 0; foreach ($searchFilter['filter'] as $key => $val) { $pre = ($i > 0) ? " AND " : ""; $whereAd .= $pre . $key . " = '" . $val . "'"; $i++; } $whereLike = !empty($whereLike) ? $sqlWhere . $whereLike : ''; $where = $where . $sqlWhere . $whereAd; } $whereLike = ''; if (!empty($searchFilter['search'])) { $sqlWhere = (strpos($where, 'WHERE') !== false) ? " AND " : " WHERE "; $i = 0; foreach ($searchFilter['search'] as $key => $val) { $pre = ($i > 0) ? " OR " : ""; $whereLike .= $pre . $key . " LIKE '%" . $val . "%'"; $i++; } $whereLike = !empty($whereLike) ? $sqlWhere . ' (' . $whereLike . ') ' : ''; } 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 `" . implode("`, `", self::pluck($columns, 'db')) . "` FROM `$table` $where $whereLike $order $limit" ); // Data set length after filtering $resFilterLength = self::sql_exec( $db, $bindings, "SELECT COUNT(`{$primaryKey}`) FROM `$table` $where $whereLike" ); $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" => isset($request['draw']) ? intval($request['draw']) : 0, "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 `" . implode("`, `", self::pluck($columns, 'db')) . "` FROM `$table` $where $order $limit" ); // Data set length after filtering $resFilterLength = self::sql_exec( $db, $bindings, "SELECT COUNT(`{$primaryKey}`) FROM `$table` $where" ); $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" => isset($request['draw']) ? intval($request['draw']) : 0, "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(PDO::FETCH_BOTH); } /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 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++) { if (empty($a[$i][$prop])) { continue; } //removing the $out array index confuses the filter method in doing proper binding, //adding it ensures that the array data are mapped correctly $out[$i] = $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; } }