--- a/lib/graphs.inc.php +++ b/lib/graphs.inc.php @@ -6,6 +6,8 @@ <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; @@ -18,11 +20,13 @@ ?> <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" - MOD("CNID",100)) as cnid from contractnotice where "CNID" < 999999) as a group by cnid order by cnid'; +$query = 'select cnid, count(*) from (select ("CNID" - MOD("CNID",100)) as cnid from contractnotice where "CNID" < 999999 and "parentCN" is null) as a group by cnid order by cnid'; $query = $conn->prepare($query); $query->execute(); if (!$query) { @@ -34,23 +38,93 @@ echo "d1.push([ ".intval($delta['cnid']).", ".intval($delta['count'])."]); \n"; }; +$query = 'select cnid, count(*) from (select ("CNID" - MOD("CNID",100)) as cnid from contractnotice where "CNID" < 999999 and "parentCN" is not null) as a group by cnid order by cnid'; +$query = $conn->prepare($query); +$query->execute(); +if (!$query) { + databaseError($conn->errorInfo()); + return Array(); +} + +foreach ($query->fetchAll() as $delta) { + + echo "d2.push([ ".intval($delta['cnid']).", ".intval($delta['count'])."]); \n"; +}; ?> - var placeholder = $("#cndist"); - - var plot = $.plot(placeholder, [ +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 } @@ -59,7 +133,7 @@ global $conn; includeFlot(); -$query = 'SELECT SUM("value") as val, MAX(contractnotice."agencyName") as agencyname FROM contractnotice join agency on contractnotice."agencyName"=agency."agencyName" WHERE "childCN" = 0 +$query = 'SELECT SUM("value") as val, MAX(contractnotice."agencyName") as agencyname FROM contractnotice join agency on contractnotice."agencyName"=agency."agencyName" WHERE "childCN" is null GROUP BY abn ORDER BY SUM("value") DESC'; $query = $conn->prepare($query); $query->execute(); @@ -117,8 +191,8 @@ 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"; +$query = 'SELECT SUM(value) as val, supplierName FROM `contractnotice` WHERE (YEAR(contractStart) >= $startYear) AND "childCN" is null AND agencyName = \'$agency\' +GROUP BY lower(supplierName) ORDER BY val DESC limit $topX'; $result = mysql_query($query); $suppliers = Array(); $values = Array(); @@ -128,8 +202,8 @@ } 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"; +$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" is null 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) { @@ -180,11 +254,11 @@ "Consultancies", "Confidentialities" ); -$query = "SELECT 'consultancy', count(1) FROM `contractnotice` WHERE $agencyQ $supplierQ consultancy='Yes' AND childCN = 0;"; +$query = 'SELECT \'consultancy\', count(1) FROM `contractnotice` WHERE $agencyQ $supplierQ consultancy=\'Yes\' AND "childCN" is null;'; $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;"; +$query = 'SELECT \'confidentiality\', count(1) FROM `contractnotice` WHERE $agencyQ $supplierQ (confidentialityContract=\'Yes\' OR confidentialityOutputs=\'Yes\') AND "childCN" is null;'; $result = mysql_query($query); $row = mysql_fetch_array($result, MYSQL_BOTH); $attributes[1] = $row[1]; @@ -192,12 +266,12 @@ } function ContractPublishedGraph() { - $query = "SELECT YEAR(publishDate), MONTH(publishDate), + $query = 'SELECT YEAR(publishDate), MONTH(publishDate), SUM(value) as val, count(1) as count FROM `contractnotice` WHERE (YEAR(publishDate) >= 2008) -AND childCN = 0 +AND "childCN" is null GROUP BY MONTH(publishDate), YEAR(publishDate) -ORDER BY YEAR(publishDate), MONTH(publishDate)"; +ORDER BY YEAR(publishDate), MONTH(publishDate)'; $result = mysql_query($query); $dates = Array(); @@ -214,12 +288,12 @@ } function ContractStartingGraph() { - $query = "SELECT YEAR(contractStart), MONTH(contractStart), + $query = 'SELECT YEAR(contractStart), MONTH(contractStart), SUM(value) as val, count(1) as count FROM `contractnotice` WHERE (YEAR(contractStart) >= 2008) -AND childCN = 0 +AND "childCN" is null GROUP BY MONTH(contractStart), YEAR(contractStart) -ORDER BY YEAR(contractStart), MONTH(contractStart)"; +ORDER BY YEAR(contractStart), MONTH(contractStart)'; $result = mysql_query($query); $dates = Array(); @@ -235,8 +309,8 @@ 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"; + $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(); @@ -296,40 +370,63 @@ } function SuppliersGraph() { + + global $conn; + includeFlot(); $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)) { + + +$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(); + if (!$query) { + 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"]); } -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"]); + +$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(); + if (!$query) { + databaseError($conn->errorInfo()); + } + + foreach ($query->fetchAll() as $row) { + $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)) { + +$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(); + if (!$query) { + 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]; @@ -339,7 +436,6 @@ 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"; @@ -356,7 +452,54 @@ $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>'; + }, + 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 +} + +?> +