def add_section(elective_id, teacher_id, times, room_nbr, year, tri):
time_ids = []
for time_desc in times.split(", "):
time_id = query_one(
DB.ELECTIVE,
"SELECT time_id FROM elective_time WHERE time_short_desc=%s",
[time_desc])[0]
time_ids.append(time_id)
section_nbr = query_one(
DB.ELECTIVE,
"SELECT COUNT(*) FROM elective_section WHERE elective_id=%s",
[elective_id])[0] + 1
insert(
DB.ELECTIVE,
"INSERT INTO elective_section (elective_id, section_nbr, teacher_id, room_nbr, course_year, tri) VALUES (%s, %s, %s, %s, %s, %s)",
[elective_id, section_nbr, teacher_id, room_nbr, year, tri])
section_id = query_one(
DB.ELECTIVE,
"SELECT section_id FROM elective_section WHERE elective_id=%s AND section_nbr=%s",
[elective_id, section_nbr])[0]
data = []
for time_id in time_ids:
data.append([section_id, time_id])
insertmany(
DB.ELECTIVE,
"INSERT INTO elective_section_time_xref (section_id, time_id) VALUES (%s, %s)",
data)
def get_elective(id):
result = query_one(
DB.ELECTIVE, "SELECT elective_id, name, `desc`, course_id, prereq "
"FROM elective "
"WHERE elective_id = %s", [id])
if result:
elective = Elective(result[0], result[1], result[2], result[3],
result[4])
sections = query(
DB.ELECTIVE,
"SELECT section_id, section_nbr, teacher_id, course_year, tri, max, room_nbr, teacher_id "
"FROM elective_section "
"WHERE elective_id = %s", [elective.id])
for section in sections:
teacher_id = section[7]
section = ElectiveSection(section[0], None, section[1], section[4],
section[3], section[5], section[6],
get_teacher(teacher_id))
section.times = get_times_by_section_id(section.id)
elective.sections.append(section)
return elective
return None
def get_amount_left(section_id):
section_info = query_one(
DB.ELECTIVE, "SELECT es.max - es.enrolled_count "
"FROM elective_section es "
"WHERE section_id = %d", [section_id])[0]
return section_info
def is_section_full(section_id):
section_info = query_one(DB.ELECTIVE, "SELECT enrolled_count, max "
"FROM elective_section "
"WHERE section_id = %s", section_id)
return section_info[0] >= section_info[1]
def create_elective(name, desc, course_id, prereq):
if not course_id:
if prereq:
insert(
DB.ELECTIVE,
"INSERT INTO elective (name, `desc`, course_id, prereq) VALUES (%s, %s, %s, %s)",
(name, desc, course_id, prereq))
else:
insert(
DB.ELECTIVE,
"INSERT INTO elective (name, `desc`, course_id) VALUES (%s, %s, %s)",
(name, desc, course_id))
else:
if prereq:
insert(
DB.ELECTIVE,
"INSERT INTO elective (name, `desc`, prereq) VALUES (%s, %s, %s)",
(name, desc, prereq))
else:
insert(DB.ELECTIVE,
"INSERT INTO elective (name, `desc`) VALUES (%s, %s)",
(name, desc))
return query_one(
DB.ELECTIVE,
"SELECT elective_id FROM elective WHERE name=%s AND `desc`=%s",
[name, desc])[0]
def enrollment_open(grade_level):
result = query_one(DB.CLUBS, "SELECT * "
"FROM signup_dates t "
"WHERE t.grade_lvl = %s "
"AND NOW() >= t.start "
"AND NOW() <= t.end", [grade_level])
return (not result is None)
def drop_club(usr_id, club_id):
delete(DB.CLUBS, 'DELETE FROM club_user_xref WHERE usr_id=%s AND club_id=%s', [usr_id, club_id])
enrollment_count = query_one(DB.CLUBS, "SELECT enrollment_count "
"FROM club "
"WHERE club_id = %s", club_id)
query = "UPDATE club SET enrollment_count = %s WHERE club_id = %s"
params = [enrollment_count[0] - 1, club_id]
update(DB.CLUBS, query, params)
def get(id):
result = query_one(
DB.SHARED,
'SELECT * FROM user WHERE usr_id=%s AND usr_active=1 LIMIT 1',
[id])
if result:
type_cde = query_one(
DB.SHARED, 'SELECT usr_type_cde FROM user WHERE usr_id = %s',
[id])[0]
if type_cde == 'ADM':
return Admin(id)
elif type_cde == 'TCH':
return Teacher(id)
elif type_cde == 'STD':
return Student(id)
else:
return User(id)
return None
def get_role(self, app_cde):
role = query_one(
DB.SHARED, 'SELECT usr_role_cde '
'FROM role_application_user_xref '
'WHERE usr_id=%s '
'AND app_cde=%s', [self.__usr_id__, app_cde])
if role:
return role[0]
else:
return self.get_type_code()
def remove_student(student_id, club_id):
delete(DB.CLUBS, 'DELETE FROM club_user_xref WHERE usr_id=%s AND club_id=%s', [student_id, club_id])
enrollment_count = query_one(DB.CLUBS, "SELECT enrollment_count "
"FROM club "
"WHERE club_id = %s", club_id)
query = "UPDATE club SET enrollment_count = %s WHERE club_id = %s"
params = [enrollment_count[0] - 1, club_id]
update(DB.CLUBS, query, params)
return False
def enroll_user(usr_id, club_id):
print(usr_id, club_id)
insert(DB.CLUBS, 'INSERT INTO club_user_xref (club_id, usr_id) VALUES (%s, %s) '
'ON DUPLICATE KEY UPDATE club_id=club_id', [club_id, usr_id])
enrollment_count = query_one(DB.CLUBS, "SELECT enrollment_count "
"FROM club "
"WHERE club_id = %s", club_id)
query = "UPDATE club SET enrollment_count = %s WHERE club_id = %s"
params = [enrollment_count[0] + 1, club_id]
update(DB.CLUBS, query, params)
def get_enrollment_time(grade_level):
result = query_one(DB.CLUBS, "SELECT grade_lvl, start, end, course_year, tri_nbr "
"FROM signup_dates t "
"WHERE t.grade_lvl = %s "
"AND NOW() >= t.start "
"AND NOW() < t.end", [grade_level])
if result:
start_time = result[1]
end_time = result[2]
return EnrollmentTime(result[0], start_time, end_time, result[3], result[4])
else:
return EnrollmentTime(grade_level, None, None, get_current_year(), '-1')
def get_current_info():
current_year = get_current_year()
formatted_year = str(current_year.split('-')[0])
print(formatted_year)
# ps_year format - Ex: 2018 = 28
formatted_year = formatted_year[0] + formatted_year[-1]
current_tri = query_one(DB.CLUBS, 'SELECT tri_nbr FROM atcsdevb_dev_shared.trimester ' +
'WHERE NOW() <= end_dt ' +
'AND NOW() >= start_dt ' +
# 'AND ps_year = %s ' +
'ORDER BY end_dt')[0]
return [current_year, current_tri]
def authenticate_user(ip_address, username, password):
resultID = query_one(DB.SHARED,
'SELECT usr_id FROM user WHERE usr_bca_id = %s',
vars=[username])
if resultID:
resultID = resultID[0]
server = Server('168.229.1.240:3268', get_info=ALL)
conn = Connection(server, user=username, password=password)
if not conn.bind:
return None
return create_token(resultID, ip_address)
def get_section_students(section_id):
users = query(DB.ELECTIVE,
"SELECT usr_id FROM elective_user_xref WHERE section_id=%s",
[section_id])
students = []
for user in users:
info = query_one(
DB.ELECTIVE,
"SELECT usr_id, usr_first_name, usr_last_name, academy_cde, usr_class_year "
"FROM user "
"WHERE usr_type_cde='STD' "
"AND usr_id=%s", [user[0]])
students.append(Student(info[0], info[1], info[2], info[3], info[4]))
return students
def get_club_students(club_id):
# get all the students enrolled in a club
users = query(DB.CLUBS, "SELECT usr_id FROM club_user_xref WHERE club_id=%s", [club_id])
# get all the info pertaining to those students
students = []
for user in users:
info = query_one(DB.SHARED, "SELECT usr_id, usr_first_name, usr_last_name, usr_class_year, academy_cde "
"FROM user "
"WHERE usr_type_cde='STD' "
"AND usr_id=%s", [user[0]])
# combine all their info into one object
students.append(Student(info[0], info[1], info[2], info[3], info[4]))
# return a list of all the students enrolled in a club + their information
return students
def delete_section(teacher_id, section_id):
print("Im boutta delete something")
can_delete = query_one(
DB.ELECTIVE,
"SELECT * FROM elective_section WHERE section_id=%s AND teacher_id=%s",
[section_id, teacher_id]) != None
print(can_delete)
print(teacher_id)
print(section_id)
if can_delete:
delete(DB.ELECTIVE,
"DELETE FROM elective_user_xref WHERE section_id=%s",
[section_id])
delete(DB.ELECTIVE,
"DELETE FROM elective_section_time_xref WHERE section_id=%s",
[section_id])
delete(DB.ELECTIVE, "DELETE FROM elective_section WHERE section_id=%s",
[section_id])
return True
return False
def is_club_full(club_id):
club_info = query_one(DB.CLUBS, "SELECT enrollment_count, max_nbr "
"FROM club "
"WHERE club_id = %s", club_id)
return club_info[0] >= club_info[1]
def get_type_name(type_cde):
type_name = query_one(DB.CLUBS, "select club_type_name from club_type where club_type.club_type_cde = %s",
[type_cde])
return type_name
def get_message_id_by_content(content):
return db.query_one("""
SELECT message_id
FROM Messages
WHERE content=%(content)s
""", content=content)
def get_chat_by_topic(topic):
return db.query_one("""
SELECT chat_id
FROM Chats
WHERE topic=%(topic)s
""", topic=topic)
def find_user(nick):
return db.query_one("""
SELECT user_id, name
FROM Users
WHERE nick=%(nick)s
""", nick=nick)
def get_type_code(self):
return query_one(DB.SHARED,
'SELECT usr_type_cde FROM user WHERE usr_id = %s',
[self.__usr_id__])[0]
def create_bakesale(group_name, group_size, items_desc, requested_day, teacher_id):
insert(DB.BAKESALE, "INSERT INTO bakesale (group_name, group_size, `items_desc`, requested_day, teacher_id, status_code) VALUES (%s, %s, %s, %s, %s, %s)", (group_name, group_size, items_desc, requested_day, teacher_id, 'R'))
return query_one(DB.BAKESALE, "SELECT bakesale_id FROM bakesale WHERE group_name=%s AND `items_desc`=%s", [group_name, items_desc])[0]
def get_amount_left(club_id):
club_info = query_one(DB.CLUBS, "SELECT c.max_nbr - c.enrolled_count "
"FROM club c"
"WHERE club_id = %d", [club_id])[0]
return club_info
def load_name(self):
return query_one(
DB.SHARED,
'SELECT usr_first_name, usr_last_name FROM user WHERE usr_id = %s',
[self.__usr_id__])
def get_club(club_id):
c = query_one(DB.CLUBS,
"SELECT club_id, name, advisor_id, club_type_name, morning_club_flg, room, description, max_nbr, enrollment_count"
" FROM club, club_type "
" WHERE club.club_type_cde = club_type.club_type_cde "
" AND club_id = %s ", [club_id])
if c:
club = Club(c[1], c[3], c[4], c[0], c[6], c[7], c[5], c[8], c[2])
return club
def __init__(self, name, day, type_cde, id, description, max_nbr, room_nbr, enrollment_count, advisor_id):
return None
def edit_club(club_id, name, day, room_nbr, description, max_nbr, type_cde):
query = "UPDATE club SET name = %s, day = %s, room_nbr = %s, description = %s, max_nbr = %s, club_type_cde = %s WHERE club_id = %s"
params = [name, day, room_nbr, description, max_nbr, type_cde, club_id]
update(DB.CLUBS, query, params)
return False
def add_club(name, max_nbr, type_cde, room_nbr, desc, advisor_id, day):
insert(DB.CLUBS,
"INSERT INTO club (name, max_nbr, club_type_cde, room, description, advisor_id, enrollment_count, course_year, tri_nbr ) "
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)",
(name, max_nbr, type_cde, room_nbr, desc, advisor_id, 0,
Config.CLUBS_CURRENT_COURSE_YEAR, Config.CLUBS_CURRENT_TRIMESTER))
return False
def get_club_days():
types = query(DB.CLUBS, "select club_type_cde, club_type_name from club_type order by sort_order")
club_types = []
for i in types:
club_types.append(Day(i[0], i[1]))
return club_types
def get_type_name(type_cde):
type_name = query_one(DB.CLUBS, "select club_type_name from club_type where club_type.club_type_cde = %s",
[type_cde])
return type_name
def get_club_day(club_id):
info = query(DB.CLUBS, "select club_type_cde, day"
"from club "
"where club.club_id = %s ", [club_id])
# i could've done this on one line but i wanna make sure i'm doing this right lol
d = info[0]
return Day(d[0], d[1])
def get_club_students(club_id):
# get all the students enrolled in a club
users = query(DB.CLUBS, "SELECT usr_id FROM club_user_xref WHERE club_id=%s", [club_id])
# get all the info pertaining to those students
students = []
for user in users:
info = query_one(DB.SHARED, "SELECT usr_id, usr_first_name, usr_last_name, usr_class_year, academy_cde "
"FROM user "
"WHERE usr_type_cde='STD' "
"AND usr_id=%s", [user[0]])
# combine all their info into one object
students.append(Student(info[0], info[1], info[2], info[3], info[4]))
# return a list of all the students enrolled in a club + their information
return students
def remove_student(student_id, club_id):
delete(DB.CLUBS, 'DELETE FROM club_user_xref WHERE usr_id=%s AND club_id=%s', [student_id, club_id])
enrollment_count = query_one(DB.CLUBS, "SELECT enrollment_count "
"FROM club "
"WHERE club_id = %s", club_id)
query = "UPDATE club SET enrollment_count = %s WHERE club_id = %s"
params = [enrollment_count[0] - 1, club_id]
update(DB.CLUBS, query, params)
return False
def delete_club(club_id):
students = get_club_students(club_id)
# remove all students from a club first
for student in students:
remove_student(student.usr_id, club_id)
# remove the club from the club & xref tables
delete(DB.CLUBS, 'DELETE FROM club WHERE club_id=%s', [club_id])
delete(DB.CLUBS, 'DELETE FROM club_user_xref WHERE club_id=%s', [club_id])
return False
def get_grade_level(self):
return query_one(DB.SHARED,
'SELECT usr_grade_lvl FROM user WHERE usr_id = %s',
[self.__usr_id__])[0]
