<?php
// Football Pool uses the Highcharts javascript API
class Football_Pool_Chart_Data {
/************************************************
All the functions to get the data for the charts
*************************************************/
public function predictions_pie_chart_data( $match, $ranking_id = FOOTBALLPOOL_RANKING_DEFAULT ) {
global $wpdb;
$prefix = FOOTBALLPOOL_DB_PREFIX;
$sql = $wpdb->prepare( "SELECT
COUNT( IF( full = 1, 1, NULL ) ) AS scorefull,
COUNT( IF( toto = 1, 1, NULL ) ) AS scoretoto,
COUNT( IF( goal_bonus = 1,
IF( toto = 1, NULL, 1 ),
NULL )
) AS goalbonus,
COUNT( userId ) AS scoretotal
FROM {$prefix}scorehistory
WHERE `type` = 0 AND ranking_id = %d
GROUP BY scoreOrder HAVING scoreOrder = %d",
$ranking_id, $match
);
return $wpdb->get_row( $sql, ARRAY_A );
}
public function score_chart_data( $users = array(), $ranking_id = FOOTBALLPOOL_RANKING_DEFAULT ) {
$data = array();
$pool = new Football_Pool_Pool;
if ( count( $users ) > 0 ) {
global $wpdb;
$prefix = FOOTBALLPOOL_DB_PREFIX;
$sql = "SELECT
COUNT( IF( s.full = 1, 1, NULL ) ) AS scorefull,
COUNT( IF( s.toto = 1, 1, NULL ) ) AS scoretoto,
COUNT( IF( s.goal_bonus = 1, IF( s.toto = 1, NULL, 1 ), NULL ) ) AS single_goal_bonus,
COUNT( s.scoreOrder ) AS scoretotal,
u.display_name AS username
FROM {$prefix}scorehistory s
INNER JOIN {$wpdb->users} u ON ( u.ID = s.userId ) ";
if ( $pool->has_leagues ) {
$sql .= "INNER JOIN {$prefix}league_users lu ON ( lu.userId = u.ID ) ";
$sql .= "INNER JOIN {$prefix}leagues l ON ( lu.leagueId = l.ID ) ";
} else {
$sql .= "LEFT OUTER JOIN {$prefix}league_users lu ON ( lu.userId = u.ID ) ";
}
$sql .= "WHERE s.ranking_id = {$ranking_id} AND s.type = 0
AND s.userId IN ( " . implode( ',', $users ) . " ) ";
if ( ! $pool->has_leagues ) $sql .= "AND ( lu.leagueId <> 0 OR lu.leagueId IS NULL ) ";
$sql .= "GROUP BY s.userId";
$rows = $wpdb->get_results( $sql, ARRAY_A );
foreach ( $rows as $row ) {
$data[ $row['username'] ] = array(
'scorefull' => $row['scorefull'],
'scoretoto' => $row['scoretoto'],
'scoretotal' => $row['scoretotal'],
'goalbonus' => $row['single_goal_bonus'],
);
}
}
return $data;
}
public function bonus_question_for_users_pie_chart_data( $users = array(),
$ranking_id = FOOTBALLPOOL_RANKING_DEFAULT ) {
$data = array();
if ( count( $users ) > 0 ) {
$pool = new Football_Pool_Pool;
$questions = $pool->get_bonus_questions();
$numquestions = count( $questions );
global $wpdb;
$prefix = FOOTBALLPOOL_DB_PREFIX;
$sql = "SELECT
COUNT( IF(s.score > 0, 1, NULL ) ) AS bonuscorrect,
COUNT( IF(s.score = 0, 1, NULL ) ) AS bonuswrong,
COUNT( s.scoreOrder ) AS bonustotal,
u.display_name AS username
FROM {$prefix}scorehistory s
INNER JOIN {$wpdb->users} u ON ( u.ID = s.userId ) ";
if ( $pool->has_leagues ) {
$sql .= "INNER JOIN {$prefix}league_users lu ON ( lu.userId = u.ID ) ";
$sql .= "INNER JOIN {$prefix}leagues l ON ( lu.leagueId = l.ID ) ";
} else {
$sql .= "LEFT OUTER JOIN {$prefix}league_users lu ON ( lu.userId = u.ID ) ";
}
$sql .= "WHERE s.ranking_id = {$ranking_id} AND s.type = 1
AND s.userId IN ( " . implode(',', $users) . " ) ";
if ( ! $pool->has_leagues ) $sql .= "AND ( lu.leagueId <> 0 OR lu.leagueId IS NULL ) ";
$sql .= "GROUP BY s.userId";
$rows = $wpdb->get_results( $sql, ARRAY_A );
foreach ( $rows as $row ) {
$data[ $row['username'] ] = array(
'bonustotal' => $numquestions,
'bonuscorrect' => $row['bonuscorrect'],
'bonuswrong' => $row['bonuswrong']
);
}
}
return $data;
}
public function bonus_question_pie_chart_data( $question ) {
global $wpdb;
$prefix = FOOTBALLPOOL_DB_PREFIX;
$pool = new Football_Pool_Pool;
$sql = "SELECT
COUNT( IF( ua.correct > 0, 1, NULL ) ) AS bonuscorrect,
COUNT( IF( ua.correct = 0, 1, NULL ) ) AS bonuswrong,
COUNT( u.ID ) AS totalusers
FROM {$prefix}bonusquestions_useranswers AS ua
RIGHT OUTER JOIN {$wpdb->users} AS u
ON ( u.ID = ua.userId AND questionId = %d ) ";
if ( $pool->has_leagues ) {
$sql .= "INNER JOIN {$prefix}league_users lu ON ( lu.userId = u.ID ) ";
$sql .= "INNER JOIN {$prefix}leagues l ON ( lu.leagueId = l.ID ) ";
} else {
$sql .= "LEFT OUTER JOIN {$prefix}league_users lu ON ( lu.userId = u.ID ) ";
$sql .= "WHERE ( lu.leagueId <> 0 OR lu.leagueId IS NULL ) ";
}
$sql = $wpdb->prepare( $sql, $question );
$row = $wpdb->get_row( $sql, ARRAY_A );
$data = array(
'totalusers' => $row['totalusers'],
'bonuscorrect' => $row['bonuscorrect'],
'bonuswrong' => $row['bonuswrong']
);
return $data;
}
public function points_total_pie_chart_data( $user, $ranking_id = FOOTBALLPOOL_RANKING_DEFAULT ) {
global $wpdb;
$prefix = FOOTBALLPOOL_DB_PREFIX;
$output = array();
// get the user's score
$sql = $wpdb->prepare( "SELECT totalScore FROM {$prefix}scorehistory
WHERE userId = %d AND ranking_id = %d
ORDER BY scoreDate DESC, scoreOrder DESC, type DESC LIMIT 1",
$user, $ranking_id
);
$data = $wpdb->get_row( $sql, ARRAY_A );
$output['totalScore'] = $data['totalScore'];
// get the number of matches for which there are results
$sql = $wpdb->prepare( "SELECT COUNT(*) AS numMatches FROM {$prefix}scorehistory
WHERE type = 0 AND userId = %d AND ranking_id = %d", $user, $ranking_id );
$data = $wpdb->get_row( $sql, ARRAY_A );
$full = Football_Pool_Utils::get_fp_option( 'fullpoints', FOOTBALLPOOL_FULLPOINTS, 'int' ) +
( 2 * Football_Pool_Utils::get_fp_option( 'goalpoints', FOOTBALLPOOL_GOALPOINTS, 'int' ) );
$output['maxScore'] = $full * 2; // count first match with joker
$output['maxScore'] += ( (int) $data['numMatches'] - 1 ) * $full; // all other matches
// add the bonusquestions
$sql = "SELECT SUM(points) AS `maxPoints` FROM {$prefix}bonusquestions WHERE scoreDate IS NOT NULL";
$data = $wpdb->get_row( $sql, ARRAY_A );
$output['maxScore'] += (int) $data['maxPoints'];
return $output;
}
public function score_per_match_line_chart_data( $users, $ranking_id = FOOTBALLPOOL_RANKING_DEFAULT ) {
return $this->per_match_line_chart_data( $users, 'totalScore', $ranking_id );
}
public function ranking_per_match_line_chart_data( $users,
$ranking_id = FOOTBALLPOOL_RANKING_DEFAULT ) {
return $this->per_match_line_chart_data( $users, 'ranking', $ranking_id );
}
private function per_match_line_chart_data( $users, $history_data_to_plot,
$ranking_id = FOOTBALLPOOL_RANKING_DEFAULT ) {
$data = array();
if ( count( $users ) > 0 ) {
global $wpdb;
$prefix = FOOTBALLPOOL_DB_PREFIX;
$sql = $wpdb->prepare( "SELECT h.scoreOrder, h." . $history_data_to_plot . ",
u.display_name, h.type
FROM {$prefix}scorehistory h, {$wpdb->users} u
WHERE h.ranking_id = %d AND u.ID = h.userId
AND h.userId IN (" . implode( ',', $users ) . ")
ORDER BY h.scoreDate ASC, h.type ASC, h.scoreOrder ASC, h.userId ASC"
, $ranking_id
);
$rows = $wpdb->get_results( $sql, ARRAY_A );
foreach ( $rows as $row ) {
$data[] = array(
'match' => $row['scoreOrder'],
'type' => $row['type'],
'value' => $row[$history_data_to_plot],
'username' => $row['display_name']
);
}
}
return $data;
}
/*****************************************
Build data arrays for the series option
******************************************/
public function score_chart_series( $rows ) {
$goal_bonus = ( Football_Pool_Utils::get_fp_option( 'goalpoints', FOOTBALLPOOL_GOALPOINTS, 'int' ) > 0 );
$data = array();
foreach ( $rows as $name => $row ) {
$data[$name] = array(
array( __( 'full score', FOOTBALLPOOL_TEXT_DOMAIN ), (int) $row['scorefull'] ),
array( __( 'toto score', FOOTBALLPOOL_TEXT_DOMAIN ), (int) $row['scoretoto'] ),
array( __( 'no score', FOOTBALLPOOL_TEXT_DOMAIN ), (int) $row['scoretotal'] - $row['scorefull'] - $row['scoretoto'] - ( $goal_bonus ? $row['goalbonus'] : 0 ) ),
);
if ( $goal_bonus ) {
$data[$name][] = array( __( 'just the goal bonus', FOOTBALLPOOL_TEXT_DOMAIN ), (int) $row['goalbonus'] );
}
}
return $data;
}
public function predictions_pie_series( $row ) {
$goal_bonus = ( Football_Pool_Utils::get_fp_option( 'goalpoints', FOOTBALLPOOL_GOALPOINTS, 'int' ) > 0 );
$data = array(
array( __( 'full score', FOOTBALLPOOL_TEXT_DOMAIN ), (int) $row['scorefull'] ),
array( __( 'toto score', FOOTBALLPOOL_TEXT_DOMAIN ), (int) $row['scoretoto'] ),
array( __( 'no score', FOOTBALLPOOL_TEXT_DOMAIN ), (int) $row['scoretotal'] - $row['scorefull'] - $row['scoretoto'] - ( $goal_bonus ? $row['goalbonus'] : 0 ) )
);
if ( $goal_bonus ) {
$data[] = array( __( 'just the goal bonus', FOOTBALLPOOL_TEXT_DOMAIN ), (int) $row['goalbonus'] );
}
return $data;
}
public function points_total_pie_series( $row ) {
$data = array(
array( __( 'points scored', FOOTBALLPOOL_TEXT_DOMAIN ), (int) $row['totalScore'] ),
array( __( 'points missed', FOOTBALLPOOL_TEXT_DOMAIN ), (int) $row['maxScore'] - $row['totalScore'] )
);
return $data;
}
public function bonus_question_pie_series( $rows ) {
$data = array();
foreach ( $rows as $name => $row ) {
$data[$name] = array(
array( __( 'correct', FOOTBALLPOOL_TEXT_DOMAIN ), (int) $row['bonuscorrect'] ),
array( __( 'wrong', FOOTBALLPOOL_TEXT_DOMAIN ), (int) $row['bonuswrong'] ),
array( __( 'still open', FOOTBALLPOOL_TEXT_DOMAIN ), (int) $row['bonustotal'] - $row['bonuscorrect'] - $row['bonuswrong'] )
//array( __( 'no answer', FOOTBALLPOOL_TEXT_DOMAIN ), (int) $row['bonusnoanswer'] )
);
}
return $data;
}
public function bonus_question_pie_series_one_question( $row ) {
$data = array(
array( __( 'correct', FOOTBALLPOOL_TEXT_DOMAIN ), (int) $row['bonuscorrect'] ),
array( __( 'wrong', FOOTBALLPOOL_TEXT_DOMAIN ), (int) $row['bonuswrong'] ),
array( __( 'no answer', FOOTBALLPOOL_TEXT_DOMAIN ), (int) $row['totalusers'] - $row['bonuscorrect'] - $row['bonuswrong'] )
);
return $data;
}
private function per_match_line_series( $lines ) {
if ( count( $lines ) > 0 ) {
$match_obj = new Football_Pool_Matches;
$categoriesdata = array();
$seriesdata = array();
$users = array();
$matchnr = 0;
$questionnr = 0;
$match = '';
$type = '';
foreach ( $lines as $datarow ) {
// if new user, then start a new series
$user = $datarow['username'];
if ( ! array_key_exists( $user, $seriesdata ) ) {
$seriesdata[$user] = array(
'name' => $user,
'data' => array()
);
}
// new match or question?
if ( $match != $datarow['match'] || $type != $datarow['type'] ) {
$match = (int) $datarow['match'];
$type = $datarow['type'];
if ( $type == 0 ) {
$matchinfo = $match_obj->get_match_info( $match );
$category_data = __( 'match', FOOTBALLPOOL_TEXT_DOMAIN ) . ' ' . ++$matchnr;
if ( isset( $matchinfo['home_team'] ) ) {
$category_data .= ': ' . $matchinfo['home_team'] . ' - ' . $matchinfo['away_team'];
}
$categoriesdata[] = $category_data;
} else {
$categoriesdata[] = __( 'bonus question', FOOTBALLPOOL_TEXT_DOMAIN ) . ' ' . ++$questionnr;
}
}
$seriesdata[$user]['data'][] = (int) $datarow['value'];
}
$output = array(
'categories' => $categoriesdata,
'series' => $seriesdata
);
} else {
$output = array(
'categories' => array(),
'series' => array()
);
}
return $output;
}
public function score_per_match_line_series( $lines ) {
return $this->per_match_line_series( $lines );
}
public function ranking_per_match_line_series( $lines ) {
return $this->per_match_line_series( $lines );
}
}
?>