--- a/displaySON.php +++ b/displaySON.php @@ -9,11 +9,25 @@ $title = reset($query->fetchAll())['title']; include_header($title); echo "<center><h1>".$_REQUEST['SONID']." - ".$title."</h1></center>"; - $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 = '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 "<span class='label label-warning'><a href='displayCategory.php?category={$row['category']}'>{$row['category']}</a></span> "; +} +echo "<br/>"; + $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 '<br><a href="https://www.tenders.gov.au/?event=public.advancedsearch.CNSONRedirect&type=sonSearchEvent&SONID=' . $_REQUEST['SONID'] . '"> View original record @ tenders.gov.au</a><br>'; echo "<table> <thead> <tr> @@ -42,8 +56,8 @@ <th>Supplier</th> </tr> </thead>"; - $query = 'SELECT "CNID", title, description, value, "agencyName", contractnotice."SONID", "contractStart", "supplierName" - FROM contractnotice inner join standingoffers on contractnotice."SONID" = standingoffers."SONID" + $query = 'SELECT "CNID", description, value, "agencyName", contractnotice."SONID", "contractStart", "supplierName" + FROM contractnotice WHERE '.$yearQ.' "childCN" is null AND contractnotice."SONID" = :SONID ORDER BY value DESC'; @@ -90,5 +104,5 @@ echo "</table>"; } include_footer(); -?> +