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]