--- a/displaySupplier.php
+++ b/displaySupplier.php
@@ -1,41 +1,183 @@
prepare($query);
-
-//$query->bindParam(":supplierName", $supplierName);
-$query->bindParam(":supplierABN", $supplierABN);
- $query->execute();
- if (!$query) {
- databaseError($conn->errorInfo());
- }
-echo '';
- echo '';
-
- echo "
+ $supplierS = htmlentities(strip_tags($_REQUEST['supplier']));
+ $title = $supplierName;
+ 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();
+ databaseError($conn->errorInfo());
+ $title = reset($query->fetchAll())['supplierName'];
+ }
+ include_header(str_replace("%", "", $title));
+ echo '' . str_replace("%", "", $title) . '
';
+
+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());
+ $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
+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' . $supplierQ . " " . $yearQ . " " .$standardQ ;
+ $query = $conn->prepare($query);
+ if (isset($supplierABN)) {
+ $query->bindParam(":supplierABN", $supplierABN);
+ } else {
+ $query->bindParam(":supplierName", $supplierName);
+ }
+ $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 ' . $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);
+ }
+ $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 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 = '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());
+
+ 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);
+ $agency =urlencode($row['agencyname']);
+ echo("
+ {$row['agencyname']} |
+ {$row['count']} |
+ \$$value |
+
");
+ }
+ echo "
";
+
+ $query = 'SELECT contractnotice."SONID", min(title) AS title, count(*), sum(value) AS value FROM contractnotice INNER JOIN standingoffers ON contractnotice."SONID" = standingoffers."SONID" WHERE ' . $yearQ . ' ' .
+ $supplierQ . ' ' . $standardQ
+ . ' AND contractnotice."SONID" != \'\' GROUP BY contractnotice."SONID"';
+ $query = $conn->prepare($query);
+
+
+ if (isset($supplierABN)) {
+ $query->bindParam(":supplierABN", $supplierABN);
+ } else {
+ $query->bindParam(":supplierName", $supplierName);
+ }
+ $query->execute();
+ databaseError($conn->errorInfo());
+$sonrows = "";
+ foreach ($query->fetchAll() as $row) {
+ setlocale(LC_MONETARY, 'en_US');
+ $value = number_format(doubleval($row['value']), 2);
+ $sonrows .= "
+ {$row['title']} |
+ {$row['count']} |
+ \$$value |
+
";
+ }
+ if (strlen($sonrows) > 1) {
+ echo "Standing Offers/Panels
+
+ Standing Offer |
+ Contracts Count |
+ Total Contract Value |
+
+ $sonrows
";
+ }
+ echo "Contracts
Contract Notice Number |
Contract Description |
@@ -45,52 +187,66 @@
Supplier |
";
- foreach ($query->fetchAll() as $row) {
- setlocale(LC_MONETARY, 'en_US');
- $value = number_format(doubleval($row['value']) , 2);
- echo ("
+ $query = 'SELECT "CNID", "description", "value", "agencyName", "category",
+ "contractStart", "supplierName"
+ FROM contractnotice WHERE ' . $yearQ . ' ' .
+ $supplierQ . ' ' . $standardQ
+ . ' ORDER BY VALUE DESC';
+
+ $query = $conn->prepare($query);
+
+
+ if (isset($supplierABN)) {
+ $query->bindParam(":supplierABN", $supplierABN);
+ } else {
+ $query->bindParam(":supplierName", $supplierName);
+ }
+ $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 {
/*
- histograph of supplier size/value
- */
-include_header("Suppliers");
-suppliersGraph();
- $query = 'SELECT SUM("value") as val, MAX("supplierName") as supplierName, "supplierABN",(
- case when "supplierABN" != 0 THEN "supplierABN"::text ELSE "supplierName" END) as supplierID
+ histograph of supplier size/value
+ */
+ include_header("Suppliers");
+ suppliersGraph();
+ $query = 'SELECT SUM("value") AS val, text_mode("supplierName") AS supplierName, "supplierABN",(
+ CASE WHEN "supplierABN" != 0 THEN lower("supplierABN"::TEXT) ELSE lower("supplierName") END) AS supplierID
FROM contractnotice
-WHERE "childCN" = 0
+WHERE ' . $yearQ . ' "childCN" IS NULL
GROUP BY supplierID,"supplierABN"
ORDER BY val DESC
LIMIT 100';
-$query = $conn->prepare($query);
- $query->execute();
- if (!$query) {
- databaseError($conn->errorInfo());
- }
- echo "
+ $query = $conn->prepare($query);
+ $query->execute();
+ databaseError($conn->errorInfo());
+ echo "
Position |
Supplier |
Total Contract Value |
";
-$i = 1;
- foreach ($query->fetchAll() as $row) {
- setlocale(LC_MONETARY, 'en_US');
- $value = number_format(doubleval($row['val']) , 2);
- $supplier = stripslashes($row['supplierABN'].'-'.$row['suppliername']);
- echo ("$i | ".ucsmart($row['suppliername'])." | \$$value |
\n");
- $i++;
- }
- echo "
";
+ $i = 1;
+ foreach ($query->fetchAll() as $row) {
+ setlocale(LC_MONETARY, 'en_US');
+ $value = number_format(doubleval($row['val']), 2);
+ $supplier = stripslashes($row['supplierABN'] . '-' . $row['suppliername']);
+ echo("$i | " . ucsmart($row['suppliername']) . " | \$$value |
\n");
+ $i++;
+ }
+ echo "
";
}
include_footer();
?>