--- a/displaySupplier.php +++ b/displaySupplier.php @@ -17,14 +17,42 @@ } include_header(str_replace("%", "", $title)); echo '<center><h1>' . str_replace("%", "", $title) . '</h1></center>'; + if (isset($supplierABN)) { + echo "<b>ABN:</b> <a href=\"https://abr.business.gov.au/SearchByAbn.aspx?abn=$supplierABN\">$supplierABN</a>"; + + $cleansedName = '%'.cleanseName($title).'%'; + $query = 'SELECT DISTINCT ON ("supplierABN") "supplierName","supplierABN" FROM contractnotice WHERE "supplierName" ILIKE :cleansedName and "supplierABN" != :supplierABN'; + $query = $conn->prepare($query); + $query->bindParam(":cleansedName", $cleansedName); + $query->bindParam(":supplierABN", $supplierABN); + $query->execute(); + databaseError($conn->errorInfo()); + echo "<br/><b>Similar to:</b> "; + foreach ($query->fetchAll() as $row) { + echo "<a href='displaySupplier.php?supplier={$row['supplierABN']}-".urlencode($row['supplierName'])."'> {$row['supplierName']} (ABN: {$row['supplierABN']})</a>, "; + } + $query = 'SELECT distinct on (lower("supplierName")) "supplierName" from contractnotice where "supplierABN" = :supplierABN'; $query = $conn->prepare($query); $query->bindParam(":supplierABN", $supplierABN); $query->execute(); databaseError($conn->errorInfo()); - echo "<b>Also known as:</b> ".implode(', ', $query->fetchAll(PDO::FETCH_COLUMN, 0)); + $names = Array(); + foreach ($query->fetchAll() as $row) { + $names[cleanseName($row[0])] = $row[0]; + } + if (count($names) < 20) { + echo "<br/><b>Also known as:</b> " . implode(', ', array_values($names))."<br/>"; + } else { + echo "<details> + <summary>Also known as ...</summary> + <p>".implode(', ', array_values($names))."</p> +</details>"; + } + } + $query = ' SELECT @@ -83,17 +111,18 @@ echo "</table><br/>"; - $query = 'SELECT SUM("value") AS "value", count(*), text_mode(contractnotice."agencyName") AS agencyname FROM contractnotice JOIN agency_nametoabn ON contractnotice."agencyName"=agency_nametoabn."agencyName" + /*$query = 'SELECT SUM("value") AS "value", count(*), text_mode(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) { +GROUP BY abn ORDER BY SUM("value") DESC';*/ + $query = 'SELECT SUM("value") AS "value", count(*), "agencyName" AS agencyname FROM contractnotice +WHERE ' . $yearQ . ' ' . $supplierQ . ' "childCN" IS NULL GROUP BY "agencyName" ORDER BY SUM("value") DESC'; + + $query = $conn->prepare($query); if (isset($supplierABN)) { $query->bindParam(":supplierABN", $supplierABN); } else { $query->bindParam(":supplierName", $supplierName); } - } $query->execute(); databaseError($conn->errorInfo()); @@ -197,11 +226,15 @@ FROM contractnotice WHERE ' . $yearQ . ' "childCN" IS NULL GROUP BY supplierID,"supplierABN" -ORDER BY val DESC -LIMIT 100'; - $query = $conn->prepare($query); - $query->execute(); - databaseError($conn->errorInfo()); +ORDER BY val DESC'; +if ($yearQ == '') $query .=' LIMIT 1000'; + $query = $conn->prepare($query); + $query->execute(); + databaseError($conn->errorInfo()); +if ($query->rowCount() > 999 && $yearQ == '') { +// if 1000 records warn too many results +print "<b> More than 1000 results found so only first 1000 shown. Please filter by year to see all results </b>"; +} echo "<table> <thead> <tr> <th>Position</th>