--- a/lib/graphs.inc.php
+++ b/lib/graphs.inc.php
@@ -1,362 +1,588 @@
+ if (!$includedFlot) {
+ echo '
+
+
';
-$includedFlot = true;
- }
+ $includedFlot = true;
+ }
}
function CNDistributionGraph() {
- global $conn;
- includeFlot();
- ?>
-
-
-
+ };
+
+ 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) {
+ $('' + contents + '
').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);
+ }
+
+
+ prepare($query);
-$query->execute();
-if (!$query) {
- databaseError($conn->errorInfo());
- return Array();
+ $query = $conn->prepare($query);
+ $query->execute();
+ $errors = $conn->errorInfo();
+ if ($errors[2] != "") {
+ echo("Export terminated, db error" . print_r($errors, true));
+ return Array();
+ }
+ ?>
+
+
+
+ = :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() {
+
+ 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 extract(year from "contractStart"),extract(month from "contractStart"),
+SUM(value) as val, count(1) as count FROM contractnotice
+WHERE (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;
+ $query = 'SELECT extract (YEAR from "contractStart") as year, extract (MONTH from "contractStart") as month,
+SUM(value) as val, count(*) as count FROM contractnotice
+WHERE 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();
+ ?>
+
+
+
+
+
-
-
-
-= $startYear) AND childCN = 0 AND agencyName = '$agency'
-GROUP BY lower(supplierName) ORDER BY val DESC limit $topX";
-$result = mysql_query($query);
-$suppliers = Array();
-$values = Array();
-while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
- $suppliers[] = ucsmart($row['supplierName']);
- $values[] = doubleval($row["val"]);
-}
-mysql_free_result($result);
-
-$query = "SELECT sum(a.val) as value, count(1) as count from (SELECT SUM(value) as val, supplierName FROM `contractnotice` WHERE (YEAR(contractStart) >= $startYear) AND childCN = 0 and agencyName = '$agency'
-GROUP BY lower(supplierName) ORDER BY val DESC LIMIT 18446744073709551610 OFFSET $topX) as a";
-$result = mysql_query($query);
-while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
-if ($row['count'] > 0) {
- $suppliers[] = $row['count'] . " other suppliers";
- $values[] = doubleval($row[0]);
-}
-}
-mysql_free_result($result);
-}
-
-function CnCGraph() {
-$query = "select procurementMethod, count(1) as count, SUM(value) as val, MONTH(contractStart) as month, YEAR(contractStart) as year from `contractnotice`
-where $agencyQ $supplierQ $standardQ group by procurementMethod,year,month order by procurementMethod,year,month";
-$result = mysql_query($query);
-$methods = Array("Direct","Open","Select");
-$dates = Array();
-$methodCountsP = Array();
-$methodCounts = Array();
-$maxValue = 0;
-while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
- setlocale(LC_MONETARY, 'en_US');
- if ($row['val'] > $maxValue) $maxValue = $row['val'];
- $date = date( 'F ', mktime(0, 0, 0, $row["month"]) ). $row["year"];
- if (array_search($date,$dates) === false ) {
- $dates[$row["year"]*100 + $row["month"]] = $date;
- ksort($dates);
- }
- $methodCountsP[$row["procurementMethod"]][$date] = $row["count"];
-
-}
-foreach ($methods as $method) {
- foreach($dates as $date) {
- if ($methodCountsP[$method][$date] > 0) $methodCounts[$method][] = $methodCountsP[$method][$date];
- else $methodCounts[$method][] = 0;
- }
-}
-$dates = array_values($dates);
-$totalRecords = array_sum_all($methodCounts);
-mysql_free_result($result);
-
-
-function formatCallback($aVal) {
- global $totalRecords;
- return percent($aVal, $totalRecords) . "%";
-}
-$attributes = Array();
-$attributeNames = Array(
- "Consultancies",
- "Confidentialities"
-);
-$query = "SELECT 'consultancy', count(1) FROM `contractnotice` WHERE $agencyQ $supplierQ consultancy='Yes' AND childCN = 0;";
-$result = mysql_query($query);
-$row = mysql_fetch_array($result, MYSQL_BOTH);
-$attributes[0] = $row[1];
-$query = "SELECT 'confidentiality', count(1) FROM `contractnotice` WHERE $agencyQ $supplierQ (confidentialityContract='Yes' OR confidentialityOutputs='Yes') AND childCN = 0;";
-$result = mysql_query($query);
-$row = mysql_fetch_array($result, MYSQL_BOTH);
-$attributes[1] = $row[1];
-mysql_free_result($result);
-
-}
-function ContractPublishedGraph() {
- $query = "SELECT YEAR(publishDate), MONTH(publishDate),
-SUM(value) as val, count(1) as count FROM `contractnotice`
-WHERE (YEAR(publishDate) >= 2008)
-AND childCN = 0
-GROUP BY MONTH(publishDate), YEAR(publishDate)
-ORDER BY YEAR(publishDate), MONTH(publishDate)";
-
-$result = mysql_query($query);
-$dates = Array();
-$values = Array();
-while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
-setlocale(LC_MONETARY, 'en_US');
-$value = number_format(doubleval($row["val"]),2);
-$month_name = date( 'F', mktime(0, 0, 0, $row[1]) );
- $dates[] = $month_name." {$row[0]}";
- $counts[] = doubleval($row["count"]);
- $values[] = doubleval($row["val"]);
-}
-mysql_free_result($result);
-
-}
-function ContractStartingGraph() {
- $query = "SELECT YEAR(contractStart), MONTH(contractStart),
-SUM(value) as val, count(1) as count FROM `contractnotice`
-WHERE (YEAR(contractStart) >= 2008)
-AND childCN = 0
-GROUP BY MONTH(contractStart), YEAR(contractStart)
-ORDER BY YEAR(contractStart), MONTH(contractStart)";
-
-$result = mysql_query($query);
-$dates = Array();
-$values = Array();
-while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
-setlocale(LC_MONETARY, 'en_US');
-$value = number_format(doubleval($row["val"]),2);
-$month_name = date( 'F', mktime(0, 0, 0, $row[1]) );
- $dates[] = $month_name." {$row[0]}";
- $counts[] = doubleval($row["count"]);
- $values[] = doubleval($row["val"]);
-}
-mysql_free_result($result);
-}
-function MethodCountGraph() {
- $query = "select procurementMethod, count(1) as count, SUM(value) as value, MONTH(contractStart) as month, YEAR(contractStart) as year from `contractnotice`
-where $agencyQ $supplierQ $standardQ group by procurementMethod,year,month order by procurementMethod,year,month";
-$result = mysql_query($query);
-$methods = Array("Direct","Open","Select");
-$dates = Array();
-$methodCountsP = Array();
-$methodCounts = Array();
-$maxValue = 0;
-while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
- setlocale(LC_MONETARY, 'en_US');
- if ($row['value'] > $maxValue) $maxValue = $row['value'];
- $date = date( 'F ', mktime(0, 0, 0, $row["month"]) ). $row["year"];
- if (array_search($date,$dates) === false ) {
- $dates[$row["year"]*100 + $row["month"]] = $date;
- ksort($dates);
- }
- $methodCountsP[$row["procurementMethod"]][$date] = $row["count"];
-
-}
-foreach ($methods as $method) {
- foreach($dates as $date) {
- if ($methodCountsP[$method][$date] > 0) $methodCounts[$method][] = $methodCountsP[$method][$date];
- else $methodCounts[$method][] = 0;
- }
-}
-$dates = array_values($dates);
-$totalRecords = array_sum_all($methodCounts);
-mysql_free_result($result);
-}
-function MethodValueGraph() {
- $query = "select procurementMethod, SUM(value) as value, MONTH(contractStart) as month, YEAR(contractStart) as year from `contractnotice`
-where $agencyQ $supplierQ $standardQ group by procurementMethod,year,month order by procurementMethod,year,month";
-$result = mysql_query($query);
-$methods = Array("Direct","Open","Select");
-$dates = Array();
-$methodValuesP = Array();
-$methodValues = Array();
-$maxValue = 0;
-while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
- setlocale(LC_MONETARY, 'en_US');
- if ($row['value'] > $maxValue) $maxValue = $row['value'];
- $date = date( 'F ', mktime(0, 0, 0, $row["month"]) ). $row["year"];
- if (array_search($date,$dates) === false ) {
- $dates[$row["year"]*100 + $row["month"]] = $date;
- ksort($dates);
- }
- $methodValuesP[$row["procurementMethod"]][$date] = $row["value"];
-
-}
-foreach ($methods as $method) {
- foreach($dates as $date) {
- if ($methodValuesP[$method][$date] > 0) $methodValues[$method][] = $methodValuesP[$method][$date];
- else $methodValues[$method][] = 0;
- }
-}
-$dates = array_values($dates);
-$totalRecords = array_sum_all($methodValues);
-mysql_free_result($result);
-
-}
-function SuppliersGraph() {
-$topX = 10;
-$query = "SELECT value, supplierName
-FROM `contractnotice` WHERE (YEAR(contractStart) >= 2009) AND childCN = 0
-GROUP BY supplierABN ORDER BY value DESC limit $topX";
-$result = mysql_query($query);
-$suppliers = Array();
-$values = Array();
-while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
- setlocale(LC_MONETARY, 'en_US');
- // $value = number_format(doubleval($row["value"]) , 2);
- $suppliers[] = ucsmart($row[1]);
- $values[] = doubleval($row["value"]);
-}
-mysql_free_result($result);
-
-$query = "SELECT sum(a.value) as val, supplierCountry from (SELECT value, supplierName, supplierCountry
-FROM `contractnotice` WHERE (YEAR(contractStart) >= 2009) AND childCN = 0
-GROUP BY supplierName ORDER BY value LIMIT 18446744073709551610 OFFSET $topX) as a group by supplierCountry order by val DESC limit 5 offset 1";
-$result = mysql_query($query);
-while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
- $suppliers[] = "Other suppliers in ".ucsmart($row["supplierCountry"]);
- $values[] = doubleval($row[0]);
-}
-
-mysql_free_result($result);
-
-$query = "SELECT sum(a.value) as val, TRUNCATE(supplierPostcode,-2) as postcode from (SELECT value, supplierName, supplierPostcode, supplierCountry
-FROM `contractnotice` WHERE (YEAR(contractStart) >= 2009) AND childCN = 0
-GROUP BY supplierName ORDER BY value LIMIT 18446744073709551610 OFFSET $topX) as a
-WHERE (supplierCountry LIKE 'Australia' OR supplierCountry LIKE 'AUSTRALIA') AND supplierPostcode < 10000
-group by TRUNCATE(supplierPostcode,-2)
-order by val DESC";
-$result = mysql_query($query);
-while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
- if ($row['postcode'][0] == 2 && $row['postcode'][1] == 6) $ACTvalue += $row[0];
- else if ($row['postcode'][0] == 2 || $row['postcode'][0] == 1) $NSWvalue += $row[0];
- else if ($row['postcode'][0] == 3 || $row['postcode'][0] == 8) $Vicvalue += $row[0];
- else if ($row['postcode'][0] == 4 || $row['postcode'][0] == 9) $QLDvalue += $row[0];
- else if ($row['postcode'][0] == 5) $SAvalue += $row[0];
- else if ($row['postcode'][0] == 6) $WAvalue += $row[0];
- else if ($row['postcode'][0] == 7) $Tasvalue += $row[0];
- else if ($row['postcode'][0] == 0) $NTvalue += $row[0];
-}
-mysql_free_result($result);
-$suppliers[] = "Other suppliers in Australia - ACT";
-$values[] = doubleval($ACTvalue);
-$suppliers[] = "Other suppliers in Australia - NSW";
-$values[] = doubleval($NSWvalue);
-$suppliers[] = "Other suppliers in Australia - Victoria";
-$values[] = doubleval($Vicvalue);
-$suppliers[] = "Other suppliers in Australia - Queensland";
-$values[] = doubleval($QLDvalue);
-$suppliers[] = "Other suppliers in Australia - NT";
-$values[] = doubleval($NTvalue);
-$suppliers[] = "Other suppliers in Australia - West Australia";
-$values[] = doubleval($WAvalue);
-$suppliers[] = "Other suppliers in Australia - South Australia";
-$values[] = doubleval($SAvalue);
-$suppliers[] = "Other suppliers in Australia - Tasmania";
-$values[] = doubleval($Tasvalue);
-
-}
-
-?>
+