--- a/lib/graphs.inc.php +++ b/lib/graphs.inc.php @@ -1,8 +1,9 @@ <?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]--> + 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> @@ -10,409 +11,542 @@ <script language="javascript" type="text/javascript" src="lib/flot/jquery.flot.stack.js"></script> '; -$includedFlot = true; - } + $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" - 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) { - databaseError($conn->errorInfo()); - return Array(); -} - -foreach ($query->fetchAll() as $delta) { - - 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"; -}; + 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"; + }; + d1d2Graph(); +} +function d1d2Graph($time = false) { + ?> + + var data = [ + { + data: d1, + series: { + lines: { show: true }, + points: { show: true } + } + <?php if (!$time){ + echo ',bars: { show: true }'; + }?> + }, + { + data: d2, + series: { + lines: { show: true }, + points: { show: true } + } + <?php if (!$time){ + echo ',bars: { show: true }'; + } else { + echo ',yaxis: 2'; + } ?> - -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" } + }] + ; + var options = + { + + grid: { hoverable: true, clickable: true, labelMargin: 17 }, + selection: { mode: "x" } <?php if ($time){ + echo ', xaxis: { + mode: "time" + }';} else { + echo ', series: { + stack: true + },'; + } ?> + }; + + 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() { + + global $conn, $yearQ, $supplierParts,$supplierQ, $supplierABN, $supplierName; + includeFlot(); + $query = 'SELECT SUM("value") as val, MAX(contractnotice."agencyName") as agencyname FROM contractnotice join agency_nametoabn on contractnotice."agencyName"=agency_nametoabn."agencyName" +WHERE ' .$yearQ . ' '.$supplierQ.' "childCN" is null +GROUP BY abn ORDER BY SUM("value") DESC'; + $query = $conn->prepare($query); +if (count($supplierParts) > 0) { + if ($supplierParts[0] > 0) { + $query->bindParam(":supplierABN", $supplierABN); + } else { + $query->bindParam(":supplierName", $supplierName); + } +} + $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) { + + global $conn,$startYear, $yearQ; + includeFlot(); + + $topX = 20; + $query = 'SELECT SUM(value) as val, mode("supplierName") "supplierName" FROM contractnotice WHERE ' .$yearQ . ' extract ("YEAR" from "contractStart") >= :startYear AND "childCN" is null AND "agencyName" like :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, mode("supplierName") FROM contractnotice WHERE ' .$yearQ . '(extract ("YEAR" from "contractStart") >= :startYear) AND "childCN" is null and "agencyName" like :agency +GROUP BY lower("supplierName") ORDER BY val DESC LIMIT 184467440 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]); + } + } + ?> + <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 +} + +function CnCGraph() { + + 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, $yearQ; + $query = 'SELECT extract(year from "contractStart"),extract(month from "contractStart"), +SUM(value) as val, count(1) as count FROM contractnotice +WHERE '.$yearQ.' (extract(year from "contractStart") >= 2008) +AND "childCN" is null +GROUP BY extract(month from "contractStart"), extract(year from "contractStart") +ORDER BY extract(year from "contractStart"), extract(month from "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 ContractStartingGraph() { + + global $conn, $yearQ; + $query = 'SELECT extract (YEAR from "contractStart") as year, extract (MONTH from "contractStart") as month, +SUM(value) as val, count(*) as count FROM contractnotice +WHERE '.$yearQ.' extract (YEAR from "contractStart") >= 2008 +AND "childCN" is null +GROUP BY extract (MONTH from "contractStart"), extract (YEAR from "contractStart") +ORDER BY extract (YEAR from "contractStart"), extract (MONTH from"contractStart")'; + + $query = $conn->prepare($query); + $query->execute(); + databaseError($conn->errorInfo()); +$dates = Array(); + $counts = Array(); + $values = Array(); + foreach ($query->fetchAll() as $row) { + setlocale(LC_MONETARY, 'en_US'); + $value = number_format(doubleval($row["val"]), 2); + $date = mktime(0, 0, 0, $row["month"],1,$row["year"])*1000; + if ($row["count"] > 1) { + $dates[] = $date; + $counts[] = $row["count"]; + $values[] = $row["val"]; + } + } + includeFlot(); + ?> + <center><div id="cstart" style="width:900px;height:550px"></div></center> + <script type="text/javascript"> + var placeholder = $("#cstart"); + $(function () { + + var d1 = []; + var d2 = []; + <?php + foreach ($counts as $key => $count) { + + echo "d1.push([ " . $dates[$key] . ", " . $count . "]); \n"; }; - -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() { - - 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" is null -GROUP BY abn ORDER BY SUM("value") DESC'; -$query = $conn->prepare($query); -$query->execute(); -if (!$query) { - databaseError($conn->errorInfo()); - 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" is null 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" 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) { - $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` + foreach ($values as $key => $value) { + + echo "d2.push([ " . $dates[$key] . ", " . $value . "]); \n"; + }; + d1d2Graph(true); +} + +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" 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" is null;'; -$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" is null -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" is null -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() { - global $conn; - includeFlot(); -$topX = 10; -$suppliers = Array(); -$values = Array(); - - -$query = 'SELECT SUM("value") as value, MAX("supplierName") as supplierName, ( + global $conn, $yearQ; + includeFlot(); + $topX = 10; + $suppliers = Array(); + $values = Array(); + + + $query = 'SELECT SUM("value") as value, mode("supplierName") as supplierName, ( case when "supplierABN" != 0 THEN "supplierABN"::text ELSE "supplierName" END) as supplierID FROM contractnotice -WHERE "childCN" is null +WHERE ' .$yearQ . ' "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"]); -} - -$query = 'SELECT sum(a.svalue) as val, suppliercountry from (SELECT sum("value") as svalue, max("supplierCountry") as suppliercountry +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(); - if (!$query) { - 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( + $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 @@ -420,86 +554,89 @@ 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]; - 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); - - + $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>'; + }, + 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 +} ?> -<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 -} - -?> - +