def get_campus_list():
'''gets a list of all campus
by: Julie'''
DB_object = Database()
sql_all_branches = "SELECT campusName, campusID from campus;"
campus_list = DB_object.sql_query(sql_all_branches)
return campus_list
def staff_list():
''' displays a list of staff with logins
by Julie'''
msg = ""
rows = []
DB_object = Database()
sql = "SELECT * from user;"
list_staff = DB_object.sql_query(sql)
#print(list_no sessions)
return render_template("staff_list.html", list_staff=list_staff)
def get_students(enrolled=False):
'''returns list of students
ARgument: enrolled shows only students enrolled in a course
by: Julie'''
DB_object = Database()
if enrolled:
sql = "SELECT student.*\
FROM student\
INNER JOIN enrolment ON student.studentID=enrolment.studentID"
else: # show all students
sql = "select * from student"
#should only show students enrolled sql = "select * from student inner join enrolment on enrolment.studentID =student.studentID ;"
list_of_students = DB_object.sql_query(sql)
return list_of_students
def no_session():
''' displays a list of Holidays and dates from and to By campus
by Julie'''
msg = ""
rows = []
DB_object = Database()
#request.args.get('studentID'):
#sql="SELECT * from nosession INNER JOIN campus ON nosession.campusID = campus.campusID;"
sql = "SELECT campus.*, noSessionID, DATE_FORMAT(startDate, '%d-%m-%Y') startDate, DATE_FORMAT(endDate, '%d-%m-%Y') endDate\
from nosession INNER JOIN campus ON nosession.campusID = campus.campusID;"
list_nosessions = DB_object.sql_query(sql)
#print(list_no sessions)
return render_template("no_session_list.html",
list_nosessions=list_nosessions)
def student_search():
''' search and select students
with or without a WHERE clause depends on how Peter wants to restrict
by Julie'''
msg = ""
list_of_students = []
DB_object = Database()
#request.args.get('studentID'):
sql = """SELECT * FROM student """
list_of_students = DB_object.sql_query(sql)
#sql = '''SELECT * FROM student WHERE firstName like %s'''
#sql = "SELECT * FROM student WHERE firstName = "
#list_of_students=DB_object.sql_query_fetchall(sql, ('e%',))
#list_of_students=DB_object.sql_query_fetchall(sql, ('Frances',))
print(msg) # msg returned for update insert query as no data is
return render_template('index_search.html',
list_of_students=list_of_students)
def enrol():
'''open enrol_student page'''
DB_object = Database()
estudentID = request.args.get('studentID')
#if user has selected a course for the student to be enrolled in, insert the new record into the enrollment table
programChosen = False
list_of_available_courses = None
if request.args.get('program_selected'):
program_dict = request.args.get('program_selected')
json_acceptable_string = program_dict.replace("'", "\"")
d = json.loads(json_acceptable_string)
estudentID = d["studentID"]
programSelected = d["program"]
sql_for_available_courses = "SELECT * FROM courseinfo WHERE courseinfo.courseID Not in (SELECT enrolment.courseID FROM enrolment WHERE enrolment.studentID= %s) AND courseinfo.program = %s"
list_of_available_courses = DB_object.sql_query_fetchall(
sql_for_available_courses, (
estudentID,
programSelected,
))
programChosen = True
if request.method == 'POST':
courseID = request.form["courseID"]
studentID = request.form["studentID"]
sql = """SELECT * FROM student Where studentID = %s"""
student = DB_object.sql_query_fetchall(sql, (studentID, ))[0]
sql_student_historic = "SELECT courseinfo.courseName, courseinfo.courseID, courseinfo.program, enrolment.startDate,enrolment.lastWithdrawDate, enrolment.actualWithdrawDate,enrolment.studentID, enrolment.actualEndDate FROM courseinfo INNER JOIN enrolment on courseinfo.courseID= enrolment.courseID WHERE enrolment.studentID= %s"
list_of_student_historic = DB_object.sql_query_fetchall(
sql_student_historic, (studentID, ))
sql_for_available_courses = "SELECT * FROM courseinfo WHERE courseinfo.courseID Not in (SELECT enrolment.courseID FROM enrolment WHERE enrolment.studentID= %s)"
list_of_available_courses = DB_object.sql_query_fetchall(
sql_for_available_courses, (studentID, ))
Student_Enrolled = False
if 'withdraw_button' in request.form:
print("withdraw button clicked")
sql_withdraw_date = "UPDATE enrolment SET actualWithdrawDate = %s WHERE studentID=%s AND courseID=%s"
today_date = datetime.today().strftime("%Y-%m-%d")
msg = DB_object.sql_update_insert(sql_withdraw_date, (
today_date,
studentID,
courseID,
))
list_of_student_historic = DB_object.sql_query_fetchall(
sql_student_historic, (studentID, ))
list_of_available_courses = DB_object.sql_query_fetchall(
sql_for_available_courses, (studentID, ))
return render_template(
'enrol_student.html',
Student_Enrolled=Student_Enrolled,
msg=msg,
student=student,
list_of_courses=list_of_available_courses,
list_of_student_historic=list_of_student_historic,
estudentID=studentID)
# for actual End Date button
if 'actualEndDate_button' in request.form:
print("actualEndDate clicked")
sql_actualEndDate = "UPDATE enrolment SET actualEndDate = %s WHERE studentID=%s AND courseID=%s"
today_date = datetime.today().strftime("%Y-%m-%d")
msg = DB_object.sql_update_insert(sql_actualEndDate, (
today_date,
studentID,
courseID,
))
print(msg)
list_of_student_historic = DB_object.sql_query_fetchall(
sql_student_historic, (studentID, ))
list_of_available_courses = DB_object.sql_query_fetchall(
sql_for_available_courses, (studentID, ))
return render_template(
'enrol_student.html',
Student_Enrolled=Student_Enrolled,
msg=msg,
student=student,
list_of_courses=list_of_available_courses,
list_of_student_historic=list_of_student_historic,
estudentID=studentID)
if 'enrol_button' in request.form:
print("enrol button clicked")
startDate = datetime.today()
startDateAsString = datetime.today().strftime("%Y-%m-%d")
#FIND THE DURATION OF THE SELECTED COURSE
duration_sql = "Select durationInDay From courseinfo Where courseID=%s"
duration = DB_object.sql_query_fetchone(duration_sql, (courseID))
duration = duration.get('durationInDay')
#CALCULATE THE ENDDATE (CURRENT DAY + DURATION OF THE COURSE)
duration = timedelta(days=duration)
end_date = (startDate + duration).strftime("%Y-%m-%d")
print("date")
print(end_date)
#CALCULATE THE LASTWITHDATE (CURRENT DAY X 70% )
lwd = startDate + timedelta(days=round(70 * 0.1, 0))
# print(lwd.strftime("%Y-%m-%d") + " line 390")
#ENROL THE STUDENT INTO THE COURSE AND LAST WITH DRAW DATE
sql = "INSERT INTO enrolment (courseID, studentID, startDate, endDate , lastWithdrawDate) VALUES (%s, %s, %s, %s, %s)"
values = (courseID, studentID, startDateAsString, end_date, lwd)
msg = DB_object.sql_update_insert(
sql, (courseID, studentID, startDateAsString, end_date, lwd))
print("Enrol")
print(msg)
#check to see if the sql query was executed successfully, if not msg will be displayed to the user
Student_Enrolled = False
if "Successful" in msg:
Student_Enrolled = True
list_of_student_historic = DB_object.sql_query_fetchall(
sql_student_historic, (studentID, ))
list_of_available_courses = DB_object.sql_query_fetchall(
sql_for_available_courses, (studentID, ))
return render_template(
'enrol_student.html',
Student_Enrolled=Student_Enrolled,
end_date=end_date,
msg=msg,
student=student,
list_of_courses=list_of_available_courses,
list_of_student_historic=list_of_student_historic,
estudentID=studentID)
#check if a student has been chosen, if yes, return any courses that they are enrolled in
if estudentID != None:
sql = """SELECT * FROM student Where studentID = %s"""
student = DB_object.sql_query_fetchall(sql, (estudentID, ))[0]
print(student)
#test code to return all courses to allow the user to select one to enrol the student into
sql_for_courses = "SELECT * FROM courseinfo"
list_of_courses = DB_object.sql_query(sql_for_courses)
sql_for_enrolled_courses = "SELECT * FROM enrolment Where studentID = %s"
list_of_enrolled_courses = DB_object.sql_query_fetchall(
sql_for_enrolled_courses, (estudentID, ))
sql_student_historic = "SELECT courseinfo.courseName, courseinfo.courseID, courseinfo.program, enrolment.startDate,enrolment.lastWithdrawDate, enrolment.actualWithdrawDate,enrolment.studentID, enrolment.actualEndDate FROM courseinfo INNER JOIN enrolment on courseinfo.courseID= enrolment.courseID WHERE enrolment.studentID= %s"
list_of_student_historic = DB_object.sql_query_fetchall(
sql_student_historic, (estudentID, ))
return render_template(
'enrol_student.html',
student=student,
list_of_courses=list_of_available_courses,
list_of_enrolled_courses=list_of_enrolled_courses,
list_of_student_historic=list_of_student_historic,
estudentID=estudentID,
programChosen=programChosen)
else:
return redirect(url_for('student_search'))
return render_template('enrol_student.html', student=student)