<?php
 
include_once("../common.inc.php");
$year = $_REQUEST['year'];
$query = "SELECT supplierCountry, sum(value) FROM `contractnotice` where childCN = 0 AND (YEAR(contractStart) = $year) AND supplierCountry != 'Australia' GROUP BY supplierCountry ";
 
$result = mysql_query($query);
if (!$result) echo mysql_error().$query;
$countryValues = Array();
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
 $supplierCountry = strtoupper($row['supplierCountry']);
if ($supplierCountry == "MYANMAR") $supplierCountry = "BURMA";
if ($supplierCountry == "RUSSIAN FEDERATION") $supplierCountry = "RUSSIA";
if ($supplierCountry == "TAIWAN, PROVINCE OF CHINA") $supplierCountry = "TAIWAN";
$countryValues[$supplierCountry] = $row['sum(value)'];
}
mysql_free_result($result);
 
$query = "SELECT supplierPostcode, sum(value) FROM `contractnotice` where childCN = 0 AND (YEAR(contractStart) = $year) AND supplierCountry = 'Australia' GROUP BY supplierPostcode ";
 
$result = mysql_query($query);
if (!$result) echo mysql_error().$query;
$postcodeValues = Array();
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
$postcodeValues[$row['supplierPostcode']] = $row['sum(value)'];
}
mysql_free_result($result);
 
 
@mysql_select_db("tme") or die( "Unable to select database tme");
$query = "SELECT un, name
FROM `country_simpl`";
 
$result = mysql_query($query);
if (!$result) echo mysql_error().$query;
$countryUN = Array();
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
$countryUN[strtoupper($row['name'])] = $row['un'];
}
mysql_free_result($result);
 
echo "<table>";
 
// drop from table existing values
//$result = mysql_query('insert into indicator_values ( "variable", "area", "year", "value" ) VALUES (2107, '.$countryUN[strtoupper($countryName)].', '.$year.','.$countryValue.');');
//if (!$result) echo mysql_error().$query;
 
 
foreach ($countryValues as $countryName => $countryValue) {
echo "<tr><td>$countryName<td>".$countryUN[strtoupper($countryName)]."<td>$countryValue</tr>";
$query = 'insert into indicator_values ( `variable`, `area`, `year`, `value` ) VALUES (\'2107\', \''.$countryUN[strtoupper($countryName)].'\', \''.$year.'\',\''.$countryValue.'\');';
$result = mysql_query($query);
if (!$result) echo mysql_error();
}
 
foreach ($postcodeValues as $postcode => $postcodeValue) {
echo "<tr><td>Australia<td>$postcode<td>$postcodeValue</tr>";
$query = 'INSERT into indicator_values ( `variable`, `area`, `year`, `value` ) VALUES (\'2107\', \''.($postcode*1000).'\', \''.$year.'\',\''.$countryValue.'\');';
$result = mysql_query($query);
if (!$result) echo mysql_error();
}
 
echo "</table>";
?>