def run_draft(match_id, team_id):
c = Cursor("emporium_draft")
items = c.execute(ITEMS_FOR_DRAFT_QUERY, (match_id, team_id)).fetchall()
for i, item in enumerate(items):
if not i % 100:
print " processing item #%s" % i
draft_item(c, match_id, team_id, item)
def send_scheduled_mails():
""" Send scheduled mails
"""
with Cursor() as cursor:
cursor.prepare("""
SELECT * FROM scheduled_mails
WHERE active is TRUE
AND (schedule_day = (EXTRACT(DOW FROM NOW()) + 1) OR schedule_day = 0)
""")
cursor.exec_()
now = QtCore.QDate.currentDate()
while cursor.next():
unit = cursor.value('schedule_unit')
interval = cursor.value('schedule_interval')
mail_data = {}
# Exlude mail when it's too soon to send them
if cursor.value('last_sent').isValid():
if unit not in UNIT_TO_FUNC:
continue
send_date = UNIT_TO_FUNC[unit](cursor.value('last_sent'),
interval)
if send_date > now:
continue
# Ok send mail
recipients = api.mail.get_recipients(
cursor.value('filter'),
cursor.value('filter_value'),
)
for recipient in recipients:
if recipient['hidden']:
continue
subject = api.mail.format_message(cursor.value('subject'),
recipient)
message = api.mail.format_message(cursor.value('message'),
recipient)
api.mail.send_mail(recipient['mail'], subject, message,
cursor.value('sender'))
# Update database.
with Cursor() as update_cursor:
update_cursor.prepare("""
UPDATE scheduled_mails SET last_sent=DATE(NOW())
WHERE name=:name
""")
update_cursor.bindValue(":name", cursor.value('name'))
update_cursor.exec_()
if update_cursor.lastError().isValid():
print(update_cursor.lastError().text())
def get_possible_filter_values(col, filters):
""" Get all the possible values for a filter given all other applied filters
:param str col: db column name
:param dict filters: A dict giving corresponding value to filters
"""
if not filters:
sqlfilters = []
else:
sqlfilters = [c + "=:" + c for c in filters if c != col]
if col == 'product' and filters.get('category', '') != "Note":
sqlfilters.append("category != 'Note'")
with Cursor() as cursor:
cursor.prepare("""
SELECT DISTINCT {c} FROM transactions
{w} {filters}
ORDER BY {c}
""".format(c=col,
w="WHERE" * bool(sqlfilters),
filters=' AND '.join(sqlfilters)))
for key, value in filters.items():
cursor.bindValue(":{}".format(key), value)
cursor.exec_()
if col not in FILTER_FIELDS_CACHE:
FILTER_FIELDS_CACHE[col] = cursor.indexOf(col)
while cursor.next():
yield cursor.value(FILTER_FIELDS_CACHE[col])
def remove_notes(note_names, category_name):
""" Remove notes from a category
:param list note_names: A list of nicknames of notes to remove from this category
:param str category_name: The category name
:return bool: True if the notes have been removed, False otherwise
"""
with Cursor() as cursor:
cursor.prepare("""
DELETE FROM note_categories_assoc WHERE
note=(SELECT id FROM notes WHERE nickname=:note) AND
category=(SELECT id FROM note_categories WHERE name=:category)
""")
cursor.bindValue(":note", note_names)
cursor.bindValue(":category", [
category_name,
] * len(note_names))
ret = cursor.execBatch()
if ret:
for note in note_names:
api.notes.rebuild_note_cache(note)
api.redis.send_message("enibar-notes", note_names)
return ret
def perform_query(self):
args = request.args
parameter = 'pm10' # parameter will be used as part of an fstring in the query, but don't worry
# as we check it against a whitelist
if 'parameter' in args:
parameter = args['parameter']
if parameter not in MyWebService.allowed_parameters:
raise ValueError("Invalid parameter passed")
retVal = dict()
with Cursor(Database()) as c:
for row in c.execute(
f"""select longitude, latitude, avg(value) as val, "average" as type, date
from air_quality
where date > strftime('%s', datetime('now') - 3*60*60)
and parameter = '{parameter}'
group by 1, 2
union
select longitude, latitude, value as val, "last_data" as type, date
from air_quality a1
where date=(select max(date) from air_quality a2 where a1.longitude=a2.longitude and a1.latitude=a2.latitude and a1.parameter = a2.parameter)
and parameter = '{parameter}'
order by date asc"""
): # date is necessary to be in the query because of the "order by" clause
cur = MyWebService.GeoPoint(lng=row[0], lat=row[1])
if row[3] == 'average' or cur not in retVal:
retVal[cur] = row[2]
# return jsonify(list({'lng': k.lng, 'lat': k.lat, 'val': v} for k, v in retVal.items()))
return jsonify(list({'lng': k.lng, 'lat': k.lat, 'val': MyWebService._get_val(v, parameter)} for k, v in retVal.items()))
def test_get(self):
""" Testing get price function """
self.assertEqual(list(api.prices.get()), [])
price_desc_id = api.prices.add_descriptor("Unité", self.cat_drink, 100)
pid = api.products.add("Lapin", category_id=self.cat_drink)
price_id = None
with Cursor() as cursor:
cursor.prepare("SELECT id FROM prices WHERE product=?")
cursor.addBindValue(pid)
self.assertTrue(cursor.exec_())
if cursor.next():
price_id = cursor.record().value('id')
self.assertIsNotNone(price_id)
self.assertEqual(list(api.prices.get()), [{
'alcoholic': False,
'id': price_id,
'label': "Unité",
'value': 0,
'product': pid,
'category': self.cat_drink,
}])
api.prices.add(None, price_desc_id, 0)
self.assertTrue(self.count_prices(), 2)
self.assertEqual(list(api.prices.get(id=price_id)),
[{
'alcoholic': False,
'id': price_id,
'label': "Unité",
'value': 0,
'product': pid,
'category': self.cat_drink,
}])
def get_notes_stats():
""" Yield dicts representing stats.
{'nickname', 'product', 'price_name', 'price', 'category', 'quantity'}
"""
global STATS_FIELDS_CACHE
with Cursor() as cursor:
cursor.prepare("SELECT notes.nickname AS nickname,\
transactions.product AS product,\
transactions.price_name AS price_name,\
transactions.price/transactions.quantity AS price,\
transactions.category AS category,\
SUM(transactions.quantity) AS quantity \
FROM transactions JOIN notes ON \
((notes.lastname = transactions.lastname AND \
notes.firstname = transactions.firstname) OR \
notes.nickname = transactions.note) AND \
notes.stats_inscription = TRUE \
GROUP BY notes.nickname, transactions.product,\
transactions.price_name, transactions.price, \
transactions.quantity, transactions.category")
cursor.exec_()
while cursor.next():
if not STATS_FIELDS_CACHE:
STATS_FIELDS_CACHE = {field: cursor.indexOf(field) for field in STATS_FIELDS}
yield {field: cursor.value(STATS_FIELDS_CACHE[field]) for field in STATS_FIELDS}
def delete_user(user_id):
try:
with Cursor() as cur:
cur.execute('DELETE FROM users WHERE id = %s', (user_id, ))
return server.ok(message='successfully deleted user')
except:
return server.error('unable to delete user')
def filtered_getter(table, filter_, reverse=False, max_=None, order_by="id"):
""" This creates a request in the table table with the filter filter_ and
returns the cursor of this request for future use.
"""
with Cursor() as cursor:
filters = []
for key in copy.copy(filter_):
if key.endswith('__gt'):
filter_[key[:-4]] = filter_[key]
del filter_[key]
filters.append("{key}>:{key}".format(key=key[:-4]))
else:
filters.append("{key}=:{key}".format(key=key))
cursor.prepare("SELECT * FROM {} {} {} {} {} {}".format(
table,
"WHERE" * bool(len(filters)),
" AND ".join(filters),
"ORDER BY {} DESC".format(order_by) * reverse,
"ORDER BY {} ASC".format(order_by) * (not reverse),
"LIMIT {}".format(max_) * bool(max_),
))
for key, arg in filter_.items():
cursor.bindValue(":{}".format(key), arg)
if cursor.exec_():
return cursor
def save_model(name, subject, message, filter_, filter_value):
""" Save mail model
:param str name: Model name
:param str subject: Model subject
:param str message: Model message
:param int filter_: Filter used to match notes when sending
:param str filter_value: Filter additioal value
:return bool: Operation succes or not
"""
try:
subject.format(**COMPLETION_FIELD)
message.format(**COMPLETION_FIELD)
except KeyError:
return False
with Cursor() as cursor:
cursor.prepare("""
INSERT INTO mail_models(name, subject, message, filter, filter_value)
VALUES (:name, :subject, :message, :filter, :filter_value)
ON CONFLICT (name) DO UPDATE SET subject=:subject, message=:message,
filter=:filter, filter_value=:filter_value
""")
cursor.bindValue(':name', name)
cursor.bindValue(':subject', subject)
cursor.bindValue(':message', message)
cursor.bindValue(':filter', filter_)
cursor.bindValue(':filter_value', filter_value)
cursor.exec_()
return not cursor.lastError().isValid()
def add_notes(note_names, category_name):
""" Add notes to the note category
:param list note_names: A list of nicknames of notes to add to this category
:param str category_name: The category name
:return bool: True if the notes have been added, False otherwise
"""
with Cursor() as cursor:
cursor.prepare("""
INSERT INTO note_categories_assoc (note, category)
VALUES((SELECT id FROM notes WHERE nickname=:note),
(SELECT id FROM note_categories WHERE name=:category))
""")
cursor.bindValue(':note', note_names)
cursor.bindValue(':category', [
category_name,
] * len(note_names))
ret = cursor.execBatch()
if ret:
for note in note_names:
api.notes.rebuild_note_cache(note)
api.redis.send_message("enibar-notes", note_names)
return ret
def get_descriptor(**kwargs):
""" Get price descriptor
:param \*\*kwargs: filters to apply
"""
global PRICE_DESCRIPTOR_FIELDS_CACHE
with Cursor() as cursor:
request_filters = []
for key in kwargs:
request_filters.append("{key}=:{key}".format(key=key))
request = "SELECT * FROM price_description WHERE {}".format(
" AND ".join(request_filters))
cursor.prepare(request)
for key, arg in kwargs.items():
cursor.bindValue(":{}".format(key), arg)
cursor.exec_()
while cursor.next():
if PRICE_DESCRIPTOR_FIELDS_CACHE == {}:
PRICE_DESCRIPTOR_FIELDS_CACHE = {
f: cursor.indexOf(f)
for f in PRICE_DESCRIPTOR_FIELDS
}
yield {
field: cursor.value(PRICE_DESCRIPTOR_FIELDS_CACHE[field])
for field in PRICE_DESCRIPTOR_FIELDS
}
def add_hidden_notes_category(self, name):
category = api.note_categories.add(name)
with Cursor() as cursor:
cursor.prepare(
"UPDATE note_categories SET hidden=TRUE WHERE name=:name")
cursor.bindValue(':name', name)
cursor.exec_()
return category
def remove(price_id):
""" Remove price
:param int price_id: Price id
"""
with Cursor() as cursor:
cursor.prepare("DELETE FROM prices WHERE id=:id")
cursor.bindValue(':id', price_id)
return cursor.exec_()
def create_user(steamid, group=UserGroup.NORMAL):
"""
Attempts to create a user based on their steamid, and optionally a group.
This expectes to fail if the steamid already exists, and will raise `psycopg2.IntegrityError`
when it does.
"""
with Cursor() as c:
now = datetime.utcnow()
return c.execute(CREATE_USER_QUERY, (steamid, True, now, now, group, Cursor.json(DEFAULT_SETTINGS))).fetchone().id
def remove_descriptor(descriptor_id):
""" Remove price from category
:param int descriptor_id: Price descriptor id
:return bool: True if operation succeed
"""
with Cursor() as cursor:
cursor.prepare("DELETE FROM price_description WHERE id=:id")
cursor.bindValue(":id", descriptor_id)
return cursor.exec_()
def delete_model(name):
""" Delete model with given name
:param str name: Model name
"""
with Cursor() as cursor:
cursor.prepare("DELETE FROM mail_models WHERE name=:name")
cursor.bindValue(':name', name)
cursor.exec_()
return not cursor.lastError().isValid()
def delete_scheduled_mail(name):
""" Delete scheduled mail
:param str name: Name of the mail you want to delete
:return bool: Operation success
"""
with Cursor() as cursor:
cursor.prepare("DELETE FROM scheduled_mails WHERE name=:name")
cursor.bindValue(':name', name)
cursor.exec_()
return not cursor.lastError().isValid()
def save_scheduled_mails(name, active, sched_int, sched_unit, sched_day,
filter_, filter_val, subject, sender, message,
last_sent):
""" Save scheduled mail
:param str name: Scheduled mail name
:param bool active: Enable mail sending if True
:param int sched_int: Schedule interval
:param str sched_unit: One of the following: day, week, month
:param int sched_day: Day the mail should be sent on
:param int filter_: Filter used to match notes to send to
;parma str filter_val: Filter value
:param str subject: Mail subject
:param str sender: Mail sender
:param str message: Mail content
:param QDateTime last_sent: Last date message was sent
:return bool: Operation success
"""
try:
subject.format(**COMPLETION_FIELD)
message.format(**COMPLETION_FIELD)
except KeyError:
return False
with Cursor() as cursor:
cursor.prepare("""
INSERT INTO scheduled_mails(name, active, schedule_interval,
schedule_day, filter, filter_value, subject, sender, message,
schedule_unit, last_sent)
VALUES(:name, :active, :sched_interval, :sched_day, :filter,
:filter_val, :subject, :sender, :message, :schedule_unit,
:last_sent)
ON CONFLICT (name) DO UPDATE SET name=:name, active=:active,
schedule_interval=:sched_interval, schedule_day=:sched_day,
filter=:filter, filter_value=:filter_val, subject=:subject,
message=:message, sender=:sender, schedule_unit=:schedule_unit,
last_sent=:last_sent
""")
cursor.bindValue(':name', name)
cursor.bindValue(':active', bool(active))
cursor.bindValue(':sched_interval', sched_int)
cursor.bindValue(':sched_day', sched_day)
cursor.bindValue(':filter', filter_)
cursor.bindValue(':filter_val', filter_val)
cursor.bindValue(':subject', subject)
cursor.bindValue(':sender', sender)
cursor.bindValue(':message', message)
cursor.bindValue(':schedule_unit', sched_unit)
cursor.bindValue(':last_sent', last_sent)
cursor.exec_()
return not cursor.lastError().isValid()
def set_value(id_, value):
""" Set price value
If you have to change value of multiple prices use set_multiple_values
instead.
:param int id_: price id
:param float value: new price value
"""
with Cursor() as cursor:
cursor.prepare("UPDATE prices SET value=:value WHERE id=:id")
cursor.bindValue(":id", id_)
cursor.bindValue(":value", value)
return cursor.exec_()
def rollback_transaction(id_, full=False):
""" Rollback transaction
And refill note with money
:param int id_: Transaction id:
:param bool full: Rollback the full transaction ?
"""
trans = get_unique(id=id_)
if not trans:
return False
try:
def filter_(x):
return x['firstname'] == trans['firstname'] and \
x['lastname'] == trans['lastname']
note = api.notes.get(filter_)[0]
except IndexError:
return False
quantity = int(trans['quantity'])
with Cursor() as cursor:
if quantity > 1 and not full:
cursor.prepare("UPDATE transactions SET quantity=quantity - 1,\
price=:price WHERE id=:id AND deletable=TRUE")
price = trans['price'] / quantity
cursor.bindValue(':price', trans['price'] - price)
cursor.bindValue(':id', trans['id'])
task = api.sde.send_history_lines([{
"id": trans["id"],
"price": trans["price"] - price,
"quantity": quantity - 1
}])
else:
task = api.sde.send_history_deletion([trans['id']])
cursor.prepare("DELETE FROM transactions WHERE id=:id "
"AND deletable=TRUE")
cursor.bindValue(':id', trans['id'])
price = trans['price']
cursor.exec_()
if not cursor.lastError().isValid() and cursor.numRowsAffected() > 0:
asyncio.ensure_future(task)
api.redis.send_message("enibar-notes", [note['nickname']])
return True
task.close()
return False
def test_protected_categories(self):
note_categories.add("test")
with Cursor() as cursor:
cursor.prepare(
"UPDATE note_categories SET protected=TRUE WHERE name='test'")
cursor.exec_()
note_categories.delete(["test"])
note_categories.set_hidden(["test"], True)
note_categories.rename("test", "test2")
self.assertEqual(list(note_categories.get()), [{
'name': 'test',
'hidden': False,
'id': 1,
'protected': 1
}])
def change_descriptor(id_, name, quantity):
""" Change price descriptor name and quantity
:param int id_: Price descriptor id
:param int quantity: The quantity (in mL) of the product
:return bool: True if operation succeed
"""
with Cursor() as cursor:
cursor.prepare(
"UPDATE price_description SET label=:label, quantity=:quantity WHERE id=:id"
)
cursor.bindValue(":id", id_)
cursor.bindValue(":label", name)
cursor.bindValue(":quantity", quantity)
return cursor.exec_()
def rename(old_name, new_name):
""" Rename a note category
:param str old_name: Old category name
:param str new_name: New category name
"""
with Cursor() as cursor:
cursor.prepare(
"UPDATE note_categories SET name=:new_name WHERE name=:old_name AND protected=FALSE"
)
cursor.bindValue(":new_name", new_name)
cursor.bindValue(":old_name", old_name)
cursor.exec_()
def rename_scheduled_mail(name, newname):
""" Rename scheduled mail
:param str name: Mail name
:param str newname: New mail name
:return bool: Operation success
"""
with Cursor() as cursor:
cursor.prepare("""
UPDATE scheduled_mails
SET name=:newname
WHERE name=:name
""")
cursor.bindValue(':name', name)
cursor.bindValue(':newname', newname)
cursor.exec_()
return not cursor.lastError().isValid()
def admin_edit_game():
game = request.values.get("game")
g.cursor.execute("SELECT id FROM games WHERE id=%s", (game, ))
if not g.cursor.fetchone():
raise APIError("Invalid Game")
query = {k: v for (k, v) in request.values.iteritems() if k in GAME_EDITABLE_FIELDS}
if not len(query):
raise APIError("Nothing to change!")
sql = "UPDATE games SET {} WHERE id=%(id)s".format(Cursor.map_values(query))
query['id'] = game
g.cursor.execute(sql, query)
return APIResponse()
def admin_user_edit():
user = request.values.get("user")
print user
g.cursor.execute("SELECT id FROM users WHERE id=%s", (user, ))
if not g.cursor.fetchone():
raise APIError("Invalid User")
query = {k: v for (k, v) in request.values.iteritems() if k in USER_EDITABLE_FIELDS}
if not len(query):
raise APIError("Nothing to change!")
sql = "UPDATE users SET {} WHERE id=%(id)s".format(Cursor.map_values(query))
query['id'] = user
g.cursor.execute(sql, query)
return APIResponse()
def change_ecocups(nick, diff, do_not=False):
""" Change the number of ecocups taken on a note.
:param str nick: The nickname og the note
:param int diff: The number of ecocups to add.
"""
with Cursor() as cursor:
cursor.prepare("UPDATE notes SET ecocups=ecocups+:diff WHERE\
nickname=:nick")
cursor.bindValue(":diff", diff)
cursor.bindValue(":nick", nick)
value = cursor.exec_()
note = NOTES_CACHE[nick]
note['ecocups'] = note['ecocups'] + diff
api.redis.send_message("enibar-notes", [
nick,
])
return value
def create_match(user, game, teams, meta, lock_date, match_date, public_date, view_perm=UserGroup.NORMAL):
validate_match_team_data(teams)
with Cursor() as c:
c.execute(CREATE_MATCH_SQL, {
"game": game,
"teams": teams,
"meta": meta,
"results": Cursor.json({}),
"lock_date": lock_date,
"match_date": match_date,
"public_date": public_date,
"view_perm": view_perm,
"active": False,
"created_at": datetime.utcnow(),
"created_by": user
})
return c.fetchone().id
def process_item(name, image, data):
with Cursor() as c:
pdata = c.execute("SELECT id, price FROM items WHERE name=%s", (name, )).fetchone()
if pdata:
return (pdata.id, float(pdata.price))
try:
i_vol, i_low, i_med = steam.market(730).get_item_price(name)
except SteamAPIError:
log.exception("Failed to get price for item '%s'" % name)
new = c.execute("INSERT INTO items (name, price, meta) VALUES (%s, %s, %s) RETURNING id", (
name, i_low, Cursor.json({
"image": image,
"descriptions": data
})
)).fetchone()
return (new.id, i_low)
def create_user(first_name, last_name, email, password, created_by):
password_hash = bcrypt.hashpw(password.encode('utf8'),
bcrypt.gensalt()).decode('utf8')
try:
with Cursor() as cur:
cur.execute(
'INSERT INTO users '
'(first_name, last_name, email, password, created_by) '
'VALUES '
'(%s, %s, %s, %s, %s) '
'RETURNING *',
(first_name, last_name, email, password_hash, created_by))
emails.new_user(email, password)
return server.ok(data=cur.fetchone())
except:
return server.error('unable to create user')
def get_users(page_size, page_index, name):
query = "SELECT * FROM users WHERE type = 'regular' "
if name:
query += "AND levenshtein(first_name, '{0}') <= 2 or levenshtein(last_name, '{0}') <= 2 " \
.format(name)
query += "ORDER BY created_on DESC LIMIT {} OFFSET {}".format(
page_size, page_size * page_index)
try:
with Cursor() as cur:
cur.execute(query)
if cur.rowcount:
return server.ok(data=cur.fetchall())
else:
return server.not_found('no users found')
except:
return server.error('unable to get users')
def get_notes(category_name):
""" Yields the nicknames of all the notes in the category
:param str category_name: The category name
"""
with Cursor() as cursor:
cursor.prepare("""
SELECT filtered_notes.nickname as nick FROM
(SELECT id, nickname FROM notes WHERE notes.id IN
(SELECT note FROM note_categories_assoc WHERE category=
(SELECT id FROM note_categories WHERE name=:name)
)
) filtered_notes
""")
cursor.bindValue(':name', category_name)
if cursor.exec_():
while cursor.next():
yield cursor.value("nick")
def set_hidden(category_names, hidden):
""" Change the hidden value of one or more categories
:param str category_name: The name of the category
:param bool hidden: The new state.
"""
with Cursor() as cursor:
cursor.prepare(
"UPDATE note_categories SET hidden=:hidden WHERE name=:name AND protected=FALSE"
)
cursor.bindValue(":name", category_names)
cursor.bindValue(":hidden", [
hidden,
] * len(category_names))
cursor.execBatch()
notes = api.notes.get(lambda x: any(category_name in x["categories"]
for category_name in category_names))
api.redis.send_message("enibar-notes",
[note['nickname'] for note in notes])
def generateReport():
"""
Generates a report of answers to the questions
in the questionsAndQueriesList
"""
for listItem in questionsAndQueriesList:
with Cursor() as cursor:
cursor.execute(listItem['query'].replace('\n', ' '))
results = cursor.fetchall()
field_names = [
str(i[0]).replace('_', ' ').upper() for i in cursor.description
]
print('### ' + listItem['question'], end='\n\n')
print('| ' + ' | '.join(field_names) + ' |')
print('| ' + ('--- | ' * len(field_names)))
for result in results:
print('| ' + ' | '.join(str(x) for x in result) + ' |')
print('\r\n', end='')
def get(**kwargs):
""" Get prices filtered by given values
:param \*\*kwargs: filters to apply
"""
global PRICE_FIELDS_CACHE
with Cursor() as cursor:
filters = []
for key in kwargs:
filters.append("prices.{key}=:{key}".format(key=key))
cursor.prepare("SELECT prices.id as id,\
prices.product as product,\
prices.value as value,\
price_description.label as label,\
price_description.category as category, \
categories.alcoholic AS alcoholic \
FROM prices JOIN price_description \
ON prices.price_description=price_description.id \
JOIN categories \
ON categories.id=price_description.category \
{} {}".format("WHERE" * bool(filters), " AND ".join(filters)))
for key, arg in kwargs.items():
cursor.bindValue(":{}".format(key), arg)
if cursor.exec_():
while cursor.next():
if PRICE_FIELDS_CACHE == {}:
PRICE_FIELDS_CACHE = {
f: cursor.indexOf(f)
for f in PRICE_FIELDS
}
line = {
field: cursor.value(PRICE_FIELDS_CACHE[field])
for field in PRICE_FIELDS
}
line[
'value'] += settings.ALCOHOL_MAJORATION * line['alcoholic']
yield line
def match_to_json(m, user=None):
"""
Right now this function is a performance clusterfuck. Almost all of the data in
here can be gathered with a single query and windowed multi-join, but lets wait
until shit breaks, eh?
"""
c = Cursor()
if not isinstance(m, tuple):
c.execute(MATCH_SELECT_SQL, (m, ))
m = c.fetchone()
if not m:
raise InvalidRequestError("Failed to match_to_json with arg %s" % m)
match = {}
match['id'] = m.id
match['game'] = m.game
match['when'] = int(m.match_date.strftime("%s"))
match['teams'] = []
match['extra'] = {}
match['stats'] = {}
# This will most definitily require some f*****g caching at some point
bet_stats = c.execute("""SELECT
sum(array_length(items, 1)) as skins_count,
count(*) as count,
sum(value) as value,
team
FROM bets WHERE match=%s AND state >= 'confirmed' GROUP BY team""", (m.id, )).fetchall(as_list=True)
match['stats']['players'] = sum(map(lambda i: i.count, bet_stats))
match['stats']['skins'] = sum(map(lambda i: i.skins_count, bet_stats))
match['stats']['value'] = sum(map(lambda i: i.value, bet_stats))
bet_stats = { i.team: i for i in bet_stats}
# Grab team information, including bets (this should be a join)
c.execute("SELECT * FROM teams WHERE id IN %s", (tuple(m.teams), ))
teams = c.fetchall()
total_bets = sum(map(lambda i: i.count, bet_stats.values())) * 1.0
total_value = sum(map(lambda i: i.value, bet_stats.values()))
for index, team in enumerate(teams):
team_data = {
"id": team.id,
"name": team.name,
"tag": team.tag,
"logo": team.logo,
"stats": {
"players": 0,
"skins": 0,
"value": 0,
},
"odds": 0
}
if index in bet_stats:
team_data['stats']['players'] = bet_stats[index].count
team_data['stats']['skins'] = bet_stats[index].skins_count
team_data['stats']['value'] = bet_stats[index].value
team_data['odds'] = float("{0:.2f}".format(bet_stats[index].count / total_bets))
match['teams'].append(team_data)
if user:
match['me'] = {}
mybet = c.execute("""
SELECT id, team, items::steam_item[], state, value
FROM bets WHERE match=%s AND better=%s
""", (m.id, user)).fetchone()
if mybet:
match['me']['id'] = mybet.id
match['me']['team'] = mybet.team
match['me']['state'] = mybet.state
match['me']['value'] = mybet.value
match['me']['state'] = mybet.state
if total_value > mybet.value:
my_return = ((total_value * 1.0) / match['teams'][mybet.team]['stats']['value']) * mybet.value
else:
my_return = mybet.value
match['me']['return'] = float("{0:.2f}".format(my_return))
# Load items in sub query :(
match['me']['items'] = []
items = c.execute("SELECT id, name, price, meta FROM items WHERE id IN %s", (tuple(
map(lambda i: i.item_id, mybet.items)), ))
for item in items.fetchall():
match['me']['items'].append({
"id": item.id,
"name": item.name,
"price": float(item.price),
"image": item.meta['image']
})
for key in ['league', 'type', 'event', 'streams', 'maps', 'note']:
if key in m.meta:
match['extra'][key] = m.meta[key]
match['results'] = m.results
return match
