✏️ Edit: Datatables.php
Path:
/home/thiqat5/sys.thiqatech.com/application/libraries/Datatables.php
Size: 22.3 KB · Modified: 2024-10-08 17:26:03 · Perm: 0644 · Writable
← Back to directory
👁 View
⬇ Download
<?php if(!defined('BASEPATH')) exit('No direct script access allowed'); /** * Ignited Datatables * * This is a wrapper class/library based on the native Datatables server-side implementation by Allan Jardine * found at http://datatables.net/examples/data_sources/server_side.html for CodeIgniter * * @package CodeIgniter * @subpackage libraries * @category library * @version 2.0 <beta> * @author Vincent Bambico <metal.conspiracy@gmail.com> * Yusuf Ozdemir <yusuf@ozdemir.be> * @link http://ellislab.com/forums/viewthread/160896/ */ class Datatables { /** * Global container variables for chained argument results * */ private $ci; private $table; private $distinct; private $group_by = array(); private $select = array(); private $joins = array(); private $columns = array(); private $where = array(); private $or_where = array(); private $like = array(); private $filter = array(); private $add_columns = array(); private $edit_columns = array(); private $unset_columns = array(); private $having = array(); private $backtick_protect = false; private $sets_columns = ""; private $sColumns = ""; private $sSearch = array(); private $sRangeSeparator = "~"; /** * Copies an instance of CI */ public function __construct() { $this->ci =& get_instance(); } /** * If you establish multiple databases in config/database.php this will allow you to * set the database (other than $active_group) - more info: http://ellislab.com/forums/viewthread/145901/#712942 */ public function set_database($db_name) { $db_data = $this->ci->load->database($db_name, TRUE); $this->ci->db = $db_data; } /** * Generates the SELECT portion of the query * * @param string $columns * @param bool $backtick_protect * @return mixed */ public function select($columns, $backtick_protect = TRUE) { $this->backtick_protect = $backtick_protect; $this->sets_columns = $columns; foreach($this->explode(',', $columns) as $val) { $column = trim(preg_replace('/(.*)\s+as\s+(\w*)/i', '$2', $val)); $this->columns[] = $column; $this->select[$column] = trim(preg_replace('/(.*)\s+as\s+(\w*)/i', '$1', $val)); } $this->ci->db->select($columns, $backtick_protect); return $this; } public function setsColumns($sColumns){ $this->sColumns = $sColumns; return $this; } public function setFilter($column, $value){ $this->sSearch[$column] = $value; return $this; } /** * Generates the DISTINCT portion of the query * * @param string $column * @return mixed */ public function distinct($column) { $this->distinct = $column; $this->ci->db->distinct($column); return $this; } /** * Generates a custom GROUP BY portion of the query * * @param string $val * @return mixed */ public function group_by($val) { $this->group_by[] = $val; $this->ci->db->group_by($val); return $this; } /** * Generates the FROM portion of the query * * @param string $table * @return mixed */ public function from($table) { $this->table = $table; return $this; } /** * Generates the JOIN portion of the query * * @param string $table * @param string $fk * @param string $type * @return mixed */ public function join($table, $fk, $type = NULL) { $this->joins[] = array($table, $fk, $type); $this->ci->db->join($table, $fk, $type); return $this; } /** * Generates the WHERE portion of the query * * @param mixed $key_condition * @param string $val * @param bool $backtick_protect * @return mixed */ public function where($key_condition, $val = NULL, $backtick_protect = TRUE) { $this->where[] = array($key_condition, $val, $backtick_protect); $this->ci->db->where($key_condition, $val, $backtick_protect); return $this; } /** * Generates the HAVING portion of the query * * @param mixed $key_condition * @param string $val * @param bool $backtick_protect * @return mixed */ public function having($key_condition, $val = NULL, $backtick_protect = TRUE) { $this->having[] = array($key_condition, $val, $backtick_protect); $this->ci->db->having($key_condition, $val, $backtick_protect); return $this; } /** * Generates the WHERE portion of the query * * @param mixed $key_condition * @param string $val * @param bool $backtick_protect * @return mixed */ public function or_where($key_condition, $val = NULL, $backtick_protect = TRUE) { $this->or_where[] = array($key_condition, $val, $backtick_protect); $this->ci->db->or_where($key_condition, $val, $backtick_protect); return $this; } /** * Generates the WHERE portion of the query * * @param mixed $key_condition * @param string $val * @param bool $backtick_protect * @return mixed */ public function filter($key_condition, $val = NULL, $backtick_protect = TRUE) { $this->filter[] = array($key_condition, $val, $backtick_protect); return $this; } /** * Generates a %LIKE% portion of the query * * @param mixed $key_condition * @param string $val * @param bool $backtick_protect * @return mixed */ public function like($key_condition, $val = NULL, $backtick_protect = TRUE) { $this->like[] = array($key_condition, $val, $backtick_protect); $this->ci->db->like($key_condition, $val, $backtick_protect); return $this; } /** * Sets additional column variables for adding custom columns * * @param string $column * @param string $content * @param string $match_replacement * @return mixed */ public function add_column($column, $content, $match_replacement = NULL) { $this->add_columns[$column] = array('content' => $content, 'replacement' => $this->explode(',', $match_replacement)); return $this; } /** * Sets additional column variables for editing columns * * @param string $column * @param string $content * @param string $match_replacement * @return mixed */ public function edit_column($column, $content, $match_replacement) { $this->edit_columns[$column][] = array('content' => $content, 'replacement' => $this->explode(',', $match_replacement)); return $this; } /** * Unset column * * @param string $column * @return mixed */ public function unset_column($column) { $column=explode(',',$column); $column2 = array(); foreach ($column as $key => $value) { $column2[$value] = $value; } $this->unset_columns=array_merge($this->unset_columns,$column2); return $this; } /** * Builds all the necessary query segments and performs the main query based on results set from chained statements * * @param string $output * @param string $charset * @return string */ public function generate($output = 'json', $charset = 'UTF-8') { //if(strtolower($output) == 'json') // $this->get_paging(); //$this->get_ordering(); //$this->get_filtering(); return $this->produce_output(strtolower($output), strtolower($charset)); } /** * Generates the LIMIT portion of the query * * @return mixed */ private function get_paging() { $iStart = $this->ci->input->post('start'); $iLength = $this->ci->input->post('length'); if($iLength != '' && $iLength != '-1') $this->ci->db->limit($iLength, ($iStart)? $iStart : 0); } /** * Generates the ORDER BY portion of the query * * @return mixed */ private function get_ordering() { $Data = $this->ci->input->post('columns'); if ($this->ci->input->post('order')){ foreach ($this->ci->input->post('order') as $key){ if($this->check_cType()){ $this->ci->db->order_by($Data[$key['column']]['data'], $key['dir']); }else{ $this->ci->db->order_by($this->columns[$key['column']] , $key['dir']); } } } } /** * Generates a %LIKE% portion of the query * * @return mixed */ protected function get_filtering_where() { /* if($this->check_cType()) $mColArray = $this->check_cType(); else */ if( $this->sColumns != "" ) $mColArray = explode(',', $this->sColumns); elseif($this->ci->input->post('sColumns')) $mColArray = explode(',', $this->ci->input->post('sColumns')); else $mColArray = $this->columns; foreach ($mColArray as $col) { if( strpos($col, ".") === false ){ $cols[] = $col; }else{ $cols[] = substr($col, strpos($col,".")+1); } } $sWhere = ""; $sSearch = addslashes($this->ci->input->post('sSearch')); $mColArray = array_values(array_diff($mColArray, $this->unset_columns)); $columns = array_values(array_diff($this->columns, $this->unset_columns)); if($sSearch != ''){ for($i = 0; $i < count($mColArray); $i++){ if($this->ci->input->post('bSearchable_' . $i) == 'true' && in_array($mColArray[$i], $columns)){ $column_to_search = $this->ci->input->post('mDataProp_' . $i)?$this->ci->input->post('mDataProp_' . $i):$cols[$i]; $column_to_search = "`$column_to_search`"; $sWhere .= $column_to_search . " LIKE '%" . $sSearch . "%' OR "; } } } $sWhere = substr_replace($sWhere, "", -3); if($sWhere != ""){ $sWhere = "(" . $sWhere . ")"; } $sRangeSeparator = $this->ci->input->post('sRangeSeparator')?$this->ci->input->post('sRangeSeparator'):$this->sRangeSeparator; $sWhere2 = array(); //print_r($mColArray);echo "\n"; for($i = 0; $i < intval($this->ci->input->post('iColumns')); $i++) { $column_to_search = $this->ci->input->post('mDataProp_' . $i)?$this->ci->input->post('mDataProp_' . $i):$cols[$i]; //echo $i." ) "; //echo $column_to_search." == ("; $searchColumn = array_key_exists($column_to_search, $this->sSearch)?$this->sSearch[$column_to_search]:$this->ci->input->post('sSearch_' . $i); //echo $searchColumn.")\n"; $column_to_search = "`$column_to_search`"; if($searchColumn && $searchColumn != '' && in_array($mColArray[$i], $columns)) { $miSearch = explode(',', $searchColumn); foreach($miSearch as $val) { if(preg_match("/(<=|>=|=|<|>)(\s*)(.+)/i", trim($val), $matches)){ $sWhere2[] = $column_to_search." ".$matches[1]." '".$matches[3]."'"; } elseif(!empty($sRangeSeparator) && preg_match("/(.*)$sRangeSeparator(.*)/i", trim($val), $matches)) { $rangeQuery = ''; $dateSQL = '%Y-%m-%d %H:%i:%s'; if(!empty($matches[1])) $rangeQuery = ' STR_TO_DATE(' . $column_to_search . ",'$dateSQL') >= STR_TO_DATE('" . $matches[1] . " 00:00:00','$dateSQL') "; if(!empty($matches[2])) $rangeQuery .= (!empty($rangeQuery)? ' AND ': '') . ' STR_TO_DATE('. $column_to_search . ",'$dateSQL') <= STR_TO_DATE('" . $matches[2] . " 23:59:59','$dateSQL') "; if(!empty($matches[1]) || !empty($matches[2])) $sWhere2[] = $rangeQuery; } else{ $sWhere2[] = $column_to_search." LIKE '%".$val."%'"; } } } } //print_r($sWhere2); //die(); $whereClause = ""; if( $sWhere != "" && count($sWhere2) > 0 ){ $whereClause = "WHERE ".$sWhere." AND (".implode(" AND ", $sWhere2).")"; }else{ if($sWhere != ""){ $whereClause = "WHERE ".$sWhere; } else if(count($sWhere2) > 0){ $whereClause = "WHERE (".implode(" AND ", $sWhere2).")"; } } return $whereClause; } /** * Compiles the select statement based on the other functions called and runs the query * * @return mixed */ private function get_display_result() { $sql_query = $this->get_query(true, true); $query = $this->ci->db->query($sql_query); return $query; } /** * Builds an encoded string data. Returns JSON by default, and an array of aaData if output is set to raw. * * @param string $output * @param string $charset * @return mixed */ private function produce_output($output, $charset) { $aaData = array(); $rResult = $this->get_display_result(); if($output == 'json') { $iTotal = $this->get_total_results(); $iFilteredTotal = $this->get_total_results(TRUE); } foreach($rResult->result_array() as $row_key => $row_val) { $aaData[$row_key] = ($this->check_cType())? $row_val : array_values($row_val); foreach($this->add_columns as $field => $val) if($this->check_cType()) $aaData[$row_key][$field] = $this->exec_replace($val, $aaData[$row_key]); else $aaData[$row_key][] = $this->exec_replace($val, $aaData[$row_key]); foreach($this->edit_columns as $modkey => $modval) foreach($modval as $val) $aaData[$row_key][($this->check_cType())? $modkey : array_search($modkey, $this->columns)] = $this->exec_replace($val, $aaData[$row_key]); $aaData[$row_key] = array_diff_key($aaData[$row_key], ($this->check_cType())? $this->unset_columns : array_intersect($this->columns, $this->unset_columns)); $aaData[$row_key] = array_diff_key($aaData[$row_key], $this->unset_columns ); if(!$this->check_cType()) $aaData[$row_key] = array_values($aaData[$row_key]); } $sColumns = array_diff($this->columns, $this->unset_columns); $sColumns = array_merge_recursive($sColumns, array_keys($this->add_columns)); if($output == 'json') { $sOutput = array ( 'sEcho' => intval($this->ci->input->post('sEcho')), //'draw' => intval($this->ci->input->post('draw')), //'recordsTotal' => $iTotal, //'recordsFiltered' => $iFilteredTotal, 'data' => $aaData, 'sColumns' => implode(',', $sColumns), 'iTotalRecords' => $iTotal, 'iTotalDisplayRecords' => $iFilteredTotal, ); if($charset == 'utf-8') return json_encode($sOutput); else return $this->jsonify($sOutput); } else return array('aaData' => $aaData); } /** * Get result count * * @return integer */ private function get_total_results($filtering = FALSE) { $sql_query = $this->get_query($filtering); $query = $this->ci->db->query($sql_query); return $query->num_rows(); } private function get_query($filtering = FALSE, $paging = false) { $this->ci->db->_reset_select(); $this->ci->db->select($this->sets_columns, $this->backtick_protect); foreach($this->joins as $val) $this->ci->db->join($val[0], $val[1], $val[2]); foreach($this->where as $val) $this->ci->db->where($val[0], $val[1], $val[2]); foreach($this->or_where as $val) $this->ci->db->or_where($val[0], $val[1], $val[2]); foreach($this->group_by as $val) $this->ci->db->group_by($val); foreach($this->like as $val) $this->ci->db->like($val[0], $val[1], $val[2]); if(strlen($this->distinct) > 0) { $this->ci->db->distinct($this->distinct); $this->ci->db->select($this->columns); } foreach($this->having as $val) $this->ci->db->having($val[0], $val[1], $val[2]); if ($this->table != '') { $this->ci->db->from($this->table); } $from = $this->ci->db->_compile_select(); foreach ($this->columns as $col) { if( strpos($col, ".") === false ){ $cols[] = $col; }else{ $cols[] = substr($col, strpos($col,".")+1); } } $whereClause = ""; if($filtering) $whereClause = $this->get_filtering_where(); $orderClause = ""; if($paging){ if( $this->sColumns != "" ) $orderData = explode(',', $this->sColumns); else $orderData = array_values(array_diff($cols, $this->unset_columns)); if( isset($_POST['iSortCol_0']) ){ $order_col = $this->ci->input->post('iSortCol_0'); $order_dir = $this->ci->input->post('sSortDir_0'); $orderClause = " ORDER BY ".$orderData[$order_col]." ".$order_dir." "; } } $limitClause = ""; if($paging){ $iStart = $this->ci->input->post('iDisplayStart'); $iLength = $this->ci->input->post('iDisplayLength'); if($iLength != '' && $iLength != '-1') $limitClause = " LIMIT ".(($iStart)? $iStart : 0).", ".$iLength; } $sql_query = "select ".implode(",", $cols)." FROM ( ".$from." ) tbala_do_datatables ".$whereClause." ".$orderClause." ".$limitClause; return $sql_query; } /** * Runs callback functions and makes replacements * * @param mixed $custom_val * @param mixed $row_data * @return string $custom_val['content'] */ private function exec_replace($custom_val, $row_data) { $replace_string = ''; if(isset($custom_val['replacement']) && is_array($custom_val['replacement'])) { foreach($custom_val['replacement'] as $key => $val) { $sval = preg_replace("/(?<!\w)([\'\"])(.*)\\1(?!\w)/i", '$2', trim($val)); if(preg_match('/(\w+::\w+|\w+)\((.*)\)/i', $val, $matches) && is_callable($matches[1])) { $func = $matches[1]; $args = preg_split("/[\s,]*\\\"([^\\\"]+)\\\"[\s,]*|" . "[\s,]*'([^']+)'[\s,]*|" . "[,]+/", $matches[2], 0, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE); foreach($args as $args_key => $args_val) { $args_val = preg_replace("/(?<!\w)([\'\"])(.*)\\1(?!\w)/i", '$2', trim($args_val)); $args[$args_key] = (in_array($args_val, $this->columns))? ($row_data[($this->check_cType())? $args_val : array_search($args_val, $this->columns)]) : $args_val; } $replace_string = call_user_func_array($func, $args); } elseif(in_array($sval, $this->columns)) $replace_string = $row_data[($this->check_cType())? $sval : array_search($sval, $this->columns)]; else $replace_string = $sval; $custom_val['content'] = str_ireplace('$' . ($key + 1), $replace_string, $custom_val['content']); } } return $custom_val['content']; } /** * Check column type -numeric or column name * * @return bool */ private function check_cType() { if(!$this->ci->input->post('mDataProp_0')) return FALSE; for($i = 0; $i < intval($this->ci->input->post('iColumns')); $i++) if(!is_numeric($this->ci->input->post('mDataProp_' . $i))) return TRUE; return FALSE; } /** * Return the difference of open and close characters * * @param string $str * @param string $open * @param string $close * @return string $retval */ private function balanceChars($str, $open, $close) { $openCount = substr_count($str, $open); $closeCount = substr_count($str, $close); $retval = $openCount - $closeCount; return $retval; } /** * Explode, but ignore delimiter until closing characters are found * * @param string $delimiter * @param string $str * @param string $open * @param string $close * @return mixed $retval */ private function explode($delimiter, $str, $open = '(', $close=')') { $retval = array(); $hold = array(); $balance = 0; $parts = explode($delimiter, $str); foreach($parts as $part) { $hold[] = $part; $balance += $this->balanceChars($part, $open, $close); if($balance < 1) { $retval[] = implode($delimiter, $hold); $hold = array(); $balance = 0; } } if(count($hold) > 0) $retval[] = implode($delimiter, $hold); return $retval; } /** * Workaround for json_encode's UTF-8 encoding if a different charset needs to be used * * @param mixed $result * @return string */ private function jsonify($result = FALSE) { if(is_null($result)) return 'null'; if($result === FALSE) return 'false'; if($result === TRUE) return 'true'; if(is_scalar($result)) { if(is_float($result)) return floatval(str_replace(',', '.', strval($result))); if(is_string($result)) { static $jsonReplaces = array(array('\\', '/', '\n', '\t', '\r', '\b', '\f', '"'), array('\\\\', '\\/', '\\n', '\\t', '\\r', '\\b', '\\f', '\"')); return '"' . str_replace($jsonReplaces[0], $jsonReplaces[1], $result) . '"'; } else return $result; } $isList = TRUE; for($i = 0, reset($result); $i < count($result); $i++, next($result)) { if(key($result) !== $i) { $isList = FALSE; break; } } $json = array(); if($isList) { foreach($result as $value) $json[] = $this->jsonify($value); return '[' . join(',', $json) . ']'; } else { foreach($result as $key => $value) $json[] = $this->jsonify($key) . ':' . $this->jsonify($value); return '{' . join(',', $json) . '}'; } } } /* End of file Datatables.php */ /* Location: ./application/libraries/Datatables.php */
💾 Save
Cancel
Save as UTF-8, newlines normalized to LF