Пользовательские функции, триггеры и хранимые процедуры

[beginer_38 (Programmer in future)]

Добрый день, уважаемые участники сообщества. Я снова к вам за помощью. Помогите разобраться. У меня есть бд(в прикрепленном файле)
Не могу понять, как выполнить вот эти пункты:

7. Запретить записывать клиента к барберу на уже занятое
   время и дату

В этом случае по всей видимости триггер, я сделал что-то вроде:

create trigger tg_booking_taboo on bookings
after insert
as
begin
if exists(select*from
bookings b,inserted i 
where b.start_time=i.start_time and b.barber_id=i.barber_id
and b.week_day_id=i.week_day_id)
begin
rollback tran
print('This date is busy')
end
end

Но это, естественно, не правильно и я не пойму, как реализовать то,чтобы учитывалось расписание барбера, продолжительность услуги и так далее.

5. Показать свободные временные слоты на неделю конкрет-
   ного барбера. Информация о барбере и дне передаётся
   в качестве параметра

Ну и здесь подобная ситуация, только тут я решил сделать хранимую процедуру. Я связываю джоинами все, на мой взгляд, нужные таблицы, но как учесть остальное:

create proc sp_free_time @day nvarchar(30),@name nvarchar (50),@surname nvarchar(50)
as
begin
select b.f_name,b.l_name 
from [services] s join barber_service bs on s.id=bs.service_id
join barbers b on b.id=bs.barber_id
join schedule sc on sc.barber_id=b.id
join week_days w on w.id=sc.week_day_id
join bookings bk on bk.week_day_id=w.id
where @day=w.name and @name=b.f_name and @surname=b.l_name
end

Barber_shop_db.txt (6.4 КБ)

[AlexP]

@beginer_38

как реализовать то,чтобы учитывалось расписание барбера, продолжительность услуги и так далее.

Надо как-нибудь проверить пересекаются ли эти интервалы. https://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap/325964#325964

Но тут непонятно откуда брать продолжительность, в таблице только время начала.

create table bookings(
id int identity primary key,
client_id int,
barber_id int,
week_day_id int,
start_time time)

[beginer_38 (Programmer in future)]

Чтобы узнать продолжительность, нужно посмотреть в [services] длительность услуги. И да, наверно, нужно еще добавить join booking_service. И я все равно не понимаю как все это посчитать.Мой мозг закипел

[AlexP]

Так посчитать же просто, по ссылке выше что-то типа b.start_time < (i.start_time + i.duration) and (b.start_time + b.duration) > i.start_time (синтаксис скорее всего неправильный, возможно нужны алиасы и т.п. для duration)
надо только правильно приджоинить всё.

[beginer_38 (Programmer in future)]

Попробую, пока забил на это. Переключился на шарп)

[beginer_38 (Programmer in future)]

А подскажите пожалуйста вот так вообще можно делать:

select*from
bookings b,inserted i join booking_service bs

То есть приджойнить к двум таблицам. Студия вроде не ругается, но я сомневаюсь

[Аватар]

К двум таблицам не получится. Можно же самому проверить написав в ON соответствующее условие

[beginer_38 (Programmer in future)]

Короче я написал вот такое:

create trigger tg_booking_taboo on bookings
after insert
as
begin
if exists(select*from
bookings b join inserted i on b.barber_id=i.barber_id and b.week_day_id=i.week_day_id
join booking_service bs
on bs.booking_id=b.id join [services] s on s.id=bs.service_id 
where b.start_time<(select dateadd(minute,datepart(minute,s.duration),i.start_time)) 
and (select dateadd(minute,datepart(minute,s.duration),b.start_time))>i.start_time)
begin
rollback tran
print('This date is busy')
end
end

И теперь у меня проблема, как сделать так чтобы тут

dateadd(minute,datepart(minute,s.duration),i.start_time)

считалась сумма всех процедур (вдруг их несколько).

[AlexP]

@beginer_38

select dateadd(minute,datepart(minute,s.duration),i.start_time)

Разве тут нужен select?

@beginer_38

считалась сумма всех процедур (вдруг их несколько).

Это как? Там же вроде одна процедура в каждой строке таблицы bookings. Если больше одной, то просто добавлять строки с соотв. временем.

[beginer_38 (Programmer in future)]

Теперь вроде бы почти все работает, за исключением того, что я могу спокойно добавить клиента, когда рабочий день закончился и когда он еще не начался, не понимаю в чем дело, вроде все логично:

create trigger tg_booking_taboo on bookings
after insert
as
begin 
declare @duration_sum int
declare @client int
declare @barber int
declare @end_time time
declare @start_time time
declare @week_day int
select @client=client_id, @barber=barber_id, @week_day=week_day_id 
from inserted
set @end_time=(select end_time from schedule where barber_id=@barber 
and week_day_id=@week_day)
set @start_time=(select start_time from schedule where barber_id=@barber
and week_day_id=@week_day)
set @duration_sum=(select sum(datediff(minute,'00:00:00',s.duration)) 
from bookings b join booking_service bs on bs.booking_id=b.id
join [services] s on s.id=bs.service_id where b.client_id=@client) 
if exists(select*from
bookings b join inserted i on b.barber_id=i.barber_id 
and b.week_day_id=i.week_day_id
join booking_service bs on bs.booking_id=b.id 
join [services] s on s.id=bs.service_id 
where b.start_time<(dateadd(minute,@duration_sum,i.start_time)) 
and (dateadd(minute,@duration_sum,b.start_time))>i.start_time
and i.start_time>=@end_time
and i.start_time<@start_time)
begin
rollback tran
print('This date is busy')
end
end

Я делаю вроде проверку:

and i.start_time>=@end_time
and i.start_time<@start_time)

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

[AlexP]

Так а тут и не просили проверять работает ли он в это время.
Это можно отдельным тригером или if’ом сделать, чтобы не путаться (и выдать соотв. сообщение).

@beginer_38

and i.start_time>=@end_time
and i.start_time<@start_time)

В первом видимо должно быть start_time + duration_sum.

[beginer_38 (Programmer in future)]

Так и не разобрался с этой задачей. Переделал немного теперь вообще никто не добавляется

create trigger tg_booking_taboo on bookings
after insert
as
begin 
declare @duration_sum int
declare @end_time time
declare @start_time time
declare @client int
declare @barber int
declare @week_day int
select @client=client_id, @barber=barber_id, @week_day=week_day_id
from inserted
set @duration_sum=(select sum(datediff(minute,'00:00:00',s.duration)) 
from bookings b join booking_service bs on bs.booking_id=b.id
join [services] s on s.id=bs.service_id where b.client_id=@client)
set @end_time=(select end_time from schedule where barber_id=@barber 
and week_day_id=@week_day)
set @start_time=(select start_time from schedule where barber_id=@barber
and week_day_id=@week_day)
if exists(select*from inserted i,bookings b 
where i.id=b.id 
and (b.start_time<(dateadd(minute,@duration_sum,i.start_time)) 
and (dateadd(minute,@duration_sum,b.start_time))>i.start_time)
or (i.start_time<@start_time and 
(dateadd(minute,@duration_sum,i.start_time)>=@end_time)))
begin
rollback tran
print('This time is busy')
end
end

Мне кажется, я не правильно джойню. В этом случае
if exists(select*from inserted i,bookings b where i.id=b.id у меня вообще нет джойнов и я не могу добавить никого вообще. А в первом случае
if exists(select*from bookings b join inserted i on b.barber_id=i.barber_id and b.week_day_id=i.week_day_id join booking_service bs on bs.booking_id=b.id join [services] s on s.id=bs.service_id
добавляю куда хочу. Нужно найти золотую середину)

[AlexP]

@beginer_38

if exists(......
where i.id=b.id 
...
and (dateadd(minute,@duration_sum,b.start_time))>i.start_time)
or (i.start_time<@start_time

Тут с and и or явно что-то не то, получится например “id совпадают ИЛИ начало раньше начала рабочего дня”.

Потому и говорю

@AlexP

отдельным тригером или if’ом сделать, чтобы не путаться (и выдать соотв. сообщение).

Например просто два разных if exists ... rollback

[beginer_38 (Programmer in future)]

Да я уже все перепробовал. Вот, например, был у меня и такой вариант, который тоже не работает:

create trigger tg_booking_taboo on bookings
instead of insert
as
begin 
declare @duration_sum int
declare @end_time time
declare @start_time time
declare @client int
declare @barber int
declare @week_day int
select @client=client_id, @barber=barber_id, @week_day=week_day_id
from inserted
set @duration_sum=(select sum(datediff(minute,'00:00:00',s.duration)) 
from bookings b join booking_service bs on bs.booking_id=b.id
join [services] s on s.id=bs.service_id where b.client_id=@client)
set @end_time=(select end_time from schedule where barber_id=@barber 
and week_day_id=@week_day)
set @start_time=(select start_time from schedule where barber_id=@barber
and week_day_id=@week_day)
if exists(select*from
bookings b join inserted i on b.barber_id=i.barber_id and b.week_day_id=i.week_day_id
join booking_service bs
on bs.booking_id=b.id join [services] s on s.id=bs.service_id 
where (b.start_time<(dateadd(minute,@duration_sum,i.start_time)) 
and (dateadd(minute,@duration_sum,b.start_time))>i.start_time))
begin
print('This time is busy')
rollback tran
end
else if exists(select*from inserted i,bookings b 
where i.id=b.id 
and i.start_time<@start_time and 
(dateadd(minute,@duration_sum,i.start_time)>=@end_time))
begin
print('The working day has not started or has ended')
rollback tran
end
else 
begin
insert bookings(client_id,barber_id,week_day_id,start_time)
select client_id,barber_id,week_day_id,start_time 
from inserted
end
end

[AlexP]

Так а тут же вроде работало всё, кроме проверки рабочего дня.

[beginer_38 (Programmer in future)]

Ну да, но как только я добавляю проверку рабочего дня, у меня перестает работать все

[AlexP]

Первое условие тоже перестало работать что ли?

Если просто добавить новый if с проверкой рабочего времени (и убрать эти условия из первого), то вряд ли перестанет работать.

@beginer_38

from inserted i,bookings b

Так лучше не делать. https://stackoverflow.com/questions/894490/sql-left-join-vs-multiple-tables-on-from-line

Просто JOIN как выше было.

[beginer_38 (Programmer in future)]

@AlexP

Первое условие тоже перестало работать что ли?

Нет, первое работает вроде.Но проверка рабочего времени не работает

[beginer_38 (Programmer in future)]

Ура!!! Я понял в чем дело! Вот окончательный рабочий вариант:

create trigger tg_booking_taboo on bookings
after insert
as
begin 
declare @duration_sum int
declare @end_time time
declare @start_time time
declare @client int
declare @barber int
declare @week_day int
select @client=client_id, @barber=barber_id, @week_day=week_day_id
from inserted
set @duration_sum=(select sum(datediff(minute,'00:00:00',s.duration)) 
from bookings b join booking_service bs on bs.booking_id=b.id
join [services] s on s.id=bs.service_id where b.client_id=@client)
set @end_time=(select end_time from schedule where barber_id=@barber 
and week_day_id=@week_day)
set @start_time=(select start_time from schedule where barber_id=@barber
and week_day_id=@week_day)
if exists(select*from
bookings b join inserted i on b.barber_id=i.barber_id and b.week_day_id=i.week_day_id
join booking_service bs
on bs.booking_id=b.id join [services] s on s.id=bs.service_id 
where (b.start_time<(dateadd(minute,@duration_sum,i.start_time)) 
and (dateadd(minute,@duration_sum,b.start_time))>i.start_time))
begin
rollback tran
print('This time is busy')
end
else if exists(select*from inserted i join bookings b 
on i.barber_id=b.barber_id and i.client_id=b.client_id
where i.start_time<@start_time or 
(dateadd(minute,@duration_sum,i.start_time)>=@end_time))
begin
rollback tran
print('The working day has not started or has ended')
end
end

Вот тут у меня было "and’’.

where i.start_time<@start_time **or** 
(dateadd(minute,@duration_sum,i.start_time)>=@end_time))

И понятное дело оно не работало. Спасибо, что не послали меня подальше и привели меня к правильному решению)

[beginer_38 (Programmer in future)]

@beginer_38

Показать свободные временные слоты на неделю конкрет-
ного барбера. Информация о барбере и дне передаётся
в качестве параметра

А теперь не пойму, как это вот сделать. С какой стороны вообще подойти к этому? Как вообще эти слоты показать? Цикл тут нужен наверное? Но как, все равно не понимаю