--- a/lib/graphs.inc.php
+++ b/lib/graphs.inc.php
@@ -1,8 +1,9 @@
+ if (!$includedFlot) {
+ echo '
@@ -10,409 +11,446 @@
';
-$includedFlot = true;
- }
+ $includedFlot = true;
+ }
}
function CNDistributionGraph() {
- global $conn;
- includeFlot();
- ?>
-
-
-' + 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();
-}
-?>
-
+ },
+ legend: {
+ show: false
+ }
+ });
+ });
+
-= $startYear) AND "childCN" is null AND agencyName = \'$agency\'
+
+ 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';
-$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);
+ $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`
-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);
-
-}
+
+ 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() {
- $query = 'SELECT YEAR(publishDate), MONTH(publishDate),
-SUM(value) as val, count(1) as count FROM `contractnotice`
+
+ 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)';
-$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),
-SUM(value) as val, count(1) as count FROM `contractnotice`
+
+ 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)';
-$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`
+
+ 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';
-$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`
+
+ 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();
-$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 +458,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);
+ ?>
+
+
+
+
-
-
-
-
-
+