def login_session():
if request.method == 'POST':
# Valores obtenidos por el metodo
rut_entrada = request.form['rut']
contraseña_entrada = request.form['contraseña']
print('usuario: ', rut_entrada)
print('contraseña: ', contraseña_entrada)
# Query donde se obtienen los datos del usuario
query = ('''
SELECT
rut,
contraseña,
credencial,
email
FROM Usuario
WHERE rut = %s;
''')
cursor = connection.cursor()
cursor.execute(query, (rut_entrada, ))
resultado = cursor.fetchall()
# Si los datos para ingresar son incorrectos redirigira al login y enviara un mensaje
if (resultado == []):
flash('El usuario o la contraseña estan mal xD')
return redirect('/victor/login')
# Si la contraseña es incorrecta
elif (contraseña_entrada != resultado[0][1]):
flash('El usuario o la contraseña estan mal xD')
return redirect('/victor/login')
else:
return 'perfil'
return 'OK'
def previwe_paper():
print_log('preview paper', request.method)
exam_id = request.args.get('exam_id')
if session.get('user_id').upper() == common_helper.admin_type:
teacher_id = request.args.get('user_id')
else:
teacher_id = session.get('user_id')
sql = 'SELECT paper_path, paper_title, paper_time, paper_date FROM ' + \
exam_paper_table + ' INNER JOIN ' + user_table + ' ON exam_paper.paper_userid=user.user_id ' + \
'WHERE paper_id=%s and paper_userid=%s'
cursor.execute(sql, (exam_id, teacher_id))
data = cursor.fetchone()
exam_dict = {
'title': data.get('paper_title'),
'exam_id': exam_id,
'duration': data.get('paper_time'),
'date': data.get('paper_date')
}
if session.get('user_id').upper() == 'ADMIN':
return render_template('admin-preview-paper.html',
question=common_helper.parse_paper(
data.get('paper_path')),
exam=exam_dict)
else:
return render_template('preview-paper.html',
question=common_helper.parse_paper(
data.get('paper_path')),
exam=exam_dict)
def get_question_list_by_ids(ids_list: list, table: str):
sql = 'SELECT * FROM ' + table + 'WHERE q_id=%s'
result = []
for x in ids_list:
cursor.execute(sql, x)
result.append(cursor.fetchone())
return result
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 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 modifyPwd():
user_id = session.get('user_id')
if user_id is None:
return '请先登录！'
sql = 'SELECT * FROM user WHERE user_id = \'' + user_id + '\' '
cursor.execute(sql)
userdata = cursor.fetchall()
password = userdata[0].get('user_password')
oldpwd = request.form.get('old_pwd')
newpwd = request.form.get('new_pwd')
conpwd = request.form.get('con_pwd')
if password != oldpwd:
return '原密码不正确！'
if newpwd != conpwd:
return '确认密码不一致！'
if len(newpwd) < 8:
return '密码长度不足8位！'
sql = 'update user set user_password = \'' + newpwd + '\' where user_id = \'' + user_id + '\' '
cursor.execute(sql)
if user_id[0] == 'T':
return redirect(url_for('teacher_personal_info'))
else:
return redirect(url_for('student_personal_info'))
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 iniciar_sesion():
datos_usuario = request.form.to_dict(
) # Se obtienen los datos del formulario
# Se codifica la password ingresada en el formulario para comparación de hash
datos_usuario["password"] = datos_usuario["password"].encode(
encoding="UTF-8")
# Se obtienen los datos del colaborador (contraseña --> hash de contraseña)
sql_query = """
SELECT rut,id_credencial,email,contraseña
FROM Usuario
WHERE rut = '%s'
""" % (datos_usuario["rut"])
cursor.execute(sql_query)
# Se obtienen los datos asociados al rut ingresado en el formulario
datos_usuario_registrado = cursor.fetchone()
# Si no se obtiene un registro, entonces el rut no se encuentra registrado en el sistema
if datos_usuario_registrado is None:
flash(
"credenciales-invalidas"
) # Se notifica al front-end acerca del error para alertar al usuario
return redirect(url_for("rutas_seba.principal"))
return "Cuenta existente"
def get_from_main(city_name):
city = [(str(city_name))]
cursor.execute("SELECT * FROM main_cities WHERE city_name = ?", city)
if cursor.fetchone() is None:
return False
else:
return True
def admin():
if session.get('user_id') == 1:
sql = 'select * from user_grade order by user_name,paper_id'
cursor.execute(sql, )
user_grade_data = cursor.fetchall()
return render_template('admin.html', user_grade_data=user_grade_data)
else:
return render_template('temp.html')
def get_log(id):
sql = ("SELECT * FROM logs where id = %s order by created ")
try:
cursor.execute(sql, (id, ))
result = cursor.fetchone()
for row in result:
print(row)
except TypeError:
print('Entered id is not present in table')
def result():
if session.get('user_id'):
sql = 'select * from user_grade where user_id = %s'
cursor.execute(sql, (session.get('user_id'), ))
user_grade = cursor.fetchall()
return render_template('results.html', user_grade=user_grade)
else:
return render_template('temp.html')
def checkSession():
user_id = session.get('user_id')
if user_id:
sql = 'select realname from users where id=%s'
cursor.execute(sql, (user_id, ))
return jsonify({
'success': 1,
'username': cursor.fetchone().get('name')
})
return jsonify({'success': 0})
def student_personal_info():
user_id = session.get('user_id')
sql = 'SELECT * FROM ' + user_table + ' WHERE user_id=%s'
cursor.execute(sql, user_id)
data = cursor.fetchone()
u = dict()
u['user_id'] = user_id
u['user_name'] = data.get('user_name')
u['user_email'] = data.get('user_email')
return render_template('student-personInfo.html', person=u)
def my_context():
user_id = session.get('user_id')
if user_id:
sql = 'select realname from users where id = %s'
cursor.execute(sql, (user_id, ))
name = cursor.fetchone().get('realname')
return {
'name': name,
}
else:
return {}
def index():
male_num = "select count(Sno) from S where Sgender='男'"
female_num = "select count(Sno) from S where Sgender='女'"
cursor.execute(male_num)
male_num = cursor.fetchall()[0][0]
cursor.execute(female_num)
female_num = cursor.fetchall()[0][0]
dict = {'male_num': male_num, 'female_num': female_num}
return render_template('cms/index.html', **dict)
def enviar_recuperacion_password():
# Se obtienen los datos del formulario
datos_recuperacion = request.form.to_dict()
datos_recuperacion["identificacion_usuario"] = db.converter.escape(
datos_recuperacion["identificacion_usuario"])
# Se revisa si el RUT o correo coincide con el registro de usuarios
sql_query = """
SELECT nombres,email
FROM Usuario
WHERE rut = '%s'
OR email = '%s'
""" % (datos_recuperacion["identificacion_usuario"],
datos_recuperacion["identificacion_usuario"])
cursor.execute(sql_query)
datos_usuario = cursor.fetchone()
# Si el correo o el rut no se encuentran registrados, se alerta al usuario
if datos_usuario is None:
flash(
"recuperacion-invalida"
) # Se notifica al front-end acerca del error para alertar al usuario
return redirect(url_for("rutas_seba.recuperacion_password"))
# En caso de existir registro, se envía el correo de recuperación y se alerta al usuario
# Se abre el template HTML correspondiente al restablecimiento de contraseña
direccion_template = os.path.normpath(
os.path.join(
os.getcwd(),
"app/templates/vistas_exteriores/recuperacion_password_mail.html"))
html_restablecimiento = open(direccion_template, encoding="utf-8").read()
# Se crea el mensaje
correo = MIMEText(html_restablecimiento, "html")
correo.set_charset("utf-8")
correo["From"] = "*****@*****.**"
correo["To"] = datos_usuario["email"]
correo["Subject"] = "Prueba - Sistema LabEIT UDP"
try:
server = smtplib.SMTP("smtp.gmail.com", 587)
server.starttls()
server.login("*****@*****.**", "LabEIT_UDP_2020")
str_correo = correo.as_string()
server.sendmail("*****@*****.**", datos_usuario["email"],
str_correo)
server.close()
return "OK"
except Exception as e:
return str(e)
def create_tables():
cursor.execute("""CREATE TABLE IF NOT EXISTS vacancy(
id TEXT PRIMARY KEY,
v_name TEXT,
salary_from INT,
salary_to INT);""")
cursor.execute("""CREATE TABLE IF NOT EXISTS main_cities(
city_name TEXT PRIMARY KEY,
salary_from INT,
salary_to INT,
avg_salary INT);""")
def max_sal():
# находим и выводим максимальную зарплату с вакансией
cursor.execute("SELECT MAX(salary_from) FROM vacancy")
result1 = cursor.fetchone()
cursor.execute("SELECT MAX(salary_to) FROM vacancy")
result2 = cursor.fetchone()
if result1 > result2:
return result1[0]
else:
return result2[0]
def min_sal():
# находим и выводим минимсальную зарплату с вакансией
cursor.execute("SELECT MIN(salary_from) FROM vacancy")
result1 = cursor.fetchone()
cursor.execute("SELECT MIN(salary_to) FROM vacancy")
result2 = cursor.fetchone()
if result1 < result2:
return result1[0]
else:
return result2[0]
def create_table(dbname):
cursor.execute(f'USE {dbname}')
for table_name in TABLES:
table_description=TABLES[table_name]
try:
cursor.execute(table_description)
print(f"Creating table {table_name}",end='')
except mysql.connector.Error as err:
if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
print("Already exists")
else:
print(err.msg)
def teacher_personal_info():
user_id = session.get('user_id')
sql = 'SELECT * FROM user WHERE user_id = \'' + user_id + '\' '
user_dict = {'user_id:': '', 'user_name': '', 'user_email': ''}
cursor.execute(sql)
userdata = cursor.fetchall()
u = dict(user_dict)
u['user_id'] = userdata[0].get('user_id')
u['user_name'] = userdata[0].get('user_name')
u['user_email'] = userdata[0].get('user_email')
return render_template('teacher-personInfo.html', person=u)
def makepaper():
for index, item in enumerate(analyze()):
answer = list(map(decidetra, getAnswer()))[index]
sql = 'select count(*) as last_id from docx'
cursor.execute(sql)
paper_id = cursor.fetchone().get('last_id')
sql = 'insert into questions (q_text,q_type,q_value,A,B,C,D,paper_id,answer) values (%s,%s,%s,%s,%s,%s,%s,%s,%s)'
if index <= 23:
cursor.execute(
sql, (item.get('question'), 'radio', 1.5, item.get('items')[0],
item.get('items')[1], item.get('items')[2],
item.get('items')[3], paper_id, answer))
db.commit()
if index > 23 and index < 36:
cursor.execute(
sql,
(item.get('question'), 'checkbox', 2, item.get('items')[0],
item.get('items')[1], item.get('items')[2],
item.get('items')[3], paper_id, answer))
db.commit()
if index >= 36:
cursor.execute(sql, (item.get('question'), 'decide', 1, '1', 0, 0,
0, paper_id, answer))
db.commit()
def get_todo_list(self):
sql = "select * from todo;"
n = cursor.execute(sql)
if not n:
return []
data = cursor.fetchall()
return TodoModel.load(data)
def get_todo_detail(self, todo_id):
sql = "select * from todo where id = %s;"
n = cursor.execute(sql)
if not n:
return None
data = cursor.fetchall()
return TodoModel.load(data)[0]
def login():
print_log('login', request.method)
if request.method == 'GET':
return 'login-GET'
else:
user_id = str(request.form['UserIDLogin'])
password = str(request.form['PasswordLogin'])
query = 'select * from ' + user_table + ' where user_id = %s'
flag = cursor.execute(query, user_id)
# flag = 1, 账号正确
if flag:
result = cursor.fetchone()
data = {
'success': 1,
'user_id': result.get('user_id'),
'user_name': result.get('user_name'),
'user_type': result.get('user_type')
}
session['user_id'] = data.get('user_id')
print_log('login', str(data))
if str(result['user_password']) == password:
return jsonify(data)
else:
data['success'] = 0
return jsonify(data)
# flag = 0, 账号错误
else:
return jsonify({'success': -1})
def my_context():
user_id = session.get('user_id') if session.get(
'user_id') is not None else ''
if user_id != '':
sql = 'select user_name, user_type from ' + user_table + ' where `user_id` = %s'
cursor.execute(sql, user_id)
result = cursor.fetchone()
user_name = result.get('user_name')
user_type = result.get('user_type')
print_log('my context', str(user_name) + str(user_type) + str(user_id))
return {
'user_id': user_id,
'user_name': user_name,
'user_type': user_type
}
else:
return {}
def check_id() -> object:
if request.method == 'GET':
return 'checkRegisterStudentID-GET'
else:
user_id = request.form['user_id']
query = 'select * from ' + user_table + ' where user_id = %s'
flag = cursor.execute(query, user_id)
return jsonify({'has': flag})
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 checkEmail():
if request.method == 'GET':
return 'GET'
else:
email = request.form['email']
sql = "select * from users where email=%s"
res = cursor.execute(sql, (email, ))
if res:
return jsonify({'has': 1})
return jsonify({'has': 0})
def start_exam():
print_log('start-exam', request.method + request.args.get('exam_id'))
exam_id = request.args.get('exam_id')
sql = 'SELECT * FROM ' + exam_paper_table + ' WHERE paper_id=%s'
cursor.execute(sql, str(exam_id))
data = cursor.fetchone()
sql2 = 'SELECT user_name FROM ' + user_table + ' WHERE user_id=%s'
cursor.execute(sql2, data.get('paper_userid'))
data2 = cursor.fetchone()
question = common_helper.parse_paper(data.get('paper_path'))
exam = {
'exam_id': exam_id,
'title': data.get('paper_title'),
'duration': data.get('paper_time'),
'teacher': data2.get('user_name')
}
return render_template('exam.html', question=question, exam=exam)
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})
