select e.id,
        date(e.execDate)                                                as 'дата',
       apt.value                                                 as 'время',
       apt.`index`                                               as 'индекс',
       apa_nom.value                                                as 'тикет',
       at.code                                                      as 'тип записи',
       api_nes.value                                                as 'не для вс',
       api_uo.value                                                as 'уо',
       api_tm.value                                                as 'ТМ',
       concat_ws(' ', p.id, p.lastName, p.firstName, p.patrName) as 'врач',
       a2.createDatetime as 'Когда записан',
       concat_ws(' ', c.id, c.lastName, c.firstName, c.patrName) as 'клиент',
       a2.note
from Event e
         join Action a on e.id = a.event_id
         join ActionType at on a.actionType_id = at.id
         join Person p on e.execPerson_id = p.id


         join ActionPropertyType apT1 on a.actionType_id = apT1.actionType_id and apT1.name like '%times%'
         join ActionProperty ap1 on a.id = ap1.action_id and ap1.type_id = apT1.id
         left join ActionProperty_Time apt on ap1.id = apt.id


         join ActionPropertyType apt_nom on a.actionType_id = apt_nom.actionType_id and apt_nom.name = REPLACE(apT1.name, 'times', 'queue')
         join ActionProperty ap_now on a.id = ap_now.action_id and ap_now.type_id = apt_nom.id and ap_now.deleted=0
         left join ActionProperty_Action apa_nom on ap_now.id = apa_nom.id and apt.`index` = apa_nom.`index`

         join ActionPropertyType apt_new on a.actionType_id = apt_new.actionType_id and apt_new.name = 'notExternalSystems'
         join ActionProperty ap_nes on a.id = ap_nes.action_id and ap_nes.type_id = apt_new.id  and ap_nes.deleted=0
         left join ActionProperty_Integer api_nes on ap_nes.id = api_nes.id and apt.`index` = api_nes.`index`

         join ActionPropertyType apt_uo on a.actionType_id = apt_uo.actionType_id and apt_uo.name = 'uos'
         join ActionProperty ap_uo on a.id = ap_uo.action_id and ap_uo.type_id = apt_uo.id and ap_uo.deleted=0
         left join ActionProperty_Integer api_uo on ap_uo.id = api_uo.id and apt.`index` = api_uo.`index`

         left join ActionPropertyType apt_tm on a.actionType_id = apt_tm.actionType_id and apt_tm.name = 'tms'
         left join ActionProperty ap_tm on a.id = ap_tm.action_id and ap_tm.type_id = apt_tm.id and ap_tm.deleted=0
         left join ActionProperty_Integer api_tm on ap_tm.id = api_tm.id and apt.`index` = api_tm.`index`

         left join Action a2 on apa_nom.value = a2.id
         left join Event e2 on a2.event_id = e2.id
         left join Client c on e2.client_id = c.id

where e.eventType_id = 5
  and at.code = 'amb'
  and a.deleted = 0
  and (a2.deleted = 0 or a2.deleted is null)
  and (e2.deleted = 0 or e2.deleted is null)
  and e.deleted = 0
  and ap1.deleted = 0
  and ap_now.deleted = 0
  and e.execPerson_id in (566)                                           -- тут врача подставляй
  #and apa_nom.value is null -- если нужны свободные слоты
#   and date(e.execDate) between '2025-06-18 00:00:00' and adddate('2025-06-18 00:00:00', 50) -- интервал на которые смотрим даты
  and date(e.execDate) between current_date and adddate(current_date, 14) -- интервал на которые смотрим даты
order by e.execDate, apt.value, p.id;