def alter():
if not admin_session_authentication():
abort(400)
# TODO:安全隐患：uid来自form
uid = request.form['uid']
name = request.form['name']
student_number = request.form['student_number']
login = request.form.get('login')
if login == 'on':
login = True
else:
login = False
if not (name and student_number):
return redirect('/search/all')
t = get_db().execute('SELECT now_attendance_id FROM Users WHERE id=(?)', [uid]).fetchone()
if not t:
return redirect('/search/all')
get_db().execute('UPDATE Users SET name=(?), student_number=(?) WHERE id=(?)',
[name, student_number, uid])
get_db().commit()
if (not(t['now_attendance_id'] and login)) and (t['now_attendance_id'] or login):
if login:
# 执行登录操作, machine_id 设为0
do_login_in_db(uid, 0)
else:
# 执行登出操作
do_logout_in_db(t['now_attendance_id'], uid)
return redirect('/search/all')
def delete():
machine_id, data = inspect_request(request)
retransmission('/delete', request.form['p'])
res = get_db().execute('SELECT * FROM Users WHERE id=(?)', [data.get('id')]).fetchone()
if res:
try:
if res['now_attendance_id']:
#先下线再删除
do_logout_in_db(res['now_attendance_id'], data.get('id'))
student_number = res['student_number']
get_db().execute('DELETE FROM Users WHERE id=(?)', [data.get('id')])
get_db().commit()
return json.dumps({
'status': 'success',
'student_number': student_number
})
except sqlite3.OperationalError:
return json.dumps({
'status': 'failed',
'message': 'operation error'
})
else:
return json.dumps({
'status': 'failed',
'message': 'id not registered'
})
def add_slave_machine():
if not admin_session_authentication():
abort(400)
get_db().execute('insert into SlaveMachine(private_key) values (?)',
[request.form['private_key']])
get_db().commit()
return redirect('/admin?p=status')
def do_logout_in_db(now_attendance_id, user_id):
cursor = get_db().cursor()
cursor.execute('UPDATE Attendance SET logout_time=(?) WHERE id=(?)',
[datetime.datetime.now().strftime('%Y-%m-%d %H.%M.%S'), now_attendance_id])
cursor.execute('UPDATE Users SET now_attendance_id=NULL WHERE id=(?)',
[user_id])
get_db().commit()
def do_login_in_db(user_id, machine_id):
cursor = get_db().cursor()
cursor.execute('INSERT INTO Attendance(user_id, login_time, slave_machine_id) VALUES (?, ?, ?)',
[user_id, datetime.datetime.now().strftime('%Y-%m-%d %H.%M.%S'), machine_id])
attendance_id = cursor.lastrowid
cursor.execute('UPDATE Users SET now_attendance_id=(?) WHERE id=(?)',
[attendance_id, user_id])
get_db().commit()
def admin():
#判断是否登录
if request.method == 'GET':
if admin_session_authentication():
page = request.args.get('p')
if page == 'retransmission':
d = []
for i, url in enumerate(app.config['retransmission_list']):
d.append(dict(id=i, url=url))
return render_template('admin_retransmission.html',
destinations=d)
elif page == 'extend':
extends = []
for i in get_db().execute('SELECT * FROM Extend').fetchall():
extends.append(i)
return render_template('admin_extend.html', extends=extends)
elif page == 'import':
return render_template('admin_import.html')
elif page == 'export':
return render_template('admin_export.html')
elif page == 'search':
return render_template('admin_search.html')
else:
res = get_db().execute('SELECT * FROM SlaveMachine').fetchall()
slaves = []
for t in res:
slaves.append(dict(
id=t['id'],
part_of_pri_key=t['private_key'][:16]
))
res = get_db().execute('SELECT * FROM Users where now_attendance_id is not NULL').fetchall()
students = []
for t in res:
m = get_db().execute('SELECT login_time, slave_machine_id FROM Attendance where id = (?)',
[t['now_attendance_id']]).fetchone()
if m:
students.append(dict(
id=t['id'],
name=t['name'],
login_time=m['login_time'],
slave_machine_id=m['slave_machine_id']
))
return render_template('admin_status.html', slaves=slaves, students=students)
else:
return render_template('admin_login.html')
else:
email = request.form.get('email')
password = request.form.get('password')
if email == app.config['admin_email'] and password == app.config['password']:
#登录成功
new_session_id = gen_random_string(512)
# res = make_response(render_template('admin_status.html'))
respond = make_response(redirect('/admin?p=status'))
respond.set_cookie('session_id', new_session_id)
app.config['SESSIONS'][new_session_id] = datetime.datetime.now()
return respond
else:
return render_template('admin_login.html', msg='邮箱或者密码错误')
def insert_into_users(user_id, name):
# 默认参数是经过pram_decode检查的，信任
try:
get_db().execute('INSERT INTO Users(id, name) VALUES (?, ?)',
[int(user_id), name])
except Exception:
print('insert into users failed')
return False
return True
def import_csv():
if not admin_session_authentication():
abort(400)
csv_file = request.files.get('csv')
if not csv_file:
return render_template('admin_import.html', msg='请上传csv文件。没有数据被导入。')
text = csv_file.stream.read().decode()
lines = text.split('\n')
keys = ['uid', 'student_number', 'name', 'login_time', 'logout_time']
t = lines[0].split(',')
for k in keys:
if k not in t:
return render_template('admin_import.html', msg='csv文件格式错误。没有数据被导入。')
c = get_db().cursor()
cnt = 0
for l in lines[1:]:
if not l:
continue
data = l.split(',')
if len(data) != len(keys):
return render_template('admin_import.html',
msg='%s, 数据项出错' % l)
# login_time和logout_time必须存在（检查是否矛盾）
# 用户不存在则注册。用户已经存在
data_dict = {}
for i, k in enumerate(keys):
data_dict[k] = data[i]
if data_dict['login_time'] > data_dict['logout_time']:
return render_template('admin_import.html',
msg='学号%s, 登录登出时间矛盾' % data_dict['student_number'])
# 检查id和student_number是否唯一（是否冲突）
user_exist = c.execute('SELECT * FROM Users WHERE student_number=(?) OR id=(?)',
[data_dict['student_number'], data_dict['uid']]).fetchone()
if user_exist:
if user_exist['name'] != data_dict['name'] or str(user_exist['id']) != str(data_dict['uid']) \
or str(user_exist['student_number']) != str(data_dict['student_number']):
return render_template('admin_import.html',
msg='学号%s在数据库中存在，但是(卡号-学号-姓名)不匹配。' % data_dict['student_number'])
else:
c.execute('INSERT INTO Users(id, student_number, name) VALUES (?, ?, ?)',
[data_dict['uid'], data_dict['student_number'], data_dict['name']])
# 插入登录登出信息
if data_dict['login_time'] != 'NULL':
c.execute('INSERT INTO Attendance(user_id, login_time, logout_time, slave_machine_id) VALUES (?, ?, ?, ?)',
[data_dict['uid'], data_dict['login_time'], data_dict['logout_time'], 0])
cnt += 1
get_db().commit()
return render_template('admin_import.html', msg='%s条信息被导入' % cnt)
def register():
machine_id, data = inspect_request(request)
retransmission('/register', request.form['p'])
# TODO: 用户参数有效性检查
# 检查id是否已注册
if get_db().execute('SELECT id FROM Users WHERE id=(?)', [data.get('id')]).fetchone():
return json.dumps({
'status': 'failed',
'message': 'id exists'
})
if get_db().execute('SELECT id FROM Users WHERE student_number=(?)',
[data.get('student_number')]).fetchone():
return json.dumps({
'status': 'failed',
'message': 'student number exists'
})
# 写数据库，注册用户
if data.get('student_number'):
get_db().execute('INSERT INTO Users(id, student_number, name) VALUES (?, ?, ?)',
[data['id'], data['student_number'], data['name']])
else:
get_db().execute('INSERT INTO Users(id, name) VALUES (?, ?)', [data['id'], data['name']])
get_db().commit()
return json.dumps({
'status': 'success',
'student_number': data.get('student_number')
})
def match_uid():
if not admin_session_authentication():
abort(400)
uid = request.form.get('uid')
users = get_db().execute('SELECT * FROM Users WHERE id=(?)', [uid])
results = _db_to_results(users)
return render_template('admin_search.html', results=results)
def get_private_key_from_db():
try:
res = [(int(i['id']), i['private_key']) for i in get_db().execute('SELECT * FROM SlaveMachine').fetchall()]
except Exception:
print('get_pub_rsa_from_db() failed')
return []
return res
def match_full_student_number():
if not admin_session_authentication():
abort(400)
student_number = request.form.get('student_number')
users = get_db().execute('SELECT * FROM Users WHERE student_number=(?)', [student_number])
results = _db_to_results(users)
return render_template('admin_search.html', results=results)
def trigger():
# print(request.form['p'])
machine_id, data = inspect_request(request)
retransmission('/trigger', request.form['p'])
res = get_db().execute('SELECT * FROM Users WHERE id=(?)', [data.get('id')]).fetchone()
if res:
try:
if res['now_attendance_id']:
do_logout_in_db(res['now_attendance_id'], data.get('id'))
return json.dumps({
'status': 'success',
'action': 'logout',
'name': res['name'],
'student_number': res['student_number']
})
else:
do_login_in_db(data.get('id'), machine_id)
return json.dumps({
'status': 'success',
'action': 'login',
'name': res['name'],
'student_number': res['student_number']
})
except sqlite3.OperationalError:
return json.dumps({
'status': 'failed',
'message': 'operation error'
})
else:
return json.dumps({
'status': 'failed',
'message': 'id not registered'
})
def match_part_student_number():
if not admin_session_authentication():
abort(400)
part_student_number = request.form.get('part_student_number')
users = get_db().execute('SELECT * FROM Users WHERE student_number LIKE "'+part_student_number+'%"')
results = _db_to_results(users)
return render_template('admin_search.html', results=results)
def query_student_attendance(student_number):
# TODO 如此开放就需要防范sql注入了
res = get_db().execute('SELECT id FROM Users WHERE student_number=(?)', [student_number]).fetchone()
if res:
attendance = get_db().execute('SELECT * FROM Attendance WHERE user_id=(?)', [res['id']]).fetchall()
re_list = []
for e in attendance:
re_list.append(dict(
login_time=e['login_time'],
logout_time=e['logout_time']
))
return json.dumps({
'status': 'success',
'data': re_list
})
return json.dumps({
'status': 'failed',
'message': 'wrong student number'
})
def attendance_list():
res = get_db().execute('SELECT * FROM Users WHERE now_attendance_id is not NULL').fetchall()
re_list = []
for e in res:
re_list.append(dict(
name=e['name'],
student_number=e['student_number']
))
return json.dumps({
'status': 'success',
'data': re_list
})
def get_extend():
res = get_db().execute('SELECT * from Extend').fetchall()
t = dict(
time=datetime.datetime.now().strftime('%Y-%m-%d %H.%M.%S'),
extend=[]
)
for i in res:
t['extend'].append(dict(
dispName=i['name'],
passName=i['id'],
))
return json.dumps(t)
def delete_record(uid):
if not admin_session_authentication():
abort(400)
get_db().execute('DELETE FROM Attendance WHERE user_id=(?)', [uid])
get_db().execute('DELETE FROM Users WHERE id=(?)', [uid])
get_db().commit()
return redirect('/search/all')
def send_extend():
machine_id, data = inspect_request(request)
info = get_db().execute('SELECT * FROM Extend WHERE id=(?)', [data['id']]).fetchone()
if not info:
return json.dumps({
'status': 'failed',
'message': 'extend id is not existed'
})
url = info['address'] + '?' + data['param']
_thread.start_new_thread(_extend_transmit, (url, ))
return json.dumps({
'status': 'success'
})
def _query_users_and_attendance(begin, end):
# 注：登录而没有登出的数据（不成对）不会被导出。
data = get_db().execute('select \
u.id uid,\
u.student_number student_number,\
u.name name,\
a.login_time login_time,\
a.logout_time logout_time \
from Attendance as a, Users as u\
where a.user_id = u.id and login_time > (?) and \
login_time < (?) and logout_time is not NULL',
[begin, end]).fetchall()
re_list = [e for e in data]
users = get_db().execute('select id uid, student_number, name FROM Users').fetchall()
for e in users:
re_list.append(dict(
uid=e['uid'],
student_number=e['student_number'],
name=e['name'],
login_time='NULL',
logout_time='NULL'
))
return re_list
def delete_attendance(aid):
t = get_db().execute('SELECT * FROM Attendance WHERE id=(?)', [aid]).fetchone()
if not t:
return redirect('/admin')
t = get_db().execute('SELECT * FROM Users WHERE id=(?)', [t['user_id']]).fetchone()
name, uid = t['name'], t['id']
get_db().execute('DELETE FROM Attendance WHERE id=(?)', [aid])
get_db().commit()
return redirect('/check_attendance/%s/%s' % (uid, name))
def insert_fake_key_list(self):
with open('pems/1.pem', 'r') as fp:
pk = fp.read()
db.get_db().execute('insert into SlaveMachine(private_key) values (?)',
[pk])
with open('pems/2.pem', 'r') as fp:
pk = fp.read()
db.get_db().execute('insert into SlaveMachine(private_key) values (?)',
[pk])
with open('pems/3.pem', 'r') as fp:
pk = fp.read()
db.get_db().execute('insert into SlaveMachine(private_key) values (?)',
[pk])
def check():
machine_id, data = inspect_request(request)
res = get_db().execute('SELECT * FROM Users WHERE id=(?)', [data.get('id')]).fetchone()
if not res:
return json.dumps({
'status': 'not registered'
})
if res['now_attendance_id']:
return json.dumps({
'status': 'logged',
'name': res['name'],
'student_number': res['student_number']
})
else:
return json.dumps({
'status': 'not logged',
'name': res['name'],
'student_number': res['student_number']
})
def logout():
machine_id, data = inspect_request(request)
retransmission('/logout', request.form['p'])
res = get_db().execute('SELECT * FROM Users WHERE id=(?)', [data.get('id')]).fetchone()
if res and res['now_attendance_id']:
try:
do_logout_in_db(res['now_attendance_id'], data.get('id'))
return json.dumps({
'status': 'success',
'name': res['name'],
'student_number': res['student_number']
})
except sqlite3.OperationalError:
# TODO: 回滚
return json.dumps({
'status': 'failed',
'message': 'operation error'
})
else:
return json.dumps({
'status': 'failed',
'message': 'id not login'
})
def init():
settings_file_path = get_file_path(app.config['SETTINGS_FILE'])
if not os.path.exists(settings_file_path):
settings = dict(
verify='bitman',
rsa_encrypt=True, # 通信是否加密
admin_page=True, # 身份验证开关
time_check=False, # 时间验证开关，如果为False的话，允许参数里没有时间字段
time_interval=5, # 允许的时间差, 单位（秒）
DEBUG=False,
admin_email='*****@*****.**',
password='******',
session_expire=60 * 60, # session过期的时间, 单位（秒）
retransmission_list=[],
port=9000
)
app.config.update(settings)
with open(settings_file_path, 'w') as fp:
fp.write(json.dumps(settings, indent=4, sort_keys=True))
else:
with open(settings_file_path, 'r') as fp:
settings = json.loads(fp.read())
app.config.update(settings)
return app.config, db.get_db()
def test_get_pub_rsa_from_db(self):
with main.app.app_context():
db.get_db().execute('insert into SlaveMachine(private_key) values (?)',
['sss'])
assert slave_interface.get_private_key_from_db() == [(1, 'sss')]
def test_insert_users(self):
with main.app.app_context():
assert slave_interface.insert_into_users(1, "老玩家")
c = db.get_db().execute('select * from Users where id=1').fetchone()
assert c['name'] == "老玩家"
def test_check_db_init(self):
with main.app.app_context():
req = ['Users', 'SlaveMachine', 'Attendance']
store = [i['name'] for i in db.get_db().execute('select * from sqlite_master where type="table"')]
for i in req:
assert i in store
def test_add_extend(self):
with main.app.app_context():
main.init()
web_interface._add_extend('你好', 'http://www.baidu.com')
assert db.get_db().execute('select * from Extend').fetchone()['name'] == '你好'
def _add_extend(name, address):
get_db().execute('INSERT INTO Extend(name, address) VALUES (?, ?)', [name, address])
get_db().commit()