Rspqq9Ew

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 TOP 1 @key=[serial_number] FROM Emails
74WHERE subject = @subject AND date = @date AND body = @body
75
76INSERT INTO Staff_send_Email_to_Staff(email_number,recipient,sender)
77VALUES (@key,@recipient,@sender)
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 reply
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 TOP 1 @key=[serial_number] FROM Emails
108WHERE subject = @subject AND date = @date AND body = @body
109
110INSERT INTO Staff_send_Email_to_Staff(email_number,recipient,sender)
111VALUES (@key,@sender,@recipient)
112
113go
114/*10 View announcements related to my company within the past 20 days*/
115create proc view_annoucements
116@username varchar(50)
117as
118declare @company varchar(50)
119select @company=s.company from Staff_Members s inner join Staff_Members h on h.username=s.username where h.username=@username
120select a.* from Announcements a inner join Staff_Members s on a.hr_employee = s.username where s.company=@company
121
122go
123/*4  =Apply for requests of both types: leave requests or business trip requests, by supplying all the
124needed information for the request. As a staff member, I can not apply for a leave if I exceeded the
125number of annual leaves allowed. If I am a manager applying for a request, the request does not
126need to be approved, but it only needs to be kept track of. Also, I can not apply for a request when
127it’s applied period overlaps with another request*/
128
129create proc apply_request_leave
130@username varchar(50),
131@start datetime,
132@end datetime,
133@type varchar(50)
134as
135declare @dayoff varchar(50)
136select @dayoff = staff_members.day_off from staff_members where username=@username
137set datefirst 1
138create table temp(
139date_name varchar(50),
140count int
141)
142/*declare @start_date datetime,@end_date datetime
143set @start_date=CURRENT_TIMESTAMP
144set @end_date=CURRENT_TIMESTAMP*/
145;WITH Days_Of_The_Week AS (
146    SELECT 1 AS day_number, 'Monday' AS day_name UNION ALL
147    SELECT 2 AS day_number, 'Tuesday' AS day_name UNION ALL
148    SELECT 3 AS day_number, 'Wednesday' AS day_name UNION ALL
149    SELECT 4 AS day_number, 'Thursday' AS day_name UNION ALL
150    SELECT 5 AS day_number, 'Friday' AS day_name UNION ALL
151    SELECT 6 AS day_number, 'Saturday' AS day_name UNION ALL
152    SELECT 7 AS day_number, 'Sunday' AS day_name
153)
154insert into temp
155SELECT 
156    day_name,
157    (1 + DATEDIFF(wk, @start, @end) -
158        CASE WHEN DATEPART(weekday, @start) > day_number THEN 1 ELSE 0 END -
159        CASE WHEN DATEPART(weekday, @end)   < day_number THEN 1 ELSE 0 END) as 'count'
160FROM
161    Days_Of_The_Week
162declare @deductible int
163select @deductible=sum(count)
164from temp
165group by date_name
166having date_name='Friday' or day_name=@dayoff
167drop table temp
168declare @leaves_after_request int
169select @leaves_after_request = annual_leaves-@deductible from staff_members where username=@username
170if @leaves_after_request<0
171print 'Not enough leaves left'
172else
173begin
174if exists(select * from Requests where applicant=@username and @start>=start_date and @end<=end_date)
175print 'Leave period overlaps with another request'
176else
177begin
178if @username not in (select username from Managers)
179insert into Requests(start_date,applicant,end_date,request_date) values(@start,@username,@end,CURRENT_TIMESTAMP)
180else
181begin
182insert into Requests values(@start,@username,@end,CURRENT_TIMESTAMP,null,'accepted',null,'accepted',null)
183update Staff_Members set annual_leaves=@leaves_after_request where username=@username
184end
185insert into Leave_Requests values(@start,@username,@type)
186end
187end
188
189go
190
191create proc apply_request_business
192@username varchar(50),
193@start datetime,
194@end datetime,
195@destination varchar(50),
196@purpose varchar(50)
197as
198
199
200if exists(select * from Requests where applicant=@username and @start>=start_date and @end<=end_date)
201print 'Leave period overlaps with another request'
202else
203begin
204if @username not in (select username from Managers)
205insert into Requests(start_date,applicant,end_date,request_date) values(@start,@username,@end,CURRENT_TIMESTAMP)
206else
207begin
208insert into Requests values(@start,@username,@end,CURRENT_TIMESTAMP,null,'accepted',null,'accepted',null)
209end
210insert into Business_Trip_Requests values(@start,@username,@destination, @purpose)
211end