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)) {
$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));
}
$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 |
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 (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 "
";
} 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();
?>