Include textual subcategories in openspending/csv export
[contractdashboard.git] / displayCalendar.php
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();
?>