def add_ta_by_uname_coursecode_group(uname, code): # for students that are yet not tutoring any course sql_helper.exec_sql('INSERT IGNORE INTO TAs VALUES(%(uname)s)', {'uname': uname}) return sql_helper.exec_sql( 'INSERT IGNORE INTO Assist values(%(uname)s, %(code)s)', { 'uname': uname, 'code': code })
def mark_enroll_complete_by_uname_and_code(code, uname): sql_helper.exec_sql( "DELETE FROM Attend WHERE code= %(code)s AND uname=%(uname)s", { 'uname': uname, 'code': code }) return sql_helper.exec_sql( "UPDATE Enroll SET status='completed' WHERE code= %(code)s AND uname=%(uname)s", { 'uname': uname, 'code': code })
def add_reply(code, fid, pid, title, content, uname): max_pid = sql_helper.fetchone_to_dict("SELECT max(pid) as max FROM Posts " "WHERE code = %(code)s AND fid = %(fid)s", {'code': code, 'fid': fid})['max'] if not max_pid: max_pid = 0 sql_helper.exec_sql('INSERT IGNORE INTO Posts values (%(code)s, %(fid)s, %(pid)s, %(code)s, %(fid)s, %(t_pid)s, ' '%(uname)s, %(title)s, %(content)s)', {'code': code, 'fid': fid, 'pid': max_pid + 1, 't_pid': pid, 'title': title, 'content': content, 'uname': uname}) return get_post_by_code_and_fid_and_pid(code, fid, max_pid + 1)
def add_student_to_tut_by_uname_coursecode_groupnum(uname, code, group_num): sql_helper.exec_sql( 'insert into Attend values (%(uname)s, %(code)s, %(group_num)s )', { 'uname': uname, 'code': code, 'group_num': group_num }) return sql_helper.fetchall_to_dict( 'SELECT * FROM Users NATURAL JOIN (Students NATURAL JOIN Attend) WHERE code = %(code)s AND group_num = %(group_num)s', { 'code': code, 'group_num': group_num })
def add_reply(code, title): max_fid = sql_helper.fetchone_to_dict( "SELECT max(fid) as max FROM Forums " "WHERE code = %(code)s", {'code': code})['max'] if not max_fid: max_fid = 0 sql_helper.exec_sql( 'INSERT IGNORE INTO Forums values (%(code)s, %(fid)s, %(title)s)', { 'code': code, 'fid': max_fid + 1, 'title': title })
def add_enroll_request_by_uname_and_code(uname, code): return sql_helper.exec_sql( "INSERT IGNORE INTO Enroll (uname, code, status) VALUES (%(uname)s, %(code)s, 'requesting')", { 'uname': uname, 'code': code })
def add_TA_to_tut_by_uname_code_group_num(uname, code, group_num): sql_helper.exec_sql( 'insert into Facilitate values (%(uname)s, %(code)s, %(group_num)s)', { 'uname': uname, 'code': code, 'group_num': group_num }) return sql_helper.fetchall_to_dict( "SELECT * FROM Users u NATURAL JOIN Participators NATURAL JOIN (Students stu NATURAL JOIN Facilitate f)" "WHERE u.uname = %(uname)s AND f.code = %(code)s AND f.group_num = %(group_num)s", { 'uname': uname, 'code': code, 'group_num': group_num })
def add_forum_to_tut_by_code_group_num_fid(code, group_num, fid): sql_helper.exec_sql( 'insert into View values (%(f_code)s, %(fid)s,%(t_code)s ,%(group_num)s)', { 'f_code': code, 'fid': fid, 't_code': code, 'group_num': group_num }) return sql_helper.fetchall_to_dict( "SELECT * FROM Forums f " "WHERE f.code = %(code)s AND f.fid = %(fid)s", { 'code': code, 'fid': fid })
def add_stu_to_attendance_by_uname_code_group_num(uname, code, group_num, attend_week): sql_helper.exec_sql( 'insert into Attendance values (%(uname)s, %(code)s, %(group_num)s,%(attend_week)s)', { 'uname': uname, 'code': code, 'group_num': group_num, 'attend_week': attend_week }) return sql_helper.fetchall_to_dict( "SELECT * FROM Users u " # "NATURAL JOIN Participators p" " NATURAL JOIN (Students stu NATURAL JOIN Attendance a)" "WHERE u.uname = %(uname)s AND" " a.code = %(code)s AND a.group_num = %(group_num)s " "AND a.attend_week=%(attend_week)s", { 'uname': uname, 'code': code, 'group_num': group_num, 'attend_week': attend_week })
def delete_post(code, fid, pid): sql_helper.exec_sql('DELETE FROM Posts WHERE code=%(code)s AND fid=%(fid)s AND pid=%(pid)s', {'code': code, 'fid': fid, 'pid': pid})
def delete_forum(code, fid): sql_helper.exec_sql( 'DELETE FROM Forums WHERE code=%(code)s AND fid=%(fid)s', { 'code': code, 'fid': fid })