--- a/displaySON.php
+++ b/displaySON.php
@@ -1,1 +1,94 @@
+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 "supplierABN", min("supplierName") as "supplierName", count(*), sum(value) as value from contractnotice WHERE '.$yearQ.' "childCN" is null AND "SONID" = :SONID group by "supplierABN"';
+$query = $conn->prepare($query);
+$query->bindParam(":SONID", $_REQUEST['SONID']);
+ $query->execute();
+ databaseError($conn->errorInfo());
+ echo "
+
+ 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 ("
+ {$row['supplierName']} |
+ {$row['count']} |
+ \$$value |
+
");
+ }
+ echo "
+
+ Contract Notice Number |
+ Contract Description |
+ Total Contract Value |
+ Agency |
+ Contract Start Date |
+ Supplier |
+
+ ";
+ $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 ("
+ {$row['CNID']} |
+ {$row['description']} |
+ \$$value | {$row['agencyName']} |
+ {$row['contractStart']} |
+ {$row['supplierName']} |
+
");
+ }
+ 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 "
+
+ SONID |
+ 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 "
";
+}
+include_footer();
+?>
+