|
|
|
|
|
|
|
|
";
//echo "2_".$defaultmatchtypeid."
";
if($defaultseasonid != 0 && $defaultmatchtypeid != 0)
{
$get_players_sql="SELECT P.PlayerID AS id,
P.PlayerLastName AS lastname,
P.PlayerFirstName AS firstname,
P.PlayerPublish AS publish,
P.PlayerNumber AS number,
COUNT( G.GoalPlayerID ) AS goals
FROM tplss_seasons S
LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$defaultseasonid'
LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
LEFT OUTER JOIN tplss_goals G ON G.GoalPlayerID = S.SeasonPlayerID AND G.GoalMatchID = M.MatchID AND G.GoalOwn = '0'
WHERE P.PlayerID != ''
GROUP BY id
ORDER BY id";
$get_players = mysqli_query($con,$get_players_sql);
$get_yellows_sql="SELECT
P.PlayerID AS id,
COUNT(Y.YellowCardPlayerID) AS yellows
FROM tplss_seasons S
LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$defaultseasonid'
LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
LEFT OUTER JOIN tplss_yellowcards Y ON Y.YellowCardPlayerID = S.SeasonPlayerID AND Y.YellowCardMatchID = M.MatchID
WHERE P.PlayerID != ''
GROUP BY id
ORDER BY id";
$get_yellows = mysqli_query($con,$get_yellows_sql);
$get_reds_sql=" SELECT
P.PlayerID AS id,
COUNT(R.RedCardPlayerID) AS reds
FROM tplss_seasons S
LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$defaultseasonid'
LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
LEFT OUTER JOIN tplss_redcards R ON R.RedCardPlayerID = S.SeasonPlayerID AND R.RedCardMatchID = M.MatchID
WHERE P.PlayerID != ''
GROUP BY id
ORDER BY id";
$get_reds = mysqli_query($con,$get_reds_sql);
$get_apps_sql=" SELECT
P.PlayerID AS id,
COUNT(A.AppearancePlayerID) AS apps
FROM tplss_seasons S
LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$defaultseasonid'
LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
LEFT OUTER JOIN tplss_appearances A ON A.AppearancePlayerID = S.SeasonPlayerID AND A.AppearanceMatchID = M.MatchID
WHERE P.PlayerID != ''
GROUP BY id
ORDER BY id";
$get_apps = mysqli_query($con,$get_apps_sql);
$get_ins_sql=" SELECT
P.PlayerID AS id,
COUNT(SU.SubstitutionPlayerIDIn) AS ins
FROM tplss_seasons S
LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$defaultseasonid'
LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
LEFT OUTER JOIN tplss_substitutions SU ON SU.SubstitutionPlayerIDIn = S.SeasonPlayerID AND SU.SubstitutionMatchID = M.MatchID
WHERE P.PlayerID != ''
GROUP BY id
ORDER BY id";
$get_ins = mysqli_query($con,$get_apps_sql);
}
//
//'Kaikki'-vaihtoehto on valittu kausista, mutta ei ottelutyypeistä
//
elseif($defaultseasonid == 0 && $defaultmatchtypeid != 0)
{
$get_players_sql="SELECT P.PlayerID AS id,
P.PlayerLastName AS lastname,
P.PlayerFirstName AS firstname,
P.PlayerPublish AS publish,
P.PlayerNumber AS number,
COUNT( G.GoalPlayerID ) AS goals
FROM tplss_seasons S
LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID
LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
LEFT OUTER JOIN tplss_goals G ON G.GoalPlayerID = S.SeasonPlayerID AND G.GoalMatchID = M.MatchID AND G.GoalOwn = '0'
WHERE P.PlayerID != ''
GROUP BY id
ORDER BY id";
$get_players = mysqli_query($con,$get_apps_sql);
$get_yellows_sql="SELECT
P.PlayerID AS id,
COUNT(Y.YellowCardPlayerID) AS yellows
FROM tplss_seasons S
LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID
LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
LEFT OUTER JOIN tplss_yellowcards Y ON Y.YellowCardPlayerID = S.SeasonPlayerID AND Y.YellowCardMatchID = M.MatchID
WHERE P.PlayerID != ''
GROUP BY id
ORDER BY id";
$get_yellows = mysqli_query($con,$get_yellows_sql);
$get_reds_sql=" SELECT
P.PlayerID AS id,
COUNT(R.RedCardPlayerID) AS reds
FROM tplss_seasons S
LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID
LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
LEFT OUTER JOIN tplss_redcards R ON R.RedCardPlayerID = S.SeasonPlayerID AND R.RedCardMatchID = M.MatchID
WHERE P.PlayerID != ''
GROUP BY id
ORDER BY id";
$get_reds = mysqli_query($con,$get_reds_sql);
$get_apps_sql ="
SELECT
P.PlayerID AS id,
COUNT(A.AppearancePlayerID) AS apps
FROM tplss_seasons S
LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID
LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
LEFT OUTER JOIN tplss_appearances A ON A.AppearancePlayerID = S.SeasonPlayerID AND A.AppearanceMatchID = M.MatchID
WHERE P.PlayerID != ''
GROUP BY id
ORDER BY id
";
$get_apps = mysqli_query($con,$get_apps_sql);
$get_ins_sql = "
SELECT
P.PlayerID AS id,
COUNT(SU.SubstitutionPlayerIDIn) AS ins
FROM tplss_seasons S
LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID
LEFT OUTER JOIN tplss_matches M ON M.MatchSeasonID = S.SeasonID AND M.MatchTypeID = '$defaultmatchtypeid'
LEFT OUTER JOIN tplss_substitutions SU ON SU.SubstitutionPlayerIDIn = S.SeasonPlayerID AND SU.SubstitutionMatchID = M.MatchID
WHERE P.PlayerID != ''
GROUP BY id
ORDER BY id
";
$get_ins = mysqli_query($con,$get_ins_sql);
}
//
//'Kaikki'-vaihtoehto on valittu ottelutyypeistä, mutta ei kausista
//
elseif($defaultseasonid != 0 && $defaultmatchtypeid == 0)
{
$get_players_sql = "
SELECT P.PlayerID AS id,
P.PlayerLastName AS lastname,
P.PlayerFirstName AS firstname,
P.PlayerPublish AS publish,
P.PlayerNumber AS number,
COUNT( G.GoalPlayerID ) AS goals
FROM tplss_seasons S
LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$defaultseasonid'
LEFT OUTER JOIN tplss_goals G ON G.GoalPlayerID = S.SeasonPlayerID AND G.GoalOwn = '0' AND G.GoalSeasonID = '$defaultseasonid'
WHERE P.PlayerID != ''
GROUP BY id
ORDER BY id
";
$get_players = mysqli_query($con,$get_players_sql);
$get_yellows_sql ="
SELECT
P.PlayerID AS id,
COUNT(Y.YellowCardPlayerID) AS yellows
FROM tplss_seasons S
LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$defaultseasonid'
LEFT OUTER JOIN tplss_yellowcards Y ON Y.YellowCardPlayerID = S.SeasonPlayerID AND Y.YellowCardSeasonID = '$defaultseasonid'
WHERE P.PlayerID != ''
GROUP BY id
ORDER BY id
";
$get_yellows = mysqli_query($con,$get_yellows_sql);
$get_reds_sql = "
SELECT
P.PlayerID AS id,
COUNT(R.RedCardPlayerID) AS reds
FROM tplss_seasons S
LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$defaultseasonid'
LEFT OUTER JOIN tplss_redcards R ON R.RedCardPlayerID = S.SeasonPlayerID AND R.RedCardSeasonID = '$defaultseasonid'
WHERE P.PlayerID != ''
GROUP BY id
ORDER BY id
";
$get_reds = mysqli_query($con,$get_reds_sql);
$get_apps_sql = "
SELECT
P.PlayerID AS id,
COUNT(A.AppearancePlayerID) AS apps
FROM tplss_seasons S
LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$defaultseasonid'
LEFT OUTER JOIN tplss_appearances A ON A.AppearancePlayerID = S.SeasonPlayerID AND A.AppearanceSeasonID = '$defaultseasonid'
WHERE P.PlayerID != ''
GROUP BY id
ORDER BY id
";
$get_apps = mysqli_query($con,$get_apps_sql);
$get_ins_sql = "
SELECT
P.PlayerID AS id,
COUNT(SU.SubstitutionPlayerIDIn) AS ins
FROM tplss_seasons S
LEFT OUTER JOIN tplss_players P ON P.PlayerID = S.SeasonPlayerID AND S.SeasonID = '$defaultseasonid'
LEFT OUTER JOIN tplss_substitutions SU ON SU.SubstitutionPlayerIDIn = S.SeasonPlayerID AND SU.SubstitutionSeasonID = '$defaultseasonid'
WHERE P.PlayerID != ''
GROUP BY id
ORDER BY id
";
$get_ins = mysqli_query($con,$get_ins_sql);
}
//
//'Kaikki' on valittu kausista ja ottelutyypeistä
//
elseif($defaultseasonid == 0 && $defaultmatchtypeid == 0)
{
$get_players_sql ="
SELECT P.PlayerID AS id,
P.PlayerLastName AS lastname,
P.PlayerFirstName AS firstname,
P.PlayerPublish AS publish,
P.PlayerNumber AS number,
COUNT( G.GoalPlayerID ) AS goals
FROM tplss_players P
LEFT OUTER JOIN tplss_goals G ON G.GoalPlayerID = P.PlayerID AND G.GoalOwn = '0'
WHERE P.PlayerID != ''
GROUP BY id
ORDER BY id
";
$get_players = mysqli_query($con,$get_players_sql);
$get_yellows_sql ="
SELECT P.PlayerID AS id, COUNT( Y.YellowCardPlayerID ) AS yellows
FROM tplss_players P
LEFT OUTER JOIN tplss_yellowcards Y ON Y.YellowCardPlayerID = P.PlayerID
WHERE P.PlayerID != ''
GROUP BY id
ORDER BY id
";
$get_yellows = mysqli_query($con,$get_yellows_sql);
$get_reds_sql = "
SELECT P.PlayerID AS id, COUNT( R.RedCardPlayerID ) AS reds
FROM tplss_players P
LEFT OUTER JOIN tplss_redcards R ON R.RedCardPlayerID = P.PlayerID
WHERE P.PlayerID != ''
GROUP BY id
ORDER BY id
";
$get_reds = mysqli_query($con,$get_reds_sql);
$get_apps_sql = "
SELECT P.PlayerID AS id, COUNT( A.AppearancePlayerID ) AS apps
FROM tplss_players P
LEFT OUTER JOIN tplss_appearances A ON A.AppearancePlayerID = P.PlayerID
WHERE P.PlayerID != ''
GROUP BY id
ORDER BY id
";
$get_apps = mysqli_query($con,$get_apps_sql);
$get_ins_sql = "
SELECT P.PlayerID AS id, COUNT( SU.SubstitutionPlayerIDIn ) AS ins
FROM tplss_players P
LEFT OUTER JOIN tplss_substitutions SU ON SU.SubstitutionPlayerIDIn = P.PlayerID
WHERE P.PlayerID != ''
GROUP BY id
ORDER BY id
";
$get_ins = mysqli_query($con,$get_ins_sql);
}
//echo $get_players_sql;
//
//Laitetaan id, nimi ja numero taulukoihin myöhempää lajittelua varten
//
$i=0;
while($data = mysqli_fetch_array($get_players))
{
$idt[$i] = $data['id'];
//
//Tarkastetaan miten nimi tehdään
//
if($data['lastname'] == '')
{
$names[$i] = $data['firstname'];
}
else
{
$names[$i] = $data['lastname'] . ', ' . $data['firstname'];
}
$publish[$i] = $data['publish'];
$numbers[$i] = $data['number'];
$goals[$i] = $data['goals'];
$i++;
}
//
//Pelaajien määrä muuttujaan ja vapautetaan muisti
//
$qty = mysqli_num_rows($get_players);
//mysql_free_result($get_players);
//
//Laitetaan saadut keltaiset taulukkoon myöhempää lajittelua varten
//
$i=0;
while($data = mysqli_fetch_array($get_yellows))
{
$yellows[$i] = $data['yellows'];
$i++;
}
//mysql_free_result($get_yellows);
//
//Laitetaan saadut punaiset taulukkoon myöhempää lajittelua varten
//
$i=0;
while($data = mysqli_fetch_array($get_reds))
{
$reds[$i] = $data['reds'];
$i++;
}
//mysql_free_result($get_reds);
//
//Laitetaan aloituskerrat taulukkoon myöhempää lajittelua varten
//
$i=0;
while($data = mysqli_fetch_array($get_apps))
{
$apps[$i] = $data['apps'];
$i++;
}
//mysql_free_result($get_apps);
//
//Laitetaan sisään tulot taulukkoon myöhempää lajittelua varten
//
$i=0;
while($data = mysqli_fetch_array($get_ins))
{
$ins[$i] = $data['ins'];
$i++;
}
//mysql_free_result($get_ins);
if($qty > 0)
{
//
//Järjestellään multisortin avulla
//
switch($sort)
{
case 'name':
array_multisort($names, SORT_ASC, SORT_STRING, $idt, $numbers, $goals, $apps, $yellows, $reds, $ins, $publish);
break;
case 'number':
array_multisort($numbers, SORT_ASC, SORT_NUMERIC, $names, SORT_ASC, SORT_STRING, $idt, $goals, $apps, $yellows, $reds, $ins, $publish);
break;
case 'apps':
array_multisort($apps, SORT_DESC, SORT_NUMERIC, $ins, SORT_DESC, SORT_NUMERIC, $names, SORT_ASC, SORT_STRING, $idt, $goals, $numbers, $yellows, $reds, $publish);
break;
case 'ins':
array_multisort($ins, SORT_DESC, SORT_NUMERIC, $apps, SORT_DESC, SORT_NUMERIC, $names, SORT_ASC, SORT_STRING, $idt, $goals, $numbers, $yellows, $reds, $publish);
break;
case 'goals':
array_multisort($goals, SORT_DESC, SORT_NUMERIC, $names, SORT_ASC, SORT_STRING, $idt, $numbers, $apps, $yellows, $reds, $ins, $publish);
break;
case 'yellows':
array_multisort($yellows, SORT_DESC, SORT_NUMERIC, $names, SORT_ASC, SORT_STRING, $idt, $goals, $apps, $numbers, $reds, $ins, $publish);
break;
case 'reds':
array_multisort($reds, SORT_DESC, SORT_NUMERIC, $names, SORT_ASC, SORT_STRING, $idt, $goals, $apps, $yellows, $numbers, $ins, $publish);
break;
}
$i=0;
$j=1;
while($i < $qty)
{
//
//Tarkastetaan jakojäännöksellä, kumpi väreistä valitaan solun taustaksi
//
if($j % 2 == 0)
$temp = "$bg1";
else
$temp = "$bg2";
echo"
| ";
//
//Tehdään järjestysnumero siten, että tarkistetaan onko edellinen arvo sama kuin nykyinen
//ja tulostetaan jär.numero, jos ovat eri
//
switch($sort)
{
case 'apps':
if($i > 0)
{
if($apps[$i] == $apps[$i-1])
{
echo' ';
$j++;
}
else
{
echo"$j.";
$j++;
}
}
else
{
echo"$j.";
$j++;
}
break;
case 'ins':
if($i > 0)
{
if($ins[$i] == $ins[$i-1])
{
echo' ';
$j++;
}
else
{
echo"$j.";
$j++;
}
}
else
{
echo"$j.";
$j++;
}
break;
case 'goals':
if($i > 0)
{
if($goals[$i] == $goals[$i-1])
{
echo' ';
$j++;
}
else
{
echo"$j.";
$j++;
}
}
else
{
echo"$j.";
$j++;
}
break;
case 'yellows':
if($i > 0)
{
if($yellows[$i] == $yellows[$i-1])
{
echo' ';
$j++;
}
else
{
echo"$j.";
$j++;
}
}
else
{
echo"$j.";
$j++;
}
break;
case 'reds':
if($i > 0)
{
if($reds[$i] == $reds[$i-1])
{
echo' ';
$j++;
}
else
{
echo"$j.";
$j++;
}
}
else
{
echo"$j.";
$j++;
}
break;
case 'number':
echo"#$numbers[$i]";
$j++;
break;
case 'name':
echo"$j.";
$j++;
break;
}
echo" |
";
//
//Tarkistetaan, onko pelaaja julkaistu
//
if($publish[$i] == 1)
{
echo "$names[$i]";
}
else
{
echo "$names[$i]";
}
echo" |
";
//
//Vahvennetaan tilasto tietyn ominaisuuden kohdalta
//
if($sort == 'apps')
echo'';
echo"$apps[$i]";
if($sort == 'apps')
echo'';
echo" |
";
if($sort == 'ins')
echo'';
echo"$ins[$i]";
if($sort == 'ins')
echo'';
echo" |
";
if($sort == 'goals')
echo'';
echo"$goals[$i]";
if($sort == 'goals')
echo'';
echo" |
";
if($sort == 'yellows')
echo'';
echo"$yellows[$i]";
if($sort == 'yellows')
echo'';
echo" |
";
if($sort == 'reds')
echo'';
echo"$reds[$i]";
if($sort == 'reds')
echo'';
echo" |
\n";
$i++;
}
?>
|
|
|
|
|
|
|
|
No players added to selected season..
|
';
}
?>
|
M.MatchGoalsOpponent,1,0)) AS won,
SUM(IF(M.MatchGoals < M.MatchGoalsOpponent,1,0)) AS lost,
SUM(IF(M.MatchGoals = M.MatchGoalsOpponent,1,0)) AS draw,
COUNT(M.MatchID) AS games,
DATE_FORMAT(M.MatchDateTime, '$how_to_print') AS time
FROM tplss_matches M, tplss_appearancesmngr A, tplss_players P
WHERE
M.MatchID = A.AppearanceMatchID AND
A.AppearancePlayerID = P.PlayerID AND
P.PlayerIsManager = 1
" . ( $defaultseasonid ? " AND M.MatchSeasonID = '{$defaultseasonid}' " : " " ) . "
GROUP BY
P.PlayerID
";
$rst = mysqli_query($con,$rst_sql);
//$rst = mysql_query($query);
?>
|
|
|
Games
|
Won
|
Drawn
|
Lost
|
Win%
|
".($i++).". |
".$r['lastname'].", ".$r['firstname']." |
".$r['games']." |
".$r['won']." |
".$r['draw']." |
".$r['lost']." |
".( $r['games'] > 0 ? round($r['won'] / $r['games'] * 100,2) : 0 )." |
";
}
?>
|
|