def admin_addchoice():
q_desc = request.form.get('q_desc')
q_value = request.form.get('q_value')
q_answer = request.form.get('q_answer')
q_diff = request.form.get('q_diff')
q_A = request.form.get('q_A')
q_B = request.form.get('q_B')
q_C = request.form.get('q_C')
q_D = request.form.get('q_D')
q_class = request.form.get('q_class')
table_name = 'choice_question'
sql = 'INSERT INTO ' + choice_question_table + \
'(q_description, q_value, q_answer, q_A, q_B, q_C, q_D, q_counter, q_difficulty, q_year,q_paperid, q_type) ' + \
'VALUES (%s, %s, %s, %s, %s, %s, %s, 0, %s, 2020,30, %s)'
print(q_desc, q_value, q_answer, table_name, q_A, q_B, q_C, q_D, q_class)
try:
cursor.execute(
sql,
(q_desc, q_value, q_answer, q_A, q_B, q_C, q_D, q_diff, q_class))
db_connector.commit()
return jsonify({'success': 1})
except:
print('[admin add choice]', 'insert failed')
db_connector.rollback()
return jsonify({'success': 0})
def submit_paper():
print_log('submit-paper', request.method)
answers = request.form['answers']
exam_id = request.form['exam_id']
print_log('submit paper', answers + ' ' + str(type(answers)))
print_log('submit paper', exam_id)
sql = 'SELECT * FROM ' + exam_paper_table + ' WHERE paper_id = %s'
cursor.execute(sql, exam_id)
data = cursor.fetchone()
# 单选题、多选题、判断题判分
grade, full_grade = common_helper.compare_answer(json.loads(answers),
data.get('paper_path'))
print_log('submit paper', 'grade = ' + str(grade))
print_log('submit paper', 'full grade = ' + str(full_grade))
# 写入数据库
sql = 'INSERT INTO ' + student_exam_log_table + ' VALUES (%s, %s, %s, %s, %s, -1, now())'
print_log('submit paper', 'sql = ' + sql)
try:
cursor.execute(
sql,
(exam_id, session.get('user_id'), str(answers), grade, full_grade))
db_connector.commit()
except:
db_connector.rollback()
return jsonify({'success': 1})
def modify_choice():
q_id = request.form.get('q_id')
q_desc = request.form.get('q_desc')
q_value = request.form.get('q_value')
q_answer = request.form.get('q_answer')
q_difficulty = request.form.get('q_diff')
q_A = request.form.get('q_A')
q_B = request.form.get('q_B')
q_C = request.form.get('q_C')
q_D = request.form.get('q_D')
q_type = request.form.get('q_type')
sql = 'UPDATE ' + choice_question_table + ' SET ' + \
'q_description=%s, q_value=%s, q_answer=%s, q_A=%s, q_B=%s, q_C=%s, q_D=%s, q_difficulty=%s, q_type=%s ' + \
'WHERE q_id = ' + q_id
print(sql)
print(q_id, q_desc, q_value, q_answer, q_A, q_B, q_C, q_D, q_difficulty,
q_type)
try:
cursor.execute(sql, (q_desc, q_value, q_answer, q_A, q_B, q_C, q_D,
q_difficulty, q_type))
db_connector.commit()
return jsonify({'success': 1})
except:
print('[admin modify choice]', 'modify failed')
db_connector.rollback()
return jsonify({'success': 0})
def deleteUser():
if request.method == 'GET':
return 'register-GET'
else:
user_id = request.form['user_id']
sql = ' DELETE FROM ' + user_table + ' WHERE user_id = ' + user_id
try:
flag = cursor.execute(sql)
db_connector.commit()
except Exception as e:
db_connector.rollback()
return jsonify({'success': flag})
def deleteExam():
if request.method == 'GET':
return 'delete exam get method'
else:
exam_id = request.form['exam_id']
print('[delete exam]', exam_id)
sql = ' DELETE FROM {} WHERE paper_id = ' + exam_id
# sql2 = ' DELETE FROM {} WHERE q_paperid = ' + exam_id
try:
cursor.execute(sql.format(teacher_student_table))
cursor.execute(sql.format(student_exam_log_table))
cursor.execute(sql.format(student_exam_log_table))
cursor.execute(sql.format(exam_paper_table))
db_connector.commit()
except:
print('[delete exam]', 'delete exam_paper failed!')
db_connector.rollback()
return jsonify({'success': 1})
def teacher_submit_grade():
print_log('teacher submit grade', request.method)
paper_id = request.form['paper_id']
student_id = request.form['student_id']
subjective_grade = request.form['subjective_grade']
print_log(
'teacher submit grade',
str(paper_id) + ' ' + str(student_id) + ' ' + str(subjective_grade))
sql = 'UPDATE ' + student_exam_log_table + ' SET subjective_grade=%s ' + \
'WHERE paper_id=%s AND student_id=%s'
try:
cursor.execute(sql, (subjective_grade, paper_id, student_id))
db_connector.commit()
except:
db_connector.rollback()
return redirect(url_for('teacher_modify'))
def deleteQuestion():
if request.method == 'GET':
return 'register-GET'
else:
q_id = request.form['q_id']
q_type = int(request.form['q_type'])
table_name = 'choice_question'
if q_type == 2:
table_name = 'judge_question'
elif q_type == 3:
table_name = 'subjective_question'
sql = ' DELETE FROM ' + table_name + ' WHERE q_id = ' + q_id
try:
flag = cursor.execute(sql)
db_connector.commit()
except Exception as e:
db_connector.rollback()
return jsonify({'success': 1})
def generate_paper():
questions = request.form.get('selected_questions')
questions = json.loads(questions)
exam_title = request.form.get('exam_title')
exam_tips = request.form.get('exam_tips')
exam_duration = request.form.get('exam_duration')
exam_datetime = request.form.get('exam_datetime')
exam_class = request.form.get('exam_class')
print('[generate paper]', str(questions), type(questions))
print(exam_title, exam_tips, exam_duration, exam_datetime, exam_class)
output = os.path.join(file_dest,
session.get('user_id') + '-' + exam_title + '.xls')
common_helper.write_paper_file(question_ids=questions, output_file=output)
import teacher_helper
teacher_helper.update_questions_info(questions_ids=questions)
sql = 'INSERT INTO ' + exam_paper_table + exam_paper_columns + \
'VALUES (%s, %s, %s, %s, %s, %s, %s, %s)'
try:
cursor.execute(sql,
(exam_title, exam_tips, exam_duration, exam_datetime, 0,
output, session.get('user_id'), exam_class))
db_connector.commit()
except:
db_connector.rollback()
return jsonify({'success': 0})
# 获取上面新增试卷的ID
sql = 'SELECT max(paper_id) FROM ' + exam_paper_table
cursor.execute(sql)
paper_id = cursor.fetchone().get('max(paper_id)')
# 在 teacher_student 中建立关联
if -1 == sql_helper.insert_teacher_student(
paper_class=exam_class,
teacher_id=session.get('user_id'),
paper_id=paper_id):
return jsonify({'success': 0})
return jsonify({'success': 1})
def register():
print_log('register', request.method)
if request.method == 'GET':
return 'register-GET'
else:
user_id = request.form['user_id']
user_name = request.form['user_name']
user_email = request.form['user_email']
password = request.form['password']
user_type = common_helper.check_type(user_id)
print_log(
'register', user_id + ' ' + user_name + ' ' + user_email + ' ' +
password + ' ' + user_type)
sql = 'INSERT INTO ' + user_table + \
' VALUES (%s, %s, %s, %s, now(), now(), %s)'
try:
flag = cursor.execute(
sql, (user_id, user_name, user_email, password, user_type))
db_connector.commit()
except Exception as e:
db_connector.rollback()
return jsonify({'success': flag})
def admin_add_questions_by_file():
import global_helper
global_helper.cmd_exec('rm -r \"' +
path.join(file_dest, question_file_folder) + '\"')
question_file = request.files['upload_questions_file']
# 数据库中的type，表示科目
q_dbtype = request.form.get('input-q-type')
file_name = 'Admin-Add-Questions.xls'
question_set.save(storage=question_file,
folder=path.join(file_dest, question_file_folder),
name=file_name)
abs_path = path.join(file_dest, question_file_folder, file_name)
questions_list = common_helper.parse_paper(abs_path)
print('[admin add ques by file]', len(questions_list))
print('[admin add ques by file]', q_dbtype)
# current_year = datetime.now().year
current_year = 1970
choice_sql = 'INSERT INTO ' + choice_question_table + \
'(q_description, q_value, q_answer, q_A, q_B, q_C, q_D, q_counter, q_difficulty, q_year, q_type) ' + \
'VALUES (%s, %s, %s, %s, %s, %s, %s, 0, %s, %s, %s)'
judge_subjective_sql = 'INSERT INTO {} ' + \
'(q_description, q_value, q_answer, q_counter, q_difficulty, q_year, q_type)' + \
'VALUES (%s, %s, %s, 0, %s, %s, %s)'
for x in questions_list:
q_type = x.get('q_type')
q_desc = x.get('q_text')
q_val = x.get('value')
q_ans = x.get('answer')
diff = random.randint(1, 3)
if q_type == 'radio' or q_type == 'checkbox':
try:
cursor.execute(
choice_sql,
(q_desc, q_val, q_ans, x.get('A'), x.get('B'), x.get('C'),
x.get('D'), diff, current_year, q_dbtype))
db_connector.commit()
except:
db_connector.rollback()
elif q_type == 'decide':
q_ans = 1 if str(q_ans).upper()[0] == 'T' else 0
try:
cursor.execute(
judge_subjective_sql.format(judge_question_table),
(q_desc, q_val, q_ans, diff, current_year, q_dbtype))
db_connector.commit()
except:
db_connector.rollback()
elif q_type == 'textarea':
try:
cursor.execute(
judge_subjective_sql.format(subjective_question_table),
(q_desc, q_val, q_ans, diff, current_year, q_dbtype))
db_connector.commit()
except:
db_connector.rollback()
return render_template('admin-add-questions.html')
def admin_add_user():
if request.method == 'GET':
name = request.args.get('uname')
id = request.args.get('id')
pwd = request.args.get('pwd')
role = request.args.get('role')
email = request.args.get('email')
else:
name = request.form.get('uname')
id = request.form.get('id')
pwd = request.form.get('pwd')
role = request.form.get('role')
email = request.form.get('email')
print('[admin add user]', name, id, pwd, email, role)
sql = 'INSERT INTO ' + user_table + ' VALUES (%s, %s, %s, %s, now(), now(), %s)'
try:
cursor.execute(sql, (id, name, email, pwd, role))
db_connector.commit()
return jsonify({'success': 1})
except:
print('[admin add user]', 'insert failed')
db_connector.rollback()
return jsonify({'success': 0})
def add_A_question():
q_desc = request.form.get('q_desc')
q_value = request.form.get('q_value')
q_answer = request.form.get('q_answer')
q_diff = request.form.get('q_diff')
q_type = int(request.form.get('q_type'))
q_class = request.form.get('q_class')
table_name = 'judge_question'
if q_type == 3:
table_name = 'subjective_question'
sql = 'INSERT INTO ' + table_name + \
'(q_description, q_value, q_answer, q_counter, q_difficulty, q_year,q_paperid,q_type)' + \
'VALUES (%s, %s, %s, 0, %s,2020,30, %s)'
print(q_desc, q_value, q_answer, table_name)
print(sql, (q_desc, q_value, q_answer, q_diff))
try:
cursor.execute(sql, (q_desc, q_value, q_answer, q_diff, q_class))
db_connector.commit()
return jsonify({'success': 1})
except:
print('[admin add user]', 'insert failed')
db_connector.rollback()
return jsonify({'success': 0})
def update_questions_info(questions_ids: dict):
now_year = datetime.now().year
sql = 'UPDATE {} SET q_year=%s, q_counter=q_counter+1 WHERE q_id=%s'
for x in questions_ids.get('choice'):
try:
cursor.execute(sql.format(choice_question_table), (now_year, x))
db_connector.commit()
except:
db_connector.rollback()
for x in questions_ids.get('judge'):
try:
cursor.execute(sql.format(judge_question_table), (now_year, x))
db_connector.commit()
except:
db_connector.rollback()
for x in questions_ids.get('subjective'):
try:
cursor.execute(sql.format(subjective_question_table),
(now_year, x))
db_connector.commit()
except:
db_connector.rollback()
def uploadFile():
user_id = session.get('user_id')
if user_id is None:
return '请先登录！'
paper_title = request.form.get('exam-title-input')
paper_desc = request.form.get('exam-desc-input')
paper_time = request.form.get('exam-time-input')
paper_date = request.form.get('exam-date-input')
paper_open = (request.form.get('optionsRadios') == 'open-paper')
paper_file = request.files['exam-file-input']
paper_class = request.form.get('exam-class-input')
print('paper-title', paper_title, type(paper_title))
print('paper-desc', paper_desc, type(paper_desc))
print('paper-time', paper_time, type(paper_time))
print('paper-date', paper_date, type(paper_date))
print('paper-open', paper_open, type(paper_open))
print('paper-file', paper_file, type(paper_file))
print('paper-class', paper_class)
# 上传文件到项目路径下的 upload_path / paper_path
paper_set.save(paper_file,
name=session.get('user_id') + '-' + paper_title + '.xlsx')
# 插入 exam_paper 表
# (paper_title, paper_desc, paper_time, paper_date, paper_open, paper_path, paper_userid)
file_path = path.join(base_path, upload_path, paper_path,
session.get('user_id') + '-' + paper_title + '.xlsx')
sql = 'insert into ' + exam_paper_table + exam_paper_columns + \
'values' + '(%s, %s, %s, %s, %s, %s, %s, %s)'
try:
cursor.execute(sql, (paper_title, paper_desc, paper_time, paper_date,
paper_open, file_path, user_id, paper_class))
db_connector.commit()
except Exception as e:
db_connector.rollback()
# 获取上面新增试卷的ID
sql = 'SELECT max(paper_id) FROM ' + exam_paper_table
cursor.execute(sql)
paper_id = cursor.fetchone().get('max(paper_id)')
print_log('upload files', str(paper_id))
# 在 teacher_student 中建立关联
for c in paper_class.split(';'):
if c != '':
sql = 'SELECT * FROM `user` WHERE user_id LIKE %s'
cursor.execute(sql, c + '%')
students = cursor.fetchall()
for s in students:
print_log('upload files', s.get('user_id'))
sql = 'INSERT INTO ' + teacher_student_table + ' VALUES (%s,%s,%s)'
try:
cursor.execute(sql, (user_id, s.get('user_id'), paper_id))
db_connector.commit()
except:
db_connector.rollback()
# 插入试题数据库
sql_helper.insert_questions(paper_id=paper_id,
paper_path=file_path,
paper_title=paper_title)
return redirect(url_for('teacherIndex'))