class QueryFactory: def __init__(self): self.db_manager = DatabaseManager() def initialize_connection(self, db_name: str, db_user: str, db_password) -> bool: connection_str: str = f"dbname='{db_name}' host=localhost user='******' password='******'" response = self.db_manager.initialize_connection(connection_str) if response is None: return True # Successful connection else: print(response) return False # Unsuccessful connection def insertLineman(self): name = "Ahmed Yasser" query : str = f"INSERT INTO efa.linesman(linesman_name)VALUES ('{name}');" self.db_manager.execute_query_no_return(query) def addStadium(self,name,rows,seatsPerRow): query:str= f"INSERT INTO efa.stadium (stadium_name, rows, columns)" \ f"VALUES ('{name}', {rows}, {seatsPerRow});" response = self.db_manager.execute_query_no_return(query) #all this dummy code if needed later error = False if response is not None: #some error happened error = True else: #no error happened error = False return error def getTeams(self): query:str = f"SELECT * FROM efa.teams;" response = self.db_manager.execute_query(query) return response def getLinemen(self): query:str = f"SELECT * FROM efa.linesman;" response = self.db_manager.execute_query(query) return response def getReferees(self): query:str = f"SELECT * FROM efa.referee;" response = self.db_manager.execute_query(query) return response def getStadiums(self): query:str = f"SELECT * FROM efa.stadium;" response = self.db_manager.execute_query(query) return response def addMatch(self,stadiumId,homeTmId,AwayTmId,refId,line1,line2,mdate,mtime): query:str = "INSERT INTO efa.match(" \ "stadium_id, home_team, away_team, main_referee,linesman_1,linesman_2, mdate, mtime)" \ f"VALUES ({stadiumId}, {homeTmId}, {AwayTmId}, {refId},"\ f" {line1}, {line2}, '{mdate}', '{mtime}');" response = self.db_manager.execute_query_no_return(query) error = False if response is not None: #some error happened print(response) error = True else: #no error happened error = False return error def getMatches(self) : query:str = "select m.* ,t.team_name,t2.team_name, s.stadium_name, r.referee_name, l1.linesman_name,l2.linesman_name from efa.match m "\ "join efa.teams t "\ "on t.team_id = m.home_team "\ "join efa.teams t2 "\ "on t2.team_id = m.away_team "\ "join efa.stadium s "\ "on s.stadium_id = m.stadium_id "\ "join efa.referee r "\ "on r.referee_id = m.main_referee "\ "join efa.linesman l1 "\ "on l1.linesman_id = m.linesman_1 "\ "join efa.linesman l2 "\ "on l2.linesman_id = m.linesman_2 "\ "where m.mdate >= CURRENT_DATE "\ "order by m.mdate, m.mtime " response = self.db_manager.execute_query(query) return response def EditMatch(self, mId, stadiumId,homeTmId,AwayTmId,refId,line1,line2,mdate,mtime): query:str = "UPDATE efa.match "\ f"SET stadium_id={stadiumId}, home_team={homeTmId}, away_team={AwayTmId}, "\ f"main_referee={refId}, linesman_1={line1}, "\ f"linesman_2={line2}, mdate='{mdate}', mtime='{mtime}' "\ f"WHERE match_id={mId}; " response = self.db_manager.execute_query_no_return(query) error = False if response is not None: #some error happened #print(response) error = True else: #no error happened error = False return error def getStadiumsSeats(self, matchId): query:str = "select s.rows as rows, s.columns as cols, ARRAY_AGG(r.seat_number)as seats\ from efa.match m\ inner join efa.stadium s\ on m.stadium_id = s.stadium_id\ left join efa.reservation r\ on m.match_id = r.match_id\ where m.match_id = "+str(matchId)+\ " group by s.rows, s.columns;" response = self.db_manager.execute_query(query) return response def reserveStadiumsSeats(self, matchId, userId, seats): reserved_seats = [] for s in seats: query:str = "INSERT INTO efa.reservation(" \ "match_id, user_id, seat_number)" \ f"VALUES ({matchId}, {userId}, {s});" response = self.db_manager.execute_query_no_return(query) #error = False if response is not None: #some error happened reserved_seats.append(s) return reserved_seats def numberOfRecvSeasts(self,matchId): query:str = "select count(*) from efa.reservation "\ f"where match_id= {matchId}" response = self.db_manager.execute_query(query) return response def checkUsername(self,username): query:str = "SELECT count(*) FROM efa.user where user_name = '{}';".format(username) response = self.db_manager.execute_query(query) exist = response[0][0] return True if exist > 0 else False def getUser(self,username): query:str = "SELECT * FROM efa.user where user_name = '{}';".format(username) response = self.db_manager.execute_query(query)[0] return response def getPass(self,username): query:str = "SELECT password FROM efa.user where user_name = '{}';".format(username) response = self.db_manager.execute_query(query)[0] return response[0] def updatePass(self,username,password): query:str = "UPDATE efa.user SET password = '******' where user_name = '{}';".format(password,username) response = self.db_manager.execute_query_no_return(query) return response def authenticate(self,data): query:str = "SELECT role , status FROM efa.user where user_name = '{}' and password = '******';".format(data['login_username'],hashlib.sha256(data['login_pass'].encode()).hexdigest()) response = self.db_manager.execute_query(query) if len(response) == 0: return False exist = response[0] # return True if exist > 0 else False return exist def addUser(self,data): query:str = "INSERT INTO efa.user(user_name, password, first_name, last_name, birth_date, gender, city, address, email, role, status) \ VALUES ( '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', '{}', {});".format( data['username'],hashlib.sha256(data['pass1'].encode()).hexdigest() ,data['first_name'],data['last_name'],data['birth_date'],data['gender'],data['city'] ,data['address'],data['email'],data['role'],0) response = self.db_manager.execute_query_no_return(query) return response def updateUser(self,data): query:str = "UPDATE efa.user\ SET first_name='{}', last_name='{}', birth_date='{}',\ gender='{}', city='{}', address='{}', role='{}', status= 0 WHERE user_name = '{}';".format( data['first_name'],data['last_name'],data['birth_date'],data['gender'],data['city'], data['address'],data['role'],data['username']) response = self.db_manager.execute_query_no_return(query) return response
class QueryFactory: def __init__(self): self.db_manager = DatabaseManager() def initialize_connection(self, db_name: str, db_user: str, db_password) -> bool: connection_str: str = f"dbname='{db_name}' user='******' password='******'" response = self.db_manager.initialize_connection(connection_str) if response is None: return True # Successful connection else: print(response) return False # Unsuccessful connection def getCourses(self,usernamee): query: str = "select * from COURSE "\ "where CODE in (select ENROLL.COURSE_CODE from ENROLL "\ f"where STUDENT_ID = (select ID from STUDENT where USERNAME = '******') "\ "and YEAR = (Select YEAR from SEMESTER order by YEAR desc limit 1) "\ "and SEMESTER =(Select SEMESTER from SEMESTER order by YEAR desc, SEMESTER desc limit 1)) "\ response = self.db_manager.execute_query(query) print("//////getcourses////////////////////asdfasfsa////////////") print(query) return response def getRequirements(self,id): query: str = "select requirement.name,project.name,requirement.id from REQUIREMENT " \ "left join project " \ "on project.id = requirement.project_id " \ "where requirement.id in (SELECT REQUIREMENT_ID from STD_ASSIGNED " \ f"where COURSE_CODE = '{id}' "\ "and YEAR = (Select YEAR from SEMESTER order by YEAR desc limit 1) "\ "and SEMESTER = (Select SEMESTER from SEMESTER order by YEAR desc, SEMESTER desc limit 1)) "\ "order by project.id,requirement.id " response = self.db_manager.execute_query(query) print(response) return response def getReqById(self,reqid): query: str = "select REQUIREMENT.*,lower(membersNumber),upper(membersNumber), COURSE.name, PROJECT.name from REQUIREMENT " \ "join COURSE on COURSE.code = REQUIREMENT.COURSE_CODE "\ "left join PROJECT on PROJECT.ID = REQUIREMENT.PROJECT_ID "\ f"where REQUIREMENT.id = {reqid} " print(f"2y 5ra m3a 2l {reqid} /n ") response = self.db_manager.execute_query(query) print(response) return response def isInTeam(self,reqid,usernamee) ->bool: query: str = "SELECT STUDENT_ID FROM FORM_TEAM "\ "WHERE TEAM_ID IN (SELECT TEAM_ID FROM TEAM "\ f"WHERE REQUIREMENT_ID = {reqid} and YEAR = (Select YEAR from SEMESTER order by YEAR desc limit 1) "\ " and SEMESTER = (Select SEMESTER from SEMESTER order by YEAR desc, SEMESTER desc limit 1)) "\ f"AND STUDENT_ID = (select ID from STUDENT where USERNAME = '******') " response = self.db_manager.execute_query(query) if response: print("////////// HE IS IN A TEAM//////////") return True else: print("////////// HE IS NOOOT A TEAM//////////") return False def getStudentsInTeamInfo(self,reqid,usernamee): query: str = "select student.NAME, student.SECTION_NUMBER , student.BENCH_NUMBER ,student.USERNAME, account.email from student "\ "join account on student.username = account.username "\ "where id in (select student_id from form_team "\ "where team_id = (SELECT team_ID FROM FORM_TEAM "\ "WHERE TEAM_ID IN (SELECT TEAM_ID FROM TEAM "\ f"WHERE REQUIREMENT_ID = {reqid}) "\ f"and student_id = (select ID from STUDENT where USERNAME = '******') )) "\ f"and student.id <> (select ID from STUDENT where USERNAME = '******') " response = self.db_manager.execute_query(query) print(response) return response def getTeamInfo(self,reqid,usernamee): query: str = "select name,team_number,team_id from team "\ "where team_id = (SELECT team_ID FROM FORM_TEAM "\ "WHERE TEAM_ID IN (SELECT TEAM_ID FROM TEAM "\ f"WHERE REQUIREMENT_ID = {reqid}) "\ f"and student_id = (select ID from STUDENT where USERNAME = '******')) " response = self.db_manager.execute_query(query) print("/////////////////////////team info") print(response) print(response) return response def getStudentsNotInTeam(self,reqid,usernamee): query:str = "select student.id, student.name from student, requirement "\ "where student.id not in (SELECT STUDENT_ID FROM FORM_TEAM "\ "WHERE TEAM_ID IN (SELECT TEAM_ID FROM TEAM "\ f"WHERE REQUIREMENT_ID = {reqid} "\ "and SEMESTER = (Select SEMESTER from SEMESTER order by YEAR desc, SEMESTER desc limit 1) "\ "and YEAR = (Select YEAR from SEMESTER order by YEAR desc limit 1))) "\ f"and requirement.id = {reqid} and requirement.type = 0 and student.id <> (select ID from STUDENT where USERNAME = '******') " response = self.db_manager.execute_query(query) print(response) return response def isTeamNoAvilabe(self,reqid,tmn)->bool: print(reqid) print(tmn) query:str = f"select team_number from team where team_number = {tmn} and team_number in( "\ "select TEAM_NUMBER from team "\ f"where REQUIREMENT_ID = {reqid} "\ "and SEMESTER = (Select SEMESTER from SEMESTER order by YEAR desc, SEMESTER desc limit 1) "\ "and YEAR = (Select YEAR from SEMESTER order by YEAR desc limit 1)) " print(query) response = self.db_manager.execute_query(query) print(response) if len(response) == 0: print("////////// Team No is Avilabe//////////") return True else: print("////////// Team NO isNot Avilable//////////") return False def addTeam(self, courseCode, reqID, teamNumber, teamName): query: str = f"INSERT INTO TEAM(COURSE_CODE, REQUIREMENT_ID, TEAM_NUMBER, NAME, SEMESTER, YEAR)" \ f"VALUES ('{courseCode}',{reqID}, {teamNumber}, '{teamName}', " \ f"(Select SEMESTER from SEMESTER order by YEAR desc, SEMESTER desc limit 1), " \ f"(Select YEAR from SEMESTER order by YEAR desc limit 1) )" response = self.db_manager.execute_query_no_return(query) query: str = "SELECT LASTVAL()" teamID = self.db_manager.execute_query(query) if response is not None: return (False, teamID[0][0]) else: return (True, teamID[0][0]) def get_student_id(self, username): query: str = f"SELECT ID FROM STUDENT WHERE USERNAME = '******'" response = self.db_manager.execute_query(query) if type(response) == Exception: return None else: return response[0][0] def fillTeamMembers(self, teamID, studentIDs): query: str = """ INSERT INTO FORM_TEAM(STUDENT_ID, TEAM_ID) SELECT S.ID, T.TEAM_ID FROM STUDENT S, TEAM T WHERE ID IN %s AND TEAM_ID = %s """ data = (tuple(studentIDs), teamID) response = self.db_manager.execute_query_no_return(query, data) if response is not None: return False else: return True def deliverableShit(self,reqID,username,teamID): print("///////////////////////////deliverable shit") print(teamID) if teamID == None: print("///////////////////////////////////////////individual////////////////////") query: str = "select d.id, d.IS_PUBLIC, d.MATERIALS, d.FEEDBACK, d.FEEDBACK_STAFF_ID, s.NAME from deliverable as d "\ "left join staff_member as s "\ "on s.id = d.FEEDBACK_STAFF_ID "\ f"where d.requirement_id = {reqID} and d.student_id = (SELECT ID FROM STUDENT WHERE USERNAME = '******') "\ "and d.SEMESTER = (Select SEMESTER from SEMESTER order by YEAR desc, SEMESTER desc limit 1) "\ "and d.year = (Select YEAR from SEMESTER order by YEAR desc limit 1) " else: print("///////////////////////////teatmtmtmtmtm////////////////////////////////") query: str = "select d.id, d.IS_PUBLIC, d.MATERIALS, d.FEEDBACK, d.FEEDBACK_STAFF_ID, s.NAME, s.CONTACT_INFO, s.OFFICE_HOURS from deliverable as d "\ "left join staff_member as s "\ "on s.id = d.FEEDBACK_STAFF_ID "\ "where "\ f"d.requirement_id = {reqID} and d.team_id = {teamID} "\ "and d.SEMESTER = (Select SEMESTER from SEMESTER order by YEAR desc, SEMESTER desc limit 1) "\ "and d.year = (Select YEAR from SEMESTER order by YEAR desc limit 1) " response = self.db_manager.execute_query(query) print(response) if response == None: print("///////////////////////////////////////a7a//////////////////////") else: print("//////////////////////////////////////msh a7a 2we////////////////////////////////////") return response def adddeliverable(self,req_id,cCode,materials,delivery_time,tmID,username): if tmID == None: query:str = "insert into deliverable (REQUIREMENT_ID, COURSE_CODE, IS_PUBLIC, MATERIALS, DELIVERY_TIME, STUDENT_ID,year,SEMESTER) "\ f"values({req_id},'{cCode}',false,'{materials}', '{delivery_time}', (SELECT ID FROM STUDENT WHERE USERNAME = '******'), (Select YEAR from SEMESTER order by YEAR desc limit 1), (Select SEMESTER from SEMESTER order by YEAR desc, SEMESTER desc limit 1) ) " else: query:str = "insert into deliverable (REQUIREMENT_ID, COURSE_CODE, IS_PUBLIC, MATERIALS, DELIVERY_TIME, TEAM_ID,year,SEMESTER) "\ f"values({req_id},'{cCode}',false,'{materials}', '{delivery_time}', {tmID} ,(Select YEAR from SEMESTER order by YEAR desc limit 1), (Select SEMESTER from SEMESTER order by YEAR desc, SEMESTER desc limit 1)) " print("//////////////////adddeliverabel") print(query) response = self.db_manager.execute_query_no_return(query) if response is not None: return False else: return True def getstaffCourses(self,username): query:str = "select t.course_code, c.name from teach as t "\ "join course as c "\ "on t.COURSE_CODE = c.code "\ f"where t.STAFF_MEMBER_ID = (select id from staff_member where username = '******') "\ "and t.SEMESTER = (Select SEMESTER from SEMESTER order by YEAR desc, SEMESTER desc limit 1) "\ "and t.year = (Select YEAR from SEMESTER order by YEAR desc limit 1) " response = self.db_manager.execute_query(query) return response def getreqbyCourseCode(self,cCode): query:str = "select r.id, r.name, p.name,p.id from requirement as r "\ "left join project as p on p.id = r.project_id "\ f"where r.course_code = '{cCode}' "\ "and r.id in(select requirement_id from std_assigned where year = (Select YEAR from SEMESTER order by YEAR desc limit 1) "\ "and SEMESTER = (Select SEMESTER from SEMESTER order by YEAR desc, SEMESTER desc limit 1)) "\ "order by p.id,r.id " response1 = self.db_manager.execute_query(query) query2:str = f"select name, id from project where course_code = '{cCode}'" response2 = self.db_manager.execute_query(query2) print("//////////////////////////////////////////////////////////////////////////////////////////") print(response1) print(response2) response = {} response["RDATA"] = response1 response["PData"] = response2 print(response) return response def addnewReqByStaff_member(self,insertionType,cCode,ProjectID,ProjectName,ProjectDocument,Name,DeadLine,addMaterials,document,type,minNumber,maxNumber): if insertionType == 3: if type == 1: minNumber = 0 maxNumber = 0 query:str = "insert into requirement(course_code,name,deadline,additional_materials,document,type,membersnumber) "\ f"values ('{cCode}','{Name}','{DeadLine}','{addMaterials}','{document}',{type},'[{minNumber},{maxNumber}]')" response = self.db_manager.execute_query_no_return(query) print(query) if response is not None: return False else: return True if insertionType == 1: if type == 1: minNumber = 0 maxNumber = 0 query1:str = "insert into project (course_code,name,document) "\ f"values ('{cCode}','{ProjectName}','{ProjectDocument}') " self.db_manager.execute_query_no_return(query1) query2:str = f"select id from project where course_code = '{cCode}' order by id desc limit 1" projnewID = self.db_manager.execute_query(query2) print(projnewID) query:str = "insert into requirement(course_code,project_id,name,deadline,additional_materials,document,type,membersnumber) "\ f"values ('{cCode}',{projnewID[0][0]},'{Name}','{DeadLine}','{addMaterials}','{document}',{type},'[{minNumber},{maxNumber}]')" response = self.db_manager.execute_query_no_return(query) if response is not None: return False else: return True if insertionType == 2: if type == 1: minNumber = 0 maxNumber = 0 query:str = "insert into requirement(course_code,project_id,name,deadline,additional_materials,document,type,membersnumber) "\ f"values ('{cCode}',{ProjectID},'{Name}','{DeadLine}','{addMaterials}','{document}',{type},'[{minNumber},{maxNumber}]')" print(query) response = self.db_manager.execute_query_no_return(query) if response is not None: return False else: return True def getUnassignedRequirements(self,cCode): query:str = "select r.name,r.id, p.name from requirement as r "\ "left join project as p "\ "on p.id = r.project_id "\ f"where r.id not in (select requirement_id from std_assigned where COURSE_CODE= '{cCode}' "\ "and year = (Select YEAR from SEMESTER order by YEAR desc limit 1) "\ "and SEMESTER = (Select SEMESTER from SEMESTER order by YEAR desc, SEMESTER desc limit 1)) "\ f"and r.course_code = '{cCode}' " response = self.db_manager.execute_query(query) print("////////////////////////////////////////////////////////////////////////") print(response) print("////////////////////////////////////////////////////////////////////////") print("////////////////////////////////////////////////////////////////////////") print(cCode) print("////////////////////////////////////////////////////////////////////////") return response def assignReqbyStaff(self,cCode,reqID): query:str = "insert into std_assigned "\ f"values ('{cCode}',{reqID},(Select YEAR from SEMESTER order by YEAR desc limit 1),(Select SEMESTER from SEMESTER order by YEAR desc, SEMESTER desc limit 1)) " response = self.db_manager.execute_query_no_return(query) if response is not None: return False else: query2:str = "select e.student_id from std_assigned as s "\ "join enroll as e "\ "on e.course_code = s.course_code "\ f"where s.requirement_id = {reqID} "\ "and e.SEMESTER = (SELECT SEMESTER FROM SEMESTER ORDER BY YEAR DESC, SEMESTER DESC LIMIT 1) "\ "and e.YEAR = (SELECT YEAR FROM SEMESTER ORDER BY YEAR DESC LIMIT 1) "\ "and s.SEMESTER = (SELECT SEMESTER FROM SEMESTER ORDER BY YEAR DESC, SEMESTER DESC LIMIT 1) "\ "and s.YEAR = (SELECT YEAR FROM SEMESTER ORDER BY YEAR DESC LIMIT 1) " response1 = self.db_manager.execute_query(query2) data = [] for x in response1: data.append(x[0]) response3 = self.get_requirement(reqID) name = response3.name msg: str = f"Requirement '{name}' was assigned for this semester" self.INSERT_NOTIFICATIONS(data,msg) return True def updateReqbyStaff(self,reqID,name,deadline,addMaterials,document): response3 = self.get_requirement(reqID) name2 = response3.name query:str = "update requirement "\ f"set name='{name}', DEADLINE='{deadline}', ADDITIONAL_MATERIALS = '{addMaterials}', DOCUMENT ='{document}' "\ f"where id = {reqID} " response = self.db_manager.execute_query_no_return(query) if response is not None: return False else: query2:str = "select e.student_id from std_assigned as s "\ "join enroll as e "\ "on e.course_code = s.course_code "\ f"where s.requirement_id = {reqID} "\ "and e.SEMESTER = (SELECT SEMESTER FROM SEMESTER ORDER BY YEAR DESC, SEMESTER DESC LIMIT 1) "\ "and e.YEAR = (SELECT YEAR FROM SEMESTER ORDER BY YEAR DESC LIMIT 1) "\ "and s.SEMESTER = (SELECT SEMESTER FROM SEMESTER ORDER BY YEAR DESC, SEMESTER DESC LIMIT 1) "\ "and s.YEAR = (SELECT YEAR FROM SEMESTER ORDER BY YEAR DESC LIMIT 1) " response1 = self.db_manager.execute_query(query2) data = [] for x in response1: data.append(x[0]) msg: str = f"Requirement '{name2}' was updated" self.INSERT_NOTIFICATIONS(data,msg) return True def getDeliverAblesbyStaff(self,reqID,cCode): query:str = "select d.*,t.name,s.name from deliverable as d "\ "left join team as t on d.team_id = t.team_id "\ "left join student as s on s.id = d.student_id "\ f"where d.REQUIREMENT_ID = {reqID} and d.COURSE_CODE = '{cCode}' and "\ "d.YEAR = (Select YEAR from SEMESTER order by YEAR desc limit 1) "\ "and d.SEMESTER = (Select SEMESTER from SEMESTER order by YEAR desc, SEMESTER desc limit 1) " response = self.db_manager.execute_query(query) print("///////////////////////get all deliverables") print(response) return response def addFeedbackbyStadd(self,deliverableID,deliverableData,staffusername): query:str = "update deliverable "\ f"set FEEDBACK = '{deliverableData}', FEEDBACK_STAFF_ID = (select id from staff_member where username = '******') "\ f"where id = {deliverableID} " response = self.db_manager.execute_query_no_return(query) if response is not None: return False else: return True def getTeamsINRequirement(self,reqID,cCode): query:str = "select TEAM_ID,TEAM_NUMBER ,NAME from team "\ f"where REQUIREMENT_ID = {reqID} and COURSE_CODE = '{cCode}' "\ "and YEAR = (Select YEAR from SEMESTER order by YEAR desc limit 1) "\ "and SEMESTER = (Select SEMESTER from SEMESTER order by YEAR desc, SEMESTER desc limit 1) " response = self.db_manager.execute_query(query) print("///////////////////////get all teamssss") print(response) return response def deleteTeam(self,teamID): print("///////////////////////////////////////delete") query:str = f"DELETE FROM team WHERE TEAM_ID = {teamID} " response = self.db_manager.execute_query_no_return(query) if response is not None: return False else: return True def unassignReqbyStaff(self,reqID,cCode): query2:str = "select e.student_id from std_assigned as s "\ "join enroll as e "\ "on e.course_code = s.course_code "\ f"where s.requirement_id = {reqID} "\ "and e.SEMESTER = (SELECT SEMESTER FROM SEMESTER ORDER BY YEAR DESC, SEMESTER DESC LIMIT 1) "\ "and e.YEAR = (SELECT YEAR FROM SEMESTER ORDER BY YEAR DESC LIMIT 1) "\ "and s.SEMESTER = (SELECT SEMESTER FROM SEMESTER ORDER BY YEAR DESC, SEMESTER DESC LIMIT 1) "\ "and s.YEAR = (SELECT YEAR FROM SEMESTER ORDER BY YEAR DESC LIMIT 1) " response1 = self.db_manager.execute_query(query2) data = [] for x in response1: data.append(x[0]) query:str = f"DELETE FROM STD_ASSIGNED WHERE REQUIREMENT_ID={reqID} and COURSE_CODE='{cCode}' "\ "and YEAR = (Select YEAR from SEMESTER order by YEAR desc limit 1) "\ "and SEMESTER = (Select SEMESTER from SEMESTER order by YEAR desc, SEMESTER desc limit 1)" response = self.db_manager.execute_query_no_return(query) if response is not None: return False else: response3 = self.get_requirement(reqID) name = response3.name msg: str = f"Requirement '{name}' was unassigned for the semester" self.INSERT_NOTIFICATIONS(data,msg) return True def deleteReqbyStaff(self,reqID): query:str =f"Delete from REQUIREMENT where ID = {reqID}" response = self.db_manager.execute_query_no_return(query) print(query) if response is not None: return False else: return True def deleteDeliverable(self,delivID): query:str =f"Delete from DELIVERABLE where ID = {delivID}" response = self.db_manager.execute_query_no_return(query) print(query) if response is not None: return False else: return True def is_user(self, user: User) -> Optional[bool]: print(user.username, user.password) query: str = f"select * from account where username='******'" \ f"and password='******'" response = self.db_manager.execute_query(query) if type(response) == Exception: return None elif response: return response else: return False def avi_email(self, user: User) -> Optional[bool]: query: str = f"select * from account where email='{user.email}'" response = self.db_manager.execute_query(query) if type(response) == Exception: return None elif response: return True else: return False def update_password(self, user: User): query: str = f"UPDATE account SET password='******'" \ f"WHERE email='{user.email}'" response = self.db_manager.execute_query_no_return(query) if type(response) == Exception: return None else: return True def Get_ALL(self, user: User): print(user.username, f"ely gay mn el database") query: str = f"select * from account where username='******'" response = self.db_manager.execute_query(query) if type(response) == Exception: return None elif response: return response else: return False def update_User(self, user: User, oldusername): print("in database") print(user.username) print(user.password) print(user.email) print(oldusername) query: str = f"UPDATE account SET password='******',username='******', email='{user.email}'" \ f"WHERE username='******'" response = self.db_manager.execute_query_no_return(query) if type(response) == Exception: return False else: return True def GET_NOTIFICATIONS(self, username): query: str = f"select n.message , n.notification_id from notification n join user_notification u " \ f"on n.notification_id = u.notification_id where username='******'" response = self.db_manager.execute_query(query) if type(response) == Exception: return None elif response: return response else: return False def INSERT_NOTIFICATIONS(self, student_ids, message): query: str = f"insert INTO notification(message) values" \ f"('{message}');" response = self.db_manager.execute_query_no_return(query) if type(response) == Exception: return None else: query = "SELECT LASTVAL()" notification_id = self.db_manager.execute_query(query) notification_id = notification_id[0][0] query = """ INSERT INTO USER_NOTIFICATION(USERNAME, NOTIFICATION_ID) SELECT S.USERNAME, NOTIFICATION_ID FROM STUDENT S, NOTIFICATION WHERE ID IN %s AND NOTIFICATION_ID = %s """ data = (tuple(student_ids), notification_id) response = self.db_manager.execute_query_no_return(query, data) if response is None: return True else: return False def Delete_NOTIFICATIONS(self, username, id): query: str = f"delete from user_notification where username='******' and notification_id ='{id}' ;" response = self.db_manager.execute_query_no_return(query) if type(response) == Exception: return None else: return True def get_portfoliocourses(self,username): query: str = f"SELECT distinct c.name FROM deliverable as d " \ f"join course as c " \ f"on c.code = d.course_code " \ f"where (d.student_id = (select id from student where username='******')) or (d.team_id in (select team_id from form_team where student_id =(select id from student where username='******'))) " response = self.db_manager.execute_query(query) print(response) if type(response) == Exception: return None elif response: return response else: return False def get_portfoliorequirement(self,username,coursename): query:str=f"select r.name,r.id from deliverable d "\ f"join requirement r on r.id = d.requirement_id "\ f"join course c on c.code = d.course_code "\ f"where c.name = '{coursename}' and (d.student_id = (select id from student where username='******')) "\ f" union "\ f"select r.name,r.id from deliverable d "\ f"join requirement r on r.id = d.requirement_id "\ f"join course c on c.code = d.course_code "\ f"where c.name = '{coursename}' and (d.team_id in (select team_id from form_team where student_id =(select id from student where username='******'))) " print(username) response = self.db_manager.execute_query(query) print if type(response) == Exception: return None elif response: return response else: return False def get_portfolio(self, username,id): query: str = f"SELECT c.name,p.name,r.name, d.materials,r.type,t.name, d.feedback,s.semester,s.year FROM deliverable as d " \ f"join course as c " \ f"on c.code = d.course_code " \ f"join requirement as r " \ f"on r.id=d.requirement_id " \ f"left join project as p " \ f"on p.id=r.project_id " \ f"left join team as t " \ f"on d.team_id=t.team_id " \ f"join std_assigned as s " \ f"on d.requirement_id=s.requirement_id " \ f"where d.requirement_id = {id} and " \ f"((d.student_id = (select id from student where username='******')) " \ f" or (d.team_id in (select team_id from form_team where student_id =(select id from student where username='******')))) " \ f"order by c.code,p.id,r.id ;" response = self.db_manager.execute_query(query) print(response) if type(response) == Exception: return None elif response: return response else: return False def get_students_by_year(self, year: int): query: str = f"SELECT ID, NAME FROM STUDENT WHERE CURRENT_YEAR = {year}" response = self.db_manager.execute_query(query) if type(response) == Exception: return None else: return response def get_courses_by_year(self, year: int): query: str = f"SELECT NAME, CODE, YEAR FROM COURSE WHERE YEAR = {year}" response = self.db_manager.execute_query(query) if type(response) == Exception: return None else: return response def edit_course(self, code: str, new_course:Course): query: str = f"UPDATE COURSE SET CODE = '{new_course.code}', NAME = '{new_course.name}', YEAR={new_course.year} " \ f"WHERE CODE = '{code}'" response = self.db_manager.execute_query_no_return(query) if type(response) == IntegrityError : return (False,"Course code already exists.") else: return (True,"Course modified successfully.") def add_course(self, new_course:Course): query: str = f"INSERT INTO COURSE(NAME, CODE, YEAR) VALUES('{new_course.name}','{new_course.code}',{new_course.year})" response = self.db_manager.execute_query_no_return(query) if type(response) == IntegrityError : return (False,"Course code already exists.") else: return (True,"Course added successfully.") def delete_course(self, code: str): query: str = f"DELETE FROM COURSE WHERE CODE = '{code}'" response = self.db_manager.execute_query_no_return(query) if type(response) == IntegrityError: return (False, "Course could not be deleted; one or more of its requirements where fulfilled by students.") else: return (True, "Course deleted successfully.") def get_course_staff(self, course_code: str): query: str = f"SELECT S.NAME FROM TEACH T JOIN STAFF_MEMBER S ON S.ID = T.STAFF_MEMBER_ID " \ f"WHERE T.COURSE_CODE = '{course_code}' AND SEMESTER = "\ f"(SELECT SEMESTER FROM SEMESTER ORDER BY YEAR DESC, SEMESTER DESC LIMIT 1)" \ f"AND YEAR = (SELECT YEAR FROM SEMESTER ORDER BY YEAR DESC LIMIT 1);" response = self.db_manager.execute_query(query) if type(response) == Exception: return None else: return response def add_user(self, user: User): query: str = f"INSERT INTO ACCOUNT(USERNAME, PASSWORD, EMAIL, USER_TYPE) VALUES " \ f"('{user.username}', '{user.password}', '{user.email}', '{user.type}')" response = self.db_manager.execute_query_no_return(query) if type(response) == IntegrityError: return False else: return True def add_student(self, student: Student): query: str = f"INSERT INTO STUDENT(NAME, CURRENT_YEAR, SECTION_NUMBER, BENCH_NUMBER, USERNAME) VALUES " \ f"('{student.name}', '{student.current_year}', '{student.section_no}', '{student.bn}', '{student.username}')" response = self.db_manager.execute_query_no_return(query) print(response) if type(response) == Exception: return False else: return True def add_staff_member(self, staff_member: StaffMember): query: str = f"INSERT INTO STAFF_MEMBER(NAME, DEPARTMENT, USERNAME) VALUES " \ f"('{staff_member.name}', '{staff_member.department}', '{staff_member.username}')" response = self.db_manager.execute_query_no_return(query) if type(response) == Exception: return False else: return True def add_semester(self, semester: Semester): query: str = f"INSERT INTO SEMESTER(SEMESTER, YEAR) VALUES " \ f"('{semester.semester}', {semester.year})" response = self.db_manager.execute_query_no_return(query) if type(response) == IntegrityError: return False else: return True def get_current_semester(self): query: str = f"SELECT SEMESTER, YEAR FROM SEMESTER WHERE " \ f"SEMESTER = "\ f"(SELECT SEMESTER FROM SEMESTER ORDER BY YEAR DESC, SEMESTER DESC LIMIT 1)" \ f"AND YEAR = (SELECT YEAR FROM SEMESTER ORDER BY YEAR DESC LIMIT 1)" response = self.db_manager.execute_query(query) [semester] = response current_semester = Semester(semester=semester[0], year=semester[1]) if type(response) == Exception: return None else: return current_semester def advance_students(self, ids): query: str = "UPDATE STUDENT SET CURRENT_YEAR = CURRENT_YEAR + 1 WHERE ID IN %s" data = (tuple(ids),) response = self.db_manager.execute_query_no_return(query, data) if response is not None: return False else: return True def enroll_students(self, ids): query: str = """ INSERT INTO ENROLL(STUDENT_ID, COURSE_CODE, SEMESTER, YEAR) SELECT * FROM (SELECT S.ID, C.CODe FROM STUDENT S JOIN COURSE C ON S.CURRENT_YEAR = C.YEAR WHERE S.ID IN %s) TEMP, SEMESTER WHERE SEMESTER = (SELECT SEMESTER FROM SEMESTER ORDER BY YEAR DESC, SEMESTER DESC LIMIT 1) AND YEAR = (SELECT YEAR FROM SEMESTER ORDER BY YEAR DESC LIMIT 1) """ data = (tuple(ids),) response = self.db_manager.execute_query_no_return(query, data) if response is not None: return False else: return True def get_student_id(self, username): query: str = f"SELECT ID FROM STUDENT WHERE USERNAME = '******'" response = self.db_manager.execute_query(query) if type(response) == Exception: return None else: return response[0][0] def get_staff_member_id(self, username): query: str = f"SELECT ID FROM STAFF_MEMBER WHERE USERNAME = '******'" response = self.db_manager.execute_query(query) if type(response) == Exception: return None else: return response[0][0] def get_staff(self): query: str = "SELECT ID, NAME FROM STAFF_MEMBER" response = self.db_manager.execute_query(query) if type(response) == Exception: return None else: return response def get_staff_member_last_semester_courses(self, id): # Returns the courses of the LAST semester query: str = f"SELECT COURSE_CODE FROM TEACH WHERE STAFF_MEMBER_ID = {id} " \ f"AND SEMESTER = (SELECT SEMESTER FROM SEMESTER ORDER BY YEAR DESC, SEMESTER DESC LIMIT 1 OFFSET 1) " \ f"AND YEAR = (SELECT YEAR FROM SEMESTER ORDER BY YEAR DESC LIMIT 1 OFFSET 1)" response = self.db_manager.execute_query(query) if type(response) == Exception: return None else: return response def assign_staff_members(self, ids, course_code): # Assigns staff members to a specific course query: str = """ INSERT INTO TEACH(STAFF_MEMBER_ID, COURSE_CODE, SEMESTER, YEAR) SELECT S.ID, C.CODE, SM.SEMESTER, SM.YEAR FROM STAFF_MEMBER S, COURSE C, SEMESTER SM WHERE C.CODE = %s AND S.ID IN %s AND SM.SEMESTER = (SELECT SEMESTER FROM SEMESTER ORDER BY YEAR DESC, SEMESTER DESC LIMIT 1) AND SM.YEAR = (SELECT YEAR FROM SEMESTER ORDER BY YEAR DESC LIMIT 1) """ data = (course_code, tuple(ids)) response = self.db_manager.execute_query_no_return(query, data) if response is not None: return False else: return True def get_requirement(self, reqID): query: str = f"SELECT * FROM REQUIREMENT WHERE ID = {reqID}" print(query) response = self.db_manager.execute_query(query) print('REQUIREMENT::') print(response) if type(response) == Exception: return None else: return Requirement(id=response[0][0], course_code=response[0][1], project_id=response[0][2], name=response[0][3], deadline=response[0][4], additional_materials=response[0][5], document=response[0][6], type=response[0][7], members_number=response[0][8]) def get_requirement_number_of_teams(self, reqID): query: str = f"SELECT COUNT(*) FROM TEAM WHERE REQUIREMENT_ID = {reqID}" \ f"AND SEMESTER = (SELECT SEMESTER FROM SEMESTER ORDER BY YEAR DESC, SEMESTER DESC LIMIT 1) " \ f"AND YEAR = (SELECT YEAR FROM SEMESTER ORDER BY YEAR DESC LIMIT 1)" response = self.db_manager.execute_query(query) print('NUMBER OF TEAMS::') print(response) if type(response) == Exception: return None else: return response[0] def get_requirement_number_of_students(self, course_code): query: str = f"SELECT COUNT(*) FROM ENROLL WHERE COURSE_CODE = '{course_code}'" \ f"AND SEMESTER = (SELECT SEMESTER FROM SEMESTER ORDER BY YEAR DESC, SEMESTER DESC LIMIT 1) " \ f"AND YEAR = (SELECT YEAR FROM SEMESTER ORDER BY YEAR DESC LIMIT 1)" response = self.db_manager.execute_query(query) print('NUMBER OF STUDENTS::') print(response) if type(response) == Exception: return None else: return response[0] def get_number_of_deliverables_deadline(self, reqID): query: str = f"SELECT COUNT(*) FROM DELIVERABLE WHERE REQUIREMENT_ID = {reqID} " \ f"AND SEMESTER = (SELECT SEMESTER FROM SEMESTER ORDER BY YEAR DESC, SEMESTER DESC LIMIT 1) " \ f"AND YEAR = (SELECT YEAR FROM SEMESTER ORDER BY YEAR DESC LIMIT 1)" response = self.db_manager.execute_query(query) print('TOTAL DELIVERED::') print(response) if type(response) == Exception: return None else: total_delivered = response[0][0] query: str = f"SELECT COUNT(*) FROM DELIVERABLE D JOIN REQUIREMENT R ON R.ID = D.REQUIREMENT_ID " \ f"WHERE DELIVERY_TIME <= DEADLINE AND REQUIREMENT_ID = {reqID} " \ f"AND SEMESTER = (SELECT SEMESTER FROM SEMESTER ORDER BY YEAR DESC, SEMESTER DESC LIMIT 1) " \ f"AND YEAR = (SELECT YEAR FROM SEMESTER ORDER BY YEAR DESC LIMIT 1)" response = self.db_manager.execute_query(query) print('DELIVERED BEFORE DEADLINE::') print(response) if type(response) == Exception: return None else: delivered_before_deadline = response[0][0] delivered_after_deadline = total_delivered - delivered_before_deadline return (delivered_before_deadline, delivered_after_deadline) def get_number_of_deliverables_feedback(self, reqID): query: str = f"SELECT COUNT(*) FROM DELIVERABLE WHERE REQUIREMENT_ID = {reqID} " \ f"AND SEMESTER = (SELECT SEMESTER FROM SEMESTER ORDER BY YEAR DESC, SEMESTER DESC LIMIT 1) " \ f"AND YEAR = (SELECT YEAR FROM SEMESTER ORDER BY YEAR DESC LIMIT 1)" response = self.db_manager.execute_query(query) if type(response) == Exception: return None else: total_delivered = response[0][0] query: str = f"SELECT COUNT(*) FROM DELIVERABLE WHERE REQUIREMENT_ID = {reqID} " \ f"AND SEMESTER = (SELECT SEMESTER FROM SEMESTER ORDER BY YEAR DESC, SEMESTER DESC LIMIT 1) " \ f"AND YEAR = (SELECT YEAR FROM SEMESTER ORDER BY YEAR DESC LIMIT 1)" \ f"AND FEEDBACK IS NOT NULL" response = self.db_manager.execute_query(query) if type(response) == Exception: return None else: have_feedback = response[0][0] no_feedback = total_delivered - have_feedback return (have_feedback, no_feedback)