add sitemap
[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
85
86
87
88
89
90
<?php
 
include_once("./lib/common.inc.php");
 
 
 
if ($_REQUEST['month']) {
    $monthParts = explode("-", str_replace(Array("'","/","\\",'-'), "",$_REQUEST['month']));
    $year = $monthParts[1];
    $month = $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 "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();
?>