forked from Crell/joindinaudit
/
report.php
73 lines (53 loc) · 1.83 KB
/
report.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
<?php
require 'vendor/autoload.php';
use Crell\HtmlModel\Head\StyleElement;
use Crell\HtmlModel\HtmlPage;
/**
* Generates an HTML report of the data.
*/
function report()
{
$page = (new HtmlPage())
->withTitle('Conference audit')
->withInlineStyle(new StyleElement(file_get_contents('styles.css')));
// Generate the results page.
$table = reportNewSpeakersPerCon();
$page = $page->withContent($page->getContent() . $table);
$table = reportTopSpeakers();
$page = $page->withContent($page->getContent() . $table);
file_put_contents('results.html', $page);
}
function reportTopSpeakers()
{
$conn = getDb();
$stmt = $conn->executeQuery("SELECT speaker, COUNT(speaker) AS appearances
FROM talk
WHERE speaker <> ''
GROUP BY speaker
HAVING appearances >= 20
ORDER BY appearances DESC, speaker DESC");
$header = ['Speaker', 'Appearances (since 2011)'];
return makeHtmlTable('Most frequent speakers', $header, $stmt->fetchAll());
}
function reportNewSpeakersPerCon()
{
$conn = getDb();
$sql = "SELECT event.start_date, event.name, talks_count, num_speakers, new_speakers, FORMAT((new_speakers/event.num_speakers)*100, 1) AS percent_new
FROM event
WHERE start_date >= '2010-01-01'
ORDER BY start_date";
$stmt = $conn->executeQuery($sql);
$rows = $stmt->fetchAll();
$header = [
'Date',
'Event',
'Total sessions',
'Speakers',
'New speakers',
'Percent new'
];
$stmt = $conn->executeQuery("SELECT 'N/A', 'Average', FORMAT(AVG(talks_count), 1), FORMAT(AVG(num_speakers), 1), FORMAT(AVG(new_speakers), 1), FORMAT(AVG(percent_new), 1) FROM ({$sql}) AS stuff");
$averages = $stmt->fetch();
return makeHtmlTable('First time speakers', $header, $rows, $averages);
}
report();