Untitled
unknown
plain_text
a year ago
5.3 kB
4
Indexable
Never
$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);