--- a/displaySON.php
+++ b/displaySON.php
@@ -1,52 +1,120 @@
prepare($query);
-$query->bindParam(":SONID", $_REQUEST['SONID']);
- $query->execute();
- databaseError($conn->errorInfo());
- $title = reset($query->fetchAll())['title'];
+ $query = 'SELECT title FROM standingoffers WHERE "SONID" = :SONID';
+ $query = $conn->prepare($query);
+ $query->bindParam(":SONID", $_REQUEST['SONID']);
+ $query->execute();
+ databaseError($conn->errorInfo());
+ $title = reset($query->fetchAll())['title'];
include_header($title);
- echo "
".$_REQUEST['SONID']." - ".$title."
";
- $query = 'select category, min("categoryUNSPSC") as "categoryUNSPSC" from contractnotice where "SONID" = :SONID group by category';
-$query = $conn->prepare($query);
-$query->bindParam(":SONID", $_REQUEST['SONID']);
- $query->execute();
- databaseError($conn->errorInfo());
-echo "Categories: ";
-foreach ($query->fetchAll() as $row) {
-echo "{$row['category']} ";
-}
-echo "
";
- $query = 'select "supplierABN", min("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
- where "SONID" = :SONID and abn not in (select distinct "supplierABN" as abn from contractnotice where "SONID" = :SONID)';
-$query = $conn->prepare($query);
-$query->bindParam(":SONID", $_REQUEST['SONID']);
- $query->execute();
- databaseError($conn->errorInfo());
+ echo "" . $_REQUEST['SONID'] . " - " . $title . "
";
echo '
View original record @ tenders.gov.au
';
- echo "
+ $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,
+sum("value") as total_value,
+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 AND ' . " $yearQ $standardQ " . '
+GROUP BY category';
+ $query = $conn->prepare($query);
+ $query->bindParam(":SONID", $_REQUEST['SONID']);
+ $query->execute();
+ databaseError($conn->errorInfo());
+ 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 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 min("supplierABN") "supplierABN", text_mode("supplierName") AS "supplierName", count(*), sum(value) AS value,
+ (CASE WHEN "supplierABN" != 0 THEN "supplierABN"::TEXT ELSE lower("supplierName") END) AS "supplierID"
+FROM contractnotice
+ WHERE ' . $yearQ . ' "childCN" IS NULL AND "SONID" = :SONID GROUP BY "supplierID"
+ UNION SELECT abn AS "supplierABN",name AS "supplierName",0 AS sum,0 AS value, NULL AS "supplierID" FROM standingoffer_suppliers
+ WHERE "SONID" = :SONID AND abn NOT IN (SELECT DISTINCT "supplierABN" AS abn FROM contractnotice WHERE "SONID" = :SONID)';
+ $query = $conn->prepare($query);
+ $query->bindParam(":SONID", $_REQUEST['SONID']);
+ $query->execute();
+ databaseError($conn->errorInfo());
+
+ echo "Suppliers
Supplier |
Contracts Count |
Total Contract Value |
";
-foreach ($query->fetchAll() as $row) {
- setlocale(LC_MONETARY, 'en_US');
- $value = number_format(doubleval($row['value']) , 2);
- $supplier = stripslashes($row['supplierABN'] . '-' . $row['supplierName']);
- echo ("
+ foreach ($query->fetchAll() as $row) {
+ setlocale(LC_MONETARY, 'en_US');
+ $value = number_format(doubleval($row['value']), 2);
+ $supplier = stripslashes($row['supplierABN'] . '-' . $row['supplierName']);
+ echo("
{$row['supplierName']} |
{$row['count']} |
\$$value |
");
- }
- echo "
Contracts
Contract Notice Number |
Contract Description |
@@ -56,52 +124,53 @@
Supplier |
";
- $query = 'SELECT "CNID", description, value, "agencyName", contractnotice."SONID", "contractStart", "supplierName"
- FROM contractnotice
- WHERE '.$yearQ.' "childCN" is null
+ $query = 'SELECT "CNID", title, description, value, "agencyName", contractnotice."SONID", "contractStart", "supplierName"
+ FROM contractnotice INNER JOIN standingoffers ON contractnotice."SONID" = standingoffers."SONID"
+ WHERE ' . $yearQ . ' "childCN" IS NULL
AND contractnotice."SONID" = :SONID
- ORDER BY value DESC';
-$query = $conn->prepare($query);
-$query->bindParam(":SONID", $_REQUEST['SONID']);
- $query->execute();
- databaseError($conn->errorInfo());
- foreach ($query->fetchAll() as $row) {
- setlocale(LC_MONETARY, 'en_US');
- $value = number_format(doubleval($row['value']) , 2);
- echo ("
+ ORDER BY VALUE DESC';
+ $query = $conn->prepare($query);
+ $query->bindParam(":SONID", $_REQUEST['SONID']);
+ $query->execute();
+ databaseError($conn->errorInfo());
+ foreach ($query->fetchAll() as $row) {
+ setlocale(LC_MONETARY, 'en_US');
+ $value = number_format(doubleval($row['value']), 2);
+ echo("
{$row['CNID']} |
{$row['description']} |
\$$value | {$row['agencyName']} |
{$row['contractStart']} |
{$row['supplierName']} |
");
- }
- echo "
";
+ }
+ echo "
";
} else {
- /*
- split by main categories
- */
+
include_header("Standing Offers");
-$query = 'SELECT sum(value), count(*), contractnotice."SONID", min(title) as title
-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 ';
-$query = $conn->prepare($query);
- $query->execute();
- databaseError($conn->errorInfo());
- echo "
+ $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 ';
+ $query = $conn->prepare($query);
+ $query->execute();
+ databaseError($conn->errorInfo());
+ echo "
SONID |
+ Main category |
Total Contracts Value |
Number of Contracts |
";
- foreach ($query->fetchAll() as $row) {
- setlocale(LC_MONETARY, 'en_US');
- $value = number_format(doubleval($row['sum']) , 2);
- echo ("{$row['title']} | \$$value | {$row['count']} |
");
-}
-echo "
";
+ foreach ($query->fetchAll() as $row) {
+ setlocale(LC_MONETARY, 'en_US');
+ $value = number_format(doubleval($row['sum']), 2);
+ $title = $row['SONID'];
+ if (strlen($row['title']) > 1) $title = $row['title'];
+ echo("$title | {$row['category']} | \$$value | {$row['count']} |
");
+ }
+ echo "
";
}
include_footer();