category breakdown
[contractdashboard.git] / displayCategory.php
blob:a/displayCategory.php -> blob:b/displayCategory.php
--- 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());