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