Creating Appt Calendar for Multiple Providers - how to best do this?

I have an appointment calendar, and previously I had a record for every timeslot available. It worked well for just me, but I added someone else to the office and needed to re-think it. I'm having some trouble figuring out how to best do this. Can someone please guide me along? it's been a rough couple of weeks, so please be kind.

Here is what I have that matters:

officehour

| weekday | open | close | doc_id | |:------- |:-----:| -----:| ------:| | Monday | 16:00 | 19:00 | 1 | | Tuesday | 08:00 | 11:00 | 1 | | Tuesday | 15:00 | 19:00 | 2 | | Wednesday | 08:00 | 11:00 | 2 | | Wednesday | 15:00 | 18:00 | 1 | | Wednesday | 15:00 | 18:00 | 2 | | Thursday | 08:00 | 11:00 | 2 | | Thursday | 15:00 | 19:00 | 1 | | Thursday| 16:00 | 19:00 | 2 | | Friday | 08:00 | 10:00 | 1 | | Friday | 15:00 | 19:00 | 2 | | Saturday| 08:00 | 11:00 | 2 |

closed

| closed_date | open | close | closed | doc_id | reason | | ----------- | ----- | ----- | ------ |------ | ------ | | 2022-08-23 | 16:00 | 17:00 | 0 | 2 | Interview | | 2022-08-30 | null | null | 1 | 1 | Vacation | | 2022-08-30 | 16:00 | 17:00 | 0 | 2 | Sleep In. |

Appointment

| appt_date | time | client | doc_id | appt_type_id | appt_status_id | appt_note | | --------- | -----| ----- | ------ | ------------ | -------------- | --------- | | 2022-08-30 | 16:00 | 1 | 1 | 1 | 2 | Mary Jones | | 2022-08-30 | 16:00 | 435 | 2 | 1 | 2 | hurt myself gardening | | 2022-08-31 | 16:00 | 435 | 2 | 2 | 2 | hurt myself gardening |

I created a CarbonPeriod to cycle through in 10 minute increments (although I'd later like to cycle through by length of the appointment if it's booked. For now, 10 minutes is fine.

I grabbed the "masterData", which is all the db queries I'd need to use for the days shown. $data->appts = DB::table('appt')-> whereBetween('appt_date', [$data->startdate->copy()->startOfDay(), $data->enddate->copy()->endofDay()])->get() ->where('appt_status_id',$this->SCHED)->get()->toArray(); $data->officehours = Officehour::all()->get(); $data->closed = Closed::whereBetween('closed_date',[$data->startdate->copy()->startOfDay(), $data->enddate->copy()->endofDay()])->get();

I have a public calendar that gets 2 weeks of data. The admin calendar shows 6 weeks and includes basic info on the booked appointments with a link to edit it instead of a link to book open slots.

I have components for the day, headings, and appointment booking buttons so they can be re-used, and data pushed into them.

My logic:

• Each day may have multiple sessions (Day displayed will find office hours by Carbon($day)->dayOfWeek = officehour->weekday
• If there are no office hours for that day, skip to the next. Also skip Sundays
• If there are office hours, check for closed records for that date and use that data instead.
• cycle through the session times in 10 minute increments.
  • if booked, move to the next time slot (I'd love to add the length of the booked appt instead of 10 minutes in a perfect world)
  • if open, add it to the list of timeslots.
  • for Admin, add the booked appointments for that session to the list
  • set the last appointment in the session as a new patient appointment, which is 60 minutes.

I am sharing my somewhat broken code, since I"ve been struggling with this for weeks and can't get it right. I'd either run out of resources or it wouldn't display properly. PLEASE tell me there is a better way to do this. My brain defaults to complicated, and it's so hard to break it free. I've tried several ways, and you'll see that in my code comments

public function index()
  {
      $doc = Auth::user()->id;
      $settings = Setting::where('type','admin')->first();
      $numdays = ($settings->weeks_visible * 7);
      $days = $this->getDates($numdays);
      $masterData = $this->getMasterData($days);
//        dd($masterData);
//       the below line runs what I was trying to do previously
//        $display = $this->getApptCalendarData($days, $masterData);
      $display = $this->getCalendarDataBySession($days, $masterData);
      $patients = Patient::whereDoctorId($doc)->orderBy('nickname')->pluck('nickname', 'id');
      $families = Patient::orderBy('nickname')->where(['parent_id'=>null, 'active'=>1,'doctor_id'=>$doc])->pluck('nickname', 'id');
//        dd($display);

      return view('calendar.newindex', ['display'=>$display, 'patients'=>$patients, 'famliies'=>$families]);
  }
// returns array of dates for display
  public function getDates($numdays) {
      // GET START AND END DATES TO SHOW
      $now = Carbon::now();
      // is it now after 11AM on Saturday?
      $isThisWeekClosed = $now->isWeekend() &&
          $now->gte($now->copy()->weekday(6)->hour(11)->minute(1));
      // IF week has closed start with Monday
      // ELSE start with past Monday
      $start = $isThisWeekClosed ? $now->copy()->nextWeekday() :
          $now->copy()->startOfWeek();
      // GET LAST DISPLAY DATE
      $end = $start->copy()->addDays($numdays)->subDays($numdays/7);
      // GET RANGE OF DATES FOR LOOPING
      $displayDates = CarbonPeriod::create($start,$end);
//        dd($displayDates);


      return $displayDates;
  }
  // gets data filtered by start and end dates
  // USES ARRAYS, not collections
  public function getMasterData($days) {
      $data = new \stdClass();
      $data->now = Carbon::now();
      // NEW DATA QUERIES USING ELOQUENT
      $data->startdate = $days->first()->copy()->startOfDay();
      $data->enddate = $days->last()->copy()->endOfDay();
      $data->appts = DB::table('appt')->
          whereBetween('appt_date', [$data->startdate->copy()->startOfDay(), $data->enddate->copy()->endofDay()])
          ->where('appt_status_id',$this->SCHED)->get()->toArray();
      $data->officehours = Officehour::all()->toArray();
      $data->closed = Closed::whereBetween('closed_date',[$data->startdate->copy()->startOfDay(), $data->enddate->copy()->endofDay()])->get()->toArray();
      return $data;
  }

  // loop through days to get calendar display
  // RETURNS AN OBJECT
  public function getCalendarDataBySession($dates, $masterData) {
      $data = new \stdClass();
//        $data->appts = $appts;
      // LOOP THROUGH DAYS
      $data->days = [];
      foreach ($dates as $date) {
          $day = new \stdClass();
          $day->carbon = $date;
          $day->date = $date->toDateString();
          $day->displayDay = $date->format("l M jS");
          $day->weekday = $date->format("l");
          // SKIP SUNDAY
          if($date->isSunday()) {
              continue;
          }
              // FOR EACH OFFICE HOURS SESSION GET INFO
              foreach($masterData->officehours as $session) {
                  array_push($day->sessions, $this->getSessionInfo($day, $masterData));
              } // END OF SESSIONS LOOP
          array_push($data->days,$day);
      } // END OF DAY LOOP
      return $data;
  }
  // get session hours and open times
  // returns and OBJECT
  public function getSessionInfo($day, $data) {
      // NEED TO ADD A LOOP IF SAME DOC IS IN MORE THAN 1 SESSION
      // NEEDED IF DOC HAS MORE THAN 1 SESSION
      // SESSION HAS HOURS, MESSAGE, AND TIMES
      $sessions = [];
      $hours = !empty($data->appts) ? array_filter($data->appts, function($h) use($day) {
          dd($h, $h[dayslot]);
          return $h[dayslot] == $day->carbon->dayOfWeek;
      }) : [];
      foreach($hours as $hr) {
          $session = new \stdClass();
          $session->doc_id = $hr->doctor_id;
          $session->doctor = $hr->doctor_id == 2 ? "Dr. Dave" : "Dr. Carr";
          $closed = !empty($data->closed) ? array_filter($data->closed, function ($c) use($day, $hr){
              return $c->closed_date == $day->carbon && $c->doctor_id == $hr->doctor_id;
          }) : [];
          $session->hours = empty($closed) ? $hr : $closed;
          $session->closed = (!empty($closed) && (bool)$closed[0]->closed) || empty($session->hours);
          $session->message = $this->getSessionMessage($session);
          if($session->closed) {
              return $session;
          }
          $session->booked = $this->getSessionBookedAppts($day, $session, $data);
          $session->bookedTimes = array_column($session->booked, 'appt_time');
          $session->times = $this->getSessionTimeslots($day, $session);
          array_push($sessions, $session);
      }
      // dd($sessions)

      return $sessions;
  }

  // display closure and doctor heading
  // RETURNS A STRING
  public function getSessionMessage($session) {
      $message = $session->closed ?
          $session->doctor . " is closed today" . ($session->hours->reason ? ": ". $session->hours->reason : '.')
          : $session->doctor . " HOURS: " . $session->hours->open->format("h:i A") . " - ". $session->close->format("h:i A");
      return strtoupper($message);
  }
  // display closure and doctor heading

  // filter booked appts[] from masterData using session info
  // returns array of appointments
  public function getSessionBookedAppts($day, $session, $data) {
      $appts = array_filter($data->appts, function ($a) use ($day, $session) {
          return $a->appt_date >= $day->carbon->copy()->startOfDay()
              && $a->appt_date <= $day->carbon->copy()->endtOfDay()
              && $a->doctor_id == $session->doc_id
              && $a->appt_status_id == $this->SCHED;
      });

  //        dd($doc, $appts);
      return $appts;
  }
  // get list of available times for session
  // RETURNS AN ARRAY
  public function getSessionTimeslots($day, $session) {
      $times = [];
      // ADMIN ONLY - ADD BOOKED TO THE LIST FOR VIEWING
      array_push($times, $session->booked);
      //unavailable array only for troubleshooting
      $unavailable = [];
      // cycle SESSION hours by 10 minutes and add open time slots
      $t = new Carbon($session->hours->open);
      $end = new Carbon($session->hours->close);
      while ($t <= $end) {
          if(in_array($t, $session->bookedTimes)) {
              // if booked move to next time
              array_push($unavailable, $t->copy()->format("H:i:s"));
          } else {
              $slot = new \stdClass();
              $slot->time = $t->copy()->format("H:i:s");
              $slot->appt_time = $t->copy()->format("h:i A");
              $slot->appt_date = $day->date;
              $slot->appt_status_id = 2;
              $slot->appt_type_id = 1;
              $slot->doctor_id = $session->doc_id;
              array_push($times, $slot);
          }
          // go to next time slot
          $t = $t->copy()->addMinutes(10);
      }

      usort($times, fn($a, $b) => strcmp($a->time, $b->time));

      return $times;
  }


// METHODS TRYING NEW CALENDAR BY DOCTOR
  // loop through days to get calendar display
  // RETURNS AN OBJECT
  public function getApptCalendarData($dates) {
      $data = new \stdClass();
//        $data->appts = $appts;
      // LOOP THROUGH DAYS
      $data->days = [];
      foreach ($dates as $date) {
          $day = new \stdClass();
          $day->carbon = $date;
          $day->date = $date->toDateString();
          $day->displayDay = $date->format("l M jS");
          $day->weekday = $date->format("l");
          // SKIP SUNDAY
          if($date->isSunday()) {
              continue;
          }
          $day->docs = [];
          foreach([1,2] as $doc) {
              array_push($day->docs, $this->getDocInfo($day, $doc, $data));
          } // END OF DOCTOR ID LOOP
          array_push($data->days,$day);
      } // END OF DAY LOOP
      return $data;
  }
  // get doctor hours and open times
  // returns and OBJECT
  public function getDocInfo($day, $doc, $data) {
  $doctor = new \stdClass();
  // if today is closed, add a message and move on
  $doctor->id = $doc;
  $doctor->name = $doc == 2 ? "Dr. Dave" : "Dr. Carr";
  // NEED TO ADD A LOOP IF SAME DOC IS IN MORE THAN 1 SESSION
  // NEEDED IF DOC HAS MORE THAN 1 SESSION
  // SESSION HAS HOURS, MESSAGE, AND TIMES
  $sessions = [];
  $doctor->hours = $this->getOfficeHours($day, $data);
  $doctor->closed = $doctor->hours ? (property_exists($doctor->hours, 'closed') && $doctor->hours->closed == true) : true;
//        dd($doctor->hours, $doctor->isClosed);
  $doctor->message = $doctor->hours ? $this->getHeaderMessage($doctor->hours, $doctor->name) : null;
  // add doc to the list and skip the rest
  if(!isset($doctor->hours) && $doctor->closed == true) {
      return $doctor;
  } else {
      // ADD BOOKED APPTS IF OPEN
      $doctor->booked = $this->getBookedInfo($day, $doctor->hours);
      $doctor->bookedTimes = $doctor->booked->pluck('appt_time')->toArray();
      $doctor->times = $this->getTimeslotsOpen($day, $doctor);
  }


  // dd($doctor)

  return $doctor;
}
  public function getHeaderMessage($docHours, $doctorName) {
      $message = $doctorName . " HOURS: ";
      $open = null;
      $close = null;
      if(isset($docHours->closed_date)) {
          $open = new Carbon($docHours->open);
          $close = new Carbon($docHours->close);
          if($docHours->closed) {
              return $doctorName . " is closed today" . ($docHours->reason ? ": ". $docHours->reason : '.');
          }
      } else {
          $open = new Carbon($docHours->begin);
          $close = new Carbon($docHours->end);
      }
           return strtoupper($doctorName) . " HOURS: " . $open->format("h:i A") . " - ". $close->format("h:i A");

  }
  // get scheduled appts from dayInfo and officehours
  public function getBookedInfo($day, $hours) {

      $doc = $hours->doctor_id;
      $appts = Appt::
      with('patient')
          ->whereBetween('appt_date', [$day->carbon->copy()->startOfDay(), $day->carbon->copy()->endofDay()])
          ->where('doctor_id','=',$doc)
          ->where('appt_status_id',$this->SCHED)->get();

//        dd($doc, $appts);
      return $appts;
  }
  // get list of available times for doc and day
  // RETURNS AN ARRAY
  public function getTimeslotsOpen($day, $docInfo) {
      $times = [];
          $bookedTimes = $docInfo->bookedTimes;
//        $bookedTimes = $docInfo->booked->count() < 0 ? $docInfo->booked->pluck('appt_time')->toArray() : [];
//        dd($day->date, $docInfo, $bookedTimes);
//        // push booked times into times array as objects
      $docInfo->booked->each(function ($appt) use ($times) {
          $time = new Carbon($appt->appt_time);
          $appt->time = $time->format("H:i:s");
          array_push($times, (object)$appt->toArray() );
      });
      $unavailable = [];
      // loop office hours and add open time slots
      $t = property_exists($docInfo->hours, 'begin') ? new Carbon($docInfo->hours->begin) : new Carbon($docInfo->hours->open);
      $end = property_exists($docInfo->hours, 'end') ? new Carbon($docInfo->hours->end) : new Carbon($docInfo->hours->close);
      while ($t <= $end) {
          if(in_array($t, $bookedTimes)) {
              // if booked move to next time
              array_push($unavailable, $t->copy()->format("H:i:s"));
          } else {
              $slot = new \stdClass();
              $slot->time = $t->copy()->format("H:i:s");
              $slot->appt_time = $t->copy()->format("h:i A");
              $slot->appt_date = $day->date;
//                $slot->id = false;
//                $slot->patient_id = false;
              $slot->appt_status_id = 2;
              $slot->appt_type_id = 2;
              $slot->doctor_id = $docInfo->id;
              array_push($times, $slot);
          }
          // go to next time slot
          $t = $t->copy()->addMinutes(10);
      }

      usort($times, fn($a, $b) => strcmp($a->time, $b->time));

      return $times;
  }
  // UNIVERSAL FXN returns ALL HOURS FOR THE DAY
  // NEED TO ADD IF STATEMENT TO LIMIT TO 1 DOC
  // RETURNS COLLECTION IF DOC IS NULL
  // RETURNS OBJECT MODEL IF DOC IS SUPPLIED
  public function getOfficeHours($day, $data) {
      $carbon = new Carbon($day->carbon);
      // USING MASTERDATA AND ARRAY FILTERS
//        dd($data->closed, $data->appts);
      $closed = $data->closed ? array_filter($data->closed, function($c) use ($day, $doc) {
          return $c->closed_date == $day->date && $c->doctor_id == $doc;
      }) : [];
      $hours = $data->appts ? array_filter($data->appts, function($a) use($carbon, $doc) {
          return $a->dayslot == $carbon->dayOfWeek && $a->doctor_id == $doc;
      }) : [];
//        dd($closed, $hours, $day->date);
      // USING ELOQUENT QUERIES BY DAY AND DOC
//        $closed = Closed::whereClosedDate($carbon)->get();
//        $hours = Officehour::whereDayslot($carbon->dayOfWeek)->get();
//        $h =  $closed->count() > 0 ? $closed : $hours;
      // if filter for doc?
      if(!is_null($doc)) {
          $c = $closed->where('doctor_id','=',$doc)->first();
          $oh = $hours->where('doctor_id','=',$doc)->first();
          $h =  !is_null($c) ? $c : $oh;
      }
//
//        if($carbon->isFriday()) {
//            dd($carbon->format("l"), $h, $hours, $closed);
//        }
      return $h;
  }
// END METHODS FOR NEW CALENDAR BY DOCTOR

My models: Appt Model

class Appt extends Model
{
    use SoftDeletes;
	protected $table = 'appt';
	public $primaryKey = 'id';
    public $timestamps = true;


    protected $casts = [
		'patient_id' => 'int',
		'appt_reminder' => 'bool',
		'appt_type_id' => 'int',
		'appt_status_id' => 'int',
        'appt_date' => 'datetime:Y-m-d',
        'appt_time' => 'datetime:h:i A',
        'doctor_id'=> 'int'
	];

	protected $dates = [
		'appt_date',
		'created_at',
		'updated_at'
	];

	protected $fillable = [
		'patient_id',
		'appt_date',
		'appt_time',
		'appt_note',
		'appt_reminder',
		'reminder_cell',
		'appt_type_id',
		'appt_status_id',
		'subjective',
        'objective_text',
		'assessment',
		'plan',
		'clearUC',
		'updated_at',
        'doctor_id'
	];

	public function patient()
	{
		return $this->belongsTo(Patient::class, 'patient_id');
	}

	public function appt_type()
	{
		return $this->belongsTo(ApptType::class);
	}

	public function appt_status() {
	    return $this->belongsTo(ApptStatus::class);
    }

    public function family() {
        return $this->hasOneThrough(Family::class, Patient::class,'family_id','id','patient_id');
    }

    public function patient_name() {
	    $fullName = $this->patient()->get('first') . ' '. $this->patient()->get('last');
	    return $fullName;
    }

    public function payments()
    {
        return $this->hasMany(Payment::class, 'patient_id','patient_id')->orderBy('pmt_duedate','desc');

    }
    public function appts() {
        return $this->hasMany(Appt::class, 'patient_id', 'patient_id')->orderBy('appt_date','desc');
    }

    public function getHasFutureAppt() {
        return Appt::whereDate('appt_date', '>', new Carbon('today'))->where(['patient_id' => $this->patient_id, 'appt_status_id' => 2])->orderBy('appt_date','asc')->first();
    }

    public function doctor() {
        return $this->belongsTo(User::class,'doctor_id','id');
    }

Officehours Model

class Officehour extends Model
{
    use SoftDeletes;
	protected $table = 'officehour';
	public $timestamps = false;

	protected $casts = [
		'dayslot' => 'int',
		'type' => 'int'
	];

	protected $dates = [
		'begin',
        'end'
	];

	protected $fillable = [
		'dayslot',
		'type',
        'doctor_id',
        'begin',
        'end',
        'weekday'
	];


    public function doctor() {
        return $this->belongsTo(User::class,'doctor_id','id');
    }
}

Closed Model

class Closed extends Model
{
    use SoftDeletes;
	protected $table = 'closed';
	public $timestamps = false;
	public $primaryKey = 'id';

	protected $dates = [
		'closed_date',
		'open',
		'close'
	];

	protected $fillable = [
		'closed_date',
		'reason',
		'open',
		'close',
        'closed',
        'doctor_id'
	];


    public function doctor() {
        return $this->belongsTo(User::class,'doctor_id','id');
    }
}

In my view, I''m using components, daycardcomponent

<div class="rounded-lg shadow-lg col-md-2 pb-2 flex-column">
    {{-- DAY HEADING --}}
    <div class="block rounded-top bg-slate-800 text-white {{ $day->carbon ==  new \Carbon\Carbon("today") ? "text-yellow-500" : ""}}">
        <h5 class="p-2 text-md font-semibold">{{ $day->displayDay }}</h5>
    </div>
    <div>
        @foreach($day->docs as $doc)
{{--            <div>{{ var_dump($doc) }}</div>--}}
{{--            <p>{{ var_dump($doc->hours) }}</p>--}}
{{--            <p>{{ "Slots: ". count($doc->times) }}</p>--}}
{{--            <p>{{ "Booked: ". $doc->booked->count() }}</p>--}}
            @if($doc->message)
                <x-daydocheader :doc="$doc" :doctor="$doc->id"></x-daydocheader>
            @endif
            @if($doc->closed == false)
                <div class="flex flex-wrap mt-0 pt-0 {{ $doc->id == 2 ? "bg-blue-50" : "bg-pink-50" }}">
                    @foreach($doc->times as $appt)
{{--                        {{ dd($appt) }}--}}
                        <x-daybookbuttons :day="$day" :doc="$doc->id" :appt="$appt"></x-daybookbuttons>
                    @endforeach
                </div>

            @endif
        @endforeach
    </div>




</div>

daydocheader (for each session)

<h5 class="p-2 mb-0 font-monospace text-center font-lg font-semibold {{ $doctor > 1 ? 'text-blue-800 bg-blue-100' : 'text-pink-800 bg-pink-50'}}">{!! strtoupper($doc->message) !!}</h5>

daybookbuttons

@isset($appt->patient_id)
    {{ dd($appt) }}
    <a role="button" class="m-2 btn w-2/5 btn-sm VIP {{ $doc > 1 ? 'bg-emerald-200 hover:bg-emerald-400' : 'bg-violet-200 hover:bg-violet-400'}}" href="/appts/{{$appt->id}}/edit"> {{  $appt->patient ? $appt->patient->nickname : $appt->patient_id." ".  $appt->appt_time }} </a>
@endisset

    @if($appt->appt_type_id == 1)
        <a role="button" class="m-2 btn w-2/5 btn-sm VIP {{ $doc > 1 ? 'bg-blue-600 hover:bg-blue-800 text-white' : 'bg-pink-600 hover:bg-pink-800'}}" href="/appt/create/{{$day->date}}/{{$appt->time}}/{{$appt->appt_type_id}}/{{$doc}}"> {{ $appt->appt_time }} </a>
    @endif
    @if($appt->appt_type_id == 2)
        <a role="button" class="m-2 btn w-2/5 btn-sm NP {{ $doc > 1 ? 'bg-blue-300 hover:bg-blue-500' : 'bg-pink-300 hover:bg-pink-500'}}" href="/appt/create/{{$day->date}}/{{$appt->time}}/{{$appt->appt_type_id}}/{{$doc}}"> {{ $appt->appt_time }} NEW PATIENT</a>
    @endif