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

class Minvoicepayment extends CI_Model {
	var $table = 'invoice_payment_details';	
    var $column_order = array('invoice_payment_details.invoice_payment_details_id','invoices.invoice_no','invoices.date_of_creation','invoices.car_no','invoices.total_amount','invoice_payment_details.payment_amount','invoice_payment_details.payment_date','invoice_payment_details.payment_mode','invoices.customer_name'); //set column field database for datatable orderable
    var $column_search = array('invoices.invoice_no','invoice_payment_details.payment_amount','invoice_payment_details.payment_date','invoice_payment_details.payment_mode','invoices.customer_name',"invoices.car_no",'invoices.total_amount','invoice_payment_details.payment_amount'); //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();
        $this->customer=$this->session->userdata('customer_data');	
    }
	private function _get_datatables_query($params=NULL){
		$this->db->select('invoice_payment_details.*,invoices.car_no,invoices.customer_name,invoices.date_of_creation,invoices. total_amount,invoices.is_paid,invoices.due_date,CASE WHEN invoice_payment_details.invoice_payment_details_id > 0 THEN get_paid_amount(invoice_payment_details.invoice_payment_details_id,invoice_payment_details.invoice_no) ELSE 0 END as total_paid_amount,invoices.invoice_no');
        $this->db->from('invoices');      
        $this->db->join('invoice_payment_details','invoice_payment_details.invoice_no=invoices.invoice_no','left');
        $this->db->join('car','car.car_id=invoices.car_id','inner');	
	    $this->db->join('customer','customer.customer_id=car.customer_id','inner');	
        $this->db->where('customer.customer_id',$this->customer['customer_id']);
        $this->db->order_by('invoice_payment_details.payment_date','DESC');
		if(!empty($params)){
            
            if(!empty($params['from_date'])){
                $this->db->where('STR_TO_DATE(date_format(invoices.date_of_creation,"%Y-%m-%d"),"%Y-%m-%d") >= STR_TO_DATE("'.$params["from_date"].'","%d/%m/%Y")',null);
            }
            if(!empty($params['to_date'])){
                $this->db->where('STR_TO_DATE(date_format(invoices.date_of_creation,"%Y-%m-%d"),"%Y-%m-%d") <= STR_TO_DATE("'.$params["to_date"].'","%d/%m/%Y")',null);
            }
        }
        $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++;
        }
         
        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($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('invoice_payment_details.*,invoices.car_no,invoices.customer_name,invoices.date_of_creation,invoices. total_amount,invoices.invoice_no');
        $this->db->from('invoices');      
        $this->db->join('invoice_payment_details','invoice_payment_details.invoice_no=invoices.invoice_no','left');   
        $this->db->join('car','car.car_id=invoices.car_id','inner');	
	    $this->db->join('customer','customer.customer_id=car.customer_id','inner');	
        $this->db->where('customer.customer_id',$this->customer['customer_id']);
		if(!empty($params)){
            
            if(!empty($params['from_date'])){
                $this->db->where('STR_TO_DATE(date_format(invoices.date_of_creation,"%Y-%m-%d"),"%Y-%m-%d") >= STR_TO_DATE("'.$params["from_date"].'","%d/%m/%Y")',null);
            }
            if(!empty($params['to_date'])){
                $this->db->where('STR_TO_DATE(date_format(invoices.date_of_creation,"%Y-%m-%d"),"%Y-%m-%d") <= STR_TO_DATE("'.$params["to_date"].'","%d/%m/%Y")',null);
            }
        }
        return $this->db->count_all_results();
    }

    public function calculate_total_paid_amt($params=NULL){
		$this->db->select('SUM(invoice_payment_details.payment_amount) as total_paid_amount');      
        $this->db->from($this->table);
        $this->db->join('invoices','invoice_payment_details.invoice_no=invoices.invoice_no','left');   
        $this->db->join('car','car.car_id=invoices.car_id','inner');	
	    $this->db->join('customer','customer.customer_id=car.customer_id','inner');	
        $this->db->where('customer.customer_id',$this->customer['customer_id']);
		if(!empty($params['from_date'])){
            $this->db->where('STR_TO_DATE(date_format(invoices.date_of_creation,"%Y-%m-%d"),"%Y-%m-%d") >= STR_TO_DATE("'.$params["from_date"].'","%d/%m/%Y")',null);
        }
        if(!empty($params['to_date'])){
            $this->db->where('STR_TO_DATE(date_format(invoices.date_of_creation,"%Y-%m-%d"),"%Y-%m-%d") <= STR_TO_DATE("'.$params["to_date"].'","%d/%m/%Y")',null);
        }
        $query=$this->db->get();
        //echo $this->db->last_query(); die;
        return $query->row_array();
    }

    public function calculate_total_invoice_amt($params=NULL){
		$this->db->select('SUM(invoices.total_amount) as total_invoice_amount');      
        $this->db->from('invoices');
        $this->db->join('car','car.car_id=invoices.car_id','inner');	
	    $this->db->join('customer','customer.customer_id=car.customer_id','inner');	
        $this->db->where('customer.customer_id',$this->customer['customer_id']);
		if(!empty($params['from_date'])){
            $this->db->where('STR_TO_DATE(date_format(invoices.date_of_creation,"%Y-%m-%d"),"%Y-%m-%d") >= STR_TO_DATE("'.$params["from_date"].'","%d/%m/%Y")',null);
        }
        if(!empty($params['to_date'])){
            $this->db->where('STR_TO_DATE(date_format(invoices.date_of_creation,"%Y-%m-%d"),"%Y-%m-%d") <= STR_TO_DATE("'.$params["to_date"].'","%d/%m/%Y")',null);
        }
        $query=$this->db->get();
        //echo $this->db->last_query(); die;
        return $query->row_array();
    }
    
	
	public function get_details($id){
		$this->db->select('invoices.*,car.car_no,car.name,car.email,car.post_code,car.street_no,car.suburb,car.   contact_no,car.street,admins.name as mechanic');
        $this->db->from($this->table);		
		$this->db->join('car','car.car_id=invoices.car_id','left');		
        $this->db->join('admins','admins.admin_id=invoices.mechanic_id','left');         	
        $this->db->where('invoices.id',$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 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('customer_name');

       $this->db->where('customer_name!=',NULL); 

        $query = $this->db->get('invoices');
        return $query->result_array();
    }

    public function get_car_list(){
        $this->db->distinct();
        $this->db->select('car_id');
       // $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_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();
    }

}