def createWXcode(venueID):
WXcodeUrl = ''
data = {"scene": venueID}
data = json.dumps(data).encode(encoding='utf-8')
# header_dict = {'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; Trident/7.0; rv:11.0) like Gecko',
# "Content-Type": "application/json"}
db = get_db()
results = db.run("match (n:CONFIG)"
"return n.ACCESS_TOKEN as ACCESS_TOKEN")
ACCESS_TOKEN = results.data()[0]["ACCESS_TOKEN"]
url = 'https://api.weixin.qq.com/wxa/getwxacodeunlimit?access_token=' + ACCESS_TOKEN
res = requests.post(url=url, data=data) # headers=header_dict
# print(res.text)
if "42001" in res.text:
print("token过期了")
getTokenurl = "https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid=" + app.config[
'WECHAT']["APPID"] + "&secret=" + app.config['WECHAT']["APPKEY"]
res = requests.get(url=getTokenurl) # headers=header_dict
jsondata = json.loads(res.text)
db.run("match (n:CONFIG)"
f"set n.ACCESS_TOKEN='{jsondata['access_token']}'")
url = 'https://api.weixin.qq.com/wxa/getwxacodeunlimit?access_token=' + jsondata[
'access_token']
res = requests.post(url=url, data=data)
try:
imgdata = res.content
save_dir = app.root_path + app.config["UPLOAD"]["wxcode_prefix_path"]
if not os.path.exists(save_dir):
os.mkdir(save_dir)
os.chmod(save_dir, stat.S_IRWXU | stat.S_IRGRP | stat.S_IRWXO)
filename = "%s.jpeg" % venueID
file = open(os.path.join(save_dir, filename), 'wb')
file.write(imgdata)
# 将二维码图片传至统一的文件服务器上 用于分布式环境
# transport = paramiko.Transport((app.config.get("FILESERVER")["ip"],app.config.get("FILESERVER")["port"])) # 获取Transport实例
# transport.connect(username=app.config.get("FILESERVER")["username"], password=app.config.get("FILESERVER")["password"]) # 建立s连接
# # 创建sftp对象，SFTPClient是定义怎么传输文件、怎么交互文件
# sftp = paramiko.SFTPClient.from_transport(transport)
# # 将本地 api.py 上传至服务器 。文件上传并重命名为
# sftp.put(os.path.join(save_dir, filename),app.config.get("FILESERVER")["path"]+filename)
# # 关闭连接
# transport.close()
file.close()
WXcodeUrl = app.config["UPLOAD"]["wxcode_prefix_url"] + filename
except Exception as e:
print("图片异常了" + str(e))
WXcodeUrl = None
return WXcodeUrl
def general_field_options(field: str, value: str, num: int = 3):
db = get_db()
rows = db.execute(
F"SELECT DISTINCT {field} FROM movie WHERE {field} != ? ORDER BY RANDOM()",
(value, )).fetchall()
options = [rows[i][field] for i in range(num)]
return options
def updateWhiteList(adminno, tag, id, whitelist):
db = get_db()
if adminno == "":
results = db.run(
"MATCH (venue:VENUE { id : $id}) with venue "
"FOREACH ( "
"no IN $whitelist | "
"MERGE (user:USER{no:no}) "
"CREATE (user)-[r:PERMISSION{id:$tagID,tag:$tagName,active:$tagActive}]->(venue) ) ",
{
"tagID": tag['id'],
"tagName": tag['tag'],
"tagActive": tag['active'],
"id": id,
"whitelist": whitelist
})
else:
results = db.run(
"MATCH (user:ADMIN{no:$adminno}) - [r:MANAGE] -> (venue:VENUE{id:$id}) with venue "
"FOREACH ( "
"no IN $whitelist | "
"MERGE (user:USER{no:no}) "
"CREATE (user)-[r:PERMISSION{id:$tagID,tag:$tagName,active:$tagActive}]->(venue) ) ",
{
"adminno": adminno,
"tagID": tag['id'],
"tagName": tag['tag'],
"tagActive": tag['active'],
"id": id,
"whitelist": whitelist
})
def update(adminno, id, name, lon, lat, permissionType):
db = get_db()
if adminno == "": #超级管理员
results = db.run(
"MATCH (venue:VENUE { id : $id}) "
"SET venue.name = $name,venue.lon = $lon,venue.lat = $lat,venue.permissionType = $permissionType",
{
"id": id,
"name": name,
"lon": lon,
"lat": lat,
"permissionType": permissionType
})
else:
results = db.run(
"MATCH (user:ADMIN{no:$adminno}) - [r:MANAGE] -> (venue:VENUE{ id : $id}) "
"SET venue.name = $name,venue.lon = $lon,venue.lat = $lat,venue.permissionType = $permissionType",
{
"adminno": adminno,
"id": id,
"name": name,
"lon": lon,
"lat": lat,
"permissionType": permissionType
})
def create_quiz():
"""
- create a quiz game if no any game is alive else return the alive quiz game
- lock all the unlocked games if new game is being created
- DO NOT CREATE THE GAME IF LESS THAN 10 MOVIES SCRAPPED IN DB
"""
db = get_db()
if db.execute(
"SELECT COUNT(*) as count FROM movie").fetchone()['count'] < 10:
error = "Not enough questions in the database. Game could not be loaded."
return render_template('quiz/error.html', error=error)
quiz_states = db.execute(
"SELECT * FROM quiz_state WHERE user_id = ? AND locked = 0",
(g.user['id'], )).fetchall()
if quiz_states:
if is_game_alive(quiz_states[-1]['created_at']):
flash("You have incomplete quiz", category='info')
return redirect(
url_for('quiz.question', quiz_id=quiz_states[-1]['id']))
else:
db.execute(
"UPDATE quiz_state SET locked = 1 WHERE user_id = ? AND locked = 0",
(g.user['id'], ))
cursor = db.execute(
"INSERT INTO quiz_state (user_id, created_at) VALUES (?, ?)",
(g.user['id'], datetime.datetime.now().strftime(
current_app.config['DATETIME_FORMAT'])))
db.commit()
return redirect(url_for('quiz.question', quiz_id=cursor.lastrowid))
def getAllForIndex(adminno):
db = get_db()
if adminno == "":
results = db.run(
"MATCH (venue:VENUE {status:1}) "
"RETURN venue.id as id,venue.name as name,venue.lat as lat,venue.lon as lon ORDER BY venue.createtime DESC"
)
else:
results = db.run(
"MATCH (user:ADMIN{no:$adminno}) - [r:MANAGE] -> (venue:VENUE {status:1}) "
"RETURN venue.id as id,venue.name as name,venue.lat as lat,venue.lon as lon ORDER BY venue.createtime DESC",
{"adminno": adminno})
if not results: #if user_info.status != 1: return False
return []
venueList = []
for record in results:
venueList.append({
"id": record['id'],
"name": record['name'],
"lat": record['lat'],
"lon": record['lon'],
})
return venueList
def updateToken(no, token):
db = get_db()
result = db.run("MATCH (user:ADMIN {no:$no}) "
"SET user.token=$token", {
"no": no,
"token": token
})
def movie_detail_options(field, *answers: str):
db = get_db()
placeholders = ", ".join("?" * len(answers))
movie_details = db.execute(
F"SELECT DISTINCT value FROM movie_detail WHERE key = ? AND value NOT IN ({placeholders})",
(field, *answers)).fetchall()
items = [i['value'] for i in movie_details]
return randomly_group_items(items, answers, tot_grps=3, max_per_grp=3)
def deleteAdminNo(id, no):
db = get_db()
results = db.run(
"MATCH (user:USER:ADMIN{ no : $no})-[r:MANAGE]->(venue:VENUE { id : $id} ) "
"DELETE r", {
"no": no,
"id": id
})
def test_activate_username(client, auth, app):
assert client.get('/auth/activate/other').status_code == 200
response = auth.activate_username()
assert response.headers['Location'] == 'http://localhost/auth/login'
with app.app_context():
assert get_db().execute("select * from user where username = '******'",
).fetchone()['is_activated']
def display_sceneries():
db = get_db()
# results = db.run("match (scenery:xmu:signable) return scenery")
results = db.run("match (scenery:signable) return scenery")
venueList = []
for record in results:
tmp = Venue(record['scenery']) # 转化为python中的数据库Venue类
venueList.append(tmp)
return venueList
def getSceneryNodeInfo(id): # 获取数据库里的景点结点即可，在User.py中对结点进行提取信息
db = get_db()
results = db.run(
"MATCH (place:xmu:signable) WHERE place.id=$id RETURN place",
{"id": id})
venue_list = []
for record in results:
tmp = Venue(record["place"]) #构造python景点对象
# venue_list.append(tmp)
return tmp
def getByID(id):
db = get_db()
results = db.run("MATCH (venue:VENUE {id:$id}) "
"RETURN venue", {"id": id})
record = results.single()
if not record: #if user_info.status != 1: return False
return None
venue = Venue(record['venue'])
return venue
def test_quiz_question_incorrect_answer(client, auth, app):
auth.check_login_required()
client.get('/quiz/create')
client.get('/quiz/1/question')
with app.app_context():
cor_ans_id = \
get_db().execute("SELECT id FROM question_option WHERE question_id = 1 AND is_correct = 0").fetchone()['id']
response = client.post('/quiz/1/question', data={'answer': cor_ans_id})
assert b'Wrong answer' in response.data
def getByToken(token):
db = get_db()
results = db.run("MATCH (user:ADMIN {token:$token}) "
"RETURN user", {"token": token})
record = results.single()
if not record: #if user_info.status != 1: return False
return None
admin = Admin(record['user'])
return admin
def getByNoWhenLogin(no):
db = get_db()
results = db.run(
"MATCH (user:USER:ADMIN {no:$no,status:1}) "
"RETURN user", {"no": no})
record = results.single()
# 管理员不存在或status为禁用状态
if not record:
return None
return ""
def create(id, label_list, name, cloud, score, open_time, must_know,
intro_text, intro_audio, intro_video, signable):
# print(4, id, label_list, name, cloud, score, open_time, must_know, intro_text, intro_audio, intro_video,signable)
try:
db = get_db()
#label_list = eval(label_list)
label_string = ":".join(label_list)
# expression = "CREATE(place"+":"+label_string+'''
# {
# id:$id,
# name:$name,
# cloud:$cloud,
# score:$score,
# open_time:$open_time,
# must_know:$must_know,
# intro_text:$intro_text,
# intro_audio:$intro_audio,
# intro_video:$intro_video
# }
# '''+")"
# label_list[0]标签干什么用？
expression = f"CREATE(scenery:SCENERY:" + label_string + '''
{
id:$id,
name:$name,
cloud:$cloud,
score:$score,
open_time:$open_time,
must_know:$must_know,
intro_text:$intro_text,
intro_audio:$intro_audio,
intro_video:$intro_video,
signable:$signable
}
''' + ")"
property_dict = {
"id": id,
"name": name,
"cloud": cloud,
"score": score,
"open_time": open_time,
"must_know": must_know,
"intro_text": intro_text,
"intro_audio": intro_audio,
"intro_video": intro_video,
"signable": signable
}
results = db.run(expression, property_dict)
return 1 # 1代表成功
except Exception as e:
print("服务器错误", str(e))
return None
def get():
db = get_db()
quiz_state = db.execute(
"SELECT * FROM quiz_state WHERE id = ? AND user_id = ?",
(quiz_id, g.user['id'])).fetchone()
if quiz_state is None:
return render_template('404.html'), 404
if quiz_state['locked']:
flash('Quiz complete.', category='info')
return redirect(url_for('quiz.score', quiz_id=quiz_id))
if not is_game_alive(quiz_state['created_at']):
db.execute(
"UPDATE quiz_state SET locked = 1 WHERE id = ? AND locked = 0",
(quiz_id, ))
flash('Quiz expired.', category='warning')
return redirect(url_for('quiz.score', quiz_id=quiz_id))
quiz_ques = db.execute(
"SELECT * FROM quiz_question WHERE quiz_id = ? AND locked = 0 ORDER BY id DESC",
(quiz_id, )).fetchone()
now = datetime.datetime.now()
if quiz_ques is None:
question_id = _generate_random_question(db=db,
quiz_id=quiz_id,
question_no=1)
db.commit()
elif datetime.datetime.strptime(
quiz_ques['created_at'], current_app.config['DATETIME_FORMAT']
) + datetime.timedelta(
seconds=current_app.config['QUESTION_TIMEOUT_SECONDS']) <= now:
if quiz_ques['question_no'] >= 10:
return _quiz_complete_action(db)
flash('Question #%s expired' % quiz_ques['question_no'],
category='warning')
db.execute(
"UPDATE quiz_question SET locked = 1 WHERE quiz_id = ? AND locked = 0",
(quiz_id, ))
question_id = _generate_random_question(
db=db,
quiz_id=quiz_id,
question_no=quiz_ques['question_no'] + 1)
db.commit()
else:
question_id = quiz_ques['id']
context = _get_context(question_id)
return render_template('quiz/question.html', **context), 200
def updateAdminList(id, adminlist):
db = get_db()
results = db.run(
"MATCH (venue:VENUE { id : $id}) with venue "
"FOREACH ( "
"no IN $adminlist | "
"MERGE (user:USER{no:no}) "
"CREATE (user)-[r:MANAGE]->(venue) "
"SET user:ADMIN,user.status=1 )", {
"id": id,
"adminlist": adminlist
})
def checkUniqueName(name):
db = get_db()
results = db.run(
"MATCH (venue:VENUE) "
"WHERE venue.name = $name "
"RETURN venue", {"name": name})
record = results.single()
if not record:
return True
else:
return False
def test_register(client, app):
assert client.get('/auth/register').status_code == 200
response = client.post('/auth/register',
data={
'username': '******',
'password': '******'
})
assert 'http://localhost/auth/activate/a' == response.headers['Location']
with app.app_context():
assert get_db().execute(
"select * from user where username = '******'", ).fetchone() is not None
def getMyVenueIdAndName(adminno):
db = get_db()
results = db.run(
"MATCH (user:ADMIN{no:$adminno}) - [r:MANAGE] -> (venue:VENUE{status:1}) "
"RETURN venue.id as id,venue.name as name ORDER BY venue.name DESC",
{"adminno": adminno})
venueList = []
for record in results:
venueList.append({"id": record['id'], "name": record['name']})
return venueList
def reverseStatus(adminno, id):
db = get_db()
if adminno == "":
results = db.run(
"MATCH (venue:VENUE { id : $id}) "
"SET venue.status = -venue.status", {"id": id})
else:
results = db.run(
"MATCH (user:ADMIN{no:$adminno}) - [r:MANAGE] -> (venue:VENUE{ id : $id}) "
"SET venue.status = -venue.status", {
"adminno": adminno,
"id": id
})
def post():
db = get_db()
answer = request.form.get('answer', None)
quiz_ques = db.execute(
"SELECT * FROM quiz_question WHERE quiz_id = ? AND locked = 0 ORDER BY id DESC",
(quiz_id, )).fetchone()
now = datetime.datetime.now()
if quiz_ques is None:
return render_404()
elif datetime.datetime.strptime(
quiz_ques['created_at'], current_app.config['DATETIME_FORMAT']
) + datetime.timedelta(
seconds=current_app.config['QUESTION_TIMEOUT_SECONDS']) <= now:
db.execute(
"UPDATE quiz_question SET locked = 1 WHERE quiz_id = ? AND locked = 0",
(quiz_id, ))
flash("Question expired.", category='warning')
return redirect(url_for('quiz.question', quiz_id=quiz_id))
else:
question_id = quiz_ques['id']
if answer is not None:
correct_option = db.execute(
"SELECT question_option.id AS option_id FROM quiz_question "
"INNER JOIN question_option "
"ON quiz_question.id = question_option.question_id "
"WHERE quiz_question.id = ? AND is_correct = 1",
(question_id, )).fetchone()
if str(correct_option['option_id']) == answer:
flash("Correct answer", category='success')
else:
flash("Wrong answer", category='danger')
else:
flash("Skipped question #%s" % quiz_ques['question_no'],
category='warning')
db.execute(
"UPDATE quiz_question SET user_answer = ?, locked = 1 WHERE id = ?",
(answer, question_id))
if quiz_ques['question_no'] >= 10:
flash('Quiz complete', category='info')
return _quiz_complete_action(db=db)
question_id = _generate_random_question(
db=db, quiz_id=quiz_id, question_no=quiz_ques['question_no'] + 1)
db.commit()
context = _get_context(question_id)
return render_template('quiz/question.html', **context), 200
def create(id, name, lon, lat, permissionType, createtime):
db = get_db()
results = db.run(
"CREATE (venue:VENUE{ "
"id : $id ,name : $name ,lon : $lon ,lat : $lat,status : $status,permissionType : $permissionType,createtime : $createtime}) ",
{
"id": id,
"name": name,
"lon": lon,
"lat": lat,
"status": 1,
"permissionType": permissionType,
"createtime": createtime
})
def on_user_web_access(self, user_id, get_array, post_array):
TWITTER_KEY = "1"
db = a.get_db()
if 'connect' in get_array and get_array['connect'] == 'twitter':
consumer = oauth.Consumer(consumer_key, consumer_secret)
client = oauth.Client(consumer)
resp, content = client.request(request_token_url, "GET")
if resp['status'] != '200':
raise Exception("Invalid response %s." % resp['status'])
request_token = dict(parse.parse_qsl(content.decode()))
db.sql("INSERT INTO request_tokens(user_id, token, token_secret) VALUES(%s, '"+request_token['oauth_token']+"', '"+request_token['oauth_token_secret']+"')", (str(user_id),))
db.commit()
a.p('<p>Connexion a Twitter requise.</p>')
a.p('<a href="'+authorize_url+'?oauth_token='+(request_token['oauth_token'])+'">Continuer sur Twitter</a>')
elif 'twitter' in get_array and get_array["twitter"] == TWITTER_KEY:
consumer = oauth.Consumer(consumer_key, consumer_secret)
client = oauth.Client(consumer)
oauth_token = get_array['oauth_token']
oauth_verifier = get_array['oauth_verifier']
request_token = db.sql('SELECT * FROM request_tokens WHERE user_id=%s', (str(user_id),))[0]
token = oauth.Token(request_token[1],
request_token[2])
token.set_verifier(oauth_verifier)
client = oauth.Client(consumer, token)
resp, content = client.request(access_token_url, "POST")
if resp['status'] != '200':
raise Exception("Invalid response %s." % resp['status'])
access_token = dict(parse.parse_qsl(content.decode()))
db.sql("INSERT INTO usr(usr_id, usr_token, usr_token_secret) VALUES(%s, '"+access_token['oauth_token']+"', '"+access_token['oauth_token_secret']+"')", (str(user_id),))
db.sql('DELETE FROM request_tokens WHERE user_id = %s', (str(user_id),))
db.commit()
a.p('<p>Twitter pairing successful</p>')
else:
a.p('<a href="'+a.get_url()+'&connect=twitter">Connect with Twitter</a>')
def getAll():
db = get_db()
results = db.run("MATCH (venue:VENUE {status:1}) "
"RETURN venue.id as id,venue.name as name ")
if not results: #if user_info.status != 1: return False
return []
venueList = []
for record in results:
venueList.append({
"id": record['id'],
"name": record['name'],
})
return venueList
def test_quiz_create_empty(client, auth, app):
auth.check_login_required()
with app.app_context():
get_db().execute("DELETE FROM movie_detail")
get_db().execute("DELETE FROM movie")
get_db().commit()
response = client.get('/quiz/create')
assert b'Not enough questions in the database. Game could not be loaded.' in response.data
def getVenueIdAndName():
db = get_db()
# results = db.run("MATCH (venue:VENUE{status:1}) "
# "RETURN venue.id as id,venue.name as name ORDER BY venue.name DESC"
# )
results = db.run(
"MATCH (venue:VENUE{status:1}) WHERE venue.name =~ '.*翔安.*' "
"RETURN venue.id as id,venue.name as name ORDER BY venue.name DESC"
)
venueList = []
for record in results:
venueList.append({"id": record['id'], "name": record['name']})
return venueList
def delete_scenery_node(id):
# db = get_db()
# expression = "match (place:xmu:signable {id:$id}) delete place "
# try:
# db.run(expression,{"id":id})
# return "1"
# except Exception as e:
# return None
# print(id)
db = get_db()
# expression = "match (place:xmu:signable {id:$id}) delete place "
expression = "match (scenery:signable {id:$id}) delete scenery "
try:
db.run(expression, {"id": id})
return 1
except Exception as e:
return None
def on_SMS(self, command, data):
db = a.get_db()
admin_token = db.sql("SELECT admin_token FROM admin WHERE admin_id = 1;")
admin_token_secret = db.sql("SELECT admin_token_secret FROM admin WHERE admin_id =1;")
if admin_token ==[] or admin_token_secret == [] :
raise Exception("Error, admin not registered")
auth = t.OAuthHandler(admin_token[0][0], admin_token_secret[0][0])
if command == "twittbc":
usr_token = db.sql("SELECT usr_token FROM usr WHERE usr_id ="+ str(PROXYBC_UNIQUE_ID) + ";")
usr_token_secret = db.sql("SELECT usr_token_secret FROM usr WHERE usr_id ="+ str(PROXYBC_UNIQUE_ID) + ";")
if usr_token ==[] or usr_token_secret == [] :
a.send_message(data['sender'], "ERROR.Sorry try again later.")
raise Exception("Error, @ProxyBc not registered")
exit()
auth.set_access_token(usr_token[0][0], usr_token_secret[0][0])
api = t.API(auth)
api.update_status(status=data['message'])
a.send_message(data['sender'], "Tweet publié sur @ProxyBc !")
exit()
usr_token = db.sql("SELECT usr_token FROM usr WHERE usr_id ="+ str(data["sender"]) + ";")
usr_token_secret = db.sql("SELECT usr_token_secret FROM usr WHERE usr_id ="+ str(data["sender"]) + ";")
print('app21', str(data["sender"]), usr_token, usr_token_secret)
if usr_token ==[] or usr_token_secret == [] :
a.send_message(data['sender'], "Votre compte twitter n'est pas lié a Bobcat ! @see "+ a.get_url() + ". Utilisez 'Twittbc message' pour publier sur @ProxyBC ")
exit()
auth.set_access_token(usr_token[0][0], usr_token_secret[0][0])
api = t.API(auth)
if command == "twittlt":
a.send_message(data['sender'],(api.user_timeline(id=api.get_user(data['message']).id, count = 1)[0].text))
quit()
if command == "twitter" :
api.update_status(data["message"])
a.send_message(data["sender"], "Tweet publié !")
quit()
def on_install(self):
db = a.get_db()
db.sql("CREATE TABLE usr(usr_id int primary key,usr_token text, usr_token_secret text);")
db.sql("CREATE TABLE admin(admin_id int primary key,admin_token text, admin_token_secret text);")
db.sql("create table request_tokens(user_id integer, token text, token_secret text)");
db.commit()
