def logout_user(data):
####??? Bearer??
# if data:
# auth_token = data.split(" ")[1]
# else:
# auth_token = ''
auth_token = data
if auth_token:
resp = Auth.decode_auth_token(auth_token)
if not isinstance(resp, str):
conn = db.connect()
cursor = conn.cursor()
cursor.execute("update user set token = null where id = %s",
(resp, ))
response_object = {
'status': 'success',
'message': 'Successfully logged out.'
}
conn.commit()
cursor.close()
conn.close()
return response_object, 200
else:
response_object = {'status': 'fail', 'message': resp}
return response_object, 401
else:
response_object = {
'status': 'fail',
'message': 'Provide a valid auth token.'
}
return response_object, 403
def save_user_class(user_id, data):
class_id = data['class_id']
specialization_id = data['specialization_id']
item_ids = data['item_ids']
conn = db.connect()
cursor = conn.cursor()
sql = """
insert into user_class (
user_id, class_id, specialization_id, updated_datetime, created_datetime)
values (
%s, %s, %s, now(), now())
on duplicate key update
updated_datetime = values(updated_datetime)
"""
cursor.execute(sql, (user_id, class_id, specialization_id))
user_class_id = cursor.lastrowid
cursor.execute("delete from user_class_item where user_class_id = %s",
(user_class_id, ))
sql = """
insert into user_class_item (
user_class_id, item_id, created_datetime
) values (%s, %s, now())
"""
cursor.executemany(sql, [(user_class_id, val) for val in item_ids])
response_object = {'status': 'success', 'message': 'Successfully saved.'}
conn.commit()
cursor.close()
conn.close()
return response_object, 200
def get_logged_in_user(new_request):
# get the auth token
auth_token = new_request.headers.get('Authorization')
if auth_token:
resp = Auth.decode_auth_token(auth_token)
if not isinstance(resp, str):
conn = db.connect()
cursor = conn.cursor()
cursor.execute(
"select login_id, last_login, created_datetime from user where id = %s",
(resp, ))
rows = cursor.fetchall()
response_object = {
'status': 'success',
'data': {
'user_id': resp,
'login_id': rows[0][0],
'last_login': str(rows[0][1]),
'created_datetime': str(rows[0][2])
}
}
cursor.close()
conn.close()
return response_object, 200
response_object = {'status': 'fail', 'message': resp}
return response_object, 401
else:
response_object = {
'status': 'fail',
'message': 'Provide a valid auth token.'
}
return response_object, 401
def get_fight_styles():
conn = db.connect()
cursor = conn.cursor()
cursor.execute("select id, name from m_fight_style ")
fight_style = {row[0]: row[1] for row in cursor}
cursor.close()
conn.close()
return fight_style
def get_inventory_types():
conn = db.connect()
cursor = conn.cursor()
cursor.execute("select id, name, slot_to from m_inventory_type")
inventory = {row[0]: [row[1], row[2]] for row in cursor}
cursor.close()
conn.close()
return inventory
def login_user(data):
try:
user = {}
conn = db.connect()
cursor = conn.cursor()
cursor.execute(
"select id, login_id, password, last_login, created_datetime from user where login_id = %s",
(data.get('login_id'), ))
rows = cursor.fetchall()
if len(rows) == 1:
user['id'] = rows[0][0]
user['login_id'] = rows[0][1]
user['password'] = rows[0][2]
user['last_login'] = rows[0][3]
user['created_datetime'] = rows[0][4]
if bool(user) and flask_bcrypt.check_password_hash(
user['password'], data.get('password')):
auth_token = Auth.generate_auth_token(user['id'])
if auth_token:
cursor.execute(
"update user set last_login = now(), token = %s where id = %s",
(auth_token.decode(), user['id']))
response_object = {
'status': 'success',
'message': 'Successfully logged in.',
'Authorization': auth_token.decode()
}
conn.commit()
cursor.close()
conn.close()
return response_object, 200
else:
response_object = {
'status': 'fail',
'message': 'login id or password does not match.'
}
cursor.close()
conn.close()
return response_object, 401
except Exception as e:
print(e)
response_object = {'status': 'fail', 'message': 'Try again'}
return response_object, 500
def decode_auth_token(auth_token):
try:
payload = jwt.decode(auth_token, key)
conn = db.connect()
cursor = conn.cursor()
cursor.execute("select id from user where token = %s",
(str(auth_token), ))
rows = cursor.fetchall()
cursor.close()
conn.close()
if len(rows) == 0 or payload['sub'] != rows[0][0]:
raise jwt.InvalidTokenError
return payload['sub']
except jwt.ExpiredSignatureError:
return 'Signature expired. Please log in again.'
except jwt.InvalidTokenError:
return 'Invalid token. Please log in again.'
def save_new_user(data):
conn = db.connect()
cursor = conn.cursor()
cursor.execute("select id from user where login_id=%s",
(data['login_id'], ))
rows = cursor.fetchall()
if len(rows) == 0:
hashed_password = flask_bcrypt.generate_password_hash(
data['password']).decode('utf-8')
cursor.execute(
"insert into user (login_id, password, created_datetime) values (%s, %s, now())",
(
data['login_id'],
hashed_password,
))
cursor.execute("select id from user where login_id = %s",
(data['login_id'], ))
rows = cursor.fetchall()
user_id = rows[0][0]
auth_token = Auth.generate_auth_token(user_id)
cursor.execute(
"update user set token = %s, last_login = now() where id=%s", (
auth_token.decode(),
user_id,
))
response_object = {
'status': 'success',
'message': 'Successfully registered.',
'Authorization': auth_token.decode()
}
conn.commit()
cursor.close()
conn.close()
return response_object, 201
else:
response_object = {
'status': 'fail',
'message': 'User already exists. Please Log in.',
}
cursor.close()
conn.close()
return response_object, 409
def get_available_class_specializations():
conn = db.connect()
cursor = conn.cursor()
cursor.execute("select id, name, color from m_class")
classes = {
row[0]: {
'name': row[1],
'color': row[2],
'specializations': {}
}
for row in cursor
}
cursor.execute(
"select id, class_id, name from m_class_specialization where available = 1"
)
for row in cursor:
classes[row[1]]['specializations'][row[0]] = {"name": row[2]}
cursor.close()
conn.close()
return classes
def get_user_class(user_id, class_id, specialization_id):
ret = {}
conn = db.connect()
cursor = conn.cursor()
cursor.execute(
"select id from user_class where user_id = %s and class_id = %s and specialization_id = %s",
(user_id, class_id, specialization_id))
row = cursor.fetchone()
if row is not None:
user_class_id = row[0]
cursor.execute(
"select item_id from user_class_item where user_class_id = %s",
(user_class_id, ))
item_rows = cursor.fetchall()
ret['class_id'] = class_id
ret['specialization_id'] = specialization_id
ret['item_ids'] = [r[0] for r in item_rows]
cursor.close()
conn.close()
return ret
def score(data):
class_id = data['class_id']
specialization_id = data['specialization_id']
items = data['items']
fight_styles = get_fight_styles()
conn = db.connect()
cursor = conn.cursor()
cursor.execute(
"select id, fight_style_id, timestamp from crawler where class_id = %s and specialization_id = %s",
(class_id, specialization_id))
crawler_ids = []
timestamps = {}
for r in cursor:
crawler_ids.append(r[0])
timestamps[r[1]] = r[2].strftime("%Y-%m-%d %H:%M")
# crawler_ids = tuple([r[0] for r in cursor])
selected_items = {}
for item in items:
grouped_powers = {}
for azeritePower in item['azeritePowers']:
sql = "select sub_spell_name, sub_spell_id from crawler_score where crawler_id in " + str(
tuple(crawler_ids)
) + " and spell_id = %s group by sub_spell_name, sub_spell_id"
cursor.execute(sql, azeritePower['spellId'])
for r in cursor:
power = {
'spellId': azeritePower['spellId'],
'spellName': azeritePower['spellName'],
'subSpellName': r[0],
'subSpellId': r[1],
'tier': azeritePower['tier']
}
if azeritePower['tier'] in grouped_powers:
grouped_powers[azeritePower['tier']].append(power)
else:
grouped_powers[azeritePower['tier']] = [
power,
]
del item['azeritePowers'] ###
for power_comb in product(*grouped_powers.values()):
selected_item = {
"id":
item['id'],
"name":
item['name'],
"slotTo":
item['slotTo'],
"inventoryName":
item['inventoryName'],
"icon":
item['icon'],
"selectedPower":
sorted(power_comb, key=lambda p: p["tier"], reverse=True)
}
if item['slotTo'] in selected_items:
selected_items[item['slotTo']].append(selected_item)
else:
selected_items[item['slotTo']] = [
selected_item,
]
score_data = {}
sql = """
select c.fight_style_id, cs.spell_id, cs.sub_spell_name, cs.count, cs.score
from crawler_score cs inner join crawler c on cs.crawler_id = c.id
where c.id in %s
order by cs.count desc, cs.item_level desc """ % str(tuple(crawler_ids))
cursor.execute(sql)
for r in cursor:
key = "%s %s" % (r[1], r[2])
if r[0] in score_data:
if key in score_data[r[0]]:
if r[3] not in score_data[r[0]][key]:
score_data[r[0]][key][r[3]] = r[4]
else:
score_data[r[0]][key] = {r[3]: r[4]}
else:
score_data[r[0]] = {key: {r[3]: r[4]}}
ret = {
"class_id": class_id,
"specialization_id": specialization_id,
"timestamps": timestamps,
"score_order": {}
}
scored_items = []
for item_comb in product(*selected_items.values()):
item_set = {"items": item_comb}
power_set = {}
for item in item_comb:
for power in item['selectedPower']:
power_key = "%s %s" % (power['spellId'], power['subSpellName'])
if power_key in power_set:
power_set[power_key] += 1
else:
power_set[power_key] = 1
##scoring
item_set["score"] = {}
for p in power_set:
for fight_style_id in fight_styles:
score = 0
if fight_style_id in score_data:
if p in score_data[fight_style_id]:
if power_set[p] in score_data[fight_style_id][p]:
score = score_data[fight_style_id][p][power_set[p]]
if fight_style_id in item_set["score"]:
item_set["score"][fight_style_id] += score
else:
item_set["score"][fight_style_id] = score
item_set["score"][3] = sum(item_set["score"].values())
scored_items.append(item_set)
## sorting
ret["score_order"]["1"] = sorted(scored_items,
key=lambda i: i["score"][1],
reverse=True)[:3]
ret["score_order"]["2"] = sorted(scored_items,
key=lambda i: i["score"][2],
reverse=True)[:3]
ret["score_order"]["3"] = sorted(scored_items,
key=lambda i: i["score"][3],
reverse=True)[:3]
## get subspell name
sub_spells = {}
for f in ret["score_order"]:
for s in ret["score_order"][f]:
for i in s["items"]:
for p in i["selectedPower"]:
sub_spells_ko = ""
if p["subSpellId"] is not None:
for n in p["subSpellId"].split("+"):
if n != "":
if n in sub_spells:
sub_spells_ko += "+" + sub_spells[n]
else:
ko = get_spell_info(n)['name']
sub_spells_ko += "+" + ko
sub_spells[n] = ko
p["subSpellNameKor"] = sub_spells_ko
return ret
