prepare($query);
$query->bindParam(":SONID", $_REQUEST['SONID']);
$query->execute();
databaseError($conn->errorInfo());
$title = reset($query->fetchAll())['title'];
include_header($title);
echo "
" . $_REQUEST['SONID'] . " - " . $title . "
";
$query = 'SELECT category, min("categoryUNSPSC") AS "categoryUNSPSC", count(*) AS count, sum(value) AS value
FROM contractnotice
WHERE "SONID" = :SONID
GROUP BY category';
$query = $conn->prepare($query);
$query->bindParam(":SONID", $_REQUEST['SONID']);
$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 "supplierABN", text_mode("supplierName") AS "supplierName", count(*), sum(value) AS value FROM contractnotice
WHERE ' . $yearQ . ' "childCN" IS NULL AND "SONID" = :SONID GROUP BY "supplierABN"
UNION SELECT abn AS "supplierABN",name AS "supplierName",0 AS sum,0 AS value FROM standingoffer_suppliers
WHERE "SONID" = :SONID AND abn NOT IN (SELECT DISTINCT "supplierABN" AS abn FROM contractnotice WHERE "SONID" = :SONID)';
$query = $conn->prepare($query);
$query->bindParam(":SONID", $_REQUEST['SONID']);
$query->execute();
databaseError($conn->errorInfo());
echo '
View original record @ tenders.gov.au
';
echo "Suppliers
Supplier |
Contracts Count |
Total Contract Value |
";
foreach ($query->fetchAll() as $row) {
setlocale(LC_MONETARY, 'en_US');
$value = number_format(doubleval($row['value']), 2);
$supplier = stripslashes($row['supplierABN'] . '-' . $row['supplierName']);
echo("
{$row['supplierName']} |
{$row['count']} |
\$$value |
");
}
echo "
Contracts
Contract Notice Number |
Contract Description |
Total Contract Value |
Agency |
Contract Start Date |
Supplier |
";
$query = 'SELECT "CNID", title, description, value, "agencyName", contractnotice."SONID", "contractStart", "supplierName"
FROM contractnotice INNER JOIN standingoffers ON contractnotice."SONID" = standingoffers."SONID"
WHERE ' . $yearQ . ' "childCN" IS NULL
AND contractnotice."SONID" = :SONID
ORDER BY VALUE DESC';
$query = $conn->prepare($query);
$query->bindParam(":SONID", $_REQUEST['SONID']);
$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 {
/*
split by main categories
*/
include_header("Standing Offers");
$query = 'SELECT sum(value), count(*), contractnotice."SONID", min(title) AS title
FROM contractnotice INNER JOIN standingoffers ON contractnotice."SONID" = standingoffers."SONID"
WHERE ' . $yearQ . ' "childCN" IS NULL AND contractnotice."SONID" != \'\'
GROUP BY contractnotice."SONID" ORDER BY sum(VALUE) DESC ';
$query = $conn->prepare($query);
$query->execute();
databaseError($conn->errorInfo());
echo "
SONID |
Total Contracts Value |
Number of Contracts |
";
foreach ($query->fetchAll() as $row) {
setlocale(LC_MONETARY, 'en_US');
$value = number_format(doubleval($row['sum']), 2);
echo("{$row['title']} | \$$value | {$row['count']} |
");
}
echo "
";
}
include_footer();