def checkCourseTable(self):
if self.request.session['is_login'] != True or self.request.session[
'role'] != STUDENT_ROLE:
self._init_response()
return self._get_response(UNAUTHORIZED_AS_STUDENT)
try:
print('user_id' in self.data)
user_id = self.data['user_id']
except Exception as error:
self._init_response()
return self._get_response(POST_ARG_ERROR, -1)
try:
cursor = connection.cursor()
#
check_courses_sql = 'select * from section natural join course natural join teaches natural join takes natural join instructor natural join exam where student_id = %s'
cursor.execute(check_courses_sql, (user_id, ))
raw_courses_taken = sql_util.dictfetchall(cursor)
total_num = len(raw_courses_taken)
sections = []
for row in raw_courses_taken:
tmp = {
'title': row['title'],
'course_id': row['course_id'],
'section_id': row['section_id'],
'dept_name': row['dept_name'],
'instructor_name': row['instructor_name'],
'credits': row['credits'],
'classroom_no': row['classroom_no'],
'day': row['day'],
'start': row['start'],
'end': row['end'],
'exam_classroom_no': row['exam_classroom_no'],
'exam_day': row['exam_day'],
'exam_type': row['type'],
'start_time': row['start_time'],
'end_time': row['end_time'],
'open_note_flag': row['open_note_flag']
}
sections.append(tmp)
res = {
'total_num': total_num,
'sections': sections,
}
self.response.update(res)
self._init_response()
return self._get_response(SHOW_COURSE_TABLE, 1)
except Exception as error:
print(error)
traceback.print_exc()
connection.rollback()
self._init_response()
return self._get_response(SERVER_ERROR, -1)
def checkTaughtCourses(self):
if self.request.session['is_login'] != True or self.request.session[
'role'] != INSTRUCTOR_ROLE:
self._init_response()
return self._get_response(UNAUTHORIZED_AS_INSTRUCTOR)
try:
user_id = self.data['user_id']
except Exception as error:
self._init_response()
return self._get_response(POST_ARG_ERROR, -1)
try:
cursor = connection.cursor()
check_courses_taught_sql = "SELECT * FROM (SELECT * FROM 'teaches' NATURAL JOIN 'section' WHERE 'teaches'.'instructor_id' = '" + user_id + "') NATURAL JOIN 'course' "
cursor.execute(check_courses_taught_sql)
raw_courses_taught = sql_util.dictfetchall(cursor)
print("raw courses taught are :", raw_courses_taught)
total_num = len(raw_courses_taught)
print("total num is ", total_num)
# sections = list(raw_courses_taken)
sections = []
for row in raw_courses_taught:
tmp = {
'title': row['title'],
'course_id': row['course_id'],
'section_id': row['section_id'],
'dept_name': row['dept_name'],
'credits': row['credits'],
'classroom_no': row['classroom_no'],
'day': row['day'],
'start': row['start'],
'end': row['end']
}
sections.append(tmp)
res = {
'total_num': total_num,
'sections': sections,
}
self.response.update(res)
self._init_response()
return self._get_response(SHOW_COURSE_TABLE, 1)
except Exception as error:
traceback.print_exc()
connection.rollback()
self._init_response()
return self._get_response(SERVER_ERROR, -1)
def checkExamTable(self):
if self.request.session['is_login'] != True or self.request.session[
'role'] != STUDENT_ROLE:
self._init_response()
return self._get_response(UNAUTHORIZED_AS_STUDENT)
try:
user_id = self.data['user_id']
except Exception as error:
self._init_response()
return self._get_response(GET_ARG_ERROR, -1)
try:
cursor = connection.cursor()
sql = 'select * from section natural join takes natural join course natural join exam where student_id = %s'
cursor.execute(sql, (user_id, ))
raw_exams_taken = sql_util.dictfetchall(cursor)
print("raw courses taken are :", raw_exams_taken)
total_num = len(raw_exams_taken)
print("total num is ", total_num)
sections = []
for row in raw_exams_taken:
tmp = {
'title': row['title'],
'course_id': row['course_id'],
'section_id': row['section_id'],
'classroom_no': row['classroom_no'],
'day': row['day'],
'start_time': row['start_time'],
'end_time': row['end_time'],
'open_note_flag': row['open_note_flag'],
'type': row['type']
}
sections.append(tmp)
res = {
'total_num': total_num,
'exams': sections,
}
self.response.update(res)
self._init_response()
return self._get_response(SHOW_EXAM_TABLE, 1)
except Exception as error:
traceback.print_exc()
connection.rollback()
self._init_response()
return self._get_response(SERVER_ERROR, -1)
def checkCourseNameList(self):
if self.request.session['is_login'] != True or self.request.session[
'role'] != INSTRUCTOR_ROLE:
self._init_response()
return self._get_response(UNAUTHORIZED_AS_INSTRUCTOR)
try:
user_id = self.data['user_id']
course_id = self.data['course_id']
section_id = self.data['section_id']
except Exception as error:
self._init_response()
return self._get_response(POST_ARG_ERROR, -1)
try:
cursor = connection.cursor()
check_namelist_sql = "SELECT * FROM 'takes' NATURAL JOIN 'student' WHERE 'takes'.'course_id'='" + course_id + "' AND 'takes'.'section_id'=" + section_id
cursor.execute(check_namelist_sql)
raw_namelist = sql_util.dictfetchall(cursor)
print("raw name list taken are :", raw_namelist)
total_num = len(raw_namelist)
print("total num is ", total_num)
students = []
for row in raw_namelist:
tmp = {
'student_id': row['student_id'],
'student_name': row['student_name'],
'student_major': row["student_major"],
'student_dept_name': row["student_dept_name"],
'grade': row['grade'],
}
students.append(tmp)
res = {
'total_num': total_num,
'students': students,
}
self.response.update(res)
self._init_response()
return self._get_response(SHOW_COURSE_TABLE, 1)
except Exception as error:
traceback.print_exc()
connection.rollback()
self._init_response()
return self._get_response(SERVER_ERROR, -1)
pass
def checkApplicationsByInstructor(self, user_id):
try:
cursor = connection.cursor()
sql = 'select * from teaches natural join application natural join course where instructor_id = %s'
cursor.execute(sql, (user_id, ))
rows = sql_util.dictfetchall(cursor)
total_num = len(rows)
self._init_response()
res = {'total_num': total_num, 'applications': rows}
self.response.update(res)
return self._get_response(SHOW_APPS, 1)
except Exception as error:
traceback.print_exc()
connection.rollback()
self._init_response()
return self._get_response(SERVER_ERROR, -1)
def checkStudentInfo(self, user_id):
try:
cursor = connection.cursor()
check_personal_info_sql = "SELECT * FROM 'student' where 'student'.'student_id'='" + user_id + "'"
cursor.execute(check_personal_info_sql)
student_info = sql_util.dictfetchone(cursor)
sql = 'select * from takes natural join course natural join section where student_id = %s'
cursor.execute(sql, (user_id, ))
take_infos = sql_util.dictfetchall(cursor)
gpa = 0
grade_lis = []
total_credit = 0
for take_info in take_infos:
title = take_info['title']
grade = take_info['grade']
credits = take_info['credits']
if grade != None:
grade_dict = {}
grade_dict['title'] = title
grade_dict['course_id'] = take_info['course_id']
grade_dict['section_id'] = take_info['section_id']
grade_dict['credits'] = credits
grade_dict['grade'] = grade
grade_lis.append(grade_dict)
total_credit += credits
if grade in GRADE_DICT.keys():
gpa += GRADE_DICT[grade] * credits
if total_credit != 0:
gpa = float(gpa / total_credit)
res = {"grade_list": grade_lis, "gpa": gpa}
self._init_response()
self.response.update(student_info)
self.response.update(res)
return self._get_response(SHOW_PERSONAL_INFO)
except Exception as error:
traceback.print_exc()
connection.rollback()
self._init_response()
return self._get_response(SERVER_ERROR, -1)
def checkApplicationsByStudent(self, user_id):
try:
cursor = connection.cursor()
check_app_sql = 'select * from application natural join course natural join teaches natural join instructor where student_id = %s'
cursor.execute(check_app_sql, (user_id, ))
app_infos = sql_util.dictfetchall(cursor)
total_num = len(app_infos)
app_infos = list(app_infos)
res = {"total_num": total_num, "applications": app_infos}
self._init_response()
self.response.update(res)
return self._get_response(SHOW_APPS, 1)
except Exception as error:
traceback.print_exc()
connection.rollback()
self._init_response()
return self._get_response(SERVER_ERROR, -1)
def registerExam(self):
# if self.request.session['is_login'] != True or self.request.session['role'] != ROOT_ROLE:
# self._init_response()
# return self._get_response(UNAUTHORIZED)
raw_exams = self.importExcel(self.request)
res = {'successed_item_num': 0}
if raw_exams != None:
print("the exams are ", raw_exams)
try:
cursor = connection.cursor()
for row in raw_exams:
flag = True
for i in range(7):
if row[i] == None or row[i] == '':
flag = False
self._init_response()
return self._get_response(INVALID_BLANK, -1)
break
if flag:
t_course_id = row[0]
t_section_id = int(row[1])
t_type = int(row[2])
t_exam_day = int(row[3])
# t_start_time = int((str(row[4])).split(":")[0])
# t_end_time = int((str(row[5])).split(":")[0])
t_start_time = float(row[4])
t_end_time = float(row[5])
t_exam_classroon_no = str(row[6])
# exist check
sql = 'select * from course where course_id =%s'
cursor.execute(sql, (row[0], ))
flag = sql_util.dictfetchone(cursor)
if flag == None:
msg = "no such course: " + str(row[0])
self._init_response()
return self._get_response(msg, -1)
sql = 'select * from classroom where classroom_no = %s'
cursor.execute(sql, (row[6], ))
test = sql_util.dictfetchone(cursor)
if test == None:
self._init_response()
return self._get_response(
"no such classroom:" + row[2], -1)
sql = 'select * from section where course_id = %s and section_id=%s '
cursor.execute(sql, (
row[0],
int(row[1]),
))
test = sql_util.dictfetchone(cursor)
if test == None:
self._init_response()
return self._get_response(
"no such section:" + row[0] + "." + row[1], -1)
sql = 'select * from exam where exam_day=%s and exam_classroom_no=%s'
cursor.execute(sql, (
t_exam_day,
t_exam_classroon_no,
))
test = sql_util.dictfetchall(cursor)
if test != []:
for item in test:
test_start = float(
int(item['start_time'].split(":")[0]) / 24)
test_end = float(
int(item['end_time'].split(":")[0]) / 24)
if (test_start > t_start_time and test_start < t_end_time) or\
(test_end > t_start_time and test_end < t_end_time):
self._init_response()
self.response.update(res)
return self._get_response(
"exam classroom time conflict", -1)
# same check
sql = 'select * from exam where course_id=%s and section_id=%s'
cursor.execute(sql, (row[0], int(row[1])))
flag = sql_util.dictfetchone(cursor)
if flag == None:
sql = 'insert into exam(course_id, section_id, exam_classroom_no, exam_day, type, start_time, end_time, open_note_flag)'\
'values(%s,%s,%s,%s,%s,%s,%s,%s)'
cursor.execute(
sql, (row[0], int(row[1]), row[6], int(
row[3]), t_type, str(t_start_time * 24),
str(t_end_time * 24), 0))
connection.commit()
res['successed_item_num'] += 1
else:
self._init_response()
self.response.update(res)
msg = "data conflict: " + row[0] + "." + str(
int(row[1]))
return self._get_response(msg, -1)
self._init_response()
return self._get_response(IMPORT_OK, 1)
except Exception as error:
traceback.print_exc()
connection.rollback()
self._init_response()
return self._get_response(SERVER_ERROR, -1)
else:
self._init_response()
return self._get_response(INVALID_BLANK, -1)
def registerSection(self):
if self.request.session['is_login'] != True or self.request.session[
'role'] != ROOT_ROLE:
self._init_response()
return self._get_response(UNAUTHORIZED)
raw_sections = self.importExcel(self.request)
res = {'successed_item_num': 0}
if raw_sections != None:
print("the sections are ", raw_sections)
try:
cursor = connection.cursor()
for row in raw_sections:
flag = True
for i in range(8):
if row[i] == None:
flag = False
self._init_response()
self.response.update(res)
return self._get_response(INVALID_BLANK, -1)
break
if flag:
# exist check
sql = 'select * from course where course_id =%s'
cursor.execute(sql, (row[0], ))
flag = sql_util.dictfetchone(cursor)
if flag == None:
msg = "no such course: " + str(row[0])
self._init_response()
self.response.update(res)
return self._get_response(msg, -1)
sql = 'select * from classroom where classroom_no = %s'
cursor.execute(sql, (row[2], ))
test = sql_util.dictfetchone(cursor)
if test == None:
self._init_response()
self.response.update(res)
return self._get_response(
"no such classroom:" + row[2], -1)
# TODO 老师的时空冲突检查 instructor_id 为 row[7]
sql = 'select * from teaches natural join section where instructor_id=%s'
cursor.execute(sql, (row[7], ))
items = sql_util.dictfetchall(cursor)
if items != []:
for item in items:
day = int(item['day'])
start = int(item['start'])
end = int(item['end'])
t_day = int(row[6])
t_start = int(row[3])
t_end = int(row[4])
if day == int(row[6]):
if (start > t_start and start < t_end) or\
(end > t_start and end < t_end):
self._init_response()
self.response.update(res)
return self._get_response(
"instructor teaching time conflict: "
+ str(t_day) + ":" + str(t_start) +
"-" + str(t_end), -1)
# TODO 教室的时空冲突检查 classroom_no 为 row[2]
sql = 'select * from section where classroom_no=%s and day=%s and start=%s and end=%s'
cursor.execute(
sql,
(row[2], int(row[6], int(row[3]), int(row[4]))))
item = sql_util.dictfetchone(cursor)
if item != None:
self._init_response()
self.response.update(res)
return self._get_response(
"classroom time conflict: " + row[2], -1)
sql = 'select * from teaches where course_id = %s and section_id=%s and instructor_id=%s'
cursor.execute(sql, (row[0], int(row[1]), row[7]))
test = sql_util.dictfetchone(cursor)
if test != None:
self._init_response()
self.response.update(res)
return self._get_response(
"data conflict:" + row[0] + "." + row[1], -1)
# same check
sql = 'select * from section where course_id=%s and section_id=%s'
cursor.execute(sql, (row[0], row[1]))
flag = sql_util.dictfetchone(cursor)
if flag == None:
sql = 'insert into section(course_id, section_id, classroom_no, start, `end`, `limit`, `day`)'\
'values(%s,%s,%s,%s,%s,%s,%s)'
cursor.execute(
sql, (row[0], int(row[1]), row[2], int(
row[3]), row[4], int(row[5]), int(row[6])))
# 同时插入teaches表
sql = 'insert into teaches(course_id, section_id, instructor_id)'\
'values(%s,%s,%s)'
cursor.execute(sql, (row[0], int(row[1]), row[7]))
connection.commit()
res['successed_item_num'] += 1
else:
self._init_response()
self.response.update(res)
msg = "data conflict: " + row[0] + "." + row[1]
return self._get_response(msg, -1)
self._init_response()
return self._get_response(IMPORT_OK, 1)
except Exception as error:
traceback.print_exc()
connection.rollback()
self._init_response()
return self._get_response(SERVER_ERROR, -1)
else:
self._init_response()
return self._get_response(INVALID_BLANK, -1)
def execute(self):
if self.request.session['is_login'] != True:
self._init_response()
return self._get_response(UNAUTHORIZED, -1)
try:
course_id = self.data['course_id']
section_id = self.data['section_id']
title = self.data['title']
instructor_name = self.data['instructor_name']
dept_name = self.data['dept_name']
page_num = int(self.data['page_num'])
except Exception as error:
self._init_response()
return self._get_response(GET_ARG_ERROR, -1)
try:
get_dict = self.data.copy()
del get_dict['page_num']
print(get_dict)
cursor = connection.cursor()
sql = 'select * from section natural join course natural join teaches natural join instructor'
cnt_sql = 'select count(*) from section natural join course natural join teaches natural join instructor'
sql_conditions = []
for key in get_dict.keys():
if get_dict[key] != None and get_dict[key] != '':
sql_conditions.append(
str(key + " like '%" + get_dict[key] + "%'"))
if len(sql_conditions) == 0:
final_sql = sql
else:
final_sql = (sql + " where ")
cnt_sql += " where "
for i, condition in enumerate(sql_conditions):
if i != len(sql_conditions) - 1:
final_sql += (condition + " and ")
cnt_sql += (condition + " and ")
else:
final_sql += condition
cnt_sql += condition
final_sql += (" limit " + str(page_num * ITEM_NUM_FOR_ONE_PAGE) +
"," + str(ITEM_NUM_FOR_ONE_PAGE))
print(final_sql)
cursor.execute(final_sql)
rows = sql_util.dictfetchall(cursor)
cursor.execute(cnt_sql)
total_num = int(cursor.fetchone()[0])
print("total num is ", total_num)
if len(rows) != 0:
sections = []
for row in rows:
res = {
'title': row['title'],
'course_id': row['course_id'],
'section_id': row['section_id'],
'dept_name': row['dept_name'],
'instructor_name': row['instructor_name'],
'credits': row['credits'],
'classroom_no': row['classroom_no'],
'day': row['day'],
'start': row['start'],
'end': row['end'],
'limit': row['limit']
}
sections.append(res)
self._init_response()
reses = {
'sections': sections,
'total_num': total_num,
}
self.response.update(reses)
return self._get_response(SEARCH_OK, 1)
else:
self._init_response()
return self._get_response(NO_RESULT, -1)
except Exception as error:
traceback.print_exc()
connection.rollback()
self._init_response()
return self._get_response(SERVER_ERROR, -1)
def execute(self):
if self.request.session['is_login'] != True or self.request.session[
'role'] != STUDENT_ROLE:
self._init_response()
return self._get_response(UNAUTHORIZED_AS_STUDENT)
try:
user_id = self.data['user_id']
course_id = self.data['course_id']
section_id = self.data['section_id']
print("the course id is ", course_id)
print("the section id is ", section_id)
except Exception as error:
self._init_response()
return self._get_response(POST_ARG_ERROR, -1)
try:
cursor = connection.cursor()
find_course_sql = "SELECT 'course'.'course_id' FROM 'course' WHERE 'course'.'course_id' ='" + course_id + "'"
cursor.execute(find_course_sql)
raw_course_id = sql_util.dictfetchone(cursor)
print("raw course id is ", raw_course_id)
if raw_course_id == None:
self._init_response()
return self._get_response(WRONG_COURSE_ID, -1)
find_section_sql = "SELECT * FROM 'section' WHERE 'section'.'course_id' ='" + course_id + "' AND 'section'.'section_id'=" + section_id
cursor.execute(find_section_sql)
raw_section_info = sql_util.dictfetchone(cursor)
if raw_section_info == None:
self._init_response()
return self._get_response(WRONG_SECTION_ID, -1)
find_whether_already_takes_sql = "SELECT * FROM 'takes' WHERE 'takes'.'course_id' = '" + course_id + "' AND 'takes'.'section_id' =" + section_id + " AND 'takes'.'student_id'='" + user_id + "'"
cursor.execute(find_whether_already_takes_sql)
raw_take_info = sql_util.dictfetchone(cursor)
print("raw take info is ", raw_take_info)
if raw_take_info == None:
self._init_response()
return self._get_response(NOT_TAKE, -1)
drop_course_sql = "DELETE FROM 'takes' WHERE ('takes'.'course_id' = '" + course_id + "' AND 'takes'.'section_id' =" + section_id + " AND 'takes'.'student_id' = '" + user_id + "')"
cursor.execute(drop_course_sql)
check_credit_sql = "SELECT 'student'.'student_total_credit' FROM 'student' WHERE 'student'.'student_id' = '" + user_id + "'"
cursor.execute(check_credit_sql)
raw_credit = cursor.fetchone()
print("before update: the raw_credit is : ", raw_credit[0])
find_course_credit_sql = "SELECT 'course'.'credits' FROM 'course' WHERE 'course'.'course_id'='" + course_id + "'"
cursor.execute(find_course_credit_sql)
raw_course_credit = cursor.fetchone()
print("the course credit is ", raw_course_credit[0])
updated_credit = int(raw_credit[0] - raw_course_credit[0])
print("updated credit is", updated_credit)
minus_credits_sql = "UPDATE 'student' SET 'student_total_credit'=" + str(
updated_credit
) + " WHERE 'student'.'student_id'='" + user_id + "'"
cursor.execute(minus_credits_sql)
# application conflict
app_sql = 'select * from application where course_id=%s and section_id=%s and student_id=%s'
cursor.execute(app_sql, (course_id, section_id, user_id))
raw_app = sql_util.dictfetchall(cursor)
if raw_app != None:
sql = 'update application set if_drop=1 where course_id=%s and section_id=%s and student_id=%s'
cursor.execute(sql, (course_id, section_id, user_id))
connection.commit()
self._init_response()
return self._get_response(DROP_OK, 1)
except Exception as error:
traceback.print_exc()
connection.rollback()
self._init_response()
return self._get_response(SERVER_ERROR, -1)
def submitApplication(self):
if self.request.session['is_login'] != True or self.request.session[
'role'] != STUDENT_ROLE:
self._init_response()
return self._get_response(UNAUTHORIZED_AS_STUDENT)
try:
user_id = self.data['user_id']
course_id = self.data['course_id']
section_id = self.data['section_id']
app_reason = self.data['application_reason']
except Exception as error:
self._init_response()
return self._get_response(POST_ARG_ERROR, -1)
try:
cursor = connection.cursor()
sql = 'select * from application where course_id = %s and section_id = %s and student_id = %s'
cursor.execute(sql, (
course_id,
section_id,
user_id,
))
row = sql_util.dictfetchone(cursor)
if row != None and int(row['if_drop']) == 1:
self._init_response()
return self._get_response(APP_DROPPED, -1)
if row != None:
self._init_response()
return self._get_response(APP_ALREADY, -1)
sql = 'select * from takes where course_id = %s and section_id = %s and student_id = %s'
cursor.execute(sql, (
course_id,
section_id,
user_id,
))
row = sql_util.dictfetchone(cursor)
if row != None:
self._init_response()
return self._get_response(APP_SELECTED, -1)
find_take_num_sql = "SELECT COUNT(*) FROM 'takes' WHERE 'takes'.'course_id' = '" + course_id + "' AND 'takes'.'section_id' =" + section_id
cursor.execute(find_take_num_sql)
raw_take_num = cursor.fetchone()
print("the take num is :", raw_take_num[0])
find_section_limit = "SELECT 'section'.'limit' FROM 'section' WHERE 'section'.'course_id'='" + course_id + "' AND 'section'.'section_id' =" + section_id
cursor.execute(find_section_limit)
raw_section_limit = cursor.fetchone()
if raw_section_limit == None:
self._init_response()
return self._get_response("nonexist section ", -1)
print("the section limit is :", raw_section_limit[0])
find_section_capacity = "SELECT capacity FROM classroom NATURAL JOIN section WHERE course_id='" + course_id + "' AND section_id='" + section_id + "'"
cursor.execute(find_section_capacity)
raw_section_capacity = cursor.fetchone()
print("the section capacity is ", raw_section_capacity[0])
if raw_section_limit[0] > raw_take_num[0]:
self._init_response()
return self._get_response(APP_VACANCY, -1)
elif raw_take_num[0] >= raw_section_capacity[0]:
self._init_response()
return self._get_response(APP_CAPACITY, -1)
# section time conflict
sql = 'select * from section where course_id=%s and section_id=%s'
cursor.execute(sql, (
course_id,
section_id,
))
raw_section_info = sql_util.dictfetchone(cursor)
section_day = int(raw_section_info['day'])
section_start_time = int(raw_section_info['start'])
section_end_time = int(raw_section_info['end'])
find_takes_sql = "select * from takes natural join section where student_id = '" + user_id + "'"
print(find_takes_sql)
cursor.execute(find_takes_sql)
takes_info = sql_util.dictfetchall(cursor)
print(takes_info)
for item in takes_info:
tmp_day = int(item['day'])
tmp_start_time = int(raw_section_info['start'])
tmp_end_time = int(raw_section_info['end'])
print(tmp_day, section_day)
if tmp_day == section_day:
print(1)
if (section_start_time >= tmp_start_time and section_start_time <= tmp_end_time) or \
( section_end_time >= tmp_start_time and section_end_time <= tmp_end_time):
self._init_response()
return self._get_response(SECTION_TIME_CONFLICT, -1)
# exam time conflict
sql_exam = 'select * from exam where course_id=%s and section_id=%s'
cursor.execute(sql_exam, (
course_id,
section_id,
))
target = sql_util.dictfetchone(cursor)
print("target", target)
if target != None:
exam_type = int(target['type'])
if exam_type == 0:
exam_day = int(target['exam_day'])
exam_start_time = int(
target['start_time'].split(":")[0]) * 60 + int(
target['start_time'].split(":")[1])
exam_end_time = int(
target['end_time'].split(":")[0]) * 60 + int(
target['end_time'].split(":")[1])
sql = 'select * from takes natural join exam where student_id =%s'
cursor.execute(sql, (user_id, ))
rows = sql_util.dictfetchall(cursor)
print("exam :", rows)
for row in rows:
tmp_type = int(row['type'])
if tmp_type == 0:
tmp_day = int(row['exam_day'])
tmp_start_time = int(
row['start_time'].split(":")[0]) * 60 + int(
row['start_time'].split(":")[1])
tmp_end_time = int(
row['end_time'].split(":")[0]) * 60 + int(
row['end_time'].split(":")[1])
if (exam_start_time >= tmp_start_time and exam_start_time <=tmp_end_time)\
or (exam_end_time >= tmp_start_time and exam_end_time <= tmp_end_time):
self._init_response()
return self._get_response(
EXAM_TIME_CONFLICT, -1)
sql = 'insert into application(course_id,section_id,student_id,application_reason) '\
'values(%s,%s,%s,%s)'
print(sql)
cursor.execute(sql, (
course_id,
section_id,
user_id,
app_reason,
))
connection.commit()
self._init_response()
return self._get_response(HANDLE_OK, 1)
except Exception as error:
traceback.print_exc()
connection.rollback()
self._init_response()
return self._get_response(SERVER_ERROR, -1)
def execute(self):
if self.request.session['is_login'] != True or self.request.session[
'role'] != STUDENT_ROLE:
self._init_response()
return self._get_response(UNAUTHORIZED_AS_STUDENT)
try:
user_id = self.data['user_id']
course_id = self.data['course_id']
section_id = self.data['section_id']
except Exception as error:
self._init_response()
return self._get_response(POST_ARG_ERROR, -1)
try:
cursor = connection.cursor()
find_course_sql = "SELECT 'course'.'course_id' FROM 'course' WHERE 'course'.'course_id' ='" + course_id + "'"
cursor.execute(find_course_sql)
raw_course_id = sql_util.dictfetchone(cursor)
if raw_course_id == None:
self._init_response()
return self._get_response(WRONG_COURSE_ID, -1)
find_section_sql = "SELECT * FROM 'section' WHERE 'section'.'course_id' ='" + course_id + "' AND 'section'.'section_id'=" + section_id
cursor.execute(find_section_sql)
raw_section_info = sql_util.dictfetchone(cursor)
if raw_section_info == None:
self._init_response()
return self._get_response(WRONG_SECTION_ID, -1)
find_whether_already_takes_sql = "SELECT * FROM 'takes' WHERE 'takes'.'course_id' = '" + course_id + "' AND 'takes'.'section_id' =" + section_id + " AND 'takes'.'student_id'='" + user_id + "'"
cursor.execute(find_whether_already_takes_sql)
raw_take_info = sql_util.dictfetchone(cursor)
if raw_take_info != None:
self._init_response()
return self._get_response(ALREADY_SELECT, -1)
find_take_num_sql = "SELECT COUNT(*) FROM 'takes' WHERE 'takes'.'course_id' = '" + course_id + "' AND 'takes'.'section_id' =" + section_id
cursor.execute(find_take_num_sql)
raw_take_num = cursor.fetchone()
find_section_limit = "SELECT 'section'.'limit' FROM 'section' WHERE 'section'.'course_id'='" + course_id + "' AND 'section'.'section_id' =" + section_id
cursor.execute(find_section_limit)
raw_section_limit = sql_util.dictfetchone(cursor)
if raw_section_limit['limit'] <= raw_take_num[0]:
self._init_response()
return self._get_response(NO_VACANCY, -1)
# section time conflict
section_day = int(raw_section_info['day'])
section_start_time = int(raw_section_info['start'])
section_end_time = int(raw_section_info['end'])
find_takes_sql = "select * from takes natural join section where student_id = '" + user_id + "'"
print(find_takes_sql)
cursor.execute(find_takes_sql)
takes_info = sql_util.dictfetchall(cursor)
print(takes_info)
for item in takes_info:
tmp_day = int(item['day'])
tmp_start_time = int(item['start'])
tmp_end_time = int(item['end'])
print(tmp_day, section_day)
if tmp_day == section_day:
if (section_start_time >= tmp_start_time and section_start_time <= tmp_end_time) or \
( section_end_time >= tmp_start_time and section_end_time <= tmp_end_time):
print(section_start_time, section_end_time,
tmp_start_time, tmp_end_time)
self._init_response()
return self._get_response(SECTION_TIME_CONFLICT, -1)
# exam time conflict
sql_exam = 'select * from exam where course_id=%s and section_id=%s'
cursor.execute(sql_exam, (
course_id,
section_id,
))
target = sql_util.dictfetchone(cursor)
exam_type = int(target['type'])
if exam_type == 0:
exam_day = int(target['exam_day'])
exam_start_time = int(
target['start_time'].split(":")[0]) * 60 + int(
target['start_time'].split(":")[1])
exam_end_time = int(
target['end_time'].split(":")[0]) * 60 + int(
target['end_time'].split(":")[1])
sql = 'select * from takes natural join exam where student_id =%s'
cursor.execute(sql, (user_id, ))
rows = sql_util.dictfetchall(cursor)
print("exam :", rows)
for row in rows:
tmp_type = int(row['type'])
if tmp_type == 0:
tmp_day = int(row['exam_day'])
tmp_start_time = int(
row['start_time'].split(":")[0]) * 60 + int(
row['start_time'].split(":")[1])
tmp_end_time = int(
row['end_time'].split(":")[0]) * 60 + int(
row['end_time'].split(":")[1])
if (exam_start_time >= tmp_start_time and exam_start_time <=tmp_end_time)\
or (exam_end_time >= tmp_start_time and exam_end_time <= tmp_end_time):
self._init_response()
return self._get_response(EXAM_TIME_CONFLICT, -1)
# application conflict
app_sql = "select * from application where course_id = '" + course_id + "' and section_id = " + section_id + " and if_drop=1 and student_id = '" + user_id + "'"
cursor.execute(app_sql)
raw_app = sql_util.dictfetchall(cursor)
if raw_app != []:
self._init_response()
return self._get_response(DROP_SELECT_ERROR)
insert_takes_sql = "INSERT INTO 'takes' ('course_id','section_id','student_id','grade') SELECT '" + course_id + "'," + section_id + ",'" + user_id + "', NULL"
cursor.execute(insert_takes_sql)
check_credit_sql = "SELECT 'student'.'student_total_credit' FROM 'student' WHERE 'student'.'student_id' = '" + user_id + "'"
cursor.execute(check_credit_sql)
raw_credit = sql_util.dictfetchone(cursor)
credit_before = raw_credit['student_total_credit']
find_course_credit_sql = "SELECT 'course'.'credits' FROM 'course' WHERE 'course'.'course_id'='" + course_id + "'"
cursor.execute(find_course_credit_sql)
raw_course_credit = sql_util.dictfetchone(cursor)
course_credit = raw_course_credit['credits']
updated_credit = int(credit_before + course_credit)
print("the updated credit is ", updated_credit)
add_credits_sql = "UPDATE 'student' SET 'student_total_credit'=" + str(
updated_credit
) + " WHERE 'student'.'student_id'='" + user_id + "'"
print(add_credits_sql)
cursor.execute(add_credits_sql)
connection.commit()
self._init_response()
return self._get_response(SELECT_OK, 1)
except Exception as error:
traceback.print_exc()
connection.rollback()
self._init_response()
return self._get_response(SERVER_ERROR, -1)