awGjpKkT

1go
2/*“As a staff member, I should be able to ...”*/
3/*1 Check-in once I arrive each day.*/
4create proc checkin
5@staffmember varchar(50)
6as
7if exists (Select * from Staff_Members m Inner Join Attendance_Records r
8on r.staff = m.username
9where m.username = @staffmember and datename(dw,Getdate())!= m.day_off and datename(dw,Getdate())!= 'Friday')
10Insert Into Attendance_Records values (current_timestamp, @staffmember, current_timestamp, null)
11else
12print 'You cannot check-in on your day-off'
13
14 go
15
16/*2 Check-out before I leave each day.*/
17create proc checkout
18@staffmember varchar(50)
19as
20if exists (Select * from Staff_Members m Inner Join Attendance_Records r
21on r.staff = m.username
22where m.username = @staffmember and datename(dw,Getdate())!= m.day_off and datename(dw,Getdate())!= 'Friday')
23Update Attendance_Records
24set end_time = current_timestamp
25where date = current_timestamp and @staffmember = staff
26else
27print 'You cannot check-in on your day-off'
28
29go
30
31/*3 View all my attendance records (check-in time, check-out time, duration, missing hours) within a
32certain period of time*/
33create proc viewAttendance
34@username varchar(50), @start datetime, @end datetime
35as
36select a.*,DATEDIFF(hh, a.start_time,a.end_time) as 'duration', j.working_hours-DATEDIFF(hh, a.start_time,a.end_time) as 'missing hours' from (Attendance_Records a inner join Staff_Members s  on s.username=a.staff) inner join Jobs j on s.job=j.title and s.department=j.department and s.company=j.company where s.username=@username and a.date >= @start and a.date <= @end
37
38go
39/*5 View the status of all requests I applied for before (HR employee and manager responses)*/
40create proc viewReqStatus
41@staffmember varchar(50)
42as
43Select * from Requests r 
44where r.applicant = @staffmember and manager_response = 'pending' and hr_response = 'pending'
45
46
47
48go
49/*6 Delete any request I applied for as long as it is still in the review process*/
50create proc Deletereq
51@staffmember varchar(50),
52@start_date datetime
53as
54Delete from Requests
55where applicant = @staffmember and start_date = @start_date
56
57
58
59go
60
61/*7 Send emails to staff members in my company*/
62CREATE PROC Send_Emails
63@sender VARCHAR(50),
64@recipient VARCHAR(50),
65@subject VARCHAR(50),
66@body VARCHAR(50)
67AS
68DECLARE @DATE DATETIME SET @DATE = CURRENT_TIMESTAMP
69INSERT INTO Emails(subject,DATE,body)
70VALUES (@subject,@DATE,@body)
71 
72DECLARE @KEY INT
73SELECT @KEY=serial_number FROM Emails WHERE subject = @subject AND DATE = @DATE AND body = @body
74 
75INSERT INTO Staff_send_Email_to_Staff(email_number,recipient,sender)
76VALUES (@KEY,@recipient,@sender)
77 
78
79go
80
81/*8 View emails sent to me by other staff members of my company*/
82create proc viewEmails
83@staffmember varchar(50)
84as
85Select e.* from Emails e Inner Join Staff_send_Email_to_Staff s 
86on e.serial_number = s.email_number
87Inner Join Staff_Members m1 
88on s.sender = m1.username
89Inner Join Staff_Members m2 
90on s.recipient = m2.username
91Inner Join Company c
92on m1.company = m2.company
93
94go
95/*9 Reply to an email sent to me, while the reply would be saved in the database as a new email record.*/
96CREATE PROC Send_Emails
97@sender VARCHAR(50),
98@recipient VARCHAR(50),
99@subject VARCHAR(50),
100@body VARCHAR(50)
101AS
102DECLARE @DATE DATETIME SET @DATE = CURRENT_TIMESTAMP
103INSERT INTO Emails(subject,DATE,body)
104VALUES (@subject,@DATE,@body)
105 
106DECLARE @KEY INT
107SELECT @KEY=serial_number FROM Emails WHERE subject = @subject AND DATE = @DATE AND body = @body
108 
109INSERT INTO Staff_send_Email_to_Staff(email_number,recipient,sender)
110VALUES (@KEY,@sender,@recipient)
111
112go
113/*10 View announcements related to my company within the past 20 days*/
114create proc view_annoucements
115@username varchar(50)
116as
117declare @company varchar(50)
118select @company=s.company from Staff_Members s inner join Staff_Members h on h.username=s.username where h.username=@username
119select a.* from Announcements a inner join Staff_Members s on a.hr_employee = s.username where s.company=@company
120
121go
122/*4  =Apply for requests of both types: leave requests or business trip requests, by supplying all the
123needed information for the request. As a staff member, I can not apply for a leave if I exceeded the
124number of annual leaves allowed. If I am a manager applying for a request, the request does not
125need to be approved, but it only needs to be kept track of. Also, I can not apply for a request when
126it’s applied period overlaps with another request*/
127
128create proc apply_request_leave
129@username varchar(50),
130@start datetime,
131@end datetime,
132@type varchar(50)
133as
134declare @dayoff varchar(50)
135select @dayoff = staff_members.day_off from staff_members where username=@username
136set datefirst 1
137create table temp(
138date_name varchar(50),
139count int
140)
141/*declare @start_date datetime,@end_date datetime
142set @start_date=CURRENT_TIMESTAMP
143set @end_date=CURRENT_TIMESTAMP*/
144;WITH Days_Of_The_Week AS (
145    SELECT 1 AS day_number, 'Monday' AS day_name UNION ALL
146    SELECT 2 AS day_number, 'Tuesday' AS day_name UNION ALL
147    SELECT 3 AS day_number, 'Wednesday' AS day_name UNION ALL
148    SELECT 4 AS day_number, 'Thursday' AS day_name UNION ALL
149    SELECT 5 AS day_number, 'Friday' AS day_name UNION ALL
150    SELECT 6 AS day_number, 'Saturday' AS day_name UNION ALL
151    SELECT 7 AS day_number, 'Sunday' AS day_name
152)
153insert into temp
154SELECT 
155    day_name,
156    (1 + DATEDIFF(wk, @start, @end) -
157        CASE WHEN DATEPART(weekday, @start) > day_number THEN 1 ELSE 0 END -
158        CASE WHEN DATEPART(weekday, @end)   < day_number THEN 1 ELSE 0 END) as 'count'
159FROM
160    Days_Of_The_Week
161declare @deductible int
162select @deductible=sum(count)
163from temp
164group by date_name
165having date_name='Friday' or day_name=@dayoff
166drop table temp
167declare @leaves_after_request int
168select @leaves_after_request = annual_leaves-@deductible from staff_members where username=@username
169if @leaves_after_request<0
170print 'Not enough leaves left'
171else
172begin
173if exists(select * from Requests where applicant=@username and @start>=start_date and @end<=end_date)
174print 'Leave period overlaps with another request'
175else
176begin
177if @username not in (select username from Managers)
178insert into Requests(start_date,applicant,end_date,request_date) values(@start,@username,@end,CURRENT_TIMESTAMP)
179else
180begin
181insert into Requests values(@start,@username,@end,CURRENT_TIMESTAMP,null,'accepted',null,'accepted',null)
182update Staff_Members set annual_leaves=@leaves_after_request where username=@username
183end
184insert into Leave_Requests values(@start,@username,@type)
185end
186end
187
188go
189
190create proc apply_request_business
191@username varchar(50),
192@start datetime,
193@end datetime,
194@destination varchar(50),
195@purpose varchar(50)
196as
197
198
199if exists(select * from Requests where applicant=@username and @start>=start_date and @end<=end_date)
200print 'Leave period overlaps with another request'
201else
202begin
203if @username not in (select username from Managers)
204insert into Requests(start_date,applicant,end_date,request_date) values(@start,@username,@end,CURRENT_TIMESTAMP)
205else
206begin
207insert into Requests values(@start,@username,@end,CURRENT_TIMESTAMP,null,'accepted',null,'accepted',null)
208end
209insert into Business_Trip_Requests values(@start,@username,@destination, @purpose)
210end