Untitled

$query = Shift::userNotificationJoin($user)
            ->withStatusLogs()
            ->join('location as l', 'shifts.location_id', '=', 'l.id')
            ->leftJoin('Shifts_MinStaffRequired as msr', function ($join) {
                $join->on('shifts.location_id', '=', 'msr.location_id');
                $join->on('shifts.shift_categories', '=', 'msr.shift_categories');
                $join->on('shifts.start_date', '>=', 'msr.start_date');
                $join->on('shifts.end_date', '<=', 'msr.end_date');
                $join->on('shifts.min_staff_needed', '=', 'msr.MinimumStaff');
            })
            ->addSelect(['shifts.id', 'shifts.location_id'])
            ->addSelect(['notifications.id as notification_id', 'notifications.read_at'])
            ->selectRaw('shifts.id as shift_id')
            ->selectRaw('date_format(shifts.start_date,"%W") as shift_day')
            ->selectRaw('shifts.permanent_key as permanent_key')
            ->selectRaw('date_format(shifts.start_date,"%D %b %Y") as shift_date')
            ->selectRaw('date_format(shifts.start_date,"%W ,  %b %D , %Y") as outer_shift_date')
            ->selectRaw('date_format(shifts.cancelled_date_time,"%W,%D %b %Y %h:%i %p") as cancelled_date_time')
            ->selectRaw('date_format(shifts.start_date,"%b %D,%Y") as startDate')
            ->addSelect(['shifts.start_time', 'shifts.end_time', 'shifts.shift_status'])
            ->selectRaw('shifts.location,concat(shifts.start_time," - ",shifts.end_time) as shift_time')
            ->selectRaw('msr.MinimumStaff as remainingMinimumStaff')
            ->selectRaw("case 
                        when (shifts.start_date<='$urgent_start_time' AND shifts.start_date>='$curent_date_string') 
                            then 1
                        when (shifts.urgent_shift='1')
                            then 1
                        when (shifts.start_date>'$urgent_start_time')
                            then 0
                        else 0
                        end as shift_urgent")
            ->selectRaw('l.location_supervisor as supervisor_id')
            ->selectRaw('shifts.supervisor_name,shifts.contact_details,shifts.aggression,shifts.check_in_time,l.location,shifts.asl,shifts.gender,shifts.notes,shifts.internal_notes,shifts.same_time_settings')
            ->selectRaw('shifts.shift_time as time_pref,shifts.personal_care')
            ->selectRaw('shifts.start_date,shifts.end_date,shifts.working_alone,shifts.shift_categories,shifts.trainee_id AS trainee_id')
            ->selectRaw($this->SHIFTS . '.' . $this->SHIFT_STATUS . ' as shiftStatus')
            ->selectRaw('case when(shifts.working_alone="Yes") then "No" else  "Yes" end as working_with_staff')
            ->selectRaw('case
                                when (shifts.shift_categories="1")
                                    then "Extra Shift"
                                when (shifts.shift_categories="0")
                                    then "Necessary Shift"
                            end as shift_type')
            ->leftJoin('dsw_user as dsw', 'shifts.trainee_id', '=', 'dsw.id')
            ->selectRaw("CONCAT(dsw.first_name,' ',dsw.last_name) AS trainee_name")
            ->whereNull('shifts.deleted_at')
            ->where('shifts.shift_status', 'p')
            ->where('shifts.shift_categories', '=', 0)
            ->where('shifts.end_date', '>=', $curent_date_string);

        if ($request->filled($this->LOCATION)) {
            $query->belongsToLocation($request->input($this->LOCATION));
        }

        if ($request->vac_startDate) {
            $startDate = date_format(date_create($request->vac_startDate), 'Y-m-d');
            $query->whereDate('shifts.start_date', '>=', $startDate);
        }
        if ($request->vac_endDate) {
            $endDate = date_format(date_create($request->vac_endDate), 'Y-m-d');
            $query->whereDate('shifts.end_date', '<=', $endDate);
        }

        if ($request->vc_startDate) {
            $startDate = date_format(date_create($request->vc_startDate), 'Y-m-d');
            $query->whereDate($this->SHIFTS . '.' . $this->START_DATE, '>=', $startDate);
        }
        if ($request->vc_endDate) {
            $endDate = date_format(date_create($request->vc_endDate), 'Y-m-d');
            $query->whereDate($this->SHIFTS . '.' . $this->END_DATE, '<=', $endDate);
        }
        if ($request->vc_extra && $request->vc_necessary) {
            $query->where(function ($query) {
                $query->where($this->SHIFTS . '.' . 'shift_categories', '=', '1')->orWhere($this->SHIFTS . '.' . 'shift_categories', '=', '0');
            });
        } elseif ($request->vc_extra) {
            $query->where($this->SHIFTS . '.' . 'shift_categories', '=', '1');
        } elseif ($request->vc_necessary) {
            $query->where($this->SHIFTS . '.' . 'shift_categories', '=', '0');
        }

        if ($request->vc_minimum_staff) {
            $query->where($this->SHIFTS . '.' . 'min_staff_needed', '=', $request->vc_minimum_staff);
        }

        $vacantShifts = $query->orderBy('shifts.shift_categories', 'desc')->orderBy($this->SHIFTS . '.' . $this->START_DATE, 'asc')->get();

        $orderedVacantShifts = $this->orderShifts($vacantShifts);

        return $this->paginate($orderedVacantShifts);