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 ($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));
}
/*
* Procurement method in text, seperate Open via SON from Open
* Consultancies, Confidentialities percentage
*/
$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) {
$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(*), MAX(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) {
$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 ($supplierParts[0] > 0) {
$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 |
Total Contract Value |
Agency |
Contract Start Date |
Supplier |
";
$query = 'SELECT "CNID", "description", "value", "agencyName", "category",
"contractStart", "supplierName"
FROM contractnotice WHERE ' . $yearQ . ' ' .
$supplierQ . ' ' . $standardQ
. ' ORDER BY VALUE DESC';
$query = $conn->prepare($query);
if ($supplierParts[0] > 0) {
$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 "
";
} else {
/*
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 ' . $yearQ . ' "childCN" IS NULL
GROUP BY supplierID,"supplierABN"
ORDER BY val DESC
LIMIT 100';
$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 "
";
}
include_footer();
?>