def pieChart() -> Pie:
db = get_db()
df = db.fetchall('SELECT * FROM chapter_info')
df = df.sort_values(by="id", ascending=True)
chapters = df.name.tolist()
dict = {}
for chapter in chapters:
dict[chapter] = 0
if (g.user != '{}') and (g.user is not None):
user_id = json.loads(g.user)['id']
df = db.fetchall(
'SELECT res_id FROM res_history WHERE user_id="{user_id}" AND operation=2'
.format(user_id=user_id))
for _, row in df.iterrows():
df = db.fetchall(
'SELECT chapter_id FROM res_info WHERE id="{res_id}"'.format(
res_id=row['res_id']))
df = db.fetchall(
'SELECT name FROM chapter_info WHERE id="{chapter_id}"'.format(
chapter_id=df.chapter_id[0]))
chapter_name = df.name[0]
dict[chapter_name] += 1
data = []
for (k, v) in dict.items():
data.append((k, v))
c = (Pie().add(
"", data).set_global_opts(title_opts=opts.TitleOpts()).set_series_opts(
label_opts=opts.LabelOpts(formatter="{b}: {c}")))
close_db()
return c.dump_options_with_quotes()
def manageDevice():
params = {}
sql = 'SELECT * FROM `device_info` ORDER BY id'
db = get_db()
results = db.fetchall(sql)
close_db()
items = []
for _, row in results.iterrows():
items.append({
'id': row['id'],
'name': row['name'],
'address': row['address'],
'minAngle': row['minAngle'],
'maxAngle': row['maxAngle'],
'minValue': row['minValue'],
'maxValue': row['maxValue'],
'unit': row['unit'],
'description': row['description']
})
params['deviceItems'] = items
dicts = {'title': 'Manage Device', 'params': params}
return render_template('/config/manageDevice.html', **dicts)
def record_res_history(context,
user_ip,
operation,
rating="null",
difficulty="null"):
db = get_db()
if (g.user != '{}') and (g.user is not None):
user_id = json.loads(g.user)['id']
df = db.fetchall(
'SELECT id FROM res_info WHERE context="{context}"'.format(
context=context))
res_id = df.id[0]
db.execute(
'INSERT INTO res_history (user_id, user_ip, res_id, operation, time, rating, difficulty) VALUES ({user_id}, "{user_ip}", {res_id}, {operation}, now(), {rating}, {difficulty})'
.format(user_id=user_id,
user_ip=user_ip,
res_id=res_id,
operation=operation,
rating=rating,
difficulty=difficulty))
db.commit()
close_db()
def register():
if request.method == 'POST':
info = {}
data = request._dict
if data.get('username') and data.get('password'):
# print(type(data.get('password'))) type
username = data.get('username')
password = data.get('password')
db = get_db()
try:
with db.cursor() as cur:
record_num = cur.execute(
"SELECT * FROM user WHERE username=%s", (username, ))
if record_num == 0:
cur.execute(
"INSERT INTO user(username,password) VALUES (%s,%s)",
(username, generate_password_hash(password)))
db.commit()
info = {"success": True, 'data': '注册成功'}
else:
info = {"success": False, "data": '用户名已存在！'}
finally:
close_db()
else:
info = {"success": False, "data": '用户名和密码不能为空！'}
return json.dumps(info)
else:
return json.dumps({'success': False, 'data': '请使用post请求'})
def record_sp_exe_history(context,
user_ip,
operation,
ans="null",
difficulty="null",
answer_easy_if="null"):
db = get_db()
if (g.user != '{}') and (g.user is not None):
user_id = json.loads(g.user)['id']
df = db.fetchall(
'SELECT id FROM sp_exe_info WHERE context="{context}"'.format(
context=context))
sp_exe_id = df.id[0]
db.execute(
'INSERT INTO sp_exe_history (user_id, user_ip, sp_exe_id, operation, time, ans, difficulty, answer_easy_if) VALUES ({user_id}, "{user_ip}", {sp_exe_id}, {operation}, now(), {ans}, {difficulty}, {answer_easy_if})'
.format(user_id=user_id,
user_ip=user_ip,
sp_exe_id=sp_exe_id,
operation=operation,
ans=ans,
difficulty=difficulty,
answer_easy_if=answer_easy_if))
db.commit()
close_db()
def check_ans():
if request.method == 'POST':
context = request.form["context"]
ans = request.form["ans"]
if (context != "" and ans != ""):
db = get_db()
record_exe_history(context=context,
user_ip=request.remote_addr,
operation=2,
ans=ans)
df = db.fetchall(
'SELECT ans FROM exe_info WHERE context="{context}"'.format(
context=context))
correct_ans = df.ans[0]
close_db()
if ans == correct_ans:
return 'correct'
else:
return 'wrong'
else:
return 'unselected'
else:
return 'error'
def register():
record_page_history(pagepath='/auth/register', user_ip=request.remote_addr)
if request.method == 'POST':
db = get_db()
username = request.form['username']
student_id = request.form['student_id']
password = request.form['password']
password_confirm = request.form['password_confirm']
reg_ip = request.remote_addr
error = None
if not username:
error = 'Please enter the username!'
elif ' ' in username:
error = 'The username cannot include spaces!'
elif not re.match(r'^\d{10}$', student_id):
error = 'Please enter correct BUPT student ID!'
elif not password:
error = 'Please enter the password!'
elif len(password) < 8:
error = 'The password must be longer than 8 characters.'
elif password_confirm != password:
error = 'Please confirm the password!'
elif len(
db.fetchall(
'SELECT id FROM user_info WHERE username = "******"'.
format(username=username))) > 0:
error = 'User {username} exists, please try again with another username.'.format(
username=username)
elif len(
db.fetchall(
'SELECT id FROM user_info WHERE student_id = "{student_id}"'
.format(student_id=student_id))) > 0:
error = 'Student ID {student_id} has signed up. If there are any problems, please contact administrator or teacher.'.format(
student_id=student_id)
if error is None:
db.execute(
'INSERT INTO user_info (username, student_id, password, reg_ip, reg_time)'
+
'VALUES ("{username}", {student_id}, "{password}", "{reg_ip}", {reg_time})'
.format(username=username,
student_id=student_id,
password=generate_password_hash(password),
reg_ip=reg_ip,
reg_time='now()'))
db.commit()
flash(
'Success to sign up! Welcome.{username}！'.format(
username=username), 'success')
close_db()
return redirect(url_for('page.index'))
flash(error, 'error')
close_db()
return render_template('/auth/register.html')
def testDatabase(username, password):
try:
db = get_db(username=username, password=password)
close_db()
return True
except Exception as e:
logging.warning(e)
return False
def initDatabase():
try:
db = get_db()
db.create()
close_db()
return 'Ok'
except Exception as err:
if err.args[0] != 1065:
return str(err)
else:
return 'Ok'
def login():
record_page_history(pagepath='/auth/login', user_ip=request.remote_addr)
if request.method == 'POST':
db = get_db()
username = request.form['username']
password = request.form['password']
os = request.form['os']
browser = request.form['browser']
resolution = request.form['resolution']
error = None
user = db.fetchall(
'SELECT * FROM user_info WHERE username = "******"'.format(
username=username))
if len(user) == 0:
error = 'The user have not been created, please sign up first.'
elif not check_password_hash(user['password'][0], password):
error = 'Invalid password, please check your password.'
if error is None:
session.clear()
user_id = int(user['id'][0])
session['user_id'] = user_id
# Set the id_active as true
db.execute(
'UPDATE user_info SET id_active = 1 WHERE id = {user_id}'.
format(user_id=user_id))
# Update the table: user_auth_history
db.execute(
'INSERT INTO user_auth_history (user_id, user_ip, operation, time, os, browser, resolution)'
+
'VALUES ("{user_id}", "{user_ip}", "{operation}", {time}, "{os}", "{browser}", "{resolution}")'
.format(user_id=user_id,
user_ip=request.remote_addr,
operation=1,
time='now()',
os=os,
browser=browser,
resolution=resolution))
db.commit()
flash('You have signed in!', 'success')
close_db()
return redirect(url_for('page.index'))
flash(error, 'error')
close_db()
return render_template('/auth/login.html')
def deleteDevice():
device_id = request.form['device_id']
sql = "DELETE FROM `device_info` WHERE `id`={device_id}".format(
device_id=device_id)
db = get_db()
db.execute(sql)
db.commit()
close_db()
os.system("sh ocr.sh")
return 'Ok'
def logout():
db = get_db()
user_id = session['user_id']
session.clear()
db.execute(
'INSERT INTO user_auth_history (user_id, user_ip, operation, time)' +
'VALUES ("{user_id}", "{user_ip}", "{operation}", {time})'.format(
user_id=user_id,
user_ip=request.remote_addr,
operation=0,
time='now()'))
db.commit()
flash('You have logged out!', 'success')
close_db()
return redirect(url_for('page.index'))
def data():
device_id = request.form['id']
db = get_db()
sql = 'SELECT * FROM `records` WHERE `device_id`={device_id} ORDER BY id'.format(
device_id=device_id)
result = db.fetchall(sql)
date = []
value = []
for _, row in result.iterrows():
date.append("{:%Y-%m-%d %H:%M:%S}".format(row['time'].to_pydatetime()))
value.append(row['value'])
close_db()
c = line_smooth(date[-10:], value[-10:])
return c.dump_options_with_quotes()
def saveDevice():
name = request.form['deviceName']
address = request.form['deviceAddress']
type = request.form['deviceType']
num = request.form['deviceNum']
minAngle = request.form["minAngle"]
maxAngle = request.form["maxAngle"]
minValue = request.form["minValue"]
maxValue = request.form["maxValue"]
x = request.form["x"]
y = request.form["y"]
r = request.form["r"]
try:
unit = "'" + request.form["unit"] + "'"
except:
unit = "NULL"
try:
description = "'" + request.form["description"] + "'"
except:
description = "NULL"
sql = "INSERT INTO `device_info` (`name`, `address`, `type`, `num`, `minAngle`, `maxAngle`, `minValue`, `maxValue`, `unit`, `description`, `x`, `y`, `r`) VALUES ('{name}', '{address}', '{type}', '{num}', {minAngle}, {maxAngle}, {minValue}, {maxValue}, {unit}, {description}, {x}, {y}, {r})".format(
name=name,
address=address,
type=type,
num=num,
minAngle=minAngle,
maxAngle=maxAngle,
minValue=minValue,
maxValue=maxValue,
unit=unit,
description=description,
x=x,
y=y,
r=r)
db = get_db()
db.execute(sql)
db.commit()
close_db()
os.system("sh ocr.sh")
return 'Ok'
def dashboard():
params = {}
sql = 'SELECT * FROM `device_info` ORDER BY id'
db = get_db()
results = db.fetchall(sql)
close_db()
items = []
for _, row in results.iterrows():
items.append({
'id': row['id'],
'name': row['name'],
'unit': row['unit'],
})
params['deviceItems'] = items
dicts = {'title': 'Dashboard', 'params': params}
return render_template('/dashboard.html', **dicts)
def record_hw_history(user_ip, operation, context=None):
if (g.user != '{}') and (g.user is not None):
user_id = json.loads(g.user)['id']
if (operation == 1 or operation == 2) and context:
db = get_db()
df = db.fetchall(
'SELECT id FROM hw_info WHERE context="{context}"'.format(
context=context))
hw_id = df.id[0]
db.execute(
'INSERT INTO hw_history (user_id, user_ip, hw_id, operation, time) VALUES ({user_id}, "{user_ip}", {hw_id}, {operation}, now())'
.format(user_id=user_id,
user_ip=user_ip,
hw_id=hw_id,
operation=operation))
db.commit()
close_db()
def check_rating(context):
db = get_db()
if (g.user != '{}') and (g.user is not None):
user_id = json.loads(g.user)['id']
df = db.fetchall(
'SELECT id FROM res_info WHERE context="{context}"'.format(
context=context))
res_id = df.id[0]
df = db.fetchall(
'SELECT user_id FROM res_history WHERE user_id="{user_id}" AND operation=3 AND res_id={res_id}'
.format(user_id=user_id, res_id=res_id))
amount = len(df)
close_db()
return amount - 1
else:
close_db()
return False
def check_submitted(context, operation):
db = get_db()
if (g.user != '{}') and (g.user is not None):
user_id = json.loads(g.user)['id']
df = db.fetchall(
'SELECT id FROM hw_info WHERE context="{context}"'.format(
context=context))
hw_id = df.id[0]
df = db.fetchall(
'SELECT * FROM hw_history WHERE user_id="{user_id}" AND operation={operation} AND hw_id={hw_id}'
.format(user_id=user_id, operation=operation, hw_id=hw_id))
amount = len(df)
close_db()
if amount >= 1:
return True, df.iloc[0].time
elif amount == 0:
return False, None
else:
close_db()
return False, None
def modifyAccount():
record_page_history(pagepath='/auth/modifyAccount',
user_ip=request.remote_addr)
if request.method == 'POST':
db = get_db()
user_id = json.loads(g.user)['id']
customer_name = request.form['customer_name']
if customer_name:
db.execute(
'UPDATE user_info SET customer_name = "{customer_name}" WHERE id = {user_id}'
.format(customer_name=customer_name, user_id=user_id))
db.commit()
mobile = request.form['mobile']
if mobile:
if (not re.match(r'^\d{11}$', mobile)):
flash('Please input the correct mobile number.', 'error')
else:
db.execute(
'UPDATE user_info SET mobile = "{mobile}" WHERE id = {user_id}'
.format(mobile=mobile, user_id=user_id))
db.commit()
birthday = request.form['birthday']
if birthday:
db.execute(
'UPDATE user_info SET birthday = "{birthday}" WHERE id = {user_id}'
.format(birthday=birthday + ' 00:00:00', user_id=user_id))
db.commit()
email = request.form['email']
if email:
db.execute(
'UPDATE user_info SET email = "{email}" WHERE id = {user_id}'.
format(email=email, user_id=user_id))
db.commit()
gender = request.form['gender']
if gender != '-1':
db.execute(
'UPDATE user_info SET gender = {gender} WHERE id = {user_id}'.
format(gender=gender, user_id=user_id))
db.commit()
else:
db.execute(
'UPDATE user_info SET gender = {gender} WHERE id = {user_id}'.
format(gender='null', user_id=user_id))
db.commit()
signature = request.form['signature']
if signature:
db.execute(
'UPDATE user_info SET signature = "{signature}" WHERE id = {user_id}'
.format(signature=signature, user_id=user_id))
db.commit()
province = request.form['province']
if province:
db.execute(
'UPDATE user_info SET province = "{province}" WHERE id = {user_id}'
.format(province=province, user_id=user_id))
db.commit()
city = request.form['city']
if city:
db.execute(
'UPDATE user_info SET city = "{city}" WHERE id = {user_id}'.
format(city=city, user_id=user_id))
db.commit()
close_db()
return redirect(url_for('auth.modifyAccount'))
return render_template('/auth/modifyAccount.html')
