--- a/lib/graphs.inc.php +++ b/lib/graphs.inc.php @@ -1,1 +1,272 @@ - +<?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] == 2 || $row['postcode'][0] == 1) $NSWvalue += $row[0]; + else if ($row['postcode'][0] == 3 || $row['postcode'][0] == 8) $Vicvalue += $row[0]; + else if ($row['postcode'][0] == 4 || $row['postcode'][0] == 9) $QLDvalue += $row[0]; + else if ($row['postcode'][0] == 5) $SAvalue += $row[0]; + else if ($row['postcode'][0] == 6) $WAvalue += $row[0]; + else if ($row['postcode'][0] == 7) $Tasvalue += $row[0]; + else if ($row['postcode'][0] == 0) $NTvalue += $row[0]; +} +mysql_free_result($result); +$suppliers[] = "Other suppliers in Australia - ACT"; +$values[] = doubleval($ACTvalue); +$suppliers[] = "Other suppliers in Australia - NSW"; +$values[] = doubleval($NSWvalue); +$suppliers[] = "Other suppliers in Australia - Victoria"; +$values[] = doubleval($Vicvalue); +$suppliers[] = "Other suppliers in Australia - Queensland"; +$values[] = doubleval($QLDvalue); +$suppliers[] = "Other suppliers in Australia - NT"; +$values[] = doubleval($NTvalue); +$suppliers[] = "Other suppliers in Australia - West Australia"; +$values[] = doubleval($WAvalue); +$suppliers[] = "Other suppliers in Australia - South Australia"; +$values[] = doubleval($SAvalue); +$suppliers[] = "Other suppliers in Australia - Tasmania"; +$values[] = doubleval($Tasvalue); + +} + +?>