def card_filter(f: Optional[Dict] = None):
sql = "select * from `card` a where "
if 'name' in f:
name = f'%{f["name"]}%'
sql += f"a.`card name` like '{escape_string(name)}' and "
if 'cost' in f:
cost = f.getlist('cost')
if "7" in cost:
cost.extend([8, 9, 10])
sql += cond_constructer("a.`cost`", cost, "and")
if 'class_display' in f:
if f['class_display'] == 'neutral only':
sql += cond_constructer("a.`class`", ['Neutral'], "and")
else:
sql += cond_constructer("a.`class`", ['Neutral'], "and", False)
if 'class' in f:
c = (f['class'], 'Neutral')
sql += cond_constructer("a.`class`", c, "and")
if 'rare' in f:
sql += cond_constructer("a.`rare`", f.getlist('rare'), "and")
if 'type' in f or 'race' in f or 'mechanism' in f:
sql += "a.`card id` = Any(select b.`card id` from `card effect view` b where "
if 'type' in f:
sql += cond_constructer("b.`effect type`", f.getlist('type'), "or")
if 'race' in f:
sql += cond_constructer("b.`effect type`", f.getlist('race'), "or")
if 'mechanism' in f:
sql += cond_constructer("b.`effect type`", f.getlist('mechanism'), "or")
sql += 'false) and '
sql += 'true order by a.`cost`, a.`card name`'
with DataBase() as db:
ret = db.query(sql)
return ret
def insert_match(form) -> str:
filed = [
"round", "winner id", "loser id", "winner desk id", "loser desk id"
]
for x in filed:
if x not in form or form[x] == "":
raise Exception(f"Error: {x} should be filled!")
with DataBase() as db:
db.insert("match", form)
return "success"
def match():
info = None
if request.method == "POST":
try:
insert_match(request.form)
info = "Success!"
except Exception as e:
info = str(e)
with DataBase() as db:
players = db.query("select * from `player` order by `player id`")
return render_template('match.html', players=players, info=info)
def insert_data(dir_path, database):
with DataBase(database) as db:
with open(os.path.join(dir_path, "cards.json")) as f:
for card in json.load(f):
db.add_card(card)
with open(os.path.join(dir_path, "desks.json")) as f:
for desk in json.load(f):
db.add_desk(desk)
with open(os.path.join(dir_path, "users.json")) as f:
for user in json.load(f):
db.add_user(user)
def desk():
with DataBase() as db:
desks = db.query("select * from `desk view`")
for x in desks:
if x["match times"] is None:
x["winner rate"] = 0
continue
tmp = x["win"] + x["lose"] - x["match times"]
if x["match times"] == tmp:
x["winner rate"] = 0
else:
x["winner rate"] = (x["win"] - tmp) / (x["match times"] - tmp)
sorted(desks, key=lambda x: x["winner rate"])
return render_template('desks.html', desks=desks)
def get_player_desk(idx: int):
with DataBase() as db:
desks = db.query('select `desk id` id, `name` name from `player desk view` where `player id`=%s order by `desk id`', args=[int(idx)])
return jsonify(desks)
def desk_detail(idx: int):
with DataBase() as db:
desk = db.query("select * from `desk` where `desk id` = %s", args=[int(idx)])[0]
cards = db.query("select b.`cost` cost, b.`card name` `card name`, b.`class` `class`, b.`rare` rare from `desk detail` a join `card` b on a.`card id` = b.`card id` where a.`desk id` = %s order by b.`cost`, b.`card name`", args=[int(idx)])
title = ["cost", "card name", "class", "rare"]
return render_template('desk_detail.html', desk=desk, cards=cards, titles=title)
def create_table(file, database):
with DataBase(database) as db:
db.execute_file(file)