--- a/displaySupplier.php +++ b/displaySupplier.php @@ -4,8 +4,11 @@ if ($_REQUEST['supplier']) { $supplierS = htmlentities(strip_tags($_REQUEST['supplier'])); $title = $supplierName; - if ($supplierABN) { - $query = 'SELECT text_mode("supplierName") AS "supplierName" FROM contractnotice WHERE "supplierABN" = :supplierABN GROUP BY "supplierABN"'; + if (isset($supplierABN)) { + $query = 'SELECT text_mode("supplierName") AS "supplierName" +FROM contractnotice +WHERE "supplierABN" = :supplierABN +GROUP BY "supplierABN"'; $query = $conn->prepare($query); $query->bindParam(":supplierABN", $supplierABN); $query->execute(); @@ -14,14 +17,42 @@ } include_header(str_replace("%", "", $title)); echo '

' . str_replace("%", "", $title) . '

'; -if ($supplierABN) { + +if (isset($supplierABN)) { + echo "ABN: $supplierABN"; + + $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 "
Similar to: "; + foreach ($query->fetchAll() as $row) { + echo " {$row['supplierName']} (ABN: {$row['supplierABN']}), "; + } + $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 "Also known as: ".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 "
Also known as: " . implode(', ', array_values($names))."
"; + } else { + echo "
+ Also known as ... +

".implode(', ', array_values($names))."

+
"; + } + } + $query = ' SELECT @@ -32,11 +63,12 @@ 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' . $supplierQ . " " . $standardQ ; - $query = $conn->prepare($query); - if ($supplierParts[0] > 0) { +WHERE' . $supplierQ . " " . $yearQ . " " .$standardQ ; + $query = $conn->prepare($query); + if (isset($supplierABN)) { $query->bindParam(":supplierABN", $supplierABN); } else { $query->bindParam(":supplierName", $supplierName); @@ -48,9 +80,9 @@ show_stats($stats); $query = 'SELECT category, min("categoryUNSPSC") AS "categoryUNSPSC", count(*) AS count, sum(value) AS value FROM contractnotice - WHERE ' . $supplierQ . " " . $standardQ . ' GROUP BY category ORDER BY count(*) DESC LIMIT 10'; - $query = $conn->prepare($query); - if ($supplierParts[0] > 0) { + WHERE ' . $supplierQ . " $yearQ $standardQ ". ' GROUP BY category ORDER BY count(*) DESC LIMIT 10'; + $query = $conn->prepare($query); + if (isset($supplierABN)) { $query->bindParam(":supplierABN", $supplierABN); } else { $query->bindParam(":supplierName", $supplierName); @@ -79,17 +111,18 @@ echo "
"; - $query = 'SELECT SUM("value") AS "value", count(*), MAX(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) { - if ($supplierParts[0] > 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()); @@ -118,7 +151,7 @@ $query = $conn->prepare($query); - if ($supplierParts[0] > 0) { + if (isset($supplierABN)) { $query->bindParam(":supplierABN", $supplierABN); } else { $query->bindParam(":supplierName", $supplierName); @@ -163,7 +196,7 @@ $query = $conn->prepare($query); - if ($supplierParts[0] > 0) { + if (isset($supplierABN)) { $query->bindParam(":supplierABN", $supplierABN); } else { $query->bindParam(":supplierName", $supplierName);