<?php
class Mcar extends CI_Model {
	var $table = 'car';
    var $column_order = array('car_id',null,'car_no','company_name','make','model_no','body','compliance_date','vin','engine','fuel','network','colour','silver_service','camera','registration_expiry_date','registration_due_month','plate_type','car_status','terminal_no'); //set column field database for datatable orderable
    var $column_search = array('car_no','company_name','make','model_no','body','compliance_date','vin','engine','fuel','network','colour','IF(silver_service=1,"Yes","NO")','camera','registration_expiry_date','registration_due_month','plate_type','car_status','terminal_no'); //set column field database for datatable searchable just firstname , lastname , address are searchable
    var $order = array('car_id' => 'desc'); // default order 
    public function __construct() {
        parent::__construct();
    }
	private function _get_datatables_query(){
		// $this->db->select('car.*,IF(silver_service=1,"Yes","NO") silver_service,DATE_FORMAT(car.compliance_date, "%d/%m/%Y") compliance_date,DATE_FORMAT(car.registration_expiry_date, "%d/%m/%Y") registration_expiry_date,DATE_FORMAT(car.registration_due_month, "%d/%m/%Y") registration_due_month');
        $this->db->select('car.*,IF(silver_service=1,"Yes","NO") silver_service,compliance_date,registration_expiry_date,registration_due_month');
        $this->db->from('car');
		
        $i = 0;
        foreach ($this->column_search as $item){ // loop column 
			//echo $_POST['search']['value'];exit;
            if($_POST['search']['value']){ // if datatable send POST for search                 
                if($i===0){ // first loop
                    $this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
                    $this->db->like($item, $_POST['search']['value']);
                }
                else{
                    $this->db->or_like($item, $_POST['search']['value']);
                }
                if(count($this->column_search) - 1 == $i) //last loop
                $this->db->group_end(); //close bracket
            }
            $i++;
        }
         
        if(isset($_POST['order'])){ // here order processing
            $this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
        }else if(isset($this->order)){
            $order = $this->order;
            $this->db->order_by(key($order), $order[key($order)]);
        }
    }
	
	public function get_datatables(){   
        $this->_get_datatables_query();
        if($_POST['length'] != -1)
        $this->db->limit($_POST['length'], $_POST['start']);
        $query = $this->db->get();
       // echo $this->db->last_query();die;
        return $query->result();
    }
	
	public function count_filtered(){
        $this->_get_datatables_query();
        $query = $this->db->get();
        return $query->num_rows();
    }
	
	public function count_all(){
        $this->db->from($this->table);      
        return $this->db->count_all_results();
    }
	
	public function get_details($car_id){
		$this->db->select('car.*,IF(silver_service=1,"Yes","NO") silver_service,DATE_FORMAT(car.compliance_date, "%d/%m/%Y") compliance_date,DATE_FORMAT(car.registration_expiry_date, "%d/%m/%Y") registration_expiry_date,DATE_FORMAT(car.registration_due_month, "%d/%m/%Y") registration_due_month,DATE_FORMAT(car.dob, "%d/%m/%Y") dob,customer.email,CASE WHEN customer.customer_type ="B" THEN customer.business_name ELSE ifnull(CONCAT(customer.first_name," ",CASE WHEN customer.middle_name IS NULL THEN "" ELSE  CONCAT(customer.middle_name," ") END,ifnull(customer.last_name,"")),"") END as name,ifnull(customer.mobile,"") as contact_no,get_full_address_customer(car.customer_id) as full_address');
        $this->db->from($this->table);
        $this->db->join('customer','customer.customer_id = car.customer_id','inner');
        $this->db->where('car.car_id',$car_id);
        $query=$this->db->get();
        return $query->row_array();
    }
	
	public function update($condition,$data){
		$this->db->where($condition);
		$this->db->update($this->table,$data);
		return 1;
	}
	
	public function get_parent_category(){
		$this->db->select('*');
        $this->db->from($this->table); 	
        $this->db->where('parent_id=0');
        $query=$this->db->get();
        return $query->result_array();
	}
	
	public function get_sub_category($category_id){		
        $this->db->select('*');
        $this->db->from($this->table);
        $this->db->where('parent_id',$category_id);		
        $query=$this->db->get();
        return $query->result_array();
    }
	
	public function add($data){
        $this->db->insert($this->table,$data);
        return $this->db->insert_id();
    }
	
	public function get_category($category_id){	
        $this->db->select('*');
        $this->db->from($this->table);
        $this->db->where('category_id',$category_id);		
        $query=$this->db->get();
        return $query->row_array();
	}
	
	public function check_category_exsits($category_name,$parent_id){
		$this->db->select('*');
        $this->db->from($this->table);
        $this->db->where('parent_id',$parent_id);		
		 $this->db->where('category_name',$category_name);	
        $query=$this->db->get();
		if($query->result())
		{
			return 1;
		}else{
			return 0;
		}
	}
	
	public function delete($condition){
        $this->db->delete($this->table,$condition);
        return 1;
    }
	
	public function active($condition,$data){
        $this->db->where($condition);
        $this->db->update($this->table,$data);
        return 1;
    }
	
	 public function getRow($table,$condition){
        $this->db->where($condition);
        $query=$this->db->get($table);
        return $query->row_array();
    } 
    
    public function getDetails($table,$condition,$order_by = null){
        $this->db->where($condition);
        if($order_by){
            $this->db->order_by($order_by,'ASC');
        }
        $query=$this->db->get($table);
        //echo $this->db->last_query(); die;
        return $query->result_array();
    } 


    public function getcustomerDetails($term){

        $sql = "SELECT * FROM customer WHERE status = 1 AND (first_name LIKE '%".$term."%' OR mobile LIKE '%".$term."%')";
		$query = $this->db->query($sql);
		$rows = $query->result_array();	
        //$num = $query->num_rows();
        
        return $rows;

    }

    public function customerDetails($custid){

        $sql = "SELECT * FROM customer WHERE customer_id = '".$custid."'";
		$query = $this->db->query($sql);
		$row = $query->row_array();	
        //$num = $query->num_rows();
        
        return $row;

    }

}
?>