/**
* Fill-in data in the league table
*
* @param integer $id_league
* @param integer $start
* @param integer $fixture
* @param integer $nb_clubs
* @param integer $pt_victory
* @param integer $pt_draw
* @param integer $pt_defeat
* @return object
*/
public function fill_league_table($id_league, $start, $fixture, $nb_clubs, $sport, $pt_v, $pt_d, $pt_l)
{
global $wpdb;
$self = new PHPLeague_Database();
// Delete old data
$wpdb->query("DELETE FROM {$wpdb->table_cache} WHERE id_league = {$id_league}");
if (!$fixture) {
$fixture = $nb_clubs * 2 - 2;
}
if (!$start) {
$start = 1;
}
// Get settings
$pt_victory = $pt_v;
$pt_draw = $pt_d;
$pt_defeat = $pt_l;
// Home victory
$query = "SELECT t_home.id, COUNT(t_home.id) as count_home_id, c.name, SUM(g.goal_home) as g_home, SUM(g.goal_away) as g_away\n FROM {$wpdb->team} t_home, {$wpdb->club} c, {$wpdb->match} g, {$wpdb->fixture} d, {$wpdb->league} l\n WHERE t_home.id_league = {$id_league}\n AND t_home.id_club = c.id\n AND t_home.id = g.id_team_home\n AND g.goal_home > g.goal_away\n AND l.id = d.id_league\n AND d.id = g.id_fixture\n AND d.number >= {$start}\n AND d.number <= {$fixture}\n GROUP BY c.name";
foreach ($wpdb->get_results($wpdb->prepare($query, NULL)) as $row) {
$name = trim($row->name);
$table[$name]['home_v'] = $row->count_home_id;
if (!isset($table[$name]['home_g_for'])) {
$table[$name]['home_g_for'] = $row->g_home;
} else {
$table[$name]['home_g_for'] += $row->g_home;
}
if (!isset($table[$name]['home_g_against'])) {
$table[$name]['home_g_against'] = $row->g_away;
} else {
$table[$name]['home_g_against'] += $row->g_away;
}
}
// Home defeat
$query = "SELECT t_home.id, COUNT(t_home.id) as count_home_id, c.name, SUM(g.goal_home) as g_home, SUM(g.goal_away) as g_away\n FROM {$wpdb->team} t_home, {$wpdb->club} c, {$wpdb->match} g, {$wpdb->fixture} d, {$wpdb->league} l\n WHERE t_home.id_league = {$id_league}\n AND t_home.id_club = c.id\n AND t_home.id = g.id_team_home\n AND g.goal_home < g.goal_away\n AND l.id = d.id_league\n AND d.id = g.id_fixture\n AND d.number >= {$start}\n AND d.number <= {$fixture}\n GROUP BY c.name";
foreach ($wpdb->get_results($wpdb->prepare($query, NULL)) as $row) {
$name = trim($row->name);
$table[$name]['home_l'] = $row->count_home_id;
if (!isset($table[$name]['home_g_for'])) {
$table[$name]['home_g_for'] = $row->g_home;
} else {
$table[$name]['home_g_for'] += $row->g_home;
}
if (!isset($table[$name]['home_g_against'])) {
$table[$name]['home_g_against'] = $row->g_away;
} else {
$table[$name]['home_g_against'] += $row->g_away;
}
}
// Home draw
$query = "SELECT t_home.id, COUNT(t_home.id) as count_home_id, c.name, SUM(g.goal_home) as g_home, SUM(g.goal_away) as g_away\n FROM {$wpdb->team} t_home, {$wpdb->club} c, {$wpdb->match} g, {$wpdb->fixture} d, {$wpdb->league} l\n WHERE t_home.id_league = {$id_league}\n AND t_home.id_club = c.id\n AND t_home.id = g.id_team_home\n AND g.goal_home = g.goal_away\n AND g.goal_home IS NOT NULL\n AND g.goal_away IS NOT NULL\n AND l.id = d.id_league\n AND d.id = g.id_fixture\n AND d.number >= {$start}\n AND d.number <= {$fixture}\n GROUP BY c.name";
foreach ($wpdb->get_results($wpdb->prepare($query, NULL)) as $row) {
$name = trim($row->name);
$table[$name]['home_d'] = $row->count_home_id;
if (!isset($table[$name]['home_g_for'])) {
$table[$name]['home_g_for'] = $row->g_home;
} else {
$table[$name]['home_g_for'] += $row->g_home;
}
if (!isset($table[$name]['home_g_against'])) {
$table[$name]['home_g_against'] = $row->g_away;
} else {
$table[$name]['home_g_against'] += $row->g_away;
}
}
// Away victory
$query = "SELECT t_away.id, COUNT(t_away.id) as count_away_id, c.name, SUM(g.goal_home) as g_home, SUM(g.goal_away) as g_away\n FROM {$wpdb->team} t_away, {$wpdb->club} c, {$wpdb->match} g, {$wpdb->fixture} d, {$wpdb->league} l\n WHERE t_away.id_league = {$id_league}\n AND t_away.id_club = c.id\n AND t_away.id = g.id_team_away\n AND g.goal_away > g.goal_home\n AND l.id = d.id_league\n AND d.id = g.id_fixture\n AND d.number >= {$start}\n AND d.number <= {$fixture}\n GROUP BY c.name";
foreach ($wpdb->get_results($wpdb->prepare($query, NULL)) as $row) {
$name = trim($row->name);
$table[$name]['away_v'] = $row->count_away_id;
if (!isset($table[$name]['away_g_for'])) {
$table[$name]['away_g_for'] = $row->g_away;
} else {
$table[$name]['away_g_for'] += $row->g_away;
}
if (!isset($table[$name]['away_g_against'])) {
$table[$name]['away_g_against'] = $row->g_home;
} else {
$table[$name]['away_g_against'] += $row->g_home;
}
}
// Away defeat
$query = "SELECT t_away.id, COUNT(t_away.id) as count_away_id, c.name, SUM(g.goal_home) as g_home, SUM(g.goal_away) as g_away\n FROM {$wpdb->team} t_away, {$wpdb->club} c, {$wpdb->match} g, {$wpdb->fixture} d, {$wpdb->league} l\n WHERE t_away.id_league = {$id_league}\n AND t_away.id_club = c.id\n AND t_away.id = g.id_team_away\n AND g.goal_away < g.goal_home\n AND l.id = d.id_league\n AND d.id = g.id_fixture\n AND d.number >= {$start}\n AND d.number <= {$fixture}\n GROUP BY c.name";
foreach ($wpdb->get_results($wpdb->prepare($query, NULL)) as $row) {
$name = trim($row->name);
$table[$name]['away_l'] = $row->count_away_id;
if (!isset($table[$name]['away_g_for'])) {
$table[$name]['away_g_for'] = $row->g_away;
} else {
$table[$name]['away_g_for'] += $row->g_away;
}
if (!isset($table[$name]['away_g_against'])) {
$table[$name]['away_g_against'] = $row->g_home;
} else {
$table[$name]['away_g_against'] += $row->g_home;
}
}
// Away draw
$query = "SELECT t_away.id, COUNT(t_away.id) as count_away_id, c.name, SUM(g.goal_home) as g_home, SUM(g.goal_away) as g_away\n FROM {$wpdb->team} t_away, {$wpdb->club} c, {$wpdb->match} g, {$wpdb->fixture} d, {$wpdb->league} l\n WHERE t_away.id_league = {$id_league}\n AND t_away.id_club = c.id\n AND t_away.id = g.id_team_away\n AND g.goal_away = g.goal_home\n AND g.goal_away IS NOT NULL\n AND g.goal_home IS NOT NULL\n AND l.id = d.id_league\n AND d.id = g.id_fixture\n AND d.number >= {$start}\n AND d.number <= {$fixture}\n GROUP BY c.name";
foreach ($wpdb->get_results($wpdb->prepare($query, NULL)) as $row) {
$name = trim($row->name);
$table[$name]['away_d'] = $row->count_away_id;
if (!isset($table[$name]['away_g_for'])) {
$table[$name]['away_g_for'] = $row->g_away;
} else {
$table[$name]['away_g_for'] += $row->g_away;
}
if (!isset($table[$name]['away_g_against'])) {
$table[$name]['away_g_against'] = $row->g_home;
} else {
$table[$name]['away_g_against'] += $row->g_home;
}
}
// Get all the data we need to fill in the table
foreach ($self->get_teams_information_table($id_league) as $row) {
$name = trim($row->name);
$home_victory = isset($table[$name]['home_v']) ? $table[$name]['home_v'] : '';
$home_draw = isset($table[$name]['home_d']) ? $table[$name]['home_d'] : '';
$home_defeat = isset($table[$name]['home_l']) ? $table[$name]['home_l'] : '';
$away_victory = isset($table[$name]['away_v']) ? $table[$name]['away_v'] : '';
$away_draw = isset($table[$name]['away_d']) ? $table[$name]['away_d'] : '';
$away_defeat = isset($table[$name]['away_l']) ? $table[$name]['away_l'] : '';
$away_g_for = isset($table[$name]['away_g_for']) ? $table[$name]['away_g_for'] : '';
$home_g_for = isset($table[$name]['home_g_for']) ? $table[$name]['home_g_for'] : '';
$away_g_aga = isset($table[$name]['away_g_against']) ? $table[$name]['away_g_against'] : '';
$home_g_aga = isset($table[$name]['home_g_against']) ? $table[$name]['home_g_against'] : '';
$home_played = $home_victory + $home_draw + $home_defeat;
$away_played = $away_victory + $away_draw + $away_defeat;
$played = $home_played + $away_played;
$home_pts = $home_victory * $pt_victory + $home_draw * $pt_draw + $home_defeat * $pt_defeat;
$away_pts = $away_victory * $pt_victory + $away_draw * $pt_draw + $away_defeat * $pt_defeat;
$points = $home_pts + $away_pts + $row->penalty;
$nb_victory = $home_victory + $away_victory;
$nb_draw = $home_draw + $away_draw;
$nb_defeat = $home_defeat + $away_defeat;
$home_v = $home_victory;
$home_d = $home_draw;
$home_l = $home_defeat;
$away_v = $away_victory;
$away_d = $away_draw;
$away_l = $away_defeat;
$goal_for = $away_g_for + $home_g_for;
$home_g_for = $home_g_for;
$away_g_for = $away_g_for;
$goal_against = $home_g_aga + $away_g_aga;
$home_g_against = $home_g_aga;
$away_g_against = $away_g_aga;
$diff = $goal_for - $goal_against;
$home_diff = $home_g_for - $home_g_against;
$away_diff = $away_g_for - $away_g_against;
$wpdb->insert($wpdb->table_cache, array('club_name' => $name, 'id_team' => $row->team_id, 'id_league' => $id_league, 'points' => $points, 'home_points' => $home_pts, 'away_points' => $away_pts, 'played' => $played, 'home_played' => $home_played, 'away_played' => $away_played, 'victory' => $nb_victory, 'draw' => $nb_draw, 'defeat' => $nb_defeat, 'home_v' => $home_v, 'home_d' => $home_d, 'home_l' => $home_l, 'away_v' => $away_v, 'away_d' => $away_d, 'away_l' => $away_l, 'goal_for' => $goal_for, 'goal_against' => $goal_against, 'home_g_for' => $home_g_for, 'home_g_against' => $home_g_against, 'away_g_for' => $away_g_for, 'away_g_against' => $away_g_against, 'diff' => $diff, 'home_diff' => $home_diff, 'away_diff' => $away_diff, 'pen' => $row->penalty), array('%s', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d', '%d'));
}
}
