--- a/lib/graphs.inc.php +++ b/lib/graphs.inc.php @@ -1,272 +1,552 @@ <?php +$includedFlot = false; + +function includeFlot() { + if (!$includedFlot) { + echo ' <!--[if lte IE 8]><script language="javascript" type="text/javascript" src="lib/flot/excanvas.min.js"></script><![endif]--> + + <script language="javascript" type="text/javascript" src="lib/flot/jquery.flot.js"></script> + <script language="javascript" type="text/javascript" src="lib/flot/jquery.flot.pie.js"></script> + <script language="javascript" type="text/javascript" src="lib/flot/jquery.flot.selection.js"></script> + <script language="javascript" type="text/javascript" src="lib/flot/jquery.flot.stack.js"></script> + +'; + $includedFlot = true; + } +} + +function CNDistributionGraph() { + global $conn; + includeFlot(); + ?> + <center><div id="cndist" style="width:900px;height:550px"></div></center> + <script type="text/javascript"> + var placeholder = $("#cndist"); + $(function () { + + var d1 = []; + var d2 = []; + <?php + $query = 'select cnid, count(*) from + (select ("CNID"::integer - MOD("CNID"::integer,100)) as cnid + from contractnotice where "CNID"::integer < 999999 + and "CNID" not like \'%-A%\' + and "parentCN" is null) as a group by cnid order by cnid'; + $query = $conn->prepare($query); + $query->execute(); + $errors = $conn->errorInfo(); + if ($errors[2] != "") { + echo("Export terminated, db error" . print_r($errors, true)); + return Array(); + } + + foreach ($query->fetchAll() as $delta) { + + echo "d1.push([ " . intval($delta['cnid']) . ", " . intval($delta['count']) . "]); \n"; + }; + $query = 'select cnid, count(*) from (select ("CNID"::integer - MOD("CNID"::integer,100)) as cnid + from contractnotice where "CNID" not like \'%-A%\' and "parentCN" is not null) + as a group by cnid order by cnid'; + $query = $conn->prepare($query); + $query->execute(); + $errors = $conn->errorInfo(); + if ($errors[2] != "") { + echo("Export terminated, db error" . print_r($errors, true)); + return Array(); + } + + foreach ($query->fetchAll() as $delta) { + + echo "d2.push([ " . intval($delta['cnid']) . ", " . intval($delta['count']) . "]); \n"; + }; + ?> + + var data = [ + { + data: d1, + series: { + lines: { show: true }, + points: { show: true } + }, + bars: { show: true } + }, + { + data: d2, + series: { + lines: { show: true }, + points: { show: true } + }, + bars: { show: true } + }] + ]; + var options = + { + series: { + stack: true, + }, + grid: { hoverable: true, clickable: true, labelMargin: 17 }, + selection: { mode: "x" } + }; + + placeholder.bind("plotselected", function (event, ranges) { + plot = $.plot(placeholder, data, + $.extend(true, {}, options, { + xaxis: { min: ranges.xaxis.from, max: ranges.xaxis.to } + })); + }); + var previousPoint = null; + placeholder.bind("plothover", function (event, pos, item) { + $("#x").text(pos.x.toFixed(2)); + $("#y").text(pos.y.toFixed(2)); + + if (item) { + if (previousPoint != item.dataIndex) { + previousPoint = item.dataIndex; + + $("#tooltip").remove(); + var x = item.datapoint[0].toFixed(2), + y = item.datapoint[1].toFixed(2); + + showTooltip(item.pageX, item.pageY, + item.series.label + " of " + x + " = " + y); + } + } + else { + $("#tooltip").remove(); + previousPoint = null; + } + }); + + var plot = $.plot(placeholder, data, + options); + }); + + function showTooltip(x, y, contents) { + $('<div id="tooltip">' + contents + '</div>').css( { + position: 'absolute', + display: 'none', + top: y + 5, + left: x + 5, + border: '1px solid #fdd', + padding: '2px', + 'background-color': '#fee', + opacity: 0.80 + }).appendTo("body").fadeIn(200); + } + + </script> + <?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); - -}; + + global $conn; + includeFlot(); + $query = 'SELECT SUM("value") as val, MAX(contractnotice."agencyName") as agencyname FROM contractnotice join agency_nametoabn on contractnotice."agencyName"=agency_nametoabn."agencyName" WHERE "childCN" is null +GROUP BY abn ORDER BY SUM("value") DESC'; + $query = $conn->prepare($query); + $query->execute(); + $errors = $conn->errorInfo(); + if ($errors[2] != "") { + echo("Export terminated, db error" . print_r($errors, true)); + return Array(); + } + ?> + <script type="text/javascript"> + $(function () { + // data + var data = [ + <?php + foreach ($query->fetchAll() as $row) { + echo '{ label: "' . $row['agencyname'] . '", data: ' . doubleval($row["val"]) . '},'; + } + ?> + ]; + // GRAPH 7 + $.plot($("#graph7"), data, + { + series: { + pie: { + show: true, + radius: 1, + tilt: 0.75, + label: { + show: true, + radius: 1, + formatter: function(label, series){ + return '<div style="font-size:8pt;text-align:center;padding:2px;color:white;">'+label+': '+Math.round(series.percent)+'%</div>'; + }, + background: { + opacity: 0.5, + color: '#000' + } + }, + combine: { + color: '#999', + threshold: 0.012 + } + } + }, + legend: { + show: false + } + }); + }); + </script> + <div id="graph7" style="width:900px;height:550px"></div> + + <?php +} + +; 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); + + global $conn; + $agency = "AusAid"; + $topX = 15; + $query = 'SELECT SUM(value) as val, "supplierName" FROM contractnotice WHERE (extract ("YEAR" from "contractStart") >= :startYear) AND "childCN" is null AND "agencyName" = :agency +GROUP BY lower(supplierName) ORDER BY val DESC limit $topX'; + $query = $conn->prepare($query); + $query->bindParam(":startYear",$startYear); + $query->bindParam(":agency",$agency); + $query->execute(); + databaseError($conn->errorInfo()); + $suppliers = Array(); + $values = Array(); + foreach ($query->fetchAll() as $row) { + $suppliers[] = ucsmart($row['supplierName']); + $values[] = doubleval($row["val"]); + } + + + $query = 'SELECT sum(a.val) as value, count(1) as count from (SELECT SUM(value) as val, "supplierName" FROM contractnotice WHERE (extract ("YEAR" from "contractStart") >= :startYear) AND "childCN" is null and "agencyName" = :agency +GROUP BY lower("supplierName") ORDER BY val DESC LIMIT 18446744073709551610 OFFSET $topX) as a'; + $query = $conn->prepare($query); + $query->bindParam(":startYear",$startYear); + $query->bindParam(":agency",$agency); + $query->execute(); + databaseError($conn->errorInfo()); + foreach ($query->fetchAll() as $row) { + if ($row['count'] > 0) { + $suppliers[] = $row['count'] . " other suppliers"; + $values[] = doubleval($row[0]); + } + } } function CnCGraph() { -$query = "select procurementMethod, count(1) as count, SUM(value) as val, MONTH(contractStart) as month, YEAR(contractStart) as year from `contractnotice` + + global $conn; + $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'; + $query = $conn->prepare($query); + $query->execute(); + databaseError($conn->errorInfo()); + + $methods = Array("Direct", "Open", "Select"); + $dates = Array(); + $methodCountsP = Array(); + $methodCounts = Array(); + $maxValue = 0; + foreach ($query->fetchAll() as $row) { + 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); + + 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" is null;'; + $result = $conn->query($query); + $row = $result->fetch(PDO::FETCH_ASSOC); + $attributes[0] = $row[1]; + $query = 'SELECT \'confidentiality\', count(1) FROM contractnotice WHERE $agencyQ $supplierQ (confidentialityContract=\'Yes\' OR confidentialityOutputs=\'Yes\') AND "childCN" is null;'; + $result = $conn->query($query); + $row = $result->fetch(PDO::FETCH_ASSOC); + $attributes[1] = $row[1]; +} + +function ContractPublishedGraph() { + + global $conn; + $query = 'SELECT YEAR(publishDate), MONTH(publishDate), +SUM(value) as val, count(1) as count FROM contractnotice +WHERE (YEAR(publishDate) >= 2008) +AND "childCN" is null +GROUP BY MONTH(publishDate), YEAR(publishDate) +ORDER BY YEAR(publishDate), MONTH(publishDate)'; + + $query = $conn->prepare($query); + $query->execute(); + databaseError($conn->errorInfo()); + + $dates = Array(); + $values = Array(); + foreach ($query->fetchAll() as $row) { + 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"]); + } +} + +function ContractStartingGraph() { + + global $conn; + $query = 'SELECT YEAR(contractStart), MONTH(contractStart), +SUM(value) as val, count(1) as count FROM contractnotice +WHERE (YEAR(contractStart) >= 2008) +AND "childCN" is null +GROUP BY MONTH(contractStart), YEAR(contractStart) +ORDER BY YEAR(contractStart), MONTH(contractStart)'; + + $query = $conn->prepare($query); + $query->execute(); + databaseError($conn->errorInfo()); + + $dates = Array(); + $values = Array(); + foreach ($query->fetchAll() as $row) { + 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"]); + } +} + +function MethodCountGraph() { + + global $conn; + $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'; + $methods = Array("Direct", "Open", "Select"); + $dates = Array(); + $methodCountsP = Array(); + $methodCounts = Array(); + $maxValue = 0; + $query = $conn->prepare($query); + $query->execute(); + databaseError($conn->errorInfo()); + + foreach ($query->fetchAll() as $row) { + 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); +} + +function MethodValueGraph() { + + global $conn; + $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(); -$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); - -} + $methods = Array("Direct", "Open", "Select"); + $dates = Array(); + $methodValuesP = Array(); + $methodValues = Array(); + $maxValue = 0; + $query = $conn->prepare($query); + $query->execute(); + databaseError($conn->errorInfo()); + + foreach ($query->fetchAll() as $row) { + 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); +} + 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); - -} - + + global $conn; + includeFlot(); + $topX = 10; + $suppliers = Array(); + $values = Array(); + + + $query = 'SELECT SUM("value") as value, MAX("supplierName") as supplierName, ( + case when "supplierABN" != 0 THEN "supplierABN"::text ELSE "supplierName" END) as supplierID +FROM contractnotice +WHERE "childCN" is null +GROUP BY supplierID +ORDER BY value DESC +LIMIT ' . $topX; + $query = $conn->prepare($query); + $query->execute(); + databaseError($conn->errorInfo()); + foreach ($query->fetchAll() as $row) { + setlocale(LC_MONETARY, 'en_US'); + // $value = number_format(doubleval($row["value"]) , 2); + $suppliers[] = ucsmart($row[1]); + $values[] = doubleval($row["value"]); + } + + $query = 'SELECT sum(a.svalue) as val, suppliercountry from (SELECT sum("value") as svalue, max("supplierCountry") as suppliercountry +FROM contractnotice WHERE "childCN" is null and "supplierCountry" NOT ILIKE \'Australia\' +GROUP BY "supplierName" ORDER BY svalue LIMIT 18446744073 OFFSET 10) as a group by suppliercountry order by val DESC limit 10 '; + $query = $conn->prepare($query); + $query->execute(); + databaseError($conn->errorInfo()); + + foreach ($query->fetchAll() as $row) { + $suppliers[] = "Other suppliers in " . ucsmart($row["suppliercountry"]); + $values[] = doubleval($row[0]); + } + + + $query = 'SELECT sum(a.value) as val, substring( +supplierpostcode from 0 for 2) as postcode from (SELECT sum(value) as value, max("supplierPostcode") as supplierpostcode, max("supplierCountry") as suppliercountry +FROM contractnotice WHERE "childCN" is null +GROUP BY "supplierABN" ORDER BY sum(value) LIMIT 1844674 OFFSET 10) as a +WHERE (suppliercountry ILIKE \'Australia\') +group by substring( +supplierpostcode from 0 for 2) +order by val DESC;'; + $query = $conn->prepare($query); + $query->execute(); + databaseError($conn->errorInfo()); + + foreach ($query->fetchAll() as $row) { + 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]; + } + $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); + ?> + <script type="text/javascript"> + $(function () { + // data + var data = [ + <?php + foreach ($suppliers as $key => $supplier) { + echo '{ label: "' . $supplier . '", data: ' . doubleval($values[$key]) . '},'; + } + ?> + ]; + // GRAPH 7 + $.plot($("#graph7"), data, + { + series: { + pie: { + show: true, + radius: 1, + tilt: 0.75, + label: { + show: true, + radius: 1, + formatter: function(label, series){ + return '<div style="font-size:8pt;text-align:center;padding:2px;color:white;">'+label+': '+Math.round(series.percent)+'%</div>'; + }, + bac