query("
SELECT
COUNT(*)
FROM
hlstats_Events_ChangeTeam
LEFT JOIN hlstats_Players ON
hlstats_Players.playerId=hlstats_Events_ChangeTeam.playerId
WHERE
clan=$clan
");
list($numteamjoins) = $db->fetch_row();
$result = $db->query("SELECT `code`,`name` FROM hlstats_Roles WHERE game='$game'");
while ($rowdata = $db->fetch_row($result))
{
$code = preg_replace("/[ \r\n\t]+/", '', $rowdata[0]);
$fname[strToLower($code)] = htmlspecialchars($rowdata[1]);
}
$result = $db->query("
SELECT
IFNULL(hlstats_Teams.name, hlstats_Events_ChangeTeam.team) AS name,
COUNT(hlstats_Events_ChangeTeam.id) AS teamcount,
ROUND(COUNT(hlstats_Events_ChangeTeam.id) / IF($numteamjoins = 0, 1, $numteamjoins) * 100, 2) AS percent
FROM
hlstats_Events_ChangeTeam
LEFT JOIN hlstats_Teams ON
hlstats_Events_ChangeTeam.team=hlstats_Teams.code
LEFT JOIN hlstats_Players ON
hlstats_Players.playerId=hlstats_Events_ChangeTeam.playerId
WHERE
clan=$clan
AND hlstats_Teams.game='$game'
AND (hidden <>'1' OR hidden IS NULL)
GROUP BY
hlstats_Events_ChangeTeam.team
ORDER BY
$tblTeams->sort $tblTeams->sortorder,
$tblTeams->sort2 $tblTeams->sortorder
");
$numitems = $db->num_rows($result);
if ($numitems > 0)
{
printSectionTitle('Team Selection *');
$tblTeams->draw($result, $numitems, 95);
?>
query("DROP TABLE IF EXISTS hlstats_Frags_as");
$db->query("
CREATE TEMPORARY TABLE hlstats_Frags_as
(
playerId INT(10),
kills INT(10),
deaths INT(10),
role varchar(128) NOT NULL default ''
)
");
$db->query("
INSERT INTO
hlstats_Frags_as
(
playerId,
kills,
role
)
SELECT
victimId,
killerId,
killerRole
FROM
hlstats_Events_Frags
LEFT JOIN hlstats_Servers ON
hlstats_Servers.serverId=hlstats_Events_Frags.serverId LEFT JOIN hlstats_Players ON
hlstats_Players.playerId = hlstats_Events_Frags.killerId
WHERE
hlstats_Servers.game='$game' AND clan = $clan
");
$db->query("
INSERT INTO
hlstats_Frags_as
(
playerId,
deaths,
role
)
SELECT
killerId,
victimId,
victimRole
FROM
hlstats_Events_Frags
LEFT JOIN
hlstats_Servers
ON
hlstats_Servers.serverId = hlstats_Events_Frags.serverId
LEFT JOIN
hlstats_Players
ON
hlstats_Players.playerId = hlstats_Events_Frags.victimId
WHERE
hlstats_Servers.game='$game' AND clan = $clan
");
$db->query("DROP TABLE IF EXISTS hlstats_Frags_as_res");
$db->query("
CREATE TEMPORARY TABLE hlstats_Frags_as_res
(
killsTotal INT(10),
deathsTotal INT(10),
role varchar(128) NOT NULL default ''
)
");
$db->query("
INSERT INTO
hlstats_Frags_as_res
(
killsTotal,
deathsTotal,
role
)
SELECT
COUNT(hlstats_Frags_as.kills) AS kills,
COUNT(hlstats_Frags_as.deaths) AS deaths,
role
from hlstats_Frags_as GROUP by role
");
$db->query("
SELECT
COUNT(*)
FROM
hlstats_Events_ChangeRole
LEFT JOIN hlstats_Players ON
hlstats_Players.playerId=hlstats_Events_ChangeRole.playerId
WHERE
clan=$clan
");
list($numrolejoins) = $db->fetch_row();
$result = $db->query("
SELECT
IFNULL(hlstats_Roles.name, hlstats_Events_ChangeRole.role) AS name,
IFNULL(hlstats_Roles.code, hlstats_Events_ChangeRole.role) AS code,
COUNT(hlstats_Events_ChangeRole.id) AS rolecount,
ROUND(COUNT(hlstats_Events_ChangeRole.id) / IF($numrolejoins = 0, 1, $numrolejoins) * 100, 2) AS percent,
killsTotal,
deathsTotal,
ROUND(killsTotal/if(deathsTotal=0,1,deathsTotal), 2) AS kpd
FROM
hlstats_Events_ChangeRole
LEFT JOIN
hlstats_Roles
ON
hlstats_Events_ChangeRole.role = hlstats_Roles.code
LEFT JOIN
hlstats_Servers
ON
hlstats_Servers.serverId = hlstats_Events_ChangeRole.serverId
LEFT JOIN
hlstats_Frags_as_res
ON
hlstats_Frags_as_res.role = hlstats_Events_ChangeRole.role
LEFT JOIN
hlstats_Players
ON
hlstats_Players.playerId = hlstats_Events_ChangeRole.playerId
WHERE
hlstats_Servers.game='$game'
AND hlstats_Players.clan=$clan
AND (hidden <>'1' OR hidden IS NULL)
AND hlstats_Roles.game = '$game'
GROUP BY
hlstats_Events_ChangeRole.role
ORDER BY
$tblRoles->sort $tblRoles->sortorder,
$tblRoles->sort2 $tblRoles->sortorder
");
$numitems = $db->num_rows($result);
if ($numitems > 0)
{
printSectionTitle('Role Selection *');
$tblRoles->draw($result, $numitems, 95);
?>