<?php namespace App\Models\Project;

class DatatableModel{
	private $db;
	private $table;
	private $select_columns = [];
	private $searchable_columns = [];
	private $sortable_columns = [];
	private $default_sort;
	public $dt_builder;	
	private $post_data;
	
    public function __construct($db)
    {
        $this->db = $db;
		$this->post_data = $_POST;
    }
	
	public function set_table($tbl_name){
		$this->table = $tbl_name;
	}
	
	public function set_column($column_name, $rules_str, $alias=""){
		$rules_arr = explode('|',strtolower($rules_str));
		
		$select_column_part = explode(".",$column_name);	
		if(in_array('date',$rules_arr)){
			$select_column = "DATE_FORMAT(".$column_name.",'%d-%m-%Y') AS ";
			$select_column .= $alias ?: (count($select_column_part) > 1 ? $select_column_part[1] : $column_name);
		}else{
			$select_column = $column_name;
			$select_column .= $alias!="" ? " AS ".$alias : "";
		}		
		
		$this->select_columns[] = $select_column;
		
		if(in_array('asc', $rules_arr)){
			$this->default_sort['column'] = $column_name;
			$this->default_sort['order'] = "ASC";
		}
		
		if(in_array('desc', $rules_arr)){
			$this->default_sort['column'] = $column_name;
			$this->default_sort['order'] = "DESC";
		}
		
		if(in_array('sort', $rules_arr)){
			if($alias!=""){
				$column = $alias;
			}else if(count($select_column_part) > 1){
				$column = $select_column_part[1];
			}else{
				$column = $column_name;
			}
			$this->sortable_columns[$column] = $column_name;
		}
		
		if(in_array('search', $rules_arr)){
			$this->searchable_columns[] = $column_name;
		}		
	}
	
	public function get_builder(){		 
		return $this->set_criteria();
	}
	
	public function get_data($builder){	
		$data['total_records'] = $this->get_total_records(clone $builder);
		$data['data'] = $this->get_list(clone $builder);				
		$data['meta'] = $this->get_meta($data['total_records']);		
		return $data;
	}
	
	private function get_total_records($builder){
        return $builder->countAllResults();
	}
	
	private function get_meta($total_records){
		$meta = array();
		$meta["page"] = $this->post_data['pagination']['page'];
		$meta["pages"] = ceil($total_records / $this->post_data['pagination']['perpage']);
		$meta["perpage"] = $this->post_data['pagination']['perpage'];
		$meta["total"] = $total_records;
		$meta["sort"] = $this->post_data['sort']['sort'] ?? $this->default_sort ?? $this->default_sort['order'];
		$meta["field"] = $this->post_data['sort']['field'] ?? $this->default_sort ?? $this->default_sort['column'];
		if(isset($this->post_data['pagination']['total'])){
			if($this->post_data['pagination']['total'] != $total_records){
				$meta["page"] = $meta["pages"] > 1 ? 1 : 0;
			}
		}
		return $meta;
	}
	
	private function set_criteria(){
		$builder = $this->db->table($this->table);
		if(isset($this->post_data['query']) && ! empty($this->post_data['query'])){
			foreach($this->post_data['query'] as $search_type => $search_text){
				if($search_type=="generalSearch" && $search_text!=""){
					$i = 0;		        
					// loop searchable columns
					foreach($this->searchable_columns as $column){	
						// first loop
						if($i===0){
							// open bracket
							$builder->groupStart();
							$builder->like($column, $search_text);
						}else{
							$builder->orLike($column, $search_text);
						}
						
						// last loop
						if(count($this->searchable_columns) - 1 == $i){
							// close bracket
							$builder->groupEnd();
						}
						$i++;
					} 
				}else if(in_array($search_type, $this->searchable_columns) && $search_text!=""){
					$builder->where($search_type, $search_text);
				}
			}
		}
		return $builder;
	}
	
	private function get_list($builder){
		$builder->select(implode(',',$this->select_columns));
		// Order List
		if(isset($this->post_data['sort']['field']) && isset($this->sortable_columns[$this->post_data['sort']['field']])){
            $builder->orderBy($this->sortable_columns[$this->post_data['sort']['field']], strtoupper($this->post_data['sort']['sort']));
        }else if(isset($this->default_sort)){
            $builder->orderBy($this->default_sort['column'], strtoupper($this->default_sort['order']));
        }
		// Limit data
		$offset = ($this->post_data['pagination']['page'] - 1) * $this->post_data['pagination']['perpage'];
		if ($offset < 0) {
			$offset = 0;
		}
        if($this->post_data['pagination']['perpage'] != -1){
            $builder->limit($this->post_data['pagination']['perpage'], $offset);
        }
		//$builder->getCompiledSelect();
        $query = $builder->get();
        $rec_list = $query->getResultArray();	
		return $this->get_structured_list($rec_list);
	}
	
	private function get_structured_list($rec_list){
		$offset = ($this->post_data['pagination']['page'] - 1) * $this->post_data['pagination']['perpage'];
		if ($offset < 0) {
			$offset = 0;
		}
		$i = $offset;
		$data = array();
		if($rec_list){
			foreach($rec_list as $rec_key => $rec_dtl){
				$i++;	
				$data[$rec_key] = $rec_dtl;
				$data[$rec_key]['SrNo'] = $i;
			}
		}		
		return $data;
	}
}