· 8 years ago · Apr 17, 2018, 02:54 AM
1USE Social_Network
2
3--------- Joining the Network (Register) ----------------------
4
5CREATE PROC NewMember
6@firstName VARCHAR(30),
7@lastName VARCHAR(30),
8@memberAddress VARCHAR(30),
9@email VARCHAR(30),
10@gender VARCHAR(30)
11AS
12BEGIN
13
14INSERT INTO Members (first_name,last_name,member_address,[email],[gender])
15VALUES (@firstName,@lastName,@memberAddress,@email,@gender)
16
17
18
19END
20
21EXEC NewMember 'menna', 'salah', 'new cairo', 'menna.salah@student.guc.edu.eg', 'Female'
22
23--------- Procedures handles sorting members to either Academics or Students by email --------
24CREATE PROC SortMembers
25@member_id INTEGER,
26@email VARCHAR(30)
27AS
28BEGIN
29
30IF @email LIKE '%student%'
31 BEGIN
32 INSERT INTO Students(student_id)
33 VALUES (@member_id)
34 END
35ELSE
36 BEGIN
37 INSERT INTO Academics (academic_id)
38 VALUES (@member_id)
39 END
40
41END
42
43EXEC SortMembers 1,'ebtehal.abed@student.guc.edu.eg'
44
45
46CREATE PROC UpdateAcademics
47@academicId INTEGER,
48@depart VARCHAR(30),
49@jobTitle VARCHAR(30),
50@isLec BIT
51AS
52BEGIN
53
54UPDATE Academics
55SET department = @depart, job_title = @jobTitle, is_lec = @isLec
56WHERE academic_id = @academicId
57
58END
59
60CREATE PROC UpdateStudents
61@studentId INTEGER,
62@majoR VARCHAR(30)
63AS
64BEGIN
65
66UPDATE Students
67SET major = @majoR
68WHERE student_id = @studentId
69
70END
71
72EXEC UpdateStudents 1,'cs'
73
74---------------------- Friend requests -----------------------
75
76CREATE PROC SendRequests
77@senderId INTEGER,
78@recipientId INTEGER
79AS
80BEGIN
81
82INSERT INTO Members_sendFriendRequestTo_Members (sender_id,recipient_id)
83VALUES (@senderId, @recipientId)
84
85END
86
87EXEC SendRequests 1,3
88
89
90CREATE PROC RespondToRequests
91@senderId INTEGER,
92@recipientID INTEGER,
93@respond BIT
94AS
95BEGIN
96
97UPDATE Members_sendFriendRequestTo_Members
98SET is_Approve = @respond, is_Pending = 'false'
99WHERE sender_id = @senderId AND recipient_id = @recipientID
100
101END
102
103EXEC RespondToRequests 1,3,'true'
104
105
106CREATE PROC ViewPendingRequests
107@recipientID INTEGER
108AS
109BEGIN
110
111SELECT first_name, last_name
112FROM Members
113LEFT JOIN Members_sendFriendRequestTo_Members ON Members.member_id = Members_sendFriendRequestTo_Members.sender_id
114WHERE Members.member_id = Members_sendFriendRequestTo_Members.sender_id AND Members_sendFriendRequestTo_Members.is_Pending = 'True'
115 AND Members_sendFriendRequestTo_Members.recipient_id = @recipientID
116
117END
118
119EXEC ViewPendingRequests 4
120
121--------------------- Search for members ---------------------
122CREATE PROC SearchByName
123@name VARCHAR (100)
124AS
125BEGIN
126
127SELECT *
128FROM Members
129WHERE first_name LIKE '%' + @name + '%' OR last_name LIKE '%' + @name + '%'
130
131
132END
133
134EXEC SearchByName 'Moh'
135
136CREATE PROC SearchByEmail
137@email VARCHAR(100)
138AS
139BEGIN
140
141SELECT *
142FROM Members
143Where email = @email
144
145END
146
147EXEC SearchByEmail 'ebtehal.abed@guc.edu.eg'
148
149------------------ Lecturer add course pages ---------------
150
151CREATE PROC AddCourse
152@name VARCHAR(100),
153@creatorID INTEGER
154AS
155BEGIN
156
157DECLARE @isLec BIT
158SELECT @isLec = is_Lec
159FROM Academics
160WHERE academic_id = @creatorID
161
162IF (@isLec = 1)
163 BEGIN
164 INSERT INTO Courses (name,[creator_id])
165 VALUES (@name,@creatorID)
166 END
167ELSE
168PRINT 'Sorry ! Academic is not allowed to add a course page'
169
170END
171
172EXEC AddCourse 'Databases', 1
173
174----------------- Lecturer assign academics on course pages -----------------
175
176CREATE PROC LecAssignAcademics
177@lecturerID INTEGER,
178@academicID INTEGER,
179@courseID INTEGER
180AS
181Begin
182
183DECLARE @isLec BIT
184SELECT @isLec = is_Lec
185FROM Academics
186WHERE academic_id = @lecturerID
187
188IF (@isLec = 1)
189 BEGIN
190 INSERT INTO Academics_assign_Academics_on_Courses (lecturer_id, academic_id, course_id)
191 VALUES (@lecturerID,@academicID,@courseID)
192 END
193ELSE
194PRINT ' Sorry ! You are not allowed to use this option '
195
196END
197
198EXEC LecAssignAcademics 1,1,2
199
200--------------------- Students subscribe to courses -----------------
201
202CREATE PROC SubscribeCourse
203@studentID INTEGER,
204@courseID INTEGER
205AS
206BEGIN
207
208INSERT INTO Courses_subscribedBy_Students (student_id,course_id)
209VALUES (@studentID,@courseID)
210
211END
212
213EXEC SubscribeCourse 2,1
214
215------------------ Academics posts Polls ----------------------
216
217CREATE PROC PostPolls
218@courseID INTEGER,
219@creatorID INTEGER,
220@name VARCHAR(100),
221@ans1 VARCHAR(255),
222@ans2 VARCHAR(255),
223@ans3 VARCHAR(255),
224@ans4 VARCHAR(255),
225@ans5 VARCHAR(255)
226AS
227BEGIN
228
229DECLARE @assignedAcademic INTEGER
230SELECT @assignedAcademic = academic_id
231FROM Academics_assign_Academics_on_Courses
232WHERE course_id = @courseID
233
234IF (@assignedAcademic = @creatorID)
235 BEGIN
236 INSERT INTO Polls (course_id,creator_id,[name],[ans1],[ans2],[ans3],[ans4],[ans5])
237 VALUES (@courseID,@creatorID,@name,@ans1,@ans2,@ans3,@ans4,@ans5)
238 END
239ELSE
240PRINT 'Sorry! You are not assigned to this course'
241
242END
243
244EXEC PostPolls 2,1,'Compensation Lecture','Yes','No', NULL,NULL,NULL
245
246DROP PROC PostPolls
247----------------- Students Answer Polls --------------------------------
248CREATE PROC AnswerPoll
249@studentID INTEGER,
250@pollID INTEGER,
251@courseID INTEGER,
252@choice VARCHAR(100)
253AS
254BEGIN
255
256DECLARE @poll_in_courseID INTEGER
257SELECT @poll_in_courseID = course_id
258FROM Polls
259WHERE poll_id = @pollID
260
261
262IF (@poll_in_courseID = @courseID)---------- checks if the given poll exists in the given course
263 BEGIN
264 DECLARE @student INTEGER
265 SELECT @student = student_id
266 FROM Courses_subscribedBy_Students
267 WHERE course_id = @courseID AND student_id = @studentID
268
269 IF (@student = @studentID) ------------ checks if the given student exists in the given course
270 BEGIN
271
272 DECLARE @ch1 VARCHAR(100)
273 DECLARE @ch2 VARCHAR(100)
274 DECLARE @ch3 VARCHAR(100)
275 DECLARE @ch4 VARCHAR(100)
276 DECLARE @ch5 VARCHAR(100)
277
278 EXEC checkChoices 1,@ch1 OUTPUT,@ch2 OUTPUT,@ch3 OUTPUT,@ch4 OUTPUT,@ch5 OUTPUT
279
280 IF (@choice = @ch1 OR @choice = @ch2 OR @choice = @ch3 OR @choice = @ch4 OR @choice = @ch5)
281 BEGIN
282
283 INSERT INTO Students_answer_Poll (student_id,poll_id,course_id,[choice])
284 VALUES (@studentID,@pollID,@courseID,@choice)
285
286 END
287 ELSE
288 PRINT 'Choice does not match the given choices'
289 END
290 ELSE
291 PRINT 'You are not subscribed in the given course'
292 END
293ELSE
294PRINT 'Poll does not exist in the given course'
295END
296
297EXEC AnswerPoll 4,1,2,'No'
298
299
300CREATE PROC checkChoices
301@poll_ID INTEGER,
302@choice1 VARCHAR(100)OUTPUT,
303@choice2 VARCHAR(100)OUTPUT,
304@choice3 VARCHAR(100)OUTPUT,
305@choice4 VARCHAR(100)OUTPUT,
306@choice5 VARCHAR(100)OUTPUT
307AS
308BEGIN
309
310SELECT @choice1 = ans1,@choice2 = ans2,@choice3= ans3,@choice4= ans4,@choice5=ans5
311FROM Polls
312WHERE poll_id = @poll_ID
313
314END
315
316----------------------- View poll results ---------------
317
318CREATE PROC PollResults
319@pollID INTEGER,
320@result1 FLOAT OUTPUT,
321@result2 FLOAT OUTPUT,
322@result3 FLOAT OUTPUT,
323@result4 FLOAT OUTPUT,
324@result5 FLOAT OUTPUT
325AS
326BEGIN
327
328DECLARE @totalResponds INTEGER
329SELECT @totalResponds = COUNT (choice)
330FROM Students_answer_Poll
331WHERE poll_id = @pollID
332
333DECLARE @ch1 INTEGER
334DECLARE @ch2 INTEGER
335DECLARE @ch3 INTEGER
336DECLARE @ch4 INTEGER
337DECLARE @ch5 INTEGER
338
339EXEC countAnswers @pollID, @ch1 OUTPUT,@ch2 OUTPUT, @ch3 OUTPUT, @ch4 OUTPUT, @ch5 OUTPUT
340SET @result1 = (@ch1*1.0 / @totalResponds*1.0)* 100.0
341SET @result2 = (@ch2*1.0 / @totalResponds*1.0)* 100.0
342SET @result3 = (@ch3*1.0 / @totalResponds*1.0)* 100.0
343SET @result4 = (@ch4*1.0 / @totalResponds*1.0)* 100.0
344SET @result5 = (@ch5*1.0 / @totalResponds*1.0)* 100.0
345
346END
347
348DECLARE @res1 FLOAT
349DECLARE @res2 FLOAT
350DECLARE @res3 FLOAT
351DECLARE @res4 FLOAT
352DECLARE @res5 FLOAT
353
354
355EXEC PollResults 1, @res1 OUTPUT, @res2 OUTPUT,@res3 OUTPUT,@res4 OUTPUT,@res5 OUTPUT
356
357PRINT @res1
358PRINT @res2
359DROP PROC PollResults
360---------------------------------------
361CREATE PROC countAnswers
362@pollID INTEGER,
363@choice1Count INTEGER OUTPUT,
364@choice2Count INTEGER OUTPUT,
365@choice3Count INTEGER OUTPUT,
366@choice4Count INTEGER OUTPUT,
367@choice5Count INTEGER OUTPUT
368AS
369BEGIN
370
371DECLARE @choice1 VARCHAR(100)
372SELECT @choice1 = ans1
373FROM Polls
374WHERE poll_id = @pollID
375
376
377SELECT @choice1Count = COUNT(choice)
378FROM Students_answer_Poll
379WHERE poll_id = @pollID AND choice = @choice1
380
381DECLARE @choice2 VARCHAR(100)
382SELECT @choice2 = ans2
383FROM Polls
384WHERE poll_id = @pollID
385
386SELECT @choice2Count = COUNT(choice)
387FROM Students_answer_Poll
388WHERE poll_id = @pollID AND choice = @choice2
389
390DECLARE @choice3 VARCHAR(100)
391SELECT @choice3 = ans3
392FROM Polls
393WHERE poll_id = @pollID
394
395SELECT @choice3Count = COUNT(choice)
396FROM Students_answer_Poll
397WHERE poll_id = @pollID AND choice = @choice3
398
399
400DECLARE @choice4 VARCHAR(100)
401SELECT @choice4 = ans4
402FROM Polls
403WHERE poll_id = @pollID
404
405SELECT @choice4Count = COUNT(choice)
406FROM Students_answer_Poll
407WHERE poll_id = @pollID AND choice = @choice4
408
409
410DECLARE @choice5 VARCHAR(100)
411SELECT @choice5 = ans5
412FROM Polls
413WHERE poll_id = @pollID
414
415SELECT @choice5Count = COUNT(choice)
416FROM Students_answer_Poll
417WHERE poll_id = @pollID AND choice = @choice5
418END
419
420 DECLARE @ch1 INTEGER
421 DECLARE @ch2 INTEGER
422 DECLARE @ch3 INTEGER
423 DECLARE @ch4 INTEGER
424 DECLARE @ch5 INTEGER
425
426EXEC countAnswers 1, @ch1 OUTPUT,@ch2 OUTPUT, @ch3 OUTPUT, @ch4 OUTPUT, @ch5 OUTPUT
427PRINT @ch1
428PRINT @ch2
429
430-------------- Members add posts ---------------
431
432CREATE PROC AddPosts
433@courseID INTEGER,
434@writerID INTEGER,
435@content VARCHAR (255)
436AS
437BEGIN
438
439DECLARE @assignedAcademic INTEGER
440SELECT @assignedAcademic = academic_id
441FROM Academics_assign_Academics_on_Courses
442WHERE course_id = @courseID
443
444DECLARE @student INTEGER
445SELECT @student = student_id
446FROM Courses_subscribedBy_Students
447WHERE course_id = @courseID AND student_id = @writerID
448
449
450IF (@assignedAcademic = @writerID OR @student = @writerID)
451 BEGIN
452 INSERT INTO Posts (course_id,writer_id,[content])
453 VALUES (@courseID,@writerID,@content)
454 END
455ELSE
456PRINT 'Sorry! You are not allowed to post in this course'
457
458END
459
460EXEC AddPosts 1,2,'Quiz 1 is postponed'
461-------------------------
462CREATE PROC RatePosts
463@memberID INTEGER,
464@postID INTEGER,
465@courseID INTEGER,
466@rate INTEGER
467AS
468BEGIN
469
470DECLARE @course INTEGER
471SELECT @course = course_id
472FROM Posts
473WHERE post_id = @postID
474
475IF (@course = @courseID)
476 BEGIN
477 DECLARE @assignedAcademic INTEGER
478 SELECT @assignedAcademic = academic_id
479 FROM Academics_assign_Academics_on_Courses
480 WHERE course_id = @courseID
481
482 DECLARE @student INTEGER
483 SELECT @student = student_id
484 FROM Courses_subscribedBy_Students
485 WHERE course_id = @courseID AND student_id = @memberID
486
487 IF (@assignedAcademic = @memberID OR @student = @memberID)
488 BEGIN
489 INSERT INTO Members_rate_Posts (member_id,post_id,course_id,[rate])
490 VALUES(@memberID,@postID,@courseID,@rate)
491 END
492 ELSE
493 PRINT 'Sorry! You are not allowed to rate this post'
494 END
495ELSE
496PRINT 'Sorry! The given post is not in the give course'
497
498END
499
500EXEC RatePosts 6,1,1,4
501-------------------------
502CREATE PROC AvgRate
503@postID INTEGER
504AS
505BEGIN
506
507DECLARE @ratesCount INTEGER
508SELECT @ratesCount = AVG(rate)
509FROM Members_rate_Posts
510WHERE post_id = @postID
511
512UPDATE Posts
513SET avg_rate = @ratesCount
514WHERE post_id = @postID
515
516PRINT @ratesCount
517END
518
519EXEC AvgRate 1
520
521---------- Members comment on posts --------------
522
523CREATE PROC LeaveComment
524@postID INTEGER,
525@courseID INTEGER,
526@writerID INTEGER,
527@content VARCHAR(255)
528AS
529BEGIN
530
531DECLARE @course INTEGER
532SELECT @course = course_id
533FROM Posts
534WHERE post_id = @postID
535
536IF (@course = @courseID)
537 BEGIN
538 DECLARE @assignedAcademic INTEGER
539 SELECT @assignedAcademic = academic_id
540 FROM Academics_assign_Academics_on_Courses
541 WHERE course_id = @courseID
542
543 DECLARE @student INTEGER
544 SELECT @student = student_id
545 FROM Courses_subscribedBy_Students
546 WHERE course_id = @courseID AND student_id = @writerID
547
548 IF (@assignedAcademic = @writerID OR @student = @writerID)
549 BEGIN
550 INSERT INTO Comments (post_id,course_id,writer_id,[content])
551 VALUES (@postID,@courseID,@writerID,@content)
552 END
553 ELSE
554 PRINT 'Sorry! You are not allowed to comment on this post'
555 END
556ELSE
557PRINT 'Sorry! The given post is not in the give course'
558
559END
560
561EXEC LeaveComment 1,1,2,'Thank you Prof'
562
563---------- Academic starts Project -------------
564
565CREATE PROC StartProject
566@courseID INTEGER,
567@creatorID INTEGER,
568@name VARCHAR(30),
569@startDate DATE,
570@expDate DATE
571AS
572BEGIN
573
574SET @startDate = GETDATE()
575
576DECLARE @assignedAcademic INTEGER
577SELECT @assignedAcademic = academic_id
578FROM Academics_assign_Academics_on_Courses
579WHERE course_id = @courseID AND academic_id = @creatorID
580
581PRINT @assignedAcademic
582IF (@assignedAcademic = @creatorID)
583 BEGIN
584 INSERT INTO Projects (course_id,creator_id,[name],exp_date,start_date)
585 VALUES (@courseID,@creatorID,@name,@expDate,@startDate)
586 END
587ELSE
588PRINT 'Sorry ! You are not allowed to announce a project in this course'
589
590END
591
592EXEC StartProject 1,1,'aykalam',NULL,'2011-11-30'
593
594CREATE PROC StartTeam
595@projectID INTEGER,
596@coursEID INTEGER,
597@name VARCHAR(30),
598@memberCreator INTEGER
599AS
600BEGIN
601
602DECLARE @projectInCourse INTEGER
603SELECT @projectInCourse = course_id
604FROM Projects
605WHERE project_id = @projectID AND course_id = @coursEID
606
607DECLARE @started DATE
608SELECT @started = start_date
609FROM Projects
610WHERE project_id = @projectID
611
612DECLARE @expired DATE
613SELECT @expired = exp_date
614FROM Projects
615WHERE project_id = @projectID
616
617DECLARE @student INTEGER
618SELECT @student = student_id
619FROM Courses_subscribedBy_Students
620WHERE course_id = @courseID AND student_id = @memberCreator
621
622IF (GETDATE() >= @started AND @started < @expired AND @student = @memberCreator AND @projectInCourse = @coursEID)
623 BEGIN
624 INSERT INTO Project_Teams (project_id,course_id,[name],member_creator)
625 VALUES (@projectID,@coursEID,@name,@memberCreator)
626 END
627ELSE
628PRINT 'Project expired or hasnt started yet'
629END
630
631EXEC StartTeam 1,1,'Voter222',2
632DROP PROC StartTeam
633
634
635CREATE PROC RequestToJoinTeam
636@teamID INTEGER,
637@studentID INTEGER,
638@projectID INTEGER,
639@courseID INTEGER
640AS
641BEGIN
642
643DECLARE @student INTEGER
644SELECT @student = student_id
645FROM Courses_subscribedBy_Students
646WHERE course_id = @courseID AND student_id = @studentID
647
648DECLARE @member1 INTEGER
649SELECT @member1 = member_id1
650FROM Project_Teams
651WHERE project_id = @projectID
652
653DECLARE @member2 INTEGER
654SELECT @member2 = member_id2
655FROM Project_Teams
656WHERE project_id = @projectID
657
658DECLARE @startDate DATE
659SELECT @startDate = start_date
660FROM Projects
661WHERE project_id = @projectID
662
663DECLARE @expDate DATE
664SELECT @expDate = exp_date
665FROM Projects
666WHERE project_id = @projectID
667
668IF (@student = @studentID)
669 BEGIN
670 IF (GETDATE() > @startDate AND GETDATE() <@expDate)
671 BEGIN
672 IF(@member1 is NULL OR @member2 is NULL)
673 BEGIN
674 INSERT INTO Students_requestToJoin_ProjectTeams (student_id,team_id,project_id,course_id)
675 VALUES (@studentID,@teamID,@projectID,@courseID)
676 END
677 ELSE
678 PRINT 'Places Full !'
679 END
680 END
681ELSE
682PRINT 'Sorry you are not allowed to join this team '
683
684END
685
686EXEC RequestToJoinTeam 2,4,1,1
687
688CREATE PROC ApproveRequest ---- still needs work
689@teamID INTEGER,
690@studentID INTEGER,
691@creatorID INTEGER
692AS
693BEGIN
694
695DECLARE @creator INTEGER
696SELECT @creator = member_creator
697FROM Project_Teams
698WHERE team_id = @teamID
699
700DECLARE @requested INTEGER
701SELECT @requested = student_id
702FROM Students_requestToJoin_ProjectTeams
703WHERE team_id = @teamID
704
705DECLARE @member1 INTEGER
706SELECT @member1 = member_id1
707FROM Project_Teams
708WHERE team_id = @teamID
709
710DECLARE @member2 INTEGER
711SELECT @member2 = member_id2
712FROM Project_Teams
713WHERE team_id = @teamID
714
715IF (@creator = @creatorID)
716 BEGIN
717 UPDATE Students_requestToJoin_ProjectTeams
718 SET is_Approve = 'True', is_Pending = 'False'
719 WHERE team_id = @teamID
720 IF (@member1 is NULL)
721 BEGIN
722 UPDATE Project_Teams
723 SET member_id1 = @studentID
724 WHERE team_id = @teamID
725 END
726 ELSE IF (@member2 is NULL)
727 BEGIN
728 UPDATE Project_Teams
729 SET member_id2 = @studentID
730 WHERE team_id = @teamID
731 END
732 END
733ELSE
734Print ' You are not allowed to approve requests'
735
736END
737
738EXEC ApproveRequest 2,2,2
739
740DROP PROC ApproveRequest
741
742
743CREATE PROC RejectRequest
744@teamID INTEGER,
745@studentID INTEGER
746AS
747BEGIN
748
749DECLARE @creator INTEGER
750SELECT @creator = member_creator
751FROM Project_Teams
752WHERE team_id = @teamID
753
754IF (@studentID = @creator)
755 BEGIN
756 UPDATE Students_requestToJoin_ProjectTeams
757 SET is_Approve = 'False', is_Pending = 'False'
758 WHERE team_id = @teamID
759 END
760
761END
762
763
764CREATE PROC DeletePosts
765@postID INTEGER,
766@academicID INTEGER,
767@courseID INTEGER
768AS
769BEGIN
770
771DECLARE @academic INTEGER
772SELECT @academic = academic_id
773FROM Academics_assign_Academics_on_Courses
774WHERE course_id = @courseID AND academic_id = @academicID
775
776IF (@academic = @academicID)
777 BEGIN
778 DELETE
779 FROM Posts
780 WHERE post_id = @postID
781 END
782END
783
784EXEC DeletePosts 3,1,1
785USE Social_Network
786
787CREATE PROC ViewFriendList
788@memberID INTEGER
789AS
790BEGIN
791
792DROP TABLE Friends
793
794CREATE TABLE Friends
795(
796friend_id INTEGER
797)
798
799INSERT INTO Friends
800SELECT recipient_id
801FROM Members_sendFriendRequestTo_Members
802WHERE sender_id = @memberID AND is_Approve = 'True'
803
804INSERT INTO Friends
805SELECT sender_id
806FROM Members_sendFriendRequestTo_Members
807WHERE recipient_id = @memberID AND is_Approve = 'True'
808
809SELECT *
810FROM Friends
811
812END
813
814EXEC ViewFriendList 1
815DROP PROC ViewFriendList