def book_student():
DB_object = Database()
#this grabs selected studentID from url and assigns to object studentID
estudentID = request.args.get('studentID')
edate = request.args.get('datetext')
esessionID = request.args.get('sessionMenu')
ecampusID = request.args.get('campusid')
#these 'objects' are passed to sql insert statement below through the '%s'
if request.method == 'GET':
#if (edate !=None) and (esessionID !=None ):
sqlDate = datetime.strptime(edate, '%d-%m-%Y').strftime("%Y-%m-%d")
book_SQL = "INSERT INTO attenbooking (studentID, actionTime ,sessionID, action)\
VALUES (%s, %s, %s, %s);"
msg = DB_object.sql_update_insert(
book_SQL, (estudentID, sqlDate, esessionID, 'book'))
flash(msg)
#else:
#flash("Please Select Campus, Date & Session to Find a Class to Book-into")
redirect_to_index = redirect(
url_for('index',
studentID=estudentID,
campusid=ecampusID,
sessionid=esessionID,
datetext=edate))
response = make_response(redirect_to_index)
return response
def student():
'''open add_student page'''
DB_object = Database()
if request.method == 'POST':
email = request.form["email"]
firstName = request.form["firstName"]
lastName = request.form["lastName"]
homePhoneNumber = request.form["homePhoneNumber"]
middleName = ""
gender = ""
MobileNumber = request.form["MobileNumber"]
studentID = request.form["studentID"]
sql = "INSERT INTO student (studentID, firstName, middleName, lastName, gender, homePhoneNumber, MobileNumber, emailAddress) VALUES (%s, %s, %s, %s, %s,%s, %s, %s)"
msg = DB_object.sql_update_insert(sql, (
studentID,
firstName,
middleName,
lastName,
gender,
homePhoneNumber,
MobileNumber,
email,
))
#flash('Congratulations, you registered a Student!')
#flash(msg)
return render_template('student.html')
def get_disabled_dates(ecampusID=0):
''' gets dates that location is closed
argument 1: campus id optional
returns: list of dates
if no campusID then only get statatory hols
by: Julie'''
DB_object = Database() #nitialize db obj
disabled_dates = [] #initialize empty list
if ecampusID != 0:
sql = '''SELECT noSessionID, campusID, startDate, endDate FROM nosession WHERE campusID = %s'''
# print(sql)
no_sesion_rows = DB_object.sql_query_fetchall(sql, (ecampusID, ))
#print(no_sesion_rows)
for no_session in no_sesion_rows:
#loop through list and pull out start and end dates from dictionary item
start_dt = (no_session.get("startDate"))
end_dt = no_session.get("endDate")
#loop through start and end date for this dictionary
for dt in daterange(start_dt, end_dt):
#append to disabled date list
disabled_dates.append(dt.strftime("%d-%m-%Y"))
# get statutory holidays from func-----------------------------------------------------------
no_school_day = no_school_years(2018, 2019)
#append statutory list to noSessions list
i = 0
while i < len(no_school_day):
for day in no_school_day[i]:
disabled_dates.append(day.strftime("%d-%m-%Y"))
# print(type(day.strftime("%Y-%m-%d")))
i = i + 1
return disabled_dates
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 delete_staff():
''' will edit a staff member logins
by Julie'''
list_staff = []
DB_object = Database()
euserID = request.args.get('userID')
sql = "DELETE FROM user WHERE id = %s"
DB_object.sql_query_delete(sql, (euserID, ))
redirect_to_index = redirect(url_for('staff_list'))
response = make_response(redirect_to_index)
return response
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_sessions(ecampusID,
edatepicked=None,
ReturnIDs=False,
esessionPeriod=False):
''' gets sessions AM, PM. EVE for a campus on a day of week like Mon
argument 1: campus id
argument 2: date
used on front page to populate session list after selecte date
argument 3: optional
used on available session page
argument 4: optional session period such as afternoon P or morning A
used on available sessions page
returns: list of sessions
by: Julie'''
DB_object = Database()
if ReturnIDs:
# uses Argument campusID and return all sessions isactive true and false
# this is used on session_schedule page to load AM or PM session on schedule
sql = "SELECT * FROM cSession WHERE sessionPeriod = %s AND campusID = %s"
# print("returnID get_session")
sqllist_of_sessions = DB_object.sql_query_fetchall(
sql, (esessionPeriod, ecampusID))
return sqllist_of_sessions
else: #ReturnIDs == False and edatepicked!=None: #and esessionPeriod == False) and edatepicked!=None:
#return only active sessions uses Arguments emapusID, dayofweek from passed date
#print( "returnid", ReturnIDs , "datepicked", edatepicked, "campus", ecampusID)
sql = "SELECT sessionID, left(sessionPeriod,1) as sessionPeriod FROM cSession WHERE campusid = %s AND SessionDay = %s AND isActive = 1"
dayofweek = datetime.strptime(edatepicked, '%d-%m-%Y').strftime('%a')
dayofweek = dayofweek[:2].upper(
) # have to change to upper 2 letters as stupidly stored in db as this
#print("day", dayofweek)
sqllist_of_sessions = DB_object.sql_query_fetchall(
sql, (ecampusID, dayofweek), True) # dont want dictionary ba
# this data should have a lookup table but too hard at mo to change db
#print("sql sessions", sqllist_of_sessions)
list_of_sessions = []
for session in sqllist_of_sessions:
inner_list = []
#print(session['sessionPeriod'])f
session_code = session['sessionPeriod']
if session_code == 'A':
session_name = 'Morning Session'
elif session_code == 'P':
session_name = 'Afternoon Session'
elif session_code == 'E':
session_name = 'Evening Session'
inner_list = [session['sessionID'], session_name]
list_of_sessions.append(inner_list)
return list_of_sessions
def student_profile():
'''Will render a table of students who haven't finished their course before the course Endate'''
DB_object = Database()
today = datetime.today().strftime('%Y-%m-%d')
sql = '''SELECT student.studentID, student.firstName, student.lastName, courseinfo.courseName, courseinfo.durationInDay, enrolment.startDate,enrolment.endDate,enrolment.actualEndDate
FROM student
INNER JOIN enrolment ON student.studentID = enrolment.studentID
INNER JOIN courseinfo ON enrolment.courseID = courseinfo.courseID
WHERE endDate <= %s and actualWithdrawDate is null and completed = %s;'''
student_not_finish = DB_object.sql_query_fetchall(sql, (today, False))
return render_template('student_profile.html',
student_not_finish=student_not_finish)
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 delete_no_session():
''' deletes record from no_session holiday closed table
from list of no_sessions
by Julie'''
DB_object = Database()
enoSessionID = request.args.get('noSessionID')
sql = "DELETE FROM nosession WHERE noSessionID = %s;"
msg = DB_object.sql_query_delete(sql, (enoSessionID, ))
flash(msg)
response = make_response(redirect('/no_session'))
return response
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 update_end_date():
'''Will receive the date and update the courseEndaDate'''
DB_object = Database()
if request.method == 'POST':
sid = request.form[
"studentID"] #if [] not working try () request.args.get('userID')request.args.get
print(sid)
cname = request.form["courseName"]
data = request.form.get['data']
print(data['date'])
print(type(date))
sql = '''UPDATE enrolment SET endDate= %s WHERE studentID = %s;'''
update = DB_object.sql_update_insert(sql, (date, sid))
redirect_to_student_profile = redirect(url_for('student_profile'))
response = make_response(redirect_to_student_profile)
return response
def engagement_record():
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
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))
print(last_engagement_summary)
return render_template('engagement_record.html',
last_engagement_summary=last_engagement_summary)
class NoSessionsForm(FlaskForm):
''' No session form ie dates campus closed
by Julie '''
DB_object = Database()
sql = "SELECT cast(campusID as char) campusID, campusName from campus;"
campus_list = list(DB_object.sql_query(sql,False))
campusID = SelectField('Campus', choices=campus_list , validators=[DataRequired()])
startDate = DateField('Start Date', format='%Y-%m-%d', validators=[DataRequired()])
endDate = DateField('End Date', format='%Y-%m-%d', validators=[DataRequired()])
submit = SubmitField('Save')
def save_weekly_sessions():
'''Save weekly sessions by location on sessions.html page
by: Julie'''
DB_object = Database()
#if user has selected a course for the student to be enrolled in, insert the new record into the enrollment table
if request.method == 'POST':
campusid = request.form["campusID"]
# print("campus from form" , campusid)
#checkboxes = request.form.getlist("sesssionChkbox")
#for checkbox in checkboxes: bummer this just returned a list of 0s
#update all sessions as inactive off for this campus, as dont know off unticked checkboxes as not returned
sql = "update cSession set isActive =0 where campusID=%s"
#set sessions with checkbox off
msg = DB_object.sql_update_insert(sql, (campusid, ))
keys = request.form
f = request.form
session_on_list = []
session_on_list = ""
for key in f.keys():
for value in f.getlist(key):
#nb only get on ticked checkboxes returned
if "campusID" not in key: #only want checkboxes
#print( key,":",value, " " , str(key[6:])) # shows as value=5 : on value=3 : on
session_on_list = session_on_list + key[6:] + ", "
session_on_list = session_on_list[:-2] # take off comma
#print(session_on_list)
sql = "UPDATE cSession set isActive =1 WHERE campusID=" + campusid + " AND sessionID IN(" + session_on_list + ");"
#sql = "UPDATE cSession set isActive =1 WHERE campusID=%s AND sessionID IN(%s)"
#DB_object.sql_update_insert(sql, (campusID, session_on_list))
norow = DB_object.sql_update_novar(sql)
# print(sql)
#print("norow", norow)
flash(msg)
#for ctr_tickboxs in request.:
# print(ctr_tickboxs)
return redirect(
url_for('session_schedule',
title='Session Schedule',
campusid=campusid))
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 cancel():
DB_object = Database()
attenbookingID = request.args.get('attenID')
edate = request.args.get('datetext')
esessionID = request.args.get('sessionMenu')
ecampusID = request.args.get('campusid')
if request.method == 'GET':
cancel = "UPDATE attenbooking SET attenbooking.action = 'cancel' WHERE attenbookingID = %s;"
msg = DB_object.sql_update_insert(cancel, (attenbookingID, ))
redirect_to_index = redirect(
url_for('index',
campusid=ecampusID,
sessionid=esessionID,
datetext=edate))
response = make_response(redirect_to_index)
return response
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 getsessions():
'''uses javascript and ajax to get the sessions for a date onchange of datepicker
from the db but
doesnt load the page again
by: Julie'''
ecampusID = request.args.get('campusid')
#print("campus", ecampusID)
edateText = request.args.get('datetext')
edateText = datetime.strptime(request.args.get('datetext'),
'%d-%m-%Y').strftime(
"%Y-%m-%d") #format for MySQL
DB_object = Database()
#print("get sessions datepicker ", edateText)
dayofweek = datetime.strptime(edateText, '%Y-%m-%d').strftime('%a')
dayofweek = dayofweek[0:2].upper(
) #this is stupidly how we store in DB, if Id know about this function...
sql = "SELECT sessionID, left(sessionPeriod,1) as sessionPeriod FROM cSession WHERE campusid = %s AND SessionDay = %s AND isActive=1"
list_of_sessions = DB_object.sql_query_fetchall(
sql, (ecampusID, dayofweek),
True) # dont want dictionary back to load for dropdown list
# list_of_sessions= get_sessions(ecampusID, edateText)
#print("list",list_of_sessions)
#print(list_of_sessions)
option_str = "<option value='none'>Select Session</option>"
for session in list_of_sessions:
#print(session['sessionPeriod'])
str_session = session['sessionPeriod']
if str_session == 'A':
session_name = 'Morning Session'
elif str_session == 'P':
session_name = 'Afternoon Session'
elif str_session == 'E':
session_name = 'Evening Session'
option_str = option_str + '<option value="' + str(
session['sessionID']) + '">' + session_name + '</option>'
#returns html to create select list options
return option_str
class RegistrationForm(FlaskForm):
"""registration form for staff
by Julie"""
DB_object = Database()
sql = "SELECT cast(campusID as char) campusID, campusName from campus;"
campus_list = list(DB_object.sql_query(sql,False))
#print(campus_list)
username = StringField('Username', validators=[DataRequired()])
email = StringField('Email', validators=[DataRequired(), Email()])
password = PasswordField('Password', validators=[DataRequired()])
password2 = PasswordField('Repeat Password', validators=[DataRequired(), EqualTo('password')])
campusID = SelectField('campusID', choices=campus_list , validators=[DataRequired()]) #[('1', 'Hornby'), ('2', 'Ricarton'), ('3', 'Rangiora')])
submit = SubmitField('Register')
#state = SelectField('State:', validators=[DataRequired()], id='select_state')
def validate_username(self, username):
user = User.query.filter_by(username=username.data).first()
if user is not None: raise ValidationError('Please use a different username.')
def validate_email(self, email):
user = User.query.filter_by(email=email.data).first()
if user is not None:
raise ValidationError('Please use a different email address.')
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 index(
campusid=1
): #defaults to AraCity, datepicked=(date.today()) , sessionid=None, datetext=None
#sessionid=esessionID, campusid=ecampusID , datetext=edate)
"""load main page
by: Julie and worked with oscar on sql to load students in class"""
DB_object = Database()
#for search for students enrolled in a course datatable
list_of_students = get_students(True)
campus_list = get_campus_list()
#print(campus_list)
# get the campus ID from the dropdown. default of 1 is passed
ecampusID = request.args.get('campusid')
esession = request.args.get('sessionid')
if (ecampusID is None or ecampusID == 'None'):
#go back to home page as no class selected and nothing to return
book_list = []
disabled_dates = []
list_of_sessions = []
return render_template('index.html',
list_of_students=list_of_students,
title='Home',
campus_list=campus_list,
list_of_sessions=list_of_sessions)
#ecampusID=campusid
elif ecampusID != None and ecampusID != 'None':
# print("ecampusID index",ecampusID)
ecampusID = int(ecampusID)
edatepicked = request.args.get('datetext')
if edatepicked is None:
# print("date is none")
list_of_sessions = []
#edatepicked=edatepicked.strftime("%d-%m-%Y")
elif edatepicked is not None:
# print("date not none")
edatepicked = datetime.strptime(request.args.get('datetext'),
'%d-%m-%Y').strftime("%d-%m-%Y")
sqlDate = datetime.strptime(edatepicked,
'%d-%m-%Y').strftime("%Y-%m-%d")
#get sessions AM, PM. EVE for day eg Mon
list_of_sessions = get_sessions(ecampusID, edatepicked)
#print("session list index", list_of_sessions)
#-------get sessions --------------------------------------
if (esession is None) or (ecampusID is None):
# dont run query
book_list = []
list_of_sessions = []
else:
sql = "SELECT attenbooking.attenbookingID, attenbooking.studentID,\
enrolment.courseID, student.firstName, student.lastName, attenbooking.action, cSession.campusID\
FROM attenbooking\
INNER JOIN student ON attenbooking.studentID=student.studentID\
INNER JOIN enrolment ON student.studentID=enrolment.studentID\
INNER JOIN cSession ON attenbooking.sessionID = cSession.sessionID\
WHERE cSession.campusID =%s AND actionTime = %s AND attenbooking.sessionID =%s AND attenbooking.action = 'book' OR attenbooking.action = 'checkin'"
print("session for sql", sql)
print("variables", ecampusID, sqlDate, esession)
book_list = DB_object.sql_query_fetchall(
sql, (ecampusID, sqlDate, esession))
print(book_list)
#-select campus-------------------------------------
disabled_dates = [] #initialize empty list
disabled_dates = get_disabled_dates(ecampusID)
#print(list_of_sessions)
#also sends back date, location, session to reselect items in controls
return render_template('index.html',
list_of_students=list_of_students,
title='Home',
book_list=book_list,
disabled_dates=disabled_dates,
ecampusID=ecampusID,
campus_list=campus_list,
edatepicked=edatepicked,
esession=esession,
list_of_sessions=list_of_sessions)
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)