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 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 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 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)
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 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
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)
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)