bMXD46WZ

1--LESSA PARENT MAT3AMALSH
2
3CREATE DATABASE dec9b
4GO
5USE dec9b
6GO
7CREATE FUNCTION isElementary(@x VARCHAR(50)) RETURNS BIT
8AS BEGIN
9	DECLARE @r BIT
10	SELECT @r = is_elementary FROM Schools WHERE @x = email
11	RETURN @r
12END
13GO
14
15CREATE FUNCTION hasChildInSchool(@parentEmail VARCHAR(50), @schoolEmail VARCHAR(50)) RETURNS BIT
16AS BEGIN
17	DECLARE @r2 BIT
18	IF EXISTS
19	(
20		SELECT *
21		FROM Enrolled_Students es
22		WHERE es.school_email = @schoolEmail AND es.parent_email = @parentEmail
23	)
24		SET @r2 = '1'
25	ELSE
26		SET @r2 = '0'
27	RETURN @r2
28END
29GO
30
31CREATE FUNCTION teacherTeachesStudent (@parentEmail VARCHAR(50), @teacherEmail VARCHAR(50)) RETURNS BIT
32AS BEGIN
33	DECLARE @r3 BIT
34	IF EXISTS
35	(
36		SELECT *
37		FROM Enrolled_Students es, Schools s, Employees e
38		WHERE @parentEmail = es.parent_email
39		AND es.school_email = s.email
40		AND s.email = e.school_email
41		AND e.email = @teacherEmail
42		AND e.is_teacher = '1'
43	)
44	SET @r3 = '1'
45	ELSE
46	SET @r3 = '0'
47	RETURN @r3
48END
49GO
50------------------------------------------------------------
51CREATE TABLE Schools 
52(
53	email VARCHAR(50) PRIMARY KEY,
54	vision VARCHAR(300),
55	main_language VARCHAR (30) ,
56	mission VARCHAR(300),
57	is_national BIT DEFAULT '0',
58	is_international BIT DEFAULT '0',
59	phone_number VARCHAR(20) UNIQUE,
60	home_page VARCHAR(100),
61	fees FLOAT ,
62	school_address VARCHAR(100) ,
63	general_information VARCHAR(300),
64	name VARCHAR (50),
65	is_elementary BIT NOT NULL DEFAULT '0',
66	is_middle BIT NOT NULL DEFAULT '0',
67	is_high BIT NOT NULL DEFAULT '0',
68	CONSTRAINT school_type CHECK (is_national<>0 OR is_international<>0), 
69	CONSTRAINT school_grade	CHECK (is_elementary<>0 OR is_middle<>0 OR is_high<>0)
70);
71CREATE TABLE Elementary_Supplies 
72(
73  supply VARCHAR(50),
74  email VARCHAR(50),
75  PRIMARY KEY(supply,email),
76  FOREIGN KEY (email) REFERENCES Schools,
77  CHECK (dbo.isElementary(email) = '1')
78);
79CREATE TABLE Parents
80(
81	email VARCHAR(50) PRIMARY KEY,
82	parent_address VARCHAR(100),
83	home_phone VARCHAR(20),
84	first_name VARCHAR(25),
85	last_name VARCHAR(25),
86	parent_user_name VARCHAR(25) UNIQUE,
87	parent_password VARCHAR(25)
88);
89CREATE TABLE Parents_Mobiles
90(
91	mobile VARCHAR(20) PRIMARY KEY,
92	email VARCHAR(50),
93	FOREIGN KEY (email) REFERENCES Parents
94);
95CREATE TABLE Parents_Write_Review_Schools
96(
97	parent_email VARCHAR(50),
98	school_email VARCHAR(50),
99	reviews VARCHAR(200),
100	PRIMARY KEY(parent_email, school_email),
101	FOREIGN KEY(parent_email) REFERENCES Parents(email),
102	FOREIGN KEY(school_email) REFERENCES Schools(email),
103	CHECK(dbo.hasChildInSchool(parent_email, school_email) = '1')
104);
105/*
106a0 2
107b0 5
108c1 1
109d1 3
110e1 10
111f1 11
112*/
113CREATE TABLE Applicants
114(
115	ssn INT PRIMARY KEY,
116	name VARCHAR(50),
117	gender BIT,
118	birth_date DATE,
119	age AS (year(current_timestamp) - year(birth_date)),
120	parent_email VARCHAR(50),
121	FOREIGN KEY (parent_email) REFERENCES Parents
122);
123
124CREATE TABLE Applicants_Applies_Schools
125(
126	school_email VARCHAR(50),
127	child_ssn INT,
128	accepted BIT DEFAULT '0',
129	parent_email VARCHAR(50),
130	PRIMARY KEY(child_ssn, school_email),
131	FOREIGN KEY(child_ssn) REFERENCES Applicants,
132	FOREIGN KEY(parent_email) REFERENCES Parents,
133	FOREIGN KEY(school_email) REFERENCES Schools
134);
135CREATE TABLE Enrolled_Students
136(
137	ssn INT PRIMARY KEY,
138	student_level INT,
139	name VARCHAR(50),
140	gender BIT,
141	birth_date DATE,
142	age AS (year(current_timestamp) - year(birth_date)),
143	student_password VARCHAR(20),
144	student_user_name VARCHAR(20) UNIQUE,
145	school_email VARCHAR(50),
146	parent_email VARCHAR(50),
147	FOREIGN KEY (school_email) REFERENCES Schools,
148	FOREIGN KEY (parent_email) REFERENCES Parents
149);
150CREATE TABLE Courses
151(
152	code VARCHAR(10) PRIMARY KEY,
153	name VARCHAR(50),
154	level_of_school INT,
155	course_description VARCHAR(100)
156);
157CREATE TABLE Courses_Prequisites
158(
159	course_code_1 VARCHAR(10),
160	course_code_2 VARCHAR(10),
161	PRIMARY KEY (course_code_1,course_code_2),
162	FOREIGN KEY (course_code_1) REFERENCES Courses,
163	FOREIGN KEY (course_code_2) REFERENCES Courses
164);
165
166
167
168CREATE TABLE Courses_Offered_By_Schools
169(
170	course_code VARCHAR(10),
171	school_email VARCHAR(50),
172	PRIMARY KEY (course_code, school_email),
173	FOREIGN KEY (course_code) REFERENCES Courses,
174	FOREIGN KEY (school_email) REFERENCES Schools
175);
176
177
178
179CREATE TABLE Employees
180(
181	email VARCHAR(50) PRIMARY KEY,
182	birth_date DATE,
183	employee_user_name VARCHAR(25) UNIQUE,
184	employee_password VARCHAR(25),
185	first_name VARCHAR(25),
186	middle_name VARCHAR(25),
187	last_name VARCHAR(25),
188	gender BIT,
189	employee_address VARCHAR(100),
190	salary FLOAT,
191	enrollment_year DATE,
192	years_of_experience as (year(current_timestamp) - year(enrollment_year)),
193	is_administrator BIT NOT NULL DEFAULT '0',
194	is_teacher BIT NOT NULL DEFAULT '0',
195	is_supervisor BIT NOT NULL DEFAULT '0',
196	is_verified BIT NOT NULL DEFAULT '0',
197	school_email VARCHAR(50),
198	supervisor_email VARCHAR(50),
199	FOREIGN KEY (school_email) REFERENCES Schools,
200	FOREIGN KEY (supervisor_email) REFERENCES Employees
201);
202CREATE TABLE Activities
203(
204	activity_date DATE,
205	activity_location VARCHAR(30),
206	activity_type VARCHAR(20),
207	activity_description VARCHAR(300),
208	administrator_email VARCHAR(50) NOT NULL,
209	teacher_email VARCHAR(50),
210	school_email VARCHAR(50) NOT NULL,
211	PRIMARY KEY (activity_date, activity_location),
212	FOREIGN KEY (administrator_email) REFERENCES Employees,
213	FOREIGN KEY (teacher_email) REFERENCES Employees,
214	FOREIGN KEY (school_email) REFERENCES Schools
215);
216CREATE TABLE Equipment
217(
218	activity_date DATE,
219	activity_location VARCHAR(30),
220	equipment VARCHAR(50),
221	PRIMARY KEY (activity_date, activity_location, equipment),
222	FOREIGN KEY (activity_date, activity_location) REFERENCES Activities
223);
224CREATE TABLE Activities_Applied_By_Enrolled_Students
225(
226	activity_date DATE,
227	activity_location VARCHAR(30),
228	ssn INT,
229	PRIMARY KEY (ssn, activity_date, activity_location),
230	FOREIGN KEY (ssn) REFERENCES Enrolled_Students,
231	FOREIGN KEY (activity_date, activity_location) REFERENCES Activities
232);
233CREATE TABLE Clubs
234(
235	name VARCHAR(50) PRIMARY KEY,
236	purpose VARCHAR(50)
237);
238CREATE TABLE Clubs_Joined_By_Enrolled_Student
239(
240	club_name VARCHAR(50),
241	enrolled_ssn INT,
242	PRIMARY KEY (club_name, enrolled_ssn),
243	FOREIGN KEY (club_name) REFERENCES Clubs,
244	FOREIGN KEY (enrolled_ssn) REFERENCES Enrolled_Students
245);
246CREATE TABLE Clubs_Offered_By_Schools --eh offers da :P
247(
248	name VARCHAR(50),
249	school_email VARCHAR(50),
250	PRIMARY KEY(name,school_email),
251	FOREIGN KEY (name) REFERENCES Clubs,
252	FOREIGN KEY (school_email) REFERENCES Schools
253);
254CREATE TABLE Questions
255(
256	question_number INT IDENTITY,
257	course_code VARCHAR(10) NOT NULL,
258	question_itself VARCHAR(500), --3ash X')
259	question_answer VARCHAR(500),
260	askers_ssn INT, 
261	PRIMARY KEY (question_number,course_code),
262	FOREIGN KEY (course_code) REFERENCES Courses,
263	FOREIGN KEY (askers_ssn) REFERENCES Enrolled_Students
264);
265CREATE TABLE Enrolled_Students_View_Questions
266(
267	ssn INT,
268	question_number INT,
269	course_code VARCHAR(10),
270	PRIMARY KEY (ssn, question_number, course_code),
271	FOREIGN KEY (ssn) REFERENCES Enrolled_Students,
272	FOREIGN KEY (question_number,course_code) REFERENCES Questions
273);
274CREATE TABLE Teachers_Response_To_Questions
275(
276	question_number INT,
277	course_code VARCHAR(10),
278	teacher_email VARCHAR(50),
279	PRIMARY KEY (question_number, course_code, teacher_email),
280	FOREIGN KEY (question_number,course_code) REFERENCES Questions,
281	FOREIGN KEY (teacher_email) REFERENCES Employees
282);
283CREATE TABLE Assignments
284(
285	assignment_number INT,
286	course_code VARCHAR(10),
287	teacher_email VARCHAR(50),
288	posting_date DATE,
289	due_date DATE,
290	content VARCHAR(500),
291	PRIMARY KEY (assignment_number, course_code, teacher_email),
292	FOREIGN KEY (course_code) REFERENCES Courses,
293	FOREIGN KEY (teacher_email) REFERENCES Employees
294);
295CREATE TABLE Assignments_Solved_By_Students
296(
297	ssn INT,
298	assignment_number INT,
299	teacher_email VARCHAR(50),
300	course_code VARCHAR(10),
301	grade FLOAT,
302	solution VARCHAR(3000),
303	PRIMARY KEY (ssn, assignment_number, teacher_email, course_code),
304	FOREIGN KEY (ssn) REFERENCES Enrolled_Students,
305	FOREIGN KEY (assignment_number, course_code, teacher_email) REFERENCES Assignments
306);
307
308
309
310CREATE TABLE View_And_Grade_Assignments
311(
312	viewer_email VARCHAR(50), --teacher_email ya3ny
313	assignment_number INT,
314	course_code VARCHAR(10),
315	creator_email VARCHAR(50),
316	PRIMARY KEY(viewer_email, assignment_number, course_code, creator_email),
317	FOREIGN KEY (assignment_number, course_code, creator_email) REFERENCES Assignments,
318	FOREIGN KEY (viewer_email) REFERENCES Employees
319);
320CREATE TABLE Attends
321(
322	enrolled_ssn INT,
323	course_code VARCHAR(10),
324	grade FLOAT,
325	PRIMARY KEY (enrolled_ssn, course_code),
326	FOREIGN KEY (enrolled_ssn) REFERENCES Enrolled_Students,
327	FOREIGN KEY (course_code) REFERENCES Courses
328);
329
330CREATE TABLE Teaches
331(
332	teacher_email VARCHAR(50),
333	course_code VARCHAR(10),
334	PRIMARY KEY (course_code, teacher_email),
335	FOREIGN KEY (teacher_email) REFERENCES Employees,
336	FOREIGN KEY (course_code) REFERENCES Courses
337);
338CREATE TABLE Announcements
339(
340	administrator_email VARCHAR(50),
341	announcement_date DATE,
342	announcement_time TIME,
343	announcement_type VARCHAR(20),
344	title VARCHAR(25),
345	announcement_description VARCHAR(300),
346	school_email VARCHAR(50) NOT NULL,
347	PRIMARY KEY (administrator_email, announcement_date, announcement_time),
348	FOREIGN KEY (administrator_email) REFERENCES Employees,
349	FOREIGN KEY (school_email) REFERENCES Schools
350);
351CREATE TABLE Announcements_Visible_To_Parents
352(
353	administrator_email VARCHAR(50),
354	announcement_date DATE,
355	announcement_time TIME,
356	parent_email VARCHAR(50),
357	PRIMARY KEY (administrator_email,announcement_date,announcement_time,parent_email),
358	FOREIGN KEY (parent_email) REFERENCES Parents,
359	FOREIGN KEY (administrator_email, announcement_date, announcement_time) REFERENCES Announcements
360);
361CREATE TABLE Announcements_Visible_To_Enrolled_Students
362(
363	administrator_email VARCHAR(50),
364	announcement_date DATE,
365	announcement_time TIME,
366	enrolled_ssn int,
367	PRIMARY KEY (administrator_email,announcement_date,announcement_time),
368	FOREIGN KEY (enrolled_ssn) REFERENCES Enrolled_Students,
369	FOREIGN KEY (administrator_email, announcement_date, announcement_time) REFERENCES Announcements
370);
371CREATE TABLE Parents_Ratings_To_Teachers
372(
373	employee_email VARCHAR(50),
374	parent_email VARCHAR(50),
375	rating DECIMAL(2,1), --eh da? X) leh decimal tab? mesh int? madam zero to five
376	CONSTRAINT chk_Rating CHECK (rating >= 0 AND rating <= 5),
377	PRIMARY KEY (employee_email, parent_email),
378	FOREIGN KEY (employee_email) REFERENCES Employees,
379	FOREIGN KEY (parent_email) REFERENCES Parents,
380	CHECK(dbo.teacherTeachesStudent(parent_email, employee_email) = '1')
381);
382
383--SELECT * FROM Monthly_Reports_Replies
384
385CREATE TABLE Monthly_Reports
386(
387	enrolled_ssn INT,
388	issue_date DATE,
389	comment VARCHAR(200),
390	writer VARCHAR(50) NOT NULL,
391	PRIMARY KEY (enrolled_ssn, issue_date),
392	FOREIGN KEY (enrolled_ssn) REFERENCES Enrolled_Students,
393	FOREIGN KEY (writer) REFERENCES Employees
394);
395
396CREATE TABLE Monthly_Reports_Replies
397(
398	enrolled_ssn INT,
399	issue_date DATE,
400	parent_email VARCHAR (50),
401	reply VARCHAR(200),
402	PRIMARY KEY (enrolled_ssn, issue_date, parent_email),
403	FOREIGN KEY (enrolled_ssn, issue_date) REFERENCES Monthly_Reports,
404	FOREIGN KEY (parent_email) REFERENCES Parents
405);
406GO
407-----------------------------------------------------------------------------------------
408--sysadmin
409CREATE PROC CreateSchool
410@school_name VARCHAR(50),
411@address VARCHAR(100),
412@phone_number VARCHAR(20),
413@email VARCHAR(50), 
414@general_information VARCHAR(300),
415@vision VARCHAR(300),
416@mission VARCHAR(300),
417@main_language VARCHAR (30),
418@type BIT,
419@is_elementary BIT,
420@is_middle BIT,
421@is_high BIT,
422@fees FLOAT
423AS
424	DECLARE @isNational bit SET @isNational = 0;
425	DECLARE @isInternational bit SET @isInternational = 0;
426	IF @type = 1
427		SET @isInternational = 1;
428	ELSE
429		SET @isNational = 1
430	IF @school_name is NULL or
431	@address is NULL or
432	@phone_number is NULL or
433	@type is NULL or
434	@fees is NULL
435		print 'One of the inputs is null'
436	else
437		INSERT INTO Schools(email , vision , main_language , mission , is_national , is_international , phone_number , fees , school_address , general_information , name,is_elementary,is_middle,is_high)
438		VALUES(@email, @vision, @main_language, @mission, @isNational, @isInternational, @phone_number, @fees, @address, @general_information, @school_name,@is_elementary,@is_middle,@is_high)
439GO
440
441--di 7aga fadla m'el functions?
442CREATE TYPE Course_List
443AS TABLE
444(
445	course_code VARCHAR(10)
446);
447GO
448
449--azonn me7tageen separate proc t'insert a separate prequisite
450CREATE PROC AddCourse
451@course_code VARCHAR(10), 
452@course_name VARCHAR(50), 
453--@course_level_elementary BIT,
454--@course_level_middle BIT,
455--@course_level_high BIT,
456@school_level INT,
457@description VARCHAR(100),
458@prequisite_courses AS Course_List READONLY
459AS
460	IF @course_code is NULL or
461	@course_name is NULL or
462--	@course_level_elementary is NULL or
463--	@course_level_middle is NULL or 
464--	@course_level_high is NULL or 
465	@school_level is NULL
466		print 'One of the inputs is null'
467	else
468		INSERT INTO Courses(code, level_of_school, course_description, name)
469		Values(@course_code, @school_level, @description, @course_name)
470
471	DECLARE @code VARCHAR(10)
472	DECLARE MY_CURSOR CURSOR 
473	LOCAL STATIC READ_ONLY FORWARD_ONLY
474	FOR 
475	SELECT DISTINCT course_code 
476	FROM @prequisite_courses
477	OPEN MY_CURSOR
478	FETCH NEXT FROM MY_CURSOR INTO @code
479	WHILE @@FETCH_STATUS = 0
480	BEGIN 
481		if EXISTS (Select code from Courses where code = @code)
482			INSERT INTO Courses_Prequisites(course_code_1, course_code_2)
483			VALUES(@course_code, @code)
484		else
485			print 'Prequisite course does not exist'
486		FETCH NEXT FROM MY_CURSOR INTO @code
487	END
488	CLOSE MY_CURSOR
489	DEALLOCATE MY_CURSOR
490GO
491
492CREATE PROC AddAdmin2
493@first_name VARCHAR(25),
494@middle_name VARCHAR(25),
495@last_name VARCHAR(25),
496@birthdate DATE,
497@address VARCHAR(100),	
498@email VARCHAR(50),
499@username VARCHAR(25),
500@password VARCHAR(25),
501@gender BIT
502AS
503	IF @first_name IS NULL OR
504	@middle_name IS NULL OR
505	@last_name IS NULL OR
506	@birthdate IS NULL OR
507	@address IS NULL OR
508	@email IS NULL OR
509	@username IS NULL OR
510	@password IS NULL OR
511	@gender IS NULL
512		print 'One of the inputs is null'
513	ELSE
514		declare @date as DATE set @date = CAST(GETDATE() AS DATE);
515		INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,gender,employee_address,enrollment_year,birth_date,is_administrator)
516		Values(@email, @username, @password, @first_name, @middle_name, @last_name, @gender, @address, @date, @birthdate, 1)
517GO
518
519CREATE PROC AssignAdminToSchool
520@admin_email VARCHAR(50),
521@school_email VARCHAR(50)
522AS
523	declare @school_type BIT
524	IF @admin_email IS NULL
525	OR @school_email IS NULL
526		print 'One of the inputs is null'
527	ELSE
528		IF EXISTS(
529			SELECT *
530			FROM Schools s
531			WHERE s.email = @school_email)
532		AND EXISTS(
533			SELECT *
534			FROM Employees
535			WHERE email = @admin_email)
536		BEGIN
537			declare @salary INT --???
538			SELECT @school_type = s.is_international
539			FROM Schools s
540			WHERE s.email = @school_email
541			IF @school_type = 1 --international
542				UPDATE Employees
543				SET school_email = @school_email, salary = 5000
544				WHERE email = @admin_email;
545			ELSE --national
546				UPDATE Employees
547				SET school_email = @school_email, salary = 3000
548				WHERE email = @admin_email;
549		END
550		ELSE
551			print 'School or Admin Does Not Exist'
552GO
553
554CREATE PROC DeleteSchool
555@school_email VARCHAR(50)
556AS
557	IF
558		@school_email IS NULL
559		print 'Input is null'
560	ELSE
561		DELETE FROM Schools
562		WHERE email = @school_email;
563		UPDATE Employees
564		SET employee_user_name = NULL, employee_password = NULL
565		WHERE school_email = @school_email
566GO
567----------------------------------------------------------
568
569-- Parent Procedures 1
570CREATE PROC InsertParents
571@email VARCHAR(50) ,
572@parent_address VARCHAR(100),
573@home_phone VARCHAR(20),
574@first_name VARCHAR(25),
575@last_name VARCHAR(25),
576@parent_user_name VARCHAR(25),
577@parent_password VARCHAR(25)
578AS
579	IF @email IS NULL OR
580	@parent_address IS NULL OR
581	@home_phone IS NULL OR
582	@first_name IS NULL OR
583	@last_name IS NULL OR
584	@parent_user_name IS NULL OR
585	@parent_password IS NULL 
586		PRINT 'One of the inputs is null'
587	ELSE
588		INSERT INTO Parents(email, parent_address, home_phone, first_name, last_name, parent_user_name, parent_password )
589		VALUES(@email, @parent_address, @home_phone, @first_name, @last_name, @parent_user_name, @parent_password)
590GO
591--2
592CREATE PROC Parents_Apply_Schools
593@ssn INT ,
594@name VARCHAR(50) ,
595@gender BIT,
596@birth_date DATE,
597@parent_email VARCHAR(50),
598@school_email_in VARCHAR(50)
599AS
600	IF
601	@ssn IS NULL OR
602	@name IS NULL OR
603	@gender IS NULL OR
604	@birth_date IS NULL OR
605	@parent_email IS NULL 
606		PRINT 'One of the inputs is null'
607	Else 
608		INSERT INTO Applicants (ssn,name,gender,birth_date,parent_email)
609		VALUES(@ssn,@name,@gender,@birth_date,@parent_email)
610		INSERT INTO Applicants_Applies_Schools
611		(school_email , child_ssn , parent_email)
612		VALUES (@school_email_in , @ssn , @parent_email)
613GO
614
615--3
616CREATE PROC Schools_Accepted_My_Children
617@ssn_in INT
618AS
619	SELECT s.* 
620	FROM Schools s
621	INNER JOIN Applicants_Applies_Schools a on a.school_email = s.email
622	where @ssn_in = a.child_ssn AND a.Accepted = 'True'
623GO
624--4
625CREATE PROC ChooseSchool --noexec
626@parent_email VARCHAR(50),
627@child_ssn INT,
628@school_email VARCHAR(50)
629AS
630	IF  @parent_email is null
631	or @child_ssn is null
632	or @school_email is null
633		print 'Null Input'
634	ELSE
635	Begin
636		--Declare @table Table
637		Declare @name VARCHAR(50)
638		Declare @gender BIT
639		Declare @birth_date DATE
640		SELECT @name = e.name, @gender = e.gender, @birth_date = e.birth_date
641		From Applicants e INNER JOIN Applicants_Applies_Schools a on a.child_ssn = e.ssn
642		Where e.parent_email = @parent_email AND a.accepted = 1
643		Insert Into Enrolled_Students
644		(ssn , name , gender , birth_date , school_email , parent_email)
645		Values(@child_ssn, @name, @gender, @birth_date, @school_email, @parent_email)
646		DELETE FROM Applicants
647		WHERE ssn = @child_ssn
648	END
649GO			
650--5
651CREATE PROC View_Reports
652@parent_email VARCHAR(50)
653AS
654	SELECT r.*
655	FROM Monthly_Reports r
656	INNER JOIN Enrolled_Students e ON r.enrolled_ssn = e.ssn
657	WHERE e.parent_email = @parent_email
658GO
659--6
660CREATE PROC Reply
661@parent_email VARCHAR(50),
662@reply VARCHAR(200),
663@issue_date DATE,
664@enrolled_ssn INT
665AS
666	INSERT INTO Monthly_Reports_Replies (enrolled_ssn ,issue_date,parent_email,reply)
667	VALUES(@enrolled_ssn,@issue_date,@parent_email,@reply)
668GO
669--7
670CREATE PROC List_of_Children_Schools
671@parent_email VARCHAR(50)
672AS
673SELECT e.name + ' in ' + s.name AS 'List of my children schools'
674FROM Enrolled_Students e
675		INNER JOIN Schools s on e.school_email = s.email
676WHERE e.parent_email = @parent_email 
677order by s.name
678GO
679--8
680CREATE PROC Posted_Announcments
681@parent_email VARCHAR(50),
682@Enrolled_ssn INT
683AS
684DECLARE @p10days TIMESTAMP = DAY(CURRENT_TIMESTAMP)
685SELECT a.announcement_description
686FROM Announcements_Visible_To_Parents ap
687		INNER JOIN Parents p ON p.email = ap.parent_email
688		INNER JOIN Announcements a ON a.administrator_email = ap.administrator_email
689		INNER JOIN Announcements_Visible_To_Enrolled_Students ae ON ae.administrator_email = ap.administrator_email
690WHERE ap.parent_email = @parent_email AND ae.enrolled_ssn = @Enrolled_ssn AND (DAY(ap.announcement_date) + 10) > @p10days
691GO
692--9
693CREATE PROC Rate_Teachers
694@parent_email VARCHAR(50),
695@teacher_email VARCHAR(50),
696@rating DECIMAL(2,1)
697AS
698IF	@parent_email IS NULL OR
699	@teacher_email IS NULL OR
700	@rating IS NULL
701	print 'NULL INPUT'
702ELSE
703IF @parent_email IN
704	(
705		Select parent_email
706		From Enrolled_Students e
707			INNER JOIN Attends a on a.enrolled_ssn = e.ssn
708			INNER JOIN Teaches T on T.course_code = a.course_code
709		Where e.parent_email = @parent_email 	
710	)
711	INSERT INTO Parents_Ratings_To_Teachers(employee_email, parent_email, rating)
712	VALUES(@teacher_email, @parent_email, @rating)
713GO
714--10
715CREATE PROC Write_Reviews
716@parent_email VARCHAR(50),
717@school_email VARCHAR(50),
718@reviews VARCHAR(200)
719AS
720	IF @parent_email IS NULL OR @school_email IS NULL OR @reviews IS NULL
721		PRINT 'Incomplete input!'
722	ELSE IF NOT EXISTS(
723	SELECT e.parent_email
724	FROM Enrolled_Students e
725	Where e.parent_email = @parent_email AND e.school_email = @school_email)
726		PRINT 'You must have a student enrolled in this school'
727	ELSE
728		INSERT INTO Parents_Write_Review_Schools
729		(parent_email , reviews , school_email)
730		VALUES (@parent_email , @reviews ,@school_email)
731GO
732
733--EXEC Write_Reviews "shady.azoz@MSA.edu.eg" , "Shwyfat@edu.eg" , "eww"
734--EXEC Write_Reviews "shady.azoz@MSA.edu.eg" , "ST.Fatima@edu.eg" , "fffff"
735
736--11
737CREATE PROC Delete_review
738@parent_email VARCHAR(50),
739@school_email VARCHAR(50)
740AS
741	DELETE FROM Parents_Write_Review_Schools
742	WHERE parent_email = @parent_email
743	AND @school_email = school_email
744GO
745
746--12
747CREATE PROC Sum_rating
748@teacher_fname VARCHAR(25),
749@teacher_lname VARCHAR(25),
750@teacher_rate FLOAT OUTPUT
751As
752DECLARE @total INT
753DECLARE @count INT
754SELECT @Total = SUM(pt.rating), @count = count(pt.rating)
755FROM Parents_Ratings_To_Teachers pt
756		INNER JOIN Employees e ON e.email = pt.employee_email
757		WHERE e.first_name = @teacher_fname AND e.last_name = @teacher_lname
758SET @teacher_rate = @total/@count
759PRINT @teacher_rate
760GO
761--13
762CREATE PROC TOP_10_Schools
763@parent_email VARCHAR(50)
764AS
765SELECT s.name, COUNT(ps.reviews), COUNT(e.ssn)
766FROM Schools s
767		INNER JOIN Parents_Write_Review_Schools ps ON ps.school_email = s.email
768		INNER JOIN Enrolled_Students e ON e.school_email = s.email
769		WHERE ps.parent_email <> @parent_email
770GROUP BY s.name 
771GO
772--14
773CREATE PROC Highest_International_School
774@schoolname VARCHAR(50) OUTPUT,
775@count INT OUTPUT
776AS
777SELECT @schoolname = s.name, @count = count(ps.reviews)
778FROM Schools s
779		INNER JOIN Parents_Write_Review_Schools ps ON ps.school_email = s.email
780GROUP BY s.name
781Having count(ps.reviews) = MAX(ps.reviews)
782GO
783---------------------------------------------------------------------------------
784-- System User
785--1
786CREATE PROC List_School
787@name VARCHAR(50),
788@address VARCHAR(100),
789@national BIT,
790@international BIT 
791AS
792SELECT *
793FROM Schools
794WHERE name = @name OR school_address = @address OR is_national = @national OR is_international = @international
795GO
796--2
797CREATE PROC Schools_By_Levels
798@elementary BIT, 
799@middle BIT, 
800@high BIT
801AS
802SELECT name,is_elementary,is_middle,is_high
803FROM Schools 
804WHERE is_elementary = @elementary or is_middle = @middle or is_high = @high
805ORDER BY is_elementary, is_middle, is_high
806GO
807--3 
808CREATE PROC School_Info
809@name VARCHAR(50)
810AS
811SELECT *
812FROM Schools s
813	INNER JOIN Parents_Write_Review_Schools ps on ps.school_email = s.email
814	INNER JOIN Employees e ON e.school_email = s.email
815	WHERE s.name = @name
816GO
817
818----------------------------------------------------------------------------------------
819--adminstrator
820
821CREATE PROC ViewTeachers
822@admin_email VARCHAR(50)
823AS
824	IF
825		@admin_email IS NULL
826		print 'Null Input'
827	ELSE
828		DECLARE @isAdmin BIT
829		DECLARE @school_email VARCHAR(50)
830		Select @isAdmin = is_administrator, @school_email = e.school_email
831		FROM Employees e
832		WHERE e.email = @admin_email
833		IF @isAdmin = 1
834			Select *
835			FROM Employees e
836			Where e.is_teacher = 1 AND e.school_email = @school_email
837		ELSE
838			PRINT 'Not Admin'
839GO
840CREATE PROC AssignTeacher
841	@admin_email VARCHAR(50),
842	@teacher_email VARCHAR(50),
843	@user_name VARCHAR(25),
844	@password VARCHAR(25)
845AS
846	IF
847		@admin_email IS NULL OR
848		@teacher_email IS NULL OR
849		@user_name IS NULL OR
850		@password IS NULL
851		print 'Null Input'
852	ELSE
853		DECLARE @isAdmin bit
854		DECLARE @school_email VARCHAR(50)
855		Select @isAdmin = is_administrator, @school_email = e.school_email
856		FROM Employees e
857		WHERE e.email = @admin_email
858		IF @isAdmin = 1
859			UPDATE Employees
860			SET employee_user_name = @user_name, employee_password = @password, salary = years_of_experience * 500
861			WHERE email = @teacher_email
862GO
863CREATE PROC ViewStudents
864@admin_email VARCHAR(50)
865AS
866Begin
867	IF
868		@admin_email IS NULL
869		print 'Null Input'
870	ELSE
871		DECLARE @isAdmin bit
872		DECLARE @school_email VARCHAR(50)
873		Select @isAdmin = is_administrator, @school_email = e.school_email
874		FROM Employees e
875		WHERE e.email = @admin_email
876		IF @isAdmin = 1
877			Select *
878			FROM Enrolled_Students e
879			WHERE e.school_email = @school_email
880End
881GO
882CREATE PROC AssignStudent
883	@admin_email VARCHAR(50),
884	@student_ssn VARCHAR(50),
885	@user_name VARCHAR(25),
886	@password VARCHAR(25)
887AS
888	IF
889		@admin_email IS NULL OR
890		@student_ssn IS NULL OR
891		@user_name IS NULL OR
892		@password IS NULL
893		print 'Null Input'
894	ELSE
895		DECLARE @isAdmin bit
896		DECLARE @school_email VARCHAR(50)
897		Select @isAdmin = is_administrator, @school_email = e.school_email
898		FROM Employees e
899		WHERE e.email = @admin_email
900		IF @isAdmin = 1
901			UPDATE Enrolled_Students
902			SET student_user_name = @user_name, student_password = @password
903			WHERE ssn = @student_ssn
904GO
905CREATE PROC AddAdmin
906	@s_admin_email VARCHAR(50),
907	@first_name VARCHAR(25),
908	@middle_name VARCHAR(25), 
909	@last_name VARCHAR(25),
910	@birthdate DATE, 
911	@address VARCHAR(100),
912	@email VARCHAR(50),
913	@username VARCHAR(25), 
914	@password VARCHAR(25),
915	@gender BIT
916AS
917	IF
918		@s_admin_email IS NULL OR
919		@first_name IS NULL OR
920		@middle_name IS NULL OR 
921		@last_name IS NULL OR
922		@birthdate IS NULL OR 
923		@address IS NULL OR
924		@email IS NULL OR
925		@username IS NULL OR 
926		@password IS NULL OR
927		@gender IS NULL
928		print 'Null Input'
929	ELSE
930		DECLARE @isAdmin bit
931		DECLARE @school_email VARCHAR(50)
932		Select @isAdmin = is_administrator, @school_email = e.school_email
933		FROM Employees e
934		WHERE e.email = @s_admin_email
935		IF @isAdmin = 1
936		DECLARE @sal FLOAT
937		DECLARE @is_international BIT
938		SELECT @is_international = is_international
939		FROM Schools s
940		WHERE s.email = @school_email
941		IF(@is_international = 1)
942			SET @sal = 5000
943		ELSE
944			SET @sal = 3000
945			INSERT INTO Employees(email,
946									employee_user_name,
947									employee_password,
948									first_name,
949									middle_name,
950									last_name,
951									gender,
952									employee_address,
953									enrollment_year,
954									school_email, 
955									is_administrator,
956									is_teacher,
957									is_supervisor,
958									salary)
959			VALUES(@email, @username, @password, @first_name, @middle_name, @last_name, @gender, @address, CAST(GETDATE() AS DATE), @school_email, 1, 0, 0, @sal)
960GO
961CREATE PROC DeleteEmployee
962	@admin_email VARCHAR(50),
963	@emp_email VARCHAR(50)
964AS
965	IF
966		@admin_email IS NULL OR
967		@emp_email IS NULL
968		print 'Null Input'
969	ELSE
970		DECLARE @isAdmin bit
971		DECLARE @school_email VARCHAR(50)
972		Select @isAdmin = is_administrator, @school_email = e.school_email
973		FROM Employees e
974		WHERE e.email = @admin_email
975			IF @isAdmin = 1
976				DELETE FROM Employees
977				WHERE school_email = @school_email AND email = @emp_email
978GO
979CREATE PROC DeleteStudent
980	@admin_email VARCHAR(50),
981	@student_ssn VARCHAR(50)
982AS
983	IF
984		@admin_email IS NULL OR
985		@student_ssn IS NULL
986		print 'Null Input'
987	ELSE
988		DECLARE @isAdmin bit
989		DECLARE @school_email VARCHAR(50)
990		Select @isAdmin = is_administrator, @school_email = e.school_email
991		FROM Employees e
992		WHERE e.email = @admin_email
993			IF @isAdmin = 1
994				DELETE FROM Enrolled_Students
995				WHERE school_email = @school_email AND ssn = @student_ssn
996GO
997
998/*EXEC EditSchool 
999"ashraf.hassan@ST.Fatima.edu.eg" 
1000, "Shwyfat@edu.eg" 
1001, "asdfasedf"
1002, "asdfasdf" 
1003, "sadfasdfasdf" 
1004, 1 , 1 
1005, "654" 
1006, "sdfgsdfg" 
1007, "54" 
1008, "fdgfgdfg" 
1009, "fgfg" 
1010, "sadfafasd" 
1011, 1 , 1 , 1*/
1012GO
1013
1014CREATE PROC EditSchool
1015	@admin_email VARCHAR(50),
1016	@email VARCHAR(50),
1017	@vision VARCHAR(300),
1018	@main_language VARCHAR (30),
1019	@mission VARCHAR(300),
1020	@is_national BIT,
1021	@is_international BIT,
1022	@phone_number VARCHAR(20),
1023	@home_page VARCHAR(100),
1024	@fees FLOAT,
1025	@school_address VARCHAR(100),
1026	@general_information VARCHAR(300),
1027	@name VARCHAR(50),
1028	@is_elementary BIT,
1029	@is_middle BIT,
1030	@is_high BIT
1031AS
1032	IF
1033		@admin_email IS NULL
1034		print 'Null Input'
1035	ELSE
1036		DECLARE @isAdmin bit
1037		DECLARE @school_email VARCHAR(50)
1038		Select @isAdmin = is_administrator, @school_email = e.school_email
1039		FROM Employees e
1040		WHERE e.email = @admin_email
1041			IF @isAdmin = 1
1042				UPDATE Schools
1043				SET 
1044					email = @email,
1045					vision = @vision,
1046					main_language = @main_language,
1047					mission = @mission,
1048					is_national = @is_national,
1049					is_international = @is_international,
1050					phone_number = @phone_number,
1051					home_page = @home_page,
1052					fees = @fees,
1053					school_address = @school_address,
1054					general_information = @general_information,
1055					name = @name,
1056					is_elementary = @is_elementary,
1057					is_middle = @is_middle,
1058					is_high = @is_high
1059				WHERE email = @school_email
1060GO
1061
1062--EXEC PostAnnoucement "bb" , "a" , "aa" , "aaa"
1063
1064
1065CREATE PROC PostAnnoucement
1066	@admin_email VARCHAR(50),
1067	@title VARCHAR(25), 
1068	@description VARCHAR(300),
1069	@type VARCHAR(20)
1070AS
1071	IF
1072		@admin_email IS NULL OR
1073		@title IS NULL OR 
1074		@description IS NULL OR
1075		@type IS NULL
1076		print 'Null Input'
1077	ELSE
1078		DECLARE @isAdmin bit
1079		DECLARE @school_email VARCHAR(50)
1080		Select @isAdmin = is_administrator, @school_email = e.school_email
1081		FROM Employees e
1082		WHERE e.email = @admin_email
1083			IF @isAdmin = 1
1084				INSERT INTO Announcements(administrator_email,
1085											announcement_date,
1086											announcement_time,
1087											announcement_type,
1088											title,
1089											announcement_description,
1090											school_email)
1091				VALUES(@admin_email,  CAST(GETDATE() AS DATE),  CAST(GETDATE() AS TIME), @type, @title, @description, @school_email)
1092GO
1093CREATE PROC CreateActivity
1094	@admin_email VARCHAR(50),
1095	@activity_date DATE,
1096	@activity_location VARCHAR(30),
1097	@teacher_email VARCHAR(50),
1098	@activity_type VARCHAR(20),
1099	@activity_description VARCHAR(300),
1100	@equipment VARCHAR(50)
1101AS
1102	IF
1103		@admin_email IS NULL OR
1104		@activity_date IS NULL OR
1105		@activity_location IS NULL OR
1106		@teacher_email IS NULL OR
1107		@activity_type IS NULL OR
1108		@activity_description IS NULL
1109		print 'Null Input'
1110	ELSE
1111		DECLARE @isAdmin bit
1112		DECLARE @school_email VARCHAR(50)
1113		Select @isAdmin = is_administrator, @school_email = e.school_email
1114		FROM Employees e
1115		WHERE e.email = @admin_email
1116			IF @isAdmin = 1
1117				INSERT INTO Activities(activity_date,
1118										activity_location,
1119										administrator_email,
1120										teacher_email,
1121										activity_type,
1122										activity_description,
1123										school_email)
1124				VALUES(@activity_date, @activity_location, @admin_email, @teacher_email, @activity_type, @activity_description, @school_email)
1125				IF @equipment IS NOT NULL
1126					INSERT INTO Equipment(activity_date, activity_location, equipment)
1127					VALUES(@activity_date, @activity_location, @equipment)
1128GO
1129CREATE PROC AssignTeacherToActivity
1130	@admin_email VARCHAR(50),
1131	@activity_date DATE,
1132	@activity_location VARCHAR(30),
1133	@teacher_email VARCHAR(50)
1134AS
1135	IF
1136		@admin_email IS NULL OR
1137		@activity_date IS NULL OR
1138		@activity_location IS NULL OR
1139		@teacher_email IS NULL
1140		print 'Null Input'
1141	ELSE
1142		DECLARE @isAdmin bit
1143		DECLARE @school_email VARCHAR(50)
1144		Select @isAdmin = is_administrator, @school_email = e.school_email
1145		FROM Employees e
1146		WHERE e.email = @admin_email
1147			IF @isAdmin = 1
1148				UPDATE Activities
1149				SET teacher_email = @teacher_email
1150				WHERE school_email = @school_email AND activity_date = @activity_date AND activity_location = @activity_location
1151GO
1152CREATE PROC AssignTeacherToCourse
1153	@admin_email VARCHAR(50),
1154	@teacher_email VARCHAR(50),
1155	@course_code VARCHAR(10)
1156AS
1157	IF
1158		@admin_email IS NULL OR
1159		@teacher_email IS NULL OR
1160		@course_code IS NULL
1161		print 'Null Input'
1162	ELSE
1163		DECLARE @isAdmin bit
1164		DECLARE @school_email VARCHAR(50)
1165		Select @isAdmin = is_administrator, @school_email = e.school_email
1166		FROM Employees e
1167		WHERE e.email = @admin_email
1168		IF @teacher_email IN (SELECT e2.email
1169							  FROM Employees e1 INNER JOIN Employees e2 on e1.school_email = e2.school_email
1170							  Where e1.email = @admin_email)
1171				DECLARE @level int
1172				DECLARE @high BIT
1173				DECLARE @mid BIT
1174				DECLARE @elem BIT
1175				SELECT @level = level_of_school  
1176				FROM Courses
1177				WHERE code = @course_code
1178				SELECT @high = is_high, @mid = is_middle, @elem = is_elementary
1179				FROM Schools
1180				WHERE email = @school_email
1181				IF (@level > 9 AND @high = 1) OR (@level > 6 AND @mid = 1) OR (@level < 7 AND @elem = 1)
1182					INSERT INTO Teaches(teacher_email, course_code)
1183					VALUES(@teacher_email, @course_code)
1184GO
1185CREATE PROC AssignSupervisor
1186	@admin_email VARCHAR(50),
1187	@teacher_email VARCHAR(50),
1188	@supervisor_email VARCHAR(50)
1189AS
1190	IF
1191		@admin_email IS NULL OR
1192		@supervisor_email IS NULL OR
1193		@teacher_email IS NULL
1194		print 'Null Input'
1195	ELSE
1196		DECLARE @isAdmin bit
1197		DECLARE @school_email VARCHAR(50)
1198		Select @isAdmin = is_administrator, @school_email = e.school_email
1199		FROM Employees e
1200		WHERE e.email = @admin_email
1201		IF @isAdmin = 1
1202			UPDATE Employees
1203			SET supervisor_email = @supervisor_email
1204			WHERE email = @teacher_email AND school_email = @school_email
1205GO
1206CREATE PROC HandleSchoolApplication
1207	@admin_email VARCHAR(50),
1208	@child_ssn INT,
1209	@answer BIT
1210AS
1211	IF
1212		@admin_email IS NULL OR
1213		@child_ssn IS NULL
1214		print 'Null Input'
1215	ELSE
1216		DECLARE @isAdmin bit
1217		DECLARE @school_email VARCHAR(50)
1218		Select @isAdmin = is_administrator, @school_email = e.school_email
1219		FROM Employees e
1220		WHERE e.email = @admin_email
1221		IF @isAdmin = 1
1222			IF @answer = 1
1223			BEGIN
1224				declare @parent_email VARCHAR(50)
1225				declare @ssn INT
1226				declare @name VARCHAR(50) 
1227				declare @gender BIT
1228				declare @birth_date DATE
1229				SELECT @parent_email = a.parent_email, @ssn = a.ssn, @name = a.name, @gender = a.gender, @birth_date = a.birth_date
1230				FROM Applicants_Applies_Schools s INNER JOIN Applicants a ON  a.ssn = s.child_ssn
1231				WHERE school_email = @school_email AND child_ssn = @child_ssn
1232				INSERT INTO Enrolled_Students
1233				(ssn , name , gender , birth_date , school_email , parent_email)
1234				VALUES(@ssn, @name, @gender, @birth_date, @school_email, @parent_email)
1235			END
1236				DELETE FROM Applicants_Applies_Schools
1237				WHERE school_email = @school_email AND child_ssn = @ssn
1238				DELETE FROM Applicants
1239				WHERE ssn = @ssn 
1240GO
1241---------------------------------------------------------------------------------------------------------------------------
1242
1243
1244--Student procedures
1245--1
1246CREATE PROC Enrolled_Student_Info_Update
1247@ssn_in INT,
1248@name_in VARCHAR(50),
1249@gender_in BIT,
1250@birth_date_in DATE,
1251@password_in VARCHAR(20),
1252@school_email_in VARCHAR(50),
1253@parent_email_in VARCHAR(50)
1254AS
1255	IF @ssn_in is NULL
1256		PRINT 'Please enter SSN.'
1257	ELSE
1258		UPDATE Enrolled_Students
1259		SET
1260			name = ISNULL(@name_in , name),
1261			gender = ISNULL(@gender_in , gender),
1262			birth_date = ISNULL(@birth_date_in , birth_date),
1263			student_password = ISNULL(@password_in , student_password),
1264			school_email = ISNULL(@school_email_in , school_email),
1265			parent_email = ISNULL(@parent_email_in , parent_email)
1266		WHERE
1267			@ssn_in = Enrolled_Students.ssn
1268GO
1269
1270--2
1271CREATE PROC Enrolled_Student_View_Courses
1272@ssn_in INT
1273AS
1274	IF @ssn_in = NULL
1275		PRINT 'Please enter SSN.'
1276	ELSE
1277		SELECT t.grade, c.name , c.code
1278		FROM Courses c INNER JOIN Attends t ON t.course_code = c.code
1279		WHERE t.enrolled_ssn = @ssn_in
1280		GROUP BY t.grade , c.name , c.code
1281		ORDER BY c.name 
1282GO
1283
1284
1285
1286--3
1287CREATE PROC Enrolled_Student_Post_Question
1288@course_code_in VARCHAR(10),
1289@question_itself_in VARCHAR(500),
1290@askers_ssn_in INT
1291AS
1292	IF @askers_ssn_in = NULL
1293		PRINT 'Please enter SSN.'
1294	ELSE
1295		INSERT INTO Questions (course_code,question_itself,askers_ssn)
1296		VALUES (@course_code_in,@question_itself_in,@askers_ssn_in)
1297GO
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308--4
1309CREATE PROC Enrolled_Student_View_Questions
1310@course_code_in VARCHAR(10),
1311@askers_ssn_in INT
1312AS
1313	IF @askers_ssn_in = NULL OR @course_code_in = NULL
1314		PRINT 'Input Incomplete.'
1315	ELSE IF NOT EXISTS
1316	(SELECT *
1317	FROM Enrolled_Students_View_Questions p --can use "Teaches" instead of this table. Redundant
1318	WHERE @course_code_in = p.course_code
1319	AND @askers_ssn_in = p.ssn)
1320		PRINT 'You do not have the permission to view questions on this course.'
1321	ELSE
1322		SELECT q.question_itself, q.question_answer
1323		FROM Questions q
1324		WHERE @course_code_in = q.course_code
1325GO
1326
1327
1328
1329--EXEC Enrolled_Student_View_Questions "MATH501" , 2113524
1330
1331
1332--5
1333/*CREATE PROC Enrolled_Student_View_All_Assignments
1334@ssn_in INT
1335AS
1336	IF @ssn_in = NULL
1337		PRINT 'Please enter ssn.'
1338	ELSE
1339		SELECT asst.assignment_number , asst.course_code , asst.posting_date , asst.due_date , asst.content
1340		FROM Attends at
1341		INNER JOIN Assignments asst ON asst.course_code = at.course_code
1342		WHERE @ssn_in = at.enrolled_ssn
1343		SELECT t.assignment_number, t.course_code, t.posting_date, t.due_date, t.content
1344		FROM Teaches t 
1345		--INNER JOIN Courses c ON t.course_code = c.code
1346		INNER JOIN Assignments a ON a.course_code = t.course_code
1347		WHERE t.enrolled_ssn = @ssn_in
1348
1349
1350ALTER PROC Enrolled_Student_View_All_Assignments
1351@ssn_in INT
1352AS
1353	IF @ssn_in = NULL
1354		PRINT 'Please enter ssn.'
1355	ELSE
1356		SELECT *
1357		FROM Assignments
1358		WHERE assignment_number IN (SELECT enrolled_ssn FROM Attends WHERE enrolled_ssn = @ssn_in)
1359GO
1360CREATE PROC Enrolled_Student_View_All_Assignments
1361@ssn_in INT
1362AS
1363	IF @ssn_in = NULL
1364		RAISERROR('Enter SSN', 16, 1)
1365	ELSE
1366		SELECT *
1367		FROM Assignments
1368		WHERE assignment_number IN (SELECT enrolled_ssn FROM Attends WHERE enrolled_ssn = @ssn_in)
1369GO*/
1370CREATE PROC Enrolled_Student_View_All_Assignments
1371@ssn_in INT
1372AS
1373	IF @ssn_in = NULL
1374	RAISERROR('Enter SSN', 16, 1)
1375	ELSE
1376	SELECT *
1377	FROM Assignments
1378	WHERE course_code IN (SELECT course_code FROM Attends WHERE enrolled_ssn = @ssn_in)
1379Go
1380
1381CREATE PROC Enrolled_Student_View_Course_Assignments
1382@ssn_in INT,
1383@course_code_in VARCHAR(10)
1384AS
1385	IF @ssn_in = NULL
1386	RAISERROR('Enter SSN', 16, 1)
1387	ELSE
1388	SELECT *
1389	FROM Assignments
1390	WHERE course_code = @course_code_in
1391GO
1392
1393
1394
1395
1396
1397--6
1398CREATE PROC Enrolled_Student_Solves_Assignment
1399@ssn_in INT,
1400@assignment_number_in INT,
1401@teacher_email_in VARCHAR(50),
1402@course_code_in VARCHAR(10),
1403@solution_in VARCHAR(3000)
1404AS
1405	IF @ssn_in = NULL
1406	OR @assignment_number_in = NULL
1407	OR @course_code_in = NULL
1408	OR @teacher_email_in = NULL
1409	OR @solution_in = NULL
1410		PRINT 'Incomplete Input.'
1411	ELSE IF NOT EXISTS(
1412	SELECT * FROM Attends t
1413	INNER JOIN Teaches tt ON t.course_code = tt.course_code
1414	WHERE t.enrolled_ssn = @ssn_in
1415	AND t.course_code = @course_code_in
1416	AND tt.teacher_email = @teacher_email_in)
1417		PRINT 'This course is not assigned to you.'
1418	ELSE
1419		INSERT INTO Assignments_Solved_By_Students
1420		(ssn,assignment_number,teacher_email,course_code,solution)
1421		VALUES (@ssn_in,@assignment_number_in,@teacher_email_in,@course_code_in,@solution_in)
1422GO
1423
1424
1425
1426
1427
1428
1429--7
1430
1431CREATE PROC Enrolled_Students_Views_Assignments_Grades
1432@ssn_in INT
1433AS
1434	IF @ssn_in = NULL
1435		PRINT 'Please enter SSN.'
1436	ELSE
1437		SELECT s.course_code, s.assignment_number, s.grade
1438		FROM Assignments_Solved_By_Students s
1439		WHERE s.ssn = @ssn_in
1440GO
1441
1442
1443
1444
1445
1446
1447
1448--8
1449--student's school
1450CREATE PROC Enrolled_Student_Views_Announcements
1451@ssn_in INT
1452AS
1453	IF @ssn_in = NULL
1454		PRINT 'Please enter SSN.'
1455	ELSE
1456		SELECT a.title, a.announcement_description
1457		FROM Announcements_Visible_To_Enrolled_Students av
1458		INNER JOIN Announcements a ON
1459		a.administrator_email = av.administrator_email
1460		AND a.announcement_date = av.announcement_date
1461		AND a.announcement_time = av.announcement_time
1462		WHERE @ssn_in = av.enrolled_ssn
1463		--AND DAY(a.announcement_date) > (DAY(CURRENT_TIMESTAMP) - 10)
1464		AND DATEDIFF(DAY,a.announcement_date,CONVERT(DATE, GETDATE())) < 10
1465		AND EXISTS
1466		(SELECT *
1467		FROM Enrolled_Students es
1468		WHERE es.ssn = @ssn_in
1469		AND a.school_email = es.school_email)
1470GO
1471
1472
1473--9
1474CREATE PROC Enrolled_Student_Views_Activities
1475@ssn_in INT
1476AS
1477	IF @ssn_in = NULL
1478		PRINT 'Please enter SSN.'
1479	ELSE
1480		SELECT a.activity_date, a.activity_location, a.activity_type, a.activity_description, e.first_name, e.middle_name, e.last_name, e.gender, e.email
1481		FROM Activities a INNER JOIN Employees e ON a.teacher_email = e.email
1482		WHERE e.is_teacher = 1
1483		AND EXISTS (
1484		SELECT *
1485		FROM Enrolled_Students es
1486		WHERE es.ssn = @ssn_in
1487		AND a.school_email = es.school_email)
1488GO
1489
1490
1491
1492
1493--10
1494--PLEASE CORRECT THIS PROCEDURE
1495--"date is not recognised as a built-in function name"
1496CREATE PROC Enrolled_Student_Applies_To_Activity
1497@ssn_in INT,
1498@activity_date_in DATE,
1499@activity_location_in VARCHAR(30)
1500AS
1501	IF @ssn_in = NULL
1502		PRINT 'Please enter SSN.'
1503	ELSE IF EXISTS(
1504	SELECT * FROM Activities_Applied_By_Enrolled_Students ae
1505	INNER JOIN Activities a ON ae.activity_date = ae.activity_date AND ae.activity_location = a.activity_location
1506	WHERE @ssn_in = ae.ssn AND EXISTS(
1507		SELECT * FROM Activities sa
1508		WHERE sa.activity_date = @activity_date_in
1509		AND sa.activity_location = @activity_location_in
1510		AND sa.activity_type = a.activity_type
1511		AND sa.activity_date = a.activity_date
1512	))
1513		PRINT 'Can not join two activities on the same date of the same type!'
1514	ELSE
1515		INSERT INTO Activities_Applied_By_Enrolled_Students
1516		(ssn, activity_date, activity_location)
1517		VALUES (@ssn_in,@activity_date_in,@activity_location_in)
1518GO
1519
1520--11
1521CREATE PROC Enrolled_Student_Join_Club
1522@ssn_in INT,
1523@club_name_in VARCHAR(50)
1524AS
1525	IF @ssn_in = NULL OR @club_name_in = NULL
1526		RAISERROR ('Incomplete input!' , 15 , 15)
1527	ELSE IF NOT EXISTS(
1528	SELECT * FROM Enrolled_Students es
1529	WHERE @ssn_in =	es.ssn AND es.student_level > 9)
1530		RAISERROR ('You are not a high school student' , 15 , 15)
1531	ELSE IF NOT EXISTS(
1532	SELECT * FROM Enrolled_Students es
1533	INNER JOIN Clubs_Offered_By_Schools c ON c.school_email = es.school_email
1534	WHERE es.ssn = @ssn_in AND c.name = @club_name_in)
1535		RAISERROR ('Your school does not offer this club.' , 15 , 15)
1536	ELSE
1537		INSERT INTO Clubs_Joined_By_Enrolled_Student
1538		(club_name,enrolled_ssn)
1539		VALUES (@club_name_in,@ssn_in)
1540GO
1541
1542--yaaFaragEllaaaah
1543CREATE PROC Enrolled_Student_Course_Search
1544@ssn_in INT,
1545@course_code_in VARCHAR(10),
1546@course_name_in VARCHAR(50)
1547AS
1548	IF @ssn_in IS NULL OR (@course_code_in IS NULL AND @course_name_in IS NULL)
1549		PRINT 'Incomplete input!'
1550	ELSE
1551		SELECT *
1552		FROM Attends a INNER JOIN Courses c on a.course_code = c.code
1553		WHERE @course_name_in IS NOT NULL AND a.enrolled_ssn = @ssn_in AND c.name = @course_name_in
1554			UNION
1555		SELECT *
1556		FROM Attends a INNER JOIN Courses c on a.course_code = c.code
1557		WHERE @course_code_in IS NOT NULL AND a.enrolled_ssn = @ssn_in AND a.course_code = @course_code_in
1558GO
1559
1560--heyya	name = ISNULL(@name_in , name) tenfa3? 2aw fee 7aga zayyaha
1561------------------------------------------------------------------------
1562
1563
1564--TEACHERRRRRRRRRRRRRR
1565CREATE PROC Teacher_Sign_Up
1566@fname_in VARCHAR(25),
1567@mname_in VARCHAR(25),
1568@lname_in VARCHAR(25),
1569@birthdate_in DATE,
1570@address_in VARCHAR(100),
1571@email_in VARCHAR(50),
1572@gender_in BIT
1573AS
1574	IF @fname_in = NULL
1575	OR @mname_in = NULL
1576	OR @lname_in = NULL
1577	OR @birthdate_in = NULL
1578	OR @address_in = NULL
1579	OR @email_in = NULL
1580	OR @gender_in = NULL
1581		PRINT 'Missing Info'
1582	ELSE
1583		INSERT INTO Employees
1584		(first_name , middle_name , last_name , birth_date , employee_address , email , gender , is_teacher)
1585		VALUES (@fname_in , @mname_in , @lname_in , @birthdate_in , @address_in , @email_in , @gender_in , '1')
1586GO
1587--2
1588CREATE PROC Teacher_View_His_Courses
1589@email_in VARCHAR(50)
1590AS
1591	IF @email_in is NULL
1592		PRINT 'Please enter email!'
1593	ELSE
1594		SELECT c.code , c.name , c.level_of_school , c.course_description
1595		FROM Teaches t
1596		INNER JOIN Courses c ON c.code = t.course_code
1597		WHERE t.teacher_email = @email_in
1598
1599GO
1600--3
1601CREATE PROC Post_Assignments
1602	@assignment_number INT,
1603	@course_code VARCHAR(10),
1604	@teacher_email VARCHAR(50),
1605	@posting_date DATE,
1606	@due_date DATE,
1607	@content VARCHAR(500)
1608AS
1609	IF 
1610		@assignment_number is NULL OR
1611		@course_code is NULL OR
1612		@teacher_email is NULL OR
1613		@posting_date is NULL OR
1614		@due_date is NULL OR
1615		@content is NULL
1616		PRINT 'some data is null'
1617	ELSE
1618		INSERT INTO Assignments
1619		VALUES(@assignment_number,
1620		@course_code,
1621		@teacher_email,
1622		@posting_date,
1623		@due_date,
1624		@content )
1625GO
1626--4
1627CREATE PROC Students_Solutions
1628	@email_in VARCHAR(50),
1629	@course_code INT
1630AS
1631	SELECT a.ssn, a.solution
1632	FROM Assignments_Solved_By_Students a
1633	WHERE a.teacher_email = @email_in AND a.course_code = @course_code
1634	ORDER BY a.ssn
1635GO
1636--5
1637CREATE PROC Grade_Assignments
1638	@teacher_email VARCHAR(50),
1639	@enrolled_ssn INT,
1640	@course_code VARCHAR(10),
1641	@grade FLOAT
1642AS
1643	UPDATE Assignments_Solved_By_Students
1644	SET grade = @grade 
1645	WHERE teacher_email = @teacher_email AND course_code = @course_code AND ssn = @enrolled_ssn
1646GO
1647--6
1648CREATE PROC Delete_Assignments
1649	@teacher_email VARCHAR(50),
1650	@course_code VARCHAR(10),
1651	@assignment_number InT
1652AS
1653DELETE FROM Assignments
1654WHERE teacher_email = @teacher_email AND course_code = @course_code AND assignment_number = @assignment_number
1655GO
1656
1657--7
1658CREATE PROC Write_Report
1659	@teacher_email VARCHAR(50),
1660	@enrolled_ssn INT,
1661	@comment VARCHAR(200)
1662AS
1663	UPDATE Monthly_Reports
1664	SET comment = @comment , issue_date = CAST(GETDATE() AS DATE)
1665	WHERE writer = @teacher_email AND enrolled_ssn = @enrolled_ssn
1666GO
1667--8
1668create PROC View_Questions
1669@teacher_email VARCHAR(50),
1670@course_code VARCHAR(10)
1671AS
1672SELECT q.question_number, e.name, q.question_itself
1673FROM Questions q
1674	INNER JOIN Enrolled_Students e ON q.askers_ssn = e.ssn
1675	INNER JOIN teaches t ON t.course_code = q.course_code
1676	WHERE q.course_code = @course_code AND t.teacher_email = @teacher_email
1677GO
1678--9
1679CREATE PROC Answer_Questions
1680@teacher_email VARCHAR(50),
1681@course_code VARCHAR(10),
1682@answer VARCHAR(500),
1683@number int
1684AS
1685	UPDATE Questions
1686	SET question_answer = @answer
1687	WHERE question_number = @number
1688GO
1689--10
1690CREATE PROC ListStudents
1691	@teacher_email VARCHAR(50)
1692AS
1693	IF @teacher_email IS NULL
1694		PRINT 'Null Input'
1695	ELSE
1696		SELECT e.*
1697		FROM Enrolled_Students e
1698				INNER JOIN Attends a on e.ssn = a.enrolled_ssn
1699				INNER JOIN Teaches t on t.course_code = a.course_code
1700				WHERE t.teacher_email = @teacher_email
1701GO
1702--11
1703CREATE PROC StudentsWithoutActivities
1704AS
1705	SELECT ssn
1706	FROM Enrolled_Students
1707	WHERE ssn NOT IN(
1708		SELECT e.ssn
1709		FROM Enrolled_Students e
1710		INNER JOIN Activities_Applied_By_Enrolled_Students a on e.ssn = a.ssn)
1711GO
1712--12
1713CREATE PROC StudentWithMostClubs
1714AS
1715	DECLARE @maxC INT
1716	SELECT @maxC = COUNT(*)
1717	FROM Enrolled_Students e INNER JOIN Clubs_Joined_By_Enrolled_Student c on e.ssn = c.enrolled_ssn
1718	GROUP BY e.ssn, e.name	
1719	SELECT e.name
1720	FROM Enrolled_Students e INNER JOIN Clubs_Joined_By_Enrolled_Student c on e.ssn = c.enrolled_ssn
1721
1722GO
1723
1724
1725--working insertions!!
1726
1727
1728
1729
1730
1731
1732
1733
1734INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1735VALUES('ST.Fatima@edu.eg',0,1,'02479106910','Heliopolice','ST.Fatima',1,1,0);
1736INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1737VALUES('ST.George@edu.eg',1,0,'02967910610','Shubra','ST.George',1,0,0);
1738INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1739VALUES('ELNozha@edu.eg',1,0,'021448323','Hegaz','EL Nozha',0,0,1);
1740INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1741VALUES('NewRamces@edu.eg',1,0,'0248825101','Heliopolice','New Ramces',0,0,1);
1742INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1743VALUES('Future@edu.eg',1,0,'029855952','Ramsees','Future',0,1,0);
1744INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1745VALUES('Karma@edu.eg',1,1,'029943119','5th settlement','Karma',1,1,1);
1746INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1747VALUES('Modern@edu.eg',0,1,'0223418103','Shubra','Modern',1,1,1);
1748INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1749VALUES('MSA@edu.eg',1,0,'027786922','Heliopolice','MSA',0,1,1);
1750INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1751VALUES('Rajak@edu.eg',0,1,'0210576831','Heliopolice','Rajak',0,0,1);
1752INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1753VALUES('ST.John@edu.eg',0,1,'0251610184','Shubra','St.John',1,0,1);
1754INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1755VALUES('Elite@edu.eg',1,0,'029923627','Heliopolice','Elite',0,0,1);
1756INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1757VALUES('Tabre@edu.eg',1,0,'022238343','5th settlement','Tabre',1,1,0);
1758INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1759VALUES('Sacrecoeur@edu.eg',1,1,'0210237531','Heliopolice','Sacre coeur',1,0,0);
1760INSERT INTO Schools (email,is_national,is_international,phone_number,school_address,name,is_elementary,is_middle,is_high)
1761VALUES('Shwyfat@edu.eg',1,0,'0251313104','Shubra','Shwyfat',0,1,0);
1762GO
1763
1764
1765
1766--SELECT * FROM Schools
1767---------------------------------
1768INSERT INTO Elementary_Supplies(supply, email)
1769VALUES('supplies', 'Sacrecoeur@edu.eg');
1770INSERT INTO Elementary_Supplies(supply, email)
1771VALUES('some_fries', 'Sacrecoeur@edu.eg');
1772INSERT INTO Elementary_Supplies(supply, email)
1773VALUES('some_fries', 'Tabre@edu.eg');
1774INSERT INTO Elementary_Supplies(supply, email)
1775VALUES('sunrise', 'Karma@edu.eg');
1776INSERT INTO Elementary_Supplies(supply, email)
1777VALUES('all_rise', 'ST.Fatima@edu.eg');
1778GO
1779---------------------------------
1780INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
1781VALUES('maged.awad@MSA.edu.eg','maged','awad','maged.awad','mego')
1782INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
1783VALUES('sherif.hanna@ST.Fatima.edu.eg','sherif','hanna','sherif.hanna','shico')
1784INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
1785VALUES('slim.abdel@ST.John.edu.eg','slim','abdel','slim.abdel','ballabizo')
1786INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
1787VALUES('aury.abdo@Shwyfat.edu.eg','aury','abdo','aury.abdo','ooory')
1788INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
1789VALUES('mira.ibrahim@Tabre.edu.eg','mira','ibrahim','mira.ibrahim','miiiraaa')
1790INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
1791VALUES('william.kaizar@ST.Fatima.edu.eg','william','kaizar','william.kaizar','wilo')
1792INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
1793VALUES('guirguis.samir@ST.George.edu.eg','guirguis','samir','guirguis.samir','gogo')
1794INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
1795VALUES('caren.miky@ST.Fatima.edu.eg','caren','miky','caren.miky','koko')
1796INSERT INTO Parents (email,first_name,last_name,parent_user_name ,parent_password)
1797VALUES('shady.azoz@MSA.edu.eg','shady','azoz','shady.azoz','shodz')
1798GO
1799
1800
1801
1802
1803--EXEC teachersOfChildren 'shady.azoz@MSA.edu.eg'
1804
1805
1806
1807--SELECT * FROM Applicants_Applies_Schools a WHERE a.parent_email = 'william.kaizar@ST.Fatima.edu.eg' AND a.accepted = '1'
1808--GO
1809-----------------------------------------
1810INSERT INTO Parents_Mobiles(mobile , email)
1811VALUES(012,'maged.awad@MSA.edu.eg');
1812INSERT INTO Parents_Mobiles(mobile , email)
1813VALUES(011,'aury.abdo@Shwyfat.edu.eg');
1814INSERT INTO Parents_Mobiles(mobile , email)
1815VALUES(010,'william.kaizar@ST.Fatima.edu.eg');
1816INSERT INTO Parents_Mobiles(mobile , email)
1817VALUES(0122,'shady.azoz@MSA.edu.eg');
1818GO
1819-----------------------------------------
1820INSERT INTO Applicants(ssn,name,parent_email)
1821VALUES(1234,'david','maged.awad@MSA.edu.eg') 
1822INSERT INTO Applicants(ssn,name,parent_email)
1823VALUES(12345,'peter','sherif.hanna@ST.Fatima.edu.eg') 
1824INSERT INTO Applicants(ssn,name,parent_email)
1825VALUES(12346,'david','slim.abdel@ST.John.edu.eg') 
1826INSERT INTO Applicants(ssn,name,parent_email)
1827VALUES(12347,'mina','william.kaizar@ST.Fatima.edu.eg') 
1828INSERT INTO Applicants(ssn,name,parent_email)
1829VALUES(12348,'fadya','shady.azoz@MSA.edu.eg') 
1830INSERT INTO Applicants(ssn,name,parent_email)
1831VALUES(12349,'sandra','guirguis.samir@ST.George.edu.eg') 
1832INSERT INTO Applicants(ssn,name,parent_email)
1833VALUES(12342,'michael','aury.abdo@Shwyfat.edu.eg')
1834GO
1835----------------------------------------------
1836INSERT INTO Courses(code,name)
1837VALUES('CSEN501','databases')
1838INSERT INTO Courses(code,name)
1839VALUES('CSEN502','networks')
1840INSERT INTO Courses(code,name)
1841VALUES('CSEN503','theory')
1842INSERT INTO Courses(code,name)
1843VALUES('MATH501','math')
1844INSERT INTO Courses(code,name)
1845VALUES('CSEN601','dsd')
1846GO
1847-----------------------------------------------
1848INSERT INTO Employees (email , employee_user_name , employee_password)
1849VALUES ('aa' , 'aa' , 'aa')
1850INSERT INTO Employees (email , employee_user_name , employee_password , is_teacher , is_administrator)
1851VALUES ('bb' , 'bb' , 'bb' , 1 , 1)
1852INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,salary,enrollment_year ,school_email, is_administrator,is_teacher,is_supervisor ,supervisor_email )
1853VALUES('ramy.mahfouz@ST.Fatima.edu.eg','ramy.mahfouz','rooooroooo','ramy','mahfouz','hanna',10000,'1996-1-1','ST.Fatima@edu.eg',0,0,1,null)
1854INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,salary,enrollment_year ,school_email, is_administrator,is_teacher,is_supervisor ,supervisor_email )
1855VALUES('ashraf.hassan@ST.Fatima.edu.eg','ashraf.hassan','hoooosoooo','ashraf','hassan','ismail',5000,'2006-2-2','ST.John@edu.eg',1,0,0,'ramy.mahfouz@ST.Fatima.edu.eg')
1856INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,salary,enrollment_year ,school_email, is_administrator,is_teacher,is_supervisor ,supervisor_email )
1857VALUES('kero.adel@ST.Fatima.edu.eg','kero.adel','kooookoooo','kero','adel','fahmy',5000,'2006-3-4','ST.Fatima@edu.eg',1,1,0,'ramy.mahfouz@ST.Fatima.edu.eg')
1858INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,salary,enrollment_year ,school_email, is_administrator,is_teacher,is_supervisor ,supervisor_email )
1859VALUES('george.krahy@ST.Fatima.edu.eg','george.krahy','goooogoooo','george','krahy','kareem','5000','2006-5-6','ST.Fatima@edu.eg',1,0,0,'ramy.mahfouz@ST.Fatima.edu.eg')
1860INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,salary,enrollment_year ,school_email, is_administrator,is_teacher,is_supervisor ,supervisor_email )
1861VALUES('michael.sherif@ST.Fatima.edu.eg','michael.sherif','moooomoooo','michael','sherif','hanna',10000,'1999-7-8','ST.Fatima@edu.eg',0,1,0,null)
1862INSERT INTO Employees(email,employee_user_name,employee_password,first_name,middle_name,last_name,salary,enrollment_year ,school_email, is_administrator,is_teacher,is_supervisor ,supervisor_email )
1863VALUES('teshtar@testar.com','teshtar.teshtar','teshhhhhh','teshtar','teshtaren','talata',10000,'1999-7-9','Tabre@edu.eg',0,1,0,null)
1864GO
1865
1866--EXEC Teacher_View_His_Courses 'michael.sherif@ST.Fatima.edu.eg'
1867
1868----------------------------------------------------------------------
1869--EXEC ViewUnverifiedTeachers 'kero.adel@ST.Fatima.edu.eg'
1870
1871--EXEC Enrolled_Student_View_Courses 2355124;
1872
1873--EXEC ViewUnverifiedTeachers 'ashraf.hassan@ST.Fatima.edu.eg'
1874
1875--EXEC Enrolled_Students_Views_Assignments_Grades 2345124
1876
1877INSERT INTO Enrolled_Students --school_email , parent_email
1878(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
1879VALUES (2345124,5,'7amada',0,'1995-1-2','3ayezArawwa7','Hamada','ST.Fatima@edu.eg','maged.awad@MSA.edu.eg')
1880INSERT INTO Enrolled_Students
1881(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
1882VALUES (2355124,5,'7amaden',0,'1995-2-2','3ayezArawwa7en','Hamaden','ST.Fatima@edu.eg','maged.awad@MSA.edu.eg') --e5wat
1883INSERT INTO Enrolled_Students --school_email , parent_email
1884(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
1885VALUES (2115124,5,'Samy',0,'1995-3-2','Mesh3ayezArawwa7','Samy','Rajak@edu.eg','slim.abdel@ST.John.edu.eg')
1886INSERT INTO Enrolled_Students --school_email , parent_email
1887(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
1888VALUES (2113524,5,'7ommos',0,'1996-3-2','Rawwa7t','Hommos','Shwyfat@edu.eg','shady.azoz@MSA.edu.eg')
1889INSERT INTO Enrolled_Students --school_email , parent_email
1890(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
1891VALUES (2115554,5,'7ommoseyya',0,'1996-4-2','Merwa7','Hommoseyya','Modern@edu.eg','shady.azoz@MSA.edu.eg')
1892GO
1893INSERT INTO Enrolled_Students
1894(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
1895VALUES (2115,5,'brens',0,'1996-4-2','bn','bns','ST.Fatima@edu.eg','shady.azoz@MSA.edu.eg')
1896INSERT INTO Enrolled_Students --school_email , parent_email
1897(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
1898VALUES (21,5,'brens2',0,'1995-3-2','b2','b2','ST.Fatima@edu.eg','slim.abdel@ST.John.edu.eg')
1899------------------------------------------------
1900INSERT INTO Courses_Prequisites
1901(course_code_1,course_code_2)
1902VALUES ('CSEN501','CSEN502')
1903GO
1904-------------------------------------------------
1905INSERT INTO Courses_Offered_By_Schools
1906(course_code , school_email)
1907VALUES ('CSEN601','ELNozha@edu.eg')
1908INSERT INTO Courses_Offered_By_Schools
1909(course_code , school_email)
1910VALUES ('CSEN601','ST.Fatima@edu.eg')
1911INSERT INTO Courses_Offered_By_Schools
1912(course_code , school_email)
1913VALUES ('MATH501','ST.Fatima@edu.eg')
1914GO
1915------------------------------------------------------------
1916INSERT INTO Activities --administrator_email , teacher_email , school_email
1917(activity_date,activity_location,activity_type,activity_description,administrator_email,teacher_email,school_email)
1918VALUES('2012-12-12','fel gam3a','trip','howwa keda','michael.sherif@ST.Fatima.edu.eg','kero.adel@ST.Fatima.edu.eg','ST.Fatima@edu.eg')
1919INSERT INTO Activities
1920(activity_date,activity_location,activity_type,activity_description,administrator_email,teacher_email,school_email)
1921VALUES('2012-11-11','barra el gam3a','trip','enter deskripshan heer','michael.sherif@ST.Fatima.edu.eg','george.krahy@ST.Fatima.edu.eg','ST.Fatima@edu.eg')
1922INSERT INTO Activities
1923(activity_date,activity_location,activity_type,activity_description,administrator_email,teacher_email,school_email)
1924VALUES('2012-10-10','ganb el gam3a','trip','kaman re7la wmalo','michael.sherif@ST.Fatima.edu.eg','kero.adel@ST.Fatima.edu.eg','ST.Fatima@edu.eg')
1925GO
1926-----------------------------------------------------------------------------
1927INSERT INTO Equipment --activity_date activity_location men Activities
1928(activity_date,activity_location,equipment)
1929VALUES ('2012-12-12','fel gam3a','3asaya')
1930INSERT INTO Equipment
1931(activity_date,activity_location,equipment)
1932VALUES ('2012-11-11','barra el gam3a','kora')
1933INSERT INTO Equipment --activity_date activity_location men Activities
1934(activity_date,activity_location,equipment)
1935VALUES ('2012-12-12','fel gam3a','gardal')
1936INSERT INTO Equipment --activity_date activity_location men Activities
1937(activity_date,activity_location,equipment)
1938VALUES ('2012-11-11','barra el gam3a','7anafeyya')
1939INSERT INTO Equipment --activity_date activity_location men Activities
1940(activity_date,activity_location,equipment)
1941VALUES ('2012-10-10','ganb el gam3a','mayya ma3daneyya')
1942INSERT INTO Equipment --activity_date activity_location men Activities
1943(activity_date,activity_location,equipment)
1944VALUES ('2012-10-10','ganb el gam3a','far5a zengeyya')
1945GO
1946----------------------------------------------------------
1947INSERT INTO Clubs
1948(name , purpose) VALUES ('MUNday','unknown')
1949INSERT INTO Clubs
1950(name , purpose) VALUES ('TImon','play mohaa')
1951INSERT INTO Clubs
1952(name , purpose) VALUES ('IEEEEEEEEE ya rasy','eat circuits for breakfast!')
1953INSERT INTO Clubs
1954(name , purpose) VALUES ('3ashan 5atrik ya gam3ety','ne3mel 7aga')
1955GO
1956------------------------------------------------------------
1957INSERT INTO Clubs_Offered_By_Schools
1958(name , school_email)
1959VALUES ('MUNday','ST.John@edu.eg')
1960INSERT INTO Clubs_Offered_By_Schools
1961(name , school_email)
1962VALUES ('TImon','Tabre@edu.eg')
1963INSERT INTO Clubs_Offered_By_Schools
1964(name , school_email)
1965VALUES ('TImon','Shwyfat@edu.eg')
1966INSERT INTO Clubs_Offered_By_Schools
1967(name , school_email)
1968VALUES ('3ashan 5atrik ya gam3ety','Tabre@edu.eg')
1969INSERT INTO Clubs_Offered_By_Schools
1970(name , school_email)
1971VALUES ('IEEEEEEEEE ya rasy','Shwyfat@edu.eg')
1972
1973INSERT INTO Clubs_Offered_By_Schools
1974(name , school_email)
1975VALUES ('IEEEEEEEEE ya rasy','Modern@edu.eg')
1976INSERT INTO Clubs_Offered_By_Schools
1977(name , school_email)
1978VALUES ('IEEEEEEEEE ya rasy','Rajak@edu.eg')
1979GO
1980----------------------------------------------------------------
1981INSERT INTO Questions --course_code , askers_ssn
1982(course_code,question_itself,askers_ssn)
1983VALUES ('CSEN601','E7na leh bna5od el course dah?',2345124 )
1984INSERT INTO Questions
1985(course_code,question_itself,askers_ssn)
1986VALUES ('CSEN601','Ya3ny eh insert?',2355124 )
1987INSERT INTO Questions
1988(course_code,question_itself,askers_ssn)
1989VALUES ('CSEN601','Ya3ny eh delete?',2115124 )
1990INSERT INTO Questions
1991(course_code,question_itself,askers_ssn)
1992VALUES ('MATH501','Ya3ny eh logical sequence?',2345124 )
1993GO
1994--------------------------------------------------------
1995INSERT INTO Assignments --course_code , teacher_email
1996(assignment_number,course_code,teacher_email,posting_date,due_date,content)
1997VALUES (4,'CSEN601','kero.adel@ST.Fatima.edu.eg','2012-3-18','2012-3-19','e3mel database')
1998INSERT INTO Assignments
1999(assignment_number,course_code,teacher_email,posting_date,due_date,content)
2000VALUES (9,'CSEN601','kero.adel@ST.Fatima.edu.eg','2012-4-18','2012-4-19','e3mel social network')
2001INSERT INTO Assignments
2002(assignment_number,course_code,teacher_email,posting_date,due_date,content)
2003VALUES (900,'MATH501','michael.sherif@ST.Fatima.edu.eg','2012-5-18','2012-5-19','proove that the implication aslaha erd')
2004GO
2005------------------------------------------------------------------------
2006INSERT INTO Announcements --administrator_email men Employees , school_email
2007(administrator_email , announcement_date , announcement_time , announcement_type , title , announcement_description , school_email)
2008VALUES ('ashraf.hassan@ST.Fatima.edu.eg','2010-3-3','10:10','trip','el trip','hanroo7 henak','MSA@edu.eg')
2009INSERT INTO Announcements
2010(administrator_email , announcement_date , announcement_time , announcement_type , title , announcement_description , school_email)
2011VALUES ('george.krahy@ST.Fatima.edu.eg','2010-3-4','10:10','trip','trip tanya','hanroo7 henak bardo','Future@edu.eg')
2012INSERT INTO Announcements
2013(administrator_email , announcement_date , announcement_time , announcement_type , title , announcement_description , school_email)
2014VALUES ('kero.adel@ST.Fatima.edu.eg','2010-3-5','10:10','trip talta','fee akl','hanroo7 henak tany','Karma@edu.eg')
2015INSERT INTO Announcements
2016(administrator_email , announcement_date , announcement_time , announcement_type , title , announcement_description , school_email)
2017VALUES ('george.krahy@ST.Fatima.edu.eg','2016-11-28','11:10','trip taltaNEW','fee akl','hanroo7 henak tany','ST.Fatima@edu.eg') --change date to be visible
2018GO
2019---
2020INSERT INTO Announcements_Visible_To_Enrolled_Students
2021(administrator_email , announcement_date , announcement_time , enrolled_ssn)
2022VALUES ('ashraf.hassan@ST.Fatima.edu.eg','03-Mar-10','10:10:00',2355124)
2023INSERT INTO Announcements_Visible_To_Enrolled_Students
2024(administrator_email , announcement_date , announcement_time , enrolled_ssn)
2025VALUES ('george.krahy@ST.Fatima.edu.eg','04-Mar-10','10:10:00',2345124)
2026INSERT INTO Announcements_Visible_To_Enrolled_Students
2027(administrator_email , announcement_date , announcement_time , enrolled_ssn)
2028VALUES ('george.krahy@ST.Fatima.edu.eg','2016-11-28','11:10:00',2345124) --change date to be visible
2029GO
2030--SELECT * FROM Announcements
2031---------------------------------------------------------------------------
2032INSERT INTO Monthly_Reports --enrolled_ssn men Enrolled_student , writer men Employees
2033(enrolled_ssn , issue_date , comment , writer)
2034VALUES (2113524 , '2011-12-28' , 'good student' , 'michael.sherif@ST.Fatima.edu.eg')
2035INSERT INTO Monthly_Reports
2036(enrolled_ssn , issue_date , comment , writer)
2037VALUES (2115124 , '2011-12-18' , 'good student' , 'michael.sherif@ST.Fatima.edu.eg')
2038INSERT INTO Monthly_Reports
2039(enrolled_ssn , issue_date , comment , writer)
2040VALUES (2355124 , '2012-4-28' , 'bad student' , 'michael.sherif@ST.Fatima.edu.eg')
2041GO
2042--------------------------------------------------------------
2043INSERT INTO Teaches
2044(teacher_email , course_code)
2045VALUES ('kero.adel@ST.Fatima.edu.eg','CSEN503')
2046INSERT INTO Teaches
2047(teacher_email , course_code)
2048VALUES ('kero.adel@ST.Fatima.edu.eg','CSEN601')
2049INSERT INTO Teaches
2050(teacher_email , course_code)
2051VALUES ('michael.sherif@ST.Fatima.edu.eg','MATH501')
2052GO
2053
2054------------------------------------------------
2055INSERT INTO Attends
2056(enrolled_ssn , course_code)
2057VALUES (2355124,'MATH501')
2058INSERT INTO Attends
2059(enrolled_ssn , course_code)
2060VALUES (2355124,'CSEN601')
2061INSERT INTO Attends
2062(enrolled_ssn , course_code)
2063VALUES (2115124,'MATH501')
2064INSERT INTO Attends
2065(enrolled_ssn , course_code)
2066VALUES (2115554,'MATH501')
2067INSERT INTO Attends
2068(enrolled_ssn , course_code)
2069VALUES (2115554,'CSEN601')
2070INSERT INTO Attends
2071(enrolled_ssn , course_code)
2072VALUES (2113524,'MATH501')
2073GO
2074------------------------------------------------------
2075INSERT INTO Clubs_Joined_By_Enrolled_Student
2076(club_name , enrolled_ssn)
2077VALUES ('IEEEEEEEEE ya rasy' , 2115124)
2078INSERT INTO Clubs_Joined_By_Enrolled_Student
2079(club_name , enrolled_ssn)
2080VALUES ('3ashan 5atrik ya gam3ety' , 2115124)
2081INSERT INTO Clubs_Joined_By_Enrolled_Student
2082(club_name , enrolled_ssn)
2083VALUES ('3ashan 5atrik ya gam3ety' , 2345124)
2084INSERT INTO Clubs_Joined_By_Enrolled_Student
2085(club_name , enrolled_ssn)
2086VALUES ('MUNday' , 2345124)
2087INSERT INTO Clubs_Joined_By_Enrolled_Student
2088(club_name , enrolled_ssn)
2089VALUES ('TImon' , 2345124)
2090GO
2091
2092
2093INSERT INTO Monthly_Reports_Replies
2094(enrolled_ssn , issue_date , parent_email , reply)
2095VALUES (2113524 , '28-Dec-11' , 'mira.ibrahim@Tabre.edu.eg' , 'thank you veery mutsh')
2096GO
2097
2098
2099INSERT INTO Enrolled_Students_View_Questions
2100(ssn , question_number , course_code)
2101VALUES (2355124 , 2 , 'CSEN601')
2102GO
2103
2104INSERT INTO Teachers_Response_To_Questions
2105(question_number , course_code , teacher_email)
2106VALUES (1 , 'CSEN601' , 'teshtar@testar.com')
2107GO
2108
2109INSERT INTO Parents_Ratings_To_Teachers
2110(employee_email , parent_email , rating)
2111VALUES ('michael.sherif@ST.Fatima.edu.eg', 'shady.azoz@MSA.edu.eg', 2)
2112INSERT INTO Parents_Ratings_To_Teachers
2113(employee_email , parent_email , rating)
2114VALUES ('michael.sherif@ST.Fatima.edu.eg', 'maged.awad@MSA.edu.eg', 2)
2115INSERT INTO Parents_Ratings_To_Teachers
2116(employee_email , parent_email , rating)
2117VALUES ('michael.sherif@ST.Fatima.edu.eg', 'slim.abdel@ST.John.edu.eg', 1)
2118
2119INSERT INTO Announcements_Visible_To_Parents
2120(administrator_email , announcement_date , announcement_time , parent_email)
2121VALUES ('ashraf.hassan@ST.Fatima.edu.eg','2010-3-3','10:10', 'shady.azoz@MSA.edu.eg')
2122GO
2123
2124INSERT INTO View_And_Grade_Assignments
2125(viewer_email , assignment_number , course_code , creator_email)
2126VALUES ('teshtar@testar.com' , 9 , 'CSEN601' , 'kero.adel@ST.Fatima.edu.eg')
2127GO
2128
2129INSERT INTO Assignments_Solved_By_Students
2130(ssn , assignment_number , teacher_email , course_code , solution)
2131VALUES (2345124 , 9 , 'kero.adel@ST.Fatima.edu.eg' , 'CSEN601' , 'bottom implies top')
2132GO
2133
2134INSERT INTO Activities_Applied_By_Enrolled_Students
2135(activity_date , activity_location , ssn)
2136VALUES ('2012-12-12' , 'fel gam3a' , 2115124)
2137GO
2138
2139INSERT INTO Courses_Offered_By_Schools
2140(course_code , school_email)
2141VALUES ('MATH501','Shwyfat@edu.eg')
2142GO
2143
2144INSERT INTO Applicants_Applies_Schools
2145(school_email , child_ssn , accepted , parent_email)
2146VALUES ('Shwyfat@edu.eg' , 12345 , 0 , 'sherif.hanna@ST.Fatima.edu.eg')
2147GO
2148
2149
2150
2151
2152
2153
2154--proc executions
2155/*
2156
2157
2158--@ssn_in INT,
2159--@name_in VARCHAR(50),
2160--@gender_in BIT,
2161--@birth_date_in DATE,
2162--@password_in VARCHAR(20),
2163--@school_email_in VARCHAR(50),
2164--@parent_email_in VARCHAR(50)
2165
2166
2167SELECT * FROM Enrolled_Students
2168EXEC Enrolled_Student_Info_Update  2113524 , 'Defet' , '0' , '1995-2-2' , 'mafeeshpasswerd' , 'Rajak@edu.eg' , 'shady.azoz@MSA.edu.eg'
2169
2170EXEC Enrolled_Student_View_Courses 2345124
2171EXEC Enrolled_Student_View_Courses 2355124
2172
2173EXEC Enrolled_Student_Post_Question 'MATH501' , '3ashan se2et?' , 2355124
2174SELECT * FROM Questions
2175
2176EXEC Enrolled_Student_View_Questions 'MATH501' , 2355124
2177
2178EXEC Enrolled_Student_View_Questions 'CSEN601' , 2355124
2179
2180--proc 4 far2 el "e"
2181
2182EXEC Enrolled_Student_View_All_Assignments 2355124
2183SELECT * FROM Assignments
2184
2185EXEC Enrolled_Student_Solves_Assignment 2355124 , 4 , 'kero.adel@ST.Fatima.edu.eg' , 'CSEN601' , 'boss enta 5od el quiz 7otto fel 5allast yetla3lak social network agmad men facebook'
2186SELECT * FROM Assignments_Solved_By_Students
2187
2188EXEC Enrolled_Students_Views_Assignments_Grades 2355124
2189
2190
2191PRINT DATEDIFF(DAY,'11-27-16','11-28-16')
2192
2193EXEC Enrolled_Student_Views_Announcements5 2345124
2194
2195SELECT * FROM Announcements
2196*/
2197
2198
2199
2200
2201
2202--USE dec3h
2203--GO
2204
2205
2206
2207
2208--PHP PROCEDURES
2209
2210CREATE PROC AdminLogIn
2211@unamein VARCHAR(25),
2212@passin VARCHAR(25)
2213AS
2214	SELECT *
2215	FROM Employees e
2216	WHERE e.employee_user_name = @unamein AND e.employee_password = @passin AND e.is_administrator = '1'
2217GO
2218
2219CREATE PROC StudentLogIn
2220@unamein VARCHAR(25),
2221@passin VARCHAR(25)
2222AS
2223	SELECT *
2224	FROM Enrolled_Students e
2225	WHERE e.student_user_name = @unamein AND e.student_password = @passin
2226GO
2227
2228CREATE PROC ParentLogIn
2229@unamein VARCHAR(25),
2230@passin VARCHAR(25)
2231AS
2232	SELECT *
2233	FROM Parents e
2234	WHERE e.parent_user_name = @unamein AND e.parent_password = @passin
2235GO
2236
2237
2238CREATE PROC TeacherLogIn
2239@unamein VARCHAR(25),
2240@passin VARCHAR(25)
2241AS
2242	SELECT *
2243	FROM Employees e
2244	WHERE e.employee_user_name = @unamein AND e.employee_password = @passin AND e.is_teacher = '1'
2245GO
2246
2247--USE dec3h
2248--GO
2249
2250--EXEC TeacherLogIn "bb" , "bb"
2251
2252CREATE PROC TeacherSignUp
2253@unamein VARCHAR(25),
2254@passin VARCHAR(25)
2255AS
2256	SELECT first_name , email
2257	FROM Employees e
2258	WHERE e.employee_user_name = @unamein AND e.employee_password = @passin AND e.is_administrator = '1'
2259GO
2260
2261--EXEC ParentLogIn "aa" , "aa"
2262
2263--USE dec3h
2264--GO
2265
2266--SELECT * FROM Enrolled_Students
2267
2268INSERT INTO Enrolled_Students
2269(ssn , student_user_name , student_password)
2270VALUES(12341234 , 'aa' , 'aa')
2271INSERT INTO Enrolled_Students --school_email , parent_email
2272(ssn,student_level,name,gender,birth_date,student_password,student_user_name,school_email,parent_email)
2273VALUES (211555,5,'7ommoseyya',0,'1996-4-2','bb','bb','Modern@edu.eg','shady.azoz@MSA.edu.eg')
2274GO
2275
2276--SELECT * FROM Employees
2277
2278--SELECT * FROM Parents
2279
2280--SELECT * FROM 
2281
2282--Teacher_Sign_Up
2283
2284--EXEC InsertParents "ASDF","asdf","asfd","ads","adsf","asdf","asdf"
2285
2286--EXEC InsertParents "aa","aa","aa","aa","aa","aa","aa"
2287
2288--EXEC Enrolled_Student_Info_Update 12341234 , 
2289
2290--InsertParents
2291
2292
2293/*CREATE PROC ParentSignUp
2294@unamein VARCHAR(25),
2295@passin VARCHAR(25),
2296@email_in VARCHAR(50),
2297@address_in VARCHAR(100),
2298@fname_in VARCHAR(25),
2299@lname_in VARCHAR(25),
2300@phone_in VARCHAR(20)
2301AS
2302	SELECT first_name , email
2303	FROM Employees e
2304	WHERE e.employee_user_name = @unamein AND e.employee_password = @passin AND e.is_administrator = '1'
2305GO*/
2306
2307--SELECT * FROM Employees
2308
2309--EXEC AdminLogIn "ashraf.hassan" , "hoooosoooo"
2310
2311
2312
2313--SELECT *
2314--FROM Employees e
2315--WHERE e.is_verified = '0' AND e.is_teacher = '1' 
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325CREATE PROC ES_View_Info
2326@ssn_in INT
2327AS
2328    SELECT *
2329    FROM Enrolled_Students es
2330    WHERE es.ssn = @ssn_in
2331GO
2332--THEN USE
2333--EXEC Enrolled_Student_Info_Update
2334/*
2335@ssn_in INT,
2336@name_in VARCHAR(50),
2337@gender_in BIT,
2338@birth_date_in DATE,
2339@password_in VARCHAR(20),
2340@school_email_in VARCHAR(50),
2341@parent_email_in VARCHAR(50)
2342*/
2343
2344
2345
2346
2347-- Enrolled_Student_View_Courses
2348--@ssn_in INT
2349
2350
2351--ok
2352
2353--ok
2354
2355--ok
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369--EXEC sp_who2
2370/*
2371USE master
2372GO
2373ALTER DATABASE dec3e
2374SET OFFLINE WITH ROLLBACK IMMEDIATE
2375GO
2376
2377DROP DATABASE dec3e*/
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388--mikel dec5
2389
2390CREATE PROC ViewSchools
2391@school_name VARCHAR(50),
2392@school_address VARCHAR(100),
2393@school_type bit
2394AS
2395	IF  @school_name IS NULL AND
2396		@school_address IS NULL AND
2397		@school_type IS NULL
2398		Print 'Null Input'
2399	ELSE
2400		if @school_name IS NOT NULL
2401		Begin
2402			SELECT *
2403			FROM Schools
2404			WHERE @school_name IS NOT NULL AND name = @school_name
2405			RETURN
2406		END
2407		if @school_address IS NOT NULL
2408		BEGIN
2409			SELECT *
2410			FROM Schools
2411			WHERE @school_address IS NOT NULL AND school_address = @school_address
2412			RETURN
2413		END
2414		if @school_type IS NOT NULL
2415		Begin
2416			SELECT *
2417			FROM Schools
2418			WHERE @school_type IS NOT NULL AND is_international = @school_type
2419			RETURN
2420		END
2421Go
2422
2423
2424--exec ViewSchools 'Elite', Null, 1
2425
2426CREATE PROC SchoolReviews
2427	@email VARCHAR(50)
2428AS
2429	SELECT *
2430	FROM Parents_Write_Review_Schools
2431	WHERE school_email = @email
2432GO
2433
2434CREATE PROC SchoolAnonucements
2435	@email VARCHAR(50)
2436AS
2437	SELECT *
2438	FROM dbo.Announcements
2439	WHERE school_email = @email
2440GO
2441CREATE FUNCTION GetStudentSSN(@username VARCHAR(20), @pass VARCHAR(20))
2442	RETURNS INT
2443BEGIN
2444	DECLARE @returnedValue INT
2445	SET @returnedValue = -1
2446		SELECT @returnedValue = ssn
2447		FROM Enrolled_Students
2448		WHERE student_user_name = @username AND student_password = @pass
2449	RETURN @returnedValue
2450END
2451GO
2452
2453CREATE FUNCTION GetParentEmail(@username VARCHAR(20), @pass VARCHAR(20))
2454	RETURNS VARCHAR(50)
2455BEGIN
2456	DECLARE @returnedValue VARCHAR(50)
2457	SET @returnedValue = ''
2458		SELECT @returnedValue = email
2459		FROM Parents
2460		WHERE parent_user_name = @username AND parent_password = @pass
2461	RETURN @returnedValue
2462END
2463GO
2464
2465CREATE FUNCTION GetEmployeeEmail(@username VARCHAR(20), @pass VARCHAR(20), @isAdmin BIT)
2466	RETURNS VARCHAR(50)
2467BEGIN
2468	DECLARE @returnedValue VARCHAR(50)
2469	SET @returnedValue = ''
2470		IF @isAdmin = 1
2471			SELECT @returnedValue = email
2472			FROM Employees
2473			WHERE employee_user_name = @username AND employee_password = @pass AND is_administrator = 1
2474		ELSE
2475			SELECT @returnedValue = email
2476			FROM Employees
2477			WHERE employee_user_name = @username AND employee_password = @pass AND is_administrator = 0
2478	RETURN @returnedValue
2479END
2480GO
2481
2482CREATE PROC ViewUnverifiedTeachers
2483@admin_email VARCHAR(50)
2484AS
2485	IF
2486		@admin_email IS NULL
2487		print 'Null Input'
2488	ELSE
2489		DECLARE @isAdmin BIT
2490		DECLARE @school_email VARCHAR(50)
2491		Select @isAdmin = is_administrator, @school_email = e.school_email
2492		FROM Employees e
2493		WHERE e.email = @admin_email
2494		IF @isAdmin = 1
2495			Select *
2496			FROM Employees e
2497			Where e.is_teacher = 1 AND e.school_email = @school_email AND employee_password IS NULL AND employee_user_name IS NULL
2498		ELSE
2499			PRINT 'Not Admin'
2500GO
2501
2502CREATE FUNCTION CheckAdminExists(@username VARCHAR(20), @pass VARCHAR(20), @email VARCHAR(50))
2503	RETURNS BIT
2504BEGIN
2505	DECLARE @returnedValue BIT
2506	SET @returnedValue = 0
2507		IF EXISTS
2508		(
2509			SELECT *
2510			FROM Employees
2511			WHERE employee_user_name = @username AND employee_password = @pass AND email = @email AND is_administrator = 1
2512		)
2513		SET @returnedValue = 1
2514	RETURN @returnedValue
2515END
2516GO
2517CREATE FUNCTION CheckTeacherExists(@username VARCHAR(20), @pass VARCHAR(20), @email VARCHAR(50))
2518	RETURNS BIT
2519BEGIN
2520	DECLARE @returnedValue BIT
2521	SET @returnedValue = 0
2522		IF EXISTS
2523		(
2524			SELECT *
2525			FROM Employees
2526			WHERE employee_user_name = @username AND employee_password = @pass AND email = @email AND is_teacher = 1
2527		)
2528		SET @returnedValue = 1
2529	RETURN @returnedValue
2530END
2531GO
2532CREATE FUNCTION CheckParentExists(@username VARCHAR(20), @pass VARCHAR(20), @email VARCHAR(50))
2533	RETURNS BIT
2534BEGIN
2535	DECLARE @returnedValue BIT
2536	SET @returnedValue = 0
2537		IF EXISTS
2538		(
2539			SELECT *
2540			FROM Parents
2541			WHERE parent_user_name = @username AND parent_password = @pass AND email = @email
2542		)
2543		SET @returnedValue = 1
2544	RETURN @returnedValue
2545END
2546GO
2547CREATE FUNCTION CheckStudentExists(@username VARCHAR(20), @pass VARCHAR(20), @ssn INT)
2548	RETURNS BIT
2549BEGIN
2550	DECLARE @returnedValue BIT
2551	SET @returnedValue = 0
2552		IF EXISTS
2553		(
2554			SELECT *
2555			FROM Enrolled_Students
2556			WHERE student_user_name = @username AND student_password = @pass AND ssn = @ssn
2557		)
2558		SET @returnedValue = 1
2559	RETURN @returnedValue
2560END
2561GO
2562
2563CREATE PROC ViewStudentApplications
2564@admin_email VARCHAR(50)
2565AS
2566	IF
2567		@admin_email IS NULL
2568		print 'Null Input'
2569	ELSE
2570		DECLARE @isAdmin BIT
2571		DECLARE @school_email VARCHAR(50)
2572		Select @isAdmin = is_administrator, @school_email = e.school_email
2573		FROM Employees e
2574		WHERE e.email = @admin_email
2575		IF @isAdmin = 1
2576			Select *
2577			FROM Applicants_Applies_Schools
2578			Where school_email = @school_email AND accepted = 0
2579		ELSE
2580			PRINT 'Not Admin'
2581GO
2582
2583
2584CREATE PROC ViewUnverifiedStudents
2585@admin_email VARCHAR(50)
2586AS
2587	IF
2588		@admin_email IS NULL
2589		print 'Null Input'
2590	ELSE
2591		DECLARE @isAdmin BIT
2592		DECLARE @school_email VARCHAR(50)
2593		Select @isAdmin = is_administrator, @school_email = e.school_email
2594		FROM Employees e
2595		WHERE e.email = @admin_email
2596		IF @isAdmin = 1
2597			Select *
2598			FROM Enrolled_Students
2599			Where school_email = @school_email AND student_password IS NULL AND student_user_name IS NULL
2600		ELSE
2601			PRINT 'Not Admin'
2602GO
2603
2604CREATE PROC ViewActivities
2605@admin_email VARCHAR(50)
2606AS
2607	IF
2608		@admin_email IS NULL
2609		print 'Null Input'
2610	ELSE
2611		DECLARE @isAdmin BIT
2612		DECLARE @school_email VARCHAR(50)
2613		Select @isAdmin = is_administrator, @school_email = e.school_email
2614		FROM Employees e
2615		WHERE e.email = @admin_email
2616		IF @isAdmin = 1
2617			Select *
2618			FROM Activities
2619			Where administrator_email = @admin_email
2620		ELSE
2621			PRINT 'Not Admin'
2622GO
2623
2624
2625CREATE PROC tstasdfa
2626@teacher_email VARCHAR(50)
2627AS
2628	SELECT *
2629	FROM Enrolled_Students e
2630	INNER JOIN Attends a ON (e.ssn = a.enrolled_ssn)
2631	WHERE a.course_code IN
2632		(Select course_code
2633		FROM Teaches
2634		Where teacher_email = @teacher_email)
2635GO
2636/*
2637CREATE TABLE Employees
2638(
2639	email VARCHAR(50) PRIMARY KEY,
2640	birth_date DATE,
2641	employee_user_name VARCHAR(25) UNIQUE,
2642	employee_password VARCHAR(25),
2643	first_name VARCHAR(25),
2644	middle_name VARCHAR(25),
2645	last_name VARCHAR(25),
2646	gender BIT,
2647	employee_address VARCHAR(100),
2648	salary FLOAT,
2649	enrollment_year DATE,
2650	years_of_experience as (year(current_timestamp) - year(enrollment_year)),
2651	is_administrator BIT NOT NULL DEFAULT '0',
2652	is_teacher BIT NOT NULL DEFAULT '0',
2653	is_supervisor BIT NOT NULL DEFAULT '0',
2654	is_verified BIT NOT NULL DEFAULT '0',
2655	school_email VARCHAR(50),
2656	supervisor_email VARCHAR(50),
2657*/
2658
2659--EXEC TeacherAvgRating 'michael.sherif@ST.Fatima.edu.eg'
2660
2661--heyya dih!
2662CREATE FUNCTION TAvgR(@t VARCHAR(50)) RETURNS FLOAT
2663AS BEGIN
2664	DECLARE @total FLOAT
2665	DECLARE @c FLOAT
2666	DECLARE @AvgRate FLOAT
2667	SELECT @total = SUM(pt.rating) , @c = count(pt.rating)
2668	FROM Parents_Ratings_To_Teachers pt
2669	WHERE @t = pt.employee_email
2670	SET @AvgRate = @total/@c
2671	RETURN @AvgRate
2672END
2673GO
2674
2675--SELECT * FROM Parents_Ratings_To_Teachers
2676
2677--SELECT dbo.TAvgR('michael.sherif@ST.Fatima.edu.eg')
2678
2679
2680--EXEC teachersOfChildren 'maged.awad@MSA.edu.eg'
2681
2682CREATE PROC teachersOfChildren
2683@parent_email VARCHAR(50)
2684AS
2685	SELECT es.first_name , es.middle_name , es.last_name , es.email , es.gender , es.years_of_experience , dbo.TAvgR(es.email)
2686	FROM Attends a
2687	INNER JOIN Teaches b ON b.course_code = a.course_code
2688	INNER JOIN Employees es ON b.teacher_email = es.email
2689	WHERE es.is_teacher = '1'
2690	AND EXISTS
2691	(
2692		SELECT *
2693		FROM Enrolled_Students ess
2694		WHERE ess.parent_email = @parent_email
2695		AND ess.ssn = a.enrolled_ssn
2696	)
2697GO
2698
2699CREATE PROC SchoolsOfChildren
2700@parent_email VARCHAR(50)
2701AS
2702	SELECT s.name , s.email
2703	FROM Schools s
2704	INNER JOIN Enrolled_Students es ON es.school_email = s.email
2705	AND EXISTS(
2706		SELECT *
2707		FROM Enrolled_Students ess
2708		WHERE ess.parent_email = @parent_email
2709		AND ess.ssn = es.ssn
2710	)
2711GO
2712
2713--EXEC SchoolsOfChildren 'shady.azoz@MSA.edu.eg'
2714
2715CREATE PROC aa
2716@parent_email_in VARCHAR(50)
2717AS
2718	SELECT *
2719	FROM Applicants_Applies_Schools a
2720	WHERE a.parent_email = @parent_email_in AND a.accepted = '1'
2721GO
2722
2723--EXEC aa 'maged.awad@MSA.edu.eg'
2724
2725
2726CREATE PROC ab
2727@ssn_in INT
2728AS
2729	SELECT name
2730	FROM Enrolled_Students
2731	WHERE ssn = @ssn_in
2732
2733	UNION
2734
2735	SELECT name
2736	FROM Applicants
2737	WHERE ssn = @ssn_in
2738GO
2739
2740
2741CREATE PROC ViewParentReviews
2742@parent_email_in VARCHAR(50)
2743AS
2744	SELECT school_email , reviews
2745	FROM Parents_Write_Review_Schools
2746	WHERE parent_email = @parent_email_in
2747GO
2748
2749--EXEC ViewParentReviews 'shady.azoz@MSA.edu.eg'
2750
2751
2752/*	parent_email VARCHAR(50),
2753	school_email VARCHAR(50),
2754	reviews VARCHAR(200),*/
2755
2756CREATE PROC deleteReview
2757@parent_email_in VARCHAR(50),
2758@school_email_in VARCHAR(50)
2759AS
2760	DELETE FROM Parents_Write_Review_Schools
2761	WHERE parent_email = @parent_email_in AND school_email = @school_email_in
2762GO
2763
2764--select * from Enrolled_Students where 2345124 = ssn
2765
2766
2767
2768--select * from Assignments where course_code = 'CSEN601'
2769
2770
2771--SELECT * FROM Schools WHERE email IN (SELECT school_email FROM Employees WHERE email = 'bb')
2772
2773
2774
2775CREATE PROC CheckHighSchool
2776@ssn_in INT
2777AS
2778	SELECT y.is_high
2779	FROM Enrolled_Students x
2780	INNER JOIN Schools y ON x.school_email = y.email AND x.ssn = 2345124
2781GO
2782
2783/*
2784SELECT *
2785FROM Enrolled_Students z
2786WHERE EXISTS
2787(
2788	SELECT y.is_high
2789	FROM Enrolled_Students x
2790	INNER JOIN Schools y ON x.school_email = y.email AND x.ssn = z.ssn
2791	WHERE y.is_high = '1'
2792)*/
2793
2794
2795
2796
2797--SELECT * FROM Clubs_Offered_By_Schools
2798--SELECT * FROM Enrolled_Students
2799
2800
2801
2802
2803
2804
2805
2806
2807
2808
2809
2810
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823--mo2akkatan
2824--USE dec3h
2825--GO
2826
2827
2828
2829--EXEC Enrolled_Student_Info_Update 12341234 , "lkjlk" , 0 , "2016-2-5" , "aa" , "Modern@edu.eg" , 'maged.awad@MSA.edu.eg'
2830
2831
2832--EXEC Enrolled_Student_Info_Update 12341234 , "aaq" , 1 , "2011-2-2" , "aa" , "Modern@edu.eg" , "maged.awad@MSA.edu.eg"
2833
2834
2835
2836--EXEC Enrolled_Student_View_Courses 
2837
2838
2839
2840
2841
2842CREATE PROC ES_View_School_Clubs
2843@ssn_in INT
2844AS
2845	SELECT c.*
2846	FROM Clubs c
2847	INNER JOIN Clubs_Offered_By_Schools co ON c.name = co.name
2848	WHERE EXISTS
2849	(
2850		SELECT *
2851		FROM Enrolled_Students ess
2852		WHERE ess.ssn = @ssn_in AND ess.school_email = co.school_email
2853	)
2854GO
2855
2856--EXEC ES_View_School_Clubs 2113524
2857
2858--EXEC Enrolled_Student_Join_Club 211555 , "TImon"
2859
2860--SELECT * FROM Clubs_Joined_By_Enrolled_Student
2861
2862--SELECT * FROM Enrolled_Students
2863
2864/*UPDATE Enrolled_Students
2865SET student_level = 10
2866WHERE student_level = 5*/
2867
2868
2869
2870--EXEC AdminLogIn "bb" , "bb"
2871
2872--EXEC AdminLogIn "teshtar.teshtar" , "teshhhhhh"
2873
2874--EXEC ViewUnverifiedTeachers "george.krahy@ST.Fatima.edu.eg"
2875
2876--SELECT * FROM Employees
2877--GO
2878
2879CREATE PROC AdminVerifiesTeacher
2880@teacher_mail_in VARCHAR(50)
2881AS
2882	UPDATE Employees
2883	SET is_verified = '1'
2884	WHERE email = @teacher_mail_in
2885GO
2886
2887--EXEC AdminVerifiesTeacher 'bb'
2888
2889
2890--EXEC Enrolled_Student_View_Questions 'CSEN601', 2355124
2891
2892--EXEC CreateActivity "ashraf.hassan@ST.Fatima.edu.eg" , "2016-3-3" , "ssasf" , "ashraf.hassan@ST.Fatima.edu.eg" , "add" , "sdsd" , "df"
2893
2894--SELECT * FROM Activities
2895
2896--SELECT * FROM Parents
2897
2898
2899
2900
2901
2902
2903--USE dec3h
2904--GO