<?
global $awardset;

//------------------------------------------------
$awardset['TAG_Thaw']['name'] = 'Best Thawer';
$awardset['TAG_Thaw']['image'] = 'TAG_thaw';
$awardset['TAG_Thaw']['category'] = 'Freeze Tag';
$awardset['TAG_Thaw']['sql'][0] = "SELECT {$tp}playerprofile.playerID,
										{$tp}playerprofile.playerName,
										SUM({$tp}eventdata1d.eventValue) AS thaws,
										{$tp}playerprofile.games AS games,
										ROUND(SUM({$tp}eventdata1d.eventValue) / {$tp}playerprofile.games, 2) AS ratio
									FROM 
										{$tp}playerprofile,
										{$tp}eventdata1d
									WHERE 
										eventName='THAW' 
										AND {$tp}playerprofile.games > {$GLOBALS['avg_games']}
										AND {$tp}playerprofile.playerID = {$tp}eventdata1d.playerID
									GROUP BY 
										{$tp}eventdata1d.playerID
									ORDER BY 
										ratio DESC
								  ";
//------------------------------------------------
$awardset['CTF_Assist']['name'] = 'Best Supporter';
$awardset['CTF_Assist']['image'] = 'CTF_Supporter';
$awardset['CTF_Assist']['category'] = 'CTF';
$awardset['CTF_Assist']['sql'][0] = "SELECT {$tp}playerprofile.playerID, 
										{$tp}playerprofile.playerName, 
										ROUND(SUM({$tp}eventdata1d.eventValue) / {$tp}playerprofile.games, 2) AS value 
									 FROM 
										{$tp}playerprofile,
										{$tp}eventdata1d
									 WHERE 
										(eventName='Defend_Carrier' OR 
										 eventName='Defend_Hurt_Carrier' OR 
										 eventName='Flag_Assist_Return' OR 
										 eventName='Flag_Assist_Frag') 
										AND eventCategory='CTF' 
										AND {$tp}playerprofile.games > {$GLOBALS['avg_games']} 
										AND {$tp}playerprofile.playerID = {$tp}eventdata1d.playerID
									 GROUP BY 
										{$tp}eventdata1d.playerID
									 ORDER BY 
										value DESC
									";
//------------------------------------------------
$awardset['CTF_Retriever']['name'] = 'Best Flag Retriever';
$awardset['CTF_Retriever']['image'] = 'CTF_Retriever';
$awardset['CTF_Retriever']['category'] = 'CTF';
$awardset['CTF_Retriever']['sql'][0] = "SELECT {$tp}playerprofile.playerID, 
											{$tp}playerprofile.playerName, 
											ROUND(SUM({$tp}eventdata1d.eventValue) / {$tp}playerprofile.games, 2) AS value 
										FROM 
											{$tp}playerprofile, 
											{$tp}eventdata1d
										WHERE 
											(eventName='Kill_Carrier' OR 
											 eventName='Flag_Return') 
											AND eventCategory='CTF' 
											AND {$tp}playerprofile.games > {$GLOBALS['avg_games']} 
											AND {$tp}playerprofile.playerID = {$tp}eventdata1d.playerID
										GROUP BY 
											{$tp}eventdata1d.playerID
										ORDER BY 
											value DESC
									   ";
//------------------------------------------------
$awardset['CTF_Defend']['name'] = 'Best Defender';
$awardset['CTF_Defend']['image'] = 'CTF_Defend';
$awardset['CTF_Defend']['category'] = 'CTF';
$awardset['CTF_Defend']['sql'][0] = "SELECT {$tp}playerprofile.playerID, 
										{$tp}playerprofile.playerName, 
										ROUND(SUM({$tp}eventdata1d.eventValue) / {$tp}playerprofile.games, 2) AS value 
									 FROM 
										{$tp}playerprofile, 
										{$tp}eventdata1d
									 WHERE 
										(eventName='Defend_Base' OR 
										 eventName='Defend_Flag') 
										AND eventCategory='CTF' 
										AND {$tp}playerprofile.games > {$GLOBALS['avg_games']} 
										AND {$tp}playerprofile.playerID = {$tp}eventdata1d.playerID
									 GROUP BY 
										{$tp}eventdata1d.playerID
									 ORDER BY 
										value DESC
									";
//------------------------------------------------
$awardset['CTF_Flag_Capture']['name'] = 'Best Flag Capper';
$awardset['CTF_Flag_Capture']['image'] = 'CTF_Flag_Capture';
$awardset['CTF_Flag_Capture']['category'] = 'CTF';
$awardset['CTF_Flag_Capture']['sql'][0] = "SELECT {$tp}playerprofile.playerID, 
												{$tp}playerprofile.playerName, 
												ROUND(SUM({$tp}eventdata1d.eventValue) / {$tp}playerprofile.games, 2) AS value 
											FROM 
												{$tp}playerprofile, 
												{$tp}eventdata1d
											WHERE 
												eventName = 'Flag_Capture' 
												AND eventCategory = 'CTF' 
												AND {$tp}playerprofile.games > {$GLOBALS['avg_games']} 
												AND {$tp}playerprofile.playerID = {$tp}eventdata1d.playerID
											GROUP BY 
												{$tp}eventdata1d.playerID
											ORDER BY 
												value DESC
										  ";
//------------------------------------------------
$awardset['item_quad']['name'] = 'Quad Whore';
$awardset['item_quad']['image'] = 'item_quad';
$awardset['item_quad']['category'] = 'Item';
$awardset['item_quad']['sql'][0] = "SELECT {$tp}playerprofile.playerID, 
										{$tp}playerprofile.playerName, 
										ROUND(SUM({$tp}eventdata1d.eventValue) / {$tp}playerprofile.games, 2) AS value 
									FROM 
										{$tp}playerprofile, 
										{$tp}eventdata1d
									WHERE 
										eventName = 'quad' 
										AND eventCategory = 'item'
										AND {$tp}playerprofile.games > {$GLOBALS['avg_games']} 
										AND {$tp}playerprofile.playerID = {$tp}eventdata1d.playerID
									GROUP BY 
										{$tp}eventdata1d.playerID
									ORDER BY 
										value DESC
								   ";
//------------------------------------------------
$awardset['item_regen']['name'] = 'Regen Romper';
$awardset['item_regen']['image'] = 'item_regen';
$awardset['item_regen']['category'] = 'Item';
$awardset['item_regen']['sql'][0] = "SELECT {$tp}playerprofile.playerID, 
										{$tp}playerprofile.playerName, 
										ROUND(SUM({$tp}eventdata1d.eventValue) / {$tp}playerprofile.games, 2) AS value 
									 FROM 
										{$tp}playerprofile, 
										{$tp}eventdata1d
									 WHERE 
										eventName = 'regen' 
										AND eventCategory = 'item'
										AND {$tp}playerprofile.games > {$GLOBALS['avg_games']} 
										AND {$tp}playerprofile.playerID = {$tp}eventdata1d.playerID
									 GROUP BY 
										{$tp}eventdata1d.playerID
									 ORDER BY 
										value DESC
									";
//------------------------------------------------
$awardset['item_haste']['name'] = 'Haste Hog';
$awardset['item_haste']['image'] = 'item_haste';
$awardset['item_haste']['category'] = 'Item';
$awardset['item_haste']['sql'][0] = "SELECT {$tp}playerprofile.playerID, 
										{$tp}playerprofile.playerName, 
										ROUND(SUM({$tp}eventdata1d.eventValue) / {$tp}playerprofile.games, 2) AS value 
									 FROM 
										{$tp}playerprofile, 
										{$tp}eventdata1d
									 WHERE 
										eventName = 'haste' 
										AND eventCategory = 'item'
										AND {$tp}playerprofile.games > {$GLOBALS['avg_games']} 
										AND {$tp}playerprofile.playerID = {$tp}eventdata1d.playerID
									 GROUP BY 
										{$tp}eventdata1d.playerID
									 ORDER BY 
										value DESC
									";
//------------------------------------------------
$awardset['item_mega']['name'] = 'Mega Whore';
$awardset['item_mega']['image'] = 'item_mega';
$awardset['item_mega']['category'] = 'Item';
$awardset['item_mega']['sql'][0] = "SELECT {$tp}playerprofile.playerID, 
										{$tp}playerprofile.playerName, 
										ROUND(SUM({$tp}eventdata1d.eventValue) / {$tp}playerprofile.games, 2) AS value 
									 FROM 
										{$tp}playerprofile, 
										{$tp}eventdata1d
									 WHERE 
										eventName = 'health_mega' 
										AND eventCategory = 'item'
										AND {$tp}playerprofile.games > {$GLOBALS['avg_games']} 
										AND {$tp}playerprofile.playerID = {$tp}eventdata1d.playerID
									 GROUP BY 
										{$tp}eventdata1d.playerID
									 ORDER BY 
										value DESC
									";
//------------------------------------------------
// Best Killer \\
$awardset['kr']['name'] = 'Best Killer';
$awardset['kr']['image'] = 'player_killer';
$awardset['kr']['category'] = 'Player';
$awardset['kr']['sql'][0] = "SELECT playerID, playerName, kills / (games + {$GLOBALS['avg_games']}) 
                             FROM {$tp}playerprofile 
                             WHERE games > {$GLOBALS['avg_games']} 
                             ORDER BY kills / (games + {$GLOBALS['avg_games']}) DESC";
//------------------------------------------------
// Highest Kill Streak \\
$awardset['ks']['name'] = 'Highest Kill Streak';
$awardset['ks']['image'] = 'player_killstreak';
$awardset['ks']['category'] = 'Player';
$awardset['ks']['sql'][0] = "SELECT playerID, playerName, kill_streak 
                             FROM {$tp}playerprofile 
                             ORDER BY kill_streak DESC";
//------------------------------------------------
// Best Efficiency \\
$awardset['eff']['name'] = 'Best Efficiency';
$awardset['eff']['image'] = 'player_efficiency';
$awardset['eff']['category'] = 'Player';
$awardset['eff']['sql'][0] = "SELECT playerID, playerName, kills / (1 + kills + deaths) 
                              FROM {$tp}playerprofile 
                              WHERE games > {$GLOBALS['avg_games']} 
                              ORDER BY kills / (1 + kills + deaths) DESC";
//------------------------------------------------
// Best Killer With Weapon \\
$awardset['killer__v_weapons']['name'] = 'Best Killer with _v_weapons';
$awardset['killer__v_weapons']['image'] = 'killer__v_weapons';
$awardset['killer__v_weapons']['category'] = 'Carnage';
$awardset['killer__v_weapons']['sql'][0] = "SELECT {$tp}eventdata2d.playerID, 
												{$tp}playerprofile.playerName, 
												SUM({$tp}eventdata2d.eventValue) AS Kills, 
												{$tp}playerprofile.games AS Games, 
												ROUND((SUM({$tp}eventdata2d.eventValue) / {$tp}playerprofile.games), 2) AS kill_ratio 
											FROM 
												{$tp}eventdata2d 
											JOIN 
												{$tp}playerprofile 
												ON {$tp}playerprofile.playerID = {$tp}eventdata2d.playerID 
											WHERE 
												{$tp}playerprofile.games > {$GLOBALS['avg_games']} 
												AND (eventName='_v_weapons') 
												AND eventCategory='kill' 
												AND {$tp}eventdata2d.playerID != {$tp}eventdata2d.player2ID 
											GROUP BY 
												{$tp}eventdata2d.playerID 
											ORDER BY 
												kill_ratio DESC
										  ";


//------------------------------------------------------------------
// Accuracy awards using compiled tables to make it fast
//
$awardset['accuracy_set_table']['sql'][0] = "drop table {$tp}awardsaccuracy";
$awardset['accuracy_set_table']['sql'][1] = "CREATE TABLE {$tp}awardsaccuracy (
													   playerID varchar(100) NOT NULL default ''
													   ,playerName varchar(255) NOT NULL default ''
													   ,weaponID varchar(100) NOT NULL default ''
													   ,games int(10) unsigned default '0'
													   ,hits int(10) unsigned default '0'
													   ,shots int(10) unsigned default '0'
													   ,accuracy float(10,2) unsigned default '0'
													   ,PRIMARY KEY  (playerID,weaponID)
													   ) ENGINE=InnoDB                                    
													  ";
//------------------------------------------------------------------
$awardset['accuracy_v_weapons_populate_table']['sql'][0] = "
    INSERT INTO {$tp}awardsaccuracy
    SELECT 
        ed1.playerID,
        {$tp}playerprofile.playerName,
        ed1.eventName,
        {$tp}playerprofile.games AS games,
        SUM(ed1.eventValue) AS hits,
        SUM(ed2.eventValue) AS shots,
        ROUND(SUM(ed1.eventValue) / SUM(ed2.eventValue) * 100.0, 2) AS accuracy
    FROM 
        {$tp}eventdata2d AS ed1
    JOIN 
        {$tp}playerprofile ON {$tp}playerprofile.playerID = ed1.playerID
    LEFT JOIN 
        {$tp}eventdata2d AS ed2 ON (
            ed1.playerID = ed2.playerID AND 
            ed1.gameID = ed2.gameID AND 
            ed1.round = ed2.round AND 
            ed1.team = ed2.team AND 
            ed1.role = ed2.role AND 
            ed1.eventCategory = ed2.eventCategory AND
            ed1.player2ID = ed2.player2ID AND 
            ed1.team2 = ed2.team2 AND 
            ed1.role2 = ed2.role2
        )
    WHERE 
        {$tp}playerprofile.games > {$avg_games} AND
        ed1.eventName = '_v_weapons_hits' AND
        ed2.eventName = '_v_weapons_shots' AND
        ed1.eventCategory = 'accuracy'
    GROUP BY 
        ed1.playerID, ed1.eventName
    ORDER BY 
        accuracy DESC
";
//------------------------------------------------------------------
$awardset['avg_shots_v_weapons']['sql'][0] = "
    SELECT STD(shots)
    FROM {$tp}awardsaccuracy
    WHERE weaponID = '_v_weapons_hits'
    GROUP BY weaponID
";
//------------------------------------------------------------------
$awardset['accuracy__v_weapons']['name'] = 'Best Accuracy with _v_weapons';
$awardset['accuracy__v_weapons']['image'] = 'accuracy__v_weapons';
$awardset['accuracy__v_weapons']['category'] = 'Accuracy';
$awardset['accuracy__v_weapons']['sql'][0] = "SELECT playerID, playerName, games, hits, shots, accuracy
											 FROM {$tp}awardsaccuracy
											 WHERE weaponID = '_v_weapons_hits'
											 AND shots > ($avg_shots - 1)
											 GROUP BY playerID
											 ORDER BY accuracy DESC
											";
//------------------------------------------------------------------
$awardset['accuracyALLWEAPONS']['name'] = 'Best Overall Accuracy';
$awardset['accuracyALLWEAPONS']['image'] = 'accuracy_ALLWEAPONS';
$awardset['accuracyALLWEAPONS']['category'] = 'Accuracy';
$awardset['accuracyALLWEAPONS']['sql'][0] = "SELECT playerID,
											   playerName,
											   games,
											   SUM(hits) AS hits,
											   SUM(shots) AS shots,
											   ROUND(SUM(hits) / NULLIF(SUM(shots), 0), 2) AS accuracy
											 FROM {$tp}awardsaccuracy
											 GROUP BY playerID
											 ORDER BY accuracy DESC
											";
//------------------------------------------------------------------
