Initial version of single-file ranking table generator

2 files changed, 227 insertions, 0 deletions

diff --git a/ranking.py b/ranking.py
new file mode 100644
index 0000000..dbcccf0
--- /dev/null
+++ b/ranking.py
@@ -0,0 +1,107 @@
+import copy, json, sys
+import mysql.connector
+from bs4 import BeautifulSoup as bs4
+
+settings = json.load(file('config.json'))
+
+connection = mysql.connector.connect(
+    user=settings['user'],
+    password=settings['pass'],
+    host=settings['host'],
+    port=settings['port'],
+    database=settings['db']
+)
+cursor = connection.cursor(dictionary=True, buffered=True)
+
+def fetch_ranking(date, assoc=False):
+    sql = '''SELECT
+    rankings.place,
+    rankings.pid,
+    CONCAT(players.name, " ", players.surname) player,
+    players.rank,
+    players.region, players.club,
+    REPLACE(players.flags, "K", "") age,
+    IF(players.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
+
+ranking = fetch_ranking(sys.argv[1])
+old_ranking = fetch_ranking(sys.argv[2], True) if len(sys.argv) > 2 else {}
+
+for row in ranking:
+    if row['pid'] in old_ranking:
+        row['place-change'] = old_ranking[row['pid']]['place'] - row['place']
+        row['place-change-class'] = 'success' if row['place-change'] > 0 else 'danger'
+        row['place-change'] = '%+d' % (row['place-change'])
+        if row['place-change'] == '+0':
+            row['place-change'] = '='
+            row['place-change-class'] = 'default'
+        for category in ['gender', 'age', 'region']:
+            if row[category] == old_ranking[row['pid']][category]:
+                row[category + '-change'] = old_ranking[row['pid']][category + '-place'] - row[category + '-place']
+                row[category + '-change-class'] = 'success' if row[category + '-change'] > 0 else 'danger'
+                row[category + '-change'] = '%+d' % (row[category + '-change'])
+                if row[category + '-change'] == '+0':
+                    row[category + '-change'] = '='
+                    row[category + '-change-class'] = 'default'
+
+table = bs4(file('templates/ranking.html'), 'lxml')
+table_body = table.select('tbody')[0]
+table_row = table_body.select('tr')[0].extract()
+
+table.select('.page-header h1 small')[0].string = 'z dnia %s' % (
+    '.'.join(sys.argv[1].split('-')[::-1])
+)
+
+for row in ranking:
+    new_row = copy.copy(table_row)
+    new_row.select('td.pid')[0].string = str(row['pid'])
+    new_row.select('td.pidlink a')[0]['href'] = 'https://msc.com.pl/cezar/?p=21&pid=%d' % (row['pid'])
+    new_row.select('td.name')[0].string = row['player']
+    new_row.select('td.club')[0].string = row['club']
+    for category in ['gender', 'age', 'region']:
+        new_row.select('td.' + category)[0].string = row[category]
+        new_row.select('td.' + category + '-place .rank')[0].string = str(row[category + '-place'])
+        badge = new_row.select('td.' + category + '-place .change')[0]
+        badge.string = row[category + '-change']
+        badge['class'] = badge['class'] + ['label-' + row[category + '-change-class']]
+    new_row.select('td.ranking')[0].string = str(row['score'])
+    new_row.select('td.place .rank')[0].string = str(row['place'])
+    badge = new_row.select('td.place .change')[0]
+    badge.string = row['place-change']
+    badge['class'] = badge['class'] + ['label-' + row['place-change-class']]
+    for category in ['gender', 'age', 'region']:
+        if row[category + '-place'] == 1:
+            new_row['class'] = new_row.get('class', []) + ['info']
+    table_body.append(new_row)
+
+print table.prettify().encode('utf-8')
diff --git a/templates/ranking.html b/templates/ranking.html
new file mode 100644
index 0000000..b7013f5
--- /dev/null
+++ b/templates/ranking.html
@@ -0,0 +1,120 @@
+<html>
+  <head>
+    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
+    <title>Ranking PZBS</title>
+    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous" />
+    <link rel="stylesheet" href="res/ranking.css" />
+  </head>
+  <body>
+    <div class="container">
+      <div class="page-header">
+        <h1>
+          Ranking PZBS
+          <small></small>
+        </h1>
+      </div>
+      <div class="panel panel-default filters">
+        <div class="panel-heading">
+          Filtry rankingu
+        </div>
+        <div class="panel-body">
+          <p>
+            <div class="btn-group" role="group">
+              <button type="button" class="btn btn-disabled">Okręg</button>
+              <button type="button" class="btn btn-default" data-filter="region" data-value="DS">DS</button>
+              <button type="button" class="btn btn-default" data-filter="region" data-value="KP">KP</button>
+              <button type="button" class="btn btn-default" data-filter="region" data-value="LB">LB</button>
+              <button type="button" class="btn btn-default" data-filter="region" data-value="LD">LD</button>
+              <button type="button" class="btn btn-default" data-filter="region" data-value="LU">LU</button>
+              <button type="button" class="btn btn-default" data-filter="region" data-value="MA">MA</button>
+              <button type="button" class="btn btn-default" data-filter="region" data-value="MP">MP</button>
+              <button type="button" class="btn btn-default" data-filter="region" data-value="OP">OP</button>
+              <button type="button" class="btn btn-default" data-filter="region" data-value="PD">PD</button>
+              <button type="button" class="btn btn-default" data-filter="region" data-value="PK">PK</button>
+              <button type="button" class="btn btn-default" data-filter="region" data-value="PM">PM</button>
+              <button type="button" class="btn btn-default" data-filter="region" data-value="SL">SL</button>
+              <button type="button" class="btn btn-default" data-filter="region" data-value="SW">SW</button>
+              <button type="button" class="btn btn-default" data-filter="region" data-value="WM">WM</button>
+              <button type="button" class="btn btn-default" data-filter="region" data-value="WP">WP</button>
+              <button type="button" class="btn btn-default" data-filter="region" data-value="ZP">ZP</button>
+              <button type="button" class="btn btn-default" data-clear="region">wszystkie</button>
+            </div>
+          </p>
+          <p>
+            <div class="btn-group" role="group">
+              <button type="button" class="btn btn-disabled">Płeć</button>
+              <button type="button" class="btn btn-default" data-filter="gender" data-value="K">K</button>
+              <button type="button" class="btn btn-default" data-filter="gender" data-value="M">M</button>
+              <button type="button" class="btn btn-default" data-clear="gender">obie</button>
+            </div>
+            <div class="btn-group" role="group">
+              <button type="button" class="btn btn-disabled">Kat. wiekowa</button>
+              <button type="button" class="btn btn-default" data-filter="age" data-value="M">M</button>
+              <button type="button" class="btn btn-default" data-filter="age" data-value="J">J</button>
+              <button type="button" class="btn btn-default" data-filter="age" data-value="">-</button>
+              <button type="button" class="btn btn-default" data-filter="age" data-value="S">S</button>
+              <button type="button" class="btn btn-default" data-filter="age" data-value="N">N</button>
+              <button type="button" class="btn btn-default" data-clear="age">wszyscy</button>
+            </div>
+          </p>
+        </div>
+      </div>
+      <table class="table table-striped table-hover table-condensed">
+        <thead>
+          <tr class="text-center">
+            <th colspan="2">Miejsce</th>
+            <th class="pid" colspan="2">PID</th>
+            <th>Zawodnik</th>
+            <th class="club">Klub</th>
+            <th class="region" colspan="3">Okręg</th>
+            <th class="gender" colspan="3">Płeć</th>
+            <th class="age" colspan="3">Kat. wiekowa</th>
+            <th class="ranking">Wynik</th>
+          </tr>
+        </thead>
+        <tbody>
+          <tr>
+            <td class="place text-right">
+              <span class="rank"></span>
+            </td>
+            <td class="place">
+              <span class="change label"></span>
+            </td>
+            <td class="pid text-right"></td>
+            <td class="pidlink">
+              <a href="" target="_blank">
+                <span class="glyphicon glyphicon-new-window"></span>
+              </a>
+            </td>
+            <td class="name"></td>
+            <td class="club"></td>
+            <td class="region text-center"></td>
+            <td class="region-place text-right">
+              <span class="rank"></span>
+            </td>
+            <td class="region-place">
+              <span class="change label"></span>
+            </td>
+            <td class="gender text-center"></td>
+            <td class="gender-place text-right">
+              <span class="rank"></span>
+            </td>
+            <td class="gender-place">
+              <span class="change label"></span>
+            </td>
+            <td class="age text-center"></td>
+            <td class="age-place text-right">
+              <span class="rank"></span>
+            </td>
+            <td class="age-place">
+              <span class="change label"></span>
+            </td>
+            <td class="ranking text-right"></td>
+          </tr>
+        </tbody>
+      </table>
+    </div>
+    <script type="text/javascript" src="https://code.jquery.com/jquery-2.2.4.min.js"></script>
+    <script type="text/javascript" src="res/ranking.js"></script>
+  </body>
+</html>