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)