--- a/lib/graphs.inc.php
+++ b/lib/graphs.inc.php
@@ -1,272 +1,642 @@
+
+
+
+
+
+
+';
+ $includedFlot = true;
+ }
+}
+
+function CNDistributionGraph() {
+ global $conn;
+ includeFlot();
+ ?>
+
+
+ = $startYear) AND childCN = 0
-GROUP BY agencyName ORDER BY val DESC limit $topX";
-$result = mysql_query($query);
-$agencies = Array();
-$values = Array();
-while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
- $agencies[] = $row['agencyName'];
- $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, agencyName FROM `contractnotice` WHERE (YEAR(contractStart) >= $startYear) AND childCN = 0
-GROUP BY agencyName ORDER BY val DESC LIMIT 18446744073709551610 OFFSET $topX) as a";
-$result = mysql_query($query);
-while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
- $agencies[] = $row['count'] . " other agencies";
- $values[] = doubleval($row[0]);
-}
-mysql_free_result($result);
-
-};
+
+ 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();
+ }
+ ?>
+
+
+
+ = $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);
+
+ global $conn,$startYear, $yearQ;
+ includeFlot();
+
+ $topX = 20;
+ $query = 'SELECT SUM(value) as val, text_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, text_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]);
+ }
+ }
+ ?>
+
+
+
+ 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();
+ ?>
+
+
+
+
+
+