#!/usr/bin/php5

<?php

date_default_timezone_set('America/Denver');

$days = $_REQUEST["days"];
if (!$days) {
	$days = 1;
}
if ($days > 30) {
	$days = 30;
}


$username = "test";
$password = "555";
$server = "96.2.123.136:3306";
$database = "test";

$query = "

SELECT `s`.`server_name`, `s`.`ip_address`, `sg`.`stats_game_id`, `sg`.`game_type`, `sg`.`duration_seconds`, `sg`.`level_name`, `sg`.`insertion_date`, `st`.`team_name`, `st`.`color_hex`, `st`.`team_score`, `st`.`result`, `sp`.`player_name`, `sp`.`is_robot`, `sp`.`points`, `sp`.`kill_count`, `sp`.`death_count`, `sp`.`suicide_count`
FROM { OJ `test`.`stats_team` AS `st` LEFT OUTER JOIN `test`.`stats_player` AS `sp` ON `st`.`stats_team_id` = `sp`.`stats_team_id` }, `test`.`server` AS `s`, `test`.`stats_game` AS `sg`
WHERE `s`.`server_id` = `sg`.`server_id` AND `sg`.`stats_game_id` = `st`.`stats_game_id`
and sg.insertion_date between sysdate() - interval $days day and sysdate()
ORDER BY `sg`.`level_name` ASC, `sg`.`insertion_date` DESC, `st`.`team_name` ASC, `sp`.`player_name` ASC

";
$query_2 = "

select s.server_name, s.ip_address,
sg.stats_game_id, sg.game_type, sg.duration_seconds, sg.level_name, sg.insertion_date,
st.team_name, st.color_hex, st.team_score, st.result,
sp.player_name, sp.is_robot, sp.points, sp.kill_count, sp.death_count, sp.suicide_count
from server s, stats_game sg, stats_team st, stats_player sp
where s.server_id = sg.server_id
and sg.stats_game_id = st.stats_game_id
and sg.stats_game_id = sp.stats_game_id
and st.stats_team_id = sp.stats_team_id
and sg.insertion_date between sysdate() - interval $days day and sysdate()
order by sg.insertion_date desc, sg.level_name, st.team_name, sp.player_name

";

$query_w_shots = "

select s.server_name, s.ip_address,
sg.stats_game_id, sg.game_type, sg.duration_seconds, sg.level_name, sg.insertion_date,
st.team_name, st.color_hex, st.team_score, st.result,
sp.player_name, sp.is_robot, sp.points, sp.kill_count, sp.death_count, sp.suicide_count,
sps.weapon, sps.shots, sps.shots_struck
from server s, stats_game sg, stats_team st, stats_player sp, stats_player_shots sps
where s.server_id = sg.server_id
and sg.stats_game_id = st.stats_game_id
and sg.stats_game_id = sp.stats_game_id
and st.stats_team_id = sp.stats_team_id
and sp.stats_player_id = sps.stats_player_id
and sg.insertion_date between sysdate() - interval $days day and sysdate()
order by sg.insertion_date desc, sg.level_name, st.team_name, sp.player_name, sps.weapon

";

function connect_to_db() {
    global $username;
    global $password;
    global $server;
    global $database;
    
    $connection  = mysql_pconnect($server, $username, $password) or die("Could not connect: \n" . mysql_error());
    //mysql_query("SET NAMES latin1") or die("Cannot SET NAMES latin1\n" . mysql_error());
    mysql_select_db($database, $connection) or die("Cannot select db $dbname: \n" . mysql_error());
    return $connection;
}


function get_database_data() {
    global $query;
    
    $master_array = array();
    
	$last_stats_game_id = "";
	$last_team_name = "";
	$last_player_name = "";
	
	$stats_game_id_changed = false;
	$team_name_changed = false;
	$player_name_changed = false;
    
    $connection = connect_to_db();
    
    $statement = mysql_query($query, $connection) or die('could not execute query: ' . $query);

	// build up data structure
    while ($row = mysql_fetch_array($statement)) {
		$server_name = $row["server_name"];
		$ip_address = $row["ip_address"];
		$stats_game_id = $row["stats_game_id"];
		$game_type = $row["game_type"];
		$duration_seconds = $row["duration_seconds"];
		$level_name = $row["level_name"];
		$insertion_date = $row["insertion_date"];
		$team_name = $row["team_name"];
		$color_hex = $row["color_hex"];
		$team_score = $row["team_score"];
		$result = $row["result"];
		$player_name = $row["player_name"];
		$is_robot = $row["is_robot"];
		//$result = $row["result"];
		$points = $row["points"];
		$kill_count = $row["kill_count"];
		$death_count = $row["death_count"];
		$suicide_count = $row["suicide_count"];
		$weapon = $row["weapon"];
		$shots = $row["shots"];
		$shots_struck = $row["shots_struck"];
		
		
		$current_game_array = array(
			"server_name" => $server_name,
			"ip_address" => $ip_address,
			"game_type" => $game_type,
			"duration_seconds" => $duration_seconds,
			"level_name" => $level_name,
			"insertion_date" => $insertion_date,
			"team_array" => array()
		);
		
		$current_team_array = array(
			"team_name" => $team_name,
			"color_hex" => $color_hex,
			"team_score" => $team_score,
			"result" => $result,
			"player_array" => array()
		);
		
		$current_player_array = array(
			"player_name" => $player_name,
			"is_robot" => $is_robot,
			"points" => $points,
			"kill_count" => $kill_count,
			"death_count" => $death_count,
			"suicide_count" => $suicide_count,
			"shots_array" => array()	
		);
		
		$current_shots_array = array(
			"weapon" => $weapon, 
			"shots" => $shots, 
			"shots_struck" => $shots_struck
		);
		
		$stats_game_id_changed = false;
		$team_name_changed = false;
		$player_name_changed = false;
		
		if ($stats_game_id != $last_stats_game_id) {
			$stats_game_id_changed = true;
			$team_name_changed = true;
			$player_name_changed = true;
		}
		
		if ($last_team_name != $team_name) {
			$team_name_changed = true;
			$player_name_changed = true;
		}
		
		if ($last_player_name != $player_name) {
			$player_name_changed = true;
		}
		
		
		if ($stats_game_id_changed) {
			$master_array[$stats_game_id] = $current_game_array;
		}
		
		if ($team_name_changed) {
			$master_array[$stats_game_id]["team_array"][$team_name] = $current_team_array;
		}
		
		if ($player_name_changed) {
			$master_array["$stats_game_id"]["team_array"]["$team_name"]["player_array"]["$player_name"] = $current_player_array; 
		}
		
		$master_array["$stats_game_id"]["team_array"]["$team_name"]["player_array"]["$player_name"]["shots_array"]["$weapon"] = $current_shots_array;
		
		$last_stats_game_id = $stats_game_id;
		$last_team_name = $team_name;
		$last_player_name = $player_name;
    }
    
    mysql_close($connection);
    
    return $master_array;
}


function build_report_html($master_array) {
	global $days;
	
	print("");
	
	print("
		<html>
		<head>
			<title>Bitfighter Game Reports</title>
			<link rel=\"stylesheet\" href=\"game_summary.css\" type=\"text/css\" />
		</head>
		<body>
		<h1>Bitfighter Game Reports in the last $days days</h1>
		<p>Select game reports for a different time period:</p>
		<form id=\"daterange\" method=\"POST\" action=\"build_bitfighter_report.php\">
			<select name=\"days\">
				<option value=\"1\">1 day</option>
				<option value=\"7\">7 days</option>
				<option value=\"30\">30 days</option>
			</select>
			<input type=\"submit\" />
		</form>
		
		");
	
	foreach($master_array as $game) {
		//print_r($game);
		$server_name = $game["server_name"];
		$ip_address = $game["ip_address"];
		$game_type = $game["game_type"];
		$duration_seconds = $game["duration_seconds"];
		$level_name = $game["level_name"];
		$insertion_date = $game["insertion_date"];
		$team_array = $game["team_array"];
		
		print("
			<div class=\"game\">
			<h2>Game ending at $insertion_date</h2>
			<table class=\"gameinfo\"><tbody>
			<tr><td><b>Server</b></td><td>$server_name</td></tr>
			<tr><td><b>IP Adress</b></td><td>$ip_address</td></tr>
			<tr><td><b>Type</b></td><td>$game_type</td></tr>
			<tr><td><b>Level Name</b></td><td>$level_name</td></tr>
			<tr><td><b>Duration</b></td><td>$duration_seconds seconds</td></tr>
			</table></tbody>
		");
		
		foreach($team_array as $team) {
			$team_name = $team["team_name"];
			$color_hex = $team["color_hex"];
			$team_score = $team["team_score"];
			$result = $team["result"];
			$player_array = $team["player_array"];
			
			print(
				"<div class=\"team\">
				<h3>Team: <span style=\"color: $color_hex;\">$team_name</span></h3>
				<table class=\"teaminfo\"><tbody>
				<tr><td><b>Score</b></td><td>$team_score</td></tr>
				<tr><td><b>Result</b></td><td>$result</td></tr>
				</table></tbody><br/>
			");
			
			print("<b>Players on this team:</b>");
			print("<table class=\"player\"><thead><tr>");
			print("<td>Player name</td>");
			print("<td>Points</td>");
			print("<td>Kills</td>");
			print("<td>Deaths</td>");
			print("<td>Suicides</td>");
			//print("<td>Shots</td>");
			print("</tr></thead>");
			print("<tbody>");
			
			$row_increment = 0;
			
			foreach($player_array as $player) {
				$player_name = $player["player_name"];
				$is_robot = $player["is_robot"];
				$points = $player["points"];
				$kill_count = $player["kill_count"];
				$death_count = $player["death_count"];
				$suicide_count = $player["suicide_count"];
				$shots_array = $player["shots_array"];
				
				$row_increment++;
				if ($row_increment % 2 == 0) {
					$row_class = "even";
				} else {
					$row_class = "odd";
				}
				
				print("<tr class=\"$row_class\">");
				print("<td>$player_name");
				if ($is_robot == 1) {
					print(" (robot)");
				}
				print("</td>");
				print("<td>$points</td>");
				print("<td>$kill_count</td>");
				print("<td>$death_count</td>");
				print("<td>$suicide_count</td>");
				
				/*
				print("<td><table class=\"shots\"><tbody><thead><tr>");
				print("<td>Weapon</td><td>Shots</td><td>Hits</td>");
				print("</tr></thead>");
				print("<tbody>");
			
				foreach($shots_array as $weapon_item) {
					$weapon = $weapon_item["weapon"];
					$shots = $weapon_item["shots"];
					$shots_struck = $weapon_item["shots_struck"];
					
					print("<tr><td>$weapon</td><td>$shots</td><td>$shots_struck</td></tr>");
				}
				
				print("</tbody></table>");
				
				print("</td>");
				print("</tr>");
				*/
			}
			
			print("</tbody></table>");
			print("</div>");
		}
		
		print("</div>");
	}
	
	print("</body></html>");
}


# Start script

if (!extension_loaded('mysql')) {
	print "You are missing the mysql php extension\n";
	exit;
}

$master_array = get_database_data();

build_report_html($master_array);

?>