--- a/displayCategory.php
+++ b/displayCategory.php
@@ -1,60 +1,118 @@
-
".$_REQUEST['category']."
";
- $query = "SELECT CNID, description, value, agencyName, category, contractStart, supplierName
- FROM `contractnotice`
- WHERE childCN is null
- AND category = '" . $_REQUEST['category'] . "'
- ORDER BY value DESC";
- $result = mysql_query($query);
- echo "
-
- Contract Notice Number |
- Contract Description |
- Total Contract Value |
- Agency |
- Contract Start Date |
- Supplier |
-
- ";
- while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
- setlocale(LC_MONETARY, 'en_US');
- $value = number_format(doubleval($row['value']) , 2);
- echo ("
- {$row['CNID']} |
- {$row['description']} |
- \$$value | {$row['agencyName']} |
- {$row['contractStart']} |
- {$row['supplierName']} |
-
");
- }
- echo "
";
- mysql_free_result($result);
-} else {
- /*
- split by main categories
- */
- include_header("Categories");
-$query = "SELECT sum(value), category
-FROM `contractnotice`
-WHERE childCN is null
-GROUP BY category ORDER BY sum(value) DESC ";
-$result = mysql_query($query);
- echo "
-
- Category |
- Total Contracts Value |
-
- ";
-while ($row = mysql_fetch_array($result, MYSQL_BOTH)) {
- setlocale(LC_MONETARY, 'en_US');
- $value = number_format(doubleval($row[0]) , 2);
- echo ("{$row[1]} | \$$value |
");
-}
-echo "
";
-mysql_free_result($result);
-}
-include_footer();
+prepare($query);
+ $query->bindParam(":category", $_REQUEST['category']);
+ $query->execute();
+ databaseError($conn->errorInfo());
+ $unspsc = Array();
+ foreach ($query->fetchAll() as $d) {
+ $unspsc[$d['key']] = $d;
+ }
+
+
+ include_header($_REQUEST['category'] . " goods and services");
+ if ($unspsc['categoryUNSPSC']) {
+ echo "{$_REQUEST['category']} ({$unspsc['categoryUNSPSC']['UNSPSC']})
";
+
+ if ($unspsc['parentUNSPSC']['UNSPSC'] != $unspsc['categoryUNSPSC']['UNSPSC']) {
+ echo "Parent Category: {$unspsc['parentUNSPSC']['UNSPSC']} {$unspsc['parentUNSPSC']['category']}
";
+ }
+ if ($unspsc['grandparentUNSPSC']['UNSPSC'] != $unspsc['categoryUNSPSC']['UNSPSC']) {
+ echo "Grandparent Category: {$unspsc['grandparentUNSPSC']['UNSPSC']} {$unspsc['grandparentUNSPSC']['category']}";
+ }
+
+ } else {
+ echo "{$_REQUEST['category']}
";
+ }
+
+ $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 category = :category AND ' . " $yearQ $standardQ ";
+ $query = $conn->prepare($query);
+ $query->bindParam(":category", $_REQUEST['category']);
+ $query->execute();
+ databaseError($conn->errorInfo());
+
+ $stats = reset($query->fetchAll());
+ show_stats($stats);
+
+
+ $query = 'SELECT "CNID", description, value, "agencyName", category, "contractStart", "supplierName"
+ FROM contractnotice
+ WHERE ' . $yearQ . ' "childCN" IS NULL
+ AND CATEGORY = :CATEGORY
+ ORDER BY VALUE DESC';
+ $query = $conn->prepare($query);
+ $query->bindParam(":CATEGORY", $_REQUEST['category']);
+ $query->execute();
+ databaseError($conn->errorInfo());
+
+ echo "
+
+ Contract Notice Number |
+ Contract Description |
+ Total Contract Value |
+ Agency |
+ Contract Start Date |
+ Supplier |
+
+ ";
+ foreach ($query->fetchAll() as $row) {
+ setlocale(LC_MONETARY, 'en_US');
+ $value = number_format(doubleval($row['value']), 2);
+ echo("
+ {$row['CNID']} |
+ {$row['description']} |
+ \$$value | {$row['agencyName']} |
+ {$row['contractStart']} |
+ {$row['supplierName']} |
+
");
+ }
+ echo "
";
+} else {
+ /*
+ split by main categories
+ */
+ include_header("Categories");
+ $query = 'SELECT sum(value), category
+FROM contractnotice
+WHERE ' . $yearQ . ' "childCN" IS NULL
+GROUP BY CATEGORY ORDER BY sum(VALUE) DESC ';
+ $query = $conn->prepare($query);
+ $query->execute();
+ databaseError($conn->errorInfo());
+ echo "
+
+ Category |
+ Total Contracts Value |
+
+ ";
+ foreach ($query->fetchAll() as $row) {
+ setlocale(LC_MONETARY, 'en_US');
+ $value = number_format(doubleval($row[0]), 2);
+ echo("{$row[1]} | \$$value |
");
+ }
+ echo "
";
+}
+include_footer();
?>
+