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 | <?php include_once("./lib/common.inc.php"); if ($_REQUEST['month']) { $monthParts = explode("-", str_replace(Array("'","/","\\"), "",$_REQUEST['month'])); $year = str_replace('-', '',$monthParts[1]); $month = str_replace('-', '',$monthParts[0]); $monthName = date("F Y", mktime(0,0,0,$month,1,$year)); include_header($monthName); echo "<center><h1>" . $monthName . "</h1></center>"; $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? */ include_header("Months and Years"); ContractStartingGraph(); ContractPublishedGraph(); $query = 'SELECT extract(year from "contractStart"), extract(month from "contractStart"), SUM(value) as val, count(1) as count FROM contractnotice WHERE '.$yearQ.' "childCN" is null GROUP BY extract(year from "contractStart"), extract(month from "contractStart") ORDER BY extract(year from "contractStart") asc, extract(month from "contractStart") asc'; $query = $conn->prepare($query); $query->execute(); databaseError($conn->errorInfo()); echo "<table> <thead> <tr> <th>Num</th> <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>'.$row[0].'-'.$row[1].'</td><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(); ?> |