summaryrefslogtreecommitdiff
path: root/scripts/pyranking/fetch.py
blob: 304f5097fc8d45e45e488b1a432df9fcbe0774ca (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
from pyranking.db import cursor

def fetch_ranking(date, assoc=False):
    sql = '''SELECT
    rankings.place,
    rankings.pid,
    CONCAT(players.name, " ", players.surname) player,
    rankings.rank,
    rankings.region, rankings.club,
    REPLACE(rankings.flags, "K", "") age,
    IF(rankings.flags LIKE 'K%', "K", "") gender,
    rankings.score
FROM rankings
JOIN players
    ON players.id = rankings.pid
WHERE rankings.date = %(date)s
ORDER BY rankings.place
    '''
    cursor.execute(sql, {'date': date})
    ranks = {
        'gender': {},
        'age': {},
        'region': {}
    }
    ranking = cursor.fetchall()
    for row in ranking:
        if row['gender'] == '':
            row['gender'] = 'M'
        for category in ['gender', 'age', 'region']:
            if row[category] not in ranks[category]:
                ranks[category][row[category]] = 0
            ranks[category][row[category]] += 1
            row[category + '-place'] = ranks[category][row[category]]
        for category in ['place', 'gender', 'age', 'region']:
            row[category + '-change'] = 'N'
            row[category + '-change-class'] = 'info'
    if assoc:
        result = {}
        for row in ranking:
            result[row['pid']] = row
        return result
    return ranking