def registerInstructor(self):
if self.request.session['is_login'] != True or self.request.session[
'role'] != ROOT_ROLE:
self._init_response()
return self._get_response(UNAUTHORIZED)
raw_instructor_infos = self.importExcel(self.request)
res = {"successed_item_num": 0}
if raw_instructor_infos != None:
print("the raw_instructor_infos are ", raw_instructor_infos)
try:
cursor = connection.cursor()
for row in raw_instructor_infos:
if (row[0]!=None and row[1]!=None and row[2]!=None and row[3]!=None)\
and (row[0]!='' and row[1]!='' and row[2]!='' and row[3]!=''):
instructor_id, instructor_name, instructor_class, dept_name = row[
0], row[1], row[2], row[3]
# same data check
sql = 'select * from instructor where instructor_id = %s'
cursor.execute(sql, (instructor_id, ))
flag = sql_util.dictfetchone(cursor)
if flag == None:
sql = 'insert into instructor(instructor_id,instructor_name,instructor_class,dept_name)'\
'values(%s,%s,%s,%s)'
cursor.execute(sql,
(instructor_id, instructor_name,
instructor_class, dept_name))
sql = 'insert into account(id, password, role)'\
'values(%s,%s,%s)'
connection.commit()
cursor.execute(sql,
(instructor_id, instructor_id, 2))
res["successed_item_num"] += 1
else:
msg = "data conflict: " + instructor_id
self._init_response()
self.response.update(res)
return self._get_response(msg, -1)
else:
self._init_response()
self.response.update(res)
return self._get_response(INVALID_BLANK, -1)
self._init_response()
self.response.update(res)
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)
def checkInstructorInfo(self, user_id):
try:
cursor = connection.cursor()
check_personal_info_sql = "SELECT * FROM 'instructor' where 'instructor'.'instructor_id'='" + user_id + "'"
cursor.execute(check_personal_info_sql)
instructor_info = sql_util.dictfetchone(cursor)
self._init_response()
self.response.update(instructor_info)
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 registerStudent(self):
if self.request.session['is_login'] != True or self.request.session[
'role'] != ROOT_ROLE:
self._init_response()
return self._get_response(UNAUTHORIZED)
raw_student_scores = self.importExcel(self.request)
res = {"successed_item_num": 0}
if raw_student_scores != None:
print("the student scores are ", raw_student_scores)
try:
cursor = connection.cursor()
for row in raw_student_scores:
if (row[0]!=None and row[1]!=None and row[2]!=None and row[3]!=None)\
and (row[0]!='' and row[1]!='' and row[2]!='' and row[3]!='') :
student_id, student_name, student_major, student_dept_name = str(
int(row[0])), row[1], row[2], row[3]
# same data check
sql = 'select * from student where student_id = %s'
cursor.execute(sql, (student_id, ))
flag = sql_util.dictfetchone(cursor)
if flag == None:
sql = 'insert into student(student_id,student_name,student_major,student_dept_name,student_total_credit)'\
'values(%s,%s,%s,%s,%s)'
cursor.execute(
sql, (student_id, student_name, student_major,
student_dept_name, 0))
sql = 'insert into account(id, password, role)'\
'values(%s,%s,%s)'
cursor.execute(sql, (student_id, student_id, 1))
connection.commit()
res["successed_item_num"] += 1
else:
self._init_response()
msg = "data conflict: " + student_id
self.response.update(res)
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)
def registerCourse(self):
if self.request.session['is_login'] != True or self.request.session[
'role'] != ROOT_ROLE:
self._init_response()
return self._get_response(UNAUTHORIZED)
raw_courses = self.importExcel(self.request)
res = {'successed_item_num': 0}
if raw_courses != None:
print("the courses are ", raw_courses)
try:
cursor = connection.cursor()
for row in raw_courses:
if (row[0]!=None and row[1]!=None and row[2]!=None and row[3]!=None)\
and(row[0]!='' and row[1]!='' and row[2]!='' and row[3]!=''):
course_id, title, credits, dept_name = row[0], row[
1], str(int(row[2])), row[3]
print(course_id, title, credits, dept_name)
# same check
sql = 'select * from course where course_id="%s"'
cursor.execute(sql % (course_id, ))
flag = sql_util.dictfetchone(cursor)
if flag == None:
sql = 'insert into course(course_id,title,credits,dept_name)'\
'values(%s,%s,%s,%s)'
cursor.execute(
sql, (course_id, title, credits, dept_name))
connection.commit()
res['successed_item_num'] += 1
else:
self._init_response()
msg = 'data conflict: ' + course_id
self.response.update(res)
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)
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 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 registerScore(self):
print(self.request.session['role'])
if self.request.session['is_login'] != True or self.request.session[
'role'] != INSTRUCTOR_ROLE:
self._init_response()
return self._get_response(UNAUTHORIZED)
raw_student_scores = self.importExcel(self.request)
if raw_student_scores != None:
print("the student scores are ", raw_student_scores)
try:
cursor = connection.cursor()
res = {"successed_item_num": 0}
for row in raw_student_scores:
if (row[0]!=None and row[1]!=None and row[2]!=None and row[3]!=None)\
and (row[0]!='' and row[1]!='' and row[2]!='' and row[3]!=''):
course_id, section_id, student_id, grade = row[0], int(
row[1]), str(int(row[2])), row[3]
# 1. whether exist conflict
# (1) account
print("the student_id is ", student_id)
sql = 'select * from account where ID=%s'
cursor.execute(sql, (student_id, ))
flag = sql_util.dictfetchone(cursor)
if flag == None:
msg = "no such account: " + str(student_id)
self._init_response()
self.response.update(res)
return self._get_response(msg, -1)
# (2) student
sql = 'select * from student where student_id=%s'
cursor.execute(sql, (student_id, ))
flag = sql_util.dictfetchone(cursor)
if flag == None:
msg = "no such student: " + str(student_id)
self._init_response()
self.response.update(res)
return self._get_response(msg, -1)
# (3) course
sql = 'select * from course where course_id=%s'
cursor.execute(sql, (course_id, ))
flag = sql_util.dictfetchone(cursor)
if flag == None:
msg = "no such course: " + str(course_id)
self._init_response()
self.response.update(res)
return self._get_response(msg, -1)
# (4) section
sql = 'select * from section where section_id=%s and course_id=%s'
cursor.execute(sql, (
section_id,
course_id,
))
flag = sql_util.dictfetchone(cursor)
if flag == None:
msg = "no such section: " + str(
course_id) + "." + str(section_id)
self._init_response()
self.response.update(res)
return self._get_response(msg, -1)
# (5) takes
sql = 'select * from takes where course_id=%s and section_id=%s and student_id=%s'
cursor.execute(sql, (
course_id,
section_id,
student_id,
))
flag = sql_util.dictfetchone(cursor)
if flag == None: # 没有选课这直接报错
msg = "no such taking record: " + str(
course_id) + "." + str(
section_id) + " for " + str(student_id)
self._init_response()
self.response.update(res)
return self._get_response(msg, -1)
else: # 选过课程则更新成绩
sql = 'update takes set grade=%s where course_id=%s and section_id=%s and student_id=%s'
cursor.execute(sql, (
grade,
course_id,
section_id,
student_id,
))
res['successed_item_num'] += 1
else:
self._init_response()
self.response.update(res)
return self._get_response(INVALID_BLANK, -1)
connection.commit()
self._init_response()
self.response.update(res)
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)
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):
try:
user_id = self.data["user_id"]
password = self.data["password"]
# logger.info("user id is ",user_id)
# logger.info("user password is ",password)
except Exception as error:
# logger.error(error)
self._init_response()
return self._get_response(POST_ARG_ERROR,-1)
try:
cursor = connection.cursor()
id_sql = 'select * from account where id = %s'
cursor.execute(id_sql, (user_id,))
row = sql_util.dictfetchone(cursor)
if row is None:
self._init_response()
return self._get_response(WRONG_USERID,-1)
else:
pass_sql = 'select * from account where id = %s and password = %s '
cursor.execute(pass_sql,(user_id,password))
row = sql_util.dictfetchone(cursor)
print(row)
if row is None:
self._init_response()
return self._get_response(WRONG_PASSWD,-1)
else:
role_num = row['role']
if role_num == STUDENT_ROLE:
find_student_name_sql = "SELECT 'student'.'student_name' FROM 'student' WHERE 'student'.'student_id' = '"+user_id+"'"
cursor.execute(find_student_name_sql)
raw_student_name = sql_util.dictfetchone(cursor)
res_name= raw_student_name["student_name"]
# logger.info(res_name)
if role_num == INSTRUCTOR_ROLE:
find_instructor_name_sql = "SELECT instructor_name FROM 'instructor' WHERE 'instructor'.'instructor_id' = '"+user_id+"'"
cursor.execute(find_instructor_name_sql)
raw_instructor_name = sql_util.dictfetchone(cursor)
res_name= raw_instructor_name["instructor_name"]
# logger.info(res_name)
if role_num == ROOT_ROLE:
res_name = "administrator"
find_instructor_name_sql = "SELECT * FROM course_status"
cursor.execute(find_instructor_name_sql)
raw_instructor_name = sql_util.dictfetchone(cursor)
status = raw_instructor_name["status"]
data = {'user_name':res_name,
'course_status': status,
'role':role_num}
self.request.session['user_id'] = user_id
self.request.session['role'] = row['role']
self.request.session['is_login'] = True
print(self.request.session)
self.response.update(data)
self._init_response()
return self._get_response(LOGIN_OK,1)
except Exception as error:
traceback.print_exc()
# logger.error(error)
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)
