--- a/displayCategory.php +++ b/displayCategory.php @@ -56,13 +56,94 @@ show_stats($stats); + $query = 'SELECT contractnotice."SONID", min(title) AS title, count(*), sum(value) AS value FROM contractnotice INNER JOIN standingoffers ON contractnotice."SONID" = standingoffers."SONID" +WHERE category = :category AND ' . " $yearQ $standardQ " . 'AND contractnotice."SONID" != \'\' GROUP BY contractnotice."SONID"'; + $query = $conn->prepare($query); + $query->bindParam(":category", $_REQUEST['category']); + $query->execute(); + databaseError($conn->errorInfo()); + $sonrows = ""; + foreach ($query->fetchAll() as $row) { + setlocale(LC_MONETARY, 'en_US'); + $value = number_format(doubleval($row['value']), 2); + $sonrows .= "<tr> + <td><B><a href=\"displaySON.php?SONID={$row['SONID']}\">{$row['title']}</a></b></td> + <td>{$row['count']}</td> + <td>\$$value</td> + </tr>"; + } + if (strlen($sonrows) > 1) { + echo "<h3>Standing Offers/Panels</h3><table> <thead> + <tr> + <th>Standing Offer</th> + <th>Contracts Count</th> + <th>Total Contract Value</th> + </tr> + </thead>$sonrows</table><br/>"; + } + + $query = 'SELECT SUM(value) AS value, count(*), text_mode("supplierName") "supplierName", min("supplierABN") AS "supplierABN", ( + CASE WHEN "supplierABN" != 0 THEN "supplierABN"::TEXT ELSE lower("supplierName") END) AS "supplierID" FROM contractnotice +WHERE ' . $yearQ . " " . $standardQ . ' AND category = :category +GROUP BY "supplierID" ORDER BY "value" DESC'; + $query = $conn->prepare($query); + $query->bindParam(":category", $_REQUEST['category']); + $query->execute(); + databaseError($conn->errorInfo()); + echo "<h3>Suppliers</h3><table> <thead> + <tr> + <th>Supplier</th> + <th>Contracts Count</th> + <th>Total Contract Value</th> + </tr> + </thead>"; + foreach ($query->fetchAll() as $row) { + setlocale(LC_MONETARY, 'en_US'); + $value = number_format(doubleval($row['value']), 2); + $supplierID = $row['supplierABN'] . '-' . urlencode($row['supplierName']); + echo("<tr> + <td><B><a href=\"displaySupplier.php?supplier=$supplierID\" >{$row['supplierName']}</a></b></td> + <td>{$row['count']}</td> + <td>\$$value</td> + </tr>"); + } + echo "</table><br/>"; + + $query = 'SELECT SUM("value") AS "value", count(*), "agencyName" AS agencyname FROM contractnotice +WHERE ' . $yearQ . ' "childCN" IS NULL AND category = :category GROUP BY "agencyName" ORDER BY SUM("value") DESC'; + + $query = $conn->prepare($query); + $query->bindParam(":category", $_REQUEST['category']); + $query->execute(); + databaseError($conn->errorInfo()); + + echo "<h3>Agencies</h3><table> <thead> + <tr> + <th>Agency</th> + <th>Contracts Count</th> + <th>Total Contract Value</th> + </tr> + </thead>"; + foreach ($query->fetchAll() as $row) { + setlocale(LC_MONETARY, 'en_US'); + $value = number_format(doubleval($row['value']), 2); + $agency =urlencode($row['agencyname']); + echo("<tr> + <td><B><a href=\"displayAgency.php?agency=$agency\">{$row['agencyname']}</a></b></td> + <td>{$row['count']}</td> + <td>\$$value</td> + </tr>"); + } + echo "</table><br/>"; + + $query = 'SELECT "CNID", description, value, "agencyName", category, "contractStart", "supplierName" FROM contractnotice WHERE ' . $yearQ . ' "childCN" IS NULL - AND CATEGORY = :CATEGORY + AND category = :category ORDER BY VALUE DESC'; $query = $conn->prepare($query); - $query->bindParam(":CATEGORY", $_REQUEST['category']); + $query->bindParam(":category", $_REQUEST['category']); $query->execute(); databaseError($conn->errorInfo());