fix supplier ID, add total value stat
[contractdashboard.git] / displaySON.php
blob:a/displaySON.php -> blob:b/displaySON.php
--- a/displaySON.php
+++ b/displaySON.php
@@ -20,6 +20,7 @@
 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 "SONID" = :SONID AND ' . " $yearQ $standardQ "   ;
@@ -84,8 +85,10 @@
     }
     echo "</table><br/>";
 
-    $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"
+    $query = 'SELECT "supplierABN", text_mode("supplierName") AS "supplierName", count(*), sum(value) AS value,
+ (CASE WHEN "supplierABN" != 0 THEN "supplierABN"::TEXT ELSE lower("supplierName") END) AS "supplierID"
+FROM contractnotice
+ WHERE ' . $yearQ . ' "childCN" IS NULL AND "SONID" = :SONID GROUP BY "supplierID"
  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);