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')