<?php
class Vehicle_running_model extends CI_Model{
	
	public function get_vehicle(){
		$this->db->from('vehicle');
		$this->db->order_by('vehicle_id', 'asc');
		return $this->db->get();
	}
	
	public function vehicle_report($filters){
		$start_date = $filters['start_date'];
		$end_date   = $filters['end_date']; 
		$start      = date('d-M-Y',strtotime($start_date));
		$end        = date('d-M-Y',strtotime($end_date));

		$qry = $this->db->query("SELECT count(*) as tot,vehicle_no,op_vehicle_id,veh_type,GROUP_CONCAT(DATE_FORMAT(sblt_operation.from_date, '%d-%m-%Y') SEPARATOR ', ') as from_date FROM sblt_operation_line inner join sblt_operation on  sblt_operation.operation_id = sblt_operation_line.operation_id and sblt_operation_line.status = '1' inner join sblt_vehicle on sblt_vehicle.vehicle_id = sblt_operation_line.op_vehicle_id inner join sblt_veh_type on sblt_veh_type.veh_type_id = sblt_vehicle.vehicle_type where sblt_operation_line.status = '1' and (DATE_FORMAT(sblt_operation.from_date, '%Y-%m-%d') BETWEEN '$start_date' and '$end_date' or DATE_FORMAT(sblt_operation.to_date, '%Y-%m-%d') BETWEEN '$start_date' and '$end_date') group by sblt_operation_line.op_vehicle_id");
		$row = $qry->result_array(); 
		return $row;
	}

	public function bar_report($start_date,$end_date){

		$qry = $this->db->query("SELECT count(*) as tot,vehicle_no FROM `sblt_operation_line` inner join sblt_operation on  sblt_operation.operation_id = sblt_operation_line.operation_id and sblt_operation_line.status = '1' inner join sblt_vehicle on sblt_vehicle.vehicle_id = sblt_operation_line.op_vehicle_id where sblt_operation_line.status = '1' and (DATE_FORMAT(sblt_operation.from_date, '%Y-%m-%d') BETWEEN '$start_date' and '$end_date' or DATE_FORMAT(sblt_operation.to_date, '%Y-%m-%d') BETWEEN '$start_date' and '$end_date') group by sblt_operation_line.op_vehicle_id");   
		$row = $qry->result_array();
	
		foreach ($row as $key => $value) {
			$vehicle_no         = $value['vehicle_no'];
			$value              = $value['tot'];
			$category['name']   = 'Vehicle No';
			$series1['name']    = 'Count';        
			$category['data'][] = $vehicle_no;
			$series1['data'][]  = $value;
		}
		$result = array();
		array_push($result,$category);
		array_push($result,$series1);
		return $result;
	}
	public function excel_report($filters){
		$start_date = $filters['start_date'];
		$end_date   = $filters['end_date'];
		
		$excel_qry = $this->db->query("SELECT sblt_operation.tour_no,customer_name,sblt_customers.phone_number,cust_sts,sblt_operation.trip_days,sblt_operation.bus_count,sblt_operation.report_address,sblt_operation.billing_name,first_name,sblt_operation.avg_km,booking_type_name,sblt_operation.from_date,sblt_operation.to_date,trip_type_name,veh_type,operation_sts,sblt_operation.trip_details,sblt_operation.booking_amount,advance_amount,discount_amount,balance_amount,extra_km,operation_type,sblt_operation.tp_info,hire_charges,vehicle_no,op_driver_name,op_driver_number,trip_start_km,trip_end_km,vendor_name,op_suspence_amt,f_city.city_name as source,dest.city_name as destination FROM `sblt_operation_line` INNER JOIN sblt_vehicle ON sblt_vehicle.vehicle_id = sblt_operation_line.op_vehicle_id INNER JOIN sblt_operation ON sblt_operation.operation_id = sblt_operation_line.operation_id INNER JOIN sblt_customers ON sblt_customers.cust_id = sblt_operation.cust_id left join sblt_employees on sblt_employees.id = sblt_operation.referral left join sblt_people on sblt_people.person_id = sblt_employees.person_id left join sblt_trip_type on sblt_trip_type.trip_type_id = sblt_operation.trip_type left join sblt_vendor on sblt_vendor.vendor_id = sblt_vehicle.owned_by left join sblt_veh_type on sblt_veh_type.veh_type_id = sblt_operation_line.op_vehicle_type left join sblt_booking_type on sblt_booking_type.booking_type_id = sblt_operation.cust_type left join sblt_city as f_city on f_city.city_id = sblt_operation.orgin left join sblt_city as dest on dest.city_id = sblt_operation.destination where sblt_operation_line.status = '1' and (DATE_FORMAT(sblt_operation.from_date, '%Y-%m-%d') BETWEEN '$start_date' and '$end_date' or DATE_FORMAT(sblt_operation.to_date, '%Y-%m-%d') BETWEEN '$start_date' and '$end_date')");   
		
		$excel_row = $excel_qry->result_array(); 
		return $excel_row;
	} 

}
?>