· 6 years ago · May 22, 2019, 09:08 PM
1use dbeksamen
2drop table if exists timeregistration
3drop table if exists employeeprojects
4drop table if exists employee
5drop table if exists boss
6drop table if exists project
7drop table if exists customer
8
9create table customer
10(
11id int identity primary key,
12customername varchar(50)
13)
14create table project
15(
16id int identity primary key,
17projectname varchar(50),
18projectstatus char(9) check(projectstatus in ('active','inactive')),
19customerid int not null references customer,
20fixedprice int,
21estimatedhours int,
22hourlyrate int
23)
24create table boss
25(
26id int identity primary key,
27name varchar(50)
28)
29create table employee
30(
31id int identity primary key,
32employeename varchar(50),
33bossid int not null references boss,
34timeoff int
35)
36create table employeeprojects
37(
38id int identity primary key,
39employeeid int not null references employee,
40projectid int not null references project
41)
42create table timeregistration
43(
44id int identity primary key,
45starttime datetime,
46endtime datetime,
47employeeid int not null references employee,
48projectid int not null references project,
49note varchar(500)
50)
51go
52insert into customer values ('microsoft'), ('apple')
53
54insert into project values ('projectOne', 'active', 1, 5000, 10, null),
55('projectTwo', 'inactive', 1, null, null, 500),
56('projectThree', 'active', 2, 15000, 30, null),
57('projectFour', 'active', 2, 6000, 11, null)
58
59insert into boss values ('jackson'), ('peterson')
60
61insert into employee values ('Ravn', 1, 2), ('Frost', 2, 5), ('Dennis', 1, 3)
62
63insert into employeeprojects values (1, 1), (1,2), (2,3), (3,4)
64
65insert into timeregistration values ((convert(datetime,'19-05-19 06:00:00 PM',5)), (convert(datetime,'19-05-19 08:00:00 PM',5)), 1, 1, '2 hours on projectOne'),
66((convert(datetime,'13-05-19 09:00:00 PM',5)), (convert(datetime,'13-05-19 11:00:00 PM',5)), 1, 2, '2 hours on projectTwo'),
67((convert(datetime,'13-05-19 09:00:00 PM',5)), (convert(datetime,'13-05-19 11:00:00 PM',5)), 2, 3, '2 hours on projectThree'),
68((convert(datetime,'13-05-19 09:00:00 PM',5)), (convert(datetime,'13-05-19 11:00:00 PM',5)), 3, 4, '2 hours on projectFour')
69
70-- Ex2) ---------------------------------------------------
71go
72create index Employeetbl_timeoff
73on employee (timeoff asc)
74
75go
76select * from employee with(index(Employeetbl_timeoff))
77
78create index Projecttbl_fixedprice
79on project (fixedprice asc)
80
81go
82select * from project with(index(Projecttbl_fixedprice))
83
84-- Ex3) ---------------------------------------------------
85go
86select e.employeename
87from employee e
88join timeregistration t on e.id = t.employeeid
89join project p on t.projectid = p.id
90join customer c on c.id = p.customerid
91where c.customername = 'apple'
92
93--Ex4) -----------------------------------------------------
94go
95create or alter trigger timeregistrationTrigger
96on timeregistration
97instead of insert
98as
99begin
100 declare @daysbefore int
101 declare @employeeonproject int
102 declare @projectstatus char(9)
103
104 select @daysbefore = DATEDIFF(day, GETDATE(), (select starttime from inserted))
105 if(@daysbefore <= -7)
106 begin
107 RAISERROR('Noget gik galt', 16, 1)
108 return
109 end
110
111 select @employeeonproject = ep.employeeid
112 from employeeprojects ep
113 where ep.employeeid = (select employeeid from inserted) and ep.projectid = (select projectid from inserted)
114 if(@employeeonproject IS NULL)
115 begin
116 RAISERROR('Noget gik galt', 16, 1)
117 return
118 end
119
120 select @projectstatus = p.projectstatus
121 from project p
122 where p.id = (select projectid from inserted)
123 if(@projectstatus = 'inactive')
124 begin
125 RAISERROR('Noget gik galt', 16, 1)
126 return
127 end
128
129 insert into timeregistration values (
130 (select starttime from inserted),
131 (select endtime from inserted),
132 (select employeeid from inserted),
133 (select projectid from inserted),
134 (select note from inserted))
135end
136
137--test til krav1)
138insert into timeregistration values ((convert(datetime,'14-05-19 09:00:00 PM',5)), (convert(datetime,'13-05-19 11:00:00 PM',5)), 1, 1, '2 hours on projectOne')
139--test til krav 2)
140insert into timeregistration values ((convert(datetime,'20-05-19 09:00:00 PM',5)), (convert(datetime,'13-05-19 11:00:00 PM',5)), 2, 1, '2 hours on projectOne')
141--test til krav 3)
142insert into timeregistration values ((convert(datetime,'20-05-19 09:00:00 PM',5)), (convert(datetime,'13-05-19 11:00:00 PM',5)), 1, 2, '2 hours on projectOne')
143
144--Ex5) ------------------------------------------------------------------
145go
146create or alter function dbo.antalhverdageiMinutter(@aar int ,@maaned int)
147-- returnerer antal minutter i hverdage i den pågældende måned
148returns int
149as
150begin
151declare @res int
152declare @firstdayofmonth date = datefromparts(@aar,@maaned,1)
153declare @firstdayofnextmonth date = dateadd(mm,1,@firstdayofmonth)
154declare @lastdateofmonth date = dateadd(dd,-1,@firstdayofnextmonth)
155if day(@lastdateofmonth) = 28
156 set @res = 20
157else
158 if day(@lastdateofmonth) = 29
159 if datename(DW,@firstdayofmonth) in ('Saturday','Sunday')
160 set @res = 20
161 else
162 set @res = 21
163 else
164 if day(@lastdateofmonth) = 30
165 if datename(DW,@firstdayofmonth) = 'Saturday'
166 set @res = 20
167 else
168 if datename(DW,@firstdayofmonth) in ('Friday','Sunday')
169 set @res = 21
170 else set @res = 22
171 else
172 if day(@lastdateofmonth) = 31
173 if datename(DW,@firstdayofmonth) in ('Friday','Saturday')
174 set @res = 21
175 else
176 if datename(DW,@firstdayofmonth) in ('Thursday','Sunday')
177 set @res = 22
178 else set @res = 23
179return 444*@res
180end
181
182go
183create or alter proc updatetimeoff
184 @monthParameter int,
185 @yearParameter int
186as
187begin
188 declare @employeeid int
189 declare @timeoff int
190 declare @starttime datetime
191 declare @endtime datetime
192 declare @lastid int = 1
193 declare @minutes int = 0
194 declare @finalminutes int = 0
195 declare @numberOfRows int = 0
196
197 declare MY_CURSOR cursor
198 local static forward_only
199 for
200 select e.id, e.timeoff, t.starttime, t.endtime
201 from employee e
202 join timeregistration t on t.employeeid = e.id
203
204 open MY_CURSOR
205 fetch next from MY_CURSOR into @employeeid, @timeoff, @starttime, @endtime
206 while (@@FETCH_STATUS = 0)
207 begin
208 if MONTH(@starttime) = @monthParameter and YEAR(@starttime) = @yearParameter
209 begin
210 if @lastid = @employeeid
211 begin
212 if DATENAME(DW, @starttime) = 'Sunday'
213 begin
214 select @minutes += DATEDIFF(MINUTE, @starttime, @endtime)
215 end
216 else
217 begin
218 select @minutes += (DATEDIFF(MINUTE, @starttime, @endtime)) * 2
219 end
220 end
221 else
222 begin
223 update employee
224 set timeoff += (@minutes - dbo.antalhverdageiMinutter(@yearParameter, @monthParameter))
225 where id = @lastid
226 set @lastid += 1
227 set @minutes = 0
228 set @finalminutes = 0
229 if DATENAME(DW, @starttime) = 'Sunday'
230 begin
231 select @minutes += DATEDIFF(MINUTE, @starttime, @endtime)
232 end
233 else
234 begin
235 select @minutes += (DATEDIFF(MINUTE, @starttime, @endtime)) * 2
236 end
237 end
238 end
239 fetch next from MY_CURSOR into @employeeid, @timeoff, @starttime, @endtime
240 end
241
242 close MY_CURSOR
243end
244
245exec updatetimeoff 5, 2019
246
247select * from employee
248
249-- Ex8) ---------------------------------
250go
251exec sp_addlogin 'admin','123'
252exec sp_addlogin 'employee','123'
253
254exec sp_grantdbaccess 'admin'
255exec sp_grantdbaccess 'employee'
256
257exec sp_addrole 'administrators'
258exec sp_addrole 'employees'
259
260exec sp_addrolemember 'administrators','admin'
261exec sp_addrolemember 'employees','employee'
262
263grant select, insert, update, delete
264on database::dbeksamen
265to [admin]
266
267grant select, insert, update, delete
268on object::dbeksamen.dbo.timeregistration
269to [employee]