public function vehicles() { $cars_on = Events::select(["UnitId", DB::raw('count("Value")')])->where("Port", 'Ignition')->where("Value", 1)->groupBy("UnitId")->get()->toArray(); $cars_off = Events::select(["UnitId", DB::raw('count("Value")')])->where("Port", 'Ignition')->where("Value", 0)->groupBy("UnitId")->get()->toArray(); $cars_speed = Positions::select(["UnitId", DB::raw('count("Speed") as total'), DB::raw('sum("Speed") as sum')])->groupBy("UnitId")->get()->toArray(); for ($i = 0; $i < count($cars_on); $i++) { $cars_on[$i]['averageSpeed'] = "Not available"; $cars_on[$i]['count_two'] = 0; foreach ($cars_off as $car_off) { if ($cars_on[$i]['UnitId'] == $car_off['UnitId']) { $cars_on[$i]['count_two'] = $car_off['count']; break; } } } for ($i = 0; $i < count($cars_speed); $i++) { $cars_speed[$i]['averageSpeed'] = floor($cars_speed[$i]['sum'] / $cars_speed[$i]['total']); $cars_speed[$i]['count_two'] = 0; $cars_speed[$i]['count'] = 0; } foreach ($cars_speed as $car_speed) { $add_new = true; for ($i = 0; $i < count($cars_on); $i++) { if ($cars_on[$i]['UnitId'] == $car_speed['UnitId']) { $cars_on[$i]['averageSpeed'] = $car_speed['averageSpeed']; $add_new = false; break; } } if ($add_new) { array_push($cars_on, $car_speed); } } for ($i = 0; $i < count($cars_on); $i++) { $cars_on[$i]['failedStarts'] = $cars_on[$i]['count'] - $cars_on[$i]['count_two']; } return view('pages.vehicles')->with('cars', $cars_on); }