324 lines
13 KiB
Python
324 lines
13 KiB
Python
from flask import Flask, abort, request, render_template, url_for
|
|
import datetime as dt
|
|
import math
|
|
import MySQLdb
|
|
import MySQLdb.cursors
|
|
import re
|
|
import urllib.parse
|
|
app = Flask(__name__)
|
|
|
|
PAGE_SIZE=20
|
|
MIN_MONTHS_FOR_LISTINGS=3
|
|
|
|
@app.template_filter('urlescape')
|
|
def urlescape(string):
|
|
if string is None:
|
|
return ''
|
|
return urllib.parse.quote_plus(string)
|
|
|
|
@app.template_filter('pace')
|
|
def pace(time):
|
|
return (dt.datetime(1,1,1) + time).strftime('%M:%S')
|
|
|
|
@app.template_filter('gender')
|
|
def gender(sex):
|
|
if sex == 'f':
|
|
return 'lady'
|
|
elif sex == 'm':
|
|
return 'man'
|
|
return
|
|
|
|
@app.template_filter('year')
|
|
def year(time):
|
|
return time.strftime('%Y')
|
|
|
|
@app.template_filter('cleandate')
|
|
def cleandate(time):
|
|
if time.month == 1 and time.day == 1:
|
|
return time.strftime('%Y')
|
|
return time.strftime('%Y-%m-%d')
|
|
|
|
@app.template_filter('ordinal')
|
|
def ordinal(n):
|
|
if not isinstance(n, int) or n < 1:
|
|
return
|
|
return "%d%s" % (n,"tsnrhtdd"[(math.floor(n/10)%10!=1)*(n%10<4)*n%10::4])
|
|
|
|
@app.template_filter('cleandict')
|
|
def cleandict(dict):
|
|
''' Prevent duplication of existing query strings when calling url_for(..., **request.args) '''
|
|
newdict = {}
|
|
for key, value in dict.items():
|
|
if key not in ( 'title', 'year', 'start', 'show' ) and value not in ( None, '', ):
|
|
newdict[key] = value
|
|
return newdict
|
|
|
|
def now():
|
|
return dt.datetime.now()
|
|
|
|
def getstart():
|
|
start = request.args.get('start', '0')
|
|
if not isinstance(start, (int, float)):
|
|
try:
|
|
return int(start)
|
|
except:
|
|
return 0
|
|
return start
|
|
|
|
def getshow():
|
|
show = request.args.get('show', PAGE_SIZE)
|
|
if show == 'all':
|
|
return -1
|
|
if not isinstance(show, (int, float)):
|
|
try:
|
|
return int(show)
|
|
except:
|
|
return PAGE_SIZE
|
|
return show
|
|
|
|
|
|
def read_db(listing=None, event=None, person=None, licence=None, search=dict(), year=None):
|
|
db = MySQLdb.connect(user='aac', passwd='saOAcCWHg4LaoSSA', db='AAC',
|
|
use_unicode=True, charset="utf8", cursorclass=MySQLdb.cursors.DictCursor)
|
|
c = db.cursor()
|
|
count = 0
|
|
start = getstart()
|
|
show = getshow()
|
|
|
|
select = '*'
|
|
close = ''
|
|
where = 'WHERE club LIKE "AAC"'
|
|
group = ''
|
|
order = 'date DESC, event, distance DESC, position'
|
|
limit = 'LIMIT {},{}'.format(start, show)
|
|
if show == -1:
|
|
limit = ''
|
|
|
|
''' Build standard query (list of results) '''
|
|
if event:
|
|
if isinstance(event, str):
|
|
event = db.escape_string(event).decode()
|
|
where += ' AND event LIKE "{}"'.format(event.lower())
|
|
if person:
|
|
if isinstance(person, str):
|
|
person = db.escape_string(person).decode()
|
|
where += ' AND CONCAT_WS(" ", name, surname) LIKE "{}"'.format(person.lower())
|
|
if licence:
|
|
if isinstance(licence, str):
|
|
licence = db.escape_string(licence).decode()
|
|
where += ' AND licence LIKE "{}"'.format(licence.lower())
|
|
|
|
''' Build search query '''
|
|
for column in search.keys():
|
|
if isinstance(column, str):
|
|
column = db.escape_string(column).decode()
|
|
if isinstance(search[column], str):
|
|
query = db.escape_string(search[column]).decode()
|
|
if not query:
|
|
next
|
|
if column in ( 'licence', 'event', ):
|
|
where += ' AND {} LIKE "%{}%"'.format(column, query.lower())
|
|
elif column in ( 'name', ):
|
|
where += ' AND ( CONCAT_WS(" ", name, surname) LIKE "%{}%" )'.format(query.lower())
|
|
elif column in ( 'position', ):
|
|
where += ' AND {} LIKE "{}"'.format(column, query)
|
|
elif column == 'after':
|
|
try:
|
|
after = dt.datetime.strptime(query, '%Y-%m-%d')
|
|
date = dt.datetime.min
|
|
date = date.replace(year=after.year, month=after.month, day=after.day)
|
|
where += ' AND date > "{}"'.format(date)
|
|
except:
|
|
pass
|
|
elif column == 'before':
|
|
try:
|
|
before = dt.datetime.strptime(query, '%Y-%m-%d')
|
|
date = dt.datetime.max
|
|
date = date.replace(year=before.year, month=before.month, day=before.day)
|
|
where += ' AND date < "{}"'.format(date)
|
|
except:
|
|
pass
|
|
else:
|
|
pass
|
|
|
|
''' Build list query (list of races, rankings, licences, or runners by distance) '''
|
|
if listing:
|
|
where += ' AND CONCAT_WS(" ", name, surname) NOT LIKE "%no%return%"'
|
|
where += ' AND CONCAT_WS(" ", name, surname) NOT LIKE "%no%card%"'
|
|
where += ' AND CONCAT_WS(" ", name, surname) NOT LIKE "%blank%card%"'
|
|
where += ' AND CONCAT_WS(" ", name, surname) NOT LIKE "%disqualified%"'
|
|
if listing == 'races':
|
|
select = 'TRIM(event) AS event, date'
|
|
group = 'GROUP BY event, date'
|
|
order = 'date DESC, TRIM(event)'
|
|
elif listing == 'runners':
|
|
select = 'TRIM(CONCAT_WS(" ", name, surname)) AS person, FORMAT(SUM(distance),0) AS total'
|
|
group = 'GROUP BY TRIM(CONCAT_WS(" ", name, surname))'
|
|
order = 'SUM(distance) DESC, TRIM(CONCAT_WS(" ", name, surname))'
|
|
elif listing == 'top':
|
|
select = '*'
|
|
group = ''
|
|
where += ' AND (position < 20 OR (position/finishers*1000) < 20 OR catposition < 10 OR (catposition/catfinishers*1000) < 10 OR sexposition < 10 OR (sexposition/sexfinishers*1000) < 10)'
|
|
order = 'date DESC, event'
|
|
elif listing == 'rankings':
|
|
# SELECT query.person, query.positions, query.races, query.podiums, query.score, sex.positions AS sexpositions, sex.races AS sexraces, cat.positions AS catpositions, cat.races catraces FROM (SELECT *, CONCAT_WS(" ", name, surname) person, SUM(position) positions, COUNT(event) races, SUM(position)/COUNT(event) podiums, FORMAT(SUM(position)/COUNT(event),1) score FROM `results` WHERE club LIKE "AAC" GROUP BY CONCAT_WS(" ", name, surname) ) AS query INNER JOIN (SELECT *, CONCAT_WS(" ", name, surname) person, SUM(sexposition) as positions, COUNT(event) races FROM `results` WHERE club LIKE "AAC" AND sexposition > 0 GROUP BY CONCAT_WS(" ", name, surname) ) sex ON query.person=sex.person INNER JOIN (SELECT *, CONCAT_WS(" ", name, surname) person, SUM(catposition) as positions, COUNT(event) races FROM `results` WHERE club LIKE "AAC" AND catposition > 0 GROUP BY CONCAT_WS(" ", name, surname) ) cat ON query.person=cat.person WHERE query.person NOT LIKE "%no return%" AND query.person NOT LIKE "%no card%" AND query.person NOT LIKE "%blank card%" AND query.person NOT LIKE "%disqualified%" GROUP BY query.person ORDER BY podiums, races DESC;
|
|
select = 'TRIM(CONCAT_WS(" ", name, surname)) AS person, SUM(position) AS positions, COUNT(event) AS races, SUM(position)/COUNT(event) AS podiums, FORMAT(SUM(position)/COUNT(event), 1) AS score'
|
|
group = 'GROUP BY TRIM(CONCAT_WS(" ", name, surname))'
|
|
order = 'podiums, races DESC'
|
|
elif listing == 'licence':
|
|
select = 'licence, date, TRIM(CONCAT_WS(" ", name, surname)) AS person'
|
|
group = 'GROUP BY licence, name, surname'
|
|
order = 'TRIM(CONCAT_WS(" ", name, surname)) ASC'
|
|
''' Add elements common to multiple types of queries '''
|
|
if year:
|
|
if not isinstance(year, (int, float)):
|
|
year = int(db.escape_string(year).decode())
|
|
firstdate = dt.datetime.min
|
|
lastdate = dt.datetime.max
|
|
firstdate = firstdate.replace(year=int(year))
|
|
lastdate = lastdate.replace(year=int(year))
|
|
where += ' AND date > "{}" AND date < "{}"'.format(firstdate, lastdate)
|
|
''' This statement is expensive but doesn't increase the count, so don't change the count statement '''
|
|
#if finishers:
|
|
# select = 'total.finishers, query.* FROM( SELECT *'
|
|
# close = ') AS query INNER JOIN (SELECT event, date, distance, COUNT(event) as finishers FROM `results` GROUP BY event, distance, date) AS total ON total.event=query.event AND total.date=query.date AND total.distance=query.distance'
|
|
|
|
|
|
sql = 'SELECT {} FROM `results` {} {} ORDER BY {} {} {};'.format(select, where, group, order, limit, close)
|
|
app.logger.debug(sql)
|
|
c.execute(sql)
|
|
queryresults = c.fetchall()
|
|
|
|
select = 'COUNT(*)'
|
|
close = ''
|
|
if listing:
|
|
if listing == 'races':
|
|
select = 'COUNT(*) FROM ( SELECT COUNT(event)'
|
|
close = ') AS races'
|
|
elif listing == 'runners':
|
|
select = 'COUNT(*) FROM ( SELECT COUNT(name)'
|
|
close = ') AS runners'
|
|
elif listing == 'top':
|
|
pass
|
|
elif listing == 'rankings':
|
|
select = 'COUNT(*) FROM ( SELECT COUNT(name)'
|
|
close = ') AS rankings'
|
|
elif listing == 'licence':
|
|
select = 'COUNT(*) FROM ( SELECT COUNT(licence)'
|
|
close = ') AS licence'
|
|
|
|
sql = 'SELECT {} FROM `results` {} {} {};'.format(select, where, group, close)
|
|
app.logger.debug(sql)
|
|
c.execute(sql)
|
|
countresult = c.fetchone()
|
|
for x in countresult.keys():
|
|
count = countresult[x]
|
|
app.logger.debug(count)
|
|
|
|
return { 'count': int(count), 'rows': queryresults }
|
|
|
|
|
|
|
|
@app.route('/')
|
|
@app.route('/<title>')
|
|
@app.route('/<title>/<int:year>')
|
|
def list(title=None, year=None):
|
|
''' Set defaults for the index page '''
|
|
if year is None and title is None:
|
|
#year = now().year
|
|
title = 'top'
|
|
title = urllib.parse.unquote_plus(title)
|
|
if title not in ( 'races', 'top', 'rankings', 'runners', 'licence', ):
|
|
abort(404)
|
|
|
|
''' In early January, we'll be left with blank pages in listings, since there won't
|
|
be any results yet, so hack it to return last year's info '''
|
|
date = dt.datetime.now()
|
|
if date.year == year and date.month <= MIN_MONTHS_FOR_LISTINGS:
|
|
results = read_db(year=date.year)
|
|
app.logger.debug(results['count'])
|
|
if results['count'] < 1:
|
|
''' get an approximate time about two months ago (at the end of the previous year) '''
|
|
lastyear = date - dt.timedelta(days=(MIN_MONTHS_FOR_LISTINGS * 31))
|
|
year = lastyear.year
|
|
|
|
results = read_db(listing=title, year=year)
|
|
return render_template('list-'+title+'.html', ltype='listing', title=title,
|
|
results=results, year=year,
|
|
request=request, getstart=getstart(), getshow=getshow(), now=now(), PAGE_SIZE=PAGE_SIZE)
|
|
|
|
@app.route('/all')
|
|
@app.route('/all/<int:year>')
|
|
def index(title=None, year=None):
|
|
if title is not None:
|
|
title = urllib.parse.unquote_plus(title)
|
|
results = read_db(year=year)
|
|
return render_template('index.html', ltype='index', title=title,
|
|
results=results, year=year,
|
|
request=request, getstart=getstart(), getshow=getshow(), now=now(), PAGE_SIZE=PAGE_SIZE)
|
|
|
|
@app.route('/races/<int:year>/<title>')
|
|
def races(year=None, title=None):
|
|
if title is not None:
|
|
title = urllib.parse.unquote_plus(title)
|
|
results = read_db(event=title, year=year)
|
|
return render_template('index.html', ltype='races', title=title,
|
|
results=results, year=year,
|
|
request=request, getstart=getstart(), getshow=getshow(), now=now(), PAGE_SIZE=PAGE_SIZE)
|
|
|
|
@app.route('/person/<title>')
|
|
@app.route('/person/<title>/<int:year>')
|
|
def person(title=None, year=None):
|
|
if title is not None:
|
|
title = urllib.parse.unquote_plus(title)
|
|
results = read_db(person=title, year=year)
|
|
return render_template('index.html', ltype='person', title=title,
|
|
results=results, year=year,
|
|
request=request, getstart=getstart(), getshow=getshow(), now=now(), PAGE_SIZE=PAGE_SIZE)
|
|
|
|
@app.route('/licence/<int:year>/<title>')
|
|
def licence(year=now().year, title=None):
|
|
if title is not None:
|
|
title = urllib.parse.unquote_plus(title)
|
|
results = read_db(licence=title, year=year)
|
|
return render_template('index.html', ltype='licence', title=title,
|
|
results=results, year=year,
|
|
request=request, getstart=getstart(), getshow=getshow(), now=now(), PAGE_SIZE=PAGE_SIZE)
|
|
|
|
@app.route('/search')
|
|
def search():
|
|
results = read_db(search=request.args)
|
|
return render_template('index.html', ltype='search', title=None,
|
|
results=results, year=None,
|
|
request=request, getstart=getstart(), getshow=getshow(), now=now(), PAGE_SIZE=PAGE_SIZE)
|
|
|
|
|
|
if __name__ == '__main__':
|
|
app.run(debug=True)
|
|
|
|
|
|
# most race KMs in the year, by distance
|
|
# fastest race pace over the year (time / KMs)
|
|
# individual KMs, and race results (race, position, time) by name
|
|
# - and by race number and year
|
|
# by race
|
|
# - and by gender
|
|
|
|
# tabs:
|
|
# list of races (sorted by recent)
|
|
# list of people (sorted by total kms for the year)
|
|
# list of licences for the year (sorted by number of races)
|
|
# list of podiums/rankings (people sorted by total_position/total_races)
|
|
# click to expand by
|
|
# person (races by recent)
|
|
# person (races by pace)]
|
|
# race (all AAC members by position)
|
|
# SEARCH
|
|
|
|
# /?sort={distance,pace}&sex={m,f}
|
|
# /race/2018/HEWAT ETC?sort={position,pace}&sex={m,f}
|
|
# /person/Timothy Allen?sort={pace,date}
|
|
# /person/Timothy Allen/2018
|
|
# /license/2018/4356
|
|
# /license/2018/4356
|