Ejtma is a meeting room booking system used by companies in Saudi Arabia. A common question in any booking system is "why can't I book this room? It looks free." In Ejtma the answer is usually one of three rules: setup time, a booking that waits for approval, or one meeting in a weekly series. This post shows how we check conflicts and how each rule works.
Two pairs of times per booking
A booking has the time the user picked (start_at, end_at) and the time the room is actually busy (actual_start_at, actual_end_at).
The difference comes from services. A booking can request services such as catering. Each service has preparing and cleaning minutes, and the room is blocked for those minutes too:
$services = Service::getCached()->whereIn('id', $serviceIds); $actualStartAt = $startAt->subMinutes($services->pluck('preparing')->sum()); $actualEndAt = $endAt->addMinutes($services->pluck('cleaning')->sum()); A 10:00 to 11:00 meeting with catering that needs 20 minutes to set up and 30 to clear blocks the room from 09:40 to 11:30. The calendar shows 10:00 to 11:00, because that is the meeting. Conflict checks use the actual times, because that is when the room is busy.
The overlap scope
All conflict queries use one scope. It lists the four ways two ranges can overlap:
public function scopeWhereOverlap(Builder $builder, string $startColumn, string $endColumn, CarbonInterface $startDate, CarbonInterface $endDate): Builder { return $builder->where(function (Builder $builder) use ($startColumn, $endColumn, $startDate, $endDate) { $builder->where(function (Builder $builder) use ($startColumn, $endColumn, $startDate, $endDate) { // INSIDE [ <-----> ] $builder->where($startColumn, '>=', $startDate)->where($endColumn, '<=', $endDate); })->orWhere(function (Builder $builder) use ($startColumn, $endColumn, $startDate, $endDate) { // INTERSECT <--[-----]--> $builder->where($startColumn, '<=', $startDate)->where($endColumn, '>=', $endDate); })->orWhere(function (Builder $builder) use ($startColumn, $endColumn, $startDate, $endDate) { // START <---[---> ] $builder->where($startColumn, '<=', $startDate)->where($endColumn, '>', $startDate)->where($endColumn, '<=', $endDate); })->orWhere(function (Builder $builder) use ($startColumn, $endColumn, $startDate, $endDate) { // END [ <---]---> $builder->where($startColumn, '>=', $startDate)->where($startColumn, '<', $endDate)->where($endColumn, '>=', $endDate); }); }); } The ASCII comments make code review easy. The strict > and < in the last two cases matter: a meeting that ends at 11:00 does not conflict with one that starts at 11:00. Back-to-back bookings are normal and must work.
If you write this from scratch, the short form gives the same result for normal ranges: start < :end AND end > :start.
Pending bookings hold the slot
Some bookings need approval in up to three parts: the time, the requested services, and a parking request. While a booking waits, it still blocks the room:
->whereIn('status', [ Reservation::STATUS_PUBLISHED, Reservation::STATUS_PENDING_TIME_APPROVAL, Reservation::STATUS_PENDING_SERVICES_APPROVAL, Reservation::STATUS_PENDING_PARKING_APPROVAL, ]) The other choice is "first approved wins". With that rule, two people book the same room, both wait two days, one gets approved, and the other learns late that the room is gone. Holding the slot means the second person sees the conflict at once and picks another time.
The check runs again when an approver accepts a booking. Data can change between the request and the approval, and the approver should not publish a conflict.
Editing a booking must not conflict with itself
A published booking can be changed. The change creates a new version that waits for approval, and the published version stays on the calendar until then. Without care, the new version conflicts with its own parent. The check excludes the record and every version of the same booking:
->when($recordId, fn (Builder $q) => $q->whereNot('id', $recordId)) ->when($excludeMasterId, fn (Builder $q) => $q->whereNot('version_master_id', $excludeMasterId)) Recurring series: all or nothing
A weekly meeting for three months has about 13 occurrences. If one of them conflicts, we reject the whole series and name the date:
foreach ($occurrences as $occ) { $actualStart = $occ['start']->subMinutes($services->pluck('preparing')->sum()); $actualEnd = $occ['end']->addMinutes($services->pluck('cleaning')->sum()); if ($conflict = ConflictCheckService::check($actualStart, $actualEnd, $roomId)) { throw ValidationException::withMessages([ __('app.recurring_conflicts_found').': '.$occ['start']->format('Y-m-d H:i') .' - '.$conflict->pluck('title')->implode(', '), ]); } } Creating 12 of 13 meetings and skipping one sounds friendly. But a skipped week is easy to miss in a warning, and the team then arrives at a room that is taken. A clear rejection with the date lets the organizer move that one week or pick another room.
Each occurrence is also checked against room maintenance windows, with the same buffers.
Suggest, don't only refuse
When a single booking conflicts, the booking form asks for suggestions in this order:
- The same room, at other free times on the same day.
- Other rooms that are free at the requested time.
- The same room and time on the next working day.
Every suggestion is checked against bookings and maintenance windows before it is shown.
ملخص بالعربية: يشرح هذا المقال كيف يمنع نظام حجز قاعات الاجتماعات تعارض الحجوزات. يحسب النظام وقت تجهيز الخدمات وتنظيفها ضمن مدة الحجز. ويبقي الحجز المعلق بانتظار الموافقة محجوزا. ويرفض السلسلة المتكررة كاملة إذا تعارض أحد مواعيدها. وعند تعارض حجز واحد، يقترح النظام أوقاتا أو قاعات بديلة.
Written by the engineering team at Renewable Systems in Riyadh, the makers of Ejtma.