--- a/lib/graphs.inc.php
+++ b/lib/graphs.inc.php
@@ -1,430 +1,536 @@
+ if (!$includedFlot) {
+ echo '
+
+
';
-$includedFlot = true;
- }
+ $includedFlot = true;
+ }
}
function CNDistributionGraph() {
- global $conn;
- includeFlot();
- ?>
-
-
-
+
+
+ prepare($query);
-$query->execute();
-if (!$query) {
- databaseError($conn->errorInfo());
- return Array();
-}
-?>
-
+ },
+ legend: {
+ show: false
+ }
+ });
+ });
+
-= $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);
+ $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';
+ $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';
+ $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 = 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);
-
-}
+ $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 = 0
+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);
-
-}
+ORDER BY YEAR(publishDate), MONTH(publishDate)';
+
+ $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 = 0
+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);
-}
+ORDER BY YEAR(contractStart), MONTH(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 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';
+ $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`
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;
+ 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" = 0
+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
-FROM contractnotice WHERE "childCN" = 0 and "supplierCountry" NOT ILIKE \'Australia\'
+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" = 0
+FROM contractnotice WHERE "childCN" is null
GROUP BY "supplierABN" ORDER BY sum(value) LIMIT 1844674 OFFSET 10) as a
WHERE (suppliercountry ILIKE \'Australia\')
group by substring(
supplierpostcode from 0 for 2)
order by val DESC;';
-$query = $conn->prepare($query);
- $query->execute();
- if (!$query) {
- databaseError($conn->errorInfo());
- }
-
- foreach ($query->fetchAll() as $row) {
- if ($row['postcode'][0] == 2 && $row['postcode'][1] == 6) $ACTvalue += $row[0];
- else if ($row['postcode'][0] == 2 || $row['postcode'][0] == 1) $NSWvalue += $row[0];
- else if ($row['postcode'][0] == 3 || $row['postcode'][0] == 8) $Vicvalue += $row[0];
- 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);
+ ?>
+
+
+
+
-
-
-
-
+