SELECT COUNT(a.id) as total, e.* FROM employee AS e
LEFT JOIN appointment as a ON (
a.employee_id = e.id AND (
a.start_time >= "11:00:00" OR
a.end_time <= "11:45:00"
)
AND a.service_id = 1
)
GROUP BY e.id
HAVING total < 3
ORDER BY total ASCpublic 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();
}