--- a/displayAgency.php
+++ b/displayAgency.php
@@ -6,14 +6,37 @@
$agency = htmlentities(strip_tags($_REQUEST['agency']));
include_header($agency);
echo '
' . $agency . '
';
- $query = 'SELECT category, min("categoryUNSPSC") AS "categoryUNSPSC", count(*) AS count, sum(value) AS value FROM contractnotice
- WHERE "agencyName" LIKE :agency AND ' . " " . $standardQ . ' ' . $yearQ . ' GROUP BY category ORDER BY count(*) DESC LIMIT 10';
+
+ $query = '
+ SELECT
+sum((consultancy = \'Yes\')::int) AS consultancy,
+sum(("confidentialityContract" = \'Yes\')::int) AS "confidentialityContract",
+sum(("confidentialityOutputs" = \'Yes\')::int) AS "confidentialityOutputs",
+sum((("procurementMethod" = \'Open\' OR "procurementMethod" = \'Open tender\') AND "SONID" IS null)::int) AS open,
+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 "agencyName" LIKE :agency AND ' . " $yearQ $standardQ " ;
$query = $conn->prepare($query);
$query->bindParam(":agency", $agency);
$query->execute();
databaseError($conn->errorInfo());
+ $stats = reset($query->fetchAll());
+ show_stats($stats);
+ /*
+ Compliance statistics: amendments, delay in reporting average and number completely late */
+
+ $query = 'SELECT category, min("categoryUNSPSC") AS "categoryUNSPSC", count(*) AS count, sum(value) AS value FROM contractnotice
+ WHERE "agencyName" LIKE :agency AND ' . " $yearQ $standardQ " . ' GROUP BY category ORDER BY count(*) DESC LIMIT 10';
+ $query = $conn->prepare($query);
+ $query->bindParam(":agency", $agency);
+ $query->execute();
+ databaseError($conn->errorInfo());
echo "Categories
Category |
@@ -34,7 +57,7 @@
echo "
";
$query = 'SELECT contractnotice."SONID", min(title) AS title, count(*), sum(value) AS value FROM contractnotice INNER JOIN standingoffers ON contractnotice."SONID" = standingoffers."SONID"
-WHERE "agencyName" LIKE :agency AND ' . " " . $standardQ . ' ' . $yearQ . 'AND contractnotice."SONID" != \'\' GROUP BY contractnotice."SONID"';
+WHERE "agencyName" LIKE :agency AND ' . " $yearQ $standardQ " . 'AND contractnotice."SONID" != \'\' GROUP BY contractnotice."SONID"';
$query = $conn->prepare($query);
$query->bindParam(":agency", $agency);
$query->execute();
@@ -85,10 +108,6 @@
");
}
echo "
";
- /*
- * Procurement method in text, seperate Open via SON from Open
- * Consultancies, Confidentialities percentage
- Compliance statistics: amendments, delay in reporting average and number completely late */
$query = 'SELECT "CNID", "description", "value", "agencyName", "category",
"contractStart", "supplierName"