dbzMXqx0

1use Lab6DB;
2go
3
4set language Russian;  
5
6if object_id('dbo.Holidays', 'U') is not null
7  drop table dbo.holidays; 
8
9if object_id('dbo.SomeServices', 'U') is not null 
10  drop table dbo.SomeServices; 
11    
12create table Holidays(
13	id bigint identity(1,1) not null primary key,
14	name nvarchar(200) unique not null,
15	holi_date date);
16go
17
18create table SomeServices(
19	id bigint identity(1,1) not null primary key,
20	service_datime datetime2 not null,
21	low_rate bit);
22go
23
24--Procedures
25
26
27--getAllServices
28if exists (select * from sys.objects where type = 'P' and name = 'getAllServices')
29	drop procedure getAllServices
30go
31
32create procedure getAllServices
33	as select * from SomeServices;
34go
35
36--getAllHolidays
37if exists (select * from sys.objects where type = 'P' and name = 'getAllHolidays')
38	drop procedure getAllHolidays
39go
40
41create procedure getAllHolidays
42	as select * from Holidays
43
44--addHoliday
45if exists (select * from sys.objects where type = 'P' and name = 'addHoliday')
46	drop procedure addHoliday
47go
48
49create procedure addHoliday @name nvarchar(200), @holi_date date
50	as insert into Holidays values(@name, @holi_date)
51go
52
53--deleteSevice
54if exists (select * from sys.objects where type = 'P' and name = 'deleteSevice')
55	drop procedure deleteSevice
56go
57
58create procedure deleteSevice @id bigint
59	as delete from SomeServices where id = @id
60go
61
62--addService
63if exists (select * from sys.objects where type = 'P' and name = 'addService')
64	drop procedure addService
65go
66
67create procedure addService @datime datetime2
68	as begin
69		declare @low_rate bit;
70		if (datename(weekday, @datime) in (N'Суббота', N'Воскресенье')
71			or cast(@datime as date) in (select holi_date from Holidays)
72			or datepart(hour, @datime) = 23 or datepart(hour, @datime) < 6)
73			set @low_rate = 0
74		else set @low_rate = 1;
75		insert into SomeServices values(@datime, @low_rate)
76	end
77go
78
79--Adding holidays
80
81exec addHoliday	'Новый год', '2018-01-01';
82exec addHoliday 'Рождество Христово', '2018-01-07';
83exec addHoliday 'Международный женский день', '2018-03-08';
84exec addHoliday 'Пасха', '2018-04-07';
85exec addHoliday 'День труда', '2018-05-01';
86exec addHoliday 'День победы', '2018-05-09';
87exec addHoliday 'Троица', '2018-05-26';
88exec addHoliday 'День Конституции Украины', '2018-06-28';
89exec addHoliday 'День независимости Украины', '2018-08-24';
90exec addHoliday 'День защитника Украины', '2018-10-14';
91exec addHoliday 'Рождество Христово (кат.)', '2018-12-25';
92go
93
94--Actual action
95
96exec getAllHolidays;
97
98exec getAllServices;
99
100--low_rate = 1
101exec addService '2018-12-21T19:58:47.1234567';
102exec getAllServices;
103
104--low_rate = 0, weekend
105exec addService '2018-12-22T19:58:47.1234567';
106exec getAllServices;
107
108--low_rate = 0, holiday
109exec addService '2018-12-25T19:58:47.1234567';
110exec getAllServices;
111
112--low_rate = 0, nighttime
113exec addService '2018-12-21T02:58:47.1234567';
114exec getAllServices;