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

class Minvoice extends CI_Model {
	var $table = 'invoices';	
    var $column_order = array('invoices.id','invoices.id','STR_TO_DATE(invoices.date_of_creation,"%Y-%m-%d %H:%i:%s")','STR_TO_DATE(invoices.due_date,"%Y-%m-%d")','invoices.car_no','invoices.total_amount',NULL,'invoices.customer_name','customer.mobile',NULL,'invoices.is_paid',NULL); //set column field database for datatable orderable
    var $column_search = array('invoices.invoice_no','invoices.date_of_creation','invoices.car_no','invoices.total_amount','invoices.customer_name'); //set column field database for datatable searchable just firstname , lastname , address are searchable

    //var $order = array('invoices.id' => 'desc'); // default order 
    var $order = array('invoices.id' => 'desc');
    public function __construct() {
        parent::__construct();
    }
	private function _get_datatables_query($params=NULL){
		$this->db->select('invoices.*,car.contact_no,customer.mobile');
		$this->db->join('car','car.car_id = invoices.car_id','left');
		$this->db->join('customer','customer.customer_id = car.customer_id','left');
        $this->db->from($this->table);	
         $this->db->where('invoices.delete_flag',0);
		//$this->db->join('car','car.car_id=job.car_id','left');	
		 
		if(!empty($params))
        {
            if(!empty($params['customer_name']))
            {
                $this->db->where('invoices.customer_name',$params['customer_name']);
            }
            if(!empty($params['car_id']))
            {
                $this->db->where('invoices.car_id',$params['car_id']);
            }
            if(!empty($params['payment_mode']))
            {
                $this->db->where('invoices.payment_mode',$params['payment_mode']);
            }
            if($params['is_paid'] != '')
            {
                if($params['is_paid'] == 4){
                    
                    $this->db->where('"'.date("Y-m-d").'" > STR_TO_DATE(invoices.due_date,"%Y-%m-%d") AND  invoices.is_paid <> 1',null,false);
                }else{
                    $this->db->where('invoices.is_paid',$params['is_paid']);
                }
                    
            }
            
            

        }
        $i = 0;
        foreach ($this->column_search as $item){ // loop column		
            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++;
        }
        // $this->db->order_by('FIELD(invoices.is_paid,"0","2","1") ASC',null,false);
        // $this->db->order_by('invoices.due_date','ASC');
        if($_POST['search']['value']){
            $this->db->OR_WHERE('(CASE WHEN invoices.is_paid = 0 THEN "Unpaid" WHEN invoices.is_paid = 1 THEN "Paid" WHEN invoices.is_paid = 3 THEN "No Recovery" ELSE "Partially Paid" END LIKE "%'.$_POST["search"]["value"].'%")', null,false);
        }
         
        if(isset($_POST['order'])){ // here order processing
            $this->db->order_by($this->column_order[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'], null,false);
        }else if(isset($this->order)){
            $order = $this->order;
            $this->db->order_by(key($order), $order[key($order)]);
        }
    }
	
	
	public function get_datatables($params=NULL){
		$this->_get_datatables_query($params);
        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($params=NULL){
		$this->_get_datatables_query($params);		
        $query = $this->db->get();
        return $query->num_rows();
    }
	

	public function count_all($params=NULL){
		// $this->db->select('job.job_id,car.car_no,job.service,job.job_status');
  //       $this->db->from($this->table);		
		// $this->db->join('car','car.car_id=job.car_id','left');	
        $this->db->select('invoices.*');	
        $this->db->from($this->table);
		if(!empty($params))
        {
            if(!empty($params['customer_name']))
            {
                $this->db->where('invoices.customer_name', $params['customer_name']);
            }
            if(!empty($params['car_id']))
            {
                $this->db->where('invoices.car_id',$params['car_id']);
            }

        }
        return $this->db->count_all_results();
    }
	
	
		private function _get_datatables_query_deleted_invoice($params=NULL){
		$this->db->select('invoices.*,car.contact_no,customer.mobile');
		$this->db->join('car','car.car_id = invoices.car_id','left');
		$this->db->join('customer','customer.customer_id = car.customer_id','left');
        $this->db->from($this->table);	
         $this->db->where('invoices.delete_flag',1);
		//$this->db->join('car','car.car_id=job.car_id','left');	
		 
		if(!empty($params))
        {
            if(!empty($params['customer_name']))
            {
                $this->db->where('invoices.customer_name',$params['customer_name']);
            }
            if(!empty($params['car_id']))
            {
                $this->db->where('invoices.car_id',$params['car_id']);
            }
            if(!empty($params['payment_mode']))
            {
                $this->db->where('invoices.payment_mode',$params['payment_mode']);
            }
            if($params['is_paid'] != '')
            {
                $this->db->where('invoices.is_paid',$params['is_paid']);
            }
            
            

        }
        $i = 0;
        foreach ($this->column_search as $item){ // loop column		
            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++;
        }
        // $this->db->order_by('FIELD(invoices.is_paid,"0","2","1") ASC',null,false);
        // $this->db->order_by('invoices.due_date','ASC');
        if($_POST['search']['value']){
            $this->db->OR_WHERE('(CASE WHEN invoices.is_paid = 0 THEN "Unpaid" WHEN invoices.is_paid = 1 THEN "Paid" ELSE "Partially Paid" END LIKE "%'.$_POST["search"]["value"].'%")', null,false);
        }
         
        if(isset($_POST['order'])){ // here order processing
            $this->db->order_by($this->column_order[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'], null,false);
        }else if(isset($this->order)){
            $order = $this->order;
            $this->db->order_by(key($order), $order[key($order)]);
        }
    }
	
	
	public function get_datatables_deleted_invoice($params=NULL){
		$this->_get_datatables_query_deleted_invoice($params);
        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_deleted_invoice($params=NULL){
		$this->_get_datatables_query_deleted_invoice($params);		
        $query = $this->db->get();
        return $query->num_rows();
    }
	

	public function count_all_deleted_invoice($params=NULL){
		// $this->db->select('job.job_id,car.car_no,job.service,job.job_status');
  //       $this->db->from($this->table);		
		// $this->db->join('car','car.car_id=job.car_id','left');	
        $this->db->select('invoices.*');	
        $this->db->from($this->table);
		if(!empty($params))
        {
            if(!empty($params['customer_name']))
            {
                $this->db->where('invoices.customer_name', $params['customer_name']);
            }
            if(!empty($params['car_id']))
            {
                $this->db->where('invoices.car_id',$params['car_id']);
            }

        }
        return $this->db->count_all_results();
    }
	public function get_details($id){
        $this->db->select('invoices.*,car.car_no,
        ifnull(CONCAT(customer.first_name," ",CASE WHEN customer.middle_name IS NULL THEN "" ELSE  CONCAT(customer.middle_name," ") END,ifnull(customer.last_name,"")),"") as name,
        ifnull(customer.email,"") as email,ifnull(customer.pin,"") as post_code,ifnull(customer.street_no,"") as street_no,ifnull(customer.suburb,"") as suburb,ifnull(customer.mobile,"") as contact_no,ifnull(customer.street_name,"") as street,admins.name as mechanic');
        $this->db->from($this->table);		
		$this->db->join('car','car.car_id=invoices.car_id','left');		
		$this->db->join('customer','customer.customer_id=car.customer_id','left');		
        $this->db->join('admins','admins.admin_id=invoices.mechanic_id','left');         	
        $this->db->where('invoices.id',$id);
        $query=$this->db->get();
        //echo $this->db->last_query(); die;
        return $query->row_array();
    }
	
	public function update($condition,$data){
		$this->db->where($condition);
		$this->db->update($this->table,$data);
		return 1;
	}
   
	public function add($data,$table=''){
        if(empty($table)){
            $this->db->insert($this->table,$data);
        }else{
             $this->db->insert($table,$data);
        }
        return $this->db->insert_id();
    }
	
	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 get_client_list(){
		$this->db->select('*');
		$this->db->from('client');
		$this->db->group_by('client_name'); 
		$query=$this->db->get();
        return $query->result_array();
	}
	
	 public function getRow($table,$condition){
        $this->db->where($condition);
        $query=$this->db->get($table);
        return $query->row_array();
    }
	
	
	public function getRows($table,$condition,$order_col=null,$order_type=null){
        $this->db->where($condition);
		
		if(isset($limit)){
          $this->db->limit($limit);      
        }
        if(!empty($order_col) && !empty($order_type)){
            $this->db->order_by($order_col,$order_type);
        }
        $query=$this->db->get($table);
        return $query->result_array();
    }
	
	
	 public function insert($table,$data){
        $this->db->insert($table,$data);
        return $this->db->insert_id();
    }
	
	public function batch_insert($table,$data){
        $this->db->insert_batch($table,$data);
        return 1;
    } 
	
	public function getServices($job_id){
		$this->db->from("job_services");		
		$this->db->join('service','service.service_id=job_services.service_id','left');	
		$this->db->where('job_id',$job_id);		
		$query=$this->db->get();
        return $query->result_array();
		
	}

    public function get_customer_list(){
        //$this->db->distinct();
        $this->db->select('DISTINCT(car.customer_id) as customer_id,invoices.customer_name');
        $this->db->from("invoices");    
        $this->db->join('car','car.car_id=invoices.car_id','inner'); 
        $this->db->where('invoices.customer_name !=',''); 
        $this->db->order_by('invoices.customer_name','ASC'); 
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_car_list(){
        $this->db->distinct();
        $this->db->select('car_id');
        $this->db->order_by('invoices.car_no','ASC');
       // $this->db->from("invoices");    
        //$this->db->join('car','car.car_id=invoices.car_id','left'); 
        $query_car = $this->db->get('invoices');
        $car=$query_car->result_array();
        for($i=0;$i<count($car);$i++)
        {
            $this->db->select('car_no');

            $this->db->where('car_id',$car[$i]['car_id']); 

             $query = $this->db->get('car'); 

             $car_data=$query->result_array();

             $car[$i]['car_no']=$car_data[0]['car_no'];
        }
        return $car;
    }

    public function get_car_list_by_customer($customer_id = null){
        $this->db->distinct();
        $this->db->select('invoices.car_id,invoices.car_no');
        $this->db->from("invoices");    
        $this->db->join('car','car.car_id=invoices.car_id','left'); 
        if($customer_id){
            $this->db->where('car.customer_id',$customer_id); 
        }
         $this->db->order_by('invoices.car_no','ASC'); 
        $query = $this->db->get();
        //echo $this->db->last_query();die;
        return $query->result_array();
    }

      public function get_datatables_excel($params){

$this->db->from($this->table);      
        //$this->db->join('car','car.car_id=job.car_id','left');    
         
        if(!empty($params))
        {
            if(!empty($params['customer_name']))
            {
                $this->db->where('invoices.customer_name',$params['customer_name']);
            }
            if(!empty($params['car_id']))
            {
                $this->db->where('invoices.car_id',$params['car_id']);
            }

        }

        $this->db->order_by('id','desc');

         $query = $this->db->get();

    

        return $query->result();

    }
    public function get_total_payment_by_invoice_no($invoice_no){
        $sql="select ifnull(sum(payment_amount),0) as sum_amount from invoice_payment_details where invoice_no=".$invoice_no;
        $query=$this->db->query($sql);
        return $query->row_array(); 
    }

    public function get_payment_list()
    {
        $this->db->select('invoice_payment_details.*,invoices.car_no,invoices.customer_name,invoices.date_of_creation,invoices. total_amount');
        $this->db->from('invoice_payment_details');      
        $this->db->join('invoices','invoice_payment_details.invoice_no=invoices.invoice_no','left');                
        $query=$this->db->get();
        //echo $this->db->last_query(); die;
        return $query->result_array();
    }

}