--- a/displaySupplier.php
+++ b/displaySupplier.php
@@ -1,58 +1,122 @@
'.str_replace("%","",$supplierName).'
';
- agenciesGraph();
- // MethodCountGraph($supplierS);
- // CnCGraph($supplierS);
- // MethodValueGraph($supplierS);
- /* lobbyist ties
-
- links to ABR/ASIC/Google News/ASX/Court records
-
- total value to various agencies (bar graph)
-
- spread procurement methods + percent consultancies + percent confidential (bar graph)
-
- spread of contract values
-
- spread of industries (textual?) */
- // echo '';
- // echo '';
-$query = 'select contractnotice."SONID", min(title) as title, count(*), sum(value) as value from contractnotice inner join standingoffers on contractnotice."SONID" = standingoffers."SONID" WHERE ' .$yearQ . ' ' .
- $supplierQ . ' ' . $standardQ
- . ' and contractnotice."SONID" != \'\' group by contractnotice."SONID"';
-$query = $conn->prepare($query);
+ $title = $supplierName;
+ if ($supplierABN) {
+ $query = 'SELECT text_mode("supplierName") AS "supplierName" FROM contractnotice WHERE "supplierABN" = :supplierABN GROUP BY "supplierABN"';
+ $query = $conn->prepare($query);
+ $query->bindParam(":supplierABN", $supplierABN);
+ $query->execute();
+ databaseError($conn->errorInfo());
+ $title = reset($query->fetchAll())['supplierName'];
+ }
+ include_header(str_replace("%", "", $title));
+ echo '' . str_replace("%", "", $title) . '
';
+ /*
+ * Common names and uncommon names
+ * Procurement method in text, seperate Open via SON from Open
+ * Consultancies, Confidentialities percentage
+ */
+ $query = 'SELECT category, min("categoryUNSPSC") AS "categoryUNSPSC", count(*) AS count, sum(value) AS value FROM contractnotice
+ WHERE ' . $supplierQ . " " . $standardQ . ' GROUP BY category ORDER BY count(*) DESC LIMIT 10';
+ $query = $conn->prepare($query);
+ if ($supplierParts[0] > 0) {
+ $query->bindParam(":supplierABN", $supplierABN);
+ } else {
+ $query->bindParam(":supplierName", $supplierName);
+ }
+ $query->execute();
+ databaseError($conn->errorInfo());
- if ($supplierParts[0] > 0) {
- $query->bindParam(":supplierABN", $supplierABN);
- } else {
- $query->bindParam(":supplierName", $supplierName);
- }
+ echo "Categories
+
+ Category |
+ Contracts Count |
+ Total Contract Value |
+
+ ";
+ foreach ($query->fetchAll() as $row) {
+ setlocale(LC_MONETARY, 'en_US');
+ $value = number_format(doubleval($row['value']), 2);
+ $category = urlencode($row['category']);
+ echo("
+ {$row['category']} |
+ {$row['count']} |
+ \$$value |
+
");
+ }
+ echo "
";
+
+
+ $query = 'SELECT SUM("value") AS "value", count(*), 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();
databaseError($conn->errorInfo());
-echo "
+
+ echo "Agencies
+
+ Agency |
+ Contracts Count |
+ Total Contract Value |
+
+ ";
+ foreach ($query->fetchAll() as $row) {
+ setlocale(LC_MONETARY, 'en_US');
+ $value = number_format(doubleval($row['value']), 2);
+ $agency =urlencode($row['agencyname']);
+ echo("
+ {$row['agencyname']} |
+ {$row['count']} |
+ \$$value |
+
");
+ }
+ echo "
";
+
+ $query = 'SELECT contractnotice."SONID", min(title) AS title, count(*), sum(value) AS value FROM contractnotice INNER JOIN standingoffers ON contractnotice."SONID" = standingoffers."SONID" WHERE ' . $yearQ . ' ' .
+ $supplierQ . ' ' . $standardQ
+ . ' AND contractnotice."SONID" != \'\' GROUP BY contractnotice."SONID"';
+ $query = $conn->prepare($query);
+
+
+ if ($supplierParts[0] > 0) {
+ $query->bindParam(":supplierABN", $supplierABN);
+ } else {
+ $query->bindParam(":supplierName", $supplierName);
+ }
+ $query->execute();
+ databaseError($conn->errorInfo());
+$sonrows = "";
+ foreach ($query->fetchAll() as $row) {
+ setlocale(LC_MONETARY, 'en_US');
+ $value = number_format(doubleval($row['value']), 2);
+ $sonrows .= "
+ {$row['title']} |
+ {$row['count']} |
+ \$$value |
+
";
+ }
+ if (strlen($sonrows) > 1) {
+ echo "Standing Offers/Panels
Standing Offer |
Contracts Count |
Total Contract Value |
- ";
- foreach ($query->fetchAll() as $row) {
- setlocale(LC_MONETARY, 'en_US');
- $value = number_format(doubleval($row['value']), 2);
- echo ("
- {$row['title']} |
- {$row['count']} |
- \$$value |
-
");
+ $sonrows
";
}
- echo "
+ echo "Contracts
Contract Notice Number |
Contract Description |
@@ -64,24 +128,24 @@
";
$query = 'SELECT "CNID", "description", "value", "agencyName", "category",
"contractStart", "supplierName"
- FROM contractnotice WHERE ' .$yearQ . ' ' .
- $supplierQ . ' ' . $standardQ
- . ' ORDER BY value DESC';
-
+ FROM contractnotice WHERE ' . $yearQ . ' ' .
+ $supplierQ . ' ' . $standardQ
+ . ' ORDER BY VALUE DESC';
+
$query = $conn->prepare($query);
- if ($supplierParts[0] > 0) {
- $query->bindParam(":supplierABN", $supplierABN);
- } else {
- $query->bindParam(":supplierName", $supplierName);
- }
+ if ($supplierParts[0] > 0) {
+ $query->bindParam(":supplierABN", $supplierABN);
+ } else {
+ $query->bindParam(":supplierName", $supplierName);
+ }
$query->execute();
databaseError($conn->errorInfo());
foreach ($query->fetchAll() as $row) {
setlocale(LC_MONETARY, 'en_US');
$value = number_format(doubleval($row['value']), 2);
- echo ("
+ echo("
{$row['CNID']} |
{$row['description']} |
\$$value | {$row['agencyName']} |
@@ -96,16 +160,16 @@
*/
include_header("Suppliers");
suppliersGraph();
- $query = 'SELECT SUM("value") as val, mode("supplierName") as supplierName, "supplierABN",(
- case when "supplierABN" != 0 THEN lower("supplierABN"::text) ELSE lower("supplierName") END) as supplierID
+ $query = 'SELECT SUM("value") AS val, text_mode("supplierName") AS supplierName, "supplierABN",(
+ CASE WHEN "supplierABN" != 0 THEN lower("supplierABN"::TEXT) ELSE lower("supplierName") END) AS supplierID
FROM contractnotice
-WHERE ' .$yearQ . ' "childCN" is null
+WHERE ' . $yearQ . ' "childCN" IS NULL
GROUP BY supplierID,"supplierABN"
ORDER BY val DESC
LIMIT 100';
$query = $conn->prepare($query);
$query->execute();
- databaseError($conn->errorInfo());
+ databaseError($conn->errorInfo());
echo "";