|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 | <?php include_once ("./lib/common.inc.php"); if ($_REQUEST['agency']) { $agency = htmlentities(strip_tags($_REQUEST['agency'])); include_header($agency); echo '<center><h1>'.$agency.'</h1></center>'; $query = 'SELECT category, min("categoryUNSPSC") AS "categoryUNSPSC", count(*) AS count, sum(value) AS value FROM contractnotice WHERE "agencyName" like :agency AND '. " " . $standardQ . ' GROUP BY category order by count(*) desc limit 10'; $query = $conn->prepare($query); $query->bindParam(":agency", $agency); $query->execute(); databaseError($conn->errorInfo()); echo "<h3>Categories</h3><table> <thead> <tr> <th>Category</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); $category = urlencode($row['category']); echo("<tr> <td><B><a href='displayCategory.php?category=$category'>{$row['category']}</a></b></td> <td>{$row['count']}</td> <td>\$$value</td> </tr>"); } echo "</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 "agencyName" like :agency GROUP BY "supplierID" ORDER BY "value" DESC'; $query = $conn->prepare($query); $query->bindParam(":agency", $agency); $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/>"; /* * Categories table * SONs/Panels used table * Replace pie chart with percent count/count/percent value/value table * 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" FROM contractnotice WHERE ' .$yearQ . ' "agencyName" like :agency and "childCN" is null ORDER BY "value" DESC limit 100'; $query = $conn->prepare($query); $query->bindParam(":agency", $agency); $query->execute(); databaseError($conn->errorInfo()); echo "<h3>Contracts</h3><table> <thead> <tr> <th>Contract Notice Number</th> <th>Contract Description</th> <th>Total Contract Value</th> <th>Agency</th> <th>Contract Start Date</th> <th>Supplier</th> </tr> </thead>"; foreach ($query->fetchAll() as $row) { setlocale(LC_MONETARY, 'en_US'); $value = number_format(doubleval($row['value']), 2); echo ("<tr> <td><a href=\"displayContract.php?CNID={$row['CNID']}\">{$row['CNID']}</a></td> <td><b>{$row['description']}</b></a></td> <td>\$$value</td><td>{$row['agencyName']}</td> <td>{$row['contractStart']}</td> <td>{$row['supplierName']}</td> </tr>"); } echo "</table>"; } else { /* split by portfolio */ include_header("Agencies"); agenciesGraph(); $query = 'SELECT SUM("value"), "agencyName" FROM contractnotice WHERE ' .$yearQ . ' "childCN" is null GROUP BY "agencyName" '; $query = $conn->prepare($query); $query->execute(); databaseError($conn->errorInfo()); echo "<table> <thead> <tr> <th>Agency</th> <th>Total Contracts Value</th> </tr> </thead>"; foreach ($query->fetchAll() as $row) { setlocale(LC_MONETARY, 'en_US'); $value = number_format(doubleval($row[0]), 2); $agency = stripslashes($row[1]); echo ("<tr><td><b><a href=\"displayAgency.php?agency={$agency}\">{$agency}</a></b></td><td>\$$value</td></tr>\n"); } echo "</table>"; } include_footer(); ?> |