More MySQL purging
[contractdashboard.git] / displayConfidentialities.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
<?php
 
include_once("./lib/common.inc.php");
/*
 
 
agencies that use alot of Confidentialities
suppliers that provide alot
how much is spent overall per year
*/
 
include_header("Confidentialities");
$query = '
SELECT SUM(value) as value,count(*), reason
FROM 
(select value, unnest(string_to_array(replace("confidentialityContractReason",\'Other - \',\'\'),\';\')) reason from contractnotice where "confidentialityContractReason" != \'\' and "confidentialityContractReason" != "confidentialityOutputsReason" and '.$yearQ.' '.$standardQ.'
union select value, unnest(string_to_array(replace("confidentialityOutputsReason",\'Other - \',\'\'),\';\')) reason from contractnotice where "confidentialityOutputsReason" != \'\' and "confidentialityContractReason" != "confidentialityOutputsReason" and '.$yearQ.' '.$standardQ.'
union select value, unnest(string_to_array(replace("confidentialityOutputsReason",\'Other - \',\'\'),\';\')) reason from contractnotice where "confidentialityOutputsReason" != \'\' and "confidentialityContractReason" = "confidentialityOutputsReason" and '.$yearQ.' '.$standardQ.') a
GROUP BY reason ORDER BY value desc
';
 
    $query = $conn->prepare($query);
    $query->execute();
    databaseError($conn->errorInfo());
 
echo "<table>";
    foreach ($query->fetchAll() as $row) {
setlocale(LC_MONETARY, 'en_US');
$value = number_format(doubleval($row[0]),2);
    echo ("<tr><td><b>{$row[2]}</b></td><td>{$row[1]} contract".($row[1] >1 ? 's':'')."<td>\$$value</td></tr>");
}
echo "</table>";
include_footer();
?>