<?php
include 'Library/sqlserverconnect.php';

	$query = "SELECT type, sum(quantity) as quantity, avg(price) as price
	FROM `test`
	WHERE station = 'P2 Monkey Island' OR station = 'P2  Monkey Island' OR station = 'HW The Cliff' OR station = 'C4C The Ook Emporium' group by type
	order by (sum(quantity)*avg(price))";
	$result = mysql_query($query) or die('<br />Query failed: ' . mysql_error().'<br /><p>'.$query.'</p>');
	$total;
	echo "<table border = 1 Width = 100%>\n";
	echo "\t<tr>\n";
	echo "\t\t<th>Item</th>\n";
	echo "\t\t<th>Quantity</th>\n";
	echo "\t\t<th>Total Price</th>\n";
	echo "\t\t<th>Total Volume</th>\n";
	echo "\t</tr>\n";
	$totalvolume;
	while ($line = mysql_fetch_array($result, MYSQL_ASSOC))
	{
		$query2 = "select volume from invtypes where typename like '".mysql_real_escape_string($line['type'])."'";
		$result2 = mysql_query($query2) or die('<br />Query failed: ' . mysql_error().'<br /><p>'.$query2.'</p>');
		$line2 = mysql_fetch_array($result2, MYSQL_ASSOC);
		echo '<tr>';
		echo '<td>'.$line['type'].'</td>';
		echo '<td>'.$line['quantity'].'</td>';
		echo '<td>'.$line['price'] * $line['quantity'].'</td>';
		echo '<td>'.$line2['volume'] * $line['quantity'].'</td>';
		echo '</tr>';
		$total += $line['price'] * $line['quantity'];
		$totalvolume += $line2['volume'] * $line['quantity'];
	}
	echo '</table>';
	$rawtotal = $total;
	$fee = $total*.0155;
	$total = $total-$total*.0155;

	echo 'Total Sales: '.number_format($rawtotal,2,'.',',').'<br/>';
	echo 'Total Fees: '.number_format($fee,2,'.',',').'<br/>';
	echo 'Total m3: '.$totalvolume.'<br/>';

	echo 'Estimated Cost: '.number_format($rawtotal / 1.2,2,'.',',') .'<br/>';
	$total -= ($rawtotal / 1.2);
	echo 'Estimated profit: '.number_format($total,2,'.',',').'<br/>';

?>