-
Notifications
You must be signed in to change notification settings - Fork 12
/
Copy pathhtml.php
337 lines (285 loc) · 26.8 KB
/
html.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
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
<?php declare(strict_types=1);
/**
* Copyright (c) 2007-2022, Jos de Ruijter <jos@dutnie.nl>
*/
/**
* Class for creating the main stats page.
*/
class html
{
use common, common_web, urlparts;
private bool $link_history_php = true;
private bool $link_user_php = true;
private bool $show_banner = true;
private bool $xxl = false;
private string $channel = 'unconfigured';
private string $favicon = 'favicon.svg';
private string $now = '';
private string $stylesheet = 'sss.css';
public function __construct()
{
$this->apply_vars('settings', ['channel', 'favicon', 'stylesheet', 'link_history_php', 'link_user_php', 'show_banner', 'xxl']);
}
/**
* Generate the HTML page.
*/
public function get_contents(): string
{
/**
* Stats require a non-empty dataset.
*/
if (db::query_single_col('SELECT EXISTS (SELECT 1 FROM channel_activity)') === 0) {
return '<!DOCTYPE html>'."\n\n".'<html lang="en"><head><meta charset="utf-8"><title>seriously?</title><link rel="stylesheet" href="'.$this->htmlify($this->stylesheet).'"></head><body><div id="container"><div id="error">There is not enough data to create statistics, yet.</div></div></body></html>'."\n";
}
/**
* Collect all details for this channel.
*/
$result = db::query_single_row('SELECT COUNT(*) AS days_logged, MIN(date) AS date_first_log_parsed, MAX(date) AS date_last_log_parsed FROM parse_history');
$days_logged = $result['days_logged'];
$date_first_log_parsed = $result['date_first_log_parsed'];
$date_last_log_parsed = $result['date_last_log_parsed'];
$this->now = $date_last_log_parsed;
$result = db::query_single_row('SELECT date, l_total FROM channel_activity ORDER BY l_total DESC, date ASC LIMIT 1');
$high_date = $result['date'];
$high_lines = $result['l_total'];
$l_total = db::query_single_col('SELECT SUM(l_total) FROM channel_activity');
$l_avg = (int) round($l_total / $days_logged);
/**
* HEAD
*/
$contents = '<!DOCTYPE html>'."\n\n"
. '<html lang="en">'."\n\n"
. '<head>'."\n"
. '<meta charset="utf-8">'."\n"
. '<meta name="referrer" content="no-referrer">'."\n"
. '<title>'.$this->htmlify($this->channel).', seriously.</title>'."\n"
. '<link rel="icon" href="'.$this->htmlify($this->favicon).'">'."\n"
. '<link rel="stylesheet" href="'.$this->htmlify($this->stylesheet).'">'."\n"
. '</head>'."\n\n"
. '<body><div id="container">'."\n"
. ($this->show_banner ? '<div id="bannerbg-top"></div><div id="bannerbg-bottom"></div><svg id="banner" viewBox="0 0 818 50"><path id="banner-text" d="M0 0h48v4h-46v21h46v25h-48v-2h46v-21h-46zm51 0h2v48h44v-48h2v50h-48zm51 0h48v27h-46v-2h44v-21h-44v46h-2zm51 0h48v27h-46v-2h44v-21h-44v44h46v2h-48zm51 0h46v25h2v25h-2v-23h-44v-2h42v-21h-42v46h-2zm51 0h48v4h-46v21h46v25h-48v-2h46v-21h-46zm51 0h48v27h-46v-2h44v-21h-44v44h46v2h-48zm51 0h46v25h2v25h-2v-23h-44v-2h42v-21h-42v46h-2zm51 0h10v4h-4v44h4v2h-10v-2h4v-44h-4zm13 0h48v50h-46v-2h44v-44h-44v46h-2zm51 0h2v48h44v-48h2v50h-48zm51 0h48v4h-46v21h46v25h-48v-2h46v-21h-46zm51 0h48v4h-46v21h46v25h-48v-2h46v-21h-46zm51 0h44v4h-21v46h-2v-46h-21zm47 0h48v50h-2v-23h-44v-2h44v-21h-44v46h-2zm51 0h44v4h-21v46h-2v-46h-21zm47 0h48v4h-46v21h46v25h-48v-2h46v-21h-46z"/><path class="banner-graph" style="fill:#7697cb" d="M428 19h8v25h-8z"/><path class="banner-graph" style="fill:#6c8fc2" d="M428 19h4v25h-4z"/><path class="banner-graph" style="fill:#416d9c" d="M428 19h8v25h-6v-2h4v-21h-4v23h-2z"/><path class="banner-graph" style="fill:#e17677" d="M441 27h8v17h-8z"/><path class="banner-graph" style="fill:#dc6c6d" d="M441 27h4v17h-4z"/><path class="banner-graph" style="fill:#c74243" d="M441 27h8v17h-6v-2h4v-13h-4v15h-2z"/><path class="banner-graph" style="fill:#8fce90" d="M454 11h8v33h-8z"/><path class="banner-graph" style="fill:#89c686" d="M454 11h4v33h-4z"/><path class="banner-graph" style="fill:#70a35e" d="M454 11h8v33h-6v-2h4v-29h-4v31h-2z"/></svg>'."\n" : '')
. '<div class="info">'.$this->htmlify($this->channel).', seriously.<br><br>'
. number_format($days_logged).' day'.($days_logged !== 1 ? 's logged from '.date('M j, Y', strtotime($date_first_log_parsed)).' to '.date('M j, Y', strtotime($date_last_log_parsed)) : ' logged on '.date('M j, Y', strtotime($date_first_log_parsed))).'.<br><br>'
. 'Logs contain '.number_format($l_total).' line'.($l_total !== 1 ? 's' : '').' – an average of '.number_format($l_avg).' line'.($l_avg !== 1 ? 's' : '').' per day.<br>'
. 'Most active day was '.date('M j, Y', strtotime($high_date)).' with a total of '.number_format($high_lines).' line'.($high_lines !== 1 ? 's' : '').' typed.</div>'."\n";
/**
* CONTENT
*/
$contents .= '<div class="section">Activity</div>'."\n";
$contents .= $this->create_table_activity_distribution_hour();
$contents .= $this->create_table_activity('day');
$contents .= $this->create_table_activity('month');
$contents .= $this->create_table_activity('year');
$contents .= $this->create_table_activity_distribution_day();
$contents .= $this->create_table_people();
$contents .= $this->create_table_people2();
$contents .= $this->create_table_people_timeofday();
/**
* Avoid displaying two identical tables.
*/
$show_table_year = false;
$show_table_month = false;
if (db::query_single_col('SELECT COUNT(DISTINCT date) FROM ruid_activity_by_year') > 1) {
$show_table_year = true;
$contents .= $this->create_table_people('year');
}
if (db::query_single_col('SELECT COUNT(DISTINCT date) FROM ruid_activity_by_month WHERE date LIKE \''.substr($this->now, 0, 4).'%\'') > 1) {
$show_table_month = true;
$contents .= $this->create_table_people('month');
}
$contents .= $this->create_table_people_timeofday(true);
/**
* When we are generating an XXL stats page override the default table size for
* specific generic tables below.
*/
$rows = ($this->xxl ? 10 : 5);
/**
* Build the "General Chat" section.
*/
$section = '';
$section .= $this->create_table('Most Talkative Chatters', ['Lines/Day', 'User'], ['num1', 'str'], ['SELECT CAST(l_total AS REAL) / activedays AS v1, csnick AS v2 FROM ruid_lines JOIN uid_details ON ruid_lines.ruid = uid_details.uid WHERE status NOT IN (3,4) AND activedays >= 7 AND lasttalked >= DATETIME(\''.$this->now.'\', \'-30 day\') ORDER BY v1 DESC, ruid_lines.ruid ASC LIMIT '.$rows], $rows);
$section .= $this->create_table('Most Fluent Chatters', ['Words/Line', 'User'], ['num1', 'str'], ['SELECT CAST(words AS REAL) / l_total AS v1, csnick AS v2 FROM ruid_lines JOIN uid_details ON ruid_lines.ruid = uid_details.uid WHERE status NOT IN (3,4) AND activedays >= 7 AND lasttalked >= DATETIME(\''.$this->now.'\', \'-30 day\') ORDER BY v1 DESC, ruid_lines.ruid ASC LIMIT '.$rows], $rows);
$section .= $this->create_table('Most Tedious Chatters', ['Chars/Line', 'User'], ['num1', 'str'], ['SELECT CAST(characters AS REAL) / l_total AS v1, csnick AS v2 FROM ruid_lines JOIN uid_details ON ruid_lines.ruid = uid_details.uid WHERE status NOT IN (3,4) AND activedays >= 7 AND lasttalked >= DATETIME(\''.$this->now.'\', \'-30 day\') ORDER BY v1 DESC, ruid_lines.ruid ASC LIMIT '.$rows], $rows);
$section .= $this->create_table('Individual Top Days – All-Time', ['Lines', 'User'], ['num', 'str'], ['SELECT MAX(l_total) AS v1, csnick AS v2 FROM ruid_activity_by_day JOIN uid_details ON ruid_activity_by_day.ruid = uid_details.uid WHERE status NOT IN (3,4) GROUP BY ruid_activity_by_day.ruid ORDER BY v1 DESC, ruid_activity_by_day.ruid ASC LIMIT '.$rows], $rows);
if ($show_table_year) {
$section .= $this->create_table('Individual Top Days – '.substr($this->now, 0, 4), ['Lines', 'User'], ['num', 'str'], ['SELECT MAX(l_total) AS v1, csnick AS v2 FROM ruid_activity_by_day JOIN uid_details ON ruid_activity_by_day.ruid = uid_details.uid WHERE status NOT IN (3,4) AND date LIKE \''.substr($this->now, 0, 4).'%\' GROUP BY ruid_activity_by_day.ruid ORDER BY v1 DESC, ruid_activity_by_day.ruid ASC LIMIT '.$rows], $rows);
}
if ($show_table_month) {
$section .= $this->create_table('Individual Top Days – '.date('F Y', strtotime($this->now)), ['Lines', 'User'], ['num', 'str'], ['SELECT MAX(l_total) AS v1, csnick AS v2 FROM ruid_activity_by_day JOIN uid_details ON ruid_activity_by_day.ruid = uid_details.uid WHERE status NOT IN (3,4) AND date LIKE \''.substr($this->now, 0, 7).'%\' GROUP BY ruid_activity_by_day.ruid ORDER BY v1 DESC, ruid_activity_by_day.ruid ASC LIMIT '.$rows], $rows);
}
$section .= $this->create_table('Most Active Chatters – All-Time', ['Activity', 'User'], ['num2%', 'str'], ['SELECT (CAST(activedays AS REAL) / '.$days_logged.') * 100 AS v1, csnick AS v2 FROM ruid_lines JOIN uid_details ON ruid_lines.ruid = uid_details.uid WHERE status NOT IN (3,4) AND activedays != 0 ORDER BY v1 DESC, ruid_lines.ruid ASC LIMIT '.$rows], $rows);
if ($show_table_year) {
$section .= $this->create_table('Most Active Chatters – '.substr($this->now, 0, 4), ['Activity', 'User'], ['num2%', 'str'], ['SELECT (CAST(COUNT(DISTINCT date) AS REAL) / (SELECT COUNT(*) FROM parse_history WHERE date LIKE \''.substr($this->now, 0, 4).'%\')) * 100 AS v1, csnick AS v2 FROM ruid_activity_by_day JOIN uid_details ON ruid_activity_by_day.ruid = uid_details.uid WHERE status NOT IN (3,4) AND date LIKE \''.substr($this->now, 0, 4).'%\' GROUP BY ruid_activity_by_day.ruid ORDER BY v1 DESC, ruid_activity_by_day.ruid ASC LIMIT '.$rows], $rows);
}
if ($show_table_month) {
$section .= $this->create_table('Most Active Chatters – '.date('F Y', strtotime($this->now)), ['Activity', 'User'], ['num2%', 'str'], ['SELECT (CAST(COUNT(DISTINCT date) AS REAL) / (SELECT COUNT(*) FROM parse_history WHERE date LIKE \''.substr($this->now, 0, 7).'%\')) * 100 AS v1, csnick AS v2 FROM ruid_activity_by_day JOIN uid_details ON ruid_activity_by_day.ruid = uid_details.uid WHERE status NOT IN (3,4) AND date LIKE \''.substr($this->now, 0, 7).'%\' GROUP BY ruid_activity_by_day.ruid ORDER BY v1 DESC, ruid_activity_by_day.ruid ASC LIMIT '.$rows], $rows);
}
$section .= $this->create_table('Exclamations', ['Total', 'User', 'Example'], ['num', 'str', 'str'], ['SELECT exclamations AS v1, csnick AS v2, ex_exclamations AS v3 FROM ruid_lines JOIN uid_details ON ruid_lines.ruid = uid_details.uid WHERE status NOT IN (3,4) AND exclamations != 0 ORDER BY v1 DESC, ruid_lines.ruid ASC LIMIT '.$rows, 'SELECT SUM(exclamations) FROM ruid_lines'], $rows);
$section .= $this->create_table('Questions', ['Total', 'User', 'Example'], ['num', 'str', 'str'], ['SELECT questions AS v1, csnick AS v2, ex_questions AS v3 FROM ruid_lines JOIN uid_details ON ruid_lines.ruid = uid_details.uid WHERE status NOT IN (3,4) AND questions != 0 ORDER BY v1 DESC, ruid_lines.ruid ASC LIMIT '.$rows, 'SELECT SUM(questions) FROM ruid_lines'], $rows);
$section .= $this->create_table('UPPERCASED Lines', ['Total', 'User', 'Example'], ['num', 'str', 'str'], ['SELECT uppercased AS v1, csnick AS v2, ex_uppercased AS v3 FROM ruid_lines JOIN uid_details ON ruid_lines.ruid = uid_details.uid WHERE status NOT IN (3,4) AND uppercased != 0 ORDER BY v1 DESC, ruid_lines.ruid ASC LIMIT '.$rows, 'SELECT SUM(uppercased) FROM ruid_lines'], $rows);
$section .= $this->create_table('Monologues', ['Total', 'User'], ['num', 'str'], ['SELECT monologues AS v1, csnick AS v2 FROM ruid_lines JOIN uid_details ON ruid_lines.ruid = uid_details.uid WHERE status NOT IN (3,4) AND monologues != 0 ORDER BY v1 DESC, ruid_lines.ruid ASC LIMIT '.$rows, 'SELECT SUM(monologues) FROM ruid_lines'], $rows);
$section .= $this->create_table('Longest Monologue', ['Lines', 'User'], ['num', 'str'], ['SELECT topmonologue AS v1, csnick AS v2 FROM ruid_lines JOIN uid_details ON ruid_lines.ruid = uid_details.uid WHERE status NOT IN (3,4) AND topmonologue != 0 ORDER BY v1 DESC, ruid_lines.ruid ASC LIMIT '.$rows], $rows);
$section .= $this->create_table('Moodiest People', ['Smileys', 'User'], ['num', 'str'], ['SELECT SUM(total) AS v1, csnick AS v2 FROM ruid_smileys JOIN uid_details ON ruid_smileys.ruid = uid_details.uid JOIN smileys ON ruid_smileys.sid = smileys.sid WHERE status NOT IN (3,4) AND category IS NOT NULL GROUP BY ruid_smileys.ruid ORDER BY v1 DESC, ruid_smileys.ruid ASC LIMIT '.$rows, 'SELECT SUM(total) FROM ruid_smileys JOIN smileys ON ruid_smileys.sid = smileys.sid WHERE category IS NOT NULL'], $rows);
$section .= $this->create_table('Slaps Given', ['Total', 'User'], ['num', 'str'], ['SELECT slaps AS v1, csnick AS v2 FROM ruid_lines JOIN uid_details ON ruid_lines.ruid = uid_details.uid WHERE status NOT IN (3,4) AND slaps != 0 ORDER BY v1 DESC, ruid_lines.ruid ASC LIMIT '.$rows, 'SELECT SUM(slaps) FROM ruid_lines'], $rows);
$section .= $this->create_table('Slaps Received', ['Total', 'User'], ['num', 'str'], ['SELECT slapped AS v1, csnick AS v2 FROM ruid_lines JOIN uid_details ON ruid_lines.ruid = uid_details.uid WHERE status NOT IN (3,4) AND slapped != 0 ORDER BY v1 DESC, ruid_lines.ruid ASC LIMIT '.$rows, 'SELECT SUM(slapped) FROM ruid_lines'], $rows);
$section .= $this->create_table('Most Lively Bots', ['Lines', 'Bot'], ['num', ($this->link_user_php ? 'str-userstats' : 'str')], ['SELECT l_total AS v1, csnick AS v2 FROM ruid_lines JOIN uid_details ON ruid_lines.ruid = uid_details.uid WHERE status = 3 AND l_total != 0 ORDER BY v1 DESC, ruid_lines.ruid ASC LIMIT '.$rows], $rows);
$section .= $this->create_table('Actions Performed', ['Total', 'User', 'Example'], ['num', 'str', 'str'], ['SELECT actions AS v1, csnick AS v2, ex_actions AS v3 FROM ruid_lines JOIN uid_details ON ruid_lines.ruid = uid_details.uid WHERE status NOT IN (3,4) AND actions != 0 ORDER BY v1 DESC, ruid_lines.ruid ASC LIMIT '.$rows, 'SELECT SUM(actions) FROM ruid_lines'], $rows);
if ($section !== '') {
$contents .= '<div class="section">General Chat</div>'."\n".$section;
}
/**
* Build the "Modes" section.
*/
$section = '';
$modes = [
'm_op' => 'Ops '+o' Given',
'm_opped' => 'Ops '+o' Received',
'm_deop' => 'deOps '-o' Given',
'm_deopped' => 'deOps '-o' Received',
'm_voice' => 'Voices '+v' Given',
'm_voiced' => 'Voices '+v' Received',
'm_devoice' => 'deVoices '-v' Given',
'm_devoiced' => 'deVoices '-v' Received'];
foreach ($modes as $mode => $title) {
$section .= $this->create_table($title, ['Total', 'User'], ['num', 'str'], ['SELECT '.$mode.' AS v1, csnick AS v2 FROM ruid_events JOIN uid_details ON ruid_events.ruid = uid_details.uid WHERE status NOT IN (3,4) AND '.$mode.' != 0 ORDER BY v1 DESC, ruid_events.ruid ASC LIMIT '.$rows, 'SELECT SUM('.$mode.') FROM ruid_events'], $rows);
}
if ($section !== '') {
$contents .= '<div class="section">Modes</div>'."\n".$section;
}
/**
* Events section.
*/
$section = '';
$section .= $this->create_table('Kicks Given', ['Total', 'User', 'Example'], ['num', 'str', 'str'], ['SELECT kicks AS v1, csnick AS v2, ex_kicks AS v3 FROM ruid_events JOIN uid_details ON ruid_events.ruid = uid_details.uid WHERE status NOT IN (3,4) AND kicks != 0 ORDER BY v1 DESC, ruid_events.ruid ASC LIMIT '.$rows, 'SELECT SUM(kicks) FROM ruid_events'], $rows);
$section .= $this->create_table('Kicks Received', ['Total', 'User', 'Example'], ['num', 'str', 'str'], ['SELECT kicked AS v1, csnick AS v2, ex_kicked AS v3 FROM ruid_events JOIN uid_details ON ruid_events.ruid = uid_details.uid WHERE status NOT IN (3,4) AND kicked != 0 ORDER BY v1 DESC, ruid_events.ruid ASC LIMIT '.$rows, 'SELECT SUM(kicked) FROM ruid_events'], $rows);
$section .= $this->create_table('Channel Joins', ['Total', 'User'], ['num', 'str'], ['SELECT joins AS v1, csnick AS v2 FROM ruid_events JOIN uid_details ON ruid_events.ruid = uid_details.uid WHERE status NOT IN (3,4) AND joins != 0 ORDER BY v1 DESC, ruid_events.ruid ASC LIMIT '.$rows, 'SELECT SUM(joins) FROM ruid_events'], $rows);
$section .= $this->create_table('Channel Parts', ['Total', 'User'], ['num', 'str'], ['SELECT parts AS v1, csnick AS v2 FROM ruid_events JOIN uid_details ON ruid_events.ruid = uid_details.uid WHERE status NOT IN (3,4) AND parts != 0 ORDER BY v1 DESC, ruid_events.ruid ASC LIMIT '.$rows, 'SELECT SUM(parts) FROM ruid_events'], $rows);
$section .= $this->create_table('IRC Quits', ['Total', 'User'], ['num', 'str'], ['SELECT quits AS v1, csnick AS v2 FROM ruid_events JOIN uid_details ON ruid_events.ruid = uid_details.uid WHERE status NOT IN (3,4) AND quits != 0 ORDER BY v1 DESC, ruid_events.ruid ASC LIMIT '.$rows, 'SELECT SUM(quits) FROM ruid_events'], $rows);
$section .= $this->create_table('Nick Changes', ['Total', 'User'], ['num', 'str'], ['SELECT nickchanges AS v1, csnick AS v2 FROM ruid_events JOIN uid_details ON ruid_events.ruid = uid_details.uid WHERE status NOT IN (3,4) AND nickchanges != 0 ORDER BY v1 DESC, ruid_events.ruid ASC LIMIT '.$rows, 'SELECT SUM(nickchanges) FROM ruid_events'], $rows);
$section .= $this->create_table('Aliases', ['Total', 'User'], ['num', 'str'], ['SELECT COUNT(*) - 1 AS v1, (SELECT csnick FROM uid_details WHERE uid = t1.ruid) AS v2 FROM uid_details AS t1 WHERE ruid IN (SELECT ruid FROM uid_details WHERE status = 1) GROUP BY ruid HAVING v1 != 0 ORDER BY v1 DESC, ruid ASC LIMIT '.$rows, 'SELECT COUNT(*) FROM uid_details WHERE status = 2'], $rows);
$section .= $this->create_table('Topics Set', ['Total', 'User'], ['num', 'str'], ['SELECT topics AS v1, csnick AS v2 FROM ruid_events JOIN uid_details ON ruid_events.ruid = uid_details.uid WHERE status NOT IN (3,4) AND topics != 0 ORDER BY v1 DESC, ruid_events.ruid ASC LIMIT '.$rows, 'SELECT SUM(topics) FROM ruid_events'], $rows);
$section .= $this->create_table('Most Recent Topics', ['Date', 'User', 'Topic'], ['date', 'str', 'str-url'], ['SELECT datetime AS v1, (SELECT csnick FROM uid_details WHERE uid = t1.ruid) AS v2, topic AS v3 FROM uid_topics JOIN uid_details AS t1 ON uid_topics.uid = t1.uid WHERE ruid NOT IN (SELECT ruid FROM uid_details WHERE status = 4) ORDER BY uid_topics.ROWID DESC LIMIT 5']);
if ($section !== '') {
$contents .= '<div class="section">Events</div>'."\n".$section;
}
/**
* Build the "Smileys" section.
*/
$section = '';
$results = db::query('SELECT category, smiley, SUM(total) AS total FROM ruid_smileys JOIN smileys ON ruid_smileys.sid = smileys.sid WHERE category IS NOT NULL GROUP BY category ORDER BY total DESC, ruid_smileys.sid ASC LIMIT 9');
while ($result = $results->fetchArray(SQLITE3_ASSOC)) {
$section .= $this->create_table(ucwords($result['category']).' '.$this->htmlify($result['smiley']), ['Total', 'User'], ['num', 'str'], ['SELECT SUM(total) AS v1, csnick AS v2 FROM ruid_smileys JOIN smileys ON ruid_smileys.sid = smileys.sid JOIN uid_details ON ruid_smileys.ruid = uid_details.uid WHERE status NOT IN (3,4) AND category = \''.$result['category'].'\' GROUP BY ruid_smileys.ruid, category ORDER BY v1 DESC, ruid_smileys.ruid ASC LIMIT '.$rows, $result['total']], $rows);
}
if ($section !== '') {
$contents .= '<div class="section">Smileys</div>'."\n".$section;
}
/**
* Build the "Expressions" section.
*/
$section = '';
$results = db::query('SELECT smiley, SUM(total) AS total FROM ruid_smileys JOIN smileys ON ruid_smileys.sid = smileys.sid WHERE category IS NULL GROUP BY smiley ORDER BY total DESC, ruid_smileys.sid ASC LIMIT 9');
while ($result = $results->fetchArray(SQLITE3_ASSOC)) {
$section .= $this->create_table('"<i>'.$result['smiley'].'</i>"', ['Total', 'User'], ['num', 'str'], ['SELECT total AS v1, csnick AS v2 FROM ruid_smileys JOIN smileys ON ruid_smileys.sid = smileys.sid JOIN uid_details ON ruid_smileys.ruid = uid_details.uid WHERE status NOT IN (3,4) AND smiley = \''.$result['smiley'].'\' ORDER BY v1 DESC, ruid_smileys.ruid ASC LIMIT '.$rows, $result['total']], $rows);
}
if ($section !== '') {
$contents .= '<div class="section">Expressions</div>'."\n".$section;
}
/**
* Build the "URLs" section.
*/
$section = '';
$section .= $this->create_table('Most Referenced Domain Names', ['Total', 'Domain', 'First Used'], ['num', 'url', 'date'], ['SELECT SUM(total) AS v1, \'http://\' || fqdn AS v2, MIN(firstused) AS v3 FROM ruid_urls JOIN urls ON ruid_urls.lid = urls.lid JOIN fqdns ON urls.fid = fqdns.fid WHERE active = 1 GROUP BY urls.fid ORDER BY v1 DESC, v3 ASC LIMIT 10'], 10);
$section .= $this->create_table('Most Referenced TLDs', ['Total', 'TLD'], ['num', 'str'], ['SELECT SUM(total) AS v1, \'.\' || tld AS v2 FROM ruid_urls JOIN urls ON ruid_urls.lid = urls.lid JOIN fqdns ON urls.fid = fqdns.fid WHERE active = 1 GROUP BY tld ORDER BY v1 DESC, v2 ASC LIMIT 10'], 10);
$section .= $this->create_table('Most Recent URLs', ['Date', 'User', 'URL'], ['date-norepeat', 'str', 'url'], ['SELECT lastused AS v1, csnick AS v2, url AS v3 FROM ruid_urls JOIN uid_details ON ruid_urls.ruid = uid_details.uid JOIN urls ON ruid_urls.lid = urls.lid WHERE status NOT IN (3,4) ORDER BY v1 DESC LIMIT 30'], 30);
$section .= $this->create_table('URLs by Users', ['Total', 'User'], ['num', 'str'], ['SELECT urls AS v1, csnick AS v2 FROM ruid_lines JOIN uid_details ON ruid_lines.ruid = uid_details.uid WHERE status NOT IN (3,4) AND urls != 0 ORDER BY v1 DESC, ruid_lines.ruid ASC LIMIT '.$rows, 'SELECT SUM(urls) FROM ruid_lines JOIN uid_details ON ruid_lines.ruid = uid_details.uid WHERE status != 3'], $rows);
$section .= $this->create_table('URLs by Bots', ['Total', 'Bot'], ['num', 'str'], ['SELECT urls AS v1, csnick AS v2 FROM ruid_lines JOIN uid_details ON ruid_lines.ruid = uid_details.uid WHERE status = 3 AND urls != 0 ORDER BY v1 DESC, ruid_lines.ruid ASC LIMIT '.$rows, 'SELECT SUM(urls) FROM ruid_lines JOIN uid_details ON ruid_lines.ruid = uid_details.uid WHERE status = 3'], $rows);
$section .= $this->create_table('Reposts by Users', ['Total', 'User'], ['num', 'str'], ['SELECT SUM(total - CASE WHEN firstused = (SELECT MIN(firstused) FROM ruid_urls WHERE lid = t1.lid) THEN 1 ELSE 0 END) AS v1, csnick AS v2 FROM ruid_urls AS t1 JOIN uid_details ON t1.ruid = uid_details.uid WHERE status NOT IN (3,4) GROUP BY t1.ruid HAVING v1 != 0 ORDER BY v1 DESC, t1.ruid ASC LIMIT '.$rows, 'SELECT SUM(total - CASE WHEN firstused = (SELECT MIN(firstused) FROM ruid_urls WHERE lid = t1.lid) THEN 1 ELSE 0 END) FROM ruid_urls AS t1 JOIN uid_details ON t1.ruid = uid_details.uid WHERE status != 3'], $rows);
if ($section !== '') {
$contents .= '<div class="section">URLs</div>'."\n".$section;
}
/**
* Build the "Words by Length" section.
*/
$section = '';
$results = db::query('SELECT * FROM (SELECT length, COUNT(*) AS total FROM words GROUP BY length ORDER BY total DESC, length DESC LIMIT 12) ORDER BY length ASC');
while ($result = $results->fetchArray(SQLITE3_ASSOC)) {
/**
* Hide nicks with at least 3 days of activity or 20 lines typed from words.
*/
$section .= $this->create_table('Words of '.$result['length'].' Characters', ['Total', 'Word'], ['num', 'str'], ['SELECT total AS v1, word AS v2 FROM words LEFT JOIN uid_details AS t1 ON words.word = t1.csnick COLLATE NOCASE WHERE length = '.$result['length'].' AND (csnick IS NULL OR ((SELECT COUNT(*) FROM uid_activity WHERE uid = t1.uid) < 3 AND IFNULL((SELECT l_total FROM uid_lines WHERE uid = t1.uid), 0) < 20)) ORDER BY v1 DESC, v2 ASC LIMIT '.$rows, $result['total']], $rows);
}
if ($section !== '') {
$contents .= '<div class="section">Words by Length</div>'."\n".$section;
}
/**
* Build the "Words by Year of First Use" section.
*/
$section = '';
$results = db::query('SELECT DISTINCT firstused FROM words ORDER BY firstused ASC');
while ($result = $results->fetchArray(SQLITE3_ASSOC)) {
/**
* Hide nicks with at least 3 days of activity or 20 lines typed from words.
*/
$section .= $this->create_table('Words First Used in '.$result['firstused'], ['Total', 'Word'], ['num', 'str'], ['SELECT total AS v1, word AS v2 FROM words LEFT JOIN uid_details AS t1 ON words.word = t1.csnick COLLATE NOCASE WHERE firstused = \''.$result['firstused'].'\' AND (csnick IS NULL OR ((SELECT COUNT(*) FROM uid_activity WHERE uid = t1.uid) < 3 AND IFNULL((SELECT l_total FROM uid_lines WHERE uid = t1.uid), 0) < 20)) ORDER BY v1 DESC, v2 ASC LIMIT '.$rows, 'SELECT COUNT(*) FROM words WHERE firstused = \''.$result['firstused'].'\''], $rows);
}
if ($section !== '') {
$contents .= '<div class="section">Words by Year of First Use</div>'."\n".$section;
}
/**
* Build the "Milestones" section.
*/
$section = '';
$results = db::query('SELECT milestone, COUNT(*) AS total FROM ruid_milestones GROUP BY milestone ORDER BY milestone ASC');
while ($result = $results->fetchArray(SQLITE3_ASSOC)) {
$section .= $this->create_table(number_format($result['milestone']).' Lines Milestone', ['Date', 'User'], ['date', 'str'], ['SELECT date AS v1, csnick AS v2 FROM ruid_milestones JOIN uid_details ON ruid_milestones.ruid = uid_details.uid WHERE milestone = '.$result['milestone'].' ORDER BY v1 ASC, ruid_milestones.ruid ASC LIMIT '.$rows, $result['total']], $rows);
}
if ($section !== '') {
$contents .= '<div class="section">Milestones</div>'."\n".$section;
}
/**
* FOOT
*/
$contents .= '<div class="info">Statistics created with <a href="https://sss.dutnie.nl">superseriousstats</a> on '.date('r').' <span class="grey">('.date('T').')</span>.</div>'."\n";
$contents .= '</div></body>'."\n\n".'</html>'."\n";
return $contents;
}
/**
* Create the "Less Talkative People" table.
*/
private function create_table_people2(): ?string
{
$offset = ($this->xxl ? 50 : 30);
$rows = ($this->xxl ? 30 : 15);
$results = db::query('SELECT csnick, l_total FROM ruid_lines JOIN uid_details ON ruid_lines.ruid = uid_details.uid WHERE status NOT IN (3,4) AND l_total != 0 ORDER BY l_total DESC, ruid_lines.ruid ASC LIMIT '.$offset.','.($rows * 3));
$col = 1;
$row = 0;
while ($result = $results->fetchArray(SQLITE3_ASSOC)) {
if (++$row > $rows) {
++$col;
$row = 1;
}
$columns[$col][$row] = [
'csnick' => $result['csnick'],
'l_total' => $result['l_total'],
'pos' => $offset + (($col - 1) * $rows) + $row];
}
/**
* Return if we don't have enough data to fill the table.
*/
if (!isset($columns[3][$rows])) {
return null;
}
$total = db::query_single_col('SELECT COUNT(*) FROM ruid_lines JOIN uid_details ON ruid_lines.ruid = uid_details.uid WHERE status NOT IN (3,4)') - ($offset + $rows * 3);
$colgroup = '<colgroup>'.str_repeat('<col>', 13);
$thead = '<thead><tr><th colspan="13">'.($total !== 0 ? '<span class="title-left">Less Talkative People – All-Time</span><span class="title-right">'.number_format($total).($total !== 1 ? ' People' : ' Person').' had even less to say..</span>' : 'Less Talkative People – All-Time');
$thead .= '<tr><td><td>Lines<td><td>User<td><td>Lines<td><td>User<td><td>Lines<td><td>User<td>';
$tbody = '<tbody>';
for ($i = 1; $i <= $rows; ++$i) {
$tbody .= '<tr><td>';
for ($j = 1; $j <= 3; ++$j) {
$tbody .= '<td>'.number_format($columns[$j][$i]['l_total']).'<td>'.$columns[$j][$i]['pos'].'<td>'.($this->link_user_php ? '<a href="user.php?nick='.$this->htmlify(urlencode($columns[$j][$i]['csnick'])).'">'.$this->htmlify($columns[$j][$i]['csnick']).'</a>' : $this->htmlify($columns[$j][$i]['csnick'])).'<td>';
}
}
return '<table class="ppl2">'.$colgroup.$thead.$tbody.'</table>'."\n";
}
}