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
})