Как реализовать сервис услуг «запись к кому-то(доктор, психолог и т.д.)»?

Question

[lemonlimelike]

Всем привет! Разрабатываю сервис услуг. Имеются такие таблицы с такими полями:

Table service - таблица содержит настройки для сервиса, который будет создаваться;

mysql> describe service;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int          | NO   | PRI | NULL    | auto_increment |
| name          | varchar(255) | NO   |     | NULL    |                |
| location      | varchar(255) | YES  |     | NULL    |                |
| description   | varchar(255) | YES  |     | NULL    |                |
| event_link    | varchar(255) | NO   |     | NULL    |                |
| event_color   | varchar(100) | YES  |     | NULL    |                |
| start_period  | date         | NO   |     | NULL    |                |
| end_period    | date         | NO   |     | NULL    |                |
| duration      | time         | NO   |     | NULL    |                |
| buffer_after  | time         | NO   |     | NULL    |                |
| buffer_before | time         | NO   |     | NULL    |                |
| max_customer  | int          | NO   |     | NULL    |                |
| is_enable     | tinyint(1)   | NO   |     | NULL    |                |
| type          | varchar(50)  | NO   |     | NULL    |                |
| optimization  | varchar(50)  | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

Пример что содержится в таблице:

mysql> select * from service;
+----+---------+----------+-------------+------------+-------------+--------------+------------+----------+--------------+---------------+--------------+-----------+--------------+--------------+
| id | name    | location | description | event_link | event_color | start_period | end_period | duration | buffer_after | buffer_before | max_customer | is_enable | type         | optimization |
+----+---------+----------+-------------+------------+-------------+--------------+------------+----------+--------------+---------------+--------------+-----------+--------------+--------------+
|  1 | Default | NULL     | NULL        | default    | NULL        | 2020-12-20   | 2021-02-19 | 00:40:00 | 00:00:00     | 00:00:00      |            1 |         1 | organization | distribution |

Table intervals_rule - содержит график работ для сервиса:

mysql> describe intervals_rule;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int         | NO   | PRI | NULL    | auto_increment |
| service_id | int         | YES  | MUL | NULL    |                |
| start_time | time        | NO   |     | NULL    |                |
| end_time   | time        | NO   |     | NULL    |                |
| name_day   | varchar(50) | NO   |     | NULL    |                |
| type       | varchar(50) | NO   |     | NULL    |                |
| date       | date        | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

Пример что содержится в таблице intervals_rule
mysql> select * from intervals_rule;
+----+------------+------------+----------+----------+------+------+
| id | service_id | start_time | end_time | name_day | type | date |
+----+------------+------------+----------+----------+------+------+
|  1 |          1 | 09:30:00   | 17:00:00 | Mon      | wday | NULL |
|  2 |          1 | 09:30:00   | 17:00:00 | Tue      | wday | NULL |
|  3 |          1 | 09:30:00   | 17:00:00 | Thu      | wday | NULL |
|  4 |          1 | 09:30:00   | 17:00:00 | Wed      | wday | NULL |
|  5 |          1 | 09:30:00   | 17:00:00 | Sat      | wday | NULL |
|  6 |          1 | 09:30:00   | 17:00:00 | Sun      | wday | NULL |
|  7 |          2 | 09:30:00   | 17:00:00 | Sun      | wday | NULL |
+----+------------+------------+----------+----------+------+------+
7 rows in set (0.00 sec)

Table appointment - содержит клиентов, которые записались к сервису:

mysql> describe appointment;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| id          | int      | NO   | PRI | NULL    | auto_increment |
| service_id  | int      | YES  | MUL | NULL    |                |
| employee_id | int      | YES  | MUL | NULL    |                |
| id_customer | int      | YES  |     | NULL    |                |
| notes       | longtext | YES  |     | NULL    |                |
| start_time  | time     | NO   |     | NULL    |                |
| end_time    | time     | NO   |     | NULL    |                |
| date        | date     | NO   |     | NULL    |                |
+-------------+----------+------+-----+---------+----------------+

Пример что содержится в таблице:
mysql> select * from appointment;
+----+------------+-------------+-------------+--------+------------+----------+------------+
| id | service_id | employee_id | id_customer | notes  | start_time | end_time | date       |
+----+------------+-------------+-------------+--------+------------+----------+------------+
|  1 |          1 |           1 |           5 | String | 12:00:00   | 12:40:00 | 2020-12-31 |
+----+------------+-------------+-------------+--------+------------+----------+------------+

С этими таблицами, с таким содержание, все работает хорошо. Все добавляется. Но проблема вот в чем. С такой бд я не смогу получить интервалы времени, которые заняты или свободны.
Для сведения, сервис содержит поле duration, которое описывает длительность работы "сеанса" с клиентом; на один и тот же момент времени может быть лишь один appointment(клиент).

Таблица intervals_rule имеет поля start_time, end_time это поля, которые показывают со скольки и до скольки работает сервис; name_day это имя дня(Mon, Thu, Tue, Wed,Fri,Sat,Sun). То есть таблица отвечает в какой день работает сервис и время работы сервиса.
Мне нужно как-то получить интервалы времени, которые свободны или заняты для сервиса. Типа... имеется день Mon, у него график работы 09:00-17:00, длительность сеанса с клиентом 30 минут. Получаются интервалы времени: 09:00, 09:30, 10:00,10:30,11:00,11:30,12:00 и т.д.. То есть нужно получить интервалы, но некоторые из них могут быть заняты. Как такое реализовать? Получается, что нужно делать еще одну таблицу? Но в таком случает будет очень много записей. К примеру: 1 сервис, у него 7 записей в intervals_rule и у каждой записи из intervals_rule будет по 20 записей в другой таблице с интервалами. Это не слишком много?

Я придумал лишь одно решение. Это создавать еще одну таблицу с интервалами. По другому можно как-то решить эту проблему? Может у меня изначально реализация неверная? Подскажите, пожалуйста

Comments

[Slava Rozhnev]

Пока в базе меньше миллиарда строк, это не много. Дерзай! Удачи!

[lemonlimelike]

Slava Rozhnev, Да?)) А другое решение есть?)

[AVSomov]

Рекомендую оценить, что хуже для данного конкретного проекта, работать:

• только с периодами - тогда, чтобы получить список свободных интервалов потребуется сформировать список всех возможных интервалов для заданного диапазона (способ зависит от версии, рекомендую посмотреть в сторону cte-запросов) с длительностью и перебрать занятые, исключая их из списка всех
  
• с таблицей интервалов - увеличивает потенциальный объём данных, но существенно упрощает получение списка свободных
  

Т.е. фактически приходим к вопросу о границах проекта, т.к. узким местом является ЦП+ОЗУ или диск.

PS: по моему опыту, получать список свободных интервалов будут очень часто, а создание новых периодов - гораздо реже. К тому же дисковое место во многих проектах не выделяется как существенно ограничение.

[Slava Rozhnev]

AVSomov, Согласен, сегодня место на диске гораздо дешевле процессорного времени, и времени программиста

[Дмитрий]

Классная задача. Я буквально в декабре ее реализовал на laravel+vue+pusher для real-time. Не уверен, что мое решение максимально эффективно, но работает.
напишите мне в почту, если интересно, могу своим опытом поделиться.

[lemonlimelike]

Дмитрий, Написал

[Сергей]

У нас отдельная таблица, которая каждый день генерируется на N дней вперёд. В ней на каждый интервал времени сотрудника отдельная запись: 1 Иванов И.И. с 8:00 до 8:30, 2 Иванов И.И с 8:30 до 9:00. и так по всему дню на несколько недель вперёд. В ней же хранится метка "Время занято".
После генерации табличка очищается от старых записей, которые были свободны. Остаются только занятые.
Плюс есть таблица, которая хранит события, например обед сотрудника, отпросился сотрудник, заболел и тд. Там хранится промежуток времени, если мы находим для сотрудника событие, то в табличке не создаём время приёма или удаляем (блокируем) если оно уже было

Answer 1

[Александр]

.Когда-то выполнял такую задачу. Конечный результат так и не могу найти, а вот файлы наброски умудрился найти какимто образом. Может поможет направить в нужное русло.

<?php
$startwork='09:00:00'; //Начало работы
$endwork='9'; //количество часов рабочих

$dayw=date("w", strtotime($_SESSION['startday']));
for($i=0; $i < 7; $i++) {
	if ($dayw>='7') { $dayw='0'; }
	$date = date('d-m-Y', strtotime($_SESSION['startday'].'+'.$i.' day'));
	if ($dayw!="0" or $dayw!="6") {
		if ($dayw=="1")  { $daywt="Понедельник"; 
		} elseif ($dayw=='2') { $daywt="Вторник";
		} elseif ($dayw=='3') { $daywt="Среда";
		} elseif ($dayw=='4') { $daywt="Четверг"; 
		} elseif ($dayw=='5') { $daywt="Пятница"; 
		} elseif ($dayw=='6') { $dayw++; continue; $daywt="Суббота"; 
		} elseif ($dayw=='0') { $dayw++; continue; $daywt="Воскресенье"; 
		}
		echo ($daywt."<br>".$date."<br>");
		
		$a=0; //Переменная для цикла считает часы
		$time=$startwork; //Переменная для цикла начало времени
		while ($a<=$endwork) {
				$STH = $mysqlcon->query("SELECT * from ".$bookdb." WHERE (datestart<='".$datesearch."' AND dateend>='".$datesearch2."')");  
				$num_rows = $STH->rowCount();
				if ($num_rows=='0') {
					echo ('Свободно<br>');
				} else {
					echo ('Занято<br>');
				}
				
				$time = date('H:i:s', strtotime($time.'+30 minute'));
				$a=$a+0.5; // Бронирование ставится на каждые пол часа
			}
		}
		$dayw++;
}
?>
 
Если как-то доработаешь, поделись пожалуйста. Очень жалею что потерял полный исходник :(
}

Comments

[lemonlimelike]

Получился такой метод:

public function __invoke(iterable $collection, array $context): iterable
   {
      $path = explode('/', $context['args']['service']);
      $idService = array_pop($path); // id сервиса

      $service = $this->serviceRepository->find($idService); // получаю сам сервис
      if (is_null($service)) return null;

      $date =  new \DateTime($context['args']['date']);

      $intervalsRule = $this->intervalsRepository->findOneBy([
         'name_day' => $date->format("D"),
         'service' => $service,
      ]); // получаю график работы для сервиса

      $startTime = $intervalsRule->getStartTime();
      $minute = $service->getDuration()->format("i");
      $hour = $service->getDuration()->format("H");
      $endTime = $intervalsRule->getEndTime();

      $interval = new \DateInterval("PT${hour}H${minute}M");
      $slots = [];

      while ($startTime <= $endTime){ // формирую список интервалов
         
         $slots[] = "ROW('{$startTime->format('H:i')}')";
         $startTime = $startTime->add($interval);
      }

      $queryString = implode(",", $slots);

      $intervals = $this->intervalsRepository->getFreeIntervals($service->getId(), $date->format("Y-m-d"), $queryString); // получаю интеварлы которые свободны

      $collection = []; // формирую коллекцию из объектов
      foreach($intervals as $value){
         $inter = new Interval();
         $inter->setSlot($value['column_0']);

         $collection[] = $inter;
      }

      return $collection;
   }

и вот метод для получения свободных интервалов из бд

public function getFreeIntervals(int $id, string $date, string $queryString)
    {
        $entityManager = $this->getEntityManager()->getConnection();

        $sql = "select slot.column_0
        from (values $queryString) slot 
        inner join appointment ap 
        where slot.column_0 not between ap.start_time and ap.end_time
        and service_id = :id and date = :date";
        $stmt = $entityManager->prepare($sql);
        $stmt->execute([
            'id' => $id,
            'date' => $date
        ]);

        return $stmt->fetchAllAssociative();
    }

Использую symfony