def patch(self):
current_id = get_jwt_identity()
args = parser.parse_args()
error = None
# name error
if not args["name"]:
error = "Name이 유효하지 않습니다."
elif not english_re.match(args["name"]) and not korean_re.match(
args["name"]):
error = "Name은 한글 또는 영어로만 입력하여야 합니다."
if error is None:
with db.cursor(DictCursor) as cursor:
sql = "UPDATE `users` SET `name` = %s WHERE id = %s"
cursor.execute(sql, (args["name"], current_id))
db.commit()
with db.cursor(DictCursor) as cursor:
sql = "UPDATE `userinfo` SET `info` = %s WHERE user_id = %s"
cursor.execute(sql, (args["info"], current_id))
db.commit()
return jsonify(status="success", logged_in_as=current_id)
return jsonify(status="fail", result={"message": error})
def blacklists():
data = request.get_json()
user_id = data.get("user_id")
reported_id = data.get("reported_id")
error = None
with db.cursor(DictCursor) as cursor:
sql = "SELECT reported_id FROM blacklists WHERE user_id = %s AND reported_id=%s"
cursor.execute(sql, (user_id, reported_id))
user = cursor.fetchone()
if user:
error = "이미 신고하셨습니다!"
if error is None:
with db.cursor(DictCursor) as cursor:
sql = "INSERT INTO `blacklists` (`user_id`, `reported_id`) VALUES (%s, %s)"
cursor.execute(sql, (user_id, reported_id))
db.commit()
with db.cursor(DictCursor) as cursor:
sql = "UPDATE users SET report_count = report_count + 1 WHERE id = %s"
cursor.execute(sql, reported_id)
db.commit()
with db.cursor(DictCursor) as cursor:
sql = "SELECT report_count FROM users WHERE id = %s"
cursor.execute(sql, reported_id)
check_count = cursor.fetchone()
if check_count["report_count"] >= 3:
with db.cursor(DictCursor) as cursor:
sql = "DELETE FROM users WHERE id = %s"
cursor.execute(sql, reported_id)
db.commit()
return jsonify(status="success", result={"message": "신고가 접수되었습니다!"})
return jsonify(status="fail", result={"message": error})
def get(self, user_id=None):
current_id = get_jwt_identity()
if not user_id:
with db.cursor(DictCursor) as cursor:
sql = "SELECT id, title, description, acquisition_date FROM `certificates` WHERE user_id = %s"
cursor.execute(sql, (current_id, ))
result = cursor.fetchall()
return jsonify(status="success", result=result)
with db.cursor(DictCursor) as cursor:
sql = "SELECT id, title, description, acquisition_date FROM `certificates` WHERE user_id = %s"
cursor.execute(sql, (user_id, ))
result = cursor.fetchall()
return jsonify(status="success", result=result)
def patch(self):
current_id = get_jwt_identity()
args = parser.parse_args()
error = None
if not args["college"]:
error = "학교 이름은 필수 입력값입니다."
elif not args["major"]:
error = "전공은 필수 입력값입니다."
elif args["degree"] == -1:
error = "학위는 필수 입력값입니다."
if error is None:
with db.cursor(DictCursor) as cursor:
sql = "UPDATE `educations` SET college = %s, major = %s, degree = %s WHERE `id` = %s AND `user_id` = %s"
cursor.execute(
sql,
(
args["college"],
args["major"],
args["degree"],
args["id"],
current_id,
),
)
db.commit()
return jsonify(status="success")
return jsonify(status="fail", result={"message": error})
def get_wallet_incomes():
"""
Calculates sum of incoming money to users (wallets according to users table).
Returns
-------
"""
cur = db.cursor()
query = """
select sum(ohne.satoshis) as satoshis, ohne.wallet as wallet
from (
select satoshis, wallet
from txoutput
except
select o.satoshis, o.wallet
from txoutput o, txinput i
where (o.txid = i.txid and o.wallet = i.wallet)
) ohne group by wallet order by satoshis desc;
"""
cur.execute(query)
res = cur.fetchall()
cur.close()
return res
def get(self, user_id=None):
current_id = get_jwt_identity()
if not user_id:
with db.cursor(DictCursor) as cursor:
sql = "SELECT email, name, image_path, info FROM users JOIN userinfo ON users.id = userinfo.user_id WHERE users.id = %s"
cursor.execute(sql, (current_id, ))
user = cursor.fetchone()
return jsonify(status="success",
user=user,
logged_in_as=current_id)
with db.cursor(DictCursor) as cursor:
sql = "SELECT email, name, image_path, info FROM users JOIN userinfo ON users.id = userinfo.user_id WHERE users.id = %s"
cursor.execute(sql, (user_id, ))
user = cursor.fetchone()
return jsonify(status="success", user=user, logged_in_as=current_id)
def patch(self):
current_id = get_jwt_identity()
args = parser.parse_args()
error = None
if not args["title"]:
error = "프로젝트 제목은 필수 입력값입니다."
if error is None:
with db.cursor(DictCursor) as cursor:
sql = "UPDATE `projects` SET title = %s, description = %s, startdate = %s, enddate= %s WHERE `id` = %s AND `user_id` = %s"
cursor.execute(
sql,
(
args["title"],
args["description"],
args["startdate"].split("T")[0],
args["enddate"].split("T")[0],
args["id"],
current_id,
),
)
db.commit()
return jsonify(status="success")
return jsonify(status="fail", result={"message": error})
def post(self):
username_or_email = self.get_argument("username").lower()
pwd = self.get_argument("password")
password = hash_password(pwd)
cursor = db.cursor()
cursor.execute("""
SELECT ID, user_login, user_email
FROM yagra_user
WHERE (user_login = %s
OR user_email = %s) AND user_passwd = %s""",
(username_or_email, username_or_email, password))
row = cursor.fetchone()
if row:
ID, username, email = row
m = hashlib.md5()
m.update(email)
email_md5 = m.hexdigest()
logging.info(row)
self.session["login"] = True
self.session["_csrf_token"] = make_digest(uuid.uuid4().get_hex())
self.session["id"] = ID
self.session["username"] = username
self.session["email"] = email
self.session["email_md5"] = email_md5
self.set_cookie("session_id", self.session.session_id)
self.redirect("/user")
return
# 登录失败
html_string = Template.render("login", login_failed=True)
self.write(html_string)
def post(self):
new_image_id = self.get_argument("new_image_id")
csrf_token = self.get_argument("csrf_token")
username = self.session["username"]
user_id = self.session["id"]
# check csrf
if csrf_token != self.session["_csrf_token"]:
self.set_status(403)
return
c = db.cursor()
c.execute("""
SELECT *
FROM yagra_user, yagra_image
WHERE ID = user_id AND user_login = %s AND image_id = %s
""", (username, new_image_id))
row = c.fetchone()
if row:
c.execute("""
UPDATE yagra_user_head
SET image_id = %s
WHERE user_id = %s""", (new_image_id, user_id))
# self.redirect("/user")
self.write({"status": "OK"})
return
self.write({"status": "Failed",
"msg": "选择的图片不正确！"})
def getUserInfo():
user={}
# 打开数据库连接
db = pymysql.connect(host='13.231.165.68', port=33070, user='******', passwd='880309jQl', db='tfs', charset='utf8')
# 使用cursor()方法获取操作游标
cursor = db.cursor()
sql = "SELECT * FROM user"
try:
# 执行SQL语句
cursor.execute(sql)
# 获取所有记录列表
results = cursor.fetchall()
for row in results:
user[row[0]]=row[5]
# print(len(results))
except:
print("Error: unable to fecth user data")
# 关闭数据库连接
db.close()
return user;
def post(self):
cursor = db.cursor()
data = self.assure_input_valid()
if not data:
return
username, pwd, email = data
hash_pwd = hash_password(pwd)
cursor.execute("""
INSERT INTO `yagra`.`yagra_user`
(
`user_login`,
`user_passwd`,
`display_name`,
`user_email`,
`user_register`,
`user_status`)
VALUES
(%s, %s, %s, %s, %s, %s)
""", (username, hash_pwd, username,
email,
time.strftime('%Y-%m-%d %H:%M:%S'), str(1)))
db.commit()
# self.write("Username: %s registered success!" % escape(username))
self.redirect("/accounts/login")
def post(self):
current_id = get_jwt_identity()
args = parser.parse_args()
error = None
if not args["college"]:
error = "학교 이름은 필수 입력값입니다."
elif not args["major"]:
error = "전공은 필수 입력값입니다."
elif args["degree"] == "-1":
error = "학위는 필수 입력값입니다."
if error is None:
with db.cursor(DictCursor) as cursor:
sql = "INSERT INTO `educations` (`college`, `major`, `degree`, `user_id`) VALUES (%s, %s, %s, %s)"
cursor.execute(
sql,
(args["college"], args["major"], args["degree"],
current_id),
)
db.commit()
return jsonify(status="success")
return jsonify(status="fail", result={"message": error})
def retranslateUi(self, Dialog):
_translate = QtCore.QCoreApplication.translate
Dialog.setWindowTitle(_translate("Dialog", "Settings"))
self.label_2.setText(_translate("Dialog", "Change Password"))
self.label_7.setText(_translate("Dialog", "Set IP"))
self.label_8.setText(_translate("Dialog", "Set URL"))
self.btn_saveIp.setText(_translate("Dialog", "Save"))
self.btn_saveUrl.setText(_translate("Dialog", "Save"))
self.label.setText(_translate("Dialog", "Settings"))
self.label_9.setText(_translate("Dialog", "Old Password"))
self.label_11.setText(_translate("Dialog", "Username"))
self.label_12.setText(_translate("Dialog", "New Password"))
self.btn_save.setText(_translate("Dialog", "Save"))
self.btn_back.setText(_translate("Dialog", "Back"))
self.label_3.setText(_translate("Dialog", "Change Password"))
self.label_4.setText(_translate("Dialog", "Settings"))
self.btn_saveUrl.clicked.connect(self.saveUrl)
self.btn_saveIp.clicked.connect(self.saveIP)
self.btn_save.clicked.connect(self.saveUserDetails)
cursor = db.cursor()
cursor.execute('SELECT url FROM url')
self.txt_url.setText(cursor.fetchone()[-1])
cursor.execute('SELECT ip FROM ip')
self.txt_ip.setText(cursor.fetchone()[-1])
cursor.close()
def get_wallet_transactions():
"""
Fetches transactions between users.
Returns
-------
res: array of tuples
Query result. It cointains
userid (from)
userid (to)
satoshis transferred
timestamp
transaction_id
"""
cur = db.cursor()
query = """
select distinct i.wallet, outputs.wallet, outputs.satoshis, outputs.timest, outputs.txid
from (
select *
from txoutput
except
select o.*
from txoutput o, txinput i
where (o.txid = i.txid and o.wallet = i.wallet)
) outputs, txinput i
where i.txid = outputs.txid and i.wallet <> outputs.wallet;
"""
cur.execute(query)
res = cur.fetchall()
cur.close()
return res
def get_subscribers():
c = db.cursor()
c.execute('SELECT id FROM subscribers')
subscribers = []
for id in c.fetchall():
subscribers.append(id[0])
c.close()
return subscribers
def network():
# Access the identity of the current user with get_jwt_identity
current_id = get_jwt_identity()
if request.method == "POST":
data = request.get_json()
searchname = data.get("searchname")
with db.cursor(DictCursor) as cursor:
sql = f"""SELECT email, name, image_path, info, users.id FROM users JOIN userinfo ON users.id = userinfo.user_id WHERE name LIKE '%{searchname}%' """
cursor.execute(sql, )
user = cursor.fetchall()
return jsonify(status="success", user=user)
with db.cursor(DictCursor) as cursor:
sql = "SELECT email, name, image_path, info, users.id FROM users JOIN userinfo ON users.id = userinfo.user_id"
cursor.execute(sql, )
user = cursor.fetchall()
return jsonify(status="success", user=user)
def delete(self):
args = parser.parse_args()
with db.cursor(DictCursor) as cursor:
sql = "DELETE FROM `certificates` WHERE `id` = %s"
cursor.execute(sql, (args["id"], ))
db.commit()
return jsonify(status="success")
def get(self, user_id=None):
current_id = get_jwt_identity()
if not user_id:
with db.cursor(DictCursor) as cursor:
sql = "SELECT id, college, major, degree FROM `educations` WHERE user_id = %s"
cursor.execute(sql, (current_id, ))
result = cursor.fetchall()
return jsonify(status="success", result=result)
with db.cursor(DictCursor) as cursor:
sql = (
"SELECT id, college, major, degree FROM `educations` WHERE user_id = %s"
)
cursor.execute(sql, (user_id, ))
result = cursor.fetchall()
return jsonify(status="success", result=result)
def saveIP(self):
ip = self.txt_ip.toPlainText()
cursor = db.cursor()
cursor.execute('DELETE FROM ip')
cursor.execute('INSERT INTO ip VALUES(?)', (ip, ))
db.commit()
cursor.execute('SELECT ip FROM ip')
self.txt_ip.setText(cursor.fetchone()[-1])
def post(self):
user_head = self.get_argument("user_head")
csrf_token = self.get_argument("csrf_token")
if csrf_token != self.session["_csrf_token"]:
self.set_status(403)
return
username = self.session["username"]
upload_filename = unicode(self.request.files["user_head"].filename,
encoding="utf-8")
filename = create_random_filename(username + "_" + upload_filename)
full_filename = "uploads/" + filename
logging.info("Writing file to %s" % (filename, ))
with open(full_filename, "wb") as out:
out.write(user_head)
# 检查用户是否存在
cursor = db.cursor()
cursor.execute("""
SELECT ID, user_email
FROM yagra_user
WHERE user_login = %s""", (username, ))
row = cursor.fetchone()
if row:
user_id, user_email = row
m = hashlib.md5()
m.update(user_email)
email_md5 = m.hexdigest()
# 插入图片信息到数据库中
cursor.execute("""
INSERT INTO yagra_image (user_id, filename, upload_date)
VALUES (%s, %s, %s)""", (str(user_id),
filename,
time.strftime('%Y-%m-%d %H:%M:%S')))
image_id = cursor.lastrowid
# 判断用户是否已经有默认头像，没有则设置默认头像
cursor.execute("""
SELECT *
FROM yagra_user_head
WHERE user_email_md5 = %s""", (email_md5, ))
row = cursor.fetchone()
if not row:
logging.info("Insert into db. Filename %s, "
"image_id: %d, "
"user_id: %d" % (filename, image_id, user_id))
cursor.execute("""
INSERT INTO yagra_user_head (user_id, image_id, user_email_md5)
VALUES (%s, %s, %s)""", (user_id, image_id, email_md5))
db.commit()
self.redirect("/user")
def saveUrl(self):
url = self.txt_url.toPlainText()
cursor = db.cursor()
cursor.execute('DELETE FROM url')
cursor.execute('INSERT INTO url VALUES(?)', (url, ))
db.commit()
cursor.execute('SELECT url FROM url')
self.txt_url.setText(cursor.fetchone()[-1])
def fetch():
with closing(db.cursor()) as c:
c.execute(
"""SELECT DISTINCT url FROM search_console_data WHERE (date=%s and user_check=0)""",
(my_date, ))
fetch = c.fetchall()
for f_row in fetch:
url = f_row[0]
yield url
print len(fetch)
def get_users():
cur = db.cursor()
cur.execute("""
select distinct userid
from users;
""")
res = cur.fetchall()
cur.close()
return [int(r[0]) for r in res]
def main():
event = sys.argv[1]
query = "SELECT * FROM event WHERE event='%s'" % event
cursor = db.cursor()
cursor.execute(query)
for row in cursor:
event, stream, start, end = row[0], row[1], row[2], row[3]
vector = np.asarray(row[4:])
query = "SELECT "
for col in data['cols'][:-1]:
query += "%s, " % col
query += "%s " % data['cols'][-1]
query += "FROM hcdm_vectors"
cursor = db.cursor()
cursor.execute(query)
results = {}
for row in cursor:
key = (row[0], row[1], row[2])
mvector = np.asarray(row[3:])
sim = csim(vector, mvector)
results[key] = sim
results = sorted(results.items(), key=lambda x: x[1], reverse=True)
final = {}
for item in results:
nstream, nstart, nend = item[0][0], item[0][1], item[0][2]
if nstream not in final and nstream != stream:
final[nstream] = (nstart, nend, item[1])
final = sorted(final.items(), key=lambda x: x[1][2], reverse=True)
print('%s,%s,%s' % (stream, start, end))
for i in range(100):
print('%s,%s,%s,%s' % (final[i][0], final[i][1][0], final[i][1][1], final[i][1][2]))
sys.stdout.flush()
def on_get(self, req, resp):
'''Return json object related to GET with lat & lon HTTP parameters
'''
raw_lat = req.get_param('lat')
raw_lon = req.get_param('lon')
if raw_lat is None or raw_lon is None:
resp.status = falcon.HTTP_400
lat = None
lon = None
else:
lat = decimal.Decimal(raw_lat)
lon = decimal.Decimal(raw_lon)
if not (self.lat_is_valid(lat) and self.lon_is_valid(lon)):
resp.status = falcon.HTTP_400
else:
cur = db.cursor()
nb_maps_raw = req.get_param('nb_maps')
nb_addr_raw = req.get_param('nb_addr')
if nb_maps_raw is None:
nb_maps_raw = '20'
if nb_addr_raw is None:
nb_addr_raw = '10'
nb_maps = decimal.Decimal(nb_maps_raw)
nb_addr = decimal.Decimal(nb_addr_raw)
#id (uuid), path (str), geom (geom), address (str), level (str), building (str)
loc = "st_setsrid(st_makepoint(%s,%s),4326)" % (lon,lat)
query = "SELECT array_to_json(array_agg(row_to_json(t)))::text FROM ("
query += " SELECT floor(st_distance(geom::geography, %s::geography)) as dist, " % loc
query += " ST_X(geom) as lon, ST_Y(geom) as lat, * FROM ((select * from ban_nomap where ST_DWithin(geom, %s, 0.001) order by st_distance(geom,%s) limit %s) union (select * from map_info where ST_DWithin(geom, %s, 0.1) limit %s)) as d ORDER BY ST_Distance(geom,%s), level" % (
loc,loc,adapt(nb_addr),loc,adapt(nb_maps),loc)
query += " ) t"
cur.execute(query)
what_is_around = cur.fetchone()[0]
resp.set_header('X-Powered-By', 'OpenEvacMap')
if what_is_around is None:
resp.status = falcon.HTTP_204
else:
resp.status = falcon.HTTP_200
resp.set_header('Access-Control-Allow-Origin', '*')
resp.set_header('Access-Control-Allow-Headers', 'X-Requested-With')
resp.body = (what_is_around)
query = """INSERT INTO log (loc, ip) VALUES (ST_SetSRID(ST_GeometryFromText('POINT(%s %s)'),4326),'%s');""" % (lon,lat,req.env['REMOTE_ADDR'])
cur.execute(query)
db.commit()
cur.close()
def ban_roadblock_author(bot, query):
if query.message.chat.id == mods_chat:
nmaps_message = retrieve_roadblock(mods_id=query.message.message_id)
else:
nmaps_message = retrieve_roadblock(roads_id=query.message.message_id)
query.edit_message_text(BOT_USER_BANNED.format(user_name(query.from_user),
query.from_user.id),
parse_mode='markdown')
c = db.cursor()
c.execute('INSERT INTO banned VALUES(%s)', (nmaps_message['user_id'], ))
db.commit()
c.close()
def create_user_table(wallet_userid):
cur = db.cursor()
cur.execute("""
create table users(
wallet varchar(34) primary key,
userid int
);
""")
execute_values(cur, "insert into users (wallet, userid) values %s",
wallet_userid.items())
db.commit()
cur.close()
def update_subscription(bot, update):
c = db.cursor()
if not subscribed(update.message.from_user.id):
c.execute('INSERT INTO subscribers VALUES (%s)',
(update.message.from_user.id, ))
update.message.reply_text(BOT_SUBSCRIBED_USR,
reply_markup=get_keyboard(update, True))
else:
c.execute('DELETE FROM subscribers WHERE id=(%s)',
(update.message.from_user.id, ))
update.message.reply_text(BOT_UNSUBSCRIBED_USR,
reply_markup=get_keyboard(update, False))
db.commit()
c.close()
def output_data() -> None:
with db.cursor() as cur:
cur.execute(OUTPUT_DATA_SQL_FILE.read_text())
rows = cur.fetchall()
assert len(rows) > 0, "At least one row must be returned"
columns = [
column for column in rows[0].keys() if column not in IGNORE_COLUMNS
]
data: List[Dict[str, Any]] = []
for row in rows:
data.append({column: row[column] for column in columns})
data_json = json.dumps(data, indent=2)
OUTPUT_DATA_JS_FILE.write_text(f"var data = {data_json};\n")
print(f"Wrote {OUTPUT_DATA_JS_FILE}.")
def unhide(keep):
with db.cursor() as cur:
for nid, data in cur:
note = keep.get(nid)
data = data.decode()
title, text = data.split(TEXT_TITLE_SEP, maxsplit=1)
note.title = title
note.text = text
note.archived = True
note.pinned = False
print_note(note)
keep.sync(dump=True)
drop_db()
def saveUserDetails(self):
username = self.txt_username.toPlainText()
new_password = self.txt_newPassword.toPlainText()
old_password = self.txt_oldPassword.toPlainText()
url = self.txt_url.toPlainText()
cursor = db.cursor()
cursor.execute(
'''INSERT INTO databasecredentials(url, usernname,password)
VALUES(?,?,?)''', (url, username, new_password))
db.commit()
self.txt_url.setText("")
self.txt_username.setText("")
self.txt_newPassword.setText("")
self.txt_oldPassword.setText("")
def on_get(self, req, resp):
'''Return JPEG map image according to id param
'''
map_id = req.get_param('id')
if not self.map_id_is_valid(map_id):
resp.status = falcon.HTTP_400
else:
cur = db.cursor()
#id (uuid), path (str), geom (geom), address (str), level (str), building (str)
query = "SELECT path, ST_astext(geom) as geom FROM %s" % self._table
query += " WHERE id='%s'" % (map_id)
cur.execute(query)
cur_map = cur.fetchall()
map_path = cur_map[0][0]
map_geom = cur_map[0][1]
resp.set_header('X-Powered-By', 'OpenEvacMap')
if map_path is None:
resp.status = falcon.HTTP_404
else:
full_path = os.path.join(config.map_dir,map_path)
preview = req.get_param('preview')
if preview is None:
preview = '0'
if preview=='1':
outfile = os.path.splitext(full_path)[0] + "_preview.jpg"
print(outfile)
if os.path.exists(outfile)==False:
im = Image.open(full_path)
im.thumbnail(preview_size, Image.ANTIALIAS)
im.save(outfile, "JPEG")
full_path=outfile
resp.status = falcon.HTTP_200
resp.set_header('Access-Control-Allow-Origin', '*')
resp.set_header('Access-Control-Allow-Headers',
'X-Requested-With')
resp.set_header("Content-Type", "image/jpeg")
resp.set_header("Content-Length", "%s" % os.path.getsize(
full_path))
with open(full_path, "rb") as f:
resp.body = f.read()
query = """INSERT INTO log (loc, ip, map) VALUES (ST_GeometryFromText('%s'),'%s','%s');""" % (map_geom,req.env['REMOTE_ADDR'],map_id)
cur.execute(query)
db.commit()
cur.close()
def post(self):
current_id = get_jwt_identity()
args = parser.parse_args()
if args["college"] != "" and args["major"] != "":
with db.cursor(DictCursor) as cursor:
sql = "INSERT INTO `educations` (`college`, `major`, `degree`, `user_id`) VALUES (%s, %s, %s, %s)"
cursor.execute(
sql,
(args["college"], args["major"], args["degree"],
current_id),
)
db.commit()
return jsonify(status="success")
return jsonify(status="fail")
def post(self):
current_id = get_jwt_identity()
args = parser.parse_args()
error = None
if not args["title"]:
error = "수상내역은 필수 입력값입니다."
if error is None:
with db.cursor(DictCursor) as cursor:
sql = "INSERT INTO `awards` (`title`, `description`, `user_id`) VALUES (%s, %s, %s)"
cursor.execute(
sql, (args["title"], args["description"], current_id))
db.commit()
return jsonify(status="success")
return jsonify(status="fail", result={"message": error})
def print_response(response, url):
with closing(db.cursor()) as x:
for report in response.get('reports', []):
for row in report.get('data', {}).get('rows', []):
dateRangeValues = row.get('metrics', [])
for value in dateRangeValues:
val = (value.get('values'))
x.execute(
"""UPDATE search_console_data SET nusers = (%s), user_check = 1 WHERE url = (%s) AND date = (%s)""",
(val, url, my_date))
x.execute(
"""INSERT INTO ga_urls (ganusers, gaurl, gadate) VALUES (%s, %s, %s)""",
(val, url, my_date))
db.commit()
def bypass_moderators(bot, update):
update.message.reply_text(
BOT_MSG_ACCEPT.format(user_name(update.message.from_user),
update.message.from_user.id))
bot.forward_message(roads_chat, update.message.chat.id,
update.message.message_id)
roads_message = bot.send_message(roads_chat,
BOT_NEW_ROADBLOCK,
reply_markup=staff_keyboard)
c = db.cursor()
c.execute('INSERT INTO roads VALUES(%s, %s, %s, %s, %s)',
(update.message.from_user.id, update.message.chat.id,
update.message.message_id, 0, roads_message.message_id))
db.commit()
c.close()
def on_get(self, req, resp):
'''Return logged requests
'''
cur = db.cursor()
#id (uuid), path (str), geom (geom), address (str), level (str), building (str)
query = "SELECT array_to_json(array_agg(row_to_json(t)))::text FROM (select EXTRACT(epoch FROM age(time,now()))::integer as age, st_asgeojson(loc) as geometry from log where time > now()- interval '1 hour') as t;"
cur.execute(query)
log_data = cur.fetchone()[0]
resp.set_header('X-Powered-By', 'OpenEvacMap')
if log_data is None:
resp.status = falcon.HTTP_404
else:
resp.status = falcon.HTTP_200
resp.set_header('Access-Control-Allow-Origin', '*')
resp.set_header('Access-Control-Allow-Headers',
'X-Requested-With')
resp.body = (log_data)
cur.close()
def get(self):
username = self.session["username"]
email_md5 = self.session["email_md5"]
csrf_token = self.session["_csrf_token"]
c = db.cursor()
c.execute("""
SELECT
filename, i.image_id, i.upload_date, user_email_md5
FROM
yagra_user AS u,
yagra_image AS i
LEFT JOIN
yagra_user_head AS h ON i.image_id = h.image_id
WHERE
u.ID = i.user_id AND u.user_login = %s
""", (username, ))
html_string = Template.render("userhome",
username,
email_md5,
imgs=c.fetchall(),
csrf_token=csrf_token)
self.write(html_string)
def get(self):
cursor = db.cursor()
cursor.execute("SELECT user_email, user_login FROM yagra_user")
for row in cursor.fetchall():
email, name = row
self.write("%s: %s<br/>" % (name, email))
def assure_input_valid(self):
"""检查用户名、邮箱是否合法，返回小写的用户名、邮箱以及密码
return None or (username_lower, pwd, email_lower)
"""
self.set_status(403)
# 检查用户名
username = self.get_argument("username")
if not yagra_check_username_valid(username):
html_string = Template.render("error",
"username %s is not valid" % (
escape(username), ))
self.write(html_string)
return
email = self.get_argument("email")
# 检查密码
pwd = self.get_argument("password")
pwd2 = self.get_argument("password-again")
if pwd != pwd2:
html_string = Template.render("error",
"two password not matched")
self.write(html_string)
return # XXX
# 检查email
if not yagra_check_email_valid(email):
html_string = Template.render("error",
"email: %s is not valid."
% escape(email))
self.write(html_string)
return
cursor = db.cursor()
email_lower = email.lower()
cursor.execute("""
SELECT ID
FROM yagra_user
WHERE user_email = %s""", (email_lower, ))
if cursor.fetchone():
html_string = Template.render("error",
"email %s registered!"
% escape(email))
self.write(html_string)
return
username_lower = username.lower()
cursor.execute("""
SELECT ID
FROM yagra_user
WHERE user_login = %s""", (username_lower, ))
if cursor.fetchone():
html_string = Template.render("error",
"username %s registered!"
% escape(username))
self.write(html_string)
return
self.set_status(200)
return username_lower, pwd, email_lower
def on_post(self, req, resp, **kargs):
'''Update database according to JSON and give ID back
'''
address_id = kargs.get('id')
env = req.env
env.setdefault('QUERY_STRING', '')
form = cgi.FieldStorage(fp=req.stream, environ=env)
try:
file_item = form['image']
raw_lat_item = form['lat']
raw_lon_item = form['lon']
except KeyError:
raw_lat = None
raw_lon = None
image_file = None
else:
image_file = file_item.file
raw_lat = raw_lat_item.value
raw_lon = raw_lon_item.value
if raw_lat is None or raw_lon is None or image_file is None:
resp.status = falcon.HTTP_400
lat = None
lon = None
else:
lat = decimal.Decimal(raw_lat)
lon = decimal.Decimal(raw_lon)
if not (self.lat_is_valid(lat) and self.lon_is_valid(lon)):
resp.status = falcon.HTTP_400
else:
cur = db.cursor()
columns = ['geom', 'address']
values = ['st_Setsrid(st_makePoint(%s,%s),4326)' % (lon, lat),
"%s" % adapt(address_id)]
for param in ('name','building','level'):
try:
param_item = form[param]
except KeyError:
pass
else:
columns.append(param)
values.append("%s" % adapt(param_item.value))
insert = "INSERT INTO %s (%s)" % (self._table, ",".join(columns))
insert += " VALUES (%s)" % ",".join(values)
insert += " RETURNING *"
cur.execute(insert)
uuid = cur.fetchone()[0]
image_path = os.path.join(config.map_dir, "%s.jpg" % uuid)
with open(image_path, "wb") as f:
f.write(image_file.read())
cur2 = db.cursor()
update = "UPDATE %s" % self._table
update += " SET path=%s" % adapt(image_path)
update += " WHERE id=%s" % adapt(uuid)
cur2.execute(update)
db.commit()
cur.close()
cur2.close()
try:
redirect_url_item = form['redirectUrl']
redirect_url = redirect_url_item.value
#split_result = urllib.parse.urlsplit(redirect_url)
#netloc = split_result[1]
#if netloc != config.netloc:
# raise ValueError("What do you think you are doing Dave?")
except:
resp.status = falcon.HTTP_200
else:
resp.status = falcon.HTTP_302
resp.set_header('Location', redirect_url)
resp.set_header('Access-Control-Allow-Origin', '*')
resp.set_header('Access-Control-Allow-Headers', 'X-Requested-With')
