<?php
 
function agenciesGraph() {
        $topX = 15;
$query = "SELECT SUM(value) as val, agencyName FROM `contractnotice` WHERE (YEAR(contractStart) >= $startYear) AND childCN = 0
GROUP BY agencyName ORDER BY val DESC limit $topX";
$result = mysql_query($query);
$agencies = Array();
$values = Array();
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
  $agencies[] = $row['agencyName'];
  $values[] = doubleval($row["val"]);
}
mysql_free_result($result);
 
$query = "SELECT sum(a.val) as value, count(1) as count from (SELECT SUM(value) as val, agencyName FROM `contractnotice` WHERE (YEAR(contractStart) >= $startYear) AND childCN = 0
GROUP BY agencyName ORDER BY val DESC LIMIT 18446744073709551610 OFFSET $topX) as a";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
  $agencies[] = $row['count'] . " other agencies";
  $values[] = doubleval($row[0]);
}
mysql_free_result($result);
 
};
 
function agencySuppliersGraph($agency) {
        $agency = "AusAid";
$topX = 15;
$query = "SELECT SUM(value) as val, supplierName FROM `contractnotice` WHERE (YEAR(contractStart) >= $startYear) AND childCN = 0 AND agencyName = '$agency'
GROUP BY lower(supplierName) ORDER BY val DESC limit $topX";
$result = mysql_query($query);
$suppliers = Array();
$values = Array();
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
  $suppliers[] = ucsmart($row['supplierName']);
  $values[] = doubleval($row["val"]);
}
mysql_free_result($result);
 
$query = "SELECT sum(a.val) as value, count(1) as count from (SELECT SUM(value) as val, supplierName FROM `contractnotice` WHERE (YEAR(contractStart) >= $startYear) AND childCN = 0 and agencyName = '$agency'
GROUP BY lower(supplierName) ORDER BY val DESC LIMIT 18446744073709551610 OFFSET $topX) as a";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
if ($row['count'] > 0) {
  $suppliers[] = $row['count'] . " other suppliers";
  $values[] = doubleval($row[0]);
}
}
mysql_free_result($result);
}
 
function CnCGraph() {
$query = "select procurementMethod, count(1) as count, SUM(value) as val, MONTH(contractStart) as month, YEAR(contractStart) as year from `contractnotice`
where $agencyQ $supplierQ $standardQ group by procurementMethod,year,month order by procurementMethod,year,month";
$result = mysql_query($query);
$methods = Array("Direct","Open","Select");
$dates = Array();
$methodCountsP = Array();
$methodCounts = Array();
$maxValue = 0;
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
  setlocale(LC_MONETARY, 'en_US');
  if ($row['val'] > $maxValue) $maxValue = $row['val'];
  $date = date( 'F ', mktime(0, 0, 0, $row["month"]) ). $row["year"];
  if (array_search($date,$dates) === false ) {
    $dates[$row["year"]*100 + $row["month"]] = $date;
    ksort($dates);
  }
  $methodCountsP[$row["procurementMethod"]][$date] = $row["count"];
 
}
foreach ($methods as $method) {
    foreach($dates as $date) {
        if ($methodCountsP[$method][$date] > 0) $methodCounts[$method][] = $methodCountsP[$method][$date];
        else $methodCounts[$method][] = 0;
    }
}
$dates = array_values($dates);
$totalRecords = array_sum_all($methodCounts);
mysql_free_result($result);
 
 
function formatCallback($aVal) {
  global $totalRecords;
  return percent($aVal, $totalRecords) . "%";
}
$attributes = Array();
$attributeNames = Array(
  "Consultancies",
  "Confidentialities"
);
$query = "SELECT 'consultancy', count(1) FROM `contractnotice` WHERE $agencyQ $supplierQ consultancy='Yes' AND childCN = 0;";
$result = mysql_query($query);
$row = mysql_fetch_array($result, MYSQL_BOTH);
$attributes[0] = $row[1];
$query = "SELECT 'confidentiality', count(1) FROM `contractnotice` WHERE $agencyQ $supplierQ (confidentialityContract='Yes' OR confidentialityOutputs='Yes') AND childCN = 0;";
$result = mysql_query($query);
$row = mysql_fetch_array($result, MYSQL_BOTH);
$attributes[1] = $row[1];
mysql_free_result($result);
        
}
function ContractPublishedGraph() {
        $query = "SELECT YEAR(publishDate), MONTH(publishDate),
SUM(value) as val, count(1) as count FROM `contractnotice`
WHERE (YEAR(publishDate) >= 2008)
AND childCN = 0
GROUP BY MONTH(publishDate), YEAR(publishDate) 
ORDER BY YEAR(publishDate), MONTH(publishDate)";
 
$result = mysql_query($query);
$dates = Array();
$values = Array();
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
setlocale(LC_MONETARY, 'en_US');
$value = number_format(doubleval($row["val"]),2);
$month_name = date( 'F', mktime(0, 0, 0, $row[1]) );
        $dates[] = $month_name." {$row[0]}";
        $counts[] = doubleval($row["count"]);
        $values[] = doubleval($row["val"]);
}
mysql_free_result($result);
 
}
function ContractStartingGraph() {
        $query = "SELECT YEAR(contractStart), MONTH(contractStart),
SUM(value) as val, count(1) as count FROM `contractnotice`
WHERE (YEAR(contractStart) >= 2008)
AND childCN = 0
GROUP BY MONTH(contractStart), YEAR(contractStart) 
ORDER BY YEAR(contractStart), MONTH(contractStart)";
 
$result = mysql_query($query);
$dates = Array();
$values = Array();
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
setlocale(LC_MONETARY, 'en_US');
$value = number_format(doubleval($row["val"]),2);
$month_name = date( 'F', mktime(0, 0, 0, $row[1]) );
        $dates[] = $month_name." {$row[0]}";
        $counts[] = doubleval($row["count"]);
        $values[] = doubleval($row["val"]);
}
mysql_free_result($result);
}
function MethodCountGraph() {
        $query = "select procurementMethod, count(1) as count, SUM(value) as value, MONTH(contractStart) as month, YEAR(contractStart) as year from `contractnotice`
where $agencyQ $supplierQ $standardQ group by procurementMethod,year,month order by procurementMethod,year,month";
$result = mysql_query($query);
$methods = Array("Direct","Open","Select");
$dates = Array();
$methodCountsP = Array();
$methodCounts = Array();
$maxValue = 0;
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
  setlocale(LC_MONETARY, 'en_US');
  if ($row['value'] > $maxValue) $maxValue = $row['value'];
  $date = date( 'F ', mktime(0, 0, 0, $row["month"]) ). $row["year"];
  if (array_search($date,$dates) === false ) {
    $dates[$row["year"]*100 + $row["month"]] = $date;
    ksort($dates);
  }
  $methodCountsP[$row["procurementMethod"]][$date] = $row["count"];
 
}
foreach ($methods as $method) {
    foreach($dates as $date) {
        if ($methodCountsP[$method][$date] > 0) $methodCounts[$method][] = $methodCountsP[$method][$date];
        else $methodCounts[$method][] = 0;
    }
}
$dates = array_values($dates);
$totalRecords = array_sum_all($methodCounts);
mysql_free_result($result);
}
function MethodValueGraph() {
        $query = "select procurementMethod, SUM(value) as value, MONTH(contractStart) as month, YEAR(contractStart) as year from `contractnotice`
where $agencyQ $supplierQ $standardQ group by procurementMethod,year,month order by procurementMethod,year,month";
$result = mysql_query($query);
$methods = Array("Direct","Open","Select");
$dates = Array();
$methodValuesP = Array();
$methodValues = Array();
$maxValue = 0;
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
  setlocale(LC_MONETARY, 'en_US');
  if ($row['value'] > $maxValue) $maxValue = $row['value'];
  $date = date( 'F ', mktime(0, 0, 0, $row["month"]) ). $row["year"];
  if (array_search($date,$dates) === false ) {
    $dates[$row["year"]*100 + $row["month"]] = $date;
    ksort($dates);
  }
  $methodValuesP[$row["procurementMethod"]][$date] = $row["value"];
 
}
foreach ($methods as $method) {
    foreach($dates as $date) {
        if ($methodValuesP[$method][$date] > 0) $methodValues[$method][] = $methodValuesP[$method][$date];
        else $methodValues[$method][] = 0;
    }
}
$dates = array_values($dates);
$totalRecords = array_sum_all($methodValues);
mysql_free_result($result);
 
}
function SuppliersGraph() {
$topX = 10;
$query = "SELECT value, supplierName
FROM `contractnotice` WHERE (YEAR(contractStart) >= 2009) AND childCN = 0
GROUP BY supplierABN ORDER BY value DESC limit $topX";
$result = mysql_query($query);
$suppliers = Array();
$values = Array();
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
  setlocale(LC_MONETARY, 'en_US');
 // $value = number_format(doubleval($row["value"]) , 2);
  $suppliers[] = ucsmart($row[1]);
  $values[] = doubleval($row["value"]);
}
mysql_free_result($result);
 
$query = "SELECT sum(a.value) as val, supplierCountry from (SELECT value, supplierName, supplierCountry
FROM `contractnotice` WHERE (YEAR(contractStart) >= 2009) AND childCN = 0
GROUP BY supplierName ORDER BY value LIMIT 18446744073709551610 OFFSET $topX) as a group by supplierCountry order by val DESC limit 5 offset 1";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
  $suppliers[] = "Other suppliers in ".ucsmart($row["supplierCountry"]);
  $values[] = doubleval($row[0]);
}
 
mysql_free_result($result);
 
$query = "SELECT sum(a.value) as val, TRUNCATE(supplierPostcode,-2) as postcode from (SELECT value, supplierName, supplierPostcode, supplierCountry
FROM `contractnotice` WHERE (YEAR(contractStart) >= 2009) AND childCN = 0 
GROUP BY supplierName ORDER BY value LIMIT 18446744073709551610 OFFSET $topX) as a
WHERE (supplierCountry LIKE 'Australia' OR supplierCountry LIKE 'AUSTRALIA') AND supplierPostcode < 10000
group by TRUNCATE(supplierPostcode,-2)
order by val DESC";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
    if ($row['postcode'][0] == 2 && $row['postcode'][1] == 6) $ACTvalue += $row[0];
    else if ($row['postcode'][0