<?php
 
include_once("./lib/common.inc.php");
/*
 
 
agencies that use alot of consultacies
suppliers that provide alot
how much is spent overall per year
*/
 
include_header("Consultancies");
$query = '
SELECT SUM(value) as value, count(*), text_mode("supplierName"), unnest(string_to_array("consultancyReason",\';\')) reason
FROM contractnotice where ' .$yearQ . ' "consultancy" = \'Yes\' and "childCN" is null
GROUP BY "supplierABN",reason order by value desc
 ';
 
    $query = $conn->prepare($query);
    $query->execute();
    databaseError($conn->errorInfo());
 
echo "<table>";
    foreach ($query->fetchAll() as $row) {
setlocale(LC_MONETARY, 'en_US');
$value = number_format(doubleval($row[0]),2);
    echo ("<tr><td><b>{$row[2]}</b></td><td>{$row[3]}</td><td>{$row[1]} contracts</td><td>\$$value</td></tr>");
}
echo "</table>";
include_footer();
?>