<?php
class Suspense_report_model extends CI_Model{
	public function search($search,$filters,$rows = 0,$limit_from= 0,$sort='',$order='asc'){
		$start_date = $filters['start_date'];
		$end_date   = $filters['end_date'];
		if(!$sort){
			$sort = "from_date"; 
		}	
		$this->db->select('tour_no,customer_name,from_date,to_date,vehicle_no,op_driver_id,op_driver_name,to.city_name as to_city_name,first_name,driver_suspence_date,trip_days,(IFNULL(sum(op_suspence_amt), 0) + IFNULL(sum(driver_suspence_amount), 0)) as tot_amt,(IFNULL(sum(suspence_amt), 0) + IFNULL(sum(diesel_amt), 0)) as expense_amt,IFNULL(sum(op_suspence_amt), 0) as op_suspence_amt,IFNULL(sum(driver_suspence_amount), 0) as driver_suspence_amount,IFNULL(sum(suspence_amt), 0) as suspence_amt,IFNULL(sum(diesel_amt), 0) as diesel_amt');
		$this->db->from('operation_line');
		$this->db->join('operation', 'operation.operation_id = operation_line.operation_id ','left');
		$this->db->join('driver_suspence', "driver_suspence.driver_suspence_op_id = operation_line.operation_id and driver_suspence.driver_suspence_vch_id = operation_line.op_vehicle_id",'left');
		$this->db->join('suspence', "suspence.suspence_op_id = operation_line.operation_id and suspence.suspence_vch_id = operation_line.op_vehicle_id",'left');
		$this->db->join('diesel', "diesel.diesel_op_id = operation_line.operation_id and diesel.diesel_vch_id = operation_line.op_vehicle_id",'left');
		$this->db->join('people', 'people.person_id = operation_line.op_driver_id ','left');
		$this->db->join('customers', 'customers.cust_id = operation.cust_id','left');
		$this->db->join('vehicle', 'vehicle.vehicle_id = operation_line.op_vehicle_id','left');
		$this->db->join('city to', 'to.city_id = operation.destination','left');
		
		$this->db->group_start();
			$this->db->where('DATE_FORMAT(from_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date));
		$this->db->group_end();
	if($search){
      $this->db->group_start();
        $this->db->like('customer_name',$search);
        $this->db->or_like('vehicle_no',$search);
        $this->db->or_like('op_driver_name',$search);
        $this->db->or_like('first_name',$search);
        $this->db->or_like('to.city_name',$search);
        if(strpos($search, '-') !== false){
          $this->db->or_like('from_date', date('Y-m-d',strtotime($search))); 
          $this->db->or_like('to_date', date('Y-m-d',strtotime($search)));
        }
      $this->db->group_end(); 
    }    
		$this->db->where('operation_line.status',1);
		//$this->db->group_by('operation_line.op_line_id');
		$this->db->group_by('operation_line.operation_id');
		if($rows>0){
			$this->db->limit($rows, $limit_from);
		}
		return $this->db->get();
	}
	public function get_found_rows($search,$filters){
		return $this->search($search, $filters)->num_rows();
	}
	public function get_excel_data($start_date,$end_date){
		
		$this->db->select('operation_line.operation_id,tour_no,customer_name,from_date,to_date,vehicle_no,op_driver_id,op_driver_name,to.city_name as to_city_name,first_name,driver_suspence_date,trip_days,(IFNULL(sum(op_suspence_amt), 0) + IFNULL(sum(driver_suspence_amount), 0)) as tot_amt,(IFNULL(sum(suspence_amt), 0) + IFNULL(sum(diesel_amt), 0)) as expense_amt,IFNULL(sum(op_suspence_amt), 0) as op_suspence_amt,IFNULL(sum(driver_suspence_amount), 0) as driver_suspence_amount,IFNULL(sum(suspence_amt), 0) as suspence_amt,IFNULL(sum(diesel_amt), 0) as diesel_amt');
		$this->db->from('operation_line');
		$this->db->join('operation', 'operation.operation_id = operation_line.operation_id ','left');
		$this->db->join('driver_suspence', "driver_suspence.driver_suspence_op_id = operation_line.operation_id and driver_suspence.driver_suspence_vch_id = operation_line.op_vehicle_id",'left');
		$this->db->join('suspence', "suspence.suspence_op_id = operation_line.operation_id and suspence.suspence_vch_id = operation_line.op_vehicle_id",'left');
		$this->db->join('diesel', "diesel.diesel_op_id = operation_line.operation_id and diesel.diesel_vch_id = operation_line.op_vehicle_id",'left');
		$this->db->join('people', 'people.person_id = operation_line.op_driver_id ','left');
		$this->db->join('customers', 'customers.cust_id = operation.cust_id','left');
		$this->db->join('vehicle', 'vehicle.vehicle_id = operation_line.op_vehicle_id','left');
		$this->db->join('city to', 'to.city_id = operation.destination','left');
		$this->db->group_start();
			$this->db->where('DATE_FORMAT(from_date, "%Y-%m-%d") BETWEEN '. $this->db->escape($start_date).' AND '.$this->db->escape($end_date));
		$this->db->group_end();
		$this->db->group_by('operation_line.operation_id');
		$suspense = $this->db->get();
		
	return $suspense->result_array();
	}
	/*
	public function get_bal_log_amount($booking_id) {
		$this->db->select('IFNULL(sum(amount), 0) as bal_log_amt');
		$this->db->from('balance_log');
		$this->db->where('booking_id', $booking_id);
		return $this->db->get()->row();
	}*/
	
	public function get_received_amt($operation_id){
		$this->db->select('IFNULL(sum(suspence_amt), 0) as suspence_amt');
		$this->db->from('suspence');
		$this->db->where('suspence_op_id', $operation_id);
		return $this->db->get()->row();
	}
	
}
?>