def course():
#鉴权
payload_or_error = Auth.identify(Auth, request)
if payload_or_error[0]:
user_info = payload_or_error[1]['data']
user_id = user_info['id']
# print(user_info)
if not user_info['catagory_name'] == "Teacher":
return jsonify(error_response(403, "没有权限"))
else:
return jsonify(error_response(401, payload_or_error[1]))
if request.method == 'GET':
sql = "select * from ((select OpenC.id,openc_time,openc_address,openc_curnum,openc_maxnum,openc_QA_time,course_no,course_name," \
"course_credit,course_hour,course_final_exam_precentage,openc_teacher_id,openc_semester_id " \
"from OpenC left join Course on openc_course_id=Course.id) A left join Semester on " \
"A.openc_semester_id=Semester.id) where openc_teacher_id=%s order by semester_name" %user_id
print(sql)
try:
mutex.acquire()
cursor.execute(sql)
results = cursor.fetchall()
mutex.release()
tmp = []
for i in results:
tmp.append(gen_openC_dict(i))
return jsonify(success_response('查询成功', {'OpenC': tmp}))
except Exception as e:
mutex.release()
print("Error: unable to fetch data")
return jsonify(error_response(400, 'DB发生错误'))
def get_personal_scores():
payload_or_error = Auth.identify(Auth, request)
if payload_or_error[0]:
user_info = payload_or_error[1]['data']
user_id = user_info['id']
if not (user_info['auth']['catagory_information_authority']
and user_info['catagory_name'] == "Student"):
return jsonify(error_response(403, "没有权限"))
else:
return jsonify(error_response(401, payload_or_error[1]))
try:
sql="select course_no,course_name,elevtive_total_grade" \
" from (((select elective_openc_id,elevtive_total_grade from Elective where elective_student_id='%s') A " \
"left join OpenC on A.elective_openc_id=OpenC.id)" \
"left join Course on openc_course_id=Course.id) "%user_id
print(sql)
mutex.acquire()
cursor.execute(sql)
results = cursor.fetchall()
tmp = []
for i in results:
tmp.append(gen_scores_dict(i))
mutex.release()
return jsonify(success_response('查询成功', {'scores': results}))
except Exception as e:
mutex.release()
print(e)
return jsonify(error_response(400, 'DB发生错误'))
def authenticate(self, user_id, PWSD):
"""
用户登录，登录成功返回token，写将登录时间写入数据库；登录失败返回失败原因
:param email:string
:param password:string
:return: dict
"""
sql = "SELECT * FROM User where user_name='%s' and user_password='******'" % (
user_id, PWSD)
try:
mutex.acquire()
cursor.execute(sql)
results = cursor.fetchone()
if results:
sql = "SELECT * FROM Catagory where id='%s'" % (results[1])
cursor.execute(sql)
auth_results = cursor.fetchone()
auth_dict = gen_auth_dict(auth_results[3:])
print(auth_dict)
catagory_name = auth_results[1]
if catagory_name in ['Student', 'Teacher']:
sql = "SELECT * FROM %s left join (select id,department_name from Department) A " \
"on A.id=%s.department_id where %s.id='%s'"%(catagory_name,catagory_name,catagory_name,results[2])
print(sql)
cursor.execute(sql)
user_info_list = cursor.fetchone()
if catagory_name == "Student":
user_info_dict = gen_stu_info_dict(user_info_list[1:])
elif catagory_name == "Teacher":
user_info_dict = gen_teacher_info_dict(
user_info_list[1:])
else:
sql = "SELECT administor_no,administor_name,administor_sex FROM %s where id='%s'" % (
catagory_name, results[2])
print(sql)
cursor.execute(sql)
user_info_list = cursor.fetchone()
user_info_dict = gen_admin_info_dict(user_info_list)
token = self.encode_auth_token(catagory_name, results[2],
auth_dict)
mutex.release()
return success_response(
'登陆成功', {
'token': token.decode(),
'user_info': user_info_dict,
'catagory_name': catagory_name
})
else:
mutex.release()
except Exception as e:
mutex.release()
print(e)
return error_response(400, '登陆失败')
def scores():
#鉴权
payload_or_error = Auth.identify(Auth, request)
if payload_or_error[0]:
user_info = payload_or_error[1]['data']
user_id = user_info['id']
# print(user_info)
if not user_info['catagory_name'] == "Teacher":
return jsonify(error_response(403, "没有权限"))
else:
return jsonify(error_response(401, payload_or_error[1]))
if request.method == 'GET':
openc_id = request.args.get('openc_id', None)
if not openc_id:
return jsonify(error_response(400, 'lack of params'))
sql = "select * from ((select elective_student_id,id,elevtive_regular_grade,elevtive_final_exam_grade,elevtive_total_grade" \
" from Elective where elective_openc_id = '%s')A " \
"left join (select student_no,student_name,student_sex,student_grade,student_tel,id from Student) B " \
"on B.id = A.elective_student_id) "%openc_id
# print(sql)
try:
mutex.acquire()
cursor.execute(sql)
results = cursor.fetchall()
tmp = []
for i in results:
tmp.append(gen_scores_dict(i))
mutex.release()
return jsonify(success_response('查询成功', {'scores': tmp}))
except Exception as e:
mutex.release()
print(e)
if request.method == 'PUT':
_data = request.get_json()
elective_id_list = _data.get("elective_id", None)
elevtive_regular_grade_list = _data.get('elevtive_regular_grade', None)
elevtive_final_exam_grade_list = _data.get('elevtive_final_exam_grade',
None)
if not (elective_id_list and elevtive_regular_grade_list
and elevtive_final_exam_grade_list):
return jsonify(error_response(400, 'lack of params'))
try:
mutex.acquire()
for i in range(len(elective_id_list)):
#查询该课程是否是自己开的
sql="select openc_teacher_id from OpenC where id = " \
"(SELECT elective_openc_id from Elective where id='%s')"%(elective_id_list[i])
cursor.execute(sql)
results = cursor.fetchone()
# print(results)
if user_id != results[0]:
mutex.release()
return jsonify(error_response(403, '没有权限'))
sql="UPDATE Elective set elevtive_regular_grade=%s,elevtive_final_exam_grade=%s " \
"where id=%s"%(float(elevtive_regular_grade_list[i]),float(elevtive_final_exam_grade_list[i]),float(elective_id_list[i]))
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('输入成功'))
except:
mutex.release()
print("Error: unable to fetch data")
return jsonify(error_response('DB发生错误'))
def semester():
#鉴权
payload_or_error = Auth.identify(Auth, request)
if payload_or_error[0]:
user_info = payload_or_error[1]['data']
user_id = user_info['id']
# print(user_info)
if not user_info['catagory_name'] == "Administrator":
return jsonify(error_response(403, "没有权限"))
else:
return jsonify(error_response(401, payload_or_error[1]))
if request.method == 'POST':
semester_name = request.form.get('semester_name', None)
if not semester_name:
return jsonify(error_response(400, 'lack of params'))
try:
mutex.acquire()
sql = "insert into Semester(semester_name, semester_available,semester_able)values ('%s',0,0)" % (
semester_name)
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('增加semester成功'))
except Exception as e:
mutex.release()
print(e)
if request.method == 'PUT':
semester_id = request.form.get('semester_id', None)
semester_name = request.form.get('semester_name', '')
semester_available = request.form.get('semester_available', 0)
semester_able = request.form.get('semester_able', 0)
if not semester_id:
return jsonify(error_response(400, 'lack of params'))
try:
mutex.acquire()
sql = "update Semester set semester_name='%s',semester_available=%s,semester_able=%s where id=%s" % (
semester_name, semester_available, semester_able, semester_id)
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('修改semester成功'))
except Exception as e:
mutex.release()
print(e)
if request.method == 'DELETE':
semester_id = request.form.get('semester_id', None)
if not semester_id:
return jsonify(error_response(400, 'lack of params'))
try:
sql = "select * from OpenC where openc_semester_id = '%s'" % (
int(semester_id))
mutex.acquire()
cursor.execute(sql)
results = cursor.fetchone()
if results:
mutex.release()
return jsonify(error_response('400', '存在openc，该semester不能删除'))
sql = "delete from Semester where id='%s'" % (semester_id)
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('删除semester成功'))
except Exception as e:
mutex.release()
print(e)
return jsonify(error_response(400, 'DB发生错误'))
def course():
#鉴权
payload_or_error = Auth.identify(Auth, request)
if payload_or_error[0]:
user_info = payload_or_error[1]['data']
user_id = user_info['id']
# print(user_info)
if not user_info['catagory_name'] == "Administrator":
return jsonify(error_response(403, "没有权限"))
else:
return jsonify(error_response(401, payload_or_error[1]))
# 获取course
if request.method == 'GET':
course_no = request.args.get('course_no', '') + '%'
try:
mutex.acquire()
sql = "select id,course_name,course_credit,course_hour,course_final_exam_precentage,course_catagory,course_no from Course where course_no like '%s'" % (
course_no)
cursor.execute(sql)
results = cursor.fetchall()
tmp = []
for i in results:
tmp.append(gen_course_info_dict(i))
mutex.release()
return jsonify(success_response('查询成功', {'courseinfo': tmp}))
except Exception as e:
mutex.release()
print(e)
if request.method == 'POST':
course_no = request.form.get('course_no', '')
course_name = request.form.get('course_name', '')
course_credit = request.form.get('course_credit', '')
course_hour = request.form.get('course_hour', '')
department_id = request.form.get('department_id', '')
course_final_exam_precentage = request.form.get(
'course_final_exam_precentage', '')
course_grade = request.form.get('course_grade', '')
course_catagory = request.form.get('course_catagory', '')
try:
mutex.acquire()
sql = "select * from Course where course_no = '%s'" % (course_no)
cursor.execute(sql)
results = cursor.fetchone()
if results:
mutex.release()
return jsonify(error_response('400', '课程号已存在'))
sql = "insert into Course(course_name, course_credit, course_hour, course_department_id, " \
"course_final_exam_precentage, course_grade, course_catagory, course_no) " \
"values ('%s','%s','%s','%s','%s','%s','%s','%s')"\
%(course_name,course_credit,course_hour,department_id,course_final_exam_precentage,course_grade,course_catagory,course_no)
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('增加course成功'))
except Exception as e:
mutex.release()
print(e)
if request.method == "DELETE":
course_id = request.form.get('course_id', '')
try:
mutex.acquire()
sql = "select * from OpenC where openc_course_id = '%s'" % (
course_id)
cursor.execute(sql)
results = cursor.fetchone()
if results:
mutex.release()
return jsonify(error_response('400', '存在openc，该course不能删除'))
sql = "delete from Course where id ='%s'" % course_id
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('删除course成功'))
except Exception as e:
mutex.release()
print(e)
return jsonify(error_response(400, 'DB发生错误'))
def openc():
#鉴权
payload_or_error = Auth.identify(Auth, request)
if payload_or_error[0]:
user_info = payload_or_error[1]['data']
user_id = user_info['id']
# print(user_info)
if not user_info['catagory_name'] == "Administrator":
return jsonify(error_response(403, "没有权限"))
else:
return jsonify(error_response(401, payload_or_error[1]))
if request.method == 'POST':
course_id = request.form.get("course_id", None)
teacher_id = request.form.get('teacher_id', '')
openc_time = request.form.get('openc_time', '')
openc_address = request.form.get('openc_address', '')
openc_maxnum = int(request.form.get('openc_maxnum', ''))
openc_QA_time = request.form.get('openc_QA_time', '')
openc_QA_address = request.form.get('openc_QA_address', '')
try:
mutex.acquire()
# 获取当前学期id
sql = "SELECT id from Semester where semester_available=TRUE "
cursor.execute(sql)
semester_id = cursor.fetchone()[0]
#开课
sql="INSERT INTO OpenC(openc_course_id, openc_semester_id, openc_teacher_id, " \
"openc_time,openc_address,openc_curnum, openc_maxnum, openc_available," \
" openc_QA_time, openc_QA_address) values ('%s','%s','%s','%s','%s',%s,%s,'%s','%s','%s') "\
%(course_id,semester_id,teacher_id,openc_time,openc_address,0,openc_maxnum,1,openc_QA_time,openc_QA_address)
print(sql)
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('开课成功'))
except Exception as e:
mutex.release()
print(e)
if request.method == 'DELETE':
openc_id = request.form.get("openc_id", None)
try:
#删课
sql = "select * from Elective where elective_openc_id='%s'" % openc_id
mutex.acquire()
cursor.execute(sql)
results = cursor.fetchone()
if results:
mutex.release()
return jsonify(error_response('400', '存在已选此课的学生无法删除'))
sql = "DELETE from OpenC where id='%s'" % openc_id
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('删课成功'))
except Exception as e:
mutex.release()
print(e)
return jsonify(error_response(400, 'DB发生错误'))
def student_info():
#鉴权
payload_or_error = Auth.identify(Auth, request)
if payload_or_error[0]:
user_info = payload_or_error[1]['data']
user_id = user_info['id']
# print(user_info)
if not user_info['catagory_name'] == "Administrator":
return jsonify(error_response(403, "没有权限"))
else:
return jsonify(error_response(401, payload_or_error[1]))
# 学号工号模糊查询
if request.method == 'GET':
catagory_name = request.args.get('catagory_name', None)
no = request.args.get('no', '') + '%'
if not catagory_name and catagory_name not in ['Student', 'Teacher']:
catagory_name = 'Student'
if catagory_name == 'Student':
name = 'student_no'
else:
name = 'teacher_no'
sql = "SELECT * FROM %s left join (select id,department_name from Department) A " \
"on A.id=%s.department_id where %s.%s like '%s'" % (catagory_name, catagory_name, catagory_name,name, no)
print(sql)
try:
mutex.acquire()
cursor.execute(sql)
results = cursor.fetchall()
tmp = []
if catagory_name == "Student":
for i in results:
user_info_dict = gen_stu_info_dict(i[1:])
tmp.append(user_info_dict)
elif catagory_name == "Teacher":
for i in results:
user_info_dict = gen_teacher_info_dict(i[1:])
tmp.append(user_info_dict)
# print(tmp)
mutex.release()
return jsonify(success_response('查询成功', {'userinfo': tmp}))
except Exception as e:
mutex.release()
print(e)
return jsonify(error_response(400, '发生错误'))
#新增教师学生
if request.method == 'POST':
catagory_id = int(request.form.get('catagory_id'))
if not catagory_id:
return jsonify(error_response(400, 'lack of params'))
elif catagory_id == 2: #插入教师信息
teacher_no = request.form.get("teacher_no", '')
teacher_name = request.form.get("teacher_name", '')
teacher_sex = request.form.get("teacher_sex", '')
teacher_birthtime = request.form.get("teacher_birthtime",
'') #形如1998-1-1
teacher_education = request.form.get("teacher_education", '')
teacher_salary = request.form.get("teacher_salary", '')
department_id = request.form.get("department_id", '')
teacher_workaddress = request.form.get("teacher_workaddress", '')
password = request.form.get("password", '')
try:
# 查询是否有此工号
sql = "SELECT * from Teacher where teacher_no='%s'" % teacher_no
mutex.acquire()
cursor.execute(sql)
results = cursor.fetchone()
if results != None:
return jsonify(error_response('400', '工号已存在'))
sql = "INSERT INTO Teacher(teacher_no,teacher_name,teacher_sex,teacher_birthtime,teacher_education,teacher_salary,department_id,teacher_workaddress) " \
"values ('%s','%s','%s','%s','%s','%s','%s','%s') " % (
teacher_no, teacher_name, teacher_sex, teacher_birthtime,
teacher_education, teacher_salary,department_id,teacher_workaddress)
print(sql)
cursor.execute(sql)
sql="insert into User(user_catagoty_id, user_catagoty_id_class_id, user_name, user_password) " \
"value ('%s','%s','%s','%s')"%(catagory_id,cursor.lastrowid,teacher_no,password)
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('新增成功'))
except Exception as e:
mutex.release()
print(e)
elif catagory_id == 3:
student_no = request.form.get("student_no", '')
student_name = request.form.get('student_sex', '')
student_sex = request.form.get('student_sex', '')
student_grade = request.form.get('student_grade', '')
student_class = request.form.get('student_class', '')
student_birthtime = request.form.get('student_birthtime', '')
student_birthaddress = request.form.get('student_birthaddress', '')
student_tel = request.form.get('student_tel', '')
department_id = request.form.get('department_id', '')
password = request.form.get('password', '')
try:
# 查询是否有此工号
sql = "SELECT * from Student where student_no='%s'" % student_no
mutex.acquire()
cursor.execute(sql)
results = cursor.fetchone()
if results != None:
return jsonify(error_response('400', '学号已存在'))
sql = "INSERT INTO Student(student_no,student_name,student_sex,student_grade,student_class,student_birthtime," \
"student_birthaddress,student_tel,department_id) " \
"values ('%s','%s','%s','%s','%s','%s','%s','%s','%s') " % (
student_no, student_name, student_sex, student_grade,
student_class, student_birthtime,student_birthaddress,student_tel,department_id)
print(sql)
cursor.execute(sql)
sql="insert into User(user_catagoty_id, user_catagoty_id_class_id, user_name, user_password) " \
"value ('%s','%s','%s','%s')"%(catagory_id,cursor.lastrowid,student_no,password)
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('新增成功'))
except Exception as e:
mutex.release()
print(e)
else:
return jsonify(error_response(400, 'error params'))
#删除用户
if request.method == 'DELETE':
no = request.form.get("no", None)
catagory_id = request.form.get("catagory_id", None)
if not (no and catagory_id):
return jsonify(error_response(400, 'lack of params'))
try:
if catagory_id == 2:
sql = "DELETE from Teacher where teacher_no='%s' " % no
else:
sql = "DELETE from Student where student_no='%s' " % no
mutex.acquire()
cursor.execute(sql)
sql = "delete from User where user_name='%s'" % no
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('删除成功'))
except Exception as e:
mutex.release()
print(e)
return jsonify(error_response(400, 'DB发生错误'))
def courses():
payload_or_error = Auth.identify(Auth, request)
if payload_or_error[0]:
user_info = payload_or_error[1]['data']
user_id = user_info['id']
print(user_info)
if not (user_info['auth']['catagoty_elective_authority']
and user_info['catagory_name'] == "Student"):
return jsonify(error_response(403, "没有权限"))
else:
return jsonify(error_response(401, payload_or_error[1]))
if request.method == 'GET':
sql = "select * from ((select OpenC.id,openc_time,openc_address,openc_curnum,openc_maxnum,openc_available,openc_QA_time,course_no,course_name," \
"course_credit,course_hour,openc_teacher_id " \
"from OpenC left join Course on openc_course_id=Course.id " \
"where openc_semester_id=(select id from Semester where semester_available = true) and " \
"OpenC.id in (select elective_openc_id from Elective where elective_student_id='%s'))A left join " \
"(select id,teacher_name from Teacher )B on A.openc_teacher_id=B.id)"%(user_id)
# print(sql)
try:
mutex.acquire()
cursor.execute(sql)
results = cursor.fetchall()
mutex.release()
tmp = []
for i in results:
tmp.append(gen_openC_dict(i))
return jsonify(success_response('查询成功', {'Elective': tmp}))
except Exception as e:
mutex.release()
print(e)
##判断是否可选课
sql = "select * from Semester where semester_available=true and semester_able=true"
mutex.acquire()
cursor.execute(sql)
result = cursor.fetchone()
mutex.release()
if not result:
return jsonify(error_response(400, '选课时间未到。'))
openc_id = request.form.get("openc_id", None)
if not openc_id:
return jsonify(error_response(400, 'lack of params'))
if request.method == 'POST':
try:
sql = "select * from OpenC where id ='%s' and openc_curnum<openc_maxnum and openc_available=TRUE " % openc_id
mutex.acquire()
cursor.execute(sql)
flag = cursor.fetchone()
if not flag:
mutex.release()
return jsonify(error_response(400, '选课失败，请确定课程处于能被选状态'))
real_time = flag[4]
#查询是否已选此课程
sql="select course_no from Course where id in (select openc_course_id from OpenC where id in " \
"(select elective_openc_id from Elective where elective_student_id='%s') " \
"and openc_semester_id in (select id from Semester where semester_available = true))"%(user_id)
cursor.execute(sql)
courses_no_tuple = cursor.fetchall()
# print(courses_no_tuple)
sql = "select course_no from Course where id in (select openc_course_id from OpenC where id = %s " \
"and openc_semester_id in (select id from Semester where semester_available = true))" % (openc_id)
cursor.execute(sql)
courses_no = cursor.fetchone()
# print(courses_no)
for i in courses_no_tuple:
if i[0] == courses_no[0]:
mutex.release()
return jsonify(error_response(400, '已选此门课程'))
sql = "select openc_time from OpenC where id in " \
"(select elective_openc_id from Elective where elective_student_id='%s')" \
"and openc_semester_id in (select id from Semester where semester_available = true)"%(user_id)
cursor.execute(sql)
time_list = cursor.fetchall()
if not judge_time(real_time, time_list):
mutex.release()
return jsonify(error_response(400, '选课时间冲突'))
#选课
sql = "INSERT INTO Elective(elective_student_id,elective_openc_id) values ('%s','%s') " % (
user_id, openc_id)
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('选课成功'))
except Exception as e:
mutex.release()
print(e)
if request.method == 'DELETE':
try:
# 查询是否已选此课程
sql="SELECT * from Elective left join OpenC on OpenC.id=elective_openc_id where openc_semester_id in (select id from Semester where semester_available = true) " \
"and elective_student_id = '%s' and elective_openc_id = '%s'"%(user_id,openc_id)
mutex.acquire()
cursor.execute(sql)
results = cursor.fetchone()
if results == None:
mutex.release()
return jsonify(error_response(400, '未选此课程'))
#退课
sql = "DELETE from Elective where elective_student_id='%s' and elective_openc_id='%s'" % (
user_id, openc_id)
cursor.execute(sql)
db.commit()
mutex.release()
return jsonify(success_response('退课成功'))
except Exception as e:
mutex.release()
print(e)
return jsonify(error_response(400, 'DB发生错误'))