def handle(self, *args, **options):
"""
delete sessions that expired at least one day ago
For performance issues, directly executes the delete operation on database,
instead of using the django's builtin clearsessions command that uses the sessions models
"""
sql = 'DELETE FROM `django_session` WHERE `expire_date` < DATE_SUB( CURDATE( ) , INTERVAL 0 DAY );'
executeRawSqlQuery(sql)
def issue_stats(issue,minmax):
"""
to get min/max numbers of fixtures issues: win, draw, loose
for the entire season
issue: win | draw | lose
minmax: min | max
"""
request = """
SELECT * from (
SELECT season_team.name AS team,
count(%(issue)s) AS %(issue)s
FROM season_team, season_table
WHERE season_team.id = season_table.team_id
AND %(issue)s > 0
AND week > 0
GROUP BY season_team.name
)innertable
WHERE %(issue)s = (
SELECT %(minmax)s(%(issue)s)
FROM (
SELECT season_team.name AS team,
count(%(issue)s) AS %(issue)s
FROM season_team, season_table
WHERE season_team.id = season_table.team_id
AND %(issue)s > 0
AND week > 0
GROUP BY season_team.name
)innertable
)
""" % ({'issue': issue, 'minmax': minmax})
return executeRawSqlQuery(request)
def goal_stats(goalmode,minmax):
"""
to get either best attack or best defense
or poorest attack or poorest defense
goalmode: for | against
minmax: min | max
"""
request = """
SELECT * from (
SELECT season_team.name AS team,
sum(goal_%(goalmode)s) AS goal_%(goalmode)s
FROM season_team, season_table
WHERE season_team.id = season_table.team_id
AND week > 0
GROUP BY season_team.name
)innertable
WHERE goal_%(goalmode)s = (
SELECT %(minmax)s(goal_%(goalmode)s)
FROM (
SELECT season_team.name AS team,
sum(goal_%(goalmode)s) AS goal_%(goalmode)s
FROM season_team, season_table
WHERE season_team.id = season_table.team_id
AND week > 0
GROUP BY season_team.name
)innertable
)
""" % ({'goalmode':goalmode,'minmax':minmax})
return executeRawSqlQuery(request)
def count_forecasts_draw(fixture_id):
request = """
SELECT COUNT(*)
FROM season_forecast, season_fixture
WHERE season_forecast.fixture_id = season_fixture.id
AND season_fixture.id = '%s'
AND season_forecast.score_a = season_forecast.score_b
""" % (fixture_id)
return executeRawSqlQuery(request)
def user_total_until(user, week):
request = """
SELECT sum(points) as total
FROM season_forecast
INNER JOIN season_fixture
ON season_forecast.fixture_id = season_fixture.id
WHERE season_forecast.user_id = '%s' AND week <= '%s'
""" % (user, week)
return executeRawSqlQuery(request)
def goals_taken_away(team_id):
"""
returns the number of goals taken by a team away
"""
request = """
SELECT SUM(score_a)
FROM season_fixture
WHERE team_b_id = '%s'
""" % (team_id)
return executeRawSqlQuery(request)
def goals_scored_home(team_id):
"""
returns the number of goals scored by a team at home
"""
request = """
SELECT SUM(score_a)
FROM season_fixture
WHERE team_a_id = '%s'
""" % (team_id)
return executeRawSqlQuery(request)
def team_win_draw_lose(team_id):
"""
return the number of win, draw, lose
for the given team for the entire season
"""
request = """
SELECT sum(win), sum(draw), sum(lose)
FROM season_table
WHERE team_id = '%s'
""" % (team_id)
return executeRawSqlQuery(request)
def team_points(team_id):
"""
returns the number of points of a team
"""
request = """
SELECT SUM(points)
FROM season_table
WHERE team_id = '%s'
""" % team_id
return executeRawSqlQuery(request)
def count_team_forecasts_lose(team_id):
request = """
SELECT COUNT(*)
FROM season_forecast, season_fixture
WHERE season_forecast.fixture_id = season_fixture.id
AND (
(season_fixture.team_a_id = '%(team_id)s' AND season_forecast.score_a < season_forecast.score_b)
OR (season_fixture.team_b_id = '%(team_id)s' AND season_forecast.score_a > season_forecast.score_b)
)
""" % ({'team_id':team_id})
return executeRawSqlQuery(request)
def team_win_draw_lose_goals(team_id):
"""
return the number of win, draw, lose, goals scored, goals against
for the given team for the entire season
"""
request = """
SELECT sum(win), sum(draw), sum(lose), sum(goal_for), sum(goal_against)
FROM season_table
WHERE team_id = '%s'
""" % (team_id)
return executeRawSqlQuery(request)
def total_goals_scored():
"""
returns the number of goals scored during the season
"""
request = """
SELECT SUM(score_a) + SUM(score_b)
FROM season_fixture
WHERE score_a IS NOT NULL
AND score_b IS NOT NULL
"""
return executeRawSqlQuery(request)
def avg_goals_scored():
"""
returns the average goals scored by match for the season
NB: returns floating number in MYSQL, not POSTGRESQL
"""
request = """
SELECT (SUM(score_a) + SUM(score_b)) / COUNT(id)
FROM season_fixture
WHERE score_a IS NOT NULL
AND score_b IS NOT NULL
"""
return executeRawSqlQuery(request)
def matches_played(team_id):
"""
returns the number of matches played by a team
"""
request = """
SELECT COUNT(id)
FROM season_fixture
WHERE (team_a_id = '%(team_id)s'
OR team_b_id = '%(team_id)s')
AND (score_a IS NOT NULL
AND score_b IS NOT NULL)
""" % ({'team_id':team_id})
return executeRawSqlQuery(request)
def count_team_forecasts_draw(team_id):
"""
count the number of draw forecasts for a team
"""
request = """
SELECT COUNT(*)
FROM season_forecast, season_fixture
WHERE season_forecast.fixture_id = season_fixture.id
AND (
(season_fixture.team_a_id = '%(team_id)s' AND season_forecast.score_a = season_forecast.score_b)
OR (season_fixture.team_b_id = '%(team_id)s' AND season_forecast.score_a = season_forecast.score_b)
)
""" % ({'team_id':team_id})
return executeRawSqlQuery(request)
def count_team_forecasts(team_id):
"""
count the number of forecasts for a team
"""
request = """
SELECT COUNT(*)
FROM season_forecast, season_fixture
WHERE season_forecast.fixture_id = season_fixture.id
AND (
season_fixture.team_a_id = '%(team_id)s'
OR season_fixture.team_b_id = '%(team_id)s'
)
""" % ({'team_id':team_id})
return executeRawSqlQuery(request)
def goals_scored(minmax):
"""
returns the number (most or less)
of goals scored during one week
@params minmax: min|max
"""
request = """
SELECT %s( goals )
FROM (
SELECT sum( score_a ) + sum( score_b ) AS goals
FROM season_fixture
GROUP BY week
) weeks_goals
""" % (minmax)
return executeRawSqlQuery(request)
def user_result(week, user):
request = """
SELECT season_forecast.fixture_id,
season_forecast.score_a, season_forecast.score_b,
season_forecast.score, season_forecast.issue,
season_forecast.points
FROM season_forecast
INNER JOIN season_fixture
ON season_forecast.fixture_id = season_fixture.id
INNER JOIN season_player
ON season_forecast.user_id = season_player.id
WHERE season_forecast.user_id = '%s' AND week = '%s'
ORDER BY season_fixture.day, season_fixture.hour
""" % (user, week)
return executeRawSqlQuery(request)
def league_table_with_teamid():
"""
return the full league table for the entire season, with team id
"""
request = """
SELECT season_team.id AS team_id,season_team.name AS team_name, sum(points) AS points,
count(season_table.week)-1 AS played,
sum(win) AS win, sum(draw) AS draw, sum(lose) AS lose,
sum(goal_for) AS goal_for, sum(goal_against) AS goal_against,
(sum(goal_for) - sum(goal_against)) AS diff
FROM season_team, season_table
WHERE season_team.id = season_table.team_id
GROUP BY season_team.id, season_team.name
ORDER BY points DESC, diff DESC, goal_for DESC, goal_against ASC,win DESC, draw DESC, lose DESC, played DESC, team_name ASC
"""
return executeRawSqlQuery(request)
def user_table():
request = """
SELECT season_player.id,
season_player.name,
sum(points) as points,
sum(score) as scores,
sum(issue) as issues,
count(points) as matches
FROM season_forecast
INNER JOIN season_player
ON season_forecast.user_id = season_player.id
GROUP BY season_player.id, season_player.name
HAVING count(points) > 0
ORDER BY points DESC, scores DESC,
issues DESC, matches, season_player.name
"""
return executeRawSqlQuery(request)
def league_table_until(week):
"""
returns the full league table until the given week
"""
request = """
SELECT season_team.name AS team, sum(points) AS points,
count(season_table.week)-1 AS played,
sum(win) AS win, sum(draw) AS draw, sum(lose) AS lose,
sum(goal_for) AS goal_for, sum(goal_against) AS goal_against,
(sum(goal_for) - sum(goal_against)) AS diff
FROM season_team, season_table
WHERE season_team.id = season_table.team_id
AND week <= %s
GROUP BY season_team.name
ORDER BY points DESC, diff DESC, goal_for DESC, goal_against ASC,win DESC, draw DESC, lose DESC, played DESC, team ASC
"""%week
return executeRawSqlQuery(request)
def user_table_with_bonus():
"""
returns the full players table with season forecast bonus
"""
request = """
SELECT c.id,
c.name,
c.points,
c.scores,
c.issues,
c.matches,
c.points + IF (ISNULL(c.bonus),0,c.bonus) as total,
c.bonus
FROM(
SELECT * from
(SELECT season_player.id,
season_player.name,
sum(points) as points,
sum(score) as scores,
sum(issue) as issues,
count(points) as matches
FROM season_forecast
INNER JOIN season_player
ON season_forecast.user_id = season_player.id
GROUP BY season_player.id, season_player.name
HAVING count(points) > 0
ORDER BY points DESC, scores DESC,
issues DESC, matches, season_player.name
)a
LEFT OUTER JOIN (SELECT season_leagueforecast.user_id, season_leagueforecast.points as bonus
FROM season_leagueforecast
)b
ON a.id = b.user_id
)c
ORDER BY ISNULL(total)ASC, total DESC, points DESC, scores DESC,
issues DESC, matches, name, id
"""
#ISNULL(total) ASC, total -->MySql
#total DESC NULLS LAST->PostGreSql
return executeRawSqlQuery(request)
def best_stats():
request = """
SELECT
max(points) as maxpoints,
max(scores) as maxscores,
max(issues) as maxissues
FROM(
SELECT season_forecast.user_id as player,
sum(points) as points,
sum(score) as scores,
sum(issue) as issues
FROM season_forecast
INNER JOIN season_fixture
ON season_forecast.fixture_id = season_fixture.id
GROUP BY season_forecast.user_id, week
) maxpoints
"""
return executeRawSqlQuery(request)
def user_table_until_week(week):
request = """
SELECT season_player.id,
season_player.name,
sum(points) as points,
sum(score) as scores,
sum(issue) as issues,
count(points) as matches
FROM season_forecast
INNER JOIN season_fixture
ON season_forecast.fixture_id = season_fixture.id
INNER JOIN season_player
ON season_forecast.user_id = season_player.id
WHERE week <= '%s'
GROUP BY season_player.id, season_player.name
HAVING count(points) > 0
ORDER BY points DESC, scores DESC,
issues DESC, matches, season_player.name
"""%(week)
return executeRawSqlQuery(request)
def weeks_goals(minmax):
"""
returns either the list of weeks
for either most or less goals scored
@params minmax: min|max
"""
request = """
SELECT *
FROM (
SELECT week, sum( score_a ) + sum( score_b ) AS goals
FROM season_fixture
GROUP BY week
)weeks_goals
WHERE goals = (
SELECT %s( goals )
FROM (
SELECT week, sum( score_a ) + sum( score_b ) AS goals
FROM season_fixture
GROUP BY week ) weeks_goals
)
""" % (minmax)
return executeRawSqlQuery(request)
