<?php
	$numbers = array();

	$con=mysqli_connect("localhost","udontgetmyname","justgetrandom","asdf");

	//Check the connection
	if (mysqli_connect_errno()) {
		echo "Failed to connect to MySQL: " . mysqli_connect_error();
	}

	//Closing numbers
	$sql="SELECT * FROM greq_tickets WHERE status = '2' OR status = '3'";

	$result = mysqli_query($con, $sql);

	while($row = mysqli_fetch_assoc($result)) {
		$name = $row['status_extra'];
		if (array_key_exists($name, $numbers)) {
			$numbers[$name] = $numbers[$name] + 1;
		} else {
			$numbers[$name] = 1;
		}
	}

	arsort($numbers);

	echo "<div style='display:inline-block;vertical-align:text-top;margin-right:50px;'>";
	echo "<b><u>Ticket closing numbers:</u></b>";
	echo "<br>";

	echo "<table>";
	foreach ($numbers as $key => $value) {
		echo "<tr>";
		echo "<td style='padding-right:20px;'>" . $key . "</td>";
		echo "<td>" . $value . "</td>";
		echo "</tr>";
	}
	echo "</table>";
	echo "</div>";

	unset($numbers);

	//Writing numbers
	$sql="SELECT * FROM greq_tickets";

	$result = mysqli_query($con, $sql);

	while($row = mysqli_fetch_assoc($result)) {
		$name = $row['author'];
		if (array_key_exists($name, $numbers)) {
			$numbers[$name] = $numbers[$name] + 1;
		} else {
			$numbers[$name] = 1;
		}
	}

	arsort($numbers);

	echo "<div style='display:inline-block;vertical-align:text-top;margin-right:50px;'>";
	echo "<b><u>Ticket writing numbers:</u></b>";
	echo "<br>";

	echo "<table>";
	foreach ($numbers as $key => $value) {
		echo "<tr>";
		echo "<td style='padding-right:20px;'>" . $key . "</td>";
		echo "<td>" . $value . "</td>";
		echo "</tr>";
	}
	echo "</table>";
	echo "</div>";

	unset($numbers);

	//Close time
	date_default_timezone_set('Europe/Berlin');
	$sql="SELECT * FROM greq_tickets WHERE (status = '2' OR status = '3') AND close_date IS NOT NULL";

	$result = mysqli_query($con, $sql);

	echo "<div style='display:inline-block;vertical-align:text-top;margin-right:50px;'>";
	echo "<b><u>Close time:</u></b>";
	echo "<br>";

	echo "<table>";

	$total = 0;
	$amount = 0;
	while ($row = mysqli_fetch_assoc($result)) {
		$id = $row['id'];
		$staff = $row['status_extra'];

		$start = DateTime::createFromFormat('d-m-Y-H-i-s', $row['date']);
		$startString = $start->format('d-m-Y H:i:s');
		$end = DateTime::createFromFormat('d-m-Y-H-i-s', $row['close_date']);
		$endString = $end->format('d-m-Y H:i:s');

		$diff = date("H:i:s", (strtotime($endString) - strtotime($startString)));
		$amount = $amount + 1;
		$total = $total + (strtotime($endString) - strtotime($startString));

		echo "<tr>";
		echo "<td style='padding-right:20px;'>#" . $id . " (" . $staff . ")" . "</td>";
		echo "<td>" . sprintf("%02d", (substr($diff, 0, 2) - 1)) . substr($diff, 2) . "</td>";
		echo "</tr>";
	}
	$average = date("H:i:s", ($total / $amount));

	echo "<tr>";
	echo "<td style='padding-right:20px;'>" . "Average:" . "</td>";
	echo "<td>" . sprintf("%02d", (substr($average, 0, 2) - 1)) . substr($average, 2) . "</td>";
	echo "</tr>";

	echo "</table>";
	echo "</div>";
?>