$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);