<?php
class Vehicle_margin_model extends CI_Model{
	
	
	public function get_vehicle_type() {
	   $this->db->select('veh_type_id,veh_type');
	   $this->db->from('veh_type');
	   $this->db->where('veh_type.status', 1);
	   $this->db->order_by('veh_type', 'asc');
	   return $this->db->get()->result();
	   
	   
	}
	public function get_vehicle_no($vehicle_type) {
		$this->db->select("vehicle_id,vehicle_no");
		$this->db->from('vehicle');
		$this->db->where_in('vehicle.vehicle_type', $vehicle_type);
	    $this->db->order_by('vehicle_id', 'asc');
	   return $this->db->get()->result();
	}
	
	public function search($start_date,$end_date,$vehicle_type,$vehicle_no){
		$type_qry = "";
		if($vehicle_type){
			$vehicle_type = implode(",",$vehicle_type);
			$type_qry = "and sblt_operation.vehicle_type in ($vehicle_type)";
		}	
		$vch_qry = "";
		if($vehicle_no){
			$vehicle_no = implode(",",$vehicle_no);
			$vch_qry = "and sblt_operation_line.op_vehicle_id in ($vehicle_no)";
		}
		$query = "SELECT cust_type,trip_type,op_vehicle_id,vehicle_no,sblt_veh_type.veh_type,count(op_line_id) as trip_count,sum(trip_days) as running_days,IFNULL(sum(booking_amount),0) as tot_sale,IFNULL(sum(trip_end_km),0) as endkm, IFNULL(sum(trip_start_km),0) as startkm, (IFNULL(sum(trip_end_km), 0) -IFNULL(sum(trip_start_km), 0)) as tot_km FROM `sblt_operation_line`INNER JOIN sblt_operation on sblt_operation.operation_id =sblt_operation_line.operation_id 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 DATE_FORMAT(sblt_operation.from_date, '%Y-%m-%d') BETWEEN '$start_date' AND '$end_date' and operation_sts in ('4','5') $type_qry $vch_qry GROUP by op_vehicle_id,trip_type";

		$query_info = $this->db->query($query);
		return $query_info->result();
	}
	public function diesel_amount($start_date,$end_date,$vehicle_id,$mode){		
		if($mode === "2"){
			$mode_info = "and trip_type = '6'";
		}else{
			$mode_info = "and trip_type != '6'";
		}		
		$query = "SELECT IFNULL(SUM(diesel_amt),0) AS diesel_amount FROM `sblt_operation_line` INNER JOIN sblt_operation ON sblt_operation.operation_id =sblt_operation_line.operation_id LEFT JOIN sblt_diesel ON (diesel_vch_id = op_vehicle_id  AND diesel_op_id = sblt_operation.operation_id) WHERE DATE_FORMAT(sblt_operation.from_date, '%Y-%m-%d') BETWEEN '$start_date' AND '$end_date' AND operation_sts IN ('4','5') AND op_vehicle_id = '$vehicle_id'  $mode_info";
		
		$query_info = $this->db->query($query);
		return $query_info->result();
	}
	public function toll_amount($start_date,$end_date,$vehicle_id,$mode){		
		if($mode === "2"){
			$mode_info = "and trip_type = '6'";
		}else{
			$mode_info = "and trip_type != '6'";
		}		
		$query = "SELECT IFNULL(SUM(suspence_amt),0) AS toll_amount FROM `sblt_operation_line` INNER JOIN sblt_operation ON sblt_operation.operation_id =sblt_operation_line.operation_id LEFT JOIN sblt_suspence ON (suspence_vch_id = op_vehicle_id  AND suspence_op_id = sblt_operation.operation_id and suspence_info ='Toll') WHERE DATE_FORMAT(sblt_operation.from_date, '%Y-%m-%d') BETWEEN '$start_date' AND '$end_date' AND operation_sts IN ('4','5') AND op_vehicle_id = '$vehicle_id'  $mode_info";
		$query_info = $this->db->query($query);
		return $query_info->result();
	}
	public function other_amount($start_date,$end_date,$vehicle_id,$mode){		
		if($mode === "2"){
			$mode_info = "and trip_type = '6'";
		}else{
			$mode_info = "and trip_type != '6'";
		}		
		$query = "SELECT IFNULL(SUM(suspence_amt),0) AS other_amount FROM `sblt_operation_line` INNER JOIN sblt_operation ON sblt_operation.operation_id =sblt_operation_line.operation_id LEFT JOIN sblt_suspence ON (suspence_vch_id = op_vehicle_id  AND suspence_op_id = sblt_operation.operation_id and suspence_info != 'Toll' and  suspence_info != 'Balance_Received') WHERE DATE_FORMAT(sblt_operation.from_date, '%Y-%m-%d') BETWEEN '$start_date' AND '$end_date' AND operation_sts IN ('4','5') AND op_vehicle_id = '$vehicle_id'  $mode_info";
		
		$query_info = $this->db->query($query);
		return $query_info->result();
	}
}
?>