<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Datatables{
    public function __construct(){
        $this->CI =& get_instance();
    }
    public function fill($tables,$fields,$dataTable,$conditons = null){
		/** Init variables */
		$filtered_records = 0;
		$records = array();
		$all_records = 0;
		/**Structure query SELECT */
		$querySELECT = $this->makeSELECT($fields);
		/**Structure query FROM */
		$mainTable = $tables;
		if(is_array($mainTable)){
			$mainTable = $mainTable["main"];
		}
		$queryFROM = " FROM ".$mainTable;
		/**Structure query JOINS */
		$queryJOIN = "";
		if(is_array($tables) && isset($tables["joins"]) && is_array($tables["joins"])){
			$tablesJoins = $tables["joins"];
			if(count($tablesJoins)){
				foreach($tablesJoins as $tableJoin => $dataJoin){
					$queryJOIN .= " ".$dataJoin['type']." JOIN ".$tableJoin." ON ".$dataJoin['matchMainTable']." = ".$dataJoin['matchJoinTable'];
				}
			}
			$queryJOIN = ($queryJOIN !="" ? $queryJOIN." " : "");
		}
		/**Structure query WHERE */
		$queryWHERE = "";
		if(isset($dataTable["search"]) && $dataTable["search"]["value"] != ""){
			$whereStr = "";
			foreach($dataTable["columns"] as $column){
				if($column["searchable"] == "true"){
					$searchableField = $fields[$column["name"]];
					if(is_array($searchableField)){
						$searchableField = $searchableField['dbField'];
					}
					$whereStr .= $searchableField." LIKE '%".$dataTable["search"]["value"]."%' OR ";
				}
			}
			if($whereStr != ""){
				$whereStr = mb_substr($whereStr, 0, -3);
				$queryWHERE = " WHERE (".$whereStr.")";
			}
		}
		/** Aditional WHERE conditions */
		if($conditons && is_array($conditons)){
			$aditionalWheres = "";
			if(count($conditons)){
				foreach($conditons as $condition => $condition_val){
					if(strpos($condition, '!=') !== false){
						$condition_stmt = explode("!=",$condition)[0]." != '".$condition_val."'";
					}
					elseif(strpos($condition, '>=') !== false){
						$condition_stmt = explode(">=", $condition)[0]." >= '".$condition_val."'";
					}
					elseif(strpos($condition, '>') !== false){
						$condition_stmt = explode(">",$condition)[0]." > '".$condition_val."'";
					}
					elseif(strpos($condition, '<=') !== false){
						$condition_stmt = explode("<=", $condition)[0]." <= '".$condition_val."'";
					}
					elseif(strpos($condition, '<') !== false){
						$condition_stmt = explode("<", $condition)[0]." < '".$condition_val."'";
					}
					else if(strpos($condition, '=') !== false){
						$condition_stmt = explode("=", $condition)[0]." = '".$condition_val."'";
					}else{
						$condition_stmt = trim($condition)." = '".$condition_val."'";
					}
					$aditionalWheres .= $condition_stmt." AND ";
				}
			}
			if($aditionalWheres != ""){
				$aditionalWheres = mb_substr($aditionalWheres, 0, -4);
				$queryWHERE .= ($queryWHERE != "" ? " AND ".$aditionalWheres : " WHERE ".$aditionalWheres)." ";
			}
		}
		/**Structure query ORDER */
		$queryORDER = "";
		if(isset($dataTable["order"]) && $dataTable["columns"]){
			$orderColumn = $fields[$dataTable["columns"][$dataTable["order"][0]["column"]]["name"]];
			if(is_array($orderColumn)){
				$orderColumn = $orderColumn['dbField'];
			}
			$orderDir = $dataTable["order"][0]["dir"];
			$queryORDER = " ORDER BY ".$orderColumn." ".$orderDir;
		}
		/**Structure query LIMIT */
		$queryLIMIT = "";
		if(isset($dataTable["length"]))
			$queryLIMIT = " LIMIT ".$dataTable["length"];

		/**Structure query OFFSET */
		$queryOFFSET = "";
		if(isset($dataTable["start"]))
			$queryOFFSET = " OFFSET ".$dataTable["start"];

		/** Query total */
		$total_records = $this->CI->Generic_model->sql_custom("SELECT count(*) as records".$queryFROM.$queryJOIN);
		$total_records = (($total_records && count($total_records)) ? $total_records[0]->records : 0);
		/** Query Filtered */
		$filtered_records = $this->CI->Generic_model->sql_custom("SELECT count(*) records".$queryFROM.$queryJOIN.$queryWHERE);
		$filtered_records = (($filtered_records && count($filtered_records)) ? $filtered_records[0]->records : 0);
		/**Query data*/
		$querySQL = $querySELECT.$queryFROM.$queryJOIN.$queryWHERE.$queryORDER.$queryLIMIT.$queryOFFSET;
		$query_records = $this->CI->Generic_model->sql_custom($querySQL); 
		/*echo $this->CI->db->last_query($querySQL);
		echo "<hr>";*/
		$data = $this->formtat_output($query_records, $fields, (isset($dataTable["DT_RowId"])? $dataTable["DT_RowId"]: null));
		return array(
			"draw"=> $dataTable["draw"],
			"recordsTotal"=>$total_records,
			"recordsFiltered"=>$filtered_records,
			"data"=>$data
		);

	}
	/** */
	private function makeSELECT($fields){
		$selectStr = "";
		if(is_array($fields) && count($fields)){
			foreach($fields as $fieldAlias => $field){
				/**Get a table field name */
				$table_field = "";
				if(is_array($field)){
					if(isset($field["dbField"])){
						$table_field = $field["dbField"];
					}
				}else{
					$table_field = $field;
				}
				/**Get table.field fields */
				if($table_field != ""){
					if(strpos($table_field, '.') !== false){
						$field_el = explode(".",$table_field);
						$table_field = "`".$field_el[0]."`."."`".$field_el[1]."`";
					}else{
						$table_field = "`".$table_field."`";
					}
					$selectStr .= $table_field." AS `".$fieldAlias."`, ";
				}
			}
			$selectStr = mb_substr($selectStr, 0, -2);
		}
		$querySELECT = "SELECT ".($selectStr != "" ? $selectStr : " * ");
		return $querySELECT;
	}
	/** */
	private function formtat_output($data,$fields,$DT_RowId = null){
		$records = array();
		if($data && count($data)){
			for($i = 0, $iend = count($data); $i < $iend; $i ++){
				$row = (array) $data[$i];
				$recordData = array();
				foreach($fields as $kField => $field){
					if(isset($field["format"])){
						$recordData[$kField] = call_user_func_array($field["format"], array($row[$kField], $row));
					}
					else{
						$recordData[$kField] = $row[$kField];
					}
					/**Add DT_RowId */
					if($DT_RowId && is_array($DT_RowId)){
						if(isset($DT_RowId["field"]) && $DT_RowId["field"] == $kField){
							$recordData["DT_RowId"] = $row[$kField];
							if(isset($DT_RowId["format"])){
								$recordData["DT_RowId"] = call_user_func_array($DT_RowId["format"], array($row[$kField], $row));
							}
						}
					}
				}
				$records[] = $recordData;
			}
		}
		return $records;
	}
}