<?php

/**
 * Turing functions
 *
 * Contains globally available tool functions for turing. It is included in register page
 *
 *
 * @package Core
 * @author  Eric GALLENE <okrome@free.fr>
 * @version $Id: functions.php,v 1.00 2006/10/23 18:15:53
 */


// add pwd to include_path
ini_set('include_path', '.:'.ini_get('include_path'));


function StatsPlayers($data) {
global $config;

$SELECT = 'SELECT '.TBL_COMPETITIONS.'.id, '.TBL_COMPETITIONS.'.shortname, SCO.shortname AS subshortname 
FROM '.TBL_CANDIDATES.' 
JOIN '.TBL_COMPETITIONS.' ON '.TBL_COMPETITIONS.'.id = '.TBL_CANDIDATES.'.id_compet
LEFT JOIN '.TBL_COMPETITIONS.' AS SCO ON '.TBL_COMPETITIONS.'.subcompet = SCO.id
WHERE '.TBL_CANDIDATES.'.id_season ='.$config['seasonfilterdefault'].' AND '.TBL_CANDIDATES.'.id_club ='.$data['id_club'];
$data['competition']   = runSQL($SELECT);


$SELECT = 'SELECT '.TBL_DAYS.'.id_compet, count('.TBL_GOALS.'.id) as goals, sum('.TBL_GOALS.'.goal_pen) as penalty 
FROM '.TBL_PERSONS.' 
JOIN '.TBL_GOALS.' ON '.TBL_PERSONS.'.id = '.TBL_GOALS.'.id_person
JOIN '.TBL_MATCHS.' ON '.TBL_MATCHS.'.id = '.TBL_GOALS.'.id_match
JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day
WHERE '.TBL_GOALS.'.id_person = '.$data['id'].' AND '.TBL_DAYS.'.id_season = '.$config['seasonfilterdefault'].' AND '.TBL_GOALS.'.goal_csc = 0
GROUP BY '.TBL_DAYS.'.id_compet';
$data['goals']   = runSQL($SELECT);

$SELECT = 'SELECT '.TBL_DAYS.'.id_compet, sum('.TBL_CARDS.'.card_red) as redcards, sum('.TBL_CARDS.'.card_yellow) as yellowcards 
FROM '.TBL_PERSONS.' 
JOIN '.TBL_CARDS.' ON '.TBL_PERSONS.'.id = '.TBL_CARDS.'.id_person
JOIN '.TBL_MATCHS.' ON '.TBL_MATCHS.'.id = '.TBL_CARDS.'.id_match
JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day
WHERE '.TBL_CARDS.'.id_person = '.$data['id'].' AND '.TBL_DAYS.'.id_season = '.$config['seasonfilterdefault'].'
GROUP BY '.TBL_DAYS.'.id_compet';
$data['cards'] = runSQL($SELECT);

$SELECT = 'SELECT '.TBL_DAYS.'.id_compet, '.TBL_LINEUP.'.id_match, '.TBL_PARAMS.'.period, '.TBL_PARAMS.'.period_time, '.TBL_MATCHS.'.extendtime, '.TBL_PARAMS.'.extension, '.TBL_PARAMS.'.extension_time, '.TBL_LINEUP.'.id_person, '.TBL_LINEUP.'.type, '.TBL_SUBS.'.id_person_in, 
'.TBL_SUBS.'.id_person_out, '.TBL_SUBS.'.subs_min 
FROM '.TBL_LINEUP.' 
JOIN '.TBL_MATCHS.' ON '.TBL_MATCHS.'.id = '.TBL_LINEUP.'.id_match
JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day 
JOIN '.TBL_PARAMS.' ON '.TBL_PARAMS.'.id_compet = '.TBL_DAYS.'.id_compet AND '.TBL_PARAMS.'.id_season = '.$config['seasonfilterdefault'].' 
LEFT JOIN '.TBL_SUBS.' ON '.TBL_LINEUP.'.id_match = '.TBL_SUBS.'.id_match AND ('.TBL_SUBS.'.id_person_in = '.$data['id'].' OR '.TBL_SUBS.'.id_person_out = '.$data['id'].')
WHERE '.TBL_LINEUP.'.id_person = '.$data['id'].' AND '.TBL_LINEUP.'.type IN (2,3) AND '.TBL_DAYS.'.id_season = '.$config['seasonfilterdefault'];
$lineup = runSQL($SELECT);



foreach($lineup as $match) {
$x=$match['id_compet'];
	$data['lineup'][$x]['id_compet'] = $match['id_compet'];
	if ($match['type'] == '2') {
	$data['lineup'][$x]['titular'] = $data['lineup'][$x]['titular'] + 1;
		if (!$match['subs_min'] && !$match['extendtime']) {
			$data['lineup'][$x]['playtime'] = $data['lineup'][$x]['playtime'] + ($match['period'] * $match['period_time']);
			}
		else if (!$match['subs_min'] && $match['extendtime']) {
			$data['lineup'][$x]['playtime'] = $data['lineup'][$x]['playtime'] + ($match['period'] * $match['period_time']) + ($match['extension'] * $match['extension_time']);
			}
		else {
			$data['lineup'][$x]['playtime'] = $data['lineup'][$x]['playtime'] + $match['subs_min'];
			}
	}
	else if ($match['type'] == '3') {
	$data['lineup'][$x]['substitute'] = $data['lineup'][$x]['substitute'] + 1;
		if ($match['subs_min']) {
			$data['lineup'][$x]['playtime'] = $data['lineup'][$x]['playtime'] + (($match['period'] * $match['period_time']) - $match['subs_min']);
			}
	}
}

return($data);
}

function StatsReferees($data) {
global $config;
$SELECT = 'SELECT '.TBL_COMPETITIONS.'.id, '.TBL_COMPETITIONS.'.shortname, SCO.shortname AS subshortname 
FROM '.TBL_COMPETITIONS.' 
JOIN '.TBL_DAYS.' ON '.TBL_COMPETITIONS.'.id = '.TBL_DAYS.'.id_compet
JOIN '.TBL_MATCHS.' ON '.TBL_MATCHS.'.id_day = '.TBL_DAYS.'.id
LEFT JOIN '.TBL_COMPETITIONS.' AS SCO ON '.TBL_COMPETITIONS.'.subcompet = SCO.id
WHERE '.TBL_DAYS.'.id_season ='.$config['seasonfilterdefault'].' AND '.TBL_MATCHS.'.id_referee ='.$data['id'].'
GROUP BY '.TBL_COMPETITIONS.'.id';
$data['competition']   = runSQL($SELECT);

$SELECT = 'SELECT '.TBL_DAYS.'.id_compet, count('.TBL_GOALS.'.id) as goals, sum('.TBL_GOALS.'.goal_pen) as penalty 
FROM '.TBL_MATCHS.' 
JOIN '.TBL_GOALS.' ON '.TBL_MATCHS.'.id = '.TBL_GOALS.'.id_match
JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day
WHERE '.TBL_MATCHS.'.id_referee = '.$data['id'].' AND '.TBL_DAYS.'.id_season = '.$config['seasonfilterdefault'].'
GROUP BY '.TBL_DAYS.'.id_compet';
$data['goals']   = runSQL($SELECT);
  
$SELECT = 'SELECT '.TBL_DAYS.'.id_compet, sum('.TBL_CARDS.'.card_red) as redcards, sum('.TBL_CARDS.'.card_yellow) as yellowcards 
FROM '.TBL_PERSONS.' 
JOIN '.TBL_CARDS.' ON '.TBL_PERSONS.'.id = '.TBL_CARDS.'.id_person
JOIN '.TBL_MATCHS.' ON '.TBL_MATCHS.'.id = '.TBL_CARDS.'.id_match
JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day
WHERE '.TBL_MATCHS.'.id_referee = '.$data['id'].' AND '.TBL_DAYS.'.id_season = '.$config['seasonfilterdefault'].'
GROUP BY '.TBL_DAYS.'.id_compet';
$data['cards'] = runSQL($SELECT);

$SELECT = 'SELECT '.TBL_DAYS.'.id_compet, '.TBL_MATCHS.'.id, '.TBL_PARAMS.'.period, '.TBL_PARAMS.'.period_time, '.TBL_MATCHS.'.extendtime, '.TBL_PARAMS.'.extension, '.TBL_PARAMS.'.extension_time, '.TBL_MATCHS.'.id_referee
FROM '.TBL_DAYS.' 
JOIN '.TBL_MATCHS.' ON '.TBL_MATCHS.'.id_day = '.TBL_DAYS.'.id
JOIN '.TBL_PARAMS.' ON '.TBL_PARAMS.'.id_compet = '.TBL_DAYS.'.id_compet AND '.TBL_PARAMS.'.id_season = '.$config['seasonfilterdefault'].' 
WHERE '.TBL_MATCHS.'.id_referee = '.$data['id'].' AND '.TBL_DAYS.'.id_season = '.$config['seasonfilterdefault'];
$lineup = runSQL($SELECT);

	foreach($lineup as $match) {
	$x=$match['id_compet'];
		$data['lineup'][$x]['id_compet'] = $match['id_compet'];

		$data['lineup'][$x]['titular'] = $data['lineup'][$x]['titular'] + 1;
			if (!$match['subs_min'] && !$match['extendtime']) {
				$data['lineup'][$x]['playtime'] = $data['lineup'][$x]['playtime'] + ($match['period'] * $match['period_time']);
				}
			else if (!$match['subs_min'] && $match['extendtime']) {
				$data['lineup'][$x]['playtime'] = $data['lineup'][$x]['playtime'] + ($match['period'] * $match['period_time']) + ($match['extension'] * $match['extension_time']);
				}
			else {
				$data['lineup'][$x]['playtime'] = $data['lineup'][$x]['playtime'] + $match['subs_min'];
				}


	}
return($data);
}


function StatsCompetitions($data,$myseason) {
global $config, $lang;


$SELECT = 'SELECT count('.TBL_MATCHS.'.id) as nummatchs
FROM '.TBL_MATCHS.'
JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day
WHERE '.TBL_DAYS.'.id_compet = '.$data['id'].' AND '.TBL_DAYS.'.id_season = '.$myseason.'
GROUP BY '.TBL_DAYS.'.id_compet';
$result   = runSQL($SELECT);
$data['matchs'] = $result[0];

$SELECT = 'SELECT count('.TBL_DAYS.'.id) as numdays
FROM '.TBL_DAYS.'
WHERE '.TBL_DAYS.'.id_compet = '.$data['id'].' AND '.TBL_DAYS.'.id_season = '.$myseason.'
GROUP BY '.TBL_DAYS.'.id_compet';
$result   = runSQL($SELECT);
$data['days'] = $result[0];

$SELECT = 'SELECT count('.TBL_MATCHS.'.id) as nummatchs, sum('.TBL_MATCHS.'.spectators) as numspectators
FROM '.TBL_MATCHS.'
JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day
WHERE '.TBL_DAYS.'.id_compet = '.$data['id'].' AND '.TBL_DAYS.'.id_season = '.$myseason.'  AND '.TBL_DAYS.'.date_day < NOW()
GROUP BY '.TBL_DAYS.'.id_compet';
$result   = runSQL($SELECT);
$data['matchsplayed']   = $result[0];

$SELECT = 'SELECT sum('.TBL_MATCHS.'.buts_dom) as goalsdom, sum('.TBL_MATCHS.'.buts_ext) as goalsext
FROM '.TBL_MATCHS.'
JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day
WHERE '.TBL_DAYS.'.id_compet = '.$data['id'].' AND '.TBL_DAYS.'.id_season = '.$myseason.'
GROUP BY '.TBL_DAYS.'.id_compet';
$result   = runSQL($SELECT);
$data['points']   = $result[0];


$data['points']['goals'] = $data['points']['goalsdom'] + $data['points']['goalsext'];
$data['points']['averagegoalsbymatch'] = sprintf("%01.2f", $data['points']['goals'] / $data['matchs']['nummatchs']);
$data['points']['averagedomgoalsbymatch'] = sprintf("%01.2f", $data['points']['goalsdom'] / $data['matchs']['nummatchs']);
$data['points']['averageextgoalsbymatch'] = sprintf("%01.2f", $data['points']['goalsext'] / $data['matchs']['nummatchs']);
$data['points']['averagegoalsbyday'] = sprintf("%01.2f", $data['points']['goals'] / $data['days']['numdays']);
$data['points']['averagedomgoalsbyday'] = sprintf("%01.2f", $data['points']['goalsdom'] / $data['days']['numdays']);
$data['points']['averageextgoalsbyday'] = sprintf("%01.2f", $data['points']['goalsext'] / $data['days']['numdays']);

$data['matchstobeplayed']['nummatchs'] = $data['matchs']['nummatchs'] - $data['matchsplayed']['nummatchs'];

$SELECT = 'SELECT sum('.TBL_CARDS.'.card_red) as redcards, sum('.TBL_CARDS.'.card_yellow) as yellowcards 
FROM '.TBL_CARDS.' 
JOIN '.TBL_MATCHS.' ON '.TBL_MATCHS.'.id = '.TBL_CARDS.'.id_match
JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day
WHERE '.TBL_DAYS.'.id_compet = '.$data['id'].' AND '.TBL_DAYS.'.id_season = '.$myseason.'
GROUP BY '.TBL_DAYS.'.id_compet';
$result   = runSQL($SELECT);
$data['cards'] = $result[0];

$data['cards']['averageredcardsbymatch']    = sprintf("%01.2f", $data['cards']['redcards'] / $data['matchs']['nummatchs']);
$data['cards']['averageyellowcardsbymatch'] = sprintf("%01.2f", $data['cards']['yellowcards'] / $data['matchs']['nummatchs']);
$data['cards']['averageredcardsbyday']     = sprintf("%01.2f", $data['cards']['redcards'] / $data['days']['numdays']);
$data['cards']['averageyellowcardsbyday']  = sprintf("%01.2f", $data['cards']['yellowcards'] / $data['days']['numdays']);

$data['spectators']['numspectators'] = $data['matchsplayed']['numspectators'];
$data['spectators']['averagespectatorsbymatch']   = sprintf("%01.0f", $data['spectators']['numspectators'] / $data['matchs']['nummatchs']);
$data['spectators']['averagespectatorsbyday']     = sprintf("%01.0f", $data['spectators']['numspectators'] / $data['days']['numdays']);

$SELECT = 'SELECT '.TBL_MATCHS.'.id, '.TBL_DAYS.'.day, '.TBL_MATCHS.'.spectators, DOM.shortname AS domclub, '.TBL_MATCHS.'.buts_dom, EXT.shortname AS extclub, '.TBL_MATCHS.'.buts_ext
FROM '.TBL_MATCHS.'
JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day
JOIN '.TBL_CLUBS.' AS DOM ON DOM.id = '.TBL_MATCHS.'.id_candidate_dom
JOIN '.TBL_CLUBS.' AS EXT ON EXT.id = '.TBL_MATCHS.'.id_candidate_ext
WHERE '.TBL_DAYS.'.id_compet = '.$data['id'].' AND '.TBL_DAYS.'.id_season = '.$myseason.'  AND '.TBL_DAYS.'.date_day < NOW()
ORDER BY '.TBL_MATCHS.'.spectators DESC LIMIT 1';
$result   = runSQL($SELECT);
$data['bestaffluence']['spectators'] = $result[0]['spectators'];
$data['bestaffluence']['domclub'] = $result[0]['domclub'];
$data['bestaffluence']['buts_dom'] = $result[0]['buts_dom'];
$data['bestaffluence']['buts_ext'] = $result[0]['buts_ext'];
$data['bestaffluence']['extclub'] = $result[0]['extclub'];
$data['bestaffluence']['id_match'] = $result[0]['id'];
$data['bestaffluence']['day'] = $result[0]['day'];

$SELECT = 'SELECT '.TBL_MATCHS.'.id, '.TBL_DAYS.'.day, '.TBL_MATCHS.'.spectators, DOM.shortname AS domclub, '.TBL_MATCHS.'.buts_dom, EXT.shortname AS extclub, '.TBL_MATCHS.'.buts_ext, ('.TBL_MATCHS.'.buts_dom - '.TBL_MATCHS.'.buts_ext) AS diff
FROM '.TBL_MATCHS.'
JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day
JOIN '.TBL_CLUBS.' AS DOM ON DOM.id = '.TBL_MATCHS.'.id_candidate_dom
JOIN '.TBL_CLUBS.' AS EXT ON EXT.id = '.TBL_MATCHS.'.id_candidate_ext
WHERE '.TBL_DAYS.'.id_compet = '.$data['id'].' AND '.TBL_DAYS.'.id_season = '.$myseason.'  AND '.TBL_DAYS.'.date_day < NOW()
ORDER BY diff DESC, '.TBL_DAYS.'.date_day DESC LIMIT 1';
$result   = runSQL($SELECT);

$data['bestwindom']['domclub']   = $result[0]['domclub'];
$data['bestwindom']['buts_dom']  = $result[0]['buts_dom'];
$data['bestwindom']['buts_ext']  = $result[0]['buts_ext'];
$data['bestwindom']['extclub']   = $result[0]['extclub'];
$data['bestwindom']['id_match']  = $result[0]['id'];
$data['bestwindom']['day'] = $result[0]['day'];

$SELECT = 'SELECT '.TBL_MATCHS.'.id, '.TBL_DAYS.'.day, '.TBL_MATCHS.'.spectators, DOM.shortname AS domclub, '.TBL_MATCHS.'.buts_dom, EXT.shortname AS extclub, '.TBL_MATCHS.'.buts_ext, ('.TBL_MATCHS.'.buts_ext - '.TBL_MATCHS.'.buts_dom) AS diff
FROM '.TBL_MATCHS.'
JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day
JOIN '.TBL_CLUBS.' AS DOM ON DOM.id = '.TBL_MATCHS.'.id_candidate_dom
JOIN '.TBL_CLUBS.' AS EXT ON EXT.id = '.TBL_MATCHS.'.id_candidate_ext
WHERE '.TBL_DAYS.'.id_compet = '.$data['id'].' AND '.TBL_DAYS.'.id_season = '.$myseason.'  AND '.TBL_DAYS.'.date_day < NOW()
ORDER BY diff DESC, '.TBL_DAYS.'.date_day DESC LIMIT 1';
$result   = runSQL($SELECT);

$data['bestwinext']['domclub']   = $result[0]['domclub'];
$data['bestwinext']['buts_dom']  = $result[0]['buts_dom'];
$data['bestwinext']['buts_ext']  = $result[0]['buts_ext'];
$data['bestwinext']['extclub']   = $result[0]['extclub'];
$data['bestwinext']['id_match'] = $result[0]['id'];
$data['bestwinext']['day'] = $result[0]['day'];

return($data);
}


function StatsClubs($data,$competitions,$myseason) {
global $config, $lang;


$SELECT = 'SELECT '.TBL_DAYS.'.id_compet, count('.TBL_MATCHS.'.id) as nummatchs
FROM '.TBL_MATCHS.'
JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day
WHERE ('.TBL_MATCHS.'.id_candidate_dom = '.$data['id'].' OR '.TBL_MATCHS.'.id_candidate_ext = '.$data['id'].') AND '.TBL_DAYS.'.id_season = '.$myseason.'
GROUP BY '.TBL_DAYS.'.id_compet';
$results = runSQL($SELECT);

foreach ($results as $result) {
$data['matchs'][$result['id_compet']]['nummatchs'] = $result['nummatchs'];
}

$SELECT = 'SELECT '.TBL_DAYS.'.id_compet, count('.TBL_DAYS.'.id) as numdays
FROM '.TBL_MATCHS.'
JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day
WHERE ('.TBL_MATCHS.'.id_candidate_dom = '.$data['id'].' OR '.TBL_MATCHS.'.id_candidate_ext = '.$data['id'].') AND '.TBL_DAYS.'.id_season = '.$myseason.'
GROUP BY '.TBL_DAYS.'.id_compet';
$results   = runSQL($SELECT);

foreach ($results as $result) {
$data['days'][$result['id_compet']]['numdays'] = $result['numdays'];
}

$SELECT = 'SELECT '.TBL_DAYS.'.id_compet, count('.TBL_MATCHS.'.id) as nummatchs, sum('.TBL_MATCHS.'.spectators) as numspectators
FROM '.TBL_MATCHS.'
JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day
WHERE ('.TBL_MATCHS.'.id_candidate_dom = '.$data['id'].' OR '.TBL_MATCHS.'.id_candidate_ext = '.$data['id'].') AND '.TBL_DAYS.'.id_season = '.$myseason.'  AND '.TBL_DAYS.'.date_day < NOW() AND '.TBL_MATCHS.'.validation = 1
GROUP BY '.TBL_DAYS.'.id_compet';
$results   = runSQL($SELECT);

foreach ($results as $result) {
$data['matchsplayed'][$result['id_compet']]['nummatchs']     = $result['nummatchs'];
$data['matchsplayed'][$result['id_compet']]['numspectators'] = $result['numspectators'];
$data['spectators'][$result['id_compet']]['numspectators']   = $result['numspectators'];
$data['spectators'][$result['id_compet']]['averagespectatorsbymatch']   = sprintf("%01.0f", $result['numspectators'] / $data['matchsplayed'][$result['id_compet']]['nummatchs']);

}

$SELECT = 'SELECT '.TBL_DAYS.'.id_compet, sum('.TBL_MATCHS.'.buts_dom) as goalsdom
FROM '.TBL_MATCHS.'
JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day
WHERE '.TBL_MATCHS.'.id_candidate_dom = '.$data['id'].' AND '.TBL_DAYS.'.id_season = '.$myseason.'
GROUP BY '.TBL_DAYS.'.id_compet';
$results   = runSQL($SELECT);

foreach ($results as $result) {
$data['points'][$result['id_compet']]['goalsdom'] = $result['goalsdom'];
$data['points'][$result['id_compet']]['averagedomgoalsbymatch'] = sprintf("%01.2f", $result['goalsdom'] / $data['matchsplayed'][$result['id_compet']]['nummatchs']);
}

$SELECT = 'SELECT '.TBL_DAYS.'.id_compet, sum('.TBL_MATCHS.'.buts_ext) as goalsext
FROM '.TBL_MATCHS.'
JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day
WHERE '.TBL_MATCHS.'.id_candidate_ext = '.$data['id'].' AND '.TBL_DAYS.'.id_season = '.$myseason.'
GROUP BY '.TBL_DAYS.'.id_compet';
$results   = runSQL($SELECT);


foreach ($results as $result) {
$data['points'][$result['id_compet']]['goalsext'] = $result['goalsext'];
$data['points'][$result['id_compet']]['goals']    = $data['points'][$result['id_compet']]['goalsdom'] + $result['goalsext'];
$data['points'][$result['id_compet']]['averagegoalsbymatch']    = sprintf("%01.2f", $data['points'][$result['id_compet']]['goals']  / $data['matchsplayed'][$result['id_compet']]['nummatchs']);

$data['points'][$result['id_compet']]['averageextgoalsbymatch'] = sprintf("%01.2f", $result['goalsext'] / $data['matchsplayed'][$result['id_compet']]['nummatchs']);
$data['matchstobeplayed'][$result['id_compet']]['nummatchs'] = $data['matchs'][$result['id_compet']]['nummatchs'] - $data['matchsplayed'][$result['id_compet']]['nummatchs'];
}



foreach ($competitions as $competition) {




	$SELECT = 'SELECT '.TBL_DAYS.'.id_compet, sum('.TBL_CARDS.'.card_red) as redcards, sum('.TBL_CARDS.'.card_yellow) as yellowcards 
	FROM '.TBL_CARDS.' 
	JOIN '.TBL_MATCHS.' ON '.TBL_MATCHS.'.id = '.TBL_CARDS.'.id_match
	JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day
	WHERE '.TBL_CARDS.'.id_club = '.$data['id'].' AND '.TBL_DAYS.'.id_season = '.$myseason.' AND '.TBL_DAYS.'.id_compet = '.$competition['id'].'
	GROUP BY '.TBL_DAYS.'.id_compet';
	$results   = runSQL($SELECT);


	foreach ($results as $result) {
	$data['cards'][$result['id_compet']]['redcards']    = $result['redcards'];
	$data['cards'][$result['id_compet']]['yellowcards'] = $result['yellowcards'];
	$data['cards'][$result['id_compet']]['averageredcardsbymatch']    = sprintf("%01.2f", $result['redcards'] / $data['matchsplayed'][$result['id_compet']]['nummatchs']);
	$data['cards'][$result['id_compet']]['averageyellowcardsbymatch'] = sprintf("%01.2f", $result['yellowcards'] / $data['matchsplayed'][$result['id_compet']]['nummatchs']);
	}
	
	
	$SELECT = 'SELECT '.TBL_DAYS.'.id_compet, '.TBL_MATCHS.'.id, '.TBL_DAYS.'.day, '.TBL_MATCHS.'.spectators, DOM.shortname AS domclub, '.TBL_MATCHS.'.buts_dom, EXT.shortname AS extclub, '.TBL_MATCHS.'.buts_ext
	FROM '.TBL_MATCHS.'
	JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day
	JOIN '.TBL_CLUBS.' AS DOM ON DOM.id = '.TBL_MATCHS.'.id_candidate_dom
	JOIN '.TBL_CLUBS.' AS EXT ON EXT.id = '.TBL_MATCHS.'.id_candidate_ext
	WHERE '.TBL_MATCHS.'.id_candidate_dom = '.$data['id'].' AND '.TBL_DAYS.'.id_season = '.$myseason.'  AND '.TBL_DAYS.'.date_day < NOW() AND '.TBL_DAYS.'.id_compet = '.$competition['id'].'
	ORDER BY '.TBL_MATCHS.'.spectators DESC LIMIT 1';
	$results   = runSQL($SELECT);
	
	foreach ($results as $result) {
	$data['bestaffluence'][$result['id_compet']]['spectators'] = $result['spectators'];
	$data['bestaffluence'][$result['id_compet']]['domclub']    = $result['domclub'];
	$data['bestaffluence'][$result['id_compet']]['buts_dom']   = $result['buts_dom'];
	$data['bestaffluence'][$result['id_compet']]['buts_ext']   = $result['buts_ext'];
	$data['bestaffluence'][$result['id_compet']]['extclub']    = $result['extclub'];
	$data['bestaffluence'][$result['id_compet']]['id_match']   = $result['id'];
	$data['bestaffluence'][$result['id_compet']]['day']        = $result['day'];
	}
	
	$SELECT = 'SELECT '.TBL_DAYS.'.id_compet, '.TBL_MATCHS.'.id, '.TBL_DAYS.'.day, '.TBL_MATCHS.'.spectators, DOM.shortname AS domclub, '.TBL_MATCHS.'.buts_dom, EXT.shortname AS extclub, '.TBL_MATCHS.'.buts_ext, ('.TBL_MATCHS.'.buts_dom - '.TBL_MATCHS.'.buts_ext) AS diff
	FROM '.TBL_MATCHS.'
	JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day
	JOIN '.TBL_CLUBS.' AS DOM ON DOM.id = '.TBL_MATCHS.'.id_candidate_dom
	JOIN '.TBL_CLUBS.' AS EXT ON EXT.id = '.TBL_MATCHS.'.id_candidate_ext
	WHERE '.TBL_MATCHS.'.id_candidate_dom = '.$data['id'].' AND '.TBL_DAYS.'.id_season = '.$myseason.'  AND '.TBL_DAYS.'.date_day < NOW() AND '.TBL_DAYS.'.id_compet = '.$competition['id'].'
	ORDER BY diff DESC, '.TBL_DAYS.'.date_day DESC LIMIT 1';
	$results   = runSQL($SELECT);

	
	
	foreach ($results as $result) {
	$data['bestwindom'][$result['id_compet']]['domclub']   = $result['domclub'];
	$data['bestwindom'][$result['id_compet']]['buts_dom']  = $result['buts_dom'];
	$data['bestwindom'][$result['id_compet']]['buts_ext']  = $result['buts_ext'];
	$data['bestwindom'][$result['id_compet']]['extclub']   = $result['extclub'];
	$data['bestwindom'][$result['id_compet']]['id_match']  = $result['id'];
	$data['bestwindom'][$result['id_compet']]['day']       = $result['day'];
	$data['bestwindom'][$result['id_compet']]['diff']      = $result['diff'];
	}
	
	$SELECT = 'SELECT '.TBL_DAYS.'.id_compet, '.TBL_MATCHS.'.id, '.TBL_DAYS.'.day, '.TBL_MATCHS.'.spectators, DOM.shortname AS domclub, '.TBL_MATCHS.'.buts_dom, EXT.shortname AS extclub, '.TBL_MATCHS.'.buts_ext, ('.TBL_MATCHS.'.buts_ext - '.TBL_MATCHS.'.buts_dom) AS diff
	FROM '.TBL_MATCHS.'
	JOIN '.TBL_DAYS.' ON '.TBL_DAYS.'.id = '.TBL_MATCHS.'.id_day
	JOIN '.TBL_CLUBS.' AS DOM ON DOM.id = '.TBL_MATCHS.'.id_candidate_dom
	JOIN '.TBL_CLUBS.' AS EXT ON EXT.id = '.TBL_MATCHS.'.id_candidate_ext
	WHERE '.TBL_MATCHS.'.id_candidate_ext = '.$data['id'].' AND '.TBL_DAYS.'.id_season = '.$myseason.'  AND '.TBL_DAYS.'.date_day < NOW() AND '.TBL_DAYS.'.id_compet = '.$competition['id'].'
	ORDER BY diff DESC LIMIT 1';
	$results   = runSQL($SELECT);
	
	foreach ($results as $result) {
	$data['bestwinext'][$result['id_compet']]['domclub']   = $result['domclub'];
	$data['bestwinext'][$result['id_compet']]['buts_dom']  = $result['buts_dom'];
	$data['bestwinext'][$result['id_compet']]['buts_ext']  = $result['buts_ext'];
	$data['bestwinext'][$result['id_compet']]['extclub']   = $result['extclub'];
	$data['bestwinext'][$result['id_compet']]['id_match']  = $result['id'];
	$data['bestwinext'][$result['id_compet']]['day']       = $result['day'];
	$data['bestwinext'][$result['id_compet']]['diff']      = $result['diff'];
	}
}//END foreach
return($data);
}

?>
