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 | <?php include_once("./lib/common.inc.php"); include_header("Months and Years"); if ($_REQUEST['month']) { echo "<center><h1>" . $_REQUEST['month'] . "</h1></center>"; $monthParts = explode("-", $_REQUEST['month']); $query = 'SELECT "CNID", "description", "value", "agencyName", "category", "contractStart", "supplierName" FROM contractnotice WHERE "childCN" is null AND extract(year from "contractStart") = :year AND extract(month from "contractStart") = :month ORDER BY value DESC'; $query = $conn->prepare($query); $query->bindParam(":month", $monthParts[0]); $query->bindParam(":year", $monthParts[1]); $query->execute(); databaseError($conn->errorInfo()); MethodCountGraph($supplier); CnCGraph($supplier); echo "<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 year todo: Year/Month drilldown - largest contracts, agencies, suppliers count per month big picture graphs? */ echo '<img src="graphs/displayContractStartingGraph.php">'; echo '<img src="graphs/displayContractPublishedGraph.php">'; $query = 'SELECT extract(year from "contractStart"), extract(month from "contractStart"), SUM(value) as val, count(1) as count FROM contractnotice WHERE "childCN" is null GROUP BY extract(year from "contractStart"), extract(month from "contractStart") ORDER BY extract(year from "contractStart"), extract(month from "contractStart") '; $query = $conn->prepare($query); $query->execute(); databaseError($conn->errorInfo()); echo "<table> <thead> <tr> <th>Month/Year</th> <th>Total Contracts Value</th> <th>Number of Contracts</th> </tr> </thead>"; foreach ($query->fetchAll() as $row) { setlocale(LC_MONETARY, 'en_US'); $value = number_format(doubleval($row["val"]), 2); $month_name = date('F', mktime(0, 0, 0, $row[1])); echo ("<tr><td><b><a href=\"?month=$row[1]-$row[0]\">$month_name {$row[0]}</a></b></td><td>\$$value</td><td>({$row['count']} contracts)</td></tr>"); } echo "</table>"; } include_footer(); ?> |