从约会表中获取可用时间段的查询

Question

我有一个包含以下细节的表: employees, working_hours, & appointments

Employees | working_hours                                                     | appointment
id, name  | id, day, start_time, end_time, employeeable_id, employeeable_type | id, employee_id, start_date_time, end_date_time

关系：

class Employee extends Model
{
   public function workingHours()
   {
      return $this->morphMany(WorkingHour::class, 'employeeable');
   }
}

class WorkingHour extends Model
{
   public function employeeable()
   {
      return $this->morphTo();
   }
}

class Appointment extends Model
{
   public function employee()
   {
      return $this->belongsTo(Employee::class);
   }
}

员工A 的工作时间如下：

[
   { day: 1, start_time: '08:00:00', end_time: '17:00:00' },
   ...
   { day: 5, start_time: '08:00:00', end_time: '17:00:00 }
]

员工 A 在 2022 年 5 月 23 日 09:00:00 到 09:30:00 有一个预约（每个预约持续时间为 30 分钟）。

问题：

如果管理员请求 2022 年 5 月 22 日 到 6 月 1 日 的 员工 A 可用时间槽，我期望得到以下响应：

[
   { '2022-05-22': ['08:00', '08:30', '09:00', ..., '17:00'] },
   { '2022-05-23': ['08:00', '08:30', '09:30'] } // 09:00 notice excluded.
   ...
   { '2022-06-01, [] }
]

如何定义上述查询？我所能想到的就是循环遍历员工A的每个工作小时时间，并检查该时间是否可用。

- uu franki

Answer 1

我建议您不要使用查询处理时间。 这是我的解决方案：

public function index()
{
    $appointment = [
        'id' => 1,
        'name' => 'Appointment 1',
        'start_date_time' => '2022-05-23 09:00:00',
        'end_date_time' => '2022-05-23 09:30:00'
    ];

    // Employee A working hours
    $workingHours = collect([
        ['day' => 1, 'start_time' => '08:00:00', 'end_time' => '17:00:00'],
        ['day' => 2, 'start_time' => '08:00:00', 'end_time' => '17:00:00'],
        ['day' => 3, 'start_time' => '08:00:00', 'end_time' => '17:00:00'],
        ['day' => 4, 'start_time' => '08:00:00', 'end_time' => '17:00:00'],
        ['day' => 5, 'start_time' => '08:00:00', 'end_time' => '17:00:00'],
        ['day' => 6, 'start_time' => '08:00:00', 'end_time' => '17:00:00'],
        ['day' => 0, 'start_time' => '08:00:00', 'end_time' => '17:00:00'], // carbon for sunday default is 0
    ]);

    $dateArray = [];
    $startDate = Carbon::parse('2022-05-22');
    $endDate = Carbon::parse('2022-06-01');

    while ($startDate->lte($endDate)) {
        // seach for working hours that match the day of the week
        $workingHour = (array) $workingHours->firstWhere('day', $startDate->dayOfWeek);

        // generate time for each day
        $times = $this->generateTimes($workingHour);

        // extract date from appoint start date time
        $appointmentDate = Carbon::parse($appointment['start_date_time'])->format('Y-m-d');

        if ($appointmentDate === $startDate->format('Y-m-d')) {
            // remove time according to appointment time
            $times = $this->removeTime($times, $appointment);
        }

        // add time to date array
        $dateArray[$startDate->format('Y-m-d')] = $times;

        // increment date
        $startDate->addDay();
    }

    dd($dateArray);
}

private function generateTimes(array $workingHour)
{
    // the working time of the workers must be reduced by at least 1 hour.
    // because there is no way for you to have an appointment on your end working time.
    $startTime = Carbon::parse($workingHour['start_time']);
    $endTime = Carbon::parse($workingHour['end_time'])->subHour();

    $times = [];
    while ($startTime->lte($endTime)) {
        $times[] = $startTime->format('H:i');
        $startTime->addMinutes(30);
    }

    return $times;
}

private function removeTime($times, $appointment)
{
    $startTime = Carbon::parse($appointment['start_date_time']);
    $endTime = Carbon::parse($appointment['end_date_time']);

    $startTime = $startTime->format('H:i');
    $endTime = $endTime->format('H:i');

    $times = array_diff($times, [$startTime, $endTime]);

    return $times;
}

- Aslam H