--- 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,430 @@ <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"; -}; -?> - -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" } + 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(); + $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"; }; - -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; - + $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(); + $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(); - var x = item.datapoint[0].toFixed(2), - y = item.datapoint[1].toFixed(2); - - showTooltip(item.pageX, item.pageY, - item.series.label + " of " + x + " = " + y); + previousPoint = null; } - } - 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 + }); + + 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 + + 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"]).'},'; - } + $query = $conn->prepare($query); + $query->execute(); + $errors = $conn->errorInfo(); + if ($errors[2] != "") { + echo("Export terminated, db error" . print_r($errors, true)); + return Array(); + } ?> - ]; - // 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' + <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 + } } - }, - combine: { - color: '#999', - threshold: 0.012 - } - } - }, - legend: { - show: false - } - }); -}); -</script> + }, + legend: { + show: false + } + }); + }); + </script> <div id="graph7" style="width:900px;height:550px"></div> -<?php -}; + <?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\' + $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\' + $query = $conn->prepare($query); + $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 (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); + $query = $conn->prepare($query); + $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` + $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" 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); - -} + $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() { - $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" 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); - -} + $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() { - $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" 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); -} + $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() { - $query = 'select procurementMethod, count(1) as count, SUM(value) as value, MONTH(contractStart) as month, YEAR(contractStart) as year from `contractnotice` + $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); -} + $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() { - $query = "select procurementMethod, SUM(value) as value, MONTH(contractStart) as month, YEAR(contractStart) as year from `contractnotice` + $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; + 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(); - 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 +442,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-&g