def avg_competitions():
sub_queries = [sub_query % ({"diff": i + 1}) for i in range(period)]
final_query = query % ({
"min_year": min_year,
"max_year": max_year,
"sub_queries": ",".join(sub_queries)
})
log.info("Connect to the database")
cnx = get_database_connection()
cursor = cnx.cursor(final_query)
cursor.execute(final_query)
table = cursor.fetchall()
log.info("Found %s countries" % len(table))
out = {}
out["title"] = title
out["groupName"] = "Countries"
out["displayMode"] = "DEFAULT"
headers = ["Avg", "Country", *range(min_year, max_year + 1)]
out["statistics"] = [{
"keys": [],
"content": table,
"headers": headers,
"showPositions": True,
"positionTieBreakerIndex": 0
}]
cnx.close()
return out
def best_podiums():
statistics = {}
statistics["title"] = title
statistics["groupName"] = "Results"
statistics["statistics"] = []
statistics["displayMode"] = "SELECTOR"
headers = [
"Competition", "Sum", "First", "Result", "Second", "Result", "Third",
"Result"
]
current_events = get_current_events()
cnx = get_database_connection()
cursor = cnx.cursor()
for current_event in current_events:
log.info(current_event.name)
# Not an sql replacement
cursor.execute(query % current_event.event_id)
lines = cursor.fetchall()
# Also skips 333mbf
if not lines:
continue
stat = []
for line in lines:
result = [
get_competition_html_link(line[0], line[1]),
str(int(line[2])) if current_event.event_id == '333mbf' else
time_format(line[2], current_event.event_id, "average")
]
# Each competitor
for i in range(podium_length):
result.append(
get_competitor_html_link(line[3 + 3 * i], line[4 + 3 * i]))
result.append(
time_format(line[5 + 3 * i], current_event.event_id,
"average"))
stat.append(result)
statistics["statistics"].append({
"keys": [current_event.name],
"content": stat,
"headers": headers,
"showPositions": True,
"positionTieBreakerIndex": 1
})
cnx.close()
return statistics
def get_current_events() -> List[Event]:
log.info("Get current events")
events = []
cnx = get_database_connection()
cursor = cnx.cursor()
cursor.execute(query)
for id, name in cursor:
event = Event(id, name)
events.append(event)
cnx.close()
log.info("Found %s events" % len(events))
return events
def avg_events():
cnx = get_database_connection()
cursor = cnx.cursor()
cursor.execute(query, {"min_year": min_year, "max_year": max_year})
content = cursor.fetchall()
out = {}
out["title"] = title
out["groupName"] = "Events"
out["displayMode"] = "DEFAULT"
headers = ["Avg", "Country"]
out["statistics"] = [{
"keys": [],
"content": content,
"headers": headers,
"showPositions": True,
"positionTieBreakerIndex": 0
}]
cnx.close()
return out
def sum_of_all_ranks():
cnx = get_database_connection()
cursor = cnx.cursor()
statistics = {}
statistics["title"] = title
statistics["groupName"] = "Competitors"
statistics["statistics"] = []
statistics["displayMode"] = "DEFAULT"
for result_type in ["Average", "Single"]:
events = []
cursor.execute(events_query % result_type) # Not an sql replacement
for event_id, event_name, max_rank in cursor:
event = Event(event_id, event_name, max_rank)
events.append(event)
log.info("Found %s events" % len(events))
event_ids = list(map(lambda e: e.event_id, events))
cursor.execute(competitors_query %
result_type) # Not an sql replacement
competitors = []
prev_event = None
event_index = None
log.info("Handle competitors ranks")
for (wca_id, world_rank, event_id, name) in cursor:
competitor = Competitor(wca_id, events)
index = bisect_left(competitors, competitor)
if index == len(competitors) or competitor != competitors[index]:
competitor.name = name
competitors.insert(index, competitor)
competitor = competitors[index]
if event_id != prev_event:
event_index = event_ids.index(event_id)
prev_event = event_id
competitor.ranks[event_index] = world_rank
log.info("Found %s competitors" % len(competitors))
log.info("Fill sum of ranks")
for competitor in competitors:
competitor.sum_of_ranks = sum(competitor.ranks)
log.info("Sort by sum of ranks")
competitors = sorted(competitors, key=lambda c: c.sum_of_ranks)
c = 0
prev = None
stat = []
for competitor in competitors:
s = competitor.sum_of_ranks
if c >= LIMIT and s != prev:
break
prev = s
# Mark some results as red
for i in range(len(events)):
if competitor.ranks[i] == events[i].max_rank:
competitor.ranks[
i] = "<span style=\"color:red\">%s</span>" % (
competitor.ranks[i])
stat.append([
get_competitor_html_link(competitor.wca_id, competitor.name),
s, *competitor.ranks
])
c += 1
custom_query = get_custom_query(result_type, events)
headers = ["Name", "Sum", *map(lambda c: c.name, events)]
statistics["statistics"].append({
"keys": [result_type],
"content": stat,
"headers": headers,
"showPositions": True,
"positionTieBreakerIndex": 1,
"sqlQueryCustom": custom_query
})
cnx.close()
return statistics
def compare_results(ev1, ev2):
"""Checks if a competitor got ev1 before ev2, expect for 333mbf and 333mbo, due to time encode."""
competitors = []
log.info("Connect to the database")
cnx = get_database_connection()
cursor = cnx.cursor()
cursor.execute(query % (ev1, ev2))
evs = [ev1, ev2]
log.info("Assign results")
for wca_id, name, country_id, event_id, best, competition_id, competition_name, date in cursor:
competitor = Competitor(wca_id, country_id, name)
i = bisect.bisect_left(competitors, competitor)
if i == len(competitors) or competitors[i] != competitor:
competitor.first_results = [None, None]
competitor.first_competitions = [None, None]
competitor.first_dates = [None, None]
competitor.diff = None
competitors.insert(i, competitor)
competitor = competitors[i]
j = evs.index(event_id)
if competitor.first_results[j] == None:
competitor.first_results[j] = time_format(best)
competitor.first_competitions[j] = [
competition_id, competition_name
]
competitor.first_dates[j] = date
log.info("Found %s competitors" % len(competitors))
competitors = list(
filter(lambda c: c.first_results[0] and c.first_results[1],
competitors))
log.info("Found %s competitors with both success" % len(competitors))
log.info("Fill diffs")
for competitor in competitors:
competitor.diff = (competitor.first_dates[1] -
competitor.first_dates[0]).days
# Filter
competitors = sorted(filter(lambda c: c.diff > 0, competitors),
key=lambda c: -c.diff)
log.info("Found %s competitors that got %s before %s" %
(len(competitors), ev1, ev2))
table = []
for competitor in competitors:
link = get_competitor_html_link(competitor.wca_id, competitor.name)
table.append([
competitor.diff, link, competitor.first_results[0],
get_competition_html_link(competitor.first_competitions[0][0],
competitor.first_competitions[0][1]),
competitor.first_results[1],
get_competition_html_link(competitor.first_competitions[1][0],
competitor.first_competitions[1][1])
])
headers = [
"Days", "Name",
"First result %s" % ev1, "Competition",
"First result %s" % ev2, "Competition"
]
out = {}
out["title"] = title
out["groupName"] = "Events"
out["displayMode"] = "DEFAULT"
out["explanation"] = "In case of multiple first results (eg. ao3), best one is taken."
out["statistics"] = [{
"keys": [],
"content": table,
"headers": headers,
"showPositions": True,
"positionTieBreakerIndex": 0
}]
cnx.close()
return out
def sub_x():
LIMIT = 10
log.info("Get database connection")
cnx = get_database_connection()
cursor = cnx.cursor()
statistics = {}
statistics["title"] = title
statistics["groupName"] = "Results"
statistics["statistics"] = []
headers = ["Count", "Name", "Country"]
statistics["displayMode"] = "GROUPED"
for current_event in get_current_events():
competitors = []
event = current_event.event_id
if event == "333mbf":
continue
log.info("Find sub x for %s" % current_event.name)
wr_single = find_wr_single(cursor, event)
wr_index = wr_single if event == "333fm" else wr_single // 100
log.info("WR single for %s is %s" %
(event, time_format(wr_single, event)))
log.info("Compute sub %s results" %
(normalize_result(wr_single, event)+RANGE))
cursor.execute(query_results % event)
for wca_id, person_name, country_id, best, v1, v2, v3, v4, v5 in cursor:
# We exclude people with DNF or results out of the range
if can_be_discarded(best, wr_index, event):
continue
competitor = Competitor(wca_id)
i = bisect_left(competitors, competitor)
if i == len(competitors) or competitors[i] != competitor:
competitor.name = person_name
competitor.country = country_id
competitors.insert(i, competitor)
competitor = competitors[i]
for x in [v1, v2, v3, v4, v5]:
if not can_be_discarded(x, wr_index, event):
index = normalize_result(x, event)-wr_index
competitor.count[index] += 1
log.info("%s elegible competitors" % len(competitors))
for i in range(RANGE):
sorted_i = sorted(
filter(lambda c: sum_to_index(c, i) > 0, competitors), key=lambda c: sum_to_index(c, i))[::-1]
c = 0
prev = None
stat = []
for x in sorted_i:
s = sum_to_index(x, i)
# Ties
if c >= LIMIT and s != prev:
break
stat.append([s, get_competitor_html_link(
x.wca_id, x.name), x.country])
prev = s
c += 1
index = i+wr_index+1
current_sub = index if event == "333fm" else index*100
statistics["statistics"].append(
{"keys": [current_event.name, "Sub %s" % time_format(current_sub, event)], "content": stat, "headers": headers, "showPositions": True, "positionTieBreakerIndex": 0, "sqlQueryCustom": query_custom % {"event_id": event, "sub_x": (1+i+wr_single//100)*100 if event != '333fm' else (1+i+wr_single)}})
cnx.close()
return statistics
def recent_success():
LIMIT = 10
MIN_SOLVES = 6
min_date = datetime.date.today() - relativedelta(years=1)
log.info("Min date: %s" % min_date)
out = {}
out["title"] = title
out["explanation"] = "Since %s, minimum %s successes" % (min_date,
MIN_SOLVES)
out["groupName"] = "Results"
out["displayMode"] = "SELECTOR"
headers = [
"Person", "Country", "Rate", "Success / Attempts", "Best", "Worst",
"Average"
]
out["statistics"] = []
log.info("Get database connection")
cnx = get_database_connection()
cursor = cnx.cursor()
for event in get_current_events():
log.info(event.name)
event_id = event.event_id
competitors = []
log.info("Find results")
cursor.execute(query, {"event_id": event_id, "min_date": min_date})
for wca_id, person_name, country_id, v1, v2, v3, v4, v5 in cursor:
competitor = Competitor(wca_id, person_name, country_id)
i = bisect.bisect_left(competitors, competitor)
if i == len(competitors) or competitors[i] != competitor:
competitors.insert(i, competitor)
competitor = competitors[i]
for x in [v1, v2, v3, v4, v5]:
if x == -1 or x > 0:
competitor.attempts += 1
if x > 0:
competitor.results.append(x)
log.info("Found %s competitors" % len(competitors))
log.info("Discard competitors with few results")
competitors = list(
filter(lambda c: len(c.results) >= MIN_SOLVES, competitors))
log.info("%s competitors left" % len(competitors))
log.info("Fill rates")
for competitor in competitors:
competitor.rate = float(
"%.2f" % (len(competitor.results) / competitor.attempts))
log.info("Sort competitors")
competitors = sorted(
competitors,
key=lambda c: [-c.rate, -len(c.results), -c.attempts, c.name])
table = []
count = 1
prev = None
for competitor in competitors:
rate = competitor.rate
if (count > LIMIT and prev != rate
) or count > 2 * LIMIT: # We interrupt the list anyways
break
link = get_competitor_html_link(competitor.wca_id, competitor.name)
table.append([
link, competitor.country,
"%.2f" % rate,
"%s / %s" % (len(competitor.results), competitor.attempts),
time_format(min(competitor.results), event.event_id),
time_format(max(competitor.results), event.event_id),
time_format(
0 if event_id == '333mbf' else sum(competitor.results) /
len(competitor.results), event.event_id, "average")
])
count += 1
prev = rate
out["statistics"].append({
"keys": [event.name],
"content": table,
"headers": headers,
"showPositions": True,
"positionTieBreakerIndex": 2,
"sqlQueryCustom": custom_query % {
"event_id": event_id,
"min_date": min_date
}
})
cnx.close()
return out
def compare_results(ev1, ev2):
"""Checks if ev1 happened before ev2."""
# This won't work for 333mbf and 333mbo, since `best` is encoded differently.
competitors = []
log.info("Get connection")
cnx = get_database_connection()
cursor = cnx.cursor()
cursor.execute(query % (ev1, ev2))
for wca_id, country_id, name, best, start_date, competition_id, competition_name, event_id in cursor:
competitor = Competitor(wca_id, country_id, name)
i = bisect.bisect_left(competitors, competitor)
if i == len(competitors) or competitors[i] != competitor:
competitors.insert(i, competitor)
competitor = competitors[i]
# first ev1 result ever
if event_id == ev1 and not competitor.first_results[0]:
competitor.first_results[0] = best
competitor.first_competitions[0] = [
competition_id, competition_name
]
competitor.first_dates[0] = start_date
elif event_id == ev2 and not competitor.first_results[
1] and competitor.first_results[
0] and best < competitor.first_results[0]:
competitor.first_results[1] = best
competitor.first_competitions[1] = [
competition_id, competition_name
]
competitor.first_dates[1] = start_date
competitor.diff = (competitor.first_dates[1] -
competitor.first_dates[0]).days
table = []
sorted_competitors = sorted(filter(lambda c: c.diff and c.diff > 0,
competitors),
key=lambda c: c.diff)
for competitor in sorted_competitors:
table.append([
competitor.diff,
get_competitor_html_link(competitor.wca_id, competitor.name),
time_format(competitor.first_results[0]),
get_competition_html_link(competitor.first_competitions[0][0],
competitor.first_competitions[0][1]),
time_format(competitor.first_results[1]),
get_competition_html_link(competitor.first_competitions[1][0],
competitor.first_competitions[1][1])
])
out = {}
out["explanation"] = "In case of multiple first results (eg. ao3), best one is taken."
out["title"] = title
out["displayMode"] = "DEFAULT"
out["groupName"] = "Competitors"
headers = [
"Days", "Name",
"First %s result" % ev1, "Competition",
"First faster %s result" % ev2, "Competition"
]
out["statistics"] = [{
"keys": [],
"content": table,
"headers": headers,
"showPositions": True,
"positionTieBreakerIndex": 0
}]
return out
def ranges():
LIMIT = 10
out = {}
out["title"] = title
out["groupName"] = "Results"
out["displayMode"] = "SELECTOR"
headers = ["Person", "Range Size", "Country", "Range Start", "Range End"]
out["statistics"] = []
current_events = get_current_events()
cnx = get_database_connection()
cursor = cnx.cursor()
log.info("Read results")
for current_event in current_events:
competitors = []
event = current_event.event_id
log.info("Event = %s" % current_event.name)
cursor.execute(query % current_event.event_id)
for wca_id, person_name, country_id, v1, v2, v3, v4, v5 in cursor:
competitor = Competitor(wca_id, person_name, country_id)
i = bisect.bisect_left(competitors, competitor)
if i == len(competitors) or competitors[i] != competitor:
competitors.insert(i, competitor)
competitor = competitors[i]
for x in [v1, v2, v3, v4, v5]:
if x < 1:
continue
if event == "333mbf":
x = str(x)
points = get_mbld_points(x)[0]
j = bisect.bisect_left(competitor.results, points)
if j == len(competitor.results
) or competitor.results[j] != points:
competitor.results.insert(j, points)
else:
j = bisect.bisect_left(competitor.results, x)
if j == len(
competitor.results) or competitor.results[j] != x:
competitor.results.insert(j, x)
log.info("Found %s competitors" % len(competitors))
log.info("Organize ranges")
for competitor in competitors:
# skipping people with only 1 result
range_size, range_start, range_end = largest_range(
competitor.results)
if range_size == 1:
continue
competitor.range = range_size
competitor.range_start = range_start
competitor.range_end = range_end
log.info("Sort results")
competitors = sorted(filter(lambda c: c.range, competitors),
key=lambda c: -c.range)
log.info("Found %s elegible competitors" % len(competitors))
table = []
log.info("Compute table")
prev = None
count = 0
for competitor in competitors:
count += 1
if count > LIMIT and prev != competitor.range:
break
if event in ["333fm", "333mbf"]:
range_start = competitor.range_start
range_end = competitor.range_end
else:
range_start = time_format(competitor.range_start)
range_end = time_format(competitor.range_end)
link = get_competitor_html_link(competitor.wca_id, competitor.name)
table.append([
link, competitor.range, competitor.country, range_start,
range_end
])
prev = competitor.range
explanation = "Competitors that got all results from the range start to the range end in %s" % (
"steps of 1" if event in ("333fm", "333mbf") else "steps of 0.01")
out["statistics"].append({
"keys": [current_event.name],
"content": table,
"headers": headers,
"explanation": explanation,
"showPositions": True,
"positionTieBreakerIndex": 1
})
cnx.close()
return out
def longest_streaks():
LIMIT = 50
out = {}
out["title"] = title
out["groupName"] = "Results"
out["displayMode"] = "SELECTOR"
headers = ["Streak", "Person", "Country", "Streak Start", "Streak End"]
out["statistics"] = []
log.info("Get database connection")
cnx = get_database_connection()
cursor = cnx.cursor()
for event in get_current_events():
log.info(event.name)
event_id = event.event_id
competitors = []
cursor.execute(query, {"event_id": event_id})
for wca_id, person_name, country_id, competition_id, v1, v2, v3, v4, v5 in cursor:
competitor = Competitor(wca_id, person_name, country_id)
i = bisect.bisect_left(competitors, competitor)
if i == len(competitors) or competitors[i] != competitor:
competitors.insert(i, competitor)
competitor = competitors[i]
for x in [v1, v2, v3, v4, v5]:
if x == -1:
competitor.count = 0
elif x > 0:
competitor.count += 1
if competitor.count >= competitor.max:
competitor.max = competitor.count
competitor.max_range_start = competitor.current_range_start
competitor.max_range_end = competition_id
if competitor.count == 1:
competitor.current_range_start = competition_id
competitors = sorted(competitors, key=lambda c: -c.max)
table = []
count = 1
prev = None
for competitor in competitors:
streak = competitor.max
if count > LIMIT and prev != streak:
break
current = competitor.count
link = get_competitor_html_link(competitor.wca_id, competitor.name)
table.append([
streak, link, competitor.country,
get_competition_html_link(competitor.max_range_start),
"-" if streak == current else get_competition_html_link(
competitor.max_range_end)
])
count += 1
prev = streak
out["statistics"].append({
"keys": [event.name],
"content": table,
"headers": headers,
"showPositions": True,
"positionTieBreakerIndex": 0
})
cnx.close()
return out