Location: PHPKode > scripts > Football Pool > football-pool/classes/class-football-pool-groups.php
<?php
class Football_Pool_Groups {
	public function add( $name ) {
		global $wpdb;
		$prefix = FOOTBALLPOOL_DB_PREFIX;
		$sql = $wpdb->prepare( "INSERT INTO {$prefix}groups ( name )
								VALUES ( %s )",
								$name
							);
		$wpdb->query( $sql );
		return $wpdb->insert_id;
	}
	
	public function update( $id, $name ) {
		global $wpdb;
		$prefix = FOOTBALLPOOL_DB_PREFIX;
		
		if ( $id == 0 ) {
			$id = self::add( $name );
		} else {
			$sql = $wpdb->prepare( "UPDATE {$prefix}groups SET
										name = %s
									WHERE id = %d",
									$name, $id
								);
			$wpdb->query( $sql );
		}
		
		return $id;
	}
	
	public function get_groups() {
		global $wpdb;
		$prefix = FOOTBALLPOOL_DB_PREFIX;
		
		$sql = "SELECT id, name FROM {$prefix}groups ORDER BY name ASC";
		return $wpdb->get_results( $sql );
	}
	
	public function get_group_by_id( $id ) {
		global $wpdb;
		$prefix = FOOTBALLPOOL_DB_PREFIX;
		
		$sql = $wpdb->prepare( "SELECT id, name FROM {$prefix}groups WHERE id = %d", $id );
		return $wpdb->get_row( $sql );
	}
	
	public function get_group_by_name( $name, $addnew = '' ) {
		if ( $name == '' ) return 0;
		
		global $wpdb;
		$prefix = FOOTBALLPOOL_DB_PREFIX;
		
		$sql = $wpdb->prepare( "SELECT id, name FROM {$prefix}groups WHERE name = %s", $name );
		$result = $wpdb->get_row( $sql );
		
		if ( $addnew == 'addnew' && $result == null ) {
			$id = self::add( $name );
			$result = (object) array( 
									'id'          => $id, 
									'name'        => $name,
									'inserted'    => true
									);
		}
		
		return $result;
	}
	
	public function get_group_names() {
		$group_names = array();
		
		$rows = $this->get_group_composition();
		foreach ( $rows as $row ) {
			$group_names[ (integer) $row['id'] ] = htmlentities( $row['name'], null, 'UTF-8' );
		}
		
		return $group_names;
	}
	
	public function get_ranking_array() {
		$ranking = $this->get_standings();
		$group_ranking = array();
		
		$rows = $this->get_group_composition();
		foreach ( $rows as $row ) {
			$group_ranking[ $row['id'] ][ (integer) $row['teamId'] ] = $this->get_standing_for_team( $ranking, $row['teamId'] );
		}
		
		return $this->order_groups( $group_ranking );
	}
	
	private function order_groups( $arr ) {
		foreach ( $arr as $group => $teams ) {
			uasort( $arr[$group], array( 'Football_Pool_Groups', 'compare_teams' ) );
		}
		return $arr;
	}
	

	/* alternative way to get all the scores: combine results of these 2 queries
	
		select 
		  homeTeamId,  
		  count(*) as games, 
		  sum(homeScore) as goals, 
		  sum(awayScore) as against, 
		  count(if(homeScore>awayScore,1,null)) as wins, 
		  count(if(homeScore=awayScore,1,null)) as draws, 
		  count(if(homeScore<awayScore,1,null)) as losses 
		from pool_matches 
		group by homeTeamId 
		
		select 
		  awayTeamId,  
		  count(*) as games, 
		  sum(awayScore) as goals, 
		  sum(homeScore) as against, 
		  count(if(homeScore<awayScore,1,null)) as wins, 
		  count(if(homeScore=awayScore,1,null)) as draws, 
		  count(if(homeScore>awayScore,1,null)) as losses 
		from pool_matches 
		group by awayTeamId
	*/
	private function get_standings()
	{
		$wins = array();
		$draws = array();
		$losses = array();
		$for = array();
		$against = array();
		
		$matches = new Football_Pool_Matches;
		$match_types = Football_Pool_Utils::get_fp_option( 
													'groups_page_match_types' 
													, array( FOOTBALLPOOL_GROUPS_PAGE_DEFAULT_MATCHTYPE ) 
												);
		$rows = $matches->get_info( $match_types );
		
		foreach ( $rows as $row ) {
			if ( ( $row['homeScore'] != null ) && ( $row['awayScore'] != null ) ) {
				// set goals
				$this->set_goals_array( 
								$for, $against, 
								$row['homeTeamId'], $row['awayTeamId'], 
								$row['homeScore'], $row['awayScore'] 
						);
				// set wins, draws and losses
				if ( (int) $row['homeScore'] > (int) $row['awayScore'] ) {
					$wins   = $this->set_standing_array( $wins, $row['homeTeamId'] );
					$losses = $this->set_standing_array( $losses, $row['awayTeamId'] );
				} elseif ( (int) $row['homeScore'] < (int) $row['awayScore'] ) {
					$losses = $this->set_standing_array( $losses, $row['homeTeamId'] );
					$wins   = $this->set_standing_array( $wins, $row['awayTeamId'] );
				} elseif ( (int) $row['homeScore'] == (int) $row['awayScore'] ) {
					$draws = $this->set_standing_array( $draws, $row['homeTeamId'] );
					$draws = $this->set_standing_array( $draws, $row['awayTeamId'] );
				} else {
					echo 'what the fuck? this shouldn\'t happen: ', $row['homeTeamId'], '-', $row['awayTeamId'], '<br />';
				}
			}
		}
		return array( $wins, $draws, $losses, $for, $against );
	}
	
	private function get_standing_for_team( $ranking, $id ) {
		$wins =    ( isset( $ranking[0][$id] ) ? $ranking[0][$id] : 0 );
		$draws =   ( isset( $ranking[1][$id] ) ? $ranking[1][$id] : 0 );
		$losses =  ( isset( $ranking[2][$id] ) ? $ranking[2][$id] : 0 );
		$for =     ( isset( $ranking[3][$id] ) ? $ranking[3][$id] : 0 );
		$against = ( isset( $ranking[4][$id] ) ? $ranking[4][$id] : 0 );
		$points =  ( $wins * 3 ) + $draws;
		$plays =   $wins + $draws + $losses;
		return array(
					'team' => $id, 
					'plays' => $plays, 
					'wins' => $wins, 
					'draws' => $draws, 
					'losses' => $losses, 
					'points' => $points, 
					'for' => $for, 
					'against' => $against
					);
	}
	
	// only return games for the first round
	public function get_plays_for_group( $group ) {
		global $wpdb;
		$prefix = FOOTBALLPOOL_DB_PREFIX;
		
		$sorting = Football_Pool_Matches::get_match_sorting_method();
		$match_types = Football_Pool_Utils::get_fp_option( 
													'groups_page_match_types' 
													, array( FOOTBALLPOOL_GROUPS_PAGE_DEFAULT_MATCHTYPE ) 
												);
		$match_types = implode( ',', $match_types );
		
		$sql = $wpdb->prepare( "SELECT DISTINCT
									UNIX_TIMESTAMP(m.playDate) AS match_timestamp, 
									m.homeTeamId, 
									m.awayTeamId, 
									m.homeScore, 
									m.awayScore, 
									s.id AS stadiumId, 
									s.name AS stadiumName, 
									t.name AS matchtype, 
									m.nr,
									m.playDate
								FROM {$prefix}matches m, {$prefix}stadiums s, 
									{$prefix}matchtypes t, {$prefix}teams tm 
								WHERE m.stadiumId = s.id 
									AND m.matchtypeId = t.id 
									AND t.id IN ( {$match_types} )
									AND ( m.homeTeamId = tm.id OR m.awayTeamId = tm.id )
									AND tm.groupId = %d
								ORDER BY {$sorting}",
							$group
						);
		
		return $wpdb->get_results( $sql, ARRAY_A );
	}

	private function compare_teams( $a, $b ) {
		// if points are equal
		if ( $a['points'] == $b['points'] ) {
			// check if they have the same number of plays
			if ( $a['plays'] == $b['plays'] ) {
				// if so, check if they played each other
				$matches = new Football_Pool_Matches;
				$gameresult = $matches->get_match_info_for_teams( $a['team'], $b['team'] );
				
				if ( is_array( $gameresult ) ) {
					// check the result
					if ( $gameresult[ $a['team'] ] != $gameresult[ $b['team'] ] ) {
						// reverse the ordering (descending order) 'cos the team that wins gets the advantage
						return ( $gameresult[ $a['team'] ] < $gameresult[ $b['team'] ] ) ? +1 : -1;
					}
				}
				
				// it was a draw or the teams didn't play each other, now check goal difference
				if ( ( $a['for'] - $a['against'] ) == ( $b['for'] - $b['against'] ) ) {
					// now check the goals scored
					if ( $a['for'] == $b['for'] ) {
						// all failed, so we check a hardcoded ordering
						$teams = new Football_Pool_Teams;
						return ( $teams->get_group_order( (integer) $a['team'] ) > $teams->get_group_order( (integer) $b['team'] ) ? +1 : -1 );
					}
					// the one with more goals wins (descending order)
					return ( $a['for'] < $b['for'] ) ? +1 : -1;
				}
				// the one with more goals wins (descending order)
				return ( ( $a['for'] - $a['against'] ) < ( $b['for'] - $b['against'] ) ? +1 : -1 );
			}
			// the one with the least plays has the advantage
			return ( $a['plays'] > $b['plays'] ) ? +1 : -1;
		}
		// order descending
		return ( $a['points'] < $b['points'] ) ? +1 : -1;
	}
	
	private function set_goals_array( &$for, &$against, $home_team, $away_team, $home_score, $away_score ) {
		$home_team = (int) $home_team;
		$away_team = (int) $away_team;
		
		$for[$home_team]     = $this->set_goals( $for, $home_team, $home_score );
		$for[$away_team]     = $this->set_goals( $for, $away_team, $away_score );
		$against[$home_team] = $this->set_goals( $against, $home_team, $away_score );
		$against[$away_team] = $this->set_goals( $against, $away_team, $home_score );
	}
	
	private function set_goals( $goals, $team, $score ) {
		if ( ! isset( $goals[$team] ) ) {
			return $score;
		} else {
			return $goals[$team] + $score;
		}
	}
	
	private function set_standing_array( $arr, $id ) {
		$id = (integer) $id;
		
		if ( isset( $arr[$id] ) && $arr[$id] != null ) {
			$arr[$id]++;
		} else {
			$arr[$id] = 1;
		}
		
		return $arr;
	}
	
	private function get_group_composition() {
		$cache_key = 'fp_get_group_composition';
		$rows = wp_cache_get( $cache_key );
		
		if ( $rows === false ) {
			global $wpdb;
			$prefix = FOOTBALLPOOL_DB_PREFIX;
			$sql = "SELECT t.id AS teamId, t.name AS teamName, g.id, g.name 
					FROM {$prefix}teams t, {$prefix}groups g 
					WHERE t.groupId = g.id AND t.is_real = 1 AND t.is_active = 1
					ORDER BY g.name ASC, t.groupOrder ASC, t.id ASC";
			$rows = $wpdb->get_results( $sql, ARRAY_A );
			wp_cache_set( $cache_key, $rows );
		}
		
		return $rows;
	}
	
	public function print_group_standing( $group_id, $layout = 'wide', $class = '' ) {
		if ( $class != '' ) $class = ' ' . $class;
		
		$output = '';
		$teams = new Football_Pool_Teams;
		$team_names = $teams->team_names;

		$group_names = $this->get_group_names();
		$ranking = $this->get_ranking_array();

		if ( $layout == 'wide' ) {
			$wdl = '<th class="wins"><span title="wins">w</span></th><th class="draws">'
				 . '<span title="draws">d</span></th><th class="losses"><span title="losses">l</span></th>';
			$th1 = '';
			$th2 = '';
			$format = '<tr>
							<td class="team">%s</td>
							<td class="plays">%d</td>
							<td class="wins">%d</td>
							<td class="draws">%d</td>
							<td class="losses">%d</td>
							<td class="points">%d</td>
							<td class="goals">(%d-%d)</td>
						</tr>';
		} else {
			$wdl = '';
			$th1 = '<span title="matches">m</span>';
			$th2 = '<span title="points">p</span>';
			$format = '<tr>
							<td class="team">%s</td>
							<td class="plays">%d</td>
							<td class="points">%d</td>
							<td class="goals">(%d-%d)</td>
						</tr>';
		}
		
		$teampage = Football_Pool::get_page_link( 'teams' );
		
		foreach ( $ranking as $group => $rank ) {
			if ( $group_id == '' || $group_id == $group ) {
				$output .= sprintf( '<div class="ranking%s"><h2>%s</h2>', $class, $group_names[$group] );
				$output .= '<table class="ranking">
								<thead>
									<tr>
										<th class="team"></th>
										<th class="plays">' . $th1 . '</th>';
				$output .= $wdl;
				$output .= '			<th class="points">' . $th2 . '</th>
										<th class="goals"></th>
									</tr>
								</thead>
								<tbody>';
				foreach ( $rank as $teamranking ) {
					if ( $teams->show_team_links ) {
						$team_name = sprintf( '<a href="%s">%s</a>'
												, esc_url( 
														add_query_arg( 
															array( 'team' => $teamranking['team'] ), 
															$teampage 
														) 
													)
												, $team_names[$teamranking['team']]
											);
					} else {
						$team_name = $team_names[$teamranking['team']];
					}
					
					if ( $layout == 'wide' ) { 
						$args_array = array(
											$team_name,
											$teamranking['plays'],
											$teamranking['wins'],
											$teamranking['draws'],
											$teamranking['losses'],
											$teamranking['points'],
											$teamranking['for'],
											$teamranking['against']
										);
					} else {
						$args_array = array(
											$team_name,
											$teamranking['plays'],
											$teamranking['points'],
											$teamranking['for'],
											$teamranking['against']
										);
					}
					
					$output .= vsprintf( $format, $args_array );
				}
				$output .= '</tbody></table></div>';
			}
		}
		
		return $output;
	}
}
?>
Return current item: Football Pool