--- a/displaySON.php
+++ b/displaySON.php
@@ -9,10 +9,31 @@
$title = reset($query->fetchAll())['title'];
include_header($title);
echo "
" . $_REQUEST['SONID'] . " - " . $title . "
";
+ echo '
View original record @ tenders.gov.au
';
+
+ $query = '
+ SELECT
+sum((consultancy = \'Yes\')::int) AS consultancy,
+sum(("confidentialityContract" = \'Yes\')::int) AS "confidentialityContract",
+sum(("confidentialityOutputs" = \'Yes\')::int) AS "confidentialityOutputs",
+sum((("procurementMethod" = \'Open\' OR "procurementMethod" = \'Open tender\') AND "SONID" IS null)::int) AS open,
+sum((("procurementMethod" = \'Open\' OR "procurementMethod" = \'Open tender\') AND "SONID" IS NOT null)::int) AS "openSON",
+sum(("procurementMethod" = \'Prequalified tender\' OR "procurementMethod" = \'Select\')::INT) AS prequalified,
+sum(("procurementMethod" = \'Direct\' OR "procurementMethod" = \'Limited tender\')::int) AS limited,
+COUNT(*) AS total
+FROM contractnotice
+ WHERE "SONID" = :SONID AND ' . " $yearQ $standardQ " ;
+ $query = $conn->prepare($query);
+ $query->bindParam(":SONID", $_REQUEST['SONID']);
+ $query->execute();
+ databaseError($conn->errorInfo());
+
+ $stats = reset($query->fetchAll());
+ show_stats($stats);
$query = 'SELECT category, min("categoryUNSPSC") AS "categoryUNSPSC", count(*) AS count, sum(value) AS value
FROM contractnotice
-WHERE "SONID" = :SONID
+WHERE "SONID" = :SONID AND ' . " $yearQ $standardQ " . '
GROUP BY category';
$query = $conn->prepare($query);
$query->bindParam(":SONID", $_REQUEST['SONID']);
@@ -37,6 +58,32 @@
}
echo "
";
+ $query = 'SELECT text_mode(contractnotice."agencyName") AS agencyname, count(*), sum(value) AS value FROM contractnotice
+ WHERE ' . $yearQ . ' "childCN" IS NULL AND "SONID" = :SONID GROUP BY "agencyName"';
+ $query = $conn->prepare($query);
+ $query->bindParam(":SONID", $_REQUEST['SONID']);
+ $query->execute();
+ databaseError($conn->errorInfo());
+
+ 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);
+
+ echo("
+ {$row['agencyname']} |
+ {$row['count']} |
+ \$$value |
+
");
+ }
+ echo "
";
+
$query = 'SELECT "supplierABN", text_mode("supplierName") AS "supplierName", count(*), sum(value) AS value FROM contractnotice
WHERE ' . $yearQ . ' "childCN" IS NULL AND "SONID" = :SONID GROUP BY "supplierABN"
UNION SELECT abn AS "supplierABN",name AS "supplierName",0 AS sum,0 AS value FROM standingoffer_suppliers
@@ -45,7 +92,6 @@
$query->bindParam(":SONID", $_REQUEST['SONID']);
$query->execute();
databaseError($conn->errorInfo());
- echo '
View original record @ tenders.gov.au
';
echo "Suppliers
@@ -64,6 +110,7 @@
\$$value |
");
}
+
echo "
Contracts
Contract Notice Number |
@@ -96,11 +143,9 @@
}
echo "
";
} else {
- /*
- split by main categories
- */
+
include_header("Standing Offers");
- $query = 'SELECT sum(value), count(*), contractnotice."SONID", min(title) AS title
+ $query = 'SELECT sum(value), count(*), contractnotice."SONID", min(title) AS title, text_mode(category) as category
FROM contractnotice INNER JOIN standingoffers ON contractnotice."SONID" = standingoffers."SONID"
WHERE ' . $yearQ . ' "childCN" IS NULL AND contractnotice."SONID" != \'\'
GROUP BY contractnotice."SONID" ORDER BY sum(VALUE) DESC ';
@@ -110,6 +155,7 @@
echo "
SONID |
+ Main category |
Total Contracts Value |
Number of Contracts |
@@ -117,7 +163,9 @@
foreach ($query->fetchAll() as $row) {
setlocale(LC_MONETARY, 'en_US');
$value = number_format(doubleval($row['sum']), 2);
- echo("{$row['title']} | \$$value | {$row['count']} |
");
+ $title = $row['SONID'];
+ if (strlen($row['title']) > 1) $title = $row['title'];
+ echo("$title | {$row['category']} | \$$value | {$row['count']} |
");
}
echo "
";
}