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 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_Semester():
sql = 'select * from Semester'
cursor.execute(sql)
results = cursor.fetchall()
tmp = []
for i in results:
tmp.append(gen_Semester_dict(i))
return jsonify(success_response(tmp))
def get_catagory():
sql = "select id,catagory_name from Catagory where id in (2,3)"
# print(sql)
cursor.execute(sql)
results = cursor.fetchall()
tmp = []
for i in results:
tmp.append(gen_catagory_dict(i))
return jsonify(success_response(tmp))
def get_department():
department_id = request.args.get('department_id', '') + '%'
sql = "select * from Department where id like '%s'" % department_id
cursor.execute(sql)
results = cursor.fetchall()
tmp = []
for i in results:
tmp.append(gen_Department_dict(i))
return jsonify(success_response(tmp))
def get_teacher():
teacher_id = request.args.get('teacher_id', None)
if not teacher_id:
sql = "select Teacher.id,teacher_no,teacher_name,teacher_sex,teacher_education,department_name,teacher_workaddress" \
" from Teacher left join Department on department_id=Department.id"
cursor.execute(sql)
results = cursor.fetchall()
tmp = []
for i in results:
tmp.append(gen_Teacher_dict1(i))
return jsonify(success_response(200, tmp))
sql = "select teacher_no,teacher_name,teacher_sex,teacher_education,department_name,teacher_workaddress" \
" from Teacher left join Department on department_id=Department.id where Teacher.id= '%s'"%teacher_id
# print(sql)
cursor.execute(sql)
results = cursor.fetchall()
tmp = []
for i in results:
tmp.append(gen_Teacher_dict(i))
return jsonify(success_response(tmp))
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 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 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发生错误'))