def cleanse(self):
students = db_session.query(UserTable)\
.filter(UserTable.id == UserRoleTable.user_id)\
.filter(UserRoleTable.role_id == RoleTable.id)\
.filter(RoleTable.name == "Student").all()
for student in students:
roles = db_session.query(UserRoleTable).filter(UserRoleTable.user_id == student.id).all()
if len(roles) == 1: # This means "Student" is their only role
student.deletedAt = datetime.now()
banned = db_session.query(UserTable).filter(UserTable.bannedDate != None).all()
for ban in banned:
ban.bannedDate = None
db_session.commit()
def schedule_appointment(self, appt_id, course, assignment):
appointment = db_session.query(AppointmentsTable)\
.filter(AppointmentsTable.id == appt_id)\
.one_or_none()
appointment.profName = None
appointment.profEmail = None
appointment.assignment = None
appointment.notes = None
appointment.suggestions = None
appointment.courseCode = None
appointment.courseSection = None
appointment.noShow = 0
# Updates the student username
user = self.get_user_by_username(flask_session['USERNAME'])
appointment.student_id = user.id
appointment.assignment = assignment.encode(
'latin-1', 'ignore') # this ignores invalid characters
if course:
appointment.courseCode = course['course_code']
appointment.courseSection = course['section']
appointment.profName = course['instructor']
appointment.profEmail = course['instructor_email']
# Commits to DB
try:
db_session.commit()
return True
except Exception as e:
return False
def close_appt(self, appt_id):
appt = db_session.query(AppointmentsTable).filter(
AppointmentsTable.id == appt_id).one()
appt.inProgress = 0
if not appt.actualEnd:
appt.actualEnd = datetime.now()
db_session.commit()
def ban_if_no_show_check(self, user_id):
no_show_count = db_session.query(AppointmentsTable)\
.filter(AppointmentsTable.student_id == user_id)\
.filter(AppointmentsTable.noShow == 1)\
.count()
if no_show_count > int(self.get_ban_limit()[0]):
self.ban_user(user_id)
def clear_current_roles(self, user_id):
roles = db_session.query(UserRoleTable)\
.filter(UserRoleTable.user_id == user_id)\
.all()
for role in roles:
db_session.delete(role)
db_session.commit()
def remove_all_bans(self):
banned_users = db_session.query(UserTable).filter(
UserTable.bannedDate != None).all()
if banned_users:
for user in banned_users:
user.bannedDate = None
db_session.commit()
def get_user_roles(self, user_id):
user_roles = db_session.query(RoleTable)\
.filter(RoleTable.id == UserRoleTable.role_id)\
.filter(UserRoleTable.user_id == UserTable.id)\
.filter(UserTable.id == user_id)\
.all()
return user_roles
def start_appointment(self, appt_id):
appointment = db_session.query(AppointmentsTable) \
.filter(AppointmentsTable.id == appt_id) \
.one_or_none()
appointment.inProgress = 1
appointment.actualStart = datetime.now()
db_session.commit()
def edit_appt(self, appt_id, student_id, tutor_id, sched_start, sched_end,
actual_start, actual_end, prof_name, prof_email, drop_in,
virtual, sub, assignment, notes, suggestions, multiligual,
course, section, no_show, in_progress):
appt = db_session.query(AppointmentsTable).filter(
AppointmentsTable.id == appt_id).one()
appt.student_id = student_id
appt.tutor_id = tutor_id
appt.scheduledStart = sched_start
appt.scheduledEnd = sched_end
appt.actualStart = actual_start
appt.actualEnd = actual_end
appt.profName = prof_name
appt.profEmail = prof_email
appt.dropIn = drop_in
appt.online = virtual
appt.sub = sub
appt.assignment = assignment
appt.notes = notes
appt.suggestions = suggestions
appt.multilingual = multiligual
appt.courseCode = course
appt.courseSection = section
appt.noShow = no_show
appt.inProgress = in_progress
db_session.commit()
def get_upcoming_appointments(self):
tomorrow = datetime.now() + timedelta(days=1)
return db_session.query(AppointmentsTable)\
.filter(AppointmentsTable.student_id != None)\
.filter(AppointmentsTable.scheduledStart > datetime.now())\
.filter(AppointmentsTable.scheduledStart < tomorrow)\
.all()
def update_user_info(self, user_id, first_name, last_name, email):
user = db_session.query(UserTable)\
.filter(UserTable.id == user_id)\
.one()
user.firstName = first_name
user.lastName = last_name
user.email = email
db_session.commit()
def get_in_progress_appointments(self, username):
tutor = self.get_user_by_username(username)
return db_session.query(AppointmentsTable)\
.filter(AppointmentsTable.scheduledStart != None)\
.filter(AppointmentsTable.scheduledEnd == None)\
.filter(AppointmentsTable.student_id != None)\
.filter(AppointmentsTable.tutor_id == tutor.id)\
.all()
def remove_user_ban(self, user_id):
banned_user = db_session.query(UserTable)\
.filter(UserTable.id == user_id)\
.filter(UserTable.bannedDate != None)\
.one_or_none()
if banned_user:
banned_user.bannedDate = None
db_session.commit()
def get_active_tutors(self):
return db_session.query(UserTable) \
.filter(UserTable.id == UserRoleTable.user_id) \
.filter(UserRoleTable.role_id == RoleTable.id) \
.filter(RoleTable.name == 'Tutor')\
.filter(UserTable.deletedAt == None)\
.order_by(UserTable.lastName) \
.all()
def get_open_appointments_in_range(self, start, end, time_limit):
time_limit = datetime.now() + timedelta(minutes=time_limit)
return db_session.query(AppointmentsTable) \
.filter(AppointmentsTable.scheduledStart >= start)\
.filter(AppointmentsTable.scheduledStart >= time_limit)\
.filter(AppointmentsTable.scheduledEnd <= end)\
.filter(AppointmentsTable.tutor_id != None)\
.filter(AppointmentsTable.student_id == None)\
.all()
def check_for_existing_schedule(self, start_time, end_time):
try:
schedule = db_session.query(ScheduleTable)\
.filter(ScheduleTable.startTime == start_time)\
.filter(ScheduleTable.endTime == end_time)\
.one()
return True
except orm.exc.NoResultFound: # otherwise return false
return False
def confirm_delete_appointment(self, appt_id):
try:
appointment = db_session.query(AppointmentsTable).filter(
AppointmentsTable.id == appt_id).one_or_none()
db_session.delete(appointment)
db_session.commit()
return True
except Exception as e:
return False
def get_in_progress_walk_ins(self, username):
tutor = self.get_user_by_username(username)
min_today = datetime.combine(datetime.now(), datetime.min.time())
max_today = datetime.combine(datetime.now(), datetime.max.time())
return db_session.query(AppointmentsTable)\
.filter(AppointmentsTable.actualStart >= min_today)\
.filter(or_(AppointmentsTable.actualEnd == None, AppointmentsTable.actualEnd <= max_today))\
.filter(AppointmentsTable.student_id != None)\
.filter(AppointmentsTable.tutor_id == tutor.id)\
.all()
def get_profs(self):
profs = db_session.query(AppointmentsTable.profName)\
.filter(AppointmentsTable.profName != None)\
.order_by(AppointmentsTable.profName)\
.distinct()
prof_list = []
for prof in profs:
prof_name = str(prof).split('\'')
prof_list.append(prof_name[1])
return prof_list
def get_scheduled_appointments(self, username):
tutor = self.get_user_by_username(username)
min_today = datetime.combine(datetime.now(), datetime.min.time())
max_today = datetime.combine(datetime.now(), datetime.max.time())
return db_session.query(AppointmentsTable)\
.filter(AppointmentsTable.scheduledStart >= min_today)\
.filter(AppointmentsTable.scheduledEnd <= max_today)\
.filter(AppointmentsTable.student_id != None)\
.filter(AppointmentsTable.tutor_id == tutor.id)\
.all()
def get_profs_and_emails(self):
profs = db_session.query(AppointmentsTable.profName, AppointmentsTable.profEmail) \
.filter(AppointmentsTable.profName != None) \
.order_by(AppointmentsTable.profName) \
.distinct()
profs_and_emails = {}
for prof in profs:
# prof_name = str(prof.profName).split('\'')
profs_and_emails[prof.profName] = prof.profEmail
return profs_and_emails
def get_tutor_appointments(self, tutors):
appointment_list = []
for tutor_id in tutors:
tutor = self.get_user_by_id(tutor_id)
if tutor:
appointment_list.append(
db_session.query(AppointmentsTable).filter(
AppointmentsTable.tutor_id == tutor.id).all())
return appointment_list
def get_courses(self):
courses = db_session.query(AppointmentsTable.courseCode)\
.filter(AppointmentsTable.courseCode != None)\
.order_by(AppointmentsTable.courseCode)\
.distinct()
course_list = []
for course in courses:
course_code = str(course).split('\'')
course_list.append(course_code[1])
return course_list
def check_for_existing_user(self, username):
try:
user = db_session.query(UserTable)\
.filter(UserTable.username == username)\
.one_or_none()
if user.bannedDate != None:
self.reactivate_user(user.id)
return user
except Exception as e:
return False
def get_user_role_ids(self, user_id):
user_roles = db_session.query(RoleTable)\
.filter(RoleTable.id == UserRoleTable.role_id)\
.filter(UserRoleTable.user_id == UserTable.id)\
.filter(UserTable.id == user_id)\
.all()
user_role_ids = []
for role in user_roles:
user_role_ids.append(role.id)
return user_role_ids
def pickup_shift(self, appt_id, username):
try:
appointment = db_session.query(AppointmentsTable) \
.filter(AppointmentsTable.id == appt_id) \
.one_or_none()
appointment.sub = 0
appointment.tutor_id = self.get_user_by_username(username).id
db_session.commit()
return True
except Exception as e:
return False
def request_substitute(self, appt_id):
# Requests a substitute for a specific appointment
try:
appointment = db_session.query(AppointmentsTable)\
.filter(AppointmentsTable.id == appt_id)\
.one_or_none()
appointment.sub = 1
db_session.commit()
return True
except Exception as e:
return False
def get_appointments(self, start, end, value):
if value == 'all':
return db_session.query(AppointmentsTable)\
.filter(AppointmentsTable.scheduledStart >= start)\
.filter(AppointmentsTable.scheduledEnd <= end)\
.filter(AppointmentsTable.scheduledStart != AppointmentsTable.scheduledEnd)\
.filter(AppointmentsTable.student_id)\
.filter(AppointmentsTable.tutor_id)\
.all()
elif value == 'non':
return db_session.query(AppointmentsTable) \
.filter(AppointmentsTable.multilingual == 0)\
.filter(AppointmentsTable.scheduledStart >= start)\
.filter(AppointmentsTable.scheduledEnd <= end) \
.filter(AppointmentsTable.scheduledStart != AppointmentsTable.scheduledEnd) \
.filter(AppointmentsTable.student_id) \
.filter(AppointmentsTable.tutor_id) \
.filter(AppointmentsTable.online == 0) \
.all()
elif value == 'multilingual':
return db_session.query(AppointmentsTable) \
.filter(AppointmentsTable.multilingual == 1) \
.filter(AppointmentsTable.scheduledStart >= start) \
.filter(AppointmentsTable.scheduledEnd <= end) \
.filter(AppointmentsTable.scheduledStart != AppointmentsTable.scheduledEnd) \
.filter(AppointmentsTable.student_id) \
.filter(AppointmentsTable.tutor_id) \
.filter(AppointmentsTable.online == 0) \
.all()
elif value == 'non-virtual':
return db_session.query(AppointmentsTable) \
.filter(AppointmentsTable.scheduledStart >= start) \
.filter(AppointmentsTable.scheduledEnd <= end) \
.filter(AppointmentsTable.scheduledStart != AppointmentsTable.scheduledEnd) \
.filter(AppointmentsTable.student_id) \
.filter(AppointmentsTable.tutor_id) \
.filter(AppointmentsTable.online == 0) \
.all()
elif value == 'virtual':
return db_session.query(AppointmentsTable) \
.filter(AppointmentsTable.multilingual == 0) \
.filter(AppointmentsTable.scheduledStart >= start) \
.filter(AppointmentsTable.scheduledEnd <= end) \
.filter(AppointmentsTable.scheduledStart != AppointmentsTable.scheduledEnd) \
.filter(AppointmentsTable.student_id) \
.filter(AppointmentsTable.tutor_id) \
.filter(AppointmentsTable.online == 1) \
.all()
elif value == 'virtual-multilingual':
return db_session.query(AppointmentsTable) \
.filter(AppointmentsTable.multilingual == 1) \
.filter(AppointmentsTable.scheduledStart >= start) \
.filter(AppointmentsTable.scheduledEnd <= end) \
.filter(AppointmentsTable.scheduledStart != AppointmentsTable.scheduledEnd) \
.filter(AppointmentsTable.student_id) \
.filter(AppointmentsTable.tutor_id) \
.filter(AppointmentsTable.online == 1) \
.all()
else:
return False
def delete_appointment(self, appt_id):
try:
appointment = db_session.query(AppointmentsTable)\
.filter(AppointmentsTable.id == appt_id)\
.one_or_none()
if appointment.student_id:
return 'sub'
else:
db_session.delete(appointment)
db_session.commit()
return True
except Exception as e:
return False
def sub_all(self, appt_id_list):
# Requests substitutes for all appointments in the list
try:
for appt_id in appt_id_list:
appointment = db_session.query(AppointmentsTable)\
.filter(AppointmentsTable.id == appt_id)\
.one_or_none()
appointment.sub = 1
self.mcc.request_substitute(appt_id)
db_session.commit()
return True
except Exception as e:
return False
