Exemplo n.º 1
0
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')
Exemplo n.º 2
0
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'
        })
Exemplo n.º 3
0
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')
Exemplo n.º 4
0
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()
Exemplo n.º 5
0
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()
Exemplo n.º 6
0
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='邮箱或者密码错误')
Exemplo n.º 7
0
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
Exemplo n.º 8
0
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)
Exemplo n.º 9
0
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')
    })
Exemplo n.º 10
0
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)
Exemplo n.º 11
0
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
Exemplo n.º 12
0
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)
Exemplo n.º 13
0
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'
        })
Exemplo n.º 14
0
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)
Exemplo n.º 15
0
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'
    })
Exemplo n.º 16
0
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
    })
Exemplo n.º 17
0
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)
Exemplo n.º 18
0
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')
Exemplo n.º 19
0
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'
    })
Exemplo n.º 20
0
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
Exemplo n.º 21
0
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))
Exemplo n.º 22
0
 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])
Exemplo n.º 23
0
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']
        })
Exemplo n.º 24
0
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'
        })
Exemplo n.º 25
0
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()
Exemplo n.º 26
0
 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')]
Exemplo n.º 27
0
 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'] == "老玩家"
Exemplo n.º 28
0
 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
Exemplo n.º 29
0
 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'] == '你好'
Exemplo n.º 30
0
def _add_extend(name, address):
    get_db().execute('INSERT INTO Extend(name, address) VALUES (?, ?)', [name, address])
    get_db().commit()