def edit_student():
'''open edit_student page'''
DB_object = Database()
#this grabs selected studentID from html and assigns to object studentID
#, selected_date and selected_sessionID here
estudentID = request.args.get('studentID')
print("estudent", estudentID)
student_sql = """SELECT * FROM student WHERE studentID = %s """
student_details = DB_object.sql_query_fetchone(student_sql, (estudentID, ))
# update student data if form data is posted
if request.method == 'POST':
studentID = request.form["studentID"]
firstName = request.form["firstName"]
lastName = request.form["lastName"]
MobileNumber = request.form["MobileNumber"]
homePhoneNumber = request.form["homePhoneNumber"]
email = request.form["email"]
emoodle = request.form["moodle"]
enote = request.form["notes"]
edit_student_sql = "UPDATE student SET studentID = %s, firstName = %s, lastName = %s, mobileNumber = %s, homePhoneNumber= %s, emailAddress = %s, lastMoodleEngagement = %s, note =%s WHERE studentID = %s"
msg = DB_object.sql_update_insert(edit_student_sql, (
studentID,
firstName,
lastName,
MobileNumber,
homePhoneNumber,
email,
emoodle,
enote,
studentID,
))
flash(msg)
student_sql = """SELECT * FROM student WHERE studentID = %s """
student_details = DB_object.sql_query_fetchone(student_sql,
(studentID, ))
return render_template(
'edit_student.html',
student_details=student_details,
)
# redirect_to_index = redirect(url_for('edit_student', studentID=studentID))
# response = make_response( redirect_to_index)
# return response
print("studen", student_details)
return render_template('edit_student.html',
student_details=student_details)
def report_dashboard():
'''open report dashboard page'''
DB_object = Database()
today = datetime.today().strftime('%Y-%m-%d') #get today's date
date = (datetime.today() - timedelta(7)).strftime(
'%Y-%m-%d') #used this date to count the last lastWithdrawDate
# yesterday = (datetime.today()-timedelta(1)).strftime('%Y-%m-%d')
# if yesterday is sunday:
# last_school_day = (datetime.today()-timedelta(2)).strftime('%Y-%m-%d')
# if yesterday is public_holiday:
# last_school_day = (datetime.today()-timedelta(day_ph)).strftime('%Y-%m-%d')
sql = '''SELECT COUNT(lastWithdrawDate)
FROM enrolment
WHERE lastWithdrawDate= %s and actualWithdrawDate is null;'''
lsw_summary = DB_object.sql_query_fetchone(sql, (today))
number_lsw_student = lsw_summary['COUNT(lastWithdrawDate)']
sql = '''SELECT COUNT(endDate)
FROM enrolment
WHERE endDate= %s and completed = %s;'''
gf_student_summary = DB_object.sql_query_fetchone(sql, (today, 'False'))
number_gf_student = gf_student_summary['COUNT(endDate)']
sql = '''SELECT student.studentID, student.firstName, student.lastName, courseinfo.courseName, attenbooking.action, attenbooking.actionTime, student.lastMoodleEngagement
From student
INNER JOIN attenbooking ON student.studentID = attenbooking.studentID
INNER JOIN enrolment ON attenbooking.studentID = enrolment.studentID
INNER JOIN courseinfo ON enrolment.courseID = courseinfo.courseID
WHERE actionTime = %s and (lastMoodleEngagement <= %s or lastMoodleEngagement is null);'''
last_engagement_summary = DB_object.sql_query_fetchall(sql, (date, date))
sql = '''SELECT COUNT(student.studentID)
FROM student
INNER JOIN attenbooking ON student.studentID = attenbooking.studentID
INNER JOIN enrolment ON attenbooking.studentID = enrolment.studentID
INNER JOIN courseinfo ON enrolment.courseID = courseinfo.courseID
WHERE actionTime = %s and (lastMoodleEngagement <= %s or lastMoodleEngagement is null);'''
inactive_student = DB_object.sql_query_fetchone(sql, (date, date))
number_of_inactive_student = inactive_student['COUNT(student.studentID)']
return render_template('report_dashboard.html',number_of_inactive_student = number_of_inactive_student, \
number_lsw_student=number_lsw_student,number_gf_student = number_gf_student,last_engagement_summary=last_engagement_summary)
def update_no_session():
'''update no_session record ie holidays and exclusions dates closed
this doesnt use WTF forms if I have time I will migrate over otherwise leave it as it works
by Julie'''
row = []
DB_object = Database()
disabled_dates = get_disabled_dates(0) #only get statatory dates
campus_list = get_campus_list()
if request.method == 'POST': #save the updates
ecampusID = request.form['campus_no_session']
eendDate = request.form['EndDatepicker']
estartDate = request.form['StartDatepicker']
enoSessionID = request.form['noSessionID']
estartDate = datetime.strptime(estartDate,
'%d-%m-%Y').strftime("%Y-%m-%d")
eendDate = datetime.strptime(eendDate, '%d-%m-%Y').strftime("%Y-%m-%d")
sql = "UPDATE nosession SET campusID = %s, startDate = %s, endDate = %s WHERE noSessionID = %s"
msg = DB_object.sql_update_insert(
sql, (ecampusID, estartDate, eendDate, enoSessionID))
flash(msg)
#sql = "SELECT * FROM nosession WHERE noSessionID =%s"
#row = DB_object.sql_query_fetchone(sql, (enoSessionID,))
#return render_template("no_session.html",row = row)
response = make_response(redirect('/no_session'))
return response
elif request.method == "GET":
enoSessionID = request.args.get('noSessionID')
# print("add_no_session", enoSessionID)
row = []
sql = "SELECT noSessionID, campusID, DATE_FORMAT(startDate, '%%d-%%m-%%Y') startDate, DATE_FORMAT(endDate, '%%d-%%m-%%Y') endDate FROM noSession WHERE noSessionID = %s"
#sql = "SELECT * FROM noSession WHERE noSessionID = %s"
print(sql)
row = DB_object.sql_query_fetchone(sql, (enoSessionID, ))
print("row", row)
return render_template("no_session_edit.html",
row=row,
campus_list=campus_list,
disabled_dates=disabled_dates)
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)