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 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 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 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 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 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 create_email_preferences(self, user_id, sub_email_pref,
stu_email_pref):
new_email_prefs = EmailPreferencesTable(
user_id=user_id,
subRequestEmail=sub_email_pref,
studentSignUpEmail=stu_email_pref)
db_session.add(new_email_prefs)
db_session.commit()
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 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 activate_existing_user(self, user_id):
try:
user = self.get_user_by_id(user_id)
user.deletedAt = None
db_session.commit()
return True
except Exception as e:
print(e)
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 tutor_change_appt(self, appt_id, assignment, notes, suggestions):
try:
appt = self.get_appointment_by_id(appt_id)
appt.assignment = assignment
appt.notes = notes
appt.suggestions = suggestions
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 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 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 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 create_user(self, first_name, last_name, username, sub_email_pref,
stu_email_pref):
new_user = UserTable(username=username,
firstName=first_name,
lastName=last_name,
email='{0}@bethel.edu'.format(username),
bannedDate=None,
deletedAt=None)
db_session.add(new_user)
db_session.commit()
self.create_email_preferences(new_user.id, sub_email_pref,
stu_email_pref)
return new_user
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
def set_user_roles(self, username, roles):
user = self.get_user_by_username(username)
for role in roles:
role_entry = self.get_role_by_name(role)
# Check if the user already has this role
role_exists = db_session.query(UserRoleTable)\
.filter(UserRoleTable.user_id == user.id)\
.filter(UserRoleTable.role_id == role_entry.id)\
.one_or_none()
if role_exists: # If they do, skip adding it again
continue
user_role = UserRoleTable(user_id=user.id, role_id=role_entry.id)
db_session.add(user_role)
db_session.commit()
def create_user(self, username, name):
first_name = name['0']['firstName']
last_name = name['0']['lastName']
email = '{0}@bethel.edu'.format(username)
user = UserTable(
username=username,
email=email,
firstName=first_name,
lastName=last_name,
)
db_session.add(user)
db_session.commit()
user_role = UserRoleTable(user_id=user.id, role_id=4)
db_session.add(user_role)
db_session.commit()
def cancel_appointment(self, appt_id):
try:
appt = db_session.query(AppointmentsTable)\
.filter(AppointmentsTable.id == appt_id)\
.one_or_none()
appt.student_id = None
appt.profName = None
appt.profEmail = None
appt.assignment = None
appt.courseCode = None
appt.courseSection = None
db_session.commit()
return True
except Exception as e:
return False
def end_appointment(self, appt_id, course, assignment, notes, suggestions):
appointment = db_session.query(AppointmentsTable)\
.filter(AppointmentsTable.id == appt_id)\
.one_or_none()
appointment.inProgress = 0
if not course:
appointment.courseCode = None
appointment.courseSection = None
appointment.profName = None
appointment.profEmail = None
else:
appointment.courseCode = course['course_code']
appointment.courseSection = course['section']
appointment.profName = course['instructor']
appointment.profEmail = course['instructor_email']
appointment.assignment = assignment
appointment.notes = notes
appointment.suggestions = suggestions
appointment.actualEnd = datetime.now()
db_session.commit()
def create_time_slot(self, start_time, end_time, is_active):
try:
if self.check_for_existing_schedule(start_time, end_time):
schedule = db_session.query(ScheduleTable) \
.filter(ScheduleTable.startTime == start_time) \
.filter(ScheduleTable.endTime == end_time) \
.one()
if schedule.active == 0 and is_active == 1:
schedule.active = 1
db_session.commit()
return True
else:
return False
new_schedule = ScheduleTable(startTime=start_time,
endTime=end_time,
active=is_active)
db_session.add(new_schedule)
db_session.commit()
return True
except Exception as e:
return False
def begin_walk_in_appointment(self, user, tutor, course, assignment,
multilingual):
if course:
course_code = course['course_code']
course_section = course['section']
prof_name = course['instructor']
prof_email = course['instructor_email']
begin_appt = AppointmentsTable(student_id=user.id,
tutor_id=tutor.id,
actualStart=datetime.now(),
profName=prof_name,
profEmail=prof_email,
assignment=assignment,
courseCode=course_code,
courseSection=course_section,
inProgress=1,
dropIn=1,
sub=0,
multilingual=multilingual,
noShow=0,
online=0)
else:
begin_appt = AppointmentsTable(student_id=user.id,
tutor_id=tutor.id,
actualStart=datetime.now(),
assignment=assignment,
inProgress=1,
dropIn=1,
sub=0,
multilingual=multilingual,
noShow=0,
online=0)
db_session.add(begin_appt)
try:
db_session.commit()
return begin_appt
except Exception as e:
return False
def delete_tutor_shifts(self, tutors, start_date, end_date):
delete_list = []
sub_list = []
if calendar.monthrange(end_date.year,
end_date.month)[1] != end_date.day:
end_date = end_date.replace(day=end_date.day + 1)
else:
end_date = end_date.replace(month=(end_date.month + 1) % 12, day=1)
for tutor_id in tutors:
tutor = self.get_user_by_id(tutor_id)
if tutor:
# Gets all of the appointments where no students are signed up for slots and
delete_list.extend(
db_session.query(AppointmentsTable).
filter(AppointmentsTable.tutor_id == tutor.id).filter(
AppointmentsTable.scheduledStart >= start_date).filter(
AppointmentsTable.scheduledEnd <= end_date).filter(
AppointmentsTable.student_id == None).all())
# Gets all the appointments where there is a student signed up for that we now potentially need
# subtitutes for
sub_list.extend(
db_session.query(AppointmentsTable).filter(
AppointmentsTable.tutor_id == tutor.id).filter(
AppointmentsTable.scheduledStart >= start_date).
filter(AppointmentsTable.scheduledEnd <= end_date).filter(
AppointmentsTable.sub == 0).filter(
AppointmentsTable.student_id != None).all())
try:
for appt in delete_list:
db_session.delete(appt)
db_session.commit()
except Exception:
return False
if delete_list and not sub_list:
return 'none'
return sub_list
def toggle_substitute(self, substitute):
toggle = self.get_email_preferences()
toggle.subRequestEmail = substitute
db_session.commit()
return 'success'
def toggle_shift(self, shift):
toggle = self.get_email_preferences()
toggle.studentSignUpEmail = shift
db_session.commit()
return 'success'
def edit_user(self, first_name, last_name, username):
user_to_edit = self.get_user_by_username(username)
user_to_edit.firstName = first_name
user_to_edit.lastName = last_name
db_session.commit()
def ban_user(self, username):
now = datetime.now()
user = self.get_user_by_username(username)
user.bannedDate = now
db_session.commit()
def deactivate_user(self, user_id):
user = self.get_user_by_id(user_id)
user.deletedAt = datetime.now()
db_session.commit()
