Untitled

<?php

namespace App\Http\Controllers;

use App\Models\Order;
use App\Models\Setting;
use App\Models\User;
use App\Models\Passanger;
use App\Models\Route;
use App\Models\Pool;
use App\Models\Car;
use App\Models\Driver;
use App\Models\CarsType;
use DateTime;
use App\Models\Schedules;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;

class DashboardController extends Controller
{
    public function index(Request $request)
    {
        $totalUser = User::count();
        $totalOrder = Order::count();
        $totalPool = Pool::count();
        $totalRoute = Route::count();
        $totalCars = Car::count();
        $totalDriver = Driver::count();
        $totalCarsType = CarsType::count();
        $totalSchedules = Schedules::count();
        $view = User::all();
        $tittle = Setting::findOrFail(1);

        $topCustomers = User::withCount('order')
            ->when($request->input('start') && $request->input('end'), function ($query) use ($request) {
                $query->whereHas('order', function($subQuery) use ($request) {
                    $subQuery->whereDate('order_date', '>=', $request->input('start'))
                        ->whereDate('order_date', '<=', $request->input('end'));
                });
            })
            ->where('role', 'customer')
            ->limit(5)
            ->get();
        $topPassangers = Passanger::select('name')
            ->when($request->input('start') && $request->input('end'), function ($query) use ($request) {
                $query->whereHas('orderDetails', function($subQuery) use ($request) {
                    $subQuery->whereHas('order', function($subQuery) use ($request) {
                        $subQuery->whereDate('order_date', '>=', $request->input('start'))
                            ->whereDate('order_date', '<=', $request->input('end'));
                    });
                });
            })
            ->withCount('orderDetails as order_count')
            ->orderByDesc('order_count')
            ->limit(5)
            ->get();

        $topRoutes = Schedules::query()
            ->withCount('order as order_count')
            ->when($request->input('start') && $request->input('end'), function ($query) use ($request) {
                $query->whereHas('order', function($subQuery) use ($request) {
                    $subQuery->whereDate('order_date', '>=', $request->input('start'))
                        ->whereDate('order_date', '<=', $request->input('end'));
                });
            })
            ->orderByDesc('order_count')
            ->limit(5)
            ->get();


        $topSchedules = Schedules::query()
            ->withCount('order as order_count')
            ->when($request->input('start') && $request->input('end'), function ($query) use ($request) {
                $query->whereHas('order', function($subQuery) use ($request) {
                    $subQuery->whereDate('order_date', '>=', $request->input('start'))
                        ->whereDate('order_date', '<=', $request->input('end'));
                });
            })
            ->orderByDesc('order_count')
            ->limit(5)
            ->get();
        $topDates = Order::query()
            ->selectRaw('DATE(order_date) as order_date')
            ->selectRaw('COUNT(id) as order_count')
            ->when($request->input('start') && $request->input('end'), function ($query) use ($request) {
                $query->whereDate('order_date', '>=', $request->input('start'))
                        ->whereDate('order_date', '<=', $request->input('end'));
            })
            ->groupBy('order_date')
            ->orderByDesc('order_count')
            ->limit(5)
            ->get();

        $genderCounts = User::selectRaw('CASE
            WHEN jenis_kelamin IS NULL THEN "Belum Dipilih"
            ELSE jenis_kelamin
            END AS jenis_kelamin')
            ->selectRaw('count(*) as total')
            ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
            ->when($request->input('start') && $request->input('end'), function ($query) use ($request) {
                $query->where('orders.order_date', '>=', $request->input('start'))
                    ->where('orders.order_date', '<=', $request->input('end'));
            })
            ->where('role', 'customer')
            ->groupBy('jenis_kelamin')
            ->get();
        $usersWithAge = User::selectRaw('CASE
            WHEN tanggal_lahir IS NULL THEN "Belum Dipilih"
            ELSE tanggal_lahir
            END AS tanggal_lahir')
            ->selectRaw('count(*) as total')
            ->selectRaw('YEAR(CURRENT_DATE()) - YEAR(tanggal_lahir) - (DATE_FORMAT(CURRENT_DATE(), "%m%d") < DATE_FORMAT(tanggal_lahir, "%m%d")) as umur')
            ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
            ->when($request->input('start') && $request->input('end'), function ($query) use ($request) {
                $query->where('orders.order_date', '>=', $request->input('start'))
                    ->where('orders.order_date', '<=', $request->input('end'));
            })
            ->where('role', 'customer')
            ->groupBy('tanggal_lahir')
            ->get();

        $topStartPools = Schedules::query()
            ->with(['route.startPool'])
            ->withCount('order as order_count')
            ->when($request->input('start') && $request->input('end'), function ($query) use ($request) {
                $query->whereHas('order', function($subQuery) use ($request) {
                    $subQuery->whereDate('order_date', '>=', $request->input('start'))
                        ->whereDate('order_date', '<=', $request->input('end'));
                });
            })
            ->orderByDesc('order_count')
            ->limit(5)
            ->get()
            ->groupBy('route.startPool.id')
            ->map(function ($group) {
                return [
                    'name' => $group->first()->route->startPool->name,
                    'order_count' => $group->sum('order_count'),
                ];
            })
            ->sortByDesc('order_count')
            ->take(5);

        $orderQuery = Order::query()
            ->when($request->input('start') && $request->input('end'), function ($query) use ($request) {
                $query->whereDate('order_date', '>=', $request->input('start'))
                    ->whereDate('order_date', '<=', $request->input('end'));
            });

        $order = $orderQuery->get();



        return view('dashboard', compact('totalUser', 'usersWithAge', 'topStartPools', 'genderCounts', 'totalSchedules', 'totalOrder', 'totalDriver', 'totalCars', 'totalCarsType', 'totalPool', 'totalRoute',  'topRoutes', 'topSchedules', 'topCustomers', 'topDates', 'topPassangers', 'view', 'tittle'));
    }
}