1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | <?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>"; ?> |